protected void btnExport_Click(object sender, EventArgs e) { ArrayList empList = WebUtils.SelectedRepeaterItemToBaseObjectList(EEmpPersonalInfo.db, Repeater, "ItemSelect"); if (empList.Count > 0) { 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(); dataSet.Tables.Add(HROne.Import.ImportClaimsAndDeductionsProcess.ExportTemplate(dbConn, empList, true)); export.Update(dataSet); //WebUtils.RegisterDownloadFileJavaScript(this, exportFileName, "ClaimsAndDeductionsTemplate_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true, 0); WebUtils.TransmitFile(Response, exportFileName, "ClaimsAndDeductionsTemplate_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; } else { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.addError(HROne.Translation.PageErrorMessage.ERROR_NO_EMPLOYEE_SELECTED); } }
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"); } }
protected void btnExport_Click(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); DateTime m_paymentDate = new DateTime(); if (PaymentDate.Value == "" || !(DateTime.TryParse(PaymentDate.Value, out m_paymentDate))) { errors.addError("Please provide a Payment Date"); return; } int m_batchID = -1; if (!int.TryParse(BatchID.SelectedValue, out m_batchID)) { errors.addError("Please select a Import Batch"); return; } DataSet dataSet = GenerateCND(m_batchID, m_paymentDate); 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); }
protected void btnExport_Click(object sender, EventArgs e) { DataTable tmpDataTable = new DataTable(TABLE_NAME); tmpDataTable.Columns.Add("Date", typeof(DateTime)); tmpDataTable.Columns.Add("Description", typeof(string)); DBFilter filter = sbinding.createFilter(); filter.add("PublicHolidayDate", true); ArrayList holidayList = EPublicHoliday.db.select(dbConn, filter); foreach (EPublicHoliday statHol in holidayList) { DataRow row = tmpDataTable.NewRow(); row["Date"] = statHol.PublicHolidayDate; row["Description"] = statHol.PublicHolidayDesc; tmpDataTable.Rows.Add(row); } string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; HROne.Export.ExcelExport excelExport = new HROne.Export.ExcelExport(exportFileName); excelExport.Update(tmpDataTable); WebUtils.TransmitFile(Response, exportFileName, "PublicHoliday_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; }
protected void btnExport_Click(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); //Hashtable values = new Hashtable(); //binding.toValues(values); string m_schemeCode = ""; string m_paymentCode = ""; int m_paymentCodeID = -1; DateTime m_paymentDate = new DateTime(); EPaymentCode m_paymentCodeObj; if (SchemeCode.SelectedIndex > 0) { m_schemeCode = SchemeCode.SelectedValue; } if (BackpayDate.Value != "") { m_paymentDate = DateTime.Parse(BackpayDate.Value); } else { errors.addError("Please provide a Backpay Payment Date"); return; } if (PaymentCode.SelectedIndex > 0) { m_paymentCode = PaymentCode.SelectedValue; m_paymentCodeObj = EPaymentCode.GetObject(dbConn, m_paymentCode.Substring(0, m_paymentCode.IndexOf("-")).Trim()); if (m_paymentCodeObj == null) { errors.addError("Cannot resolve Backpay Payment Code"); return; } } else { errors.addError("Pelase select a Backpay Payment Code"); return; } DataSet dataSet = GenerateBackpayTemplate(m_schemeCode, m_paymentCodeObj.PaymentCodeID, m_paymentDate); 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, "BackpayCND_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); }
protected void btnExport_Click(object sender, EventArgs e) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //tmpDataTable.Columns.Add("ID", typeof(Int32)); tmpDataTable.Columns.Add("Effective Date", typeof(DateTime)); tmpDataTable.Columns.Add("Expiry Date", typeof(DateTime)); tmpDataTable.Columns.Add("Scheme Code", typeof(String)); tmpDataTable.Columns.Add("Point", typeof(Decimal)); tmpDataTable.Columns.Add("Salary", typeof(Decimal)); DBFilter filter = new DBFilter(); // sbinding.createFilter(); OR m_OR = new OR(); DateTime m_asAtDate = new DateTime(); if (SchemeSelect.SelectedIndex > 0) { filter.add(new Match("SchemeCode", AppUtils.Encode(EPayScale.db.getField("SchemeCode"), SchemeSelect.SelectedValue.ToString()))); } if (DateTime.TryParse(AsAtDate.TextBox.Text, out m_asAtDate)) { filter.add(new Match("EffectiveDate", "<=", m_asAtDate)); m_OR.add(new Match("ExpiryDate", ">=", m_asAtDate)); m_OR.add(new NullTerm("ExpiryDate")); filter.add(m_OR); } filter.add("Point", false); ArrayList list = EPayScaleMap.db.select(dbConn, filter); foreach (EPayScaleMap c in list) { DataRow row = tmpDataTable.NewRow(); //row["ID"] = c.PayScaleID; row["Effective Date"] = c.EffectiveDate; row["Expiry Date"] = c.ExpiryDate; row["Scheme Code"] = c.SchemeCode.ToString(); row["Point"] = c.Point; row["Salary"] = c.Salary; tmpDataTable.Rows.Add(row); } string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; HROne.Export.ExcelExport excelExport = new HROne.Export.ExcelExport(exportFileName); excelExport.Update(tmpDataTable); WebUtils.TransmitFile(Response, exportFileName, "PayScalePoints_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; }
protected void btnExportTemplate_Click(object sender, EventArgs e) { 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); export.Update(ImportLeaveBalanceAdjustmentProcess.Export(dbConn, new ArrayList(), false, false, false, new DateTime())); WebUtils.TransmitFile(Response, exportFileName, "LeaveBalanceAdjustmentTemplate_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; }
protected void btnGenerateCND_Click(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); // string m_schemeCode = ""; string m_paymentCode = ""; int m_paymentCodeID = -1; DateTime m_paymentDate = new DateTime(); EPaymentCode m_paymentCodeObj; if (PaymentDate.Text != "") { m_paymentDate = DateTime.Parse(PaymentDate.Text); } else { errors.addError("Please provide a Backpay Payment Date"); return; } if (PaymentCode.Text != "") { m_paymentCode = PaymentCode.Text; m_paymentCodeObj = EPaymentCode.GetObject(dbConn, PaymentCode.Text); if (m_paymentCodeObj == null) { errors.addError("Cannot resolve Backpay Payment Code"); return; } } else { errors.addError("Pelase select a Backpay Payment Code"); return; } DataSet dataSet = GenerateCND(CurID, m_paymentCodeObj.PaymentCodeID, m_paymentDate); 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, "BackpayCND_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); }
private void GenerateAttendanceRecordData(ArrayList EmpInfoList, DateTime PeriodFrom, DateTime PeriodTo) { 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(); dataSet.Tables.Add(HROne.Import.ImportEmpWorkingSummaryProcess.GenerateTemplate(dbConn, EmpInfoList, PeriodFrom, PeriodTo)); export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, "WorkingSummaryTemplate_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; }
protected void btnExportPart1Template_Click(object sender, EventArgs e) { 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();// export.GetDataSet(); HROne.Import.ImportBonusProcess m_importProcess = new HROne.Import.ImportBonusProcess(dbConn, Session.SessionID, WebUtils.GetCurUser(Session).UserID, CurID); DataTable m_table = m_importProcess.ExportStandardBonusTemplate(true); if (m_table != null) { dataSet.Tables.Add(m_table); export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, m_table.TableName + "_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); } }
protected void btnGenerateCND_Click(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); EAttendancePreparationProcess m_process = EAttendancePreparationProcess.GetObject(dbConn, CurID); if (m_process != null) { DBFilter m_rpFilter = new DBFilter(); OR m_or = new OR(); m_or.add(new NullTerm("EmpRPEffTo")); m_or.add(new Match("EmpRPEffTo", ">=", HROne.CommonLib.Utility.LastDateOfMonth(AppUtils.ServerDateTime()))); m_rpFilter.add(m_or); m_rpFilter.add(new Match("EmpRPEffFr", "<=", HROne.CommonLib.Utility.LastDateOfMonth(AppUtils.ServerDateTime()))); 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 IN("EmpID", "SELECT EmpID FROM EmpRecurringPayment", m_rpFilter)); ArrayList empList = EEmpPersonalInfo.db.select(dbConn, m_EmpPersonalFilter); HROne.Import.ImportAttendancePreparationProcess m_import = new HROne.Import.ImportAttendancePreparationProcess(dbConn, Session.SessionID, WebUtils.GetCurUser(Session).UserID, CurID); string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName); DataSet dataSet = m_import.GenerateCND(dbConn, empList, exportFileName, CurID); export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, "CND_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); } else { errors.addError("Failed to open batch"); } }
protected void btnExport_Click(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); if (errors.isEmpty()) { 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();// export.GetDataSet(); dataSet.Tables.Add(ExportBatchDetail(dbConn, CurID).Copy()); export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, "SalaryIncrement_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); } return; }
protected void btnExport_Click(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); ArrayList empList = WebUtils.SelectedRepeaterItemToBaseObjectList(EEmpPersonalInfo.db, Repeater, "ItemSelect"); if (empList.Count > 0) { 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();// export.GetDataSet(); // Start 0000168, KuangWei, 2015-02-11 if (string.Compare(gProcessName, "AttendancePreparationProcess", true) == 0) { HROne.Import.ImportAttendancePreparationProcess.ExportTemplate(dbConn, empList, exportFileName, gPID); WebUtils.TransmitFile(Response, exportFileName, "AttendanceRecordDataEntry_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); } else { DataTable m_table = PrepareExportData(gProcessName, gPID, empList, errors); if (m_table != null) { dataSet.Tables.Add(m_table); export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, m_table.TableName + "_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); errors.addError("Export Completed"); } } // End 0000168, KuangWei, 2015-02-11 return; } else { errors.addError(HROne.Translation.PageErrorMessage.ERROR_NO_EMPLOYEE_SELECTED); } }
protected void btnExport_Click(object sender, EventArgs e) { DataTable tmpDataTable = new DataTable(TABLE_NAME); tmpDataTable.Columns.Add("Scheme Code", typeof(string)); tmpDataTable.Columns.Add("Capacity", typeof(string)); tmpDataTable.Columns.Add("First Point", typeof(Decimal)); tmpDataTable.Columns.Add("Mid Point", typeof(Decimal)); tmpDataTable.Columns.Add("Last Point", typeof(Decimal)); DBFilter filter = sbinding.createFilter(); filter.add("LastPoint", false); ArrayList list = EPayScale.db.select(dbConn, filter); foreach (EPayScale c in list) { DataRow row = tmpDataTable.NewRow(); //row["ID"] = c.PayScaleID; row["Scheme Code"] = c.SchemeCode; row["Capacity"] = c.Capacity; row["First Point"] = c.FirstPoint; row["Mid Point"] = c.MidPoint; row["Last Point"] = c.LastPoint; tmpDataTable.Rows.Add(row); } string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; HROne.Export.ExcelExport excelExport = new HROne.Export.ExcelExport(exportFileName); excelExport.Update(tmpDataTable); WebUtils.TransmitFile(Response, exportFileName, "PayScale_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; }
protected void btnExport_Click(object sender, EventArgs e) { ArrayList list = WebUtils.SelectedRepeaterItemToBaseObjectList(db, Repeater, "ItemSelect"); int GroupingHierarchyLevelID = 1; string GroupingHierarchyLevelDesc = string.Empty; if (list.Count > 0) { 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 tmpDataTable = dataSet.Tables.Add(TABLE_NAME); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NAME, typeof(string)); tmpDataTable.Columns.Add(FIELD_JOB_TITLE, typeof(string)); tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_COST_CENTER, typeof(string)); tmpDataTable.Columns.Add(FIELD_PERCENTAGE, typeof(double)); foreach (EEmpPersonalInfo obj in list) { if (EEmpPersonalInfo.db.select(dbConn, obj)) { // get job title string empJobTitle = null; EEmpPositionInfo empPositionInfo = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime().Date, obj.EmpID); if (empPositionInfo != null) { EPosition position = new EPosition(); position.PositionID = empPositionInfo.PositionID; if (EPosition.db.select(dbConn, position)) { empJobTitle = position.PositionDesc; } } DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", obj.EmpID)); string m_year = this.Year.Text; // ((TextBox)EmployeeSearchControl1.AdditionElementControl.FindControl("Year")).Text; string m_month = this.Month.Text; // ((DropDownList)EmployeeSearchControl1.AdditionElementControl.FindControl("Month")).SelectedValue; filter.add(getEffRangeDBTerm(m_year, m_month, null)); ArrayList empCsts = EEmpCostCenter.db.select(dbConn, filter); foreach (EEmpCostCenter empCostCenter in empCsts) { DBFilter empCstDetailFilter = new DBFilter(); empCstDetailFilter.add(new Match("EmpCostCenterID", empCostCenter.EmpCostCenterID)); ArrayList empCstDetailList = EEmpCostCenterDetail.db.select(dbConn, empCstDetailFilter); foreach (EEmpCostCenterDetail detail in empCstDetailList) { DataRow row = tmpDataTable.NewRow(); row[FIELD_EMP_NO] = obj.EmpNo; row[FIELD_EMP_NAME] = obj.EmpEngFullNameWithAlias; //for field Job Title if (!string.IsNullOrEmpty(empJobTitle)) { row[FIELD_JOB_TITLE] = empJobTitle; } //for fields From and To row[FIELD_FROM] = empCostCenter.EmpCostCenterEffFr; row[FIELD_TO] = empCostCenter.EmpCostCenterEffTo; //for field Cost Center ECostCenter costCenter = new ECostCenter(); costCenter.CostCenterID = detail.CostCenterID; if (ECostCenter.db.select(dbConn, costCenter)) { row[FIELD_COST_CENTER] = costCenter.CostCenterDesc;//costCenter.CostCenterCode } //for field Percentage row[FIELD_PERCENTAGE] = detail.EmpCostCenterPercentage; //add row data to data table tmpDataTable.Rows.Add(row); } } } } export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, "CostCenterExportReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); Response.End(); } else { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.addError("Employee not selected"); } view = loadData(info, db, Repeater); }
protected void btnGenerate_Command(object sender, CommandEventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); 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) { DataSet dataSet = GenerateAttendanceRecordDataSet(empList, CurPayPeriodID); if (e.CommandName.Equals("Export", StringComparison.CurrentCultureIgnoreCase)) { 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); export.Update(dataSet); //WebUtils.RegisterDownloadFileJavaScript(this, exportFileName, "AttendancePaymentCND_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true, 0); WebUtils.TransmitFile(Response, exportFileName, "AttendancePaymentCND_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); } else if (e.CommandName.Equals("ImportCND", StringComparison.CurrentCultureIgnoreCase)) { HROne.Import.ImportClaimsAndDeductionsProcess CNDImport = new HROne.Import.ImportClaimsAndDeductionsProcess(dbConn, Session.SessionID, WebUtils.GetCurUser(Session).UserID); CNDImport.ClearTempTable(); EPayrollPeriod payPeriod = new EPayrollPeriod(); payPeriod.PayPeriodID = CurPayPeriodID; if (EPayrollPeriod.db.select(dbConn, payPeriod)) { CNDImport.Remark = "Attendance Payment from " + payPeriod.PayPeriodAttnFr.ToString("yyyy-MM-dd") + " to " + payPeriod.PayPeriodAttnTo.ToString("yyyy-MM-dd"); EPayrollGroup payGroup = new EPayrollGroup(); payGroup.PayGroupID = payPeriod.PayGroupID; if (EPayrollGroup.db.select(dbConn, payGroup)) { CNDImport.Remark += " (" + payGroup.PayGroupCode + ")"; } } try { CNDImport.UploadToTempDatabase(dataSet.Tables[0], WebUtils.GetCurUser(Session).UserID); CNDImport.ImportToDatabase(); errors.addError(HROne.Translation.PageMessage.IMPORT_SUCCESSFUL); } catch (HROne.Import.HRImportException ex) { if (CNDImport.errors.List.Count > 0) { foreach (string errorString in CNDImport.errors.List) { errors.addError(errorString); } } else { errors.addError(ex.Message); } } } } else { errors.addError("Employee not selected"); } }
protected void Export_Click(object sender, EventArgs e) { 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 tmpDataTable = dataSet.Tables.Add(TABLE_NAME); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NAME, typeof(string)); tmpDataTable.Columns.Add(FIELD_TIME_CARD_RECORD_NO, typeof(string)); tmpDataTable.Columns.Add(FIELD_TIME_CARD_DATE_TIME, typeof(string));//DateTime tmpDataTable.Columns.Add(FIELD_TIME_CARD_RECORD_LOCATION, typeof(string)); //DBFilter filter = new DBFilter(); //filter.add(new Match("EmpID", CurID)); //ArrayList empRosterTableGroupList = EEmpRosterTableGroup.db.select(dbConn, filter); //if (empRosterTableGroupList.Count > 0) //{ // EEmpRosterTableGroup empRosterTableGroup = (EEmpRosterTableGroup)empRosterTableGroupList[0]; // DBFilter subordinateFilter = new DBFilter(); // subordinateFilter.add(new Match("EmpID", "!=", empRosterTableGroup.EmpID)); // subordinateFilter.add(new Match("RosterTableGroupID", empRosterTableGroup.RosterTableGroupID)); DBFilter m_empIDFilter = new DBFilter(); DBFilter m_inFilter = new DBFilter(); m_inFilter.add(new Match("EmpID", CurID)); m_inFilter.add(new Match("EmpRosterTableGroupIsSupervisor", "!=", 0)); m_empIDFilter.add(new IN("RosterTableGroupID", "SELECT RosterTableGroupID FROM EmpRosterTableGroup", m_inFilter)); DBFilter m_staffFilter = new DBFilter(); m_staffFilter.add(new IN("EmpID", "SELECT EmpID FROM EmpRosterTableGroup", m_empIDFilter)); ArrayList m_subordinateList = EEmpPersonalInfo.db.select(dbConn, m_staffFilter); if (m_subordinateList.Count <= 0) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.addError("Employee not selected"); } else { foreach (EEmpPersonalInfo m_subordinate in m_subordinateList) { DBFilter timeCardRecordFilter = new DBFilter(); OR orTimeCardRecordHolder = new OR(); orTimeCardRecordHolder.add(new Match("EmpID", m_subordinate.EmpID)); orTimeCardRecordHolder.add(new Match("TimeCardRecordCardNo", GetTimeCardNo(m_subordinate.EmpID))); DateTime dtTimeCardRecordDate; if (!DateTime.TryParse(TimeCardRecordDate.Value, out dtTimeCardRecordDate)) { dtTimeCardRecordDate = AppUtils.ServerDateTime().Date; } timeCardRecordFilter.add(new Match("TimeCardRecordDateTime", ">=", dtTimeCardRecordDate)); timeCardRecordFilter.add(new Match("TimeCardRecordDateTime", "<", dtTimeCardRecordDate.AddDays(1))); timeCardRecordFilter.add(orTimeCardRecordHolder); ArrayList timeCardRecordList = ETimeCardRecord.db.select(dbConn, timeCardRecordFilter); if (timeCardRecordList.Count > 0) { foreach (ETimeCardRecord timeCardRecord in timeCardRecordList) { DataRow row = tmpDataTable.NewRow(); EEmpPersonalInfo empPersonInfo = new EEmpPersonalInfo(); empPersonInfo.EmpID = timeCardRecord.EmpID; if (EEmpPersonalInfo.db.select(dbConn, empPersonInfo)) { row[FIELD_EMP_NO] = empPersonInfo.EmpNo; row[FIELD_EMP_NAME] = empPersonInfo.EmpEngFullName; } row[FIELD_TIME_CARD_RECORD_NO] = timeCardRecord.TimeCardRecordCardNo; row[FIELD_TIME_CARD_DATE_TIME] = timeCardRecord.TimeCardRecordDateTime.ToString("yyyy-MM-dd HH:mm:ss"); row[FIELD_TIME_CARD_RECORD_LOCATION] = timeCardRecord.TimeCardRecordLocation; tmpDataTable.Rows.Add(row); } } } export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, "TeamRecordExport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); Response.End(); } }
protected void ExportReport() { DBFilter m_filter = new DBFilter(); foreach (ListItem li in CriteriaFieldCBL.Items) { // Start 0000167, Miranda, 2015-03-09 String[] str = li.Text.Split(new Char[] { '|' }); String DBField = str[0].ToString().Trim(); // End 0000167, Miranda, 2015-03-09 // Start 0000167, Miranda, 2015-03-11 String criteria = str[1].ToString().Trim(); String value = str[2].ToString().Trim(); // End 0000167, Miranda, 2015-03-11 switch (criteria) { case "IS": m_filter.add(new Match(DBField, value)); break; case "NOT IS": m_filter.add(new Match(DBField, "!=", value)); break; // Start 0000167, Miranda, 2015-03-11 case "CONTAIN": m_filter.add(new Match(DBField, "LIKE", "%" + value + "%")); break; case "NOT CONTAIN": m_filter.add(new Match(DBField, "NOT LIKE", "%" + value + "%")); break; case "START WITH": m_filter.add(new Match(DBField, "LIKE", value + "%")); break; case "END WITH": m_filter.add(new Match(DBField, "LIKE", "%" + value)); break; // End 0000167, Miranda, 2015-03-11 case "LESS THAN": m_filter.add(new Match(DBField, "<", value)); break; case "GRATER THAN": m_filter.add(new Match(DBField, ">", value)); break; case "LESS OR EQUAL": m_filter.add(new Match(DBField, "<=", value)); break; case "GRATER OR EQUAL": m_filter.add(new Match(DBField, ">=", value)); break; } } // Start 0000167, Miranda, 2015-03-09 //ArrayList empList = EEmpPersonalInfo.db.select(dbConn, m_filter); // load data String SelectedFieldNames = getSelectedFields(); // Start 0000167, Miranda, 2015-04-06 String from = " FROM rb_" + ModuleName.SelectedItem.Text.Replace(" ", "_"); m_filter.add(WebUtils.AddRankFilter(Session, "EmpID", true)); DataTable m_table = AppUtils.runSelectSQL(SelectedFieldNames, from, m_filter, dbConn); // End 0000167, Miranda, 2015-04-06 if (m_table.Rows.Count > 0) // End 0000167, Miranda, 2015-03-09 { 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(); String fileName = ExportFileName.Text; DataTable tmpDataTable = dataSet.Tables.Add(fileName); // Start 0000167, Miranda, 2015-03-09 //foreach (ListItem item in FilterFieldCheckBoxList.Items) //{ // if (!item.Text.Equals("Not Selected")) // { // String[] values = item.Value.Split(new Char[] { ';' }); // string Description = item.Text; // string FieldID = values[0]; // string Type = values[1]; // //string Type = values[2]; // switch (Type) // { // case "tvarchar": tmpDataTable.Columns.Add(Description, typeof(string)); break; // case "tdate": tmpDataTable.Columns.Add(Description, typeof(DateTime)); break; // case "tinteger": tmpDataTable.Columns.Add(Description, typeof(double)); break; // } // } //} foreach (DataColumn fieldName in m_table.Columns) { string fieldType = fieldName.DataType.ToString(); switch (fieldType) { case "System.String": case "System.Char": tmpDataTable.Columns.Add(fieldName.ColumnName, typeof(string)); break; case "System.DateTime": case "System.TimeSpan": tmpDataTable.Columns.Add(fieldName.ColumnName, typeof(DateTime)); break; case "System.Boolean": break; default: tmpDataTable.Columns.Add(fieldName.ColumnName, typeof(double)); break; } } //foreach (EEmpPersonalInfo obj in empList) foreach (DataRow curRow in m_table.Rows) { DataRow row = tmpDataTable.NewRow(); //foreach (ListItem item in FilterFieldCheckBoxList.Items) //{ // if (!item.Text.Equals("Not Selected")) // { // String[] values = item.Value.Split(new Char[] { ';' }); // string Description = item.Text; // string FieldID = values[0]; // string Type = values[1]; // //string Type = values[2]; // foreach (System.Reflection.PropertyInfo p in obj.GetType().GetProperties()) // { // if (p.Name.ToString().Equals(DBField)) // { // row[Description] = p.GetValue(obj, null); // } // } // } //} Object[] o = curRow.ItemArray; for (int i = 0; i < o.Length; i++) { row[i] = o[i]; } // End 0000167, Miranda, 2015-03-09 tmpDataTable.Rows.Add(row); } export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, fileName + "_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); Response.End(); } else { PageErrors errors = PageErrors.getErrors(db, Page.Master); // Start 0000167, Miranda, 2015-03-09 errors.addError("No data"); // End 0000167, Miranda, 2015-03-09 } }
protected void btnExport_Click(object sender, EventArgs e) { ArrayList list = WebUtils.SelectedRepeaterItemToBaseObjectList(ECostAllocation.db, Repeater, "ItemSelect"); if (list.Count > 0) { 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("CostAllocation$"); 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("EmployeeID", typeof(string)); dataTable.Columns.Add("EnglishName", typeof(string)); dataTable.Columns.Add("ChineseName", typeof(string)); dataTable.Columns.Add("PaymentCodeDesc", typeof(string)); dataTable.Columns.Add("CostCenterCode", typeof(string)); dataTable.Columns.Add("Amount", typeof(double)); foreach (ECostAllocation obj in list) { if (ECostAllocation.db.select(dbConn, obj)) { EEmpPersonalInfo empInfo = new EEmpPersonalInfo(); empInfo.EmpID = obj.EmpID; EEmpPersonalInfo.db.select(dbConn, empInfo); DBFilter costAllocationDetailFilter = new DBFilter(); costAllocationDetailFilter.add(new Match("CostAllocationID", obj.CostAllocationID)); ArrayList costAllocationDetailList = ECostAllocationDetail.db.select(dbConn, costAllocationDetailFilter); foreach (ECostAllocationDetail detail in costAllocationDetailList) { ECompany company = new ECompany(); company.CompanyID = detail.CompanyID; ECompany.db.select(dbConn, company); DataRow row = dataTable.NewRow(); row["EmployeeID"] = empInfo.EmpNo; row["EnglishName"] = empInfo.EmpEngFullName;; row["ChineseName"] = empInfo.EmpChiFullName; row["Company"] = company.CompanyCode; DBFilter costAllocationDetailHierarchyFilter = new DBFilter(); costAllocationDetailHierarchyFilter.add(new Match("CostAllocationDetailID", detail.CostAllocationDetailID)); ArrayList empHierarchyList = ECostAllocationDetailHElement.db.select(dbConn, costAllocationDetailHierarchyFilter); foreach (ECostAllocationDetailHElement 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.HElementCode; } } } EPaymentCode paymentCode = new EPaymentCode(); paymentCode.PaymentCodeID = detail.PaymentCodeID; EPaymentCode.db.select(dbConn, paymentCode); if (detail.CostAllocationDetailIsContribution) { row["PaymentCodeDesc"] = paymentCode.PaymentCodeDesc + "(Contribution)"; } else { row["PaymentCodeDesc"] = paymentCode.PaymentCodeDesc; } ECostCenter costCenter = new ECostCenter(); costCenter.CostCenterID = detail.CostCenterID; ECostCenter.db.select(dbConn, costCenter); row["CostCenterCode"] = costCenter.CostCenterCode; row["Amount"] = detail.CostAllocationDetailAmount; dataTable.Rows.Add(row); } } } export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, "CostALlocation" + (CostAllocationStatus.SelectedValue.Equals("T")?"Trial":"Confirm") + "_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; } else { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.addError("Employee not selected"); } view = loadData(info, EEmpPayroll.db, Repeater); }
protected void btnGenerate_Click(object sender, EventArgs e) { const string FIELD_COMPANY = "Company"; const string FIELD_POSITION = "Position"; const string FIELD_PAYROLLGROUP = "Payroll Group"; const string FIELD_EMPNO = "EmployeeID"; const string FIELD_EMPENGFULLNAME = "English Name"; const string FIELD_CHINESENAME = "¤¤¤å©m¦W"; const string FIELD_HKID = @"HKID/Passport"; const string FIELD_PERIODFROM = "From"; const string FIELD_PERIODTO = "To"; const string FIELD_WAGESWORK = "Wages Paid"; const string FIELD_WORKHOURTOTAL = "Total Working Hours"; const string FIELD_RESTDAYTOTAL = "No. of Rest Day"; const string FIELD_STATUTORYHOLIDAYTOTAL = "No. of SH"; const string FIELD_FULLPAIDLEAVETOTAL = "No. of Full Paid Leave"; const string FIELD_NONFULLPAIDLEAVETOTAL = "Non-Full Paid Leave"; ArrayList list = 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); list.Add(o); } } ArrayList payPeriodList = Payroll_ConfirmedPeriod_List1.GetSelectedBaseObjectList(); if (list.Count > 0 && payPeriodList.Count > 0) { //const string PAYMENTCODE_PREFIX = "[StatutoryMinimumWageSummary] "; 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(FIELD_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(FIELD_POSITION, typeof(string)); dataTable.Columns.Add(FIELD_PAYROLLGROUP, typeof(string)); dataTable.Columns.Add(FIELD_EMPNO, typeof(string)); dataTable.Columns.Add(FIELD_EMPENGFULLNAME, typeof(string)); dataTable.Columns.Add(FIELD_CHINESENAME, typeof(string)); dataTable.Columns.Add(FIELD_HKID, typeof(string)); dataTable.Columns.Add(FIELD_PERIODFROM, typeof(DateTime)); dataTable.Columns.Add(FIELD_PERIODTO, typeof(DateTime)); dataTable.Columns.Add(FIELD_WAGESWORK, typeof(double)); dataTable.Columns.Add(FIELD_WORKHOURTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_RESTDAYTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_STATUTORYHOLIDAYTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_FULLPAIDLEAVETOTAL, typeof(double)); dataTable.Columns.Add(FIELD_NONFULLPAIDLEAVETOTAL, typeof(double)); int firstSummaryColumnPos = dataTable.Columns.Count; int firstDetailColumnPos = dataTable.Columns.Count; foreach (EPayrollPeriod payPeriod in payPeriodList) { if (EPayrollPeriod.db.select(dbConn, payPeriod)) { EPayrollGroup payrollGroup = new EPayrollGroup(); payrollGroup.PayGroupID = payPeriod.PayGroupID; EPayrollGroup.db.select(dbConn, payrollGroup); foreach (EEmpPersonalInfo empInfo in list) { EEmpPersonalInfo.db.select(dbConn, empInfo); EEmpTermination empTerm = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("ep.EmpID", empInfo.EmpID)); empPayrollFilter.add(new Match("ep.PayPeriodID", payPeriod.PayPeriodID)); if (PayrollStatus.SelectedValue.Equals("T")) { empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T")); } else { empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T")); } DataRow row = dataTable.NewRow(); row[FIELD_EMPNO] = empInfo.EmpNo; row[FIELD_EMPENGFULLNAME] = empInfo.EmpEngFullName; row[FIELD_CHINESENAME] = empInfo.EmpChiFullName; row[FIELD_HKID] = empInfo.EmpHKID; row[FIELD_PERIODFROM] = payPeriod.PayPeriodFr; row[FIELD_PERIODTO] = payPeriod.PayPeriodTo; 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[FIELD_COMPANY] = company.CompanyCode; } EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { row[FIELD_POSITION] = position.PositionDesc; } 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; } } } EPayrollGroup curentPayGroup = new EPayrollGroup(); curentPayGroup.PayGroupID = empPos.PayGroupID; if (EPayrollGroup.db.select(dbConn, curentPayGroup)) { row[FIELD_PAYROLLGROUP] = curentPayGroup.PayGroupDesc; } } 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; double wagesByWork = 0; double wagesByRest = 0; double fullPaidLeaveDays = 0; double nonFullPaidLeaveDays = 0; DBFilter paymentRecordFilter = new DBFilter(); paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); 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; } if (payCode.PaymentCodeIsWages) { if (paymentRecord.PayRecIsRestDayPayment) { wagesByRest += paymentRecord.PayRecActAmount; } else { wagesByWork += paymentRecord.PayRecActAmount; } } } DBFilter mpfRecordFilter = new DBFilter(); mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); 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[FIELD_WAGESWORK] = wagesByWork; DBFilter workingSummaryFilter = new DBFilter(); workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr)); if (empTerm != null) { workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo)); } else { workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo)); } workingSummaryFilter.add(new Match("EmpID", empInfo.EmpID)); ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, workingSummaryFilter); double workHourTotal = 0, restDayTotal = 0; foreach (EEmpWorkingSummary empWorkSummary in empWorkingSummaryList) { workHourTotal += empWorkSummary.EmpWorkingSummaryTotalWorkingHours; restDayTotal += empWorkSummary.EmpWorkingSummaryRestDayEntitled; } row[FIELD_WORKHOURTOTAL] = workHourTotal; row[FIELD_RESTDAYTOTAL] = restDayTotal; DBFilter statutoryHolidayFilter = new DBFilter(); statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr)); if (empTerm != null) { statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo)); } else { statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo)); } ArrayList statutoryHolidayList = EStatutoryHoliday.db.select(dbConn, statutoryHolidayFilter); double restDayCount = 0; foreach (EStatutoryHoliday statutoryHoliday in statutoryHolidayList) { restDayCount++; } row[FIELD_STATUTORYHOLIDAYTOTAL] = restDayCount; DBFilter LeaveAppEmpPayrollFilter = new DBFilter(); LeaveAppEmpPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); ArrayList LeaveAppEmpPayrollLists = ELeaveApplication.db.select(dbConn, LeaveAppEmpPayrollFilter); foreach (ELeaveApplication leaveApp in LeaveAppEmpPayrollLists) { ELeaveCode leaveCode = new ELeaveCode(); leaveCode.LeaveCodeID = leaveApp.LeaveCodeID; if (ELeaveCode.db.select(dbConn, leaveCode)) { if (leaveCode.LeaveCodePayRatio >= 1) { fullPaidLeaveDays += leaveApp.LeaveAppDays; } else { nonFullPaidLeaveDays += leaveApp.LeaveAppDays; } } } row[FIELD_FULLPAIDLEAVETOTAL] = fullPaidLeaveDays; row[FIELD_NONFULLPAIDLEAVETOTAL] = nonFullPaidLeaveDays; 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 // { // Console.Write("System reserved payment column is used"); // } // } //} //for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++) // dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1); export.Update(dataSet); System.IO.FileStream excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelfileStream); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0); workSheet.ShiftRows(workSheet.FirstRowNum, workSheet.LastRowNum, 1); NPOI.HSSF.UserModel.HSSFRow excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(0); if (excelRow == null) { excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0); } NPOI.HSSF.UserModel.HSSFCell excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.GetCell(0); if (excelCell == null) { excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.CreateCell(0); } excelCell.SetCellValue("Statutory Minimum Wage Summary Report"); excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open); workbook.Write(excelfileStream); excelfileStream.Close(); WebUtils.TransmitFile(Response, exportFileName, "StatutoryMinimumWageSummary_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; } else { PageErrors errors = PageErrors.getErrors(db, Page.Master); 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"); } }