public List <WHONetMappingListsDTO> GetWHONetMappingListWithModel(WHONetMappingSearch searchModel)
{
log.MethodStart();
List <WHONetMappingListsDTO> objList = new List <WHONetMappingListsDTO>();
using (var trans = _db.Database.BeginTransaction())
{
try
{
objList = _db.WHONetMappingListsDTOs.FromSqlRaw <WHONetMappingListsDTO>("sp_GET_TRWHONetMappingList {0}, {1}", searchModel.wnm_mappingid, searchModel.wnm_mst_code).ToList();
trans.Commit();
}
catch (Exception ex)
{
// TODO: Handle failure
trans.Rollback();
}
finally
{
trans.Dispose();
}
}
log.MethodFinish();
return(objList);
}
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 IEnumerable <WHONetMappingListsDTO> Get_WHONetMappingListByModel([FromBody] WHONetMappingSearch searchModel)
{
var objReturn = _service.GetWHONetMappingListWithModel(searchModel);
return(objReturn);
}
public async Task <List <WHONetMappingListsDTO> > GetWHONetMappingListByModelAsync(WHONetMappingSearch searchData)
{
List <WHONetMappingListsDTO> objList = new List <WHONetMappingListsDTO>();
objList = await _apiHelper.GetDataListByModelAsync <WHONetMappingListsDTO, WHONetMappingSearch>("mapping_api/Get_WHONetMappingListByModel", searchData);
return(objList);
}
