public IList<string> GetPnListByModelAndBomNodeType(IList<string> modelList, string bomNodeType, string prefixDescr)
{
try
{
IList<string> ret = new List<string>();
ITableAndFields tf1 = null;
ITableAndFields tf2 = null;
ITableAndFields[] tafa = null;
MethodBase mthObj = MethodBase.GetCurrentMethod();
int tk = mthObj.MetadataToken;
SQLContextNew sqlCtx = null;
lock (mthObj)
{
if (!SQLCache.PeerTheSQL(tk, out sqlCtx))
{
tf1 = new TableAndFields<Part_NEW>();
Part_NEW cond = new Part_NEW();
cond.bomNodeType = bomNodeType;
cond.flag = 1;
tf1.Conditions.Add(new EqualCondition<Part_NEW>(cond));
Part_NEW cond3 = new Part_NEW();
cond3.descr = prefixDescr + "%";
tf1.Conditions.Add(new LikeCondition<Part_NEW>(cond3));
tf1.AddRangeToGetFieldNames(Part_NEW.fn_partNo);
tf2 = new TableAndFields<ModelBOM_NEW>();
ModelBOM_NEW cond2 = new ModelBOM_NEW();
cond2.flag = 1;
tf2.Conditions.Add(new EqualCondition<ModelBOM_NEW>(cond2));
ModelBOM_NEW cond4 = new ModelBOM_NEW();
cond4.material = "[INSET]";
tf2.Conditions.Add(new InSetCondition<ModelBOM_NEW>(cond4));
tf2.ClearToGetFieldNames();
tafa = new ITableAndFields[] { tf1, tf2 };
TableConnectionCollection tblCnnts = new TableConnectionCollection(
new TableConnectionItem<Part_NEW, ModelBOM_NEW>(tf1, Part_NEW.fn_partNo, tf2, ModelBOM_NEW.fn_component));
sqlCtx = FuncNew.GetConditionedJoinedSelect(tk, "DISTINCT", tafa, tblCnnts);
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_flag)).Value = cond.flag;
sqlCtx.Param(g.DecAlias(tf2.Alias, ModelBOM_NEW.fn_flag)).Value = cond2.flag;
}
}
tafa = sqlCtx.TableFields;
tf1 = tafa[0];
tf2 = tafa[1];
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_bomNodeType)).Value = bomNodeType;
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_descr)).Value = prefixDescr;
string Sentence = sqlCtx.Sentence.Replace(g.DecAlias(tf2.Alias, g.DecInSet(mtns::ModelBOM_NEW.fn_material)), g.ConvertInSet(new List<string>(modelList)));
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM,
CommandType.Text,
Sentence,
sqlCtx.Params))
{
while (sqlR != null && sqlR.Read())
{
string item = g.GetValue_Str(sqlR, sqlCtx.Indexes(g.DecAlias(tf1.Alias, Part_NEW.fn_partNo)));
ret.Add(item);
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
public IList<string> GetESOPListByModel(string model)
{
try
{
IList<string> ret = new List<string>();
ITableAndFields tf1 = null;
ITableAndFields tf2 = null;
ITableAndFields tf3 = null;
ITableAndFields tf4 = null;
ITableAndFields[] tafa = null;
MethodBase mthObj = MethodBase.GetCurrentMethod();
int tk = mthObj.MetadataToken;
SQLContextNew sqlCtx = null;
lock (mthObj)
{
if (!SQLCache.PeerTheSQL(tk, out sqlCtx))
{
tf1 = new TableAndFields<Part_NEW>();
Part_NEW cond = new Part_NEW();
cond.bomNodeType = "PL";
cond.flag = 1;
tf1.Conditions.Add(new EqualCondition<Part_NEW>(cond));
tf1.ClearToGetFieldNames();
tf2 = new TableAndFields<ModelBOM_NEW>();
ModelBOM_NEW cond2 = new ModelBOM_NEW();
cond2.material = model;
cond2.flag = 1;
tf2.Conditions.Add(new EqualCondition<ModelBOM_NEW>(cond2));
tf2.ClearToGetFieldNames();
tf3 = new TableAndFields<_Metas.PartInfo>();
_Metas.PartInfo cond3 = new _Metas.PartInfo();
cond3.infoType = "Picture";
tf3.Conditions.Add(new EqualCondition<_Metas.PartInfo>(cond3));
_Metas.PartInfo cond31 = new _Metas.PartInfo();
cond31.infoValue = "Y";
tf3.Conditions.Add(new EqualCondition<_Metas.PartInfo>(cond31, "RTRIM({0})"));
tf3.ClearToGetFieldNames();
tf4 = new TableAndFields<_Metas.PartInfo>();
_Metas.PartInfo cond4 = new _Metas.PartInfo();
cond4.infoType = "PictureName";
tf4.Conditions.Add(new EqualCondition<_Metas.PartInfo>(cond4));
tf4.AddRangeToGetFieldNames(_Metas.PartInfo.fn_infoValue);
tafa = new ITableAndFields[] { tf1, tf2, tf3, tf4 };
TableConnectionCollection tblCnnts = new TableConnectionCollection(
new TableConnectionItem<Part_NEW, ModelBOM_NEW>(tf1, Part_NEW.fn_partNo, tf2, ModelBOM_NEW.fn_component),
new TableConnectionItem<Part_NEW, _Metas.PartInfo>(tf1, Part_NEW.fn_partNo, tf3, _Metas.PartInfo.fn_partNo),
new TableConnectionItem<Part_NEW, _Metas.PartInfo>(tf1, Part_NEW.fn_partNo, tf4, _Metas.PartInfo.fn_partNo)
);
sqlCtx = FuncNew.GetConditionedJoinedSelect(tk, "TOP 4", tafa, tblCnnts);
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_flag)).Value = cond.flag;
sqlCtx.Param(g.DecAlias(tf2.Alias, ModelBOM_NEW.fn_flag)).Value = cond2.flag;
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_bomNodeType)).Value = cond.bomNodeType;
sqlCtx.Param(g.DecAlias(tf3.Alias, _Metas.PartInfo.fn_infoType)).Value = cond3.infoType;
sqlCtx.Param(g.DecAlias(tf3.Alias, _Metas.PartInfo.fn_infoValue)).Value = cond31.infoValue;
sqlCtx.Param(g.DecAlias(tf4.Alias, _Metas.PartInfo.fn_infoType)).Value = cond4.infoType;
}
}
tafa = sqlCtx.TableFields;
tf1 = tafa[0];
tf2 = tafa[1];
tf3 = tafa[2];
tf4 = tafa[3];
sqlCtx.Param(g.DecAlias(tf2.Alias, ModelBOM_NEW.fn_material)).Value = model;
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM, CommandType.Text, sqlCtx.Sentence, sqlCtx.Params))
{
while (sqlR != null && sqlR.Read())
{
string item = g.GetValue_Str(sqlR, sqlCtx.Indexes(g.DecAlias(tf4.Alias, _Metas.PartInfo.fn_infoValue)));
ret.Add(item);
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
//public IFlatBOM GetMoFlatBOMByStation(string station, string line, string mo)
//{
//
//}
//public IHierarchicalBOM GetHierarchicalBOMByMo(string mo)
//{
//
//}
public IList<string> GetPartInfoValueListByModelAndBomNodeTypeAndInfoType(string model, string bomNodeType, string infoType, string notEqInfoType, string notEqInfoValue)
{
try
{
IList<string> ret = new List<string>();
ITableAndFields tf1 = null;
ITableAndFields tf2 = null;
ITableAndFields tf3 = null;
ITableAndFields[] tafa = null;
MethodBase mthObj = MethodBase.GetCurrentMethod();
int tk = mthObj.MetadataToken;
SQLContextNew sqlCtx = null;
lock (mthObj)
{
if (!SQLCache.PeerTheSQL(tk, out sqlCtx))
{
tf1 = new TableAndFields<Part_NEW>();
Part_NEW cond = new Part_NEW();
cond.bomNodeType = bomNodeType;
cond.flag = 1;
tf1.Conditions.Add(new EqualCondition<Part_NEW>(cond));
tf1.ClearToGetFieldNames();
tf2 = new TableAndFields<ModelBOM_NEW>();
ModelBOM_NEW cond2 = new ModelBOM_NEW();
cond2.material = model;
cond2.flag = 1;
tf2.Conditions.Add(new EqualCondition<ModelBOM_NEW>(cond2));
tf2.ClearToGetFieldNames();
tf3 = new TableAndFields<_Metas.PartInfo>();
_Metas.PartInfo cond3 = new _Metas.PartInfo();
cond3.infoType = infoType;
tf3.Conditions.Add(new EqualCondition<_Metas.PartInfo>(cond3));
tf3.AddRangeToGetFieldNames(_Metas.PartInfo.fn_infoValue);
tafa = new ITableAndFields[] { tf1, tf2, tf3 };
TableConnectionCollection tblCnnts = new TableConnectionCollection(
new TableConnectionItem<Part_NEW, ModelBOM_NEW>(tf1, Part_NEW.fn_partNo, tf2, ModelBOM_NEW.fn_component),
new TableConnectionItem<Part_NEW, _Metas.PartInfo>(tf1, Part_NEW.fn_partNo, tf3, _Metas.PartInfo.fn_partNo)
);
sqlCtx = FuncNew.GetConditionedJoinedSelect(tk, "DISTINCT", tafa, tblCnnts);
sqlCtx.Sentence = sqlCtx.Sentence + string.Format(" AND t1.{0} NOT IN (SELECT d.{1} FROM {2} d WHERE d.{1}=t1.{0} AND d.{3}=@notEq{3} AND d.{4}=@notEq{4})", Part_NEW.fn_partNo, _Metas.PartInfo.fn_partNo, ToolsNew.GetTableName(typeof(_Metas.PartInfo)), _Metas.PartInfo.fn_infoType, _Metas.PartInfo.fn_infoValue);
sqlCtx.AddParam(_Metas.PartInfo.fn_infoType, new SqlParameter("@notEq" + _Metas.PartInfo.fn_infoType, ToolsNew.GetDBFieldType<_Metas.PartInfo>(_Metas.PartInfo.fn_infoType)));
sqlCtx.AddParam(_Metas.PartInfo.fn_infoValue, new SqlParameter("@notEq" + _Metas.PartInfo.fn_infoValue, ToolsNew.GetDBFieldType<_Metas.PartInfo>(_Metas.PartInfo.fn_infoValue)));
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_flag)).Value = cond.flag;
sqlCtx.Param(g.DecAlias(tf2.Alias, ModelBOM_NEW.fn_flag)).Value = cond2.flag;
}
}
tafa = sqlCtx.TableFields;
tf1 = tafa[0];
tf2 = tafa[1];
tf3 = tafa[2];
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_bomNodeType)).Value = bomNodeType;
sqlCtx.Param(g.DecAlias(tf2.Alias, ModelBOM_NEW.fn_material)).Value = model;
sqlCtx.Param(g.DecAlias(tf3.Alias, _Metas.PartInfo.fn_infoType)).Value = infoType;
sqlCtx.Param(_Metas.PartInfo.fn_infoType).Value = notEqInfoType;
sqlCtx.Param(_Metas.PartInfo.fn_infoValue).Value = notEqInfoValue;
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM, CommandType.Text, sqlCtx.Sentence, sqlCtx.Params))
{
while (sqlR != null && sqlR.Read())
{
string item = g.GetValue_Str(sqlR, sqlCtx.Indexes(g.DecAlias(tf3.Alias, _Metas.PartInfo.fn_infoValue)));
ret.Add(item);
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
public bool CheckExistModelBOMByMaterialAndPartDescrLike(string material, string descrInStr)
{
try
{
bool ret = false;
ITableAndFields tf1 = null;
ITableAndFields tf2 = null;
ITableAndFields[] tafa = null;
MethodBase mthObj = MethodBase.GetCurrentMethod();
int tk = mthObj.MetadataToken;
SQLContextNew sqlCtx = null;
lock (mthObj)
{
if (!SQLCache.PeerTheSQL(tk, out sqlCtx))
{
tf1 = new TableAndFields<Part_NEW>();
Part_NEW cond0 = new Part_NEW();
cond0.flag = 1;
tf1.Conditions.Add(new EqualCondition<Part_NEW>(cond0));
Part_NEW cond = new Part_NEW();
cond.descr = "%" + descrInStr + "%";
tf1.Conditions.Add(new LikeCondition<Part_NEW>(cond, "UPPER({0})", "UPPER({0})"));
tf1.ClearToGetFieldNames();
tf2 = new TableAndFields<ModelBOM_NEW>();
ModelBOM_NEW cond2 = new ModelBOM_NEW();
cond2.material = material;
cond2.flag = 1;
tf2.Conditions.Add(new EqualCondition<ModelBOM_NEW>(cond2));
tf2.AddRangeToGetFieldNames(ModelBOM_NEW.fn_id);
tafa = new ITableAndFields[] { tf1, tf2 };
TableConnectionCollection tblCnnts = new TableConnectionCollection(
new TableConnectionItem<Part_NEW, ModelBOM_NEW>(tf1, Part_NEW.fn_partNo, tf2, ModelBOM_NEW.fn_component));
sqlCtx = FuncNew.GetConditionedJoinedSelect(tk, "COUNT", tafa, tblCnnts);
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_flag)).Value = cond0.flag;
sqlCtx.Param(g.DecAlias(tf2.Alias, ModelBOM_NEW.fn_flag)).Value = cond2.flag;
}
}
tafa = sqlCtx.TableFields;
tf1 = tafa[0];
tf2 = tafa[1];
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_descr)).Value = "%" + descrInStr + "%";
sqlCtx.Param(g.DecAlias(tf2.Alias, ModelBOM_NEW.fn_material)).Value = material;
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM, CommandType.Text, sqlCtx.Sentence, sqlCtx.Params))
{
if (sqlR != null && sqlR.Read())
{
int cnt = g.GetValue_Int32(sqlR, sqlCtx.Indexes("COUNT"));
ret = cnt > 0 ? true : false;
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
public IList<MoBOMInfo> GetPnListByModelAndBomNodeTypeAndDescr(string model, string bomNodeType, string descrPrefix)
{
try
{
IList<MoBOMInfo> ret = null;
ITableAndFields tf1 = null;
ITableAndFields tf2 = null;
ITableAndFields[] tafa = null;
MethodBase mthObj = MethodBase.GetCurrentMethod();
int tk = mthObj.MetadataToken;
SQLContextNew sqlCtx = null;
lock (mthObj)
{
if (!SQLCache.PeerTheSQL(tk, out sqlCtx))
{
tf1 = new TableAndFields<Part_NEW>();
Part_NEW cond = new Part_NEW();
cond.bomNodeType = bomNodeType;
cond.flag = 1;
tf1.Conditions.Add(new EqualCondition<Part_NEW>(cond));
Part_NEW cond3 = new Part_NEW();
cond3.descr = descrPrefix + "%";
tf1.Conditions.Add(new LikeCondition<Part_NEW>(cond3));
tf1.ClearToGetFieldNames();
tf2 = new TableAndFields<ModelBOM_NEW>();
ModelBOM_NEW cond2 = new ModelBOM_NEW();
cond2.material = model;
cond2.flag = 1;
tf2.Conditions.Add(new EqualCondition<ModelBOM_NEW>(cond2));
tafa = new ITableAndFields[] { tf1, tf2 };
TableConnectionCollection tblCnnts = new TableConnectionCollection(
new TableConnectionItem<Part_NEW, ModelBOM_NEW>(tf1, Part_NEW.fn_partNo, tf2, ModelBOM_NEW.fn_component));
sqlCtx = FuncNew.GetConditionedJoinedSelect(tk, "DISTINCT", tafa, tblCnnts);
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_flag)).Value = cond.flag;
sqlCtx.Param(g.DecAlias(tf2.Alias, ModelBOM_NEW.fn_flag)).Value = cond2.flag;
}
}
tafa = sqlCtx.TableFields;
tf1 = tafa[0];
tf2 = tafa[1];
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_bomNodeType)).Value = bomNodeType;
sqlCtx.Param(g.DecAlias(tf1.Alias, Part_NEW.fn_descr)).Value = descrPrefix + "%";
sqlCtx.Param(g.DecAlias(tf2.Alias, ModelBOM_NEW.fn_material)).Value = model;
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM, CommandType.Text, sqlCtx.Sentence, sqlCtx.Params))
{
ret = FuncNew.SetFieldFromColumn<ModelBOM_NEW, MoBOMInfo, MoBOMInfo>(ret, sqlR, sqlCtx, tf2.Alias);
}
return ret;
}
catch (Exception)
{
throw;
}
}
