<?php
require_once (PATH_TABLES . "activity/JobCentreTaskTable.php") ;
require_once (PATH_TABLES . "admin/UserTable.php") ;
require_once (PATH_MODELS . "base/MasterBase.php") ;

class JobCentreTaskClass extends MasterBase {
    function __construct($db) {
        $this->db       = $db ;
        $this->tbl      = JobCentreTaskTable::C_TABLE ;
        $this->fldid    = JobCentreTaskTable::C_ID ;
        $this->fldid2   = JobCentreTaskTable::C_DETAIL_ID ;
    }

    function __destruct() {
    }
    function getJobCenterTaskAgentList($id){
        $sql = "SELECT DISTINCT(j." . JobCentreTaskTable::C_AGENT . "), u." . UserTable::C_FULL_NAME . ", u." . UserTable::C_DESIGNATION .
                " FROM " . JobCentreTaskTable::C_TABLE . " AS j " .
                " LEFT OUTER JOIN " . UserTable::C_TABLE . " AS u ON u." . UserTable::C_ID . " = j." . JobCentreTaskTable::C_AGENT .
                " WHERE " . JobCentreTaskTable::C_ID . " = " . $id .
                " AND " . JobCentreTaskTable::C_AGENT . " <> 0 " .
                " ORDER BY j." . JobCentreTaskTable::C_AGENT ." ASC";

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

        if(!is_null($rows) && count($rows) > 0){
            return $rows;
        }
        
        return null;
    }
    function deleteTask($id){
        $sql = "delete from " . JobCentreTaskTable::C_TABLE 
        . " where " . $this->db->fieldParam(JobCentreTaskTable::C_ID) ;

        $params = array() ;
        $params[] = $this->db->valueParam(JobCentreTaskTable::C_ID,$id) ;
        
        return $this->db->deleteRows($sql,$params) ;
    }

    function getJobCenterTask($id){
        $sql = "SELECT * FROM " ;
        $sql .= JobCentreTaskTable::C_TABLE . " WHERE " . JobCentreTaskTable::C_ID . " = " . $id;
        $sql .= "ORDER BY " . JobCentreTaskTable::C_PHASE ." ASC";
        
        $rows = $this->db->getTable($sql);

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

    function getAssignmentDetailByUserId($id,$userId,$assignId,$assignDate,$startTime,$totalHours){
        $sql = "SELECT * FROM " ;
        $sql .= JobCentreTaskTable::C_TABLE . " D " ;
        $sql .= "LEFT JOIN " . JobAssignmentTable::C_TABLE . " H ON (H." . JobAssignmentTable::C_ID . " = D." . JobCentreTaskTable::C_ID .") ";
        $sql .= " WHERE H." . JobAssignmentTable::C_JOB_CENTRE_ID . " = " . $id;
        $sql .= " AND D." . JobCentreTaskTable::C_AGENT . " = " . $userId;
        $sql .= " AND H." . JobCentreTaskTable::C_ID . " = " . $assignId;
        $sql .= " AND D." . JobCentreTaskTable::C_DATE . " = '" . $assignDate . "'";
        $sql .= " AND D." . JobCentreTaskTable::C_TIME . " = '" . $startTime . "'";
        $sql .= " AND D." . JobCentreTaskTable::C_HOURS . " = '" . $totalHours . "'";
        
        $rows = $this->db->getTable($sql);

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

    function getJobPlanTaskDetail($detalId,$jobId){
        $sql = " SELECT TOP 1 *  FROM " . JobCentreTaskTable::C_TABLE . 
                " WHERE " . JobCentreTaskTable::C_DETAIL_ID . " = " . $detalId.
                " AND ".JobCentreTaskTable::C_ID." = ".$jobId." ";
        
        $rows = $this->db->getTable($sql);

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



    function deleteDetail(){
        $sql = " DELETE FROM " . JobCentreTaskTable::C_TABLE . " " .
                " WHERE " . JobCentreTaskTable::C_ID . " = 0";
        
        $this->db->executeSql($sql);
    }


    function updateRecordByDetailId($update='',$detailId){
        $sql = " UPDATE " . JobCentreTaskTable::C_TABLE . " " .
                " SET " . $update .
                " WHERE " . JobCentreTaskTable::C_DETAIL_ID . " = " . $detailId;
        
        $this->db->executeSql($sql);
    }

    function getJobAssignmentListMobile($filter='',$orderby=''){
        $sql = "SELECT C." . JobCentreTable::C_JOB_NUMBER . ", C." . JobCentreTable::C_JOB_NAME . ", C." . JobCentreTable::C_JOB_TYPE . ", C." . JobCentreTable::C_PRODUCT_TYPE . ", C." . JobCentreTable::C_COY_ID . " AS COYID, C." . JobCentreTable::C_CUST_ID . ", D.* ";
        $sql .= "FROM " . JobCentreTaskTable::C_TABLE . " D ";
        $sql .= "LEFT JOIN " . JobAssignmentTable::C_TABLE . " H ON (H." . JobAssignmentTable::C_ID . " = D." . JobCentreTaskTable::C_ID .") ";
        $sql .= "LEFT JOIN " . JobCentreTable::C_TABLE . " C ON (H." . JobAssignmentTable::C_JOB_CENTRE_ID . " = C." . JobCentreTable::C_ID .") ";

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

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

    function getRecordByDetailId($detalId){
        $sql = "SELECT * FROM " .$this->tbl ;
        $sql .= " WHERE " . JobCentreTaskTable::C_DETAIL_ID . " = " . $detalId;
        
        $rows = $this->db->getTable($sql);

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

    function getTotalSummary($job_center_id,$status ='',$agent_status = ''){
        $sql = "SELECT ".JobCentreTaskTable::C_DETAIL_ID." FROM " .$this->tbl ;
        $sql .= " WHERE " . JobCentreTaskTable::C_ID . " = " . $job_center_id;

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

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

    /** unused */
    function getJobCentreDetail($filter='',$orderby=''){
        $sql = "SELECT C.*, D.* ";
        $sql .= "FROM " . JobCentreTaskTable::C_TABLE . " D ";
        $sql .= "LEFT JOIN " . JobAssignmentTable::C_TABLE . " H ON (H." . JobAssignmentTable::C_ID . " = D." . JobCentreTaskTable::C_ID .") ";
        $sql .= "LEFT JOIN " . JobCentreTable::C_TABLE . " C ON (H." . JobAssignmentTable::C_JOB_CENTRE_ID . " = C." . JobCentreTable::C_ID .") ";

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

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

    function updateCompleteTask($plan_id,$id, $modifyBy, $modifyDate) {
        $sql = " UPDATE " . JobCentreTaskTable::C_TABLE . " " .
                " SET " . JobCentreTaskTable::C_STATUS . " = 1," .
                " " . JobCentreTaskTable::C_MODIFY_BY . " = " . $modifyBy ."," .
                " " . JobCentreTaskTable::C_MODIFY_DATE . " = '" . $modifyDate . "' ," .
                " " . JobCentreTaskTable::C_CLOSE_USER_ID . " = " . $modifyBy . " ," .
                " " . JobCentreTaskTable::C_CLOSE_DATE . " = '" . $modifyDate . "'" .
                " WHERE " . JobCentreTaskTable::C_DETAIL_ID . " = " . $id.
                " AND " . JobCentreTaskTable::C_ID . " = " . $plan_id;
        
        Log::write($sql);
        $this->db->executeSql($sql);
    }

    function getAgentSchedule($month,$year){
        $sql = "SELECT a.*,u.".UserTable::C_FULL_NAME.",u.".UserTable::C_NAME.",u.".UserTable::C_ID." FROM " ;
        $sql .= JobCentreTaskTable::C_TABLE ." a ";
        $sql .= " left outer join ".UserTable::C_TABLE ." u ";
        $sql .= " on ( u.".UserTable::C_ID ." = a.".JobCentreTaskTable::C_AGENT ." ) ";
        $sql .= " WHERE MONTH(a." . JobCentreTaskTable::C_DATE . ") = " . $month ;
        $sql .= " AND YEAR(a." . JobCentreTaskTable::C_DATE . ") = " . $year ;

        $sql .= " ORDER BY a.".    JobCentreTaskTable::C_DATE." ASC";
        $rows = $this->db->getTable($sql);

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