Exemplo n.º 1
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 ");
            }
        }
Exemplo n.º 2
0
        public static void Save_Doctors_to_DB(Doctor_model_from_xlsx Parameter, 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
            {
                Thread.CurrentThread.CurrentUICulture = CultureInfo.GetCultureInfo("de-DE");
                Guid   BusinessParticipantID         = Guid.NewGuid();
                Guid   personInfoID                  = Guid.NewGuid();
                Guid   PracticeCustomerID            = Guid.NewGuid();
                Guid   PracticeBusinessParticipantID = Guid.NewGuid();
                Guid   PracticeCompanyInfoID         = Guid.NewGuid();
                String PracticeName                  = "";
                Guid   BankAccountID                 = Guid.Empty;
                Guid   doctor_id    = Parameter.DoctorID;
                var    isOpPractice = false;

                if (!String.IsNullOrEmpty(Parameter.LoginEmail))
                //uncomment this if you've created account
                //  if (false)
                {
                    string[] stringUser  = Parameter.LoginEmail.Split('@');
                    string   usernameStr = stringUser[0];

                    try
                    {
                        var doctorAccountInfo = ORM_USR_Account.Query.Search(Connection, Transaction, new ORM_USR_Account.Query()
                        {
                            IsDeleted    = false,
                            Tenant_RefID = securityTicket.TenantID,
                            AccountSignInEmailAddress = Parameter.LoginEmail,
                        }).Single();

                        var accountGroupQuery = new ORM_USR_Account_FunctionLevelRights_Group.Query();
                        accountGroupQuery.Tenant_RefID             = securityTicket.TenantID;
                        accountGroupQuery.IsDeleted                = false;
                        accountGroupQuery.GlobalPropertyMatchingID = "mm.docconect.doc.app.group";

                        var accountGroup = ORM_USR_Account_FunctionLevelRights_Group.Query.Search(Connection, Transaction, accountGroupQuery).SingleOrDefault();

                        if (accountGroup == null)
                        {
                            accountGroup = new ORM_USR_Account_FunctionLevelRights_Group();
                            accountGroup.Tenant_RefID             = securityTicket.TenantID;
                            accountGroup.Label                    = "mm.docconect.doc.app.group";
                            accountGroup.GlobalPropertyMatchingID = "mm.docconect.doc.app.group";
                            accountGroup.Creation_Timestamp       = DateTime.Now;
                            accountGroup.USR_Account_FunctionLevelRights_GroupID = Guid.NewGuid();
                            accountGroup.Save(Connection, Transaction);
                        }

                        var PracticeAccount2UniversalProperty = ORM_HEC_MedicalPractice_2_UniversalProperty.Query.Search(Connection, Transaction, new ORM_HEC_MedicalPractice_2_UniversalProperty.Query()
                        {
                            IsDeleted    = false,
                            Tenant_RefID = securityTicket.TenantID,
                            HEC_MedicalPractice_RefID = Parameter.PracticeID
                        }).ToList();

                        List <ORM_HEC_MedicalPractice_UniversalProperty> PracticeUniversalPropertyList = new List <ORM_HEC_MedicalPractice_UniversalProperty>();
                        foreach (var item in PracticeAccount2UniversalProperty)
                        {
                            var PracticeUniversalProperty = ORM_HEC_MedicalPractice_UniversalProperty.Query.Search(Connection, Transaction, new ORM_HEC_MedicalPractice_UniversalProperty.Query()
                            {
                                IsDeleted    = false,
                                Tenant_RefID = securityTicket.TenantID,
                                HEC_MedicalPractice_UniversalPropertyID = item.HEC_MedicalPractice_UniversalProperty_RefID
                            }).Single();
                            PracticeUniversalPropertyList.Add(PracticeUniversalProperty);
                        }
                        ;

                        P_DO_GPAIDfPID_1522 practiceAccountIDParameter = new P_DO_GPAIDfPID_1522();
                        practiceAccountIDParameter.PracticeID = Parameter.PracticeID;

                        var practiceAccountToFunctionLevelRightQ = new ORM_USR_Account_2_FunctionLevelRight.Query();
                        practiceAccountToFunctionLevelRightQ.Account_RefID = cls_Get_Practice_AccountID_for_PracticeID.Invoke(Connection, Transaction, practiceAccountIDParameter, securityTicket).Result.accountID;
                        practiceAccountToFunctionLevelRightQ.Tenant_RefID  = securityTicket.TenantID;
                        practiceAccountToFunctionLevelRightQ.IsDeleted     = false;

                        var practiceAccountToFunctionLevelRight = ORM_USR_Account_2_FunctionLevelRight.Query.Search(Connection, Transaction, practiceAccountToFunctionLevelRightQ).SingleOrDefault();

                        if (practiceAccountToFunctionLevelRight != null)
                        {
                            var practiceAccountFunctionLevelRightQ = new ORM_USR_Account_FunctionLevelRight.Query();
                            practiceAccountFunctionLevelRightQ.Tenant_RefID = securityTicket.TenantID;
                            practiceAccountFunctionLevelRightQ.IsDeleted    = false;
                            practiceAccountFunctionLevelRightQ.USR_Account_FunctionLevelRightID = practiceAccountToFunctionLevelRight.FunctionLevelRight_RefID;

                            var practiceAccountFunctionLevelRight = ORM_USR_Account_FunctionLevelRight.Query.Search(Connection, Transaction, practiceAccountFunctionLevelRightQ).SingleOrDefault();

                            if (practiceAccountFunctionLevelRight != null)
                            {
                                isOpPractice = practiceAccountFunctionLevelRight.GlobalPropertyMatchingID.Equals("mm.docconect.doc.app.op.practice");
                            }
                        }

                        var functionLevelRightQ = new ORM_USR_Account_FunctionLevelRight.Query();
                        functionLevelRightQ.Tenant_RefID             = securityTicket.TenantID;
                        functionLevelRightQ.IsDeleted                = false;
                        functionLevelRightQ.GlobalPropertyMatchingID = isOpPractice ? "mm.docconect.doc.app.op.doctor" : "mm.docconect.doc.app.ac.doctor";

                        var existingFunctionLevelRight = ORM_USR_Account_FunctionLevelRight.Query.Search(Connection, Transaction, functionLevelRightQ).SingleOrDefault();

                        var tempFunctionLevelRightID = Guid.Empty;

                        if (existingFunctionLevelRight == null)
                        {
                            ORM_USR_Account_FunctionLevelRight functionLevelRight = new ORM_USR_Account_FunctionLevelRight();
                            functionLevelRight.USR_Account_FunctionLevelRightID = Guid.NewGuid();
                            functionLevelRight.FunctionLevelRights_Group_RefID  = accountGroup.USR_Account_FunctionLevelRights_GroupID;
                            functionLevelRight.Tenant_RefID       = securityTicket.TenantID;
                            functionLevelRight.Creation_Timestamp = DateTime.Now;

                            functionLevelRight.RightName = isOpPractice ? "mm.docconect.doc.app.op.doctor" : "mm.docconect.doc.app.ac.doctor";
                            functionLevelRight.GlobalPropertyMatchingID = isOpPractice ? "mm.docconect.doc.app.op.doctor" : "mm.docconect.doc.app.ac.doctor";

                            functionLevelRight.Save(Connection, Transaction);

                            tempFunctionLevelRightID = functionLevelRight.USR_Account_FunctionLevelRightID;
                        }
                        else
                        {
                            tempFunctionLevelRightID = existingFunctionLevelRight.USR_Account_FunctionLevelRightID;
                        }

                        var accountToFunctionLevelRight = new ORM_USR_Account_2_FunctionLevelRight();
                        accountToFunctionLevelRight.Tenant_RefID             = securityTicket.TenantID;
                        accountToFunctionLevelRight.Creation_Timestamp       = DateTime.Now;
                        accountToFunctionLevelRight.AssignmentID             = Guid.NewGuid();
                        accountToFunctionLevelRight.Account_RefID            = doctorAccountInfo.USR_AccountID;
                        accountToFunctionLevelRight.FunctionLevelRight_RefID = tempFunctionLevelRightID;

                        accountToFunctionLevelRight.Save(Connection, Transaction);

                        var businessParticipantQ = new ORM_CMN_BPT_BusinessParticipant.Query();

                        businessParticipantQ.IsDeleted    = false;
                        businessParticipantQ.Tenant_RefID = securityTicket.TenantID;
                        businessParticipantQ.CMN_BPT_BusinessParticipantID = doctorAccountInfo.BusinessParticipant_RefID;
                        BusinessParticipantID = doctorAccountInfo.BusinessParticipant_RefID;

                        var DoctorBusinessParticipant = ORM_CMN_BPT_BusinessParticipant.Query.Search(Connection, Transaction, businessParticipantQ).Single();
                        DoctorBusinessParticipant.IfNaturalPerson_CMN_PER_PersonInfo_RefID = Guid.NewGuid();
                        DoctorBusinessParticipant.DisplayName            = Parameter.FirstName + " " + Parameter.LastNAme;
                        DoctorBusinessParticipant.IsNaturalPerson        = true;
                        DoctorBusinessParticipant.Modification_Timestamp = DateTime.Now;
                        DoctorBusinessParticipant.Save(Connection, Transaction);
                        personInfoID = DoctorBusinessParticipant.IfNaturalPerson_CMN_PER_PersonInfo_RefID;


                        var companyInfoPractice = new ORM_CMN_PER_PersonInfo();
                        companyInfoPractice.IsDeleted            = false;
                        companyInfoPractice.Tenant_RefID         = securityTicket.TenantID;
                        companyInfoPractice.CMN_PER_PersonInfoID = personInfoID;
                        companyInfoPractice.FirstName            = Parameter.FirstName;
                        companyInfoPractice.LastName             = Parameter.LastNAme;
                        companyInfoPractice.Salutation_General   = Parameter.Salutation;
                        companyInfoPractice.Title = Parameter.Title;
                        companyInfoPractice.Save(Connection, Transaction);

                        var communicationContact = new ORM_CMN_PER_CommunicationContact();
                        communicationContact.IsDeleted              = false;
                        communicationContact.Contact_Type           = Guid.NewGuid();
                        communicationContact.Tenant_RefID           = securityTicket.TenantID;
                        communicationContact.Modification_Timestamp = DateTime.Now;
                        communicationContact.PersonInfo_RefID       = personInfoID;
                        communicationContact.Content = Parameter.Email;
                        communicationContact.Save(Connection, Transaction);
                        Guid CommunicationContactTypeID = communicationContact.Contact_Type;


                        var communicationContactType = new ORM_CMN_PER_CommunicationContact_Type();
                        communicationContactType.IsDeleted    = false;
                        communicationContactType.Tenant_RefID = securityTicket.TenantID;
                        communicationContactType.CMN_PER_CommunicationContact_TypeID = CommunicationContactTypeID;
                        communicationContactType.Type = "Email";
                        communicationContactType.Save(Connection, Transaction);

                        var communicationContact2 = new ORM_CMN_PER_CommunicationContact();
                        communicationContact2.IsDeleted              = false;
                        communicationContact2.Contact_Type           = Guid.NewGuid();
                        communicationContact2.Tenant_RefID           = securityTicket.TenantID;
                        communicationContact2.Modification_Timestamp = DateTime.Now;
                        communicationContact2.PersonInfo_RefID       = personInfoID;
                        communicationContact2.Content = Parameter.Phone;
                        communicationContact2.Save(Connection, Transaction);
                        Guid CommunicationContactTypeID2 = communicationContact2.Contact_Type;


                        var communicationContactType2 = new ORM_CMN_PER_CommunicationContact_Type();
                        communicationContactType2.IsDeleted    = false;
                        communicationContactType2.Tenant_RefID = securityTicket.TenantID;
                        communicationContactType2.CMN_PER_CommunicationContact_TypeID = CommunicationContactTypeID2;
                        communicationContactType2.Type = "Phone";
                        communicationContactType2.Save(Connection, Transaction);

                        var doctor = new ORM_HEC_Doctor();
                        doctor.HEC_DoctorID = Guid.NewGuid();
                        doctor.IsDeleted    = false;
                        doctor.Tenant_RefID = securityTicket.TenantID;
                        doctor.BusinessParticipant_RefID = BusinessParticipantID;
                        doctor.DoctorIDNumber            = Parameter.LANR.ToString();
                        doctor.Account_RefID             = Parameter.account_id;

                        doctor.Save(Connection, Transaction);

                        doctor_id = doctor.HEC_DoctorID;

                        var ogranizationUnitPractice = ORM_CMN_BPT_CTM_OrganizationalUnit.Query.Search(Connection, Transaction, new ORM_CMN_BPT_CTM_OrganizationalUnit.Query()
                        {
                            IsDeleted    = false,
                            Tenant_RefID = securityTicket.TenantID,
                            IfMedicalPractise_HEC_MedicalPractice_RefID = Parameter.PracticeID
                        }).Single();


                        var organizationalunit_Staff = new ORM_CMN_BPT_CTM_OrganizationalUnit_Staff();
                        organizationalunit_Staff.IsDeleted    = false;
                        organizationalunit_Staff.Tenant_RefID = securityTicket.TenantID;
                        organizationalunit_Staff.BusinessParticipant_RefID = BusinessParticipantID;
                        organizationalunit_Staff.OrganizationalUnit_RefID  = ogranizationUnitPractice.CMN_BPT_CTM_OrganizationalUnitID;
                        organizationalunit_Staff.Save(Connection, Transaction);

                        var CustomerPRactice = ORM_CMN_BPT_CTM_Customer.Query.Search(Connection, Transaction, new ORM_CMN_BPT_CTM_Customer.Query()
                        {
                            IsDeleted              = false,
                            Tenant_RefID           = securityTicket.TenantID,
                            CMN_BPT_CTM_CustomerID = ogranizationUnitPractice.Customer_RefID
                        }).Single();
                        var PracticeBusinessParticipant = ORM_CMN_BPT_BusinessParticipant.Query.Search(Connection, Transaction, new ORM_CMN_BPT_BusinessParticipant.Query()
                        {
                            IsDeleted    = false,
                            Tenant_RefID = securityTicket.TenantID,
                            CMN_BPT_BusinessParticipantID = CustomerPRactice.Ext_BusinessParticipant_RefID
                        }).Single();

                        PracticeName = PracticeBusinessParticipant.DisplayName;
                        PracticeBusinessParticipantID = PracticeBusinessParticipant.CMN_BPT_BusinessParticipantID;
                        PracticeCompanyInfoID         = PracticeBusinessParticipant.IfCompany_CMN_COM_CompanyInfo_RefID;

                        if (Parameter.IsUsePracticeBank)
                        {
                            var PracticeBusinessParticipant2bankaccount = ORM_CMN_BPT_BusinessParticipant_2_BankAccount.Query.Search(Connection, Transaction, new ORM_CMN_BPT_BusinessParticipant_2_BankAccount.Query()
                            {
                                IsDeleted    = false,
                                Tenant_RefID = securityTicket.TenantID,
                                CMN_BPT_BusinessParticipant_RefID = PracticeBusinessParticipant.CMN_BPT_BusinessParticipantID,
                            }).Single();


                            var business2bankAccount = new ORM_CMN_BPT_BusinessParticipant_2_BankAccount();
                            business2bankAccount.IsDeleted    = false;
                            business2bankAccount.Tenant_RefID = securityTicket.TenantID;
                            business2bankAccount.CMN_BPT_BusinessParticipant_RefID = BusinessParticipantID;
                            business2bankAccount.ACC_BNK_BankAccount_RefID         = PracticeBusinessParticipant2bankaccount.ACC_BNK_BankAccount_RefID;
                            business2bankAccount.Creation_Timestamp = DateTime.Now;
                            business2bankAccount.Save(Connection, Transaction);

                            BankAccountID = PracticeBusinessParticipant2bankaccount.ACC_BNK_BankAccount_RefID;
                            //end of save bank data if inherited from practice
                        }
                        else
                        {
                            if (!String.IsNullOrEmpty(Parameter.IBAN) || !String.IsNullOrEmpty(Parameter.Bank))
                            {
                                var business2bankAccount = new ORM_CMN_BPT_BusinessParticipant_2_BankAccount();
                                business2bankAccount.IsDeleted    = false;
                                business2bankAccount.Tenant_RefID = securityTicket.TenantID;
                                business2bankAccount.CMN_BPT_BusinessParticipant_RefID = BusinessParticipantID;
                                business2bankAccount.ACC_BNK_BankAccount_RefID         = Guid.NewGuid();
                                business2bankAccount.Creation_Timestamp = DateTime.Now;
                                business2bankAccount.Save(Connection, Transaction);

                                var bankAccountDoctor = new ORM_ACC_BNK_BankAccount();
                                bankAccountDoctor.IsDeleted             = false;
                                bankAccountDoctor.Tenant_RefID          = securityTicket.TenantID;
                                bankAccountDoctor.ACC_BNK_BankAccountID = business2bankAccount.ACC_BNK_BankAccount_RefID;
                                bankAccountDoctor.OwnerText             = Parameter.AccountHolder;
                                bankAccountDoctor.IBAN               = Parameter.IBAN;
                                bankAccountDoctor.Bank_RefID         = Guid.NewGuid();
                                bankAccountDoctor.Creation_Timestamp = DateTime.Now;
                                bankAccountDoctor.Save(Connection, Transaction);

                                if (!String.IsNullOrEmpty(Parameter.Bank))
                                {
                                    var bank = new ORM_ACC_BNK_Bank();
                                    bank.IsDeleted          = false;
                                    bank.Tenant_RefID       = securityTicket.TenantID;
                                    bank.ACC_BNK_BankID     = bankAccountDoctor.Bank_RefID;
                                    bank.BICCode            = Parameter.Bic;
                                    bank.BankName           = Parameter.Bank;
                                    bank.Creation_Timestamp = DateTime.Now;
                                    bank.Save(Connection, Transaction);
                                }

                                BankAccountID = business2bankAccount.ACC_BNK_BankAccount_RefID;
                                // end save bank data
                            }
                        }
                    }
                    catch
                    {
                        throw new Exception();
                    }
                }
                else
                {
                    var DoctorBusinessParticipant = new ORM_CMN_BPT_BusinessParticipant();

                    DoctorBusinessParticipant.IsDeleted    = false;
                    DoctorBusinessParticipant.Tenant_RefID = securityTicket.TenantID;
                    DoctorBusinessParticipant.CMN_BPT_BusinessParticipantID            = Guid.NewGuid();
                    DoctorBusinessParticipant.IfNaturalPerson_CMN_PER_PersonInfo_RefID = Guid.NewGuid();
                    DoctorBusinessParticipant.DisplayName            = Parameter.FirstName + " " + Parameter.LastNAme;
                    DoctorBusinessParticipant.IsNaturalPerson        = true;
                    DoctorBusinessParticipant.Modification_Timestamp = DateTime.Now;
                    DoctorBusinessParticipant.Save(Connection, Transaction);
                    personInfoID          = DoctorBusinessParticipant.IfNaturalPerson_CMN_PER_PersonInfo_RefID;
                    BusinessParticipantID = DoctorBusinessParticipant.CMN_BPT_BusinessParticipantID;

                    var companyInfoPractice = new ORM_CMN_PER_PersonInfo();
                    companyInfoPractice.IsDeleted            = false;
                    companyInfoPractice.Tenant_RefID         = securityTicket.TenantID;
                    companyInfoPractice.CMN_PER_PersonInfoID = personInfoID;
                    companyInfoPractice.FirstName            = Parameter.FirstName;
                    companyInfoPractice.LastName             = Parameter.LastNAme;
                    companyInfoPractice.Salutation_General   = Parameter.Salutation;
                    companyInfoPractice.Title = Parameter.Title;
                    companyInfoPractice.Save(Connection, Transaction);

                    var communicationContact = new ORM_CMN_PER_CommunicationContact();
                    communicationContact.IsDeleted              = false;
                    communicationContact.Contact_Type           = Guid.NewGuid();
                    communicationContact.Tenant_RefID           = securityTicket.TenantID;
                    communicationContact.Modification_Timestamp = DateTime.Now;
                    communicationContact.PersonInfo_RefID       = personInfoID;
                    communicationContact.Content = Parameter.Email;
                    communicationContact.Save(Connection, Transaction);
                    Guid CommunicationContactTypeID = communicationContact.Contact_Type;

                    var communicationContactType = new ORM_CMN_PER_CommunicationContact_Type();
                    communicationContactType.IsDeleted    = false;
                    communicationContactType.Tenant_RefID = securityTicket.TenantID;
                    communicationContactType.CMN_PER_CommunicationContact_TypeID = CommunicationContactTypeID;
                    communicationContactType.Type = "Email";
                    communicationContactType.Save(Connection, Transaction);

                    var communicationContact2 = new ORM_CMN_PER_CommunicationContact();
                    communicationContact2.IsDeleted              = false;
                    communicationContact2.Contact_Type           = Guid.NewGuid();
                    communicationContact2.Tenant_RefID           = securityTicket.TenantID;
                    communicationContact2.Modification_Timestamp = DateTime.Now;
                    communicationContact2.PersonInfo_RefID       = personInfoID;
                    communicationContact2.Content = Parameter.Phone;
                    communicationContact2.Save(Connection, Transaction);
                    Guid CommunicationContactTypeID2 = communicationContact2.Contact_Type;


                    var communicationContactType2 = new ORM_CMN_PER_CommunicationContact_Type();
                    communicationContactType2.IsDeleted    = false;
                    communicationContactType2.Tenant_RefID = securityTicket.TenantID;
                    communicationContactType2.CMN_PER_CommunicationContact_TypeID = CommunicationContactTypeID2;
                    communicationContactType2.Type = "Phone";
                    communicationContactType2.Save(Connection, Transaction);

                    var doctor = new ORM_HEC_Doctor();
                    doctor.HEC_DoctorID = Guid.NewGuid();
                    doctor.IsDeleted    = false;
                    doctor.Tenant_RefID = securityTicket.TenantID;
                    doctor.BusinessParticipant_RefID = BusinessParticipantID;
                    doctor.DoctorIDNumber            = Parameter.LANR.ToString();
                    doctor.Account_RefID             = Parameter.account_id;

                    doctor.Save(Connection, Transaction);

                    doctor_id = doctor.HEC_DoctorID;

                    var ogranizationUnitPractice = ORM_CMN_BPT_CTM_OrganizationalUnit.Query.Search(Connection, Transaction, new ORM_CMN_BPT_CTM_OrganizationalUnit.Query()
                    {
                        IsDeleted    = false,
                        Tenant_RefID = securityTicket.TenantID,
                        IfMedicalPractise_HEC_MedicalPractice_RefID = Parameter.PracticeID
                    }).Single();


                    var organizationalunit_Staff = new ORM_CMN_BPT_CTM_OrganizationalUnit_Staff();
                    organizationalunit_Staff.IsDeleted    = false;
                    organizationalunit_Staff.Tenant_RefID = securityTicket.TenantID;
                    organizationalunit_Staff.BusinessParticipant_RefID = BusinessParticipantID;
                    organizationalunit_Staff.OrganizationalUnit_RefID  = ogranizationUnitPractice.CMN_BPT_CTM_OrganizationalUnitID;
                    organizationalunit_Staff.Save(Connection, Transaction);


                    var CustomerPRactice = ORM_CMN_BPT_CTM_Customer.Query.Search(Connection, Transaction, new ORM_CMN_BPT_CTM_Customer.Query()
                    {
                        IsDeleted              = false,
                        Tenant_RefID           = securityTicket.TenantID,
                        CMN_BPT_CTM_CustomerID = ogranizationUnitPractice.Customer_RefID
                    }).Single();
                    var PracticeBusinessParticipant = ORM_CMN_BPT_BusinessParticipant.Query.Search(Connection, Transaction, new ORM_CMN_BPT_BusinessParticipant.Query()
                    {
                        IsDeleted    = false,
                        Tenant_RefID = securityTicket.TenantID,
                        CMN_BPT_BusinessParticipantID = CustomerPRactice.Ext_BusinessParticipant_RefID
                    }).Single();
                    PracticeName = PracticeBusinessParticipant.DisplayName;
                    PracticeBusinessParticipantID = PracticeBusinessParticipant.CMN_BPT_BusinessParticipantID;
                    PracticeCompanyInfoID         = PracticeBusinessParticipant.IfCompany_CMN_COM_CompanyInfo_RefID;
                    if (Parameter.IsUsePracticeBank == true)
                    {
                        var PracticeBusinessParticipant2bankaccount = ORM_CMN_BPT_BusinessParticipant_2_BankAccount.Query.Search(Connection, Transaction, new ORM_CMN_BPT_BusinessParticipant_2_BankAccount.Query()
                        {
                            IsDeleted    = false,
                            Tenant_RefID = securityTicket.TenantID,
                            CMN_BPT_BusinessParticipant_RefID = PracticeBusinessParticipant.CMN_BPT_BusinessParticipantID,
                        }).Single();


                        var business2bankAccount = new ORM_CMN_BPT_BusinessParticipant_2_BankAccount();
                        business2bankAccount.IsDeleted    = false;
                        business2bankAccount.Tenant_RefID = securityTicket.TenantID;
                        business2bankAccount.CMN_BPT_BusinessParticipant_RefID = BusinessParticipantID;
                        business2bankAccount.ACC_BNK_BankAccount_RefID         = PracticeBusinessParticipant2bankaccount.ACC_BNK_BankAccount_RefID;
                        business2bankAccount.Creation_Timestamp = DateTime.Now;
                        business2bankAccount.Save(Connection, Transaction);

                        BankAccountID = PracticeBusinessParticipant2bankaccount.ACC_BNK_BankAccount_RefID;
                        //end of save bank data if inherited from practice
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(Parameter.IBAN) || !String.IsNullOrEmpty(Parameter.Bank))
                        {
                            var business2bankAccount = new ORM_CMN_BPT_BusinessParticipant_2_BankAccount();
                            business2bankAccount.IsDeleted    = false;
                            business2bankAccount.Tenant_RefID = securityTicket.TenantID;
                            business2bankAccount.CMN_BPT_BusinessParticipant_RefID = BusinessParticipantID;
                            business2bankAccount.ACC_BNK_BankAccount_RefID         = Guid.NewGuid();
                            business2bankAccount.Creation_Timestamp = DateTime.Now;
                            business2bankAccount.Save(Connection, Transaction);

                            var bankAccountDoctor = new ORM_ACC_BNK_BankAccount();
                            bankAccountDoctor.IsDeleted             = false;
                            bankAccountDoctor.Tenant_RefID          = securityTicket.TenantID;
                            bankAccountDoctor.ACC_BNK_BankAccountID = business2bankAccount.ACC_BNK_BankAccount_RefID;
                            bankAccountDoctor.OwnerText             = Parameter.AccountHolder;
                            bankAccountDoctor.IBAN               = Parameter.IBAN;
                            bankAccountDoctor.Bank_RefID         = Guid.NewGuid();
                            bankAccountDoctor.Creation_Timestamp = DateTime.Now;
                            bankAccountDoctor.Save(Connection, Transaction);

                            if (!String.IsNullOrEmpty(Parameter.Bank))
                            {
                                var bank = new ORM_ACC_BNK_Bank();
                                bank.IsDeleted          = false;
                                bank.Tenant_RefID       = securityTicket.TenantID;
                                bank.ACC_BNK_BankID     = bankAccountDoctor.Bank_RefID;
                                bank.BICCode            = Parameter.Bic;
                                bank.BankName           = Parameter.Bank;
                                bank.Creation_Timestamp = DateTime.Now;
                                bank.Save(Connection, Transaction);
                            }

                            BankAccountID = business2bankAccount.ACC_BNK_BankAccount_RefID;
                            // end save bank data
                        }
                    }
                }
                var PracticeCompanyInfoAddress = ORM_CMN_COM_CompanyInfo_Address.Query.Search(Connection, Transaction, new ORM_CMN_COM_CompanyInfo_Address.Query()
                {
                    IsDeleted           = false,
                    Tenant_RefID        = securityTicket.TenantID,
                    CompanyInfo_RefID   = PracticeCompanyInfoID,
                    Address_Description = "Standard address for billing, shipping",
                }).SingleOrDefault();



                Practice_Doctors_Model DPModel = new Practice_Doctors_Model();

                if (PracticeCompanyInfoAddress != null)
                {
                    var PracticeUCD = ORM_CMN_UniversalContactDetail.Query.Search(Connection, Transaction, new ORM_CMN_UniversalContactDetail.Query()
                    {
                        IsDeleted    = false,
                        Tenant_RefID = securityTicket.TenantID,
                        CMN_UniversalContactDetailID = PracticeCompanyInfoAddress.Address_UCD_RefID,
                    }).SingleOrDefault();
                    DPModel.address = PracticeUCD.Street_Name + " " + PracticeUCD.Street_Number;
                    DPModel.zip     = PracticeUCD.ZIP;
                    DPModel.city    = PracticeUCD.Town;
                }

                DPModel.account_status = "aktiv";
                DPModel.id             = doctor_id.ToString();

                var title = string.IsNullOrEmpty(Parameter.Title) ? "" : Parameter.Title.Trim();

                DPModel.name              = title + " " + Parameter.FirstName + " " + Parameter.LastNAme;
                DPModel.name_untouched    = Parameter.FirstName + " " + Parameter.LastNAme;
                DPModel.bsnr_lanr         = Parameter.LANR.ToString();
                DPModel.autocomplete_name = title + " " + Parameter.FirstName + " " + Parameter.LastNAme;
                DPModel.salutation        = title;

                DPModel.type = "Doctor";

                DPModel.bank = string.IsNullOrEmpty(Parameter.Bank) ? "" : Parameter.Bank;

                DPModel.bank_untouched = string.IsNullOrEmpty(Parameter.Bank) ? "" : Parameter.Bank;


                DPModel.phone = Parameter.Phone;

                DPModel.email = string.IsNullOrEmpty(Parameter.Email) ? "" : Parameter.Email;

                DPModel.iban = string.IsNullOrEmpty(Parameter.IBAN) ? "" : Parameter.IBAN;

                DPModel.bic = string.IsNullOrEmpty(Parameter.Bic) ? "" : Parameter.Bic;

                DPModel.bank_id             = BankAccountID.ToString();
                DPModel.bank_info_inherited = Parameter.IsUsePracticeBank;
                DPModel.aditional_info      = "";
                DPModel.contract            = 0;


                DPModel.tenantid = securityTicket.TenantID.ToString();
                DPModel.practice_name_for_doctor = PracticeName;
                DPModel.practice_for_doctor_id   = Parameter.PracticeID.ToString();

                DPModel.role = isOpPractice ? "op" : "ac";

                List <Practice_Doctors_Model> DPModelL = new List <Practice_Doctors_Model>();
                DPModelL.Add(DPModel);

                Add_Practice_Doctors_to_Elastic.Import_Practice_Data_to_ElasticDB(DPModelL, securityTicket.TenantID.ToString());

                #region Assignment to Contract

                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)
                {
                    Guid AssignmentID = Guid.NewGuid();
                    var  insuranceTobrokerContract = ORM_HEC_CRT_InsuranceToBrokerContract.Query.Search(Connection, Transaction, new ORM_HEC_CRT_InsuranceToBrokerContract.Query()
                    {
                        Ext_CMN_CTR_Contract_RefID = contractIvi.CMN_CTR_ContractID,
                        Tenant_RefID = securityTicket.TenantID,
                        IsDeleted    = false,
                    }).SingleOrDefault();
                    if (insuranceTobrokerContract != null)
                    {
                        AssignmentID = insuranceTobrokerContract.HEC_CRT_InsuranceToBrokerContractID;
                    }
                    else
                    {
                        var insuranceTobrokerContractNew = new ORM_HEC_CRT_InsuranceToBrokerContract();
                        insuranceTobrokerContractNew.HEC_CRT_InsuranceToBrokerContractID = Guid.NewGuid();
                        insuranceTobrokerContractNew.Creation_Timestamp         = DateTime.Now;
                        insuranceTobrokerContractNew.IsDeleted                  = false;
                        insuranceTobrokerContractNew.Tenant_RefID               = securityTicket.TenantID;
                        insuranceTobrokerContractNew.Ext_CMN_CTR_Contract_RefID = contractIvi.CMN_CTR_ContractID;
                        insuranceTobrokerContractNew.Save(Connection, Transaction);
                        AssignmentID = insuranceTobrokerContractNew.HEC_CRT_InsuranceToBrokerContractID;
                    }
                    var insuranceTobrokerContract2doctor = new ORM_HEC_CRT_InsuranceToBrokerContract_ParticipatingDoctor();
                    insuranceTobrokerContract2doctor.Creation_Timestamp = DateTime.Now;
                    insuranceTobrokerContract2doctor.HEC_CRT_InsuranceToBrokerContract_ParticipatingDoctorID = Guid.NewGuid();
                    insuranceTobrokerContract2doctor.InsuranceToBrokerContract_RefID = AssignmentID;
                    insuranceTobrokerContract2doctor.Tenant_RefID = securityTicket.TenantID;
                    insuranceTobrokerContract2doctor.IsDeleted    = false;
                    insuranceTobrokerContract2doctor.Doctor_RefID = doctor_id;
                    insuranceTobrokerContract2doctor.ValidFrom    = new DateTime(2013, 6, 15);
                    insuranceTobrokerContract2doctor.ValidThrough = DateTime.MinValue;
                    insuranceTobrokerContract2doctor.Save(Connection, Transaction);

                    P_DO_CDCD_1505 ParameterDoctorID = new P_DO_CDCD_1505();
                    ParameterDoctorID.DoctorID = doctor_id;
                    var data = cls_Get_Doctor_Contract_Numbers.Invoke(Connection, Transaction, ParameterDoctorID, securityTicket).Result;

                    int DoctorContracts = data.Count();
                    Doctor_Contracts ParameterDoctor = new Doctor_Contracts();
                    ParameterDoctor.DocID = doctor_id;

                    Practice_Doctors_Model DoctorFound = Get_Doctors_for_PracticeID.Set_Contract_Number_for_DoctorID(ParameterDoctor, securityTicket);

                    if (DoctorFound != null)
                    {
                        List <Practice_Doctors_Model> DoctorFoundL = new List <Practice_Doctors_Model>();
                        DoctorFound.contract = DoctorContracts;
                        DoctorFoundL.Add(DoctorFound);
                        Add_Practice_Doctors_to_Elastic.Import_Practice_Data_to_ElasticDB(DoctorFoundL, securityTicket.TenantID.ToString());
                    }


                    P_DO_GPIDfDID_1353 ParametarDocID = new P_DO_GPIDfDID_1353();
                    ParametarDocID.DoctorID = doctor_id;
                    DO_GPIDfDID_1353[] data2 = cls_Get_PracticeID_for_DoctorID.Invoke(Connection, Transaction, ParametarDocID, securityTicket).Result;

                    P_DO_GCfPID_1507 ParametarPractice = new P_DO_GCfPID_1507();

                    ParametarPractice.PracticeID = data2.First().HEC_MedicalPractiseID;
                    DO_GCfPID_1507[] Contracts       = cls_Get_all_Doctors_Contract_Assignment_for_PracticeID.Invoke(Connection, Transaction, ParametarPractice, securityTicket).Result;
                    int NumberOfContractsForPractice = Contracts.Count();
                    Practice_Doctors_Model practice  = new Practice_Doctors_Model();
                    practice.id = ParametarPractice.PracticeID.ToString();

                    Practice_Doctors_Model        PracticeFound = Get_Doctors_for_PracticeID.Get_Practice_for_PracticeID(practice, securityTicket);
                    List <Practice_Doctors_Model> practiceL     = new List <Practice_Doctors_Model>();
                    PracticeFound.contract = NumberOfContractsForPractice;
                    practiceL.Add(PracticeFound);
                    Add_Practice_Doctors_to_Elastic.Import_Practice_Data_to_ElasticDB(practiceL, securityTicket.TenantID.ToString());
                }
                #endregion



                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;
            }
        }
Exemplo n.º 3
0
        public static Guid Save_accounts_to_DBPerson(Doctor_model_from_xlsx Parameter, 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
            {
                IAccountServiceProvider accountService;
                var _providerFactory = ProviderFactory.Instance;
                accountService = _providerFactory.CreateAccountServiceProvider();
                string[] stringUser  = Parameter.LoginEmail.Split('@');
                string   usernameStr = stringUser[0];

                Person DoctorAccount = new Person();
                DoctorAccount.FirstName = Parameter.FirstName;
                DoctorAccount.LastName  = Parameter.LastNAme;
                DoctorAccount.Title     = Parameter.Title;
                var     accId   = Guid.NewGuid();
                Account account = new Account();
                account.Person       = DoctorAccount;
                account.ID           = accId;
                account.TenantID     = securityTicket.TenantID;
                account.Email        = Parameter.LoginEmail;
                account.PasswordHash = ValidationMethods.CalculateMD5Hash(Parameter.inPassword);
                account.Username     = usernameStr;
                account.AccountType  = EAccountType.Standard;
                account.Verified     = true;
                accountService.CreateAccount(account, securityTicket.SessionTicket);
                accountService.VerifyAccount(account.ID);

                return(accId);

                //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;
            }
        }