def select(query, incursor=None, label=True, args=None): my_logger.debug(query) if not incursor: conn = MySQLdb.connect (host = "localhost", user = "******", passwd = "pos", db = "pos", charset = "utf8") cursor = conn.cursor() else: cursor = incursor cursor.execute(query, args) rows = cursor.fetchall() colnames = [coldesc[0] for coldesc in cursor.description] if label == 'separate': return colnames, rows if label: results = label_query_rows(colnames, rows) else: results = rows if not incursor: cursor.close() conn.close() return results
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 execute(sql, incursor=None, args= None): my_logger.debug(sql) if not incursor: conn = MySQLdb.connect (host = "localhost", user = "******", passwd = "pos", db = "pos", charset = "utf8") cursor = conn.cursor() else: cursor = incursor cursor.execute(sql, args) if not incursor: cursor.close() conn.close()