public override void ImportToDatabase()
{
DBFilter sessionFilter = new DBFilter();
sessionFilter.add(new Match("SessionID", m_SessionID));
sessionFilter.add("EmpID", true);
ArrayList uploadTimeCardRecordList = EUploadTimeCardRecord.db.select(dbConn, sessionFilter);
foreach (EUploadTimeCardRecord obj in uploadTimeCardRecordList)
{
ETimeCardRecord timeCardRecord = new ETimeCardRecord();
timeCardRecord.TimeCardRecordID = obj.TimeCardRecordID;
if (!ETimeCardRecord.db.select(dbConn, timeCardRecord))
{
timeCardRecord.TimeCardRecordID = 0;
}
timeCardRecord.EmpID = obj.EmpID;
timeCardRecord.TimeCardRecordOriginalData = obj.TimeCardRecordOriginalData;
timeCardRecord.TimeCardRecordCardNo = obj.TimeCardRecordCardNo;
timeCardRecord.TimeCardRecordDateTime = obj.TimeCardRecordDateTime;
timeCardRecord.TimeCardRecordLocation = obj.TimeCardRecordLocation;
timeCardRecord.TimeCardRecordInOutIndex = obj.TimeCardRecordInOutIndex;
if (obj.ImportActionStatus.Equals(ImportDBObject.ImportActionEnum.INSERT))
{
ETimeCardRecord.db.insert(dbConn, timeCardRecord);
}
else
{
ETimeCardRecord.db.update(dbConn, timeCardRecord);
}
EUploadTimeCardRecord.db.delete(dbConn, obj);
}
}
private NPOI.HSSF.UserModel.HSSFSheet CreateWorkSheet(NPOI.HSSF.UserModel.HSSFWorkbook workbook, DataTable dataTable)
{
if (workbook != null)
{
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(dataTable.TableName.Replace("$", ""));
//NPOI.HSSF.UserModel.HSSFRow chineseHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
int columnCount = 0;
//System.Globalization.CultureInfo chineseCI = new System.Globalization.CultureInfo("zh-cht");
foreach (DataColumn headercolumn in dataTable.Columns)
{
headercolumn.ColumnName = headercolumn.ColumnName.Trim();
if (!headercolumn.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase))
{
string columnName = headercolumn.ColumnName;
//string chineseColumnName = HROne.Common.WebUtility.GetLocalizedString(columnName, chineseCI);
//if (columnName.Equals(chineseColumnName))
// chineseColumnName = string.Empty;
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
cell.SetCellValue(columnName);
//cell = (NPOI.HSSF.UserModel.HSSFCell)chineseHeaderRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
//cell.SetCellValue(chineseColumnName);
//headerRow.SetCell(columnCount,cell);
//worksheet.Cells[0, columnCount] = cell;//new ExcelLibrary.SpreadSheet.Cell(column.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
columnCount++;
}
}
//worksheet.Cells.Rows.Add(0, headerRow);
int rowCount = 0;
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; //900;
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
NPOI.HSSF.UserModel.HSSFCellStyle ManualAdjustCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//ManualInputDateCellStyle.CloneStyleFrom(dateCellStyle);
ManualAdjustCellStyle.SetFont(boldFont);
//NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//numericCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); ;
//NPOI.HSSF.UserModel.HSSFCellStyle integerCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//integerCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0"); ;
foreach (DataRow row in dataTable.Rows)
{
rowCount++;
columnCount = 0;
// ExcelLibrary.SpreadSheet.Row detailRow = new ExcelLibrary.SpreadSheet.Row();
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
foreach (DataColumn column in dataTable.Columns)
{
if (!column.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase))
{
//ExcelLibrary.SpreadSheet.Cell cell =new ExcelLibrary.SpreadSheet.Cell(string.Empty, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(columnCount);
if (column.DataType.Equals(typeof(string)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
cell.SetCellValue(row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
// Override style to bold if manual adjust
if (cell.StringCellValue != string.Empty)
{
if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
}
}
else if (column.DataType.Equals(typeof(double)) || column.DataType.Equals(typeof(float)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
if (row[column] != System.DBNull.Value)
{
double value = Convert.ToDouble(row[column].ToString());
if (value.Equals(double.NaN))
{
cell.SetCellValue(string.Empty);
}
else
{
cell.SetCellValue(value);
}
}
//cell.CellStyle = numericCellStyle;
}
else if (column.DataType.Equals(typeof(int)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue(Convert.ToDouble(row[column].ToString()));
}
//cell.CellStyle = integerCellStyle;
}
else if (column.DataType.Equals(typeof(DateTime)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.DateTime, "yyyy-MM-dd");
//if (row[column] == System.DBNull.Value)
// cell.Value = string.Empty;
//else
// cell.Value = (DateTime)row[column];
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue((DateTime)row[column]);
}
cell.CellStyle = dateCellStyle;
}
else
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue(row[column].ToString());
}
}
//worksheet.Cells[rowCount, columnCount] = cell;
columnCount++;
}
}
// worksheet.Cells.Rows.Add(rowCount, detailRow);
}
//workbook.Worksheets.Add(worksheet);
return(worksheet);
}
else
{
return(null);
}
}