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!");
}
}
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 ");
}
}
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;
}
}