<?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";

        /* 
        // DELIVERY ORDER HEADER
        $ssql="SELECT fdtrans_date, ftdo_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'delivery_order' AS ftsource, '0' AS fcamount, '1' AS fcrate
                FROM txdelivery_order_hdr a
                WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                ORDER BY a.fdtrans_date, a.ftdo_no";

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

        pr("*** INSERTING DELIVERY ORDER 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; ";

            mysql_query($ssql);
            //pr($ssql);
            mysql_query("COMMIT");
        }

        // DELIVERY ORDER DETAIL
        pr("*** INSERTING DELIVERY ORDER 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,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'P01') AS ftcoa_code,
                    CONCAT(a.ftdo_no,' - ',IFNULL(a.ftcustomer_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 txdelivery_order_hdr a
                    INNER JOIN txdelivery_order_det b ON a.ftdo_no = b.ftdo_no
                    INNER JOIN txgeneral_journal_hdr c ON a.ftdo_no = c.ftdocument_no AND c.fnstatus = 1
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftdo_no and e.ftItem_Code = b.ftItem_Code AND e.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    GROUP BY a.fdtrans_date, a.ftdo_no

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'2' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I01') AS ftcoa_code,
                    CONCAT(a.ftdo_no,' - ',IFNULL(a.ftcustomer_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 txdelivery_order_hdr a
                    INNER JOIN txdelivery_order_det b ON a.ftdo_no = b.ftdo_no
                    INNER JOIN txgeneral_journal_hdr c ON a.ftdo_no = c.ftdocument_no AND c.fnstatus = 1
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftdo_no and e.ftItem_Code = b.ftItem_Code AND e.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    GROUP BY a.fdtrans_date, a.ftdo_no
                    
                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

         pr("*** UPDATE JURNAL SYSTEM HDR - DO ***");

        // UPDATE COGS DO HEADER
        $ssql="SELECT a.ftjournal_no, a.ftsource, a.ftdocument_no, SUM(b.fcdebit) AS fcamount
                FROM txgeneral_journal_hdr a
                INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no
                WHERE a.ftsource LIKE '%delivery_order%' AND a.fnstatus = 1
                GROUP BY a.ftjournal_no
                ORDER BY a.ftjournal_no";

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

        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");

            $ssql="UPDATE txgeneral_journal_hdr
                    SET fcdebit = '{$r['fcamount']}', fccredit = '{$r['fcamount']}'
                    WHERE ftjournal_no = '{$r['ftjournal_no']}'  ";

            mysql_query($ssql);
            //pr($ssql);
            mysql_query("COMMIT");
        }

        */
        /* sdh di jalankan di abrindo
        // SALES RETUR HEADER
        $ssql="SELECT fdtrans_date, ftsales_return_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'sales_return' AS ftsource,
                fcamount, '1' as fcrate
                FROM txsales_return_hdr a
                WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fdtrans_date >= '2019-05-03' and a.ftsales_return_no > 'SR190500003'
                ORDER BY a.fdtrans_date, a.ftsales_return_no";

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

        pr("*** INSERTING SALES RETURN TO JURNAL SYSTEM HDR ***");

        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");
            $ftjournal_no=getAutoNumber($_SESSION['strbranch'],"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");


        }
        */
        // SALES RETURN DETAIL
        pr("*** INSERTING SALES RETURN 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,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S05') AS ftcoa_code,
                    CONCAT(IFNULL(a.ftcustomer_code,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    a.fcsub_amount AS fcdebit, '0' AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_return_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_return_no = c.ftdocument_no AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                                AND a.ftsales_return_no >= 'SR190500006' AND a.ftsales_return_no <= 'SR190500060' 
				AND a.ftsales_return_no != 'SR190500007' AND a.ftsales_return_no != 'SR190500021' 
                                
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'2' AS fnindex, a.fdtrans_date,
                    IF(a.fcother_cost>0,(SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S07'),(SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S08')) AS ftcoa_code,
                    CONCAT(IFNULL(a.ftcustomer_code,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    IF(a.fcother_cost>0,a.fcother_cost,'0') AS fcdebit, IF(a.fcother_cost<0,ABS(a.fcother_cost),'0') AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_return_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_return_no = c.ftdocument_no AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcother_cost <> 0
                                AND a.ftsales_return_no >= 'SR190500006' AND a.ftsales_return_no <= 'SR190500060' 
				AND a.ftsales_return_no != 'SR190500007' AND a.ftsales_return_no != 'SR190500021' 
                                
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'3' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S03') AS ftcoa_code,
                    CONCAT(IFNULL(a.ftcustomer_code,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    a.fcvat AS fcdebit, '0' AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_return_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_return_no = c.ftdocument_no AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcvat > 0
                                AND a.ftsales_return_no >= 'SR190500006' AND a.ftsales_return_no <= 'SR190500060' 
				AND a.ftsales_return_no != 'SR190500007' AND a.ftsales_return_no != 'SR190500021' 
                                
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'4' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F04') AS ftcoa_code,
                    CONCAT(IFNULL(a.ftcustomer_code,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, a.fcamount AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_return_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_return_no = c.ftdocument_no AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                                AND a.ftsales_return_no >= 'SR190500006' AND a.ftsales_return_no <= 'SR190500060' 
				AND a.ftsales_return_no != 'SR190500007' AND a.ftsales_return_no != 'SR190500021' 
                                
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'5' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S02') AS ftcoa_code,
                    CONCAT(IFNULL(a.ftcustomer_code,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, a.fcdiscount AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_return_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_return_no = c.ftdocument_no AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcdiscount > 0
                                AND a.ftsales_return_no >= 'SR190500006' AND a.ftsales_return_no <= 'SR190500060' 
				AND a.ftsales_return_no != 'SR190500007' AND a.ftsales_return_no != 'SR190500021' 
                                
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'6' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I01') AS ftcoa_code,
                    CONCAT(IFNULL(a.ftcustomer_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 txsales_return_hdr a
                    INNER JOIN txsales_return_det b ON a.ftsales_return_no = b.ftsales_return_no
                    INNER JOIN txgeneral_journal_hdr c ON a.ftsales_return_no = c.ftdocument_no AND c.fnstatus = 1
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftsales_return_no and e.ftItem_Code = b.ftItem_Code AND e.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                                AND a.ftsales_return_no >= 'SR190500006' AND a.ftsales_return_no <= 'SR190500060' 
				AND a.ftsales_return_no != 'SR190500007' AND a.ftsales_return_no != 'SR190500021' 
                                
                    GROUP BY a.fdtrans_date, a.ftsales_return_no
                              
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'7' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'P01') AS ftcoa_code,
                    CONCAT(IFNULL(a.ftcustomer_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 txsales_return_hdr a
                    INNER JOIN txsales_return_det b ON a.ftsales_return_no = b.ftsales_return_no
                    INNER JOIN txgeneral_journal_hdr c ON a.ftsales_return_no = c.ftdocument_no AND c.fnstatus = 1
                    LEFT JOIN TXStockFIFO e on e.ftDocument_No=a.ftsales_return_no and e.ftItem_Code = b.ftItem_Code AND e.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                                AND a.ftsales_return_no >= 'SR190500006' AND a.ftsales_return_no <= 'SR190500060' 
				AND a.ftsales_return_no != 'SR190500007' AND a.ftsales_return_no != 'SR190500021' 
                                    
                    GROUP BY a.fdtrans_date, a.ftsales_return_no

                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

         pr("*** UPDATE TO JURNAL SYSTEM HDR ***");

        // UPDATE SALES RETURN HEADER
        $ssql="SELECT a.ftjournal_no, a.ftsource, a.ftdocument_no, SUM(b.fcdebit) AS fcamount
                FROM txgeneral_journal_hdr a
                INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no
                WHERE a.ftsource LIKE '%sales_return%' AND a.fnstatus = 1
                          AND a.ftdocument_no >= 'SR190500006' AND a.ftdocument_no <= 'SR190500060' 
                          AND a.ftdocument_no != 'SR190500007' AND a.ftdocument_no != 'SR190500021' 
                                
                GROUP BY a.ftjournal_no
                ORDER BY a.ftjournal_no";

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

        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");

            $ssql="UPDATE txgeneral_journal_hdr
                    SET fcdebit = '{$r['fcamount']}', fccredit = '{$r['fcamount']}'
                    WHERE ftjournal_no = '{$r['ftjournal_no']}'  ";

            mysql_query($ssql);
            //pr($ssql);
            mysql_query("COMMIT");
        }
        
        
        /*
        // SALES INVOICE HEADER

        $ssql="SELECT fdtrans_date, ftsales_invoice_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'sales_invoice' AS ftsource, fcamount, fcrate
                FROM txsales_invoice_hdr a
                WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                ORDER BY a.fdtrans_date, a.ftsales_invoice_no";

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

        pr("*** INSERTING SALES INVOICE 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,
                        'sales_invoice' 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");


        }
       
        // SALES INVOICE DETAIL
        pr("*** INSERTING SALES INVOICE 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,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F04') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    a.fcamount*a.fcrate AS fcdebit, '0' AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'2' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S01') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, a.fcsub_amount*a.fcrate AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'3' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S02') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    a.fcdiscount*a.fcrate AS fcdebit,'0' AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcdiscount > 0
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'4' AS fnindex, a.fdtrans_date,
                    IF(a.fcother_cost>0,(SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S07'),(SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S08')) AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    IF(a.fcother_cost<0,ABS(a.fcother_cost*a.fcrate),'0') AS fcdebit, IF(a.fcother_cost>0,a.fcother_cost*a.fcrate,'0') AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcother_cost > 0
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'5' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S03') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, a.fcvat*a.fcrate AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcvat > 0
                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

        */
        /* 
        // SALES ORDER HEADER
        $ssql="SELECT fdtrans_date, ftsales_invoice_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'sales_order' AS ftsource, fcamount, fcrate
                FROM txsales_invoice_hdr a
                WHERE -- (a.fnstatus = 1 OR a.fnstatus = 2) AND fdtrans_date >= '2019-05-04' 
                        a.ftsales_invoice_no = 'SI190500067' or a.ftsales_invoice_no = 'SI190500069'
                ORDER BY a.fdtrans_date, a.ftsales_invoice_no";

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

        pr("*** INSERTING SALES INVOICE TO JURNAL SYSTEM HDR ***");

        $counter = 0;  
        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");
            $ftjournal_no=getAutoNumber($_SESSION['strbranch'],"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,
                        'sales_order' 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");
            $counter = $counter + 1;    

        }
        
        // SALES INVOICE DETAIL
        pr("*** INSERTING SALES INVOICE 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,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F04') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    a.fcamount*a.fcrate AS fcdebit, '0' AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_order' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                         AND a.ftsales_invoice_no >= 'si190500284' AND a.ftsales_invoice_no <= 'SI190500327'

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'2' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S01') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, a.fcsub_amount*a.fcrate AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_order' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                         AND a.ftsales_invoice_no >= 'si190500284' AND a.ftsales_invoice_no <= 'SI190500327'

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'3' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S02') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    a.fcdiscount*a.fcrate AS fcdebit,'0' AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_order' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcdiscount > 0
                        AND a.ftsales_invoice_no >= 'si190500284' AND a.ftsales_invoice_no <= 'SI190500327'

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'4' AS fnindex, a.fdtrans_date,
                    IF(a.fcother_cost>0,(SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S07'),(SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S08')) AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    IF(a.fcother_cost<0,ABS(a.fcother_cost*a.fcrate),'0') AS fcdebit, IF(a.fcother_cost>0,a.fcother_cost*a.fcrate,'0') AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_order' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcother_cost > 0
                      AND a.ftsales_invoice_no >= 'si190500284' AND a.ftsales_invoice_no <= 'SI190500327'

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'5' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S03') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    '0' AS fcdebit, a.fcvat*a.fcrate AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_order' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcvat > 0
                       AND a.ftsales_invoice_no >= 'si190500284' AND a.ftsales_invoice_no <= 'SI190500327'

                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

        pr("*** SELESAI : SALES INVOICE DETAIL ({$counter} rows) ***");
        
        pr("*** INSERTING DELIVERY ORDER 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,'3' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'P01') AS ftcoa_code,
                    CONCAT(d.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_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 txdelivery_order_hdr a
                    INNER JOIN txdelivery_order_det b ON a.ftdo_no = b.ftdo_no
                    INNER JOIN txsales_invoice_hdr d ON d.ftdo_no = a.ftdo_no 
                    INNER JOIN txstock_out_hdr d2 ON d2.ftdocument_no = a.ftdo_no 
                    INNER JOIN txgeneral_journal_hdr c ON d.ftsales_invoice_no = c.ftdocument_no AND c.fnstatus = 1
                    LEFT JOIN TXStockFIFO e ON e.ftDocument_No=d2.ftstock_out_no AND e.ftItem_Code = b.ftItem_Code AND e.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fdtrans_date >= '2019-01-01'
                       AND d.ftsales_invoice_no >= 'si190500284' AND d.ftsales_invoice_no <= 'SI190500327'

                    GROUP BY a.fdtrans_date, a.ftdo_no

                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'4' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'I01') AS ftcoa_code,
                    CONCAT(d.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_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 txdelivery_order_hdr a
                    INNER JOIN txdelivery_order_det b ON a.ftdo_no = b.ftdo_no
                    INNER JOIN txsales_invoice_hdr d ON d.ftdo_no = a.ftdo_no 
                    INNER JOIN txstock_out_hdr d2 ON d2.ftdocument_no = a.ftdo_no 
                    INNER JOIN txgeneral_journal_hdr c ON d.ftsales_invoice_no = c.ftdocument_no AND c.fnstatus = 1
                    LEFT JOIN TXStockFIFO e ON e.ftDocument_No=d2.ftstock_out_no AND e.ftItem_Code = b.ftItem_Code AND e.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fdtrans_date >= '2019-01-01'
                      AND d.ftsales_invoice_no >= 'si190500284' AND d.ftsales_invoice_no <= 'SI190500327'

                    GROUP BY a.fdtrans_date, a.ftdo_no
                    
                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

        pr($ssql);
        mysql_query("BEGIN");
        $rs=mysql_query($ssql);
        mysql_query("COMMIT");
       
        
        
        // SALES INVOICE ADJUSTMENT
        /* sudah dijalankan di abrindo
        $ssql="SELECT fdtrans_date, ftsales_invoice_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'sales_invoice_adjustment' AS ftsource, fcamount, fcrate
                FROM txsales_invoice_adjustment_hdr a
                WHERE a.fnstatus = 1  AND fdtrans_date >= '2019-01-01' -- AND fdtrans_date <= '2019-04-25'
                ORDER BY a.fdtrans_date, a.ftsales_invoice_no";

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

        pr("*** INSERTING SALES INVOICE ADJUSTMENT TO JURNAL SYSTEM HDR ***");

        $counter = 0;  
        while($r=mysql_fetch_assoc($rs)) {
            mysql_query("BEGIN");
            $ftjournal_no=getAutoNumber($_SESSION['strbranch'],"acc001","txgeneral_journal_hdr","ftjournal_no",CDateRev($r['fdtrans_date']));
            
            if ($r['fcamount'] > 0 ) {
                $fcamount = $r['fcamount']; 
            } else {
                $fcamount = $r['fcamount'] * -1; 
            }
            
            $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, {$fcamount}*{$r['fcrate']} as fcdebit, {$fcamount}*{$r['fcrate']}  as fccredit,
                        '1' AS fnstatus, '0' AS fntype,
                        'sales_invoice_adjustment' 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");
            $counter = $counter + 1;    

        }
        
        // SALES INVOICE DETAIL
        pr("*** INSERTING SALES INVOICE 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,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F04') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    case when (a.fcamount > 0) then (a.fcamount*a.fcrate) else 0 end AS fcdebit, 
                    case when (a.fcamount < 0) then (a.fcamount*a.fcrate)*-1 else 0 end AS fccredit, 
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_adjustment_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice_adjustment' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'2' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S01') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    case when (a.fcsub_amount < 0) then (a.fcsub_amount*a.fcrate)*-1 else 0 end AS fcdebit, 
                    case when (a.fcsub_amount > 0) then (a.fcsub_amount*a.fcrate) else 0 end AS fccredit, 
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_adjustment_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice_adjustment' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2)
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'3' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S02') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    case when (a.fcdiscount > 0) then (a.fcdiscount*a.fcrate) else 0 end AS fcdebit, 
                    case when (a.fcdiscount < 0) then (a.fcdiscount*a.fcrate)*-1 else 0 end AS fccredit, 
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_adjustment_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice_adjustment' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcdiscount > 0
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'4' AS fnindex, a.fdtrans_date,
                    IF(a.fcother_cost>0,(SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S07'),(SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S08')) AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    IF(a.fcother_cost<0,ABS(a.fcother_cost*a.fcrate),'0') AS fcdebit, IF(a.fcother_cost>0,a.fcother_cost*a.fcrate,'0') AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_adjustment_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice_adjustment' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcother_cost > 0
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,'5' AS fnindex, a.fdtrans_date,
                    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'S03') AS ftcoa_code,
                    CONCAT(a.ftsales_invoice_no,' - ',IFNULL(a.ftcustomer_name,''),' - ',IFNULL(a.ftnotes,'')) AS ftdescription,
                    IF(a.fcvat<0,ABS(a.fcvat*a.fcrate),'0') AS fcdebit, IF(a.fcvat>0,a.fcvat*a.fcrate,'0') AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txsales_invoice_adjustment_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftsales_invoice_no = c.ftdocument_no AND c.ftsource='sales_invoice_adjustment' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcvat > 0
                ) X
                ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

        pr("*** SELESAI : SALES INVOICE ADJUSTMENT DETAIL ({$counter} rows) ***");
        */
?>