def emp_gross_sal(empcode, month, year): pay_period = get_pay_period(month, year) prod = Connection.get_connection() cursor = Connection.get_cursor() cursor.execute(query.GROSS_SALRY,(empcode, pay_period)) #9||01/04/2017||30/04/2017||0 resultset = cursor.fetchall() return resultset
def get_distinct_emp(month, year): pay_period = get_pay_period(month, year) prod = Connection.get_connection() cursor = Connection.get_cursor() cursor.execute(query.DISTINCT_EMP,(pay_period,)) resultset = cursor.fetchall() return resultset
def get_employee_pro_sal(emp_code, year, month): pay_period = get_pay_period(month, year) connection = Connection.get_connection() cursor = Connection.get_cursor() cursor.execute(query.EMP_SAL, (emp_code, pay_period)) #9||01/04/2017||30/04/2017||0 resultset = cursor.fetchall() return resultset
def read_excel(file, sheet): workbook = openpyxl.load_workbook(file) sheet = workbook.get_sheet_by_name(sheet) count = 0 for row in sheet: if count == 0: count += 1 continue policy = row[0].value empcode = row[1].value amount = row[2].value update_database(amount, empcode, policy) Connection.close_cursor() Connection.connection_close()
def sal_comparison(file_name, sheet, year, month): emp_sal_map = {} fox_salary = {} count = 0 workbook = openpyxl.load_workbook(file_name) sheet = workbook.get_sheet_by_name(sheet) empcode_col = global_vars.HEAD_SEQUENCE['PR_NEWCODE'] for row in sheet: global found_emp global row_count row_count = 1 found_emp = False if found_emp == True: row_count += 1 '''if count == 100: break''' fox_salary.clear() emp_sal_map.clear() # ignore first row of the sheet. if count == 0: count += 1 continue empcode = row[empcode_col].value # employee salary result set emp_sal_resultset = prod_sal.get_employee_pro_sal(empcode, year, month) ''' Process employee record from fox salary row and Prodigious Resultset to create two maps. 1) Foxpro Map 2) Prodigious Map Both map will have common keys for direct and simple comparison. A common value is set to represnt salary heads of both systems. ''' if emp_sal_resultset: fox_salary = fox_salary_map.generate_fox_sal_map( row) # employee fox salary map #process result set to get salary head and value for val in emp_sal_resultset: emp_sal_map[val[2]] = val[0] #print("{1} -> {0},{2}".format(val[0],val[1], val[2])) prod_salary = prod_salary_map.generate_prod_sal_map( emp_sal_map) # employee prod salary map compare_emp_salary(empcode, fox_salary, prod_salary, row, count) #print(count) count += 1 Connection.connection_close() Connection.close_cursor() comparison_workbook.save('D://comparison1.xlsx') parseworkbook.process_workbook('D://comparison1.xlsx')
def get_loans(empcode, loan_date): count = 1 loans_list = [] with Connection() as cursor: cursor.execute(query.LOAN_INFO, (empcode, loan_date)) for row in cursor.fetchall(): loans_list.append(row) print("{0}.{1}".format(count, row)) count += 1 print('-' * 300) return loans_list
def update_loan_date(year, month, day, emi_id_list, loandr, employee): with Connection() as cursor2: for emi_id in emi_id_list: value_list = [] local_date = '-'.join([str(year), str(month), str(day)]) #cursor2.execute(query.LOAN_SELECT,emi_id) cursor2.execute(query.UPDATE_LOAN_DATE, (local_date, loandr, employee, emi_id)) '''for row in cursor2.fetchone(): value_list.append(str(row)) print("{}-{}->{}".format(local_date, emi_id, ','.join(value_list)))''' if month == 12: month = 1 year += 1 else: month += 1 con = Connection.get_connection() con.commit() print('Updation Done') Connection.connection_close()
def get_loan_detail(loan): id_loan = [] employee = loan[0] issue_date = loan[1] loandr = loan[5] print('{0}-> {2},{1}'.format(employee, issue_date, loandr)) with Connection() as cursor1: cursor1.execute(query.LOAN_DETAILS, (employee, loandr)) for record in cursor1.fetchall(): id_loan.append(record[0]) print(record) print("-" * 300) return id_loan
def get_emp_salary(self, gaa_num, pay_period): with Connection() as cursor: cursor.execute(query.EMP_SAL, (gaa_num, pay_period)) for row in cursor.fetchall(): for col in row: print(col)
def update_database(amount, empcode, policy): connection = Connection.get_connection() cursor = Connection.get_cursor() cursor.execute(query.UPDATE_LIC, (float(amount), empcode, policy)) connection.commit()
def close(): Connection.close_cursor() Connection.connection_close()
def update_template(resultset: ''): con = Connection.get_connection() cursor.executemany(query.UPDATE_TEMPLATE_INC, resultset) con.commit()
__author__ = 'gaa8664' from salary.connection import Connection from salary import query # create static object for connection and cursor. Connection.get_connection() cursor = Connection.get_cursor() # fetch data that needs to be updated def get_resultset(): cursor.execute(query.GET_EMPLOYEE_TEMPLATE, ('2017-11-01', '2017-11-30')) resultset = cursor.fetchall() return resultset # display the fetched data def show_data(resultset): #resultset = get_resultset() for row in resultset: print(row) print('{}->{}'.format(row[0], row[3])) # update the template information def update_template(resultset: ''): con = Connection.get_connection() cursor.executemany(query.UPDATE_TEMPLATE_INC, resultset) con.commit()
from salary.connection import Connection import salary.query as query con = Connection.get_connection() cursor = Connection.get_cursor() ''' get employees code, whose increment has to be removed. ''' def get_valid_employee(from_date, to_date, param): cursor.execute(query.EMP_INC_DETAIL,(from_date,to_date,param)) resultset = cursor.fetchall() return resultset # method will remove given employee entries from INCTHISTORY, EMPSALTEMPLATE, SALHISTORY for selected month def delete_increment_entries( emp, date,template ): cursor.execute(query.DELETE_EMP_SALTEMPLATE,(emp, date, template)) cursor.execute(query.DELETE_SAL_DETAILS,(emp,date)) cursor.execute(query.DELETE_EMP_INC_DETAIL,(emp,date,template)) con.commit() if __name__ == '__main__': data = get_valid_employee('2018-02-01', '2018-02-28', '%3%') count = 0 for record in data: print(record) delete_increment_entries(record[0], record[1] , record[2]) Connection.close_cursor()
def sal_comparison(file_name, sheet, month, year): # map specifying columns of comparison sheets. comparison_sheet_col = { 1: 'Emp Code', 2: 'Foxpro Net', 3: 'Prod Net', 4: 'Foxpro Deduction', 5: 'Prod Deduction' } workbook = openpyxl.load_workbook(file_name) sheet = workbook.get_sheet_by_name(sheet) comparison_workbook = openpyxl.Workbook() comp_sheet = comparison_workbook.create_sheet("Total Comparison") foxpro_empmaster_col = {} empcode_index = '' emp_deduct_index = '' emp_net_index = '' emp_depcode = '' count = 0 for row in sheet: if count == 0: col_count = 0 # find index of following columns in foxpro excel sheet. for col in row: if col.value == 'PR_NEWCODE': empcode_index = col_count elif col.value == 'DEDUCT': emp_deduct_index = col_count elif col.value == 'NET': emp_net_index = col_count elif col.value == 'PR_DEPCODE': emp_depcode = col_count col_count += 1 # create header in comparison sheet for index in range(1, 6): comp_sheet.cell( row=1, column=index).value = comparison_sheet_col[index] count += 1 continue empcode = row[empcode_index].value # employee salary result set emp_sal_resultset = emp_gross_sal.emp_gross_sal(empcode, month, year) ''' Process employee record from fox salary row and Prodigious Resultset to create two maps. 1) Foxpro Map 2) Prodigious Map Both map will have common keys for direct and simple comparison. A common value is set to represnt salary heads of both systems. ''' prod_net_amount = 0.0 prod_deduction = 0.0 if emp_sal_resultset: for record in emp_sal_resultset: if record[1] == 0: prod_net_amount = record[2] # prodigious net elif record[1] == 1: prod_deduction = record[2] # prodigious deduction #compare_emp_salary(empcode, fox_salary, prod_salary,row,count) #print(count) fox_net_amount = row[emp_net_index].value fox_deduction = row[emp_deduct_index].value comp_sheet.cell(row=count, column=1).value = empcode comp_sheet.cell(row=count, column=2).value = fox_net_amount comp_sheet.cell(row=count, column=3).value = prod_net_amount comp_sheet.cell(row=count, column=4).value = fox_deduction comp_sheet.cell(row=count, column=5).value = prod_deduction count += 1 Connection.connection_close() Connection.close_cursor() # output file path comparison_workbook.save('E:\\Salary\Jan2019\\Salary total Comp_2.xlsx')
try: cursor.execute(query.GET_HEAD_AMOUNT_FROM_PAYSLIP,(emp, salhead, payperiod)) amount = list(cursor.fetchone())[0] print("{0} -> {1}".format(emp, amount)) cursor.execute(query.UPDATE_EMP_AMOUNT_SALHISTORY,(amount * -1,fromdate, emp, salhead)) except: print("{0} salary is not generated".format(emp)) con.commit() ''' def get_salhead_details(emp, fromdate): payperiod = '9||01/12/2018||31/12/2018||0' cursor = con.cursor() try: print(emp) cursor.execute(query.SALARY_DETAILS_PAYSLIP, (emp, payperiod)) recordset = cursor.fetchall() if recordset: print("{0}->{1}".format(emp, " ".join(recordset))) except Exception as ex: print("{0} -> {1}".format(emp, ex)) if __name__ == '__main__': con = Connection.get_connection() file_name = 'E:\Salary\Conveyance Subsidy Uploaded.xlsx' sheet_name = 'Conveyance Subsidy' get_emp_amount(file_name, sheet_name, '9||CC', con) con.close()