How to upgrade to v16 SQL - abcsoftware/abc-docs GitHub Wiki

Installing Version 16

Server Core 16 provides:

  • Management of one or more Legacy Server instances.
  • Connections to the PostgreSQL database cluster for the Legacy Server.
  • Bi-directional communication between Client5 and the Legacy Server.
  • New reports in the interactive report viewer.
  • Hosting for input web applications such as the new bank statement reconciliation app and Client5.

Upgrade Preparation

Updating Clients

  1. For Client4, update to the latest version for v16. In Advanced Setup Options, set Custom Browser to Chrome. You can update the clients even before updating the server; it may just show some anomalies on the startup (*) screen.
  2. Client5 shortcuts will need to be updated after server core is installed.

Prepare for Export from v15

This can be done while waiting on the Server.Core and PostgreSQL installers.

  1. Make sure ALL posting reports have been run.
  2. Delete any user names ending in "|": use Ctrl+R &0=031.0 delete"031"
  3. Make all passwords strong: 6 characters, upper and lower case, digit.
  4. Run a custom utility to save custom data fields into the W screen if any data needs to be moved in the standard SQL data model.
  5. Check that all bank ledgers have an ending closeout entry. If the most recent Period Total is non-zero it works.

Installation / Setup Procedures

  1. Install ABCSoftware.Server.Core - which (optionally) includes ABC PostgreSQL. For security, the master password for Server Core / PostgreSQL should be unique to this customer's system. You'll need to enter the password in the PostgreSQL installer and again in the super user credentials.

  2. (Optional) Install pgAdmin.

  3. Open a browser to http://localhost:5016 and register a new user. The first user is automatically granted System Administrator privileges. The user name should be one that the customer is not already using in their ABC database, or it will get overwritten. So avoid generic names like "admin". If the dashboard page doesn't show the sidebar menu, try refreshing the page. If the page is showing access denied, make sure that you are at the root of the webapp without any additional URL path segments. (note that navigating to the root will auto redirect you to /dashboard)

  4. Stop the Legacy ABC Server (or service) if running.

  5. Make a backup copy of the current Server folder.

  6. If uninstalling ABC Server v15 (highly recommended),

    • Make a working copy of the Server folder. Make sure you can still run the v15 legacy server from the renamed folder (if using the authorization shortcut for this, make sure it is set to run in the renamed folder). This is the server from which you will export the v15 data for import into v16.
    • Uninstall ABC Server 15.
    • Delete the original Server folder.

  7. Create a new Server folder from the LegacyServer folder (found in the ABC Server Core 16 folder).

    • Copy in the customer's netw.exp and custom FMT file.
    • In server.config, set webServerUri key to point to server core address and port. (You can use localhost:5016 to get the server working, but will need to use the server name or IP address to enable clients to access the report server.)

  8. In Server Core, add a Legacy Server using the new server folder created in the last step. Specify the custom FMT file name. Make sure Legacy Server Number is set the same (typically 1) as the setting the server.config file.

Moving the Data to Server.Core

Export from v15

  1. Start the v15 Legacy Server using:
    • The latest version 15 FMT (at least 15.22). If a customer has custom file structures, you must upgrade their custom to at least version 15.22 to make sure there are no conflicts that can't be resolved.
    • The latest Version15\710.crm compiled into your FMT.
    • The latest version 16 DLLs (at least 16.7.6.0).
  2. If upgrading from a version prior to 15, follow the recommended procedures to upgrade the data to version 15
  3. Run 7-10 accepting all default options and choose B)rowser output (other output methods DO NOT WORK). Save a copy of the output for later reference. Repeat this step for each company in the Legacy Server.

Import Data into Server.Core

  1. Open Server.Core in your browser.
  2. Click Settings in the top right corner.
  3. Click Bulk Import.
  4. Make sure the legacy server configuration is selected at the top.
  5. Click the functions from left to right:
    • Create Record
    • Create DB
    • Import Data
    • Migrate
    • Make sure there are no errors, and all migrations have been applied. (Repeat for each company)
  6. If you need to rerun the process because of errors, in Server Core delete the company and its database, and go back to rerun 7-10.
  7. Start the v16 legacy server. In the startup (*) screen, use Ctrl+R gosub82 to import legacy data. Verify number of records imported matches report 7-10. If the company number changed because you rearranged or dropped some, you'll need to rename the Company00x folder to the new number for this step.
  8. Run custom utility, if any, to move saved custom fields from W file into SQL data model.
  9. If multiple companies, for each company use Alt+C to select the company, and repeat the legacy data import.
  10. If there are custom tables to import into SQL(this only applies to SQL, if custom stays in CRAM see below,
    • Stop the legacy server running in Server.Core.
    • In the FMT for the v16 legacy server, change '04212.crm to concatenate a list of custom file tables after the ScreenGosub,"7","71".
    • Start the v15 server, rerun report 7-10, and select the custom tables when it asks for "File characters".
    • Import into Server Core by using Bulk Import but only the "Import" function; skip the others. When done, in the "Database Export" folder for the company, rename the SQL script files to add ".completed" on the end so the unwanted functions in Bulk Import will no longer be highlighted.
    • Stop the v15 server and start the legacy server from Server.Core.
    • For custom file tables that stay in CRAM that 7-10 did not export just use 8-42 and 7-42.
  11. Under Setup > Company > Reports, (Repeat for each company)
    • Get updates
    • Auto add categories
    • Migrate auth
    • Get stylesheets.
  12. Set roles and reports for all users; can set for all categories, but not global all. The customer's admin user should be different from the one we use.
  13. (Remove this step if the problem is resolved.) Manually enter port data ('008), which did not come across.
  14. (Remove this step if the problem is resolved.) Get the 0 file tables (the records come across but not the file tables): In v15 data Alt+Y into 00common.dat, run 8-42 on file 00, whole record, for all ABC_File! records. In v16 run 7-42 to import.
  15. For Client 5, in the company setup in Server Core, enter the Client 5 URL.
  16. Run General Ledger Posting and Balance Verification Report before any new transactions are entered. Repeat for each company. (NOTE: This seems to erroneously report out-of-balance conditions in the oldest G/L period that was open on the G screen. Run a current balance sheet from v15 and v16 and make sure they match.)
  17. Make sure the sales start G/L is set correctly in the # screen so that the 4-18 works correctly. The sales start G/L must have MS in Statement 1.
  18. For Client5 users, update any shortcuts to go to the new Server Core, which will redirect to the Client 5 URL specified for the company.
  19. !ALWAYS! Add an exception to the antivirus for the ABC Software folder.

Troubleshooting

If server core does not auto start when Windows starts, or you cannot start the service manually, run the following command in the installation directory. WebServer.exe --Environment=Development --Console This will attempt to start WebServer in development mode and will output detailed log/error messages. You can shut it down by pressing Ctrl+C

Legacy servers can be managed in the Server Core Settings. Be sure to enter the .fmt file name, as the server core doesn't like bad data. The server.config in each legacy server must have a "webServerUri" key pointing to the server core address and port. If the legacy server has an error obtaining a token check this setting. This address is also used to dispatch reports from Client4, so localhost:5016 will probably not work unless this is a single user system.

Test the postgres server performance with pgbench. Open command prompt and navigate to C:\ABC Software\ABC Postgresql 12\PostgreSQL\bin and run this command pgbench -h localhost -p 4012 -U postgres -i(for initialization). Run the same command again but leave off the -i and add -t 10000 to run a test on 10,000 transactions.

Upgrading from Version 16 prior to 16.7.45

Version 16.7.45 and later require an updated version of ABC PostgreSQL (12.0.8) which includes PostGIS. This extension enables geographic and spatial storage fields and calculations which are used in geocoding addresses.

  1. Open Windows Services and stop the ABC PostgreSQL 12 and ABC Server Core services.
  2. Uninstall ABC PostgreSQL 12.0.1
  3. Install the newest version of Server Core which will also automatically install ABC PostgreSQL 12.0.8

Upgrading from Version 16.7.56 to 16.7.100 couldn't start the service. Error was 42P01: relation "configurations" does not exist

Upgrading from Version 16 prior to 16.7.79

Version 16.7.79 and later changed the way configurations are stored for integrations with other software (mapping, address geo-coding, etc). If you were using any of these features prior to 16.7.79,

  1. Open pgAdmin and run the following query to retrieve your API keys.
SELECT module, namespace, id, value
FROM configurations
WHERE module = 'legacy_configuration'
  1. Enter them in the new configuration interface.
  2. Delete the legacy records from the query above.

Upgrading from Version 16 prior to 16.7.125

Version 16.7.125 changed the way search tables are built and requires re-indexing. After all migrations have been applied to all companies, restart the server and indexing will begin automatically. Re-indexing does consume significant CPU and disk I/O resources on the server so make sure you do it at a time when that won’t matter.

Cash to Accrual Sales Tax Conversion

If the customer was using report 3-39 to calculate sales tax collected for reporting purposes, they will need to switch to using the new 3-5 report.

  • For the first tax reporting period after the upgrade to version 16, use the query below to calculate the accrual portion that needs to be paid for that period. Set the ending_date to the last day of the period to be reported and the prior_ending_deposit_id to the last deposit.id which they used on their last 3-39 report.
  • All future periods will be reported using the 3-5 report.
WITH parameters(ending_date, prior_ending_deposit_id) AS (
	values('2021-08-31'::date, 45440)
), invoice_payments AS (
	SELECT i.id, customer_id, date, total_invoice, d.id AS deposit_id, COALESCE(payment_applied + discount_applied, 0.00) AS payment
	FROM invoice AS i
	LEFT JOIN deposit_detail AS d ON d.invoice_id = i.id,
		parameters
	WHERE date <= ending_date AND discount_earned <> 'o'
	ORDER BY i.id DESC, d.id DESC
), prior_tax_paid AS (
	SELECT id, customer_id, date, total_invoice, SUM(CASE WHEN deposit_id <= prior_ending_deposit_id THEN payment ELSE 0.00 END) AS paid_tax_on
	FROM invoice_payments, parameters
	GROUP BY id, customer_id, date, total_invoice
	ORDER BY id DESC
), unpaid_portions AS (
	SELECT *, ((total_invoice - paid_tax_on) / total_invoice)::numeric(28,6) AS unpaid_portion
	FROM prior_tax_paid
	WHERE total_invoice <> paid_tax_on
	ORDER BY paid_tax_on DESC
), details AS (
	SELECT invoice_detail.* 
	FROM invoice_detail
	JOIN unpaid_portions AS t ON t.id = invoice_detail.id
	WHERE taxable_and_weight ->> 'taxable' IN ('X', 'M')
), tax_collected_detail AS (
	SELECT *, 
		(total_invoice * unpaid_portion)::numeric(28,2) AS prorated_total_invoice,
		(taxable_amount * unpaid_portion)::numeric(28,2) AS prorated_taxable_amount,
		(tax_collected * unpaid_portion)::numeric(28,2) AS prorated_tax_collected
	FROM (
		SELECT unpaid_portions.*, COALESCE(list_price, 0.00) AS taxable_amount, COALESCE(amount,0.00) AS tax_collected
		FROM unpaid_portions
		LEFT JOIN details ON details.id = unpaid_portions.id
		ORDER BY unpaid_portions.id
	) t
)
SELECT *
FROM (
	SELECT id, customer_id, date, total_invoice, paid_tax_on, unpaid_portion, taxable_amount, tax_collected,
		prorated_total_invoice, prorated_taxable_amount, prorated_tax_collected
	FROM tax_collected_detail

	UNION

	SELECT NULL::bigint AS id, 'Totals as of deposit_id: ' || prior_ending_deposit_id AS customer_id, ending_date, totals.*
	FROM (
		SELECT 
			SUM(total_invoice) AS total_invoice, SUM(paid_tax_on) AS paid_tax_on, SUM(NULL::numeric(28,2)) AS unpaid_portion, 
			SUM(taxable_amount) AS taxable_amount, SUM(tax_collected) AS tax_collected, SUM(prorated_total_invoice) AS prorated_total_invoice,
			SUM(prorated_taxable_amount) AS prorated_taxable_amount, SUM(prorated_tax_collected) AS prorated_tax_collected
		FROM tax_collected_detail
	) totals, parameters
) add_total_line
ORDER BY id IS NULL DESC, id

Secure Access

You should set up secure access to the server, even if the customer does not permit access from the web. If you do not, clicking links in reports will always pop up a message asking if you are sure you want to allow it.

See https://github.com/abcsoftware/abc-docs/wiki/Using-SSL-Certificates-in-Server-Core for instructions.

Fix NIA

We have had a few instances where a customer was out of balance in V16. The only way to bring it back to balance was to correct a payment record. The NIA, payment amount, and payment applied, did not add up. Here is the SQL query to update any records like this:

WITH details AS (
	SELECT h.id, amount, amount_not_applied, total_discount, gl_amount, SUM(payment_applied) AS payment_applied, SUM(discount_applied) AS discount_applied
	FROM deposit h
	LEFT JOIN deposit_detail d ON d.id = h.id
	GROUP BY h.id, amount, amount_not_applied, total_discount, gl_amount
)
UPDATE deposit 
SET amount_not_applied = d.amount - d.gl_amount - d.payment_applied
FROM (SELECT * FROM details WHERE amount - gl_amount - payment_applied <> amount_not_applied)d
WHERE deposit.id = d.id