
<?
require_once ('DBTable.php');
require_once ('Util.php');
require_once ('DBFactory.php');
//require_once ('Stock.php');
require_once ('PackageType.php');


final class StockCardRecord extends DBTable
{

	public $__TABLE = 'StockCardRecord';
	public static $__COLUMNS = array(
            'id'                    => 0,
            'item_id' 		        => '',
            'item_dvsn_id' 		    => 0,
            'customer_id' 		    => '',
            'customer_dvsn_id' 		=> 0,
            'sum_date' 		        => '',
            'package_id'            => '',
            'current_stock_pcs'	    => 0,
            'stockcard_pack'        => 0,
            'stockcard_pcs'         => 0,
            'inbound_pack'			=> 0,
            'inbound_pcs'			=> 0,
            'outbound_pack'		    => 0,
            'outbound_pcs'		    => 0,
            'balance_pack' 	        => 0,
            'balance_pcs' 	        => 0,
            'month_mm'			    => '',
            'year_20yy'             => '',
            'create_dt' 		    => 0,
            'create_user_id'        => 0,
            'update_dt' 		    => 0,
            'update_user_id'        => 0,
					);


	/**
	 * Update stock card ib
	 *
	 * @return true if update successfully, otherwise return false
	 */
	public function addStockCard_ib($data)
	{   
        $cal_date = substr($data['create_dt'],0,10);
        $todate = date('Y-m-d');

            $result = $this->cal_stock_card_ib($cal_date,$data);
           
            //if( $cal_date!=$todate ){

                $current_stock_pcs = 0;
                $stockcard_pack = 0;
                $stockcard_pcs = 0;
                
                $last_stockcard = (new DBFactory())->query("SELECT top 1 balance_pack AS stockcard_pack , balance_pcs AS stockcard_pcs FROM StockCardRecord WHERE item_id ='".$data['item_id']."' AND customer_id='".$data['customer_id']."' AND sum_date < '".$cal_date."' order by  sum_date DESC, create_dt DESC");    
                if( isset($last_stockcard[0]['stockcard_pcs']) ){
                    $current_stock_pcs = $last_stockcard[0]['stockcard_pcs'];
                    $stockcard_pack = $last_stockcard[0]['stockcard_pack'];
                    $stockcard_pcs = $last_stockcard[0]['stockcard_pcs'];
                }

                $list_transaction = (new DBFactory())->query("SELECT * FROM StockCardRecord WHERE item_id='". $data['item_id'] ."' AND customer_id='". $data['customer_id'] ."' AND SUM_date BETWEEN '".$cal_date."' AND '".$todate."' order by  sum_date, create_dt ASC");
                foreach( $list_transaction as $row ){
                    $sql =   " UPDATE " . $this->__TABLE .
                        " SET current_stock_pcs = ".$current_stock_pcs." , " .
                        "     stockcard_pack = ".$stockcard_pack." , " .
                        "     stockcard_pcs = ".$stockcard_pcs." , " .
                        "     balance_pack = (".$stockcard_pack." + inbound_pack) - outbound_pack , " .
                        "     balance_pcs = (".$stockcard_pcs." + inbound_pcs) - outbound_pcs, " .
                        "     update_dt = '". date('Y-m-d H:i:s') ."' ".
                        " WHERE item_id='" . $row['item_id'] . "'" .
                        "   AND item_dvsn_id=" . $row['item_dvsn_id'] . 
                        "   AND customer_id='" . $row['customer_id'] . "'" .
                        "   AND customer_dvsn_id=" . $row['customer_dvsn_id'] .
                        "   AND sum_date='". $row['sum_date'] ."'";

                    $this::$logger->debug("SQL: " . $sql);
                    $stmt = sqlsrv_query( $this::$db->getInstance(), $sql );
                    $rows_affected = sqlsrv_rows_affected( $stmt);
                    
                    if( $rows_affected === false) {
                        $this::$logger->error("Caught exception: " . print_r(sqlsrv_errors(), true));
                    } elseif( $rows_affected == -1 ) {
                        $rows_affected = 0;
                    } else {
                        $this::$logger->debug($rows_affected." rows were updated.");
                    }
                    $stockcard_pack = ($stockcard_pack + $row['inbound_pack']) - abs($row['outbound_pack']);
                    $stockcard_pcs = ($stockcard_pcs + $row['inbound_pcs']) - abs($row['outbound_pcs']);
                    $current_stock_pcs = $stockcard_pcs;
                }
            //}

            return $result;
    } 

   	/**
	 * Update stock card ib
	 *
	 * @return true if update successfully, otherwise return false
	 */
	public function cal_stock_card_ib($cal_date,$data)
	{  
           $sql =   " UPDATE " . $this->__TABLE .
                    " SET inbound_pack = inbound_pack + ".$data['package_qty'].", " .
                    "     inbound_pcs = inbound_pcs + ".$data['qty'].", " .
                    "     balance_pack = balance_pack + ".$data['package_qty'].", " .
                    "     balance_pcs = balance_pcs + ".$data['qty'].", " .
                    "     update_dt = '". date('Y-m-d H:i:s') . "'," .
                    "     update_user_id = 'system'" .
                    " WHERE item_id='" . $data['item_id'] . "'" .
                    "   AND item_dvsn_id=" . $data['item_dvsn_id'] . 
                    "   AND customer_id='" . $data['customer_id'] . "'" .
                    "   AND customer_dvsn_id=" . $data['customer_dvsn_id'] .
                    "   AND sum_date='". $cal_date ."'";

            $this::$logger->debug("SQL: " . $sql);
            $stmt = sqlsrv_query( $this::$db->getInstance(), $sql );
            $rows_affected = sqlsrv_rows_affected( $stmt);

            if( $rows_affected === false) {
                $this::$logger->error("Caught exception: " . print_r(sqlsrv_errors(), true));
            } elseif( $rows_affected == -1 ) {
                $rows_affected = 0;
            } else {
                $this::$logger->debug($rows_affected." rows were updated.");
            }

            if (!$rows_affected) {
                $StockCardRecord_cols = $this::$__COLUMNS;

                // remove unwant data
                $unsets = [ 'id', 'update_dt', 'update_user_id'];
                foreach ($unsets as $u) unset($StockCardRecord_cols[$u]);               

                $StockCardRecord_cols = Util::parseArrayData($StockCardRecord_cols, $data);

                list( $y, $m, $d) = explode("-",$cal_date);
                
                $clean_m = strlen($m)==1?"0".$m:$m;
                
                $packageTypes = (new PackageType())->get(PackageType::$__COLUMNS, "item_id='".$data['item_id']."' AND item_dvsn_id='".$data['item_dvsn_id']."'");

                // $stockcard_pack = 0;
                // $stockcard_pcs = 0;packageTypes
                // if ( $type_cal=='realtime' ) {
                //     $last_stock = (new Stock())->get(Stock::$__COLUMNS, "item_id='".$data['item_id']."' AND item_dvsn_id='".$data['item_dvsn_id']."' AND customer_id='".$data['customer_id']."' AND customer_dvsn_id='".$data['customer_dvsn_id']."'");
                //     if( isset($last_stock[0]['qty']) ){
                //         $stockcard_pack = $last_stock[0]['qty']/$packageTypes[0]['qty'];
                //         $stockcard_pcs = $last_stock[0]['qty'];
                //     }
                // }
                
                // if ($type_cal=='recalulate' ) {
                //     $last_stockcard = (new DBFactory())->query("SELECT top 1 balance_pack AS stockcard_pack , balance_pcs AS stockcard_pcs FROM StockCardRecord WHERE item_id ='".$data['item_id']."' AND customer_id='".$data['customer_id']."' AND sum_date < '".$cal_date."' order by  sum_date, create_dt DESC");    
                //     if( isset($last_stockcard[0]['stockcard_pcs']) ){
                //         $stockcard_pack = $$last_stockcard[0]['stockcard_pack'];
                //         $stockcard_pcs = $last_stockcard[0]['stockcard_pcs'];
                //     }
                // }

                
                //$StockCardRecord_cols['item_id']            = $data['item_id'];
                //$StockCardRecord_cols['item_dvsn_id']       = $data['item_dvsn_id'];
               // $StockCardRecord_cols['customer_id']        = $data['customer_id'];
                //$StockCardRecord_cols['customer_dvsn_id']   = $data['customer_dvsn_id'];
                $StockCardRecord_cols['sum_date']           = $cal_date;
                $StockCardRecord_cols['package_id']         = isset($packageTypes[0]['package_id'])?$packageTypes[0]['package_id']:"box";
                //$StockCardRecord_cols['current_stock_pcs']  = $stockcard_pcs;   
                //$StockCardRecord_cols['stockcard_pack']     = $stockcard_pack;
               // $StockCardRecord_cols['stockcard_pcs']      = $stockcard_pcs; 
                $StockCardRecord_cols['inbound_pack']       = $data['package_qty'];
                $StockCardRecord_cols['inbound_pcs']        = $data['qty'];
                //$StockCardRecord_cols['balance_pack']       = $stockcard_pack + $data['package_qty'];
                //$StockCardRecord_cols['balance_pcs']        = $stockcard_pcs + $data['qty'];
                $StockCardRecord_cols['month_mm']           = $clean_m;
                $StockCardRecord_cols['year_20yy']          = $y;
                $StockCardRecord_cols['create_dt']          = date('Y-m-d H:i:s');
                //$StockCardRecord_cols['create_user_id']     = $data['create_user_id'];           

                $this::$logger->debug("array : " . json_encode($StockCardRecord_cols));
                $rows_affected = $this->add($StockCardRecord_cols) ? 1 : 0;
            }    
            return $rows_affected;
    }    


    	/**
	 * Update stock card ib
	 *
	 * @return true if update successfully, otherwise return false
	 */
	public function addStockCard_ob($data)
	{   
        $cal_date = substr($data['create_dt'],0,10);
        $todate = date('Y-m-d');

            $result = $this->cal_stock_card_ob($cal_date,$data);

                $current_stock_pcs = 0;
                $stockcard_pack = 0;
                $stockcard_pcs = 0;
                
                $last_stockcard = (new DBFactory())->query("SELECT top 1 balance_pack AS stockcard_pack , balance_pcs AS stockcard_pcs FROM StockCardRecord WHERE item_id ='".$data['item_id']."' AND customer_id='".$data['customer_id']."' AND sum_date < '".$cal_date."'  order by  sum_date DESC, create_dt DESC");    
                if( isset($last_stockcard[0]['stockcard_pcs']) ){
                    $current_stock_pcs = $last_stockcard[0]['stockcard_pcs'];
                    $stockcard_pack = $last_stockcard[0]['stockcard_pack'];
                    $stockcard_pcs = $last_stockcard[0]['stockcard_pcs'];
                }

                $list_transaction = (new DBFactory())->query("SELECT * FROM StockCardRecord WHERE item_id='". $data['item_id'] ."' AND customer_id='". $data['customer_id'] ."' AND SUM_date BETWEEN '".$cal_date."' AND '".$todate."' order by  sum_date, create_dt ASC");             
                foreach( $list_transaction as $row ){
                    $sql =   " UPDATE " . $this->__TABLE .
                        " SET current_stock_pcs = ".$current_stock_pcs." , " .
                        "     stockcard_pack = ".$stockcard_pack." , " .
                        "     stockcard_pcs = ".$stockcard_pcs." , " .
                        "     balance_pack = (".$stockcard_pack." + inbound_pack) - outbound_pack, " .
                        "     balance_pcs = (".$stockcard_pcs." + inbound_pcs) - outbound_pcs, " .
                        "     update_dt = '". date('Y-m-d H:i:s') . "' " .
                        " WHERE item_id='" . $row['item_id'] . "'" .
                        "   AND item_dvsn_id=" . $row['item_dvsn_id'] . 
                        "   AND customer_id='" . $row['customer_id'] . "'" .
                        "   AND customer_dvsn_id=" . $row['customer_dvsn_id'] .
                        "   AND sum_date='". $row['sum_date'] ."'";

                    $this::$logger->debug("SQL: " . $sql);
                    $stmt = sqlsrv_query( $this::$db->getInstance(), $sql );
                    $rows_affected = sqlsrv_rows_affected( $stmt);
                    
                    if( $rows_affected === false) {
                        $this::$logger->error("Caught exception: " . print_r(sqlsrv_errors(), true));
                    } elseif( $rows_affected == -1 ) {
                        $rows_affected = 0;
                    } else {
                        $this::$logger->debug($rows_affected." rows were updated.");
                    }
                    $stockcard_pack = ($stockcard_pack + $row['inbound_pack']) - abs($row['outbound_pack']);
                    $stockcard_pcs = ($stockcard_pcs + $row['inbound_pcs']) - abs($row['outbound_pcs']);
                    $current_stock_pcs = $stockcard_pcs;
                }

  
        return $result;
      
    } 

       	/**
	 * Update stock card ib
	 *
	 * @return true if update successfully, otherwise return false
	 */
	public function cal_stock_card_ob($cal_date,$data)
	{  
           $sql =   " UPDATE " . $this->__TABLE .
                    " SET outbound_pack = outbound_pack + ".$data['package_qty'].", " .
                    "     outbound_pcs = outbound_pcs + ".$data['qty'].", " .
                    "     balance_pack = balance_pack - ".$data['package_qty'].", " .
                    "     balance_pcs = balance_pcs - ".$data['qty'].", " .
                    "     update_dt = '". date('Y-m-d H:i:s') . "'," .
                    "     update_user_id = 'system'" .
                    " WHERE item_id='" . $data['item_id'] . "'" .
                    "   AND item_dvsn_id=" . $data['item_dvsn_id'] . 
                    "   AND customer_id='" . $data['customer_id'] . "'" .
                    "   AND customer_dvsn_id=" . $data['customer_dvsn_id'] .
                    "   AND sum_date='". $cal_date ."'";

            $this::$logger->debug("SQL: " . $sql);
            $stmt = sqlsrv_query( $this::$db->getInstance(), $sql );
            $rows_affected = sqlsrv_rows_affected( $stmt);

            if( $rows_affected === false) {
                $this::$logger->error("Caught exception: " . print_r(sqlsrv_errors(), true));
            } elseif( $rows_affected == -1 ) {
                $rows_affected = 0;
            } else {
                $this::$logger->debug($rows_affected." rows were updated.");
            }

            if (!$rows_affected) {
                $StockCardRecord_cols = $this::$__COLUMNS;

                // remove unwant data
                $unsets = [ 'id', 'update_dt', 'update_user_id'];
                foreach ($unsets as $u) unset($StockCardRecord_cols[$u]);               

                $StockCardRecord_cols = Util::parseArrayData($StockCardRecord_cols, $data);

                list( $y, $m, $d) = explode("-",$cal_date);
                
                $clean_m = strlen($m)==1?"0".$m:$m;
                
                $packageTypes = (new PackageType())->get(PackageType::$__COLUMNS, "item_id='".$data['item_id']."' AND item_dvsn_id='".$data['item_dvsn_id']."'");

                // $stockcard_pack = 0;
                // $stockcard_pcs = 0;
                // if ( $type_cal=='realtime' ) {
                //     $last_stock = (new Stock())->get(Stock::$__COLUMNS, "item_id='".$data['item_id']."' AND item_dvsn_id='".$data['item_dvsn_id']."' AND customer_id='".$data['customer_id']."' AND customer_dvsn_id='".$data['customer_dvsn_id']."'");
                //     if( isset($last_stock[0]['qty']) ){
                //         $stockcard_pack = $last_stock[0]['qty']/$packageTypes[0]['qty'];
                //         $stockcard_pcs = $last_stock[0]['qty'];
                //     }
                // }
                
                // if ($type_cal=='recalulate' ) {
                //     $last_stockcard = (new DBFactory())->query("SELECT top 1 balance_pack AS stockcard_pack , balance_pcs AS stockcard_pcs FROM StockCardRecord WHERE item_id ='".$data['item_id']."' AND customer_id='".$data['customer_id']."' AND sum_date < '".$cal_date."' order by  sum_date, create_dt DESC");    
                //     if( isset($last_stockcard[0]['stockcard_pcs']) ){
                //         $stockcard_pack = $$last_stockcard[0]['stockcard_pack'];
                //         $stockcard_pcs = $last_stockcard[0]['stockcard_pcs'];
                //     }
                // }

                $StockCardRecord_cols['sum_date']           = $cal_date;
                $StockCardRecord_cols['package_id']         = isset($packageTypes[0]['package_id'])?$packageTypes[0]['package_id']:"box";
               // $StockCardRecord_cols['current_stock_pcs']  = $stockcard_pcs;   
               // $StockCardRecord_cols['stockcard_pack']     = $stockcard_pack;
              //  $StockCardRecord_cols['stockcard_pcs']      = $stockcard_pcs; 
                $StockCardRecord_cols['outbound_pack']      = $data['package_qty'];
                $StockCardRecord_cols['outbound_pcs']       = $data['qty'];
              //  $StockCardRecord_cols['balance_pack']       = $stockcard_pack - $data['package_qty'];
               // $StockCardRecord_cols['balance_pcs']        = $stockcard_pcs - $data['qty'];
                $StockCardRecord_cols['month_mm']           = $clean_m;
                $StockCardRecord_cols['year_20yy']          = $y;
                $StockCardRecord_cols['create_dt']          = date('Y-m-d H:i:s');
    
                $this::$logger->debug("array : " . json_encode($StockCardRecord_cols));
                $rows_affected = $this->add($StockCardRecord_cols) ? 1 : 0;
            }    

            return $rows_affected;
    } 
}