drop  procedure crm_trn_spleadbanksummary;
DELIMITER $$
CREATE PROCEDURE `crm_trn_spleadbanksummary`()
BEGIN
SELECT a.leadbank_gid,b.leadbankcontact_gid,a.leadbank_pin, a.leadbank_id,a.approval_flag,m.country_name,b.mobile,a.leadbank_state,b.email,b.address1,b.address2,a.leadbank_city, a.leadbank_name,b.leadbankcontact_name,ifnull(a.customer_gid,' ') as customer_gid, 
                     a.remarks,CONCAT(IFNULL(b.leadbankcontact_name, ''),' / ',IFNULL(b.country_code1, ''), IFNULL('-', ''),IFNULL(b.mobile, ''),' / ',IFNULL(b.email, '')) AS contact_details,a.lead_status,  
                     concat(d.region_name,' / ',a.leadbank_city,' / ',a.leadbank_state) as region_name,  
                     concat(b.address1,b.address2) As address_details,a.customer_type,concat_ws('-',c.user_firstname,c.user_lastname) as created_by, 
                     date_format(a.created_date,'%d-%m-%Y') as created_date, 
                     CONCAT(CASE WHEN e.source_name IS NULL THEN '' ELSE e.source_name END,' / ',CASE WHEN l.categoryindustry_name IS NULL THEN '' ELSE l.categoryindustry_name END) AS source_name, 
                     ( g.assign_to )as assigned ,   
                     concat(j.user_firstname,' ',j.user_lastname) as assign_to,a.lead_type from crm_trn_tleadbank a   
                     left join crm_trn_tleadbankcontact b on a.leadbank_gid=b.leadbank_gid   
                     left join crm_mst_tregion d on a.leadbank_region=d.region_gid   
                     left join crm_mst_tsource e on a.source_gid=e.source_gid   
                     left join crm_mst_tcategoryindustry l on a.categoryindustry_gid = l.categoryindustry_gid   
                     left join hrm_mst_temployee k on a.created_by=k.employee_gid   
                     left join adm_mst_tuser c on  c.user_gid = k.user_gid   
                     left join crm_trn_tappointment g on a.leadbank_gid = g.leadbank_gid  
                     left join hrm_mst_temployee h on  g.assign_to = h.employee_gid   
                     left join adm_mst_tuser j on h.user_gid = j.user_gid  
                     left join adm_mst_tcountry m on a.leadbank_country = m.country_gid  
                     where a.leadbank_gid is not null
                     group by a.leadbank_gid Order by date(a.created_date) desc,a.created_date asc,a.leadbank_gid desc;
END $$
DELIMITER ;

#--------------------------21.08.2024 VISHWA---
drop  procedure crm_trn_spleadbanksummary;
DELIMITER $$
 
CREATE  PROCEDURE `crm_trn_spleadbanksummary`()
BEGIN
   SELECT 
    a.leadbank_gid,
    b.leadbankcontact_gid,
    a.leadbank_pin,
    a.leadbank_id,
    a.approval_flag,
    m.country_name,
    b.mobile,
    a.leadbank_state,
    b.email,
    b.address1,
    b.address2,
    a.leadbank_city,
    a.leadbank_name,
    b.leadbankcontact_name,
    IFNULL(a.customer_gid, ' ') AS customer_gid, 
    a.remarks,
    CONCAT(
        IFNULL(b.leadbankcontact_name, ''),
        ' / ',
        IFNULL(b.country_code1, ''),
        IFNULL('-', ''),
        IFNULL(b.mobile, ''),
        ' / ',
        IFNULL(b.email, '')
    ) AS contact_details,
    a.lead_status,
    concat_ws('/', d.region_name,d.city)AS region_name,
    CONCAT_ws(' ',b.address1, b.address2) AS address_details,
    a.customer_type,
    CONCAT_WS('-', c.user_firstname, c.user_lastname) AS created_by, 
    DATE_FORMAT(a.created_date, '%d-%m-%Y') AS created_date, 
    CASE 
        WHEN e.source_name IS NULL THEN '-' 
        ELSE e.source_name 
    END AS source_name, 
    g.assign_to AS assigned,   
    CONCAT(j.user_firstname, ' ', j.user_lastname) AS assign_to,
    a.lead_type 
FROM 
    crm_trn_tleadbank a   
LEFT JOIN 
    crm_trn_tleadbankcontact b ON a.leadbank_gid = b.leadbank_gid   
LEFT JOIN 
    crm_mst_tregion d ON a.leadbank_region = d.region_gid   
LEFT JOIN 
    crm_mst_tsource e ON a.source_gid = e.source_gid   
LEFT JOIN 
    crm_mst_tcategoryindustry l ON a.categoryindustry_gid = l.categoryindustry_gid   
LEFT JOIN 
    hrm_mst_temployee k ON a.created_by = k.employee_gid   
LEFT JOIN 
    adm_mst_tuser c ON c.user_gid = k.user_gid   
LEFT JOIN 
    crm_trn_tappointment g ON a.leadbank_gid = g.leadbank_gid  
LEFT JOIN 
    hrm_mst_temployee h ON g.assign_to = h.employee_gid   
LEFT JOIN 
    adm_mst_tuser j ON h.user_gid = j.user_gid  
LEFT JOIN 
    adm_mst_tcountry m ON a.leadbank_country = m.country_gid  
WHERE 
    a.leadbank_gid IS NOT NULL
GROUP BY 
    a.leadbank_gid 
ORDER BY 
    DATE(a.created_date) DESC,
    a.created_date ASC,
    a.leadbank_gid DESC;
 
END$$
 
DELIMITER ;
 
###################
drop  procedure crm_trn_spregisterleadsummary;
DELIMITER $$
 
CREATE PROCEDURE `crm_trn_spregisterleadsummary`(employee_id varchar(100))
BEGIN
SELECT a.leadbank_gid,b.leadbankcontact_gid, a.leadbank_id,a.approval_flag,  a.leadbank_name,b.leadbankcontact_name,ifnull(a.customer_gid,' ') as customer_gid, 
                a.remarks,concat(b.leadbankcontact_name,' / ',b.country_code1,'-',b.mobile,' / ',b.email)as contact_details,a.lead_status,  
    concat_ws('/', d.region_name,d.city)AS region_name,  
                concat(b.address1,b.address2) As address_details,a.customer_type,concat_ws('-',c.user_firstname,c.user_lastname) as created_by, 
                date_format(a.created_date,'%d-%m-%Y') as created_date,CASE 
        WHEN e.source_name IS NULL THEN '-' 
        ELSE e.source_name 
    END AS source_name,  ( g.assign_to )as assigned ,   
                concat(j.user_firstname,' ',j.user_lastname) as assign_to from crm_trn_tleadbank a   
                left join crm_trn_tleadbankcontact b on a.leadbank_gid=b.leadbank_gid   
                left join crm_mst_tregion d on a.leadbank_region=d.region_gid   
                left join crm_mst_tsource e on a.source_gid=e.source_gid   
                left join crm_mst_tcategoryindustry l on a.categoryindustry_gid = l.categoryindustry_gid   
                left join hrm_mst_temployee k on a.created_by=k.employee_gid   
                left join adm_mst_tuser c on  c.user_gid = k.user_gid   
                left join crm_trn_tlead2campaign g on a.leadbank_gid = g.leadbank_gid  
                left join hrm_mst_temployee h on  g.assign_to = h.employee_gid   
                left join adm_mst_tuser j on h.user_gid = j.user_gid  
                where a.main_branch ='Y' and a.customertype_gid is not null and a.created_by = employee_id
                group by a.leadbank_gid Order by date(a.created_date) desc,a.created_date asc,a.leadbank_gid desc;
 
END$$
 
DELIMITER ;
#--------------------------21.08.2024 VISHWA---
drop  procedure crm_trn_spleadbanksummary;
DELIMITER $$
 
CREATE  PROCEDURE `crm_trn_spleadbanksummary`()
BEGIN
   SELECT 
    a.leadbank_gid,
    b.leadbankcontact_gid,
    a.leadbank_pin,
    a.leadbank_id,
    a.approval_flag,
    m.country_name,
    b.mobile,
    a.leadbank_state,
    b.email,
    b.address1,
    b.address2,
    a.leadbank_city,
    a.leadbank_name,
    b.leadbankcontact_name,
    IFNULL(a.customer_gid, ' ') AS customer_gid, 
    a.remarks,
    CONCAT(
        IFNULL(b.leadbankcontact_name, ''),
        ' / ',
        IFNULL(b.country_code1, ''),
        IFNULL('-', ''),
        IFNULL(b.mobile, ''),
        ' / ',
        IFNULL(b.email, '')
    ) AS contact_details,
    a.lead_status,
    concat_ws('/', d.region_name,d.city)AS region_name,
    CONCAT_ws(' ',b.address1, b.address2) AS address_details,
    a.customer_type,
    CONCAT_WS('-', c.user_firstname, c.user_lastname) AS created_by, 
    DATE_FORMAT(a.created_date, '%d-%m-%Y') AS created_date, 
    CASE 
        WHEN e.source_name IS NULL THEN '-' 
        ELSE e.source_name 
    END AS source_name, 
    g.assign_to AS assigned,   
    CONCAT(j.user_firstname, ' ', j.user_lastname) AS assign_to,
    a.lead_type 
FROM 
    crm_trn_tleadbank a   
LEFT JOIN 
    crm_trn_tleadbankcontact b ON a.leadbank_gid = b.leadbank_gid   
LEFT JOIN 
    crm_mst_tregion d ON a.leadbank_region = d.region_gid   
LEFT JOIN 
    crm_mst_tsource e ON a.source_gid = e.source_gid   
LEFT JOIN 
    crm_mst_tcategoryindustry l ON a.categoryindustry_gid = l.categoryindustry_gid   
LEFT JOIN 
    hrm_mst_temployee k ON a.created_by = k.employee_gid   
LEFT JOIN 
    adm_mst_tuser c ON c.user_gid = k.user_gid   
LEFT JOIN 
    crm_trn_tappointment g ON a.leadbank_gid = g.leadbank_gid  
LEFT JOIN 
    hrm_mst_temployee h ON g.assign_to = h.employee_gid   
LEFT JOIN 
    adm_mst_tuser j ON h.user_gid = j.user_gid  
LEFT JOIN 
    adm_mst_tcountry m ON a.leadbank_country = m.country_gid  
WHERE 
    a.leadbank_gid IS NOT NULL
GROUP BY 
    a.leadbank_gid 
ORDER BY 
    DATE(a.created_date) DESC,
    a.created_date ASC,
    a.leadbank_gid DESC;
 
END$$
 
DELIMITER ;
 
###################
drop  procedure crm_trn_spregisterleadsummary;
DELIMITER $$
 
CREATE PROCEDURE `crm_trn_spregisterleadsummary`(employee_id varchar(100))
BEGIN
SELECT a.leadbank_gid,b.leadbankcontact_gid, a.leadbank_id,a.approval_flag,  a.leadbank_name,b.leadbankcontact_name,ifnull(a.customer_gid,' ') as customer_gid, 
                a.remarks,concat(b.leadbankcontact_name,' / ',b.country_code1,'-',b.mobile,' / ',b.email)as contact_details,a.lead_status,  
    concat_ws('/', d.region_name,d.city)AS region_name,  
                concat(b.address1,b.address2) As address_details,a.customer_type,concat_ws('-',c.user_firstname,c.user_lastname) as created_by, 
                date_format(a.created_date,'%d-%m-%Y') as created_date,CASE 
        WHEN e.source_name IS NULL THEN '-' 
        ELSE e.source_name 
    END AS source_name,  ( g.assign_to )as assigned ,   
                concat(j.user_firstname,' ',j.user_lastname) as assign_to from crm_trn_tleadbank a   
                left join crm_trn_tleadbankcontact b on a.leadbank_gid=b.leadbank_gid   
                left join crm_mst_tregion d on a.leadbank_region=d.region_gid   
                left join crm_mst_tsource e on a.source_gid=e.source_gid   
                left join crm_mst_tcategoryindustry l on a.categoryindustry_gid = l.categoryindustry_gid   
                left join hrm_mst_temployee k on a.created_by=k.employee_gid   
                left join adm_mst_tuser c on  c.user_gid = k.user_gid   
                left join crm_trn_tlead2campaign g on a.leadbank_gid = g.leadbank_gid  
                left join hrm_mst_temployee h on  g.assign_to = h.employee_gid   
                left join adm_mst_tuser j on h.user_gid = j.user_gid  
                where a.main_branch ='Y' and a.customertype_gid is not null and a.created_by = employee_id
                group by a.leadbank_gid Order by date(a.created_date) desc,a.created_date asc,a.leadbank_gid desc;
 
END$$
 
DELIMITER ;