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; } }