0 comments on “Accounting: Monthly History Data”

Accounting: Monthly History Data

Posted by lcleveland in Architecture, General, Store Locator Plus®

Goal: Create a persistent accounting information data store using the built-in WordPress Multisite database surfaces.

The table should follow some standard rules that will allow us to implement CRUD operations via REST endpoints in the MySLP Dashboard plugin.

Tasks

  • One time task “Create Or Update The Database” when the MySLP Dashboard version indicates a change.
  • Create a Monthly Ledger User Interface to allow for manual CRUD operations on the slp_accounting_monthly_history table.

Monthly History Data : Environment

Environment

All accounting history data will reside in tables for the main WordPress super admin account.

The Main WordPress Super Admin account site ID is stored in the PHP constant SITE_ID_CURRENT_SITE and should be 1.
The database table prefix should come up as wp_ unlike user accounts that start with wp_<user_site_id>.

slp_accounting_monthly_history Table

The table should be named $wpdb->prefix . “slp_accounting_monthly_history” resulting in a table name of wp_slp_accounting_history.

slp_accounting_monthly_history Structure

  • ID – a unique ID to allow for explicit record I/O for CRUD operations.
  • KEY – a short string indicating the type of monthly data for example:
    • “SAAS_REVENUE”
    • “PLUGIN_REVENUE”
  • AMOUNT -an integer representing the numeric value for the key for example:
    • 3500 which may represent “35.00” or 35 dollars in our UI surfaces
  • DATE – a date and time for they entry representing the date this value represents NOT when the data was created or modified
  • YEAR – the 4-digit year represented by DATE
  • MONTH – the 2-digit month represented by DATE
  • DAY – the 2-digit day represented by DATE

slp_accounting_monthly_history Example Data

  • ID | KEY | AMOUNT | DATE | YEAR | MONTH | DAY
  • 1 | SAAS_REVENUE | 299000 | 2026-03-01 00:00:00 | 2026 | 03 | 31
  • 2 | SAAS_REVENUE | 302000 | 2026-04-01 00:00:00 | 2026 | 04 | 30
  • 3 | SAAS_REVENUE | 303000 | 2026-05-01 00:00:00 | 2026 | 05 | 31

slp_accounting_monthly_history Data Use Case

This Accounting Monthly History Data interface will run various monthly cron jobs to collect data and store it in this table.
We will use this data to product things like monthly revenue graphs for the SaaS Account Overview dashboard.
The graph will show SaaS Revenue from active accounts on a monthly basis.

We will do the same providing a manual process to add other revenue such as WordPress plugin revenue under “PLUGIN_REVENUE” keys.

We will allow for users to change the Line Chart revenue graph to show “monthly” or “yearly” graphs.
Future data keys may warrant daily graphs.

Task: Create Or Update The Database

When the MySLP Dashboard module version (plugin version) changes, run a database “create or update” hook to create the table or modify it as needed.

Copy the design pattern in the Store Locator Plus plugin in the \SLP_Admin_Activation class.
wp-content/plugins/store-locator-plus/include/module/admin/SLP_Admin_Activation.php
This is fired from \SLPlus::initialize_after_plugins_loaded
When the Store Locator Plus version reported is newer than the installed version for the plugin (see version_compare( $this->installed_version, SLPLUS_VERSION, ‘<‘ )) , the MySLP Dashboard should follow that pattern.

Task: Monthly Ledger User Interface

Admin Menu

  • Add a submenu with the label “Monthly Ledger” under the “Accounting” admin sidebar menu we created in \MySLP::create_network_admin_menu.
		$this->menu_hooks[ MYSLP_ACCOUNTING_MENU_SLUG ] =
			add_menu_page( __( 'Accounting', 'myslp' ),
				__( 'Accounting', 'myslp' ),
				'manage_network_options',
				MYSLP_ACCOUNTING_MENU_SLUG,
				array( $this, 'render_accounting_page' ),
				SLPlus::menu_icon,
				1.50
			);

Initial User Interface

  • Add a new My MySLP_Account_MonthlyLedger PHP React loader and helper class.
    Follow the design pattern of \MySLP_Accounting in wp-content/plugins/myslp-dashboard/include/accounting/MySLP_Accounting.php
    Place it in the same directory as MySLP_Accounting.php as a sibling.
  • Create a new React component that allows basic CRUD operations on the slp_accounting_monthly_history Table.
    Attach it to the MySLP_Account_MonthlyLedger PHP React loader.
    Follow the UI design pattern of the AccountingPanel React component at wp-content/plugins/myslp-dashboard/src/accounting/accounting.tsx
    Use a MUI X DataGridPro component as the primary table interface.
    Allow for pagination, starting with a default page length of 25 records.
    Sort the records by DATE descending on initial load so we see newer records at the top.
    Provide an add record interface where a user can enter the KEY, AMOUNT, DATE.
    Provide an inline edit record interface on the DataGridPro table to allow users to click on they KEY, AMOUNT, or DATE field on an existing record and change it.
    Provide a delete option for each record.

Data Interface

Use REST for all CRUD operations and to fetch the initial data set.

When records are added the backend data interface should:

  • Ensure all keys entered are trimmed (no leading/trailing spaces) and are shifted to uppercase.
  • AMOUNT is stored only as an integer.
  • DATE field needs to allow for flexible input converting text input into a best guess for the date to be stored as a date-time entry in the database. Examples:
    • “05/2026” is May 2026 which should be recorded as the date time 2026-05-01 00:00:00
    • “05/01/2026” is May 1st 2026 which should be recorded as the date time 2026-05-01 00:00:00
    • “May 2026” is May 2026 which should be recorded as the date time 2026-05-01 00:00:00
    • Allow for various separators such as / or – or .
      • 05-01-2026 is the same as 05/01/2026
      • 05.01.2026 is the same as 05/01/2026
    • Use standard Date/Time JavaScript or React libraries for data manipulation.
    • The manipulation can be not the front-end (React/JavaScript) before communicating with the REST endpoint.
  • The backend should do basic sanitation before recording data.