<?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/ProspectDeliveryClass.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\IOFactory;

class Prospect extends GLBase {
	private $type = "" ;
	private $user ;

	function __construct() {
		$this->db = $_SESSION[SE_DB] ;


	}
	function __destruct() {
		unset($this->user) ;

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

			switch ($this->type) {
				case Command::ProspectView:
					$this->getView($params) ;
					break ;
                case Command::ProspectListView:
                    $this->getProspectList($params);
                    break;
                case REQ_ADD:
                    $this->addRecord($params);
                    break;
                case REQ_UPDATE:
                    $this->updateRecord($params);
                    break;
                case REQ_DELETE:
                    $this->deleteProspectRecord($params);
                    break;
                //CUSTOMER CONTACT
                case Command::ProspectContactDelete:
                    $this->deleteProspectContact($params);
                    break;
                case Command::ProspectContactFilter:
                    $this->processContactFilter($params);
                    break;
                    //CUSTOMER DELIVERY
                case Command::ProspectProspectDeliveryDelete:
                    $this->deleteProspectDelivery($params);
                    break;
                case Command::ProspectProspectDeliveryFilter:
                    $this->processProspectDeliveryFilter($params);
                    break;
                //AUTO SEARCH
                case "PROSPECT_CODE_SEARCH_SALES_QUOTATION":
                    $this->getProspectSearchList($params);
                    break;
                case "C_GET_SALES_QUOTATION_PROSPECT_CODE":
                case "C_GET_SALES_QUOTATION_FILTER_PROSPECT_CODE":
                    $this->getProspectRecord($params);
                    break;
                case "UPDATE_PROSPECT_CONTACT_OPTION":
                    $this->updateProspectContactOption($params);
                    break;
                //IMPORT EXCEL
				case "C_DOWNLOAD_PROSPECT_EXCEL_TEMPLATE":
                    $this->getTemplate($params) ;
					break ;
				case "C_PROSPECT_UPLOAD_EXCEL":
                    $this->processProspectUploadExcel();
					break ;
				case "C_PROSPECT_READ_EXCEL":
                    $this->processProspectReadExcel($params) ;
                    break ;
                case "CONVERT_PROSPECT_CUSTOMER":
                    $this->convertToCustomer($params) ;
                    break ;
                case "C_DOWNLOAD_PROSPECT_DOCUMENT":
                    $this->processDownloadDocument($params) ;
                    break ;
                case "C_DELETE_DOCUMENT_PROSPECT":
                    $this->deleteDownloadDocument($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 deleteDownloadDocument($params)
    {
        $clsDocument = new ProspectDocumentClass($this->db);
        $id =  $this->getParamInt($params, 'id', 0);
        if ($id == 0) {
            $this->sendJsonResponse(Status::Error, "Invalid ID.", "", $this->type);
            return;
        }

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

          
            $clsDocument->deleteRecord($id);
            $this->db->commitTran();

            $this->sendJsonResponse(Status::Ok, "Successfully delete prospect document from system.", "", $this->type);
            return;
        } catch (Exception $e) {
            $this->db->rollbackTran();
            Log::write('[Prospect] ' . $e->getMessage());
            $this->sendJsonResponse(Status::Error, "Sorry, there is a error in deleting prospect document from the system.", "", $this->type);
            return;
        }
    }
    private function processDownloadDocument($params) {
	
		$id = $this->getParam($params,'id',"") ;

		$clsDocument = new ProspectDocumentClass($this->db);
        $document = $clsDocument->getRecord($id);
        if(!is_null($document) && count($document) > 0){
            $file_name = $document[ProspectDocumentTable::C_NAME];
            $file_extension = $document[ProspectDocumentTable::C_FILE_TYPE];
            
            $expfile = $document[ProspectDocumentTable::C_FILE];
          
            if ($expfile != "") {
                    $filename = basename($file_name);
                
                    switch( $file_extension ) {
                        case "gif": 
                            header('Content-type: image/gif'); 
                            break;
                        case "png": 
                            header('Content-type: image/png'); 
                            break;
                        case "jpeg":
                        case "jpg": 
                            header('Content-type: image/jpeg'); 
                            break;
                        case "pdf": 
                            header('Content-type: application/pdf'); 
                            break;
                        default:
                            header('Content-disposition: attachment; filename='."supporting_document");
                            header('Content-type: application/octet-stream'); 
                            break;
                    }
                    
                    header('Content-Description: View File');
                    header('Content-Transfer-Encoding: binary');
                    header('Expires: 0');
                    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
                    header('Pragma: public');
                 

                    ob_clean();
                    flush();
                    echo base64_decode($expfile);
                    exit;

                
            } else {
                echo "<tr><td colspan='12'>Missing document file info1.</td></tr>" ;
                return;
            }
        }else{
            echo "<tr><td colspan='12'>Missing document file info2.</td></tr>" ;
			return;
        }

		
	}
	private function getView($params) {
		ob_start() ;
		include (PATH_VIEWS . "prospect/ProspectView.php") ;

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

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

    private function getProspectList($params) {
        $cls = new ProspectClass($this->db);

        $sorts  = $params['sorts'];

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

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

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

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

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

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

    private function addRecord($params) {
        $clsProspect  = new ProspectClass($this->db);

        $datas        = array();
        $orgid        = $_SESSION[SE_ORGID];
        $modifyby     = $_SESSION[SE_USERID];
        $modifydate   = date_create('now')->format('Y-m-d H:i:s');
        $ws           = $_SESSION[SE_REMOTE_IP];
      
        $acc_exist = $this->getParam($params, 'account_exist', 0);

        $exist = $clsProspect->getProspectByCode($this->getParam($params, 'code', ""));

        if (!is_null($exist)) {
            $this->sendJsonResponse(Status::Error, "Prospect code already exist.", "", $this->type);
            return;
        }

        $datas[] = $this->db->fieldValue(ProspectTable::C_STATUS, $this->getParamInt($params, 'active', 1));
        $datas[] = $this->db->fieldValue(ProspectTable::C_NAME, $this->getParam($params, 'name', ""));
        $datas[] = $this->db->fieldValue(ProspectTable::C_CODE, $this->getParam($params, 'code', ""));
        $datas[] = $this->db->fieldValue(ProspectTable::C_TELEPHONE, $this->getParam($params, 'telephone', ""));
        $datas[] = $this->db->fieldValue(ProspectTable::C_UEN, $this->getParam($params, 'uen', ""));
        $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_1, $this->getParam($params, 'address1', ""));
        $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_2, "");
        $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_3, "");
        $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_4, "");
        $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_5, "");

        $datas[] = $this->db->fieldValue(ProspectTable::C_SOFTWARE_INFO, $this->getParam($params, 'software', ""));
        $datas[] = $this->db->fieldValue(ProspectTable::C_BILLING_CYCLE, $this->getParam($params, 'billing_cycle', ""));
        $datas[] = $this->db->fieldValue(ProspectTable::C_REMARKS, $this->getParam($params, 'remarks', ""));

        if($acc_exist==1) {
            //existing
            $datas[] = $this->db->fieldValue(ProspectTable::C_ACCOUNT, $this->getParamInt($params, 'account', null));
        } else {
            //new
            $datas[] = $this->db->fieldValue(ProspectTable::C_ACCOUNT, null);
        }

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

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

            //$exist = $clsProspect->getProspectByCode($this->getParam($params, 'code', ""));
           // if (!is_null($exist)) {
               // $this->sendJsonResponse(Status::Error, "Prospect code already exist.", "", $this->type);
          //  } else {
                $id = $clsProspect->addRecord($datas);
                if ($id > 0) {
                    //====================

                    $params['id'] = $id;

                    //prospect delivery
                    $mesg = "Prospect successfully updated to the system.";
                    if (isset($params['delivery'])) {
                        $result_delivery = $this->addProspectDelivery($params);
                        if ($result_delivery != "") {
                            $mesg .= $result_delivery;
                        }
                    }

                    //prospect contact
                    if (isset($params['contacts'])) {
                        $result = $this->addProspectContacts($params);
                        if ($result != "") {
                            $mesg .= "\n" . $result;
                        }
                    }
                    $this->db->commitTran();
                    $this->sendJsonResponse(Status::Ok, $mesg, $id, $this->type);
                } else {
                    $this->db->rollbackTran() ;	
                    $this->sendJsonResponse(Status::Error, "Sorry, there is a error in adding new prospect to the system.", "", $this->type);
                }
            //}
        } catch (Exception $e) {
            $this->db->rollbackTran() ;	
            Log::write('[Prospect]' . $e->getMessage());
            $this->sendJsonResponse(Status::Error, "Sorry, there is a error in database operation.", "", $this->type);
        }
        unset($clsProspect);
    }

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

			$cls = new ProspectClass($this->db) ;
			$clsContact = new ProspectContactClass($this->db);

            $datas = array() ;

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

            $acc_exist = $this->getParam($params, 'account_exist', 0);

            $datas[] = $this->db->fieldValue(ProspectTable::C_STATUS,$this->getParamInt($params,'active',1)) ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_NAME,$this->getParam($params,'name',"")) ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_CODE,$this->getParam($params,'code',"")) ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_TELEPHONE,$this->getParam($params,'telephone',"")) ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_UEN, $this->getParam($params, 'uen', ""));
            $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_1,$this->getParam($params,'address1',"")) ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_2, "") ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_3, "") ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_4, "") ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_5, "") ;

            $datas[] = $this->db->fieldValue(ProspectTable::C_SOFTWARE_INFO,$this->getParam($params,'software',"")) ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_BILLING_CYCLE,$this->getParam($params,'billing_cycle',"")) ;
            $datas[] = $this->db->fieldValue(ProspectTable::C_REMARKS,$this->getParam($params,'remarks',""));

            if ($acc_exist == 1) {
                //existing
                $datas[] = $this->db->fieldValue(ProspectTable::C_ACCOUNT, $this->getParamInt($params, 'account', null));
            } else {
                //new
                $datas[] = $this->db->fieldValue(ProspectTable::C_ACCOUNT, null);
            }

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

			try {
                $this->db->beginTran();
                
                $cls->updateRecord($id,$datas);

                //====================
                //prospect delivery
                $mesg = "Prospect successfully updated to the system.";
                if (isset($params['delivery'])) {
                    $result_delivery = $this->addProspectDelivery($params);
                    if($result_delivery!="") {
                        $mesg .= $result_delivery;
                    }
                }

                //prospect contact
                if(isset($params['contacts'])) {
                    $result = $this->addProspectContacts($params);
                    if($result!="") {
                        $mesg .= "\n".$result;
                    }
                }
                $this->db->commitTran();
                $this->sendJsonResponse(Status::Ok, $mesg,$id,$this->type) ;
			} catch (Exception $e) {
                $this->db->rollbackTran() ;	
				Log::write('[Prospect]' . $e->getMessage());
				$this->sendJsonResponse(Status::Error,"Sorry, there is a error in updating prospect detail to the system.","",$this->type) ;
			}
			unset($cls) ;
			unset($clsContact) ;
		}else {
            
			$this->sendJsonResponse(Status::Error,"You must supply the prospect id you wish to update. Please try again.","",$this->type);
		}
    }

    private function addProspectDelivery($params) {
        $clsDelivery = new ProspectDeliveryClass($this->db);
        $mesg = "";

        $custId = $params['id'];
        $custDelivery = $params['delivery'];
        if (!is_null($custDelivery) && count($custDelivery) > 0) {
            foreach ($custDelivery as $delivery) {
                $datas = array();
                $datas[] = $this->db->fieldValue(ProspectDeliveryTable::C_ID, $custId);
                $datas[] = $this->db->fieldValue(ProspectDeliveryTable::C_CODE, $delivery['code']);
                $datas[] = $this->db->fieldValue(ProspectDeliveryTable::C_ADDRESS, $delivery['address']);
                $datas[] = $this->db->fieldValue(ProspectDeliveryTable::C_CONTACT, $this->getParam($delivery, 'contact', null));

                $rowContact = $clsDelivery->getRecord($delivery['id']);
                if (!is_null($rowContact) && count($rowContact) > 0) {
                    if ($clsDelivery->updateRecord($delivery['id'], $datas)) {
                    } else {
                        if ($mesg == "")
                            $mesg = "Failed to insert delivery for: ";

                        $mesg .= $delivery['code'] . ", ";
                    }
                } else {
                    if ($clsDelivery->addRecord($datas) > 0) {
                    } else {
                        if ($mesg == "")
                            $mesg = "Failed to insert delivery for: ";

                        $mesg .= $delivery['code'] . ", ";
                    }
                }
            }
        }
        unset($clsDelivery);

        return $mesg;
    }

    private function addProspectContacts($params) {
        $clsContact = new ProspectContactClass($this->db);
        $mesg = "";

        $custId = $params['id'];
        $custContacts = $params['contacts'];
        if (!is_null($custContacts) && count($custContacts) > 0) {
            foreach ($custContacts as $contact) {
                $datas = array();
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_ID, $custId);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_NAME, $contact['name']);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_EMAIL, $contact['email']);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_TELEPHONE, $contact['phone']);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_MOBILE, $contact['mobile']);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_DESIGNATION, $contact['position']);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_DEPT, $contact['dept']);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_REPORT_TO, $contact['reportto']);
                $datas[] = $this->db->fieldValue(ProspectContactTable::C_ACTIVE, $contact['status']);

                $rowContact = $clsContact->getRecord($contact['id']);
                if (!is_null($rowContact) && count($rowContact) > 0) {
                    if ($clsContact->updateRecord($contact['id'], $datas)) {
                    } else {
                        if ($mesg == "")
                            $mesg = "Failed to insert contact for: ";

                        $mesg .= $contact['name'] . ", ";
                    }
                } else {
                    if ($clsContact->addRecord($datas) > 0) {
                    } else {
                        if ($mesg == "")
                            $mesg = "Failed to insert contact for: ";

                        $mesg .= $contact['name'] . ", ";
                    }
                }
            }
        }
        unset($clsContact);

        return $mesg;
    }

    private function deleteProspectRecord($params) 
    {
        $cls = new ProspectClass($this->db);
        $clsContact = new ProspectContactClass($this->db);
        $clsDelivery = new ProspectDeliveryClass($this->db);
        $clsDocument = new ProspectDocumentClass($this->db);
        $id =  $this->getParamInt($params, 'id', 0);
        if ($id == 0) {
            $this->sendJsonResponse(Status::Error, "Invalid ID.", "", $this->type);
            return;
        }

        try {
            $this->db->beginTran();
            $clsDelivery->deleteRecord($id);
            $clsContact->deleteRecord($id);
            $clsDocument->deleteRecord($id);
            $cls->deleteRecord($id);
            $this->db->commitTran();

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

        unset($cls);
    }

    //=============================================================================================
    //CUSTOMER CONTACT
    private function deleteProspectContact($params) {
        if (isset($params['id'])) {
			$clsContact = new ProspectContactClass($this->db);

            $custId     = $params['cust_id'] ;
            $contactId  = $params['id'];

            try {
                $this->db->beginTran();
    
                $clsContact->deleteProspectContactByContactId($custId, $contactId);
    
                $this->db->commitTran();
                $this->sendJsonResponse(Status::Ok, "Successfully delete prospect contact from system.", "", $this->type);
            } catch (Exception $e) {
                $this->db->rollbackTran() ;
                if($e->getMessage() == Message::DB_ERR_DELETE_USED){
                    $this->sendJsonResponse(Status::Error, Message::DB_ERR_DELETE_USED,"",$this->type);
                }else{
                    Log::write('[Prospect] ' . $e->getMessage());
                    $this->sendJsonResponse(Status::Error,"Sorry, there is a error in deleting prospect contact from the system.","",$this->type);
                }
                return;
            }

			unset($clsContact) ;
        } else {
            $this->sendJsonResponse(Status::Error,"You must supply the prospect contact id if you wish to delete. Please try again.","",$this->type);
        }
    }

    private function processContactFilter($params) {
        //contact
        $clsContact = new ProspectContactClass($this->db);

        $custId = $params['cust_id'];
        $sorts  = $params['sorts'];

        if ($custId == "") {
            $this->sendJsonResponse(Status::Ok, "", "", $this->type);
            return;
        }

        $filter = "";
        $orderby = "";
        $sort = array();
        if ($sorts != "") {
            $sort = explode(':', $sorts);
            switch ($sort[0]) {
                case "col_contactname":
                    $orderby = ProspectContactTable::C_NAME . " " . $sort[1];
                    break;
                case "col_contactdept":
                    $orderby = ProspectContactTable::C_DEPT . " " . $sort[1];
                    break;
                case "col_contactposition":
                    $orderby = ProspectContactTable::C_DESIGNATION . " " . $sort[1];
                    break;
                case "col_contactemail":
                    $orderby = ProspectContactTable::C_EMAIL . " " . $sort[1];
                    break;
                case "col_contactphone":
                    $orderby = ProspectContactTable::C_TELEPHONE . " " . $sort[1];
                    break;
                case "col_contactmobile":
                    $orderby = ProspectContactTable::C_MOBILE . " " . $sort[1];
                    break;
                case "col_contactreportto":
                    $orderby = ProspectContactTable::C_REPORT_TO . " " . $sort[1];
                    break;
                case "col_contactstatus":
                    $orderby = ProspectContactTable::C_ACTIVE . " " . $sort[1];
                    break;
                default:
                    $orderby = ProspectContactTable::C_CONTACT_ID;
                    break;
            }
        } else {
            $orderby = ProspectContactTable::C_CONTACT_ID;
        }
        $contacts = $clsContact->getProspectContact($custId, $orderby);
        $dataReturn['contactIdx'] = 0;
        $htmlStr    = "";
        if (!is_null($contacts) && count($contacts) > 0) {
            $dataReturn['contactIdx'] = count($contacts);
            $counter = 0;
            foreach ($contacts as $contact) {
                $counter++;
                $htmlStr .= '<tr data-row="contact-rows-' . $counter . '" id="contact-rows-' . $counter . '" class="dnd-moved kt-datatable__row" style="left: 0px;">
                                <td data-field="contactid" class="kt-datatable__cell" style="display:none;" id="col_contactid' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcolcontactid' . $counter . '" value="' . $contact[ProspectContactTable::C_CONTACT_ID] . '" /></td>
                                <td data-field="contactname" class="kt-datatable__cell" id="col_contactname' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcolcontactname' . $counter . '" value="' . $contact[ProspectContactTable::C_NAME] . '" /></td>
                                <td data-field="contactdept" class="kt-datatable__cell" id="col_contactdept' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcolcontactdept' . $counter . '" value="' . trim($contact[ProspectContactTable::C_DEPT]) . '" /></td>
                                <td data-field="contactposition" class="kt-datatable__cell" id="col_contactposition' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcolcontactposition' . $counter . '" value="' . trim($contact[ProspectContactTable::C_DESIGNATION]) . '" /></td>
                                <td data-field="contactemail" class="kt-datatable__cell" id="col_contactemail' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcolcontactemail' . $counter . '" value="' . trim($contact[ProspectContactTable::C_EMAIL]) . '" /></td>
                                <td data-field="contactphone" class="kt-datatable__cell" id="col_contactphone' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcolcontactphone' . $counter . '" value="' . trim($contact[ProspectContactTable::C_TELEPHONE]) . '" /></td>
                                <td data-field="contactmobile" class="kt-datatable__cell" id="col_contactmobile' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcolcontactmobile' . $counter . '" value="' . trim($contact[ProspectContactTable::C_MOBILE]) . '" /></td>
                                <td data-field="contactreportto" class="kt-datatable__cell" id="col_contactreportto' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcolcontactreportto' . $counter . '" value="' . trim($contact[ProspectContactTable::C_REPORT_TO]) . '" /></td>
                                <td data-field="contactstatus" class="kt-datatable__cell" id="col_contactstatus' . $counter . '">
                                    <input type="hidden" class="form-control" value="' . $contact[ProspectContactTable::C_ACTIVE] . '" />
                                    <select class="form-control" name="input_row" id="txtcolcontactstatus' . $counter . '">
                                        <option value="1" ' . ($contact[ProspectContactTable::C_ACTIVE] == 1 ? 'selected' : '') . '>Active</option>
                                        <option value="0" ' . ($contact[ProspectContactTable::C_ACTIVE] == 0 ? 'selected' : '') . '>Inactive</option>
                                    </select>
                                </td>
                                <td data-field="filter" class="kt-datatable__cell" id="col_filter' . $counter . '">
                                    <button class="btn btn-danger btn-sm btn-icon btn-icon-md" type="button" title="Delete" onclick="deleteProspectContact(' . $contact[ProspectContactTable::C_CONTACT_ID] . ',this);">
                                        <i class="fa fa-trash-o"></i>
                                    </button>
                                </td>
                            </tr>';
            }
        }

        $dataReturn['htmlContact'] = $htmlStr;
        $dataReturn['sortedContact'] = $contacts;
        $dataReturn['sortType'] = $sort;

        $this->sendJsonResponse(Status::Ok, "Edit Prospect Master", $dataReturn, $this->type);
        unset($clsContact);
        unset($contacts);
    }
    //=============================================================================================

    //=============================================================================================
    //CUSTOMER DELIVERY
    private function deleteProspectDelivery($params){
        if (isset($params['id'])) {
            $custId = $params['cust_id'];
            $deliveryId = $params['id'];
            $clsDelivery = new ProspectDeliveryClass($this->db);

            //check whether delivery is being used on DO
            $do_exist = false;

            if ($do_exist == true) {
                $this->sendJsonResponse(Status::Error, "Failed, The delivery is being used on another module.", "", $this->type);
            } else {
                try {
                    $clsDelivery->deleteProspectDeliveryByDeliveryId($custId, $deliveryId);
                    $this->sendJsonResponse(Status::Ok, "Prospect delivery successfully deleted from the system.", $deliveryId, $this->type);
                } catch (Exception $e) {
                    Log::write('[ProspectDelivery]' . $e->getMessage());
                    $this->sendJsonResponse(Status::Error, "Sorry, there is a problem in deleting prospect delivery record from the system.", "", $this->type);
                }
            }

            unset($clsDelivery);
        } else {
            $this->sendJsonResponse(Status::Error, "You must supply the prospect delivery id if you wish to delete. Please try again.", "", $this->type);
        }
    }

    private function processProspectDeliveryFilter($params) {
        $clsDelivery = new ProspectDeliveryClass($this->db);

        $custId = $params['cust_id'];
        $sorts  = $params['sorts'];

        if ($custId == "") {
            $this->sendJsonResponse(Status::Ok, "", "", $this->type);
            return;
        }

        $filter = "";
        $orderby = "";
        $sort = array();
        if ($sorts != "") {
            $sort = explode(':', $sorts);
            switch ($sort[0]) {
                case "col_deliverycode":
                    $orderby = ProspectDeliveryTable::C_CODE . " " . $sort[1];
                    break;
                case "col_deliveryaddress":
                    $orderby = ProspectDeliveryTable::C_ADDRESS . " " . $sort[1];
                    break;
                case "col_deliverycontact":
                    $orderby = ProspectDeliveryTable::C_CONTACT . " " . $sort[1];
                    break;
                default:
                    $orderby = ProspectDeliveryTable::C_CODE;
                    break;
            }
        } else {
            $orderby = ProspectDeliveryTable::C_DELIVERY_ID;
        }

        $delivery = $clsDelivery->getProspectDelivery($custId, $orderby);
        $dataReturn['deliveryIdx'] = 0;
        $htmlStr    = "";
        if (!is_null($delivery) && count($delivery) > 0) {
            $dataReturn['deliveryIdx'] = count($delivery);
            $counter = 0;
            foreach ($delivery as $dev) {
                $counter++;
                $htmlStr .= '<tr data-row="delivery-rows-' . $counter . '" id="delivery-rows-' . $counter . '" class="dnd-moved kt-datatable__row" style="left: 0px;">
                                <td data-field="deliveryid" class="kt-datatable__cell" style="display:none;" id="col_deliveryid' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcoldeliveryid' . $counter . '" value="' . $dev[ProspectDeliveryTable::C_DELIVERY_ID] . '" /></td>
                                <td data-field="deliverycode" class="kt-datatable__cell" id="col_deliverycode' . $counter . '">
                                    <input type="text" class="form-control" name="input_row" id="txtcoldeliverycode' . $counter . '" value="' . $dev[ProspectDeliveryTable::C_CODE] . '" /></td>
                                <td data-field="deliveryaddress" class="kt-datatable__cell" id="col_deliveryaddress' . $counter . '">
                                    <textarea style="overflow-y: auto;" class="form-control" name="input_row" id="txtcoldeliveryaddress' . $counter . '">'.trim($dev[ProspectDeliveryTable::C_ADDRESS]).'</textarea></td>
                                <td data-field="deliverycontact" class="kt-datatable__cell" id="col_deliverycontact' . $counter . '">
                                    <input type="hidden" class="form-control" value="' . $dev[ProspectDeliveryTable::C_CONTACT] . '" />
                                    <select class="form-control cmbColDeliveryContact" name="input_row" id="txtcoldeliverycontact' . $counter . '">
                                       '. $this->getProspectContactOption($dev[ProspectDeliveryTable::C_ID], $dev[ProspectDeliveryTable::C_CONTACT]) .'
                                    </select>
                                </td>
                                <td data-field="filter" class="kt-datatable__cell" id="col_filter' . $counter . '">
                                    <button class="btn btn-danger btn-sm btn-icon btn-icon-md" type="button" title="Delete" onclick="deleteProspectDelivery(' . $dev[ProspectDeliveryTable::C_DELIVERY_ID] . ',this);">
                                        <i class="fa fa-trash-o"></i>
                                    </button>
                                </td>
                            </tr>';
            }
        }

        $dataReturn['htmlDelivery'] = $htmlStr;

        $this->sendJsonResponse(Status::Ok, "Edit Prospect Master", $dataReturn, $this->type);
        unset($clsDelivery);
        unset($delivery);
    }
    //=============================================================================================

    //=============================================================================================
    //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 **/
		$spreadsheet = new Spreadsheet();
        $title = "Prospect Template";

        $filename = PATH_PROSPECT_EXCEL . "template-prospect.xlsx";
        $column = 2;

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

        //TITLE
		$spreadsheet->setActiveSheetIndex(0);
		$worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle($title);
        $worksheet->setCellValue("A1", "Prospect Code");
        $worksheet->getStyle("A1")->applyFromArray($style_title);
        $worksheet->setCellValue("B1", "Prospect Name");
        $worksheet->getStyle("B1")->applyFromArray($style_title);
        $worksheet->setCellValue("C1", "Telephone");
        $worksheet->getStyle("C1")->applyFromArray($style_title);
        $worksheet->setCellValue("D1", "Billing Cycle");
        $worksheet->getStyle("D1")->applyFromArray($style_title);
        $worksheet->setCellValue("E1", "Software Information");
        $worksheet->getStyle("E1")->applyFromArray($style_title);
        $worksheet->setCellValue("F1", "Address");
        $worksheet->getStyle("F1")->applyFromArray($style_title);
        $worksheet->setCellValue("G1", "Remarks");
        $worksheet->getStyle("G1")->applyFromArray($style_title);
        $worksheet->setCellValue("H1", "Prospect Status");
        $worksheet->getStyle("H1")->applyFromArray($style_title);

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

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

        //TITLE
        $worksheet->setCellValue("A1", "Prospect Code");
        $worksheet->getStyle("A1")->applyFromArray($style_title);
        $worksheet->setCellValue("B1", "Contact Name");
        $worksheet->getStyle("B1")->applyFromArray($style_title);
        $worksheet->setCellValue("C1", "Department");
        $worksheet->getStyle("C1")->applyFromArray($style_title);
        $worksheet->setCellValue("D1", "Designation");
        $worksheet->getStyle("D1")->applyFromArray($style_title);
        $worksheet->setCellValue("E1", "Email");
        $worksheet->getStyle("E1")->applyFromArray($style_title);
        $worksheet->setCellValue("F1", "Telephone");
        $worksheet->getStyle("F1")->applyFromArray($style_title);
        $worksheet->setCellValue("G1", "Mobile");
        $worksheet->getStyle("G1")->applyFromArray($style_title);
        $worksheet->setCellValue("H1", "Report To");
        $worksheet->getStyle("H1")->applyFromArray($style_title);
        $worksheet->setCellValue("I1", "Status");
        $worksheet->getStyle("I1")->applyFromArray($style_title);

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

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

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

        //TITLE
        $worksheet->setCellValue("A1", "Prospect Code");
        $worksheet->getStyle("A1")->applyFromArray($style_title);
        $worksheet->setCellValue("B1", "Delivery Code");
        $worksheet->getStyle("B1")->applyFromArray($style_title);
        $worksheet->setCellValue("C1", "Delivery Address");
        $worksheet->getStyle("C1")->applyFromArray($style_title);
        $worksheet->setCellValue("D1", "Delivery Contact");
        $worksheet->getStyle("D1")->applyFromArray($style_title);

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

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

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

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

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

         //Account Type
        $worksheet->setCellValue("C3", "Account Type : ");
        $worksheet->getStyle("C3")->applyFromArray($style_title);
        $worksheet->setCellValue("C4", "Value");
        $worksheet->getStyle("C4")->applyFromArray($style_label);
        $worksheet->setCellValue("C5", "Balance Sheet");
        $worksheet->getStyle("C5")->applyFromArray($style_hint);
        $worksheet->setCellValue("C6", "P&L");
        $worksheet->getStyle("C6")->applyFromArray($style_hint);
        $worksheet->setCellValue("C7", "Debtor");
        $worksheet->getStyle("C7")->applyFromArray($style_hint);
        $worksheet->setCellValue("C8", "Creditor");
        $worksheet->getStyle("C8")->applyFromArray($style_hint);
        $worksheet->setCellValue("C9", "Client");
        $worksheet->getStyle("C9")->applyFromArray($style_hint);

        //Account Status
        $worksheet->setCellValue("F3", "Account Status : ");
        $worksheet->getStyle("F3")->applyFromArray($style_title);
        $worksheet->setCellValue("F4", "Value");
        $worksheet->getStyle("F4")->applyFromArray($style_label);
        $worksheet->setCellValue("F5", "Open");
        $worksheet->getStyle("F5")->applyFromArray($style_hint);
        $worksheet->setCellValue("F6", "Draft");
        $worksheet->getStyle("F6")->applyFromArray($style_hint);

        //Contact Status
        $worksheet->setCellValue("H3", "Contact Status : ");
        $worksheet->getStyle("H3")->applyFromArray($style_title);
        $worksheet->setCellValue("H4", "Value");
        $worksheet->getStyle("H4")->applyFromArray($style_label);
        $worksheet->setCellValue("H5", "Active");
        $worksheet->getStyle("H5")->applyFromArray($style_hint);
        $worksheet->setCellValue("H6", "Inactive");
        $worksheet->getStyle("H6")->applyFromArray($style_hint);

        //========================================================================
        //check if directory exist if not then create
		$directory = PATH_PROSPECT_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 processProspectUploadExcel()
    {
        $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["uploadFileProspect"])) {
            $resultTemp = true;
            $size       = $_FILES["uploadFileProspect"]['size'][0];
            $fileName  = $_FILES["uploadFileProspect"]["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_PROSPECT_EXCEL . "temp-" . $userId . "/";
            $newName   = "prospect_excel_doc_" . $userId . "." . $ext;
            $newfile   = $directory . $newName;
            $fileUpload = $_FILES["uploadFileProspect"]['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 processProspectReadExcel($params)
    {
        $values = $this->processReadExcel($_SESSION[SE_USERID], $params['new_name']);

        if ($values['status']) {
            $this->sendJsonResponse(Status::Ok, "Success import prospect 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);
        $clsContact  = new ProspectContactClass($this->db);

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

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

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

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

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

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

            //================================================
            //GET 2ND SHEET DATA
		    $spreadsheet->setActiveSheetIndex(1);
			$worksheet = $spreadsheet->getActiveSheet();
            $highestColumm2 = $worksheet->getHighestColumn(); // e.g. "EL"
            $highestRow2 = $worksheet->getHighestRow();

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

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

                if ($dataset2 != "") {
                    $data2[] = array_filter($dataset2);
                }
            }

            //================================================
            //GET 3RD SHEET DATA
		    $spreadsheet->setActiveSheetIndex(2);
			$worksheet = $spreadsheet->getActiveSheet();
            $highestColumm3 = $worksheet->getHighestColumn(); // e.g. "EL"
            $highestRow3 = $worksheet->getHighestRow();

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

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

                if ($dataset3 != "") {
                    $data3[] = array_filter($dataset3);
                }
            }

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

                    $cust_code         = $this->getParam($value, '0', "");
                    $cust_name         = $this->getParam($value, '1', "");
                    $phone             = $this->getParam($value, '2', "");
                    $billing           = $this->getParam($value, '3', "");
                    $software          = $this->getParam($value, '4', "");
                    $address           = $this->getParam($value, '5', "");
                    $remarks           = $this->getParam($value, '6', "");
                    $cust_status       = $this->getParam($value, '7', "");
                    // $acc_code          = $this->getParam($value, '8', "");
                    // $acc_desc          = $this->getParam($value, '9', "");
                    // $acc_type          = $this->getParam($value, '10', "");
                    // $acc_group         = $this->getParam($value, '11', "");
                    // $acc_status        = $this->getParam($value, '12', "");
                    // $payment_terms     = $this->getParam($value, '13', "");
                    // $coy_reg           = $this->getParam($value, '14', "");
                    // $tax_reg           = $this->getParam($value, '15', "");

                    //prospect status
					$cust_status_id = '1'; //open
					if(strtolower($cust_status)=="inactive") {
						$cust_status_id = '0';
					} else if(strtolower($cust_status)=="active") {
                        $cust_status_id = '1';
					}

                    //account status
                    // $acc_status_id = '1'; //open
                    // if (strtolower($acc_status) == "draft") {
                    //     $acc_status_id = '0';
                    // } else if (strtolower($acc_status) == "open") {
                    //     $acc_status_id = '1';
                    // }

                    // //account type
                    // $acc_type_id = '0'; //open
                    // if (strtolower($acc_type) == "balance sheet") {
                    //     $acc_type_id = '1';
                    // } else if (strtolower($acc_type) == "p&l" || strtolower($acc_type) == 'p & l') {
                    //     $acc_type_id = '2';
                    // } else if (strtolower($acc_type) == "debtor") {
                    //     $acc_type_id = '3';
                    // } else if (strtolower($acc_type) == "creditor") {
                    //     $acc_type_id = '4';
                    // } else if (strtolower($acc_type) == "client") {
                    //     $acc_type_id = '5';
                    // }

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

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

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

                    // if ($acc_code == "") {
                    //     $allstatus = false;
                    //     $column = "Account Code";
                    // }

                    //===========
                    //check if acc code not exist, check if any other required field is empty
                    // $acc_exist = false;
                    // if ($acc_code != "") {
                    //     $acc_exist = $clsAccount->isExistByCode($acc_code);
                    //     if (!$acc_exist) {
                    //         if ($acc_desc == "") {
                    //             $allstatus = false;
                    //             $column = "Account Description";
                    //         }

                    //         if ($acc_group == "") {
                    //             $allstatus = false;
                    //             $column = "Account Group";
                    //         }

                    //         if ($acc_status == "") {
                    //             $allstatus = false;
                    //             $column = "Account Status";
                    //         }

                    //         if ($payment_terms == "" && $acc_type_id > 2) {
                    //             $allstatus = false;
                    //             $column = "Payment Terms";
                    //         }
                    //     }
                    // }

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

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

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

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

                    // if($acc_code!="") {
                    //     $acc_id = $clsAccount->getIdByCode($acc_code);
                    //     if ($acc_id=="") {
                    //         $allid = false;
                    //         $column = "Account Code";
                    //     }
                    // }

                    // $group_id = 0;
                    // if($acc_group!="") {
                    //     $group_id = $clsAccGroup->getIdByCode($acc_group);
                    //     if ($group_id == "") {
                    //         $allid = false;
                    //         $column = "Account Group";
                    //     }
                    // }

                    // $payment_id = 0;
                    // if($payment_terms!="") {
                    //     $payment_id = $clsPayment->getIdByCode($payment_terms);
                    //     if ($payment_id == "") {
                    //         $allid = false;
                    //         $column = "Payment Terms";
                    //     }
                    // }

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

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

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

                    $datas = array();
                    $datas[] = $this->db->fieldValue(ProspectTable::C_CODE, $cust_code);
                    $datas[] = $this->db->fieldValue(ProspectTable::C_NAME, $cust_name);
                    $datas[] = $this->db->fieldValue(ProspectTable::C_TELEPHONE, $phone);
                    $datas[] = $this->db->fieldValue(ProspectTable::C_STATUS, $cust_status_id);

                    $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_1, $address);
                    $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_2, "");
                    $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_3, "");
                    $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_4, "");
                    $datas[] = $this->db->fieldValue(ProspectTable::C_ADDRESS_5, "");

                    $datas[] = $this->db->fieldValue(ProspectTable::C_SOFTWARE_INFO, $software);
                    $datas[] = $this->db->fieldValue(ProspectTable::C_BILLING_CYCLE, $billing);
                    $datas[] = $this->db->fieldValue(ProspectTable::C_REMARKS, $remarks);

                    // if ($acc_exist) {
                    //     //existing
                    //     $datas[] = $this->db->fieldValue(ProspectTable::C_ACCOUNT, $acc_id);
                    // } else {
                        //new
                    $datas[] = $this->db->fieldValue(ProspectTable::C_ACCOUNT, null);                        
                    // }

                    $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);
                    // Log::write($cust_code . " - " . $acc_code . "(". $acc_id .") - " . $address);
                    // Log::write(print_r($datas,true));

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

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

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

                    $cust_code        = $this->getParam($value2, '0', "");
                    $contact_name     = $this->getParam($value2, '1', "");
                    $dept             = $this->getParam($value2, '2', "");
                    $designation      = $this->getParam($value2, '3', "");
                    $contact_email    = $this->getParam($value2, '4', "");
                    $contact_phone    = $this->getParam($value2, '5', "");
                    $contact_mobile   = $this->getParam($value2, '6', "");
                    $report_to        = $this->getParam($value2, '7', "");
                    $contact_status   = $this->getParam($value2, '8', "");

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

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

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

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

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

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

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

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

                    $cust_id = $clsProspect->getIdByCode($cust_code);

                    if ($cust_id == "") {
                        $allid = false;
                        $column = "Prospect Code";
                    }

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

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

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

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

                    if (!is_null($existing_cust) && $existing_cust != "") {
                        //update prospect contact
                        $this->insertToProspectContactTable($cust_id, $contact_name, $dept, $designation, $contact_email, $contact_phone,
                                                            $contact_mobile, $report_to, $contact_status_id);
                        //====================
                        // $this->db->commitTran();
                    } else {
                        $this->db->rollbackTran();

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

                        return $ret_data;
                    }

                }

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

                    $cust_code        = $this->getParam($value3, '0', "");
                    $delivery_code    = $this->getParam($value3, '1', "");
                    $delivery_address = $this->getParam($value3, '2', "");
                    $delivery_contact = $this->getParam($value3, '3', "");

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

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

                    if ($delivery_code == "") {
                        $allstatus = false;
                        $column = "Delivery Code";
                    }

                    if ($delivery_address == "") {
                        $allstatus = false;
                        $column = "Delivery Address";
                    }

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

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

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

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

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

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

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

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

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

                    $existing_cust = $clsProspect->getExistingRecords($cust_code);
                    if (!is_null($existing_cust) && $existing_cust != "") {
                        //update prospect contact
                        $this->insertToProspectDeliveryTable($cust_id, $delivery_code, $delivery_address, $contact_id);
                        //====================

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

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

                        return $ret_data;
                    }

                }
            }

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

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

        return $ret_data;
    }

    private function insertToProspectContactTable($cust_id, $contact_name, $dept, $designation, $contact_email, $contact_phone,
                                                $contact_mobile, $report_to, $contact_status_id) {

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

        $datas = array();
        $datas[] = $this->db->fieldValue(ProspectContactTable::C_ID, $cust_id);
        $datas[] = $this->db->fieldValue(ProspectContactTable::C_NAME, $contact_name);
        $datas[] = $this->db->fieldValue(ProspectContactTable::C_EMAIL, $contact_email);
        $datas[] = $this->db->fieldValue(ProspectContactTable::C_TELEPHONE, $contact_phone);
        $datas[] = $this->db->fieldValue(ProspectContactTable::C_MOBILE, $contact_mobile);
        $datas[] = $this->db->fieldValue(ProspectContactTable::C_DESIGNATION, $designation);
        $datas[] = $this->db->fieldValue(ProspectContactTable::C_DEPT, $dept);
        $datas[] = $this->db->fieldValue(ProspectContactTable::C_REPORT_TO, $report_to);
        $datas[] = $this->db->fieldValue(ProspectContactTable::C_ACTIVE, $contact_status_id);

        $existing_contact = $clsContact->getExistingRecords($cust_id, $contact_name);
        // Log::write($existing_contact);
        if ($existing_contact == "") {
            $clsContact->addRecord($datas);
        } else {
            $id = $existing_contact[0][ProspectContactTable::C_CONTACT_ID];
            $clsContact->updateRecord($id, $datas);
        }

    }

    private function insertToProspectDeliveryTable($cust_id, $delivery_code, $delivery_address, $delivery_contact) {
        $clsDelivery = new ProspectDeliveryClass($this->db);

        $datas = array();
        $datas[] = $this->db->fieldValue(ProspectDeliveryTable::C_ID, $cust_id);
        $datas[] = $this->db->fieldValue(ProspectDeliveryTable::C_CODE, $delivery_code);
        $datas[] = $this->db->fieldValue(ProspectDeliveryTable::C_ADDRESS, $delivery_address);
        $datas[] = $this->db->fieldValue(ProspectDeliveryTable::C_CONTACT, $delivery_contact);

        $existing_delivery = $clsDelivery->getExistingRecords($cust_id, $delivery_code);
        // Log::write($existing_delivery);
        if ($existing_delivery == "") {
            $clsDelivery->addRecord($datas);
        } else {
            $id = $existing_delivery[0][ProspectDeliveryTable::C_DELIVERY_ID];
            $clsDelivery->updateRecord($id, $datas);
        }
    }

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

    private function getProspectRecord($params) {

        $clsCust = new ProspectClass($this->db);

        $sql = "SELECT " . ProspectTable::C_ID . ", " . ProspectTable::C_ACCOUNT . ", " . ProspectTable::C_NAME . ", " . ProspectTable::C_CODE .
                " FROM " . ProspectTable::C_TABLE;
        $sql .= " WHERE " . ProspectTable::C_CODE . " LIKE '%" . trim($params['code']) . "%' ";
        $rows = $this->db->getTable($sql);

        $account_id = 0;
        $id = 0;
        if (!is_null($rows) && count($rows) > 0) {
            $id = $rows[0][ProspectTable::C_ID];
            $account_id = $rows[0][ProspectTable::C_ACCOUNT];
        }

        $datas = [
            'cust_id' => intVal($rows[0][ProspectTable::C_ID]),
            'cust_name' => $rows[0][ProspectTable::C_NAME],
            'cust_code' => $rows[0][ProspectTable::C_CODE],
            'cust_account' => $rows[0][ProspectTable::C_ACCOUNT],
        ];

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

    private function getProspectSearchList($params) {
        $clsCust = new ProspectClass($this->db);

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

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

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

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

    function updateProspectContactOption($params) {
        $contact = $this->getProspectContactOption($params['cust_id']);

        $this->sendJsonResponse(Status::Ok, "Success", $contact, $this->type);
    }
    public function convertToCustomer($params)
    {
        if (isset($params['id'])) {
            $cls        = new ProspectClass($this->db);
            $clsContact = new ProspectContactClass($this->db);
            $clsDelivery= new ProspectDeliveryClass($this->db);
            $clsCustomer= new CustomerClass($this->db);

            $id         = $params['id'];
            $orgid      = $_SESSION[SE_ORGID];
            $modifyby   = $_SESSION[SE_USERID];
            $modifydate = date_create('now')->format('Y-m-d H:i:s');
            $ws         = $_SESSION[SE_REMOTE_IP];
            try {            
                $this->db->beginTran();
                $row = $cls->getRecord($id);
                if(!is_null($row) && count($row)>0){
                    if($row[ProspectTable::C_CODE] == ''){
                        $this->sendJsonResponse(Status::Error, "please assign prospect code before converting to customer.", "", $this->type);
                        return;
                    }else{
                        $exist = $clsCustomer->isExistByCode($row[ProspectTable::C_CODE]);
                        if ($exist) {
                            $this->sendJsonResponse(Status::Error, "Customer code already exist.", "", $this->type);
                            return;
                        } else {
                            $datas   = array();
                            $datas[] = $this->db->fieldValue(CustomerTable::C_STATUS, 1);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_NAME, $row[ProspectTable::C_NAME]);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_CODE, $row[ProspectTable::C_CODE]);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_BILLING_CYCLE, $row[ProspectTable::C_BILLING_CYCLE]);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_ADDRESS, $row[ProspectTable::C_ADDRESS_1]);

                            $datas[] = $this->db->fieldValue(CustomerTable::C_TELEPHONE, $row[ProspectTable::C_TELEPHONE]);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_SOFTWARE_INFO,$row[ProspectTable::C_SOFTWARE_INFO]);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_REMARKS, $row[ProspectTable::C_REMARKS]);
                    
                            //new
                            $datas[] = $this->db->fieldValue(CustomerTable::C_CUS_ACC, "");                          
                            $datas[] = $this->db->fieldValue(CustomerTable::C_ORG_ID, $orgid);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_WS_ID, $ws);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_MODIFY_BY, $modifyby);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_CREATE_BY, $modifyby);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_MODIFY_DATE, $modifydate);
                            $datas[] = $this->db->fieldValue(CustomerTable::C_CREATE_DATE, $modifydate);

                            $cust_id = $clsCustomer->addRecord($datas);
                            if ($cust_id > 0) {
                                //customer contact
                                $mesg = "Customer successfully updated to the system.";
                                $rows_contact=  $clsContact->getProspectContact($id);
                                if (!is_null($rows_contact )) {
                                    $result = $this->addCustomerContacts($cust_id,$rows_contact);
                                    if ($result != "") {
                                        $mesg .= "\n" . $result;
                                    }
                                }
                                
                                $rows_delivery=  $clsDelivery->getProspectDelivery($id);
                                if (!is_null($rows_delivery )) {
                                    $result_delivery = $this->addCustomerBranch($cust_id,$rows_delivery);
                                    if ($result_delivery != "") {
                                        $mesg .= $result_delivery;
                                    }
                                }            
                            }

                            $cls->updateStatusConvertToCustomer($id);
                        }
                    }                                        
                }
                $this->db->commitTran();
                $this->sendJsonResponse(Status::Ok, "Successfully converted prospect into customer", "", $this->type);
                return;
            }catch(Exception $e){
                Log::write('[Convert to Customer]' . $e->getMessage());
                $this->db->rollbackTran();
                $this->sendJsonResponse(Status::Error, "Cannot perform this action now.", "", $this->type);
                return;
            }                    
        }else{
            $this->sendJsonResponse(Status::Error, "Cannot perform this action now.", "", $this->type);
            return;
        }
    }

    private function addCustomerBranch($custId,$rows_contact) {
        $clsProspectContact = new ProspectContactClass($this->db);
        $clsDelivery = new CustomerBranchClass($this->db);
        $clsContact = new CustomerContactClass($this->db);
        $mesg = "";

        $orgid      = $_SESSION[SE_ORGID];
        $modifyby   = $_SESSION[SE_USERID];
        $modifydate = date_create('now')->format('Y-m-d H:i:s');
        $ws         = $_SESSION[SE_REMOTE_IP];
       
        $custDelivery = $rows_contact;
        if (!is_null($custDelivery) && count($custDelivery) > 0) {
            foreach ($custDelivery as $delivery) {
                $row_prospect = $clsProspectContact->getRecord($delivery[ProspectDeliveryTable::C_CONTACT]);
                $customer_contact_id = $clsContact->getIdByContactName($custId, $row_prospect[ProspectContactTable::C_NAME]);
                
                $datas = array();
                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_ID, $custId);
                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_NAME, $delivery[ProspectDeliveryTable::C_CODE]);
                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_ADD, $delivery[ProspectDeliveryTable::C_ADDRESS]);
                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CONTACT, $customer_contact_id);

                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_ORG_ID, $orgid);
                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_WS_ID, $ws);
                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_MODIFY_BY, $modifyby);
                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CREATE_BY, $modifyby);
                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_MODIFY_DATE, $modifydate);
                $datas[] = $this->db->fieldValue(CustomerBranchTable::C_CREATE_DATE, $modifydate);
                
                if ($clsDelivery->addRecord($datas) > 0) {
                } else {
                    if ($mesg == "")
                        $mesg = "Failed to insert delivery for: ";

                    $mesg .= $delivery['code'] . ", ";
                }
                
            }
        }
        unset($clsDelivery);

        return $mesg;
    }

    private function addCustomerContacts($custId ,$rows_contact) {
        $clsContact = new CustomerContactClass($this->db);
        $mesg = "";

        $orgid      = $_SESSION[SE_ORGID];
        $modifyby   = $_SESSION[SE_USERID];
        $modifydate = date_create('now')->format('Y-m-d H:i:s');
        $ws         = $_SESSION[SE_REMOTE_IP];
  
        $custContacts = $rows_contact;
        if (!is_null($custContacts) && count($custContacts) > 0) {
            foreach ($custContacts as $contact) {
                $datas = array();
                $datas[] = $this->db->fieldValue(CustomerContactTable::C_ID, $custId);
                $datas[] = $this->db->fieldValue(CustomerContactTable::C_NAME, $contact[ProspectContactTable::C_NAME]);
                $datas[] = $this->db->fieldValue(CustomerContactTable::C_EMAIL, $contact[ProspectContactTable::C_EMAIL]);
                $datas[] = $this->db->fieldValue(CustomerContactTable::C_TELEPHONE, $contact[ProspectContactTable::C_TELEPHONE]);
                $datas[] = $this->db->fieldValue(CustomerContactTable::C_MOBILE, $contact[ProspectContactTable::C_MOBILE]);
                $datas[] = $this->db->fieldValue(CustomerContactTable::C_DESIGNATION, $contact[ProspectContactTable::C_DESIGNATION]);
                $datas[] = $this->db->fieldValue(CustomerContactTable::C_DEPT, $contact[ProspectContactTable::C_DEPT]);
                $datas[] = $this->db->fieldValue(CustomerContactTable::C_REPORT_TO, $contact[ProspectContactTable::C_REPORT_TO]);
                $datas[] = $this->db->fieldValue(CustomerContactTable::C_STATUS, 1);

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

                
                if ($clsContact->addRecord($datas) > 0) {
                } else {
                    if ($mesg == "")
                        $mesg = "Failed to insert contact for: ";

                    $mesg .= $contact['name'] . ", ";
                }
                
            }
        }
        unset($clsContact);

        return $mesg;
    }

}
