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