protected static FR_L5SO_GRIfPID_1455_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5SO_GRIfPID_1455 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
//Leave UserCode region to enable user code saving
#region UserCode
var returnValue = new FR_L5SO_GRIfPID_1455_Array();
//Put your code here
List <L5SO_GRIfPID_1455> retVal = new List <L5SO_GRIfPID_1455>();
var allReservation = cls_Get_Reservations_for_ProductID.Invoke(Connection, Transaction, new P_L5SO_GRfPID_1516 {
ProductID = Parameter.ProductID
}, securityTicket).Result;
P_L3WH_GASCQfPL_1239 shelfContentQuantitiesParam = new P_L3WH_GASCQfPL_1239();
shelfContentQuantitiesParam.ProductIDList = new Guid[1];
shelfContentQuantitiesParam.ProductIDList[0] = Parameter.ProductID;
var shelfContentQuantities = cls_Get_All_ShelfContent_Quantities_for_ProductListID.Invoke(Connection, Transaction, shelfContentQuantitiesParam, securityTicket).Result;
foreach (var reservation in allReservation)
{
L5SO_GRIfPID_1455 retItem = new L5SO_GRIfPID_1455();
retItem.CMN_BPT_BusinessParticipantID = reservation.CMN_BPT_BusinessParticipantID;
retItem.CompanyName_Line1 = reservation.CompanyName_Line1;
retItem.LOG_SHP_Shipment_HeaderID = reservation.LOG_SHP_Shipment_HeaderID;
retItem.LOG_SHP_Shipment_PositionID = reservation.LOG_SHP_Shipment_PositionID;
retItem.PositionQuantity = shelfContentQuantities.Select(x => x.Sum_Quantity_Current).FirstOrDefault();
retItem.ReservedQuantity = shelfContentQuantities.Select(x => x.Sum_R_ReservedQuantity).FirstOrDefault();
retItem.ShipmentHeader_Number = reservation.ShipmentHeader_Number;
retVal.Add(retItem);
}
returnValue.Result = retVal.ToArray();
return(returnValue);
#endregion UserCode
}
protected static FR_L5SO_GSPwPaSfSH_1141_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5SO_GSPwPaSfSH_1141 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
#region UserCode
var returnValue = new FR_L5SO_GSPwPaSfSH_1141_Array();
#region Get Shipment Positions
P_L2SH_GSPfToSH_1334 positionsGetParam = new P_L2SH_GSPfToSH_1334();
positionsGetParam.ShipmentHeaderID = Parameter.ShippmentHeaderID;
var shipmentPositions = cls_Get_ShipmentPositions_for_Tenant_or_ShipmentHeaderID.Invoke(Connection, Transaction, positionsGetParam, securityTicket).Result.ToList();
#endregion
if (shipmentPositions.Count() == 0)
{
returnValue.Result = new L5SO_GSPwPaSfSH_1141[0];
return(returnValue);
}
#region Get Articles For ShipmentPositions
P_L3AR_GAfAL_0942 articlesGetParam = new P_L3AR_GAfAL_0942();
articlesGetParam.ProductID_List = shipmentPositions.Select(i => i.CMN_PRO_Product_RefID).Distinct().ToArray();
var articles = cls_Get_Articles_for_ArticleList.Invoke(Connection, Transaction, articlesGetParam, securityTicket).Result;
#endregion
#region Get Quantities on Shelf contents per product
var articleIDs = shipmentPositions.Select(x => x.CMN_PRO_Product_RefID).Distinct();
var qntsPerProduct = new L3WH_GASCQfPL_1239[] { };
if (articleIDs.Count() > 0)
{
var qntParam = new P_L3WH_GASCQfPL_1239 {
ProductIDList = articleIDs.ToArray()
};
qntsPerProduct = cls_Get_All_ShelfContent_Quantities_for_ProductListID.Invoke(Connection, Transaction, qntParam, securityTicket).Result;
}
#endregion
#region Get ABDAPrices
var abdaPricesParam = new P_L2PR_GPVfSC_1424()
{
SubscribedCatalogITL = EnumUtils.GetEnumDescription(EPublicCatalogs.ABDA)
};
var abdaPrices = cls_Get_PriceValues_for_SubscribedCatalogITL.Invoke(Connection, Transaction, abdaPricesParam, securityTicket).Result;
#endregion
#region cls_Get_AllGeneralAverageProcurementPrices_for_TenantID
var avgProcPrice = cls_Get_AllGeneralAverageProcurementPrices_for_TenantID.Invoke(Connection, Transaction, securityTicket).Result;
#endregion
List <L5SO_GSPwPaSfSH_1141> listOfPositionsWithPrices = new List <L5SO_GSPwPaSfSH_1141>();
foreach (var position in shipmentPositions)
{
var quantity = qntsPerProduct.Where(i => i.Product_RefID == position.CMN_PRO_Product_RefID).SingleOrDefault();
if (quantity == null)
{
quantity = new L3WH_GASCQfPL_1239()
{
Product_RefID = position.CMN_PRO_Product_RefID,
Sum_Quantity_Current = 0,
Sum_R_ReservedQuantity = 0,
Sum_R_FreeQuantity = 0
};
}
var tempPositionWithPrice = new L5SO_GSPwPaSfSH_1141();
tempPositionWithPrice.ShipmentPositionID = position.LOG_SHP_Shipment_PositionID;
var reservationSum = ORM_LOG_RSV_Reservation.Query.Search(Connection, Transaction, new ORM_LOG_RSV_Reservation.Query()
{
LOG_SHP_Shipment_Position_RefID = position.LOG_SHP_Shipment_PositionID,
IsDeleted = false,
IsReservationExecuted = false
}).Sum(x => x.ReservedQuantity);
var correspodingCustomerOrderPosition = new ORM_ORD_CUO_CustomerOrder_Position();
var positionArticle = articles.Where(ar => ar.CMN_PRO_ProductID == position.CMN_PRO_Product_RefID).Single();
tempPositionWithPrice.Product_Number = positionArticle.Product_Number;
tempPositionWithPrice.Product_Name = positionArticle.Product_Name;
tempPositionWithPrice.UnitAmount = positionArticle.UnitAmount;
tempPositionWithPrice.UnitIsoCode = positionArticle.UnitIsoCode;
tempPositionWithPrice.DossageFormName = positionArticle.DossageFormName;
tempPositionWithPrice.Producer = positionArticle.ProducerName;
tempPositionWithPrice.CMN_PRO_ProductID = positionArticle.CMN_PRO_ProductID;
tempPositionWithPrice.ReservedQuantity = reservationSum;
tempPositionWithPrice.QuantityToShip = position.QuantityToShip;
tempPositionWithPrice.QuantityInStock = quantity.Sum_Quantity_Current;
tempPositionWithPrice.QuantityAvailable = quantity.Sum_R_FreeQuantity;
tempPositionWithPrice.ABDAPrice = abdaPrices.Where(i => i.CMN_PRO_Product_RefID == position.CMN_PRO_Product_RefID).Select(j => j.PriceAmount).SingleOrDefault();
tempPositionWithPrice.SalesPrice = position.ShipmentPosition_ValueWithoutTax;
tempPositionWithPrice.AverageProcurementPrice = avgProcPrice.Where(i => i.Product_RefID == position.CMN_PRO_Product_RefID).Select(j => j.PriceValue_Amount).SingleOrDefault();
tempPositionWithPrice.ShipmentPosition_PricePerUnitValueWithoutTax = position.ShipmentPosition_PricePerUnitValueWithoutTax;
#region Product Replacement Allowed
var assignmentToCustomerOrderPositionQuery = new ORM_ORD_CUO_CustomerOrder_Position_2_ShipmentPosition.Query();
assignmentToCustomerOrderPositionQuery.LOG_SHP_Shipment_Position_RefID = position.LOG_SHP_Shipment_PositionID;
assignmentToCustomerOrderPositionQuery.Tenant_RefID = securityTicket.TenantID;
assignmentToCustomerOrderPositionQuery.IsDeleted = false;
var foundAssignment = ORM_ORD_CUO_CustomerOrder_Position_2_ShipmentPosition.Query.
Search(Connection, Transaction, assignmentToCustomerOrderPositionQuery).SingleOrDefault();
if (foundAssignment != null)
{
correspodingCustomerOrderPosition.Load(Connection, Transaction, foundAssignment.ORD_CUO_CustomerOrder_Position_RefID);
tempPositionWithPrice.IsProductReplacementAllowed = correspodingCustomerOrderPosition.IsProductReplacementAllowed;
}
else
{
tempPositionWithPrice.IsProductReplacementAllowed = true;
}
#endregion
listOfPositionsWithPrices.Add(tempPositionWithPrice);
}
returnValue.Result = listOfPositionsWithPrices.ToArray();
return(returnValue);
#endregion UserCode
}
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
}
