<?php
include (PATH_CONTROLLERS . "Common.php") ;
require_once(PATH_MODELS . "customer/CustomerClass.php");
require_once(PATH_MODELS . "customer/CustomerContactClass.php");
require_once(PATH_MODELS . "customer/CustomerPolicyClass.php");
require_once(PATH_MODELS . "customer/CustomerBranchClass.php");
require_once(PATH_MODELS . "customer/CustomerSoftwareClass.php");
require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\IOFactory;
class Customer extends GLBase {
	private $type = "" ;
	private $user ;

	function __construct() {
		$this->db = $_SESSION[SE_DB] ;
	}
	function __destruct() {
		unset($this->user) ;
	}
	public function processRequest($params) {
		$this->type = REQ_VIEW ;
		try {
			$this->db->open() ;
			$this->loadSetting() ;
			if (isset($params) && count($params) > 0) {
				if (isset($params['type']))
					$this->type = $params['type'] ;
			}
			if (!$this->checkSessionTimeout($this->type)) {
				$this->db->close();
				return false;
			}

			switch ($this->type) {
				case Command::CustomerView:
					$this->getView($params) ;
					break ;
                case Command::CustomerListView:
                    $this->getCustomerList($params);
                    break;
                case Command::CustomerContactFilter:
                    $this->getCustomerContactList($params);
                    break;
                case REQ_ADD:
                    $this->addRecord($params);
                    break;
                case REQ_UPDATE:
                    $this->updateRecord($params);
                    break;
                case REQ_DELETE:
                    $this->deleteCustomerRecord($params);
                    break;
                case "C_DELETE_CONTACT":
                    $this->deleteContact($params);
                    break;
                case "C_DELETE_BRANCH":
                    $this->deleteBranch($params);
                    break;
                case "C_LATEST_CONTACT_BRANCH":
                    $this->getContactBranchList($params);
                    break;
                case "C_CUSTOMER_DOWNLOAD_POLICY":
                    $this->processDownloadFile($params);
                    break;
                //AUTO SEARCH
                case "CUSTOMER_CODE_SEARCH_SALES_QUOTATION":
                    $this->getCustomerSearchList($params);
                    break;
                //IMPORT EXCEL
				case "C_DOWNLOAD_CUSTOMER_EXCEL_TEMPLATE":
                    $this->getTemplate($params) ;
					break ;
				case "C_CUSTOMER_UPLOAD_EXCEL":
                    $this->processCustomerUploadExcel();
					break ;
				case "C_CUSTOMER_READ_EXCEL":
                    $this->processCustomerReadExcel($params) ;
                    break ;
				default:
					$this->sendJsonResponse(Status::Error,"invalid request.","",$this->type) ;
					break ;
			}
			$this->db->close() ;
			return true ;
		} catch (Exception $e) {
			$this->db->close() ;
			die ($e->getMessage()) ;
		}
	}

	private function getView($params) {
		ob_start() ;
		include (PATH_VIEWS . "customer/CustomerView.php") ;

		$action_menu = '<section class="content-header" id="main-header" style="padding-left: 230px;">' .
						'<div class="dropdown">'.
							'<button class="dropbtn">Actions&nbsp;&#x25BC;</button>'.
							'<div class="dropdown-content" id="actionsMenu">'.
								"<a href='javascript:' onclick='showImportCustomer()'>".
								"<span>Import</span>".
							"</a>".
							"</div>".
						"</div>".
						'</section>';

        $var_screen = str_replace("[index]",$params['index'], Util::minifyHtml(ob_get_clean())) ;
        $dataReturn=[];
		$dataReturn['htmlContent'] = $var_screen;
		$dataReturn['action_menu'] = $action_menu;
		$dataReturn['contactOption'] = $this->getCustomerContactOption();
        $dataReturn['headTitle']  = "Customer" ;
		$this->sendJsonResponse(Status::Ok,"Customer Info",$dataReturn,$this->type);
	}

    private function getCustomerList($params) {
        $cls = new CustomerClass($this->db);

        $sorts  = $params['sorts'];

        $orderby = "";
        $sort = array();
        if ($sorts != "") {
            $sort = explode(':', $sorts);
            switch ($sort[0]) {
                case "col_code":
                    $orderby = CustomerTable::C_CODE . " " . $sort[1];
                    break;
                case "col_name":
                    $orderby = CustomerTable::C_NAME . " " . $sort[1];
                    break;
                case "col_status":
                    $orderby = CustomerTable::C_STATUS . " " . $sort[1];
                    break;
                default:
                    $orderby = CustomerTable::C_NAME . " " . $sort[1];
                    break;
            }
        } else {
            $orderby = CustomerTable::C_NAME . " asc";
        }

        $status = "";
        if (isset($params['status'])) {
            $status = $params['status'];
        } else {
            $status = "all";
        }

        $rows = $cls->getCustomerList("", $status, $orderby);

        $lists = [];
        if (!is_null($rows) && count($rows) > 0) {
            foreach ($rows as $row) {
                $list = [
                    "id" => $row[CustomerTable::C_ID],
                    "code" => htmlentities($row[CustomerTable::C_CODE]),
                    "name" => htmlentities($row[CustomerTable::C_NAME]),
                    "status" => ($row[CustomerTable::C_STATUS] == 1 ? 'Active' : 'Inactive'),
                ];

                array_push($lists, $list);
            }
        }
        $lists = json_encode($lists);

        $this->sendJsonResponse(Status::Ok, "", $lists, $this->type);
    }

    private function getCustomerContactList($params) {
        $cls = new CustomerContactClass($this->db);

        $sorts  = $params['sorts'];

        $orderby = "";
        $sort = array();
        if ($sorts != "") {
            $sort = explode(':', $sorts);
            switch ($sort[0]) {
                case "col_name":
                    $orderby = CustomerContactTable::C_PREFER_NAME . " " . $sort[1];
                    break;
                case "col_dept":
                    $orderby = CustomerContactTable::C_DEPT . " " . $sort[1];
                    break;
                case "col_email":
                    $orderby = CustomerContactTable::C_EMAIL . " " . $sort[1];
                    break;
                case "col_telephone":
                    $orderby = CustomerContactTable::C_TELEPHONE . " " . $sort[1];
                    break;
                case "col_mobile":
                    $orderby = CustomerContactTable::C_MOBILE . " " . $sort[1];
                    break;
                case "col_priority":
                    $orderby = CustomerContactTable::C_PRIORITY . " " . $sort[1];
                    break;
                case "col_status":
                    $orderby = CustomerContactTable::C_STATUS . " " . $sort[1];
                    break;
                default:
                    $orderby = CustomerContactTable::C_PREFER_NAME . " " . $sort[1];
                    break;
            }
        } else {
            $orderby = CustomerContactTable::C_PREFER_NAME . " asc";
        }

        $rows = $cls->getCustomerContactList($params['cust_id'], "", $orderby);

        $lists = [];
        if (!is_null($rows) && count($rows) > 0) {
            foreach ($rows as $row) {
                $list = [
                    "id"                    => $row[CustomerContactTable::C_CONTACT_ID],
                    "full_name"             => $row[CustomerContactTable::C_NAME],
                    "prefer_name"           => $row[CustomerContactTable::C_PREFER_NAME],
                    "designation"           => $row[CustomerContactTable::C_DESIGNATION],
                    "salutation"            => $row[CustomerContactTable::C_SALUTATION],
                    "job_desc"              => $row[CustomerContactTable::C_JOB_DESC],
                    "dept"                  => $row[CustomerContactTable::C_DEPT],
                    "dept_group_id"         => $row[CustomerContactTable::C_DEPT_GROUP],
                    "contact_status"        => ($row[CustomerContactTable::C_STATUS] == 1 ? 'Active' : 'Inactive'),
                    "status"                => $row[CustomerContactTable::C_STATUS],
                    "report_to"             => $row[CustomerContactTable::C_REPORT_TO],
                    "email"                 => $row[CustomerContactTable::C_EMAIL],
                    "telephone"             => $row[CustomerContactTable::C_TELEPHONE],
                    "mobile"                => $row[CustomerContactTable::C_MOBILE],
                    "commun_prefer"         => $row[CustomerContactTable::C_COMM_PREF],
                    "hobbies"               => $row[CustomerContactTable::C_HOBBIES],
                    "character"             => $row[CustomerContactTable::C_CHARACTER],
                    "priority"              => $row[CustomerContactTable::C_PRIORITY],
                    "esupport_finance"      => $row[CustomerContactTable::C_ESUPPORT_FINANCE],
                    "esupport_it"           => $row[CustomerContactTable::C_ESUPPORT_IT],
                    "esupport_project"      => $row[CustomerContactTable::C_ESUPPORT_PROJECT],
                    "esupport_management"   => $row[CustomerContactTable::C_ESUPPORT_MANAGEMENT],
                ];

                array_push($lists, $list);
            }
        }
        $lists = json_encode($lists);

        $this->sendJsonResponse(Status::Ok, "", $lists, $this->type);
    }

    private function addRecord($params) {
        $clsCustomer  = new CustomerClass($this->db);

        $datas        = array();
        $modifyby     = $_SESSION[SE_USERID];
        $modifydate   = date_create('now')->format('Y-m-d H:i:s');
        $ws           = $_SESSION[SE_REMOTE_IP];

        $datas[] = $this->db->fieldValue(CustomerTable::C_CODE, $this->getParam($params, 'code', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_STATUS,$this->getParamInt($params,'active',1)) ;
        $datas[] = $this->db->fieldValue(CustomerTable::C_NAME, $this->getParam($params, 'name', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_TELEPHONE, $this->getParam($params, 'telephone', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_FAX, $this->getParam($params, 'fax', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_WEB, $this->getParam($params, 'website', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_ADDRESS, $this->getParam($params, 'address', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_COMPANY_REG_NO, $this->getParam($params, 'company_reg_no', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_INDUSTRY, $this->getParam($params, 'industry', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_BUSINESS_SECTOR, $this->getParam($params, 'business_sector', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_NUM_PEOPLE, $this->getParam($params, 'no_people', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_TURN_OVER, $this->getParam($params, 'sales_turn_over', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_SUN_VERSION, $this->getParam($params, 'sunsystem_ver', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_MSSQL_VERSION, $this->getParam($params, 'mssql_ver', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_QA_VERSION, $this->getParam($params, 'qa_ver', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_WIN_SERVER_VERSION, $this->getParam($params, 'windows_server_ver', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_SUPP_PARTNER, $this->getParam($params, 'supporting_partner', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_SOFTWARE_INFO, $this->getParam($params, 'software_info', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_BILLING_CYCLE, $this->getParam($params, 'billing_cycle', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_REMARKS, $this->getParam($params, 'remarks', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_CUS_ACC, $this->getParam($params, 'account', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_COMP_POLICY, $this->getParam($params, 'company_policy', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_PROD_CONVERSATION, $this->getParam($params, 'product_conver', ""));
        $datas[] = $this->db->fieldValue(CustomerTable::C_PRIMARY_REMOTE, $this->getParamInt($params, 'pri_remote_access', 0));
        $datas[] = $this->db->fieldValue(CustomerTable::C_SECOND_REMOTE, $this->getParamInt($params, 'sec_remote_access', 0));
        $datas[] = $this->db->fieldValue(CustomerTable::C_PRIMARY_MESG, $this->getParamInt($params, 'pri_video_mesg', 0));
        $datas[] = $this->db->fieldValue(CustomerTable::C_SECOND_MESG, $this->getParamInt($params, 'sec_video_mesg', 0));

        $datas[] = $this->db->fieldValue(CustomerTable::C_WS_ID, $ws);
        $datas[] = $this->db->fieldValue(CustomerTable::C_MODIFY_BY, $modifyby);
        $datas[] = $this->db->fieldValue(CustomerTable::C_CREATE_BY, $modifyby);
        $datas[] = $this->db->fieldValue(CustomerTable::C_MODIFY_DATE, $modifydate);
        $datas[] = $this->db->fieldValue(CustomerTable::C_CREATE_DATE, $modifydate);

        try {
            $this->db->beginTran();
            $exist  = $clsCustomer->getExistingRecords($this->getParam($params, 'code', ""));
            $result = array();

            if (!is_null($exist)) {
                $this->sendJsonResponse(Status::Error, "Customer code already exist.", "", $this->type);
            } else {                
                $cust_id    = $clsCustomer->addRecord($datas);
                $result['id'] = $cust_id;
                if(isset($params['new_contact_list'])){
                    $contact_data = $params['new_contact_list'];
                    $this->insertCustomerContact($cust_id, $contact_data);
                }
                if(isset($params['new_software_list'])){
                    $software_data  = $params['new_software_list'];
                    $this->insertCustomerSoftware($cust_id, $software_data);
                }
                if(isset($params['new_branch_list'])){
                    $branch_data  = $params['new_branch_list'];
                    $this->insertCustomerBranch($cust_id, $branch_data);
                }
                if(isset($params['new_policy_list'])){
                    $policy_data  = $params['new_policy_list'];
                    $policy_result = $this->insertCustomerPolicy($cust_id, $policy_data);

                    $result['policy'] = $policy_result[1];
                    if($policy_result[0] != "success"){
                        $this->sendJsonResponse(Status::Error, $policy_result[0],'',$this->type) ;
                        return;
                    }
                }

                $this->db->commitTran();
                $this->sendJsonResponse(Status::Ok, "Successfully inserted into system", $result, $this->type);
            }
        } catch (Exception $e) {
            $this->db->rollbackTran() ;	
            $result['id'] = 0;
            Log::write('[Customer]' . $e->getMessage());
            $this->sendJsonResponse(Status::Error, "Sorry, there is a error in database operation.", $result['id'], $this->type);
        }
        unset($clsCustomer);
    }

    private function updateRecord($params) {
        $cls            = new CustomerClass($this->db);
        $clsContact     = new CustomerContactClass($this->db);
        $clsBranch      = new CustomerBranchClass($this->db);
        $clsSoftware    = new CustomerSoftwareClass($this->db);

		if (isset($params['id'])) {
            $id = $params['id'] ;

            $contact_data   = "";
            $branch_data    = "";
            $software_data  = "";

			$cls = new CustomerClass($this->db) ;

            $datas = array() ;

            $modifyby 	= $_SESSION[SE_USERID] ;
            $modifydate = date_create('now')->format('Y-m-d H:i:s') ;
            $ws = $_SESSION[SE_REMOTE_IP] ;

            $datas[] = $this->db->fieldValue(CustomerTable::C_CODE, $this->getParam($params, 'code', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_STATUS, $this->getParamInt($params, 'active', 1));
            $datas[] = $this->db->fieldValue(CustomerTable::C_NAME, $this->getParam($params, 'name', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_TELEPHONE, $this->getParam($params, 'telephone', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_FAX, $this->getParam($params, 'fax', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_WEB, $this->getParam($params, 'website', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_ADDRESS, $this->getParam($params, 'address', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_COMPANY_REG_NO, $this->getParam($params, 'company_reg_no', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_INDUSTRY, $this->getParam($params, 'industry', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_BUSINESS_SECTOR, $this->getParam($params, 'business_sector', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_NUM_PEOPLE, $this->getParam($params, 'no_people', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_TURN_OVER, $this->getParam($params, 'sales_turn_over', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_SUN_VERSION, $this->getParam($params, 'sunsystem_ver', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_MSSQL_VERSION, $this->getParam($params, 'mssql_ver', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_QA_VERSION, $this->getParam($params, 'qa_ver', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_WIN_SERVER_VERSION, $this->getParam($params, 'windows_server_ver', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_SUPP_PARTNER, $this->getParam($params, 'supporting_partner', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_SOFTWARE_INFO, $this->getParam($params, 'software_info', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_BILLING_CYCLE, $this->getParam($params, 'billing_cycle', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_REMARKS, $this->getParam($params, 'remarks', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_CUS_ACC, $this->getParam($params, 'account', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_COMP_POLICY, $this->getParam($params, 'company_policy', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_PROD_CONVERSATION, $this->getParam($params, 'product_conver', ""));
            $datas[] = $this->db->fieldValue(CustomerTable::C_PRIMARY_REMOTE, $this->getParamInt($params, 'pri_remote_access', 0));
            $datas[] = $this->db->fieldValue(CustomerTable::C_SECOND_REMOTE, $this->getParamInt($params, 'sec_remote_access', 0));
            $datas[] = $this->db->fieldValue(CustomerTable::C_PRIMARY_MESG, $this->getParamInt($params, 'pri_video_mesg', 0));
            $datas[] = $this->db->fieldValue(CustomerTable::C_SECOND_MESG, $this->getParamInt($params, 'sec_video_mesg', 0));

            $datas[] = $this->db->fieldValue(CustomerTable::C_WS_ID,$ws) ;
            $datas[] = $this->db->fieldValue(CustomerTable::C_MODIFY_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerTable::C_MODIFY_DATE,$modifydate) ;

			try {
                $this->db->beginTran();
                $mesg   = "";
                $result['id'] = $id;

                $exist = $cls->getExistingRecords($this->getParam($params, 'code', ""), $id);
                if (!is_null($exist)) {
                    $this->sendJsonResponse(Status::Error, "Customer code already exist.", "", $this->type);
                } else {
                    $cls->updateRecord($id,$datas);

                    if(isset($params['exist_contact_list'])){
                        $contact_data = $params['exist_contact_list'];
                        $this->updateCustomerContact($id, $contact_data);
                    }else{
                        $clsContact->deleteCustomerContact($id);
                    }
                    if(isset($params['new_contact_list'])){
                        $contact_data = $params['new_contact_list'];
                        $this->insertCustomerContact($id, $contact_data);
                    }

                    if(isset($params['exist_software_list'])){
                        $software_data  = $params['exist_software_list'];
                        $this->updateCustomerSoftware($id, $software_data);
                    }else{
                        $clsSoftware->deleteCustomerSoftware($id);
                    }
                    if(isset($params['new_software_list'])){
                        $software_data  = $params['new_software_list'];
                        $this->insertCustomerSoftware($id, $software_data);
                    }

                    if(isset($params['exist_branch_list'])){
                        $branch_data  = $params['exist_branch_list'];
                        $this->updateCustomerBranch($id, $branch_data);
                    }else{
                        $clsBranch->deleteCustomerBranch($id);
                    }
                    if(isset($params['new_branch_list'])){
                        $branch_data  = $params['new_branch_list'];
                        $this->insertCustomerBranch($id, $branch_data);
                    }

                    if(isset($params['exist_policy_list'])){
                        $policy_data  = $params['exist_policy_list'];
                        $policy_result = $this->updateCustomerPolicy($id, $policy_data);
                    }

                    if(isset($params['new_policy_list'])){
                        $policy_data  = $params['new_policy_list'];
                        $policy_result = $this->insertCustomerPolicy($id, $policy_data);

                        $result['policy'] = $policy_result[1];
                        if($policy_result[0] != "success"){
                            $this->sendJsonResponse(Status::Error, $policy_result[0],'',$this->type) ;
                            return;
                        }
                    }
                    
                    $mesg = "Customer successfully updated to the system.";

                    $this->db->commitTran();
                    $this->sendJsonResponse(Status::Ok, $mesg, $result, $this->type) ;
                }
			} catch (Exception $e) {
                $this->db->rollbackTran() ;	
				Log::write('[Customer]' . $e->getMessage());
                $result['id'] = $id;
				$this->sendJsonResponse(Status::Error,"Sorry, there is a error in updating customer detail to the system.",$result,$this->type) ;
			}
			unset($cls) ;
			unset($clsContact) ;
		}else {
            $result['id'] = 0;
			$this->sendJsonResponse(Status::Error,"You must supply the customer id you wish to update. Please try again.",$result,$this->type);
		}
    }

    private function deleteContact($params) {
        $id = $params['contact_id'];
        $cust_id = $params['cust_id'];
        $clsContact = new CustomerContactClass($this->db);

        try {
            $this->db->beginTran();
            $clsContact->deleteRecord($id);
            $this->db->commitTran();
            $this->sendJsonResponse(Status::Ok, "Successfully deleted contact",$cust_id,$this->type);
        } catch (Exception $e) {
            $this->db->rollbackTran();
            $this->sendJsonResponse(Status::Error, Message::DB_ERR_DELETE_USED,$cust_id,$this->type);
        }
    }

    private function deleteBranch($params) {
        $id      = $params['branch_id'];
        $cust_id = $params['cust_id'];
        $clsBranch = new CustomerBranchClass($this->db);

        try {
            $this->db->beginTran();
            $clsBranch->deleteRecord($id);
            $this->db->commitTran();
            $this->sendJsonResponse(Status::Ok, "Successfully deleted branch", $cust_id,$this->type);
        } catch (Exception $e) {
            $this->db->rollbackTran();
            $this->sendJsonResponse(Status::Error, Message::DB_ERR_DELETE_USED, $cust_id,$this->type);
        }
    }

    private function deleteCustomerRecord($params) {
        $cls        = new CustomerClass($this->db);
        $clsContact = new CustomerContactClass($this->db);
        $clsBranch  = new CustomerBranchClass($this->db);

        $id =  $this->getParamInt($params, 'id', 0);
        if ($id == 0) {
            $this->sendJsonResponse(Status::Error, "Invalid ID.", "", $this->type);
            return;
        }

        try {
            $this->db->beginTran();

            $cls->deleteRecord($id);
            $clsContact->deleteCustomerContact($id);
            $clsBranch->deleteCustomerBranch($id);

            $this->db->commitTran();

            $this->sendJsonResponse(Status::Ok, "Successfully delete customer from system.", "", $this->type);
            return;
        } catch (Exception $e) {
            $this->db->rollbackTran() ;
            if($e->getMessage() == Message::DB_ERR_DELETE_USED){
                $this->sendJsonResponse(Status::Error, Message::DB_ERR_DELETE_USED,"",$this->type);
            }else{
                Log::write('[Customer] ' . $e->getMessage());
                $this->sendJsonResponse(Status::Error,"Sorry, there is a error in deleting customer from the system.","",$this->type);
            }
            return;
        }

        unset($cls);
    }

    private function updateCustomerContact($cust_id, $datas) {        
        $clsContact = new CustomerContactClass($this->db);
        $rows       = $clsContact->getCustomerContact($cust_id);

        $modifyby 	= $_SESSION[SE_USERID] ;
        $modifydate = date_create('now')->format('Y-m-d H:i:s') ;
        $ws         = $_SESSION[SE_REMOTE_IP] ;
        $list_id    = array();
        $existing_list_id    = array();

        foreach($datas as $data){
            $datas = array();

            $id      = $data['id'];
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_NAME, $this->getParam($data, 'full_name', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_PREFER_NAME, $this->getParam($data, 'prefer_name', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_STATUS, $this->getParamInt($data, 'contact_status', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_EMAIL, $this->getParam($data, 'email', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_TELEPHONE, $this->getParam($data, 'telephone', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_MOBILE, $this->getParam($data, 'mobile', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_DESIGNATION, $this->getParam($data, 'designation', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_SALUTATION, $this->getParamInt($data, 'salutation', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_REPORT_TO, $this->getParam($data, 'report_to', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_DEPT, $this->getParam($data, 'dept', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_COMM_PREF, $this->getParam($data, 'commun_prefer', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_JOB_DESC, $this->getParam($data, 'job_desc', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_HOBBIES, $this->getParam($data, 'hobbies', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_CHARACTER, $this->getParam($data, 'character', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_DEPT_GROUP, $this->getParamInt($data, 'dept_group_id', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_PRIORITY, $this->getParam($data, 'priority', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_ESUPPORT_FINANCE, $this->getParam($data, 'esupport_finance', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_ESUPPORT_IT, $this->getParam($data, 'esupport_it', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_ESUPPORT_MANAGEMENT, $this->getParam($data, 'esupport_project', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_ESUPPORT_PROJECT, $this->getParam($data, 'esupport_management', 0));
            
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_WS_ID,$ws) ;
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_CREATE_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_CREATE_DATE,$modifydate) ;
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_MODIFY_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_MODIFY_DATE,$modifydate) ;

            $clsContact->updateRecord($id, $datas);
            array_push($list_id, $id);
        }

        foreach($rows as $row){
            array_push($existing_list_id, $row[CustomerContactTable::C_CONTACT_ID]);
        }

        $deleted_contacts = array_diff($existing_list_id, $list_id);

        foreach($deleted_contacts as $deleted_contact){
            $clsContact->deleteRecord($deleted_contact);
        }
    }

    private function insertCustomerContact($cust_id, $datas) {        
        $clsContact = new CustomerContactClass($this->db);

        $modifyby 	= $_SESSION[SE_USERID] ;
        $modifydate = date_create('now')->format('Y-m-d H:i:s') ;
        $ws = $_SESSION[SE_REMOTE_IP] ;

        foreach($datas as $data){
            $datas = array();

            $datas[] = $this->db->fieldValue(CustomerContactTable::C_ID, $cust_id);
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_NAME, $this->getParam($data, 'full_name', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_PREFER_NAME, $this->getParam($data, 'prefer_name', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_EMAIL, $this->getParam($data, 'email', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_TELEPHONE, $this->getParam($data, 'telephone', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_MOBILE, $this->getParam($data, 'mobile', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_DESIGNATION, $this->getParam($data, 'designation', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_SALUTATION, $this->getParamInt($data, 'salutation', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_REPORT_TO, $this->getParam($data, 'report_to', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_DEPT, $this->getParam($data, 'dept', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_STATUS, $this->getParamInt($data, 'contact_status', 0));

            $datas[] = $this->db->fieldValue(CustomerContactTable::C_COMM_PREF, $this->getParam($data, 'commun_prefer', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_JOB_DESC, $this->getParam($data, 'job_desc', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_HOBBIES, $this->getParam($data, 'hobbies', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_CHARACTER, $this->getParam($data, 'character', ""));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_DEPT_GROUP, $this->getParamInt($data, 'dept_group_id', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_PRIORITY, $this->getParam($data, 'priority', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_ESUPPORT_FINANCE, $this->getParam($data, 'esupport_finance', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_ESUPPORT_IT, $this->getParam($data, 'esupport_it', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_ESUPPORT_MANAGEMENT, $this->getParam($data, 'esupport_project', 0));
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_ESUPPORT_PROJECT, $this->getParam($data, 'esupport_management', 0));
            
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_WS_ID,$ws) ;
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_CREATE_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_CREATE_DATE,$modifydate) ;
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_MODIFY_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerContactTable::C_MODIFY_DATE,$modifydate) ;

            $clsContact->addRecord($datas);
        }
    }

    public function updateCustomerBranch($cust_id, $datas){
        $clsBranch  = new CustomerBranchClass($this->db);
        $clsContact = new CustomerContactClass($this->db);
        $rows       = $clsBranch->getCustomerBranch($cust_id);

        $existing_list_id = array();
        $list_id          = array();

        $modifyby 	      = $_SESSION[SE_USERID] ;
        $modifydate       = date_create('now')->format('Y-m-d H:i:s') ;
        $ws               = $_SESSION[SE_REMOTE_IP] ;

        foreach($datas as $data){
            $datas = array();
            $id      = $data['id'];

            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CODE, $this->getParam($data, 'code', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_NAME, $this->getParam($data, 'name', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_TYPE, $this->getParamInt($data, 'branch_type', 0));
        
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_ADD, $this->getParam($data, 'add', ''));
            
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_WEB, $this->getParam($data, 'web', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_TELEPHONE, $this->getParam($data, 'telephone', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_FAX, $this->getParam($data, 'fax', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_EMAIL, $this->getParam($data, 'email', ''));
        
            $contact = $this->getParam($data, 'contact', '');
            if(!is_null($contact) && $contact != ""){
                if(is_numeric($contact)){
                    $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CONTACT, $contact);
                }else{
                    $contact = $clsContact->getIdByContactName($cust_id, $contact);
                    $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CONTACT, $contact);
                }
            }

            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_WS_ID,$ws) ;
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_MODIFY_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_MODIFY_DATE,$modifydate) ;

            $clsBranch->updateRecord($id, $datas);
            array_push($list_id, $data['id']);
        }

        foreach($rows as $row){
            array_push($existing_list_id, $row[CustomerBranchTable::C_BRANCH_ID]);
        }

        $deleted_branchs = array_diff($existing_list_id, $list_id);

        foreach($deleted_branchs as $deleted_branch){
            $clsBranch->deleteRecord($deleted_branch);
        }
    }

    private function insertCustomerSoftware($cust_id, $datas) {
        $clsSoftware  = new CustomerSoftwareClass($this->db);

        $modifyby 	= $_SESSION[SE_USERID] ;
        $modifydate = date_create('now')->format('Y-m-d H:i:s') ;
        $ws = $_SESSION[SE_REMOTE_IP] ;

        foreach($datas as $data){
            $datas = array();

            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_ID, $cust_id);
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_SOFTWARE, $this->getParamInt($data, 'code', 0));
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_VERSION, $this->getParam($data, 'version', ''));
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_USER, $this->getParamInt($data, 'user', 0));
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_TYPE, $this->getParamInt($data, 'soft_type', 0));
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_LIVE_DATE, $this->getParam($data, 'live_date', NULL_DATE));
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_RENEW_DATE, $this->getParam($data, 'renew_date', NULL_DATE));

            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_WS_ID,$ws) ;
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_CREATE_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_CREATE_DATE,$modifydate) ;
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_MODIFY_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerSoftwareTable::C_MODIFY_DATE,$modifydate) ;

            $clsSoftware->addRecord($datas);
        }
    }

    public function updateCustomerSoftware($cust_id, $datas){
        $clsSoftware  = new CustomerSoftwareClass($this->db);
        $rows = $clsSoftware->getCustomerSoftware($cust_id);

        $existing_list_id = array();
        $list_id          = array();
        foreach($datas as $data){
            $content = array();
            $content[] = $this->db->fieldValue(CustomerSoftwareTable::C_SOFTWARE, $this->getParamInt($data, 'code', 0));
            $content[] = $this->db->fieldValue(CustomerSoftwareTable::C_VERSION, $this->getParam($data, 'version', ''));
            $content[] = $this->db->fieldValue(CustomerSoftwareTable::C_TYPE, $this->getParamInt($data, 'soft_type', 0));
            $content[] = $this->db->fieldValue(CustomerSoftwareTable::C_USER, $this->getParamInt($data, 'user', 0));
            $content[] = $this->db->fieldValue(CustomerSoftwareTable::C_LIVE_DATE, $this->getParam($data, 'live_date', NULL_DATE));
            $content[] = $this->db->fieldValue(CustomerSoftwareTable::C_RENEW_DATE, $this->getParam($data, 'renew_date', NULL_DATE));
            $clsSoftware->updateRecord($data['id'], $content);
            array_push($list_id, $data['id']);
        }

        foreach($rows as $row){
            array_push($existing_list_id, $row[CustomerSoftwareTable::C_CUS_SOFTWARE_ID]);
        }

        $deleted_softwares = array_diff($existing_list_id, $list_id);

        foreach($deleted_softwares as $deleted_software){
            $clsSoftware->deleteRecord($deleted_software);
        }
    }

    private function insertCustomerBranch($cust_id, $datas) {
        $clsBranch  = new CustomerBranchClass($this->db);
        $clsContact = new CustomerContactClass($this->db);

        $modifyby 	= $_SESSION[SE_USERID] ;
        $modifydate = date_create('now')->format('Y-m-d H:i:s') ;
        $ws = $_SESSION[SE_REMOTE_IP] ;

        foreach($datas as $data){
            $datas = array();

            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_ID, $cust_id);
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CODE, $this->getParam($data, 'code', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_NAME, $this->getParam($data, 'name', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_TYPE, $this->getParamInt($data, 'branch_type', 0));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_ADD, $this->getParam($data, 'add', ''));
            
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_WEB, $this->getParam($data, 'web', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_TELEPHONE, $this->getParam($data, 'telephone', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_FAX, $this->getParam($data, 'fax', ''));
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_EMAIL, $this->getParam($data, 'email', ''));
        
            $contact = $this->getParam($data, 'contact', '');
            if(!is_null($contact) && $contact != ""){
                if(is_numeric($contact)){
                    $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CONTACT, $contact);
                }else{
                    $contact = $clsContact->getIdByContactName($cust_id, $contact);
                    $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CONTACT, $contact);
                }
            }

            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_WS_ID,$ws) ;
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CREATE_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CREATE_DATE,$modifydate) ;
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_MODIFY_BY,$modifyby) ;
            $datas[] = $this->db->fieldValue(CustomerBranchTable::C_MODIFY_DATE,$modifydate) ;

            $clsBranch->addRecord($datas);
        }
    }

    public function getContactBranchList($params){
        $clsContact = new CustomerContactClass($this->db);
        $clsBranch  = new CustomerBranchClass($this->db);

        $id = $params['id'];
        
        $rows_contact   = $clsContact->getCustomerContact($id);
        $rows_branch    = $clsBranch->getCustomerBranch($id);

        $contact_list = array();
        if(!is_null($rows_contact) && count($rows_contact) > 0){
            $datas_contact = array();
            foreach($rows_contact as $row_contact){

                $datas_contact['id']                    = $row_contact[CustomerContactTable::C_CONTACT_ID];
                $datas_contact['prefer_name']           = $row_contact[CustomerContactTable::C_PREFER_NAME];
                $datas_contact['dept']                  = $row_contact[CustomerContactTable::C_DEPT];
                $datas_contact['dept_group']            = $row_contact[DepartmentGroupTable::C_DESC];
                $datas_contact['dept_group_id']         = $row_contact[CustomerContactTable::C_DEPT_GROUP];
                $datas_contact['contact_status']        = ($row_contact[CustomerContactTable::C_STATUS] == 0)? "Inactive":"Active";
                $datas_contact['status']                = $row_contact[CustomerContactTable::C_STATUS];
                $datas_contact['email']                 = $row_contact[CustomerContactTable::C_EMAIL];
                $datas_contact['telephone']             = $row_contact[CustomerContactTable::C_TELEPHONE];
                $datas_contact['mobile']                = $row_contact[CustomerContactTable::C_MOBILE];
                $datas_contact['full_name']             = $row_contact[CustomerContactTable::C_NAME];
                $datas_contact['designation']           = $row_contact[CustomerContactTable::C_DESIGNATION];
                $datas_contact['salutation']            = $row_contact[CustomerContactTable::C_SALUTATION];
                $datas_contact['job_desc']              = $row_contact[CustomerContactTable::C_JOB_DESC];
                $datas_contact['report_to']             = $row_contact[CustomerContactTable::C_REPORT_TO];
                $datas_contact['commun_prefer']         = $row_contact[CustomerContactTable::C_COMM_PREF];
                $datas_contact['hobbies']               = $row_contact[CustomerContactTable::C_HOBBIES];
                $datas_contact['character']             = $row_contact[CustomerContactTable::C_CHARACTER];
                $datas_contact['priority']              = $row_contact[CustomerContactTable::C_PRIORITY];
                $datas_contact['esupport_finance']      = $row_contact[CustomerContactTable::C_ESUPPORT_FINANCE];
                $datas_contact['esupport_it']           = $row_contact[CustomerContactTable::C_ESUPPORT_IT];
                $datas_contact['esupport_project']      = $row_contact[CustomerContactTable::C_ESUPPORT_MANAGEMENT];
                $datas_contact['esupport_management']   = $row_contact[CustomerContactTable::C_ESUPPORT_PROJECT];

                array_push($contact_list, $datas_contact);
            }
        }

        $branch_list = array();
        if(!is_null($rows_branch) && count($rows_branch) > 0){
            $datas_branch = array();
            foreach($rows_branch as $row_branch){
                $array_branch                       = ['', 'HQ', 'Branch'];

                $datas_branch['id']                 = $row_branch[CustomerBranchTable::C_BRANCH_ID];  
                $datas_branch['code']               = $row_branch[CustomerBranchTable::C_CODE];  
                $datas_branch['name']               = $row_branch[CustomerBranchTable::C_NAME];  
                $datas_branch['branch_type_name']   = $array_branch[$row_branch[CustomerBranchTable::C_TYPE]];
                $datas_branch['branch_type']        = $row_branch[CustomerBranchTable::C_TYPE];
                $datas_branch['telephone']          = $row_branch[CustomerBranchTable::C_TELEPHONE];
                $datas_branch['add']                = $row_branch[CustomerBranchTable::C_ADD];
                $datas_branch['web']                = $row_branch[CustomerBranchTable::C_WEB];
                $datas_branch['contact']            = $row_branch[CustomerBranchTable::C_CONTACT];
                $datas_branch['fax']                = $row_branch[CustomerBranchTable::C_FAX];

                array_push($branch_list, $datas_branch);
            }
        }

        $result['contact'] = $contact_list;
        $result['branch'] = $branch_list;
    
        $this->sendJsonResponse(Status::Ok,"", $result,$this->type);
    }

    public function updateCustomerPolicy($cust_id, $datas){
        $clsPolicy  = new CustomerPolicyClass($this->db);
        $rows = $clsPolicy->getCustomerPolicy($cust_id);

        $existing_list_id = array();
        $list_id          = array();
        foreach($datas as $data){
            $clsPolicy->updateCustomerPolicy($data['id'], $data['desc']);
            array_push($list_id, $data['id']);
        }

        foreach($rows as $row){
            array_push($existing_list_id, $row[CustomerPolicyTable::C_POLICY_ID]);
        }

        $deleted_policies = array_diff($existing_list_id, $list_id);

        foreach($deleted_policies as $deleted_policy){
            $clsPolicy->deleteRecord($deleted_policy);
        }
    }

    public function insertCustomerPolicy($cust_id, $datas){
        $policy = array();
        $count = 0;
        foreach($datas as $data){
            $newfile    = "";
            $base64file = $this->getParam($data,'base_64',"");
            $extension  = $this->getParam($data,'ext',"");
            $name       = "customer_policy_" . time() . "_" . $count;
            $desc       = $this->getParam($data,'desc',"");

            $clsPolicy  = new CustomerPolicyClass($this->db);		

            if($base64file!="" || $base64file!=null){

                $path = PATH_CUSTOMER_POLICY . $cust_id . "/";
                if (!file_exists($path)) {
                    mkdir($path, 1777, true);
                }

                $data= $base64file;
                $type= $extension;

                $newfile = $name . '.' . strtolower($extension);

                if (preg_match('/^data:image\/(\w+);base64,/', $data, $type)) {
                    $data = substr($data, strpos($data, ',') + 1);
                    $type = strtolower($type[1]); // jpg, png, gif

                    if (!in_array($type, [ 'jpg', 'jpeg', 'png' ])) {

                        Log::write('invalid image type');
                        return "invalid image type.";
                    }
                    $data = str_replace( ' ', '+', $data );
                    $data = base64_decode($data);

                    if ($data === false) {
                        //throw new \Exception('base64_decode failed');
                        Log::write('base64_decode failed');
                        $this->sendJsonResponse(Status::Error,"base64_decode failed.","",$this->type);
                        return;
                    }

                    file_put_contents($path . $newfile, $data);
                } else if (preg_match('/^data:application\/(\w+);base64,/', $data, $type)) {
                    $data = substr($data, strpos($data, ',') + 1);
                    $type = strtolower($type[1]); // pdf

                    if (!in_array($type, [ 'pdf' ])) {

                        Log::write('invalid file type');
                        return "invalid file type.";
                    }
                    $data = str_replace( ' ', '+', $data );
                    $data = base64_decode($data);

                    if ($data === false) {
                        //throw new \Exception('base64_decode failed');
                        Log::write('base64_decode failed');
                        return "base64_decode failed.";
                    }

                    file_put_contents($path . $newfile, $data);
                }else {
                    //throw new \Exception('did not match data URI with image data');
                    Log::write('did not match data URI with image data');
                    return "did not match data URI with image data.";
                }

                $data_policy = array();
                try {
                    $datas_doc_to_save   = array() ;
                    $datas_doc_to_save[] = $this->db->fieldValue(CustomerPolicyTable::C_ID ,$cust_id);
                    $datas_doc_to_save[] = $this->db->fieldValue(CustomerPolicyTable::C_DESC , $desc);
                    $datas_doc_to_save[] = $this->db->fieldValue(CustomerPolicyTable::C_ATTACH , $newfile);
                    
                    $id = $clsPolicy->addRecord($datas_doc_to_save) ;

                    $data_policy['id']       = $id ;
                    $data_policy['desc']     = $desc ;
                    $data_policy['filename'] = $newfile;
                    
                    array_push($policy, $data_policy);
                } catch (Exception $e){
                    Log::write('[Customer]' . $e->getMessage());            
                    return $e->getMessage();

                }
                $count++;
            }
        }

        return ["success", $policy];
    }

    private function processDownloadFile($params) {
        $cls = new CustomerPolicyClass($this->db);
        
        $id = $this->getParamInt($params,'id',0) ;
        $row  = $cls->getRecord($id);
        if(!is_null($row) && count($row) > 0) {
            $expfile   = $row[CustomerPolicyTable::C_ATTACH];
            $policy_id = $row[CustomerPolicyTable::C_ID];
        }else{
            $expfile   = "";
            $policy_id = "";
        }

        $dir = PATH_CUSTOMER_POLICY . $policy_id . "/";
        $fn = $expfile;
        if ($expfile != "") {
            $docfile = $dir . $fn;

            if (file_exists($docfile)) {
                $filename = basename($expfile);
                $file_extension = strtolower(substr(strrchr($filename,"."),1));
                switch( $file_extension ) {
                    case "gif":
                        header('Content-type: image/gif');
                        break;
                    case "png":
                        header('Content-type: image/png');
                        break;
                    case "jpeg":
                    case "jpg":
                        header('Content-type: image/jpeg');
                        break;
                    case "pdf":
                        header('Content-type: application/pdf');
                        break;
                    default:
                        header('Content-disposition: attachment; filename='. $filename);
                        header('Content-type: application/octet-stream');
                        break;
                }

                header('Content-Description: View File');
                header('Content-Transfer-Encoding: binary');
                header('Expires: 0');
                header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
                header('Pragma: public');
                header('Content-Length: ' . filesize($docfile));

                ob_clean();
                flush();
                readfile($docfile);
                exit;

            } else {
                echo "<tr><td colspan='12'>Document file not found.</td></tr>" ;
                return;
            }
        } else {
            echo "<tr><td colspan='12'>Missing document file info.</td></tr>" ;
            return;
        }
    }

    //=============================================================================================
    //IMPORT EXCEL
    private function getTemplate() {
		/** Create a new Spreadsheet Object **/
		$spreadsheet = new Spreadsheet();

        $style_title = array(
            'font'             => array('bold' => true, 'size' => 10),
            'alignment'     => array('horizontal' => Alignment::HORIZONTAL_LEFT,)
        );

        $style_header = array(
            'font'             => array('bold' => true, 'size' => 14),
            'alignment'     => array('horizontal' => Alignment::HORIZONTAL_LEFT,)
        );

        $style_hint = array(
            'font'             => array('bold' => false, 'size' => 10),
            'alignment'     => array('horizontal' => Alignment::HORIZONTAL_LEFT,),
            'color'         => array('rgb' => 'D3D3D3'),
        );
        $style_label = array(
            'font'             => array('bold' => false, 'size' => 10),
            'alignment'     => array('horizontal' => Alignment::HORIZONTAL_LEFT,),
            'color'         => array('rgb' => 'D3D3D3'),
            'borders'         => array('bottom' => array('borderStyle' => Border::BORDER_THIN))
        );

        $title = "Customer Template";

        $filename = PATH_CUSTOMER_EXCEL . "template-customer.xlsx";
        $column = 2;

        //========================================================================
        //1ST SHEET

        //TITLE
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle($title);
        $worksheet->setCellValue("A1", "Customer Code");
        $worksheet->getStyle("A1")->applyFromArray($style_title);
        $worksheet->setCellValue("B1", "Customer Name");
        $worksheet->getStyle("B1")->applyFromArray($style_title);
        $worksheet->setCellValue("C1", "Company Reg No");
        $worksheet->getStyle("C1")->applyFromArray($style_title);
        $worksheet->setCellValue("D1", "Customer Status");
        $worksheet->getStyle("D1")->applyFromArray($style_title);
        $worksheet->setCellValue("E1", "Account");
        $worksheet->getStyle("E1")->applyFromArray($style_title);
        $worksheet->setCellValue("F1", "Billing Cycle");
        $worksheet->getStyle("F1")->applyFromArray($style_title);
        $worksheet->setCellValue("G1", "Telephon");
        $worksheet->getStyle("G1")->applyFromArray($style_title);
        $worksheet->setCellValue("H1", "Fax");
        $worksheet->getStyle("H1")->applyFromArray($style_title);
        $worksheet->setCellValue("I1", "Billing Address");
        $worksheet->getStyle("I1")->applyFromArray($style_title);
        $worksheet->setCellValue("J1", "Remarks");
        $worksheet->getStyle("J1")->applyFromArray($style_title);

        $worksheet->setCellValue("K1", "Industry");
        $worksheet->getStyle("K1")->applyFromArray($style_title);
        $worksheet->setCellValue("L1", "Business Sector");
        $worksheet->getStyle("L1")->applyFromArray($style_title);
        $worksheet->setCellValue("M1", "Website");
        $worksheet->getStyle("M1")->applyFromArray($style_title);
        $worksheet->setCellValue("N1", "No. People");
        $worksheet->getStyle("N1")->applyFromArray($style_title);
        $worksheet->setCellValue("O1", "Sales Turnover");
        $worksheet->getStyle("O1")->applyFromArray($style_title);

        $worksheet->setCellValue("P1", "Sunsystem Version");
        $worksheet->getStyle("P1")->applyFromArray($style_title);
        $worksheet->setCellValue("Q1", "MSSQL Version");
        $worksheet->getStyle("Q1")->applyFromArray($style_title);
        $worksheet->setCellValue("R1", "QA Version");
        $worksheet->getStyle("R1")->applyFromArray($style_title);
        $worksheet->setCellValue("S1", "Windows Server Version");
        $worksheet->getStyle("S1")->applyFromArray($style_title);
        $worksheet->setCellValue("T1", "Software Info");
        $worksheet->getStyle("T1")->applyFromArray($style_title);
        $worksheet->setCellValue("U1", "Supporting Partner");
        $worksheet->getStyle("U1")->applyFromArray($style_title);
        $worksheet->setCellValue("V1", "Product Conversation");
        $worksheet->getStyle("V1")->applyFromArray($style_title);
        $worksheet->setCellValue("W1", "Company Policy");
        $worksheet->getStyle("W1")->applyFromArray($style_title);

        $worksheet->setCellValue("X1", "Primary Remote Access");
        $worksheet->getStyle("X1")->applyFromArray($style_title);
        $worksheet->setCellValue("Y1", "Secondary Remote Access");
        $worksheet->getStyle("Y1")->applyFromArray($style_title);
        $worksheet->setCellValue("Z1", "Primary Video Message");
        $worksheet->getStyle("Z1")->applyFromArray($style_title);
        $worksheet->setCellValue("AA1", "Secondary Video Message");
        $worksheet->getStyle("AA1")->applyFromArray($style_title);

        foreach (range('A', $worksheet->getHighestDataColumn()) as $columnID) {
            $worksheet->getColumnDimension($columnID)
                ->setAutoSize(true);
        }
        //========================================================================

        //========================================================================
        //2nd SHEET
        $spreadsheet->createSheet(1);
        $spreadsheet->setActiveSheetIndex(1);
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle('Customer Contact');

        //TITLE
        $worksheet->setCellValue("A1", "Customer Code");
        $worksheet->getStyle("A1")->applyFromArray($style_title);
        $worksheet->setCellValue("B1", "Full Name");
        $worksheet->getStyle("B1")->applyFromArray($style_title);
        $worksheet->setCellValue("C1", "Preferred Name");
        $worksheet->getStyle("C1")->applyFromArray($style_title);
        $worksheet->setCellValue("D1", "Designation");
        $worksheet->getStyle("D1")->applyFromArray($style_title);
        $worksheet->setCellValue("E1", "Salutation");
        $worksheet->getStyle("E1")->applyFromArray($style_title);
        $worksheet->setCellValue("F1", "Job Description");
        $worksheet->getStyle("F1")->applyFromArray($style_title);
        $worksheet->setCellValue("G1", "Department Group");
        $worksheet->getStyle("G1")->applyFromArray($style_title);
        $worksheet->setCellValue("H1", "Department");
        $worksheet->getStyle("H1")->applyFromArray($style_title);
        $worksheet->setCellValue("I1", "Report To");
        $worksheet->getStyle("I1")->applyFromArray($style_title);
        $worksheet->setCellValue("J1", "Position");
        $worksheet->getStyle("J1")->applyFromArray($style_title);
        $worksheet->setCellValue("K1", "Customer Contact Status");
        $worksheet->getStyle("K1")->applyFromArray($style_title);

        $worksheet->setCellValue("L1", "Email");
        $worksheet->getStyle("L1")->applyFromArray($style_title);
        $worksheet->setCellValue("M1", "Telephone");
        $worksheet->getStyle("M1")->applyFromArray($style_title);
        $worksheet->setCellValue("N1", "Mobile");
        $worksheet->getStyle("N1")->applyFromArray($style_title);
        $worksheet->setCellValue("O1", "Communication Preference");
        $worksheet->getStyle("O1")->applyFromArray($style_title);

        $worksheet->setCellValue("P1", "Hobbies");
        $worksheet->getStyle("P1")->applyFromArray($style_title);
        $worksheet->setCellValue("Q1", "Character");
        $worksheet->getStyle("Q1")->applyFromArray($style_title);
        $worksheet->setCellValue("R1", "Priority");
        $worksheet->getStyle("R1")->applyFromArray($style_title);

        foreach (range('A', $worksheet->getHighestDataColumn()) as $columnID) {
            $worksheet->getColumnDimension($columnID)
                ->setAutoSize(true);
        }

        //========================================================================

        //3rd SHEET
        $spreadsheet->createSheet(2);
        $spreadsheet->setActiveSheetIndex(2);
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle('Customer Branch');

        //TITLE
        $worksheet->setCellValue("A1", "Customer Code");
        $worksheet->getStyle("A1")->applyFromArray($style_title);
        $worksheet->setCellValue("B1", "Code");
        $worksheet->getStyle("B1")->applyFromArray($style_title);
        $worksheet->setCellValue("C1", "Name");
        $worksheet->getStyle("C1")->applyFromArray($style_title);
        $worksheet->setCellValue("D1", "Branch Type");
        $worksheet->getStyle("D1")->applyFromArray($style_title);

        $worksheet->setCellValue("E1", "Address");
        $worksheet->getStyle("E1")->applyFromArray($style_title);

        $worksheet->setCellValue("F1", "Web");
        $worksheet->getStyle("F1")->applyFromArray($style_title);
        $worksheet->setCellValue("G1", "Telephone");
        $worksheet->getStyle("G1")->applyFromArray($style_title);
        $worksheet->setCellValue("H1", "Fax");
        $worksheet->getStyle("H1")->applyFromArray($style_title);
        $worksheet->setCellValue("I1", "Email");
        $worksheet->getStyle("I1")->applyFromArray($style_title);
        $worksheet->setCellValue("J1", "Branch Contact");
        $worksheet->getStyle("J1")->applyFromArray($style_title);

        foreach (range('A', $worksheet->getHighestDataColumn()) as $columnID) {
            $worksheet->getColumnDimension($columnID)
                ->setAutoSize(true);
        }

        //========================================================================
        //4th SHEET

        $spreadsheet->createSheet(3);
        $spreadsheet->setActiveSheetIndex(3);
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle('Input Hint');

        //HINT
        $worksheet->setCellValue("A1", "PLEASE USE THE REQUIRED VALUE WHEN FILL THE COLUMN");
        $worksheet->getStyle("A1")->applyFromArray($style_header);
        $worksheet->setCellValue("A2", "For eSupport Primary Contact Department, please manunally edit in CCM after import into CCM. (Reason, it will take a long of time to develop checking maximum 2 contact in department for import function. By Mr Darren.)");
        $worksheet->getStyle("A2")->applyFromArray($style_title);

        //Customer Status
        $worksheet->setCellValue("A4", "Customer Status : ");
        $worksheet->getStyle("A4")->applyFromArray($style_title);
        $worksheet->setCellValue("A5", "Value");
        $worksheet->getStyle("A5")->applyFromArray($style_label);
        $worksheet->setCellValue("A6", "Active");
        $worksheet->getStyle("A6")->applyFromArray($style_hint);
        $worksheet->setCellValue("A7", "Inactive");
        $worksheet->getStyle("A7")->applyFromArray($style_hint);

        //Primary/Secondary Remote Access
        $worksheet->setCellValue("C4", "Primary/Secondary Remote Access : ");
        $worksheet->getStyle("C4")->applyFromArray($style_title);
        $worksheet->setCellValue("C5", "Value");
        $worksheet->getStyle("C5")->applyFromArray($style_label);
        $worksheet->setCellValue("C6", "Anydesk");
        $worksheet->getStyle("C6")->applyFromArray($style_hint);
        $worksheet->setCellValue("C7", "Teamviewer");
        $worksheet->getStyle("C7")->applyFromArray($style_hint);
        $worksheet->setCellValue("C8", "Webex");
        $worksheet->getStyle("C8")->applyFromArray($style_hint);
        $worksheet->setCellValue("C9", "Teams");
        $worksheet->getStyle("C9")->applyFromArray($style_hint);

        //Primary/Secondary Video Message
        $worksheet->setCellValue("E4", "Primary/Secondary Video Message : ");
        $worksheet->getStyle("E4")->applyFromArray($style_title);
        $worksheet->setCellValue("E5", "Value");
        $worksheet->getStyle("E5")->applyFromArray($style_label);
        $worksheet->setCellValue("E6", "Zoom");
        $worksheet->getStyle("E6")->applyFromArray($style_hint);
        $worksheet->setCellValue("E7", "Teams");
        $worksheet->getStyle("E7")->applyFromArray($style_hint);
        $worksheet->setCellValue("E8", "Skype");
        $worksheet->getStyle("E8")->applyFromArray($style_hint);
        $worksheet->setCellValue("E9", "Google Meet");
        $worksheet->getStyle("E9")->applyFromArray($style_hint);
        $worksheet->setCellValue("E10", "Jitsi");
        $worksheet->getStyle("E10")->applyFromArray($style_hint);
        $worksheet->setCellValue("E11", "Slack");
        $worksheet->getStyle("E11")->applyFromArray($style_hint);

        //Customer Contact Status
        $worksheet->setCellValue("G4", "Customer Contact Status : ");
        $worksheet->getStyle("G4")->applyFromArray($style_title);
        $worksheet->setCellValue("G5", "Value");
        $worksheet->getStyle("G5")->applyFromArray($style_label);
        $worksheet->setCellValue("G6", "Active");
        $worksheet->getStyle("G6")->applyFromArray($style_hint);
        $worksheet->setCellValue("G7", "Inactive");
        $worksheet->getStyle("G7")->applyFromArray($style_hint);

        //Salutation
        $worksheet->setCellValue("I4", "Salutation : ");
        $worksheet->getStyle("I4")->applyFromArray($style_title);
        $worksheet->setCellValue("I5", "Value");
        $worksheet->getStyle("I5")->applyFromArray($style_label);
        $worksheet->setCellValue("I6", "Mr");
        $worksheet->getStyle("I6")->applyFromArray($style_hint);
        $worksheet->setCellValue("I7", "Ms");
        $worksheet->getStyle("I7")->applyFromArray($style_hint);

        //Position
        $worksheet->setCellValue("K4", "Position: ");
        $worksheet->getStyle("K4")->applyFromArray($style_title);
        $worksheet->setCellValue("K5", "Value");
        $worksheet->getStyle("K5")->applyFromArray($style_label);
        $worksheet->setCellValue("K6", "License");
        $worksheet->getStyle("K6")->applyFromArray($style_hint);
        $worksheet->setCellValue("K7", "Finance");
        $worksheet->getStyle("K7")->applyFromArray($style_hint);
        $worksheet->setCellValue("K8", "IT");
        $worksheet->getStyle("K8")->applyFromArray($style_hint);
        $worksheet->setCellValue("K9", "Project");
        $worksheet->getStyle("K9")->applyFromArray($style_hint);
        $worksheet->setCellValue("K10", "NA");
        $worksheet->getStyle("K10")->applyFromArray($style_hint);

        //Branch Type
        $worksheet->setCellValue("M4", "Branch Type: ");
        $worksheet->getStyle("M4")->applyFromArray($style_title);
        $worksheet->setCellValue("M5", "Value");
        $worksheet->getStyle("M5")->applyFromArray($style_label);
        $worksheet->setCellValue("M6", "HQ");
        $worksheet->getStyle("M6")->applyFromArray($style_hint);
        $worksheet->setCellValue("M7", "Branch");
        $worksheet->getStyle("M7")->applyFromArray($style_hint);

        //========================================================================
        //check if directory exist if not then create
		$directory = PATH_CUSTOMER_EXCEL;
		if (!file_exists($directory)) {
			mkdir($directory, 1777, true);
		}

        // Write a new .xlsx file
		$writer = new Xlsx($spreadsheet);
		// Save the new .xlsx file
		$writer->save($filename);

        header('Content-Description: File Transfer');
        header('Content-Type: application/force-download');
        header('Content-Disposition: attachement; filename="' . basename($filename) . '"');
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($filename));
        ob_clean();
        flush();
        readfile($filename);
    }

    private function processCustomerUploadExcel()
    {
        $userId     = $_SESSION[SE_USERID];

        $id         = 0;
        $docNo      = 0;
        $result     = true;
        $no_doc     = 0;
        $err        = "";
        $reqDocReturn  = "";
        $newName = "";
        $statusSend = "";

        $sizeConfigured       = 500;
        $limitFilesize  = $sizeConfigured * 1024;   //5000kb

        if (isset($_FILES["uploadFileCustomer"])) {
            $resultTemp = true;
            $size       = $_FILES["uploadFileCustomer"]['size'][0];
            $fileName  = $_FILES["uploadFileCustomer"]["name"][0];

            $docfile =  basename($fileName);
            $pi      = pathinfo($docfile);
            $ext     = $pi['extension'];
            $exts    = array("xls", "xlsx");

            if (Util::validFileExt($ext, $exts) == false) {
                $err .= ", unsupported upload file type";
                $result &= false;
            }

            $docExts = array("xls", "xlsx");
            if (Util::validFileExt($ext, $docExts) && $size > $limitFilesize) {
                $err .= ", upload file over limit. Upload file size must be less than 5000KB.";
                $result &= false;
            }

            $directory = PATH_CUSTOMER_EXCEL . "temp-" . $userId . "/";
            $newName   = "customer_excel_doc_" . $userId . "." . $ext;
            $newfile   = $directory . $newName;
            $fileUpload = $_FILES["uploadFileCustomer"]['tmp_name'][0];

            if (!file_exists($directory)) {
                mkdir($directory, 1777, true);
            }

            $uploadResult = false;

            if ($result) {
                $uploadResult = move_uploaded_file($fileUpload, $newfile);
            }

            if ($uploadResult) {
                $reqDocReturn .= $userId . "|" . $newName . "|" . $docfile;
                $resultTemp = true;
            } else {
                $resultTemp = false;
				$reqDocReturn = $fileName;
                $err .= ", error in processing uploaded file";
            }

            $result &= $resultTemp;

        }

        if ($result) {
            $statusSend =  Status::Ok . "*" . $newName . "*" . $err;
        } else {
            $statusSend =  Status::Error . "*" . $reqDocReturn . "*" . $err;
        }
        
        echo $statusSend;
    }

    private function processCustomerReadExcel($params)
    {
        $values = $this->processReadExcel($_SESSION[SE_USERID], $params['new_name']);

        if ($values['status']) {
            $this->sendJsonResponse(Status::Ok, "Success import customer from excel", "", $this->type);
            return;
        } else {
            $this->sendJsonResponse(Status::Error, $values['mesg'], "", $this->type);
            return;
        }
    }

    private function processReadExcel($userId, $newName)
    {
        $clsCustomer = new CustomerClass($this->db);
        $clsContact  = new CustomerContactClass($this->db);
        $clsBranch  = new CustomerBranchClass($this->db);
        $this->db->beginTran();
        try {
            //get file
            $tmpfname = PATH_CUSTOMER_EXCEL . "temp-" . $userId . "/" . $newName;
            $excelReader = IOFactory::createReaderForFile($tmpfname);
            $spreadsheet = $excelReader->load($tmpfname);
            $data = array();
            $data2 = array();
            $data3 = array();

            $mesg = "Successfully import data from excel";
            $allclear = true;

            //GET 1ST SHEET DATA
		    $spreadsheet->setActiveSheetIndex(0);
			$worksheet = $spreadsheet->getActiveSheet();
            $highestColumm = $worksheet->getHighestColumn(); // e.g. "EL"
            $highestRow = $worksheet->getHighestRow();

            $highestColumm++;
            for ($row = 2; $row <= $highestRow; $row++) {
                $dataset = array();

                for ($column = 'A'; $column != $highestColumm; $column++) {
                    $dataset[] = $worksheet->getCell($column . $row)->getCalculatedValue();
                }

                if ($dataset != "") {
                    $data[] = array_filter($dataset);
                }
            }
            //================================================
            //GET 2ND SHEET DATA
		    $spreadsheet->setActiveSheetIndex(1);
			$worksheet = $spreadsheet->getActiveSheet();
            $highestColumm2 = $worksheet->getHighestColumn(); // e.g. "EL"
            $highestRow2 = $worksheet->getHighestRow();

            $highestColumm2++;
            for ($row2 = 2; $row2 <= $highestRow2; $row2++) {
                $dataset2 = array();

                for ($column2 = 'A'; $column2 != $highestColumm2; $column2++
                ) {
                    $dataset2[] = $worksheet->getCell($column2 . $row2)->getCalculatedValue();
                }

                if ($dataset2 != "") {
                    $data2[] = array_filter($dataset2);
                }
            }
            //================================================
            //GET 3RD SHEET DATA
		    $spreadsheet->setActiveSheetIndex(2);
			$worksheet = $spreadsheet->getActiveSheet();
            $highestColumm3 = $worksheet->getHighestColumn(); // e.g. "EL"
            $highestRow3 = $worksheet->getHighestRow();

            $highestColumm3++;
            for ($row3 = 2; $row3 <= $highestRow3; $row3++) {
                $dataset3 = array();

                for ($column3 = 'A';
                    $column3 != $highestColumm3;
                    $column3++
                ) {
                    $dataset3[] = $worksheet->getCell($column3 . $row3)->getCalculatedValue();
                }

                if ($dataset3 != "") {
                    $data3[] = array_filter($dataset3);
                }
            }
            if ($allclear) {
                //1ST SHEET
                //INSERT CUSTOMER AND ACCOUNT
                foreach (array_filter($data) as $value) {
                    $orgid      = $_SESSION[SE_ORGID];
                    $modifyby     = $_SESSION[SE_USERID];
                    $modifydate = date_create('now')->format('Y-m-d H:i:s');
                    $ws             = $_SESSION[SE_REMOTE_IP];

                    $cust_code              = $this->getParam($value, '0', "");
                    $cust_name              = $this->getParam($value, '1', "");
                    $coy_reg                = $this->getParam($value, '2', "");
                    $cust_status            = $this->getParam($value, '3', "");
                    $account                = $this->getParam($value, '4', "");
                    $bill_cycle             = $this->getParam($value, '5', "");
                    $telephone              = $this->getParam($value, '6', "");
                    $fax                    = $this->getParam($value, '7', "");
                    $bill_address           = $this->getParam($value, '8', "");
                    $remarks                = $this->getParam($value, '9', "");

                    $industry               = $this->getParam($value, '10', "");
                    $business_sector        = $this->getParam($value, '11', "");
                    $website                = $this->getParam($value, '12', "");
                    $no_people              = $this->getParam($value, '13', "");
                    $sales_turnover         = $this->getParam($value, '14', "");

                    $sun_ver                = $this->getParam($value, '15', "");
                    $mssql_ver              = $this->getParam($value, '16', "");
                    $qa_ver                 = $this->getParam($value, '17', "");
                    $winserver_ver          = $this->getParam($value, '18', "");
                    $software_info          = $this->getParam($value, '19', "");
                    $supporting_partner     = $this->getParam($value, '20', "");
                    $product_conv           = $this->getParam($value, '21', "");
                    $coy_policy             = $this->getParam($value, '22', "");

                    $primary_remote         = $this->getParam($value, '23', "");
                    $secondary_remote       = $this->getParam($value, '24', "");
                    $primary_video          = $this->getParam($value, '25', "");
                    $secondary_video        = $this->getParam($value, '26', "");

                    //customer status
					$cust_status_id = '1'; //open
					if(strtolower($cust_status)=="inactive") {
						$cust_status_id = '0';
					} else if(strtolower($cust_status)=="active") {
                        $cust_status_id = '1';
					}
                    
                    //primary remote
                    $primary_remote_id = '0'; //empty
                    if (strtolower($primary_remote) == "anydesk") {
                        $primary_remote_id = '1';
                    } else if (strtolower($primary_remote) == "teamviewer") {
                        $primary_remote_id = '2';
                    } else if (strtolower($primary_remote) == "webex") {
                        $primary_remote_id = '3';
                    } else if (strtolower($primary_remote) == "teams") {
                        $primary_remote_id = '4';
                    } 

                    //secondary remote
                    $secondary_remote_id = '0'; //empty
                    if (strtolower($secondary_remote) == "anydesk") {
                        $secondary_remote_id = '1';
                    } else if (strtolower($secondary_remote) == "teamviewer") {
                        $secondary_remote_id = '2';
                    } else if (strtolower($secondary_remote) == "webex") {
                        $secondary_remote_id = '3';
                    } else if (strtolower($secondary_remote) == "teams") {
                        $secondary_remote_id = '4';
                    }  

                    //primary video
                    $primary_video_id = '0'; //empty
                    if (strtolower($primary_video) == "zoom") {
                        $primary_video_id = '1';
                    } else if (strtolower($primary_video) == "teams") {
                        $primary_video_id = '2';
                    } else if (strtolower($primary_video) == "skype") {
                        $primary_video_id = '3';
                    } else if (strtolower($primary_video) == "google meet") {
                        $primary_video_id = '4';
                    } else if (strtolower($primary_video) == "jitsi") {
                        $primary_video_id = '5';
                    } else if (strtolower($primary_video) == "slack") {
                        $primary_video_id = '6';
                    } 

                    //secondary video
                    $secondary_video_id = '0'; //empty
                    if (strtolower($secondary_video) == "zoom") {
                        $secondary_video_id = '1';
                    } else if (strtolower($secondary_video) == "teams") {
                        $secondary_video_id = '2';
                    } else if (strtolower($secondary_video) == "skype") {
                        $secondary_video_id = '3';
                    } else if (strtolower($secondary_video) == "google meet") {
                        $secondary_video_id = '4';
                    } else if (strtolower($secondary_video) == "jitsi") {
                        $secondary_video_id = '5';
                    } else if (strtolower($secondary_video) == "slack") {
                        $secondary_video_id = '6';
                    } 

                    //====================================
                    //check if any required field is empty
                    $column = "";
                    $allstatus = true;

                    if ($cust_code == "") {
                        $allstatus = false;
                        $column = "Customer Code";
                    }

                    if ($cust_name == "") {
                        $allstatus = false;
                        $column = "Customer Name";
                    }

                    //===========
                    //check if acc code not exist, check if any other required field is empty
                    if (!$allstatus) {
                        $this->db->rollbackTran();

                        $ret_data = array();
                        $ret_data['mesg'] = "There is an empty value inside " . $column . " field (Customer Sheet)";
                        $ret_data['status'] = false;

                        return $ret_data;
                    }
                    //=================================

                    $datas = array();
                    $datas[] = $this->db->fieldValue(CustomerTable::C_CODE, strtoupper($cust_code ?? ""));
                    $datas[] = $this->db->fieldValue(CustomerTable::C_NAME, strtoupper($cust_name ?? ""));
                    $datas[] = $this->db->fieldValue(CustomerTable::C_COMPANY_REG_NO, $coy_reg);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_TELEPHONE, $telephone);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_FAX, $fax);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_STATUS, $cust_status_id);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_CUS_ACC, $account);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_BILLING_CYCLE, $bill_cycle);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_ADDRESS, strtoupper($bill_address ?? ""));
                    $datas[] = $this->db->fieldValue(CustomerTable::C_REMARKS, $remarks);

                    $datas[] = $this->db->fieldValue(CustomerTable::C_INDUSTRY, $industry);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_BUSINESS_SECTOR, $business_sector);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_WEB, $website);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_NUM_PEOPLE, $no_people);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_TURN_OVER, $sales_turnover);

                    $datas[] = $this->db->fieldValue(CustomerTable::C_SUN_VERSION, $sun_ver);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_MSSQL_VERSION, $mssql_ver);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_QA_VERSION, $qa_ver);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_WIN_SERVER_VERSION, $winserver_ver);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_SOFTWARE_INFO, $software_info);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_SUPP_PARTNER, $supporting_partner);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_PROD_CONVERSATION, $product_conv);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_COMP_POLICY, $coy_policy);

                    $datas[] = $this->db->fieldValue(CustomerTable::C_PRIMARY_REMOTE, $primary_remote_id);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_SECOND_REMOTE, $secondary_remote_id);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_PRIMARY_MESG, $primary_video_id);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_SECOND_MESG, $secondary_video_id);


                    $datas[] = $this->db->fieldValue(CustomerTable::C_WS_ID, $ws);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_MODIFY_BY, $modifyby);
                    $datas[] = $this->db->fieldValue(CustomerTable::C_MODIFY_DATE, $modifydate);

                    $existing_cust = $clsCustomer->getExistingRecords($cust_code);
                    if ($existing_cust == "") {
                        //add new
                        $datas[] = $this->db->fieldValue(CustomerTable::C_CREATE_BY, $modifyby);
                        $datas[] = $this->db->fieldValue(CustomerTable::C_CREATE_DATE, $modifydate);
                        $id = $clsCustomer->addRecord($datas);
                    } else {
                        $id = $existing_cust[0][CustomerTable::C_ID];
                        $clsCustomer->updateRecord($id, $datas);
                    }
                }
                //=========================================

                //=========================================
                //2ND SHEET
                //INSERT CUSTOMER CONTACT
                foreach (array_filter($data2) as $value2) {
                    $orgid      = $_SESSION[SE_ORGID];
                    $modifyby   = $_SESSION[SE_USERID];
                    $modifydate = date_create('now')->format('Y-m-d H:i:s');
                    $ws         = $_SESSION[SE_REMOTE_IP];

                    $cust_code          = $this->getParam($value2, '0', "");
                    $full_name          = $this->getParam($value2, '1', "");
                    $preferred_name     = $this->getParam($value2, '2', "");
                    $designation        = $this->getParam($value2, '3', "");
                    $salutation         = $this->getParam($value2, '4', "");
                    $job_desc           = $this->getParam($value2, '5', "");
                    $dept               = $this->getParam($value2, '6', "");
                    $report_to          = $this->getParam($value2, '7', "");
                    $dept_group_id      = $this->getParam($value2, '8', "");
                    $contact_status     = $this->getParam($value2, '9', "");

                    $email              = $this->getParam($value2, '10', "");
                    $phone              = $this->getParam($value2, '11', "");
                    $mobile             = $this->getParam($value2, '12', "");
                    $com_pref           = $this->getParam($value2, '13', "");

                    $hobbies            = $this->getParam($value2, '14', "");
                    $character          = $this->getParam($value2, '15', "");
                    $priority           = $this->getParam($value2, '16', "");

                    //customer contact status
					$contact_status_id = '1'; //open
					if(strtolower($contact_status)=="inactive") {
						$contact_status_id = '0';
					} else if(strtolower($contact_status)=="active") {
                        $contact_status_id = '1';
					}

                    //salutation
                    $salutation_id = '0'; //empty
                    if (strtolower($salutation) == "mr" || strtolower($salutation) == "mr.") {
                        $salutation_id = '1';
                    } else if (strtolower($salutation) == "ms" || strtolower($salutation) == "ms.") {
                        $salutation_id = '2';
                    }

                    //====================================
                    //check if any required field is empty
                    $column = "";
                    $allstatus = true;

                    if ($cust_code == "") {
                        $allstatus = false;
                        $column = "Customer Code";
                    }

                    if ($full_name == "") {
                        $allstatus = false;
                        $column = "Full Name";
                    }

                    if (!$allstatus) {
                        $this->db->rollbackTran();

                        $ret_data = array();
                        $ret_data['mesg'] = "There is an empty value inside " . $column . " field (Customer Contact Sheet)";
                        $ret_data['status'] = false;

                        return $ret_data;
                    }
                    //=================================

                    //=================================
                    //get ID by the code
                    $column = "";
                    $allid = true;

                    $cust_id = $clsCustomer->getIdByCode($cust_code);
                    if ($cust_id == "") {
                        $allid = false;
                        $column = "Customer Code";
                    }

                    if (!$allid) {
                        $this->db->rollbackTran();

                        $ret_data = array();
                        $ret_data['mesg'] = "There is a mismatch '".$cust_code."' value on " . $column . " field with the ID inside the system (Customer Contact Sheet)";
                        $ret_data['status'] = false;

                        return $ret_data;
                    }
                    //=================================

                    $existing_cust = $clsCustomer->getExistingRecords($cust_code);

                    if (!is_null($existing_cust) && $existing_cust != "") {
                        //update customer contact
                        $this->insertToCustomerContactTable($cust_id, $full_name, $preferred_name, $designation, $salutation_id, $job_desc, $dept, $report_to, $dept_group_id, $contact_status_id, 
                                                            $email, $phone, $mobile, $com_pref, $hobbies, $character, $priority);
                        //====================
                        // $this->db->commitTran();
                    } else {
                        $this->db->rollbackTran();

                        //customer not exist
                        $ret_data = array();
                        $ret_data['mesg'] = "Sorry, the customer code " . $cust_code . " is not exist in the system (Customer Contact Sheet)";
                        $ret_data['status'] = false;

                        return $ret_data;
                    }

                }
                //=========================================
                //3RD SHEET
                //INSERT CUSTOMER DELIVERY
                foreach (array_filter($data3) as $value3) {
                    $orgid          = $_SESSION[SE_ORGID];
                    $modifyby       = $_SESSION[SE_USERID];
                    $modifydate     = date_create('now')->format('Y-m-d H:i:s');
                    $ws             = $_SESSION[SE_REMOTE_IP];

                    $cust_code      = $this->getParam($value3, '0', "");
                    $code           = $this->getParam($value3, '1', "");
                    $name           = $this->getParam($value3, '2', "");
                    $branch_type    = $this->getParam($value3, '3', "");
                    $address        = $this->getParam($value3, '4', "");

                    $web            = $this->getParam($value3, '5', "");
                    $branch_phone   = $this->getParam($value3, '6', "");
                    $branch_fax     = $this->getParam($value3, '7', "");
                    $branch_email   = $this->getParam($value3, '8', "");
                    $branch_contact = $this->getParam($value3, '9', "");

                    //====================================
                    //check if any required field is empty
                    $column = "";
                    $allstatus = true;

                    if ($cust_code == "") {
                        $allstatus = false;
                        $column = "Customer Code";
                    }

                    if ($name == "") {
                        $allstatus = false;
                        $column = "Name";
                    }

                    if (!$allstatus) {
                        $this->db->rollbackTran();

                        $ret_data = array();
                        $ret_data['mesg'] = "There is an empty value inside " . $column . " field (Customer Branch Sheet).";
                        $ret_data['status'] = false;

                        return $ret_data;
                    }
                    //=================================

                    //=================================
                    //get ID by the code
                    $column = "";
                    $allid = true;

                    $cust_id = $clsCustomer->getIdByCode($cust_code);
                    if ($cust_id == "") {
                        $allid = false;
                        $column = "Customer Code";
                    }

                    if (!$allid) {
                        $this->db->rollbackTran();

                        $ret_data = array();
                        $ret_data['mesg'] = "There is a mismatch '".$cust_code."' value on " . $column . "  field with the ID inside the system (Customer Branch Sheet)";
                        $ret_data['status'] = false;

                        return $ret_data;
                    }
                    //=================================

                    //get contact id by contact name
                    $branch_contact_id = $clsContact->getIdByContactName($cust_id, $branch_contact);
                    if ($branch_contact_id == "") {
                        $branch_contact_id = 0;
                    }

                    //get branch type
                    $branch_type_id = 0;
                    if (strtolower($branch_type) == "hq") {
                        $branch_type_id = 1;
                    } else if (strtolower($branch_type) == "branch") {
                        $branch_type_id = 2;
                    }

                    $existing_cust = $clsCustomer->getExistingRecords($cust_code);
                    if (!is_null($existing_cust) && $existing_cust != "") {
                        //update customer contact
                        $this->insertToCustomerBranchTable($cust_id, $code, $name, $branch_type_id, $address, $web, $branch_phone, $branch_fax, $branch_email, $branch_contact_id);
                        //====================

                        // $this->db->commitTran();
                    } else {
                        $this->db->rollbackTran();

                        //customer not exist
                        $ret_data = array();
                        $ret_data['mesg'] = "Sorry, the customer code " . $cust_code . " is not exist in the system (Customer Branch Sheet)";
                        $ret_data['status'] = false;

                        return $ret_data;
                    }

                }
            }

            $this->db->commitTran();
        } catch (Exception $e) {
            $this->db->rollbackTran();
            $mesg = "Error in database Operation";
            $allclear = false;
        }

        $ret_data = array();
        $ret_data['mesg'] = $mesg;
        $ret_data['status'] = $allclear;

        return $ret_data;
    }

    private function insertToCustomerContactTable($cust_id, $full_name, $preferred_name, $designation, $salutation_id, $job_desc, $dept, $report_to, $dept_group_id, $contact_status_id, 
                                                $email, $phone, $mobile, $com_pref, $hobbies, $character, $priority) {

        $clsContact = new CustomerContactClass($this->db);

        $orgid      = $_SESSION[SE_ORGID];
        $modifyby   = $_SESSION[SE_USERID];
        $modifydate = date_create('now')->format('Y-m-d H:i:s');
        $ws         = $_SESSION[SE_REMOTE_IP];

        $datas = array();
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_ID, $cust_id);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_NAME, strtoupper($full_name ?? ""));
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_PREFER_NAME, strtoupper($preferred_name ?? ""));
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_DESIGNATION, $designation);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_SALUTATION, $salutation_id);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_JOB_DESC, $job_desc);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_DEPT, $dept);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_REPORT_TO, $report_to);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_DEPT_GROUP, $dept_group_id);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_STATUS, $contact_status_id);

        $datas[] = $this->db->fieldValue(CustomerContactTable::C_EMAIL, $email);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_TELEPHONE, $phone);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_MOBILE, $mobile);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_COMM_PREF, $com_pref);
        
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_HOBBIES, $hobbies);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_CHARACTER, $character);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_PRIORITY,$priority);
        //no need to insert esupport fields for import as it will take too long to develop

        $datas[] = $this->db->fieldValue(CustomerContactTable::C_WS_ID, $ws);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_MODIFY_BY, $modifyby);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_MODIFY_DATE, $modifydate);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_CREATE_BY, $modifyby);
        $datas[] = $this->db->fieldValue(CustomerContactTable::C_CREATE_DATE, $modifydate);

        $existing_contact = $clsContact->getExistingRecords($cust_id, $full_name);
        if ($existing_contact == "") {
            $clsContact->addRecord($datas);
        } else {
            $id = $existing_contact[0][CustomerContactTable::C_CONTACT_ID];
            $clsContact->updateRecord($id, $datas);
        }

    }

    private function insertToCustomerBranchTable($cust_id, $code, $name, $branch_type_id, $address, $web, $branch_phone, $branch_fax, $branch_email, $branch_contact_id) {
        
        $clsBranch = new CustomerBranchClass($this->db);

        $orgid      = $_SESSION[SE_ORGID];
        $modifyby   = $_SESSION[SE_USERID];
        $modifydate = date_create('now')->format('Y-m-d H:i:s');
        $ws         = $_SESSION[SE_REMOTE_IP];

        $datas = array();
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_ID, $cust_id);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CODE, $code);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_NAME, strtoupper($name ?? ""));
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_TYPE, $branch_type_id);
        
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_ADD, strtoupper($address ?? ""));
        
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_WEB, $web);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_TELEPHONE, $branch_phone);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_FAX, $branch_fax);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_EMAIL, $branch_email);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CONTACT, $branch_contact_id);

        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_WS_ID, $ws);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_MODIFY_BY, $modifyby);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_MODIFY_DATE, $modifydate);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CREATE_BY, $modifyby);
        $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CREATE_DATE, $modifydate);

        $existing_branch = $clsBranch->getExistingRecords($cust_id, $code);
        if ($existing_branch == "") {
            $clsBranch->addRecord($datas);
        } else {
            $id = $existing_branch[0][CustomerBranchTable::C_BRANCH_ID];
            $clsBranch->updateRecord($id, $datas);
        }
    }
	//=============================================================================================

    private function getCustomerSearchList($params) {
        $clsCust = new CustomerClass($this->db);

        try {
            $rows = $clsCust->getCustomerSuggest();

            $dataReturn = array();
            $dataReturn['idx'] = isset($params['idx']) ? $params['idx'] : -1;

            $data = [];
            if (!is_null($rows)) {
                foreach ($rows as $row) {
                    if (trim($row[CustomerTable::C_CODE]) != "") {
                        array_push($data, trim("" . $row[CustomerTable::C_CODE] . " - " . $row[CustomerTable::C_NAME] . ""));
                    }
                }

                $dataReturn['list'] = $data;
                $this->sendJsonResponse(Status::Ok, "", $dataReturn, $this->type);
            } else {
                $dataReturn['list'] = $data;
                $this->sendJsonResponse(Status::Error, "", $dataReturn, $this->type);
            }
        } catch (Exception $e) {
            Log::write('[getCustomerSearchList]' . $e->getMessage());
            $this->sendJsonResponse(Status::Error, "", "", $this->type);
        }
    }
}
