]
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' ]
tsql=tsql+wheretext+ordertext
try
&tsql
catch
tstr=[Has error]
endtry
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+[| ]+iif(empty(THISitemid),[NO ITEM ID],ALLT(THISitemid))+[ | ]
tstr=tstr+[]+iif(empty(THISitemname),[NO ITEM NAME],ALLT( THISitemname))+[ | ]
tstr=tstr+[]+iif(empty(thissale),[ ],ALLT(transform(thissale,[99999999999999.99])))+[ | ]
tstr=tstr+[]+iif(empty(thisreturns),[ ],ALLT(transform(thisreturns,[99999999999999.99])))+[ | ]
tstr=tstr+[]+iif(empty(thissale-thisreturns),[ ],ALLT(transform(thissale-thisreturns,[99999999999999.99])))+[ | ]
tstr=tstr+[]+iif(empty(thiscostSale-thiscostRet),[ ],ALLT(transform(thiscostSale-thiscostRet,[99999999999999.99])))+[ | ]
thisprofet=thissale-thisreturns-(thiscostSale-thiscostRet)
if thissale-thisreturns<>0
thisprofetMargin=round(thisprofet*100/(thissale-thisreturns),2)
else
thisprofetMargin=0.00
endif
tstr=tstr+[]+iif(empty(thisprofet),[ ],ALLT(transform(thisprofet,[99999999999999.99])))+[ | ]
tstr=tstr+[]+iif(empty(thisprofetMargin),[ ],ALLT(transform(thisprofetMargin,[99999999999999.99]))+[%])+[ | ]
tstr=tstr+[
]+chr(13)
tthissale=tthissale+thissale
thisreturns=thisreturns+thisreturns
tthiscost=tthiscost+thiscostSale-thiscostRet
tthisprofet=tthisprofet+thisprofet
enddo
tstr=tstr+[TOTALs: | ]
tstr=tstr+[]+iif(empty(tthissale),[ ],ALLT(transform(tthissale,[99999999999999.99])))+[ | ]
tstr=tstr+[]+iif(empty(tthisreturns),[ ],ALLT(transform(tthisreturns,[99999999999999.99])))+[ | ]
tstr=tstr+[]+iif(empty(tthissale-tthisreturns),[ ],ALLT(transform(tthissale-tthisreturns,[99999999999999.99])))+[ | ]
tstr=tstr+[]+iif(empty(tthiscost),[ ],ALLT(transform(tthiscost,[99999999999999.99])))+[ | ]
tthisprofet=tthissale-tthisreturns-(tthiscost)
if tthissale-tthisreturns<>0
tthisprofetMargin=round(tthisprofet*100/(tthissale-tthisreturns),2)
else
tthisprofetMargin=0.00
endif
tstr=tstr+[]+iif(empty(tthisprofet),[ ],ALLT(transform(tthisprofet,[99999999999999.99])))+[ | ]
tstr=tstr+[]+iif(empty(tthisprofetMargin),[ ],ALLT(transform(tthisprofetMargin,[99999999999999.99]))+[%])+[ | ]
tstr=tstr+[]+chr(13)
tstr=tstr+[
]
%>