SET @currency=(SELECT MID(`value`,1,3) AS currency FROM `2_sys_prefs` WHERE category='setup.company' AND `name`='curr_default'); # SGD
SET @debtorsact=(SELECT (`value`+0) AS debtors_act FROM `2_sys_prefs` WHERE category='glsetup.sales' AND `name`='debtors_act'); # 1200
SET @paymentdiscact=(SELECT (`value`+0) AS debtors_act FROM `2_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_prompt_payment_act'); # 4500
SET @salesdiscact=(SELECT (`value`+0) AS debtors_act FROM `2_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_sales_discount_act'); # 4510
SET @ref='Jack Brown';
SET @name='Jack David';
SET @name2='Brown';
SET @address='123 Timbuktoo Street, Johannesburg, South Africa';
SET @phone='94568745';
SET @email='jack@dbrown.com';
SET @CitizenID='H5463546T';
SET @Gender='Male';
INSERT INTO 2_crm_persons (ref, `name`, name2, address, phone, email, notes) VALUES (@ref, @name, @name2, @address, @phone, @email, CONCAT(@CitizenID,' ',@Gender));
SET @personid=LAST_INSERT_ID();
INSERT INTO `2_debtors_master` (debtor_no, `name`, debtor_ref, address, tax_id, curr_code, credit_status, payment_terms, credit_limit, notes)
SELECT
NULL AS debtor_no,
CONCAT(`name`,' ',name2) AS `name`,
ref AS debtor_ref,
address,
'' AS tax_id,
@currency AS curr_code,
1 AS credit_status,
4 AS payment_terms,
0 AS credit_limit,
'' AS notes
FROM 2_crm_persons
WHERE id = @personid;
SET @debtorno=LAST_INSERT_ID();
INSERT INTO `2_cust_branch` (branch_code, debtor_no, br_name, branch_ref, br_address, area, salesman, contact_name, default_location,
tax_group_id, sales_account, sales_discount_account, receivables_account, payment_discount_account, br_post_address, notes)
SELECT
NULL AS branch_code,
@debtorno AS debtor_no,
`name` AS br_name,
debtor_ref AS branch_ref,
address AS br_address,
1 AS area,
1 AS salesman,
'' AS contact_name,
'DEF' AS default_location,
1 AS tax_group_id,
'' AS sales_account,
@salesdiscact AS sales_discount_account,
@debtorsact AS receivables_account,
@paymentdiscact AS payment_discount_account,
address AS br_post_address,
notes
FROM `2_debtors_master`
WHERE debtor_no = @debtorno;
SET @brcode=LAST_INSERT_ID();
INSERT INTO `2_crm_contacts` (id, person_id, `type`, `action`, entity_id) VALUES
(NULL, @personid, 'cust_branch', 'general', @brcode)
, (NULL, @personid, 'customer', 'general', @debtorno);
.