public static List <StatisticModule> GetStatistic()
{
using (var dbManager = DbManager.FromHttpContext(Dbid))
{
var sql = new SqlQuery(ResDataTable + " r1")
.Select("sum(LENGTH(r1.textvalue) - LENGTH(REPLACE(r1.textvalue, ' ', '')) + 1)")
.Select("rc.value", "rf.projectName")
.Select("sum(LENGTH(r2.textvalue) - LENGTH(REPLACE(r2.textvalue, ' ', '')) + 1)")
.InnerJoin(ResFilesTable + " as rf", Exp.EqColumns("rf.id", "r1.fileid"))
.InnerJoin(ResCultureTable + " as rc", Exp.EqColumns("r1.cultureTitle", "rc.title"))
.InnerJoin(ResDataTable + " as r2", Exp.And(Exp.EqColumns("r1.fileID", "r2.fileID"), Exp.EqColumns("r1.title", "r2.title")))
.Where(Exp.Lt("r1.flag", 3))
.Where("r1.resourceType", "text")
.Where("rf.isLock", 0)
.Where("r2.cultureTitle", "Neutral")
.Where(!Exp.In("rf.id", new List <int> {
259, 260, 261
}))
.GroupBy("rc.value", "rf.projectName")
.OrderBy("rc.value", true)
.OrderBy("rf.projectName", true);
var stat = dbManager.ExecuteList(sql);
var allStat = new List <StatisticModule>();
foreach (var culture in stat.Select(data => (string)data[1]).Distinct())
{
var cultureData = new StatisticModule {
Culture = culture
};
foreach (var project in stat.Select(data => (string)data[2]).Distinct())
{
var data = stat.Where(r => (string)r[1] == culture && (string)r[2] == project).ToList();
cultureData.Counts.Add(project, new Tuple <int, int>(data.Sum(r => Convert.ToInt32(r[0])), data.Sum(r => Convert.ToInt32(r[3]))));
}
allStat.Add(cultureData);
}
return(allStat);
}
}
public void QueryAndOr()
{
int begin = 10;
int end = 45;
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(setName);
stmt.SetFilter(Filter.Range(binName, begin, end));
// ((bin2 > 40 && bin2 < 44) || bin2 == 22 || bin2 == 9) && (binName == bin2)
QueryPolicy policy = new QueryPolicy();
policy.filterExp = Exp.Build(
Exp.And(
Exp.Or(
Exp.And(
Exp.GT(Exp.IntBin("bin2"), Exp.Val(40)),
Exp.LT(Exp.IntBin("bin2"), Exp.Val(44))),
Exp.EQ(Exp.IntBin("bin2"), Exp.Val(22)),
Exp.EQ(Exp.IntBin("bin2"), Exp.Val(9))),
Exp.EQ(Exp.IntBin(binName), Exp.IntBin("bin2"))));
RecordSet rs = client.Query(policy, stmt);
try
{
int count = 0;
while (rs.Next())
{
//Console.WriteLine(rs.Record.GetValue(binName));
count++;
}
// 22, 41, 42, 43
Assert.AreEqual(4, count);
}
finally
{
rs.Close();
}
}
private void RunQuery1(AerospikeClient client, Arguments args, string binName)
{
int begin = 10;
int end = 40;
console.Info("Query Predicate: (bin2 > 126 && bin2 <= 140) || (bin2 = 360)");
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(args.set);
// Filter applied on query itself. Filter can only reference an indexed bin.
stmt.SetFilter(Filter.Range(binName, begin, end));
// Predicates are applied on query results on server side.
// Predicates can reference any bin.
QueryPolicy policy = new QueryPolicy(client.queryPolicyDefault);
policy.filterExp = Exp.Build(
Exp.Or(
Exp.And(
Exp.GT(Exp.IntBin("bin2"), Exp.Val(126)),
Exp.LE(Exp.IntBin("bin2"), Exp.Val(140))),
Exp.EQ(Exp.IntBin("bin2"), Exp.Val(360))));
RecordSet rs = client.Query(policy, stmt);
try
{
while (rs.Next())
{
Record record = rs.Record;
console.Info("Record: " + record.ToString());
}
}
finally
{
rs.Close();
}
}
public void QueryNot()
{
int begin = 10;
int end = 45;
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(setName);
stmt.SetFilter(Filter.Range(binName, begin, end));
// ! (bin2 >= 15 && bin2 <= 42)
QueryPolicy policy = new QueryPolicy();
policy.filterExp = Exp.Build(
Exp.Not(
Exp.And(
Exp.GE(Exp.IntBin("bin2"), Exp.Val(15)),
Exp.LE(Exp.IntBin("bin2"), Exp.Val(42)))));
RecordSet rs = client.Query(policy, stmt);
try
{
int count = 0;
while (rs.Next())
{
//Console.WriteLine(rs.Record.GetValue(binName));
count++;
}
// 10, 11, 12, 13, 43, 44, 45
Assert.AreEqual(8, count);
}
finally
{
rs.Close();
}
}
public static List <StatisticUser> GetUserStatisticForLang(DateTime from, DateTime till)
{
using (var dbManager = new DbManager(Dbid))
{
var sql = new SqlQuery(ResDataTable + " as r1");
sql.SelectCount().Select(ResCultureTable + ".title", "r1.authorLogin", "sum(length(r2.textvalue))")
.InnerJoin(ResDataTable + " as r2", Exp.And(Exp.EqColumns("r1.fileID", "r2.fileID"), Exp.EqColumns("r1.title", "r2.title")))
.InnerJoin(ResCultureTable, Exp.EqColumns("r1.cultureTitle", ResCultureTable + ".title"))
.Where(!Exp.Eq("r1.flag", 4))
.Where(!Exp.Eq("r1.flag", 3))
.Where(!Exp.Eq("r1.authorLogin", "Console"))
.Where(!Exp.Eq("r1.cultureTitle", "Neutral"))
.Where(Exp.Ge("r1.timeChanges", from))
.Where(Exp.Le("r1.timeChanges", till))
.Where("r2.cultureTitle", "Neutral")
.GroupBy("title", "authorLogin")
.OrderBy("title", true)
.OrderBy("authorLogin", true);
return(dbManager.ExecuteList(sql).ConvertAll(GetUserStatisticFromDB));
}
}
private void RunQuery2(AerospikeClient client, Arguments args, string binName)
{
int begin = 10;
int end = 40;
console.Info("Query Predicate: Record updated in 2020");
DateTime beginTime = new DateTime(2020, 1, 1);
DateTime endTime = new DateTime(2021, 1, 1);
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(args.set);
stmt.SetFilter(Filter.Range(binName, begin, end));
QueryPolicy policy = new QueryPolicy(client.queryPolicyDefault);
policy.filterExp = Exp.Build(
Exp.And(
Exp.GE(Exp.LastUpdate(), Exp.Val(beginTime)),
Exp.LT(Exp.LastUpdate(), Exp.Val(endTime))));
RecordSet rs = client.Query(policy, stmt);
try
{
while (rs.Next())
{
Record record = rs.Record;
console.Info("Record: " + record.ToString());
}
}
finally
{
rs.Close();
}
}
public void DeleteMessageAttachments(int tenant, string user, int messageId, List <int> attachmentIds)
{
var ids = attachmentIds.ToArray();
long usedQuota;
using (var db = GetDb())
{
using (var tx = db.BeginTransaction(IsolationLevel.ReadUncommitted))
{
usedQuota = MarkAttachmetsNeedRemove(db, tenant,
Exp.And(Exp.Eq(AttachmentTable.Columns.MailId, messageId),
Exp.In(AttachmentTable.Columns.Id, ids)));
ReCountAttachments(db, messageId);
UpdateMessageChainAttachmentsFlag(db, tenant, user, messageId);
tx.Commit();
}
}
if (usedQuota > 0)
{
QuotaUsedDelete(tenant, usedQuota);
}
}
public IEnumerable <Tenant> GetTenants(List <int> ids)
{
return(GetTenants(Exp.And(Exp.In("t.id", ids), Exp.Eq("t.status", (int)TenantStatus.Active))));
}
public virtual Exp GetExpression()
{
var filterExp = Exp.Empty;
if (!FactoryIndexer <MailWrapper> .Support)
{
Func <ConditionKeyType, string> toDbField = c =>
{
switch (c)
{
case ConditionKeyType.From:
return(MailTable.Columns.From.Prefix(MM_ALIAS));
case ConditionKeyType.To:
return(MailTable.Columns.To.Prefix(MM_ALIAS));
case ConditionKeyType.Cc:
return(MailTable.Columns.Cc.Prefix(MM_ALIAS));
case ConditionKeyType.Subject:
return(MailTable.Columns.Subject.Prefix(MM_ALIAS));
default:
throw new ArgumentOutOfRangeException("c", c, null);
}
};
Func <MailSieveFilterConditionData, Exp> getConditionExp = c =>
{
var e = Exp.Empty;
switch (c.Operation)
{
case ConditionOperationType.Matches:
e = c.Key == ConditionKeyType.ToOrCc
? Exp.Or(Exp.Eq(MailTable.Columns.To.Prefix(MM_ALIAS), c.Value),
Exp.Eq(MailTable.Columns.Cc.Prefix(MM_ALIAS), c.Value))
: Exp.Eq(toDbField(c.Key), c.Value);
break;
case ConditionOperationType.Contains:
e = c.Key == ConditionKeyType.ToOrCc
? Exp.Or(Exp.Like(MailTable.Columns.To.Prefix(MM_ALIAS), c.Value, SqlLike.AnyWhere),
Exp.Like(MailTable.Columns.Cc.Prefix(MM_ALIAS), c.Value, SqlLike.AnyWhere))
: Exp.Like(toDbField(c.Key), c.Value, SqlLike.AnyWhere);
break;
case ConditionOperationType.NotMatches:
e = c.Key == ConditionKeyType.ToOrCc
? !Exp.And(Exp.Eq(MailTable.Columns.To.Prefix(MM_ALIAS), c.Value),
Exp.Eq(MailTable.Columns.Cc.Prefix(MM_ALIAS), c.Value))
: !Exp.Eq(toDbField(c.Key), c.Value);
break;
case ConditionOperationType.NotContains:
e = c.Key == ConditionKeyType.ToOrCc
? Exp.And(!Exp.Like(MailTable.Columns.To.Prefix(MM_ALIAS), c.Value, SqlLike.AnyWhere),
!Exp.Like(MailTable.Columns.Cc.Prefix(MM_ALIAS), c.Value, SqlLike.AnyWhere))
: !Exp.Like(toDbField(c.Key), c.Value, SqlLike.AnyWhere);
break;
}
return(e);
};
if (Filter.Conditions != null && Filter.Conditions.Any())
{
var cExp = Exp.Empty;
foreach (var c in Filter.Conditions)
{
switch (Filter.Options.MatchMultiConditions)
{
case MatchMultiConditionsType.MatchAll:
case MatchMultiConditionsType.None:
cExp &= getConditionExp(c);
break;
case MatchMultiConditionsType.MatchAtLeastOne:
cExp |= getConditionExp(c);
break;
}
}
filterExp &= cExp;
}
}
if (Ids != null && Ids.Any())
{
filterExp &= Exp.In(MailTable.Columns.Id.Prefix(MM_ALIAS), Ids);
}
if (Filter.Options.ApplyTo.Folders.Any())
{
filterExp &= Exp.In(MailTable.Columns.Folder.Prefix(MM_ALIAS), Filter.Options.ApplyTo.Folders);
}
if (Filter.Options.ApplyTo.Mailboxes.Any())
{
filterExp &= Exp.In(MailTable.Columns.MailboxId.Prefix(MM_ALIAS), Filter.Options.ApplyTo.Mailboxes);
}
switch (Filter.Options.ApplyTo.WithAttachments)
{
case ApplyToAttachmentsType.WithAttachments:
filterExp &= Exp.Gt(MailTable.Columns.AttachCount.Prefix(MM_ALIAS), 0);
break;
case ApplyToAttachmentsType.WithoutAttachments:
filterExp &= Exp.Eq(MailTable.Columns.AttachCount.Prefix(MM_ALIAS), 0);
break;
case ApplyToAttachmentsType.WithAndWithoutAttachments:
break;
default:
throw new ArgumentOutOfRangeException();
}
var exp = Exp.Eq(MailTable.Columns.Tenant.Prefix(MM_ALIAS), Tenant) &
Exp.Eq(MailTable.Columns.User.Prefix(MM_ALIAS), User) &
Exp.Eq(MailTable.Columns.IsRemoved.Prefix(MM_ALIAS), false);
exp &= filterExp;
return(exp);
}
public void SetConversationsImportanceFlags(int tenant, string user, bool important, List <int> ids)
{
List <MailInfo> mailInfos;
using (var daoFactory = new DaoFactory())
{
mailInfos = GetChainedMessagesInfo(daoFactory, ids);
var chainsInfo = mailInfos
.Select(m => new
{
m.ChainId,
m.MailboxId,
m.Folder
})
.Distinct().ToList();
if (!chainsInfo.Any())
{
throw new Exception("no chain messages belong to current user");
}
using (var tx = daoFactory.DbManager.BeginTransaction(IsolationLevel.ReadUncommitted))
{
var daoMailInfo = daoFactory.CreateMailInfoDao(tenant, user);
var exp = Exp.Empty;
var сhains = new List <Tuple <int, string> >();
foreach (var chain in chainsInfo)
{
var key = new Tuple <int, string>(chain.MailboxId, chain.ChainId);
if (сhains.Any() &&
сhains.Contains(key) &&
(chain.Folder == FolderType.Inbox || chain.Folder == FolderType.Sent))
{
continue;
}
var innerWhere = Exp.And(
Exp.Eq(MailTable.Columns.ChainId.Prefix(MM_ALIAS), chain.ChainId),
Exp.Eq(MailTable.Columns.MailboxId.Prefix(MM_ALIAS), chain.MailboxId));
if (chain.Folder == FolderType.Inbox || chain.Folder == FolderType.Sent)
{
innerWhere &= Exp.Or(
Exp.Eq(MailTable.Columns.Folder.Prefix(MM_ALIAS), (int)FolderType.Inbox),
Exp.Eq(MailTable.Columns.Folder.Prefix(MM_ALIAS), (int)FolderType.Sent));
сhains.Add(key);
}
else
{
innerWhere &= Exp.Eq(MailTable.Columns.Folder.Prefix(MM_ALIAS), (int)chain.Folder);
}
exp |= innerWhere;
}
daoMailInfo.SetFieldValue(
SimpleMessagesExp.CreateBuilder(tenant, user)
.SetExp(exp)
.Build(),
MailTable.Columns.Importance,
important);
var daoChain = daoFactory.CreateChainDao(tenant, user);
foreach (var chain in chainsInfo)
{
daoChain.SetFieldValue(
SimpleConversationsExp.CreateBuilder(tenant, user)
.SetChainId(chain.ChainId)
.SetMailboxId(chain.MailboxId)
.SetFolder((int)chain.Folder)
.Build(),
ChainTable.Columns.Importance,
important);
}
tx.Commit();
}
}
var factory = new EngineFactory(Tenant, User);
var data = new MailWrapper
{
Importance = important
};
factory.IndexEngine.Update(data, s => s.In(m => m.Id, mailInfos.Select(o => o.Id).ToArray()),
wrapper => wrapper.Importance);
}
public int[] GetPremiumTenants()
{
/*
* // v_premium_tenants view:
* select
* t.tenant,
* (now() < max(t.stamp)) AS premium
*
* from tenants_tariff t
* left join tenants_quota q ON (t.tariff = q.tenant)
*
* where
* (
* (
* isnull(t.comment) or
* (
* (not((t.comment like '%non-profit%'))) and
* (not((t.comment like '%test%'))) and
* (not((t.comment like '%translate%'))) and
* (not((t.comment like '%trial%')))
* )
* ) and
* (not((q.features like '%free%'))) and
* (not((q.features like '%non-profit%'))) and
* (not((q.features like '%trial%')))
* )
* group by t.tenant
*/
var result = cache.Get <int[]>(cacheKey);
if (result != null)
{
return(result);
}
var nullTariffComment = Exp.Eq("t.comment", null);
var excludeTariffComments = new List <string> {
"non-profit", "test", "translate", "trial"
}
.Aggregate(Exp.Empty, (current, item) => Exp.And(current, !Exp.Like("t.comment", item, SqlLike.AnyWhere)));
var excludeQuotaFeatures = new List <string> {
"free", "non-profit", "trial"
}
.Aggregate(Exp.Empty, (current, item) => Exp.And(current, !Exp.Like("q.features", item, SqlLike.AnyWhere)));
var search = new SqlQuery("tenants_tariff t")
.LeftOuterJoin("tenants_quota q", Exp.EqColumns("t.tariff", "q.tenant"))
.Select("t.tenant", "(NOW() < MAX(t.stamp)) AS premium")
.Where(
Exp.And(
Exp.Or(
nullTariffComment,
excludeTariffComments
),
excludeQuotaFeatures
)
)
.GroupBy("t.tenant");
using (var db = new DbManager(config.ConnectionStringName))
{
result = db.ExecuteList(search)
.Where(row => Convert.ToBoolean(row[1]))
.Select(row => Convert.ToInt32(row[0]))
.ToArray();
cache.Insert(cacheKey, result, DateTime.UtcNow.AddHours(1));
return(result);
}
}