static void Main(string[] args)
{
var HISDataDAL = new HISDataDAL();
var HISFileUpload = HISDataDAL.Get_NewHISFileUpload('A');
var HISFileUploadReprocess = HISDataDAL.Get_NewHISFileUpload('R');
if (HISFileUploadReprocess.Count > 0)
{
HISFileUpload.AddRange(HISFileUploadReprocess);
}
string Param_RefNo = "";
string Param_HNNo = "";
string Param_LabNo = "";
string Param_Date = "";
int iDupRecord = 0;
int iMatchRecord = 0;
bool FIRST_ROW_IS_COLUMN = true;
int iFileUploadID = 0;
try
{
Console.WriteLine("Batch Start ... ");
if (HISFileUpload != null)
{
//Update status = P (Processing)
foreach (HISUploadDataDTO HISFile in HISFileUpload)
{
var rowsAffected = HISDataDAL.Update_HISFileUploadStatus(HISFile.hfu_id, 'P', 0, 0, "BATCH");
}
List <HISFileTemplateDTO> TemplateHISActive = HISDataDAL.GetHISFileTemplate_Active();
if (TemplateHISActive.Count > 0)
{
Param_RefNo = TemplateHISActive.FirstOrDefault().hft_field1;
Param_HNNo = TemplateHISActive.FirstOrDefault().hft_field2;
Param_LabNo = TemplateHISActive.FirstOrDefault().hft_field3;
Param_Date = TemplateHISActive.FirstOrDefault().hft_field4;
}
foreach (HISUploadDataDTO HISFile in HISFileUpload)
{
iMatchRecord = 0;
iDupRecord = 0;
iFileUploadID = HISFile.hfu_id;
var strFileName = HISFile.hfu_file_name;
var strFilePath = HISFile.hfu_file_path;
if (!File.Exists(strFilePath))
{
var msgErr = string.Format("Path not found in file {0}", strFileName);
var logw = new LogWriter(msgErr);
continue;
}
if (Path.GetExtension(strFileName) == ".xls" || Path.GetExtension(strFileName) == ".xlsx")
{
List <TRSTGHISFileUploadHeader> HISFileDataHeaderList = new List <TRSTGHISFileUploadHeader>();
List <TRSTGHISFileUploadDetail> HISFileDataDetailList = new List <TRSTGHISFileUploadDetail>();
using (var stream = File.Open(strFilePath, FileMode.Open, FileAccess.Read))
{
DataSet result = new DataSet();
if (Path.GetExtension(strFileName) == ".xls" || Path.GetExtension(strFileName) == ".xlsx")
{
var reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
if (FIRST_ROW_IS_COLUMN == true)
{
result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
}
);
}
else
{
result = reader.AsDataSet();
}
}
var iRow = 0;
string[] columnNames = (from dc in result.Tables[0].Columns.Cast <DataColumn>()
select dc.ColumnName).ToArray();
foreach (DataRow drRow in result.Tables[0].Rows)
{
var strRefNo = drRow[Param_RefNo].ToString();
var strHNNo = drRow[Param_HNNo].ToString();
var strLabNo = drRow[Param_LabNo].ToString();
DateTime?dtDate = null;;
//string formateDate = "dd/MM/yyyy";
var strDate = (DateTime)drRow[Param_Date];
//if (!string.IsNullOrEmpty(strDate))
//{
// try
// {
// dtDate = DateTime.Parse(strDate, new CultureInfo("en-US"));
// }
// catch(Exception ex)
// {
// var logw = new LogWriter(ex.Message);
// Console.WriteLine(ex.Message);
// }
//}
#region InsertHISFileDataHeader
//DateTime StartHeader = DateTime.Now;
TRSTGHISFileUploadHeader objHeader = new TRSTGHISFileUploadHeader();
objHeader.huh_hfu_id = iFileUploadID;
objHeader.huh_template_id = HISFile.hfu_template_id;
objHeader.huh_status = 'F';
objHeader.huh_delete_flag = false;
objHeader.huh_hos_code = HISFile.hfu_hos_code;
objHeader.huh_ref_no = strRefNo;
objHeader.huh_hn_no = strHNNo;
objHeader.huh_lab_no = strLabNo;
//objHeader.huh_date = dtDate;
objHeader.huh_date = strDate;
objHeader.huh_createuser = "******";
objHeader.huh_strdate = strDate.ToString();
//Tuple<int, Boolean> checkDup = HISDataDAL.Get_CheckExistingHeader(objHeader, HISFile.hfu_file_type);
//int seq = checkDup.Item1;
//if (checkDup.Item2) { iDupRecord += 1; }
int refNextSeq = 1;
Boolean refIsDup = false;
//DateTime StartCheckE = DateTime.Now;
HISDataDAL.Get_CheckExistingHeaders(objHeader, HISFile.hfu_file_type, ref refNextSeq, ref refIsDup);
// DateTime EndCheckE = DateTime.Now;
//TimeSpan tsCheckE = EndCheckE - StartCheckE;
//Console.WriteLine("Get_CheckExistingHeaders = {0}", tsCheckE.TotalMilliseconds);
int seq = refNextSeq;
if (refIsDup)
{
iDupRecord += 1;
}
objHeader.huh_seq_no = seq;
objHeader.huh_createdate = DateTime.Now;
//HISFileDataHeaderList.Add(objHeader);
var huh_id = HISDataDAL.Save_HISFileDataHeader(objHeader);
//DateTime EndHeader = DateTime.Now;
//TimeSpan tsHeader = EndHeader - StartHeader;
//Console.WriteLine("Header = {0}", tsHeader.TotalMilliseconds);
#endregion
#region InsertHISFileDataDetail
int nextseq = 1;
//DateTime StartDetail = DateTime.Now;
if (HISFile.hfu_file_type == "HIS")
{
for (int c = 4; c <= (columnNames.Length - 1); c++)
{
string value = "";
if (!drRow[columnNames[c]].Equals(null))
{
value = drRow[columnNames[c]].ToString();
}
TRSTGHISFileUploadDetail objDetail = new TRSTGHISFileUploadDetail();
objDetail.hud_id = (huh_id * 100) + c;
objDetail.hud_status = 'F';
objDetail.hud_huh_id = huh_id;
objDetail.hud_field_name = columnNames[c];
objDetail.hud_field_value = value;
objDetail.hud_createuser = "******";
objDetail.hud_createdate = DateTime.Now;
//Tuple<int, Boolean> checkDupDetail = HISDataDAL.Get_CheckExistingDetail(objHeader, objDetail);
//if (!string.IsNullOrEmpty(value))
//{
// nextseq = checkDupDetail.Item1;
// //if (checkDupDetail.Item2) { iDupRecord += 1; } // Dup คือนับของ Header
//}
objDetail.hud_seq_no = nextseq;
HISFileDataDetailList.Add(objDetail);
}
}
#endregion
//DateTime EndDetail = DateTime.Now;
//TimeSpan tsDetail = EndDetail - StartDetail;
//Console.WriteLine("Detail = {0}", tsDetail.TotalMilliseconds);
iRow++;
} // end for
//var objHISHeader = HISDataDAL.Save_HISFileDataHeaderList(HISFileDataHeaderList);
if (HISFile.hfu_file_type == "HIS" && HISFileDataDetailList.Count != 0)
{
//DateTime StartSave_HISFileDataDetail = DateTime.Now;
var ldd = HISDataDAL.Save_HISFileDataDetail(HISFileDataDetailList);
//DateTime EndSave_HISFileDataDetail = DateTime.Now;
//TimeSpan tsSaveDetail = EndSave_HISFileDataDetail - StartSave_HISFileDataDetail;
//Console.WriteLine("Save_HISFileDataDetail = {0}", tsSaveDetail.TotalMilliseconds);
}
}
}
else
{
// support only .xls .xlsx file
}
iMatchRecord = HISDataDAL.GetLabDataWithHISMatching(HISFile.hfu_hos_code, HISFile.hfu_id);
//Update status = F (Finish)
var rowsAffected2 = HISDataDAL.Update_HISFileUploadStatus(HISFile.hfu_id, 'F', iMatchRecord, iDupRecord, "BATCH");
}
}
Console.WriteLine("Batch Complete ...");
var log_end = new LogWriter("Batch Complete ...");
}
catch (Exception ex)
{
var rowsAffected = HISDataDAL.Update_HISFileUploadStatus(iFileUploadID, 'E', 0, 0, "BATCH");
var d = ex.Message;
var logw = new LogWriter(d);
Console.WriteLine(d);
}
}
public Tuple <int, Boolean> Get_CheckExistingDetail(TRSTGHISFileUploadHeader modelHeader, TRSTGHISFileUploadDetail modelDetail)
{
int iNextSeqNo = 1;
bool blnDup = false;
var currentDateTime = DateTime.Now;
using (var _db = new HISDataContext())
{
using (var trans = _db.Database.BeginTransaction())
{
try
{
var objResult = _db.TRSTGHISFileUploadDetails.FromSqlRaw <TRSTGHISFileUploadDetail>("sp_UPLOAD_HIS_CheckExisting_Detail {0},{1},{2},{3},{4},{5},{6},{7}",
modelHeader.huh_hos_code
, modelHeader.huh_ref_no
, modelHeader.huh_lab_no
, modelHeader.huh_hn_no
, modelHeader.huh_date
, modelHeader.huh_hfu_id
, modelDetail.hud_field_name
, modelDetail.hud_field_value
).AsEnumerable();
if (objResult != null)
{
foreach (var obj in objResult)
{
iNextSeqNo = obj.hud_seq_no + 1;
blnDup = true;
obj.hud_status = 'D';
obj.hud_updateuser = "******";
obj.hud_updatedate = currentDateTime;
}
_db.SaveChanges();
}
trans.Commit();
}
catch (Exception ex)
{
// TODO: Handle failure
trans.Rollback();
}
finally
{
trans.Dispose();
}
}
}
return(Tuple.Create(iNextSeqNo, blnDup));
}
