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()