protected void HierarchyLevel_ItemDataBound(object sender, RepeaterItemEventArgs e) { EHierarchyLevel level = (EHierarchyLevel)e.Item.DataItem; DBFilter filter = new DBFilter(); filter.add(new Match("HLevelID", level.HLevelID)); filter.add(new Match("CompanyID", CompanyID.SelectedValue.Equals(string.Empty) ? "0" : CompanyID.SelectedValue)); DropDownList c = (DropDownList)e.Item.FindControl("HElementID"); EEmpHierarchy h = (EEmpHierarchy)CurElements[level.HLevelID]; string selected = null; if (h != null) { selected = h.HElementID.ToString(); EHierarchyElement element = new EHierarchyElement(); element.HElementID = h.HElementID; EHierarchyElement.db.select(dbConn, element); Label HierarchyElementLabel = (Label)e.Item.FindControl("OldHElementID"); HierarchyElementLabel.Text = element.HElementCode + " - " + element.HElementDesc; } else { c.Text = ""; } WebFormUtils.loadValues(dbConn, c, EHierarchyElement.VLHierarchyElement, filter, null, selected, "combobox.notselected"); c.Attributes["HLevelID"] = level.HLevelID.ToString(); }
protected void Repeater_ItemDataBound(object sender, DataListItemEventArgs e) { HROne.Common.WebUtility.WebControlsLocalization(Session, e.Item.Controls); e.Item.FindControl("DeleteItem").Visible = IsAllowEdit; if (e.Item.ItemIndex == Repeater.EditItemIndex) { ebinding = new Binding(dbConn, db); ebinding.add((HtmlInputHidden)e.Item.FindControl("HLevelID")); ebinding.add((TextBox)e.Item.FindControl("HLevelCode")); ebinding.add((TextBox)e.Item.FindControl("HLevelDesc")); ebinding.add((TextBox)e.Item.FindControl("HLevelSeqNo")); ebinding.init(Request, Session); EHierarchyLevel obj = new EHierarchyLevel(); db.toObject(((DataRowView)e.Item.DataItem).Row, obj); Hashtable values = new Hashtable(); db.populate(obj, values); ebinding.toControl(values); } else { HtmlInputHidden h = (HtmlInputHidden)e.Item.FindControl("HLevelID"); h.Value = ((DataRowView)e.Item.DataItem)["HLevelID"].ToString(); e.Item.FindControl("Edit").Visible = IsAllowEdit; } }
protected void Repeater_ItemCommand(object source, DataListCommandEventArgs e) { Button b = (Button)e.CommandSource; if (b.ID.Equals("Edit")) { Repeater.EditItemIndex = e.Item.ItemIndex; view = loadData(info, db, Repeater); WebUtils.SetEnabledControlSection(AddPanel, false); } else if (b.ID.Equals("Cancel")) { Repeater.EditItemIndex = -1; view = loadData(info, db, Repeater); WebUtils.SetEnabledControlSection(AddPanel, true); } else if (b.ID.Equals("Save")) { ebinding = new Binding(dbConn, db); ebinding.add((HtmlInputHidden)e.Item.FindControl("HLevelID")); ebinding.add((TextBox)e.Item.FindControl("HLevelCode")); ebinding.add((TextBox)e.Item.FindControl("HLevelDesc")); ebinding.add((TextBox)e.Item.FindControl("HLevelSeqNo")); ebinding.init(Request, Session); EHierarchyLevel obj = new EHierarchyLevel(); Hashtable values = new Hashtable(); PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); ebinding.toValues(values); db.validate(errors, values); if (!errors.isEmpty()) { return; } db.parse(values, obj); if (!AppUtils.checkDuplicate(dbConn, db, obj, errors, "HLevelCode")) { return; } WebUtils.StartFunction(Session, FUNCTION_CODE); db.update(dbConn, obj); WebUtils.EndFunction(dbConn); Repeater.EditItemIndex = -1; view = loadData(info, db, Repeater); WebUtils.SetEnabledControlSection(AddPanel, true); } }
protected void Page_Load(object sender, EventArgs e) { if (!WebUtils.CheckAccess(Response, Session, FUNCTION_CODE, WebUtils.AccessLevel.ReadWrite)) { return; } toolBar.FunctionCode = FUNCTION_CODE; binding = new Binding(dbConn, db); binding.add(HElementID); binding.add(CompanyID); binding.add(HLevelID); binding.add(HElementCode); binding.add(HElementDesc); binding.init(Request, Session); if (!int.TryParse(DecryptedRequest["HElementID"], out CurID)) { CurID = -1; } HROne.Common.WebUtility.WebControlsLocalization(this, this.Controls); if (!Page.IsPostBack) { if (CurID > 0) { loadObject(); } else { HLevelID.Value = DecryptedRequest["HLevelID"]; CompanyID.Value = DecryptedRequest["CompanyID"]; toolBar.DeleteButton_Visible = false; } } int intHLevelID, intCompanyID; HLevel = new EHierarchyLevel(); if (int.TryParse(HLevelID.Value, out intHLevelID)) { HLevel.HLevelID = intHLevelID; EHierarchyLevel.db.select(dbConn, HLevel); } Company = new ECompany(); if (int.TryParse(CompanyID.Value, out intCompanyID)) { Company.CompanyID = intCompanyID; ECompany.db.select(dbConn, Company); } }
protected void HierarchyLevel_ItemDataBound(object sender, RepeaterItemEventArgs e) { EHierarchyLevel level = (EHierarchyLevel)e.Item.DataItem; DBFilter filter = new DBFilter(); filter.add(new Match("HLevelID", level.HLevelID)); DropDownList c = (DropDownList)e.Item.FindControl("HElementID"); string selected = c.SelectedValue; WebFormUtils.loadValues(dbConn, c, EHierarchyElement.VLHierarchyElement, filter, ci, selected, "combobox.notselected"); c.Attributes["HLevelID"] = level.HLevelID.ToString(); }
// Start 0000185, KuangWei, 2015-05-05 public EmployeeGenderProcess(DatabaseConnection dbConn, DateTime AsOfDate, ReportGroupBy GroupBy, int HierarchyLevelID, ArrayList empList) : base(dbConn) { this.CurrentDate = AsOfDate; this.ReferenceDate = AsOfDate; this.GroupBy = GroupBy; this.HierarchyLevelID = HierarchyLevelID; EHierarchyLevel HLevel = new EHierarchyLevel(); HLevel.HLevelID = HierarchyLevelID; if (EHierarchyLevel.db.select(dbConn, HLevel)) { this.HierarchyLevelName = HLevel.HLevelDesc; } this.empList = empList; }
private static DataRow CreateWorkingSummaryRow(DatabaseConnection dbConn, DataTable dataTable, Hashtable hierarchyLevelHashTable, EEmpPersonalInfo empInfo, DateTime AsOfDate, double RestDayEntitled, double RestDayTaken, double TotalWorkingDays, double TotalWorkingHours) { DataRow row = dataTable.NewRow(); row[FIELD_EMP_NO] = empInfo.EmpNo; row["EnglishName"] = empInfo.EmpEngFullName; row["ChineseName"] = empInfo.EmpChiFullName; DBFilter empPosFilter = new DBFilter(); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AsOfDate, 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.HElementCode; } } } } row[FIELD_AS_OF_DATE] = AsOfDate; row[FIELD_REST_DAY_ENTITLED] = RestDayEntitled; row[FIELD_REST_DAY_TAKEN] = RestDayTaken; row[FIELD_TOTAL_WORKING_DAYS] = TotalWorkingDays; row[FIELD_TOTAL_WORKING_HOURS] = TotalWorkingHours; dataTable.Rows.Add(row); return(row); }
protected void HierarchyLevel_ItemDataBound(object sender, RepeaterItemEventArgs e) { HROne.Common.WebUtility.WebControlsLocalization(Session, e.Item.Controls); EHierarchyLevel level = (EHierarchyLevel)e.Item.DataItem; Label lblHLevel = (Label)e.Item.FindControl("HLevel"); lblHLevel.Text = level.HLevelDesc; DBFilter filter = new DBFilter(); filter.add(new Match("HLevelID", level.HLevelID)); filter.add(new Match("CompanyID", ((HtmlInputHidden)e.Item.Parent.Parent.FindControl("CompanyID")).Value)); CheckBoxList c = (CheckBoxList)e.Item.FindControl("HierarchyElementList"); string selected = c.SelectedValue; WebFormUtils.loadValues(dbConn, c, EHierarchyElement.VLHierarchyElement, filter, ci, null, "combobox.notselected"); c.Attributes["HLevelID"] = level.HLevelID.ToString(); }
protected void HierarchyLevel_ItemDataBound(object sender, RepeaterItemEventArgs e) { EHierarchyLevel level = (EHierarchyLevel)e.Item.DataItem; Label c = (Label)e.Item.FindControl("HElementID"); // Start 0000125, Miranda, 2014-11-19 HyperLink hl = (HyperLink)e.Item.FindControl("hlHElementID"); // End 0000125, Miranda, 2014-11-19 EEmpHierarchy h = (EEmpHierarchy)CurElements[level.HLevelID]; //string selected = null; if (h != null) { EHierarchyElement element = new EHierarchyElement(); element.HElementID = h.HElementID; EHierarchyElement.db.select(dbConn, element); c.Text = element.HElementCode + " - " + element.HElementDesc; // Start 0000125, Miranda, 2014-11-19 if (h.HElementID > 0) { hl.NavigateUrl += h.HElementID; hl.ForeColor = System.Drawing.Color.RoyalBlue; } else { hl.NavigateUrl = ""; } // End 0000125, Miranda, 2014-11-19 } else { c.Text = ""; // Start 0000125, Miranda, 2014-11-19 hl.NavigateUrl = ""; // End 0000125, Miranda, 2014-11-19 } }
protected void Add_Click(object sender, EventArgs e) { Repeater.EditItemIndex = -1; EHierarchyLevel c = new EHierarchyLevel(); Hashtable values = new Hashtable(); binding.toValues(values); PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); db.validate(errors, values); if (!errors.isEmpty()) { return; } db.parse(values, c); if (!AppUtils.checkDuplicate(dbConn, db, c, errors, "HLevelCode")) { return; } WebUtils.StartFunction(Session, FUNCTION_CODE); db.insert(dbConn, c); WebUtils.EndFunction(dbConn); AppUtils.repairHierarchyInformation(dbConn); binding.toControl(new Hashtable()); view = loadData(info, db, Repeater); }
public override CrystalDecisions.CrystalReports.Engine.ReportDocument GenerateReport() { if (EmpPayrollList != null) { DataSet.PayrollTrialRunSummaryDataSet dataSet = new DataSet.PayrollTrialRunSummaryDataSet(); DataSet.PayrollTrialRunSummaryDataSet.TrialRunSummaryDataTable dataTable = dataSet.TrialRunSummary; foreach (EEmpPayroll empPayroll in EmpPayrollList) { EEmpPayroll.db.select(dbConn, empPayroll); EPayrollPeriod payPeriod = new EPayrollPeriod(); payPeriod.PayPeriodID = empPayroll.PayPeriodID; EPayrollPeriod.db.select(dbConn, payPeriod); string strPayPeriod = payPeriod.PayPeriodFr.ToString("yyyy-MM-dd") + " - " + payPeriod.PayPeriodTo.ToString("yyyy-MM-dd"); EPayrollGroup payGroup = new EPayrollGroup(); payGroup.PayGroupID = payPeriod.PayGroupID; EPayrollGroup.db.select(dbConn, payGroup); EPayrollBatch payBatch = new EPayrollBatch(); payBatch.PayBatchID = empPayroll.PayBatchID; EPayrollBatch.db.select(dbConn, payBatch); EEmpPersonalInfo empInfo = new EEmpPersonalInfo(); empInfo.EmpID = empPayroll.EmpID; EEmpPersonalInfo.db.select(dbConn, empInfo); DataSet.PayrollTrialRunSummaryDataSet.TrialRunSummaryRow row = dataTable.NewTrialRunSummaryRow(); row.EmpPayrollID = empPayroll.EmpPayrollID; row.EmpID = empPayroll.EmpID; row.EmpNo = empInfo.EmpNo; row.EmpName = empInfo.EmpEngFullName; row.EmpAlias = empInfo.EmpAlias; row.PayGroupDesc = payGroup.PayGroupCode + " - " + payGroup.PayGroupDesc; row.PayGroupID = empPayroll.PayPeriodID; row.PayrollPeriod = strPayPeriod; if (!payBatch.PayBatchValueDate.Ticks.Equals(0)) { row.PayBatchValueDate = payBatch.PayBatchValueDate; } row.PayBatchRemark = payBatch.PayBatchRemark; row.BasicSalary = 0; row.LeaveAllowance = 0; row.PFundEE = 0; row.PFundER = 0; row.Overtime = 0; row.Bonus = 0; row.Commission = 0; row.Others = 0; row.NetIncome = 0; row.TotalIncome = 0; row.PayMethodAutoPay = 0; row.PayMethodCash = 0; row.PayMethodCheque = 0; row.PayMethodOthers = 0; AND recurringBasicSalaryAndTerms = new AND(); recurringBasicSalaryAndTerms.add(new Match("EmpRPEffFr", "<=", payPeriod.PayPeriodTo)); DBFilter basicSalaryPaymentCodeFilter = new DBFilter(); basicSalaryPaymentCodeFilter.add(new Match("PaymentTypeID", EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeID)); recurringBasicSalaryAndTerms.add(new IN("PayCodeID", "Select PaymentCodeID FROM " + EPaymentCode.db.dbclass.tableName, basicSalaryPaymentCodeFilter)); EEmpRecurringPayment empRP = (EEmpRecurringPayment)AppUtils.GetLastObj(dbConn, EEmpRecurringPayment.db, "EmpRPEffFr", empPayroll.EmpID, recurringBasicSalaryAndTerms); if (empRP != null) { row.RecurringBasicSalary = empRP.EmpRPAmount; } 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); EPaymentType payType = new EPaymentType(); payType.PaymentTypeID = payCode.PaymentTypeID; EPaymentType.db.select(dbConn, payType); if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.BasicSalary += paymentRecord.PayRecActAmount; row.TotalIncome += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.LeaveDeductionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.LeaveAllowance += paymentRecord.PayRecActAmount; row.TotalIncome += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.LeaveAllowancePaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.LeaveAllowance += paymentRecord.PayRecActAmount; row.TotalIncome += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.MPFEmployeeMandatoryContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.PFundEE += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.MPFEmployeeVoluntaryContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.PFundEE += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.PFundEmployeeContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.PFundEE += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.MPFEmployerMandatoryContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.PFundER += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.MPFEmployerVoluntaryContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.PFundER += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.PFundEmployerContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.PFundER += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.OverTimePaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.Overtime += paymentRecord.PayRecActAmount; row.TotalIncome += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.BonusPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.Bonus += paymentRecord.PayRecActAmount; row.TotalIncome += paymentRecord.PayRecActAmount; } else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.CommissionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase)) { row.Commission += paymentRecord.PayRecActAmount; row.TotalIncome += paymentRecord.PayRecActAmount; } else { row.Others += paymentRecord.PayRecActAmount; row.TotalIncome += paymentRecord.PayRecActAmount; } if (paymentRecord.PayRecMethod.Equals("A")) { row.PayMethodAutoPay += paymentRecord.PayRecActAmount; } else if (paymentRecord.PayRecMethod.Equals("C")) { row.PayMethodCash += paymentRecord.PayRecActAmount; } else if (paymentRecord.PayRecMethod.Equals("Q")) { row.PayMethodCheque += paymentRecord.PayRecActAmount; } else { row.PayMethodOthers += paymentRecord.PayRecActAmount; } row.NetIncome += paymentRecord.PayRecActAmount; } row.TotalWagesForMinWages = HROne.Payroll.PayrollProcess.GetTotalWagesWithoutRestDayPayment(dbConn, empInfo.EmpID, payPeriod.PayPeriodFr, payPeriod.PayPeriodTo, null); row.TotalWorkingHours = HROne.Payroll.PayrollProcess.GetTotalEmpPayrollWorkingHours(dbConn, empInfo.EmpID, payPeriod.PayPeriodID); row.MinWagesRequired = row.TotalWorkingHours * HROne.Payroll.PayrollProcess.GetMinimumWages(dbConn, empInfo.EmpID, payPeriod.PayPeriodTo); DBFilter mpfRecordFilter = new DBFilter(); mpfRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID)); ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter); foreach (EMPFRecord mpfRecord in mpfRecords) { row.PFundER += mpfRecord.MPFRecActVCER + mpfRecord.MPFRecActMCER; } ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter); foreach (EORSORecord orsoRecord in orsoRecords) { row.PFundER += orsoRecord.ORSORecActER; } DBFilter positionFilter = new DBFilter(); positionFilter.add(new Match("EmpID", empInfo.EmpID)); positionFilter.add(new Match("PayGroupID", payGroup.PayGroupID)); positionFilter.add(new Match("EmpPosEffFr", "<=", payPeriod.PayPeriodTo)); OR orPosEffToTerms = new OR(); orPosEffToTerms.add(new Match("EmpPosEffTo", ">=", payPeriod.PayPeriodFr)); orPosEffToTerms.add(new NullTerm("EmpPosEffTo")); positionFilter.add(orPosEffToTerms); ArrayList positionInfos = EEmpPositionInfo.db.select(dbConn, positionFilter); EEmpPositionInfo empPos = null; if (positionInfos.Count > 0) { empPos = (EEmpPositionInfo)positionInfos[0]; } else { empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID); } if (HLevelIDDisplayList != null && empPos != null) { for (int count = 0; count < HLevelIDDisplayList.Count && count < 2; count++) { int hLevelID = Convert.ToInt32(HLevelIDDisplayList[count]); EHierarchyLevel hLevel = new EHierarchyLevel(); hLevel.HLevelID = hLevelID; if (EHierarchyLevel.db.select(dbConn, hLevel)) { row["HLevelDesc" + (count + 1)] = hLevel.HLevelDesc; DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); empHierarchyFilter.add(new Match("HLevelID", hLevelID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); if (empHierarchyList.Count > 0) { EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0]; EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hElement)) { row["HElementCode" + (count + 1)] = hElement.HElementCode; row["HElementDesc" + (count + 1)] = hElement.HElementDesc; } } } } } dataTable.Rows.Add(row); } if (reportDocument == null) { if (reportType.Equals(ReportType.TrialRun)) { reportDocument = new ReportTemplate.Report_Payroll_TrialRunSummary(); } else if (reportType.Equals(ReportType.History)) { reportDocument = new ReportTemplate.Report_Payroll_HistorySummary(); } } else { } reportDocument.SetDataSource(dataSet); return(reportDocument); } else { return(null); } }
public static string GetValueFromID(DatabaseConnection dbConn, string fieldName, string fieldValue) { try { string tmpfieldName = fieldName; if (tmpfieldName.Equals("PreviousEmpID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("NewEmpID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "EmpID"; } if (tmpfieldName.EndsWith("PaymentCodeID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.EndsWith("PayCodeID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PaymentCodeID"; } if (tmpfieldName.Equals("DefaultMPFPlanID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "MPFPlanID"; } if (tmpfieldName.Equals("AttendanceFormulaPayFormID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("ReferencePayFormID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PayFormID"; } if (tmpfieldName.StartsWith("LeaveCode", StringComparison.CurrentCultureIgnoreCase) && tmpfieldName.EndsWith("Formula", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PayFormID"; } if (tmpfieldName.StartsWith("PayGroup", StringComparison.CurrentCultureIgnoreCase) && tmpfieldName.EndsWith("Formula", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PayFormID"; } if (tmpfieldName.Equals("CurrentPayPeriodID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "PayPeriodID"; } if (tmpfieldName.Equals("EmpPosDefaultRosterCodeID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "RosterCodeID"; } if (tmpfieldName.Equals("RosterClientMappingSiteCodeToHLevelID", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "HLevelID"; } if (tmpfieldName.Equals("EmpFirstAuthorizationGp", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("EmpSecondAuthorizationGp", StringComparison.CurrentCultureIgnoreCase)) { tmpfieldName = "AuthorizationGroupID"; } if (tmpfieldName.StartsWith("A", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("ALProrataRoundingRuleID", StringComparison.CurrentCultureIgnoreCase)) { EALProrataRoundingRule obj = new EALProrataRoundingRule(); obj.ALProrataRoundingRuleID = int.Parse(fieldValue); if (EALProrataRoundingRule.db.select(dbConn, obj)) { return(obj.ALProrataRoundingRuleCode + " - " + obj.ALProrataRoundingRuleDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("AttendancePlanID", StringComparison.CurrentCultureIgnoreCase)) { EAttendancePlan obj = new EAttendancePlan(); obj.AttendancePlanID = int.Parse(fieldValue); if (EAttendancePlan.db.select(dbConn, obj)) { return(obj.AttendancePlanCode + " - " + obj.AttendancePlanDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("AuthorizationGroupID", StringComparison.CurrentCultureIgnoreCase)) { EAuthorizationGroup obj = new EAuthorizationGroup(); obj.AuthorizationGroupID = int.Parse(fieldValue); if (EAuthorizationGroup.db.select(dbConn, obj)) { return(obj.AuthorizationCode + " - " + obj.AuthorizationDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("AVCPlanID", StringComparison.CurrentCultureIgnoreCase)) { EAVCPlan obj = new EAVCPlan(); obj.AVCPlanID = int.Parse(fieldValue); if (EAVCPlan.db.select(dbConn, obj)) { return(obj.AVCPlanCode + " - " + obj.AVCPlanDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("C", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("CessationReasonID", StringComparison.CurrentCultureIgnoreCase)) { ECessationReason obj = new ECessationReason(); obj.CessationReasonID = int.Parse(fieldValue); if (ECessationReason.db.select(dbConn, obj)) { return(obj.CessationReasonCode + " - " + obj.CessationReasonDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("CompanyID", StringComparison.CurrentCultureIgnoreCase)) { ECompany obj = new ECompany(); obj.CompanyID = int.Parse(fieldValue); if (ECompany.db.select(dbConn, obj)) { return(obj.CompanyCode + " - " + obj.CompanyName); } else { return(string.Empty); } } if (tmpfieldName.Equals("CostAllocationDetailID", StringComparison.CurrentCultureIgnoreCase)) { ECostAllocationDetail obj = new ECostAllocationDetail(); obj.CostAllocationDetailID = int.Parse(fieldValue); if (ECostAllocationDetail.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "PayCodeID", obj.PaymentCodeID.ToString()) + ", " + GetValueFromID(dbConn, "CostCenterID", obj.CostCenterID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("CostAllocationID", StringComparison.CurrentCultureIgnoreCase)) { ECostAllocation obj = new ECostAllocation(); obj.CostAllocationID = int.Parse(fieldValue); if (ECostAllocation.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "EmpPayrollID", obj.EmpPayrollID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("CostCenterID", StringComparison.CurrentCultureIgnoreCase)) { ECostCenter obj = new ECostCenter(); obj.CostCenterID = int.Parse(fieldValue); if (ECostCenter.db.select(dbConn, obj)) { return(obj.CostCenterCode + " - " + obj.CostCenterDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("D", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("DocumentTypeID", StringComparison.CurrentCultureIgnoreCase)) { EDocumentType obj = new EDocumentType(); obj.DocumentTypeID = int.Parse(fieldValue); if (EDocumentType.db.select(dbConn, obj)) { return(obj.DocumentTypeCode + " - " + obj.DocumentTypeDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("E", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("EmpAccID", StringComparison.CurrentCultureIgnoreCase)) { EEmpBankAccount obj = new EEmpBankAccount(); obj.EmpBankAccountID = int.Parse(fieldValue); if (EEmpBankAccount.db.select(dbConn, obj)) { return(obj.EmpBankCode + "-" + obj.EmpBranchCode + "-" + obj.EmpAccountNo); } //return obj.EmpBankCode + "-" + obj.EmpBranchCode + "-" + string.Empty.PadRight(obj.EmpAccountNo.Length, 'X'); else { return(string.Empty); } } if (tmpfieldName.Equals("EmpCostCenterID", StringComparison.CurrentCultureIgnoreCase)) { EEmpCostCenter obj = new EEmpCostCenter(); obj.EmpCostCenterID = int.Parse(fieldValue); if (EEmpCostCenter.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "EmpID", obj.EmpID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmpExtraFieldID", StringComparison.CurrentCultureIgnoreCase)) { EEmpExtraField obj = new EEmpExtraField(); obj.EmpExtraFieldID = int.Parse(fieldValue); if (EEmpExtraField.db.select(dbConn, obj)) { return(obj.EmpExtraFieldName); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmpID", StringComparison.CurrentCultureIgnoreCase)) { EEmpPersonalInfo obj = new EEmpPersonalInfo(); obj.EmpID = int.Parse(fieldValue); if (EEmpPersonalInfo.db.select(dbConn, obj)) { return(obj.EmpNo + " - " + obj.EmpEngFullName); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmpPayrollID", StringComparison.CurrentCultureIgnoreCase)) { EEmpPayroll obj = new EEmpPayroll(); obj.EmpPayrollID = int.Parse(fieldValue); if (EEmpPayroll.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "EmpID", obj.EmpID.ToString()) + " : " + GetValueFromID(dbConn, "PayPeriodID", obj.PayPeriodID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmpPosID", StringComparison.CurrentCultureIgnoreCase)) { EEmpPositionInfo obj = new EEmpPositionInfo(); obj.EmpPosID = int.Parse(fieldValue); if (EEmpPositionInfo.db.select(dbConn, obj)) { return(obj.EmpPosEffFr.ToString("yyyy-MM-dd") + " - " + (obj.EmpPosEffTo.Ticks.Equals(0) ? "Present" : obj.EmpPosEffTo.ToString("yyyy-MM-dd"))); } else { return(string.Empty); } } if (tmpfieldName.Equals("EmploymentTypeID", StringComparison.CurrentCultureIgnoreCase)) { EEmploymentType obj = new EEmploymentType(); obj.EmploymentTypeID = int.Parse(fieldValue); if (EEmploymentType.db.select(dbConn, obj)) { return(obj.EmploymentTypeCode + " - " + obj.EmploymentTypeDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("F", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("FunctionID", StringComparison.CurrentCultureIgnoreCase)) { ESystemFunction obj = new ESystemFunction(); obj.FunctionID = int.Parse(fieldValue); if (ESystemFunction.db.select(dbConn, obj)) { return(obj.FunctionCode + " - " + obj.Description); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("H", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("HElementID", StringComparison.CurrentCultureIgnoreCase)) { EHierarchyElement obj = new EHierarchyElement(); obj.HElementID = int.Parse(fieldValue); if (EHierarchyElement.db.select(dbConn, obj)) { return(obj.HElementCode + " - " + obj.HElementDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("HLevelID", StringComparison.CurrentCultureIgnoreCase)) { EHierarchyLevel obj = new EHierarchyLevel(); obj.HLevelID = int.Parse(fieldValue); if (EHierarchyLevel.db.select(dbConn, obj)) { return(obj.HLevelCode + " - " + obj.HLevelDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("L", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("LeaveTypeID", StringComparison.CurrentCultureIgnoreCase)) { ELeaveType obj = new ELeaveType(); obj.LeaveTypeID = int.Parse(fieldValue); if (ELeaveType.db.select(dbConn, obj)) { return(obj.LeaveType + " - " + obj.LeaveTypeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("LeaveCodeID", StringComparison.CurrentCultureIgnoreCase)) { ELeaveCode obj = new ELeaveCode(); obj.LeaveCodeID = int.Parse(fieldValue); if (ELeaveCode.db.select(dbConn, obj)) { return(obj.LeaveCode + " - " + obj.LeaveCodeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("LeavePlanID", StringComparison.CurrentCultureIgnoreCase)) { ELeavePlan obj = new ELeavePlan(); obj.LeavePlanID = int.Parse(fieldValue); if (ELeavePlan.db.select(dbConn, obj)) { return(obj.LeavePlanCode + " - " + obj.LeavePlanDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("M", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("MPFPlanID", StringComparison.CurrentCultureIgnoreCase)) { EMPFPlan obj = new EMPFPlan(); obj.MPFPlanID = int.Parse(fieldValue); if (EMPFPlan.db.select(dbConn, obj)) { return(obj.MPFPlanCode + " - " + obj.MPFPlanDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("MPFSchemeID", StringComparison.CurrentCultureIgnoreCase)) { EMPFScheme obj = new EMPFScheme(); obj.MPFSchemeID = int.Parse(fieldValue); if (EMPFScheme.db.select(dbConn, obj)) { return(obj.MPFSchemeCode + " - " + obj.MPFSchemeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("MPFSchemeCessationReasonID", StringComparison.CurrentCultureIgnoreCase)) { EMPFSchemeCessationReason obj = new EMPFSchemeCessationReason(); obj.MPFSchemeCessationReasonID = int.Parse(fieldValue); if (EMPFSchemeCessationReason.db.select(dbConn, obj)) { return(obj.MPFSchemeCessationReasonCode + " - " + obj.MPFSchemeCessationReasonDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("O", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("ORSOPlanID", StringComparison.CurrentCultureIgnoreCase)) { EORSOPlan obj = new EORSOPlan(); obj.ORSOPlanID = int.Parse(fieldValue); if (EORSOPlan.db.select(dbConn, obj)) { return(obj.ORSOPlanCode + " - " + obj.ORSOPlanDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("P", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("PayGroupID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("PayrollGroupID", StringComparison.CurrentCultureIgnoreCase)) { EPayrollGroup obj = new EPayrollGroup(); obj.PayGroupID = int.Parse(fieldValue); if (EPayrollGroup.db.select(dbConn, obj)) { return(obj.PayGroupCode + " - " + obj.PayGroupDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PayFormID", StringComparison.CurrentCultureIgnoreCase)) { EPayrollProrataFormula obj = new EPayrollProrataFormula(); obj.PayFormID = int.Parse(fieldValue); if (EPayrollProrataFormula.db.select(dbConn, obj)) { return(obj.PayFormCode + " - " + obj.PayFormDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PaymentCodeID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("PayCodeID", StringComparison.CurrentCultureIgnoreCase)) { EPaymentCode obj = new EPaymentCode(); obj.PaymentCodeID = int.Parse(fieldValue); if (EPaymentCode.db.select(dbConn, obj)) { return(obj.PaymentCode + " - " + obj.PaymentCodeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PaymentTypeID", StringComparison.CurrentCultureIgnoreCase)) { EPaymentType obj = new EPaymentType(); obj.PaymentTypeID = int.Parse(fieldValue); if (EPaymentType.db.select(dbConn, obj)) { return(obj.PaymentTypeCode + " - " + obj.PaymentTypeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PayPeriodID", StringComparison.CurrentCultureIgnoreCase)) { EPayrollPeriod obj = new EPayrollPeriod(); obj.PayPeriodID = int.Parse(fieldValue); if (EPayrollPeriod.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "PayrollGroupID", obj.PayGroupID.ToString()) + ": " + obj.PayPeriodFr.ToString("yyyy-MM-dd") + " to " + obj.PayPeriodTo.ToString("yyyy-MM-dd")); } else { return(string.Empty); } } if (tmpfieldName.Equals("PermitTypeID", StringComparison.CurrentCultureIgnoreCase)) { EPermitType obj = new EPermitType(); obj.PermitTypeID = int.Parse(fieldValue); if (EPermitType.db.select(dbConn, obj)) { return(obj.PermitTypeCode + " - " + obj.PermitTypeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("PositionID", StringComparison.CurrentCultureIgnoreCase)) { EPosition obj = new EPosition(); obj.PositionID = int.Parse(fieldValue); if (EPosition.db.select(dbConn, obj)) { return(obj.PositionCode + " - " + obj.PositionDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("Q", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("QualificationID", StringComparison.CurrentCultureIgnoreCase)) { EQualification obj = new EQualification(); obj.QualificationID = int.Parse(fieldValue); if (EQualification.db.select(dbConn, obj)) { return(obj.QualificationCode + " - " + obj.QualificationDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("R", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("RankID", StringComparison.CurrentCultureIgnoreCase)) { ERank obj = new ERank(); obj.RankID = int.Parse(fieldValue); if (ERank.db.select(dbConn, obj)) { return(obj.RankCode + " - " + obj.RankDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("ReminderTypeID", StringComparison.CurrentCultureIgnoreCase)) { EReminderType obj = new EReminderType(); obj.ReminderTypeID = int.Parse(fieldValue); if (EReminderType.db.select(dbConn, obj)) { return(obj.ReminderTypeCode + " - " + obj.ReminderTypeDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("RosterClientID", StringComparison.CurrentCultureIgnoreCase)) { ERosterClient obj = new ERosterClient(); obj.RosterClientID = int.Parse(fieldValue); if (ERosterClient.db.select(dbConn, obj)) { return(obj.RosterClientCode + " - " + obj.RosterClientName); } else { return(string.Empty); } } if (tmpfieldName.Equals("RosterClientSiteID", StringComparison.CurrentCultureIgnoreCase)) { ERosterClientSite obj = new ERosterClientSite(); obj.RosterClientSiteID = int.Parse(fieldValue); if (ERosterClientSite.db.select(dbConn, obj)) { return(obj.RosterClientSiteCode); } else { return(string.Empty); } } if (tmpfieldName.Equals("RosterCodeID", StringComparison.CurrentCultureIgnoreCase)) { ERosterCode obj = new ERosterCode(); obj.RosterCodeID = int.Parse(fieldValue); if (ERosterCode.db.select(dbConn, obj)) { return(obj.RosterCode + " - " + obj.RosterCodeDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("S", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("SkillID", StringComparison.CurrentCultureIgnoreCase)) { ESkill obj = new ESkill(); obj.SkillID = int.Parse(fieldValue); if (ESkill.db.select(dbConn, obj)) { return(obj.SkillCode + " - " + obj.SkillDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("SkillLevelID", StringComparison.CurrentCultureIgnoreCase)) { ESkillLevel obj = new ESkillLevel(); obj.SkillLevelID = int.Parse(fieldValue); if (ESkillLevel.db.select(dbConn, obj)) { return(obj.SkillLevelCode + " - " + obj.SkillLevelDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("StaffTypeID", StringComparison.CurrentCultureIgnoreCase)) { EStaffType obj = new EStaffType(); obj.StaffTypeID = int.Parse(fieldValue); if (EStaffType.db.select(dbConn, obj)) { return(obj.StaffTypeCode + " - " + obj.StaffTypeDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("T", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("TaxCompID", StringComparison.CurrentCultureIgnoreCase)) { ETaxCompany obj = new ETaxCompany(); obj.TaxCompID = int.Parse(fieldValue); if (ETaxCompany.db.select(dbConn, obj)) { return(obj.TaxCompEmployerName); } else { return(string.Empty); } } if (tmpfieldName.Equals("TaxEmpID", StringComparison.CurrentCultureIgnoreCase)) { ETaxEmp obj = new ETaxEmp(); obj.TaxEmpID = int.Parse(fieldValue); if (ETaxEmp.db.select(dbConn, obj)) { return(obj.TaxEmpSurname + ", " + obj.TaxEmpOtherName + ", " + GetValueFromID(dbConn, "TaxFormID", obj.TaxFormID.ToString())); } else { return(string.Empty); } } if (tmpfieldName.Equals("TaxFormID", StringComparison.CurrentCultureIgnoreCase)) { ETaxForm obj = new ETaxForm(); obj.TaxFormID = int.Parse(fieldValue); if (ETaxForm.db.select(dbConn, obj)) { return("Tax Year :" + obj.TaxFormYear + ", Form: IR56" + obj.TaxFormType); } else { return(string.Empty); } } if (tmpfieldName.Equals("TaxPayID", StringComparison.CurrentCultureIgnoreCase)) { ETaxPayment obj = new ETaxPayment(); obj.TaxPayID = int.Parse(fieldValue); if (ETaxPayment.db.select(dbConn, obj)) { return(obj.TaxPayCode + " - " + obj.TaxPayDesc); } else { return(string.Empty); } } if (tmpfieldName.Equals("TrainingCourseID", StringComparison.CurrentCultureIgnoreCase)) { ETrainingCourse obj = new ETrainingCourse(); obj.TrainingCourseID = int.Parse(fieldValue); if (ETrainingCourse.db.select(dbConn, obj)) { return(obj.TrainingCourseCode + " - " + obj.TrainingCourseName); } else { return(string.Empty); } } if (tmpfieldName.Equals("TrainingSeminarID", StringComparison.CurrentCultureIgnoreCase)) { ETrainingSeminar obj = new ETrainingSeminar(); obj.TrainingSeminarID = int.Parse(fieldValue); if (ETrainingSeminar.db.select(dbConn, obj)) { return(GetValueFromID(dbConn, "TrainingCourseID", obj.TrainingCourseID.ToString()) + ": " + obj.TrainingSeminarDateFrom.ToString("yyyy-MM-dd") + " to " + obj.TrainingSeminarDateTo.ToString("yyyy-MM-dd")); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("U", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("UserID", StringComparison.CurrentCultureIgnoreCase)) { EUser obj = new EUser(); obj.UserID = int.Parse(fieldValue); if (EUser.db.select(dbConn, obj)) { return(obj.LoginID + " - " + obj.UserName); } else { return(string.Empty); } } if (tmpfieldName.Equals("UserGroupID", StringComparison.CurrentCultureIgnoreCase)) { EUserGroup obj = new EUserGroup(); obj.UserGroupID = int.Parse(fieldValue); if (EUserGroup.db.select(dbConn, obj)) { return(obj.UserGroupName + " - " + obj.UserGroupDesc); } else { return(string.Empty); } } } else if (tmpfieldName.StartsWith("Y", StringComparison.CurrentCultureIgnoreCase)) { if (tmpfieldName.Equals("YebPlanID", StringComparison.CurrentCultureIgnoreCase)) { EYEBPlan obj = new EYEBPlan(); obj.YEBPlanID = int.Parse(fieldValue); if (EYEBPlan.db.select(dbConn, obj)) { return(obj.YEBPlanCode + " - " + obj.YEBPlanDesc); } else { return(string.Empty); } } } if (tmpfieldName.EndsWith("ID") && !tmpfieldName.EndsWith("HKID") && !tmpfieldName.EndsWith("LoginID") && !tmpfieldName.EndsWith("CurrencyID") && !tmpfieldName.EndsWith("LeaveAppID") && !tmpfieldName.EndsWith("EmpPaymentID") && !tmpfieldName.EndsWith("PayBatchID") && !tmpfieldName.EndsWith("PayRecID") && !tmpfieldName.EndsWith("RosterTableID") && !tmpfieldName.EndsWith("SynID") && !tmpfieldName.EndsWith("CNDImportBatchID")) { if (ESystemParameter.getParameter(dbConn, "DebugMode").Equals("Y")) { throw new Exception("ID field not define:" + fieldName); } } return(string.Empty); } catch (Exception ex) { if (ESystemParameter.getParameter(dbConn, "DebugMode").Equals("Y")) { throw ex; } else { return(string.Empty); } } }
private void GenerateReport(ArrayList empList, DateTime PeriodFrom, DateTime PeriodTo, string SortBy) { DataTable dataTable = new DataTable(); dataTable.Columns.Add("EmpID", typeof(int)); dataTable.Columns.Add("EmpNo", typeof(string)); dataTable.Columns.Add("EmpName", typeof(string)); dataTable.Columns.Add("EmpAlias", typeof(string)); 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("Position", typeof(string)); dataTable.Columns.Add("TrainingSeminarID", typeof(int)); dataTable.Columns.Add("TrainingCourseName", typeof(string)); dataTable.Columns.Add("TrainingSeminarDesc", typeof(string)); dataTable.Columns.Add("TrainingSeminarDateFrom", typeof(DateTime)); dataTable.Columns.Add("TrainingSeminarDateTo", typeof(DateTime)); dataTable.Columns.Add("TrainingSeminarDuration", typeof(double)); dataTable.Columns.Add("TrainingSeminarDurationUnit", typeof(string)); dataTable.Columns.Add("TrainingSeminarTrainer", typeof(string)); foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime().Date, empInfo.EmpID); ECompany company = new ECompany(); EPosition position = new EPosition(); ArrayList empHierarchyList = new ArrayList(); if (empPos != null) { company.CompanyID = empPos.CompanyID; ECompany.db.select(dbConn, company); //row["Company"] = company.CompanyCode; DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); 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; // } //} position.PositionID = empPos.PositionID; EPosition.db.select(dbConn, position); } DBFilter empTrainingSeminar = new DBFilter(); empTrainingSeminar.add(new Match("EmpID", empInfo.EmpID)); OR orTrainingCourse = null; foreach (ListItem item in TrainingCourseList.Items) { if (item.Selected) { if (orTrainingCourse == null) { orTrainingCourse = new OR(); } orTrainingCourse.add(new Match("te.TrainingCourseID", item.Value)); } } DBFilter trainingSeminarFilter = new DBFilter(); if (!PeriodFrom.Ticks.Equals(0)) { trainingSeminarFilter.add(new Match("TrainingSeminarDateFrom", ">=", PeriodFrom)); } if (!PeriodTo.Ticks.Equals(0)) { trainingSeminarFilter.add(new Match("TrainingSeminarDateTo", "<=", PeriodTo)); } if (orTrainingCourse != null) { trainingSeminarFilter.add(orTrainingCourse); } empTrainingSeminar.add(new IN("TrainingSeminarID", "Select TrainingSeminarID from " + ETrainingSeminar.db.dbclass.tableName + " te", trainingSeminarFilter)); ArrayList empTrainingSeminarList = EEmpTrainingEnroll.db.select(dbConn, empTrainingSeminar); foreach (EEmpTrainingEnroll empTrainingEnroll in empTrainingSeminarList) { ETrainingSeminar trainingSeminar = new ETrainingSeminar(); trainingSeminar.TrainingSeminarID = empTrainingEnroll.TrainingSeminarID; if (ETrainingSeminar.db.select(dbConn, trainingSeminar)) { DataRow row = dataTable.NewRow(); row["EmpID"] = empInfo.EmpID; row["EmpNo"] = empInfo.EmpNo; row["EmpName"] = empInfo.EmpEngFullName; row["EmpAlias"] = empInfo.EmpAlias; row["Company"] = company.CompanyCode; 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; } } } row["Position"] = position.PositionDesc; row["TrainingSeminarID"] = trainingSeminar.TrainingSeminarID; ETrainingCourse trainingCourse = new ETrainingCourse(); trainingCourse.TrainingCourseID = trainingSeminar.TrainingCourseID; if (ETrainingCourse.db.select(dbConn, trainingCourse)) { row["TrainingCourseName"] = trainingCourse.TrainingCourseName; } else { row["TrainingCourseName"] = string.Empty; } row["TrainingSeminarDesc"] = trainingSeminar.TrainingSeminarDesc == null ? string.Empty : trainingSeminar.TrainingSeminarDesc; row["TrainingSeminarDateFrom"] = trainingSeminar.TrainingSeminarDateFrom; row["TrainingSeminarDateTo"] = trainingSeminar.TrainingSeminarDateTo; row["TrainingSeminarDuration"] = trainingSeminar.TrainingSeminarDuration; if (trainingSeminar.TrainingSeminarDurationUnit.Equals("H")) { row["TrainingSeminarDurationUnit"] = "Hour(s)"; } else { row["TrainingSeminarDurationUnit"] = trainingSeminar.TrainingSeminarDurationUnit; } row["TrainingSeminarTrainer"] = trainingSeminar.TrainingSeminarTrainer; dataTable.Rows.Add(row); } } } } //org.in2bits.MyXls.XlsDocument document = new org.in2bits.MyXls.XlsDocument(); //org.in2bits.MyXls.Worksheet worksheet = document.Workbook.Worksheets.Add("training report"); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("training report"); NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont(); boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); reportHeaderStyle.SetFont(boldFont); NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleCenter.SetFont(boldFont); columnHeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); //numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00"); int rowCount = 0; //worksheet.Cells.Add(rowCount, (ushort)1, "Training Report").Font.Bold = true; NPOI.HSSF.UserModel.HSSFCell reportHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)worksheet.CreateRow(rowCount).CreateCell(0); reportHeaderCell.SetCellValue("Training Report"); reportHeaderCell.CellStyle = reportHeaderStyle; rowCount++; if (!PeriodFrom.Ticks.Equals(0) && !PeriodTo.Ticks.Equals(0)) { rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy")); worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy")); rowCount++; } else if (!PeriodTo.Ticks.Equals(0)) { rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Up to: " + PeriodTo.ToString("dd/MM/yyyy")); worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Up to: " + PeriodTo.ToString("dd/MM/yyyy")); rowCount++; } else if (!PeriodFrom.Ticks.Equals(0)) { rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "From: " + PeriodFrom.ToString("dd/MM/yyyy")); worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("From: " + PeriodFrom.ToString("dd/MM/yyyy")); rowCount++; } if (SortBy.Equals("Date", StringComparison.CurrentCultureIgnoreCase)) { DataView dataView = new DataView(dataTable); dataView.Sort = "TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName, EmpNo"; DataTable sortedTable = dataView.ToTable(); rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Training Date").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)2, "Course Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)3, "Description").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true; //worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered; //worksheet.Cells.Add(rowCount, (ushort)5, string.Empty); //worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true; //worksheet.Cells.Merge(rowCount, rowCount, 4, 5); NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0); columnHeaderCell.SetCellValue("Training Date"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1); columnHeaderCell.SetCellValue("Course Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2); columnHeaderCell.SetCellValue("Description"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3); columnHeaderCell.SetCellValue("Duration"); columnHeaderCell.CellStyle = columnHeaderStyleCenter; worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4)); columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5); columnHeaderCell.SetCellValue("Trainer"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6); columnHeaderCell.SetCellValue("Employee No."); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7); columnHeaderCell.SetCellValue("Employee Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8); columnHeaderCell.SetCellValue("Alias"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9); columnHeaderCell.SetCellValue("Position"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10); columnHeaderCell.SetCellValue("Company"); columnHeaderCell.CellStyle = reportHeaderStyle; int colCount = 10; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount); columnHeaderCell.SetCellValue(hlevel.HLevelDesc); columnHeaderCell.CellStyle = reportHeaderStyle; } int currentTrainingSeminarID = 0; foreach (DataRow row in sortedTable.Rows) { rowCount++; NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); if (!currentTrainingSeminarID.Equals((int)row["TrainingSeminarID"])) { if (row["TrainingSeminarDateFrom"] != DBNull.Value) { DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]); DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]); if (trainingFrom.Equals(trainingTo)) { //worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy")); detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy")); } else { //worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); } } } currentTrainingSeminarID = ((int)row["TrainingSeminarID"]); //worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingCourseName"]); //worksheet.Cells.Add(rowCount, (ushort)3, row["TrainingSeminarDesc"]); //worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]); //worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]); //worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]); //worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]); //worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]); //worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]); //worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]); //worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]); detailRow.CreateCell(1).SetCellValue(row["TrainingCourseName"].ToString()); detailRow.CreateCell(2).SetCellValue(row["TrainingSeminarDesc"].ToString()); detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]); //detailRow.GetCell(3).CellStyle = numericStyle; detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString()); detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString()); detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString()); detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString()); detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString()); detailRow.CreateCell(9).SetCellValue(row["Position"].ToString()); detailRow.CreateCell(10).SetCellValue(row["Company"].ToString()); colCount = 10; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; if (row[hlevel.HLevelDesc] != DBNull.Value) { //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]); detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString()); } } } } else if (SortBy.Equals("Position", StringComparison.CurrentCultureIgnoreCase)) { DataView dataView = new DataView(dataTable); dataView.Sort = "Position, EmpNo, TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName "; DataTable sortedTable = dataView.ToTable(); rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Position").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)2, "Employee No.").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)3, "Employee Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)4, "Alias").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)5, "Company").Font.Bold = true; NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0); columnHeaderCell.SetCellValue("Position"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1); columnHeaderCell.SetCellValue("Employee No."); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2); columnHeaderCell.SetCellValue("Employee Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3); columnHeaderCell.SetCellValue("Alias"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(4); columnHeaderCell.SetCellValue("Company"); columnHeaderCell.CellStyle = reportHeaderStyle; int colCount = 4; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount); columnHeaderCell.SetCellValue(hlevel.HLevelDesc); columnHeaderCell.CellStyle = reportHeaderStyle; } //worksheet.Cells.Add(rowCount, (ushort)colCount + 1, "Course Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)colCount + 2, "Description").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, "Training Date").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)colCount + 4, "Duration").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)colCount + 5, string.Empty); //worksheet.Cells.Merge(rowCount, rowCount, colCount + 4, colCount + 5); //worksheet.Rows[rowCount].GetCell((ushort)(colCount + 4)).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered; //worksheet.Cells.Add(rowCount, (ushort)colCount + 6, "Trainer").Font.Bold = true; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 1); columnHeaderCell.SetCellValue("Course Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 2); columnHeaderCell.SetCellValue("Description"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 3); columnHeaderCell.SetCellValue("Training Date"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 4); columnHeaderCell.SetCellValue("Duration"); columnHeaderCell.CellStyle = columnHeaderStyleCenter; worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, colCount + 4, colCount + 5)); columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 6); columnHeaderCell.SetCellValue("Trainer"); columnHeaderCell.CellStyle = reportHeaderStyle; int currentEmpID = 0; foreach (DataRow row in sortedTable.Rows) { rowCount++; NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); if (!currentEmpID.Equals((int)row["EmpID"])) { //worksheet.Cells.Add(rowCount, (ushort)1, row["Position"]); //worksheet.Cells.Add(rowCount, (ushort)2, row["EmpNo"]); //worksheet.Cells.Add(rowCount, (ushort)3, row["EmpName"]); //worksheet.Cells.Add(rowCount, (ushort)4, row["EmpAlias"]); //worksheet.Cells.Add(rowCount, (ushort)5, row["Company"]); detailRow.CreateCell(0).SetCellValue(row["Position"].ToString()); detailRow.CreateCell(1).SetCellValue(row["EmpNo"].ToString()); detailRow.CreateCell(2).SetCellValue(row["EmpName"].ToString()); detailRow.CreateCell(3).SetCellValue(row["EmpAlias"].ToString()); detailRow.CreateCell(4).SetCellValue(row["Company"].ToString()); colCount = 4; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; if (row[hlevel.HLevelDesc] != DBNull.Value) { //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]); detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString()); } } } currentEmpID = ((int)row["EmpID"]); //worksheet.Cells.Add(rowCount, (ushort)colCount + 1, row["TrainingCourseName"]); //worksheet.Cells.Add(rowCount, (ushort)colCount + 2, row["TrainingSeminarDesc"]); detailRow.CreateCell(colCount + 1).SetCellValue(row["TrainingCourseName"].ToString()); detailRow.CreateCell(colCount + 2).SetCellValue(row["TrainingSeminarDesc"].ToString()); if (row["TrainingSeminarDateFrom"] != DBNull.Value) { DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]); DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]); if (trainingFrom.Equals(trainingTo)) { //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy")); detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy")); } else { //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); } } //worksheet.Cells.Add(rowCount, (ushort)colCount + 4, row["TrainingSeminarDuration"]); //worksheet.Cells.Add(rowCount, (ushort)colCount + 5, row["TrainingSeminarDurationUnit"]); //worksheet.Cells.Add(rowCount, (ushort)colCount + 6, row["TrainingSeminarTrainer"]); detailRow.CreateCell(colCount + 4).SetCellValue((double)row["TrainingSeminarDuration"]); //detailRow.GetCell(colCount + 4).CellStyle = numericStyle; detailRow.CreateCell(colCount + 5).SetCellValue(row["TrainingSeminarDurationUnit"].ToString()); detailRow.CreateCell(colCount + 6).SetCellValue(row["TrainingSeminarTrainer"].ToString()); } } else if (SortBy.Equals("Course", StringComparison.CurrentCultureIgnoreCase)) { DataView dataView = new DataView(dataTable); dataView.Sort = "TrainingCourseName, TrainingSeminarDateFrom, TrainingSeminarDateTo, EmpNo"; DataTable sortedTable = dataView.ToTable(); rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Course Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)2, "Description").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)3, "Training Date").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true; //worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered; //worksheet.Cells.Add(rowCount, (ushort)5, string.Empty); //worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true; //worksheet.Cells.Merge(rowCount, rowCount, 4, 5); NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0); columnHeaderCell.SetCellValue("Course Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1); columnHeaderCell.SetCellValue("Description"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2); columnHeaderCell.SetCellValue("Training Date"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3); columnHeaderCell.SetCellValue("Duration"); columnHeaderCell.CellStyle = columnHeaderStyleCenter; worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4)); columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5); columnHeaderCell.SetCellValue("Trainer"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6); columnHeaderCell.SetCellValue("Employee No."); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7); columnHeaderCell.SetCellValue("Employee Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8); columnHeaderCell.SetCellValue("Alias"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9); columnHeaderCell.SetCellValue("Position"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10); columnHeaderCell.SetCellValue("Company"); columnHeaderCell.CellStyle = reportHeaderStyle; int colCount = 10; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount); columnHeaderCell.SetCellValue(hlevel.HLevelDesc); columnHeaderCell.CellStyle = reportHeaderStyle; } foreach (DataRow row in sortedTable.Rows) { rowCount++; NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); //worksheet.Cells.Add(rowCount, (ushort)1, row["TrainingCourseName"]); //worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingSeminarDesc"]); detailRow.CreateCell(0).SetCellValue(row["TrainingCourseName"].ToString()); detailRow.CreateCell(1).SetCellValue(row["TrainingSeminarDesc"].ToString()); if (row["TrainingSeminarDateFrom"] != DBNull.Value) { DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]); DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]); if (trainingFrom.Equals(trainingTo)) { //worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy")); detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy")); } else { //worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); } } //worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]); //worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]); //worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]); //worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]); //worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]); //worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]); //worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]); //worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]); detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]); //detailRow.GetCell(3).CellStyle = numericStyle; detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString()); detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString()); detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString()); detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString()); detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString()); detailRow.CreateCell(9).SetCellValue(row["Position"].ToString()); detailRow.CreateCell(10).SetCellValue(row["Company"].ToString()); colCount = 10; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; if (row[hlevel.HLevelDesc] != DBNull.Value) { //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]); detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString()); } } } } string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; //document.FileName = exportFileName; //document.Save(); System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workbook.Write(file); file.Close(); string filename = "TrainingReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls"; WebUtils.TransmitFile(Response, exportFileName, filename, true); return; }
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); }
private void GenerateRosterTableData(ArrayList EmpInfoList, int year, int month) { DateTime dateStart = new DateTime(year, month, 1); DateTime dateEnd = new DateTime(year, month, DateTime.DaysInMonth(year, month)); string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; const string FIELD_EMP_NO = "Emp. No"; const int COLUMN_HEADER_ROW = 2; NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("RosterTable"); NPOI.HSSF.UserModel.HSSFCellStyle upperLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); upperLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle bottomLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); bottomLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle leftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); leftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle rightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); rightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle upperLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); upperLeftLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; upperLeftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle bottomLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); bottomLeftLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; bottomLeftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle upperRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); upperRightLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; upperRightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle bottomRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); bottomRightLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; bottomRightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; workSheet.CreateRow(0).CreateCell(0).SetCellValue("Year"); workSheet.GetRow(0).CreateCell(1).SetCellValue(year); workSheet.CreateRow(1).CreateCell(0).SetCellValue("Month"); workSheet.GetRow(1).CreateCell(1).SetCellValue(month); NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW); DBFilter hLevelFilter = new DBFilter(); hLevelFilter.add("HLevelSeqNo", true); int HIERARCHYLEVEL_COLUMN = 0; ArrayList hLevelList = EHierarchyLevel.db.select(dbConn, hLevelFilter); for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++) { EHierarchyLevel hLevel = (EHierarchyLevel)hLevelList[levelIndex]; headerRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hLevel.HLevelDesc); } int POSITION_COLUMN = headerRow.LastCellNum; headerRow.CreateCell(POSITION_COLUMN).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Position")); int EMPNO_COLUMN = headerRow.LastCellNum; headerRow.CreateCell(EMPNO_COLUMN).SetCellValue(FIELD_EMP_NO); headerRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Name")); headerRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Alias")); headerRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Chinese Name")); NPOI.HSSF.UserModel.HSSFCellStyle sundayStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); NPOI.HSSF.UserModel.HSSFFont sundayFont = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont(); sundayFont.Color = NPOI.HSSF.Util.HSSFColor.RED.index; sundayStyle.SetFont(sundayFont); Hashtable styleList = new Hashtable(); ArrayList availableRosterClientList = new ArrayList(); ArrayList availableRosterClientSiteList = new ArrayList(); #region Create Column Header int ROSTER_DETAIL_COLUMN = headerRow.LastCellNum; for (int i = 1; i <= DateTime.DaysInMonth(year, month); i++) { //workSheet.Cells.Add(HEADER_ROW, ROSTAER_DETAIL_COLUMN + i - 1,i); NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(ROSTER_DETAIL_COLUMN + i - 1); headerCell.SetCellValue(i); if (new DateTime(year, month, i).DayOfWeek == DayOfWeek.Sunday) { headerCell.CellStyle = sundayStyle; } } #endregion #region Create Employee Roster Detail int recordCount = 0; foreach (EEmpPersonalInfo empInfo in EmpInfoList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { recordCount++; //workSheet.Cells.Add(HEADER_ROW + recordCount, 1,empInfo.EmpNo); //workSheet.Cells.Add(HEADER_ROW + recordCount, 2,empInfo.EmpEngFullName); NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + recordCount); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, dateEnd, empInfo.EmpID); if (empPos != null) { for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++) { EHierarchyLevel hLevel = (EHierarchyLevel)hLevelList[levelIndex]; DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); empHierarchyFilter.add(new Match("HLevelID", hLevel.HLevelID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); if (empHierarchyList.Count > 0) { EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0]; EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hElement)) { detailRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hElement.HElementDesc); } } } EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { detailRow.CreateCell(POSITION_COLUMN).SetCellValue(position.PositionDesc); } } detailRow.CreateCell(EMPNO_COLUMN).SetCellValue(empInfo.EmpNo); detailRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(empInfo.EmpEngFullName); detailRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(empInfo.EmpAlias); detailRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(empInfo.EmpChiFullName); DBFilter rosterTableFilter = new DBFilter(); rosterTableFilter.add(new Match("EmpID", empInfo.EmpID)); rosterTableFilter.add(new Match("RosterTableDate", ">=", dateStart)); rosterTableFilter.add(new Match("RosterTableDate", "<=", dateEnd)); ArrayList rosterTableList = ERosterTable.db.select(dbConn, rosterTableFilter); foreach (ERosterTable rosterTable in rosterTableList) { ERosterCode rosterCode = new ERosterCode(); rosterCode.RosterCodeID = rosterTable.RosterCodeID; if (ERosterCode.db.select(dbConn, rosterCode)) { string value = string.Empty; //if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists ((ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)) ) // value = workSheet.Rows[(ushort)(HEADER_ROW+ recordCount)].CellAtCol( (ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)).Value.ToString(); //if (string.IsNullOrEmpty(value)) // workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,rosterCode.RosterCode); //else // workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,value + "|" + rosterCode.RosterCode); int cellColIndex = ROSTER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1; NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex); } else { value = rosterCell.StringCellValue; } string rosterCodeValue = rosterCode.RosterCode; if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0) || !rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0)) { DateTime inTime = rosterCode.RosterCodeInTime; DateTime outTime = rosterCode.RosterCodeOutTime; if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0)) { inTime = rosterTable.RosterTableOverrideInTime; } if (!rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0)) { outTime = rosterTable.RosterTableOverrideOutTime; } rosterCodeValue += "(" + inTime.ToString("HHmm") + "~" + outTime.ToString("HHmm") + ")"; } if (string.IsNullOrEmpty(value)) { rosterCell.SetCellValue(rosterCodeValue); //if (!string.IsNullOrEmpty(rosterCode.RosterCodeColorCode)) //{ // //System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode); // //System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color); // //rosterCell.CellStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex(); // //rosterCell.CellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; // //rosterCell.CellStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex(); // string styleCode = "RosterCode" + "_" + rosterCode.RosterCode; // if (styleList.Contains(styleCode)) // rosterCell.CellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)styleList[styleCode]; // else // { // NPOI.HSSF.UserModel.HSSFCellStyle rosterCodeStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); // System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode); // System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color); // rosterCodeStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex(); // rosterCodeStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; // rosterCodeStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex(); // styleList.Add(styleCode, rosterCodeStyle); // rosterCell.CellStyle = rosterCodeStyle; // } //} } else { rosterCell.SetCellValue(value + "|" + rosterCodeValue); //rosterCell.CellStyle=workBook.GetCellStyleAt(0); } } } for (DateTime dateIndex = dateStart; dateIndex <= dateEnd; dateIndex = dateIndex.AddDays(1)) { string value = string.Empty; //if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)) ) // value = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)).Value.ToString(); int cellColIndex = ROSTER_DETAIL_COLUMN + dateIndex.Day - 1; NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex); } else { value = rosterCell.StringCellValue; } if (string.IsNullOrEmpty(value)) { EEmpPositionInfo empPosInfo = AppUtils.GetLastPositionInfo(dbConn, dateIndex, empInfo.EmpID); if (empPosInfo != null) { if ((empPosInfo.EmpPosEffTo.Ticks.Equals(0) || dateIndex <= empPosInfo.EmpPosEffTo)) { EWorkHourPattern workPattern = new EWorkHourPattern(); workPattern.WorkHourPatternID = empPosInfo.WorkHourPatternID; if (EWorkHourPattern.db.select(dbConn, workPattern)) { ERosterCode rosterCode = new ERosterCode(); rosterCode.RosterCodeID = workPattern.GetDefaultRosterCodeID(dbConn, dateIndex); if (ERosterCode.db.select(dbConn, rosterCode)) { //workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1, rosterCode.RosterCode); rosterCell.SetCellValue(rosterCode.RosterCode); } } DBFilter empRosterTableGroupListFilter = new DBFilter(); empRosterTableGroupListFilter.add(new Match("EmpID", empInfo.EmpID)); empRosterTableGroupListFilter.add(new Match("empRosterTableGroupEffFr", "<=", dateIndex)); OR orEmpPosEffToTerms = new OR(); orEmpPosEffToTerms.add(new Match("empRosterTableGroupEffTo", ">=", dateIndex)); orEmpPosEffToTerms.add(new NullTerm("empRosterTableGroupEffTo")); empRosterTableGroupListFilter.add(orEmpPosEffToTerms); ArrayList empRosterTableGroupList = EEmpRosterTableGroup.db.select(dbConn, empRosterTableGroupListFilter); foreach (EEmpRosterTableGroup empRosterTableGroup in empRosterTableGroupList) { ERosterTableGroup rosterTableGroup = new ERosterTableGroup(); rosterTableGroup.RosterTableGroupID = empRosterTableGroup.RosterTableGroupID; if (ERosterTableGroup.db.select(dbConn, rosterTableGroup)) { if (rosterTableGroup.RosterClientSiteID > 0) { if (!availableRosterClientSiteList.Contains(rosterTableGroup.RosterClientSiteID)) { availableRosterClientSiteList.Add(rosterTableGroup.RosterClientSiteID); } } else if (rosterTableGroup.RosterClientID > 0) { if (!availableRosterClientList.Contains(rosterTableGroup.RosterClientID)) { availableRosterClientList.Add(rosterTableGroup.RosterClientID); } } } } } } } } DBFilter leaveAppFilter = new DBFilter(); leaveAppFilter.add(new Match("EmpID", empInfo.EmpID)); leaveAppFilter.add(new Match("LeaveAppDateTo", ">=", dateStart)); leaveAppFilter.add(new Match("LeaveAppDateFrom", "<=", dateEnd)); ArrayList leaveAppList = ELeaveApplication.db.select(dbConn, leaveAppFilter); foreach (ELeaveApplication leaveApp in leaveAppList) { ELeaveCode leaveCode = new ELeaveCode(); leaveCode.LeaveCodeID = leaveApp.LeaveCodeID; if (ELeaveCode.db.select(dbConn, leaveCode)) { //if (leaveCode.LeaveCodeColorCode.Length == 6) //{ // try // { // int red = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(0, 2), System.Globalization.NumberStyles.AllowHexSpecifier); // int green = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(2, 2), System.Globalization.NumberStyles.AllowHexSpecifier); // int blue = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(4, 2), System.Globalization.NumberStyles.AllowHexSpecifier); // string Color = System.Drawing.Color.FromArgb(red, green, blue).ToKnownColor().ToString(); // for (DateTime dateIndex = leaveApp.LeaveAppDateFrom; dateIndex <= leaveApp.LeaveAppDateTo; dateIndex = dateIndex.AddDays(1)) // { // org.in2bits.MyXls.Cell cell = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)); // //cell.Pattern = 1; // //cell.PatternColor = org.in2bits.MyXls.Colors.Yellow; // } // } // catch // { // } //} } } } } #endregion #region Create Roster Code Liet OR orRosterCodeTerm = new OR(); foreach (int rosterClientID in availableRosterClientList) { AND andRosterCodeTerms = new AND(); orRosterCodeTerm.add(new Match("RosterClientID", rosterClientID)); } foreach (int rosterClientSiteID in availableRosterClientSiteList) { AND andRosterCodeTerms = new AND(); orRosterCodeTerm.add(new Match("RosterClientSiteID", rosterClientSiteID)); } orRosterCodeTerm.add(new Match("RosterClientID", 0)); DBFilter rosterCodeListFilter = new DBFilter(); rosterCodeListFilter.add(orRosterCodeTerm); rosterCodeListFilter.add("RosterCode", true); ArrayList rosterCodeList = ERosterCode.db.select(dbConn, rosterCodeListFilter); int ROSTER_CODE_START_ROW = COLUMN_HEADER_ROW + recordCount + 5; int rosterCodeCount = 0; int maxColumnCount = 3; int columnCellWidth = 9; int maxRowCount = (int)(rosterCodeList.Count / maxColumnCount) + (rosterCodeList.Count % maxColumnCount == 0 ? 0 : 1); foreach (ERosterCode rosterCode in rosterCodeList) { int currentRowNum = rosterCodeCount % maxRowCount; int currentColumnNum = (rosterCodeCount / maxRowCount) * columnCellWidth; rosterCodeCount++; NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(ROSTER_CODE_START_ROW + currentRowNum); if (rosterCodeRow == null) { rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(ROSTER_CODE_START_ROW + currentRowNum); } NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum); rosterCell.SetCellValue(rosterCode.RosterCode); rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum + 1); rosterCell.SetCellValue(rosterCode.RosterCodeDesc); if (rosterCodeCount.Equals(1)) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN - 1); rosterCell.SetCellValue("Code:"); } } for (int rowIdx = ROSTER_CODE_START_ROW - 1; rowIdx < ROSTER_CODE_START_ROW + maxRowCount + 1; rowIdx++) { NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(rowIdx); if (rosterCodeRow == null) { rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowIdx); } if (rowIdx == ROSTER_CODE_START_ROW - 1) { for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++) { NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx); } if (colIdx == ROSTER_DETAIL_COLUMN - 1) { rosterCell.CellStyle = upperLeftLineStyle; } else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1) { rosterCell.CellStyle = upperRightLineStyle; } else { rosterCell.CellStyle = upperLineStyle; } } } else if (rowIdx == ROSTER_CODE_START_ROW + maxRowCount) { for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++) { NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx); } if (colIdx == ROSTER_DETAIL_COLUMN - 1) { rosterCell.CellStyle = bottomLeftLineStyle; } else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1) { rosterCell.CellStyle = bottomRightLineStyle; } else { rosterCell.CellStyle = bottomLineStyle; } } } else { for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++) { NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx); } if (colIdx == ROSTER_DETAIL_COLUMN - 1) { rosterCell.CellStyle = leftLineStyle; } else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1) { rosterCell.CellStyle = rightLineStyle; } //else // rosterCell.CellStyle = bottomLineStyle; } } } #endregion //doc.FileName = exportFileName; //doc.Save(); System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workBook.Write(file); file.Close(); WebUtils.TransmitFile(Response, exportFileName, "RosterTable_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); Response.End(); }
public void GenerateDetailByEmpID() { dataSet = new DataSet.Payroll_PaySlip(); 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) { if (EPayrollPeriod.db.select(dbConn, payPeriod)) { EPayrollGroup payGroup = new EPayrollGroup(); payGroup.PayGroupID = payPeriod.PayGroupID; EPayrollGroup.db.select(dbConn, payGroup); string strPayPeriod = payPeriod.PayPeriodFr.ToString("yyyy-MM-dd") + " - " + payPeriod.PayPeriodTo.ToString("yyyy-MM-dd"); DataSet.Payroll_PaySlip.EmpInfoDataTable empInfoTable = dataSet.EmpInfo; DataSet.Payroll_PaySlip.PaySlipDataTable dataTable = dataSet.PaySlip; DataSet.Payroll_PaySlip.PaySlip_MPFDataTable mpfTable = dataSet.PaySlip_MPF; DataSet.Payroll_PaySlip.PaySlip_ORSODataTable orsoTable = dataSet.PaySlip_ORSO; foreach (EEmpPersonalInfo empInfo in EmpList) { EEmpPersonalInfo.db.select(dbConn, empInfo); if ((reportType.Equals(ReportType.PaySlip) || reportType.Equals(ReportType.PaySlipDotMatrix)) && empInfo.MasterEmpID > 0 && empInfo.EmpIsCombinePaySlip) { continue; } DBFilter bankAccountFilter = new DBFilter(); if ((reportType.Equals(ReportType.PaySlip) || reportType.Equals(ReportType.PaySlipDotMatrix))) { bankAccountFilter.add(empInfo.GetAllRoleEmpIDTerms(dbConn, "EmpID", EEmpPersonalInfo.RoleFilterOptionEnum.Payslip)); } else { bankAccountFilter.add(new Match("EmpID", empInfo.EmpID)); } bankAccountFilter.add(new Match("EmpAccDefault", 1)); ArrayList bankAccounts = EEmpBankAccount.db.select(dbConn, bankAccountFilter); EEmpBankAccount bankAccount; if (bankAccounts.Count > 0) { bankAccount = (EEmpBankAccount)bankAccounts[0]; } else { bankAccount = new EEmpBankAccount(); } // Get Last Position using selected payroll group; DBFilter positionFilter = new DBFilter(); positionFilter.add(new Match("EmpID", empInfo.EmpID)); positionFilter.add(new Match("PayGroupID", payGroup.PayGroupID)); positionFilter.add(new Match("EmpPosEffFr", "<=", payPeriod.PayPeriodTo)); OR orPosEffToTerms = new OR(); orPosEffToTerms.add(new Match("EmpPosEffTo", ">=", payPeriod.PayPeriodFr)); orPosEffToTerms.add(new NullTerm("EmpPosEffTo")); positionFilter.add(orPosEffToTerms); ArrayList positionInfos = EEmpPositionInfo.db.select(dbConn, positionFilter); EEmpPositionInfo empPos = null; if (positionInfos.Count > 0) { empPos = (EEmpPositionInfo)positionInfos[0]; } else { empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID); } EPosition position = new EPosition(); ECompany company = new ECompany(); if (empPos != null) { position.PositionID = empPos.PositionID; EPosition.db.select(dbConn, position); company.CompanyID = empPos.CompanyID; ECompany.db.select(dbConn, company); } DBFilter empPayrollFilter = new DBFilter(); if ((reportType.Equals(ReportType.PaySlip) || reportType.Equals(ReportType.PaySlipDotMatrix))) { empPayrollFilter.add(empInfo.GetAllRoleEmpIDTerms(dbConn, "EmpID", EEmpPersonalInfo.RoleFilterOptionEnum.Payslip)); } else { 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) { bool skipPayBatch = false; if ((reportType.Equals(ReportType.PaySlip) || reportType.Equals(ReportType.PaySlipDotMatrix))) { if (empInfo.GetOtherRoleList(dbConn, EEmpPersonalInfo.RoleFilterOptionEnum.Payslip).Count > 0) { skipPayBatch = true; } } if (!skipPayBatch) { 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)); IN inEmpPayroll = new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter); ArrayList empPayrollList = EEmpPayroll.db.select(dbConn, empPayrollFilter); if (empPayrollList.Count > 0) { DBFilter paymentRecordFilter = new DBFilter(); paymentRecordFilter.add(inEmpPayroll); paymentRecordFilter.add(new Match("PayRecStatus", "A")); paymentRecordFilter.add("PaymentCodeID", true); paymentRecordFilter.add("PayRecMethod", true); ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter); DBFilter mpfRecordFilter = new DBFilter(); mpfRecordFilter.add(inEmpPayroll); mpfRecordFilter.add("MPFRecPeriodFr", true); ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter); DBFilter orsoRecordFilter = new DBFilter(); orsoRecordFilter.add(inEmpPayroll); orsoRecordFilter.add("ORSORecPeriodFr", true); ArrayList orsoRecords = EORSORecord.db.select(dbConn, orsoRecordFilter); //// need double confirm //DBFilter payBatchFilter = new DBFilter(); //payBatchFilter.add(new IN("PayBatchID", "Select PayBatchID from EmpPayroll", empPayrollFilter)); //payBatchFilter.add("PayBatchValueDate", false); //ArrayList payBatchs = EPayrollBatch.db.select(dbConn, payBatchFilter); //EPayrollBatch payBatch = null; //if (payBatchs.Count > 0) // payBatch = (EPayrollBatch)payBatchs[0]; DataSet.Payroll_PaySlip.EmpInfoRow empInfoRow = empInfoTable.NewEmpInfoRow(); empInfoRow.ActualEmpID = empInfo.EmpID; empInfoRow.EmpNo = empInfo.EmpNo; empInfoRow.EmpName = empInfo.EmpEngFullName; empInfoRow.EmpAlias = empInfo.EmpAlias; empInfoRow.EmpEngFullNameWithAlias = empInfo.EmpEngFullNameWithAlias; empInfoRow.EmpChineseName = empInfo.EmpChiFullName; empInfoRow.EmpDateOfJoin = empInfo.EmpDateOfJoin; empInfoRow.PayGroupDesc = payGroup.PayGroupCode + " - " + payGroup.PayGroupDesc; empInfoRow.PayrollPeriod = strPayPeriod; empInfoRow.PeriodFr = payPeriod.PayPeriodFr; empInfoRow.PeriodTo = payPeriod.PayPeriodTo; empInfoRow.MPFDate = new DateTime(payPeriod.PayPeriodTo.Year, payPeriod.PayPeriodTo.Month, 10).AddMonths(1); empInfoRow.BankAccount = bankAccount.EmpBankCode + " - " + bankAccount.EmpBranchCode + "-" + bankAccount.EmpAccountNo; empInfoRow.CompanyCode = company.CompanyCode; empInfoRow.CompanyName = company.CompanyName; empInfoRow.PositionDesc = position.PositionDesc; empInfoRow.IsShowMinWageInfo = IsShowMinimumWage; empInfoRow.TotalWagesForMinWages = HROne.Payroll.PayrollProcess.GetTotalWagesWithoutRestDayPayment(dbConn, empInfo.EmpID, payPeriod.PayPeriodFr, payPeriod.PayPeriodTo, null); empInfoRow.TotalWorkingHours = HROne.Payroll.PayrollProcess.GetTotalEmpPayrollWorkingHours(dbConn, empInfo.EmpID, payPeriod.PayPeriodID); empInfoRow.MinWagesRequired = empInfoRow.TotalWorkingHours * HROne.Payroll.PayrollProcess.GetMinimumWages(dbConn, empInfo.EmpID, payPeriod.PayPeriodTo); //if (payBatch != null) // if (payBatch.PayBatchValueDate.Ticks != 0) // empInfoRow.ValueDate = payBatch.PayBatchValueDate; if (HLevelIDDisplayList != null && empPos != null) { for (int count = 0; count < HLevelIDDisplayList.Count && count < 2; count++) { int hLevelID = Convert.ToInt32(HLevelIDDisplayList[count]); EHierarchyLevel hLevel = new EHierarchyLevel(); hLevel.HLevelID = hLevelID; if (EHierarchyLevel.db.select(dbConn, hLevel)) { empInfoRow["HLevelDesc" + (count + 1)] = hLevel.HLevelDesc; DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); empHierarchyFilter.add(new Match("HLevelID", hLevelID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); if (empHierarchyList.Count > 0) { EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0]; EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hElement)) { empInfoRow["HElementCode" + (count + 1)] = hElement.HElementCode; empInfoRow["HElementDesc" + (count + 1)] = hElement.HElementDesc; } } } } } foreach (EEmpPayroll empPayroll in empPayrollList) { if (!string.IsNullOrEmpty(empPayroll.EmpPayRemark)) { if (empInfoRow.IsRemarkNull()) { empInfoRow.Remark = empPayroll.EmpPayRemark.Trim(); } else if (string.IsNullOrEmpty(empInfoRow.Remark)) { empInfoRow.Remark = empPayroll.EmpPayRemark.Trim(); } else { empInfoRow.Remark += "\r\n" + empPayroll.EmpPayRemark.Trim(); } } } empInfoTable.Rows.Add(empInfoRow); empInfoRow.EmpID = empInfoRow.PaySlipID; DateTime LastValueDate; GeneratePayrollData(empInfoRow.PaySlipID, paymentRecords, out LastValueDate); bool hasMPF = false, hasORSO = false, hasLeaveBalance = false; if (IsShowLeaveBalance) { hasLeaveBalance = GenerateLeaveBalanceData(empInfoRow.PaySlipID, empInfo.EmpID, payPeriod.PayPeriodTo); } hasMPF = GenerateMPFData(empInfoRow.PaySlipID, mpfRecords); hasORSO = GenerateORSOData(empInfoRow.PaySlipID, orsoRecords); empInfoRow.HasMPF = hasMPF; empInfoRow.HasORSO = hasORSO; empInfoRow.HasLeaveBalance = hasLeaveBalance; if (!LastValueDate.Ticks.Equals(0)) { empInfoRow.ValueDate = LastValueDate; } } } } } System.Data.DataRow[] allowanceRows = dataSet.PaySlip.Select("PayAmount > 0", "PaymentCodeDisplaySeqNo"); foreach (System.Data.DataRow importRow in allowanceRows) { dataSet.PaySlip_Allowance.ImportRow(importRow); } System.Data.DataRow[] deductionRows = dataSet.PaySlip.Select("PayAmount < 0", "PaymentCodeDisplaySeqNo"); foreach (System.Data.DataRow importRow in deductionRows) { dataSet.PaySlip_Deduction.ImportRow(importRow); } }
protected void Delete_Click(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); ArrayList list = new ArrayList(); foreach (DataListItem item in Repeater.Items) { CheckBox c = (CheckBox)item.FindControl("DeleteItem"); HtmlInputHidden h = (HtmlInputHidden)item.FindControl("HLevelID"); if (c.Checked) { EHierarchyLevel obj = new EHierarchyLevel(); obj.HLevelID = Int32.Parse(h.Value); list.Add(obj); } } foreach (EHierarchyLevel obj in list) { EHierarchyLevel.db.select(dbConn, obj); DBFilter hierarchyLevelFilter = new DBFilter(); hierarchyLevelFilter.add(new Match("HLevelID", obj.HLevelID)); DBFilter hierarchyElementFilter = new DBFilter(); hierarchyElementFilter.add(new IN("HElementID", "Select HElementID from " + EHierarchyElement.db.dbclass.tableName, hierarchyLevelFilter)); IN inTerms = new IN("EmpPosID", "Select EmpPosID From " + EEmpHierarchy.db.dbclass.tableName, hierarchyElementFilter); DBFilter empPosFilter = new DBFilter(); empPosFilter.add(inTerms); empPosFilter.add("empid", true); ArrayList empPosList = EEmpPositionInfo.db.select(dbConn, empPosFilter); if (empPosList.Count > 0) { errors.addError(string.Format(HROne.Translation.PageErrorMessage.ERROR_CODE_USED_BY_EMPLOYEE, new string[] { HROne.Common.WebUtility.GetLocalizedString("Code"), obj.HLevelCode })); foreach (EEmpPositionInfo empPos in empPosList) { EEmpPersonalInfo empInfo = new EEmpPersonalInfo(); empInfo.EmpID = empPos.EmpID; if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { errors.addError("- " + empInfo.EmpNo + ", " + empInfo.EmpEngFullName); } else { EEmpPositionInfo.db.delete(dbConn, empPos); } } errors.addError(HROne.Translation.PageErrorMessage.ERROR_ACTION_ABORT); } else { EHierarchyElement.db.delete(dbConn, hierarchyElementFilter); EEmpHierarchy.db.delete(dbConn, hierarchyLevelFilter); WebUtils.StartFunction(Session, FUNCTION_CODE); db.delete(dbConn, obj); WebUtils.EndFunction(dbConn); } } view = loadData(info, db, Repeater); }
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 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); }
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 void btnExport_Click(object sender, EventArgs e) { ArrayList list = WebUtils.SelectedRepeaterItemToBaseObjectList(ECostAllocation.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"; //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(); CreateSummaryDataTable(dataSet); 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 (string.IsNullOrEmpty(GroupingHierarchyLevelDesc)) { GroupingHierarchyLevelID = hlevel.HLevelID; GroupingHierarchyLevelDesc = hlevel.HLevelDesc.Trim(); } hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel); } foreach (ECostAllocation obj in list) { if (ECostAllocation.db.select(dbConn, obj)) { EEmpPayroll empPayroll = new EEmpPayroll(); empPayroll.EmpPayrollID = obj.EmpPayrollID; EEmpPayroll.db.select(dbConn, empPayroll); EPayrollPeriod payPeriod = new EPayrollPeriod(); payPeriod.PayPeriodID = empPayroll.PayPeriodID; EPayrollPeriod.db.select(dbConn, payPeriod); EPayrollGroup payGroup = new EPayrollGroup(); payGroup.PayGroupID = payPeriod.PayGroupID; EPayrollGroup.db.select(dbConn, payGroup); 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); ECostCenter costCenter = new ECostCenter(); costCenter.CostCenterID = detail.CostCenterID; ECostCenter.db.select(dbConn, costCenter); int HElementID = 0; string hElementDesc = string.Empty; 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)) { // Select first hierarchy for testing if (hierarchyLevel.HLevelDesc.Equals(GroupingHierarchyLevelDesc.Trim()) && HElementID == 0) { HElementID = hierarchyElement.HElementID; hElementDesc = hierarchyElement.HElementDesc; } } } } DataTable hierarchyTable = dataSet.Tables["hierarchy"]; DataTable paymentTable = dataSet.Tables["payment"]; DataTable contributionTable = dataSet.Tables["contribution"]; DataRow[] rows = hierarchyTable.Select("CompanyID=" + company.CompanyID + " and PayGroupID=" + payGroup.PayGroupID + " and HElementID=" + HElementID + " and CostCenterID=" + detail.CostCenterID); int hierarchyRowID = 0; if (rows.Length == 0) { hierarchyRowID = hierarchyTable.Rows.Count + 1; DataRow hierarchyRow = hierarchyTable.NewRow(); hierarchyRow["ID"] = hierarchyRowID; hierarchyRow["Company"] = company.CompanyName; hierarchyRow["PayrollGroupDesc"] = payGroup.PayGroupDesc; hierarchyRow["HierarchyDesc"] = hElementDesc; hierarchyRow["CostCenterDesc"] = costCenter.CostCenterDesc; hierarchyRow["CompanyID"] = company.CompanyID; hierarchyRow["hElementID"] = HElementID; hierarchyRow["CostCenterID"] = costCenter.CostCenterID; hierarchyRow["PayGroupID"] = payGroup.PayGroupID; hierarchyTable.Rows.Add(hierarchyRow); } else { hierarchyRowID = (int)rows[0]["ID"]; } EPaymentCode paymentCode = new EPaymentCode(); paymentCode.PaymentCodeID = detail.PaymentCodeID; EPaymentCode.db.select(dbConn, paymentCode); if (!detail.CostAllocationDetailIsContribution) { if (!paymentTable.Columns.Contains(paymentCode.PaymentCodeDesc.Trim())) { paymentTable.Columns.Add(paymentCode.PaymentCodeDesc.Trim(), typeof(double)); } rows = paymentTable.Select("ID=" + hierarchyRowID); DataRow paymentRow; if (rows.Length == 0) { paymentRow = paymentTable.NewRow(); paymentRow["ID"] = hierarchyRowID; paymentTable.Rows.Add(paymentRow); } else { paymentRow = rows[0]; } if (paymentRow.IsNull(paymentCode.PaymentCodeDesc.Trim())) { paymentRow[paymentCode.PaymentCodeDesc.Trim()] = detail.CostAllocationDetailAmount; } else { paymentRow[paymentCode.PaymentCodeDesc.Trim()] = (double)paymentRow[paymentCode.PaymentCodeDesc.Trim()] + detail.CostAllocationDetailAmount; } } else { rows = contributionTable.Select("ID=" + hierarchyRowID); DataRow contributionRow; if (rows.Length == 0) { contributionRow = contributionTable.NewRow(); contributionRow["ID"] = hierarchyRowID; contributionRow["MCEE"] = 0; contributionRow["MCER"] = 0; contributionRow["VCEE"] = 0; contributionRow["VCER"] = 0; contributionRow["PFUNDEE"] = 0; contributionRow["PFUNDER"] = 0; contributionTable.Rows.Add(contributionRow); } else { contributionRow = rows[0]; } if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.MPFEmployeeMandatoryContributionPaymentType(dbConn).PaymentTypeID)) { contributionRow["MCEE"] = (double)contributionRow["MCEE"] + detail.CostAllocationDetailAmount; } else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.MPFEmployeeVoluntaryContributionPaymentType(dbConn).PaymentTypeID)) { contributionRow["VCEE"] = (double)contributionRow["VCEE"] + detail.CostAllocationDetailAmount; } else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.MPFEmployerMandatoryContributionPaymentType(dbConn).PaymentTypeID)) { contributionRow["MCER"] = (double)contributionRow["MCER"] + detail.CostAllocationDetailAmount; } else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.MPFEmployerVoluntaryContributionPaymentType(dbConn).PaymentTypeID)) { contributionRow["VCER"] = (double)contributionRow["VCER"] + detail.CostAllocationDetailAmount; } else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.PFundEmployeeContributionPaymentType(dbConn).PaymentTypeID)) { contributionRow["PFUNDEE"] = (double)contributionRow["PFUNDEE"] + detail.CostAllocationDetailAmount; } else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.PFundEmployerContributionPaymentType(dbConn).PaymentTypeID)) { contributionRow["PFUNDER"] = (double)contributionRow["PFUNDER"] + detail.CostAllocationDetailAmount; } } } } } GenerateExcelReport(dataSet, exportFileName); //export.Update(dataSet); WebUtils.TransmitFile(Response, exportFileName, "CostALlocation" + (CostAllocationStatus.SelectedValue.Equals("T") ? "Trial" : "Confirm") + "_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); Response.End(); } else { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.addError("Employee not selected"); } view = loadData(info, EEmpPayroll.db, Repeater); }
public void GenerateDetailByEmpID() { DataTable empInfoTable = dataSet.Tables["EmpInfo"]; DataTable dataTable = dataSet.Tables["PaySlip"]; foreach (EEmpPersonalInfo empInfo in EmpList) { string empDiv = string.Empty; string empDep = string.Empty; EEmpPersonalInfo.db.select(dbConn, empInfo); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, empInfo.EmpDateOfJoin, empInfo.EmpID); if (empPos == null) { empPos = new EEmpPositionInfo(); } EPosition position = new EPosition(); ECompany company = new ECompany(); position.PositionID = empPos.PositionID; EPosition.db.select(dbConn, position); company.CompanyID = empPos.CompanyID; ECompany.db.select(dbConn, company); DBFilter emphierarchyFilter = new DBFilter(); emphierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, emphierarchyFilter); foreach (EEmpHierarchy empHierarchy in empHierarchyList) { EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hElement)) { EHierarchyLevel hLevel = new EHierarchyLevel(); hLevel.HLevelID = hElement.HLevelID; if (EHierarchyLevel.db.select(dbConn, hLevel)) { if (hLevel.HLevelDesc.Equals("Division", StringComparison.CurrentCultureIgnoreCase) || hLevel.HLevelCode.Equals("DIV", StringComparison.CurrentCultureIgnoreCase)) { empDiv = hElement.HElementDesc; } } } } DBFilter basicSalaryRPFilter = new DBFilter(); basicSalaryRPFilter.add(new Match("EmpID", empInfo.EmpID)); basicSalaryRPFilter.add(new Match("EmpRPEffFr", "<=", empInfo.EmpDateOfJoin)); OR orbasicSalaryRPToTerm = new OR(); orbasicSalaryRPToTerm.add(new Match("EmpRPEffTo", ">=", empInfo.EmpDateOfJoin)); orbasicSalaryRPToTerm.add(new NullTerm("EmpRPEffTo")); basicSalaryRPFilter.add(orbasicSalaryRPToTerm); DBFilter basicSalaryPaymentCodeFilter = new DBFilter(); basicSalaryPaymentCodeFilter.add(new Match("PaymentTypeID", EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeID)); basicSalaryRPFilter.add(new IN("PayCodeID", "Select PaymentCodeID from PaymentCode", basicSalaryPaymentCodeFilter)); ArrayList recurringPayments = EEmpRecurringPayment.db.select(dbConn, basicSalaryRPFilter); EEmpRecurringPayment basicSalaryRP = new EEmpRecurringPayment(); if (recurringPayments.Count > 0) { basicSalaryRP = (EEmpRecurringPayment)recurringPayments[0]; } DBFilter payPeriodFilter = new DBFilter(); payPeriodFilter.add(new Match("PayPeriodFr", "<=", empInfo.EmpDateOfJoin)); payPeriodFilter.add(new Match("PayPeriodTo", ">=", empInfo.EmpDateOfJoin)); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("EmpID", empInfo.EmpID)); empPayrollFilter.add(new IN("PayPeriodID", "Select PayPeriodID from PayrollPeriod", payPeriodFilter)); string PayBatchInTerm = string.Empty; if (payBatchList != null) { foreach (EPayrollBatch payrollBatch in payBatchList) { if (string.IsNullOrEmpty(PayBatchInTerm)) { PayBatchInTerm = payrollBatch.PayBatchID.ToString(); } else { PayBatchInTerm += "," + payrollBatch.PayBatchID.ToString(); } } } if (!string.IsNullOrEmpty(PayBatchInTerm)) { empPayrollFilter.add(new IN("PayBatchID", PayBatchInTerm, null)); } if (PayPeriodList != null) { string payPeriodIDList = string.Empty; foreach (EPayrollPeriod payPeriod in PayPeriodList) { if (string.IsNullOrEmpty(payPeriodIDList)) { payPeriodIDList = payPeriod.PayPeriodID.ToString(); } else { payPeriodIDList += "," + payPeriod.PayPeriodID.ToString(); } } if (!string.IsNullOrEmpty(payPeriodIDList)) { empPayrollFilter.add(new IN("PayPeriodID", payPeriodIDList, null)); } } // if ReportType.Unspecify, no filter is add (for backward compatibility with previous UI) if (reportType.Equals(ReportType.TrialRun)) { empPayrollFilter.add(new Match("EmpPayStatus", "=", "T")); } else if (reportType.Equals(ReportType.History)) { empPayrollFilter.add(new Match("EmpPayStatus", "<>", "T")); } IN inEmpPayroll = new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter); DBFilter paymentRecordFilter = new DBFilter(); paymentRecordFilter.add(inEmpPayroll); paymentRecordFilter.add(new Match("PayRecStatus", "A")); paymentRecordFilter.add("PaymentCodeID", true); paymentRecordFilter.add("PayRecMethod", true); ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter); EPayrollBatch payBatch = null; DBFilter payBatchFilter = new DBFilter(); payBatchFilter.add(new IN("PayBatchID", "Select PayBatchID from EmpPayroll", empPayrollFilter)); if (!string.IsNullOrEmpty(PayBatchInTerm)) { payBatchFilter.add(new IN("PayBatchID", PayBatchInTerm, null)); } payBatchFilter.add("PayBatchValueDate", false); ArrayList payBatchs = EPayrollBatch.db.select(dbConn, payBatchFilter); if (payBatchs.Count > 0) { payBatch = (EPayrollBatch)payBatchs[0]; } DataRow empInfoRow = empInfoTable.NewRow(); empInfoRow["EmpID"] = empInfo.EmpID; empInfoRow["EmpNo"] = empInfo.EmpNo; empInfoRow["EmpName"] = empInfo.EmpEngFullName; empInfoRow["EmpChineseName"] = empInfo.EmpChiFullName; empInfoRow["EmpHKID"] = empInfo.EmpHKID; empInfoRow["EmpDateOfJoin"] = empInfo.EmpDateOfJoin; empInfoRow["CompanyName"] = company.CompanyName; empInfoRow["PositionDesc"] = position.PositionDesc; empInfoRow["BasicSalary"] = basicSalaryRP.EmpRPAmount; if (payBatch != null) { if (payBatch.PayBatchValueDate.Ticks != 0) { empInfoRow["ValueDate"] = payBatch.PayBatchValueDate; } } if (empDep.Equals(string.Empty) && empDiv.Equals(string.Empty)) { empInfoRow["Department"] = empDiv; } else if (empDiv.Equals(string.Empty)) { empInfoRow["Department"] = empDep; } else if (empDep.Equals(string.Empty)) { empInfoRow["Department"] = empDiv; } else { empInfoRow["Department"] = empDiv + " / " + empDep; } empInfoTable.Rows.Add(empInfoRow); GeneratePayrollData(empInfo.EmpID, paymentRecords); } DataRow[] allowanceRows = dataSet.Tables["PaySlip"].Select("PayAmount > 0", "PaymentCodeDisplaySeqNo"); foreach (DataRow importRow in allowanceRows) { dataSet.Tables["PaySlip_Allowance"].ImportRow(importRow); } DataRow[] deductionRows = dataSet.Tables["PaySlip"].Select("PayAmount < 0", "PaymentCodeDisplaySeqNo"); foreach (DataRow importRow in deductionRows) { dataSet.Tables["PaySlip_Deduction"].ImportRow(importRow); } }
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"); } }
public static DataTable Export(DatabaseConnection dbConn, ArrayList EmpInfoList, DateTime PeriodFrom, DateTime PeriodTo) { DataTable tmpDataTable = new DataTable("WorkingSummary$"); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); tmpDataTable.Columns.Add("EnglishName", typeof(string)); tmpDataTable.Columns.Add("ChineseName", typeof(string)); tmpDataTable.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) { tmpDataTable.Columns.Add(hlevel.HLevelDesc, typeof(string)); hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel); } tmpDataTable.Columns.Add(FIELD_AS_OF_DATE, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_REST_DAY_ENTITLED, typeof(double)); tmpDataTable.Columns.Add(FIELD_REST_DAY_TAKEN, typeof(double)); tmpDataTable.Columns.Add(FIELD_TOTAL_WORKING_DAYS, typeof(double)); tmpDataTable.Columns.Add(FIELD_TOTAL_WORKING_HOURS, typeof(double)); tmpDataTable.Columns.Add(FIELD_TOTAL_LUNCH_HOURS, typeof(double)); foreach (EEmpPersonalInfo empInfo in EmpInfoList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter empWorkingSummaryFilter = new DBFilter(); empWorkingSummaryFilter.add(new Match("EmpID", empInfo.EmpID)); empWorkingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", PeriodFrom)); empWorkingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", PeriodTo)); empWorkingSummaryFilter.add("EmpWorkingSummaryAsOfDate", true); ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, empWorkingSummaryFilter); foreach (EEmpWorkingSummary empWorkingSummary in empWorkingSummaryList) { DataRow row = tmpDataTable.NewRow(); row[FIELD_EMP_NO] = empInfo.EmpNo; row["EnglishName"] = empInfo.EmpEngFullName; row["ChineseName"] = empInfo.EmpChiFullName; DBFilter empPosFilter = new DBFilter(); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, empWorkingSummary.EmpWorkingSummaryAsOfDate, 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.HElementCode; } } } } row[FIELD_AS_OF_DATE] = empWorkingSummary.EmpWorkingSummaryAsOfDate; row[FIELD_REST_DAY_ENTITLED] = empWorkingSummary.EmpWorkingSummaryRestDayEntitled; row[FIELD_REST_DAY_TAKEN] = empWorkingSummary.EmpWorkingSummaryRestDayTaken; row[FIELD_TOTAL_WORKING_DAYS] = empWorkingSummary.EmpWorkingSummaryTotalWorkingDays; row[FIELD_TOTAL_WORKING_HOURS] = empWorkingSummary.EmpWorkingSummaryTotalWorkingHours; row[FIELD_TOTAL_LUNCH_HOURS] = empWorkingSummary.EmpWorkingSummaryTotalLunchTimeHours; tmpDataTable.Rows.Add(row); } } } return(tmpDataTable); }
public void GenerateDetailByEmpID() { //dataSet = new DataSet.Payroll_PaySlip(); //EPayrollPeriod payPeriod = new EPayrollPeriod(); //payPeriod.PayPeriodID = PayPeriodID; //EPayrollPeriod.db.select(dbConn, payPeriod); //EPayrollGroup payGroup = new EPayrollGroup(); //payGroup.PayGroupID = payPeriod.PayGroupID; //EPayrollGroup.db.select(dbConn, payGroup); //string strPayPeriod = payPeriod.PayPeriodFr.ToString("yyyy-MM-dd") + " - " + payPeriod.PayPeriodTo.ToString("yyyy-MM-dd"); DataTable empInfoTable = dataSet.Tables["EmpInfo"]; DataTable dataTable = dataSet.Tables["PaySlip"]; DataTable mpfTable = dataSet.Tables["PaySlip_MPF"]; DataTable orsoTable = dataSet.Tables["PaySlip_ORSO"]; foreach (EEmpTermination empTermination in EmpList) { string empDiv = string.Empty; string empDep = string.Empty; EEmpTermination.db.select(dbConn, empTermination); ECessationReason cessationReason = new ECessationReason(); cessationReason.CessationReasonID = empTermination.CessationReasonID; ECessationReason.db.select(dbConn, cessationReason); //DBFilter bankAccountFilter = new DBFilter(); //bankAccountFilter.add(new Match("EmpID", empInfo.EmpID)); //bankAccountFilter.add(new Match("EmpAccDefault", 1)); //ArrayList bankAccounts = EEmpBankAccount.db.select(dbConn, bankAccountFilter); //EEmpBankAccount bankAccount; //if (bankAccounts.Count > 0) // bankAccount = (EEmpBankAccount)bankAccounts[0]; //else // bankAccount = new EEmpBankAccount(); EEmpPersonalInfo empInfo = new EEmpPersonalInfo(); empInfo.EmpID = empTermination.EmpID; if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { //DBFilter positionFilter = new DBFilter(); //positionFilter.add(new Match("EmpID", empInfo.EmpID)); ////positionFilter.add(new Match("PayGroupID", payGroup.PayGroupID)); //positionFilter.add(new Match("EmpPosEffFr", "<=", empTermination.EmpTermLastDate)); //OR orPosEffToTerms = new OR(); //orPosEffToTerms.add(new Match("EmpPosEffTo", ">=", empTermination.EmpTermLastDate)); //orPosEffToTerms.add(new NullTerm("EmpPosEffTo")); //positionFilter.add(orPosEffToTerms); //ArrayList positionInfos = EEmpPositionInfo.db.select(dbConn, positionFilter); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, empTermination.EmpTermLastDate, empInfo.EmpID);// new EPosition(); if (empPos == null) { empPos = new EEmpPositionInfo(); } EPosition position = new EPosition(); ECompany company = new ECompany(); ////if (positionInfos.Count > 0) ////{ // EEmpPositionInfo empPos = (EEmpPositionInfo)positionInfos[0]; position.PositionID = empPos.PositionID; EPosition.db.select(dbConn, position); company.CompanyID = empPos.CompanyID; ECompany.db.select(dbConn, company); DBFilter emphierarchyFilter = new DBFilter(); emphierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, emphierarchyFilter); foreach (EEmpHierarchy empHierarchy in empHierarchyList) { EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hElement)) { EHierarchyLevel hLevel = new EHierarchyLevel(); hLevel.HLevelID = hElement.HLevelID; if (EHierarchyLevel.db.select(dbConn, hLevel)) { if (hLevel.HLevelDesc.Equals("Division", StringComparison.CurrentCultureIgnoreCase) || hLevel.HLevelCode.Equals("DIV", StringComparison.CurrentCultureIgnoreCase)) { empDiv = hElement.HElementDesc; } //if (hLevel.HLevelDesc.Equals("Department", StringComparison.CurrentCultureIgnoreCase) || hLevel.HLevelCode.Equals("DEP", StringComparison.CurrentCultureIgnoreCase)) // empDep = hElement.HElementDesc; } } } //} DBFilter basicSalaryRPFilter = new DBFilter(); basicSalaryRPFilter.add(new Match("EmpID", empInfo.EmpID)); basicSalaryRPFilter.add(new Match("EmpRPEffFr", "<=", empTermination.EmpTermLastDate)); OR orbasicSalaryRPToTerm = new OR(); orbasicSalaryRPToTerm.add(new Match("EmpRPEffTo", ">=", empTermination.EmpTermLastDate)); orbasicSalaryRPToTerm.add(new NullTerm("EmpRPEffTo")); basicSalaryRPFilter.add(orbasicSalaryRPToTerm); //filter.add(EmpRPEffFr,true); DBFilter basicSalaryPaymentCodeFilter = new DBFilter(); basicSalaryPaymentCodeFilter.add(new Match("PaymentTypeID", EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeID)); basicSalaryRPFilter.add(new IN("PayCodeID", "Select PaymentCodeID from PaymentCode", basicSalaryPaymentCodeFilter)); ArrayList recurringPayments = EEmpRecurringPayment.db.select(dbConn, basicSalaryRPFilter); EEmpRecurringPayment basicSalaryRP = new EEmpRecurringPayment(); if (recurringPayments.Count > 0) { basicSalaryRP = (EEmpRecurringPayment)recurringPayments[0]; } DBFilter payPeriodFilter = new DBFilter(); payPeriodFilter.add(new Match("PayPeriodTo", ">=", empTermination.EmpTermLastDate)); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("EmpID", empInfo.EmpID)); empPayrollFilter.add(new IN("PayPeriodID", "Select PayPeriodID from PayrollPeriod", payPeriodFilter)); string PayBatchInTerm = string.Empty; if (payBatchList != null) { foreach (EPayrollBatch payrollBatch in payBatchList) { if (string.IsNullOrEmpty(PayBatchInTerm)) { PayBatchInTerm = payrollBatch.PayBatchID.ToString(); } else { PayBatchInTerm += "," + payrollBatch.PayBatchID.ToString(); } } } if (!string.IsNullOrEmpty(PayBatchInTerm)) { empPayrollFilter.add(new IN("PayBatchID", PayBatchInTerm, null)); } if (PayPeriodList != null) { string payPeriodIDList = string.Empty; foreach (EPayrollPeriod payPeriod in PayPeriodList) { if (string.IsNullOrEmpty(payPeriodIDList)) { payPeriodIDList = payPeriod.PayPeriodID.ToString(); } else { payPeriodIDList += "," + payPeriod.PayPeriodID.ToString(); } } if (!string.IsNullOrEmpty(payPeriodIDList)) { empPayrollFilter.add(new IN("PayPeriodID", payPeriodIDList, null)); } } // if ReportType.Unspecify, no filter is add (for backward compatibility with previous UI) if (reportType.Equals(ReportType.TrialRun)) { empPayrollFilter.add(new Match("EmpPayStatus", "=", "T")); } else if (reportType.Equals(ReportType.History)) { empPayrollFilter.add(new Match("EmpPayStatus", "<>", "T")); } IN inEmpPayroll = new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter); DBFilter paymentRecordFilter = new DBFilter(); paymentRecordFilter.add(inEmpPayroll); paymentRecordFilter.add(new Match("PayRecStatus", "A")); paymentRecordFilter.add("PaymentCodeID", true); paymentRecordFilter.add("PayRecMethod", true); ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter); DBFilter mpfRecordFilter = new DBFilter(); mpfRecordFilter.add(inEmpPayroll); mpfRecordFilter.add("MPFRecPeriodFr", true); ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter); DBFilter orsoRecordFilter = new DBFilter(); orsoRecordFilter.add(inEmpPayroll); orsoRecordFilter.add("ORSORecPeriodFr", true); ArrayList orsoRecords = EORSORecord.db.select(dbConn, orsoRecordFilter); EPayrollBatch payBatch = null; DBFilter payBatchFilter = new DBFilter(); payBatchFilter.add(new IN("PayBatchID", "Select PayBatchID from EmpPayroll", empPayrollFilter)); if (!string.IsNullOrEmpty(PayBatchInTerm)) { payBatchFilter.add(new IN("PayBatchID", PayBatchInTerm, null)); } payBatchFilter.add("PayBatchValueDate", false); ArrayList payBatchs = EPayrollBatch.db.select(dbConn, payBatchFilter); if (payBatchs.Count > 0) { payBatch = (EPayrollBatch)payBatchs[0]; } bool hasMPF = false, hasORSO = false, hasLeaveBalance = false; if (IsShowLeaveBalance) { hasLeaveBalance = GenerateLeaveBalanceData(empInfo.EmpID, empTermination.EmpTermLastDate); } //double intALBalance = 0; //HROne.LeaveCalc.LeaveBalanceCalc calc = new HROne.LeaveCalc.LeaveBalanceCalc(empInfo.EmpID, payPeriod.PayPeriodFr.AddDays(-1)); //ArrayList leaveBalanceList = calc.getCurrentBalanceList(); //foreach (ELeaveBalance b in leaveBalanceList) //{ // if (b.LeaveTypeID.Equals(ELeaveType.ANNUAL_LEAVE_TYPE(dbConn).LeaveTypeID)) // intALBalance = b.Balance; //} hasMPF = GenerateMPFData(empInfo.EmpID, mpfRecords); hasORSO = GenerateORSOData(empInfo.EmpID, orsoRecords); DataRow empInfoRow = empInfoTable.NewRow(); empInfoRow["EmpID"] = empInfo.EmpID; empInfoRow["EmpNo"] = empInfo.EmpNo; empInfoRow["EmpName"] = empInfo.EmpEngFullName; empInfoRow["EmpChineseName"] = empInfo.EmpChiFullName; empInfoRow["EmpHKID"] = empInfo.EmpHKID; empInfoRow["EmpDateOfJoin"] = empInfo.EmpDateOfJoin; //empInfoRow["PayGroupDesc"] = payGroup.PayGroupCode + " - " + payGroup.PayGroupDesc; //empInfoRow["PayrollPeriod"] = strPayPeriod; //empInfoRow["PeriodFr"] = payPeriod.PayPeriodFr; //empInfoRow["PeriodTo"] = payPeriod.PayPeriodTo; //empInfoRow["MPFDate"] = new DateTime(payPeriod.PayPeriodTo.Year, payPeriod.PayPeriodTo.Month, 10).AddMonths(1); //empInfoRow["BankAccount"] = bankAccount.EmpBankCode + " - " + bankAccount.EmpBranchCode + "-" + bankAccount.EmpAccountNo; empInfoRow["CompanyName"] = company.CompanyName; empInfoRow["PositionDesc"] = position.PositionDesc; empInfoRow["BasicSalary"] = basicSalaryRP.EmpRPAmount; empInfoRow["EmpTermResignDate"] = empTermination.EmpTermResignDate; empInfoRow["EmpTermLastDate"] = empTermination.EmpTermLastDate; empInfoRow["CessationReasonDesc"] = cessationReason.CessationReasonDesc; empInfoRow["HasMPF"] = hasMPF; empInfoRow["HasORSO"] = hasORSO; empInfoRow["HasLeaveBalance"] = hasLeaveBalance; int YearOfServiceMonth = Convert.ToInt32(Math.Truncate(HROne.CommonLib.Utility.MonthDifference(empInfo.EmpServiceDate, empTermination.EmpTermLastDate))); empInfoRow["YearOfServiceYear"] = YearOfServiceMonth / 12; empInfoRow["YearOfServiceMonth"] = YearOfServiceMonth % 12; if (payBatch != null) { if (payBatch.PayBatchValueDate.Ticks != 0) { empInfoRow["ValueDate"] = payBatch.PayBatchValueDate; } } if (empDep.Equals(string.Empty) && empDiv.Equals(string.Empty)) { empInfoRow["Department"] = empDiv; } else if (empDiv.Equals(string.Empty)) { empInfoRow["Department"] = empDep; } else if (empDep.Equals(string.Empty)) { empInfoRow["Department"] = empDiv; } else { empInfoRow["Department"] = empDiv + " / " + empDep; } //empInfoRow["AnnualLeaveBalance"] = intALBalance; //empInfoRow["LeaveBalanceAsOfDate"] = payPeriod.PayPeriodFr.AddDays(-1); empInfoTable.Rows.Add(empInfoRow); GeneratePayrollData(empInfo.EmpID, paymentRecords); } } DataRow[] allowanceRows = dataSet.Tables["PaySlip"].Select("PayAmount > 0", "PaymentCodeDisplaySeqNo"); foreach (DataRow importRow in allowanceRows) { dataSet.Tables["PaySlip_Allowance"].ImportRow(importRow); } DataRow[] deductionRows = dataSet.Tables["PaySlip"].Select("PayAmount < 0", "PaymentCodeDisplaySeqNo"); foreach (DataRow importRow in deductionRows) { dataSet.Tables["PaySlip_Deduction"].ImportRow(importRow); } }
private static DataRow CreateWorkingSummaryTemplateRow(DatabaseConnection dbConn, DataTable dataTable, Hashtable hierarchyLevelHashTable, EEmpPersonalInfo empInfo, DateTime PeriodFrom, DateTime PeriodTo, EWorkHourPattern currentWorkHourPattern) { double restDayCount = 0; double statutoryHolidayCount = 0; double restDayTaken = 0; double statutoryHolidayTaken = 0; double workHourPatternWorkingDaysCount = 0; double workHourPatternLunchTimeCount = 0; double workHourPatternWorkingHourCount = 0; double totalWorkingDays = 0; double totalWorkingHours = 0; double totalLunchMins = 0; double totalWorkingHoursExpected = 0; double totalLateMins = 0; double totalEarlyLeaveMins = 0; double totalOvertimeMins = 0; double totalLeaveApplicationDayTaken = 0; EEmpTermination empTermination = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID); bool hasStatutoryHolidayRosterCode = false; // Check if Roster Code List contains Statutory Holiday Roster Code ArrayList rosterCodeList = ERosterCode.db.select(dbConn, new DBFilter()); foreach (ERosterCode rosterCode in rosterCodeList) { if (rosterCode.RosterCodeType.Equals(ERosterCode.ROSTERTYPE_CODE_STATUTORYHOLIDAY)) { hasStatutoryHolidayRosterCode = true; break; } } if (currentWorkHourPattern != null) { for (DateTime asOfDate = PeriodFrom; asOfDate <= PeriodTo; asOfDate = asOfDate.AddDays(1)) { if (EStatutoryHoliday.IsHoliday(dbConn, asOfDate))// && currentWorkHourPattern.WorkHourPatternUseStatutoryHolidayTable) { statutoryHolidayCount++; } //rest day must be integer and rest within 24 hour restDayCount += (1 - currentWorkHourPattern.GetDefaultDayUnit(dbConn, asOfDate, false, false)); double workDayUnit = 0; double workHourUnit = 0; double LunchTimeUnit = 0; if (empInfo.EmpDateOfJoin <= asOfDate) { workDayUnit = currentWorkHourPattern.GetDefaultDayUnit(dbConn, asOfDate, false, false); LunchTimeUnit = currentWorkHourPattern.GetDefaultLunch(dbConn, asOfDate); workHourUnit = currentWorkHourPattern.GetDefaultWorkHour(dbConn, asOfDate); DBFilter leaveAppFilter = new DBFilter(); leaveAppFilter.add(new Match("EmpID", empInfo.EmpID)); leaveAppFilter.add(new Match("LeaveAppDateFrom", "<=", asOfDate)); leaveAppFilter.add(new Match("LeaveAppDateTo", ">=", asOfDate)); leaveAppFilter.add(new Match("LeaveAppNoPayProcess", false)); ArrayList leaveAppList = ELeaveApplication.db.select(dbConn, leaveAppFilter); foreach (ELeaveApplication leaveApp in leaveAppList) { if (leaveApp.LeaveAppDateFrom.Equals(leaveApp.LeaveAppDateTo)) { workDayUnit -= leaveApp.LeaveAppDays; double currentDayDefaultDayUnit = currentWorkHourPattern.GetDefaultDayUnit(dbConn, asOfDate, false, false); if (currentDayDefaultDayUnit * leaveApp.LeaveAppDays > 0) { workHourUnit -= currentWorkHourPattern.GetDefaultWorkHour(dbConn, asOfDate) / currentDayDefaultDayUnit * leaveApp.LeaveAppDays; } } else { workDayUnit = 0; workHourUnit = 0; LunchTimeUnit = 0; } } if (workDayUnit < 0) { workDayUnit = 0; } if (workHourUnit < 0) { workHourUnit = 0; } if (workDayUnit < 1) { LunchTimeUnit = 0; } if (empTermination != null) { if (empTermination.EmpTermLastDate < asOfDate) { workDayUnit = 0; workHourUnit = 0; LunchTimeUnit = 0; } } } workHourPatternWorkingDaysCount += workDayUnit; workHourPatternWorkingHourCount += workHourUnit; workHourPatternLunchTimeCount += LunchTimeUnit; } DBFilter leaveAppTakenFilter = new DBFilter(); leaveAppTakenFilter.add(new Match("EmpID", empInfo.EmpID)); leaveAppTakenFilter.add(new Match("LeaveAppDateFrom", "<=", PeriodTo)); leaveAppTakenFilter.add(new Match("LeaveAppDateTo", ">=", PeriodFrom)); leaveAppTakenFilter.add(new Match("LeaveAppNoPayProcess", false)); ArrayList leaveAppTakenList = ELeaveApplication.db.select(dbConn, leaveAppTakenFilter); foreach (ELeaveApplication leaveApp in leaveAppTakenList) { totalLeaveApplicationDayTaken += leaveApp.LeaveAppDays; } } DBFilter attendanceRecordFilter = new DBFilter(); attendanceRecordFilter.add(new Match("EmpID", empInfo.EmpID)); attendanceRecordFilter.add(new Match("AttendanceRecordDate", ">=", PeriodFrom)); attendanceRecordFilter.add(new Match("AttendanceRecordDate", "<=", PeriodTo)); attendanceRecordFilter.add("AttendanceRecordDate", true); ArrayList attendanceRecordList = EAttendanceRecord.db.select(dbConn, attendanceRecordFilter); foreach (EAttendanceRecord attendanceRecord in attendanceRecordList) { ERosterCode rosterCode = new ERosterCode(); rosterCode.RosterCodeID = attendanceRecord.RosterCodeID; if (ERosterCode.db.select(dbConn, rosterCode)) { if (rosterCode.RosterCodeType.Equals(ERosterCode.ROSTERTYPE_CODE_RESTDAY)) { restDayTaken++; } if (rosterCode.RosterCodeType.Equals(ERosterCode.ROSTERTYPE_CODE_STATUTORYHOLIDAY)) { statutoryHolidayTaken++; } } double workingHours = attendanceRecord.TotalWorkingHourTimeSpan(dbConn).TotalHours; if (workingHours <= 0) { workingHours = attendanceRecord.AttendanceRecordActualWorkingHour + Convert.ToDouble(attendanceRecord.AttendanceRecordActualEarlyLeaveMins - attendanceRecord.AttendanceRecordActualLateMins + attendanceRecord.AttendanceRecordActualOvertimeMins) / 60.0; } totalWorkingDays += attendanceRecord.AttendanceRecordActualWorkingDay; totalWorkingHours += workingHours; totalWorkingHoursExpected += attendanceRecord.AttendanceRecordActualWorkingHour; totalLateMins += attendanceRecord.AttendanceRecordActualLateMins; totalEarlyLeaveMins += attendanceRecord.AttendanceRecordActualEarlyLeaveMins; totalOvertimeMins += attendanceRecord.AttendanceRecordActualOvertimeMins; totalLunchMins += attendanceRecord.AttendanceRecordActualLunchTimeMins; } if (totalWorkingDays <= 0 && totalWorkingHours <= 0 && totalLunchMins <= 0 && empInfo.EmpDateOfJoin <= PeriodFrom) { totalWorkingDays = workHourPatternWorkingDaysCount; totalWorkingHours = workHourPatternWorkingHourCount; totalLunchMins = workHourPatternLunchTimeCount * 60; } DataRow row = dataTable.NewRow(); row[FIELD_EMP_NO] = empInfo.EmpNo; row["EnglishName"] = empInfo.EmpEngFullName; row["ChineseName"] = empInfo.EmpChiFullName; DBFilter empPosFilter = new DBFilter(); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, PeriodTo, 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.HElementCode; } } } } row[FIELD_AS_OF_DATE] = PeriodTo; row[FIELD_REST_DAY_ENTITLED] = restDayCount - statutoryHolidayCount; if (hasStatutoryHolidayRosterCode) { row[FIELD_REST_DAY_TAKEN] = restDayTaken; } else { row[FIELD_REST_DAY_TAKEN] = restDayTaken > statutoryHolidayCount ? restDayTaken - statutoryHolidayCount : 0; } row[FIELD_TOTAL_WORKING_DAYS] = totalWorkingDays; row[FIELD_TOTAL_WORKING_HOURS] = totalWorkingHours; row[FIELD_TOTAL_LUNCH_HOURS] = totalLunchMins / 60.0; row[FIELD_EXTRA_TOTAL_STATUTORY_HOLIDAY] = statutoryHolidayCount; row[FIELD_EXTRA_TOTAL_LEAVE_APPLICATION_TAKEN] = totalLeaveApplicationDayTaken; row[FIELD_EXTRA_TOTAL_WORKING_HOURS_EXPECTED] = totalWorkingHoursExpected; row[FIELD_EXTRA_TOTAL_LATE_MINS] = totalLateMins; row[FIELD_EXTRA_TOTAL_EARLYLEAVE_MINS] = totalEarlyLeaveMins; row[FIELD_EXTRA_TOTAL_OVERTIME_MINS] = totalOvertimeMins; dataTable.Rows.Add(row); return(row); }
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); }