''Tested by Ajay on 2-Nov-2020
Sub Main( )
With MetaScript

.exec "Update customer set tcs=0 where tcs is null",.getdatafile
.exec "Update salehead set tcs=0 where tcs is null",.getdatafile
.exec "Update Purchasehead set tcs=0 where tcs is null",.getdatafile
.exec "Update customer set pan =substring(gstin,3,10) where len(gstin)=15 and len(pan)<>10 ",.getdatafile

TCSFolder = SpecialFolderPath("TCS_" & .setr(local.dtfrom.tag,5,"MMMYY"))
FF =" and s.billdate between '" & local.dtfrom.tag & "' and '" & local.dtto.tag & "'   "

set rs = .sumrs("Select PAN, GSTIN,sum(S.Gross-S.Discountamount) as Taxable, sum(s.gst + s.igst) as Tax,  sum(Payrupees) as BillAmount, Sum(s.TCS) as TCSCollected from Salehead as s, customer as c " & _
" where customerkey = customerid " & FF & " and s.saletype<900 Group by c.pan, c.gstin having sum(s.tcs)<>0 ", .getdatafile)
   filename = TCSFolder & "\TCS Collected PAN Wise.xls"
   local.dumprstoexcel rs,filename


set rs = .sumrs("Select Code,Name,case when IsNull(c.tcs,0)=0 then 'Non TCS' else 'TCS' end as TCSCustomer, Cell,PAN, GSTIN,sum(S.Gross-S.Discountamount) as Taxable, sum(s.gst + s.igst) as Tax,  sum(Payrupees) as BillAmount, Sum(s.TCS) as TCSCollected from Salehead as s, customer as c " & _
" where customerkey = customerid " & FF & " and s.saletype<900 Group by c.code,c.name, c.tcs, c.cell, c.pan, c.gstin having sum(s.tcs)<>0 ", .getdatafile)
   filename = TCSFolder & "\TCS Collected.xls"
   local.dumprstoexcel rs,filename

set rs = .sumrs("Select Code,Name,case when IsNull(c.tcs,0)=0 then 'Non TCS' else 'TCS' end as TCSCustomer, Cell,PAN, GSTIN,s.billno, s.billdate, s.Gross- s.discountAmount as Taxable,  s.GST + S.IGST as Tax,  Payrupees as Sales, s.TCS as  TCSCollected, round((s.gross-s.discountamount )* 0.075/100,2)  as TCSOnTaxable, round((s.gross-s.discountamount + s.gst+ s.igst) * 0.075/100,2)  as TCSOnGST  from Salehead as s, customer as c " & _
" where customerkey = customerid and s.saletype<900 and s.tcs<>0 " & FF , .getdatafile)
   filename = TCSFolder & "\TCS Collected Details.xls"
   local.dumprstoexcel rs,filename


set rs = .sumrs("Select Code,Name,case when IsNull(c.tcs,0)=0 then 'Non TCS' else 'TCS' end as TCSCustomer, Cell,PAN, GSTIN,s.billno, s.billdate, s.Gross- s.discountAmount as Taxable,  s.GST + S.IGST as Tax,  Payrupees as Sales, s.TCS as  TCSCollected, round((s.gross-s.discountamount )* 0.075/100,2)  as TCSOnTaxable, round((s.gross-s.discountamount + s.gst+ s.igst) * 0.075/100,2)  as TCSOnGST  from Salehead as s, customer as c " & _
" where customerkey = customerid " & FF & " and s.saletype<900 And ((c.tcs<>0 and s.tcs=0) or (c.tcs=0 and s.tcs<>0) )", .getdatafile)
   filename = TCSFolder & "\Improper TCS Collected.xls"
   local.dumprstoexcel rs,filename


set rs = .sumrs("Select Code,Name, Cell,PAN, GSTIN,sum(s.Gross- s.discountAmount - s.discountExtra) as Taxable,  sum(s.CGST + s.sGST + S.IGST) as Tax,  sum(Payrupees) as Purchase, Sum(s.TCS) as TCSPaid from Purchasehead as s, customer as c " & _
" where customerkey = customerProfileid " & FF & " and s.tcs<>0 Group by c.code,c.name, c.tcs, c.cell, c.pan, c.gstin having sum(s.tcs)<>0 ", .getdatafile)
   filename = TCSFolder & "\TCS Paid.xls"
   local.dumprstoexcel rs,filename

set rs = .sumrs("Select Code,Name, Cell,PAN, GSTIN, s.billno, s.billdate, s.Gross- s.discountAmount - s.discountExtra as Taxable,  s.CGST + s.sGST + S.TaxCST as Tax,  Payrupees , s.TCS as  TCSPaid from Purchasehead as s, customer as c " & _
" where customerkey = customerProfileid " & FF & " and s.tcs<>0 ", .getdatafile)
   filename = TCSFolder & "\TCS Paid Bill Wise.xls"
   local.dumprstoexcel rs,filename

msgbox "Files exported to " & tcsfolder, vbinformation, "PharmaPulse - TCS 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