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
}
protected static FR_L5AR_GAADfAL_1426_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5AR_GAADfAL_1426 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
#region UserCode
var returnValue = new FR_L5AR_GAADfAL_1426_Array();
if (Parameter.ProductITLList != null && Parameter.ProductITLList.Length > 0)
{
P_L3AR_GAIDfAITLs_1641 articleParam = new P_L3AR_GAIDfAITLs_1641();
articleParam.ProductITLList = Parameter.ProductITLList;
var articles = cls_Get_ArticleID_for_ArticleITLs.Invoke(Connection, Transaction, articleParam, securityTicket).Result;
Guid[] productIDList = articles.Select(x => x.CMN_PRO_ProductID).ToArray();
#region Get Msr
var msrForProducts = cls_Get_MSR_for_ProductIDList.Invoke(Connection, Transaction, new P_L3AS_GSMRfPL_1508 {
ProductIDList = productIDList
}, securityTicket).Result;
#endregion
#region Get storage data for ariticle list
List <L5AR_GAADfAL_1426> returnValuesList = new List <L5AR_GAADfAL_1426>();
L3WH_GASfA_1924[] storages = new L3WH_GASfA_1924[] { };
Guid[] articlesWithExpectedDelivery = new Guid[] { };
if (productIDList != null && productIDList.Count() > 0)
{
var storageParam = new P_L3WH_GASfA_1924();
storageParam.ArticleID_List = productIDList;
storages = cls_Get_ArticleStorages_for_ArticleIDList.Invoke(Connection, Transaction, storageParam, securityTicket).Result;
var expectedDeliveryParam = new P_L3AR_GIHEDfPL_0911 {
ProductIDList = productIDList
};
articlesWithExpectedDelivery = cls_Get_If_Has_Expected_Delivery_for_ProductIDList.Invoke(
Connection, Transaction, expectedDeliveryParam, securityTicket).Result.Select(x => x.ProductID).ToArray();
}
foreach (var item in productIDList)
{
var storage = storages.FirstOrDefault(x => x.ArticleID == item);
#region get supplier data
string supplierName = String.Empty;
Guid supplierID = Guid.Empty;
string storagePlace = String.Empty;
if (storage != null)
{
storagePlace = storage.WarehouseCode + storage.AreaCode + storage.RackCode + storage.ShelfCode;
P_L3SP_GDSfS_1347 supplierParam = new P_L3SP_GDSfS_1347();
supplierParam.AreaID = storage.AreaID;
supplierParam.RackID = storage.RackID;
supplierParam.WarehouseID = storage.WarehouseID;
var supplier = cls_Get_Default_Supplier_from_Storage.Invoke(Connection, Transaction, supplierParam, securityTicket).Result;
supplierName = supplier.SupplierName;
supplierID = supplier.SupplierID;
}
#endregion
#region stock quantities
P_L3AR_GSQfP_1220 stockParam = new P_L3AR_GSQfP_1220();
stockParam.ProductID = item;
var stockQuantities = cls_Get_StockQuantities_for_ProductID.Invoke(Connection, Transaction, stockParam, securityTicket).Result;
#endregion
var productMSR = msrForProducts.SingleOrDefault(x => x.ProductID == item);
#region form output parameter
L5AR_GAADfAL_1426 result = new L5AR_GAADfAL_1426();
result.ProductID = item;
result.SupplierID = supplierID;
result.SupplierName = supplierName;
result.StockQuantities = stockQuantities;
result.StoragePlace = storagePlace;
result.ProductITL = articles.SingleOrDefault(x => x.CMN_PRO_ProductID == item).ProductITL;
var expDel = articlesWithExpectedDelivery.SingleOrDefault(x => x == item);
result.HasExpectedDelivery = (expDel == Guid.Empty) ? false : true;
result.MSR = (productMSR != null) ? productMSR.MSR : (double)0.0;
returnValuesList.Add(result);
#endregion
}
#endregion
returnValue.Result = returnValuesList.ToArray();
}
return(returnValue);
#endregion UserCode
}
