<?php
if(!function_exists(ValAR)){
    function ValAR() {
        if(RetRstField("SELECT fnid FROM tlcustomer WHERE ftcustomer_code='{$_REQUEST['ftcustomer_code']}'")) {
            echo"<script>alert('Customer tidak valid...'); window.history.back();</script>";
            exit;
        }
    }
}

switch($_REQUEST[mode]) {
    case "batal":
      $ssql="select a.ftar_no,b.ftsales_invoice_no
                from txar_hdr a,
                        txar_det b
                where a.ftar_no=b.ftar_no and
                         a.fnstatus<>9 and a.fnstatus<>3
                  and b.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";

        $rs=mysql_query($ssql);
        $str="No. Transaksi Penerimaan piutang ini harus dibatal kan dahulu :";
        while($r=mysql_fetch_assoc($rs)) {
            $str.=$r['ftar_no'];
        }
    //   die($str);
         if(RetRstField($ssql)) {
               echo "<script>
                    alert('$str');
                    window.history.back();
                </script>";
                die("");
         }

         $ssql="SELECT fnid FROM txsales_return_hdr where fnstatus<>9 AND ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";
         $rs=mysql_query($ssql);
         $str="No. Transaksi ini sudah digunakan untuk retur penjualan.";
         if(RetRstField($ssql)) {
               echo "<script>
                    alert('{$str}');
                    window.history.back();
                </script>";
                die("");
         }



          break;
    case "revisi":
         $ssql="SELECT ftsales_invoice_no, ftsales_return_no FROM txsales_return_hdr where fnstatus<>9";
         $rs=mysql_query($ssql);
          $str="No. Transaksi ini sudah digunakan untuk retur penjualan : ";
         while( $r=mysql_fetch_assoc($rs))
            {
             $str.=$r['ftsales_return_no'];
                if($r['ftsales_invoice_no']==$_REQUEST['ftsales_invoice_no'])
                {
                    echo "<script>
                    alert('$str');
                    window.history.back();
                </script>";

                die("");
                }
            }

        $ssql="select a.ftar_no,b.ftsales_invoice_no
                from txar_hdr a,
                        txar_det b
                where a.ftar_no=b.ftar_no and
                         a.fnstatus<>9 and a.fnstatus2<>1
                  and b.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";

        $rs=mysql_query($ssql);
        $str="No. Transaksi Penerimaan piutang ini harus dibatal kan dahulu :";
        while($r=mysql_fetch_assoc($rs)) {
            $str.=$r['ftar_no'];
        }
    //   die($str);
         if(RetRstField($ssql)) {
               echo "<script>
                    alert('$str');
                    window.history.back();
                </script>";
                die("");
         }

          break;

    case "update":
    case "simpan":
        
        if ($_SESSION["SOManual"]) { 
            // Validasi Invoice tidak dibuat untuk DO yg sama
            $ssql = "SELECT ftdo_no FROM txsales_invoice_hdr a
                        WHERE a.ftdo_no='{$_REQUEST['ftdo_no']}' AND a.fnstatus <> 3 and a.fnstatus <> 9 ";

            if(RetRstField($ssql)) {
                echo "<script>
                        alert('Invoice utk No DO : {$_REQUEST['ftdo_no']} sudah terbuat. Harap dicek kembali. ');
                        window.location='{$url}?form={$form}&mode=edit&id={$_REQUEST['id']}';
                      </script>";
                die("");
            }
            
        } else if ($_SESSION["SIAutoDO"]) {
            // Update ALL Stock Detail QTY
            include "tools/update_stock_det.php";
            UpdateStockDetail();
            
            // Double Check QTY Available > Qty Order
            for($j=1; $j<count($_REQUEST['fnindexD0']); $j++) {
                if(((float)CCur($_REQUEST['fnqtyD0'][$j])>0) && ($_REQUEST['ftstock_category_codeD0'][$j] != 'srv')){
                    $ssql=" SELECT IFNULL(SUM(a.fnqty_available),0) as fnqty_available 
                            FROM tlstock_det a
                            WHERE a.ftwarehouse_code='{$_REQUEST['ftwarehouse_codeD0'][$j]}' 
                                    AND a.ftlocation_code='{$_REQUEST['ftlocation_codeD0'][$j]}'  
                                    AND a.ftitem_code='{$_REQUEST['ftitem_codeD0'][$j]}'  
                                    AND a.fdexp_date='{$_REQUEST['fdexp_dateD0'][$j]}' ";
                    $qty_avail = RetRstField($ssql);                

                    // get current si qty
                    if ($_REQUEST[mode] == "simpan_revisi") {
                        $ssql=" SELECT IFNULL(SUM(a.fnqty_base),0) AS fnqty_base 
                                FROM txsales_invoice_det a
                                WHERE a.ftwarehouse_code='{$_REQUEST['ftwarehouse_codeD0'][$j]}' 
                                        AND a.ftlocation_code='{$_REQUEST['ftlocation_codeD0'][$j]}'  
                                        AND a.ftitem_code='{$_REQUEST['ftitem_codeD0'][$j]}'  
                                        AND a.ftso_no='{$_REQUEST['ftso_no']}' ";
                        $qty_curr = RetRstField($ssql);      
                        $qty_avail = $qty_avail + $qty_curr;
                    }

                    if ($_REQUEST['fnqty_baseD0'][$j] > $qty_avail) {
                        $url = $_SERVER["PHP_SELF"];
                        $form = $_REQUEST["form"];

                        echo"  <script>
                                    alert('Qty Item {$_REQUEST['ftitem_codeD0'][$j]} tidak mencukupi. Harap dicek kembali '); 
                                    window.location='{$url}?form={$form}&mode=edit&id={$_REQUEST['id']}';
                                </script>";
                        exit;    
                    }                                
                }
            }
                
        }   
        break;
        
    case "final":
        
        break;    
}


function SaveData() {
    global $objAxpDB;   
    $strSQL=array();
    //$objAxpDB->Allow_Save(false);
    
    switch($_REQUEST[mode]) {
        case "simpan":
            // auto generate number when save
            if ($_REQUEST['fntax_code']==0) {
                $ftmodule_code = "TN006";
            }
            else if ($_REQUEST['fntax_code']==1) {
                $ftmodule_code = "TN021";
            }
            else if ($_REQUEST['fntax_code']==2) {
                $ftmodule_code = "TN022";
            }
            $ftsi_no=getAutoNumber($_SESSION['strbranch'],$ftmodule_code,'txsales_invoice_hdr','ftsales_invoice_no',$_REQUEST['fdtrans_date']);
            $_REQUEST['ftsales_invoice_no'] = $ftsi_no;    
            
            // untuk case apa yah ?
            // $strSQL["Update Status Final DO"][]="UPDATE txdelivery_order_hdr SET fnstatus=2 WHERE ftdo_no='{$_REQUEST['ftdo_no']}'";
            // Simpan auto create DO
            if ($_SESSION["SIAutoDO"]) {
                // Penomoran SI dan DO harus sama kecuali prefix nya saja.
                $ftsi_code = RetRstField("SELECT ftcode FROM tstransaction_number WHERE ftmodule_code = '{$ftmodule_code}' and ftbranch_code = '{$_SESSION['strbranch']}'");
                $ftdo_code = RetRstField("SELECT ftcode FROM tstransaction_number WHERE ftmodule_code = 'TN005' and ftbranch_code = '{$_SESSION['strbranch']}'");
                $ftdo_no = str_replace($ftsi_code,$ftdo_code,$ftsi_no); 
        
                $ftstock_out_no=getAutoNumber($_SESSION['strbranch'],'inv005','txstock_out_hdr','ftstock_out_no',$_REQUEST['fdtrans_date']);

                $fdtrans_date_original = $_REQUEST["fdtrans_date"];
                $_REQUEST["fdtrans_date"]=getDateYmd($_REQUEST["fdtrans_date"]);
                
                $ssql = mysql_query("SELECT ftcustomer_name FROM tlcustomer WHERE ftcustomer_code='{$_REQUEST["ftcustomer_code"]}'");
                $r = mysql_fetch_assoc($ssql);

                if ($_REQUEST['fntax_code'] == '0') { $fdvat_date = $_REQUEST["fdtrans_date"]; }

                $strSQL['Insert DO Hdr'][]="
                    INSERT INTO txdelivery_order_hdr(
                        ftbranch_code,
                        ftdo_no, ftdo_type, ftso_no,
                        ftcustomer_code, ftcustomer_name,ftship_address, fdtrans_date,
                        ftdelivery_method, ftreference, ftnotes,
                        fftotal_qty, fnstatus, ftsales_code,
                        ftcreated_user, fdcreated_date ) 
                    SELECT
                        '{$_SESSION['strbranch']}' AS ftbranch_code,
                        '{$ftdo_no}' AS ftdo_no, '0' AS ftdo_type, '{$_REQUEST["ftso_no"]}' AS ftso_no,
                        '".js_esc($_REQUEST["ftcustomer_code"])."' AS ftcustomer_code,'".js_esc($r['ftcustomer_name'])."' AS ftcustomer_name,'".js_esc($_REQUEST["ftship_address"])."' AS ftship_address, '{$_REQUEST["fdtrans_date"]}' AS fdtrans_date,
                        '{$_REQUEST["ftdelivery_method"]}' AS ftdelivery_method, '".js_esc($_REQUEST["ftreference"])."' AS ftreference, '".js_esc($_REQUEST["ftnotes"])."' AS ftnotes,
                        '".CCur($_REQUEST["fntotal_qty"])."' AS fftotal_qty, '".CCur($_REQUEST["fnstatus"])."' AS fnstatus, '{$_REQUEST['ftsales_code']}',
                        '{$_SESSION['username']}' AS ftcreated_user, now() AS fdcreated_date";

                $strSQL['Insert Stock Out Hdr'][]="
                        INSERT INTO txstock_out_hdr
                            (ftbranch_code, ftstock_out_no, ftcategory_code,ftreff_no,
                            ftstock_out_company, ftdocument_no, fdtrans_date,
                            fntotal_qty, ftnotes, fnstatus, ftcreated_user,
                            fdcreated_date, ftmodified_user, fdmodified_date, ftstock_type_code)
                        SELECT
                            '{$_SESSION['strbranch']}' AS ftbranch_code, '{$ftstock_out_no}' AS ftstock_out_no, '4' AS ftcategory_code,'".js_esc($_REQUEST['ftreference'])."',
                            '{$_REQUEST['ftcustomer_code']}' AS  ftstock_out_company, '{$ftdo_no}' AS ftdocument_no,'{$_REQUEST['fdtrans_date']}' AS  fdtrans_date,
                            '".CCur($_REQUEST['fntotal_qty'])."' AS fntotal_qty,'".js_esc($_REQUEST['ftnotes'])."' AS  ftnotes, '1' AS fnstatus,  '{$_SESSION['username']}' AS ftcreated_user,
                            now() AS fdcreated_date, '{$_SESSION['username']}' AS ftmodified_user, now() AS fdmodified_date, 'inv' as ftstock_type_code";

                for($j=1; $j<count($_REQUEST['fnindexD0']); $j++) {
                    if((float)CCur($_REQUEST['fnqtyD0'][$j])>0){
                        $rs=mysql_query("select ftitem_name from tlstock_hdr where ftitem_code='".$_REQUEST['ftitem_codeD0'][$j]."'");
                        $r=mysql_fetch_assoc($rs);

                        $ftprint_name = $_REQUEST['ftprint_nameD0'][$j];

                        $strSQL['Insert DO Det'][]="
                                INSERT INTO txdelivery_order_det
                                    (ftbranch_code, ftdo_no, fnindex, 
                                    ftitem_code, ftitem_name, ftprint_name,  
                                    ftwarehouse_code, ftlocation_code,
                                    fnqty_available, fnqty_so,fnqty_so_base,
                                    fnqty, ftuom_code, fdtrans_date,
                                    ftdescription,
                                    fnqty_base,ffformula,
                                    ftcreated_user, fdcreated_date,
                                    ftmodified_user, fdmodified_date)
                                SELECT
                                    '{$_SESSION['strbranch']}' AS ftbranch_code, '{$ftdo_no}' AS ftdo_no, '{$j}' AS fnindex, 
                                    '".js_esc($_REQUEST['ftitem_codeD0'][$j])."' AS ftitem_code, '".js_esc($r['ftitem_name'])."' AS ftitem_name, '".js_esc($ftprint_name)."' AS ftprint_name, 
                                    '{$_REQUEST['ftwarehouse_codeD0'][$j]}' AS ftwarehouse_code, '{$_REQUEST['ftlocation_codeD0'][$j]}' AS ftlocation_code,
                                    '".CCur($_REQUEST['fnqty_availableD0'][$j])."' AS fnqty_available, '".CCur($_REQUEST['fnqty_SOD0'][$j])."' AS fnqty_so, '".CCur($_REQUEST['fnqty_SO_baseD0'][$j])."' AS fnqty_so_base,
                                    '".CCur($_REQUEST['fnqtyD0'][$j])."' AS fnqty, '{$_REQUEST['ftuom_codeD0'][$j]}' AS ftuom_code, '{$_REQUEST["fdtrans_date"]}' AS  fdtrans_date,
                                    '".js_esc($_REQUEST['ftdescriptionD0'][$j])."' AS ftdescription, 
                                    '".CCur($_REQUEST['fnqty_baseD0'][$j])."' AS fnqty_base, '".CCur($_REQUEST['ffformulaD0'][$j])."' AS ffformula,
                                    '{$_SESSION['username']}' AS ftcreated_user, now() AS fdcreated_date,
                                    '{$_SESSION['username']}' AS ftmodified_user, now() AS fdmodified_date";

                        $strSQL['Insert Stock Out Det'][]="
                            INSERT INTO txstock_out_det
                                (ftbranch_code, ftstock_out_no, fnindex, ftitem_code,
                                ftwarehouse_code, ftlocation_code, fnqty_available,
                                fnqty, ftuom_code, fnqty_base,ffformula,
                                ftcreated_user, fdcreated_date, ftmodified_user, fdmodified_date,
                                ftprint_name,ftdescription)
                            SELECT
                                '{$_SESSION['strbranch']}' AS ftbranch_code,'{$ftstock_out_no}' AS ftstock_out_no, '{$j}' AS fnindex, '".js_esc($_REQUEST['ftitem_codeD0'][$j])."' AS ftitem_code,
                                '{$_REQUEST['ftwarehouse_codeD0'][$j]}' AS ftwarehouse_code, '{$_REQUEST['ftlocation_codeD0'][$j]}' AS ftlocation_code, '".CCur($_REQUEST['fnqty_availableD0'][$j])."' AS fnqty_available,
                                '".CCur($_REQUEST['fnqtyD0'][$j])."' AS fnqty, '{$_REQUEST['ftuom_codeD0'][$j]}' AS ftuom_code, '".CCur($_REQUEST['fnqty_baseD0'][$j])."' AS fnqty_base, '".CCur($_REQUEST['ffformulaD0'][$j])."' AS ffformula,
                                '{$_SESSION['username']}' AS ftcreated_user, now() AS fdcreated_date, '{$_SESSION['username']}' AS ftmodified_user, now() AS fdmodified_date,
                                '".js_esc($ftprint_name)."','".js_esc($_REQUEST['ftdescriptionD0'][$j])."'";    

                        $m=SaveStock('-',$_REQUEST['ftitem_codeD0'][$j],'',$_REQUEST['ftwarehouse_codeD0'][$j],$_REQUEST['ftlocation_codeD0'][$j],'',CCur($_REQUEST['fnqty_baseD0'][$j]),CCur($_REQUEST['fnqty_baseD0'][$j]));
                        $strSQL=array_merge_recursive($strSQL,$m);

                    }
                }
                    
                $_REQUEST["fdtrans_date"] = $fdtrans_date_original;
            
            }
              
           
       break;
      
    }
    #pr($strSQL);die("mm");
    return $strSQL;
}

function SaveDataLast() {
    global $objAxpDB;
    $strSQL=array();
    switch($_REQUEST[mode]) {
        case "simpan":
            $strSQL["Update Customer Name"][]="UPDATE txsales_invoice_hdr a, tlcustomer b SET a.ftcustomer_name=b.ftcustomer_name WHERE a.ftcustomer_code = b.ftcustomer_code AND  a.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";
            $strSQL["Update Stock Name"][]="UPDATE txsales_invoice_det a, tlstock_hdr b SET a.ftitem_name=b.ftitem_name, a.ftprint_name = CASE WHEN((a.ftprint_name IS NULL) OR (a.ftprint_name= '')) THEN b.ftprint_name ELSE a.ftprint_name END WHERE a.ftitem_code = b.ftitem_code AND  a.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";

            // update status kirim menjadi sebagian dahulu (default)
            $strSQL["Update Status SO"][]="UPDATE txsales_order_hdr a SET a.fnreceive_status=2, a.fnstatus = 2 WHERE a.ftso_no='{$_REQUEST['ftso_no']}'";

            break;        
    }

    return $strSQL;
}

function SaveDataAfter() {
    global $objAxpDB;
    $strSQL=array();
    switch($_REQUEST[mode]) {
        case "simpan":
            
            // Simpan auto create DO
            if ($_SESSION["SIAutoDO"] ) {
                // delete item with qty 0
                $ssql="DELETE FROM txsales_invoice_det WHERE fnqty=0 AND ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}' ";
                $strSQL['Sales Invoice Index'][]= $ssql;
                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }

                // reindex item detail
                $ssql=" update txsales_invoice_det a,
                            (select c.fnid,(select ifnull(count(d.fnid),0)+1
                                            from txsales_invoice_det d
                                            where d.fnid < c.fnid and d.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}') as m
                                    from  txsales_invoice_det c
                                    where c.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}') b
                        set a.fnindex=b.m
                        where a.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}' and a.fnid=b.fnid";

                $strSQL['Sales Invoice Index'][]= $ssql;
                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }
                
                // update no DO di transaksi SI
                if ($_REQUEST['fntax_code']==0) {
                    $ftmodule_code = "TN006";
                }
                else if ($_REQUEST['fntax_code']==1) {
                    $ftmodule_code = "TN021";
                }
                else if ($_REQUEST['fntax_code']==2) {
                    $ftmodule_code = "TN022";
                }
                $ftsi_code = RetRstField("SELECT ftcode FROM tstransaction_number WHERE ftmodule_code = '{$ftmodule_code}' and ftbranch_code = '{$_SESSION['strbranch']}'");
                $ftdo_code = RetRstField("SELECT ftcode FROM tstransaction_number WHERE ftmodule_code = 'TN005' and ftbranch_code = '{$_SESSION['strbranch']}'");
                $ftdo_no = str_replace($ftsi_code,$ftdo_code,$_REQUEST['ftsales_invoice_no']); 
        
                $ssql="UPDATE txsales_invoice_hdr SET ftdo_no='{$ftdo_no}' WHERE ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}' ";
                $strSQL['Sales Invoice Update No DO'][]= $ssql;
                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }

                // Update Qty Accept SO
                $ssql="UPDATE txsales_order_det a INNER JOIN ( 
                            SELECT SUM(b.fnqty) AS fnqty, SUM(b.fnqty_base) AS fnqty_base, b.ftitem_code, b.fnindex_so, a.ftso_no
                            FROM txsales_invoice_hdr a 
                            INNER JOIN txsales_invoice_det b ON a.ftsales_invoice_no=b.ftsales_invoice_no  
                            WHERE a.fnstatus != 3 and a.fnstatus != 9 AND a.ftso_no = '{$_REQUEST['ftso_no']}'
                                    AND b.fnindex_so IS NOT NULL AND b.fnindex_so != ''
                            GROUP BY b.fnindex_so ) b
                        SET a.fnqty_accept = b.fnqty, a.fnqty_accept_base = b.fnqty_base  
                        WHERE a.fnindex = b.fnindex_so AND a.ftso_no = b.ftso_no; ";
                
                $strSQL["Update txsales_order_det Qty Accept"][]=$ssql;

                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }

                // set status terkirim apabila sudah memenuhi syarat
                $ssql=" UPDATE txsales_order_hdr a INNER JOIN ( 
                            SELECT a.ftso_no, COUNT(a.fnid) AS total_item, COUNT(c.fnindex) AS total_item_closed
                            FROM txsales_order_det a INNER JOIN txsales_order_hdr b ON a.ftso_no=b.ftso_no
                            LEFT JOIN txsales_order_det c ON c.ftso_no=a.ftso_no AND c.fnindex=a.fnindex AND c.fnqty_base = c.fnqty_accept_base
                            WHERE b.fnstatus != 3 AND b.fnstatus != 9  AND b.fnreceive_status != 3 AND b.ftso_no = '{$_REQUEST['ftso_no']}' 
                            GROUP BY b.ftso_no
                            HAVING total_item = total_item_closed ) b 
                        SET a.fnstatus=2 , a.fnreceive_status = 3 WHERE a.ftso_no = b.ftso_no; ";

                $strSQL["Update txsales_order_hdr Receipt1"][]=$ssql;

                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }
                
            }    
                        
            break;
        
    }

    return $strSQL;
}


function UpdateData() {
    global $objAxpDB;   
    $strSQL=array();
    $objAxpDB->Allow_Update(false);
    
    switch($_REQUEST[mode]) {
        case "update":
            // Simpan auto create DO
            if ($_SESSION["SIAutoDO"]) {
                // get tax code
                if ($_REQUEST['fntax_code']==0) {
                    $ftmodule_code = "TN006";
                }
                else if ($_REQUEST['fntax_code']==1) {
                    $ftmodule_code = "TN021";
                }
                else if ($_REQUEST['fntax_code']==2) {
                    $ftmodule_code = "TN022";
                }

                // Penomoran SI dan DO harus sama kecuali prefix nya saja.
                $ftsi_code = RetRstField("SELECT ftcode FROM tstransaction_number WHERE ftmodule_code = '{$ftmodule_code}' and ftbranch_code = '{$_SESSION['strbranch']}'");
                $ftdo_code = RetRstField("SELECT ftcode FROM tstransaction_number WHERE ftmodule_code = 'TN005' and ftbranch_code = '{$_SESSION['strbranch']}'");
                $ftdo_no = str_replace($ftsi_code,$ftdo_code,$_REQUEST['ftsales_invoice_no']); 
        
                $ftstock_out_no=getAutoNumber($_SESSION['strbranch'],'inv005','txstock_out_hdr','ftstock_out_no',$_REQUEST['fdtrans_date']);

                $fdtrans_date_original = $_REQUEST["fdtrans_date"];
                $_REQUEST["fdtrans_date"]=getDateYmd($_REQUEST["fdtrans_date"]);
                $_REQUEST["fnstatus"] = 1;
                
                $ssql = mysql_query("SELECT ftcustomer_name FROM tlcustomer WHERE ftcustomer_code='{$_REQUEST["ftcustomer_code"]}'");
                $r = mysql_fetch_assoc($ssql);

                if ($_REQUEST['fntax_code'] == '0') { $fdvat_date = $_REQUEST["fdtrans_date"]; }

                $strSQL['Insert DO Hdr'][]="
                    INSERT INTO txdelivery_order_hdr(
                        ftbranch_code,
                        ftdo_no, ftdo_type, ftso_no,
                        ftcustomer_code, ftcustomer_name,ftship_address, fdtrans_date,
                        ftdelivery_method, ftreference, ftnotes,
                        fftotal_qty, fnstatus, ftsales_code,
                        ftcreated_user, fdcreated_date ) 
                    SELECT
                        '{$_SESSION['strbranch']}' AS ftbranch_code,
                        '{$ftdo_no}' AS ftdo_no, '0' AS ftdo_type, '{$_REQUEST["ftso_no"]}' AS ftso_no,
                        '".js_esc($_REQUEST["ftcustomer_code"])."' AS ftcustomer_code,'".js_esc($r['ftcustomer_name'])."' AS ftcustomer_name,'".js_esc($_REQUEST["ftship_address"])."' AS ftship_address, '{$_REQUEST["fdtrans_date"]}' AS fdtrans_date,
                        '{$_REQUEST["ftdelivery_method"]}' AS ftdelivery_method, '".js_esc($_REQUEST["ftreference"])."' AS ftreference, '".js_esc($_REQUEST["ftnotes"])."' AS ftnotes,
                        '".CCur($_REQUEST["fntotal_qty"])."' AS fftotal_qty, '".CCur($_REQUEST["fnstatus"])."' AS fnstatus, '{$_REQUEST['ftsales_code']}',
                        '{$_SESSION['username']}' AS ftcreated_user, now() AS fdcreated_date";

                $strSQL['Insert Stock Out Hdr'][]="
                        INSERT INTO txstock_out_hdr
                            (ftbranch_code, ftstock_out_no, ftcategory_code,ftreff_no,
                            ftstock_out_company, ftdocument_no, fdtrans_date,
                            fntotal_qty, ftnotes, fnstatus, ftcreated_user,
                            fdcreated_date, ftmodified_user, fdmodified_date, ftstock_type_code)
                        SELECT
                            '{$_SESSION['strbranch']}' AS ftbranch_code, '{$ftstock_out_no}' AS ftstock_out_no, '4' AS ftcategory_code,'".js_esc($_REQUEST['ftreference'])."',
                            '{$_REQUEST['ftcustomer_code']}' AS  ftstock_out_company, '{$ftdo_no}' AS ftdocument_no,'{$_REQUEST['fdtrans_date']}' AS  fdtrans_date,
                            '".CCur($_REQUEST['fntotal_qty'])."' AS fntotal_qty,'".js_esc($_REQUEST['ftnotes'])."' AS  ftnotes, '1' AS fnstatus,  '{$_SESSION['username']}' AS ftcreated_user,
                            now() AS fdcreated_date, '{$_SESSION['username']}' AS ftmodified_user, now() AS fdmodified_date, 'inv' as ftstock_type_code";

                for($j=1; $j<count($_REQUEST['fnindexD0']); $j++) {
                    if((float)CCur($_REQUEST['fnqtyD0'][$j])>0){
                        $rs=mysql_query("select ftitem_name from tlstock_hdr where ftitem_code='".$_REQUEST['ftitem_codeD0'][$j]."'");
                        $r=mysql_fetch_assoc($rs);

                        $ftprint_name = $_REQUEST['ftprint_nameD0'][$j];

                        $strSQL['Insert DO Det'][]="
                                INSERT INTO txdelivery_order_det
                                    (ftbranch_code, ftdo_no, fnindex, 
                                    ftitem_code, ftitem_name, ftprint_name,  
                                    ftwarehouse_code, ftlocation_code,
                                    fnqty_available, fnqty_so,fnqty_so_base,
                                    fnqty, ftuom_code, fdtrans_date,
                                    ftdescription,
                                    fnqty_base,ffformula,
                                    ftcreated_user, fdcreated_date,
                                    ftmodified_user, fdmodified_date)
                                SELECT
                                    '{$_SESSION['strbranch']}' AS ftbranch_code, '{$ftdo_no}' AS ftdo_no, '{$j}' AS fnindex, 
                                    '".js_esc($_REQUEST['ftitem_codeD0'][$j])."' AS ftitem_code, '".js_esc($r['ftitem_name'])."' AS ftitem_name, '".js_esc($ftprint_name)."' AS ftprint_name, 
                                    '{$_REQUEST['ftwarehouse_codeD0'][$j]}' AS ftwarehouse_code, '{$_REQUEST['ftlocation_codeD0'][$j]}' AS ftlocation_code,
                                    '".CCur($_REQUEST['fnqty_availableD0'][$j])."' AS fnqty_available, '".CCur($_REQUEST['fnqty_SOD0'][$j])."' AS fnqty_so, '".CCur($_REQUEST['fnqty_SO_baseD0'][$j])."' AS fnqty_so_base,
                                    '".CCur($_REQUEST['fnqtyD0'][$j])."' AS fnqty, '{$_REQUEST['ftuom_codeD0'][$j]}' AS ftuom_code, '{$_REQUEST["fdtrans_date"]}' AS  fdtrans_date,
                                    '".js_esc($_REQUEST['ftdescriptionD0'][$j])."' AS ftdescription, 
                                    '".CCur($_REQUEST['fnqty_baseD0'][$j])."' AS fnqty_base, '".CCur($_REQUEST['ffformulaD0'][$j])."' AS ffformula,
                                    '{$_SESSION['username']}' AS ftcreated_user, now() AS fdcreated_date,
                                    '{$_SESSION['username']}' AS ftmodified_user, now() AS fdmodified_date";

                        $strSQL['Insert Stock Out Det'][]="
                            INSERT INTO txstock_out_det
                                (ftbranch_code, ftstock_out_no, fnindex, ftitem_code,
                                ftwarehouse_code, ftlocation_code, fnqty_available,
                                fnqty, ftuom_code, fnqty_base,ffformula,
                                ftcreated_user, fdcreated_date, ftmodified_user, fdmodified_date,
                                ftprint_name,ftdescription)
                            SELECT
                                '{$_SESSION['strbranch']}' AS ftbranch_code,'{$ftstock_out_no}' AS ftstock_out_no, '{$j}' AS fnindex, '".js_esc($_REQUEST['ftitem_codeD0'][$j])."' AS ftitem_code,
                                '{$_REQUEST['ftwarehouse_codeD0'][$j]}' AS ftwarehouse_code, '{$_REQUEST['ftlocation_codeD0'][$j]}' AS ftlocation_code, '".CCur($_REQUEST['fnqty_availableD0'][$j])."' AS fnqty_available,
                                '".CCur($_REQUEST['fnqtyD0'][$j])."' AS fnqty, '{$_REQUEST['ftuom_codeD0'][$j]}' AS ftuom_code, '".CCur($_REQUEST['fnqty_baseD0'][$j])."' AS fnqty_base, '".CCur($_REQUEST['ffformulaD0'][$j])."' AS ffformula,
                                '{$_SESSION['username']}' AS ftcreated_user, now() AS fdcreated_date, '{$_SESSION['username']}' AS ftmodified_user, now() AS fdmodified_date,
                                '".js_esc($ftprint_name)."','".js_esc($_REQUEST['ftdescriptionD0'][$j])."'";    

                        $m=SaveStock('-',$_REQUEST['ftitem_codeD0'][$j],'',$_REQUEST['ftwarehouse_codeD0'][$j],$_REQUEST['ftlocation_codeD0'][$j],'',CCur($_REQUEST['fnqty_baseD0'][$j]),CCur($_REQUEST['fnqty_baseD0'][$j]));
                        $strSQL=array_merge_recursive($strSQL,$m);

                    }
                }
                    
                $_REQUEST["fdtrans_date"] = $fdtrans_date_original;
            
            }
              
           
       break;
      
    }
    #pr($strSQL);die("mm");
    return $strSQL;
}

function UpdateDataLast() {
    global $objAxpDB;
    $strSQL=array();
    switch($_REQUEST[mode]) {
        case "update":
            $strSQL["Update Customer Name"][]="UPDATE txsales_invoice_hdr a, tlcustomer b SET a.ftcustomer_name=b.ftcustomer_name WHERE a.ftcustomer_code = b.ftcustomer_code AND  a.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";
            $strSQL["Update Stock Name"][]="UPDATE txsales_invoice_det a, tlstock_hdr b SET a.ftitem_name=b.ftitem_name, a.ftprint_name = CASE WHEN((a.ftprint_name IS NULL) OR (a.ftprint_name= '')) THEN b.ftprint_name ELSE a.ftprint_name END WHERE a.ftitem_code = b.ftitem_code AND  a.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";

            // update status kirim menjadi sebagian dahulu (default)
            $strSQL["Update Status SO"][]="UPDATE txsales_order_hdr a SET a.fnreceive_status=2, a.fnstatus = 2 WHERE a.ftso_no='{$_REQUEST['ftso_no']}'";

            break;        
    }

    return $strSQL;
}

function UpdateDataAfter() {
    global $objAxpDB;
    $strSQL=array();
    switch($_REQUEST[mode]) {
        case "update":
            // Simpan auto create DO
            if ($_SESSION["SIAutoDO"] ) {
                // delete item with qty 0
                $ssql="DELETE FROM txsales_invoice_det WHERE fnqty=0 AND ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}' ";
                $strSQL['Sales Invoice Index'][]= $ssql;
                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }

                // reindex item detail
                $ssql=" update txsales_invoice_det a,
                            (select c.fnid,(select ifnull(count(d.fnid),0)+1
                                            from txsales_invoice_det d
                                            where d.fnid < c.fnid and d.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}') as m
                                    from  txsales_invoice_det c
                                    where c.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}') b
                        set a.fnindex=b.m
                        where a.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}' and a.fnid=b.fnid";

                $strSQL['Sales Invoice Index'][]= $ssql;
                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }
                
                // update no DO di transaksi SI
                if ($_REQUEST['fntax_code']==0) {
                    $ftmodule_code = "TN006";
                }
                else if ($_REQUEST['fntax_code']==1) {
                    $ftmodule_code = "TN021";
                }
                else if ($_REQUEST['fntax_code']==2) {
                    $ftmodule_code = "TN022";
                }
                $ftsi_code = RetRstField("SELECT ftcode FROM tstransaction_number WHERE ftmodule_code = '{$ftmodule_code}' and ftbranch_code = '{$_SESSION['strbranch']}'");
                $ftdo_code = RetRstField("SELECT ftcode FROM tstransaction_number WHERE ftmodule_code = 'TN005' and ftbranch_code = '{$_SESSION['strbranch']}'");
                $ftdo_no = str_replace($ftsi_code,$ftdo_code,$_REQUEST['ftsales_invoice_no']); 
        
                $ssql="UPDATE txsales_invoice_hdr SET fnstatus = 1 , ftdo_no='{$ftdo_no}' WHERE ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}' ";
                $strSQL['Sales Invoice Update No DO'][]= $ssql;
                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }

                // Update Qty Accept SO
                $ssql="UPDATE txsales_order_det a INNER JOIN ( 
                            SELECT SUM(b.fnqty) AS fnqty, SUM(b.fnqty_base) AS fnqty_base, b.ftitem_code, b.fnindex_so, a.ftso_no
                            FROM txsales_invoice_hdr a 
                            INNER JOIN txsales_invoice_det b ON a.ftsales_invoice_no=b.ftsales_invoice_no  
                            WHERE a.fnstatus != 3 and a.fnstatus != 9 AND a.ftso_no = '{$_REQUEST['ftso_no']}'
                                    AND b.fnindex_so IS NOT NULL AND b.fnindex_so != ''
                            GROUP BY b.fnindex_so ) b
                        SET a.fnqty_accept = b.fnqty, a.fnqty_accept_base = b.fnqty_base  
                        WHERE a.fnindex = b.fnindex_so AND a.ftso_no = b.ftso_no; ";
                
                $strSQL["Update txsales_order_det Qty Accept"][]=$ssql;

                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }

                // set status terkirim apabila sudah memenuhi syarat
                $ssql=" UPDATE txsales_order_hdr a INNER JOIN ( 
                            SELECT a.ftso_no, COUNT(a.fnid) AS total_item, COUNT(c.fnindex) AS total_item_closed
                            FROM txsales_order_det a INNER JOIN txsales_order_hdr b ON a.ftso_no=b.ftso_no
                            LEFT JOIN txsales_order_det c ON c.ftso_no=a.ftso_no AND c.fnindex=a.fnindex AND c.fnqty_base = c.fnqty_accept_base
                            WHERE b.fnstatus != 3 AND b.fnstatus != 9  AND b.fnreceive_status != 3 AND b.ftso_no = '{$_REQUEST['ftso_no']}' 
                            GROUP BY b.ftso_no
                            HAVING total_item = total_item_closed ) b 
                        SET a.fnstatus=2 , a.fnreceive_status = 3 WHERE a.ftso_no = b.ftso_no; ";

                $strSQL["Update txsales_order_hdr Receipt1"][]=$ssql;

                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }
                
            }    
                        
            break;
        
    }

    return $strSQL;
}
 
function CancelData() {
    global $objAxpDB;  
    // $objAxpDB->Allow_Cancel(false);
    $strSQL=array();
    switch($_REQUEST[mode]) {
        case "batal":
            // Setting SI auto create DO
            if ($_SESSION["SIAutoDO"]) {
                $ftdo_no = RetRstField("SELECT ftdo_no FROM txsales_invoice_hdr WHERE ftsales_invoice_no = '{$_REQUEST['ftsales_invoice_no']}' and ftbranch_code = '{$_SESSION['strbranch']}'");
                if ($ftdo_no != "") {
                    $strSQL["Batal txdelivery_order_hdr"][]="UPDATE txdelivery_order_hdr SET fnstatus=9, ftmodified_user = '{$_SESSION['username']}', fdmodified_date = now() WHERE ftdo_no='{$ftdo_no}'";
                    $strSQL['Batal txstock_out_hdr'][]="UPDATE txstock_out_hdr SET fnstatus=9, ftmodified_user = '{$_SESSION['username']}', fdmodified_date = now() WHERE ftdocument_no='{$ftdo_no}' ";
                }

                for($j=1; $j<count($_REQUEST['ftitem_codeD0']); $j++) {
                    $strSQL['Update txsales_order_det'][]="
                                UPDATE txsales_order_det
                                SET fnqty_accept_base=IFNULL(fnqty_accept_base,0)- ".(double)CCur($_REQUEST['fnqty_baseD0'][$j])."
                                    ,fnqty_accept= IFNULL(fnqty_accept,0)- ".(double)CCur($_REQUEST['fnqtyD0'][$j])."
                                    ,ftmodified_user = '{$_SESSION['username']}', fdmodified_date = now()    
                                WHERE ftso_no='{$_REQUEST['ftso_no']}' AND ftitem_code='{$_REQUEST['ftitem_codeD0'][$j]}' AND fnindex='{$_REQUEST['fnindex_soD0'][$j]}'  ";
                    $m=SaveStock('+',$_REQUEST['ftitem_codeD0'][$j],'',$_REQUEST['ftwarehouse_codeD0'][$j],$_REQUEST['ftlocation_codeD0'][$j],'',CCur($_REQUEST['fnqty_baseD0'][$j]),CCur($_REQUEST['fnqty_baseD0'][$j]));
                    $strSQL=array_merge_recursive($strSQL,$m);
                }

            // Setting SO Manual : Batal SI -> Batal DO -> Batal SO    
            } else {
                $strSQL["Update Status Posted DO"][]="UPDATE txdelivery_order_hdr SET fnstatus=1 WHERE ftdo_no='{$ftdo_no}'";
            }
            
        break;
    }
   
    return $strSQL;
}    

function CancelDataAfter() {
    global $objAxpDB;
    $strSQL=array();
    switch($_REQUEST[mode]) {
    case "batal":
        // Setting SI auto create DO
        if ($_SESSION["SIAutoDO"]) {
            $ssql="SELECT IFNULL(SUM(fnqty_accept),0) AS fnqty_accept
                    FROM txsales_order_det WHERE ftso_no = '{$_REQUEST['ftso_no']}'
                    GROUP BY ftso_no";
            $fnqty_accept = RetRstField($ssql);

            if ($fnqty_accept == 0) {
                $strSQL['Update txsales_order_hdr'][] = "UPDATE txsales_order_hdr set fnreceive_status = 1, fnstatus = 1, ftmodified_user = '{$_SESSION['username']}', fdmodified_date = now()  WHERE ftso_no='{$_REQUEST['ftso_no']}'  ";
            } 
        }
        
        break;

    }

    return $strSQL;
}
 
function RevisiData() {
    global $objAxpDB;  
    $objAxpDB->Allow_Revisi(false);
    $strSQL=array();
    switch($_REQUEST[mode]) {
    case "revisi":
            $strSQL["Update Status Posted DO"][]="UPDATE txdelivery_order_hdr SET fnstatus=1 WHERE ftdo_no='{$_REQUEST['ftdo_no']}'";
  
         //-----------------------------------------------------
         // batal -%
         $ssql1="SELECT IFNULL(MAX(ftsales_invoice_no),'')  AS M
                      FROM txsales_invoice_hdr  
                      WHERE ftsales_invoice_no LIKE '{$_REQUEST['ftsales_invoice_no']}-%'";            
            $no=RetRstField($ssql1);            
            if($no!="") {
                $no1=explode("-",$no);
                $no=(int)$no1[1]+1;
                $str1="-{$no}";
            }
            else {
                $str1="-1";
            }
             
             $ftsales_invoice_no_new="{$_REQUEST['ftsales_invoice_no']}{$str1}";

             $strSQL["batal"][]="update txsales_invoice_hdr 
                                set fnstatus=3,ftrevisi_user='{$_SESSION['username']}',
                                     ftsales_invoice_no='{$ftsales_invoice_no_new}'
                                where ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";

             $strSQL["batal"][]="update txsales_invoice_det
                                set ftsales_invoice_no='{$ftsales_invoice_no_new}' 
                                where ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";

             $ssql="select a.ftar_no,b.ftsales_invoice_no
                    from txar_hdr a,
                            txar_det b
                    where a.ftar_no=b.ftar_no and
                             a.fnstatus<>9	and a.fnstatus2=1
                      and b.ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";

            $rs=mysql_query($ssql) or die ($ssql) ;
            while($r=mysql_fetch_assoc($rs)) {
                 $strSQL['Batal txar_det'][]="DELETE FROM txar_det 
                                            WHERE ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}' AND
                                                ftar_no='{$r['ftar_no']}'";
            }
           
         //--------------------------------------------
         
         $strSQL['Batal txar_det'][]="UPDATE txar_det
                                            SET ftsales_invoice_no='{$ftsales_invoice_no_new}'
                                            WHERE ftsales_invoice_no='{$_REQUEST['ftsales_invoice_no']}'";

        // Call Function Revisi General Ledger
        if(function_exists("RevisiGL")) {
            $m = RevisiGL($ftsales_invoice_no_new);
            if ($m != '') { $strSQL=array_merge_recursive($strSQL,$m); }
        }

        break;
    }
   
     return $strSQL;
 }    

   