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 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 } }
public override CrystalDecisions.CrystalReports.Engine.ReportDocument GenerateReport() { DataSet.Payroll_NetPaymentList dataSet = new DataSet.Payroll_NetPaymentList(); if (ByAutoPay) { DataSet.Payroll_NetPaymentList.PaymentMethodRow payMethodRow = dataSet.PaymentMethod.NewPaymentMethodRow(); payMethodRow.PayMethodCode = "A"; payMethodRow.PayMethodDesc = "Autopay"; dataSet.PaymentMethod.AddPaymentMethodRow(payMethodRow); } if (ByCheque) { DataSet.Payroll_NetPaymentList.PaymentMethodRow payMethodRow = dataSet.PaymentMethod.NewPaymentMethodRow(); payMethodRow.PayMethodCode = "Q"; payMethodRow.PayMethodDesc = "Cheque"; dataSet.PaymentMethod.AddPaymentMethodRow(payMethodRow); } if (ByCash) { DataSet.Payroll_NetPaymentList.PaymentMethodRow payMethodRow = dataSet.PaymentMethod.NewPaymentMethodRow(); payMethodRow.PayMethodCode = "C"; payMethodRow.PayMethodDesc = "Cash"; dataSet.PaymentMethod.AddPaymentMethodRow(payMethodRow); } if (ByOthers) { DataSet.Payroll_NetPaymentList.PaymentMethodRow payMethodRow = dataSet.PaymentMethod.NewPaymentMethodRow(); payMethodRow.PayMethodCode = "O"; payMethodRow.PayMethodDesc = "Others"; dataSet.PaymentMethod.AddPaymentMethodRow(payMethodRow); } foreach (EEmpPersonalInfo empInfo in EmpList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DataSet.Payroll_NetPaymentList.employeedetailRow row = dataSet.employeedetail.NewemployeedetailRow(); { EEmpPositionInfo posInfo = AppUtils.GetLastPositionInfo(dbConn, empInfo.EmpID); if (posInfo != null) { ECompany company = new ECompany(); company.CompanyID = posInfo.CompanyID; if (ECompany.db.select(dbConn, company)) { row.CompanyCode = company.CompanyCode; row.CompanyID = company.CompanyID; row.CompanyName = company.CompanyName; } else { row.CompanyCode = string.Empty; row.CompanyID = 0; row.CompanyName = "-"; } row.EmpPosEffFr = posInfo.EmpPosEffFr; row.EmpPosEffTo = posInfo.EmpPosEffTo; //posInfo.EmploymentTypeID row.EmpPosID = posInfo.EmpPosID; row.LeavePlanID = posInfo.LeavePlanID; row.PayGroupID = posInfo.PayGroupID; EPosition position = new EPosition(); position.PositionID = posInfo.PositionID; if (EPosition.db.select(dbConn, position)) { row.PositionCode = position.PositionCode; row.PositionDesc = position.PositionDesc; row.PositionID = position.PositionID; } else { row.PositionCode = string.Empty; row.PositionDesc = "-"; row.PositionID = 0; } row.RankID = posInfo.RankID; row.Remark = empInfo.Remark; row.StaffTypeID = posInfo.StaffTypeID; //posInfo.YebPlanID; ArrayList HierarchyLevelList = EHierarchyLevel.db.select(dbConn, new DBFilter()); foreach (EHierarchyLevel hLevel in HierarchyLevelList) { if (hLevel.HLevelSeqNo < 4 && hLevel.HLevelSeqNo > 0) { DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", posInfo.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; EHierarchyElement.db.select(dbConn, hElement); row["HierarchyCode" + hLevel.HLevelSeqNo] = hElement.HElementCode; row["HierarchyDesc" + hLevel.HLevelSeqNo] = hElement.HElementDesc; } } } } else { row.CompanyCode = string.Empty; row.CompanyID = 0; row.CompanyName = "-"; row.PositionCode = string.Empty; row.PositionDesc = "-"; row.PositionID = 0; } } row.EmpAlias = empInfo.EmpAlias; row.EmpChiFullName = empInfo.EmpChiFullName; row.EmpCorAddr = empInfo.EmpCorAddr; row.EmpDateOfBirth = empInfo.EmpDateOfBirth; row.EmpDateOfJoin = empInfo.EmpDateOfJoin; row.EmpEmail = empInfo.EmpEmail; row.EmpEngOtherName = empInfo.EmpEngOtherName; row.EmpEngSurname = empInfo.EmpEngSurname; row.EmpEngFullName = empInfo.EmpEngFullName; row.EmpGender = empInfo.EmpGender; row.EmpHKID = empInfo.EmpHKID; row.EmpHomePhoneNo = empInfo.EmpHomePhoneNo; row.EmpID = empInfo.EmpID; row.EmpMaritalStatus = empInfo.EmpMaritalStatus; row.EmpMobileNo = empInfo.EmpMobileNo; row.EmpNationality = empInfo.EmpNationality; row.EmpNo = empInfo.EmpNo; row.EmpNoticePeriod = empInfo.EmpNoticePeriod; row.EmpNoticeUnit = empInfo.EmpNoticeUnit; row.EmpOfficePhoneNo = empInfo.EmpOfficePhoneNo; row.EmpPassportExpiryDate = empInfo.EmpPassportExpiryDate; row.EmpPassportIssuedCountry = empInfo.EmpPassportIssuedCountry; row.EmpPassportNo = empInfo.EmpPassportNo; row.EmpPlaceOfBirth = empInfo.EmpPlaceOfBirth; row.EmpProbaLastDate = empInfo.EmpProbaLastDate; row.EmpProbaPeriod = empInfo.EmpProbaPeriod; row.EmpProbaUnit = empInfo.EmpProbaUnit; row.EmpResAddr = empInfo.EmpResAddr; row.EmpResAddrAreaCode = empInfo.EmpResAddrAreaCode; row.EmpServiceDate = empInfo.EmpServiceDate; row.EmpStatus = empInfo.EmpStatus; DBFilter empTermFilter = new DBFilter(); empTermFilter.add(new Match("EmpID", empInfo.EmpID)); ArrayList empTermList = EEmpTermination.db.select(dbConn, empTermFilter); if (empTermList.Count > 0) { row.EmpTermLastDate = ((EEmpTermination)empTermList[0]).EmpTermLastDate; } dataSet.employeedetail.AddemployeedetailRow(row); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("EmpID", empInfo.EmpID)); OR orPayrollBatch = new OR(); foreach (EPayrollBatch payBatch in PayrollBatchList) { orPayrollBatch.add(new Match("PayBatchID", payBatch.PayBatchID)); } empPayrollFilter.add(orPayrollBatch); IN inEmpPayroll = new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter); DBFilter autoPayPaymentRecordFilter = new DBFilter(); autoPayPaymentRecordFilter.add(inEmpPayroll); autoPayPaymentRecordFilter.add("EmpAccID", true); ArrayList autoPayPaymentRecords = EPaymentRecord.db.select(dbConn, autoPayPaymentRecordFilter); foreach (EPaymentRecord paymentRecord in autoPayPaymentRecords) { DataSet.Payroll_NetPaymentList.PaymentRecordRow paymentRow = dataSet.PaymentRecord.NewPaymentRecordRow(); paymentRow.PayRecID = paymentRecord.PayRecID; paymentRow.EmpID = empInfo.EmpID; paymentRow.PayRecActAmount = paymentRecord.PayRecActAmount; paymentRow.PayRecMethod = paymentRecord.PayRecMethod; paymentRow.EmpAccountNo = string.Empty; EEmpPayroll empPayroll = new EEmpPayroll(); empPayroll.EmpPayrollID = paymentRecord.EmpPayrollID; if (EEmpPayroll.db.select(dbConn, empPayroll)) { if (!empPayroll.EmpPayValueDate.Ticks.Equals(0)) { paymentRow.EmpPayValueDate = empPayroll.EmpPayValueDate; } } if (paymentRecord.PayRecMethod.Equals("A", StringComparison.CurrentCultureIgnoreCase)) { // Only Autopay payment show account no EEmpBankAccount empBankAccount = new EEmpBankAccount(); empBankAccount.EmpBankAccountID = paymentRecord.EmpAccID; if (EEmpBankAccount.db.select(dbConn, empBankAccount)) { if (empBankAccount.EmpID.Equals(empInfo.EmpID)) { paymentRow.EmpAccountNo = empBankAccount.EmpBankCode + "-" + empBankAccount.EmpBranchCode + "-" + empBankAccount.EmpAccountNo; paymentRow.EmpAccID = paymentRecord.EmpAccID; } } } dataSet.PaymentRecord.AddPaymentRecordRow(paymentRow); } } } if (reportDocument == null) { reportDocument = new ReportTemplate.Report_Payroll_NetPaymentListByPaymentMethod(); } else { } reportDocument.SetDataSource(dataSet); return(reportDocument); }
public override ReportDocument GenerateReport() { string HierarchyLevel1 = string.Empty; string HierarchyLevel2 = string.Empty; string HierarchyLevel3 = string.Empty; string HierarchyLevel4 = string.Empty; if (values.Count > 0) { DataSet.EmployeeDetail ds = new DataSet.EmployeeDetail(); DataSet.EmployeeDetail.HierarchyLevelDataTable HierarchyLevelTable = ds.HierarchyLevel; ArrayList HierarchyLevelList = EHierarchyLevel.db.select(dbConn, new DBFilter()); foreach (EHierarchyLevel HierarchyLevel in HierarchyLevelList) { DataSet.EmployeeDetail.HierarchyLevelRow row = HierarchyLevelTable.NewHierarchyLevelRow(); row.HLevelCode = HierarchyLevel.HLevelCode; row.HLevelDesc = HierarchyLevel.HLevelDesc; row.HLevelID = HierarchyLevel.HLevelID; row.HLevelSeqNo = HierarchyLevel.HLevelSeqNo; HierarchyLevelTable.Rows.Add(row); } int currentDummyPosID = 0; int currentDummyHierarchyID = 0; foreach (int EmpID in values) { EmployeeDetailProcess.ImportEmployeeDetailRow(dbConn, ds.employeedetail, EmpID, AsOfDate); DBFilter empBasicSalaryfilter = new DBFilter(); DBFilter paymentCodeFilter = new DBFilter(); paymentCodeFilter.add(new Match("PaymentTypeID", EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeID)); //empBasicSalaryfilter.add(new MatchField("erp.PayCodeID", "pc.PaymentCodeID")); empBasicSalaryfilter.add(new IN("PayCodeID", "Select PaymentCodeID from PaymentCode", paymentCodeFilter)); empBasicSalaryfilter.add(new Match("EmpRPEffFr", "<=", AsOfDate)); OR orEffToFilter = new OR(); orEffToFilter.add(new Match("EmpRPEffTo", ">=", AsOfDate)); orEffToFilter.add(new NullTerm("EmpRPEffTo")); empBasicSalaryfilter.add(orEffToFilter); empBasicSalaryfilter.add(new Match("EmpID", EmpID)); DataSet.EmployeeDetail.EmpRecurringPaymentDataTable empRPTable = ds.EmpRecurringPayment; ArrayList empRecurringPaymentList = EEmpRecurringPayment.db.select(dbConn, empBasicSalaryfilter); foreach (EEmpRecurringPayment empRP in empRecurringPaymentList) { DataSet.EmployeeDetail.EmpRecurringPaymentRow row = empRPTable.NewEmpRecurringPaymentRow(); row.CurrencyID = empRP.CurrencyID; row.EmpAccID = empRP.EmpAccID; row.EmpID = empRP.EmpID; row.EmpRPAmount = Convert.ToDecimal(empRP.EmpRPAmount); row.EmpRPEffFr = empRP.EmpRPEffFr; row.EmpRPEffTo = empRP.EmpRPEffTo; row.EmpRPID = empRP.EmpRPID; row.EmpRPMethod = empRP.EmpRPMethod; row.EmpRPRemark = empRP.EmpRPRemark; row.EmpRPUnit = empRP.EmpRPUnit; EPaymentCode paymentCode = new EPaymentCode(); paymentCode.PaymentCodeID = empRP.PayCodeID; if (EPaymentCode.db.select(dbConn, paymentCode)) { row.PayCodeID = paymentCode.PaymentCodeID; row.PaymentCode = paymentCode.PaymentCode; row.PaymentCodeDesc = paymentCode.PaymentCodeDesc; row.PaymentCodeID = paymentCode.PaymentCodeID; // row.p } empRPTable.AddEmpRecurringPaymentRow(row); } { DataSet.EmployeeDetail.EmpPositionInfoDataTable empPosTable = ds.EmpPositionInfo; EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AsOfDate, EmpID); if (empPos == null) { currentDummyPosID--; DataSet.EmployeeDetail.EmpPositionInfoRow row = empPosTable.NewEmpPositionInfoRow(); row.EmpPosID = currentDummyPosID; row.EmpID = EmpID; empPosTable.AddEmpPositionInfoRow(row); DataSet.EmployeeDetail.EmpHierarchyDataTable empHierarchyTable = ds.EmpHierarchy; foreach (EHierarchyLevel hLevel in HierarchyLevelList) { currentDummyHierarchyID--; DataSet.EmployeeDetail.EmpHierarchyRow empHierarchyRow = empHierarchyTable.NewEmpHierarchyRow(); empHierarchyRow.EmpHierarchyID = currentDummyHierarchyID; empHierarchyRow.EmpID = EmpID; empHierarchyRow.EmpPosID = currentDummyPosID; empHierarchyRow.HLevelID = hLevel.HLevelID; empHierarchyRow.HLevelSeqNo = hLevel.HLevelSeqNo; empHierarchyTable.AddEmpHierarchyRow(empHierarchyRow); } } else { DataSet.EmployeeDetail.EmpPositionInfoRow row = empPosTable.NewEmpPositionInfoRow(); row.CompanyID = empPos.CompanyID; row.EmpID = empPos.EmpID; row.EmpPosEffFr = empPos.EmpPosEffFr; row.EmpPosEffTo = empPos.EmpPosEffTo; row.EmpPosID = empPos.EmpPosID; row.LeavePlanID = empPos.LeavePlanID; row.PayGroupID = empPos.PayGroupID; EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { row.PositionCode = position.PositionCode; row.PositionDesc = position.PositionDesc; row.PositionID = position.PositionID; } ERank rank = new ERank(); rank.RankID = empPos.RankID; if (ERank.db.select(dbConn, rank)) { row.RankCode = rank.RankCode; row.RankDesc = rank.RankDesc; row.RankID = rank.RankID; } row.StaffTypeID = empPos.StaffTypeID; empPosTable.AddEmpPositionInfoRow(row); DataSet.EmployeeDetail.EmpHierarchyDataTable empHierarchyTable = ds.EmpHierarchy; foreach (EHierarchyLevel hLevel in HierarchyLevelList) { if (hLevel.HLevelSeqNo.Equals(1)) { HierarchyLevel1 = hLevel.HLevelDesc; } else if (hLevel.HLevelSeqNo.Equals(2)) { HierarchyLevel2 = hLevel.HLevelDesc; } else if (hLevel.HLevelSeqNo.Equals(3)) { HierarchyLevel3 = hLevel.HLevelDesc; } else if (hLevel.HLevelSeqNo.Equals(4)) { HierarchyLevel4 = hLevel.HLevelDesc; } 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); DataSet.EmployeeDetail.EmpHierarchyRow empHierarchyRow = empHierarchyTable.NewEmpHierarchyRow(); empHierarchyRow.EmpID = EmpID; empHierarchyRow.EmpPosID = empPos.EmpPosID; empHierarchyRow.HLevelID = hLevel.HLevelID; empHierarchyRow.HLevelSeqNo = hLevel.HLevelSeqNo; if (empHierarchyList.Count > 0) { EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0]; empHierarchyRow.EmpHierarchyID = empHierarchy.EmpHierarchyID; EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = empHierarchy.HElementID; EHierarchyElement.db.select(dbConn, hElement); empHierarchyRow.HElementCode = hElement.HElementCode; empHierarchyRow.HElementDesc = hElement.HElementDesc; empHierarchyRow.HElementID = hElement.HElementID; } else { currentDummyHierarchyID--; empHierarchyRow.EmpHierarchyID = currentDummyHierarchyID; } empHierarchyTable.AddEmpHierarchyRow(empHierarchyRow); } } } } DataSet.EmployeeDetail.PayrollGroupDataTable payrollGroupTable = ds.PayrollGroup; ArrayList payrollGroupList = EPayrollGroup.db.select(dbConn, new DBFilter()); foreach (EPayrollGroup payrollGroup in payrollGroupList) { DataSet.EmployeeDetail.PayrollGroupRow row = payrollGroupTable.NewPayrollGroupRow(); row.PayGroupCode = payrollGroup.PayGroupCode; row.PayGroupDesc = payrollGroup.PayGroupDesc; row.PayGroupID = payrollGroup.PayGroupID; payrollGroupTable.Rows.Add(row); } DataSet.EmployeeDetail.PositionDataTable PositionTable = ds.Position; ArrayList PositionList = EPosition.db.select(dbConn, new DBFilter()); foreach (EPosition Position in PositionList) { DataSet.EmployeeDetail.PositionRow row = PositionTable.NewPositionRow(); row.PositionCode = Position.PositionCode; row.PositionDesc = Position.PositionDesc; row.PositionID = Position.PositionID; PositionTable.Rows.Add(row); } DataSet.EmployeeDetail.BankListDataTable BankListTable = ds.BankList; ArrayList BankListList = EBankList.db.select(dbConn, new DBFilter()); foreach (EBankList BankList in BankListList) { DataSet.EmployeeDetail.BankListRow row = BankListTable.NewBankListRow(); row.BankCode = BankList.BankCode; row.BankName = BankList.BankName; BankListTable.Rows.Add(row); } //System.Data.DataSet ds = new System.Data.DataSet(); //string select; //string from; //DBFilter filter; //OR or; //foreach (int EmpID in values) //{ //from = "from EmpPersonalInfo e LEFT JOIN EmpTermination et ON et.EmpID=e.EmpID "; //select = " e.*, et.EmpTermLastDate"; //filter = new DBFilter(); //filter.add(new Match("e.EmpID", EmpID)); //filter.loadData(ds, "employeedetail", null, select, from, null); //from = "from EmpPositionInfo ";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID"; //select = "* "; //filter = new DBFilter(); //filter.add(new Match("EmpPosEffFr", "<=", AsOfDate)); //OR orEffToFilter = new OR(); //orEffToFilter.add(new Match("EmpPosEffTo", ">=", AsOfDate)); //orEffToFilter.add(new NullTerm("EmpPosEffTo")); //filter.add(orEffToFilter); //filter.add(new Match("EmpID", EmpID)); //filter.loadData(ds, "EmpPositionInfo", null, select, from, null); //from = "from EmpRecurringPayment erp, PaymentCode pc";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID"; //select = "* "; //filter = new DBFilter(); //filter.add(new MatchField("erp.PayCodeID", "pc.PaymentCodeID")); //filter.add(new Match("pc.PaymentTypeID", EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeID)); //filter.add(new Match("erp.EmpRPEffFr", "<=", AsOfDate)); //orEffToFilter = new OR(); //orEffToFilter.add(new Match("erp.EmpRPEffTo", ">=", AsOfDate)); //orEffToFilter.add(new NullTerm("erp.EmpRPEffTo")); //filter.add(orEffToFilter); //filter.add(new Match("EmpID", EmpID)); //filter.loadData(ds, "EmpRecurringPayment", null, select, from, null); //} //from = " from PayrollGroup ";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID"; //select = "* "; //filter = new DBFilter(); //filter.loadData(ds, "PayrollGroup", null, select, from, null); //from = " from Position ";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID"; //select = "* "; //filter = new DBFilter(); //filter.loadData(ds, "Position", null, select, from, null); //from = "from EmpHierarchy eh Left Join HierarchyElement he ON eh.HElementID=he.HElementID LEFT JOIN HierarchyLevel hl on hl.HLevelID=eh.HLevelID";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID"; //select = " eh.*, he.HElementCode, he.HElementDesc, hl.HLevelSeqNo"; //filter = new DBFilter(); //filter.add("HLevelSeqNo", true); //filter.loadData(ds, "EmpHierarchy", null, select, from, null); foreach (System.Data.DataRow row in ds.Tables["EmpHierarchy"].Rows) { if (row.IsNull("HElementDesc")) { row["HElementDesc"] = "-"; } if (string.IsNullOrEmpty(row["HElementDesc"].ToString())) { row["HElementDesc"] = "-"; } } //from = "from HierarchyLevel hl ";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID"; //select = " hl.* "; //filter = new DBFilter(); //filter.add("HLevelSeqNo", true); //filter.loadData(ds, "HierarchyLevel", null, select, from, null); if (reportDocument == null) { reportDocument = new ReportTemplate.Report_Employee_SalaryList(); } else { } reportDocument.SetDataSource(ds); reportDocument.SetParameterValue("HierarchyLevel1", HierarchyLevel1); reportDocument.SetParameterValue("HierarchyLevel2", HierarchyLevel2); reportDocument.SetParameterValue("HierarchyLevel3", HierarchyLevel3); if (reportDocument.ParameterFields["HierarchyLevel4"] != null) { reportDocument.SetParameterValue("HierarchyLevel4", HierarchyLevel4); } return(reportDocument); } else { return(null); } }
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 DataSet.EmployeeDetail.employeedetailRow ImportEmployeeDetailRow(DatabaseConnection dbConn, DataSet.EmployeeDetail.employeedetailDataTable empInfoTable, int EmpID, DateTime AsOfDate) { EEmpPersonalInfo empInfo = new EEmpPersonalInfo(); empInfo.EmpID = EmpID; if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DataSet.EmployeeDetail.employeedetailRow row = empInfoTable.NewemployeedetailRow(); { EEmpPositionInfo posInfo = AppUtils.GetLastPositionInfo(dbConn, AsOfDate, EmpID); if (posInfo != null) { ECompany company = new ECompany(); company.CompanyID = posInfo.CompanyID; if (ECompany.db.select(dbConn, company)) { row.CompanyCode = company.CompanyCode; row.CompanyID = company.CompanyID; row.CompanyName = company.CompanyName; } else { row.CompanyCode = string.Empty; row.CompanyID = 0; row.CompanyName = "-"; } row.EmpPosEffFr = posInfo.EmpPosEffFr; row.EmpPosEffTo = posInfo.EmpPosEffTo; //posInfo.EmploymentTypeID row.EmpPosID = posInfo.EmpPosID; row.LeavePlanID = posInfo.LeavePlanID; row.PayGroupID = posInfo.PayGroupID; EPosition position = new EPosition(); position.PositionID = posInfo.PositionID; if (EPosition.db.select(dbConn, position)) { row.PositionCode = position.PositionCode; row.PositionDesc = position.PositionDesc; row.PositionID = position.PositionID; } else { row.PositionCode = string.Empty; row.PositionDesc = "-"; row.PositionID = 0; } row.RankID = posInfo.RankID; row.Remark = empInfo.Remark; row.StaffTypeID = posInfo.StaffTypeID; //posInfo.YebPlanID; DBFilter hLevelFilter = new DBFilter(); hLevelFilter.add("HLevelSeqNo", true); string BusinessHierarchy = string.Empty; ArrayList HierarchyLevelList = EHierarchyLevel.db.select(dbConn, hLevelFilter); foreach (EHierarchyLevel hLevel in HierarchyLevelList) { DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", posInfo.EmpPosID)); empHierarchyFilter.add(new Match("HLevelID", hLevel.HLevelID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); string currentElement = string.Empty; if (empHierarchyList.Count > 0) { EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0]; EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hElement)) { if (empInfoTable.Columns.Contains("HierarchyCode" + hLevel.HLevelSeqNo)) { row["HierarchyCode" + hLevel.HLevelSeqNo] = hElement.HElementCode; } if (empInfoTable.Columns.Contains("HierarchyDesc" + hLevel.HLevelSeqNo)) { row["HierarchyDesc" + hLevel.HLevelSeqNo] = hElement.HElementDesc; } currentElement = hElement.HElementCode; } } if (string.IsNullOrEmpty(BusinessHierarchy)) { BusinessHierarchy = currentElement; } else { BusinessHierarchy += " / " + currentElement; } } row.BusinessHierarchy = BusinessHierarchy; } else { row.CompanyCode = string.Empty; row.CompanyID = 0; row.CompanyName = "-"; row.PositionCode = string.Empty; row.PositionDesc = "-"; row.PositionID = 0; } } row.EmpAlias = empInfo.EmpAlias; row.EmpChiFullName = empInfo.EmpChiFullName; row.EmpCorAddr = empInfo.EmpCorAddr; row.EmpDateOfBirth = empInfo.EmpDateOfBirth; row.EmpDateOfJoin = empInfo.EmpDateOfJoin; row.EmpEmail = empInfo.EmpEmail; row.EmpEngOtherName = empInfo.EmpEngOtherName; row.EmpEngSurname = empInfo.EmpEngSurname; row.EmpEngFullName = empInfo.EmpEngFullName; row.EmpGender = empInfo.EmpGender; row.EmpHKID = empInfo.EmpHKID; row.EmpHomePhoneNo = empInfo.EmpHomePhoneNo; row.EmpID = empInfo.EmpID; row.EmpMaritalStatus = empInfo.EmpMaritalStatus; row.EmpMobileNo = empInfo.EmpMobileNo; row.EmpNationality = empInfo.EmpNationality; row.EmpNo = empInfo.EmpNo; row.EmpNoticePeriod = empInfo.EmpNoticePeriod; row.EmpNoticeUnit = empInfo.EmpNoticeUnit; row.EmpOfficePhoneNo = empInfo.EmpOfficePhoneNo; row.EmpPassportExpiryDate = empInfo.EmpPassportExpiryDate; row.EmpPassportIssuedCountry = empInfo.EmpPassportIssuedCountry; row.EmpPassportNo = empInfo.EmpPassportNo; row.EmpPlaceOfBirth = empInfo.EmpPlaceOfBirth; row.EmpProbaLastDate = empInfo.EmpProbaLastDate; row.EmpProbaPeriod = empInfo.EmpProbaPeriod; row.EmpProbaUnit = empInfo.EmpProbaUnit; row.EmpResAddr = empInfo.EmpResAddr; row.EmpResAddrAreaCode = empInfo.EmpResAddrAreaCode; if (row.EmpResAddrAreaCode.Equals("H")) { row.EmpResAddr += ", " + HROne.Common.WebUtility.GetLocalizedString("Hong Kong"); } else if (row.EmpResAddrAreaCode.Equals("K")) { row.EmpResAddr += ", " + HROne.Common.WebUtility.GetLocalizedString("Kowloon"); } else if (row.EmpResAddrAreaCode.Equals("N")) { row.EmpResAddr += ", " + HROne.Common.WebUtility.GetLocalizedString("New Territories"); } //else // row.EmpResAddr = ", " + HROne.Common.WebUtility.GetLocalizedString("Overseas"); row.EmpServiceDate = empInfo.EmpServiceDate; row.EmpStatus = empInfo.EmpStatus; DBFilter empTermFilter = new DBFilter(); empTermFilter.add(new Match("EmpID", EmpID)); ArrayList empTermList = EEmpTermination.db.select(dbConn, empTermFilter); if (empTermList.Count > 0) { row.EmpTermLastDate = ((EEmpTermination)empTermList[0]).EmpTermLastDate; } if (!row.IsEmpTermLastDateNull()) { row.YearOfService = Utility.YearDifference(empInfo.EmpServiceDate, row.EmpTermLastDate < AsOfDate ? row.EmpTermLastDate : AsOfDate); } else { row.YearOfService = Utility.YearDifference(empInfo.EmpServiceDate, AsOfDate); } if (row.YearOfService < 0) { row.YearOfService = 0; } empInfoTable.AddemployeedetailRow(row); return(row); } else { return(null); } }
public override ReportDocument GenerateReport() { if (EmpList.Count > 0) { string HierarchyLevel1 = " "; string HierarchyLevel2 = " "; string HierarchyLevel3 = " "; ArrayList HierarchyLevelList = EHierarchyLevel.db.select(dbConn, new DBFilter()); foreach (EHierarchyLevel hLevel in HierarchyLevelList) { if (hLevel.HLevelSeqNo.Equals(1)) { HierarchyLevel1 = hLevel.HLevelDesc; } else if (hLevel.HLevelSeqNo.Equals(2)) { HierarchyLevel2 = hLevel.HLevelDesc; } else if (hLevel.HLevelSeqNo.Equals(3)) { HierarchyLevel3 = hLevel.HLevelDesc; } } DataSet.EmployeeDetail ds = new DataSet.EmployeeDetail(); ArrayList empExtraFieldList = EEmpExtraField.db.select(dbConn, new DBFilter()); DataSet.EmployeeDetail.EmpExtraFieldDataTable empExtraFieldTable = ds.EmpExtraField; foreach (EEmpExtraField empExtraField in empExtraFieldList) { DataSet.EmployeeDetail.EmpExtraFieldRow empExtraFieldRow = empExtraFieldTable.NewEmpExtraFieldRow(); empExtraFieldRow.EmpExtraFieldGroupName = empExtraField.EmpExtraFieldGroupName; empExtraFieldRow.EmpExtraFieldID = empExtraField.EmpExtraFieldID; empExtraFieldRow.EmpExtraFieldName = empExtraField.EmpExtraFieldName; if (empExtraFieldRow.IsEmpExtraFieldGroupNameNull()) { empExtraFieldRow.EmpExtraFieldGroupName = "Extra Information"; } empExtraFieldTable.Rows.Add(empExtraFieldRow); } foreach (int EmpID in EmpList) { DataSet.EmployeeDetail.employeedetailRow empRow = ImportEmployeeDetailRow(dbConn, ds.employeedetail, EmpID, AppUtils.ServerDateTime().Date); EEmpDocument empDocument = EEmpDocument.GetProfilePhotoEmpDocument(dbConn, EmpID); if (empDocument != null) { string documentFilePath = empDocument.GetDocumentPhysicalPath(dbConn); string transferFilePath = documentFilePath; string strTmpFolder = string.Empty; if (empDocument.EmpDocumentIsCompressed) { transferFilePath = empDocument.GetExtractedFilePath(dbConn); } empRow.PhotoByteArray = ThumbnailToByteArray(transferFilePath, 200, 200); empDocument.RemoveExtractedFile(); } DBFilter empFilter = new DBFilter(); empFilter.add(new Match("EmpID", EmpID)); DBFilter empPosFilter = new DBFilter(); empPosFilter.add(new Match("EmpID", EmpID)); empPosFilter.add("EmpPosEffFr", false); ArrayList empPosList = EEmpPositionInfo.db.select(dbConn, empPosFilter); DataSet.EmployeeDetail.EmpPositionInfoDataTable empPosTable = ds.EmpPositionInfo; foreach (EEmpPositionInfo empPos in empPosList) { DataSet.EmployeeDetail.EmpPositionInfoRow row = empPosTable.NewEmpPositionInfoRow(); row.CompanyID = empPos.CompanyID; row.EmpID = empPos.EmpID; row.EmpPosEffFr = empPos.EmpPosEffFr; if (!empPos.EmpPosEffTo.Ticks.Equals(0)) { row.EmpPosEffTo = empPos.EmpPosEffTo; } row.EmpPosID = empPos.EmpPosID; row.LeavePlanID = empPos.LeavePlanID; row.PayGroupID = empPos.PayGroupID; EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { row.PositionCode = position.PositionCode; row.PositionDesc = position.PositionDesc; row.PositionID = position.PositionID; } ERank rank = new ERank(); rank.RankID = empPos.RankID; if (ERank.db.select(dbConn, rank)) { row.RankCode = rank.RankCode; row.RankDesc = rank.RankDesc; row.RankID = rank.RankID; } row.StaffTypeID = empPos.StaffTypeID; foreach (EHierarchyLevel hLevel in HierarchyLevelList) { if (hLevel.HLevelSeqNo < 4 && hLevel.HLevelSeqNo > 0) { 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; EHierarchyElement.db.select(dbConn, hElement); row["HierarchyCode" + hLevel.HLevelSeqNo] = hElement.HElementCode; row["HierarchyDesc" + hLevel.HLevelSeqNo] = hElement.HElementDesc; } } } empPosTable.AddEmpPositionInfoRow(row); } DBFilter empRPFilter = new DBFilter(); empRPFilter.add(new Match("EmpID", EmpID)); empRPFilter.add("empRPEffFr", false); ArrayList empRPList = EEmpRecurringPayment.db.select(dbConn, empRPFilter); DataSet.EmployeeDetail.EmpRecurringPaymentDataTable empRPTable = ds.EmpRecurringPayment; foreach (EEmpRecurringPayment empRP in empRPList) { DataSet.EmployeeDetail.EmpRecurringPaymentRow row = empRPTable.NewEmpRecurringPaymentRow(); row.CurrencyID = empRP.CurrencyID; row.EmpAccID = empRP.EmpAccID; row.EmpID = empRP.EmpID; row.EmpRPAmount = Convert.ToDecimal(empRP.EmpRPAmount); row.EmpRPEffFr = empRP.EmpRPEffFr; if (!empRP.EmpRPEffTo.Ticks.Equals(0)) { row.EmpRPEffTo = empRP.EmpRPEffTo; } row.EmpRPID = empRP.EmpRPID; if (empRP.EmpRPMethod.Equals("A")) { row.EmpRPMethod = HROne.Common.WebUtility.GetLocalizedString("Autopay"); } else if (empRP.EmpRPMethod.Equals("Q")) { row.EmpRPMethod = HROne.Common.WebUtility.GetLocalizedString("Cheque"); } else if (empRP.EmpRPMethod.Equals("C")) { row.EmpRPMethod = HROne.Common.WebUtility.GetLocalizedString("Cash"); } else if (empRP.EmpRPMethod.Equals("O")) { row.EmpRPMethod = HROne.Common.WebUtility.GetLocalizedString("Others"); } row.EmpRPRemark = empRP.EmpRPRemark; row.EmpRPUnit = empRP.EmpRPUnit; EPaymentCode paymentCode = new EPaymentCode(); paymentCode.PaymentCodeID = empRP.PayCodeID; if (EPaymentCode.db.select(dbConn, paymentCode)) { row.PayCodeID = paymentCode.PaymentCodeID; row.PaymentCode = paymentCode.PaymentCode; row.PaymentCodeDesc = paymentCode.PaymentCodeDesc; row.PaymentCodeID = paymentCode.PaymentCodeID; // row.p } empRPTable.AddEmpRecurringPaymentRow(row); } EmployeeBankAccountProcess.ImportEmpBankAccountRow(dbConn, ds.EmpBankAccount, EmpID); ArrayList empSpouseList = EEmpSpouse.db.select(dbConn, empFilter); DataSet.EmployeeDetail.EmpSpouseDataTable empSpouseTable = ds.EmpSpouse; foreach (EEmpSpouse empSpouse in empSpouseList) { DataSet.EmployeeDetail.EmpSpouseRow row = empSpouseTable.NewEmpSpouseRow(); row.EmpID = empSpouse.EmpID; row.EmpSpouseChineseName = empSpouse.EmpSpouseChineseName; //empSpouse.EmpSpouseDateOfBirth; row.EmpSpouseHKID = empSpouse.EmpSpouseHKID; row.EmpSpouseID = empSpouse.EmpSpouseID; row.EmpSpouseOtherName = empSpouse.EmpSpouseOtherName; row.EmpSpousePassportIssuedCountry = empSpouse.EmpSpousePassportIssuedCountry; row.EmpSpousePassportNo = empSpouse.EmpSpousePassportNo; row.EmpSpouseSurname = empSpouse.EmpSpouseSurname; // Start 0000142, KuangWei, 2014-12-21 row.EmpGender = empSpouse.EmpGender; if (empSpouse.EmpIsMedicalSchemaInsured) { row.EmpIsMedicalSchemaInsured = "Yes"; } else { row.EmpIsMedicalSchemaInsured = "No"; } row.EmpMedicalEffectiveDate = empSpouse.EmpMedicalEffectiveDate; row.EmpMedicalExpiryDate = empSpouse.EmpMedicalExpiryDate; // End 0000142, KuangWei, 2014-12-21 empSpouseTable.AddEmpSpouseRow(row); } ArrayList empDependantList = EEmpDependant.db.select(dbConn, empFilter); DataSet.EmployeeDetail.EmpDependantDataTable empDependantTable = ds.EmpDependant; foreach (EEmpDependant empDependant in empDependantList) { DataSet.EmployeeDetail.EmpDependantRow row = empDependantTable.NewEmpDependantRow(); row.EmpID = empDependant.EmpID; row.EmpDependantChineseName = empDependant.EmpDependantChineseName; //empDependant.EmpDependantDateOfBirth; row.EmpDependantGender = empDependant.EmpDependantGender; row.EmpDependantHKID = empDependant.EmpDependantHKID; row.EmpDependantID = empDependant.EmpDependantID; row.EmpDependantOtherName = empDependant.EmpDependantOtherName; row.EmpDependantPassportIssuedCountry = empDependant.EmpDependantPassportIssuedCountry; row.EmpDependantPassportNo = empDependant.EmpDependantPassportNo; row.EmpDependantRelationship = empDependant.EmpDependantRelationship; row.EmpDependantSurname = empDependant.EmpDependantSurname; empDependantTable.AddEmpDependantRow(row); } DBFilter empQualificationFilter = new DBFilter(); empQualificationFilter.add(new Match("EmpID", EmpID)); empQualificationFilter.add("EmpQualificationFrom", false); ArrayList empQualificationList = EEmpQualification.db.select(dbConn, empQualificationFilter); DataSet.EmployeeDetail.EmpQualificationDataTable empQualificationTable = ds.EmpQualification; foreach (EEmpQualification empQualification in empQualificationList) { DataSet.EmployeeDetail.EmpQualificationRow row = empQualificationTable.NewEmpQualificationRow(); row.EmpID = empQualification.EmpID; if (!empQualification.EmpQualificationFrom.Ticks.Equals(0)) { row.EmpQualificationFrom = empQualification.EmpQualificationFrom; } if (!empQualification.EmpQualificationTo.Ticks.Equals(0)) { row.EmpQualificationTo = empQualification.EmpQualificationTo; } row.EmpQualificationID = empQualification.EmpQualificationID; row.EmpQualificationInstitution = empQualification.EmpQualificationInstitution; row.EmpQualificationRemark = empQualification.EmpQualificationRemark; EQualification qualification = new EQualification(); qualification.QualificationID = empQualification.QualificationID; if (EQualification.db.select(dbConn, qualification)) { row.QualificationID = qualification.QualificationID; row.QualificationCode = qualification.QualificationCode; row.QualificationDesc = qualification.QualificationDesc; } empQualificationTable.AddEmpQualificationRow(row); } ArrayList empSkillList = EEmpSkill.db.select(dbConn, empFilter); DataSet.EmployeeDetail.EmpSkillDataTable empSkillTable = ds.EmpSkill; foreach (EEmpSkill empSkill in empSkillList) { DataSet.EmployeeDetail.EmpSkillRow row = empSkillTable.NewEmpSkillRow(); row.EmpID = empSkill.EmpID; row.EmpSkillID = empSkill.EmpSkillID; ESkill skill = new ESkill(); skill.SkillID = empSkill.SkillID; if (ESkill.db.select(dbConn, skill)) { row.SkillID = skill.SkillID; row.SkillCode = skill.SkillCode; row.SkillDesc = skill.SkillDesc; } ESkillLevel skillLevel = new ESkillLevel(); skillLevel.SkillLevelID = empSkill.SkillLevelID; if (ESkillLevel.db.select(dbConn, skillLevel)) { row.SkillLevelID = skillLevel.SkillLevelID; row.SkillLevelCode = skillLevel.SkillLevelCode; row.SkillLevelDesc = skillLevel.SkillLevelDesc; } empSkillTable.AddEmpSkillRow(row); } ArrayList empWorkExpList = EEmpWorkExp.db.select(dbConn, empFilter); DataSet.EmployeeDetail.EmpWorkExpDataTable empWorkExpTable = ds.EmpWorkExp; foreach (EEmpWorkExp empWorkExp in empWorkExpList) { DataSet.EmployeeDetail.EmpWorkExpRow row = empWorkExpTable.NewEmpWorkExpRow(); row.EmpID = empWorkExp.EmpID; row.EmpWorkExpID = empWorkExp.EmpWorkExpID; row.EmpWorkExpFromMonth = empWorkExp.EmpWorkExpFromMonth; row.EmpWorkExpFromYear = empWorkExp.EmpWorkExpFromYear; row.EmpWorkExpToYear = empWorkExp.EmpWorkExpToYear; row.EmpWorkExpToMonth = empWorkExp.EmpWorkExpToMonth; row.EmpWorkExpPosition = empWorkExp.EmpWorkExpPosition; row.EmpWorkExpCompanyName = empWorkExp.EmpWorkExpCompanyName; row.EmpWorkExpRemark = empWorkExp.EmpWorkExpRemark; empWorkExpTable.AddEmpWorkExpRow(row); } ArrayList empExtraFieldValueList = EEmpExtraFieldValue.db.select(dbConn, empFilter); DataSet.EmployeeDetail.EmpExtraFieldValueDataTable empExtraFieldValueTable = ds.EmpExtraFieldValue; foreach (EEmpExtraFieldValue empExtraFieldValue in empExtraFieldValueList) { DataSet.EmployeeDetail.EmpExtraFieldValueRow row = empExtraFieldValueTable.NewEmpExtraFieldValueRow(); row.EmpID = empExtraFieldValue.EmpID; row.EmpExtraFieldValueID = empExtraFieldValue.EmpExtraFieldValueID; row.EmpExtraFieldID = empExtraFieldValue.EmpExtraFieldID; row.EmpExtraFieldValue = empExtraFieldValue.EmpExtraFieldValue; empExtraFieldValueTable.AddEmpExtraFieldValueRow(row); } //DBFilter filter = new DBFilter(); //filter.add(new Match("P.EmpID", EmpID)); //select = "P.*,EmpPos.*,Pos.*"; //from = "from EmpPersonalInfo P LEFT JOIN EmpPositionInfo EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID"; //filter.loadData(ds, "employeedetail", null, select, from, null); //select = "P.*,Pos.PositionCode,Pos.PositionDesc,R.RankCode, R.RankDesc"; //from = "from EmpPositionInfo P LEFT JOIN Position Pos ON P.PositionID=Pos.PositionID LEFT JOIN Rank R ON P.RankID=R.RankID"; //filter.loadData(ds, "EmpPositionInfo", null, select, from, null); //select = "P.*,C.*"; //from = "from EmpRecurringPayment P LEFT JOIN PaymentCode C ON P.PayCodeID=C.PaymentCodeID "; //filter.loadData(ds, "EmpRecurringPayment", null, select, from, null); //select = "P.*"; //from = "from EmpBankAccount P "; //filter.loadData(ds, "EmpBankAccount", null, select, from, null); //select = "P.*"; //from = "from EmpSpouse P "; //filter.loadData(ds, "EmpSpouse", null, select, from, null); //select = "P.*"; //from = "from EmpDependant P "; //filter.loadData(ds, "EmpDependant", null, select, from, null); //select = "P.*, Q.*"; //from = "from EmpQualification P LEFT JOIN Qualification Q on P.QualificationID=Q.QualificationID"; //filter.loadData(ds, "EmpQualification", null, select, from, null); //select = "P.*, S.*,L.*"; //from = "from EmpSkill P LEFT JOIN Skill S ON P.SkillID=S.SkillID LEFT JOIN SkillLevel L on P.SkillLevelID=L.SkillLevelID"; //filter.loadData(ds, "EmpSkill", null, select, from, null); } //DBAESEncryptStringFieldAttribute.decode(ds.Tables["employeedetail"], "EmpHKID", true); //DBAESEncryptStringFieldAttribute.decode(ds.Tables["employeedetail"], "EmpPassportNo", false); //DBAESEncryptStringFieldAttribute.decode(ds.Tables["employeedetail"], "EmpResAddr", true); //DBAESEncryptStringFieldAttribute.decode(ds.Tables["employeedetail"], "EmpCorAddr", true); //DBAESEncryptStringFieldAttribute.decode(ds.Tables["EmpSpouse"], "EmpSpouseHKID", false); //DBAESEncryptStringFieldAttribute.decode(ds.Tables["EmpSpouse"], "EmpSpousePassportNo", false); //DBAESEncryptStringFieldAttribute.decode(ds.Tables["EmpDependant"], "EmpDependantHKID", false); //DBAESEncryptStringFieldAttribute.decode(ds.Tables["EmpDependant"], "EmpDependantPassportNo", false); if (reportDocument == null) { reportDocument = new ReportTemplate.Report_Employee_Detail(); } else { } reportDocument.SetDataSource(ds); reportDocument.SetParameterValue("HierarchyLevel1", HierarchyLevel1); reportDocument.SetParameterValue("HierarchyLevel2", HierarchyLevel2); reportDocument.SetParameterValue("HierarchyLevel3", HierarchyLevel3); reportDocument.SetParameterValue("HierarchyLevel1", HierarchyLevel1, "PositionInfo"); reportDocument.SetParameterValue("HierarchyLevel2", HierarchyLevel2, "PositionInfo"); reportDocument.SetParameterValue("HierarchyLevel3", HierarchyLevel3, "PositionInfo"); return(reportDocument); } else { return(null); } }
protected void Save_Click(object sender, EventArgs e) { EEmpPositionInfo newEmpPos = new EEmpPositionInfo(); Hashtable values = new Hashtable(); empPosBinding.toValues(values); PageErrors errors = PageErrors.getErrors(empPosDB, Page); errors.clear(); empPosDB.validate(errors, values); if (!errors.isEmpty()) { return; } empPosDB.parse(values, newEmpPos); int OldEmpPosID = newEmpPos.EmpPosID; EEmpPersonalInfo newEmpInfo = new EEmpPersonalInfo(); values = new Hashtable(); empBinding.toValues(values); errors = PageErrors.getErrors(empDB, Page); errors.clear(); empDB.validate(errors, values); if (!errors.isEmpty()) { return; } empDB.parse(values, newEmpInfo); int OldEmpID = newEmpInfo.EmpID; newEmpInfo.EmpID = 0; newEmpInfo.EmpNo = newEmpInfo.EmpNo.ToUpper(); if (!AppUtils.checkDuplicate(dbConn, empDB, newEmpInfo, errors, "EmpNo")) { return; } DBFilter empTermFilter = new DBFilter(); empTermFilter.add(new Match("EmpID", OldEmpID)); empTermFilter.add(WebUtils.AddRankFilter(Session, "EmpID", true)); ArrayList empTermList = EEmpTermination.db.select(dbConn, empTermFilter); if (empTermList.Count == 0) { errors.addError(HROne.Translation.PageErrorMessage.ERROR_TERMINATION_RECORD_NOT_FOUND); return; } EEmpTermination empTerm = (EEmpTermination)empTermList[0]; if (!empTerm.EmpTermIsTransferCompany) { errors.addError(HROne.Translation.PageErrorMessage.ERROR_EMP_NOT_TRANSFERABLE); return; } if (empTerm.NewEmpID >= 0) { EEmpPersonalInfo NextEmpInfo = new EEmpPersonalInfo(); NextEmpInfo.EmpID = empTerm.NewEmpID; if (EEmpPersonalInfo.db.select(dbConn, NextEmpInfo)) { errors.addError(string.Format(HROne.Translation.PageErrorMessage.ERROR_EMP_TRANSFERRED_BEFORE, new string[] { NextEmpInfo.EmpNo })); return; } } empTerm.NewEmpID = CopyEmployeeDetail(OldEmpID); EEmpTermination.db.update(dbConn, empTerm); newEmpInfo.EmpID = empTerm.NewEmpID; newEmpInfo.EmpStatus = "A"; newEmpInfo.EmpDateOfJoin = empTerm.EmpTermLastDate.AddDays(1); newEmpInfo.PreviousEmpID = OldEmpID; EEmpPersonalInfo.db.update(dbConn, newEmpInfo); EEmpPositionInfo oldEmpPos = new EEmpPositionInfo(); oldEmpPos.EmpPosID = OldEmpPosID; if (EEmpPositionInfo.db.select(dbConn, oldEmpPos)) { //oldEmpPos.EmpPosEffTo = empTerm.EmpTermLastDate; EEmpPositionInfo.db.update(dbConn, oldEmpPos); } newEmpPos.EmpPosEffFr = empTerm.EmpTermLastDate.AddDays(1); newEmpPos.EmpID = newEmpInfo.EmpID; newEmpPos.EmpPosID = 0; EEmpPositionInfo.db.insert(dbConn, newEmpPos); ArrayList list = new ArrayList(); foreach (RepeaterItem item in HierarchyLevel.Items) { DropDownList d = (DropDownList)item.FindControl("HElementID"); int HLevelID = Int32.Parse(d.Attributes["HLevelID"]); if (d.SelectedIndex > 0) { EEmpHierarchy h = new EEmpHierarchy(); h.EmpID = newEmpInfo.EmpID; h.EmpPosID = newEmpPos.EmpPosID; h.HLevelID = HLevelID; h.HElementID = Int32.Parse(d.SelectedValue); list.Add(h); } } DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpID", newEmpInfo.EmpID)); empHierarchyFilter.add(new Match("EmpPosID", newEmpPos.EmpPosID)); EEmpHierarchy.db.delete(dbConn, empHierarchyFilter); foreach (EEmpHierarchy h in list) { EEmpHierarchy.db.insert(dbConn, h); } HROne.LeaveCalc.LeaveBalanceCalc bal = new HROne.LeaveCalc.LeaveBalanceCalc(dbConn, OldEmpID, empTerm.EmpTermLastDate); ArrayList balList = bal.getCurrentBalanceList(); foreach (ELeaveBalance leaveBalance in balList) { ELeaveBalanceAdjustment leaveAdj = new ELeaveBalanceAdjustment(); leaveAdj.EmpID = newEmpInfo.EmpID; leaveAdj.LeaveBalAdjDate = empTerm.EmpTermLastDate; leaveAdj.LeaveBalAdjType = ELeaveBalanceAdjustment.ADJUST_TYPE_RESET_BALANCE; leaveAdj.LeaveTypeID = leaveBalance.LeaveTypeID; leaveAdj.LeaveBalAdjValue = leaveBalance.getBalance(); leaveAdj.LeaveBalAdjRemark = "System adjustment by transfer company"; ELeaveBalanceAdjustment.db.insert(dbConn, leaveAdj); } HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "Emp_PositionInfo_View.aspx?EmpPosID=" + newEmpPos.EmpPosID + "&EmpID=" + newEmpInfo.EmpID); }
public bool Save() { EEmpPositionInfo c = new EEmpPositionInfo(); Hashtable values = new Hashtable(); binding.toValues(values); PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); db.validate(errors, values); if (!errors.isEmpty()) { return(false); } db.parse(values, c); //DBFilter filter = new DBFilter(); //filter.add(new Match("EmpID", CurEmpID)); //if (db.count(dbConn, filter) == 0) //{ // EEmpPersonalInfo pi = new EEmpPersonalInfo(); // pi.EmpID = CurEmpID; // EEmpPersonalInfo.db.select(dbConn, pi); // c.EmpPosEffFr = pi.EmpServiceDate; //} if (c.EmpPosEffTo > DateTime.MinValue && c.EmpPosEffTo < c.EmpPosEffFr) { errors.addError("EmpPosEffTo", HROne.Translation.PageErrorMessage.ERROR_DATE_TO_TOO_EARLY); } AND andTerms = new AND(); andTerms.add(new Match("EmpPosID", "<>", c.EmpPosID)); andTerms.add(new Match("EmpPosEffFr", "<=", c.EmpPosEffFr)); EEmpPositionInfo lastObj = (EEmpPositionInfo)AppUtils.GetLastObj(dbConn, db, "EmpPosEffFr", c.EmpID, andTerms); if (lastObj != null && (c.EmpPosEffFr <= lastObj.EmpPosEffTo || c.EmpPosEffFr == lastObj.EmpPosEffFr)) { errors.addError("EmpPosEffFr", HROne.Translation.PageErrorMessage.ERROR_DATE_FROM_OVERLAP); } DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", c.EmpID)); filter.add(new Match("EmpPosID", "<>", c.EmpPosID)); OR or = new OR(); AND and; and = new AND(); and.add(new Match("EmpPosEffFr", "<=", c.EmpPosEffFr)); and.add(new Match("EmpPosEffTo", ">=", c.EmpPosEffFr)); or.add(and); // do not allow early terms without "TO" date if (c.EmpPosEffTo.Ticks.Equals(0)) { or.add(new Match("EmpPosEffFr", ">", c.EmpPosEffFr)); } if (c.EmpPosEffTo > DateTime.MinValue) { and = new AND(); and.add(new Match("EmpPosEffFr", "<=", c.EmpPosEffTo)); and.add(new Match("EmpPosEffTo", ">=", c.EmpPosEffTo)); or.add(and); and = new AND(); and.add(new Match("EmpPosEffFr", ">=", c.EmpPosEffFr)); and.add(new Match("EmpPosEffFr", "<=", c.EmpPosEffTo)); or.add(and); } filter.add(or); if (db.count(dbConn, filter) > 0) { errors.addError(HROne.Translation.PageErrorMessage.ERROR_POS_OVERLAP); } if (!errors.isEmpty()) { return(false); } WebUtils.StartFunction(Session, FUNCTION_CODE, c.EmpID); if (lastObj != null) { if (lastObj.EmpPosEffTo.Ticks.Equals(0)) { if (lastObj.EmpPosEffFr <= c.EmpPosEffFr.AddDays(-1)) { lastObj.EmpPosEffTo = c.EmpPosEffFr.AddDays(-1); db.update(dbConn, lastObj); } else { if (c.EmpPosEffTo.Ticks.Equals(0)) { errors.addError(HROne.Translation.PageErrorMessage.ERROR_POS_OVERLAP); } } } } else { EEmpPositionInfo prev = AppUtils.GetLastPositionInfo(dbConn, EmpID); if (prev != null && prev.EmpPosID != EmpPosID) { if (prev.EmpPosEffTo.Ticks.Equals(0)) { if (prev.EmpPosEffFr <= c.EmpPosEffFr.AddDays(-1)) { prev.EmpPosEffTo = c.EmpPosEffFr.AddDays(-1); db.update(dbConn, prev); } else if (c.EmpPosEffTo.Ticks.Equals(0)) { errors.addError(HROne.Translation.PageErrorMessage.ERROR_POS_OVERLAP); } } } } if (!errors.isEmpty()) { WebUtils.EndFunction(dbConn); return(false); } if (c.EmpPosID <= 0) { // Utils.MarkCreate(Session, c); db.insert(dbConn, c); EmpPosID = c.EmpPosID; // url = Utils.BuildURL(-1, CurID); } else { // Utils.Mark(Session, c); db.update(dbConn, c); } ArrayList list = new ArrayList(); foreach (RepeaterItem item in HierarchyLevel.Items) { DropDownList d = (DropDownList)item.FindControl("HElementID"); int HLevelID = Int32.Parse(d.Attributes["HLevelID"]); filter = new DBFilter(); filter.add(new Match("EmpPosID", EmpPosID)); filter.add(new Match("HLevelID", HLevelID)); ArrayList existingHierarchyList = EEmpHierarchy.db.select(dbConn, filter); EEmpHierarchy h = null; if (existingHierarchyList.Count > 0) { h = (EEmpHierarchy)existingHierarchyList[0]; } else { h = new EEmpHierarchy(); h.EmpPosID = EmpPosID; h.HLevelID = HLevelID; } h.EmpID = EmpID; if (d.SelectedIndex == 0) { h.HElementID = 0; } else { h.HElementID = Int32.Parse(d.SelectedValue); } list.Add(h); } //filter = new DBFilter(); //filter.add(new Match("EmpID", CurEmpID)); //filter.add(new Match("EmpPosID", CurID)); //EEmpHierarchy.db.delete(dbConn, filter); foreach (EEmpHierarchy h in list) { if (h.EmpHierarchyID == 0) { EEmpHierarchy.db.insert(dbConn, h); } else { EEmpHierarchy.db.update(dbConn, h); } } WebUtils.EndFunction(dbConn); return(true); }
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); } }