<?php
require_once (PATH_TABLES . "activity/JobCentreTable.php") ;
require_once (PATH_MODELS . "base/MasterBase.php") ;

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

	function __destruct() {
	}

    function getJobNumberListSuggest() {
        $sql = "select distinct ".JobCentreTable::C_JOB_NUMBER." from " . JobCentreTable::C_TABLE ." ";

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

        if (is_null($rows) || count($rows) == 0)
            return null ;
        else 
            return $rows ; 
    }
    function getJobPlanNoByQuot($id)
    {
        $sql = "SELECT TOP 1 " . JobCentreTable::C_JOB_NUMBER . "  ";
        $sql .= "FROM " . JobCentreTable::C_TABLE . " ";
        $sql .= "WHERE " . JobCentreTable::C_JOB_QUOTATION_ID . " = ". $id ."";

        $rows = $this->db->getTable($sql);
        $job_number = "";
        if(!is_null($rows) && count($rows)>0) {
            
            $job_number = $rows[0][JobCentreTable::C_JOB_NUMBER];
            return $job_number;

        } else {
            return $job_number;
        }

    }
    function getJobNumber($jobnumber) {
        $sql = "SELECT COUNT(" . JobCentreTable::C_JOB_NUMBER . ") AS TOTAL  ";
        $sql .= "FROM " . JobCentreTable::C_TABLE . " ";
        $sql .= "WHERE " . JobCentreTable::C_JOB_NUMBER . " LIKE '%". $jobnumber ."%'";
        
        $rows = $this->db->getTable($sql);
        if($rows[0]['TOTAL']>0) {
            $ctr = $rows[0]['TOTAL'];
            $code = $jobnumber ."-". str_pad($ctr, 2, "0",STR_PAD_LEFT);

            return $code;
        } else {
            return $jobnumber;
        }
    }

    function getMaxID(){
        $sql = "SELECT MAX ( " . JobCentreTable::C_ID . ")+1 AS " . JobCentreTable::C_ID . "  ";
        $sql .= "FROM " . JobCentreTable::C_TABLE . " ";
        
        $rows = $this->db->getTable($sql);
        return $rows;
        
    }

    function getJobCentreList($customerFilter = '', $custCodeFilter = '', $orderby=''){
        $sql = "SELECT * FROM " . JobCentreTable::C_TABLE . " J ";
        $sql .= "LEFT JOIN " . CustomerTable::C_TABLE . " C ON (C.".CustomerTable::C_ID." = J.".JobCentreTable::C_CUST_ID.")";
        // $filter =" where ".JobCentreTable::C_CONVERT." = 1 " ;
        $filter ="";
        if($customerFilter != ''){
            if($filter != "") $filter .= "AND ";
            else $filter .= "WHERE ";
            
            $filter .= " C." . CustomerTable::C_NAME . " like '%" . str_replace("'", "''", $customerFilter) . "%' ";
        }

        if($custCodeFilter != ''){
            if($filter != "") $filter .= "AND ";
            else $filter .= "WHERE ";
            
            $filter .= " C." . CustomerTable::C_CODE . " like '%" . $custCodeFilter . "%' ";
        }

        if($orderby != "")
            $filter .= " ORDER BY " . $orderby;
     
        $rows = $this->db->getTable($sql . $filter);
        
        return $rows;
    }

    function getJobCentreListing($orderby='') {
        $sql = "SELECT * FROM " . JobCentreTable::C_TABLE . " J ";
        $sql .= "LEFT JOIN " . CustomerTable::C_TABLE . " C ON (C.".CustomerTable::C_ID." = J.".JobCentreTable::C_CUST_ID.")";

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

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

    function getCustomerProjectList($custIdFilter = 0, $orderby = '') {
        $sql = " SELECT J.".JobCentreTable::C_ID.", J.". JobCentreTable::C_JOB_NUMBER.", J.". JobCentreTable::C_JOB_NAME.", J.".JobCentreTable::C_JOB_START_DATE.", J.".JobCentreTable::C_JOB_END_DATE.", Q.".JobQuotationTable::C_JOB_NUMBER." AS JOB_QUOTATION_NUMBER, J.".JobCentreTable::C_STATUS.", J.".JobCentreTable::C_CUST_CONTACT_ID.
                " FROM " . JobCentreTable::C_TABLE . " J " .
                "LEFT JOIN " . JobQuotationTable::C_TABLE . " Q ON (Q.".JobQuotationTable::C_ID." = J.".JobCentreTable::C_JOB_QUOTATION_ID.") " .
                // "LEFT JOIN " . CustomerContactTable::C_TABLE . " C ON (C.".CustomerContactTable::C_ID." = J.".JobCentreTable::C_CUST_ID." AND C.".CustomerContactTable::C_DESIGNATION." = 'Project Manager') ".
                "WHERE J." . JobCentreTable::C_CUST_ID . " = " . $custIdFilter ;

        if($orderby != "") {
            $sql .= " ORDER BY " . $orderby;
        }
        // log::write($sql);
        $rows = $this->db->getTable($sql);
        
        return $rows;
    }
}
?>