<?php

/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
class OrderController extends Controller {
    
    public function init() {
        parent::init();
        $this->initAjaxCsrfToken();
    }
 
    // this function will work to post csrf token.
    protected function initAjaxCsrfToken() {
 
       Yii::app()->clientScript->registerScript('AjaxCsrfToken', ' $.ajaxSetup({
                         data: {"YII_CSRF_TOKEN": "' . Yii::app()->request->csrfToken . '"},
                         cache:false
                    });', CClientScript::POS_HEAD);
    }

    public function accessRules() {
        return array(
            array('allow', // allow all users to perform 'index' and 'view' actions
                'actions' => array('login', 'error', 'logout'),
                'users' => array('*'),
            ),
            array('allow', // allow authenticated user to perform 'create' and 'update' actions
                'actions' => array('Index', 'DownloadOrderFormat','DownloadOrderFormatExcel', 'GetList', 'Save','View','Edit','SaveEdittedOrder','Delete','UploadOrder','SaveUpload','UploadDocuments'
                ,'ConsolidatedOrder','GetConsolidatedOrderList','AddConsolidatedOrder','SaveConsolidatedOrder','DownloadConsolidatedOrder','DownloadUploadedConsolidatedOrder','deleteconsolOrder',
                'GetviewList','GetOrderdetails','DownloadUploadedOrderQty'),
                'expression' => '$user->isMember()',
            ),
            array('deny', // deny all users
                'users' => array('*'),
            ),
        );
    }

    public function actionIndex() {
        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $this->render('index');
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }

    // Uncomment the following methods and override them if needed
    public function filters() {
        return array(
            'accessControl', // perform access control for CRUD operations
        );
    }

    public function actionGetList() {
        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if(count($_POST) > 0){
            $model = new Order();
            $draw = $_POST['draw'];
            $offset = $_POST['start'];
            $limit = $_POST['length'];
            $distributer_id = $_POST['distributer_id'];
            $year = $_POST['year'];
            $month = $_POST['month'];
            $columns = array(
                // datatable column index  => database column name
                0 => 't.id',
                1 => 'order_date',
                2 => 'distributer_det.distributer_name',
                3 => 'month',
                4 => 'year',
            );
            $filterColumn = $columns[$_POST['order'][0]['column']];
            $filterOrder = $_POST['order'][0]['dir'];
            if($_POST['order'][1]['column']>0){
                $filterColumn .= ' , '.$columns[$_POST['order'][1]['column']];
            }
            $orderBy = $filterColumn . " " . $filterOrder;
            $condition = '1=1';
            if($distributer_id>0){
                $condition.=' AND distributer_id='. $distributer_id;
                
            }
            if($month!=''){
                $condition.=' AND month='. $month;
                
            }
            if($year>0){
                $condition.=' AND year='. $year;
                
            }
            $arr_months=Order::getmonths();
            if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
                $searchString = $_POST['search']['value'];
                $condition .= " and (distributer_det.distributer_name LIKE '%" . $searchString . "%' 
                         OR month LIKE '%" . $searchString . "%' OR year LIKE '%" . $searchString . "%')";
            }
            $count = $model->with('distributer_det')->count(array('condition' => $condition));
            $model = $model->with('distributer_det')->findAll(array('condition' => $condition, 'limit' => $limit, 'offset' => $offset, 'order' => $orderBy));
            $recordsTotal = $count;
            $recordsFiltered = $count;
            $data['draw'] = $draw;
            $data['recordsTotal'] = $recordsTotal;
            $data['recordsFiltered'] = $recordsFiltered;
            $rowData = array();
            $m = 1;
            foreach ($model as $row) {
                $div = '';
                if (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["orders_view"])) {
                     $div .=   ' <button class="btn btn-primary btn-xs" title="View Order" onclick="vieworder('.$row[id].');"><i class="fa fa-eye"></i></button>';
                }
                if($row['is_locked']=='N'){
                    $model_his = SupplyConfirmation::model()->find(array('condition'=>'month = "'.$row['month'].'" and year = "'.$row['year'].'"'));
                    if (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["orders_edit"])) {
                        if(empty($model_his))
                        {
                            $div .=   ' <button class="btn btn-primary btn-xs" title="Edit Order" onclick="loadPopupModal(\'order/edit&id='.$row['id'].'\',\'Edit Order\',\'xl\')"><i class="fa fa-pencil"></i></button> ';
                        }
                    }
                    if (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["orders_delete"])) {
                        if(empty($model_his))
                        {
                            $div .=   ' <button class="btn btn-primary btn-xs" title="Delte Order" onclick="deleteOrder('.$row['id'].')"><i class="fa fa-trash"></i></button> ';
                        }
                    }
                }
                if (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["orders_download"])) { 
                    $rowdistributer_id=$row["distributer_id"];$rowyear=$row["year"];$rowmonth=$row["month"];
                $div .=   '<button type="button"  class="btn btn-xs btn-primary" title="Download Quantity" onclick="downloadUploadorderQty('.$row[id].');" title="Uploaded"><i class="fa fa-download"></i></button>';
                $div .=   '<button type="button"  class="btn btn-xs btn-primary" title="Download Excel" onclick="window.open($(\'#liveSite\').val()+\'index.php?r=order/DownloadOrderFormatExcel&distributer_id='.$rowdistributer_id.'&year='.$rowyear.'&month='.$rowmonth.'\', \'_blank\');" title="Uploaded"><i class="fa fa-file-excel-o"></i></button>';
                //$div .=   '<button type="button"  class="btn btn-xs btn-primary" title="Download Amount" onclick="downloadUploadorderAmt('.$row[id].');" title="Uploaded"><i class="fa fa-download"></i></button>';
                      //}
                }     
                $rowData[] = array($m,
                    date('d/m/Y',strtotime($row['order_date'])),$row['distributer_det']['distributer_name'],$arr_months[(int)$row['month']],$row['year'],
                    '<div class="btn-wrap text-center">' . $div . '</div>');
    
                $m++;
            }
            $data['data'] = $rowData;
            echo json_encode($data);
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }

    public function actionUploadOrder() {
        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $model = new Order();
            Yii::app()->clientScript->scriptMap = array('*jquery.js' => false, '*jquery.min.js' => false, 'jquery.min.js' => false, '*jquery-ui.js' => false, '*jquery-ui.min.js' => false, 'jquery-2.1.4.js' => false);
            $this->render('upload_form', array('model' => $model), false, false);
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }
    
    public function actionDownloadOrderFormat(){
        
        ini_set("memory_limit", "-1");
        ini_set('max_execution_time', "-1");
        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $distributer_id = Yii::app()->request->getParam('distributer_id');
            $year = Yii::app()->request->getParam('year');
            $month = Yii::app()->request->getParam('month');
            $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
            ]);
            
            $file = fopen("upload/test.csv", "w");
            $j = 0;
            $mod_dis = Distributers::model()->with('region_det')->findByPk((int)$distributer_id);
            $region_id= (int)$mod_dis->region_id;
            $arr_months=Order::getmonths();
            $list[$j++] = 'ORDER REVIEW FORM QTY';
            $jan='';$feb='';$mar='';$apr='';$may='';$jun='';$jul='';$aug='';$sep='';$oct='';$nov='';$dec='';
            if($month<=3){
              $year =$year-1;
            }
            if((int)$month>0){
                switch((int)$month){
                    case 1:
                        $jan='Type Here||';
                        break;
                    case 2:
                        $feb='Type Here||';
                        break;
                    case 3:
                        $mar='Type Here||';
                        break;
                    case 4:
                        $apr='Type Here||';
                        break;
                    case 5:
                        $may='Type Here||';
                        break;
                    case 6:
                        $jun='Type Here||';
                        break;
                    case 7:
                        $jul='Type Here||';
                        break;
                    case 8:
                        $aug='Type Here||';
                        break;
                    case 9:
                        $sep='Type Here||';
                        break;
                    case 10:
                        $oct='Type Here||';
                        break;
                    case 11:
                        $nov='Type Here||';
                        break;
                    case 12:
                        $dec='Type Here||';
                        break;
                        
                }
            }
            $list[$j++] = 'Distributor!!'.$mod_dis->distributer_name.'!!!!!'.$apr.'!!!!!'.$may.'!!!!!'.$jun.'!!!!!'.'!!!!!'.$jul.'!!!!!'.$aug.'!!!!!'.$sep.'!!!!!'.'!!!!!'.$oct.'!!!!!'.$nov.'!!!!!'.$dec.'!!!!!'.'!!!!!'.$jan.'!!!!!'.$feb.'!!!!!'.$mar ;
            $year1 =(int)$year+1;
            $list[$j++] = '!PRODUCT CATEGORY!GMC!Model!'.$mod_dis->region_det->region_name.'!CBM!BASE CODE!01-04-'.$year.'!!!!!01-05-'.$year.'!!!!!01-06-'.$year.'!!!!!1Q TOTAL!!!!!01-07-'.$year.'!!!!!01-08-'.$year.'!!!!!01-09-'.$year.'!!!!!2Q TOTAL!!!!!01-10-'.$year.'!!!!!01-11-'.$year.'!!!!!01-12-'.$year.'!!!!!3Q TOTAL!!!!!01-01-'.$year1.'!!!!!01-02-'.$year1.'!!!!!01-03-'.$year1.'!!!!!4Q TOTAL!!!!!TOTAL'.$year.'-'.$year1;
            
            $list[$j++] = '!!!!!!!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM';
            foreach ($model_inventory as $i => $row) {
                $apr_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="4" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $may_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="5" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jun_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="6" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jul_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="7" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $aug_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="8" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $sep_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="9" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $oct_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="10" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $nov_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="11" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $dec_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="12" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jan_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="1" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                $feb_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="2" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                $mar_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="3" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                /*ordered*/
                $qty_apr=(int)$apr_model->quantity_ordered;
                $qty_may=(int)$may_model->quantity_ordered;
                $qty_jun=(int)$jun_model->quantity_ordered;
                $qty_jul=(int)$jul_model->quantity_ordered;
                $qty_aug=(int)$aug_model->quantity_ordered;
                $qty_sep=(int)$sep_model->quantity_ordered;
                $qty_oct=(int)$oct_model->quantity_ordered;
                $qty_nov=(int)$nov_model->quantity_ordered;
                $qty_dec=(int)$dec_model->quantity_ordered;
                $qty_jan=(int)$jan_model->quantity_ordered;
                $qty_feb=(int)$feb_model->quantity_ordered;
                $qty_mar=(int)$mar_model->quantity_ordered;
                $qty_q1=$qty_apr+$qty_may+$qty_jun;
                $qty_q2=$qty_jul+$qty_aug+$qty_sep;
                $qty_q3=$qty_oct+$qty_nov+$qty_dec;
                $qty_q4=$qty_jan+$qty_feb+$qty_mar;
                $qty_tot=$qty_q1+$qty_q2+$qty_q3+$qty_q4;
                /*supplyconfirmation*/
                $supplyqty_apr=(int)$apr_model->quantity_confirmed;
                $supplyqty_may=(int)$may_model->quantity_confirmed;
                $supplyqty_jun=(int)$jun_model->quantity_confirmed;
                $supplyqty_jul=(int)$jul_model->quantity_confirmed;
                $supplyqty_aug=(int)$aug_model->quantity_confirmed;
                $supplyqty_sep=(int)$sep_model->quantity_confirmed;
                $supplyqty_oct=(int)$oct_model->quantity_confirmed;
                $supplyqty_nov=(int)$nov_model->quantity_confirmed;
                $supplyqty_dec=(int)$dec_model->quantity_confirmed;
                $supplyqty_jan=(int)$jan_model->quantity_confirmed;
                $supplyqty_feb=(int)$feb_model->quantity_confirmed;
                $supplyqty_mar=(int)$mar_model->quantity_confirmed;
                $supplyqty_q1=$supplyqty_apr+$supplyqty_may+$supplyqty_jun;
                $supplyqty_q2=$supplyqty_jul+$supplyqty_aug+$supplyqty_sep;
                $supplyqty_q3=$supplyqty_oct+$supplyqty_nov+$supplyqty_dec;
                $supplyqty_q4=$supplyqty_jan+$supplyqty_feb+$supplyqty_mar;
                $supplyqty_tot=$supplyqty_q1+$supplyqty_q2+$supplyqty_q3+$supplyqty_q4;
                /*supplyconfirmation*/
                /*supplycbm*/
                $supplycbmqty_apr=$supplyqty_apr*$row['cbm'];
                $supplycbmqty_may=$supplyqty_may*$row['cbm'];
                $supplycbmqty_jun=$supplyqty_jun*$row['cbm'];
                $supplycbmqty_jul=$supplyqty_jul*$row['cbm'];
                $supplycbmqty_aug=$supplyqty_aug*$row['cbm'];
                $supplycbmqty_sep=$supplyqty_sep*$row['cbm'];
                $supplycbmqty_oct=$supplyqty_oct*$row['cbm'];
                $supplycbmqty_nov=$supplyqty_nov*$row['cbm'];
                $supplycbmqty_dec=$supplyqty_dec*$row['cbm'];
                $supplycbmqty_jan=$supplyqty_jan*$row['cbm'];
                $supplycbmqty_feb=$supplyqty_feb*$row['cbm'];
                $supplycbmqty_mar=$supplyqty_mar*$row['cbm'];
                $supplycbmqty_q1=$supplycbmqty_apr+$supplycbmqty_may+$supplycbmqty_jun;
                $supplycbmqty_q2=$supplycbmqty_jul+$supplycbmqty_aug+$supplycbmqty_sep;
                $supplycbmqty_q3=$supplycbmqty_oct+$supplycbmqty_nov+$supplycbmqty_dec;
                $supplycbmqty_q4=$supplycbmqty_jan+$supplycbmqty_feb+$supplycbmqty_mar;
                $supplycbmqty_tot=$supplycbmqty_q1+$supplycbmqty_q2+$supplycbmqty_q3+$supplycbmqty_q4;
                /*supplycbm*/
                /*shipped cbm*/
                $shp_apr=(int)$apr_model->quantity_shipped;
                $shp_may=(int)$may_model->quantity_shipped;
                $shp_jun=(int)$jun_model->quantity_shipped;
                $shp_jul=(int)$jul_model->quantity_shipped;
                $shp_aug=(int)$aug_model->quantity_shipped;
                $shp_sep=(int)$sep_model->quantity_shipped;
                $shp_oct=(int)$oct_model->quantity_shipped;
                $shp_nov=(int)$nov_model->quantity_shipped;
                $shp_dec=(int)$dec_model->quantity_shipped;
                $shp_jan=(int)$jan_model->quantity_shipped;
                $shp_feb=(int)$feb_model->quantity_shipped;
                $shp_mar=(int)$mar_model->quantity_shipped;
                /*shipped cbm*/
                /*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*/
                
                
                
                $list[$j++] = '!'.$row['category_det']['product_category']. '!' . $row['gmc_code'] . '!' . $row['model_no']
                        . '!' . (float)$mod_inv_price['price'] . '!' . $row['cbm'] . '!' . $row['base_code'] 
                        . '!'.$qty_apr.'!'.$supplyqty_apr.'!'.$shp_apr.'!'.($supplyqty_apr-$shp_apr).'!'.$supplycbmqty_apr. '!'.$qty_may.'!'.$supplyqty_may.'!'.$shp_may.'!0!'.$supplycbmqty_may. '!'.$qty_jun.'!'.$supplyqty_jun.'!'.$shp_jun.'!0!'.$supplycbmqty_jun. '!'.$qty_q1.'!'.$supplyqty_q1.'!0!0!'.$supplycbmqty_q1
                        . '!'.$qty_jul.'!'.$supplyqty_jul.'!'.$shp_jul.'!0!'.$supplycbmqty_jul. '!'.$qty_aug.'!'.$supplyqty_aug.'!'.$shp_aug.'!0!'.$supplycbmqty_aug. '!'.$qty_sep.'!'.$supplyqty_sep.'!'.$shp_sep.'!0!'.$supplycbmqty_sep. '!'.$qty_q2.'!'.$supplyqty_q2.'!0!0!'.$supplycbmqty_q2
                        . '!'.$qty_oct.'!'.$supplyqty_oct.'!'.$shp_oct.'!0!'.$supplycbmqty_oct. '!'.$qty_nov.'!'.$supplyqty_nov.'!'.$shp_nov.'!0!'.$supplycbmqty_nov. '!'.$qty_dec.'!'.$supplyqty_dec.'!'.$shp_dec.'!0!'.$supplycbmqty_dec. '!'.$qty_q3.'!'.$supplyqty_q3.'!0!0!'.$supplycbmqty_q3
                        . '!'.$qty_jan.'!'.$supplyqty_jan.'!'.$shp_jan.'!0!0'. '!'.$qty_feb.'!'.$supplyqty_feb.'!'.$shp_feb.'!0!0'. '!'.$qty_mar.'!'.$supplyqty_mar.'!'.$shp_mar.'!0!0'. '!'.$qty_q4.'!'.$supplyqty_q4.'!0!0!'.$supplycbmqty_q4
                        . '!'.$qty_tot.'!'.$supplyqty_tot.'!0!0!'.$supplycbmqty_tot;
            }
            $j++;
            foreach ($list as $line) {
                fputcsv($file, explode('!', $line));
            }
            fclose($file);
            $filename = 'upload/test.csv';
            $path = Yii::app()->getBaseUrl(true) . '/upload';
            header('Content-Type: application/csv');
            $file_name='Order_'.$mod_dis->distributer_name.'_'.$year.'.csv';
            header('Content-disposition: attachment; filename="'.$file_name.'"'); 
            readfile($filename);
            Yii::app()->end();
        }else{
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionDownloadOrderFormatExcel(){
        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $distributer_id = Yii::app()->request->getParam('distributer_id');
            $year = Yii::app()->request->getParam('year');
            $month = Yii::app()->request->getParam('month');
            $mod_dis = Distributers::model()->findByPk((int)$distributer_id);
            $file_name='Order_'.$mod_dis->distributer_name.'_'.$year.'.xls';
            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=\"" . $file_name . "\"");
            $this->renderpartial('application.views.front.order.order_download_format_excel', array('distributer_id' => $distributer_id,
                'year' => $year,'month'=>$month), false, true);
            Yii::app()->end();
            return;
        }else{
            throw new CHttpException(404, 'Page not found.');
        }
    }
    
    public function actionSaveUpload(){
        
        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if(count($_POST) > 0){
            $model = new Order();$other=array();
            if (isset($_POST['Order'])) {
                if ((int) @$_POST['Order']['id'] > 0) {
                    $id = (int) @$_POST['Order']['id'];
                    $model = $model->findByPk($id);
                }else{
                    $model->created_by =Yii::app()->user->id;
                    $model->created_date_time =date('Y-m-d H:i:s');
                }
                $model->updated_by =Yii::app()->user->id;
                $model->updated_date_time =date('Y-m-d H:i:s');
                $model->attributes = $_POST['Order'];
                $model->order_date = date('Y-m-d');
                $file= $_POST['Order']['file_name'];
                $model->file_name =$file;
                $valid= $model->validate();
                $i=0;$arr_products=[];
                if($file!=''){
                    if (($handle = fopen("upload/orders/".$file, "r")) !== FALSE) {
                        $n = 1;
                        $arr_months=Order::getmonths();
                        $mod_his = Order::model()->find(array('condition'=>'distributer_id='.(int)$_POST['Order']['distributer_id'].' and month = "'.$_POST['Order']['month'].'" and year = "'.$_POST['Order']['year'].'"'));
                        if(!empty($mod_his)){
                            $other[]='Already added Data';
                            $valid = false;
                        }
                        while (($row = fgetcsv($handle)) !== FALSE) {
                            if ($n == 2) {
                                $supplier_name = $row[2];
                                $distributer_model = Distributers::model()->with('region_det')->find(array('condition'=>'distributer_name="'.$supplier_name.'"'));
                                if($distributer_model->id!=$_POST['Order']['distributer_id']){
                                    $other[]='Distributor Not Matching with Selected One';
                                    $valid = false;
                                }
                                $region_id =(int)$distributer_model->region_id;
                                
                            }
                            if ($n > 4) {
                                $product_category= trim($row[1],'\'"'); //productcategory
                                $category_id = (int)MasProductCategory::model()->find(array('condition'=>'product_category="'.$product_category.'"'))->id;
                                if ($category_id==0) {
                                    $other[] = ' Category '.$product_category.' not added';
                                    $valid = false;
                                }
                                $gmc_code= trim($row[2]); //gmc
                                $model_no= trim($row[3]); //model
                                /*
                                1-4-21 = 7
                                1-5-21 = 12
                                1-6-21 = 17
                                
                                1Qtotal= 22
                                
                                1-7-21 = 27
                                1-8-21 = 32
                                1-9-21 = 37
                                
                                2Q total=42
                                
                                1-10-21 = 47
                                1-11-21 = 52
                                1-12-21 = 57
                                
                                3Q total =62
                                
                                1-1-22 =67
                                1-2-22 = 72
                                1-3-22 = 77
                                4Q    =  82
                                Total  = 87
                                */
                                switch((int)$_POST['Order']['month']){
                                    case '1':
                                        $quantity_ordered=(int)$row[67];
                                    break;
                                    case '2':
                                        $quantity_ordered=(int)$row[72];
                                    break;
                                    case '3':
                                        $quantity_ordered=(int)$row[77];
                                    break;
                                    case '4':
                                        $quantity_ordered=(int)$row[7];
                                    break;
                                    case '5':
                                        $quantity_ordered=(int)$row[12];
                                    break;
                                    case '6':
                                        $quantity_ordered=(int)$row[17];
                                    break;
                                    case '7':
                                        $quantity_ordered=(int)$row[27];
                                    break;
                                    case '8':
                                        $quantity_ordered=(int)$row[32];
                                    break;
                                    case '9':
                                        $quantity_ordered=(int)$row[37];
                                    break;
                                    case '10':
                                        $quantity_ordered=(int)$row[47];
                                    break;
                                    case '11':
                                        $quantity_ordered=(int)$row[52];
                                    break;
                                    case '12':
                                        $quantity_ordered=(int)$row[57];
                                    break;
                                }
                                if($quantity_ordered<0){
                                    $other[] = 'GMC with code '.$gmc_code.' and model '.$model_no.' entered negative qty '.$quantity_ordered;
                                    $valid = false;
                                }else{
                                    //AND (is_active="Y" OR (is_active="D" AND ((discontinuing_month<='.$_POST['Order']['month'].' AND discontinuing_year='.$_POST['Order']['year'].') OR discontinuing_year<'.$_POST['Order']['year'].') 
                                    $cnd = 'gmc_code="'.$gmc_code.'" AND (is_active="Y" OR (is_active="D" AND ((discontinuing_month>='.(int)$_POST['Order']['month'].' AND discontinuing_year='.(int)$_POST['Order']['year'].') OR discontinuing_year>'.(int)$_POST['Order']['year'].')))'; 
                                    $mod_inv = Inventory::model()->find(array('condition'=>$cnd));
                                    $price_ordered=0;
                                    if(!empty($mod_inv)){
                                        /*region value*/
                                        $price_date = (int)$_POST['Order']['year'].'-'.(int)$_POST['Order']['month'].'-'.'01';
                                        $price_cnd = 'product_id='.(int)$mod_inv->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));
                                        $price_ordered=(float)$mod_inv_price->price;
                                        /*region value*/
                                    
                                        $modelItems[$i] = new OrderDetails();
                                        $modelItems[$i]->order_id= 0 ;
                                        $modelItems[$i]->category_id= (int)$category_id ;
                                        $modelItems[$i]->product_id= $mod_inv->id ;
                                        $modelItems[$i]->model = $model_no;
                                        $modelItems[$i]->base_code = $row[5] ;
                                        $modelItems[$i]->quantity_ordered = $quantity_ordered;
                                        $modelItems[$i]->price = $price_ordered;
                                        $modelItems[$i]->region_id = (int)$region_id;
                                        $modelItems[$i]->order_date =$price_date;
                                        if($distributer_model->region_det->currency_type == 'USD'){
                                            $modelItems[$i]->exchange_rate = 1;
                                        }else{
                                            $mod_settings = MasSettings::model()->find();
                                            $currency_rate = $mod_settings->currency_rate;
                                            $modelItems[$i]->exchange_rate = (1/$currency_rate);
                                        }
                                        $modelItems[$i]->currency_type = $distributer_model->region_det->currency_type;
                                        $modelItems[$i]->created_by = Yii::app()->user->id;
                                        $modelItems[$i]->created_date_time =date('Y-m-d H:i:s');
                                        $modelItems[$i]->updated_by = Yii::app()->user->id;
                                        $modelItems[$i]->updated_date_time =date('Y-m-d H:i:s');
                                        $valid = $modelItems[$i]->validate()&&$valid;
                                        $i++;
                                        if (in_array($mod_inv->id,$arr_products)) {
                                            $other[] = $gmc_code. ' GMC Repeated ';
                                            $valid = false;
                                        }else{
                                            $arr_products[]=$mod_inv->id;
                                        }
                                    }else{
                                        $cnd = 'gmc_code="'.$gmc_code.'" AND is_active="D"'; 
                                        $mod_inv = Inventory::model()->find(array('condition'=>$cnd));
                                        if(!empty($mod_inv)){
                                            $other[] = 'GMC with code '.$gmc_code.' and model '.$model_no.' Discontinued';
                                        }else{
                                            $other[] = 'GMC with code '.$gmc_code.' and model '.$model_no.' not exits';
                                        }
                                        $valid = false;
                                    }
                                }
                            }
                            $n++;
                            
                        }
                        fclose($handle);
                    }
                }else{
                    $other[]='File Not Uploaded';
                    $valid = false;
                }
                if ($valid) {
                    $model->save();
                    if(!empty($modelItems)){
                        foreach($modelItems as $each){
                            $each->order_id= $model->id;
                            $each->save(false);
                        }
                    }
                    $jsonData['status'] = 'success';
                    $jsonData['frm'] = $_POST['Order'];
                    $jsonData['id'] = $model->id;
                    $jsonData['updated_flag'] = OrderDetails::updateShippingConfirmation((int)$_POST['Order']['month'],(int)$_POST['Order']['year']);
                    
                    echo CJSON::encode($jsonData);
                } else {
                    $error1 = json_decode(CActiveForm::validate($model, '', false), true);
                    if(!empty($modelItems)){
                        $error2 = json_decode(CActiveForm::validateTabular($modelItems, '', false), true);
                    }else{
                        $error2[] = "Please add atleast one line item";
                    }
                    $error = CJSON::encode(array_merge_recursive($error1,$error2, $other));
                    if ($error != '[]')
                        echo $error;
                }
            }
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
        
    }
    public function actionView() {
         if(count($_GET) > 0){
            $model = new Order();
            $id = (int) Yii::app()->request->getParam('id');
            if ($id > 0) {
                $model = $model->findByPk($id);
            }
           $this->render('view_form', array('model' => $model));  
         }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionEdit() {
         $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $model = new Order();
            $modelItems= array(new OrderDetails());
            $id = (int) Yii::app()->request->getParam('id');
            if ($id > 0) {
                $model = $model->findByPk($id);
                $modelItems=OrderDetails::model()->with('category_det','product_det')->findAll(array('condition'=>'order_id='.$model->id));
            }
            Yii::app()->clientScript->scriptMap = array('*jquery.js' => false, '*jquery.min.js' => false, 'jquery.min.js' => false, '*jquery-ui.js' => false, '*jquery-ui.min.js' => false, 'jquery-2.1.4.js' => false);
            $this->render('edit_form', array('model' => $model,'modelItems'=>$modelItems), false, false);
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }
    
    public function actionSaveEdittedOrder(){
        
        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if(count($_POST) > 0){
            $model = new Order();$other=array();
            if (isset($_POST['Order'])) {
                if ((int) @$_POST['Order']['id'] > 0) {
                    $id = (int) @$_POST['Order']['id'];
                    $model = $model->findByPk($id);
                }else{
                    $model->created_by =Yii::app()->user->id;
                    $model->created_date_time =date('Y-m-d H:i:s');
                }
                $model->updated_by =Yii::app()->user->id;
                $model->updated_date_time =date('Y-m-d H:i:s');
                $model->attributes = $_POST['Order'];
                $valid = $model->validate();
                    $p=0;
                    if (is_array($_POST['OrderDetails'])) 
                    {
                      foreach ($_POST['OrderDetails'] as $i => $item) 
                        {
                            $modelItems[$p] = new OrderDetails;
                            if((int)$item['id']>0){
                                $modelItems[$p] = OrderDetails::model()->findByPk((int)$item['id']);
                            }else{
                                $modelItems[$p]->created_by = Yii::app()->user->id;
                                $modelItems[$p]->created_date_time = date("Y-m-d H:i:s");
                            }
                            $modelItems[$p]->attributes = $item;
                            $modelItems[$p]->order_id = 0;
                            $modelItems[$p]->updated_by = Yii::app()->user->id;
                            $modelItems[$p]->updated_date_time = date("Y-m-d H:i:s");
                            $valid = $modelItems[$p]->validate() && $valid;
                            $p++;
                        }
                    }
                
                
                
                
                if ($valid) {
                    
                    $model->save();
                    if(!empty($modelItems)){
                        foreach ($modelItems as $eachModel) {
                            $eachModel->order_id = $model->id;
                            $eachModel->save(false);
                        }
                    }
                    
                    
                    $jsonData['status'] = 'success';
                    $jsonData['id'] = $model->id;
                    $jsonData['updated_flag'] = OrderDetails::updateShippingConfirmation((int)$model->month,(int)$model->year);
                    echo CJSON::encode($jsonData);
                } else{
                $error1 = $error2 = array();
                $error1 = json_decode(CActiveForm::validate($model, '', false), true);
                if (!empty($modelItems))
                $error2 = json_decode(CActiveForm::validateTabular($modelItems, '', false), true);
                else
                $error2[] = "Please add atleast one data";
                $error = CJSON::encode(array_merge_recursive($error1, $error2,$other));
                if ($error != '[]')
                    echo $error;
                }
            }
        }else{
              throw new CHttpException(404, 'Page not found.');
        }
    } 
    public function actionGetviewList() {
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if (count($_POST) > 0) {
            $order_id = (int)$_POST['order_id'];
            $model = new OrderDetails();
            $draw = $_POST['draw'];
            $offset = $_POST['start'];
            $limit = $_POST['length'];
            $columns = array(
                // datatable column index  => database column name
                0 => 't.id',
                1 => 'category_det.product_category',
                2 => 'product_det.gmc_code',
                3 => 't.model',
                4 => 't.base_code',
                5 => 't.quantity_ordered'
            );
            $filterColumn = $columns[$_POST['order'][0]['column']];
            $filterOrder = $_POST['order'][0]['dir'];
            $orderBy = $filterColumn . " " . $filterOrder;
            $condition = 'order_id='.$order_id;
            if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
                $searchString = $_POST['search']['value'];
                $condition .= " and (category_det.product_category LIKE '%" . $searchString . "%' 
                         OR product_det.gmc_code LIKE '%" . $searchString . "%' OR t.model LIKE '%" . $searchString . "%' 
                         OR t.base_code LIKE '%" . $searchString . "%' OR t.quantity_ordered LIKE '%" . $searchString . "%')";
            }
            $count = $model->with('category_det','product_det')->count(array('condition' => $condition));
            $model = $model->with('category_det','product_det')->findAll(array('condition' => $condition, 'limit' => $limit, 'offset' => $offset, 'order' => $orderBy));
            $recordsTotal = $count;
            $recordsFiltered = $count;
            $data['draw'] = $draw;
            $data['recordsTotal'] = $recordsTotal;
            $data['recordsFiltered'] = $recordsFiltered;
            $rowData = array();
            $m = 1;
            foreach ($model as $row) {
                $rowData[] = array($m,
                    $row['category_det']['product_category'], $row['product_det']['gmc_code'], $row['model'], $row->product_det->base_code, $row['quantity_ordered']);
                $m++;
            }
            $data['data'] = $rowData;
            echo json_encode($data);
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionDelete(){
        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if(count($_POST)>0){
            $id = $_POST['id'];
            if($id>0){
                $model = new Order();
                $model = $model->findByPk((int)$id);
                $month=(int)$model->month;
                $year=(int)$model->year;
                $model->deleteByPk((int)$id);
                $modelItems= new OrderDetails();
                $modelItems->deleteAll('order_id=:refer_id', array(':refer_id' => $id));
                $flag = OrderDetails::updateShippingConfirmation((int)$month,(int)$year);
                echo "success";
            } else {
                echo "fail";
            }
            
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
     
    }  
    public function actionConsolidatedOrder() {
        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $this->render('consolidated_orders');
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionGetConsolidatedOrderList() {
        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if(count($_POST) > 0){
            $model = new ConsolidatedOrder();
            $draw = $_POST['draw'];
            $offset = $_POST['start'];
            $limit = $_POST['length'];
            $year = $_POST['year'];
            $month = $_POST['month'];
            $columns = array(
                // datatable column index  => database column name
                0 => 't.id',
                1 => 'added_date',
                2 => 'month',
                4 => 'year',
            );
            $filterColumn = $columns[$_POST['order'][0]['column']];
            $filterOrder = $_POST['order'][0]['dir'];
            $orderBy = $filterColumn . " " . $filterOrder;
            $condition = '1=1';
            if($month!=''){
                $condition.=' AND month='. $month;
                
            }
            if($year>0){
                $condition.=' AND year='. $year;
                
            }
            $arr_months=Order::getmonths();
            if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
                $searchString = $_POST['search']['value'];
                $condition .= " and (added_date LIKE '%" . $searchString . "%' 
                         OR month LIKE '%" . $searchString . "%' OR year LIKE '%" . $searchString . "%')";
            }
            $count = $model->count(array('condition' => $condition));
            $model = $model->findAll(array('condition' => $condition, 'limit' => $limit, 'offset' => $offset, 'order' => $orderBy));
            $recordsTotal = $count;
            $recordsFiltered = $count;
            $data['draw'] = $draw;
            $data['recordsTotal'] = $recordsTotal;
            $data['recordsFiltered'] = $recordsFiltered;
            $rowData = array();
            $m = 1;
            foreach ($model as $row) {
                $div = '';
                if (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["consolidatedorders_download"])) {
                     //$div .=   '<button type="button"  class="btn btn-sm btn-theme" onclick="downloadconsolidatedorder('.$row[id].');" title="ALL"><i class="fa fa-download"></i></button>';
                     $div .=   '<button type="button"  class="btn btn-xs btn-primary" onclick="downloadUploadedconsolidatedorder('.$row[id].');" title="Uploaded"><i class="fa fa-download"></i></button>';
                }
                if (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["consolidatedorders_delete"])) { 
                     if($row['is_locked']!="Y"){
                        $model_his = SupplyConfirmation::model()->find(array('condition'=>'month = "'.$row['month'].'" and year = "'.$row['year'].'"'));
                        if(empty($model_his))
                        {
                            $div .=   '&nbsp<button type="button"  class="btn btn-xs btn-primary" onclick="deleteconOrder('.$row['id'].')"><i class="fa fa-trash"></i></button>';
                        }
                    }
                }
           
                $rowData[] = array($m,
                    date('d/m/Y',strtotime($row['added_date'])),$arr_months[$row['month']],$row['year'],
                    '<div class="btn-wrap text-center">' . $div . '</div>');
    
                $m++;
            }
            $data['data'] = $rowData;
            echo json_encode($data);
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionAddConsolidatedOrder() {
        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $model = new ConsolidatedOrder();
            $id = (int) Yii::app()->request->getParam('id');
            if ($id > 0) {
                $model = $model->findByPk($id);
            }
            Yii::app()->clientScript->scriptMap = array('*jquery.js' => false, '*jquery.min.js' => false, 'jquery.min.js' => false, '*jquery-ui.js' => false, '*jquery-ui.min.js' => false, 'jquery-2.1.4.js' => false);
            $this->render('add_consolidated_order', array('model' => $model), false, false);
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionSaveConsolidatedOrder(){
        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if(count($_POST) > 0){
            $model = new ConsolidatedOrder();$other=array();
            if (isset($_POST['ConsolidatedOrder'])) {
                if ((int) @$_POST['ConsolidatedOrder']['id'] > 0) {
                    $id = (int) @$_POST['ConsolidatedOrder']['id'];
                    $model = $model->findByPk($id);
                }else{
                    $model->created_by =Yii::app()->user->id;
                    $model->created_date_time =date('Y-m-d H:i:s');
                }
                $model->updated_by =Yii::app()->user->id;
                $model->updated_date_time =date('Y-m-d H:i:s');
                $model->attributes = $_POST['ConsolidatedOrder'];
                $model->added_date =date('Y-m-d');
                $valid= $model->validate();
                $mod_his = ConsolidatedOrder::model()->find(array('condition'=>'month = "'.$_POST['ConsolidatedOrder']['month'].'" and year = "'.$_POST['ConsolidatedOrder']['year'].'"'));
                if(!empty($mod_his)){
                    $other[]='Already added Data';
                    $valid = false;
                }
                if($valid){
                    $model->save();
                    $jsonData['status'] = 'success';
                    $jsonData['frm'] = $_POST['ConsolidatedOrder'];
                    $jsonData['id'] = $model->id;
                    echo CJSON::encode($jsonData);
                } else {
                    $error1 = json_decode(CActiveForm::validate($model, '', false), true);
                    $error = CJSON::encode(array_merge_recursive($error1, $other));
                    if ($error != '[]')
                        echo $error;
                }
            }
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }
    
    public function actionDownloadConsolidatedOrder(){
        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $id = Yii::app()->request->getParam('id');
            $model= new ConsolidatedOrder();
            $model= $model->findByPK((int)$id);
            $month= $model->month;
            $year = $model->year;
            $model_inventory = Inventory::model()->findAll();
            $file = fopen("upload/test.csv", "w");
            $j = 0;
            $mod_dis = Distributers::model()->findAll(array('condition'=>'isActive="Y"','order'=>'id asc'));
            $arr_months=Order::getmonths();
            $list[$j++] = 'Year!'.$year;
            $list[$j++] = 'Month!'.$arr_months[$month];
            $thirdrow = 'PRODUCT CATEGORY!GMC!Model!YMGF FOB!CBM!BASE CODE';
            foreach($mod_dis as $eachdis){
                $thirdrow .='!'.$eachdis->distributer_name ;
            }
            $thirdrow.='! Total';
            $list[$j++] = $thirdrow ;
            foreach ($model_inventory as $i => $row) {
                $inv_row=$row['category_det']['product_category']. '!' . $row['gmc_code'] . '!' . $row['model_no']
                        . '!' . $row['ymgf_fob'] . '!' . $row['cbm'] . '!' . $row['base_code'] ;
                $ind_sum=0;
                foreach($mod_dis as $eachdis){
                    $ordersum = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis->id.' AND product_id='.(int)$row['id']))->quantity_ordered;
                    $ind_sum+=(int)$ordersum;
                    $inv_row .='!'.(int)$ordersum;
                }
                $inv_row .='!'.$ind_sum;
                $list[$j++] = $inv_row;
            }
            $tot_row ='!!!!!';
            foreach($mod_dis as $eachdis){
                $tot_ordersum = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered','condition'=> 'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis->id))->quantity_ordered;
                $totind_sum+=(int)$tot_ordersum;
                $tot_row .='!'.(int)$tot_ordersum;
            }
            $tot_row .='!'.$totind_sum;
            $list[$j++] = $tot_row;
            $list[$j++] ='';
            $list[$j++] ='';
            $list[$j++] ='CATEGORY!!!!!!';
            $modelmascategories = MasProductCategory::model()->findAll(array('order'=>'sort_order ASC'));
            $totcat_sum=0;
            foreach($modelmascategories as $eachcat){
                $cinv_row=$eachcat->product_category.'!!!!!';
                $cind_sum=0;
                foreach($mod_dis as $eachdis){
                    $cordersum = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis->id.' AND category_id='.(int)$eachcat['id']))->quantity_ordered;
                    $cind_sum+=(int)$cordersum;
                    $cinv_row .='!'.(int)$cordersum;
                }
                $cinv_row .='!'.$cind_sum;
                $list[$j++] = $cinv_row;
            }
            $ctot_row ='Total!!!!!';
            foreach($mod_dis as $eachdis){
                $ctot_ordersum = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered','condition'=> 'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis->id))->quantity_ordered;
                $ctotind_sum+=(int)$ctot_ordersum;
                $ctot_row .='!'.(int)$ctot_ordersum;
            }
            $ctot_row .='!'.$ctotind_sum;
            $list[$j++] =$ctot_row;
            $list[$j++] ='';
            $list[$j++] ='CATEGORY Amount!!!!!!';
            $modelmascategories = MasProductCategory::model()->findAll(array('order'=>'sort_order ASC'));
            $totcat_sum=0;
            foreach($modelmascategories as $eachcat){
                $cinv_row=$eachcat->product_category.'!!!!!';
                $cind_sum=0;
                foreach($mod_dis as $eachdis){
                    $cordersum = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(price*quantity_ordered) AS quantity_ordered','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis->id.' AND category_id='.(int)$eachcat['id']))->quantity_ordered;
                    $cind_sum+=$cordersum;
                    $cinv_row .='!'.$cordersum;
                }
                $cinv_row .='!'.$cind_sum;
                $list[$j++] = $cinv_row;
            }
            $ctot_row ='Total!!!!!';
            foreach($mod_dis as $eachdis){
                $ctot_ordersum = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(price*quantity_ordered) AS quantity_ordered','condition'=> 'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis->id))->quantity_ordered;
                $ctotind_sum+=$ctot_ordersum;
                $ctot_row .='!'.$ctot_ordersum;
            }
            $ctot_row .='!'.$ctotind_sum;
            $list[$j++] =$ctot_row;
            $j++;
            foreach ($list as $line) {
                fputcsv($file, explode('!', $line));
            }
            fclose($file);
            $filename = 'upload/test.csv';
            $path = Yii::app()->getBaseUrl(true) . '/upload';
            header('Content-Type: application/csv');
            $file_name='consolidated_order_'.$month.'_'.$year.'.csv';
            header('Content-disposition: attachment; filename="'.$file_name.'"');
            readfile($filename);
            Yii::app()->end();
            return;
        }else{
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionDownloadUploadedConsolidatedOrder(){
        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $id = Yii::app()->request->getParam('id');
            $model= new ConsolidatedOrder();
            $model= $model->findByPK((int)$id);
            $month= $model->month;
            $year = $model->year;
            $model_inventory = Inventory::model()->findAll();
            $file = fopen("upload/test.csv", "w");
            $j = 0;
            $sql="SELECT distributer_id AS id,D.distributer_name FROM ymh_order t LEFT JOIN ymh_distributers D ON D.id=t.distributer_id WHERE month='".$month."' AND year='".$year."' ORDER BY t.id ASC";
            $mod_dis = Yii::app()->db->createCommand($sql)->queryAll();
            //$cnd = "isActive='Y' AND t.id IN (SELECT distributer_id FROM ymh_order WHERE month='".$month."' AND year='".$year." ORDER BY id ASC')";
            //$mod_dis = Distributers::model()->findAll(array('condition'=>$cnd));
            $arr_months=Order::getmonths();
            $list[$j++] = 'Year!'.$year;
            $list[$j++] = 'Month!'.$arr_months[$month];
            $thirdrow = 'PRODUCT CATEGORY!GMC!Model!YMGF FOB!CBM!BASE CODE';
            foreach($mod_dis as $eachdis){
                $thirdrow .='!'.$eachdis['distributer_name'] ;
            }
            $thirdrow.='! Total';
            $list[$j++] = $thirdrow ;
            foreach ($model_inventory as $i => $row) {
                $inv_row=$row['category_det']['product_category']. '!' . $row['gmc_code'] . '!' . $row['model_no']
                        . '!' . $row['ymgf_fob'] . '!' . $row['cbm'] . '!' . $row['base_code'] ;
                $ind_sum=0;
                foreach($mod_dis as $eachdis){
                    $ordersum = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis['id'].' AND product_id='.(int)$row['id'],'group'=>'parent_det.distributer_id'))->quantity_ordered;
                    $ind_sum+=(int)$ordersum;
                    $inv_row .='!'.(int)Yii::app()->numberFormatter->formatCurrency($ordersum,"");
                }
                $inv_row .='!'.$ind_sum;
                $list[$j++] = $inv_row;
            }
            $tot_row ='!!!!!';
            foreach($mod_dis as $eachdis){
                $tot_ordersum = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered','condition'=> 'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis['id'],'group'=>'parent_det.distributer_id'))->quantity_ordered;
                $totind_sum+=(int)$tot_ordersum;
                $tot_row .='!'.(int)Yii::app()->numberFormatter->formatCurrency($tot_ordersum,"");
            }
            $tot_row .='!'.$totind_sum;
            $list[$j++] = $tot_row;
            $list[$j++] ='';
            
            $list[$j++] ='CATEGORY!!!!!!';
            $modelmascategories = MasProductCategory::model()->findAll(array('order'=>'sort_order ASC'));
            $totcat_sum=0;
            foreach($modelmascategories as $eachcat){
                $cinv_row=$eachcat->product_category.' - Qty !!!!!';
                $camount_row=$eachcat->product_category.' - AMT !!!!!';
                $cind_sum=0;$camtind_sum=0;
                foreach($mod_dis as $eachdis){
                    $cat_det =  OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(price*quantity_ordered*exchange_rate) AS amount_ordered','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis['id'].' AND category_id='.(int)$eachcat['id'],'group'=>'parent_det.distributer_id'));
                    $cordersum =$cat_det->quantity_ordered;
                    $camtordersum =$cat_det->amount_ordered;
                    $cind_sum+=(int)$cordersum;
                    $camtind_sum+=$camtordersum;
                    $cinv_row .='!'.(int)$cordersum;
                    $camount_row.='!'.Yii::app()->numberFormatter->formatCurrency($camtordersum,"");
                }
                $cinv_row .='!'.$cind_sum;
                $camount_row .='!'.Yii::app()->numberFormatter->formatCurrency($camtind_sum,"");
                $list[$j++] = $cinv_row;
                $list[$j++] = $camount_row;
            }
            $ctot_row ='Total - Qty !!!!!';
            $camttot_row ='Total - AMT !!!!!';
            foreach($mod_dis as $eachdis){
                $cat_totdet = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(price*quantity_ordered*exchange_rate) AS amount_ordered','condition'=> 'parent_det.month='.$month.' AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$eachdis['id'],'group'=>'parent_det.distributer_id'));
                $ctot_ordersum =$cat_totdet->quantity_ordered;
                $ctotamt_ordersum =$cat_totdet->amount_ordered;
                $ctotind_sum+=(int)$ctot_ordersum;
                $ctotamtind_sum+=$ctotamt_ordersum;
                $ctot_row .='!'.(int)$ctot_ordersum;
                $camttot_row .='!'.Yii::app()->numberFormatter->formatCurrency($ctotamt_ordersum,"");
            }
            $ctot_row .='!'.$ctotind_sum;
            $camttot_row .='!'.Yii::app()->numberFormatter->formatCurrency($ctotamtind_sum,"");
            $list[$j++] =$ctot_row;
            $list[$j++] =$camttot_row;
            $list[$j++] ='';
            
            $j++;
            foreach ($list as $line) {
                fputcsv($file, explode('!', $line));
            }
            fclose($file);
            $filename = 'upload/test.csv';
            $path = Yii::app()->getBaseUrl(true) . '/upload';
            header('Content-Type: application/csv');
            $file_name='consolidated_order_'.$month.'_'.$year.'.csv';
            header('Content-disposition: attachment; filename="'.$file_name.'"');
            readfile($filename);
            Yii::app()->end();
            return;
        }else{
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionGetOrderdetails(){
        $_GET  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if(count($_GET)>0){
            $model = new ConsolidatedOrder();
            $month = (int) Yii::app()->request->getParam('month');
            $year = (int) Yii::app()->request->getParam('year');
            $count = Order::model()->count(array('condition' => 'month = "' . $month .  '"  AND year="'. $year .'"' ));
            $msg='';
            
            if (($count)==0) {
                $msg.='<div style="color:red">Order => Not Done</div>';
            }
            else{
                $msg.='<div style="color:green">Order => Done ('.$count.')</div>';
            }
            echo $msg;
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionDeleteconsolOrder(){
        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if(count($_POST)>0){
            $id = $_POST['id'];
            if($id>0){
                $model = new ConsolidatedOrder();
                $model->deleteByPk($id);
                echo "success";
            } else {
                echo "fail";
            }
           
        }
        else{
              throw new CHttpException(404, 'Page not found.');
        }
    } 
    
    public function actionDownloadUploadedOrderQty(){
        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if(count($_GET) > 0){
            $order_id = Yii::app()->request->getParam('id');
            $modelOrder =Order::model()->findByPk((int)$order_id);
            $year = $modelOrder->year;
            $month = $modelOrder->month;
            $distributer_id= $modelOrder->distributer_id;
            $price_date = $year.'-'.$month.'-'.'01';
            $cnd = 'is_active="Y" OR (is_active="D" AND ((discontinuing_month>='.(int)$month.' AND discontinuing_year='.(int)$year.') OR discontinuing_year>'.$year.'))'; 
            $model_inventory = Inventory::model()->findAll(array('condition'=>$cnd,'order' => 'product_category_id asc'));
            $file = fopen("upload/test.csv", "w");
            $j = 0;
            $mod_dis = Distributers::model()->with('region_det')->findByPk((int)$distributer_id);
            $region_id= (int)$mod_dis->region_id;
            $arr_months=Order::getmonths();
            if($month<=3){
              $year =$year-1;
            }
            $list[$j++] = 'ORDER REVIEW FORM QTY';
            $jan='';$feb='';$mar='';$apr='';$may='';$jun='';$jul='';$aug='';$sep='';$oct='';$nov='';$dec='';
            if((int)$month>0){
                switch((int)$month){
                    case 1:
                        $jan='Type Here||';
                        break;
                    case 2:
                        $feb='Type Here||';
                        break;
                    case 3:
                        $mar='Type Here||';
                        break;
                    case 4:
                        $apr='Type Here||';
                        break;
                    case 5:
                        $may='Type Here||';
                        break;
                    case 6:
                        $jun='Type Here||';
                        break;
                    case 7:
                        $jul='Type Here||';
                        break;
                    case 8:
                        $aug='Type Here||';
                        break;
                    case 9:
                        $sep='Type Here||';
                        break;
                    case 10:
                        $oct='Type Here||';
                        break;
                    case 11:
                        $nov='Type Here||';
                        break;
                    case 12:
                        $dec='Type Here||';
                        break;
                        
                }
            }
            $list[$j++] = 'Distributor!!'.$mod_dis->distributer_name.'!!!!!'.$apr.'!!!!!'.$may.'!!!!!'.$jun.'!!!!!'.'!!!!!'.$jul.'!!!!!'.$aug.'!!!!!'.$sep.'!!!!!'.'!!!!!'.$oct.'!!!!!'.$nov.'!!!!!'.$dec.'!!!!!'.'!!!!!'.$jan.'!!!!!'.$feb.'!!!!!'.$mar ;
            $year1 =(int)$year+1;
            $list[$j++] = '!PRODUCT CATEGORY!GMC!Model!'.$mod_dis->region_det->region_name.'!CBM!BASE CODE!01-04-'.$year.'!!!!!01-05-'.$year.'!!!!!01-06-'.$year.'!!!!!1Q TOTAL!!!!!01-07-'.$year.'!!!!!01-08-'.$year.'!!!!!01-09-'.$year.'!!!!!2Q TOTAL!!!!!01-10-'.$year.'!!!!!01-11-'.$year.'!!!!!01-12-'.$year.'!!!!!3Q TOTAL!!!!!01-01-'.$year1.'!!!!!01-02-'.$year1.'!!!!!01-03-'.$year1.'!!!!!4Q TOTAL!!!!!TOTAL'.$year.'-'.$year1;
            
            $list[$j++] = '!!!!!!!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM';
            foreach ($model_inventory as $i => $row) {
                $apr_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="4" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $may_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="5" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jun_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="6" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jul_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="7" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $aug_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="8" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $sep_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="9" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $oct_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="10" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $nov_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="11" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $dec_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="12" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jan_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="1" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                $feb_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="2" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                $mar_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="3" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                /*ordered*/
                $qty_apr=(int)$apr_model->quantity_ordered;
                $qty_may=(int)$may_model->quantity_ordered;
                $qty_jun=(int)$jun_model->quantity_ordered;
                $qty_jul=(int)$jul_model->quantity_ordered;
                $qty_aug=(int)$aug_model->quantity_ordered;
                $qty_sep=(int)$sep_model->quantity_ordered;
                $qty_oct=(int)$oct_model->quantity_ordered;
                $qty_nov=(int)$nov_model->quantity_ordered;
                $qty_dec=(int)$dec_model->quantity_ordered;
                $qty_jan=(int)$jan_model->quantity_ordered;
                $qty_feb=(int)$feb_model->quantity_ordered;
                $qty_mar=(int)$mar_model->quantity_ordered;
                $qty_q1=$qty_apr+$qty_may+$qty_jun;
                $qty_q2=$qty_jul+$qty_aug+$qty_sep;
                $qty_q3=$qty_oct+$qty_nov+$qty_dec;
                $qty_q4=$qty_jan+$qty_feb+$qty_mar;
                $qty_tot=$qty_q1+$qty_q2+$qty_q3+$qty_q4;
                /*supplyconfirmation*/
                $supplyqty_apr=(int)$apr_model->quantity_confirmed;
                $supplyqty_may=(int)$may_model->quantity_confirmed;
                $supplyqty_jun=(int)$jun_model->quantity_confirmed;
                $supplyqty_jul=(int)$jul_model->quantity_confirmed;
                $supplyqty_aug=(int)$aug_model->quantity_confirmed;
                $supplyqty_sep=(int)$sep_model->quantity_confirmed;
                $supplyqty_oct=(int)$oct_model->quantity_confirmed;
                $supplyqty_nov=(int)$nov_model->quantity_confirmed;
                $supplyqty_dec=(int)$dec_model->quantity_confirmed;
                $supplyqty_jan=(int)$jan_model->quantity_confirmed;
                $supplyqty_feb=(int)$feb_model->quantity_confirmed;
                $supplyqty_mar=(int)$mar_model->quantity_confirmed;
                $supplyqty_q1=$supplyqty_apr+$supplyqty_may+$supplyqty_jun;
                $supplyqty_q2=$supplyqty_jul+$supplyqty_aug+$supplyqty_sep;
                $supplyqty_q3=$supplyqty_oct+$supplyqty_nov+$supplyqty_dec;
                $supplyqty_q4=$supplyqty_jan+$supplyqty_feb+$supplyqty_mar;
                $supplyqty_tot=$supplyqty_q1+$supplyqty_q2+$supplyqty_q3+$supplyqty_q4;
                /*supplyconfirmation*/
                /*supplycbm*/
                $supplycbmqty_apr=$supplyqty_apr*$row['cbm'];
                $supplycbmqty_may=$supplyqty_may*$row['cbm'];
                $supplycbmqty_jun=$supplyqty_jun*$row['cbm'];
                $supplycbmqty_jul=$supplyqty_jul*$row['cbm'];
                $supplycbmqty_aug=$supplyqty_aug*$row['cbm'];
                $supplycbmqty_sep=$supplyqty_sep*$row['cbm'];
                $supplycbmqty_oct=$supplyqty_oct*$row['cbm'];
                $supplycbmqty_nov=$supplyqty_nov*$row['cbm'];
                $supplycbmqty_dec=$supplyqty_dec*$row['cbm'];
                $supplycbmqty_jan=$supplyqty_jan*$row['cbm'];
                $supplycbmqty_feb=$supplyqty_feb*$row['cbm'];
                $supplycbmqty_mar=$supplyqty_mar*$row['cbm'];
                $supplycbmqty_q1=$supplycbmqty_apr+$supplycbmqty_may+$supplycbmqty_jun;
                $supplycbmqty_q2=$supplycbmqty_jul+$supplycbmqty_aug+$supplycbmqty_sep;
                $supplycbmqty_q3=$supplycbmqty_oct+$supplycbmqty_nov+$supplycbmqty_dec;
                $supplycbmqty_q4=$supplycbmqty_jan+$supplycbmqty_feb+$supplycbmqty_mar;
                $supplycbmqty_tot=$supplycbmqty_q1+$supplycbmqty_q2+$supplycbmqty_q3+$supplycbmqty_q4;
                /*supplycbm*/
                /*shipped cbm*/
                $shp_apr=(int)$apr_model->quantity_shipped;
                $shp_may=(int)$may_model->quantity_shipped;
                $shp_jun=(int)$jun_model->quantity_shipped;
                $shp_jul=(int)$jul_model->quantity_shipped;
                $shp_aug=(int)$aug_model->quantity_shipped;
                $shp_sep=(int)$sep_model->quantity_shipped;
                $shp_oct=(int)$oct_model->quantity_shipped;
                $shp_nov=(int)$nov_model->quantity_shipped;
                $shp_dec=(int)$dec_model->quantity_shipped;
                $shp_jan=(int)$jan_model->quantity_shipped;
                $shp_feb=(int)$feb_model->quantity_shipped;
                $shp_mar=(int)$mar_model->quantity_shipped;
                /*shipped cbm*/
                /*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*/
                
                
                
                $list[$j++] = '!'.$row['category_det']['product_category']. '!' . $row['gmc_code'] . '!' . $row['model_no']
                        . '!' . (float)$mod_inv_price['price'] . '!' . $row['cbm'] . '!' . $row['base_code'] 
                        . '!'.$qty_apr.'!'.$supplyqty_apr.'!'.$shp_apr.'!'.($supplyqty_apr-$shp_apr).'!'.$supplycbmqty_apr. '!'.$qty_may.'!'.$supplyqty_may.'!'.$shp_may.'!0!'.$supplycbmqty_may. '!'.$qty_jun.'!'.$supplyqty_jun.'!'.$shp_jun.'!0!'.$supplycbmqty_jun. '!'.$qty_q1.'!'.$supplyqty_q1.'!0!0!'.$supplycbmqty_q1
                        . '!'.$qty_jul.'!'.$supplyqty_jul.'!'.$shp_jul.'!0!'.$supplycbmqty_jul. '!'.$qty_aug.'!'.$supplyqty_aug.'!'.$shp_aug.'!0!'.$supplycbmqty_aug. '!'.$qty_sep.'!'.$supplyqty_sep.'!'.$shp_sep.'!0!'.$supplycbmqty_sep. '!'.$qty_q2.'!'.$supplyqty_q2.'!0!0!'.$supplycbmqty_q2
                        . '!'.$qty_oct.'!'.$supplyqty_oct.'!'.$shp_oct.'!0!'.$supplycbmqty_oct. '!'.$qty_nov.'!'.$supplyqty_nov.'!'.$shp_nov.'!0!'.$supplycbmqty_nov. '!'.$qty_dec.'!'.$supplyqty_dec.'!'.$shp_dec.'!0!'.$supplycbmqty_dec. '!'.$qty_q3.'!'.$supplyqty_q3.'!0!0!'.$supplycbmqty_q3
                        . '!'.$qty_jan.'!'.$supplyqty_jan.'!'.$shp_jan.'!0!0'. '!'.$qty_feb.'!'.$supplyqty_feb.'!'.$shp_feb.'!0!0'. '!'.$qty_mar.'!'.$supplyqty_mar.'!'.$shp_mar.'!0!0'. '!'.$qty_q4.'!'.$supplyqty_q4.'!0!0!'.$supplycbmqty_q4
                        . '!'.$qty_tot.'!'.$supplyqty_tot.'!0!0!'.$supplycbmqty_tot;
            }
            $list[$j++] ='';
            $list[$j++] = '!PRODUCT CATEGORY!!!!!!01-04-'.$year.'!!!!!01-05-'.$year.'!!!!!01-06-'.$year.'!!!!!1Q TOTAL!!!!!01-07-'.$year.'!!!!!01-08-'.$year.'!!!!!01-09-'.$year.'!!!!!2Q TOTAL!!!!!01-10-'.$year.'!!!!!01-11-'.$year.'!!!!!01-12-'.$year.'!!!!!3Q TOTAL!!!!!01-01-'.$year1.'!!!!!01-02-'.$year1.'!!!!!01-03-'.$year1.'!!!!!4Q TOTAL!!!!!TOTAL'.$year.'-'.$year1;
            $modelmascategories = MasProductCategory::model()->findAll(array('order'=>'sort_order ASC'));
            $totcat_sum=0;
            $totqty_apr=$totqty_may=$totqty_jun=$totqty_jul=$totqty_aug=$totqty_sep=$totqty_oct=$totqty_nov=$totqty_dec=$totqty_jan=$totqty_feb=$totqty_mar=0;
            $totamt_apr=$totamt_may=$totamt_jun=$totamt_jul=$totamt_aug=$totamt_sep=$totamt_oct=$totamt_nov=$totamt_dec=$totamt_jan=$totamt_feb=$totamt_mar=0;
            $totsupplyqty_apr=$totsupplyqty_may=$totsupplyqty_jun=$totsupplyqty_jul=$totsupplyqty_aug=$totsupplyqty_sep=$totsupplyqty_oct=$totsupplyqty_nov=$totsupplyqty_dec=$totsupplyqty_jan=$totsupplyqty_feb=$totsupplyqty_mar=0;
            $totsupplycbmqty_apr=$totsupplycbmqty_may=$totsupplycbmqty_jun=$totsupplycbmqty_jul=$totsupplycbmqty_aug=$totsupplycbmqty_sep=$totsupplycbmqty_oct=$totsupplycbmqty_nov=$totsupplycbmqty_dec=$totsupplycbmqty_jan=$totsupplycbmqty_feb=$totsupplycbmqty_mar=0;
            $totshp_apr=$totshp_may=$totshp_jun=$totshp_jul=$totshp_aug=$totshp_sep=$totshp_oct=$totshp_nov=$totshp_dec=$totshp_jan=$totshp_feb=$totshp_mar=0;
            $totqty_q1=$totqty_q2=$totqty_q3=$totqty_q4=$totqty_tot=0;
            $totsupplyqty_q1=$totsupplyqty_q2=$totsupplyqty_q3=$totsupplyqty_q4=$totsupplyqty_tot=0;
            $totsupplycbmqty_q1=$totsupplycbmqty_q2=$totsupplycbmqty_q3=$totsupplycbmqty_q4=$totsupplycbmqty_tot=0;
            $totamt_q1=$totamt_q2=$totamt_q3=$totamt_q4=$totamt_tot=0;
            $totsupplyamt_q1=$totsupplyamt_q2=$totsupplyamt_q3=$totsupplyamt_q4=$totsupplyamt_tot=0;
            $totsupplycbmamt_q1=$totsupplycbmamt_q2=$totsupplycbmamt_q3=$totsupplycbmamt_q4=$totsupplycbmamt_tot=0;
            $totshpamt_q1=$totshpamt_q2=$totshpamt_q3=$totshpamt_q4=$totshpamt_tot=0;
            foreach($modelmascategories as $eachcat){
                $apr_model=OrderDetails::model()->with('parent_det')->find(array('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) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="4" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $may_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="5" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jun_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="6" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jul_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="7" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $aug_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="8" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $sep_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="9" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $oct_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="10" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $nov_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="11" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $dec_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="12" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jan_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="1" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                $feb_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="2" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                $mar_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(quantity_confirmed) AS quantity_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(quantity_ordered*price) AS amount_ordered,SUM(quantity_confirmed*price) AS amount_confirmed,SUM(quantity_shipped*price) AS amount_shipped','condition'=>'category_id='.(int)$eachcat['id'].' AND parent_det.month="3" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                /*ordered*/
                $qty_apr=(int)$apr_model->quantity_ordered;
                $qty_may=(int)$may_model->quantity_ordered;
                $qty_jun=(int)$jun_model->quantity_ordered;
                $qty_jul=(int)$jul_model->quantity_ordered;
                $qty_aug=(int)$aug_model->quantity_ordered;
                $qty_sep=(int)$sep_model->quantity_ordered;
                $qty_oct=(int)$oct_model->quantity_ordered;
                $qty_nov=(int)$nov_model->quantity_ordered;
                $qty_dec=(int)$dec_model->quantity_ordered;
                $qty_jan=(int)$jan_model->quantity_ordered;
                $qty_feb=(int)$feb_model->quantity_ordered;
                $qty_mar=(int)$mar_model->quantity_ordered;
                $qty_q1=$qty_apr+$qty_may+$qty_jun;
                $qty_q2=$qty_jul+$qty_aug+$qty_sep;
                $qty_q3=$qty_oct+$qty_nov+$qty_dec;
                $qty_q4=$qty_jan+$qty_feb+$qty_mar;
                $qty_tot=$qty_q1+$qty_q2+$qty_q3+$qty_q4;
                
                //totals
                $totqty_apr+=$qty_apr;
                $totqty_may+=$qty_may;
                $totqty_jun+=$qty_jun;
                $totqty_jul+=$qty_jul;
                $totqty_aug+=$qty_aug;
                $totqty_sep+=$qty_sep;
                $totqty_oct+=$qty_oct;
                $totqty_nov+=$qty_nov;
                $totqty_dec+=$qty_dec;
                $totqty_jan+=$qty_jan;
                $totqty_feb+=$qty_feb;
                $totqty_mar+=$qty_mar;
                $totqty_q1 = $totqty_apr + $totqty_may + $totqty_jun;
                $totqty_q2 = $totqty_jul + $totqty_aug + $totqty_sep;
                $totqty_q3 = $totqty_oct + $totqty_nov + $totqty_dec;
                $totqty_q4 = $totqty_jan + $totqty_feb + $totqty_mar;
                $totqty_tot = $totqty_q1 + $totqty_q2 + $totqty_q3 + $totqty_q4;
                /*supplyconfirmation*/
                $supplyqty_apr=(int)$apr_model->quantity_confirmed;
                $supplyqty_may=(int)$may_model->quantity_confirmed;
                $supplyqty_jun=(int)$jun_model->quantity_confirmed;
                $supplyqty_jul=(int)$jul_model->quantity_confirmed;
                $supplyqty_aug=(int)$aug_model->quantity_confirmed;
                $supplyqty_sep=(int)$sep_model->quantity_confirmed;
                $supplyqty_oct=(int)$oct_model->quantity_confirmed;
                $supplyqty_nov=(int)$nov_model->quantity_confirmed;
                $supplyqty_dec=(int)$dec_model->quantity_confirmed;
                $supplyqty_jan=(int)$jan_model->quantity_confirmed;
                $supplyqty_feb=(int)$feb_model->quantity_confirmed;
                $supplyqty_mar=(int)$mar_model->quantity_confirmed;
                $supplyqty_q1=$supplyqty_apr+$supplyqty_may+$supplyqty_jun;
                $supplyqty_q2=$supplyqty_jul+$supplyqty_aug+$supplyqty_sep;
                $supplyqty_q3=$supplyqty_oct+$supplyqty_nov+$supplyqty_dec;
                $supplyqty_q4=$supplyqty_jan+$supplyqty_feb+$supplyqty_mar;
                $supplyqty_tot=$supplyqty_q1+$supplyqty_q2+$supplyqty_q3+$supplyqty_q4;
                /*supplyconfirmation*/
                //total
                $totsupplyqty_apr+=$supplyqty_apr;
                $totsupplyqty_may+=$supplyqty_may;
                $totsupplyqty_jun+=$supplyqty_jun;
                $totsupplyqty_jul+=$supplyqty_jul;
                $totsupplyqty_aug+=$supplyqty_aug;
                $totsupplyqty_sep+=$supplyqty_sep;
                $totsupplyqty_oct+=$supplyqty_oct;
                $totsupplyqty_nov+=$supplyqty_nov;
                $totsupplyqty_dec+=$supplyqty_dec;
                $totsupplyqty_jan+=$supplyqty_jan;
                $totsupplyqty_feb+=$supplyqty_feb;
                $totsupplyqty_mar+=$supplyqty_mar;
                $totsupplyqty_q1 = $totsupplyqty_apr + $totsupplyqty_may + $totsupplyqty_jun;
                $totsupplyqty_q2 = $totsupplyqty_jul + $totsupplyqty_aug + $totsupplyqty_sep;
                $totsupplyqty_q3 = $totsupplyqty_oct + $totsupplyqty_nov + $totsupplyqty_dec;
                $totsupplyqty_q4 = $totsupplyqty_jan + $totsupplyqty_feb + $totsupplyqty_mar;
                $totsupplyqty_tot = $totsupplyqty_q1 + $totsupplyqty_q2 + $totsupplyqty_q3 + $totsupplyqty_q4;
                
                /*supplycbm*/
                $supplycbmqty_apr=$supplyqty_apr*$row['cbm'];
                $supplycbmqty_may=$supplyqty_may*$row['cbm'];
                $supplycbmqty_jun=$supplyqty_jun*$row['cbm'];
                $supplycbmqty_jul=$supplyqty_jul*$row['cbm'];
                $supplycbmqty_aug=$supplyqty_aug*$row['cbm'];
                $supplycbmqty_sep=$supplyqty_sep*$row['cbm'];
                $supplycbmqty_oct=$supplyqty_oct*$row['cbm'];
                $supplycbmqty_nov=$supplyqty_nov*$row['cbm'];
                $supplycbmqty_dec=$supplyqty_dec*$row['cbm'];
                $supplycbmqty_jan=$supplyqty_jan*$row['cbm'];
                $supplycbmqty_feb=$supplyqty_feb*$row['cbm'];
                $supplycbmqty_mar=$supplyqty_mar*$row['cbm'];
                $supplycbmqty_q1=$supplycbmqty_apr+$supplycbmqty_may+$supplycbmqty_jun;
                $supplycbmqty_q2=$supplycbmqty_jul+$supplycbmqty_aug+$supplycbmqty_sep;
                $supplycbmqty_q3=$supplycbmqty_oct+$supplycbmqty_nov+$supplycbmqty_dec;
                $supplycbmqty_q4=$supplycbmqty_jan+$supplycbmqty_feb+$supplycbmqty_mar;
                $supplycbmqty_tot=$supplycbmqty_q1+$supplycbmqty_q2+$supplycbmqty_q3+$supplycbmqty_q4;
                /*supplycbm*/
                //total
                $totsupplycbmqty_apr+=$supplycbmqty_apr;
                $totsupplycbmqty_may+=$supplycbmqty_may;
                $totsupplycbmqty_jun+=$supplycbmqty_jun;
                $totsupplycbmqty_jul+=$supplycbmqty_jul;
                $totsupplycbmqty_aug+=$supplycbmqty_aug;
                $totsupplycbmqty_sep+=$supplycbmqty_sep;
                $totsupplycbmqty_oct+=$supplycbmqty_oct;
                $totsupplycbmqty_nov+=$supplycbmqty_nov;
                $totsupplycbmqty_dec+=$supplycbmqty_dec;
                $totsupplycbmqty_jan+=$supplycbmqty_jan;
                $totsupplycbmqty_feb+=$supplycbmqty_feb;
                $totsupplycbmqty_mar+=$supplycbmqty_mar;
                $totsupplycbmqty_q1=$totsupplycbmqty_apr+$totsupplycbmqty_may+$totsupplycbmqty_jun;
                $totsupplycbmqty_q2=$totsupplycbmqty_jul+$totsupplycbmqty_aug+$totsupplycbmqty_sep;
                $totsupplycbmqty_q3=$totsupplycbmqty_oct+$totsupplycbmqty_nov+$totsupplycbmqty_dec;
                $totsupplycbmqty_q4=$totsupplycbmqty_jan+$totsupplycbmqty_feb+$totsupplycbmqty_mar;
                $totsupplycbmqty_tot=$totsupplycbmqty_q1+$totsupplycbmqty_q2+$totsupplycbmqty_q3+$totsupplycbmqty_q4;
                /*shipped cbm*/
                $shp_apr=(int)$apr_model->quantity_shipped;
                $shp_may=(int)$may_model->quantity_shipped;
                $shp_jun=(int)$jun_model->quantity_shipped;
                $shp_jul=(int)$jul_model->quantity_shipped;
                $shp_aug=(int)$aug_model->quantity_shipped;
                $shp_sep=(int)$sep_model->quantity_shipped;
                $shp_oct=(int)$oct_model->quantity_shipped;
                $shp_nov=(int)$nov_model->quantity_shipped;
                $shp_dec=(int)$dec_model->quantity_shipped;
                $shp_jan=(int)$jan_model->quantity_shipped;
                $shp_feb=(int)$feb_model->quantity_shipped;
                $shp_mar=(int)$mar_model->quantity_shipped;
                /*shipped cbm*/
                //total
                 $totshp_apr+=$shp_apr;
                $totshp_may+=$shp_may;
                $totshp_jun+=$shp_jun;
                $totshp_jul+=$shp_jul;
                $totshp_aug+=$shp_aug;
                $totshp_sep+=$shp_sep;
                $totshp_oct+=$shp_oct;
                $totshp_nov+=$shp_nov;
                $totshp_dec+=$shp_dec;
                $totshp_jan+=$shp_jan;
                $totshp_feb+=$shp_feb;
                $totshp_mar+=$shp_mar;
                
                //amount started
                /*ordered*/
                $amt_apr=(int)$apr_model->amount_ordered;
                $amt_may=(int)$may_model->amount_ordered;
                $amt_jun=(int)$jun_model->amount_ordered;
                $amt_jul=(int)$jul_model->amount_ordered;
                $amt_aug=(int)$aug_model->amount_ordered;
                $amt_sep=(int)$sep_model->amount_ordered;
                $amt_oct=(int)$oct_model->amount_ordered;
                $amt_nov=(int)$nov_model->amount_ordered;
                $amt_dec=(int)$dec_model->amount_ordered;
                $amt_jan=(int)$jan_model->amount_ordered;
                $amt_feb=(int)$feb_model->amount_ordered;
                $amt_mar=(int)$mar_model->amount_ordered;
                $amt_q1=$amt_apr+$amt_may+$amt_jun;
                $amt_q2=$amt_jul+$amt_aug+$amt_sep;
                $amt_q3=$amt_oct+$amt_nov+$amt_dec;
                $amt_q4=$amt_jan+$amt_feb+$amt_mar;
                $amt_tot=$amt_q1+$amt_q2+$amt_q3+$amt_q4;
                /*supplyconfirmation*/
                //total
                $totamt_apr+=$amt_apr;
                $totamt_may+=$amt_may;
                $totamt_jun+=$amt_jun;
                $totamt_jul+=$amt_jul;
                $totamt_aug+=$amt_aug;
                $totamt_sep+=$amt_sep;
                $totamt_oct+=$amt_oct;
                $totamt_nov+=$amt_nov;
                $totamt_dec+=$amt_dec;
                $totamt_jan+=$amt_jan;
                $totamt_feb+=$amt_feb;
                $totamt_mar+=$amt_mar;
                $totamt_q1=$totamt_apr+$totamt_may+$totamt_jun;
                $totamt_q2=$totamt_jul+$totamt_aug+$totamt_sep;
                $totamt_q3=$totamt_oct+$totamt_nov+$totamt_dec;
                $totamt_q4=$totamt_jan+$totamt_feb+$totamt_mar;
                $totamt_tot=$totamt_q1+$totamt_q2+$totamt_q3+$totamt_q4;
                //end total
                $supplyamt_apr=(int)$apr_model->amount_confirmed;
                $supplyamt_may=(int)$may_model->amount_confirmed;
                $supplyamt_jun=(int)$jun_model->amount_confirmed;
                $supplyamt_jul=(int)$jul_model->amount_confirmed;
                $supplyamt_aug=(int)$aug_model->amount_confirmed;
                $supplyamt_sep=(int)$sep_model->amount_confirmed;
                $supplyamt_oct=(int)$oct_model->amount_confirmed;
                $supplyamt_nov=(int)$nov_model->amount_confirmed;
                $supplyamt_dec=(int)$dec_model->amount_confirmed;
                $supplyamt_jan=(int)$jan_model->amount_confirmed;
                $supplyamt_feb=(int)$feb_model->amount_confirmed;
                $supplyamt_mar=(int)$mar_model->amount_confirmed;
                $supplyamt_q1=$supplyamt_apr+$supplyamt_may+$supplyamt_jun;
                $supplyamt_q2=$supplyamt_jul+$supplyamt_aug+$supplyamt_sep;
                $supplyamt_q3=$supplyamt_oct+$supplyamt_nov+$supplyamt_dec;
                $supplyamt_q4=$supplyamt_jan+$supplyamt_feb+$supplyamt_mar;
                $supplyamt_tot=$supplyamt_q1+$supplyamt_q2+$supplyamt_q3+$supplyamt_q4;
                /*supplyconfirmation*/
                //total
                 $totsupplyamt_apr+=$supplyamt_apr;
                $totsupplyamt_may+=$supplyamt_may;
                $totsupplyamt_jun+=$supplyamt_jun;
                $totsupplyamt_jul+=$supplyamt_jul;
                $totsupplyamt_aug+=$supplyamt_aug;
                $totsupplyamt_sep+=$supplyamt_sep;
                $totsupplyamt_oct+=$supplyamt_oct;
                $totsupplyamt_nov+=$supplyamt_nov;
                $totsupplyamt_dec+=$supplyamt_dec;
                $totsupplyamt_jan+=$supplyamt_jan;
                $totsupplyamt_feb+=$supplyamt_feb;
                $totsupplyamt_mar+=$supplyamt_mar;
                $totsupplyamt_q1=$totsupplyamt_apr+$totsupplyamt_may+$totsupplyamt_jun;
                $totsupplyamt_q2=$totsupplyamt_jul+$totsupplyamt_aug+$totsupplyamt_sep;
                $totsupplyamt_q3=$totsupplyamt_oct+$totsupplyamt_nov+$totsupplyamt_dec;
                $totsupplyamt_q4=$totsupplyamt_jan+$totsupplyamt_feb+$totsupplyamt_mar;
                $totsupplyamt_tot=$totsupplyamt_q1+$totsupplyamt_q2+$totsupplyamt_q3+$totsupplyamt_q4;
                //end total
                /*supplycbm*/
                $supplycbmamt_apr=$supplyamt_apr*$row['cbm'];
                $supplycbmamt_may=$supplyamt_may*$row['cbm'];
                $supplycbmamt_jun=$supplyamt_jun*$row['cbm'];
                $supplycbmamt_jul=$supplyamt_jul*$row['cbm'];
                $supplycbmamt_aug=$supplyamt_aug*$row['cbm'];
                $supplycbmamt_sep=$supplyamt_sep*$row['cbm'];
                $supplycbmamt_oct=$supplyamt_oct*$row['cbm'];
                $supplycbmamt_nov=$supplyamt_nov*$row['cbm'];
                $supplycbmamt_dec=$supplyamt_dec*$row['cbm'];
                $supplycbmamt_jan=$supplyamt_jan*$row['cbm'];
                $supplycbmamt_feb=$supplyamt_feb*$row['cbm'];
                $supplycbmamt_mar=$supplyamt_mar*$row['cbm'];
                $supplycbmamt_q1=$supplycbmamt_apr+$supplycbmamt_may+$supplycbmamt_jun;
                $supplycbmamt_q2=$supplycbmamt_jul+$supplycbmamt_aug+$supplycbmamt_sep;
                $supplycbmamt_q3=$supplycbmamt_oct+$supplycbmamt_nov+$supplycbmamt_dec;
                $supplycbmamt_q4=$supplycbmamt_jan+$supplycbmamt_feb+$supplycbmamt_mar;
                $supplycbmamt_tot=$supplycbmamt_q1+$supplycbmamt_q2+$supplycbmamt_q3+$supplycbmamt_q4;
                /*supplycbm*/
                //total
                $totsupplycbmamt_apr+=$supplyamt_apr;
                $totsupplycbmamt_may+=$supplyamt_may;
                $totsupplycbmamt_jun+=$supplyamt_jun;
                $totsupplycbmamt_jul+=$supplyamt_jul;
                $totsupplycbmamt_aug+=$supplyamt_aug;
                $totsupplycbmamt_sep+=$supplyamt_sep;
                $totsupplycbmamt_oct+=$supplyamt_oct;
                $totsupplycbmamt_nov+=$supplyamt_nov;
                $totsupplycbmamt_dec+=$supplyamt_dec;
                $totsupplycbmamt_jan+=$supplyamt_jan;
                $totsupplycbmamt_feb+=$supplyamt_feb;
                $totsupplycbmamt_mar+=$supplyamt_mar;
                $totsupplycbmamt_q1=$totsupplycbmamt_apr+$totsupplycbmamt_may+$totsupplycbmamt_jun;
                $totsupplycbmamt_q2=$totsupplycbmamt_jul+$totsupplycbmamt_aug+$totsupplycbmamt_sep;
                $totsupplycbmamt_q3=$totsupplycbmamt_oct+$totsupplycbmamt_nov+$totsupplycbmamt_dec;
                $totsupplycbmamt_q4=$totsupplycbmamt_jan+$totsupplycbmamt_feb+$totsupplycbmamt_mar;
                $totsupplycbmamt_tot=$totsupplycbmamt_q1+$totsupplycbmamt_q2+$totsupplycbmamt_q3+$totsupplycbmamt_q4;
                //end total
                /*shipped cbm*/
                $shpamt_apr=(int)$apr_model->amount_shipped;
                $shpamt_may=(int)$may_model->amount_shipped;
                $shpamt_jun=(int)$jun_model->amount_shipped;
                $shpamt_jul=(int)$jul_model->amount_shipped;
                $shpamt_aug=(int)$aug_model->amount_shipped;
                $shpamt_sep=(int)$sep_model->amount_shipped;
                $shpamt_oct=(int)$oct_model->amount_shipped;
                $shpamt_nov=(int)$nov_model->amount_shipped;
                $shpamt_dec=(int)$dec_model->amount_shipped;
                $shpamt_jan=(int)$jan_model->amount_shipped;
                $shpamt_feb=(int)$feb_model->amount_shipped;
                $shpamt_mar=(int)$mar_model->amount_shipped;
                /*shipped cbm*/
                //total
                $totshpamt_apr+=$shpamt_apr;
                $totshpamt_may+=$shpamt_may;
                $totshpamt_jun+=$shpamt_jun;
                $totshpamt_jul+=$shpamt_jul;
                $totshpamt_aug+=$shpamt_aug;
                $totshpamt_sep+=$shpamt_sep;
                $totshpamt_oct+=$shpamt_oct;
                $totshpamt_nov+=$shpamt_nov;
                $totshpamt_dec+=$shpamt_dec;
                $totshpamt_jan+=$shpamt_jan;
                $totshpamt_feb+=$shpamt_feb;
                $totshpamt_mar+=$shpamt_mar;
                $totshpamt_q1=totshpamt_apr+$totshpamt_may+$totshpamt_jun;
                $totshpamt_q2=$totshpamt_jul+$totshpamt_aug+$totshpamt_sep;
                $totshpamt_q3=$totshpamt_oct+$totshpamt_nov+$totshpamt_dec;
                $totshpamt_q4=$totshpamt_jan+$totshpamt_feb+$totshpamt_mar;
                $totshpamt_tot=$totshpamt_q1+$totshpamt_q2+$totshpamt_q3+$totshpamt_q4;
                //total
                
                $cinv_row = '!'.$eachcat->product_category. ' - Qty!!!!!!'.$qty_apr.'!'.$supplyqty_apr.'!'.$shp_apr.'!'.($supplyqty_apr-$shp_apr).'!'.$supplycbmqty_apr. '!'.$qty_may.'!'.$supplyqty_may.'!'.$shp_may.'!0!'.$supplycbmqty_may. '!'.$qty_jun.'!'.$supplyqty_jun.'!'.$shp_jun.'!0!'.$supplycbmqty_jun. '!'.$qty_q1.'!'.$supplyqty_q1.'!0!0!'.$supplycbmqty_q1
                        . '!'.$qty_jul.'!'.$supplyqty_jul.'!'.$shp_jul.'!0!'.$supplycbmqty_jul. '!'.$qty_aug.'!'.$supplyqty_aug.'!'.$shp_aug.'!0!'.$supplycbmqty_aug. '!'.$qty_sep.'!'.$supplyqty_sep.'!'.$shp_sep.'!0!'.$supplycbmqty_sep. '!'.$qty_q2.'!'.$supplyqty_q2.'!0!0!'.$supplycbmqty_q2
                        . '!'.$qty_oct.'!'.$supplyqty_oct.'!'.$shp_oct.'!0!'.$supplycbmqty_oct. '!'.$qty_nov.'!'.$supplyqty_nov.'!'.$shp_nov.'!0!'.$supplycbmqty_nov. '!'.$qty_dec.'!'.$supplyqty_dec.'!'.$shp_dec.'!0!'.$supplycbmqty_dec. '!'.$qty_q3.'!'.$supplyqty_q3.'!0!0!'.$supplycbmqty_q3
                        . '!'.$qty_jan.'!'.$supplyqty_jan.'!'.$shp_jan.'!0!0'. '!'.$qty_feb.'!'.$supplyqty_feb.'!'.$shp_feb.'!0!0'. '!'.$qty_mar.'!'.$supplyqty_mar.'!'.$shp_mar.'!0!0'. '!'.$qty_q4.'!'.$supplyqty_q4.'!0!0!'.$supplycbmqty_q4
                        . '!'.$qty_tot.'!'.$supplyqty_tot.'!0!0!'.$supplycbmqty_tot;
                $camount_row = '!'.$eachcat->product_category. ' - AMT!!!!!!'.Yii::app()->numberFormatter->formatCurrency($amt_apr,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_apr,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_apr,"").'!'.($supplyamt_apr-$shpamt_apr).'!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_apr,""). '!'.Yii::app()->numberFormatter->formatCurrency($amt_may,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_may,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_may,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_may,""). '!'.Yii::app()->numberFormatter->formatCurrency($amt_jun,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_jun,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_jun,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_jun,""). '!'.Yii::app()->numberFormatter->formatCurrency($amt_q1,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_q1,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_q1,"")
                        . '!'.Yii::app()->numberFormatter->formatCurrency($amt_jul,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_jul,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_jul,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_jul,""). '!'.Yii::app()->numberFormatter->formatCurrency($amt_aug,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_aug,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_aug,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_aug,""). '!'.Yii::app()->numberFormatter->formatCurrency($amt_sep,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_sep,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_sep,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_sep,""). '!'.Yii::app()->numberFormatter->formatCurrency($amt_q2,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_q2,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_q2,"")
                        . '!'.Yii::app()->numberFormatter->formatCurrency($amt_oct,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_oct,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_oct,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_oct,""). '!'.Yii::app()->numberFormatter->formatCurrency($amt_nov,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_nov,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_nov,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_nov,""). '!'.Yii::app()->numberFormatter->formatCurrency($amt_dec,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_dec,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_dec,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_dec,""). '!'.Yii::app()->numberFormatter->formatCurrency($amt_q3,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_q3,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_q3,"")
                        . '!'.Yii::app()->numberFormatter->formatCurrency($amt_jan,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_jan,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_jan,"").'!0!0'. '!'.Yii::app()->numberFormatter->formatCurrency($amt_feb,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_feb,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_feb,"").'!0!0'. '!'.Yii::app()->numberFormatter->formatCurrency($amt_mar,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_mar,"").'!'.Yii::app()->numberFormatter->formatCurrency($shpamt_mar,"").'!0!0'. '!'.Yii::app()->numberFormatter->formatCurrency($amt_q4,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_q4,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_q4,"")
                        . '!'.Yii::app()->numberFormatter->formatCurrency($amt_tot,"").'!'.Yii::app()->numberFormatter->formatCurrency($supplyamt_tot,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($supplycbmamt_tot,"");
                $list[$j++] = $cinv_row;
                $list[$j++] = $camount_row;
            }
            $ctot_row = '!Total - Qty!!!!!!'.$totqty_apr.'!'.$totsupplyqty_apr.'!'.$totshp_apr.'!'.($totsupplyqty_apr-$totshp_apr).'!'.$totsupplycbmqty_apr. '!'.$totqty_may.'!'.$totsupplyqty_may.'!'.$totshp_may.'!0!'.$totsupplycbmqty_may. '!'.$totqty_jun.'!'.$totsupplyqty_jun.'!'.$totshp_jun.'!0!'.$totsupplycbmqty_jun. '!'.$totqty_q1.'!'.$totsupplyqty_q1.'!0!0!'.$totsupplycbmqty_q1
                    . '!'.$totqty_jul.'!'.$totsupplyqty_jul.'!'.$totshp_jul.'!0!'.$totsupplycbmqty_jul. '!'.$totqty_aug.'!'.$totsupplyqty_aug.'!'.$totshp_aug.'!0!'.$totsupplycbmqty_aug. '!'.$totqty_sep.'!'.$totsupplyqty_sep.'!'.$totshp_sep.'!0!'.$totsupplycbmqty_sep. '!'.$totqty_q2.'!'.$totsupplyqty_q2.'!0!0!'.$totsupplycbmqty_q2
                    . '!'.$totqty_oct.'!'.$totsupplyqty_oct.'!'.$totshp_oct.'!0!'.$totsupplycbmqty_oct. '!'.$totqty_nov.'!'.$totsupplyqty_nov.'!'.$totshp_nov.'!0!'.$totsupplycbmqty_nov. '!'.$totqty_dec.'!'.$totsupplyqty_dec.'!'.$totshp_dec.'!0!'.$totsupplycbmqty_dec. '!'.$totqty_q3.'!'.$totsupplyqty_q3.'!0!0!'.$totsupplycbmqty_q3
                    . '!'.$totqty_jan.'!'.$totsupplyqty_jan.'!'.$totshp_jan.'!0!0'. '!'.$totqty_feb.'!'.$totsupplyqty_feb.'!'.$totshp_feb.'!0!0'. '!'.$totqty_mar.'!'.$totsupplyqty_mar.'!'.$totshp_mar.'!0!0'. '!'.$totqty_q4.'!'.$totsupplyqty_q4.'!0!0!'.$totsupplycbmqty_q4
                    . '!'.$totqty_tot.'!'.$totsupplyqty_tot.'!0!0!'.$totsupplycbmqty_tot;
            $ctotamount_row = '!Total - AMT!!!!!!'.Yii::app()->numberFormatter->formatCurrency($totamt_apr,"").' !'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_apr,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_apr,"").'!'.Yii::app()->numberFormatter->formatCurrency(($totsupplyamt_apr-$totshpamt_apr),"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_apr,""). '!'.Yii::app()->numberFormatter->formatCurrency($totamt_may,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_may,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_may,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_may,""). '!'.Yii::app()->numberFormatter->formatCurrency($totamt_jun,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_jun,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_jun,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_jun,""). '!'.Yii::app()->numberFormatter->formatCurrency($totamt_q1,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_q1,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_q1,"")
                    . '!'.Yii::app()->numberFormatter->formatCurrency($totamt_jul,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_jul,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_jul,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_jul,""). '!'.Yii::app()->numberFormatter->formatCurrency($totamt_aug,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_aug,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_aug,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_aug,""). '!'.Yii::app()->numberFormatter->formatCurrency($totamt_sep,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_sep,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_sep,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_sep,""). '!'.Yii::app()->numberFormatter->formatCurrency($totamt_q2,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_q2,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_q2,"")
                    . '!'.Yii::app()->numberFormatter->formatCurrency($totamt_oct,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_oct,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_oct,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_oct,""). '!'.Yii::app()->numberFormatter->formatCurrency($totamt_nov,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_nov,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_nov,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_nov,""). '!'.Yii::app()->numberFormatter->formatCurrency($totamt_dec,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_dec,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_dec,"").'!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_dec,""). '!'.Yii::app()->numberFormatter->formatCurrency($totamt_q3,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_q3,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_q3,"")
                    . '!'.Yii::app()->numberFormatter->formatCurrency($totamt_jan,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_jan,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_jan,"").'!0!0'. '!'.Yii::app()->numberFormatter->formatCurrency($totamt_feb,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_feb,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_feb,"").'!0!0'. '!'.Yii::app()->numberFormatter->formatCurrency($totamt_mar,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_mar,"").'!'.Yii::app()->numberFormatter->formatCurrency($totshpamt_mar,"").'!0!0'. '!'.Yii::app()->numberFormatter->formatCurrency($totamt_q4,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_q4,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_q4,"")
                    . '!'.Yii::app()->numberFormatter->formatCurrency($totamt_tot,"").'!'.Yii::app()->numberFormatter->formatCurrency($totsupplyamt_tot,"").'!0!0!'.Yii::app()->numberFormatter->formatCurrency($totsupplycbmamt_tot,"");
            $list[$j++] = $ctot_row;
            $list[$j++] = $ctotamount_row;
            $j++;
            foreach ($list as $line) {
                fputcsv($file, explode('!', $line));
            }
            fclose($file);
            $filename = 'upload/test.csv';
            $path = Yii::app()->getBaseUrl(true) . '/upload';
            header('Content-Type: application/csv');
            $file_name='Order_'.$mod_dis->distributer_name.'_'.$year.'.csv';
            header('Content-disposition: attachment; filename="'.$file_name.'"'); 
            readfile($filename);
            Yii::app()->end();
        }else{
            throw new CHttpException(404, 'Page not found.');
        }
    }
    
}


