<?php
class Analysis_report_model extends CI_Model
{
	//get role
	public function get_role() {
	   $this->db->from('role');
	   $this->db->order_by('role_name', 'asc');
	   return $this->db->get();
	}
	
	//get category
	public function get_category() {
	   $this->db->from('category');
	   $this->db->order_by('cat_name', 'asc');
	   return $this->db->get();
	}
	
	
	//get lead status
	public function get_lead_status() {
	   $this->db->from('lead_status');
	   $this->db->order_by('status_id', 'asc');
	   return $this->db->get();
	  
	}
	
	//get prospect status
	public function get_prospect_level() {
	   $this->db->from('prospect_level');
	   $this->db->order_by('prospect_id', 'asc');
	   return $this->db->get();
	}
	
	//get employee list
	/*public function get_employee_list($category) {
	   $this->db->from('employees');
	   $this->db->join('people', 'people.person_id = employees.person_id');
	   $this->db->where('deleted', 0);
	   $this->db->where_in('category', $category);
	   return $this->db->get()->result();
	}*/
	
	public function get_employee_list($role,$category){
	  
	   /*$role   = implode(",",$role);
	   $category = implode(",",$category);
	   
	   $this->db->select('employees.id as emp_id,people.first_name as name');
	   $this->db->from('employees');
	   $this->db->join('people', 'people.person_id = employees.person_id');
	   $this->db->where_in('employees.role',$role);
	   $this->db->where_in('employees.category',$category);
	   $this->db->where('deleted', 0);
	   $this->db->get()->result();
	   echo $this->db->last_query();*/
	}

	
	public function search($start_date,$end_date,$process,$category,$lead_status,$prospect_level,$issuance_status,$document_issue_status,$renewal_status,$client_status,$premium_status){
		
		$start_date = $this->db->escape($start_date);
		$end_date   = $this->db->escape($end_date);
		
		
		//Lead, Product and Renewal Select field name		
		if($process === "lead"){			
			$select = "ospos_leads.lead_id,ospos_lead_type_info.lead_type_id,cust_name,cust_mobile,cat_name,prospect_name,statusname,ncd,expected_value,cre_people.first_name as created_by,con_people.first_name as control_by,rm_people.first_name as rm_name,lead_type.lead_type,ospos_lead_type_info.created_date as createddate,tl_people.first_name as tl_name";
		}else
		if($process === "product"){
			
			$select = "ospos_leads.lead_id,ospos_lead_type_info.lead_type_id,cust_name,cust_mobile,cat_name,prospect_name,statusname,overall_net_premium,overall_total_premium,issuance_status,document_issue_status,sum_assured,policy_term,paying_term,product_name,ospos_vendor.vendorcompanyname,ospos_leads_product.mode,lead_type.lead_type,happy_calling.policy_no,ospos_happy_calling.login_date,ospos_happy_calling.issued_date,rm_people.first_name as rm_name,ospos_lead_type_info.created_date as createddate,tl_people.first_name as tl_name,rm_username.username as rm_code";
		}else
		if($process === "renewal"){
			$select = "ospos_leads.lead_id,cust_name,cust_mobile,cat_name,prospect_name,statusname,renew_status,client_status,premium_status,ospos_renewal.policy_no,ospos_renewal.sum_assured,ospos_renewal.renewal_date,ospos_renewal.issued_date,ospos_renewal.paying_year,ospos_lead_type_info.created_date as createddate";
		}
		
		//Common DB selection
		$this->db->select($select);
		$this->db->from('leads');
		$this->db->join('customers', 'customers.cust_id = leads.cust_id');
		$this->db->join('lead_type_info', 'lead_type_info.lead_id = leads.lead_id');
		$this->db->join('prospect_level', 'prospect_level.prospect_id = lead_type_info.prospect_level');
		$this->db->join('category', 'category.cat_id = lead_type_info.category','left');
		$this->db->join('lead_status', 'lead_status.status_id = lead_type_info.lead_status','left');
		$this->db->join('lead_type', 'lead_type.lead_type_id = lead_type_info.lead_type');
		

		//Lead,Product and Renewal Process db join query
		
		if($process === "lead"){
			$this->db->join('ospos_people cre_people', 'cre_people.person_id = ospos_lead_type_info.created_by','left');
			$this->db->join('ospos_people con_people', 'con_people.person_id = ospos_lead_type_info.current_control','left');
			$this->db->join('ospos_people rm_people', 'rm_people.person_id = ospos_lead_type_info.rm_name','left');
			$this->db->join('ospos_people tl_people', 'tl_people.person_id = ospos_lead_type_info.team_leader','left');
			$this->db->where("DATE_FORMAT(ospos_lead_type_info.created_date, '%Y-%m-%d') BETWEEN $start_date AND $end_date");
		}else
		if($process === "product"){
			$this->db->join('leads_product', 'leads_product.lead_info_id = ospos_lead_type_info.lead_type_id');
			$this->db->join('happy_calling', 'happy_calling.call_product_id = leads_product.customer_product_id');	
			$this->db->join('items', 'items.product_id = leads_product.product');	
			$this->db->join('vendor', 'vendor.vendor_id = leads_product.company');	
			$this->db->join('ospos_people rm_people', 'rm_people.person_id = ospos_lead_type_info.rm_name','left');
			$this->db->join('ospos_people tl_people', 'tl_people.person_id = ospos_lead_type_info.team_leader','left');
			$this->db->join('ospos_employees rm_username', 'rm_username.person_id = ospos_lead_type_info.rm_name','left');

			
			if($issuance_status){
				$this->db->where_in("ospos_happy_calling.issuance_status", $issuance_status);
			}
			if($document_issue_status){
				$this->db->where_in("ospos_happy_calling.document_issue_status", $document_issue_status);
			}
			$this->db->where('leads_product.status', 1);			
			$this->db->where("DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') BETWEEN $start_date AND $end_date");
			
		}else
		if($process === "renewal"){
			$this->db->join('renewal', 'renewal.lead_id = leads.lead_id');
			
			if($renewal_status){
				$this->db->where_in("ospos_renewal.renew_status", $renewal_status);
		    }
		    if($client_status){
		    	$this->db->where_in("ospos_renewal.client_status", $client_status);
		    }
		    if($premium_status){
		    	$this->db->where_in("ospos_renewal.premium_status", $premium_status);
		    }
			$this->db->where('ospos_renewal.staus', 1);
		}
		
		//common condition part
		if($category){
			$this->db->where_in("ospos_lead_type_info.category", $category);
		}
		if($lead_status){
			$this->db->where_in("ospos_lead_type_info.lead_status", $lead_status);
		}
		if($prospect_level){
			$this->db->where_in("ospos_lead_type_info.prospect_level", $prospect_level);
		}
		$this->db->where('ospos_leads.status', 1);
		$this->db->where('ospos_lead_type_info.status', 1);	
		return $this->db->get()->result();
		//echo $this->db->last_query(); exit(0);

	}
}
?>