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 void LoadExistingMemberRowInfo(EEmpPersonalInfo empInfo, EORSORecord mpfRecord, DataSet.Payroll_KTPFundStatement.ExistingMemberRow existingORSORow)
{
existingORSORow.EmpID = empInfo.EmpID;
existingORSORow.EmpNo = empInfo.EmpNo;
existingORSORow.MemberID = "";
//existingORSORow.HKID = empInfo.EmpHKID.Length < 7 ? empInfo.EmpPassportNo : empInfo.EmpHKID;
existingORSORow.EmpName = empInfo.EmpEngFullName;
existingORSORow.EmpSex = empInfo.EmpGender;
existingORSORow.EmpDOB = empInfo.EmpDateOfBirth;
existingORSORow.EmpDateJoin = empInfo.EmpDateOfJoin;
existingORSORow.PeriodFrom = mpfRecord.ORSORecPeriodFr;
existingORSORow.PeriodTo = mpfRecord.ORSORecPeriodTo;
existingORSORow.ORSOPlanID = mpfRecord.ORSOPlanID;
existingORSORow.RelevantIncome = 0;
existingORSORow.EE = 0;
existingORSORow.EE = 0;
existingORSORow.ER = 0;
existingORSORow.ER = 0;
existingORSORow.MpfMCEE = 0;
existingORSORow.MpfMCER = 0;
existingORSORow.MpfVCEE = 0;
existingORSORow.MpfVCER = 0;
existingORSORow.TermCode = "";
DBFilter empTerminationFilter = new DBFilter();
empTerminationFilter.add(new Match("EmpID", empInfo.EmpID));
empTerminationFilter.add(new Match("EmpTermLastDate", "<=", mpfRecord.ORSORecPeriodTo));
empTerminationFilter.add(new Match("EmpTermLastDate", ">=", mpfRecord.ORSORecPeriodFr));
ArrayList empTerminations = EEmpTermination.db.select(dbConn, empTerminationFilter);
if (empTerminations.Count > 0)
{
EEmpTermination empTermination = (EEmpTermination)empTerminations[0];
existingORSORow.LastEmploymentDate = empTermination.EmpTermLastDate;
ECessationReason cessationReason = new ECessationReason();
cessationReason.CessationReasonID = empTermination.CessationReasonID;
ECessationReason.db.select(dbConn, cessationReason);
existingORSORow.TermCode = cessationReason.CessationReasonCode;
}
// get member ID
DBFilter m_empFieldFilter = new DBFilter();
m_empFieldFilter.add(new Match("EmpExtraFieldName", "P-Fund Member ID"));
int m_extraFieldID = 0;
ArrayList m_fieldList = EEmpExtraField.db.select(dbConn, m_empFieldFilter);
if (m_fieldList.Count > 0)
{
m_extraFieldID = ((EEmpExtraField)m_fieldList[0]).EmpExtraFieldID;
}
if (m_extraFieldID > 0)
{
DBFilter m_empFieldValueFilter = new DBFilter();
m_empFieldValueFilter.add(new Match("EmpID", empInfo.EmpID));
m_empFieldValueFilter.add(new Match("EmpExtraFieldID", m_extraFieldID));
ArrayList m_fieldValueList = EEmpExtraFieldValue.db.select(dbConn, m_empFieldValueFilter);
if (m_fieldValueList.Count > 0)
{
existingORSORow.MemberID = ((EEmpExtraFieldValue)m_fieldValueList[0]).EmpExtraFieldValue;
}
}
// get P-Fund effective date (i.e. 1st contribution date)
DBFilter m_empOrsoPlanFilter = new DBFilter();
m_empOrsoPlanFilter.add(new Match("EmpID", empInfo.EmpID));
m_empOrsoPlanFilter.add("EmpORSOEffFr", true);
ArrayList m_orsoPlanList = EEmpORSOPlan.db.select(dbConn, m_empOrsoPlanFilter);
if (m_orsoPlanList.Count > 0)
{
existingORSORow.OrsoEffDate = ((EEmpORSOPlan)m_orsoPlanList[0]).EmpORSOEffFr;
}
}
