Get all reporting head details just pass id only

SELECT

 a.id as exe_id,

    a.sap_code as exe_sapcode,

    a.username as exe_username,

    b.user_fname as exe_user_fname,

    b.user_mobile as exe_mobile,

    (CASE WHEN b.vertical = '0' THEN 'Used Car' WHEN b.vertical = '1' THEN 'Tractor' WHEN b.vertical = '2' THEN 'New Car' WHEN b.vertical = '3' THEN 'Mahindra New Car' WHEN b.vertical = '4' THEN '' WHEN b.vertical = '5' THEN 'DMT' WHEN b.vertical = '6' THEN '3W' WHEN b.vertical = '7' THEN 'CV' WHEN b.vertical = '9' THEN 'Manthan'

    END) AS 'exe_vertical',

    ( CASE

          WHEN a.user_status = '1' THEN 'Active'

          WHEN a.user_status = '2' THEN 'Inactive'

          WHEN a.user_status = '3' THEN 'Inactive'

    END ) AS 'exe_current_status',

    e2.id as abm_id,

    e2.sap_code as abm_sap_code,

    e2.username as abm_username,

    b1.user_fname as abm_user_fname,

    b1.user_mobile as abm_mobile,

    (CASE WHEN b1.vertical = '0' THEN 'Used Car' WHEN b1.vertical = '1' THEN 'Tractor' WHEN b1.vertical = '2' THEN 'New Car' WHEN b1.vertical = '3' THEN 'Mahindra New Car' WHEN b1.vertical = '4' THEN '' WHEN b1.vertical = '5' THEN 'DMT' WHEN b1.vertical = '6' THEN '3W' WHEN b1.vertical = '7' THEN 'CV' WHEN b1.vertical = '9' THEN 'Manthan'

    END) AS 'abm_vertical',

    ( CASE

          WHEN e2.user_status = '1' THEN 'Active'

          WHEN e2.user_status = '2' THEN 'Inactive'

          WHEN e2.user_status = '3' THEN 'Inactive'

    END ) AS 'abm_current_status',

    e3.id as dm_id,

    e3.sap_code as dm_sap_code,

    e3.username as dm_username,

    b2.user_fname as dm_user_fname,

    b2.user_mobile as dm_mobile,

    (CASE WHEN b2.vertical = '0' THEN 'Used Car' WHEN b2.vertical = '1' THEN 'Tractor' WHEN b2.vertical = '2' THEN 'New Car' WHEN b2.vertical = '3' THEN 'Mahindra New Car' WHEN b2.vertical = '4' THEN '' WHEN b2.vertical = '5' THEN 'DMT' WHEN b2.vertical = '6' THEN '3W' WHEN b2.vertical = '7' THEN 'CV' WHEN b2.vertical = '9' THEN 'Manthan'

    END) AS 'dm_vertical',

    ( CASE

          WHEN e3.user_status = '1' THEN 'Active'

          WHEN e3.user_status = '2' THEN 'Inactive'

          WHEN e3.user_status = '3' THEN 'Inactive'

    END ) AS 'dm_current_status',

    e4.id as ch_id,

    e4.sap_code as ch_sapcode,

    e4.username as ch_username,

    b3.user_fname as ch_user_fname,

    b3.user_mobile as ch_mobile,

    (CASE WHEN b3.vertical = '0' THEN 'Used Car' WHEN b3.vertical = '1' THEN 'Tractor' WHEN b3.vertical = '2' THEN 'New Car' WHEN b3.vertical = '3' THEN 'Mahindra New Car' WHEN b3.vertical = '4' THEN '' WHEN b3.vertical = '5' THEN 'DMT' WHEN b3.vertical = '6' THEN '3W' WHEN b3.vertical = '7' THEN 'CV' WHEN b3.vertical = '9' THEN 'Manthan'

    END) AS 'ch_vertical',

    ( CASE

          WHEN e4.user_status = '1' THEN 'Active'

          WHEN e4.user_status = '2' THEN 'Inactive'

          WHEN e4.user_status = '3' THEN 'Inactive'

    END ) AS 'ch_current_status'

FROM

    user_login a

        LEFT JOIN

    user_info b ON b.uid = a.id

        LEFT JOIN

    user_login e2 ON e2.id = a.reporting_head

    LEFT JOIN

    user_info b1 ON b1.uid = e2.id

        LEFT JOIN

    user_login e3 ON e3.id = e2.reporting_head

    LEFT JOIN

    user_info b2 ON b2.uid = e3.id

        LEFT JOIN

    user_login e4 ON e4.id = e3.reporting_head

    LEFT JOIN

    user_info b3 ON b3.uid = e4.id

WHERE

    a.user_type = '3' AND b.vertical='0'

************************************************************************

SELECT

 a.id as exe_id,

    a.sap_code as exe_sapcode,

    a.username as exe_username,

    b.user_fname as exe_user_fname,

    b.user_mobile as exe_mobile,

    ( CASE

          WHEN a.user_status = '1' THEN 'Active'

          WHEN a.user_status = '2' THEN 'Inactive'

          WHEN a.user_status = '3' THEN 'Inactive'

    END ) AS 'exe_current_status',

    e2.id as abm_id,

    e2.sap_code as abm_sap_code,

    e2.username as abm_username,

    b1.user_fname as abm_user_fname,

    b1.user_mobile as abm_mobile,

    ( CASE

          WHEN e2.user_status = '1' THEN 'Active'

          WHEN e2.user_status = '2' THEN 'Inactive'

          WHEN e2.user_status = '3' THEN 'Inactive'

    END ) AS 'abm_current_status',

    e3.id as dm_id,

    e3.sap_code as dm_sap_code,

    e3.username as dm_username,

    b2.user_fname as dm_user_fname,

    b2.user_mobile as dm_mobile,

    ( CASE

          WHEN e3.user_status = '1' THEN 'Active'

          WHEN e3.user_status = '2' THEN 'Inactive'

          WHEN e3.user_status = '3' THEN 'Inactive'

    END ) AS 'dm_current_status',

    e4.id as ch_id,

    e4.sap_code as ch_sapcode,

    e4.username as ch_username,

    b3.user_fname as ch_user_fname,

    b3.user_mobile as ch_mobile,

    ( CASE

          WHEN e4.user_status = '1' THEN 'Active'

          WHEN e4.user_status = '2' THEN 'Inactive'

          WHEN e4.user_status = '3' THEN 'Inactive'

    END ) AS 'ch_current_status'

FROM

    user_login a

        LEFT JOIN

    user_info b ON b.uid = a.id

        LEFT JOIN

    user_login e2 ON e2.id = a.reporting_head

    LEFT JOIN

    user_info b1 ON b1.uid = e2.id

        LEFT JOIN

    user_login e3 ON e3.id = e2.reporting_head

    LEFT JOIN

    user_info b2 ON b2.uid = e3.id

        LEFT JOIN

    user_login e4 ON e4.id = e3.reporting_head

    LEFT JOIN

    user_info b3 ON b3.uid = e4.id

WHERE

    a.user_type = '3' AND b.vertical='3'

**************************************

                                                                OR



SELECT
    a.id as exe_id,
    a.sap_code as exe_sapcode,
    a.username as exe_username,
    b.user_fname as exe_user_fname,
    b.user_mobile as exe_mobile,
    b.vertical as exe_vertical,
    e2.id as abm_id,
    e2.sap_code as abm_sap_code,
    e2.username as abm_username,
    b1.user_fname as abm_user_fname,
    b1.user_mobile as abm_mobile,
    e3.id as dm_id,
    e3.sap_code as dm_sap_code,
    e3.username as dm_username,
    b2.user_fname as dm_user_fname,
    b2.user_mobile as dm_mobile,
    e4.id as ch_id,
    e4.sap_code as ch_sapcode,
    e4.username as ch_username,
    b3.user_fname as ch_user_fname,
    b3.user_mobile as ch_mobile
FROM
    user_login a
        LEFT JOIN
    user_info b ON b.uid = a.id
        LEFT JOIN
    user_login e2 ON e2.id = a.reporting_head 
    LEFT JOIN
    user_info b1 ON b1.uid = e2.id
        LEFT JOIN
    user_login e3 ON e3.id = e2.reporting_head
    LEFT JOIN
    user_info b2 ON b2.uid = e3.id
        LEFT JOIN
    user_login e4 ON e4.id = e3.reporting_head
    LEFT JOIN
    user_info b3 ON b3.uid = e4.id
WHERE
    a.user_type = '3' AND a.id = '55'

**************************************************************

Get Specific user type reporting head details:

    SELECT a.id,a.username,b.user_fname,b.user_mobile,( CASE

              WHEN a.user_status = '1' THEN 'Active'

              WHEN a.user_status = '2' THEN 'Inactive'

              WHEN a.user_status = '3' THEN 'Inactive'

              END ) AS 'status', c.id AS 'reporting_head_id',c.username AS 'reporting_head_username',d.user_fname AS 'reporting_head_fname',d.user_mobile AS 'reporting_head_mobile',( CASE

              WHEN c.user_status = '1' THEN 'Active'

              WHEN c.user_status = '2' THEN 'Inactive'

              WHEN c.user_status = '3' THEN 'Inactive'

              END ) AS 'reporting_head_status' FROM `user_login` `a` 

              LEFT JOIN `user_info` `b` ON `a`.`id`=`b`.`uid`

              LEFT JOIN `user_login` `c` ON `c`.`id`=`a`.`reporting_head`

              LEFT JOIN `user_info` `d` ON `d`.`uid`=`c`.`id`

              WHERE a.user_type = '5' AND b.vertical='6' ORDER BY a.user_status ASC

Clear form fields cache with jQuery

Clear form fields cache with jQuery:
$("#edit-data-mdl").find('form').trigger('reset');

Auto close popup after update response:
setTimeout(function(){ $('#edit-data-mdl').modal('hide'); }, 4000);

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