<?php

class JOBS {

    private $db;

    function __construct($DB_con) {
        $this->db = $DB_con;
    } 
      
    
public function checkPermissions(){
    
    $Page = explode("/", $_SERVER['PHP_SELF']);
    $CurrentPage = end($Page); 
    
    $AdminPages = array('agents.php' ,'agent_controls.php' ,'ajax.php' ,'assign_client.php' ,'assign_client22.php' ,'assign_client_marketing.php' ,'assign_client_step2.php' ,'assign_client_step2_.php' ,'assign_client_step2_without_datatable.php' ,'assign_client_step3.php' ,'assign_client_without_datatable.php' ,'blank.php' ,'categories.php' ,'category_controls.php' ,'change_password.php' ,'clients.php' ,'client_controls.php' ,'client_import.php' ,'client_view.php' ,'dashboard.php' ,'events.php' ,'events_marketing.php' ,'event_controls.php' ,'event_graph.php' ,'event_graph_dates.php' ,'event_tasks.php' , 'event_view.php', 'logs.php' ,'my_tasks.php' ,'notifications.php' ,'send_sms.php' ,'send_sms_step3.php' ,'task_calender.php' ,'_clients.php');
    
    $AgentPages = array('assign_client.php' ,'assign_client_step2.php' ,'assign_client_step2_without_datatable.php' ,'assign_client_step3.php' ,'assign_client_without_datatable.php', 'categories.php' ,'category_controls.php' ,'change_password.php' , 'clients.php' ,'client_controls.php'  ,'client_view.php' ,'dashboard.php' ,'events.php' ,'event_controls.php' ,'event_graph.php' ,'event_graph_dates.php' ,'event_tasks.php' ,'event_view.php' ,'my_tasks.php' ,'send_sms.php' ,'send_sms_step3.php' ,'task_calender.php', 'campaign_controls.php' );
    

    $MarketingPages = array('assign_client_marketing.php', 'events_marketing.php', 'event_graph.php' ,'event_graph_dates.php' ,'event_tasks.php' ,'send_sms.php' ,'send_sms_step3.php' ,'task_calender.php');

    $InterneePages = array('clients.php',  'client_controls.php');
    
    if($_SESSION['user_role_session'] == 'Admin'){

    }
    else if($_SESSION['user_role_session'] == 'Agent'){
        
        if( !in_array( $CurrentPage , $AgentPages) ){
                echo "<script>window.location='dashboard.php'</script>";
                die();
        }
        
    }
    else if($_SESSION['user_role_session'] == 'Internee'){
        if( !in_array( $CurrentPage , $InterneePages) ){
                 echo "<script>window.location='clients.php'</script>";
                die();
        }
    }
    else if($_SESSION['user_role_session'] == 'Marketing'){
        if( !in_array( $CurrentPage , $MarketingPages) ){
                echo "<script>window.location='events_marketing.php'</script>";
                die();
        }
    }
 }


public function getDashboardEvents(){
        try {
            
            $return = array();
            $stmt = $this->db->prepare("SELECT * FROM events where `status` = 'Active'  order by market_status desc limit 10" );
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                  $return[] = $Row;  
            }
            return $return;
            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
    
    
public function getSmsTemp($agent_id=''){
        try {
            
            $return = array(); 
            if(!empty($agent_id)){
                $qry = "SELECT * FROM sms_templete  where `agent_id` = ".$agent_id."  and `status` <> '0'";
            }else {
                $qry = "SELECT * FROM sms_templete  where `status` <> '0'";
            }
             
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
           }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
} 

public function getDeniedReasons() {
        try {
            
            $return = array(); 
            $qry = "SELECT * FROM client_denied_reasons";
            
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
            }
            return $return;
            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
   
    
   public function updateDeniedReasonsClient($assignId, $reason_id) {
        try {
               $return = array(); 
                $qry = "update clients_events_assigning  set `denied_reason` = '$reason_id' where `id` = ".$assignId;
                $stmt = $this->db->prepare($qry);
                $stmt->execute();

            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
    
    
public function getEventPendingTasks($agent_id, $event_id = ''){
        try {
            
            $return = array(); 
            if(!empty($event_id)){
                $qry = "SELECT * FROM event_tasks  where `agent_id` = ".$agent_id." and `event_id` = ".$event_id." and `status` = 'pending'";
            }else {
                $qry = "SELECT * FROM event_tasks  where `agent_id` = ".$agent_id." and `status` = 'pending' ";
            }
             
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
           }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }

} 



public function getEventAllTasks($agent_id, $event_id = ''){
        try {
            
            $return = array(); 
            if(!empty($event_id)){
                $qry = "SELECT * FROM event_tasks  where `agent_id` = ".$agent_id." and `event_id` = ".$event_id." and `status` <> 'deleted'";
            }else {
                $qry = "SELECT * FROM event_tasks  where `agent_id` = ".$agent_id." and `status` <> 'deleted' ";
            }
             
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
           }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }

} 

    
    public function deleteAgentTasks($task_id){
        try {
                    $return = array(); 
                    $qry = "delete from agent_tasks where `id` = ".$task_id;
                    $stmt = $this->db->prepare($qry);
                    $stmt->execute();
                   
                    return true;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }

        }

        
    
    public function updateAgentTasks($task_id, $status){
        try {
                    $return = array(); 
                      $qry = "update agent_tasks  set `status` = '$status' where `id` = ".$task_id;
                    $stmt = $this->db->prepare($qry);
                    $stmt->execute();
                   
                return true;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }

        }

 public function addAgentTask($title, $description, $start_date, $end_date, $exhibition, $agent_id){
        try {
            
            $return = array(); 
            $i=0;

            $stmt = $this->db->prepare("INSERT INTO agent_tasks(
                                title, start_date, end_date, exhibition, description, agent_id
                            ) VALUES(
                                :title, :start_date, :end_date, :exhibition, :description, :agent_id
                            )");
            
            $stmt->bindparam(":title", $title);
            $stmt->bindparam(":start_date", $start_date );
            $stmt->bindparam(":end_date", $end_date);
            $stmt->bindparam(":exhibition", $exhibition);
            $stmt->bindparam(":description", $description);
            $stmt->bindparam(":agent_id", $agent_id);
            
            $stmt->execute();
            
        return true;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


  public function getAgentPendingTasks($id=''){
        try {
            
            $return = array();
            
            $stmt = $this->db->prepare("SELECT * FROM agent_tasks where status < 2 order by `status` " );
            $stmt->execute();  

            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                   $return[] = $Row;
             }
             
            return $return;
            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
    


    public function getAgentTasks($id='', $home=""){
        try { 
            
            $return = array();
            if(!empty($id)){
                if(!empty($home)){
                    $qry = "SELECT * FROM agent_tasks WHERE `status` <> '2' and agent_id=:uId order by `status`, start_date limit 10   ";
                }else {
                    $qry = "SELECT * FROM agent_tasks WHERE agent_id=:uId order by `status`, start_date  ";
                }
                
                 
                 
                $stmt = $this->db->prepare($qry);
                $stmt->execute(array(':uId' => $id)); 
                
            }else {
                if(!empty($home)){
                    $qry = "SELECT * FROM agent_tasks `status` <> '2'  order by `status`";
                }  else {
                    $qry = "SELECT * FROM agent_tasks order by `status`";
                }
                
                $stmt = $this->db->prepare($qry);
                $stmt->execute();  
            }
            
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                   $return[] = $Row;
            }
             
            return $return;
            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
    
    public function AssignClientsToEvent($event_id, $client_id, $agent_id) {
        try {
              $qry = "select * from clients_events_assigning where event_id = $event_id and client_id = $client_id";    
            $stmt2 = $this->db->prepare($qry);
            $stmt2->execute();  
            if($stmt2->rowCount()< 1){
                $stmt = $this->db->prepare("INSERT INTO clients_events_assigning( event_id, client_id,  agent_id) 
                                VALUES( :event_id,  :client_id,  :agent_id )");

                $stmt->bindparam(":event_id", $event_id);
                $stmt->bindparam(":client_id", $client_id);
                $stmt->bindparam(":agent_id", $agent_id);
                $stmt->execute();    
                return 1;
                
            }else { return 0;}
            

            
            
            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

    public function getAssignClientsByEveIds($event_ids, $agent_id){
        try {
             if($_SESSION['user_role_session']=="Admin"){
         
            $nSql = "SELECT * FROM `clients_events_assigning` WHERE `event_id` in( ".$event_ids.") order by `status_order` DESC ";
            }else {
            $nSql = "SELECT * FROM `clients_events_assigning` WHERE `event_id` in( ".$event_ids.") and `agent_id` = ".$agent_id .' order by `status_order` DESC';
        }
            
            $stmtA = $this->db->prepare($nSql);
            $stmtA->execute();  
            $i=0;
            $return = array(); 
            while($RowA = $stmtA->fetch(PDO::FETCH_ASSOC)){
                
                $qry = "SELECT * FROM clients  where `client_id` = ".$RowA['client_id'];
                $stmt = $this->db->prepare($qry);
                $stmt->execute();
                while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                    //$return[]= $Row;
                      $Row['status_order'] = $RowA['status_order'];
                      $return[$i] = $Row;
                      $return[$i]['assign_id'] = $RowA['id'];
                      $return[$i]['pipeline'] = $RowA['pipeline'];
                      $stmt2 = $this->db->prepare("SELECT * FROM client_persons where client_id = ".$Row['client_id']);
                      $stmt2->execute();
                      while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                           $return[$i]['persons'][] = $Row2;
                      }
                    $i++;
               }
            }
              
        return $return;

    } catch (PDOException $e) {
        echo $e->getMessage();
    } 
}

    public function getAssignClients($event_id, $agent_id, $status = false){
        try {
            
        if($_SESSION['user_role_session']=="Admin" || $_SESSION['user_role_session']=="Marketing"){
            $nSql = "SELECT * FROM `clients_events_assigning` WHERE `event_id` = ".$event_id." order by `status_order` DESC ,pipeline DESC ";
        } elseif($status !=false) {
         $nSql = "SELECT * FROM `clients_events_assigning` WHERE `event_id` = ".$event_id." and `agent_id` = ".$agent_id ." and $status  order by `status_order` DESC ,pipeline  DESC";
        }
        else {
         $nSql = "SELECT * FROM `clients_events_assigning` WHERE `event_id` = ".$event_id." and `agent_id` = ".$agent_id .'  order by `status_order` DESC ,pipeline  DESC';
        }
        
        
            //$nSql.=" limit 10";
            
            $stmtA = $this->db->prepare($nSql);
            $stmtA->execute();  
            $i=0;
            $return = array(); 
            while($RowA = $stmtA->fetch(PDO::FETCH_ASSOC)){

                $qry = "SELECT * FROM clients  where `client_id` = ".$RowA['client_id'];
                $stmt = $this->db->prepare($qry);
                $stmt->execute();
                while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                    //$return[]= $Row;
                      $Row['status_order'] = $RowA['status_order'];
                      $return[$i] = $Row;
                      $return[$i]['assign_id'] = $RowA['id'];
                      $return[$i]['pipeline'] = $RowA['pipeline'];
                      $return[$i]['denied_reason'] = $RowA['denied_reason'];
                      
                      $stmt2 = $this->db->prepare("SELECT * FROM client_persons where client_id = ".$Row['client_id']);
                      $stmt2->execute();
                      while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                           $return[$i]['persons'][] = $Row2;
                      }
                    $i++;
               }
            }
              
        return $return;

        } catch (PDOException $e) {
            echo $e->getMessage();
    } 
}


public function getSMSClient(){
        try {
            
            $return = array(); 
            $qry = "SELECT * FROM sms_log where `status` = 0";
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
            }
           
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


public function updateSMSClient($id){
        try {
            
            $return = array(); 
            $qry = "update sms_log set `status` = 1  where `id` =".$id;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            
            return $return;
        
    } catch (PDOException $e) {
        
        echo $e->getMessage();
        
    }
}


    public function getClientsAssignDetails($assign_id){
        try {
            
            $return = array(); 
            $qry = "SELECT * FROM clients_events_assign_details  where `assign_id` = ".$assign_id;
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return = $Row;
                $i++;
           }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }

}


    public function addClientsAssignDetails($assign_id, $column, $value){
        try{
            
            $value = date("Y-m-d H:i:s");
            $return = array(); 
            $qry = "SELECT * FROM clients_events_assign_details  where `assign_id` = ".$assign_id;
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            $count = $stmt->rowCount();
            
            if($count>0){
               $sql1 = 'update clients_events_assign_details set  `'.$column.'` = "'.$value.'" where assign_id = '.$assign_id; 
            }else {
                $sql1 = 'insert into clients_events_assign_details  ( `assign_id`, `'.$column.'` )values( '.$assign_id .', "'.$value.'" )';
            }
           
                $stmt = $this->db->prepare($sql1);
                $stmt->execute();
             
            return $value;
        } catch (PDOException $e) {
        
            echo $e->getMessage();
        }
}


    public function updateClientsAssign($assign_id, $status, $status_order, $pipline_status = ''){
    try {
        if(!empty($pipline_status)){
             $sql1 = 'update clients_events_assigning  set  `status` = "'.$status.'" ,  `status_order` = "'.$status_order.'",  `pipeline` =  "'.$pipline_status.'" where `id` = "'.$assign_id.'"';
        }else{
            $sql1 = 'update clients_events_assigning  set  `status` = "'.$status.'" ,  `status_order` = "'.$status_order.'" where `id` = "'.$assign_id.'"';
        }
            $stmt = $this->db->prepare($sql1);
            $stmt->execute();

            return true;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

  
    public function getApprovedClientsName($event_id){
        try {
        
            $return=array(); 
            $return['client_accpt'] =  0;
            $return['client_hot']   =  0;
        
          $nSql = "SELECT  `clients`.`company_name`, `clients`.`client_id`, clients_events_assigning.`event_id`, clients_events_assigning.`client_id`, 
                clients_events_assigning.`agent_id`, clients_events_assigning.`status_order`, clients_events_assigning.`pipeline`, 
                clients_events_assigning.`status` FROM  `clients_events_assigning` , `clients` WHERE `clients`.`client_id` = clients_events_assigning.
                `client_id` AND `clients_events_assigning`.`event_id` = ".$event_id." AND 
                ( `clients_events_assigning`.`status_order` = 1 OR  `clients_events_assigning`.`pipeline` =  80) order by `clients_events_assigning`.`status_order`";

                $stmtA = $this->db->prepare($nSql);
                $stmtA->execute();  
                $i=0;
                
                while($RowA = $stmtA->fetch(PDO::FETCH_ASSOC)){
                    $return['client'][]= $RowA;
                    
                    if($RowA['status_order']==1){
                        $return['client_accpt']+=1 ; 
                    }else if($RowA['pipeline']==80){
                        $return['client_hot']+=1 ; 
                    }
                    
                    $i++;
                }
                return $return;

                } catch (PDOException $e) {
              echo $e->getMessage();
    }

}



    public function getApprovedAssignClients($event_id, $agent_id){
        try {

$taskList = $this->getAssignStep3Tasks();
//echo '<pre>';print_r($taskList);echo '</pre>';

 if($_SESSION['user_role_session']=="Admin" || $_SESSION['user_role_session']=="Marketing"){
        $nSql = "SELECT * FROM `clients_events_assigning` WHERE `event_id` = ".$event_id." and `status_order` = 1";
 }else {
        $nSql = "SELECT * FROM `clients_events_assigning` WHERE `event_id` = ".$event_id." and `agent_id` = ".$agent_id .'  and `status_order` = 1';
 }



$stmtA = $this->db->prepare($nSql);
$stmtA->execute();  
$i=0;
$return = array(); 
while($RowA = $stmtA->fetch(PDO::FETCH_ASSOC)){

    $qry = "SELECT * FROM clients  where `client_id` = ".$RowA['client_id'];
    $stmt = $this->db->prepare($qry);
    $stmt->execute();
    while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
    //$return[]= $Row;
      $Row['status_order'] = $RowA['status_order'];
      $return[$i] = $Row;
      $return[$i]['assign_id'] = $RowA['id'];
      $return[$i]['pipeline'] = $RowA['pipeline'];

      foreach($taskList as $tl ){
            $sss = "select * from events_assign_step3_tasks_details where event_id = ".$event_id .' and agent_id = '.$agent_id.' and client_id = '.$Row['client_id'].' and task_id ='.$tl['id'];
            '<br>';
          
        $stmt2 = $this->db->prepare($sss);
        $stmt2->execute();
        if($stmt2->rowCount()>0){
            while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                $return[$i]['tasks'][] = $Row2;
            }
        }else { 
                $return[$i]['tasks'][] = array('task_id'=> $tl['id'], 'task'=> $tl['task']);
        }
      
      }
        $i++;
   }
}

        return $return;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }

}



    public function getAssignStep3Tasks(){
        try {
            
            $return = array(); 
            $qry = "SELECT * FROM events_assign_step3_tasks";
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
            }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
} 

    public function addAssignStep3TaskDate($event_id, $task_id, $last_date, $agent_id){
        try {
            
            $return = array(); 
            $i=0;

            $stmt = $this->db->prepare("INSERT INTO events_assign_step3_tasks_date(
                                task_id, event_id, agent_id, last_date
                            ) VALUES(
                                :task_id, :event_id, :agent_id, :last_date
                            )");
            
            $stmt->bindparam(":task_id", $task_id);
            $stmt->bindparam(":event_id", $event_id);
            $stmt->bindparam(":agent_id", $agent_id);
            $stmt->bindparam(":last_date", $last_date);

            $stmt->execute();
            
        return true;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}



public function getAssignStep3TasksDates($event_id){
        try {
            
            $return = array();
            $qry = "SELECT * FROM events_assign_step3_tasks";
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                    $return[$i]['task'] = $Row['task'];
                    $qry2 = "SELECT * FROM events_assign_step3_tasks_date where task_id = ".$Row['id'].' and event_id = '.$event_id.' order by id desc ';
                    $stmt2 = $this->db->prepare($qry2);
                    $stmt2->execute();
                    if($stmt2->rowCount()>0 ){
                        while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                            $return[$i]['last_date'][] = $Row2['last_date'];
                        }
                    }else {
                        $return[$i]['last_date'] = '';
                    }
                $i++;
            }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

 
    public function addEventStep3Task($event_id, $client_id, $agent_id,  $task_id, $task_status){
        
        try {
            $return = array(); 
            $i=0;
                
            $qry1 = "SELECT * FROM events_assign_step3_tasks_details where event_id = ".$event_id.' and client_id = '.$client_id.' and agent_id = '.$agent_id.' and task_id = '.$task_id;
            $stmt1 = $this->db->prepare($qry1);
            $stmt1->execute();
            
            if($stmt1->rowCount()<1){
                
                $stmt = $this->db->prepare("INSERT INTO events_assign_step3_tasks_details(
                                event_id, client_id, agent_id, task_id, task_status
                                ) 
                                VALUES(
                                    :event_id, :client_id, :agent_id, :task_id, :task_status
                                )");

                $stmt->bindparam(":event_id", $event_id);
                $stmt->bindparam(":client_id", $client_id);
                $stmt->bindparam(":agent_id", $agent_id);
                $stmt->bindparam(":task_id", $task_id);
                $stmt->bindparam(":task_status", $task_status);
                $stmt->execute();       
                return true;
            }else {
                return false;
            }
        
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

public function removeEventStep3Task($event_id, $client_id, $agent_id,  $task_id){
        
        try {
            $return = array(); 
            $i=0;
            
            $qry1 = "SELECT * FROM events_assign_step3_tasks_details where event_id = ".$event_id.' and client_id = '.$client_id.' and agent_id = '.$agent_id.' and task_id = '.$task_id;
            $stmt1 = $this->db->prepare($qry1);
            $stmt1->execute();
            
            if($stmt1->rowCount()>0){
            
                $stmt = $this->db->prepare("delete from  events_assign_step3_tasks_details
                               where  event_id = :event_id and 
                                      client_id = :client_id and 
                                      agent_id = :agent_id and
                                      task_id = :task_id
                            ");

            $stmt->bindparam(":event_id", $event_id);
            $stmt->bindparam(":client_id", $client_id);
            $stmt->bindparam(":agent_id", $agent_id);
            $stmt->bindparam(":task_id", $task_id);
            $stmt->execute();
            
            return true;
            }else {
                return false;
            }
            
        
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}



public function getStep3TasksPercent( $event_id, $client_id, $agent_id){
        try {
            $tot = count($this->getAssignStep3Tasks());
            $return = ''; 
            $qry = "SELECT * FROM events_assign_step3_tasks_details where event_id = ".$event_id.' and client_id = '.$client_id.' and agent_id = '.$agent_id;
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            $cc = $stmt->rowCount();
            $return = floor(($cc/$tot)*100);
            
            
            
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
} 

public function getEventTasks($assign_id='', $event_id='', $agent_id='', $home=""){
        try {
            $agent = '';
             
            if(!empty($agent_id)){
                $agent = " and agent_id = $agent_id ";
            }
            $assign = "";
            if(!empty($assign_id)){
                $assign = " and assign_id = $assign_id ";
            }
            $event = "";
            if(!empty($event_id)){
                $event = " and event_id = $event_id ";
            }
            
            $return = array();
            if(!empty($home)){
                $qry = "SELECT * FROM event_tasks  where status ='pending'  ".$assign.$event.$agent.' order by `status`, start_date limit 10'; //
            }else{
                $qry = "SELECT * FROM event_tasks  where 1=1  ".$assign.$event.$agent.' order by `status`, start_date '; //status ='pending'
            }
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
           }
           
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
} 


    public function updateEventTasks($task_id, $status){
        try {
                    $return = array(); 
                    $qry = "update event_tasks  set `status` = '$status' where `task_id` = ".$task_id;
                    $stmt = $this->db->prepare($qry);
                    $stmt->execute();

                    return true;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }

        }


    public function getEventCompletedTasks($assign_id, $event_id){
        try {
            
            $return = array(); 
              $qry = "SELECT * FROM event_tasks  where `assign_id` = ".$assign_id." and `event_id` = ".$event_id.' and `status` = "completed"';
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
           }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }

} 


    public function addEventTask($assign_id, $event_id, $client_id, $task_title, $start_date, $end_date, $person_id, $agent_id){
        try {
            
            $return = array(); 
            $i=0;

            $stmt = $this->db->prepare("INSERT INTO event_tasks(
                                assign_id, event_id, client_id, title, start_date, end_date, person_id, agent_id
                            ) 
		            VALUES(
                                :assign_id, :event_id, :client_id, :title, :start_date, :end_date, :person_id, :agent_id
                            )");
            
            $stmt->bindparam(":assign_id", $assign_id);
            $stmt->bindparam(":event_id", $event_id);
            $stmt->bindparam(":client_id", $client_id);
            $stmt->bindparam(":title", $task_title);
            $stmt->bindparam(":start_date", $start_date);
            $stmt->bindparam(":end_date", $end_date);
            $stmt->bindparam(":person_id", $person_id);
            $stmt->bindparam(":agent_id", $agent_id);
            
            $stmt->execute();
            
        return true;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

    
    public function removeTask($task_id){
        try {
               echo $sql1 = 'update event_tasks  set  `status` = "deleted" where `task_id` = "'.$task_id.'"';
                $stmt = $this->db->prepare($sql1);
                $stmt->execute();

            return true;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


    public function completeTask($task_id){
        try {
                $sql1 = 'update event_tasks  set  `status` = "completed" where `task_id` = "'.$task_id.'"';
                $stmt = $this->db->prepare($sql1);
                $stmt->execute();

            return true;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

  
    public function addEventComment($assign_id, $event_id, $client_id, $comments, $agent_id){
        try {
            
            $return = array(); 
            $i=0;

            $stmt = $this->db->prepare("INSERT INTO event_comments(
                                assign_id, event_id, client_id, comments, agent_id
                            ) 
		            VALUES(
                                :assign_id, :event_id, :client_id, :comments, :agent_id
                            )");
            
            $stmt->bindparam(":assign_id", $assign_id);
            $stmt->bindparam(":event_id", $event_id);
            $stmt->bindparam(":client_id", $client_id);
            $stmt->bindparam(":comments", $comments);
            $stmt->bindparam(":agent_id", $agent_id);
            
            $stmt->execute();
            
        return true;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


    public function getEventComments($assign_id, $event_id){
        try {
            
            $return = array(); 
            $qry = "SELECT * FROM event_comments  where `assign_id` = ".$assign_id." and `event_id` = ".$event_id.' order by comment_id desc';
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
           }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }

} 
    public function getNotifications(){
        try {
            
            $Date = date('Y-m-d H:i:s');
            $beforeDate =  date('Y-m-d ', strtotime($Date. ' - 2 days'))." 00:00:00";
            
            $result= array();
            $index = 0;
            //$qry = "SELECT * FROM categories  where `ts` >= '".$beforeDate."' order by ts desc";
            
            $qry = "SELECT * FROM categories   order by ts desc limit 100";
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                if($Row['cat_add_by']==100001){
                    $agent = $_SESSION['user_name_session'];
                }else {
                    $agent = $this->getAgentName($Row['cat_add_by']);
                }
                     
                //print_r($agent);
                $result[$index]['title'] = "<h5 class='media-heading'>".$Row['cat_title']." </h5> New category added by ".$agent;
                $result[$index]['date'] = $this->change_date_hours($Row['ts']);   
                $index++;
            }

            //$qry = "SELECT * FROM events  where `ts` >= '".$beforeDate."'  order by ts desc";
            
            $qry = "SELECT * FROM events  order by ts desc limit 100";
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                    if($Row['add_by']==100001){
                        $agent = $_SESSION['user_name_session'];
                    }else {
                        $agent = $this->getAgentName($Row['add_by']);
                    } 
                    $result[$index]['title'] = "<h5 class='media-heading'>".$Row['event_name']." </h5> New Exhibition added by ".$agent;
                     $result[$index]['date'] = $this->change_date_hours($Row['ts']); 
                     
                    $index++;
            }

            //$qry = "SELECT * FROM clients  where `ts` >= '".$beforeDate."' order by ts desc ";
            
            $qry = "SELECT * FROM clients order by ts desc limit 100";
            
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){ 
                if($Row['add_by']==100001){
                        $agent = $_SESSION['user_name_session'];
                    }else {
                        $agent = $this->getAgentName($Row['add_by']);
                    } 
                    
                $result[$index]['title'] = "<h5 class='media-heading'>".$Row['company_name']." </h5> New Client added by ".$agent; 
                 $result[$index]['date'] = $this->change_date_hours($Row['ts']); 
                $index++;
            }
            
            //$qry = "SELECT * FROM categories  where `last_update` >= '".$beforeDate."'  order by ts desc";
            $qry = "SELECT * FROM categories  order by ts desc limit 100";
            
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                
                if($Row['cat_add_by']==100001){
                        $agent = $_SESSION['user_name_session'];
                    }else {
                        $agent = $this->getAgentName($Row['cat_add_by']);
                    } 
                     
                $result[$index]['title'] = "<h5 class='media-heading'>".$Row['cat_title']." </h5> category updated by ".$agent;
                $result[$index]['date'] = $this->change_date_hours($Row['ts']); 
                
                $index++;
            }
            
            //$qry = "SELECT * FROM events where `last_update` >= '".$beforeDate."' order by ts desc";
            
            $qry = "SELECT * FROM events order by ts desc limit 100";
            
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){ 
                
                if($Row['add_by']==100001){
                        $agent = $_SESSION['user_name_session'];
                    }else {
                        $agent = $this->getAgentName($Row['add_by']);
                    }
                    
                $result[$index]['title'] = "<h5 class='media-heading'>".$Row['event_name']." </h5> Exhibition updated by ".$agent;
                $result[$index]['date'] = $this->change_date_hours($Row['ts']); 
                $index++;
            }
            
           // $qry = "SELECT * FROM clients where `last_update` >= '".$beforeDate."' order by ts desc";
            
            $qry = "SELECT * FROM clients order by ts desc limit 100";
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){ 
                    if($Row['add_by']==100001 || $Row['add_by']== 0 || $Row['add_by']==''  ){
                        $agent = $_SESSION['user_name_session'];
                    }else {
                        $agent = $this->getAgentName($Row['add_by']);
                    }
                    
                $result[$index]['title'] = "<h5 class='media-heading'>".$Row['company_name']." </h5> Client updated by ".$agent;
                $result[$index]['date'] = $this->change_date_hours($Row['ts']); 
                $index++;
            }
               
            return $result;
            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    } 


    
    
    public function getAgentsForSidebar(){
       try {
            
            $return = array();
            
                $stmt = $this->db->prepare("SELECT * FROM agents order by login_status desc");
                $stmt->execute();
                 $in = 0;
                while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                    
                    $return[$in]['agent_id'] = $Row['agent_id'];
                    $return[$in]['name'] = $Row['name'];
                    $return[$in]['login_status'] = $Row['login_status'];
                    $return[$in]['profile_img'] = $Row['profile_img'];
                    $return[$in]['agent_type'] = $this->getUserTypeName($Row['agent_type']);
                     
                $in++;    
                }
            
                return $return;
        
                } catch (PDOException $e) {
                echo $e->getMessage();
        }
    }
    
    
    public function getAgents($id){
       try {
            
            $return = array();
            if(!empty($id)){
                $stmt = $this->db->prepare("SELECT * FROM agents WHERE agent_id <> :uId and agent_type = 1");
                $stmt->execute(array(':uId' => $id));
                 
                while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                    $return[] = $Row;
                }
                
            }
            return $return;
            
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
    }
    

    
    public function getUserTypeName($id){
       try {
            
            $return = array();
            if(!empty($id)){
                $stmt = $this->db->prepare("SELECT ut_title FROM user_types WHERE utId = :uId ");
                $stmt->execute(array(':uId' => $id));
                $return = $stmt->fetch(PDO::FETCH_ASSOC);
                $return = $return['ut_title'];
            }
            return $return;
            
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
    }

        public function getLogs(){
        try {
            $result= array();
            $index = 0;
            $qry = "SELECT * FROM agents_log order by id desc limit 100 ";
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $result[$index]['name'] = $this->getAgentName($Row['agent_id']);
                $result[$index]['date'] = $this->change_date_hours($Row['ts']);
                if(!empty($Row['logout'])){
                    $result[$index]['logout_date'] = $this->change_date_hours($Row['logout']);
                }else if(!empty($Row['last_update'])) {
                    $result[$index]['logout_date'] =$this->change_date_hours($Row['last_update']);
                   }else {$result[$index]['logout_date'] = '';}
                $index++;
            }
            return $result;
        }catch (PDOException $e) {
                echo $e->getMessage();
        }
    }
    
    
 
    public function getAgentName($id){
       try {
            
            $return = array();
            if(!empty($id)){
                $stmt = $this->db->prepare("SELECT * FROM agents WHERE agent_id=:uId ");
                $stmt->execute(array(':uId' => $id));
                $return = $stmt->fetch(PDO::FETCH_ASSOC);
                $return = $return['name'];
            }
            return $return;
            
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
    }
    
    
    

    public function removeAssignClients($event_id, $client_id, $agent_id){
        try {
            $qry = "delete  FROM `clients_events_assigning` WHERE `event_id`=".$event_id." and `client_id` = ".$client_id." and `agent_id` = ".$agent_id;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
           
            return true;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }

}




 public function addSMSLogs($phone, $message, $sender, $agent, $client_name){
        try {
            
            $return = array(); 
            $i=0;
               //      
            $stmt = $this->db->prepare("INSERT INTO sms_log( phone, message, sender, agent,client) 
                        VALUES(:phone, :message, :sender, :agent, :client)");
            
            $stmt->bindparam(":phone", $phone);
            $stmt->bindparam(":message", $message);
            $stmt->bindparam(":sender", $sender);
            $stmt->bindparam(":agent", $agent);
            $stmt->bindparam(":client", $client_name);
            
            $stmt->execute();
            
        return true;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


    public function getSMSLogs(){
       try {
            
            $return = array(); 
            $stmt = $this->db->prepare("SELECT * FROM sms_log order by id desc ");
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
            }
            
            return $return;
            
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
    }
    
    
    
    



    function change_date($date){
	if(!empty($date)){
		$date1 = explode(' ',$date);
		$mn = explode('-',$date1[0]);
		$day = $mn[2];
		$year = $mn[0];
		$monthNum = $mn[1];
		$monthName = date('F', mktime(0, 0, 0, $monthNum, 10));  
		$return = $monthName.' '.$day.', '.$year .' '.$date1[1];
		
		$time = explode(':', $date1[1]);
		
		$time_in_hour = "";//date("g:i a", strtotime($time[0].':'.$time[1]));
		 
		return  $return ; 
        }
	} 

        
        
    function change_date_hours($date){
            
        if(!empty($date)){
                //$date = date( "Y-m-d H:i:s", strtotime($date)+4*3600 );
                $date = date( "Y-m-d H:i:s", strtotime($date)+4*3600 );
		$date1 = explode(' ',$date);
		$mn = explode('-',$date1[0]);
		$day = $mn[2];
		$year = $mn[0];
		$monthNum = $mn[1];
		$monthName = date('F', mktime(0, 0, 0, $monthNum, 10));  
		$return = $monthName.' '.$day.', '.$year .' '.   date("g:i:s a", strtotime($date1[1])+4);  ;
		
		$time = explode(':', $date1[1]);
		
		$time_in_hour = "";//date("g:i a", strtotime($time[0].':'.$time[1]));
		 
		return  $return ;
        }
	} 
    function change_date2($date){
	if(!empty($date)){
		$date1 = $date;
		$mn = explode('/',$date1);
		$day = $mn[1];
		$year = $mn[2];
		$monthNum = $mn[0];
		$monthName = date('F', mktime(0, 0, 0, $monthNum, 10));  
		$return = $day.'  '.$monthName.' '.$year;
		$time = explode(':', $date1[1]);
		$time_in_hour = "";//date("g:i a", strtotime($time[0].':'.$time[1]));
		return  $return ; 
        }
	}
        
    function change_date3($date){
	
        
        if(!empty($date)){
		$date1 = $date;
		$mn = explode('-',$date1);
		$day = $mn[2];
		$year = $mn[0];
		$monthNum = $mn[1];
		$monthName = date('M', mktime(0, 0, 0, $monthNum, 10));  
		$return = $day.'  '.$monthName.' '.$year;
		$time = explode(':', $date1[1]);
		$time_in_hour = "";//date("g:i a", strtotime($time[0].':'.$time[1]));
		return  $return ; 
        }
	}

 function change_date4($date){
	if(!empty($date)){
		$date1 = $date;
		$mn = explode('-',$date1);
		$day = $mn[2];
		$year = $mn[0];
		$monthNum = $mn[1];
		$monthName = date('M', mktime(0, 0, 0, $monthNum, 10));  
		$return = $day.'  '.$monthName;
		$time = explode(':', $date1[1]);
		$time_in_hour = "";//date("g:i a", strtotime($time[0].':'.$time[1]));
		return  $return ; 
        }
	}

        
public function getCities(){
        try {
            
            $return = array(); 
            $qry = "SELECT * FROM cities ";
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
           }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
} 

public function getCountries(){
        try {
            
            $return = array(); 
            $qry = "SELECT * FROM countries ";
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $return[] = $Row;
                $i++;
           }
        return $return;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
} 


}

?>