<html>
    <body>
        <?php
        
        $price_date = $year.'-'.$month.'-'.'01';
        $cnd = 'is_active=:active OR (is_active=:discontinue AND ((discontinuing_month>=:month AND discontinuing_year=:year) OR discontinuing_year>:year))';
        $params = [
            ':active' => 'Y',
            ':discontinue' => 'D',
            ':month' => (int)$month,
            ':year' => (int)$year
        ];
        $model_inventory = Inventory::model()->findAll([
            'condition' => $cnd,
            'order' => 'product_category_id asc',
            'params' => $params,
            //'limit'=>10
        ]);
        $mod_dis = Distributers::model()->with('region_det')->findByPk((int)$distributer_id);
        $region_id= (int)$mod_dis->region_id;
        if($month<=3){
          $year =$year-1;
        }
        $year1 =(int)$year+1;
        if ($month >= 1 && $month <= 12) {
            $months = ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'];
            $index = $month - 1; // Adjust index for array starting at 0
            $type = 'Type Here||';
        
            ${$months[$index]} = $type; // Use variable variable to set the month value
        }
        ?>
        <table style="width:100%" class="table" border="1">
            <thead>
                <tr><th  colspan="92">ORDER REVIEW FORM QTY</th></tr>
                <tr>
                    <th colspan="2">Distributer</th>
                    <th colspan="6" class="text-left"><?php echo $mod_dis->distributer_name;?></th>
                    <th colspan="5" class="text-left"><?php echo $apr;?></th>
                    <th colspan="5" class="text-left"><?php echo $may;?></th>
                    <th colspan="5" class="text-left"><?php echo $jun;?></th>
                    <th colspan="5" class="text-left"><?php echo $jul;?></th>
                    <th colspan="5" class="text-left"><?php echo $aug;?></th>
                    <th colspan="5" class="text-left"><?php echo $sep;?></th>
                    <th colspan="5" class="text-left"><?php echo $oct;?></th>
                    <th colspan="5" class="text-left"><?php echo $nov;?></th>
                    <th colspan="5" class="text-left"><?php echo $dec;?></th>
                    <th colspan="5" class="text-left"><?php echo $jan;?></th>
                    <th colspan="5" class="text-left"><?php echo $feb;?></th>
                    <th colspan="5" class="text-left"><?php echo $mar;?></th>
                </tr>
                <tr>
                    <th rowspan="2" class="text-center"></th>
                    <th class="text-center">PRODUCT CATEGORY</th>
                    <th rowspan="2" class="text-center">New/old</th>
                    <th rowspan="2" class="text-center">GMC</th>
                    <th rowspan="2" class="text-center">Model</th>
                    <th rowspan="2" class="text-center"><?= $mod_dis->region_det->region_name;?></th>
                    <th rowspan="2" class="text-center">CBM</th>
                    <th rowspan="2" class="text-center">BASE CODE</th>
                    <th colspan="5" class="text-center">01-04-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-05-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-06-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">1Q TOTAL</th>
                    <th colspan="5" class="text-center">01-07-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-08-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-09-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">2Q TOTAL</th>
                    <th colspan="5" class="text-center">01-10-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-11-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-12-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">3Q TOTAL</th>
                    <th colspan="5" class="text-center">01-01-<?php echo $year1;?></th>
                    <th colspan="5" class="text-center">01-02-<?php echo $year1;?></th>
                    <th colspan="5" class="text-center">01-03-<?php echo $year1;?></th>
                    <th colspan="5" class="text-center">4Q TOTAL</th>
                    <th colspan="5" class="text-center">TOTAL <?php echo $year;?>-<?php echo $year1;?></th>
                </tr>
                <tr>
                    <?php for($a=1;$a<18;$a++){ ?>
                    <th>ORDER</th>
                    <th>Supply Confirmation</th>
                    <th>SHIP</th>
                    <th>DIF</th>
                    <th>CBM</th>
                    <?php } ?>
                </tr>
            </thead>
            <tbody>
                <?php
                $model_inventory_ids = array_map(function($item) {
                    return $item->id;
                }, $model_inventory);
                 // Generate a list of conditions to retrieve data in one query
                $conditions = array();
                for($i = 4; $i <= 12; $i++) {
                    $conditions[] = "(parent_det.month = {$i} AND parent_det.year = {$year})";
                }
                for($i = 1; $i <= 3; $i++) {
                    $conditions[] = "(parent_det.month = {$i} AND parent_det.year = {$year1})";
                }
                $condition_string = "parent_det.distributer_id = {$distributer_id} AND  product_id IN (".implode(',', $model_inventory_ids).")  AND (". implode(' OR ', $conditions).")";
                
                // Retrieve data in one query
                $order_details = OrderDetails::model()->with('parent_det')->findAll(array(
                    'select' => 'product_id,category_id, quantity_ordered, quantity_confirmed, quantity_shipped, parent_det.month AS order_month,is_done',
                    'condition' => $condition_string,
                ));
                
                // Prepare the data
                $data = array();$datac = array();
                foreach ($order_details as $eachorder) {
                    $product_id = $eachorder['product_id'];
                    $category_id = $eachorder['category_id'];
                    $month = (int) $eachorder['parent_det']['month'];
                    $quantity_ordered = (int) $eachorder['quantity_ordered'];
                    $quantity_confirmed = (int) $eachorder['quantity_confirmed'];
                    $quantity_shipped = (int) $eachorder['quantity_shipped'];
                    $data[$product_id][$month] = [
                        'quantity_ordered' => $quantity_ordered,
                        'quantity_confirmed' => $quantity_confirmed,
                        'quantity_shipped' => $quantity_shipped,
                        'quantity_diff'=>($quantity_confirmed-$quantity_shipped),
                        'is_done'=>$eachorder['is_done'],
                    ];
                    
                }
                // Retrieve Category data in one query
                $sql_categort_orderdetails = 'SELECT category_id, SUM(quantity_ordered)AS quantity_ordered,SUM(quantity_confirmed)AS quantity_confirmed , SUM(quantity_shipped) AS quantity_shipped,
                SUM(product_det.cbm*quantity_confirmed) AS quantity_confirmed_cbm, parent_det.month AS order_month
                FROM ymh_order_details t
                LEFT JOIN ymh_order parent_det ON parent_det.id=t.order_id
                LEFT JOIN ymh_inventory product_det ON product_det.id=t.product_id
                WHERE '.$condition_string.'
                GROUP BY t.category_id,parent_det.month
                ';
                $cat_order_details = Yii::app()->db->createCommand($sql_categort_orderdetails)->queryAll();
                /*$cat_order_details = OrderDetails::model()->with('parent_det','product_det')->findAll(array(
                    'select' => 'category_id, SUM(quantity_ordered)AS quantity_ordered,SUM(quantity_confirmed)AS quantity_confirmed , SUM(quantity_shipped) AS quantity_shipped,SUM(product_det.cbm*quantity_confirmed) AS quantity_backlog, parent_det.month AS order_month',
                    'condition' => $condition_string,
                    'group'=>'category_id,parent_det.month'
                ));*/
                // Prepare the data
                foreach ($cat_order_details as $eachcatorder) {
                    $category_id = $eachcatorder['category_id'];
                    $month = (int) $eachcatorder['order_month'];
                    $quantity_ordered = (int) $eachcatorder['quantity_ordered'];
                    $quantity_confirmed = (int) $eachcatorder['quantity_confirmed'];
                    $quantity_shipped = (int) $eachcatorder['quantity_shipped'];
                    $datac[$category_id][$month] = [
                        'category_id' =>$category_id,
                        'quantity_ordered' => $quantity_ordered,
                        'quantity_confirmed' => $quantity_confirmed,
                        'quantity_shipped' => $quantity_shipped,
                        'quantity_diff'=>($quantity_confirmed-$quantity_shipped),
                        'quantity_cbm'=>(int) $eachcatorder['quantity_confirmed_cbm']
                    ];
                }
                //echo '<pre>'.print_r($datac);
                // Calculate the required data
                
                foreach ($model_inventory as $i => $row) {
                    $product_id = $row['id'];
                    $tr_style='';
                    if( $row['is_active']=='D'){
                        $tr_style='background-color:#D3D3D3 ;';
                    }
                    $new_tilldate=$row['new_till_year']."-".$row['new_till_month']."-01";
                    //echo $price_date;
                    $timestamp1 = strtotime($price_date);
                    $timestampnewtill = strtotime($new_tilldate);
                    $old_new='';
                    if($timestampnewtill>=$timestamp1){
                       $old_new='New';
                    }
                    /*region value*/
                        $price_cnd = 'product_id='.(int)$row['id'].' AND region_id='.(int)$region_id.' AND from_date <="'.$price_date.'" AND to_date >="'.$price_date.'"';
                        $mod_inv_price=InventoryPrice::model()->find(array('condition'=>$price_cnd));
                    /*region value*/
                    $quarters = [[4, 5, 6], [7, 8, 9], [10, 11, 12], [1, 2, 3]];
                    $qty = ['ordered' => [], 'confirmed' => [],'shipped'=>[],'diff'=>[]];
                    foreach ($quarters as $j => $quarter) {
                        $qty['ordered'][$j] = array_sum(array_column(array_intersect_key($data[$product_id], array_flip($quarter)), 'quantity_ordered'));
                        $qty['confirmed'][$j] = array_sum(array_column(array_intersect_key($data[$product_id], array_flip($quarter)), 'quantity_confirmed'));
                        $qty['shipped'][$j] = array_sum(array_column(array_intersect_key($data[$product_id], array_flip($quarter)), 'quantity_shipped'));
                        $qty['diff'][$j] = array_sum(array_column(array_intersect_key($data[$product_id], array_flip($quarter)), 'quantity_diff'));
                    }
                    $qty_ord_tot = array_sum($qty['ordered']);
                    $qty_confirmed_tot = array_sum($qty['confirmed']);
                    $qty_shipped_tot = array_sum($qty['shipped']);
                    $qty_diff_tot = array_sum($qty['diff']);
                    
                ?>
                    <tr style="<?= $tr_style;?> ">
                        <td></td>
                        <td class="nw"><?= $row['category_det']['product_category']; ?></td>
                        <td class="nw"><?= $old_new; ?></td>
                        <td class="nw"><?= $row['gmc_code']; ?></td>
                        <td class="nw"><?= $row['model_no']; ?></td>
                        <td class="nw"><?= (float)$mod_inv_price['price']; ?></td>
                        <td class="nw"><?= $row['cbm']; ?></td>
                        <td class="nw"><?= $row['base_code']; ?></td>
                        <?php for ($j = 0; $j < 4; $j++) { ?>
                            <?php foreach ($quarters[$j] as $quarter_month) { 
                            $row_cbm = isset($data[$product_id][$quarter_month]['quantity_confirmed']) ?($data[$product_id][$quarter_month]['quantity_confirmed']*$row['cbm']):0;
                            $confirmed='';
                            if((int)$data[$product_id][$quarter_month]['quantity_ordered']==0){
                                $confirmed='';
                            }
                            else if((int)$data[$product_id][$quarter_month]['quantity_confirmed']>0 || $data[$product_id][$quarter_month]['is_done']=='Y'){
                                $confirmed= $data[$product_id][$quarter_month]['quantity_confirmed'];
                            } 
                            
                            ?>
                                <td class="nw"><?= isset($data[$product_id][$quarter_month]['quantity_ordered']) ? $data[$product_id][$quarter_month]['quantity_ordered']:0; ?></td>
                                <td class="nw"><?= $confirmed; ?></td>
                                <td class="nw"><?= isset($data[$product_id][$quarter_month]['quantity_shipped']) ? $data[$product_id][$quarter_month]['quantity_shipped']:0; ?></td>
                                <td class="nw"><?= isset($data[$product_id][$quarter_month]['quantity_diff']) ? $data[$product_id][$quarter_month]['quantity_diff']:0; ?></td>
                                <td class="nw"><?php echo $row_cbm;?></td>
                            <?php } ?>
                            <td class="nw"><?= $qty['ordered'][$j]; ?></td>
                            <td class="nw"><?= $qty['confirmed'][$j]; ?></td>
                            <td class="nw"><?= $qty['shipped'][$j]; ?></td>
                            <td class="nw"><?= $qty['diff'][$j]; ?></td>
                            <td class="nw"><?= $qty['confirmed'][$j]*$row['cbm']; ?></td>
                    <?php } ?>
                        
                        <td class="nw"><?= $qty_ord_tot; ?></td>
                        <td class="nw"><?= $qty_confirmed_tot; ?></td>
                        <td class="nw"><?= $qty_shipped_tot; ?></td>
                        <td class="nw"><?= $qty_diff_tot; ?></td>
                        <td class="nw"><?= ($qty_confirmed_tot*$row['cbm']); ?></td>
                    </tr>
                    
                <?php } ?>
                <tr>
                    <th class="text-center"></th>
                    <th class="text-center">PRODUCT CATEGORY</th>
                    <th colspan="6" class="text-center"></th>
                    <th colspan="5" class="text-center">01-04-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-05-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-06-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">1Q TOTAL</th>
                    <th colspan="5" class="text-center">01-07-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-08-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-09-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">2Q TOTAL</th>
                    <th colspan="5" class="text-center">01-10-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-11-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">01-12-<?php echo $year;?></th>
                    <th colspan="5" class="text-center">3Q TOTAL</th>
                    <th colspan="5" class="text-center">01-01-<?php echo $year1;?></th>
                    <th colspan="5" class="text-center">01-02-<?php echo $year1;?></th>
                    <th colspan="5" class="text-center">01-03-<?php echo $year1;?></th>
                    <th colspan="5" class="text-center">4Q TOTAL</th>
                    <th colspan="5" class="text-center">TOTAL <?php echo $year;?>-<?php echo $year1;?></th>
                </tr>
                <?php
                $modelmascategories = MasProductCategory::model()->findAll(array('order'=>'sort_order ASC'));
                $quarters = [[4, 5, 6], [7, 8, 9], [10, 11, 12], [1, 2, 3]];
                foreach($modelmascategories as $eachcat){ 
                    $category_id = $eachcat['id'];
                    $cqty = ['ordered' => [], 'confirmed' => [],'shipped'=>[],'diff'=>[]];
                    foreach ($quarters as $j => $quarter) {
                        $cqty['ordered'][$j] = array_sum(array_column(array_intersect_key($datac[$category_id], array_flip($quarter)), 'quantity_ordered'));
                        $cqty['confirmed'][$j] = array_sum(array_column(array_intersect_key($datac[$category_id], array_flip($quarter)), 'quantity_confirmed'));
                        $cqty['shipped'][$j] = array_sum(array_column(array_intersect_key($datac[$category_id], array_flip($quarter)), 'quantity_shipped'));
                        $cqty['diff'][$j] = array_sum(array_column(array_intersect_key($datac[$category_id], array_flip($quarter)), 'quantity_diff'));
                        $cqty['quantity_cbm'][$j] = array_sum(array_column(array_intersect_key($datac[$category_id], array_flip($quarter)), 'quantity_cbm'));
                    }
                    $cqty_ord_tot = array_sum($cqty['ordered']);
                    $cqty_confirmed_tot = array_sum($cqty['confirmed']);
                    $cqty_shipped_tot = array_sum($cqty['shipped']);
                    $cqty_diff_tot = array_sum($cqty['diff']);
                    $cqty_cbm_tot = array_sum($cqty['quantity_cbm']);
                ?>
                <tr style="<?= $tr_style;?> ">
                        <td></td>
                        <td class="nw"><?= $eachcat['product_category']; ?></td>
                        <td class="nw" colspan="6"></td>
                        <?php for ($j = 0; $j < 4; $j++) { ?>
                            <?php foreach ($quarters[$j] as $quarter_month) { 
                            $confirmed= $datac[$category_id][$quarter_month]['quantity_confirmed'];
                            ?>
                                <td class="nw"><?= isset($datac[$category_id][$quarter_month]['quantity_ordered']) ? $datac[$category_id][$quarter_month]['quantity_ordered']:0; ?></td>
                                <td class="nw"><?= $confirmed; ?></td>
                                <td class="nw"><?= isset($datac[$category_id][$quarter_month]['quantity_shipped']) ? $datac[$category_id][$quarter_month]['quantity_shipped']:0; ?></td>
                                <td class="nw"><?= isset($datac[$category_id][$quarter_month]['quantity_diff']) ? $datac[$category_id][$quarter_month]['quantity_diff']:0; ?></td>
                                <td class="nw"><?php echo isset($datac[$category_id][$quarter_month]['quantity_cbm']) ? $datac[$category_id][$quarter_month]['quantity_cbm']:0;?></td>
                            <?php } ?>
                            <td class="nw"><?= $cqty['ordered'][$j]; ?></td>
                            <td class="nw"><?= $cqty['confirmed'][$j]; ?></td>
                            <td class="nw"><?= $cqty['shipped'][$j]; ?></td>
                            <td class="nw"><?= $cqty['diff'][$j]; ?></td>
                            <td class="nw"><?= $cqty['quantity_cbm'][$j]; ?></td>
                    <?php } ?>
                        
                        <td class="nw"><?= $cqty_ord_tot; ?></td>
                        <td class="nw"><?= $cqty_confirmed_tot; ?></td>
                        <td class="nw"><?= $cqty_shipped_tot; ?></td>
                        <td class="nw"><?= $cqty_diff_tot; ?></td>
                        <td class="nw"><?= $cqty_cbm_tot; ?></td>
                    </tr>
                <?php
                }
                ?>

            </tbody>
        </table>
    </body>
</html>
