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

class ProspectContactClass extends MasterBase {
    function __construct($db) {
        $this->db       = $db ;
        $this->tbl      = ProspectContactTable::C_TABLE ;
        $this->fldid2   = ProspectContactTable::C_ID ;
        $this->fldid    = ProspectContactTable::C_CONTACT_ID ;
        $this->flddesc  = ProspectContactTable::C_NAME ;
    }
    function __destruct() {
    }
    function getProspectContactByCompanyName($desc, $name){
        $sql  = "SELECT * FROM " . ProspectContactTable::C_TABLE . " contact " .
                " LEFT JOIN " . ProspectTable::C_TABLE . " prospect ON (contact." . ProspectContactTable::C_ID . " = prospect." . ProspectTable::C_ID . ")" .
                " LEFT JOIN " . PersonContactTable::C_TABLE . " person ON (contact." . ProspectContactTable::C_PERSON_CONTACT . " = person." . PersonContactTable::C_ID . ")";
        $sql .= " WHERE " . ProspectTable::C_NAME . " = '" . $desc . "' AND " . PersonContactTable::C_FULL_NAME . " = '" . $name . "'";
        
        $rows = $this->db->getTable($sql);
        if(!is_null($rows) && count($rows) > 0){
            return $rows[0];
        }
        return null;
    }

    function getProspectContact($id, $orderby='', $api=false){
        if ($api) {
             $sql = "SELECT ".$this->fldid2.",".$this->flddesc." FROM " ;
        } else {
            $sql = "SELECT * FROM " ;
        }

        $sql .= ProspectContactTable::C_TABLE . " WHERE " . ProspectContactTable::C_ID . " = " . $id;
        if($orderby != "")
            $sql .= " ORDER BY " . $orderby;

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

        if(!is_null($rows) && count($rows) > 0){
            return $rows;
        }

        return null;
    }
    function getProspectContactByContactId($id, $contactId,$active=""){
        $sql = "SELECT * FROM " . ProspectContactTable::C_TABLE . " WHERE " . ProspectContactTable::C_ID . " = " . $id .
                " AND " . ProspectContactTable::C_CONTACT_ID . " = " . $contactId;

        if($active==""){
            $sql .= " AND " .ProspectContactTable::C_ACTIVE. "= 1 ";
        }
        $rows = $this->db->getTable($sql);

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

        return null;
    }

    function deleteProspectContactByContactId($id, $contactId){
            $sql = "DELETE FROM " . ProspectContactTable::C_TABLE .
                    " WHERE " . ProspectContactTable::C_ID . " = " . $id .
                    " AND " . ProspectContactTable::C_CONTACT_ID . " = " . $contactId;

        return $this->db->executeSql($sql);
    }
    function deleteProspectContact($id){
            $sql = "DELETE FROM " . ProspectContactTable::C_TABLE .
                    " WHERE " . ProspectContactTable::C_ID . " = " . $id;

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

    function getProspectContactbyDesignation($custId) {
        $sql = "SELECT * FROM " ;
        $sql .= ProspectContactTable::C_TABLE . " WHERE " . ProspectContactTable::C_ID . " = " . $custId;
        $sql .= " AND (" . ProspectContactTable::C_DESIGNATION . " LIKE '%Project Sponsor%'";
        $sql .= " OR " . ProspectContactTable::C_DESIGNATION . " LIKE '%Project Advisor%'";
        $sql .= " OR " . ProspectContactTable::C_DESIGNATION . " LIKE '%Project Manager%'";
        $sql .= " OR " . ProspectContactTable::C_DESIGNATION . " LIKE '%Finance%' )";

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

        if(!is_null($rows) && count($rows) > 0){
            $datas = [];
            $datas['project_sponsor'] = " ";
            $datas['project_advisor'] = " ";
            $datas['project_manager'] = " ";
            $datas['finance'] = " ";

            foreach ($rows as $row) {
                if(strpos($row[ProspectContactTable::C_DESIGNATION], 'Project Sponsor') !== false) {
                    $datas['project_sponsor'] = $row[ProspectContactTable::C_NAME];
                }
                if(strpos($row[ProspectContactTable::C_DESIGNATION], 'Project Advisor') !== false) {
                    $datas['project_advisor'] = $row[ProspectContactTable::C_NAME];
                }
                if(strpos($row[ProspectContactTable::C_DESIGNATION], 'Project Manager') !== false) {
                    $datas['project_manager'] = $row[ProspectContactTable::C_NAME];
                }
                if(strpos($row[ProspectContactTable::C_DESIGNATION], 'Finance') !== false) {
                    $datas['finance'] = $row[ProspectContactTable::C_NAME];
                }
            }
            return $datas;
        }

        return null;
    }

    function getProspectFinanceAndIT($custId) {
        $sql = "SELECT TOP 4 * FROM " ;
        $sql .= ProspectContactTable::C_TABLE . " WHERE " . ProspectContactTable::C_ID . " = " . $custId;
        $sql .= " AND (" . ProspectContactTable::C_DESIGNATION . " LIKE '%Finance%'";
        $sql .= " OR " . ProspectContactTable::C_DESIGNATION . " LIKE '%IT%' )";
        $sql .= " ORDER BY " . ProspectContactTable::C_CONTACT_ID . " ASC";

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

        if(!is_null($rows) && count($rows) > 0){
            $datas = [];
            $datas['it'] = "";
            $datas['finance'] = "";
            foreach ($rows as $row) {
                if(strpos($row[ProspectContactTable::C_DESIGNATION], 'Finance') !== false) {
                    if($datas['finance']=="") {
                        $datas['finance'] = $row[ProspectContactTable::C_NAME];
                    } else {
                        $datas['finance2'] = $row[ProspectContactTable::C_NAME];
                    }
                }
                if(strpos($row[ProspectContactTable::C_DESIGNATION], 'IT') !== false) {
                    if($datas['it']=="") {
                        $datas['it'] = $row[ProspectContactTable::C_NAME];
                    } else {
                        $datas['it2'] = $row[ProspectContactTable::C_NAME];
                    }
                }
            }
            // Log::write(print_r($datas,true));
            return $datas;
        }

        return null;
    }

    function getIdByContactName($cust_id, $desc) {
        $sql = "select " . $this->fldid . " from " . $this->tbl
        . " where " . $this->db->fieldParam($this->flddesc)
        . " and " . $this->db->fieldParam($this->fldid2);

        $params = array();
        $params[] = $this->db->valueParam($this->flddesc, strtolower($desc));
        $params[] = $this->db->valueParam($this->fldid2, $cust_id);

        $rows = $this->db->getRow($sql, $params);
        // Log::write(print_r($sql, true));
        if (is_null($rows) || count($rows) == 0)
            return "";
        else
            return $rows[0][$this->fldid];
    }

    public function getExistingRecords($cust_id, $contact_name)
    {
        $sql  = "SELECT * FROM " . ProspectContactTable::C_TABLE .
            " WHERE " . ProspectContactTable::C_ID . " = '" . $cust_id . "' " .
            " AND " . ProspectContactTable::C_NAME . " = '" . $contact_name . "' ";

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

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

    
    function deleteSuspectContactByArray($array){
        $sql = "DELETE FROM " . ProspectContactTable::C_TABLE 
                ." WHERE " . ProspectContactTable::C_CONTACT_ID . " IN (" . $array . ")";
        $this->db->executeSql($sql);

        $sql = "DELETE FROM " . ProspectConversationTable::C_TABLE 
                ." WHERE " . ProspectConversationTable::C_CONTACT_ID . " IN (" . $array . ")";
        $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 deleteProspectContactFromProspect($prospect_id, $contact_ids){
        $sql = "DELETE FROM " . ProspectContactTable::C_TABLE .
                    " WHERE " . ProspectContactTable::C_ID . " = " . $prospect_id . 
                    " AND " . ProspectContactTable::C_CONTACT_ID . " NOT IN (" . $contact_ids . ")";

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

    function exportProspectContactWithCompanyInfo($status = "All"){
        $sql = "SELECT * FROM " . ProspectContactTable::C_TABLE . " con " .
                " LEFT JOIN " . ProspectTable::C_TABLE . " pro ON pro." . ProspectTable::C_ID . " = con." . ProspectContactTable::C_ID .
                " LEFT JOIN " . PersonContactTable::C_TABLE . " per ON per." . PersonContactTable::C_ID . " = con." . ProspectContactTable::C_PERSON_CONTACT .
                " LEFT JOIN " . IndustryTable::C_TABLE . " ind ON pro." . ProspectTable::C_INDUSTRY . " = ind." . IndustryTable::C_ID;

        if($status == "New"){
            $sql .= " WHERE " . ProspectContactTable::C_EXPORT_STATUS . " = 0";
        }
        $rows = $this->db->getTable($sql);

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

    function markedAsExported(){
        $sql = "UPDATE " . ProspectContactTable::C_TABLE . " SET " . ProspectContactTable::C_EXPORT_STATUS . " = 1";
        $this->db->executeSql($sql);
    }

    function clearActionDateAndNextAction($contact_id){
        $sql = "UPDATE " . ProspectContactTable::C_TABLE .
                " SET " . ProspectContactTable::C_ACTION_DATE . " = '" . NULL_DATE . "', " 
                        . ProspectContactTable::C_NEXT_ACTION . " = ''" .
                " WHERE " . ProspectContactTable::C_CONTACT_ID . " = " . $contact_id;
        $this->db->executeSql($sql);
    }

    function getPersonContactByContactID($id){
        $sql = "SELECT * FROM " . ProspectContactTable::C_TABLE . " CON " .
                " LEFT JOIN " . PersonContactTable::C_TABLE . " PER ON CON."  . ProspectContactTable::C_PERSON_CONTACT . " = PER." . PersonContactTable::C_ID .
                " WHERE " . ProspectContactTable::C_CONTACT_ID . " = " . $id;
        $rows = $this->db->getTable($sql);

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