public DataTable ExportStandardBonusTemplate(bool IsIncludeCurrentPositionInfo)
{
DataTable tmpDataTable = new DataTable(TABLE_NAME_S);
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_EMP_NO, typeof(string));
if (IsIncludeCurrentPositionInfo)
{
ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable);
ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable);
}
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_TARGET_SALARY, typeof(double));
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_STD_RATE, typeof(double));
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_YEAR_OF_SERVICE, typeof(double));
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_BONUS_AMOUNT, typeof(double));
DBFilter m_filter = new DBFilter();
m_filter.add(new Match("BonusProcessID", m_BonusProcessID));
m_filter.add(new Match("EmpBonusProcessType", "S"));
m_filter.add("EmpID", true);
EBonusProcess m_bonusProcess = EBonusProcess.GetObject(dbConn, m_BonusProcessID);
foreach (EEmpBonusProcess m_empBonusProcess in EEmpBonusProcess.db.select(dbConn, m_filter))
{
EEmpPersonalInfo empInfo = EEmpPersonalInfo.GetObject(dbConn, m_empBonusProcess.EmpID);
DataRow row = tmpDataTable.NewRow();
row[FIELD_EMP_NO] = empInfo.EmpNo;
if (IsIncludeCurrentPositionInfo)
{
ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID);
ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID);
}
row[FIELD_TARGET_SALARY] = m_empBonusProcess.EmpBonusProcessTargetSalary;
row[FIELD_STD_RATE] = m_bonusProcess.BonusProcessStdRate;
row[FIELD_YEAR_OF_SERVICE] = Math.Round(m_empBonusProcess.EmpBonusProcessBonusProportion, 4);
row[FIELD_BONUS_AMOUNT] = Math.Round(m_empBonusProcess.EmpBonusProcessBonusAmount, 2);
tmpDataTable.Rows.Add(row);
}
if (IsIncludeCurrentPositionInfo)
{
ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable);
}
return(tmpDataTable);
}
protected void btnConfirm_Click(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
if (CurID > 0)
{
ESalaryIncrementBatch m_process = ESalaryIncrementBatch.GetObject(dbConn, CurID);
if (m_process.Status == ESalaryIncrementBatch.STATUS_OPEN) // not Confirmed/Applied
{
DBFilter m_filterByBatchID = new DBFilter();
m_filterByBatchID.add(new Match("BatchID", m_process.BatchID));
m_filterByBatchID.add("EmpID", true);
try
{
WebUtils.StartFunction(Session, FUNCTION_CODE, true);
dbConn.BeginTransaction();
foreach (ESalaryIncrementBatchDetail d in ESalaryIncrementBatchDetail.db.select(dbConn, m_filterByBatchID))
{
if (d.CurrentPoint != d.NewPoint)
{
DBFilter m_cpFilter = new DBFilter();
m_cpFilter.add(new NullTerm("EmpRPEffTo"));
m_cpFilter.add(new Match("EmpID", d.EmpID));
m_cpFilter.add(new Match("SchemeCode", AppUtils.Encode(EEmpRecurringPayment.db.getField("SchemeCode"), d.SchemeCode)));
m_cpFilter.add(new Match("Capacity", AppUtils.Encode(EEmpRecurringPayment.db.getField("Capacity"), d.Capacity)));
m_cpFilter.add(new Match("Point", d.CurrentPoint));
DBFilter m_payTypeCodeFilter = new DBFilter();
m_payTypeCodeFilter.add(new Match("PaymentTypeCode", "BASICSAL"));
DBFilter m_payTypeFilter = new DBFilter();
m_payTypeFilter.add(new IN("PaymentTypeID", "SELECT PaymentTypeID FROM PaymentType", m_payTypeCodeFilter));
m_cpFilter.add(new IN("PayCodeID", "SELECT PaymentCodeID FROM PaymentCode", m_payTypeFilter));
foreach (EEmpRecurringPayment m_cp in EEmpRecurringPayment.db.select(dbConn, m_cpFilter))
{
DBFilter m_payScaleFilter = new DBFilter();
m_payScaleFilter.add(new Match("SchemeCode", AppUtils.Encode(EEmpRecurringPayment.db.getField("SchemeCode"), d.SchemeCode)));
m_payScaleFilter.add(new Match("EffectiveDate", "<=", m_process.AsAtDate));
OR m_orDate = new OR();
m_orDate.add(new Match("ExpiryDate", ">=", m_process.AsAtDate));
m_orDate.add(new NullTerm("ExpiryDate"));
m_payScaleFilter.add(m_orDate);
m_payScaleFilter.add(new Match("Point", d.NewPoint));
ArrayList m_payScaleMapList = EPayScaleMap.db.select(dbConn, m_payScaleFilter);
if (m_payScaleMapList.Count > 0)
{
EEmpRecurringPayment m_newCp = new EEmpRecurringPayment();
m_newCp.EmpRPAmount = System.Convert.ToDouble(((EPayScaleMap)m_payScaleMapList[0]).Salary);
m_newCp.EmpRPAmount = Math.Round(m_newCp.EmpRPAmount, 2);
m_newCp.EmpRPEffFr = m_process.AsAtDate;
m_newCp.Point = d.NewPoint;
//m_newCp.EmpRPEffTo = m_cp.EmpRPEffTo;
m_newCp.Capacity = m_cp.Capacity;
m_newCp.CostCenterID = m_cp.CostCenterID;
m_newCp.CurrencyID = m_cp.CurrencyID;
m_newCp.EmpAccID = m_cp.EmpAccID;
m_newCp.EmpID = m_cp.EmpID;
m_newCp.EmpRPID = m_cp.EmpRPID;
m_newCp.EmpRPIsNonPayrollItem = m_cp.EmpRPIsNonPayrollItem;
m_newCp.EmpRPMethod = m_cp.EmpRPMethod;
m_newCp.EmpRPRemark = m_cp.EmpRPRemark;
m_newCp.EmpRPUnit = m_cp.EmpRPUnit;
m_newCp.EmpRPUnitPeriodAsDaily = m_cp.EmpRPUnitPeriodAsDaily;
m_newCp.EmpRPUnitPeriodAsDailyPayFormID = m_cp.EmpRPUnitPeriodAsDailyPayFormID;
m_newCp.PayCodeID = m_cp.PayCodeID;
m_newCp.SchemeCode = m_cp.SchemeCode;
m_cp.EmpRPEffTo = m_process.AsAtDate.AddDays(-1);
if (EEmpRecurringPayment.db.update(dbConn, m_cp))
{
EEmpRecurringPayment.db.insert(dbConn, m_newCp);
}
}
else
{
errors.addError(string.Format("Cannot map salary setting (Scheme Code={0}, Capacity={1}, Point={2})", new string[] { d.SchemeCode, d.Capacity, d.NewPoint.ToString("0.00") }));
dbConn.RollbackTransaction();
WebUtils.EndFunction(dbConn);
return;
}
}
}
EEmpPersonalInfo m_empInfo = EEmpPersonalInfo.GetObject(dbConn, d.EmpID);
if (m_empInfo.EmpNextSalaryIncrementDate == m_process.AsAtDate)
{
m_empInfo.EmpNextSalaryIncrementDate = m_process.AsAtDate.AddYears(1);
EEmpPersonalInfo.db.update(dbConn, m_empInfo);
}
}
m_process.Status = ESalaryIncrementBatch.STATUS_CONFIRMED;
m_process.ConfirmDateTime = AppUtils.ServerDateTime();
m_process.ConfirmBy = WebUtils.GetCurUser(Session).UserID;
ESalaryIncrementBatch.db.update(dbConn, m_process);
//errors.addError("Salary Increment Batch confirmed!");
dbConn.CommitTransaction();
WebUtils.EndFunction(dbConn);
}
catch (Exception ex)
{
dbConn.RollbackTransaction();
errors.addError(ex.Message);
WebUtils.EndFunction(dbConn);
return;
}
if (errors.isEmpty())
{
string m_message = "Salary Increment Batch confirmed!";
string m_url = "Payroll_SalaryIncrementBatch_View.aspx?BatchID=" + CurID;
WebUtils.RegisterRedirectJavaScript(this, m_url, HROne.Common.WebUtility.GetLocalizedString(m_message));
}
}
else
{
errors.addError("Batch is confirmed.");
}
}
}
protected DataTable LoadORSODetail()
{
DataTable _table = new DataSet.Payroll_MPFRemittanceStatement.ExistingMemberDataTable();
DBFilter _payPeriodFilter = new DBFilter();
_payPeriodFilter.add(new Match("PayPeriodFr", "<=", this.PayPeriodTo));
_payPeriodFilter.add(new Match("PayPeriodTo", ">=", this.PayPeriodFr));
DBFilter _empPayrollFilter = new DBFilter();
_empPayrollFilter.add(new IN("PayPeriodID", "SELECT PayPeriodID FROM PayrollPeriod", _payPeriodFilter));
DBFilter _orsoRecordFilter = new DBFilter();
_orsoRecordFilter.add(new Match("ORSOPlanID", this.ORSOPlanID));
_orsoRecordFilter.add(new IN("EmpPayrollID", "SELECT EmpPayrollID FROM EmpPayroll", _empPayrollFilter));
foreach (EORSORecord _orsoRecord in EORSORecord.db.select(dbConn, _orsoRecordFilter))
{
DataRow _newRow = _table.NewRow();
EEmpPayroll _payroll = EEmpPayroll.GetObject(dbConn, _orsoRecord.EmpPayrollID);
EEmpPersonalInfo _empInfo = EEmpPersonalInfo.GetObject(dbConn, _payroll.EmpID);
_newRow["EmpID"] = _empInfo.EmpID;
_newRow["MPFPlanID"] = _orsoRecord.ORSOPlanID;
_newRow["EmpNo"] = _empInfo.EmpNo;
_newRow["EmpName"] = _empInfo.EmpEngFullName;
_newRow["SchemeJoinDate"] = _empInfo.EmpDateOfJoin;
if (string.IsNullOrEmpty(_empInfo.EmpHKID))
{
_newRow["HKID"] = _empInfo.EmpPassportNo;
}
else
{
_newRow["HKID"] = _empInfo.EmpHKID;
}
_newRow["PeriodFrom"] = _orsoRecord.ORSORecPeriodFr;
_newRow["PeriodTo"] = _orsoRecord.ORSORecPeriodTo;
_newRow["RelevantIncome"] = 0; //_orsoRecord.ORSORecActRI;
_newRow["MCEE"] = _orsoRecord.ORSORecActEE;
_newRow["MCER"] = _orsoRecord.ORSORecActER;
_newRow["VCEE"] = 0;
_newRow["VCER"] = 0;
EEmpTermination _empTermination = EEmpTermination.GetObjectByEmpID(dbConn, _empInfo.EmpID);
if (_empTermination != null && _empTermination.EmpTermLastDate.Ticks != 0)
{
_newRow["LastEmploymentDate"] = _empTermination.EmpTermLastDate;
_newRow["IsBackPayRecord"] = (_empTermination.EmpTermLastDate > this.PayPeriodFr);
}
else
{
_newRow["LastEmploymentDate"] = DBNull.Value;
_newRow["IsBackPayRecord"] = false;
}
_newRow["IsLSP"] = false;
_newRow["IsSP"] = false;
_newRow["LSPSPAmount"] = 0;
_newRow["LSPSPAmountPaidByER"] = 0;
_table.Rows.Add(_newRow);
}
return(_table);
}
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);
}