def index(req, item_name=None):
results = utils.select('''
select date(created - interval daynumber day) week, item_name, sum(count),
group_concat(concat_ws(' ', dayname(created), count) order by created SEPARATOR ',') detail
from
(SELECT
item_name, count(*) count, (weekday(created) + 1) MOD 7 as daynumber, created
from order_item
where is_cancelled = false
and item_name rlike %s
group by item_name, date(created)
) items_per_day
group by yearweek(created), item_name
order by date(created) desc, item_name
''',
incursor=None,
label=False,
args=[item_name]
)
# format detail
formatted_results = []
for row in results:
detail = row[-1] #last field
detail = dict(d.split(' ') for d in detail.split(','))
det_string = '<pre>' + ' | '.join(
('%s %s' % (dayname[:3], detail.get(dayname, '').ljust(3))
for dayname in ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
)) + '</pre>'
formatted_results.append(row[:-1] + (det_string,))
html = (
'''
<html>
<body>
''' +
utils.tohtml(
'items sold',
('week of', 'name', 'count', 'detail'),
formatted_results
) +
'''</body></html>'''
)
return html
def index(req, lag=0):
grand_total = utils.select('''
SELECT total, dtotal
FROM nd_tots
WHERE dat = date(now() - INTERVAL '%(lag)s' day);''' % locals(),
incursor=None,
label=False
)
html = (
'''
<html>
<body>
''' +
# utils.tohtml(
# 'Nightly Receipts by Server',
# ('Server', 'ccid', 'Receipts', 'Taxable', 'Tabs Closed'),
# results
# ) +
utils.tohtml(
"Nightly Total",
('Dinner','Lunch'),
grand_total
) +
# utils.tohtml(
# "7 day Total",
# ('Dinner','Lunch'),
# seven_day_total
# ) +
# utils.tohtml(
# "2 week average",
# ('Dinner','Lunch'),
# avg
# ) +
# utils.tohtml(
# "Day Totals",
# ('Dinner', 'Day', 'Date','Lunch'),
# day_totals
# ) +
'''</body></html>'''
)
return html
def notuse():
html = (
'''
<html>
<body>
''' +
utils.tohtml(
'Worked per week by person',
results[0].keys(),
results,
breakonfirst = True
) +
'''</body></html>'''
)
return html
def index(req):
colnames, records = utils.select(
"""
SELECT * from monthly_withholding
""",
incursor=None,
label="separate",
)
html = """
<html>
<body>
"""
html += utils.tohtml("Wages and withholding by month", colnames, records, breakonfirst=True) + """</body></html>"""
return html
def index(req, subcat=None):
results = utils.select('''
SELECT
item_name, count(*) count, DATE(created) dat
from order_item
where is_cancelled = false
group by item_name, date(created)
order by date(created) desc, item_name
''',
incursor=None,
label=False
)
if subcat:
cfg, cfg_items = config.iget()
filtered_items = []
for res in results:
name, count, dat = res
item = cfg_items.get(name)
if item and item['subcatname'] == subcat:
filtered_items.append(res)
else:
filtered_items = results
html = (
'''
<html>
<body>
''' +
utils.tohtml(
'items sold',
('name', 'count', 'date'),
filtered_items
) +
'''</body></html>'''
)
return html
def index(req, name):
colnames, results = utils.select('''
SELECT * from %(name)s
''' % locals(),
incursor=None,
label='separate'
)
html = (
'''
<html>
<body>
''' +
utils.tohtml(
name,
colnames,
results
) +
'''</body></html>'''
)
return html
def index(req, lag=0):
results = queries.nightly_sales_by_server(lag_days=lag)
seven_day_total = utils.select('''
SELECT sum(total) night, sum(dtotal) lunch
FROM nd_tots
WHERE dat > now() - INTERVAL '7' DAY''',
incursor=None,
label=False
)
avg = utils.select('''
SELECT sum(total)/2 night, sum(dtotal)/2 lunch
FROM nd_tots
WHERE dat > now() - INTERVAL '14' DAY;''',
incursor=None,
label=False
)
day_totals = utils.select('''select * from nd_tots order by dat desc''', incursor=None, label=False)
grand_total = utils.select('''
SELECT total, dtotal
FROM nd_tots
WHERE dat = date(now());''',
incursor=None,
label=False
)
html = (
'''
<html>
<body>
''' +
utils.tohtml(
'Nightly Receipts by Server',
('Server', 'posid', 'ccid', 'Receipts', 'Reciepts', 'Taxable', 'Tabs Closed', 'Date'),
results
) +
utils.tohtml(
"Nightly Total",
('Dinner','Lunch'),
grand_total
) +
utils.tohtml(
"7 day Total",
('Dinner','Lunch'),
seven_day_total
) +
utils.tohtml(
"2 week average",
('Dinner','Lunch'),
avg
) +
utils.tohtml(
"Day Totals",
('Dinner', 'Day', 'Date','Lunch'),
day_totals
) +
'''</body></html>'''
)
return html
def index(req, doprint=0):
if doprint:
print_pay()
print_message="<p> PRINTED.<br>"
else:
print_message = ""
cursor = utils.get_cursor()
populate_response = populate_pay_stub.populate_pay_stub(temp = True, incursor = cursor)
weekly = queries.weekly_pay(incursor=cursor)
payroll_sql = '''
SELECT week_of,
round(sum(hours_worked)) as hours_worked,
round(avg(hours_worked)) as avg_hours_worked,
count(person_id) as num_employees,
round(sum(weekly_pay - nys_withholding - fed_withholding - social_security_tax - medicare_tax)) as payroll
from
%(table_name)s
where yearweek(week_of) > yearweek(now() - interval '5' week)
and last_name not in ('Kobrin', 'Labossier', 'Kanarova')
group by yearweek(week_of)
order by yearweek(week_of) desc
'''
new_payroll = utils.select(
payroll_sql%{ 'table_name' : 'PAY_STUB_TEMP'},
incursor=cursor,
label=False
)
past_payroll = utils.select(
payroll_sql%{ 'table_name' : 'PAY_STUB'},
incursor=cursor,
label=False
)
detail = utils.select('''
SELECT concat(yearweek(intime),' ',dayname(intime),' ',date),
last_name,
first_name,
time_in,
time_out,
hours_worked
from hours_worked
where yearweek(intime) > yearweek(now() - interval '5' week)
order by yearweek(intime) desc, last_name, date(intime)''',
incursor=None,
label=False
)
html = (
'''
<html>
<body>
''' )
if populate_response:
html += '<h1>' + populate_response + '</h1>'
else:
html +='''
<form action="time.py?doprint=1" method="POST">
<input type="submit" value="print pay slips">
</form>
''' + print_message
html += (
utils.tohtml(
'Hours worked per week by person',
('week of', 'last name', 'first_name', 'hours_worked', 'rate', 'tax', 'net weekly wage', 'tips', 'total hourly'),
weekly,
breakonfirst = True
) +
utils.tohtml(
'New Payroll',
('yearweek', 'hours_worked', 'avg_hrs', '# employees', 'payroll'),
new_payroll,
breakonfirst = True
) +
utils.tohtml(
'Past Payroll',
('yearweek', 'hours_worked', 'avg_hrs', '# employees', 'payroll'),
past_payroll,
breakonfirst = True
) +
utils.tohtml(
"detail hours",
('date', 'last_name', 'first_name', 'time_in', 'time_out', 'hours_worked'),
detail
) +
'''</body></html>'''
)
return html
def index(req):
grand_total = utils.select('''
SELECT total, dtotal
FROM nd_tots
WHERE dat = date(now());''',
incursor=None,
label=False
)
seven_day_total1 = utils.select('''
SELECT sum(total) night, sum(dtotal) lunch
FROM nd_tots
WHERE dat > now() - INTERVAL '7' DAY''',
incursor=None,
label=False
)
seven_day_total2 = utils.select('''
SELECT sum(total) night, sum(dtotal) lunch
FROM nd_tots
WHERE dat > now() - INTERVAL '8' DAY''',
incursor=None,
label=False
)
in_out1 = utils.select('''
SELECT last_name,
time_in,
time_out
from hours_worked
where date(intime) = date(now())
order by intime;''',
incursor=None,
label=False
)
in_out2 = utils.select('''
SELECT last_name,
time_in,
time_out
from hours_worked
where date(intime) = date(now() - INTERVAL '1' DAY)
order by intime;''',
incursor=None,
label=False
)
fw_stats = utils.select('''
SELECT dname, dat,
comped,
wine_tot,
n_tot,
wine_pct
from fw_tots_and_staff
WHERE dat > now() - INTERVAL '7' DAY
ORDER by dat desc;''',
incursor=None,
label=False
)
html = (
'''
<html>
<body>
''' +
utils.tohtml(
"Nightly Total",
('Dinner','Lunch'),
grand_total
) +
utils.tohtml(
'7 Day Total today',
('Night', 'Lunch'),
seven_day_total1
) +
utils.tohtml(
'7 Day Total yesterday',
('Night', 'Lunch'),
seven_day_total2
) +
utils.tohtml(
'Clocked in today',
('Name', 'In', 'Out'),
in_out1
) +
utils.tohtml(
'Clocked in yesterday',
('Name', 'In', 'Out'),
in_out2
) +
utils.tohtml(
'FW Stats',
('Day', 'Date', 'Comped', 'Wine', 'Total','Percentage'),
fw_stats
) +
'''</body></html>'''
)
return html
