<?php
require_once(__DIR__ . '/../../extensions/Spreadsheet/autoload.php'); 
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Style\Border;
class GmcuploadController 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', 'UploadGMC', 'SaveGMCUpload', 'UploadDocuments', 'DownloadGmcFormat'//'DownloadFormat', 
                                        ,'uploadNewGMC','DownloadNewGmcFormat','SaveNewGMCUpload','DownloadNewGMCExcel','DownloadAllGMCExcel'),
                'expression' => '$user->isMember()',
            ),
            array('deny', // deny all users
                'users' => array('*'),
            ),
        );
    }

    public function actionUploadGMC() {
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if (count($_GET) > 0) {
            $model = new GmcUploads();
            Yii::app()->clientScript->scriptMap = array('*jquery.js' => false, '*jquery.min.js' => false, 'jquery.min.js' => false, '*jquery-ui.js' => false, '*jquery-ui.min.js' => false, 'jquery-2.1.4.js' => false);
            $this->render('upload_form', array('model' => $model), false, false);
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionUploadNewGMC() {
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if (count($_GET) > 0) {
            $model = new GmcUploads();
            Yii::app()->clientScript->scriptMap = array('*jquery.js' => false, '*jquery.min.js' => false, 'jquery.min.js' => false, '*jquery-ui.js' => false, '*jquery-ui.min.js' => false, 'jquery-2.1.4.js' => false);
            $this->render('upload_new_form', array('model' => $model), false, false);
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }

    public function actionDownloadGmcFormat() {
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if (count($_GET) > 0) {
            $file = fopen("upload/test.csv", "w");
            $j = 0;
            $list[$j++] = 'PRODUCT CATEGORY!GMC Code!Model No!CBM!BASE CODE!New Till Year!New Till Month';
            $model_inventory = Inventory::model()->with('category_det')->findAll(array('condition' => 't.is_active="Y"'));
            foreach ($model_inventory as $row) {
                $list[$j++] = $row['category_det']['product_category'] . '!!!' . $row['gmc_code'] . '!!!' . $row['model_no'] . '!!!' . $row['cbm'] . '!!!' . $row['base_code']. '!!!' . $row['new_till_year']. '!!!' . $row['new_till_month'];
            }
            $j++;
            foreach ($list as $line) {
                fputcsv($file, explode('!!!', $line));
            }
            fclose($file);
            $filename = 'upload/test.csv';
            header("Content-Type: application/csv");
            header('Content-Disposition:attachment; filename="GMC_downloadformat.csv"');
            readfile($filename);
            Yii::app()->end();
            return;
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionDownloadNewGmcFormat() {
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        if (count($_GET) > 0) {
            $file = fopen("upload/test.csv", "w");
            $j = 0;
            $firstrow = 'Sort #(Current FY)!!!PRODUCT CATEGORY!!!GMC Code!!!Model No!!!CBM!!!BASE CODE!!!New Till Year!!!New Till Month!!!Color!!!Base Category Code!!!Base Category!!!Sub Category!!!Other 1!!!Other 2'.
            '!!!Series Qty wise!!!Series Amt wise!!!Other 3!!! Other 4!!!Description!!!M3!!!Length cm!!!Width cm!!!Height cm!!!KG (Gross)!!!BAR CODE!!!Origin!!!ABC CATEGORY Type(Current FY)!!!Order Lot to YCJ!!!ETA!!!Cable Modification!!!
            InventoryReportType(N-Normal,S-Strategic)!!!Is Confidential(Y/N)!!!Top Level Model(Y/N)!!!ABC CATEGORY Type(Next FY)!!!Sort #(Next FY)'/*.
            '!Old YCJ FCA!New YCJ FCA!YMGF L.I.C. On Old Price!YMGF L.I.C.'*/;
            $arr_months = Order::getmonths();
            
            /*$modMasRegion = MasRegion::model()->findAll(array('order'=>'id asc'));
            foreach($modMasRegion as $eachcat){
                $firstrow .='!'.$eachcat->region_name.' FOB'.'!'.$eachcat->region_name.' Margin On Old LIC'.'!'.$eachcat->region_name.' Margin' ;
            }*/
            $list[$j++] =$firstrow;
            $model_inventory = Inventory::model()->with('category_det')->findAll(array('condition' => 't.is_active="Y"'));
            // Fetch all distributors in a single query
            $modMasCategoryType = MasCategoryType::model()->findAll();
            $arr_categry = [];
            foreach ($modMasCategoryType as $d) {
                $arr_categry[$d->id] = $d->category_type_name;
            }
            //base_category_code,base_category_name,description,category_type_id, is_active,origin,barcode,gmc_colour,
            //sub_category,spiral_sub_category,psi_series,order_lot_to_ycj,cable_modeification,other_1,other_2,other_3,other_4,length, width,height,cbm,weight,
            foreach ($model_inventory as $row) {
                $eta_month='';
                /*if($row['goods_in_end_month']!=12)
                {
                    $eta_month=((int)$row['goods_in_end_month']+1);
                    $eta_year=(int) $row->goods_in_end_year;
                }else{
                    $eta_month=1;
                    $eta_year=(int) $row->goods_in_end_year+1;
                }*/
                $eta_month=$row['eta_month'];
                $eta_year=(int)$row->eta_year;
                $eta_month_name=$arr_months[$eta_month]. ' - ' .$eta_year;
                $list[$j++] = $row['gmc_sort_order'] . '!!!' .$row['category_det']['product_category'] . '!!!' . $row['gmc_code'] . '!!!' . $row['model_no'] . '!!!' . $row['cbm'] . '!!!' . $row['base_code']. '!!!' . $row['new_till_year']. '!!!' . $row['new_till_month']
                                . '!!!' . $row['gmc_colour']. '!!!' . $row['base_category_code']. '!!!' . $row['base_category_name']. '!!!' . $row['sub_category']. '!!!' . $row['other_1']. '!!!' . $row['other_2']
                                . '!!!' . $row['spiral_sub_category']. '!!!' . $row['psi_series']. '!!!' . $row['other_3']. '!!!' . $row['other_4']. '!!!' . $row['description']. '!!!' . $row['cbm']
                                . '!!!' . $row['length']. '!!!' . $row['width']. '!!!' . $row['height']. '!!!' . $row['weight']. '!!!' . $row['barcode']. '!!!' . $row['origin']
                                . '!!!' . $arr_categry[$row['category_type_id']]. '!!!' . $row['order_lot_to_ycj']. '!!!'. $eta_month_name.'!!!' . $row['cable_modeification']. '!!!'.$row['inventory_type'].'!!!'.$row['is_confidential'].'!!!'.$row['is_toplevel'].'!!!'.$arr_categry[$row['future_category_type_id']].'!!!'.$row['future_gmc_sort_order'].'!!!';
            }
            $j++;
            foreach ($list as $line) {
                fputcsv($file, explode('!!!', $line));
            }
            fclose($file);
            $filename = 'upload/test.csv';
            header("Content-Type: application/csv");
            header('Content-Disposition:attachment; filename="GMC_downloadformat.csv"');
            readfile($filename);
            Yii::app()->end();
            return;
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionSaveGMCUpload() {

        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if (count($_POST) > 0) {
            $model = new GmcUploads();
            $other = array();
            if (isset($_POST['GmcUploads'])) {
                if ((int) @$_POST['GmcUploads']['id'] > 0) {
                    $id = (int) @$_POST['GmcUploads']['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['GmcUploads'];
                $model->upload_date = date('Y-m-d');
                $file = $_POST['GmcUploads']['file_name'];
                $model->file_name = $file;
                $valid = $model->validate();
                $i = 0;
                $arr_products = [];
                $n = 1;
                if ($file != '') {
                    if (($handle = fopen("upload/gmc/" . $file, "r")) !== FALSE) {

                        while (($row = fgetcsv($handle)) !== FALSE) {

                            if ($n > 1) {
                                $product_category = trim($row[0], '\'"'); //productcategory
                                $category_id = (int) MasProductCategory::model()->find(array('condition' => 'product_category="' . $product_category . '"'))->id;
                                if ($category_id == 0) {
                                    $other[] = ' Category ' . $product_category . ' not added';
                                    $valid = false;
                                }
                                $gmc_code = trim($row[1]); //gmc
                                $model_no = trim($row[2]); //model
                                $mod_inv = Inventory::model()->find(array('condition' => 'gmc_code="' . $gmc_code . '"'));
                                if($row[5]==''||$row[6]==''){
                                    $other[] = ' New Till Month Or New Till Year for gmc ' . $gmc_code . ' not added';
                                    $valid = false;
                                }
                                if (!empty($mod_inv)) {
                                    $modelItems[$i] = Inventory::model()->findByPK((int) $mod_inv->id);
                                    $modelItems[$i]->product_category_id = (int) $category_id;
                                    $modelItems[$i]->model_no = $model_no;
                                    $modelItems[$i]->cbm = $row[3];
                                    $modelItems[$i]->base_code = $row[4];
                                    $modelItems[$i]->new_till_year = $row[5];
                                    $modelItems[$i]->new_till_month =$row[6];
                                    $modelItems[$i]->updated_by = Yii::app()->user->id;
                                    $modelItems[$i]->updated_date_time = date('Y-m-d H:i:s');
                                    $valid = $modelItems[$i]->validate() && $valid;
                                    $i++;
                                    if (in_array($gmc_code, $arr_products)) {
                                        $other[] = $gmc_code . ' GMC Repeated ';
                                        $valid = false;
                                    } else {
                                        $arr_products[] = $gmc_code;
                                    }
                                } else {
                                    $modelItems[$i] = new Inventory();
                                    $modelItems[$i]->product_category_id = (int) $category_id;
                                    $modelItems[$i]->gmc_code = $gmc_code;
                                    $modelItems[$i]->model_no = $model_no;
                                    $modelItems[$i]->cbm = $row[3];
                                    $modelItems[$i]->base_code = $row[4];
                                    $modelItems[$i]->new_till_year = $row[5];
                                    $modelItems[$i]->new_till_month =$row[6];
                                    $modelItems[$i]->goods_in_month = 0;
                                    $modelItems[$i]->goods_in_year = '0000';
                                    $modelItems[$i]->goods_in_end_month = 0;
                                    $modelItems[$i]->goods_in_end_year = '0000';
                                    $modelItems[$i]->backlog_month = 0;
                                    $modelItems[$i]->backlog_year = '0000';
                                    $modelItems[$i]->backlog_end_month = 0;
                                    $modelItems[$i]->backlog_end_year = '0000';
                                    $modelItems[$i]->is_active = 'Y';
                                    $modelItems[$i]->created_by = Yii::app()->user->id;
                                    $modelItems[$i]->created_date_time = date('Y-m-d H:i:s');
                                    $modelItems[$i]->updated_by = Yii::app()->user->id;
                                    $modelItems[$i]->updated_date_time = date('Y-m-d H:i:s');
                                    $valid = $modelItems[$i]->validate() && $valid;
                                    $i++;
                                }
                            }
                            $n++;
                        }
                        fclose($handle);
                    }
                } else {
                    $other[] = 'File Not Uploaded';
                    $valid = false;
                }
                /*echo '<pre>';
                print_r($modelItems);
                exit;*/
                if ($valid) {

                    $model->save();
                    if (!empty($modelItems)) {
                        foreach ($modelItems as $each) {
                            $each->save(false);
                        }
                    }
                    $jsonData['status'] = 'success';
                    $jsonData['frm'] = $_POST['GmcUploads'];
                    $jsonData['id'] = $model->id;
                    echo CJSON::encode($jsonData);
                } else {
                    $error1 = json_decode(CActiveForm::validate($model, '', false), true);
                    if (!empty($modelItems)) {
                        $error2 = json_decode(CActiveForm::validateTabular($modelItems, '', false), true);
                    } else {
                        $error2[] = "Please add atleast one line item";
                    }
                    $error = CJSON::encode(array_merge_recursive($error1, $error2, $other));
                    if ($error != '[]')
                        echo $error;
                }
            }
        }
        else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    
    public function actionSaveNewGMCUpload() {

        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        if (count($_POST) > 0) {
            $model = new GmcUploads();
            $other = array();
            if (isset($_POST['GmcUploads'])) {
                if ((int) @$_POST['GmcUploads']['id'] > 0) {
                    $id = (int) @$_POST['GmcUploads']['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['GmcUploads'];
                $model->upload_date = date('Y-m-d');
                $file = $_POST['GmcUploads']['file_name'];
                $model->file_name = $file;
                $valid = $model->validate();
                $i = 0;
                $arr_products = [];
                $n = 1;
                if ($file != '') {
                    if (($handle = fopen("upload/gmc/" . $file, "r")) !== FALSE) {

                        while (($row = fgetcsv($handle)) !== FALSE) {

                            if ($n > 1) {
                                $product_category = trim($row[1], '\'"'); //productcategory
                                $category_id = (int) MasProductCategory::model()->find(array('condition' => 'product_category="' . $product_category . '"'))->id;
                                if ($category_id == 0) {
                                    $other[] = ' Category ' . $product_category . ' not added';
                                    $valid = false;
                                }
                                $category_type_id = (int) MasCategoryType::model()->find(array('condition' => 'category_type_name="' . $row[26] . '"'))->id;
                                $category_type_id_fy = (int) MasCategoryType::model()->find(array('condition' => 'category_type_name="' . $row[34] . '"'))->id;
                                
                                $gmc_code = trim($row[2]); //gmc
                                $model_no = trim($row[3]); //model
                                $mod_inv = Inventory::model()->find(array('condition' => 'gmc_code="' . $gmc_code . '"'));
                                if($row[6]==''||$row[7]==''){
                                    $other[] = ' New Till Month Or New Till Year for gmc ' . $gmc_code . ' not added';
                                    $valid = false;
                                }
                                $eta_date_string = $row[28];
                                $timestamp = strtotime('01-' . $eta_date_string); // Create a timestamp, assuming day '01'
                                if ($timestamp !== false) {
                                    $eta_month = date('n', $timestamp); // 'n' is the numeric month without leading zeros (1 to 12)
                                    $eta_year = date('Y', $timestamp);  // 'Y' is the four-digit year
                                } else {
                                    // Handle parsing error, e.g., set to defaults or invalidate
                                    $eta_month = 1; 
                                    $eta_year = '2000';
                                }
                                if (!empty($mod_inv)) {
                                    $modelItems[$i] = Inventory::model()->findByPK((int) $mod_inv->id);
                                    $modelItems[$i]->product_category_id = (int) $category_id;
                                    $modelItems[$i]->gmc_sort_order = $row[0];
                                    if(!empty($model_no))
                                    $modelItems[$i]->model_no = $model_no;
                                    $modelItems[$i]->cbm = empty($row[4])?$row[19]:$row[4];
                                    $modelItems[$i]->base_code = $row[5];
                                    $modelItems[$i]->new_till_year = $row[6];
                                    $modelItems[$i]->new_till_month =$row[7];
                                    /*base_category_code,base_category_name,description,category_type_id, is_active,origin,barcode,gmc_colour,
            //sub_category,spiral_sub_category,psi_series,order_lot_to_ycj,cable_modeification,other_1,other_2,other_3,other_4,length, width,height,cbm,weight,
            . '!' . $row['gmc_colour']. '!' . $row['base_category_code']. '!' . $row['base_category_name']. '!' . $row['sub_category']. '!' . $row['other_1']. '!' . $row['other_2']
                                . '!' . $row['spiral_sub_category']. '!' . $row['psi_series']. '!' . $row['other_3']. '!' . $row['other_4']. '!' . $row['description']. '!' . $row['cbm']
                                . '!' . $row['length']. '!' . $row['width']. '!' . $row['height']. '!' . $row['weight']. '!' . $row['barcode']. '!' . $row['origin']
                                . '!' . $row['category_type_id']. '!' . $row['order_lot_to_ycj']. '!'.' '.'!' . $row['cable_modeification']. '!!!!';
            */
                                    if(!empty($row[8]))
                                    $modelItems[$i]->gmc_colour =$row[8];
                                    if(!empty($row[9]))
                                    $modelItems[$i]->base_category_code =$row[9];
                                    if(!empty($row[10]))
                                    $modelItems[$i]->base_category_name =$row[10];
                                    $modelItems[$i]->sub_category =$row[11];
                                    $modelItems[$i]->other_1 =$row[12];
                                    $modelItems[$i]->other_2 =$row[13];
                                    $modelItems[$i]->spiral_sub_category =$row[14];
                                    $modelItems[$i]->psi_series =$row[15];
                                    $modelItems[$i]->other_3 =$row[16];
                                    $modelItems[$i]->other_4 =$row[17];
                                    $modelItems[$i]->description =$row[18];
                                    $modelItems[$i]->length =(float)$row[20]/10;
                                    $modelItems[$i]->width =(float)$row[21]/10;
                                    $modelItems[$i]->height =(float)$row[22]/10;
                                    $modelItems[$i]->weight =(float)$row[23];
                                    $modelItems[$i]->barcode =$row[24];
                                    $modelItems[$i]->origin =$row[25];
                                    $modelItems[$i]->category_type_id =$category_type_id;
                                    $modelItems[$i]->order_lot_to_ycj =$row[27];
                                    $modelItems[$i]->eta_month = $eta_month;
                                    $modelItems[$i]->eta_year = $eta_year;
                                    $modelItems[$i]->cable_modeification =$row[29];
                                    $modelItems[$i]->inventory_type =$row[30];
                                    $modelItems[$i]->is_confidential =$row[31];
                                    $modelItems[$i]->is_toplevel =$row[32];
                                    $modelItems[$i]->comments =$row[33];
                                    $modelItems[$i]->future_category_type_id =$category_type_id_fy;
                                    $modelItems[$i]->future_gmc_sort_order =$row[35];
                                    $modelItems[$i]->updated_by = Yii::app()->user->id;
                                    $modelItems[$i]->updated_date_time = date('Y-m-d H:i:s');
                                    $valid = $modelItems[$i]->validate() && $valid;
                                    $i++;
                                    if (in_array($gmc_code, $arr_products)) {
                                        $other[] = $gmc_code . ' GMC Repeated ';
                                        $valid = false;
                                    } else {
                                        $arr_products[] = $gmc_code;
                                    }
                                } else {
                                    $modelItems[$i] = new Inventory();
                                    $modelItems[$i]->product_category_id = (int) $category_id;
                                    $modelItems[$i]->gmc_sort_order = $row[0];
                                    $modelItems[$i]->gmc_code = $gmc_code;
                                    $modelItems[$i]->model_no = $model_no;
                                    $modelItems[$i]->cbm = empty($row[4])?$row[19]:$row[4];
                                    $modelItems[$i]->base_code = $row[5];
                                    $modelItems[$i]->new_till_year = $row[6];
                                    $modelItems[$i]->new_till_month =$row[7];
                                    $modelItems[$i]->gmc_colour =$row[8];
                                    $modelItems[$i]->base_category_code =$row[9];
                                    $modelItems[$i]->base_category_name =$row[10];
                                    $modelItems[$i]->sub_category =$row[11];
                                    $modelItems[$i]->other_1 =$row[12];
                                    $modelItems[$i]->other_2 =$row[13];
                                    $modelItems[$i]->spiral_sub_category =$row[14];
                                    $modelItems[$i]->psi_series =$row[15];
                                    $modelItems[$i]->other_3 =$row[16];
                                    $modelItems[$i]->other_4 =$row[17];
                                    $modelItems[$i]->description =$row[18];
                                    $modelItems[$i]->length =$row[20]/10;
                                    $modelItems[$i]->width =$row[21]/10;
                                    $modelItems[$i]->height =$row[22]/10;
                                    $modelItems[$i]->weight =$row[23];
                                    $modelItems[$i]->barcode =$row[24];
                                    $modelItems[$i]->origin =$row[25];
                                    $modelItems[$i]->category_type_id =$category_type_id;
                                    $modelItems[$i]->order_lot_to_ycj =$row[27];
                                    //$modelItems[$i]->eta =$row[28];
                                    $modelItems[$i]->eta_month =$row[28];
                                    $modelItems[$i]->eta_year =$row[28];
                                    $modelItems[$i]->cable_modeification =$row[29];
                                    $modelItems[$i]->inventory_type =$row[30];
                                    $modelItems[$i]->is_confidential =$row[31];
                                    $modelItems[$i]->is_toplevel =$row[32];
                                    $modelItems[$i]->comments =$row[33];
                                    $modelItems[$i]->future_category_type_id =$category_type_id_fy;
                                    $modelItems[$i]->future_gmc_sort_order =$row[35];
                                    $modelItems[$i]->goods_in_month = 0;
                                    $modelItems[$i]->goods_in_year = '0000';
                                    $modelItems[$i]->goods_in_end_month = 0;
                                    $modelItems[$i]->goods_in_end_year = '0000';
                                    $modelItems[$i]->backlog_month = 0;
                                    $modelItems[$i]->backlog_year = '0000';
                                    $modelItems[$i]->backlog_end_month = 0;
                                    $modelItems[$i]->backlog_end_year = '0000';
                                    $modelItems[$i]->is_active = 'Y';
                                    $modelItems[$i]->created_by = Yii::app()->user->id;
                                    $modelItems[$i]->created_date_time = date('Y-m-d H:i:s');
                                    $modelItems[$i]->updated_by = Yii::app()->user->id;
                                    $modelItems[$i]->updated_date_time = date('Y-m-d H:i:s');
                                    $valid = $modelItems[$i]->validate() && $valid;
                                    $i++;
                                }
                            }
                            $n++;
                        }
                        fclose($handle);
                    }
                } else {
                    $other[] = 'File Not Uploaded';
                    $valid = false;
                }
                /*echo '<pre>';
                print_r($modelItems);
                exit;*/
                if ($valid) {

                    $model->save();
                    if (!empty($modelItems)) {
                        foreach ($modelItems as $each) {
                            $each->save(false);
                        }
                    }
                    $jsonData['status'] = 'success';
                    $jsonData['frm'] = $_POST['GmcUploads'];
                    $jsonData['id'] = $model->id;
                    echo CJSON::encode($jsonData);
                } else {
                    $error1 = json_decode(CActiveForm::validate($model, '', false), true);
                    if (!empty($modelItems)) {
                        $error2 = json_decode(CActiveForm::validateTabular($modelItems, '', false), true);
                    } else {
                        $error2[] = "Please add atleast one line item";
                    }
                    $error = CJSON::encode(array_merge_recursive($error1, $error2, $other));
                    if ($error != '[]')
                        echo $error;
                }
            }
        }
        else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionDownloadNewGMCExcel(){
        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet();
        $rowNumber = 1;
        function setData($worksheet, $erow, $data) {
            foreach ($data as $column => $value) {
                $cell = $column . $erow;
                $worksheet->setCellValue($cell, $value);
            }
        }
        function setFillColor($worksheet, $range, $color) {
            $worksheet->getStyle($range)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB($color);
        }
        // Merge and set title
        //$activeWorksheet->mergeCells('A1:BI1')->setCellValue('A1', 'FIXED ORDER FORM');
        // Set column headers
            $columnHeaders = [
                'A' => 'Sort #(Current FY)',//0
                'B' => 'PRODUCT CATEGORY',//1
                'C' => 'GMC Code',//2
                'D' => 'Model No',
                'E' => 'CBM',
                'F' => 'BASE CODE',
                'G' => 'New Till Year',
                'H' => 'New Till Month',
                'I' => 'Color',
                'J' => 'Base Category Code' ,
                'K' => 'Base Category',//10
                'L' => 'Sub Category',
                'M' => 'Other 1',
                'N' => 'Other 2',
                'O' => 'Series Qty wise',
                'P' => 'Series Amt wise',//15
                'Q' => 'Other 3',
                'R' => 'Other 4',
                'S' => 'Description',
                'T' => 'M3',
                'U' => 'Length cm',//20
                'V' => 'Width cm',
                'W' => 'Height cm',
                'X' => 'KG (Gross)',
                'Y' => 'BAR CODE',
                'Z' => 'Origin',//25
                'AA' => 'ABC CATEGORY Type(Current FY)',//26 
                'AB' => 'Order Lot to YCJ',//27
                'AC' => 'ETA',
                'AD' => 'Cable Modification',//29
                'AE' => 'InventoryReportType(N-Normal,S-Strategic)',//30
                'AF' => 'Is Confidential(Y/N)',//31
                'AG' => 'Top Level Model(Y/N)',//32
                'AH' => 'Comments',
                'AI' => 'ABC CATEGORY Type(Next FY)',
                'AJ' => 'Sort #(Next FY)'
            ];
            setData($activeWorksheet, $rowNumber, $columnHeaders);
            $rowNumber++;
            $activeWorksheet->getStyle("A1:AJ1")->getFont()->setBold(true);
            $activeWorksheet->freezePane('L1');
            $arr_months = Order::getmonths();
            $model_inventory = Inventory::model()->with('category_det')->findAll(array('condition' => 't.is_active="Y"'));
            // Fetch all distributors in a single query
            $modMasCategoryType = MasCategoryType::model()->findAll();
            $arr_categry = [];
            foreach ($modMasCategoryType as $d) {
                $arr_categry[$d->id] = $d->category_type_name;
            }
            foreach ($model_inventory as $row) {
                $eta_month=$row['eta_month'];
                $eta_year=(int)$row->eta_year;
                
                $eta_month_name=$arr_months[$eta_month]. ' - ' .$eta_year;
                $columnHeaders = [
                    'A' => $row['gmc_sort_order'],
                    'B' => $row['category_det']['product_category'],
                    'C' => $row['gmc_code'],
                    'D' => $row['model_no'],
                    'E' => $row['cbm'],
                    'F' => $row['base_code'],
                    'G' => $row['new_till_year'],
                    'H' => $row['new_till_month'],
                    'I' => $row['gmc_colour'],
                    'J' => $row['base_category_code'],
                    'K' => $row['base_category_name'],
                    'L' => $row['sub_category'],
                    'M' => $row['other_1'],
                    'N' => $row['other_2'],
                    'O' => $row['spiral_sub_category'], // This corresponds to 'Series Qty wise' in the previous header array
                    'P' => $row['psi_series'],          // This corresponds to 'Series Amt wise'
                    'Q' => $row['other_3'],
                    'R' => $row['other_4'],
                    'S' => $row['description'],
                    'T' => $row['cbm'],                 // Note: CBM is present in both E and T based on the list order
                    'U' => ($row['length']),
                    'V' => ($row['width']),
                    'W' => ($row['height']),
                    'X' => ($row['weight']),
                    'Y' => $row['barcode'],
                    'Z' => $row['origin'],
                    'AA' => $arr_categry[$row['category_type_id']],
                    'AB' => $row['order_lot_to_ycj'],
                    'AC' => $eta_month_name,
                    'AD' => $row['cable_modeification'],
                    'AE' => $row['inventory_type'],
                    'AF' => $row['is_confidential'],
                    'AG' => $row['is_toplevel'],
                    'AH' => $row['comments'],
                    'AI' => $arr_categry[$row['future_category_type_id']],
                    'AJ' => $row['future_gmc_sort_order']
                ];
                
                                    
                setData($activeWorksheet, $rowNumber, $columnHeaders);
                $rowNumber++;
                
            }
            $highestRow = $activeWorksheet->getHighestRow();
            // Set thiN border line from A1 to LAST
            $allColumnsRange = "A1:AJ{$highestRow}";
            $activeWorksheet->getStyle($allColumnsRange)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
            setFillColor($activeWorksheet, 'A1:AJ1', '99ccff'); // Dark blue
            
             $range_barcode = "Y2:Y{$highestRow}";
            // Set comma-separated number format for the ranges
            $activeWorksheet->getStyle($range_barcode)
                    ->getNumberFormat()
                    ->setFormatCode('##');

            // Create a writer and save the spreadsheet to a file
            $writer = new Xlsx($spreadsheet);
            $file_name = 'GMC_New_Excel.xlsx';
            // Create a writer
            // Set the appropriate HTTP headers for Excel file download
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename="' . $file_name . '"');
            header('Cache-Control: max-age=0');
            // Save the spreadsheet to the output (i.e., it will trigger a file download)
            $writer->save('php://output');
            return;
    }
    public function actionDownloadAllGMCExcel(){
        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet();
        $rowNumber = 1;
        function setDatas($worksheet, $erow, $data) {
            foreach ($data as $column => $value) {
                $cell = $column . $erow;
                $worksheet->setCellValue($cell, $value);
            }
        }
        function setFillColors($worksheet, $range, $color) {
            $worksheet->getStyle($range)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB($color);
        }
        // Set column headers
            $columnHeaders = [
                'A' => 'Sort #(Current FY)',//0
                'B' => 'PRODUCT CATEGORY',//1
                'C' => 'GMC Code',//2
                'D' => 'Model No',
                'E' => 'CBM',
                'F' => 'BASE CODE',
                'G' => 'New Till Year',
                'H' => 'New Till Month',
                'I' => 'Color',
                'J' => 'Base Category Code' ,
                'K' => 'Base Category',//10
                'L' => 'Sub Category',
                'M' => 'Other 1',
                'N' => 'Other 2',
                'O' => 'Series Qty wise',
                'P' => 'Series Amt wise',//15
                'Q' => 'Other 3',
                'R' => 'Other 4',
                'S' => 'Description',
                'T' => 'M3',
                'U' => 'Length cm',//20
                'V' => 'Width cm',
                'W' => 'Height cm',
                'X' => 'KG (Gross)',
                'Y' => 'BAR CODE',
                'Z' => 'Origin',//25
                'AA' => 'CATEGORY Type',//26 
                'AB' => 'Order Lot to YCJ',//27
                'AC' => 'ETA',
                'AD' => 'Cable Modification',//29
                'AE' => 'InventoryReportType(N-Normal,S-Strategic)',//30
                'AF' => 'Is Confidential(Y/N)',//31
                'AG' => 'Top Level Model(Y/N)',//32
                'AH' => 'Comments',
                'AI' => 'Status',
                'AJ' => 'ABC Category Type(Next FY)',
                'AK' => 'Sort #(Next FY)',
                'AL' => 'GMC Type',
                'AM' => 'Goods In Start Month',
                'AN' => 'Goods In Start Year',
                'AO' => 'Goods In End Month',
                'AP' => 'Goods In End Year',
                'AQ' => 'Backlog Start Month',
                'AR' => 'Backlog Start Year',
                'AS' => 'Backlog End Month',
                'AT' =>' Backlog End Year',
            ];
            setDatas($activeWorksheet, $rowNumber, $columnHeaders);
            $rowNumber++;
            $activeWorksheet->getStyle("A1:AT1")->getFont()->setBold(true);
            $activeWorksheet->freezePane('L1');
            $activeWorksheet->getColumnDimension('AT')->setWidth(25);
            $arr_months = Order::getmonths();
            $model_inventory = Inventory::model()->with('category_det')->findAll();
            // Fetch all distributors in a single query
            $modMasCategoryType = MasCategoryType::model()->findAll();
            $arr_categry = [];
            $arr_status=array('Y' => 'Active', 'N' => 'Block', 'D' => 'Fully Discontinued');
            foreach ($modMasCategoryType as $d) {
                $arr_categry[$d->id] = $d->category_type_name;
            }
            foreach ($model_inventory as $row) {
                $eta_month=$row['eta_month'];
                $eta_year=(int)$row->eta_year;
                
                $eta_month_name=$arr_months[$eta_month]. ' - ' .$eta_year;
                $goods_in_month_name = '';
            if (!empty($row['goods_in_month'])) {
                $goods_in_month_name = $arr_months[(int)$row['goods_in_month']];
            }

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

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

            $backlog_end_month_name = '';
            if (!empty($row['backlog_end_month'])) {
                $backlog_end_month_name = $arr_months[(int)$row['backlog_end_month']];
            }
                $columnHeaders = [
                    'A' => $row['gmc_sort_order'],
                    'B' => $row['category_det']['product_category'],
                    'C' => $row['gmc_code'],
                    'D' => $row['model_no'],
                    'E' => $row['cbm'],
                    'F' => $row['base_code'],
                    'G' => $row['new_till_year'],
                    'H' => $row['new_till_month'],
                    'I' => $row['gmc_colour'],
                    'J' => $row['base_category_code'],
                    'K' => $row['base_category_name'],
                    'L' => $row['sub_category'],
                    'M' => $row['other_1'],
                    'N' => $row['other_2'],
                    'O' => $row['spiral_sub_category'], // This corresponds to 'Series Qty wise' in the previous header array
                    'P' => $row['psi_series'],          // This corresponds to 'Series Amt wise'
                    'Q' => $row['other_3'],
                    'R' => $row['other_4'],
                    'S' => $row['description'],
                    'T' => $row['cbm'],                 // Note: CBM is present in both E and T based on the list order
                    'U' => ($row['length']),
                    'V' => ($row['width']),
                    'W' => ($row['height']),
                    'X' => ($row['weight']),
                    'Y' => $row['barcode'],
                    'Z' => $row['origin'],
                    'AA' => $arr_categry[$row['category_type_id']],
                    'AB' => $row['order_lot_to_ycj'],
                    'AC' => $eta_month_name,
                    'AD' => $row['cable_modeification'],
                    'AE' => $row['inventory_type'],
                    'AF' => $row['is_confidential'],
                    'AG' => $row['is_toplevel'],
                    'AH' => $row['comments'],
                    'AI' => $arr_status[$row['is_active']],
                    'AJ' => $arr_categry[$row['future_category_type_id']],
                    'AK' => $row['future_gmc_sort_order'],
                    'AL' => $row['gmc_type'],
                    'AM' => $goods_in_month_name,
                    'AN' => $row['goods_in_year'],
                    'AO' => $goods_in_end_month_name,
                    'AP' => $row['goods_in_end_year'],
                    'AQ' => $backlog_month_name,
                    'AR' => $row['backlog_year'],
                    'AS' => $backlog_end_month_name,
                    'AT' => $row['backlog_end_year']
                ];
                setDatas($activeWorksheet, $rowNumber, $columnHeaders);
                $rowNumber++;
                
            }
            $highestRow = $activeWorksheet->getHighestRow();
            // Set thiN border line from A1 to LAST
            $allColumnsRange = "A1:AT{$highestRow}";
            $activeWorksheet->getStyle($allColumnsRange)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
            setFillColors($activeWorksheet, 'A1:AT1', '99ccff'); // Dark blue
            
             $range_barcode = "Y2:Y{$highestRow}";
            // Set comma-separated number format for the ranges
            $activeWorksheet->getStyle($range_barcode)
                    ->getNumberFormat()
                    ->setFormatCode('##');

            // Create a writer and save the spreadsheet to a file
            $writer = new Xlsx($spreadsheet);
            $file_name = 'All_GMC_Excel.xlsx';
            // Create a writer
            // Set the appropriate HTTP headers for Excel file download
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename="' . $file_name . '"');
            header('Cache-Control: max-age=0');
            // Save the spreadsheet to the output (i.e., it will trigger a file download)
            $writer->save('php://output');
            return;
    }
}
