<%
thepage =upper(orequest.form('newpage'))
set date ymd
set cent on
datefrom =orequest.form("datefrom")
dateto =orequest.form("dateto")
reportbase =orequest.form("reportbase")
pdate1=ctod(datefrom)
pdate2=ctod(dateto)
set date british
counternumber =val(transform(orequest.form("counternumber")))
Classname= UPPER(allt(orequest.form("Classname")))
wheretext=[]
reptsecline=''
REPTTITLE=thepage
Do Case
Case Empty(pdate1) And Empty(pdate2)
Case Empty(pdate1) And Not Empty(pdate2)
wheretext=Alltrim(wheretext)+" And x.Date <= PDate2 "
reptsecline='TILL '+DTOC(PDATE2)
Case Not Empty(pdate1) And Empty(pdate2)
wheretext=Alltrim(wheretext)+" And x.Date >= PDate1 "
reptsecline='FROM '+DTOC(PDATE1)
Case Not Empty(pdate1) And Not Empty(pdate1)
wheretext=Alltrim(wheretext)+" And BETWEEN(x.Date,pdate1,pdate2) "
reptsecline='FROM '+DTOC(PDATE1)+' TO '+DTOC(PDATE2)
Endcase
do case
case empty(Classname)
case Classname=[ALL CLASSES]
REPTTITLE=REPTTITLE+' (ALL CLASSES) '
case Classname=[NO CLASSES]
wheretext=wheretext+[ and empty(y.ALMARA) ]
REPTTITLE=REPTTITLE+' (NO Class GIVEN) '
otherwise
wheretext=wheretext+[ and allt(y.ALMARA)==']+Classname+[']
REPTTITLE=REPTTITLE+' ('+Classname+')'
endcase
if not counternumber<0
wheretext=Alltrim(wheretext)+" And x.spot >= ?counternumber "
REPTTITLE=REPTTITLE+' ON SPOT '+TRANSFORM(counternumber)
endif
wheretext=wheretext+" AND INLIST(x.vtype,[CashSale],[Invoice],[CreditMemo],[CREFUND]) "
If Not(Empty(wheretext))
wheretext=" where "+Substr(wheretext,5)
Endif
tstr=[
]+REPTTITLE+[
]+reptsecline+[
]
if thepage=[CLASS WISE PROFITABILIY SUMMARY]
ordertext=" order by y.ALMARA, y.name, y.itemid, x.date, x.vtype "
tsql=[Select x.In, x.out, x.rate, x.transid, x.vtype, x.date, x.cost, y.Name, y.packing, y.almara, X.ItemID, z.incomep ]
tsql=tsql+[ from (']+dbclocation+[stock') x LEFT JOIN (']+dbclocation+[itemmast') y ON (X.ITEMID=Y.ITEMID) ]
tsql=tsql+[ LEFT JOIN (']+dbclocation+[depart') Z ON(Y.PACKING=Z.DEPART) into cursor 'tempcursor' ]+wheretext+ordertext
* return wheretext+[ ]+thepage+[ ]+TSQL
* RETURN TSQL
&tsql
if _tally=0
return [No records found with current criteria]
endif
tstr=tstr+[
Class
Sales
Refunds
Net Sales
Net Cost
Net Profit
Margin
]
store 0 to tthissale, tthisreturns, tthiscost, tthisprofet
SELECT 'tempcursor'
do while not eof()
thisClass=NVL(ALMARA,[])
tstr=tstr+[
]
store 0 to thissale, thisreturns, thiscostSale,THISCOSTRET, thisprofet
do while not eof() and thisClass=NVL(ALMARA,[])
if in-out > 0
thisreturns=thisreturns+((in-out)*rate)
thiscostRet=thiscostRet+((in-out)*cost)
else
thissale=thissale+((out-in)*rate)
thiscostSale=thiscostSale+((out-in)*cost)
endif
skip
enddo
tstr=tstr+[
]
return tstr
endif
if thepage=[ITEM WISE PROFITABILIY SUMMARY]
ordertext=" order by y.name, y.itemid, x.date, x.vtype "
tsql=[Select x.In, x.out, x.rate, x.transid, x.vtype, x.date, x.cost, y.Name, y.packing, y.almara, X.ItemID, z.incomep ]
tsql=tsql+[ from (']+dbclocation+[stock') x LEFT JOIN (']+dbclocation+[itemmast') y ON (X.ITEMID=Y.ITEMID) ]
tsql=tsql+[ LEFT JOIN (']+dbclocation+[depart') Z ON(Y.PACKING=Z.DEPART) into cursor 'tempcursor' ]+wheretext+ordertext
&tsql
if _tally=0
return [No records found with current criteria]
endif
tstr=tstr+[
Item#
Class
Sales
Refunds
Net Sales
Net Cost
Net Profit
Margin
]
store 0 to tthissale, tthisreturns, tthiscost, tthisprofet
do while not eof()
thisitemid=itemid
THISitemname=NVL(name,[])
tstr=tstr+[
]
store 0 to thissale, thisreturns, thiscostSale,THISCOSTRET, thisprofet
do while not eof() and itemid=thisitemid
if in-out > 0
thisreturns=thisreturns+((in-out)*rate)
thiscostRet=thiscostRet+((in-out)*cost)
else
thissale=thissale+((out-in)*rate)
thiscostSale=thiscostSale+((out-in)*cost)
endif
skip
enddo
tstr=tstr+[