---------customer summary-------------------------- DELIMITER $$ CREATE PROCEDURE `crm_mst_spcustomersummary`() BEGIN Select distinct UCASE(a.customer_id) as customer_id,y.user_firstname, a.customer_gid,z.pricesegment_name,a.customer_name, concat_ws( '/',c.customercontact_name,c.mobile,c.email) as contact_details, d.region_name,a.customer_state, (SELECT CONCAT(DATEDIFF(CURDATE(), MIN(created_date)), ' days') FROM crm_mst_tcustomer WHERE customer_gid = a.customer_gid) as customer_since, a.status as status from crm_mst_tcustomer a left join crm_mst_tcustomercontact c on a.customer_gid=c.customer_gid left join adm_mst_tuser y on a.salesperson_gid = y.user_gid left join crm_mst_tregion d on a.customer_region=d.region_gid left join smr_trn_tpricesegment z on a.pricesegment_gid = z.pricesegment_gid where c.customerbranch_name='H.Q' order by a.created_date DESC ; END$$ DELIMITER ; ------------------taxsegment-------------------------- DELIMITER $$ CREATE PROCEDURE `crm_mst_spcustomertaxsegment`() BEGIN SELECT a.customer_gid, h.user_firstname, v.region_name,a.customer_name,a.customer_country,u.country_name, a.customer_id, a.salesperson_gid,CONCAT(d.customercontact_name, '/', d.mobile, '/', d.email) AS contact_info, a.customer_country,a.pricesegment_gid, f.pricesegment_name, CONCAT(DATEDIFF(CURDATE(), first_created_date), ' days') AS customer_since, CONCAT(a.customer_address, ' ', a.customer_address2) AS address FROM crm_mst_tcustomer a LEFT JOIN crm_mst_tcustomercontact d ON a.customer_gid = d.customer_gid LEFT JOIN crm_mst_tregion v ON v.region_gid = a.customer_region LEFT JOIN adm_mst_tuser h ON h.user_gid = a.salesperson_gid LEFT JOIN smr_trn_tpricesegment f ON a.pricesegment_gid = f.pricesegment_gid left join adm_mst_tcountry u on u.country_gid = a.customer_country LEFT JOIN ( SELECT customer_gid, MIN(created_date) AS first_created_date FROM crm_mst_tcustomer GROUP BY customer_gid ) sub ON a.customer_gid = sub.customer_gid WHERE a.taxsegment_gid IS NULL OR a.taxsegment_gid = '' GROUP BY a.customer_gid; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `crm_mst_spcustomertaxsegmentassign`(in P_taxsegment_gid varchar(64)) BEGIN select a.customer_gid,h.user_firstname,v.region_name,a.customer_name,DATE_FORMAT(a.created_date,'%d-%m-%Y') as created_date,a.taxsegment_gid,a.customer_id, a.salesperson_gid,b.taxsegment_name,d.email,CONCAT(d.customercontact_name, '/', d.mobile, '/',d.email) AS contact_info, a.customer_country,u.country_name, a.customer_pin, a.pricesegment_gid,a.customer_region, a.customer_city, a.customer_state, concat(a.customer_address, ' ', a.customer_address2) as address, (SELECT CONCAT(DATEDIFF(CURDATE(), MIN(created_date)), ' days') FROM crm_mst_tcustomer WHERE customer_gid = a.customer_gid) as customer_since from crm_mst_tcustomer a left join crm_mst_tcustomercontact d on a.customer_gid = d.customer_gid left join acp_mst_ttaxsegment b on a.taxsegment_gid = b.taxsegment_gid left join acp_mst_ttaxsegment2customer c on a.customer_gid = c.customer_gid left join adm_mst_tuser h on h.user_gid = a.salesperson_gid left join adm_mst_tcountry u on u.country_gid = a.customer_country left join crm_mst_tregion v on v.region_gid = a.customer_region where a.taxsegment_gid= P_taxsegment_gid group by a.customer_gid ; END$$ DELIMITER ; ---------------------product search---------------------------------- DELIMITER $$ CREATE PROCEDURE `pmr_mst_spproductsearch`(in sqltype varchar(45),in product_gid varchar(45),in customer_gid varchar(45)) BEGIN case sqltype when 'product' then SELECT a.product_name, CASE WHEN a.customerproduct_code IS NULL THEN a.product_code ELSE a.customerproduct_code END AS product_code, a.product_gid, a.product_desc,a.mrp_price, a.cost_price, b.productuom_gid, d.producttype_name, b.productuom_name, c.productgroup_name, c.productgroup_gid, a.productuom_gid, d.producttype_gid FROM pmr_mst_tproduct a LEFT JOIN pmr_mst_tproductuom b ON a.productuom_gid = b.productuom_gid LEFT JOIN pmr_mst_tproductgroup c ON a.productgroup_gid = c.productgroup_gid LEFT JOIN pmr_mst_tproducttype d ON d.producttype_gid = a.producttype_gid WHERE 1=1; when 'customer' then SELECT f.taxsegment_gid, d.taxsegment_gid,a.product_gid, a.product_name,concat(b.tax_prefix) as tax_prefix, e.taxsegment_name, d.tax_name, d.tax_gid,d.tax_percentage , d.tax_amount, a.mrp_price, a.cost_price FROM acp_mst_ttaxsegment2product d LEFT JOIN acp_mst_ttaxsegment e ON e.taxsegment_gid = d.taxsegment_gid LEFT JOIN crm_mst_tcustomer f ON f.taxsegment_gid = e.taxsegment_gid left join acp_mst_ttax b on b.tax_gid=d.tax_gid LEFT JOIN pmr_mst_tproduct a ON a.product_gid = d.product_gid WHERE a.product_gid=product_gid and f.customer_gid=customer_gid; when 'pricesegmentcustomer' then SELECT distinct f.taxsegment_gid, d.taxsegment_gid,a.product_gid, a.product_name,concat(b.tax_prefix) as tax_prefix, e.taxsegment_name, d.tax_name, d.tax_gid,d.tax_percentage , d.tax_amount, a.mrp_price,h.product_price, a.cost_price FROM acp_mst_ttaxsegment2product d LEFT JOIN acp_mst_ttaxsegment e ON e.taxsegment_gid = d.taxsegment_gid LEFT JOIN crm_mst_tcustomer f ON f.taxsegment_gid = e.taxsegment_gid LEFT JOIN pmr_mst_tproduct a ON a.product_gid = d.product_gid LEFT JOIN smr_trn_tpricesegment2product h ON h.product_gid = a.product_gid LEFT JOIN smr_trn_tpricesegment2customer x ON x.pricesegment_gid = h.pricesegment_gid left join acp_mst_ttax b on b.tax_gid=d.tax_gid WHERE h.product_gid=product_gid and x.customer_gid=customer_gid and h.pricesegment_gid = x.pricesegment_gid; when 'vendor' then SELECT f.taxsegment_gid, d.taxsegment_gid,a.product_gid, a.product_name,concat(b.tax_prefix ) as tax_prefix, e.taxsegment_name, d.tax_name, d.tax_gid,d.tax_percentage , d.tax_amount, a.mrp_price, a.cost_price FROM acp_mst_ttaxsegment2product d left join acp_mst_ttax b on b.tax_gid=d.tax_gid LEFT JOIN acp_mst_ttaxsegment e ON e.taxsegment_gid = d.taxsegment_gid LEFT JOIN acp_mst_tvendor f ON f.taxsegment_gid = e.taxsegment_gid LEFT JOIN pmr_mst_tproduct a ON a.product_gid = d.product_gid WHERE a.product_gid=product_gid and f.vendor_gid=customer_gid; end case; END$$ DELIMITER ; -------------------------------sales dashboard------------------------------------ DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `salesdashboard`() BEGIN select count(CASE WHEN DATE(a.salesorder_date) = CURDATE() THEN ifnull(a.salesorder_gid,0) END) AS Today_SalesOrder, count(CASE WHEN DATE(b.directorder_date) = CURDATE() THEN ifnull(b.salesorder_gid,0) END) AS Today_DespatchOrder, count(CASE WHEN DATE(c.invoice_date) = CURDATE() THEN IFNULL(c.invoice_gid, 0) END) AS Today_Invoice, count(CASE WHEN DATE(d.payment_date) = CURDATE() THEN IFNULL(d.payment_gid, 0) END) AS Today_Payment, SUM(CASE WHEN DATE(d.payment_date) = CURDATE() THEN IFNULL(d.total_amount, 0)else 0 END) AS Today_PaymentAmount, SUM(CASE WHEN DATE(c.invoice_date) = CURDATE() THEN IFNULL(c.invoice_amount, 0)else 0 END) AS Today_InvoiceAmount , count(CASE WHEN DATE(a.salesorder_date) = CURDATE() - INTERVAL 1 DAY THEN ifnull(a.salesorder_gid,0)END) AS Yesterday_SalesOrder, count(CASE WHEN DATE(b.directorder_date) = CURDATE() - INTERVAL 1 DAY THEN IFNULL(b.salesorder_gid, 0) END) AS Yesterday_DespatchOrder, count(CASE WHEN DATE(c.invoice_date) = CURDATE() - INTERVAL 1 DAY THEN IFNULL(c.invoice_gid, 0)END) AS Yesterday_Invoice, count(CASE WHEN DATE(d.payment_date) = CURDATE() - INTERVAL 1 DAY THEN IFNULL(d.payment_gid, 0) END) AS Yesterday_Payment, SUM(CASE WHEN DATE(d.payment_date) = CURDATE() - INTERVAL 1 DAY THEN IFNULL(d.total_amount, 0) ELSE 0 END) AS Yesterday_PaymentAmount, SUM(CASE WHEN DATE(c.invoice_date) = CURDATE() - INTERVAL 1 DAY THEN IFNULL(c.invoice_amount, 0) ELSE 0 END) AS Yesterday_InvoiceAmount, count(CASE WHEN WEEK(a.salesorder_date, 1) = WEEK(CURDATE(), 1) AND YEAR(a.salesorder_date) = YEAR(CURDATE()) THEN ifnull(a.salesorder_gid,0) END) AS CurrentWeek_SalesOrder, count(CASE WHEN WEEK(b.directorder_date, 1) = WEEK(CURDATE(), 1) AND YEAR(b.directorder_date) = YEAR(CURDATE()) THEN IFNULL(b.salesorder_gid, 0) END) AS CurrentWeek_DespatchOrder, count(CASE WHEN WEEK(c.invoice_date, 1) = WEEK(CURDATE(), 1) AND YEAR(c.invoice_date) = YEAR(CURDATE()) THEN IFNULL(c.invoice_gid, 0) END) AS CurrentWeek_Invoice, count(CASE WHEN WEEK(d.payment_date, 1) = WEEK(CURDATE(), 1) AND YEAR(d.payment_date) = YEAR(CURDATE()) THEN IFNULL(d.payment_gid, 0) END) AS CurrentWeek_Payment, SUM(CASE WHEN WEEK(d.payment_date, 1) = WEEK(CURDATE(), 1) AND YEAR(d.payment_date) = YEAR(CURDATE()) THEN IFNULL(d.total_amount, 0) ELSE 0 END) AS CurrentWeek_PaymentAmount, SUM(CASE WHEN WEEK(c.invoice_date, 1) = WEEK(CURDATE(), 1) AND YEAR(c.invoice_date) = YEAR(CURDATE()) THEN IFNULL(c.invoice_amount, 0) ELSE 0 END) AS CurrentWeek_InvoiceAmount, count(CASE WHEN WEEK(a.salesorder_date, 1) = WEEK(CURDATE(), 1) - 1 AND YEAR(a.salesorder_date) = YEAR(CURDATE()) THEN ifnull(a.salesorder_gid,0)END) AS LastWeek_SalesOrder, count(CASE WHEN WEEK(b.directorder_date, 1) = WEEK(CURDATE(), 1) - 1 AND YEAR(b.directorder_date) = YEAR(CURDATE()) THEN IFNULL(b.salesorder_gid, 0) END) AS LastWeek_DespatchOrder, count(CASE WHEN WEEK(c.invoice_date, 1) = WEEK(CURDATE(), 1) - 1 AND YEAR(c.invoice_date) = YEAR(CURDATE()) THEN IFNULL(c.invoice_gid, 0) END) AS LastWeek_Invoice, count(CASE WHEN WEEK(d.payment_date, 1) = WEEK(CURDATE(), 1) - 1 AND YEAR(d.payment_date) = YEAR(CURDATE()) THEN IFNULL(d.payment_gid, 0) END) AS LastWeek_Payment, SUM(CASE WHEN WEEK(d.payment_date, 1) = WEEK(CURDATE(), 1) - 1 AND YEAR(d.payment_date) = YEAR(CURDATE()) THEN IFNULL(d.total_amount, 0) ELSE 0 END) AS LastWeek_PaymentAmount, SUM(CASE WHEN WEEK(c.invoice_date, 1) = WEEK(CURDATE(), 1) - 1 AND YEAR(c.invoice_date) = YEAR(CURDATE()) THEN IFNULL(c.invoice_amount, 0) ELSE 0 END) AS LastWeek_InvoiceAmount, count(CASE WHEN MONTH(a.salesorder_date) = MONTH(CURDATE()) AND YEAR(a.salesorder_date) = YEAR(CURDATE()) THEN ifnull(a.salesorder_gid,0)END) AS CurrentMonth_SalesOrder, count(CASE WHEN MONTH(b.directorder_date) = MONTH(CURDATE()) AND YEAR(b.directorder_date) = YEAR(CURDATE()) THEN IFNULL(b.salesorder_gid, 0) END) AS CurrentMonth_DespatchOrder, count(CASE WHEN MONTH(c.invoice_date) = MONTH(CURDATE()) AND YEAR(c.invoice_date) = YEAR(CURDATE()) THEN IFNULL(c.invoice_gid, 0) END) AS CurrentMonth_Invoice, count(CASE WHEN MONTH(d.payment_date) = MONTH(CURDATE()) AND YEAR(d.payment_date) = YEAR(CURDATE()) THEN IFNULL(d.payment_gid, 0) END) AS CurrentMonth_Payment, SUM(CASE WHEN MONTH(d.payment_date) = MONTH(CURDATE()) AND YEAR(d.payment_date) = YEAR(CURDATE()) THEN IFNULL(d.total_amount, 0) ELSE 0 END) AS CurrentMonth_PaymentAmount, SUM(CASE WHEN MONTH(c.invoice_date) = MONTH(CURDATE()) AND YEAR(c.invoice_date) = YEAR(CURDATE()) THEN IFNULL(c.invoice_amount, 0) ELSE 0 END) AS CurrentMonth_InvoiceAmount, count(CASE WHEN MONTH(a.salesorder_date) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(a.salesorder_date) = YEAR(CURDATE() - INTERVAL 1 MONTH)THEN ifnull(a.salesorder_gid,0) END) AS PreviousMonth_SalesOrder, count(CASE WHEN MONTH(b.directorder_date) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(b.directorder_date) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN IFNULL(b.salesorder_gid, 0) END) AS PreviousMonth_DespatchOrder, count(CASE WHEN MONTH(c.invoice_date) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(c.invoice_date) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN IFNULL(c.invoice_gid , 0) END) AS PreviousMonth_Invoice, count(CASE WHEN MONTH(d.payment_date) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(d.payment_date) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN IFNULL(d.payment_gid , 0) END) AS PreviousMonth_Payment, SUM(CASE WHEN MONTH(d.payment_date) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(d.payment_date) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN IFNULL(d.total_amount, 0) ELSE 0 END) AS PreviousMonth_PaymentAmount, SUM(CASE WHEN MONTH(c.invoice_date) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(c.invoice_date) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN IFNULL(c.invoice_amount, 0) ELSE 0 END) AS PreviousMonth_InvoiceAmount , count(CASE WHEN YEAR(a.salesorder_date) = YEAR(CURDATE()) THEN ifnull(a.salesorder_gid,0) END) AS CurrentYear_SalesOrder, count(CASE WHEN YEAR(b.directorder_date) = YEAR(CURDATE()) THEN IFNULL(b.directorder_date, 0) END) AS CurrentYear_DespatchOrder, count(CASE WHEN YEAR(c.invoice_date) = YEAR(CURDATE()) THEN IFNULL(c.invoice_gid , 0) END) AS CurrentYear_Invoice, count(CASE WHEN YEAR(d.payment_date) = YEAR(CURDATE()) THEN IFNULL(d.payment_gid , 0) END) AS CurrentYear_Payment, SUM(CASE WHEN YEAR(d.payment_date) = YEAR(CURDATE()) THEN IFNULL(d.total_amount, 0) ELSE 0 END) AS CurrentYear_PaymentAmount, SUM(CASE WHEN YEAR(c.invoice_date) = YEAR(CURDATE()) THEN IFNULL(c.invoice_amount, 0) ELSE 0 END) AS CurrentYear_InvoiceAmount, count(CASE WHEN YEAR(a.salesorder_date) = YEAR(CURDATE() - INTERVAL 1 YEAR) THEN ifnull(a.salesorder_gid,0)END) AS PerivousYear_SalesOrder, count(CASE WHEN YEAR(b.directorder_date) = YEAR(CURDATE() - INTERVAL 1 YEAR) THEN IFNULL(b.directorder_date, 0) END) AS PerivousYear_DespatchOrder, count(CASE WHEN YEAR(c.invoice_date) = YEAR(CURDATE() - INTERVAL 1 YEAR) THEN IFNULL(c.invoice_gid , 0) END) AS PerivousYear_Invoice, count(CASE WHEN YEAR(d.payment_date) = YEAR(CURDATE() - INTERVAL 1 YEAR) THEN IFNULL(d.payment_gid , 0) END) AS PerivousYear_Payment, SUM(CASE WHEN YEAR(d.payment_date) = YEAR(CURDATE() - INTERVAL 1 YEAR) THEN IFNULL(d.total_amount, 0) ELSE 0 END) AS PerivousYear_PaymentAmount, SUM(CASE WHEN YEAR(c.invoice_date) = YEAR(CURDATE() - INTERVAL 1 YEAR) THEN IFNULL(c.invoice_amount, 0) ELSE 0 END) AS PerivousYear_InvoiceAmount from smr_trn_tsalesorder a left join smr_trn_tdeliveryorder b on a.salesorder_gid= b.salesorder_gid left join rbl_trn_tinvoice c on a.salesorder_gid=c.invoice_reference left join rbl_trn_tpayment d on c.invoice_gid =d.invoice_gid ; END$$ DELIMITER ; ---------------------------invoice summary--------------------------------------- DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `smr_trn_invoicesummary`( in lscurrency varchar(64)) BEGIN select distinct a.invoice_gid,x.company_code, s.salesorder_status, case when a.invoice_reference like '%AREF%' then j.agreement_referencenumber else cast(concat(s.so_referenceno1,if(s.so_referencenumber<>'',concat(' ',' | ',' ',s.so_referencenumber),'') ) as char) end as so_referencenumber, a.invoice_refno, CASE WHEN a.irncancel_date IS NOT NULL THEN 'IRN Cancelled' WHEN a.creditnote_status = 'Y' THEN 'Credit Noted' ELSE a.invoice_status END AS status, a.mail_status,a.customer_gid,a.invoice_date,a.invoice_reference,a.additionalcharges_amount,a.discount_amount, CASE when a.payment_flag <> 'PY Pending' then a.payment_flag else a.invoice_flag end as 'overall_status', a.mail_status, a.payment_flag, format(a.initialinvoice_amount,2) as initialinvoice_amount,s.salesorder_gid,a.invoice_status,a.invoice_flag, case when a.irn is not null then 'IRN Generated' else 'IRN Pending' end as 'irn_status',case when a.irncancel_date is not null then 'IRN Cancelled' else '' end as 'irncancel_status', format(a.invoice_amount,2) as invoice_amount,c.customer_code, case when a.customer_contactnumber is null then concat(a.customer_contactperson,' / ',a.customer_contactnumber) else concat(a.customer_contactperson,' / ',a.customer_contactnumber, if(m.email='',' ',concat(' / ',m.email))) end as customer_contactperson, case when a.currency_code = lscurrency then concat(c.customer_id,' / ',c.customer_name) when a.currency_code is null then concat(c.customer_id,' / ', c.customer_name) when a.currency_code is not null and a.currency_code <> lscurrency then concat(c.customer_name,' / ',h.country) end as customer_name,a.currency_code, a.customer_contactnumber as mobile,a.invoice_from,i.directorder_gid,a.progressive_invoice from rbl_trn_tinvoice a left join rbl_trn_tinvoicedtl b on a.invoice_gid = b.invoice_gid left join crm_mst_tcustomer c on a.customer_gid = c.customer_gid left join crm_mst_tcustomercontact m on a.customer_gid = m.customer_gid left join crm_trn_tcurrencyexchange h on a.currency_code = h.currency_code left join adm_mst_tcompany x on x.country_gid = h.country_gid left join smr_trn_tsalesorder s on a.invoice_reference = s.salesorder_gid left join crm_trn_tagreement j on j.agreement_gid = a.invoice_reference left join smr_trn_tdeliveryorder i on s.salesorder_gid=i.salesorder_gid where a.invoice_type<>'Opening Invoice' and a.invoice_status<>'Invoice Cancelled' order by date(a.invoice_date) desc,a.invoice_date asc, a.invoice_gid desc; END$$ DELIMITER ; --------------------------------price segment-------------------------------------- DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `smr_trn_sppriceassignsummary`(in pricesegment_gid varchar(64)) BEGIN SELECT DISTINCT UCASE(a.customer_id) AS customer_id,a.customer_gid,z.pricesegment_name,a.customer_name, CONCAT(c.customercontact_name, ' / ', c.mobile, ' / ', c.email) AS contact_details, h.user_firstname as salespersonname, d.region_name,y.country_name,(SELECT CONCAT(DATEDIFF(CURDATE(), MIN(created_date)), ' days') FROM crm_mst_tcustomer WHERE customer_gid = a.customer_gid) as customer_since FROM crm_mst_tcustomer a LEFT JOIN crm_mst_tcustomercontact c ON a.customer_gid = c.customer_gid LEFT JOIN crm_mst_tregion d ON a.customer_region = d.region_gid LEFT JOIN adm_mst_tcountry y ON a.customer_country = y.country_gid LEFT JOIN smr_trn_tpricesegment z ON a.pricesegment_gid = z.pricesegment_gid left join adm_mst_tuser h on h.user_gid = a.salesperson_gid where a.status = 'Active' and a.pricesegment_gid=pricesegment_gid; END$$ DELIMITER ; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `smr_trn_sppriceproductsummary`(in sqltype varchar(45), in P_pricesegment_gid varchar(64)) BEGIN case sqltype when 'unassignsummary' then SELECT a.product_name,a.mrp_price,a.product_code,a.product_gid, d.productgroup_name, a.customerproduct_code , a.product_desc, c.productuom_name FROM pmr_mst_tproduct a LEFT JOIN pmr_mst_tproductuom c ON a.productuom_gid = c.productuom_gid LEFT JOIN pmr_mst_tproductgroup d ON a.productgroup_gid = d.productgroup_gid left join smr_trn_tpricesegment2product h on h.product_gid = a.product_gid where a.product_gid NOT in (select product_gid from smr_trn_tpricesegment2product where pricesegment_gid = P_pricesegment_gid); when 'assignsummary' then SELECT a.product_name,a.mrp_price,a.product_code,h.pricesegment2product_gid , a.product_gid, d.productgroup_name, a.customerproduct_code , a.product_desc, c.productuom_name FROM pmr_mst_tproduct a LEFT JOIN pmr_mst_tproductuom c ON a.productuom_gid = c.productuom_gid LEFT JOIN pmr_mst_tproductgroup d ON a.productgroup_gid = d.productgroup_gid left join smr_trn_tpricesegment2product h on h.product_gid = a.product_gid where h.pricesegment_gid = P_pricesegment_gid; end case; END$$ DELIMITER ; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `smr_trn_sppriceunassignsummary`() BEGIN SELECT DISTINCT UCASE(a.customer_id) AS customer_id,a.customer_gid,a.customer_name, CONCAT(c.customercontact_name, ' / ', c.mobile, ' / ', c.email) AS contact_details, h.user_firstname as salespersonname, d.region_name,y.country_name, (SELECT CONCAT(DATEDIFF(CURDATE(), MIN(created_date)), ' days') FROM crm_mst_tcustomer WHERE customer_gid = a.customer_gid) as customer_since FROM crm_mst_tcustomer a LEFT JOIN crm_mst_tcustomercontact c ON a.customer_gid = c.customer_gid LEFT JOIN crm_mst_tregion d ON a.customer_region = d.region_gid LEFT JOIN adm_mst_tcountry y ON a.customer_country = y.country_gid left join adm_mst_tuser h on h.user_gid = a.salesperson_gid where a.status = 'Active' and a.pricesegment_gid is null; END$$ DELIMITER ; 20/08/2024-Maxson DELIMITER $$ CREATE PROCEDURE `smr_trn_addreceiptsummary` () BEGIN SELECT a.customer_gid,COUNT(a.invoice_gid) AS invoice_count,CONCAT_WS('/', b.customer_id, b.customer_name) AS customer_name,FORMAT(SUM(a.invoice_amount), 2) AS total_invoice_amount, FORMAT(SUM(a.payment_amount), 2) AS total_payment_amount, FORMAT(SUM(a.invoice_amount - a.payment_amount), 2) AS total_outstanding, GROUP_CONCAT(a.invoice_refno ORDER BY a.invoice_gid DESC SEPARATOR ', ') AS invoice_references, CONCAT_ws( '/',c.customercontact_name, c.mobile, c.email) AS contact_info FROM rbl_trn_tinvoice a LEFT JOIN crm_mst_tcustomer b ON b.customer_gid = a.customer_gid LEFT JOIN crm_mst_tcustomercontact c ON c.customer_gid = a.customer_gid WHERE a.invoice_amount > a.payment_amount AND a.invoice_date <= curdate() AND a.invoice_flag IN ('Invoice Approved') AND a.invoice_status NOT IN ('Payment done') GROUP BY b.customer_gid ORDER BY a.customer_gid DESC; END$$ DELIMITER ; 29/08/2024-Maxson DELIMITER $$ CREATE PROCEDURE `smr_trn_addreceiptsummary` () BEGIN SELECT a.customer_gid,COUNT(a.invoice_gid) AS invoice_count,CONCAT_WS('/', b.customer_id, b.customer_name) AS customer_name,FORMAT(SUM(a.invoice_amount), 2) AS total_invoice_amount, FORMAT(SUM(a.payment_amount), 2) AS total_payment_amount, FORMAT(SUM(a.invoice_amount - a.payment_amount), 2) AS total_outstanding, GROUP_CONCAT(a.invoice_refno ORDER BY a.invoice_gid DESC SEPARATOR ', ') AS invoice_references, CONCAT_ws( '/',c.customercontact_name, c.mobile, c.email) AS contact_info FROM rbl_trn_tinvoice a LEFT JOIN crm_mst_tcustomer b ON b.customer_gid = a.customer_gid LEFT JOIN crm_mst_tcustomercontact c ON c.customer_gid = a.customer_gid WHERE a.invoice_amount > a.payment_amount AND a.invoice_date <= curdate() AND a.invoice_flag IN ('Invoice Approved') AND a.invoice_status NOT IN ('Payment done') GROUP BY b.customer_gid ORDER BY a.customer_gid DESC; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `pmr_mst_spproductsearch`(in sqltype varchar(45),in product_gid varchar(45),in customer_gid varchar(45)) BEGIN case sqltype when 'product' then SELECT a.product_name, CASE WHEN a.customerproduct_code IS NULL THEN a.product_code ELSE a.customerproduct_code END AS product_code, a.product_gid, a.product_desc,a.mrp_price, a.cost_price, b.productuom_gid, d.producttype_name, b.productuom_name, c.productgroup_name, c.productgroup_gid, a.productuom_gid, d.producttype_gid FROM pmr_mst_tproduct a LEFT JOIN pmr_mst_tproductuom b ON a.productuom_gid = b.productuom_gid LEFT JOIN pmr_mst_tproductgroup c ON a.productgroup_gid = c.productgroup_gid LEFT JOIN pmr_mst_tproducttype d ON d.producttype_gid = a.producttype_gid WHERE 1=1; when 'customer' then SELECT f.taxsegment_gid, d.taxsegment_gid,a.product_gid, a.product_name,concat(b.tax_prefix) as tax_prefix, e.taxsegment_name, d.tax_name, d.tax_gid,d.tax_percentage , d.tax_amount, a.mrp_price,x.exchange_rate, a.cost_price FROM acp_mst_ttaxsegment2product d LEFT JOIN acp_mst_ttaxsegment e ON e.taxsegment_gid = d.taxsegment_gid LEFT JOIN crm_mst_tcustomer f ON f.taxsegment_gid = e.taxsegment_gid LEFT JOIN crm_trn_tcurrencyexchange x ON x.currencyexchange_gid = f.currency_gid left join acp_mst_ttax b on b.tax_gid=d.tax_gid LEFT JOIN pmr_mst_tproduct a ON a.product_gid = d.product_gid WHERE a.product_gid=product_gid and f.customer_gid=customer_gid; when 'pricesegmentcustomer' then SELECT distinct f.taxsegment_gid, d.taxsegment_gid,a.product_gid, a.product_name,concat(b.tax_prefix) as tax_prefix, e.taxsegment_name, d.tax_name, d.tax_gid,d.tax_percentage , d.tax_amount, a.mrp_price,h.product_price, a.cost_price FROM acp_mst_ttaxsegment2product d LEFT JOIN acp_mst_ttaxsegment e ON e.taxsegment_gid = d.taxsegment_gid LEFT JOIN crm_mst_tcustomer f ON f.taxsegment_gid = e.taxsegment_gid LEFT JOIN pmr_mst_tproduct a ON a.product_gid = d.product_gid LEFT JOIN smr_trn_tpricesegment2product h ON h.product_gid = a.product_gid LEFT JOIN smr_trn_tpricesegment2customer x ON x.pricesegment_gid = h.pricesegment_gid left join acp_mst_ttax b on b.tax_gid=d.tax_gid WHERE h.product_gid=product_gid and x.customer_gid=customer_gid and h.pricesegment_gid = x.pricesegment_gid; when 'vendor' then SELECT f.taxsegment_gid, d.taxsegment_gid,a.product_gid, a.product_name,concat(b.tax_prefix ) as tax_prefix, e.taxsegment_name, d.tax_name, d.tax_gid,d.tax_percentage , d.tax_amount, a.mrp_price, a.cost_price FROM acp_mst_ttaxsegment2product d left join acp_mst_ttax b on b.tax_gid=d.tax_gid LEFT JOIN acp_mst_ttaxsegment e ON e.taxsegment_gid = d.taxsegment_gid LEFT JOIN acp_mst_tvendor f ON f.taxsegment_gid = e.taxsegment_gid LEFT JOIN pmr_mst_tproduct a ON a.product_gid = d.product_gid WHERE a.product_gid=product_gid and f.vendor_gid=customer_gid; end case; END$$ DELIMITER ; 17/09/2024--Hariharan drop procedure smr_trn_invoicesummary; DELIMITER $$ CREATE PROCEDURE `smr_trn_invoicesummary`( in lscurrency varchar(64)) BEGIN select distinct a.invoice_gid,x.company_code, s.salesorder_status, a.irn,x.einvoice_flag,case when a.invoice_reference like '%AREF%' then j.agreement_referencenumber else cast(concat(s.so_referenceno1,if(s.so_referencenumber<>'',concat(' ',' | ',' ',s.so_referencenumber),'') ) as char) end as so_referencenumber, a.invoice_refno, CASE WHEN a.irncancel_date IS NOT NULL THEN 'IRN Cancelled' WHEN a.creditnote_status = 'Y' THEN 'Credit Noted' ELSE a.invoice_status END AS status, a.mail_status,a.customer_gid,a.invoice_date,a.invoice_reference,a.additionalcharges_amount,a.discount_amount, CASE when a.payment_flag <> 'PY Pending' then a.payment_flag else a.invoice_flag end as 'overall_status', a.mail_status, a.payment_flag, format(a.initialinvoice_amount,2) as initialinvoice_amount,s.salesorder_gid,a.invoice_status,a.invoice_flag, case when a.irn is not null then 'IRN Generated' else 'IRN Pending' end as 'irn_status',case when a.irncancel_date is not null then 'IRN Cancelled' else '' end as 'irncancel_status', format(a.invoice_amount,2) as invoice_amount,c.customer_code, case when a.customer_contactnumber is null then concat(a.customer_contactperson,' / ',a.customer_contactnumber) else concat(a.customer_contactperson,' / ',a.customer_contactnumber, if(m.email='',' ',concat(' / ',m.email))) end as customer_contactperson, case when a.currency_code = lscurrency then concat(c.customer_id,' / ',c.customer_name) when a.currency_code is null then concat(c.customer_id,' / ', c.customer_name) when a.currency_code is not null and a.currency_code <> lscurrency then concat(c.customer_name,' / ',h.country) end as customer_name,a.currency_code, a.customer_contactnumber as mobile,a.invoice_from,i.directorder_gid,a.progressive_invoice from rbl_trn_tinvoice a left join rbl_trn_tinvoicedtl b on a.invoice_gid = b.invoice_gid left join crm_mst_tcustomer c on a.customer_gid = c.customer_gid left join crm_mst_tcustomercontact m on a.customer_gid = m.customer_gid left join crm_trn_tcurrencyexchange h on a.currency_code = h.currency_code left join adm_mst_tcompany x on 1=1 left join smr_trn_tsalesorder s on a.invoice_reference = s.salesorder_gid left join crm_trn_tagreement j on j.agreement_gid = a.invoice_reference left join smr_trn_tdeliveryorder i on s.salesorder_gid=i.salesorder_gid where a.invoice_type<>'Opening Invoice' and a.invoice_status<>'Invoice Cancelled' group by a.invoice_gid order by date(a.invoice_date) desc,a.invoice_date asc, a.invoice_gid desc; END$$ DELIMITER ; 19/09/2024--Hariharan drop procedure smr_trn_invoicesummary; DELIMITER $$ CREATE PROCEDURE `smr_trn_invoicesummary`( in lscurrency varchar(64)) BEGIN select distinct a.invoice_gid,x.company_code, s.salesorder_status, a.irn,x.einvoice_flag,case when a.invoice_reference like '%AREF%' then j.agreement_referencenumber else cast(concat(s.so_referenceno1,if(s.so_referencenumber<>'',concat(' ',' | ',' ',s.so_referencenumber),'') ) as char) end as so_referencenumber, a.invoice_refno, CASE WHEN a.irncancel_date IS NOT NULL THEN 'IRN Cancelled' WHEN a.creditnote_status = 'Y' THEN 'Credit Noted' ELSE a.invoice_status END AS status, a.mail_status,a.customer_gid,a.invoice_date,a.invoice_reference,a.additionalcharges_amount,a.discount_amount, CASE when a.payment_flag <> 'PY Pending' then a.payment_flag else a.invoice_flag end as 'overall_status', a.mail_status, a.payment_flag, format(a.initialinvoice_amount,2) as initialinvoice_amount,s.salesorder_gid,a.invoice_status,a.invoice_flag, case when a.irn is not null then 'IRN Generated' else 'IRN Pending' end as 'irn_status',case when a.irncancel_date is not null then 'IRN Cancelled' else '' end as 'irncancel_status', format(a.invoice_amount,2) as invoice_amount,c.customer_code, case when a.customer_contactnumber is null then concat(a.customer_contactperson,' / ',a.customer_contactnumber) else concat(a.customer_contactperson,' / ',a.customer_contactnumber, if(m.email='',' ',concat(' / ',m.email))) end as customer_contactperson, case when a.currency_code = lscurrency then concat(c.customer_id,' / ',c.customer_name) when a.currency_code is null then concat(c.customer_id,' / ', c.customer_name) when a.currency_code is not null and a.currency_code <> lscurrency then concat(c.customer_name,' / ',h.country) end as customer_name,a.currency_code, a.customer_contactnumber as mobile,a.invoice_from,i.directorder_gid,a.progressive_invoice from rbl_trn_tinvoice a left join rbl_trn_tinvoicedtl b on a.invoice_gid = b.invoice_gid left join crm_mst_tcustomer c on a.customer_gid = c.customer_gid left join crm_mst_tcustomercontact m on a.customer_gid = m.customer_gid left join crm_trn_tcurrencyexchange h on a.currency_code = h.currency_code left join adm_mst_tcompany x on 1=1 left join smr_trn_tsalesorder s on a.invoice_reference = s.salesorder_gid left join crm_trn_tagreement j on j.agreement_gid = a.invoice_reference left join smr_trn_tdeliveryorder i on s.salesorder_gid=i.salesorder_gid where a.invoice_type<>'Opening Invoice' and a.invoice_status<>'Invoice Cancelled' group by a.invoice_gid order by date(a.invoice_date) desc,a.invoice_date asc, a.invoice_gid desc; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE adm_mst_spSequenceGid(IN pModule_Code VARCHAR(1024)) BEGIN DECLARE lsfinyear VARCHAR(500); DECLARE lsSeqNo INT; DECLARE lsTempGid VARCHAR(100); DECLARE lsSeqflag VARCHAR(1) DEFAULT ''; DECLARE lsSequenceCurval VARCHAR(45) DEFAULT ''; DECLARE lsSequenceFormat VARCHAR(45) DEFAULT ''; START TRANSACTION; SELECT YEAR(fyear_start) INTO lsfinyear FROM adm_mst_tyearendactivities WHERE CURDATE() BETWEEN fyear_start AND fyear_end; SET lsfinyear = IF(lsfinyear IS NULL OR lsfinyear = '', (SELECT YEAR(fyear_start) FROM adm_mst_tyearendactivities ORDER BY finyear_gid DESC LIMIT 1), lsfinyear); SELECT sequence_flag, sequence_curval, sequence_format INTO lsSeqflag, lsSequenceCurval, lsSequenceFormat FROM adm_mst_tsequence WHERE sequence_code = pModule_Code AND finyear = lsfinyear LIMIT 1; SET lsSeqNo = lsSequenceCurval + 1; SET lsTempGid = CASE WHEN lsSeqflag = 'Y' THEN CONCAT(pModule_Code, LPAD(lsSeqNo, lsSequenceFormat, '0')) WHEN lsSeqflag = 'N' THEN CONCAT(pModule_Code, DATE_FORMAT(CURDATE(), '%Y%m%d'), lsSeqNo) ELSE 'E' END; UPDATE adm_mst_tsequence SET sequence_curval = lsSeqNo WHERE sequence_code = pModule_Code AND finyear = lsfinyear; COMMIT; SELECT lsTempGid; END$$ DELIMITER ;