<?php

class Abstrack_model extends CI_Model
{

	public function role_wise_abstrack_report($start_date,$end_date){
	  
	   //enquiry sts and count  
	   $qry = $this->db->query("SELECT IFNULL(count(*),0) as tot,enquiry_sts from sblt_enquiry where sblt_enquiry.status = '1' and from_date between '$start_date' and '$end_date' group by enquiry_sts ");   
	   $row = $qry->result_array();
	   //print_r($row); die();
		  $enquirey_followup = 0;
		  $enquirey_confirm  = 0;
		  $enquirey_cancel   = 0;
		  
		foreach($row as $rslt){
			$enquiry_sts = $rslt['enquiry_sts'];
			$tot         = $rslt['tot'];
			if($enquiry_sts === "1"){
			$enquirey_followup = $tot;
			//echo "$enquirey_followup<br/>";
			}else
			if($enquiry_sts === "2"){
			$enquirey_confirm = $tot;
			//echo "$enquirey_confirm <br/>";
			}else
			if($enquiry_sts === "3"){
			$enquirey_cancel = $tot;
			//echo "$enquirey_cancel";
			}
		}			
		 
	   /*if($row[0]['enquiry_sts']===1){
		$enquirey_followup=$row[0]['tot'];
	   }
	   if($row[1]['enquiry_sts']==NULL){
		$enquirey_confirm=$row[1]['tot'];
	   }
	   if($row[2]['tot']!==NULL){
		$enquirey_cancel=$row[2]['tot'];
	   }*/
	   
	   //Direct booking count
	   
	   $DBookingrow_tot=0;
	   $qry_DBooking = $this->db->query("SELECT count(*) as tot,booking_sts from sblt_booking where (enquiry_id = '0' or enquiry_id IS NULL) and sblt_booking.booking_sts in ('2','5','4') and sblt_booking.status = '1' and from_date between '$start_date' and '$end_date'");
	   $DBookingrow = $qry_DBooking->result_array();
	   
	   if($DBookingrow[0]['tot']!==NULL){
	     $DBookingrow_tot=$DBookingrow[0]['tot'];
	   }
	   
	   //enq booking followup
	   $enqBooking_tot=0;
	   $enqBooking = $this->db->query("SELECT count(*) as tot,booking_sts from sblt_booking INNER join sblt_enquiry ON sblt_enquiry.enquiry_id = sblt_booking.enquiry_id where enquiry_sts = '2' and sblt_booking.status = '1' and  sblt_booking.booking_sts in (2,4,5) and sblt_booking.from_date between '$start_date' and '$end_date'");
	   $enqBooking_total = $enqBooking->result_array();
	   
	   if($enqBooking_total[0]['tot']!==NULL){
	     $enqBooking_tot=$enqBooking_total[0]['tot'];
	   }
	   
	   //Booking cancelled
	   $DBookingcancelrow_tot=0;
	   $qry_booking_cancel =$this->db->query("SELECT count(*) as tot,booking_sts from sblt_booking where sblt_booking.status = '1' and from_date between '$start_date' and '$end_date' and booking_sts='3'");
	   $DBookingcancelrow = $qry_booking_cancel->result_array();
	   
	   if($DBookingcancelrow[0]['tot']!==NULL){
	     $DBookingcancelrow_tot=$DBookingcancelrow[0]['tot'];
	   }
	   
	   //Total Booking
	   $DBookingtotalrow_tot=0;
	   $qry_booking_total =$this->db->query("SELECT count(*) as tot,booking_sts from sblt_booking where sblt_booking.status = '1' and from_date between '$start_date' and '$end_date' and booking_sts in (2,5,4)");
	   $DBookingtotalrow = $qry_booking_total->result_array();
	   
	   if($DBookingtotalrow[0]['tot']!==NULL){
	    $DBookingtotalrow_tot=$DBookingtotalrow[0]['tot'];
	   }
	   
	   //Booking Follow
	   
	   //Booking cancelled
	   $bookingfollowrow_tot=0;
	   $qry_booking_follow =$this->db->query("SELECT count(*) as tot,booking_sts from sblt_booking where sblt_booking.status = '1' and from_date between '$start_date' and '$end_date' and booking_sts='1'");
	   $DBookingfollowrow = $qry_booking_follow->result_array();
	   
	   if($DBookingfollowrow[0]['tot']!==NULL){
	     $bookingfollowrow_tot=$DBookingfollowrow[0]['tot'];
	   }
	   
	   
	   $salesconversion_array = array();
	   $salesconversion_array['salesconversion']= array('enquirey_followup'=>$enquirey_followup,'enquirey_confirm'=>$enquirey_confirm,'enquirey_cancel'=>$enquirey_cancel,'DBookingrow_tot'=>$DBookingrow_tot,'enqBooking_tot'=>$enqBooking_tot,'bookingfollowrow_tot'=>$bookingfollowrow_tot,'DBookingcancelrow_tot'=>$DBookingcancelrow_tot,'DBookingtotalrow_tot'=>$DBookingtotalrow_tot);
	      
	   return $salesconversion_array;
	      
	   
	}
}
?>