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 }
///<summary> /// Invokes the method for the given Connection, and Transaction, leaving them open/not commited if no exceptions occured ///<summary> public static FR_L5AR_GBDfPABTS_1202_Array Invoke(DbConnection Connection, DbTransaction Transaction, P_L5AR_GBDfPABTS_1202 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { return(Invoke(Connection, Transaction, null, Parameter, securityTicket)); }
///<summary> /// Method Invocation of wrapper classes ///<summary> protected static FR_L5AR_GBDfPABTS_1202_Array Invoke(DbConnection Connection, DbTransaction Transaction, string ConnectionString, P_L5AR_GBDfPABTS_1202 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { bool cleanupConnection = Connection == null; bool cleanupTransaction = Transaction == null; FR_L5AR_GBDfPABTS_1202_Array functionReturn = new FR_L5AR_GBDfPABTS_1202_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_Get_BaseDemand_for_Period_and_BaseTimeSpan", ex); } return(functionReturn); }
///<summary> /// Opens the connection/transaction for the given connectionString, and closes them when complete ///<summary> public static FR_L5AR_GBDfPABTS_1202_Array Invoke(string ConnectionString, P_L5AR_GBDfPABTS_1202 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { return(Invoke(null, null, ConnectionString, Parameter, securityTicket)); }