<?php
// Validasi
switch($_REQUEST[mode]) {
    case "simpan":
        // validasi tutup buku transaksi belum dibereskan
        /* $closing_date = getDateYmd($_REQUEST['fdawalperiodebln']);
        $ssql="SELECT fnid FROM tlperiode_transaction a WHERE fdawalperiodebln='{$closing_date}' AND fnidtutup = 1 ";

        if(!RetRstField($ssql)) {
            echo "<script>
                    alert('Tutup Buku Transaksi belum dijalankan. Harap Tutup Buku Transaksi terlebih dahulu.');
                    window.history.back();
                </script>";
            die("");
        } */

        break;
        
    case "update":
        $closing_date = getDateYmd($_REQUEST['fdawalperiodebln']);
        $ssql="SELECT fnid FROM tlperiode_transaction a WHERE fdawalperiodebln='{$closing_date}' AND fnidtutup = 1 ";
        
        if(RetRstField($ssql)) {
            echo "<script>
                    alert('Buka Buku Transaksi belum dijalankan. Harap Buka Buku Transaksi terlebih dahulu. {$closing_date}');
                    window.history.back();
                </script>";
            die("");
        }

        break;    
}
    
function SaveData() {
    global $objAxpDB;
    $objAxpDB->Allow_Save(false);
    $strSQL=array();
    switch($_REQUEST[mode]) {
    case "simpan":
        
        // create next month date
        $d = new DateTime(getDateYmd($_REQUEST['fdstart_month_periodD1'][1])); 
        $fdthis_month = ($d->format('n'));
        $fdnext_month = ($d->format('n')+1);
        $fdyear = $d->format('Y');
        
        if ($fdnext_month == 13) { 
          $fdnext_month = 1;  
          $fdcurrent_year = $fdyear;         
          $fdyear = $fdyear + 1;         
        } 

        // create next fdend_month_period
        $datetime=$fdyear."-".$fdnext_month."-01";
        $d_next = date_create($datetime);
        $fddate_end = $d_next->format('t');
        // echo $_REQUEST['fdstart_inputD1'][1] . "<br>";
        
        //$fdStartInput = getDateYmd($_REQUEST['fdstart_inputD1'][1]); 
        $fdStartInput = $_REQUEST['fdstart_inputD1'][1]; 
        //$fdStartPeriod = getDateYmd($_REQUEST['fdstart_periodD1'][1]); 
        $fdStartPeriod = $_REQUEST['fdstart_periodD1'][1]; 
        $fdstartMonthPeriod = getDateYmd($_REQUEST['fdstart_month_periodD1'][1]); 
        $fdendMonthPeriod = getDateYmd($_REQUEST['fdend_month_periodD1'][1]); 
        // echo $fdStartInput . " " . $fdStartPeriod;
        
        $closing_date = RetRstField("SELECT fnid FROM tlperiode WHERE fdstart_month_period='{$fdstartMonthPeriod}'");
        
        if ($closing_date == "") {          
            $strSQL['Insert TlPeriode New'][]=" 
                INSERT INTO tlperiode (ftbranch_code,fdstart_input, fdstart_period, fdstart_month_period, fdend_month_period, fbclosed, 
                                        ftcreated_user, fdcreated_date, ftmodified_user, fdmodified_date)
                SELECT '{$_SESSION['strbranch']}','{$fdStartInput}','{$fdStartPeriod}','{$fdstartMonthPeriod}','{$fdendMonthPeriod}',1,
                                        '{$_SESSION['username']}', now(),'{$_SESSION['username']}', now()";
        } else {
            $strSQL['Update TlPeriode Status'][]="Update tlperiode SET fbclosed = 1,ftmodified_user = '{$_SESSION['username']}', fdmodified_date = now()  
                                    WHERE fdstart_month_period='{$fdstartMonthPeriod}'";
        }        
        
        $strSQL['Insert TlPeriode Next'][]=" 
                  INSERT INTO tlperiode (ftbranch_code,fdstart_input, fdstart_period, fdstart_month_period, fdend_month_period, fbclosed, 
                      ftcreated_user, fdcreated_date, ftmodified_user, fdmodified_date)
                  SELECT '{$_SESSION['strbranch']}','{$fdStartInput}','{$fdyear}-01-01','{$fdyear}-{$fdnext_month}-01','{$fdyear}-{$fdnext_month}-{$fddate_end}',0,
                      '{$_SESSION['username']}', now(),'{$_SESSION['username']}', now()";
        
        // Insert THSaldoPerkiraan ketika akhir tahun              
        if ($fdthis_month == 12) { 
            $ssql ="INSERT INTO thsaldoperkiraan (ftbranch_code, fnindex, ftkode, fdperiodesaldo, fcdebit, fccredit,  
                            ftcreated_user, fdcreated_date, ftmodified_user, fdmodified_date)
                    SELECT '{$_SESSION['strbranch']}',@row_num := @row_num + 1,ftcoa_code,'{$fdyear}-01-01',0,0,
                            '{$_SESSION['username']}', now(),'{$_SESSION['username']}', now() 
                    FROM tlcoa , (SELECT @row_num:=0) AS t WHERE fnstatus = 1 ORDER BY ftcoa_code;";
            // INSERT DATA TO THSALDOPERKIRAAN DIRECTLY
            mysql_query($ssql);

            // query neraca perincian            
            $ssql2= "SELECT * FROM 
                    (
                    SELECT z1.ftbranch_code,z1.ftcoa_code AS ftcoa_code,z2.ftcoa_name,z1.ftDescription,z1.ftcoa_subgroup_code, 
                            z1.ftGroup, -- SUM(z1.fcsaldo) AS fcsaldo,
                            CASE WHEN LEFT(z1.ftcoa_code,1)='1' THEN SUM(z1.fcsaldo) ELSE (SUM(z1.fcsaldo) * -1) END AS fcsaldo
                    FROM
                    (
                            -- Saldo Awal Aktiva dan Kewajiban dan Modal
                            SELECT a.ftbranch_code,a.ftkode AS ftcoa_code,b.ftcoa_name, c.ftcoa_subgroup_name AS ftDescription, c.ftcoa_subgroup_code,
                                    CASE WHEN LEFT(a.ftkode,1)='1' THEN 'AKTIVA' ELSE 'KEWAJIBAN DAN MODAL' END AS ftGroup,
                                    CASE WHEN LEFT(a.ftkode,1)='1'  
                                            THEN SUM(IFNULL(a.fcDebit,0)) - SUM(IFNULL(a.fccredit,0)) 
                                            ELSE SUM(IFNULL(a.fccredit,0)) - SUM(IFNULL(a.fcDebit,0)) END AS fcSaldo
                            FROM THSaldoPerkiraan a 
                            INNER JOIN TLCOA b ON a.ftkode = b.ftcoa_code AND a.ftBranch_Code = b.ftBranch_Code
                            INNER JOIN tlcoa_subgroup c ON c.ftcoa_subgroup_code = b.ftcoa_subgroup_code 
                            WHERE (LEFT(ftkode,1) IN (1,2,3)) AND (RIGHT(ftkode,4) <> '0000') AND (ftkode <> '')
                                    AND YEAR(fdPeriodeSaldo) = '{$fdcurrent_year}'
                            GROUP BY a.ftBranch_Code, a.ftkode

                            -- Jurnal Aktiva dan Kewajiban dan Modal
                            UNION ALL	
                            SELECT b.ftBranch_Code, b.ftcoa_code AS ftcoa_code,c.ftcoa_name, d.ftcoa_subgroup_name AS ftDescription, c.ftcoa_subgroup_code,	
                                     CASE WHEN LEFT(b.ftcoa_code,1)='1' THEN 'AKTIVA' ELSE 'KEWAJIBAN DAN MODAL' END AS ftGroup,													
                                     CASE WHEN LEFT(b.ftcoa_code,1)='1' 
                                            THEN SUM(IFNULL(b.fcDebit,0)) - SUM(IFNULL(b.fccredit,0)) 
                                            ELSE SUM(IFNULL(b.fccredit,0)) - SUM(IFNULL(b.fcDebit,0)) END AS fcSaldo
                            FROM txgeneral_journal_hdr a
                            INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no AND a.ftBranch_Code = b.ftBranch_Code
                            INNER JOIN TLCOA c ON c.ftcoa_code = b.ftcoa_code AND c.ftBranch_Code = b.ftBranch_Code
                            INNER JOIN tlcoa_subgroup d ON d.ftcoa_subgroup_code = c.ftcoa_subgroup_code 
                            WHERE (LEFT(b.ftcoa_code,1) IN (1,2,3)) AND (RIGHT(b.ftcoa_code,4) <> '0000') AND (b.ftcoa_code <> '')
                                    AND YEAR(a.fdtrans_date) = '{$fdcurrent_year}' AND MONTH(a.fdtrans_date) <= '12'
                                    AND a.fnStatus != 3 AND a.fnStatus != 9 
                            GROUP BY b.ftBranch_Code, b.ftcoa_code
                            
                    ) Z1
                    LEFT JOIN 
                            (SELECT ftcoa_code, ftcoa_name FROM TLCOA 
                             UNION ALL SELECT '3199.0000' AS ftcoa_code, 'Laba (Rugi) Ditahan Tahun Berjalan' AS ftcoa_name )
                    Z2 ON z1.ftcoa_code = z2.ftcoa_code
                    GROUP BY z1.ftbranch_code,z1.ftDescription,z1.ftGroup,ftcoa_name

                    ) X
                    WHERE (fcSaldo <> 0)
                    GROUP BY ftDescription,ftcoa_name,ftcoa_code
                    ORDER BY ftGroup,ftcoa_code ";
            
            $rs=mysql_query($ssql2);
            
            // die ($ssql2);
            while($r=mysql_fetch_assoc($rs)) {
                // Update Data THSaldoPerkiraan 
                if ($r['fcsaldo'] > 0) {
                    $fcdebit = $r['fcsaldo'];
                    $fccredit = 0;
                } else {
                    $fcdebit = 0;
                    $fccredit = $r['fcsaldo'] * -1;
                }
                        
                $strSQL['Update THSaldoPerkiraan'][]=" 
                    Update THSaldoPerkiraan SET fcdebit={$fcdebit},fccredit={$fccredit} WHERE fdperiodesaldo = '{$fdyear}-01-01' AND ftkode = '{$r['ftcoa_code']}'";
                    
            }
            
        }               
                      
        break;
    }

    return $strSQL;
}

function SaveDataAfter() {
    global $objAxpDB;
    $strSQL=array();
    
    switch($_REQUEST[mode]) {
        case "simpan":
            $coa_labarugi = RetRstField("SELECT ftcoa_code FROM tlcoa_setting WHERE ftcoa_no = 'ACC01'");
            
            $d = new DateTime(getDateYmd($_REQUEST['fdstart_month_periodD1'][1])); 
            $fdthis_month = ($d->format('n'));
            $fdyear = $d->format('Y');

            // Insert THSaldoPerkiraan ketika akhir tahun              
            if ($fdthis_month == 12) { 
                $fdcurrent_year = $fdyear;         
                $fdyear = $fdyear + 1;     
              
                // query neraca perincian (laba rugi)           
                $ssql2= "SELECT * FROM 
                        (
                        SELECT z1.ftbranch_code,z1.ftcoa_code AS ftcoa_code,z1.ftcoa_name,z1.ftDescription,z1.ftcoa_subgroup_code, 
                                z1.ftGroup, -- SUM(z1.fcsaldo) AS fcsaldo,
                                CASE WHEN LEFT(z1.ftcoa_code,1)='1' THEN SUM(z1.fcsaldo) ELSE (SUM(z1.fcsaldo) * -1) END AS fcsaldo
                        FROM
                        (
                                -- Saldo Awal Aktiva dan Kewajiban dan Modal
                                SELECT a.ftbranch_code,a.ftkode AS ftcoa_code,b.ftcoa_name, c.ftcoa_subgroup_name AS ftDescription, c.ftcoa_subgroup_code,
                                        CASE WHEN LEFT(a.ftkode,1)='1' THEN 'AKTIVA' ELSE 'KEWAJIBAN DAN MODAL' END AS ftGroup,
                                        CASE WHEN LEFT(a.ftkode,1)='1'  
                                                THEN SUM(IFNULL(a.fcDebit,0)) - SUM(IFNULL(a.fccredit,0)) 
                                                ELSE SUM(IFNULL(a.fccredit,0)) - SUM(IFNULL(a.fcDebit,0)) END AS fcSaldo
                                FROM THSaldoPerkiraan a 
                                INNER JOIN TLCOA b ON a.ftkode = b.ftcoa_code AND a.ftBranch_Code = b.ftBranch_Code
                                INNER JOIN tlcoa_subgroup c ON c.ftcoa_subgroup_code = b.ftcoa_subgroup_code 
                                WHERE (LEFT(ftkode,1) IN (1,2,3)) AND (RIGHT(ftkode,4) <> '0000') AND (ftkode <> '')
                                        AND YEAR(fdPeriodeSaldo) = '{$fdcurrent_year}' AND a.ftkode = '{$coa_labarugi}'
                                GROUP BY a.ftBranch_Code, a.ftkode

                                -- Jurnal Aktiva dan Kewajiban dan Modal
                                UNION ALL	
                                SELECT b.ftBranch_Code, b.ftcoa_code AS ftcoa_code,c.ftcoa_name, d.ftcoa_subgroup_name AS ftDescription, c.ftcoa_subgroup_code,	
                                         CASE WHEN LEFT(b.ftcoa_code,1)='1' THEN 'AKTIVA' ELSE 'KEWAJIBAN DAN MODAL' END AS ftGroup,													
                                         CASE WHEN LEFT(b.ftcoa_code,1)='1' 
                                                THEN SUM(IFNULL(b.fcDebit,0)) - SUM(IFNULL(b.fccredit,0)) 
                                                ELSE SUM(IFNULL(b.fccredit,0)) - SUM(IFNULL(b.fcDebit,0)) END AS fcSaldo
                                FROM txgeneral_journal_hdr a
                                INNER JOIN txgeneral_journal_det b ON a.ftjournal_no = b.ftjournal_no AND a.ftBranch_Code = b.ftBranch_Code
                                INNER JOIN TLCOA c ON c.ftcoa_code = b.ftcoa_code AND c.ftBranch_Code = b.ftBranch_Code
                                INNER JOIN tlcoa_subgroup d ON d.ftcoa_subgroup_code = c.ftcoa_subgroup_code 
                                WHERE (LEFT(b.ftcoa_code,1) IN (1,2,3)) AND (RIGHT(b.ftcoa_code,4) <> '0000') AND (b.ftcoa_code <> '')
                                        AND YEAR(a.fdtrans_date) = '{$fdcurrent_year}' 
                                        AND a.fnStatus != 3 AND a.fnStatus != 9 AND b.ftcoa_code = '{$coa_labarugi}'
                                GROUP BY b.ftBranch_Code, b.ftcoa_code
                                
                                -- Laba Rugi Akhir Tahun
                                UNION ALL
                                SELECT ftBranch_Code,'{$coa_labarugi}' AS ftcoa_code,'' AS ftcoa_name, 
                                        'Equitas - Laba Ditahan' AS ftDescription, ftcoa_subgroup_code, 'KEWAJIBAN DAN MODAL' AS ftGroup, 
                                        SUM(fcSaldo) AS fcSaldo
                                FROM
                                (		
                                        SELECT DISTINCT m1.ftbranch_code,m0.`ftcoa_code`,m0.`ftcoa_name`,m0.ftcoa_subgroup_code,  
                                                SUM(IFNULL(m1.fccredit,0))-SUM(IFNULL(m1.fcdebit,0)) AS fcsaldo
                                        FROM tlcoa m0
                                        #Sort Jurnal
                                        LEFT JOIN (
                                                SELECT a.`ftbranch_code`,b.`ftcoa_code` AS ftcoa_code,SUM(b.fcdebit) AS fcdebit, SUM(b.fccredit) AS fccredit
                                                FROM txgeneral_journal_hdr a
                                                LEFT JOIN txgeneral_journal_det b ON a.ftjournal_no=b.ftjournal_no
                                                WHERE a.fnStatus != 3 AND a.fnStatus != 9 
                                                        AND YEAR(a.fdtrans_date) = '{$fdcurrent_year}' 
                                                GROUP BY a.`ftbranch_code`,LEFT(b.`ftcoa_code`,4)

                                        ) m1 ON LEFT(m0.`ftcoa_code`,4) = LEFT(m1.`ftcoa_code`,4)
                                        WHERE LEFT(m0.`ftcoa_code`,1) >= 4 AND RIGHT(m0.`ftcoa_code`,4) = '0000'
                                        GROUP BY m0.ftcoa_code
                                ) X
                                GROUP BY ftbranch_code,ftcoa_code	
                        ) Z1
                        GROUP BY z1.ftbranch_code,z1.ftcoa_code

                        ) X
                        WHERE (fcSaldo <> 0)
                        GROUP BY ftDescription,ftcoa_name,ftcoa_code
                        ORDER BY ftGroup,ftcoa_code ";

                $rs=mysql_query($ssql2);
                while($r=mysql_fetch_assoc($rs)) {
                    // Update Data THSaldoPerkiraan 
                    if ($r['fcsaldo'] > 0) {
                        $fcdebit = $r['fcsaldo'];
                        $fccredit = 0;
                    } else {
                        $fcdebit = 0;
                        $fccredit = $r['fcsaldo'] * -1;
                    }

                    $strSQL['Update THSaldoPerkiraan'][]=" 
                        Update THSaldoPerkiraan SET fcdebit={$fcdebit},fccredit={$fccredit} WHERE fdperiodesaldo = '{$fdyear}-01-01' AND ftkode = '{$r['ftcoa_code']}'";

                }

            }               
                      
    
        break;
    }
	
    return $strSQL;

}

function UpdateData() {
    global $objAxpDB;
    $objAxpDB->Allow_Update(false);
    $strSQL=array();
    switch($_REQUEST[mode]) {
    case "update":
       
        $fdawalperiodebln = getDateYmd($_REQUEST['fdawalperiodebln']); 
        $fdakhirperiodebln = getDateYmd($_REQUEST['fdakhirperiodebln']); 

        // History user buka buku                            
        $strSQL['Insert THPeriode Open'][]=" 
                  INSERT INTO thperiode_open (fdawalperiodebln, fdakhirperiodebln,  
                      ftcreated_user, fdcreated_date, ftmodified_user, fdmodified_date)
                  SELECT '{$fdawalperiodebln}','{$fdakhirperiodebln}',
                      '{$_SESSION['username']}', now(),'{$_SESSION['username']}', now()";
                      
        // Hapus Transaksi buka buku              
        $strSQL['Delete TLPeriode'][]="Delete From tlperiode WHERE fdawalperiodebln>'{$fdawalperiodebln}'";
              
        // Update Status Transaksi Tutup Buku Terakhir
        $strSQL['Update Status'][]="Update tlperiode SET fnidtutup = 0,ftmodified_user = '{$_SESSION['username']}', fdmodified_date = now()  
                                    ORDER BY fdawalperiodebln DESC LIMIT 1";
                                    
        break;
        
    }

    return $strSQL;
 }
