Skip to content

Payroll and invoicing

More notes are in ‘payroll-adjustments’ file.

Invoicing

billable

All shifts are by default ‘billable’. Some shifts that are cancelled may also be ‘billable’.

Initial thought is to have a ‘is_billable’ flag in shift table, and, when cancelling, determine whether the billable flag should be set on or off. ‘Billable’ flag would default to ‘on’. Q: should this be a tri-state boolean of null/true/false? Or simply true/false? Unsure if there’s ever a need to have an indetermined (null) state for is_billable.

Initial migration should include the notion of setting all ‘cancelled’ shifts to be ‘is_billable=false’.

geo/rate info

Hospitals each need a rate for invoice calculation. The prod system has a separate ‘data’ schema with table ‘ hospital_region_geo_mapping’. We need to either be able to query that directly, or import that data to the public schema.

The ‘core_id’ in data.hospital_region_geo_mapping is the public.locations.core_id.

There are two options here - grab the shifts.site_id and find the specific site record, then use its site.location_id, or use the shifts.location_id directly. The ‘shifts.location_id’ is set to the site’s location_id, and… generally, should not be separated/split.
There’s a potential that they could split, in that a site moves in to a different ‘geo/location’ area, but the original ‘geo/location’ still should apply to the shift at the point in time the shift’s location was set.

To query from laravel for that, we might need to have a separate connection/credentials to have access to the ‘data’ schema - perhaps ‘read only’?

\DB::select('select * from data.hospital_region_geo_mapping');

This looks to work using the local dev credentials and gives us access to the data schema, but may not work in prod, and… won’t work in PR/staging environments.

The ‘hospital_region_geo_mapping’ table could be (re)created in cats-area schema (‘public’) then copy from data. to public. via a copy/button step? This would allow for defined ‘update’ process on prod, but allow for seeding/testing on dev/local/test/PR/staging.

Can migrations create/write to multiple schemas?

7/28/21

added ‘inRangeIncludeBillableCancelled’ method

  • inRange() will give back all non-cancelled shifts
  • inRangeIncludeAllCancelled() will include all shifts in range and all cancelled shifts
  • inRangeIncludeBillableCancelled() will include all shifts in range and only cancelled shifts marked billable

Added test to demonstrate.

7/30/21

‘geo rate’ table. on prod, this will need to connect to ‘data.hospital_region_geo_mapping’. For non prod/test/local/dev we’d need to have the same schema.table available, so… a bit of fancy footwork may be needed to fake this.

the initial call will be in RateService::~~getHospitalGeoRateNoDate~~– - the noDate is to explicitly call out that this is not taking in to account any effective date.

Will need to show ‘is_billable’ flag on

  • schedule view of shift info,
  • timesheet area,
  • and admin view of shift

May need to filter in/out ‘billable/cancelled’ on calendar/schedule area as well (currently ‘cancelled’ is excluded/included only).

Multiple calls to get information exist:

RateService::~~getHospitalGeoRateNoDate~~ RateService::~~getAllGeoRateNoDate~~ RateService::~~getShiftGeoRateNoDate~~

Seeder will call

RateService::~~insertGeoRateNoDate~~

Invoice process will use RateService::~~getShiftGeoRateNoDate~~ because we need to grab the rate value for the location as it was assigned for the shift at that time (location may change - unlikely, but it might happen, and this has been discussed with Meg/IV before).

Note:
Even with this as a base, there’s currently not going to be a way to do any manual verification with any confidence, because there’s no way to view location (geo) current rates. Pulling from a different table (managed by sharepoint/etc) doesn’t address a visibility issue for confirmation. Planning a small CRUD for location (geo) screen.

8/2/21

The ‘is_billable’ concept was reverted to a tri-state - null, false or true. TimesheetService::getEntriesForPeriod() call was updated to pull entries that are not cancelled, or… if they are cancelled, include the entry if it is explicitly marked as ‘is_billable=true’.

And… when a shift is marked cancelled, a ShiftCanceled event is thrown. The listener was deleting the related timesheet entry. That was modified to only delete the entry if the shift is not explicitly marked as is_billable=true.

These changes were necessary at least for the time being as a transitionary step. Existing shift isn’t marked as billable or not, and existing code doesn’t make any explicit setting.

The ‘set cancel’ shift process was modified last week to be able to take an is_billable flag, but could deal with a null value by just skipping over that processing. If it’s true or false, the value is set accordingly. This will allow a UI to be hooked up relatively easily in the near future.

An existing test [1] was modified to demonstrate the effect of canceling with is_billable value of null, true and false, explicitly, via a dataProvider.

[1] TimesheetControllerTest::testTimesheetCancelRemovesFromTimesheetPeriod

8/4/21

CATS-378 - getBillableEntries endpoints converted existing internal getEntriesForTimePeriod to getAllEntriesForTimePeriod, and removed the ‘canceled/billable’ checking inside that call.

Created new getEntriesForTimePeriod that wraps getAllEntriesForTimePeriod, then does the cancel/billable checking, and then added getEntriesForTimePeriodAndCustomer separate internal call. Modified existing app calls and test calls to point to the getEntriesForTimePeriod wrapper method and tests all passed.

Added two new AJAX endpoints: timesheet_get_billable_entries and timesheet_get_billable_entries_for_customer

Added tests to demonstrate/validate behaviour.

invoice

An invoice is a collection of charges for a particular company for a collection of billable shifts. Each shift will have one or more line associated with it.

An invoice helper class will take create an invoice object from provided timesheet entry/shift data.

An invoice helper class will transform invoice objects for viewing/debugging, csv export and quickbooks posting.

aug 23, 2021

Invoice recap thus far

QB SDK for PHP offers (seemingly) good class definitions, and these were used in the development of defining/posting invoices to quickbooks. Two issues came up:

  1. phpstan

static analysis and QB SDK don’t go together. the bulk of their code is meant to be backwards compatible with earlier PHP versions, and their use of @var definitions points to XML name/paths, not PHP namespaces. Example

class IPPItem
    extends IPPIntuitEntity {

     * @xmlType element
     * @xmlNamespace http://schema.intuit.com/finance/v3
     * @xmlMinOccurs 0
     * @xmlName IncomeAccountRef
     * @var com\intuit\schema\finance\v3\IPPReferenceType
     */
    public $IncomeAccountRef;

phpstan will concat the namespace of a file with the com\intuit\schema stuff and complain that it has an ‘unknown class’.

Short term solution is to just add in many @phpstan-ignore-next-line comments - this may be ‘good enough’ for now. The ‘code completion’ experience - $invoice->CustomerRef having the IDE know what type ‘CustomerRef’ was useful, but static analysis breaks.

  1. Second issue

The QB data service can post in XML or JSON, and each have different serializers. The “add service item” - if we encouter a new invoice line item that doesn’t exist in our known collection, we need to create and add a service item in quickbooks - the ->Add() method for this seems to only work with JSON serialized calls. XML serialized calls triggered extremely hard to track down errors - something on the intuit side can’t parse something, and the error message back is useless. Switching to JSON for posting that ‘just worked’. HOWEVER… other calls (adding invoice) did NOT like JSON, but DID work with XML serialization. So… the process will swap the dataService with ->useXml() or ->useJson() calls as needed. This can probably be improved or patched more but it’s working for now.

The ->Add() method to add an invoice didn’t initially work in JSON, and I tracked down the problem to … just really poor/bad code internal to a JSONSerializer file. I’ve submitted a bug and patch back to QB SDK package:

https://github.com/intuit/QuickBooks-V3-PHP-SDK/pull/420

but I doubt it will get taken in any time soon (or perhaps not at all).

NOTE: mods were made to remove useJSON() call altogether - only noticed after starting fresh against in a PR scenario with QB with no ‘income account’ and trying to make first service item there.

QB INCOME ACCOUNT

We need an income account to associate dynamically created service items against. Config::get(‘quickbooks.income-account-name’) is used to pull a configuration from the quickbooks config, and that references

env('QB_INCOME_ACCOUNT_NAME', 'income_acct'),

Overview

The bulk of the work is done in InvoiceService and QBServiceTrait. The trait holds calls to the QB SDK via the QB dataService, and is used in InvoiceService and CustomerService. The CustomerService call is primarily for dev systems - it will create a customer in QB if one doesn’t exist, but that situation basically never exists in production, only potentially dev systems.

Noted - the ‘create service item’ call - posting to QB to add an item - will take any name with a : in it and create an item and subitem, which is not what was wanted. A conversion method will convert : in names to - if they exist. This caused some confusion with something like “Exams: promoted” and “Exams: weekend”. Those items are not in production, and I removed these and similar ‘positions’ from the setup process for testing/dev.

aug 25, 2021

‘quickbooks last updated at’ was added to each CATS invoice record, along with the quickbooks_id and quickbooks_doc_number.

Each APi calls takes 4-5 seconds. To deal with this, we’ll run these as queued jobs. The ‘quickbooks_last_updated_at’ field will serve as an indicator of the last successful update to QB API call for that invoice.

The ‘serviceItems’ need to be prepped first - any missing ones should be scanned for and then created if missing. If there’s a problem making one, we will log to sentry.

The initial ‘bulk post’ will check if any customer or customer QB IDs are missing, and it will prevent the system from queueing up any publish jobs.

PublishInvoice as a job will be queued up for each invoice, and will update the CAYS invoice with quickbooks_last_updated_at, quickbooks_id and quickbooks_doc_number. Shifts can reference their ‘invoice_id’ to the CATS invoices table to get the pieces of quickbooks data needed.

New ‘env’ var QB_VIEW_URL for direct link to QB view of invoice.

SETUP NEEDS

QB_VIEW_URL env QB_INCOME_ACCOUNT_NAME env

12/6/21

Updated docs to strikethrough references to NoDate calls in RateService. In support of ‘date aware’ geo rates, NoDate method calls were initially deprecated, and now being removed. Replacement calls should be At (with a date supplied) or Now

Example:

RateService::getGeoRateNoDate(Location $location)
is now 
RateService::getGeoRateAt(Location $location, Carbon $effectiveDate)

Tests and docs should all have been updated.