public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.* ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e ";
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
//DBFilter payPeriodFilter = new DBFilter();
//if (DecryptedRequest["PayGroupID"] != null)
// payPeriodFilter.add(new Match("PayGroupID", DecryptedRequest["PayGroupID"]));
//else
// payPeriodFilter.add(new Match("PayGroupID", 0));
filter.add(new IN("e.EmpID", "SELECT DISTINCT ep.EmpID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime().Date, AppUtils.ServerDateTime().Date);
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
if (table.Rows.Count != 0)
{
btnGenerate.Visible = true;
}
else
{
btnGenerate.Visible = false;
}
view = new DataView(table);
//if (info != null)
//{
// info.loadPageList(null, PrevPage, NextPage, FirstPage, LastPage);
// CurPage.Value = info.page.ToString();
// NumPage.Value = info.numPage.ToString();
//}
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(EEmpPayroll.db, Page.Master);
errors.clear();
Payroll_PeriodSelectionList1.SaveAsReleaseESSPaySlip();
errors.addError("ESS Pay Slip is updated.");
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
filter.add(info.orderby, info.order);
}
string select = "e.*";
string from = "from [" + db.dbclass.tableName + "] e ";
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm("ep"));
filter.add(new IN("e.EmpID", "SELECT DISTINCT ep.EmpID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
DBFilter payPeriodFilter = new SearchBinding(dbConn, EPayrollPeriod.db).createFilter();;
payPeriodFilter.add(new IN("pp.PayPeriodID", "SELECT DISTINCT ep.PayPeriodID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
string sqlStr = " min(pp.PayPeriodFr) as PayPeriodFr, max(pp.PayPeriodTo) as PayPeriodTo ";
string fromStr = " from [" + EPayrollPeriod.db.dbclass.tableName + "] pp ";
DataTable payPeriodTable = payPeriodFilter.loadData(dbConn, null, sqlStr, fromStr);
int rowCount = payPeriodTable.Rows.Count;
if (rowCount > 0 && !string.IsNullOrEmpty(payPeriodTable.Rows[0]["PayPeriodFr"].ToString()))
{
filter.add(new Match("e.EmpDateOfJoin", ">=", ((DateTime)payPeriodTable.Rows[0]["PayPeriodFr"]).ToString("yyyy/MM/dd")));
filter.add(new Match("e.EmpDateOfJoin", "<=", ((DateTime)payPeriodTable.Rows[0]["PayPeriodTo"]).ToString("yyyy/MM/dd")));
}
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
filter.add(info.orderby, info.order);
}
string select = "et.EmpTermID, e.*";
string from = "from [" + db.dbclass.tableName + "] e ";//LEFT JOIN " + EEmpPositionInfo.db.dbclass.tableName + " p ON c.EmpID=p.EmpID AND p.EmpPosEffTo IS NULL";
from += " inner join " + EEmpTermination.db.dbclass.tableName + " et on et.empid = e.empid ";
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
//DateTime dtLastEmploymentDateFrom, dtLastEmploymentDateTo;
//if (DateTime.TryParse(((WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("LastEmploymentDateFrom")).Value, out dtLastEmploymentDateFrom))
// filter.add(new Match("et.EmpTermLastDate", ">=", dtLastEmploymentDateFrom));
//if (DateTime.TryParse(((WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("LastEmploymentDateTo")).Value, out dtLastEmploymentDateTo))
// filter.add(new Match("et.EmpTermLastDate", "<=", dtLastEmploymentDateTo));
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
DBFilter lastEmploymentDateFilter = new DBFilter();
lastEmploymentDateFilter.add(new MatchField("et.EmpTermLastDate", "<=", "tmpPP.PayPeriodTo"));
empPayrollFilter.add(new IN("ep.PayPeriodID", "SELECT tmpPP.PayPeriodID FROM " + EPayrollPeriod.db.dbclass.tableName + " tmpPP", lastEmploymentDateFilter));
filter.add(new IN("e.EmpID", "SELECT DISTINCT ep.EmpID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
string select = "e.* ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e ";
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
filter.add(new IN("e.EmpID", "SELECT DISTINCT ep.EmpID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime().Date, AppUtils.ServerDateTime().Date);
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
if (table.Rows.Count != 0)
{
btnGenerate.Visible = true;
}
else
{
btnGenerate.Visible = false;
}
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.*, ep.*";
string from = "from [" + db.dbclass.tableName + "] ep, EmpPersonalInfo e ";
filter.add(new MatchField("e.EmpID", "ep.EmpID"));
filter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm("ep"));
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
if (table.Rows.Count > 0)
{
btnUndo.Visible = IsAllowEdit;
}
else
{
btnUndo.Visible = false;
}
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
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)
{
HROne.Reports.Payroll.PaymentSummaryListProcess reportProcess;
if (Payroll_PeriodSelectionList1.SelectedPayrollStatus.Equals("T"))
{
reportProcess = new HROne.Reports.Payroll.PaymentSummaryListProcess(dbConn, ci, list, HROne.Reports.Payroll.PaymentSummaryListProcess.ReportType.TrialRun, Payroll_PeriodSelectionList1.GetTrialRunPayPeriodList(), null);
}
else
{
reportProcess = new HROne.Reports.Payroll.PaymentSummaryListProcess(dbConn, ci, list, HROne.Reports.Payroll.PaymentSummaryListProcess.ReportType.History, null, Payroll_PeriodSelectionList1.GetPayBatchList());
}
if (Response.IsClientConnected)
{
HROneConfig config = HROneConfig.GetCurrentHROneConfig();
if (config.GenerateReportAsInbox)
{
if (EInboxAttachment.GetTotalSize(dbConn, 0) < WebUtils.productLicense(Session).MaxInboxSizeMB * 1000 * 1000)
{
HROne.TaskService.GenericExcelReportTaskFactory reportTask = new HROne.TaskService.GenericExcelReportTaskFactory(dbConn, user, lblReportHeader.Text, reportProcess, "PaymentList");
AppUtils.reportTaskQueueService.AddTask(reportTask);
errors.addError(HROne.Translation.PageMessage.REPORT_GENERATING_TO_INBOX);
}
else
{
errors.addError(HROne.Translation.PageMessage.INBOX_SIZE_EXCEEDED);
}
}
else
{
System.IO.FileInfo excelFile = reportProcess.GenerateExcelReport();
WebUtils.TransmitFile(Response, excelFile.FullName, "PaymentList_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
}
}
// const string PAYMENTCODE_PREFIX = "[Payment] ";
// string exportFileName = System.IO.Path.GetTempFileName();
// System.IO.File.Delete(exportFileName);
// exportFileName += ".xls";
// //System.IO.File.Copy(Server.MapPath("~/template/HistoryList_Template.xls"), exportFileName, true);
// HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
// DataSet dataSet = new 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 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, 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"));
// 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;
// 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"] = PayrollProcess.GetTotalWagesWithoutRestDayPayment(dbConn, empInfo.EmpID, payPeriod.PayPeriodFr, payPeriod.PayPeriodTo, null);
// row["Total Hours Worked"] = PayrollProcess.GetTotalEmpPayrollWorkingHours(dbConn, empInfo.EmpID, payPeriod.PayPeriodID);
// row["Min Wages Required"] = (double)row["Total Hours Worked"] * 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());
// DBFilter mpfRecordFilter = new DBFilter();
// mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilterForPaymentRecord));
// ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
// foreach (EMPFRecord mpfRecord in mpfRecords)
// {
// vcER += mpfRecord.MPFRecActVCER;
// mcER += +mpfRecord.MPFRecActMCER;
// vcEE += mpfRecord.MPFRecActVCEE;
// mcEE += mpfRecord.MPFRecActMCEE;
// }
// ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
// 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);
// export.Update(dataSet);
// WebUtils.TransmitFile(Response, exportFileName, "PaymentList_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
// return;
}
else
{
errors.addError("Employee not selected");
}
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
ArrayList empList = new ArrayList();
foreach (RepeaterItem i in Repeater.Items)
{
CheckBox cb = (CheckBox)i.FindControl("ItemSelect");
if (cb.Checked)
{
EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject();
WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb);
empList.Add(o);
}
}
if (empList.Count > 0)
{
//EPayrollPeriod payPeriod = new EPayrollPeriod();
//payPeriod.PayPeriodID = CurPayPeriodID;
//EPayrollPeriod.db.select(dbConn, payPeriod);
//string strEmpPayrollIDList = string.Empty;
//foreach (EEmpPayroll o in list)
//{
// if (strEmpPayrollIDList == string.Empty)
// strEmpPayrollIDList = ((EEmpPayroll)o).EmpPayrollID.ToString();
// else
// strEmpPayrollIDList += "_" + ((EEmpPayroll)o).EmpPayrollID.ToString();
//}
if (Payroll_PeriodSelectionList1.SelectedPayrollStatus.Equals("T"))
{
HROne.Reports.Payroll.PayrollDetailProcess rpt = new HROne.Reports.Payroll.PayrollDetailProcess(dbConn, empList, HROne.Reports.Payroll.PayrollDetailProcess.ReportType.TrialRun, Payroll_PeriodSelectionList1.GetTrialRunPayPeriodList(), null, null);
string reportFileName = WebUtils.GetLocalizedReportFile(Server.MapPath("~/Report_Payroll_TrialDetail.rpt"));
WebUtils.ReportExport(dbConn, user, errors, lblReportHeader.Text, Response, rpt, reportFileName, ((Button)sender).CommandArgument, "PayTrialRunDetail", true);
}
else
{
HROne.Reports.Payroll.PayrollDetailProcess rpt = new HROne.Reports.Payroll.PayrollDetailProcess(dbConn, empList, HROne.Reports.Payroll.PayrollDetailProcess.ReportType.History, null, Payroll_PeriodSelectionList1.GetPayBatchList(), null);
string reportFileName = WebUtils.GetLocalizedReportFile(Server.MapPath("~/Report_Payroll_HistoryDetail.rpt"));
WebUtils.ReportExport(dbConn, user, errors, lblReportHeader.Text, Response, rpt, reportFileName, ((Button)sender).CommandArgument, "PayHistoryDetail", true);
}
//Server.Transfer("Report_Payroll_TrialRunDetail_View.aspx?"
// + "PayPeriodID=" + DecryptedRequest["PayPeriodID"]
// + "&EmpPayrollID=" + strEmpPayrollIDList);
// errors.addError("Complete");
//Response.Write("<script>alert('Completed'); </script>");
}
else
{
errors.addError("Employee not selected");
}
}
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");
}
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(EEmpPayroll.db, Page.Master);
errors.clear();
ArrayList hLevelIDList = new ArrayList();
string hLevelIDListString = string.Empty;
int tmpHLevelID = 0;
if (int.TryParse(HLevel1.SelectedValue, out tmpHLevelID))
{
if (tmpHLevelID > 0)
{
hLevelIDList.Add(tmpHLevelID);
if (string.IsNullOrEmpty(hLevelIDListString))
{
hLevelIDListString = tmpHLevelID.ToString();
}
else
{
hLevelIDListString += "|" + tmpHLevelID.ToString();
}
}
}
if (int.TryParse(HLevel2.SelectedValue, out tmpHLevelID))
{
if (tmpHLevelID > 0)
{
hLevelIDList.Add(tmpHLevelID);
}
}
if (string.IsNullOrEmpty(hLevelIDListString))
{
hLevelIDListString = tmpHLevelID.ToString();
}
else
{
hLevelIDListString += "|" + tmpHLevelID.ToString();
}
DBTerm payrollPeriodTerm = Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm();
//OR orEmpIDTerms = null;
ArrayList empPayrollList = new ArrayList();
foreach (RepeaterItem i in Repeater.Items)
{
CheckBox cb = (CheckBox)i.FindControl("ItemSelect");
if (cb.Checked)
{
EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject();
WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb);
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(payrollPeriodTerm);
empPayrollFilter.add(new Match("EmpID", o.EmpID));
ArrayList list = EEmpPayroll.db.select(dbConn, empPayrollFilter);
if (list != null)
{
empPayrollList.AddRange(list);
}
//if (orEmpIDTerms == null)
// orEmpIDTerms = new OR();
//orEmpIDTerms.add(new Match("EmpID", o.EmpID));
}
}
if (empPayrollList.Count > 0)
{
//empPayrollFilter.add(orEmpIDTerms);
//ArrayList list = EEmpPayroll.db.select(dbConn, empPayrollFilter);
ESystemParameter.setParameter(dbConn, ESystemParameter.PARAM_CODE_PAYROLL_SUMMARY_HIERARCHY_DISPLAY_SEQUENCE, hLevelIDListString);
if (Payroll_PeriodSelectionList1.SelectedPayrollStatus.Equals("T"))
{
HROne.Reports.Payroll.PayrollSummaryProcess rpt = new HROne.Reports.Payroll.PayrollSummaryProcess(dbConn, empPayrollList, HROne.Reports.Payroll.PayrollSummaryProcess.ReportType.TrialRun, hLevelIDList);
string reportFileName = WebUtils.GetLocalizedReportFile(Server.MapPath("~/Report_Payroll_TrialRunSummary.rpt"));
WebUtils.ReportExport(dbConn, user, errors, lblReportHeader.Text, Response, rpt, reportFileName, ((Button)sender).CommandArgument, "PayTrialRunSummary", true);
}
else
{
HROne.Reports.Payroll.PayrollSummaryProcess rpt = new HROne.Reports.Payroll.PayrollSummaryProcess(dbConn, empPayrollList, HROne.Reports.Payroll.PayrollSummaryProcess.ReportType.History, hLevelIDList);
string reportFileName = WebUtils.GetLocalizedReportFile(Server.MapPath("~/Report_Payroll_HistorySummary.rpt"));
WebUtils.ReportExport(dbConn, user, errors, lblReportHeader.Text, Response, rpt, reportFileName, ((Button)sender).CommandArgument, "PayHistorySummary", true);
}
}
else
{
errors.addError("Employee not selected");
}
}