protected override System.Data.DataSet CreateDataSource()
{
System.Data.DataSet dataSet = new System.Data.DataSet();
DataTable empInfoTable = new DataTable("EmpInfo");
DataTable payrollTable = new DataTable("Payment");
dataSet.Tables.Add(empInfoTable);
dataSet.Tables.Add(payrollTable);
payrollTable.Columns.Add("EmpPayrollID", typeof(string));
int firstDetailColumnPos = payrollTable.Columns.Count;
empInfoTable.Columns.Add("Employee No.", typeof(string));
empInfoTable.Columns.Add("EmpPayrollID", typeof(int));
empInfoTable.Columns.Add("Employee Name", typeof(string));
empInfoTable.Columns.Add("Alias", typeof(string));
empInfoTable.Columns.Add("Company", typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
//***** Start 2013/11/22, Ricky So, Special handle display by staff level (not in hierarchy setting)
if (intHierarchyLevelID == STAFF_LEVEL_ID)
{
hierarchyLevelGroupingFieldName = "Employee Name";
}
//***** End 2013/11/22, Ricky So, Special handle display by staff level (not in hierarchy setting)
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
if (hlevel.HLevelID.Equals(intHierarchyLevelID))
{
hierarchyLevelGroupingFieldName = hlevel.HLevelDesc;
}
empInfoTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
empInfoTable.Columns.Add("Payroll Group", typeof(string));
empInfoTable.Columns.Add("Date Join", typeof(DateTime));
empInfoTable.Columns.Add("Date Left", typeof(DateTime));
empInfoTable.Columns.Add("Net Payable", typeof(double));
empInfoTable.Columns.Add("MCEE", typeof(double));
empInfoTable.Columns.Add("MCER", typeof(double));
empInfoTable.Columns.Add("VCEE", typeof(double));
empInfoTable.Columns.Add("VCER", typeof(double));
empInfoTable.Columns.Add("PFundEE", typeof(double));
empInfoTable.Columns.Add("PFundER", typeof(double));
DBFilter payPeriodFilter = new DBFilter();
if (!PeriodFrom.Ticks.Equals(0))
{
payPeriodFilter.add(new Match("PayPeriodTo", ">=", PeriodFrom));
}
if (!PeriodTo.Ticks.Equals(0))
{
payPeriodFilter.add(new Match("PayPeriodTo", "<=", PeriodTo));
}
ArrayList payPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
if (payPeriodList.Count > 0)
{
// reset period
PeriodFrom = new DateTime();
PeriodTo = new DateTime();
}
DBFilter m_userCompanyFilter = new DBFilter();
m_userCompanyFilter.add(new Match("UserID", currentUser.UserID));
DBFilter m_userRankFilter = new DBFilter();
m_userRankFilter.add(new Match("UserID", currentUser.UserID));
Hashtable m_userCompanyList = new Hashtable();
foreach (EUserCompany m_userCompany in EUserCompany.db.select(dbConn, m_userCompanyFilter))
{
m_userCompanyList.Add(m_userCompany.CompanyID, m_userCompany.CompanyID);
}
Hashtable m_userRankList = new Hashtable();
foreach (EUserRank m_userRank in EUserRank.db.select(dbConn, m_userRankFilter))
{
m_userRankList.Add(m_userRank.RankID, m_userRank.RankID);
}
int[] m_EmpIDList = new int[empList.Count];
int i = 0;
foreach (EEmpPersonalInfo m_info in empList)
{
m_EmpIDList[i] = m_info.EmpID;
i++;
}
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
if (PeriodFrom > payPeriod.PayPeriodFr || PeriodFrom.Ticks.Equals(0))
{
PeriodFrom = payPeriod.PayPeriodFr;
}
if (PeriodTo < payPeriod.PayPeriodTo || PeriodTo.Ticks.Equals(0))
{
PeriodTo = payPeriod.PayPeriodTo;
}
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
empPayrollFilter.add(new IN("EmpID", m_EmpIDList));
ArrayList empPayrollList = EEmpPayroll.db.select(dbConn, empPayrollFilter);
foreach (EEmpPayroll empPayroll in empPayrollList)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPayroll.EmpID;
EEmpPersonalInfo.db.select(dbConn, empInfo);
DataRow row = empInfoTable.NewRow();
DataRow paymentRow = payrollTable.NewRow();
row["Employee No."] = empInfo.EmpNo;
row["EmpPayrollID"] = empPayroll.EmpPayrollID;
paymentRow["EmpPayrollID"] = empPayroll.EmpPayrollID;
row["Employee Name"] = empInfo.EmpEngFullName;
row["Alias"] = empInfo.EmpAlias;
row["Date Join"] = empInfo.EmpDateOfJoin;
DBFilter empTerminationFilter = new DBFilter();
empTerminationFilter.add(new Match("EmpID", empInfo.EmpID));
ArrayList empTerminationList = EEmpTermination.db.select(dbConn, empTerminationFilter);
if (empTerminationList.Count > 0)
{
row["Date Left"] = ((EEmpTermination)empTerminationList[0]).EmpTermLastDate;
}
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
if (!m_userCompanyList.Contains(empPos.CompanyID) || !m_userRankList.Contains(empPos.RankID))
{
continue;
}
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row["Company"] = company.CompanyName;
}
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = empPos.PayGroupID;
if (EPayrollGroup.db.select(dbConn, payrollGroup))
{
row["Payroll Group"] = payrollGroup.PayGroupDesc;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
}
double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, MCEE = 0, MCER = 0, VCEE = 0, VCER = 0, PFUNDEE = 0, PFUNDER = 0;
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode payCode = new EPaymentCode();
payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, payCode);
string fieldName = payCode.PaymentCodeDesc;
if (payrollTable.Columns[fieldName] == null)
{
payrollTable.Columns.Add(new DataColumn(fieldName, typeof(double)));
}
if (paymentRow[fieldName] == null || paymentRow[fieldName] == DBNull.Value)
{
paymentRow[fieldName] = 0;
}
paymentRow[fieldName] = (double)paymentRow[fieldName] + paymentRecord.PayRecActAmount;
netAmount += paymentRecord.PayRecActAmount;
if (payCode.PaymentCodeIsMPF)
{
releventIncome += paymentRecord.PayRecActAmount;
}
else
{
nonRelevantIncome += paymentRecord.PayRecActAmount;
}
}
row["Net Payable"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
//row["Relevant Income"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(releventIncome, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
//row["Non-Relevant Income"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(nonRelevantIncome, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
foreach (EMPFRecord mpfRecord in mpfRecords)
{
VCER += mpfRecord.MPFRecActVCER;
MCER += mpfRecord.MPFRecActMCER;
VCEE += mpfRecord.MPFRecActVCEE;
MCEE += mpfRecord.MPFRecActMCEE;
}
row["MCEE"] = MCEE;
row["VCEE"] = VCEE;
row["MCER"] = MCER;
row["VCER"] = VCER;
ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
foreach (EORSORecord orsoRecord in orsoRecords)
{
PFUNDER += orsoRecord.ORSORecActER;
PFUNDEE += orsoRecord.ORSORecActEE;
}
row["PFundEE"] = PFUNDEE;
row["PFundER"] = PFUNDER;
empInfoTable.Rows.Add(row);
payrollTable.Rows.Add(paymentRow);
}
}
DBFilter paymentCodeFilter = new DBFilter();
paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
paymentCodeFilter.add("PaymentCode", false);
ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
foreach (EPaymentCode paymentCode in paymentCodeList)
{
if (payrollTable.Columns.Contains(paymentCode.PaymentCodeDesc))
{
payrollTable.Columns[paymentCode.PaymentCodeDesc].SetOrdinal(firstDetailColumnPos);
}
}
return(dataSet);
}
public DataSet GenerateCND()
{
EBonusProcess m_process = EBonusProcess.GetObject(dbConn, m_BonusProcessID);
// EPaymentCode m_paymentCode = EPaymentCode.GetObject(dbConn, m_process.BonusProcessPayCodeID);
DataSet dataSet = new DataSet();//export.GetDataSet();
DataTable dataTable = new DataTable("ClaimsAndDeduction$");
dataSet.Tables.Add(dataTable);
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_EMP_NO, typeof(string));
dataTable.Columns.Add("English Name", typeof(string));
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_EFFECTIVE_DATE, typeof(DateTime));
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_CODE, typeof(string));
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_METHOD, typeof(string));
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_BANK_ACCOUNT_NO, typeof(string));
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_AMOUNT, typeof(double));
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_NUM_OF_DAY_ADJUST, typeof(double));
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_REST_PAYMENT, typeof(string));
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_REMARK, typeof(string));
dataTable.Columns.Add(HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_COST_CENTER, typeof(string));
DBFilter m_detailFilter = new DBFilter();
m_detailFilter.add(new Match("BonusProcessID", m_BonusProcessID));
m_detailFilter.add("EmpID", true);
m_detailFilter.add("EmpBonusProcessType", false); // Standard > Discretionary
double m_bonusAmount = 0;
double m_targetSalary = 0;
int m_currentEmpID = -1;
string m_payMethod = "";
string m_remarks = "";
string m_bankAccountCode = "";
string m_costCenterCode = "";
DataRow m_row = null;
EEmpPersonalInfo m_empInfo = null;
double m_proportion = 0; // the proportion of bonus recieved, depending on the join date
EBonusProcess m_bonusProcess = EBonusProcess.GetObject(dbConn, m_BonusProcessID);
foreach (EEmpBonusProcess m_detail in EEmpBonusProcess.db.select(dbConn, m_detailFilter))
{
if (m_detail.EmpID != m_currentEmpID)
{
if (m_currentEmpID > -1)
{
dataTable.Rows.Add(m_row);
}
m_currentEmpID = m_detail.EmpID;
m_row = dataTable.NewRow();
GetEmpRecurringPaymentInfo(m_detail.EmpID, m_process.BonusProcessSalaryMonth, out m_targetSalary, out m_payMethod, out m_bankAccountCode, out m_costCenterCode);
m_empInfo = EEmpPersonalInfo.GetObject(dbConn, m_detail.EmpID);
System.TimeSpan m_totalDaysInPeriod = m_bonusProcess.BonusProcessPeriodTo.Subtract(m_bonusProcess.BonusProcessPeriodFr);
System.TimeSpan m_totalDaysJoint = m_bonusProcess.BonusProcessPeriodTo.Subtract((m_empInfo.EmpDateOfJoin < m_bonusProcess.BonusProcessPeriodFr) ? m_bonusProcess.BonusProcessPeriodFr : m_empInfo.EmpDateOfJoin);
m_proportion = Math.Round(Convert.ToDouble(m_totalDaysJoint.Days + 1) / Convert.ToDouble(m_totalDaysInPeriod.Days + 1), 4);
m_remarks = "";
m_bonusAmount = 0;
//re-take targetSalary from RecurringPayment
//m_targetSalary = m_detail.EmpBonusProcessTargetSalary;
}
else // same EmpID
{
m_remarks += " + ";
}
if (m_detail.EmpBonusProcessType == "S")
{
m_bonusAmount = m_targetSalary * m_proportion * m_bonusProcess.BonusProcessStdRate;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessStdRate.ToString("0.0000") + ")";
}
else if (m_detail.EmpBonusProcessType == "D")
{
if (m_detail.EmpBonusProcessRank == "1")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank1 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank1.ToString("0.00") + "%)";
}
else if (m_detail.EmpBonusProcessRank == "2")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank2 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank2.ToString("0.00") + "%)";
}
else if (m_detail.EmpBonusProcessRank == "3")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank3 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank3.ToString("0.00") + "%)";
}
else if (m_detail.EmpBonusProcessRank == "4")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank4 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank4.ToString("0.00") + "%)";
}
else if (m_detail.EmpBonusProcessRank == "5")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank5 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank5.ToString("0.00") + "%)";
}
}
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_EMP_NO] = m_empInfo.EmpNo;
m_row["English Name"] = m_empInfo.EmpEngFullName;
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_EFFECTIVE_DATE] = m_process.BonusProcessPayDate;
EPaymentCode m_payCodeObj = EPaymentCode.GetObject(dbConn, m_process.BonusProcessPayCodeID);
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_CODE] = m_payCodeObj.PaymentCode;
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_METHOD] = m_payMethod;
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_NUM_OF_DAY_ADJUST] = 0; //DateTime.DaysInMonth(m_process.AsAtDate);
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_REST_PAYMENT] = "No";
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_AMOUNT] = Math.Round(m_bonusAmount, 2);
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_REMARK] = m_remarks;
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_BANK_ACCOUNT_NO] = m_bankAccountCode;
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_COST_CENTER] = m_costCenterCode;
}
if (m_currentEmpID > -1) // indicate there are CND records written into the table.
{
dataTable.Rows.Add(m_row);
}
return(dataSet);
}