<?php

/**
 * reverse the stock
 * post adjustment
 * 
 */
require_once 'ProcessBase.php';
 
class Pman_Xtuple_Fifo_ProcessFifoAdjustment extends  Pman_Xtuple_Fifo_ProcessBase

{
    static $cli_desc = "Fix Fifo Adjustment.";
   
    static $permitError = false;
    
    function getAuth()
    {
        $ff = HTML_FlexyFramework::get();
        if (!$ff->cli) {
            die("run form cli only");
        }
         
    }
    
    function get()
    {
        
        $this->transObj = DB_DataObject::Factory('invdetail');
        
        $this->transObj->query('BEGIN');
        
        PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
        
        
        echo "RUN ProcessCoheadvoids and invfifo_apply_gl_cohead_all() before running this\n"; 
        
        
        
        
        
        
        
        // check that stuff below is going to wokr.
        DB_DataObject::Factory('invadjgrp');
        DB_DataObject::Factory('invadj');
        
        $yearperiod = DB_DataObject::factory('yearperiod');
        $yearperiod->yearperiod_closed = FALSE;
        $yearperiod->orderBy('yearperiod_start ASC');
        if(!$yearperiod->find(true)){
            $this->jerr('All yearperiod have been closed?!');
        }
        
        // find the max invdetail_id
        $invdetail = DB_DataObject::factory('invdetail');
        $invdetail->query("SELECT MAX(invdetail_id) AS max FROM invdetail");
        $invdetail->fetch();
        $max = $invdetail->max;
        
        
        $period = DB_DataObject::factory('period');
        $period->whereAdd("
            period_start >= '{$yearperiod->yearperiod_start}'::date
        "); 
        $period->orderBy('period_start ASC');
        if(!$period->find(true)){
            $this->jerr("There is no any period in open yearperiod?!");
        }
        
        // temp open period..
        $tmp = DB_DataObject::factory('period');
        $tmp->query("SELECT period_temp_open('{$period->period_start}'::DATE) AS v_result");
        $tmp->fetch();
        $v_result = $tmp->v_result;
        
        //DB_DataObject::debugLevel(1);
        // fetching all problem order..
        $cohead = DB_DataObject::factory('cohead');
        $cohead->whereAdd('cohead_fifo_has_error = true');
        $cohead->whereAdd("cohead_orderdate < '2013-08-01'");
        //$cohead->whereAdd("cohead_orderdate > '2013-09-01'");
       
        $coheads = $cohead->fetchAll('cohead_id','cohead_number');
        
        echo "got coheads\n";
        //DB_DataObject::debugLevel(1);
        
        foreach ($coheads as $cohead_id => $cohead_number){
            echo "PROCESS $cohead_number \n";
            
            $invdetail = DB_DataObject::factory('invdetail');  
            $invdetail->query("
                SELECT invfifo_cohead_void_flag_order_force({$cohead_id})  
            ");
            
            $invdetail = DB_DataObject::factory('invdetail');
            $invdetail->autoJoin();
            $invdetail->autoJoinItem();
            $invdetail->selectAdd();
            $invdetail->selectAdd("
                invhist_itemsite_id,
                join_item.item_number as item_number,
                COALESCE ((SELECT
                            SUM(COALESCE(coitem_qtyreturned,0))
                        FROM
                            coitem
                        WHERE
                            coitem_cohead_id = $cohead_id
                        and
                            coitem_itemsite_id = invhist_itemsite_id
                ) ,0) AS rec_returned,
                COALESCE ((SELECT
                            SUM(COALESCE(coitem_qtyshipped, 0))
                        FROM
                            coitem
                        WHERE
                            coitem_cohead_id = $cohead_id
                        and
                            coitem_itemsite_id = invhist_itemsite_id
                ), 0) AS rec_shipped,

                COALESCE(SUM(invdetail_qty),0) as tx_total
            
            ");
            $invdetail->whereAdd("
                    invhist_ordtype = 'SO'
                AND
                    (
                        invhist_ordnumber LIKE '{$cohead->escape($cohead_number)}' || '-%'
                        OR
                        invhist_ordnumber ='{$cohead->escape($cohead_number)}'
                    )
            ");
            $invdetail->groupBy("invhist_itemsite_id, join_item.item_number");
            
            $lines = $invdetail->fetchAll();
            
            $changes = 0;
            
            foreach($lines as $l) {
                
                if(($l->rec_returned - $l->rec_shipped) == $l->tx_total){
                    continue;
                }
                echo "Doing cohead_number : $cohead_number , item_number : $l->item_number , itemsite_id : $l->invhist_itemsite_id     $l->rec_returned - $l->rec_shipped  != $l->tx_total \n";
                
                // fifo qty does not match..
//                DB_DataObject::debugLevel(1);
                $inv = DB_DataObject::factory('invdetail');
                $inv->autoJoin();
                $inv->autoJoinItem();
                
                $inv->selectAdd("
                    ROUND(COALESCE((SELECT SUM(  COALESCE(coitem_qtyreturned,0) - COALESCE(coitem_qtyshipped,0) ) from coitem
                        where coitem_cohead_id = $cohead_id
                        AND
                        coitem_linenumber =  SPLIT_PART(SUBSTRING(invhist_ordnumber, " . (strlen($cohead_number) + 2) . "), '.', 1)::INTEGER
                        AND
                        coitem_subnumber =   (0 || SPLIT_PART(SUBSTRING(invhist_ordnumber, " . (strlen($cohead_number) + 2) . "), '.', 2))::INTEGER
                            
                    ),0),0) AS coitem_shipped,
                    
                    (
                        SELECT 
                                SUM(COALESCE(invdetail_qty,0)) 
                        FROM 
                                invdetailview
                        WHERE
                                invdetailview.invhist_ordnumber = join_invhist.invhist_ordnumber
                    ) AS invhist_qty

                ");
                
                $inv->whereAdd("
                        
                        invhist_ordtype = 'SO'
                    AND
                        (
                            invhist_ordnumber LIKE '{$cohead->escape($cohead_number)}' || '-%'
                            OR
                            invhist_ordnumber ='{$cohead->escape($cohead_number)}'
                        )
                    AND
                        invhist_itemsite_id = $l->invhist_itemsite_id
                ");
                $inv->orderBy(" invhist_ordnumber ASC ");
                
                $inv->find(); 
                $reversed = array();
                while ($inv->fetch()){
                    
                    $ori = clone ($inv);
                    
                    if(
                        (
                            ($ori->invfifo_void != 0 && $ori->coitem_shipped == 0) 
                            || 
                            ($ori->invdetail_qty == $ori->coitem_shipped)
                        )
                        ||
                        (in_array($ori->invhist_ordnumber, $reversed))
                        ||
                        ($ori->coitem_shipped == $ori->invhist_qty)
                        
                    ){
                       continue;
                    }
                    
                    
                    $reversed[] = $ori->invhist_ordnumber;
                    
                    
                    $date = $ori->invhist_transdate;
                    
                    if(strtotime($ori->invhist_transdate) < strtotime($period->period_start)){
                        $date = $period->period_start;
                    }

                    $ori->reverse($this,array('_force' => true, '_return_only' => true, '_as_of' => $date));
                   
                    $changes ++;
                }
               
            }
            
            if ($changes) {
                $fifo = DB_DataObject::factory('invdetail');
                $fifo->query("SELECT invfifo_apply_gl_cohead($cohead_id)");
            }
            
        }
       
        //DB_DataObject::DebugLevel(1);
        
        $invdetail = DB_DataObject::factory('invdetail');
        $invdetail->query("SELECT invdetail_location_id FROM invdetail WHERE invdetail_id > $max LIMIT 1");
        $invdetail->fetch();
        
        $loc = $invdetail->invdetail_location_id;
        
        if(!$loc){
            $this->jerr('Nothing to do, exit directly!');
        }
        
        $invdetail = DB_DataObject::factory('invdetail');
        $invdetail->autoJoin();
        $invdetail->selectAdd();
        $invdetail->selectAdd("
            DISTINCT(join_invhist.invhist_transdate::date) AS transdate
        ");
        $invdetail->whereAdd("invdetail_id > $max");
        $dates = $invdetail->fetchAll('transdate');
        
         
        foreach($dates as $date){
            $invadjgrp = DB_DataObject::factory('invadjgrp');
            $invadjgrp->setFrom(array(
                'invadjgrp_name' => "Magento-June-fix-Adjustment for {$date}",
                'invadjgrp_transdate' => "$date",
                'invadjgrp_location_id' => $loc,
                'invadjgrp_posted' => true,
                'invadjgrp_comments' => "Reverse affect of Magento import fixes  {$date}",
                'invadjgrp_void' => false
            ));
            $invadjgrp->insert();
            echo "create invadjgrp for {$date} \n";
            if(!$invadjgrp->pid()){
                $this->jerr("insert invadjgrp for {$date} fail");
            }
            
            
//            DB_DataObject::debugLevel(1);
            $invdetail = DB_DataObject::factory('invdetail');
            $invdetail->autoJoin();
            $invdetail->selectAdd();
            $invdetail->selectAdd("
                join_invhist.invhist_itemsite_id AS itemsite_id,
                SUM(invdetail_qty) AS invdetail_qty
            ");
            $invdetail->whereAdd("
                    invdetail_id > {$max}
                AND
                    join_invhist.invhist_transdate::date = '$date'::date
            ");
            $invdetail->groupBy('itemsite_id');
                    
            if (!$invdetail->find()) {
                $this->jerr("no items found?");
            }
            
            while ($invdetail->fetch()){
                
                
                $inv = clone ($invdetail);
                
                if (!abs($inv->invdetail_qty)) {
                    continue; //$this->jerr("0 quantity?");
                }
                
                $invadj = DB_DataObject::factory('invadj');
                $invadj->setFrom(array(
                    'invadj_transdate' => "$date",
                    'invadj_location_id' => $loc,
                    'invadj_itemsite_id' => $inv->itemsite_id,
                    'invadj_qty_by' => $inv->invdetail_qty * -1,
                    'invadj_posted' => false,
                    'invadj_comments' => "Reverse affect of Magento import fixes  {$date}",
                    'invadj_voids_id' => 0,
                    'invadj_invdetail_id' => 0, //$inv->invdetail_id, when we post, it will auto set!
                    'invadj_voided_by_id' => 0,
                    'invadj_invadjgrp_id' => $invadjgrp->pid()
                ));
                //print_r($invadj);
                $invadj->insert();
                if(!$invadj->pid()){
                    $this->jerr("insert invadj fail");
                }
                
            }
            
            $invadjgrp->post($this);
        }
        
        if($v_result > 0){
            $tmp = DB_DataObject::factory('period');
            $tmp->query("SELECT period_temp_close($v_result)");
        }
        
        // check the stock level at start
        $invdetail = DB_DataObject::factory('invdetail');
        $invdetail->_join .= "
            LEFT JOIN
                    invhist
            ON
                    invdetail_invhist_id = invhist_id
            LEFT JOIN
                    invfifo
            ON
                    invfifo_invdetail_id = invdetail_id
        ";
        $invdetail->selectAdd();
        $invdetail->selectAdd("
            invhist_itemsite_id,
            SUM(invdetail_qty) AS invdetail_qty
        ");
        $invdetail->whereAdd("
                invdetail_location_id = {$loc}
            AND
                invdetail_id <= $max
            AND 
                invfifo_void = 0
        ");
        $invdetail->groupBy('invhist_itemsite_id');
        
        $start_stock = $invdetail->fetchAll('invhist_itemsite_id', 'invdetail_qty');
        
        // check the stock level at the end
        $invdetail = DB_DataObject::factory('invdetail');
        $invdetail->_join .= "
            LEFT JOIN
                    invhist
            ON
                    invdetail_invhist_id = invhist_id
            LEFT JOIN
                    invfifo
            ON
                    invfifo_invdetail_id = invdetail_id
        ";
        $invdetail->selectAdd();
        $invdetail->selectAdd("
            invhist_itemsite_id,
            SUM(invdetail_qty) AS invdetail_qty
        ");
        $invdetail->whereAdd("
                invdetail_location_id = $loc
            AND
                invfifo_void = 0
        ");
        $invdetail->groupBy('invhist_itemsite_id');
        
        $end_stock = $invdetail->fetchAll('invhist_itemsite_id', 'invdetail_qty');
       
        $diff = array();
        foreach ($end_stock as $key => $value){
            if(!isset($start_stock[$key])){
                $diff[] = "$key not set";
                continue;
            }
            
            $start = isset($error[$key]) ? $error[$key] + $start_stock[$key] : $start_stock[$key];
            
            if($start  != $value){
                $diff[] = "$key , start : $start , end : $value";
            }
        }
        
        //$this->jerr("canceled");
        
        if(count($diff)){
            $this->jerr("Stock level not correct! \n " . print_r($diff,true));
        }
        
        
        
        $this->transObj->query('COMMIT');
        $this->transObj = false;
        
        $this->jok('DONE');
         
   
    }
     
    
}