<?php   // tools to update Jurnal System
        session_cache_expire(1440);
        session_start();
        session_name("insys");
        include "include/config.php";
        include "include/functions.php";
        include "class/UctVw.php";

        /*
        // STOCK MASUK HEADER - BARANG JADI
        $ssql="SELECT a.fdtrans_date, a.ftstock_in_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'barang_masuk' AS ftsource,
                IFNULL(SUM(b.fcprice * b.fnqty_base),0) AS fcamount , '1' AS fcrate, a.ftstock_type_code
                FROM txstock_in_hdr a inner join txstock_in_det b ON a.ftstock_in_no=b.ftstock_in_no
                WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftcategory_code <> 0 and a.ftcategory_code <> 4 and a.ftstock_type_code = 'inv'
                GROUP BY a.fdtrans_date, a.ftstock_in_no
                ORDER BY a.fdtrans_date, a.ftstock_in_no";

        $rs=mysql_query($ssql);
        $ssql1=array();

        pr("*** INSERTING STOCK MASUK BARANG JADI TO JURNAL SYSTEM HDR ***");

        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");
            $ftjournal_no=getAutoNumber("acc001","txgeneral_journal_hdr","ftjournal_no",CDateRev($r['fdtrans_date']));

            $ssql="INSERT INTO txgeneral_journal_hdr(ftbranch_code,ftjournal_no,fdtrans_date,ftdocument_no, fcdebit, fccredit,
                        fnstatus,fntype,ftsource,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                        SELECT 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' AS ftjournal_no, '{$r['fdtrans_date']}' as fdtrans_date,
                        '{$r['ftdocument_no']}' AS ftdocument_no, {$r['fcamount']}*{$r['fcrate']} as fcdebit, {$r['fcamount']}*{$r['fcrate']}  as fccredit,
                        '1' AS fnstatus, '0' AS fntype,
                        '{$r['ftsource']}' AS ftsource, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                        'Admin' AS ftmodified_user,  now() AS fdmodified_date; ";

            $strSQL["insert txgeneral_journal_hdr"]=$ssql;
            mysql_query($ssql);
            //pr($ssql);
            mysql_query("COMMIT");
        }

        // STOCK MASUK BARANG JADI DETAIL
        pr("*** INSERTING STOCK MASUK BARANG JADI TO JURNAL SYSTEM DET ***");

        $ssql="INSERT INTO txgeneral_journal_det(ftbranch_code,ftjournal_no,fnindex,fdtrans_date,
                    ftcoa_code,ftdescription,fcdebit,fccredit,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                    SELECT * FROM (
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,(b.fnindex*2)-1 AS fnindex, a.fdtrans_date,
                    d.ftgl_inv_acc AS ftcoa_code,
                    CONCAT(a.ftstock_in_no,' - ',b.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    ifnull(b.fcprice*b.fnqty_base,0) AS fcdebit, '0' AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txstock_in_hdr a INNER JOIN txstock_in_det b ON a.ftstock_in_no = b.ftstock_in_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftstock_in_no = c.ftdocument_no AND c.ftsource='barang_masuk' AND c.fnstatus = 1
                    INNER JOIN tlstock_hdr d ON b.ftitem_code = d.ftitem_code
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,(b.fnindex*2) AS fnindex, a.fdtrans_date,
                    CASE WHEN a.ftcategory_code = 1 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I11')
			 WHEN a.ftcategory_code = 2 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I12')
			 WHEN a.ftcategory_code = 3 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I13')
			 WHEN a.ftcategory_code = 5 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I15')
			 WHEN a.ftcategory_code = 6 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I14')
                    END AS ftcoa_code,
                    CONCAT(a.ftstock_in_no,' - ',b.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, ifnull(b.fcprice*b.fnqty_base,0) AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txstock_in_hdr a INNER JOIN txstock_in_det b ON a.ftstock_in_no = b.ftstock_in_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftstock_in_no = c.ftdocument_no AND c.ftsource='barang_masuk' AND c.fnstatus = 1
                    INNER JOIN tlstock_hdr d ON b.ftitem_code = d.ftitem_code
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

        pr($ssql);
        mysql_query("BEGIN");
        $rs=mysql_query($ssql);
        mysql_query("COMMIT");

        // STOCK KELUAR HEADER - BARANG JADI
        $ssql="SELECT a.fdtrans_date, a.ftstock_out_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'barang_keluar' AS ftsource,
                IFNULL(SUM(ABS(c.fnQty)*c.fcNet_Price),0) AS fcamount , '1' AS fcrate
                FROM txstock_out_hdr a
                inner join txstock_out_det b ON a.ftstock_out_no=b.ftstock_out_no
                LEFT JOIN TXStockFIFO c on c.ftDocument_No=a.ftstock_out_no and c.ftItem_Code = b.ftItem_Code
                WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftcategory_code <> 0 and a.ftcategory_code <> 4
                GROUP BY a.fdtrans_date, a.ftstock_out_no
                ORDER BY a.fdtrans_date, a.ftstock_out_no";

        $rs=mysql_query($ssql);
        $ssql1=array();

        pr("*** INSERTING STOCK KELUAR BARANG JADI TO JURNAL SYSTEM HDR ***");

        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");
            $ftjournal_no=getAutoNumber("acc001","txgeneral_journal_hdr","ftjournal_no",CDateRev($r['fdtrans_date']));

            $ssql="INSERT INTO txgeneral_journal_hdr(ftbranch_code,ftjournal_no,fdtrans_date,ftdocument_no, fcdebit, fccredit,
                        fnstatus,fntype,ftsource,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                        SELECT 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' AS ftjournal_no, '{$r['fdtrans_date']}' as fdtrans_date,
                        '{$r['ftdocument_no']}' AS ftdocument_no, {$r['fcamount']}*{$r['fcrate']} as fcdebit, {$r['fcamount']}*{$r['fcrate']}  as fccredit,
                        '1' AS fnstatus, '0' AS fntype,
                        '{$r['ftsource']}' AS ftsource, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                        'Admin' AS ftmodified_user,  now() AS fdmodified_date; ";

            $strSQL["insert txgeneral_journal_hdr"]=$ssql;
            mysql_query($ssql);
            //pr($ssql);
            mysql_query("COMMIT");
        }


        // STOCK KELUAR BARANG JADI DETAIL
        pr("*** INSERTING STOCK KELUAR BARANG JADI TO JURNAL SYSTEM DET ***");

        $ssql="INSERT INTO txgeneral_journal_det(ftbranch_code,ftjournal_no,fnindex,fdtrans_date,
                    ftcoa_code,ftdescription,fcdebit,fccredit,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                    SELECT * FROM (

                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,(b.fnindex*2)-1 AS fnindex, a.fdtrans_date,
                    CASE WHEN a.ftcategory_code = 1 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I22')
			 WHEN a.ftcategory_code = 2 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I23')
			 WHEN a.ftcategory_code = 3 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I24')
			 WHEN a.ftcategory_code = 5 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I21')
			 WHEN a.ftcategory_code = 6 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I25')
                    END AS ftcoa_code,
                    CONCAT(a.ftstock_out_no,' - ',b.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    ifnull(sum(d.fcNet_Price*ABS(d.fnqty)),0) AS fcdebit, '0' AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txstock_out_hdr a INNER JOIN txstock_out_det b ON a.ftstock_out_no = b.ftstock_out_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftstock_out_no = c.ftdocument_no AND c.ftsource='barang_keluar' AND c.fnstatus = 1
                    LEFT JOIN TXStockFIFO d on d.ftDocument_No=a.ftstock_out_no and d.ftItem_Code = b.ftItem_Code
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    GROUP BY a.fdtrans_date, a.ftstock_out_no, b.ftitem_code

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,(b.fnindex*2) AS fnindex, a.fdtrans_date,
                    d.ftgl_inv_acc AS ftcoa_code,
                    CONCAT(a.ftstock_out_no,' - ',b.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, ifnull(sum(e.fcNet_Price*ABS(e.fnqty)),0) AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txstock_out_hdr a
                    INNER JOIN txstock_out_det b ON a.ftstock_out_no = b.ftstock_out_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftstock_out_no = c.ftdocument_no AND c.ftsource='barang_keluar' AND c.fnstatus = 1
                    INNER JOIN tlstock_hdr d ON b.ftitem_code = d.ftitem_code
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftstock_out_no and e.ftItem_Code = b.ftItem_Code
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    GROUP BY a.fdtrans_date, a.ftstock_out_no, b.ftitem_code

                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

        pr($ssql);
        mysql_query("BEGIN");
        $rs=mysql_query($ssql);
        mysql_query("COMMIT");


        */
  
        // STOCK PENYESUAIAN HEADER - BARANG JADI
        /* 
        $ssql="SELECT a.fdtrans_date, a.ftstock_adjustment_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'penyesuaian_barang' AS ftsource,
                IFNULL(SUM(ABS(c.fnQty)*c.fcNet_Price),0) AS fcamount, '1' AS fcrate
                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 c.ftItem_Code = b.ftItem_Code
                WHERE a.fnstatus = 2 AND a.fdtrans_date >= '2019-01-01'
                GROUP BY a.fdtrans_date, a.ftstock_adjustment_no
                ORDER BY a.fdtrans_date, a.ftstock_adjustment_no";

        $rs=mysql_query($ssql);
        $ssql1=array();

        pr("*** INSERTING STOCK PENYESUAIAN BARANG JADI TO JURNAL SYSTEM HDR ***");

        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");
            $ftjournal_no=getAutoNumber("PUSAT","acc001","txgeneral_journal_hdr","ftjournal_no",CDateRev($r['fdtrans_date']));

            $ssql="INSERT INTO txgeneral_journal_hdr(ftbranch_code,ftjournal_no,fdtrans_date,ftdocument_no, fcdebit, fccredit,
                        fnstatus,fntype,ftsource,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                        SELECT 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' AS ftjournal_no, '{$r['fdtrans_date']}' as fdtrans_date,
                        '{$r['ftdocument_no']}' AS ftdocument_no, {$r['fcamount']}*{$r['fcrate']} as fcdebit, {$r['fcamount']}*{$r['fcrate']}  as fccredit,
                        '1' AS fnstatus, '0' AS fntype,
                        '{$r['ftsource']}' AS ftsource, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                        'Admin' AS ftmodified_user,  now() AS fdmodified_date; ";

            $strSQL["insert txgeneral_journal_hdr"]=$ssql;
            mysql_query($ssql);
            //pr($ssql);
            mysql_query("COMMIT");
        }

        // STOCK PENYESUAIAN BARANG JADI DETAIL
        pr("*** INSERTING STOCK PENYESUAIAN BARANG JADI TO JURNAL SYSTEM DET ***");

        $ssql="INSERT INTO txgeneral_journal_det(ftbranch_code,ftjournal_no,fnindex,fdtrans_date,
                    ftcoa_code,ftdescription,fcdebit,fccredit,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                    SELECT * FROM (

                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,(b.fnindex*2)-1 AS fnindex, a.fdtrans_date,
                    CASE WHEN b.fnqty_base < 0 THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I02')
			 ELSE d.ftgl_inv_acc
                    END AS ftcoa_code,
                    CONCAT(a.ftstock_adjustment_no,' - ',b.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    ifnull(sum(e.fcNet_Price*ABS(e.fnqty)),0) AS fcdebit, '0' AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txstock_adjustment_hdr a
                    INNER JOIN txstock_adjustment_det b ON a.ftstock_adjustment_no = b.ftstock_adjustment_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftstock_adjustment_no = c.ftdocument_no AND c.ftsource='penyesuaian_barang' AND c.fnstatus = 1
                    INNER JOIN tlstock_hdr d ON b.ftitem_code = d.ftitem_code
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftstock_adjustment_no and e.ftItem_Code = b.ftItem_Code
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    GROUP BY a.fdtrans_date, a.ftstock_adjustment_no, b.ftitem_code

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,(b.fnindex*2) AS fnindex, a.fdtrans_date,
                    CASE WHEN b.fnqty_base < 0 THEN d.ftgl_inv_acc
			 ELSE (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I03')
                    END AS ftcoa_code,
                    CONCAT(a.ftstock_adjustment_no,' - ',b.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, ifnull(sum(e.fcNet_Price*ABS(e.fnqty)),0) AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txstock_adjustment_hdr a
                    INNER JOIN txstock_adjustment_det b ON a.ftstock_adjustment_no = b.ftstock_adjustment_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftstock_adjustment_no = c.ftdocument_no AND c.ftsource='penyesuaian_barang' AND c.fnstatus = 1
                    INNER JOIN tlstock_hdr d ON b.ftitem_code = d.ftitem_code
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftstock_adjustment_no and e.ftItem_Code = b.ftItem_Code
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    GROUP BY a.fdtrans_date, a.ftstock_adjustment_no, b.ftitem_code

                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

        pr($ssql);
        mysql_query("BEGIN");
        $rs=mysql_query($ssql);
        mysql_query("COMMIT");

        pr("*** SELESAI ***");
        */
        
        // sudah dijalankan di sunter
        /*
        // STOCK ASSEMBLY HEADER 
        $ssql="SELECT a.fdtrans_date, a.ftassembly_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'assembly' AS ftsource,
                IFNULL(SUM(ABS(c.fnQty)*c.fcNet_Price),0) AS fcamount, '1' AS fcrate
                FROM txassembly_hdr a
                LEFT JOIN TXStockFIFO c ON c.ftDocument_No=a.ftassembly_no AND c.ftItem_Code = a.ftItem_Code
                WHERE a.fnstatus = 1 AND a.fdtrans_date >= '2019-01-01'
                GROUP BY a.fdtrans_date, a.ftassembly_no
                ORDER BY a.fdtrans_date, a.ftassembly_no ";
                
        $rs=mysql_query($ssql);
        $ssql1=array();

        pr("*** INSERTING STOCK ASSEMBLY TO JURNAL SYSTEM HDR ***");

        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");
            $ftjournal_no=getAutoNumber("PUSAT","acc001","txgeneral_journal_hdr","ftjournal_no",CDateRev($r['fdtrans_date']));

            $ssql="INSERT INTO txgeneral_journal_hdr(ftbranch_code,ftjournal_no,fdtrans_date,ftdocument_no, fcdebit, fccredit,
                        fnstatus,fntype,ftsource,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                        SELECT 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' AS ftjournal_no, '{$r['fdtrans_date']}' as fdtrans_date,
                        '{$r['ftdocument_no']}' AS ftdocument_no, {$r['fcamount']}*{$r['fcrate']} as fcdebit, {$r['fcamount']}*{$r['fcrate']}  as fccredit,
                        '1' AS fnstatus, '0' AS fntype,
                        '{$r['ftsource']}' AS ftsource, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                        'Admin' AS ftmodified_user,  now() AS fdmodified_date; ";

            $strSQL["insert txgeneral_journal_hdr"]=$ssql;
            mysql_query($ssql);
            // pr($ssql);
            mysql_query("COMMIT");
        }

        // STOCK ASSEMBLY DETAIL
        pr("*** INSERTING STOCK ASSEMBLY TO JURNAL SYSTEM DET ***");

        $ssql="INSERT INTO txgeneral_journal_det(ftbranch_code,ftjournal_no,fnindex,fdtrans_date,
                    ftcoa_code,ftdescription,fcdebit,fccredit,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                    SELECT * FROM (

                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,1 AS fnindex, a.fdtrans_date,
                    d.ftgl_inv_acc AS ftcoa_code,
                    CONCAT(a.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    ifnull(sum(e.fcNet_Price*ABS(e.fnqty)),0) AS fcdebit, '0' AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txassembly_hdr a
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftassembly_no = c.ftdocument_no AND c.ftsource='assembly' AND c.fnstatus = 1
                    INNER JOIN tlstock_hdr d ON a.ftitem_code = d.ftitem_code
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftassembly_no and e.ftItem_Code = a.ftItem_Code
                    WHERE a.fnstatus = 1
                    GROUP BY a.fdtrans_date, a.ftassembly_no, a.ftitem_code

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,b.fnindex+1 AS fnindex, a.fdtrans_date,
                    d.ftgl_inv_acc AS ftcoa_code,
                    CONCAT(b.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, ifnull(sum(e.fcNet_Price*ABS(e.fnqty)),0) AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txassembly_hdr a
                    INNER JOIN txassembly_det b ON a.ftassembly_no = b.ftassembly_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftassembly_no = c.ftdocument_no AND c.ftsource='assembly' AND c.fnstatus = 1
                    INNER JOIN tlstock_hdr d ON b.ftitem_code = d.ftitem_code
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftassembly_no and e.ftItem_Code = b.ftItem_Code
                    WHERE a.fnstatus = 1
                    GROUP BY a.fdtrans_date, a.ftassembly_no, b.ftitem_code

                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

        pr($ssql);
        mysql_query("BEGIN");
        $rs=mysql_query($ssql);
        mysql_query("COMMIT");

        pr("*** SELESAI ASSEMBLY ***");
        
        // STOCK SELISIH ASSEMBLY
        pr("*** INSERTING SELISIH STOCK ASSEMBLY TO JURNAL SYSTEM DET ***");

        $ssql="INSERT INTO txgeneral_journal_det(ftbranch_code,ftjournal_no,fnindex,fdtrans_date,
                    ftcoa_code,ftdescription,fcdebit,fccredit,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                    SELECT * FROM (

                    SELECT 'PUSAT' AS ftbranch_code, a.ftjournal_no,MAX(b.fnindex) + 1  AS fnindex, a.fdtrans_date,
                    CASE WHEN SUM(b.fcdebit) - SUM(b.fccredit) > 0 
                            THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I15') 
                            ELSE (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I21') END AS ftcoa_code,
                    'SELISIH ASSEMBLY' AS ftdescription,
                    CASE WHEN SUM(b.fcdebit) - SUM(b.fccredit) > 0 
                            THEN 0 ELSE (SUM(b.fcdebit) - SUM(b.fccredit)) * -1 END AS fcdebit, 
                    CASE WHEN SUM(b.fcdebit) - SUM(b.fccredit) > 0 
                            THEN (SUM(b.fcdebit) - SUM(b.fccredit)) ELSE 0 END AS fccredit, 
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txgeneral_journal_hdr a INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no
                    WHERE a.fnstatus = 1 AND a.fdtrans_date >= '2019-01-01' AND ftsource = 'assembly'
                    GROUP BY a.fdtrans_date, a.ftjournal_no
                    HAVING SUM(b.fccredit) != SUM(b.fcdebit)
                    ORDER BY a.fdtrans_date, a.ftjournal_no 

                    ) X
                    ORDER BY fdtrans_date, ftjournal_no, fnindex";
        
            $strSQL["insert txgeneral_journal_det"]=$ssql;
            mysql_query($ssql);
            pr($ssql);
            mysql_query("COMMIT");
        
        pr("*** SELESAI SELISIH ASSEMBLY ***");

        */
        // sudah update di sunter
        // STOCK DISASSEMBLY HEADER 
        $ssql="SELECT a.fdtrans_date, a.ftdisassembly_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype, 'disassembly' AS ftsource,
                IFNULL(SUM(ABS(c.fnQty)*c.fcNet_Price),0) AS fcamount, '1' AS fcrate
                FROM txdisassembly_hdr a
                LEFT JOIN TXStockFIFO c ON c.ftDocument_No=a.ftdisassembly_no AND c.ftItem_Code = a.ftItem_Code
                WHERE a.fnstatus = 1 AND a.fdtrans_date >= '2019-01-01'
                GROUP BY a.fdtrans_date, a.ftdisassembly_no
                ORDER BY a.fdtrans_date, a.ftdisassembly_no ";
                
        $rs=mysql_query($ssql);
        $ssql1=array();

        pr("*** INSERTING STOCK DISASSEMBLY TO JURNAL SYSTEM HDR ***");

        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");
            $ftjournal_no=getAutoNumber("PUSAT","acc001","txgeneral_journal_hdr","ftjournal_no",CDateRev($r['fdtrans_date']));

            $ssql="INSERT INTO txgeneral_journal_hdr(ftbranch_code,ftjournal_no,fdtrans_date,ftdocument_no, fcdebit, fccredit,
                        fnstatus,fntype,ftsource,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                        SELECT 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' AS ftjournal_no, '{$r['fdtrans_date']}' as fdtrans_date,
                        '{$r['ftdocument_no']}' AS ftdocument_no, {$r['fcamount']}*{$r['fcrate']} as fcdebit, {$r['fcamount']}*{$r['fcrate']}  as fccredit,
                        '1' AS fnstatus, '0' AS fntype,
                        '{$r['ftsource']}' AS ftsource, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                        'Admin' AS ftmodified_user,  now() AS fdmodified_date; ";

            $strSQL["insert txgeneral_journal_hdr"]=$ssql;
            mysql_query($ssql);
            // pr($ssql);
            mysql_query("COMMIT");
        }

        // STOCK DISASSEMBLY DETAIL
        pr("*** INSERTING STOCK DISASSEMBLY TO JURNAL SYSTEM DET ***");

        $ssql="INSERT INTO txgeneral_journal_det(ftbranch_code,ftjournal_no,fnindex,fdtrans_date,
                    ftcoa_code,ftdescription,fcdebit,fccredit,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                    SELECT * FROM (

                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,1 AS fnindex, a.fdtrans_date,
                    d.ftgl_inv_acc AS ftcoa_code,
                    CONCAT(a.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, ifnull(sum(e.fcNet_Price*ABS(e.fnqty)),0) AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txdisassembly_hdr a
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftdisassembly_no = c.ftdocument_no AND c.ftsource='disassembly' AND c.fnstatus = 1
                    INNER JOIN tlstock_hdr d ON a.ftitem_code = d.ftitem_code
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftdisassembly_no and e.ftItem_Code = a.ftItem_Code
                    WHERE a.fnstatus = 1
                    GROUP BY a.fdtrans_date, a.ftdisassembly_no, a.ftitem_code

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,b.fnindex+1 AS fnindex, a.fdtrans_date,
                    d.ftgl_inv_acc AS ftcoa_code,
                    CONCAT(b.ftitem_code,' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    ifnull(sum(e.fcNet_Price*ABS(e.fnqty)),0) AS fcdebit, '0' AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txdisassembly_hdr a
                    INNER JOIN txdisassembly_det b ON a.ftdisassembly_no = b.ftdisassembly_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftdisassembly_no = c.ftdocument_no AND c.ftsource='disassembly' AND c.fnstatus = 1
                    INNER JOIN tlstock_hdr d ON b.ftitem_code = d.ftitem_code
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftdisassembly_no and e.ftItem_Code = b.ftItem_Code
                    WHERE a.fnstatus = 1
                    GROUP BY a.fdtrans_date, a.ftdisassembly_no, b.ftitem_code

                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

        pr($ssql);
        mysql_query("BEGIN");
        $rs=mysql_query($ssql);
        mysql_query("COMMIT");

        pr("*** SELESAI DISASSEMBLY ***");
        
        // STOCK SELISIH DISASSEMBLY
        pr("*** INSERTING SELISIH STOCK DISASSEMBLY TO JURNAL SYSTEM DET ***");

        $ssql="INSERT INTO txgeneral_journal_det(ftbranch_code,ftjournal_no,fnindex,fdtrans_date,
                    ftcoa_code,ftdescription,fcdebit,fccredit,ftcreated_user,fdcreated_date,ftmodified_user,fdmodified_date)
                    SELECT * FROM (

                    SELECT 'PUSAT' AS ftbranch_code, a.ftjournal_no,MAX(b.fnindex) + 1  AS fnindex, a.fdtrans_date,
                    CASE WHEN SUM(b.fccredit) - SUM(b.fcdebit) > 0 
                            THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I21') 
                            ELSE (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I15') END AS ftcoa_code,
                    'SELISIH DISASSEMBLY' AS ftdescription,
                    CASE WHEN SUM(b.fcdebit) - SUM(b.fccredit) > 0 
                            THEN 0 ELSE (SUM(b.fcdebit) - SUM(b.fccredit)) * -1 END AS fcdebit, 
                    CASE WHEN SUM(b.fcdebit) - SUM(b.fccredit) > 0 
                            THEN (SUM(b.fcdebit) - SUM(b.fccredit)) ELSE 0 END AS fccredit, 
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txgeneral_journal_hdr a INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no
                    WHERE a.fnstatus = 1 AND a.fdtrans_date >= '2019-01-01' AND ftsource = 'disassembly'
                    GROUP BY a.fdtrans_date, a.ftjournal_no
                    HAVING SUM(b.fccredit) != SUM(b.fcdebit)
                    ORDER BY a.fdtrans_date, a.ftjournal_no 

                    ) X
                    ORDER BY fdtrans_date, ftjournal_no, fnindex";
        
            $strSQL["insert txgeneral_journal_det"]=$ssql;
            mysql_query($ssql);
            pr($ssql);
            mysql_query("COMMIT");
        
        pr("*** SELESAI SELISIH DISASSEMBLY ***");

?>