[%INCLUDE "$tdir/head.html"%]
Product Sales for Period [%pars.sdate%] - [%pars.edate%]
[%da = pars.sdate.split('/')%]
[%da = [ da.2, da.1, da.0 ] %]
[%sdate = da.join('-')%]
[%da = pars.edate.split('/')%]
[%da = [ da.2, da.1, da.0 ] %]
[%edate = da.join('-')%]
[%data = {}%]
[%FOREACH sale = DBI.query("select DATE_FORMAT(inv.stamp, '%d/%m/%Y %W') date,DATE_FORMAT(inv.stamp, '%Y-%m-%d') date2,
menu.code,menu.name,menu.stockonhand,avg(tods.price) price,sum(tods.qty) qty,sum(tods.price * tods.qty) total
from tillordersdetails tods, tillinvoices inv, menu
where
menu.code REGEXP '^$profile.code[0-9]'
and tods.plu = menu.id
and tods.invoice = inv.id
and (inv.voided = 0 or inv.voided is null)
and date(inv.stamp) >= ? and date(inv.stamp) <= ?
group by date(inv.stamp), menu.id
order by date(inv.stamp), LENGTH(menu.code), menu.code;", sdate, edate)%]
[%IF ! data.${sale.date2}%] [%data.${sale.date2} = []%] [%END%]
[%data.${sale.date2}.push(sale)%] [%#date(inv.stamp)%]
[%END%]
[%totals = {}%]
[%sohs = {}%]
[% PERL %]
my $prof = $stash->get('profile');
my %proff = %{$prof};
my $profile_code = ucfirst $proff{'code'};
$stash->set('profile_code', $profile_code);
[% END %]
[%FOREACH sale = DBI.query("select DATE_FORMAT(inv.stamp, '%d/%m/%Y %W') date,
menu.code,menu.name,menu.stockonhand,avg(tods.price) price,sum(tods.qty) qty,menu.stockonhand,sum(tods.price * tods.qty) total
from tillordersdetails tods, tillinvoices inv, menu
where
menu.code REGEXP '^$profile.code[0-9]'
and tods.plu = menu.id
and tods.invoice = inv.id
and (inv.voided = 0 or inv.voided is null)
and date(inv.stamp) >= ? and date(inv.stamp) <= ?
group by menu.id
order by date(inv.stamp);", sdate, edate)%]
[%scode = sale.code %]
[%#profile_code.ucfirst%]
[%scode = scode.remove(profile_code)%]
[%scode = scode.replace('\-[A-Za-z0-9]+','')%]
[%totals.${scode} = sale%]
[%END%]
[%IF pars.breakdown%]
DAILY BREAKDOWN
[%FOREACH tdate = data.keys.sort%]
[%thetotal = 0%]
| [%data.$tdate.0.date%] |
| CODE |
DESCRIPTION |
SALES QTY |
SELL PRICE |
TOTAL |
Current
S.O.H. |
[%FOREACH item = data.$tdate%]
| [%item.code%] |
[%item.name%] |
[%item.qty * 1%] |
R
[%pr(item.price * 1)%] |
R
[%pr(item.total * 1)%] |
[%item.stockonhand%] |
[%thetotal = thetotal + item.total%]
[%END%]
| TOTAL for [%data.$tdate.0.date%] |
R
[%pr(thetotal)%]
| |
[%END%]
[%END%]
SUMMARY
| CODE |
DESCRIPTION |
QTY |
SELL PRICE |
TOTAL |
Current
S.O.H. |
[%FOREACH item = totals.keys.nsort%]
| [%totals.$item.code%] |
[%totals.$item.name%] |
[%totals.$item.qty * 1%] |
R [%pr(totals.$item.price * 1)%] |
R [%pr(totals.$item.total * 1)%] |
[%totals.$item.stockonhand%] |
[%summarytotal = summarytotal + totals.$item.total%][%END%]
| GRAND TOTAL |
R
[%pr(summarytotal)%]
|
|
[%INCLUDE "$tdir/foot.html"%]