protected static FR_L5HI_GHIFHIID_1329 Execute(DbConnection Connection, DbTransaction Transaction, P_L5HI_GHIFHIID_1329 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
#region UserCode
var returnValue = new FR_L5HI_GHIFHIID_1329();
//Put your code here
returnValue.Result = new L5HI_GHIFHIID_1329();
ORM_HEC_HIS_HealthInsurance_Company.Query healthInsurenceQuery = new ORM_HEC_HIS_HealthInsurance_Company.Query();
healthInsurenceQuery.HEC_HealthInsurance_CompanyID = Parameter.HEC_HealthInsurance_CompanyID;
healthInsurenceQuery.Tenant_RefID = securityTicket.TenantID;
healthInsurenceQuery.IsDeleted = false;
List <ORM_HEC_HIS_HealthInsurance_Company> healthInsurances = ORM_HEC_HIS_HealthInsurance_Company.Query.Search(Connection, Transaction, healthInsurenceQuery);
if (healthInsurances.Count == 0)
{
return(null);
}
else
{
ORM_CMN_BPT_BusinessParticipant bpart = ORM_CMN_BPT_BusinessParticipant.Query.Search(Connection, Transaction, new ORM_CMN_BPT_BusinessParticipant.Query
{
CMN_BPT_BusinessParticipantID = healthInsurances[0].CMN_BPT_BusinessParticipant_RefID,
Tenant_RefID = securityTicket.TenantID,
IsDeleted = false
}).FirstOrDefault();
ORM_CMN_COM_CompanyInfo companyInfo = new ORM_CMN_COM_CompanyInfo();
companyInfo.Load(Connection, Transaction, bpart.IfCompany_CMN_COM_CompanyInfo_RefID);
ORM_CMN_UniversalContactDetail ucd = new ORM_CMN_UniversalContactDetail();
ucd.Load(Connection, Transaction, companyInfo.Contact_UCD_RefID);
L5HI_GHIFT_1138 healthInsurance = new L5HI_GHIFT_1138();
healthInsurance.HEC_HealthInsurance_CompanyID = healthInsurances[0].HEC_HealthInsurance_CompanyID;
healthInsurance.DisplayName = bpart.DisplayName;
healthInsurance.HealthInsurance_IKNumber = healthInsurances[0].HealthInsurance_IKNumber;
healthInsurance.Country_ISO = ucd.Country_639_1_ISOCode;
healthInsurance.Town = ucd.Town;
returnValue.Result.HealthInsurance = healthInsurance;
}
return(returnValue);
#endregion UserCode
}
protected static FR_L5HI_GHIFT_1138_Array Execute(DbConnection Connection, DbTransaction Transaction, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
//Leave UserCode region to enable user code saving
#region UserCode
var returnValue = new FR_L5HI_GHIFT_1138_Array();
List <L5HI_GHIFT_1138> healthInsuranceResult = new List <L5HI_GHIFT_1138>();
ORM_HEC_HIS_HealthInsurance_Company.Query healthInsurenceQuery = new ORM_HEC_HIS_HealthInsurance_Company.Query();
healthInsurenceQuery.Tenant_RefID = securityTicket.TenantID;
healthInsurenceQuery.IsDeleted = false;
List <ORM_HEC_HIS_HealthInsurance_Company> healthInsurances = ORM_HEC_HIS_HealthInsurance_Company.Query.Search(Connection, Transaction, healthInsurenceQuery);
foreach (var healthInsurance in healthInsurances)
{
if (healthInsurance.CMN_BPT_BusinessParticipant_RefID == Guid.Empty)
{
continue;
}
L5HI_GHIFT_1138 item = new L5HI_GHIFT_1138();
ORM_CMN_BPT_BusinessParticipant bpart = ORM_CMN_BPT_BusinessParticipant.Query.Search(Connection, Transaction, new ORM_CMN_BPT_BusinessParticipant.Query
{
CMN_BPT_BusinessParticipantID = healthInsurance.CMN_BPT_BusinessParticipant_RefID,
Tenant_RefID = securityTicket.TenantID,
IsDeleted = false
}).FirstOrDefault();
ORM_CMN_COM_CompanyInfo companyInfo = new ORM_CMN_COM_CompanyInfo();
companyInfo.Load(Connection, Transaction, bpart.IfCompany_CMN_COM_CompanyInfo_RefID);
ORM_CMN_UniversalContactDetail ucd = new ORM_CMN_UniversalContactDetail();
ucd.Load(Connection, Transaction, companyInfo.Contact_UCD_RefID);
item.HEC_HealthInsurance_CompanyID = healthInsurance.HEC_HealthInsurance_CompanyID;
item.DisplayName = bpart.DisplayName;
item.HealthInsurance_IKNumber = healthInsurance.HealthInsurance_IKNumber;
item.Country_ISO = ucd.Country_639_1_ISOCode;
item.Town = ucd.Town;
healthInsuranceResult.Add(item);
}
returnValue.Result = healthInsuranceResult.ToArray();
//Put your code here
return(returnValue);
#endregion UserCode
}
protected static FR_Guid Execute(DbConnection Connection, DbTransaction Transaction, P_CAS_CCtSE_1100 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
#region UserCode
var returnValue = new FR_Guid();
Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("de-DE");
Thread.CurrentThread.CurrentUICulture = CultureInfo.GetCultureInfo("de-DE");
//Get statuses from Resources
var FS1 = Properties.Resources.FS1;
var FS2 = Properties.Resources.FS2;
var FS3 = Properties.Resources.FS3;
var FS4 = Properties.Resources.FS4;
var FS5 = Properties.Resources.FS5;
var FS6 = Properties.Resources.FS6;
var FS7 = Properties.Resources.FS7;
var FS8 = Properties.Resources.FS8;
var FS9 = Properties.Resources.FS9;
var FS10 = Properties.Resources.FS10;
var FS11 = Properties.Resources.FS11;
Guid treatment_performed_action_type_id = Guid.Empty;
var treatment_performed_action_type = ORM_HEC_ACT_ActionType.Query.Search(Connection, Transaction, new ORM_HEC_ACT_ActionType.Query()
{
Tenant_RefID = securityTicket.TenantID,
IsDeleted = false,
GlobalPropertyMatchingID = "mm.docconect.doc.app.performed.action.treatment"
}).SingleOrDefault();
if (treatment_performed_action_type == null)
{
treatment_performed_action_type = new ORM_HEC_ACT_ActionType();
treatment_performed_action_type.GlobalPropertyMatchingID = "mm.docconect.doc.app.performed.action.treatment";
treatment_performed_action_type.Creation_Timestamp = DateTime.Now;
treatment_performed_action_type.Modification_Timestamp = DateTime.Now;
treatment_performed_action_type.Tenant_RefID = securityTicket.TenantID;
treatment_performed_action_type.Save(Connection, Transaction);
treatment_performed_action_type_id = treatment_performed_action_type.HEC_ACT_ActionTypeID;
}
else
{
treatment_performed_action_type_id = treatment_performed_action_type.HEC_ACT_ActionTypeID;
}
List <Submitted_Case_Model> allCases = Get_All_Cases_With_Custom_Status.Get_All_Submited_Cases_With_Custom_Status("FS2", securityTicket);
List <Submitted_Case_Model> allCases_FS11 = Get_All_Cases_With_Custom_Status.Get_All_Submited_Cases_With_Custom_Status("FS11", securityTicket);
List <Submitted_Case_Model> newCaseList = new List <Submitted_Case_Model>();
List <Settlement_Model> settlements = new List <Settlement_Model>();
List <PatientDetailViewModel> patientDetailList = new List <PatientDetailViewModel>();
List <DateTime> OldTransmitionDateList = new List <DateTime>();
Dictionary <Guid, int> consentValidToCache = new Dictionary <Guid, int>();
//List<Guid> casesToChange = new List<Guid>();
//List<string> typeList = new List<string>();
List <NegativeResponseModel> NegativeResponseList = new List <NegativeResponseModel>();
var preexaminationsCache = cls_Get_Patient_Preexaminations_on_Tenant.Invoke(Connection, Transaction, securityTicket).Result.GroupBy(t => t.PatientID).ToDictionary(t => t.Key, t => t.GroupBy(c => c.Localization).ToDictionary(d => d.Key, d => d));
DateTime DateForElastic = DateTime.SpecifyKind(DateTime.Now, DateTimeKind.Local);
var casesForReport = cls_Get_Cases_For_Report.Invoke(Connection, Transaction, new P_CAS_GCFR_0910()
{
Status = 2
}, securityTicket).Result;
var casesForReport_FS11 = cls_Get_Cases_For_Report.Invoke(Connection, Transaction, new P_CAS_GCFR_0910()
{
Status = 11
}, securityTicket).Result;
Dictionary <string, string> serviceFeeValueCache = new Dictionary <string, string>();
List <CaseForReportModel> caseModelList = new List <CaseForReportModel>();
foreach (var item in Parameter.CasesToBeChanged)
{
try
{
var errorCase = casesForReport.SingleOrDefault(i => int.Parse(i.PositionNumber) == int.Parse(item.bill_number));
bool shouldChangeToFS8 = false;
if (errorCase == null)
{
errorCase = casesForReport_FS11.SingleOrDefault(i => int.Parse(i.PositionNumber) == int.Parse(item.bill_number));
shouldChangeToFS8 = true;
}
var management_fee_value = "-";
if (!string.IsNullOrEmpty(errorCase.BillingCode))
{
if (!serviceFeeValueCache.ContainsKey(errorCase.BillingCode))
{
var service_fee_value = cls_Get_ServiceFeeValue_for_BillingCode.Invoke(Connection, Transaction, new P_CAS_GSFVfBC_1721()
{
BillingCode = errorCase.BillingCode
}, securityTicket).Result;
if (service_fee_value != null)
{
serviceFeeValueCache.Add(errorCase.BillingCode, service_fee_value.service_fee);
}
}
management_fee_value = serviceFeeValueCache[errorCase.BillingCode];
}
var negativeTransmitionQuery = new ORM_BIL_BillPosition_TransmitionStatus.Query();
negativeTransmitionQuery.Tenant_RefID = securityTicket.TenantID;
negativeTransmitionQuery.IsDeleted = false;
negativeTransmitionQuery.BillPosition_RefID = errorCase.StatusID;
var negativeTransmition = ORM_BIL_BillPosition_TransmitionStatus.Query.Search(Connection, Transaction, negativeTransmitionQuery).ToList();
var caseStatusQuery = new ORM_BIL_BillPosition_TransmitionStatus.Query();
caseStatusQuery.Tenant_RefID = securityTicket.TenantID;
caseStatusQuery.IsDeleted = false;
caseStatusQuery.IsActive = true;
caseStatusQuery.BIL_BillPosition_TransmitionStatusID = errorCase.StatusID;
var caseStatusOld = ORM_BIL_BillPosition_TransmitionStatus.Query.Search(Connection, Transaction, caseStatusQuery).SingleOrDefault();
if (caseStatusOld == null)
{
throw new Exception("Transmition status not found for id: " + errorCase.StatusID);
}
OldTransmitionDateList.Add(caseStatusOld.TransmittedOnDate);
caseStatusOld.IsActive = false;
caseStatusOld.Save(Connection, Transaction);
var caseStatus = new ORM_BIL_BillPosition_TransmitionStatus();
caseStatus.IsDeleted = false;
caseStatus.Creation_Timestamp = DateTime.Now;
caseStatus.Modification_Timestamp = DateTime.Now;
caseStatus.Tenant_RefID = securityTicket.TenantID;
caseStatus.IsActive = true;
caseStatus.PrimaryComment = item.error_message;
caseStatus.BIL_BillPosition_TransmitionStatusID = Guid.NewGuid();
caseStatus.BillPosition_RefID = caseStatusOld.BillPosition_RefID;
caseStatus.TransmitionCode = 5;
caseStatus.TransmittedOnDate = DateTime.Now;// should we use from edifact?
caseStatus.TransmitionStatusKey = caseStatusOld.TransmitionStatusKey;
caseStatus.Save(Connection, Transaction);
if (shouldChangeToFS8)
{
caseStatus.IsActive = false;
caseStatus.Save(Connection, Transaction);
var caseStatus2 = new ORM_BIL_BillPosition_TransmitionStatus();
caseStatus2.IsDeleted = false;
caseStatus2.Creation_Timestamp = DateTime.Now;
caseStatus2.Modification_Timestamp = DateTime.Now;
caseStatus2.Tenant_RefID = securityTicket.TenantID;
caseStatus2.IsActive = true;
caseStatus2.PrimaryComment = item.error_message;
caseStatus2.BIL_BillPosition_TransmitionStatusID = Guid.NewGuid();
caseStatus2.BillPosition_RefID = caseStatusOld.BillPosition_RefID;
caseStatus2.TransmitionCode = 8;
caseStatus2.TransmittedOnDate = DateTime.Now;// should we use from edifact?
caseStatus2.TransmitionStatusKey = caseStatusOld.TransmitionStatusKey;
caseStatus2.Save(Connection, Transaction);
}
Guid caseID = errorCase.CaseID;
NegativeResponseModel negativeResponse = new NegativeResponseModel();
negativeResponse.caseID = caseID;
//casesToChange.Add(caseID);
if (caseStatusOld.TransmitionStatusKey == "aftercare")
{
negativeResponse.plannedActionID = errorCase.IsAftercareID;
negativeResponse.type = "ac";
}
//typeList.Add("ac");
else if (caseStatusOld.TransmitionStatusKey == "treatment")
{
negativeResponse.plannedActionID = errorCase.IsTreatmentID;
negativeResponse.type = "op";
}
else
{
negativeResponse.plannedActionID = errorCase.IsTreatmentID;
negativeResponse.type = caseStatusOld.TransmitionStatusKey;
}
//typeList.Add("op");
NegativeResponseList.Add(negativeResponse);
P_PA_GPDfPID_1124 patientData = cls_Get_Patient_Details_for_PatientID.Invoke(Connection, Transaction, new P_P_PA_GPDfPID_1124()
{
PatientID = errorCase.Patient_RefID
}, securityTicket).Result;
#region Report data
CaseForReportModel caseModel = new CaseForReportModel();
caseModel.HIP = patientData.health_insurance_provider;
caseModel.ContractID = patientData.contractID;
caseModel.HIP_IK = patientData.HealthInsurance_IKNumber;
caseModel.PatientInsuranceNumber = patientData.insurance_id;
caseModel.PatientGender = patientData.gender;
caseModel.PatientStatusNumber = patientData.insurance_status;
caseModel.PatientFirstName = patientData.patient_first_name;
caseModel.PatientLastName = patientData.patient_last_name;
caseModel.PatientBirthday = patientData.birthday;
try
{
if (!consentValidToCache.ContainsKey(patientData.contractID))
{
double DurationOfParticipationConsentinMonths = ORM_CMN_CTR_Contract_Parameter.Query.Search(Connection, Transaction, new ORM_CMN_CTR_Contract_Parameter.Query()
{
IsDeleted = false,
Tenant_RefID = securityTicket.TenantID,
ParameterName = "Duration of participation consent – Month",
Contract_RefID = patientData.contractID
}).SingleOrDefault().IfNumericValue_Value;
consentValidToCache.Add(patientData.contractID, Convert.ToInt32(DurationOfParticipationConsentinMonths));
}
DateTime participationConsentValidTo = patientData.ParticipationConsent.OrderBy(dt => dt.participation_consent_issue_date).FirstOrDefault().participation_consent_issue_date.AddMonths(consentValidToCache[patientData.contractID]);
caseModel.PatientParticipationConsentValidUntil = participationConsentValidTo;
}
catch (Exception ex)
{
caseModel.PatientParticipationConsentValidUntil = DateTime.MinValue;
}
caseModel.CaseNumber = Convert.ToInt32(errorCase.CaseNumber);
caseModel.CaseType = errorCase.CodeName;
try
{
caseModel.Drug = cls_Get_Drug_Details_for_DrugID.Invoke(Connection, Transaction, new P_CAS_GDDfDID_1614()
{
DrugID = errorCase.DrugID
}, securityTicket).Result.drug_name;
}
catch (Exception ex)
{
caseModel.Drug = "-";
}
caseModel.Diagnose = errorCase.IM_PotentialDiagnosis_Name;
caseModel.DiagnoseCode = errorCase.IM_PotentialDiagnosis_Code;
caseModel.Localization = errorCase.IM_PotentialDiagnosisLocalization_Code;
P_CAS_GTCfPIDaDIDaLC_1008 parameterDia = new P_CAS_GTCfPIDaDIDaLC_1008();
parameterDia.PatientID = errorCase.Patient_RefID;
parameterDia.DiagnoseID = errorCase.CodeForType == "treatment" || errorCase.CodeForType == "preexamination" ? errorCase.TreatmentPerformedDiganoseID : errorCase.AftercasePerformedDiagnoseID;
parameterDia.LocalizationCode = errorCase.IM_PotentialDiagnosisLocalization_Code;
parameterDia.PerformedDate = errorCase.CodeForType == "treatment" || errorCase.CodeForType == "preexamination" ? errorCase.TreatmentDate : errorCase.AfterCareDate;
parameterDia.ActionTypeID = treatment_performed_action_type_id;
if (errorCase.CodeForType == "preexamination")
{
if (preexaminationsCache.ContainsKey(errorCase.Patient_RefID) && preexaminationsCache[errorCase.Patient_RefID].ContainsKey(errorCase.IM_PotentialDiagnosisLocalization_Code))
{
caseModel.TreatmentCount = preexaminationsCache[errorCase.Patient_RefID][errorCase.IM_PotentialDiagnosisLocalization_Code].Count(c => c.PreexaminationDate.Date <= errorCase.TreatmentDate.Date);
}
}
else
{
caseModel.TreatmentCount = cls_Get_Treatment_Count_for_PatientID_And_DiagnoseID_and_LocalizationCode.Invoke(Connection, Transaction, parameterDia, securityTicket).Result.treatment_count;
}
caseModel.GPOS = errorCase.BillingCode;
caseModel.TreatmentDay = errorCase.CodeForType == "treatment" || errorCase.CodeForType == "preexamination" ? errorCase.TreatmentDate : errorCase.AfterCareDate;
if (errorCase.CodeForType == "treatment")
{
caseModel.SurgeryDateForThisCase = errorCase.TreatmentDate;
}
else if (errorCase.CodeForType == "preexamination")
{
caseModel.SurgeryDateForThisCase = DateTime.MinValue;
}
else
{
CAS_GTdfA_0936 Trdate = cls_Get_Treatment_Date_for_Aftercare.Invoke(Connection, Transaction, new P_CAS_GTdfA_0936()
{
CaseID = errorCase.CaseID
}, securityTicket).Result;
if (Trdate != null)
{
caseModel.SurgeryDateForThisCase = Trdate.TreatmentDate;
}
}
switch (patientData.gender)
{
case 0:
caseModel.PatientSalutation = "Herr";
break;
case 1:
caseModel.PatientSalutation = "Frau";
break;
default:
caseModel.PatientSalutation = "-";
break;
}
if (!shouldChangeToFS8)
{
caseModel.CurrentStatus = FS5;
}
else
{
caseModel.CurrentStatus = FS8;
}
caseModel.DateOfCurrentStatus = DateTime.Now;
if (!shouldChangeToFS8)
{
caseModel.PreCurrentStatus = FS2;
}
else
{
caseModel.PreCurrentStatus = FS11;
}
caseModel.DateOfPreCurrentStatus = caseStatusOld.TransmittedOnDate;
caseModel.InvoiceNumberForTheHIP = Convert.ToInt32(errorCase.PositionNumber);
caseModel.AmountForThisGPOS = errorCase.NumberForPayment;
caseModel.NumberOfNegativeTry = negativeTransmition.Count.ToString();
caseModel.DateOfTheSubmissionToTheHIP = Parameter.transferToHIPDate;
caseModel.FeedBackOfTheHIP = item.transmition_date;
caseModel.PaymentDate = DateTime.MinValue;
caseModel.DrugOrdered = (errorCase.orderId != Guid.Empty && int.Parse(errorCase.orderStatusCode) != 6) ? "Ja" : "Nein";
caseModel.NoFee = errorCase.IsPatientFeeWaived ? "Ja" : "Nein";
caseModel.InvoiceToPractice = errorCase.SendInvoiceToPractice ? "Ja" : "Nein";
caseModel.OnlyLabelRequired = errorCase.isLabelOnly ? "Ja" : "Nein";
var gposAssignmentCount = cls_Get_Gpos_AssignmentCount_for_GposID.Invoke(Connection, Transaction, new P_CAS_GGPOSACfGPOSID_1252()
{
GposID = errorCase.GposID
}, securityTicket).Result;
if (gposAssignmentCount.AssignmentCount != 0)
{
var is_management_fee_waived = cls_Get_Management_Fee_Property_Value_for_CaseID_and_GposID.Invoke(Connection, Transaction, new P_CAS_GMFPVfCIDaGPOSTID_1749()
{
CaseID = errorCase.CaseID, GposID = errorCase.GposID
}, securityTicket).Result;
caseModel.ManagementFee = is_management_fee_waived == null ? "-" : is_management_fee_waived.PropertyValue == "waived" ? "-" : management_fee_value;
}
else
{
caseModel.ManagementFee = "-";
}
Guid DocID = errorCase.CodeForType == "treatment" || errorCase.CodeForType == "preexamination" ? errorCase.SurgeryDoctor : errorCase.AfterCareDoctor;
DO_GDDfDID_0823 doctorData = cls_Get_Doctor_Details_for_DoctorID.Invoke(Connection, Transaction, new P_DO_GDDfDID_0823()
{
DoctorID = DocID
}, securityTicket).Result.First();
caseModel.BSNR = doctorData.BSNR;
caseModel.PracticeName = doctorData.practice;
caseModel.DocName = doctorData.first_name + " " + doctorData.last_name;
caseModel.LANR = doctorData.lanr;
caseModel.BankAccountHolder = string.IsNullOrEmpty(doctorData.OwnerText) ? "-" : doctorData.OwnerText;
caseModel.BankName = doctorData.BankName == null ? "-" : doctorData.BankName;
caseModel.IBAN = doctorData.IBAN == null ? "-" : doctorData.IBAN;
caseModel.BIC = doctorData.BICCode == null ? "-" : doctorData.BICCode;
caseModelList.Add(caseModel);
#endregion
}
catch (Exception ex)
{
LogUtils.Logger.LogInfo(new LogUtils.LogEntry(ex.StackTrace));
throw new Exception("Bill position: " + item.bill_number, ex);
}
}
//Change in Elastic---------------------------------------------------------------------------------
for (int k = 0; k < NegativeResponseList.Count; k++)
{
var errorCase = allCases.SingleOrDefault(i => i.case_id == NegativeResponseList[k].caseID.ToString() && i.type == NegativeResponseList[k].type);
if (errorCase == null)
{
errorCase = allCases_FS11.SingleOrDefault(i => i.case_id == NegativeResponseList[k].caseID.ToString() && i.type == NegativeResponseList[k].type);
if (errorCase == null)
{
continue;
}
errorCase.status = "FS8";
//Change settlement from Stornierung anhängig to storniert
Settlement_Model settlement = Get_Settlement.GetSettlementForID(NegativeResponseList[k].plannedActionID.ToString(), securityTicket);
settlement.status = "FS8";
settlements.Add(settlement);
PatientDetailViewModel patient_detail = Retrieve_Patients.Get_PatientDetaiForID(settlement.id, securityTicket);
if (patient_detail != null)
{
patient_detail.status = "FS8";
patientDetailList.Add(patient_detail);
}
}
else
{
errorCase.status = "FS5";
}
errorCase.status_date = DateTime.Now;
errorCase.status_date_string = DateTime.Now.ToString("dd.MM.yyyy");
newCaseList.Add(errorCase);
}
if (settlements.Count > 0)
{
Add_new_Settlement.Import_Settlement_to_ElasticDB(settlements, securityTicket.TenantID.ToString());
}
if (patientDetailList.Count > 0)
{
Add_New_Patient.ImportPatientDetailsToElastic(patientDetailList, securityTicket.TenantID.ToString());
}
if (newCaseList.Count > 0)
{
Add_New_Submitted_Case.Import_Submitted_Case_Data_to_ElasticDB(newCaseList, securityTicket.TenantID.ToString());
List <Documents> documentList = new List <Documents>();
string hipID = Parameter.hipId;
var healthInsuranceCompanyQuery = new ORM_HEC_HIS_HealthInsurance_Company.Query();
healthInsuranceCompanyQuery.IsDeleted = false;
healthInsuranceCompanyQuery.Tenant_RefID = securityTicket.TenantID;
healthInsuranceCompanyQuery.HealthInsurance_IKNumber = hipID;
var helathInsurance = ORM_HEC_HIS_HealthInsurance_Company.Query.Search(Connection, Transaction, healthInsuranceCompanyQuery).Single();
var businessParticipantQuery = new ORM_CMN_BPT_BusinessParticipant.Query();
businessParticipantQuery.CMN_BPT_BusinessParticipantID = helathInsurance.CMN_BPT_BusinessParticipant_RefID;
businessParticipantQuery.IsDeleted = false;
businessParticipantQuery.Tenant_RefID = securityTicket.TenantID;
var businessParticipant = ORM_CMN_BPT_BusinessParticipant.Query.Search(Connection, Transaction, businessParticipantQuery).Single();
//Save Edifact
string edi_path = System.IO.Path.GetTempPath() + Parameter.edi_name;
System.IO.File.WriteAllText(edi_path, Parameter.edi_message);
List <string> files = new List <string>();
files.Add(edi_path);
string zipPath = System.IO.Path.GetTempPath() + Parameter.edi_name + ".zip";
ZipFIlesUtils.AddToArchive(zipPath, files);
string earliestDate = OldTransmitionDateList.OrderBy(d => d).First().ToString("dd.MM.yyyy");
string lastDate = OldTransmitionDateList.OrderBy(d => d).Last().ToString("dd.MM.yyyy");
Documents documentEdifact = new Documents();
documentEdifact.documentName = "Import von " + earliestDate + " - " + lastDate;
documentEdifact.documentOutputLocation = zipPath;
documentEdifact.receiver = businessParticipant.DisplayName;
documentEdifact.mimeType = "Application/Edifact_Error";
documentList.Add(documentEdifact);
Documents documentExcel = new Documents();
documentExcel.documentName = "ExcelReport" + DateTime.Now.ToString("dd.MM.yyyy_HH.mm");
documentExcel.documentOutputLocation = GenerateReportCases.CreateCaseXlsReport(caseModelList, documentExcel.documentName);
documentExcel.mimeType = UtilMethods.GetMimeType(documentExcel.documentOutputLocation);
documentExcel.receiver = "MM";
documentList.Add(documentExcel);
foreach (var item in documentList)
{
MemoryStream ms = new MemoryStream(File.ReadAllBytes(item.documentOutputLocation));
byte[] byteArrayFile = ms.ToArray();
var _providerFactory = ProviderFactory.Instance;
var documentProvider = _providerFactory.CreateDocumentServiceProvider();
var uploadedFrom = HttpContext.Current.Request.UserHostAddress;
Guid documentID = documentProvider.UploadDocument(byteArrayFile, item.documentOutputLocation, securityTicket.SessionTicket, uploadedFrom);
string downloadURL = documentProvider.GenerateImageThumbnailLink(documentID, securityTicket.SessionTicket, false, 200);
P_ARCH_UD_1326 parameterDoc = new P_ARCH_UD_1326();
parameterDoc.DocumentID = documentID;
parameterDoc.Mime = item.mimeType;
parameterDoc.DocumentName = item.documentName;
parameterDoc.DocumentDate = DateForElastic;
parameterDoc.Receiver = item.receiver;
parameterDoc.ContractID = item.ContractID;
if (parameterDoc.Mime == "Application/Edifact_Error")
{
parameterDoc.Description = parameterDoc.DocumentName;
}
else
{
parameterDoc.Description = "KV Fehler";
}
cls_Upload_Report.Invoke(Connection, Transaction, parameterDoc, securityTicket);
}
}
return(returnValue);
#endregion UserCode
}
public static void Save_Patients_to_DB(Patient_Model_xls Parameter, bool create_consents, string connectionString, SessionSecurityTicket securityTicket)
{
DbConnection Connection = null;
DbTransaction Transaction = null;
bool cleanupConnection = Connection == null;
bool cleanupTransaction = Transaction == null;
Guid patient_id = Guid.NewGuid();
if (cleanupConnection == true)
{
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(connectionString);
Connection.Open();
}
if (cleanupTransaction == true)
{
Transaction = Connection.BeginTransaction();
}
try
{
Thread.CurrentThread.CurrentUICulture = CultureInfo.GetCultureInfo("de-DE");
ORM_HEC_Patient patients = new ORM_HEC_Patient();
patients.HEC_PatientID = Guid.NewGuid();
patients.Tenant_RefID = securityTicket.TenantID;
patients.Creation_Timestamp = DateTime.Now;
patients.Modification_Timestamp = DateTime.Now;
patients.CMN_BPT_BusinessParticipant_RefID = Guid.NewGuid();
patients.Save(Connection, Transaction);
patient_id = patients.HEC_PatientID;
ORM_CMN_BPT_BusinessParticipant businesParticipantPatient = new ORM_CMN_BPT_BusinessParticipant();
businesParticipantPatient.CMN_BPT_BusinessParticipantID = patients.CMN_BPT_BusinessParticipant_RefID;
businesParticipantPatient.Tenant_RefID = securityTicket.TenantID;
businesParticipantPatient.Creation_Timestamp = DateTime.Now;
businesParticipantPatient.Modification_Timestamp = DateTime.Now;
businesParticipantPatient.IsNaturalPerson = true;
businesParticipantPatient.IfNaturalPerson_CMN_PER_PersonInfo_RefID = Guid.NewGuid();
businesParticipantPatient.Save(Connection, Transaction);
int PatientSex = 0;
switch (Parameter.sex)
{
case "M":
PatientSex = 0;
break;
case "W":
PatientSex = 1;
break;
case "o.A.":
PatientSex = 2;
break;
}
ORM_CMN_PER_PersonInfo personInfo = new ORM_CMN_PER_PersonInfo();
personInfo.CMN_PER_PersonInfoID = businesParticipantPatient.IfNaturalPerson_CMN_PER_PersonInfo_RefID;
personInfo.Tenant_RefID = securityTicket.TenantID;
personInfo.Creation_Timestamp = DateTime.Now;
personInfo.Modification_Timestamp = DateTime.Now;
personInfo.FirstName = Parameter.name;
personInfo.LastName = Parameter.LastName;
personInfo.BirthDate = Parameter.birthday;
personInfo.Gender = PatientSex;
personInfo.Save(Connection, Transaction);
ORM_HEC_Patient_MedicalPractice medical_practice_to_patient = new ORM_HEC_Patient_MedicalPractice();
medical_practice_to_patient.HEC_Patient_MedicalPracticeID = Guid.NewGuid();
medical_practice_to_patient.HEC_Patient_RefID = patients.HEC_PatientID;
medical_practice_to_patient.HEC_MedicalPractices_RefID = Guid.Parse(Parameter.practice_id);//
medical_practice_to_patient.Tenant_RefID = securityTicket.TenantID;
medical_practice_to_patient.Creation_Timestamp = DateTime.Now;
medical_practice_to_patient.Save(Connection, Transaction);
var medicalPracticeQuery = new ORM_HEC_HIS_HealthInsurance_Company.Query();
medicalPracticeQuery.IsDeleted = false;
medicalPracticeQuery.Tenant_RefID = securityTicket.TenantID;
var HIPList = ORM_HEC_HIS_HealthInsurance_Company.Query.Search(Connection, Transaction, medicalPracticeQuery).ToList();
if (Parameter.isPrivatelyInsured)
{
Parameter.health_insurance_providerNumber = "000000000";
}
var GetHip = HIPList.Where(hp => hp.HealthInsurance_IKNumber == Parameter.health_insurance_providerNumber).SingleOrDefault();
if (GetHip == null)
{
var businessParticipantHIP = new ORM_CMN_BPT_BusinessParticipant();
businessParticipantHIP.IsCompany = true;
businessParticipantHIP.Tenant_RefID = securityTicket.TenantID;
businessParticipantHIP.Modification_Timestamp = DateTime.Now;
businessParticipantHIP.DisplayName = Parameter.health_insurance_provider;
businessParticipantHIP.Save(Connection, Transaction);
GetHip = new ORM_HEC_HIS_HealthInsurance_Company();
GetHip.Tenant_RefID = securityTicket.TenantID;
GetHip.CMN_BPT_BusinessParticipant_RefID = businessParticipantHIP.CMN_BPT_BusinessParticipantID;
GetHip.HealthInsurance_IKNumber = String.IsNullOrEmpty(Parameter.health_insurance_provider) ? "privat versichert" : Parameter.health_insurance_provider;
GetHip.Save(Connection, Transaction);
}
ORM_HEC_Patient_HealthInsurance patientHealthInsurance = new ORM_HEC_Patient_HealthInsurance();
patientHealthInsurance.HEC_Patient_HealthInsurancesID = Guid.NewGuid();
patientHealthInsurance.Patient_RefID = patients.HEC_PatientID;
patientHealthInsurance.HealthInsurance_Number = Parameter.insurance_id; //
patientHealthInsurance.Tenant_RefID = securityTicket.TenantID;
patientHealthInsurance.InsuranceStateCode = Parameter.insurance_status; //
patientHealthInsurance.HIS_HealthInsurance_Company_RefID = GetHip.HEC_HealthInsurance_CompanyID;
patientHealthInsurance.Save(Connection, Transaction);
#region import Patient to Elastic
Patient_Model patientModel = new Patient_Model();
patientModel.birthday = Parameter.birthday;
patientModel.birthday_string = Parameter.birthday.ToString("dd.MM.yyyy");
patientModel.name = Parameter.LastName + ", " + Parameter.name;
patientModel.health_insurance_provider = String.IsNullOrEmpty(Parameter.health_insurance_provider) ? "privat versichert" : Parameter.health_insurance_provider;
patientModel.name_with_birthdate = Parameter.name + " " + Parameter.LastName + " (" + Parameter.birthday.ToString("dd.MM.yyyy") + ")";
patientModel.id = patients.HEC_PatientID.ToString();
patientModel.insurance_id = String.IsNullOrEmpty(Parameter.insurance_id) ? "-" : Parameter.insurance_id;
patientModel.insurance_status = String.IsNullOrEmpty(Parameter.insurance_status) ? "-" : Parameter.insurance_status;
patientModel.practice_id = Parameter.practice_id.ToString();
if (PatientSex == 0)
{
patientModel.sex = "M";
}
else if (PatientSex == 1)
{
patientModel.sex = "W";
}
else if (PatientSex == 2)
{
patientModel.sex = "o.A.";
}
Add_New_Patient.Import_Patients_to_ElasticDB(patientModel, securityTicket.TenantID.ToString());
if (create_consents)
{
#region Participation Consent
var contractIvi = ORM_CMN_CTR_Contract.Query.Search(Connection, Transaction, new ORM_CMN_CTR_Contract.Query()
{
IsDeleted = false,
Tenant_RefID = securityTicket.TenantID,
ContractName = "IVI-Vertrag"
}).SingleOrDefault();
if (contractIvi != null)
{
var InsuranceToBrokerContractQuery = new ORM_HEC_CRT_InsuranceToBrokerContract.Query();
InsuranceToBrokerContractQuery.Tenant_RefID = securityTicket.TenantID;
InsuranceToBrokerContractQuery.IsDeleted = false;
InsuranceToBrokerContractQuery.Ext_CMN_CTR_Contract_RefID = contractIvi.CMN_CTR_ContractID;
ORM_HEC_CRT_InsuranceToBrokerContract InsuranceToBrokerContract = ORM_HEC_CRT_InsuranceToBrokerContract.Query.Search(Connection, Transaction, InsuranceToBrokerContractQuery).Single();
List <DateTime> TimeFrom = new List <DateTime>();
DateTime time1 = new DateTime(2013, 6, 15);
DateTime time2 = new DateTime(2014, 6, 15);
DateTime time3 = new DateTime(2015, 6, 15);
TimeFrom.Add(time1);
TimeFrom.Add(time2);
TimeFrom.Add(time3);
foreach (var date in TimeFrom)
{
ORM_HEC_CRT_InsuranceToBrokerContract_ParticipatingPatient InsuranceToBrokerContract_ParticipatingPatient = new ORM_HEC_CRT_InsuranceToBrokerContract_ParticipatingPatient();
InsuranceToBrokerContract_ParticipatingPatient.HEC_CRT_InsuranceToBrokerContract_ParticipatingPatientID = Guid.NewGuid();
InsuranceToBrokerContract_ParticipatingPatient.InsuranceToBrokerContract_RefID = InsuranceToBrokerContract.HEC_CRT_InsuranceToBrokerContractID;
InsuranceToBrokerContract_ParticipatingPatient.Creation_Timestamp = DateTime.Now;
InsuranceToBrokerContract_ParticipatingPatient.Modification_Timestamp = DateTime.Now;
InsuranceToBrokerContract_ParticipatingPatient.Tenant_RefID = securityTicket.TenantID;
InsuranceToBrokerContract_ParticipatingPatient.ValidFrom = date;
InsuranceToBrokerContract_ParticipatingPatient.ValidThrough = DateTime.MinValue;
InsuranceToBrokerContract_ParticipatingPatient.Patient_RefID = patient_id;
InsuranceToBrokerContract_ParticipatingPatient.Save(Connection, Transaction);
Patient_Model patientModel2 = new Patient_Model();
patientModel2 = Retrieve_Patients.Get_Patient_for_PatientID(patient_id.ToString(), securityTicket);
var InsuranceToBrokerContract_ParticipatingPatientQuery = new ORM_HEC_CRT_InsuranceToBrokerContract_ParticipatingPatient.Query();
InsuranceToBrokerContract_ParticipatingPatientQuery.IsDeleted = false;
InsuranceToBrokerContract_ParticipatingPatientQuery.Tenant_RefID = securityTicket.TenantID;
InsuranceToBrokerContract_ParticipatingPatientQuery.Patient_RefID = patient_id;
var allInsuranceToBrokerContract_ParticipatingPatient = ORM_HEC_CRT_InsuranceToBrokerContract_ParticipatingPatient.Query.Search(Connection, Transaction, InsuranceToBrokerContract_ParticipatingPatientQuery).ToList();
var latest_participation_date = allInsuranceToBrokerContract_ParticipatingPatient.OrderByDescending(m => m.ValidFrom).FirstOrDefault();
patientModel2.participation_consent_from = latest_participation_date.ValidFrom;
patientModel2.participation_consent_to = latest_participation_date.ValidThrough;
patientModel2.has_participation_consent = true;
Add_New_Patient.Import_Patients_to_ElasticDB(patientModel2, securityTicket.TenantID.ToString());
}
}
}
#endregion
//Commit the transaction
if (cleanupTransaction == true)
{
Transaction.Commit();
}
//Close the connection
if (cleanupConnection == true)
{
Connection.Close();
}
}
catch (Exception ex)
{
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw ex;
}
#endregion
}
public static void ImportTestPatients(DbConnection Connection, DbTransaction Transaction, SessionSecurityTicket securityTicket, Guid medical_practice_id)
{
List <Patient_Model_xls> patientList = new List <Patient_Model_xls>();
List <Patient_Model> elasticPatientList = new List <Patient_Model>();
IFormatProvider culture = new System.Globalization.CultureInfo("de", true);
string folder = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
string filePath = Path.Combine(folder, "Excel\\german_names_sheet.xlsx");
bool hasHeader = true;
System.Data.DataTable excelData = ExcelUtils.getDataFromExcelFile(filePath, hasHeader);
foreach (System.Data.DataRow item in excelData.Rows)
{
if (item.ItemArray[0].ToString() == "")
{
break;
}
Patient_Model_xls patientModel = new Patient_Model_xls();
patientModel.name = item.ItemArray[0].ToString();
patientModel.LastName = item.ItemArray[1].ToString();
patientModel.sex = item.ItemArray[2].ToString();
patientModel.birthday = DateTime.Parse(item.ItemArray[3].ToString(), culture, System.Globalization.DateTimeStyles.AssumeLocal);
patientList.Add(patientModel);
}
List <string> StateCode_first_Caracter = new List <string>();
StateCode_first_Caracter.Add("1");
StateCode_first_Caracter.Add("3");
StateCode_first_Caracter.Add("5");
List <string> StateCode_fift_Caracter = new List <string>();
StateCode_fift_Caracter.Add("1");
StateCode_fift_Caracter.Add("4");
StateCode_fift_Caracter.Add("6");
StateCode_fift_Caracter.Add("7");
StateCode_fift_Caracter.Add("8");
StateCode_fift_Caracter.Add("9");
StateCode_fift_Caracter.Add("D");
StateCode_fift_Caracter.Add("F");
StateCode_fift_Caracter.Add("A");
StateCode_fift_Caracter.Add("C");
StateCode_fift_Caracter.Add("S");
StateCode_fift_Caracter.Add("P");
StateCode_fift_Caracter.Add("E");
StateCode_fift_Caracter.Add("N");
StateCode_fift_Caracter.Add("M");
StateCode_fift_Caracter.Add("X");
StateCode_fift_Caracter.Add("L");
StateCode_fift_Caracter.Add("K");
int status_code_counter = 0;
int status_fift_counter = 0;
int counter = 0;
var medicalPracticeQuery = new ORM_HEC_HIS_HealthInsurance_Company.Query();
medicalPracticeQuery.IsDeleted = false;
medicalPracticeQuery.Tenant_RefID = securityTicket.TenantID;
var HIPList = ORM_HEC_HIS_HealthInsurance_Company.Query.Search(Connection, Transaction, medicalPracticeQuery).ToList();
int i = 0;
foreach (var item in patientList)
{
ORM_HEC_Patient patients = new ORM_HEC_Patient();
patients.HEC_PatientID = Guid.NewGuid();
patients.Tenant_RefID = securityTicket.TenantID;
patients.Creation_Timestamp = DateTime.Now;
patients.Modification_Timestamp = DateTime.Now;
patients.CMN_BPT_BusinessParticipant_RefID = Guid.NewGuid();
patients.Save(Connection, Transaction);
ORM_CMN_BPT_BusinessParticipant businesParticipantPatient = new ORM_CMN_BPT_BusinessParticipant();
businesParticipantPatient.CMN_BPT_BusinessParticipantID = patients.CMN_BPT_BusinessParticipant_RefID;
businesParticipantPatient.Tenant_RefID = securityTicket.TenantID;
businesParticipantPatient.Creation_Timestamp = DateTime.Now;
businesParticipantPatient.Modification_Timestamp = DateTime.Now;
businesParticipantPatient.IsNaturalPerson = true;
businesParticipantPatient.IfNaturalPerson_CMN_PER_PersonInfo_RefID = Guid.NewGuid();
businesParticipantPatient.Save(Connection, Transaction);
ORM_CMN_PER_PersonInfo personInfo = new ORM_CMN_PER_PersonInfo();
personInfo.CMN_PER_PersonInfoID = businesParticipantPatient.IfNaturalPerson_CMN_PER_PersonInfo_RefID;
personInfo.Tenant_RefID = securityTicket.TenantID;
personInfo.Creation_Timestamp = DateTime.Now;
personInfo.Modification_Timestamp = DateTime.Now;
personInfo.FirstName = item.name; //
personInfo.LastName = item.LastName; //
personInfo.BirthDate = item.birthday; //
personInfo.Gender = int.Parse(item.sex); //
personInfo.Save(Connection, Transaction);
ORM_HEC_Patient_MedicalPractice medical_practice_to_patient = new ORM_HEC_Patient_MedicalPractice();
medical_practice_to_patient.HEC_Patient_MedicalPracticeID = Guid.NewGuid();
medical_practice_to_patient.HEC_Patient_RefID = patients.HEC_PatientID;
medical_practice_to_patient.HEC_MedicalPractices_RefID = medical_practice_id;//
medical_practice_to_patient.Tenant_RefID = securityTicket.TenantID;
medical_practice_to_patient.Creation_Timestamp = DateTime.Now;
medical_practice_to_patient.Save(Connection, Transaction);
ORM_HEC_Patient_HealthInsurance patientHealthInsurance = new ORM_HEC_Patient_HealthInsurance();
patientHealthInsurance.HEC_Patient_HealthInsurancesID = Guid.NewGuid();
patientHealthInsurance.Patient_RefID = patients.HEC_PatientID;
Random rnd = new Random();
int random_insurance_number = rnd.Next(10000000, 99999999);
int genre = 0;
switch (int.Parse(item.sex))
{
case 0:
genre = 2;
break;
case 1:
genre = 1;
break;
case 2:
genre = 0;
break;
}
var birth = item.birthday.Year.ToString().Substring(2);
patientHealthInsurance.HealthInsurance_Number = random_insurance_number.ToString(); //
patientHealthInsurance.Tenant_RefID = securityTicket.TenantID;
patientHealthInsurance.InsuranceStateCode = StateCode_first_Caracter[status_code_counter] + genre.ToString() + birth + StateCode_fift_Caracter[status_fift_counter]; //
patientHealthInsurance.HIS_HealthInsurance_Company_RefID = HIPList[i].HEC_HealthInsurance_CompanyID;
patientHealthInsurance.Save(Connection, Transaction);
if (status_code_counter == 2)
{
status_code_counter = 0;
}
else
{
status_code_counter++;
}
if (status_fift_counter == 17)
{
status_fift_counter = 0;
}
else
{
status_fift_counter++;
}
if (i == HIPList.Count - 1)
{
i = 0;
}
else
{
i++;
}
var businesParticipantHIPQuery = new ORM_CMN_BPT_BusinessParticipant.Query();
businesParticipantHIPQuery.IsDeleted = false;
businesParticipantHIPQuery.Tenant_RefID = securityTicket.TenantID;
businesParticipantHIPQuery.CMN_BPT_BusinessParticipantID = HIPList[i].CMN_BPT_BusinessParticipant_RefID;
var businesParticipantHIP = ORM_CMN_BPT_BusinessParticipant.Query.Search(Connection, Transaction, businesParticipantHIPQuery).Single();;
Patient_Model patientModel = new Patient_Model();
patientModel.birthday = DateTime.Parse(item.birthday.ToString("dd.MM.yyyy"), culture, System.Globalization.DateTimeStyles.AssumeLocal);
patientModel.birthday_string = item.birthday.ToString("dd.MM.yyyy");
patientModel.name = item.LastName + ", " + item.name;
patientModel.health_insurance_provider = businesParticipantHIP.DisplayName;
patientModel.name_with_birthdate = item.name + " " + item.LastName + " (" + item.birthday.ToString("dd.MM.yyyy") + ")";
patientModel.id = patients.HEC_PatientID.ToString();
patientModel.insurance_id = patientHealthInsurance.HealthInsurance_Number;
patientModel.insurance_status = patientHealthInsurance.InsuranceStateCode;
patientModel.practice_id = medical_practice_id.ToString();
if (int.Parse(item.sex) == 0)
{
patientModel.sex = "M";
}
else if (int.Parse(item.sex) == 1)
{
patientModel.sex = "W";
}
else if (int.Parse(item.sex) == 2)
{
patientModel.sex = "o.A.";
}
elasticPatientList.Add(patientModel);
counter++;
Console.WriteLine(counter + "________________________" + patientList.Count);
}
string indexElastic = securityTicket.TenantID.ToString();
var serializer = new JsonNetSerializer();
var connection = Elastic_Utils.ElsaticConnection();
bool checkIndex = Elastic_Utils.IfIndexOrTypeExists(indexElastic, connection);
if (!checkIndex)
{
string settings = Elastic_Utils.BuildIndexSettings();
connection.Put(indexElastic, settings);
string jsonProductMapping = Add_New_Patient.BuildPatientMapping();
string resultProductMapping = connection.Put(new PutMappingCommand(indexElastic, "patient"), jsonProductMapping);
}
bool checkTupe = Elastic_Utils.IfIndexOrTypeExists(indexElastic + "/patient", connection);
if (!checkTupe)
{
string jsonProductMapping = Add_New_Patient.BuildPatientMapping();
string resultProductMapping = connection.Put(new PutMappingCommand(indexElastic, "patient"), jsonProductMapping);
}
Elastic_Utils.BulkType_Generic <Patient_Model>(elasticPatientList, connection, serializer, indexElastic, "patient");
}
