def populate_pay_stub(temp = True, incursor=None):
#days_of_tips_calculated = utils.select(
# '''select count(distinct date(intime)) from hours
# where yearweek(intime) = yearweek(now() - interval '1' week) and tip_pay is not null''',
# label = False
# )[0][0]
#if days_of_tips_calculated != 7:
# return 'Tips have been calculated for only %s days last week. When all days tips are calculated, refresh this page to see and print weekly pay for last week.'%days_of_tips_calculated
results = utils.select('''
select
DATE(intime) - interval (DAYOFWEEK(intime) -1) DAY as week_of,
hours_worked.person_id,
last_name, first_name,
sum(hours_worked) as hours_worked,
pay_rate,
COALESCE(allowances, 0) allowances,
COALESCE(nominal_scale, 0) nominal_scale,
COALESCE(married, 0) married,
COALESCE(salary, 0) + COALESCE(round(sum(hours_worked)*pay_rate), 0) as weekly_pay,
COALESCE(salary, 0) + COALESCE(round(sum(hours_worked)*pay_rate), 0) * COALESCE(nominal_scale,0) as gross_wages,
COALESCE(sum(tip_pay),0) tips,
COALESCE(sum(tip_pay) / sum(hours_worked) + pay_rate, 0) as total_hourly_pay
from hours_worked LEFT OUTER JOIN employee_tax_info ON hours_worked.person_id = employee_tax_info.person_id
where yearweek(intime) = yearweek(now() - interval '1' week)
and intime != 0
group by hours_worked.person_id, yearweek(intime)
''',
incursor = incursor,
label = True
)
if temp:
utils.execute('''
create temporary table PAY_STUB_TEMP like PAY_STUB;
''', incursor=incursor);
table_names = ('PAY_STUB_TEMP',)
else:
table_names = ('PAY_STUB', 'WEEKLY_PAY_STUB')
for row in results:
if not temp and utils.select(
'select 1 from PAY_STUB where week_of = "%(week_of)s" and person_id = %(person_id)s'%row,
incursor = incursor
):
continue
for table_name in table_names:
if table_name == 'WEEKLY_PAY_STUB':
row['gross_wages'] = row['weekly_pay'] + float(row['tips'])
row['pay_rate'] = round(row['total_hourly_pay'])
tax.add_witholding_fields(row)
columns = ', '.join(row.keys())
values = ', '.join(("'%s'" % value for value in row.values()))
sqltext = 'INSERT into %s (%s) VALUES (%s);'%(table_name, columns, values)
my_logger.debug('pay stub: ' + sqltext)
utils.execute(sqltext, incursor=incursor)
def make_estub(first_name, last_name, baserate, rate_variance, basehours, hour_variance):
incursor = utils.get_cursor()
table_name = 'E_STUB'
utils.execute('''
create temporary table E_STUB like PAY_STUB;
''', incursor=incursor);
for sunday in last_sundays(7):
hours = basehours + randint(-basehours, basehours)*hour_variance
rate = baserate + randint(-baserate, baserate)*rate_variance
wages = rate*hours
row = {
'week_of' : sunday,
'person_id' : 0,
'last_name': last_name,
'first_name': first_name,
'hours_worked' : hours,
'pay_rate': rate,
'allowances': 1,
'nominal_scale': 0,
'married': 0,
'weekly_pay': 0,
'gross_wages': wages,
'tips': 0,
'total_hourly_pay': rate
}
tax.add_witholding_fields(row)
columns = ', '.join(row.keys())
values = ', '.join(("'%s'" % value for value in row.values()))
sqltext = 'INSERT into %s (%s) VALUES (%s);'%(table_name, columns, values)
#my_logger.debug('pay stub: ' + sqltext)
utils.execute(sqltext, incursor=incursor)
for sunday in last_sundays(12):
print_stubs(0, sunday, table_name, incursor=incursor)
