public List<SKUEncode> GetSKUEncodeByCondition(SKUSearchParams param)
        {
            List<SKUEncode> result = null;
            try
            {
                DataTable dt = _dal.GetSKUEncodeByCondition(param);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = new List<SKUEncode>();
                    SKUEncode encode;
                    foreach (DataRow dr in dt.Rows)
                    {
                        Guid ID = dr.GetFieldValue<Guid>("ID");
                        string code = dr.GetFieldValue<string>("CODE");
                        string name = dr.GetFieldValue<string>("NAME");
                        Guid ATT3ID = dr.GetFieldValue<Guid>("ATT3ID");
                        Guid ATT4ID = dr.GetFieldValue<Guid>("ATT4ID");
                        Guid ATT5ID = dr.GetFieldValue<Guid>("ATT5ID");
                        Guid ATT6ID = dr.GetFieldValue<Guid>("ATT6ID");
                        Guid ATT7ID = dr.GetFieldValue<Guid>("ATT7ID");
                        encode = new SKUEncode(ID)
                        {
                            Code = code,
                            Name = name,
                            Att3ID = ATT3ID,
                            Att4ID = ATT4ID,
                            Att5ID = ATT5ID,
                            Att6ID = ATT6ID,
                            Att7ID = ATT7ID,
                        };
                        result.Add(encode);
                    }
                }
            }
            catch (Exception e)
            {
                string excepMsg = string.Format(@"{0},{1}", DateTime.Now.ToString(), e.Message);
                Trace.TraceError(excepMsg);
                throw new Exception("获取SKU编码列表出错:BLLSKUEncodeManagement.GetSKUEncodeByCondition(SKUSearchParams param)", e);
            }

            return result;
        }
        public DataTable GetSKUEncodeByCondition(SKUSearchParams param)
        {
            DataTable result = new DataTable();
            StringBuilder sbSql = new StringBuilder();
            //            sbSql.Append(@"SELECT C.*, A.SKUCID
            //                           FROM SKUENCODE C
            //                           LEFT JOIN SKUATT A ON C.ATT3ID = A.ID
            //                           WHERE 1 = 1 ");
            //if(param.IDTwo != Guid.Empty)
            //{
            //    sbSql.Append(@" AND SKUCID = @SKUCID ");
            //}
            sbSql.Append(@"WITH SUBQRY(ID)
                            AS
                            (
                                SELECT ID
                                FROM SKUCGY
                                WHERE ID = @SelectedSKUCID
                                UNION ALL
                                SELECT SKUCGY.ID
                                FROM SKUCGY, SUBQRY
                                WHERE SKUCGY.PID = SUBQRY.ID
                            )
                             SELECT C.*, A.SKUCID
                             FROM SKUENCODE C
                             LEFT JOIN SKUATT A ON C.ATT3ID = A.ID
                             WHERE A.SKUCID IN
                             (
                             	SELECT ID FROM SUBQRY
                             ) ");
            if(param.Att3 != Guid.Empty)
            {
                sbSql.Append(@" AND ATT3ID = @ATT3ID ");
            }
            if (param.Att4 != Guid.Empty)
            {
                sbSql.Append(@" AND ATT4ID = @ATT4ID ");
            }
            if (param.Att5 != Guid.Empty)
            {
                sbSql.Append(@" AND ATT5ID = @ATT5ID ");
            }
            if (param.Att6 != Guid.Empty)
            {
                sbSql.Append(@" AND ATT6ID = @ATT6ID ");
            }
            if (param.Att7 != Guid.Empty)
            {
                sbSql.Append(@" AND ATT7ID = @ATT7ID ");
            }
            if(!string.IsNullOrWhiteSpace(param.Keyword))
            {
                //sbSql.Append(@" AND C.CODE + C.NAME LIKE '%@Keyword%' ");
                sbSql.Append(string.Format(" AND C.CODE + C.NAME LIKE '%{0}%' ", param.Keyword));
            }
            using (DbCommand cmd = _database.GetSqlStringCommand(sbSql.ToString()))
            {
                //if (param.IDTwo != Guid.Empty)
                //{
                //    _database.AddInParameter(cmd, "@SKUCID", DbType.Guid, param.IDTwo);
                //}
                _database.AddInParameter(cmd, "@SelectedSKUCID", DbType.Guid, param.SelectedSKUCID);
                if (param.Att3 != Guid.Empty)
                {
                    _database.AddInParameter(cmd, "@ATT3ID", DbType.Guid, param.Att3);
                }
                if (param.Att4 != Guid.Empty)
                {
                    _database.AddInParameter(cmd, "@ATT4ID", DbType.Guid, param.Att4);
                }
                if (param.Att5 != Guid.Empty)
                {
                    _database.AddInParameter(cmd, "@ATT5ID", DbType.Guid, param.Att5);
                }
                if (param.Att6 != Guid.Empty)
                {
                    _database.AddInParameter(cmd, "@ATT6ID", DbType.Guid, param.Att6);
                }
                if (param.Att7 != Guid.Empty)
                {
                    _database.AddInParameter(cmd, "@ATT7ID", DbType.Guid, param.Att7);
                }
                //if (!string.IsNullOrWhiteSpace(param.Keyword))
                //{
                //    _database.AddInParameter(cmd, "@Keyword", DbType.String, param.Keyword);
                //}
                using (IDataReader reader = _database.ExecuteReader(cmd))
                {
                    result.Load(reader);
                }
            }

            return result;
        }
Ejemplo n.º 3
0
 private void CmdSearchExecute()
 {
     if(this.RefreshingVisibility == Visibility.Visible)
     {
         return;
     }
     this.ItemsSource.Clear();
     this.RefreshingVisibility = Visibility.Visible;
     SKUSearchParams param = new SKUSearchParams();
     //param.IDOne = this.SelectedSKUCGY.PID.HasValue ? this.SelectedSKUCGY.PID.Value : Guid.Empty;
     //param.IDTwo = this.SelectedSKUCGY.ID;
     param.SelectedSKUCID = this.SelectedSKUCGY == null ? Guid.Empty : this.SelectedSKUCGY.ID;
     param.Att3 = this.SeletedATT3.ID;
     param.Att4 = this.SeletedATT4.ID;
     param.Att5 = this.SeletedATT5.ID;
     param.Att6 = this.SeletedATT6.ID;
     param.Att7 = this.SeletedATT7.ID;
     param.Keyword = this.SKUKeyword;
     _factorySKUEncode.StartNew(this.GetSKUEncodeByCondition, param).ContinueWith(this.GetSKUEncodeByConditionComplete, _schedule);
 }