<?php
require_once (PATH_TABLES . "customer/CustomerTable.php") ;
require_once (PATH_TABLES . "customer/CustomerContactTable.php") ;
require_once (PATH_MODELS . "base/MasterBase.php") ;

class CustomerClass extends MasterBase {
	function __construct($db) {
		$this->db 		= $db ;
		$this->tbl 		= CustomerTable::C_TABLE;
		$this->fldid 	= CustomerTable::C_ID ;
		$this->flddesc 	= CustomerTable::C_NAME ;
		$this->fldcode 	= CustomerTable::C_CODE ;
		$this->fldorg 	= CustomerTable::C_ORG_ID ;
	}
	function __destruct() {
	}
    function getCustomerByCode($custCode){
        $sql = "SELECT * FROM " . $this->tbl . " ";
        $sql .= "WHERE " . $this->fldcode . " = '" . $custCode . "' ";

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

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else
            return $rows[0] ;
    }
    function getActiveCustomerList($namefilter = '', $statusfilter = 'all', $orderby = ''){
        $sql = "SELECT * FROM " . $this->tbl . " ";
        $sql .= "WHERE " . $this->flddesc . " LIKE '%" . $namefilter . "%' AND " . CustomerTable::C_STATUS . " = 1" ;

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

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

        return $rows;
    }
    function getCustomerList($namefilter = '', $statusfilter = 'all', $orderby = ''){
        $sql = "SELECT * FROM " . $this->tbl . " ";
        $sql .= "WHERE " . $this->flddesc . " LIKE '%" . $namefilter . "%' ";
        // if($statusfilter == 'active'){
        //     $sql .= "AND " . CustomerTable::C_STATUS . " = 1 ";
        // } else if($statusfilter == 'non-active'){
        //     $sql .= "AND " . CustomerTable::C_STATUS . " = 0 ";
        // }

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

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

        return $rows;
    }
    function getCustomerListSuggest() {
        $sql = "select distinct ".$this->flddesc." from " . $this->tbl ." ";

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

    function getCustomerByAccountId($accId) {
        $sql = "SELECT * FROM " . $this->tbl . " ";
        $sql .= "WHERE " . CustomerTable::C_CUS_ACC . " = " . $accId . " ";

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

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

    function getCustomerAccountId($id) {
        $sql = "SELECT * FROM " . $this->tbl . " ";
        if($id != "")
            $sql .= "WHERE " . $this->fldid . " = " . $id . " ";

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

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

    function getCustomerSuggest() {
        $sql = "select distinct (" . $this->fldcode . "), " . $this->flddesc . " from " . $this->tbl . " ";

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

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

    public function getExistingRecords($cust_code, $cust_id=0) {
        $sql  = "SELECT * FROM " . $this->tbl .
            " WHERE " . $this->fldcode . " = '" . $cust_code . "' ";

        if($cust_id != 0) {
            $sql  .= " AND " . $this->fldid . " <> " . $cust_id . " ";
        }

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

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

    public function getCustomerContactListing($company_code, $company_name, $software, $customer_status, $contact, $contact_status){
        $sql = "SELECT " .
                    " CUS." . CustomerTable::C_ID . "," .
                    CustomerContactTable::C_CONTACT_ID . "," .
                    " MAX(" . CustomerTable::C_CODE . ") AS " . CustomerTable::C_CODE ."," .
                    " MAX(" . CustomerTable::C_NAME . ") AS " . CustomerTable::C_NAME ."," .
                    " STRING_AGG(SOFT." . SoftwareTable::C_DESC . ", ', ') AS " .SoftwareTable::C_DESC . "," .
                    " MAX(" . CustomerTable::C_ADDRESS . ") AS " . CustomerTable::C_ADDRESS ."," .
                    " MAX(" . CustomerContactTable::C_NAME . ") AS " . CustomerContactTable::C_NAME ."," .
                    " MAX(" . CustomerContactTable::C_DEPT . ") AS " . CustomerContactTable::C_DEPT ."," .
                    " MAX(" . CustomerContactTable::C_DESIGNATION . ") AS " . CustomerContactTable::C_DESIGNATION ."," .
                    " MAX(" . CustomerContactTable::C_TELEPHONE . ") AS " . CustomerContactTable::C_TELEPHONE ."," .
                    " MAX(" . CustomerContactTable::C_MOBILE . ") AS " . CustomerContactTable::C_MOBILE ."," .
                    " MAX(" . CustomerContactTable::C_EMAIL . ") AS " . CustomerContactTable::C_EMAIL ."," .
                    " MAX(" . CustomerTable::C_STATUS . ") AS " . CustomerTable::C_STATUS ."," .
                    " MAX(" . CustomerContactTable::C_STATUS . ") AS " . CustomerContactTable::C_STATUS ."" .
                " FROM " . CustomerTable::C_TABLE . " CUS " .
                " LEFT JOIN " . CustomerSoftwareTable::C_TABLE . " CSOFT ON CUS." . CustomerTable::C_ID . " = CSOFT." . CustomerSoftwareTable::C_ID .
                " LEFT JOIN " . CustomerContactTable::C_TABLE . " CON ON CUS." . CustomerTable::C_ID . " = CON." . CustomerContactTable::C_ID .
                " LEFT JOIN " . SoftwareTable::C_TABLE . " SOFT ON CSOFT." . CustomerSoftwareTable::C_SOFTWARE . " = SOFT." . SoftwareTable::C_ID;
        $conditions = [];

        if ($company_code != "") {
            $conditions[] = "CUS." . CustomerTable::C_CODE . " LIKE '%" . $company_code . "%'";
        }

        if ($company_name != "") {
            $conditions[] = "CUS." . CustomerTable::C_NAME . " LIKE '%" . $company_name . "%'";
        }

        if ($software != 0) {
            $conditions[] = "SOFT." . CustomerSoftwareTable::C_SOFTWARE . " = " . $software;
        }

        if ($customer_status != "") {
            $conditions[] = "CUS." . CustomerTable::C_STATUS . " = " . $customer_status;
        }

        if ($contact != "") {
            $conditions[] = "CON." . CustomerContactTable::C_NAME . " LIKE '%" . $contact . "%'";
        }
        
        if ($contact_status != "") {
            $conditions[] = "CON." . CustomerContactTable::C_STATUS . " = " . $contact_status;
        }
        $group_by = " GROUP BY CUS." . CustomerTable::C_ID . ", " . CustomerContactTable::C_CONTACT_ID;
        $order_by = " ORDER BY " . CustomerTable::C_NAME . "," . CustomerContactTable::C_NAME . " ASC ";
        $filter = "";

        if (count($conditions) > 0) {
            $filter = " WHERE " . implode(" AND ", $conditions);
        }

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

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

    public function getCorporateGiftList($company_code, $company_name, $contact, $gift_type){
        $sql = "SELECT " .
                    " CUS." . CustomerTable::C_ID . "," .
                    " CUS." . CustomerTable::C_CODE . "," .
                    " CUS." . CustomerTable::C_NAME . "," .
                    " CUS." . CustomerTable::C_ADDRESS . "," .
                    " CON." . CustomerContactTable::C_CONTACT_ID . "," .
                    " CON." . CustomerContactTable::C_NAME . "," .
                    " CON." . CustomerContactTable::C_DESIGNATION . "," .
                    " CON." . CustomerContactTable::C_TELEPHONE . "," .
                    " CON." . CustomerContactTable::C_MOBILE . "," .
                    " CON." . CustomerContactTable::C_EMAIL . "," .
                    " CON." . CustomerContactTable::C_REF_ID . "," .
                    " REC." . CorporateGiftRecipientTable::C_GIFT_AMOUNT . ", " .
                    " CASE " .
                        " WHEN SCH." . CustomerScheduleTable::C_EMAIL_DATE . " IS NULL " .
                                " THEN 0 " .
                            " ELSE 1 " .
                        " END as SCHEDULED_STATUS " .
                " FROM " . CorporateGiftRecipientTable::C_TABLE . " REC " .
                    " LEFT JOIN " . CorporateGiftTable::C_TABLE . " GIF ON REC." . CorporateGiftRecipientTable::C_ID . " = GIF." . CorporateGiftTable::C_ID . 
                    " LEFT JOIN " . CustomerTable::C_TABLE . " CUS ON CUS." . CustomerTable::C_ID . " = REC." . CorporateGiftRecipientTable::C_CUSTOMER_ID .
                    " LEFT JOIN " . CustomerContactTable::C_TABLE . " CON ON CON." . CustomerContactTable::C_CONTACT_ID . " = REC." . CorporateGiftRecipientTable::C_CONTACT_ID .
                    " LEFT JOIN " . CustomerScheduleTable::C_TABLE . " SCH ON SCH." . CustomerScheduleTable::C_NAME_ID . " = REC." . CorporateGiftRecipientTable::C_CONTACT_ID .
                        " AND SCH." . CustomerScheduleTable::C_GIFT_MASTER_ID . " = GIF." . CorporateGiftTable::C_ID .
                        " AND YEAR(SCH." . CustomerScheduleTable::C_EMAIL_DATE . ") = YEAR(GETDATE()) ";
        if ($company_code != "") {
            $conditions[] = "CUS." . CustomerTable::C_CODE . " LIKE '%" . $company_code . "%'";
        }

        if ($company_name != "") {
            $conditions[] = "CUS." . CustomerTable::C_NAME . " LIKE '%" . $company_name . "%'";
        }

        if ($contact != "") {
            $conditions[] = "CON." . CustomerContactTable::C_NAME . " LIKE '%" . $contact . "%'";
        }

        if ($gift_type != "") {
            $conditions[] = "REC." . CorporateGiftRecipientTable::C_ID . " = " . $gift_type;
        }

        // Only get Customer Contact that still Active
        $conditions[] = "CON." . CustomerContactTable::C_STATUS . " = '1'";

        $order_by = " ORDER BY " . CustomerTable::C_NAME . "," . CustomerContactTable::C_NAME . " ASC ";
        $filter = "";

        if (count($conditions) > 0) {
            $filter = " WHERE " . implode(" AND ", $conditions);
        }

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

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

    function getCoversationReport($company_name, $contact_name, $con_date_from, $con_date_to, $next_from_date, $next_to_date, $assign_to){
        $sql = "SELECT * FROM " . ProspectConversationTable::C_TABLE . " CONV " . 
                " LEFT JOIN " . ProspectTable::C_TABLE . " PRO ON CONV." . ProspectConversationTable::C_ID . " = PRO." . ProspectTable::C_ID .
                " LEFT JOIN " . ProspectContactTable::C_TABLE . " CON ON CONV." . ProspectConversationTable::C_CONTACT_ID . " = CON." . ProspectContactTable::C_CONTACT_ID .
                " LEFT JOIN " . PersonContactTable::C_TABLE . " PER ON PER." . PersonContactTable::C_ID . " = CON." . ProspectContactTable::C_PERSON_CONTACT .
                " LEFT JOIN " . UserTable::C_TABLE . " USR ON USR." . UserTable::C_ID . " = CON." . ProspectContactTable::C_ASSIGN_TO;

        $conditions = [];
        $sql_params = [];

        if($company_name != ''){
            $conditions[] = ProspectTable::C_NAME . " LIKE '%' + :" . ProspectTable::C_NAME . " + '%' ";
            $sql_params[] = $this->db->fieldValue(ProspectTable::C_NAME, $company_name);
        }
        if($contact_name != ''){
            $conditions[] = PersonContactTable::C_PREFER_NAME . " LIKE '%' + :" . PersonContactTable::C_PREFER_NAME . " + '%' ";
            $sql_params[] = $this->db->fieldValue(PersonContactTable::C_PREFER_NAME, $contact_name);
        }
        if($con_date_from != ''){
            $conditions[] = " CON." . ProspectConversationTable::C_CREATE_DATE . " >= :conv_date_from ";
            $sql_params[] = $this->db->fieldValue("conv_date_from", $con_date_from . ' 00:00:00.000');
        }
        if($con_date_to != ''){
            $conditions[] = " CON." . ProspectConversationTable::C_CREATE_DATE . " <= :conv_date_to ";
            $sql_params[] = $this->db->fieldValue("conv_date_to", $con_date_to .  ' 23:59:59.000');
        }
        if($next_from_date != ''){
            $conditions[] = ProspectContactTable::C_ACTION_DATE . " >= :next_date_from ";
            $sql_params[] = $this->db->fieldValue("next_date_from", $next_from_date);
        }
        if($next_to_date != ''){
            $conditions[] = ProspectContactTable::C_ACTION_DATE . " <= :next_date_to ";
            $sql_params[] = $this->db->fieldValue("next_date_to", $next_to_date);
        }

        if($assign_to != 0 && $assign_to != ""){
            $conditions[] = ProspectContactTable::C_ASSIGN_TO . " = :assign_to ";
            $sql_params[] = $this->db->fieldValue("assign_to", $assign_to);
        }

        $filter = "";
        if(!is_null($conditions) && count($conditions) > 0){
            $filter = " WHERE " . implode(" AND ", $conditions);
        }
        return $this->db->getTable($sql . $filter . " ORDER BY CONV." . ProspectConversationTable::C_CREATE_DATE  . " DESC", $sql_params);
    }

    public function getSendCampaignCustomerList($company_name, $contact, $gift_type, $type){
        $sql = "";
        if($type == 0 || $type == 1){
            $sql .= "SELECT " .
                        " PRO." . ProspectTable::C_ID . " AS COMP_ID," .
                        " PRO." . ProspectTable::C_NAME . " AS COMP_NAME," .
                        " CON." . ProspectContactTable::C_CONTACT_ID . " AS CONTACT_ID," .
                        " PER." . PersonContactTable::C_PREFER_NAME . " AS PREFER_NAME," .
                        " CON." . ProspectContactTable::C_DESIGNATION . " AS DESIGNATION," .
                        " PER." . PersonContactTable::C_TELEPHONE . " AS TELEPHONE," .
                        " PER." . PersonContactTable::C_EMAIL . " AS EMAIL, ".
                        " 1 AS TYPE ".
                    " 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 . " AND REC." .  CampaignRecipientTable::C_ID . " = " . $gift_type . 
                    " WHERE REC." . CampaignRecipientTable::C_PRO_CONTACT_ID . " IS NULL and CON." . ProspectContactTable::C_CONTACT_ID . " IS NOT NULL ".
                    " AND " . PersonContactTable::C_UNSUBSCRIBE_DATE . " IS NULL ";
            if ($company_name != "") {
                $sql .= " AND CUS." . ProspectTable::C_NAME . " LIKE '%" . $company_name . "%'";
            }

            if ($contact != "") {
                $sql .= " AND CON." . ProspectContactTable::C_NAME . " LIKE '%" . $contact . "%'";
            }
        }
        if($type == 0){
            $sql .= " UNION ";
        }
        if($type == 0 || $type == 2){
            $sql .= "SELECT " .
                        " CUS." . CustomerTable::C_ID . " AS COMP_ID," .
                        " CUS." . CustomerTable::C_NAME . " AS COMP_NAME," .
                        " CON." . CustomerContactTable::C_CONTACT_ID . " AS CONTACT_ID," .
                        " CON." . CustomerContactTable::C_PREFER_NAME . " AS PREFER_NAME," .
                        " CON." . CustomerContactTable::C_DESIGNATION . " AS DESIGNATION," .
                        " CON." . CustomerContactTable::C_TELEPHONE . " AS TELEPHONE," .
                        " CON." . CustomerContactTable::C_EMAIL . " AS EMAIL, ".
                        " 2 AS TYPE ".
                    " FROM " . CustomerTable::C_TABLE . " CUS " .
                        " LEFT JOIN " . CustomerContactTable::C_TABLE . " CON ON CON." . CustomerContactTable::C_ID . " = CUS." . CustomerTable::C_ID . 
                        " LEFT JOIN " . CampaignRecipientTable::C_TABLE . " REC ON REC." . CampaignRecipientTable::C_CUSTOMER_ID . " = CUS." . CustomerTable::C_ID . " AND REC." . CampaignRecipientTable::C_CUS_CONTACT_ID . " = CON." . CustomerContactTable::C_CONTACT_ID . " AND REC." .  CampaignRecipientTable::C_ID . " = " . $gift_type . 
                    " WHERE REC." . CampaignRecipientTable::C_CUS_CONTACT_ID . " IS NULL and CON." . CustomerContactTable::C_CONTACT_ID . " IS NOT NULL " .
                    " AND " . CustomerContactTable::C_UNSUBSCRIBE_DATE . " IS NULL ";
            if ($company_name != "") {
                $sql .= " AND CUS." . CustomerTable::C_NAME . " LIKE '%" . $company_name . "%'";
            }

            if ($contact != "") {
                $sql .= " AND CON." . CustomerContactTable::C_NAME . " LIKE '%" . $contact . "%'";
            }
        }
        $order_by = " ORDER BY COMP_NAME, PREFER_NAME " ;
        $rows = $this->db->getTable($sql . $order_by);

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