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)