select DATE_FORMAT(A.BILLDATE, '%d-%m-%Y') as BILLDATE, sum(case when A.GSTP=0.0 then A.MATVAL else 0 end) as SALE0,
       sum(case when A.GSTP=5.0 then A.MATVAL else 0 end) as SALE5, 
        sum(case when A.GSTP=12.0 then A.MATVAL else 0 end) as SALE12, 
        sum(case when A.GSTP=18.0 then A.MATVAL else 0 end) as SALE18, 
        sum(case when A.GSTP=28.0 then A.MATVAL else 0 end) as SALE28, 
        sum(A.MATVAL) as TOTSALE, sum(A.CGST + A.SGST) as TOTGST, B.BLTYPE from salesdet A 
        left join (Select BILLNO, BLTYPE from sales) B on A.BILLNO = B.BILLNO
        where A.BILLNO <> '' AND (A.BILLDATE >= '2024-02-01'
         and A.BILLDATE <= '2024-02-31') AND B.BLTYPE = 1 group by A.BILLDATE order by A.BILLDATE


select DATE_FORMAT(A.BILLDATE, '%d-%m-%Y') as BILLDATE, sum(case when A.GSTP=0.0 then A.TAXABLEAMT else 0 end) as PURCHASE0,
       sum(case when A.GSTP=5.0 then A.TAXABLEAMT else 0 end) as PURCHASE5, 
        sum(case when A.GSTP=12.0 then A.TAXABLEAMT else 0 end) as PURCHASE12, 
        sum(case when A.GSTP=18.0 then A.TAXABLEAMT else 0 end) as PURCHASE18, 
        sum(case when A.GSTP=28.0 then A.TAXABLEAMT else 0 end) as PURCHASE28, 
        sum(A.TAXABLEAMT) as TOTPURCHASE, sum(GSTA) as TOTGST, B.BLTYPE from purchasedet A 
        left join (Select BILLNO, BLTYPE from purchase) B on A.BILLNO = B.BILLNO
        where A.BILLNO <> '' AND (A.BILLDATE >= '2024-02-01'
         and A.BILLDATE <= '2024-02-31') AND B.BLTYPE = 1 group by A.BILLDATE order by A.BILLDATE



select B.GSTIN as 'GSTIN/UIN of Recipient', B.CUSTNAME as 'Receiver Name', A.BILLNO as 'Invoice Number', 
A.BILLDATE as 'Invoice date', (B.MATVAL+B.GSTA) as 'Invoice Value', '19-West Bengal' as  'Place Of Supply', 'N' as 'Reverse Charge', 
'REGULAR B2B' AS 'Invoice Type', '' AS 'E-Commerce GSTIN', A.GSTP as 'Rate', '' as 'Applicable % of Tax Rate', 
sum(A.MATVAL) as 'Taxable Value',    '0.00' AS 'Cess Amount'
      from salesdet A, sales B 
     where A.BILLNO<> '0'  AND (A.BILLNO = B.BILLNO) AND (A.BILLDATE >= '2024-03-01' AND A.BILLDATE <= '2024-03-31') 
      AND (length(trim(B.GSTIN))=15 AND B.BLTYPE = 1
       AND (B.GSTIN regexp '19[A-Za-z][A-Za-z][A-Za-z][ABCFGHJLPTabcfghjlpt][A-Za-z][0-9][0-9][0-9][0-9][A-Za-z][0-9]Z[A-Za-z0-9]'))
        group by A.BILLNO, A.GSTP order by A.BILLDATE, A.BILLNO




select 'OE' as Type, '19-West Bengal' as 'Place Of Supply', D.GSTP as 'Rate', '' as 'Applicable % of Tax Rate', 
sum(D.TAXABLE) as 'Taxable Value', '0.00' as 'Cess Amount', '' as 'E-Commerce GSTIN'  from
      (select B.GSTIN, A.GSTP, B.BLTYPE, sum(A.MATVAL) as TAXABLE
      from salesdet A, sales B
      where(A.BILLNO <> '0' And (A.BILLNO = B.BILLNO)) AND B.BLTYPE = 1
      AND (A.BILLDATE >= '2024-03-01' AND A.BILLDATE <= '2024-03-31') AND (length(trim(B.GSTIN))<>15
      or B.GSTIN not regexp '19[A-Za-z][A-Za-z][A-Za-z][ABCFGHJLPTabcfghjlpt][A-Za-z][0-9][0-9][0-9][0-9][A-Za-z][0-9]Z[A-Za-z0-9]')
      group by A.BILLNO, A.GSTP order by A.BILLDATE, A.BILLNO) D
      group by GSTP order by GSTP
      
      

select if(HSN in ('','-','0'),'3924', A.HSN) as HSN,  ' ' as DESCRIPTION, 'NOS-NUMBERS' as UQC, sum(A.QTY) as 'Total Quantity',
sum(A.NETTOTAL) as 'Total Value', A.GSTP as 'Rate', sum(A.MATVAL) AS 'Taxable Value', 0 AS 'Integrated Tax Amount', 
sum(A.CGST) as 'Central Tax Amount', sum(A.SGST) as 'State/UT Tax Amount', '' AS CESSAMT,  B.ITEMNAME 
      from salesdet A 
    Left join (select ITEMCODE, ITEMNAME from itemsinf) B on A.ITEMCODE = B.ITEMCODE
    Left join (select BILLNO, BLTYPE from sales) C on A.BILLNO = C.BILLNO
    where A.BILLNO<>'0' AND C.BLTYPE = 1 AND (A.BILLDATE >= '2024-03-01' AND A.BILLDATE <= '2024-03-31') group by HSN order by HSN