Sub Main( )
With MetaScript
   
   .exec "Update customer set pan =substring(gstin,3,10) where len(gstin)=15 and len(pan)<>10 ",.getdatafile
  
   TDSFolder = SpecialFolderPath("TDS_" & .setr(local.dtfrom.tag,5,"MMMYY"))
   FF =" and s.billdate between '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'   "

   .showform "Select 0 as s, Code, Name, PAN, sum(S.Gross-S.Discountamount-s.discountextra) as Taxable, sum(Payrupees) as BillAmount , customerkey  from purchasehead as s, customer as c " & _
      " where customerkey = customerProfileid " & FF & " and s.transferout<900 and s.transferout<>2 Group by c.pan, c.code, c.name  , c.customerkey  order by c.pan, c.name ", .getdatafile, c1, c2

   FF = FF & " And Customerkey in (" & c1 & ") "   

   set rs = .sumrs("Select PAN, GSTIN,sum(S.Gross-S.Discountamount-s.discountextra) as Taxable,sum(case when c.purchaseat=3 then 0 else cgst + sgst end ) as GST , sum(case when c.purchaseat<>3 then 0 else cgst + sgst end)  as IGST,sum( s.payrupees - (S.Gross-S.Discountamount-s.discountextra+s.sgst + s.cgst) ) as Others, sum(Payrupees) as BillAmount  from purchasehead as s, customer as c " & _
      " where customerkey = customerProfileid " & FF & " and s.transferout<900 and s.transferout<>2 Group by c.pan, c.gstin ", .getdatafile)
   filename = TDSFolder & "\Purchase PAN Wise.xls"
   local.dumprstoexcel rs,filename
   
   set rs = .sumrs("Select Code, Name, PAN, GSTIN,sum(S.Gross-S.Discountamount-s.discountextra) as Taxable,sum(case when c.purchaseat=3 then 0 else cgst + sgst end ) as GST , sum(case when c.purchaseat<>3 then 0 else cgst + sgst end)  as IGST,sum( s.payrupees - (S.Gross-S.Discountamount-s.discountextra+s.sgst + s.cgst) ) as Others,  sum(Payrupees) as BillAmount  from purchasehead as s, customer as c " & _
      " where customerkey = customerProfileid " & FF & " and s.transferout<900 and s.transferout<>2 Group by c.pan, c.gstin, c.code, c.name  ", .getdatafile)
   filename = TDSFolder & "\Purchase Supplier Wise.xls"
   local.dumprstoexcel rs,filename

   set rs = .sumrs("Select Code, Name, PAN, GSTIN,BillNo, BillDate, InvoiceNo, Invoicedate, S.Gross-S.Discountamount-s.discountextra as Taxable, case when c.purchaseat=3 then 0 else cgst + sgst end as GST , case when c.purchaseat<>3 then 0 else cgst + sgst end as IGST, s.payrupees - (S.Gross-S.Discountamount-s.discountextra+s.sgst + s.cgst)  as Others,  Payrupees as BillAmount  from purchasehead as s, customer as c " & _
      " where customerkey = customerProfileid " & FF & " and s.transferout<900 and s.transferout<>2 ", .getdatafile)
   filename = TDSFolder & "\Purchase Bill Wise.xls"
   local.dumprstoexcel rs,filename
   
   msgbox "Files exported to " & tdsfolder, vbinformation, "PharmaPulse - TDS Reports"
End With
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