protected bool ValidateBusinessRule(EBackpayBatchDetail inputObject, out string errorMessage)
{
if (inputObject.Point < 0)
{
errorMessage = String.Format("Point must be not be a negative number (Point = {0})", new string[] { inputObject.Point.ToString("0") });
return(false);
}
if (inputObject.Salary < 0)
{
errorMessage = string.Format("Salary must be a positive value (Salary = {0})", new string[] { inputObject.Salary.ToString("0.00") });
return(false);
}
if (HROne.Import.Parse.GetPaymentCodeID(dbConn, inputObject.PaymentCode) <= 0)
{
errorMessage = string.Format("Invalid Payment Code ({0}).", new string[] { inputObject.PaymentCode });
return(false);
}
if (inputObject.BackpayDate.CompareTo(inputObject.EffectiveDate) <= 0)
{
errorMessage = string.Format("Backpay Date({0}) must be after Effective Date({1}).", inputObject.BackpayDate.ToString("yyyy-MM-dd"), inputObject.EffectiveDate.ToString("yyyy-MM-dd"));
return(false);
}
errorMessage = "";
return(true);
}
protected EBackpayBatchDetail Transform(DataRow row, int UserID)
{
EBackpayBatchDetail m_new = new EBackpayBatchDetail();
m_new.AnnounceDate = HROne.Import.Parse.toDateTimeObject(row[FIELD_ANNOUNCE_DATE]);
m_new.EffectiveDate = HROne.Import.Parse.toDateTimeObject(row[FIELD_EFFECTIVE_DATE]);
m_new.BackpayDate = HROne.Import.Parse.toDateTimeObject(row[FIELD_BACKPAY_DATE]);
m_new.SchemeCode = row[FIELD_SCHEME_CODE].ToString().Trim();
m_new.Point = HROne.Import.Parse.toInteger(row[FIELD_POINT]);
m_new.CurrentSalary = HROne.Import.Parse.toDecimal(row[FIELD_CURRENT_SALARY]);
m_new.Salary = HROne.Import.Parse.toDecimal(row[FIELD_SALARY]);
m_new.PaymentCode = row[FIELD_PAYMENT_CODE].ToString();
return(m_new);
}
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);
}
