<?php
// PENJUALAN 
// SALES INVOICE

// Query cek jumlah sales invoice
SELECT * FROM txsales_invoice_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fnsaldo_awal = 0 AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftsales_invoice_no ASC

// Query cek jumlah gl utk sales invoice        
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource = "sales_order" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.fdtrans_date ASC

// Query cek no document sales invoice yg double  
SELECT a.ftsales_invoice_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftsales_invoice_no)
FROM txsales_invoice_hdr a LEFT JOIN txgeneral_journal_hdr b ON a.ftsales_invoice_no = b.`ftdocument_no` AND b.fnstatus != 9 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fnsaldo_awal = 0 AND a.fdtrans_date >= '2019-01-01'
GROUP BY a.ftsales_invoice_no
HAVING COUNT(a.ftsales_invoice_no) > 1
ORDER BY COUNT(a.ftsales_invoice_no)  DESC
        
// query cek invoice yg tdk ada di jurnal
SELECT DISTINCT ftsales_invoice_no,fnstatus FROM txsales_invoice_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9 AND a.fnsaldo_awal = 0 AND a.fdtrans_date >= '2019-01-01'  
	AND ftsales_invoice_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource ='sales_order' )
        
// query cek jurnal yg tdk ada di invoice
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource ='sales_order' AND ftdocument_no NOT IN 
(SELECT ftsales_invoice_no  FROM txsales_invoice_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fnsaldo_awal = 0 AND a.fdtrans_date >= '2019-01-01' )


        
// PEMBELIAN ???        
// Query untuk cek jurnal dengan no document yg sama (double)
-- query cek jumlah transaksi pembelian
SELECT * FROM txpurchase_invoice_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftpurchase_invoice_no ASC
-- 298

-- Query cek jumlah stock in utk pembelian
SELECT *, fdtrans_date,ftdocument_no,fnstatus  FROM txstock_in_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fdtrans_date >= '2019-01-01' AND ftcategory_code = 0
ORDER BY a.ftdocument_no ASC
-- 298

-- Query cek jumlah gl utk pembelian
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource LIKE "pembelian_stock_masuk%" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.ftdocument_no ASC
-- 298

-- cek pembelian lebih dari 1x di GL
SELECT a.ftpurchase_invoice_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftpurchase_invoice_no)
FROM txpurchase_invoice_hdr a INNER JOIN txgeneral_journal_hdr b ON a.ftpurchase_invoice_no = b.`ftdocument_no` AND b.fnstatus != 9 
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' AND b.ftsource LIKE "pembelian_stock_masuk%" 
GROUP BY a.ftpurchase_invoice_no, b.ftsource
HAVING COUNT(a.ftpurchase_invoice_no) > 1
ORDER BY a.ftpurchase_invoice_no

-- cek pembelian  tdk ada di gl
SELECT DISTINCT fdtrans_date,ftpurchase_invoice_no,fnstatus FROM txpurchase_invoice_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9 AND a.fdtrans_date >= '2019-01-01'  
	AND ftpurchase_invoice_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource LIKE "pembelian_stock_masuk%"  )


-- query cek jurnal yg tdk ada di pembelian
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource LIKE "pembelian_stock_masuk%"  AND ftdocument_no NOT IN 
(SELECT ftdocument_no  FROM txpurchase_invoice_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fdtrans_date >= '2019-01-01' )


-- cek harga stock masuk = harga pi
SELECT a.ftpurchase_invoice_no,b.ftstock_in_no,b.ftitem_code,b.fcprice,(d.fcprice)
FROM txpurchase_invoice_hdr a 
INNER JOIN txpurchase_invoice_det b ON a.ftpurchase_invoice_no = b.ftpurchase_invoice_no 
LEFT JOIN txstock_in_hdr c ON c.ftdocument_no = a.ftpo_no AND ftcategory_code = 0
LEFT JOIN txstock_in_det d ON c.ftstock_in_no = d.ftstock_in_no AND b.ftitem_code = d.ftitem_code
WHERE  a.fnstatus = 1 AND c.fnstatus = 1 AND b.ftitem_code != 'A9999-001'   AND b.fcprice * a.fcrate != d.fcprice             
                
-- cek harga stock masuk = harga fifo                 
SELECT a.ftdocument_no,b.ftstock_in_no,a.ftitem_code,a.fcnet_price,(c.fcprice)FROM txstockfifo a 
INNER JOIN txstock_in_hdr b ON a.ftdocument_no = b.ftstock_in_no AND ftcategory_code = 0
INNER JOIN txstock_in_det c ON c.ftstock_in_no = b.ftstock_in_no AND a.ftitem_code = c.ftitem_code
WHERE a.fnqty > 0 AND fcnet_price != fcprice AND a.ftitem_code != 'A9999-001' AND a.fnstatus = 1 AND b.fnstatus = 1


// KEUANGAN
// KAS KELUAR
SELECT * FROM txcashpym_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftcashpym_no ASC
-- 533

// QUERY cek jumlah gl utk sales invoice        
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource = "kas_keluar" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.fdtrans_date ASC
-- 533

SELECT a.ftcashpym_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftcashpym_no)
FROM txcashpym_hdr a LEFT JOIN txgeneral_journal_hdr b ON a.ftcashpym_no = b.`ftdocument_no` AND b.fnstatus != 9 
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01'
GROUP BY a.ftcashpym_no
HAVING COUNT(a.ftcashpym_no) > 1
ORDER BY COUNT(a.ftcashpym_no)  DESC

SELECT DISTINCT ftcashpym_no,fnstatus FROM txcashpym_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9  AND a.fdtrans_date >= '2019-01-01'  
	AND ftcashpym_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource ='kas_keluar' )

// QUERY cek jurnal yg tdk ada di cashpym
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource ='kas_keluar' AND ftdocument_no NOT IN 
(SELECT ftcashpym_no  FROM txcashpym_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' )

// cek kas ada nominal tapi jurnal 0 
SELECT DISTINCT c.ftcashpym_no, c.fcamount, a.fdtrans_date, a.ftjournal_no, a.ftdocument_no, a.fcdebit, a.fccredit, c.fcrate -- , b.*  
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no` 
LEFT JOIN txcashpym_hdr c ON a.`ftdocument_no` = c.ftcashpym_no 
WHERE a.ftsource = 'kas_keluar' AND a.fcdebit != c.fcamount AND a.fnstatus = 1 AND c.fnstatus = 1
	AND a.fdtrans_date >= '2019-01-01' AND a.fnstatus = 1 AND c.fcamount != 0 
ORDER BY a.ftdocument_no 
        
// KAS MASUK
-- query cek jumlah transaksi kas masuk
SELECT * FROM txcashrcp_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftcashrcp_no ASC
-- 10

// QUERY cek jumlah gl utk sales invoice        
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource = "kas_masuk" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.fdtrans_date ASC
-- 10

SELECT a.ftcashrcp_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftcashrcp_no)
FROM txcashrcp_hdr a LEFT JOIN txgeneral_journal_hdr b ON a.ftcashrcp_no = b.`ftdocument_no` AND b.fnstatus != 9 
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01'
GROUP BY a.ftcashrcp_no
HAVING COUNT(a.ftcashrcp_no) > 1
ORDER BY COUNT(a.ftcashrcp_no)  DESC

SELECT DISTINCT ftcashrcp_no,fnstatus FROM txcashrcp_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9  AND a.fdtrans_date >= '2019-01-01'  
	AND ftcashrcp_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource ='kas_masuk' )

// QUERY cek jurnal yg tdk ada di kas masuk
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource ='kas_masuk' AND ftdocument_no NOT IN 
(SELECT ftcashrcp_no  FROM txcashrcp_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' )

-- cek kas ada nominal tapi jurnal 0 
SELECT DISTINCT c.ftcashrcp_no, c.fcamount, a.fdtrans_date, a.ftjournal_no, a.ftdocument_no, a.fcdebit, a.fccredit, c.fcrate -- , b.*  
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no` 
LEFT JOIN txcashrcp_hdr c ON a.`ftdocument_no` = c.ftcashrcp_no 
WHERE a.ftsource = 'kas_masuk' AND a.fcdebit != c.fcamount AND a.fnstatus = 1 AND c.fnstatus = 1
	AND a.fdtrans_date >= '2019-01-01' AND a.fnstatus = 1 AND c.fcamount != 0 
ORDER BY a.ftdocument_no 
         
// BANK MASUK
-- query cek jumlah transaksi bank masuk
SELECT * FROM txbankrcp_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftbankrcp_no ASC
-- 13

// QUERY cek jumlah gl utk bank masuk     
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource = "Bank_masuk" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.fdtrans_date ASC
-- 13

SELECT a.ftbankrcp_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftbankrcp_no)
FROM txbankrcp_hdr a LEFT JOIN txgeneral_journal_hdr b ON a.ftbankrcp_no = b.`ftdocument_no` AND b.fnstatus != 9 
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01'
GROUP BY a.ftbankrcp_no
HAVING COUNT(a.ftbankrcp_no) > 1
ORDER BY COUNT(a.ftbankrcp_no)  DESC

SELECT DISTINCT ftbankrcp_no,fnstatus FROM txbankrcp_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9  AND a.fdtrans_date >= '2019-01-01'  
	AND ftbankrcp_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource ='bank_masuk' )

// QUERY cek jurnal yg tdk ada di bank masuk
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource ='bank_masuk' AND ftdocument_no NOT IN 
(SELECT ftbankrcp_no  FROM txbankrcp_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' )

-- cek kas ada nominal tapi jurnal 0 
SELECT DISTINCT c.ftbankrcp_no, c.fcamount, a.fdtrans_date, a.ftjournal_no, a.ftdocument_no, a.fcdebit, a.fccredit, c.fcrate -- , b.*  
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no` 
LEFT JOIN txbankrcp_hdr c ON a.`ftdocument_no` = c.ftbankrcp_no 
WHERE a.ftsource = 'bank_masuk' AND a.fcdebit != c.fcamount AND a.fnstatus = 1 AND c.fnstatus = 1
	AND a.fdtrans_date >= '2019-01-01' AND a.fnstatus = 1 AND c.fcamount != 0 
ORDER BY a.ftdocument_no 
        
// BANK KELUAR
-- query cek jumlah transaksi bank keluar
SELECT * FROM txbankpym_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftbankpym_no ASC
-- 286

// QUERY cek jumlah gl utk bank keluar     
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource = "bank_keluar" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.fdtrans_date ASC
-- 286

-- SELECT a.ftbankpym_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftbankpym_no)
SELECT b.*
FROM txbankpym_hdr a LEFT JOIN txgeneral_journal_hdr b ON a.ftbankpym_no = b.`ftdocument_no` AND b.fnstatus != 9 
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01'
GROUP BY a.ftbankpym_no, b.ftsource
HAVING COUNT(a.ftbankpym_no) > 1
ORDER BY a.ftbankpym_no

SELECT DISTINCT fdtrans_date,ftbankpym_no,fnstatus FROM txbankpym_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9  AND a.fdtrans_date >= '2019-01-01'  
	AND ftbankpym_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource ='bank_keluar' )

// QUERY cek jurnal yg tdk ada di bank keluar
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource ='bank_keluar' AND ftdocument_no NOT IN 
(SELECT ftbankpym_no  FROM txbankpym_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' )

-- cek bank ada nominal tapi jurnal 0 
SELECT DISTINCT c.ftbankpym_no, c.fcamount, a.fdtrans_date, a.ftjournal_no, a.ftdocument_no, a.fcdebit, a.fccredit, c.fcrate -- , b.*  
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no` 
LEFT JOIN txbankpym_hdr c ON a.`ftdocument_no` = c.ftbankpym_no 
WHERE a.ftsource = 'bank_keluar' AND a.fcdebit != c.fcamount AND a.fnstatus = 1 AND c.fnstatus = 1
	AND a.fdtrans_date >= '2019-01-01' AND a.fnstatus = 1 AND c.fcamount != 0 
ORDER BY a.ftdocument_no 
        
// PIUTANG
-- query cek jumlah transaksi piutang 
SELECT * FROM txar_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftar_no ASC
-- 911

-- Query cek jumlah gl utk bank piutang     
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource = "penerimaan_piutang" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.fdtrans_date ASC
-- 911

SELECT a.ftar_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftar_no)
FROM txar_hdr a LEFT JOIN txgeneral_journal_hdr b ON a.ftar_no = b.`ftdocument_no` AND b.fnstatus != 9 
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' AND b.ftsource = 'penerimaan_piutang'
GROUP BY a.ftar_no, b.ftsource 
HAVING COUNT(a.ftar_no) > 1
ORDER BY a.ftar_no

-- cek piutang tdk ada di gl
SELECT DISTINCT fdtrans_date,ftar_no,fnstatus FROM txar_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9  AND a.fdtrans_date >= '2019-01-01'  
	AND ftar_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource ='penerimaan_piutang' )

-- query cek jurnal yg tdk ada di piutang
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource ='penerimaan_piutang' AND ftdocument_no NOT IN 
(SELECT ftar_no  FROM txar_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' )

-- cek inv piutang yg tdk cocok di gl
SELECT DISTINCT c.ftar_no, c.fcinvoice_amount, c.fcother_cost, (SUM(b.fccredit) - SUM(b.fcdebit)) AS gltotal, SUM(d.fcpayment_amount_base) AS invtotal, 
		a.fdtrans_date, a.ftjournal_no, a.ftdocument_no
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no` 
INNER JOIN txar_hdr c ON a.`ftdocument_no` = c.ftar_no 
INNER JOIN txar_det d ON c.ftar_no = d.ftar_no 
WHERE a.ftsource = 'penerimaan_piutang' AND a.fnstatus = 1 AND c.fnstatus = 1 
	AND a.fdtrans_date >= '2019-01-01'  
	 AND b.ftcoa_code = '1121.0100'
GROUP BY c.ftar_no, a.ftjournal_no
HAVING gltotal!=invtotal
ORDER BY a.ftdocument_no 

-- cek penerimaan piutang dgn gl
SELECT DISTINCT c.ftar_no, c.fcother_cost, SUM(d.fcamount) AS invtotal, SUM(b.fcdebit) AS gltotal,
		-- (SUM(b.fcdebit) - SUM(b.fccredit)) AS gltotal, d.fcamount as invtotal, -- , sum(d.fcamount)+c.fcother_cost AS invtotal, 
		a.fdtrans_date, a.ftjournal_no, a.ftdocument_no
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no` 
INNER JOIN txar_hdr c ON a.`ftdocument_no` = c.ftar_no 
INNER JOIN txar_det1 d ON c.ftar_no = d.ftar_no AND b.fcdebit = d.fcamount  
WHERE a.ftsource = 'penerimaan_piutang' AND a.fnstatus = 1 AND c.fnstatus = 1 
	AND a.fdtrans_date >= '2019-01-01'  
	 AND b.ftcoa_code != '1121.0100'
GROUP BY c.ftar_no, a.ftjournal_no, d.ftar_no
HAVING gltotal!=invtotal
ORDER BY a.ftdocument_no 

// HUTANG
-- query cek jumlah transaksi piutang 
SELECT * FROM txrecap_bank_voucher_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftcvs_no ASC
-- 101

-- Query cek jumlah gl utk bank piutang     
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource LIKE "rekap_bank_voucher%" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.fdtrans_date ASC
-- 101

SELECT a.ftcvs_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftcvs_no)
FROM txrecap_bank_voucher_hdr a LEFT JOIN txgeneral_journal_hdr b ON a.ftcvs_no = b.`ftdocument_no` AND b.fnstatus != 9 
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' AND b.ftsource LIKE "rekap_bank_voucher%" 
GROUP BY a.ftcvs_no, b.ftsource 
HAVING COUNT(a.ftcvs_no) > 1
ORDER BY a.ftcvs_no

-- cek hutang tdk ada di gl
SELECT DISTINCT fdtrans_date,ftcvs_no,fnstatus FROM txrecap_bank_voucher_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9  AND a.fdtrans_date >= '2019-01-01'  
	AND ftcvs_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource LIKE "rekap_bank_voucher%"  )

-- query cek jurnal yg tdk ada di hutang
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource LIKE "rekap_bank_voucher%"  AND ftdocument_no NOT IN 
(SELECT ftcvs_no  FROM txrecap_bank_voucher_hdr a
WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.fdtrans_date >= '2019-01-01' )

-- cek inv hutang yg tdk cocok di gl
SELECT DISTINCT c.ftcvs_no, c.fcinvoice_amount, c.fcother_cost, (SUM(b.fcdebit) - SUM(b.fccredit)) AS gltotal, SUM(d.fcpayment_amount) AS invtotal, 
		a.fdtrans_date, a.ftjournal_no, a.ftdocument_no
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no` 
INNER JOIN txrecap_bank_voucher_hdr c ON a.`ftdocument_no` = c.ftcvs_no 
INNER JOIN txrecap_bank_voucher_det d ON c.ftcvs_no = d.ftcvs_no 
WHERE a.ftsource LIKE "rekap_bank_voucher%" AND a.fnstatus = 1 AND c.fnstatus = 1 
	AND a.fdtrans_date >= '2019-01-01'  
	 AND b.ftcoa_code = '2110.0001'
GROUP BY c.ftcvs_no, a.ftjournal_no
HAVING gltotal!=invtotal
ORDER BY a.ftdocument_no 

-- cek penerimaan hutang dgn gl
SELECT DISTINCT c.ftcvs_no, c.fcother_cost, SUM(d.fcamount) AS invtotal, SUM(b.fccredit) AS gltotal,
		a.fdtrans_date, a.ftjournal_no, a.ftdocument_no
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no` 
INNER JOIN txrecap_bank_voucher_hdr c ON a.`ftdocument_no` = c.ftcvs_no 
INNER JOIN txrecap_bank_voucher_det1 d ON c.ftcvs_no = d.ftcvs_no AND b.fccredit = d.fcamount  
WHERE a.ftsource LIKE "rekap_bank_voucher%" AND a.fnstatus = 1 AND c.fnstatus = 1 
	AND a.fdtrans_date >= '2019-01-01'  
	 AND b.ftcoa_code != '2110.0001'
GROUP BY c.ftcvs_no, a.ftjournal_no, d.ftcvs_no
HAVING gltotal!=invtotal
ORDER BY a.ftdocument_no 

// giro mundur piutang
        -- query cek jumlah transaksi giro mundur piutang 
SELECT * FROM txgiro_hist a
WHERE a.fnstatus_giro != 3 AND a.fnstatus_giro != 9 AND a.fnstatus_giro != 0  AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftdocument_no ASC
-- 160

-- Query cek jumlah gl utk giro mundur piutang   
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource LIKE "giro_mundur_piutang%" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.ftdocument_no ASC
-- 160

SELECT a.ftgiro_no, a.ftdocument_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftdocument_no)
FROM txgiro_hist a INNER JOIN txgeneral_journal_hdr b ON a.ftdocument_no = b.`ftdocument_no` AND b.fnstatus != 9 
INNER JOIN txgeneral_journal_det c ON b.ftjournal_no = c.ftjournal_no AND a.ftgiro_no = c.ftdescription AND c.fcdebit = a.fctotal 
WHERE a.fnstatus_giro != 3 AND a.fnstatus_giro != 9  AND a.fdtrans_date >= '2019-01-01' AND b.ftsource LIKE "giro_mundur_piutang%" 
GROUP BY a.ftdocument_no, b.ftsource , a.ftgiro_no
HAVING COUNT(a.ftdocument_no) > 1
ORDER BY a.ftdocument_no

-- cek giro mundur piutang    tdk ada di gl
SELECT DISTINCT fdtrans_date,ftdocument_no,fnstatus_giro FROM txgiro_hist a
WHERE fnstatus_giro != 3 AND fnstatus_giro!= 9 AND fnstatus_giro!= 0  AND a.fdtrans_date >= '2019-01-01'  
	AND ftdocument_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource LIKE "giro_mundur_piutang%"  )


-- cek giro mundur piutang    tdk ada di gl
SELECT DISTINCT fdtrans_date,ftdocument_no,fnstatus_giro,ftgiro_no,fdgiro_cliring_date,a.fctotal FROM txgiro_hist a
WHERE fnstatus_giro != 3 AND fnstatus_giro!= 9 AND fnstatus_giro!= 0  AND a.fdtrans_date >= '2019-01-01'  
	AND ftgiro_no NOT IN 
(SELECT ftdescription  FROM txgeneral_journal_hdr a INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no
WHERE a.fnstatus = 1 AND ftsource LIKE "giro_mundur_piutang%"  )


-- query cek jurnal yg tdk ada di giro mundur piutang   
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource LIKE "giro_mundur_piutang%"  AND ftdocument_no NOT IN 
(SELECT ftdocument_no  FROM txgiro_hist a
WHERE a.fnstatus_giro != 3 AND a.fnstatus_giro != 9 AND a.fnstatus_giro != 0 AND a.fdtrans_date >= '2019-01-01' )

// GIRO MUNDUR HUTANG
-- query cek jumlah transaksi giro mundur hutang 
SELECT * FROM txgiro2_hist a
WHERE a.fnstatus_giro != 3 AND a.fnstatus_giro != 9 AND a.fnstatus_giro != 0  AND a.fdtrans_date >= '2019-01-01' 
ORDER BY a.ftdocument_no ASC
-- 36

-- Query cek jumlah gl utk giro mundur hutang   
SELECT fdtrans_date,ftdocument_no,fnstatus FROM txgeneral_journal_hdr a 
WHERE a.fnstatus != 3 AND a.fnstatus != 9 AND a.ftsource LIKE "giro_mundur_hutang%" AND a.fdtrans_date >= '2019-01-01'
ORDER BY a.ftdocument_no ASC
-- 36

SELECT a.ftgiro_no, a.ftdocument_no, b.`ftjournal_no`, b.`ftdocument_no`, COUNT(a.ftdocument_no)
FROM txgiro2_hist a INNER JOIN txgeneral_journal_hdr b ON a.ftdocument_no = b.`ftdocument_no` AND b.fnstatus != 9 
INNER JOIN txgeneral_journal_det c ON b.ftjournal_no = c.ftjournal_no AND a.ftgiro_no = c.ftdescription AND c.fcdebit = a.fctotal 
WHERE a.fnstatus_giro != 3 AND a.fnstatus_giro != 9  AND a.fdtrans_date >= '2019-01-01' AND b.ftsource LIKE "giro_mundur_hutang%" 
GROUP BY a.ftdocument_no, b.ftsource , a.ftgiro_no
HAVING COUNT(a.ftdocument_no) > 1
ORDER BY a.ftdocument_no

-- cek giro mundur hutang    tdk ada di gl
SELECT DISTINCT fdtrans_date,ftdocument_no,fnstatus_giro FROM txgiro2_hist a
WHERE fnstatus_giro != 3 AND fnstatus_giro!= 9 AND fnstatus_giro!= 0  AND a.fdtrans_date >= '2019-01-01'  
	AND ftdocument_no NOT IN 
(SELECT ftdocument_no  FROM txgeneral_journal_hdr a
WHERE a.fnstatus = 1 AND ftsource LIKE "giro_mundur_hutang%"  )


-- cek giro mundur hutang    tdk ada di gl
SELECT DISTINCT fdtrans_date,ftdocument_no,fnstatus_giro,ftgiro_no,fdgiro_cliring_date,a.fctotal FROM txgiro2_hist a
WHERE fnstatus_giro != 3 AND fnstatus_giro!= 9 AND fnstatus_giro!= 0  AND a.fdtrans_date >= '2019-01-01'  
	AND ftgiro_no NOT IN 
(SELECT ftdescription  FROM txgeneral_journal_hdr a INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no
WHERE a.fnstatus = 1 AND ftsource LIKE "giro_mundur_hutang%"  )


-- query cek jurnal yg tdk ada di giro mundur hutang   
SELECT DISTINCT ftdocument_no,fnstatus FROM txgeneral_journal_hdr
WHERE fnstatus = 1 AND ftsource LIKE "giro_mundur_hutang%"  AND ftdocument_no NOT IN 
(SELECT ftdocument_no  FROM txgiro2_hist a
WHERE a.fnstatus_giro != 3 AND a.fnstatus_giro != 9 AND a.fnstatus_giro != 0 AND a.fdtrans_date >= '2019-01-01' )

        
        
        
// AKUNTING
// CEK JURNAL 

// cek total hdr  != total detail
SELECT a.ftjournal_no,a.ftdocument_no,a.fcdebit,a.fccredit, SUM(IFNULL(b.fcdebit,0)), SUM(IFNULL(b.fccredit,0)) 
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no`
WHERE a.fdtrans_date >= '2019-01-01' AND a.fnstatus = 1
GROUP BY a.ftjournal_no 
HAVING a.fcdebit != SUM(IFNULL(b.fcdebit,0)) OR a.fccredit != SUM(IFNULL(b.fccredit,0)) 

// update jurnal total hdr = total detail
UPDATE txgeneral_journal_hdr a LEFT JOIN 
(SELECT a.ftjournal_no,a.ftdocument_no,a.fcdebit,a.fccredit, SUM(IFNULL(b.fcdebit,0)) AS total_debit, SUM(IFNULL(b.fccredit,0)) AS total_credit 
FROM txgeneral_journal_hdr a LEFT JOIN txgeneral_journal_det b ON a.`ftjournal_no` = b.`ftjournal_no`
WHERE a.fdtrans_date >= '2019-01-01' AND a.fnstatus = 1
GROUP BY a.ftjournal_no 
HAVING a.fcdebit != SUM(IFNULL(b.fcdebit,0)) OR a.fccredit != SUM(IFNULL(b.fccredit,0)) 
) X 
ON a.`ftjournal_no` = x.`ftjournal_no`
SET  a.fcdebit = x.total_debit, a.fccredit = x.total_credit 
WHERE a.fdtrans_date >= '2019-01-01' AND a.fnstatus = 1
	AND (a.fcdebit != total_debit OR a.fccredit != total_credit) 
        
        
        
        
// FIFO
-- cek fifo outstanding dgn stock detail tdk sesuai
SELECT a.ftitem_code,fnqty_available,SUM(fnqty_outstanding) 
FROM tlstock_det a INNER JOIN txstockfifo b ON a.ftitem_code = b.ftitem_code AND a.ftlocation_code = b.ftwarehouse_code 
GROUP BY a.ftitem_code,a.ftlocation_code
HAVING fnqty_available !=SUM(fnqty_outstanding) 
        
-- cek fifo outstanding tdk sesuai
SELECT a.fnidfifo,a.fnidfiforef,a.ftitem_code,a.ftwarehouse_code, a.fnqty, a.fnqty_outstanding, 
	IFNULL(b.qty_out,0), ROUND(a.fnqty + IFNULL(b.qty_out,0),2) AS real_outstanding
FROM txstockfifo  a
LEFT JOIN (SELECT fnidfiforef, IFNULL(SUM(ROUND(b.fnqty,2)),0) AS qty_out, ftitem_code, ftwarehouse_code  
		FROM txstockfifo b
		WHERE b.fnstatus = 1 AND b.fnqty < 0 
		GROUP BY b.fnidfiforef ) b ON a.fnidfifo = b.fnidfiforef 
WHERE a.fnstatus = 1 AND a.fnqty > 0 -- and a.ftitem_code = 'Y0101-066'
	AND ROUND(a.fnqty_outstanding,2) != ROUND(a.fnqty + IFNULL(b.qty_out,0),2)
ORDER BY ftitem_code,fnidfiforef

-- update fifo outstanding tdk sesuai
UPDATE txstockfifo  a
LEFT JOIN (SELECT fnidfiforef, IFNULL(SUM(ROUND(b.fnqty,2)),0) AS qty_out, ftitem_code, ftwarehouse_code  
		FROM txstockfifo b
		WHERE b.fnstatus = 1 AND b.fnqty < 0 
		GROUP BY b.fnidfiforef ) b ON a.fnidfifo = b.fnidfiforef 
SET a.fnqty_outstanding = ROUND(a.fnqty + IFNULL(b.qty_out,0),2)
WHERE a.fnstatus = 1 AND a.fnqty > 0 
	AND ROUND(a.fnqty_outstanding,2) != ROUND(a.fnqty + IFNULL(b.qty_out,0),2)

-- cek total qty dan outstanding secara keseluruhan (seharusnya sama)
SELECT ftitem_code,SUM(fnqty) , SUM(fnqty_outstanding)
FROM txstockfifo
WHERE fnstatus = 1        
        
-- cek total qty dan outstanding secara warehouse dan item (seharusnya sama)
SELECT ftitem_code,ftwarehouse_code, SUM(fnqty) , SUM(fnqty_outstanding)
FROM txstockfifo
WHERE fnstatus = 1
GROUP BY ftitem_code,ftwarehouse_code
HAVING ROUND(SUM(fnqty),2) != ROUND(SUM(fnqty_outstanding),2)

        
// inventory
-- cek sm kecuali retur tdk ada di fifo
SELECT DISTINCT fdtrans_date,ftstock_in_no,fnstatus,ftcategory_code FROM txstock_in_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9 AND a.ftcategory_code != 4 -- AND a.ftcategory_code != 5 AND a.ftcategory_code != 11 -- AND a.fdtrans_date >= '2019-01-01'    AND a.ftcategory_code = 0 
	AND ftstock_in_no NOT IN 
(SELECT ftdocument_no  FROM txstockfifo a
WHERE a.fnstatus = 1  )

-- cek item sm kecuali retur tdk ada di fifo
SELECT DISTINCT a.ftstock_in_no,a.ftcategory_code, COUNT(b.fnindex) AS tstock, COUNT(c.fnindex) AS tfifo 
FROM txstock_in_hdr a INNER JOIN txstock_in_det b ON a.ftstock_in_no = b.ftstock_in_no
LEFT JOIN txstockfifo c ON a.ftstock_in_no = c.ftdocument_no  AND c.fnstatus = 1 
WHERE a.fnstatus = 1  AND a.ftcategory_code != 4 
GROUP BY a.ftstock_in_no
HAVING tstock != tfifo

-- cek and compare item and qty SM tdk sama di fifo
SELECT fttrans_no, SUM(tstock) AS tstock, SUM(tfifo) AS tfifo
FROM (
	SELECT DISTINCT a.ftstock_in_no AS fttrans_no,SUM(b.fnqty) AS tstock , 0 AS tfifo
	FROM txstock_in_hdr a INNER JOIN txstock_in_det b ON a.ftstock_in_no = b.ftstock_in_no
	WHERE a.fnstatus = 1 AND a.ftcategory_code != 4  -- and a.ftstock_out_no = 'SK/DO190301027'
	GROUP BY a.ftstock_in_no
	UNION ALL 
	SELECT DISTINCT a.ftdocument_no AS fttrans_no,0 AS tstock, SUM(a.fnqty) AS tfifo 
	FROM txstockfifo a 
	WHERE a.fnstatus = 1 AND fnqty > 0  -- AND a.ftdocument_no = 'SK/DO190301027'
	GROUP BY a.ftdocument_no
) X 
WHERE fttrans_no NOT LIKE 'IS%' AND fttrans_no NOT LIKE 'SR%' AND fttrans_no NOT LIKE 'MS%'  AND fttrans_no NOT LIKE 'PS%'
GROUP BY fttrans_no
HAVING tstock != tfifo

-- cek and compare item and qty SM tdk sama di fifo ?? no need ?
SELECT DISTINCT a.fdtrans_date,a.ftstock_in_no,a.fnstatus,a.ftcategory_code,b.ftitem_code,b.fnqty,c.ftdocument_no,c.ftitem_code,c.fnqty 
FROM txstock_in_hdr a INNER JOIN txstock_in_det b ON a.ftstock_in_no = b.ftstock_in_no
LEFT JOIN txstockfifo c ON c.ftdocument_no = a.ftstock_in_no AND b.ftitem_code = c.ftitem_code	AND c.fnstatus = 1  
WHERE a.fnstatus != 3 AND a.fnstatus!= 9 AND a.ftcategory_code != 4 
		AND c.ftitem_code IS NULL
	

-- cek item adjustment tdk ada di fifo
SELECT DISTINCT a.ftstock_adjustment_no, COUNT(b.fnindex) AS tstock, COUNT(c.fnindex) AS tfifo 
FROM txstock_adjustment_hdr a INNER JOIN txstock_adjustment_det b ON a.ftstock_adjustment_no = b.ftstock_adjustment_no
LEFT JOIN txstockfifo c ON a.ftstock_adjustment_no = c.ftdocument_no  AND c.fnstatus = 1 AND c.fttableref = 'txstock_adjustment_det'
WHERE a.fnstatus = 2  
GROUP BY a.ftstock_adjustment_no
HAVING tstock != tfifo		

-- cek and compare item and qty adjustment tdk sama di fifo
SELECT DISTINCT a.fdtrans_date,a.ftstock_adjustment_no,a.fnstatus,b.ftitem_code,b.fnqty,c.ftdocument_no,c.ftitem_code,c.fnqty 
FROM txstock_adjustment_hdr a INNER JOIN txstock_adjustment_det b ON a.ftstock_adjustment_no = b.ftstock_adjustment_no
LEFT JOIN txstockfifo c ON c.ftdocument_no = a.ftstock_adjustment_no AND b.ftitem_code = c.ftitem_code	AND c.fnstatus = 1  
WHERE a.fnstatus != 3 AND a.fnstatus!= 9 
		AND c.ftitem_code IS NULL
	
-- cek item assembly hdr tdk ada di fifo
SELECT DISTINCT a.ftassembly_no, 1 AS tstock, COUNT(c.fnindex) AS tfifo 
FROM txassembly_hdr a -- INNER JOIN txassembly_det b ON a.ftassembly_no = b.ftassembly_no
LEFT JOIN txstockfifo c ON a.ftassembly_no = c.ftdocument_no
	AND  c.fnstatus = 1 AND c.fttableref = 'txassembly_hdr'   AND a.ftitem_code = c.ftitem_code AND c.fnqty > 0  
WHERE a.fnstatus = 1
GROUP BY a.ftassembly_no
HAVING tstock != tfifo		

-- cek and compare item assembly tdk sama di fifo
SELECT DISTINCT a.fdtrans_date,a.ftassembly_no,a.fnstatus,a.ftitem_code,a.fnqty,c.ftdocument_no,c.ftitem_code,c.fnqty 
FROM txassembly_hdr a 
LEFT JOIN txstockfifo c ON c.ftdocument_no = a.ftassembly_no AND a.ftitem_code = c.ftitem_code	AND c.fnstatus = 1 AND c.fnqty > 0  
WHERE a.fnstatus != 3 AND a.fnstatus!= 9 
		AND c.ftitem_code IS NULL	
		

-- cek item assembly det tdk ada di fifo
SELECT DISTINCT a.ftassembly_no, COUNT(b.fnindex) AS tstock, COUNT(c.fnindex) AS tfifo 
FROM txassembly_hdr a INNER JOIN txassembly_det b ON a.ftassembly_no = b.ftassembly_no
LEFT JOIN txstockfifo c ON a.ftassembly_no = c.ftdocument_no
	AND  c.fnstatus = 1 AND c.fttableref = 'txassembly_hdr' AND b.ftitem_code = c.ftitem_code -- AND c.fnqty < 0  
WHERE a.fnstatus = 1
GROUP BY a.ftassembly_no
HAVING tstock != tfifo		

-- cek and compare item and qty assembly tdk sama di fifo
SELECT DISTINCT a.fdtrans_date,a.ftassembly_no,a.fnstatus,b.ftitem_code,a.fnqty,c.ftdocument_no,c.ftitem_code,c.fnqty 
FROM txassembly_hdr a INNER JOIN txassembly_det b ON a.ftassembly_no = b.ftassembly_no
LEFT JOIN txstockfifo c ON c.ftdocument_no = a.ftassembly_no AND b.ftitem_code = c.ftitem_code	
	AND c.fnstatus = 1 AND c.fttableref = 'txassembly_hdr'  
WHERE a.fnstatus != 3 AND a.fnstatus!= 9 
		AND c.ftitem_code IS NULL	
				 		
				 		
-- cek item disassembly hdr tdk ada di fifo
SELECT DISTINCT a.ftdisassembly_no, 1 AS tstock, COUNT(c.fnindex) AS tfifo 
FROM txdisassembly_hdr a 
LEFT JOIN txstockfifo c ON a.ftdisassembly_no = c.ftdocument_no
	AND  c.fnstatus = 1 AND c.fttableref = 'txdisassembly_hdr'   AND a.ftitem_code = c.ftitem_code AND c.fnqty < 0  
WHERE a.fnstatus = 1
GROUP BY a.ftdisassembly_no
HAVING tstock != tfifo		

-- cek and compare item and qty disassembly tdk sama di fifo
SELECT DISTINCT a.fdtrans_date,a.ftdisassembly_no,a.fnstatus,a.ftitem_code,a.fnqty,c.ftdocument_no,c.ftitem_code,c.fnqty 
FROM txdisassembly_hdr a 
LEFT JOIN txstockfifo c ON c.ftdocument_no = a.ftdisassembly_no AND a.ftitem_code = c.ftitem_code AND c.fnstatus = 1 AND c.fnqty < 0  
WHERE a.fnstatus != 3 AND a.fnstatus!= 9 
		AND c.ftitem_code IS NULL	
		
-- cek item disassembly det tdk ada di fifo
SELECT DISTINCT a.ftdisassembly_no, COUNT(b.fnindex) AS tstock, COUNT(c.fnindex) AS tfifo 
FROM txdisassembly_hdr a INNER JOIN txdisassembly_det b ON a.ftdisassembly_no = b.ftdisassembly_no
LEFT JOIN txstockfifo c ON a.ftdisassembly_no = c.ftdocument_no
	AND  c.fnstatus = 1 AND c.fttableref = 'txdisassembly_hdr' AND b.ftitem_code = c.ftitem_code -- AND c.fnqty < 0  
WHERE a.fnstatus = 1
GROUP BY a.ftdisassembly_no
HAVING tstock != tfifo		

-- cek and compare item and qty assembly tdk sama di fifo
SELECT DISTINCT a.fdtrans_date,a.ftdisassembly_no,a.fnstatus,b.ftitem_code,a.fnqty,c.ftdocument_no,c.ftitem_code,c.fnqty 
FROM txdisassembly_hdr a INNER JOIN txdisassembly_det b ON a.ftdisassembly_no = b.ftdisassembly_no
LEFT JOIN txstockfifo c ON c.ftdocument_no = a.ftdisassembly_no AND b.ftitem_code = c.ftitem_code	
	AND c.fnstatus = 1 AND c.fttableref = 'txdisassembly_hdr'  
WHERE a.fnstatus != 3 AND a.fnstatus!= 9 
		AND c.ftitem_code IS NULL	
				 		
				
-- cek stock non inv
SELECT DISTINCT a.ftitem_code FROM txstockfifo a INNER JOIN tlstock_hdr b ON a.ftitem_code = b.`ftitem_code`
WHERE a.fnstatus = 1 AND b.ftstock_category_code != 'inv'

-- batal fifo karena stock non inv
-- UPDATE txstockfifo SET fnstatus = 9 WHERE ftitem_code = '11111-001';
-- UPDATE txstockfifo SET fnstatus = 9 WHERE ftitem_code = '99999';
-- UPDATE txstockfifo SET fnstatus = 9 WHERE ftitem_code = 'A9999-001';	
	 
-- cek stock out tdk ada di fifo
SELECT DISTINCT a.fdtrans_date,a.ftstock_out_no,a.fnstatus,c.ftdocument_no,c.fnstatus 
FROM txstock_out_hdr a INNER JOIN txstock_out_det b ON a.ftstock_out_no = b.ftstock_out_no
INNER JOIN tlstock_hdr d ON b.ftitem_code = d.ftitem_code AND d.ftstock_category_code = 'inv'	
LEFT JOIN txstockfifo c ON c.ftdocument_no = a.ftstock_out_no AND c.fnstatus = 1	
WHERE a.fnstatus = 1 AND c.ftdocument_no IS NULL 


-- cek item sk tdk ada di fifo
SELECT DISTINCT fttrans_no, ROUND(SUM(tstock),2) AS tstock, ROUND(SUM(tfifo),2) AS tfifo
FROM (
	SELECT DISTINCT a.ftstock_out_no AS fttrans_no,SUM(b.fnqty) AS tstock , 0 AS tfifo
	FROM txstock_out_hdr a INNER JOIN txstock_out_det b ON a.ftstock_out_no = b.ftstock_out_no
	INNER JOIN tlstock_hdr c ON b.ftitem_code = c.ftitem_code AND c.ftstock_category_code = 'inv'
	WHERE a.fnstatus = 1 
	GROUP BY a.ftstock_out_no
	UNION ALL 
	SELECT DISTINCT a.ftdocument_no AS fttrans_no,0 AS tstock, SUM(a.fnqty) * -1 AS tfifo 
	FROM txstockfifo a 
	WHERE a.fnstatus = 1 AND fnqty < 0  
	GROUP BY a.ftdocument_no
) X 
WHERE fttrans_no NOT LIKE 'AS%' AND fttrans_no NOT LIKE 'DS%' AND fttrans_no NOT LIKE 'MS%' AND fttrans_no NOT LIKE 'PS%'
GROUP BY fttrans_no
HAVING tstock != tfifo
 
-- hapus sk yg tdk lengkap / salah di fifo
DELETE FROM txstockfifo WHERE ftdocument_no IN 
	( SELECT  fttrans_no FROM (
		SELECT DISTINCT fttrans_no, ROUND(SUM(tstock),2) AS tstock, ROUND(SUM(tfifo),2) AS tfifo
		FROM (
			SELECT DISTINCT a.ftstock_out_no AS fttrans_no,SUM(b.fnqty) AS tstock , 0 AS tfifo
			FROM txstock_out_hdr a INNER JOIN txstock_out_det b ON a.ftstock_out_no = b.ftstock_out_no
			WHERE a.fnstatus = 1 -- and a.ftstock_out_no = 'SK/DO190301027'
			GROUP BY a.ftstock_out_no
			UNION ALL 
			SELECT DISTINCT a.ftdocument_no AS fttrans_no,0 AS tstock, SUM(a.fnqty) * -1 AS tfifo 
			FROM txstockfifo a 
			WHERE a.fnstatus = 1 AND fnqty < 0 -- AND a.ftdocument_no = 'SK/DO190301027'
			GROUP BY a.ftdocument_no
		) X 
		WHERE fttrans_no NOT LIKE 'AS%' AND fttrans_no NOT LIKE 'DS%' AND fttrans_no NOT LIKE 'MS%' AND fttrans_no NOT LIKE 'PS%'
		GROUP BY fttrans_no
		HAVING tstock != tfifo ) Y 
	)  
	

-- cek retur tdk ada di stock masuk
SELECT DISTINCT fdtrans_date,ftsales_return_no,fnstatus FROM txsales_return_hdr a
WHERE fnstatus != 3 AND fnstatus!= 9  
	AND ftsales_return_no NOT IN 
(SELECT ftdocument_no  FROM txstock_in_hdr a
WHERE a.fnstatus = 1  )	


-- cek retur  lebih dr 1 stock masuk
SELECT a.ftsales_return_no, COUNT(b.ftdocument_no) AS counter FROM txsales_return_hdr a
INNER JOIN txstock_in_hdr b ON a.ftsales_return_no = b.ftdocument_no
WHERE a.fnstatus != 3 AND a.fnstatus!= 9  AND b.fnstatus = 1
GROUP BY a.ftsales_return_no
HAVING counter > 1

-- cek retur tdk ada di fifo 
SELECT DISTINCT a.fdtrans_date,a.ftsales_return_no,a.fnstatus 
FROM txsales_return_hdr a INNER JOIN txsales_return_det b ON a.ftsales_return_no = b.ftsales_return_no
INNER JOIN tlstock_hdr c ON b.ftitem_code = c.ftitem_code AND c.ftstock_category_code = 'inv'	
WHERE a.fnstatus != 3 AND a.fnstatus!= 9  
	AND a.ftsales_return_no NOT IN 
(SELECT ftdocument_no  FROM txstockfifo a
WHERE a.fnstatus = 1  )	
	
-- cek item sr tdk ada di fifo
SELECT DISTINCT fttrans_no, ftso_no, ROUND(SUM(tstock),2) AS tstock, ROUND(SUM(tfifo),2) AS tfifo
FROM (
	SELECT DISTINCT a.ftdocument_no AS fttrans_no,SUM(b.fnqty) AS tstock , 0 AS tfifo
	FROM txstock_in_hdr a INNER JOIN txstock_in_det b ON a.ftstock_in_no = b.ftstock_in_no
	INNER JOIN tlstock_hdr c ON b.ftitem_code = c.ftitem_code AND c.ftstock_category_code = 'inv'
	WHERE a.fnstatus = 1 AND a.ftcategory_code = 4	-- and a.ftdocument_no = 'SR190400076'
	GROUP BY a.ftstock_in_no
	UNION ALL 
	SELECT DISTINCT a.ftdocument_no AS fttrans_no,0 AS tstock, SUM(a.fnqty) AS tfifo 
	FROM txstockfifo a 
	WHERE a.fnstatus = 1 AND fnqty > 0 AND fttableref = 'txsales_return_det'
	GROUP BY a.ftdocument_no
) X 
INNER JOIN txsales_return_hdr z ON x.fttrans_no = z.ftsales_return_no
WHERE fttrans_no NOT LIKE 'AS%' AND fttrans_no NOT LIKE 'DS%' AND fttrans_no NOT LIKE 'MS%' AND fttrans_no NOT LIKE 'PS%'
	-- and ftso_no = ''	
GROUP BY fttrans_no
HAVING tstock != tfifo  

-- hapus sales retur yg tdk lengkap / salah di fifo
DELETE FROM txstockfifo WHERE ftdocument_no IN 
	( SELECT  fttrans_no FROM (
		SELECT DISTINCT fttrans_no, ROUND(SUM(tstock),2) AS tstock, ROUND(SUM(tfifo),2) AS tfifo
		FROM (
			SELECT DISTINCT a.ftdocument_no AS fttrans_no,SUM(b.fnqty) AS tstock , 0 AS tfifo
			FROM txstock_in_hdr a INNER JOIN txstock_in_det b ON a.ftstock_in_no = b.ftstock_in_no
			INNER JOIN tlstock_hdr c ON b.ftitem_code = c.ftitem_code AND c.ftstock_category_code = 'inv'
			WHERE a.fnstatus = 1 AND a.ftcategory_code = 4	-- and a.ftdocument_no = 'SR190400076'
			GROUP BY a.ftstock_in_no
			UNION ALL 
			SELECT DISTINCT a.ftdocument_no AS fttrans_no,0 AS tstock, SUM(a.fnqty) AS tfifo 
			FROM txstockfifo a 
			WHERE a.fnstatus = 1 AND fnqty > 0 AND fttableref = 'txsales_return_det'
			GROUP BY a.ftdocument_no
		) X 
		WHERE fttrans_no NOT LIKE 'AS%' AND fttrans_no NOT LIKE 'DS%' AND fttrans_no NOT LIKE 'MS%' AND fttrans_no NOT LIKE 'PS%'
		GROUP BY fttrans_no
		HAVING tstock != tfifo   ) Y 
	)  
		
SELECT * FROM txstockfifo WHERE fnqty = 0 AND fnstatus = 1
UPDATE txstockfifo SET fnstatus = 9 WHERE fnqty = 0 AND fnstatus = 1;

SELECT * FROM txsales_return_det WHERE fnqty != fnqty_base
UPDATE txsales_return_det SET fnqty_base = fnqty * ffformula WHERE fnqty != fnqty_base;
        
        
?>
