protected void Delete_Click(object sender, EventArgs e)
{
ArrayList list = new ArrayList();
foreach (DataListItem item in Repeater.Items)
{
CheckBox c = (CheckBox)item.FindControl("DeleteItem");
HtmlInputHidden h = (HtmlInputHidden)item.FindControl("PayScaleMapID");
if (c.Checked)
{
EPayScaleMap obj = new EPayScaleMap();
obj.PayScaleMapID = Int32.Parse(h.Value);
list.Add(obj);
}
}
foreach (EPayScaleMap obj in list)
{
if (EPayScaleMap.db.select(dbConn, obj))
{
WebUtils.StartFunction(Session, FUNCTION_CODE);
db.delete(dbConn, obj);
WebUtils.EndFunction(dbConn);
}
}
view = loadData(info, db, Repeater);
//Response.Redirect(Request.Url.LocalPath);
}
protected void Repeater_ItemCommand(object source, DataListCommandEventArgs e)
{
Button b = (Button)e.CommandSource;
if (b.ID.Equals("Edit"))
{
Repeater.EditItemIndex = e.Item.ItemIndex;
view = loadData(info, db, Repeater);
WebUtils.SetEnabledControlSection(AddPanel, false);
}
else if (b.ID.Equals("Cancel"))
{
Repeater.EditItemIndex = -1;
view = loadData(info, db, Repeater);
WebUtils.SetEnabledControlSection(AddPanel, true);
}
else if (b.ID.Equals("Save"))
{
ebinding = new Binding(dbConn, db);
ebinding.add((HtmlInputHidden)e.Item.FindControl("PayScaleMapID"));
//ebinding.add(new TextBoxBinder(db, ((WebDatePicker)e.Item.FindControl("StatutoryHolidayDate")).TextBox, "StatutoryHolidayDate"));
ebinding.add(new TextBoxBinder(db, ((WebDatePicker)e.Item.FindControl("EffectiveDate")).TextBox, "EffectiveDate"));
ebinding.add(new TextBoxBinder(db, ((WebDatePicker)e.Item.FindControl("ExpiryDate")).TextBox, "ExpiryDate"));
// ebinding.add((TextBox)e.Item.FindControl("EffectiveDate"));
// ebinding.add((TextBox)e.Item.FindControl("ExpiryDate"));
ebinding.add((TextBox)e.Item.FindControl("SchemeCode"));
ebinding.add((TextBox)e.Item.FindControl("Point"));
ebinding.add((TextBox)e.Item.FindControl("Salary"));
ebinding.init(Request, Session);
EPayScaleMap obj = new EPayScaleMap();
Hashtable values = new Hashtable();
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
ebinding.toValues(values);
db.validate(errors, values);
if (!errors.isEmpty())
{
return;
}
db.parse(values, obj);
if (!AppUtils.checkDuplicate(dbConn, db, obj, errors, "Point", new Match("EffectiveDate", obj.EffectiveDate)))
{
return;
}
WebUtils.StartFunction(Session, FUNCTION_CODE);
db.update(dbConn, obj);
WebUtils.EndFunction(dbConn);
Repeater.EditItemIndex = -1;
view = loadData(info, db, Repeater);
WebUtils.SetEnabledControlSection(AddPanel, true);
}
}
protected void Repeater_ItemDataBound(object sender, DataListItemEventArgs e)
{
HROne.Common.WebUtility.WebControlsLocalization(Session, e.Item.Controls);
e.Item.FindControl("DeleteItem").Visible = IsAllowEdit;
if (e.Item.ItemIndex == Repeater.EditItemIndex)
{
ebinding = new Binding(dbConn, db);
ebinding.add((HtmlInputHidden)e.Item.FindControl("PayScaleMapID"));
//ebinding.add(new TextBoxBinder(db, ((WebDatePicker)e.Item.FindControl("StatutoryHolidayDate")).TextBox, "StatutoryHolidayDate"));
ebinding.add(new TextBoxBinder(db, ((WebDatePicker)e.Item.FindControl("EffectiveDate")).TextBox, EffectiveDate.ID));
ebinding.add(new TextBoxBinder(db, ((WebDatePicker)e.Item.FindControl("ExpiryDate")).TextBox, ExpiryDate.ID));
ebinding.add((TextBox)e.Item.FindControl("SchemeCode"));
ebinding.add((TextBox)e.Item.FindControl("Point"));
ebinding.add((TextBox)e.Item.FindControl("Salary"));
ebinding.init(Request, Session);
EPayScaleMap obj = new EPayScaleMap();
db.toObject(((DataRowView)e.Item.DataItem).Row, obj);
Hashtable values = new Hashtable();
db.populate(obj, values);
ebinding.toControl(values);
}
else
{
e.Item.FindControl("Edit").Visible = IsAllowEdit;
HtmlInputHidden h = (HtmlInputHidden)e.Item.FindControl("PayScaleMapID");
h.Value = ((DataRowView)e.Item.DataItem)["PayScaleMapID"].ToString();
}
}
protected void Add_Click(object sender, EventArgs e)
{
Repeater.EditItemIndex = -1;
EPayScaleMap c = new EPayScaleMap();
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);
if (!AppUtils.checkDuplicate(dbConn, db, c, errors, "Point"))
{
return;
}
WebUtils.StartFunction(Session, FUNCTION_CODE);
db.insert(dbConn, c);
WebUtils.EndFunction(dbConn);
EffectiveDate.Value = string.Empty;
ExpiryDate.Value = string.Empty;
Point.Text = "0.00";
Salary.Text = "0.00";
view = loadData(info, db, Repeater);
}
protected void btnUpload_Click(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
// m_list determine if generation of Recurring Payment is necessary
ArrayList m_list = EPayScaleMap.db.select(dbConn, new DBFilter());
if (BatchFile.HasFile)
{
string strTmpFolder = HROne.Common.Folder.GetOrCreateSessionTempFolder(Session.SessionID).FullName;; //Environment.GetFolderPath(Environment.SpecialFolder.InternetCache);
string strTmpFile = System.IO.Path.Combine(strTmpFolder, AppUtils.ServerDateTime().ToString("~yyyyMMddHHmmss_") + BatchFile.FileName);
BatchFile.SaveAs(strTmpFile);
ArrayList pendingList = new ArrayList();
try
{
DataTable rawDataTable = HROne.Import.ExcelImport.parse(strTmpFile, string.Empty).Tables[TABLE_NAME];
if (rawDataTable.Columns.Contains("Effective Date") && rawDataTable.Columns.Contains("Expiry Date") &&
rawDataTable.Columns.Contains("Scheme Code") &&
rawDataTable.Columns.Contains("Point") && rawDataTable.Columns.Contains("Salary"))
{
int row_count = 0;
Decimal m_point;
DateTime m_date;
Decimal m_salary;
int id;
foreach (DataRow row in rawDataTable.Rows)
{
row_count++;
EPayScaleMap c = new EPayScaleMap();
if (!row.IsNull("Effective Date") && DateTime.TryParse(((String)row["Effective Date"]), out m_date))
{
c.EffectiveDate = m_date;
}
else
{
errors.addError("Missing Effective Date in row " + row_count.ToString());
if (!errors.isEmpty())
{
return;
}
}
if (!row.IsNull("Expiry Date") && DateTime.TryParse(((String)row["Expiry Date"]), out m_date))
{
c.ExpiryDate = m_date;
}
if (!row.IsNull("Scheme Code") && row["Scheme Code"].ToString().Trim() != "")
{
c.SchemeCode = row["Scheme Code"].ToString().Trim();
}
else
{
errors.addError("Missing Scheme Code in row " + row_count.ToString());
if (errors.errors.Count > 10)
{
return;
}
}
if (!row.IsNull("Point") && Decimal.TryParse(((String)row["Point"]), out m_point))
{
c.Point = m_point;
}
else
{
errors.addError("Missing Point in row " + row_count.ToString());
if (errors.errors.Count > 10)
{
return;
}
}
if (!row.IsNull("Salary") && Decimal.TryParse(((String)row["Salary"]), out m_salary))
{
c.Salary = m_salary;
}
else
{
errors.addError("Missing Salary in row " + row_count.ToString());
if (errors.errors.Count > 10)
{
return;
}
}
pendingList.Add(c);
}
}
else
{
errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_FILE);
}
}
catch (Exception)
{
errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_FILE);
}
if (errors.isEmpty())
{
try
{
WebUtils.StartFunction(Session, FUNCTION_CODE);
dbConn.BeginTransaction();
foreach (EPayScaleMap c in pendingList)
{
// check existing point-salary-mapping
DBFilter filter = new DBFilter();
OR m_OR = new OR();
DBFieldTranscoder idTranscoder = EPayScale.db.getField("SchemeCode").transcoder;
if (idTranscoder != null)
{
filter.add(new Match("SchemeCode", idTranscoder.toDB(c.SchemeCode).ToString()));
}
//filter.add(new Match("SchemeCode", c.SchemeCode));
filter.add(new Match("Point", c.Point));
filter.add(new Match("EffectiveDate", "<=", c.EffectiveDate));
m_OR.add(new NullTerm("ExpiryDate"));
m_OR.add(new Match("ExpiryDate", ">=", c.EffectiveDate));
filter.add(m_OR);
filter.add("EffectiveDate", true);
ArrayList currentList = EPayScaleMap.db.select(dbConn, filter);
if (currentList.Count > 0)
{
foreach (EPayScaleMap m_currentItem in currentList)
{
if ((m_currentItem.ExpiryDate.Ticks != 0) && m_currentItem.ExpiryDate.CompareTo(c.EffectiveDate) < 0) // current expiry before new become effective
{
;
}
else if (m_currentItem.ExpiryDate.Ticks == 0)
{
m_currentItem.ExpiryDate = c.EffectiveDate.AddDays(-1);
EPayScaleMap.db.update(dbConn, m_currentItem);
}
else
{
errors.addError("You are trying to insert a new point (" + c.SchemeCode + " - " + c.Point.ToString("0.00") + ") with invalid Effective Date(" + c.EffectiveDate.ToString("yyyy.MM.dd") + "). ");
break;
}
}
if (errors.isEmpty())
{
EPayScaleMap.db.insert(dbConn, c);
}
}
else
{
EPayScaleMap.db.insert(dbConn, c);
}
}
if (!errors.isEmpty())
{
dbConn.RollbackTransaction();
}
else
{
if (m_list.Count > 0)
{
GenerateRecurringPayment();
}
dbConn.CommitTransaction();
}
WebUtils.EndFunction(dbConn);
}
catch (Exception)
{
errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_FILE);
dbConn.RollbackTransaction();
WebUtils.EndFunction(dbConn);
}
}
else
{
errors.addError(HROne.Translation.PageErrorMessage.ERROR_NO_RECORD_IMPORT);
}
System.IO.File.Delete(strTmpFile);
}
else
{
errors.addError(HROne.Translation.PageErrorMessage.ERROR_INVALID_FILE);
}
// Repeater.EditItemIndex = -1;
view = loadData(info, db, Repeater);
if (errors.isEmpty())
{
errors.addError(HROne.Translation.PageMessage.IMPORT_SUCCESSFUL);
}
}
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);
}
