protected override System.Data.DataSet CreateDataSource()
{
System.Data.DataSet dataSet = new System.Data.DataSet();
DataTable empInfoTable = new DataTable("EmpInfo");
DataTable payrollTable = new DataTable("Payment");
DataTable hierarchyTable = new DataTable("hierarchy");
DataTable payPeriodTable = new DataTable("payPeriod");
dataSet.Tables.Add(empInfoTable);
dataSet.Tables.Add(payrollTable);
dataSet.Tables.Add(hierarchyTable);
dataSet.Tables.Add(payPeriodTable);
payPeriodTable.Columns.Add("payPeriodFr", typeof(DateTime));
hierarchyTable.Columns.Add("LevelDesc", typeof(string));
payrollTable.Columns.Add("EmpPayrollID", typeof(int));
payrollTable.Columns.Add("empID", typeof(int));
payrollTable.Columns.Add("payPeriodFr", typeof(DateTime));
payrollTable.Columns.Add("netAmount", typeof(double));
payrollTable.Columns.Add("EE", typeof(double));
payrollTable.Columns.Add("ER", typeof(double));
int firstDetailColumnPos = payrollTable.Columns.Count;
empInfoTable.Columns.Add("Employee No.", typeof(string));
empInfoTable.Columns.Add("EmpPayrollID", typeof(int));
empInfoTable.Columns.Add("Employee Name", typeof(string));
empInfoTable.Columns.Add("Alias", typeof(string));
empInfoTable.Columns.Add("Chinese Name", typeof(string));
empInfoTable.Columns.Add("Company", typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
if (hlevel.HLevelID.Equals(intHierarchyLevelID))
{
hierarchyLevelGroupingFieldName = hlevel.HLevelDesc;
}
empInfoTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
DataRow m_hierarchyRow = hierarchyTable.NewRow();
m_hierarchyRow["LevelDesc"] = hlevel.HLevelDesc;
hierarchyTable.Rows.Add(m_hierarchyRow);
}
empInfoTable.Columns.Add("EmpID", typeof(int));
empInfoTable.Columns.Add("Payroll Group", typeof(string));
empInfoTable.Columns.Add("Date Join", typeof(DateTime));
empInfoTable.Columns.Add("Date Left", typeof(DateTime));
empInfoTable.Columns.Add("Net Payable", typeof(double));
empInfoTable.Columns.Add("MCEE", typeof(double));
empInfoTable.Columns.Add("MCER", typeof(double));
empInfoTable.Columns.Add("VCEE", typeof(double));
empInfoTable.Columns.Add("VCER", typeof(double));
empInfoTable.Columns.Add("PFundEE", typeof(double));
empInfoTable.Columns.Add("PFundER", typeof(double));
DBFilter payPeriodFilter = new DBFilter();
if (!PeriodFrom.Ticks.Equals(0))
{
payPeriodFilter.add(new Match("PayPeriodTo", ">=", PeriodFrom));
}
if (!PeriodTo.Ticks.Equals(0))
{
payPeriodFilter.add(new Match("PayPeriodTo", "<=", PeriodTo));
}
ArrayList payPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
if (payPeriodList.Count > 0)
{
// reset period
PeriodFrom = new DateTime();
PeriodTo = new DateTime();
}
DBFilter m_userCompanyFilter = new DBFilter();
m_userCompanyFilter.add(new Match("UserID", currentUser.UserID));
DBFilter m_userRankFilter = new DBFilter();
m_userRankFilter.add(new Match("UserID", currentUser.UserID));
Hashtable m_userCompanyList = new Hashtable();
foreach (EUserCompany m_userCompany in EUserCompany.db.select(dbConn, m_userCompanyFilter))
{
m_userCompanyList.Add(m_userCompany.CompanyID, m_userCompany.CompanyID);
}
Hashtable m_userRankList = new Hashtable();
foreach (EUserRank m_userRank in EUserRank.db.select(dbConn, m_userRankFilter))
{
m_userRankList.Add(m_userRank.RankID, m_userRank.RankID);
}
int[] m_EmpIDList = new int[empList.Count];
int i = 0;
foreach (EEmpPersonalInfo m_info in empList)
{
m_EmpIDList[i] = m_info.EmpID;
i++;
}
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
DataRow[] m_payPeriodRows = payPeriodTable.Select("payPeriodFr = '" + ((DateTime)payPeriod.PayPeriodFr).ToString("yyyy-MM-dd") + "' ");
if (m_payPeriodRows.Length <= 0)
{
DataRow m_payPeriodRow = payPeriodTable.NewRow();
m_payPeriodRow["payPeriodFr"] = payPeriod.PayPeriodFr;
payPeriodTable.Rows.Add(m_payPeriodRow);
}
if (PeriodFrom > payPeriod.PayPeriodFr || PeriodFrom.Ticks.Equals(0))
{
PeriodFrom = payPeriod.PayPeriodFr;
}
if (PeriodTo < payPeriod.PayPeriodTo || PeriodTo.Ticks.Equals(0))
{
PeriodTo = payPeriod.PayPeriodTo;
}
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
empPayrollFilter.add(new IN("EmpID", m_EmpIDList));
ArrayList empPayrollList = EEmpPayroll.db.select(dbConn, empPayrollFilter);
foreach (EEmpPayroll empPayroll in empPayrollList)
{
DataRow paymentRow = payrollTable.NewRow();
DataRow[] m_existingEmpID = payrollTable.Select("EmpID = " + empPayroll.EmpID.ToString());
if (m_existingEmpID.Length == 0)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPayroll.EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo) == false)
{
continue;
}
DataRow row = empInfoTable.NewRow();
row["EmpID"] = empInfo.EmpID;
row["Employee No."] = empInfo.EmpNo;
row["EmpPayrollID"] = empPayroll.EmpPayrollID;
row["Employee Name"] = empInfo.EmpEngFullName;
row["Alias"] = empInfo.EmpAlias;
row["Chinese Name"] = empInfo.EmpChiFullName;
row["Date Join"] = empInfo.EmpDateOfJoin;
DBFilter empTerminationFilter = new DBFilter();
empTerminationFilter.add(new Match("EmpID", empInfo.EmpID));
ArrayList empTerminationList = EEmpTermination.db.select(dbConn, empTerminationFilter);
if (empTerminationList.Count > 0)
{
row["Date Left"] = ((EEmpTermination)empTerminationList[0]).EmpTermLastDate;
}
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
if (!m_userCompanyList.Contains(empPos.CompanyID) || !m_userRankList.Contains(empPos.RankID))
{
continue;
}
if (selectedCompanyID > 0 && empPos.CompanyID != selectedCompanyID)
{
continue;
}
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row["Company"] = company.CompanyName;
}
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = empPos.PayGroupID;
if (EPayrollGroup.db.select(dbConn, payrollGroup))
{
row["Payroll Group"] = payrollGroup.PayGroupDesc;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
}
empInfoTable.Rows.Add(row);
}
double netAmount = 0;
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
foreach (EPaymentRecord paymentRecord in EPaymentRecord.db.select(dbConn, paymentRecordFilter))
{
netAmount += paymentRecord.PayRecActAmount;
}
paymentRow["EmpID"] = empPayroll.EmpID;
paymentRow["EmpPayrollID"] = empPayroll.EmpPayrollID;
paymentRow["payPeriodFr"] = payPeriod.PayPeriodFr;
paymentRow["netAmount"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
double m_ee = 0;
double m_er = 0;
foreach (EMPFRecord mpfRecord in EMPFRecord.db.select(dbConn, mpfRecordFilter))
{
m_ee += (mpfRecord.MPFRecActVCEE + mpfRecord.MPFRecActMCEE);
m_er += (mpfRecord.MPFRecActVCER + mpfRecord.MPFRecActMCER);
}
ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
foreach (EORSORecord orsoRecord in orsoRecords)
{
m_ee += orsoRecord.ORSORecActEE;
m_er += orsoRecord.ORSORecActER;
}
paymentRow["EE"] = m_ee;
paymentRow["ER"] = m_er;
payrollTable.Rows.Add(paymentRow);
}
}
//DBFilter paymentCodeFilter = new DBFilter();
//paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
//paymentCodeFilter.add("PaymentCode", false);
//ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
//foreach (EPaymentCode paymentCode in paymentCodeList)
//{
// if (payrollTable.Columns.Contains(paymentCode.PaymentCodeDesc))
// payrollTable.Columns[paymentCode.PaymentCodeDesc].SetOrdinal(firstDetailColumnPos);
//}
return(dataSet);
}
protected override System.Data.DataSet CreateDataSource()
{
const string PAYMENTCODE_PREFIX = "[Payment] ";
System.Data.DataSet dataSet = new System.Data.DataSet(); //export.GetDataSet();
DataTable dataTable = new DataTable("Payroll$");
dataSet.Tables.Add(dataTable);
dataTable.Columns.Add("Company", typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
dataTable.Columns.Add("Payroll Group", typeof(string));
dataTable.Columns.Add("Position", typeof(string));
dataTable.Columns.Add("EmpNo", typeof(string));
dataTable.Columns.Add("English Name", typeof(string));
dataTable.Columns.Add("Chinese Name", typeof(string));
dataTable.Columns.Add("HKID", typeof(string));
dataTable.Columns.Add("From", typeof(DateTime));
dataTable.Columns.Add("To", typeof(DateTime));
int firstSummaryColumnPos = dataTable.Columns.Count;
dataTable.Columns.Add("Net Payment", typeof(double));
dataTable.Columns.Add("Relevant Income", typeof(double));
dataTable.Columns.Add("Non-Relevant Income", typeof(double));
dataTable.Columns.Add("Wages Payable for Min Wages", typeof(double));
dataTable.Columns.Add("Total Hours Worked", typeof(double));
dataTable.Columns.Add("Min Wages Required", typeof(double));
dataTable.Columns.Add("Employer Mandatory Contribution", typeof(double));
// dataTable.Columns.Add("Employee Mandatory Contribution", typeof(double));
dataTable.Columns.Add("Employer Voluntary Contribution", typeof(double));
// dataTable.Columns.Add("Employee Voluntary Contribution", typeof(double));
dataTable.Columns.Add("Employer P-Fund Contribution", typeof(double));
// dataTable.Columns.Add("Employee P-Fund Contribution", typeof(double));
dataTable.Columns.Add("Total Employer Contribution", typeof(double));
dataTable.Columns.Add("Total Employee Contribution", typeof(double));
dataTable.Columns.Add("Total Taxable Payment", typeof(double));
dataTable.Columns.Add("Total Non-Taxable Payment", typeof(double));
int firstDetailColumnPos = dataTable.Columns.Count;
foreach (EEmpPersonalInfo empInfo in EmpList)
{
EEmpPersonalInfo.db.select(dbConn, empInfo);
//DBFilter empPayrollFilterForPayrollPeriod = new DBFilter();
//empPayrollFilterForPayrollPeriod.add(new Match("ep.EmpID", empInfo.EmpID));
//empPayrollFilterForPayrollPeriod.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
//DBFilter payPeriodFilter = new DBFilter();
//payPeriodFilter.add(new IN("PayPeriodID", "SELECT PayPeriodID from " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilterForPayrollPeriod));
//ArrayList payPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
if (PayPeriodList == null)
{
OR orPayBatchIDTerms = new OR();
foreach (EPayrollBatch payBatch in PayBatchList)
{
orPayBatchIDTerms.add(new Match("ep.PayBatchID", payBatch.PayBatchID));
}
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(orPayBatchIDTerms);
DBFilter payPeriodFilter = new DBFilter();
payPeriodFilter.add(new IN(EPayrollPeriod.db.dbclass.tableName + ".PayPeriodID ", "SELECT DISTINCT PayPeriodID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
PayPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
}
foreach (EPayrollPeriod payPeriod in PayPeriodList)
{
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("EmpID", empInfo.EmpID));
if (reportType.Equals(ReportType.TrialRun))
{
empPayrollFilter.add(new Match("EmpPayStatus", "=", "T"));
}
else
{
empPayrollFilter.add(new Match("EmpPayStatus", "<>", "T"));
}
if (PayBatchList != null)
{
OR orPayrollBatch = new OR();
foreach (EPayrollBatch payrollBatch in PayBatchList)
{
orPayrollBatch.add(new Match("PayBatchID", payrollBatch.PayBatchID));
}
empPayrollFilter.add(orPayrollBatch);
}
empPayrollFilter.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
// Check if the EmpPayroll record for that payroll period exists
if (EEmpPayroll.db.count(dbConn, empPayrollFilter) > 0 && EPayrollPeriod.db.select(dbConn, payPeriod))
{
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payrollGroup);
DataRow row = dataTable.NewRow();
row["EmpNo"] = empInfo.EmpNo;
row["English Name"] = empInfo.EmpEngFullName;
row["Chinese Name"] = empInfo.EmpChiFullName;
row["HKID"] = empInfo.EmpHKID;
row["From"] = payPeriod.PayPeriodFr;
row["To"] = payPeriod.PayPeriodTo;
row["Payroll Group"] = payrollGroup.PayGroupDesc;
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row["Company"] = company.CompanyCode;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
row["Position"] = position.PositionDesc;
}
}
double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, taxableAmount = 0, nonTaxableAmount = 0;
double mcER = 0, mcEE = 0;
double vcER = 0, vcEE = 0;
double pFundER = 0, pFundEE = 0;
//DBFilter empPayrollFilterForPaymentRecord = new DBFilter(empPayrollFilterForPayrollPeriod);
//empPayrollFilterForPaymentRecord.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
//DBFilter paymentRecordFilter = new DBFilter();
//paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilterForPaymentRecord));
//paymentRecordFilter.add(new Match("PayRecStatus", "A"));
IN inEmpPayroll = new IN("EmpPayrollID", "Select ep.EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter);
DBFilter empPayrollFilterForPaymentRecord = new DBFilter();
empPayrollFilterForPaymentRecord.add(inEmpPayroll);
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, empPayrollFilterForPaymentRecord);
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode payCode = new EPaymentCode();
payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, payCode);
// Always Use Payment Code Description for grouping payment code with same description
string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc;
if (dataTable.Columns[fieldName] == null)
{
dataTable.Columns.Add(new DataColumn(fieldName, typeof(double)));
}
if (row[fieldName] == null || row[fieldName] == DBNull.Value)
{
row[fieldName] = 0;
}
row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount;
netAmount += paymentRecord.PayRecActAmount;
if (payCode.PaymentCodeIsMPF)
{
releventIncome += paymentRecord.PayRecActAmount;
}
else
{
nonRelevantIncome += paymentRecord.PayRecActAmount;
}
DBFilter taxPaymentMapFilter = new DBFilter();
taxPaymentMapFilter.add(new Match("PaymentCodeID", paymentRecord.PaymentCodeID));
if (ETaxPaymentMap.db.count(dbConn, taxPaymentMapFilter) > 0)
{
taxableAmount += paymentRecord.PayRecActAmount;
}
else
{
nonTaxableAmount += paymentRecord.PayRecActAmount;
}
}
row["Net Payment"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
row["Relevant Income"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(releventIncome, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
row["Non-Relevant Income"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(nonRelevantIncome, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
row["Wages Payable for Min Wages"] = HROne.Payroll.PayrollProcess.GetTotalWagesWithoutRestDayPayment(dbConn, empInfo.EmpID, payPeriod.PayPeriodFr, payPeriod.PayPeriodTo, null);
row["Total Hours Worked"] = HROne.Payroll.PayrollProcess.GetTotalEmpPayrollWorkingHours(dbConn, empInfo.EmpID, payPeriod.PayPeriodID);
row["Min Wages Required"] = (double)row["Total Hours Worked"] * HROne.Payroll.PayrollProcess.GetMinimumWages(dbConn, empInfo.EmpID, payPeriod.PayPeriodTo);
row["Total Taxable Payment"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(taxableAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
row["Total Non-Taxable Payment"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(nonTaxableAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, empPayrollFilterForPaymentRecord);
foreach (EMPFRecord mpfRecord in mpfRecords)
{
vcER += mpfRecord.MPFRecActVCER;
mcER += +mpfRecord.MPFRecActMCER;
vcEE += mpfRecord.MPFRecActVCEE;
mcEE += mpfRecord.MPFRecActMCEE;
}
ArrayList orsoRecords = EORSORecord.db.select(dbConn, empPayrollFilterForPaymentRecord);
foreach (EORSORecord orsoRecord in orsoRecords)
{
pFundER += orsoRecord.ORSORecActER;
pFundEE += orsoRecord.ORSORecActEE;
}
row["Employer Mandatory Contribution"] = mcER;
// row["Employee Mandatory Contribution"] = mcEE;
row["Employer Voluntary Contribution"] = vcER;
// row["Employee Voluntary Contribution"] = vcEE;
row["Employer P-Fund Contribution"] = pFundER;
// row["Employee P-Fund Contribution"] = pFundEE;
row["Total Employer Contribution"] = mcER + vcER + pFundER;
row["Total Employee Contribution"] = mcEE + vcEE + pFundEE;
dataTable.Rows.Add(row);
}
}
}
DBFilter paymentCodeFilter = new DBFilter();
paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
paymentCodeFilter.add("PaymentCode", false);
ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
foreach (EPaymentCode paymentCode in paymentCodeList)
{
if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc))
{
DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc];
paymentColumn.SetOrdinal(firstDetailColumnPos);
if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc))
{
paymentColumn.ColumnName = paymentCode.PaymentCodeDesc;
}
else
{
System.Diagnostics.Debug.Write("System reserved payment column is used");
}
}
}
for (int i = 0; i < firstDetailColumnPos; i++)
{
dataTable.Columns[i].ColumnName = HROne.Common.WebUtility.GetLocalizedString(dataTable.Columns[i].ColumnName);
}
for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++)
{
dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1);
}
return(dataSet);
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
ArrayList list = WebUtils.SelectedRepeaterItemToBaseObjectList(EEmpPersonalInfo.db, Repeater, "ItemSelect");
if (list.Count > 0)
{
const string PAYMENTCODE_PREFIX = "[Payment] ";
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable("Payroll$");
dataSet.Tables.Add(dataTable);
dataTable.Columns.Add("Company", typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
dataTable.Columns.Add("Payroll Group", typeof(string));
dataTable.Columns.Add("Position", typeof(string));
dataTable.Columns.Add("EmpNo", typeof(string));
dataTable.Columns.Add("English Name", typeof(string));
dataTable.Columns.Add("Chinese Name", typeof(string));
dataTable.Columns.Add("HKID", typeof(string));
dataTable.Columns.Add("From", typeof(DateTime));
dataTable.Columns.Add("To", typeof(DateTime));
int firstSummaryColumnPos = dataTable.Columns.Count;
dataTable.Columns.Add("Net Payment", typeof(double));
int firstDetailColumnPos = dataTable.Columns.Count;
foreach (EEmpPersonalInfo empInfo in list)
{
EEmpPersonalInfo.db.select(dbConn, empInfo);
DBFilter empPayrollFilterForPayrollPeriod = new DBFilter();
empPayrollFilterForPayrollPeriod.add(new Match("ep.EmpID", empInfo.EmpID));
empPayrollFilterForPayrollPeriod.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
DBFilter payPeriodFilter = new DBFilter();
payPeriodFilter.add(new IN("PayPeriodID", "SELECT PayPeriodID from " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilterForPayrollPeriod));
ArrayList payPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
if (EPayrollPeriod.db.select(dbConn, payPeriod))
{
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payrollGroup);
DataRow row = dataTable.NewRow();
row["EmpNo"] = empInfo.EmpNo;
row["English Name"] = empInfo.EmpEngFullName;
row["Chinese Name"] = empInfo.EmpChiFullName;
row["HKID"] = empInfo.EmpHKID;
row["From"] = payPeriod.PayPeriodFr;
row["To"] = payPeriod.PayPeriodTo;
row["Payroll Group"] = payrollGroup.PayGroupDesc;
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row["Company"] = company.CompanyCode;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
row["Position"] = position.PositionDesc;
}
}
double netAmount = 0;
DBFilter empPayrollFilterForPaymentRecord = new DBFilter(empPayrollFilterForPayrollPeriod);
empPayrollFilterForPaymentRecord.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilterForPaymentRecord));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode payCode = new EPaymentCode();
payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, payCode);
// Always Use Payment Code Description for grouping payment code with same description
string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc;
if (dataTable.Columns[fieldName] == null)
{
dataTable.Columns.Add(new DataColumn(fieldName, typeof(double)));
}
if (row[fieldName] == null || row[fieldName] == DBNull.Value)
{
row[fieldName] = 0;
}
row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount;
netAmount += paymentRecord.PayRecActAmount;
}
row["Net Payment"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
dataTable.Rows.Add(row);
}
}
}
DBFilter paymentCodeFilter = new DBFilter();
paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
paymentCodeFilter.add("PaymentCode", false);
ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
foreach (EPaymentCode paymentCode in paymentCodeList)
{
if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc))
{
DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc];
paymentColumn.SetOrdinal(firstDetailColumnPos);
if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc))
{
paymentColumn.ColumnName = paymentCode.PaymentCodeDesc;
}
else
{
System.Diagnostics.Debug.Write("System reserved payment column is used");
}
}
}
for (int i = 0; i < firstDetailColumnPos; i++)
{
dataTable.Columns[i].ColumnName = HROne.Common.WebUtility.GetLocalizedString(dataTable.Columns[i].ColumnName);
}
for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++)
{
dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1);
}
export.Update(dataSet);
WebUtils.TransmitFile(Response, exportFileName, "PaymentListWaiJi_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
return;
}
else
{
errors.addError("Employee not selected");
}
}
//private static double CalculateProrataFactor(int EmpID, EPayrollPeriod payrollPeriod)
//{
// EEmpPersonalInfo oEmp = new EEmpPersonalInfo();
// oEmp.EmpID = EmpID;
// EEmpPersonalInfo.db.select(dbConn, oEmp);
// DateTime dt1AgeMin = oEmp.EmpDateOfBirth.AddYears(AGE_MINIMUM);
// DateTime dt1AgeMax = oEmp.EmpDateOfBirth.AddYears(AGE_MAXIMUM);
// EEmpTermination empTermination = EEmpTermination.GetObjectByEmpID(dbConn, EmpID);
// bool blnTerminated;
// if (empTermination != null)
// {
// if (empTermination.EmpTermLastDate <= payrollPeriod.PayPeriodTo && empTermination.EmpTermLastDate >= payrollPeriod.PayPeriodFr)
// blnTerminated = true;
// else
// blnTerminated = false;
// }
// else
// blnTerminated = false;
// double prorataFactor = 1;
// if (oEmp.EmpDateOfJoin < dt1AgeMin)
// {
// if (dt1AgeMin <= payrollPeriod.PayPeriodTo && dt1AgeMin >= payrollPeriod.PayPeriodFr)
// {
// if (blnTerminated)
// prorataFactor = (double)(empTermination.EmpTermLastDate.Subtract(dt1AgeMin).Days + 1) / (empTermination.EmpTermLastDate.Subtract(payrollPeriod.PayPeriodFr > oEmp.EmpDateOfJoin ? payrollPeriod.PayPeriodFr : oEmp.EmpDateOfJoin).Days + 1);
// else
// prorataFactor = (double)(payrollPeriod.PayPeriodTo.Subtract(dt1AgeMin).Days + 1) / (payrollPeriod.PayPeriodTo.Subtract(payrollPeriod.PayPeriodFr > oEmp.EmpDateOfJoin ? payrollPeriod.PayPeriodFr : oEmp.EmpDateOfJoin).Days + 1);
// }
// if (dt1AgeMin >= payrollPeriod.PayPeriodTo)
// prorataFactor = 0;
// }
// if (dt1AgeMax <= payrollPeriod.PayPeriodTo && dt1AgeMax >= payrollPeriod.PayPeriodFr)
// {
// if (blnTerminated)
// {
// if (empTermination.EmpTermLastDate < dt1AgeMax)
// prorataFactor = 1;
// else
// prorataFactor = 1.0 - (double)(empTermination.EmpTermLastDate.Subtract(dt1AgeMax).Days + 1) / (empTermination.EmpTermLastDate.Subtract(payrollPeriod.PayPeriodFr > oEmp.EmpDateOfJoin ? payrollPeriod.PayPeriodFr : oEmp.EmpDateOfJoin).Days + 1);
// }
// else
// prorataFactor = 1.0 - (double)(payrollPeriod.PayPeriodTo.Subtract(dt1AgeMax).Days + 1) / (payrollPeriod.PayPeriodTo.Subtract(payrollPeriod.PayPeriodFr > oEmp.EmpDateOfJoin ? payrollPeriod.PayPeriodFr : oEmp.EmpDateOfJoin).Days + 1);
// }
// if (dt1AgeMax <= payrollPeriod.PayPeriodFr)
// prorataFactor = 0;
// return prorataFactor;
//}
private static EORSORecord CreateORSORecord(DatabaseConnection dbConn, int EmpID, EPayrollPeriod payrollPeriod, ArrayList paymentRecords, DateTime ORSOJoinDate, ORSOJoinType ORSOJoinType, EMPFRecord mpfRecord)
{
//double RIProrateFactor = CalculateProrataFactor(EmpID, payrollPeriod);
EORSORecord orsoRecord = new EORSORecord();
switch (ORSOJoinType)
{
case ORSOJoinType.NewJoin:
orsoRecord.ORSORecType = "N";
break;
case ORSOJoinType.Existing:
orsoRecord.ORSORecType = "E";
break;
//case ORSOJoinType.Terminated:
// ORSORecord.ORSORecType = "T";
//break;
}
orsoRecord.ORSORecCalRI = 0;
orsoRecord.ORSOPlanID = GetORSOPlanID(dbConn, EmpID, payrollPeriod.PayPeriodTo);
if (orsoRecord.ORSOPlanID > 0)
{
EEmpPersonalInfo oEmp = new EEmpPersonalInfo();
oEmp.EmpID = EmpID;
EEmpPersonalInfo.db.select(dbConn, oEmp);
//DateTime dt1Age18 = oEmp.EmpDateOfBirth.AddYears(18);
//DateTime dt1Age65 = oEmp.EmpDateOfBirth.AddYears(65);
EEmpTermination empTermination = EEmpTermination.GetObjectByEmpID(dbConn, EmpID);
orsoRecord.ORSORecPeriodFr = payrollPeriod.PayPeriodFr;
orsoRecord.ORSORecPeriodTo = payrollPeriod.PayPeriodTo;
if (orsoRecord.ORSORecPeriodFr > oEmp.EmpDateOfJoin && orsoRecord.ORSORecPeriodTo < oEmp.EmpDateOfJoin)
{
orsoRecord.ORSORecPeriodFr = oEmp.EmpDateOfJoin;
}
//if (ORSORecord.ORSORecPeriodFr > dt1Age18 && ORSORecord.ORSORecPeriodTo < dt1Age18)
// ORSORecord.ORSORecPeriodFr = dt1Age18;
if (empTermination != null)
{
// Start 0000186, Ricky So, 2016/04/15
// incomplete month --> no orso contribution
if (orsoRecord.ORSORecPeriodTo > empTermination.EmpTermLastDate && orsoRecord.ORSORecPeriodFr <= empTermination.EmpTermLastDate)
{
orsoRecord.ORSORecPeriodTo = empTermination.EmpTermLastDate;
orsoRecord.ORSORecActEE = 0;
orsoRecord.ORSORecActER = 0;
orsoRecord.ORSORecActRI = 0;
orsoRecord.ORSORecCalEE = 0;
orsoRecord.ORSORecCalER = 0;
orsoRecord.ORSORecCalRI = 0;
return(orsoRecord);
}
// End 0000186, Ricky So, 2016/04/15
if (orsoRecord.ORSORecPeriodTo < empTermination.EmpTermLastDate && orsoRecord.ORSORecPeriodFr >= empTermination.EmpTermLastDate)
{
orsoRecord.ORSORecPeriodTo = empTermination.EmpTermLastDate;
}
}
//if (ORSORecord.ORSORecPeriodTo < dt1Age65 && ORSORecord.ORSORecPeriodFr > dt1Age65)
// ORSORecord.ORSORecPeriodTo = dt1Age65;
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode paymentCode = new EPaymentCode();
paymentCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, paymentCode);
if (paymentCode.PaymentCodeIsORSO)
{
orsoRecord.ORSORecCalRI += paymentRecord.PayRecActAmount;
}
}
orsoRecord.ORSORecActRI = orsoRecord.ORSORecCalRI;
if (orsoRecord.ORSOPlanID > 0)
{
DBFilter oldORSORecordFilter = new DBFilter();
DBFilter empIDFilter = new DBFilter();
empIDFilter.add(new Match("EmpID", EmpID));
oldORSORecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from EMPPayroll ", empIDFilter));
oldORSORecordFilter.add(new Match("ORSORecPeriodFr", "<=", orsoRecord.ORSORecPeriodTo));
oldORSORecordFilter.add(new Match("ORSORecPeriodTo", ">=", orsoRecord.ORSORecPeriodFr));
ArrayList oldORSORecords = EORSORecord.db.select(dbConn, oldORSORecordFilter);
EORSORecord oldTotalORSORecord = new EORSORecord();
EORSORecord newTotalORSORecord = new EORSORecord();
foreach (EORSORecord oldORSORecord in oldORSORecords)
{
oldTotalORSORecord.ORSORecActRI += oldORSORecord.ORSORecActRI;
oldTotalORSORecord.ORSORecActER += oldORSORecord.ORSORecActER;
oldTotalORSORecord.ORSORecActEE += oldORSORecord.ORSORecActEE;
}
newTotalORSORecord.ORSORecCalRI = oldTotalORSORecord.ORSORecActRI + orsoRecord.ORSORecCalRI;
newTotalORSORecord.ORSORecCalER = CalculateERAmount(dbConn, oEmp, orsoRecord.ORSOPlanID, newTotalORSORecord.ORSORecCalRI, payrollPeriod, mpfRecord);
newTotalORSORecord.ORSORecCalEE = CalculateEEAmount(dbConn, oEmp, orsoRecord.ORSOPlanID, newTotalORSORecord.ORSORecCalRI, payrollPeriod, ORSOJoinDate, mpfRecord);
orsoRecord.ORSORecCalER = newTotalORSORecord.ORSORecCalER - oldTotalORSORecord.ORSORecActER;
orsoRecord.ORSORecCalEE = newTotalORSORecord.ORSORecCalEE - oldTotalORSORecord.ORSORecActEE;
orsoRecord.ORSORecActER = Math.Round(orsoRecord.ORSORecCalER, 2, MidpointRounding.AwayFromZero);
orsoRecord.ORSORecActEE = Math.Round(orsoRecord.ORSORecCalEE, 2, MidpointRounding.AwayFromZero);
EORSOPlan orsoPlan = new EORSOPlan();
orsoPlan.ORSOPlanID = orsoRecord.ORSOPlanID;
if (EORSOPlan.db.select(dbConn, orsoPlan))
{
if (string.IsNullOrEmpty(orsoPlan.ORSOPlanEmployerRoundingRule))
{
orsoPlan.ORSOPlanEmployerRoundingRule = Values.ROUNDING_RULE_ROUND_TO;
orsoPlan.ORSOPlanEmployerDecimalPlace = 2;
}
if (orsoPlan.ORSOPlanEmployerRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_TO))
{
orsoRecord.ORSORecActER = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(orsoRecord.ORSORecCalER, orsoPlan.ORSOPlanEmployerDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces());
}
else if (orsoPlan.ORSOPlanEmployerRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_UP))
{
orsoRecord.ORSORecActER = HROne.CommonLib.GenericRoundingFunctions.RoundingUp(orsoRecord.ORSORecCalER, orsoPlan.ORSOPlanEmployerDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces());
}
else if (orsoPlan.ORSOPlanEmployerRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_DOWN))
{
orsoRecord.ORSORecActER = HROne.CommonLib.GenericRoundingFunctions.RoundingDown(orsoRecord.ORSORecCalER, orsoPlan.ORSOPlanEmployerDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces());
}
if (string.IsNullOrEmpty(orsoPlan.ORSOPlanEmployeeRoundingRule))
{
orsoPlan.ORSOPlanEmployeeRoundingRule = Values.ROUNDING_RULE_ROUND_TO;
orsoPlan.ORSOPlanEmployeeDecimalPlace = 2;
}
if (orsoPlan.ORSOPlanEmployeeRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_TO))
{
orsoRecord.ORSORecActEE = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(orsoRecord.ORSORecCalEE, orsoPlan.ORSOPlanEmployeeDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces());
}
else if (orsoPlan.ORSOPlanEmployeeRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_UP))
{
orsoRecord.ORSORecActEE = HROne.CommonLib.GenericRoundingFunctions.RoundingUp(orsoRecord.ORSORecCalEE, orsoPlan.ORSOPlanEmployeeDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces());
}
else if (orsoPlan.ORSOPlanEmployeeRoundingRule.Equals(Values.ROUNDING_RULE_ROUND_DOWN))
{
orsoRecord.ORSORecActEE = HROne.CommonLib.GenericRoundingFunctions.RoundingDown(orsoRecord.ORSORecCalEE, orsoPlan.ORSOPlanEmployeeDecimalPlace, ExchangeCurrency.DefaultCurrencyDecimalPlaces());
}
}
}
return(orsoRecord);
}
else
{
return(null);
}
}
protected override System.Data.DataSet CreateDataSource()
{
System.Data.DataSet dataSet = new System.Data.DataSet();
DataTable empInfoTable = new DataTable("EmpInfo");
DataTable payrollTable = new DataTable("Payment");
dataSet.Tables.Add(empInfoTable);
dataSet.Tables.Add(payrollTable);
payrollTable.Columns.Add("EmpPayrollID", typeof(string));
int firstDetailColumnPos = payrollTable.Columns.Count;
empInfoTable.Columns.Add("Employee No.", typeof(string));
empInfoTable.Columns.Add("EmpPayrollID", typeof(int));
empInfoTable.Columns.Add("Employee Name", typeof(string));
empInfoTable.Columns.Add("Alias", typeof(string));
empInfoTable.Columns.Add("Company", typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
//***** Start 2013/11/22, Ricky So, Special handle display by staff level (not in hierarchy setting)
if (intHierarchyLevelID == STAFF_LEVEL_ID)
{
hierarchyLevelGroupingFieldName = "Employee Name";
}
//***** End 2013/11/22, Ricky So, Special handle display by staff level (not in hierarchy setting)
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
if (hlevel.HLevelID.Equals(intHierarchyLevelID))
{
hierarchyLevelGroupingFieldName = hlevel.HLevelDesc;
}
empInfoTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
empInfoTable.Columns.Add("Payroll Group", typeof(string));
empInfoTable.Columns.Add("Date Join", typeof(DateTime));
empInfoTable.Columns.Add("Date Left", typeof(DateTime));
empInfoTable.Columns.Add("Net Payable", typeof(double));
empInfoTable.Columns.Add("MCEE", typeof(double));
empInfoTable.Columns.Add("MCER", typeof(double));
empInfoTable.Columns.Add("VCEE", typeof(double));
empInfoTable.Columns.Add("VCER", typeof(double));
empInfoTable.Columns.Add("PFundEE", typeof(double));
empInfoTable.Columns.Add("PFundER", typeof(double));
DBFilter payPeriodFilter = new DBFilter();
if (!PeriodFrom.Ticks.Equals(0))
{
payPeriodFilter.add(new Match("PayPeriodTo", ">=", PeriodFrom));
}
if (!PeriodTo.Ticks.Equals(0))
{
payPeriodFilter.add(new Match("PayPeriodTo", "<=", PeriodTo));
}
ArrayList payPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
if (payPeriodList.Count > 0)
{
// reset period
PeriodFrom = new DateTime();
PeriodTo = new DateTime();
}
DBFilter m_userCompanyFilter = new DBFilter();
m_userCompanyFilter.add(new Match("UserID", currentUser.UserID));
DBFilter m_userRankFilter = new DBFilter();
m_userRankFilter.add(new Match("UserID", currentUser.UserID));
Hashtable m_userCompanyList = new Hashtable();
foreach (EUserCompany m_userCompany in EUserCompany.db.select(dbConn, m_userCompanyFilter))
{
m_userCompanyList.Add(m_userCompany.CompanyID, m_userCompany.CompanyID);
}
Hashtable m_userRankList = new Hashtable();
foreach (EUserRank m_userRank in EUserRank.db.select(dbConn, m_userRankFilter))
{
m_userRankList.Add(m_userRank.RankID, m_userRank.RankID);
}
int[] m_EmpIDList = new int[empList.Count];
int i = 0;
foreach (EEmpPersonalInfo m_info in empList)
{
m_EmpIDList[i] = m_info.EmpID;
i++;
}
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
if (PeriodFrom > payPeriod.PayPeriodFr || PeriodFrom.Ticks.Equals(0))
{
PeriodFrom = payPeriod.PayPeriodFr;
}
if (PeriodTo < payPeriod.PayPeriodTo || PeriodTo.Ticks.Equals(0))
{
PeriodTo = payPeriod.PayPeriodTo;
}
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
empPayrollFilter.add(new IN("EmpID", m_EmpIDList));
ArrayList empPayrollList = EEmpPayroll.db.select(dbConn, empPayrollFilter);
foreach (EEmpPayroll empPayroll in empPayrollList)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPayroll.EmpID;
EEmpPersonalInfo.db.select(dbConn, empInfo);
DataRow row = empInfoTable.NewRow();
DataRow paymentRow = payrollTable.NewRow();
row["Employee No."] = empInfo.EmpNo;
row["EmpPayrollID"] = empPayroll.EmpPayrollID;
paymentRow["EmpPayrollID"] = empPayroll.EmpPayrollID;
row["Employee Name"] = empInfo.EmpEngFullName;
row["Alias"] = empInfo.EmpAlias;
row["Date Join"] = empInfo.EmpDateOfJoin;
DBFilter empTerminationFilter = new DBFilter();
empTerminationFilter.add(new Match("EmpID", empInfo.EmpID));
ArrayList empTerminationList = EEmpTermination.db.select(dbConn, empTerminationFilter);
if (empTerminationList.Count > 0)
{
row["Date Left"] = ((EEmpTermination)empTerminationList[0]).EmpTermLastDate;
}
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
if (!m_userCompanyList.Contains(empPos.CompanyID) || !m_userRankList.Contains(empPos.RankID))
{
continue;
}
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row["Company"] = company.CompanyName;
}
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = empPos.PayGroupID;
if (EPayrollGroup.db.select(dbConn, payrollGroup))
{
row["Payroll Group"] = payrollGroup.PayGroupDesc;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
}
double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, MCEE = 0, MCER = 0, VCEE = 0, VCER = 0, PFUNDEE = 0, PFUNDER = 0;
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode payCode = new EPaymentCode();
payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, payCode);
string fieldName = payCode.PaymentCodeDesc;
if (payrollTable.Columns[fieldName] == null)
{
payrollTable.Columns.Add(new DataColumn(fieldName, typeof(double)));
}
if (paymentRow[fieldName] == null || paymentRow[fieldName] == DBNull.Value)
{
paymentRow[fieldName] = 0;
}
paymentRow[fieldName] = (double)paymentRow[fieldName] + paymentRecord.PayRecActAmount;
netAmount += paymentRecord.PayRecActAmount;
if (payCode.PaymentCodeIsMPF)
{
releventIncome += paymentRecord.PayRecActAmount;
}
else
{
nonRelevantIncome += paymentRecord.PayRecActAmount;
}
}
row["Net Payable"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
//row["Relevant Income"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(releventIncome, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
//row["Non-Relevant Income"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(nonRelevantIncome, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
foreach (EMPFRecord mpfRecord in mpfRecords)
{
VCER += mpfRecord.MPFRecActVCER;
MCER += mpfRecord.MPFRecActMCER;
VCEE += mpfRecord.MPFRecActVCEE;
MCEE += mpfRecord.MPFRecActMCEE;
}
row["MCEE"] = MCEE;
row["VCEE"] = VCEE;
row["MCER"] = MCER;
row["VCER"] = VCER;
ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
foreach (EORSORecord orsoRecord in orsoRecords)
{
PFUNDER += orsoRecord.ORSORecActER;
PFUNDEE += orsoRecord.ORSORecActEE;
}
row["PFundEE"] = PFUNDEE;
row["PFundER"] = PFUNDER;
empInfoTable.Rows.Add(row);
payrollTable.Rows.Add(paymentRow);
}
}
DBFilter paymentCodeFilter = new DBFilter();
paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
paymentCodeFilter.add("PaymentCode", false);
ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
foreach (EPaymentCode paymentCode in paymentCodeList)
{
if (payrollTable.Columns.Contains(paymentCode.PaymentCodeDesc))
{
payrollTable.Columns[paymentCode.PaymentCodeDesc].SetOrdinal(firstDetailColumnPos);
}
}
return(dataSet);
}