<?php

session_cache_expire(1440);
session_start();
session_name("insys");
include "include/config.php";
include "include/functions.php";
include "include/fifo.php";
include "class/UctVw.php";


   function  SaveStock_bahan_bakux($strType='+',$strItemCode='',$strBatchCode,$strWarehouseCode,$strLocationCode,$intQtyOnHand,$intQtyAvail) {

        if($strBatchCode) {
            $strWhereBatch="AND ftbatch_code = '{$strBatchCode}'";
            $strInsertBatchField=",ftbatch_code";
            $strInsertBatchValue=",{$strBatchCode}";
        }
        $strWhere="WHERE  ftitem_code = '{$strItemCode}' {$strWhereBatch}  AND ftwarehouse_code = '{$strWarehouseCode}' AND ftlocation_code = '{$strLocationCode}'";
        $rs=mysql_query("SELECT fnid FROM tlstock_det_bahan_bakux {$strWhere} ")    ;

        if(mysql_num_rows($rs)) {
            $ssql="UPDATE tlstock_det_bahan_bakux
                                        SET
                                            fnqty_on_hand = fnqty_on_hand {$strType} {$intQtyOnHand},
                                            fnqty_available = fnqty_available {$strType} {$intQtyAvail},
                                            ftmodified_user ='{$_SESSION[username]}'
                                        WHERE ftbranch_code = '{$_SESSION[strbranch]}' AND
                                              ftitem_code = '{$strItemCode}' AND
                                              ftwarehouse_code = '{$strWarehouseCode}' AND
                                              ftlocation_code = '{$strLocationCode}' {$strWhereBatch}";

              if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
               }
              else {
                   $strSQL['stock '.$strType][]=$ssql;
              }
        }
        else {
            $ssql="INSERT INTO tlstock_det_bahan_bakux
                                  (ftbranch_code, ftitem_code {$strInsertBatchField},
                                  ftwarehouse_code, ftlocation_code, fnqty_on_hand,
                                  fnqty_available, ftcreated_user, ftmodified_user)
                                  VALUES
                                  ('{$_SESSION[strbranch]}','{$strItemCode}' {$strInsertBatchValue},
                                    '{$strWarehouseCode}', '{$strLocationCode}',0,
                                    0,'{$_SESSION[username]}', '{$_SESSION[username]}')";

             mysql_query($ssql);

              $ssql="UPDATE tlstock_det_bahan_bakux
                                        SET
                                            fnqty_on_hand = fnqty_on_hand {$strType} {$intQtyOnHand},
                                            fnqty_available = fnqty_available {$strType} {$intQtyAvail},
                                            ftmodified_user ='{$_SESSION[username]}'
                                        WHERE ftbranch_code = '{$_SESSION[strbranch]}' AND
                                              ftitem_code = '{$strItemCode}' AND
                                              ftwarehouse_code = '{$strWarehouseCode}' AND
                                              ftlocation_code = '{$strLocationCode}' {$strWhereBatch}";

                if($_REQUEST['DIRECT_EXECUTE_SQL']==1) {
                    mysql_query($ssql) or die("SQL ERROR :{$ssql}");
                }
                else {
                     $strSQL['stock '.$strType][]=$ssql;
                }
        }
      # pr($ssql);die("mmm");

        return $strSQL;
    }

    
$ssql="
        SELECT x.*
        FROM
        (
        select 'INITIAL STOCK' as fttype, 'txinnitial_stock_det_bahan_baku' as fttable , a.ftstock_in_no as fttrans_no,a.fdtrans_date, b.fnindex, b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,(fnqty) as fnmasuk,0 as fnkeluar,0 as fcprice
        from txinnitial_stock_hdr_bahan_baku a,txinnitial_stock_det_bahan_baku b
        where a.ftstock_in_no=b.ftstock_in_no
                and a.fnstatus=1
        union all
        /*stock masuk */
        select
        CASE 	WHEN ftcategory_code='0' THEN 'STOCK MASUK PEMBELIAN'
                WHEN ftcategory_code='1' THEN 'STOCK MASUK SERVICE'
                WHEN ftcategory_code='2' THEN 'STOCK MASUK RETURN'
                WHEN ftcategory_code='3' THEN 'STOCK MASUK SURAT JALAN PINJAMAN KEMBALI'
                WHEN ftcategory_code='4' THEN 'STOCK MASUK SURAT JALAN SAMPLE'
                END AS  fttype,
        CASE 	WHEN ftcategory_code='0' THEN 'txstock_in_det_bahan_baku'
                WHEN ftcategory_code='1' THEN 'txstock_in_return_det_bahan_baku'
                WHEN ftcategory_code='2' THEN 'txstock_in_return_det_bahan_baku'
                WHEN ftcategory_code='3' THEN 'txdelivery_order_det_pinjaman'
                WHEN ftcategory_code='4' THEN 'txdelivery_order_det_sample'
                END AS  fttable,
        CASE WHEN ifnull(a.ftdocument_no,'')<>'' THEN ifnull(a.ftdocument_no,'') ELSE a.ftstock_in_no  END  as fttrans_no,
        a.fdtrans_date, b.fnindex, b.ftitem_code,
        b.ftwarehouse_code,b.ftlocation_code,(b.fnqty) as fnmasuk,0 as fnkeluar,e.fcprice*d.fcrate as fcprice
        from txstock_in_hdr_bahan_baku a
                inner join txstock_in_det_bahan_baku b ON a.ftstock_in_no=b.ftstock_in_no
                inner join tlstock_hdr_bahan_baku c ON b.ftitem_code=c.ftitem_code
                LEFT JOIN txpurchase_order_hdr d ON a.ftdocument_no=d.ftpo_no
                LEFT JOIN txpurchase_order_det e ON d.ftpo_no =e.ftpo_no AND e.fnindex=b.fnindex_po
        where
                 a.fnstatus=1  and
                (
                        (a.fnstatus<>9 and a.ftcategory_code<>0)
                        or
                        (a.fnstatus<>9 and a.fnpo_type3<>1 and a.ftcategory_code=0)
                )

        /*stock keluar */
        union all

        select

        CASE 	WHEN ftcategory_code='0' THEN 'SURAT JALAN RETURN'
                WHEN ftcategory_code='1' THEN 'SURAT JALAN SUBCON'
                WHEN ftcategory_code='2' THEN 'SURAT JALAN SERVICE'
                WHEN ftcategory_code='3' THEN 'SURAT JALAN FREE'
                WHEN ftcategory_code='4' THEN 'SURAT JALAN KOMPONEN PON POTONG'
                WHEN ftcategory_code='5' THEN ''
                WHEN ftcategory_code='6' THEN 'SURAT JALAN SAMPLE'
                WHEN ftcategory_code='7' THEN 'SURAT JALAN MESIN KE PEMBORONG'
                WHEN ftcategory_code='8' THEN 'SURAT JALAN JUAL SAMPAH DAN BAHAN'
                WHEN ftcategory_code='9' THEN 'SURAT JALAN PINJAMAN'
                WHEN ftcategory_code='10' THEN 'SURAT JALAN ALAT-ALAT MAINTENANCE'
                WHEN ftcategory_code='11' THEN 'SURAT JALAN INTERNAL'


                END AS  fttype,
        CASE 	WHEN ftcategory_code='0' THEN 'txdelivery_order_return_det_bahan_baku'
                WHEN ftcategory_code='1' THEN 'txdelivery_order_det_bahan_baku'
                WHEN ftcategory_code='2' THEN 'txdelivery_order_return_det_bahan_baku'
                WHEN ftcategory_code='3' THEN 'txdelivery_order_det_free'
                WHEN ftcategory_code='4' THEN ''
                WHEN ftcategory_code='5' THEN ''
                WHEN ftcategory_code='6' THEN 'txdelivery_order_det_sample'
                WHEN ftcategory_code='7' THEN ''
                WHEN ftcategory_code='8' THEN 'txdelivery_order_det_jual'
                WHEN ftcategory_code='9' THEN 'txdelivery_order_det_pinjaman'
                WHEN ftcategory_code='10' THEN 'txdelivery_order_det_alat'
                WHEN ftcategory_code='11' THEN 'txdelivery_order_det_internal'
                END AS  fttable,

        CASE WHEN ifnull(a.ftdocument_no,'')<>'' THEN ifnull(a.ftdocument_no,'') ELSE a.ftstock_out_no  END  as fttrans_no,a.fdtrans_date, b.fnindex,b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,0 as fnmasuk,(fnqty)  as fnkeluar
        ,0 as fcprice
        from txstock_out_hdr_bahan_baku a,txstock_out_det_bahan_baku b,tlstock_hdr_bahan_baku c
        where a.ftstock_out_no=b.ftstock_out_no
                and a.fnstatus=1 and
                b.ftitem_code=c.ftitem_code
        /*mutasi */
        /* keluar */
        union all
        select  'MUTASI' as fttype, 'txstock_mutation_det_bahan_baku' as fttable , a.ftstock_mutation_no as fttrans_no,a.fdtrans_date, b.fnindex, b.ftitem_code,b.ftwarehouse_from as ftwarehouse_code,b.ftlocation_from as ftlocation_code,0 as fnmasuk,(fnqty) as fnkeluar,0 as fcprice
        from txstock_mutation_hdr_bahan_baku a,txstock_mutation_det_bahan_baku b,tlstock_hdr_bahan_baku c
        where a.ftstock_mutation_no=b.ftstock_mutation_no
                and a.fnstatus=1 and
                b.ftitem_code=c.ftitem_code
        /*masuk */
        union all
        select 'MUTASI' as fttype, 'txstock_mutation_det_bahan_baku' as fttable , a.ftstock_mutation_no as fttrans_no,a.fdtrans_date, b.fnindex, b.ftitem_code,b.ftwarehouse_to as ftwarehouse_code,b.ftlocation_to as ftlocation_code,(fnqty)  as fnmasuk,0 as fnkeluar,0 as fcprice
        from txstock_mutation_hdr_bahan_baku a,txstock_mutation_det_bahan_baku b,tlstock_hdr_bahan_baku c
        where a.ftstock_mutation_no=b.ftstock_mutation_no
                and a.fnstatus=1  and
                b.ftitem_code=c.ftitem_code
        /*penyesuaian*/
        union all
        select 'PENYESUAIAN' as fttype, 'txstock_adjustment_det_bahan_baku' as fttable ,a.ftstock_adjustment_no as fttrans_no,a.fdtrans_date, b.fnindex, b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,
                case when (fnqty_opname)>(fnqty_available) then (fnqty_opname)-(fnqty_available) else 0 end as fnmasuk,
                case when (fnqty_opname)<(fnqty_available) then (fnqty_available)-(fnqty_opname) else 0 end as fnkeluar,0 as fcprice
        from txstock_adjustment_hdr_bahan_baku a,txstock_adjustment_det_bahan_baku b,tlstock_hdr_bahan_baku c
        where a.ftstock_adjustment_no=b.ftstock_adjustment_no  and  a.fnstatus=2  and
                b.ftitem_code=c.ftitem_code
        ) x
        #where x.ftitem_code='BSCABSGILDMXX.D013'
        ORDER BY x.fdtrans_date,x.fttrans_no,x.fnindex
        LIMIT 0,5 # 0,5000 # 5001, 5000  # 10001, 5000 #  15001, 5000
        ";

        $_REQUEST['DIRECT_EXECUTE_SQL']=1;
        mysql_query("BEGIN");

        $strSQL_fifo=array();
         $strSQL=array();
       
        $rs=mysql_query($ssql);
        while($r=mysql_fetch_assoc($rs)) {
           $i++; 
            if($r['fttype']=="STOCK MASUK PEMBELIAN") {
                $ssql_1="SELECT fchpp
                        FROM tlstock_hdr_bahan_baku
                        WHERE ftitem_code='{$_REQUEST['ftitem_codeD0'][$j]}'";
                $fchpp=(double)RetRstField($ssql_1);
                  $ssql_1="SELECT SUM(fnqty_on_hand) AS fnqty_on_hand 
                        FROM tlstock_det_bahan_bakux
                        WHERE ftitem_code='{$_REQUEST['ftitem_codeD0'][$j]}'";
                $fnqty_on_hand=(double)RetRstField($ssql_1);
               
                $fcprice=$r['fcprice']; 
                $fnqty=$r['fnmasuk'];
                 if($fnqty_on_hand<=0) {
                    $fchpp=0;
                }
                
               $result_fchpp=(($fchpp*$fnqty_on_hand)+($fcprice*$fnqty))/($fnqty_on_hand+$fnqty);
               
                $ssql="INSERT INTO TXCOGS_Hist
                      (ftItem_Code, fdTrans_Date, ftTrans_No,
                            fcStarting_Price, fnStarting_Qty, fcPurchase_Price,
                            fnPurchase_Qty, fcLast_Price, fnStatus,
                            ftCreated_User, fdCreated_Date, ftModified_User,
                            fdModified_Date)
                     SELECT '{$r['ftitem_code']}' AS ftItem_Code, '{$r['fdtrans_date']}' AS fdTrans_Date,   '{$r['fttrans_no']}'  AS ftTrans_No,
                             '{$fchpp}'  AS fcStarting_Price,  '{$fnqty_on_hand}'  AS fnStarting_Qty, '{$fcprice}'   AS fcPurchase_Price,
                             '{$fnqty}'  AS fnPurchase_Qty,  '{$result_fchpp}'  AS fcLast_Price,  '1'  AS fnStatus,
                             '{$_SESSION['username']}'  AS ftCreated_User,  now()  AS fdCreated_Date,  '{$_SESSION['username']}' AS ftModified_User,
                             now()  AS fdModified_Date";
                mysql_query($ssql) or die($ssql);
                
            }
            if($r['fnmasuk']>0) {
                SaveStock_bahan_bakux("+",$r['ftitem_code'],$r['ftwarehouse_code'],$r['ftlocation_code'],(double)$r['fnmasuk'],(double)$r['fnmasuk']);
            }
            else {
                 SaveStock_bahan_bakux("-",$r['ftitem_code'],$r['ftwarehouse_code'],$r['ftlocation_code'],(double)$r['fnkeluar'],(double)$r['fnkeluar']);

            }
            
        }
       # Query_SqlArr($strSQL);
       
        $_REQUEST['DIRECT_EXECUTE_SQL']=0;
        mysql_query("COMMIT");