<html>
    <head>
        <title>Consolidated 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">Consolidated Order Report </th></tr>
                <?php
                $arr_months = Order::getmonths();
                $start_month = $from_month;
                $end_month = $to_month;
                $start_year = $from_year;
                $end_year = $to_year;
                $start_yeardate = $from_year . '-' . (($from_month > 9) ? $from_month : '0' . $from_month) . '-' . '01';
                $end_yeardate = $to_year . '-' . $to_month . '-' . '01';
                $modelmascategories = MasProductCategory::model()->findAll(array('order'=>'sort_order ASC'));
                $sql="SELECT distributer_id AS id,D.distributer_name 
                FROM ymh_order_details OD 
                LEFT JOIN ymh_order t ON t.id=OD.order_id 
                LEFT JOIN ymh_distributers D ON D.id=t.distributer_id 
                WHERE OD.order_date>='".$start_yeardate."' AND OD.order_date<='".$end_yeardate."' 
                GROUP BY t.distributer_id
                ORDER BY D.distributer_name ASC";
                $mod_dis = Yii::app()->db->createCommand($sql)->queryAll();
                $count_cat = count($modelmascategories);
                ?>
                <tr>
                    <th>Distributers</th>
                    <th>Category</th>
                    <?php for ($newdate = $start_yeardate; strtotime($newdate) <= strtotime($end_yeardate); 
                    $newdate = date('Y-m-d', strtotime($newdate . ' + 1 months'))) { ?>
                        <th class="text-center"><?php echo $arr_months[(int) date("m", strtotime($newdate))] . ' ' . (int) date("Y", strtotime($newdate)); ?></th>
                    <?php } ?>
                </tr>
                <?php
                    foreach($mod_dis as $each_distributer){ 
                    foreach($modelmascategories as $sl=>$each_cat){ ?>
                    
                <tr>
                    <?php //if($sl==0){ ?>
                    <td rowspan="<?php //echo $count_cat;?>"><?php echo $each_distributer['distributer_name'];?> </td>
                    <?php //} ?>
                    <td><?php echo $each_cat['product_category'];?> </td>
                     <?php for ($newdate = $start_yeardate; strtotime($newdate) <= strtotime($end_yeardate); 
                    $newdate = date('Y-m-d', strtotime($newdate . ' + 1 months'))) {
                    $cSQL = " SELECT IFNULL(SUM(quantity_ordered*price*exchange_rate),0) AS amount_ordered
                                FROM  `ymh_order_details` D LEFT JOIN ymh_order O ON O.id =D.order_id
                            WHERE O.month='".(int) date("m", strtotime($newdate))."' AND O.year='".(int) date("Y", strtotime($newdate))."'
                            AND D.category_id=".(int)$each_cat['id']." AND O.distributer_id=".(int)$each_distributer['id'];
                    $tmodeltotal = Yii::app()->db->createCommand($cSQL)->queryScalar();
                        
                    ?>
                    <td class="text-right"><?php echo number_format($tmodeltotal,2);?></td>
                    <?php } ?>
                    
                </tr>
                <?php }
                ?>
                    
                <!--<tr>
                    <td><?php /*echo $each_distributer['distributer_name'];?> </td>
                    <td></td>
                     <?php for ($newdate = $start_yeardate; strtotime($newdate) <= strtotime($end_yeardate); 
                    $newdate = date('Y-m-d', strtotime($newdate . ' + 1 months'))) {
                    $cSQL1 = " SELECT IFNULL(SUM(quantity_ordered*price*exchange_rate),0) AS amount_ordered
                                FROM  `ymh_order_details` D LEFT JOIN ymh_order O ON O.id =D.order_id
                            WHERE O.month='".(int) date("m", strtotime($newdate))."' AND O.year='".(int) date("Y", strtotime($newdate))."'
                             AND O.distributer_id=".(int)$each_distributer['id'];
                    $tmodeltotal1 = Yii::app()->db->createCommand($cSQL1)->queryScalar();
                        
                    ?>
                    <td class="text-right"><?php echo number_format($tmodeltotal1,2);?></td>
                    <?php }*/ ?>
                </tr>-->
                <?php 
                    }
                ?>
            </tbody>
        </table>
    </body>
</html>