<html>
<head>
    <title>Sales Transaction Dump 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="42">Sales Transaction Dump Report</th>
        </tr>
        <tr>
            <th>Sort #(Current FY)</th>
            <th>Area</th>
            <th>Distributor</th>
            <th>Distributor Type</th>
            <th>Distributor Alias</th>
            <th>Distributor Alias 2</th>
            <th>Distributor Alias 3</th>
            <th>Sales Month</th>
            <th>Product Category</th>
            <th>GMC Code</th>
            <th>Model No</th>
            <th>Qty</th>
            <th>Amount in USD</th>
            <th>CBM</th>
            <th>Base Code</th>
            <th>New Till Year</th>
            <th>New Till Month</th>
            <th>Color</th>
            <th>Base Category Code</th>
            <th>Base Category</th>
            <th>Sub Category</th>
            <th>Other 1</th>
            <th>Other 2</th>
            <th>Series Qty wise</th>
            <th>Series Amt wise</th>
            <th>Other 3</th>
            <th>Other 4</th>
            <th>Description</th>
            <th>M3</th>
            <th>Length cm</th>
            <th>Width cm</th>
            <th>Height cm</th>
            <th>KG (Gross)</th>
            <th>Bar Code</th>
            <th>Origin</th>
            <th>ABC Category Type(Current FY)</th>
            <th>Order Lot to YCJ</th>
            <th>ETA</th>
            <th>Cable Modification</th>
            <th>Inventory Report Type</th>
            <th>Is Confidential</th>
            <th>Top Level Model</th>
            <th>ABC Category Type(Next FY)</th>
            <th>Sort #(Next FY)</th>
            <th>Is Successor</th>
            <th>GMC Type</th>
            <th>Status</th>
            <th>Goods In start Month</th>
            <th>Goods In Start Year</th>
            <th>Goods In End Month </th>
            <th>Goods In End Year </th>
            <th>Backlog Start Month</th>
            <th>Backlog Start Year</th>
            <th>Backlog End Month</th>
            <th>Backlog End Year</th>
        </tr>
    </thead>

    <tbody>
        <?php
        $condition_SD = " (
            (sales_year  > $from_year AND sales_year  < $to_year) 
            OR 
            (sales_year  = $from_year AND sales_year  = $to_year AND sales_month >= $from_month AND sales_month <= $to_month)
            OR
            (sales_year  = $from_year AND sales_year  < $to_year AND sales_month >= $from_month)
            OR 
            (sales_year  = $to_year AND sales_year  > $from_year AND sales_month <= $to_month)
        )";
        
        $sql_SD = "SELECT 
            I.product_category_id,
            I.sub_category,
            I.spiral_sub_category,
            I.gmc_code,
            I.length,
            I.width,
            I.height,
            I.weight,
            I.cbm,
            I.base_code,
            I.new_till_month,
            I.new_till_year,
            I.gmc_colour,
            I.model_no,
            I.base_category_code,
            I.base_category_name,
            I.other_1,
            I.other_2,
            I.other_3,
            I.other_4,
            I.psi_series,
            I.description,
            I.barcode,
            I.origin,
            I.order_lot_to_ycj,
            I.eta_month,
            I.eta_year,
            I.cable_modeification,
            I.inventory_type,
            I.is_confidential,
            I.is_toplevel,
            I.gmc_sort_order,
            I.future_gmc_sort_order,
            I.category_type_id,
            I.future_category_type_id,
            I.gmc_type,
            I.is_active,
            I.have_successor,
            I.goods_in_month,
            I.goods_in_year,
            I.goods_in_end_month,
            I.goods_in_end_year,
            I.backlog_month,
            I.backlog_year,
            I.backlog_end_month,
            I.backlog_end_year,
            C.product_category,
            D.distributer_name,
            D.alias_name,
            D.alias_2_name,
            D.alias_3_name,
            DT.distributer_type,
            A.area_name,
            SD.sales_month AS su_month,
            SD.sales_year AS su_year,
            SD.sales_quantity AS qty,
            SD.amount_in_usd AS amt

        FROM {{sales_details}} SD
        LEFT JOIN {{inventory}} I ON SD.product_id = I.id
        LEFT JOIN {{mas_product_category}} C ON C.id = I.product_category_id
        LEFT JOIN {{distributers}} D ON SD.distributor_id = D.id
        LEFT JOIN {{mas_distributer_type}} DT ON D.distributer_type = DT.id
        LEFT JOIN {{mas_area}} A ON A.id = D.area_id
        WHERE $condition_SD
        ORDER BY SD.id ASC";
        
        $arr_SD = Yii::app()->db->createCommand($sql_SD)->queryAll();
        $arr_months = Order::getmonths();
        $modMasCategoryType = MasCategoryType::model()->findAll();
        $arr_categry = [];
        foreach ($modMasCategoryType as $d) {
            $arr_categry[$d->id] = $d->category_type_name;
        }
        $sr = 1;
        
        foreach ($arr_SD as $row) {
        
            $eta_month_name = '';
            if (!empty($row['eta_month'])) {
                $eta_month_name = $arr_months[(int)$row['eta_month']];
            }
            $su_month = (int)$row['su_month'];
            $su_year  = (int)$row['su_year'];
        
            $su_month_name = '';
            if ($su_month && $su_year) {
                $su_month_name = $arr_months[$su_month] . ' - ' . $su_year;
            }
            $new_till_month_name = '';
            if (!empty($row['new_till_month'])) {
                $new_till_month_name = $arr_months[(int)$row['new_till_month']];
            }
            $goods_in_month_name = '';
            if (!empty($row['goods_in_month'])) {
                $goods_in_month_name = $arr_months[(int)$row['goods_in_month']];
            }

            $goods_in_end_month_name = '';
            if (!empty($row['goods_in_end_month'])) {
                $goods_in_end_month_name = $arr_months[(int)$row['goods_in_end_month']];
            }

            $backlog_month_name = '';
            if (!empty($row['backlog_month'])) {
                $backlog_month_name = $arr_months[(int)$row['backlog_month']];
            }

            $backlog_end_month_name = '';
            if (!empty($row['backlog_end_month'])) {
                $backlog_end_month_name = $arr_months[(int)$row['backlog_end_month']];
            }

        ?>
        <tr>
            <td><?php echo $row['gmc_sort_order']; ?></td>
            <td><?php echo $row['area_name']; ?></td>
            <td><?php echo $row['distributer_name']; ?></td>
            <td><?php echo $row['distributer_type']; ?></td>
            <td><?php echo $row['alias_name']; ?></td>
            <td><?php echo $row['alias_2_name']; ?></td>
            <td><?php echo $row['alias_3_name']; ?></td>
            <td><?php echo $su_month_name; ?></td>
            <td><?php echo $row['product_category']; ?></td>
            <td><?php echo $row['gmc_code']; ?></td>
            <td><?php echo $row['model_no']; ?></td>
            <td class="text-right"><?php echo $row['qty']; ?></td>
            <td class="text-right"><?php echo number_format($row['amt'], 2); ?></td>
            <td><?php echo $row['cbm']; ?></td>
            <td><?php echo $row['base_code']; ?></td>
            <td><?php echo $row['new_till_year']; ?></td>
            <td><?php echo $new_till_month_name; ?></td>
            <td><?php echo $row['gmc_colour']; ?></td>
            <td><?php echo $row['base_category_code']; ?></td>
            <td><?php echo $row['base_category_name']; ?></td>
            <td><?php echo $row['sub_category']; ?></td>
            <td><?php echo $row['other_1']; ?></td>
            <td><?php echo $row['other_2']; ?></td>
            <td><?php echo $row['spiral_sub_category']; ?></td>
            <td><?php echo $row['psi_series']; ?></td>
            <td><?php echo $row['other_3']; ?></td>
            <td><?php echo $row['other_4']; ?></td>
            <td><?php echo $row['description']; ?></td>
            <td><?php echo $row['cbm']; ?></td>
            <td><?php echo $row['length']; ?></td>
            <td><?php echo $row['width']; ?></td>
            <td><?php echo $row['height']; ?></td>
            <td><?php echo $row['weight']; ?></td>
            <td><?php echo $row['barcode']; ?></td>
            <td><?php echo $row['origin']; ?></td>
            <td><?php echo $arr_categry[$row['category_type_id']]; ?></td>
            <td><?php echo $row['order_lot_to_ycj']; ?></td>
            <td><?php echo $eta_month_name; ?></td>
            <td><?php echo $row['cable_modeification']; ?></td>
            <?php
                $invType = [
                    'N' => 'Normal',
                    'S' => 'Strategic'
                ];
            ?>
            <td><?php echo $invType[$row['inventory_type']] ?? ''; ?></td>
             <?php
                $isConfdntl = [
                    'Y' => 'Yes',
                    'N' => 'No'
                ];
            ?>
            <td><?php echo $isConfdntl[$row['is_confidential']] ?? ''; ?></td>
            <!-- <td><?php //echo $row['is_confidential']; ?></td> -->
             <?php
                $isToplvl = [
                    'Y' => 'Yes',
                    'N' => 'No'
                ];
            ?>
            <td><?php echo $isToplvl[$row['is_toplevel']] ?? ''; ?></td>
            <td><?php echo $arr_categry[$row['future_category_type_id']]; ?></td>
            <td><?php echo $row['future_gmc_sort_order']; ?></td>
             <?php
                $is_scscer = [
                    'Y' => 'Yes',
                    'N' => 'No'
                ];
            ?>
            <td><?php echo $is_scscer[$row['have_successor']] ?? ''; ?></td>
            <?php
                $gmctyp = [
                    'NI' => 'Normal Item',
                    'K' => 'Kit',
                    'P' => 'Pair'
                ];
            ?>
            <td><?php echo $gmctyp[$row['gmc_type']] ?? ''; ?></td>
            <?php
                $isActive = [
                    'Y' => 'Active',
                    'N' => 'Block',
                    'D' => 'Fully Discontinued'
                ];
            ?>
            <td><?php echo $isActive[$row['is_active']] ?? ''; ?></td>
            <td><?php echo $goods_in_month_name; ?></td>
            <td><?php echo $row['goods_in_year']; ?></td>
            <td><?php echo $goods_in_end_month_name; ?></td>
            <td><?php echo $row['goods_in_end_year']; ?></td>
            <td><?php echo $backlog_month_name; ?></td>
            <td><?php echo $row['backlog_year']; ?></td>
            <td><?php echo $backlog_end_month_name; ?></td>
            <td><?php echo $row['backlog_end_year']; ?></td>
        </tr>
        
        <?php } ?>
    </tbody>
</table>

</body>
</html>
