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

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

    function __destruct() {
    }

    function getJobAssignmentList($userId, $fromdate,$todate,$jobnumber,$jobtype){
        $sql = "SELECT C." . JobCentreTable::C_JOB_NUMBER . ", C." . JobCentreTable::C_JOB_NAME . ", C." . JobCentreTable::C_JOB_TYPE . ", C." . JobCentreTable::C_PRODUCT_TYPE . ", D.* ,E.".JobCentreListEquipmentTable::C_PRODUCT." ";
        $sql .= "FROM " . JobAssignmentDetailTable::C_TABLE . " D ";
        $sql .= "LEFT JOIN " . JobAssignmentTable::C_TABLE . " H ON (H." . JobAssignmentTable::C_ID . " = D." . JobAssignmentDetailTable::C_ID .") ";
        $sql .= "LEFT JOIN " . JobCentreTable::C_TABLE . " C ON (H." . JobAssignmentTable::C_JOB_CENTRE_ID . " = C." . JobCentreTable::C_ID .") ";
        $sql .= "LEFT OUTER JOIN " . JobCentreListEquipmentTable::C_TABLE . " E ON (E." . JobCentreListEquipmentTable::C_EQUIPMENT_ID . " = D." . JobAssignmentDetailTable::C_EQUIPMENT .") ";

        if($jobnumber != ''){
            $sql .= "WHERE C." . JobCentreTable::C_JOB_NUMBER . " LIKE '%" . $jobnumber . "%' ";
            $sql .= "AND cast(D." . JobAssignmentDetailTable::C_DATE . " as date) BETWEEN '" . $fromdate . "' AND '" . $todate . "' ";
        } else {
            $sql .= "WHERE cast(D." . JobAssignmentDetailTable::C_DATE . " as date) BETWEEN '" . $fromdate . "' AND '" . $todate . "' ";
        }

        if($jobtype != 0){
            $sql .= "AND C." . JobCentreTable::C_JOB_TYPE . " = " . $jobtype . " ";
        }

        $sql .= "AND (D." . JobAssignmentDetailTable::C_AGENT . " = " . $userId . " ";
        $sql .= "OR H." . JobAssignmentTable::C_MODIFY_BY . " = " . $userId . ") ";
        $sql .= "ORDER BY C." . JobCentreTable::C_JOB_NUMBER ." ASC";

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

    function getJobAssignmentDetail($id){
        $sql = "SELECT * FROM " ;
        $sql .= JobAssignmentDetailTable::C_TABLE . " WHERE " . JobAssignmentDetailTable::C_ID . " = " . $id;
        $sql .= "ORDER BY " . JobAssignmentDetailTable::C_DATE ." 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 .= JobAssignmentDetailTable::C_TABLE . " D " ;
        $sql .= "LEFT JOIN " . JobAssignmentTable::C_TABLE . " H ON (H." . JobAssignmentTable::C_ID . " = D." . JobAssignmentDetailTable::C_ID .") ";
        $sql .= " WHERE H." . JobAssignmentTable::C_JOB_CENTRE_ID . " = " . $id;
        $sql .= " AND D." . JobAssignmentDetailTable::C_AGENT . " = " . $userId;
        $sql .= " AND H." . JobAssignmentDetailTable::C_ID . " = " . $assignId;
        $sql .= " AND D." . JobAssignmentDetailTable::C_DATE . " = '" . $assignDate . "'";
        $sql .= " AND D." . JobAssignmentDetailTable::C_TIME . " = '" . $startTime . "'";
        $sql .= " AND D." . JobAssignmentDetailTable::C_HOURS . " = '" . $totalHours . "'";
        
        $rows = $this->db->getTable($sql);

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

    function showJobAssignmentDetail($id){
        $sql = "SELECT D.* FROM " ;
        $sql .= JobAssignmentDetailTable::C_TABLE . " D " ;
        $sql .= "LEFT JOIN " . JobAssignmentTable::C_TABLE . " H ON (H." . JobAssignmentTable::C_ID . " = D." . JobAssignmentDetailTable::C_ID .") ";
        $sql .= "LEFT JOIN " . JobCentreTable::C_TABLE . " C ON (H." . JobAssignmentTable::C_JOB_CENTRE_ID . " = C." . JobCentreTable::C_ID .") ";
        $sql .= "WHERE H." . JobAssignmentTable::C_JOB_CENTRE_ID . " = " . $id;

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

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

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

        if($agent!=0)
            $sql .= " AND a." . JobAssignmentDetailTable::C_AGENT . " = " . $agent;
        
        $sql .= " ORDER BY a.".    JobAssignmentDetailTable::C_DATE." ASC";
        $rows = $this->db->getTable($sql);

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

    function getTaskDescription($detalId,$jobId){
        $sql = " SELECT " . JobCentreTaskTable::C_TASK." 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 getHeaderbyDetailId($detalId){
        $sql = " SELECT " . JobAssignmentDetailTable::C_ID . "  FROM " . JobAssignmentDetailTable::C_TABLE . 
                " WHERE " . JobAssignmentDetailTable::C_DETAIL_ID . " = " . $detalId;
        
        $rows = $this->db->getTable($sql);

        return $rows[0][JobAssignmentDetailTable::C_ID];
    }

     function updateStatusJobAssignment($actId,$assignId){
        $actCls = new ActivityHeaderClass($this->db) ;

        $status = $actCls->getRecord($actId)[ActivityHeaderTable::C_PROJECT_STATUS];

        $sql = " UPDATE " . JobAssignmentDetailTable::C_TABLE . " " .
                "SET " . JobAssignmentDetailTable::C_STATUS . " = '" . $status . "'" .
                " WHERE " . JobAssignmentDetailTable::C_DETAIL_ID . " = " . $assignId;

        $this->db->executeSql($sql);
    }

    function deleteRecordbyStatus($id){
        $sql = " DELETE FROM " . JobAssignmentDetailTable::C_TABLE . " " .
                " WHERE " . JobAssignmentDetailTable::C_STATUS . " = 0" .
                " AND " . JobAssignmentDetailTable::C_AGENT_STATUS . " = 0" .
                " AND " . JobAssignmentDetailTable::C_ID . " = " . $id ;
        
        $this->db->executeSql($sql);
    }
    function deleteRecordbyJobCentre($id){
        $sql = " DELETE FROM " . JobAssignmentDetailTable::C_TABLE . " " .
                " WHERE " . JobAssignmentDetailTable::C_ID . " = ".$id ;
        
        $this->db->executeSql($sql);
    }
    function getAttachmentByDetailId($detalId){
        $sql = " SELECT " . JobAssignmentDetailTable::C_DETAIL_ID . "," . JobAssignmentDetailTable::C_DOC_NAME . ", " . JobAssignmentDetailTable::C_DESC . "  FROM " . JobAssignmentDetailTable::C_TABLE . 
                " WHERE " . JobAssignmentDetailTable::C_DETAIL_ID . " = " . $detalId;
        
        $rows = $this->db->getTable($sql);
        return $rows;
    }

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

    function deleteAttachmentByDetailId($detalId){
        $sql = " UPDATE " . JobAssignmentDetailTable::C_TABLE . " " .
                "SET " . JobAssignmentDetailTable::C_DOC_NAME . " = '', " . JobAssignmentDetailTable::C_DESC . " = '' " .
                " WHERE " . JobAssignmentDetailTable::C_DETAIL_ID . " = " . $detalId;
        
        $this->db->executeSql($sql);
    }

    // function updateAttachmentByDetailId($newName,$docfile,$id){
    //     $sql = " UPDATE " . JobAssignmentDetailTable::C_TABLE . " " .
    //             "SET " . JobAssignmentDetailTable::C_DOC_NAME . " = '" . $newName . "', " . JobAssignmentDetailTable::C_DESC . " = '" . $docfile . "' " .
    //             " WHERE " . JobAssignmentDetailTable::C_DETAIL_ID . " = " . $id;
    //     // Log::write(print_r($sql,true));
    //     $this->db->executeSql($sql);
    // }

    // function insertAttachmentByDetailId($newName,$docfile,$id,$header){
    //     try {
    //         $sql = "SET IDENTITY_INSERT " . JobAssignmentDetailTable::C_TABLE . " ON
                   
    //                 INSERT INTO " . JobAssignmentDetailTable::C_TABLE . 
    //                     "(" . JobAssignmentDetailTable::C_ID . ", " . 
    //                             JobAssignmentDetailTable::C_DETAIL_ID . ", " . 
    //                             JobAssignmentDetailTable::C_DOC_NAME . ", " . 
    //                             JobAssignmentDetailTable::C_DESC . ")";

    //         $sql .= "VALUES (" . $header . ", " . $id . ", '" . $newName . "', '" . $docfile . "')" ;
    //         $sql .= "SET IDENTITY_INSERT " . JobAssignmentDetailTable::C_TABLE . " OFF" ;
    //         $this->db->executeSql($sql);
    //     } catch (Exception $e){
    //         Log::write("[JobAssignAttachment] " . $e->getMessage());
    //     }
    // }    
    
    function updateRecordByDetailId($update='',$detailId){
        $sql = " UPDATE " . JobAssignmentDetailTable::C_TABLE . " " .
                " SET " . $update .
                " WHERE " . JobAssignmentDetailTable::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 " . JobAssignmentDetailTable::C_TABLE . " D ";
        $sql .= "LEFT JOIN " . JobAssignmentTable::C_TABLE . " H ON (H." . JobAssignmentTable::C_ID . " = D." . JobAssignmentDetailTable::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 " . JobAssignmentDetailTable::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 ".JobAssignmentDetailTable::C_DETAIL_ID." FROM " .$this->tbl ;
        $sql .= " WHERE " . JobAssignmentDetailTable::C_ID . " = " . $job_center_id;

        // if($status !=''){
        //     $sql .= " AND " . JobAssignmentDetailTable::C_STATUS . " = " . $status;
        // }

        // if($agent_status !=''){
        //     $sql .= " AND " . JobAssignmentDetailTable::C_AGENT_STATUS . " = " . $agent_status;
        // }

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

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

    function getJobCentreDetail($filter='',$orderby=''){
        $sql = "SELECT C.*, D.* ";
        $sql .= "FROM " . JobAssignmentDetailTable::C_TABLE . " D ";
        $sql .= "LEFT JOIN " . JobAssignmentTable::C_TABLE . " H ON (H." . JobAssignmentTable::C_ID . " = D." . JobAssignmentDetailTable::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;
    }

    
    
}
?>