protected void Delete_ClickTop(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); EORSOPlan o = new EORSOPlan(); o.ORSOPlanID = CurID; if (db.select(dbConn, o)) { DBFilter empORSOFilter = new DBFilter(); empORSOFilter.add(new Match("ORSOPlanID", o.ORSOPlanID)); empORSOFilter.add("empid", true); ArrayList empORSOList = EEmpORSOPlan.db.select(dbConn, empORSOFilter); if (empORSOList.Count > 0) { int curEmpID = 0; errors.addError(string.Format(HROne.Translation.PageErrorMessage.ERROR_CODE_USED_BY_EMPLOYEE, new string[] { HROne.Common.WebUtility.GetLocalizedString("P-Fund Plan Code"), o.ORSOPlanCode })); foreach (EEmpORSOPlan empORSOPlan in empORSOList) { EEmpPersonalInfo empInfo = new EEmpPersonalInfo(); empInfo.EmpID = empORSOPlan.EmpID; if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { if (curEmpID != empORSOPlan.EmpID) { errors.addError("- " + empInfo.EmpNo + ", " + empInfo.EmpEngFullName); curEmpID = empORSOPlan.EmpID; } else { EEmpORSOPlan.db.delete(dbConn, empORSOPlan); } } } errors.addError(HROne.Translation.PageErrorMessage.ERROR_ACTION_ABORT); return; } else { WebUtils.StartFunction(Session, FUNCTION_CODE); db.delete(dbConn, o); DBFilter obj = new DBFilter(); obj.add(new Match("ORSOPlanID", o.ORSOPlanID)); ArrayList objList = EORSOPlanDetail.db.select(dbConn, obj); foreach (EORSOPlanDetail match in objList) { EORSOPlanDetail.db.delete(dbConn, match); } WebUtils.EndFunction(dbConn); } } HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "ORSOPlan_List.aspx"); }
protected void Save_Click(object sender, EventArgs e) { EORSOPlan c = new EORSOPlan(); Hashtable values = new Hashtable(); binding.toValues(values); PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); db.validate(errors, values); if (!errors.isEmpty()) { return; } db.parse(values, c); if (!AppUtils.checkDuplicate(dbConn, db, c, errors, "ORSOPlanCode")) { return; } WebUtils.StartFunction(Session, FUNCTION_CODE); if (CurID < 0) { // Utils.MarkCreate(Session, c); db.insert(dbConn, c); CurID = c.ORSOPlanID; // url = Utils.BuildURL(-1, CurID); } else { // Utils.Mark(Session, c); db.update(dbConn, c); } WebUtils.EndFunction(dbConn); HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "ORSOPlan_View.aspx?ORSOPlanID=" + CurID); }
//private static double CalculateEEAmount(DatabaseConnection dbConn, EEmpPersonalInfo empInfo, int ORSOPlanID, double TotalVCRI, EPayrollPeriod payrollPeriod, DateTime ORSOJoinDate) //{ // double totalVC = 0; // EORSOPlan orsoPlan = new EORSOPlan(); // orsoPlan.ORSOPlanID = ORSOPlanID; // if (EORSOPlan.db.select(dbConn, orsoPlan)) // { // // Use Service Year to Compare // double YearOfService = HROne.Payroll.PayrollProcess.GetYearOfServer(dbConn, empInfo.EmpID, payrollPeriod.PayPeriodFr); // EORSOPlanDetail orsoPlanDetailFrom = orsoPlan.GetORSOPlanDetail(dbConn, YearOfService); // if (TotalVCRI <= 0) // return 0; // else // { // totalVC = TotalVCRI * orsoPlanDetailFrom.ORSOPlanDetailEE/ 100; // totalVC += orsoPlanDetailFrom.ORSOPlanDetailEEFix; // return totalVC < orsoPlan.ORSOPlanMaxEmployeeVC ? totalVC : orsoPlan.ORSOPlanMaxEmployeeVC; // } // } // else // return 0; //} // End 0000084, Ricky So, 2014-08-22 // Start 0000084, Ricky So, 2014-08-22 private static double CalculateERAmount(DatabaseConnection dbConn, EEmpPersonalInfo empInfo, int ORSOPlanID, double TotalVCRI, EPayrollPeriod payrollPeriod, EMPFRecord mpfRecord) { double totalVC = 0; EORSOPlan orsoPlan = new EORSOPlan(); orsoPlan.ORSOPlanID = ORSOPlanID; if (EORSOPlan.db.select(dbConn, orsoPlan)) { // Use Service Year to Compare double YearOfService = HROne.Payroll.PayrollProcess.GetYearOfServer(dbConn, empInfo.EmpID, payrollPeriod.PayPeriodFr); EORSOPlanDetail orsoPlanDetailFrom = orsoPlan.GetORSOPlanDetail(dbConn, YearOfService); if (orsoPlanDetailFrom == null || TotalVCRI <= 0) { return(0); } else { totalVC = TotalVCRI * orsoPlanDetailFrom.ORSOPlanDetailER / 100; // Cap before residual if (orsoPlan.ORSOPlanEmployerResidual) { // Start 0000084, Ricky So, 2014-08-22 totalVC = (totalVC > orsoPlan.ORSOPlanEmployerResidualCap) ? orsoPlan.ORSOPlanEmployerResidualCap : totalVC; // End 0000084, Ricky So, 2014-08-22 // Deduct MPF contribution (use residual) if (mpfRecord != null) { totalVC = (totalVC > mpfRecord.MPFRecActMCER) ? (totalVC - mpfRecord.MPFRecActMCER) : 0; } } totalVC += orsoPlanDetailFrom.ORSOPlanDetailERFix; return(totalVC < orsoPlan.ORSOPlanMaxEmployerVC ? totalVC : orsoPlan.ORSOPlanMaxEmployerVC); } } else { return(0); } }
protected bool loadObject() { obj = new EORSOPlan(); bool isNew = WebFormWorkers.loadKeys(db, obj, DecryptedRequest); if (!db.select(dbConn, obj)) { return(false); } if (string.IsNullOrEmpty(obj.ORSOPlanEmployerRoundingRule) && string.IsNullOrEmpty(obj.ORSOPlanEmployeeRoundingRule)) { obj.ORSOPlanEmployerRoundingRule = Values.ROUNDING_RULE_ROUND_TO; obj.ORSOPlanEmployerDecimalPlace = 2; obj.ORSOPlanEmployeeRoundingRule = Values.ROUNDING_RULE_ROUND_TO; obj.ORSOPlanEmployeeDecimalPlace = 2; } Hashtable values = new Hashtable(); db.populate(obj, values); binding.toControl(values); return(true); }
public static string GetValueFromID(DatabaseConnection dbConn, string fieldName, string fieldValue) { try { string tmpfieldName = fieldName; if (tmpfieldName.Equals("PreviousEmpID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("NewEmpID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "EmpID"; } if (tmpfieldName.EndsWith("PaymentCodeID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.EndsWith("PayCodeID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PaymentCodeID"; } if (tmpfieldName.Equals("DefaultMPFPlanID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "MPFPlanID"; } if (tmpfieldName.Equals("AttendanceFormulaPayFormID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("ReferencePayFormID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PayFormID"; } if (tmpfieldName.StartsWith("LeaveCode", StringComparison.CurrentCultureIgnoreCase) && tmpfieldName.EndsWith("Formula", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PayFormID"; } if (tmpfieldName.StartsWith("PayGroup", StringComparison.CurrentCultureIgnoreCase) && tmpfieldName.EndsWith("Formula", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PayFormID"; } if (tmpfieldName.Equals("CurrentPayPeriodID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PayPeriodID"; } if (tmpfieldName.Equals("EmpPosDefaultRosterCodeID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "RosterCodeID"; } if (tmpfieldName.Equals("RosterClientMappingSiteCodeToHLevelID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "HLevelID"; } if (tmpfieldName.Equals("EmpFirstAuthorizationGp", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("EmpSecondAuthorizationGp", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "AuthorizationGroupID"; } if (tmpfieldName.StartsWith("A", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("ALProrataRoundingRuleID", StringComparison.CurrentCultureIgnoreCase)) { EALProrataRoundingRule obj = new EALProrataRoundingRule(); obj.ALProrataRoundingRuleID = int.Parse(fieldValue); if (EALProrataRoundingRule.db.select(dbConn, obj)) { return(obj.ALProrataRoundingRuleCode + " - " + obj.ALProrataRoundingRuleDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("AttendancePlanID", StringComparison.CurrentCultureIgnoreCase)) { EAttendancePlan obj = new EAttendancePlan(); obj.AttendancePlanID = int.Parse(fieldValue); if (EAttendancePlan.db.select(dbConn, obj)) { return(obj.AttendancePlanCode + " - " + obj.AttendancePlanDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("AuthorizationGroupID", StringComparison.CurrentCultureIgnoreCase)) { EAuthorizationGroup obj = new EAuthorizationGroup(); obj.AuthorizationGroupID = int.Parse(fieldValue); if (EAuthorizationGroup.db.select(dbConn, obj)) { return(obj.AuthorizationCode + " - " + obj.AuthorizationDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("AVCPlanID", StringComparison.CurrentCultureIgnoreCase)) { EAVCPlan obj = new EAVCPlan(); obj.AVCPlanID = int.Parse(fieldValue); if (EAVCPlan.db.select(dbConn, obj)) { return(obj.AVCPlanCode + " - " + obj.AVCPlanDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("C", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("CessationReasonID", StringComparison.CurrentCultureIgnoreCase)) { ECessationReason obj = new ECessationReason(); obj.CessationReasonID = int.Parse(fieldValue); if (ECessationReason.db.select(dbConn, obj)) { return(obj.CessationReasonCode + " - " + obj.CessationReasonDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("CompanyID", StringComparison.CurrentCultureIgnoreCase)) { ECompany obj = new ECompany(); obj.CompanyID = int.Parse(fieldValue); if (ECompany.db.select(dbConn, obj)) { return(obj.CompanyCode + " - " + obj.CompanyName); } else { return(string.Empty); } } if (tmpfieldName.Equals("CostAllocationDetailID", StringComparison.CurrentCultureIgnoreCase)) { ECostAllocationDetail obj = new ECostAllocationDetail(); obj.CostAllocationDetailID = int.Parse(fieldValue); if (ECostAllocationDetail.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "PayCodeID", obj.PaymentCodeID.ToString()) + ", " + GetValueFromID(dbConn, "CostCenterID", obj.CostCenterID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("CostAllocationID", StringComparison.CurrentCultureIgnoreCase)) { ECostAllocation obj = new ECostAllocation(); obj.CostAllocationID = int.Parse(fieldValue); if (ECostAllocation.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "EmpPayrollID", obj.EmpPayrollID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("CostCenterID", StringComparison.CurrentCultureIgnoreCase)) { ECostCenter obj = new ECostCenter(); obj.CostCenterID = int.Parse(fieldValue); if (ECostCenter.db.select(dbConn, obj)) { return(obj.CostCenterCode + " - " + obj.CostCenterDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("D", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("DocumentTypeID", StringComparison.CurrentCultureIgnoreCase)) { EDocumentType obj = new EDocumentType(); obj.DocumentTypeID = int.Parse(fieldValue); if (EDocumentType.db.select(dbConn, obj)) { return(obj.DocumentTypeCode + " - " + obj.DocumentTypeDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("E", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("EmpAccID", StringComparison.CurrentCultureIgnoreCase)) { EEmpBankAccount obj = new EEmpBankAccount(); obj.EmpBankAccountID = int.Parse(fieldValue); if (EEmpBankAccount.db.select(dbConn, obj)) { return(obj.EmpBankCode + "-" + obj.EmpBranchCode + "-" + obj.EmpAccountNo); } //return obj.EmpBankCode + "-" + obj.EmpBranchCode + "-" + string.Empty.PadRight(obj.EmpAccountNo.Length, 'X'); else { return(string.Empty); } } if (tmpfieldName.Equals("EmpCostCenterID", StringComparison.CurrentCultureIgnoreCase)) { EEmpCostCenter obj = new EEmpCostCenter(); obj.EmpCostCenterID = int.Parse(fieldValue); if (EEmpCostCenter.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "EmpID", obj.EmpID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmpExtraFieldID", StringComparison.CurrentCultureIgnoreCase)) { EEmpExtraField obj = new EEmpExtraField(); obj.EmpExtraFieldID = int.Parse(fieldValue); if (EEmpExtraField.db.select(dbConn, obj)) { return(obj.EmpExtraFieldName); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmpID", StringComparison.CurrentCultureIgnoreCase)) { EEmpPersonalInfo obj = new EEmpPersonalInfo(); obj.EmpID = int.Parse(fieldValue); if (EEmpPersonalInfo.db.select(dbConn, obj)) { return(obj.EmpNo + " - " + obj.EmpEngFullName); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmpPayrollID", StringComparison.CurrentCultureIgnoreCase)) { EEmpPayroll obj = new EEmpPayroll(); obj.EmpPayrollID = int.Parse(fieldValue); if (EEmpPayroll.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "EmpID", obj.EmpID.ToString()) + " : " + GetValueFromID(dbConn, "PayPeriodID", obj.PayPeriodID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmpPosID", StringComparison.CurrentCultureIgnoreCase)) { EEmpPositionInfo obj = new EEmpPositionInfo(); obj.EmpPosID = int.Parse(fieldValue); if (EEmpPositionInfo.db.select(dbConn, obj)) { return(obj.EmpPosEffFr.ToString("yyyy-MM-dd") + " - " + (obj.EmpPosEffTo.Ticks.Equals(0) ? "Present" : obj.EmpPosEffTo.ToString("yyyy-MM-dd"))); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmploymentTypeID", StringComparison.CurrentCultureIgnoreCase)) { EEmploymentType obj = new EEmploymentType(); obj.EmploymentTypeID = int.Parse(fieldValue); if (EEmploymentType.db.select(dbConn, obj)) { return(obj.EmploymentTypeCode + " - " + obj.EmploymentTypeDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("F", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("FunctionID", StringComparison.CurrentCultureIgnoreCase)) { ESystemFunction obj = new ESystemFunction(); obj.FunctionID = int.Parse(fieldValue); if (ESystemFunction.db.select(dbConn, obj)) { return(obj.FunctionCode + " - " + obj.Description); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("H", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("HElementID", StringComparison.CurrentCultureIgnoreCase)) { EHierarchyElement obj = new EHierarchyElement(); obj.HElementID = int.Parse(fieldValue); if (EHierarchyElement.db.select(dbConn, obj)) { return(obj.HElementCode + " - " + obj.HElementDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("HLevelID", StringComparison.CurrentCultureIgnoreCase)) { EHierarchyLevel obj = new EHierarchyLevel(); obj.HLevelID = int.Parse(fieldValue); if (EHierarchyLevel.db.select(dbConn, obj)) { return(obj.HLevelCode + " - " + obj.HLevelDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("L", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("LeaveTypeID", StringComparison.CurrentCultureIgnoreCase)) { ELeaveType obj = new ELeaveType(); obj.LeaveTypeID = int.Parse(fieldValue); if (ELeaveType.db.select(dbConn, obj)) { return(obj.LeaveType + " - " + obj.LeaveTypeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("LeaveCodeID", StringComparison.CurrentCultureIgnoreCase)) { ELeaveCode obj = new ELeaveCode(); obj.LeaveCodeID = int.Parse(fieldValue); if (ELeaveCode.db.select(dbConn, obj)) { return(obj.LeaveCode + " - " + obj.LeaveCodeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("LeavePlanID", StringComparison.CurrentCultureIgnoreCase)) { ELeavePlan obj = new ELeavePlan(); obj.LeavePlanID = int.Parse(fieldValue); if (ELeavePlan.db.select(dbConn, obj)) { return(obj.LeavePlanCode + " - " + obj.LeavePlanDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("M", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("MPFPlanID", StringComparison.CurrentCultureIgnoreCase)) { EMPFPlan obj = new EMPFPlan(); obj.MPFPlanID = int.Parse(fieldValue); if (EMPFPlan.db.select(dbConn, obj)) { return(obj.MPFPlanCode + " - " + obj.MPFPlanDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("MPFSchemeID", StringComparison.CurrentCultureIgnoreCase)) { EMPFScheme obj = new EMPFScheme(); obj.MPFSchemeID = int.Parse(fieldValue); if (EMPFScheme.db.select(dbConn, obj)) { return(obj.MPFSchemeCode + " - " + obj.MPFSchemeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("MPFSchemeCessationReasonID", StringComparison.CurrentCultureIgnoreCase)) { EMPFSchemeCessationReason obj = new EMPFSchemeCessationReason(); obj.MPFSchemeCessationReasonID = int.Parse(fieldValue); if (EMPFSchemeCessationReason.db.select(dbConn, obj)) { return(obj.MPFSchemeCessationReasonCode + " - " + obj.MPFSchemeCessationReasonDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("O", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("ORSOPlanID", StringComparison.CurrentCultureIgnoreCase)) { EORSOPlan obj = new EORSOPlan(); obj.ORSOPlanID = int.Parse(fieldValue); if (EORSOPlan.db.select(dbConn, obj)) { return(obj.ORSOPlanCode + " - " + obj.ORSOPlanDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("P", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("PayGroupID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("PayrollGroupID", StringComparison.CurrentCultureIgnoreCase)) { EPayrollGroup obj = new EPayrollGroup(); obj.PayGroupID = int.Parse(fieldValue); if (EPayrollGroup.db.select(dbConn, obj)) { return(obj.PayGroupCode + " - " + obj.PayGroupDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PayFormID", StringComparison.CurrentCultureIgnoreCase)) { EPayrollProrataFormula obj = new EPayrollProrataFormula(); obj.PayFormID = int.Parse(fieldValue); if (EPayrollProrataFormula.db.select(dbConn, obj)) { return(obj.PayFormCode + " - " + obj.PayFormDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PaymentCodeID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("PayCodeID", StringComparison.CurrentCultureIgnoreCase)) { EPaymentCode obj = new EPaymentCode(); obj.PaymentCodeID = int.Parse(fieldValue); if (EPaymentCode.db.select(dbConn, obj)) { return(obj.PaymentCode + " - " + obj.PaymentCodeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PaymentTypeID", StringComparison.CurrentCultureIgnoreCase)) { EPaymentType obj = new EPaymentType(); obj.PaymentTypeID = int.Parse(fieldValue); if (EPaymentType.db.select(dbConn, obj)) { return(obj.PaymentTypeCode + " - " + obj.PaymentTypeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PayPeriodID", StringComparison.CurrentCultureIgnoreCase)) { EPayrollPeriod obj = new EPayrollPeriod(); obj.PayPeriodID = int.Parse(fieldValue); if (EPayrollPeriod.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "PayrollGroupID", obj.PayGroupID.ToString()) + ": " + obj.PayPeriodFr.ToString("yyyy-MM-dd") + " to " + obj.PayPeriodTo.ToString("yyyy-MM-dd")); } else { return(string.Empty); } } if (tmpfieldName.Equals("PermitTypeID", StringComparison.CurrentCultureIgnoreCase)) { EPermitType obj = new EPermitType(); obj.PermitTypeID = int.Parse(fieldValue); if (EPermitType.db.select(dbConn, obj)) { return(obj.PermitTypeCode + " - " + obj.PermitTypeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PositionID", StringComparison.CurrentCultureIgnoreCase)) { EPosition obj = new EPosition(); obj.PositionID = int.Parse(fieldValue); if (EPosition.db.select(dbConn, obj)) { return(obj.PositionCode + " - " + obj.PositionDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("Q", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("QualificationID", StringComparison.CurrentCultureIgnoreCase)) { EQualification obj = new EQualification(); obj.QualificationID = int.Parse(fieldValue); if (EQualification.db.select(dbConn, obj)) { return(obj.QualificationCode + " - " + obj.QualificationDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("R", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("RankID", StringComparison.CurrentCultureIgnoreCase)) { ERank obj = new ERank(); obj.RankID = int.Parse(fieldValue); if (ERank.db.select(dbConn, obj)) { return(obj.RankCode + " - " + obj.RankDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("ReminderTypeID", StringComparison.CurrentCultureIgnoreCase)) { EReminderType obj = new EReminderType(); obj.ReminderTypeID = int.Parse(fieldValue); if (EReminderType.db.select(dbConn, obj)) { return(obj.ReminderTypeCode + " - " + obj.ReminderTypeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("RosterClientID", StringComparison.CurrentCultureIgnoreCase)) { ERosterClient obj = new ERosterClient(); obj.RosterClientID = int.Parse(fieldValue); if (ERosterClient.db.select(dbConn, obj)) { return(obj.RosterClientCode + " - " + obj.RosterClientName); } else { return(string.Empty); } } if (tmpfieldName.Equals("RosterClientSiteID", StringComparison.CurrentCultureIgnoreCase)) { ERosterClientSite obj = new ERosterClientSite(); obj.RosterClientSiteID = int.Parse(fieldValue); if (ERosterClientSite.db.select(dbConn, obj)) { return(obj.RosterClientSiteCode); } else { return(string.Empty); } } if (tmpfieldName.Equals("RosterCodeID", StringComparison.CurrentCultureIgnoreCase)) { ERosterCode obj = new ERosterCode(); obj.RosterCodeID = int.Parse(fieldValue); if (ERosterCode.db.select(dbConn, obj)) { return(obj.RosterCode + " - " + obj.RosterCodeDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("S", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("SkillID", StringComparison.CurrentCultureIgnoreCase)) { ESkill obj = new ESkill(); obj.SkillID = int.Parse(fieldValue); if (ESkill.db.select(dbConn, obj)) { return(obj.SkillCode + " - " + obj.SkillDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("SkillLevelID", StringComparison.CurrentCultureIgnoreCase)) { ESkillLevel obj = new ESkillLevel(); obj.SkillLevelID = int.Parse(fieldValue); if (ESkillLevel.db.select(dbConn, obj)) { return(obj.SkillLevelCode + " - " + obj.SkillLevelDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("StaffTypeID", StringComparison.CurrentCultureIgnoreCase)) { EStaffType obj = new EStaffType(); obj.StaffTypeID = int.Parse(fieldValue); if (EStaffType.db.select(dbConn, obj)) { return(obj.StaffTypeCode + " - " + obj.StaffTypeDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("T", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("TaxCompID", StringComparison.CurrentCultureIgnoreCase)) { ETaxCompany obj = new ETaxCompany(); obj.TaxCompID = int.Parse(fieldValue); if (ETaxCompany.db.select(dbConn, obj)) { return(obj.TaxCompEmployerName); } else { return(string.Empty); } } if (tmpfieldName.Equals("TaxEmpID", StringComparison.CurrentCultureIgnoreCase)) { ETaxEmp obj = new ETaxEmp(); obj.TaxEmpID = int.Parse(fieldValue); if (ETaxEmp.db.select(dbConn, obj)) { return(obj.TaxEmpSurname + ", " + obj.TaxEmpOtherName + ", " + GetValueFromID(dbConn, "TaxFormID", obj.TaxFormID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("TaxFormID", StringComparison.CurrentCultureIgnoreCase)) { ETaxForm obj = new ETaxForm(); obj.TaxFormID = int.Parse(fieldValue); if (ETaxForm.db.select(dbConn, obj)) { return("Tax Year :" + obj.TaxFormYear + ", Form: IR56" + obj.TaxFormType); } else { return(string.Empty); } } if (tmpfieldName.Equals("TaxPayID", StringComparison.CurrentCultureIgnoreCase)) { ETaxPayment obj = new ETaxPayment(); obj.TaxPayID = int.Parse(fieldValue); if (ETaxPayment.db.select(dbConn, obj)) { return(obj.TaxPayCode + " - " + obj.TaxPayDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("TrainingCourseID", StringComparison.CurrentCultureIgnoreCase)) { ETrainingCourse obj = new ETrainingCourse(); obj.TrainingCourseID = int.Parse(fieldValue); if (ETrainingCourse.db.select(dbConn, obj)) { return(obj.TrainingCourseCode + " - " + obj.TrainingCourseName); } else { return(string.Empty); } } if (tmpfieldName.Equals("TrainingSeminarID", StringComparison.CurrentCultureIgnoreCase)) { ETrainingSeminar obj = new ETrainingSeminar(); obj.TrainingSeminarID = int.Parse(fieldValue); if (ETrainingSeminar.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "TrainingCourseID", obj.TrainingCourseID.ToString()) + ": " + obj.TrainingSeminarDateFrom.ToString("yyyy-MM-dd") + " to " + obj.TrainingSeminarDateTo.ToString("yyyy-MM-dd")); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("U", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("UserID", StringComparison.CurrentCultureIgnoreCase)) { EUser obj = new EUser(); obj.UserID = int.Parse(fieldValue); if (EUser.db.select(dbConn, obj)) { return(obj.LoginID + " - " + obj.UserName); } else { return(string.Empty); } } if (tmpfieldName.Equals("UserGroupID", StringComparison.CurrentCultureIgnoreCase)) { EUserGroup obj = new EUserGroup(); obj.UserGroupID = int.Parse(fieldValue); if (EUserGroup.db.select(dbConn, obj)) { return(obj.UserGroupName + " - " + obj.UserGroupDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("Y", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("YebPlanID", StringComparison.CurrentCultureIgnoreCase)) { EYEBPlan obj = new EYEBPlan(); obj.YEBPlanID = int.Parse(fieldValue); if (EYEBPlan.db.select(dbConn, obj)) { return(obj.YEBPlanCode + " - " + obj.YEBPlanDesc); } else { return(string.Empty); } } } if (tmpfieldName.EndsWith("ID") && !tmpfieldName.EndsWith("HKID") && !tmpfieldName.EndsWith("LoginID") && !tmpfieldName.EndsWith("CurrencyID") && !tmpfieldName.EndsWith("LeaveAppID") && !tmpfieldName.EndsWith("EmpPaymentID") && !tmpfieldName.EndsWith("PayBatchID") && !tmpfieldName.EndsWith("PayRecID") && !tmpfieldName.EndsWith("RosterTableID") && !tmpfieldName.EndsWith("SynID") && !tmpfieldName.EndsWith("CNDImportBatchID")) { if (ESystemParameter.getParameter(dbConn, "DebugMode").Equals("Y")) { throw new Exception("ID field not define:" + fieldName); } } return(string.Empty); } catch (Exception ex) { if (ESystemParameter.getParameter(dbConn, "DebugMode").Equals("Y")) { throw ex; } else { return(string.Empty); } } }
public override ReportDocument GenerateReport() { DataSet.Payroll_ORSOStatement dataSet = new DataSet.Payroll_ORSOStatement(); DataSet.Payroll_ORSOStatement.ExistingMemberDataTable existingORSO; DataSet.Payroll_ORSOStatement.ORSOPlanDataTable orsoPlan; existingORSO = dataSet.ExistingMember; orsoPlan = dataSet.ORSOPlan; if (PayPeriodFr.Ticks != 0 && PayPeriodTo.Ticks != 0 && EmpList != null) { string strPrintPeriod = PayPeriodFr.ToString("yyyy-MM-dd") + " - " + PayPeriodTo.ToString("yyyy-MM-dd"); DBFilter payPeriodFilter = new DBFilter(); payPeriodFilter.add(new Match("pp.PayPeriodFr", "<=", PayPeriodTo)); payPeriodFilter.add(new Match("pp.PayPeriodTo", ">=", PayPeriodFr)); foreach (EEmpPersonalInfo empInfo in EmpList) { EEmpPersonalInfo.db.select(dbConn, empInfo); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("EmpID", empInfo.EmpID)); empPayrollFilter.add(new IN("PayPeriodID", "Select PayPeriodID from PayrollPeriod pp", payPeriodFilter)); DBFilter orsoRecordFilter = new DBFilter(); orsoRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter)); orsoRecordFilter.add(new Match("ORSOPlanID", ORSOPlanID)); orsoRecordFilter.add("ORSORecPeriodFr", true); orsoRecordFilter.add("ORSORecPeriodTo", true); ArrayList orsoRecords = EORSORecord.db.select(dbConn, orsoRecordFilter); DataSet.Payroll_ORSOStatement.ExistingMemberRow existingORSORow = null; foreach (EORSORecord orsoRecord in orsoRecords) { EORSOPlan orsoPlanObject = new EORSOPlan(); orsoPlanObject.ORSOPlanID = orsoRecord.ORSOPlanID; if (EORSOPlan.db.select(dbConn, orsoPlanObject)) { if (orsoPlan.Select("ORSOPlanID=" + orsoPlanObject.ORSOPlanID).Length == 0) { DataSet.Payroll_ORSOStatement.ORSOPlanRow orsoPlanRow = orsoPlan.NewORSOPlanRow(); orsoPlanRow.ORSOPlanID = orsoPlanObject.ORSOPlanID; orsoPlanRow.ORSOPlanCode = orsoPlanObject.ORSOPlanCode; orsoPlanRow.ORSOPlanCompanyName = orsoPlanObject.ORSOPlanCompanyName; orsoPlanRow.ORSOPlanPayCenter = orsoPlanObject.ORSOPlanPayCenter; orsoPlanRow.ORSOPlanSchemeNo = orsoPlanObject.ORSOPlanSchemeNo; orsoPlanRow.ORSOPlanDesc = orsoPlanObject.ORSOPlanDesc; orsoPlan.Rows.Add(orsoPlanRow); } } EEmpPayroll empPayroll = new EEmpPayroll(); empPayroll.EmpPayrollID = orsoRecord.EmpPayrollID; EEmpPayroll.db.select(dbConn, empPayroll); EPayrollPeriod payrollPeriod = new EPayrollPeriod(); payrollPeriod.PayPeriodID = empPayroll.PayPeriodID; EPayrollPeriod.db.select(dbConn, payrollPeriod); { if (existingORSORow == null) { existingORSORow = existingORSO.NewExistingMemberRow(); LoadExistingMemberRowInfo(empInfo, orsoRecord, existingORSORow); } else { if (!(existingORSORow.EmpID == empInfo.EmpID && existingORSORow.PeriodFrom.Equals(orsoRecord.ORSORecPeriodFr) && existingORSORow.PeriodTo.Equals(orsoRecord.ORSORecPeriodTo))) { existingORSO.Rows.Add(existingORSORow); existingORSORow = existingORSO.NewExistingMemberRow(); LoadExistingMemberRowInfo(empInfo, orsoRecord, existingORSORow); } } existingORSORow.RelevantIncome += orsoRecord.ORSORecActRI; existingORSORow.EE += orsoRecord.ORSORecActEE; existingORSORow.ER += orsoRecord.ORSORecActER; } } if (existingORSORow != null) { existingORSO.Rows.Add(existingORSORow); } } if (reportDocument == null) { reportDocument = new ReportTemplate.Report_Payroll_PFundStatement(); } else { } reportDocument.SetDataSource(dataSet); reportDocument.SetParameterValue("ContributionPeriod", PayPeriodFr.ToString("yyyy-MM-dd") + " - " + PayPeriodTo.ToString("yyyy-MM-dd")); return(reportDocument); } else { return(null); } }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_ORSOPLAN, typeof(string)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpORSOPlan.db.select(dbConn, filter); foreach (EEmpORSOPlan empORSOPlan in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(empORSOPlan.EmpORSOID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = empORSOPlan.EmpORSOEffFr; row[FIELD_TO] = empORSOPlan.EmpORSOEffTo; EORSOPlan ORSOPlan = new EORSOPlan(); ORSOPlan.ORSOPlanID = empORSOPlan.ORSOPlanID; if (EORSOPlan.db.select(dbConn, ORSOPlan)) { row[FIELD_ORSOPLAN] = IsShowDescription ? ORSOPlan.ORSOPlanDesc : ORSOPlan.ORSOPlanCode; } if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = empORSOPlan.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
protected DataSet.Payroll_KTPFundStatement CreateDataSource() { DataSet.Payroll_KTPFundStatement dataSet = new DataSet.Payroll_KTPFundStatement(); Payroll_KTPFundStatement.ExistingMemberDataTable existingORSO = dataSet.ExistingMember; Payroll_KTPFundStatement.ORSOPlanDataTable orsoPlan = dataSet.ORSOPlan; if (_payPeriodFr.Ticks != 0 && _payPeriodTo.Ticks != 0 && EmpList != null) { string strPrintPeriod = _payPeriodFr.ToString("yyyy-MM-dd") + " - " + _payPeriodTo.ToString("yyyy-MM-dd"); DBFilter payPeriodFilter = new DBFilter(); payPeriodFilter.add(new Match("pp.PayPeriodFr", "<=", _payPeriodTo)); payPeriodFilter.add(new Match("pp.PayPeriodTo", ">=", _payPeriodFr)); foreach (EEmpPersonalInfo empInfo in EmpList) { EEmpPersonalInfo.db.select(dbConn, empInfo); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("EmpID", empInfo.EmpID)); empPayrollFilter.add(new IN("PayPeriodID", "Select PayPeriodID from PayrollPeriod pp", payPeriodFilter)); DBFilter orsoRecordFilter = new DBFilter(); orsoRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter)); orsoRecordFilter.add(new Match("ORSOPlanID", ORSOPlanID)); orsoRecordFilter.add("ORSORecPeriodFr", true); orsoRecordFilter.add("ORSORecPeriodTo", true); ArrayList orsoRecords = EORSORecord.db.select(dbConn, orsoRecordFilter); DataSet.Payroll_KTPFundStatement.ExistingMemberRow existingORSORow = null; foreach (EORSORecord orsoRecord in orsoRecords) { EORSOPlan orsoPlanObject = new EORSOPlan(); orsoPlanObject.ORSOPlanID = orsoRecord.ORSOPlanID; if (EORSOPlan.db.select(dbConn, orsoPlanObject)) { if (orsoPlan.Select("ORSOPlanID=" + orsoPlanObject.ORSOPlanID).Length == 0) { DataSet.Payroll_KTPFundStatement.ORSOPlanRow orsoPlanRow = orsoPlan.NewORSOPlanRow(); orsoPlanRow.ORSOPlanID = orsoPlanObject.ORSOPlanID; orsoPlanRow.ORSOPlanCode = orsoPlanObject.ORSOPlanCode; orsoPlanRow.ORSOPlanCompanyName = orsoPlanObject.ORSOPlanCompanyName; orsoPlanRow.ORSOPlanPayCenter = orsoPlanObject.ORSOPlanPayCenter; orsoPlanRow.ORSOPlanSchemeNo = orsoPlanObject.ORSOPlanSchemeNo; orsoPlanRow.ORSOPlanDesc = orsoPlanObject.ORSOPlanDesc; orsoPlan.Rows.Add(orsoPlanRow); } } EEmpPayroll empPayroll = new EEmpPayroll(); empPayroll.EmpPayrollID = orsoRecord.EmpPayrollID; EEmpPayroll.db.select(dbConn, empPayroll); EPayrollPeriod payrollPeriod = new EPayrollPeriod(); payrollPeriod.PayPeriodID = empPayroll.PayPeriodID; EPayrollPeriod.db.select(dbConn, payrollPeriod); { if (existingORSORow == null) { existingORSORow = existingORSO.NewExistingMemberRow(); LoadExistingMemberRowInfo(empInfo, orsoRecord, existingORSORow); } else { if (!(existingORSORow.EmpID == empInfo.EmpID && existingORSORow.PeriodFrom.Equals(orsoRecord.ORSORecPeriodFr) && existingORSORow.PeriodTo.Equals(orsoRecord.ORSORecPeriodTo))) { existingORSO.Rows.Add(existingORSORow); existingORSORow = existingORSO.NewExistingMemberRow(); LoadExistingMemberRowInfo(empInfo, orsoRecord, existingORSORow); } } existingORSORow.RelevantIncome += orsoRecord.ORSORecActRI; existingORSORow.EE += orsoRecord.ORSORecActEE; existingORSORow.ER += orsoRecord.ORSORecActER; DBFilter m_mpfRecordFilter = new DBFilter(); m_mpfRecordFilter.add(new Match("EmpPayrollID", orsoRecord.EmpPayrollID)); foreach (EMPFRecord m_mpfRecord in EMPFRecord.db.select(dbConn, m_mpfRecordFilter)) { existingORSORow.MpfMCEE += m_mpfRecord.MPFRecActMCEE; existingORSORow.MpfMCER += m_mpfRecord.MPFRecActMCER; existingORSORow.MpfVCEE += m_mpfRecord.MPFRecActVCEE; existingORSORow.MpfVCER += m_mpfRecord.MPFRecActVCER; } } } if (existingORSORow != null) { // business requirement from user, "Basic Salary over $100,000 should be shown $100,000 only if (existingORSORow.RelevantIncome > 100000) { existingORSORow.RelevantIncome = 100000; } existingORSO.Rows.Add(existingORSORow); } } } return(dataSet); }
//private static double CalculateProrataFactor(int EmpID, EPayrollPeriod payrollPeriod) //{ // EEmpPersonalInfo oEmp = new EEmpPersonalInfo(); // oEmp.EmpID = EmpID; // EEmpPersonalInfo.db.select(dbConn, oEmp); // DateTime dt1AgeMin = oEmp.EmpDateOfBirth.AddYears(AGE_MINIMUM); // DateTime dt1AgeMax = oEmp.EmpDateOfBirth.AddYears(AGE_MAXIMUM); // EEmpTermination empTermination = EEmpTermination.GetObjectByEmpID(dbConn, EmpID); // bool blnTerminated; // if (empTermination != null) // { // if (empTermination.EmpTermLastDate <= payrollPeriod.PayPeriodTo && empTermination.EmpTermLastDate >= payrollPeriod.PayPeriodFr) // blnTerminated = true; // else // blnTerminated = false; // } // else // blnTerminated = false; // double prorataFactor = 1; // if (oEmp.EmpDateOfJoin < dt1AgeMin) // { // if (dt1AgeMin <= payrollPeriod.PayPeriodTo && dt1AgeMin >= payrollPeriod.PayPeriodFr) // { // if (blnTerminated) // prorataFactor = (double)(empTermination.EmpTermLastDate.Subtract(dt1AgeMin).Days + 1) / (empTermination.EmpTermLastDate.Subtract(payrollPeriod.PayPeriodFr > oEmp.EmpDateOfJoin ? payrollPeriod.PayPeriodFr : oEmp.EmpDateOfJoin).Days + 1); // else // prorataFactor = (double)(payrollPeriod.PayPeriodTo.Subtract(dt1AgeMin).Days + 1) / (payrollPeriod.PayPeriodTo.Subtract(payrollPeriod.PayPeriodFr > oEmp.EmpDateOfJoin ? payrollPeriod.PayPeriodFr : oEmp.EmpDateOfJoin).Days + 1); // } // if (dt1AgeMin >= payrollPeriod.PayPeriodTo) // prorataFactor = 0; // } // if (dt1AgeMax <= payrollPeriod.PayPeriodTo && dt1AgeMax >= payrollPeriod.PayPeriodFr) // { // if (blnTerminated) // { // if (empTermination.EmpTermLastDate < dt1AgeMax) // prorataFactor = 1; // else // prorataFactor = 1.0 - (double)(empTermination.EmpTermLastDate.Subtract(dt1AgeMax).Days + 1) / (empTermination.EmpTermLastDate.Subtract(payrollPeriod.PayPeriodFr > oEmp.EmpDateOfJoin ? payrollPeriod.PayPeriodFr : oEmp.EmpDateOfJoin).Days + 1); // } // else // prorataFactor = 1.0 - (double)(payrollPeriod.PayPeriodTo.Subtract(dt1AgeMax).Days + 1) / (payrollPeriod.PayPeriodTo.Subtract(payrollPeriod.PayPeriodFr > oEmp.EmpDateOfJoin ? payrollPeriod.PayPeriodFr : oEmp.EmpDateOfJoin).Days + 1); // } // if (dt1AgeMax <= payrollPeriod.PayPeriodFr) // prorataFactor = 0; // return prorataFactor; //} private static EORSORecord CreateORSORecord(DatabaseConnection dbConn, int EmpID, EPayrollPeriod payrollPeriod, ArrayList paymentRecords, DateTime ORSOJoinDate, ORSOJoinType ORSOJoinType, EMPFRecord mpfRecord) { //double RIProrateFactor = CalculateProrataFactor(EmpID, payrollPeriod); EORSORecord orsoRecord = new EORSORecord(); switch (ORSOJoinType) { case ORSOJoinType.NewJoin: orsoRecord.ORSORecType = "N"; break; case ORSOJoinType.Existing: orsoRecord.ORSORecType = "E"; break; //case ORSOJoinType.Terminated: // ORSORecord.ORSORecType = "T"; //break; } orsoRecord.ORSORecCalRI = 0; orsoRecord.ORSOPlanID = GetORSOPlanID(dbConn, EmpID, payrollPeriod.PayPeriodTo); if (orsoRecord.ORSOPlanID > 0) { EEmpPersonalInfo oEmp = new EEmpPersonalInfo(); oEmp.EmpID = EmpID; EEmpPersonalInfo.db.select(dbConn, oEmp); //DateTime dt1Age18 = oEmp.EmpDateOfBirth.AddYears(18); //DateTime dt1Age65 = oEmp.EmpDateOfBirth.AddYears(65); EEmpTermination empTermination = EEmpTermination.GetObjectByEmpID(dbConn, EmpID); orsoRecord.ORSORecPeriodFr = payrollPeriod.PayPeriodFr; orsoRecord.ORSORecPeriodTo = payrollPeriod.PayPeriodTo; if (orsoRecord.ORSORecPeriodFr > oEmp.EmpDateOfJoin && orsoRecord.ORSORecPeriodTo < oEmp.EmpDateOfJoin) { orsoRecord.ORSORecPeriodFr = oEmp.EmpDateOfJoin; } //if (ORSORecord.ORSORecPeriodFr > dt1Age18 && ORSORecord.ORSORecPeriodTo < dt1Age18) // ORSORecord.ORSORecPeriodFr = dt1Age18; if (empTermination != null) { // Start 0000186, Ricky So, 2016/04/15 // incomplete month --> no orso contribution if (orsoRecord.ORSORecPeriodTo > empTermination.EmpTermLastDate && orsoRecord.ORSORecPeriodFr <= empTermination.EmpTermLastDate) { orsoRecord.ORSORecPeriodTo = empTermination.EmpTermLastDate; orsoRecord.ORSORecActEE = 0; orsoRecord.ORSORecActER = 0; orsoRecord.ORSORecActRI = 0; orsoRecord.ORSORecCalEE = 0; orsoRecord.ORSORecCalER = 0; orsoRecord.ORSORecCalRI = 0; return(orsoRecord); } // End 0000186, Ricky So, 2016/04/15 if (orsoRecord.ORSORecPeriodTo < empTermination.EmpTermLastDate && orsoRecord.ORSORecPeriodFr >= empTermination.EmpTermLastDate) { orsoRecord.ORSORecPeriodTo = empTermination.EmpTermLastDate; } } //if (ORSORecord.ORSORecPeriodTo < dt1Age65 && ORSORecord.ORSORecPeriodFr > dt1Age65) // ORSORecord.ORSORecPeriodTo = dt1Age65; foreach (EPaymentRecord paymentRecord in paymentRecords) { EPaymentCode paymentCode = new EPaymentCode(); paymentCode.PaymentCodeID = paymentRecord.PaymentCodeID; EPaymentCode.db.select(dbConn, paymentCode); if (paymentCode.PaymentCodeIsORSO) { orsoRecord.ORSORecCalRI += paymentRecord.PayRecActAmount; } } orsoRecord.ORSORecActRI = orsoRecord.ORSORecCalRI; if (orsoRecord.ORSOPlanID > 0) { DBFilter oldORSORecordFilter = new DBFilter(); DBFilter empIDFilter = new DBFilter(); empIDFilter.add(new Match("EmpID", EmpID)); oldORSORecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from EMPPayroll ", empIDFilter)); oldORSORecordFilter.add(new Match("ORSORecPeriodFr", "<=", orsoRecord.ORSORecPeriodTo)); oldORSORecordFilter.add(new Match("ORSORecPeriodTo", ">=", orsoRecord.ORSORecPeriodFr)); ArrayList oldORSORecords = EORSORecord.db.select(dbConn, oldORSORecordFilter); EORSORecord oldTotalORSORecord = new EORSORecord(); EORSORecord newTotalORSORecord = new EORSORecord(); foreach (EORSORecord oldORSORecord in oldORSORecords) { oldTotalORSORecord.ORSORecActRI += oldORSORecord.ORSORecActRI; oldTotalORSORecord.ORSORecActER += oldORSORecord.ORSORecActER; oldTotalORSORecord.ORSORecActEE += oldORSORecord.ORSORecActEE; } newTotalORSORecord.ORSORecCalRI = oldTotalORSORecord.ORSORecActRI + orsoRecord.ORSORecCalRI; newTotalORSORecord.ORSORecCalER = CalculateERAmount(dbConn, oEmp, orsoRecord.ORSOPlanID, newTotalORSORecord.ORSORecCalRI, payrollPeriod, mpfRecord); newTotalORSORecord.ORSORecCalEE = CalculateEEAmount(dbConn, oEmp, orsoRecord.ORSOPlanID, newTotalORSORecord.ORSORecCalRI, payrollPeriod, ORSOJoinDate, mpfRecord); orsoRecord.ORSORecCalER = newTotalORSORecord.ORSORecCalER - oldTotalORSORecord.ORSORecActER; orsoRecord.ORSORecCalEE = newTotalORSORecord.ORSORecCalEE - oldTotalORSORecord.ORSORecActEE; orsoRecord.ORSORecActER = Math.Round(orsoRecord.ORSORecCalER, 2, MidpointRounding.AwayFromZero); orsoRecord.ORSORecActEE = Math.Round(orsoRecord.ORSORecCalEE, 2, MidpointRounding.AwayFromZero); EORSOPlan orsoPlan = new EORSOPlan(); orsoPlan.ORSOPlanID = orsoRecord.ORSOPlanID; if (EORSOPlan.db.select(dbConn, orsoPlan)) { if (string.IsNullOrEmpty(orsoPlan.ORSOPlanEmployerRoundingRule)) { orsoPlan.ORSOPlanEmployerRoundingRule = Values.ROUNDING_RULE_ROUND_TO; orsoPlan.ORSOPlanEmployerDecimalPlace = 2; } if (orsoPlan.ORSOPlanEmployerRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_TO)) { orsoRecord.ORSORecActER = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(orsoRecord.ORSORecCalER, orsoPlan.ORSOPlanEmployerDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces()); } else if (orsoPlan.ORSOPlanEmployerRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_UP)) { orsoRecord.ORSORecActER = HROne.CommonLib.GenericRoundingFunctions.RoundingUp(orsoRecord.ORSORecCalER, orsoPlan.ORSOPlanEmployerDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces()); } else if (orsoPlan.ORSOPlanEmployerRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_DOWN)) { orsoRecord.ORSORecActER = HROne.CommonLib.GenericRoundingFunctions.RoundingDown(orsoRecord.ORSORecCalER, orsoPlan.ORSOPlanEmployerDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces()); } if (string.IsNullOrEmpty(orsoPlan.ORSOPlanEmployeeRoundingRule)) { orsoPlan.ORSOPlanEmployeeRoundingRule = Values.ROUNDING_RULE_ROUND_TO; orsoPlan.ORSOPlanEmployeeDecimalPlace = 2; } if (orsoPlan.ORSOPlanEmployeeRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_TO)) { orsoRecord.ORSORecActEE = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(orsoRecord.ORSORecCalEE, orsoPlan.ORSOPlanEmployeeDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces()); } else if (orsoPlan.ORSOPlanEmployeeRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_UP)) { orsoRecord.ORSORecActEE = HROne.CommonLib.GenericRoundingFunctions.RoundingUp(orsoRecord.ORSORecCalEE, orsoPlan.ORSOPlanEmployeeDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces()); } else if (orsoPlan.ORSOPlanEmployeeRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_DOWN)) { orsoRecord.ORSORecActEE = HROne.CommonLib.GenericRoundingFunctions.RoundingDown(orsoRecord.ORSORecCalEE, orsoPlan.ORSOPlanEmployeeDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces()); } } } return(orsoRecord); } else { return(null); } }
public void GenerateExcelReport(string _filename) { // F&V MPF Remittance Statement is composed of MPF and PFund data. So the current MPF Remittance Statement can only achieve 1/2 of their needs. if (PayPeriodFr.Ticks != 0 && PayPeriodTo.Ticks != 0 && EmpList != null) { // get MPF part of the remmittance statement MPFFile.GenericMPFFile mpfFile = new MPFFile.GenericMPFFile(dbConn); mpfFile.LoadMPFFileDetail(EmpList, MPFPlanID, PayPeriodFr, PayPeriodTo); System.Data.DataSet dataSet = mpfFile.CreateRemittanceStatementDataSet(); string _schemeNo = ""; string _companyName = ""; DataTable m_table = null; NPOI.HSSF.UserModel.HSSFSheet m_worksheet = null; NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); if (this.MPFPlanID > 0) { EMPFPlan _mpfPlan = EMPFPlan.GetObject(dbConn, this.MPFPlanID); if (_mpfPlan != null) { EMPFScheme _mpfScheme = EMPFScheme.GetObject(dbConn, _mpfPlan.MPFSchemeID); if (_mpfScheme != null) { _schemeNo = _mpfScheme.MPFSchemeCode; } _companyName = _mpfPlan.MPFPlanCompanyName; } m_table = ConvertData(dataSet.Tables["NewJoinMember"], "", 1); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-NewJoinMember"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1); m_table = ConvertData(dataSet.Tables["ExistingMember"], "IsBackPayRecord = false", 1); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-ExistingEmployee"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1); m_table = ConvertData(dataSet.Tables["ExistingMember"], "IsBackPayRecord = true", 1); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-TerminatedEmployee"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1); } if (this.ORSOPlanID > 0) { EORSOPlan _orsoPlan = EORSOPlan.GetObject(dbConn, ORSOPlanID); if (_orsoPlan != null) { _schemeNo = _orsoPlan.ORSOPlanSchemeNo; _companyName = _orsoPlan.ORSOPlanCompanyName; } DataTable _orsoStaffTable = LoadORSODetail(); m_table = ConvertData(_orsoStaffTable, "IsBackPayRecord = false", 2); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("2-ExistingEmployee"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 2); m_table = ConvertData(_orsoStaffTable, "IsBackPayRecord = true", 2); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("2-TerminatedEmployee"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 2); } // get ORSO part of remittance statement System.IO.FileStream file = new System.IO.FileStream(_filename, System.IO.FileMode.Create); workbook.Write(file); file.Close(); } }