-- Fixed for MariaDB 12
-- Key changes:
-- 1) Proper DELIMITER handling around stored procedures.
-- 2) Reset DELIMITER back to ';' before running normal SQL (CALL/DROP/CREATE TABLE/etc.).
-- 3) Use 'CREATE TABLE ... AS SELECT ...' form consistently.
-- 4) Minor robustness tweaks: drop temp tables if exist using IF EXISTS where applicable.
-- 5) DML-only sections are wrapped in transactions. DDL in MariaDB still does implicit commit.

-- PENTING!!! BACA Readme-Konversi-v4-v8.txt

SET @aspersediaandivisi='107.999';
-- perbaikan db V4 utk kode-kode yang awalannya 0 akan ditambahkan awalan huruf
START TRANSACTION;
UPDATE dbv4.customergroup SET id=CONCAT('C',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.customer SET id=CONCAT('C',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.suppliergroup SET id=CONCAT('S',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.supplier SET id=CONCAT('S',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.salesmangroup SET id=CONCAT('P',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.salesman SET id=CONCAT('P',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.servicedoergroup SET id=CONCAT('T',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.servicedoer SET id=CONCAT('T',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.employeegroup SET id=CONCAT('E',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.employee SET id=CONCAT('E',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.productgroup SET id=CONCAT('P',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.product SET id=CONCAT('P',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.product SET salesdisc='0' WHERE salesdisc='';
UPDATE dbv4.product SET salesdisc=REPLACE(salesdisc,',', '.');
UPDATE dbv4.product SET salesdisc=REPLACE(SUBSTRING(salesdisc, 1, INSTR(salesdisc, '+')-1), '%', '');
UPDATE dbv4.product SET salesdisc='0'
WHERE NOT salesdisc REGEXP '^[+-]?[0-9]*([0-9]\.|[0-9]|\.[0-9])[0-9]*(e[+-]?[0-9]+)?$';
UPDATE dbv4.division SET id=CONCAT('B',id) WHERE SUBSTRING(id, 1, 1)='0';
UPDATE dbv4.departement SET id=CONCAT('W',id) WHERE SUBSTRING(id, 1, 1)='0';
SELECT id INTO @defaultsalesmanid FROM dbv4.salesman LIMIT 0, 1;
UPDATE dbv4.customer set defsalesmanid=@defaultsalesmanid where defsalesmanid not in (select id from dbv4.salesman);
UPDATE dbv4.customer set shipto='' where shipto is null;
UPDATE dbv4.units set description=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(unit, ' ', ''),'0',''),'1',''),'2',''),
'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','');
COMMIT;

ALTER TABLE dbv8.company DROP FOREIGN KEY `company_ibfk_1`;

START TRANSACTION;
SELECT id INTO @defaultsalesmanid FROM dbv4.salesman LIMIT 0, 1;
SELECT id INTO @defaultcustomerid FROM dbv4.customer LIMIT 0, 1;
SELECT id INTO @defaultservicedoerid FROM dbv4.servicedoer LIMIT 0, 1;
SELECT id INTO @defaultsupplierid FROM dbv4.supplier LIMIT 0, 1;
SELECT id INTO @defaultemployeeid FROM dbv4.employee LIMIT 0, 1;
SELECT id INTO @defaultdivisionid FROM dbv4.division LIMIT 0, 1;
SELECT id INTO @defaultdepartementid FROM dbv4.departement LIMIT 0, 1;
SELECT id INTO @defaultshipmentid FROM dbv4.shipments LIMIT 0, 1;
SELECT id INTO @defaultcurrencyid FROM dbv4.currencies LIMIT 0, 1;
SELECT id INTO @defaultpaymenttypeid FROM dbv4.paymenttype LIMIT 0, 1;


INSERT INTO dbv8.religions (id, name)
SELECT id, name FROM dbv4.religions ORDER BY id;

INSERT INTO dbv8.shipments (id, name, description)
SELECT id, name, description FROM dbv4.shipments ORDER BY id;

INSERT INTO dbv8.account (id,name,accountgroup,accountcategory,accounttype,isgroup,usercreate,useredit)
SELECT id,name,accountgroup,accountcategory,accounttype,isgroup,usercreate,useredit FROM
(
  SELECT id,name,parent AS accountgroup,accountcategory,0 AS accounttype,1 AS isgroup, usercreate,useredit
  FROM dbv4.accountgroup
  UNION
  SELECT id,name,accountgroup,accountcategory,accounttype,0 AS isgroup, usercreate,useredit
  FROM dbv4.account
) AS a
ORDER BY id;
COMMIT;

-- =============================
-- Procedure 1: updateGroupAccount
-- =============================
DROP PROCEDURE IF EXISTS updateGroupAccount;

-- masuk ke mode delimiter hanya untuk definisi prosedur
DELIMITER ;;
CREATE PROCEDURE updateGroupAccount()
BEGIN
  DECLARE cursor_id CHAR(30);
  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor_Group CURSOR FOR
    SELECT id FROM dbv8.account2 WHERE isgroup=1 ORDER BY id DESC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cursor_Group;
  read_loop: LOOP
    FETCH cursor_Group INTO cursor_id;
    IF done THEN
      LEAVE read_loop;
    END IF;
    UPDATE dbv8.account
    SET accounttype = (
      SELECT accounttype
      FROM (SELECT * FROM dbv8.account) AS a
      WHERE accountgroup = cursor_id
      LIMIT 1
    )
    WHERE id = cursor_id;
  END LOOP;
  CLOSE cursor_Group;
END;;
DELIMITER ;

-- kembali ke perintah biasa
CREATE TABLE dbv8.account2 AS
SELECT * FROM dbv8.account;

CALL updateGroupAccount();

DROP PROCEDURE IF EXISTS updateGroupAccount;


-- =============================
-- Procedure 2: updateGroupAccount2
-- =============================
DROP PROCEDURE IF EXISTS updateGroupAccount2;

-- definisikan prosedur dulu
DELIMITER ;;
CREATE PROCEDURE updateGroupAccount2()
BEGIN
  DECLARE cursor_id CHAR(30);
  DECLARE cursor_group VARCHAR(100);
  DECLARE done INT DEFAULT FALSE;

  DECLARE cursor_Group CURSOR FOR
    SELECT id, accountgroup
    FROM dbv8.account2
    WHERE isgroup = 1 AND accounttype IS NULL
    ORDER BY id DESC;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cursor_Group;
  read_loop: LOOP
    FETCH cursor_Group INTO cursor_id, cursor_group;
    IF done THEN
      LEAVE read_loop;
    END IF;

    UPDATE dbv8.account
    SET accounttype = (
      SELECT accounttype
      FROM (SELECT * FROM dbv8.account) AS a
      WHERE accountgroup = cursor_group
      LIMIT 1
    )
    WHERE id = cursor_id;
  END LOOP;

  CLOSE cursor_Group;
END;;
DELIMITER ;

-- baru eksekusi perintah biasa
CREATE TABLE dbv8.account2 AS
SELECT * FROM dbv8.account;

CALL updateGroupAccount2();

DROP PROCEDURE IF EXISTS updateGroupAccount2;

-- =============================
-- Lanjutkan migrasi
-- =============================
START TRANSACTION;
UPDATE dbv8.company SET inventorytransferaccount=@aspersediaandivisi;
UPDATE dbv8.company AS a, dbv4.company AS b
SET a.name=b.name,a.cityname=b.city,a.address=b.address,a.country=b.country,a.postcode=b.postcode,
    a.telephone=b.telephone,a.faximile=b.faximile,a.email=b.email,a.website=b.website,a.motto=b.motto,
    a.taxid=b.taxid,a.taxiddate=b.taxiddate,a.defcurrency=b.defcurrency,a.usercreate=b.usercreate,a.useredit=b.useredit;
COMMIT;

ALTER TABLE dbv8.company
ADD CONSTRAINT `company_ibfk_1`
FOREIGN KEY (`inventorytransferaccount`) REFERENCES dbv8.`account` (`id`)
ON UPDATE CASCADE ON DELETE RESTRICT;

START TRANSACTION;
INSERT INTO dbv8.division (id, description, acumsalesdisc, updlastsalesmanidtocust, frmsupplierdpid, frmpurchaseorderid, frmgoodsreceiptid, frmpurchaseid, frmpurchasereturnid, frmpurchasepaymentid, frmpayableadjustid, frmcustomerdpid, frmsalesorderid, frmdeliveryorderid, frmsalesid, frmtaxid, frmsalesreturnid, frmsalespaymentid, frmreceivableadjustid, frminventoryid, frmjournalentryid, frmmanufactureid, frmpayrollid, supplierdpidno, purchaseorderidno, goodsreceiptidno, purchaseidno, purchasereturnidno, purchasepaymentidno, payableadjustidno, customerdpidno, salesorderidno, deliveryorderidno, salesidno, taxidno, salesreturnidno, salespaymentidno, receivableadjustidno, inventoryidno, journalentryidno, manufactureidno, payrollidno, retainedearningacc, exchangerateacc, profitlossdiffcashacc, usercreate, useredit)
SELECT id,description,acumsalesdisc,updlastsalesmanidtocust,
replace(frmsupplierdpid,'%0:6.6d','######') AS frmsupplierdpid,
replace(frmpurchaseorderid,'%0:6.6d','######') AS frmpurchaseorderid,
replace(frmgoodsreceiptid,'%0:6.6d','######') AS frmgoodsreceiptid,
replace(frmpurchaseid,'%0:6.6d','######') AS frmpurchaseid,
replace(frmpurchasereturnid,'%0:6.6d','######') AS frmpurchasereturnid,
replace(frmpurchasepaymentid,'%0:6.6d','######') AS frmpurchasepaymentid,
replace(frmpayableadjustid,'%0:6.6d','######') AS frmpayableadjustid,
replace(frmcustomerdpid,'%0:6.6d','######') AS frmcustomerdpid,
replace(frmsalesorderid,'%0:6.6d','######') AS frmsalesorderid,
replace(frmdeliveryorderid,'%0:6.6d','######') AS frmdeliveryorderid,
replace(frmsalesid,'%0:6.6d','######') AS frmsalesid,
replace(frmtaxid,'%0:6.6d','######') AS frmtaxid,
replace(frmsalesreturnid,'%0:6.6d','######') AS frmsalesreturnid,
replace(frmsalespaymentid,'%0:6.6d','######') AS frmsalespaymentid,
replace(frmreceivableadjustid,'%0:6.6d','######') AS frmreceivableadjustid,
replace(frminventoryid,'%0:6.6d','######') AS frminventoryid,
replace(frmjournalentryid,'%0:6.6d','######') AS frmjournalentryid,
replace(frmmanufactureid,'%0:6.6d','######') AS frmmanufactureid,
replace(frmpayrollid,'%0:6.6d','######') AS frmpayrollid,
supplierdpidno,purchaseorderidno,goodsreceiptidno,purchaseidno,purchasereturnidno,purchasepaymentidno,payableadjustidno,customerdpidno,
salesorderidno,deliveryorderidno,salesidno,taxidno,salesreturnidno,salespaymentidno,receivableadjustidno,inventoryidno,journalentryidno,
manufactureidno,payrollidno,retainedearningacc,exchangerateacc,exchangerateacc AS profitlossdiffcashacc,usercreate,useredit
FROM dbv4.division ORDER BY id;

INSERT INTO dbv8.departement (id, name, description, isactive, division, usercreate, useredit)
SELECT id, name, description, isactive, division, usercreate, useredit FROM dbv4.departement;

INSERT INTO dbv8.customergroup (id, description, salesdiscrules, salesproductrewardrules, salespointrewardrules, paymentdiscrules, receivableacc, rewardacc, salesdiscacc, gainlossreceivableacc, usercreate, useredit)
SELECT id, description, salesdiscrules, salesproductrewardrules, salespointrewardrules, paymentdiscrules, receivableacc, rewardacc, salesdiscacc, gainlossreceivableacc, usercreate, useredit
FROM dbv4.customergroup ORDER BY id;

INSERT INTO dbv8.salesmangroup (id, description, commisionrules, payablecommacc, expensecommacc, usercreate, useredit)
SELECT id, description, commisionrules, payablecommacc, expensecommacc, usercreate, useredit
FROM dbv4.salesmangroup ORDER BY id;

INSERT INTO dbv8.salesman (id, name, salesmangroup, address, telephone, faximile, mobilephone, category, contactperson, religion, birthdate, sex, email, website, creditlimit, registerdate, activeuntil, country, isactive, commisionrules, payablecommacc, expensecommacc, usercreate, useredit, image)
SELECT id, name, salesmangroup, address, telephone, faximile, mobilephone, category, contactperson, religion, birthdate, sex, email, website, creditlimit, registerdate, activeuntil, country, isactive, commisionrules, payablecommacc, expensecommacc, usercreate, useredit, '[]' AS image
FROM dbv4.salesman ORDER BY id;

INSERT INTO dbv8.customer (id, name, customergroup, address, shipto, telephone, faximile, mobilephone, category, contactperson, religion, birthdate, sex, email, website, country, creditlimit, creditlimittype, registerdate, activeuntil, taxid, taxiddate, taxable, termofpayment, isactive, defsalesmanid, salesdiscrules, salesproductrewardrules, salespointrewardrules, paymentdiscrules, receivableacc, rewardacc, salesdiscacc, gainlossreceivableacc, point, usercreate, useredit, pricelevel, image)
SELECT id, name, customergroup, address, shipto, telephone, faximile, mobilephone, category, contactperson, religion, birthdate, sex, email, website, country, creditlimit, creditlimittype, registerdate, activeuntil, taxid, taxiddate, taxable, termofpayment, isactive, defsalesmanid, salesdiscrules, salesproductrewardrules, salespointrewardrules, paymentdiscrules, receivableacc, rewardacc, salesdiscacc, gainlossreceivableacc, point, usercreate, useredit, pricelevel, '[]' AS image
FROM dbv4.customer ORDER BY id;

INSERT INTO dbv8.suppliergroup (id, description, payableacc, rewardacc, purchasediscacc, gainlosspayableacc, accruedpayableacc, usercreate, useredit)
SELECT id, description, payableacc, rewardacc, purchasediscacc, gainlosspayableacc, payableacc as accruedpayableacc, usercreate, useredit
FROM dbv4.suppliergroup ORDER BY id;

INSERT INTO dbv8.supplier (id, name, suppliergroup, address, telephone, faximile, mobilephone, category, contactperson, religion, birthdate, sex, email, website, country, creditlimit, registerdate, activeuntil, taxid, isactive, payableacc, rewardacc, purchasediscacc, gainlosspayableacc, accruedpayableacc, termofpayment, usercreate, useredit, updatetimestamp, image)
SELECT id, name, suppliergroup, address, telephone, faximile, mobilephone, category, contactperson, religion, birthdate, sex, email, website, country, creditlimit, registerdate, activeuntil, taxid, isactive, payableacc, rewardacc, purchasediscacc, payableacc as accruedpayableacc, gainlosspayableacc, termofpayment, usercreate, useredit, updatetimestamp, '[]' AS image
FROM dbv4.supplier ORDER BY id;

INSERT INTO dbv8.productgroup (id, name, salesdiscrules, salesmancommrules, salesproductrewardrules, salespointrewardrules, servicedoercommrules, inventoryacc, cogsacc, salesacc, salesdiscacc, salesreturnacc, usercreate, useredit)
SELECT id, name, salesdiscrules, salesmancommrules, salesproductrewardrules, salespointrewardrules, servicedoercommrules, inventoryacc, cogsacc, salesacc, salesdiscacc, salesreturnacc, usercreate, useredit
FROM dbv4.productgroup ORDER BY id;

INSERT INTO dbv8.productbrand (id, name, usercreate, useredit)
SELECT id, name, usercreate, useredit FROM dbv4.productbrand ORDER BY id;

INSERT INTO dbv8.factories (id, name, address, usercreate, useredit)
SELECT id, name, address, usercreate, useredit FROM dbv4.factories ORDER BY id;

INSERT INTO dbv8.authors (id, name, address, usercreate, useredit)
SELECT id, name, address, usercreate, useredit FROM dbv4.authors ORDER BY id;

select taxacc into @taxacc from dbv4.product group by taxacc limit 1;

INSERT INTO dbv8.taxes (id, name, description, percent, inclusive, tax_code, taxoutacc, taxinacc, usercreate, useredit)
SELECT id, name, description, percent, inclusive, tax_code, @taxacc as taxoutacc, @taxacc as taxinacc, usercreate, useredit FROM dbv4.taxes ORDER BY id;

INSERT INTO dbv8.product (id, aliasid, name, description, productgroup, supplier, category, factory, brand, usesn, minimum, maximum, minimumreorder, defaultreorder, taxtype, author, dwidth, dheight, dlength, weight, salesdiscrules, salesmancommrules, salesproductrewardrules, salespointrewardrules, servicedoercommrules, inventoryacc, cogsacc, salesacc, salesdiscacc, salesreturnacc, isactive, usercreate, useredit, image)
SELECT id, aliasid, name, description, productgroup, supplier, category, factory, brand, usesn, minimum, maximum, minimumreorder, defaultreorder, taxtype, author, dwidth, dheight, dlength, weight, salesdiscrules, salesmancommrules, salesproductrewardrules, salespointrewardrules, servicedoercommrules, inventoryacc, cogsacc, salesacc, salesdiscacc, salesreturnacc, isactive, usercreate, useredit, '[]' AS image
FROM dbv4.product ORDER BY id;

INSERT INTO dbv8.units (name, productid, quantity, costprice, purchasedisc, purchasetax, netpurchase, salesprice1, salesprice2, salesprice3, salesprice4, salesprice5, salesprice6, salesprice7, salesdiscqty1, salesdiscprice1, salesdiscqty2, salesdiscprice2, salesdiscqty3, salesdiscprice3, salesdisc, divisionid, departementid, isactive, usercreate, useredit)
SELECT defunit AS name, id AS productid, 1 AS quantity, costprice, purchasedisc, purchasetax, netpurchase, salesprice1, salesprice2, salesprice3, salesprice4, salesprice5, salesprice6, salesprice7, salesdiscqty1, salesdiscprice1, salesdiscqty2, salesdiscprice2, salesdiscqty3, salesdiscprice3, salesdisc, '["*"]' AS divisionid, '["*"]' AS departementid, 1 AS isactive, usercreate, useredit
FROM dbv4.product ORDER BY id;
COMMIT;

CREATE TABLE dbv4.tmpunit AS
SELECT productid, unit
FROM (
  SELECT productid,unit FROM dbv4.purchasedetail WHERE unit NOT IN (SELECT name FROM dbv8.units) GROUP BY productid,unit
  UNION
  SELECT productid,unit FROM dbv4.salesdetail WHERE unit NOT IN (SELECT name FROM dbv8.units) GROUP BY productid,unit
  UNION
  SELECT productid,unit FROM dbv4.productassembledetail WHERE unit NOT IN (SELECT name FROM dbv8.units) GROUP BY productid,unit
) AS c
GROUP BY productid,unit
ORDER BY productid,unit;

CREATE TABLE dbv4.tmpunit2 AS
SELECT a.id AS productid, a.defunit, b.qtyfrom, b.quantity,
a.costprice, a.purchasedisc, a.purchasetax, a.netpurchase, a.salesprice1, a.salesprice2, a.salesprice3, a.salesprice4, a.salesprice5, a.salesprice6, a.salesprice7, a.salesdiscqty1, a.salesdiscprice1, a.salesdiscqty2, a.salesdiscprice2, a.salesdiscqty3, a.salesdiscprice3, a.salesdisc, 
'["*"]' AS divisionid, '["*"]' AS departementid, 1 AS isactive, a.usercreate, a.useredit
FROM dbv4.product AS a
LEFT JOIN dbv4.units AS b ON b.unit=a.defunit
ORDER BY a.id;

START TRANSACTION;
INSERT INTO dbv8.units (name, productid, quantity, costprice, purchasedisc, purchasetax, netpurchase, salesprice1, salesprice2, salesprice3, salesprice4, salesprice5, salesprice6, salesprice7, salesdiscqty1, salesdiscprice1, salesdiscqty2, salesdiscprice2, salesdiscqty3, salesdiscprice3, salesdisc, divisionid, departementid, isactive, usercreate, useredit)
SELECT a.unit AS NAME, a.productid, (b.quantity/c.quantity) AS quantity, 
c.costprice*(b.quantity/c.quantity) AS costprice,
c.purchasedisc*(b.quantity/c.quantity) AS purchasedisc, 
c.purchasetax*(b.quantity/c.quantity) AS purchasetax, 
c.netpurchase*(b.quantity/c.quantity) AS netpurchase, 
c.salesprice1*(b.quantity/c.quantity) AS salesprice1, 
c.salesprice2*(b.quantity/c.quantity) AS salesprice2, 
c.salesprice3*(b.quantity/c.quantity) AS salesprice3, 
c.salesprice4*(b.quantity/c.quantity) AS salesprice4, 
c.salesprice5*(b.quantity/c.quantity) AS salesprice5, 
c.salesprice6*(b.quantity/c.quantity) AS salesprice6, 
c.salesprice7*(b.quantity/c.quantity) AS salesprice7, 
c.salesdiscqty1*(b.quantity/c.quantity) AS salesdiscqty1, 
c.salesdiscprice1*(b.quantity/c.quantity) AS salesdiscprice1, 
c.salesdiscqty2*(b.quantity/c.quantity) AS salesdiscqty2, 
c.salesdiscprice2*(b.quantity/c.quantity) AS salesdiscprice2, 
c.salesdiscqty3*(b.quantity/c.quantity) AS salesdiscqty3, 
c.salesdiscprice3*(b.quantity/c.quantity) AS salesdiscprice3, 
c.salesdisc*(b.quantity/c.quantity) AS salesdisc, 
c.divisionid AS divisionid, 
c.departementid AS departementid, 
c.isactive AS isactive, 
c.usercreate AS usercreate, 
c.useredit AS useredit
FROM (dbv4.tmpunit AS a LEFT JOIN dbv4.units AS b ON b.unit=a.unit)
LEFT JOIN dbv4.tmpunit2 AS c ON c.productid=a.productid
ORDER BY a.productid, b.quantity;
COMMIT;


START TRANSACTION;
UPDATE dbv8.units AS a, dbv4.units AS b
SET a.name=b.description
WHERE b.unit=a.name;

INSERT INTO dbv8.employeegroup (id, description, usercreate, useredit)
SELECT id, description, usercreate, useredit FROM dbv4.employeegroup ORDER BY id;

INSERT INTO dbv8.salarystructure (id, description, usercreate, useredit)
SELECT id, description, usercreate, useredit FROM dbv4.employeelevel ORDER BY id;

INSERT INTO dbv8.employee (id, name, employeegroup, address, telephone, faximile, mobilephone, salarystructure, website, religion, birthdate, sex, email, creditlimit, registerdate, activeuntil, taxid, isactive, country, taxtype, occupation, bankaccount, bankname, usercreate, useredit, image, userid)
SELECT id, name, employeegroup, address, telephone, faximile, mobilephone, employeelevel AS salarystructure, website, religion, birthdate, sex, email, creditlimit, registerdate, activeuntil, taxid, isactive, country, taxtype, occupation, bankaccount, bankname, usercreate, useredit, '[]' AS image, '' AS userid
FROM dbv4.employee ORDER BY id;

INSERT INTO dbv8.paymenttype (id, name, description, paymentacc, cashdiscacc, expenseacc, defexpense, kind, qrcode, usercreate, useredit)
SELECT id, name, description, paymentacc, cashdiscacc, expenseacc, defexpense, kind, '[]' AS qrcode, usercreate, useredit
FROM dbv4.paymenttype ORDER BY id;

INSERT INTO dbv8.servicedoergroup (id, description, commisionrules, payablecommacc, expensecommacc, usercreate, useredit)
SELECT id, description, commisionrules, payablecommacc, expensecommacc, usercreate, useredit
FROM dbv4.servicedoergroup ORDER BY id;

INSERT INTO dbv8.servicedoer (id, name, servicedoergroup, address, telephone, faximile, mobilephone, category, religion, birthdate, sex, email, country, registerdate, activeuntil, isactive, commisionrules, payablecommacc, expensecommacc, contactperson, website, usercreate, useredit, image)
SELECT id, name, servicedoergroup, address, telephone, faximile, mobilephone, category, religion, birthdate, sex, email, country, registerdate, activeuntil, isactive, commisionrules, payablecommacc, expensecommacc, contactperson, website, usercreate, useredit, '[]' AS image
FROM dbv4.servicedoer ORDER BY id;

INSERT INTO dbv8.productpackage (packageid, productid, productname, qty, cogs, subtotal, usercreate, useredit)
SELECT a.packageid, a.productid, b.name AS productname, a.qty, a.cogs, a.subtotal, a.usercreate, a.useredit
FROM dbv4.productpackage AS a
LEFT JOIN dbv4.product AS b ON b.id=a.productid
ORDER BY a.packageid;

UPDATE dbv8.customergroup SET salesdiscrules=CONCAT('[',REPLACE(salesdiscrules,';',','),']');
UPDATE dbv8.customergroup SET salesproductrewardrules=CONCAT('[',REPLACE(salesproductrewardrules,';',','),']');
UPDATE dbv8.customergroup SET salespointrewardrules=CONCAT('[',REPLACE(salespointrewardrules,';',','),']');
UPDATE dbv8.customergroup SET paymentdiscrules=CONCAT('[',REPLACE(paymentdiscrules,';',','),']');

UPDATE dbv8.customer SET salesdiscrules=CONCAT('[',REPLACE(salesdiscrules,';',','),']');
UPDATE dbv8.customer SET salesproductrewardrules=CONCAT('[',REPLACE(salesproductrewardrules,';',','),']');
UPDATE dbv8.customer SET salespointrewardrules=CONCAT('[',REPLACE(salespointrewardrules,';',','),']');
UPDATE dbv8.customer SET paymentdiscrules=CONCAT('[',REPLACE(paymentdiscrules,';',','),']');
UPDATE dbv8.customer SET shipto='' WHERE ISNULL(shipto);

UPDATE dbv8.salesmangroup SET commisionrules=CONCAT('[',REPLACE(commisionrules,';',','),']');
UPDATE dbv8.salesman SET commisionrules=CONCAT('[',REPLACE(commisionrules,';',','),']');

UPDATE dbv8.servicedoergroup SET commisionrules=CONCAT('[',REPLACE(commisionrules,';',','),']');
UPDATE dbv8.servicedoer SET commisionrules=CONCAT('[',REPLACE(commisionrules,';',','),']');

UPDATE dbv8.productgroup SET salesdiscrules=CONCAT('[',REPLACE(salesdiscrules,';',','),']');
UPDATE dbv8.productgroup SET salesmancommrules=CONCAT('[',REPLACE(salesmancommrules,';',','),']');
UPDATE dbv8.productgroup SET salesproductrewardrules=CONCAT('[',REPLACE(salesproductrewardrules,';',','),']');
UPDATE dbv8.productgroup SET salespointrewardrules=CONCAT('[',REPLACE(salespointrewardrules,';',','),']');
UPDATE dbv8.productgroup SET servicedoercommrules=CONCAT('[',REPLACE(servicedoercommrules,';',','),']');

UPDATE dbv8.product SET salesdiscrules=CONCAT('[',REPLACE(salesdiscrules,';',','),']');
UPDATE dbv8.product SET salesmancommrules=CONCAT('[',REPLACE(salesmancommrules,';',','),']');
UPDATE dbv8.product SET salesproductrewardrules=CONCAT('[',REPLACE(salesproductrewardrules,';',','),']');
UPDATE dbv8.product SET salespointrewardrules=CONCAT('[',REPLACE(salespointrewardrules,';',','),']');
UPDATE dbv8.product SET servicedoercommrules=CONCAT('[',REPLACE(servicedoercommrules,';',','),']');

INSERT INTO dbv8.salesdiscrules (id, description, displayname, formula, category, kind, usercreate, useredit)
SELECT id, description, displayname, formula, category, kind, usercreate, useredit FROM dbv4.salesdiscrules ORDER BY id;

INSERT INTO dbv8.salesproductrewardrules (id, description, displayname, formula, category, productid, unit, departement, usercreate, useredit)
SELECT id, description, displayname, formula, category, productid, unit, departement, usercreate, useredit FROM dbv4.salesproductrewardrules ORDER BY id;

INSERT INTO dbv8.salespointrewardrules (id, description, displayname, formula, category, usercreate, useredit)
SELECT id, description, displayname, formula, category, usercreate, useredit FROM dbv4.salespointrewardrules ORDER BY id;

INSERT INTO dbv8.paymentdiscrules (id, description, displayname, formula, usercreate, useredit)
SELECT id, description, displayname, formula, usercreate, useredit FROM dbv4.paymentdiscrules ORDER BY id;

INSERT INTO dbv8.salesmancommrules (id, description, displayname, formula, category, usercreate, useredit)
SELECT id, description, displayname, formula, category, usercreate, useredit FROM dbv4.salesmancommrules ORDER BY id;

INSERT INTO dbv8.servicedoercommrules (id, description, displayname, formula, category, usercreate, useredit)
SELECT id, description, displayname, formula, category, usercreate, useredit FROM dbv4.servicedoercommrules ORDER BY id;

INSERT INTO dbv8.`roles` (`id`, `name`, `menus`, `defaultdivision`, `lockdivision`, `defaultcustomer`, `lockcustomer`, `defaultsupplier`, 
`locksupplier`, `defaultdepartement`, `lockdepartement`, `defaultsalesman`, `locksalesman`, `defaultservicedoer`, `lockservicedoer`, `approvaljournal`, 
`defaultcurrency`, `lockcurrency`, `defaultemployee`, `lockemployee`, `defaultshipment`, `lockshipment`, `defaultpayment`, `lockpayment`, 
`useextratax`, `usesnproduct`, `closepos`, `lockdatetime`, `locksalesprice`, `locksalesdisc`, `sellbelowcogs`, `productgroup`, `customergroup`, `suppliergroup`, `employeegroup`, `salesmangroup`, `servicedoergroup`, `divisionlist`, `departementlist`) VALUES
('administrator', 'Administrator', '[\"supplierdp\",\"purchaserequisition\",\"purchaseorder\",\"goodsreceipt\",\"purchase\",\"purchasereturn\",\"payablepayment\",\"payableadjust\",\"repsalesconsignsummary\",\"purchasesummary\",\"purchasedivtransprod\",\"purchasedivdeptprod\",\"purchasedivsupprod\",\"purchasedivpurprod\",\"purchaseperproductgroup\",\"purchaseperproductbrand\",\"purchasedivshipmenttrans\",\"purchasereturnsummary\",\"purchasereturndivtransprod\",\"purchasereturndivdeptprod\",\"purchasereturndivsupprod\",\"purchasereturndivpurprod\",\"repgoodsreceiptsummary\",\"purchaseanalysis\",\"reppurchaseanalysbyproduct\",\"reppurchaseanalysbyminreorder\",\"repsupplierdp\",\"repsupplierbalance\",\"repsupplierbalancesummary\",\"reppayablepayment\",\"supplierhistories\",\"/form/suppliergroup\",\"/form/supplier\",\"listofemployee\",\"listofpurchaseexpenses\",\"inputpayablebal\",\"journal\",\"approvejournal\",\"journalreport\",\"generalledger\",\"balancesheet\",\"profitlossstatement\",\"cashflow\",\"repchangeequity\",\"exportefaktur\",\"repgirosupplier\",\"repgirocustomer\",\"analysiscenter\",\"fixedassetmng\",\"fixedassetdepr\",\"repfixedasset\",\"/form/account\",\"closingbook\",\"help\",\"activation\",\"tutorial\",\"checkupdates\",\"aboutacosys\",\"inventorymoving\",\"assembly\",\"disassembly\",\"sellingpackage\",\"repinventoryout\",\"inventoryadjust\",\"repinventorybal\",\"repinventorybalnosn\",\"repinventorybalnocogs\",\"repinventorybalnosncogs\",\"repglobalinventory\",\"inventorydetail\",\"producthistories\",\"repinventorymovingsummary\",\"repinventorymoving\",\"inventorybalanceloc\",\"/form/product\",\"/form/productgroup\",\"productmanagement\",\"masspricing\",\"/form/factories\",\"/form/units\",\"/form/authors\",\"/form/productbrand\",\"printlabel\",\"inputinventorybal\",\"/login\",\"/changepassword\",\"/options\",\"/changedate\",\"/changelanguage\",\"/form/company\",\"/form/users\",\"/form/roles\",\"/form/authorityrequest\",\"/form/division\",\"/form/departement\",\"/form/paymenttype\",\"/form/taxes\",\"/form/shipments\",\"/form/groupid\",\"/form/discountdays\",\"/form/salesdiscrules\",\"/form/salesproductrewardrules\",\"/form/salespointrewardrules\",\"listofpaymentdiscrules\",\"/form/salesmancommrules\",\"/form/servicedoercommrules\",\"/synctransferproduct\",\"/syncreceiveproduct\",\"/syncexportdata\",\"/syncimportdatas\",\"/maintenance\",\"/form/logtransaction\",\"generalgroup\",\"/dashboard\",\"productgroup\",\"productsearch\",\"synchronizegroup\",\"productreportgroup\",\"purchaseconsigngroup\",\"salesgroup\",\"salesquote\",\"salesorder\",\"deliveryorder\",\"sales\",\"packinggroup\",\"processpacking\",\"unpacked\",\"packed\",\"salespos\",\"postingpos\",\"salesreturn\",\"receivablepayment\",\"massreceivable\",\"receivableadjust\",\"customernotes2\",\"pullcustomerpoint\",\"salesconsigngroup\",\"inpsoldconsign\",\"repsalesconsigncustomer\",\"salesreportgroup\",\"salessummary\",\"salesdivtransprod\",\"salesdivdeptprod\",\"salesdivcustprod\",\"listofcustomer\",\"listofcustomerguide\",\"listofsalesman\",\"listofservicedoer\",\"listofsalesexpenses\",\"customernotestype\",\"inputreceivablebal\",\"rentgroup\",\"rentaltransaction\",\"rentreportgroup\",\"statusproduct\",\"reservation\",\"listofrentproductstatus\",\"listofrentproduct\",\"fixedassetgroup\",\"salaryintake\",\"masspayroll\",\"salariescalc\",\"salesmancommission\",\"servicedoercommission\",\"listofemployeelevel\",\"listofsalaries\",\"listholiday\",\"helpgroup\",\"salesdivsalesmanprod\",\"salesdivservicedoerprod\",\"salesdivshipmentprod\",\"salesdivshipmenttransaction\",\"salesperproductgroup\",\"salesperproductbrand\",\"salescustdivtransprod\",\"analyzeperbrand\",\"salesreturnsummary\",\"salesreturndivtransprod\",\"salesreturndivdeptprod\",\"salesreturndivcustprod\",\"salesreturndivsalesmanprod\",\"salesreturndivservicedoerprod\",\"salesorderpersalesman\",\"salesorderdivtransprod\",\"dopercustomerso\",\"repsalespayment\",\"repcustomerpoint\",\"repcustomerdp\",\"customerreward\",\"customerrewarddetail\",\"repcustomerbalance\",\"repcustomerbalancesummary\",\"repreceivablepayment\",\"repbilltosummary\",\"customerhistories\",\"/form/religions\",\"/form/currencies\",\"/form/countries\",\"myprofile\",\"changepassword\",\"options\",\"changedate\",\"changelanguage\",\"maintenance\",\"form/logtransaction\",\"/form/paymentdiscrules\",\"synctransferproduct\",\"syncreceiveproduct\",\"syncexportdata\",\"syncimportdatas\",\"rulesgroup\",\"/form/employee\",\"/form/employeegroup\",\"/form/customer\",\"/form/customergroup\",\"/form/salesman\",\"/form/salesmangroup\",\"/form/servicedoer\",\"/form/servicedoergroup\",\"payrollgroup\",\"/form/journal\",\"/form/printdesign\",\"/admin/reports\",\"/form/purchaseexpenses\",\"/form/salesexpenses\",\"/form/employeelevel\",\"/form/salaries\",\"/form/purchase\",\"/form/report\",\"/report/journalreport\",\"/report/generalledger\",\"/form/inputpayablebal\",\"/form/supplierdp\",\"/form/purchaserequisition\",\"/form/purchaseorder\",\"/form/goodsreceipt\",\"/form/purchasereturn\",\"/form/payablepayment\",\"/form/payableadjust\",\"/form/repsalesconsignsummary\",\"/report/purchasesummary\",\"/report/purchasedivtransprod\",\"/report/purchasedivdeptprod\",\"purchasereportgroup\",\"/report/purchasedivsupprod\",\"/report/purchaseperproductgroup\",\"/report/purchasedivpurprod\",\"/report/purchaseperproductbrand\",\"/report/purchasedivshipmenttrans\",\"/report/purchasereturnsummary\",\"/report/purchasereturndivtransprod\",\"/report/purchasereturndivsupprod\",\"purchasegroup\",\"/report/purchasereturndivdeptprod\",\"/report/purchasereturndivpurprod\",\"/report/purchaserequisition\",\"/report/purchaseorder\",\"/report/goodsreceipt\",\"/report/repgoodsreceiptsummary\",\"/report/purchaseanalysis\",\"/report/reppurchaseanalysbyproduct\",\"/report/reppurchaseanalysbyminreorder\",\"/report/repsupplierdp\",\"/report/repsupplierbalance\",\"/report/repsupplierbalancesummary\",\"/report/reppayablepayment\",\"/report/payableadjust\",\"/report/supplierhistories\",\"/form/customerdp\",\"/form/salesquote\",\"/form/salesorder\",\"/form/sales\",\"/form/salespos\",\"/form/processpacking\",\"/form/unpacked\",\"/form/packed\",\"/form/postingpos\",\"/form/salesreturn\",\"/form/receivablepayment\",\"/form/massreceivable\",\"/form/receivableadjust\",\"/form/customernotes2\",\"/form/pullcustomerpoint\",\"/form/inpsoldconsign\",\"/report/repsalesconsignsummary\",\"/report/repsalesconsigncustomer\",\"/report/salessummary\",\"/report/salesdivtransprod\",\"/report/salesdivdeptprod\",\"/report/salesdivcustprod\",\"/report/salesdivsalesmanprod\",\"/report/salesdivservicedoerprod\",\"/report/salesdivshipmentprod\",\"/report/salesdivshipmenttransaction\",\"/report/salesperproductgroup\",\"/report/salesperproductbrand\",\"/report/salescustdivtransprod\",\"/report/analyzeperbrand\",\"/report/salesreturnsummary\",\"/report/salesreturndivtransprod\",\"/report/salesreturndivdeptprod\",\"/report/salesreturndivcustprod\",\"/report/salesreturndivsalesmanprod\",\"/report/salesreturndivservicedoerprod\",\"/report/salesquote\",\"/report/salesorder\",\"/report/salesorderpersalesman\",\"/report/salesorderdivtransprod\",\"/report/deliveryorder\",\"/report/dopercustomerso\",\"/report/repsalespayment\",\"/report/repcustomerpoint\",\"/report/repcustomerdp\",\"/report/customerreward\",\"/report/customerrewarddetail\",\"/report/repcustomerbalance\",\"/report/repcustomerbalancesummary\",\"/report/repreceivablepayment\",\"/report/repbilltosummary\",\"/report/receivableadjust\",\"/report/customernotes2\",\"/report/customerhistories\",\"/form/customernotestype\",\"/form/inputreceivablebal\",\"/form/closingbook\",\"/form/fixedassetmng\",\"/form/fixedassetdepr\",\"/report/repfixedasset\",\"/report/analysiscenter\",\"/report/repgirocustomer\",\"/report/repgirosupplier\",\"/report/exportefaktur\",\"/report/repchangeequity\",\"/report/cashflow\",\"/report/profitlossstatement\",\"/report/balancesheet\",\"/form/approvejournal\",\"/form/rentaltransaction\",\"/form/listofrentproduct\",\"/report/statusproduct\",\"/report/reservation\",\"/report/listofrentproductstatus\",\"/report/rentaltransaction\",\"/updateversion\",\"/report/repinventorybal\",\"/report/repinventorybalnosn\",\"/report/repinventorybalnocogs\",\"/report/repinventorybalnosncogs\",\"/report/repglobalinventory\",\"/report/inventorydetail\",\"/report/producthistories\",\"/report/repinventorymovingsummary\",\"/report/repinventorymoving\",\"/report/repinventoryout\",\"/report/inventoryadjust\",\"/report/inventorybalanceloc\",\"/report/assembly\",\"/report/disassembly\",\"/displayconfig\",\"/report/cogshistories\",\"/documentation\",\"/form/themecolors\",\"/form/fixedasset\",\"/form/fixedassetgroup\",\"/form/fixedassetdepreciation\",\"/form/deliveryorder\",\"/form/fixedassetdispose\",\"/form/inventorymoving\",\"/form/productassemble\",\"/form/productdisassemble\",\"/form/inventoryout\",\"/form/inventoryadjust\",\"/report/printproductbarcode\",\"/form/pointofsales\",\"/form/posprintcashier\",\"/form/posprintorder\",\"/pointofsales\",\"/posstart\",\"/form/postable\",\"/form/inventoryin\",\"/backuprestore\",\"manufacturegroup\",\"/form/mrporder\",\"/form/mrpworkorder\",\"mrpreportgroup\",\"/report/mrporder\",\"/report/mrpworkorder\",\"/form/mrpbomoperation\",\"/form/mrpoperation\",\"/form/mrpworkcenter\",\"/form/domrpworkorder\",\"/report/mrporderready\",\"/report/mrporderinprogress\",\"/report/mrporderexpected\",\"/report/mrporderdeadline\",\"/report/mrporderfinish\",\"/report/workorderready\",\"/report/workorderinprogress\",\"/report/workorderfinish\",\"/report/workorderexpected\",\"/report/workorderdeadline\",\"/report/workorderemployee\",\"/report/workorderemployeeoperation\",\"/report/repinventoryin\",\"/form/postablegroup\",\"/report/workorder\",\"/report/workorderoperation\",\"/report/massmrporder\",\"accountinggroup\",\"/form/workinghours\",\"/form/salarystructure\",\"payrollreportgroup\",\"/form/servicedoercommission\",\"/form/salesmancommission\",\"/form/salariescalc\",\"/form/salaryintake\",\"/attendance\",\"/report/attendance\",\"/report/attendancesummary\",\"/report/salaryslip\",\"/report/salarysummary\",\"/report/salarypertype\",\"/report/mrporderprogress\",\"/report/workorderlate\",\"/report/salestrenddays\",\"/report/salestrendmonths\",\"/form/suppliergiro\",\"/form/customergiro\",\"/report/reppurchasepayment\",\"/report/reppurchaseanalysis\",\"/form/logtransactions\",\"/analystcenter\",\"/logout\",\"/report/printproductlabel\",\"/customerinformation\",\"/report/productpricelist\",\"/masspricing\",\"/report/sheetattendance\",\"/customreports\",\"/apsconfig\",\"/aboutapp\",\"/form/rentalreturn\",\"/form/customerdisplay\"]', 
@defaultdivisionid, 0, @defaultcustomerid, 0, @defaultsupplierid, 0, @defaultdepartementid, 0, @defaultsalesmanid, 0, @defaultservicedoerid, 0, 0, 
@defaultcurrencyid, 0, @defaultemployeeid, 0, @defaultshipmentid, 0, @defaultpaymenttypeid, 0, 1, 0, 1, 0, 0, 0, 0, '[]', '[]', '[]', '[]', '[]', '[]', '[]', '[]'),
('visitor', 'Visitor', '["supplierdp","purchaserequisition","purchaseorder","goodsreceipt","purchase","purchasereturn","payablepayment","payableadjust","repsalesconsignsummary","purchasesummary","purchasedivtransprod","purchasedivdeptprod","purchasedivsupprod","purchasedivpurprod","purchaseperproductgroup","purchaseperproductbrand","purchasedivshipmenttrans","purchasereturnsummary","purchasereturndivtransprod","purchasereturndivdeptprod","purchasereturndivsupprod","purchasereturndivpurprod","repgoodsreceiptsummary","purchaseanalysis","reppurchaseanalysbyproduct","reppurchaseanalysbyminreorder","repsupplierdp","repsupplierbalance","repsupplierbalancesummary","reppayablepayment","supplierhistories","listofemployee","listofpurchaseexpenses","inputpayablebal","journal","approvejournal","journalreport","generalledger","balancesheet","profitlossstatement","cashflow","repchangeequity","exportefaktur","repgirosupplier","repgirocustomer","analysiscenter","fixedassetmng","fixedassetdepr","repfixedasset","closingbook","help","activation","tutorial","checkupdates","aboutacosys","inventorymoving","assembly","disassembly","sellingpackage","repinventoryout","inventoryadjust","repinventorybal","repinventorybalnosn","repinventorybalnocogs","repinventorybalnosncogs","repglobalinventory","inventorydetail","producthistories","repinventorymovingsummary","repinventorymoving","inventorybalanceloc","productmanagement","masspricing","/form/units","/form/authors","printlabel","inputinventorybal","/login","/changepassword","/options","/changedate","/changelanguage","/form/authorityrequest","/form/groupid","/form/discountdays","listofpaymentdiscrules","/form/salesmancommrules","/form/servicedoercommrules","/synctransferproduct","/syncreceiveproduct","/syncexportdata","/syncimportdatas","/maintenance","/form/logtransaction","productsearch","synchronizegroup","purchaseconsigngroup","salesquote","salesorder","deliveryorder","sales","packinggroup","processpacking","unpacked","packed","postingpos","salesreturn","receivablepayment","massreceivable","receivableadjust","customernotes2","pullcustomerpoint","salesconsigngroup","inpsoldconsign","repsalesconsigncustomer","salessummary","salesdivtransprod","salesdivdeptprod","salesdivcustprod","listofcustomer","listofcustomerguide","listofsalesman","listofservicedoer","listofsalesexpenses","customernotestype","inputreceivablebal","rentgroup","rentaltransaction","rentreportgroup","statusproduct","reservation","listofrentproductstatus","listofrentproduct","salaryintake","masspayroll","salariescalc","salesmancommission","servicedoercommission","listofemployeelevel","listofsalaries","listholiday","helpgroup","salesdivsalesmanprod","salesdivservicedoerprod","salesdivshipmentprod","salesdivshipmenttransaction","salesperproductgroup","salesperproductbrand","salescustdivtransprod","analyzeperbrand","salesreturnsummary","salesreturndivtransprod","salesreturndivdeptprod","salesreturndivcustprod","salesreturndivsalesmanprod","salesreturndivservicedoerprod","salesorderpersalesman","salesorderdivtransprod","dopercustomerso","repsalespayment","repcustomerpoint","repcustomerdp","customerreward","customerrewarddetail","repcustomerbalance","repcustomerbalancesummary","repreceivablepayment","repbilltosummary","customerhistories","myprofile","changepassword","options","changedate","changelanguage","maintenance","form/logtransaction","synctransferproduct","syncreceiveproduct","syncexportdata","syncimportdatas","/form/printdesign","/admin/reports","/form/employeelevel","/form/report","/form/purchaserequisition","/form/repsalesconsignsummary","/report/purchaserequisition","/report/purchaseanalysis","/report/reppurchaseanalysbyproduct","/report/reppurchaseanalysbyminreorder","/form/salesquote","/form/salespos","/form/processpacking","/form/unpacked","/form/packed","/form/postingpos","/form/massreceivable","/form/customernotes2","/form/pullcustomerpoint","/form/inpsoldconsign","/report/repsalesconsignsummary","/report/repsalesconsigncustomer","/report/salescustdivtransprod","/report/analyzeperbrand","/report/salesquote","/report/customernotes2","/form/customernotestype","/form/fixedassetmng","/form/fixedassetdepr","/report/analysiscenter","/report/repgirocustomer","/report/repgirosupplier","/form/rentaltransaction","/form/listofrentproduct","/report/statusproduct","/report/reservation","/report/listofrentproductstatus","/report/rentaltransaction","/report/assembly","/report/disassembly","/documentation","/form/productassemble","/form/productdisassemble","/pointofsales","/form/mrpworkorder","/report/mrpworkorder","/form/mrpbomoperation","/report/mrporderready","/report/mrporderinprogress","/report/mrporderexpected","/report/mrporderfinish","/report/workorderready","/report/workorderinprogress","/report/workorderfinish","/report/workorderexpected","/report/workorderemployeeoperation","/form/servicedoercommission","/form/salesmancommission","/form/suppliergiro","/form/customergiro","/report/reppurchaseanalysis","/analystcenter","/logout","/customreports","/apsconfig","/aboutapp","https://www.youtube.com/playlist?list=PLjfTJHP3hmCdHX7ff9d-wTy1PhKiAYlTz","https://www.acosys8.com/doc"]', 
@defaultdivisionid, 0, @defaultcustomerid, 0, @defaultsupplierid, 0, @defaultdepartementid, 0, @defaultsalesmanid, 0, @defaultservicedoerid, 0, 0, 
@defaultcurrencyid, 0, @defaultemployeeid, 0, @defaultshipmentid, 0, @defaultpaymenttypeid, 0, 1, 0, 1, 0, 0, 0, 0, '[]', '[]', '[]', '[]', '[]', '[]', '[]', '[]');

DELETE FROM dbv8.users;
INSERT INTO dbv8.users (id, userpassword, name, description, usercreate, isactive, useredit, photo, roles)
SELECT id, '280a03080a' as userpassword, name, description, usercreate, isactive, useredit, '[]' as photo, 'administrator' as roles
FROM dbv4.users ORDER BY id;

INSERT INTO dbv8.`company` (`id`, `name`, `provincename`, `cityname`, `subdistrictname`, `address`, `country`, `postcode`, `telephone`, `faximile`, `email`, `website`, `motto`, `taxid`, `taxiddate`, `currentdate`, `defcurrency`, `usercreate`, `useredit`, `logo`, `general_log`, `locationlat`, `locationlng`, `cachetype`, `layout`, `customstyle`, `inventorytransferaccount`, `digitmoney`, `digitproduct`, `paypal_client_id`, `paypal_secret`, `paypal_sandbox`, `paypal_enabled`, `stripe_api_key`, `stripe_secret`, `stripe_sandbox`, `stripe_enabled`, `midtrans_server_key`, `midtrans_client_key`, `midtrans_sandbox`, `midtrans_enabled`, `timezone`) VALUES
	(1, 'Demo Company', 'Lampung', 'Bandar Lampung (Kota)', 'Rajabasa', 'Alamat perusahaan', 'IDN', '35139', '', '', '', '', 'Slogan perusahaan', 'sample-taxid', NULL, '2011-10-08', 'IDR', 'admin', 'admin', '[{"img":"\\/api\\/assets\\/company\\/deflogo.webp","caption":"","subtitle":""}]', 0, -5.3622345, 105.2383336, 1, '{"view":"lHr LpR lfr","headerReveal":false,"headerSep":"elevated","footerReveal":false,"footerSep":"elevated","leftBehavior":"default","leftOverlay":false,"leftSep":"bordered","rightBehavior":"default","rightOverlay":false,"rightSep":"bordered","filled":false,"outlined":true,"standout":false,"borderless":false,"rounded":false,"transitionshow":"scale","transitionhide":"scale","headerClass":"bg-primary text-white","leftsideClass":"bg-dark text-light-green-2","rightsideClass":"bg-dark text-white","footerClass":"bg-dark text-white","square":false,"themecolor":"{\\"primary\\":\\"#027BE3\\",\\"secondary\\":\\"#26A69A\\",\\"accent\\":\\"#9C27B0\\",\\"dark\\":\\"#1e2b45\\",\\"positive\\":\\"#21BA45\\",\\"negative\\":\\"#C10015\\",\\"info\\":\\"#31CCEC\\",\\"warning\\":\\"#F2C037\\"}","button":{"flat":false,"rounded":true,"outline":false,"push":false,"unelevated":false,"glossy":false},"darkmode":true}', '<style>\n.custom1 {\n  color: #ff0000;\n  text-shadow: 2px 2px #ff0000;\n}\n</style>', @aspersediaandivisi, 2, 2, '', '', 1, 0, '', '', 1, 0, '', '', 1, 0, 'Asia/jakarta');
COMMIT;

-- untuk konversi dari perakitan produk ke manufaktur
-- jika perakitan dari Acosys V4 tidak dikonversi ke manufaktur maka hapus dari baris ini sampai akhir
CREATE TABLE dbv4.tmpmanufacture AS
SELECT a.transid, b.productid AS mrpproduct, b.qty AS qtyproduced, b.unit AS mrpunit,
       a.productid, a.qty, a.unit, a.cogs, a.subtotal
FROM dbv4.productassembledetail AS a
LEFT JOIN dbv4.productassemble AS b ON b.id=a.transid
WHERE transid IN (SELECT MAX(id) AS refmf FROM dbv4.productassemble GROUP BY productid)
ORDER BY a.transid, a.productid;

START TRANSACTION;
INSERT INTO dbv8.mrpbomcomponent (mrpproduct, productid, productname, qty, unit, usercreate)
SELECT a.mrpproduct, a.productid, b.name AS productname,
       (a.qty/a.qtyproduced) AS qty, a.unit, 'admin' AS usercreate
FROM dbv4.tmpmanufacture AS a
LEFT JOIN dbv4.product AS b ON b.id=a.productid
ORDER BY a.mrpproduct, a.productid;
COMMIT;

