public List <LabDataWithHISDTO> GetLabDataWithHIS(LabDataWithHISSearchDTO searchModel)
{
log.MethodStart();
List <LabDataWithHISDTO> objList = new List <LabDataWithHISDTO>();
using (var trans = _db.Database.BeginTransaction())
{
try
{
var objDataList = _db.LabDataWithHISDTOs.FromSqlRaw <LabDataWithHISDTO>("sp_GET_LabDataWithHIS {0} ,{1} ,{2}"
, searchModel.hos_code
, searchModel.start_date_str
, searchModel.end_date_str).ToList();
objList = _mapper.Map <List <LabDataWithHISDTO> >(objDataList);
trans.Commit();
}
catch (Exception ex)
{
// TODO: Handle failure
trans.Rollback();
}
finally
{
trans.Dispose();
}
}
log.MethodFinish();
return(objList);
}
public async Task <List <LabDataWithHISDTO> > GetLabDataWithHISByModelAsync(LabDataWithHISSearchDTO model)
{
List <LabDataWithHISDTO> objList = new List <LabDataWithHISDTO>();
objList = await _apiHelper.GetDataListByModelAsync <LabDataWithHISDTO, LabDataWithHISSearchDTO>("his_api/GetLabDataWithHIS", model);
return(objList);
}
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 <STGLabFileDataDetailDTO> GetLabDataWithHISDetail([FromBody] LabDataWithHISSearchDTO searchModel)
{
var objReturn = _service.GetLabDataWithHISDetail(searchModel);
return(objReturn);
}
public IEnumerable <LabDataWithHISDTO> GetLabDataWithHIS([FromBody] LabDataWithHISSearchDTO searchModel)
{
var objReturn = _service.GetLabDataWithHIS(searchModel);
return(objReturn);
}
public IEnumerable <HISDetailDTO> GetHISUploadDetail([FromBody] LabDataWithHISSearchDTO searchModel)
{
var objReturn = _service.GetSTGHISUploadDetail(searchModel);
return(objReturn);
}
public async Task ExportLabDataWithHIS(IJSRuntime iJSRuntime, LabDataWithHISSearchDTO model, MasterTemplateDTO ActiveMasterTemplate)
{
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>();
//const int idxColAdmisDate = 6;
//const int idxColCINI = 7;
int idxRowCurrent = 1;
List <LabDataWithHISDTO> objLabHIS = new List <LabDataWithHISDTO>();
List <WHONETColumnDTO> objWhonetActive = new List <WHONETColumnDTO>();
WHONETColumnDTO objSearchWhonet = new WHONETColumnDTO();
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage())
{
var workSheet = package.Workbook.Worksheets.Add("REF_HIS");
// Header Column
workSheet.Cells[idxRowCurrent, idxColRef].Value = "Ref";
workSheet.Cells[idxRowCurrent, idxColHN].Value = "HN";
workSheet.Cells[idxRowCurrent, idxColLabNo].Value = "Lab No.";
workSheet.Cells[idxRowCurrent, idxColSpcDate].Value = "Date";
// Dynamic Column From Whonet Setting
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();
foreach (var obj in objWhonetHIS)
{
workSheet.Cells[idxRowCurrent, idcColWhonetConfig].Value = obj.wnc_name;
lstDynamicColumn.Add(idcColWhonetConfig);
idcColWhonetConfig++;
}
}
//workSheet.Cells[idxRowCurrent, idxColAdmisDate].Value = "Admission Date";
//workSheet.Cells[idxRowCurrent, idxColCINI].Value = "CI/NI";
objLabHIS = await _apiHelper.GetDataListByModelAsync <LabDataWithHISDTO, LabDataWithHISSearchDTO>("his_api/GetLabDataWithHIS", model);
foreach (LabDataWithHISDTO obj in objLabHIS)
{
idxRowCurrent += 1;
workSheet.Cells[idxRowCurrent, idxColRef].Value = obj.ref_no;
workSheet.Cells[idxRowCurrent, idxColHN].Value = obj.hn_no;
workSheet.Cells[idxRowCurrent, idxColLabNo].Value = obj.lab_no;
workSheet.Cells[idxRowCurrent, idxColSpcDate].Value = obj.spec_date;
//if ((obj.admission_date.HasValue)) { workSheet.Cells[idxRowCurrent, idxColAdmisDate].Value = obj.admission_date; }
//if (!string.IsNullOrEmpty(obj.cini)) { workSheet.Cells[idxRowCurrent, idxColCINI].Value = obj.cini; }
}
workSheet.Protection.IsProtected = true; // Protect whole sheet
foreach (var colHIS in lstDynamicColumn)
{
workSheet.Column(colHIS).Style.Locked = false;//unlock column
}
fileContents = package.GetAsByteArray();
}
try
{
await iJSRuntime.InvokeAsync <HISFileUploadService>(
"saveAsFile",
"HIS_WITH_SP.xlsx",
Convert.ToBase64String(fileContents)
);
}
catch (Exception ex)
{
}
}
