public List <DefaultAssignment> GetDefaults(Data.Model model)
{
var versions = model.Versions.Select(v => "'" + v.Name + "'").Aggregate((s, e) => s + "," + e);
var variants = model.Versions.Select(v => "'" + v.Variant + "'").Aggregate((s, e) => s + "," + e);
var hierarchyElements = $"'{model.Name}', {versions}, {variants}";
Debug.WriteLine(hierarchyElements);
using (var connection = new SqlCeConnection(_rulesConnectionString)) {
var defaults = new List <DefaultAssignment>();
connection.Open();
var sql = $"select var, country, cstic, value from ZTVC_MF_S_DEF where char_prod in ({hierarchyElements})";
Debug.WriteLine(sql);
using (var cmd = new SqlCeCommand(sql,
connection)) {
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var variant = reader.GetString(0);
var country = reader.GetString(1);
var cstic = reader.GetString(2);
var value = reader.GetString(3);
var defaultAssignment =
new DefaultAssignment(model.Name, variant, country, new Assignment(cstic, value));
defaults.Add(defaultAssignment);
}
}
return(defaults);
}
}
public Data.Model GetModelByMaterial(string materialNbr)
{
using (var connection = new SqlCeConnection(_categoriesConnectionString)) {
connection.Open();
var model = new Data.Model();
using (var cmd =
new SqlCeCommand(
$"SELECT mr.MODEL, mr.MODEL_RANGE, grp.PRODUCT_GROUP, b.BRAND FROM YTVC_XX_MOD_MRA mr JOIN YTVC_XX_MRA_GRP grp on grp.MODEL_RANGE = mr.MODEL_RANGE JOIN YTVC_XX_GRP_BRD b on b.PRODUCT_GROUP = grp.PRODUCT_GROUP WHERE mr.MATNR = '{materialNbr}'",
connection)) {
var reader = cmd.ExecuteReader();
while (reader.Read())
{
model.Matnr = materialNbr;
model.Name = reader.GetString(0);
model.ModelRange = reader.GetString(1);
model.ProdcutGroup = reader.GetString(2);
model.Brand = reader.GetString(3);
}
}
using (var cmd = new SqlCeCommand($"SELECT VERSION, VAR FROM YTVC_XX_VER_MOD WHERE MODEL = '{model.Name}'",
connection)) {
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var version = reader.GetString(0);
var variant = reader.GetString(1);
model.Versions.Add(new ModelVersion(version, variant));
}
}
return(model);
}
}
public List <Basket> GetBaskets(Data.Model model)
{
var versions = model.Versions.Select(v => "'" + v.Name + "'").Aggregate((s, e) => s + "," + e);
var variants = model.Versions.Select(v => "'" + v.Variant + "'").Aggregate((s, e) => s + "," + e);
var hierarchyElements = $"'{model.Name}', {versions}, {variants}";
Debug.WriteLine(hierarchyElements);
using (var connection = new SqlCeConnection(_rulesBasketsConnectionString)) {
var dic = new Dictionary <string, Basket>();
connection.Open();
using (SqlCeCommand cmd = new SqlCeCommand(
$"select counter, char_prod, char_in, value_in, char_out, value_out, char_if, value_if from ZTVC_MF_S_RULE where char_prod in ({hierarchyElements})",
connection)) {
var reader = cmd.ExecuteReader();
while (reader.Read())
{
string counter = reader.GetString(0);
string char_prod = reader.GetString(1);
string char_in = reader.GetString(2);
string value_in = reader.GetString(3);
string char_out = reader.GetString(4);
string value_out = reader.GetString(5);
string char_if = reader.GetString(6);
string value_if = reader.GetString(7);
if (dic.TryGetValue(counter, out var extractedRule))
{
extractedRule.IncludedElements.Add(new Assignment(char_out, value_out));
}
else
{
var rule = new Basket(counter)
{
Package = new Assignment(char_in, value_in),
ConditionAssignment = new Assignment(char_if, value_if),
HierarchyElement = char_prod,
IncludedElements = new List <Assignment>()
{
new Assignment(char_out, value_out)
}
};
dic.Add(counter, rule);
}
}
}
return(dic.Values.ToList());
}
}
