/// <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();
}
public new void ToString()
{
SqlPredicate p;
p = new SqlPredicate("x is null");
p.ToString();
Assert.That(p.ToString(), Is.EqualTo("x IS NULL"));
}
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);
}
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));
}
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"));
}
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(""));
}
/// <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);
}
/// <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);
}
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)"));
}
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);
}
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'" }
}));
}
/// <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);
}
/// <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);
}
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();
}
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"));
}
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"));
}
public QueryBuilder SetWhereStatement(SqlPredicate whereStatement)
{
WhereStatement = whereStatement;
return(this);
}