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