public List <HISFileTemplateDTO> GetHISFileTemplate_Actice_WithModel(HISFileTemplateDTO searchModel)
{
log.MethodStart();
List <HISFileTemplateDTO> objList = new List <HISFileTemplateDTO>();
using (var trans = _db.Database.BeginTransaction())
{
try
{
var objDataList = _db.HISFileTemplateDTOs.FromSqlRaw <HISFileTemplateDTO>("sp_GET_TCHISFileTemplate_Active").ToList();
objList = _mapper.Map <List <HISFileTemplateDTO> >(objDataList);
trans.Commit();
}
catch (Exception ex)
{
// TODO: Handle failure
trans.Rollback();
}
finally
{
trans.Dispose();
}
}
log.MethodFinish();
return(objList);
}
public async Task <HISFileTemplateDTO> GetHISTemplate_Active_Async(HISFileTemplateDTO model)
{
List <HISFileTemplateDTO> objList = new List <HISFileTemplateDTO>();
objList = await _apiHelper.GetDataListByModelAsync <HISFileTemplateDTO, HISFileTemplateDTO>("his_api/GetHISTemplateActive", model);
return(objList.FirstOrDefault());
}
public async Task ExportLabDataWithHIS(IJSRuntime iJSRuntime, LabDataWithHISSearchDTO model, MasterTemplateDTO ActiveMasterTemplate, HISFileTemplateDTO HISTemplateActive)
{
byte[] fileContents;
const int idxColLabNo = 3;
const int idxColSpcDate = 4;
const int idxColHN = 2;
const int idxColRef = 1;
int idcColWhonetConfig = 5;
var lstDynamicColumn = new List <int>();
var dctWhonetColumn = new Dictionary <string, int>();
//const int idxColAdmisDate = 6;
//const int idxColCINI = 7;
int idxRowCurrent = 1;
string COL_REF_NO = HISTemplateActive.hft_field1; // "Ref No";
string COL_HN_NO = HISTemplateActive.hft_field2; //"HN";
string COL_LAB_NO = HISTemplateActive.hft_field3; // "Lab";
string COL_DATE = HISTemplateActive.hft_field4; //"Date";
List <LabDataWithHISDTO> objLabWithRef = new List <LabDataWithHISDTO>();
List <HISDetailDTO> objHISDetail = new List <HISDetailDTO>();
List <WHONETColumnDTO> objWhonetActive = new List <WHONETColumnDTO>();
List <string> objWhonetHISColumn = new List <string>();
WHONETColumnDTO objSearchWhonet = new WHONETColumnDTO();
List <STGLabFileDataDetailDTO> objLabDetail = new List <STGLabFileDataDetailDTO>();
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
//sp_GET_LabDataWithHIS
objLabWithRef = await _apiHelper.GetDataListByModelAsync <LabDataWithHISDTO, LabDataWithHISSearchDTO>("his_api/GetLabDataWithHIS", model);
objHISDetail = await _apiHelper.GetDataListByModelAsync <HISDetailDTO, LabDataWithHISSearchDTO>("his_api/GetHISDetail", model);
objLabDetail = await _apiHelper.GetDataListByModelAsync <STGLabFileDataDetailDTO, LabDataWithHISSearchDTO>("his_api/GetLabDataWithHISDetail", model);
if (objLabWithRef.Count == 0)
{
// Show message No data to export
}
using (var package = new ExcelPackage())
{
var workSheet = package.Workbook.Worksheets.Add("REF_HIS");
// Header Column
workSheet.Cells[idxRowCurrent, idxColRef].Value = COL_REF_NO;
workSheet.Cells[idxRowCurrent, idxColHN].Value = COL_HN_NO;
workSheet.Cells[idxRowCurrent, idxColLabNo].Value = COL_LAB_NO;
workSheet.Cells[idxRowCurrent, idxColSpcDate].Value = COL_DATE;
objSearchWhonet.wnc_mst_code = ActiveMasterTemplate.mst_code;
objWhonetActive = await _apiHelper.GetDataListByModelAsync <WHONETColumnDTO, WHONETColumnDTO>("whonetcolumn_api/Get_List_Active_ByModel", objSearchWhonet);
if (objWhonetActive != null)
{
// ToDo : change wnc_mendatory to wnc_his_export_flag
var objWhonetHIS = objWhonetActive.Where(w => w.wnc_mendatory == true).ToList();
objWhonetHISColumn = objWhonetHIS.Select(s => s.wnc_name).ToList();
//objWhonetHISColumn = new List<string> { "Last name", "First name", "Sex", "Date of birth", "Age", "Location" };
foreach (var obj in objWhonetHISColumn)
{
// Dynamic Column From Whonet Setting
workSheet.Cells[idxRowCurrent, idcColWhonetConfig].Value = obj;
if (!dctWhonetColumn.ContainsKey(obj))
{
dctWhonetColumn.Add(obj, idcColWhonetConfig);
}
//lstDynamicColumn.Add(idcColWhonetConfig);
idcColWhonetConfig++;
}
}
foreach (LabDataWithHISDTO objLab in objLabWithRef)
{
//if (idxRowCurrent == 11) { break; } //for test
idxRowCurrent += 1;
workSheet.Cells[idxRowCurrent, idxColRef].Value = objLab.ref_no;
workSheet.Cells[idxRowCurrent, idxColHN].Value = objLab.hn_no;
workSheet.Cells[idxRowCurrent, idxColLabNo].Value = objLab.lab_no;
workSheet.Cells[idxRowCurrent, idxColSpcDate].Value = objLab.spec_date;
workSheet.Column(idxColSpcDate).Style.Numberformat.Format = "dd/MM/yyyy";
// Note : obj.huh_id ที่เอามา where ต้องหาจาก query มาแล้วว่าเป็นตัวล่าสุด ที่จะ export ออกมา สถานะไม่ใช่ delete ...
//(First) Find HIS Data from Upload HIS
var objHISDetailFromHIS = objHISDetail.Where(w => w.ref_no == objLab.ref_no &&
w.hn_no == objLab.hn_no &&
w.lab_no == objLab.lab_no &&
w.spec_date == objLab.spec_date).ToList();
foreach (var objHIS in objHISDetailFromHIS)
{
if (objWhonetHISColumn.Contains(objHIS.hud_field_name))
{
if (!string.IsNullOrEmpty(objHIS.hud_field_value))
{
workSheet.Cells[idxRowCurrent, dctWhonetColumn[objHIS.hud_field_name]].Value = objHIS.hud_field_value;
}
}
}
//(Second) Find HIS Data from Lab
Guid guidHeaderId = new Guid(objLab.lab_header_id);
var objHISDetailFromLab = objLabDetail.Where(w => w.ldd_ldh_id == guidHeaderId).ToList();
foreach (var obj in objHISDetailFromLab)
{
if (objWhonetHISColumn.Contains(obj.ldd_whonetfield))
{
if (!string.IsNullOrEmpty(obj.ldd_originalvalue))
{
//var DecodeValue = CryptoHelper.UnicodeDecoding(obj.ldd_originalvalue);
if (workSheet.Cells[idxRowCurrent, dctWhonetColumn[obj.ldd_whonetfield]].Value == null)
{
workSheet.Cells[idxRowCurrent, dctWhonetColumn[obj.ldd_whonetfield]].Value = obj.ldd_originalvalue;
}
}
}
}
} // End Loop Lab Data
workSheet.Column(idxColRef).AutoFit();
workSheet.Column(idxColHN).AutoFit();
workSheet.Column(idxColLabNo).AutoFit();
workSheet.Column(idxColSpcDate).AutoFit();
workSheet.Protection.IsProtected = true; // Protect whole sheet
foreach (var colHIS in dctWhonetColumn.Values)
{
workSheet.Column(colHIS).Style.Locked = false;//unlock workSheet
workSheet.Column(1).AutoFit();
}
fileContents = package.GetAsByteArray();
}
try
{
await iJSRuntime.InvokeAsync <HISFileUploadService>(
"saveAsFile",
"HIS_WITH_SP.xlsx",
Convert.ToBase64String(fileContents)
);
}
catch (Exception ex)
{
}
}
public IEnumerable <HISFileTemplateDTO> GetHISTemplateActive([FromBody] HISFileTemplateDTO searchModel)
{
var objReturn = _service.GetHISFileTemplate_Actice_WithModel(searchModel);
return(objReturn);
}
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);
}