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); }