public override void ImportToDatabase()
{
DataTable dataTable = GetImportDataFromTempDatabase(null);
if (dataTable.Rows.Count > 0)
{
EClaimsAndDeductionsImportBatch batchDetail = new EClaimsAndDeductionsImportBatch();
batchDetail.CNDImportBatchDateTime = AppUtils.ServerDateTime();
//batchDetail.CNDImportBatchOriginalFilename = OriginalBatchFilename;
batchDetail.CNDImportBatchRemark = Remark;
batchDetail.CNDImportBatchUploadedBy = m_UserID;
EClaimsAndDeductionsImportBatch.db.insert(dbConn, batchDetail);
foreach (DataRow row in dataTable.Rows)
{
EUploadClaimsAndDeductions obj = new EUploadClaimsAndDeductions();
EUploadClaimsAndDeductions.db.toObject(row, obj);
EClaimsAndDeductions CND = new EClaimsAndDeductions();
CND.CNDAmount = obj.CNDAmount;
CND.CNDEffDate = obj.CNDEffDate;
CND.CNDNumOfDayAdj = obj.CNDNumOfDayAdj;
CND.CNDPayMethod = obj.CNDPayMethod;
CND.CNDRemark = obj.CNDRemark;
CND.CurrencyID = obj.CurrencyID;
CND.EmpAccID = obj.EmpAccID;
CND.EmpID = obj.EmpID;
CND.PayCodeID = obj.PayCodeID;
CND.CostCenterID = obj.CostCenterID;
CND.CNDIsRestDayPayment = obj.CNDIsRestDayPayment;
CND.CNDImportBatchID = batchDetail.CNDImportBatchID;
EClaimsAndDeductions.db.insert(dbConn, CND);
EUploadClaimsAndDeductions.db.delete(dbConn, obj);
//DBFilter dbfilter = new DBFilter();
//dbfilter.add(new Match("EmpID", obj.EmpID));
//dbfilter.add(new Match("CNDEffDate", obj.CNDEffDate));
//dbfilter.add(new Match("PayCodeID", obj.PayCodeID));
//dbfilter.add(new Match("CNDPayMethod", obj.CNDPayMethod));
//dbfilter.add(new Match("EmpAccID", obj.EmpAccID));
//dbfilter.add(new Match("
}
}
}
public DataTable UploadToTempDatabase(DataTable rawDataTable, int UserID)
{
if (rawDataTable == null)
{
return(GetImportDataFromTempDatabase(null));
}
int rowCount = 1;
//ArrayList results = new ArrayList();
try
{
foreach (DataRow row in rawDataTable.Rows)
{
rowCount++;
string EmpNo = row[FIELD_EMP_NO].ToString();
int EmpID = HROne.Import.Parse.GetEmpID(dbConn, EmpNo, UserID);
if (EmpID < 0)
{
errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() });
}
string PaymentCode = row[FIELD_PAYMENT_CODE].ToString();
int PayCodeID = HROne.Import.Parse.GetPaymentCodeID(dbConn, PaymentCode);
string BankCode = row[FIELD_BANK_ACCOUNT_NO].ToString();
int BankAccID = HROne.Import.Parse.GetEmpAccID(dbConn, BankCode, EmpID);
string EffDateString = row[FIELD_EFFECTIVE_DATE].ToString();
DateTime EffDate = Import.Parse.toDateTimeObject(row[FIELD_EFFECTIVE_DATE]);
//if (EffDateString.Trim().Length == 8 && long.TryParse(EffDateString, out tryEffDateString))
//{
// EffDate = new DateTime(int.Parse(EffDateString.Substring(0, 4)), int.Parse(EffDateString.Substring(4, 2)), int.Parse(EffDateString.Substring(6, 2)));
//}
string PayMethod = row[FIELD_PAYMENT_METHOD].ToString().Trim();
string Remark = row[FIELD_REMARK].ToString();
string amountString = row[FIELD_AMOUNT].ToString();
double amount = 0;
double NumOfDayAdjust = 0;
if (rawDataTable.Columns.Contains(FIELD_NUM_OF_DAY_ADJUST))
{
string NumOfDayAdjustString = row[FIELD_NUM_OF_DAY_ADJUST].ToString();
if (!string.IsNullOrEmpty(NumOfDayAdjustString.Trim()))
{
if (!double.TryParse(NumOfDayAdjustString, out NumOfDayAdjust))
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_NUM_OF_DAY_ADJUST + "=" + NumOfDayAdjustString, EmpNo, rowCount.ToString() });
}
}
}
bool IsRestDayPayment = false;
if (rawDataTable.Columns.Contains(FIELD_REST_PAYMENT))
{
if (row.IsNull(FIELD_REST_PAYMENT))
{
IsRestDayPayment = false;
}
else
{
IsRestDayPayment = (row[FIELD_REST_PAYMENT].ToString().Equals("Y", StringComparison.CurrentCultureIgnoreCase) || row[FIELD_REST_PAYMENT].ToString().Equals("Yes", StringComparison.CurrentCultureIgnoreCase));
}
}
int CostCenterID = 0;
if (rawDataTable.Columns.Contains(FIELD_COST_CENTER))
{
string CostCenter = row[FIELD_COST_CENTER].ToString();
if (!string.IsNullOrEmpty(CostCenter))
{
CostCenterID = HROne.Import.Parse.GetCostCenterID(dbConn, CostCenter, false, UserID);
if (CostCenterID <= 0)
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_COST_CENTER + "=" + CostCenter, EmpNo, rowCount.ToString() });
}
}
}
//if (!double.TryParse(amountString, out amount))
//{
// throw new ImportException("Amount", amountString, "Invalid Number Format");
//}
if (EmpID > 0 && PayCodeID > 0 && (BankAccID > 0 || BankAccID == 0 && BankCode.Trim().Equals(string.Empty)) && EffDate.Ticks != 0 && double.TryParse(amountString, out amount))
{
EUploadClaimsAndDeductions CND = new EUploadClaimsAndDeductions();
CND.EmpID = EmpID;
CND.CNDEffDate = EffDate;
CND.PayCodeID = PayCodeID;
CND.CurrencyID = HROne.Lib.ExchangeCurrency.DefaultCurrency();
CND.EmpAccID = BankAccID;
CND.CNDNumOfDayAdj = NumOfDayAdjust;
if (PayMethod.Equals("Autopay", StringComparison.CurrentCultureIgnoreCase))
{
CND.CNDPayMethod = "A";
if (CND.EmpAccID == 0)
{
EEmpBankAccount bankAccount = EEmpBankAccount.GetDefaultBankAccount(dbConn, CND.EmpID);
if (bankAccount != null)
{
//CND.EmpAccID = bankAccount.EmpBankAccountID;
}
else
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_BANK_ACCOUNT_NO + "=" + BankCode, EmpNo, rowCount.ToString() });
}
}
}
else if (PayMethod.Equals("Cash", StringComparison.CurrentCultureIgnoreCase))
{
CND.CNDPayMethod = "C";
}
else if (PayMethod.Equals("Cheque", StringComparison.CurrentCultureIgnoreCase))
{
CND.CNDPayMethod = "Q";
}
else
{
CND.CNDPayMethod = "O";
}
CND.CNDIsRestDayPayment = IsRestDayPayment;
CND.CostCenterID = CostCenterID;
CND.CNDRemark = Remark;
CND.CNDAmount = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(amount, 2, 2);
CND.SessionID = m_SessionID;
CND.TransactionDate = UploadDateTime;
CND.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE;
db.insert(dbConn, CND);
//results.Add(CND);
}
else
{
if (EmpID == 0)
{
errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() });
}
else if (PayCodeID == 0)
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() });
}
else if (PayCodeID == 0)
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_PAYMENT_CODE + "=" + PaymentCode, EmpNo, rowCount.ToString() });
}
else if (EffDate.Ticks == 0)
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_EFFECTIVE_DATE + "=" + EffDateString, EmpNo, rowCount.ToString() });
}
else if (BankAccID == 0 && !BankCode.Trim().Equals(string.Empty))
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_BANK_ACCOUNT_NO + "=" + BankCode, EmpNo, rowCount.ToString() });
}
else if (!double.TryParse(amountString, out amount))
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { FIELD_AMOUNT + "=" + amountString, EmpNo, rowCount.ToString() });
}
}
}
}
catch (Exception e)
{
errors.addError(e.Message, null);
}
if (errors.List.Count > 0)
{
ClearTempTable();
throw (new HRImportException(rawDataTable.TableName + "\r\n" + errors.Message()));
}
return(GetImportDataFromTempDatabase(null));
}