public static void import_Practice_from_xls(string connectionString, SessionSecurityTicket securityTicket)
        {
            string folder    = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
            string filePath  = Path.Combine(folder, "Excel\\practices.xlsx");
            bool   hasHeader = true;
            bool   flagValid = true;

            System.Data.DataTable excelData = ExcelUtils.getDataFromExcelFileMM(filePath, hasHeader);


            try
            {
                excelData = ExcelUtils.getDataFromExcelFileMM(filePath, hasHeader);
            }
            catch (Exception ex)
            {
                filePath = Path.Combine(folder, "Excel\\practices.xls");
            }

            excelData = ExcelUtils.getDataFromExcelFileMM(filePath, hasHeader);


            List <Practice_Model_from_xlsx> PracticeL = new List <Practice_Model_from_xlsx>();
            List <string> BSNRUnique      = new List <string>();
            List <string> LoginMailUnique = new List <string>();


            foreach (System.Data.DataRow item in excelData.Rows)
            {
                Practice_Model_from_xlsx practice = new Practice_Model_from_xlsx();
                practice.PracticeName     = item.ItemArray[0].ToString();
                practice.ValidationErrors = "";
                if (practice.PracticeName == "")
                {
                    flagValid = false;
                    practice.ValidationErrors = "Practice name empty";
                }

                practice.BSNR = item.ItemArray[1].ToString();
                foreach (var bs in BSNRUnique)
                {
                    if (bs == practice.BSNR)
                    {
                        flagValid = false;
                        practice.ValidationErrors = practice.ValidationErrors + ", " + "BSNR already exists";
                    }
                }
                BSNRUnique.Add(practice.BSNR);
                if (practice.BSNR == "")
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "BSNR name empty";
                }

                if (!ValidationMethods.IsDigitsOnly(practice.BSNR))
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "BSNR doesn't have only digits";
                }
                if (practice.BSNR.Length != 9)
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "BSNR doesn't have 9 digits";
                }

                practice.Street = item.ItemArray[2].ToString();
                if (practice.Street == "")
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "Street name empty";
                }
                practice.No = item.ItemArray[3].ToString();
                if (practice.No == "")
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "Street number empty";
                }
                practice.Zip = item.ItemArray[4].ToString();
                if (practice.Zip == "")
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "ZIP code empty";
                }

                practice.City = item.ItemArray[5].ToString();
                if (practice.City == "")
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "City name empty";
                }
                practice.MainEmail = item.ItemArray[6].ToString();
                if (practice.MainEmail != "")
                {
                    if (!ValidationMethods.IsMailValid(practice.MainEmail))
                    {
                        flagValid = false;
                        practice.ValidationErrors = practice.ValidationErrors + ", " + "Main email not valid";
                    }
                }
                practice.MainPhone = item.ItemArray[7].ToString();

                //if (practice.MainPhone == "")
                //{
                //    flagValid = false;
                //    practice.ValidationErrors = practice.ValidationErrors + ", " + "Main phone empty";
                //}
                practice.Fax           = item.ItemArray[8].ToString();
                practice.ContactPerson = item.ItemArray[9].ToString();
                practice.Email         = item.ItemArray[10].ToString();
                if (practice.Email != "")
                {
                    if (!ValidationMethods.IsMailValid(practice.Email))
                    {
                        flagValid = false;
                        practice.ValidationErrors = practice.ValidationErrors + ", " + "Email not valid";
                    }
                }

                practice.Phone         = item.ItemArray[11].ToString();
                practice.AccountHolder = item.ItemArray[12].ToString();
                practice.Bic           = item.ItemArray[13].ToString();
                if (!String.IsNullOrEmpty(practice.Bic))
                {
                    if (!ValidationMethods.isValidBic(practice.Bic.ToString()))
                    {
                        flagValid = false;
                        practice.ValidationErrors = practice.ValidationErrors + ", " + "BIC not valid";
                    }
                }

                practice.IBAN = item.ItemArray[14].ToString();
                practice.Bank = item.ItemArray[15].ToString();
                if (practice.Bic != "" && practice.Bank == "")
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "Bic not empty but bank is empty";
                }

                practice.LoginEmail = item.ItemArray[16].ToString();
                if (practice.LoginEmail == "")
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "Login email empty";
                }

                if (!ValidationMethods.IsMailValid(practice.LoginEmail))
                {
                    flagValid = false;
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "Login email not valid";
                }

                foreach (var lg in LoginMailUnique)
                {
                    if (lg == practice.LoginEmail)
                    {
                        flagValid = false;
                        practice.ValidationErrors = practice.ValidationErrors + ", " + "Login email already exists";
                    }
                }
                LoginMailUnique.Add(practice.LoginEmail);


                practice.inPassword = item.ItemArray[17].ToString();
                if (practice.inPassword != "")
                {
                    if (!ValidationMethods.isValidPass(practice.inPassword))
                    {
                        flagValid = false;
                        practice.ValidationErrors = practice.ValidationErrors + ", " + "Password not valid";
                    }
                }
                else
                {
                    practice.inPassword = ValidationMethods.CreatePassword(8);
                }
                practice.IsSurgeryPractice         = int.Parse(item.ItemArray[18].ToString()) == 1 ? true : false;
                practice.IsOrderDrugs              = int.Parse(item.ItemArray[19].ToString()) == 1 ? true : false;
                practice.DefaultShippingDateOffset = item.ItemArray[20].ToString();
                if (practice.DefaultShippingDateOffset == "")
                {
                    practice.ValidationErrors = practice.ValidationErrors + ", " + "Default shipping date offset empty";
                    flagValid = false;
                }
                practice.IsOnlyLabelRequired = int.Parse(item.ItemArray[21].ToString()) == 1 ? true : false;
                practice.isWaiveServiceFee   = int.Parse(item.ItemArray[22].ToString()) == 1 ? true : false;
                practice.isValid             = flagValid;

                if (practice.ValidationErrors != "")
                {
                    practice.ValidationErrors = practice.ValidationErrors.Substring(1);
                }


                PracticeL.Add(practice);
            }

            string       file = ExportPracticesBeforeUpload.ExportPracticesBeforeUploadToDB(PracticeL);
            MemoryStream ms   = new MemoryStream(File.ReadAllBytes(file));

            Console.WriteLine("----- XLS created.");

            if (!PracticeL.Any(vl => vl.isValid == false))
            {
                foreach (var practice in PracticeL)
                {
                    if (practice.LoginEmail != "")
                    {
                        Create_Accounts.Save_accounts_to_DBCompany(practice, connectionString, securityTicket);
                    }
                }
                foreach (var practice in PracticeL)
                {
                    if (practice.LoginEmail != "")
                    {
                        Save_practices.Save_practices_to_DB(practice, connectionString, securityTicket);
                    }
                }
                Console.WriteLine("Practices imported");
            }
            else
            {
                Console.WriteLine("Data invalid, won't be saved!");
            }
        }
Пример #2
0
        public static void Import_Doctors_from_xls(string connectionString, SessionSecurityTicket securityTicket)
        {
            string folder    = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
            string filePath  = Path.Combine(folder, "Excel\\doctors.xlsx");
            bool   hasHeader = true;
            bool   flagValid = true;

            System.Data.DataTable excelData = ExcelUtils.getDataFromExcelFileMM(filePath, hasHeader);
            try
            {
                excelData = ExcelUtils.getDataFromExcelFileMM(filePath, hasHeader);
                filePath  = Path.Combine(folder, "Excel\\doctors.xls");
            }
            catch (Exception ex)
            {
            }


            List <Doctor_model_from_xlsx> DoctorL = new List <Doctor_model_from_xlsx>();

            DO_GAPR_1112[] PracticeData       = new DO_GAPR_1112[] { };
            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
            {
                PracticeData = cls_Get_all_Practices.Invoke(Connection, Transaction, securityTicket).Result;


                //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;
            }
            List <string> LoginMailUnique          = new List <string>();
            Dictionary <string, string> LANRUnique = new Dictionary <string, string>();
            IEnumerable <DataRow>       doctors    = excelData.Rows.Cast <DataRow>();

            foreach (System.Data.DataRow item in excelData.Rows)
            {
                Doctor_model_from_xlsx doctor = new Doctor_model_from_xlsx();
                doctor.ValidationErrors = "";
                // doctor.Salutation = item.ItemArray[0].ToString();
                doctor.Salutation = "";
                doctor.Title      = item.ItemArray[1].ToString();
                doctor.FirstName  = item.ItemArray[2].ToString();
                if (doctor.FirstName == "")
                {
                    flagValid = false;
                    doctor.ValidationErrors = "First name empty";
                }
                doctor.LastNAme = item.ItemArray[3].ToString();
                if (doctor.LastNAme == "")
                {
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Last name empty";
                }
                doctor.Email = item.ItemArray[4].ToString();
                if (doctor.Email != "")
                {
                    if (!ValidationMethods.IsMailValid(doctor.Email))
                    {
                        flagValid = false;
                        doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Email not valid";
                    }
                }

                doctor.Phone = item.ItemArray[5].ToString();
                if (doctor.Phone == "")
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Phone empty";
                }
                doctor.LANR = item.ItemArray[6].ToString();

                if (doctor.LANR == "")
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "LANR empty";
                }
                else if (doctor.LANR.Length != 9)
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "LANR length not valid";
                }
                else if (!ValidationMethods.IsDigitsOnly(doctor.LANR))
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "LANR not number";
                }

                else if (!ValidationMethods.LANRValidation(doctor.LANR))
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "LANR not valid";
                }

                doctor.BSNR = item.ItemArray[7].ToString();


                if (doctors.Count(doc => doc.ItemArray[6] == doctor.LANR && doc.ItemArray[7] == doctor.BSNR) > 1)
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "LANR already exists in practice: " + item.ItemArray[8].ToString() + ", (BSNR: " + doctor.BSNR + ")";
                }


                if (doctor.BSNR == "")
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "BSNR empty";
                }
                doctor.PracticeName = item.ItemArray[8].ToString();
                if (doctor.PracticeName == "")
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Practice name empty";
                }
                var PracticeForDoctor = PracticeData.Where(pr => pr.CompanyInfo_EstablishmentNumber == doctor.BSNR && pr.DisplayName == doctor.PracticeName).SingleOrDefault();
                if (PracticeForDoctor == null)
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Practice with given name and BSNR do not exists";
                }
                else
                {
                    doctor.PracticeID = PracticeForDoctor.HEC_MedicalPractiseID;
                }
                try
                {
                    doctor.IsUsePracticeBank = int.Parse(item.ItemArray[9].ToString()) == 1 ? true : false;
                }
                catch (Exception ex)
                {
                    doctor.IsUsePracticeBank = bool.Parse(item.ItemArray[9].ToString());
                }

                doctor.AccountHolder = item.ItemArray[10].ToString();

                doctor.Bic = item.ItemArray[11].ToString();
                if (doctor.Bic != "")
                {
                    if (!doctor.IsUsePracticeBank && !ValidationMethods.isValidBic(doctor.Bic))
                    {
                        flagValid = false;
                        doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Bic invalid";
                    }
                }
                doctor.IBAN = item.ItemArray[12].ToString();
                if (doctor.IBAN != "")
                {
                    IBAN.IbanValidator validateIban = new IbanValidator();
                    if (doctor.IBAN != "")
                    {
                        try
                        {
                            bool isValidIban = validateIban.ValidateIban(doctor.IBAN);
                        }
                        catch
                        {
                            flagValid = false;
                            doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Iban not valid";
                        }
                    }
                }
                doctor.Bank = item.ItemArray[13].ToString();
                if (!doctor.IsUsePracticeBank && doctor.Bic != "" && doctor.Bank == "")
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Bic entered, bank empty";
                }
                doctor.LoginEmail = item.ItemArray[14].ToString();
                if (doctor.LoginEmail == "")
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Login mail empty";
                }
                else if (!ValidationMethods.IsMailValid(doctor.LoginEmail))
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Login mail not valid";
                }

                foreach (var lg in LoginMailUnique)
                {
                    if (lg == doctor.LoginEmail)
                    {
                        flagValid = false;
                        doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Login email already exists";
                    }
                }
                LoginMailUnique.Add(doctor.LoginEmail);


                doctor.inPassword = item.ItemArray[15].ToString();
                if (doctor.inPassword != "" && !ValidationMethods.isValidPass(doctor.inPassword))
                {
                    flagValid = false;
                    doctor.ValidationErrors = doctor.ValidationErrors + ", " + "Password not valid";
                }
                else if (doctor.inPassword == "")
                {
                    doctor.inPassword = ValidationMethods.CreatePassword(8);
                }
                if (doctor.ValidationErrors != "")
                {
                    doctor.ValidationErrors = doctor.ValidationErrors.Substring(1);
                }
                doctor.isValid = flagValid;
                DoctorL.Add(doctor);
            }


            string       file = ExportDoctorsBeforeUpload.ExportDoctorBeforeUploadToDB(DoctorL);
            MemoryStream ms   = new MemoryStream(File.ReadAllBytes(file));

            Console.WriteLine("----- XLS created.");

            if (!DoctorL.Any(vl => vl.isValid == false))
            {
                foreach (var doctor in DoctorL)
                {
                    if (doctor.LoginEmail != "")
                    {
                        doctor.account_id = Create_Accounts.Save_accounts_to_DBPerson(doctor, connectionString, securityTicket);
                    }
                }

                foreach (var doctor in DoctorL)
                {
                    if (doctor.LoginEmail != "")
                    {
                        Save_Doctors.Save_Doctors_to_DB(doctor, connectionString, securityTicket);
                    }
                }

                Console.WriteLine("Doctors imported");
            }
            else
            {
                Console.WriteLine("Data not valid,won't be saved ");
            }
        }
Пример #3
0
        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;
            }
        }