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 DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = detailBinding.createFilter();
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
filter.add(info.orderby, info.order);
}
else
{
filter.add("emp.EmpNo", true);
}
filter.add(new Match("d.BatchID", CurID));
//filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
/****************************************************************************
* SELECT d.BatchID, d.DetailID, emp.EmpNo, emp.EmpEngSurname, emp.EmpEngOthername, emp.EmpAlias, d.SchemeCode, d.Capacity, d.CurrentPoint, d.NewPoint
* FROM [PS_SalaryIncrementBatchDetail] AS d
* INNER JOIN [EmpPersonalInfo] AS emp ON d.EmpID = emp.EmpID
****************************************************************************/
string select = "d.BatchID, d.DetailID, emp.EmpID, emp.EmpNo, emp.EmpEngSurname, emp.EmpEngOthername, emp.EmpAlias, d.SchemeCode, d.Capacity, d.CurrentPoint, d.NewPoint ";
string from = "FROM [PS_SalaryIncrementBatchDetail] AS d " +
"INNER JOIN [EmpPersonalInfo] AS emp ON d.EmpID = emp.EmpID ";
DataTable table = filter.loadData(dbConn, null, select, from);
foreach (DataColumn col in table.Columns)
{
if (col.DataType.Equals(typeof(string)))
{
DBAESEncryptStringFieldAttribute.decode(table, col.ColumnName);
}
}
//table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
// get BackpayProcess
ESalaryIncrementBatch m_process = ESalaryIncrementBatch.GetObject(dbConn, CurID);
if (m_process != null)
{
EUser m_user;
int m_id;
UploadBy.Text = "";
ConfirmBy.Text = "";
if (m_process.UploadBy > 0)
{
m_user = EUser.GetObject(dbConn, m_process.UploadBy);
if (m_user != null)
{
UploadBy.Text = m_user.UserName;
}
}
if (m_process.ConfirmBy > 0)
{
m_user = EUser.GetObject(dbConn, m_process.ConfirmBy);
if (m_user != null)
{
ConfirmBy.Text = m_user.UserName;
}
}
}
return(view);
}
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);
}
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);
}