CREATE TABLE `cust_list` (
  `CustomerID` INT(11) NOT NULL AUTO_INCREMENT,
  `CustName` VARCHAR(60) NOT NULL DEFAULT '',
  `BranchName` VARCHAR(60) NOT NULL DEFAULT '',
  `Contact` VARCHAR(60) NOT NULL DEFAULT '',
  `ShippingAddress` TINYTEXT NOT NULL,
  `BillingAddress` TINYTEXT NOT NULL,
  `phone` VARCHAR(30) NOT NULL DEFAULT '',
  `phone2` VARCHAR(30) NOT NULL DEFAULT '',
  `fax` VARCHAR(30) NOT NULL DEFAULT '',
  `email` VARCHAR(100) NOT NULL DEFAULT '',
  `sales_account`  VARCHAR(15) NOT NULL DEFAULT '',
  `payment_terms` INT(11) NOT NULL DEFAULT '1',
  `credit_limit` FLOAT NOT NULL DEFAULT '0',
  `TaxNum` VARCHAR(55) NOT NULL DEFAULT '',
  `TaxExempt`  TINYINT(1) NOT NULL DEFAULT '0',
  `Pricing` INT(11) NOT NULL DEFAULT '1',
  `NeedPO` TINYINT(1) NOT NULL DEFAULT '0',
  `IsMB` TINYINT(1) NOT NULL DEFAULT '0',
  `debtor_no` INT(11) DEFAULT NULL,
  `debtor_ref` VARCHAR(30) DEFAULT NULL,
  `branch_code` INT(11) DEFAULT NULL,
  `branch_ref` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`CustomerID`)
) ENGINE=INNODB;


# Importing Persons into the '''#_crm_persons''' table
INSERT INTO `0_crm_persons` 
SELECT 
    `debtor_no` AS `id`
  , `debtor_ref` AS `ref`
  , IF(LENGTH(`Contact`) > 0, `Contact`, `CustName`) AS `name`
  , '' AS `name2`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `phone`
  , `phone2`
  , `fax`
  , `email`
  , '' AS lang
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0  AS inactive
FROM `cust_list` WHERE NOT IsMB

UNION ALL

(SELECT 
    `debtor_no` AS `id`
  , `debtor_ref` AS `ref`
  , IF(LENGTH(`Contact`) > 0, `Contact`, `CustName`) AS `name`
  , '' AS `name2`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `phone`
  , `phone2`
  , `fax`
  , `email`
  , '' AS lang
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0  AS inactive
FROM `cust_list` WHERE IsMB GROUP BY debtor_no)

UNION ALL

SELECT 
    `branch_code` AS `id`
  , `branch_ref` AS `ref`
  , IF(LENGTH(`Contact`) > 0, `Contact`, `BranchName`) AS `name`
  , '' AS `name2`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `phone`
  , `phone2`
  , `fax`
  , `email`
  , '' AS lang
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0  AS inactive
FROM `cust_list` WHERE IsMB;
#Importing into '''#_debtors_master''' table

SET @currency=(SELECT MID(`value`,1,3) AS currency FROM `0_sys_prefs` WHERE category='setup.company' AND `name`='curr_default');
INSERT INTO `0_debtors_master`
(SELECT 
    `debtor_no`
  , `CustName` AS `name`
  , `debtor_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `TaxNum` AS `tax_id`
  , @currency AS `curr_code`
  , `Pricing` AS `sales_type`
  , 0 AS `dimension_id`
  , 0 AS `dimension2_id`
  , 1 AS `credit_status`
  , `payment_terms`
  , 0 AS `discount`
  , 0 AS `pymt_discount`
  , `credit_limit`
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0 AS inactive
FROM cust_list WHERE NOT IsMB)

UNION ALL

(SELECT 
    `debtor_no`
  , `CustName` AS `name`
  , `debtor_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `TaxNum` AS `tax_id`
  , @currency AS `curr_code`
  , `Pricing` AS `sales_type`
  , 0 AS `dimension_id`
  , 0 AS `dimension2_id`
  , 1 AS `credit_status`
  , `payment_terms`
  , 0 AS `discount`
  , 0 AS `pymt_discount`
  , `credit_limit`
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0 AS inactive
FROM cust_list WHERE IsMB GROUP BY debtor_no);
# Import the branches into '''#_cust_branch''' table

SET @salesdiscact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_sales_discount_act');
SET @debtorsact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='debtors_act');
SET @paymentdiscact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_prompt_payment_act');

INSERT INTO `0_cust_branch`
(SELECT 
    `debtor_no` AS `branch_code`
  , `debtor_no`
  , `CustName` AS `BranchName`
  , `debtor_ref` AS `branch_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `br_address`
  , 1 AS `area`
  , 1 AS `salesman`
  , 'DEF' AS `default_location`
  , `TaxExempt`+1 AS `tax_group_id`
  , `sales_account`
  , @salesdiscact AS `sales_discount_account`
  , @debtorsact AS receivables_account
  , @paymentdiscact AS `payment_discount_account`
  , 1 AS `default_ship_via`
  , REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), "  ", " ") AS `br_post_address`
  , 0 AS `group_no`
  , '' AS `notes`
  , '' AS `bank_account`
  , 0 AS `inactive`
FROM `cust_list` WHERE NOT `IsMB`)

UNION ALL

(SELECT 
    `debtor_no` AS `branch_code`
  , `debtor_no`
  , `CustName` AS `BranchName`
  , `debtor_ref` AS `branch_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `br_address`
  , 1 AS `area`
  , 1 AS `salesman`
  , 'DEF' AS `default_location`
  , `TaxExempt`+1 AS `tax_group_id`
  , `sales_account`
  , @salesdiscact AS `sales_discount_account`
  , @debtorsact AS receivables_account
  , @paymentdiscact AS `payment_discount_account`
  , 1 AS `default_ship_via`
  , REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), "  ", " ") AS `br_post_address`
  , 0 AS `group_no`
  , '' AS `notes`
  , '' AS `bank_account`
  , 0 AS `inactive`
FROM `cust_list` WHERE `IsMB` GROUP BY `debtor_no`)

UNION ALL

(SELECT 
    `branch_code`
  , `debtor_no`
  , `BranchName`
  , `branch_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `br_address`
  , 1 AS `area`
  , 1 AS `salesman`
  , 'DEF' AS `default_location`
  , `TaxExempt`+1 AS `tax_group_id`
  , `sales_account`
  , @salesdiscact AS `sales_discount_account`
  , @debtorsact AS receivables_account
  , @paymentdiscact AS `payment_discount_account`
  , 1 AS `default_ship_via`
  , REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), "  ", " ") AS `br_post_address`
  , 0 AS `group_no`
  , '' AS `notes`
  , '' AS `bank_account`
  , 0 AS `inactive`
FROM `cust_list` WHERE `IsMB`);
# Import the persons mappings into '''#_contacts''' table

INSERT INTO `0_crm_contacts`
SELECT * FROM (
SELECT 
    NULL AS id
  , `debtor_no` AS person_id
  , 'customer' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE `IsMB`

UNION ALL 

SELECT 
    NULL AS id
  , `debtor_no` AS person_id
  , 'cust_branch' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE `IsMB`

UNION ALL 

(SELECT 
    NULL AS id
  , `debtor_no` AS person_id
  , 'customer' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB` GROUP BY debtor_no)


UNION ALL

(SELECT 
    NULL AS id
  , `debtor_no` AS person_id
  , 'cust_branch' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB` GROUP BY debtor_no)


UNION ALL

SELECT 
    NULL AS id
  , `branch_code` AS person_id
  , 'cust_branch' AS `type`
  , 'general' AS `action`
  , `branch_code` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB`
) a ORDER BY entity_id, person_id, `type` DESC;