<?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 SupplyconfirmationController 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','GetList','Create','Save','Download','Delete','GetSupplydetails','Test','DownloadOld','DownloadLatest'),

                '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.');

        }

    }

    public function actionGetList() {

        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);

        if(count($_POST) > 0){

            $model = new SupplyConfirmation();

            $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;
            }

            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 . "%')";

            }

            $modelOrder =new Order();

            $arr_months=$modelOrder->getmonths();

            $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["supply_download"])) {

                     //$div .=   '<button type="button"  class="btn btn-sm btn-theme" onclick="downloadSupplyConfirmation('.$row[id]..'&file_id='.$row['file_id'].');" title="ALL"><i class="fa fa-download"></i></button>';

                     $div .=   '<button type="button"  class="btn btn-xs btn-primary" onclick="downloadSupplyConfirmation('.$row['id'].');" title="download"><i class="fa fa-download"></i></button>';

               }

                     $model_supp_his = SupplyConfirmation::model()->findAll(array('condition' => '(month > "' . $row['month'] .'" AND year= "' .$row['year'].'") || (year> "' .$row['year'].'")'));

                    if (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["supply_delete"])) {

                     if($row['is_locked']!="Y" && empty($model_supp_his)){

                        $div .=   '&nbsp<button type="button"  class="btn btn-xs btn-primary" onclick="deleteSupplyConfirmation('.$row['id'].')" title="Delete"><i class="fa fa-trash"></i></button>';

                    }

                    }

                    $model_his = UploadSales::model()->findAll(array('condition' => 'supply_confirm_id = "' . $row['id'] . '" '));

                     if (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["supply_salesupload"])) {

                    if(empty($model_his)  && $row['is_locked']!="Y")//&&empty($model_supp_his)

                     {

                     $div .=   '&nbsp<button type="button" class="btn btn-xs btn-primary" onclick="loadPopupModal(\'uploadsales/UploadSalesdocument&supply_confirm_id='.$row['id'].'&month='.$row['month'].'&year='.$row['year'].'\',\'Sales\',\'\')"title="Upload Sales"><i class="fa fa-arrow-up"></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 actionCreate() {

        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);

        if(count($_GET) > 0){

            $model = new SupplyConfirmation();

            $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('create', array('model' => $model), false, false);

        }

        else{

              throw new CHttpException(404, 'Page not found.');

        }

    }

    public function actionSave(){

        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);

        if(count($_POST) > 0){

            $model = new SupplyConfirmation();$other=array();

            if (isset($_POST['SupplyConfirmation'])) {

                if ((int) @$_POST['SupplyConfirmation']['id'] > 0) {

                    $id = (int) @$_POST['SupplyConfirmation']['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['SupplyConfirmation'];

                $model->added_date =date('Y-m-d');

                $valid= $model->validate();

                $mod_his= new SupplyConfirmation();

                $mod_his = $mod_his->find(array('condition'=>'month = "'.$_POST['SupplyConfirmation']['month'].'" and year = "'.$_POST['SupplyConfirmation']['year'].'"'));

                if(!empty($mod_his)){

                    $other[]='Already added Data';

                    $valid = false;

                }

                $mod_goodsin = new InventoryUploads();

                $mod_goodsin = $mod_goodsin->find(array('condition' => 'month = "' . $_POST['SupplyConfirmation']['month'] .  '" AND year="'. $_POST['SupplyConfirmation']['year'] .'" AND  type="GoodsIn"'));

                if(empty($mod_goodsin)){

                    $other[]='Please Add Goods In Before Supply Confirmation';

                    $valid = false;

                }

                if($valid){

                    $model->save();

                    $modelOrderDetails = new OrderDetails();

                    $jsonData['updated_flag'] = $modelOrderDetails->updateShippingConfirmation((int)$_POST['SupplyConfirmation']['month'],(int)$_POST['SupplyConfirmation']['year']);

                    $jsonData['status'] = 'success';

                    $jsonData['frm'] = $_POST['SupplyConfirmation'];

                    $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 actionDownload(){

        ini_set("memory_limit", "-1");

        ini_set('max_execution_time', "-1");

        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);

        if(count($_GET) > 0){

            $id = Yii::app()->request->getParam('id');

            $model= new SupplyConfirmation();

            $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 D.id ASC";

            $mod_dis = Yii::app()->db->createCommand($sql)->queryAll();

            $arr_months=Order::getmonths();

            $list[$j++] = 'MERGED FILE_'.substr($arr_months[$month],0,3).'_'.$year.'!!!!Total Orders!!!merged file!!Total Check';

            $list[$j++] = '';

            $thirdrow = '!PRODUCT CATEGORY!GMC!Model!TTL Qty!TTL Qty!TTL Qty!Opening Inventory!SPIRAL Good-In!Unshipped!Backlog!Net Goods!Supply!Supply';

            $fourrow = '!!!!Order Received!Order Confirmed!Order Shipped!'.substr($arr_months[$month-1],0,3).'_'.substr($year,2).'!'.substr($arr_months[$month],0,3).'_'.substr($year,2).'!!!L=(H+I-J-K)!Shortfall!Excess Qty';

            foreach($mod_dis as $eachdis){

                $thirdrow .='!'.$eachdis['distributer_name'].'!!' ;
                $fourrow .='!Order!Confirmed!Shipped' ;

            }

            $list[$j++] = $thirdrow ;

            $list[$j++] = $fourrow ;

            $cur_month = (int)date('m');

            $cur_year = (int)date('Y');
            $sql_od="   SELECT t.product_id,O.distributer_id,O.month,O.year ,
                        quantity_ordered AS quantity_ordered,
                        quantity_confirmed AS quantity_confirmed,
                        quantity_shipped AS quantity_shipped
                        FROM ymh_order_details t 
                        LEFT JOIN ymh_order O ON O.id=t.order_id
                        WHERE O.month='".$month."' AND O.year='".$year."' 
                        ";

            $mod_od = Yii::app()->db->createCommand($sql_od)->queryAll();
            $arr_ord_detail=[];$arr_cat_detail=[];
            if(!empty($mod_od)){
                foreach($mod_od as $eachord){
                    $arr_ord_detail[$eachord['distributer_id']][$eachord['product_id']]['quantity_ordered']=$eachord['quantity_ordered'];
                    $arr_ord_detail[$eachord['distributer_id']][$eachord['product_id']]['quantity_confirmed']=$eachord['quantity_confirmed'];
                    $arr_ord_detail[$eachord['distributer_id']][$eachord['product_id']]['quantity_shipped']=$eachord['quantity_shipped'];
                    $arr_ord_detail[$eachord['distributer_id']][$eachord['product_id']]['is_done']=$eachord['is_done'];
                }
            }
            $sql_cod="   SELECT t.category_id,O.distributer_id,O.month,O.year ,
                        SUM(quantity_ordered) AS quantity_ordered,
                        SUM(quantity_confirmed) AS quantity_confirmed,
                        SUM(quantity_shipped) AS quantity_shipped,
                        SUM(price*quantity_ordered*exchange_rate) AS amount_ordered,
                        SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,
                        SUM(price*quantity_shipped*exchange_rate) AS amount_shipped
                        
                        FROM ymh_order_details t 
                        LEFT JOIN ymh_order O ON O.id=t.order_id
                        WHERE O.month='".$month."' AND O.year='".$year."' 
                        GROUP BY t.category_id,O.distributer_id ";

            $mod_cod = Yii::app()->db->createCommand($sql_cod)->queryAll();
            if(!empty($mod_cod)){
                foreach($mod_cod as $eachord){
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['quantity_ordered']=$eachord['quantity_ordered'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['quantity_confirmed']=$eachord['quantity_confirmed'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['quantity_shipped']=$eachord['quantity_shipped'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['amount_ordered']=$eachord['amount_ordered'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['amount_confirmed']=$eachord['amount_confirmed'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['amount_shipped']=$eachord['amount_shipped'];
                }
            }
            $mod_supply_det = SupplyDetails::model()->with('product_det')->findAll(array('condition'=>'supply_confirm_id='.(int)$id));

            foreach ($mod_supply_det as $i => $row) {

                $inv_row='!'.$row['product_det']['category_det']['product_category']. '!' . $row['product_det']['gmc_code'] . '!' . $row['product_det']['model_no'];

                $ind_sum=0;
                $totinvsum = (int)$row['inventory'];
                $totgoodssum =(int)$row['goods_in'];
                $unshipped_sales =(int)$row['unshipped'];
                $total_bklog =(int)$row['backlog'];
                
                $productToFind = (int)$row['product_id']; 
                $totordersum = 0;
                $total_confirmed_month = 0;
                $total_shipped_month = 0;
                
                foreach ($arr_ord_detail as $distributorId => $products) {
                    if (isset($products[$productToFind])) {
                        $totordersum += (int)$products[$productToFind]['quantity_ordered'];
                        $total_confirmed_month +=(int)$products[$productToFind]['quantity_confirmed'];
                        $total_shipped_month +=(int)$products[$productToFind]['quantity_shipped'];
                    }
                }
                
                
                
                /*$modtotordersum = 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 product_id='.(int)$row['product_id']));
                    $totordersum = !empty($modtotordersum )?$modtotordersum ->quantity_ordered:0;
                
               

                //$total_confirmed_month =(int)OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_confirmed) AS quantity_confirmed','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['product_id']))->quantity_confirmed;
                $modtotal_confirmed_month=OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_confirmed) AS quantity_confirmed','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['product_id']));
                $total_confirmed_month = !empty($modtotal_confirmed_month )?$modtotal_confirmed_month ->quantity_confirmed:0;


                //$total_shipped_month =(int)OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_shipped) AS quantity_shipped','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['product_id']))->quantity_shipped;
                $modtotal_shipped_month =OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_shipped) AS quantity_shipped','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['product_id']));
                $total_shipped_month = !empty($modtotal_shipped_month )?$modtotal_shipped_month ->quantity_shipped:0;
                */
                

                    

                $totreceived=$totinvsum+$totgoodssum-($total_bklog+$unshipped_sales);//+$totordersum

                ($totreceived<0)?$totreceived=0:'';

                $net_goods = $totinvsum+$totgoodssum-($total_bklog+$unshipped_sales);

                //echo '$net_goods='.$net_goods.'$totgoodssum='.$totgoodssum.'$total_bklog='.$total_bklog.'$unshipped_sales='.$unshipped_sales;

                if($totordersum<$totreceived){

                    $excess=$totreceived-$totordersum;

                    $shortfall=0;

                }else{

                    $shortfall=$totreceived-$totordersum;

                    $excess=0;

                }

                $inv_row .='!'.$totordersum.'!'.$total_confirmed_month.'!'.$total_shipped_month.'!'.$totinvsum.'!'.$totgoodssum.'!'.$unshipped_sales.'!'.$total_bklog.'!'.$net_goods.'!'.$shortfall.'!'.$excess;  

                $bal_rec=$totreceived;$bal_ord=$totordersum;

                foreach($mod_dis as $eachdis){
                    $ordersum = (int)$arr_ord_detail[(int)$eachdis['id']][(int)$row['product_id']]['quantity_ordered'];
                    $dis_sum = (int)$arr_ord_detail[(int)$eachdis['id']][(int)$row['product_id']]['quantity_confirmed'];
                    $quantity_shipped_sum = (int)$arr_ord_detail[(int)$eachdis['id']][(int)$row['product_id']]['quantity_shipped'];
                    $confirmed='';
                    if((int)$ordersum==0){
                        $confirmed='';
                    }

                    else if($arr_ord_detail[(int)$eachdis['id']][(int)$row['product_id']]['is_done']=="Y"||$dis_sum>0){
                        $confirmed=$dis_sum;
                    }
                    $inv_row .='!'.(int)$ordersum.'!'.$confirmed.'!'.$quantity_shipped_sum;
                }

                $list[$j++] = $inv_row;
            }

            $list[$j++] ='';

            $list[$j++] ='';

            

            $list[$j++] ='CATEGORY!!!!';

            $totcat_sum=0;

            $modelmascategories = MasProductCategory::model()->findAll(array('order'=>'sort_order ASC'));

            $gtotordersum = $gtotal_confirmed_month = $gtotal_shipped_month = $gtotal_shippedamt_month = $gtotorderamtsum = $gtotal_confirmedamt_month = $gtotinvsum = $gtotgoodssum = $gunshipped_sales = $gtotal_bklog = $gnet_goods = $gshortfall = $gexcess = 0;

            

            foreach ($modelmascategories as $eachcat) {

                $categoryId = (int)$eachcat['id'];

                $cinv_row = $eachcat->product_category . ' - Qty !!!';

                $camt_row = $eachcat->product_category . ' - AMT !!!';

            

                $orderQuery = 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 category_id=:category_id',

                        'params' => array(':month' => $month, ':year' => $year, ':category_id' => $categoryId),

                    )

                );

                //$ctotordersum = (int)$orderQuery->quantity_ordered;
                $ctotordersum = !empty($orderQuery )?(int)$orderQuery->quantity_ordered:0;

                //$ctotorderamtsum = $orderQuery->amount_ordered;
                $ctotorderamtsum = !empty($orderQuery )?$orderQuery->amount_ordered:0;

            

                $inventoryQuery = InventoryUploadDetails::model()->with('parent_det')->find(

                    array(

                        'select' => 'SUM(total_quantity) AS total_quantity',

                        'condition' => 'parent_det.month=:month AND parent_det.year=:year AND category_id=:category_id AND parent_det.type="Inventory"',

                        'params' => array(':month' => $month, ':year' => $year, ':category_id' => $categoryId),

                    )

                );

               // $ctotinvsum = (int)$inventoryQuery->total_quantity;
                $ctotinvsum = !empty($inventoryQuery )?$inventoryQuery->total_quantity:0;

            

                $goodsQuery = InventoryUploadDetails::model()->with('parent_det')->find(

                    array(

                        'select' => 'SUM(total_quantity) AS total_quantity',

                        'condition' => '(parent_det.month=:month AND parent_det.year=:year) AND parent_det.type="GoodsIn" AND category_id=:category_id',

                        'params' => array(':month' => $month, ':year' => $year, ':category_id' => $categoryId),

                    )

                );

              //  $ctotgoodssum = (int)$goodsQuery->total_quantity;
                $ctotgoodssum = !empty($goodsQuery )?$goodsQuery->total_quantity:0;

            

                $confirmedQuery = OrderDetails::model()->with('parent_det')->find(

                    array(

                        'select' => 'SUM(quantity_confirmed) AS quantity_confirmed, SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,SUM(quantity_shipped) AS quantity_shipped, SUM(price*quantity_shipped*exchange_rate) AS amount_shipped',

                        'condition' => '(parent_det.month=:month AND parent_det.year=:year) AND category_id=:category_id',

                        'params' => array(':month' => $month, ':year' => $year, ':category_id' => $categoryId),

                    )

                );

               // $ctotal_confirmed_month = (int)$confirmedQuery->quantity_confirmed;
                $ctotal_confirmed_month = !empty($confirmedQuery )?$confirmedQuery->quantity_confirmed:0;

              //  $ctotal_confirmedamt_month = $confirmedQuery->amount_confirmed;
                $ctotal_confirmedamt_month = !empty($confirmedQuery )?$confirmedQuery->amount_confirmed:0;

                //$ctotal_shipped_month =(int)$confirmedQuery->quantity_shipped;
                $ctotal_shipped_month = !empty($confirmedQuery )?$confirmedQuery->quantity_shipped:0;

                //$ctotal_shippedamt_month = $confirmedQuery->amount_shipped;
                $ctotal_shippedamt_month = !empty($confirmedQuery )?$confirmedQuery->amount_shipped:0;

                //$cunshipped_sales =(int)UploadUnshippedDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']))->quantity;
                $modcunshipped_sales=UploadUnshippedDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']));
                $cunshipped_sales = !empty($modcunshipped_sales )?$modcunshipped_sales ->quantity:0;

                //$ctotal_bklog =(int)UploadBacklogDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']))->quantity;
                $modctotal_bklog=UploadBacklogDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']));
                $ctotal_bklog = !empty($modctotal_bklog )?$modctotal_bklog ->quantity:0;

                $cnet_goods = $ctotinvsum+$ctotgoodssum-($ctotal_bklog+$cunshipped_sales);

                $ctotreceived=$cnet_goods;

                $gtotordersum+=$ctotordersum;

                $gtotal_confirmed_month+=$ctotal_confirmed_month;

                $gtotorderamtsum+=$ctotorderamtsum;

                $gtotal_confirmedamt_month+=$ctotal_confirmedamt_month;

                $gtotal_shipped_month+=$ctotal_shipped_month;

                $gtotal_shippedamt_month+=$ctotal_shippedamt_month;

                $gtotinvsum+=$ctotinvsum;

                $gtotgoodssum+=$ctotgoodssum;

                $gunshipped_sales+=$cunshipped_sales;

                $gtotal_bklog+=$ctotal_bklog;

                $gnet_goods+=$cnet_goods;

                ($ctotreceived<0)?$ctotreceived=0:'';

                if($ctotordersum<$cnet_goods){

                    $excess=$ctotreceived-$ctotordersum;

                    $cshortfall=0;

                }else{

                    $cshortfall=$ctotreceived-$ctotordersum;

                    $cexcess=0;

                }

                $cinv_row .='!'.$ctotordersum.'!'.$ctotal_confirmed_month.'!'.$ctotal_shipped_month.'!'.$ctotinvsum.'!'.$ctotgoodssum.'!'.$cunshipped_sales.'!'.$ctotal_bklog.'!'.$cnet_goods.'!0!0';  

                $camt_row .='!'.$ctotorderamtsum.'!'.$ctotal_confirmedamt_month.'!'.$ctotal_shippedamt_month.'!!!!!!0!0';  

                

                $cind_sum=0;

                foreach($mod_dis as $eachdis){
                    
                    $cordersum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['quantity_ordered'];
                    $cdis_sum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['quantity_confirmed'];
                    $cquantity_shipped_sum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['quantity_shipped'];
                    $camtordersum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['amount_ordered'];
                    $camtdis_sum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['amount_confirmed'];
                    $camtquantity_shipped_sum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['amount_shipped'];
                    /*$cmodel_order_det = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(price*quantity_ordered*exchange_rate) AS amount_ordered, SUM(quantity_confirmed) AS quantity_confirmed,SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(price*quantity_shipped*exchange_rate) AS amount_shipped','condition'=>'parent_det.month="'.$month.'" AND parent_det.year="'.$year.'" AND parent_det.distributer_id="'.(int)$eachdis['id'].'"  AND category_id='.(int)$eachcat['id']));

                    //$cordersum = (int)$cmodel_order_det['quantity_ordered'];
                    $cordersum = !empty($cmodel_order_det )?$cmodel_order_det['quantity_ordered']:0;

                   // $cdis_sum = (int)$cmodel_order_det['quantity_confirmed'];
                    $cdis_sum = !empty($cmodel_order_det )?$cmodel_order_det['quantity_confirmed']:0;

                   // $cquantity_shipped_sum = (int)$cmodel_order_det['quantity_shipped'];
                    $cquantity_shipped_sum = !empty($cmodel_order_det )?$cmodel_order_det['quantity_shipped']:0;

                    //$camtordersum = $cmodel_order_det['amount_ordered'];
                    $camtordersum = !empty($cmodel_order_det )?$cmodel_order_det['amount_ordered']:0;

                    //$camtdis_sum = $cmodel_order_det['amount_confirmed'];
                    $camtdis_sum = !empty($cmodel_order_det )?$cmodel_order_det['amount_confirmed']:0;

                    //$camtquantity_shipped_sum = $cmodel_order_det['amount_shipped'];
                    $camtquantity_shipped_sum = !empty($cmodel_order_det )?$cmodel_order_det['amount_shipped']:0;
*/
                    if($cdis_sum<0){

                        $cdis_sum=0;

                    }

                    $cind_sum+=(int)$cordersum;

                    $cinv_row .='!'.(int)$cordersum.'!'.$cdis_sum.'!'.$cquantity_shipped_sum;

                    $camt_row .='!'.Yii::app()->numberFormatter->formatCurrency($camtordersum,"").'!'.Yii::app()->numberFormatter->formatCurrency($camtdis_sum,"").'!'.Yii::app()->numberFormatter->formatCurrency($camtquantity_shipped_sum,"");

                }

                $list[$j++] = $cinv_row;

                $list[$j++] = $camt_row;

            }

            

           

            $ctot_row ='Total _ QTY !!!';

            $ctot_row .='!'.$gtotordersum.'!'.$gtotal_confirmed_month.'!'.$gtotal_shipped_month.'!'.$gtotinvsum.'!'.$gtotgoodssum.'!'.$gunshipped_sales.'!'.$gtotal_bklog.'!'.$gnet_goods.'!0!0';  

            $camttot_row ='Total - AMT !!!';

            $camttot_row .='!'.$gtotorderamtsum.'!'.$gtotal_confirmedamt_month.'!'.$gtotal_shippedamt_month.'!!!!!!0!0';

                

            foreach($mod_dis as $eachdis){

                $ctot_model_order_det = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(price*quantity_ordered*exchange_rate) AS amount_ordered, SUM(quantity_confirmed) AS quantity_confirmed,SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(price*quantity_shipped*exchange_rate) AS amount_shipped','condition'=>'parent_det.month="'.$month.'" AND parent_det.year="'.$year.'" AND parent_det.distributer_id="'.(int)$eachdis['id'].'"'));

                $ctot_ordersum =(int)$ctot_model_order_det['quantity_ordered'];

                $ctot_dis_sum =(int)$ctot_model_order_det['quantity_confirmed'];

                $ctot_quantity_shipped_sum =(int)$ctot_model_order_det['quantity_shipped'];

                $ctamtordersum = $ctot_model_order_det['amount_ordered'];

                $ctamtdis_sum = $ctot_model_order_det['amount_confirmed'];

                $ctamtquantity_shipped_sum = $ctot_model_order_det['amount_shipped'];

                $ctot_row .='!'.(int)$ctot_ordersum.'!'.$ctot_dis_sum.'!'.$ctot_quantity_shipped_sum;

                $camttot_row .='!'.Yii::app()->numberFormatter->formatCurrency($ctamtordersum,"").'!'.Yii::app()->numberFormatter->formatCurrency($ctamtdis_sum,"").'!'.Yii::app()->numberFormatter->formatCurrency($ctamtquantity_shipped_sum,"");

            }

            $list[$j++] = $ctot_row;

            $list[$j++] = $camttot_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='supply_confirmation_'.$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 actionDelete(){

        $_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);

        if(count($_POST)>0){

            $id = $_POST['id'];

            if($id>0){

                $model = new SupplyConfirmation();

                //removing confirmed details frm order  --start

                $modelItems=  new OrderDetails();

                $modelAllItems=  $modelItems->findAll(array('condition'=>'supply_confirm_id='.$id));

                if(!empty($modelAllItems)){

                    foreach($modelAllItems as $modelordDet){

                        $modelordDet->quantity_confirmed=0;

                        $modelordDet->supply_confirm_id=0;

                        $modelordDet->is_done='N';

                        $modelordDet->save(false);

                    }

                }

                //removing confirmed details frm order --end

                $modelSItems= new SupplyDetails();

                $modelSItems->deleteAll('supply_confirm_id=:refer_id', array(':refer_id' => $id));

                $model->deleteByPk($id);
                $modelInv = new Inventory();
                $modelAllItems=  $modelInv->findAll(array('condition' => 't.is_active="Y"'));
                if(!empty($modelAllItems)){
                    foreach($modelAllItems as $eachInv){
                        Inventory::DeductOneETAMonth((int)$eachInv->id);
                    }
                }

                echo "success";

            } else {

                echo "fail";

            }

           

        }

        else{

              throw new CHttpException(404, 'Page not found.');

        }

    }

    public function actionGetSupplydetails(){

        $_GET  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);

        if(count($_GET)>0){

            $model = new SupplyConfirmation();

            $month = (int) Yii::app()->request->getParam('month');

            $year = (int) Yii::app()->request->getParam('year');

            $type =  (int) Yii::app()->request->getParam('type');

            $model = ConsolidatedOrder::model()->find(array('condition' => 'month = "' . $month .  '"  AND year="'. $year .'"' ));

            $model_inve = InventoryUploads::model()->find(array('condition' => 'month = "' . $month .  '" AND year="'. $year .'" AND  type="Inventory"'));

            $model_goods = InventoryUploads::model()->find(array('condition' => 'month = "' . $month .  '" AND year="'. $year .'" AND  type="GoodsIn"'));

            $model_backlog = UploadBacklog::model()->find(array('condition' => 'month = "' . $month .  '" AND year="'. $year .'"'));

            $model_unshipped = UploadUnshippedSales::model()->find(array('condition' => 'month = "' . $month .  '" AND year="'. $year .'"'));

            $msg='';

            

            if (empty($model)) {

                $msg.='<div style="color:red"><dt>Consolidated &nbsp;=> Not Done</dt></div>';

            }

            else{

                $msg.='<div style="color:green"><dt>Consolidated => Done</dt></div>';

            }

            if (empty($model_inve)) {

                $msg.='<div style="color:red"><dt>Inventory &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> Not Done</dt></div>'; 

            }

            else{

                $msg.='<div style="color:green"><dt>Inventory &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=>  Done</dt></div>'; 

            }

            if (empty($model_goods)) {

                $msg.='<div style="color:red"><dt>Goods In &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> Not Done</dt></div>'; 

            }

            else{

                 $msg.='<div style="color:green"><dt>Goods In &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> Done</dt></div>'; 

            }

            if (empty($model_backlog)) {

                $msg.='<div style="color:red"><dt>Backlog &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> Not Done</dt></div>'; 

            }

            else{

                 $msg.='<div style="color:green"><dt>Backlog &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=> Done</dt></div>'; 

            }

            if (empty($model_unshipped)) {

                $msg.='<div style="color:red"><dt>UnShipped &nbsp;&nbsp;&nbsp;&nbsp;=> Not Done</dt></div>'; 

            }

            else{

                 $msg.='<div style="color:green"><dt>UnShipped &nbsp;&nbsp;&nbsp;&nbsp;=> Done</dt></div>'; 

            }

            echo $msg;

        }

        else{

              throw new CHttpException(404, 'Page not found.');

        }

    }

    

    public function actionDownloadOld(){

        ini_set("memory_limit", "-1");

        ini_set('max_execution_time', "-1");

        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);

        if(count($_GET) > 0){

            $id = Yii::app()->request->getParam('id');

            $model= new SupplyConfirmation();

            $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 D.id ASC";

            $mod_dis = Yii::app()->db->createCommand($sql)->queryAll();

            $arr_months=Order::getmonths();

            $list[$j++] = 'MERGED FILE_'.substr($arr_months[$month],0,3).'_'.$year.'!!!!Total Orders!!!merged file!!Total Check';

            $list[$j++] = '';

            $thirdrow = '!PRODUCT CATEGORY!GMC!Model!TTL Qty!TTL Qty!TTL Qty!Opening Inventory!SPIRAL Good-In!Unshipped!Backlog!Net Goods!Supply!Supply';

            $fourrow = '!!!!Order Received!Order Confirmed!Order Shipped!'.substr($arr_months[$month-1],0,3).'_'.substr($year,2).'!'.substr($arr_months[$month],0,3).'_'.substr($year,2).'!!!L=(H+I-J-K)!Shortfall!Excess Qty';

            foreach($mod_dis as $eachdis){

                $thirdrow .='!'.$eachdis['distributer_name'].'!!' ;

                $fourrow .='!Order!Confirmed!Shipped' ;

            }

            $list[$j++] = $thirdrow ;

            $list[$j++] = $fourrow ;

            $cur_month = (int)date('m');

            $cur_year = (int)date('Y');

            foreach ($model_inventory as $i => $row) {

                $inv_row='!'.$row['category_det']['product_category']. '!' . $row['gmc_code'] . '!' . $row['model_no'];

                $ind_sum=0;

                $totordersum = (int)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 product_id='.(int)$row['id']))->quantity_ordered;

                $totinvsum = (int)InventoryUploadDetails::model()->with('parent_det')->find(array('select'=>'total_quantity AS total_quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_id='.(int)$row['id'].' AND parent_det.type="Inventory"'))->total_quantity;

                $totgoodssum =(int)InventoryUploadDetails::model()->with('parent_det')->find(array('select'=>'total_quantity AS total_quantity','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND parent_det.type="GoodsIn" AND product_id='.(int)$row['id']))->total_quantity;

                $total_confirmed_month =(int)OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_confirmed) AS quantity_confirmed','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['id']))->quantity_confirmed;

                $total_shipped_month =(int)OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_shipped) AS quantity_shipped','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['id']))->quantity_shipped;

                $unshipped_sales =(int)UploadUnshippedDetails::model()->with('parent_det')->find(array('condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_id='.(int)$row['id']))->quantity;

                $total_bklog =(int)UploadBacklogDetails::model()->with('parent_det')->find(array('condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_id='.(int)$row['id']))->quantity;

                    

                $totreceived=$totinvsum+$totgoodssum-($total_bklog+$unshipped_sales);//+$totordersum

                ($totreceived<0)?$totreceived=0:'';

                $net_goods = $totinvsum+$totgoodssum-($total_bklog+$unshipped_sales);

                //echo '$net_goods='.$net_goods.'$totgoodssum='.$totgoodssum.'$total_bklog='.$total_bklog.'$unshipped_sales='.$unshipped_sales;

                if($totordersum<$totreceived){

                    $excess=$totreceived-$totordersum;

                    $shortfall=0;

                }else{

                    $shortfall=$totreceived-$totordersum;

                    $excess=0;

                }

                $inv_row .='!'.$totordersum.'!'.$total_confirmed_month.'!'.$total_shipped_month.'!'.$totinvsum.'!'.$totgoodssum.'!'.$unshipped_sales.'!'.$total_bklog.'!'.$net_goods.'!'.$shortfall.'!'.$excess;  

                $bal_rec=$totreceived;$bal_ord=$totordersum;

                foreach($mod_dis as $eachdis){

                    //echo 'parent_det.month="'.$month.'" AND parent_det.year="'.$year.'" AND parent_det.distributer_id="'.(int)$eachdis['id'].'" AND product_id="'.(int)$row['id'].'"';

                    $model_order_det = 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,is_done','condition'=>'parent_det.month="'.$month.'" AND parent_det.year="'.$year.'" AND parent_det.distributer_id="'.(int)$eachdis['id'].'" AND product_id="'.(int)$row['id'].'"'));

                    $ordersum = (int)$model_order_det['quantity_ordered'];

                    $dis_sum = (int)$model_order_det['quantity_confirmed'];

                    $quantity_shipped_sum = (int)$model_order_det['quantity_shipped'];

                    $confirmed='';

                    if($model_order_det['is_done']=="Y"||$dis_sum>0){

                        $confirmed=$dis_sum;

                    }

                    $inv_row .='!'.(int)$ordersum.'!'.$confirmed.'!'.$quantity_shipped_sum;

                }

                $list[$j++] = $inv_row;

                //break;

            }

            $list[$j++] ='';

            $list[$j++] ='';

            $list[$j++] ='CATEGORY!!!!';

            $modelmascategories = MasProductCategory::model()->findAll(array('order'=>'sort_order ASC'));

            $totcat_sum=0;

            $gtotordersum=$gtotal_confirmed_month=$gtotal_shipped_month=$gtotorderamtsum=$gtotal_confirmedamt_month=$gtotal_shippedamt_month=$gtotinvsum=$gtotgoodssum=$gunshipped_sales=$gtotal_bklog=$gnet_goods=$gshortfall=$gexcess=0;

            foreach($modelmascategories as $eachcat){

                $cinv_row=$eachcat->product_category.' - Qty !!!';

                $camt_row=$eachcat->product_category.' - AMT !!!';

                $ctotordersum = (int)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 category_id='.(int)$eachcat['id']))->quantity_ordered;

                $ctotorderamtsum = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(price*quantity_ordered*exchange_rate) AS amount_ordered','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND category_id='.(int)$eachcat['id']))->amount_ordered;

                $ctotinvsum = (int)InventoryUploadDetails::model()->with('parent_det')->find(array('select'=>'SUM(total_quantity) AS total_quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND category_id='.(int)$eachcat['id'].' AND parent_det.type="Inventory"'))->total_quantity;

                $ctotgoodssum =(int)InventoryUploadDetails::model()->with('parent_det')->find(array('select'=>'SUM(total_quantity) AS total_quantity','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND parent_det.type="GoodsIn" AND category_id='.(int)$eachcat['id']))->total_quantity;

                $ctotal_confirmed_month =(int)OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_confirmed) AS quantity_confirmed','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND category_id='.(int)$eachcat['id']))->quantity_confirmed;

                $ctotal_confirmedamt_month =OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND category_id='.(int)$eachcat['id']))->amount_confirmed;

                $ctotal_shipped_month =(int)OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_shipped) AS quantity_shipped','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND category_id='.(int)$eachcat['id']))->quantity_shipped;

                $ctotal_shippedamt_month =OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(price*quantity_shipped*exchange_rate) AS amount_shipped','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND category_id='.(int)$eachcat['id']))->amount_shipped;

                $cunshipped_sales =(int)UploadUnshippedDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']))->quantity;

                $ctotal_bklog =(int)UploadBacklogDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']))->quantity;

                $cnet_goods = $ctotinvsum+$ctotgoodssum-($ctotal_bklog+$cunshipped_sales);

                $ctotreceived=$cnet_goods;

                $gtotordersum+=$ctotordersum;

                $gtotal_confirmed_month+=$ctotal_confirmed_month;

                $gtotorderamtsum+=$ctotorderamtsum;

                $gtotal_confirmedamt_month+=$ctotal_confirmedamt_month;

                $gtotal_shipped_month+=$ctotal_shipped_month;

                $gtotal_shippedamt_month+=$ctotal_shippedamt_month;

                $gtotinvsum+=$ctotinvsum;

                $gtotgoodssum+=$ctotgoodssum;

                $gunshipped_sales+=$cunshipped_sales;

                $gtotal_bklog+=$ctotal_bklog;

                $gnet_goods+=$cnet_goods;

                ($ctotreceived<0)?$ctotreceived=0:'';

                if($ctotordersum<$cnet_goods){

                    $excess=$ctotreceived-$ctotordersum;

                    $cshortfall=0;

                }else{

                    $cshortfall=$ctotreceived-$ctotordersum;

                    $cexcess=0;

                }

                $cinv_row .='!'.$ctotordersum.'!'.$ctotal_confirmed_month.'!'.$ctotal_shipped_month.'!'.$ctotinvsum.'!'.$ctotgoodssum.'!'.$cunshipped_sales.'!'.$ctotal_bklog.'!'.$cnet_goods.'!0!0';  

                $camt_row .='!'.$ctotorderamtsum.'!'.$ctotal_confirmedamt_month.'!'.$ctotal_shippedamt_month.'!!!!!!0!0';  

                $cind_sum=0;

                foreach($mod_dis as $eachdis){

                    $cmodel_order_det = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(price*quantity_ordered*exchange_rate) AS amount_ordered, SUM(quantity_confirmed) AS quantity_confirmed,SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(price*quantity_shipped*exchange_rate) AS amount_shipped','condition'=>'parent_det.month="'.$month.'" AND parent_det.year="'.$year.'" AND parent_det.distributer_id="'.(int)$eachdis['id'].'"  AND category_id='.(int)$eachcat['id']));

                    $cordersum = (int)$cmodel_order_det['quantity_ordered'];

                    $cdis_sum = (int)$cmodel_order_det['quantity_confirmed'];

                    $cquantity_shipped_sum = (int)$cmodel_order_det['quantity_shipped'];

                    $camtordersum = $cmodel_order_det['amount_ordered'];

                    $camtdis_sum = $cmodel_order_det['amount_confirmed'];

                    $camtquantity_shipped_sum = $cmodel_order_det['amount_shipped'];

                    if($cdis_sum<0){

                        $cdis_sum=0;

                    }

                    $cind_sum+=(int)$cordersum;

                    $cinv_row .='!'.(int)$cordersum.'!'.$cdis_sum.'!'.$cquantity_shipped_sum;

                    $camt_row .='!'.Yii::app()->numberFormatter->formatCurrency($camtordersum,"").'!'.Yii::app()->numberFormatter->formatCurrency($camtdis_sum,"").'!'.Yii::app()->numberFormatter->formatCurrency($camtquantity_shipped_sum,"");

                }

                $list[$j++] = $cinv_row;

                $list[$j++] = $camt_row;

            }

            $ctot_row ='Total _ QTY !!!';

            $ctot_row .='!'.$gtotordersum.'!'.$gtotal_confirmed_month.'!'.$gtotal_shipped_month.'!'.$gtotinvsum.'!'.$gtotgoodssum.'!'.$gunshipped_sales.'!'.$gtotal_bklog.'!'.$gnet_goods.'!0!0';  

            $camttot_row ='Total - AMT !!!';

            $camttot_row .='!'.$gtotorderamtsum.'!'.$gtotal_confirmedamt_month.'!'.$gtotal_shippedamt_month.'!!!!!!0!0';  

                

            foreach($mod_dis as $eachdis){

                $ctot_model_order_det = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(price*quantity_ordered*exchange_rate) AS amount_ordered, SUM(quantity_confirmed) AS quantity_confirmed,SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(price*quantity_shipped*exchange_rate) AS amount_shipped','condition'=>'parent_det.month="'.$month.'" AND parent_det.year="'.$year.'" AND parent_det.distributer_id="'.(int)$eachdis['id'].'"'));

                $ctot_ordersum =(int)$ctot_model_order_det['quantity_ordered'];

                $ctot_dis_sum =(int)$ctot_model_order_det['quantity_confirmed'];

                $ctot_quantity_shipped_sum =(int)$ctot_model_order_det['quantity_shipped'];

                $ctamtordersum = $ctot_model_order_det['amount_ordered'];

                $ctamtdis_sum = $ctot_model_order_det['amount_confirmed'];

                $ctamtquantity_shipped_sum = $ctot_model_order_det['amount_shipped'];

                $ctot_row .='!'.(int)$ctot_ordersum.'!'.$ctot_dis_sum.'!'.$ctot_quantity_shipped_sum;

                $camttot_row .='!'.Yii::app()->numberFormatter->formatCurrency($ctamtordersum,"").'!'.Yii::app()->numberFormatter->formatCurrency($ctamtdis_sum,"").'!'.Yii::app()->numberFormatter->formatCurrency($ctamtquantity_shipped_sum,"");

            }

            $list[$j++] = $ctot_row;

            $list[$j++] = $camttot_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='supply_confirmation_'.$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 actionTest(){

	    $new_goods_date = '2022-12-01';

        echo $goodsmonth=date('m',strtotime($new_goods_date.' -1 months'));

        echo $goodsyear=date('Y',strtotime($new_goods_date));;

	}
	public function actionDownloadLatest(){

        ini_set("memory_limit", "-1");

        ini_set('max_execution_time', "-1");

        $_GET  = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);

        if(count($_GET) > 0){

            $id = Yii::app()->request->getParam('id');

            $model= new SupplyConfirmation();

            $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 D.id ASC";

            $mod_dis = Yii::app()->db->createCommand($sql)->queryAll();
            
            $sql_cat="
            SELECT
                product_category_id AS id,C.product_category, C.sort_order
            FROM    ymh_order_details t
            LEFT JOIN ymh_order O ON O.id = t.order_id
            LEFT JOIN ymh_inventory I ON I.id = t.product_id
            LEFT JOIN ymh_mas_product_category C ON  C.id = I.product_category_id
            WHERE O.month='".$month."' AND O.year='".$year."'
            GROUP BY    I.product_category_id
            ORDER BY    C.sort_order ASC";
            $mod_cat = Yii::app()->db->createCommand($sql_cat)->queryAll();

            $arr_months=Order::getmonths();

            $list[$j++] = 'MERGED FILE_'.substr($arr_months[$month],0,3).'_'.$year.'!!!!Total Orders!!!merged file!!Total Check';

            $list[$j++] = '';

            $thirdrow = '!PRODUCT CATEGORY!GMC!Model!TTL Qty!TTL Qty!TTL Qty!Opening Inventory!SPIRAL Good-In!Unshipped!Backlog!Net Goods!Supply!Supply';

            $fourrow = '!!!!Order Received!Order Confirmed!Order Shipped!'.substr($arr_months[$month-1],0,3).'_'.substr($year,2).'!'.substr($arr_months[$month],0,3).'_'.substr($year,2).'!!!L=(H+I-J-K)!Shortfall!Excess Qty';

            foreach($mod_dis as $eachdis){

                $thirdrow .='!'.$eachdis['distributer_name'].'!!' ;
                $fourrow .='!Order!Confirmed!Shipped' ;

            }

            $list[$j++] = $thirdrow ;

            $list[$j++] = $fourrow ;

            $cur_month = (int)date('m');

            $cur_year = (int)date('Y');
            $sql_od="   SELECT t.product_id,O.distributer_id,O.month,O.year ,
                        quantity_ordered AS quantity_ordered,
                        quantity_confirmed AS quantity_confirmed,
                        quantity_shipped AS quantity_shipped
                        FROM ymh_order_details t 
                        LEFT JOIN ymh_order O ON O.id=t.order_id
                        WHERE O.month='".$month."' AND O.year='".$year."' 
                        ";

            $mod_od = Yii::app()->db->createCommand($sql_od)->queryAll();
            $arr_ord_detail=[];$arr_cat_detail=[];
            if(!empty($mod_od)){
                foreach($mod_od as $eachord){
                    $arr_ord_detail[$eachord['distributer_id']][$eachord['product_id']]['quantity_ordered']=$eachord['quantity_ordered'];
                    $arr_ord_detail[$eachord['distributer_id']][$eachord['product_id']]['quantity_confirmed']=$eachord['quantity_confirmed'];
                    $arr_ord_detail[$eachord['distributer_id']][$eachord['product_id']]['quantity_shipped']=$eachord['quantity_shipped'];
                    $arr_ord_detail[$eachord['distributer_id']][$eachord['product_id']]['is_done']=$eachord['is_done'];
                }
            }
            $sql_cod="   SELECT t.category_id,O.distributer_id,O.month,O.year ,
                        SUM(quantity_ordered) AS quantity_ordered,
                        SUM(quantity_confirmed) AS quantity_confirmed,
                        SUM(quantity_shipped) AS quantity_shipped,
                        SUM(price*quantity_ordered*exchange_rate) AS amount_ordered,
                        SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,
                        SUM(price*quantity_shipped*exchange_rate) AS amount_shipped
                        
                        FROM ymh_order_details t 
                        LEFT JOIN ymh_order O ON O.id=t.order_id
                        WHERE O.month='".$month."' AND O.year='".$year."' 
                        GROUP BY t.category_id,O.distributer_id ";

            $mod_cod = Yii::app()->db->createCommand($sql_cod)->queryAll();
            if(!empty($mod_cod)){
                foreach($mod_cod as $eachord){
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['quantity_ordered']=$eachord['quantity_ordered'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['quantity_confirmed']=$eachord['quantity_confirmed'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['quantity_shipped']=$eachord['quantity_shipped'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['amount_ordered']=$eachord['amount_ordered'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['amount_confirmed']=$eachord['amount_confirmed'];
                    $arr_cat_detail[$eachord['distributer_id']][$eachord['category_id']]['amount_shipped']=$eachord['amount_shipped'];
                }
            }
            $mod_supply_det = SupplyDetails::model()->with('product_det')->findAll(array('condition'=>'supply_confirm_id='.(int)$id));

            foreach ($mod_supply_det as $i => $row) {

                $inv_row='!'.$row['product_det']['category_det']['product_category']. '!' . $row['product_det']['gmc_code'] . '!' . $row['product_det']['model_no'];

                $ind_sum=0;
                $totinvsum = (int)$row['inventory'];
                $totgoodssum =(int)$row['goods_in'];
                $unshipped_sales =(int)$row['unshipped'];
                $total_bklog =(int)$row['backlog'];
                
                $productToFind = (int)$row['product_id']; 
                $totordersum = 0;
                $total_confirmed_month = 0;
                $total_shipped_month = 0;
                
                foreach ($arr_ord_detail as $distributorId => $products) {
                    if (isset($products[$productToFind])) {
                        $totordersum += (int)$products[$productToFind]['quantity_ordered'];
                        $total_confirmed_month +=(int)$products[$productToFind]['quantity_confirmed'];
                        $total_shipped_month +=(int)$products[$productToFind]['quantity_shipped'];
                    }
                }
                
                
                
                /*$modtotordersum = 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 product_id='.(int)$row['product_id']));
                    $totordersum = !empty($modtotordersum )?$modtotordersum ->quantity_ordered:0;
                
               

                //$total_confirmed_month =(int)OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_confirmed) AS quantity_confirmed','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['product_id']))->quantity_confirmed;
                $modtotal_confirmed_month=OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_confirmed) AS quantity_confirmed','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['product_id']));
                $total_confirmed_month = !empty($modtotal_confirmed_month )?$modtotal_confirmed_month ->quantity_confirmed:0;


                //$total_shipped_month =(int)OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_shipped) AS quantity_shipped','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['product_id']))->quantity_shipped;
                $modtotal_shipped_month =OrderDetails::model()->with('parent_det')->find(array('select'  =>'SUM(quantity_shipped) AS quantity_shipped','condition'=>'(parent_det.month='.$month.' AND parent_det.year='.$year.') AND product_id='.(int)$row['product_id']));
                $total_shipped_month = !empty($modtotal_shipped_month )?$modtotal_shipped_month ->quantity_shipped:0;
                */
                

                    

                $totreceived=$totinvsum+$totgoodssum-($total_bklog+$unshipped_sales);//+$totordersum

                ($totreceived<0)?$totreceived=0:'';

                $net_goods = $totinvsum+$totgoodssum-($total_bklog+$unshipped_sales);

                //echo '$net_goods='.$net_goods.'$totgoodssum='.$totgoodssum.'$total_bklog='.$total_bklog.'$unshipped_sales='.$unshipped_sales;

                if($totordersum<$totreceived){

                    $excess=$totreceived-$totordersum;

                    $shortfall=0;

                }else{

                    $shortfall=$totreceived-$totordersum;

                    $excess=0;

                }

                $inv_row .='!'.$totordersum.'!'.$total_confirmed_month.'!'.$total_shipped_month.'!'.$totinvsum.'!'.$totgoodssum.'!'.$unshipped_sales.'!'.$total_bklog.'!'.$net_goods.'!'.$shortfall.'!'.$excess;  

                $bal_rec=$totreceived;$bal_ord=$totordersum;

                foreach($mod_dis as $eachdis){
                    $ordersum = (int)$arr_ord_detail[(int)$eachdis['id']][(int)$row['product_id']]['quantity_ordered'];
                    $dis_sum = (int)$arr_ord_detail[(int)$eachdis['id']][(int)$row['product_id']]['quantity_confirmed'];
                    $quantity_shipped_sum = (int)$arr_ord_detail[(int)$eachdis['id']][(int)$row['product_id']]['quantity_shipped'];
                    $confirmed='';
                    if((int)$ordersum==0){
                        $confirmed='';
                    }

                    else if($arr_ord_detail[(int)$eachdis['id']][(int)$row['product_id']]['is_done']=="Y"||$dis_sum>0){
                        $confirmed=$dis_sum;
                    }
                    $inv_row .='!'.(int)$ordersum.'!'.$confirmed.'!'.$quantity_shipped_sum;
                }

                $list[$j++] = $inv_row;
            }

            $list[$j++] ='';

            $list[$j++] ='';

            

            $list[$j++] ='CATEGORY!!!!';

            $totcat_sum=0;

            //$modelmascategories = MasProductCategory::model()->findAll(array('order'=>'sort_order ASC'));

            $gtotordersum = $gtotal_confirmed_month = $gtotal_shipped_month = $gtotal_shippedamt_month = $gtotorderamtsum = $gtotal_confirmedamt_month = $gtotinvsum = $gtotgoodssum = $gunshipped_sales = $gtotal_bklog = $gnet_goods = $gshortfall = $gexcess = 0;

            

            foreach ($mod_cat as $eachcat) {

                $categoryId = (int)$eachcat['id'];

                $cinv_row = $eachcat['product_category'] . ' - Qty !!!';

                $camt_row = $eachcat['product_category'] . ' - AMT !!!';

            

                $orderQuery = 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 category_id=:category_id',

                        'params' => array(':month' => $month, ':year' => $year, ':category_id' => $categoryId),

                    )

                );

                //$ctotordersum = (int)$orderQuery->quantity_ordered;
                $ctotordersum = !empty($orderQuery )?(int)$orderQuery->quantity_ordered:0;

                //$ctotorderamtsum = $orderQuery->amount_ordered;
                $ctotorderamtsum = !empty($orderQuery )?$orderQuery->amount_ordered:0;

            

                $inventoryQuery = InventoryUploadDetails::model()->with('parent_det')->find(

                    array(

                        'select' => 'SUM(total_quantity) AS total_quantity',

                        'condition' => 'parent_det.month=:month AND parent_det.year=:year AND category_id=:category_id AND parent_det.type="Inventory"',

                        'params' => array(':month' => $month, ':year' => $year, ':category_id' => $categoryId),

                    )

                );

               // $ctotinvsum = (int)$inventoryQuery->total_quantity;
                $ctotinvsum = !empty($inventoryQuery )?$inventoryQuery->total_quantity:0;

            

                $goodsQuery = InventoryUploadDetails::model()->with('parent_det')->find(

                    array(

                        'select' => 'SUM(total_quantity) AS total_quantity',

                        'condition' => '(parent_det.month=:month AND parent_det.year=:year) AND parent_det.type="GoodsIn" AND category_id=:category_id',

                        'params' => array(':month' => $month, ':year' => $year, ':category_id' => $categoryId),

                    )

                );

              //  $ctotgoodssum = (int)$goodsQuery->total_quantity;
                $ctotgoodssum = !empty($goodsQuery )?$goodsQuery->total_quantity:0;

            

                $confirmedQuery = OrderDetails::model()->with('parent_det')->find(

                    array(

                        'select' => 'SUM(quantity_confirmed) AS quantity_confirmed, SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,SUM(quantity_shipped) AS quantity_shipped, SUM(price*quantity_shipped*exchange_rate) AS amount_shipped',

                        'condition' => '(parent_det.month=:month AND parent_det.year=:year) AND category_id=:category_id',

                        'params' => array(':month' => $month, ':year' => $year, ':category_id' => $categoryId),

                    )

                );

               // $ctotal_confirmed_month = (int)$confirmedQuery->quantity_confirmed;
                $ctotal_confirmed_month = !empty($confirmedQuery )?$confirmedQuery->quantity_confirmed:0;

              //  $ctotal_confirmedamt_month = $confirmedQuery->amount_confirmed;
                $ctotal_confirmedamt_month = !empty($confirmedQuery )?$confirmedQuery->amount_confirmed:0;

                //$ctotal_shipped_month =(int)$confirmedQuery->quantity_shipped;
                $ctotal_shipped_month = !empty($confirmedQuery )?$confirmedQuery->quantity_shipped:0;

                //$ctotal_shippedamt_month = $confirmedQuery->amount_shipped;
                $ctotal_shippedamt_month = !empty($confirmedQuery )?$confirmedQuery->amount_shipped:0;

                //$cunshipped_sales =(int)UploadUnshippedDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']))->quantity;
                $modcunshipped_sales=UploadUnshippedDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']));
                $cunshipped_sales = !empty($modcunshipped_sales )?$modcunshipped_sales ->quantity:0;

                //$ctotal_bklog =(int)UploadBacklogDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']))->quantity;
                $modctotal_bklog=UploadBacklogDetails::model()->with('parent_det','product_det')->find(array('select'  =>'SUM(quantity) AS quantity','condition'=>'parent_det.month='.$month.' AND parent_det.year='.$year.' AND product_det.product_category_id='.(int)$eachcat['id']));
                $ctotal_bklog = !empty($modctotal_bklog )?$modctotal_bklog ->quantity:0;

                $cnet_goods = $ctotinvsum+$ctotgoodssum-($ctotal_bklog+$cunshipped_sales);

                $ctotreceived=$cnet_goods;

                $gtotordersum+=$ctotordersum;

                $gtotal_confirmed_month+=$ctotal_confirmed_month;

                $gtotorderamtsum+=$ctotorderamtsum;

                $gtotal_confirmedamt_month+=$ctotal_confirmedamt_month;

                $gtotal_shipped_month+=$ctotal_shipped_month;

                $gtotal_shippedamt_month+=$ctotal_shippedamt_month;

                $gtotinvsum+=$ctotinvsum;

                $gtotgoodssum+=$ctotgoodssum;

                $gunshipped_sales+=$cunshipped_sales;

                $gtotal_bklog+=$ctotal_bklog;

                $gnet_goods+=$cnet_goods;

                ($ctotreceived<0)?$ctotreceived=0:'';

                if($ctotordersum<$cnet_goods){

                    $excess=$ctotreceived-$ctotordersum;

                    $cshortfall=0;

                }else{

                    $cshortfall=$ctotreceived-$ctotordersum;

                    $cexcess=0;

                }

                $cinv_row .='!'.$ctotordersum.'!'.$ctotal_confirmed_month.'!'.$ctotal_shipped_month.'!'.$ctotinvsum.'!'.$ctotgoodssum.'!'.$cunshipped_sales.'!'.$ctotal_bklog.'!'.$cnet_goods.'!0!0';  

                $camt_row .='!'.$ctotorderamtsum.'!'.$ctotal_confirmedamt_month.'!'.$ctotal_shippedamt_month.'!!!!!!0!0';  

                

                $cind_sum=0;

                foreach($mod_dis as $eachdis){
                    
                    $cordersum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['quantity_ordered'];
                    $cdis_sum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['quantity_confirmed'];
                    $cquantity_shipped_sum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['quantity_shipped'];
                    $camtordersum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['amount_ordered'];
                    $camtdis_sum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['amount_confirmed'];
                    $camtquantity_shipped_sum =$arr_cat_detail[(int)$eachdis['id']][(int)$eachcat['id']]['amount_shipped'];
                    /*$cmodel_order_det = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(price*quantity_ordered*exchange_rate) AS amount_ordered, SUM(quantity_confirmed) AS quantity_confirmed,SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(price*quantity_shipped*exchange_rate) AS amount_shipped','condition'=>'parent_det.month="'.$month.'" AND parent_det.year="'.$year.'" AND parent_det.distributer_id="'.(int)$eachdis['id'].'"  AND category_id='.(int)$eachcat['id']));

                    //$cordersum = (int)$cmodel_order_det['quantity_ordered'];
                    $cordersum = !empty($cmodel_order_det )?$cmodel_order_det['quantity_ordered']:0;

                   // $cdis_sum = (int)$cmodel_order_det['quantity_confirmed'];
                    $cdis_sum = !empty($cmodel_order_det )?$cmodel_order_det['quantity_confirmed']:0;

                   // $cquantity_shipped_sum = (int)$cmodel_order_det['quantity_shipped'];
                    $cquantity_shipped_sum = !empty($cmodel_order_det )?$cmodel_order_det['quantity_shipped']:0;

                    //$camtordersum = $cmodel_order_det['amount_ordered'];
                    $camtordersum = !empty($cmodel_order_det )?$cmodel_order_det['amount_ordered']:0;

                    //$camtdis_sum = $cmodel_order_det['amount_confirmed'];
                    $camtdis_sum = !empty($cmodel_order_det )?$cmodel_order_det['amount_confirmed']:0;

                    //$camtquantity_shipped_sum = $cmodel_order_det['amount_shipped'];
                    $camtquantity_shipped_sum = !empty($cmodel_order_det )?$cmodel_order_det['amount_shipped']:0;
*/
                    if($cdis_sum<0){

                        $cdis_sum=0;

                    }

                    $cind_sum+=(int)$cordersum;

                    $cinv_row .='!'.(int)$cordersum.'!'.$cdis_sum.'!'.$cquantity_shipped_sum;

                    $camt_row .='!'.Yii::app()->numberFormatter->formatCurrency($camtordersum,"").'!'.Yii::app()->numberFormatter->formatCurrency($camtdis_sum,"").'!'.Yii::app()->numberFormatter->formatCurrency($camtquantity_shipped_sum,"");

                }

                $list[$j++] = $cinv_row;

                $list[$j++] = $camt_row;

            }

            

           

            $ctot_row ='Total _ QTY !!!';

            $ctot_row .='!'.$gtotordersum.'!'.$gtotal_confirmed_month.'!'.$gtotal_shipped_month.'!'.$gtotinvsum.'!'.$gtotgoodssum.'!'.$gunshipped_sales.'!'.$gtotal_bklog.'!'.$gnet_goods.'!0!0';  

            $camttot_row ='Total - AMT !!!';

            $camttot_row .='!'.$gtotorderamtsum.'!'.$gtotal_confirmedamt_month.'!'.$gtotal_shippedamt_month.'!!!!!!0!0';

                

            foreach($mod_dis as $eachdis){

                $ctot_model_order_det = OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered,SUM(price*quantity_ordered*exchange_rate) AS amount_ordered, SUM(quantity_confirmed) AS quantity_confirmed,SUM(price*quantity_confirmed*exchange_rate) AS amount_confirmed,SUM(quantity_shipped) AS quantity_shipped,SUM(price*quantity_shipped*exchange_rate) AS amount_shipped','condition'=>'parent_det.month="'.$month.'" AND parent_det.year="'.$year.'" AND parent_det.distributer_id="'.(int)$eachdis['id'].'"'));

                $ctot_ordersum =(int)$ctot_model_order_det['quantity_ordered'];

                $ctot_dis_sum =(int)$ctot_model_order_det['quantity_confirmed'];

                $ctot_quantity_shipped_sum =(int)$ctot_model_order_det['quantity_shipped'];

                $ctamtordersum = $ctot_model_order_det['amount_ordered'];

                $ctamtdis_sum = $ctot_model_order_det['amount_confirmed'];

                $ctamtquantity_shipped_sum = $ctot_model_order_det['amount_shipped'];

                $ctot_row .='!'.(int)$ctot_ordersum.'!'.$ctot_dis_sum.'!'.$ctot_quantity_shipped_sum;

                $camttot_row .='!'.Yii::app()->numberFormatter->formatCurrency($ctamtordersum,"").'!'.Yii::app()->numberFormatter->formatCurrency($ctamtdis_sum,"").'!'.Yii::app()->numberFormatter->formatCurrency($ctamtquantity_shipped_sum,"");

            }

            $list[$j++] = $ctot_row;

            $list[$j++] = $camttot_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='supply_confirmation_'.$month.'_'.$year.'.csv';

            header('Content-disposition: attachment; filename="'.$file_name.'"');

            readfile($filename);

            Yii::app()->end();

            return;

        }else{

            throw new CHttpException(404, 'Page not found.');

        }

    }

}