<?php
/* PIUTANG AWAL
1. Prepare data Piutang Awal di Excel dengan rumus :
=CONCATENATE(" UNION ALL SELECT 'pusat','",A6,"','2019-01-01','",B6,"','17','",C6,"','",D6,"','IDR','1','1','",E6,"','",E6,"','",E6,"','1','1'")

# A6 : Kolom A Row 6
# A6 = No Sales Invoice, B6 = No Referensi, "17" = Kode Sales, C6 = Kode Customer, D6 = Nama Customer, E6 = Nilai Invoice 
 
2. Insert Data Piutang Awal ke table txsales_invoice_hdr 
INSERT INTO txsales_invoice_hdr (ftbranch_code,ftsales_invoice_no,fdtrans_date,ftreff_no,ftsales_code,ftcustomer_code,ftcustomer_name,ftcurrency_code,fcrate,fnpayment_status,fcsub_amount,fcamount,fcoutstanding_amount,fnstatus,fnsaldo_awal) 
SELECT ... (Data dari Excel)

3. Query Utk cek dan edit kode customer yang tdk sama 
 
# CEK Data Customer Dengan Nama Sama di Piutang Awal tapi Beda Kode di daftar customer
SELECT DISTINCT a.ftcustomer_code,a.ftcustomer_name,b.ftcustomer_code,b.ftcustomer_name 
FROM tlcustomer a LEFT JOIN txsales_invoice_hdr b ON a.ftcustomer_name = b.ftcustomer_name
WHERE b.fnstatus = 1 AND b.fnsaldo_awal = 1 AND a.ftcustomer_code != b.`ftcustomer_code`
ORDER BY b.`ftcustomer_name`

# CEK Data Customer Dengan Kode di Piutang Awal tapi tidak ada di daftar customer
SELECT DISTINCT b.ftcustomer_code,b.ftcustomer_name 
FROM txsales_invoice_hdr b  
WHERE b.fnstatus = 1 AND b.fnsaldo_awal = 1 
	AND b.ftcustomer_code NOT IN (SELECT a.ftcustomer_code FROM tlcustomer a)

SELECT * FROM tlcustomer WHERE ftcustomer_code = 'P05JUT' OR ftcustomer_name LIKE '%prima%'

# UPDATE Data transaksi disesuaikan ke kode customer yang ada di Daftar Piutang Awal
UPDATE tlcustomer SET ftcustomer_code = 'P05JUT' WHERE ftcustomer_code = 'P25';
UPDATE txsales_order_hdr SET ftcustomer_code = 'P05JUT' WHERE ftcustomer_code = 'P25';
UPDATE txsales_invoice_hdr SET ftcustomer_code = 'P05JUT' WHERE ftcustomer_code = 'P25';
UPDATE txdelivery_order_hdr SET ftcustomer_code = 'P05JUT' WHERE ftcustomer_code = 'P25';
UPDATE txsales_return_hdr SET ftcustomer_code = 'P05JUT' WHERE ftcustomer_code = 'P25';
UPDATE txsales_invoice_adjustment_hdr SET ftcustomer_code = 'P05JUT' WHERE ftcustomer_code = 'P25';
 
# UPDATE Data Piutang Awal disesuaikan ke kode customer yang ada di master customer
UPDATE txsales_invoice_hdr SET ftcustomer_code = 'A08JTM' 
WHERE  fnstatus = 1 AND fnsaldo_awal = 1 
	AND  ftcustomer_code = 'A08BKS';
	  

*/

/* HUTANG AWAL
1. Prepare data Hutang Awal di Excel dengan rumus :
=CONCATENATE(" UNION ALL SELECT 'PUSAT','",A5,"','2019-01-01','",B5,"','",F5,"','",C5,"','",D5,"','",G5,"','",H5,"','1','",E5,"','",E5,"','",E5,"','1','1','1','2019-01-01','0','0','0','0','0'")
 * 
# A5 : Kolom A Row 5
# A5 = No Purchase Invoice, B5 = No Referensi, F5 = Kode Local / Impor, C5 = Kode Supplier, D5 = Nama Supplier, G5 = Kode Currency, H5 = Nilai Kurs, E5 = Nominal Invoice 
 
2. Insert Data Hutang Awal ke table txpurchase_invoice_hdr 
INSERT INTO txpurchase_invoice_hdr (ftbranch_code,ftpurchase_invoice_no,fdtrans_date,ftnotes,fnpo_type,ftsupplier_code,ftsupplier_name,ftcurrency_code,fcrate,fnpayment_status,fcsub_amount,fcamount,fcoutstanding_amount,fnstatus,fnsaldo_awal,fntax_code,fddue_date,ftterm_payment,ffvat,fcvat,fcpayment_amount,fcreturn_amount)
SELECT ... (Data dari Excel)

3. Query Utk cek dan edit kode supplier yang tdk sama 
 
# CEK Data Supplier Dengan Nama Sama di Hutang Awal tapi Beda Kode di daftar supplier
SELECT DISTINCT a.ftsupplier_code,a.ftsupplier_name,b.ftsupplier_code,b.ftsupplier_name 
FROM tlsupplier a LEFT JOIN txpurchase_invoice_hdr b ON a.ftsupplier_name = b.ftsupplier_name
WHERE b.fnstatus = 1 AND b.fnsaldo_awal = 1 AND a.ftsupplier_code != b.`ftsupplier_code`
ORDER BY b.`ftsupplier_code`


# CEK Data Supplier Dengan Kode di Hutang Awal tapi tidak ada di daftar supplier
SELECT DISTINCT b.ftsupplier_code,b.ftsupplier_name 
FROM txpurchase_invoice_hdr b  
WHERE b.fnstatus = 1 AND b.fnsaldo_awal = 1 
	AND b.ftsupplier_code NOT IN (SELECT a.ftsupplier_code FROM tlsupplier a)

SELECT * FROM tlsupplier WHERE ftsupplier_name LIKE '%fuji%' ftsupplier_code = 'LM01'	

# UPDATE Data transaksi disesuaikan ke kode supplier yang ada di Daftar Hutang Awal
UPDATE tlsupplier SET ftsupplier_code = 'LA11' WHERE ftsupplier_code = 'LA32';
UPDATE txpurchase_order_hdr SET ftsupplier_code = 'LA11' WHERE ftsupplier_code = 'LA32';
UPDATE txpurchase_invoice_hdr SET ftsupplier_code = 'LA11' WHERE ftsupplier_code = 'LA32';
UPDATE txstock_in_hdr SET ftstock_in_company = 'LA11' WHERE ftstock_in_company = 'LA32';
UPDATE txpurchase_return_hdr SET ftsupplier_code = 'LA11' WHERE ftsupplier_code = 'LA32';
 
# UPDATE Data Hutang Awal disesuaikan ke kode supplier yang ada di master supplier
UPDATE txpurchase_invoice_hdr SET ftsupplier_code = 'LM16' 
WHERE  fnstatus = 1 AND fnsaldo_awal = 1 
	AND  ftsupplier_code = 'LM04';
   
*/


?>
