Example #1
0
        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;
            }
        }
Example #2
0
        //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;
            }
        }