<?php
    // RE-CREATE STOCK FIFO

    session_cache_expire(1440);
    session_start();
    session_name("insys");
    include "include/config.php";
    include "include/functions.php";
    include "include/save_fifo.php";
    include "include/fifo.php";
    include "class/UctVw.php";
    
    //$DateStart = date("Y-m-d");

    //$DateStart = "2018-01-01";
    //$DateStart = "2018-07-01";
    //$DateStart = "2018-08-01";
    //$DateStart = "2018-09-01";
    //$DateStart = "2018-10-01";
    //$DateStart = "2018-11-01";
    //$DateStart = "2018-12-01";
    //$DateStart = "2019-01-01";
    //$DateStart = "2019-02-01";
    //$DateStart = "2019-03-01";
    //$DateStart = "2019-04-01";
    $DateStart = "2019-05-01";

    //$DateEnd = "2018-06-30";
    //$DateEnd = "2018-07-31";
    //$DateEnd = "2018-08-31";
    //$DateEnd = "2018-09-30";
    //$DateEnd = "2018-10-31";
    //$DateEnd = "2018-11-30";
    //$DateEnd = "2018-12-31";
    //$DateEnd = "2019-01-31";
    //$DateEnd = "2019-02-28";
    //$DateEnd = "2019-03-31";
    //$DateEnd = "2019-04-30";
    $DateEnd = "2019-05-31";

    // SQL RESET TABLE TXSTOCKFIFO (BILA DIPERLUKAN) 
    // DELETE FROM txstockfifo;
    // ALTER TABLE txstockfifo AUTO_INCREMENT = 1;
    
    // Innitial Stock
    /* RUN ONE TIME ONLY
     * $ssql="select a.ftstock_in_no,a.fdtrans_date, b.fnindex,b.ftitem_code,ifnull(b.fnqty_base,0) as fnqty_base, b.ftlocation_code,
                b.fcprice as fcprice_cogs
            FROM txinnitial_stock_hdr a inner join txinnitial_stock_det b ON a.ftstock_in_no=b.ftstock_in_no
            WHERE a.fnstatus = 1 ORDER BY b.fnindex ";

    $rs=mysql_query($ssql) or die($ssql);
    while($r=mysql_fetch_assoc($rs)) {
          SaveFIFO($_SESSION['strbranch'],"txinnitial_stock_hdr{$tablename}",$r['ftstock_in_no'], $r['fdtrans_date'], $r['fnindex'],$r['ftitem_code'], $r['fnqty_base'],  $r['fcprice_cogs'], $r['ftlocation_code'],"","","");
    }
        
    pr("*** SELESAI : INNITIAL STOCK ***");
    */
    
    pr("*** START : {$DateStart} {$DateEnd} - " . date("Y-m-d H:i:s") . " ***");
    
    // FIFO IN  
    $ssql=" SELECT fttrans_no, fdtrans_date, fnindex, ftitem_code, ROUND(fnqty_base,2) as fnqty_base, ftlocation_code,fcprice_cogs, table_name
            FROM (
                        SELECT a.ftstock_in_no AS fttrans_no, a.fdtrans_date, b.fnindex,b.ftitem_code,ROUND(IFNULL(b.fnqty_base,0),2) AS fnqty_base, b.ftlocation_code,
                            CASE WHEN (IFNULL(b.fcprice,0) = 0) THEN 0 ELSE ROUND(b.fcprice,2) END AS fcprice_cogs, 'txstock_in_det' AS table_name
                        FROM txstock_in_hdr a INNER JOIN txstock_in_det b ON a.ftstock_in_no=b.ftstock_in_no
                        WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.ftcategory_code != 5  AND a.ftcategory_code != 4 AND a.ftcategory_code != 11 

                        UNION ALL
                        SELECT a.ftstock_adjustment_no AS fttrans_no, a.fdtrans_date, b.fnindex,b.ftitem_code,ROUND(IFNULL(b.fnqty_base,0),2) AS fnqty_base, b.ftlocation_code,
                            CASE WHEN (IFNULL(b.fcprice,0) = 0) THEN 0 ELSE ROUND(b.fcprice,2) END AS fcprice_cogs, 'txstock_adjustment_det' AS table_name
                        FROM txstock_adjustment_hdr a INNER JOIN txstock_adjustment_det b ON a.ftstock_adjustment_no=b.ftstock_adjustment_no
                        WHERE a.fnstatus = 2

                        UNION ALL 
                        SELECT a.ftassembly_no AS fttrans_no, a.fdtrans_date, '1' AS fnindex,a.ftitem_code,ROUND(IFNULL(a.fnqty,0),2) AS fnqty_base, a.ftlocation_code,
                            CASE WHEN (IFNULL(a.fcnet_price,0) = 0) THEN 0 ELSE ROUND(a.fcnet_price,2) END AS fcprice_cogs, 'txassembly_hdr' AS table_name
                        FROM txassembly_hdr a 
                        WHERE a.fnstatus != 3 AND a.fnstatus != 9  

                        UNION ALL
                        SELECT a.ftdisassembly_no AS fttrans_no, a.fdtrans_date, b.fnindex,b.ftitem_code,ROUND(IFNULL(b.fnqty,0),2) AS fnqty_base, b.ftlocation_code,
                            CASE WHEN (IFNULL(b.fcnet_price,0) = 0) THEN 0 ELSE ROUND(b.fcnet_price,2) END AS fcprice_cogs, 'txdisassembly_hdr' AS table_name
                        FROM txdisassembly_hdr a INNER JOIN txdisassembly_det b ON a.ftdisassembly_no=b.ftdisassembly_no
                        WHERE a.fnstatus != 3 AND a.fnstatus != 9  

            ) X
            WHERE fdtrans_date >= '{$DateStart}' AND fdtrans_date <= '{$DateEnd}'
            ORDER BY fdtrans_date,fnIndex
            "; 

    /*
    $ssql=" SELECT fttrans_no, fdtrans_date, fnindex, ftitem_code, ROUND(fnqty_base,2) as fnqty_base, ftlocation_code,fcprice_cogs, table_name
            FROM (
                        SELECT a.ftstock_in_no AS fttrans_no, a.fdtrans_date, b.fnindex,b.ftitem_code,ROUND(IFNULL(b.fnqty_base,0),2) AS fnqty_base, b.ftlocation_code,
                            CASE WHEN (IFNULL(b.fcprice,0) = 0) THEN 0 ELSE ROUND(b.fcprice,2) END AS fcprice_cogs, 'txstock_in_det' AS table_name
                        FROM txstock_in_hdr a INNER JOIN txstock_in_det b ON a.ftstock_in_no=b.ftstock_in_no
                        WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND a.ftcategory_code = 0 
                            and a.ftstock_in_no >= 'PB190400065' AND a.ftstock_in_no <= 'PB190400073'
                       
            ) X
            -- WHERE fdtrans_date >= '2019-01-01' AND fdtrans_date <= '{$DateEnd}'
            ORDER BY fdtrans_date,fnIndex
            ";  */
            
    $counter = 0;           
    $rs=mysql_query($ssql) or die($ssql);
    while($r=mysql_fetch_assoc($rs)) {
        $counter = $counter + 1;    
        SaveFIFO($_SESSION['strbranch'],$r['table_name'],$r['fttrans_no'], $r['fdtrans_date'], $r['fnindex'],$r['ftitem_code'], $r['fnqty_base'], $r['fcprice_cogs'], $r['ftlocation_code'],"","","");
    }
        
    pr("*** SELESAI : FIFO IN {$DateEnd} ({$counter} rows) ***");
    
    
    // SALES RETUR NON SO
    $ssql=" SELECT a.ftsales_return_no AS fttrans_no,a.fdtrans_date, b.fnindex,b.ftitem_code,ROUND(IFNULL(b.fnqty_base,0),2) AS fnqty_base, b.ftlocation_code,
               a.ftso_no, 'txsales_return_det' AS table_name
            FROM  txsales_return_hdr a
            INNER JOIN txsales_return_det b ON a.ftsales_return_no=b.ftsales_return_no 
            WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND b.fnqty_base > 0 AND ftso_no = '' 
                    AND a.fdtrans_date >= '{$DateStart}' AND a.fdtrans_date <= '{$DateEnd}'
            GROUP BY a.ftsales_return_no, b.ftitem_code, b.fnindex
            ORDER BY a.ftsales_return_no, b.fnindex
            ";

    $counter = 0;                     
    $rs=mysql_query($ssql) or die($ssql);
    while($r=mysql_fetch_assoc($rs)) {
        $counter = $counter + 1;    
        $fcprice_cogs = GetLastPriceValue($_SESSION['strbranch'], $r['ftitem_code']);
        SaveFIFO($_SESSION['strbranch'],$r['table_name'],$r['fttrans_no'], $r['fdtrans_date'], $r['fnindex'],$r['ftitem_code'], $r['fnqty_base'], $fcprice_cogs, $r['ftlocation_code'],"","","");
    }
        
    pr("*** SELESAI : SALES RETUR NO SO {$DateEnd} ({$counter} rows) ***");
   
    // STOCK MUTASI
    $ssql=" SELECT 'txstock_mutation_det' AS table_name , a.ftstock_mutation_no AS fttrans_no,a.fdtrans_date,
                    b.fnindex, b.ftitem_code,b.ftwarehouse_from AS ftwarehouse_code_from,b.ftlocation_from AS ftlocation_code_from,
                    b.ftwarehouse_to AS ftwarehouse_code_to,b.ftlocation_to AS ftlocation_code_to,
                    0 AS fnmasuk,ROUND(b.fnqty_base,2) * -1 AS fnkeluar,0 AS fcprice
            FROM txstock_mutation_hdr a INNER JOIN txstock_mutation_det b ON  a.ftstock_mutation_no=b.ftstock_mutation_no  
            WHERE a.fnstatus != 3 AND a.fnstatus != 9 
                AND a.fdtrans_date >= '{$DateStart}' AND a.fdtrans_date <= '{$DateEnd}'
            ORDER BY a.fdtrans_date, a.ftstock_mutation_no, b.fnindex  
            ";

    $counter = 0;        
    $rs=mysql_query($ssql) or die($ssql);
    while($r=mysql_fetch_assoc($rs)) {
        $counter = $counter + 1;
        SaveFIFO($_SESSION['strbranch'],$r['table_name'],$r['fttrans_no'], $r['fdtrans_date'], $r['fnindex'],$r['ftitem_code'], $r['fnkeluar'],  0, $r['ftlocation_code_from'], $r['ftlocation_code_to'],"","");
    }
        
    pr("*** SELESAI : STOCK MUTASI {$DateEnd} ({$counter} rows) ***");
    
    /*
    // STOCK ASSEMBLY YG TDK ADA DI FIFO
    $ssql=" SELECT 'txassembly_hdr' AS table_name , a.ftassembly_no AS fttrans_no,a.fdtrans_date,
                    b.fnindex, b.ftitem_code,b.ftwarehouse_code AS ftwarehouse_code,b.ftlocation_code AS ftlocation_code,
                    0 AS fnmasuk,ROUND(b.fnqty,2) * -1 AS fnkeluar,0 AS fcprice
            FROM txassembly_hdr a INNER JOIN txassembly_det b ON  a.ftassembly_no=b.ftassembly_no  
            LEFT JOIN txstockfifo c ON c.ftdocument_no = a.ftassembly_no AND b.ftitem_code = c.ftitem_code	
                    AND c.fnstatus = 1 AND c.fttableref = 'txassembly_hdr'  
            WHERE a.fnstatus != 3 AND a.fnstatus!= 9 AND c.ftitem_code IS NULL	
            ORDER BY a.fdtrans_date, b.fnindex    
            ";

    $counter = 0;        
    $rs=mysql_query($ssql) or die($ssql);
    while($r=mysql_fetch_assoc($rs)) {
        $counter = $counter + 1;
        SaveFIFO($_SESSION['strbranch'],$r['table_name'],$r['fttrans_no'], $r['fdtrans_date'], $r['fnindex'],$r['ftitem_code'], $r['fnkeluar'],  0, $r['ftlocation_code'], "","","");
    }
        
    pr("*** SELESAI : STOCK ASSEMBLY TIDAK ADA DI FIFO {$DateEnd} ({$counter} rows) ***");
    */
    
    // FIFO OUT  
    $ssql=" SELECT fttrans_no, fdtrans_date, fnindex, ftitem_code, ROUND(fnqty_base,2) as fnqty_base, ftlocation_code,fcprice_cogs, table_name
            FROM (
                    SELECT a.ftstock_out_no AS fttrans_no, a.fdtrans_date, b.fnindex,b.ftitem_code,ROUND(IFNULL(b.fnqty_base,0),2) * -1 AS fnqty_base, b.ftlocation_code,
                        CASE WHEN (IFNULL(b.fcprice,0) = 0) THEN 0 ELSE ROUND(b.fcprice,2) END AS fcprice_cogs, 
                        CASE WHEN (LEFT(a.ftstock_out_no,2)='AS') THEN 'txassembly_hdr' 
                             WHEN (LEFT(a.ftstock_out_no,2)='DS') THEN 'txdisassembly_hdr'  
                        ELSE 'txstock_out_det' END AS table_name
                    FROM txstock_out_hdr a INNER JOIN txstock_out_det b ON a.ftstock_out_no=b.ftstock_out_no
                    WHERE a.fnstatus != 3 AND a.fnstatus != 9  
                              
            ) X
            WHERE fdtrans_date >= '{$DateStart}' AND fdtrans_date <= '{$DateEnd}'
            ORDER BY fdtrans_date,fnIndex
            ";

    $counter = 0;                
    $rs=mysql_query($ssql) or die($ssql);
    while($r=mysql_fetch_assoc($rs)) {
        $counter = $counter + 1;    
        SaveFIFO($_SESSION['strbranch'],$r['table_name'],$r['fttrans_no'], $r['fdtrans_date'], $r['fnindex'],$r['ftitem_code'], $r['fnqty_base'], $r['fcprice_cogs'], $r['ftlocation_code'],"","","");
    }
        
    pr("*** SELESAI : FIFO OUT {$DateEnd} ({$counter} rows) ***");

    // SALES RETUR
    $ssql=" SELECT a.ftsales_return_no,a.fdtrans_date, b.fnindex,b.ftitem_code,ROUND(IFNULL(b.fnqty_base,0),2) AS fnqty_base, b.ftlocation_code,
                0 AS fcprice_cogs, d.ftstock_out_no, 'txsales_return_det' AS table_name
            FROM  txsales_return_hdr a
            INNER JOIN txsales_return_det b ON a.ftsales_return_no=b.ftsales_return_no 
            LEFT JOIN txdelivery_order_hdr c ON c.ftso_no = a.ftso_no
            LEFT JOIN txstock_out_hdr d ON d.ftdocument_no = c.ftdo_no
            WHERE a.fnstatus != 3 AND a.fnstatus != 9  AND fnqty_base > 0 AND a.ftso_no != '' 
                    AND a.fdtrans_date >= '{$DateStart}' AND a.fdtrans_date <= '{$DateEnd}'
                    -- AND (a.ftsales_return_no = 'SR190200045' OR a.ftsales_return_no = 'SR190400034')   
            ORDER BY a.fdtrans_date, b.fnIndex
            ";

    $counter = 0;  
    $rs=mysql_query($ssql) or die($ssql);
    while($r=mysql_fetch_assoc($rs)) {
        $counter = $counter + 1; 
        ReturFIFO($_SESSION['strbranch'], $r['table_name'], $r['ftsales_return_no'], $r['ftstock_out_no'] ,$r['fdtrans_date'], $r['fnindex'], $r['ftitem_code'], $r['fnqty_base'], $r['ftlocation_code'],"");
    }
        
    pr("*** SELESAI : SALES RETUR WITH SO {$DateEnd} ({$counter} rows) ***");
    
    /*
    // FIFO ADJ
    $ssql=" SELECT ftbranch_code,ftitem_code,ftwarehouse_code,fnidfifo,fttableref,ftdocument_no,fnindex FROM txstockfifo WHERE fnidfiforef = 0 and fnstatus = 1   
            ORDER BY fnid ";

    $counter = 0;  
    $strWarehouseTo = "";
    
    $rs=mysql_query($ssql) or die($ssql);
    while($r=mysql_fetch_assoc($rs)) {
        $counter = $counter + 1; 
        
        // khusus stock mutasi, ambil data lokasi tujuan
        if ($r['fttableref'] == "txstock_mutation_det") {
            $ssql2 = "SELECT b.ftlocation_to FROM txstock_mutation_hdr a INNER JOIN txstock_mutation_det b ON a.ftstock_mutation_no = b.ftstock_mutation_no 
                        WHERE a.fnstatus = 1 AND a.ftstock_mutation_no = '{$r['ftdocument_no']}' AND ftitem_code = '{$r['ftitem_code']}' AND ftlocation_from = '{$r['ftwarehouse_code']}' AND fnindex = '{$r['fnindex']}' ";
            $strWarehouseTo = RetRstField($ssql2);
        }
        
        FIFOAdj($r['ftbranch_code'], $strIndex, $r['ftitem_code'], $r['ftwarehouse_code'],$strWarehouseTo,"","",$r['fnidfifo']);   
        
    }
        
    pr("*** SELESAI : FIFO ADJ ({$counter} rows) ***");
    */
    
    pr("*** SELESAI : {$DateStart} {$DateEnd} - " . date("Y-m-d H:i:s") . " ***");
    
?>