protected static FR_L3AR_IoUPBD_1631_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L3AR_IoUPBD_1631 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { #region UserCode var returnValue = new FR_L3AR_IoUPBD_1631_Array(); var result = new List <L3AR_IoUPBD_1631>(); #region Get All Producers for Tenant var allProducers = ORM_CMN_BPT_BusinessParticipant.Query.Search(Connection, Transaction, new ORM_CMN_BPT_BusinessParticipant.Query() { Tenant_RefID = securityTicket.TenantID, IsDeleted = false }); #endregion #region Get Taxes For Default Country var taxesToImport = Parameter.Products.Select(i => i.VAT).Distinct(); var param = new P_L3TX_STfDCaRR_1119(); param.TaxRates = taxesToImport.ToArray(); var allTaxes = cls_Save_Taxes_for_DefaultCountry_and_ReturnResult.Invoke(Connection, Transaction, param, securityTicket).Result; #endregion #region DosageForms var dosageQuery = new ORM_HEC_Product_DosageForm.Query(); dosageQuery.Tenant_RefID = securityTicket.TenantID; dosageQuery.IsDeleted = false; var allDosages = ORM_HEC_Product_DosageForm.Query.Search(Connection, Transaction, dosageQuery); #endregion foreach (var item in Parameter.Products) { bool alreadyExistingInDB = true; #region ORM_CMN_PRO_Product var productQuery = new ORM_CMN_PRO_Product.Query(); productQuery.ProductITL = item.ProductITL; productQuery.Tenant_RefID = securityTicket.TenantID; productQuery.IsDeleted = false; productQuery.IsProductAvailableForOrdering = true; var product = ORM_CMN_PRO_Product.Query.Search(Connection, Transaction, productQuery).FirstOrDefault(); if (product == null) { product = new ORM_CMN_PRO_Product(); product.CMN_PRO_ProductID = Guid.NewGuid(); product.ProductITL = item.ProductITL; product.PackageInfo_RefID = Guid.NewGuid(); product.Creation_Timestamp = DateTime.Now; product.Tenant_RefID = securityTicket.TenantID; alreadyExistingInDB = false; } product.Product_Name = item.Product_Name; product.Product_Description = item.Product_Description; product.Product_Number = item.Product_Number; product.IsProduct_Article = true; product.IsProductAvailableForOrdering = true; product.DefaultExpirationPeriod_in_sec = item.DefaultExpirationPeriod_in_sec; if (item.DefaultStorageTemperature_min_in_kelvin != 0) { product.DefaultStorageTemperature_min_in_kelvin = (float)Math.Round(item.DefaultStorageTemperature_min_in_kelvin, 2); } if (item.DefaultStorageTemperature_max_in_kelvin != 0) { product.DefaultStorageTemperature_max_in_kelvin = (float)Math.Round(item.DefaultStorageTemperature_max_in_kelvin, 2); } product.IsStorage_CoolingRequired = item.IsStorage_CoolingRequired; var xmlModel = new ProductAdditionalInfoXML() { IsPharmacyOnlyDistribution = item.IsPharmacyOnlyDistribution }; product.ProductAdditionalInfoXML = xmlModel.ToPayload(); product.IsStorage_ExpiryDateMandatory = (item.DefaultExpirationPeriod_in_sec != 0); product.Save(Connection, Transaction); #endregion #region Save Producer var producerBPT = allProducers.Where(i => i.DisplayName == item.Producer).FirstOrDefault(); if (producerBPT == default(ORM_CMN_BPT_BusinessParticipant)) { producerBPT = new ORM_CMN_BPT_BusinessParticipant(); producerBPT.CMN_BPT_BusinessParticipantID = Guid.NewGuid(); producerBPT.DisplayName = item.Producer; producerBPT.IsCompany = true; producerBPT.Tenant_RefID = securityTicket.TenantID; producerBPT.Creation_Timestamp = DateTime.Now; producerBPT.Save(Connection, Transaction); } product.ProducingBusinessParticipant_RefID = producerBPT.CMN_BPT_BusinessParticipantID; product.Save(Connection, Transaction); #endregion #region Product Code (EAN) var product2Code = ORM_CMN_PRO_Product_2_ProductCode.Query.Search(Connection, Transaction, new ORM_CMN_PRO_Product_2_ProductCode.Query() { CMN_PRO_Product_RefID = product.CMN_PRO_ProductID, Tenant_RefID = securityTicket.TenantID, IsDeleted = false }).SingleOrDefault(); if (product2Code == null) { var eanID = DMProductCodeTypes.Get_ProductCodeType_ByGlobalMatchingID(Connection, Transaction, EProductCodeType.EAN, securityTicket); var productCode = new ORM_CMN_PRO_ProductCode(); productCode.CMN_PRO_ProductCodeID = Guid.NewGuid(); productCode.ProductCode_Type_RefID = eanID; productCode.ProductCode_Value = String.Empty; productCode.Creation_Timestamp = DateTime.Now; productCode.Tenant_RefID = securityTicket.TenantID; productCode.Save(Connection, Transaction); product2Code = new ORM_CMN_PRO_Product_2_ProductCode(); product2Code.AssignmentID = Guid.NewGuid(); product2Code.CMN_PRO_Product_RefID = product.CMN_PRO_ProductID; product2Code.CMN_PRO_ProductCode_RefID = productCode.CMN_PRO_ProductCodeID; product2Code.Creation_Timestamp = DateTime.Now; product2Code.Tenant_RefID = securityTicket.TenantID; product2Code.Save(Connection, Transaction); } else { var productCode = new ORM_CMN_PRO_ProductCode(); productCode.Load(Connection, Transaction, product2Code.CMN_PRO_ProductCode_RefID); productCode.ProductCode_Value = String.Empty; productCode.Save(Connection, Transaction); } #endregion #region Unit //Unit could be used for multiple products, there is no sense to edit unit //If there is need to change unit, we should find unit with that iso code and change reference var unitsQuery = new ORM_CMN_Unit.Query(); unitsQuery.Tenant_RefID = securityTicket.TenantID; unitsQuery.ISOCode = item.MeasuredInUnit_ISO_um_ums; unitsQuery.IsDeleted = false; var measuredInUnit = ORM_CMN_Unit.Query.Search(Connection, Transaction, unitsQuery).FirstOrDefault(); if (measuredInUnit == null) { measuredInUnit = new ORM_CMN_Unit(); measuredInUnit.Tenant_RefID = securityTicket.TenantID; measuredInUnit.Creation_Timestamp = DateTime.Now; measuredInUnit.ISOCode = item.MeasuredInUnit_ISO_um_ums; measuredInUnit.CMN_UnitID = Guid.NewGuid(); measuredInUnit.Save(Connection, Transaction); } #endregion #region PackageInfo var packageInfo = new ORM_CMN_PRO_PAC_PackageInfo(); packageInfo.Load(Connection, Transaction, product.PackageInfo_RefID); if (packageInfo.CMN_PRO_PAC_PackageInfoID == Guid.Empty) { packageInfo.CMN_PRO_PAC_PackageInfoID = product.PackageInfo_RefID; packageInfo.Creation_Timestamp = DateTime.Now; packageInfo.Tenant_RefID = securityTicket.TenantID; } packageInfo.PackageContent_DisplayLabel = item.Amount; packageInfo.PackageContent_Amount = PackageAmountUtils.GetPackageAmount(item.Amount); packageInfo.PackageContent_MeasuredInUnit_RefID = measuredInUnit.CMN_UnitID; packageInfo.Save(Connection, Transaction); #endregion #region VAT var salesTax = ORM_CMN_PRO_Product_SalesTaxAssignmnet.Query.Search(Connection, Transaction, new ORM_CMN_PRO_Product_SalesTaxAssignmnet.Query() { Product_RefID = product.CMN_PRO_ProductID, Tenant_RefID = securityTicket.TenantID, IsDeleted = false }).SingleOrDefault(); if (salesTax == null) { salesTax = new ORM_CMN_PRO_Product_SalesTaxAssignmnet(); salesTax.CMN_PRO_Product_SalesTaxAssignmnetID = Guid.NewGuid(); salesTax.Product_RefID = product.CMN_PRO_ProductID; salesTax.Creation_Timestamp = DateTime.Now; salesTax.Tenant_RefID = securityTicket.TenantID; } salesTax.ApplicableSalesTax_RefID = allTaxes.Where(i => i.TaxRate == item.VAT).Select(j => j.TaxID).FirstOrDefault(); salesTax.Save(Connection, Transaction); #endregion #region DosageForms //DosageForms could be used for multiple products, there is no sense to edit unit //If there is need to change unit, we should find unit with that GlobalPropertyMatchingID and change reference var dosage = allDosages.Where(i => i.GlobalPropertyMatchingID == item.Dosage).FirstOrDefault(); //if not create new if (dosage == null) { dosage = new ORM_HEC_Product_DosageForm(); dosage.HEC_Product_DosageFormID = Guid.NewGuid(); dosage.Tenant_RefID = securityTicket.TenantID; dosage.Creation_Timestamp = DateTime.Now; dosage.GlobalPropertyMatchingID = item.Dosage; dosage.Save(Connection, Transaction); } #endregion #region HecProduct var hec_product = ORM_HEC_Product.Query.Search(Connection, Transaction, new ORM_HEC_Product.Query() { Ext_PRO_Product_RefID = product.CMN_PRO_ProductID, Tenant_RefID = securityTicket.TenantID, IsDeleted = false }).SingleOrDefault(); if (hec_product == null) { hec_product = new ORM_HEC_Product(); hec_product.HEC_ProductID = Guid.NewGuid(); hec_product.Creation_Timestamp = DateTime.Now; hec_product.Tenant_RefID = securityTicket.TenantID; } hec_product.Ext_PRO_Product_RefID = product.CMN_PRO_ProductID; hec_product.ProductDosageForm_RefID = dosage.HEC_Product_DosageFormID; hec_product.ProductDistributionStatus = item.DistributionStatus; hec_product.IsProduct_AddictiveDrug = item.IsProduct_AddictiveDrug; hec_product.Save(Connection, Transaction); #endregion var date = DateTime.Now; #region Save Active Components var componentsParam = new P_L3CO_SCfIPFC_1324(); componentsParam.HEC_ProductID = hec_product.HEC_ProductID; componentsParam.ActiveComponents = item.ActiveComponents; cls_Save_Componets_for_ImportedProductFromCatalog.Invoke(Connection, Transaction, componentsParam, securityTicket); #endregion result.Add(new L3AR_IoUPBD_1631() { ProductITL = product.ProductITL, ProductID = product.CMN_PRO_ProductID, IsAlreadyExisted = alreadyExistingInDB }); date = DateTime.Now; } returnValue.Result = result.ToArray(); return(returnValue); #endregion UserCode }
protected static FR_Guid Execute(DbConnection Connection, DbTransaction Transaction, P_L5AR_SA_1525 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { #region UserCode var returnValue = new FR_Guid(); var languageQuery = new ORM_CMN_Language.Query(); languageQuery.Tenant_RefID = securityTicket.TenantID; languageQuery.IsDeleted = false; var languages = ORM_CMN_Language.Query.Search(Connection, Transaction, languageQuery); var product = new ORM_CMN_PRO_Product(); if (Parameter.ArticleID == Guid.Empty) { #region ORM_CMN_PRO_Product product = new ORM_CMN_PRO_Product(); product.CMN_PRO_ProductID = Guid.NewGuid(); product.ProductITL = Guid.NewGuid().ToString(); product.IsProduct_Article = true; product.IsProductAvailableForOrdering = true; product.IsImportedFromExternalCatalog = false; product.Product_Name = new Dict(ORM_CMN_PRO_Product.TableName); product.Product_Description = new Dict(ORM_CMN_PRO_Product.TableName); product.PackageInfo_RefID = Guid.NewGuid(); product.Creation_Timestamp = DateTime.Now; product.Tenant_RefID = securityTicket.TenantID; product.Save(Connection, Transaction); #endregion #region CustomArticles Group var customerArticlesGroupID = Guid.Empty; var customArticlesGlobalMatching = EnumUtils.GetEnumDescription(EProductGroup.CustomArticles); var customerArticlesGroup = ORM_CMN_PRO_ProductGroup.Query.Search(Connection, Transaction, new ORM_CMN_PRO_ProductGroup.Query() { GlobalPropertyMatchingID = customArticlesGlobalMatching, IsDeleted = false, Tenant_RefID = securityTicket.TenantID }); if (customerArticlesGroup == null || customerArticlesGroup.Count() == 0) { var group = new ORM_CMN_PRO_ProductGroup(); group.CMN_PRO_ProductGroupID = Guid.NewGuid(); group.GlobalPropertyMatchingID = customArticlesGlobalMatching; group.ProductGroup_Name = new Dict(ORM_CMN_PRO_ProductGroup.TableName); foreach (var language in languages) { group.ProductGroup_Name.AddEntry(language.CMN_LanguageID, "Custom Articles"); } group.ProductGroup_Description = new Dict(ORM_CMN_PRO_ProductGroup.TableName); group.IsDeleted = false; group.Creation_Timestamp = DateTime.Now; group.Tenant_RefID = securityTicket.TenantID; group.Save(Connection, Transaction); customerArticlesGroupID = group.CMN_PRO_ProductGroupID; } else { customerArticlesGroupID = customerArticlesGroup.Single().CMN_PRO_ProductGroupID; } var product2Group = new ORM_CMN_PRO_Product_2_ProductGroup(); product2Group.AssignmentID = Guid.NewGuid(); product2Group.CMN_PRO_Product_RefID = product.CMN_PRO_ProductID; product2Group.CMN_PRO_ProductGroup_RefID = customerArticlesGroupID; product2Group.Creation_Timestamp = DateTime.Now; product2Group.Tenant_RefID = securityTicket.TenantID; product2Group.Save(Connection, Transaction); #endregion } else { product.Load(Connection, Transaction, Parameter.ArticleID); } #region ORM_CMN_PRO_Product product.Product_Number = Parameter.PZN; foreach (var language in languages) { product.Product_Name.UpdateEntry(language.CMN_LanguageID, Parameter.ArticleName); } foreach (var language in languages) { product.Product_Description.UpdateEntry(language.CMN_LanguageID, Parameter.Notice); } product.DefaultStorageTemperature_min_in_kelvin = Parameter.StorageTemperatureFrom_in_kelvin; product.DefaultStorageTemperature_max_in_kelvin = Parameter.StorageTemperatureTo_in_kelvin; product.DefaultExpirationPeriod_in_sec = Parameter.Expiring_in_seconds; product.ProductType_RefID = Parameter.ArticleType_RefID; product.IsPlaceholderArticle = Parameter.IsDummy; product.IsStorage_CoolingRequired = Parameter.IsStorage_CoolingRequired; product.IsStorage_BatchNumberMandatory = Parameter.IsStorage_BatchNumberMandatory; product.IsStorage_ExpiryDateMandatory = Parameter.IsStorage_ExpiryDateMandatory; product.IsProductPartOfDefaultStock = Parameter.IsProduct_PartOfDefaultStock; var xml = new ProductAdditionalInfoXML() { IsPharmacyOnlyDistribution = Parameter.IsProduct_PharmacyOnlyDistribution }; product.ProductAdditionalInfoXML = xml.ToPayload(); product.ProductSuccessor_RefID = Parameter.ProductSuccessor_RefID; product.Save(Connection, Transaction); #endregion #region ORM_HEC_Product var query = new ORM_HEC_Product.Query(); query.Ext_PRO_Product_RefID = product.CMN_PRO_ProductID; query.IsDeleted = false; query.Tenant_RefID = securityTicket.TenantID; var hecProduct = ORM_HEC_Product.Query.Search(Connection, Transaction, query).SingleOrDefault(); if (hecProduct == null) { hecProduct = new ORM_HEC_Product(); hecProduct.HEC_ProductID = Guid.NewGuid(); hecProduct.Ext_PRO_Product_RefID = product.CMN_PRO_ProductID; hecProduct.Creation_Timestamp = DateTime.Now; hecProduct.Tenant_RefID = securityTicket.TenantID; } hecProduct.ProductDosageForm_RefID = Parameter.DosageForm_RefID; hecProduct.IsProduct_AddictiveDrug = Parameter.IsProduct_AddictiveDrug; hecProduct.Save(Connection, Transaction); #endregion #region ORM_CMN_PRO_Product_2_ProductGroup var groupQuery = new ORM_CMN_PRO_Product_2_ProductGroup.Query(); groupQuery.CMN_PRO_Product_RefID = product.CMN_PRO_ProductID; groupQuery.IsDeleted = false; groupQuery.Tenant_RefID = securityTicket.TenantID; var alreadyPersistedGroups = ORM_CMN_PRO_Product_2_ProductGroup.Query.Search(Connection, Transaction, groupQuery); //delete removed groups foreach (var item in alreadyPersistedGroups) { var globalGroup = new ORM_CMN_PRO_ProductGroup(); globalGroup.Load(Connection, Transaction, item.CMN_PRO_ProductGroup_RefID); if (!Parameter.ArticleGroup_RefIDs.Contains(item.CMN_PRO_ProductGroup_RefID) && String.IsNullOrEmpty(globalGroup.GlobalPropertyMatchingID)) { item.IsDeleted = true; item.Save(Connection, Transaction); } } //add new groups foreach (var groupID in Parameter.ArticleGroup_RefIDs) { if (!alreadyPersistedGroups.Select(i => i.CMN_PRO_ProductGroup_RefID).Contains(groupID)) { var group = new ORM_CMN_PRO_Product_2_ProductGroup(); group.AssignmentID = Guid.NewGuid(); group.CMN_PRO_Product_RefID = product.CMN_PRO_ProductID; group.CMN_PRO_ProductGroup_RefID = groupID; group.Creation_Timestamp = DateTime.Now; group.Tenant_RefID = securityTicket.TenantID; group.Save(Connection, Transaction); } } #endregion #region ORM_CMN_PRO_Product_SalesTaxAssignmnet var salesTaxQuery = new ORM_CMN_PRO_Product_SalesTaxAssignmnet.Query(); salesTaxQuery.Product_RefID = product.CMN_PRO_ProductID; salesTaxQuery.IsDeleted = false; salesTaxQuery.Tenant_RefID = securityTicket.TenantID; var salesTax = ORM_CMN_PRO_Product_SalesTaxAssignmnet.Query.Search(Connection, Transaction, salesTaxQuery).SingleOrDefault(); if (salesTax == null) { salesTax = new ORM_CMN_PRO_Product_SalesTaxAssignmnet(); salesTax.CMN_PRO_Product_SalesTaxAssignmnetID = Guid.NewGuid(); salesTax.Product_RefID = product.CMN_PRO_ProductID; salesTax.Creation_Timestamp = DateTime.Now; salesTax.Tenant_RefID = securityTicket.TenantID; } salesTax.ApplicableSalesTax_RefID = Parameter.VAT_RefID; salesTax.Save(Connection, Transaction); #endregion #region ORM_CMN_PRO_Product_2_ProductCode var product2CodeQuery = new ORM_CMN_PRO_Product_2_ProductCode.Query(); product2CodeQuery.CMN_PRO_Product_RefID = product.CMN_PRO_ProductID; product2CodeQuery.Tenant_RefID = securityTicket.TenantID; product2CodeQuery.IsDeleted = false; var productCodeAssignment = ORM_CMN_PRO_Product_2_ProductCode.Query.Search(Connection, Transaction, product2CodeQuery).SingleOrDefault(); if (productCodeAssignment == null) { #region ORM_CMN_PRO_ProductCode //Get Product Code Type or create it if not exist var eanID = DMProductCodeTypes.Get_ProductCodeType_ByGlobalMatchingID(Connection, Transaction, EProductCodeType.EAN, securityTicket); var productCode = new ORM_CMN_PRO_ProductCode(); productCode.CMN_PRO_ProductCodeID = Guid.NewGuid(); productCode.ProductCode_Type_RefID = eanID; productCode.ProductCode_Value = Parameter.EAN; productCode.Creation_Timestamp = DateTime.Now; productCode.Tenant_RefID = securityTicket.TenantID; productCode.Save(Connection, Transaction); #endregion #region ORM_CMN_PRO_Product_2_ProductCode var product2Code = new ORM_CMN_PRO_Product_2_ProductCode(); product2Code.AssignmentID = Guid.NewGuid(); product2Code.CMN_PRO_Product_RefID = product.CMN_PRO_ProductID; product2Code.CMN_PRO_ProductCode_RefID = productCode.CMN_PRO_ProductCodeID; product2Code.Creation_Timestamp = DateTime.Now; product2Code.Tenant_RefID = securityTicket.TenantID; product2Code.Save(Connection, Transaction); #endregion } else { #region ORM_CMN_PRO_ProductCode var productCode = new ORM_CMN_PRO_ProductCode(); productCode.Load(Connection, Transaction, productCodeAssignment.CMN_PRO_ProductCode_RefID); productCode.ProductCode_Value = Parameter.EAN; productCode.Save(Connection, Transaction); #endregion } #endregion #region ORM_CMN_PRO_PackageInfo var packageInfo = new ORM_CMN_PRO_PAC_PackageInfo(); packageInfo.Load(Connection, Transaction, product.PackageInfo_RefID); if (packageInfo.CMN_PRO_PAC_PackageInfoID == Guid.Empty) { packageInfo = new ORM_CMN_PRO_PAC_PackageInfo(); packageInfo.CMN_PRO_PAC_PackageInfoID = product.PackageInfo_RefID; packageInfo.Creation_Timestamp = DateTime.Now; packageInfo.Tenant_RefID = securityTicket.TenantID; } packageInfo.PackageContent_Amount = PackageAmountUtils.GetPackageAmount(Parameter.UnitAmount); packageInfo.PackageContent_DisplayLabel = Parameter.UnitAmount; packageInfo.PackageContent_MeasuredInUnit_RefID = Parameter.Unit_RefID; packageInfo.Save(Connection, Transaction); #endregion #region Producer var producer = new ORM_CMN_BPT_BusinessParticipant(); producer.Load(Connection, Transaction, product.ProducingBusinessParticipant_RefID); if (producer.CMN_BPT_BusinessParticipantID == Guid.Empty) { producer.CMN_BPT_BusinessParticipantID = Guid.NewGuid(); producer.IsCompany = true; producer.Tenant_RefID = securityTicket.TenantID; producer.Creation_Timestamp = DateTime.Now; product.ProducingBusinessParticipant_RefID = producer.CMN_BPT_BusinessParticipantID; product.Save(Connection, Transaction); } producer.DisplayName = Parameter.Producer; producer.Save(Connection, Transaction); #endregion returnValue.Result = product.CMN_PRO_ProductID; return(returnValue); #endregion UserCode }