
<?
require_once "lib/DBFactory.php";
require_once "lib/ext/DateTimeUtil.php";
require_once "lib/PackageType.php";
require_once "lib/Customer.php";
require_once "lib/Util.php";
require_once "lib/Item.php";
require_once "lib/Stock.php";
require_once "lib/StockCardRecord.php";

$PackageTypes = (new DBFactory())->query("SELECT * FROM PackageType WHERE item_dvsn_id=1");

$WHERE = "1=1";
$Customers = (new Customer())->get(Customer::$__COLUMNS,$WHERE);

$year = 2021;
$month = 12;

$count_total = 0;
foreach( $Customers as $row_cust ){
    $customer_id = $row_cust['id'];
    $customer_dvsn_id = $row_cust['division'];
    $WHERE_Loop =  " customer_id='" . $customer_id."' ";
    $Items = (new Item())->get(Item::$__COLUMNS,$WHERE_Loop);
    if( $Items!=null ){
      foreach( $Items as $row_item ){
          $current_stock_pcs = 0;
          $stockcard_pack = 0;
          $stockcard_pcs = 0;
          $item_id = $row_item['id'];
          $item_dvsn_id = $row_item['division'];
          $row_package = util::util_search($PackageTypes,'item_id',$item_id);
          $package_id = $row_package['package_id'];
          $qty_per_pack = $row_package['qty'];
          for($y=2021; $y<=$year; $y++){

            for($m=1; $m<=$month; $m++){

                $last_date = date("t", strtotime($y.'-'.$m.'-1'));  // yyyy-mm-dd "2008-02-23"

                for($d=1; $d<=$last_date; $d++){
                    $sum_date = "$y-$m-$d";
                    $start = "'$sum_date 00:00:00.000'";
                    $end = "'$sum_date 23:59:59.000'";
                    // echo "SELECT item_id, sum(package_qty) AS inbound_pack, sum(qty) AS inbound_pcs, '$sum_date' AS sum_date  FROM Inbound WHERE item_id='$item_id' AND create_dt BETWEEN $start AND $end GROUP BY item_id";
                    // echo "<br/>";
                    // echo "SELECT item_id, sum(package_qty) AS outbound_pack, sum(qty) AS outbound_pcs, '$sum_date' AS sum_date  FROM Outbound WHERE item_id='$item_id' AND create_dt BETWEEN $start AND $end GROUP BY item_id";
                    // echo "<br/>";
                    // echo "<br/>";
                    $row_in = (new DBFactory())->query("SELECT item_id, sum(package_qty) AS inbound_pack, sum(qty) AS inbound_pcs, '$sum_date' AS sum_date  FROM Inbound WHERE item_id='$item_id' AND (create_dt BETWEEN $start AND $end ) GROUP BY item_id");
                    $row_out = (new DBFactory())->query("SELECT item_id, sum(package_qty) AS outbound_pack, sum(qty) AS outbound_pcs, '$sum_date' AS sum_date  FROM Outbound WHERE item_id='$item_id' AND (create_dt BETWEEN $start AND $end) GROUP BY item_id");
                   
                    // echo json_encode($row_in);
                    // echo "<br/>";
                    // echo json_encode($row_out);
                    // echo "<br/>";
                    // echo "<br/>";
                    
                    if( $row_in != null || $row_out != null){
                          $inbound_pack = 0;   $inbound_pcs = 0; 
                          $outbound_pack = 0;   $outbound_pcs = 0; 
                          if( $row_in!=null ){ $inbound_pack = $row_in['inbound_pack'];   $inbound_pcs = $row_in['inbound_pcs']; }
                          if( $row_out!=null ){ $outbound_pack = $row_out['outbound_pack'];   $outbound_pcs = $row_out['outbound_pcs']; }

                          $stockcard_pack = $current_stock_pcs / $qty_per_pack;
                          $stockcard_pcs = $current_stock_pcs;

                          $balance_pack = ($stockcard_pack + $inbound_pack)-$outbound_pack;
                          $balance_pcs = ($stockcard_pcs + $inbound_pcs)-$outbound_pcs;
                          $cols = StockCardRecord::$__COLUMNS;
                          $unsets = ['create_dt', 'update_dt', 'update_user_id'];
                          foreach ($unsets as $u) unset($cols[$u]);

                          $cols['item_id'] = $item_id;
                          $cols['item_dvsn_id'] = $item_dvsn_id;
                          $cols['customer_id'] = $customer_id;
                          $cols['customer_dvsn_id'] = $customer_dvsn_id;
                          $cols['sum_date'] = $sum_date;
                          $cols['package_id'] = $package_id;
                          $cols['current_stock_pcs'] = $current_stock_pcs;
                          $cols['stockcard_pack'] = $stockcard_pack;
                          $cols['stockcard_pcs'] = $stockcard_pcs;
                          $cols['inbound_pack'] = $inbound_pack;
                          $cols['inbound_pcs'] = $inbound_pcs;

                          $cols['outbound_pack'] = $outbound_pack;
                          $cols['outbound_pcs'] = $outbound_pcs;
                          $cols['balance_pack'] = $balance_pack;
                          $cols['balance_pcs'] = $balance_pcs;
                          $cols['month_mm'] = $m;
                          $cols['year_20yy'] = $y;
                          $cols['create_user_id'] = 'System';

                          //echo json_encode($cols);
                          //echo '<br/>';
                          $id = (new StockCardRecord())->add($cols);
                          if ($id) {
                            $current_stock_pcs = $balance_pcs;
                            $count_total++;
                          }
                    }
                 
                }
            }

          }

      }




    }
}

echo $count_total;

//echo date("t", strtotime('2023-8-1'));
//echo date('t'); => last date



?>