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

class CustomerScheduleClass extends MasterBase {
    function __construct($db) {
        $this->db       = $db ;
        $this->tbl      = CustomerScheduleTable::C_TABLE ;
        $this->fldid    = CustomerScheduleTable::C_ID ;
    }
    function __destruct() {
    }

    function isRecordExist($id, $gift_id){
		$sql = "SELECT * FROM " . CustomerScheduleTable::C_TABLE;
        $sql .= " WHERE " . CustomerScheduleTable::C_NAME_ID . " = " . $id;
        $sql .= " AND YEAR(" . CustomerScheduleTable::C_EMAIL_DATE . ") = YEAR(GETDATE()) ";
        $sql .= " AND " . CustomerScheduleTable::C_GIFT_MASTER_ID . " = " . $gift_id;
        $rows = $this->db->getTable($sql);
        if (is_null($rows) || empty($rows)){
            return false;
        } else {
            return true;
        }
	}

    function insertCustomerSchedule($id, $ref_id, $name, $phone, $email, $total_mc, $send_email, $coy_id, $cc_email, $gift_id, $gift_amt){
        if($send_email=="false") {
            //send wa
            $wa_date = date_create('now')->format('Y-m-d') . " 00:00:00";
            $wa_time = date_create('now')->format('H:i');
            $email_date = date_create(NULL_DATE)->format('Y-m-d') . " 00:00:00";
            $email_time = "00:00";
        } else {
            //send email
            $wa_date = date_create(NULL_DATE)->format('Y-m-d') . " 00:00:00";
            $wa_time = "00:00";
            $email_date = date_create('now')->format('Y-m-d') . " 00:00:00";
            $email_time = date_create('now')->format('H:i');
        }

		$sql = "INSERT INTO " . CustomerScheduleTable::C_TABLE . 
                        "(" . CustomerScheduleTable::C_NAME_ID . ", " . 
                              CustomerScheduleTable::C_NAME . ", " . 
                              CustomerScheduleTable::C_PHONE . ", " . 
                              CustomerScheduleTable::C_EMAIL . ", " . 
                              CustomerScheduleTable::C_TOTAL_MC . ", " . 
                              CustomerScheduleTable::C_WHATSAPP_DATE . ", " . 
                              CustomerScheduleTable::C_WHATSAPP_TIME . ", " . 
                              CustomerScheduleTable::C_EMAIL_DATE . ", " . 
                              CustomerScheduleTable::C_EMAIL_TIME . ", " . 
                              CustomerScheduleTable::C_DELIVERY_DATE . ", " . 
                              CustomerScheduleTable::C_DELIVERY_TIME . ", " . 
                              CustomerScheduleTable::C_REF_ID . "," .
                              CustomerScheduleTable::C_CC_EMAIL . "," .
                              CustomerScheduleTable::C_GIFT_MASTER_ID . "," .
                              CustomerScheduleTable::C_CHOSEN_GIFT_ITEM . "," .
                              CustomerScheduleTable::C_GIFT_AMOUNT . "," .
                              CustomerScheduleTable::C_COY_ID . ")";

        $sql .= "VALUES (" .
                    "'" . $id . "'," . 
                    "'" . $name . "'," . 
                    "'" . $phone . "'," . 
                    "'" . $email . "'," . 
                    "'" . $total_mc . "'," . 
                    "'" . $wa_date . "'," . 
                    "'" . $wa_time . "'," . 
                    "'" . $email_date . "'," . 
                    "'" . $email_time . "'," . 
                    "'" . NULL_DATE . "'," . 
                    "''," . 
                    "'" .$ref_id . "'," .
                    "'" .$cc_email . "'," .
                    "'" .$gift_id . "'," .
                    "0," .
                    "'" .$gift_amt . "'," .
                    "'" .$coy_id . "')" ;

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

    function updateCustomerSchedule($id, $ref_id, $gift_amt){
        $wa_date = date_create('now')->format('Y-m-d');
        $wa_time = date_create('now')->format('H:i');

		$sql = "UPDATE " . CustomerScheduleTable::C_TABLE . 
                " SET " . CustomerScheduleTable::C_REF_ID . " = '" . $ref_id . "', " .
                " " . CustomerScheduleTable::C_WHATSAPP_DATE . " = '" . $wa_date . "', " .
                " " . CustomerScheduleTable::C_WHATSAPP_TIME . " = '" . $wa_time . "', " .
                " " . CustomerScheduleTable::C_GIFT_AMOUNT . " = '" . $gift_amt . "' " .
                " WHERE " . CustomerScheduleTable::C_NAME_ID . " = " . $id ;

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

    function updateSendEmailCustomerSchedule($id, $ref_id, $email, $cc_email, $coy_id, $gift_id, $gift_amt, $total_mc=0){
        $email_date = date_create('now')->format('Y-m-d');
        $email_time = date_create('now')->format('H:i');

		$sql = "UPDATE " . CustomerScheduleTable::C_TABLE . 
                " SET " . CustomerScheduleTable::C_EMAIL_DATE . " = '" . $email_date . "', " .
                " " . CustomerScheduleTable::C_EMAIL_TIME . " = '" . $email_time . "', " .
                " " . CustomerScheduleTable::C_EMAIL . " = '" . $email . "', " .
                " " . CustomerScheduleTable::C_TOTAL_MC . " = '" . $total_mc . "', " .
                " " . CustomerScheduleTable::C_REF_ID . " = '" . $ref_id . "', " .
                " " . CustomerScheduleTable::C_COY_ID . " = '" . $coy_id . "', " .
                " " . CustomerScheduleTable::C_CC_EMAIL . " = '" . $cc_email . "', " .
                " " . CustomerScheduleTable::C_CHOSEN_GIFT_ITEM . " = 0, " .
                " " . CustomerScheduleTable::C_GIFT_AMOUNT . " = '" . $gift_amt . "' " .
                " WHERE " . CustomerScheduleTable::C_NAME_ID . " = " . $id .
                " AND " . CustomerScheduleTable::C_GIFT_MASTER_ID . " = " . $gift_id .
                " AND YEAR(" . CustomerScheduleTable::C_EMAIL_DATE . ") = YEAR(GETDATE()) ";

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

    function getRecordByRefId($ref, $gift_id) {
		$sql = "select *, SHC." . CustomerScheduleTable::C_COY_ID . " as SCH_COY_ID from " . CustomerScheduleTable::C_TABLE . " SHC " .
                    " LEFT JOIN " . CustomerContactTable::C_TABLE . " CON ON SHC." . CustomerScheduleTable::C_NAME_ID . " = CON." . CustomerContactTable::C_CONTACT_ID .
                    " where " . CustomerScheduleTable::C_REF_ID. " = '" . $ref . "'" .
                    " AND " . CustomerScheduleTable::C_GIFT_MASTER_ID . " = '" . $gift_id ."'" .
                " ORDER BY SHC." . CustomerScheduleTable::C_EMAIL_DATE . " DESC";
        $rows   =  $this->db->getTable($sql);
        if(!is_null($rows) && count($rows) > 0){
            return $rows[0];
        }else{
            return null;
        }
	}

    function getCustomerScheduleGiftByID($id){
        $sql = "SELECT * FROM " 
                    . CustomerScheduleTable::C_TABLE . " SCH " .
                " LEFT JOIN " .CorporateGiftDetailTable::C_TABLE . " D " .
                    " ON SCH." . CustomerScheduleTable::C_GIFT_MASTER_ID . " = D." . CorporateGiftDetailTable::C_ID . 
                    " AND SCH." . CustomerScheduleTable::C_CHOSEN_GIFT_ITEM . " = D." . CorporateGiftDetailTable::C_DETAIL_ID . 
                " LEFT JOIN " .CorporateGiftTable::C_TABLE . " GIFT " .
                    " ON D." . CorporateGiftDetailTable::C_ID . " = GIFT." . CorporateGiftTable::C_ID .
                " WHERE " .
                    CustomerScheduleTable::C_ID . " = " . $id;
        $rows = $this->db->getTable($sql);
        if(!is_null($rows) && count($rows) > 0){
            return $rows[0];
        }else{
            return null;
        }
    }

    function getScheduleByID($id){
        $sql = "SELECT * FROM " . CustomerScheduleTable::C_TABLE . " SCH " .
                    "LEFT JOIN " . CustomerContactTable::C_TABLE . " CON ON SCH." . CustomerScheduleTable::C_NAME_ID . " = CON." . CustomerContactTable::C_CONTACT_ID .
                " WHERE " . CustomerScheduleTable::C_ID . " = " . $id;
        $row = $this->db->getTable($sql);

        if(!is_null($row) && count($row) > 0){
            return $row[0];
        }else{
            return null;
        }
    }

    function getCorporateGiftSummary($delivery_from, $delivery_to, $gift, $gift_type){
        $sql = "SELECT " .
                    CustomerScheduleTable::C_DELIVERY_DATE . "," .
                    CorporateGiftDetailTable::C_ITEM_NAME . "," .
                    CustomerScheduleTable::C_CHOSEN_GIFT_ITEM . "," .
                    CustomerScheduleTable::C_GIFT_AMOUNT .
                " FROM " . CustomerScheduleTable::C_TABLE . " SCH " .
                " LEFT JOIN " . CorporateGiftTable::C_TABLE . " GIF ON SCH." . CustomerScheduleTable::C_GIFT_MASTER_ID . " = GIF." . CorporateGiftTable::C_ID .
                " LEFT JOIN " . CorporateGiftDetailTable::C_TABLE . " DET " .
                    " ON SCH." . CustomerScheduleTable::C_GIFT_MASTER_ID . " = DET." . CorporateGiftDetailTable::C_ID . 
                    " AND SCH." . CustomerScheduleTable::C_CHOSEN_GIFT_ITEM . " = DET." . CorporateGiftDetailTable::C_DETAIL_ID .

                    " WHERE " . CustomerScheduleTable::C_GIFT_MASTER_ID . " = " . $gift . " AND " . CustomerScheduleTable::C_DELIVERY_DATE . " != '1969-12-31'";
        $filter = "";

        if($gift_type != "" && $gift_type != 0){
            $filter .= " AND " . CustomerScheduleTable::C_CHOSEN_GIFT_ITEM . " = " . $gift_type;
        }

        if($delivery_from != ""){
            $filter .= " AND " . CustomerScheduleTable::C_DELIVERY_DATE . " >= '" . $delivery_from . "'";
        }

        if($delivery_to != ""){
            $filter .= " AND " . CustomerScheduleTable::C_DELIVERY_DATE . " <= '" . $delivery_to . "'";
        }

        $orderby = " ORDER BY " . CustomerScheduleTable::C_DELIVERY_DATE;

        return $this->db->getTable($sql . $filter . $orderby);
    }

    function getCorporateGiftInquiry($name, $phone, $delivery_from, $delivery_to, $gift){
        $sql = "SELECT " .
                    CustomerScheduleTable::C_ID . "," .
                    CustomerScheduleTable::C_DELIVERY_DATE . "," .
                    CustomerScheduleTable::C_DELIVERY_TIME . "," .
                    CustomerTable::C_NAME . "," .
                    CustomerTable::C_ADDRESS . "," .
                    CustomerContactTable::C_PREFER_NAME . "," .
                    CustomerContactTable::C_MOBILE . "," .
                    CustomerContactTable::C_TELEPHONE . "," .
                    CorporateGiftDetailTable::C_ITEM_NAME . "," .
                    CustomerScheduleTable::C_GIFT_AMOUNT . "," .
                    CustomerScheduleTable::C_REMARKS . 
                " FROM " . CustomerScheduleTable::C_TABLE . " SCH " .
                " LEFT JOIN " . CustomerContactTable::C_TABLE . " CON ON CON." . CustomerContactTable::C_CONTACT_ID . " = SCH." . CustomerScheduleTable::C_NAME_ID .
                " LEFT JOIN " . CustomerTable::C_TABLE . " CUS ON CUS." . CustomerTable::C_ID . " = CON." . CustomerContactTable::C_ID .
                " LEFT JOIN " . CorporateGiftTable::C_TABLE . " GIF ON SCH." . CustomerScheduleTable::C_GIFT_MASTER_ID . " = GIF." . CorporateGiftTable::C_ID .
                " LEFT JOIN " . CorporateGiftDetailTable::C_TABLE . " DET " .
                    " ON SCH." . CustomerScheduleTable::C_GIFT_MASTER_ID . " = DET." . CorporateGiftDetailTable::C_ID . 
                    " AND SCH." . CustomerScheduleTable::C_CHOSEN_GIFT_ITEM . " = DET." . CorporateGiftDetailTable::C_DETAIL_ID .

                    " WHERE " . CustomerScheduleTable::C_GIFT_MASTER_ID . " = " . $gift . " AND " . CustomerScheduleTable::C_DELIVERY_DATE . " != '1969-12-31'";
        $filter = "";

        if($name != ""){
            $filter .= " AND " . CustomerContactTable::C_PREFER_NAME . " LIKE '%" . $name . "%'";
        }
        if($phone != ""){
            // $filter .= " AND " . CustomerContactTable::C_MOBILE . " LIKE '%" . $phone . "%'";
            $filter .= " AND " . CustomerContactTable::C_TELEPHONE . " LIKE '%" . $phone . "%'";
        }

        if($delivery_from != ""){
            $filter .= " AND " . CustomerScheduleTable::C_DELIVERY_DATE . " >= '" . $delivery_from . "'";
        }

        if($delivery_to != ""){
            $filter .= " AND " . CustomerScheduleTable::C_DELIVERY_DATE . " <= '" . $delivery_to . "'";
        }

        if($gift != "" && $gift != 0){
            $filter .= " AND " . CustomerScheduleTable::C_GIFT_MASTER_ID . " = " . $gift;
        }

        $orderby = " ORDER BY " . CustomerScheduleTable::C_DELIVERY_DATE;

        return $this->db->getTable($sql . $filter . $orderby);
    }

    function getCorporateGiftDetailByID($id){
        $sql = "SELECT " .
                    CustomerTable::C_NAME . "," .
                    CustomerTable::C_ADDRESS . "," .
                    CustomerContactTable::C_PREFER_NAME . "," .
                    CustomerContactTable::C_MOBILE . "," .
                    CustomerContactTable::C_EMAIL . "," .
                    CustomerScheduleTable::C_ID . "," .
                    CustomerScheduleTable::C_GIFT_AMOUNT . "," .
                    CustomerScheduleTable::C_GIFT_MASTER_ID . "," .
                    CustomerScheduleTable::C_CHOSEN_GIFT_ITEM . "," .
                    CustomerScheduleTable::C_DELIVERY_DATE . "," .
                    CustomerScheduleTable::C_DELIVERY_TIME . "," .
                    CustomerScheduleTable::C_EMAIL_DATE . "," .
                    CustomerScheduleTable::C_EMAIL_TIME . "," .
                    CustomerScheduleTable::C_REMARKS . 
                " FROM " . CustomerScheduleTable::C_TABLE . " SCH " .
                " LEFT JOIN " . CustomerContactTable::C_TABLE . " CON ON CON." . CustomerContactTable::C_CONTACT_ID . " = SCH." . CustomerScheduleTable::C_NAME_ID .
                " LEFT JOIN " . CustomerTable::C_TABLE . " CUS ON CUS." . CustomerTable::C_ID . " = CON." . CustomerContactTable::C_ID .
                    " WHERE " . CustomerScheduleTable::C_ID . " = " . $id;

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

        if(!is_null($rows) && count($rows) > 0 ){
            return $rows[0];
        }else{
            return null;
        }

    }
}
