<?php

class CLIENTS {

    private $db;

    function __construct($DB_con) {
        $this->db = $DB_con;
    }

public function addClient($company_name, $owner_name, $categories, $website, $products, $office_add,$city, $country, $fax, $add_by, $lead_source, $member_of) {
        
        try {
            $stmt = $this->db->prepare("INSERT INTO clients(
                            company_name,
                            owner_name,
                            categories,
                            website,
                            lead_source,
                            products,
                            office_add,
                            city,
                            country,
                            fax,
                            add_by,
                            member_of
                            ) 
		            VALUES(
                            :company_name,
                            :owner_name,
                            :categories,
                            :website,
                            :lead_source,
                            :products,
                            :office_add,
                            :city,
                            :country,
                            :fax,
                            :add_by,
                            :member_of
                            )");
            
            $stmt->bindparam(":company_name", trim($company_name));
            $stmt->bindparam(":owner_name", trim($owner_name));
            $stmt->bindparam(":categories", trim($categories));
            $stmt->bindparam(":products", trim($products));
            $stmt->bindparam(":website", trim($website));
            $stmt->bindparam(":lead_source", trim($lead_source));
            $stmt->bindparam(":office_add",  trim($office_add));
            $stmt->bindparam(":city", trim($city));
            $stmt->bindparam(":country", trim($country));
            $stmt->bindparam(":fax", trim($fax));
            $stmt->bindparam(":add_by", trim($add_by));
            $stmt->bindparam(":member_of", trim($member_of));
            
            
            $stmt->execute();
            return  $this->db->lastInsertId();
            
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
}


public function addClientPerson($client_id, $f_name, $l_name, $designation, $email, $mobile, $landline, $line_ext) {
        
        try {
            $stmt = $this->db->prepare("INSERT INTO client_persons(
                                client_id,
                                f_name,
                                l_name,
                                designation,
                                email,
                                mobile,
                                landline,
                                line_ext
                            ) 
		            VALUES(
                                :client_id,
                                :f_name,
                                :l_name,
                                :designation,
                                :email,
                                :mobile,
                                :landline,
                                :line_ext
                            )");
            
            $stmt->bindparam(":client_id", $client_id);
            $stmt->bindparam(":f_name", $f_name);
            $stmt->bindparam(":l_name", $l_name);
            $stmt->bindparam(":designation", $designation);
            $stmt->bindparam(":email", $email);            
            $stmt->bindparam(":mobile", $mobile);
            $stmt->bindparam(":landline", $landline);
            $stmt->bindparam(":line_ext", $line_ext); 
            //$stmt->bindparam(":last_update", date("Y-m-d H:i:s") ); 
            $stmt->execute();
            return  $this->db->lastInsertId();
            
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
    }
 
public function addUpdateClient($client_id , $company_name, $owner_name, $categories, $website, $products, $office_add,$city, $country, $fax, $add_by, $lead_source, $member_of) {
        
        try {
            $stmt = $this->db->prepare("Update clients Set
                            company_name = :company_name,
                            owner_name = :owner_name,
                            categories = :categories,
                            website = :website,
                            products = :products,
                            office_add = :office_add,
                            city = :city,
                            country = :country,
                            fax = :fax,
                            add_by  = :add_by,
                            lead_source = :lead_source,
                            member_of = :member_of,
                            last_update = :last_update
                            where client_id = :client_id
                        ");
            
            $stmt->bindparam(":company_name", $company_name);
            $stmt->bindparam(":owner_name", $owner_name);
            $stmt->bindparam(":categories", $categories);
            $stmt->bindparam(":products", $products);
            $stmt->bindparam(":website", $website);            
            $stmt->bindparam(":office_add", $office_add);
            $stmt->bindparam(":city", $city);
            $stmt->bindparam(":country", $country);
            $stmt->bindparam(":fax", $fax);
            $stmt->bindparam(":add_by", $add_by);
            $stmt->bindparam(":lead_source", $lead_source);
            $stmt->bindparam(":member_of", $member_of);
            $stmt->bindparam(":client_id", $client_id); //
            $stmt->bindparam(":last_update", date("Y-m-d H:i:s") ); 
            
            $stmt->execute();
            return  $this->db->lastInsertId();
            
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
}




public function addUpdateClientStatus($client_id) {
        
        try {
            $stmt = $this->db->prepare("Update clients Set `status` = 'Deactive'    where client_id = :client_id ");
            $stmt->bindparam(":client_id", $client_id); //
            $stmt->execute();
            
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
}

public function deleteClientAndClientPersons($client_id) {
        
        try {
            $stmt = $this->db->prepare("Delete FROM clients  where client_id = :client_id ");
            $stmt->bindparam(":client_id", $client_id); //
            $stmt->execute();
            
            
            $stmte = $this->db->prepare("Delete FROM client_persons where client_id = :client_id ");
            $stmte->bindparam(":client_id", $client_id); //
            $stmte->execute();
                        
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
}



public function updateClientPerson($person_id, $client_id, $f_name, $l_name, $designation, $email, $mobile, $landline, $line_ext) {
        
        try {
            $stmt = $this->db->prepare("Update client_persons Set
                                client_id =:client_id ,
                                f_name =:f_name ,
                                l_name =:l_name ,
                                designation =:designation ,
                                email =:email ,
                                mobile =:mobile ,
                                landline =:landline ,
                                line_ext =:line_ext 
                                where person_id = :person_id
                            ");
            
            $stmt->bindparam(":client_id", $client_id);
            $stmt->bindparam(":f_name", $f_name);
            $stmt->bindparam(":l_name", $l_name);
            $stmt->bindparam(":designation", $designation);
            $stmt->bindparam(":email", $email);            
            $stmt->bindparam(":mobile", $mobile);
            $stmt->bindparam(":landline", $landline);
            $stmt->bindparam(":line_ext", $line_ext); 
            $stmt->bindparam(":person_id", $person_id); 
            
            $stmt->execute();
            return  $this->db->lastInsertId();
            
        } catch (PDOException $e) {
                echo $e->getMessage();
        }
    }

    
    
public function deletePerson($id) {
        try {
            $stmt = $this->db->prepare("Delete FROM client_persons WHERE person_id=:uId");
            $stmt->execute(array(':uId' => $id));
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

    
    
public function getClients($id='', $not_persons=''){
        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);
               
                $i=0;
                $stmt = $this->db->prepare("SELECT * FROM clients where client_id = ".$id." order by client_id desc");
                $stmt->execute();
                while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                      //$return[] = $Row;
                        $return = $Row;
                        $stmt2 = $this->db->prepare("SELECT * FROM client_persons where client_id = ".$Row['client_id']);
                        $stmt2->execute();
                        while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                             $return['persons'][] = $Row2;
                        }
                  $i++;
               }
               
                
            }else {
                
                $i=0;
				
                if($_SESSION['user_role_session']!="Admin" ){
                       $stmt = $this->db->prepare("SELECT * FROM clients where `status`= 'Active' order by client_id desc"); 
                }else {

                        $stmt = $this->db->prepare("SELECT * FROM clients order by client_id desc");

                        }

                $stmt->execute();
                while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                        $return[$i] = $Row; 
                        if(empty($not_persons)){
                            $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 CheckClient1(){
            try {

                $return = array();
                $i=0;
                $stmt2 = $this->db->prepare("SELECT * FROM client_persons_or ");
                    $stmt2->execute();
                    while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                         $return[] = $Row2;
                    }
                
                return $return;

            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
        
        
        
    public function CheckEmail2($email){
            try {
                $return = array();
                if(!empty($email)){
                    $i=0;
                    $stmt2 = $this->db->prepare("SELECT * FROM client_persons_live  where email like '%".$email."%' ");
                        $stmt2->execute();
                        while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                             $return = $Row2;
                        }
                }
                return $return;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
        
    public function updateEmail2($email, $new){
            try {
                $return = array();
                if(!empty($email)){
                    $i=0;
                    $stmt2 = $this->db->prepare("update client_persons_live set email = '".$new."' where email = '".$email."' ");
                        $stmt2->execute();
                }
                return true;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
        
 public function CheckWeb2($email){
            try {
                $return = array();
                if(!empty($email)){
                    $i=0;
                    $stmt2 = $this->db->prepare("SELECT * FROM client_persons_live  where email like '%".$email."%' ");
                        $stmt2->execute();
                        while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                             $return = $Row2;
                        }
                }
                return $return;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
        
    public function updateWeb2($email, $new){
            try {
                $return = array();
                if(!empty($email)){
                    $i=0;
                    $stmt2 = $this->db->prepare("update client_persons_live set email = '".$new."' where email = '".$email."' ");
                        $stmt2->execute();
                }
                return true;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
        
        
        
        
        
    public function getClientsPersons($client_id){
            try {

                $return = array();
                if(!empty($client_id)){
                        
                    $i=0;
                    $stmt2 = $this->db->prepare("SELECT * FROM client_persons where client_id = ".$client_id);
                        $stmt2->execute();
                        while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                             $return[] = $Row2;
                        }
                }
                return $return;

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


    public function checkCompany($company, $id = ""){
            try {   
                $return = array();
                $i=0;
                if(!empty($company)){
                
                    if(!empty($id)){
                        $stmt2 = $this->db->prepare("SELECT * FROM clients  where company_name = '".$company."' and client_id <> $id");
                    }else {
                        $stmt2 = $this->db->prepare("SELECT * FROM clients  where company_name = '".$company."'");
                    }
                        $stmt2->execute();
                        if($stmt2->rowCount()>0){
                            return "yes";
                        }else { return "no"; }
                }
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }


    
    
    
    
public function getClientsByCategory($cat_id, $event_id){
        try {
            
            $return = array();
            if(!empty($cat_id)){
                    "SELECT * FROM clients where  FIND_IN_SET( '".$cat_id."', `categories` ) AND `client_id` NOT IN (SELECT `client_id` FROM `clients_events_assigning` WHERE `event_id` = ".$event_id.")  order by company_name";
                $i=0;
                $stmt = $this->db->prepare("SELECT * FROM clients where  FIND_IN_SET( '".$cat_id."', `categories` ) AND `client_id` NOT IN (SELECT `client_id` FROM `clients_events_assigning` WHERE `event_id` = ".$event_id.")  order by company_name");
                
                
                
                $stmt->execute();
                while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                      //$return[] = $Row;
                        $return[] = $Row;
                      $i++;
               }
            }
            return $return;
            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

 
public function getUnassignClients($event_id, $agent_id){
        try {
            
            $return = array(); 
            
            $agent_id = $this->getEventAssigns($event_id);
             
            
              $qry = "SELECT * FROM clients where `client_id` NOT IN (SELECT `client_id` FROM `clients_events_assigning` WHERE `event_id` = ".$event_id." and agent_id in (".$agent_id.")) order by company_name ";
            
            $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 getEventAssigns($event_id){
        try {
            
            $return = array(); 
            
              $qry = "SELECT assign_agent, add_by FROM events where `event_id` = $event_id";
            
            $i=0;
            $stmt = $this->db->prepare($qry);
            $stmt->execute();
            while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){ 
                    
                    if(!empty($Row['assign_agent'])){
                        
                        $as_ag = trim($Row['assign_agent']);
                        $ind = strlen($as_ag);
                        
                        if($as_ag[$ind-1]==","){
                           $a = substr($as_ag, 0, $ind-1); 
                        }else {
                            $a = $as_ag; 
                        }
                        
                        $return= $a.', '.$Row['add_by'];
                        
                        
                        
                    }else { 
                        $return= $Row['assign_agent'];
                    }
              $i++;
           }
        
        return $return;

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

}
    
    
    
    
    
    
    
    
    
    
    
    
    
    
public function checkAgent($email=''){
        try {
            
            $return = '';
            
                $stmt = $this->db->prepare("SELECT * FROM agents WHERE email=:email ");
                $stmt->execute(array(':email' => $email));
                $return = $stmt->fetch(PDO::FETCH_ASSOC);
                
                if(count($return)>1){
                    $return = 'exists'; 
                }
            
            return $return;
            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

public function update($agent_id, $full_name, $father_name, $contact,$mobile, $address, $gender, $cnic, $join_date, $profile_img) {
        try {
            //echo "here";
            $stmt = $this->db->prepare("Update agents
                            Set
                            name = :full_name,
                            father_name = :father_name,
                            contact_no = :contact,
                            mobile_no = :mobile,
                            gender = :gender,
                            address = :address,
                            cnic = :cnic,
                            join_date = :join_date,
                            profile_img = :profile_img
                            where agent_id = :ag_id
                            "
                            );
            
            $stmt->bindparam(":full_name", $full_name);
            $stmt->bindparam(":father_name", $father_name);
            $stmt->bindparam(":contact", $contact);
            $stmt->bindparam(":mobile", $mobile);
            $stmt->bindparam(":address", $address);
            $stmt->bindparam(":gender", $gender);
            $stmt->bindparam(":cnic", $cnic);
            $stmt->bindparam(":join_date", $join_date);
            $stmt->bindparam(":profile_img", $profile_img); 
            $stmt->bindparam(":ag_id", $agent_id);
 
            $stmt->execute();

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

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

public function getAllCategories(){
        try {
            
            $return = array();
            
                $stmt = $this->db->prepare("SELECT * FROM categories WHERE cat_parentId = 0" );
                $stmt->execute();
                $i=0;
                while($Row = $stmt->fetch(PDO::FETCH_ASSOC)){
                      //$return[] = $Row;
                      $stmt2 = $this->db->prepare("SELECT * FROM categories WHERE cat_parentId = ".$Row['cat_id'] ."  " );
                      $stmt2->execute();
                      
                        $return[$i]['cat_id'] =$Row['cat_id'];
                        $return[$i]['category'] =$Row['cat_title'];
                        $return[$i]['desc'] =$Row['cat_desc'];
                        $return[$i]['status'] =$Row['cat_status'];
                        $j = 0 ;
                      while($Row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
                          
                                $return[$i]['sub_cat'][$j]['category'] =$Row2['cat_title'];
                                $return[$i]['sub_cat'][$j]['desc'] =$Row2['cat_desc'];
                                $return[$i]['sub_cat'][$j]['status'] =$Row2['cat_status'];    
                                $return[$i]['sub_cat'][$j]['main'] =$Row['cat_title'];
                                $return[$i]['sub_cat'][$j]['cat_id'] =$Row2['cat_id'];
                                
                            $j++;
                      }
                     $i++; 
                }
            
            return $return;
            
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
 

    public function redirect($url) {
        header("Location: $url");
    } 
}

?>