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

$counter = 0;
$varyear = '2013';

/*
// PENERIMAAN PIUTANG HEADER
$ssql="SELECT a.ftjournal_no , a.ftdocument_no, SUM(b.fcdebit), SUM(b.fccredit)
                FROM txgeneral_journal_hdr a
                INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no 
                WHERE a.fnstatus = 1 AND a.ftsource='penerimaan_piutang' AND YEAR(a.fdtrans_date) = '{$varyear}'
                     -- AND MONTH(a.fdtrans_date) = '4'
                GROUP BY a.ftjournal_no, a.ftdocument_no
                HAVING SUM(b.fcdebit) <> SUM(b.fccredit)
                ORDER BY a.fdtrans_date
                ";

$rs0=mysql_query($ssql);
pr($ssql);

pr("*** INSERTING PENERIMAAN PIUTANG TO JURNAL SYSTEM HDR ***");

while($r0=mysql_fetch_assoc($rs0)) {
        // batalkan jurnal yang salah
        $ssql="UPDATE txgeneral_journal_hdr set fnstatus = 9
                WHERE ftjournal_no = '{$r0['ftjournal_no']}' ";
        $rs_batal=mysql_query($ssql);

        // insert ulang jurnal yang benar
        $ssql="SELECT fdtrans_date, ftar_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'penerimaan_piutang' AS ftsource, fcamount, fcrate
                FROM txar_hdr a
                WHERE ftar_no = '{$r0['ftdocument_no']}' ";


        $rs=mysql_query($ssql);
        // pr($ssql);

        
        while($r=mysql_fetch_assoc($rs)) {
            $counter = $counter + 1;
            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,
                        'penerimaan_piutang' 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);
            mysql_query("COMMIT");
            // pr($ssql);

             //  PENERIMAAN PIUTANG DETAIL
            // pr("*** INSERTING PENERIMAAN PIUTANG (PEMBAYARAN) 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, '{$ftjournal_no}' as ftjournal_no ,b.fnindex AS  fnindex, a.fdtrans_date,
                        CASE WHEN b.fnpayment_method IN ('0','1') THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F07')
                             WHEN b.fnpayment_method IN ('9') THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F09')
                             ELSE b.ftacc_no END AS ftcoa_code,
                        CONCAT(a.ftar_no,' - ',a.ftremarks) AS ftdescription,
                        b.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 txar_hdr a INNER JOIN txar_det1 b ON a.ftar_no = b.ftar_no
                        WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftar_no = '{$r['ftdocument_no']}'
                        ) X
                        ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

            //  PENERIMAAN PIUTANG DETAIL 1 - PEMBAYARAN OTHER COST
            // pr("*** INSERTING PENERIMAAN PIUTANG (OTHER COST PEMBAYARAN) TO JURNAL SYSTEM DET ***");

            $ssql="SELECT * FROM (
                        SELECT a.fdtrans_date, b.fnindex,b.ftother_coa_code as ftcoa_code,
                        CONCAT(a.ftar_no,' - ',a.ftremarks) AS ftdescription,
                        IF(b.fcother_cost>0,b.fcother_cost * a.fcrate,'0') AS fcdebit, IF(b.fcother_cost<0,ABS(b.fcother_cost * a.fcrate),'0')  AS fccredit
                        FROM txar_hdr a INNER JOIN txar_det1 b ON a.ftar_no = b.ftar_no
                        WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftar_no = '{$r['ftdocument_no']}'
                        ) X
                        WHERE fcdebit <> 0 or fccredit <> 0
                        ORDER BY fnindex";

            $rs1=mysql_query($ssql);
            // pr($ssql);

            $fnindex = RetRstField("SELECT count(*) + 1 FROM txgeneral_journal_hdr a INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no WHERE a.ftdocument_no = '{$r['ftdocument_no']}'");

            while($r1=mysql_fetch_assoc($rs1)) {
                mysql_query("BEGIN");

                $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 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' as ftjournal_no, '{$fnindex}' as fnindex, '{$r1['fdtrans_date']}' AS fdtrans_date,
                            '{$r1['ftcoa_code']}' AS ftcoa_code, '{$r1['ftdescription']}' AS ftdescription,
                            '{$r1['fcdebit']}' AS fcdebit, '{$r1['fccredit']}' AS fccredit,
                            'Admin' AS ftcreated_user, now() AS fdcreated_date,
                            'Admin' AS ftmodified_user,  now() AS fdmodified_date";

                $strSQL["insert txgeneral_journal_det"]=$ssql;
                mysql_query($ssql);
                mysql_query("COMMIT");
                $fnindex = $fnindex + 1;
                // pr($ssql);
            }


            //  PENERIMAAN PIUTANG DETAIL 2
            // pr("*** INSERTING PENERIMAAN PIUTANG (INVOICE) TO JURNAL SYSTEM DET ***");

            $ssql="SELECT * FROM (
                        SELECT a.fdtrans_date, b.fnindex,
                        (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F04') AS ftcoa_code,
                        CONCAT(a.ftar_no,' - ',b.ftsales_invoice_no) AS ftdescription,
                        b.fcpayment_amount_base AS fccredit
                        FROM txar_hdr a INNER JOIN txar_det b ON a.ftar_no = b.ftar_no
                        WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftar_no = '{$r['ftdocument_no']}'
                        ) X
                        ORDER BY fnindex";

            $rs2=mysql_query($ssql);
            // pr($ssql);

            $fnindex = RetRstField("SELECT count(*) + 1 FROM txgeneral_journal_hdr a INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no WHERE a.ftdocument_no = '{$r['ftdocument_no']}'");

            while($r2=mysql_fetch_assoc($rs2)) {
                mysql_query("BEGIN");

                $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 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' as ftjournal_no, '{$fnindex}' as fnindex, '{$r2['fdtrans_date']}' AS fdtrans_date,
                            '{$r2['ftcoa_code']}' AS ftcoa_code, '{$r2['ftdescription']}' AS ftdescription,
                            '0' AS fcdebit, '{$r2['fccredit']}' AS fccredit,
                            'Admin' AS ftcreated_user, now() AS fdcreated_date,
                            'Admin' AS ftmodified_user,  now() AS fdmodified_date";

                $strSQL["insert txgeneral_journal_det"]=$ssql;
                mysql_query($ssql);
                mysql_query("COMMIT");
                $fnindex = $fnindex + 1;
                // pr($ssql);
            }

            //  PENERIMAAN PIUTANG DETAIL 3 - BIAYA
            // pr("*** INSERTING PENERIMAAN PIUTANG (BIAYA) TO JURNAL SYSTEM DET ***");

            $ssql="SELECT * FROM (
                        SELECT a.fdtrans_date, b.fnindex, b.ftcoa_code,
                        CONCAT(a.ftar_no,' - ',b.ftdescription) AS ftdescription,
                        IF(b.fcamount>0,b.fcamount * a.fcrate,'0') AS fcdebit, IF(b.fcamount<0,ABS(b.fcamount * a.fcrate),'0')  AS fccredit
                        FROM txar_hdr a INNER JOIN txar_cost b ON a.ftar_no = b.ftar_no AND b.fcamount <> 0
                        WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftar_no = '{$r['ftdocument_no']}'
                        ) X
                        WHERE fcdebit <> 0 OR fccredit <> 0
                        ORDER BY fnindex";

            $rs3=mysql_query($ssql);
            // pr($ssql);

            $fnindex = RetRstField("SELECT count(*) + 1 FROM txgeneral_journal_hdr a INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no WHERE a.ftdocument_no = '{$r['ftdocument_no']}'");

            while($r3=mysql_fetch_assoc($rs3)) {
                mysql_query("BEGIN");

                $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 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' as ftjournal_no, '{$fnindex}' as fnindex, '{$r3['fdtrans_date']}' AS fdtrans_date,
                            '{$r3['ftcoa_code']}' AS ftcoa_code, '{$r3['ftdescription']}' AS ftdescription,
                            '{$r3['fcdebit']}' AS fcdebit, '{$r3['fccredit']}' AS fccredit,
                            'Admin' AS ftcreated_user, now() AS fdcreated_date,
                            'Admin' AS ftmodified_user,  now() AS fdmodified_date";

                $strSQL["insert txgeneral_journal_det"]=$ssql;
                mysql_query($ssql);
                mysql_query("COMMIT");
                $fnindex = $fnindex + 1;
                // pr($ssql);
            }

            //  PENERIMAAN PIUTANG DETAIL 4 - SELISIH KURS
            // pr("*** INSERTING PENERIMAAN PIUTANG (SELISIH KURS) TO JURNAL SYSTEM DET ***");

            $ssql="SELECT * FROM (
                    SELECT a.fdtrans_date,
		    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F21') AS ftcoa_code,
                    CONCAT(a.ftar_no,' - ',a.ftremarks) AS ftdescription,
                    IF(a.fcrate_adjustment>0,a.fcrate_adjustment,'0') AS fcdebit, IF(a.fcrate_adjustment<0,ABS(a.fcrate_adjustment),'0')  AS fccredit
                    FROM txar_hdr a
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcrate_adjustment <> 0 and a.ftar_no = '{$r['ftdocument_no']}'
                    ) X
                    WHERE fcdebit <> 0 OR fccredit <> 0 ";

            $rs4=mysql_query($ssql);
            // pr($ssql);

            $fnindex = RetRstField("SELECT count(*) + 1 FROM txgeneral_journal_hdr a INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no WHERE a.ftdocument_no = '{$r['ftdocument_no']}'");

            while($r4=mysql_fetch_assoc($rs4)) {
                mysql_query("BEGIN");

                $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 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' as ftjournal_no, '{$fnindex}' as fnindex, '{$r4['fdtrans_date']}' AS fdtrans_date,
                            '{$r4['ftcoa_code']}' AS ftcoa_code, '{$r4['ftdescription']}' AS ftdescription,
                            '{$r4['fcdebit']}' AS fcdebit, '{$r4['fccredit']}' AS fccredit,
                            'Admin' AS ftcreated_user, now() AS fdcreated_date,
                            'Admin' AS ftmodified_user,  now() AS fdmodified_date";

                $strSQL["insert txgeneral_journal_det"]=$ssql;
                mysql_query($ssql);
                mysql_query("COMMIT");
                $fnindex = $fnindex + 1;
                // pr($ssql);
            }

        }
}

pr("*** SELESAI PENERIMAAN PIUTANG - TOTAL : {$counter} records ***");
*/
/*
// PEMBAYARAN HUTANG HEADER
$ssql="SELECT a.ftjournal_no , a.ftdocument_no, SUM(b.fcdebit), SUM(b.fccredit)
                FROM txgeneral_journal_hdr a
                INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no
                WHERE a.fnstatus = 1 AND a.ftsource LIKE 'pembayaran_hutang%' AND YEAR(a.fdtrans_date) = '{$varyear}'
                    -- AND MONTH(a.fdtrans_date) = '12'
                GROUP BY a.ftjournal_no, a.ftdocument_no
                HAVING SUM(b.fcdebit) <> SUM(b.fccredit)
                ORDER BY a.fdtrans_date
                ";

$rs0=mysql_query($ssql);
pr($ssql);


pr("*** INSERTING PEMBAYARAN HUTANG TO JURNAL SYSTEM HDR ***");

$counter = 0;

while($r0=mysql_fetch_assoc($rs0)) {
        // batalkan jurnal yang salah
        $ssql="UPDATE txgeneral_journal_hdr set fnstatus = 9
                WHERE ftjournal_no = '{$r0['ftjournal_no']}' ";
        $rs_batal=mysql_query($ssql);

        // insert ulang jurnal yang benar
        $ssql="SELECT fdtrans_date, ftcvs_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                IF(a.fntype=0,'pembayaran_hutang_local','pembayaran_hutang_impor') AS ftsource, fcamount, fckurs as fcrate
                FROM txrecap_bank_voucher_hdr a
                WHERE ftcvs_no = '{$r0['ftdocument_no']}' ";

        $rs=mysql_query($ssql);
        // pr($ssql);

        while($r=mysql_fetch_assoc($rs)) {
            $counter = $counter + 1;
            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']} as fcdebit, {$r['fcamount']} 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);
            mysql_query("COMMIT");
            // pr($ssql);

             //  PEMBAYARAN HUTANG DETAIL
            // pr("*** INSERTING PEMBAYARAN HUTANG (PEMBAYARAN) 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, '{$ftjournal_no}' as ftjournal_no ,b.fnindex AS  fnindex, a.fdtrans_date,
                        CASE WHEN b.fnpayment_method IN ('0','1') THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F08')
                             WHEN b.fnpayment_method IN ('9') THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F10')
                             ELSE b.ftacc_no END AS ftcoa_code,
                        CONCAT(a.ftcvs_no,' - ',a.ftremarks) AS ftdescription,
                        '0' AS fcdebit, b.fcamount AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                        'Admin' AS ftmodified_user,  now() AS fdmodified_date
                        FROM txrecap_bank_voucher_hdr a INNER JOIN txrecap_bank_voucher_det1 b ON a.ftcvs_no = b.ftcvs_no
                        WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftcvs_no = '{$r['ftdocument_no']}'
                        ) X
                        ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

            //  PEMBAYARAN HUTANG DETAIL 1 - PEMBAYARAN OTHER COST
            // pr("*** INSERTING PEMBAYARAN HUTANG (OTHER COST PEMBAYARAN) TO JURNAL SYSTEM DET ***");

            $ssql="SELECT * FROM (
                        SELECT a.fdtrans_date, b.fnindex,b.ftother_coa_code as ftcoa_code,
                        CONCAT(a.ftcvs_no,' - ',a.ftremarks) AS ftdescription,
                        IF(b.fcother_cost<0,ABS(b.fcother_cost),'0') AS fcdebit, IF(b.fcother_cost>0,b.fcother_cost,'0')  AS fccredit
                        FROM txrecap_bank_voucher_hdr a INNER JOIN txrecap_bank_voucher_det1 b ON a.ftcvs_no = b.ftcvs_no
                        WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftcvs_no = '{$r['ftdocument_no']}'
                        ) X
                        WHERE fcdebit <> 0 or fccredit <> 0
                        ORDER BY fnindex";

            $rs1=mysql_query($ssql);
            // pr($ssql);

            $fnindex = RetRstField("SELECT count(*) + 1 FROM txgeneral_journal_hdr a INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no WHERE a.ftdocument_no = '{$r['ftdocument_no']}'");

            while($r1=mysql_fetch_assoc($rs1)) {
                mysql_query("BEGIN");

                $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 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' as ftjournal_no, '{$fnindex}' as fnindex, '{$r1['fdtrans_date']}' AS fdtrans_date,
                            '{$r1['ftcoa_code']}' AS ftcoa_code, '{$r1['ftdescription']}' AS ftdescription,
                            '{$r1['fcdebit']}' AS fcdebit, '{$r1['fccredit']}' AS fccredit,
                            'Admin' AS ftcreated_user, now() AS fdcreated_date,
                            'Admin' AS ftmodified_user,  now() AS fdmodified_date";

                $strSQL["insert txgeneral_journal_det"]=$ssql;
                mysql_query($ssql);
                mysql_query("COMMIT");
                $fnindex = $fnindex + 1;
                // pr($ssql);
            }


            //  PEMBAYARAN HUTANG DETAIL 2
            // pr("*** INSERTING PEMBAYARAN HUTANG (INVOICE) TO JURNAL SYSTEM DET ***");

            $ssql="SELECT * FROM (
                        SELECT a.fdtrans_date, b.fnindex,
                        (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F01') AS ftcoa_code,
                        CONCAT(a.ftcvs_no,' - ',b.ftpi_no) AS ftdescription,
                        b.fcpayment_amount AS fcdebit
                        FROM txrecap_bank_voucher_hdr a INNER JOIN txrecap_bank_voucher_det b ON a.ftcvs_no = b.ftcvs_no
                        WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftcvs_no = '{$r['ftdocument_no']}'
                        ) X
                        ORDER BY fnindex";

            $rs2=mysql_query($ssql);
            // pr($ssql);

            while($r2=mysql_fetch_assoc($rs2)) {
                mysql_query("BEGIN");

                $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 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' as ftjournal_no, '{$fnindex}' as fnindex, '{$r2['fdtrans_date']}' AS fdtrans_date,
                            '{$r2['ftcoa_code']}' AS ftcoa_code, '{$r2['ftdescription']}' AS ftdescription,
                            '{$r2['fcdebit']}' AS fcdebit, '0' AS fccredit,
                            'Admin' AS ftcreated_user, now() AS fdcreated_date,
                            'Admin' AS ftmodified_user,  now() AS fdmodified_date";

                $strSQL["insert txgeneral_journal_det"]=$ssql;
                mysql_query($ssql);
                mysql_query("COMMIT");
                $fnindex = $fnindex + 1;
                // pr($ssql);
            }

            //  PEMBAYARAN HUTANG DETAIL 3 - BIAYA
            // pr("*** INSERTING PEMBAYARAN HUTANG (BIAYA) TO JURNAL SYSTEM DET ***");

            $ssql="SELECT * FROM (
                        SELECT a.fdtrans_date, b.fnindex, b.ftcoa_code,
                        CONCAT(a.ftcvs_no,' - ',b.ftdescription) AS ftdescription,
                        IF(b.fcamount>0,b.fcamount * a.fckurs,'0') AS fccredit , IF(b.fcamount<0,ABS(b.fcamount * a.fckurs),'0')  AS fcdebit
                        FROM txrecap_bank_voucher_hdr a INNER JOIN txrecap_bank_voucher_cost b ON a.ftcvs_no = b.ftcvs_no AND b.fcamount <> 0
                        WHERE (a.fnstatus = 1 OR a.fnstatus = 2) and a.ftcvs_no = '{$r['ftdocument_no']}'
                        ) X
                        WHERE fcdebit <> 0 OR fccredit <> 0
                        ORDER BY fnindex";

            $rs3=mysql_query($ssql);
            // pr($ssql);

            while($r3=mysql_fetch_assoc($rs3)) {
                mysql_query("BEGIN");

                $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 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' as ftjournal_no, '{$fnindex}' as fnindex, '{$r3['fdtrans_date']}' AS fdtrans_date,
                            '{$r3['ftcoa_code']}' AS ftcoa_code, '{$r3['ftdescription']}' AS ftdescription,
                            '{$r3['fcdebit']}' AS fcdebit, '{$r3['fccredit']}' AS fccredit,
                            'Admin' AS ftcreated_user, now() AS fdcreated_date,
                            'Admin' AS ftmodified_user,  now() AS fdmodified_date";

                $strSQL["insert txgeneral_journal_det"]=$ssql;
                mysql_query($ssql);
                mysql_query("COMMIT");
                $fnindex = $fnindex + 1;
                // pr($ssql);
            }

            //  PEMBAYARAN HUTANG DETAIL 4 - SELISIH KURS
            // pr("*** INSERTING PEMBAYARAN HUTANG (SELISIH KURS) TO JURNAL SYSTEM DET ***");

            $ssql="SELECT * FROM (
                    SELECT a.fdtrans_date,
		    (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F21') AS ftcoa_code,
                    CONCAT(a.ftcvs_no,' - ',a.ftremarks) AS ftdescription,
                    IF(a.fcrate_adjustment>0,a.fcrate_adjustment,'0') AS fccredit, IF(a.fcrate_adjustment<0,ABS(a.fcrate_adjustment),'0')  AS fcdebit
                    FROM txrecap_bank_voucher_hdr a
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND a.fcrate_adjustment <> 0 and a.ftcvs_no = '{$r['ftdocument_no']}'
                    ) X
                    WHERE fcdebit <> 0 OR fccredit <> 0 ";

            $rs4=mysql_query($ssql);
            // pr($ssql);

            while($r4=mysql_fetch_assoc($rs4)) {
                mysql_query("BEGIN");

                $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 'PUSAT' AS ftbranch_code, '{$ftjournal_no}' as ftjournal_no, '{$fnindex}' as fnindex, '{$r4['fdtrans_date']}' AS fdtrans_date,
                            '{$r4['ftcoa_code']}' AS ftcoa_code, '{$r4['ftdescription']}' AS ftdescription,
                            '{$r4['fcdebit']}' AS fcdebit, '{$r4['fccredit']}' AS fccredit,
                            'Admin' AS ftcreated_user, now() AS fdcreated_date,
                            'Admin' AS ftmodified_user,  now() AS fdmodified_date";

                $strSQL["insert txgeneral_journal_det"]=$ssql;
                mysql_query($ssql);
                mysql_query("COMMIT");
                $fnindex = $fnindex + 1;
                // pr($ssql);
            }

        }


}

pr("*** SELESAI PEMBAYARAN HUTANG - TOTAL : {$counter} records ***");
*/
/*
// GIRO MUNDUR PIUTANG HEADER
$ssql="SELECT a.ftjournal_no , a.ftdocument_no, SUM(b.fcdebit), SUM(b.fccredit)
                FROM txgeneral_journal_hdr a
                INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no
                WHERE a.fnstatus = 1 AND a.ftsource = 'giro_mundur_piutang' AND YEAR(a.fdtrans_date) = '{$varyear}'
                     AND MONTH(a.fdtrans_date) = '8'
                GROUP BY a.ftjournal_no, a.ftdocument_no
                HAVING SUM(b.fcdebit) <> SUM(b.fccredit)
                ORDER BY a.fdtrans_date
                ";
*/

// GIRO MUNDUR PIUTANG HEADER
$ssql="SELECT a.ftjournal_no , a.ftdocument_no, count(*)
                FROM txgeneral_journal_hdr a
                INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no
                WHERE a.fnstatus = 1 AND a.ftsource = 'giro_mundur_piutang'
                GROUP BY a.ftjournal_no, a.ftdocument_no
                HAVING  count(*) = 1
                ORDER BY a.fdtrans_date
                ";
                


$rs0=mysql_query($ssql);
pr($ssql);


pr("*** INSERTING GIRO MUNDUR PIUTANG TO JURNAL SYSTEM HDR ***");

$counter = 0;

while($r0=mysql_fetch_assoc($rs0)) {
        // batalkan jurnal yang salah
        $ssql="UPDATE txgeneral_journal_hdr set fnstatus = 9
                WHERE ftjournal_no = '{$r0['ftjournal_no']}' ";
        $rs_batal=mysql_query($ssql);

        // insert ulang jurnal yang benar
        $ssql="SELECT fdgiro_cliring_date as fdtrans_date, ftdocument_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'giro_mundur_piutang' AS ftsource, fcamount, fcrate, ftgiro_no
                FROM txgiro_hist a
                WHERE ftdocument_no = '{$r0['ftdocument_no']}' ";

        $rs=mysql_query($ssql);
        pr($ssql);

        while($r=mysql_fetch_assoc($rs)) {
            $counter = $counter + 1;
            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']} as fcdebit, {$r['fcamount']} 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);
            mysql_query("COMMIT");
            pr($ssql);

            //  GIRO MUNDUR PIUTANG DETAIL
            // pr("*** INSERTING GIRO MUNDUR PIUTANG-DEBIT 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, '{$ftjournal_no}' as ftjournal_no ,1 AS  fnindex, a.fdtrans_date,
                        CASE WHEN a.fnstatus_giro IN ('1') THEN a.ftacc_no
                             WHEN a.fnstatus_giro IN ('2') THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F04')
                        END AS ftcoa_code,
                        CONCAT(a.ftdocument_no,' - ',a.ftgiro_no) 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 txgiro_hist a
                        WHERE ((a.fnstatus_giro = 1) OR (a.fnstatus_giro = 2)) and a.ftdocument_no = '{$r['ftdocument_no']}' and a.ftgiro_no = '{$r['ftgiro_no']}'
                        ) X
                        ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

            //  GIRO MUNDUR PIUTANG - CREDIT
            // pr("*** INSERTING GIRO MUNDUR PIUTANG - CREDIT 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, '{$ftjournal_no}' as ftjournal_no ,'2' AS  fnindex, a.fdtrans_date,
                        b.ftcoa_code,
                        CONCAT(a.ftdocument_no,' - ',a.ftgiro_no) AS ftdescription,
                        '0' AS fcdebit, a.fcamount*a.fcrate AS fccredit, 'Admin' AS ftcreated_user, Now() AS fdcreated_date,
                        'Admin' AS ftmodified_user,  Now() AS fdmodified_date
                        FROM txgiro_hist a
                        INNER JOIN tlcoa_setting b ON ftcoa_no = 'F07'
                        WHERE ((a.fnstatus_giro = 1) OR (a.fnstatus_giro = 2)) AND a.ftdocument_no = '{$r['ftdocument_no']}'  and a.ftgiro_no = '{$r['ftgiro_no']}'
                        ) X
                        ORDER BY fdtrans_date, ftjournal_no, fnindex";

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


}

pr("*** SELESAI GIRO MUNDUR PIUTANG - TOTAL : {$counter} records ***");



/*
// BANK MASUK HEADER*

$ssql="select b.ftjournal_no, b.ftdocument_no
                from txbankrcp_hdr a
                inner join txgeneral_journal_hdr b on a.ftbankrcp_no = b.ftdocument_no and b.ftsource LIKE 'bank%'
                WHERE (a.fnstatus <> 3 OR a.fnstatus <> 9) and b.fnstatus = 1 AND YEAR(a.fdtrans_date) = '2014'
                and (a.fcamount * fcrate) <> b.fcdebit
                ORDER BY a.fdtrans_date
                ";

$rs0=mysql_query($ssql);
pr($ssql);

pr("*** INSERTING BANK MASUK  TO JURNAL SYSTEM HDR ***");

while($r0=mysql_fetch_assoc($rs0)) {
        // batalkan jurnal yang salah
        $ssql="UPDATE txgeneral_journal_hdr set fnstatus = 9
                WHERE ftjournal_no = '{$r0['ftjournal_no']}' ";
        $rs_batal=mysql_query($ssql);

        // insert ulang jurnal yang benar
        $ssql="SELECT fdtrans_date, ftbankrcp_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'bank_masuk' AS ftsource, fcamount, fcrate
                FROM txbankrcp_hdr a
                WHERE ftbankrcp_no = '{$r0['ftdocument_no']}' ";

        $rs=mysql_query($ssql);
        // pr($ssql);

        while($r=mysql_fetch_assoc($rs)) {
            $counter = $counter + 1;
            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);
            mysql_query("COMMIT");
            // pr($ssql);

            //  BANK MASUK DETAIL
            $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,
                    a.ftacc_no1 AS ftcoa_code, CONCAT(ftbankrcp_no,' - ',ftreceive_name,' - ',ftremarks) 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 txbankrcp_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftbankrcp_no = c.ftdocument_no AND c.ftsource='{$r['ftsource']}' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND c.ftjournal_no = '{$ftjournal_no}'
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,b.fnindex+1 AS  fnindex, a.fdtrans_date,
		    CASE WHEN b.fnpayment_method IN ('0','1') THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F07')
			 ELSE b.ftcoa_code END AS ftcoa_code,
                    CONCAT(a.ftbankrcp_no,' - ',a.ftreceive_name,' - ',b.ftdescription) AS ftdescription,
                    IF(b.fcamount<0,ABS(b.fcamount*a.fcrate),'0') AS fcdebit, IF(b.fcamount>0,b.fcamount*a.fcrate,'0')  AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user,  now() AS fdmodified_date
                    FROM txbankrcp_hdr a INNER JOIN txbankrcp_det b ON a.ftbankrcp_no = b.ftbankrcp_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftbankrcp_no = c.ftdocument_no AND c.ftsource='{$r['ftsource']}' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND c.ftjournal_no = '{$ftjournal_no}'
                    ) X
                    ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

pr("*** SELESAI BANK MASUK: {$counter} records ***");

*/

// BANK KELUAR HEADER*
/*
$ssql="select b.ftjournal_no, b.ftdocument_no
                from txbankpym_hdr a
                inner join txgeneral_journal_hdr b on a.ftbankpym_no = b.ftdocument_no and b.ftsource LIKE 'bank%'
                WHERE (a.fnstatus <> 3 OR a.fnstatus <> 9) and b.fnstatus = 1 AND YEAR(a.fdtrans_date) = '2013'
                and (a.fcamount * fcrate) <> b.fcdebit
                ORDER BY a.fdtrans_date
                ";

$rs0=mysql_query($ssql);
pr($ssql);

pr("*** INSERTING BANK KELUAR TO JURNAL SYSTEM HDR ***");

while($r0=mysql_fetch_assoc($rs0)) {
        // batalkan jurnal yang salah
        $ssql="UPDATE txgeneral_journal_hdr set fnstatus = 9, fdmodified_date = now()
                WHERE ftjournal_no = '{$r0['ftjournal_no']}' ";
        $rs_batal=mysql_query($ssql);

        // insert ulang jurnal yang benar
        $ssql="SELECT fdtrans_date, ftbankpym_no AS ftdocument_no, '1' AS fnstatus, '0' AS fntype,
                'bank_keluar' AS ftsource, fcamount, fcrate
                FROM txbankpym_hdr a
                WHERE ftbankpym_no = '{$r0['ftdocument_no']}' ";

        $rs=mysql_query($ssql);
        // pr($ssql);

        while($r=mysql_fetch_assoc($rs)) {
            $counter = $counter + 1;
            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);
            mysql_query("COMMIT");
            // pr($ssql);

            //  BANK KELUAR DETAIL
            $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,
                    a.ftacc_no1 AS ftcoa_code, CONCAT(ftbankpym_no,' - ',ftreceive_name,' - ',ftremarks) AS ftdescription,
                    '0' AS fcdebit, a.fcamount*a.fcrate AS fccredit, 'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user, now() AS fdmodified_date
                    FROM txbankpym_hdr a INNER JOIN txgeneral_journal_hdr c
                    ON a.ftbankpym_no = c.ftdocument_no AND c.ftsource='{$r['ftsource']}' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND c.ftjournal_no = '{$ftjournal_no}'
                    UNION ALL
                    SELECT 'PUSAT' AS ftbranch_code, c.ftjournal_no,b.fnindex+1 AS  fnindex, a.fdtrans_date,
                    CASE WHEN b.fnpayment_method IN ('0','1') THEN (SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'F08')
			 ELSE b.ftcoa_code END AS ftcoa_code,
                    CONCAT(a.ftbankpym_no,' - ',a.ftreceive_name,' - ',b.ftdescription) AS ftdescription,
                    IF(b.fcamount>0,b.fcamount*a.fcrate,'0') AS fcdebit, IF(b.fcamount<0,ABS(b.fcamount*a.fcrate),'0')  AS fccredit,
                    'Admin' AS ftcreated_user, now() AS fdcreated_date,
                    'Admin' AS ftmodified_user, now() AS fdmodified_date
                    FROM txbankpym_hdr a INNER JOIN txbankpym_det b ON a.ftbankpym_no = b.ftbankpym_no
                    INNER JOIN txgeneral_journal_hdr c
                    ON a.ftbankpym_no = c.ftdocument_no AND c.ftsource='{$r['ftsource']}' AND c.fnstatus = 1
                    WHERE (a.fnstatus = 1 OR a.fnstatus = 2) AND c.ftjournal_no = '{$ftjournal_no}'
                    ) X
                    ORDER BY fdtrans_date, ftjournal_no, fnindex";

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

pr("*** SELESAI BANK KELUAR: {$counter} records ***");
*/
?>