<?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;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
class InventoryreportController 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::app()->request->csrfToken . '": "' . 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('stockIndex', 'baseinvIndex','budgetIndex','Index',
				'GetstockList','GetbaseinventoryList','GetBudgetList', 'GetList',
				'StockCreate','BudgetCreate','BaseInventoryCreate','Create' ,
				'SaveBudgetUpload','SaveBaseInventoryUpload','SaveStockUpload','Save',
				'DeleteBudgetDetails','DeleteBIDetails','deleteStockDetails',
				'UploadDocuments','DownloadInventoryFinalReport','downloadRawFile'
                ),
				'expression' => '$user->isMember()',
			),
			array(
				'deny', // deny all users
				'users' => array('*'),
			),
		);
	}

	public function actionIndex()
	{
		//if (isset(Yii::app()->user->havePermissionOn['master_category_type_name_view']) || Yii::app()->user->authorised_user == 1) {
		$_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
		if (count($_GET) > 0) {
			$this->render('index');
		} else {
			throw new CHttpException(404, 'Page not found.');
		}
		/*} else {
			throw new CHttpException(401, 'Unauthorized Access');
		}*/
	}
	public function actionstockIndex()
	{
		//if (isset(Yii::app()->user->havePermissionOn['master_category_type_name_view']) || Yii::app()->user->authorised_user == 1) {
		$_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
		if (count($_GET) > 0) {
			$this->render('stockindex');
		} else {
			throw new CHttpException(404, 'Page not found.');
		}
		/*} else {
			throw new CHttpException(401, 'Unauthorized Access');
		}*/
	}
	public function actionbaseinvIndex()
	{
		//if (isset(Yii::app()->user->havePermissionOn['master_category_type_name_view']) || Yii::app()->user->authorised_user == 1) {
		$_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
		if (count($_GET) > 0) {
			$this->render('baseinventoryindex');
		} else {
			throw new CHttpException(404, 'Page not found.');
		}
		/*} else {
			throw new CHttpException(401, 'Unauthorized Access');
		}*/
	}
	public function actionbudgetIndex()
	{
		//if (isset(Yii::app()->user->havePermissionOn['master_category_type_name_view']) || Yii::app()->user->authorised_user == 1) {
		$_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);

		if (count($_GET) > 0) {
			$this->render('budgetindex');
		} else {
			throw new CHttpException(404, 'Page not found.');
		}
		/*} else {
			throw new CHttpException(401, 'Unauthorized Access');
		}*/
	}
	
	public function actionCreate() {

        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_GET) > 0) {
            $model = new InventoryReport();
            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 actionBaseInventoryCreate() {
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_GET) > 0) {
            $model = new InventoryReportBaseinventoryUpload();
            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('bi_upload_form', array('model' => $model), false, false);
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }public function actionStockCreate() {

        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_GET) > 0) {
            $model = new InventoryReportStockUpload();
            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('stock_upload_form', array('model' => $model), false, false);
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionBudgetCreate() {
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_GET) > 0) {
            $model = new InventoryReportBudgetUpload();
            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('budget_upload_form', array('model' => $model), false, false);
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionGetBudgetList() {

        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);

        if (count($_POST) > 0) {

            $model = new InventoryReportBudgetUpload();

            $draw = $_POST['draw'];
            $offset = $_POST['start'];
            $limit = $_POST['length'];
            $columns = array(
                // datatable column index  => database column name

                0 => 't.id',
                1 => 't.inv_month',
                2 => 't.inv_year',
            );

            $filterColumn = $columns[$_POST['order'][0]['column']];

            $filterOrder = $_POST['order'][0]['dir'];

            $orderBy = $filterColumn . " " . $filterOrder;

            $condition = '1=1';

            $arr_months = Order::getmonths();

            if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {

                $searchString = $_POST['search']['value'];

                $condition .= " and (inv_month LIKE '%" . $searchString . "%' OR inv_year LIKE '%" . $searchString . "%')";
            }

            $count = $model->count(array('condition' => $condition));

            $model = $model->findAll(array('condition' => $condition, 'limit' => $limit, 'offset' => $offset, 'order' => $orderBy));

            //$count =$model->count();

            $recordsTotal = $count;

            $recordsFiltered = $count;

            $data['draw'] = $draw;

            $data['recordsTotal'] = $recordsTotal;

            $data['recordsFiltered'] = $recordsFiltered;

            $rowData = array();

            $m = 1;

            foreach ($model as $row) {

                $div = '';
                if($row['is_used']!="Y"){
                    $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" onclick="deleteBudgetDetails(' . $row['id'] . ')"><i class="fa fa-trash"></i></button>';
                }
                $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" title="Download Raw File" onclick="downloadRawFile(\'' . $row['budget_file'] . '\');"><i class="fa fa-arrow-down"></i></button>';
                
                $rowData[] = array($m, $arr_months[(int) $row['inv_month']], $row['inv_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 actionGetList() {

        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);

        if (count($_POST) > 0) {

            $model = new InventoryReport();

            $draw = $_POST['draw'];

            $offset = $_POST['start'];

            $limit = $_POST['length'];

            $columns = array(
                // datatable column index  => database column name

                0 => 't.id',
                1 => 't.inv_month',
                2 => 't.inv_year',
            );

            $filterColumn = $columns[$_POST['order'][0]['column']];

            $filterOrder = $_POST['order'][0]['dir'];

            $orderBy = $filterColumn . " " . $filterOrder;

            $condition = '1=1';

            $arr_months = Order::getmonths();

            if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {

                $searchString = $_POST['search']['value'];

                $condition .= " and (inv_month LIKE '%" . $searchString . "%' OR inv_year LIKE '%" . $searchString . "%')";
            }

            $count = $model->count(array('condition' => $condition));

            $model = $model->findAll(array('condition' => $condition, 'limit' => $limit, 'offset' => $offset, 'order' => $orderBy));

            //$count =$model->count();

            $recordsTotal = $count;

            $recordsFiltered = $count;

            $data['draw'] = $draw;

            $data['recordsTotal'] = $recordsTotal;

            $data['recordsFiltered'] = $recordsFiltered;

            $rowData = array();

            $m = 1;

            foreach ($model as $row) {

                $div = '';
                $div .= '<button type="button" class="btn btn-xs btn-primary" title="Inventory Report Final" onclick="window.open($(\'#liveSite\').val() + \'index.php?r=inventoryreport/DownloadInventoryFinalReport&inv_report_id=' . $row['id'] . '\', \'_blank\');">
                <i class="fa fa-file-excel-o"></i>
                </button>';
                $rowData[] = array($m, $arr_months[(int) $row['inv_month']], $row['inv_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 actionGetbaseinventoryList() {

        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {

            $model = new InventoryReportBaseinventoryUpload();

            $draw = $_POST['draw'];

            $offset = $_POST['start'];

            $limit = $_POST['length'];

            $columns = array(
                // datatable column index  => database column name

                0 => 't.id',
                1 => 't.inv_month',
                2 => 't.inv_year',
            );

            $filterColumn = $columns[$_POST['order'][0]['column']];

            $filterOrder = $_POST['order'][0]['dir'];

            $orderBy = $filterColumn . " " . $filterOrder;

            $condition = '1=1';

            $arr_months = Order::getmonths();

            if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {

                $searchString = $_POST['search']['value'];

                $condition .= " and (inv_month LIKE '%" . $searchString . "%' OR inv_year LIKE '%" . $searchString . "%')";
            }

            $count = $model->count(array('condition' => $condition));

            $model = $model->findAll(array('condition' => $condition, 'limit' => $limit, 'offset' => $offset, 'order' => $orderBy));

            //$count =$model->count();

            $recordsTotal = $count;

            $recordsFiltered = $count;

            $data['draw'] = $draw;

            $data['recordsTotal'] = $recordsTotal;

            $data['recordsFiltered'] = $recordsFiltered;

            $rowData = array();

            $m = 1;

            foreach ($model as $row) {

                $div = '';
                if($row['is_used']!="Y"){
                    $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" onclick="deleteBIDetails(' . $row['id'] . ')"><i class="fa fa-trash"></i></button>';
                }
                $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" title="Download Raw File" onclick="downloadRawFile(\'' . $row['bi_file'] . '\')"><i class="fa fa-arrow-down"></i></button>';
                /*$div .= '<button type="button" class="btn btn-xs btn-primary" title="Inventory Report Final" onclick="window.open($(\'#liveSite\').val() + \'index.php?r=inventoryreport/DownloadInventoryFinalReport&inv_report_id=' . $row['id'] . '\', \'_blank\');">
                <i class="fa fa-file-excel-o"></i>
                </button>';*/

                $rowData[] = array($m, $arr_months[(int) $row['inv_month']], $row['inv_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 actionGetstockList() {

        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {

            $model = new InventoryReportStockUpload();

            $draw = $_POST['draw'];

            $offset = $_POST['start'];

            $limit = $_POST['length'];

            $columns = array(
                // datatable column index  => database column name

                0 => 't.id',
                1 => 't.inv_month',
                2 => 't.inv_year',
            );

            $filterColumn = $columns[$_POST['order'][0]['column']];

            $filterOrder = $_POST['order'][0]['dir'];

            $orderBy = $filterColumn . " " . $filterOrder;

            $condition = '1=1';

            $arr_months = Order::getmonths();

            if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {

                $searchString = $_POST['search']['value'];

                $condition .= " and (inv_month LIKE '%" . $searchString . "%' OR inv_year LIKE '%" . $searchString . "%')";
            }

            $count = $model->count(array('condition' => $condition));

            $model = $model->findAll(array('condition' => $condition, 'limit' => $limit, 'offset' => $offset, 'order' => $orderBy));

            //$count =$model->count();

            $recordsTotal = $count;

            $recordsFiltered = $count;

            $data['draw'] = $draw;

            $data['recordsTotal'] = $recordsTotal;

            $data['recordsFiltered'] = $recordsFiltered;

            $rowData = array();

            $m = 1;

            foreach ($model as $row) {

                $div = '';
                if($row['is_used']!="Y"){
                    $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" onclick="deleteStockDetails(' . $row['id'] . ')"><i class="fa fa-trash"></i></button>';
                }
                
                $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" onclick="downloadRawFile(\'' . $row['stock_file'] . '\')" title="Download Raw File"><i class="fa fa-arrow-down"></i></button>';
                /*if (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["orders_view"])) {

                    $div .= ' <button class="btn btn-primary btn-xs" title="View Order" onclick="vieworder(' . $row['id'] . ');"><i class="fa fa-eye"></i></button>';
                }

               $div .= '<button type="button" class="btn btn-xs btn-primary" title="Inventory Report Final" onclick="window.open($(\'#liveSite\').val() + \'index.php?r=inventoryreport/DownloadInventoryFinalReport&inv_report_id=' . $row['id'] . '\', \'_blank\');">
                <i class="fa fa-file-excel-o"></i>
                </button>';*/

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

                    $rowdistributer_id = $row["distributer_id"];
                    $rowyear = $row["year"];
                    $rowmonth = $row["month"];

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

                    $div .= '<button type="button"  class="btn btn-xs btn-primary" title="Download Excel" onclick="window.open($(\'#liveSite\').val()+\'index.php?r=order/DownloadOrderFormatExcel&type=2&distributer_id=' . $rowdistributer_id . '&year=' . $rowyear . '&month=' . $rowmonth . '\', \'_blank\');" title="Uploaded"><i class="fa fa-file-excel-o"></i></button>';

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

                $rowData[] = array($m, $arr_months[(int) $row['inv_month']], $row['inv_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.');
        }
    }
    
    //save inventory Report
    public function actionSave(){
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {
            $model = new InventoryReport();
            $other = array();
            if (isset($_POST['InventoryReport'])) {
                if ((int) @$_POST['InventoryReport']['id'] > 0) {
                    $id = (int) @$_POST['InventoryReport']['id'];
                    $model = $model->findByPk($id); 
                }
                $model->created_by = Yii::app()->user->id;
                $model->created_date_time = date('Y-m-d H:i:s');
                $model->attributes = $_POST['InventoryReport'];
                $valid = $model->validate();$budget_his = new InventoryReportBudgetUpload();
                $budget_his = $budget_his->find(['condition'=>'inv_month="'.$model->inv_month.'" AND inv_year="'.$model->inv_year.'" AND is_completed="Y"']);
                $stock_his = new InventoryReportStockUpload();
                $stock_his = $stock_his->find(['condition'=>'inv_month="'.$model->inv_month.'" AND inv_year="'.$model->inv_year.'" AND is_completed="Y"']);
                $baseinv_his = new InventoryReportBaseinventoryUpload();
                $baseinv_his = $baseinv_his->find(['condition'=>'inv_month="'.$model->inv_month.'" AND inv_year="'.$model->inv_year.'" AND is_completed="Y"']);
                $arr_months = Order::getmonths();
                if(empty($budget_his))
                {
                    $other[] = 'YMGF_PSI Budget File Not Uploaded/Not Completed for '.$model->inv_year.'-'.$arr_months[(int) $model->inv_month];
                    $valid = false;
                }else{
                    $model->budget_upload_id=$budget_his->id;
                }
                if(empty($stock_his))
                {
                    $other[] = 'YMGF_StockDetails File Not Uploaded/Not Completed for '.$model->inv_year.'-'.$arr_months[(int) $model->inv_month];
                    $valid = false;
                }else{
                    $model->stock_upload_id=$stock_his->id;
                }
                if(empty($baseinv_his))
                {
                    $other[] = 'BASE INVENTORY LIST File Not Uploaded/Not Completed for '.$model->inv_year.'-'.$arr_months[(int) $model->inv_month];
                    $valid = false;
                }else{
                    $model->baseinventory_upload_id=$baseinv_his->id;
                }
                if ($valid) {
                    $model->save();
                    InventoryReportBudgetUpload::model()->updateByPk($budget_his->id, ["is_used" => "Y"]);
                    InventoryReportStockUpload::model()->updateByPk($stock_his->id, ["is_used" => "Y"]);
                    InventoryReportBaseinventoryUpload::model()->updateByPk($baseinv_his->id, ["is_used" => "Y"]);
                    $jsonData['status'] = 'success';
                    $jsonData['frm'] = $_POST['InventoryReport'];
                    $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.');
        }
    }
    
    //save master file directly on first time
    public function actionSaveBudgetUpload(){
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {
            $model = new InventoryReportBudgetUpload();
            $other = array();
            function determineDateFormat($dateString, $formats) {
                foreach ($formats as $format) {
                    $date = DateTime::createFromFormat($format, $dateString);
                    if ($date && $date->format($format) === $dateString) {
                        return $format; // Return the matching format
                    }
                }
                return false; // No matching format found
            }

            if (isset($_POST['InventoryReportBudgetUpload'])) {
                if ((int) @$_POST['InventoryReportBudgetUpload']['id'] > 0) {
                    $id = (int) @$_POST['InventoryReportBudgetUpload']['id'];
                    $model = $model->findByPk($id); 
                }
                $model->uploaded_by = Yii::app()->user->id;
                $model->uploaded_date_time = date('Y-m-d H:i:s');
                $model->attributes = $_POST['InventoryReportBudgetUpload'];
                $file = $_POST['InventoryReportBudgetUpload']['budget_file'];
                $model->budget_file = $file;
                $valid = $model->validate();
                $i = 0;$lastid=0;
                $arr_product_cat = [];
                $dateFormats = ['d-m-Y', 'M-y', 'd-M-Y', 'Y-m-d'];
                $mod_his = InventoryReportBudgetUpload::model()->find(array('condition' => ' inv_month = "' . $_POST['InventoryReportBudgetUpload']['inv_month'] . '" and inv_year = "' . $_POST['InventoryReportBudgetUpload']['inv_year'] . '"'));
                if (!empty($mod_his)) {
                    $other[] = 'Already added Data';
                    $valid = false;
                }
                else{
                    $budget_date = '01-'.$_POST['InventoryReportBudgetUpload']['inv_month'].'-'.$_POST['InventoryReportBudgetUpload']['inv_year'];
                    $budget_date = DateTime::createFromFormat('d-m-Y', $budget_date);
                                    
                    if ($file != '') {
                        if (($handle = fopen("upload/inventoryreport/" . $file, "r")) !== FALSE) {
                            $n = 1;
                            $i = 0;
                            while (($row = fgetcsv($handle)) !== FALSE) {
                                if ($n == 4) {
                                    $m = 0;
                                    $var = 'A';
                                    $k = 0;
                                
                                    while ($m != 1 && isset($row[$k]) && $k <= 80) {
                                        $rowDate = $row[$k];
                                        if (!empty($rowDate)) {
                                            $detectedFormat = determineDateFormat($rowDate, $dateFormats);
                                            if ($detectedFormat) {
                                                if ($detectedFormat === 'M-y') {
                                                    $rowDate = '01-' . $rowDate; // Prepend default day
                                                    $detectedFormat = 'd-M-y';  // Adjust format accordingly
                                                }
                                                $date_field = DateTime::createFromFormat($detectedFormat, $rowDate);
                                                if ($date_field && $budget_date->format('d-m-Y') === $date_field->format('d-m-Y')) {
                                                    $m = 1;
                                                    $colname_corresponding_date = chr(ord($var) + 4); // Adjust for column name
                                                    $col_corresponding_date = $k + 4; // Adjust for numeric index
                                                    //echo "Matched column: $colname_corresponding_date ($col_corresponding_date)";
                                                    break;
                                                }
                                            }
                                        }
                                        $var++;
                                        $k++;
                                    }
                                
                                    if ($m == 0 && $k > 80) {
                                        $other[] = "Error: Budget date not found after checking up to 80 columns.";
                                        $valid = false;
                                    }
                                }
                                else if ($n > 6) {
                                    // Process categories...
                                    $category_code = $row[0];
                                    $category_code = str_replace('Total', '', $category_code); // Remove 'Total'
                                    $product_category = trim($category_code); // Trim any extra spaces
                                    $modelProductCat = new MasProductCategory(); 
                                    $modelProductCat = $modelProductCat->find(['condition'=>'product_category="'.$product_category.'"']);
                                    
                                    if(!empty($modelProductCat)){
                                        $lastid =$n;
                                        $modelItems[$i] = new InventoryReportBudgetUploadDetails();
                                        $modelItems[$i]->product_category_id=$modelProductCat->id;
                                        $modelItems[$i]->inv_year=$model->inv_year;
                                        $modelItems[$i]->inv_month=$model->inv_month;
                                        $modelItems[$i]->inv_report_id=0;
                                        $modelItems[$i]->product_category=$product_category;
                                        $modelItems[$i]->budget_amount = ($modelProductCat->inventory_report_type =='AO')?(float)str_replace(',', '', $row[$col_corresponding_date]):0;
                                        $modelItems[$i]->created_by== Yii::app()->user->id;
                                        $modelItems[$i]->created_date_time = date('Y-m-d H:i:s');
                                        $valid = $modelItems[$i]->validate() && $valid;
                                        $i++;
                                    }else{
                                        if($n==($lastid+1) && $modelProductCat->inventory_report_type =='QA'){
                                            $p=$i-1;
                                            $modelItems[$p]->budget_amount =(float)str_replace(',', '', $row[$col_corresponding_date]);
                                        }
                                    }
                                }
                                $n++;
                                if ($n > 5000) { // Limit rows processed for debugging
                                    $other[] = "Error: Reached MAXIMUM column limit 5000.";
                                    $valid = false;
                                    break;
                                }
                            }
                            fclose($handle);
                        }
                        
                    } else {
                        $other[] = 'YMGF_PSI Budget File Not Uploaded';
                        $valid = false;
                    }
                    
                    
                }
                if ($valid) {
                    $model->save();
                    if (!empty($modelItems)) {
                        foreach ($modelItems as $each) {
                            $each->inv_report_id  = $model->id;
                            $each->save(false);
                        }
                    }
                    $jsonData['status'] = 'success';
                    $jsonData['frm'] = $_POST['InventoryReportBudgetUpload'];
                    $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 actionSaveBaseInventoryUpload(){
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {
            $model = new InventoryReportBaseinventoryUpload();
            $other = array();

            if (isset($_POST['InventoryReportBaseinventoryUpload'])) {
                if ((int) @$_POST['InventoryReportBaseinventoryUpload']['id'] > 0) {
                    $id = (int) @$_POST['InventoryReportBaseinventoryUpload']['id'];
                    $model = $model->findByPk($id); 
                }
                $model->uploaded_by = Yii::app()->user->id;
                $model->uploaded_date_time = date('Y-m-d H:i:s');
                $model->attributes = $_POST['InventoryReportBaseinventoryUpload'];
                $file = $_POST['InventoryReportBaseinventoryUpload']['bi_file'];
                $model->bi_file = $file;
                $valid = $model->validate();
                $i = 0;
                $arr_product_cat = [];
                
                $mod_his = InventoryReportBaseinventoryUpload::model()->find(array('condition' => ' inv_month = "' . $_POST['InventoryReportBaseinventoryUpload']['inv_month'] . '" and inv_year = "' . $_POST['InventoryReportBaseinventoryUpload']['inv_year'] . '"'));
                if (!empty($mod_his)) {
                    $other[] = 'Already added Data';
                    $valid = false;
                }
                else{
                    if ($file != '') {
                        if (($handle = fopen("upload/inventoryreport/" . $file, "r")) !== FALSE) {
                            $n = 1;
                            while (($row = fgetcsv($handle)) !== FALSE) {
                                if($n>3){
                                    $gmc = trim($row[1]); // Trim any extra spaces
                                    $modelInventory = new Inventory(); 
                                    $modelInventory = $modelInventory->find(['condition'=>'gmc_code="'.$gmc.'"']);
                                    $cls_bal_amt = $row[11];
                                    $cls_bal_amt = str_replace(['$', ','], '', $cls_bal_amt); // Remove $ and commas
                                    $cls_bal_amt = number_format((float)$cls_bal_amt, 2, '.', ''); // Convert to float and format to 2 decimal places
                                    $intransist_amount =$row[13];
                                    $intransist_amount = str_replace(['$', ','], '', $intransist_amount); // Remove $ and commas
                                    $intransist_amount = number_format((float)$intransist_amount, 2, '.', ''); 
                                    if(!empty($modelInventory)){
                                        $modelItems[$i] = new InventoryReportBaseinventoryUploadDetails();
                                        $modelItems[$i]->product_category_id=$modelInventory->product_category_id;
                                        $modelItems[$i]->product_id=$modelInventory->id;
                                        $modelItems[$i]->inv_year=$model->inv_year;
                                        $modelItems[$i]->inv_month=$model->inv_month;
                                        $modelItems[$i]->inv_report_id=0;
                                        $modelItems[$i]->gmc=$gmc;
                                        $modelItems[$i]->gmc_sub_category=$modelInventory->sub_category;
                                        $modelItems[$i]->description=$modelInventory->description;
                                        $modelItems[$i]->inventory_type=$modelInventory->inventory_type;
                                        $modelItems[$i]->cls_bal_amt= $cls_bal_amt;
                                        $modelItems[$i]->intransist_amount= $intransist_amount;
                                        $modelItems[$i]->created_by= Yii::app()->user->id;
                                        $modelItems[$i]->created_date_time = date('Y-m-d H:i:s');
                                        $valid = $modelItems[$i]->validate() && $valid;
                                        $i++;
                                    }else{
                                        $gmc_list[]=$gmc;
                                    }
                                }
                                $n++;
                                if ($n > 6000) { // Limit rows processed for debugging
                                    $other[] = "Error: Reached MAXIMUM column limit 6000.";
                                    $valid = false;
                                    break;
                                } 
                                
                            }
                            fclose($handle);
                        }
                    } else {
                        $other[] = 'BASE INVENTORY LIST File Not Uploaded';
                        $valid = false;
                    }
                    
                    
                }
                if ($valid) {
                    $model->save();
                    if (!empty($modelItems)) {
                        foreach ($modelItems as $each) {
                            $each->inv_report_id  = $model->id;
                            $each->save(false);
                        }
                    }
                    $jsonData['status'] = 'success';
                    $jsonData['frm'] = $_POST['InventoryReportBaseinventoryUpload'];
                    $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 actionSaveStockUpload(){
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {
            $model = new InventoryReportStockUpload();
            $other = array();

            if (isset($_POST['InventoryReportStockUpload'])) {
                if ((int) @$_POST['InventoryReportStockUpload']['id'] > 0) {
                    $id = (int) @$_POST['InventoryReportStockUpload']['id'];
                    $model = $model->findByPk($id); 
                }
                $model->uploaded_by = Yii::app()->user->id;
                $model->uploaded_date_time = date('Y-m-d H:i:s');
                $model->attributes = $_POST['InventoryReportStockUpload'];
                $file = $_POST['InventoryReportStockUpload']['stock_file'];
                $model->stock_file = $file;
                $valid = $model->validate();
                $i = 0;
                $arr_months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
                $gmc_list=[];
                $mod_his = InventoryReportStockUpload::model()->find(array('condition' => ' inv_month = "' . $_POST['InventoryReportStockUpload']['inv_month'] . '" and inv_year = "' . $_POST['InventoryReportStockUpload']['inv_year'] . '"'));
                if (!empty($mod_his)) {
                    $other[] = 'Already added Data';
                    $valid = false;
                }
                else{
                    if ($file != '') {
                        if (($handle = fopen("upload/inventoryreport/" . $file, "r")) !== FALSE) {
                            $n = 1;
                            while (($row = fgetcsv($handle)) !== FALSE) {
                                if ($n == 3) {
                                    $col_corresponding_inv_class = array_search('Inv Class', $row);
                                    if ($col_corresponding_inv_class === false) {
                                        $other[] = "Error: 'Inv Class' column not found in row 3.";
                                        $valid = false;
                                        break;
                                    }
                                }
                                if ($n == 4) {
                                    $year = $_POST['InventoryReportStockUpload']['inv_year'];
                                    $startSearchIndex = $col_corresponding_inv_class + 1;
                                    $col_corresponding_year_class = false;
                                    for ($k = $startSearchIndex; $k < count($row); $k++) {
                                        if (isset($row[$k]) && trim($row[$k]) === $year) {
                                            $col_corresponding_year_class = $k;
                                            break;
                                        }
                                    }
                                    if ($col_corresponding_year_class === false) {
                                        $other[] = "Error: Year column '$year' not found in row 4.";
                                        $valid = false;
                                        break;
                                    }
                                }
                                if ($n == 5) {
                                    $monthIndex = (int)$_POST['InventoryReportStockUpload']['inv_month'] - 1;
                                    $month = $arr_months[$monthIndex];
                                
                                    // Start searching from the year column index
                                    $startSearchIndex = $col_corresponding_year_class + 1;
                                
                                    $col_corresponding_month_class = false;
                                    for ($k = $startSearchIndex; $k < count($row); $k++) {
                                        if (isset($row[$k]) && trim($row[$k]) === $month) {
                                            $col_corresponding_month_class = $k;
                                            break;
                                        }
                                    }
                                
                                    if ($col_corresponding_month_class === false) {
                                        $other[] = "Error: Month column '$month' not found in row 5 after the year column.";
                                        $valid = false;
                                    }
                                }
                                if($n>5){
                                    $gmc = $row[2];
                                    $category_type =$row[$col_corresponding_month_class];
                                    $gmc = trim($row[2]); // Trim any extra spaces
                                    $modelInventory = new Inventory(); 
                                    $modelInventory = $modelInventory->find(['condition'=>'gmc_code="'.$gmc.'"']);
                                    if(!empty($modelInventory)&& !empty($category_type)){
                                        $modelItems[$i] = new InventoryReportStockUploadDetails();
                                        $modelItems[$i]->product_category_id=$modelInventory->product_category_id;
                                        $modelItems[$i]->product_id=$modelInventory->id;
                                        $modelItems[$i]->inv_year=$model->inv_year;
                                        $modelItems[$i]->inv_month=$model->inv_month;
                                        $modelItems[$i]->inv_report_stock_id=0;
                                        $modelItems[$i]->gmc=$gmc;
                                        $modelItems[$i]->inv_category = $category_type;
                                        $modelItems[$i]->gmc_sub_category=$modelInventory->sub_category;
                                        $modelItems[$i]->description=$modelInventory->description;
                                        $modelItems[$i]->inventory_type=$modelInventory->inventory_type;
                                        $modelItems[$i]->created_by= Yii::app()->user->id;
                                        $modelItems[$i]->created_date_time = date('Y-m-d H:i:s');
                                        $valid = $modelItems[$i]->validate() && $valid;
                                        $i++;
                                    }else{
                                        if(empty($modelInventory)){
                                        $gmc_list[]=$gmc;
                                        }
                                    }
                                }
                                $n++;
                                if ($n > 25000) { // Limit rows processed for debugging
                                    $other[] = "Error: Reached MAXIMUM column limit 25000.";
                                    $valid = false;
                                    break;
                                }
                            }
                            fclose($handle);
                        }
                    } else {
                        $other[] = 'YMGF_StockDetails File Not Uploaded';
                        $valid = false;
                    }
                    
                    
                }
                if ($valid) {
                    $model->save();
                    if (!empty($modelItems)) {
                        foreach ($modelItems as $each) {
                            $each->inv_report_stock_id  = $model->id;
                            $each->save(false);
                        }
                    }
                    $jsonData['status'] = 'success';
                    $jsonData['frm'] = $_POST['InventoryReportStockUpload'];
                    $jsonData['id'] = $model->id;
                    echo CJSON::encode($jsonData);
                } else {

                    $jsonData['col_corresponding_month_class'] = $col_corresponding_month_class;
                    $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($jsonData,$error1, $error2, $other));
                    if ($error != '[]')
                        echo $error;
                }
            }
        } else {

            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionDownloadInventoryFinalReport()
    {
        $inv_report_id = $_REQUEST['inv_report_id'];
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
        
        if (count($_GET) > 0) {
            
            // Helper function to populate a row with data
            function setData($worksheet, $rowNumber, $data) {
                $column = 'A';
                foreach ($data as $value) {
                    $worksheet->setCellValue("{$column}{$rowNumber}", $value);
                    $column++;
                }
            }
    
            // Helper function to set cell background color
            function setFillColor($worksheet, $range, $color) {
                $worksheet->getStyle($range)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB($color);
            }
            // Retrieve data from the database
            $arr_month = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec"];
            $modelInventoryReport = new InventoryReport();
            $modelInventoryReport = $modelInventoryReport->findByPK((int)$inv_report_id);
            $ModSlowMoving = new MasSlowmovingInvCategeries();
            $ModSlowMoving = $ModSlowMoving->findAll(['condition'=>'is_active="Y"','order'=>'id asc']);
            $slowmoving_count=count($ModSlowMoving);
             //$model->baseinventory_upload_id=$baseinv_his->id;
              //      $model->budget_upload_id=$budget_his->id;
               //     $model->stock_upload_id=$stock_his->id;
            $sql = "SELECT 
                    product_category_id,
                    product_category,
                    report_sort_order
                FROM 
                    view_inventory_report
                WHERE inv_month='" . $modelInventoryReport->inv_month. "' AND inv_year='".$modelInventoryReport->inv_year."'
                GROUP BY 
                    product_category_id
                ORDER BY report_sort_order ASC";
            $modelInventoryReportDetails = Yii::app()->db->createCommand($sql)->queryAll();
            $prev_month=($modelInventoryReport['inv_month']!=1)?(int)$modelInventoryReport['inv_month']-1:12;
            $prev_year=($modelInventoryReport['inv_month']!=1)?(int)$modelInventoryReport['inv_year']:(int)$modelInventoryReport['inv_year']-1;
            $modelBudgetDetails = new InventoryReportBudgetUploadDetails();
            $modelBudgetDetails =$modelBudgetDetails->findAll(['condition'=>"inv_month='" . $modelInventoryReport->inv_month. "' AND inv_year='".$modelInventoryReport->inv_year."'"]);
            $modelPrevBudgetDetails = new InventoryReportBudgetUploadDetails();
            $modelPrevBudgetDetails =$modelPrevBudgetDetails->findAll(['condition'=>"inv_month='" . $prev_month. "' AND inv_year='".$prev_year."'"]);
            $arr_budget=[];$arr_prev_budget=[];
            foreach($modelBudgetDetails as $eachBudget){
                $arr_budget[$eachBudget->product_category_id]=$eachBudget->budget_amount;
            }
            foreach($modelPrevBudgetDetails as $eachPBudget){
                $arr_prev_budget[$eachPBudget->product_category_id]=$eachPBudget->budget_amount;
            }
            //current_year_sum
            $arr_normal_cur=[];$arr_pdt_subcat=[];
            //slowmoving
            $sqlslowmoving ='SELECT SUM(total_amount)AS inv_total,inv_category,product_category,product_category_id,gmc_sub_category FROM `view_inventory_report` 
            WHERE inventory_type="N" AND inv_category IN (SELECT inv_category FROM ymh_mas_slowmoving_inv_categeries where is_active="Y") AND 
            inv_month=" '. $modelInventoryReport->inv_month.' " AND inv_year="'.$modelInventoryReport->inv_year.'"
            GROUP BY product_category_id,gmc_sub_category,inv_category;';
            $arrSlowMovingCurDetails = Yii::app()->db->createCommand($sqlslowmoving)->queryAll();
            if(!empty($arrSlowMovingCurDetails)){
                foreach($arrSlowMovingCurDetails as $eachsm){
                    $productCategoryId=$eachsm['product_category_id'];
                    $subCategory = $eachsm['gmc_sub_category'];
                    $invCategory = $eachsm['inv_category'];
                    $invTotal = (float) $eachsm['inv_total'];
                    // Initialize subcategories array if not set
                    $arr_pdt_subcat[$productCategoryId]['subcategories'] = 
                        $arr_pdt_subcat[$productCategoryId]['subcategories'] ?? [];
            
                    // Add sub-category if not already present
                    if (!in_array($subCategory, $arr_pdt_subcat[$productCategoryId]['subcategories'])) {
                        $arr_pdt_subcat[$productCategoryId]['subcategories'][] = $subCategory;
                    }
                    $arr_normal_cur[$eachsm['product_category_id']][$eachsm['gmc_sub_category']][$eachsm['inv_category']]['sum']=$eachsm['inv_total'];
                    $arr_normal_cur[$eachsm['product_category_id']]['grand_total']=isset($arr_normal_cur[$eachsm['product_category_id']]['grand_total'])?((float)$arr_normal_cur[$eachsm['product_category_id']]['grand_total']+$eachsm['inv_total']):$eachsm['inv_total'];
                }
            }
            //normal
            $sqlnormalcur ='SELECT SUM(total_amount)AS inv_total,product_category,product_category_id,gmc_sub_category FROM `view_inventory_report` 
            WHERE inventory_type="N" AND inv_category NOT IN (SELECT inv_category FROM ymh_mas_slowmoving_inv_categeries where is_active="Y") AND 
            inv_month=" '. $modelInventoryReport->inv_month.' " AND inv_year="'.$modelInventoryReport->inv_year.'"
            GROUP BY product_category_id,gmc_sub_category;';
            $arrNormalCurDetails = Yii::app()->db->createCommand($sqlnormalcur)->queryAll();
            if(!empty($arrNormalCurDetails)){
                foreach($arrNormalCurDetails as $eachsm){
                    $arr_normal_cur[$eachsm['product_category_id']][$eachsm['gmc_sub_category']]['normal']=$eachsm['inv_total'];
                    $arr_normal_cur[$eachsm['product_category_id']]['normal']=isset($arr_normal_cur[$eachsm['product_category_id']]['normal'])?((float)$arr_normal_cur[$eachsm['product_category_id']]['normal']+$eachsm['inv_total']):$eachsm['inv_total'];
                }
            }
            //strategic
            $sqlstrategiccur ='SELECT SUM(total_amount)AS inv_total,product_category,product_category_id,gmc_sub_category FROM `view_inventory_report` 
            WHERE inventory_type!="N"  AND 
            inv_month=" '. $modelInventoryReport->inv_month.' " AND inv_year="'.$modelInventoryReport->inv_year.'"
            GROUP BY product_category_id,gmc_sub_category;';
            $arrstrategicCurDetails = Yii::app()->db->createCommand($sqlstrategiccur)->queryAll();
            if(!empty($arrstrategicCurDetails)){
                foreach($arrstrategicCurDetails as $eachsm){
                    $arr_normal_cur[$eachsm['product_category_id']][$eachsm['gmc_sub_category']]['strategic']=$eachsm['inv_total'];
                    $arr_normal_cur[$eachsm['product_category_id']]['strtegic']=isset($arr_normal_cur[$eachsm['product_category_id']]['strtegic'])?((float)$arr_normal_cur[$eachsm['product_category_id']]['strtegic']+$eachsm['inv_total']):$eachsm['inv_total'];
                }
            }
            //prev_year_sum
            $arr_normal_prev=[];
            //slowmoving
            $sqlslowmoving ='SELECT SUM(total_amount)AS inv_total,inv_category,product_category,product_category_id,gmc_sub_category FROM `view_inventory_report` 
            WHERE inventory_type="N" AND inv_category IN (SELECT inv_category FROM ymh_mas_slowmoving_inv_categeries where is_active="Y") AND 
            inv_month=" '. $prev_month.' " AND inv_year="'.$prev_year.'"
            GROUP BY product_category_id,gmc_sub_category,inv_category;';
            $arrSlowMovingCurDetails = Yii::app()->db->createCommand($sqlslowmoving)->queryAll();
            if(!empty($arrSlowMovingCurDetails)){
                foreach($arrSlowMovingCurDetails as $eachsm){
                    $arr_normal_prev[$eachsm['product_category_id']][$eachsm['gmc_sub_category']][$eachsm['inv_category']]['sum']=$eachsm['inv_total'];
                    $arr_normal_prev[$eachsm['product_category_id']]['grand_total']=isset($arr_normal_prev[$eachsm['product_category_id']]['grand_total'])?((float)$arr_normal_prev[$eachsm['product_category_id']]['grand_total']+$eachsm['inv_total']):$eachsm['inv_total'];
                }
            }
            //normal
            $sqlnormalcur ='SELECT SUM(total_amount)AS inv_total,product_category,product_category_id,gmc_sub_category FROM `view_inventory_report` 
            WHERE inventory_type="N" AND inv_category NOT IN (SELECT inv_category FROM ymh_mas_slowmoving_inv_categeries where is_active="Y") AND 
            inv_month=" '. $prev_month.' " AND inv_year="'.$prev_year.'"
            GROUP BY product_category_id,gmc_sub_category;';
            $arrNormalCurDetails = Yii::app()->db->createCommand($sqlnormalcur)->queryAll();
            if(!empty($arrNormalCurDetails)){
                foreach($arrNormalCurDetails as $eachsm){
                    $arr_normal_prev[$eachsm['product_category_id']][$eachsm['gmc_sub_category']]['normal']=$eachsm['inv_total'];
                    $arr_normal_prev[$eachsm['product_category_id']]['normal']=isset($arr_normal_prev[$eachsm['product_category_id']]['normal'])?((float)$arr_normal_prev[$eachsm['product_category_id']]['normal']+$eachsm['inv_total']):$eachsm['inv_total'];
                }
            }
            //strategic
            $sqlstrategiccur ='SELECT SUM(total_amount)AS inv_total,product_category,product_category_id,gmc_sub_category FROM `view_inventory_report` 
            WHERE inventory_type!="N"  AND 
            inv_month=" '. $prev_month.' " AND inv_year="'.$prev_year.'"
            GROUP BY product_category_id,gmc_sub_category;';
            $arrstrategicCurDetails = Yii::app()->db->createCommand($sqlstrategiccur)->queryAll();
            if(!empty($arrstrategicCurDetails)){
                foreach($arrstrategicCurDetails as $eachsm){
                    $arr_normal_prev[$eachsm['product_category_id']][$eachsm['gmc_sub_category']]['strategic']=$eachsm['inv_total'];
                    $arr_normal_prev[$eachsm['product_category_id']]['strtegic']=isset($arr_normal_prev[$eachsm['product_category_id']]['strtegic'])?((float)$arr_normal_prev[$eachsm['product_category_id']]['strtegic']+$eachsm['inv_total']):$eachsm['inv_total'];
                }
            }
            // Create new spreadsheet
            $spreadsheet = new Spreadsheet();
            $activeWorksheet = $spreadsheet->getActiveSheet();
    
            // Set the title in merged cells
            $activeWorksheet->mergeCells('A1:L1');
            $activeWorksheet->setCellValue('A1', "Inventory Report");
            $activeWorksheet->mergeCells('A2:L2');
            $activeWorksheet->setCellValue('A2', "01-".$arr_month[$modelInventoryReport['inv_month']-1]."-".$modelInventoryReport['inv_year']);
            $activeWorksheet->getStyle('A1:L1')->getFont()->setBold(true);
            $activeWorksheet->getStyle('A2:L2')->getFont()->setBold(true);
            $activeWorksheet->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            $activeWorksheet->getStyle('A2')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            // Set font size to 16 for cell A1
            $activeWorksheet->getStyle('A1')->getFont()->setSize(16);
            $activeWorksheet->mergeCells('A3:A4');
            $activeWorksheet->mergeCells('B3:B4');
            $activeWorksheet->mergeCells('C3:C4');
            $activeWorksheet->mergeCells('D3:D4');
            $activeWorksheet->mergeCells('E3:E4');
            $activeWorksheet->mergeCells('F3:F4');
            $var='G';
            $varprevtotal = chr(ord($var) + $slowmoving_count-1);
            $vartotal = chr(ord($var) + $slowmoving_count);
            $varlast=chr(ord($var) + $slowmoving_count+1);
            $activeWorksheet->mergeCells("G3:{$varprevtotal}3");
            
            $activeWorksheet->mergeCells("{$vartotal}3:{$vartotal}4");
            $activeWorksheet->mergeCells("{$varlast}3:{$varlast}4");
            // Freeze the pane at cell C3
            $activeWorksheet->freezePane('A5');
            
            // Set text wrap and alignment for row number 2
            $cellsToWrapAndCenter = ["A3","B3", "C3", "D3", "E3", "F3", "G3", "K3", "l3"];
            foreach ($cellsToWrapAndCenter as $cellReference) {
                $cell = $activeWorksheet->getCell($cellReference);
                $cell->getStyle()->getAlignment()->setWrapText(true);
                $cell->getStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            }
            // Set width for column A and B individually
            $activeWorksheet->getColumnDimension('A')->setWidth(21.83);
            $activeWorksheet->getColumnDimension('B')->setWidth(19);
            
            // Set width for columns C to I to 22.42
            for ($column = 'C'; $column <= 'L'; $column++) {
                $activeWorksheet->getColumnDimension($column)->setWidth(22.42);
            }
            // Set the height of row 1 to 25
            $activeWorksheet->getRowDimension(1)->setRowHeight(25);
            // Define and add column headers
            $columnHeaders = [
                'Category', '','Sub - Category','Normal Inventory','Strategic Inventory','Total (Normal + Strategic Inventory)','Slow-Moving Inventory'
            ];
            $headerRow = 3;
            setData($activeWorksheet, $headerRow, $columnHeaders);
            $activeWorksheet->setCellValue("{$vartotal}3", 'Total Slow-Moving Inventory');
            $activeWorksheet->setCellValue("{$varlast}3", 'GRAND TOTAL');
            
            $activeWorksheet->getStyle('A3:L3')->getFont()->setBold(true);
            //fourth
            $cat_var='G';
            
            if(!empty($ModSlowMoving)){
                foreach($ModSlowMoving as $eachcat){
                    $activeWorksheet->setCellValue("{$cat_var}4", $eachcat->inv_category);
                    $cat_var = chr(ord($cat_var) +1);
                }    
            }    
            // Populate data rows
            $dataRow = $headerRow + 2;
            //$tot_0=0;$tot_1=0;$tot_2=0;$tot_3=0;$tot_4=0;$tot_5=0;$tot_g=0;
            if (!empty($modelInventoryReportDetails)) {
                foreach($modelInventoryReportDetails as $eachdetails){
                    //$subCategories = ['UP', 'GP', 'others'];
                    $subCategories = isset($arr_pdt_subcat[$eachdetails['product_category_id']]['subcategories'])?$arr_pdt_subcat[$eachdetails['product_category_id']]['subcategories']:[];
                    $row_last = $dataRow+count($subCategories)+1;
                    $activeWorksheet->mergeCells("A{$dataRow}:A{$row_last}");
                    $activeWorksheet->setCellValue("A{$dataRow}", $eachdetails['product_category'].$arr_month[$prev_month-1]."-".$prev_year);
                    $activeWorksheet->setCellValue("B{$dataRow}", 'Inventory Budget');
                    setFillColor($activeWorksheet, "B{$dataRow}:{$varlast}{$dataRow}", 'FFE699');  
                    $prevBudgetValue = isset($arr_prev_budget[$eachdetails['product_category_id']])? $arr_prev_budget[$eachdetails['product_category_id']]: '0';
                    $activeWorksheet->setCellValue("{$varlast}{$dataRow}",$prevBudgetValue);
                    $dataRow++;
                    $activeWorksheet->setCellValue("B{$dataRow}", 'Inventory Amount');
                    setFillColor($activeWorksheet, "B{$dataRow}:{$varlast}{$dataRow}", 'FFE699');
                    $prev_inv_amount_row=$dataRow;
                    $prevNormValue = isset($arr_normal_prev[$eachdetails['product_category_id']]['normal'])? $arr_normal_prev[$eachdetails['product_category_id']]['normal']: '0';
                    $activeWorksheet->setCellValue("D{$dataRow}", $prevNormValue);
                    $prevstrategicValue = isset($arr_normal_prev[$eachdetails['product_category_id']]['strtegic'])? $arr_normal_prev[$eachdetails['product_category_id']]['strtegic']: '0';
                    $activeWorksheet->setCellValue("D{$dataRow}", $prevstrategicValue);
                    $activeWorksheet->setCellValue("F{$dataRow}", '=IFERROR(D' . $dataRow . '+E' . $dataRow.',0)'); // Total
                    $cat_var='G';
                    if(!empty($ModSlowMoving)){
                        foreach($ModSlowMoving as $eachcat){
                            $cat_wise_val=isset($arr_normal_prev[$eachdetails['product_category_id']][$eachcat->inv_category]['sum'])? $arr_normal_prev[$eachdetails['product_category_id']][$subCategory][$eachcat->inv_category]['sum']: '0';
                            $activeWorksheet->setCellValue("{$cat_var}{$dataRow}", $cat_wise_val);
                            $cat_var = chr(ord($cat_var) +1);
                        }    
                    }
                    //for Total Slow-Moving Inventory
                    $cat_var = chr(ord($cat_var) -1);
                    $activeWorksheet->setCellValue("{$vartotal}{$dataRow}", "=SUM(G{$dataRow}:{$cat_var}{$dataRow})");
                    //grand total
                    $activeWorksheet->setCellValue("{$varlast}{$dataRow}", "=SUM(F{$dataRow},{$vartotal}{$dataRow})");
                    // Additional Rows for Sub-categories
                    foreach ($subCategories as $subCategory) {
                        $dataRow++;
                        $activeWorksheet->setCellValue("C{$dataRow}", $subCategory);
                        $prevNormsubValue = isset($arr_normal_prev[$eachdetails['product_category_id']][$subCategory]['normal'])? $arr_normal_prev[$eachdetails['product_category_id']][$subCategory]['normal']: '0';
                        $activeWorksheet->setCellValue("D{$dataRow}",$prevNormsubValue); // Placeholder for Normal Inventory
                        $prevstrategicsubValue = isset($arr_normal_prev[$eachdetails['product_category_id']][$subCategory]['strtegic'])? $arr_normal_prev[$eachdetails['product_category_id']][$subCategory]['strtegic']: '0';
                        $activeWorksheet->setCellValue("E{$dataRow}", $prevstrategicsubValue); // Placeholder for Strategic Inventory
                        $activeWorksheet->setCellValue("F{$dataRow}", '=IFERROR(D' . $dataRow . '+E' . $dataRow.',0)'); // Total
                        $cat_var='G';
                        if(!empty($ModSlowMoving)){
                            foreach($ModSlowMoving as $eachcat){
                                $cat_wise_val=isset($arr_normal_prev[$eachdetails['product_category_id']][$subCategory][$eachcat->inv_category]['sum'])? $arr_normal_prev[$eachdetails['product_category_id']][$subCategory][$eachcat->inv_category]['sum']: '0';
                                $activeWorksheet->setCellValue("{$cat_var}{$dataRow}", $cat_wise_val);
                                $cat_var = chr(ord($cat_var) +1);
                            }    
                        }
                        //for Total Slow-Moving Inventory
                        $cat_var = chr(ord($cat_var) -1);
                        $activeWorksheet->setCellValue("{$vartotal}{$dataRow}", "=SUM(G{$dataRow}:{$cat_var}{$dataRow})");
                        //grandtotal
                        $activeWorksheet->setCellValue("{$varlast}{$dataRow}", "=SUM(F{$dataRow},{$vartotal}{$dataRow})");
                    }
                    $dataRow++;
                    $row_last = $dataRow+count($subCategories)+1;
                    $activeWorksheet->mergeCells("A{$dataRow}:A{$row_last}");
                    $activeWorksheet->setCellValue("A{$dataRow}", $eachdetails['product_category'].$arr_month[$modelInventoryReport['inv_month']-1]."-".(int)$modelInventoryReport['inv_year']);
                    // First Row: Inventory Budget
                    $activeWorksheet->setCellValue("B{$dataRow}", 'Inventory Budget');
                    setFillColor($activeWorksheet, "B{$dataRow}:{$varlast}{$dataRow}", 'FFE699');
                    
                    $curBudgetValue = isset($arr_budget[$eachdetails['product_category_id']])? $arr_budget[$eachdetails['product_category_id']]: '0';
                    $activeWorksheet->setCellValue("{$varlast}{$dataRow}",$curBudgetValue );
                    // Next Row for Inventory Amount
                    $dataRow++;
                    $activeWorksheet->setCellValue("B{$dataRow}", 'Inventory Amount');
                    setFillColor($activeWorksheet, "B{$dataRow}:{$varlast}{$dataRow}", 'FFE699');
                    $cur_inv_amount_row=$dataRow;
                    $curNormValue = isset($arr_normal_cur[$eachdetails['product_category_id']]['normal'])? $arr_normal_cur[$eachdetails['product_category_id']]['normal']: '0';
                    $activeWorksheet->setCellValue("D{$dataRow}", $curNormValue);
                    $curstrategicValue = isset($arr_normal_cur[$eachdetails['product_category_id']]['strtegic'])? $arr_normal_cur[$eachdetails['product_category_id']]['strtegic']: '0';
                    $activeWorksheet->setCellValue("E{$dataRow}", $curstrategicValue);
                    $activeWorksheet->setCellValue("F{$dataRow}", '=IFERROR(D' . $dataRow . '+E' . $dataRow.',0)'); // Total
                    $cat_var='G';
                    if(!empty($ModSlowMoving)){
                        foreach($ModSlowMoving as $eachcat){
                            $cat_wise_val=isset($arr_normal_cur[$eachdetails['product_category_id']][$eachcat->inv_category]['sum'])? $arr_normal_cur[$eachdetails['product_category_id']][$subCategory][$eachcat->inv_category]['sum']: '0';
                            $activeWorksheet->setCellValue("{$cat_var}{$dataRow}", $cat_wise_val);
                            $cat_var = chr(ord($cat_var) +1);
                        }    
                    }
                    //for Total Slow-Moving Inventory
                    $cat_var = chr(ord($cat_var) -1);
                    $activeWorksheet->setCellValue("{$vartotal}{$dataRow}", "=SUM(G{$dataRow}:{$cat_var}{$dataRow})");
                    //grandtotal
                    $activeWorksheet->setCellValue("{$varlast}{$dataRow}", "=SUM(F{$dataRow},{$vartotal}{$dataRow})");
                    // Additional Rows for Sub-categories
                    foreach ($subCategories as $subCategory) {
                        $dataRow++;
                        $activeWorksheet->setCellValue("C{$dataRow}", $subCategory);
                        $curNormsubValue = isset($arr_normal_cur[$eachdetails['product_category_id']][$subCategory]['normal'])? $arr_normal_cur[$eachdetails['product_category_id']][$subCategory]['normal']: '0';
                        $activeWorksheet->setCellValue("D{$dataRow}", $curNormsubValue); // Placeholder for Normal Inventory
                        $curstrategicsubValue = isset($arr_normal_cur[$eachdetails['product_category_id']][$subCategory]['strtegic'])? $arr_normal_cur[$eachdetails['product_category_id']][$subCategory]['strtegic']: '0';
                        $activeWorksheet->setCellValue("E{$dataRow}", $curstrategicsubValue); // Placeholder for Strategic Inventory
                        $activeWorksheet->setCellValue("F{$dataRow}", '=IFERROR(D' . $dataRow . '+E' . $dataRow.',0)'); // Total
                        $cat_var='G';
                        if(!empty($ModSlowMoving)){
                            foreach($ModSlowMoving as $eachcat){
                                $cat_wise_val=isset($arr_normal_cur[$eachdetails['product_category_id']][$subCategory][$eachcat->inv_category]['sum'])? $arr_normal_cur[$eachdetails['product_category_id']][$subCategory][$eachcat->inv_category]['sum']: '0';
                                $activeWorksheet->setCellValue("{$cat_var}{$dataRow}", $cat_wise_val);
                                $cat_var = chr(ord($cat_var) +1);
                            }    
                        }
                        //for Total Slow-Moving Inventory
                        $cat_var = chr(ord($cat_var) -1);
                        $activeWorksheet->setCellValue("{$vartotal}{$dataRow}", "=SUM(G{$dataRow}:{$cat_var}{$dataRow})");
                        //grandtotal
                        $activeWorksheet->setCellValue("{$varlast}{$dataRow}", "=SUM(F{$dataRow},{$vartotal}{$dataRow})");
                    }
                    
                    $dataRow++; 
                    // Move to the next row for the next category
        
        
        

        
        
                    
                    $row_last = $dataRow+2;
                    $activeWorksheet->mergeCells("A{$dataRow}:A{$row_last}");
                    $activeWorksheet->setCellValue("A{$dataRow}", $eachdetails['product_category'].' - Total');
                    $activeWorksheet->setCellValue("C{$dataRow}", 'Last Month');
                    setFillColor($activeWorksheet, "A{$dataRow}:{$varlast}{$dataRow}", 'F4B084');
                    
                    // Define the start and end columns
                    $startColumn = 'D';
                    $endColumn = $varlast;
                    //#
                    // Loop through the columns and set the cell values
                    for ($col = $startColumn; $col <= $endColumn; $col++) {
                        $activeWorksheet->setCellValue("{$col}{$dataRow}", "={$col}{$prev_inv_amount_row}");
                    }
                    $dataRow++;
                    setFillColor($activeWorksheet, "A{$dataRow}:{$varlast}{$dataRow}", 'F4B084');
                    $activeWorksheet->setCellValue("C{$dataRow}", "Current Month");
                    for ($col = $startColumn; $col <= $endColumn; $col++) {
                        $activeWorksheet->setCellValue("{$col}{$dataRow}", "={$col}{$cur_inv_amount_row}");
                    }
                    $dataRow++;
                    setFillColor($activeWorksheet, "A{$dataRow}:{$varlast}{$dataRow}", 'F4B084');
                    $activeWorksheet->setCellValue("C{$dataRow}", 'Difference');
                    for ($col = $startColumn; $col <= $endColumn; $col++) {
                        $activeWorksheet->setCellValue("{$col}{$dataRow}", "={$col}{$cur_inv_amount_row}-{$col}{$prev_inv_amount_row}");
                    }
                    $dataRow++;
                }  
                $range_for_number_comma = "D5:L{$dataRow}";
                $activeWorksheet->getStyle($range_for_number_comma)
                    ->getNumberFormat()
                    ->setFormatCode('_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)');
                $highestRow = $activeWorksheet->getHighestRow();
                // Set thiN border line from A1 to LAST
                $allColumnsRange = "A1:{$varlast}{$highestRow}";
                $activeWorksheet->getStyle($allColumnsRange)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
                $activeWorksheet->getStyle("A1:B{$highestRow}")->getFont()->setBold(true);
            
                
                $width = 14; // Desired width in pixels
                for ($row = 5; $row <= $highestRow; $row++) {
                    $activeWorksheet->getRowDimension($row)->setRowHeight($width);
                }
                 
            } else {
                // No records found, so add a message to row 3
                $activeWorksheet->mergeCells("A5:I5");
                $activeWorksheet->setCellValue('A5', 'Empty Record');
            }
            
            // Set headers for file download
            $writer = new Xlsx($spreadsheet);
            $fileName = 'InventoryFinalReport_' . date('Ymd') . '.xlsx';
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename="' . $fileName . '"');
            header('Cache-Control: max-age=0');
    
            // Save the spreadsheet to output
            $writer->save('php://output');
            Yii::app()->end();
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionDeleteStockDetails() {
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (!empty($_POST['id'])) { // Ensure `id` is set
            $id = (int) $_POST['id']; // Cast to integer for security
            if ($id > 0) {
                // Delete related details first
                InventoryReportStockUploadDetails::model()->deleteAll('inv_report_stock_id=:refer_id', array(':refer_id' => $id));
    
                // Find the main InventoryReportStockUpload record
                $model = InventoryReportStockUpload::model()->findByPk($id);
    
                if ($model !== null) { // Ensure record exists before accessing properties
                    $locfile = "upload/inventoryreport/" . $model->stock_file;
                    // Delete the main InventoryReportStockUpload record
                    if ($model->deleteByPk($id)) {
                        // Delete the file if it exists
                        if (!empty($model->stock_file) && file_exists($locfile)) {
                            if (unlink($locfile)) {
                                echo "success";
                            } else {
                                echo "fail: unable to delete file.";
                            }
                        } else {
                            echo "success"; // File doesn't exist, but deletion is still successful
                        }
                    } else {
                        echo "fail: unable to delete record.";
                    }
                } else {
                    echo "fail: record not found.";
                }
            } else {
                echo "fail: invalid ID.";
            }
        } else {
            throw new CHttpException(400, 'Bad Request: ID is required.');
        }
    }
    public function actionDeleteBudgetDetails() {
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (!empty($_POST['id'])) { // Ensure `id` is set
            $id = (int) $_POST['id']; // Cast to integer for security
            if ($id > 0) {
                // Delete related details first
                InventoryReportBudgetUploadDetails::model()->deleteAll('inv_report_id=:refer_id', array(':refer_id' => $id));
    
                // Find the main InventoryReportBudgetUpload record
                $model = InventoryReportBudgetUpload::model()->findByPk($id);
    
                if ($model !== null) { // Ensure record exists before accessing properties
                    $locfile = "upload/inventoryreport/" . $model->budget_file;
                    // Delete the main InventoryReportBudgetUpload record
                    if ($model->deleteByPk($id)) {
                        // Delete the file if it exists
                        if (!empty($model->budget_file) && file_exists($locfile)) {
                            if (unlink($locfile)) {
                                echo "success";
                            } else {
                                echo "fail: unable to delete file.";
                            }
                        } else {
                            echo "success"; // File doesn't exist, but deletion is still successful
                        }
                    } else {
                        echo "fail: unable to delete record.";
                    }
                } else {
                    echo "fail: record not found.";
                }
            } else {
                echo "fail: invalid ID.";
            }
        } else {
            throw new CHttpException(400, 'Bad Request: ID is required.');
        }
    }
    public function actionDeleteBIDetails() {
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (!empty($_POST['id'])) { // Ensure `id` is set
            $id = (int) $_POST['id']; // Cast to integer for security
            if ($id > 0) {
                // Delete related details first
                InventoryReportBaseinventoryUploadDetails::model()->deleteAll('inv_report_id=:refer_id', array(':refer_id' => $id));
    
                // Find the main InventoryReportBaseinventoryUpload record
                $model = InventoryReportBaseinventoryUpload::model()->findByPk($id);
    
                if ($model !== null) { // Ensure record exists before accessing properties
                    $locfile = "upload/inventoryreport/" . $model->bi_file;
                    // Delete the main InventoryReportBaseinventoryUpload record
                    if ($model->deleteByPk($id)) {
                        // Delete the file if it exists
                        if (!empty($model->bi_file) && file_exists($locfile)) {
                            if (unlink($locfile)) {
                                echo "success";
                            } else {
                                echo "fail: unable to delete file.";
                            }
                        } else {
                            echo "success"; // File doesn't exist, but deletion is still successful
                        }
                    } else {
                        echo "fail: unable to delete record.";
                    }
                } else {
                    echo "fail: record not found.";
                }
            } else {
                echo "fail: invalid ID.";
            }
        } else {
            throw new CHttpException(400, 'Bad Request: ID is required.');
        }
    }
    public function actionDownloadRawFile(){
        if (!isset($_GET['file'])) {
            throw new CHttpException(400, 'Invalid Request. No file specified.');
        }
    
        $file = $_GET['file'];
        $filePath = Yii::getPathOfAlias('webroot') . '/upload/inventoryreport/' . $file;
    
        if (!file_exists($filePath)) {
            throw new CHttpException(404, 'The requested file does not exist.');
        }
    
        // Set headers for file download
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . basename($filePath) . '"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($filePath));
        
        // Read the file and output to the browser
        readfile($filePath);
        Yii::app()->end();
    }
}