import datetime import json from datetime import timezone,datetime import numpy as np import pandas as pd from django.db.models import Q from django.http import HttpResponse from django.template import loader from django.template.loader import get_template from xhtml2pdf import pisa from io import BytesIO from npayroll.settings import BASE_DIR, STATIC_ROOT from payrollservice.controller import emppaystructurecontroller from payrollservice.data.response.empmonthlypaydetailsresponse import Employeemonthlypay_detailsResponse from payrollservice.models import Employeemonthlypay_details, Employeemonthlypay_deductions, Employeemonthly_payinfo, \ EmployeePaystructure_details from payrollservice.service.auditservice import AudtiService from payrollservice.service.empmonthlypaydeductionservice import Employeemonthlypay_deductionsService from payrollservice.service.emppaystructdeductionservice import EmployeePaystructure_deductionsService from payrollservice.service.emppaystructuredetailsservice import EmployeePaystructure_detailsService from payrollservice.service.payrollmastersservice import PayrollComponentService, EmployeePFService, \ CompanyContributionService, SegmentMasterService from payrollservice.util.payrollutil import Activestatus, Advancetype, ModifyStatus, is_advance_data, data_roundup, \ float_to_decimal, netpay_float_to_decimal, net_pay_calc, data_roundup_arr from utilityservice.data.response.empmessage import SuccessMessage from utilityservice.data.response.nwisefinerror import NWisefinError from utilityservice.data.response.nwisefinerrorconstants import ErrorMessage, ErrorDescription from utilityservice.data.response.nwisefinlist import NWisefinList from utilityservice.data.response.nwisefinpaginator import NWisefinPaginator from utilityservice.data.response.nwisefinsuccess import SuccessStatus, NWisefinSuccess from utilityservice.permissions.util.dbutil import ModuleList from utilityservice.service.applicationconstants import ApplicationNamespace from utilityservice.service.payroll_api_service import Payrollcommon_Apicall from utilityservice.service.threadlocal import NWisefinThread from django.utils import timezone class Employeemonthlypay_detailsService(NWisefinThread): def __init__(self, scope): super().__init__(scope) self._set_namespace(ApplicationNamespace.PAYROLL_SERVICE) self.logopath = str(STATIC_ROOT) + "//logo.png" print(self.logopath) def create_employeemonthly_detail(self, request, allowance_obj, user_id, data, gross_pay): success_obj = NWisefinSuccess() success_obj.set_status(SuccessStatus.SUCCESS) if allowance_obj.get_id() is not None: emp_details = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter( id=allowance_obj.get_id()).update( empmonthly_pay_id=data, paycomponent=allowance_obj.get_paycomponent(), paycomponent_type=allowance_obj.get_paycomponent_type(), paycomponent_percentage=allowance_obj.get_paycomponent_percentage(), company_contribution=allowance_obj.get_company_contribution(), type=allowance_obj.get_type(), from_date=allowance_obj.get_from_date(), to_date=allowance_obj.get_to_date(), updated_by=user_id, updated_date=timezone.now(), entity_id=self._entity_id(), amount=float(gross_pay) * float(allowance_obj.get_paycomponent_percentage()) / 100, segment=allowance_obj.get_segment()) emp_details = Employeemonthlypay_details.objects.using(self._current_app_schema()).get( id=allowance_obj.get_id()) audit_insert_data = AudtiService(self._scope()).audit_function(emp_details, emp_details.id, user_id, Advancetype.employeemonthlypay_details, ModifyStatus.UPDATE) success_obj.set_message(SuccessMessage.UPDATE_MESSAGE) else: emp_details = Employeemonthlypay_details.objects.using(self._current_app_schema()).create( empmonthly_pay_id=data, paycomponent=allowance_obj.get_paycomponent(), paycomponent_type=allowance_obj.get_paycomponent_type(), paycomponent_percentage=allowance_obj.get_paycomponent_percentage(), company_contribution=allowance_obj.get_company_contribution(), type=allowance_obj.get_type(), from_date=allowance_obj.get_from_date(), to_date=allowance_obj.get_to_date(), created_by=user_id, entity_id=self._entity_id(), amount=float(gross_pay) * float(allowance_obj.get_paycomponent_percentage()) / 100, segment=allowance_obj.get_segment()) audit_insert_data = AudtiService(self._scope()).audit_function(emp_details, emp_details.id, user_id, Advancetype.employeemonthlypay_details, ModifyStatus.CREATE) success_obj.set_message(SuccessMessage.CREATE_MESSAGE) success_obj.id = emp_details.id return success_obj def employeemonthlypay_detail_get(self, empmonthly_id): empmonth_data = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(empmonthly_pay_id=empmonthly_id,company_contribution=False, status=Activestatus.active) paycomponent = [i.paycomponent for i in empmonth_data] # pay_com_obj = Payrollcommon_Apicall(self._scope()).get_payroll_component(paycomponent) pay_com_obj = PayrollComponentService(self._scope()).get_multiple_payroll_component_val(paycomponent) # pay_com_obj = PayrollComponentService(self._scope()).get_multiple_payroll_component(paycomponent) amount_arr = [i.amount for i in empmonth_data] amount = data_roundup_arr(amount_arr) segment_id = [i.segment for i in empmonth_data] # seg_dta = Payrollcommon_Apicall(self._scope()).get_segment_dta(segment_id) seg_dta = SegmentMasterService(self._scope()).get_multiple_segment_data(segment_id) arr = [] for empmonth_pay in empmonth_data: data_resp = Employeemonthlypay_detailsResponse() data_resp.set_id(empmonth_pay.id) data_resp.set_paycomponent_val(empmonth_pay.paycomponent, pay_com_obj) data_resp.set_paycomponent_type(empmonth_pay.paycomponent_type) # if empmonth_pay.company_contribution == 1: # company_con_obj = CompanyContributionService(self._scope()).get_multiple_contribution(paycomponent) # data_resp.set_company_contribution_val(empmonth_pay.paycomponent, company_con_obj) data_resp.set_paycomponent_percentage(empmonth_pay.paycomponent_percentage) data_resp.set_amount(empmonth_pay.amount) # data_resp.sum_amount = str(sum(amount)) data_resp.sum_amount = data_roundup(sum(amount)) data_resp.set_type(empmonth_pay.type) data_resp.set_from_date(empmonth_pay.from_date) data_resp.set_to_date(empmonth_pay.to_date) month_percentage = empmonth_pay.paycomponent_percentage data_resp.set_segment_data(empmonth_pay.segment, seg_dta) if month_percentage is None: percentage = 0.00 else: percentage = empmonth_pay.paycomponent_percentage # data_resp.gross_pay_amount = str(float(empmonth_pay.empmonthly_pay.gross_pay)*float(percentage/100)) data_resp.gross_pay_amount = data_roundup(float(empmonth_pay.empmonthly_pay.gross_pay)*float(percentage/100)) # data_resp.gross_pay_amount = str(empmonth_pay.empmonthly_pay.gross_pay*empmonth_pay.paycomponent_percentage/100) # data_resp.set_company_contribution(empmonth_pay.company_contribution) arr.append(data_resp) return arr # segment based def segment_employeemonthlypay_detail_get(self, empmonth_data,segement_id): if len(empmonth_data) > 0: empmonth_data1 = empmonth_data.filter(status=Activestatus.active, segment=segement_id) paycomponent = [i.paycomponent for i in empmonth_data1] # pay_com_obj = Payrollcommon_Apicall(self._scope()).get_payroll_component(paycomponent) pay_com_obj = PayrollComponentService(self._scope()).get_multiple_payroll_component_val(paycomponent) amount = [i.amount for i in empmonth_data1] arr = [] for empmonth_pay in empmonth_data1: data_resp = Employeemonthlypay_detailsResponse() data_resp.set_id(empmonth_pay.id) data_resp.set_paycomponent_val(empmonth_pay.paycomponent, pay_com_obj) data_resp.set_paycomponent_type(empmonth_pay.paycomponent_type) data_resp.set_paycomponent_percentage(empmonth_pay.paycomponent_percentage) data_resp.set_amount(empmonth_pay.amount) data_resp.sum_amount = str(sum(amount)) data_resp.set_type(empmonth_pay.type) data_resp.set_from_date(empmonth_pay.from_date) data_resp.set_to_date(empmonth_pay.to_date) month_percentage = empmonth_pay.paycomponent_percentage if month_percentage is None: percentage = 0.00 else: percentage = empmonth_pay.paycomponent_percentage data_resp.gross_pay_amount = str(float(empmonth_pay.empmonthly_pay.gross_pay)*float(percentage/100)) arr.append(data_resp) return arr # segment based details data def segment_details(self,empmonthly_id): empmonth_data = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(empmonthly_pay_id=empmonthly_id, company_contribution=False, status=Activestatus.active) segment_id = empmonth_data.values('segment').distinct() segment_id_list = [i['segment'] for i in segment_id] # segment_data = Payrollcommon_Apicall(self._scope()).segment_details_data(segment_id_list) segment_data = SegmentMasterService(self._scope()).segment_details_data(segment_id_list) self.segment_data = None arr = [] for i in segment_data: for j in segment_id_list: if i.id == j: segment = i monthly_data = self.segment_employeemonthlypay_detail_get(empmonth_data, j) Employeepay_details_data = monthly_data arr.append(segment) arr.append({"Employeempay_details_data":Employeepay_details_data}) return arr def employeemonthly_detail_get(self, id): empmonth_allowance_obj = Employeemonthlypay_details.objects.using(self._current_app_schema()).get(status=Activestatus.active,id=id) data_resp = Employeemonthlypay_detailsResponse() data_resp.set_id(empmonth_allowance_obj.id) data_resp.set_amount(empmonth_allowance_obj.amount) data_resp.set_empmonthly_pay_id(empmonth_allowance_obj.empmonthly_pay_id) # pay_com_obj = Payrollcommon_Apicall(self._scope()).get_component_val(empmonth_allowance_obj.paycomponent) pay_com_obj = PayrollComponentService(self._scope()).get_payroll_component(empmonth_allowance_obj.paycomponent) data_resp.paycomponent = pay_com_obj if empmonth_allowance_obj.company_contribution == 1: # company_con_obj = Payrollcommon_Apicall(self._scope()).get_companycontribution_multi_data(empmonth_allowance_obj.paycomponent) company_con_obj = CompanyContributionService(self._scope()).get_multiple_contribution(empmonth_allowance_obj.paycomponent) data_resp.set_company_contribution_val(empmonth_allowance_obj.paycomponent, company_con_obj) data_resp.set_paycomponent_type(empmonth_allowance_obj.paycomponent_type) data_resp.set_paycomponent_percentage(empmonth_allowance_obj.paycomponent_percentage) data_resp.set_is_deduction(empmonth_allowance_obj.is_deduction) data_resp.set_type(empmonth_allowance_obj.type) data_resp.set_from_date(empmonth_allowance_obj.from_date) data_resp.set_to_date(empmonth_allowance_obj.to_date) data_resp.set_company_contribution(empmonth_allowance_obj.company_contribution) # segment_dta = Payrollcommon_Apicall(self._scope()).single_segment_dta(empmonth_allowance_obj.segment) segment_dta = SegmentMasterService(self._scope()).get_segmentmaster(empmonth_allowance_obj.segment) data_resp.set_segment(segment_dta) return data_resp def employeemonthly_detail_inactive(self, id, user_id): employeemon_obj = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(id=id).update(status=Activestatus.inactive, updated_by=user_id, updated_date=timezone.now()) if employeemon_obj > 0: success_obj = NWisefinSuccess() success_obj.set_status(SuccessStatus.SUCCESS) success_obj.set_message(SuccessMessage.DELETE_MESSAGE) else: success_obj = NWisefinError() success_obj.set_code(ErrorMessage.UNEXPECTED_ERROR) success_obj.set_description(ErrorDescription.INVALID_DATA) return success_obj def empmonthdetails_summary(self,id): emp_details = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(empmonthly_pay_id__in=id, company_contribution=False, status=Activestatus.active) resp_arr = [] paycomponent = [i.paycomponent for i in emp_details] amount = [i.amount for i in emp_details] segment_id = [i.segment for i in emp_details] # segment_dta = Payrollcommon_Apicall(self._scope()).get_segment_dta(segment_id) segment_dta =SegmentMasterService(self._scope()).get_multiple_segment_data(segment_id) # pay_com_obj = Payrollcommon_Apicall(self._scope()).get_payroll_component(paycomponent) pay_com_obj = PayrollComponentService(self._scope()).get_multiple_payroll_component_val(paycomponent) for empmonth in emp_details: resp = Employeemonthlypay_detailsResponse() resp.set_id(empmonth.id) resp.set_empmonthly_pay_id(empmonth.empmonthly_pay_id) resp.set_paycomponent_val(empmonth.paycomponent, pay_com_obj) # if empmonth.company_contribution == 1: # company_con_obj = Payrollcommon_Apicall(self._scope()).get_companycontribution_multi_data(paycomponent) # # company_con_obj = CompanyContributionService(self._scope()).get_multiple_contribution(paycomponent) # resp.set_company_contribution_val(empmonth.paycomponent, company_con_obj) resp.set_paycomponent_type(empmonth.paycomponent_type) resp.set_paycomponent_percentage(empmonth.paycomponent_percentage) resp.set_amount(empmonth.amount) resp.sum_amount = str(sum(amount)) resp.set_type(empmonth.type) resp.set_from_date(empmonth.from_date) resp.set_to_date(empmonth.to_date) resp.set_segment_data(empmonth.segment,segment_dta) # resp.set_is_deduction(empmonth.is_deduction) # emp_deduction = self.emppaystruct_deduct(empmonth.id) # resp.Employeemonthlypay_deduct_data = emp_deduction resp_arr.append(resp) return resp_arr def employeemonthlypay_detailinfo_get(self, employee_id, year): try: empmonth_data = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(status=Activestatus.active,empmonthly_pay__employee_id=employee_id, empmonthly_pay__payroll_date__year=year, company_contribution=False) deduct_data = Employeemonthlypay_deductions.objects.using(self._current_app_schema()).filter(status=Activestatus.active, employee_id=employee_id, deduct_date__year=year) month_info = empmonth_data.order_by().values('empmonthly_pay__payroll_date', 'empmonthly_pay__paid_days').distinct() paycomponent_list = self.paycomponent_month_details(employee_id, year, empmonth_data) apifunction = Payrollcommon_Apicall(self._scope()) emp_data = apifunction.emp_details_payroll(employee_id) date = str(emp_data.get('doj')) if date != 'None': employee_doj = datetime.strptime(date, "%Y-%m-%d").strftime("%d %b %Y") else: employee_doj = date # employee_doj = datetime.strptime(emp_data['doj'], "%Y-%m-%d").strftime("%b %Y") segment_id = [i.segment for i in empmonth_data] # segment_dta = Payrollcommon_Apicall(self._scope()).get_segment_dta(segment_id) segment_dta = SegmentMasterService(self._scope()).get_multiple_segment_data(segment_id) dummy_record = [{'amount': '', 'id': '', 'paycomponent': n} for n in paycomponent_list] arr = [] import calendar arr2 = [] for month in range(1, 13): month_name = calendar.month_name[month].capitalize() year_bet = f"{month_name} {year}" arr2.append(year_bet) arr3 = [] for month_deduct in deduct_data: data_res = Employeemonthlypay_detailsResponse() data_res.set_id(month_deduct.id) data_res.set_amount(month_deduct.amount) if month_deduct.deduction_status == 1 and month_deduct.is_advance == 0: # pf_struct = Payrollcommon_Apicall(self._scope()).get_component_name([month_deduct.paycomponent_id]) pf_struct = PayrollComponentService(self._scope()).get_payrollcomponent_name([month_deduct.paycomponent_id]) elif month_deduct.deduction_status == -1 and month_deduct.is_advance == 0: # pf_struct = EmployeePFService(self._scope()).get_pf_name([month_deduct.paycomponent_id]) # pf_struct = Payrollcommon_Apicall(self._scope()).get_pf_name([month_deduct.paycomponent_id]) pf_struct = EmployeePFService(self._scope()).get_pf_name([month_deduct.paycomponent_id]) elif month_deduct.deduction_status == 1 and month_deduct.is_advance == 1: #advance pf_struct = is_advance_data.data['name'] data_res.paycomponent =pf_struct # data_res.payroll_date = str(month_deduct.created_date.date()) # from_date = datetime.strptime(str(month_deduct.created_date.date()), "%Y-%m-%d") # from_datem = str(from_date.strftime("%Y-%m")) # data_res.condition_date = from_datem data_res.payroll_date = str(month_deduct.deduct_date) from_date1 = datetime.strptime(str(month_deduct.deduct_date), "%Y-%m-%d") from_datem1 = str(from_date1.strftime("%Y-%m")) data_res.condition_date = from_datem1 arr3.append(json.loads(data_res.get())) for empmonth_pay in empmonth_data: data_resp = Employeemonthlypay_detailsResponse() data_resp.set_id(empmonth_pay.id) data_resp.payroll_date = str(empmonth_pay.empmonthly_pay.payroll_date) from_date1 = datetime.strptime(str(empmonth_pay.empmonthly_pay.payroll_date), "%Y-%m-%d") from_datem1 = str(from_date1.strftime("%Y-%m")) data_resp.condition_date = from_datem1 # if empmonth_pay.company_contribution == 1: # company_con_obj = CompanyContributionService(self._scope()).get_contribution([empmonth_pay.paycomponent]) # data_resp.paycomponent = company_con_obj # else: # pay_com_obj = Payrollcommon_Apicall(self._scope()).get_component_name([empmonth_pay.paycomponent]) pay_com_obj = PayrollComponentService(self._scope()).get_payrollcomponent_name([empmonth_pay.paycomponent]) # pay_com_obj = PayrollComponentService(self._scope()).get_payrollcomponent_name([empmonth_pay.paycomponent]) data_resp.paycomponent = pay_com_obj data_resp.set_amount(empmonth_pay.amount) data_resp.set_segment_data(empmonth_pay.segment,segment_dta) arr.append(json.loads(data_resp.get())) arr1=[] date_arr=[] f_date = [] for i in month_info: date = str(i['empmonthly_pay__payroll_date']) from_date2 = datetime.strptime(str(date), "%Y-%m-%d") from_datem2 = str(from_date2.strftime("%Y-%m")) paid_days = str(i['empmonthly_pay__paid_days']) df = pd.DataFrame(arr) df1 = pd.DataFrame(arr3) df1['amount'] = pd.to_numeric(df1['amount']) df1['condition_date1'] = pd.to_datetime(df1['condition_date']) df1['month'] = df1['condition_date1'].dt.month df1['year'] = df1['condition_date1'].dt.year grouped_df = df1.groupby(['paycomponent', 'year', 'month'])['amount'].sum().reset_index() deduct_df1 = pd.merge(df1, grouped_df, on=['paycomponent', 'year', 'month'], how='outer') deduct_df2 = deduct_df1[deduct_df1['paycomponent'] == 'ADVANCE'] # Drop duplicates based on YearMonth and PayComponent deduct_df2= deduct_df2.drop_duplicates(subset=['month','year', 'paycomponent']) deduct_df1 = deduct_df1[deduct_df1['paycomponent'] != 'ADVANCE'] df2 = pd.concat([deduct_df1, deduct_df2], axis=0) df2 = df2.drop(['amount_x', 'month', 'year', 'condition_date1'], axis=1) df2.rename(columns={'amount_y': 'amount'}, inplace=True) print(grouped_df) if len(df2) > 0: condition1 = df2['condition_date'] == from_datem2 filtered_df1 = df2[condition1] amount = filtered_df1['amount'].astype('float').sum() else: amount = 0.00 condition2 = df['condition_date'] == from_datem2 filtered_df2 = df[condition2] amount1 = filtered_df2['amount'].astype('float').sum() final_amount = abs(amount - amount1) df3 = pd.concat([df, df2], axis=0) condition = df3['condition_date'] == from_datem2 filtered_df = df3[condition] amount2 = filtered_df['amount'].astype('float').sum() final_df = filtered_df.drop(columns=['payroll_date','condition_date']) final_data = final_df.to_dict('records') formatted_date = datetime.strptime(date, "%Y-%m-%d").strftime("%B %Y") print(final_data) data = {"final_data": final_data, "formatted_date": formatted_date, "Paid Days":paid_days,"total_deduction":round(amount), "net_pay":round(final_amount), "total_earning":round(amount1)} date_arr.append(data) f_date.append(formatted_date) for item in arr2: if item in f_date: for j in date_arr: if j['formatted_date'] == item: paycom_list1 = [] for difference in j['final_data']: paycom_list1.append(difference['paycomponent']) missing_paycom = (set(paycomponent_list).difference(paycom_list1)) s =['TotalDeduct','PaidDays','TotalEarning','NetPay'] for k in missing_paycom: if k not in s: j['final_data'].append({'amount':'','id':None,'paycomponent':k,'segment':None}) dict = {"payroll_date": j['formatted_date'], "PaidDays": j['Paid Days'], "Employeemonthlypay_details_data": j['final_data'], "TotalDeduct": j['total_deduction'], "TotalEarning": j['total_earning'], "NetPay": j['net_pay']} else: dict = {"payroll_date":item, "Employeemonthlypay_details_data":dummy_record} arr1.append(dict) data = {"employee_personal_info": emp_data, 'pay_info_month': arr1, 'paycomponent': paycomponent_list, "employee_doj":employee_doj,"year":year} return data except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def paycomponent_month_details(self, employee_id, year,empmonth_data): # empmonth_data = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(status=Activestatus.active, empmonthly_pay__employee_id=employee_id,empmonthly_pay__payroll_date__year=year, company_contribution=False) data = [] if empmonth_data.count() > 0: empmonth_data_df = pd.DataFrame(empmonth_data.values('id', 'paycomponent')) paycomponent_id = [i.paycomponent for i in empmonth_data] # condition = empmonth_data_df['company_contribution'].isnull().values.any() # if condition == True: # company_obj = CompanyContribution.objects.using(self._current_app_schema()).filter(id__in=paycomponent_id) # company_obj_df = pd.DataFrame(company_obj.values('id', 'name')) # empmonth_df1 = empmonth_data_df.merge(company_obj_df, left_on='paycomponent', right_on='id', how='inner') # else: # paycomponent_obj = Payrollcommon_Apicall(self._scope()).get_paycom_details(paycomponent_id) paycomponent_obj = PayrollComponentService(self._scope()).get_paycom_details(paycomponent_id) # paycomponent_obj = PayrollComponent.objects.using(self._current_app_schema()).filter(id__in=paycomponent_id) paycomponent_obj_df = pd.DataFrame(paycomponent_obj.values('id', 'name')) empmonth_df1 = empmonth_data_df.merge(paycomponent_obj_df, left_on='paycomponent', right_on='id', how='inner') concat_month = empmonth_df1.drop(columns=['id_x', 'paycomponent']) deduct_data = Employeemonthlypay_deductions.objects.using(self._current_app_schema()).filter(status=Activestatus.active, employee_id=employee_id, deduct_date__year=year) dedut_paycom_id = [i.paycomponent_id for i in deduct_data] if deduct_data.count() > 0: arr=[] for k in deduct_data: if k.deduction_status == 1 and k.is_advance == 0: # paycomponent_obj = Payrollcommon_Apicall(self._scope()).get_paycom_details([k.paycomponent_id]) paycomponent_obj = PayrollComponentService(self._scope()).get_paycom_details([k.paycomponent_id]) # paycomponent_obj = PayrollComponent.objects.using(self._current_app_schema()).filter(id__in=paycomponent_id) # paycomponent_df = pd.DataFrame(paycomponent_obj.values('id', 'name')) paycomponent_data = paycomponent_obj.values('id', 'name') elif k.deduction_status == -1 and k.is_advance == 0: # dedut_paycom_id_obj = Payrollcommon_Apicall(self._scope()).get_employeepf_details([k.paycomponent_id]) dedut_paycom_id_obj = EmployeePFService(self._scope()).get_employeepf_details([k.paycomponent_id]) # dedut_paycom_id_obj_df = pd.DataFrame(dedut_paycom_id_obj.values('id', 'name')) paycomponent_data = dedut_paycom_id_obj.values('id', 'name') elif k.deduction_status == 1 and k.is_advance == 1:#advance paycomponent_data = [is_advance_data.data] arr.append(paycomponent_data) dicts = [v[0] for v in arr] df = pd.DataFrame(dicts) # deduct_df = pd.concat([paycomponent_df, dedut_paycom_id_obj_df], join='inner') data_concat = pd.concat([concat_month, df], join='inner') else: data_concat = concat_month data = data_concat.groupby(['name']).count().reset_index() final_d = data.rename(columns={'name': 'values'}, inplace=True) final_data = data['values'].values.tolist() final_data1 = ['TotalDeduct', 'NetPay', 'TotalEarning', 'PaidDays'] final_data += final_data1 return final_data else: return data def employeemonthlyyear_excel_report(self, employee_id, year): try: import calendar arr2 = [] for month in range(1, 13): month_name = calendar.month_name[month].capitalize() year_bet = f"{month_name} {year}" arr2.append(year_bet) # employeemonthly empmonth_data = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(status=Activestatus.active, empmonthly_pay__employee_id=employee_id,empmonthly_pay__payroll_date__year=year, company_contribution=False) month_info = empmonth_data.order_by().values('empmonthly_pay__payroll_date', 'empmonthly_pay__paid_days').distinct() paycomponent_list = self.paycomponent_month_details(employee_id, year, empmonth_data) empmonth_data_df = pd.DataFrame(empmonth_data.values('empmonthly_pay_id', 'paycomponent', 'amount')) if empmonth_data.count() > 0: paycomponent_id = [i.paycomponent for i in empmonth_data] # condition = empmonth_data_df['company_contribution'].values.any() # condition = empmonth_data_df['company_contribution'] == True # if condition == True: # company_obj = CompanyContribution.objects.using(self._current_app_schema()).filter(id__in=paycomponent_id) # company_obj_df = pd.DataFrame(company_obj.values('id', 'name')) # empmonth_df1 = empmonth_data_df.merge(company_obj_df, left_on='paycomponent', right_on='id', how='inner') # else: # paycomponent_obj = Payrollcommon_Apicall(self._scope()).get_paycom_details(paycomponent_id) paycomponent_obj = PayrollComponentService(self._scope()).get_paycom_details(paycomponent_id) # paycomponent_obj = PayrollComponent.objects.using(self._current_app_schema()).filter(id__in=paycomponent_id) paycomponent_obj_df = pd.DataFrame(paycomponent_obj.values('id', 'name')) empmonth_df1 = empmonth_data_df.merge(paycomponent_obj_df, left_on='paycomponent', right_on='id', how='inner') deduct_data = Employeemonthlypay_deductions.objects.using(self._current_app_schema()).filter( status=Activestatus.active, employee_id=employee_id,deduct_date__year=year) if deduct_data.count() > 0: deduct_info = deduct_data.order_by().values('deduct_date').distinct() deduct_data_df = pd.DataFrame( deduct_data.values('id', 'employee_id', 'paycomponent_id', 'amount', 'employee_id', 'deduct_date', 'type','deduction_status')) arr = [] for k in deduct_data: if k.deduction_status == 1 and k.is_advance == 0: # paycomponent_obj = Payrollcommon_Apicall(self._scope()).get_paycom_details([k.paycomponent_id]) paycomponent_obj = PayrollComponentService(self._scope()).get_paycom_details([k.paycomponent_id]) paycomponent_data = paycomponent_obj.values('id', 'name') elif k.deduction_status == -1 and k.is_advance == 0: # dedut_paycom_id_obj = Payrollcommon_Apicall(self._scope()).get_employeepf_details([k.paycomponent_id]) dedut_paycom_id_obj = EmployeePFService(self._scope()).get_employeepf_details([k.paycomponent_id]) paycomponent_data = dedut_paycom_id_obj.values('id', 'name') elif k.deduction_status == 1 and k.is_advance == 1: # advance paycomponent_data = [is_advance_data.data] arr.append(paycomponent_data) dicts = [v[0] for v in arr] df = pd.DataFrame(dicts) deduct_df1 = deduct_data_df.merge(df, left_on='paycomponent_id', right_on='id',how='inner') drop_deduct_df1 = deduct_df1.drop_duplicates('id_x') print(drop_deduct_df1) drop_deduct = drop_deduct_df1.drop(columns=['id_x', 'employee_id', 'id_y']) drop_deduct['deduct_date'] = pd.to_datetime(drop_deduct['deduct_date']) date = drop_deduct['deduct_date'] drop_deduct['deduct_date'] = date.dt.strftime("%B %Y") drop_deduct.rename(columns={'paycomponent_id': 'paycomponent', 'deduct_date': 'payroll_date'},inplace=True) final_deduct = drop_deduct[['paycomponent', 'amount', 'name', 'payroll_date']] emp_pay_id = [i.empmonthly_pay.id for i in empmonth_data] payinfo_obj = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter( status=Activestatus.active, id__in=emp_pay_id) payinfo_obj_df = pd.DataFrame(payinfo_obj.values('id', 'payroll_date')) details_info_df = empmonth_df1.merge(payinfo_obj_df, left_on='empmonthly_pay_id', right_on='id', how='inner') drop_month = details_info_df.drop(columns=['id_x', 'id_y', 'empmonthly_pay_id']) drop_month['payroll_date'] = pd.to_datetime(drop_month['payroll_date']) date1 = drop_month['payroll_date'] drop_month['payroll_date'] = date1.dt.strftime("%B %Y") data_concat = pd.concat([drop_month, final_deduct], join='inner') else: emp_pay_id = [i.empmonthly_pay.id for i in empmonth_data] payinfo_obj = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter( status=Activestatus.active, id__in=emp_pay_id) payinfo_obj_df = pd.DataFrame(payinfo_obj.values('id', 'payroll_date')) details_info_df = empmonth_df1.merge(payinfo_obj_df, left_on='empmonthly_pay_id', right_on='id', how='inner') drop_month = details_info_df.drop(columns=['id_x', 'id_y', 'empmonthly_pay_id']) drop_month['payroll_date'] = pd.to_datetime(drop_month['payroll_date']) date1 = drop_month['payroll_date'] drop_month['payroll_date'] = date1.dt.strftime("%B %Y") data_concat = drop_month date_arr = [] for j in month_info: date = str(j['empmonthly_pay__payroll_date']) formatted_date = datetime.strptime(date, "%Y-%m-%d").strftime("%B %Y") paid_days = str(j['empmonthly_pay__paid_days']) if deduct_data.count() > 0: # for k in deduct_info: # # from_date = datetime.strptime(str(k.from_date), "%Y-%m-%d") # # from_datem = from_date.strftime("%Y-%m") condition1 = final_deduct['payroll_date'] == formatted_date filtered_df1 = final_deduct[condition1] amount = filtered_df1['amount'].astype('float').sum() else: amount = 0.00 condition2 = drop_month['payroll_date'] == formatted_date filtered_df2 = drop_month[condition2] amount1 = filtered_df2['amount'].astype('float').sum() condition3 = data_concat['payroll_date'] == formatted_date filtered_df3 = data_concat[condition3] amount2 = filtered_df3['amount'].astype('float').sum() final_amount = abs(amount - amount1) data = {"TotalDeduct": round(amount), "NetPay": round(final_amount), "TotalEarning": round(amount1), "PaidDays": paid_days, "payroll_date": formatted_date} date_arr.append(data) total_dataframe = pd.DataFrame(date_arr) pivot = total_dataframe.pivot_table(columns='payroll_date',values=['TotalDeduct', 'NetPay', 'TotalEarning','PaidDays']).reset_index() pivot.rename(columns={"index": "name"}, inplace=True) column_names = ['Item'] + arr2 df = pd.DataFrame(None, columns=column_names) df['Item'] = paycomponent_list df.sort_index(inplace=True) piv = data_concat.pivot_table(index='name', columns='payroll_date', values='amount').reset_index() final_concat = pd.concat([piv, pivot], join='inner') final_value = final_concat.reset_index() final_drop = final_value.drop(columns=['index']) df1 = final_drop.merge(df, left_on='name', right_on='Item', how='inner') column_names = final_drop.columns.values df1['name'] = '' df1[column_names] = final_drop[column_names] else: import calendar arr2 = [] for month in range(1, 13): month_name = calendar.month_name[month].capitalize() year_bet = f"{month_name} {year}" arr2.append(year_bet) column_names = ['Item'] + arr2 df1 = pd.DataFrame(None, columns=column_names) df1['Item'] = paycomponent_list df1.sort_values("Item", inplace=True) return df1 except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def employeemonthlypay_detailinfo_get1(self, employee_id, year): try: empmonth_data = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(status=Activestatus.active,empmonthly_pay__employee_id=employee_id, empmonthly_pay__payroll_date__year=year, company_contribution=False) deduct_data = Employeemonthlypay_deductions.objects.using(self._current_app_schema()).filter(status=Activestatus.active, employee_id=employee_id, deduct_date__year=year) month_info = empmonth_data.order_by().values('empmonthly_pay__payroll_date', 'empmonthly_pay__paid_days').distinct() paycomponent_list = self.paycomponent_month_details(employee_id, year, empmonth_data) segment_id = [i.segment for i in empmonth_data] # segment_dta = Payrollcommon_Apicall(self._scope()).get_segment_dta(segment_id) segment_dta = SegmentMasterService(self._scope()).get_multiple_segment_data(segment_id) arr = [] import calendar arr2 = [] for month in range(1, 13): month_name = calendar.month_abbr[month].capitalize() year_bet = f"{month_name} {year}" arr2.append(year_bet) arr3 = [] for month_deduct in deduct_data: data_res = Employeemonthlypay_detailsResponse() data_res.set_id(month_deduct.id) data_res.set_amount(month_deduct.amount) if month_deduct.deduction_status == 1 and month_deduct.is_advance == 0: # pf_struct = Payrollcommon_Apicall(self._scope()).get_component_name([month_deduct.paycomponent_id]) pf_struct = PayrollComponentService(self._scope()).get_payrollcomponent_name([month_deduct.paycomponent_id]) elif month_deduct.deduction_status == -1 and month_deduct.is_advance == 0: # pf_struct = Payrollcommon_Apicall(self._scope()).get_pf_name([month_deduct.paycomponent_id]) pf_struct = EmployeePFService(self._scope()).get_pf_name([month_deduct.paycomponent_id]) elif month_deduct.deduction_status == 1 and month_deduct.is_advance == 1: # advance pf_struct = is_advance_data.data['name'] data_res.paycomponent =pf_struct data_res.payroll_date = str(month_deduct.deduct_date) from_date1 = datetime.strptime(str(month_deduct.deduct_date), "%Y-%m-%d") from_datem1 = str(from_date1.strftime("%Y-%m")) data_res.condition_date = from_datem1 arr3.append(json.loads(data_res.get())) for empmonth_pay in empmonth_data: data_resp = Employeemonthlypay_detailsResponse() data_resp.set_id(empmonth_pay.id) data_resp.payroll_date = str(empmonth_pay.empmonthly_pay.payroll_date) from_date1 = datetime.strptime(str(empmonth_pay.empmonthly_pay.payroll_date), "%Y-%m-%d") from_datem1 = str(from_date1.strftime("%Y-%m")) data_resp.condition_date = from_datem1 # pay_com_obj = Payrollcommon_Apicall(self._scope()).get_component_name([empmonth_pay.paycomponent]) pay_com_obj = PayrollComponentService(self._scope()).get_payrollcomponent_name([empmonth_pay.paycomponent]) data_resp.paycomponent = pay_com_obj data_resp.set_amount(empmonth_pay.amount) data_resp.set_segment_data(empmonth_pay.segment, segment_dta) arr.append(json.loads(data_resp.get())) arr1=[] date_arr=[] f_date = [] for i in month_info: date = str(i['empmonthly_pay__payroll_date']) from_date2 = datetime.strptime(str(date), "%Y-%m-%d") from_datem2 = str(from_date2.strftime("%Y-%m")) paid_days = str(i['empmonthly_pay__paid_days']) df = pd.DataFrame(arr) df1 = pd.DataFrame(arr3) df1['amount'] = pd.to_numeric(df1['amount']) df1['condition_date1'] = pd.to_datetime(df1['condition_date']) df1['month'] = df1['condition_date1'].dt.month df1['year'] = df1['condition_date1'].dt.year grouped_df = df1.groupby(['paycomponent', 'year', 'month'])['amount'].sum().reset_index() deduct_df1 = pd.merge(df1, grouped_df, on=['paycomponent', 'year', 'month'], how='outer') deduct_df2 = deduct_df1[deduct_df1['paycomponent'] == 'ADVANCE'] # Drop duplicates based on YearMonth and PayComponent deduct_df2 = deduct_df2.drop_duplicates(subset=['month', 'year', 'paycomponent']) deduct_df1 = deduct_df1[deduct_df1['paycomponent'] != 'ADVANCE'] df2 = pd.concat([deduct_df1, deduct_df2], axis=0) # df2 = pd.merge(df1, grouped_df, on=['paycomponent', 'year', 'month'], how='outer') df2 = df2.drop(['amount_x','month','year','condition_date1'],axis=1) df2.rename(columns={'amount_y': 'amount'}, inplace=True) print(grouped_df) if len(df2) > 0: condition1 = df2['condition_date'] == from_datem2 filtered_df1 = df2[condition1] amount = filtered_df1['amount'].astype('float').sum() else: amount = 0.00 condition2 = df['condition_date'] == from_datem2 filtered_df2 = df[condition2] amount1 = filtered_df2['amount'].astype('float').sum() final_amount = abs(amount - amount1) df3 = pd.concat([df, df2], axis=0) condition = df3['condition_date'] == from_datem2 filtered_df = df3[condition] amount2 = filtered_df['amount'].astype('float').sum() final_df = filtered_df.drop(columns=['payroll_date','condition_date']) paycomponent_df = paycomponent_list[:len(paycomponent_list) - 4] component_df = pd.DataFrame(paycomponent_df) component_df.rename(columns={0: 'paycomponent'}, inplace=True) details_info_df = component_df.merge(final_df, left_on='paycomponent', right_on='paycomponent',how='left') final_df1 = details_info_df.fillna("") final_data = final_df1.to_dict('records') formatted_date = datetime.strptime(date, "%Y-%m-%d").strftime("%b %Y") print(final_data) data = {"final_data": final_data, "formatted_date": formatted_date, "paid_days":paid_days,"total_deduction":round(amount), "net_pay":round(final_amount), "total_earning":round(amount1)} date_arr.append(data) f_date.append(formatted_date) for item in arr2: if item in f_date: for j in date_arr: if j['formatted_date'] == item: dict = {"payroll_date": j['formatted_date'], "PaidDays": j['paid_days'], "Employeemonthlypay_details_data": j['final_data'], "TotalDeduct": j['total_deduction'], "TotalEarning": j['total_earning'],"NetPay":j['net_pay']} else: dict = {"payroll_date":item, "Employeemonthlypay_details_data":[]} arr1.append(dict) arr1.append(paycomponent_list) return arr1 except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def salary_excel_downloade(self): try: date_time_data = datetime.datetime.now() month = date_time_data.strftime('%m') obj = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(status=Activestatus.active,empmonthly_pay__payroll_date__month=month) data_val = EmployeePaystructure_details.objects.using(self._current_app_schema()).filter(status=Activestatus.active,emp_pay__created_date__month=month) print(data_val) emp_pay_id = [i.empmonthly_pay.id for i in obj] paycomponent_id = [i.paycomponent for i in obj] payinfo_obj = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(status=Activestatus.active,id__in=emp_pay_id) employee_id = [i.employee_id for i in payinfo_obj] apifunction = Payrollcommon_Apicall(self._scope()) emp_data = apifunction.employee_id_get(employee_id) employee_df = pd.DataFrame(emp_data) # paycomponent_obj = apifunction.get_paycom_details(paycomponent_id) paycomponent_obj = PayrollComponentService(self._scope()).get_paycom_details(paycomponent_id) # paycomponent_obj = PayrollComponent.objects.using(self._current_app_schema()).filter(id__in=paycomponent_id) deduction_amount = Employeemonthlypay_deductions.objects.using(self._current_app_schema()).filter(status=Activestatus.active,employee_id__in=employee_id) emp_dedu_df = pd.DataFrame(deduction_amount.values()) df2 = pd.DataFrame(paycomponent_obj.values()) df1 = pd.DataFrame(obj.values()) data_df = pd.DataFrame(payinfo_obj.values()) employee_df_val = data_df.merge(employee_df, left_on='employee_id', right_on='id', how='inner') emp_ded_data_df = employee_df_val.merge(emp_dedu_df,left_on='id_y',right_on='employee_id',how='inner') detail_df = df1.merge(df2, left_on='paycomponent', right_on='id', how='inner') p = pd.pivot_table(data=detail_df, index='empmonthly_pay_id', columns='name', values='amount', fill_value=0) reindex_val = p.reset_index() merge_df = emp_ded_data_df.merge(reindex_val, left_on='id_x', right_on='empmonthly_pay_id', how='inner') return merge_df except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def payroll_review(self, month, year,excel,vys_page,query,pay_status,emp_cc,emp_bs): arr = [] one_month_pre = int(month) - int(1) arr.append(int(month) - int(1)) two_month_pre = int(month)-int(2) arr.append(int(two_month_pre)) arr.append(int(month)) from django.db.models import F condition=Q(status=Activestatus.active) apifunction = Payrollcommon_Apicall(self._scope()) if emp_cc != None and emp_cc != "" or emp_bs != None and emp_bs != "": emp_list = apifunction.emp_arr_cc_bs(emp_cc,emp_bs) condition &= Q(employee_id__in=emp_list) if query==1: condition&=Q(paid_days=F('payable_days')) if query==2: condition&=~Q(paid_days=F('payable_days')) if pay_status: condition&=Q(pay_status=pay_status) else: condition&=Q(pay_status=8) if int(excel): emp_ids = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condition,payroll_date__month__in=arr,payroll_date__year=year, is_deduct=1).values('employee_id', 'id') employee_id = [i['employee_id'] for i in emp_ids] id__in_dta = [i['id'] for i in emp_ids] pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter( empmonthly_pay__employee_id__in=employee_id,company_contribution=False,status=Activestatus.active,empmonthly_pay_id__in=id__in_dta).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id') # pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(empmonthly_pay__paid_days=condition,empmonthly_pay__payroll_date__month__in=arr,empmonthly_pay__payroll_date__year=year, company_contribution=False).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type', 'empmonthly_pay__payable_days', 'empmonthly_pay__paid_days','empmonthly_pay__id') else: emp_ids = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condition,payroll_date__month__in=arr,payroll_date__year=year, is_deduct=1).values('employee_id', 'id').order_by("employee_id")#[vys_page.get_offset():vys_page.get_query_limit()] employee_id_val = [i['employee_id'] for i in emp_ids] set1 = set(employee_id_val) result = list(set1)[vys_page.get_offset():vys_page.get_query_limit()] empmonth = emp_ids.filter(employee_id__in=result) id__in_dta = [i['id'] for i in empmonth] employee_id = [i['employee_id'] for i in empmonth] if len(empmonth)>0: pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(empmonthly_pay__employee_id__in=employee_id,company_contribution=False,status=Activestatus.active,empmonthly_pay_id__in=id__in_dta).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id') emp_list = employee_id if len(emp_ids)>0: employee_cc_bs = apifunction.emp_data_cc_bs(emp_list) employee_cc_bs_df = pd.DataFrame([vars(s) for s in employee_cc_bs], columns=['id', 'costcentre','businesssegment']) employee_paystucture = EmployeePaystructure_detailsService(self._scope()).employee_paystucture_details(emp_list,arr) emp_paystruct_deductions = EmployeePaystructure_deductionsService(self._scope()).Structure_deductioninfo(emp_list) emp_paystruct_deductions=pd.DataFrame.from_records(emp_paystruct_deductions) std_df = pd.DataFrame.from_records(employee_paystucture) std_withbonus = self.groupby_sum__(std_df, '') std_withbonus.columns = ['employee_id', 'amount', 'bonus'] s_d = emp_paystruct_deductions.groupby('employee_id')['amount'].sum().reset_index() s_d.columns = ['employee_id', 'deduction_amout'] fin_std_df = s_d.merge(std_withbonus, on='employee_id', how='left') fin_std_df = fin_std_df.fillna('0') # fin_std_df = std_withbonus.merge(s_d, on='employee_id') # std_b=groupby_sum__(deductiondf,'') empmonthly_deduction = Employeemonthlypay_deductionsService(self._scope()).employee_dedution_info(emp_list, arr, year) monthly_df=pd.DataFrame.from_records(pay_stru_detail_dist) deductiondf=pd.DataFrame.from_records(empmonthly_deduction) deductiondf['year'] = pd.DatetimeIndex(deductiondf['deduct_date']).year deductiondf['month'] = pd.DatetimeIndex(deductiondf['deduct_date']).month monthly = monthly_df.groupby(['empmonthly_pay__employee_id', 'empmonthly_pay__payroll_date', 'empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id'])['amount'].sum().reset_index() monthly_bonus = monthly_df[monthly_df['type'].notnull()] MONTH_b = monthly_bonus.groupby('empmonthly_pay__id')['amount'].sum().reset_index() MONTH_b.columns = ['empmonthly_pay__id', 'bonus_amount'] final_monthly = pd.merge(monthly, MONTH_b, on='empmonthly_pay__id',how='left') final_monthly = final_monthly.fillna('0') final_monthly.columns = ['employee_id', 'payroll_date', 'payable_days', 'paid_days', 'empmonthly_payid', 'amount', 'bonus'] final_monthly['year'] = pd.DatetimeIndex(final_monthly['payroll_date']).year final_monthly['month'] = pd.DatetimeIndex(final_monthly['payroll_date']).month deductiondf[['year', 'month']] = deductiondf[['year', 'month']].astype(object) final_monthly = final_monthly.merge(deductiondf, left_on=['employee_id', 'year', 'month'], right_on=['employee_id', 'year', 'month'], how='inner') final_monthly = pd.concat([final_monthly, fin_std_df]) print(final_monthly) final_monthly = final_monthly.fillna('0000:00:00') final_monthly['payroll_date'] = final_monthly['payroll_date'].astype('str') final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(month)),'headings']='Current Month' final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(one_month_pre)),'headings']='CM-1' final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(two_month_pre)),'headings']='CM-2' final_monthly.loc[final_monthly['payroll_date'] =='0000:00:00','headings']='Std' Apicall=Payrollcommon_Apicall(self._scope()) employee_data = Apicall.get_emp_list_info(final_monthly['employee_id'].to_list()) emp_fin_df1 = final_monthly.merge(employee_data, left_on='employee_id', right_on='id', how='left') emp_fin_df2 = emp_fin_df1.merge(employee_cc_bs_df, left_on='employee_id', right_on='id', how='left') emp_fin_df = emp_fin_df2.drop(['id_y'], axis=1) if not excel: list_data = NWisefinList() res = emp_fin_df.to_json(orient="records") emp_fin_df = json.loads(res) list_data.data = emp_fin_df vpage = NWisefinPaginator(result, vys_page.get_index(), 10) list_data.pagination=vpage return list_data return emp_fin_df else: if not excel: list_data = NWisefinList() vpage = NWisefinPaginator([], vys_page.get_index(), 10) list_data.pagination = vpage else: list_data = pd.DataFrame() return list_data def payroll_review1(self, month, year,excel,vys_page,query,pay_status,emp_cc,emp_bs,employee): current_month_pay_status = None arr = [] one_month_pre = int(month) - int(1) arr.append(int(month) - int(1)) two_month_pre = int(month)-int(2) arr.append(int(two_month_pre)) arr.append(int(month)) from django.db.models import F condition=Q(status=Activestatus.active) apifunction = Payrollcommon_Apicall(self._scope()) if emp_cc != None and emp_cc != "" or emp_bs != None and emp_bs != "": emp_list = apifunction.emp_arr_cc_bs(emp_cc,emp_bs) condition &= Q(employee_id__in=emp_list) if employee != None and employee != "": # emp_list = apifunction.emp_arr_function(employee) condition &= Q(employee_id=employee) if query==1: condition&=Q(paid_days=F('payable_days')) if query==2: condition&=~Q(paid_days=F('payable_days')) if pay_status: current_month_pay_status = pay_status else: current_month_pay_status = 8 # current_month_pay_status = 8 previous_two_months_pay_status = [1,6] condition_current_month = condition & Q(payroll_date__month=month, pay_status=current_month_pay_status,payroll_date__year=year) condition_previous_two_months = condition & Q(payroll_date__month__in=[one_month_pre, two_month_pre],pay_status__in=previous_two_months_pay_status, payroll_date__year=year) if int(excel): emp_ids = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condition,payroll_date__month__in=arr,payroll_date__year=year, is_deduct=1).values('employee_id', 'id') emp_ids_current_month = emp_ids.filter(condition_current_month) emp_ids_previous_two_months = emp_ids.filter(condition_previous_two_months) df_emp_ids_current_month = pd.DataFrame(emp_ids_current_month) df_emp_ids_previous_two_months = pd.DataFrame(emp_ids_previous_two_months) data_concat = pd.concat([df_emp_ids_current_month, df_emp_ids_previous_two_months], join='inner') if data_concat.empty: data = self.empty_values(excel, vys_page) return data employee_id = data_concat['employee_id'].to_list() id__in_dta = data_concat['id'].to_list() # employee_id = [i['employee_id'] for i in emp_ids] # id__in_dta = [i['id'] for i in emp_ids] pay_stru_detail_list = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter( empmonthly_pay__employee_id__in=employee_id,company_contribution=False,status=Activestatus.active,empmonthly_pay_id__in=id__in_dta).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id','empmonthly_pay__take_home') # pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(empmonthly_pay__paid_days=condition,empmonthly_pay__payroll_date__month__in=arr,empmonthly_pay__payroll_date__year=year, company_contribution=False).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type', 'empmonthly_pay__payable_days', 'empmonthly_pay__paid_days','empmonthly_pay__id') else: emp_ids = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condition, is_deduct=1).values('employee_id', 'id').order_by("employee_id")#[vys_page.get_offset():vys_page.get_query_limit()] emp_ids_current_month = emp_ids.filter(condition_current_month) emp_ids_previous_two_months = emp_ids.filter(condition_previous_two_months) df_emp_ids_current_month = pd.DataFrame(emp_ids_current_month) df_emp_ids_previous_two_months = pd.DataFrame(emp_ids_previous_two_months) if df_emp_ids_previous_two_months.empty: df_emp_ids_previous_two_months = pd.DataFrame(columns=['employee_id', 'id']) data_concat = pd.concat([df_emp_ids_current_month, df_emp_ids_previous_two_months], join='inner') if data_concat.empty: data = self.empty_values(excel, vys_page) return data employee_id_val = data_concat['employee_id'].to_list() id_val = data_concat['id'].to_list() # employee_id_val = [i['employee_id'] for i in emp_ids] set1 = set(employee_id_val) result = list(set1)[vys_page.get_offset():vys_page.get_query_limit()] empmonth = emp_ids.filter(employee_id__in=result) id__in_data = [i['id'] for i in empmonth] id__in_dta = list(set(id__in_data).intersection(set(id_val))) employee_id = [i['employee_id'] for i in empmonth] if len(empmonth)>0: pay_stru_detail_list = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(empmonthly_pay__employee_id__in=employee_id,company_contribution=False,status=Activestatus.active,empmonthly_pay_id__in=id__in_dta).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id','empmonthly_pay__take_home') emp_list = employee_id if len(emp_ids)>0: employee_cc_bs = apifunction.emp_data_cc_bs(emp_list) employee_cc_bs_df = pd.DataFrame([vars(s) for s in employee_cc_bs], columns=['id', 'costcentre','businesssegment']) employee_paystucture = EmployeePaystructure_detailsService(self._scope()).employee_paystucture_details1(emp_list,arr) emp_paystruct_deductions = EmployeePaystructure_deductionsService(self._scope()).Structure_deductioninfo(emp_list) emp_paystruct_deductions=pd.DataFrame.from_records(emp_paystruct_deductions) std_df = pd.DataFrame.from_records(employee_paystucture) std_withbonus = self.groupby_sum__1(std_df, '') std_withbonus.columns = ['employee_id','take_home w/o bonus','bonus'] std_withbonus = std_withbonus.fillna('0') std_withbonus['take_home w/o bonus']=std_withbonus['take_home w/o bonus'].astype(float) std_withbonus['bonus']=std_withbonus['bonus'].astype(float) std_withbonus['take_home'] = std_withbonus['take_home w/o bonus'] + std_withbonus['bonus'] empmonthly_deduction = Employeemonthlypay_deductionsService(self._scope()).employee_dedution_info1(emp_list, arr, year) empmonthly_deduction1 = Employeemonthlypay_deductionsService(self._scope()).employee_dedution_data1(emp_list, arr, year) monthly_df=pd.DataFrame.from_records(pay_stru_detail_list) monthly_df = self.roundup_amt(monthly_df) from decimal import Decimal monthly_df['amount'] = monthly_df['amount'].apply(lambda x: Decimal(x) if isinstance(x, str) else x) deductiondf=pd.DataFrame.from_records(empmonthly_deduction) deductiondf1=pd.DataFrame.from_records(empmonthly_deduction1) deductiondf1['deduct_date']=pd.to_datetime(deductiondf1['deduct_date'],errors='coerce') deductiondf1['month'] = deductiondf1['deduct_date'].dt.month deductiondf1['year'] = deductiondf1['deduct_date'].dt.year # condtion_1 = deductiondf1['deduction_status'] == -1 # condition_2 = deductiondf1['is_advance'] == False # result = deductiondf1.loc[condtion_1 & condition_2] monthly_sum_amt = monthly_df.groupby(['empmonthly_pay__employee_id', 'empmonthly_pay__payroll_date', 'empmonthly_pay__payable_days','empmonthly_pay__paid_days', 'empmonthly_pay__id'])['amount'].sum().reset_index() monthly_sum_amt['empmonthly_pay__payroll_date'] = pd.to_datetime(monthly_sum_amt['empmonthly_pay__payroll_date'], errors='coerce') monthly_sum_amt['month'] = monthly_sum_amt['empmonthly_pay__payroll_date'].dt.month monthly_sum_amt['year'] = monthly_sum_amt['empmonthly_pay__payroll_date'].dt.year dedcut_monthly = deductiondf1.groupby(['employee_id','month','year'])['amount'].sum().reset_index() month_dedcut_monthly = dedcut_monthly[['employee_id','amount','month','year']] # calcul_final_tk1 = monthly_sum_amt.merge(month_dedcut_monthly, left_on='empmonthly_pay__employee_id', right_on='employee_id',how='left') calcul_final_tk1 = monthly_sum_amt.merge(month_dedcut_monthly, left_on=['empmonthly_pay__employee_id','month','year'], right_on=['employee_id','month','year'],how='left') calcul_final_tk1 = calcul_final_tk1.fillna('0') calcul_final_tk1['take_home'] = (calcul_final_tk1['amount_x'].astype(float).sub(calcul_final_tk1['amount_y'].astype(float)))#(amount_x--grosspay,amount_y--deduction) find with bonus takehome calcul_final_tk1 = self.roundup_totalamt(calcul_final_tk1) monthly = calcul_final_tk1.drop(['amount_x','amount_y','employee_id','month','year'],axis=1) monthly_bonus = monthly_df[monthly_df['type'].notnull()] MONTH_b = monthly_bonus.groupby('empmonthly_pay__id')['amount'].sum().reset_index() MONTH_b.columns = ['empmonthly_pay__id', 'bonus_amount'] final_monthly = pd.merge(monthly, MONTH_b, on='empmonthly_pay__id', how='left') final_monthly = final_monthly.fillna('0') final_monthly.columns = ['employee_id', 'payroll_date', 'payable_days', 'paid_days', 'empmonthly_payid','take_home', 'bonus'] final_monthly['take_home w/o bonus'] = (final_monthly['take_home'].astype(float).sub(final_monthly['bonus'].astype(float))) final_monthly['year'] = pd.DatetimeIndex(final_monthly['payroll_date']).year final_monthly['month'] = pd.DatetimeIndex(final_monthly['payroll_date']).month if deductiondf.empty: data = {'employee_id': [], 'deduct_date__month': [], 'deduct_date__year': [], 'deduct_date': [], 'deduction_amout': [], 'year': [], 'month': []} deduction_df = pd.DataFrame(data) else: deductiondf['year'] = pd.DatetimeIndex(deductiondf['deduct_date']).year deductiondf['month'] = pd.DatetimeIndex(deductiondf['deduct_date']).month deductiondf[['year', 'month']] = deductiondf[['year', 'month']].astype(object) # final_monthly = final_monthly.merge(deductiondf, on=['employee_id'], how='left') final_monthly = final_monthly.merge(deductiondf, left_on=['employee_id','month','year'] ,right_on=['employee_id','month','year'], how='left') if 'deduct_date__month' not in final_monthly.columns: final_monthly['deduct_date__month'] = 0.0 if 'deduct_date__year' not in final_monthly.columns: final_monthly['deduct_date__year'] = 0.0 if 'deduct_date' not in final_monthly.columns: final_monthly['deduct_date'] = 0.0 if 'deduction_amout' not in final_monthly.columns: final_monthly['deduction_amout'] = 0.0 final_monthly = pd.concat([final_monthly, std_withbonus]) # final_monthly = final_monthly.drop(['year_y','month_y'],axis=1) final_monthly['deduction_amout'].replace({np.nan:0}, inplace=True) # final_monthly['take_home'] = (final_monthly['take_home'].astype(float).sub(final_monthly['deduction_amout'].astype(float))) final_monthly = final_monthly.fillna('0000:00:00') final_monthly['payroll_date'] = final_monthly['payroll_date'].astype('str') # final_monthly = final_monthly.drop_duplicates(subset=['empmonthly_payid', '', '']) final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(month)),'headings']='Current Month' final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(one_month_pre)),'headings']='CM-1' final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(two_month_pre)),'headings']='CM-2' final_monthly.loc[final_monthly['payroll_date'] =='0000:00:00','headings']='Std' Apicall=Payrollcommon_Apicall(self._scope()) employee_data = Apicall.get_emp_list_info(final_monthly['employee_id'].to_list()) emp_fin_df1 = final_monthly.merge(employee_data, left_on='employee_id', right_on='id', how='left') emp_fin_df2 = emp_fin_df1.merge(employee_cc_bs_df, left_on='employee_id', right_on='id', how='left') emp_fin_df = emp_fin_df2.drop(['id_y'], axis=1) emp_fin_df = emp_fin_df[['employee_id', 'payroll_date', 'payable_days', 'paid_days', 'empmonthly_payid','take_home w/o bonus', 'bonus','take_home', 'year', 'month', 'deduct_date__month', 'deduct_date__year', 'deduct_date', 'deduction_amout', 'headings', 'id_x', 'full_name', 'code','employee_branch__code', 'employee_branch__name', 'doj', 'department', 'costcentre', 'businesssegment']] if not excel: list_data = NWisefinList() res = emp_fin_df.to_json(orient="records") emp_fin_df = json.loads(res) list_data.data = emp_fin_df vpage = NWisefinPaginator(result, vys_page.get_index(), 10) list_data.pagination=vpage return list_data return emp_fin_df else: data = self.empty_values(excel,vys_page) return data def empty_values(self,excel,vys_page): if not excel: list_data = NWisefinList() vpage = NWisefinPaginator([], vys_page.get_index(), 10) list_data.pagination = vpage else: list_data = pd.DataFrame() return list_data def groupby_sum__(self,std_df, structure): structure = std_df.groupby('emp_pay__employee_id')['amount'].sum().reset_index() std_bonus = std_df[std_df['type'].notnull()] std_b = std_bonus.groupby('emp_pay__employee_id')['amount'].sum().reset_index() std_b.columns = ['emp_pay__employee_id', 'bonus_amount'] std_b = pd.merge(structure, std_b, on='emp_pay__employee_id',how='left') return std_b def groupby_sum__1(self, std_df, structure): structure_takehome = std_df.groupby(["emp_pay__employee_id", "emp_pay__take_home"]).count().reset_index() takehome = structure_takehome.drop(['paycomponent', 'amount', 'type', 'from_date', 'to_date'], axis=1) std_bonus = std_df[std_df['type'].notnull()] std_b = std_bonus.groupby('emp_pay__employee_id')['amount'].sum().reset_index() std_b.columns = ['emp_pay__employee_id', 'bonus_amount'] std_b1 = pd.merge(takehome, std_b, on='emp_pay__employee_id', how='left') return std_b1 # payapproval summary a person with multiple payslip for a month # def payroll_review(self, month, year,excel,vys_page,query,pay_status,emp_cc,emp_bs): # arr = [] # one_month_pre = int(month) - int(1) # arr.append(int(month) - int(1)) # two_month_pre = int(month)-int(2) # arr.append(str(two_month_pre)) # arr.append(str(month)) # from django.db.models import F # condition=Q(status=Activestatus.active) # apifunction = Payrollcommon_Apicall(self._scope()) # if emp_cc != None and emp_cc != "" or emp_bs != None and emp_bs != "": # emp_list = apifunction.emp_arr_cc_bs(emp_cc,emp_bs) # condition &= Q(employee_id__in=emp_list) # if query==1: # condition&=Q(paid_days=F('payable_days')) # if query==2: # condition&=~Q(paid_days=F('payable_days')) # if pay_status: # condition&=Q(pay_status=pay_status) # else: # condition&=Q(pay_status=8) # if int(excel): # emp_ids = list(Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condition,payroll_date__month__in=arr,payroll_date__year=year).values_list('employee_id', flat=True)) # pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter( empmonthly_pay__employee_id__in=emp_ids,company_contribution=False,status=Activestatus.active).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id','empmonthly_pay__code','empmonthly_pay__is_deduct') # # pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(empmonthly_pay__paid_days=condition,empmonthly_pay__payroll_date__month__in=arr,empmonthly_pay__payroll_date__year=year, company_contribution=False).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type', 'empmonthly_pay__payable_days', 'empmonthly_pay__paid_days','empmonthly_pay__id') # else: # emp_ids = list(Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condition,payroll_date__month__in=arr,payroll_date__year=year).values_list('employee_id', flat=True)[vys_page.get_offset():vys_page.get_query_limit()]) # if len(emp_ids)>0: # pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter( empmonthly_pay__employee_id__in=emp_ids,company_contribution=False,status=Activestatus.active).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id','empmonthly_pay__code','empmonthly_pay__is_deduct') # emp_list = emp_ids # if len(emp_ids)>0: # employee_cc_bs = apifunction.emp_data_cc_bs(emp_list) # employee_cc_bs_df = pd.DataFrame([vars(s) for s in employee_cc_bs], columns=['id', 'costcentre','businesssegment']) # employee_paystucture = EmployeePaystructure_detailsService(self._scope()).employee_paystucture_details(emp_list,arr) # emp_paystruct_deductions = EmployeePaystructure_deductionsService(self._scope()).Structure_deductioninfo(emp_list) # emp_paystruct_deductions=pd.DataFrame.from_records(emp_paystruct_deductions) # std_df = pd.DataFrame.from_records(employee_paystucture) # std_withbonus = self.groupby_sum__(std_df, '') # std_withbonus.columns = ['employee_id', 'amount', 'bonus'] # s_d = emp_paystruct_deductions.groupby('employee_id')['amount'].sum().reset_index() # s_d.columns = ['employee_id', 'deduction_amout'] # fin_std_df = s_d.merge(std_withbonus, on='employee_id', how='left') # fin_std_df = fin_std_df.fillna('0') # # fin_std_df = std_withbonus.merge(s_d, on='employee_id') # # std_b=groupby_sum__(deductiondf,'') # empmonthly_deduction = Employeemonthlypay_deductionsService(self._scope()).employee_dedution_info(emp_list, arr, year) # monthly_df=pd.DataFrame.from_records(pay_stru_detail_dist) # deductiondf=pd.DataFrame.from_records(empmonthly_deduction) # deductiondf['year'] = pd.DatetimeIndex(deductiondf['deduct_date']).year # deductiondf['month'] = pd.DatetimeIndex(deductiondf['deduct_date']).month # monthly = monthly_df.groupby(['empmonthly_pay__employee_id', 'empmonthly_pay__payroll_date', 'empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id', 'empmonthly_pay__code','empmonthly_pay__is_deduct'])['amount'].sum().reset_index() # monthly_bonus = monthly_df[monthly_df['type'].notnull()] # MONTH_b = monthly_bonus.groupby('empmonthly_pay__id')['amount'].sum().reset_index() # MONTH_b.columns = ['empmonthly_pay__id', 'bonus_amount'] # final_monthly = pd.merge(monthly, MONTH_b, on='empmonthly_pay__id',how='left') # final_monthly = final_monthly.fillna('0') # final_monthly.columns = ['employee_id', 'payroll_date', 'payable_days', 'paid_days', 'empmonthly_payid', 'empmonthly_pay__code','empmonthly_pay__is_deduct','amount', 'bonus'] # final_monthly['year'] = pd.DatetimeIndex(final_monthly['payroll_date']).year # final_monthly['month'] = pd.DatetimeIndex(final_monthly['payroll_date']).month # final_monthly1 = final_monthly.loc[final_monthly['empmonthly_pay__is_deduct'] == True] # result_df = final_monthly.loc[final_monthly['empmonthly_pay__is_deduct'] == False] # deductiondf[['year', 'month']] = deductiondf[['year', 'month']].astype(object) # final_monthly = final_monthly1.merge(deductiondf, left_on=['employee_id', 'year', 'month'], right_on=['employee_id', 'year', 'month'], how='inner') # resultdf1 = pd.concat([final_monthly,result_df]) # resultdf2 = pd.merge(resultdf1, fin_std_df, on='employee_id', how='outer') # final_df = resultdf2.drop(['payroll_date','payable_days','paid_days', 'empmonthly_payid', 'empmonthly_pay__code','amount_x','bonus_x','year','month','deduct_date__month','deduct_date__year','deduct_date','deduction_amout_x'], axis=1) # final_df.columns = ['employee_id','empmonthly_pay__is_deduct','deduction_amout','amount','bonus'] # final_monthly = pd.concat([resultdf1, final_df]) # print(final_monthly) # final_monthly = final_monthly.fillna('0000:00:00') # final_monthly['payroll_date'] = final_monthly['payroll_date'].astype('str') # final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(month)),'headings']='Current Month' # final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(one_month_pre)),'headings']='CM-1' # final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(two_month_pre)),'headings']='CM-2' # final_monthly.loc[final_monthly['payroll_date'] =='0000:00:00','headings']='Std' # Apicall=Payrollcommon_Apicall(self._scope()) # employee_data = Apicall.get_emp_list_info(final_monthly['employee_id'].to_list()) # emp_fin_df1 = final_monthly.merge(employee_data, left_on='employee_id', right_on='id', how='left') # emp_fin_df2 = emp_fin_df1.merge(employee_cc_bs_df, left_on='employee_id', right_on='id', how='left') # emp_fin_df = emp_fin_df2.drop(['id_y','empmonthly_pay__is_deduct'], axis=1) # if not excel: # list_data = NWisefinList() # res = emp_fin_df.to_json(orient="records") # emp_fin_df = json.loads(res) # list_data.data = emp_fin_df # vpage = NWisefinPaginator(emp_fin_df, vys_page.get_index(), 10) # list_data.pagination=vpage # return list_data # return emp_fin_df # else: # if not excel: # list_data = NWisefinList() # vpage = NWisefinPaginator([], vys_page.get_index(), 10) # list_data.pagination = vpage # else: # list_data = pd.DataFrame() # return list_data def payroll_review_data_df(self,month,year): arr = [] apifunction = Payrollcommon_Apicall(self._scope()) emp_obj = apifunction.employee_overall_data() id_data = [i.id for i in emp_obj] one_month_pre = int(month) - int(1) arr.append(one_month_pre) two_month_pre = int(month) - int(2) arr.append(two_month_pre) arr.append(month) pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter( empmonthly_pay__payroll_date__month__in=arr, empmonthly_pay__employee_id__in=id_data, company_contribution=False,status=Activestatus.active).values('id','paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payable_days', 'empmonthly_pay__payroll_date', 'type', 'empmonthly_pay__payable_days').distinct() data_df = pd.DataFrame(pay_stru_detail_dist) emp_id = [i['empmonthly_pay__employee_id'] for i in pay_stru_detail_dist] emp_id = set(emp_id) emp_id = list(emp_id) employee_df = pd.DataFrame(columns=['id', 'emp_id', str(month), str(one_month_pre),str(two_month_pre)]) employee_df['emp_id'] = emp_id print(employee_df) employee_df[str(month)] = data_df['id'].apply(lambda x: x['empmonthly_pay__payroll_date']) def salary_statement_summary(self): date_time_data = datetime.datetime.now() month = date_time_data.strftime('%m') year = date_time_data.strftime('%Y') obj = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(status=Activestatus.active,empmonthly_pay__created_date__year=year, empmonthly_pay__created_date__month=month) emp_pay_id = [i.empmonthly_pay.id for i in obj] paycomponent_id = [i.paycomponent for i in obj] payinfo_obj = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(status=Activestatus.active, id__in=emp_pay_id) employee_id = [i.employee_id for i in payinfo_obj] apifunction = Payrollcommon_Apicall(self._scope()) emp_data = apifunction.employee_id_get(employee_id) employee_df = pd.DataFrame(emp_data) # paycomponent_obj = Payrollcommon_Apicall(self._scope()).get_paycom_details(paycomponent_id) paycomponent_obj = PayrollComponentService(self._scope()).get_paycom_details(paycomponent_id) # paycomponent_obj = PayrollComponent.objects.using(self._current_app_schema()).filter(id__in=paycomponent_id) deduction_amount = Employeemonthlypay_deductions.objects.using(self._current_app_schema()).filter(status=Activestatus.active, employee_id__in=employee_id) emp_dedu_df = pd.DataFrame(deduction_amount.values()) df2 = pd.DataFrame(paycomponent_obj.values()) df1 = pd.DataFrame(obj.values()) data_df = pd.DataFrame(payinfo_obj.values()) employee_df_val = data_df.merge(employee_df, left_on='employee_id', right_on='id', how='inner') emp_ded_data_df = employee_df_val.merge(emp_dedu_df, left_on='id_y', right_on='employee_id', how='inner') detail_df = df1.merge(df2, left_on='paycomponent', right_on='id', how='inner') p = pd.pivot_table(data=detail_df, index='empmonthly_pay_id', columns='name', values='amount', fill_value=0) reindex_val = p.reset_index() merge_df = emp_ded_data_df.merge(reindex_val, left_on='id_x', right_on='empmonthly_pay_id', how='inner') return merge_df def salarystatement_forallemp_cm(self,request,c_m,c_y,user_id): apifunction = Payrollcommon_Apicall(self._scope()) condition = Q(entity_id=self._entity_id(), status=Activestatus.active) month = request.GET.get('month') year = request.GET.get('year') if month != None and month != "": condition &= Q(empmonthly_pay__payroll_date__month=month) if year != None and year != "": condition &= Q(empmonthly_pay__payroll_date__year=year) struct_w_ot_cmpycontribtn, structdeduction_df1, struct_cmpy_contribution, monthly_w_ot_cmpycontribtn, monthlydeduction_df1, monthly_cmpy_contribution = (pd.DataFrame(),) * 6 # date_time_data = datetime.datetime.now() # month = date_time_data.strftime('%m') # year = date_time_data.strftime('%Y') module = ModuleList.Employee_Payroll hr_rights = apifunction.employee_role_check(user_id, module, None, None) if hr_rights == 1: monthly_query = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(condition).values('paycomponent','company_contribution','amount','type','empmonthly_pay__employee_id','empmonthly_pay__code','empmonthly_pay__gross_pay','empmonthly_pay__payable_days','empmonthly_pay__paid_days') else: monthly_query = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(condition,empmonthly_pay__employee_id=user_id).values('paycomponent', 'company_contribution', 'amount', 'type', 'empmonthly_pay__employee_id', 'empmonthly_pay__code', 'empmonthly_pay__gross_pay','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days') if monthly_query.count()>0: M_Q_to_df=pd.DataFrame.from_records(monthly_query) # m = M_Q_to_df.pivot_table(index='empmonthly_pay__employee_id', columns='paycomponent',values='amount').reset_index() emp_list=M_Q_to_df['empmonthly_pay__employee_id'].to_list() structure_emp_info=EmployeePaystructure_detailsService(self._scope()).emp_structure_info_withcb(emp_list) structure_deduction=EmployeePaystructure_deductionsService(self._scope()).Structure_deductioninfo(emp_list) struct_df=pd.DataFrame.from_records(structure_emp_info) structdeduction_df=pd.DataFrame.from_records(structure_deduction) struct_cb = struct_df.loc[struct_df['company_contribution'] == True] struct_w_ot_cb = struct_df.loc[struct_df['company_contribution'] == False] Apicall=Payrollcommon_Apicall(self._scope()) if structdeduction_df.empty is not True: # empcb_data = Apicall.get_employeepf_details(structdeduction_df['paycomponent_id'].to_list()) empcb_data = EmployeePFService(self._scope()).get_employeepf_details(structdeduction_df['paycomponent_id'].to_list()) structdeduction_df1 = self.common_merge(empcb_data, ['id', 'name'], 'id', 'paycomponent_id', 'left', structdeduction_df) structdeduction_df1.rename(columns={'paycomponent_id': 'paycomponent'}, inplace=True) structdeduction_df1 = structdeduction_df1[['employee_id', 'amount', 'paycomponent', 'name']] # structdeduction_df1=structdeduction_df1.add_prefix('Std Emp Con_') # structdeduction_df1.rename(columns={'Std Emp Con_employee_id': 'employee_id'}, inplace=True) structdeduction_df1['name'] = 'Std Emp deductions ' + structdeduction_df1['name'].astype(str) # struct_w_ot_cmpycontribtn['area']='emp' if struct_w_ot_cb.empty is not True: # paycomponent_data = Apicall.get_payroll_component(struct_w_ot_cb['paycomponent'].to_list()) paycomponent_data = PayrollComponentService(self._scope()).get_multiple_payroll_component_val(struct_w_ot_cb['paycomponent'].to_list()) struct_w_ot_cmpycontribtn = self.common_merge(paycomponent_data, ['id', 'name'], 'id', 'paycomponent','left', struct_w_ot_cb) struct_w_ot_cmpycontribtn = struct_w_ot_cmpycontribtn[['paycomponent', 'amount', 'type', 'emp_pay__employee_id', 'emp_pay__gross_pay', 'name']] struct_w_ot_cmpycontribtn.rename(columns={'emp_pay__employee_id': 'employee_id'}, inplace=True) struct_w_ot_cmpycontribtn['name'] = 'Std ' + struct_w_ot_cmpycontribtn['name'].astype(str) # struct_w_ot_cmpycontribtn['area'] = 'Std ' + struct_w_ot_cmpycontribtn['name'].astype(str) # struct_w_ot_cmpycontribtn=struct_w_ot_cmpycontribtn.add_prefix('Std_') # struct_w_ot_cmpycontribtn.rename(columns={'Std_': 'Std_employee_id'}, inplace=True) # struct_w_ot_cmpycontribtn['area'] = 'main' if struct_cb.empty is not True: # cb_data = Apicall.get_companycontribution_multi_data(struct_cb['paycomponent'].to_list()) cb_data = CompanyContributionService(self._scope()).get_multiple_contribution(struct_cb['paycomponent'].to_list()) struct_cmpy_contribution = self.common_merge(cb_data, ['id', 'name'], 'id', 'paycomponent', 'inner', struct_cb) struct_cmpy_contribution = struct_cmpy_contribution[['paycomponent', 'amount', 'type', 'emp_pay__employee_id', 'emp_pay__gross_pay', 'name']] struct_cmpy_contribution['name'] = 'Std Cmp' + struct_cmpy_contribution['name'].astype(str) # struct_cmpy_contribution=struct_cmpy_contribution.add_prefix('Std_') # struct_w_ot_cmpycontribtn['area'] = 'cmpy' struct_cmpy_contribution.rename(columns={'emp_pay__employee_id': 'employee_id'}, inplace=True) final_struct = pd.concat([struct_w_ot_cmpycontribtn, structdeduction_df1, struct_cmpy_contribution]) # pivt = final_struct.pivot_table(index=['employee_id'], columns=['name'], values='amount').reset_index() p = final_struct.groupby(["employee_id", "emp_pay__gross_pay"]).count().reset_index() pivt_data = final_struct.pivot_table(index=['employee_id'], columns=['name'], values='amount').reset_index() f = p.merge(pivt_data, how='left', on='employee_id') pivt=f.drop(['paycomponent', 'amount', 'type', 'name'], axis=1) # pivt['Find']='Std' monthly_cb = M_Q_to_df[M_Q_to_df['company_contribution'] == True] monthly_w_ot_cb = M_Q_to_df[M_Q_to_df['company_contribution'] == False] monthly_deductions = Employeemonthlypay_deductionsService(self._scope()).employee_dedution_data(emp_list, month, year) monthly_deductions = pd.DataFrame.from_records(monthly_deductions) if monthly_deductions.empty is not True: _normal_deduct = monthly_deductions[monthly_deductions['deduction_status'] == -1] _additional_deduct = monthly_deductions[(monthly_deductions['deduction_status'] == 1) & (monthly_deductions['is_advance'] == False)] # empcb_data = Apicall.get_employeepf_details(_normal_deduct['paycomponent_id'].to_list()) empcb_data = EmployeePFService(self._scope()).get_employeepf_details(_normal_deduct['paycomponent_id'].to_list()) # empcb_data2 = Apicall.get_paycom_details(_additional_deduct['paycomponent_id'].to_list()) empcb_data2 = PayrollComponentService(self._scope()).get_paycom_details(_additional_deduct['paycomponent_id'].to_list()) monthlydeduction_df_1 = self.common_merge(empcb_data, ['id', 'name'], 'id', 'paycomponent_id', 'left',_normal_deduct) monthlydeduction_df_2 = self.common_merge(empcb_data2, ['id', 'name'], 'id', 'paycomponent_id', 'left',_additional_deduct) monthlydeduction_df1 = pd.concat([monthlydeduction_df_1, monthlydeduction_df_2]) monthlydeduction_df1 = self._common_merge(monthlydeduction_df1, ['id', 'name'], 'id', 'paycomponent_id', 'left', monthly_deductions) monthlydeduction_df1 = monthlydeduction_df1[['employee_id', 'paycomponent_id', 'amount', 'name']] monthlydeduction_df1.rename(columns={'paycomponent_id': 'paycomponent', 'name_x': 'name'}, inplace=True) advance_data_id = is_advance_data.data['id'] advance_data_name = is_advance_data.data['name'] monthlydeduction_df1['paycomponent'] = monthlydeduction_df1['paycomponent'].fillna(advance_data_id) monthlydeduction_df1['name'] = monthlydeduction_df1['name'].fillna(advance_data_name) # empcb_data = Apicall.get_employeepf_details(monthly_deductions['paycomponent'].to_list()) # monthlydeduction_df1 = self.common_merge(empcb_data, ['id', 'name'], 'id', 'paycomponent', 'left', monthly_deductions) # monthlydeduction_df1.rename(columns={'paycomponent_id': 'paycomponent'}, inplace=True) # monthlydeduction_df1 = monthlydeduction_df1[['employee_id', 'amount', 'paycomponent', 'name']] # structdeduction_df1.rename(columns={'deduction_amout': 'amount'}, inplace=True) # monthlydeduction_df1['name'] = 'Emp deductions ' + monthlydeduction_df1['name'].astype(str) # monthlydeduction_df1['area']='emp' if monthly_w_ot_cb.empty is not True: # paycomponent_data = Apicall.get_payroll_component(monthly_w_ot_cb['paycomponent'].to_list()) paycomponent_data = PayrollComponentService(self._scope()).get_multiple_payroll_component_val(monthly_w_ot_cb['paycomponent'].to_list()) monthly_w_ot_cmpycontribtn = self.common_merge(paycomponent_data, ['id', 'name'], 'id', 'paycomponent', 'left', monthly_w_ot_cb) monthly_w_ot_cmpycontribtn = monthly_w_ot_cmpycontribtn[['paycomponent', 'amount', 'type', 'empmonthly_pay__employee_id', 'empmonthly_pay__gross_pay', 'name','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days']] monthly_w_ot_cmpycontribtn.rename(columns={'empmonthly_pay__employee_id': 'employee_id', 'empmonthly_pay__payable_days':'payable days', 'empmonthly_pay__paid_days': 'paid days'}, inplace=True) # monthly_w_ot_cmpycontribtn['area']='main' if monthly_cb.empty is not True: # cb_data = Apicall.get_companycontribution_multi_data(monthly_cb['paycomponent'].to_list()) cb_data = CompanyContributionService(self._scope()).get_multiple_contribution(monthly_cb['paycomponent'].to_list()) monthly_cmpy_contribution = self.common_merge(cb_data, ['id', 'name'], 'id', 'paycomponent', 'inner', monthly_cb) monthly_cmpy_contribution = monthly_cmpy_contribution[['paycomponent', 'amount', 'type', 'empmonthly_pay__employee_id', 'empmonthly_pay__gross_pay', 'name','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days']] monthly_cmpy_contribution.rename(columns={'empmonthly_pay__employee_id': 'employee_id','empmonthly_pay__payable_days':'payable days', 'empmonthly_pay__paid_days': 'paid days'}, inplace=True) # monthly_cmpy_contribution['area']='cmpy' monthly_cmpy_contribution['name'] = 'Cmpy cont' + struct_cmpy_contribution['name'].astype(str) final_monthly = pd.concat([monthly_w_ot_cmpycontribtn, monthlydeduction_df1, monthly_cmpy_contribution]) # pivt2 = final_monthly.pivot_table(index=['employee_id'], columns=['name'], values='amount').reset_index() # p_data = final_monthly.groupby(["employee_id", "empmonthly_pay__gross_pay"]).count().reset_index() pivt_data_val = final_monthly.pivot_table(index=['employee_id'], columns=['name'],values='amount').reset_index() f2 = final_monthly.merge(pivt_data_val, how='left', on='employee_id') pivt2 = f2.drop(['paycomponent', 'amount', 'type', 'name'], axis=1) # pivt2['Find'] = 'monthly' df1 = pivt df2 = pivt2 df1.rename(columns={'emp_pay__gross_pay': 'Gross pay'}, inplace=True) df2.rename(columns={'empmonthly_pay__gross_pay': 'Gross pay'}, inplace=True) m = pd.merge(df1, df2, on='employee_id', suffixes=('_std', '')) employee_data = Apicall.get_emp_list_info(m['employee_id'].to_list()) emp_fin_df = employee_data.merge(m, left_on='id', right_on='employee_id', how='left') emp_fin_df1 = emp_fin_df.drop_duplicates('employee_id') emp_fin_df2 = emp_fin_df1.drop(['id','employee_id'], axis=1) else: emp_fin_df2 = pd.DataFrame() return emp_fin_df2 def salarystatement_forallemp_cm1(self,request,c_m,c_y,user_id): apifunction = Payrollcommon_Apicall(self._scope()) condition = Q(entity_id=self._entity_id(), status=Activestatus.active) month = request.GET.get('month') year = request.GET.get('year') if month != None and month != "": condition &= Q(empmonthly_pay__payroll_date__month=month) if year != None and year != "": condition &= Q(empmonthly_pay__payroll_date__year=year) struct_w_ot_cmpycontribtn, structdeduction_df1, struct_cmpy_contribution, monthly_w_ot_cmpycontribtn, monthlydeduction_df1, monthly_cmpy_contribution = (pd.DataFrame(),) * 6 module = ModuleList.Employee_Payroll hr_rights = apifunction.employee_role_check(user_id, module, None, None) if hr_rights == 1: monthly_query = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(condition).values('paycomponent','company_contribution','amount','type','empmonthly_pay__employee_id','empmonthly_pay__code','empmonthly_pay__gross_pay','empmonthly_pay__payable_days','empmonthly_pay__paid_days') else: monthly_query = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(condition,empmonthly_pay__employee_id=user_id).values('paycomponent', 'company_contribution', 'amount', 'type', 'empmonthly_pay__employee_id', 'empmonthly_pay__code', 'empmonthly_pay__gross_pay','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days') if monthly_query.count()>0: M_Q_to_df=pd.DataFrame.from_records(monthly_query) emp_list=M_Q_to_df['empmonthly_pay__employee_id'].to_list() structure_emp_info=EmployeePaystructure_detailsService(self._scope()).emp_structure_info_withcb(emp_list) structure_deduction=EmployeePaystructure_deductionsService(self._scope()).Structure_deductioninfo(emp_list) struct_df=pd.DataFrame.from_records(structure_emp_info) structdeduction_df=pd.DataFrame.from_records(structure_deduction) struct_cb = struct_df.loc[struct_df['company_contribution'] == True] struct_w_ot_cb = struct_df.loc[struct_df['company_contribution'] == False] Apicall=Payrollcommon_Apicall(self._scope()) if structdeduction_df.empty is not True: empcb_data = EmployeePFService(self._scope()).get_employeepf_details(structdeduction_df['paycomponent_id'].to_list()) structdeduction_df1 = self.common_merge(empcb_data, ['id', 'name'], 'id', 'paycomponent_id', 'left', structdeduction_df) structdeduction_df1.rename(columns={'paycomponent_id': 'paycomponent'}, inplace=True) structdeduction_df1 = structdeduction_df1[['employee_id', 'amount', 'paycomponent', 'name']] structdeduction_df1['name'] = 'Std Emp deductions ' + structdeduction_df1['name'].astype(str) if struct_w_ot_cb.empty is not True: paycomponent_data = PayrollComponentService(self._scope()).get_multiple_payroll_component_val(struct_w_ot_cb['paycomponent'].to_list()) struct_w_ot_cmpycontribtn = self.common_merge(paycomponent_data, ['id', 'name'], 'id', 'paycomponent','left', struct_w_ot_cb) struct_w_ot_cmpycontribtn = struct_w_ot_cmpycontribtn[['paycomponent', 'amount', 'type', 'emp_pay__employee_id', 'emp_pay__gross_pay', 'name']] struct_w_ot_cmpycontribtn.rename(columns={'emp_pay__employee_id': 'employee_id'}, inplace=True) struct_w_ot_cmpycontribtn['name'] = 'Std ' + struct_w_ot_cmpycontribtn['name'].astype(str) if struct_cb.empty is not True: cb_data = CompanyContributionService(self._scope()).get_multiple_contribution(struct_cb['paycomponent'].to_list()) struct_cmpy_contribution = self.common_merge(cb_data, ['id', 'name'], 'id', 'paycomponent', 'inner', struct_cb) struct_cmpy_contribution = struct_cmpy_contribution[['paycomponent', 'amount', 'type', 'emp_pay__employee_id', 'emp_pay__gross_pay', 'name']] struct_cmpy_contribution['name'] = 'Std Cmp' + struct_cmpy_contribution['name'].astype(str) struct_cmpy_contribution.rename(columns={'emp_pay__employee_id': 'employee_id'}, inplace=True) final_struct = pd.concat([struct_w_ot_cmpycontribtn, structdeduction_df1, struct_cmpy_contribution]) p = final_struct.groupby(["employee_id", "emp_pay__gross_pay"]).count().reset_index() pivt_data = final_struct.pivot_table(index=['employee_id'], columns=['name'], values='amount').reset_index() f = p.merge(pivt_data, how='left', on='employee_id') pivt=f.drop(['paycomponent', 'amount', 'type', 'name'], axis=1) monthly_cb = M_Q_to_df[M_Q_to_df['company_contribution'] == True] monthly_w_ot_cb = M_Q_to_df[M_Q_to_df['company_contribution'] == False] monthly_deductions = Employeemonthlypay_deductionsService(self._scope()).employee_dedution_data(emp_list, month, year) monthly_deductions = pd.DataFrame.from_records(monthly_deductions) if monthly_deductions.empty is not True: _normal_deduct = monthly_deductions[monthly_deductions['deduction_status'] == -1] _additional_deduct = monthly_deductions[(monthly_deductions['deduction_status'] == 1) & (monthly_deductions['is_advance'] == False)] empcb_data = EmployeePFService(self._scope()).get_employeepf_details(_normal_deduct['paycomponent_id'].to_list()) empcb_data2 = PayrollComponentService(self._scope()).get_paycom_details(_additional_deduct['paycomponent_id'].to_list()) monthlydeduction_df_1 = self.common_merge(empcb_data, ['id', 'name'], 'id', 'paycomponent_id', 'left',_normal_deduct) monthlydeduction_df_2 = self.common_merge(empcb_data2, ['id', 'name'], 'id', 'paycomponent_id', 'left',_additional_deduct) monthlydeduction_df1 = pd.concat([monthlydeduction_df_1, monthlydeduction_df_2]) monthlydeduction_df1 = self._common_merge(monthlydeduction_df1, ['id', 'name'], 'id', 'paycomponent_id', 'left', monthly_deductions) monthlydeduction_df1 = monthlydeduction_df1[['employee_id', 'paycomponent_id', 'amount', 'name']] monthlydeduction_df1.rename(columns={'paycomponent_id': 'paycomponent', 'name_x': 'name'}, inplace=True) advance_data_id = is_advance_data.data['id'] advance_data_name = is_advance_data.data['name'] monthlydeduction_df1['paycomponent'] = monthlydeduction_df1['paycomponent'].fillna(advance_data_id) monthlydeduction_df1['name'] = monthlydeduction_df1['name'].fillna(advance_data_name) if monthly_w_ot_cb.empty is not True: paycomponent_data = PayrollComponentService(self._scope()).get_multiple_payroll_component_val(monthly_w_ot_cb['paycomponent'].to_list()) monthly_w_ot_cmpycontribtn = self.common_merge(paycomponent_data, ['id', 'name'], 'id', 'paycomponent', 'left', monthly_w_ot_cb) monthly_w_ot_cmpycontribtn = monthly_w_ot_cmpycontribtn[['paycomponent', 'amount', 'type', 'empmonthly_pay__employee_id', 'empmonthly_pay__gross_pay', 'name','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days']] monthly_w_ot_cmpycontribtn.rename(columns={'empmonthly_pay__employee_id': 'employee_id', 'empmonthly_pay__payable_days':'payable days', 'empmonthly_pay__paid_days': 'paid days'}, inplace=True) if monthly_cb.empty is not True: cb_data = CompanyContributionService(self._scope()).get_multiple_contribution(monthly_cb['paycomponent'].to_list()) monthly_cmpy_contribution = self.common_merge(cb_data, ['id', 'name'], 'id', 'paycomponent', 'inner', monthly_cb) monthly_cmpy_contribution = monthly_cmpy_contribution[['paycomponent', 'amount', 'type', 'empmonthly_pay__employee_id', 'empmonthly_pay__gross_pay', 'name','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days']] monthly_cmpy_contribution.rename(columns={'empmonthly_pay__employee_id': 'employee_id','empmonthly_pay__payable_days':'payable days', 'empmonthly_pay__paid_days': 'paid days'}, inplace=True) monthly_cmpy_contribution['name'] = 'Cmpy cont' + struct_cmpy_contribution['name'].astype(str) final_monthly = pd.concat([monthly_w_ot_cmpycontribtn, monthlydeduction_df1, monthly_cmpy_contribution]) pivt_data_val = final_monthly.pivot_table(index=['employee_id'], columns=['name'],values='amount').reset_index() f2 = final_monthly.merge(pivt_data_val, how='left', on='employee_id') pivt2 = f2.drop(['paycomponent', 'amount', 'type', 'name'], axis=1) df1 = pivt df2 = pivt2 df1.rename(columns={'emp_pay__gross_pay': 'Gross pay'}, inplace=True) df2.rename(columns={'empmonthly_pay__gross_pay': 'Gross pay'}, inplace=True) m = pd.merge(df1, df2, on='employee_id', suffixes=('_std', '')) employee_data1 = Apicall.employee_payrolldetails(m['employee_id'].to_list()) employee_data1 = pd.DataFrame(employee_data1) emp_fin_df = employee_data1.merge(m, left_on='id', right_on='employee_id', how='left') emp_fin_df1 = emp_fin_df.drop_duplicates('employee_id') emp_fin_df2 = emp_fin_df1.drop(['id','employee_id'], axis=1) else: emp_fin_df2 = pd.DataFrame() return emp_fin_df2 def common_merge(self,response,columns_list,right_col,left_col,join,main_df): response_df = pd.DataFrame([vars(s) for s in response], columns=columns_list) final_df= main_df.merge(response_df, left_on=left_col, right_on=right_col,how=join) return final_df def roundup_amt(self, df): df['amount'] = df['amount'].apply(data_roundup) return df def roundup_totalamt(self, df): df['take_home'] = df['take_home'].apply(net_pay_calc) return df def _common_merge(self, response, column_list, right_col, left_col, join, main_df): response_df = pd.DataFrame(response[['name', 'id']], columns=column_list) drop_data = response_df.drop_duplicates('name') final_df = main_df.merge(drop_data, left_on=left_col, right_on=right_col, how=join) return final_df def payroll_commonpdf(self, _json_data): _json_data['logopath']=self.logopath response = HttpResponse(content_type='application/pdf') template = loader.get_template('pdf_content_file.html') html = template.render(_json_data) pisa.CreatePDF(html, dest=response) data = emppaystructurecontroller.read_pdf(response.content) return data def addictional_allowance_data(self, month, year, excel, vys_page, query, pay_status, emp_cc, emp_bs,employee): arr = [] one_month_pre = int(month) - int(1) arr.append(int(month) - int(1)) two_month_pre = int(month) - int(2) arr.append(str(two_month_pre)) arr.append(str(month)) from django.db.models import F condition = Q(status=Activestatus.active) apifunction = Payrollcommon_Apicall(self._scope()) if emp_cc != None and emp_cc != "" or emp_bs != None and emp_bs != "": emp_list = apifunction.emp_arr_cc_bs(emp_cc, emp_bs) condition &= Q(employee_id__in=emp_list) if employee != None and employee != "": emp_list = apifunction.emp_arr_function(employee) condition &= Q(employee_id__in=emp_list) if query == 1: condition &= Q(paid_days=F('payable_days')) if query == 2: condition &= ~Q(paid_days=F('payable_days')) if pay_status: condition &= Q(pay_status=pay_status) else: condition &= Q(pay_status=8) if int(excel): emp_ids = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condition, payroll_date__month__in=arr, payroll_date__year=year, is_deduct=0).values('employee_id', 'id') employee_id = [i['employee_id'] for i in emp_ids] id__in_dta = [i['id'] for i in emp_ids] pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter( empmonthly_pay__employee_id__in=employee_id, company_contribution=False, status=Activestatus.active, empmonthly_pay_id__in=id__in_dta).values( 'paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type', 'empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id') else: try: emp_ids = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condition, payroll_date__month__in=arr, payroll_date__year=year, is_deduct=0).\ values('employee_id', 'id')[vys_page.get_offset():vys_page.get_query_limit()] employee_id = [i['employee_id'] for i in emp_ids] id__in_dta = [i['id'] for i in emp_ids] '-----------------------------------------------------------------------------------------------------------' date_values = year+'-'+month print(date_values) # condtion = ~Q(payroll_date__month=month, payroll_date__year=year) & Q(is_deduct=0, status=Activestatus.active) & \ # Q(payroll_date__month__lt=month, payroll_date__year__lt=year) # obj = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condtion).values('employee_id', 'id', 'payroll_date').annotate(Max('id')) condtion = Q(is_deduct=0, status=Activestatus.active) & \ Q(employee_id__in=employee_id) & ~Q(payroll_date__month=month, payroll_date__year=year) obj = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condtion).values('id', 'payroll_date', 'employee_id') df = pd.DataFrame.from_records(obj) df['payroll_date'] = pd.to_datetime(df['payroll_date']) df['year_month'] = df['payroll_date'].dt.strftime('%Y-%m') <= date_values # filtered_df = df[~df['year_month'].isin(condtions)] validation = df['year_month'] == True filtered_df = df[validation] max_id_index = filtered_df.groupby('employee_id')['id'].idxmax() row_with_max_id = filtered_df.loc[max_id_index] # last_record_indices = df.groupby('employee_id')['year_month'].idxmax() # last_record_indices = df.groupby('employee_id')['payroll_date'].idxmax() # last_record = df.loc[last_record_indices] # print(last_record) # filtered_df = df.loc[~(df['year']<=int(year)& df['month']<=int(month))] # print(filtered_df) # obj = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(is_deduct=0, # status=Activestatus.active, # payroll_date__year__lt=year, # payroll_date__month__lt=month).\ # values('employee_id', 'id').annotate(max_id=Max('id')) # arr_dta = [] # for i in obj: # if int(i['payroll_date'].month) < int(month) or int(i['payroll_date'].year) < int(year): # arr_dta.append(i['id']) # else: # pass # emp_df = pd.DataFrame.from_records(obj) # print(emp_df) # emp_id = [i.employee_id for i in obj] last=row_with_max_id['id'].to_list() details_q = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter( empmonthly_pay_id__in=last, company_contribution=False, status=Activestatus.active).values('empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id') emp_det = pd.DataFrame.from_records(details_q) merge_data = row_with_max_id.merge(emp_det, left_on='id', right_on='empmonthly_pay__id', how='inner') drop_data = merge_data.drop(['empmonthly_pay__employee_id', 'id','payroll_date'], axis=1) drop_data['headings'] = 'previous bonus record' drop_data.rename(columns={'empmonthly_pay__payable_days': 'payable_days', 'empmonthly_pay__paid_days': 'paid_days', 'empmonthly_pay__payroll_date': 'payroll_date', 'empmonthly_pay__id': 'empmonthly_payid'}, inplace=True) drop_data['payroll_date'] = drop_data['payroll_date'].astype('str') drop_data['bonus'] = '0.0' drop_data['year'] = '0.0' drop_data['month'] = '0.0' drop_data['deduct_date_month'] = '0.0' drop_data['deduct_date_year'] = '0.0' drop_data['deduction_amount'] = '0.0' '-----------------------------------------------------------------------------------------------------------' except: list_data = NWisefinList() vpage = NWisefinPaginator([], vys_page.get_index(), 10) list_data.pagination = vpage if len(emp_ids) > 0: pay_stru_detail_dist = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter( empmonthly_pay__employee_id__in=employee_id, company_contribution=False, status=Activestatus.active, empmonthly_pay_id__in=id__in_dta).values('paycomponent', 'empmonthly_pay__employee_id', 'amount', 'type', 'empmonthly_pay__payroll_date', 'type', 'empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id') emp_list = employee_id if len(emp_ids) > 0: employee_cc_bs = apifunction.emp_data_cc_bs(emp_list) employee_cc_bs_df = pd.DataFrame([vars(s) for s in employee_cc_bs],columns=['id', 'costcentre', 'businesssegment']) employee_paystucture = EmployeePaystructure_detailsService(self._scope()).employee_paystucture_details(emp_list, arr) emp_paystruct_deductions = EmployeePaystructure_deductionsService(self._scope()).Structure_deductioninfo(emp_list) emp_paystruct_deductions = pd.DataFrame.from_records(emp_paystruct_deductions) std_df = pd.DataFrame.from_records(employee_paystucture) std_withbonus = self.groupby_sum__(std_df, '') std_withbonus.columns = ['employee_id', 'amount', 'bonus'] s_d = emp_paystruct_deductions.groupby('employee_id')['amount'].sum().reset_index() s_d.columns = ['employee_id', 'deduction_amout'] fin_std_df = s_d.merge(std_withbonus, on='employee_id', how='left') fin_std_df = fin_std_df.fillna('0') # fin_std_df = std_withbonus.merge(s_d, on='employee_id') # std_b=groupby_sum__(deductiondf,'') empmonthly_deduction = Employeemonthlypay_deductionsService(self._scope()).employee_dedution_info(emp_list, arr, year) monthly_df = pd.DataFrame.from_records(pay_stru_detail_dist) deductiondf = pd.DataFrame.from_records(empmonthly_deduction) deductiondf['year'] = pd.DatetimeIndex(deductiondf['deduct_date']).year deductiondf['month'] = pd.DatetimeIndex(deductiondf['deduct_date']).month monthly = monthly_df.groupby( ['empmonthly_pay__employee_id', 'empmonthly_pay__payroll_date', 'empmonthly_pay__payable_days', 'empmonthly_pay__paid_days', 'empmonthly_pay__id'])['amount'].sum().reset_index() monthly_bonus = monthly_df[monthly_df['type'].notnull()] MONTH_b = monthly_bonus.groupby('empmonthly_pay__id')['amount'].sum().reset_index() MONTH_b.columns = ['empmonthly_pay__id', 'bonus_amount'] final_monthly = pd.merge(monthly, MONTH_b, on='empmonthly_pay__id', how='left') final_monthly = final_monthly.fillna('0') final_monthly.columns = ['employee_id', 'payroll_date', 'payable_days', 'paid_days', 'empmonthly_payid', 'amount', 'bonus'] final_monthly['year'] = pd.DatetimeIndex(final_monthly['payroll_date']).year final_monthly['month'] = pd.DatetimeIndex(final_monthly['payroll_date']).month deductiondf[['year', 'month']] = deductiondf[['year', 'month']].astype(object) # final_monthly = final_monthly.merge(deductiondf, left_on=['employee_id', 'year', 'month'], # right_on=['employee_id', 'year', 'month'], how='inner') # final_monthly = pd.concat([final_monthly, fin_std_df]) print(final_monthly) final_monthly = final_monthly.fillna('0000:00:00') final_monthly['payroll_date'] = final_monthly['payroll_date'].astype('str') final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(month)), 'headings'] = 'Current Month' final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(one_month_pre)), 'headings'] = 'CM-1' final_monthly.loc[(final_monthly['year'] == int(year)) & (final_monthly['month'] == int(two_month_pre)), 'headings'] = 'CM-2' final_monthly.loc[final_monthly['payroll_date'] == '0000:00:00', 'headings'] = 'Std' if df.empty: drop_data = pd.DataFrame() if drop_data.empty: drop_data = pd.DataFrame() final_monthly = pd.concat([final_monthly, drop_data], ignore_index=True) else: final_monthly = pd.concat([final_monthly, drop_data], ignore_index=True) print(final_monthly) Apicall = Payrollcommon_Apicall(self._scope()) employee_data = Apicall.get_emp_list_info(final_monthly['employee_id'].to_list()) emp_fin_df1 = final_monthly.merge(employee_data, left_on='employee_id', right_on='id', how='left') emp_fin_df2 = emp_fin_df1.merge(employee_cc_bs_df, left_on='employee_id', right_on='id', how='left') emp_fin_df = emp_fin_df2.drop(['id_y'], axis=1) if not excel: list_data = NWisefinList() res = emp_fin_df.to_json(orient="records") emp_fin_df = json.loads(res) list_data.data = emp_fin_df vpage = NWisefinPaginator(emp_fin_df, vys_page.get_index(), 10) list_data.pagination = vpage return list_data return emp_fin_df else: if not excel: list_data = NWisefinList() vpage = NWisefinPaginator([], vys_page.get_index(), 10) list_data.pagination = vpage else: list_data = pd.DataFrame() return list_data def salarystatement_forallemp_cm_sample(self,request,c_m,c_y,user_id,vys_page,excel,bank,pay_status): try: apifunction = Payrollcommon_Apicall(self._scope()) Apicall = Payrollcommon_Apicall(self._scope()) condition = Q(entity_id=self._entity_id(), status=Activestatus.active) month = request.GET.get('month') year = request.GET.get('year') if month != None and month != "": condition &= Q(empmonthly_pay__payroll_date__month=month) if year != None and year != "": condition &= Q(empmonthly_pay__payroll_date__year=year) if pay_status != None and pay_status != "": condition &= Q(empmonthly_pay__pay_status__in=pay_status) struct_w_ot_cmpycontribtn, structdeduction_df1, struct_cmpy_contribution, monthly_w_ot_cmpycontribtn, monthlydeduction_df1, monthly_cmpy_contribution = (pd.DataFrame(),) * 6 module = ModuleList.Employee_Payroll hr_rights = apifunction.employee_role_check(user_id, module, None, None) if hr_rights == 1: monthly_query = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(condition).values('paycomponent','company_contribution','amount','type','empmonthly_pay__employee_id','empmonthly_pay__code','empmonthly_pay__gross_pay','empmonthly_pay__payable_days','empmonthly_pay__paid_days','empmonthly_pay__code') else: monthly_query = Employeemonthlypay_details.objects.using(self._current_app_schema()).filter(condition,empmonthly_pay__employee_id=user_id).values('paycomponent', 'company_contribution', 'amount', 'type', 'empmonthly_pay__employee_id', 'empmonthly_pay__code', 'empmonthly_pay__gross_pay','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days','empmonthly_pay__code') if monthly_query.count()>0: M_Q_to_df = pd.DataFrame.from_records(monthly_query) emp_list = M_Q_to_df['empmonthly_pay__employee_id'].to_list() emp_data1 = Apicall.employee_payrolldetails(emp_list) emp_data1 = pd.DataFrame(emp_data1) emp_data1.rename(columns={'id':'id','code':'Code','full_name':'Name','designation':'Designation','pf_number':'PF Number','uan':'UAN','esi_number':'ESI Number','accountnumber':'Bank A/C No.'}, inplace=True) structure_emp_info=EmployeePaystructure_detailsService(self._scope()).emp_structure_info_withcb(emp_list)#structure details structure_deduction=EmployeePaystructure_deductionsService(self._scope()).Structure_deductioninfo(emp_list)#structure deduction struct_df=pd.DataFrame.from_records(structure_emp_info) structdeduction_df=pd.DataFrame.from_records(structure_deduction) struct_cb = struct_df.loc[struct_df['company_contribution'] == True] struct_w_ot_cb = struct_df.loc[struct_df['company_contribution'] == False] if structdeduction_df.empty is not True: empcb_data = EmployeePFService(self._scope()).get_employeepf_details(structdeduction_df['paycomponent_id'].to_list()) structdeduction_df1 = self.common_merge(empcb_data, ['id', 'name'], 'id', 'paycomponent_id', 'left', structdeduction_df) structdeduction_df1.rename(columns={'paycomponent_id': 'paycomponent'}, inplace=True) structdeduction_df1 = structdeduction_df1[['employee_id', 'amount', 'paycomponent', 'name']] structdeduction_df1['name'] = 'Std Emp deductions ' + structdeduction_df1['name'].astype(str) if struct_w_ot_cb.empty is not True: paycomponent_data = PayrollComponentService(self._scope()).get_multiple_payroll_component_val(struct_w_ot_cb['paycomponent'].to_list()) struct_w_ot_cmpycontribtn = self.common_merge(paycomponent_data, ['id', 'name'], 'id', 'paycomponent','left', struct_w_ot_cb) struct_w_ot_cmpycontribtn = struct_w_ot_cmpycontribtn[['paycomponent', 'amount', 'type', 'emp_pay__employee_id', 'emp_pay__gross_pay', 'name']] struct_w_ot_cmpycontribtn.rename(columns={'emp_pay__employee_id': 'employee_id'}, inplace=True) struct_w_ot_cmpycontribtn['name'] = 'Std ' + struct_w_ot_cmpycontribtn['name'].astype(str) if struct_cb.empty is not True: cb_data = CompanyContributionService(self._scope()).get_multiple_contribution(struct_cb['paycomponent'].to_list()) struct_cmpy_contribution = self.common_merge(cb_data, ['id', 'name'], 'id', 'paycomponent', 'inner', struct_cb) struct_cmpy_contribution = struct_cmpy_contribution[['paycomponent', 'amount', 'type', 'emp_pay__employee_id', 'emp_pay__gross_pay', 'name']] struct_cmpy_contribution['name'] = 'Std Cmp' + struct_cmpy_contribution['name'].astype(str) struct_cmpy_contribution.rename(columns={'emp_pay__employee_id': 'employee_id'}, inplace=True) # std grosspay calculation if structdeduction_df.empty is not True: structdeduction_df1=structdeduction_df1 else: if 'employee_id' not in structdeduction_df1.columns: structdeduction_df1['employee_id'] = '0.0' if 'amount' not in structdeduction_df1.columns: structdeduction_df1['amount'] = '0.0' if 'paycomponent' not in structdeduction_df1.columns: structdeduction_df1['paycomponent'] = '0.0' if 'name' not in structdeduction_df1.columns: structdeduction_df1['name'] = '0.0' std_gp_cal1 = struct_w_ot_cmpycontribtn.groupby('employee_id')['amount'].sum().reset_index() std_gp_cal2 = structdeduction_df1.groupby('employee_id')['amount'].sum().reset_index() std_gross_pay = std_gp_cal1.merge(std_gp_cal2, left_on="employee_id", right_on="employee_id", how='left') std_gross_pay = std_gross_pay.fillna('0') std_gross_pay['Std_NetSalary'] = (std_gross_pay['amount_x'].astype(float).sub(std_gross_pay['amount_y'].astype(float))) #takehome=details amount with cc - deduction final_struct = pd.concat([struct_w_ot_cmpycontribtn, structdeduction_df1, struct_cmpy_contribution]) pivt_data = final_struct.pivot_table(index=['employee_id'], columns=['name'],sort=False,values='amount').reset_index() f = pivt_data.merge(std_gross_pay, how='right', on='employee_id') struct_final = emp_data1.merge(f, left_on='id',right_on='employee_id',how='left') pivt = struct_final.drop(['id','amount_y'], axis=1) pivt.rename(columns={'amount_x': 'Gross pay_std'}, inplace=True) monthly_cb = M_Q_to_df[M_Q_to_df['company_contribution'] == True] monthly_w_ot_cb = M_Q_to_df[M_Q_to_df['company_contribution'] == False] monthly_deductions = Employeemonthlypay_deductionsService(self._scope()).employee_dedution_data(emp_list, month, year) monthly_deductions = pd.DataFrame.from_records(monthly_deductions) monthlydeduction_df3 = pd.DataFrame() if monthly_deductions.empty is not True: _normal_deduct = monthly_deductions[monthly_deductions['deduction_status'] == -1] _additional_deduct = monthly_deductions[(monthly_deductions['deduction_status'] == 1) & (monthly_deductions['is_advance'] == False)] empcb_data = EmployeePFService(self._scope()).get_employeepf_details(_normal_deduct['paycomponent_id'].to_list()) empcb_data2 = PayrollComponentService(self._scope()).get_paycom_details(_additional_deduct['paycomponent_id'].to_list()) monthlydeduction_df_1 = self.common_merge(empcb_data, ['id', 'name'], 'id', 'paycomponent_id', 'left',_normal_deduct) monthlydeduction_df_2 = self.common_merge(empcb_data2, ['id', 'name'], 'id', 'paycomponent_id', 'left',_additional_deduct) monthlydeduction_df1 = pd.concat([monthlydeduction_df_1, monthlydeduction_df_2]) monthlydeduction_df1 = self._common_merge(monthlydeduction_df1, ['id', 'name'], 'id', 'paycomponent_id', 'left', monthly_deductions) monthlydeduction_df1 = monthlydeduction_df1[['employee_id', 'paycomponent_id', 'amount', 'name']] monthlydeduction_df1.rename(columns={'paycomponent_id': 'paycomponent', 'name_x': 'name'}, inplace=True) advance_data_id = is_advance_data.data['id'] advance_data_name = is_advance_data.data['name'] monthlydeduction_df1['paycomponent'] = monthlydeduction_df1['paycomponent'].fillna(advance_data_id) monthlydeduction_df1['name'] = monthlydeduction_df1['name'].fillna(advance_data_name) monthlydeduction_df2 = monthlydeduction_df1.groupby('employee_id')['amount'].sum().reset_index() #one employee based multiple advance sum advances_df = monthlydeduction_df1[monthlydeduction_df1['name'] == 'ADVANCE'] sum_amounts = advances_df.groupby(['employee_id', 'paycomponent'])['amount'].sum().reset_index() advances_df = pd.merge(advances_df, sum_amounts, on=['employee_id', 'paycomponent'], suffixes=('', '_sum')) advances_df['amount'] = advances_df['amount_sum'] advances_df = advances_df.drop(columns=['amount_sum']) deduct_df2 = advances_df.drop_duplicates(subset=['employee_id']) deduct_df1 = monthlydeduction_df1[monthlydeduction_df1['name'] != 'ADVANCE'] monthlydeduction_df3 = pd.concat([deduct_df1, deduct_df2], axis=0)#final monthlydeduct if monthly_w_ot_cb.empty is not True: paycomponent_data = PayrollComponentService(self._scope()).get_multiple_payroll_component_val(monthly_w_ot_cb['paycomponent'].to_list()) monthly_w_ot_cmpycontribtn = self.common_merge(paycomponent_data, ['id', 'name'], 'id', 'paycomponent', 'left', monthly_w_ot_cb) monthly_w_ot_cmpycontribtn = self.roundup_amt(monthly_w_ot_cmpycontribtn) from decimal import Decimal monthly_w_ot_cmpycontribtn['amount'] = monthly_w_ot_cmpycontribtn['amount'].apply(lambda x: Decimal(x) if isinstance(x, str) else x) # monthly_w_ot_cmpycontribtn = self.roundup_float_to_decimal(monthly_w_ot_cmpycontribtn) monthly_w_ot_cmpycontribtn = monthly_w_ot_cmpycontribtn[['paycomponent', 'amount', 'type', 'empmonthly_pay__employee_id', 'empmonthly_pay__gross_pay', 'name','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days','empmonthly_pay__code']] monthly_w_ot_cmpycontribtn.rename(columns={'empmonthly_pay__employee_id': 'employee_id', 'empmonthly_pay__payable_days':'payable days', 'empmonthly_pay__paid_days': 'paid days','payshlip_code':'empmonthly_pay__code'}, inplace=True) if monthly_cb.empty is not True: cb_data = CompanyContributionService(self._scope()).get_multiple_contribution(monthly_cb['paycomponent'].to_list()) monthly_cmpy_contribution = self.common_merge(cb_data, ['id', 'name'], 'id', 'paycomponent', 'inner', monthly_cb) monthly_cmpy_contribution = self.roundup_amt(monthly_cmpy_contribution) from decimal import Decimal monthly_cmpy_contribution['amount'] = monthly_cmpy_contribution['amount'].apply(lambda x: Decimal(x) if isinstance(x, str) else x) monthly_cmpy_contribution = monthly_cmpy_contribution[['paycomponent', 'amount', 'type', 'empmonthly_pay__employee_id', 'empmonthly_pay__gross_pay', 'name','empmonthly_pay__payable_days', 'empmonthly_pay__paid_days','empmonthly_pay__code']] monthly_cmpy_contribution.rename(columns={'empmonthly_pay__employee_id': 'employee_id','empmonthly_pay__payable_days':'payable days', 'empmonthly_pay__paid_days': 'paid days','payshlip_code':'empmonthly_pay__code'}, inplace=True) monthly_cmpy_contribution['name'] = 'Cmpy cont' + monthly_cmpy_contribution['name'].astype(str) # monthly if monthly_deductions.empty is not True: monthlydeduction_df3=monthlydeduction_df3 else: if monthlydeduction_df3 is not None: if 'employee_id' not in monthlydeduction_df3.columns: monthlydeduction_df3['employee_id'] = '0.0' if 'amount' not in monthlydeduction_df3.columns: monthlydeduction_df3['amount'] = '0.0' if 'paycomponent' not in monthlydeduction_df3.columns: monthlydeduction_df3['paycomponent'] = '0.0' if 'name' not in monthlydeduction_df3.columns: monthlydeduction_df3['name'] = '0.0' monthly_gp_cal1 = monthly_w_ot_cmpycontribtn.groupby('employee_id')['amount'].sum().reset_index() monthly_gp_cal2 = monthlydeduction_df3.groupby('employee_id')['amount'].sum().reset_index() monthly_gross_pay = monthly_gp_cal1.merge(monthly_gp_cal2, left_on="employee_id", right_on="employee_id", how='left') monthly_gross_pay = monthly_gross_pay.fillna('0') monthly_gross_pay['NetSalary'] = (monthly_gross_pay['amount_x'].astype(float).sub(monthly_gross_pay['amount_y'].astype(float))) monthly_gross_pay['NetSalary'] = monthly_gross_pay['NetSalary'].apply(netpay_float_to_decimal) final_monthly = pd.concat([monthly_w_ot_cmpycontribtn, monthlydeduction_df3, monthly_cmpy_contribution]) final_monthly1 = final_monthly[['employee_id','payable days','paid days']] pivt_data_val = final_monthly.pivot_table(index=['employee_id'], columns=['name'],sort=False,values='amount').reset_index() pivt_data_value = pivt_data_val.applymap(float_to_decimal) pivt_data_value = pivt_data_value.replace({np.nan: None}) f2 = pivt_data_value.merge(final_monthly, how='left', on='employee_id') final_month = f2.merge(monthly_gross_pay, how='left', on='employee_id') pivt2 = final_month.drop(['paycomponent', 'amount', 'type', 'name','amount_y'], axis=1) df1 = pivt df2 = pivt2 df3 = final_monthly1.merge(df2, how='left',on='employee_id') df3 = df3.drop(['payable days_y','paid days_y','empmonthly_pay__gross_pay'],axis=1) df3.rename(columns={'amount_x': 'Gross pay','payable days_x':'payable days','paid days_x':'paid days'}, inplace=True) m = pd.merge(df1, df3, on='employee_id') emp_fin_df1 = m.drop_duplicates('employee_id') emp_fin_df2 = emp_fin_df1.drop(['employee_id','empmonthly_pay__code'], axis=1) if bank!=None: emp_fin_df3 = emp_fin_df1.drop(['employee_id'], axis=1) return emp_fin_df3 if not excel: list_data = NWisefinList() if emp_fin_df2.empty: vpage = NWisefinPaginator([], vys_page.get_index(), 10) list_data.set_pagination(vpage) else: res = emp_fin_df2.to_json(orient="records") emp_fin_df = json.loads(res)[vys_page.get_offset():vys_page.get_query_limit()] list_data.data=emp_fin_df vpage = NWisefinPaginator(emp_fin_df, vys_page.get_index(), 10) list_data.pagination=vpage val = json.dumps(list_data, default=lambda o: o.__dict__, indent=4) return val return emp_fin_df2 else: emp_fin_df2 = pd.DataFrame() return emp_fin_df2 except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj