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

class JobCentreBillingClass extends MasterBase {
	function __construct($db) {
		$this->db 		= $db ;
		$this->tbl 		= JobCentreBillingTable::C_TABLE ;
		$this->fldid 	= JobCentreBillingTable::C_ID ;
        $this->fldid2   = JobCentreBillingTable::C_BILLING_ID ;
	}

	function __destruct() {
	}

    function getJobCentreBillingReport($filter='',$orderby=''){
        $sql = "select c.*,b.* " .
            " from ".JobCentreBillingTable::C_TABLE." b " .
            " inner join ".JobCentreTable::C_TABLE." c on c.".JobCentreTable::C_ID." = b.".JobCentreBillingTable::C_ID ;
        if($filter != "") {
            $sql .= " where " . $filter ;
        }
        if($orderby != "") {
            $sql .= " order by " . $orderby ;
        }
        
        $rows = $this->db->getTable($sql);

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

    function getJobCentreBilling($id){
        $sql = "SELECT * FROM " ;
        $sql .= JobCentreBillingTable::C_TABLE . " WHERE " . JobCentreBillingTable::C_ID . " = " . $id;
        
        $rows = $this->db->getTable($sql);

        if(!is_null($rows) && count($rows) > 0){
            return $rows;
        }
        
        return null;
    }
    
    function getTotalNoInvoice($jobCentreId){
        $sql = "SELECT COUNT(". JobCentreBillingTable::C_BILL_INV .") AS [NO_INVOICE] ";
        $sql .= "FROM " . JobCentreBillingTable::C_TABLE . " ";
        $sql .= "WHERE " . JobCentreBillingTable::C_BILL_INV . " <> '' ";
        $sql .= "AND " . JobCentreBillingTable::C_ID . " = " . $jobCentreId . " ";
        $rows = $this->db->getTable($sql);

        return $rows;
    }

    function getTotalBill($jobCentreId){
        $sql = "SELECT COALESCE(SUM(" . JobCentreBillingTable::C_BILL_MANDAYS . "),0) AS [BILLED] ";
        $sql .= "FROM " . JobCentreBillingTable::C_TABLE . " ";
        $sql .= "WHERE " . JobCentreBillingTable::C_ID . " = " . $jobCentreId . " ";
        $sql .= "AND " . JobCentreBillingTable::C_BILL_INV . " <> '' ";

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

    function getTotalBillPaid($jobCentreId){
        $sql = "SELECT COALESCE(SUM(" . JobCentreBillingTable::C_BILL_PAID_MANDAYS . "),0) AS [PAID] ";
        $sql .= "FROM " . JobCentreBillingTable::C_TABLE . " ";
        $sql .= "WHERE " . JobCentreBillingTable::C_ID . " = " . $jobCentreId . " ";
        $sql .= "AND " . JobCentreBillingTable::C_BILL_INV . " <> '' ";

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

}
?>