Customer Table Relationships

  • Field `0_crm_contacts`.`entity_id` will be changed from VARCHAR(11) to INT(11) in FA v2.4.
  • The direct relationship between `0_crm_contacts`.`entity_id` and `0_debtors_master`.`debtor_no` is only for `0_suppliers`.`supplier_id` records.
  • Shipping Address is the Billing Address of the Branch for Credit Deliveries/Orders/Invoices and that of the Customer for Cash Sales.

Navigating 0_crm_contacts table

The 0_crm_contacts table performs a many to many relationship between the 0_persons table and the a few entity_id tables such as 0_debtors_master, 0_cust_branch, 0_suppliers. Furthermore, this implementation in FA leverages several non standard constructs in php/mysql code that roughly translates into the following SQL statements: 

-- Select a CustomerCode (for example) from '''debtor_no''' field in the '''0_debtor_master''' table
SET @debtor_entity_id:=600;

-- Get the HQ Person ID
SET @branch_person_id:=(SELECT person_id FROM `0_crm_contacts` 
	WHERE `type`='customer' AND `action`='general' AND person_id=@debtor_entity_id);

-- Get the BranchCodes 
SET @branch_codes:=(SELECT GROUP_CONCAT(branch_code) FROM `0_cust_branch` WHERE debtor_no=@debtor_entity_id);

-- Show the Input Parameters and Extracted Parameters
SELECT @debtor_entity_id AS CustomerCode, @branch_person_id AS HQPerson, @branch_codes AS BranchCodes;


-- HQ Branch Person details
SELECT * FROM `0_crm_persons` WHERE `id` IN (
SELECT person_id FROM `0_crm_contacts` WHERE `type`='cust_branch' AND `action`='general' AND entity_id IN (
        SELECT branch_code FROM `0_cust_branch` WHERE debtor_no=@debtor_entity_id
    ) AND person_id = @branch_person_id
);

-- Non HQ Branch Person Details
SELECT * FROM `0_crm_persons` WHERE `id` IN (
SELECT person_id FROM `0_crm_contacts` WHERE `type`='cust_branch' AND `action`='general' AND entity_id IN (
        SELECT branch_code FROM `0_cust_branch` WHERE debtor_no=@debtor_entity_id
    ) AND person_id <> @branch_person_id
);

To effectively leverage this mechanism, a few indices are in order to speedup the process by over a many times (9000ms down to 3.5ms when queried from among 2500 records): 

ALTER TABLE `0_crm_contacts` ADD INDEX `entity_id` (`entity_id`);
ALTER TABLE `0_crm_contacts` ADD INDEX `person_id` (`person_id`);

Supplier Table Relationships

  • One crm_persons table record is created for each supplier and one for each of their contact persons.
  • Matching entries are made for each such crm_persons record in the crm_contacts table.
  • The 0_suppliers.contact field is no longer used as all contact information now resides in the 0_crm_persons table.
  • Screenshot of the New Supplier form matching the form fields with the table fields where the data is posted.
  • Suppliers Export SQL: 
SELECT * FROM 0_suppliers s 
    LEFT JOIN 0_crm_contacts c ON (s.supplier_id = c.entity_id) AND (c.type='supplier')
    LEFT JOIN 0_crm_persons p ON ( (c.person_id = p.id) /* AND (p.ref = s.supp_ref) */ )
    LEFT JOIN 0_crm_categories USING (`type`, `action`)
    LEFT JOIN 0_payment_terms pt ON (pt.terms_indicator = s.payment_terms)
    LEFT JOIN 0_tax_groups tg ON (tg.id = s.tax_group_id);

A/c Masters and POS Relationships

Stock and Item Relationships

Purchase Item Supplier Relationships

Tax Table Relationships

Manufacturing Workorders Relationships

  • This is only a tentative ERD
  • Requirements are to be taken as defaults and issues are the actuals and hence their relationships are only informative.

Audit Trail Relationships

  • Files that refer to gl_seq field:
    • includes/db/audit_trail_db.inc
    • gl/includes/db/gl_db_trans.inc
    • gl/inquiry/journal_inquiry.php
    • reporting/rep710.php - date range for report refers to stamp (date of change) and not gl_date. Alter it if you want gl_date instead.
  • Notes on Fiscal Year Closing / Opening / Re-opening / Re-Closing
    • Note that the primary key of the audit_trail and bank_trans tables, viz., id, are used (for convenience of single PK) only for CRUD operations and are never used for data lookups as foreign keys anywhere and hence can be re-numbered at will (except that the audit_trail.stamp will get updated due to it's table definition - ON UPDATE CURRENT_TIMESTAMP)
    • Current code updates the audit_trail.gl_seq field for all records on or before the end date of the fiscal year to be (re-)closed and for all dates on or after the begin date of the fiscal year to be re-opened. Hence is there are a large number of previous fiscal years that have not been closed, then, for large data sets, scripts will timeout or memory will get exhausted.
    • Forum Post on speeding up the fiscal year open/close process. Example of closing gl_trans entries. The SQL necessary to display these entries is: $sql = "SELECT * FROM ".TB_PREF".gl_trans WHERE `type`=0 AND memo_ IN ("._('Open Balance').", "._('Closing Year').")";
    • All audit records except latest one should have gl_seq set to NULL to avoid need for subqueries (absent in MySQL 3) all over the code - see code and comments in function add_audit_trail() in includes/db/audit_trail_db.inc file.
  • Notes on tables related to fiscal year deletion
    • A backup is attempted to be taken before fiscal year deletion and for large datasets it is advised to be done manually commenting out the appropriate line in admin/db/fiscalyears_db.inc's function delete_this_fiscalyear(): db_backup($db_connections[$_SESSION["wa_current_user"]->company], 'Security backup before Fiscal Year Removal');
    • Related here are attachments in the company/#/attachments/ folder pertaining to the fiscal year to be deleted which will need to be deleted and deleted from the attachments and comments table as well.