public async Task <List <LabFileUploadErrorMessageDTO> > ValidateLabFileAsync(IFileListEntry fileEntry, MappingDataDTO MappingTemplate)
{
var ErrorMessage = new List <LabFileUploadErrorMessageDTO>();
int row = 1;
try
{
string path = "";
List <ParameterDTO> objParamList = new List <ParameterDTO>();
var searchModel = new ParameterDTO()
{
prm_code_major = "UPLOAD_PATH"
};
objParamList = await _apiHelper.GetDataListByModelAsync <ParameterDTO, ParameterDTO>("dropdownlist_api/GetParameterList", searchModel);
if (objParamList.FirstOrDefault(x => x.prm_code_minor == "PATH") != null)
{
path = objParamList.FirstOrDefault(x => x.prm_code_minor == "PATH").prm_value;
}
else
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'E',
lfu_Err_type = 'E',
lfu_Err_no = 1,
lfu_Err_Column = "",
lfu_Err_Message = "ไม่พบ Config PATH กรุณาติดต่อผู้ดูแลระบบ "
});
return(ErrorMessage);
}
string str_CurrentDate = DateTime.Now.ToString("yyyyMMdd");
path = Path.Combine(path, str_CurrentDate, MappingTemplate.mp_hos_code);
bool exists = System.IO.Directory.Exists(path);
if (!exists)
{
System.IO.Directory.CreateDirectory(path);
}
path = Path.Combine(path, fileEntry.Name);
using (FileStream file = new FileStream(path, FileMode.Create))
{
try
{
await fileEntry.Data.CopyToAsync(file);
}
catch (Exception ex)
{
}
finally
{
file.Flush();
}
}
WHONetMappingSearch searchWHONet = new WHONetMappingSearch();
searchWHONet.wnm_mappingid = MappingTemplate.mp_id;
searchWHONet.wnm_mst_code = MappingTemplate.mp_mst_code;
List <WHONetMappingListsDTO> WHONetColumn = await _apiHelper.GetDataListByModelAsync <WHONetMappingListsDTO, WHONetMappingSearch>("mapping_api/Get_WHONetMappingListByModel", searchWHONet);
var WHONetColumnMandatory = WHONetColumn.Where(x => x.wnm_mandatory == true);
#region ReadExcel
if (Path.GetExtension(fileEntry.Name) == ".xls" || Path.GetExtension(fileEntry.Name) == ".xlsx")
{
ExcelDataSetConfiguration option = new ExcelDataSetConfiguration();
using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = new DataSet();
//First row is header
if (MappingTemplate.mp_firstlineisheader == true)
{
result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
}
);
}
else
{
result = reader.AsDataSet();
}
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'I',
lfu_Err_type = 'I',
lfu_Err_no = 1,
lfu_Err_Column = "Total",
lfu_Err_Message = result.Tables[0].Rows.Count.ToString()
});
foreach (WHONetMappingListsDTO item in WHONetColumnMandatory)
{
var wnm_originalfield = item.wnm_originalfield;
if (MappingTemplate.mp_firstlineisheader == false)
{
int index = 0;
Int32.TryParse(item.wnm_originalfield.Replace("Column", ""), out index);
item.wnm_originalfield = "Column" + (index - 1);
}
Boolean columnExists = result.Tables[0].Columns.Contains(item.wnm_originalfield);
if (columnExists == false)
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'E',
lfu_Err_type = 'C',
lfu_Err_no = 1,
lfu_Err_Column = wnm_originalfield,
lfu_Err_Message = "ไม่พบ Column " + wnm_originalfield
});
}
if (columnExists)
{
var chkResult = result.Tables[0].Select("[" + item.wnm_originalfield + "]" + " is null");
if (chkResult.Length > 0)
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'E',
lfu_Err_type = 'N',
lfu_Err_no = 1,
lfu_Err_Column = wnm_originalfield,
lfu_Err_Message = "กรุณาตรวจสอบข้อมูล Column " + wnm_originalfield + " จะต้องไม่เท่ากับค่าว่าง"
});
}
//if (item.wnm_type == "Date")
//{
// var FieldDateType = result.Tables[0].Columns[item.wnm_originalfield].DataType.ToString();
// if(FieldDateType == "System.String")
// {
// try
// {
// }
// catch()
// {
// }
// }
//}
}
}
var x = ErrorMessage;
}
}
}
#endregion
#region ReadCSV
else if (Path.GetExtension(fileEntry.Name) == ".csv")
{
using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
{
var reader = ExcelReaderFactory.CreateCsvReader(stream, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding(1252),
AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' },
LeaveOpen = false,
AnalyzeInitialCsvRows = 0,
});
DataSet result = new DataSet();
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'I',
lfu_Err_type = 'I',
lfu_Err_no = 1,
lfu_Err_Column = "Total",
lfu_Err_Message = result.Tables[0].Rows.Count.ToString()
});
//First row is header
if (MappingTemplate.mp_firstlineisheader == true)
{
result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
}
);
}
else
{
result = reader.AsDataSet();
}
var ee = result.Tables[0];
foreach (WHONetMappingListsDTO item in WHONetColumnMandatory)
{
var wnm_originalfield = item.wnm_originalfield;
if (MappingTemplate.mp_firstlineisheader == false)
{
int index = 0;
Int32.TryParse(item.wnm_originalfield.Replace("Column", ""), out index);
item.wnm_originalfield = "Column" + (index - 1);
}
Boolean columnExists = result.Tables[0].Columns.Contains(item.wnm_originalfield);
if (columnExists == false)
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'E',
lfu_Err_type = 'C',
lfu_Err_no = 1,
lfu_Err_Column = wnm_originalfield,
lfu_Err_Message = "ไม่พบ Column " + wnm_originalfield
});
}
if (columnExists)
{
var chkResult = result.Tables[0].Select(item.wnm_originalfield + " = ''");
if (chkResult.Length > 0)
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'E',
lfu_Err_type = 'N',
lfu_Err_no = 1,
lfu_Err_Column = wnm_originalfield,
lfu_Err_Message = "กรุณาตรวจสอบข้อมูล Column " + wnm_originalfield + " จะต้องไม่เท่ากับค่าว่าง"
});
}
}
}
var x = ErrorMessage;
}
}
#endregion
#region ReadText
else if (Path.GetExtension(fileEntry.Name) == ".txt")
{
string line;
DataTable dt = new DataTable();
string tempFilename = Guid.NewGuid().ToString() + ".txt";
//var path = Path.Combine(@"D:\Work\02-DMSC ALISS\TEMP\", tempFilename);
//var ms = new MemoryStream();
//await fileEntry.Data.CopyToAsync(ms);
//using (FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write))
//{
// ms.WriteTo(file);
//}
//using (TextReader tr = File.OpenText(@"D:\Work\02-DMSC ALISS\TEMP\" + tempFilename))
using (TextReader tr = File.OpenText(path))
{
while ((line = tr.ReadLine()) != null)
{
string[] items = line.Split('\t');
if (dt.Columns.Count == 0)
{
if (MappingTemplate.mp_firstlineisheader == false)
{
for (int i = 0; i < items.Length; i++)
{
dt.Columns.Add(new DataColumn("Column" + i, typeof(string)));
}
}
else
{
for (int i = 0; i < items.Length; i++)
{
dt.Columns.Add(new DataColumn(items[i].ToString(), typeof(string)));
}
}
}
dt.Rows.Add(items);
}
}
File.Delete(path);
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'I',
lfu_Err_type = 'I',
lfu_Err_no = 1,
lfu_Err_Column = "Total",
lfu_Err_Message = (dt.Rows.Count - 1).ToString()
});
foreach (WHONetMappingListsDTO item in WHONetColumnMandatory)
{
var wnm_originalfield = item.wnm_originalfield;
if (MappingTemplate.mp_firstlineisheader == false)
{
int index = 0;
Int32.TryParse(item.wnm_originalfield.Replace("Column", ""), out index);
item.wnm_originalfield = "Column" + (index - 1);
}
Boolean columnExists = dt.Columns.Contains(item.wnm_originalfield);
if (columnExists == false)
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'E',
lfu_Err_type = 'C',
lfu_Err_no = 1,
lfu_Err_Column = wnm_originalfield,
lfu_Err_Message = "ไม่พบ Column " + wnm_originalfield
});
}
if (columnExists)
{
var chkResult = dt.Select(item.wnm_originalfield + " = ''");
if (chkResult.Length > 0)
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'E',
lfu_Err_type = 'N',
lfu_Err_no = 1,
lfu_Err_Column = wnm_originalfield,
lfu_Err_Message = "กรุณาตรวจสอบข้อมูล Column " + wnm_originalfield + " จะต้องไม่เท่ากับค่าว่าง"
});
}
}
}
}
#endregion
else
{
string tempFilename = Guid.NewGuid().ToString() + ".dbf";
//var path = Path.Combine(@"D:\Work\02-DMSC ALISS\TEMP\", tempFilename);
//bool exists = System.IO.Directory.Exists(@"D:\Work\02-DMSC ALISS\TEMP\");
//if (!exists)
// System.IO.Directory.CreateDirectory(@"D:\Work\02-DMSC ALISS\TEMP\");
//using (FileStream file = new FileStream(path, FileMode.Create))
//{
// try
// {
// await fileEntry.Data.CopyToAsync(file);
// }
// catch (Exception ex)
// {
// }
// finally
// {
// file.Flush();
// }
//}
var options = new DbfDataReaderOptions
{
Encoding = Encoding.GetEncoding(874)
};
using (var dbfDataReader = new DbfDataReader.DbfDataReader(path, options))
//using (var dbfDataReader = new DbfDataReader.DbfDataReader(@"D:\Work\02-DMSC ALISS\TEMP\" + tempFilename, options))
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'I',
lfu_Err_type = 'I',
lfu_Err_no = 1,
lfu_Err_Column = "Total",
lfu_Err_Message = dbfDataReader.DbfTable.Header.RecordCount.ToString()
});
while (dbfDataReader.Read())
{
//Validate Mandatory Field
foreach (WHONetMappingListsDTO item in WHONetColumnMandatory)
{
var columnExists = dbfDataReader.DbfTable.Columns.FirstOrDefault(x => x.Name == item.wnm_originalfield);
//var ll = dbfDataReader.DbfTable.Rea
if (columnExists != null)
{
if (dbfDataReader[item.wnm_originalfield] == "" || dbfDataReader[item.wnm_originalfield] == null)
{
if (ErrorMessage.FirstOrDefault(x => x.lfu_Err_type == 'N' && x.lfu_Err_Column == item.wnm_originalfield) == null)
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'E',
lfu_Err_type = 'N',
lfu_Err_no = 1,
lfu_Err_Column = item.wnm_originalfield,
lfu_Err_Message = "กรุณาตรวจสอบข้อมูล Column " + item.wnm_originalfield + " จะต้องไม่เท่ากับค่าว่าง"
});
var ccc = dbfDataReader[item.wnm_originalfield];
}
}
}
else
{
if (ErrorMessage.FirstOrDefault(x => x.lfu_Err_type == 'C' && x.lfu_Err_Column == item.wnm_originalfield) == null)
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'E',
lfu_Err_type = 'C',
lfu_Err_no = 1,
lfu_Err_Column = item.wnm_originalfield,
lfu_Err_Message = "ไม่พบ Column " + item.wnm_originalfield
});
}
}
}
row++;
}
var x = ErrorMessage;
//using (FileStream file = new FileStream(@"D:\Work\02-DMSC ALISS\TEMP\"+ fileEntry.Name, FileMode.Create))
//{
// try
// {
// await fileEntry.Data.CopyToAsync(file);
// }
// catch (Exception ex)
// {
// }
// finally
// {
// file.Flush();
// }
//}
//var zipStream = new MemoryStream();
//using (var compressedFileStream = new MemoryStream())
//{
// //compressedFileStream.Seek(0, SeekOrigin.Begin);
// using (var zipArchive = new ZipArchive(compressedFileStream, ZipArchiveMode.Create, false))
// {
// var zipEntry = zipArchive.CreateEntry(fileEntry.Name);
// using (var zipEntryStream = zipEntry.Open())
// {
// try
// {
// await fileEntry.Data.CopyToAsync(zipEntryStream);
// //fileEntry.Data.CopyTo(zipEntryStream);
// }
// catch (Exception ex)
// {
// }
// finally
// {
// zipEntryStream.Flush();
// }
// }
// }
// using (var fileStream = new FileStream(@"D:\Work\02-DMSC ALISS\TEMP\test.zip", FileMode.Create))
// {
// var bytes = compressedFileStream.GetBuffer();
// fileStream.Write(bytes, 0, bytes.Length);
// try
// {
// await compressedFileStream.CopyToAsync(fileStream);
// }
// catch (Exception ex)
// {
// }
// finally
// {
// fileStream.Flush();
// }
// }
//}
//using (var outStream = new MemoryStream())
//{
// using (var archive = new ZipArchive(outStream, ZipArchiveMode.Create, true))
// {
// var fileInArchive = archive.CreateEntry(fileEntry.Name, CompressionLevel.Optimal);
// using (var entryStream = fileInArchive.Open())
// await fileEntry.Data.CopyToAsync(entryStream);
// }
// using (var fileStream = new FileStream(@"D:\test.zip", FileMode.Create))
// {
// outStream.Seek(0, SeekOrigin.Begin);
// outStream.CopyTo(fileStream);
// }
//}
}
//File.Delete(path);
}
var chkError = ErrorMessage.FirstOrDefault(x => x.lfu_status == 'E');
if (chkError != null)
{
File.Delete(path);
}
else
{
ErrorMessage.Add(new LabFileUploadErrorMessageDTO
{
lfu_status = 'I',
lfu_Err_type = 'P',
lfu_Err_no = 1,
lfu_Err_Column = "path",
lfu_Err_Message = path
});
}
}
catch (Exception ex)
{
}
return(ErrorMessage);
}
public async Task <List <HISUploadErrorMessageDTO> > ValidateSPFileAsync(IFileListEntry fileEntry, HISUploadDataDTO HISfileOwner)
{
List <HISUploadErrorMessageDTO> ErrorMessage = new List <HISUploadErrorMessageDTO>();
try
{
string path = "";
const bool FIRSTROW_IS_HEADER = true;
List <ParameterDTO> objParamList = new List <ParameterDTO>();
var searchModel = new ParameterDTO()
{
prm_code_major = "UPLOAD_PATH"
};
objParamList = await _apiHelper.GetDataListByModelAsync <ParameterDTO, ParameterDTO>("dropdownlist_api/GetParameterList", searchModel);
if (objParamList.FirstOrDefault(x => x.prm_code_minor == "PATH") != null)
{
path = objParamList.FirstOrDefault(x => x.prm_code_minor == "PATH").prm_value;
}
else
{
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'E',
hfu_Err_type = "E",
hfu_Err_no = 1,
hfu_Err_Column = "",
hfu_Err_Message = "ไม่พบ Config PATH กรุณาติดต่อผู้ดูแลระบบ "
});
return(ErrorMessage);
}
string str_CurrentDate = DateTime.Now.ToString("yyyyMMdd");
path = Path.Combine(path, str_CurrentDate, HISfileOwner.hfu_hos_code);
bool exists = System.IO.Directory.Exists(path);
if (!exists)
{
System.IO.Directory.CreateDirectory(path);
}
path = Path.Combine(path, fileEntry.Name);
using (FileStream file = new FileStream(path, FileMode.Create))
{
try
{
await fileEntry.Data.CopyToAsync(file);
}
catch (Exception ex)
{
}
finally
{
file.Flush();
}
}
// WHONetMappingSearch searchWHONet = new WHONetMappingSearch();
// searchWHONet.wnm_mappingid = MappingTemplate.mp_id;
// searchWHONet.wnm_mst_code = MappingTemplate.mp_mst_code;
// List<WHONetMappingListsDTO> WHONetColumn = await _apiHelper.GetDataListByModelAsync<WHONetMappingListsDTO, WHONetMappingSearch>("mapping_api/Get_WHONetMappingListByModel", searchWHONet);
// var WHONetColumnMandatory = WHONetColumn.Where(x => x.wnm_mandatory == true);
#region ReadExcel
if (Path.GetExtension(fileEntry.Name) == ".xls" || Path.GetExtension(fileEntry.Name) == ".xlsx")
{
ExcelDataSetConfiguration option = new ExcelDataSetConfiguration();
DataSet result = new DataSet();
using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
if (FIRSTROW_IS_HEADER == true)
{
result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
}
);
}
else
{
result = reader.AsDataSet();
}
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'I',
hfu_Err_type = "I",
hfu_Err_no = 1,
hfu_Err_Column = "Total",
hfu_Err_Message = result.Tables[0].Rows.Count.ToString()
});
}
}
#endregion
//---------- Validate Data in File -------------
var dataTable = result.Tables[0];
const int colRefNo = 0;
const int colHNNo = 1;
const int colLabNo = 2;
const int colSpecDate = 3;
const string COL_REF_NO = "Ref No";
const string COL_HN_NO = "HN";
const string COL_LAB_NO = "Lab";
const string COL_DATE = "Date";
// Check column Exist
Boolean columnExists = result.Tables[0].Columns.Contains(COL_REF_NO) &&
result.Tables[0].Columns.Contains(COL_HN_NO) &&
result.Tables[0].Columns.Contains(COL_LAB_NO) &&
result.Tables[0].Columns.Contains(COL_DATE);
if (columnExists == false)
{
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'E',
hfu_Err_type = "C",
hfu_Err_no = 1,
hfu_Err_Column = COL_REF_NO,
hfu_Err_Message = "ไม่พบ Column " + COL_REF_NO
});
}
for (var i = 0; i < dataTable.Rows.Count; i++)
{
for (var j = 0; j < dataTable.Columns.Count; j++)
{
//HISWithSP
if (HISfileOwner.hfu_file_type == "HIS" && j == 4)
{
break;
}
var data = dataTable.Rows[i][j];
// Check column not null
if (string.IsNullOrEmpty(data.ToString()))
{
string columnError = "";
if (j == colRefNo)
{
columnError = "Ref No.";
}
else if (j == colHNNo)
{
columnError = "HN No.";
}
else if (j == colLabNo)
{
columnError = "Lab No.";
}
else if (j == colSpecDate)
{
columnError = "Date";
}
}
// Check Date format incorrect
if (j == colSpecDate)
{
//string dateString = data.ToString();
//DateTime dtValue;
//DateTime dt;
//string[] formateDate = { "dd/MM/yyyy" };
//if (DateTime.TryParse(dateString, out dtValue))
//{
// var dateonly = dtValue.Date;
// if (!DateTime.TryParseExact(dateonly.ToString(), formateDate,
// new CultureInfo("en-US"),
// DateTimeStyles.None, out dt))
// {
// //your condition fail code goes here
// ErrorMessage.Add(new HISUploadErrorMessageDTO
// {
// hfu_status = 'E',
// hfu_Err_type = "E",
// hfu_Err_no = 1,
// hfu_Err_Column = "",
// hfu_Err_Message = "Column Date ต้องอยู่ในรูปแบบ " + formateDate[0]
// }); ;
// return ErrorMessage;
// }
//}
//else
//{
// ErrorMessage.Add(new HISUploadErrorMessageDTO
// {
// hfu_status = 'E',
// hfu_Err_type = "E",
// hfu_Err_no = 1,
// hfu_Err_Column = "",
// hfu_Err_Message = "Column Date ต้องอยู่ในรูปแบบ " + formateDate[0]
// }); ;
// return ErrorMessage;
//}
}
}
}
var chkError = ErrorMessage.FirstOrDefault(x => x.hfu_status == 'E');
if (chkError != null)
{
File.Delete(path);
}
else
{
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'I',
hfu_Err_type = "P",
hfu_Err_no = 1,
hfu_Err_Column = "path",
hfu_Err_Message = path
});
}
}
}
catch (Exception ex)
{
//create log
}
return(ErrorMessage);
}
public void OnFileUploaded(IFileListEntry[] files)
{
FileEntry = files.FirstOrDefault();
}
public async Task UploadAsync(IFileListEntry fileListEntry)
{
path = @"C:\inetpub\wwwroot\publish\wwwroot\Documents\" + fileListEntry.Name;
ms = new MemoryStream();
await fileListEntry.Data.CopyToAsync(ms);
}
public void HandleFileSelected(IFileListEntry[] files)
{
file = files.FirstOrDefault();
}
public async Task <List <TemplateFileListsDTO> > UploadAsync(IFileListEntry fileEntry, bool?FirstLineIsHeader)
{
var Templates = new List <TemplateFileListsDTO>();
string _tempheader = "", _tempvalue = "", path = "";
int _temprow = 0;
try
{
var g = Path.GetExtension(fileEntry.Name);
#region ReadExcel
if (Path.GetExtension(fileEntry.Name) == ".xls" || Path.GetExtension(fileEntry.Name) == ".xlsx")
{
using (var stream = new MemoryStream())
{
await fileEntry.Data.CopyToAsync(stream);
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
do
{
_temprow = 1;
while (reader.Read() && _temprow <= 2) //Each ROW
{
if (_temprow == 1)
{
for (int column = 0; column < reader.FieldCount; column++)
{
if (FirstLineIsHeader == true)
{
if (reader.GetValue(column) != null)
{
_tempheader = reader.GetValue(column).ToString().Trim();
}
else
{
_tempheader = "";
}
}
else
{
_tempheader = "Column" + (column + 1).ToString();
if (reader.GetValue(column) != null)
{
_tempvalue = reader.GetValue(column).ToString();
}
else
{
_tempvalue = "";
}
}
Templates.Add(new TemplateFileListsDTO
{
tmp_no = column,
tmp_header = _tempheader,
tmp_value = _tempvalue
}
);
}
}
else if (_temprow == 2 && FirstLineIsHeader == true)
{
for (int column = 0; column < reader.FieldCount; column++)
{
TemplateFileListsDTO template = Templates.Where(t => t.tmp_no == column).FirstOrDefault();
if (reader.GetValue(column) != null)
{
template.tmp_value = reader.GetValue(column).ToString();
var testvalue = reader.GetValue(column);
}
}
}
_temprow++;
}
} while (reader.NextResult());
}
}
}
#endregion
#region Readcsv
else if (Path.GetExtension(fileEntry.Name) == ".csv")
{
using (var stream = new MemoryStream())
{
await fileEntry.Data.CopyToAsync(stream);
var reader = ExcelReaderFactory.CreateCsvReader(stream, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding(1252),
AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' },
LeaveOpen = false,
AnalyzeInitialCsvRows = 0,
});
var result = reader.AsDataSet();
do
{
_temprow = 1;
while (reader.Read() && _temprow <= 2) //Each ROW
{
if (_temprow == 1)
{
for (int column = 0; column < reader.FieldCount; column++)
{
if (FirstLineIsHeader == true)
{
if (reader.GetValue(column) != null)
{
_tempheader = reader.GetValue(column).ToString().Trim();
}
else
{
_tempheader = "";
}
}
else
{
_tempheader = "Column" + (column + 1).ToString();
if (reader.GetValue(column) != null)
{
_tempvalue = reader.GetValue(column).ToString();
}
else
{
_tempvalue = "";
}
}
Templates.Add(new TemplateFileListsDTO
{
tmp_no = column,
tmp_header = _tempheader,
tmp_value = _tempvalue
}
);
}
}
else if (_temprow == 2 && FirstLineIsHeader == true)
{
for (int column = 0; column < reader.FieldCount; column++)
{
TemplateFileListsDTO template = Templates.Where(t => t.tmp_no == column).FirstOrDefault();
if (reader.GetValue(column) != null)
{
template.tmp_value = reader.GetValue(column).ToString();
var testvalue = reader.GetValue(column);
}
}
}
_temprow++;
}
} while (reader.NextResult());
}
}
#endregion
#region ReadText
else if (Path.GetExtension(fileEntry.Name) == ".txt")
{
string tempFilename = Guid.NewGuid().ToString() + ".txt";
path = Path.Combine(_environment.ContentRootPath, "TempFile", tempFilename);
var ms = new MemoryStream();
await fileEntry.Data.CopyToAsync(ms);
using (FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write))
{
ms.WriteTo(file);
}
DataTable dt = new DataTable();
using (TextReader tr = File.OpenText("TempFile/" + tempFilename))
{
string line;
_temprow = 1;
while ((line = tr.ReadLine()) != null && _temprow <= 2)
{
string[] items = line.Split('\t');
if (_temprow == 1)
{
for (int i = 0; i < items.Length; i++)
{
if (FirstLineIsHeader == true)
{
_tempheader = items[i].ToString();
}
else
{
_tempheader = "Column" + (i + 1).ToString();
_tempvalue = items[i].ToString();
}
Templates.Add(new TemplateFileListsDTO
{
tmp_no = i,
tmp_header = _tempheader,
tmp_value = _tempvalue
}
);
}
}
else if (_temprow == 2 && FirstLineIsHeader == true)
{
for (int i = 0; i < items.Length; i++)
{
TemplateFileListsDTO template = Templates.Where(t => t.tmp_no == i).FirstOrDefault();
template.tmp_value = items[i].ToString();
}
}
_temprow++;
}
}
File.Delete(path);
}
#endregion
else
{
string tempFilename = Guid.NewGuid().ToString() + ".dbf";
path = Path.Combine(_environment.ContentRootPath, "TempFile", tempFilename);
bool exists = System.IO.Directory.Exists(Path.Combine(_environment.ContentRootPath, "TempFile"));
if (!exists)
{
System.IO.Directory.CreateDirectory(Path.Combine(_environment.ContentRootPath, "TempFile"));
}
using (FileStream file = new FileStream(path, FileMode.Create))
{
try{
await fileEntry.Data.CopyToAsync(file);
}
catch (Exception ex)
{
}
finally {
file.Flush();
}
}
//using (var dbfTable = new DbfTable("TempFile/" + tempFilename, Encoding.GetEncoding(1252)))
using (var dbfTable = new DbfTable("TempFile/" + tempFilename, Encoding.GetEncoding(874)))
{
var header = dbfTable.Header;
var recordCount = header.RecordCount;
var column = 1;
if (FirstLineIsHeader == true)
{
foreach (var dbfColumn in dbfTable.Columns)
{
//var name = dbfColumn.Name;
//var columnType = dbfColumn.ColumnType;
//var length = dbfColumn.Length;
//var decimalCount = dbfColumn.DecimalCount;
Templates.Add(new TemplateFileListsDTO
{
tmp_no = column,
tmp_header = dbfColumn.Name
}
);
column++;
}
}
else
{
foreach (var dbfColumn in dbfTable.Columns)
{
Templates.Add(new TemplateFileListsDTO
{
tmp_no = column,
tmp_header = "Column" + (column).ToString()
}
);
column++;
}
}
column = 1;
_temprow = 1;
var dbfRecord = new DbfRecord(dbfTable);
while (dbfTable.Read(dbfRecord) && _temprow < 2)
{
foreach (var dbfValue in dbfRecord.Values)
{
TemplateFileListsDTO template = Templates.Where(t => t.tmp_no == column).FirstOrDefault();
if (dbfValue != null)
{
template.tmp_value = dbfValue.ToString();
}
//var stringValue = dbfValue.ToString();
//var obj = dbfValue.GetValue();
column++;
}
_temprow++;
}
}
File.Delete(path);
}
}
catch (Exception ex)
{
return(Templates);
}
return(Templates);
}
// to upload file
public async Task UploadAsync(IFileListEntry fileEntry, string filename, int initializatorId, string fileTypeName)
{
try
{
// create directory with initializator id
string directory = environment.WebRootPath + "\\Uploads\\Initializator_" + initializatorId;
if (!Directory.Exists(directory))
{
DirectoryInfo di = Directory.CreateDirectory(directory);
}
Debug.Print(directory);
//directory = environment.WebRootPath + "\\uploads\\" + companyId + "\\" + spesId;
//if (!Directory.Exists(directory))
//{
// DirectoryInfo di = Directory.CreateDirectory(directory);
//}
// save file in initializator directory
var path = Path.Combine("wwwroot", directory, filename);
var ms = new MemoryStream();
await fileEntry.Data.CopyToAsync(ms);
using (FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write))
{
ms.WriteTo(file);
}
Debug.Print(path);
// Add file names with ";" to parse later
Initializers initializer = db.Initializers.Find(initializatorId);
if (fileTypeName.Contains("заявление"))
{
initializer.FilesNamesZayavleniye += filename + ";";
}
if (fileTypeName.Contains("представление"))
{
initializer.FilesNamesPredstavleniye += filename + ";";
}
if (fileTypeName.Contains("трудовая"))
{
initializer.FilesNamesTrudovaya += filename + ";";
}
if (fileTypeName.Contains("награда"))
{
initializer.FilesNamesNagrady += filename + ";";
}
if (fileTypeName.Contains("выписка"))
{
initializer.FilesNamesVypyska += filename + ";";
}
Debug.Print(fileTypeName);
Debug.Print(initializer.FilesNamesZayavleniye);
Debug.Print(initializer.FilesNamesPredstavleniye);
Debug.Print(initializer.FilesNamesTrudovaya);
Debug.Print(initializer.FilesNamesNagrady);
Debug.Print(initializer.FilesNamesVypyska);
// Update Initializers files names
db.Initializers.Update(initializer);
await db.SaveChangesAsync();
}
catch (Exception exp)
{
Debug.Print(exp.Message);
}
}
public SelectedImage(IFileListEntry file)
{
_file = file;
}
public async Task <List <HISUploadErrorMessageDTO> > ValidateAndUploadFileSPFileAsync(IFileListEntry fileEntry
, HISUploadDataDTO HISfileOwner
, HISFileTemplateDTO HISTemplateActive)
{
List <HISUploadErrorMessageDTO> ErrorMessage = new List <HISUploadErrorMessageDTO>();
try
{
string path = "";
List <ParameterDTO> objParamList = new List <ParameterDTO>();
var searchModel = new ParameterDTO()
{
prm_code_major = "UPLOAD_PATH"
};
const bool FIRSTROW_IS_HEADER = true;
const int colRefNo = 0;
const int colHNNo = 1;
const int colLabNo = 2;
const int colSpecDate = 3;
var formateDate = HISTemplateActive.hft_date_format;
var COL_REF_NO = HISTemplateActive.hft_field1; // "Ref No";
var COL_HN_NO = HISTemplateActive.hft_field2; //"HN";
var COL_LAB_NO = HISTemplateActive.hft_field3; // "Lab";
var COL_DATE = HISTemplateActive.hft_field4; //"Date";
objParamList = await _apiHelper.GetDataListByModelAsync <ParameterDTO, ParameterDTO>("dropdownlist_api/GetParameterList", searchModel);
if (objParamList.FirstOrDefault(x => x.prm_code_minor == "PATH") != null)
{
path = objParamList.FirstOrDefault(x => x.prm_code_minor == "PATH").prm_value;
}
else
{
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'E',
hfu_Err_type = "E",
hfu_Err_no = 1,
hfu_Err_Column = "",
hfu_Err_Message = "ไม่พบ Config PATH กรุณาติดต่อผู้ดูแลระบบ "
});
return(ErrorMessage);
}
string str_CurrentDate = DateTime.Now.ToString("yyyyMMdd");
path = Path.Combine(path, str_CurrentDate, HISfileOwner.hfu_hos_code);
bool exists = System.IO.Directory.Exists(path);
if (!exists)
{
System.IO.Directory.CreateDirectory(path);
}
path = Path.Combine(path, fileEntry.Name);
using (FileStream file = new FileStream(path, FileMode.Create))
{
try
{
await fileEntry.Data.CopyToAsync(file);
}
catch (Exception ex)
{
}
finally
{
file.Flush();
}
}
#region ReadExcel
if (Path.GetExtension(fileEntry.Name) == ".xls" || Path.GetExtension(fileEntry.Name) == ".xlsx")
{
ExcelDataSetConfiguration option = new ExcelDataSetConfiguration();
DataSet result = new DataSet();
using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
if (FIRSTROW_IS_HEADER == true)
{
result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
}
);
}
else
{
result = reader.AsDataSet();
}
while (reader.Read())
{
var strFileFormatDate = reader.GetNumberFormatString(3);
if (strFileFormatDate.ToLower() != formateDate.ToLower())
{
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'E',
hfu_Err_type = "E",
hfu_Err_no = 1,
hfu_Err_Column = "",
hfu_Err_Message = "Column Date ต้องอยู่ในรูปแบบ " + formateDate
});;
return(ErrorMessage);
}
}
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'I',
hfu_Err_type = "I",
hfu_Err_no = 1,
hfu_Err_Column = "Total",
hfu_Err_Message = result.Tables[0].Rows.Count.ToString()
});
}
}
#endregion
//---------- Validate Data in File -------------
var dataTable = result.Tables[0];
// Check column Exist
Boolean columnExists = result.Tables[0].Columns.Contains(COL_REF_NO) &&
result.Tables[0].Columns.Contains(COL_HN_NO) &&
result.Tables[0].Columns.Contains(COL_LAB_NO) &&
result.Tables[0].Columns.Contains(COL_DATE);
if (columnExists == false)
{
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'E',
hfu_Err_type = "C",
hfu_Err_no = 1,
hfu_Err_Column = COL_REF_NO,
hfu_Err_Message = "ไม่พบ Column " + COL_REF_NO
});
}
for (var i = 0; i < dataTable.Rows.Count; i++)
{
for (var j = 0; j < 4; j++)
{
var data = dataTable.Rows[i][j];
// Check column not null
if (string.IsNullOrEmpty(data.ToString()))
{
string columnError = "";
if (j == colRefNo)
{
columnError = COL_REF_NO;
}
else if (j == colHNNo)
{
columnError = COL_HN_NO;
}
else if (j == colLabNo)
{
columnError = COL_LAB_NO;
}
else if (j == colSpecDate)
{
columnError = COL_DATE;
}
var chkErrExist = ErrorMessage.FirstOrDefault(e => e.hfu_status == 'W' && e.hfu_Err_no == i + 2);
if (chkErrExist == null)
{
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'W',
hfu_Err_type = columnError,
hfu_Err_no = i + 2,
hfu_Err_Column = "Required",
hfu_Err_Message = "Field is required"
});
}
else
{
//ErrorMessage.Where(e => e.hfu_status == 'W' && e.hfu_Err_no == i + 1).
// Select(n => { n.hfu_Err_Message = ""; return n; }).ToList();
chkErrExist.hfu_Err_type += ',' + columnError;
}
}
// Check Date format incorrect
//if (j == colSpecDate)
//{
// string dateString = data.ToString();
// DateTime dt;
// //string[] formateDate = { HISTemplateActive.hft_date_format }; // dd/MM/yyyy
// // en-GB , en-US
// //var formateDate = HISTemplateActive.hft_date_format;
// try
// {
// dt = DateTime.Parse(dateString,new CultureInfo("en-US"));
// }
// catch (Exception ex)
// {
// ErrorMessage.Add(new HISUploadErrorMessageDTO
// {
// hfu_status = 'E',
// hfu_Err_type = "E",
// hfu_Err_no = 1,
// hfu_Err_Column = "",
// hfu_Err_Message = "Column Date ต้องอยู่ในรูปแบบ " + formateDate
// }); ;
// return ErrorMessage;
// }
//}
}
}
var chkError = ErrorMessage.FirstOrDefault(x => x.hfu_status == 'E');
if (chkError != null)
{
File.Delete(path);
}
else
{
ErrorMessage.Add(new HISUploadErrorMessageDTO
{
hfu_status = 'I',
hfu_Err_type = "P",
hfu_Err_no = 1,
hfu_Err_Column = "path",
hfu_Err_Message = path
});
}
}
}
catch (Exception ex)
{
//create log
}
return(ErrorMessage);
}
public async Task UploadAsync(IFileListEntry fileListEntry, string sFolder)
{
path = @"C:\inetpub\publish\wwwroot\" + sFolder + "\\" + fileListEntry.Name;
ms = new MemoryStream();
await fileListEntry.Data.CopyToAsync(ms);
}
protected void HandleFileSelected(IFileListEntry[] files)
{
Image = files.FirstOrDefault();
}
public static bool IsNotAccepted(this IFileListEntry file)
{
return(!file.IsAccepted());
}