File: //home/cafsindia/allyindian_com/sbltt/application/models/Aggregate_sales_model.php
<?php
class Aggregate_sales_model extends CI_Model
{
public function search($search, $filters, $rows = 0, $limit_from = 0, $sort = '', $order = 'asc'){
$crm = $filters['crm'];
$crmto = $filters['crmto'];
$emp_id = $this->session->userdata('emp_id');
$category = $this->session->userdata('emp_category');
$role_id = $this->session->userdata('emp_role');
if(!$sort){
$sort = "lead_type_info.ncd";
}
if($role_id === "6"){
$team = $this->get_team_info($emp_id);
$team_rslt = explode(",",$team->teammembers);
$team_rslt[] = $emp_id;
}else
if($role_id === "4"){
$area_info = $this->get_area_info($emp_id);
$rslt = "";
foreach ($area_info as $key => $value){
$team = $this->get_team_info($value->id);
if($rslt !== ""){
$rslt .= ",$team->teammembers,$value->id";
}else{
$rslt .= "$team->teammembers,$value->id";
}
}
$team_rslt = explode(",",$rslt);
}else
if($role_id === "9"){
$area_info = $this->get_area_info($emp_id);
$rslt = "";
foreach ($area_info as $key => $value){
if($rslt !== ""){
$rslt .= ",$value->id";
}else{
$rslt .= "$value->id";
}
}
$team_rslt = explode(",",$rslt);
}
$this->db->select('lead_type_info.lead_id as leads_id,customers.cust_name,customers.cust_mobile,customers.cust_address,lead_type.lead_type,lead_status.statusname,prospect_level.prospect_name,lead_type_info.ncd,lead_type_info.remarks,lead_type_info.created_by,lead_type_info.expected_login,lead_type_info.current_control,lead_type_info.created_date,category.cat_name');
$this->db->from('lead_type_info');
$this->db->join('leads', 'leads.lead_id = lead_type_info.lead_id');
$this->db->join('customers', 'customers.cust_id = leads.cust_id');
$this->db->join('lead_type', 'lead_type.lead_type_id = lead_type_info.lead_type');
$this->db->join('lead_status', 'lead_status.status_id = lead_type_info.lead_status');
$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');
$this->db->group_start();
$this->db->like('customers.cust_name', $search);
$this->db->or_like('customers.cust_mobile', $search);
$this->db->or_like('lead_type.lead_type', $search);
$this->db->or_like('lead_status.statusname', $search);
$this->db->or_like('prospect_level.prospect_name', $search);
$this->db->or_like('category.cat_name', $search);
if(strpos($search, '-') !== false){
$this->db->or_like('lead_type_info.ncd', date('Y-m-d',strtotime($search)));
$this->db->or_like('lead_type_info.created_date', date('Y-m-d',strtotime($search)));
}
$this->db->group_end();
//Category
if(($role_id === "3") || ($role_id === "5") || ($role_id === "6")){
$this->db->where('lead_type_info.category',$category);
}
//Created by and current control
if(($role_id === "1") || ($role_id === "2")){
$this->db->where("lead_type_info.prospect_level !=",3);
$this->db->where("lead_type_info.prospect_level !=",4);
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",3)->or_where("lead_type_info.lead_status",4)->or_where("lead_type_info.lead_status",5)->or_where("lead_type_info.lead_status",6)->or_where("lead_type_info.lead_status",7)->or_where("lead_type_info.lead_status",9)->or_where("lead_type_info.lead_status",10);
$this->db->group_end();
}else
if($role_id === "3"){
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",10)->or_where("lead_type_info.lead_status",7);
$this->db->group_end();
$this->db->group_start();
$this->db->where("lead_type_info.created_by",$emp_id)->or_where("lead_type_info.current_control",$emp_id);
$this->db->group_end();
}else
if($role_id === "4"){
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",4)->or_where("lead_type_info.lead_status",10);
$this->db->group_end();
$this->db->where_in('lead_type_info.created_by', $team_rslt);
}else
if($role_id === "5"){
$this->db->where("lead_type_info.prospect_level !=",3);
$this->db->where("lead_type_info.prospect_level !=",4);
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",6)->or_where("lead_type_info.lead_status",9);
$this->db->group_end();
$this->db->group_start();
$this->db->where("lead_type_info.created_by",$emp_id)->or_where("lead_type_info.current_control",$emp_id)->or_where("lead_type_info.rm_name",$emp_id);
$this->db->group_end();
}else
if($role_id === "6"){
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",4)->or_where("lead_type_info.lead_status",10);
$this->db->group_end();
$this->db->group_start();
$this->db->where_in("lead_type_info.created_by",$team_rslt)->or_where("lead_type_info.current_control",$emp_id);
$this->db->group_end();
}else
if($role_id === "9"){
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",6);
$this->db->group_end();
$this->db->group_start();
$this->db->where_in("lead_type_info.created_by",$team_rslt)->or_where("lead_type_info.current_control",$emp_id)->or_where_in("lead_type_info.rm_name",$team_rslt);
$this->db->group_end();
}
$this->db->where("lead_type_info.created_by", $crm);
$this->db->where("lead_type_info.current_control", $crm);
$this->db->where('lead_type_info.status', 1);
if(($role_id === "1") || ($role_id === "2") || ($role_id === "4") || ($role_id === "9")) {
$this->db->where('lead_type_info.lead_mode', 1);
}
$this->db->order_by($sort, $order);
if($rows > 0){
$this->db->limit($rows, $limit_from);
}
return $this->db->get();
}
public function get_area_info($id){
$this->db->from('employees');
$this->db->where('reporting', $id);
$this->db->where('employees.deleted',0);
return $this->db->get()->result();
}
public function get_found_rows($search, $filters){
return $this->search($search, $filters)->num_rows();
}
public function get_crm($id) {
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->where('role', '3');
$this->db->where('reporting',$id);
$this->db->order_by('first_name', 'asc');
return $this->db->get()->result_array();
}
public function get_role() {
$this->db->from('role');
$this->db->order_by('role_name', 'asc');
return $this->db->get();
}
public function get_category() {
$this->db->from('category');
$this->db->order_by('cat_name', 'asc');
return $this->db->get();
}
//Aggregate_sales
public function aggregate_sales($filters){
$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));
$book_qry = $this->db->query("SELECT vehicle_id,vehicle_no,count(*) as count from sblt_customers inner join sblt_operation on sblt_customers.cust_id = sblt_operation.cust_id inner join sblt_operation_line on sblt_operation_line.operation_id = sblt_operation.operation_id left join sblt_vehicle on sblt_vehicle.vehicle_id = sblt_operation_line.op_vehicle_id where sblt_operation.cust_type = '1' and sblt_operation.status = '1' and DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date' GROUP BY op_vehicle_id");
$book_row = $book_qry->result_array();
return $book_row;
}
public function excel_aggregate_report($start_date,$end_date){
$this->db->select('operation.booking_id,operation.tour_no,vehicle_no,customer_name,customers.phone_number,cust_sts,operation.trip_days,operation.bus_count,operation.report_address,operation.reporting_time,operation.billing_name,first_name,operation.pax,operation.operation_sts,op_driver_name,op_driver_number,operation.avg_km,booking_type_name,frm.city_name as frm_city_name,to.city_name as to_city_name,operation.from_date,operation.to_date,trip_type_name,veh_type,accounts_sts,operation.trip_details,operation.booking_amount,operation.advance_amount,operation.tax_precentage,operation.discount_amount,operation.payment_type,operation.operation_type,operation.catering,operation.tp_info,IFNULL(sum(stay_tot_amt), 0) as stay_tot_amt,IFNULL(amount, 0) as catering_amount, trip_start_km,trip_end_km,extra_km,extra_amt,op_suspence_amt,diesel_amt,hire_charges,vendor_name,operation.balance_amount');
$this->db->from('operation');
$this->db->join('customers', 'operation.cust_id = customers.cust_id','left');
$this->db->join('accounts', 'accounts.cust_id = customers.cust_id','left');
$this->db->join('operation_line', 'operation_line.operation_id = operation.operation_id','left');
$this->db->join('trip_type', 'trip_type.trip_type_id = operation.trip_type','left');
$this->db->join('veh_type', 'veh_type.veh_type_id = operation_line.op_vehicle_type','left');
$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = operation.cust_type','left');
$this->db->join('employees', 'employees.id = operation.referral','left');
$this->db->join('people', 'people.person_id = employees.person_id','left');
$this->db->join('stay', 'stay.booking_id = operation.booking_id','left');
$this->db->join('diesel', 'diesel.diesel_op_id = operation.operation_id','left');
$this->db->join('catering_orders', 'catering_orders.booking_id = operation.booking_id','left');
$this->db->join('city frm', 'frm.city_id = operation.orgin','left');
$this->db->join('city to', 'to.city_id = operation.destination','left');
$this->db->join('vendor', 'vendor.vendor_id = vehicle.owned_by','left');
$this->db->group_start();
$this->db->where('DATE_FORMAT(sblt_operation.from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$this->db->group_end();
$this->db->where('operation.cust_type', 1);
$this->db->where('operation.status',1);
$this->db->group_by('operation.operation_id');
$excel = $this->db->get();
//echo $this->db->last_query(); die;
$result = $excel->result_array();
return $result;
}
public function get_value($ids,$cat){
$this->get_value($ids,$cat);
$ids = $row[0]['ids'];
$this->get_value($ids);
}
public function get_total_trip_amount($booking_id){
$booking_info = $this->get_load_data($booking_id);
$catering_order = $this->get_catering_order($booking_id);
$stay_info = $this->get_stay_list($booking_id);
$balance_log = $this->get_bal_log_amount($booking_id);
$extra_log = $this->get_extra_amount($booking_id);
foreach($booking_info as $booking){
$bus_count = $booking->bus_count;
$trip_days = $booking->trip_days;
$avg_km = $booking->avg_km;
$booking_amount = $booking->booking_amount;
$advance_amount = $booking->advance_amount;
$tax_precentage = $booking->tax_precentage;
$payment_type = $booking->payment_type;
$discount_amount = $booking->discount_amount;
$state = $booking->state;
$includ_gst = $booking->includ_gst;
$toll = $booking->toll;
$parking = $booking->parking;
$driver_batta = $booking->driver_batta;
$state_tax = $booking->state_tax;
}
$igst = 0;
$sgst = 0;
$cgst = 0;
if($tax_precentage > 0){
$igst = $tax_precentage;
$sgst = $tax_precentage /2;
$cgst = $tax_precentage /2;
}
$sub_total = 0;
foreach($catering_order as $order){
$amount = $order->amount;
$sub_total += $amount;
}
foreach($stay_info as $stay){
$stay_tot_amt = $stay->stay_tot_amt;
$sub_total += $stay_tot_amt;
}
$discount_total = $booking_amount - $discount_amount;
$sub_total = $discount_total + $sub_total+ (int)$extra_log->extra_amt;
$igst_amt = $sub_total * $igst/100;
$sgst_amt = $sub_total * $sgst/100;
$cgst_amt = $sub_total * $cgst/100;
$total_amt = $sub_total;
if($includ_gst === "1"){
if($state === "33"){
$total_amt = $sub_total + $cgst_amt + $sgst_amt;
$cgst_amt = number_format($cgst_amt,2);
$sgst_amt = number_format($sgst_amt,2);
}else{
$total_amt = $sub_total + $igst_amt;
$igst_amt = number_format($igst_amt,2);
}
}else{
if($toll === "1"){
$toll_amt = $this->get_suspence_amount_mode($booking_id,'Toll');
}
if($parking === "1"){
$parking_amt = $this->get_suspence_amount_mode($booking_id,'Parking');
}
if($driver_batta === "1"){
$driver_batta_amt = $this->get_suspence_amount_mode($booking_id,'driver_batta');
}
if($state_tax === "1"){
$state_tax_amt = $this->get_suspence_amount_mode($booking_id,'TAX');
}
$kickbacks_amt = $this->get_suspence_amount_mode($booking_id,'Kickbacks');
$extra = (int)$toll_amt + (int)$parking_amt + (int)$driver_batta_amt + (int)$state_tax_amt + (int)$kickbacks_amt;
$total_amt = $total_amt + $extra;
}
$advance = (int)$advance_amount + (int)$balance_log->bal_log_amt;
$balance_amount = (int)$total_amt - (int)$advance;
return $balance_amount;
}
public function get_suspence_amount_mode($booking_id,$mode){
$this->db->select('IFNULL(sum(suspence_amt), 0) as amt');
$this->db->from('operation');
$this->db->join('suspence', 'suspence.suspence_op_id = operation.operation_id','left');
$this->db->where('operation.status',1);
$this->db->where('operation.booking_id',$booking_id);
$this->db->where('suspence.suspence_info',$mode);
$result = $this->db->get()->row();
$suspence_amt = $result->amt;
return $suspence_amt;
}
public function get_load_data($booking_id){
$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name');
$this->db->from('operation');
$this->db->join('customers', 'customers.cust_id = operation.cust_id','left');
$this->db->join('trip_type', 'trip_type.trip_type_id = operation.trip_type','left');
$this->db->join('veh_type', 'veh_type.veh_type_id = operation.vehicle_type','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = operation.cust_type','left');
$this->db->join('city frm', 'frm.city_id = operation.orgin','left');
$this->db->join('city to', 'to.city_id = operation.destination','left');
$this->db->join('state', 'state_code = customers.state','left');
$this->db->where('booking_id',$booking_id);
return $this->db->get()->result();
}
public function get_catering_order($booking_id) {
$this->db->from('catering_orders');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('order_date', 'asc');
return $this->db->get()->result();
}
public function get_extra_amount($booking_id) {
$this->db->select('IFNULL(sum(extra_amt), 0) as extra_amt');
$this->db->from('operation');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
public function get_stay_list($booking_id) {
$this->db->from('stay');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('stay_date', 'asc');
return $this->db->get()->result();
}
public function get_bal_log_amount($booking_id) {
$this->db->select('IFNULL(sum(amount), 0) as bal_log_amt');
$this->db->from('balance_log');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
public function get_catering($booking_id) {
$this->db->select('IFNULL(sum(amount), 0) as amount');
$this->db->from('catering_orders');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('order_date', 'asc');
return $this->db->get()->row();
}
public function get_stay($booking_id) {
$this->db->select('IFNULL(sum(stay_tot_amt), 0) as stay_tot_amt');
$this->db->from('stay');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('stay_date', 'asc');
return $this->db->get()->row();
}
public function get_tax_percent($booking_id) {
$this->db->select('tax_precentage');
$this->db->from('operation');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
public function get_booking_amount($booking_id) {
$this->db->select('IFNULL(sum(booking_amount), 0) as booking_amount');
$this->db->from('operation');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
}
?>