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

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

	function __destruct() {
	}

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

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

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

    function getJobNumber($jobnumber) {
        $sql = "SELECT MAX(" . JobQuotationTable::C_JOB_NUMBER . ") AS TOTAL  ";
        $sql .= "FROM " . JobQuotationTable::C_TABLE . " ";
        $sql .= "WHERE " . JobQuotationTable::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 ( " . JobQuotationTable::C_ID . ")+1 AS " . JobQuotationTable::C_ID . "  ";
        $sql .= "FROM " . JobQuotationTable::C_TABLE . " ";
        
        $rows = $this->db->getTable($sql);
        return $rows;
        
    }

    function getJobQuotationList($orderby='', $titlefilter = '', $customerFilter = '', $custCodeFilter = '', $jobtypefilter = 0, $statusFilter = 'all',$fromdate='', $todate='',$jobnumber='',$converted=-1){
        $sql = "SELECT * FROM " . JobQuotationTable::C_TABLE . " J ";
        $sql .= "LEFT JOIN " . CustomerTable::C_TABLE . " C ON (C.".CustomerTable::C_ID." = J.".JobQuotationTable::C_CUST_ID.")";
        //$filter =" where ".JobQuotationTable::C_CONVERT." = 1 " ;
        $filter ="";
        if($jobtypefilter != 0){
            if($filter != "") $filter .= "AND ";
            else $filter .= "WHERE ";
            
            $filter = "WHERE J." . JobQuotationTable::C_JOB_NAME . " like '%" . $titlefilter . "%' ";
        }

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

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

        if($jobtypefilter != 0){
            if($filter != "") $filter .= "AND ";
            else $filter .= "WHERE ";
            
            $filter .= " J." . JobQuotationTable::C_JOB_TYPE . " = " . $jobtypefilter . " ";
        }
        if($jobnumber != ''){
            if($filter != "") $filter .= "AND ";
            else $filter .= "WHERE ";
            
            $filter .= " J." . JobQuotationTable::C_JOB_NUMBER . " LIKE '%" . $jobnumber . "%' ";
        }

        if($converted != -1){
            if($filter != "") $filter .= "AND ";
            else $filter .=  " WHERE ";
            
            $filter .= " J." . JobQuotationTable::C_CONVERT . " = " . $converted . " ";
        }

        if($statusFilter != 'all'){
            if($filter != "") $filter .= "AND ";
            else $filter .= "WHERE ";
            
            if($statusFilter == 'active')
                $filter .= " J." . JobQuotationTable::C_STATUS . " = 1 ";
            else if($statusFilter == 'non-active')
                $filter .= " J." . JobQuotationTable::C_STATUS . " = 0 ";
        }

        if($fromdate!='' && $todate!=''){
            if($filter != "") $filter .= "AND ";
            else $filter .= "WHERE ";

			$filter .= " cast(J." . JobQuotationTable::C_JOB_START_DATE . " as date) BETWEEN '" . $fromdate . "' AND '" . $todate . "' ";
        }
        
        if($orderby != "")
            $filter .= " ORDER BY " . $orderby;
        
        $rows = $this->db->getTable($sql . $filter);
        
        return $rows;
    }

    function changeConvertPlanStatus($id, $status){
        $sql = "UPDATE " . JobQuotationTable::C_TABLE . 
                " SET " . JobQuotationTable::C_CONVERT . " = " .$status. ", " . JobQuotationTable::C_STATUS . " = " .$status. " " .
                " WHERE " . JobQuotationTable::C_ID . " = '" . $id. "'";

        try{
            $this->db->executeSql($sql);
        } catch (Exception $e){
            Log::write("[JobQuotationTable]" . $e->getMessage());
        }
    }
}
?>