protected bool loadObject() { obj = new ERosterTableGroup(); bool isNew = WebFormWorkers.loadKeys(db, obj, DecryptedRequest); if (!db.select(dbConn, obj)) { return(false); } if (!IsPostBack) { DBFilter rosterClientSiteFilter = new DBFilter(); rosterClientSiteFilter.add(new Match("RosterClientID", obj.RosterClientID)); binding.add(new DropDownVLBinder(db, RosterClientSiteID, ERosterClientSite.VLRosterClientSite, rosterClientSiteFilter)); binding.init(Request, Session); } Hashtable values = new Hashtable(); db.populate(obj, values); binding.toControl(values); return(true); }
protected void Save_Click(object sender, EventArgs e) { ERosterTableGroup c = new ERosterTableGroup(); 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, "RosterTableGroupCode")) { return; } WebUtils.StartFunction(Session, FUNCTION_CODE); if (CurID < 0) { // Utils.MarkCreate(Session, c); db.insert(dbConn, c); CurID = c.RosterTableGroupID; // url = Utils.BuildURL(-1, CurID); } else { // Utils.Mark(Session, c); db.update(dbConn, c); } WebUtils.EndFunction(dbConn); HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "Attendance_RosterTableGroup_View.aspx?RosterTableGroupID=" + CurID); }
protected void Delete_Click(object sender, EventArgs e) { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); ERosterTableGroup o = new ERosterTableGroup(); o.RosterTableGroupID = CurID; if (ERosterTableGroup.db.select(dbConn, o)) { DBFilter empPosFilter = new DBFilter(); empPosFilter.add(new Match("RosterTableGroupID", o.RosterTableGroupID)); 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("Roster Table Group"), o.RosterTableGroupCode })); 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); return; } else { WebUtils.StartFunction(Session, FUNCTION_CODE); db.delete(dbConn, o); WebUtils.EndFunction(dbConn); } } HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "Attendance_RosterTableGroup_List.aspx"); }
protected bool loadObject() { obj = new ERosterTableGroup(); bool isNew = WebFormWorkers.loadKeys(db, obj, DecryptedRequest); if (!db.select(dbConn, obj)) { return(false); } Hashtable values = new Hashtable(); db.populate(obj, values); binding.toControl(values); return(true); }
protected int BuildCommonRosterCodeAndTermsFilter() { if (selectedRosterTableGroupID > 0) { DBFilter groupFilter = new DBFilter(); groupFilter.add(new Match("RosterTableGroupID", selectedRosterTableGroupID)); ERosterTableGroup group = (ERosterTableGroup)GetFirstItem(ERosterTableGroup.db.select(dbConn, groupFilter)); if (group != null) { hiddenRosterTableGroupID.Value = group.RosterTableGroupID.ToString(); hiddenRosterClientID.Value = group.RosterClientID.ToString(); hiddenRosterClientSiteID.Value = group.RosterClientSiteID.ToString(); return(group.RosterTableGroupID); } } hiddenRosterTableGroupID.Value = "0"; hiddenRosterClientID.Value = "0"; hiddenRosterClientSiteID.Value = "0"; return(-1); }
public static DataTable Export(DatabaseConnection dbConn, ArrayList empList, bool IsIncludeCurrentPositionInfo, bool IsShowDescription, bool IsIncludeSyncID, DateTime ReferenceDateTime) { DataTable tmpDataTable = new DataTable(TABLE_NAME); //if (IsIncludeInternalID) // tmpDataTable.Columns.Add(FIELD_INTERNAL_ID, typeof(string)); tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string)); if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfoHeader(tmpDataTable); ImportEmpPositionInfoProcess.AddEmployeePositionInfoHeader(dbConn, tmpDataTable); } tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime)); tmpDataTable.Columns.Add(FIELD_ROSTERTABLEGROUP, typeof(string)); tmpDataTable.Columns.Add(FIELD_ROSTERTABLEGROUP_IS_SUPERVISOR, typeof(string)); if (IsIncludeSyncID) { tmpDataTable.Columns.Add(FIELD_SYNC_ID, typeof(string)); } foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { DBFilter filter = new DBFilter(); filter.add(new Match("EmpID", empInfo.EmpID)); filter.add(getCreateModifiedRecordsAfterDBTerm(ReferenceDateTime)); ArrayList list = EEmpRosterTableGroup.db.select(dbConn, filter); foreach (EEmpRosterTableGroup EmpRosterTableGroup in list) { DataRow row = tmpDataTable.NewRow(); //if (IsIncludeInternalID) // row[FIELD_INTERNAL_ID] = ToHexDecWithCheckDigit(EmpRosterTableGroup.EmpRosterTableGroupID); row[FIELD_EMP_NO] = empInfo.EmpNo; if (IsIncludeCurrentPositionInfo) { ImportEmpPersonalInfoProcess.AddEmployeeInfo(dbConn, row, empInfo.EmpID); ImportEmpPositionInfoProcess.AddEmployeePositionInfo(dbConn, row, empInfo.EmpID); } row[FIELD_FROM] = EmpRosterTableGroup.EmpRosterTableGroupEffFr; row[FIELD_TO] = EmpRosterTableGroup.EmpRosterTableGroupEffTo; ERosterTableGroup rosterTableGroup = new ERosterTableGroup(); rosterTableGroup.RosterTableGroupID = EmpRosterTableGroup.RosterTableGroupID; if (ERosterTableGroup.db.select(dbConn, rosterTableGroup)) { row[FIELD_ROSTERTABLEGROUP] = IsShowDescription ? rosterTableGroup.RosterTableGroupDesc : rosterTableGroup.RosterTableGroupCode; } row[FIELD_ROSTERTABLEGROUP_IS_SUPERVISOR] = EmpRosterTableGroup.EmpRosterTableGroupIsSupervisor ? "Yes" : "No"; //row[FIELD_IS_ROSTER_TABLE_GROUP_SUPERVISOR] = empPositionInfo.EmpPosIsRosterTableGroupSupervisor ? "Yes" : "No"; if (IsIncludeSyncID) { row[FIELD_SYNC_ID] = EmpRosterTableGroup.SynID; } tmpDataTable.Rows.Add(row); } } } if (IsIncludeCurrentPositionInfo) { ImportEmpPositionInfoProcess.RetriveHierarchyLevelHeader(dbConn, tmpDataTable); } return(tmpDataTable); }
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(); }