<%
thepage =upper(orequest.form('newpage'))
set date ymd
set cent on
datefrom =orequest.form("datefrom")
dateto =orequest.form("dateto")
pdate1=ctod(datefrom)
pdate2=ctod(dateto)
set date british
counternumber =val(transform(orequest.form("counternumber")))
categoryname= allt(orequest.form("categoryname"))
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(categoryname)
case categoryname=[All Categories]
REPTTITLE=REPTTITLE+' (ALL CATEGORIES) '
case categoryname=[No Categories]
wheretext=wheretext+[ and empty(y.packing) ]
REPTTITLE=REPTTITLE+' (NO CATEGORY GIVEN) '
otherwise
wheretext=wheretext+[ and allt(y.packing)==']+categoryname+[']
REPTTITLE=REPTTITLE+' ('+categoryname+')'
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=[CATEGORY WISE PROFITABILIY SUMMARY]
ordertext=" order by y.packing, 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
&tsql
if _tally=0
return [No records found with current criteria]
endif
tstr=tstr+[
Category
Sales
Refunds
Net Sales
Net Cost
Net Profit
Margin
]
store 0 to tthissale, tthisreturns, tthiscost, tthisprofet
SELECT 'tempcursor'
do while not eof()
thiscategory=NVL(packing,[])
tstr=tstr+[
]
store 0 to thissale, thisreturns, thiscostSale,THISCOSTRET, thisprofet
do while not eof() and thiscategory=NVL(packing,[])
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#
Category
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+[