Ejemplo n.º 1
0
        /// <summary>
        /// Returns MessageProductionData for each Recepient in template.
        /// </summary>
        public IEnumerable <MessageProductionData> Collect(IMessageTemplate templ)
        {
            var output = new List <MessageProductionData>();
            //Migrate all needed data to memory, because it will be intensive processed
            var filters = templ.Filters != null?templ.Filters.ToArray() : new tblFilter[]
            {
            };
            var wildcards = templ.Wildcards != null?templ.Wildcards.ToArray() : new tblWildcard[]
            {
            };
            var recepients = templ.Recepients != null?templ.Recepients.ToArray() : new tblRecepientCard[]
            {
            };
            var userInputedValues = templ.FilterValueContainers != null?templ.FilterValueContainers.ToArray() : new FilterValueContainer[]
            {
            };

            Dictionary <int, ValueOperatorPair[]> filtsToValOps = GetFiltersActualSettings(filters, userInputedValues);

            var Condition = SqlPredicate.BuildAndNode();

            if (filters.Length > 0)
            {
                foreach (var f in filters)
                {
                    var orNode = SqlPredicate.BuildOrNode();
                    var valops = filtsToValOps[f.Id];
                    foreach (var valop in valops)
                    {
                        orNode.Append(SqlPredicate.BuildEndNode(f.Key, valop.Operator, valop.Value, f.Type));
                    }
                    Condition.Append(orNode);
                }
            }

            //Build list of needed colomns
            var colomns = wildcards.Select(x => x.Key)
                          .Concat(recepients.Select(x => x.EmailKey))
                          .Concat(recepients.Select(x => x.NameKey))
                          .Concat(recepients.Select(x => x.PhoneKey)).Distinct();

            var sqlData = Manager.SqlLogic.FetchData(colomns, templ.TableWithKeysName, "dbo", Condition);

            var wildcardsSummed = wildcards.SelectMany(x => x.ToKeyValues());

            bool IsSms = templ.IsSms;

            foreach (var rec in recepients)
            {
                var prodData = new MessageProductionData();
                var GroupKey = IsSms ? rec.PhoneKey : rec.EmailKey;
                prodData.TextProductionData = sqlData
                                              .Where(x => !string.IsNullOrWhiteSpace(x[GroupKey].ToString()))
                                              .GroupBy(x => x[GroupKey].ToString());
                prodData.wildCards = wildcardsSummed.Concat(rec.ToKeyValues());
                output.Add(prodData);
            }

            return(output);
        }
        public async Task TestKeySetPredicate()
        {
            var dictionary = await Client.GetMapAsync <string, string>(CreateUniqueName());

            await using var _ = DestroyAndDispose(dictionary);

            await dictionary.SetAsync("key1", "value1");

            await dictionary.SetAsync("key2", "value2");

            await dictionary.SetAsync("key3", "value3");

            await using var context = await Client.BeginTransactionAsync();

            var txDictionary = await context.GetMapAsync <string, string>(dictionary.Name);

            var sqlPredicate = new SqlPredicate("this == value1");
            var keys         = await txDictionary.GetKeysAsync(sqlPredicate);

            Assert.AreEqual(1, keys.Count);
            Assert.AreEqual("key1", keys.First());

            var values = await txDictionary.GetValuesAsync(sqlPredicate);

            Assert.AreEqual(1, values.Count);
            Assert.AreEqual("value1", values.First());

            await context.CommitAsync();
        }
Ejemplo n.º 3
0
        public new void ToString()
        {
            SqlPredicate p;

            p = new SqlPredicate("x is null");
            p.ToString();
            Assert.That(p.ToString(), Is.EqualTo("x IS NULL"));
        }
Ejemplo n.º 4
0
        public void IsPlaceHolderOnly()
        {
            SqlPredicate p;

            p = new SqlPredicate("@PH");
            Assert.That(p.IsPlaceHolderOnly, Is.True);

            p = new SqlPredicate("a is not null");
            Assert.That(p.IsPlaceHolderOnly, Is.False);
        }
Ejemplo n.º 5
0
        public long Count <TEntity>(string columnName, SqlPredicate whereStatement) where TEntity : new()
        {
            EnsureConnectionOpen();
            QueryBuilder builder = QueryBuilder.Create().SpecifyColumns(null).AddSpecialColumn(new CountSpecialColumn(columnName, $"{(columnName == "*" ? "AllColumns" : columnName)}Count"));

            if (whereStatement != null)
            {
                builder.SetWhereStatement(whereStatement);
            }
            return(ExecuteScalar <TEntity, long>(builder));
        }
Ejemplo n.º 6
0
        public void Clone()
        {
            SqlPredicate p;
            SqlPredicate p1;

            p  = new SqlPredicate("@PH");
            p1 = p.Clone();
            Assert.That(p.ToString(), Is.EqualTo("@PH"));
            Assert.That(p1.ToString(), Is.EqualTo("@PH"));
            p1.Place("PH", "x=1");
            Assert.That(p.ToString(), Is.EqualTo("@PH"));
            Assert.That(p1.ToString(), Is.EqualTo("x=1"));
        }
Ejemplo n.º 7
0
        public void Empty()
        {
            SqlPredicate p = new SqlPredicate();

            Assert.That(p.And(p).ToString(), Is.EqualTo(""));
            Assert.That(p.Clone().ToString(), Is.EqualTo(""));
            Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new string[] { }));
            Assert.That(p.HasUnplacedHolder("T"), Is.False);
            Assert.That(p.HasUnplacedHolders(), Is.False);
            Assert.That(p.IsEmpty, Is.True);
            Assert.That(p.IsPlaceHolderOnly, Is.False);
            p.Place("PH", "a=1");
            Assert.That(p.ToString(), Is.EqualTo(""));
        }
Ejemplo n.º 8
0
        /// <summary>
        /// 查询已存在的数据项
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="userRole">要保存的数据项</param>
        /// <returns></returns>
        protected override (UserRole, Exception) QueryExists(IDbConnection conn, UserRole userRole)
        {
            if (!userRole.UserGuid.IsValid() || !userRole.RoleCode.IsValid())
            {
                return(null, Exceptions.InvalidParam);
            }

            var matchFields = new string[] {
                nameof(userRole.Uid), nameof(userRole.UserGuid), nameof(userRole.RoleCode)
            };

            var matchSql  = $"{{0}} {LogicTypes.OR} ({{1}} {LogicTypes.AND} {{2}})";
            var predicate = new SqlPredicate <UserRole> (userRole, matchSql, matchFields);

            var(_, items) = conn.GetWhere(predicate);
            return(items?.FirstOrDefault(), null);
        }
Ejemplo n.º 9
0
        /// <summary>
        /// 查询已存在的数据项
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="rolePerm">要保存的数据项</param>
        /// <returns></returns>
        protected override (RolePermission, Exception) QueryExists(IDbConnection conn, RolePermission rolePerm)
        {
            if (null == rolePerm || !rolePerm.Uid.IsValid() ||
                !rolePerm.RoleCode.IsValid() || !rolePerm.PermCode.IsValid())
            {
                return(null, Exceptions.InvalidParam);
            }

            var matchFields = new string[] {
                nameof(rolePerm.Uid), nameof(rolePerm.RoleCode), nameof(rolePerm.PermCode)
            };

            var matchSql  = $"{{0}} {LogicTypes.OR} ({{1}} {LogicTypes.AND} {{2}})";
            var predicate = new SqlPredicate <RolePermission> (rolePerm, matchSql, matchFields);

            var(_, items) = conn.GetWhere(predicate);
            return(items?.FirstOrDefault(), null);
        }
Ejemplo n.º 10
0
        public void Place()
        {
            SqlPredicate p;

            p = new SqlPredicate("not c > 1");
            p.Place("PH", "a");
            Assert.That(p.ToString(), Is.EqualTo("NOT c>1"));

            p = new SqlPredicate("@PH");
            Assert.Throws <CannotBuildASTException>(() => { p.Place("PH", "1"); });

            p = new SqlPredicate("@PH");
            p.Place("PH", "a=1");
            Assert.That(p.ToString(), Is.EqualTo("a=1"));

            p = new SqlPredicate("Not @PH");
            p.Place("PH", "x=2 or y=3");
            Assert.That(p.ToString(), Is.EqualTo("NOT (x=2 OR y=3)"));
        }
Ejemplo n.º 11
0
        public void HasUnplacedHolders()
        {
            SqlPredicate p;

            p = new SqlPredicate("@PH");
            Assert.That(p.HasUnplacedHolders(), Is.True);
            p.Place("PH", "'abc' <= 'abcd'");
            Assert.That(p.HasUnplacedHolders(), Is.False);

            p = new SqlPredicate("@PH1 And @PH2");
            p.Place("PH1", "x = +100");
            Assert.That(p.HasUnplacedHolders(), Is.True);
            p.Place("PH2", "y = -9");
            Assert.That(p.HasUnplacedHolders(), Is.False);

            p = new SqlPredicate("@PH AND @PH");
            p.Place("PH", "'abc' <= 'abcd'");
            Assert.That(p.HasUnplacedHolders(), Is.False);
            p.Place("PH", "'abc' <= 'abcd'");
            Assert.That(p.HasUnplacedHolders(), Is.False);
        }
Ejemplo n.º 12
0
        public void GetAllPlaceHolders()
        {
            SqlPredicate p;

            p = new SqlPredicate("@PH");
            Assert.That(p.GetAllPlaceHolders()
                        , Is.EqualTo(new Dictionary <string, string> {
                { "PH", "" }
            }));
            p.Place("PH", "'abc' > 0");
            Assert.That(p.GetAllPlaceHolders()
                        , Is.EqualTo(new Dictionary <string, string> {
                { "PH", "'abc'>0" }
            }));

            p = new SqlPredicate("@PH1 And @PH2");
            p.Place("PH1", "x=+100");
            Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string>
            {
                { "PH1", "x=+100" }, { "PH2", "" }
            }));
            p.Place("PH2", "y = -9");
            Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string>
            {
                { "PH1", "x=+100" }, { "PH2", "y=-9" }
            }));

            p = new SqlPredicate("@PH AND @PH");
            p.Place("PH", "x = 'abc'");
            Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string>
            {
                { "PH", "x='abc'" }
            }));
            p.Place("PH", "x = 'abc'");
            Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string>
            {
                { "PH", "x='abc'" }
            }));
        }
Ejemplo n.º 13
0
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="user"></param>
        /// <param name="newPasswordHash">新密码哈希值</param>
        /// <returns></returns>
        private Exception ChangePassword(IDbConnection conn, IDbTransaction trans, User user, string newPasswordHash)
        {
            if (null == user || !user.Uid.IsValid() ||
                !user.PasswordHash.IsValid() || !newPasswordHash.IsValid())
            {
                return(Exceptions.InvalidParam);
            }

            var matchFields = new string[] {
                nameof(user.Uid), nameof(user.IsDeleted)
            };

            //未删除的用户
            user.IsDeleted = 0;

            var predicate = new SqlPredicate <User> (user, matchFields);

            var(_, items, ex) = Select(predicate);
            var item = items?.FirstOrDefault(x => x != null);

            if (null == item)
            {
                return(Exceptions.NotFound);
            }

            if (!user.PasswordHash.Equals(item.PasswordHash))
            {
                return(Exceptions.Indentify);
            }

            item.PasswordHash = newPasswordHash;
            item.UpdateUser   = user.UpdateUser;
            item.UpdateTime   = DateTime.Now;

            return(conn.Update(item, trans) ? null : Exceptions.Failure);
        }
Ejemplo n.º 14
0
        /// <summary>
        /// 验证用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public (User, Exception) VerifyUser(User user)
        {
            if (null == user || !user.UserName.IsValid() || !user.PasswordHash.IsValid())
            {
                return(null, Exceptions.InvalidParam);
            }

            var matchFields = new string[] {
                nameof(user.UserName), nameof(user.PasswordHash), nameof(user.IsDeleted)
            };

            //未删除的用户
            user.IsDeleted = 0;

            var predicate = new SqlPredicate <User> (user, matchFields);

            var(_, items, ex) = Select(predicate);
            if (items.IsEmpty())
            {
                return(null, ex ?? Exceptions.NotFound);
            }

            return(items.First(), ex);
        }
Ejemplo n.º 15
0
        public virtual void TestKeySetPredicate()
        {
            _map.Put("key1", "value1");
            _map.Put("key2", "value2");
            _map.Put("key3", "value3");

            var context = Client.NewTransactionContext();

            context.BeginTransaction();
            var txnMap = context.GetMap <object, object>(_name);

            var sqlPredicate = new SqlPredicate <object, object>("this == value1");
            var keys         = txnMap.KeySet(sqlPredicate);

            Assert.AreEqual(1, keys.Count);
            Assert.AreEqual("key1", keys.First());

            var values = txnMap.Values(sqlPredicate);

            Assert.AreEqual(1, values.Count);
            Assert.AreEqual("value1", values.First());

            context.CommitTransaction();
        }
Ejemplo n.º 16
0
        public void And()
        {
            SqlPredicate p;
            SqlPredicate p1;

            p  = new SqlPredicate("@PH and 1 = 1");
            p1 = p.And(new SqlPredicate("x is null and 2=2"));
            Assert.That(p1.ToString(), Is.EqualTo("@PH AND 1=1 AND x IS NULL AND 2=2"));

            p1 = p.And(new SqlPredicate("x is null or 2=2"));
            Assert.That(p1.ToString(), Is.EqualTo("@PH AND 1=1 AND (x IS NULL OR 2=2)"));

            p  = new SqlPredicate("@PH or 1 = 1");
            p1 = p.And(new SqlPredicate("x is null and 2 = 2"));
            Assert.That(p1.ToString(), Is.EqualTo("(@PH OR 1=1) AND x IS NULL AND 2=2"));

            p  = new SqlPredicate("x is null");
            p1 = p.And(p);
            Assert.That(p1.ToString(), Is.EqualTo("x IS NULL AND x IS NULL"));

            p  = new SqlPredicate("x = 11");
            p1 = p.And(new SqlPredicate());
            Assert.That(p1.ToString(), Is.EqualTo("x=11"));
        }
Ejemplo n.º 17
0
        public void Place2()
        {
            SqlPredicate p;

            //
            // Predicate演算子の結合の優先順位が適用前後で変わらないことを確認する
            //

            p = new SqlPredicate("@PH");
            p.Place("PH", "a=1 and b=2");
            Assert.That(p.ToString(), Is.EqualTo("a=1 AND b=2"));

            p = new SqlPredicate("@PH");
            p.Place("PH", "a=1 or b=2");
            Assert.That(p.ToString(), Is.EqualTo("a=1 OR b=2"));

            p = new SqlPredicate("@PH");
            p.Place("PH", "not a=1");
            Assert.That(p.ToString(), Is.EqualTo("NOT a=1"));

            p = new SqlPredicate("@PH");
            p.Place("PH", "a=1 collate jp");
            Assert.That(p.ToString(), Is.EqualTo("a=1 COLLATE jp"));


            p = new SqlPredicate("@PH AND 1 = 1");
            p.Place("PH", "a=1 and b=2");
            Assert.That(p.ToString(), Is.EqualTo("a=1 AND b=2 AND 1=1"));

            p = new SqlPredicate("@PH AND 1 = 1");
            p.Place("PH", "a=1 or b=2");
            Assert.That(p.ToString(), Is.EqualTo("(a=1 OR b=2) AND 1=1"));

            p = new SqlPredicate("@PH AND 1 = 1");
            p.Place("PH", "not a=1");
            Assert.That(p.ToString(), Is.EqualTo("NOT a=1 AND 1=1"));

            p = new SqlPredicate("@PH AND 1 = 1");
            p.Place("PH", "a=1 collate jp");
            Assert.That(p.ToString(), Is.EqualTo("a=1 COLLATE jp AND 1=1"));


            p = new SqlPredicate("@PH OR 1 = 1");
            p.Place("PH", "a=1 and b=2");
            Assert.That(p.ToString(), Is.EqualTo("a=1 AND b=2 OR 1=1"));

            p = new SqlPredicate("@PH OR 1 = 1");
            p.Place("PH", "a=1 or b=2");
            Assert.That(p.ToString(), Is.EqualTo("a=1 OR b=2 OR 1=1"));

            p = new SqlPredicate("@PH OR 1 = 1");
            p.Place("PH", "not a=1");
            Assert.That(p.ToString(), Is.EqualTo("NOT a=1 OR 1=1"));

            p = new SqlPredicate("@PH OR 1 = 1");
            p.Place("PH", "a=1 collate jp");
            Assert.That(p.ToString(), Is.EqualTo("a=1 COLLATE jp OR 1=1"));


            p = new SqlPredicate("Not @PH");
            p.Place("PH", "a=1 and b=2");
            Assert.That(p.ToString(), Is.EqualTo("NOT (a=1 AND b=2)"));

            p = new SqlPredicate("Not @PH");
            p.Place("PH", "a=1 or b=2");
            Assert.That(p.ToString(), Is.EqualTo("NOT (a=1 OR b=2)"));

            p = new SqlPredicate("Not @PH");
            p.Place("PH", "not a=1");
            Assert.That(p.ToString(), Is.EqualTo("NOT NOT a=1"));

            p = new SqlPredicate("Not @PH");
            p.Place("PH", "a=1 collate jp");
            Assert.That(p.ToString(), Is.EqualTo("NOT a=1 COLLATE jp"));


            p = new SqlPredicate("@PH collate jp");
            p.Place("PH", "a=1 and b=2");
            Assert.That(p.ToString(), Is.EqualTo("(a=1 AND b=2) COLLATE jp"));

            p = new SqlPredicate("@PH collate jp");
            p.Place("PH", "a=1 or b=2");
            Assert.That(p.ToString(), Is.EqualTo("(a=1 OR b=2) COLLATE jp"));

            p = new SqlPredicate("@PH collate jp");
            p.Place("PH", "not a=1");
            Assert.That(p.ToString(), Is.EqualTo("NOT a=1 COLLATE jp"));

            p = new SqlPredicate("@PH collate jp");
            p.Place("PH", "a=1 collate jp");
            Assert.That(p.ToString(), Is.EqualTo("a=1 COLLATE jp COLLATE jp"));
        }
Ejemplo n.º 18
0
 public QueryBuilder SetWhereStatement(SqlPredicate whereStatement)
 {
     WhereStatement = whereStatement;
     return(this);
 }