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`);