Topic: Anatomy of adding 1 customer

Analysing the diffs of backups before and after adding 1 Customer resulted in a set of INSERT statements when parsed out and sequences with variable extractions done resulted in the following (Assumed TB_PREF=3_):

SET @Customer  := "PKA Enterprises";
SET @CustShort := "PKA";
SET @Phone     := "28557841";
SET @EMail     := "pka@example.com";
SET @Address   := "Joe Smith Road\nTimbuktoo - Z9HG34";
SET @GST       := "1234578";
SET @Location  := 'DEF';
SET @DefCurr   := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="curr_default" AND `category`="setup.company");
SET @Credit    := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="default_credit_limit" AND `category`="glsetup.customer");
SET @DfPayAc   := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="default_prompt_payment_act" AND `category`="glsetup.sales");
SET @PrPayAc   := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="default_sales_discount_act" AND `category`="glsetup.sales");
SET @DrsAc     := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="debtors_act" AND `category`="glsetup.sales");

INSERT INTO `3_debtors_master` VALUES
(NULL, @Customer, @CustShort, @Address, @GST, @DefCurr, '1', '0', '0', '1', '4', '0', '0', @Credit, '', '0');
SET @DrNum := (SELECT LAST_INSERT_ID());

INSERT INTO `3_cust_branch` VALUES
(NULL, @DrNum, @Customer, @CustShort, @Address, '1', '1', @Location, '1', '', @PrPayAc, @DrsAc, @DfPayAc, '1', @Address, '0', '', NULL, '0');
SET @BranchID := (SELECT LAST_INSERT_ID());

INSERT INTO `3_crm_persons` VALUES
(NULL, @CustShort, @Customer, NULL, @Address, @Phone, NULL, NULL, @EMail, NULL, '', '0');
SET @PersonID := (SELECT LAST_INSERT_ID());

INSERT INTO `3_crm_contacts` VALUES
(NULL, @PersonID, 'cust_branch', 'general', @BranchID),
(NULL, @PersonID, 'customer', 'general', @DrNum);

Here, first a customer is created and then a branch for the said customer.
Then one Person is created.
This Person is then allotted to both -  customer and cust_branch.

The above can be useful for bulk importing of customers from disparate systems.

Post's attachments

FA243_Add_1_Customer.sql 1.5 kb, 15 downloads since 2018-03-16 

You don't have the permssions to download the attachments of this post.

Re: Anatomy of adding 1 customer

This is useful. I will be writing the customer migration code, soon. Thank you.

Re: Anatomy of adding 1 customer

hi did you write the code? is it available ?