public ActionResult GetMechanicalMetal(int materialId, int sourceMaterialId, int sourceId, int subgroupId, int groupId, string searchText, int type, int conditionId = 0)
{
if (type == 2)
{
type = 1;
}
if (type == 3)
{
type = 2;
}
IService service = new TotalMateriaService();
IPlusService servicePLUS = new TMPlusService();
string sessionId = System.Web.HttpContext.Current.Session["TotalMateriaSession"].ToString();
ConditionModel conditionData = new ConditionModel();
Condition cond = new ConditionTMMetalsBinder().FillMechanicalConditionData(subgroupId, sourceMaterialId, conditionId, materialContextUow, type, sessionId);
conditionData.ConditionId = cond.ConditionId;
conditionData.ConditionName = cond.ConditionName;
conditionData.Properties = cond.Properties;
conditionData.ProductGroupId = ProductGroup.ProductGroupType.Mechanical;
//TODO: Fill references
conditionData.SelectedReferences = null;
return(Json(ResponseStatus.Success, RenderPartialViewToString("ConditionPropertiesContent", conditionData), JsonRequestBehavior.AllowGet));
}
public ActionResult GetEquivalency(int sourceMaterialId, int subgroupId, int sourceId)
{
string sessionId = System.Web.HttpContext.Current.Session["TotalMateriaSession"].ToString();
EquivalencyModel model = new EquivalencyModel();
if (sourceId == 1)
{
model = MaterialDetailsBinder.BindEquivalencyEMS(sourceMaterialId, materialContextUow, sessionId);
}
if (sourceId == 2)
{
IService service = new TotalMateriaService();
model = service.GetCrossReferenceFromService(sessionId, sourceMaterialId, subgroupId);
}
if (sourceId == 3)
{
TMPlusService plusService = new TMPlusService();
var cr = plusService.GetCrossReference(sourceMaterialId);
IList <CrossReferenceModel> eq = new List <CrossReferenceModel>();
eq = cr.Materials.Select(v => new CrossReferenceModel()
{
MaterialId = v.MaterialId,
CountryStandard = v.CountryStandard,
EquivalenceCategory = (v.Type.ToString() == "OtherSources") ? "Other Sources" : v.Type.ToString(),
MaterialName = v.Name
}).ToList();
model = new EquivalencyModel()
{
Equivalences = eq
};
}
return(Json(ResponseStatus.Success, RenderPartialViewToString("Equivalency", model), JsonRequestBehavior.AllowGet));
}
public ElsevierMaterials.Models.Domain.Material.MaterialBasicInfo GetMaterialInfo(int materialId, int sourceMaterialId, int sourceId, int subgroupId, IMaterialsContextUow context)
{
FullTextSearch fts = context.FullTextSearch.GetMaterialById(materialId);
ElsevierMaterials.Models.Domain.Material.MaterialBasicInfo material = null;
IPlusService servicePLUS = new TMPlusService();
material = servicePLUS.GetMaterialSubgroupPLUSListFromService(System.Web.HttpContext.Current.Session["TotalMateriaSession"].ToString(), materialId, sourceMaterialId).Where(m => m.MaterialId == materialId && m.SubgroupId == subgroupId).Select(m => new ElsevierMaterials.Models.Domain.Material.MaterialBasicInfo {
MaterialId = m.MaterialId, Name = m.Name, SourceId = m.SourceId, SourceMaterialId = m.SourceMaterialId, Standard = m.Standard, SubgroupId = m.SubgroupId, SubgroupName = (m.SourceText == "-" ? "" : m.SourceText.Replace("<br>", " ").Replace("<br >", " ").Replace("<br />", " ").Replace("<br/>", " ") + "; ") + (m.Standard == "-" ? "" : m.Standard.Replace("<br>", " ").Replace("<br >", " ").Replace("<br />", " ").Replace("<br/>", " ") + "; ") + (m.Specification == "-" ? "" : m.Specification.Replace("<br>", " ").Replace("<br >", " ").Replace("<br />", " ").Replace("<br/>", " "))
}).FirstOrDefault();
if (material != null)
{
material.Name = fts.material_designation;
}
return(material);
}
public ActionResult GetProccesing(int sourceMaterialId, int subgroupId, int sourceId)
{
string sessionId = System.Web.HttpContext.Current.Session["TotalMateriaSession"].ToString();
ProcessingModel model = new ProcessingModel();
if (sourceId == 2)
{
IService service = new TotalMateriaService();
model.HeatTreatment = service.GetHeatTreatmentFromService(sessionId, sourceMaterialId, subgroupId);
model.Metallography = service.GetMetallographyPropertiesFromService(sessionId, sourceMaterialId, subgroupId);
model.Machinability = service.GetMachinabilityPropertiesFromService(sessionId, sourceMaterialId);
}
if (sourceId == 3)
{
TMPlusService plusService = new TMPlusService();
model.Manufacturing = _materialDetailsBinder.GetManufacturing(sessionId, sourceMaterialId, subgroupId, materialContextUow, plusService);
}
return(Json(ResponseStatus.Success, RenderPartialViewToString("Processing", model), JsonRequestBehavior.AllowGet));
}
public Condition FillConditionData(int subgroupId, int sourceMaterialId, int sourceId, int groupId, int conditionId, IMaterialsContextUow context)
{
string sessionId = System.Web.HttpContext.Current.Session["TotalMateriaSession"].ToString();
IService service = new TotalMateriaService();
IPlusService servicePLUS = new TMPlusService();
Condition condition = null;
if (groupId == 806)
{
PropertiesContainer mechanical = servicePLUS.GetMechanicalPLUSPropertiesFromService(sessionId, sourceMaterialId, subgroupId);
IList <TabConditionAndData> mechanicalCondition = mechanical.Model.Where(m => m.ConditionId == conditionId).ToList();
condition = new Condition();
condition.Properties = new List <Property>();
foreach (var item in mechanicalCondition)
{
condition.ConditionName = item.Condition;
condition.ConditionId = item.ConditionId;
foreach (var prop in item.DataForCondition)
{
foreach (var row in prop.Rows)
{
string tempString = "";
if (!string.IsNullOrEmpty(row.Item3))
{
if (tempString != "")
{
tempString += "; " + row.Item3;
}
else
{
tempString += row.Item3;
}
}
if (tempString != "")
{
tempString += "; " + row.Item2;
}
else
{
tempString += row.Item2;
}
condition.Properties.Add(new Property()
{
SourcePropertyId = prop.PropertyId, PropertyName = prop.Header.ToLower(), OrigUnit = prop.Unit.Replace("(", "").Replace(")", ""), OrigValue = row.Item1, OrigValueText = tempString
});
}
}
condition.Properties = condition.Properties.OrderBy(s => s.PropertyName).ToList();
int count = 0;
foreach (var prop in condition.Properties)
{
var st = context.EquivalentProperties.AllAsNoTracking.Where(s => s.SourcePropertyId == prop.SourcePropertyId && s.SourceId == 3).FirstOrDefault();
if (st != null)
{
prop.PropertyId = st.PropertyId;
prop.SourcePropertyId = st.SourcePropertyId;
prop.PropertyName = st.Name;
prop.ValueId = count;
count = count + 1;
}
else
{
prop.ValueId = count;
count = count + 1;
}
}
}
}
else
{
PropertiesContainer physical = servicePLUS.GetPhysicalPropertiesPLUSFromService(sessionId, sourceMaterialId);
IList <TabConditionAndData> physicalCondition = physical.Model.Where(m => m.ConditionId == conditionId).ToList();
condition = new Condition();
condition.Properties = new List <Property>();
foreach (var item in physicalCondition)
{
condition.ConditionName = item.Condition;
condition.ConditionId = item.ConditionId;
foreach (var prop in item.DataForCondition)
{
foreach (var row in prop.Rows)
{
string tempString = "";
if (!string.IsNullOrEmpty(row.Item3))
{
if (tempString != "")
{
tempString += "; " + row.Item3;
}
else
{
tempString += row.Item3;
}
}
if (tempString != "")
{
tempString += "; " + row.Item2;
}
else
{
tempString += row.Item2;
}
condition.Properties.Add(new Property()
{
SourcePropertyId = prop.PropertyId, PropertyName = prop.Header.ToLower(), OrigUnit = prop.Unit.Replace("(", "").Replace(")", ""), OrigValue = row.Item1, OrigValueText = tempString
});
}
}
condition.Properties = condition.Properties.OrderBy(s => s.PropertyName).ToList();
int count = 0;
foreach (var prop in condition.Properties)
{
var st = context.EquivalentProperties.AllAsNoTracking.Where(s => s.SourcePropertyId == prop.SourcePropertyId && s.SourceId == 3).FirstOrDefault();
if (st != null)
{
prop.PropertyId = st.PropertyId;
prop.SourcePropertyId = st.SourcePropertyId;
prop.PropertyName = st.Name;
prop.ValueId = count;
count = count + 1;
}
else
{
prop.ValueId = count;
count = count + 1;
}
}
}
}
return(condition);
}
public ICollection <Material> GetMaterialByEquivalence(int equivalentId, FullTextSearch fts, string sessionId)
{
Classification cl = new Classification();
if (fts.material_type != null)
{
cl.ClassificationNames.Add(ClassificationType.Type, fts.material_type);
}
if (fts.material_group != null)
{
cl.ClassificationNames.Add(ClassificationType.Group, fts.material_group);
}
if (fts.material_class != null)
{
cl.ClassificationNames.Add(ClassificationType.Class, fts.material_class);
}
if (fts.material_subClass != null)
{
cl.ClassificationNames.Add(ClassificationType.Subclass, fts.material_subClass);
}
ICollection <Material> materialsELS = DataSet.Where(m => m.MaterialId == equivalentId).Where(m => m.SourceId == 1).ToList();
ICollection <Material> materialsTMMetals = DataSet.Where(m => m.MaterialId == equivalentId).Where(m => m.SourceId == 2).ToList();
ICollection <Material> materialsTMPLUS = DataSet.Where(m => m.MaterialId == equivalentId).Where(m => m.SourceId == 3).ToList();
ICollection <Material> results = new List <Material>();
int eqNumber = 0;
foreach (var item in materialsELS)
{
item.Name = fts.material_designation;
item.Classification = cl;
results.Add(item);
}
IService service = new TotalMateriaService();
IPlusService servicePLUS = new TMPlusService();
ICollection <Material> materialList = new HashSet <Material>();
if (materialsTMMetals.Count > 0)
{
materialList = service.GetMetalsMaterialsSubgroupListFromService(sessionId, materialsTMMetals.Select(m => m.SourceMaterialId).ToList());
foreach (Material itemTM in materialList)
{
itemTM.MaterialId = materialsTMMetals.Where(m => m.SourceMaterialId == itemTM.SourceMaterialId).Select(m => m.MaterialId).FirstOrDefault();
itemTM.Classification = cl;
itemTM.Name = fts.material_designation;
if (eqNumber == 0)
{
eqNumber = itemTM.NumEquivalency;
}
results.Add(itemTM);
}
}
if (materialsTMPLUS.Count > 0)
{
materialList = servicePLUS.GetPLUSMaterialsSubgroupListFromService(sessionId, materialsTMPLUS.Select(m => m.SourceMaterialId).ToList());
foreach (Material itemTM in materialList)
{
itemTM.MaterialId = materialsTMPLUS.Where(m => m.SourceMaterialId == itemTM.SourceMaterialId).Select(m => m.MaterialId).FirstOrDefault();
itemTM.Classification = cl;
itemTM.Name = fts.material_designation;
if (eqNumber == 0)
{
eqNumber = itemTM.NumEquivalency;
}
results.Add(itemTM);
}
}
foreach (var it in results.Where(s => s.SourceId == 1))
{
it.NumEquivalency = eqNumber;
}
return(results);
}
public IEnumerable <int> GetMaterialsByAdvancedSearch(bool withTracking, AdvSearchFiltersAll filters, string sessionId, IQueryable <EquivalentProperty> propIds, IQueryable <EquivalentMaterial> matIds)
{
//string fiedList = "[MaterialID], [PropertyID], [Value], [ValueMin], [ValueMax]";
string fieldList = "[MaterialID], 0 AS [PropertyID], CAST(1.0 as FLOAT) AS [Value], CAST(2.0 AS FLOAT) AS [ValueMin], CAST(3.0 AS FLOAT) AS [ValueMax]";
string fieldListMat = "[ID] AS [MaterialID], 0 AS [PropertyID], CAST(1.0 as FLOAT) AS [Value], CAST(2.0 AS FLOAT) AS [ValueMin], CAST(3.0 AS FLOAT) AS [ValueMax]";
//StringBuilder sb = new StringBuilder();
//sb.Append(";");
int optCounter = 0;
int argCounter = -1;
IList <object> allArgs = new List <object>();
IList <AdvSearchSqlQueryDef> AdvSearchSqlQueryDefs = new List <AdvSearchSqlQueryDef>();
string queryToAppend = "";
foreach (AdvSearchFilters f in filters.AllFilters)
{
// To return the data in Set A that doesn’t overlap with B, use A EXCEPT B.
// To return only the data that overlaps in the two sets, use A INTERSECT B.
// To return the data in Set B that doesn’t overlap with A, use B EXCEPT A.
// To return the data in all three areas without duplicates, use A UNION B.
// To return the data in all three areas, including duplicates, use A UNION ALL B.
// To return the data in the non-overlapping areas of both sets, use (A UNION B) except (A INTERSECT B), or perhaps (A EXCEPT B) UNION (B EXCEPT A)
//
// If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:
// 1. Expressions in parentheses
// 2. The INTERSECT operator
// 3. EXCEPT and UNION evaluated from left to right based on their position in the expression
//
// Limitation: Max number of parameters for T-SQL command is 2100!
AdvSearchSqlQueryDef oneAdvSearchSqlQueryDef = new AdvSearchSqlQueryDef()
{
Filter = f
};
optCounter++;
queryToAppend = "";
IList <object> oneArgs = new List <object>();
switch (f.propertyType)
{
case PropertyType.NotDefined:
break;
case PropertyType.Material:
oneAdvSearchSqlQueryDef.Operator = f.binaryOperators;
queryToAppend = "";
// reset first binary operator to AND
f.binaryOperators = (optCounter == 1 ? BinaryOperators.And : f.binaryOperators);
switch (f.binaryOperators)
{
case BinaryOperators.NotDefined:
break;
case BinaryOperators.And:
//sb.Append(optCounter > 1 ? " INTERSECT " : "");
queryToAppend = PrepareMaterial(f, fieldListMat, ref argCounter, ref allArgs, ref oneArgs);
break;
case BinaryOperators.Or:
//sb.Append(optCounter > 1 ? " UNION " : "");
queryToAppend = PrepareMaterial(f, fieldListMat, ref argCounter, ref allArgs, ref oneArgs);
break;
case BinaryOperators.Not:
//sb.Append(optCounter > 1 ? " EXCEPT " : "");
queryToAppend = PrepareMaterial(f, fieldListMat, ref argCounter, ref allArgs, ref oneArgs);
break;
default:
break;
}
//sb.Append(queryToAppend);
oneAdvSearchSqlQueryDef.Query = queryToAppend;
break;
case PropertyType.Property:
oneAdvSearchSqlQueryDef.Operator = f.binaryOperators;
queryToAppend = "";
// reset first binary operator to AND
f.binaryOperators = (optCounter == 1 ? BinaryOperators.And : f.binaryOperators);
switch (f.binaryOperators)
{
case BinaryOperators.NotDefined:
break;
case BinaryOperators.And:
//sb.Append(optCounter > 1 ? " INTERSECT " : "");
queryToAppend = prepareLogicalOperator(f, fieldList, ref argCounter, ref allArgs, ref oneArgs);
break;
case BinaryOperators.Or:
//sb.Append(optCounter > 1 ? " UNION " : "");
queryToAppend = prepareLogicalOperator(f, fieldList, ref argCounter, ref allArgs, ref oneArgs);
break;
case BinaryOperators.Not:
//sb.Append(optCounter > 1 ? " EXCEPT " : "");
queryToAppend = prepareLogicalOperator(f, fieldList, ref argCounter, ref allArgs, ref oneArgs);
break;
default:
break;
}
//sb.Append(queryToAppend);
oneAdvSearchSqlQueryDef.Query = queryToAppend;
break;
default:
break;
}
oneAdvSearchSqlQueryDef.Args = oneArgs;
AdvSearchSqlQueryDefs.Add(oneAdvSearchSqlQueryDef);
}
// Add source condition
int sourceId = -1;
int databookId = -1;
if (filters.SelectedSource != null && filters.SelectedSource != "0" /* "0" = All */)
{
string[] ids = filters.SelectedSource.Split(',');
sourceId = ids[0] != null?int.Parse(ids[0]) : -1;
databookId = ids[1] != null?int.Parse(ids[1]) : -1;
AdvSearchFilters fSource = new AdvSearchFilters();
AdvSearchSqlQueryDef oneAdvSearchSqlQueryDefSource = new AdvSearchSqlQueryDef();
IList <object> oneArgsSource = new List <object>();
if (databookId == 0 && sourceId != 0 && sourceId != -1)
{
fSource = new AdvSearchFilters()
{
propertyType = PropertyType.Material,
binaryOperators = BinaryOperators.And,
propertyId = sourceId,
propertyName = "SOURCE:"
};
queryToAppend = PrepareMaterial(fSource, fieldListMat, ref argCounter, ref allArgs, ref oneArgsSource);
}
else if (databookId != 0 && databookId != -1 && sourceId != 0 && sourceId != -1)
{
fSource = new AdvSearchFilters()
{
propertyType = PropertyType.Material,
binaryOperators = BinaryOperators.And,
propertyId = databookId,
propertyName = "DATABOOK:"
};
queryToAppend = PrepareMaterial(fSource, fieldListMat, ref argCounter, ref allArgs, ref oneArgsSource);
}
oneAdvSearchSqlQueryDefSource.Filter = fSource;
oneAdvSearchSqlQueryDefSource.Query = queryToAppend;
oneAdvSearchSqlQueryDefSource.Args = oneArgsSource;
oneAdvSearchSqlQueryDefSource.Operator = fSource.binaryOperators;
AdvSearchSqlQueryDefs.Add(oneAdvSearchSqlQueryDefSource);
}
// (with TM data)
int nextStartIndex = 0;
int queryCounter = 0;
IList <EquivalentMaterial> matIdsM = matIds.Where(m => m.SourceId == 2).ToList();
IList <EquivalentMaterial> matIdsP = matIds.Where(m => m.SourceId == 3).ToList();
IList <EquivalentProperty> propIdsM = propIds.Where(p => p.SourceId == 2).ToList();
IList <EquivalentProperty> propIdsP = propIds.Where(p => p.SourceId == 3).ToList();
IService service = new TotalMateriaService();
IPlusService servicePLUS = new TMPlusService();
IEnumerable <int> dsAll = null;
foreach (AdvSearchSqlQueryDef qDef in AdvSearchSqlQueryDefs)
{
queryCounter++;
// Prepare arguments for SQL Query (renumerate them)
for (int i = 0; i < qDef.Args.Count(); i++)
{
qDef.Query = qDef.Query.Replace("{" + (nextStartIndex).ToString() + "}", "{" + i.ToString() + "}");
nextStartIndex++;
}
/////////
string cmdText = qDef.Query;
object[] arg = qDef.Args.ToArray();
IEnumerable <int> ds = DataSet.SqlQuery(cmdText, arg).Select(m => m.MaterialID);
// Call Web service for TM property data and make UNION with ds from the EMS dabase
if ((databookId == 0 || databookId == -1) && filters.IsChemical == false)
{
if (qDef.Filter.propertyType == PropertyType.Property)
{
// SELECT SourcePropertyId FROM [EquivalentProperty] WHERE [EquivalentProperty].SourceId=2 AND [EquivalentProperty].PropertyId = propertyId
int propertyId = qDef.Filter.propertyId;
if (sourceId == 2 || sourceId == -1)
{
//
// Step 1. Metals
//
EquivalentProperty epM = propIdsM.Where(p => p.PropertyId == propertyId).FirstOrDefault();
// Call service only if relation is defined in the equivalent table
if (epM != null)
{
//int pIdMetalI = propIdsM.Where(p => p.PropertyId == propertyId).FirstOrDefault().SourcePropertyId;
int pIdMetalI = epM.SourcePropertyId;
IList <AdvSearchFilters> filtersForService = new List <AdvSearchFilters>();
//Make object clone and do c; See SearchResultsBinder.cs
filtersForService.Add(new AdvSearchFilters()
{
propertyType = qDef.Filter.propertyType,
logicalOperators = qDef.Filter.logicalOperators,
binaryOperators = qDef.Filter.binaryOperators,
propertyId = pIdMetalI, /* !!! calling byRef, original value is changed !!! */
propertyName = qDef.Filter.propertyName,
valueFrom = qDef.Filter.valueFrom,
valueTo = qDef.Filter.valueTo,
valueFrom_orig = qDef.Filter.valueFrom_orig,
valueTo_orig = qDef.Filter.valueTo_orig,
unitId = qDef.Filter.unitId,
unitName = qDef.Filter.unitName
});
// Service returns list of SourceMaterialId
IList <int> listOfTMMetalIds = service.GetMaterialIdsForAdvSearchPropertiesFromService(sessionId, new AdvSearchFiltersAll()
{
AllFilters = filtersForService
});
// Find material IDs in EMS database
IList <int> res2M = (from u1 in matIdsM join u2 in listOfTMMetalIds on u1.SourceMaterialId equals(int) u2 select u1.MaterialId).ToList();
ds = ds.Concat(res2M).Distinct();
} // End of Call service only if relation is defined in the equivalent table
//
// End of Step 1. Metals
}
//if (sourceId == 3 || sourceId == -1)
if ((sourceId == 3 || sourceId == -1) && filters.IsChemical == false)
{
//
// Step 2. PLUS
//
EquivalentProperty epP = propIdsP.Where(p => p.PropertyId == propertyId).FirstOrDefault();
// Call service only if relation is defined in the equivalent table
if (epP != null)
{
//int pIdMetalI = propIdsM.Where(p => p.PropertyId == propertyId).FirstOrDefault().SourcePropertyId;
int pIdPlusI = epP.SourcePropertyId;
IList <AdvSearchFilters> filtersForService = new List <AdvSearchFilters>();
//Make object clone and do c; See SearchResultsBinder.cs
filtersForService.Add(new AdvSearchFilters()
{
propertyType = qDef.Filter.propertyType,
logicalOperators = qDef.Filter.logicalOperators,
binaryOperators = qDef.Filter.binaryOperators,
propertyId = pIdPlusI, /* !!! calling byRef, original value is changed !!! */
propertyName = qDef.Filter.propertyName,
valueFrom = qDef.Filter.valueFrom,
valueTo = qDef.Filter.valueTo,
valueFrom_orig = qDef.Filter.valueFrom_orig,
valueTo_orig = qDef.Filter.valueTo_orig,
unitId = qDef.Filter.unitId,
unitName = qDef.Filter.unitName
});
// Service returns list of SourceMaterialId
IList <int> listOfTMPLUSIds = servicePLUS.GetMaterialIdsForAdvSearchPropertiesFromServicePLUS(sessionId, new AdvSearchFiltersAll()
{
AllFilters = filtersForService
});
// Find material IDs in EMS database
IList <int> res2P = (from u1 in matIdsP join u2 in listOfTMPLUSIds on u1.SourceMaterialId equals(int) u2 select u1.MaterialId).ToList();
ds = ds.Concat(res2P).Distinct();
} // End of Call service only if relation is defined in the equivalent table
//ds = ds.Concat(res2P).Distinct();
//
// End of Step 2. PLUS
}
} // End of Call Web service
}
if (!withTracking)
{
if (queryCounter == 1)
{
dsAll = ds;
}
else
{
switch (qDef.Operator)
{
case BinaryOperators.NotDefined:
break;
case BinaryOperators.And:
// INTERSECT
dsAll = dsAll.Intersect(ds);
break;
case BinaryOperators.Or:
// UNION
dsAll = dsAll.Union(ds);
break;
case BinaryOperators.Not:
// EXCEPT
dsAll = dsAll.Except(ds);
break;
default:
break;
}
}
}
}
return(dsAll);
}
