<html>
    <head>
        <title>Consolidated Supply Confirmation</title>
        <style>

            table, th, td {
                border: 1px solid black;
            }

        </style>

    </head>
    <body>
        <table style="width:100%">
            <thead>
                <tr><th  colspan="11">Consolidated Supply Confirmation </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';
                ?>
                <tr>
                    <th rowspan="3">Product Category</th>
                    <th rowspan="3">GMC</th>
                    <th rowspan="3">Model</th>
                    <?php for ($newdate = $start_yeardate; strtotime($newdate) <= strtotime($end_yeardate); $newdate = date('Y-m-d', strtotime($newdate . ' + 1 months'))) { ?>
                        <th colspan="6" class="text-center"><?php echo $arr_months[(int) date("m", strtotime($newdate))] . ' ' . $start_year; ?></th>
                    <?php } ?>
                    <th colspan="6" class="text-center">Total</th>
                </tr>
                <tr>
                    <?php for ($newdate = $start_yeardate; strtotime($newdate) <= strtotime($end_yeardate); $newdate = date('Y-m-d', strtotime($newdate . ' + 1 months'))) { ?>
                        <th colspan="3" class="text-center">Quantity</th>
                        <th colspan="3" class="text-center">Amount</th>
                    <?php } ?>
                    <th colspan="3" class="text-center">Quantity</th>
                    <th colspan="3" class="text-center">Amount</th>

                </tr>
                <tr>
                    <?php for ($newdate = $start_yeardate; strtotime($newdate) <= strtotime($end_yeardate); $newdate = date('Y-m-d', strtotime($newdate . ' + 1 months'))) { ?>
                        <th>Ordered</th>
                        <th>Confirmed</th>
                        <th>Shipped</th>
                        <th>Ordered</th>
                        <th>Confirmed</th>
                        <th>Shipped</th>
                    <?php } ?>
                    <th>Ordered</th>
                    <th>Confirmed</th>
                    <th>Shipped</th>
                    <th>Ordered</th>
                    <th>Confirmed</th>
                    <th>Shipped</th>
                </tr>
            </thead>
            <tbody>
                <?php
                $model_inventory = Inventory::model()->with('category_det')->findAll();//array('order' => 'category_det.sort_order asc')

                foreach ($model_inventory as $i => $row) {
                    $total_quantity_ordered = $total_amount_ordered = 0;
                    $total_quantity_confirmed = $total_amount_confirmed = 0;
                    $total_quantity_shipped = $total_amount_shipped = 0;
                    ?>
                    <tr>
                        <td><?php echo $row['category_det']['product_category']; ?></td>
                        <td><?php echo $row['gmc_code']; ?></td>
                        <td><?php echo $row['model_no']; ?></td>
                        <?php
                        $p = 1;
                        $total_quantity_ordered = $total_amount_ordered = 0;
                        $total_quantity_confirmed = $total_amount_confirmed = 0;
                        $total_quantity_shipped = $total_amount_shipped = 0;
                        for ($newdate = $start_yeardate; strtotime($newdate) <= strtotime($end_yeardate); $newdate = date('Y-m-d', strtotime($newdate . ' + 1 months'))) {
                            $p++;
                            $SQL = " SELECT SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,
                                    SUM(quantity_ordered*price*exchange_rate) AS amount_ordered,SUM(quantity_confirmed*price*exchange_rate) AS amount_confirmed,SUM(quantity_shipped*price*exchange_rate) AS amount_shipped
                                    FROM  `ymh_order_details` LEFT JOIN ymh_order O ON O.id =ymh_order_details.order_id
                                    WHERE month=" . (int) date("m", strtotime($newdate)) . " AND year=" . (int) date("Y", strtotime($newdate)) . " AND product_id=" . (int) $row['id'];
                            $modeltotal = Yii::app()->db->createCommand($SQL)->queryRow();
                            $total_quantity_ordered+=$modeltotal['quantity_ordered'];
                            $total_quantity_confirmed+=$modeltotal['quantity_confirmed'];
                            $total_quantity_shipped+=$modeltotal['quantity_shipped'];
                            $total_amount_ordered+=$modeltotal['amount_ordered'];
                            $total_amount_confirmed+=$modeltotal['amount_confirmed'];
                            $total_amount_shipped+=$modeltotal['amount_shipped'];
                            ?>
                            <td class="text-right"><?php echo $modeltotal['quantity_ordered']; ?></td>
                            <td class="text-right"><?php echo $modeltotal['quantity_confirmed']; ?></td>
                            <td class="text-right"><?php echo $modeltotal['quantity_shipped']; ?></td>
                            <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($modeltotal['amount_ordered'], 2), ""); ?></td>
                            <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($modeltotal['amount_confirmed'], 2), ""); ?></td>
                            <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($modeltotal['amount_shipped'], 2), ""); ?></td>
                        <?php } ?>
                        <td class="text-right"><?php echo $total_quantity_ordered; ?></td>
                        <td class="text-right"><?php echo $total_quantity_confirmed; ?></td>
                        <td class="text-right"><?php echo $total_quantity_shipped; ?></td>
                        <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($total_amount_ordered, 2), ""); ?></td>
                        <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($total_amount_confirmed, 2), ""); ?></td>
                        <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($total_amount_shipped, 2), ""); ?></td>
                    </tr>

                <?php } ?>
                <tr>
                    <th colspan="<?php echo ($p * 6) + 9; ?>">Category</th>
                </tr>
                <?php
                $modelmascategories = MasProductCategory::model()->findAll(array('order' => 'sort_order ASC'));

                foreach ($modelmascategories as $eachcat) {
                    $ctotal_quantity_ordered = $ctotal_amount_ordered = 0;
                    $ctotal_quantity_confirmed = $ctotal_amount_confirmed = 0;
                    $ctotal_quantity_shipped = $ctotal_amount_shipped = 0;
                    ?>

                    <tr>
                        <th colspan ="3"><?php echo $eachcat['product_category']; ?></th>
                        <?php
                        for ($newdate = $start_yeardate; strtotime($newdate) <= strtotime($end_yeardate); $newdate = date('Y-m-d', strtotime($newdate . ' + 1 months'))) {
                            $cSQL = " SELECT SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,
                                    SUM(quantity_ordered*price*exchange_rate) AS amount_ordered,SUM(quantity_confirmed*price*exchange_rate) AS amount_confirmed,SUM(quantity_shipped*price*exchange_rate) AS amount_shipped
                                    FROM  `ymh_order_details` LEFT JOIN ymh_order O ON O.id =ymh_order_details.order_id
                                    WHERE month=" . (int) date("m", strtotime($newdate)) . " AND year=" . (int) date("Y", strtotime($newdate)) . " AND category_id=" . (int) $eachcat['id'];
                            $cmodeltotal = Yii::app()->db->createCommand($cSQL)->queryRow();
                            $ctotal_quantity_ordered+=$cmodeltotal['quantity_ordered'];
                            $ctotal_quantity_confirmed+=$cmodeltotal['quantity_confirmed'];
                            $ctotal_quantity_shipped+=$cmodeltotal['quantity_shipped'];
                            $ctotal_amount_ordered+=$cmodeltotal['amount_ordered'];
                            $ctotal_amount_confirmed+=$cmodeltotal['amount_confirmed'];
                            $ctotal_amount_shipped+=$cmodeltotal['amount_shipped'];
                            ?>
                            <td class="text-right"><?php echo $cmodeltotal['quantity_ordered']; ?></td>
                            <td class="text-right"><?php echo $cmodeltotal['quantity_confirmed']; ?></td>
                            <td class="text-right"><?php echo $cmodeltotal['quantity_shipped']; ?></td>
                            <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($cmodeltotal['amount_ordered'], 2), ""); ?></td>
                            <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($cmodeltotal['amount_confirmed'], 2), ""); ?></td>
                            <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($cmodeltotal['amount_shipped'], 2), ""); ?></td>
                        <?php } ?>
                        <td class="text-right"><?php echo $ctotal_quantity_ordered; ?></td>
                        <td class="text-right"><?php echo $ctotal_quantity_confirmed; ?></td>
                        <td class="text-right"><?php echo $ctotal_quantity_shipped; ?></td>
                        <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($ctotal_amount_ordered, 2), ""); ?></td>
                        <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($ctotal_amount_confirmed, 2), ""); ?></td>
                        <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($ctotal_amount_shipped, 2), ""); ?></td>
                    </tr> 
                <?php }
                ?>
                <tr>
                    <th colspan ="3">Total</th>
                    <?php
                    $ttotal_quantity_ordered = $ttotal_quantity_confirmed = $ttotal_quantity_shipped = 0;
                    $ttotal_amount_ordered = $ttotal_amount_confirmed = $ttotal_amount_shipped = 0;
                    for ($newdate = $start_yeardate; strtotime($newdate) <= strtotime($end_yeardate); $newdate = date('Y-m-d', strtotime($newdate . ' + 1 months'))) {
                        $cSQL = " SELECT SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,
                            SUM(quantity_ordered*price*exchange_rate) AS amount_ordered,SUM(quantity_confirmed*price*exchange_rate) AS amount_confirmed,SUM(quantity_shipped*price*exchange_rate) AS amount_shipped
                            FROM  `ymh_order_details` LEFT JOIN ymh_order O ON O.id =ymh_order_details.order_id
                            WHERE month=" . (int) date("m", strtotime($newdate)) . " AND year=" . (int) date("Y", strtotime($newdate)) . " ";
                        $tmodeltotal = Yii::app()->db->createCommand($cSQL)->queryRow();
                        $ttotal_quantity_ordered+=$tmodeltotal['quantity_ordered'];
                        $ttotal_quantity_confirmed+=$tmodeltotal['quantity_confirmed'];
                        $ttotal_quantity_shipped+=$tmodeltotal['quantity_shipped'];
                        $ttotal_amount_ordered+=$tmodeltotal['amount_ordered'];
                        $ttotal_amount_confirmed+=$tmodeltotal['amount_confirmed'];
                        $ttotal_amount_shipped+=$tmodeltotal['amount_shipped'];
                        ?>
                        <td class="text-right"><?php echo $tmodeltotal['quantity_ordered']; ?></td>
                        <td class="text-right"><?php echo $tmodeltotal['quantity_confirmed']; ?></td>
                        <td class="text-right"><?php echo $tmodeltotal['quantity_shipped']; ?></td>
                        <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($tmodeltotal['amount_ordered'], 2), ""); ?></td>
                        <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($tmodeltotal['amount_confirmed'], 2), ""); ?></td>
                        <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($tmodeltotal['amount_shipped'], 2), ""); ?></td>
                    <?php } ?>
                    <td class="text-right"><?php echo $ttotal_quantity_ordered; ?></td>
                    <td class="text-right"><?php echo $ttotal_quantity_confirmed; ?></td>
                    <td class="text-right"><?php echo $ttotal_quantity_shipped; ?></td>
                    <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($ttotal_amount_ordered, 2), ""); ?></td>
                    <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($ttotal_amount_confirmed, 2), ""); ?></td>
                    <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency(round($ttotal_amount_shipped, 2), ""); ?></td>
                </tr>
            </tbody>
        </table>
    </body>
</html>