<?php
require_once (PATH_TABLES . "prospect/ProspectTable.php") ;
require_once (PATH_TABLES . "prospect/ProspectContactTable.php") ;
require_once (PATH_MODELS . "base/MasterBase.php") ;

class ProspectClass extends MasterBase {
	function __construct($db) {
		$this->db 		= $db ;
		$this->tbl 		= ProspectTable::C_TABLE ;
		$this->fldid 	= ProspectTable::C_ID ;
		$this->flddesc 	= ProspectTable::C_NAME ;
		$this->fldcode 	= ProspectTable::C_CODE ;
		$this->fldorg 	= ProspectTable::C_ORG_ID ;
	}
	function __destruct() {
	}

    function getActiveAndConvertedProspect(){
        $sql = "SELECT * FROM " . ProspectTable::C_TABLE . " ";
        $sql .= "WHERE " . ProspectTable::C_STATUS . " IN ('" . ProspectCompanyStatus::Active . "', '" . ProspectCompanyStatus::Converted . "') ";

        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows;
    }
    function getActiveProspect(){
        $sql = "SELECT * FROM " . ProspectTable::C_TABLE . " ";
        $sql .= "WHERE " . ProspectTable::C_STATUS . " = '" . ProspectCompanyStatus::Active . "' ";

        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows;
    }

    function getProspectByCompanyName($prospect_name){
        $sql = "SELECT * FROM " . ProspectTable::C_TABLE . " ";
        $sql .= "WHERE " . ProspectTable::C_NAME . " = '" . $prospect_name . "'";

        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows[0];
    }

    function getProspectByCode($custCode){
        $sql = "SELECT * FROM " . ProspectTable::C_TABLE . " ";
        $sql .= "WHERE " . ProspectTable::C_CODE . " = '" . $custCode . "' ";

        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows[0] ;
    }
    function getProspectList($namefilter = '', $statusfilter = 'all', $orderby = ''){
        $sql = "SELECT * FROM " . ProspectTable::C_TABLE . " ";
        $sql .= "WHERE " . ProspectTable::C_NAME . " LIKE '%" . $namefilter . "%' ";

        if($statusfilter == 'active'){
            $sql .= "AND " . ProspectTable::C_STATUS . " = 1 ";
        } else if($statusfilter == 'non-active'){
            $sql .= "AND " . ProspectTable::C_STATUS . " = 0 ";
        } else if($statusfilter == 'converted'){
            $sql .= "AND " . ProspectTable::C_STATUS . " = 2 ";
        }

        if($orderby != "")
            $sql .= " ORDER BY " . $orderby;

        $rows = $this->db->getTable($sql);

        return $rows;
    }
    function getProspectListSuggest() {
        $sql = "select distinct ".ProspectTable::C_NAME." from " . ProspectTable::C_TABLE ." ";

        $rows = $this->db->getTable($sql) ;
        // Log::write(print_r($rows,true));
        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows ;
    }

    function getProspectByAccountId($accId) {
        $sql = "SELECT * FROM " . ProspectTable::C_TABLE . " ";
        $sql .= "WHERE " . ProspectTable::C_ACCOUNT . " = " . $accId . " ";

        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return 0;
        else
            return $rows[0][ProspectTable::C_ID];
    }

    function getProspectSuggest() {
        $sql = "select distinct (" . ProspectTable::C_CODE . "), " . ProspectTable::C_NAME . " from " . ProspectTable::C_TABLE . " ";

        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null;
        else
            return $rows;
    }

    public function getExistingRecords($cust_code) {
        $sql  = "SELECT * FROM " . ProspectTable::C_TABLE .
            " WHERE " . ProspectTable::C_CODE . " = '" . $cust_code . "' ";

        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0) {
            return null;
        } else {
            return $rows;
        }
    }

    public function updateStatusConvertToCustomer($id) {
        $sql  = "UPDATE " . ProspectTable::C_TABLE . " SET " . ProspectTable::C_STATUS . " = 2 ".
                " WHERE " . ProspectTable::C_ID . " = " . $id;

        $this->db->executeSql($sql);
    }

    
    function getSuspectContactByID($id){
        $sql = "SELECT * FROM " . ProspectContactTable::C_TABLE . " as prop "
                . "LEFT JOIN " . PersonContactTable::C_TABLE . " as person ON prop." . ProspectContactTable::C_PERSON_CONTACT . " = person." . PersonContactTable::C_ID
                . " WHERE " . ProspectContactTable::C_ID . " = " . $id . " ORDER BY " . ProspectContactTable::C_SEQUENCE;

        $rows = $this->db->getTable($sql);
        if(!is_null($rows) && count($rows) > 0){
            return $rows;
        }
        return null;
    }

    function getSuspectAndContactList($params, $createdBy=0){
        //changes
        //filter $business_nature = (isset($params['business_nature']))? $params['business_nature'] : '';
        //sql select from BusinessNatureTable, filter business_nature
        $industry           = (isset($params['industry']))? $params['industry'] : '';
        $business_sector    = (isset($params['business_sector']))? $params['business_sector'] : '';
        $total_employee     = (isset($params['total_employee']))? $params['total_employee'] : '';
        $company_name       = (isset($params['company_name']))? $params['company_name'] : '';
        $telephone          = (isset($params['telephone']))? $params['telephone'] : '';
        $state              = (isset($params['state']))? $params['state'] : '';
        $account_flag       = (isset($params['account_flag']))? $params['account_flag'] : '';
        $prospect_id        = (isset($params['prospect_id']))? $params['prospect_id'] : '';

        $sql    = "SELECT pro.*,
                          ind.". IndustryTable::C_DESC .",
                          sec.". BusinessSectorTable::C_DESC .",
                          nature.". BusinessNatureTable::C_DESC .",
                          soft_acc.". SoftwareTable::C_DESC ." as SoftwareAcc,
                          soft_sales.". SoftwareTable::C_DESC ." as SoftwareSales,
                          soft_other.". SoftwareTable::C_DESC ." as SoftwareOther,
                          contact." . ProspectContactTable::C_ACTION_DATE . ",
                          contact." . ProspectContactTable::C_CONTACT_ID . ",
                          contact." . ProspectContactTable::C_SEQUENCE . ",
                          contact." . ProspectContactTable::C_DESIGNATION . ",
                          contact." . ProspectContactTable::C_REPORT_TO . ",
                          contact." . ProspectContactTable::C_NEXT_ACTION . ",
                          contact." . ProspectContactTable::C_ACTION_DATE . ",
                          contact." . ProspectContactTable::C_BEST_TIME . ",
                          contact." . ProspectContactTable::C_ASSIGN_TO . ",
                          contact." . ProspectContactTable::C_LUNCH_TIME . ",
                          person." . PersonContactTable::C_ID . ",
                          person." . PersonContactTable::C_FULL_NAME . ",
                          person." . PersonContactTable::C_PREFER_NAME . ",
                          person." . PersonContactTable::C_TELEPHONE . ",
                          person." . PersonContactTable::C_EMAIL . ",
                          person." . PersonContactTable::C_PREFER_PROMOTION . ",
                          person." . PersonContactTable::C_PREFER_TELEPHONE . ",
                          person." . PersonContactTable::C_PREFER_WHATSAPP . ",
                          person." . PersonContactTable::C_PREFER_TELEGRAM . ",
                          person." . PersonContactTable::C_PREFER_EMAIL . ",
                          person." . PersonContactTable::C_MOONCAKE . ",
                          person." . PersonContactTable::C_XMAS_CARD . ",
                          person." . PersonContactTable::C_CNY . "
                   FROM " . ProspectTable::C_TABLE . " pro " .
                  " LEFT JOIN " . IndustryTable::C_TABLE . " ind " . " ON pro." . ProspectTable::C_INDUSTRY . " = ind." . IndustryTable::C_ID .
                  " LEFT JOIN " . BusinessSectorTable::C_TABLE . " sec " . " ON pro." . ProspectTable::C_BUSINESS_SECTOR . " = sec." . BusinessSectorTable::C_ID .
                  " LEFT JOIN " . BusinessNatureTable::C_TABLE . " nature " . " ON pro." . ProspectTable::C_BUSINESS_NATURE . " = nature." . BusinessNatureTable::C_ID .
                  " LEFT JOIN " . SoftwareTable::C_TABLE . " soft_acc " . " ON pro." . ProspectTable::C_ACC_SOFTWARE . " = soft_acc." . SoftwareTable::C_ID .
                  " LEFT JOIN " . SoftwareTable::C_TABLE . " soft_sales " . " ON pro." . ProspectTable::C_SALES_SOFTWARE . " = soft_sales." . SoftwareTable::C_ID .
                  " LEFT JOIN " . SoftwareTable::C_TABLE . " soft_other " . " ON pro." . ProspectTable::C_OTHER_SOFTWARE . " = soft_other." . SoftwareTable::C_ID .
                  " LEFT JOIN " . ProspectContactTable::C_TABLE . " contact " . " ON pro." . ProspectTable::C_ID . " = contact." . ProspectContactTable::C_ID .
                  " LEFT JOIN " . PersonContactTable::C_TABLE . " person " . " ON contact." . ProspectContactTable::C_PERSON_CONTACT . " = person." . PersonContactTable::C_ID;
    
        $filter = "";

        $filters = array();
        if($industry != ''){
            $filters[] = "ind.".IndustryTable::C_DESC . " like '%" . $industry . "%' ";
        }
        if($business_sector != ''){
            $filters[] = "sec.".BusinessSectorTable::C_DESC . " like '%" . $business_sector. "%' ";
        }
        if($total_employee != ''){
            $filters[] = ProspectTable::C_TOTAL_EMPLOYEE . " like '%" . $total_employee. "%' ";
        }
        if($company_name != ''){
            $filters[] = ProspectTable::C_NAME . " like '%" . $company_name. "%' ";
        }
        if($telephone != ''){
            $filters[] = ProspectTable::C_TELEPHONE . " like '%" . $telephone. "%' ";
        }
        if($state != ''){
            $filters[] = ProspectTable::C_STATE . " like '%" . $state. "%' ";
        }
        //value comes from a fixed dropdown, so only the two known flags may reach the sql.
        //"All" arrives as an empty string and simply adds no filter.
        if($account_flag == AccountFlags::Customer || $account_flag == AccountFlags::Supplier){
            $filters[] = "pro." . ProspectTable::C_FLAG . " = '" . $account_flag . "' ";
        }
        if($createdBy != 0){
            $filters[] = "pro." . ProspectTable::C_CREATE_BY . " = " . $createdBy . " ";
        }
        if($prospect_id != 0 && $prospect_id != ''){
            $filters[] = "pro." . ProspectTable::C_ID . " = " . $prospect_id . " ";
        }

        $filter = implode(' AND ', $filters);
        if($filter != ""){
            $filter = " WHERE " . $filter;            
        }

        $sql .= $filter . " ORDER BY " . ProspectTable::C_NAME;
        
        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows;
    }

    function getSuspectList($params){
        $industry           = (isset($params['industry']))? $params['industry'] : '';
        $business_sector    = (isset($params['business_sector']))? $params['business_sector'] : '';
        $total_employee     = (isset($params['total_employee']))? $params['total_employee'] : '';
        $company_name       = (isset($params['company_name']))? $params['company_name'] : '';
        $telephone          = (isset($params['telephone']))? $params['telephone'] : '';
        $prospect_id        = (isset($params['prospect_id']))? $params['prospect_id'] : '';

        $sql    = "SELECT pro.*, 
                          ind.". IndustryTable::C_DESC .",
                          sec.". BusinessSectorTable::C_DESC .",
                          soft_acc.". SoftwareTable::C_DESC ." as SoftwareAcc,
                          soft_sales.". SoftwareTable::C_DESC ." as SoftwareSales,
                          soft_other.". SoftwareTable::C_DESC ." as SoftwareOther
                   FROM " . ProspectTable::C_TABLE . " pro " .
                  " LEFT JOIN " . IndustryTable::C_TABLE . " ind " . " ON pro." . ProspectTable::C_INDUSTRY . " = ind." . IndustryTable::C_ID .
                  " LEFT JOIN " . BusinessSectorTable::C_TABLE . " sec " . " ON pro." . ProspectTable::C_BUSINESS_SECTOR . " = sec." . BusinessSectorTable::C_ID .
                  " LEFT JOIN " . SoftwareTable::C_TABLE . " soft_acc " . " ON pro." . ProspectTable::C_ACC_SOFTWARE . " = soft_acc." . SoftwareTable::C_ID .
                  " LEFT JOIN " . SoftwareTable::C_TABLE . " soft_sales " . " ON pro." . ProspectTable::C_SALES_SOFTWARE . " = soft_sales." . SoftwareTable::C_ID .
                  " LEFT JOIN " . SoftwareTable::C_TABLE . " soft_other " . " ON pro." . ProspectTable::C_OTHER_SOFTWARE . " = soft_other." . SoftwareTable::C_ID;
    
        $filter = "";
        
        // if($industry != ""){
        //     $filter = ($filter != "") ? " AND ". ProspectTable::C_INDUSTRY . " like '%" . $industry. "%' " :
        //                                 " WHERE " . ProspectTable::C_INDUSTRY . " like '%" . $industry. "%' ";
        // }
        // if($business_sector != ""){
        //     $filter = ($filter != "") ? " AND ". ProspectTable::C_BUSINESS_SECTOR . " like '%" . $business_sector. "%' " :
        //                                 " WHERE " . ProspectTable::C_BUSINESS_SECTOR . " like '%" . $business_sector. "%' ";
        // }
        if($total_employee != ""){
            $filter = ($filter != "") ? " AND ". ProspectTable::C_TOTAL_EMPLOYEE . " like '%" . $total_employee. "%' " :
                                        " WHERE " . ProspectTable::C_TOTAL_EMPLOYEE . " like '%" . $total_employee. "%' ";
        }
        if($company_name != ""){
            $filter = ($filter != "") ? " AND ". ProspectTable::C_NAME . " like '%" . $company_name. "%' " :
                                        " WHERE " . ProspectTable::C_NAME . " like '%" . $company_name. "%' ";
        }
        if($telephone != ""){
            $filter = ($filter != "") ? " AND ". ProspectTable::C_TELEPHONE . " like '%" . $telephone. "%' " :
                                        " WHERE " . ProspectTable::C_TELEPHONE . " like '%" . $telephone. "%' ";
        }
        if($prospect_id != 0){
            $filter = ($filter != "") ? " AND ". ProspectTable::C_ID . " = " . $prospect_id. " " :
                                        " WHERE " . ProspectTable::C_ID . " = " . $prospect_id. " ";
        }

        $sql .= $filter . " ORDER BY " . ProspectTable::C_NAME;
        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows;
    }
    
    function getSuspectActionList($params){
        $industry           = (isset($params['industry']))? $params['industry'] : '';
        $business_sector    = (isset($params['business_sector']))? $params['business_sector'] : '';
        $total_employee     = (isset($params['total_employee']))? $params['total_employee'] : '';
        $company_name       = (isset($params['company_name']))? $params['company_name'] : '';
        $telephone          = (isset($params['telephone']))? $params['telephone'] : '';

        $sql    = "SELECT * FROM " . ProspectTable::C_TABLE . " as sus " .
                  " LEFT JOIN " . ProspectContactTable::C_TABLE . " as sus_cont ";
        $filter = " WHERE sus." . ProspectTable::C_ACTIVE_STATUS . " = " . ProspectCompanyStatus::Assigned . 
                  " AND sus_cont." . ProspectContactTable::C_ASSIGN_TO . " = " .$_SESSION[SE_USERID]. " AND " . ProspectContactTable::C_ACTIVE_STATUS . " = 0";
        
        if($industry != ""){
            $filter = " AND ". ProspectTable::C_INDUSTRY . " like '%" . $industry. "%' ";
        }
        if($business_sector != ""){
            $filter = " AND ". ProspectTable::C_BUSINESS_SECTOR . " like '%" . $business_sector. "%' ";
        }
        if($total_employee != ""){
            $filter = " AND ". ProspectTable::C_TOTAL_EMPLOYEE . " like '%" . $total_employee. "%' ";
        }
        if($company_name != ""){
            $filter = " AND ". ProspectTable::C_NAME . " like '%" . $company_name. "%' ";
        }
        if($telephone != ""){
            $filter = " AND ". ProspectTable::C_TELEPHONE . " like '%" . $telephone. "%' ";
        }

        $sql .= $filter;
        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows;
    }

    //All Account Report : every company with its account flag and accounting software
    function getAllAccountList(){
        $sql = "SELECT pro." . ProspectTable::C_ID .
                    ", pro." . ProspectTable::C_NAME .
                    ", pro." . ProspectTable::C_FLAG .
                    ", soft_acc." . SoftwareTable::C_DESC . " as SoftwareAcc" .
               " FROM " . ProspectTable::C_TABLE . " pro" .
               " LEFT JOIN " . SoftwareTable::C_TABLE . " soft_acc " .
                    " ON pro." . ProspectTable::C_ACC_SOFTWARE . " = soft_acc." . SoftwareTable::C_ID .
               " ORDER BY pro." . ProspectTable::C_NAME ;

        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows;
    }

    function getFollowUpListByActionDate($action_from, $action_to){
        $sql    = "SELECT " .
                    "P." .  ProspectTable::C_ID . 
                    ", P." .  ProspectTable::C_NAME . 
                    ", C." . ProspectContactTable::C_ACTION_DATE . 
                    ", C." . ProspectContactTable::C_BEST_TIME . 
                    ", PC." . PersonContactTable::C_FULL_NAME . 
                    ", PC." . PersonContactTable::C_TELEPHONE . 
                    ", C." . ProspectContactTable::C_CONTACT_ID .
                    ", C." . ProspectContactTable::C_PERSON_CONTACT .
                    ", C." . ProspectContactTable::C_NEXT_ACTION .
                    ", C." . ProspectContactTable::C_ACTION_LOCATION .
                    ", C." . ProspectContactTable::C_ACTION_STATUS .
                    ", USR." . UserTable::C_FULL_NAME . 
                    ", LastestConversation." . ProspectConversationTable::C_DESC . 
                    ", LastestConversation." . ProspectConversationTable::C_CONVERSATION_ID . "
                FROM " . ProspectTable::C_TABLE . " P
                LEFT JOIN " . ProspectContactTable::C_TABLE . " C ON P." . ProspectTable::C_ID . " = C." . ProspectContactTable::C_ID . "
                LEFT JOIN " . PersonContactTable::C_TABLE . " PC ON C." . ProspectContactTable::C_PERSON_CONTACT . " = PC." . PersonContactTable::C_ID . "
                LEFT JOIN " . UserTable::C_TABLE . " USR ON USR." . UserTable::C_ID . " = C." . ProspectContactTable::C_ASSIGN_TO . "
                LEFT JOIN (" .
                    " SELECT * FROM (SELECT " .
                        ProspectConversationTable::C_DESC . "," .
                        ProspectConversationTable::C_CONVERSATION_ID . "," .
                        ProspectConversationTable::C_ID . "," .
                        ProspectConversationTable::C_CONTACT_ID . "," .
                        ProspectConversationTable::C_CREATE_DATE . "," .
                        "ROW_NUMBER() OVER (PARTITION BY " . ProspectConversationTable::C_CONTACT_ID . " ORDER BY " . ProspectConversationTable::C_CREATE_DATE . " DESC) as rn" .
                    " FROM " . ProspectConversationTable::C_TABLE . 
                    ") as ranked WHERE rn = 1" .
                ") as LastestConversation " .
                " ON P." . ProspectTable::C_ID . " = LastestConversation." . ProspectConversationTable::C_ID .
                " AND C." . ProspectContactTable::C_CONTACT_ID . " = LastestConversation." . ProspectConversationTable::C_CONTACT_ID .
                " WHERE C." . ProspectContactTable::C_CONTACT_ID . " IS NOT NULL 
                AND CAST(" . ProspectContactTable::C_ACTION_DATE . " AS DATE) > CAST('" . NULL_DATE . "' AS DATE)";

        $filter = "";
        
        if($action_from != ""){
            $filter .= " AND CAST(C.". ProspectContactTable::C_ACTION_DATE . " AS DATE) >= CAST('" . $action_from. "' AS DATE) ";
        }
        if($action_to != ""){
            $filter .= " AND CAST(C.". ProspectContactTable::C_ACTION_DATE . " AS DATE) <= CAST('" . $action_to. "' AS DATE) ";
        }
        $filter .= " ORDER BY C." . ProspectContactTable::C_ACTION_DATE . ", C." . ProspectContactTable::C_BEST_TIME;

        $sql .= $filter;
        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows;
    }

    function getFollowUpList($params, $showClosed=false, $createdBy=0){
        $industry           = (isset($params['industry']))? $params['industry'] : '';
        $business_sector    = (isset($params['business_sector']))? $params['business_sector'] : '';
        $total_employee     = (isset($params['total_employee']))? $params['total_employee'] : '';
        $company_name       = (isset($params['company_name']))? $params['company_name'] : '';
        $telephone          = (isset($params['telephone']))? $params['telephone'] : '';
        $action_status      = (isset($params['action_status']))? $params['action_status'] : '';
        $action_date        = (isset($params['action_date']))? $params['action_date'] : date('Y-m-d');

        $sql    = "SELECT " .
                    "P." .  ProspectTable::C_ID . 
                    ", P." .  ProspectTable::C_NAME . 
                    ", USR." . UserTable::C_FULL_NAME . 
                    ", C." . ProspectContactTable::C_ACTION_DATE . 
                    ", C." . ProspectContactTable::C_BEST_TIME . 
                    ", PC." . PersonContactTable::C_FULL_NAME . 
                    ", PC." . PersonContactTable::C_TELEPHONE . 
                    ", C." . ProspectContactTable::C_CONTACT_ID .
                    ", C." . ProspectContactTable::C_PERSON_CONTACT .
                    ", C." . ProspectContactTable::C_NEXT_ACTION .
                    ", C." . ProspectContactTable::C_ACTION_LOCATION .
                    ", C." . ProspectContactTable::C_ACTION_STATUS .
                    ", LastestConversation." . ProspectConversationTable::C_DESC . 
                    ", LastestConversation." . ProspectConversationTable::C_CONVERSATION_ID . "
                FROM " . ProspectTable::C_TABLE . " P
                LEFT JOIN " . ProspectContactTable::C_TABLE . " C ON P." . ProspectTable::C_ID . " = C." . ProspectContactTable::C_ID . "
                LEFT JOIN " . UserTable::C_TABLE . " USR ON USR." . UserTable::C_ID . " = C." . ProspectContactTable::C_ASSIGN_TO . "
                LEFT JOIN " . PersonContactTable::C_TABLE . " PC ON C." . ProspectContactTable::C_PERSON_CONTACT . " = PC." . PersonContactTable::C_ID . "
                LEFT JOIN (" .
                    " SELECT * FROM (SELECT " .
                        ProspectConversationTable::C_DESC . "," .
                        ProspectConversationTable::C_CONVERSATION_ID . "," .
                        ProspectConversationTable::C_ID . "," .
                        ProspectConversationTable::C_CONTACT_ID . "," .
                        ProspectConversationTable::C_CREATE_DATE . "," .
                        "ROW_NUMBER() OVER (PARTITION BY " . ProspectConversationTable::C_CONTACT_ID . " ORDER BY " . ProspectConversationTable::C_CREATE_DATE . " DESC) as rn" .
                    " FROM " . ProspectConversationTable::C_TABLE . 
                    ") as ranked WHERE rn = 1" .
                ") as LastestConversation " .
                " ON P." . ProspectTable::C_ID . " = LastestConversation." . ProspectConversationTable::C_ID .
                " AND C." . ProspectContactTable::C_CONTACT_ID . " = LastestConversation." . ProspectConversationTable::C_CONTACT_ID .
                " WHERE C." . ProspectContactTable::C_CONTACT_ID . " IS NOT NULL 
                AND CAST(" . ProspectContactTable::C_ACTION_DATE . " AS DATE) >= CAST(getdate() AS DATE)";

        $filter = "";
        
        if($industry != ""){
            $filter .= " AND P." . ProspectTable::C_INDUSTRY . " like '%" . $industry. "%' ";
        }
        if($business_sector != ""){
            $filter .=  " AND P.". ProspectTable::C_BUSINESS_SECTOR . " like '%" . $business_sector. "%' ";
        }
        if($total_employee != ""){
            $filter .= " AND P.". ProspectTable::C_TOTAL_EMPLOYEE . " like '%" . $total_employee. "%' ";
        }
        if($company_name != ""){
            $filter .= " AND P.". ProspectTable::C_NAME . " like '%" . $company_name. "%' ";
        }
        if($telephone != ""){
            $filter .= " AND P.". ProspectTable::C_TELEPHONE . " like '%" . $telephone. "%' ";
        }

        if($action_status != ""){
            $filter .= " AND C.". ProspectContactTable::C_ACTION_STATUS . " = '" . $action_status. "' ";
        }
        if($action_date != ""){
            $filter .= " AND CAST(C.". ProspectContactTable::C_ACTION_DATE . " AS DATE) = CAST('" . $action_date. "' AS DATE) ";
        }
        if(!$showClosed){
            $filter .= " AND C.". ProspectContactTable::C_ACTION_STATUS . " <> '" . MeetingStatus::Closed. "' ";
        }
        if($createdBy != 0){
            $filter .= " AND P." . ProspectTable::C_CREATE_BY . " = " . $createdBy . " ";
        }

        $filter .= " ORDER BY C." . ProspectContactTable::C_ACTION_DATE . ", C." . ProspectContactTable::C_BEST_TIME;

        $sql .= $filter;
        
        $rows = $this->db->getTable($sql);

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows;
    }

    function getProspectAndContact($account_name){
        $sql = "SELECT " 
                    . ProspectTable::C_FLAG . ","
                    . ProspectTable::C_NAME . ","
                    . ProspectTable::C_SALES_STATUS2 . ","
                    . ProspectTable::C_COMPANY_STATUS . ","
                    . ProspectTable::C_TELEPHONE . ","
                    . ProspectTable::C_TELEPHONE2 . ","
                    . ProspectTable::C_COMPANY_EMAIL . ","
                    . ProspectTable::C_ADDRESS_1 . ","
                    . PersonContactTable::C_ID . ","
                    . PersonContactTable::C_FULL_NAME . ","
                    . PersonContactTable::C_PREFER_NAME . ","
                    . PersonContactTable::C_TELEPHONE . ","
                    . PersonContactTable::C_EMAIL . ","
                    . PersonContactTable::C_COMMENT . ","
                    . PersonContactTable::C_PREFER_PROMOTION . ","
                    . PersonContactTable::C_PREFER_WHATSAPP . ","
                    . PersonContactTable::C_PREFER_TELEGRAM . ","
                    . PersonContactTable::C_PREFER_EMAIL . ","
                    . PersonContactTable::C_PREFER_TELEPHONE . ","
                    . " pro." . ProspectContactTable::C_ID . ","
                    . ProspectContactTable::C_CONTACT_ID . ","
                    . ProspectContactTable::C_DESIGNATION . ","
                    . ProspectContactTable::C_DEPT . ","
                    . ProspectContactTable::C_REPORT_TO . ","
                    . ProspectContactTable::C_PERSON_CONTACT .
                " FROM " . ProspectTable::C_TABLE. " pro " .
                " LEFT JOIN " . ProspectContactTable::C_TABLE . " cont ON cont." . ProspectContactTable::C_ID . " = pro." . ProspectTable::C_ID . 
                " LEFT JOIN " . PersonContactTable::C_TABLE . " person ON person." . PersonContactTable::C_ID . " = cont." . ProspectContactTable::C_PERSON_CONTACT.
                " WHERE pro." . ProspectTable::C_NAME . " = '" . $account_name . "'";
                
        $rows = $this->db->getTable($sql);
        
        if(!is_null($rows) && count($rows)){
            return $rows;
        }
        return null;
    }

    public function getSendCampaignProspctList($company_name, $contact, $gift_type){
        $sql = "SELECT " .
                    " PRO." . ProspectTable::C_ID . "," .
                    " PRO." . ProspectTable::C_NAME . "," .
                    " PER." . PersonContactTable::C_ID . "," .
                    " CON." . ProspectContactTable::C_CONTACT_ID . "," .
                    " PER." . PersonContactTable::C_PREFER_NAME . "," .
                    " CON." . ProspectContactTable::C_DESIGNATION . "," .
                    " PER." . PersonContactTable::C_TELEPHONE . "," .
                    " PER." . PersonContactTable::C_EMAIL .
                " FROM " . ProspectTable::C_TABLE . " PRO " .
                    " LEFT JOIN " . ProspectContactTable::C_TABLE . " CON ON CON." . ProspectContactTable::C_ID . " = PRO." . ProspectTable::C_ID . 
                    " LEFT JOIN " . PersonContactTable::C_TABLE . " PER ON CON." . ProspectContactTable::C_PERSON_CONTACT . " = PER." . PersonContactTable::C_ID . 
                    " LEFT JOIN " . CampaignRecipientTable::C_TABLE . " REC ON REC." . CampaignRecipientTable::C_PROSPECT_ID . " = PRO." . ProspectTable::C_ID . " AND REC." . CampaignRecipientTable::C_PRO_CONTACT_ID . " = CON." . ProspectContactTable::C_CONTACT_ID .
                " WHERE (REC." .  CampaignRecipientTable::C_ID . " = " . $gift_type . " AND REC." . CampaignRecipientTable::C_PRO_CONTACT_ID . " IS NULL)";
        $filter = "";
        if ($company_name != "") {
            $filter = " AND CUS." . ProspectTable::C_NAME . " LIKE '%" . $company_name . "%'";
        }

        if ($contact != "") {
            $filter = " AND CON." . ProspectContactTable::C_NAME . " LIKE '%" . $contact . "%'";
        }

        if ($gift_type != "") {
            $filter = " AND REC." . CampaignRecipientTable::C_ID . " = " . $gift_type;
        }

        $order_by = " ORDER BY " . ProspectTable::C_NAME . "," . ProspectContactTable::C_NAME . " ASC ";

        $rows = $this->db->getTable($sql . $filter . $order_by);

        if (is_null($rows) || count($rows) == 0) {
            return null;
        } else {
            return $rows;
        }
    }
}
