protected static FR_L3PR_GAwSPfAL_1021_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L3PR_GAwSPfAL_1021 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
#region UserCode
var returnValue = new FR_L3PR_GAwSPfAL_1021_Array();
//Put your code here
List <Guid> productList = new List <Guid>();
var paramArticles = new CL3_Articles.Atomic.Retrieval.P_L3AR_GAfAL_0942
{
ProductID_List = Parameter.ProductListParameter.ProductID_List
};
List <L3AR_GAfAL_0942> articles = cls_Get_Articles_for_ArticleList.Invoke(Connection, Transaction, paramArticles, securityTicket).Result.ToList();
foreach (var item in articles)
{
productList.Add(item.CMN_PRO_ProductID);
}
List <L3PR_GAwSPfAL_1021> results = new List <L3PR_GAwSPfAL_1021>();
if (productList.Count > 0)
{
var paramPrices = new CL3_Price.Complex.Retrieval.P_L3PR_GSPfPIL_1645
{
ProductIDList = productList.ToArray()
};
var prices = CL3_Price.Complex.Retrieval.cls_Get_StandardPrices_for_ProductIDList.Invoke(Connection, Transaction, paramPrices, securityTicket).Result.ToList();
foreach (var articl in articles)
{
var articlesWithPrices = new L3PR_GAwSPfAL_1021();
articlesWithPrices.Article = articl;
articlesWithPrices.Prices = prices.Where(x => x.ProductID == articl.CMN_PRO_ProductID).Single();
results.Add(articlesWithPrices);
}
}
returnValue.Result = results.ToArray();
return(returnValue);
#endregion UserCode
}
protected static FR_L5SO_GASPwABIfPC_1135_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5SO_GASPwABIfPC_1135 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
#region UserCode
var returnValue = new FR_L5SO_GASPwABIfPC_1135_Array();
var articlesFromPosition = new List <Guid>();
var paramP = new CL5_APOLogistic_ShippingOrder.Atomic.Retrieval.P_L5SO_GASPfPC_1725
{
ShippmentHeaderID = Parameter.ShippmentHeaderID
};
var positions = cls_Get_All_ShippmentPositions_for_PickingControl.Invoke(Connection, Transaction, paramP, securityTicket).Result.ToList();
foreach (var item in positions)
{
articlesFromPosition.Add(item.CMN_PRO_Product_RefID);
}
#region Get Article
var paramA = new CL3_Articles.Atomic.Retrieval.P_L3AR_GAfAL_0942
{
ProductID_List = articlesFromPosition.Distinct().ToArray()
};
var articles = new List <L3AR_GAfAL_0942>();
if (paramA.ProductID_List.Length > 0)
{
articles = CL3_Articles.Atomic.Retrieval.cls_Get_Articles_for_ArticleList.Invoke(Connection, Transaction, paramA, securityTicket).Result.ToList();
}
#endregion
var results = new List <L5SO_GASPwABIfPC_1135>();
foreach (var pos in positions)
{
var positionArticles = new L5SO_GASPwABIfPC_1135();
positionArticles.Position = pos;
positionArticles.Articles = articles.Where(ar => ar.CMN_PRO_ProductID == pos.CMN_PRO_Product_RefID).Single();
results.Add(positionArticles);
}
returnValue.Result = results.ToArray();
return(returnValue);
#endregion UserCode
}
protected static FR_L3AR_GAfAL_0942_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L3AR_GAfAL_0942 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
var returnStatus = new FR_L3AR_GAfAL_0942_Array();
DbCommand command = Connection.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction;
var commandLocation = "CL3_Articles.Atomic.Retrieval.SQL.cls_Get_Articles_for_ArticleList.sql";
command.CommandText = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(commandLocation)).ReadToEnd();
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ticket", securityTicket);
command.CommandTimeout = QueryTimeout;
command.CommandText = System.Text.RegularExpressions.Regex.Replace(command.CommandText, "=[ \t]*@ProductID_List", " IN $$ProductID_List$$");
CSV2Core_MySQL.Support.DBSQLSupport.AppendINStatement(command, "$ProductID_List$", Parameter.ProductID_List);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ProducingBusinessParticipant", Parameter.ProducingBusinessParticipant);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ProductGroupID", Parameter.ProductGroupID);
List <L3AR_GAfAL_0942_raw> results = new List <L3AR_GAfAL_0942_raw>();
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
try
{
reader.SetOrdinals(new string[] { "Product_Name_DictID", "Product_Number", "ProductGroup", "CMN_PRO_ProductID", "PackageInfo_RefID", "ProductType_Name_DictID", "Product_Description_DictID", "Abbreviation_DictID", "Label_DictID", "IsPlaceholderArticle", "ProductITL", "UnitAmount", "UnitAmount_DisplayLabel", "UnitIsoCode", "DossageFormName", "ProducerName", "ProducerId", "IsStorage_BatchNumberMandatory", "IsStorage_ExpiryDateMandatory", "ACC_TAX_TaxeID", "EconomicRegion_RefID", "TaxRate", "TaxName_DictID" });
while (reader.Read())
{
L3AR_GAfAL_0942_raw resultItem = new L3AR_GAfAL_0942_raw();
//0:Parameter Product_Name of type Dict
resultItem.Product_Name = reader.GetDictionary(0);
resultItem.Product_Name.SourceTable = "cmn_pro_products";
loader.Append(resultItem.Product_Name);
//1:Parameter Product_Number of type String
resultItem.Product_Number = reader.GetString(1);
//2:Parameter ProductGroup of type String
resultItem.ProductGroup = reader.GetString(2);
//3:Parameter CMN_PRO_ProductID of type Guid
resultItem.CMN_PRO_ProductID = reader.GetGuid(3);
//4:Parameter PackageInfo_RefID of type Guid
resultItem.PackageInfo_RefID = reader.GetGuid(4);
//5:Parameter ProductType_Name of type Dict
resultItem.ProductType_Name = reader.GetDictionary(5);
resultItem.ProductType_Name.SourceTable = "cmn_pro_product_types";
loader.Append(resultItem.ProductType_Name);
//6:Parameter Product_Description of type Dict
resultItem.Product_Description = reader.GetDictionary(6);
resultItem.Product_Description.SourceTable = "cmn_pro_products";
loader.Append(resultItem.Product_Description);
//7:Parameter Abbreviation of type Dict
resultItem.Abbreviation = reader.GetDictionary(7);
resultItem.Abbreviation.SourceTable = "cmn_units";
loader.Append(resultItem.Abbreviation);
//8:Parameter Label of type Dict
resultItem.Label = reader.GetDictionary(8);
resultItem.Label.SourceTable = "cmn_units";
loader.Append(resultItem.Label);
//9:Parameter IsPlaceholderArticle of type bool
resultItem.IsPlaceholderArticle = reader.GetBoolean(9);
//10:Parameter ProductITL of type String
resultItem.ProductITL = reader.GetString(10);
//11:Parameter UnitAmount of type double
resultItem.UnitAmount = reader.GetDouble(11);
//12:Parameter UnitAmount_DisplayLabel of type String
resultItem.UnitAmount_DisplayLabel = reader.GetString(12);
//13:Parameter UnitIsoCode of type String
resultItem.UnitIsoCode = reader.GetString(13);
//14:Parameter DossageFormName of type string
resultItem.DossageFormName = reader.GetString(14);
//15:Parameter ProducerName of type string
resultItem.ProducerName = reader.GetString(15);
//16:Parameter ProducerId of type Guid
resultItem.ProducerId = reader.GetGuid(16);
//17:Parameter IsStorage_BatchNumberMandatory of type bool
resultItem.IsStorage_BatchNumberMandatory = reader.GetBoolean(17);
//18:Parameter IsStorage_ExpiryDateMandatory of type bool
resultItem.IsStorage_ExpiryDateMandatory = reader.GetBoolean(18);
//19:Parameter ACC_TAX_TaxeID of type Guid
resultItem.ACC_TAX_TaxeID = reader.GetGuid(19);
//20:Parameter EconomicRegion_RefID of type Guid
resultItem.EconomicRegion_RefID = reader.GetGuid(20);
//21:Parameter TaxRate of type double
resultItem.TaxRate = reader.GetDouble(21);
//22:Parameter TaxName_DictID of type Dict
resultItem.TaxName_DictID = reader.GetDictionary(22);
resultItem.TaxName_DictID.SourceTable = "acc_tax_taxes";
loader.Append(resultItem.TaxName_DictID);
results.Add(resultItem);
}
}
catch (Exception ex)
{
reader.Close();
throw new Exception("Exception occured durng data retrieval in method cls_Get_Articles_for_ArticleList", ex);
}
reader.Close();
//Load all the dictionaries from the datatables
loader.Load();
returnStatus.Result = L3AR_GAfAL_0942_raw.Convert(results).ToArray();
return(returnStatus);
}
///<summary>
/// Method Invocation of wrapper classes
///<summary>
protected static FR_L3AR_GAfAL_0942_Array Invoke(DbConnection Connection, DbTransaction Transaction, string ConnectionString, P_L3AR_GAfAL_0942 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
bool cleanupConnection = Connection == null;
bool cleanupTransaction = Transaction == null;
FR_L3AR_GAfAL_0942_Array functionReturn = new FR_L3AR_GAfAL_0942_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_Articles_for_ArticleList", ex);
}
return(functionReturn);
}
///<summary>
/// Invokes the method for the given Connection, and Transaction, leaving them open/not commited if no exceptions occured
///<summary>
public static FR_L3AR_GAfAL_0942_Array Invoke(DbConnection Connection, DbTransaction Transaction, P_L3AR_GAfAL_0942 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
return(Invoke(Connection, Transaction, null, Parameter, securityTicket));
}
///<summary>
/// Opens the connection/transaction for the given connectionString, and closes them when complete
///<summary>
public static FR_L3AR_GAfAL_0942_Array Invoke(string ConnectionString, P_L3AR_GAfAL_0942 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
return(Invoke(null, null, ConnectionString, 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
}
