private void AddSubTotalRow(NPOI.HSSF.UserModel.HSSFSheet excelWorksheet, string currentCompany, int recordCount, int lastRowIndex, int lastColumnIndex, NPOI.HSSF.UserModel.HSSFRow headerRow, NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle)
{
//execlWorksheet.Cells[lastRowIndex, PAYMENTDETAIL_START_COLUMN - 1].Value = "Subtotal - " + currentCompany;
NPOI.HSSF.UserModel.HSSFRow subTotalRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
NPOI.HSSF.UserModel.HSSFCell subTotalCell = (NPOI.HSSF.UserModel.HSSFCell)subTotalRow.CreateCell(0);
subTotalCell.SetCellValue("Subtotal - " + currentCompany);
subTotalCell.CellStyle = subTotalStyle;
excelWorksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(lastRowIndex, lastRowIndex, 0, PAYMENTDETAIL_START_COLUMN - 1));
for (int iCol = PAYMENTDETAIL_START_COLUMN; iCol <= lastColumnIndex; iCol++)
{
//double totalPayment = 0;
//bool IsTotalPaymentNull = true;
//for (int iRow = lastRowIndex - recordCount; iRow < lastRowIndex; iRow++)
//{
// ExcelLibrary.SpreadSheet.Cell cell = execlWorksheet.Cells[iRow, iCol];
// if (cell.Value is double)
// {
// IsTotalPaymentNull = false;
// totalPayment += (double)cell.Value;
// }
//}
//if (!IsTotalPaymentNull)
//{
// ExcelLibrary.SpreadSheet.Cell totalCell = execlWorksheet.Cells[lastRowIndex, iCol];
// totalCell.FormatString = "#,##0.00";
// totalCell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(totalPayment, 2, 2);
//}
if (headerRow.GetCell(iCol) != null)
{
subTotalCell = (NPOI.HSSF.UserModel.HSSFCell)subTotalRow.CreateCell(iCol);
subTotalCell.CellFormula = "SUM(" + ToCellString(lastRowIndex - recordCount, iCol) + ":" + ToCellString(lastRowIndex - 1, iCol) + ")";
subTotalCell.CellStyle = subTotalStyle;
}
}
}
private static void CopyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, IDictionary<Int32, HSSFCellStyle> styleMap, Dictionary<short, short> paletteMap, bool keepFormulas)
{
List<SS.Util.CellRangeAddress> mergedRegions = destSheet.Sheet.MergedRecords.MergedRegions;
destRow.Height = srcRow.Height;
destRow.IsHidden = srcRow.IsHidden;
destRow.RowRecord.OptionFlags = srcRow.RowRecord.OptionFlags;
for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
{
HSSFCell oldCell = (HSSFCell)srcRow.GetCell(j);
HSSFCell newCell = (HSSFCell)destRow.GetCell(j);
if (srcSheet.Workbook == destSheet.Workbook)
{
newCell = (HSSFCell)destRow.GetCell(j);
}
if (oldCell != null)
{
if (newCell == null)
{
newCell = (HSSFCell)destRow.CreateCell(j);
}
HSSFCellUtil.CopyCell(oldCell, newCell, styleMap, paletteMap, keepFormulas);
CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum, (short)oldCell.ColumnIndex);
if (mergedRegion != null)
{
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
if (IsNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.Add(newMergedRegion);
}
}
}
}
}
/// <summary>
/// 利用office组件导出Excel表格文件
/// </summary>
//public static void ExportExcelFile(DataTable ExcelTable)
//{
// try
// {
// string SaveExcelName = string.Empty;//保存的Excel文件名称
// SaveFileDialog SFDialog = new SaveFileDialog();
// SFDialog.DefaultExt = "xls";
// SFDialog.Filter = "Excel文件(*.xls)|*.xls";
// SFDialog.ShowDialog();
// SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
// if (SaveExcelName.IndexOf(":") < 0) return;
// Microsoft.Office.Interop.Excel.Application XlsApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel应用程序
// object missing = System.Reflection.Missing.Value;
// if (XlsApp == null)
// {
// MessageBoxEx.Show("无法创建Excel表格文件，您的电脑可能未安装Excel软件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
// }
// else
// {
// Microsoft.Office.Interop.Excel.Workbooks WkBks = XlsApp.Workbooks;//获取工作簿对像
// Microsoft.Office.Interop.Excel.Workbook WkBk = WkBks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//添加Excel表格模板
// Microsoft.Office.Interop.Excel.Worksheet WkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WkBk.Worksheets[1];//获取工作表格1;
// Microsoft.Office.Interop.Excel.Range Ran;//声明Excel表格
// int TotalCount = ExcelTable.Rows.Count;
// //int rowRead = 0;//读取行数
// //float PercentRead = 0;//导入百分比
// //写入字段名
// for (int i = 0; i < ExcelTable.Columns.Count; i++)
// {
// WkSheet.Cells[1, i + 1] = ExcelTable.Columns[i].ColumnName.ToString();//获取表列名称
// Ran = (Microsoft.Office.Interop.Excel.Range)WkSheet.Cells[1, i + 1];//列名称写入单元格
// Ran.Interior.ColorIndex = 15;
// Ran.Font.Bold = true;//标题加粗
// }
// //ProgressBarMsg ProgBarMsg = new ProgressBarMsg();
// //ProgBarMsg.MaxNum = TotalCount;//获取总记录行项
// //ProgBarMsg.ShowDialog();//显示进度条
// //写字段值
// for (int j = 0; j < ExcelTable.Rows.Count; j++)
// {
// for (int k = 0; k < ExcelTable.Columns.Count; k++)
// {
// WkSheet.Cells[j + 2, k + 1] = ExcelTable.Rows[j][k].ToString();//写表格值
// }
// //rowRead++;
// //PercentRead = ((float)rowRead * 100) / TotalCount;//导入进度百分比
// //ProgressBarMsg.PercentMsg = rowRead;
// //Thread.Sleep(200);
// Application.DoEvents();//处理当前在消息队列中所有windows消息
// }
// WkSheet.SaveAs(SaveExcelName, missing, missing, missing, missing, missing, missing, missing, missing);
// Ran = WkSheet.get_Range((object)WkSheet.Cells[2, 1], (object)WkSheet.Cells[ExcelTable.Rows.Count + 1, ExcelTable.Columns.Count]);//给工作表指定区域
// //设置Excel表格边框样式
// Ran.BorderAround2(missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
// Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, missing, missing);
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;//设置区域边框颜色
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//连续边框
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框浓度
// if (ExcelTable.Columns.Count > 1)
// {//设置垂直表格颜色索引
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
// }
// WkBk.Save();//保存Excel表数据
// //关闭表格对像，并退出应用程序域
// WkBk.Close(missing, missing, missing);
// XlsApp.Quit();
// XlsApp = null;
// GC.Collect();//强制关闭
// MessageBoxEx.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// }
// }
// catch (Exception ex)
// {
// MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
// }
//}
/// <summary>
/// 设置导出Excel标题样式
/// </summary>
/// <param name="HeaderRow">标题行</param>
/// <param name="WkBk">工作表</param>
/// <param name="ColNum">列序号</param>
private static void SetXlsHeaderStyle(HSSFRow HeaderRow, HSSFWorkbook WkBk, int ColNum)
{
try
{
HSSFCellStyle HeaderStyle = (HSSFCellStyle)WkBk.CreateCellStyle();//创建Excel表格标题样式
HSSFFont fontStyle = (HSSFFont)WkBk.CreateFont();//创建Excel表格字体样式
HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//设置居中
HeaderStyle.FillForegroundColor = HSSFColor.Lime.Index;//设置背景色
HeaderStyle.FillPattern = FillPattern.SolidForeground;//设置填充样式
HeaderStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0");//设置数据格式
fontStyle.FontHeightInPoints = 11;
fontStyle.Boldweight = 700;
HeaderStyle.SetFont(fontStyle);
HeaderRow.GetCell(ColNum).CellStyle = (ICellStyle)HeaderStyle;//设置列样式
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
/// <summary>
/// Get a specific cell from a row. If the cell doesn't exist,
/// </summary>
/// <param name="row">The row that the cell is part of</param>
/// <param name="column">The column index that the cell is in.</param>
/// <returns>The cell indicated by the column.</returns>
public static HSSFCell GetCell(HSSFRow row, int column)
{
HSSFCell cell = row.GetCell(column);
if (cell == null)
{
cell = row.CreateCell(column);
}
return cell;
}
/// <summary>
/// 插入Excel行
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
/// <param name="row"></param>
private static void MyInsertRow(HSSFSheet sheet, int rowIndex, int count, HSSFRow row)
{
#region 批量移动行
sheet.ShiftRows(
rowIndex, //--开始行
sheet
.LastRowNum, //--结束行
count, //--移动大小(行数)--往下移动
true, //是否复制行高
false, //是否重置行高
true //是否移动批注
);
#endregion
#region 对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)
for (int i = rowIndex; i < rowIndex + count - 1; i++)
{
HSSFRow targetRow;
HSSFCell sourceCell;
HSSFCell targetCell;
targetRow = sheet.CreateRow(i + 1);
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
sourceCell = row.GetCell(m);
if (sourceCell == null)
continue;
targetCell = targetRow.CreateCell(m);
targetCell.Encoding = sourceCell.Encoding;
targetCell.CellStyle = sourceCell.CellStyle;
targetCell.SetCellType(sourceCell.CellType);
}
//CopyRow(sourceRow, targetRow);
//Util.CopyRow(sheet, sourceRow, targetRow);
}
HSSFRow firstTargetRow = sheet.GetRow(rowIndex);
HSSFCell firstSourceCell;
HSSFCell firstTargetCell = null;
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
firstSourceCell = row.GetCell(m);
if (firstSourceCell == null)
continue;
firstTargetCell = firstTargetRow.CreateCell(m);
firstTargetCell.Encoding = firstSourceCell.Encoding;
firstTargetCell.CellStyle = firstSourceCell.CellStyle;
firstTargetCell.SetCellType(firstSourceCell.CellType);
}
sheet.AddMergedRegion(new Region(firstTargetRow.RowNum, 3, firstTargetRow.RowNum, 4));
#endregion
}
/**
* @return maximum 1-base index of column that were rendered, zero if none
*/
protected int ProcessRow(CellRangeAddress[][] mergedRanges, HSSFRow row,
XmlElement tableRowElement)
{
HSSFSheet sheet = (HSSFSheet)row.Sheet;
int maxColIx = row.LastCellNum;
if (maxColIx <= 0)
return 0;
List<XmlElement> emptyCells = new List<XmlElement>(maxColIx);
if (OutputRowNumbers)
{
XmlElement tableRowNumberCellElement = htmlDocumentFacade.CreateTableHeaderCell();
ProcessRowNumber(row, tableRowNumberCellElement);
emptyCells.Add(tableRowNumberCellElement);
}
int maxRenderedColumn = 0;
for (int colIx = 0; colIx < maxColIx; colIx++)
{
if (!OutputHiddenColumns && sheet.IsColumnHidden(colIx))
continue;
CellRangeAddress range = ExcelToHtmlUtils.GetMergedRange(
mergedRanges, row.RowNum, colIx);
if (range != null && (range.FirstColumn != colIx || range.FirstRow != row.RowNum))
continue;
HSSFCell cell = (HSSFCell)row.GetCell(colIx);
int divWidthPx = 0;
if (UseDivsToSpan)
{
divWidthPx = GetColumnWidth(sheet, colIx);
bool hasBreaks = false;
for (int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++)
{
if (!OutputHiddenColumns && sheet.IsColumnHidden(nextColumnIndex))
continue;
if (row.GetCell(nextColumnIndex) != null && !IsTextEmpty((HSSFCell)row.GetCell(nextColumnIndex)))
{
hasBreaks = true;
break;
}
divWidthPx += GetColumnWidth(sheet, nextColumnIndex);
}
if (!hasBreaks)
divWidthPx = int.MaxValue;
}
XmlElement tableCellElement = htmlDocumentFacade.CreateTableCell();
if (range != null)
{
if (range.FirstColumn != range.LastColumn)
tableCellElement.SetAttribute("colspan", (range.LastColumn - range.FirstColumn + 1).ToString());
if (range.FirstRow != range.LastRow)
tableCellElement.SetAttribute("rowspan", (range.LastRow - range.FirstRow + 1).ToString());
}
bool emptyCell;
if (cell != null)
{
emptyCell = ProcessCell(cell, tableCellElement, GetColumnWidth(sheet, colIx), divWidthPx, row.Height / 20f);
}
else
{
emptyCell = true;
}
if (emptyCell)
{
emptyCells.Add(tableCellElement);
}
else
{
foreach (XmlElement emptyCellElement in emptyCells)
{
tableRowElement.AppendChild(emptyCellElement);
}
emptyCells.Clear();
tableRowElement.AppendChild(tableCellElement);
maxRenderedColumn = colIx;
}
}
return maxRenderedColumn + 1;
}
private void SetRowStringValue(HSSFRow row, string column, string value)
{
((HSSFCell)row.GetCell(Convert.ToInt32(column))).SetCellValue(value);
}
private void SetRowNumericValue(HSSFRow row, string column, double value)
{
((HSSFCell)row.GetCell(Convert.ToInt32(column))).SetCellValue(value);
}
private void SetRowFormula(HSSFRow row, string column, string formula)
{
row.GetCell(Convert.ToInt32(column)).CellFormula = formula;
}
/// <summary>填充内容</summary>
static void FillContent(HSSFRow hssfRow
, DataColumn column, DataRow row
, HSSFCellStyle contentStyle, HSSFFont contentFont
, HSSFCellStyle contentDateStyle
, HSSFCellStyle contentStyleDailyBalance, HSSFFont fontDailyBalance
, string colHeadFont, short colHeadSize, short colHeadColor
, short contentColor, bool contentBorder
, string contentChangeColorFrom, short contentChangeColor
, HSSFSheet sheet, string headTextStrA)
{
HSSFCell newCell = hssfRow.CreateCell(column.Ordinal) as HSSFCell;
#region 当数据访问转换DataTime时生效
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
contentDateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#endregion
string columnF = row[column].ToString();
decimal i;
bool b = decimal.TryParse(columnF, out i);
#region 判断如果是仓库库存日结核对
if (contentChangeColorFrom == "DailyBalance" && sheet == workbook.GetSheet(headTextStrA)
&& ((column.Ordinal == 5 && b == false)
|| (column.Ordinal == 6 && b == false)
|| (column.Ordinal == 7 && b == false)
|| (column.Ordinal == 8 && b == false)
|| (column.Ordinal == 9 && b == false)
|| (column.Ordinal == 10 && b == false)))
{
fontDailyBalance.FontName = colHeadFont;
fontDailyBalance.FontHeightInPoints = colHeadSize;
fontDailyBalance.Color = contentChangeColor;
contentStyleDailyBalance.SetFont(fontDailyBalance);
if (contentBorder == true)
{
contentStyleDailyBalance.BorderBottom = BorderStyle.THIN;
contentStyleDailyBalance.BorderLeft = BorderStyle.THIN;
contentStyleDailyBalance.BorderRight = BorderStyle.THIN;
contentStyleDailyBalance.BorderTop = BorderStyle.THIN;
}
hssfRow.GetCell(column.Ordinal).CellStyle = contentStyleDailyBalance;
}
#endregion
else
{
contentFont.FontName = colHeadFont;
contentFont.FontHeightInPoints = colHeadSize;
contentFont.Color = contentColor;
contentStyle.SetFont(contentFont);
//画边框
if (contentBorder == true)
{
contentStyle.BorderBottom = BorderStyle.THIN;
contentStyle.BorderLeft = BorderStyle.THIN;
contentStyle.BorderRight = BorderStyle.THIN;
contentStyle.BorderTop = BorderStyle.THIN;
}
hssfRow.GetCell(column.Ordinal).CellStyle = contentStyle;
}
string drValue = row[column].ToString();
ChangeFormat(column,drValue,newCell,contentDateStyle);
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
const string FIELD_COMPANY = "Company";
const string FIELD_POSITION = "Position";
const string FIELD_PAYROLLGROUP = "Payroll Group";
const string FIELD_EMPNO = "EmployeeID";
const string FIELD_EMPENGFULLNAME = "English Name";
const string FIELD_CHINESENAME = "¤¤¤å©m¦W";
const string FIELD_HKID = @"HKID/Passport";
const string FIELD_PERIODFROM = "From";
const string FIELD_PERIODTO = "To";
const string FIELD_WAGESWORK = "Wages Paid";
const string FIELD_WORKHOURTOTAL = "Total Working Hours";
const string FIELD_RESTDAYTOTAL = "No. of Rest Day";
const string FIELD_STATUTORYHOLIDAYTOTAL = "No. of SH";
const string FIELD_FULLPAIDLEAVETOTAL = "No. of Full Paid Leave";
const string FIELD_NONFULLPAIDLEAVETOTAL = "Non-Full Paid Leave";
ArrayList list = new ArrayList();
foreach (RepeaterItem i in Repeater.Items)
{
CheckBox cb = (CheckBox)i.FindControl("ItemSelect");
if (cb.Checked)
{
EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject();
WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb);
list.Add(o);
}
}
ArrayList payPeriodList = Payroll_ConfirmedPeriod_List1.GetSelectedBaseObjectList();
if (list.Count > 0 && payPeriodList.Count > 0)
{
//const string PAYMENTCODE_PREFIX = "[StatutoryMinimumWageSummary] ";
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
//System.IO.File.Copy(Server.MapPath("~/template/HistoryList_Template.xls"), exportFileName, true);
HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
DataSet dataSet = new DataSet(); //export.GetDataSet();
DataTable dataTable = new DataTable("Payroll$");
dataSet.Tables.Add(dataTable);
dataTable.Columns.Add(FIELD_COMPANY, typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
dataTable.Columns.Add(FIELD_POSITION, typeof(string));
dataTable.Columns.Add(FIELD_PAYROLLGROUP, typeof(string));
dataTable.Columns.Add(FIELD_EMPNO, typeof(string));
dataTable.Columns.Add(FIELD_EMPENGFULLNAME, typeof(string));
dataTable.Columns.Add(FIELD_CHINESENAME, typeof(string));
dataTable.Columns.Add(FIELD_HKID, typeof(string));
dataTable.Columns.Add(FIELD_PERIODFROM, typeof(DateTime));
dataTable.Columns.Add(FIELD_PERIODTO, typeof(DateTime));
dataTable.Columns.Add(FIELD_WAGESWORK, typeof(double));
dataTable.Columns.Add(FIELD_WORKHOURTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_RESTDAYTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_STATUTORYHOLIDAYTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_FULLPAIDLEAVETOTAL, typeof(double));
dataTable.Columns.Add(FIELD_NONFULLPAIDLEAVETOTAL, typeof(double));
int firstSummaryColumnPos = dataTable.Columns.Count;
int firstDetailColumnPos = dataTable.Columns.Count;
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
if (EPayrollPeriod.db.select(dbConn, payPeriod))
{
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payrollGroup);
foreach (EEmpPersonalInfo empInfo in list)
{
EEmpPersonalInfo.db.select(dbConn, empInfo);
EEmpTermination empTerm = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID);
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("ep.EmpID", empInfo.EmpID));
empPayrollFilter.add(new Match("ep.PayPeriodID", payPeriod.PayPeriodID));
if (PayrollStatus.SelectedValue.Equals("T"))
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T"));
}
else
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T"));
}
DataRow row = dataTable.NewRow();
row[FIELD_EMPNO] = empInfo.EmpNo;
row[FIELD_EMPENGFULLNAME] = empInfo.EmpEngFullName;
row[FIELD_CHINESENAME] = empInfo.EmpChiFullName;
row[FIELD_HKID] = empInfo.EmpHKID;
row[FIELD_PERIODFROM] = payPeriod.PayPeriodFr;
row[FIELD_PERIODTO] = payPeriod.PayPeriodTo;
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row[FIELD_COMPANY] = company.CompanyCode;
}
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
row[FIELD_POSITION] = position.PositionDesc;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
EPayrollGroup curentPayGroup = new EPayrollGroup();
curentPayGroup.PayGroupID = empPos.PayGroupID;
if (EPayrollGroup.db.select(dbConn, curentPayGroup))
{
row[FIELD_PAYROLLGROUP] = curentPayGroup.PayGroupDesc;
}
}
double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, taxableAmount = 0, nonTaxableAmount = 0;
double mcER = 0, mcEE = 0;
double vcER = 0, vcEE = 0;
double pFundER = 0, pFundEE = 0;
double wagesByWork = 0;
double wagesByRest = 0;
double fullPaidLeaveDays = 0;
double nonFullPaidLeaveDays = 0;
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode payCode = new EPaymentCode();
payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, payCode);
// Always Use Payment Code Description for grouping payment code with same description
//string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc;
//if (dataTable.Columns[fieldName] == null)
// dataTable.Columns.Add(new DataColumn(fieldName, typeof(double)));
//if (row[fieldName] == null || row[fieldName] == DBNull.Value)
// row[fieldName] = 0;
//row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount;
netAmount += paymentRecord.PayRecActAmount;
if (payCode.PaymentCodeIsMPF)
{
releventIncome += paymentRecord.PayRecActAmount;
}
else
{
nonRelevantIncome += paymentRecord.PayRecActAmount;
}
DBFilter taxPaymentMapFilter = new DBFilter();
taxPaymentMapFilter.add(new Match("PaymentCodeID", paymentRecord.PaymentCodeID));
if (ETaxPaymentMap.db.count(dbConn, taxPaymentMapFilter) > 0)
{
taxableAmount += paymentRecord.PayRecActAmount;
}
else
{
nonTaxableAmount += paymentRecord.PayRecActAmount;
}
if (payCode.PaymentCodeIsWages)
{
if (paymentRecord.PayRecIsRestDayPayment)
{
wagesByRest += paymentRecord.PayRecActAmount;
}
else
{
wagesByWork += paymentRecord.PayRecActAmount;
}
}
}
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
foreach (EMPFRecord mpfRecord in mpfRecords)
{
vcER += mpfRecord.MPFRecActVCER;
mcER += +mpfRecord.MPFRecActMCER;
vcEE += mpfRecord.MPFRecActVCEE;
mcEE += mpfRecord.MPFRecActMCEE;
}
ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
foreach (EORSORecord orsoRecord in orsoRecords)
{
pFundER += orsoRecord.ORSORecActER;
pFundEE += orsoRecord.ORSORecActEE;
}
row[FIELD_WAGESWORK] = wagesByWork;
DBFilter workingSummaryFilter = new DBFilter();
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
if (empTerm != null)
{
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
}
else
{
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo));
}
workingSummaryFilter.add(new Match("EmpID", empInfo.EmpID));
ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, workingSummaryFilter);
double workHourTotal = 0, restDayTotal = 0;
foreach (EEmpWorkingSummary empWorkSummary in empWorkingSummaryList)
{
workHourTotal += empWorkSummary.EmpWorkingSummaryTotalWorkingHours;
restDayTotal += empWorkSummary.EmpWorkingSummaryRestDayEntitled;
}
row[FIELD_WORKHOURTOTAL] = workHourTotal;
row[FIELD_RESTDAYTOTAL] = restDayTotal;
DBFilter statutoryHolidayFilter = new DBFilter();
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
if (empTerm != null)
{
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
}
else
{
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo));
}
ArrayList statutoryHolidayList = EStatutoryHoliday.db.select(dbConn, statutoryHolidayFilter);
double restDayCount = 0;
foreach (EStatutoryHoliday statutoryHoliday in statutoryHolidayList)
{
restDayCount++;
}
row[FIELD_STATUTORYHOLIDAYTOTAL] = restDayCount;
DBFilter LeaveAppEmpPayrollFilter = new DBFilter();
LeaveAppEmpPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
ArrayList LeaveAppEmpPayrollLists = ELeaveApplication.db.select(dbConn, LeaveAppEmpPayrollFilter);
foreach (ELeaveApplication leaveApp in LeaveAppEmpPayrollLists)
{
ELeaveCode leaveCode = new ELeaveCode();
leaveCode.LeaveCodeID = leaveApp.LeaveCodeID;
if (ELeaveCode.db.select(dbConn, leaveCode))
{
if (leaveCode.LeaveCodePayRatio >= 1)
{
fullPaidLeaveDays += leaveApp.LeaveAppDays;
}
else
{
nonFullPaidLeaveDays += leaveApp.LeaveAppDays;
}
}
}
row[FIELD_FULLPAIDLEAVETOTAL] = fullPaidLeaveDays;
row[FIELD_NONFULLPAIDLEAVETOTAL] = nonFullPaidLeaveDays;
dataTable.Rows.Add(row);
}
}
}
//DBFilter paymentCodeFilter = new DBFilter();
//paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
//paymentCodeFilter.add("PaymentCode", false);
//ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
//foreach (EPaymentCode paymentCode in paymentCodeList)
//{
// if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc))
// {
// DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc];
// paymentColumn.SetOrdinal(firstDetailColumnPos);
// if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc))
// paymentColumn.ColumnName = paymentCode.PaymentCodeDesc;
// else
// {
// Console.Write("System reserved payment column is used");
// }
// }
//}
//for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++)
// dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1);
export.Update(dataSet);
System.IO.FileStream excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelfileStream);
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0);
workSheet.ShiftRows(workSheet.FirstRowNum, workSheet.LastRowNum, 1);
NPOI.HSSF.UserModel.HSSFRow excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(0);
if (excelRow == null)
{
excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0);
}
NPOI.HSSF.UserModel.HSSFCell excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.GetCell(0);
if (excelCell == null)
{
excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.CreateCell(0);
}
excelCell.SetCellValue("Statutory Minimum Wage Summary Report");
excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
workbook.Write(excelfileStream);
excelfileStream.Close();
WebUtils.TransmitFile(Response, exportFileName, "StatutoryMinimumWageSummary_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
return;
}
else
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.addError("Employee not selected");
}
}
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 static bool CheckValidDataRow(HSSFRow row, int startColIndex, int endColIndex)
{
for (int i = startColIndex; i < endColIndex; i++)
{
ICell cell = row.GetCell(i);
if (cell != null && cell.CellType != NPOI.SS.UserModel.CellType.BLANK)
{
return true;
}
}
return false;
}
