protected static FR_L5AR_FPfDS_1324_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5AR_FPfDS_1324 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { #region UserCode var returnValue = new FR_L5AR_FPfDS_1324_Array(); /* Explanation: * There are three levels of priority to retrieve a supplier for a product: * 1. A supplier is assigned to a product in CMN_PRO_Product_Suppliers with CMN_PRO_Product_Suppliers.SupplierPriority = 1 * 2. A product is assigned to a LOG_WRH_QuantityLevels -> the supplier is retrieved by the default supplier of the corresponding storage level e.g. LOG_WRH_Rack_DefaultSuppliers * 3. A product has neither 1. or 2. In that case retrieve the LOG_WRH_Area_DefaultSuppliers from the area with LOG_WRH_Areas.IsDefaultIntakeArea * */ var result = new List <L5AR_FPfDS_1324>(); var includeProducts = new List <Guid>(); var excludeProducts = new List <Guid>(); #region The first priority var productsSuppliersWithHighestPriority = ORM_CMN_PRO_Product_Supplier.Query.Search(Connection, Transaction, new ORM_CMN_PRO_Product_Supplier.Query() { SupplierPriority = 1, Tenant_RefID = securityTicket.TenantID, IsDeleted = false }); if (Parameter.SupplierID != null) { includeProducts = productsSuppliersWithHighestPriority.Where(i => i.CMN_BPT_Supplier_RefID == Parameter.SupplierID).Select(i => i.CMN_PRO_Product_RefID).Intersect(Parameter.ProductIDList).ToList(); excludeProducts = productsSuppliersWithHighestPriority.Where(i => i.CMN_BPT_Supplier_RefID != Parameter.SupplierID).Select(i => i.CMN_PRO_Product_RefID).Intersect(Parameter.ProductIDList).ToList(); } else { includeProducts = productsSuppliersWithHighestPriority.Select(i => i.CMN_PRO_Product_RefID).Intersect(Parameter.ProductIDList).ToList(); } result = productsSuppliersWithHighestPriority.Where(i => includeProducts.Contains(i.CMN_PRO_Product_RefID)).Select(i => new L5AR_FPfDS_1324() { ProductID = i.CMN_PRO_Product_RefID, SupplierID = i.CMN_BPT_Supplier_RefID, SupplierName = String.Empty, PriorityLevel = 1 }).ToList(); //exclude already procesed elements Parameter.ProductIDList = Parameter.ProductIDList.Except(excludeProducts).ToArray(); Parameter.ProductIDList = Parameter.ProductIDList.Except(includeProducts).ToArray(); #endregion #region The second priority var param2 = new P_L5AR_FPfDSoQL_1410() { ProductIDList = Parameter.ProductIDList, }; var defaultSuppliersOfQuantityLevels = cls_Filter_ProductIDs_for_DefaultSupplierOfQuantityLevels.Invoke(Connection, Transaction, param2, securityTicket).Result; if (defaultSuppliersOfQuantityLevels != null) { if (Parameter.SupplierID != null) { includeProducts = defaultSuppliersOfQuantityLevels.Where(i => i.CMN_BPT_Supplier_RefID == Parameter.SupplierID).Select(i => i.Product_RefID).Intersect(Parameter.ProductIDList).ToList(); excludeProducts = defaultSuppliersOfQuantityLevels.Where(i => i.CMN_BPT_Supplier_RefID != Parameter.SupplierID).Select(i => i.Product_RefID).Intersect(Parameter.ProductIDList).ToList(); } else { includeProducts = defaultSuppliersOfQuantityLevels.Select(i => i.Product_RefID).Intersect(Parameter.ProductIDList).ToList(); } result.AddRange(defaultSuppliersOfQuantityLevels.Where(i => includeProducts.Contains(i.Product_RefID)).Select(i => new L5AR_FPfDS_1324() { ProductID = i.Product_RefID, SupplierID = i.CMN_BPT_Supplier_RefID, SupplierName = String.Empty, PriorityLevel = 2 })); //exclude already procesed elements Parameter.ProductIDList = Parameter.ProductIDList.Except(excludeProducts).ToArray(); Parameter.ProductIDList = Parameter.ProductIDList.Except(includeProducts).ToArray(); } #endregion #region The third priority var defaultIntakeArea = ORM_LOG_WRH_Area.Query.Search(Connection, Transaction, new ORM_LOG_WRH_Area.Query() { IsDefaultIntakeArea = true, Tenant_RefID = securityTicket.TenantID, IsDeleted = false }).SingleOrDefault(); if (defaultIntakeArea != null) { var defaultSupplierForIntakeArea = ORM_LOG_WRH_Area_DefaultSupplier.Query.Search(Connection, Transaction, new ORM_LOG_WRH_Area_DefaultSupplier.Query() { Area_RefID = defaultIntakeArea.LOG_WRH_AreaID, IsDeleted = false, CMN_BPT_Supplier_RefID = Parameter.SupplierID }).SingleOrDefault(); if (defaultSupplierForIntakeArea != null) { includeProducts.AddRange(Parameter.ProductIDList); result.AddRange(Parameter.ProductIDList.Select(i => new L5AR_FPfDS_1324() { ProductID = i, SupplierID = defaultSupplierForIntakeArea.CMN_BPT_Supplier_RefID, SupplierName = String.Empty, PriorityLevel = 3 })); } } #endregion var suppliers = cls_Get_SuppliersDisplayNames_with_ID_for_TenantID.Invoke(Connection, Transaction, securityTicket).Result; foreach (var item in result) { item.SupplierName = suppliers.Where(i => i.CMN_BPT_SupplierID == item.SupplierID).Select(i => i.DisplayName).SingleOrDefault(); } returnValue.Result = result.ToArray(); return(returnValue); #endregion UserCode }
///<summary> /// Method Invocation of wrapper classes ///<summary> protected static FR_L5AR_FPfDS_1324_Array Invoke(DbConnection Connection, DbTransaction Transaction, string ConnectionString, P_L5AR_FPfDS_1324 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { bool cleanupConnection = Connection == null; bool cleanupTransaction = Transaction == null; FR_L5AR_FPfDS_1324_Array functionReturn = new FR_L5AR_FPfDS_1324_Array(); try { if (cleanupConnection == true) { Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString); Connection.Open(); } if (cleanupTransaction == true) { Transaction = Connection.BeginTransaction(); } functionReturn = Execute(Connection, Transaction, Parameter, securityTicket); #region Cleanup Connection/Transaction //Commit the transaction if (cleanupTransaction == true) { Transaction.Commit(); } //Close the connection if (cleanupConnection == true) { Connection.Close(); } #endregion } catch (Exception ex) { try { if (cleanupTransaction == true && Transaction != null) { Transaction.Rollback(); } } catch { } try { if (cleanupConnection == true && Connection != null) { Connection.Close(); } } catch { } throw new Exception("Exception occured in method cls_Filter_ProductIDs_for_DefaultSupplierID", ex); } return(functionReturn); }
///<summary> /// Opens the connection/transaction for the given connectionString, and closes them when complete ///<summary> public static FR_L5AR_FPfDS_1324_Array Invoke(string ConnectionString, P_L5AR_FPfDS_1324 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { return(Invoke(null, null, ConnectionString, Parameter, securityTicket)); }
///<summary> /// Invokes the method for the given Connection, and Transaction, leaving them open/not commited if no exceptions occured ///<summary> public static FR_L5AR_FPfDS_1324_Array Invoke(DbConnection Connection, DbTransaction Transaction, P_L5AR_FPfDS_1324 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { return(Invoke(Connection, Transaction, null, Parameter, securityTicket)); }
protected static FR_L5AR_GBDfPABTS_1202_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5AR_GBDfPABTS_1202 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { #region UserCode var returnValue = new FR_L5AR_GBDfPABTS_1202_Array(); var productRates = new List <Tuple <Guid, Double> >(); if (Parameter.BaseTimeSpan_EndDate == null || Parameter.BaseTimeSpan_StartDate == null) { #region Get MSR (Monthly sales rate) - If no base time span var msrResult = cls_Get_MSR_for_TenantID.Invoke(Connection, Transaction, securityTicket).Result; productRates = msrResult.Select(i => new Tuple <Guid, Double>(i.ProductID, i.MSR)).ToList(); #endregion } else { #region Get MSRB (Monthly sales rate base) - If there is base time span var msrbParam = new P_L3AS_GMSRfBS_1404() { StartDate = (DateTime)Parameter.BaseTimeSpan_StartDate, EndDate = (DateTime)Parameter.BaseTimeSpan_EndDate }; var msrbResult = cls_Get_MSRB_for_BaseSpan.Invoke(Connection, Transaction, msrbParam, securityTicket).Result; productRates = msrbResult.Select(i => new Tuple <Guid, Double>(i.ProductID, i.MSRB)).ToList(); #endregion } if (productRates == null || productRates.Count() == 0) { returnValue.Result = new L5AR_GBDfPABTS_1202[0]; return(returnValue); } #region Get articles var articleParam = new CL3_Articles.Atomic.Retrieval.P_L3AR_GAfAL_0942 { ProductID_List = productRates.Select(i => i.Item1).ToArray() }; var articles = CL3_Articles.Atomic.Retrieval.cls_Get_Articles_for_ArticleList.Invoke(Connection, Transaction, articleParam, securityTicket).Result; #endregion #region Minimum Quantities //This list needs GroupBy var tenantQuanityLevels = ORM_LOG_WRH_QuantityLevel.Query.Search(Connection, Transaction, new ORM_LOG_WRH_QuantityLevel.Query() { IsDeleted = false, Tenant_RefID = securityTicket.TenantID }); #endregion #region Quantities In ShipmentPositions var quantitiesInShipmentPositions = cls_Get_ProductQuantities_for_UnshippedAndUnfinishedShipments.Invoke(Connection, Transaction, securityTicket).Result; #endregion #region Current ShelContent Quantities var paramShelfContentQuantities = new P_L3WH_GASCQfPL_1239() { ProductIDList = productRates.Select(i => i.Item1).ToArray() }; var currentShelContentQuantities = cls_Get_All_ShelfContent_Quantities_for_ProductListID.Invoke(Connection, Transaction, paramShelfContentQuantities, securityTicket).Result; #endregion #region Quantities In Procurement var activeStatus = ORM_ORD_PRC_ProcurementOrder_Status.Query.Search(Connection, Transaction, new ORM_ORD_PRC_ProcurementOrder_Status.Query() { GlobalPropertyMatchingID = EnumUtils.GetEnumDescription(EProcurementStatus.Active), Tenant_RefID = securityTicket.TenantID, IsDeleted = false }).Single(); var paramQuantitiesInProcurementPositions = new P_L2PO_GPQfPHwS_1553() { CurrentProcurementOrderStatus = activeStatus.ORD_PRC_ProcurementOrder_StatusID }; var quantitiesInProcurementPositions = cls_Get_ProductQuantities_for_ProcurementHeader_with_Status.Invoke(Connection, Transaction, paramQuantitiesInProcurementPositions, securityTicket).Result; #endregion #region Unstored Expected Deliveries var confirmedOrderStatus = ORM_ORD_PRC_ProcurementOrder_Status.Query.Search(Connection, Transaction, new ORM_ORD_PRC_ProcurementOrder_Status.Query() { GlobalPropertyMatchingID = EnumUtils.GetEnumDescription(EProcurementStatus.Confirmed), Tenant_RefID = securityTicket.TenantID, IsDeleted = false }).Single(); var paramQuantitiesInExpectedDeliveries = new P_L2PO_GPQfUED_1637() { OrderedProcurementStatusID = confirmedOrderStatus.ORD_PRC_ProcurementOrder_StatusID, ExpectedDateBefore = DateTime.Now.AddDays(365) //this is hardcoded, because we don't have setup for it right now }; var quantitiesInExpectedDeliveries = cls_Get_ProductQuantities_for_UnstoredExpectedDelivery.Invoke(Connection, Transaction, paramQuantitiesInExpectedDeliveries, securityTicket).Result; #endregion #region Filter if (Parameter.ProducerBPID != null) { articles = articles.Where(x => x.ProducerId == Parameter.ProducerBPID).ToArray(); if (articles == null || articles.Count() == 0) { returnValue.Result = new L5AR_GBDfPABTS_1202[0]; return(returnValue); } } var paramFilterBySupplier = new P_L5AR_FPfDS_1324() { ProductIDList = articles.Select(i => i.CMN_PRO_ProductID).ToArray(), SupplierID = Parameter.SupplierID }; var articlesFilteredBySupplierID = cls_Filter_ProductIDs_for_DefaultSupplierID.Invoke(Connection, Transaction, paramFilterBySupplier, securityTicket).Result; var filteredArticlesIDs = articlesFilteredBySupplierID.Select(i => i.ProductID); articles = articles.Where(x => filteredArticlesIDs.Contains(x.CMN_PRO_ProductID)).ToArray(); #endregion var result = new List <L5AR_GBDfPABTS_1202>(); foreach (var article in articles) { #region Recalculation var quantityParam = new P_L3AR_GSQfP_1220() { ProductID = article.CMN_PRO_ProductID }; var stockQuantities = cls_Get_StockQuantities_for_ProductID.Invoke(Connection, Transaction, quantityParam, securityTicket).Result; var defaultSupplier = articlesFilteredBySupplierID.Where(i => i.ProductID == article.CMN_PRO_ProductID).Single(); var msrOrMsrb = productRates.Where(i => i.Item1 == article.CMN_PRO_ProductID).Select(i => i.Item2).SingleOrDefault(); var minimumQuantity = tenantQuanityLevels.Where(i => i.Product_RefID == article.CMN_PRO_ProductID && i.Quantity_Minimum != 0).Sum(i => i.Quantity_Minimum); minimumQuantity += tenantQuanityLevels.Where(i => i.Product_RefID == article.CMN_PRO_ProductID && i.Quantity_Minimum == 0).Sum(i => i.Quantity_RecommendedMinimumCalculation); var qunatityInShipmentPositions = quantitiesInShipmentPositions.Where(i => i.CMN_PRO_Product_RefID == article.CMN_PRO_ProductID).Select(i => i.QuantityToShip).SingleOrDefault(); var currentShelContentQuantity = currentShelContentQuantities.Where(i => i.Product_RefID == article.CMN_PRO_ProductID).Select(i => i.Sum_Quantity_Current).SingleOrDefault(); var quantityInProcurementPositions = quantitiesInProcurementPositions.Where(i => i.CMN_PRO_Product_RefID == article.CMN_PRO_ProductID).Select(i => i.Quantity).SingleOrDefault(); var quantityInExpectedDeliveries = quantitiesInExpectedDeliveries.Where(i => i.CMN_PRO_Product_RefID == article.CMN_PRO_ProductID).Select(i => i.TotalExpectedQuantity).SingleOrDefault(); #endregion var resultItem = new L5AR_GBDfPABTS_1202(); resultItem.Article = article; resultItem.SupplierName = defaultSupplier.SupplierName; resultItem.SupplierID = defaultSupplier.SupplierID; resultItem.CurrentQuantity = Math.Ceiling(stockQuantities.Quantity_Current); resultItem.MSR_or_MSRB = Math.Ceiling(msrOrMsrb); var baseDemand = (msrOrMsrb / 30) * Parameter.OrderingPeriodInDays + minimumQuantity + qunatityInShipmentPositions - currentShelContentQuantity - quantityInProcurementPositions - quantityInExpectedDeliveries; resultItem.BaseDemand = Math.Ceiling(baseDemand); //this happens when there is more quantities in shelf than we expect to sell for that period if (resultItem.BaseDemand <= 0) { continue; } if ((Parameter.FromAmount == null || Parameter.FromAmount <= resultItem.BaseDemand) && (Parameter.ToAmount == null || resultItem.BaseDemand <= Parameter.ToAmount)) { result.Add(resultItem); } } returnValue.Result = result.ToArray(); return(returnValue); #endregion UserCode }