<?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'),
                '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');
            $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;
                $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['product_id']))->quantity_ordered;
                $totinvsum = (int)$row['inventory'];
                $totgoodssum =(int)$row['goods_in'];
                $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;
                $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;
                $unshipped_sales =(int)$row['unshipped'];
                $total_bklog =(int)$row['backlog'];
                    
                $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','condition'=>'parent_det.month="'.$month.'" AND parent_det.year="'.$year.'" AND parent_det.distributer_id="'.(int)$eachdis['id'].'" AND product_id="'.(int)$row['product_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'];
                    if($dis_sum<0){
                        $dis_sum=0;
                    }
                    $inv_row .='!'.(int)$ordersum.'!'.$dis_sum.'!'.$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=$gtotal_shippedamt_month=$gtotorderamtsum=$gtotal_confirmedamt_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 = (int)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 =(int)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 =(int)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.'!!!!!!!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 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);
                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','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'];
                    if($dis_sum<0){
                        $dis_sum=0;
                    }
                    $inv_row .='!'.(int)$ordersum.'!'.$dis_sum.'!'.$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 = (int)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 =(int)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 =(int)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));;
	}
}