public static void Import_Patients_to_ElasticDB(Patient_Model_xls patient, string TenantID)
{
string indexElastic = TenantID;
var serializer = new JsonNetSerializer();
var connection = Elastic_Utils.ElsaticConnection();
connection.Delete(indexElastic);
//bool checkIndex = Elastic_Utils.IfIndexOrTypeExists(indexElastic, connection);
//if (!checkIndex)
//{
// Console.WriteLine("Index ne postoji");
// string settings = Elastic_Utils.BuildIndexSettings();
// connection.Put(indexElastic, settings);
// string jsonProductMapping = BuildPatientMapping();
// string resultProductMapping = connection.Put(new PutMappingCommand(indexElastic, "patient"), jsonProductMapping);
//}
//Console.WriteLine("Index exists: " + checkIndex.ToString());
//bool checkTupe = Elastic_Utils.IfIndexOrTypeExists(indexElastic + "/patient", connection);
//if (!checkTupe)
//{
// Console.WriteLine("Type ne postoji. ");
// string jsonProductMapping = BuildPatientMapping();
// string resultProductMapping = connection.Put(new PutMappingCommand(indexElastic, "patient"), jsonProductMapping);
// Console.WriteLine("Type Kreiran. ");
//}
//Console.WriteLine("Type exists: " + checkTupe.ToString());
//List<Patient_Model> modelList = new List<Patient_Model>();
//modelList.Add(patient);
//Elastic_Utils.BulkType_Generic<Patient_Model>(modelList, connection, serializer, indexElastic, "patient");
}
public static void ExecudeAddingToLynx(DbConnection Connection, DbTransaction Transaction, SessionSecurityTicket securityTicket)
{
Patient_Model_xls patientModel = new Patient_Model_xls();
IFormatProvider culture = new System.Globalization.CultureInfo("de", true);
patientModel.birthday = DateTime.Parse(DateTime.Now.ToString("dd.MM.yyyy"), culture, System.Globalization.DateTimeStyles.AssumeLocal);
patientModel.birthday_string = DateTime.Now.ToString("dd.MM.yyyy");
patientModel.name = "pera" + ", " + "peric";
patientModel.health_insurance_provider = "AOK Berlin";
patientModel.name_with_birthdate = "pera" + " " + "peric" + " (" + patientModel.birthday_string + ")";
patientModel.id = Guid.NewGuid().ToString();
patientModel.insurance_id = "12341";
patientModel.insurance_status = "123456742";
patientModel.group_name = "";
patientModel.participation_consent = "";
patientModel.practice_id = "7547b215-cb90-4fc0-a546-2041471ec71d";
patientModel.participation_consent = "none";
Console.WriteLine("TenantID je " + securityTicket.TenantID.ToString());
Import_Patients_to_ElasticDB(patientModel, securityTicket.TenantID.ToString());
}
public static void Get_PatientsList(ElasticParameterObject Parameter, string practice_id, SessionSecurityTicket userSecurityTicket)
{
List <Patient_Model_xls> patientList = new List <Patient_Model_xls>();
var TenantID = userSecurityTicket.TenantID.ToString();
var serializer = new JsonNetSerializer();
var connection = Elastic_Utils.ElsaticConnection();
string elasticType = "patient";
if (Elastic_Utils.IfIndexOrTypeExists(TenantID, connection) && Elastic_Utils.IfIndexOrTypeExists(TenantID + "/" + elasticType, connection))
{
string sort_by_second_key = "name";
string sort_by_third_key = "name";
switch (Parameter.sort_by)
{
case "name":
sort_by_second_key = "name";
sort_by_third_key = "birthday";
break;
case "participation_consent":
sort_by_second_key = "birthday";
sort_by_third_key = "birthday";
break;
}
Console.WriteLine(sort_by_second_key + " " + sort_by_third_key);
string query = String.Empty;
if (string.IsNullOrEmpty(Parameter.search_params))
{
query = QueryBuilderPatients.BuildGetPatientsQuery(Parameter.start_row_index, 100, Parameter.sort_by, Parameter.isAsc, practice_id, sort_by_second_key, sort_by_third_key);
}
else
{
query = QueryBuilderPatients.BuildGetPatientsSearchAsYouTypeQuery(Parameter.start_row_index, 100, Parameter.sort_by, Parameter.isAsc, Parameter.search_params, practice_id, sort_by_second_key, sort_by_third_key);
}
string searchCommand = Commands.Search(TenantID, elasticType).Pretty();
string result = connection.Post(searchCommand, query);
Console.WriteLine(searchCommand + query);
var foundResults = serializer.ToSearchResult <Patient_Model_xls>(result);
Console.WriteLine(foundResults.hits.total);
foreach (var item in foundResults.Documents)
{
Console.WriteLine(item.name);
IFormatProvider culture = new System.Globalization.CultureInfo("de", true);
Patient_Model_xls patient = new Patient_Model_xls();
patient.birthday_string = item.birthday_string;
patient.birthday = DateTime.Parse(item.birthday_string, culture, System.Globalization.DateTimeStyles.AssumeLocal);
patient.health_insurance_provider = item.health_insurance_provider;
patient.id = item.id;
patient.insurance_id = item.insurance_id;
patient.insurance_status = item.insurance_status;
patient.name = item.name;
patient.participation_consent = item.participation_consent;
patient.sex = item.sex;
switch (Parameter.sort_by)
{
case "name":
patient.group_name = string.IsNullOrEmpty(item.name) ? "-" : item.name.Substring(0, 1).ToUpper();
break;
case "sex":
patient.group_name = string.IsNullOrEmpty(item.sex) ? "-" : item.sex;
break;
case "health_insurance_provider":
patient.group_name = string.IsNullOrEmpty(item.health_insurance_provider) ? "-" : item.health_insurance_provider;
break;
case "participation_consent":
patient.group_name = string.IsNullOrEmpty(item.participation_consent) ? "-" : item.participation_consent;
break;
}
patientList.Add(patient);
Console.WriteLine("Zavrsio");
}
}
}
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");
}
public static void Import_Patients_from_xls(bool create_consents, string connectionString, SessionSecurityTicket securityTicket)
{
DbConnection Connection = null;
DbTransaction Transaction = null;
bool cleanupConnection = Connection == null;
bool cleanupTransaction = Transaction == null;
if (cleanupConnection == true)
{
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(connectionString);
Connection.Open();
}
if (cleanupTransaction == true)
{
Transaction = Connection.BeginTransaction();
}
try
{
string folder = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
string filePath = Path.Combine(folder, "Excel\\patients.xlsx");
bool hasHeader = true;
DataTable excelData = null;
try
{
excelData = ExcelUtils.getDataFromExcelFileMM(filePath, hasHeader);
}
catch (Exception ex)
{
filePath = Path.Combine(folder, "Excel\\patients.xls");
}
excelData = ExcelUtils.getDataFromExcelFileMM(filePath, hasHeader);
CultureInfo culture = CultureInfo.InvariantCulture;
List <Patient_Model_xls> patientL = new List <Patient_Model_xls>();
DO_GAPR_1112[] PracticeData = new DO_GAPR_1112[] { };
PracticeData = cls_Get_all_Practices.Invoke(Connection, Transaction, securityTicket).Result;
var HiPs = Get_HIPs.Get_HIPs_for_Search_Criteria("");
List <string> FirstLastNameUnique = new List <string>();
List <string> HipNumUnique = new List <string>();
bool flagValid = true;
foreach (System.Data.DataRow item in excelData.Rows)
{
flagValid = true;
Patient_Model_xls patient = new Patient_Model_xls();
patient.ValidationErrors = "";
patient.isPrivatelyInsured = item.ItemArray[6].ToString() == "private";
if (!String.IsNullOrEmpty(item.ItemArray[0].ToString()))
{
patient.name = item.ItemArray[0].ToString();
}
else
{
patient.name = " ";
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Patient First Name empty";
}
if (!String.IsNullOrEmpty(item.ItemArray[1].ToString()))
{
patient.LastName = item.ItemArray[1].ToString();
}
else
{
patient.LastName = " ";
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Patient Last Name empty";
}
if (!String.IsNullOrEmpty(item.ItemArray[2].ToString()))
{
try
{
patient.birthday_string = item.ItemArray[2].ToString();
patient.birthday = DateTime.ParseExact(patient.birthday_string, "M/d/yyyy", culture);
if (FirstLastNameUnique.Contains(patient.name + " " + patient.LastName + " " + patient.birthday_string))
{
// patient.ValidationErrors = patient.ValidationErrors + ", " + "Patient with same first, last name and birthday already exists";
}
FirstLastNameUnique.Add(patient.name + " " + patient.LastName + " " + patient.birthday_string);
}
catch
{
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Patient birthday not valid";
}
}
else
{
patient.birthday_string = " ";
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Birthday empty ";
}
if (!String.IsNullOrEmpty(item.ItemArray[3].ToString()))
{
patient.sex = item.ItemArray[3].ToString();
}
else
{
patient.sex = " ";
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Gender empty ";
}
if (!patient.isPrivatelyInsured)
{
if (!String.IsNullOrEmpty(item.ItemArray[4].ToString()))
{
patient.health_insurance_providerNumber = item.ItemArray[4].ToString();
if (!HiPs.Any(hp => hp.ik_number == patient.health_insurance_providerNumber))
{
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Health insurance IK do not exists ";
}
}
else
{
patient.health_insurance_providerNumber = " ";
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Health insurance IK empty ";
}
if (!String.IsNullOrEmpty(item.ItemArray[5].ToString()))
{
if (!patient.isPrivatelyInsured)
{
patient.health_insurance_provider = item.ItemArray[5].ToString();
var hip = HiPs.Where(hp => hp.ik_number == patient.health_insurance_providerNumber).SingleOrDefault();
if (hip == null || hip.name != patient.health_insurance_provider)
{
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Health insurance name do not match given Health insurance IK ";
}
}
}
else
{
patient.health_insurance_provider = " ";
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + " Health insurance name empty ";
}
if (!String.IsNullOrEmpty(item.ItemArray[6].ToString()))
{
patient.insurance_id = item.ItemArray[6].ToString();
if (HipNumUnique.Contains(patient.insurance_id))
{
// patient.ValidationErrors = patient.ValidationErrors + ", " + " Insurance number already exists ";
}
HipNumUnique.Add(patient.insurance_id);
}
else
{
patient.insurance_id = " ";
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + " Insurance number empty ";
}
if (!String.IsNullOrEmpty(item.ItemArray[7].ToString()))
{
patient.insurance_status = item.ItemArray[7].ToString();
}
else
{
patient.insurance_status = " ";
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + " Insurance state empty ";
}
var insurancestatusValid = ValidationMethods.ValidateInsuranceStatusCode(patient.insurance_status);
bool invalidStatus = insurancestatusValid.Item1;
patient.insurance_status = insurancestatusValid.Item2;
if (!invalidStatus)
{
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + " Insurance status not valid ";
}
}
if (!String.IsNullOrEmpty(item.ItemArray[8].ToString()))
{
patient.practice_bsnr = item.ItemArray[8].ToString();
if (!ValidationMethods.IsDigitsOnly(patient.practice_bsnr))
{
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "BSNR doesn't have only digits";
}
if (patient.practice_bsnr.Length != 9)
{
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "BSNR doesn't have 9 digits";
}
if (PracticeData.Where(pp => pp.CompanyInfo_EstablishmentNumber == patient.practice_bsnr).SingleOrDefault() == null)
{
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Practice with given BSNR do not exists";
}
}
else
{
patient.practice_bsnr = " ";
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "BSNR empty ";
}
if (!String.IsNullOrEmpty(item.ItemArray[9].ToString()))
{
patient.practice_name = item.ItemArray[9].ToString();
if (!String.IsNullOrWhiteSpace(patient.practice_bsnr))
{
if (!String.IsNullOrEmpty(patient.practice_name))
{
var practice = PracticeData.Where(bs => bs.CompanyInfo_EstablishmentNumber == patient.practice_bsnr).SingleOrDefault();
if (practice == null)
{
flagValid = false;
patient.ValidationErrors = patient.ValidationErrors + ", " + "Practice with given BSNR do not exists";
}
else if (practice.DisplayName != patient.practice_name)
{
flagValid = false;
patient.practice_name = " ";
patient.ValidationErrors = patient.ValidationErrors + ", " + "Practice with given Name do not match given BSNR";
}
else
{
patient.practice_id = practice.HEC_MedicalPractiseID.ToString();
}
}
}
}
else
{
patient.practice_name = " ";
patient.ValidationErrors = patient.ValidationErrors + ", " + "Practice Name empty ";
flagValid = false;
}
if (patient.ValidationErrors != "")
{
patient.ValidationErrors = patient.ValidationErrors.Substring(1);
}
patient.isValid = flagValid;
patientL.Add(patient);
}
string file = ExportPatientsBeforeUpload.ExportPatientsBeforeUploadToDB(patientL);
MemoryStream ms = new MemoryStream(File.ReadAllBytes(file));
Console.WriteLine("----- XLS created.");
if (!patientL.Any(vl => vl.isValid == false))
{
//import patients to DB
foreach (var patientForSave in patientL)
{
Save_Patients.Save_Patients_to_DB(patientForSave, create_consents, connectionString, securityTicket);
}
Console.WriteLine("Patients imported");
}
else
{
Console.WriteLine("Data not valid,won't be saved ");
}
//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;
}
}