''two digits must
''date in dd-MMM-yyyy format
''b2cl state code missing
''update default state codes
''

'***GSTR1 Section wise
'----------Created by ajay on 27-Aug-2017
Sub Main( )
Dim  rs, i
dim GSTR1Folder
Dim STC(100)

'Initializing the GSTR1 folder (on desktop)
GSTR1Folder = SpecialFolderPath("GSTR1_" & month(local.dtfrom.value) & "_" & year(local.dtfrom.value))
'Initialize StateCodeArray
STC(1) = "01-Jammu & Kashmir": STC(2) = "02-Himachal Pradesh": STC(3) = "03-Punjab": STC(4) = "04-Chandigarh": STC(5) =  "05-Uttarakhand": STC(6) = "06-Haryana": STC(7) = "07-Delhi"
stc(8) =  "08-Rajasthan": STC(9) =  "09-Uttar Pradesh": STC(10) = "10-Bihar": STC(11) = "11-Sikkim": STC(12) = "12-Arunachal Pradesh": STC(13) =  "13-Nagaland": STC(14) =  "14-Manipur"
stc(15) =  "15-Mizoram": STC(16) =  "16-Tripura": STC(17) =  "17-Meghalaya": STC(18) = "18-Assam": STC(19) =  "19-West Bengal": STC(20) = "20-Jharkhand": STC(21) = "21-Odisha": STC(22) = "22-Chhattisgarh"
stc(23) = "23-Madhya Pradesh": STC(24) = "24-Gujarat": STC(25) = "25-Daman & Diu": STC(26) = "26-Dadra & Nagar Haveli": STC(27) = "27-Maharashtra": STC(29) = "29-Karnataka": STC(30) = "30-Goa"
stc(31) = "31-Lakshdweep": STC(32) = "32-Kerala": STC(33) = "33-Tamil Nadu": STC(34) = "34-Pondicherry": STC(35) = "35-Andaman & Nicobar Islands": STC(36) = "36-Telengana": STC(37) = "37-Andhra Pradesh": STC(97) = "97-Other Territory"


Set FSO = CreateObject("Scripting.FileSystemObject") 

With MetaScript

.exec "update s set s.gotirn=1, s.irnno=i.IRN from SaleHead as s, GIRN_Out as i where s.BillNo = i.DocNo and i.irn<>'' and isnull(s.gotirn,0)=0", .getdatafile




   set rs = .sumrs("select cu.name as [Customer name],s.billno as [Bill No], s.billdate as [Bill Date], cu.state , cu.statecode as POS " & _
      " ,s.payrupees,  sum(c.Taxable + (c.taxable * (sgstper+cgstper)/100)) as [Total Value], sum(c.Taxable) as [Taxable Value], " & _
      " sum(case when cu.purchaseat<=2 then c.taxable * cgstper/100 else 0 end) as [CGST Amount]," & _
      " sum(case when cu.purchaseat<=2 then c.taxable * sgstper/100 else 0 end) as [SGST Amount]," & _
      " sum(case when cu.purchaseat>2 then c.taxable * sgstper*2/100 else 0 end) as [IGST Amount]" & _
      ", s.irnno  from product as p, CashItems as c , SaleHead as s, Series as se, (Select name, state, statecode, Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)=0 then 1  else 0 end as DealerType from  Customer ) as cu   " & _
      " where saletype=serieskey and productkey = c.productid  AND s.GSTBILL<>0 and customerid=customerkey and SaleKey = SaleId and s.billdate between '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "' and se.hideseries=0 " & _
"  Group by s.irnno, cu.name ,s.billno , s.billdate , cu.state , cu.statecode  ,s.payrupees", .getdatafile)
   
   if not rs.eof then
      filename = GSTR1Folder & "\AllDetailsTotals.xlsx"
      local.DumpRsToExcel Rs,filename
   end if

   
   set rs = .sumrs("select cu.name as [Customer name],s.billno as [Bill No], s.billdate as [Bill Date], cu.state , cu.statecode as POS, p.HSN , p.name as [Product name]" & _
      " ,c.quantity as Qty, c.unitprice as [Rate per Unit], 'Units' as UQC, c.quantity as [Total Quantity] , c.Taxable + (c.taxable * (sgstper+cgstper)/100) as [Total Value], c.Taxable as [Taxable Value], " & _
      " case when cu.purchaseat<=2 then sgstper else 0 end as [CGST Rate],case when cu.purchaseat<=2 then c.taxable * sgstper/100 else 0 end as [CGST Amount]," & _
      " case when cu.purchaseat<=2 then sgstper else 0 end as [SGST Rate],case when cu.purchaseat<=2 then c.taxable * sgstper/100 else 0 end as [SGST Amount]," & _
      " case when cu.purchaseat>2 then sgstper+cgstper else 0 end as [IGST Rate],case when cu.purchaseat>2 then c.taxable * sgstper*2/100 else 0 end as [IGST Amount]," & _
      " 0 as Cess, s.irnno from product as p, CashItems as c , SaleHead as s, series as se, (Select name, state, statecode, Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)=0 then 1  else 0 end as DealerType from  Customer ) as cu   " & _
      " where Saletype = serieskey and productkey = c.productid  AND s.GSTBILL<>0 and customerid=customerkey and SaleKey = SaleId and s.billdate between '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "' and se.hideseries=0 ", .getdatafile)
   
   if not rs.eof then
      filename = GSTR1Folder & "\AllDetails.xlsx"
      local.DumpRsToExcel Rs,filename
   end if
   
   
   set rs = .sumrs("select Code, name, idarea, cell, gstin from Customer " & _
      " where len(GSTIN)=15 and not ( isnumeric(SUBSTRING(gstin,1,2))=0 or isnumeric(SUBSTRING(gstin,3,1))=1 or isnumeric(SUBSTRING(gstin,4,1))=1 or isnumeric(SUBSTRING(gstin,5,1))=1 or isnumeric(SUBSTRING(gstin,6,1))=1 or isnumeric(SUBSTRING(gstin,7,1))=1 or isnumeric(SUBSTRING(gstin,8,1))=0 or isnumeric(SUBSTRING(gstin,9,1))=1 or isnumeric(SUBSTRING(gstin,10,1))=0 or isnumeric(SUBSTRING(gstin,11,1))=0 or isnumeric(SUBSTRING(gstin,12,1))=1 )" & _
      " and customerkey in (Select customerid from salehead where billdate between '" & local.dtfrom.tag & "' and '" & local.dtfrom.tag & "' union all " & _
      " Select customerprofileid from purchasehead where billdate between '" & local.dtfrom.tag & "' and '" & local.dtfrom.tag & "')"  , .getdatafile)
   if not rs.eof then
      msgbox "Found customer profiles with improper GSTINs", vbcritical
      filename = GSTR1Folder & "\ImproperGSTIN.xlsx"
      local.DumpRsToExcel Rs,filename
   end if
   
   
   
   'If Views Exist then delete them
   Set rs = .sumrs("select isnull(count(*),0) as ct  from sysobjects where name = 'VS_SaleGSTDetails'",.getdatafile)
   if rs.fields("ct")>0 then .exec "Drop view VS_SaleGSTDetails", .getdatafile
   
   'If Views Exist then delete them
   Set rs = .sumrs("select isnull(count(*),0) as ct  from sysobjects where name = 'VS_SaleReturnGSTDetails'",.getdatafile)
   if rs.fields("ct")>0 then .exec "Drop view VS_SaleReturnGSTDetails", .getdatafile
   

   Set rs = .sumrs("select isnull(count(*),0) as ct  from sysobjects where name = 'VS_PurGSTDetails'",.getdatafile)
   if rs.fields("ct")>0 then .exec "Drop view VS_PurGSTDetails", .getdatafile
   
   'CheckStateCode
   UpdateStateCode
   
   'Creating the views
   .exec "create view VS_SaleGSTDetails as  select cu.PurchaseAt, StateCode, saleid, DealerType , sgstper * 2 as TaxPer, round(sum(case when cu.purchaseat<3 then  taxable else 0 end),2) as LSG,  round(sum(case when cu.purchaseat<3 then  taxable * sgstper*2/100 else 0 end  ),2) as LST   , round(sum(case when cu.purchaseat<3 then  0 else taxable end),2) as ISG, round(sum(case when cu.purchaseat<3 then  0 else taxable * sgstper*2/100  end ),2) as IST  " & _
      " from CashItems as c , SaleHead as s, Series as se, (Select Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)<>0 then 1  else 0 end as DealerType, Statecode from  Customer ) as cu    " & _
      " where customerid=customerkey  AND s.GSTBILL<>0 and s.saletype = se.serieskey and se.hideseries=0 and SaleKey = SaleId and c.mark<>'Rpl'  " & _
      " group by cu.PurchaseAt, StateCode, sgstper , SaleId , DealerType ", .getdatafile
   
   'Creating the views
   .exec "create view VS_SaleReturnGSTDetails as  select cu.PurchaseAt, StateCode, saleid, DealerType , sgstper * 2 as TaxPer, round(sum(case when cu.purchaseat<3 then  taxable else 0 end),2) as LSG,  round(sum(case when cu.purchaseat<3 then  taxable * sgstper*2/100 else 0 end  ),2) as LST   , round(sum(case when cu.purchaseat<3 then  0 else taxable end),2) as ISG, round(sum(case when cu.purchaseat<3 then  0 else taxable * sgstper*2/100  end ),2) as IST  " & _
      " from CashItems as c , SaleHead as s,  (Select Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)<>0 then 1  else 0 end as DealerType, Statecode from  Customer ) as cu    " & _
      " where customerid=customerkey  AND s.GSTBILL<>0 and s.saletype = 9999 and SaleKey = SaleId and c.mark<>'Rpl'  " & _
      " group by cu.PurchaseAt, StateCode, sgstper , SaleId , DealerType ", .getdatafile
   
   .exec "create view VS_PurGSTDetails as " & _
      " select PurchaseId, DealerType, sgstper * 2 as TaxPer, round(sum(case when cu.purchaseat<3 then  taxgross else 0 end),2) as LPG,  round(sum(case when cu.purchaseat<3 then  taxgross * sgstper*2/100 else 0 end  ) ,2) as LPT  , round(sum(case when cu.purchaseat<3 then  0 else TaxGross  end),2) as IPG, round(sum(case when cu.purchaseat<3 then  0 else taxgross * sgstper*2/100  end ),2) as IPT  " & _
      " from PurchaseItems  as c , purchaseHead as s, (Select Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)<>0 then 1  else 0 end as DealerType from  Customer ) as cu   " & _
      " where customerProfileid=customerkey and purchaseKey = purchaseId and c.comment<>'Rpl' and Transferout=0  " & _
      " group by sgstper, purchaseid, dealertype ", .getdatafile
   
   
   'Docs
   Set Rs = .SUMRs("select cashprefix as Prefix, MIN(billcount) as StartNo, MAX(billcount) as EndNo, MAX(billcount) - MIN(billcount)+1 as Diff, COUNT(*) as Bills, MAX(billcount) - MIN(billcount) - count(*) +1 as CancelledBills from SaleHead , Series " & _
      " where SaleType = SeriesKey and HideSeries =0 and CashCredit = 0 and BillDate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "' " & _
      " group by CashPrefix " & _
      " union all  " & _
      " select creditprefix , MIN(billcount), MAX(billcount), MAX(billcount) - MIN(billcount) + 1 as NumbersConsumed, COUNT(*) as Bills, MAX(billcount) - MIN(billcount)-count(*) +1 from SaleHead , Series  " & _
      " where SaleType = SeriesKey and HideSeries =0 and CashCredit <>0 and BillDate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "' " & _
      " group by CreditPrefix  ", .getdatafile)
   FileName=  GSTR1Folder & "\Docs.xlsx"
   
   local.dumprstoexcel rs, filename
   
   
   'GSTR1 SRB2B details
   Set Rs = .SUMRs("select c.name, c.GSTIn, s.billno, s.billdate, s.payrupees,c.StateCode,'N' as RC,'Regular' as IT,'' as EC, TaxPer, sum(IsG+Lsg) as TotalTaxableSale , s.irnno from VS_SaleGSTDetails as v, Salehead as s , customer as c" & _
      " Where c.customerkey = s.customerid and salekey = saleid and s.saletype=9999  AND s.GSTBILL<>0 and v.dealertype in (0,1)   and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'   " & _
      " Group by c.name, taxper, s.billno, s.billdate, s.payrupees, c.GSTIN, C.StateCode, s.irnno ", .getdatafile)
   FileName=  GSTR1Folder & "\SR_B2b_Details.xlsx"
   
   local.dumprstoexcel rs, filename

   'GSTR1 B2B details
   Set Rs = .SUMRs("select c.name, c.GSTIn, s.billno, s.billdate, s.payrupees,c.StateCode,'N' as RC,'Regular' as IT,'' as EC, TaxPer, sum(IsG+Lsg) as TotalTaxableSale , s.irnno from VS_SaleGSTDetails as v, Salehead as s   , Series as se , customer as c" & _
      " Where c.customerkey = s.customerid and salekey = saleid and s.saletype=se.serieskey  AND s.GSTBILL<>0 and v.dealertype in (0,1) and se.hideseries=0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'   " & _
      " Group by c.name, taxper, s.billno, s.billdate, s.payrupees, c.GSTIN, C.StateCode, s.irnno ", .getdatafile)
   FileName=  GSTR1Folder & "\B2bDetails.xlsx"
   
   local.dumprstoexcel rs, filename

   ''GSTR1 B2B Section
   Set Rs = .SUMRs("select c.GSTIn, s.billno, s.billdate, s.payrupees,c.StateCode,'N' as RC,'Regular' as IT,'' as EC, TaxPer, sum(IsG+Lsg) as TotalTaxableSale , s.irnno from VS_SaleGSTDetails as v, Salehead as s   , Series as se , customer as c" & _
      " Where c.customerkey = s.customerid and salekey = saleid and s.saletype=se.serieskey and se.hideseries=0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  and isnull(s.irnno,'')<>'' " & _
      " Group by s.irnno, taxper, s.billno, s.billdate, s.payrupees, c.GSTIN, C.StateCode having sum(IsG+Lsg)>0 ", .getdatafile)
   
   FileName=  GSTR1Folder & "\B2bEInv.csv"
   Set FrontPg = FSO.CreateTextFile(FileName, True) 
   
   FrontPg.Write "GSTIN/UIN of Recipient,Invoice Number,Invoice date,Invoice Value,Place Of Supply,Reverse Charge,Invoice Type,E-Commerce GSTIN,Rate,Taxable Value,Cess Amount, Irn"
   frontpg.write vbnewline 
   
   dim RX
   set RX = new RegExp
   RX.Pattern = "\r|\n|,|"""
   
   
   do while not rs.eof
      Separator = ""
      billno=rs.fields("Billno")
      if RX.Test(billno) then billno = """" & Replace(billno, """", """""") & """"
      
      FrontPg.Write Rs.fields("GSTIN") & "," & billno & "," & .setr(rs.fields("BillDate"),11,"dd-MMM-yyyy") & ","  & ltrim(.setr(rs.fields("Payrupees"),11,"#0.00")) & "," & _
         STc(int(rs.fields("StateCode"))) & ",N,Regular,," &  rs.fields("TaxPer") & "," &  ltrim(.setr(rs.fields("TotalTaxableSale"),11,"#0.00")) & "," & "," &  rs.fields("IRNNo")  & vbNewLine
      
      
      Rs.MoveNext	
      IF Not Rs.Eof then
         Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
      End If 
   LOOP 
   FrontPg.Close
   
   ''GSTR1 B2B Section
   Set Rs = .SUMRs("select c.GSTIn, s.billno, s.billdate, s.payrupees,c.StateCode,'N' as RC,'Regular' as IT,'' as EC, TaxPer, sum(IsG+Lsg) as TotalTaxableSale , c.pin from VS_SaleGSTDetails as v, Salehead as s   , Series as se , customer as c" & _
      " Where c.customerkey = s.customerid and salekey = saleid and s.saletype=se.serieskey  AND s.GSTBILL<>0 and v.dealertype in (0,1)  and se.hideseries=0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  and isnull(s.irnno,'')='' " & _
      " Group by taxper, s.billno, s.billdate, s.payrupees, c.GSTIN, C.StateCode, c.pin having sum(IsG+Lsg)>0 ", .getdatafile)
   
   FileName=  GSTR1Folder & "\B2bNonEInv.csv"
   Set FrontPg = FSO.CreateTextFile(FileName, True) 
   
   FrontPg.Write "GSTIN/UIN of Recipient,Invoice Number,Invoice date,Invoice Value,Place Of Supply,Reverse Charge,Invoice Type,E-Commerce GSTIN,Rate,Taxable Value,Cess Amount, PIN"
   frontpg.write vbnewline 
   

   set RX = new RegExp
   RX.Pattern = "\r|\n|,|"""
   
   
   do while not rs.eof
      Separator = ""
      billno=rs.fields("Billno")
      if RX.Test(billno) then billno = """" & Replace(billno, """", """""") & """"
      
      FrontPg.Write Rs.fields("GSTIN") & "," & billno & "," & .setr(rs.fields("BillDate"),11,"dd-MMM-yyyy") & ","  & ltrim(.setr(rs.fields("Payrupees"),11,"#0.00")) & "," & _
         STc(int(rs.fields("StateCode"))) & ",N,Regular,," &  rs.fields("TaxPer") & "," &  ltrim(.setr(rs.fields("TotalTaxableSale"),11,"#0.00")) & ", ," & rs.fields("pin")  & vbNewLine
      
      
      Rs.MoveNext	
      IF Not Rs.Eof then
         Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
      End If 
   LOOP 
   FrontPg.Close
   


   
   ''GSTR1 B2B Section
   Set Rs = .SUMRs("select c.GSTIn, s.billno, s.billdate, s.payrupees,c.StateCode,'N' as RC,'Regular' as IT,'' as EC, TaxPer, sum(IsG+Lsg) as TotalTaxableSale from VS_SaleGSTDetails as v, Salehead as s   , Series as se , customer as c" & _
      " Where c.customerkey = s.customerid and salekey = saleid and s.saletype=se.serieskey  AND s.GSTBILL<>0 and (v.dealertype in (0,1) or isnull(s.irnno,'')<>''  ) and se.hideseries=0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'   " & _
      " Group by  taxper, s.billno, s.billdate, s.payrupees, c.GSTIN, C.StateCode having sum(IsG+Lsg)>0 ", .getdatafile)
   
   FileName=  GSTR1Folder & "\B2bAll.csv"
   Set FrontPg = FSO.CreateTextFile(FileName, True) 
   
   FrontPg.Write "GSTIN/UIN of Recipient,Invoice Number,Invoice date,Invoice Value,Place Of Supply,Reverse Charge,Invoice Type,E-Commerce GSTIN,Rate,Taxable Value,Cess Amount"
   frontpg.write vbnewline 
   
   set RX = new RegExp
   RX.Pattern = "\r|\n|,|"""
   
   
   do while not rs.eof
      Separator = ""
      billno=rs.fields("Billno")
      if RX.Test(billno) then billno = """" & Replace(billno, """", """""") & """"
      
      FrontPg.Write Rs.fields("GSTIN") & "," & billno & "," & .setr(rs.fields("BillDate"),11,"dd-MMM-yyyy") & ","  & ltrim(.setr(rs.fields("Payrupees"),11,"#0.00")) & "," & _
         STc(int(rs.fields("StateCode"))) & ",N,Regular,," &  rs.fields("TaxPer") & "," &  ltrim(.setr(rs.fields("TotalTaxableSale"),11,"#0.00")) & "," & vbNewLine
      
      
      Rs.MoveNext	
      IF Not Rs.Eof then
         Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
      End If 
   LOOP 
   FrontPg.Close


   ''GSTR1 SR B2B Section
   Set Rs = .SUMRs("select c.GSTIn, s.billno, s.billdate, s.payrupees,c.StateCode,'N' as RC,'Regular' as IT,'' as EC, TaxPer, sum(IsG+Lsg) as TotalTaxableSale from VS_SaleReturnGSTDetails as v, Salehead as s   , customer as c" & _
      " Where c.customerkey = s.customerid and salekey = saleid and s.saletype=9999  AND s.GSTBILL<>0 and (v.dealertype in (0,1) or isnull(s.irnno,'')<>''  )   and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'   " & _
      " Group by  taxper, s.billno, s.billdate, s.payrupees, c.GSTIN, C.StateCode having sum(IsG+Lsg)<>0 ", .getdatafile)
   
   FileName=  GSTR1Folder & "\SRB2b.csv"
   Set FrontPg = FSO.CreateTextFile(FileName, True) 
   
   FrontPg.Write "GSTIN/UIN of Recipient,Invoice Number,Invoice date,Invoice Value,Place Of Supply,Reverse Charge,Invoice Type,E-Commerce GSTIN,Rate,Taxable Value,Cess Amount"
   frontpg.write vbnewline 
   
   set RX = new RegExp
   RX.Pattern = "\r|\n|,|"""
   
   
   do while not rs.eof
      Separator = ""
      billno=rs.fields("Billno")
      if RX.Test(billno) then billno = """" & Replace(billno, """", """""") & """"
      
      FrontPg.Write Rs.fields("GSTIN") & "," & billno & "," & .setr(rs.fields("BillDate"),11,"dd-MMM-yyyy") & ","  & ltrim(.setr(rs.fields("Payrupees"),11,"#0.00")) & "," & _
         STc(int(rs.fields("StateCode"))) & ",N,Regular,," &  rs.fields("TaxPer") & "," &  ltrim(.setr(rs.fields("TotalTaxableSale"),11,"#0.00")) & "," & vbNewLine
      
      
      Rs.MoveNext	
      IF Not Rs.Eof then
         Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
      End If 
   LOOP 
   FrontPg.Close

   
   
   ''GSTR1 B2CL Detail
   Set Rs = .SUMRs("select s.billno, s.billdate, s.payrupees,v.StateCode,TaxPer, sum(IsG+Lsg) as TotalTaxableSale  from VS_SaleGSTDetails as v, Salehead as s   , Series as se " & _
      " Where salekey = saleid and s.saletype=se.serieskey and se.hideseries=0   AND s.GSTBILL<>0 and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  and  v.PurchaseAt>2 and S.Payrupees>250000 " & _
      " Group by taxper, s.billno, s.billdate, s.payrupees, V.StateCode ", .getdatafile)
   
   FileName=  GSTR1Folder & "\B2ClDetail.xlsx"
   
   local.dumprstoexcel rs, filename
   
   
   ''GSTR1 B2CL Section
   Set Rs = .SUMRs("select s.billno, s.billdate, s.payrupees,v.StateCode,TaxPer, sum(IsG+Lsg) as TotalTaxableSale  from VS_SaleGSTDetails as v, Salehead as s   , Series as se " & _
      " Where salekey = saleid and s.saletype=se.serieskey and se.hideseries=0  AND s.GSTBILL<>0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  and  v.PurchaseAt>2 and S.Payrupees>250000 " & _
      " Group by taxper, s.billno, s.billdate, s.payrupees, V.StateCode having sum(IsG+Lsg)>0 ", .getdatafile)
   
   FileName=  GSTR1Folder & "\B2Cl.csv"
   Set FrontPg = FSO.CreateTextFile(FileName, True) 
   
   FrontPg.Write "Invoice Number,Invoice date,Invoice Value,Place Of Supply,Rate,Taxable Value,Cess Amount,E-Commerce GSTIN"
   frontpg.write vbnewline 
   
   do while not rs.eof
      Separator = ""
      billno=rs.fields("Billno")
      if RX.Test(billno) then billno = """" & Replace(billno, """", """""") & """"
      
      FrontPg.Write billno & "," & .setr(rs.fields("BillDate"),11,"dd-MMM-yyyy") & ","  & ltrim(.setr(rs.fields("Payrupees"),11,"#0.00")) & "," & _
         STc(int(rs.fields("StateCode"))) & "," &  rs.fields("TaxPer") & "," & ltrim(.setr( rs.fields("TotalTaxableSale"),11,"#0.00")) & ",," & vbNewLine
      
      Rs.MoveNext	
      IF Not Rs.Eof then
         Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
      End If 
   LOOP 
   FrontPg.Close
   
   ''GSTR1 B2Cs details
   Set Rs = .SUMRs("select s.billno, s.billdate, StateCode,TaxPer, IsG as IGSTTaxable, Lsg as LocalTaxable , isg+lsg as TotalTaxableSale from VS_SaleGSTDetails as v, Salehead as s   , Series as se  " & _
      " Where salekey = saleid and s.saletype=se.serieskey and v.dealertype=2 and se.hideseries=0  AND s.GSTBILL<>0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  and  ((v.PurchaseAt>2 and S.Payrupees<=250000 ) or (v.PurchaseAt<=2 ) ) and isnull(s.irnno,'')='' " & _
      " ", .getdatafile)
   
   FileName=  GSTR1Folder & "\B2CsDetails.xlsx"
   
   local.dumprstoexcel rs, filename
   
   
   ''GSTR1 SRB2Cs details
   Set Rs = .SUMRs("select s.billno, s.billdate, StateCode,TaxPer, IsG as IGSTTaxable, Lsg as LocalTaxable , isg+lsg as TotalTaxableSale from VS_SaleReturnGSTDetails as v, Salehead as s   " & _
      " Where salekey = saleid and s.saletype=9999 and v.dealertype=2   AND s.GSTBILL<>0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  and  ((v.PurchaseAt>2 and S.Payrupees<=250000 ) or (v.PurchaseAt<=2 ) ) and isnull(s.irnno,'')='' " & _
      " ", .getdatafile)
   
   FileName=  GSTR1Folder & "\SR_B2Cs_Details.xlsx"
   
   local.dumprstoexcel rs, filename
   

Set Rs = .SUMRs("select s.billno, s.billdate, StateCode, s.payrupees, ist as IGST, lst as LocalGST, ist + lst as TotalGST,  IsG as IGSTTaxable, Lsg as LocalTaxable , isg+lsg as TotalTaxableSale, payrupees - isg-lsg-lst-ist as diff from (select saleid, sum(lsg) as lsg, sum(lst) as lst, sum(isg) as isg, sum(ist) as ist, dealertype, purchaseat, statecode from VS_SaleGSTDetails group by saleid , dealertype, purchaseat, statecode) as v, Salehead as s   , Series as se  " & _
      " Where salekey = saleid and s.saletype=se.serieskey and v.dealertype=2 and se.hideseries=0  AND s.GSTBILL<>0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  and  ((v.PurchaseAt>2 and S.Payrupees<=250000 ) or (v.PurchaseAt<=2 ) ) and isnull(s.irnno,'')=''" & _
      " ", .getdatafile)
   
   FileName=  GSTR1Folder & "\B2CsPayRsDetails.xlsx"
   
   local.dumprstoexcel rs, filename

   Set Rs = .SUMRs("select s.billno, s.billdate, StateCode, s.payrupees, ist as IGST, lst as LocalGST, ist + lst as TotalGST,  IsG as IGSTTaxable, Lsg as LocalTaxable , isg+lsg as TotalTaxableSale, payrupees - isg-lsg-lst-ist as diff , s.irnno from (select saleid, sum(lsg) as lsg, sum(lst) as lst, sum(isg) as isg, sum(ist) as ist, dealertype, purchaseat, statecode from VS_SaleGSTDetails group by saleid , dealertype, purchaseat, statecode) as v, Salehead as s   , Series as se  " & _
      " Where salekey = saleid and s.saletype=se.serieskey and v.dealertype<>2 and se.hideseries=0  AND s.GSTBILL<>0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  and  ((v.PurchaseAt>2 and S.Payrupees<=250000 ) or (v.PurchaseAt<=2 ) ) " & _
      " ", .getdatafile)
   
   FileName=  GSTR1Folder & "\B2BPayRsDetails.xlsx"
   
   local.dumprstoexcel rs, filename
   
   
   
   ''GSTR1 B2Cs Section
   Set Rs = .SUMRs("select StateCode,TaxPer, sum(IsG+Lsg) as TotalTaxableSale  from VS_SaleGSTDetails as v, Salehead as s   , Series as se  " & _
      " Where salekey = saleid and s.saletype=se.serieskey and v.dealertype=2 and se.hideseries=0  AND s.GSTBILL<>0  and billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  and  ((v.PurchaseAt>2 and S.Payrupees<=250000 ) or (v.PurchaseAt<=2 ) )   and isnull(s.irnno,'')='' " & _
      " Group by taxper, StateCode having sum(IsG+Lsg)>0", .getdatafile)
   
   FileName=  GSTR1Folder & "\B2Cs.csv"
   Set FrontPg = FSO.CreateTextFile(FileName, True) 
   
   FrontPg.Write "Type,Place Of Supply,Rate,Taxable Value,Cess Amount,E-Commerce GSTIN"
   frontpg.write vbnewline 
   
   do while not rs.eof
      
      FrontPg.Write "OE," & STc(int(rs.fields("StateCode"))) & "," &  rs.fields("TaxPer") & "," &   ltrim(.setr( rs.fields("TotalTaxableSale"),11,"#0.00")) & ",," & vbNewLine
      
      Rs.MoveNext	
      IF Not Rs.Eof then
         Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
      End If 
   LOOP 
   FrontPg.Close
   
   
   set rs = .sumrs("select left(P.HSN,6) as HSN,p.name, 'UNT-UNITS' as UQc, sum(c.Quantity+c.free) as Qty,sum(c.taxable + (c.taxable * sgstper*2/100) ) as Total, sum(c.Taxable) as TaxableValue,  sum(case when cu.purchaseat<3 then  c.taxable * sgstper/100 else 0 end  ) as SGST   , sum(case when cu.purchaseat<3 then  c.taxable * sgstper/100 else 0 end  ) as CGST   , sum(case when cu.purchaseat<3 then  0 else c.taxable * sgstper*2/100  end ) as IGST , sgstper*2 as GSTPer " & _
      " from Product as P, CashItems as c , SaleHead as s, Series as se, (Select Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)<>0 then 1  else 0 end as DealerType, Statecode from  Customer ) as cu    " & _
      " where p.productkey = c.productid and customerid=customerkey and s.saletype = se.serieskey  AND s.GSTBILL<>0 and se.hideseries=0 and SaleKey = SaleId and c.mark<>'Rpl'   and s.billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  " & _
      " group by left(P.HSN,6), p.name, sgstper  order by left(P.HSN,6), p.name ", .getdatafile)
   
   
   FileName=  GSTR1Folder & "\HSNDetails.xlsx"
   
   local.dumprstoexcel rs, filename   

   set rs = .sumrs("select left(P.HSN,6) as HSN,SGSTPER + CGSTPER AS GSTPER, 'UNT-UNITS' as UQc, sum(c.Quantity+c.free) as Qty,sum(c.taxable + (c.taxable * sgstper*2/100) ) as Total, sum(c.Taxable) as TaxableValue,  sum(case when cu.purchaseat<3 then  c.taxable * sgstper/100 else 0 end  ) as SGST   , sum(case when cu.purchaseat<3 then  c.taxable * sgstper/100 else 0 end  ) as CGST   , sum(case when cu.purchaseat<3 then  0 else c.taxable * sgstper*2/100  end ) as IGST  " & _
      " from Product as P, CashItems as c , SaleHead as s, Series as se, (Select Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)<>0 then 1  else 0 end as DealerType, Statecode from  Customer ) as cu    " & _
      " where p.productkey = c.productid and customerid=customerkey and s.saletype = se.serieskey  AND s.GSTBILL<>0 and se.hideseries=0 and SaleKey = SaleId and c.mark<>'Rpl'   and s.billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  " & _
      " group by left(P.HSN,6), SGSTPER + CGSTPER  order by left(P.HSN,6), SGSTPER + CGSTPER ", .getdatafile)
   
   
   FileName=  GSTR1Folder & "\HSNGSTPerWise.xlsx"
   
   local.dumprstoexcel rs, filename   
   set rs = .sumrs("select customerkey,code,name,statecode,gstin from customer where left(gstin,2)<>left(statecode,2) and len(gstin)>10", .getdatafile)
   
   
   FileName=  GSTR1Folder & "\StateCodeVsGSTIN.xlsx"
   
   local.dumprstoexcel rs, filename   


   'HSN,Description,UQC,Total Quantity,Total Value,Taxable Value,Integrated Tax Amount,Central Tax Amount,State/UT Tax Amount,Cess Amount
   ''GSTR1 HSN Section
   
   set rs = .sumrs("select left(P.HSN,6) as HSN,'' as name, 'UNT-UNITS' as UQc, sum(c.Quantity+c.free) as Qty,sum(c.taxable + (c.taxable * sgstper*2/100) ) as Total, sum(c.Taxable) as TaxableValue,  sum(case when cu.purchaseat<3 then  c.taxable * sgstper/100 else 0 end  ) as LST   , sum(case when cu.purchaseat<3 then  0 else c.taxable * sgstper*2/100  end ) as IST , sgstper*2 as Rate " & _
      " from Product as P, CashItems as c , SaleHead as s, Series as se, (Select Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)<>0 then 1  else 0 end as DealerType, Statecode from  Customer ) as cu    " & _
      " where p.productkey = c.productid and customerid=customerkey and s.saletype = se.serieskey  AND s.GSTBILL<>0 and se.hideseries=0 and SaleKey = SaleId and c.mark<>'Rpl'   and s.billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  " & _
      " group by left(P.HSN,6) , sgstper  having sum(c.taxable + (c.taxable * sgstper*2/100) )>0 ", .getdatafile)
   
   
   FileName=  GSTR1Folder & "\HSN.csv"
   Set FrontPg = FSO.CreateTextFile(FileName, True) 
   
   FrontPg.Write "HSN,Description,UQC,Total Quantity,Total Value,Taxable Value,Integrated Tax Amount,Central Tax Amount,State/UT Tax Amount,Cess Amount, Rate"
   frontpg.write vbnewline 
   
   do while not rs.eof
      
      FrontPg.Write rs.fields("HSN") & "," &  rs.fields("Name") & "," & rs.fields("UQC") & "," &  rs.fields("Qty") & "," &  ltrim(.setr(rs.fields("Total"),12,"#0.00")) & "," &  ltrim(.setr(rs.fields("TaxableValue") ,12,"#0.00")) & "," &  ltrim(.setr(rs.fields("IST") ,12,"#0.00")) & "," & ltrim(.setr(rs.fields("LST"),12,"#0.00")) & "," &  ltrim(.setr(rs.fields("LST"),12,"#0.00")) & "," &  "," &  rs.fields("rate") & vbNewLine
      
      Rs.MoveNext	
      IF Not Rs.Eof then
         Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
      End If 
   LOOP 
   FrontPg.Close
   
   set rs = .sumrs("select left(P.HSN,6) as HSN,'' as name, 'UNT-UNITS' as UQc, sum(c.Quantity+c.free) as Qty,sum(c.taxable + (c.taxable * sgstper*2/100) ) as Total, sum(c.Taxable) as TaxableValue,  sum(case when cu.purchaseat<3 then  c.taxable * sgstper/100 else 0 end  ) as LST   , sum(case when cu.purchaseat<3 then  0 else c.taxable * sgstper*2/100  end ) as IST , sgstper*2 as Rate " & _
      " from Product as P, CashItems as c , SaleHead as s, Series as se, (Select Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)<>0 then 1  else 0 end as DealerType, Statecode from  Customer ) as cu    " & _
      " where p.productkey = c.productid and customerid=customerkey and s.saletype = se.serieskey and cu.dealertype=0  AND s.GSTBILL<>0 and se.hideseries=0 and SaleKey = SaleId and c.mark<>'Rpl'   and s.billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  " & _
      " group by left(P.HSN,6) , sgstper  having sum(c.taxable + (c.taxable * sgstper*2/100) )>0 ", .getdatafile)
   
   
   FileName=  GSTR1Folder & "\HSN(B2B).csv"
   Set FrontPg = FSO.CreateTextFile(FileName, True) 
   
   FrontPg.Write "HSN,Description,UQC,Total Quantity,Total Value,Taxable Value,Integrated Tax Amount,Central Tax Amount,State/UT Tax Amount,Cess Amount, Rate"
   frontpg.write vbnewline 
   
   do while not rs.eof
      
      FrontPg.Write rs.fields("HSN") & "," &  rs.fields("Name") & "," & rs.fields("UQC") & "," &  rs.fields("Qty") & "," &  ltrim(.setr(rs.fields("Total"),12,"#0.00")) & "," &  ltrim(.setr(rs.fields("TaxableValue") ,12,"#0.00")) & "," &  ltrim(.setr(rs.fields("IST") ,12,"#0.00")) & "," & ltrim(.setr(rs.fields("LST"),12,"#0.00")) & "," &  ltrim(.setr(rs.fields("LST"),12,"#0.00")) & "," &  "," &  rs.fields("rate") & vbNewLine
      
      Rs.MoveNext	
      IF Not Rs.Eof then
         Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
      End If 
   LOOP 
   FrontPg.Close

   set rs = .sumrs("select left(P.HSN,6) as HSN,'' as name, 'UNT-UNITS' as UQc, sum(c.Quantity+c.free) as Qty,sum(c.taxable + (c.taxable * sgstper*2/100) ) as Total, sum(c.Taxable) as TaxableValue,  sum(case when cu.purchaseat<3 then  c.taxable * sgstper/100 else 0 end  ) as LST   , sum(case when cu.purchaseat<3 then  0 else c.taxable * sgstper*2/100  end ) as IST , sgstper*2 as Rate " & _
      " from Product as P, CashItems as c , SaleHead as s, Series as se, (Select Customerkey, PurchaseAt , case when len(IsNull(GSTIN,''))<15 then 2 when len(IsNull(GSTIN,''))=15 and IsNull(GSTCD,0)<>0 then 1  else 0 end as DealerType, Statecode from  Customer ) as cu    " & _
      " where p.productkey = c.productid and customerid=customerkey and s.saletype = se.serieskey and cu.dealertype<>0  AND s.GSTBILL<>0 and se.hideseries=0 and SaleKey = SaleId and c.mark<>'Rpl'   and s.billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  " & _
      " group by left(P.HSN,6) , sgstper  having sum(c.taxable + (c.taxable * sgstper*2/100) )>0 ", .getdatafile)
   
   
   FileName=  GSTR1Folder & "\HSN(B2C).csv"
   Set FrontPg = FSO.CreateTextFile(FileName, True) 
   
   FrontPg.Write "HSN,Description,UQC,Total Quantity,Total Value,Taxable Value,Integrated Tax Amount,Central Tax Amount,State/UT Tax Amount,Cess Amount, Rate"
   frontpg.write vbnewline 
   
   do while not rs.eof
      
      FrontPg.Write rs.fields("HSN") & "," &  rs.fields("Name") & "," & rs.fields("UQC") & "," &  rs.fields("Qty") & "," &  ltrim(.setr(rs.fields("Total"),12,"#0.00")) & "," &  ltrim(.setr(rs.fields("TaxableValue") ,12,"#0.00")) & "," &  ltrim(.setr(rs.fields("IST") ,12,"#0.00")) & "," & ltrim(.setr(rs.fields("LST"),12,"#0.00")) & "," &  ltrim(.setr(rs.fields("LST"),12,"#0.00")) & "," &  "," &  rs.fields("rate") & vbNewLine
      
      Rs.MoveNext	
      IF Not Rs.Eof then
         Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
      End If 
   LOOP 
   FrontPg.Close

   msgbox "Exported successfully to CSV and saved @ " & GSTR1Folder  & ".  ",vbinformation,"Pharma Pulse"
   
End With	
END SUB

sub DumpValues(Rs , FrontPg)
dim RX
set RX = new RegExp
RX.Pattern = "\r|\n|,|"""


do while not rs.eof
   Separator = ""
   
   for i = 0 to RS.Fields.Count - 1
      Field = RS.Fields(i).Value & ""
      if RX.Test(Field) then
         Field = """" & Replace(Field, """", """""") & """"
      end if
      FrontPg.Write Separator & Field
      Separator = ","
   next
   FrontPg.Write vbNewLine
   Rs.MoveNext	
   IF Not Rs.Eof then
      Local.UpDateStatus Rs.AbsolutePosition  / Rs.RecordCount 
   End If 
LOOP 


end sub

Function SpecialFolderPath(StFolderName) 
Dim objWSHShell 
Dim strSpecialFolderPath
Set objWSHShell = CreateObject("WScript.Shell")
Set FSO = CreateObject("Scripting.FileSystemObject") 
strSpecialFolderPath = objWSHShell.SpecialFolders("Desktop") & "\" & StFolderName

If not FSO.FolderExists (strSpecialFolderPath ) then 
   fso.CreateFolder strSpecialFolderPath 
end if 

SpecialFolderPath=strSpecialFolderPath

Set objWSHShell = Nothing
Set fso = Nothing
End Function

sub UpdateStateCode()
with metascript   
   
   stc="36"
   set rs = .sumrs("select cast(IsNull(statecode,'0') as int) as SC, CompanyName from licence "  , .getdatafile)
   sc=rs.fields("sc")
   if sc <1 or sc = 28 or sc>37 then
      stc = inputbox("StateCode not defined for " & rs.fields("CompanyName") & vbcrlf & "Please enter a valid StateCode ", "StateCode Missing","36")
      if isnumeric(stc) then
         if stc <1 or stc = 28 or stc>37 then
            Msgbox "Invalid statecode entered", vbinformation
            stc="36"
         else
            .exec "Update licence set statecode='" & stc & "'", .getdatafile
         end if
      end if
   end if
   
   st="Select 0 as s, code, name, IDArea, StateCode, CustomerKey from customer where (isnull(Statecode,'0')='0'  or statecode='' or (statecode ='28' or cast(StateCode as int) >37 )) and customerkey in (select customerid from salehead where billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  ) order by IDArea,name "
   set crs = .sumrs(st & " ", .getdatafile)
   if crs.eof and crs.bof then exit sub   
   do while not crs.eof
      
      if msgbox ("Customer with Missing StateCodes Found. Do you want to update?", vbquestion + vbyesno,"Confirm updating StateCode") = vbno then exit sub
      
      .showform "Select 0 as s, code, name, IDArea, StateCode, CustomerKey from customer where isnull(Statecode,'0')='0'  and customerkey in (select customerid from salehead where billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  ) order by IDArea,name ", .getdatafile, ID1, ID2
      if trim(id1)<>"" then
         stcd = inputbox("Enter the State Code for the selected customers","36 for Telangana, 37 for Andhra...",stc)
         if msgbox("Do you want to update selected customers' StateCode with " & stcd, vbquestion + vbyesno,"Confirm Update") = vbno then exit sub
         .exec "Update customer set statecode='" & stcd & "' where customerkey in (" & ID1 & ") ", .getdatafile
      end if
      set crs = .sumrs("Select 0 as s, code, name, IDArea, StateCode, CustomerKey from customer where isnull(Statecode,'0')='0' and customerkey in (select customerid from salehead where billdate between  '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'  ) order by IDArea,name ", .getdatafile)
   loop 
end with
end sub