<?php
include (PATH_CONTROLLERS . "Common.php") ;
require_once(PATH_MODELS . "prospect/ProspectClass.php");
require_once(PATH_MODELS . "prospect/ProspectContactClass.php");
require_once(PATH_MODELS . "prospect/ProspectConversationClass.php");
require_once(PATH_MODELS . "prospect/PersonContactClass.php");
require_once(PATH_MODELS . "admin/IndustryClass.php");
require_once(PATH_MODELS . "admin/BusinessSectorClass.php");
require_once(PATH_MODELS . "admin/BusinessNatureClass.php");
require_once(PATH_MODELS . "admin/SoftwareClass.php");
require_once(PATH_MODELS . "admin/UserGroupClass.php");
require_once(PATH_MODELS . "admin/UserRight.php");
// require_once (PATH_MODELS . "prospect/ProspectDocumentClass.php") ;

// require_once(PATH_MODELS . "customer/CustomerClass.php");
// require_once(PATH_MODELS . "customer/CustomerContactClass.php");
// require_once(PATH_MODELS . "customer/CustomerBranchClass.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\Style\Fill;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Telemarketing extends GLBase {
	private $type = "" ;
	private $user ;
    private $rights;

	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::TelemarketingView:
				case Command::TelemarketingAccountView:
					$this->getView($params) ;
					break ;
				case Command::TelemarketingListView :
					$this->getSuspectList($params) ;
					break ;
				case REQ_ADD :
					$this->addSuspectContact($params) ;
					break ;
				case REQ_UPDATE :
					$this->updateSuspectContact($params) ;
					break ;
				case Command::SuspectConversationHistory :
					$this->getConversationHistory($params) ;
					break ;
				case Command::PersonContactDetail :
					$this->getPersonContact($params) ;
					break ;
				case Command::UpdateCallScript :
					$this->updateCallScript($params) ;
					break ;
				case "C_DOWNLOAD_FOLLOW_UP" :
					$this->downloadFollowUp($params) ;
					break ;
                //Follow Up
                //======================
                case Command::FollowUpListView :
					$this->getFollowUpList($params) ;
					break ;
                //Import Excel
                //======================
				case "C_SUSPECT_UPLOAD_EXCEL":
					$this->processSuspectUploadExcel();
					break ;
                case "C_SUSPECT_READ_EXCEL":
                	$this->processSuspectReadExcel($params);
                	break;
                case "C_DOWNLOAD_SUSPECT_EXCEL_TEMPLATE":
                    $this->getTemplate();
                    break;
                //Import Excel
                //======================
                case "C_EXPORT_ALL_CONTACT":
                    $this->exportAllContact();
                    break;
                case "C_EXPORT_NEW_CONTACT":
                    $this->exportNewContact();
                    break;
                case "C_END_OF_ACTION":
                    $this->endOfAction($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) {
        //the Account entry point reuses this screen without the follow up table
        $show_followup = ($this->type != Command::TelemarketingAccountView) ;
        $head_title    = $show_followup ? "Entity" : "Account" ;

		ob_start() ;
		include (PATH_VIEWS . "telemarketing/TelemarketingView.php") ;
        
        $action_menu = '';
        $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='showImportSuspect()'>".
                                        "<span>Import</span>".
                                    "</a>".
                                    "<a href='javascript:' onclick='showExportSuspect()'>".
                                        "<span>Export</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->getProspectContactOption();
        $dataReturn['headTitle']  = $head_title ;
		$this->sendJsonResponse(Status::Ok,$head_title,$dataReturn,$this->type);
	}

    private function updateSuspectContact($params){
        $clsProspect      = new ProspectClass($this->db);
        $clsContact       = new ProspectContactClass($this->db);
        $clsConversation  = new ProspectConversationClass($this->db);
        $clsPersonContact = new PersonContactClass($this->db);
        
        $modifyby    = $_SESSION[SE_USERID];
        $modifydate  = date_create('now')->format('Y-m-d H:i:s');
        $ws          = $_SESSION[SE_REMOTE_IP];

        $id          = $params['id'];
        $row_header  = $params['suspect_header'];
        $rows_detail = $params['contact_detail'];

        // $array_delete = $row_header['deleted_contact'];
        $clsContact->deleteSuspectContactByArray($row_header['deleted_contact']);
    
        $data_header   = array() ;
        $data_header[] = $this->db->fieldValue(ProspectTable::C_FLAG,$row_header['account_flag']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_NAME,$row_header['company_name']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_TELEPHONE,$row_header['telephone']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_TELEPHONE2,$row_header['telephone2']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_WEBSITE,$row_header['website']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_INDUSTRY,$row_header['industry']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_BUSINESS_SECTOR,$row_header['business_sector']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_BUSINESS_NATURE,$row_header['business_nature']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_TOTAL_EMPLOYEE,$row_header['total_empolyee']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_BUSINESS_DESC,$row_header['business_desc']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE,$row_header['acc_software']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE_C,$row_header['acc_software_c']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE_N,$row_header['acc_software_n']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE_USER,$row_header['acc_software_user']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_SOFTWARE,$row_header['sales_software']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_SOFTWARE_C,$row_header['sales_software_c']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_SOFTWARE_N,$row_header['sales_software_n']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_SOFTWARE_USER,$row_header['sales_software_user']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE,$row_header['other_software']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE_C,$row_header['other_software_c']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE_N,$row_header['other_software_n']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE_USER,$row_header['other_software_user']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_PROPOSAL_1,$row_header['proposal_1']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_PROPOSAL_2,$row_header['proposal_2']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SUBSIDIARY_LOCATION,$row_header['subsidiary_location']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SUBSIDIARY_ENTITY,$row_header['subsidiary_entity']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_COMPANY_STATUS,$row_header['company_status']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_STATUS,$row_header['sales_status']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_STATUS2,$row_header['sales_status2']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_COMPANY_EMAIL,$row_header['company_email']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_LINKEDIN_WEBSITE,$row_header['company_linkedin']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SOURCES,$row_header['company_source']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_STATE,$row_header['company_state']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_WS_ID,$ws);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_MODIFY_BY,$modifyby);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_MODIFY_DATE,$modifydate);

        $clsProspect->updateRecord($id, $data_header);

        if(!is_null($rows_detail) && count($rows_detail) > 0){
            foreach($rows_detail as $row_detail){
                $person_id  = $row_detail['person_id'];
                $contact_id = $row_detail['contact_id'];

                $datas_person   = array();
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_FULL_NAME,$this->getParam($row_detail,'full_name',""));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_NAME,$this->getParam($row_detail, 'prefer_name', ""));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_TELEPHONE,$this->getParam($row_detail,'telephone',""));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_EMAIL,$this->getParam($row_detail,'email',""));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_PROMOTION,$this->getParamInt($row_detail,'email_promo',0));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_WHATSAPP,$this->getParamInt($row_detail,'prefer_whatsapp',0));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_TELEGRAM,$this->getParamInt($row_detail,'prefer_telegram',0));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_EMAIL,$this->getParamInt($row_detail,'prefer_email',0));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_TELEPHONE,$this->getParamInt($row_detail,'prefer_telephone',0));

                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MOONCAKE,$this->getParam($row_detail,'mooncake',''));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_XMAS_CARD,$this->getParam($row_detail,'xmas_card',''));
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CNY,$this->getParam($row_detail,'cny',''));
    
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_WS_ID,$ws);
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MODIFY_BY,$modifyby);
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MODIFY_DATE,$modifydate);
                
                if($person_id != '-1'){
                    $clsPersonContact->updateRecord($person_id, $datas_person);
                }else{
                    $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CREATE_BY,$modifyby);
                    $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CREATE_DATE,$modifydate);
                    $person_id = $clsPersonContact->addRecord($datas_person);
                }

                $datas = array() ;                
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_ID,$id);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_SEQUENCE,$this->getParamInt($row_detail,'sequence',0));
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_PERSON_CONTACT,$person_id);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_DESIGNATION,$this->getParam($row_detail,'designation',""));
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_REPORT_TO, $this->getParam($row_detail,'report_to',""));
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_ACTION_DATE, $this->getParam($row_detail,'action_date',NULL_DATE));
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_BEST_TIME, $this->getParam($row_detail,'best_time','00:00'));
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_LUNCH_TIME, $this->getParam($row_detail,'lunch_time',''));
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_ASSIGN_TO,$this->getParam($row_detail,'assign_to',""));
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_NEXT_ACTION,$this->getParam($row_detail,'next_action',""));
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_WS_ID,$ws);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_MODIFY_BY,$modifyby);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_MODIFY_DATE,$modifydate);
                
                if($contact_id != '-1'){
                    $clsContact->updateRecord($contact_id, $datas);
                }else{
                    $datas[] = $this->db->fieldValue(ProspectContactTable::C_CREATE_BY,$modifyby);
                    $datas[] = $this->db->fieldValue(ProspectContactTable::C_CREATE_DATE,$modifydate);
                    $contact_id = $clsContact->addRecord($datas);
                }
                
                if($row_detail['conversation'] != ""){
                    $datas_conversation   = array();
                    $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_ID,$id);
                    $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_CONTACT_ID,$contact_id);
                    $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_DESC,$this->getParam($row_detail,'conversation',""));
                    
                    $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_CREATE_BY,$modifyby);
                    $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_CREATE_DATE,$modifydate);
                    $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_MODIFY_BY,$modifyby);
                    $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_MODIFY_DATE,$modifydate);
    
                    $clsConversation->addRecord($datas_conversation);
                }
            }
        }

        $datas = array() ;
        $datas[] = $this->db->fieldValue(ProspectTable::C_ACTIVE_STATUS,ProspectCompanyStatus::Assigned) ;
        $clsProspect->updateRecord($id,$datas);

        unset($clsContact);
        $this->sendJsonResponse(Status::Ok, "Successfully insert suspect contact into database.", '', $this->type);
    }

    private function addSuspectContact($params){
        $clsProspect      = new ProspectClass($this->db);
        $clsContact       = new ProspectContactClass($this->db);
        $clsConversation  = new ProspectConversationClass($this->db);
        $clsPersonContact = new PersonContactClass($this->db);
        
        $modifyby    = $_SESSION[SE_USERID];
        $modifydate  = date_create('now')->format('Y-m-d H:i:s');
        $ws          = $_SESSION[SE_REMOTE_IP];

        // $id          = $params['id'];
        $row_header  = $params['suspect_header'];
        $rows_detail = $params['contact_detail'];
    
        $data_header   = array() ;
        $data_header[] = $this->db->fieldValue(ProspectTable::C_FLAG,$row_header['account_flag']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_NAME,$row_header['company_name']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_TELEPHONE,$row_header['telephone']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_TELEPHONE2,$row_header['telephone2']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_WEBSITE,$row_header['website']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_INDUSTRY,$row_header['industry']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_BUSINESS_SECTOR,$row_header['business_sector']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_TOTAL_EMPLOYEE,$row_header['total_empolyee']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_BUSINESS_DESC,$row_header['business_desc']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_BUSINESS_NATURE,$row_header['business_nature']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE,$row_header['acc_software']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE_C,$row_header['acc_software_c']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE_N,$row_header['acc_software_n']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE_USER,$row_header['acc_software_user']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_SOFTWARE,$row_header['sales_software']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_SOFTWARE_C,$row_header['sales_software_c']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_SOFTWARE_N,$row_header['sales_software_n']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_SOFTWARE_USER,$row_header['sales_software_user']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE,$row_header['other_software']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE_C,$row_header['other_software_c']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE_N,$row_header['other_software_n']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE_USER,$row_header['other_software_user']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_PROPOSAL_1,$row_header['proposal_1']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_PROPOSAL_2,$row_header['proposal_2']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SUBSIDIARY_LOCATION,$row_header['subsidiary_location']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SUBSIDIARY_ENTITY,$row_header['subsidiary_entity']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_STATUS,$row_header['sales_status']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SALES_STATUS2,$row_header['sales_status2']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_COMPANY_STATUS,$row_header['company_status']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_COMPANY_EMAIL,$row_header['company_email']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_LINKEDIN_WEBSITE,$row_header['company_linkedin']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_SOURCES,$row_header['company_source']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_STATE,$row_header['company_state']);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_WS_ID,$ws);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_MODIFY_BY,$modifyby);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_MODIFY_DATE,$modifydate);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_CREATE_BY,$modifyby);
        $data_header[] = $this->db->fieldValue(ProspectTable::C_CREATE_DATE,$modifydate);

        $id = $clsProspect->addRecord($data_header);

        foreach($rows_detail as $row_detail){
            $person_id = $row_detail['person_id'];
            
            $datas_person   = array();
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_FULL_NAME,$this->getParam($row_detail,'full_name',""));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_NAME,$this->getParam($row_detail, 'prefer_name', ""));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_TELEPHONE,$this->getParam($row_detail,'telephone',""));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_EMAIL,$this->getParam($row_detail,'email',""));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_PROMOTION,$this->getParamInt($row_detail,'email_promo',0));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_WHATSAPP,$this->getParamInt($row_detail,'prefer_whatsapp',0));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_TELEGRAM,$this->getParamInt($row_detail,'prefer_telegram',0));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_EMAIL,$this->getParamInt($row_detail,'prefer_email',0));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_TELEPHONE,$this->getParamInt($row_detail,'prefer_telephone',0));

            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MOONCAKE,$this->getParam($row_detail,'mooncake',''));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_XMAS_CARD,$this->getParam($row_detail,'xmas_card',''));
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CNY,$this->getParam($row_detail,'cny',''));

            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_WS_ID,$ws);
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MODIFY_BY,$modifyby);
            $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MODIFY_DATE,$modifydate);
            
            if($person_id == "-1"){
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CREATE_BY,$modifyby);
                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CREATE_DATE,$modifydate);
                $person_id = $clsPersonContact->addRecord($datas_person);
            }else{
                $clsPersonContact->updateRecord($person_id, $datas_person);
            }

            $datas = array() ;
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_ID,$id);
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_PERSON_CONTACT,$person_id);
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_SEQUENCE,$this->getParamInt($row_detail,'sequence',0));
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_DESIGNATION,$this->getParam($row_detail,'designation',""));
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_REPORT_TO, $this->getParam($row_detail,'report_to',""));
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_ACTION_DATE, $this->getParam($row_detail,'action_date',NULL_DATE));
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_BEST_TIME, $this->getParam($row_detail,'best_time','00:00'));
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_LUNCH_TIME, $this->getParam($row_detail,'lunch_time',''));
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_ASSIGN_TO,$this->getParam($row_detail,'assign_to',""));
            $datas[] = $this->db->fieldValue(ProspectContactTable::C_NEXT_ACTION,$this->getParam($row_detail,'next_action',""));

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

            if($row_detail['conversation'] != ""){
                $datas_conversation   = array();
                $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_ID,$id);
                $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_CONTACT_ID,$contact_id);
                $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_DESC,$this->getParam($row_detail,'conversation',""));
                
                $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_CREATE_BY,$modifyby);
                $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_CREATE_DATE,$modifydate);
                $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_MODIFY_BY,$modifyby);
                $datas_conversation[] = $this->db->fieldValue(ProspectConversationTable::C_MODIFY_DATE,$modifydate);

                $clsConversation->addRecord($datas_conversation);
            }
        }

        $datas = array() ;
        $datas[] = $this->db->fieldValue(ProspectTable::C_ACTIVE_STATUS,ProspectCompanyStatus::Assigned) ;
        $clsProspect->updateRecord($id,$datas);

        unset($clsContact);
        $this->sendJsonResponse(Status::Ok, "Successfully insert suspect contact into database.", '', $this->type);
    }

    private function getSuspectList($params) {
        $clsProspect       = new ProspectClass($this->db);
        $clsPersonContact  = new PersonContactClass($this->db);
    
        $clsGroup = new UserGroupClass($this->db);
		$clsRight = new UserRight();

        $row_group = $clsGroup->getRecord($_SESSION[SE_USERGROUP]);
		
		if (is_null($row_group[UserGroupTable::C_RIGHTS]))
			$clsRight->initRights();
		else {
			$clsRight->toRights($row_group[UserGroupTable::C_RIGHTS]);
		}
		$rights = $clsRight->getAdminRights();
        
        $created_by  = $_SESSION[SE_USERID];
        if ($rights[UserRight::C_MARKETING_VIEW_OTHERS][UserRight::C_ENABLE] == 1 || $rights[UserRight::C_SALES_VIEW_OTHERS][UserRight::C_ENABLE] == 1) {
            $created_by =  0;
        }
        $rows            = $clsProspect->getSuspectAndContactList($params, $created_by);
        $rows_person     = $clsPersonContact->getPersonNameList();
        
        $lists                  = [];
        $lists_person           = [];
        $array_comp_detail      = array();
        
        if (!is_null($rows) && count($rows) > 0) {

            $company_status = ['Inactive', "Active"] ;
            $sales_status   = ['None', "Account"] ;
            $sales_status2  = ['Raw', "Suspect", "Prospect", "Customer"] ;
            $status_color   = ['red', "brown", "orange", "green"] ;

            // create lists wihtout duplicate prospect id
            $current_id = "";
            foreach ($rows as $key=>$row) {
                $action_date = "";
                if($row[ProspectContactTable::C_ACTION_DATE] != NULL_DATE.'.000'){
                    $action_date = date_create($row[ProspectContactTable::C_ACTION_DATE] ?? NULL_DATE)->format('Y-m-d');
                }
                
                //nchar columns come back space padded, so trim before matching an option value
                $best_time  = trim($row[ProspectContactTable::C_BEST_TIME] ?? "");
                $lunch_time = trim($row[ProspectContactTable::C_LUNCH_TIME] ?? "");

                $comp_detail            = array();
                if($row[ProspectContactTable::C_CONTACT_ID] != null){
                    $comp_detail = [
                        "id"                => $row[ProspectContactTable::C_CONTACT_ID],
                        "sequence"          => $row[ProspectContactTable::C_SEQUENCE],
                        "designation"       => $row[ProspectContactTable::C_DESIGNATION],
                        "person_id"         => $row[PersonContactTable::C_ID],
                        "full_name"         => $row[PersonContactTable::C_FULL_NAME],
                        "prefer_name"       => $row[PersonContactTable::C_PREFER_NAME],
                        "telephone"         => $row[PersonContactTable::C_TELEPHONE],
                        "email"             => $row[PersonContactTable::C_EMAIL],
                        "report_to"         => $row[ProspectContactTable::C_REPORT_TO],
                        "next_action"       => $row[ProspectContactTable::C_NEXT_ACTION],
                        "action_date"       => $action_date,
                        "email_promo"       => ($row[PersonContactTable::C_PREFER_PROMOTION] == 1) ? "checked" : "",
                        "prefer_phone"      => ($row[PersonContactTable::C_PREFER_TELEPHONE] == 1) ? "checked" : "",
                        "prefer_whatsapp"   => ($row[PersonContactTable::C_PREFER_WHATSAPP] == 1) ? "checked" : "",
                        "prefer_telegram"   => ($row[PersonContactTable::C_PREFER_TELEGRAM] == 1) ? "checked" : "",
                        "prefer_email"      => ($row[PersonContactTable::C_PREFER_EMAIL] == 1) ? "checked" : "",
                        "mooncake"          => $row[PersonContactTable::C_MOONCAKE],
                        "xmas_card"         => $row[PersonContactTable::C_XMAS_CARD],
                        "cny"               => $row[PersonContactTable::C_CNY],
                        //send the selected value only; the screen already renders these
                        //dropdowns once as hidden templates and clones them per row
                        "assign_to"         => $row[ProspectContactTable::C_ASSIGN_TO] ?? "0",
                        "best_time"         => ($best_time  == "0" || $best_time  == "") ? "00:00" : $best_time,
                        "lunch_time"        => ($lunch_time == "0" || $lunch_time == "") ? "00:00" : $lunch_time,
                    ];
                    $array_comp_detail[$row[ProspectTable::C_ID]][] = $comp_detail;
                }else{
                    $array_comp_detail[$row[ProspectTable::C_ID]][] = $comp_detail;
                }
                $current_id = $row[ProspectTable::C_ID];
            }

            //changes
            //add PROSPECT_BUSINESS_NATURE & use BusinessNatureTable::C_DESC
            //add business_nature_id & business_nature into $list, same as business_sector
            $current_id = "";
            foreach ($rows as $key=>$row) {
                if ($current_id != $row[ProspectTable::C_ID]) {
                    $list = [
						"id"                    => $rows[$key][ProspectTable::C_ID],
						"account_flag"          => $rows[$key][ProspectTable::C_FLAG],
						"account_flag_desc"     => htmlentities($this->getAccountFlagsDesc($rows[$key][ProspectTable::C_FLAG])),
						"company_name"          => htmlentities($rows[$key][ProspectTable::C_NAME] ?? ''),
						"company_desc"          => htmlentities($rows[$key][ProspectTable::C_BUSINESS_DESC] ?? ''),
						"telephone"             => htmlentities($rows[$key][ProspectTable::C_TELEPHONE] ?? ''),
						"telephone2"            => htmlentities($rows[$key][ProspectTable::C_TELEPHONE2] ?? ''),
						"website"               => htmlentities($rows[$key][ProspectTable::C_WEBSITE] ?? ''),
						"business_desc"         => htmlentities($rows[$key][ProspectTable::C_BUSINESS_DESC] ?? ''),
						"industry_id"           => $rows[$key][ProspectTable::C_INDUSTRY] ?? 0,
						"industry"              => htmlentities($rows[$key][IndustryTable::C_DESC] ?? ''),
						"business_sector_id"    => $rows[$key][ProspectTable::C_BUSINESS_SECTOR] ?? 0,
						"business_sector"       => htmlentities($rows[$key][BusinessSectorTable::C_DESC] ?? ''),
                        "business_nature_id"    => $rows[$key][ProspectTable::C_BUSINESS_NATURE] ?? 0,
						"business_nature"       => htmlentities($rows[$key][BusinessNatureTable::C_DESC] ?? ''),
						"total_employee"        => htmlentities($rows[$key][ProspectTable::C_TOTAL_EMPLOYEE] ?? ''),
						"acc_software_id"       => $rows[$key][ProspectTable::C_ACC_SOFTWARE] ?? 0,
						"acc_software"          => htmlentities($rows[$key]['SoftwareAcc'] ?? ''),
						"acc_software_c"        => htmlentities($rows[$key][ProspectTable::C_ACC_SOFTWARE_C] ?? ''),
						"acc_software_n"        => htmlentities($rows[$key][ProspectTable::C_ACC_SOFTWARE_N] ?? ''),
						"acc_software_user"     => htmlentities($rows[$key][ProspectTable::C_ACC_SOFTWARE_USER] ?? ''),
						"sales_software_id"     => $rows[$key][ProspectTable::C_SALES_SOFTWARE] ?? 0,
						"sales_software"        => htmlentities($rows[$key]['SoftwareSales'] ?? ''),
						"sales_software_c"      => htmlentities($rows[$key][ProspectTable::C_SALES_SOFTWARE_C] ?? ''),
						"sales_software_n"      => htmlentities($rows[$key][ProspectTable::C_SALES_SOFTWARE_N] ?? ''),
						"sales_software_user"   => htmlentities($rows[$key][ProspectTable::C_SALES_SOFTWARE_USER] ?? ''),
						"other_software_id"     => $rows[$key][ProspectTable::C_BUSINESS_DESC] ?? 0,
						"other_software"        => htmlentities($rows[$key]['SoftwareOther'] ?? ''),
						"other_software_c"      => htmlentities($rows[$key][ProspectTable::C_OTHER_SOFTWARE_C] ?? ''),
						"other_software_n"      => htmlentities($rows[$key][ProspectTable::C_OTHER_SOFTWARE_N] ?? ''),
						"other_software_user"   => htmlentities($rows[$key][ProspectTable::C_OTHER_SOFTWARE_USER] ?? ''),
						"proposal_1"            => htmlentities($rows[$key][ProspectTable::C_PROPOSAL_1] ?? ''),
						"proposal_2"            => htmlentities($rows[$key][ProspectTable::C_PROPOSAL_2] ?? ''),
						"subsidiary_location"   => htmlentities($rows[$key][ProspectTable::C_SUBSIDIARY_LOCATION] ?? ''),
						"subsidiary_entity"     => htmlentities($rows[$key][ProspectTable::C_SUBSIDIARY_ENTITY] ?? ''),
						"company_status_id"     => $rows[$key][ProspectTable::C_COMPANY_STATUS],
						"company_status"        => $company_status[$rows[$key][ProspectTable::C_COMPANY_STATUS]],
						"sales_status_id"       => $rows[$key][ProspectTable::C_SALES_STATUS],
						"sales_status"          => $sales_status[$rows[$key][ProspectTable::C_SALES_STATUS]],
						"sales_status2_id"      => $rows[$key][ProspectTable::C_SALES_STATUS2],
						"sales_status2"         => $sales_status2[$rows[$key][ProspectTable::C_SALES_STATUS2]],
						"status_color"          => $status_color[$rows[$key][ProspectTable::C_SALES_STATUS2]],

						"company_email"         => $rows[$key][ProspectTable::C_COMPANY_EMAIL],
						"company_linkedin"      => $rows[$key][ProspectTable::C_LINKEDIN_WEBSITE],
						"company_source"        => $rows[$key][ProspectTable::C_SOURCES],
						"company_state"         => $rows[$key][ProspectTable::C_STATE],
						"company_detail"        => $array_comp_detail[$row[ProspectTable::C_ID]],
					];
					array_push($lists, $list);
                    $current_id = $row[ProspectTable::C_ID];
                } else {
                    continue;
                }
            }
        }
        // log::write("lists final ".print_r($lists,true));
        

        if (!is_null($rows_person) && count($rows_person) > 0) {
            foreach ($rows_person as $row_person) {

                $list_person = [
                    "person_id"     => $row_person[PersonContactTable::C_ID],
                    "person_name"   => $row_person[PersonContactTable::C_FULL_NAME]
                ];

                array_push($lists_person, $list_person);
            }
        }
        $datas = array();
        $datas = [ 
                    "details"       => json_encode($lists),
                    "person_con"    => $lists_person,
                    "prospect_id"   => $params['prospect_id']
                ];
        // log::write("datas ".print_r($datas,true));
        unset($clsProspect);
        unset($clsPersonContact);
        unset($clsGroup);
        unset($clsRight);
        $this->sendJsonResponse(Status::Ok, "", $datas, $this->type);
    }

    private function getSuspectContactDetail($id){
        $clsContact = new ProspectContactClass($this->db);

        $rows_contact = $clsContact->getSuspectContactByID($id);

        $lists            = [];
        $lists_assign_to  = [];
        $lists_best_time  = [];
        $lists_lunch_time = [];

        if (!is_null($rows_contact) && count($rows_contact) > 0) {
            foreach ($rows_contact as $row_contact) {   
                $action_date = "";
                if($row_contact[ProspectContactTable::C_ACTION_DATE] != NULL_DATE.'.000'){
                    $action_date = date_create($row_contact[ProspectContactTable::C_ACTION_DATE])->format('Y-m-d');
                }

                $list = [
                    "id"                => $row_contact[ProspectContactTable::C_CONTACT_ID],
                    "sequence"          => $row_contact[ProspectContactTable::C_SEQUENCE],
                    "designation"       => $row_contact[ProspectContactTable::C_DESIGNATION],
                    "person_id"         => $row_contact[PersonContactTable::C_ID],
                    "full_name"         => $row_contact[PersonContactTable::C_FULL_NAME],
                    "prefer_name"       => $row_contact[PersonContactTable::C_PREFER_NAME],
                    "telephone"         => $row_contact[PersonContactTable::C_TELEPHONE],
                    "email"             => $row_contact[PersonContactTable::C_EMAIL],
                    "report_to"         => $row_contact[ProspectContactTable::C_REPORT_TO],
                    "next_action"       => $row_contact[ProspectContactTable::C_NEXT_ACTION],
                    "action_date"       => $action_date,
                    "email_promo"       => ($row_contact[PersonContactTable::C_PREFER_PROMOTION] == 1) ? "checked" : "",
                    "prefer_phone"      => ($row_contact[PersonContactTable::C_PREFER_TELEPHONE] == 1) ? "checked" : "",
                    "prefer_whatsapp"   => ($row_contact[PersonContactTable::C_PREFER_WHATSAPP] == 1) ? "checked" : "",
                    "prefer_telegram"   => ($row_contact[PersonContactTable::C_PREFER_TELEGRAM] == 1) ? "checked" : "",
                    "prefer_email"      => ($row_contact[PersonContactTable::C_PREFER_EMAIL] == 1) ? "checked" : "",
                    "mooncake"          => $row_contact[PersonContactTable::C_MOONCAKE],
                    "xmas_card"         => $row_contact[PersonContactTable::C_XMAS_CARD],
                    "cny"               => $row_contact[PersonContactTable::C_CNY],
                ];
                array_push($lists, $list);
                array_push($lists_best_time, Util::getTimeDropdown($row_contact[ProspectContactTable::C_BEST_TIME]));
                array_push($lists_lunch_time, Util::getTimeDropdown($row_contact[ProspectContactTable::C_LUNCH_TIME]));
                array_push($lists_assign_to, $this->getUserOption($row_contact[ProspectContactTable::C_ASSIGN_TO]));
            }
        }
        return [$lists, $lists_best_time, $lists_lunch_time, $lists_assign_to];
    }

    private function getCompanyStatusOption($id = 0){
        $select1 = "";
        $select2 = "";
        if($id == 0){
            $select1 = "selected";
        }else if($id == 1){
            $select2 = "selected";
        }
        $html = "<option value='0' ".$select1.">Inactive</option>
                 <option value='1' ".$select2.">Active</option>";

        if($id != 0){
            return $html;
        }else{
            return $html;
        }
    }

    private function getSalesStatusOption($id = 0){
        $select1 = "";
        $select2 = "";
        if($id == 0){
            $select1 = "selected";
        }else if($id == 1){
            $select2 = "selected";
        }
        $html = "<option value='0' ".$select1.">None</option>
                 <option value='1' ".$select2.">Account</option>";

        if($id != 0){
            return $html;
        }else{
            return $html;
        }
    }

    private function getSalesStatus2Option($id = 0){
        $select1 = "";
        $select2 = "";
        $select3 = "";
        $select4 = "";
        if($id == 0){
            $select1 = "selected";
        }else if($id == 1){
            $select2 = "selected";
        }else if($id == 2){
            $select3 = "selected";
        }else{
            $select4 = "selected";
        }
        $html = "<option value='0' style='color:red'".$select1.">Raw</option>
                 <option value='1' style='color:brown'".$select2.">Suspect</option>
                 <option value='2' style='color:orange'".$select3.">Prospect</option>
                 <option value='3' style='color:green'".$select4.">Customer</option>";

        if($id != 0){
            return $html;
        }else{
            return $html;
        }
    }

    private function getIndustryOption($rows = null, $id = 0){
        $cls    = new IndustryClass($this->db);
        $vls    = array();
        $vls[]  = array('code'=>'0', 'desc'=>'--Select Industry--');
        
        // if(!is_null($rows)){
        //     foreach($rows as $row){
        //         $vls[]  = array('code'=>$row[IndustryTable::C_ID], 'desc'=>$row[IndustryTable::C_DESC]);
        //     }
        // }else{
            try{
                $rows   = $cls->getTable("", IndustryTable::C_DESC);
                foreach($rows as $row){
                    $vls[]  = array('code'=>$row[IndustryTable::C_ID], 'desc'=>$row[IndustryTable::C_DESC]);
                }
            } catch (Exception $e) {
                Log::write($e->getMessage());
                return $vls;
            }
        // }

        if($id != 0){
            return Util::createOptionValue($vls, $id);
        }else{
            return Util::createOptionValue($vls);
        }
    }

    private function getBusinessSectorOption($rows = null, $id = 0){
        $cls    = new BusinessSectorClass($this->db);
        $vls    = array();
        $vls[]  = array('code'=>'0', 'desc'=>'--Select Business Sector--');

        // if(!is_null($rows)){
        //     foreach($rows as $row){
        //         $vls[]  = array('code'=>$row[BusinessSectorTable::C_ID], 'desc'=>$row[BusinessSectorTable::C_DESC]);
        //     }
        // }else{
            try{
                $rows   = $cls->getTable("", BusinessSectorTable::C_DESC);
                foreach($rows as $row){
                    $vls[]  = array('code'=>$row[BusinessSectorTable::C_ID], 'desc'=>$row[BusinessSectorTable::C_DESC]);
                }
            } catch (Exception $e) {
                Log::write($e->getMessage());
                return $vls;
            }
        // }

        if($id != 0){
            return Util::createOptionValue($vls, $id);
        }else{
            return Util::createOptionValue($vls);
        }
    }

    //changes
    private function getBusinessNatureOption($rows = null, $id = 0){
        $cls    = new BusinessNatureClass($this->db);
        $vls    = array();
        $vls[]  = array('code'=>'0', 'desc'=>'--Select Business Nature--');

        try{
            $rows   = $cls->getTable("", BusinessNatureTable::C_DESC);
            foreach($rows as $row){
                $vls[]  = array('code'=>$row[BusinessNatureTable::C_ID], 'desc'=>$row[BusinessNatureTable::C_DESC]);
            }
        } catch (Exception $e) {
            Log::write($e->getMessage());
            return $vls;
        }

        if($id != 0){
            return Util::createOptionValue($vls, $id);
        }else{
            return Util::createOptionValue($vls);
        }
    }
    //changes ended

    private function getSoftwareOption($type = 0, $rows = null, $id = 0){
        $cls    = new SoftwareClass($this->db);
        $vls    = array();
        $filter = "";
        if($type == 1){
            $vls[]  = array('code'=>'0', 'desc'=>'--Select Acc Software--');
            $filter = SoftwareTable::C_TYPE . " = " . $type;
        }else if($type == 2){
            $vls[]  = array('code'=>'0', 'desc'=>'--Select Sales Software--');
            $filter = SoftwareTable::C_TYPE . " = " . $type;
        }else if($type == 3){
            $vls[]  = array('code'=>'0', 'desc'=>'--Select Other Software--');
            $filter = SoftwareTable::C_TYPE . " = " . $type;
        }

        // if(!is_null($rows)){
        //     foreach($rows as $row){
        //         $vls[]  = array('code'=>$row[SoftwareTable::C_ID], 'desc'=>$row[SoftwareTable::C_DESC]);
        //     }
        // }else{
            try{
                $rows   = $cls->getTable($filter, SoftwareTable::C_DESC);
                foreach($rows as $row){
                    $vls[]  = array('code'=>$row[SoftwareTable::C_ID], 'desc'=>$row[SoftwareTable::C_DESC]);
                }
            } catch (Exception $e) {
                Log::write($e->getMessage());
                return $vls;
            }
        // }

        if($id != 0){
            return Util::createOptionValue($vls, $id);
        }else{
            return Util::createOptionValue($vls);
        }
    }

    // private function getTimeOption($id = 0){
    //     $vls   = array();
    //     $vls[] = array('code'=>'0','desc'=>'--:-- --');
    //     $vls[] = array('code'=>'08:00','desc'=>'08:00 AM');
    //     $vls[] = array('code'=>'09:00','desc'=>'09:00 AM');
    //     $vls[] = array('code'=>'10:00','desc'=>'10:00 AM');
    //     $vls[] = array('code'=>'11:00','desc'=>'11:00 AM');
    //     $vls[] = array('code'=>'12:00','desc'=>'12:00 AM');
    //     $vls[] = array('code'=>'13:00','desc'=>'01:00 PM');
    //     $vls[] = array('code'=>'14:00','desc'=>'02:00 PM');
    //     $vls[] = array('code'=>'15:00','desc'=>'03:00 PM');
    //     $vls[] = array('code'=>'16:00','desc'=>'04:00 PM');
    //     $vls[] = array('code'=>'17:00','desc'=>'05:00 PM');

    //     if($id != 0){
    //         return Util::createOptionValue($vls, $id);
    //     }else{
    //         return Util::createOptionValue($vls);
    //     }
    // }

    private function getUserOption($id = 0){
        $vls = array();
        try{
            $cls   = new UserClass($this->db);
            $rows  = $cls->getTable();
            $vls[] = array('code'=>'0','desc'=>'None');

            if(!is_null($rows) && count($rows) >0){
                foreach($rows as $row){
                    $vls[] = array('code'=>$row[UserTable::C_ID],'desc'=>$row[UserTable::C_FULL_NAME]) ;
                }
            }
        } catch (Exception $e) {
			Log::write($e->getMessage());
			return $vls;
		}

        if($id != 0){
            return Util::createOptionValue($vls, $id);
        }else{
            return Util::createOptionValue($vls);
        }
    }

    private function getMemo(){
        $cls   = new UserClass($this->db);
        $rows  = $cls->getRecord($_SESSION[SE_USERID]);

        return $rows[UserTable::C_MEMO];
    }

    private function getConversationHistory($params) {
        $cls   = new ProspectConversationClass($this->db);

        $id         = $params['id'];
        $contact_id = $params['contact_id'];
        $rows       = $cls->getConversationHistory($id, $contact_id);
        $html       = "";
        if (!is_null($rows) && count($rows) > 0) {
            foreach ($rows as $row) {                
                $html .= "Date: " . date_create($row[ProspectConversationTable::C_CREATE_DATE])->format('d/m/Y').
                         "<br>Conversation: " . $row[ProspectConversationTable::C_DESC] . "<br><br>";
            }
        }
        $this->sendJsonResponse(Status::Ok, "", $html, $this->type);
    }
    
    private function getPersonContact($params) {
        $clsPersonContact  = new PersonContactClass($this->db);

        $person_id  = $params['person_id'];
        $id         = $params['id'];
        $row        = $params['row'];

        $row_person = $clsPersonContact->getRecord($person_id);

        $datas      = array();
        if (!is_null($row_person) && count($row_person) > 0) {
            $datas = [
                "full_name"   => $row_person[PersonContactTable::C_FULL_NAME],
                "prefer_name" => $row_person[PersonContactTable::C_PREFER_NAME],
                "telephone"   => $row_person[PersonContactTable::C_TELEPHONE],
                "email"       => $row_person[PersonContactTable::C_EMAIL],
                "id"          => $id,
                "row"         => $row
            ];
        }

        $this->sendJsonResponse(Status::Ok, "", $datas, $this->type);
    }
    private function updateCallScript($params) {
        $cls  = new UserClass($this->db);
        $id   = $_SESSION[SE_USERID];

        try{
            $datas = array() ;
            $datas[] = $this->db->fieldValue(UserTable::C_MEMO,$this->getParam($params,'memo',""));
    
            $memo = $params['memo'];
            $cls->updateRecord($id, $datas);
            $this->sendJsonResponse(Status::Ok, "Successfullt saved into the database", $memo, $this->type);

        } catch(Exception $e){
            Log::write("[Telemarketing]" . $e->getMessage());
            $this->sendJsonResponse(Status::Error, "Failed to updated memo", '', $this->type);
            return;
        }
    }

    private function downloadFollowUp($params) {
        $style_header = array(
            'font'          => array('bold' => true),
            'alignment'     => array('horizontal' => Alignment::HORIZONTAL_LEFT,),
            'fill'          => array('fillType' => Fill::FILL_SOLID,'color' => array('rgb' => 'C8C8C8'))
        );

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

        $action_from        = $params['action_from'];
        $action_to          = $params['action_to'];

        $columnHeader = array ("Date", "Best Time", "Full Name", "Last Conversation", "Phone Number", "Assign To");

        /** Create a new Spreadsheet Object **/
		$spreadsheet = new Spreadsheet();

        $filename = PATH_SUSPECT_EXCEL . "Follow Up Table.xlsx";
        //========================================================================
        //1ST SHEET

        //TITLE
		$spreadsheet->setActiveSheetIndex(0);
		$worksheet = $spreadsheet->getActiveSheet();

        $worksheet->setCellValue("A1", "Follow Up Table");
        $worksheet->getStyle("A1")->applyFromArray($style_title);

        $idx = 3;
        for($i=0;$i<count($columnHeader);$i++){
            $worksheet->setCellValue(chr(65+$i) . $idx, $columnHeader[$i]);
        }
        $worksheet->getStyle(chr(65) . $idx . ":" . chr(65+count($columnHeader)-1) . $idx)->applyFromArray($style_header);

        $clsProspect       = new ProspectClass($this->db);
        $clsConv           = new ProspectConversationClass($this->db);
        $rows              = $clsProspect->getFollowUpListByActionDate($action_from, $action_to);

        $idx++;
        if(!is_null($rows) && count($rows)>0){
            foreach ($rows as $row) {
                $worksheet->setCellValue("A" . $idx, htmlentities(date_create($row[ProspectContactTable::C_ACTION_DATE])->format("d/m/Y")));
                $worksheet->setCellValue("B" . $idx, htmlentities($row[ProspectContactTable::C_BEST_TIME]));
                $worksheet->setCellValue("C" . $idx, htmlentities($row[PersonContactTable::C_FULL_NAME]));
                $worksheet->setCellValue("D" . $idx, htmlentities($row[ProspectConversationTable::C_DESC]));
                $worksheet->setCellValue("E" . $idx, htmlentities($row[PersonContactTable::C_TELEPHONE]));
                $worksheet->setCellValue("F" . $idx, htmlentities($row[UserTable::C_FULL_NAME]));
                $idx++;
            }
        }

        //check if directory exist if not then create
		$directory = PATH_SUSPECT_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);
    }

    //=============================================================================================
    //FOLLOW UP LIST
    private function getFollowUpList($params) {
        $clsProspect     = new ProspectClass($this->db);
        $action_from        = $params['action_from'];
        $action_to          = $params['action_to'];

        $rows            = $clsProspect->getFollowUpListByActionDate($action_from, $action_to);
        $lists           = [];
        if (!is_null($rows) && count($rows) > 0) {
            foreach ($rows as $row) {
                // if($row[ProspectConversationTable::C_DESC]!=null) {
                    $list = [
                        "prospect_id"       => $row[ProspectTable::C_ID],
                        "contact_id"        => $row[ProspectContactTable::C_CONTACT_ID],
                        "action_date"       => date_create($row[ProspectContactTable::C_ACTION_DATE])->format('d/m/Y'),
                        "best_time"         => htmlentities($row[ProspectContactTable::C_BEST_TIME]),
                        "full_name"         => htmlentities($row[PersonContactTable::C_FULL_NAME]),
                        "phone_number"      => htmlentities($row[PersonContactTable::C_TELEPHONE]),
                        "last_conversation" => htmlentities($row[ProspectConversationTable::C_DESC] ?? ''),
                        "next_action"       => htmlentities($row[ProspectContactTable::C_NEXT_ACTION]),
                        "assign_to"         => htmlentities($row[UserTable::C_FULL_NAME]),
                    ];
                    array_push($lists, $list);
                // }
            }
        }
        $datas = array();
        $datas = [ 
                    "details"   => json_encode($lists),
                ];
        $this->sendJsonResponse(Status::Ok, "", $datas, $this->type);
    }

    //=============================================================================================
    //EXPORT EXCEL
    private function exportAllContact() {
        $spreadsheet = new Spreadsheet();
        $title = "All Contact";

        $filename = PATH_SUSPECT_EXCEL . "All Contact " . date_create('now')->format('d_m_Y') . ".xlsx";
        //========================================================================
        //1ST SHEET

        //TITLE
		$spreadsheet->setActiveSheetIndex(0);
		$worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle($title);
        $worksheet->setCellValue("A1", "Email <CONTACT email>");
        $worksheet->setCellValue("B1", "First Name <CONTACT firstname>");
        $worksheet->setCellValue("C1", "Phone number <CONTACT phone numbe>");
        $worksheet->setCellValue("D1", "Company name <COMPANY name>");
        $worksheet->setCellValue("E1", "Phone number <COMPANY phone number>");
        $worksheet->setCellValue("F1", "Industry <COMPANY Industry>");
        $worksheet->setCellValue("G1", "Preferred Channels <CONTACT Preferred Channels>");

        $cls  = new ProspectContactClass($this->db);
        $rows = $cls->exportProspectContactWithCompanyInfo("All");
        $count = 2;
        foreach($rows as $row){
            $worksheet->setCellValue("A" . $count, $row[PersonContactTable::C_EMAIL]);
            $worksheet->setCellValue("B" . $count, $row[PersonContactTable::C_PREFER_NAME]);
            $worksheet->setCellValue("C" . $count, $row[ProspectContactTable::C_TELEPHONE]);
            $worksheet->setCellValue("D" . $count, $row[ProspectTable::C_NAME]);
            $worksheet->setCellValue("E" . $count, $row[ProspectTable::C_TELEPHONE]);
            $worksheet->setCellValue("F" . $count, $row[IndustryTable::C_DESC]);
            
            if($row[PersonContactTable::C_PREFER_TELEGRAM] == 1)
                $worksheet->setCellValue("G" . $count, "Telegram");
            if($row[PersonContactTable::C_PREFER_WHATSAPP] == 1){
                $worksheet->setCellValue("G" . $count, "Whatsapp");
            }
            $count++;
        }

         //check if directory exist if not then create
		$directory = PATH_SUSPECT_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);

        $cls->markedAsExported();
        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 exportNewContact() {
            $spreadsheet = new Spreadsheet();
        $title = "All Contact";

        $filename = PATH_SUSPECT_EXCEL . "All Contact " . date_create('now')->format('d_m_Y') . ".xlsx";
        //========================================================================
        //1ST SHEET

        //TITLE
		$spreadsheet->setActiveSheetIndex(0);
		$worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle($title);
        $worksheet->setCellValue("A1", "Email <CONTACT email>");
        $worksheet->setCellValue("B1", "First Name <CONTACT firstname>");
        $worksheet->setCellValue("C1", "Phone number <CONTACT phone numbe>");
        $worksheet->setCellValue("D1", "Company name <COMPANY name>");
        $worksheet->setCellValue("E1", "Phone number <COMPANY phone number>");
        $worksheet->setCellValue("F1", "Industry <COMPANY Industry>");
        $worksheet->setCellValue("G1", "Preferred Channels <CONTACT Preferred Channels>");

        $cls  = new ProspectContactClass($this->db);
        $rows = $cls->exportProspectContactWithCompanyInfo("New");
        $count = 2;
        if(!is_null($rows) && count($rows) > 0){
            foreach($rows as $row){
                $worksheet->setCellValue("A" . $count, $row[PersonContactTable::C_EMAIL]);
                $worksheet->setCellValue("B" . $count, $row[PersonContactTable::C_PREFER_NAME]);
                $worksheet->setCellValue("C" . $count, $row[ProspectContactTable::C_TELEPHONE]);
                $worksheet->setCellValue("D" . $count, $row[ProspectTable::C_NAME]);
                $worksheet->setCellValue("E" . $count, $row[ProspectTable::C_TELEPHONE]);
                $worksheet->setCellValue("F" . $count, $row[IndustryTable::C_DESC]);
                
                if($row[PersonContactTable::C_PREFER_TELEGRAM] == 1)
                    $worksheet->setCellValue("G" . $count, "Telegram");
                if($row[PersonContactTable::C_PREFER_WHATSAPP] == 1){
                    $worksheet->setCellValue("G" . $count, "Whatsapp");
                }
                $count++;
            }
            //check if directory exist if not then create
           $directory = PATH_SUSPECT_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);
    
           $cls->markedAsExported();
           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);
        }else{
            echo('No new contact to export');
        }

    }
    //=============================================================================================
    //IMPORT EXCEL
    private function getTemplate() {
        $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))
		);

        /** Create a new Spreadsheet Object **/
        $clsSoftware = new SoftwareClass($this->db);
        $clsBusiness = new BusinessSectorClass($this->db);
        $clsIndustry = new IndustryClass($this->db);

		$spreadsheet = new Spreadsheet();
        $title = "Suspect Template";

        $filename = PATH_SUSPECT_EXCEL . "template-suspect.xlsx";
        //========================================================================
        //1ST SHEET

        //TITLE
		$spreadsheet->setActiveSheetIndex(0);
		$worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle($title);
        $worksheet->setCellValue("A1", "Company Name");
        $worksheet->getStyle("A1")->applyFromArray($style_title);
        $worksheet->setCellValue("B1", "Telephone");
        $worksheet->getStyle("B1")->applyFromArray($style_title);
        $worksheet->setCellValue("C1", "Website");
        $worksheet->getStyle("C1")->applyFromArray($style_title);
        $worksheet->setCellValue("D1", "Business Description");
        $worksheet->getStyle("D1")->applyFromArray($style_title);
        $worksheet->setCellValue("E1", "Address");
        $worksheet->getStyle("E1")->applyFromArray($style_title);
        $worksheet->setCellValue("F1", "Industry");
        $worksheet->getStyle("F1")->applyFromArray($style_title);
        $worksheet->setCellValue("G1", "Business Sector");
        $worksheet->getStyle("G1")->applyFromArray($style_title);
        $worksheet->setCellValue("H1", "Total Employee");
        $worksheet->getStyle("H1")->applyFromArray($style_title);
        $worksheet->setCellValue("I1", "Accounting Software");
        $worksheet->getStyle("I1")->applyFromArray($style_title);
        $worksheet->setCellValue("J1", "No. of Accounting Software Users");
        $worksheet->getStyle("J1")->applyFromArray($style_title);
        $worksheet->setCellValue("K1", "Other Software");
        $worksheet->getStyle("K1")->applyFromArray($style_title);
        $worksheet->setCellValue("L1", "No. of Other Software Users");
        $worksheet->getStyle("L1")->applyFromArray($style_title);

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

        //========================================================================
        //2rd SHEET
		$spreadsheet->createSheet(1);
		$spreadsheet->setActiveSheetIndex(1);
		$worksheet = $spreadsheet->getActiveSheet();
		$worksheet->setTitle('Suspect Contact');
        $worksheet->setCellValue("A1", "Company Name");
        $worksheet->getStyle("A1")->applyFromArray($style_title);
        $worksheet->setCellValue("B1", "Designation");
        $worksheet->getStyle("B1")->applyFromArray($style_title);
        $worksheet->setCellValue("C1", "Name");
        $worksheet->getStyle("C1")->applyFromArray($style_title);
        $worksheet->setCellValue("D1", "Preferred Name");
        $worksheet->getStyle("D1")->applyFromArray($style_title);
        $worksheet->setCellValue("E1", "Phone");
        $worksheet->getStyle("E1")->applyFromArray($style_title);
        $worksheet->setCellValue("F1", "Email");
        $worksheet->getStyle("F1")->applyFromArray($style_title);

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

		$column = 0;

		//HINT
		$worksheet->setCellValue("A1", "PLEASE USE THE REQUIRED VALUE WHEN FILL THE COLUMN");
		$worksheet->getStyle("A1")->applyFromArray($style_header);

		$worksheet->setCellValue("A3", "Industry : ");
		$worksheet->getStyle("A3")->applyFromArray($style_title);
		$worksheet->setCellValue("A4", "Value");
		$worksheet->getStyle("A4")->applyFromArray($style_label);
		$row_industry = 5;
		$list_industry = $clsIndustry->getTable();
		if (!is_null($list_industry) && count($list_industry) > 0) {
			foreach ($list_industry as $industry) {
				$worksheet->setCellValue("A".$row_industry, $industry[IndustryTable::C_DESC]);
				$worksheet->getStyle("A".$row_industry)->applyFromArray($style_hint);
				$row_industry++;
			}
		}

		$worksheet->setCellValue("C3", "Busniess Sector : ");
		$worksheet->getStyle("C3")->applyFromArray($style_title);
		$worksheet->setCellValue("C4", "Value");
		$worksheet->getStyle("C4")->applyFromArray($style_label);
		$row_business = 5;
		$list_business = $clsBusiness->getTable();
		if (!is_null($list_business) && count($list_business) > 0) {
			foreach ($list_business as $business) {
				$worksheet->setCellValue("C".$row_business, $business[BusinessSectorTable::C_DESC]);
				$worksheet->getStyle("C".$row_business)->applyFromArray($style_hint);
				$row_business++;
			}
		}

		$worksheet->setCellValue("E3", "Accounting Software : ");
		$worksheet->getStyle("E3")->applyFromArray($style_title);
		$worksheet->setCellValue("E4", "Value");
		$worksheet->getStyle("E4")->applyFromArray($style_label);
		$row_software = 5;
		$list_software = $clsSoftware->getTable();
		if (!is_null($list_software) && count($list_software) > 0) {
			foreach ($list_software as $software) {
				$worksheet->setCellValue("E".$row_software, $software[SoftwareTable::C_DESC]);
				$worksheet->getStyle("E".$row_software)->applyFromArray($style_hint);
				$row_software++;
			}
		}

        $worksheet->setCellValue("G3", "Other Software : ");
		$worksheet->getStyle("G3")->applyFromArray($style_title);
		$worksheet->setCellValue("G4", "Value");
		$worksheet->getStyle("G4")->applyFromArray($style_label);
		$row_software = 5;
		$list_software = $clsSoftware->getTable();
		if (!is_null($list_software) && count($list_software) > 0) {
			foreach ($list_software as $software) {
				$worksheet->setCellValue("G".$row_software, $software[SoftwareTable::C_DESC]);
				$worksheet->getStyle("G".$row_software)->applyFromArray($style_hint);
				$row_software++;
			}
		}

      
        //check if directory exist if not then create
		$directory = PATH_SUSPECT_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 processSuspectUploadExcel(){
        $userId     = $_SESSION[SE_USERID];

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

        // $sizeConfigured = $this->getSizeConfig();
        $limitFilesize  = 1000 * 1024;   //5000kb

        if (isset($_FILES["uploadFileSuspect"])) {
            $resultTemp = true;
            $size       = $_FILES["uploadFileSuspect"]['size'][0];
            $fileName  = $_FILES["uploadFileSuspect"]["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_SUSPECT_EXCEL . "temp-" . $userId . "/";
            $newName   = "suspect_excel_doc_" . $userId . "." . $ext;
            $newfile   = $directory . $newName;
            $fileUpload = $_FILES["uploadFileSuspect"]['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 processSuspectReadExcel($params){
        $values = $this->processReadExcel($_SESSION[SE_USERID], $params['new_name']);

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

    private function processReadExcel($userId, $newName){
        $clsProspect = new ProspectClass($this->db);
        $clsPerson   = new PersonContactClass($this->db);
        $clsContact  = new ProspectContactClass($this->db);
        $clsSoftware = new SoftwareClass($this->db);
        $clsBusiness = new BusinessSectorClass($this->db);
        $clsIndustry = new IndustryClass($this->db);

        $this->db->beginTran();
        try {
            //get file
            $tmpfname      = PATH_SUSPECT_EXCEL . "temp-" . $userId . "/" . $newName;
            $excelReader   = IOFactory::createReaderForFile($tmpfname);
            $spreadsheet   = $excelReader->load($tmpfname);
            $data          = array();
            $data_contact  = array();
            $comps         = array();
            $comps_contact = array();

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

            $numSheets = $spreadsheet->getSheetCount();

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

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

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

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

                if ($dataset != "") {
                    $data[] = array_filter($dataset);
                }
            }
            
            if($numSheets > 1){
                //GET 2Nd SHEET DATA
                $spreadsheet->setActiveSheetIndex(1);
                $worksheet_contact      = $spreadsheet->getActiveSheet();
                $highestColumm_contact  = $worksheet_contact->getHighestColumn(); // e.g. "EL"
                $highestRow_contact     = $worksheet_contact->getHighestRow();

                $highestColumm_contact++;
                for ($row_contact = 2; $row_contact <= $highestRow_contact; $row_contact++) {
                    $dataset = array();

                    for ($column = 'A'; $column != $highestColumm_contact; $column++) {
                        if($column == "A"){
                            $comp[] = $worksheet_contact->getCell($column . $row_contact)->getCalculatedValue();
                        }
                        $dataset[] = $worksheet_contact->getCell($column . $row_contact)->getCalculatedValue();
                    }

                    if ($dataset != "") {
                        $data_contact[] = array_filter($dataset);
                    }
                }

                foreach($comps_contact as $comp_contact){
                    if(!in_array($comps, $comp_contact)){
                        $ret_data['mesg'] = "Company Name '" . $comp_contact . "' in Suspect Contact doesnt exist in Suspect Template";
                        $ret_data['status'] = false;

                        return $ret_data;
                    }
                }
            }

            if ($allclear) {
                foreach (array_filter($data) as $value) {
                    $modifyby       = $_SESSION[SE_USERID];
                    $modifydate     = date_create('now')->format('Y-m-d H:i:s');
                    $ws             = $_SESSION[SE_REMOTE_IP];

                    if($numSheets > 1){
                        $comp_name              = $this->getParam($value, '0', "");
                        $telephone              = $this->getParam($value, '1', "");
                        $website                = $this->getParam($value, '2', "");
                        $business_desc          = $this->getParam($value, '3', "");
                        $address                = $this->getParam($value, '4', "");
                        $industry               = $this->getParam($value, '5', "");
                        $business_sector        = $this->getParam($value, '6', "");
                        $total_employee         = $this->getParam($value, '7', "");
                        $acc_software           = $this->getParam($value, '8', "");
                        $acc_software_user      = $this->getParam($value, '9', "");
                        $other_software         = $this->getParam($value, '10', "");
                        $other_software_user    = $this->getParam($value, '11', "");

                        if ($business_sector != "") {
                            $id = $clsBusiness->getBusinessIDByDesc($business_sector);
    
                            if($id == null){
                                $allstatus = false;
                                $mesg   = "'" . $business_sector . "' in Business Sector field in Suspect Template doesnt exist in the system";
                            }else{
                                $business_sector = $id;
                            }
                        }
                        
                        if ($acc_software != "") {
                            $id = $clsSoftware->getSoftwareIDByDesc($acc_software);
    
                            if($id == null){
                                $allstatus = false;
                                $mesg   = "'" . $acc_software . "' in Accouning Software field in Suspect Template doesnt exist in the system";
                            }else{
                                $acc_software = $id;
                            }
                        }
                        
                        if ($other_software != "") {
                            $id = $clsSoftware->getSoftwareIDByDesc($other_software);
    
                            if($id == null){
                                $allstatus = false;
                                $mesg   = "'" . $other_software . "' in Other Software field in Suspect Template doesnt exist in the system";
                            }else{
                                $other_software = $id;
                            }
                        }
                    }else{
                        $state                  = $this->getParam($value, '2', "");
                        $industry               = $this->getParam($value, '3', "");
                        $business_desc          = $this->getParam($value, '4', "");
                        $total_employee         = $this->getParam($value, '5', "");
                        $comp_name              = $this->getParam($value, '6', "");
                        $contact_name           = $this->getParam($value, '7', "");
                        $contact_designation    = $this->getParam($value, '8', "");
                        $contact_telephone      = $this->getParam($value, '9', "");
                        $contact_email          = $this->getParam($value, '10', "");
                        $website                = $this->getParam($value, '11', "");
                        $email                  = $this->getParam($value, '12', "");
                        $telephone              = $this->getParam($value, '13', "");
                        $linkedIn_website       = $this->getParam($value, '14', "");
                        $sources                = $this->getParam($value, '15', "");
                    }
                 
                    //====================================
                    //check if any required field is empty
                    $column = "";
                    $allstatus = true;

                    if ($comp_name == "") {
                        $allstatus = false;
                        $mesg   = "There is an empty value inside Company Name field in Suspect Template";
                    }

                    if ($telephone == "") {
                        $allstatus = false;
                        $mesg   = "There is an empty value inside Telephone field in Suspect Template";
                    }
                    
                    if ($business_desc == "") {
                        $allstatus = false;
                        $mesg   = "There is an empty value inside Business Desc field in Suspect Template";
                    }
                    
                    if ($industry != "") {
                        $id = $clsIndustry->getIndustryIDByDesc($industry);

                        if($id == null){
                            $allstatus = false;
                            $mesg   = "'" . $industry . "' in Industry field in Suspect Template doesnt exist in the system";
                        }else{
                            $industry = $id;
                        }
                    }

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

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

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

                    if($numSheets > 1){
                        $datas = array();
                        $datas[] = $this->db->fieldValue(ProspectTable::C_NAME, $comp_name);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_TELEPHONE, $telephone);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_WEBSITE, $website);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_BUSINESS_DESC, $business_desc);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_INDUSTRY, $industry);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_BUSINESS_SECTOR, $business_sector);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_1, $address);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_TOTAL_EMPLOYEE, $total_employee);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE, $acc_software);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_ACC_SOFTWARE_USER, $acc_software_user);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE, $other_software);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_OTHER_SOFTWARE_USER, $other_software_user);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_SALES_STATUS, ProspectSalesStatus::Raw);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_WS_ID, $ws);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_MODIFY_BY, $modifyby);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_MODIFY_DATE, $modifydate);

                        $row_prospect = $clsProspect->getProspectByCompanyName($comp_name);

                        //if total employee < 0 then skip insert/update
                        if($row_prospect != null){
                            if($row_prospect[ProspectTable::C_STATUS] == ProspectSalesStatus::Raw){
                                $clsProspect->updateRecord($row_prospect[ProspectTable::C_ID], $datas);
                            }
                        }else{
                            $datas[] = $this->db->fieldValue(ProspectTable::C_CREATE_BY, $modifyby);
                            $datas[] = $this->db->fieldValue(ProspectTable::C_CREATE_DATE, $modifydate);
                            $clsProspect->addRecord($datas);
                        }
                    }else{
                        $datas = array();
                        $datas[] = $this->db->fieldValue(ProspectTable::C_STATE, $state);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_INDUSTRY, $industry);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_BUSINESS_DESC, $business_desc);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_TOTAL_EMPLOYEE, $total_employee);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_NAME, $comp_name);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_WEBSITE, $website);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_TELEPHONE, $telephone);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_LINKEDIN_WEBSITE, $linkedIn_website);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_SOURCES, $sources);

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

                        $row_prospect = $clsProspect->getProspectByCompanyName($comp_name);

                        //if total employee < 0 then skip insert/update
                        $datas[] = $this->db->fieldValue(ProspectTable::C_CREATE_BY, $modifyby);
                        $datas[] = $this->db->fieldValue(ProspectTable::C_CREATE_DATE, $modifydate);

                        if($row_prospect != null){
                            if($row_prospect[ProspectTable::C_STATUS] == ProspectSalesStatus::Raw){
                                $clsProspect->updateRecord($row_prospect[ProspectTable::C_ID], $datas);
                            }
                        }else{
                            $id = $clsProspect->addRecord($datas);
                            
                            if($contact_name != ""){
                                $datas_person = array();
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_FULL_NAME, $contact_name);
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_NAME, $contact_name);
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_EMAIL, $contact_email);
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_TELEPHONE, $contact_telephone);
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MODIFY_BY, $modifyby);
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MODIFY_DATE, $modifydate);
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CREATE_BY, $modifyby);
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CREATE_DATE, $modifydate);
                                $person_id = $clsPerson->addRecord($datas_person);
        
                                $datas_contact = array();
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_ID, $id);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_ACTION_DATE, NULL_DATE);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_DESIGNATION, $contact_designation);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_PERSON_CONTACT, $person_id);
                                $clsContact->addRecord($datas_contact);
                            }
                        }
                    }
                }

                if($numSheets > 1){
                    $allstatus = true;
                    foreach (array_filter($data_contact) as $value) {
                        $company        = $this->getParam($value, '0', "");
                        $designation    = $this->getParam($value, '1', "");
                        $name           = $this->getParam($value, '2', "");
                        $preferred_name = $this->getParam($value, '3', "");
                        $phone          = $this->getParam($value, '4', "");
                        $email          = $this->getParam($value, '5', "");

                        if ($name != "") {
                            $allstatus = false;
                            $mesg   = "There is an empty value inside Name field in Suspsect Contact";
                        }
                        
                        if ($business_sector != "") {
                            $allstatus = false;
                            $mesg   = "There is an empty value inside Phone field in Suspsect Contact";
                        }

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

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

                            return $ret_data;
                        }


                        $rows_prospect  = $clsProspect->getProspectByCompanyName($company);
                        $rows_contact   = $clsContact->getProspectContactByCompanyName($company, $name);

                        $datas_person = array();
                        $datas_person[] = $this->db->fieldValue(PersonContactTable::C_FULL_NAME, $name);
                        $datas_person[] = $this->db->fieldValue(PersonContactTable::C_PREFER_NAME, $preferred_name);
                        $datas_person[] = $this->db->fieldValue(PersonContactTable::C_EMAIL, $email);
                        $datas_person[] = $this->db->fieldValue(PersonContactTable::C_TELEPHONE, $phone);
                        $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MODIFY_BY, $modifyby);
                        $datas_person[] = $this->db->fieldValue(PersonContactTable::C_MODIFY_DATE, $modifydate);
                        
                        
                        if($rows_contact != null){
                            if($rows_contact[ProspectTable::C_STATUS] == ProspectSalesStatus::Raw){
                                $datas_contact = array();
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_DESIGNATION, $designation);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_MODIFY_BY, $modifyby);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_MODIFY_DATE, $modifydate);

                                $clsPerson->updateRecord($rows_contact[PersonContactTable::C_ID], $datas_person);
                                $clsContact->updateRecord($rows_contact[ProspectContactTable::C_CONTACT_ID], $datas_contact);
                            }
                        }else{
                            if($row_prospect != null){
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CREATE_BY, $modifyby);
                                $datas_person[] = $this->db->fieldValue(PersonContactTable::C_CREATE_DATE, $modifydate);
                                $person_id = $clsPerson->addRecord($datas_person);
                                
                                $datas_contact = array();
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_ID, $rows_prospect[ProspectTable::C_ID]);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_DESIGNATION, $designation);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_PERSON_CONTACT, $person_id);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_CREATE_BY, $modifyby);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_CREATE_DATE, $modifydate);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_MODIFY_BY, $modifyby);
                                $datas_contact[] = $this->db->fieldValue(ProspectContactTable::C_MODIFY_DATE, $modifydate);
                                $clsContact->addRecord($datas_contact);
                            }
                        }


                        $datas[] = $this->db->fieldValue(ProspectContactTable::C_ID, $rows_contact[ProspectTable::C_ID]);


                    }
                }
            }
            $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;
    }

    function endOfAction($params){
        $cls = new ProspectContactClass($this->db);
        $cls->clearActionDateAndNextAction($params['contact_id']);

        $this->sendJsonResponse(Status::Ok, "Successfully updated into database.", '', $this->type);
    }
}
