The Digital Team is responsible for maintaining the ASP app and databases which drive the assessing online application.
See tabbed notes for December (on this page) for detailed instructions.
When ready to deploy:
See tabbed notes for July (on this page) for detailed instructions.
The ASP application is served by an IIS Server from zpcobweb01.web.cob.
On the Windows server hosting the IIS Server, the ASP files which make up the assessing online app can be found at d:\wwwcob\assessing
.
The files which are appear to be live (i.e. have no redirect to boston.gov) and which currently live in the folder are:
Create sandbox ASP page for testing.
Create a copy of default.asp
named default20XXQ3.asp
.
Update DB connection string.
Once the SQLDBA's have cloned the current live database:
In the global.asa
for the IIS server (in the root of wwwcob folder) there are a number of database connection strings defined. Search for assessingupdates
and you will find a block defining the assessing-online apps connection strings. Make a new connection string called assessingupdates_20YYQ3
and point it at the new database created by the DBA's.
The constant for AssessingSearchDB
in (line 58 of) default20YYQ3.asp
indicates the actual connection string for the page to use. Change it to assessingupdates_20YYQ3
(which will then run SQL queries on the page in the assessingupdates20YYQ3
database in the MSSQL Server).
Make changes to dates and tax rates for next year.
Using data provided by the Assessing Team, update the constants in default20YYQ3.asp.
Refer to the notes in the default.asp page next to each constant for guidance.
Testing/verification of the new data can now be completed at:
https://cityofboston.gov/assessing/search/default20YYQ3.asp
The page rendered by this script should have a banner indicating this is still a test page along with info on the date being simulated (see below) and the connection string being used.
This banner will automatically be removed when the page is deployed (i.e. when the file is renamed default.asp
)
While testing, you can add &date=YYYY-MM-DD
to the URL to test the site for a future date and see that the date fields and the availability of exemption forms links are altering on the page as expected.
Create sandbox ASP page for testing.
Create a copy of default.asp
named default20YYQ1.asp
.
Update DB connection string.
Once the SQLDBA's have cloned the current live database:
In the global.asa
for the IIS server (in the root of wwwcob folder) there are a number of database connection strings defined. Search for assessingupdates
and you will find a block defining the assessing-online apps connection strings. Make a new connection string called assessingupdates20YYQ1
and point it at the new database created by the DBA's.
The constant for AssessingSearchDB
in (line 56 of) default20YYQ1.asp
indicates the actual connection string for the page to use. Change it to assessingupdates20YYQ1
(which will then run SQL queries on the page in the assessingupdates20YYQ1
database in the MSSQL Server).
Testing/verification of the new data can now be completed at:
https://cityofboston.gov/assessing/search/default20YYQ1.asp
Typically the dates in the form do not change, and the tax rate should also not change. Really, the only expected change to default20YYQ1.asp
is to change the DB connection string name.
There are a number of forms which are provided as part of the assessment process during the first part of each calendar year.
PDF's are generated by a service on boston.gov so the templates for these forms are saved in docroot/sites/default/files/pdf_templates/pdf/FY20YY
on the boston.gov webserver.
When a constituent requests a form, it is processed through the bos_assessing
managed endpoint at boston.gov/assessing-online/
This endpoint adds a barcode (related to the parcelid) and selected tax bill information onto the form.
See Assessing Form generation notes here, and more detailed notes on PDF generation here.
Various assessing forms are made available to constituents based on a calendar. The calendar is set in the constants fields in wwwcob\assessing\search\default.asp
(see box above).
Once those dates are set, the form display is controlled by code and no further action is needed to enable or disable them.
PDF files can be modified and copied to the boston.gov web server using this command (from the docroot folder):
Non-Prod Environments
rsync -arz -essh -P sites/default/files/pdf_templates/pdf/FY20YY/* "bostond8.dev@bostond8dev.ssh.prod.acquia-sites.com:/mnt/gfs/bostond8dev/sites/default/files/pdf_templates/pdf/FY20YY/"
This command copies to the dev environment. All other Acquia non-prod environments map this folder, so running this command simultaneously copies across for all non-prod environments.
Prod Environment
rsync -arz -essh -P sites/default/files/pdf_templates/pdf/FY20YY/* "bostond8.prod@bostond8.ssh.prod.acquia-sites.com:/mnt/gfs/bostond8/sites/default/files/pdf_templates/pdf/FY20YY/"
Each year new forms for exemptions and other request are generated and will be provided by the Assessing Team.
There is nothing to be done with forms in the July updates.
The database which contains the data for the assessing online app is contained onvsql01
(aka zpdmzsql01, vsql02
, zrb01
(?)). This may well be migrated to vsql71
at some point.
The assessing department validate and transfer data to the Digital Team via an MS Access database. This database contains tables of data that should be mapped and uploaded to databases on the MS SQLServer.
MSAccess data is imported into tables prefixed "_" in the MSSQL Database.
Stored procedures are then written to update/merge data from the import tables into the main tables used by the app. The stored procedure is saved in the DB as a record of the updates and manipulations performed.
There is a bash script on the cityofboston IIS server (zpcobweb01
) at /wwwcob/assessing/search/extract.sh
which migrates and pre-processes the data from MSAccess to the MS SQL Server. In a perfect world, this script can just be run to preform the migration (it takes some hours to complete).
Obtain the updated copy of the MS Access database.
In the assessingupdates20YYQ3
database, delete all tables prefixed "_", these are import tables from the previous Q1 update.
Following these instructions, use the extract.sh
script to copy tables from Access to assessingupdates20YYQ3
in MS SQL.
Notes from Assessing Team will detail any particular instructions on data manipulations that are required.
To keep a record and for replaying updates, and data updates (from import tables to main tables) and data manipulations should be coded into stored procedures and executed from the stored procedure.
The stored procedure should be saved in the DB, and named sp_20YYQ3_import
. If multiple sp's are used, then number them sequentially (sp_20YYQ3_import_1
etc).
For example, in 2023Q3 there is a (incorrectly named...) stored procedure sp_update_current_owners
which should be executed to make additional data changes to parcel/property ownership.
Testing/verification of the new data can then be completed at:
https://cityofboston.gov/assessing/search/default20YYQ3.asp
Obtain the updated copy of the MS Access database.
In the assessingupdates20YYQ1
database, delete all tables prefixed "_", these are import tables from the previous Q3 update.
Following these instructions, copy tables from Access to assessingupdates20YYQ1
in MS SQL.
Notes from Assessing Team will detail any particular instructions on data manipulations that are required.
To keep a record and for replaying updates, and data updates (from import tables to main tables) and data manipulations should be coded into stored procedures and executed from the stored procedure.
The stored procedure should be saved in the DB, and named sp_20YYQ1_import
. If multiple sp's are used, then number them sequentially (sp_20YYQ1_import_1
etc).
Filename | Last Modified | Description |
---|---|---|
..\global.asa (wwwcob)
2022
Contains the database connection strings for the whole cityofboston.gov website. See drawer below.
paymentinfo.asp
07/2017
payments.asp
11/2015
res_prop_sales_archive.asp
03/2016
Suspect this file is not live, just an archive of res_sales_prop.asp which has not been redirected.
taxratehistory.asp
03/2016
global.asa (assessing)
08/2013
Contains connection strings which do not appear to be used.
paymentshistory\default.aspx
03/2014
Unknown, appears to be a file uploader - inserts records into the Treasury database on vsql01
search\default.asp
11/2022
This is the main script which drives the AOL application. See drawer below.
search\hotline.asp
06/2020
Unknown, looks unused.
search\json.asp
05/2016
Unknown, looks unused.
How-tos and gotchas for legacy Assessing On Line (aka AOL -sic)
The dates in search/default.asp
(approx lines 43-48):
These dates are used to control a text block which invites residents to submit Property Revaluation submission/requests.
The PublicDisclosureStartAnnouncing, PublicDisclosureStartOnWeb
and PublicDisclosureEnd` should all be the same date/time, or be in the past to prevent the message from displaying.
Generally, the dates do not need to be updated year-to-year unless advised by Assessing.
When dates are set to show the block, this is what it looks like this (using Nov 2023 based dates):
If requested by the Assessing Dept, the current owner of a parcel/property will need to be changed on occasion. You need to know the old owner name, the new owner name and the parcel id - this change can be made directly in VSQL01
using this script:
This page contains information on the database tables in assessingsearch on VSQL01. (NOTE: assessingupdates is a clone of assessingsearch for dev)
This view does not appear to be used by default.asp
or any other procedures.
This view could be deleted from the DB.
None of these stored procedures appear to be used by default.asp
or any view or triggers.
These procedures could be deleted from the DB
This table is accessed from default.asp
.
Check the columns in the MS Access database to see if new information is provided. If the unlikely event that it is, then check with Assessing Dept to see if the info needs to be displayed.
If so, then the query and HTML table in default.asp
needs to be updated to display the new information.
This table is accessed from default.asp
.
Potentially, there is some adjustment of the bid amount depending on billing schedule.
Check the tables in the MS Access database to see if new BID region/categories have been created. If the unlikely event there are, then check with Assessing Dept to see if the info needs to be displayed.
If so, then this table will need an additional column for the new bid region/category, and the HTML in default.asp
will need to be updated.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
It seems that this table only contains owners with the seqno
>= 1, so the primary current owners.
2022 year only included seqno
= 1, but the code looks like it will be OK with seqno
> 1
This table is accessed from default.asp
.
This data should be unchanged between years, but double check with Assessing Team to be sure.
Nothing to do, just be sure the table exists from the previous year
This table is accessed from default.asp
.
This table does not appear to be used.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This data should be unchanged between years, but double check with Assessing Team to be sure.This data should be unchanged between years, but double check with Assessing Team to be sure.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
As noted elsewhere, data for the MS SQL databases are provided by Assessing in MS Access database/s.
The number of rows in the database tables is high (e.g. 150k - 400k records) and the data size of the tables can be high (30 - 500 MB).
This means a strategy for updating the SQL database with the source data needs to accommodate the size of the import, and the time it will take to import.
Request a new assessingupdates20YYQX
database be created for data import.
a. In Q1 this should be a copy of the previous Q3 database. b. in Q3 this can be an empty database.
Extract the schema from Access database and use to create new import tables in the new database.
Export the data from each of the source tables in the MS Access database as a series of INSERT statements.
Manipulate the data from the working tables into the "permanent" tables in assessingupdates20YYQX
.
In 12/2023 for FY2024 Q3 a script was created (intended to run on a linux workstation or in a WSL2 session on Windows). The constants from line 13-22 in the script need to be chaked and updated as needed.
Assuming the import is being performed on a Linux computer or Windows computer in a WSL2 session, with the /wwwcob/assessing/search
forlder copied/cloned locally - to run the script:
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
Column | Source | Notes |
---|
parcel_id nvarchar(10) | MS Access | PK The unique parcel ID |
living_area int | MS Access |
gross_area int | MS Access |
year_built smallint | MS Access |
year_remodeled smallint | MS Access |
condo_units_residential smallint | MS Access |
condo_units_commercial smallint | MS Access |
condo_units_mixed smallint | MS Access |
stories decimal(4, 1) | MS Access |
parcel_id nvarchar(10) | PK The unique parcel ID |
bid_greenway money | MS Access | This is extracted and compiled from the greenway_bid table in the MSAccess database. |
bid_downtown money | MS Access | This is extracted and compiled from the greenway_bid table in the MSAccess database. |
parcel_id nchar(10) |
Style nvarchar(20) |
Exterior Condition nvarchar(14) |
Exterior Wall nvarchar(14) |
Grade nvarchar(14) |
Stories nvarchar(4) |
'#Units' decimal(9, 0) |
Street Type nvarchar(50) |
Square Feet of Living area decimal(9, 0) |
Base Floor nvarchar(4) |
Fireplaces nvarchar(4) |
'# Floors' nvarchar(4) |
Total Rooms nvarchar(4) |
Bedrooms nvarchar(4) |
Bathrooms nvarchar(4) |
Half Bathrooms nvarchar(4) |
Bathroom Style1 nvarchar(14) |
Bathroom Style2 nvarchar(14) |
Bathroom Style3 nvarchar(14) |
Kitchen Style nvarchar(14) |
Kitchen Type nvarchar(14) |
Heat Type nvarchar(14) |
Interior Condition nvarchar(14) |
Interior Finish nvarchar(14) |
Orientation nvarchar(14) |
Corner Unit nvarchar(14) |
View nvarchar(14) |
condo_main nchar(10) |
CNS_BID decimal(9, 0) |
Parcel_id nchar(10) | PK |
seqno tinyint | PK |
owner_name nvarchar(255) |
id int |
Short_Description nvarchar(10) |
Description nvarchar(50) |
parcel_id nchar(10) | PK |
line_number tinyint | PK |
Code nvarchar(50) |
Tot Units decimal(9, 2) |
Quantity decimal(9, 2) |
Quality nvarchar(50) |
Condition nvarchar(50) |
parcel_id nchar(10) | PK |
street_number nvarchar(10) |
street_name nvarchar(50) |
apartment_no nvarchar(20) |
suffix nvarchar(2) |
landuse nvarchar(2) |
owner nvarchar(255) |
condo_main nchar(10) |
parcel_id nchar(10) |
street_number nvarchar(10) |
street_name nvarchar(50) |
apartment_no nvarchar(20) |
suffix nvarchar(2) |
landuse nvarchar(2) |
owner nvarchar(255) |
condo_main nchar(10) |
property-code smallint |
property-class tinyint |
property-class-description nvarchar(255) |
property-code-description nvarchar(255) |
property-code-state bit |
property-code-city bit |
parcel_id nvarchar(10) | MSAccess:Tyler Real Estate Export File |
residential_exemption nvarchar(255) | MSAccess:Tyler Real Estate Export File |
personal_exemption bit | MSAccess:Taxes | Defaults to 0 but is 1 if either of |
parcel_id nvarchar(255) |
Building Seq float |
Composite Land Use nvarchar(255) |
Building Style nvarchar(255) |
Rooms nvarchar(255) |
Bedrooms nvarchar(255) |
Full Bath float |
Half Bath float |
Other Fixtures float |
Bath Style 1 nvarchar(255) |
Bath Style 2 nvarchar(255) |
Bath Style 3 nvarchar(255) |
Kitchens float |
Kitchen Type nvarchar(255) |
Kitchen Style 1 nvarchar(255) |
Kitchen Style 2 nvarchar(255) |
Kitchen Style 3 nvarchar(255) |
Fireplaces float |
AC Type nvarchar(255) |
Heat Type nvarchar(255) |
Interior Condition nvarchar(255) |
Interior Finish nvarchar(255) |
View nvarchar(255) |
Grade nvarchar(255) |
"# of Parking Spots" nvarchar(255) |
Year Built float |
Story Height nvarchar(255) |
Roof Cover nvarchar(255) |
Roof Structure nvarchar(255) |
Exterior Finish nvarchar(255) |
Exterior Condition nvarchar(255) |
Foundation nvarchar(255) |
parcel_id nchar(10) |
Bill Year smallint |
Bill Number int |
RE Tax Amt decimal(12, 2) |
CPA Amt decimal(12, 2) |
Downtown BID Amt decimal(12, 2) |
Greenway BID Amt decimal(12, 2) |
Total Billed Amt decimal(12, 2) |
parcel_id nchar(10) |
condo_main nchar(10) |
ward_precinct_block nvarchar(7) |
street_number nvarchar(10) |
street_number_suffix nvarchar(10) |
street_name nvarchar(50) |
street_name_only nvarchar(50) |
street_name_suffix nvarchar(2) |
apt_unit nvarchar(20) |
city nvarchar(50) |
location_zip_code nvarchar(5) |
location_zip_code_plus_four nvarchar(4) |
owner nvarchar(50) |
mail_addressee nvarchar(50) |
mail_street_address1 nvarchar(50) |
mail_street_address2 nvarchar(50) |
mail_city_and_state nvarchar(50) |
mail_zip_code nvarchar(5) |
mail_zip_code_plus_four nvarchar(4) |
country nvarchar(50) |
future_owner nvarchar(50) |
future_mail_addressee nvarchar(50) |
future_owner_mail_street_address1 nvarchar(50) |
future_owner_mail_street_address2 nvarchar(50) |
future_owner_mail_city_and_state nvarchar(50) |
future_owner_mail_zip_code nvarchar(5) |
future_owner_mail_zip_code_plus_four nvarchar(4) |
future_owner_country nvarchar(50) |
land_area int |
land_use nvarchar(2) |
exempt_code nvarchar(4) |
property_type nvarchar(4) |
state_class_code nvarchar(1) |
residential_building_value decimal(12, 2) residential_land_value decimal(12, 2) residential_air_rights_value decimal(12, 2) |
commercial_building_value decimal(12, 2) commercial_land_value decimal(12, 2) commercial_air_rights_value decimal(12, 2) |
industrial_building_value decimal(12, 2) industrial_land_value decimal(12, 2) industrial_air_rights_value decimal(12, 2) |
open_space_land_value decimal(12, 2) |
exempt_building_value decimal(12, 2) exempt_land_value decimal(12, 2) |
total_building_value decimal(12, 2) total_land_value decimal(12, 2) total_value decimal(12, 2) |
latest_sale_date date |
latest_bkpgcert nvarchar(10) |
residential_exemption_flag bit |
coop_value decimal(12, 2) |
clause_abatement_type_1 nvarchar(6) clause_abt_1_pct_ownership nvarchar(5) clause_abt_1_pct_occupancy nvarchar(5) clause_abatement_type_2 nvarchar(6) clause_abt_2_pct_ownership nvarchar(5) clause_abt_2_pct_occupancy nvarchar(5) |
paraplegic nvarchar(6) |
workoff_type nvarchar(6) workoff_credit_amt decimal(12, 2) |
sewer_betterment_amt decimal(12, 2) street_betterment_amt decimal(12, 2) sidewalk_betterment_amt decimal(12, 2) |
penalty1_type nvarchar(6) penalty1_amt decimal(12, 2) penalty2_type nvarchar(6) penalty2_amt decimal(12, 2) penalty3_type nvarchar(6) penalty3_amt decimal(12, 2) |
fine1_type nvarchar(6) fine1_amt decimal(12, 2) fine2_type nvarchar(6) fine2_amt decimal(12, 2) fine3_type nvarchar(6) fine3_amt decimal(12, 2) |
BID_type nvarchar(6) BID_amt decimal(12, 2) |
nontax_collection_type nvarchar(6) nontax_collection_amt decimal(12, 2) |
personal_exemption_flag bit |
condo_main_value decimal(12, 2) |
parcel_id nchar(10) | PK |
gross_tax decimal(12, 2) |
net_tax decimal(12, 2) |
persexempt_1 decimal(12, 2) |
persexempt_2 decimal(12, 2) |
persexempt_total decimal(12, 2) |
resexempt decimal(12, 2) |
cpa decimal(12, 2) |
code_enforcement_tax decimal(12, 2) |
38D_fine decimal(12, 2) |
sidewalk_betterment decimal(12, 2) |
street_betterment decimal(12, 2) |
bill_number int |
Parcel_id nchar(10) |
Fiscal_Year smallint |
Assessed_value bigint |
Land_use nvarchar(4) |