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