import json import pandas as pd from django.db.models import Q from django.http import HttpResponse from django.utils import timezone from npayroll.settings import logger from payrollservice.data.response.report_response import report_response from payrollservice.models import ReportColumns, ReportTemplate, ReportTemplateDetails, Employeemonthly_payinfo from payrollservice.service.empmonthlypayinfoservice import EmployeemonthlypayService from payrollservice.util.payrollutil import Activestatus, ReportColumns_Type, Advancestatus, advancestatus from utilityservice.data.response.empmessage import ErrorMessage, ErrorDescription from utilityservice.data.response.nwisefinerror import NWisefinError from utilityservice.data.response.nwisefinlist import NWisefinList from utilityservice.data.response.nwisefinpaginator import NWisefinPaginator from utilityservice.data.response.nwisefinsuccess import NWisefinSuccess, SuccessMessage, SuccessStatus from utilityservice.service.applicationconstants import ApplicationNamespace from utilityservice.service.payroll_api_service import Payrollcommon_Apicall from utilityservice.service.threadlocal import NWisefinThread class Report_Service(NWisefinThread): def __init__(self, scope): super().__init__(scope) self._set_namespace(ApplicationNamespace.PAYROLL_SERVICE) def create_reportcolumn(self, report_request, emp_id): try: resp = NWisefinSuccess() if report_request.get_id() is not None: ReportColumns_update = ReportColumns.objects.using(self._current_app_schema()).filter(id=report_request.get_id()).update(name=report_request.get_name(), type=report_request.get_type(), link_columns=report_request.get_link_columns(), updated_by=emp_id,updated_date=timezone.now(), entity_id=self._entity_id()) message = SuccessMessage.UPDATE_MESSAGE else: ReportColumns_create = ReportColumns.objects.using(self._current_app_schema()).create(name=report_request.get_name(), type = report_request.get_type(), link_columns=report_request.get_link_columns(), created_by=emp_id, entity_id=self._entity_id()) message = SuccessMessage.CREATE_MESSAGE resp.set_status(SuccessStatus.SUCCESS) resp.set_message(message) return resp except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def report_column_summary(self,name,vys_page): try: condition = Q(status=Activestatus.active) if name != None and name != "": condition &= Q(name__icontains=name) report_obj = ReportColumns.objects.using(self._current_app_schema()).filter(condition)[vys_page.get_offset():vys_page.get_query_limit()] list_data = NWisefinList() for report in report_obj: resp = report_response() resp.set_id(report.id) resp.set_name(report.name) resp.set_type1(report.type) resp.set_link_columns(report.link_columns) list_data.append(resp) v_page = NWisefinPaginator(report_obj, vys_page.get_index(), 10) list_data.set_pagination(v_page) return list_data except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def get_report_column(self,rep_id): try: report = ReportColumns.objects.using(self._current_app_schema()).get(status=Activestatus.active, id=rep_id) resp = report_response() resp.set_id(report.id) resp.set_name(report.name) resp.set_type1(report.type) resp.set_link_columns(report.link_columns) return resp except report.DoesNotExist: error = NWisefinError() error.set_code(ErrorMessage.INVALID_DATA) error.set_description(ErrorDescription.INVALID_DATA) return error def delete_report_column(self,rep_id): try: report_id = ReportColumns.objects.using(self._current_app_schema()).filter(id=rep_id).update( status=Activestatus.inactive) if report_id != 0: success = NWisefinSuccess() success.set_status(SuccessStatus.SUCCESS) success.set_message(SuccessMessage.DELETE_MESSAGE) return success else: error = NWisefinError() error.set_code(ErrorMessage.INVALID_DATA) error.set_description(ErrorDescription.INVALID_DATA) return error except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def create_ReportTemplate(self,template_request,emp_id,resp_data): try: resp = NWisefinSuccess() if template_request.get_id() is not None: ReportTemplate_update = ReportTemplate.objects.using(self._current_app_schema()).filter( id=template_request.get_id()).update(name=template_request.get_name(),bank_id=template_request.get_bank_id(), updated_date=timezone.now(), updated_by=emp_id) details_values = resp_data["details"] for i in details_values: if "id" in i and not "isdelete" in i: report_details_update = ReportTemplateDetails.objects.using(self._current_app_schema()).filter(id =i['id']).update( updated_date=timezone.now(), updated_by=emp_id,reportcolumns_id=i["column_id"], order=i["order"]) elif "id" not in i and not "isdelete" in i: detail_create = ReportTemplateDetails.objects.using(self._current_app_schema()).create( report_template_id=template_request.get_id(), created_by=emp_id, reportcolumns_id=i["column_id"], order=i["order"]) elif "id" in i and "isdelete" in i: delete_details = ReportTemplateDetails.objects.using(self._current_app_schema()).filter( id=i['id']).update(status=Activestatus.inactive) message = SuccessMessage.UPDATE_MESSAGE else: ReportTemplate_create = ReportTemplate.objects.using(self._current_app_schema()).create( name=template_request.get_name(), bank_id=template_request.get_bank_id(), created_by=emp_id, entity_id=self._entity_id()) report_template_id = ReportTemplate_create.id details_values = resp_data["details"] for i in details_values: report_details = ReportTemplateDetails.objects.using(self._current_app_schema()).create( report_template_id=report_template_id, created_by=emp_id, reportcolumns_id=i["column_id"], order=i["order"]) message = SuccessMessage.CREATE_MESSAGE resp.set_status(SuccessStatus.SUCCESS) resp.set_message(message) return resp except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def report_template_summary(self,bank_id,name,vys_page): try: condition = Q(status=Activestatus.active) if name != None and name != "": condition &= Q(name__icontains=name) if bank_id != None and bank_id != "": condition &= Q(bank_id=bank_id) template_obj = ReportTemplate.objects.using(self._current_app_schema()).filter(condition)[vys_page.get_offset():vys_page.get_query_limit()] temp_id = [i.id for i in template_obj] bank_id_arr = [i.bank_id for i in template_obj] # bank_data= BankService(self._scope()).fetch_bank_details(bank_id_arr) bank_data=Payrollcommon_Apicall(self._scope()).fetch_bank_details(bank_id_arr) list_data = NWisefinList() for temp in template_obj: resp = report_response() resp.set_id(temp.id) resp.set_name(temp.name) resp.set_bank_val(temp.bank_id,bank_data) resp.details = self.report_details_info([temp.id]) list_data.append(resp) v_page = NWisefinPaginator(template_obj, vys_page.get_index(), 10) list_data.set_pagination(v_page) return list_data except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def get_report_template(self,temp_id): try: details = ReportTemplate.objects.using(self._current_app_schema()).get(status=Activestatus.active, id=temp_id) # bank_data = BankService(self._scope()).fetch_bank_details([details.bank_id]) bank_data = Payrollcommon_Apicall(self._scope()).fetch_bank_details([details.bank_id]) resp = report_response() resp.set_id(details.id) resp.set_name(details.name) resp.set_bank_val(details.bank_id, bank_data) resp.details = self.report_details_info([details.id]) return resp except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def delete_report_template(self,temp_id): try: report_template_id = ReportTemplate.objects.using(self._current_app_schema()).filter(id=temp_id).update( status=Activestatus.inactive) report_detail_id = ReportTemplateDetails.objects.using(self._current_app_schema()).filter(report_template_id=temp_id).update( status=Activestatus.inactive) if report_detail_id and report_template_id != 0: success = NWisefinSuccess() success.set_status(SuccessStatus.SUCCESS) success.set_message(SuccessMessage.DELETE_MESSAGE) return success else: error = NWisefinError() error.set_code(ErrorMessage.INVALID_DATA) error.set_description(ErrorDescription.INVALID_DATA) return error except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def delete_details(self,detail_id): try: report_detail_id = ReportTemplateDetails.objects.using(self._current_app_schema()).filter( id=detail_id).update(status=Activestatus.inactive) if report_detail_id != 0: success = NWisefinSuccess() success.set_status(SuccessStatus.SUCCESS) success.set_message(SuccessMessage.DELETE_MESSAGE) return success except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def report_details_info(self,template_id): report = [] temp = ReportTemplateDetails.objects.using(self._current_app_schema()).filter(status=Activestatus.active, report_template_id__in=template_id) for i in temp: resp = report_response() resp.set_id(i.id) resp.set_order(i.order) resp.set_column_id(i.reportcolumns_id) resp.set_name(i.reportcolumns.name) report.append(resp) return report # BANK TEMPLATE DOWNLOADS def report_banktemplates(self,template_id): try: banktemplates = ReportTemplateDetails.objects.using(self._current_app_schema()).filter(status=Activestatus.active, report_template_id=template_id) banktemplates_df = pd.DataFrame(banktemplates.values('report_template__id', 'report_template__bank_id','reportcolumns__name')) columns_val = [i.reportcolumns.name for i in banktemplates] bank_id = [i.report_template.bank_id for i in banktemplates] bank_data = Payrollcommon_Apicall(self._scope()).fetch_bank_details(bank_id) bank_name = bank_data[0].name bank_df = pd.DataFrame(None, columns=columns_val) excel = 'application/vnd.ms-excel' response = HttpResponse(content_type=excel) filename = bank_name response['Content-Disposition'] = f'attachment; filename="{str(filename) + ".xlsx"}"' writer = pd.ExcelWriter(response, engine='xlsxwriter') bank_df.to_excel(writer, index=False) worksheet = writer.sheets['Sheet1'] # worksheet.write('A1',bank_name) writer.close() return HttpResponse(response) except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) response = HttpResponse(obj.get(), content_type='application/json') return response def payroll_bank_template(self,request,bank_id,user_id,filename): try: banktemplates = ReportTemplateDetails.objects.using(self._current_app_schema()).filter(status=Activestatus.active, report_template__bank_id=bank_id) final_df = pd.DataFrame() if banktemplates.count() > 0: # bankbased template banktemplates_df = pd.DataFrame(banktemplates.values('report_template__id', 'report_template__bank_id', 'reportcolumns__name')) bankcolumns_val = [i.reportcolumns.name for i in banktemplates] banktempdf = Payrollcommon_Apicall(self._scope()).bank_template_based_employee(request,bank_id,user_id) if banktempdf.empty: df = pd.DataFrame(None, columns=bankcolumns_val) return df else: print('payrollbank_data',banktempdf) # netamount logger.info("employee net amount"+ str(banktempdf['NetSalary'])) arr=pd.DataFrame() for columns in bankcolumns_val: if columns in ('Name','name','employee name','Employee','EMPLOYEE NAME','employee_name','Employee_Name','EMPLOYEE_NAME',"full_name"): arr[columns] = banktempdf['employee_id__full_name'] elif columns in ('code','CODE','Code','Employee_Code','EmployeeCode','EMPLOYEECODE','employee_code','EMPLOYEE_CODE','employeecode','employee code','Employee Code','EMPLOYEE CODE','Emp_code','empcode','EMP_CODE'): arr[columns] = banktempdf['employee_id__code'] elif columns in ('Account Number','ACCOUNT NUMBER','account_number','Account Number','ACCOUNT NUMBER','account number','AccountNumber','ACCOUNTNUMBER','accountnumber','account_no'): arr[columns] = banktempdf['account_no'] elif columns in ('ifsc','IFSC','Ifsc','ifsc_code','IFSC_CODE','Ifsc_code','IFSC CODE','Ifsc code','ifsc code'): arr[columns] = banktempdf['ifsc'] elif columns in ('pf_number','PF_NUMBER','PF','PF NUMBER','PF.NO','pfnumber','Pf_Number','pf'): arr[columns] = banktempdf['employee_id__pf_number'] elif columns in ('ESI_NUMBER','ESI','ESI NUMBER','esi_number','esi','esi number','ESINUMBER','esinumber'): arr[columns] = banktempdf['employee_id__esi_number'] elif columns in ('BANK','BANK_NAME'): arr[columns] = filename elif columns in ('Sno','S.no','SNO','S.NO','Serial','SERIAL NUMBER','Serial_Number','SERIAL NUMBER','Serial.no','serial_number','S_no','S_NO','s.no'): serial_number = 1 arr[columns] = range(serial_number, serial_number + len(banktempdf)) serial_number += len(arr) # Update the serial number for the next iteration final_df = arr final_df['Amount'] = banktempdf['NetSalary'] final_df['Payshlip_code'] = banktempdf['empmonthly_pay__code'] print('bank template',final_df) logger.info('bank template',str(final_df)) return final_df else: return final_df except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def get_linkcolumns(self,request): try: linkcolumns_val=None linkcolumns = request.GET.get('linkcolumns') if int(linkcolumns) == ReportColumns_Type.employee_basic: linkcolumns_val = Payrollcommon_Apicall(self._scope()).get_employeecolumns() elif int(linkcolumns) == ReportColumns_Type.employee_bank: linkcolumns_val = Payrollcommon_Apicall(self._scope()).get_employeebankcolumns() elif int(linkcolumns) == ReportColumns_Type.employee_salary: linkcolumns_val = EmployeemonthlypayService(self._scope()).get_columnnames_payinfo() if linkcolumns_val.empty: linkcolumns_list = [] else: linkcolumns_list = linkcolumns_val.tolist() # Convert NumPy array to list val = json.dumps(linkcolumns_list, indent=4) return val except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) return obj def bank_linkcolumns_exceldownload(self,request,bank_id,user_id,filename): final_df = pd.DataFrame() try: banktemplates = ReportTemplateDetails.objects.using(self._current_app_schema()).filter(status=Activestatus.active, report_template__bank_id=bank_id) if banktemplates.count() > 0: banklinkcolumns_val = [i.reportcolumns.link_columns for i in banktemplates] banktempdf = Payrollcommon_Apicall(self._scope()).bank_template_based_employee(request,bank_id,user_id) if banktempdf.empty: df = pd.DataFrame(None, columns=banklinkcolumns_val) return df else: print('payrollbank_data',banktempdf) # netamount logger.info("employee net amount"+ str(banktempdf['NetSalary'])) arr=pd.DataFrame() for i in banktemplates: if i.reportcolumns.link_columns in ("full_name"): arr[i.reportcolumns.name] = banktempdf['full_name'] # elif i.reportcolumns.link_columns in ("code"): # arr[i.reportcolumns.name] = banktempdf['employee_id__code'] elif i.reportcolumns.link_columns in ("email_id"): arr[i.reportcolumns.name] = banktempdf['email_id'] elif i.reportcolumns.link_columns in ("pf_number"): arr[i.reportcolumns.name] = banktempdf['pf_number'] elif i.reportcolumns.link_columns in ("esi_number"): arr[i.reportcolumns.name] = banktempdf['esi_number'] elif i.reportcolumns.link_columns in ("account_no"): arr[i.reportcolumns.name] = banktempdf['account_no'] elif i.reportcolumns.link_columns in ("ifsc"): arr[i.reportcolumns.name] = banktempdf['ifsc'] elif i.reportcolumns.link_columns in ("bank"): arr[i.reportcolumns.name] = banktempdf['name_x'] # elif i.reportcolumns.link_columns in ("code"): # arr[i.reportcolumns.name] = banktempdf['code'] final_df = arr final_df['Amount'] = banktempdf['NetSalary'] final_df['Payshlip_code'] = banktempdf['empmonthly_pay__code'] final_df['Code'] = banktempdf['code'] final_df["S.N0"] = range(1, len(final_df) + 1) #last column to change position one final_df.insert(0, 'S.N0', final_df.pop('S.N0')) # Move 'Payshlip_code' to the second position final_df.insert(1, 'Code', final_df.pop('Code')) final_df.insert(2, 'Payshlip_code', final_df.pop('Payshlip_code')) print('bank template',final_df) logger.info('bank template',str(final_df)) return final_df else: return final_df except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) logger.info('Bank Template Error ',str(excep)) return final_df def excelupdate_payrollpaystatus(self,request,user_id, payroll_obj): try: update_status = None from datetime import datetime condition = Q(status=Activestatus.active) month = request.GET.get('month') year = request.GET.get('year') for payroll_status in payroll_obj: employee = Payrollcommon_Apicall(self._scope()).emp_code(payroll_status['EMPLOYEE_CODE']) paiddate = payroll_status['PAID_DATE'] pay_status=advancestatus(payroll_status['PAY_STATUS'])['id'] logger.info('Bank Based Employee Salary Payroll Status Update', str({"employee": employee.get('id'),"pay_status":payroll_status['PAY_STATUS'],"paid_date":paiddate,"transaction_num":payroll_status['TRANSACTION_NUMBER'],"remarks":payroll_status['REMARKS']})) update_status = Employeemonthly_payinfo.objects.using(self._current_app_schema()).filter(condition, employee_id=employee.get('id'),payroll_date__month=month,payroll_date__year=year).update(pay_status=pay_status,updated_by=user_id,updated_date=datetime.now(),paid_date=paiddate,transaction_num=payroll_status['TRANSACTION_NUMBER'],remarks=payroll_status['REMARKS']) if update_status == 0: error_obj = NWisefinError() error_obj.set_code(ErrorMessage.UNEXPECTED_ERROR) error_obj.set_description(ErrorDescription.INVALID_DATA) return error_obj else: success_obj = NWisefinSuccess() success_obj.set_status(SuccessStatus.SUCCESS) success_obj.set_message(SuccessMessage.UPDATE_MESSAGE) return success_obj except Exception as excep: obj = NWisefinError() obj.set_code(ErrorMessage.INVALID_DATA) obj.set_description(str(excep)) logger.info('Bank Based Employee Payroll Paid Status Update', str(excep)) return obj