private static List <ORM_CMN_BPT_CTM_OrganizationalUnit_Address> Search(Query query, string connectionString, DbConnection connection, DbTransaction transaction) { CSV2Core.Core.Interfaces.IManagedConnection managedConnection = new CSV2Core_MySQL.MySQLManagedConnection(); List <ORM_CMN_BPT_CTM_OrganizationalUnit_Address> items; try { managedConnection.set(connectionString, connection, transaction); var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(managedConnection.getConnection(), managedConnection.getTransaction()); DbCommand command = managedConnection.manage(query.CreateSelectQuery(TableName)); query.SetParameters(command); items = new List <ORM_CMN_BPT_CTM_OrganizationalUnit_Address>(); var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader()); reader.SetOrdinals(new string[] { "CMN_BPT_CTM_OrganizationalUnit_AddressID", "UniversalContactDetail_Address_RefID", "OrganizationalUnit_RefID", "IsPrimary", "AddressType", "Creation_Timestamp", "Tenant_RefID", "IsDeleted", "Modification_Timestamp" }); while (reader.Read()) { ORM_CMN_BPT_CTM_OrganizationalUnit_Address item = new ORM_CMN_BPT_CTM_OrganizationalUnit_Address(); //0:Parameter CMN_BPT_CTM_OrganizationalUnit_AddressID of type Guid item.CMN_BPT_CTM_OrganizationalUnit_AddressID = reader.GetGuid(0); //1:Parameter UniversalContactDetail_Address_RefID of type Guid item.UniversalContactDetail_Address_RefID = reader.GetGuid(1); //2:Parameter OrganizationalUnit_RefID of type Guid item.OrganizationalUnit_RefID = reader.GetGuid(2); //3:Parameter IsPrimary of type Boolean item.IsPrimary = reader.GetBoolean(3); //4:Parameter AddressType of type int item.AddressType = reader.GetInteger(4); //5:Parameter Creation_Timestamp of type DateTime item.Creation_Timestamp = reader.GetDate(5); //6:Parameter Tenant_RefID of type Guid item.Tenant_RefID = reader.GetGuid(6); //7:Parameter IsDeleted of type Boolean item.IsDeleted = reader.GetBoolean(7); //8:Parameter Modification_Timestamp of type DateTime item.Modification_Timestamp = reader.GetDate(8); item.Status_IsAlreadySaved = true; item.Status_IsDirty = false; items.Add(item); } reader.Close(); loader.Load(); managedConnection.commit(); } catch (Exception ex) { managedConnection.rollback(); throw; } return(items); }
protected static FR_Guid Execute(DbConnection Connection, DbTransaction Transaction, P_L3OS_COUaMR_1344 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { //Leave UserCode region to enable user code saving #region UserCode var returnValue = new FR_Guid(); #region Preload data var languages = ORM_CMN_Language.Query.Search(Connection, Transaction, new ORM_CMN_Language.Query { IsDeleted = false, Tenant_RefID = securityTicket.TenantID }); #endregion #region Retrieve already saved org. units and addresses var retrievedOrgUnits = CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit.Query.Search(Connection, Transaction, new ORM_CMN_BPT_CTM_OrganizationalUnit.Query { Customer_RefID = Parameter.CustomerID, IsDeleted = false, Tenant_RefID = securityTicket.TenantID }); var retrievedOrgUnitOfficeITLs = retrievedOrgUnits.Select(x => x.CustomerTenant_OfficeITL); String[] officesForDeletionITLs = retrievedOrgUnitOfficeITLs.Except(Parameter.OrganizationUnits.Select(x => x.OfficeITL)).ToArray(); String[] officesToUpdateITLs = retrievedOrgUnitOfficeITLs.Intersect(Parameter.OrganizationUnits.Select(x => x.OfficeITL)).ToArray(); String[] officesToCreateITLs = Parameter.OrganizationUnits.Select(x => x.OfficeITL).Except(officesToUpdateITLs).ToArray(); var dOffices = new Dictionary <String, Guid>(); foreach (var item in retrievedOrgUnits) { dOffices[item.CustomerTenant_OfficeITL] = item.CMN_BPT_CTM_OrganizationalUnitID; } // generate IDs for new offices foreach (var item in officesToCreateITLs) { dOffices[item] = Guid.NewGuid(); } #endregion #region Deleted Offices foreach (var deletingOfficeITL in officesForDeletionITLs) { var officeForDeletion = CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit.Query.Search(Connection, Transaction, new ORM_CMN_BPT_CTM_OrganizationalUnit.Query { CustomerTenant_OfficeITL = deletingOfficeITL, IsDeleted = false, Tenant_RefID = securityTicket.TenantID }).Single(); officeForDeletion.IsDeleted = true; officeForDeletion.Save(Connection, Transaction); var addressesForDeletion = CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query.Search(Connection, Transaction, new ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query { OrganizationalUnit_RefID = Guid.Parse(deletingOfficeITL), IsDeleted = false, Tenant_RefID = securityTicket.TenantID }); foreach (var item in addressesForDeletion) { // Delete all ORM_CMN_UniversalContactDetail connected to this Org. unit address CL1_CMN.ORM_CMN_UniversalContactDetail.Query.SoftDelete(Connection, Transaction, new CL1_CMN.ORM_CMN_UniversalContactDetail.Query { CMN_UniversalContactDetailID = item.UniversalContactDetail_Address_RefID, IsDeleted = false, Tenant_RefID = securityTicket.TenantID }); // Delete ORM_CMN_BPT_CTM_OrganizationalUnit_Address item.IsDeleted = true; item.Save(Connection, Transaction); } } #endregion CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit orgUnit = null; CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit_Address address = null; #region Save new organizational units with addresses foreach (var item in Parameter.OrganizationUnits.Where(x => officesToCreateITLs.Contains(x.OfficeITL))) { Dict officeNameDict = new Dict(ORM_CMN_BPT_CTM_OrganizationalUnit.TableName, Guid.NewGuid()); foreach (var lang in languages) { officeNameDict.AddEntry(lang.CMN_LanguageID, item.Name); } orgUnit = new ORM_CMN_BPT_CTM_OrganizationalUnit { CMN_BPT_CTM_OrganizationalUnitID = dOffices[item.OfficeITL], CustomerTenant_OfficeITL = item.OfficeITL, Customer_RefID = Parameter.CustomerID, Parent_OrganizationalUnit_RefID = String.IsNullOrEmpty(item.ParentOfficeITL) ? Guid.Empty : dOffices[item.ParentOfficeITL], OrganizationalUnit_SimpleName = item.Name, OrganizationalUnit_Name = officeNameDict, InternalOrganizationalUnitNumber = item.Code, InternalOrganizationalUnitSimpleName = item.Name, OrganizationalUnit_Description = new Dict(ORM_CMN_BPT_CTM_OrganizationalUnit.TableName, Guid.NewGuid()), Creation_Timestamp = DateTime.Now, Tenant_RefID = securityTicket.TenantID, IsDeleted = false, ExternalOrganizationalUnitNumber = item.Code, Default_PhoneNumber = item.ContactPhone, Default_FaxNumber = item.ContactFax, }; if (String.IsNullOrEmpty(item.BillingAddressITL) == false) { var billingAddress = Parameter.Addresses.SingleOrDefault(x => item.BillingAddressITL == x.AddressITL); address = new ORM_CMN_BPT_CTM_OrganizationalUnit_Address { CMN_BPT_CTM_OrganizationalUnit_AddressID = Guid.NewGuid(), AddressType = (int)OrganizationalUnitAddressType.Billing, IsPrimary = billingAddress.IsPrimaryBillingAddress, OrganizationalUnit_RefID = orgUnit.CMN_BPT_CTM_OrganizationalUnitID, Creation_Timestamp = DateTime.Now, Tenant_RefID = securityTicket.TenantID }; var ucd = new CL1_CMN.ORM_CMN_UniversalContactDetail { CMN_UniversalContactDetailID = Guid.NewGuid(), CompanyName_Line1 = null, Street_Name = billingAddress.StreetName, Street_Number = billingAddress.StreetNumber, ZIP = billingAddress.ZipCode, Town = billingAddress.City, Country_639_1_ISOCode = billingAddress.CountryISO, Creation_Timestamp = DateTime.Now, Tenant_RefID = securityTicket.TenantID }; ucd.Save(Connection, Transaction); address.UniversalContactDetail_Address_RefID = ucd.CMN_UniversalContactDetailID; address.Save(Connection, Transaction); } if (String.IsNullOrEmpty(item.ShippingAddressITL) == false) { var shippingAddress = Parameter.Addresses.SingleOrDefault(x => item.ShippingAddressITL == x.AddressITL); address = new ORM_CMN_BPT_CTM_OrganizationalUnit_Address { CMN_BPT_CTM_OrganizationalUnit_AddressID = Guid.NewGuid(), AddressType = (int)OrganizationalUnitAddressType.Shipping, IsPrimary = shippingAddress.IsPrimaryBillingAddress, OrganizationalUnit_RefID = orgUnit.CMN_BPT_CTM_OrganizationalUnitID, Creation_Timestamp = DateTime.Now, Tenant_RefID = securityTicket.TenantID }; var ucd = new CL1_CMN.ORM_CMN_UniversalContactDetail { CMN_UniversalContactDetailID = Guid.NewGuid(), CompanyName_Line1 = null, Street_Name = shippingAddress.StreetName, Street_Number = shippingAddress.StreetNumber, ZIP = shippingAddress.ZipCode, Town = shippingAddress.City, Country_639_1_ISOCode = shippingAddress.CountryISO, Creation_Timestamp = DateTime.Now, Tenant_RefID = securityTicket.TenantID }; ucd.Save(Connection, Transaction); address.UniversalContactDetail_Address_RefID = ucd.CMN_UniversalContactDetailID; address.Save(Connection, Transaction); } orgUnit.Save(Connection, Transaction); dOffices[item.OfficeITL] = orgUnit.CMN_BPT_CTM_OrganizationalUnitID; } #endregion #region Update org. units with addresses foreach (var item in Parameter.OrganizationUnits.Where(x => officesToUpdateITLs.Contains(x.OfficeITL))) { orgUnit = retrievedOrgUnits.Single(x => x.CustomerTenant_OfficeITL == item.OfficeITL); #region Addresses #region Billing Addresses if (String.IsNullOrEmpty(item.BillingAddressITL)) { ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query.SoftDelete(Connection, Transaction, new CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query { OrganizationalUnit_RefID = orgUnit.CMN_BPT_CTM_OrganizationalUnitID, AddressType = (int)OrganizationalUnitAddressType.Billing }); } else { var billingAddress = Parameter.Addresses.SingleOrDefault(x => item.BillingAddressITL == x.AddressITL); var existingBillingAdress = CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query.Search(Connection, Transaction, new CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query { OrganizationalUnit_RefID = orgUnit.CMN_BPT_CTM_OrganizationalUnitID, AddressType = (int)OrganizationalUnitAddressType.Billing, IsDeleted = false, Tenant_RefID = securityTicket.TenantID }).SingleOrDefault(); if (existingBillingAdress == null) { #region Create New address = new ORM_CMN_BPT_CTM_OrganizationalUnit_Address { CMN_BPT_CTM_OrganizationalUnit_AddressID = Guid.NewGuid(), AddressType = (int)OrganizationalUnitAddressType.Billing, IsPrimary = billingAddress.IsPrimaryBillingAddress, OrganizationalUnit_RefID = orgUnit.CMN_BPT_CTM_OrganizationalUnitID, Creation_Timestamp = DateTime.Now, Tenant_RefID = securityTicket.TenantID }; var ucd = new CL1_CMN.ORM_CMN_UniversalContactDetail { CMN_UniversalContactDetailID = Guid.NewGuid(), CompanyName_Line1 = null, Street_Name = billingAddress.StreetName, Street_Number = billingAddress.StreetNumber, ZIP = billingAddress.ZipCode, Town = billingAddress.City, Country_639_1_ISOCode = billingAddress.CountryISO, Creation_Timestamp = DateTime.Now, Tenant_RefID = securityTicket.TenantID }; ucd.Save(Connection, Transaction); address.UniversalContactDetail_Address_RefID = ucd.CMN_UniversalContactDetailID; address.Save(Connection, Transaction); #endregion } else { #region Update existing var ucd = CL1_CMN.ORM_CMN_UniversalContactDetail.Query.Search(Connection, Transaction, new CL1_CMN.ORM_CMN_UniversalContactDetail.Query { CMN_UniversalContactDetailID = existingBillingAdress.UniversalContactDetail_Address_RefID, IsDeleted = false, Tenant_RefID = securityTicket.TenantID }).Single(); ucd.Street_Name = billingAddress.StreetName; ucd.Street_Number = billingAddress.StreetNumber; ucd.ZIP = billingAddress.ZipCode; ucd.Town = billingAddress.City; ucd.Country_639_1_ISOCode = billingAddress.CountryISO; ucd.Save(Connection, Transaction); #endregion } } #endregion #region Shipping Addresses if (String.IsNullOrEmpty(item.ShippingAddressITL)) { ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query.SoftDelete(Connection, Transaction, new CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query { OrganizationalUnit_RefID = orgUnit.CMN_BPT_CTM_OrganizationalUnitID, AddressType = (int)OrganizationalUnitAddressType.Shipping }); } else { var shippingAddress = Parameter.Addresses.SingleOrDefault(x => item.ShippingAddressITL == x.AddressITL); var existingShippingAddress = CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query.Search(Connection, Transaction, new CL1_CMN_BPT_CTM.ORM_CMN_BPT_CTM_OrganizationalUnit_Address.Query { OrganizationalUnit_RefID = orgUnit.CMN_BPT_CTM_OrganizationalUnitID, AddressType = (int)OrganizationalUnitAddressType.Shipping, IsDeleted = false, Tenant_RefID = securityTicket.TenantID }).SingleOrDefault(); if (existingShippingAddress == null) { #region Create New address = new ORM_CMN_BPT_CTM_OrganizationalUnit_Address { CMN_BPT_CTM_OrganizationalUnit_AddressID = Guid.NewGuid(), AddressType = (int)OrganizationalUnitAddressType.Shipping, IsPrimary = shippingAddress.IsPrimaryShippingAddress, OrganizationalUnit_RefID = orgUnit.CMN_BPT_CTM_OrganizationalUnitID, Creation_Timestamp = DateTime.Now, Tenant_RefID = securityTicket.TenantID }; var ucd = new CL1_CMN.ORM_CMN_UniversalContactDetail { CMN_UniversalContactDetailID = Guid.NewGuid(), CompanyName_Line1 = null, Street_Name = shippingAddress.StreetName, Street_Number = shippingAddress.StreetNumber, ZIP = shippingAddress.ZipCode, Town = shippingAddress.City, Country_639_1_ISOCode = shippingAddress.CountryISO, Creation_Timestamp = DateTime.Now, Tenant_RefID = securityTicket.TenantID }; ucd.Save(Connection, Transaction); address.UniversalContactDetail_Address_RefID = ucd.CMN_UniversalContactDetailID; address.Save(Connection, Transaction); #endregion } else { #region Update existing var ucd = CL1_CMN.ORM_CMN_UniversalContactDetail.Query.Search(Connection, Transaction, new CL1_CMN.ORM_CMN_UniversalContactDetail.Query { CMN_UniversalContactDetailID = existingShippingAddress.UniversalContactDetail_Address_RefID, IsDeleted = false, Tenant_RefID = securityTicket.TenantID }).Single(); ucd.Street_Name = shippingAddress.StreetName; ucd.Street_Number = shippingAddress.StreetNumber; ucd.ZIP = shippingAddress.ZipCode; ucd.Town = shippingAddress.City; ucd.Country_639_1_ISOCode = shippingAddress.CountryISO; ucd.Save(Connection, Transaction); #endregion } } #endregion #endregion #region Org. unit foreach (DictEntry dictEntry in orgUnit.OrganizationalUnit_Name.Contents) { dictEntry.Content = item.Name; } orgUnit.Parent_OrganizationalUnit_RefID = String.IsNullOrEmpty(item.ParentOfficeITL) ? Guid.Empty : dOffices[item.ParentOfficeITL]; orgUnit.ExternalOrganizationalUnitNumber = item.Code; orgUnit.OrganizationalUnit_SimpleName = item.Name; orgUnit.Default_PhoneNumber = item.ContactPhone; orgUnit.Default_FaxNumber = item.ContactFax; orgUnit.Save(Connection, Transaction); #endregion } #endregion return(returnValue); #endregion UserCode }