<?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 CreditholdController 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(
                    'Index',
                    'ReportIndex',
                    'Create',
                    'GetList',
                    'SaveUpload',
                    'SaveUploadNext',
                    'UploadDocuments',
                    'SaveInitialUpload',
                    'SaveInitialUploadNext',
                    'DeleteCreditHold',
                    'DownloadCreditHoldMaster',/*'DownloadCreditHoldDetailsWithStatus',*/
                    'DownloadCreditHoldRegion',
                    'DownloadCreditHoldDistributer',
                    'DownloadStockAgeingDistributor',
                    'DownloadStockAgeingRegion',
                    'FilterRegionMaster',
                    'FilterPage',
                    'CheckErrorsCreditHold',
                    'DownloadErrorCsv',
                    'CheckErrorsCreditHoldInitial',
                    'GetListReport'
                ),
                '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 actionReportIndex()
    {
        //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) {
//            type added by nisha on 17-10-2025
            $type = $_GET['type'];
            $this->render('report_index',['report_type'=>$type]);
        } 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) {
            $save_type = $_GET['save_type'];
            $model = new CreditHold();
            $modelHistory = $model->find();
            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);
            if (empty($modelHistory)) {
                $this->render('initial_upload_form', array('model' => $model), false, false);
            } else {
                $this->render('upload_form', array('model' => $model, 'save_type' => $save_type), false, false);
            }
        } else {

            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionFilterRegionMaster()
    {
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_GET) > 0) {
            $credit_hold_id = (int) $_GET['credit_hold_id'];
            Yii::app()->clientScript->scriptMap = array('*jquery.js' => false, '*jquery.min.js' => false, 'jquery.min.js' => false, '*jquery-ui.js' => false, '*jquery-ui.min.js' => false, 'jquery-2.1.4.js' => false);
            $this->render('filter_master_report', array('credit_hold_id' => $credit_hold_id), false, false);
        } else {
            throw new CHttpException(404, 'Page not found.');
        }

    }
    public function actionFilterPage()
    {
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_GET) > 0) {
            $credit_hold_id = (int) $_GET['credit_hold_id'];
            $type = (int) $_GET['ch_rep_type'];
            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('filter_page', array('credit_hold_id' => $credit_hold_id, 'type' => $type), false, false);
        } 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 CreditHold();

            $draw = $_POST['draw'];

            $offset = $_POST['start'];

            $limit = $_POST['length'];

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

                0 => 't.id',
                1 => 't.ch_month',
                2 => 't.ch_year',
            );

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

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

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

            $condition = '1=1';

            /*if ($month != '') {

                $condition .= ' AND ch_month=' . $month;
            }

            if ($year > 0) {

                $condition .= ' AND ch_year=' . $year;
            }*/

            $arr_months = Order::getmonths();

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

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

                $condition .= " and (ch_month LIKE '%" . $searchString . "%' OR ch_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 (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["orders_view"])) {

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

                if ($row['is_completed'] == "Y") {
                    $div .= '<button type="button" class="btn btn-xs btn-primary" title="Master File" onclick="loadPopupModal(\'credithold/FilterRegionMaster&credit_hold_id=' . $row['id'] . '\',\'Master Report\',\'lg\')">
                    <i class="fa fa-print"></i>
                    </button>';
                    $div .= '<button type="button" class="btn btn-xs btn-primary" title="CHReport-Distributor" onclick="loadPopupModal(\'credithold/FilterPage&ch_rep_type=1&credit_hold_id=' . $row['id'] . '\',\'CH-Distributerwise\',\'lg\')" >
                    <i class="fa fa-file-excel-o"></i>
                    </button>';//onclick="window.open($(\'#liveSite\').val() + \'index.php?r=Credithold/DownloadCreditHoldDistributer&credit_hold_id=' . $row['id'] . '\', \'_blank\');"
                    $div .= '<button type="button" class="btn btn-xs btn-primary" title="CHReport-Region" onclick="loadPopupModal(\'credithold/FilterPage&ch_rep_type=2&credit_hold_id=' . $row['id'] . '\',\'CH-Regionwise\',\'lg\')" >
                    <i class="fa fa-file-excel-o"></i>
                    </button>';//onclick="window.open($(\'#liveSite\').val() + \'index.php?r=Credithold/DownloadCreditHoldRegion&credit_hold_id=' . $row['id'] . '\', \'_blank\');"

                    $div .= '<button type="button" class="btn btn-xs btn-primary" title="Stock_Ageing-Distributor_wise" onclick="loadPopupModal(\'credithold/FilterPage&ch_rep_type=3&credit_hold_id=' . $row['id'] . '\',\'StockAgeing-Distributerwise\',\'lg\')" >
                    <i class="fa fa-file-excel-o"></i>
                    </button>';//onclick="window.open($(\'#liveSite\').val() + \'index.php?r=Credithold/DownloadStockAgeingDistributor&credit_hold_id=' . $row['id'] . '\', \'_blank\');"
                    $div .= '<button type="button" class="btn btn-xs btn-primary" title="Stock_Ageing-Region_wise" onclick="loadPopupModal(\'credithold/FilterPage&ch_rep_type=4&credit_hold_id=' . $row['id'] . '\',\'StockAgeing-Regionwise\',\'lg\')">
                    <i class="fa fa-file-excel-o"></i>
                    </button>';// onclick="window.open($(\'#liveSite\').val() + \'index.php?r=Credithold/DownloadStockAgeingRegion&credit_hold_id=' . $row['id'] . '\', \'_blank\');"
                    //will remove in live
                    $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" onclick="deleteCreditHold(' . $row['id'] . ')"><i class="fa fa-trash"></i></button>';
                    if ($row['have_errors'] == "Y") {
                        $div .= CHtml::link(
                            'Download log',
                            ['credithold/DownloadErrorCsv', 'id' => $row['ch_file_name']],
                            ['style' => 'border: 1px solid #686869; padding: 2px 10px; border-radius: 10px; font-size: 10px; background: #a1a1a1; color: #fff;']
                        );
                    }

                } elseif ($row['is_checked'] == "Y") {
                    if ($row['have_errors'] == "Y") {
                        $div .= CHtml::link(
                            'Download log',
                            ['credithold/DownloadErrorCsv', 'id' => $row['ch_file_name']],
                            ['style' => 'border: 1px solid #686869; padding: 2px 10px; border-radius: 10px; font-size: 10px; background: #a1a1a1; color: #fff;']
                        );
                    }
                    if ($count == 1) {

                        $div .= '<button type="button" class="btn btn-xs btn-primary" title="Upload Next" onclick="SaveInitialUploadNext(' . $row['id'] . ');">
                    <i class="fa fa-arrow-up"></i>
                    </button>';
                    } else {
                        $div .= '<button type="button" class="btn btn-xs btn-primary" title="Upload Next" onclick="SaveUploadNext(' . $row['id'] . ');">
                        <i class="fa fa-arrow-up"></i>
                        </button>';
                    }
                    $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" onclick="deleteCreditHold(' . $row['id'] . ')"><i class="fa fa-trash"></i></button>';

                } else {
                    if ($count == 1) {
                        $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" title="Check Errors" onclick="CheckErrorsCreditHoldInitial(' . $row['id'] . ')"><i class="fa fa-retweet"></i></button>';
                    } else {
                        $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" title="Check Errors" onclick="CheckErrorsCreditHold(' . $row['id'] . ')"><i class="fa fa-retweet"></i></button>';
                    }
                    $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" title="Delete File" onclick="deleteCreditHold(' . $row['id'] . ')"><i class="fa fa-trash"></i></button>';
                }
                $rowData[] = array(
                    $m,
                    $arr_months[(int) $row['ch_month']],
                    $row['ch_year'],
                    $row['last_entry'] . ' out of ' . $row['total_entries'],
                    $row['is_master'],
                    '<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 master file directly on first time
    public function actionSaveInitialUpload()
    {
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {
            $model = new CreditHold();
            $other = array();
            $flag_stop = 'N';
            if (isset($_POST['CreditHold'])) {
                if ((int) @$_POST['CreditHold']['id'] > 0) {
                    $id = (int) @$_POST['CreditHold']['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['CreditHold'];
                $model->is_master = 'Y';
                $file = $_POST['CreditHold']['file_name'];
                $model->ch_file_name = $file;
                $valid = $model->validate();
                $i = 0;
                $arr_products = [];
                $arr_kit = [];
                $modelMasSettings = new MasSettings();
                $currencyRates = [
                    'AED' => (float) $modelMasSettings->GetCurrenctAmt('AED'),
                    'EUR' => (float) $modelMasSettings->GetCurrenctAmt('EUR'),
                ];
                if ($file != '') {
                    $locfile = "upload/credithold/" . $file;
                    $lines = file($locfile);
                    if ($lines !== false) {
                        $lineCount = count($lines);
                    } else {
                        $other[] = 'Error: Unable to read the file.';
                        $valid = false;
                    }
                    $totalrows = $lineCount;

                } else {
                    $other[] = 'File Not Uploaded';
                    $valid = false;
                }
                if ($valid) {
                    $model->last_entry = 0;
                    $model->total_entries = $totalrows;
                    $model->save();
                    $jsonData['status'] = 'success';
                    $jsonData['completed'] = $model->is_completed;
                    $jsonData['last_entry'] = $model->last_entry;
                    $jsonData['msg'] = "CreditHold File Uploaded. Please Check For Errors and Upload";
                    $jsonData['id'] = $model->id;
                    echo CJSON::encode($jsonData);
                } else {
                    $error1 = json_decode(CActiveForm::validate($model, '', false), true);
                    $error = CJSON::encode(array_merge_recursive($error1, $other));
                    if ($error != '[]')
                        echo $error;
                }
            }
        } else {
            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionSaveInitialUploadNext()
    {
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {
            $model = new CreditHold();
            $other = array();
            $flag_stop = 'N';
            $last_entry = 0;
            $total_entry = 0;
            $limit_entry = 0;

            if ((int) @$_REQUEST['id'] > 0) {
                $id = (int) @$_REQUEST['id'];
                $model = $model->findByPk($id);
                $last_entry = (int) $model->last_entry;
                $total_entry = (int) $model->total_entries;
                $limit_entry = (int) $last_entry + 1500;
                if ($limit_entry > $total_entry) {
                    $limit_entry = $total_entry;
                }
                $file = $model->ch_file_name;
                $valid = true;
            }


            $i = 0;
            $arr_products = [];
            $arr_kit = [];
            $arr_not_exist_dis = [];
            $modelMasSettings = new MasSettings();
            $currencyRates = [
                'AED' => (float) $modelMasSettings->GetCurrenctAmt('AED'),
                'EUR' => (float) $modelMasSettings->GetCurrenctAmt('EUR'),
            ];
            function formatDate($date)
            {
                $dt = DateTime::createFromFormat('d/m/Y', $date) ?: DateTime::createFromFormat('j/n/Y', $date);
                if ($dt) {
                    return $dt->format('Y-m-d'); // Convert to yyyy-mm-dd
                }
                return false; // Invalid date format
            }
            $batchData = [];
            if ($file != '') {

                if (($handle = fopen("upload/credithold/" . $file, "r")) !== FALSE) {
                    $n = 1;
                    $arr_months = Order::getmonths();
                    if (empty($model)) {
                        $other[] = 'File Not Found';
                        $valid = false;
                    } else {
                        // Fetch all distributors in a single query
                        $distributors = Distributers::model()->findAll();
                        $distributorMap = [];

                        foreach ($distributors as $d) {
                            $aliases = explode(',', $d->alias_name);
                            foreach ($aliases as $alias) {
                                $alias = strtoupper(trim($alias)); // normalize to lowercase
                                $distributorMap[$alias] = $d;
                            }
                        }
                        // Fetch inventory items in a single query
                        $inventoryItems = Inventory::model()->findAll(['condition' => '(is_active="Y" OR is_active="D" )']);
                        $inventoryMap = [];
                        foreach ($inventoryItems as $inv) {
                            $inventoryMap[$inv->gmc_code] = $inv;
                        }
                        while (($row = fgetcsv($handle)) !== FALSE) {
                            if ($n > $last_entry && $flag_stop == 'N') {
                                $gmc_code = trim($row[2]); //gmc
                                $model_no = trim($row[3]); //model
                                $supplier_code = strtoupper(trim($row[0]));
                                $distributer_model = isset($distributorMap[$supplier_code]) ? $distributorMap[$supplier_code] : [];
                                $region_id = (int) $distributer_model['region_id'];
                                $area_id = (int) $distributer_model['area_id'];
                                $order_date = formatDate($row[4]);
                                $request_date = formatDate($row[5]);
                                //$order_date = $row[4];
                                //$request_date= $row[5];
                                $currency = trim($row[15]);
                                $ex_comment = trim($row[20]);
                                $ch_status = trim($row[10]);
                                $original_shipment_month = $row[9];
                                $shipmentdateTime = DateTime::createFromFormat("M'y", $original_shipment_month);
                                if ($shipmentdateTime) {
                                    $shipment_month = $shipmentdateTime->format("m");
                                    $shipment_year = $shipmentdateTime->format("Y");
                                } else {
                                    // Handle the error, e.g., by setting default values or logging the issue
                                    /*$shipment_month = null;
                                    $shipment_year = null;
                                    $other[] = "Invalid date format: " . $original_shipment_month;
                                    $valid = false;*/
                                }
                                $orderline_no = $row[8];
                                $arr_ordline = explode('-', $orderline_no);
                                if ($row[11] == "4+")
                                    $stock_ageing = 5;
                                else
                                    $stock_ageing = $row[11];
                                $unit_price = trim($row[13]);
                                $unit_price = str_replace(['$', ','], '', $unit_price); // Remove $ and commas
                                $unit_price = number_format((float) $unit_price, 2, '.', ''); // Convert to float and format to 2 decimal places
                                $amount = trim($row[14]);
                                $amount = str_replace(['$', ','], '', $amount); // Remove $ and commas$shipmentdateTime
                                $amount = number_format((float) $amount, 2, '.', ''); // Convert to float and format to 2 decimal places
                                $amount_in_usd = trim($row[16]);//isset($currencyRates[$currency]) ? (float)$amount * $currencyRates[$currency] : (float)$amount;
                                $amount_in_usd = str_replace(['$', ','], '', $amount_in_usd); // Remove $ and commas
                                $amount_in_usd = number_format((float) $amount_in_usd, 2, '.', ''); // Convert to float and format to 2 decimal places
                                $mod_inv = isset($inventoryMap[$gmc_code]) ? $inventoryMap[$gmc_code] : [];
                                if ($mod_inv['product_category_id'] == 32) {
                                    $shipmentdateTime = date('Y-m-d H:i:s');
                                    $shipment_month = $model->ch_month;
                                    $shipment_year = $model->ch_year;
                                }

                                if (!empty($mod_inv) && !empty($distributer_model) && $order_date && $request_date && $shipmentdateTime) {

                                    $batchData[] = [
                                        'credit_hold_id' => $id,
                                        'product_id' => $mod_inv['id'],
                                        'product_category_id' => $mod_inv['product_category_id'],
                                        'distributor_id' => $distributer_model['id'],
                                        'region_id' => $region_id,
                                        'area_id' => $area_id,
                                        'account_code' => trim($row[0]),
                                        'distributor' => $distributer_model['distributer_name'],
                                        'gmc' => $gmc_code,
                                        'model' => $model_no,
                                        'order_date' => $order_date,
                                        'request_date' => $request_date,
                                        'order_no' => $arr_ordline[0],
                                        'line_no' => $arr_ordline[1],
                                        'order_line_no' => $orderline_no,
                                        'original_shipment_month' => $original_shipment_month,
                                        'shipment_month' => $shipment_month,
                                        'shipment_year' => $shipment_year,
                                        'status' => $ch_status,
                                        'stock_ageing' => $stock_ageing,
                                        'quantity' => trim($row[12]),
                                        'unit' => $unit_price,
                                        'amount' => $amount,
                                        'currency' => $currency,
                                        'amount_in_usd' => $amount_in_usd,
                                        'unit_cbm' => $mod_inv['cbm'],
                                        'total_cbm' => $mod_inv['cbm'] * trim($row[12]),
                                        'pi_no' => trim($row[19]),
                                        'ex_comment' => $ex_comment,
                                        'in_comment' => trim($row[21])
                                    ];
                                }

                                if ($n >= $limit_entry) {
                                    $flag_stop = 'Y';
                                }
                            } else {
                                if ($n > $last_entry)
                                    break;
                            }
                            $n++;
                        }

                    }
                    fclose($handle);
                }
                $locfile = "upload/credithold/" . $file;
                $lines = file($locfile);

                if ($lines !== false) {
                    $lineCount = count($lines);
                } else {
                    $other[] = 'Error: Unable to read the file.';
                    $valid = false;
                }
                $totalrows = $lineCount;

            } else {
                $other[] = 'File Not Uploaded';
                $valid = false;
            }
            if ($valid) {
                if (!empty($batchData)) {
                    $this->bulkInsert('ymh_credit_hold_details', $batchData);
                }
                $model->last_entry = $limit_entry;
                $model->total_entries = $totalrows;
                if (($model->last_entry) >= ($model->total_entries)) {
                    $model->is_completed = 'Y';
                }
                $model->save();

                $jsonData['status'] = 'success';
                $jsonData['completed'] = $model->is_completed;
                $jsonData['last_entry'] = $model->last_entry;
                $jsonData['msg'] = "CreditHold Initial Data Upto " . $model->last_entry . " Uploaded. Press Next to Continue Upload";
                $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 = array_merge_recursive($error1, $error2, $other);
                $jsonData['status'] = 'failed';
                $jsonData['msg'] = $error;
                echo CJSON::encode($jsonData);
            }

        } else {

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

    public function actionSaveUpload()
    {
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {
            $model = new CreditHold();
            $other = array();

            if (isset($_POST['CreditHold'])) {
                if ((int) @$_POST['CreditHold']['id'] > 0) {
                    $id = (int) @$_POST['CreditHold']['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['CreditHold'];
                $file = $_POST['CreditHold']['file_name'];
                $model->ch_file_name = $file;
                $model->is_master = $_POST['CreditHold']['is_master'];
                $valid = $model->validate();
                $mod_his = CreditHold::model()->find(array('condition' => ' ch_month = "' . $_POST['CreditHold']['ch_month'] . '" and ch_year = "' . $_POST['CreditHold']['ch_year'] . '" AND is_master="Y"'));
                $mod_his_compl = CreditHold::model()->find(array('condition' => 'is_completed="N" '));
                if (!empty($mod_his_compl)) {
                    $other[] = 'Please Complete Uploading the previous file before adding New';
                    $valid = false;
                }
                if (!empty($mod_his) && $_POST['CreditHold']['is_master'] == "Y") {
                    $other[] = 'Already added Data For Master';
                    $valid = false;
                }
                if ($file != '') {
                    $locfile = "upload/credithold/" . $file;
                    $lines = file($locfile);
                    if ($lines !== false) {
                        $lineCount = count($lines);
                    } else {
                        $other[] = 'Error: Unable to read the file.';
                        $valid = false;
                    }
                    $totalrows = $lineCount;

                } else {
                    $other[] = 'File Not Uploaded';
                    $valid = false;
                }
                if ($valid) {
                    $model->last_entry = 0;
                    $model->total_entries = $totalrows;
                    $model->save();
                    $jsonData['status'] = 'success';
                    $jsonData['completed'] = $model->is_completed;
                    $jsonData['last_entry'] = $model->last_entry;
                    $jsonData['msg'] = "CreditHold File Uploaded. Please Check For Errors and Upload";
                    $jsonData['id'] = $model->id;
                    echo CJSON::encode($jsonData);
                } else {
                    $error1 = json_decode(CActiveForm::validate($model, '', false), true);
                    $error = CJSON::encode(array_merge_recursive($error1, $other));
                    if ($error != '[]')
                        echo $error;
                }
            }
        } else {

            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionSaveUploadNext()
    {
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_SPECIAL_CHARS);
        if (count($_POST) > 0) {
            $model = new CreditHold();
            $other = array();
            $flag_stop = 'N';
            $last_entry = 0;
            $total_entry = 0;
            $limit_entry = 0;

            if ((int) @$_REQUEST['id'] > 0) {
                $id = (int) @$_REQUEST['id'];
                $model = $model->findByPk($id);
                $last_entry = (int) $model->last_entry;
                $total_entry = (int) $model->total_entries;
                $limit_entry = (int) $last_entry + 1500;
                if ($limit_entry > $total_entry) {
                    $limit_entry = $total_entry;
                }
                $file = $model->ch_file_name;
                $valid = true;
            }

            $i = 0;
            $arr_products = [];
            $arr_kit = [];
            $modelMasSettings = new MasSettings();

            $currencyRates = [
                'AED' => (float) 1 / $modelMasSettings->GetCurrenctAmt('AED'),
                'EUR' => (float) $modelMasSettings->GetCurrenctAmt('EUR'),
            ];
            $batchData = [];
            if ($file != '') {
                if (($handle = fopen("upload/credithold/" . $file, "r")) !== FALSE) {
                    $n = 1;

                    $arr_months = Order::getmonths();

                    // Fetch all distributors in a single query
                    $distributors = Distributers::model()->findAll();
                    $distributorMap = [];
                    foreach ($distributors as $d) {
                        $aliases = explode(',', $d->alias_name);
                        foreach ($aliases as $alias) {
                            $alias = strtoupper(trim($alias)); // normalize to lowercase
                            $distributorMap[$alias] = $d;
                        }
                    }
                    // Fetch inventory items in a single query
                    $inventoryItems = Inventory::model()->findAll(['condition' => '(is_active="Y" OR is_active="D" )']);
                    $inventoryMap = [];
                    foreach ($inventoryItems as $inv) {
                        $inventoryMap[$inv->gmc_code] = $inv;
                    }
                    while (($row = fgetcsv($handle)) !== FALSE) {
                        if ($n > $last_entry && $flag_stop == 'N') {
                            $gmc_code = trim($row[34]); //gmc//AI
                            $model_no = trim($row[9]); //model
                            $supplier_code = strtoupper(trim($row[3]));
                            $distributer_model = isset($distributorMap[$supplier_code]) ? $distributorMap[$supplier_code] : [];
                            //Distributers::model()->with('region_det')->find(array('condition' => 'alias_name="' . $supplier_code . '"'));
                            $region_id = (int) $distributer_model['region_id'];
                            $area_id = (int) $distributer_model['area_id'];
                            //$cnd = 'gmc_code="' . $gmc_code . '" AND (is_active="Y" OR is_active="D" )';
                            $order_date = $row[14] . '-' . $row[15] . '-' . $row[16];
                            $request_date = $row[11] . '-' . $row[12] . '-' . $row[13];
                            $currency = trim($row[40]);
                            $ex_comment = trim($row[43]);
                            $ch_status = trim($row[19]);

                            //$ex_comment = "SHIFT TO OCT'23";

                            // Extract the last 6 characters
                            $original_shipment_month = substr($ex_comment, -6);

                            // Replace single quote with hyphen
                            $formatted_shipment_month = str_replace("'", "-", $original_shipment_month);

                            // Convert to Title Case (e.g., "OCT" to "Oct")
                            $formatted_shipment_month = ucwords(strtoupper($formatted_shipment_month));

                            
                            $mod_inv = isset($inventoryMap[$gmc_code]) ? $inventoryMap[$gmc_code] : [];
                            if ($mod_inv['product_category_id'] == 32) {
                                $shipmentdateTime = date('Y-m-d H:i:s');
                                $shipment_month = $model->ch_month;
                                $shipment_year = $model->ch_year;
                            }else{
                                // Parse the date
                                $shipmentdateTime = DateTime::createFromFormat("M-y", $formatted_shipment_month);
                                if ($shipmentdateTime !== false) {
                                    // Use $shipment_month and $shipment_year as needed
                                    $shipment_month = $shipmentdateTime->format("m");
                                    $shipment_year = $shipmentdateTime->format("Y");
                                }
                                
                            }
                            if ($shipmentdateTime !== false) {
                                

                                $stock_ageing = '0';
                                //Status any,  shipmentmonth >=currnt   month        , stock ageing month=0 
                                //echo '$shipment_month= '.$shipment_month.'$shipment_year= '.$shipment_year.'ch_month= '.$model->ch_month.'ch_year= '.$model->ch_year;
                                if ($ch_status <= 2) {
                                    $stock_ageing = 0;
                                } else if ($ch_status == 4) {
                                    $stock_ageing = '';
                                } else {//status=3;

                                    if ($shipment_month == $model->ch_month && ($shipment_year == $model->ch_year)) {//9>2 && 24!=25
                                        $stock_ageing = 1;
                                    } else if (($shipment_month > $model->ch_month && ($shipment_year == $model->ch_year)) || ($shipment_year > $model->ch_year)) {//9>2 && 24!=25
                                        $stock_ageing = 0;
                                    } else {
                                        $modelHistory = new CreditHoldDetails();
                                        $line_trimmed = substr(trim($row[18]), 0, -1);//lastdigit avoided
                                        $modelHistory = $modelHistory->with('parent')->find(['condition' => 'parent.is_master ="Y" AND order_no="' . trim($row[17]) . '" AND line_no = "'.trim($row[18]).'" AND credit_hold_id!=' . $id, 'order' => 't.id DESC']);
                                        if (!empty($modelHistory)) {
                                            $stock_ageing = (int) $modelHistory->stock_ageing + 1;
                                        } else {
                                            $modelHistorytrimmed = new CreditHoldDetails();
                                            $modelHistorytrimmed = $modelHistorytrimmed->with('parent')->find(['condition' => 'parent.is_master ="Y" AND order_no="'.trim($row[17]).'" AND line_no LIKE "' . $line_trimmed . '%" AND credit_hold_id!=' . $id, 'order' => 't.line_no DESC,t.id DESC']);
                                            if (!empty($modelHistorytrimmed)) {
                                                $stock_ageing = (int) $modelHistorytrimmed->stock_ageing + 1;
                                            } else {
                                                $stock_ageing = 2;
                                            }
                                        }
                                    }
                                }
                                //echo '$stock_ageing ='.$stock_ageing.'<br/>';
                                $unit_price = trim($row[38]);
                                $unit_price = str_replace(['$', ','], '', $unit_price); // Remove $ and commas
                                $unit_price = number_format((float) $unit_price, 2, '.', ''); // Convert to float and format to 2 decimal places
                                $amount = trim($row[39]);
                                $amount = str_replace(['$', ','], '', $amount); // Remove $ and commas
                                $amount = number_format((float) $amount, 2, '.', ''); // Convert to float and format to 2 decimal places

                                $amount_in_usd = isset($currencyRates[$currency]) ? (float) $amount * $currencyRates[$currency] : (float) $amount;
                                //$mod_inv = Inventory::model()->find(array('condition' => $cnd));
                                

                                if (!empty($mod_inv) && !empty($distributer_model)) {
                                    $batchData[] = [
                                        'credit_hold_id' => $id,
                                        'product_id' => $mod_inv['id'],
                                        'product_category_id' => $mod_inv['product_category_id'],
                                        'distributor_id' => $distributer_model['id'],
                                        'region_id' => $region_id,
                                        'area_id' => $area_id,
                                        'account_code' => $supplier_code,
                                        'distributor' => $distributer_model['distributer_name'],
                                        'gmc' => $gmc_code,
                                        'model' => $model_no,
                                        'order_date' => $order_date,
                                        'request_date' => $request_date,
                                        'order_no' => trim($row[17]),
                                        'line_no' => trim($row[18]),
                                        'order_line_no' => trim($row[17]) . '-' . trim($row[18]),
                                        'original_shipment_month' => $original_shipment_month,
                                        'shipment_month' => $shipment_month,
                                        'shipment_year' => $shipment_year,
                                        'status' => $ch_status,
                                        'stock_ageing' => $stock_ageing,
                                        'quantity' => trim($row[20]),
                                        'unit' => $unit_price,
                                        'amount' => $amount,
                                        'currency' => $currency,
                                        'amount_in_usd' => $amount_in_usd,
                                        'unit_cbm' => $mod_inv['cbm'],
                                        'total_cbm' => $mod_inv['cbm'] * trim($row[20]),
                                        'pi_no' => trim($row[42]),
                                        'ex_comment' => $ex_comment,
                                        'in_comment' => trim($row[44])
                                    ];
                                    /*$modelItems[$i] = new CreditHoldDetails();
                                    $modelItems[$i]->credit_hold_id = 0;
                                    $modelItems[$i]->product_id = $mod_inv['id'];
                                    $modelItems[$i]->product_category_id = $mod_inv['product_category_id'];
                                    $modelItems[$i]->distributor_id = $distributer_model['id'];
                                    $modelItems[$i]->account_code = trim($row[3]);
                                    $modelItems[$i]->distributor = $distributer_model['distributer_name'];
                                    $modelItems[$i]->gmc = $gmc_code;
                                    $modelItems[$i]->model = $model_no;
                                    $modelItems[$i]->order_date = $order_date;
                                    $modelItems[$i]->request_date =$request_date;
                                    $modelItems[$i]->order_no = trim($row[17]);
                                    $modelItems[$i]->line_no = trim($row[18]);
                                    $modelItems[$i]->order_line_no =trim($row[17]).'-'.trim($row[18]);
                                    $modelItems[$i]->original_shipment_month = $original_shipment_month;
                                    $modelItems[$i]->shipment_month = $shipment_month;
                                    $modelItems[$i]->shipment_year  =  $shipment_year;
                                    $modelItems[$i]->status = $ch_status;
                                    $modelItems[$i]->stock_ageing = $stock_ageing;
                                    $modelItems[$i]->quantity = trim($row[20]);
                                    $modelItems[$i]->unit = $unit_price;
                                    $modelItems[$i]->amount = $amount;
                                    $modelItems[$i]->currency = $currency;
                                    $modelItems[$i]->amount_in_usd = $amount_in_usd;
                                    $modelItems[$i]->unit_cbm =$mod_inv['cbm'];
                                    $modelItems[$i]->total_cbm = $mod_inv['cbm']*trim($row[20]);
                                    $modelItems[$i]->pi_no = trim($row[42]);
                                    $modelItems[$i]->ex_comment = $ex_comment;
                                    $modelItems[$i]->in_comment = trim($row[44]);
                                    $modelItems[$i]->region_id = $region_id;
                                    $valid = $modelItems[$i]->validate() && $valid;
                                    $i++;*/
                                }
                                /*else {

                                    if (empty($distributer_model)) {
                                        $other[] = 'Distributor Not Exists '.$supplier_code;
                                        $valid = false;
                                    }else{
                                        $other[] = 'GMC with code ' . $gmc_code . ' and model ' . $model_no . ' not exits';
                                        $valid = false;
                                    }
                                }*/
                            }
                            /*else {
                                // Handle parsing error
                                $other[] = "Error: Unable to parse the shipment month on line ".$n;
                                $valid = false;
                            }*/


                            if ($n >= $limit_entry) {
                                $flag_stop = 'Y';
                            }
                        } else {
                            if ($n > $last_entry) {
                                break;
                            }

                        }
                        $n++;
                    }

                    fclose($handle);
                    $locfile = "upload/credithold/" . $file;
                    $lines = file($locfile);
                    if ($lines !== false) {
                        $lineCount = count($lines);
                    }
                    $totalrows = $lineCount;
                }

            } else {
                $other[] = 'File Not Uploaded';
                $valid = false;
            }
            if ($valid) {

                $model->last_entry = $limit_entry;
                $model->total_entries = $totalrows;
                if (($model->last_entry) >= ($model->total_entries)) {
                    $model->is_completed = 'Y';
                }
                $model->save();
                if (!empty($batchData)) {
                    $this->bulkInsert('ymh_credit_hold_details', $batchData);
                }
                /*if (!empty($modelItems)) {
                    foreach ($modelItems as $each) {
                        $each->credit_hold_id  = $model->id;
                        $each->save(false);
                    }
                }*/
                $jsonData['status'] = 'success';
                $jsonData['completed'] = $model->is_completed;
                $jsonData['last_entry'] = $status;
                $jsonData['msg'] = "CreditHold Data Upto " . $model->last_entry . " Uploaded. Press Next to Continue Upload";
                $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 = array_merge_recursive($error1, $error2, $other);
                $jsonData['status'] = 'failed';
                $jsonData['msg'] = $error;
                echo CJSON::encode($jsonData);
            }

        } else {

            throw new CHttpException(404, 'Page not found.');
        }
    }
    public function actionCheckErrorsCreditHold()
    {
        $model = new CreditHold();
        $other = [];
        $valid = true;
        $arr_err_list = [];

        if (!isset($_REQUEST['id']) || (int) $_REQUEST['id'] <= 0) {
            echo CJSON::encode(['error' => 'Invalid Id']);
            return;
        }

        $id = (int) $_REQUEST['id'];
        $model = $model->findByPk($id);
        if (!$model) {
            echo CJSON::encode(['error' => 'Credit Hold entry not found.']);
            return;
        }

        $file = $model->ch_file_name;
        if (empty($file)) {
            echo CJSON::encode(['error' => 'File Not Uploaded']);
            return;
        }

        $locfile = "upload/credithold/" . $file;
        $error_url = "upload/credithold/error/" . $file;

        // Fetch all distributors and inventory items in one query
        $distributors = Distributers::model()->findAll();
        $distributorMap = [];
        foreach ($distributors as $d) {
            $aliases = explode(',', $d->alias_name);
            foreach ($aliases as $alias) {
                $alias = strtoupper(trim($alias)); // normalize to lowercase
                $distributorMap[$alias] = $d;
            }
            //$distributorMap[strtoupper(trim($d->alias_2_name))] = $d;
        }
        //$distributorMap = array_flip(array_column(Distributers::model()->findAll(), 'alias_name'));
        $inventoryMap = array_flip(array_column(Inventory::model()->findAll(['condition' => '(is_active="Y" OR is_active="D")']), 'gmc_code'));
        $inventoryItems = Inventory::model()->findAll(['condition' => '(is_active="Y" OR is_active="D" )']);
        $inventoryMaps = [];
        foreach ($inventoryItems as $inv) {
            $inventoryMaps[$inv->gmc_code] = $inv;
        }
        // Check if file exists before processing
        if (!file_exists($locfile) || !is_readable($locfile)) {
            echo CJSON::encode(['error' => 'Invalid File']);
            return;
        }

        $fileObj = new SplFileObject($locfile);
        $fileObj->setFlags(SplFileObject::READ_CSV);

        $lineNumber = 0;
        foreach ($fileObj as $row) {
            $lineNumber++;
            if ($lineNumber === 1 || $lineNumber === 2 || $lineNumber === 3 || empty($row))
                continue; // Skip header row & empty rows

            $gmc_code = trim($row[34]); // GMC Code
            $model_no = trim($row[9]);  // Model
            $supplier_code = strtoupper(trim($row[3])); // Supplier Code
            $ex_comment = trim($row[43]);
            //$ex_comment = "SHIFT TO OCT'23";// Extract the last 6 characters
            $original_shipment_month = substr($ex_comment, -6);
            // Replace single quote with hyphen
            $formatted_shipment_month = str_replace("'", "-", $original_shipment_month);
            // Convert to Title Case (e.g., "OCT" to "Oct")
            $formatted_shipment_month = ucwords(strtoupper($formatted_shipment_month));
            // Parse the date
            
            $mod_inv = isset($inventoryMaps[$gmc_code]) ? $inventoryMaps[$gmc_code] : [];
            if ($mod_inv['product_category_id'] == 32) {
                $shipmentdateTime = date('Y-m-d H:i:s');
                $shipment_month = $model->ch_month;
                $shipment_year = $model->ch_year;
            }else{
                $shipmentdateTime = DateTime::createFromFormat("M-y", $formatted_shipment_month);
            }
            if ($shipmentdateTime === false) {
                $arr_err_list[] = "Error: Unable to parse the shipment month on line $lineNumber";
            }
            if (!isset($distributorMap[$supplier_code])) {
                $arr_err_list[] = "Distributor Not Exists: $supplier_code in line $lineNumber";
            }
            if (!isset($inventoryMap[$gmc_code])) {
                $arr_err_list[] = "GMC with code $gmc_code and model $model_no not exists in line $lineNumber";
            }
        }

        if (!empty($arr_err_list)) {
            $model->have_errors = "Y";
            $model->is_checked = "Y";
            $model->save(false);
            file_put_contents($error_url, implode("\n", $arr_err_list)); // Write errors in one go

            echo CJSON::encode([
                'error_url' => $error_url,
                'msg' => 'Have errors. Fix Errors /Continue to Upload',
                'filename' => $file,
                'link' => CHtml::link(
                    'Download log',
                    ['credithold/DownloadErrorCsv', 'id' => $file],
                    ['target' => '_blank', 'style' => 'border: 1px solid #686869; padding: 2px 10px; border-radius: 10px; font-size: 10px; background: #a1a1a1; color: #fff;']
                ),
                'status' => 'success',
                'id' => $model->id
            ]);
        } else {
            $model->have_errors = "N";
            $model->is_checked = "Y";
            $model->save(false);
            echo CJSON::encode([
                'msg' => 'No errors. Continue to Upload',
                'status' => 'success',
                'id' => $model->id
            ]);
        }
    }
    public function actionCheckErrorsCreditHoldInitial()
    {
        $model = new CreditHold();
        $other = [];
        $valid = true;
        $arr_err_list = [];

        if (!isset($_REQUEST['id']) || (int) $_REQUEST['id'] <= 0) {
            echo CJSON::encode(['error' => 'Invalid Id']);
            return;
        }

        $id = (int) $_REQUEST['id'];
        $model = $model->findByPk($id);
        if (!$model) {
            echo CJSON::encode(['error' => 'Credit Hold entry not found.']);
            return;
        }

        $file = $model->ch_file_name;
        if (empty($file)) {
            echo CJSON::encode(['error' => 'File Not Uploaded']);
            return;
        }

        $locfile = "upload/credithold/" . $file;
        $error_url = "upload/credithold/error/" . $file;

        // Fetch all distributors and inventory items in one query
        $distributors = Distributers::model()->findAll();
        $distributorMap = [];
        foreach ($distributors as $d) {
            $aliases = explode(',', $d->alias_name);
            foreach ($aliases as $alias) {
                $alias = strtoupper(trim($alias)); // normalize to lowercase
                $distributorMap[$alias] = $d;
            }
        }
        //$distributorMap = array_flip(array_column(Distributers::model()->findAll(), 'alias_name'));
        $inventoryMap = array_flip(array_column(Inventory::model()->findAll(['condition' => '(is_active="Y" OR is_active="D")']), 'gmc_code'));
        function formatDate($date)
        {
            $dt = DateTime::createFromFormat('d/m/Y', $date) ?: DateTime::createFromFormat('j/n/Y', $date);
            if ($dt) {
                return $dt->format('Y-m-d'); // Convert to yyyy-mm-dd
            }
            return false; // Invalid date format
        }
        /*function formatDate($date) {
            $dt = DateTime::createFromFormat('d/m/Y', $date);
            if ($dt && $dt->format('d/m/Y') === $date) {
                return $dt->format('Y-m-d'); // Convert to yyyy-mm-dd
            }
            else
            return false; // Invalid date format
        }*/
        // Check if file exists before processing
        if (!file_exists($locfile) || !is_readable($locfile)) {
            echo CJSON::encode(['error' => 'Invalid File']);
            return;
        }

        $fileObj = new SplFileObject($locfile);
        $fileObj->setFlags(SplFileObject::READ_CSV);

        $lineNumber = 0;
        foreach ($fileObj as $row) {
            $lineNumber++;
            if ($lineNumber === 1 || $lineNumber === 2 || $lineNumber === 3 || empty($row))
                continue; // Skip header row & empty rows
            $gmc_code = trim($row[2]); //gmc
            $model_no = trim($row[3]); //model
            $supplier_code = strtoupper(trim($row[0]));
            $distributer_model = isset($distributorMap[$supplier_code]) ? $distributorMap[$supplier_code] : [];
            $region_id = (int) $distributer_model['region_id'];
            $order_date = trim($row[4]);
            $request_date = trim($row[5]);
            $ex_comment = trim($row[20]);
            $ch_status = trim($row[10]);
            if(!empty($gmc_code)||!empty($model_no)||!empty($supplier_code)||!empty($order_date)||!empty($request_date)||!empty($ex_comment)||!empty($ch_status)){
                if (!isset($distributorMap[$supplier_code])) {
                    $arr_err_list[] = "Distributor Not Exists: $supplier_code in line $lineNumber";
                }
                if (!isset($inventoryMap[$gmc_code])) {
                    $arr_err_list[] = "GMC with code $gmc_code and model $model_no not exists in line $lineNumber";
                }
                $original_shipment_month = $row[9];
                $formatted_order_date = formatDate($order_date);
                $formatted_request_date = formatDate($request_date);
                if ($formatted_order_date === false) {
                    $arr_err_list[] = "Error: Invalid Order Date format:-  $order_date on line $lineNumber";
                }
    
                if ($formatted_request_date === false) {
                    $arr_err_list[] = "Error: Invalid Request Date format:-  $request_date on line $lineNumber";
                }
    
                $shipmentdateTime = DateTime::createFromFormat("M'y", $original_shipment_month);
                $mod_inv = isset($inventoryMap[$gmc_code]) ? $inventoryMap[$gmc_code] : [];
                if ($mod_inv['product_category_id'] == 32) {
                    $shipmentdateTime = date('Y-m-d H:i:s');
                    $shipment_month = $model->ch_month;
                    $shipment_year = $model->ch_year;
                }
                if ($shipmentdateTime) {
                    $shipment_month = $shipmentdateTime->format("m");
                    $shipment_year = $shipmentdateTime->format("Y");
                } else {
                    // Handle the error, e.g., by setting default values or logging the issue
                    $shipment_month = null;
                    $shipment_year = null;
                    $arr_err_list[] = "Error: Unable to parse the shipment month:  $original_shipment_month on line $lineNumber";
                }
            }


        }

        if (!empty($arr_err_list)) {
            $model->have_errors = "Y";
            $model->is_checked = "Y";
            $model->save(false);
            file_put_contents($error_url, implode("\n", $arr_err_list)); // Write errors in one go

            echo CJSON::encode([
                'error_url' => $error_url,
                'msg' => 'Have errors. Fix Errors /Continue to Upload',
                'filename' => $file,
                'link' => CHtml::link(
                    'Download log',
                    ['credithold/DownloadErrorCsv', 'id' => $file],
                    ['target' => '_blank', 'style' => 'border: 1px solid #686869; padding: 2px 10px; border-radius: 10px; font-size: 10px; background: #a1a1a1; color: #fff;']
                ),
                'status' => 'success',
                'id' => $model->id
            ]);
        } else {
            $model->have_errors = "N";
            $model->is_checked = "Y";
            $model->save(false);
            echo CJSON::encode([
                'msg' => 'No errors. Continue to Upload',
                'status' => 'success',
                'id' => $model->id
            ]);
        }
    }

    /*public function actionCheckErrorsCreditHold(){
        $model = new CreditHold();
        $other = array();$err=true;
        $valid=true;$arr_err_list=[];
        if ((int) @$_REQUEST['id'] > 0) {
            $id = (int) @$_REQUEST['id'];
            $model = $model->findByPk($id); 
            $total_entry=(int)$model->total_entries;
            $file = $model->ch_file_name;
            $locfile = "upload/credithold/" . $file;
            //$error_url = Yii::app()->basePath . '/../upload/credithold/error/' . $file;
            $error_url = 'upload/credithold/error/' . $file;
            if ($file != '') {
                // Fetch all distributors in a single query
                $distributors = Distributers::model()->findAll();
                $distributorMap = [];
                foreach ($distributors as $d) {
                    $distributorMap[$d->alias_name] = $d;
                }
                // Fetch inventory items in a single query
                $inventoryItems = Inventory::model()->findAll(['condition'=>'(is_active="Y" OR is_active="D" )']);
                $inventoryMap = [];
                foreach ($inventoryItems as $inv) {
                    $inventoryMap[$inv->gmc_code] = $inv;
                }
                if (($handle = fopen("upload/credithold/" . $file, "r")) !== FALSE) {
                    $n = 1;
                    while (($row = fgetcsv($handle)) !== FALSE) {
                        if ($n > 1) {
                            $gmc_code = trim($row[34]); //gmc
                            $model_no = trim($row[9]); //model
                            $supplier_code = $row[3];
                            $distributer_model = isset($distributorMap[$supplier_code])?$distributorMap[$supplier_code]:[];
                            $mod_inv = isset($inventoryMap[$gmc_code])?$inventoryMap[$gmc_code]:[];
                            if (empty($distributer_model)) {
                                $arr_err_list[] = 'Distributor Not Exists '.$supplier_code.' in line '.$n;
                                $err = false;
                            }
                            if (empty($mod_inv)){
                                $arr_err_list[] = 'GMC with code ' . $gmc_code . ' and model ' . $model_no . ' not exits'.' in line '.$n;
                                $err = false;
                            }
                        }
                        $n++;
                    }
                }else {
                    $other[] = 'Invalid File';
                    $valid = false;
                }
            }else {
                $other[] = 'File Not Uploaded';
                $valid = false;
            }
        }else{
            $other[] = 'Invalid Id';
            $valid = false;
        }
        if ($valid) {
            $list = array();
            if (!empty($arr_err_list)) {
                $file_error = fopen($error_url, "w+");
                foreach ($arr_err_list as $line) {
                    fputcsv($file_error, $line);
                }
                fclose($file_error);
                $jsonData['error_url'] = $error_url;
                $jsonData['filename'] = $file;
                $jsonData['link'] = CHtml::link('Download log', array('credithold/DownloadErrorCsv', 'id' => $file), array('target' => '_blank',
                            'style' => 'border: 1px solid #686869;padding: 2px 10px;border-radius: 10px;font-size: 10px;background: #a1a1a1;color: #fff;'
                            . ''
                ));
            }else{
                $jsonData['msg'] = 'No errors. Continue to Upload';
            }
            $jsonData['status'] = 'success';

            $jsonData['id'] = $model->id;
            echo CJSON::encode($jsonData);
        } else {
            $error = CJSON::encode( $other);
            if ($error != '[]')
                echo $error;
        }

    }*/
    public function actionDownloadErrorCsv()
    {
        $file_name = $event_id = Yii::app()->request->getParam('id');
        //$error_url = Yii::app()->basePath . '../upload/credithold/error/'. $file_name;
        $error_url = 'upload/credithold/error/' . $file_name;
        return Yii::app()->getRequest()->sendFile('log.csv', @file_get_contents($error_url));
    }
    public function actionDownloadCreditHoldMaster()
    {
        $credit_hold_id = $_REQUEST['credit_hold_id'];
        $region_id = $_REQUEST['region_id'];
        $distributer_id = $_REQUEST['distributer_id'];
        $product_cat_id = $_REQUEST['product_cat_id'];
        $status = $_REQUEST['status'];
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_SPECIAL_CHARS);
        $arr_month = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];

        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);
            }
            // Helper function to populate and merge headers across multiple rows
            function setMergedHeader($worksheet, $rowNumber, $headers)
            {
                $column = 'A';
                foreach ($headers as $header) {
                    // Merge cells vertically from row $rowNumber to $rowNumber + 2
                    $worksheet->mergeCells("{$column}{$rowNumber}:{$column}" . ($rowNumber + 2));

                    // Set the header text in the top cell of the merged range
                    $worksheet->setCellValue("{$column}{$rowNumber}", $header);

                    // Center-align the header text
                    $worksheet->getStyle("{$column}{$rowNumber}")->getAlignment()
                        ->setHorizontal(Alignment::HORIZONTAL_CENTER)
                        ->setVertical(Alignment::VERTICAL_CENTER);

                    // Apply bold font to the header
                    $worksheet->getStyle("{$column}{$rowNumber}")->getFont()->setBold(true);

                    $column++;
                }
            }
            // Create new spreadsheet
            $spreadsheet = new Spreadsheet();
            $activeWorksheet = $spreadsheet->getActiveSheet();

            // Define and add column headers
            $columnHeaders = [
                'Account Code',
                'Region',
                'Distributor',
                'ProductCategory',
                'SubCategory',
                'GMC',
                'Model',
                'Order Date',
                'Request Date',
                'Order#',
                'Line#',
                'Order Line#',
                'Original Shipment Month',
                'Status',
                'Stock Ageing',
                'Quantity',
                'Unit',
                'Amount',
                'Currency',
                'Amount In USD',
                'Unit CBM',
                'Total CBM',
                'PI#',
                'Ex Comment',
                'In Comment'
            ];

            $headerRow = 1;
            setMergedHeader($activeWorksheet, $headerRow, $columnHeaders);
            // Set width for column A B C 
            $width = 11; // Desired width in pixels
            for ($col = 'A'; $col <= 'U'; $col++) {
                $activeWorksheet->getColumnDimension($col)->setWidth($width);
            }
            $activeWorksheet->getColumnDimension('X')->setWidth(24);
            $activeWorksheet->getColumnDimension('W')->setWidth(24);
            $activeWorksheet->getColumnDimension('V')->setWidth(24);
            // Add filter for row 1
            $activeWorksheet->setAutoFilter("A1:X1");
            setFillColor($activeWorksheet, "A1:X3", 'D9D9D9');

            // Retrieve data from the database
            $condition = "credit_hold_id = '" . $credit_hold_id . "'";
            if ((int) $distributer_id > 0) {
                $condition .= " AND distributor_id = '" . $distributer_id . "'";
            }
            if ((int) $region_id > 0) {
                $condition .= " AND area_id = '" . $region_id . "'";
            }
            if ((int) $product_cat_id > 0) {
                $condition .= " AND product_category_id = '" . $product_cat_id . "'";
            }
            if ($status != '') {
                $condition .= " AND t.status IN (" . $status . ")";
            }
            $modelCreditHold = new CreditHold();
            $modelCreditHold = $modelCreditHold->findByPK((int) $credit_hold_id);
            $modelCreditHoldDetails = new CreditHoldDetails();
            $modelCreditHoldDetails_list = $modelCreditHoldDetails->with('area_det', 'category_det', 'product_det')->findAll(['condition' => $condition]);

            // Populate data rows
            $dataRow = $headerRow + 1;
            if (!empty($modelCreditHoldDetails_list)) {
                foreach ($modelCreditHoldDetails_list as $each) {
                    $data = [
                        $each['account_code'],
                        $each['area_det']['area_name'],
                        $each['distributor'],
                        $each['category_det']['product_category'],
                        $each['product_det']['sub_category'],
                        $each['gmc'],
                        $each['model'],
                        date("d/m/Y", strtotime($each['order_date'])),
                        date("d/m/Y", strtotime($each['request_date'])),
                        $each['order_no'],
                        $each['line_no'],
                        $each['order_line_no'],
                        $each['original_shipment_month'],
                        $each['status'],
                        $each['stock_ageing'],
                        $each['quantity'],
                        $each['unit'],
                        $each['amount'],
                        $each['currency'],
                        $each['amount_in_usd'],
                        $each['unit_cbm'],
                        $each['total_cbm'],
                        $each['pi_no'],
                        $each['ex_comment'],
                        $each['in_comment'],
                    ];
                    setData($activeWorksheet, $dataRow, $data);
                    $dataRow++;
                }
            } else {
                // No records found, so add a message to row 3
                $activeWorksheet->mergeCells("A3:V3");
                $activeWorksheet->setCellValue('A3', 'Empty Record');
            }

            // Set headers for file download
            $writer = new Xlsx($spreadsheet);
            $fileName = 'CreditHold_Master_' . $arr_month[$modelCreditHold['ch_month'] - 1] . '.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 actionDownloadCreditHoldDetailsWithStatus()
    {
        $credit_hold_id = $_REQUEST['credit_hold_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);
            }
            // Helper function to populate and merge headers across multiple rows
            function setMergedHeader($worksheet, $rowNumber, $headers) {
                $column = 'A';
                foreach ($headers as $header) {
                    // Merge cells vertically from row $rowNumber to $rowNumber + 2
                    $worksheet->mergeCells("{$column}{$rowNumber}:{$column}" . ($rowNumber + 2));

                    // Set the header text in the top cell of the merged range
                    $worksheet->setCellValue("{$column}{$rowNumber}", $header);

                    // Center-align the header text
                    $worksheet->getStyle("{$column}{$rowNumber}")->getAlignment()
                              ->setHorizontal(Alignment::HORIZONTAL_CENTER)
                              ->setVertical(Alignment::VERTICAL_CENTER);

                    // Apply bold font to the header
                    $worksheet->getStyle("{$column}{$rowNumber}")->getFont()->setBold(true);

                    $column++;
                }
            }
            // Create new spreadsheet
            $spreadsheet = new Spreadsheet();
            $activeWorksheet = $spreadsheet->getActiveSheet();



            // Define and add column headers
            $columnHeaders = [
                'Account Code', 'Region','Distributor','Product Category',  'GMC', 'Model', 'Order Date', 'Request Date', 'Order#', 'Line#', 
                'Order Line#', 'Original Shipment Month', 'Status', 'Stock Ageing', 'Quantity', 'Unit', 'Amount', 
                'Currency', 'Amount In USD', 'Unit CBM', 'Total CBM', 'PI#', 'Ex Comment', 'In Comment'
            ];

            $headerRow = 1;
            setMergedHeader($activeWorksheet, $headerRow, $columnHeaders);
            // Set width for column A B C 
            $width = 11; // Desired width in pixels
            for ($col = 'A'; $col <= 'U'; $col++) {
                $activeWorksheet->getColumnDimension($col)->setWidth($width);
            }
            $activeWorksheet->getColumnDimension('X')->setWidth(24);
            $activeWorksheet->getColumnDimension('W')->setWidth(24);
            $activeWorksheet->getColumnDimension('V')->setWidth(24);
            // Add filter for row 1
            $activeWorksheet->setAutoFilter("A1:X1");
            setFillColor($activeWorksheet, "A1:X3", 'D9D9D9'); 
            // Retrieve data from the database
            $condition = "credit_hold_id = '" . $credit_hold_id . "' AND (status=2 OR status=3)";
            $modelCreditHoldDetails = new CreditHoldDetails();
            $modelCreditHoldDetails_list = $modelCreditHoldDetails->with('region_det','category_det')->findAll(['condition' => $condition]);

            // Populate data rows
            $dataRow = $headerRow + 1;
            if (!empty($modelCreditHoldDetails_list)) {
                foreach ($modelCreditHoldDetails_list as $each) {
                    $data = [
                        $each['account_code'],
                        $each['region_det']['region_name'],
                        $each['distributor'],
                        $each['category_det']['product_category'],
                        $each['gmc'],
                        $each['model'],
                        date("d/m/Y", strtotime($each['order_date'])),
                        date("d/m/Y", strtotime($each['request_date'])),
                        $each['order_no'],
                        $each['line_no'],
                        $each['order_line_no'],
                        $each['original_shipment_month'],
                        $each['status'],
                        $each['stock_ageing'],
                        $each['quantity'],
                        $each['unit'],
                        $each['amount'],
                        $each['currency'],
                        $each['amount_in_usd'],
                        $each['unit_cbm'],
                        $each['total_cbm'],
                        $each['pi_no'],
                        $each['ex_comment'],
                        $each['in_comment'],
                    ];
                    setData($activeWorksheet, $dataRow, $data);
                    $dataRow++;
                }
            } else {
                // No records found, so add a message to row 3
                $activeWorksheet->mergeCells("A3:V3");
                $activeWorksheet->setCellValue('A3', 'Empty Record');
            }

            // Set headers for file download
            $writer = new Xlsx($spreadsheet);
            $fileName = 'CreditHold_Status_' . 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 actionDownloadCreditHoldDistributer()
    {
        $credit_hold_id = $_REQUEST['credit_hold_id'];
        $status = $_REQUEST['status'];
        $_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 = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
            $modelCreditHold = new CreditHold();
            $modelCreditHold = $modelCreditHold->findByPK((int) $credit_hold_id);
            $condition = "credit_hold_id='" . $credit_hold_id . "' AND D.consider_for_ch_report='Y' AND ((CH.shipment_month<= " . $modelCreditHold['ch_month'] . "  AND CH.shipment_year=" . $modelCreditHold['ch_year'] . ") OR (CH.shipment_year<" . $modelCreditHold['ch_year'] . "))";
            if ($status != '') {
                $condition .= " AND CH.status IN (" . $status . ")";
            }
            $sql = "SELECT 
                    CH.distributor_id,
                    CH.product_category_id,
                    CH.shipment_month,
                    CH.shipment_year,
                    D.distributer_name,
                    D.alias_name,
                    PC.report_name,
                    PC.report_sort_order,
                    SUM(amount_in_usd) AS grand_total
                FROM 
                    ymh_credit_hold_details CH
                LEFT JOIN ymh_inventory I ON I.id=CH.product_id
                LEFT JOIN ymh_mas_product_category PC ON PC.id=CH.product_category_id
                LEFT JOIN ymh_distributers D ON D.id=CH.distributor_id
                WHERE " . $condition . "
                GROUP BY 
                    CH.distributor_id, 
                    CH.shipment_year, 
                    CH.shipment_month, 
                    PC.report_name
                ORDER BY D.distributer_name,CH.shipment_year,CH.shipment_month,PC.report_sort_order ASC";
            $arr_CH = Yii::app()->db->createCommand($sql)->queryAll();
            $arr_credithold = [];
            $arr_dis = [];
            $arr_credithold_dis = [];
            $arr_credithold_dis_year = [];
            foreach ($arr_CH as $each) {
                $arr_credithold[$each['distributor_id']][$each['shipment_year']][$each['shipment_month']][$each['report_name']]['grand_total'] = $each['grand_total'];
                $arr_dis[$each['distributor_id']] = $each['distributer_name'];
                //for distributerwise sum
                $arr_credithold_dis[$each['distributor_id']][$each['report_name']]['grand_total'] = (isset($arr_credithold_dis[$each['distributor_id']][$each['report_name']]['grand_total']) ? (float) $arr_credithold_dis[$each['distributor_id']][$each['report_name']]['grand_total'] : 0) + (float) $each['grand_total'];
                //for distributer and year wise report
                $arr_credithold_dis_year[$each['distributor_id']][$each['shipment_year']][$each['report_name']]['grand_total'] = (isset($arr_credithold_dis_year[$each['distributor_id']][$each['shipment_year']][$each['report_name']]['grand_total']) ? (float) $arr_credithold_dis_year[$each['distributor_id']][$each['shipment_year']][$each['report_name']]['grand_total'] : 0) + (float) $each['grand_total'];
            }
            $modelCreditHoldDetails = new CreditHoldDetails();
            $modelMasProductCategory = new MasProductCategory();
            $modelMasProductCategory = $modelMasProductCategory->findAll(array('group' => 'report_name', 'order' => 'report_sort_order ASC'));
            // Create new spreadsheet
            $spreadsheet = new Spreadsheet();
            $activeWorksheet = $spreadsheet->getActiveSheet();

            // Set the title in merged cells
            $activeWorksheet->mergeCells('A1:Z1');
            $title = "CREDIT HOLD REPORT " . $arr_month[$modelCreditHold->ch_month - 1] . "'" . $modelCreditHold->ch_year;

            $activeWorksheet->setCellValue('A1', $title);//4+distributer_Count
            $activeWorksheet->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            // Set font size to 16 for cell A1
            $activeWorksheet->getStyle('A1')->getFont()->setSize(16);
            // Freeze the pane at cell C3
            $activeWorksheet->freezePane('C3');

            // Set text wrap and alignment for row number 2
            $cellsToWrapAndCenter = ["A1", "A2", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2", "O2", "P2", "Q2", "R2", "S2", "T2", "U2", "V2", "W2"];
            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 B C individually
            $activeWorksheet->getColumnDimension('A')->setWidth(21.11);
            $activeWorksheet->getColumnDimension('B')->setWidth(12.78);
            $activeWorksheet->getColumnDimension('C')->setWidth(14.67);
            // Set the height of row 1 
            $activeWorksheet->getRowDimension(1)->setRowHeight(46.7);
            // Define and add column headers
            $var = 'C';
            $columnHeaders = [
                'Distributor',
                'Year Final',
                'Month Final'
            ];
            if (!empty($modelMasProductCategory)) {
                foreach ($modelMasProductCategory as $eachcat) {
                    $columnHeaders[] = $eachcat['report_name'];
                    $var++;
                    $activeWorksheet->getColumnDimension($var)->setWidth(13.89);
                }
                $var++;
                $activeWorksheet->getColumnDimension($var)->setWidth(13.89);
            }
            // Add filter for row 2
            $activeWorksheet->setAutoFilter("A2:{$var}2");
            //bold--row 1
            $activeWorksheet->getStyle("A1:{$var}1")->getFont()->setBold(true);

            $columnHeaders[] = 'GrandTotal';
            $var++;
            $headerRow = 2;
            setData($activeWorksheet, $headerRow, $columnHeaders);
            $activeWorksheet->getStyle('A' . $headerRow . ':' . $var . $headerRow)->getFont()->setBold(true);
            // Populate data rows
            $dataRow = $headerRow + 1;
            $dp = '';
            $d = 0;
            $sum_cat = [];
            if (!empty($arr_credithold)) {
                foreach ($arr_credithold as $distributorId => $arr_years) {
                    $y = 0;
                    $dis_total = 0;
                    $yp = '';
                    $mp = '';
                    foreach ($arr_years as $year => $arr_ch_months) {
                        $m = 0;
                        foreach ($arr_ch_months as $shipment_month => $arr_categories) {
                            if (!empty($modelMasProductCategory)) {
                                $data = [];
                                if ($dp != $d) {
                                    $data[] = $arr_dis[$distributorId];
                                    $dp = $d;
                                } else {
                                    $data[] = '';
                                }
                                if ($yp != $y) {
                                    $data[] = $year;
                                    $yp = $y;
                                } else {
                                    $data[] = '';
                                }
                                if ($mp != $m) {
                                    $data[] = $arr_month[$shipment_month - 1];
                                    $mp = $m;
                                } else {
                                    $data[] = '';
                                }

                                $cat_total = 0;
                                foreach ($modelMasProductCategory as $eachcat) {
                                    $total_amount_in_usd = '';
                                    if (isset($arr_credithold[(int) $distributorId][$year][$shipment_month][$eachcat->report_name]['grand_total'])) {
                                        $total_amount_in_usd = (float) $arr_credithold[(int) $distributorId][$year][$shipment_month][$eachcat->report_name]['grand_total'];
                                        $cat_total += (float) $total_amount_in_usd;
                                        $sum_cat[$eachcat['report_name']] = (float) ($sum_cat[$eachcat->report_name] ?? 0) + $total_amount_in_usd;
                                    }
                                    $data[] = $total_amount_in_usd;

                                }
                                $data[] = $cat_total;
                                setData($activeWorksheet, $dataRow, $data);
                                $dataRow++;
                            }
                            $m++;
                        }
                        $data = ['', $year . '- Total ', ''];
                        foreach ($modelMasProductCategory as $eachcat1) {
                            $tot_year_total = $arr_credithold_dis_year[$distributorId][$year][$eachcat1->report_name]['grand_total'] ?? 0;
                            $data[] = $tot_year_total;
                        }
                        setData($activeWorksheet, $dataRow, $data);
                        $activeWorksheet->getStyle('A' . $dataRow . ':' . $var . $dataRow)->getFont()->setBold(true);
                        setFillColor($activeWorksheet, "B{$dataRow}:{$var}{$dataRow}", 'FFFF00');
                        $dataRow++;
                        $y++;
                    }
                    $data = [$arr_dis[$distributorId] . '- Total ', '', ''];
                    foreach ($modelMasProductCategory as $eachcat) {
                        $total_amount_in_usdt = $arr_credithold_dis[$distributorId][$eachcat->report_name]['grand_total'] ?? 0;
                        $data[] = "=IFERROR(" . ($total_amount_in_usdt) . ",0)";
                        $dis_total += $total_amount_in_usdt;
                    }
                    $data[] = $dis_total;
                    setData($activeWorksheet, $dataRow, $data);
                    $activeWorksheet->getStyle('A' . $dataRow . ':' . $var . $dataRow)->getFont()->setBold(true);
                    setFillColor($activeWorksheet, "A{$dataRow}:{$var}{$dataRow}", '83CCEB');
                    $dataRow++;
                    $d++;
                }
                //set colur first 2 rows
                setFillColor($activeWorksheet, 'A1:' . $var . '2', 'FFFF00'); // yellow
                $range_for_number_comma = "D3:{$var}{$dataRow}";
                $activeWorksheet->getStyle($range_for_number_comma)
                    ->getNumberFormat()
                    ->setFormatCode('_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)');
                $data = ['Grand Total', '', ''];
                $grand_tot = 0;
                foreach ($modelMasProductCategory as $eachcat2) {
                    $amt = $sum_cat[$eachcat2->report_name] ?? 0;
                    $data[] = "=IFERROR($amt,0)";
                    $grand_tot += $amt;
                }
                $data[] = $grand_tot;
                setData($activeWorksheet, $dataRow, $data);
                $activeWorksheet->getStyle('A' . $dataRow . ':' . $var . $dataRow)->getFont()->setBold(true);
                setFillColor($activeWorksheet, "A{$dataRow}:{$var}{$dataRow}", 'FFFF00');
                $dataRow++;
                $highestRow = $activeWorksheet->getHighestRow();
                // Set thiN border line from A1 to LAST
                $allColumnsRange = "A2:{$var}{$highestRow}";
                $activeWorksheet->getStyle($allColumnsRange)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

            } else {
                // No records found, so add a message to row 3
                $activeWorksheet->mergeCells("A3:V3");
                $activeWorksheet->setCellValue('A3', 'Empty Record');
            }

            // Set headers for file download
            $writer = new Xlsx($spreadsheet);
            $fileName = 'CreditHold_Distributer_' . $arr_month[$modelCreditHold['ch_month'] - 1] . 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 actionDownloadCreditHoldRegion()
    {
        $credit_hold_id = $_REQUEST['credit_hold_id'];
        $status = $_REQUEST['status'];
        $_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 = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
            $modelCreditHold = new CreditHold();
            $modelCreditHold = $modelCreditHold->findByPK((int) $credit_hold_id);

            $condition = "credit_hold_id='" . $credit_hold_id . "' AND D.consider_for_ch_report='Y' AND ((CH.shipment_month<= " . $modelCreditHold['ch_month'] . "  AND CH.shipment_year=" . $modelCreditHold['ch_year'] . ") OR (CH.shipment_year<" . $modelCreditHold['ch_year'] . "))";
            if ($status != '') {
                $condition .= " AND CH.status IN (" . $status . ")";
            }

            $sql = "SELECT 
                    CH.area_id AS area_id,
                    CH.product_category_id,
                    CH.shipment_month,
                    CH.shipment_year,
                    A.area_name ,
                    PC.product_category,
                    PC.report_name,
                    PC.report_sort_order,
                   SUM(amount_in_usd) AS grand_total
                FROM 
                    ymh_credit_hold_details CH
                LEFT JOIN ymh_mas_product_category PC ON PC.id=CH.product_category_id
                LEFT JOIN ymh_mas_area A ON A.id=CH.area_id
                LEFT JOIN ymh_distributers D ON D.id=CH.distributor_id
                WHERE " . $condition . "
                GROUP BY 
                    CH.area_id, 
                    CH.shipment_year, 
                    CH.shipment_month,PC.report_name
                ORDER BY A.sort_order ASC,CH.shipment_year ASC,CH.shipment_month ASC,PC.report_sort_order ASC";
            $arr_CH = Yii::app()->db->createCommand($sql)->queryAll();
            $arr_credithold = [];
            $arr_reg = [];
            $arr_credithold_area = [];
            $arr_credithold_reg_year = [];
            foreach ($arr_CH as $each) {
                $arr_credithold[$each['area_id']][$each['shipment_year']][$each['shipment_month']][$each['report_name']]['grand_total'] = $each['grand_total'];
                $arr_reg[$each['area_id']] = $each['area_name'];
                //for regionwise sum
                $arr_credithold_area[$each['area_id']][$each['report_name']]['grand_total'] = (isset($arr_credithold_area[$each['area_id']][$each['report_name']]['grand_total']) ? (float) $arr_credithold_area[$each['area_id']][$each['report_name']]['grand_total'] : 0) + $each['grand_total'];
                //for region and year wise report
                $arr_credithold_reg_year[$each['area_id']][$each['shipment_year']][$each['report_name']]['grand_total'] = (isset($arr_credithold_reg_year[$each['area_id']][$each['shipment_year']][$each['report_name']]['grand_total']) ? (float) $arr_credithold_reg_year[$each['area_id']][$each['shipment_year']][$each['report_name']]['grand_total'] : 0) + $each['grand_total'];
            }

            $modelCreditHoldDetails = new CreditHoldDetails();
            $modelMasProductCategory = new MasProductCategory();
            $modelMasProductCategory = $modelMasProductCategory->findAll(array('group' => 'report_name', 'order' => 'report_sort_order ASC'));
            // Create new spreadsheet
            $spreadsheet = new Spreadsheet();
            $activeWorksheet = $spreadsheet->getActiveSheet();

            // Set the title in merged cells
            $activeWorksheet->mergeCells('A1:Z1');
            $activeWorksheet->setCellValue('A1', "CREDIT HOLD REPORT " . $arr_month[$modelCreditHold['ch_month'] - 1] . "'" . $modelCreditHold['ch_year']);//4+distributer_Count
            $activeWorksheet->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            // Set font size to 16 for cell A1
            $activeWorksheet->getStyle('A1')->getFont()->setSize(16);
            // Freeze the pane at cell C3
            $activeWorksheet->freezePane('C3');
            // Add filter for row 2
            $activeWorksheet->setAutoFilter('A2:I2');
            // Set text wrap and alignment for row number 2
            $cellsToWrapAndCenter = ["A1", "A2", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2", "O2", "P2", "Q2", "R2", "S2", "T2", "U2", "V2", "W2"];
            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 B C individually
            $activeWorksheet->getColumnDimension('A')->setWidth(21.11);
            $activeWorksheet->getColumnDimension('B')->setWidth(12.78);
            $activeWorksheet->getColumnDimension('C')->setWidth(14.67);
            // Set the height of row 1 
            $activeWorksheet->getRowDimension(1)->setRowHeight(46.7);
            // Define and add column headers
            $var = 'C';
            $columnHeaders = [
                'Region',
                'Year Final',
                'Month Final'
            ];
            if (!empty($modelMasProductCategory)) {
                foreach ($modelMasProductCategory as $eachcat) {
                    $columnHeaders[] = $eachcat['report_name'];
                    $var++;
                    $activeWorksheet->getColumnDimension($var)->setWidth(17.33);
                }
            }
            $columnHeaders[] = 'GrandTotal';
            $var++;
            $activeWorksheet->getColumnDimension($var)->setWidth(18.67);
            $activeWorksheet->getStyle("A1:{$var}1")->getFont()->setBold(true);

            $headerRow = 2;
            setData($activeWorksheet, $headerRow, $columnHeaders);
            $activeWorksheet->getStyle('A' . $headerRow . ':' . $var . $headerRow)->getFont()->setBold(true);
            // Populate data rows
            $dataRow = $headerRow + 1;
            $dp = '';
            $d = 0;
            $sum_cat = [];
            if (!empty($arr_credithold)) {
                foreach ($arr_credithold as $regionId => $arr_years) {
                    $y = 0;
                    $reg_total = 0;
                    $yp = '';
                    $mp = '';
                    foreach ($arr_years as $year => $arr_ch_months) {
                        $m = 0;
                        foreach ($arr_ch_months as $shipment_month => $arr_categories) {
                            if (!empty($modelMasProductCategory)) {
                                $data = [];
                                if ($dp != $d) {
                                    $data[] = $arr_reg[$regionId];
                                    $dp = $d;
                                    $activeWorksheet->getStyle("A{$dataRow}:A{$dataRow}")->getFont()->setBold(true);
                                } else {
                                    $data[] = '';
                                }
                                if ($yp != $y) {
                                    $data[] = $year;
                                    $yp = $y;

                                } else {
                                    $data[] = '';
                                }
                                if ($mp != $m) {
                                    $data[] = $arr_month[$shipment_month - 1];
                                    $mp = $m;
                                } else {
                                    $data[] = '';
                                }

                                $cat_total = 0;
                                foreach ($modelMasProductCategory as $eachcat) {
                                    $total_amount_in_usd = '';
                                    if (isset($arr_credithold[(int) $regionId][$year][$shipment_month][$eachcat->report_name]['grand_total'])) {
                                        $total_amount_in_usd = $arr_credithold[(int) $regionId][$year][$shipment_month][$eachcat->report_name]['grand_total'];
                                        $cat_total += $total_amount_in_usd;
                                        $sum_cat[$eachcat->report_name] = (float) ($sum_cat[$eachcat->report_name] ?? 0) + $total_amount_in_usd;
                                    }
                                    $data[] = $total_amount_in_usd;

                                }
                                $data[] = $cat_total;
                                setData($activeWorksheet, $dataRow, $data);
                                $dataRow++;
                            }
                            $m++;
                        }
                        $data = ['', $year . '- Total ', ''];
                        $year_total = 0;
                        foreach ($modelMasProductCategory as $eachcat1) {
                            $tot_year_total = $arr_credithold_reg_year[$regionId][$year][$eachcat1->report_name]['grand_total'] ?? 0;
                            $data[] = $tot_year_total;
                            $year_total += $tot_year_total;
                        }
                        $data[] = $year_total;
                        setData($activeWorksheet, $dataRow, $data);
                        $activeWorksheet->getStyle('A' . $dataRow . ':' . $var . $dataRow)->getFont()->setBold(true);
                        setFillColor($activeWorksheet, "B{$dataRow}:{$var}{$dataRow}", 'FFFF00');
                        $dataRow++;
                        $y++;
                    }
                    $data = [$arr_reg[$regionId] . '- Total ', '', ''];
                    foreach ($modelMasProductCategory as $eachcat) {
                        $total_amount_in_usdt = $arr_credithold_area[$regionId][$eachcat->report_name]['grand_total'] ?? 0;
                        $data[] = "=IFERROR(" . ($total_amount_in_usdt) . ",0)";
                        $reg_total += $total_amount_in_usdt;
                    }
                    $data[] = $reg_total;
                    setData($activeWorksheet, $dataRow, $data);
                    $activeWorksheet->getStyle('A' . $dataRow . ':' . $var . $dataRow)->getFont()->setBold(true);
                    setFillColor($activeWorksheet, "A{$dataRow}:{$var}{$dataRow}", '83CCEB');
                    $dataRow++;
                    $d++;
                }
                setFillColor($activeWorksheet, 'A1:' . $var . '2', 'FFFF00'); // yellow

                $range_for_number_comma = "D3:{$var}{$dataRow}";
                $activeWorksheet->getStyle($range_for_number_comma)
                    ->getNumberFormat()
                    ->setFormatCode('_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)');
                $data = ['Grand Total', '', ''];
                $grand_tot = 0;
                foreach ($modelMasProductCategory as $eachcat2) {
                    $amt = $sum_cat[$eachcat2->report_name] ?? 0;
                    $data[] = "=IFERROR($amt,0)";
                    $grand_tot += $amt;
                }
                $data[] = $grand_tot;
                setData($activeWorksheet, $dataRow, $data);
                $activeWorksheet->getStyle('A' . $dataRow . ':' . $var . $dataRow)->getFont()->setBold(true);
                setFillColor($activeWorksheet, "A{$dataRow}:{$var}{$dataRow}", 'FFFF00');
                $dataRow++;
                $highestRow = $activeWorksheet->getHighestRow();
                // Set thiN border line from A1 to LAST
                $allColumnsRange = "A2:{$var}{$highestRow}";
                $activeWorksheet->getStyle($allColumnsRange)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

            } else {
                // No records found, so add a message to row 3
                $activeWorksheet->mergeCells("A3:V3");
                $activeWorksheet->setCellValue('A3', 'Empty Record');
            }

            // Set headers for file download
            $writer = new Xlsx($spreadsheet);
            $fileName = 'CreditHold_Region_' . $arr_month[$modelCreditHold['ch_month'] - 1] . '.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 actionDownloadStockAgeingDistributor()
    {
        $credit_hold_id = $_REQUEST['credit_hold_id'];
        $status = $_REQUEST['status'];
        $_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"];
            $modelCreditHold = new CreditHold();
            $modelCreditHold = $modelCreditHold->findByPK((int) $credit_hold_id);
            $condition = "credit_hold_id='" . $credit_hold_id . "' AND D.consider_for_ch_report='Y' AND ((CH.shipment_month<= " . $modelCreditHold['ch_month'] . "  AND CH.shipment_year=" . $modelCreditHold['ch_year'] . ") OR (CH.shipment_year<" . $modelCreditHold['ch_year'] . "))";
            if ($status != '') {
                $condition .= " AND CH.status IN (" . $status . ")";
            }
            $sql = "SELECT 
                    CH.distributor_id,
                    CH.product_category_id,
                    D.distributer_name,
                    D.alias_name,
                    PC.report_name AS product_category,
                    PC.report_sort_order,
                    SUM(CASE WHEN stock_ageing = 0 THEN amount_in_usd ELSE 0 END) AS sum_status_0,
                    SUM(CASE WHEN stock_ageing = 1 THEN amount_in_usd ELSE 0 END) AS sum_status_1,
                    SUM(CASE WHEN stock_ageing = 2 THEN amount_in_usd ELSE 0 END) AS sum_status_2,
                    SUM(CASE WHEN stock_ageing = 3 THEN amount_in_usd ELSE 0 END) AS sum_status_3,
                    SUM(CASE WHEN stock_ageing = 4 THEN amount_in_usd ELSE 0 END) AS sum_status_4,
                    SUM(CASE WHEN stock_ageing > 4 THEN amount_in_usd ELSE 0 END) AS sum_status_above_4,
                    SUM(amount_in_usd) AS grand_total
                    
                FROM 
                    ymh_credit_hold_details CH
                LEFT JOIN ymh_mas_product_category PC ON PC.id=CH.product_category_id
                LEFT JOIN ymh_distributers D ON D.id=CH.distributor_id
                WHERE " . $condition . "
                GROUP BY 
                    CH.distributor_id, 
                    PC.report_sort_order
                ORDER BY D.distributer_name,PC.report_sort_order ASC";
            $modelCreditHoldDetails = Yii::app()->db->createCommand($sql)->queryAll();

            // Create new spreadsheet
            $spreadsheet = new Spreadsheet();
            $activeWorksheet = $spreadsheet->getActiveSheet();

            // Set the title in merged cells
            $activeWorksheet->mergeCells('A1:I1');
            $activeWorksheet->setCellValue('A1', "CREDIT HOLD REPORT " . $arr_month[$modelCreditHold['ch_month'] - 1] . "'" . $modelCreditHold['ch_year']);
            $activeWorksheet->getStyle('A1:I1')->getFont()->setBold(true);
            $activeWorksheet->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            // Set font size to 16 for cell A1
            $activeWorksheet->getStyle('A1')->getFont()->setSize(16);
            // Freeze the pane at cell C3
            $activeWorksheet->freezePane('C3');
            // Add filter for row 2
            $activeWorksheet->setAutoFilter('A2:I2');
            // Set text wrap and alignment for row number 2
            $cellsToWrapAndCenter = ["A1", "A2", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2"];
            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 <= 'I'; $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 = [
                'Distributor',
                'Category',
                '0',
                '1',
                '2',
                '3',
                '4',
                '4+',
                'Grand Total'
            ];

            $headerRow = 2;
            setData($activeWorksheet, $headerRow, $columnHeaders);
            $activeWorksheet->getStyle('A2:I2')->getFont()->setBold(true);



            // Populate data rows
            $dataRow = $headerRow + 1;
            $dis_id = 0;
            $start = 0;
            $end = 0;
            $tot_0 = 0;
            $tot_1 = 0;
            $tot_2 = 0;
            $tot_3 = 0;
            $tot_4 = 0;
            $tot_5 = 0;
            $tot_g = 0;
            if (!empty($modelCreditHoldDetails)) {
                foreach ($modelCreditHoldDetails as $eachdetails) {
                    if (($dis_id != $eachdetails['distributor_id'])) {
                        if ($dataRow !== 3) {
                            $end = $dataRow - 1;
                            $dis_status_0 = "=IFERROR(SUM(C{$start}:C{$end}),0)";
                            $dis_status_1 = "=IFERROR(SUM(D{$start}:D{$end}),0)";
                            $dis_status_2 = "=IFERROR(SUM(E{$start}:E{$end}),0)";
                            $dis_status_3 = "=IFERROR(SUM(F{$start}:F{$end}),0)";
                            $dis_status_4 = "=IFERROR(SUM(G{$start}:G{$end}),0)";
                            $dis_status_5 = "=IFERROR(SUM(H{$start}:H{$end}),0)";
                            $dis_status_grnd = "=IFERROR(SUM(I{$start}:I{$end}),0)";
                            $columndata = [
                                $olddist_name . '-Total',
                                '',
                                $dis_status_0,
                                $dis_status_1,
                                $dis_status_2,
                                $dis_status_3,
                                $dis_status_4,
                                $dis_status_5,
                                $dis_status_grnd
                            ];
                            setData($activeWorksheet, $dataRow, $columndata);
                            $activeWorksheet->getStyle("A{$dataRow}:I{$dataRow}")->getFont()->setBold(true);
                            setFillColor($activeWorksheet, "A{$dataRow}:I{$dataRow}", '83CCEB');

                            //#83CCEB
                            $dataRow++;
                        }
                        $dist_name = $eachdetails['distributer_name'];
                        $olddist_name = $eachdetails['distributer_name'];
                        $dis_id = $eachdetails['distributor_id'];
                        $start = $dataRow;
                    } else {
                        $olddist_name = $eachdetails['distributer_name'];
                        $dist_name = '';
                    }
                    setFillColor($activeWorksheet, "B{$dataRow}:B{$dataRow}", 'FFFF00');
                    $tot_0 += (float) $eachdetails['sum_status_0'];
                    $tot_1 += (float) $eachdetails['sum_status_1'];
                    $tot_2 += (float) $eachdetails['sum_status_2'];
                    $tot_3 += (float) $eachdetails['sum_status_3'];
                    $tot_4 += (float) $eachdetails['sum_status_4'];
                    $tot_5 += (float) $eachdetails['sum_status_above_4'];
                    $tot_g += (float) $eachdetails['grand_total'];
                    $sum_status_0 = ($eachdetails['sum_status_0'] == 0) ? '' : $eachdetails['sum_status_0'];
                    $sum_status_1 = ($eachdetails['sum_status_1'] == 0) ? '' : $eachdetails['sum_status_1'];
                    $sum_status_2 = ($eachdetails['sum_status_2'] == 0) ? '' : $eachdetails['sum_status_2'];
                    $sum_status_3 = ($eachdetails['sum_status_3'] == 0) ? '' : $eachdetails['sum_status_3'];
                    $sum_status_4 = ($eachdetails['sum_status_4'] == 0) ? '' : $eachdetails['sum_status_4'];
                    $sum_status_above_4 = ($eachdetails['sum_status_above_4'] == 0) ? '' : $eachdetails['sum_status_above_4'];
                    $columndata = [
                        $dist_name,
                        $eachdetails['product_category'],
                        $sum_status_0,
                        $sum_status_1,
                        $sum_status_2,
                        $sum_status_3,
                        $sum_status_4,
                        $sum_status_above_4,
                        $eachdetails['grand_total']
                    ];
                    setData($activeWorksheet, $dataRow, $columndata);
                    $dataRow++;

                }
                if ($dataRow !== 3) {
                    $end = $dataRow - 1;
                    $dis_status_0 = "=IFERROR(SUM(C{$start}:C{$end}),0)";
                    $dis_status_1 = "=IFERROR(SUM(D{$start}:D{$end}),0)";
                    $dis_status_2 = "=IFERROR(SUM(E{$start}:E{$end}),0)";
                    $dis_status_3 = "=IFERROR(SUM(F{$start}:F{$end}),0)";
                    $dis_status_4 = "=IFERROR(SUM(G{$start}:G{$end}),0)";
                    $dis_status_5 = "=IFERROR(SUM(H{$start}:H{$end}),0)";
                    $dis_status_grnd = "=IFERROR(SUM(I{$start}:I{$end}),0)";
                    $columndata = [
                        $olddist_name . '-Total',
                        '',
                        $dis_status_0,
                        $dis_status_1,
                        $dis_status_2,
                        $dis_status_3,
                        $dis_status_4,
                        $dis_status_5,
                        $dis_status_grnd
                    ];
                    setData($activeWorksheet, $dataRow, $columndata);
                    $activeWorksheet->getStyle("A{$dataRow}:I{$dataRow}")->getFont()->setBold(true);
                    setFillColor($activeWorksheet, "A{$dataRow}:I{$dataRow}", '83CCEB');

                    //#83CCEB
                    $dataRow++;
                }
                $columndata = [
                    'Grand Total',
                    '',
                    $tot_0,
                    $tot_1,
                    $tot_2,
                    $tot_3,
                    $tot_4,
                    $tot_5,
                    $tot_g
                ];
                setData($activeWorksheet, $dataRow, $columndata);
                setFillColor($activeWorksheet, "A{$dataRow}:I{$dataRow}", 'FFFF00');//yellow
                $activeWorksheet->getStyle("A{$dataRow}:I{$dataRow}")->getFont()->setBold(true);
                //For making bold B column Category
                $activeWorksheet->getStyle("B3:B{$dataRow}")->getFont()->setBold(true);
                //For making bold I column grandTotal
                $activeWorksheet->getStyle("I3:I{$dataRow}")->getFont()->setBold(true);

                setFillColor($activeWorksheet, 'A1:I2', 'FFFF00'); // yellow
                $range_for_number_comma = "C3:I{$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 = "A2:I{$highestRow}";
                $activeWorksheet->getStyle($allColumnsRange)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
                $activeWorksheet->getRowDimension(1)->setRowHeight(40.5);
                $activeWorksheet->getRowDimension(2)->setRowHeight(40.5);
                $width = 14; // Desired width in pixels
                for ($row = 3; $row <= $highestRow; $row++) {
                    $activeWorksheet->getRowDimension($row)->setRowHeight($width);
                }

            } else {
                // No records found, so add a message to row 3
                $activeWorksheet->mergeCells("A3:I3");
                $activeWorksheet->setCellValue('A3', 'Empty Record');
            }

            // Set headers for file download
            $writer = new Xlsx($spreadsheet);
            $fileName = 'Stock Ageing-Distributerwise_' . $arr_month[$modelCreditHold['ch_month'] - 1] . '.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 actionDownloadStockAgeingRegion()
    {
        $credit_hold_id = $_REQUEST['credit_hold_id'];
        $status = $_REQUEST['status'];
        $_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"];
            $modelCreditHold = new CreditHold();
            $modelCreditHold = $modelCreditHold->findByPK((int) $credit_hold_id);
            $condition = "credit_hold_id='" . $credit_hold_id . "' AND D.consider_for_ch_report='Y' AND ((CH.shipment_month<= " . $modelCreditHold['ch_month'] . "  AND CH.shipment_year=" . $modelCreditHold['ch_year'] . ") OR (CH.shipment_year<" . $modelCreditHold['ch_year'] . "))";
            if ($status != '') {
                $condition .= " AND CH.status IN (" . $status . ")";
            }
            /*CH.region_id, R.region_name,
             */
            $sql = "SELECT 
                    CH.area_id,
                    I.product_category_id,
                    A.area_name,
                    PC.report_name AS product_category,
                    PC.report_sort_order,
                    SUM(CASE WHEN stock_ageing = 0 THEN amount_in_usd ELSE 0 END) AS sum_status_0,
                    SUM(CASE WHEN stock_ageing = 1 THEN amount_in_usd ELSE 0 END) AS sum_status_1,
                    SUM(CASE WHEN stock_ageing = 2 THEN amount_in_usd ELSE 0 END) AS sum_status_2,
                    SUM(CASE WHEN stock_ageing = 3 THEN amount_in_usd ELSE 0 END) AS sum_status_3,
                    SUM(CASE WHEN stock_ageing = 4 THEN amount_in_usd ELSE 0 END) AS sum_status_4,
                    SUM(CASE WHEN stock_ageing > 4 THEN amount_in_usd ELSE 0 END) AS sum_status_above_4,
                    SUM(amount_in_usd) AS grand_total

                FROM 
                    ymh_credit_hold_details CH
                LEFT JOIN ymh_inventory I ON I.id=CH.product_id
                LEFT JOIN ymh_mas_product_category PC ON PC.id=I.product_category_id
                LEFT JOIN ymh_mas_area A ON A.id=CH.area_id
                LEFT JOIN ymh_distributers D ON D.id=CH.distributor_id
                WHERE " . $condition . "
                GROUP BY 
                    CH.area_id, 
                    PC.report_sort_order
                ORDER BY A.sort_order  ASC,PC.report_sort_order  ASC";
            $modelCreditHoldDetails = Yii::app()->db->createCommand($sql)->queryAll();

            // Create new spreadsheet
            $spreadsheet = new Spreadsheet();
            $activeWorksheet = $spreadsheet->getActiveSheet();

            // Set the title in merged cells
            $activeWorksheet->mergeCells('A1:I1');
            $activeWorksheet->setCellValue('A1', "CREDIT HOLD REPORT " . $arr_month[$modelCreditHold['ch_month'] - 1] . "'" . $modelCreditHold['ch_year']);
            $activeWorksheet->getStyle('A1:I1')->getFont()->setBold(true);
            $activeWorksheet->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            // Set font size to 16 for cell A1
            $activeWorksheet->getStyle('A1')->getFont()->setSize(16);
            // Freeze the pane at cell C3
            $activeWorksheet->freezePane('C3');
            // Add filter for row 2
            $activeWorksheet->setAutoFilter('A2:I2');
            // Set text wrap and alignment for row number 2
            $cellsToWrapAndCenter = ["A1", "A2", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2"];
            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 <= 'I'; $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 = [
                'REGION',
                'CATEGORY',
                '0',
                '1',
                '2',
                '3',
                '4',
                '4+',
                'Grand Total'
            ];

            $headerRow = 2;
            setData($activeWorksheet, $headerRow, $columnHeaders);
            $activeWorksheet->getStyle('A2:I2')->getFont()->setBold(true);



            // Populate data rows
            $dataRow = $headerRow + 1;
            $reg_id = 0;
            $start = 0;
            $end = 0;
            $tot_0 = 0;
            $tot_1 = 0;
            $tot_2 = 0;
            $tot_3 = 0;
            $tot_4 = 0;
            $tot_5 = 0;
            $tot_g = 0;
            if (!empty($modelCreditHoldDetails)) {
                foreach ($modelCreditHoldDetails as $eachdetails) {
                    if (($reg_id != $eachdetails['area_id'])) {
                        if ($dataRow !== 3) {
                            $end = $dataRow - 1;
                            $reg_status_0 = "=IFERROR(SUM(C{$start}:C{$end}),0)";
                            $reg_status_1 = "=IFERROR(SUM(D{$start}:D{$end}),0)";
                            $reg_status_2 = "=IFERROR(SUM(E{$start}:E{$end}),0)";
                            $reg_status_3 = "=IFERROR(SUM(F{$start}:F{$end}),0)";
                            $reg_status_4 = "=IFERROR(SUM(G{$start}:G{$end}),0)";
                            $reg_status_5 = "=IFERROR(SUM(H{$start}:H{$end}),0)";
                            $reg_status_grnd = "=IFERROR(SUM(I{$start}:I{$end}),0)";
                            $columndata = [
                                $oldreg_name . '-Total',
                                '',
                                $reg_status_0,
                                $reg_status_1,
                                $reg_status_2,
                                $reg_status_3,
                                $reg_status_4,
                                $reg_status_5,
                                $reg_status_grnd
                            ];
                            setData($activeWorksheet, $dataRow, $columndata);
                            $activeWorksheet->getStyle("A{$dataRow}:I{$dataRow}")->getFont()->setBold(true);
                            setFillColor($activeWorksheet, "A{$dataRow}:I{$dataRow}", '83CCEB');

                            //#83CCEB
                            $dataRow++;
                        }
                        $reg_name = $eachdetails['area_name'];
                        $oldreg_name = $eachdetails['area_name'];
                        $reg_id = $eachdetails['area_id'];
                        $start = $dataRow;
                    } else {
                        $oldreg_name = $eachdetails['area_name'];
                        $reg_name = '';
                    }
                    setFillColor($activeWorksheet, "B{$dataRow}:B{$dataRow}", 'FFFF00');
                    $tot_0 += (float) $eachdetails['sum_status_0'];
                    $tot_1 += (float) $eachdetails['sum_status_1'];
                    $tot_2 += (float) $eachdetails['sum_status_2'];
                    $tot_3 += (float) $eachdetails['sum_status_3'];
                    $tot_4 += (float) $eachdetails['sum_status_4'];
                    $tot_5 += (float) $eachdetails['sum_status_above_4'];
                    $tot_g += (float) $eachdetails['grand_total'];
                    $sum_status_0 = ($eachdetails['sum_status_0'] == 0) ? '' : $eachdetails['sum_status_0'];
                    $sum_status_1 = ($eachdetails['sum_status_1'] == 0) ? '' : $eachdetails['sum_status_1'];
                    $sum_status_2 = ($eachdetails['sum_status_2'] == 0) ? '' : $eachdetails['sum_status_2'];
                    $sum_status_3 = ($eachdetails['sum_status_3'] == 0) ? '' : $eachdetails['sum_status_3'];
                    $sum_status_4 = ($eachdetails['sum_status_4'] == 0) ? '' : $eachdetails['sum_status_4'];
                    $sum_status_above_4 = ($eachdetails['sum_status_above_4'] == 0) ? '' : $eachdetails['sum_status_above_4'];
                    $columndata = [
                        $reg_name,
                        $eachdetails['product_category'],
                        $sum_status_0,
                        $sum_status_1,
                        $sum_status_2,
                        $sum_status_3,
                        $sum_status_4,
                        $sum_status_above_4,
                        $eachdetails['grand_total']
                    ];
                    setData($activeWorksheet, $dataRow, $columndata);
                    $dataRow++;

                }
                if ($dataRow !== 3) {
                    $end = $dataRow - 1;
                    $reg_status_0 = "=IFERROR(SUM(C{$start}:C{$end}),0)";
                    $reg_status_1 = "=IFERROR(SUM(D{$start}:D{$end}),0)";
                    $reg_status_2 = "=IFERROR(SUM(E{$start}:E{$end}),0)";
                    $reg_status_3 = "=IFERROR(SUM(F{$start}:F{$end}),0)";
                    $reg_status_4 = "=IFERROR(SUM(G{$start}:G{$end}),0)";
                    $reg_status_5 = "=IFERROR(SUM(H{$start}:H{$end}),0)";
                    $reg_status_grnd = "=IFERROR(SUM(I{$start}:I{$end}),0)";
                    $columndata = [
                        $oldreg_name . '-Total',
                        '',
                        $reg_status_0,
                        $reg_status_1,
                        $reg_status_2,
                        $reg_status_3,
                        $reg_status_4,
                        $reg_status_5,
                        $reg_status_grnd
                    ];
                    setData($activeWorksheet, $dataRow, $columndata);
                    $activeWorksheet->getStyle("A{$dataRow}:I{$dataRow}")->getFont()->setBold(true);
                    setFillColor($activeWorksheet, "A{$dataRow}:I{$dataRow}", '83CCEB');

                    //#83CCEB
                    $dataRow++;
                }
                $columndata = [
                    'Grand Total',
                    '',
                    $tot_0,
                    $tot_1,
                    $tot_2,
                    $tot_3,
                    $tot_4,
                    $tot_5,
                    $tot_g
                ];
                setData($activeWorksheet, $dataRow, $columndata);
                setFillColor($activeWorksheet, "A{$dataRow}:I{$dataRow}", 'FFFF00');//yellow
                $activeWorksheet->getStyle("A{$dataRow}:I{$dataRow}")->getFont()->setBold(true);
                //For making bold I column grandTotal
                $activeWorksheet->getStyle("I3:I{$dataRow}")->getFont()->setBold(true);
                $activeWorksheet->getStyle("A3:A{$dataRow}")->getFont()->setBold(true);
                //For making bold B column Category
                $activeWorksheet->getStyle("B3:B{$dataRow}")->getFont()->setBold(true);

                setFillColor($activeWorksheet, 'A1:I2', 'FFFF00'); // yellow
                $range_for_number_comma = "C3:I{$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 = "A2:I{$highestRow}";
                $activeWorksheet->getStyle($allColumnsRange)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
                $activeWorksheet->getRowDimension(1)->setRowHeight(40.5);
                $activeWorksheet->getRowDimension(2)->setRowHeight(40.5);
                $width = 14; // Desired width in pixels
                for ($row = 3; $row <= $highestRow; $row++) {
                    $activeWorksheet->getRowDimension($row)->setRowHeight($width);
                }

            } else {
                // No records found, so add a message to row 3
                $activeWorksheet->mergeCells("A3:I3");
                $activeWorksheet->setCellValue('A3', 'Empty Record');
            }

            // Set headers for file download
            $writer = new Xlsx($spreadsheet);
            $fileName = 'Stock Ageing-Regionwise_' . $arr_month[$modelCreditHold['ch_month'] - 1] . '.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 actionDeleteCreditHold()
    {
        $_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
                CreditHoldDetails::model()->deleteAll('credit_hold_id=:refer_id', array(':refer_id' => $id));

                // Find the main CreditHold record
                $model = CreditHold::model()->findByPk($id);

                if ($model !== null) { // Ensure record exists before accessing properties
                    $locfile = "upload/credithold/" . $model->ch_file_name;

                    // Delete the main CreditHold record
                    if ($model->deleteByPk($id)) {
                        // Delete the file if it exists
                        if (!empty($model->ch_file_name) && 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.');
        }
    }
    /**
     * Bulk insert function
     */

    private function bulkInsert($tableName, $data)
    {
        if (empty($data)) {
            return;
        }

        $columns = array_keys($data[0]); // Get column names from first record
        $values = [];
        $params = [];

        foreach ($data as $index => $row) {
            $placeholders = [];
            foreach ($row as $key => $value) {
                $paramName = ":{$key}_{$index}";
                $placeholders[] = $paramName;
                $params[$paramName] = $value;
            }
            $values[] = "(" . implode(",", $placeholders) . ")";
        }

        $sql = "INSERT INTO {$tableName} (" . implode(",", $columns) . ") VALUES " . implode(",", $values);

        Yii::app()->db->createCommand($sql)->execute($params);
    }


    public function actionGetListReport()
    {

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

            $model = new CreditHold();

            $draw = $_POST['draw'];

            $offset = $_POST['start'];

            $limit = $_POST['length'];
            $type = $_POST['type'];

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

                0 => 't.id',
                1 => 't.ch_month',
                2 => 't.ch_year',
            );

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

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

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

            $condition = '1=1';

            /*if ($month != '') {

                $condition .= ' AND ch_month=' . $month;
            }

            if ($year > 0) {

                $condition .= ' AND ch_year=' . $year;
            }*/

            $arr_months = Order::getmonths();

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

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

                $condition .= " and (ch_month LIKE '%" . $searchString . "%' OR ch_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 (Yii::app()->user->authorised_user == 1 || ((int) Yii::app()->user->havePermissionOn["orders_view"])) {

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

                if ($row['is_completed'] == "Y") {
//                    type added by nisha 17-10-2025
                    if($type==1){
                        $div .= '<button type="button" class="btn btn-xs btn-primary" title="Master File" onclick="loadPopupModal(\'credithold/FilterRegionMaster&credit_hold_id=' . $row['id'] . '\',\'Master Report\',\'lg\')">
                        <i class="fa fa-print"></i>
                        </button>';
                    }
                    if($type==2){
                        $div .= '<button type="button" class="btn btn-xs btn-primary" title="CHReport-Distributor" onclick="loadPopupModal(\'credithold/FilterPage&ch_rep_type=1&credit_hold_id=' . $row['id'] . '\',\'CH-Distributerwise\',\'lg\')" >
                        <i class="fa fa-file-excel-o"></i>
                        </button>';//onclick="window.open($(\'#liveSite\').val() + \'index.php?r=Credithold/DownloadCreditHoldDistributer&credit_hold_id=' . $row['id'] . '\', \'_blank\');"
                    }
                    if($type==3){
                        $div .= '<button type="button" class="btn btn-xs btn-primary" title="CHReport-Region" onclick="loadPopupModal(\'credithold/FilterPage&ch_rep_type=2&credit_hold_id=' . $row['id'] . '\',\'CH-Regionwise\',\'lg\')" >
                        <i class="fa fa-file-excel-o"></i>
                        </button>';//onclick="window.open($(\'#liveSite\').val() + \'index.php?r=Credithold/DownloadCreditHoldRegion&credit_hold_id=' . $row['id'] . '\', \'_blank\');"
                    }
                    if($type==4){
                        $div .= '<button type="button" class="btn btn-xs btn-primary" title="Stock_Ageing-Distributor_wise" onclick="loadPopupModal(\'credithold/FilterPage&ch_rep_type=3&credit_hold_id=' . $row['id'] . '\',\'StockAgeing-Distributerwise\',\'lg\')" >
                        <i class="fa fa-file-excel-o"></i>
                        </button>';//onclick="window.open($(\'#liveSite\').val() + \'index.php?r=Credithold/DownloadStockAgeingDistributor&credit_hold_id=' . $row['id'] . '\', \'_blank\');"
                    }
                    if($type==5){
                    $div .= '<button type="button" class="btn btn-xs btn-primary" title="Stock_Ageing-Region_wise" onclick="loadPopupModal(\'credithold/FilterPage&ch_rep_type=4&credit_hold_id=' . $row['id'] . '\',\'StockAgeing-Regionwise\',\'lg\')">
                    <i class="fa fa-file-excel-o"></i>
                    </button>';// onclick="window.open($(\'#liveSite\').val() + \'index.php?r=Credithold/DownloadStockAgeingRegion&credit_hold_id=' . $row['id'] . '\', \'_blank\');"
                    }
                    //will remove in live

                } elseif ($row['is_checked'] == "Y") {
                    if ($row['have_errors'] == "Y") {
                        $div .= CHtml::link(
                            'Download log',
                            ['credithold/DownloadErrorCsv', 'id' => $row['ch_file_name']],
                            ['style' => 'border: 1px solid #686869; padding: 2px 10px; border-radius: 10px; font-size: 10px; background: #a1a1a1; color: #fff;']
                        );
                    }
                    if ($count == 1) {

                        $div .= '<button type="button" class="btn btn-xs btn-primary" title="Upload Next" onclick="SaveInitialUploadNext(' . $row['id'] . ');">
                    <i class="fa fa-arrow-up"></i>
                    </button>';
                    } else {
                        $div .= '<button type="button" class="btn btn-xs btn-primary" title="Upload Next" onclick="SaveUploadNext(' . $row['id'] . ');">
                        <i class="fa fa-arrow-up"></i>
                        </button>';
                    }
                    $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" onclick="deleteCreditHold(' . $row['id'] . ')"><i class="fa fa-trash"></i></button>';

                } else {
                    if ($count == 1) {
                        $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" title="Check Errors" onclick="CheckErrorsCreditHoldInitial(' . $row['id'] . ')"><i class="fa fa-retweet"></i></button>';
                    } else {
                        $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" title="Check Errors" onclick="CheckErrorsCreditHold(' . $row['id'] . ')"><i class="fa fa-retweet"></i></button>';
                    }
                    $div .= '&nbsp<button type="button"  class="btn btn-xs btn-primary" title="Delete File" onclick="deleteCreditHold(' . $row['id'] . ')"><i class="fa fa-trash"></i></button>';
                }
                $rowData[] = array(
                    $m,
                    $arr_months[(int) $row['ch_month']],
                    $row['ch_year'],
//                    commnted by nisha 17-10-2025
//                    $row['last_entry'] . ' out of ' . $row['total_entries'],
//                    $row['is_master'],
                    '<div class="btn-wrap text-center">' . $div . '</div>'
                );

                $m++;
            }

            $data['data'] = $rowData;

            echo json_encode($data);
        } else {

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

}
?>