public void GenerateStandardBonusData(ArrayList pEmpList)
{
//double m_targetSalary = 0;
EBonusProcess m_bonusProcess = EBonusProcess.GetObject(dbConn, m_BonusProcessID);
if (m_bonusProcess != null)
{
foreach (EEmpPersonalInfo empInfo in pEmpList)
{
if (EEmpPersonalInfo.db.select(dbConn, empInfo) && empInfo.EmpProbaLastDate <= m_bonusProcess.BonusProcessPeriodTo)
{
EEmpRecurringPayment m_recurringPayment = GetSalaryMonthRecurringPayment(empInfo.EmpID, m_bonusProcess.BonusProcessSalaryMonth);
if (m_recurringPayment != null)
{
EEmpBonusProcess m_empBonusProcess = new EEmpBonusProcess();
m_empBonusProcess.BonusProcessID = m_BonusProcessID;
m_empBonusProcess.EmpID = empInfo.EmpID;
m_empBonusProcess.EmpBonusProcessTargetSalary = m_recurringPayment.EmpRPBasicSalary;
System.TimeSpan m_totalDaysInPeriod = m_bonusProcess.BonusProcessPeriodTo.Subtract(m_bonusProcess.BonusProcessPeriodFr);
System.TimeSpan m_totalDaysJoint = m_bonusProcess.BonusProcessPeriodTo.Subtract((empInfo.EmpDateOfJoin < m_bonusProcess.BonusProcessPeriodFr) ? m_bonusProcess.BonusProcessPeriodFr : empInfo.EmpDateOfJoin);
m_empBonusProcess.EmpBonusProcessBonusProportion = Math.Round(Convert.ToDouble(m_totalDaysJoint.Days + 1) / Convert.ToDouble(m_totalDaysInPeriod.Days + 1), 4);
m_empBonusProcess.EmpBonusProcessType = "S";
m_empBonusProcess.EmpBonusProcessBonusAmount = Math.Round(m_recurringPayment.EmpRPBasicSalary *
m_empBonusProcess.EmpBonusProcessBonusProportion *
m_bonusProcess.BonusProcessStdRate, 2);
EEmpBonusProcess.db.insert(dbConn, m_empBonusProcess);
}
}
}
}
}
protected void btnGenerateCND_Click(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
EBonusProcess m_process = EBonusProcess.GetObject(dbConn, CurID);
if (m_process != null)
{
HROne.Import.ImportBonusProcess m_import = new HROne.Import.ImportBonusProcess(dbConn, Session.SessionID, WebUtils.GetCurUser(Session).UserID, CurID);
DataSet dataSet = m_import.GenerateCND();
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
export.Update(dataSet);
WebUtils.TransmitFile(Response, exportFileName, "CND_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
}
else
{
errors.addError("Failed to open batch");
}
}
public bool ConfirmBonusProcess(PageErrors pErrors)
{
EBonusProcess m_process = EBonusProcess.GetObject(dbConn, m_BonusProcessID);
if (m_process.BonusProcessStatus == EBonusProcess.STATUS_NORMAL)
{
m_process.BonusProcessStatus = EBonusProcess.STATUS_CONFIRMED;
return(EBonusProcess.db.update(dbConn, m_process));
}
return(false);
}
public DataTable ExportStandardBonusTemplate(bool IsIncludeCurrentPositionInfo)
{
DataTable tmpDataTable = new DataTable(TABLE_NAME_S);
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_EMP_NO, typeof(string));
if (IsIncludeCurrentPositionInfo)
{
ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable);
ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable);
}
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_TARGET_SALARY, typeof(double));
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_STD_RATE, typeof(double));
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_YEAR_OF_SERVICE, typeof(double));
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_BONUS_AMOUNT, typeof(double));
DBFilter m_filter = new DBFilter();
m_filter.add(new Match("BonusProcessID", m_BonusProcessID));
m_filter.add(new Match("EmpBonusProcessType", "S"));
m_filter.add("EmpID", true);
EBonusProcess m_bonusProcess = EBonusProcess.GetObject(dbConn, m_BonusProcessID);
foreach (EEmpBonusProcess m_empBonusProcess in EEmpBonusProcess.db.select(dbConn, m_filter))
{
EEmpPersonalInfo empInfo = EEmpPersonalInfo.GetObject(dbConn, m_empBonusProcess.EmpID);
DataRow row = tmpDataTable.NewRow();
row[FIELD_EMP_NO] = empInfo.EmpNo;
if (IsIncludeCurrentPositionInfo)
{
ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID);
ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID);
}
row[FIELD_TARGET_SALARY] = m_empBonusProcess.EmpBonusProcessTargetSalary;
row[FIELD_STD_RATE] = m_bonusProcess.BonusProcessStdRate;
row[FIELD_YEAR_OF_SERVICE] = Math.Round(m_empBonusProcess.EmpBonusProcessBonusProportion, 4);
row[FIELD_BONUS_AMOUNT] = Math.Round(m_empBonusProcess.EmpBonusProcessBonusAmount, 2);
tmpDataTable.Rows.Add(row);
}
if (IsIncludeCurrentPositionInfo)
{
ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable);
}
return(tmpDataTable);
}
protected DBTerm CreateFilterByProcess(string pProcessName, int pProcessID)
{
if (pProcessName == "BonusProcess")
{
EBonusProcess m_process = EBonusProcess.GetObject(dbConn, pProcessID);
Match m_match = new Match("E.EmpProbaLastDate", "<=", m_process.BonusProcessPeriodTo);
return(m_match);
}
else if (pProcessName == "DoublePayAdjustment")
{
// only staffs with commission calculation is configured through latest Recurring Payment
DBFilter m_rpFilter = new DBFilter();
OR m_or = new OR();
m_or.add(new NullTerm("EmpRPEffTo"));
m_or.add(new Match("EmpRPEffTo", ">=", Utility.LastDateOfMonth(AppUtils.ServerDateTime())));
//m_rpFilter.add(new NullTerm("NOT EmpRPFPS"));
m_rpFilter.add(m_or);
m_rpFilter.add(new Match("EmpRPEffFr", "<=", Utility.LastDateOfMonth(AppUtils.ServerDateTime())));
m_rpFilter.add(new Match("EmpRPFPS", ">", 0));
m_rpFilter.add(new Match("EmpRPFPS", "<", 100));
//m_rpFilter.add(new NullTerm("NOT EmpRPBasicSalary"));
m_rpFilter.add(new Match("EmpRPBasicSalary", ">", 0));
m_rpFilter.add(AppUtils.GetPayemntCodeDBTermByPaymentType(dbConn, "PayCodeID", "BASICSAL"));
// check probation end date
DBFilter m_EmpPersonalFilter = new DBFilter();
m_EmpPersonalFilter.add(new Match("EmpProbaLastDate", "<=", new DateTime(AppUtils.ServerDateTime().Year - 1, 12, 31)));
m_EmpPersonalFilter.add(new IN("EmpID", "SELECT EmpID FROM EmpRecurringPayment", m_rpFilter));
return(new IN("EmpID", "SELECT EmpID FROM EmpPersonalInfo", m_EmpPersonalFilter));
}
else if (pProcessName == "HitRateProcess")
{
// only staffs with Recurring Payment (where PaymentType == isHitRateBased)
DBFilter m_rpFilter = new DBFilter();
OR m_or = new OR();
m_or.add(new NullTerm("EmpRPEffTo"));
m_or.add(new Match("EmpRPEffTo", ">=", Utility.LastDateOfMonth(AppUtils.ServerDateTime())));
m_rpFilter.add(m_or);
m_rpFilter.add(new Match("EmpRPEffFr", "<=", Utility.LastDateOfMonth(AppUtils.ServerDateTime())));
DBFilter m_isHitRateBasedFilter = new DBFilter();
m_isHitRateBasedFilter.add(new Match("PaymentCodeIsHitRateBased", true));
m_rpFilter.add(new IN("PayCodeID", "SELECT PaymentCodeID FROM PaymentCode", m_isHitRateBasedFilter));
DBFilter m_EmpPersonalFilter = new DBFilter();
//m_EmpPersonalFilter.add(new Match("EmpStatus", "A"));
m_EmpPersonalFilter.add(new IN("EmpID", "SELECT EmpID FROM EmpRecurringPayment", m_rpFilter));
return(new IN("EmpID", "SELECT EmpID FROM EmpPersonalInfo", m_EmpPersonalFilter));
}
// Start 0000168, KuangWei, 2015-02-09
else if (pProcessName == "AttendancePreparationProcess")
{
// only staffs with Recurring Payment (where PaymentType == isHitRateBased)
EAttendancePreparationProcess m_process = EAttendancePreparationProcess.GetObject(dbConn, pProcessID);
DBFilter m_rpFilter = new DBFilter();
OR m_or = new OR();
m_or.add(new NullTerm("EmpRPEffTo"));
m_or.add(new Match("EmpRPEffTo", ">=", m_process.AttendancePreparationProcessPeriodTo));
m_rpFilter.add(m_or);
m_rpFilter.add(new Match("EmpRPEffFr", "<=", m_process.AttendancePreparationProcessPeriodFr));
DBFilter m_isRPWinsonFilter = new DBFilter();
m_rpFilter.add(new IN("EmpRPID", "SELECT EmpRPID FROM EmpRPWinson", m_isRPWinsonFilter));
DBFilter m_EmpPersonalFilter = new DBFilter();
//m_EmpPersonalFilter.add(new Match("EmpStatus", "A"));
m_EmpPersonalFilter.add(new IN("EmpID", "SELECT EmpID FROM EmpRecurringPayment", m_rpFilter));
return(new IN("EmpID", "SELECT EmpID FROM EmpPersonalInfo", m_EmpPersonalFilter));
}
// End 0000168, KuangWei, 2015-02-09
return(null);
}
public DataSet GenerateCND()
{
EBonusProcess m_process = EBonusProcess.GetObject(dbConn, m_BonusProcessID);
// EPaymentCode m_paymentCode = EPaymentCode.GetObject(dbConn, m_process.BonusProcessPayCodeID);
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("BonusProcessID", m_BonusProcessID));
m_detailFilter.add("EmpID", true);
m_detailFilter.add("EmpBonusProcessType", false); // Standard > Discretionary
double m_bonusAmount = 0;
double m_targetSalary = 0;
int m_currentEmpID = -1;
string m_payMethod = "";
string m_remarks = "";
string m_bankAccountCode = "";
string m_costCenterCode = "";
DataRow m_row = null;
EEmpPersonalInfo m_empInfo = null;
double m_proportion = 0; // the proportion of bonus recieved, depending on the join date
EBonusProcess m_bonusProcess = EBonusProcess.GetObject(dbConn, m_BonusProcessID);
foreach (EEmpBonusProcess m_detail in EEmpBonusProcess.db.select(dbConn, m_detailFilter))
{
if (m_detail.EmpID != m_currentEmpID)
{
if (m_currentEmpID > -1)
{
dataTable.Rows.Add(m_row);
}
m_currentEmpID = m_detail.EmpID;
m_row = dataTable.NewRow();
GetEmpRecurringPaymentInfo(m_detail.EmpID, m_process.BonusProcessSalaryMonth, out m_targetSalary, out m_payMethod, out m_bankAccountCode, out m_costCenterCode);
m_empInfo = EEmpPersonalInfo.GetObject(dbConn, m_detail.EmpID);
System.TimeSpan m_totalDaysInPeriod = m_bonusProcess.BonusProcessPeriodTo.Subtract(m_bonusProcess.BonusProcessPeriodFr);
System.TimeSpan m_totalDaysJoint = m_bonusProcess.BonusProcessPeriodTo.Subtract((m_empInfo.EmpDateOfJoin < m_bonusProcess.BonusProcessPeriodFr) ? m_bonusProcess.BonusProcessPeriodFr : m_empInfo.EmpDateOfJoin);
m_proportion = Math.Round(Convert.ToDouble(m_totalDaysJoint.Days + 1) / Convert.ToDouble(m_totalDaysInPeriod.Days + 1), 4);
m_remarks = "";
m_bonusAmount = 0;
//re-take targetSalary from RecurringPayment
//m_targetSalary = m_detail.EmpBonusProcessTargetSalary;
}
else // same EmpID
{
m_remarks += " + ";
}
if (m_detail.EmpBonusProcessType == "S")
{
m_bonusAmount = m_targetSalary * m_proportion * m_bonusProcess.BonusProcessStdRate;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessStdRate.ToString("0.0000") + ")";
}
else if (m_detail.EmpBonusProcessType == "D")
{
if (m_detail.EmpBonusProcessRank == "1")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank1 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank1.ToString("0.00") + "%)";
}
else if (m_detail.EmpBonusProcessRank == "2")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank2 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank2.ToString("0.00") + "%)";
}
else if (m_detail.EmpBonusProcessRank == "3")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank3 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank3.ToString("0.00") + "%)";
}
else if (m_detail.EmpBonusProcessRank == "4")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank4 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank4.ToString("0.00") + "%)";
}
else if (m_detail.EmpBonusProcessRank == "5")
{
m_bonusAmount = m_bonusAmount + m_targetSalary * m_proportion * m_process.BonusProcessRank5 / 100;
m_remarks = m_remarks + "(" + m_targetSalary.ToString("#,##0.00") + " * " + m_proportion.ToString("0.0000") + " * " + m_process.BonusProcessRank5.ToString("0.00") + "%)";
}
}
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_process.BonusProcessPayDate;
EPaymentCode m_payCodeObj = EPaymentCode.GetObject(dbConn, m_process.BonusProcessPayCodeID);
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_CODE] = m_payCodeObj.PaymentCode;
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_PAYMENT_METHOD] = m_payMethod;
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_AMOUNT] = Math.Round(m_bonusAmount, 2);
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_REMARK] = m_remarks;
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_BANK_ACCOUNT_NO] = m_bankAccountCode;
m_row[HROne.Import.ImportClaimsAndDeductionsProcess.FIELD_COST_CENTER] = m_costCenterCode;
}
if (m_currentEmpID > -1) // indicate there are CND records written into the table.
{
dataTable.Rows.Add(m_row);
}
return(dataSet);
}
public DataTable ExportDiscretionaryBonusTemplate(ArrayList pEmpList, bool IsIncludeCurrentPositionInfo)
{
DataTable tmpDataTable = new DataTable(TABLE_NAME_D);
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_EMP_NO, typeof(string));
if (IsIncludeCurrentPositionInfo)
{
ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable);
ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable);
}
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_TARGET_SALARY, typeof(double));
tmpDataTable.Columns.Add(HROne.Import.ImportBonusProcess.FIELD_RANK, typeof(string));
EBonusProcess m_bonusProcess = EBonusProcess.GetObject(dbConn, m_BonusProcessID);
if (m_bonusProcess != null)
{
foreach (EEmpPersonalInfo empInfo in pEmpList)
{
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
DataRow row = tmpDataTable.NewRow();
row[FIELD_EMP_NO] = empInfo.EmpNo;
if (IsIncludeCurrentPositionInfo)
{
ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID);
ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID);
}
// check if record already exists in EmpBonusProcess table
//DBFilter m_empBonusProcessFilter = new DBFilter();
//m_empBonusProcessFilter.add(new Match("EmpID", empInfo.EmpID));
//m_empBonusProcessFilter.add(new Match("BonusProecssID", m_bonusProcess.BonusProcessID ));
//m_empBonusProcessFilter.add(new Match("EmpID", empInfo.EmpID));
//m_empBonusProcessFilter.add(new Match("EmpBonusProcessType", "D"));
//ArrayList m_empBonusProcessList = EEmpBonusProcess.db.select(dbConn, m_empBonusProcessFilter);
//if (m_empBonusProcessList.Count > 0)
//{
// EEmpBonusProcess m_empBonusProcess = (EEmpBonusProcess) m_empBonusProcessList[0];
// row[FIELD_STD_RATE] = m_bonusProcess.BonusProcessStdRate;
// row[FIELD_TARGET_SALARY] = m_empBonusProcess.EmpBonusProcessTargetSalary;
// row[RANK] = m_empBonusProcess.EmpBonusProcessRank;
//}
//else
//{
EEmpRecurringPayment m_recurringPayment = GetSalaryMonthRecurringPayment(empInfo.EmpID, m_bonusProcess.BonusProcessSalaryMonth);
if (m_recurringPayment != null)
{
row[FIELD_TARGET_SALARY] = m_recurringPayment.EmpRPBasicSalary;
}
else
{
row[FIELD_TARGET_SALARY] = 0;
}
row[FIELD_RANK] = "";
//}
tmpDataTable.Rows.Add(row);
}
}
}
if (IsIncludeCurrentPositionInfo)
{
ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable);
}
return(tmpDataTable);
}