public function getChDmLeads($userType,$id = '')
{
//echo $vert;die;
$sql = " Select ui.uid,ui.user_fname as exec_name,
ui.user_mobile as exec_mobile,l.lead_id,date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') AS lead_cre_date,
l.lead_cre_type, date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') AS last_modified_on, l.lead_status, l.cus_name,
l.cus_mobile, l.veh_reg_no, l.mfcw_id, l.agmt_no, l.final_agmt_no, l.branch,
l.branch_id,l.mmv,l.given_for,l.assigned_to,l.cc_remark,l.remarks,
date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') AS follow_up_date_time,
date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') AS inprgs_datetime,C.city_name,
l.lead_for,S.state_name,l.application_no,date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') AS first_contacted_date,
( CASE
WHEN A.user_type = '1' THEN 'Super Admin'
WHEN A.user_type = '3' THEN 'Executive'
WHEN A.user_type = '5' THEN 'ABM'
WHEN A.user_type = '16' THEN 'Mini User'
END ) AS user_type,A.username as lead_created_username,l.lead_type,
( CASE
WHEN l.workable_month = '1' THEN 'Workable This Month'
WHEN l.workable_month = '2' THEN 'Workable Next Month'
END ) AS workable_month
from custom_leads l
LEFT JOIN master_states S ON S.state_id = l.state_id
LEFT JOIN master_cities C ON C.city_id = l.city_id
LEFT JOIN user_login ul ON ul.id = l.assigned_to
LEFT JOIN user_login A ON A.id = l.lead_cre_user_id
LEFT JOIN user_info ui ON ui.uid = ul.id
where l.lead_status != '8' and " ;
$sql.="assigned_to IN
(Select id from user_login where user_type = '3' and reporting_head IN
(Select id from user_login where user_type = '5' and reporting_head IN
(Select id from user_login where user_type = '4'";
if ($id != '')
$sql .=" AND user_login.id = '".$id."' " ;
$sql.=" and reporting_head IN
(Select id from user_login where user_type = '6'";
if($id == '')
$sql.=" AND user_login.id = '".$this->session->userdata('id')."'";
$sql.=")
)
)
) ORDER BY l.lead_id desc";
$query = $this->db->query($sql);
//echo $this->db->last_query(); die;
return $query->result_array();
}
// All Conditions at one place in MySQL Query:
public function test_function(){
$this->load->dbutil();
$this->load->helper('file');
$this->load->helper('download');
$delimiter = ",";
$newline = "\r\n";
$todayDate = date('Y-m-d').' 23:59:59';
$sql = "SELECT l.lead_id AS 'Lead ID', l.any_id AS 'Any ID ',l.cus_name AS 'Customer Name',l.cus_mobile AS 'Customer Mobile',
l.veh_reg_no AS 'Vehicle Reg. No.',l.mmv AS 'Vehicle',
( CASE WHEN l.lead_status = '2' THEN 'Open Leads'
WHEN l.lead_status = '3' THEN 'Follow Up'
WHEN l.lead_status = '4' THEN 'No Response'
END) AS 'Status',
( CASE WHEN l.abm_lead_status = '1' THEN 'Lost'
WHEN l.abm_lead_status = '2' THEN 'Bad track Record'
WHEN l.abm_lead_status = '3' THEN 'Debarred Assets'
END) AS 'ABM Status',
( CASE WHEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') = date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') THEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y')
END ) AS 'Last Updated On',
ui.user_fname AS 'Executive Name',ui.user_mobile as 'Executive Mobile Number',
( CASE WHEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') = date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') THEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y')
END ) AS 'Created On',
l.lead_cre_type AS 'Created Type',l.given_for AS 'Given For',l.agmt_no AS 'Loan Agreement No.',
l.branch_code AS 'Branch Code',l.branch AS 'Branch',l.final_agmt_no AS 'Final Application No.',
l.cc_remark AS 'CC Initial Remarks',l.remarks AS 'Remarks',
( CASE WHEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') = date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') THEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y')
END ) AS 'Next Follow Up Date',
( CASE WHEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') = date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') THEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y')
END ) AS 'In Progress',
C.city_name AS 'City',
l.lead_for AS 'Lead For',
( CASE WHEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') = date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') THEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y')
END ) AS 'First Contacted Date',
l.lead_type AS 'Lead Type',
( CASE WHEN A.user_type = '1' THEN 'Super Admin'
WHEN A.user_type = '3' THEN 'Executive'
WHEN A.user_type = '5' THEN 'ABM'
WHEN A.user_type = '16' THEN 'Mini User'
END ) AS 'Created by',
A.username as 'Lead Created Username',
( CASE
WHEN l.workable_month = '1' THEN 'Workable This Month'
WHEN l.workable_month = '2' THEN 'Workable Next Month'
END ) AS 'Workable Month',l.mf_lead_id AS 'CRM ID',ul.sap_code AS 'Sap Code',
( CASE WHEN date_format(substr(l.do_date,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.do_date,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.do_date,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.do_date,1,10),'%d/%m/%Y') = date_format(substr(l.do_date,1,10),'%d/%m/%Y') THEN date_format(substr(l.do_date,1,10),'%d/%m/%Y')
END ) AS 'DO Date',
l.abm_cnc_remarks AS 'ABM Remarks'
FROM custom_leads as l
LEFT JOIN user_login as ul ON ul.id = l.assigned_to
LEFT JOIN user_info as ui ON ui.uid = l.assigned_to
LEFT JOIN user_login as A ON A.id = l.lead_cre_user_id
LEFT JOIN master_cities as C ON C.city_id = l.city_id
WHERE lead_cre_date <= '$todayDate'
AND l.lead_status != '8'
GROUP BY l.lead_id
ORDER BY l.lead_id desc";
$query = $this->db->query($sql);
$data = $this->dbutil->csv_from_result($query, $delimiter, $newline);
file_put_contents("excel-reports/Previous-Days-Leads-".date('d-m-Y').".csv",$data);
//force_download("Previousdays_Reports".date('d-m-Y').".csv", $data);
$url = base_url().'excel-reports/Previous-Days-Leads-'.date('d-m-Y').".csv";
$this->db->set('previous_days_url', $url);
$this->db->set('last_updated', date('Y-m-d H:i:s'));
$this->db->where('id', 1);
$this->db->update('download_lead_reports');
}
{
//echo $vert;die;
$sql = " Select ui.uid,ui.user_fname as exec_name,
ui.user_mobile as exec_mobile,l.lead_id,date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') AS lead_cre_date,
l.lead_cre_type, date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') AS last_modified_on, l.lead_status, l.cus_name,
l.cus_mobile, l.veh_reg_no, l.mfcw_id, l.agmt_no, l.final_agmt_no, l.branch,
l.branch_id,l.mmv,l.given_for,l.assigned_to,l.cc_remark,l.remarks,
date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') AS follow_up_date_time,
date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') AS inprgs_datetime,C.city_name,
l.lead_for,S.state_name,l.application_no,date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') AS first_contacted_date,
( CASE
WHEN A.user_type = '1' THEN 'Super Admin'
WHEN A.user_type = '3' THEN 'Executive'
WHEN A.user_type = '5' THEN 'ABM'
WHEN A.user_type = '16' THEN 'Mini User'
END ) AS user_type,A.username as lead_created_username,l.lead_type,
( CASE
WHEN l.workable_month = '1' THEN 'Workable This Month'
WHEN l.workable_month = '2' THEN 'Workable Next Month'
END ) AS workable_month
from custom_leads l
LEFT JOIN master_states S ON S.state_id = l.state_id
LEFT JOIN master_cities C ON C.city_id = l.city_id
LEFT JOIN user_login ul ON ul.id = l.assigned_to
LEFT JOIN user_login A ON A.id = l.lead_cre_user_id
LEFT JOIN user_info ui ON ui.uid = ul.id
where l.lead_status != '8' and " ;
$sql.="assigned_to IN
(Select id from user_login where user_type = '3' and reporting_head IN
(Select id from user_login where user_type = '5' and reporting_head IN
(Select id from user_login where user_type = '4'";
if ($id != '')
$sql .=" AND user_login.id = '".$id."' " ;
$sql.=" and reporting_head IN
(Select id from user_login where user_type = '6'";
if($id == '')
$sql.=" AND user_login.id = '".$this->session->userdata('id')."'";
$sql.=")
)
)
) ORDER BY l.lead_id desc";
$query = $this->db->query($sql);
//echo $this->db->last_query(); die;
return $query->result_array();
}
// All Conditions at one place in MySQL Query:
public function test_function(){
$this->load->dbutil();
$this->load->helper('file');
$this->load->helper('download');
$delimiter = ",";
$newline = "\r\n";
$todayDate = date('Y-m-d').' 23:59:59';
$sql = "SELECT l.lead_id AS 'Lead ID', l.any_id AS 'Any ID ',l.cus_name AS 'Customer Name',l.cus_mobile AS 'Customer Mobile',
l.veh_reg_no AS 'Vehicle Reg. No.',l.mmv AS 'Vehicle',
( CASE WHEN l.lead_status = '2' THEN 'Open Leads'
WHEN l.lead_status = '3' THEN 'Follow Up'
WHEN l.lead_status = '4' THEN 'No Response'
END) AS 'Status',
( CASE WHEN l.abm_lead_status = '1' THEN 'Lost'
WHEN l.abm_lead_status = '2' THEN 'Bad track Record'
WHEN l.abm_lead_status = '3' THEN 'Debarred Assets'
END) AS 'ABM Status',
( CASE WHEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') = date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y') THEN date_format(substr(l.last_modified_on,1,10),'%d/%m/%Y')
END ) AS 'Last Updated On',
ui.user_fname AS 'Executive Name',ui.user_mobile as 'Executive Mobile Number',
( CASE WHEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') = date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y') THEN date_format(substr(l.lead_cre_date,1,10),'%d/%m/%Y')
END ) AS 'Created On',
l.lead_cre_type AS 'Created Type',l.given_for AS 'Given For',l.agmt_no AS 'Loan Agreement No.',
l.branch_code AS 'Branch Code',l.branch AS 'Branch',l.final_agmt_no AS 'Final Application No.',
l.cc_remark AS 'CC Initial Remarks',l.remarks AS 'Remarks',
( CASE WHEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') = date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y') THEN date_format(substr(l.follow_up_date_time,1,10),'%d/%m/%Y')
END ) AS 'Next Follow Up Date',
( CASE WHEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') = date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y') THEN date_format(substr(l.inprgs_datetime,1,10),'%d/%m/%Y')
END ) AS 'In Progress',
C.city_name AS 'City',
l.lead_for AS 'Lead For',
( CASE WHEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') = date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y') THEN date_format(substr(l.first_contacted_date,1,10),'%d/%m/%Y')
END ) AS 'First Contacted Date',
l.lead_type AS 'Lead Type',
( CASE WHEN A.user_type = '1' THEN 'Super Admin'
WHEN A.user_type = '3' THEN 'Executive'
WHEN A.user_type = '5' THEN 'ABM'
WHEN A.user_type = '16' THEN 'Mini User'
END ) AS 'Created by',
A.username as 'Lead Created Username',
( CASE
WHEN l.workable_month = '1' THEN 'Workable This Month'
WHEN l.workable_month = '2' THEN 'Workable Next Month'
END ) AS 'Workable Month',l.mf_lead_id AS 'CRM ID',ul.sap_code AS 'Sap Code',
( CASE WHEN date_format(substr(l.do_date,1,10),'%d/%m/%Y') = '' THEN ''
WHEN date_format(substr(l.do_date,1,10),'%d/%m/%Y') = 'NULL' THEN ''
WHEN date_format(substr(l.do_date,1,10),'%d/%m/%Y') = '00/00/0000' THEN ''
WHEN date_format(substr(l.do_date,1,10),'%d/%m/%Y') = date_format(substr(l.do_date,1,10),'%d/%m/%Y') THEN date_format(substr(l.do_date,1,10),'%d/%m/%Y')
END ) AS 'DO Date',
l.abm_cnc_remarks AS 'ABM Remarks'
FROM custom_leads as l
LEFT JOIN user_login as ul ON ul.id = l.assigned_to
LEFT JOIN user_info as ui ON ui.uid = l.assigned_to
LEFT JOIN user_login as A ON A.id = l.lead_cre_user_id
LEFT JOIN master_cities as C ON C.city_id = l.city_id
WHERE lead_cre_date <= '$todayDate'
AND l.lead_status != '8'
GROUP BY l.lead_id
ORDER BY l.lead_id desc";
$query = $this->db->query($sql);
$data = $this->dbutil->csv_from_result($query, $delimiter, $newline);
file_put_contents("excel-reports/Previous-Days-Leads-".date('d-m-Y').".csv",$data);
//force_download("Previousdays_Reports".date('d-m-Y').".csv", $data);
$url = base_url().'excel-reports/Previous-Days-Leads-'.date('d-m-Y').".csv";
$this->db->set('previous_days_url', $url);
$this->db->set('last_updated', date('Y-m-d H:i:s'));
$this->db->where('id', 1);
$this->db->update('download_lead_reports');
}
