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

class ReportClass extends MasterBase {
	function __construct($db) {
		$this->db	= $db ;
	}

	function __destruct() {
	}
	
    function getJobStatusSummaryReport($params){
        $jobCentreId = $params['id'];
        $customer = $params['custid'];
        $jobNo = $params['jobno'];
        $jobStatus = $params['status'];
        $fromDate = $params['fdate'];
        $toDate = $params['tdate'];
        $coyId = $params['coy'];
		
        $sql = "SELECT J.*,C.".CustomerTable::C_ID.", C.".CustomerTable::C_CODE.", C.".CustomerTable::C_NAME ;
        $sql .= " FROM " . JobCentreTable::C_TABLE . " J ";
        $sql .= "LEFT JOIN " . CustomerTable::C_TABLE . " C ON (C.".CustomerTable::C_ID." = J.".JobCentreTable::C_CUST_ID.")";
		
		$filter = " WHERE " ;
		if($jobCentreId != 0){
			$filter .= " J.".JobCentreTable::C_ID." = ".$jobCentreId." " ;
		} else {
			if($jobStatus == -1) {
				$filter .= " J.".JobCentreTable::C_STATUS." IN (0,1)" ;
			} else {
				$filter .= " J.".JobCentreTable::C_STATUS." IN (".$jobStatus.")" ;
			}
			if($fromDate != "" && $toDate != "") {
				$fromDate    = date_create(str_replace('/', '-', $params['fdate'] ))->format('Y-m-d');
				$toDate      = date_create(str_replace('/', '-', $params['tdate'] ))->format('Y-m-d');
				$filter 	.= " AND  J.".JobCentreTable::C_JOB_CONFIRM_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' " ;
			} else if($fromDate == "" && $toDate != "") {
				// $fromDate    = date_create(str_replace('/', '-', $params['fdate'] ))->format('Y-m-d');
				$toDate      = date_create(str_replace('/', '-', $params['tdate'] ))->format('Y-m-d');
				$filter 	.= " AND  J.".JobCentreTable::C_JOB_CONFIRM_DATE." <= '".$toDate." 23:59' " ;
			}
			if($customer != ''){
				$filter .= " AND C.".CustomerTable::C_NAME." LIKE '%".$customer."%' " ;
			}
			if($jobNo != ""){
				$filter .= " AND  J.".JobCentreTable::C_JOB_NUMBER." = '".$jobNo."' " ;
			}
			if($coyId != 0){
				$filter .= " AND  J.".JobCentreTable::C_COY_ID." = ".$coyId." " ;
			}
		}
		// $orderby = " ORDER BY J.".JobCentreTable::C_COY_ID.",J.".JobCentreTable::C_JOB_NUMBER.", J.".JobCentreTable::C_CUST_ID;
		// $orderby = " ORDER BY J.".JobCentreTable::C_COY_ID.", J.".JobCentreTable::C_JOB_CONFIRM_DATE.", J.".JobCentreTable::C_JOB_START_DATE;   

		$sorts  = $params['sorts'];
		$orderby = "";
        $sort = array();
        if($sorts != ""){
            $sort = explode(':',$sorts);
            switch($sort[0]) {
                case "col_jobnumber":
                    $orderby = " ORDER BY J.". JobCentreTable::C_JOB_NUMBER." ".$sort[1];
                break;
                case "col_jobtitle":
                    $orderby = " ORDER BY J.". JobCentreTable::C_JOB_NAME." ".$sort[1];
                break;
                case "col_jobtype":
                    $orderby = " ORDER BY J.". JobCentreTable::C_JOB_TYPE." ".$sort[1];
                break;
                case "col_jobcustomer":
                    $orderby = " ORDER BY C.". CustomerTable::C_NAME." ".$sort[1];
                break;
                case "col_confirmdate":
                    $orderby = " ORDER BY J.". JobCentreTable::C_JOB_CONFIRM_DATE." ".$sort[1];
                break;
                case "col_startdate":
                    $orderby = " ORDER BY J.". JobCentreTable::C_JOB_START_DATE." ".$sort[1];
                break;
                case "col_enddate":
                    $orderby = " ORDER BY J.". JobCentreTable::C_JOB_END_DATE." ".$sort[1];
                break;
                case "col_jobstatus":
                    $orderby = " ORDER BY J.". JobCentreTable::C_STATUS." ".$sort[1];
                break;
                case "col_totalmandays":
                    $orderby = " ORDER BY J.". JobCentreTable::C_TOTAL_MANDAYS." ".$sort[1];
                break;
                default:
                    $orderby = " ORDER BY J.". JobCentreTable::C_JOB_NAME." ".$sort[1];
                    break;
            }
        } else {
            $orderby = " ORDER BY J.".JobCentreTable::C_JOB_CONFIRM_DATE;
		}
        log::write($sql.$filter);
        $rows = $this->db->getTable($sql . $filter . $orderby);
        
        return $rows;
    }
	
    function getJobBillingStatusSummaryReport($params){
        $jobCentreId = $params['id'];
        // $customer = $params['custid'];
        // $jobNo = $params['jobno'];
        // $jobType = $params['jobtype'];
        $fromDate = $params['fdate'];
        $toDate = $params['tdate'];
        $coyId = $params['coy'];
		
        $sql = "SELECT J.*,C.".CustomerTable::C_ID.", C.".CustomerTable::C_CODE.", C.".CustomerTable::C_NAME ;
        $sql .= " FROM " . JobCentreTable::C_TABLE . " J ";
        $sql .= " LEFT JOIN " . CustomerTable::C_TABLE . " C ON (C.".CustomerTable::C_ID." = J.".JobCentreTable::C_CUST_ID.")";
		
		$filter = "" ;
		if($jobCentreId != 0){
			$filter .= " J.".JobCentreTable::C_ID." = ".$jobCentreId." " ;
		} else {
			// if($jobType == 0) {
				$filter .= " J.".JobCentreTable::C_JOB_TYPE." IN (1,2,3)" ;
			// } else {
			// 	$filter .= " J.".JobCentreTable::C_JOB_TYPE." IN (".$jobType.")" ;
			// }
			if($fromDate != "" && $toDate != "") {
				$fromDate    = date_create(str_replace('/', '-', $params['fdate'] ))->format('Y-m-d');
				$toDate      = date_create(str_replace('/', '-', $params['tdate'] ))->format('Y-m-d');
				$filter 	.= " AND J.".JobCentreTable::C_JOB_CONFIRM_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' " ;
			}
			/* if($customer != ''){
				$filter .= " AND C.".CustomerTable::C_NAME." LIKE '%".$customer."%' " ;
			}
			if($jobNo != ""){
				$filter .= " AND J.".JobCentreTable::C_JOB_NUMBER." = '".$jobNo."' " ;
			} */
			if($coyId != 0){
				$filter .= " AND  J.".JobCentreTable::C_COY_ID." = ".$coyId." " ;
			}
		}

		if($filter != "") {
			$filter = " WHERE " . $filter;
		}

		$sorts  = $params['sorts'];
		$orderby = "";
        $sort = array();
        if($sorts != ""){
            $sort = explode(':',$sorts);
            switch($sort[0]) {
                case "col_jobnumber":
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_NUMBER." ".$sort[1];
                break;
                case "col_jobtitle":
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_NAME." ".$sort[1];
                break;
                case "col_jobtype":
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_TYPE." ".$sort[1];
                break;
                case "col_jobcustomer":
                    $orderby = " ORDER BY ". CustomerTable::C_NAME." ".$sort[1];
                break;
                case "col_totalmandays":
                    $orderby = " ORDER BY ". JobCentreTable::C_TOTAL_MANDAYS." ".$sort[1];
                break;
                default:
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_NAME." ".$sort[1];
                    break;
            }
        } else {
            $orderby = " ORDER BY J.".JobCentreTable::C_COY_ID.",J.".JobCentreTable::C_JOB_NUMBER.",J.".JobCentreTable::C_CUST_ID;
		}
     
        $rows = $this->db->getTable($sql . $filter . $orderby);
        
        return $rows;
    }
	
    function getBillingListingByJobReport($params){
        $jobCentreId = $params['id'];
        $fromMonth = $params['fmonth'];
        $fromYear = $params['fyear'];
        $toMonth = $params['tmonth'];
        $toYear = $params['tyear'];
        $customer = $params['custid'];
        $coyId = $params['coy'];
		
		$sql = "SELECT B.*, J.*, C.".CustomerTable::C_ID.", C.".CustomerTable::C_CODE.", C.".CustomerTable::C_NAME ;
        $sql .= " FROM ".JobCentreBillingTable::C_TABLE." B " ;
        $sql .= " LEFT JOIN ".JobCentreTable::C_TABLE." J ON J.".JobCentreTable::C_ID." = B.".JobCentreBillingTable::C_ID ;
        $sql .= " LEFT JOIN " . CustomerTable::C_TABLE . " C ON C.".CustomerTable::C_ID." = J.".JobCentreTable::C_CUST_ID;
		
		$filter = " WHERE " ;
		if($jobCentreId != 0){
			$filter .= " J.".JobCentreTable::C_ID." = ".$jobCentreId." " ;
		} else {
			$fromDate	= $fromYear."-".str_pad($fromMonth,2,"0",STR_PAD_LEFT)."-01";
			$toDate		= $toYear."-".str_pad($toMonth,2,"0",STR_PAD_LEFT)."-01";
			$filter 	.= " J.".JobCentreTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' " ;
			if($customer != ''){
				$filter .= " AND C.".CustomerTable::C_NAME." LIKE '%".$customer."%' " ;
			}
			if($coyId != 0){
				$filter .= " AND  J.".JobCentreTable::C_COY_ID." = ".$coyId." " ;
			}
		}
        $orderby = " ORDER BY J.".JobCentreTable::C_COY_ID.",J.".JobCentreTable::C_JOB_NUMBER.", J.".JobCentreTable::C_CUST_ID;
		
        $rows = $this->db->getTable($sql . $filter . $orderby);
        
        return $rows;
    }
	
    function getPlanScheduleSummaryReport($params){
        $jobCentreId = $params['id'];
        $customer = $params['custid'];
        $jobNo = $params['jobno'];
        $jobType = $params['jobtype'];
        $fromDate = $params['fdate'];
        $toDate = $params['tdate'];
        $coyId = $params['coy'];
		
        $sql = "SELECT J.*,C.".CustomerTable::C_ID.", C.".CustomerTable::C_CODE.", C.".CustomerTable::C_NAME ;
        $sql .= " FROM " . JobCentreTable::C_TABLE . " J ";
        $sql .= " LEFT JOIN " . CustomerTable::C_TABLE . " C ON (C.".CustomerTable::C_ID." = J.".JobCentreTable::C_CUST_ID.")";
		
		$filter = " WHERE " ;
		if($jobCentreId != 0){
			$filter .= " J.".JobCentreTable::C_ID." = ".$jobCentreId." " ;
		} else {
			$filter .= " J.".JobCentreTable::C_JOB_TYPE." IN (3)" ;
			if($fromDate != "" && $toDate != "") {
				$fromDate    = date_create(str_replace('/', '-', $params['fdate'] ))->format('Y-m-d');
				$toDate      = date_create(str_replace('/', '-', $params['tdate'] ))->format('Y-m-d');
				$filter 	.= " AND J.".JobCentreTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' " ;
			}
			if($customer != ''){
				$filter .= " AND C.".CustomerTable::C_NAME." LIKE '%".$customer."%' " ;
			}
			if($jobNo != ""){
				$filter .= " AND J.".JobCentreTable::C_JOB_NUMBER." = '".$jobNo."' " ;
			}
			if($coyId != 0){
				$filter .= " AND  J.".JobCentreTable::C_COY_ID." = ".$coyId." " ;
			}
		}
		$orderby = " ORDER BY J.".JobCentreTable::C_COY_ID.",J.".JobCentreTable::C_JOB_NUMBER.",J.".JobCentreTable::C_CUST_ID;
     
        $rows = $this->db->getTable($sql . $filter . $orderby);
        
        return $rows;
    }
	
    function getJobPlanVsActualReport($params){
        $jobCentreId = $params['id'];
        // $customer = $params['custid'];
        // $jobNo = $params['jobno'];
        // $jobType = $params['jobtype'];
		$coyId = $params['coy'];
		
        $sql = "SELECT J.*,C.".CustomerTable::C_ID.", C.".CustomerTable::C_CODE.", C.".CustomerTable::C_NAME ;
        $sql .= " FROM " . JobCentreTable::C_TABLE . " J ";
        $sql .= " LEFT JOIN " . CustomerTable::C_TABLE . " C ON (C.".CustomerTable::C_ID." = J.".JobCentreTable::C_CUST_ID.")";
		
		$filter = "" ;
		if($jobCentreId != 0){
			$filter .= " J.".JobCentreTable::C_ID." = ".$jobCentreId." " ;
		} else {
			/* if($jobType == 0) {
				$filter .= " J.".JobCentreTable::C_JOB_TYPE." IN (1,2)" ;
			} else {
				$filter .= " J.".JobCentreTable::C_JOB_TYPE." IN (".$jobType.")" ;
			}
			if($customer != ''){
				$filter .= " AND C.".CustomerTable::C_NAME." LIKE '%".$customer."%' " ;
			}
			if($jobNo != ""){
				$filter .= " AND J.".JobCentreTable::C_JOB_NUMBER." = '".$jobNo."' " ;
			} */
			if($coyId != 0){
				$filter .= " J.".JobCentreTable::C_COY_ID." = ".$coyId." " ;
			}
		}
		if($filter != "") {
			$filter = " WHERE " . $filter;
		}

		$sorts  = $params['sorts'];
		$orderby = "";
        $sort = array();
        if($sorts != ""){
            $sort = explode(':',$sorts);
            switch($sort[0]) {
                case "col_jobnumber":
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_NUMBER." ".$sort[1];
                break;
                case "col_jobtitle":
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_NAME." ".$sort[1];
                break;
                case "col_jobtype":
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_TYPE." ".$sort[1];
                break;
                case "col_jobcustomer":
                    $orderby = " ORDER BY ". CustomerTable::C_NAME." ".$sort[1];
                break;
                case "col_productype":
                    $orderby = " ORDER BY ". JobCentreTable::C_PRODUCT_TYPE." ".$sort[1];
                break;
                case "col_totalmandays":
                    $orderby = " ORDER BY ". JobCentreTable::C_TOTAL_MANDAYS." ".$sort[1];
                break;
                case "col_jobstatus":
                    $orderby = " ORDER BY ". JobCentreTable::C_STATUS." ".$sort[1];
                break;
                default:
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_NAME." ".$sort[1];
                    break;
            }
        } else {
            $orderby = " ORDER BY J.".JobCentreTable::C_COY_ID.",J.".JobCentreTable::C_JOB_NUMBER.",J.".JobCentreTable::C_CUST_ID;
		}
     
        $rows = $this->db->getTable($sql . $filter . $orderby);
        
        return $rows;
    }
	
    function getPlanVsActualSummaryReport($params){
        $jobCentreId = $params['id'];
        // $customer = $params['custid'];
        // $jobNo = $params['jobno'];
        // $jobType = $params['jobtype'];
        $fromDate = $params['fdate'];
        $toDate = $params['tdate'];
        $coyId = $params['coy'];
		
        $sql = "SELECT J.*,C.".CustomerTable::C_ID.", C.".CustomerTable::C_CODE.", C.".CustomerTable::C_NAME ;
        $sql .= " FROM " . JobCentreTable::C_TABLE . " J ";
        $sql .= " LEFT JOIN " . CustomerTable::C_TABLE . " C ON (C.".CustomerTable::C_ID." = J.".JobCentreTable::C_CUST_ID.")";
		
		$filter = "" ;
		if($jobCentreId != 0){
			$filter .= " J.".JobCentreTable::C_ID." = ".$jobCentreId." " ;
		} else {
			// if($jobType == 0) {
				$filter .= " J.".JobCentreTable::C_JOB_TYPE." IN (1,2)" ;
			// } else {
			// 	$filter .= " J.".JobCentreTable::C_JOB_TYPE." IN (".$jobType.")" ;
			// }
			if($fromDate != "" && $toDate != "") {
				$fromDate    = date_create(str_replace('/', '-', $params['fdate'] ))->format('Y-m-d');
				$toDate      = date_create(str_replace('/', '-', $params['tdate'] ))->format('Y-m-d');
				$filter 	.= " AND J.".JobCentreTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' " ;
			}
			/* if($customer != ''){
				$filter .= " AND C.".CustomerTable::C_NAME." LIKE '%".$customer."%' " ;
			}
			if($jobNo != ""){
				$filter .= " AND J.".JobCentreTable::C_JOB_NUMBER." = '".$jobNo."' " ;
			} */
			if($coyId != 0){
				$filter .= " AND  J.".JobCentreTable::C_COY_ID." = ".$coyId." " ;
			}
		}

		if($filter != "") {
			$filter = " WHERE " . $filter;
		}

		$sorts  = $params['sorts'];
		$orderby = "";
        $sort = array();
        if($sorts != ""){
            $sort = explode(':',$sorts);
            switch($sort[0]) {
                case "col_jobnumber":
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_NUMBER." ".$sort[1];
                break;
                case "col_jobtitle":
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_NAME." ".$sort[1];
                break;
                case "col_jobtype":
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_TYPE." ".$sort[1];
                break;
                case "col_jobcustomer":
                    $orderby = " ORDER BY ". CustomerTable::C_NAME." ".$sort[1];
                break;
                case "col_productype":
                    $orderby = " ORDER BY ". JobCentreTable::C_PRODUCT_TYPE." ".$sort[1];
                break;
                case "col_totalmandays":
                    $orderby = " ORDER BY ". JobCentreTable::C_TOTAL_MANDAYS." ".$sort[1];
                break;
                case "col_jobstatus":
                    $orderby = " ORDER BY ". JobCentreTable::C_STATUS." ".$sort[1];
                break;
                default:
                    $orderby = " ORDER BY ". JobCentreTable::C_JOB_NAME." ".$sort[1];
                    break;
            }
        } else {
            $orderby = " ORDER BY J.".JobCentreTable::C_COY_ID.",J.".JobCentreTable::C_JOB_NUMBER.",J.".JobCentreTable::C_CUST_ID;
		}
     
        $rows = $this->db->getTable($sql . $filter . $orderby);
        
        return $rows;
    }
	
    function getPlanVsActualContractSummaryReport($params){
        $jobCentreId = $params['id'];
        $customer = $params['custid'];
        $jobNo = $params['jobno'];
        $jobType = $params['jobtype'];
        $fromDate = $params['fdate'];
        $toDate = $params['tdate'];
        $coyId = $params['coy'];
		
        $sql = "SELECT J.*,C.".CustomerTable::C_ID.", C.".CustomerTable::C_CODE.", C.".CustomerTable::C_NAME ;
        $sql .= " FROM " . JobCentreTable::C_TABLE . " J ";
        $sql .= " LEFT JOIN " . CustomerTable::C_TABLE . " C ON (C.".CustomerTable::C_ID." = J.".JobCentreTable::C_CUST_ID.")";
		
		$filter = " WHERE " ;
		if($jobCentreId != 0){
			$filter .= " J.".JobCentreTable::C_ID." = ".$jobCentreId." " ;
		} else {
			$filter .= " J.".JobCentreTable::C_JOB_TYPE." IN (3)" ;
			if($fromDate != "" && $toDate != "") {
				$fromDate    = date_create(str_replace('/', '-', $params['fdate'] ))->format('Y-m-d');
				$toDate      = date_create(str_replace('/', '-', $params['tdate'] ))->format('Y-m-d');
				$filter 	.= " AND J.".JobCentreTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' " ;
			}
			if($customer != ''){
				$filter .= " AND C.".CustomerTable::C_NAME." LIKE '%".$customer."%' " ;
			}
			if($jobNo != ""){
				$filter .= " AND J.".JobCentreTable::C_JOB_NUMBER." = '".$jobNo."' " ;
			}
			if($coyId != 0){
				$filter .= " AND  J.".JobCentreTable::C_COY_ID." = ".$coyId." " ;
			}
		}
		$orderby = " ORDER BY J.".JobCentreTable::C_COY_ID.", J.".JobCentreTable::C_JOB_NUMBER.",J.".JobCentreTable::C_CUST_ID;
     
        $rows = $this->db->getTable($sql . $filter . $orderby);
        
        return $rows;
    }    
	
    function getSalesQuotationSummaryReport($params){
        $coyId = $params['coy'];
        $jobStatus = $params['status'];
        $fromDate = $params['fdate'];
        $toDate = $params['tdate'];
        $filterDate   = $params['date'];
		
        $sql = "SELECT q.*, i.".InvoiceHeaderTable::C_INVOICE_NUMBER.", i.".InvoiceHeaderTable::C_CREATE_DATE." AS 'INVOICE_DATE' ".
                " FROM " .SalesQuotationTable::C_TABLE. " q ".
                " LEFT JOIN " .InvoiceHeaderTable::C_TABLE. " i ON i.". InvoiceHeaderTable::C_ID. " = q.". InvoiceHeaderTable::C_ID.
                " LEFT JOIN " .CustomerTable::C_TABLE. " c ON c.". CustomerTable::C_ID. " = q.". SalesQuotationTable::C_CUSTOMER_ID.
                " LEFT JOIN " .CustomerContactTable::C_TABLE. " t ON t.". CustomerContactTable::C_ID. " = q.". SalesQuotationTable::C_CUSTOMER_ID. " AND t.". CustomerContactTable::C_CONTACT_ID. " = q.". SalesQuotationTable::C_CUSTOMER_ID. 
                " WHERE q." .SalesQuotationTable::C_ID. " IN (".
                " SELECT TOP 1 WITH TIES " .SalesQuotationTable::C_ID.
                " FROM " .SalesQuotationTable::C_TABLE.
                " ORDER BY ".
                " ROW_NUMBER() OVER(PARTITION BY " .SalesQuotationTable::C_REF_ID. " ORDER BY " .SalesQuotationTable::C_ID. " DESC)".
                ")";

        if($coyId > 0) {
            $sql .= " AND q.".SalesQuotationTable::C_BU . " = " . $coyId;
        }

        if($jobStatus != "") {
            if($jobStatus > -1) {
                if($jobStatus == SalesQuotationStatus::Closed)
                    $sql .= " AND q.".SalesQuotationTable::C_IS_CLOSED . " = 1 ";
                else {
                    $sql .= " AND q.".SalesQuotationTable::C_STATUS . " = " . $jobStatus . " AND q.".SalesQuotationTable::C_IS_CLOSED . " = 0 ";
                }
            }
        }

        if($fromDate != "" && $toDate != "") {
            $fromDate    = date_create(str_replace('/', '-', $params['fdate'] ))->format('Y-m-d');
            $toDate      = date_create(str_replace('/', '-', $params['tdate'] ))->format('Y-m-d');
            switch ($filterDate) {
                case 0:
                    $sql 	.= " AND q.".SalesQuotationTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND q.".SalesQuotationTable::C_CREATE_DATE." <> '".NULL_DATE."' " ;
                    break;
                case 1:
                    $sql 	.= " AND q.".SalesQuotationTable::C_ISSUE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND q.".SalesQuotationTable::C_ISSUE_DATE." <> '".NULL_DATE."' " ;
                    break;
                case 2:
                    $sql 	.= " AND q.".SalesQuotationTable::C_REVISE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND q.".SalesQuotationTable::C_REVISE_DATE." <> '".NULL_DATE."' " ;
                    break;
                case 3:
                    $sql 	.= " AND q.".SalesQuotationTable::C_ACCEPT_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND q.".SalesQuotationTable::C_ACCEPT_DATE." <> '".NULL_DATE."' " ;
                    break;
                case 4:
                    $sql 	.= " AND q.".SalesQuotationTable::C_CLOSED_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND q.".SalesQuotationTable::C_CLOSED_DATE." <> '".NULL_DATE."' " ;
                    break;
                case 5: //proforma invoice date
                    $sql 	.= " AND i.".InvoiceHeaderTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND i.".InvoiceHeaderTable::C_CREATE_DATE." <> '".NULL_DATE."' " ;
                    break;
                case 6:
                    $sql 	.= " AND q.".SalesQuotationTable::C_EXPECTED_ACCEPT_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND q.".SalesQuotationTable::C_EXPECTED_ACCEPT_DATE." <> '".NULL_DATE."' " ;
                    break;
                case 7:
                    $sql 	.= " AND q.".SalesQuotationTable::C_EXPECTED_PAY_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND q.".SalesQuotationTable::C_EXPECTED_PAY_DATE." <> '".NULL_DATE."' " ;
                    break;
                default:
                    $sql 	.= " AND q.".SalesQuotationTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND q.".SalesQuotationTable::C_CREATE_DATE." <> '".NULL_DATE."' " ;
                    break;
            }            
        }
        // $orderby = " ORDER BY q." .SalesQuotationTable::C_CREATE_DATE. " DESC";

		$sorts  = $params['sorts'];
        // log::write($sorts);
		$orderby = "";
        $sort = array();
        if($sorts != ""){
            $sort = explode(':',$sorts);
            switch($sort[0]) {
                case "col_businessunit":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_BU." ".$sort[1];
                break;
                case "col_quotnumber":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_NUMBER." ".$sort[1];
                break;
                case "col_jobtype":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_TYPE." ".$sort[1];
                break;
                case "col_jobcustomer":
                    $orderby = " ORDER BY c.". CustomerTable::C_NAME." ".$sort[1];
                break;
                case "col_contactname":
                    $orderby = " ORDER BY t.". CustomerContactTable::C_NAME." ".$sort[1];
                break;
                case "col_jobtitle":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_TITLE." ".$sort[1];
                break;
                case "col_createdate":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_CREATE_DATE." ".$sort[1];
                break;
                case "col_issuedate":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_ISSUE_DATE." ".$sort[1];
                break;
                case "col_expacceptdate":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_EXPECTED_ACCEPT_DATE." ".$sort[1];
                break;
                case "col_acceptdate":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_ACCEPT_DATE." ".$sort[1];
                break;
                case "col_closeddate":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_CLOSED_DATE." ".$sort[1];
                break;
                case "col_invoicedate":
                    $orderby = " ORDER BY i.". InvoiceHeaderTable::C_CREATE_DATE." ".$sort[1];
                break;
                case "col_exppaydate":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_EXPECTED_PAY_DATE." ".$sort[1];
                break;
                case "col_percentagepayment":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_PERCENTAGE_PAYMENT." ".$sort[1];
                break;
                case "col_status":
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_STATUS." ".$sort[1];
                break;
                // case "col_totalamount":
                //     $orderby = " ORDER BY q.". SalesQuotationTable::C_TOTAL_MANDAYS." ".$sort[1];
                // break;
                default:
                    $orderby = " ORDER BY q.". SalesQuotationTable::C_CREATE_DATE." ".$sort[1];
                    break;
            }
        } else {
            $orderby = " ORDER BY q." .SalesQuotationTable::C_CREATE_DATE. " ASC";
		}
        // log::write($sql . $orderby);
        $rows = $this->db->getTable($sql . $orderby);

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

}
?>