public int Save_HISFileDataHeader(TRSTGHISFileUploadHeader model) { var currentDateTime = DateTime.Now; int objReturn = 0; using (var _db = new HISDataContext()) { using (var trans = _db.Database.BeginTransaction()) { try { _db.TRSTGHISFileUploadHeaders.Add(model); _db.SaveChanges(); trans.Commit(); objReturn = model.huh_id; } catch (Exception ex) { // TODO: Handle failure trans.Rollback(); } finally { trans.Dispose(); } } } return(objReturn); }
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)); }
public string Get_CheckExistingHeaders(TRSTGHISFileUploadHeader model, string mp_filetype, ref int refNextSeq, ref bool refIsDup) { string returnvalue = ""; //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.TRSTGHISFileUploadHeaders.FromSqlRaw <TRSTGHISFileUploadHeader>("sp_UPLOAD_HIS_CheckExisting_SP_Header {0},{1},{2},{3},{4},{5},{6}", model.huh_hos_code, model.huh_ref_no, model.huh_lab_no, model.huh_hn_no, model.huh_date, model.huh_hfu_id, mp_filetype ).AsEnumerable().FirstOrDefault(); if (objResult != null) { refNextSeq = objResult.huh_seq_no + 1; refIsDup = true; objResult.huh_status = 'D'; objResult.huh_delete_flag = true; objResult.huh_updateuser = "******"; objResult.huh_updatedate = currentDateTime; _db.SaveChanges(); } trans.Commit(); returnvalue = "OK"; } catch (Exception ex) { // TODO: Handle failure trans.Rollback(); } finally { trans.Dispose(); } } } return(returnvalue); }
public int Get_HeaderIdForMultipleline(TRSTGHISFileUploadHeader model) { int objReturn = 0; using (var _db = new HISDataContext()) { using (var trans = _db.Database.BeginTransaction()) { try { var objResult = _db.TRSTGHISFileUploadHeaders.FirstOrDefault(x => x.huh_hfu_id == model.huh_hfu_id && x.huh_hos_code == model.huh_hos_code && x.huh_ref_no == model.huh_ref_no && x.huh_lab_no == model.huh_lab_no && x.huh_hn_no == model.huh_hn_no && x.huh_date == model.huh_date && x.huh_delete_flag == false); if (objResult != null) { objReturn = objResult.huh_id; } } catch (Exception ex) { // TODO: Handle failure trans.Rollback(); } finally { trans.Dispose(); } } } return(objReturn); }
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); } }
static void Main(string[] args) { var HISDataDAL = new HISDataDAL(); var HISFileUpload = HISDataDAL.Get_NewHISFileUpload('N'); 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; var strFileType = "SP"; bool FIRST_ROW_IS_COLUMN = true; try { 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 <TCParameter> ParameterList = HISDataDAL.GetParameter(); if (ParameterList.Count != 0) { Param_RefNo = ParameterList.FirstOrDefault(x => x.prm_code_minor == "REF_NO").prm_value; Param_HNNo = ParameterList.FirstOrDefault(x => x.prm_code_minor == "HN").prm_value; Param_LabNo = ParameterList.FirstOrDefault(x => x.prm_code_minor == "LAB_NO").prm_value; Param_Date = ParameterList.FirstOrDefault(x => x.prm_code_minor == "DATE").prm_value; } foreach (HISUploadDataDTO HISFile in HISFileUpload) { iMatchRecord = 0; iDupRecord = 0; var iFileUploadID = HISFile.hfu_id; var strFileName = HISFile.hfu_file_name; var strFilePath = HISFile.hfu_file_path; if (Path.GetExtension(strFileName) == ".xls" || Path.GetExtension(strFileName) == ".xlsx") { 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") { ExcelDataSetConfiguration option = new ExcelDataSetConfiguration(); //var reader = ExcelReaderFactory.CreateReader(stream); 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; var strDate = drRow[Param_Date].ToString(); if (!string.IsNullOrEmpty(strDate)) { dtDate = (DateTime)drRow[Param_Date]; } #region InsertHISFileDataHeader TRSTGHISFileUploadHeader objHeader = new TRSTGHISFileUploadHeader(); objHeader.huh_hfu_id = iFileUploadID; objHeader.huh_template_id = HISFile.hfu_template_id; objHeader.huh_status = 'N'; 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_createuser = "******"; int seq = HISDataDAL.Get_CheckExistingHeader(objHeader, strFileType); objHeader.huh_seq_no = seq; objHeader.huh_createdate = DateTime.Now; var huh_id = HISDataDAL.Save_HISFileDataHeader(objHeader); // else if (FieldDateType == "System.DateTime") // { // objModel.ldh_cdate = (DateTime)row[DATE]; // int year = objModel.ldh_cdate.Value.Year; // if (year > DateTime.Now.Year) // { // objModel.ldh_cdate = objModel.ldh_cdate.Value.AddYears(-543); // } // } #endregion #region TRHISFileUploadSummary //////string strErrColumn = ""; //////string strErrMessage = ""; //////int iErrorRow = 0; //////bool blnError = false; //////if (string.IsNullOrEmpty(strRefNo)) //////{ ////// strErrColumn = Param_RefNo; ////// strErrMessage = "Field is required"; ////// iErrorRow = iRow +1 ; ////// blnError = true; //////} //////if (string.IsNullOrEmpty(strHNNo)) //////{ ////// strErrColumn = Param_HNNo; ////// strErrMessage = "Field is required"; ////// iErrorRow = iRow + 1; ////// blnError = true; //////} //////if (string.IsNullOrEmpty(strLabNo)) //////{ ////// strErrColumn = Param_LabNo; ////// strErrMessage = "Field is required"; ////// iErrorRow = iRow + 1; ////// blnError = true; //////} //////if (!dtDate.HasValue) //////{ ////// strErrColumn = Param_Date; ////// strErrMessage = "Field is required"; ////// iErrorRow = iRow + 1; ////// blnError = true; //////} //////if (blnError) //////{ ////// TRHISFileUploadSummary objSummary = new TRHISFileUploadSummary(); ////// objSummary.hus_hfu_id = iFileUploadID; ////// objSummary.hus_error_fieldname = strErrColumn; ////// objSummary.hus_error_fielddescr = strErrMessage; ////// objSummary.hus_error_fieldrecord = iErrorRow.ToString(); ////// objSummary.hus_delete_flag = false; ////// objSummary.hus_createuser = "******"; ////// objSummary.hus_createdate = DateTime.Now; ////// var complete = HISDataDAL.Save_HISFileUploadSummary(objSummary); ////// if (!complete) ////// { ////// Console.WriteLine("Error Save HISFileUploadSummary"); ////// } //////} #endregion #region InsertHISFileDataDetail if (HISFile.hfu_file_type == "HIS") { for (int c = 4; c <= (columnNames.Length - 1); c++) { string value = ""; if (!drRow[columnNames[c].ToString()].Equals(null)) { value = drRow[columnNames[c].ToString()].ToString(); } HISFileDataDetailList.Add(new TRSTGHISFileUploadDetail { hud_status = 'N', hud_huh_id = huh_id, hud_field_name = columnNames[c].ToString(), hud_field_value = value, hud_createuser = "******", hud_createdate = DateTime.Now }); } } //// Save แยก header/detail ไม่ได้ //var ldd = HISDataDAL.Save_HISFileDataDetail(HISFileDataDetailList); #endregion iRow++; } if (HISFile.hfu_file_type == "HIS" && HISFileDataDetailList.Count != 0) { var ldd = HISDataDAL.Save_HISFileDataDetail(HISFileDataDetailList); } } } else { // support only .xls .xlsx file } //char status; //if (intDupRecord == 0) //{ // status = 'D'; //} //else //{ // status = 'E'; //} //Update status = F (Finish) var rowsAffected2 = HISDataDAL.Update_HISFileUploadStatus(HISFile.hfu_id, 'F', iMatchRecord, iDupRecord, "BATCH"); } } Console.WriteLine("Batch Complete"); } catch (Exception ex) { //var rowsAffected = HISDataDAL.Update_HISFileUploadStatus(HISFile.hfu_id, 'N', 0, 0, "BATCH"); var d = ex.Message; Console.WriteLine(d); } }