<html>
    <head>
        <title>Distributerwise Order Report</title>
        <style>

            table, th, td {
                border: 1px solid black;
            }
            .text-right{
                text-align:right;
            }

        </style>

    </head>
    <body>
    <table style="width:100%">
    <thead>
    <tr>
      <th colspan="11">Distributerwise Order Report</th>
    </tr>
    <?php
    $arr_month = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
    $start_date = new DateTime("$from_year-$from_month-01");
    $end_date = new DateTime("$to_year-$to_month-01");
    //Prepare the SQL query to get distributers and categories
    $dcsql = "SELECT t.distributer_id, D.distributer_name, t.category_id, C.product_category
              FROM view_order_consolidated_details t
              LEFT JOIN ymh_distributers D ON D.id = t.distributer_id
              LEFT JOIN ymh_mas_product_category C ON C.id = t.category_id
              WHERE t.order_date >= :start_date AND t.order_date <= :end_date
              GROUP BY t.distributer_id, t.category_id
              ORDER BY D.distributer_name ASC, C.sort_order ASC";
    $command1 = Yii::app()->db->createCommand($dcsql);
    $command1->bindParam(":start_date", $start_date->format("Y-m-d"));
    $command1->bindParam(":end_date", $end_date->format("Y-m-t"));
    $modeldiscategories = $command1->queryAll();
    // Prepare the SQL query to create view
    $sqlview = "
        CREATE OR REPLACE VIEW `view_order_categorywise_details` AS(
    SELECT
        V.distributer_id,        
        V.category_id,
        V.order_month,
        V.order_year,
        SUM(quantity_ordered) AS quantity_ordered,
        SUM(quantity_confirmed) AS quantity_confirmed,
        SUM(quantity_shipped) AS quantity_shipped,
        SUM(amount_ordered) AS amount_ordered,
        SUM(amount_confirmed) AS amount_confirmed,
        SUM(amount_shipped) AS amount_shipped
    FROM
        view_order_consolidated_details V
    WHERE V.order_date >= :start_date AND V.order_date <= :end_date
    GROUP BY
        	V.order_year,
		V.order_month,
		V.distributer_id,
        	V.category_id )
    ";

    // Bind the parameter values
    $command2 = Yii::app()->db->createCommand($sqlview);
    $command2->bindParam(":start_date", $start_date->format("Y-m-d"));
    $command2->bindParam(":end_date", $end_date->format("Y-m-t"));

    // Execute the query and to create view
    $command2->execute();
    //echo $end_date->format("Y-m-t"); echo $start_date->format("Y-m-d");
    
    // Prepare the SQL query to select all values
    $sqlallmonths = "SELECT distributer_id,category_id, O.order_month, O.order_year, O.quantity_ordered, O.quantity_confirmed, O.quantity_shipped, O.amount_ordered, O.amount_confirmed, O.amount_shipped
            FROM view_order_categorywise_details O
            ORDER BY O.order_year ASC, O.order_month ASC";
    $command3 = Yii::app()->db->createCommand($sqlallmonths);
    // Execute the query and fetch all the results
    $results = $command3->queryAll();

    // Group the results by distributor, category, month, and year
    $grouped_results = [];
    foreach ($results as $result) {
        $group_key = "{$result['distributer_id']}_{$result['category_id']}_{$result['order_month']}_{$result['order_year']}";
        $grouped_results[$group_key] = $result;
    }
   //echo '<pre>';print_r($grouped_results);
    ?>
    <tr>
      <th rowspan="2">Distributers</th>
      <th rowspan="2">Category</th>
      <?php while ($start_date <= $end_date) { ?>
        <th class="text-center" colspan="6"><?php echo  $arr_month[(int) $start_date->format("m") - 1] . ' ' . $start_date->format("Y"); ?></th>
        <?php $start_date->add(new DateInterval("P1M")); ?>
      <?php } ?>
    </tr>
    <tr>
      <?php $start_date = new DateTime("$from_year-$from_month-01");
      while ($start_date <= $end_date) { ?>
        <th class="text-center">QTY ORDERED</th>
        <th class="text-center">QTY CONFIRMED</th>
        <th class="text-center">QTY SHIPPED</th>
        <th class="text-center">AMT ORDERED</th>
        <th class="text-center">AMT CONFIRMED</th>
        <th class="text-center">AMT SHIPPED</th>
        <?php $start_date->add(new DateInterval("P1M")); ?>
      <?php } ?>
    </tr>
  </thead>
  <tbody>
    <?php 
    // Output the table rows
    foreach ($modeldiscategories as $each_catdis) { ?>
      <tr>
        <td><?php echo  $each_catdis['distributer_name']; ?></td>
        <td><?php echo  $each_catdis['product_category'];?> </td>
        <?php 
        $start_date = new DateTime("$from_year-$from_month-01");
        while ($start_date <= $end_date) { 
            $m=(int) $start_date->format('m');
            $y=(int) $start_date->format('Y');
            $group_key = "{$each_catdis['distributer_id']}_{$each_catdis['category_id']}_{$m}_{$y}";
            if (isset($grouped_results[$group_key])) {
                $tmodel = $grouped_results[$group_key];
            } else {
                $tmodel = null;
            }?>
                    <td class="text-right"><?php echo number_format(isset($tmodel['quantity_ordered']) ? $tmodel['quantity_ordered'] : 0, 2);?></td>
                    <td class="text-right"><?php echo number_format(isset($tmodel['quantity_confirmed']) ? $tmodel['quantity_confirmed'] : 0, 2);?></td>
                    <td class="text-right"><?php echo number_format(isset($tmodel['quantity_shipped']) ? $tmodel['quantity_shipped'] : 0,2);?></td>
                    <td class="text-right"><?php echo number_format(isset($tmodel['amount_ordered']) ? $tmodel['amount_ordered'] : 0,2);?></td>
                    <td class="text-right"><?php echo number_format(isset($tmodel['amount_confirmed']) ? $tmodel['amount_confirmed'] : 0,2);?></td>
                    <td class="text-right"><?php echo number_format(isset($tmodel['amount_shipped']) ? $tmodel['amount_shipped'] : 0,2);?></td>
               <?php $start_date->add(new DateInterval("P1M")); ?>
        <?php } ?>
                </tr>
                <?php } ?>
                
            </tbody>
        </table>
    </body>
</html>