# Assessing Online (AOL) Maintenance ### Maintenance Cycle Checklist {% tabs %} {% tab title="December (Q3)" %} See tabbed notes for December (on this page) for detailed instructions. * [ ] ACQUIA: Around 20 Dec, set the conntoken\_assessing field in the `DBCONNECTOR_SETTINGS`environment variable on `Production` to an empty string (i.e. `username_assessing:xxxxxx,password_assessing:xxx,conntoken_assessing:)` see [knowledgebase](https://docs.boston.gov/digital/guides/legacy-website-cityofboston.gov/legacy-website-maintenance/knowledge-base#pdf-token) * [ ] MSSQL: Request SQLDBA's to clone the current live database (`assessingupdates20YYQ1` ) to a new database called `assessingupdates20YYQ3` where YY is the current financial year. * [ ] IIS: Update `global.asa` with new connection string * [ ] IIS: Create sandbox `default.asp` page for testing by copying the current `default.asp` to `default20YYQ3.asp` , * [ ] IIS: Update the dates and tax rates in sandbox, * [ ] IIS: Update sandbox to connect to the `assessingupdates_20YYQ3` connection string, * [ ] MSSQL: Update MS SQL `assessingupdates20YYQ3` database [with data from MS Access,](https://docs.boston.gov/digital/guides/legacy-website-cityofboston.gov/legacy-website-maintenance/database-tables#updating-database-tables) * [ ] MSSQL: Run any adjustment processes in `assessingupdates20YYQ3` database, * [ ] ACQUIA: Copy new forms into pdf folder at `/sites/default/files/pdf_templates/pdf/FY20YY`, * [ ] AWS: (using Postman) Create a new connection the the dbconnector proxy in local/stage and prod environments which connects to the `assessingupdates20YYQ3` database - note the connection token. * [ ] IIS: Test sandbox, and re-import data as needed/provided by Assessing Team, When ready to deploy: * [ ] ACQUIA: Update the token in `DBCONNECTOR_SETTINGS` envar for the relevant environments * [ ] IIS: Backup the current `default.asp` page by copying into `default.asp20YYQ3.bak` . * [ ] IIS: To put the new page and data live, simply delete `default.asp` and then rename `default20YYQ3.asp` to`default.asp.` * [ ] IIS: Cleanup any remaining asp pages you may have created and any redundant databases and tables. {% endtab %} {% tab title="July (Q1)" %} See tabbed notes for July (on this page) for detailed instructions. * [ ] Request SQLDBA's to clone the current live database (`assessingupdates20YYQ3` ) to a new database called `assessingupdates20YYQ1` where YY is the next financial year. * [ ] Create sandbox `default.asp` page for testing by copying the current `default.asp` to `default20YYQ1.asp`, * [ ] Update sandbox to connect to the `assessingupdates20YYQ1` MS SQL database, * [ ] Update MS SQL `assessingupdates20YYQ1` database with [data from MS Access](https://docs.boston.gov/digital/guides/legacy-website-cityofboston.gov/legacy-website-maintenance/database-tables#updating-database-tables), * [ ] Run any needed adjustment processes in `assessingupdates20YYQ1` database, * [ ] To put the new page and data live, simply delete `default.asp` and then rename `default20YYQ1.asp` to`default.asp.` * [ ] Cleanup any remaining asp pages you may have created and any redundant databases and tables. {% endtab %} {% endtabs %} ### ASP Application 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:
FilenameLast ModifiedDescription
..\global.asa (wwwcob)2022Contains the database connection strings for the whole cityofboston.gov website. See drawer below.
paymentinfo.asp07/2017
payments.asp11/2015
res_prop_sales_archive.asp03/2016Suspect this file is not live, just an archive of res_sales_prop.asp which has not been redirected.
taxratehistory.asp03/2016
global.asa (assessing)08/2013Contains connection strings which do not appear to be used.
paymentshistory\default.aspx03/2014Unknown, appears to be a file uploader - inserts records into the Treasury database on vsql01
search\default.asp11/2022This is the main script which drives the AOL application. See drawer below.
search\hotline.asp06/2020Unknown, looks unused.
search\json.asp05/2016Unknown, looks unused.
wwwcob\global.asa This file contains connection strings for the whole cityofboston.gov website. The strings used by Assessing on-line are found around line 450:\ Create a new set of Sesion variables copying the pattern from the previous quarters connection string. ```vba '--Project Data Connection SQL Session("assessingupdates_20YYQ3_ConnectionString") = "DRIVER=SQL Server;SERVER=VSQL01.web.cob;DATABASE=assessingupdates_20YYQ3;" Session("assessingupdates_20YYQ3_ConnectionTimeout") = 15 Session("assessingupdates_20YYQ3_CommandTimeout") = 30 Session("assessingupdates_20YYQ3_RuntimeUserName") = "***" Session("assessingupdates_20YYQ3_RuntimePassword") = "***" ``` **`Note:`**Do not use IPAddresses in connection strings. Also use the DNS for the SQL Server cluster, not the DNS entry for the actual Database server. i.e. do not use the server `zpdmzsql01.web.cob` instead use the cluster `vsql01.` The cluster will always redirect traffic to the currently active database server during maintenance or fail-over conditions.
wwwcob\assessing\search\default.asp This file contains the assessing online search service. There is a section titled BEGIN CONSTANTS (line 20) to END CONSTANTS (line 70). This block of code contains a number of constants. Principally these are dates, but also some tax rate information. This data is provided by the Assessing Team in Dec each year. ***Nothing outside of this block requires routine maintenance.***
#### Maintenance Cycle {% tabs %} {% tab title="December (Q3)" %} **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.` \ \&#xNAN;*Refer to the notes in the default.asp page next to each constant for guidance.* {% hint style="success" %} 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`)* {% endhint %} {% hint style="info" %} 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. {% endhint %} {% endtab %} {% tab title="July (Q1)" %} **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). {% hint style="success" %} Testing/verification of the new data can now be completed at:\ `https://cityofboston.gov/assessing/search/default20YYQ1.asp` {% endhint %} {% hint style="info" %} 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. {% endhint %} {% endtab %} {% endtabs %} ### Assessing Forms 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](https://docs.boston.gov/digital/guides/drupal-8/drupal-8-micro-services-api-end-points/assessing-forms-endpoint), and more detailed notes on [PDF generation here.](https://docs.boston.gov/digital/guides/drupal-8/drupal-8-micro-services-api-end-points/pdf-manager-module) {% hint style="success" %} 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. {% endhint %} {% hint style="info" %} 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/"` {% endhint %} #### Maintenance Cycle {% tabs %} {% tab title="December (Q3)" %} Each year new forms for exemptions and other request are generated and will be provided by the Assessing Team. * [ ] Ensure dates (`AbatementExemptionStartThisYear`, `AbatementDeadlineThisYear` and `ExemptionDeadlineThisYear`) are set correctly in `wwwcob\assessing\search\default20YYQ3.asp` * [ ] Upload the forms (typically 4) to a new folder at `docroot/sites/default/files/pdf_templates/pdf/FY20xx.` * [ ] Update the the json and fdf files in the same folder. {% endtab %} {% tab title="July (Q1)" %} **There is nothing to be done with forms in the July updates.** {% endtab %} {% endtabs %} ### MSSQL Database The database which contains the data for the assessing online app is contained on`vsql01` (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. {% hint style="info" %} 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. {% endhint %} {% hint style="success" %} 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). {% endhint %} #### Maintenance Cycle {% tabs %} {% tab title="December (Q3)" %} 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](https://docs.boston.gov/digital/guides/legacy-website-cityofboston.gov/legacy-website-maintenance/database-tables#updating-database-tables), 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.* {% hint style="success" %} Testing/verification of the new data can then be completed at:\ `https://cityofboston.gov/assessing/search/default20YYQ3.asp` {% endhint %} {% endtab %} {% tab title="July (Q1)" %} 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](https://docs.boston.gov/digital/guides/legacy-website-cityofboston.gov/legacy-website-maintenance/database-tables#updating-database-tables), 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). {% endtab %} {% endtabs %}