<?php
class Sabarimalai_pkg_model extends CI_Model
{
	
public function get_package() {
	  $this->db->from('sblt_package_management');
    $this->db->order_by('package_id', 'asc');
    return $this->db->get();
}

public function sabarimalai_pkg_report($filters,$package){

   $start_date = $filters['start_date'];
   $end_date   = $filters['end_date']; 
   $start = date('d-M-Y',strtotime($start_date));
   $end = date('d-M-Y',strtotime($end_date));
   $pkg = "and sblt_package_line.package_id = '$package'";
   if($package === "All" || $package === ""){
    $pkg = "";
   }
     $qry = $this->db->query("SELECT from_date,to_date,trip_days,bus_count,person_count,package_sts,gurusamy_name,catering_name,coordinator_one,coordinator_two,coordinator_three,package_name,sourcing,count(*) as count from sblt_package_management inner join sblt_package_line on sblt_package_management.package_id=sblt_package_line.package_id  where sblt_package_management.status = '1' $pkg and (DATE_FORMAT(sblt_package_management.from_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date' or DATE_FORMAT(sblt_package_management.to_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date') group by sourcing,package_name,sblt_package_management.package_id");
     $row = $qry->result_array(); 
     $count = 1;
     $Regular = 0;
     $Web = 0;
     $Referral = 0;
     $Staff_Referral = 0;
     $Banner = 0;
     $Others = 0;
      foreach ($row as $key => $value) {     
      $tot = $value['count']; 
      $from_date = date('d-m-Y',strtotime($value['from_date']));
      $to_date   = date('d-m-Y',strtotime($value['to_date']));
      $trip_days = $value['trip_days']; 
      $bus_count = $value['bus_count']; 
      $person_count = $value['person_count']; 
      $package_sts = $value['package_sts'];
      $gurusamy_name = $value['gurusamy_name']; 
      $catering_name = $value['catering_name']; 
      $coordinator_one = $value['coordinator_one']; 
      $coordinator_two = $value['coordinator_two']; 
      $coordinator_three = $value['coordinator_three']; 
      $package_name = $value['package_name']; 
      $sourcing = $value['sourcing']; 

      if($sourcing === "Regular"){
        $Regular = $tot;
      }else
      if($sourcing === "Web"){
        $Web = $tot;
      }else
      if($sourcing === "Referral"){
        $Referral = $tot;
      }else
      if($sourcing === "Staff_Referral"){
        $Staff_Referral = $tot;
      }else
      if($sourcing === "Banner"){
        $Banner = $tot;
      }else
      if($sourcing === "Others"){
        $Others = $tot;
      }
          
      $tr_line .="<tr>
                        <td style ='color:#00000 !important;text-align:center !important;'>$count</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$from_date</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$to_date</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$gurusamy_name</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$catering_name</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$coordinator_one</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$coordinator_two</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$coordinator_three</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$bus_count</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$person_count</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$trip_days</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$package_name</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$Regular</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$Web</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$Referral</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$Staff_Referral</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$Banner</td>
                        <td style ='color:#00000 !important;text-align:center !important;'>$Others</td></tr>";
     $count ++;
   }
   echo "<table id='detail_list' class='table table-hover table-striped col-style'>
       <thead>
        <tr><h5 style='font-weight:bold;text-align:center !important;'>$start to $end</h5></tr>
        <tr style='font-weight:bold;text-align:center !important;background:#3369E6;'>
             <th style='text-align:center !important;padding:4px;'>Sl.No</th>
             <th style='text-align:center !important;padding:4px;'>From</th>
             <th style='text-align:center !important;padding:4px;'>To</th>
             <th style='text-align:center !important;padding:4px;'>Gurusamy</th>
             <th style='text-align:center !important;padding:4px;'>Catering</th> 
             <th style='text-align:center !important;padding:4px;'>Coordinator-1</th>  
             <th style='text-align:center !important;padding:4px;'>Coordinator-2</th>
             <th style='text-align:center !important;padding:4px;'>Coordinator-3</th>
             <th style='text-align:center !important;padding:4px;'>Buses</th> 
             <th style='text-align:center !important;padding:4px;'>Total Paxes</th> 
             <th style='text-align:center !important;padding:4px;'>Trip Days</th> 
             <th style='text-align:center !important;padding:4px;'>Package</th> 
             <th style='text-align:center !important;padding:4px;'>Regular</th> 
             <th style='text-align:center !important;padding:4px;'>Web</th> 
             <th style='text-align:center !important;padding:4px;'>Referral</th> 
             <th style='text-align:center !important;padding:4px;'>Staff Referral</th> 
             <th style='text-align:center !important;padding:4px;'>Banner</th> 
             <th style='text-align:center !important;padding:4px;'>Others</th>  
        </tr>       
      </thead>
      <tbody>$tr_line</tbody>
      </table>";
  }
}
?>