CASE WHEN and SUB Query and DATE FORMAT - Code in one function With Excel CSV Download Report

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');
    }