public DataView loadData(ListInfo info, DBManager db, DataList repeater)
{
//DBFilter filter = sbinding.createFilter();
DBFilter filter = new DBFilter();
if (SchemeSelect.SelectedIndex > 0)
{
filter.add(new Match("SchemeCode", AppUtils.Encode(EPayScale.db.getField("SchemeCode"), SchemeSelect.SelectedValue.ToString())));
}
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
}
else
{
info.orderby = "SchemeCode, LastPoint";
info.order = false;
}
string select = "c.*";
string from = "from " + db.dbclass.tableName + " c ";
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
protected void btnExport_Click(object sender, EventArgs e)
{
DataTable tmpDataTable = new DataTable(TABLE_NAME);
//tmpDataTable.Columns.Add("ID", typeof(Int32));
tmpDataTable.Columns.Add("Effective Date", typeof(DateTime));
tmpDataTable.Columns.Add("Expiry Date", typeof(DateTime));
tmpDataTable.Columns.Add("Scheme Code", typeof(String));
tmpDataTable.Columns.Add("Point", typeof(Decimal));
tmpDataTable.Columns.Add("Salary", typeof(Decimal));
DBFilter filter = new DBFilter(); // sbinding.createFilter();
OR m_OR = new OR();
DateTime m_asAtDate = new DateTime();
if (SchemeSelect.SelectedIndex > 0)
{
filter.add(new Match("SchemeCode", AppUtils.Encode(EPayScale.db.getField("SchemeCode"), SchemeSelect.SelectedValue.ToString())));
}
if (DateTime.TryParse(AsAtDate.TextBox.Text, out m_asAtDate))
{
filter.add(new Match("EffectiveDate", "<=", m_asAtDate));
m_OR.add(new Match("ExpiryDate", ">=", m_asAtDate));
m_OR.add(new NullTerm("ExpiryDate"));
filter.add(m_OR);
}
filter.add("Point", false);
ArrayList list = EPayScaleMap.db.select(dbConn, filter);
foreach (EPayScaleMap c in list)
{
DataRow row = tmpDataTable.NewRow();
//row["ID"] = c.PayScaleID;
row["Effective Date"] = c.EffectiveDate;
row["Expiry Date"] = c.ExpiryDate;
row["Scheme Code"] = c.SchemeCode.ToString();
row["Point"] = c.Point;
row["Salary"] = c.Salary;
tmpDataTable.Rows.Add(row);
}
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
HROne.Export.ExcelExport excelExport = new HROne.Export.ExcelExport(exportFileName);
excelExport.Update(tmpDataTable);
WebUtils.TransmitFile(Response, exportFileName, "PayScalePoints_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
return;
}
public DataView loadData(ListInfo info, DBManager db, DataList repeater)
{
//DBFilter filter = sbinding.createFilter();
DBFilter filter = new DBFilter();
OR m_OR = new OR();
DateTime m_asAtDate = new DateTime();
if (DateTime.TryParse(AsAtDate.TextBox.Text, out m_asAtDate))
{
filter.add(new Match("EffectiveDate", "<=", m_asAtDate));
m_OR.add(new Match("ExpiryDate", ">=", m_asAtDate));
m_OR.add(new NullTerm("ExpiryDate"));
filter.add(m_OR);
}
if (SchemeSelect.SelectedIndex > 0)
{
DBFieldTranscoder idTranscoder = EPayScale.db.getField("SchemeCode").transcoder;
if (idTranscoder != null)
{
filter.add(new Match("SchemeCode", AppUtils.Encode(EPayScaleMap.db.getField("SchemeCode"), SchemeSelect.SelectedValue.ToString())));
}
}
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
//filter.add(info.orderby, info.order);
}
else
{
info.orderby = "SchemeCode, Point";
info.order = false;
}
string select = "*";
string from = "from PayscaleMap ";
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
protected void Add_Click(object sender, EventArgs e)
{
Repeater.EditItemIndex = -1;
EPayScale c = new EPayScale();
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);
AND andFilterTerm = new AND();
andFilterTerm.add(new Match("SchemeCode", AppUtils.Encode(EPayScale.db.getField("SchemeCode"), c.SchemeCode)));
andFilterTerm.add(new Match("Capacity", AppUtils.Encode(EPayScale.db.getField("Capacity"), c.Capacity)));
if (!AppUtils.checkDuplicate(dbConn, db, c, errors, "Capacity", andFilterTerm))
{
return;
}
WebUtils.StartFunction(Session, FUNCTION_CODE);
db.insert(dbConn, c);
WebUtils.EndFunction(dbConn);
SchemeCode.Text = string.Empty;
Capacity.Text = string.Empty;
FirstPoint.Text = "0.00";
MidPoint.Text = "0.00";
LastPoint.Text = "0.00";
view = loadData(info, db, Repeater);
}
protected void RefreshCapacity()
{
CapacitySelect.Items.Clear();
PointSelect.Items.Clear();
if (SchemeCode.SelectedIndex > 0)
{
DBFilter filter = new DBFilter();
filter.add(new Match("SchemeCode", AppUtils.Encode(EPayScale.db.getField("SchemeCode"), SchemeCode.SelectedValue)));
CapacitySelect.Items.Add("Not Selected");
foreach (EPayScale o in EPayScale.db.select(dbConn, filter))
{
CapacitySelect.Items.Add(o.Capacity);
}
}
}
protected void PointSelect_Changed(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
if (SchemeCode.SelectedIndex > 0 && CapacitySelect.SelectedIndex > 0 && PointSelect.Text != "")
{
DBFilter filter = new DBFilter();
OR m_OR = new OR();
filter.add(new Match("SchemeCode", AppUtils.Encode(EPayScaleMap.db.getField("SchemeCode"), SchemeCode.SelectedValue.ToString())));
filter.add(new Match("Point", Decimal.Parse(PointSelect.Text)));
if (EmpRPEffFr.Value != "")
{
filter.add(new Match("EffectiveDate", "<=", DateTime.Parse(EmpRPEffFr.Value)));
m_OR.add(new Match("ExpiryDate", ">=", DateTime.Parse(EmpRPEffFr.Value)));
m_OR.add(new NullTerm("ExpiryDate"));
}
else
{
filter.add(new Match("EffectiveDate", "<=", AppUtils.ServerDateTime()));
m_OR.add(new Match("ExpiryDate", ">=", AppUtils.ServerDateTime()));
m_OR.add(new NullTerm("ExpiryDate"));
}
filter.add(m_OR);
ArrayList m_list = EPayScaleMap.db.select(dbConn, filter);
if (m_list.Count > 0)
{
EmpRPAmount.Text = ((EPayScaleMap)m_list[0]).Salary.ToString("0.00");
}
else
{
EmpRPAmount.Text = "0.00";
errors.addError("Failed to retrieve salary");
}
}
}
protected void RefreshPoint()
{
PointSelect.Items.Clear();
if (SchemeCode.SelectedIndex > 0 && CapacitySelect.SelectedIndex > 0)
{
DBFilter filter = new DBFilter();
filter.add(new Match("SchemeCode", AppUtils.Encode(EPayScale.db.getField("SchemeCode"), SchemeCode.SelectedValue)));
filter.add(new Match("Capacity", AppUtils.Encode(EPayScale.db.getField("Capacity"), CapacitySelect.SelectedValue)));
ArrayList m_payscaleList = EPayScale.db.select(dbConn, filter);
if (m_payscaleList.Count > 0)
{
EPayScale m_payscale = (EPayScale)m_payscaleList[0];
DBFilter m_pointsFilter = new DBFilter();
OR m_OR = new OR();
m_pointsFilter.add(new Match("SchemeCode", AppUtils.Encode(EPayScaleMap.db.getField("SchemeCode"), m_payscale.SchemeCode)));
m_pointsFilter.add(new Match("Point", ">=", m_payscale.FirstPoint));
m_pointsFilter.add(new Match("Point", "<=", m_payscale.LastPoint));
if (EmpRPEffFr.Value != "")
{
m_pointsFilter.add(new Match("EffectiveDate", "<=", DateTime.Parse(EmpRPEffFr.Value)));
m_OR.add(new Match("ExpiryDate", ">=", DateTime.Parse(EmpRPEffFr.Value)));
m_OR.add(new NullTerm("ExpiryDate"));
}
else
{
m_pointsFilter.add(new Match("EffectiveDate", "<=", AppUtils.ServerDateTime()));
m_OR.add(new Match("ExpiryDate", ">=", AppUtils.ServerDateTime()));
m_OR.add(new NullTerm("ExpiryDate"));
}
m_pointsFilter.add(m_OR);
foreach (EPayScaleMap m_point in EPayScaleMap.db.select(dbConn, m_pointsFilter))
{
PointSelect.Items.Add(m_point.Point.ToString("0.00"));
}
}
}
}
private decimal GetPayScale(EEmpRecurringPayment EmpRP)
{
DBFilter m_filter = new DBFilter();
if (EmpRP.SchemeCode == null || EmpRP.SchemeCode == "" || EmpRP.Capacity == null || EmpRP.Capacity == "" || EmpRP.Point == null || EmpRP.Point < 0)
{
return(Decimal.Zero);
}
m_filter.add(new Match("SchemeCode", AppUtils.Encode(EPayScaleMap.db.getField("SchemeCode"), EmpRP.SchemeCode)));
m_filter.add(new NullTerm("ExpiryDate"));
m_filter.add(new Match("Point", EmpRP.Point));
decimal m_salary = Decimal.Zero;
ArrayList m_salaryList = EPayScaleMap.db.select(dbConn, m_filter);
if (m_salaryList.Count > 0)
{
return(((EPayScaleMap)m_salaryList[0]).Salary);
}
return(-1);
}
protected void GenerateRecurringPayment()
{
DBFilter m_filter = new DBFilter();
//ArrayList m_existing =
m_filter.add(new NullTerm("ExpiryDate"));
m_filter.add("SchemeCode", true);
m_filter.add("Point", true);
foreach (EPayScaleMap o in EPayScaleMap.db.select(dbConn, m_filter))
{
DBFilter m_filter2 = new DBFilter();
m_filter2.add(new NullTerm("EmpRPEffTo"));
m_filter2.add(new Match("SchemeCode", AppUtils.Encode(EPayScaleMap.db.getField("SchemeCode"), o.SchemeCode)));
m_filter2.add(new Match("Point", o.Point));
foreach (EEmpRecurringPayment p in EEmpRecurringPayment.db.select(dbConn, m_filter2))
{
if (p.EmpRPAmount != System.Convert.ToDouble(o.Salary))
{
EEmpRecurringPayment m_newRP = new EEmpRecurringPayment();
m_newRP.Capacity = p.Capacity;
m_newRP.CostCenterID = p.CostCenterID;
m_newRP.CurrencyID = p.CurrencyID;
m_newRP.EmpAccID = p.EmpAccID;
m_newRP.EmpID = p.EmpID;
m_newRP.EmpRPAmount = System.Convert.ToDouble(o.Salary);
// get employee position history -> get payroll group -> get payroll period
DBFilter m_filter3 = new DBFilter();
DBFilter m_filter4 = new DBFilter();
m_filter3.add(new Match("EmpID", p.EmpID));
m_filter3.add(new NullTerm("EmpPosEffTo"));
m_filter4.add(new IN("payGroupID", "SELECT payGroupID FROM EmpPositionInfo", m_filter3));
m_filter4.add(new Match("PayPeriodStatus", EPayrollPeriod.PAYPERIOD_STATUS_NORMAL_FLAG));
foreach (EPayrollPeriod m_payPeriod in EPayrollPeriod.db.select(dbConn, m_filter4))
{
m_newRP.EmpRPEffFr = m_payPeriod.PayPeriodFr;
p.EmpRPEffTo = m_payPeriod.PayPeriodFr.AddDays(-1);
}
//m_newRP.EmpRPEffFr = p.
//m_newRP.EmpRPEffTo = null;
m_newRP.EmpRPIsNonPayrollItem = p.EmpRPIsNonPayrollItem;
m_newRP.EmpRPMethod = p.EmpRPMethod;
m_newRP.EmpRPRemark = p.EmpRPRemark;
m_newRP.EmpRPUnit = p.EmpRPUnit;
m_newRP.EmpRPUnitPeriodAsDaily = p.EmpRPUnitPeriodAsDaily;
m_newRP.EmpRPUnitPeriodAsDailyPayFormID = p.EmpRPUnitPeriodAsDailyPayFormID;
m_newRP.PayCodeID = p.PayCodeID;
m_newRP.Point = p.Point;
m_newRP.SchemeCode = p.SchemeCode;
EEmpRecurringPayment.db.update(dbConn, p);
EEmpRecurringPayment.db.insert(dbConn, m_newRP);
}
}
}
}
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 DataSet GenerateCND(int ProcessID, int PaymentCodeID, DateTime PaymentDate)
{
DateTime m_paymentDate = new DateTime();
string m_paymentCode = "";
ESalaryIncrementBatch m_process = ESalaryIncrementBatch.GetObject(dbConn, ProcessID);
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("BatchID", m_process.BatchID));
m_detailFilter.add("DetailID", true);
foreach (ESalaryIncrementBatchDetail m_detail in ESalaryIncrementBatchDetail.db.select(dbConn, m_detailFilter))
{
DBFilter m_rpFilter = new DBFilter();
m_rpFilter.add(new Match("EmpRPID", m_detail.EmpRPID));
//m_rpFilter.add(new IN("EmpRPID", "SELECT EmpRPID FROM PS_SalaryIncrementBatchDetail ", m_detailFilter));
foreach (EEmpRecurringPayment m_empRP in EEmpRecurringPayment.db.select(dbConn, m_rpFilter))
{
DBFilter m_mapFilter = new DBFilter();
m_mapFilter.add(new Match("SchemeCode", AppUtils.Encode(EPayScaleMap.db.getField("SchemeCode"), m_empRP.SchemeCode)));
m_mapFilter.add(new Match("Point", m_detail.NewPoint));
m_mapFilter.add(new NullTerm("ExpiryDate"));
foreach (EPayScaleMap m_map in EPayScaleMap.db.select(dbConn, m_mapFilter))
{
decimal m_newSalary = m_map.Salary;
string m_remarks = "";
double m_amount = System.Convert.ToDouble(m_newSalary) - m_empRP.EmpRPAmount;
if (Math.Abs(m_amount) > 0.01)
{
m_remarks = String.Format("Backpay {0}: {1}-{2}.", m_process.AsAtDate.ToString("yyyy-MM"),
m_newSalary.ToString("#,##0.00"),
m_empRP.EmpRPAmount.ToString("#,##0.00"));
EEmpPersonalInfo m_empInfo = new EEmpPersonalInfo();
m_empInfo.EmpID = m_empRP.EmpID;
EEmpPersonalInfo.db.select(dbConn, m_empInfo);
DataRow m_row = dataTable.NewRow();
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_paymentDate;
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_CODE] = m_paymentCode;
switch (m_empRP.EmpRPMethod)
{
case "A":
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_METHOD] = "Autopay";
break;
case "Q":
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_METHOD] = "Cheque";
break;
case "C":
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_METHOD] = "Cash";
break;
default:
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_METHOD] = "Other";
break;
}
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_AMOUNT] = Math.Round(m_amount, 2);
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_REMARK] = m_remarks;
EEmpBankAccount m_bank = new EEmpBankAccount();
m_bank.EmpBankAccountID = m_empRP.EmpAccID;
if (EEmpBankAccount.db.select(dbConn, m_bank))
{
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_BANK_ACCOUNT_NO] = m_bank.EmpAccountNo;
}
ECostCenter m_costCenter = new ECostCenter();
m_costCenter.CostCenterID = m_empRP.CostCenterID;
if (ECostCenter.db.select(dbConn, m_costCenter))
{
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_COST_CENTER] = m_costCenter.CostCenterCode;
}
dataTable.Rows.Add(m_row);
}
}
}
}
return(dataSet);
}
protected bool ValidateBusinessRule(ESalaryIncrementBatchDetail inputObject, out string errorMessage)
{
// validate if new point is .5 or .0
if (inputObject.NewPoint % Decimal.One != (decimal)0.5 && inputObject.NewPoint % Decimal.One != Decimal.Zero)
{
errorMessage = String.Format("Point({0}) must be a mupltiple of 0.5.", new string[] { inputObject.NewPoint.ToString("0.00") });
return(false);
}
if (inputObject.CurrentPoint + ALLOWABLE_POINT_CHANGE_IN_BACKPAY < inputObject.NewPoint || inputObject.CurrentPoint - ALLOWABLE_POINT_CHANGE_IN_BACKPAY > inputObject.NewPoint)
{
errorMessage = String.Format("Point change violation. New Point ({0}) is cannot be 0.5 point more / less than the Original Point({1})", new string[] { inputObject.NewPoint.ToString("0.00"), inputObject.CurrentPoint.ToString("0.00") });
return(false);
}
if (inputObject.SchemeCode != "" && inputObject.Capacity != "" && inputObject.CurrentPoint >= 0)
{
DBFilter m_recurringFilter = new DBFilter();
m_recurringFilter.add(new NullTerm("EmpRPEffTo"));
m_recurringFilter.add(new Match("EmpID", inputObject.EmpID));
string m_schemeCode = AppUtils.Encode(EEmpRecurringPayment.db.getField("SchemeCode"), inputObject.SchemeCode);
string m_capacity = AppUtils.Encode(EEmpRecurringPayment.db.getField("Capacity"), inputObject.Capacity);
m_recurringFilter.add(new Match("SchemeCode", m_schemeCode));
m_recurringFilter.add(new Match("Capacity", m_capacity));
m_recurringFilter.add(new Match("Point", inputObject.CurrentPoint));
DBFilter m_paymentTypeFilter = new DBFilter();
m_paymentTypeFilter.add(new Match("PaymentTypeCode", "BASICSAL"));
DBFilter m_paymentCodeFilter = new DBFilter();
m_paymentCodeFilter.add(new IN("PaymentTypeID", "SELECT PaymentTypeID FROM PaymentType", m_paymentTypeFilter));
m_recurringFilter.add(new IN("PayCodeID", "SELECT PaymentCodeID FROM PaymentCode", m_paymentCodeFilter));
m_recurringFilter.add("EmpRPID", false);
ArrayList m_recurringList = EEmpRecurringPayment.db.select(dbConn, m_recurringFilter);
if (m_recurringList.Count > 0) // recurring payment matched
{
EEmpRecurringPayment m_recurringPayment = (EEmpRecurringPayment)m_recurringList[0];
// cross check if payscale is out of range
DBFilter m_payScaleFilter = new DBFilter();
m_payScaleFilter.add(new Match("SchemeCode", m_schemeCode));
m_payScaleFilter.add(new Match("Capacity", m_capacity));
ArrayList m_payScaleList = EPayScale.db.select(dbConn, m_payScaleFilter);
if (m_payScaleList.Count > 0)
{
EPayScale m_payScale = (EPayScale)m_payScaleList[0];
if (inputObject.NewPoint < m_payScale.FirstPoint || inputObject.NewPoint > m_payScale.LastPoint)
{
errorMessage = string.Format("New Point({0}) is out of range({1}-{2}) according to PayScale Setup.", new string[] { inputObject.NewPoint.ToString("0.00"), m_payScale.FirstPoint.ToString("0.00"), m_payScale.LastPoint.ToString("0.00") });
return(false);
}
}
}
else
{
errorMessage = string.Format("Recurring Payment not matched. (Scheme Code={0}, Capacity={1}, Point={2})", new string[] { inputObject.SchemeCode, inputObject.Capacity, inputObject.CurrentPoint.ToString("0.00") });
return(false);
}
}
else
{
errorMessage = string.Format("Recurring Payment not matched. (Scheme Code={0}, Capacity={1}, Point={2})", new string[] { inputObject.SchemeCode, inputObject.Capacity, inputObject.CurrentPoint.ToString("0.00") });
return(false);
}
errorMessage = "";
return(true);
}
public override DataTable UploadToTempDatabase(string Filename, int UserID, string ZipPassword)
{
ESalaryIncrementBatch m_processHeader = ESalaryIncrementBatch.GetObject(dbConn, m_processID);
if (m_processHeader == null)
{
errors.addError("Salary Increment Batch ID is not initialized!", new string[] { "" });
return(null);
}
DataTable rawDataTable = HROne.Import.ExcelImport.parse(Filename, ZipPassword).Tables[0];
ArrayList results = new ArrayList();
int rowCount = 0;
string m_errorMessage;
string m_colName;
foreach (DataRow row in rawDataTable.Rows)
{
rowCount++;
if (!ValidateInputData(row, out m_errorMessage, out m_colName))
{
errors.addError(m_errorMessage, new string[] { rowCount.ToString("0"), m_colName });
}
else
{
ESalaryIncrementBatchDetail m_upload = Transform(row, UserID); // m_util.Transform(row);
m_upload.BatchID = m_processID;
// check batch detail existenc
DBFilter m_existingRecordFilter = new DBFilter();
m_existingRecordFilter.add(new Match("BatchID", m_processID));
m_existingRecordFilter.add(new Match("EmpID", m_upload.EmpID));
m_existingRecordFilter.add(new Match("SchemeCode", AppUtils.Encode(ESalaryIncrementBatchDetail.db.getField("SchemeCode"), m_upload.SchemeCode)));
m_existingRecordFilter.add(new Match("Capacity", AppUtils.Encode(ESalaryIncrementBatchDetail.db.getField("Capacity"), m_upload.Capacity)));
m_existingRecordFilter.add(new Match("CurrentPoint", m_upload.CurrentPoint));
ArrayList m_existingRecordList = ESalaryIncrementBatchDetail.db.select(dbConn, m_existingRecordFilter);
if (m_existingRecordList.Count > 0)
{
if (!ValidateBusinessRule(m_upload, out m_errorMessage))
{
errors.addError(m_errorMessage + "(row#={0})", new string[] { rowCount.ToString("0") });
}
else
{
ESalaryIncrementBatchDetail m_currentRecord = (ESalaryIncrementBatchDetail)m_existingRecordList[0];
m_currentRecord.NewPoint = m_upload.NewPoint;
ESalaryIncrementBatchDetail.db.update(dbConn, m_currentRecord);
}
}
else
{
errors.addError("Cannot find record from batch. (Row# : {0})", new string[] { rowCount.ToString("0") });
}
}
}
if (errors.List.Count <= 0)
{
// update process header
m_processHeader.Status = ESalaryIncrementBatch.STATUS_OPEN;
m_processHeader.UploadDateTime = AppUtils.ServerDateTime();
m_processHeader.UploadBy = UserID;
ESalaryIncrementBatch.db.update(dbConn, m_processHeader);
}
return(rawDataTable);
}
public override DataTable UploadToTempDatabase(string Filename, int UserID, string ZipPassword)
{
DataTable rawDataTable = HROne.Import.ExcelImport.parse(Filename, ZipPassword).Tables[0];
ArrayList results = new ArrayList();
int rowCount = 0;
string m_errorMessage;
string m_colName;
try
{
dbConn.BeginTransaction();
foreach (DataRow row in rawDataTable.Rows)
{
rowCount++;
if (!ValidateInputData(row, out m_errorMessage, out m_colName))
{
errors.addError(m_errorMessage, new string[] { rowCount.ToString("0"), m_colName });
}
else
{
EBackpayBatchDetail m_upload = Transform(row, UserID); // m_util.Transform(row);
if (!ValidateBusinessRule(m_upload, out m_errorMessage))
{
errors.addError(m_errorMessage + " (row#={0})", new string[] { rowCount.ToString("0") });
}
else
{
// update payscalemap
DBFilter m_existingRecordFilter = new DBFilter();
m_existingRecordFilter.add(new Match("SchemeCode", AppUtils.Encode(EBackpayBatchDetail.db.getField("SchemeCode"), m_upload.SchemeCode)));
m_existingRecordFilter.add(new NullTerm("ExpiryDate"));
m_existingRecordFilter.add(new Match("Point", m_upload.Point));
ArrayList m_existingRecordList = EPayScaleMap.db.select(dbConn, m_existingRecordFilter);
EPayScaleMap m_newMap = new EPayScaleMap();
m_newMap.SchemeCode = m_upload.SchemeCode;
m_newMap.Point = m_upload.Point;
m_newMap.Salary = m_upload.Salary;
m_newMap.EffectiveDate = m_upload.EffectiveDate;
if (m_existingRecordList.Count > 0)
{
EPayScaleMap m_currentMap = (EPayScaleMap)m_existingRecordList[0];
if (m_upload.EffectiveDate < m_currentMap.EffectiveDate)
{
errors.addError("Invalid Effective Date({0}). It must be before current effective date({1}).",
new string[] { m_upload.EffectiveDate.ToString("yyyy-MM-dd"), m_currentMap.EffectiveDate.ToString("yyyy-MM-dd") });
break;
}
else
{
// expire current map
m_currentMap.ExpiryDate = m_upload.EffectiveDate.AddDays(-1);
EPayScaleMap.db.update(dbConn, m_currentMap);
// insert new salary
EPayScaleMap.db.insert(dbConn, m_newMap);
EBackpayBatchDetail.db.insert(dbConn, m_upload);
}
}
else
{
EPayScaleMap.db.insert(dbConn, m_newMap);
EBackpayBatchDetail.db.insert(dbConn, m_upload);
}
// go through recurring payment
DBFilter m_recurringPaymentFilter = new DBFilter();
DBFilter m_paymentTypeFilter = new DBFilter();
DBFilter m_paymentCodeFilter = new DBFilter();
m_paymentTypeFilter.add(new Match("PaymentTypeCode", "BASICSAL"));
m_paymentCodeFilter.add(new IN("PaymentTypeID", "SELECT PaymentTypeID FROM PaymentType", m_paymentTypeFilter));
m_recurringPaymentFilter.add(new IN("PayCodeID", "SELECT PaymentCodeID FROM PaymentCode", m_paymentCodeFilter));
m_recurringPaymentFilter.add(new Match("SchemeCode", AppUtils.Encode(EEmpRecurringPayment.db.getField("SchemeCode"), m_upload.SchemeCode)));
m_recurringPaymentFilter.add(new Match("Point", m_upload.Point));
m_recurringPaymentFilter.add(new Match("EmpRpEffFr", "<=", m_upload.EffectiveDate));
OR m_orDate = new OR();
m_orDate.add(new NullTerm("EmpRpEffTo"));
m_orDate.add(new Match("EmpRPEffTo", ">=", m_upload.EffectiveDate));
m_recurringPaymentFilter.add(m_orDate);
m_recurringPaymentFilter.add("EmpID", true);
m_recurringPaymentFilter.add("EmpRpEffFr", true);
foreach (EEmpRecurringPayment m_rp in EEmpRecurringPayment.db.select(dbConn, m_recurringPaymentFilter))
{
// find payment records of each recurring payment
DBFilter m_PaymentRecordFilter = new DBFilter();
DBFilter m_EmpPayrollFilter = new DBFilter();
DBFilter m_PayrollPeriodFilter = new DBFilter();
m_PayrollPeriodFilter.add(new Match("PayPeriodFr", ">=", m_upload.EffectiveDate));
m_EmpPayrollFilter.add(new Match("EmpID", m_rp.EmpID));
m_EmpPayrollFilter.add(new IN("PayPeriodID", "SELECT PayPeriodID FROM PayrollPeriod", m_PayrollPeriodFilter));
m_PaymentRecordFilter.add(new Match("PaymentCodeID", m_rp.PayCodeID));
m_PaymentRecordFilter.add(new IN("EmpPayrollID", "SELECT EmpPayrollID FROM EmpPayroll", m_EmpPayrollFilter));
string m_remarks = "";
double m_amount = 0;
foreach (EPaymentRecord m_payRecords in EPaymentRecord.db.select(dbConn, m_PaymentRecordFilter))
{
if (m_remarks != "")
{
m_remarks = m_remarks + " + ";
}
m_remarks = ((m_payRecords.PayRecActAmount / m_rp.EmpRPAmount) * (System.Convert.ToDouble(m_upload.Salary) - m_rp.EmpRPAmount)).ToString("0.00");
m_amount = ((m_payRecords.PayRecActAmount / m_rp.EmpRPAmount) * (System.Convert.ToDouble(m_upload.Salary) - m_rp.EmpRPAmount));
}
// insert to claims & deduction
EClaimsAndDeductions m_cnd = new EClaimsAndDeductions();
m_cnd.CNDEffDate = m_upload.BackpayDate;
m_cnd.CNDAmount = m_amount;
m_cnd.EmpID = m_rp.EmpID;
m_cnd.CNDRemark = m_remarks;
m_cnd.EmpAccID = m_rp.EmpAccID;
m_cnd.CNDPayMethod = m_rp.EmpRPMethod;
m_cnd.CostCenterID = m_rp.CostCenterID;
m_cnd.CurrencyID = m_rp.CurrencyID;
m_cnd.PayCodeID = HROne.Import.Parse.GetPaymentCodeID(dbConn, m_upload.PaymentCode);
EClaimsAndDeductions.db.insert(dbConn, m_cnd);
}
}
}
}
dbConn.CommitTransaction();
}
catch (SqlException sqlEx)
{
errors.addError(sqlEx.Message, new string[] { });
dbConn.RollbackTransaction();
}
return(rawDataTable);
}
protected DataTable ConvertData(DataTable _staffTable, string _whereClause, int _jobCategory)
{
DataTable _table = CreateEmptyTable();
foreach (DataRow _row in _staffTable.Select(_whereClause))
{
DataRow _newRow = _table.NewRow();
if (_staffTable.Columns.Contains("EmpName"))
{
_newRow[FIELD_MEMBER_NAME] = _row["EmpName"];
}
else
{
_newRow[FIELD_MEMBER_NAME] = _row["EmpSurname"] + " " + _row["EmpOtherName"];
}
_newRow[FIELD_CERT_NO] = "";
_newRow[FIELD_HKID] = _row["HKID"].ToString();
_newRow[FIELD_JOB_CATEGORY] = _jobCategory.ToString("0");
_newRow[FIELD_EMPLOYMENT_DATE] = _row["SchemeJoinDate"];
_newRow[FIELD_FROM] = _row["PeriodFrom"];
_newRow[FIELD_TO] = _row["PeriodTo"];
_newRow[FIELD_RELEVANT_INCOME] = _row["RelevantIncome"];
_newRow[FIELD_MCEE] = _row["MCEE"];
_newRow[FIELD_MCER] = _row["MCER"];
_newRow[FIELD_VCEE] = _row["VCEE"];
_newRow[FIELD_VCER] = _row["VCER"];
_newRow[FIELD_SURCHARGE] = 0;
if (_staffTable.Columns.Contains("LastEmploymentDate") && _row["LastEmploymentDate"] != DBNull.Value)
{
_newRow[FIELD_LAST_DATE_OF_EMPLOYMENT] = _row["LastEmploymentDate"];
_newRow[FIELD_LSP_SP_ENTITLMENT] = "Y";
}
else
{
_newRow[FIELD_LAST_DATE_OF_EMPLOYMENT] = DBNull.Value;
_newRow[FIELD_LSP_SP_ENTITLMENT] = "";
}
_newRow[FIELD_REMARKS] = ""; //^ 1. Rejoin 2. Intra-group transfer 3. back-payment for terminated member 4. Over age 65 5. Overseas member 6.New in Billing Class 02 7. Work < 60 days
if (_table.TableName == "NewJoinMember")
{
DBFilter _terminatedStaffFilter = new DBFilter();
_terminatedStaffFilter.add(new Match("EmpStatus", "T"));
string _encryptedHKID;
string _encryptedPassportNo;
_encryptedHKID = AppUtils.Encode(EEmpPersonalInfo.db.getField("EmpHKID"), _row["HKID"].ToString());
_encryptedPassportNo = AppUtils.Encode(EEmpPersonalInfo.db.getField("EmpPassportNo"), _row["HKID"].ToString());
OR _or = new OR();
_or.add(new Match("EmpHKID", _encryptedHKID));
_or.add(new Match("EmpPassportNo", _encryptedHKID));
_terminatedStaffFilter.add(_or);
ArrayList _terminatedStaffList = EEmpPersonalInfo.db.select(dbConn, _terminatedStaffFilter);
if (_terminatedStaffList.Count > 0)
{
_newRow[FIELD_REMARKS] = "1"; // Rejoin
}
}
if (_jobCategory == 1)
{
// get extra fields from EmpExtraField
EEmpExtraField _extraField1 = EEmpExtraField.GetObjectByName(dbConn, "MPF Topup Cert. No");
if (_extraField1 != null)
{
DBFilter m_filter = new DBFilter();
m_filter.add(new Match("EmpID", (int)_row["EmpID"]));
m_filter.add(new Match("EmpExtraFieldID", _extraField1.EmpExtraFieldID));
ArrayList m_list = EEmpExtraFieldValue.db.select(dbConn, m_filter);
if (m_list.Count > 0)
{
_newRow[FIELD_CERT_NO] = ((EEmpExtraFieldValue)m_list[0]).EmpExtraFieldValue;
}
}
}
else
{
EEmpExtraField _extraField2 = EEmpExtraField.GetObjectByName(dbConn, "VC Cert. No");
if (_extraField2 != null)
{
DBFilter m_filter = new DBFilter();
m_filter.add(new Match("EmpID", (int)_row["EmpID"]));
m_filter.add(new Match("EmpExtraFieldID", _extraField2.EmpExtraFieldID));
ArrayList m_list = EEmpExtraFieldValue.db.select(dbConn, m_filter);
if (m_list.Count > 0)
{
_newRow[FIELD_CERT_NO] = ((EEmpExtraFieldValue)m_list[0]).EmpExtraFieldValue;
}
}
}
_table.Rows.Add(_newRow);
}
return(_table);
}
