public AddUserToDatabaseStep(IDbManager dbManager, string databaseName, string usermane)
{
_dbManager = dbManager;
_username = usermane;
_databaseName = databaseName;
}
public AddRoleToUserStep(IDbManager dbManager, string databaseName, string username, string roleName)
{
_dbManager = dbManager;
_databaseName = databaseName;
_username = username;
_dbRole = roleName;
}
private static TenantServerDto GetFreeServer(IDbManager db)
{
if (db == null)
throw new ArgumentNullException("db");
TenantServerDto serverDto;
var serversQuery = new SqlQuery(ServerTable.name)
.Select(ServerTable.Columns.id)
.Select(ServerTable.Columns.connection_string)
.Select(ServerTable.Columns.mx_record)
.Select(ServerTable.Columns.server_type)
.Select(ServerTable.Columns.smtp_settings_id)
.Select(ServerTable.Columns.imap_settings_id)
.Where(Exp.Gt(ServerTable.Columns.server_type, 0));
var servers = db.ExecuteList(serversQuery)
.Select(r => r.ToTenantServerDto())
.ToList();
if (servers.Count > 1)
{
const string mst_alias = "mst";
const string mb_alias = "mb";
var serverXmailboxes = new Dictionary<int, int>();
foreach (var tenantServerDto in servers)
{
var serverMailboxesQuery = new SqlQuery(TenantXServerTable.name.Alias(mst_alias))
.InnerJoin(MailboxTable.name.Alias(mb_alias),
Exp.EqColumns(TenantXServerTable.Columns.id_tenant.Prefix(mst_alias),
MailboxTable.Columns.id_tenant.Prefix(mb_alias)))
.SelectCount()
.Where(MailboxTable.Columns.is_teamlab_mailbox.Prefix(mb_alias), true)
.Where(MailboxTable.Columns.is_removed.Prefix(mb_alias), false)
.Where(TenantXServerTable.Columns.id_server.Prefix(mst_alias), tenantServerDto.id)
.GroupBy(TenantXServerTable.Columns.id_server.Prefix(mst_alias));
var count = db.ExecuteScalar<int>(serverMailboxesQuery);
serverXmailboxes.Add(tenantServerDto.id, count);
}
var lightestServerId = serverXmailboxes.Aggregate((l, r) => l.Value < r.Value ? l : r).Key;
serverDto =
servers.First(s => s.id == lightestServerId);
}
else
{
serverDto = servers.FirstOrDefault();
}
return serverDto;
}
public ItemCategoryDAOImpl(IDbProxyContext dbContext, IDbManager manager, ILogger logger)
{
if (dbContext == null) throw new ArgumentNullException("dbContext");
if (manager == null) throw new ArgumentNullException("manager");
if (logger == null) throw new ArgumentNullException("logger");
_dbContext = dbContext;
_manager = manager;
_logger = logger;
}
public static int MailContactExists(IDbManager db, int tenant, string user_id, string name, string address)
{
return db.ExecuteScalar<int>(
new SqlQuery(ContactsTable.name)
.Select(ContactsTable.Columns.id)
.Where(ContactsTable.Columns.id_user, user_id)
.Where(ContactsTable.Columns.id_tenant, tenant)
.Where(ContactsTable.Columns.name, name)
.Where(ContactsTable.Columns.address, address));
}
public int MailContactExists(IDbManager db, int tenant, string user_id, string name, string address)
{
return db.ExecuteScalar<int>(
new SqlQuery(MAIL_CONTACTS)
.Select(MailContactsFields.id)
.Where(MailContactsFields.id_user, user_id)
.Where(MailContactsFields.id_tenant, tenant)
.Where(MailContactsFields.name, name)
.Where(MailContactsFields.address, address));
}
public void SetUp()
{
try
{
Database = new PgDbManager {Description = new PgDbDescription {XmlRoot = Resources.TestDbDescription}};
Database.Create();
}
catch (Exception e)
{
Debug.WriteLine("GlobalTest.Setup : {0} : {1}", e.GetType(), e.Message);
throw;
}
}
public void SetUp()
{
try
{
Database = new PgDbManager(new PgDbDescription(XElement.Parse(Resources.TestDbDescription)));
Database.Create();
}
catch (Exception e)
{
Debug.WriteLine("GlobalTest.Setup : {0} : {1}", e.GetType(), e.Message);
throw;
}
}
public void SetUp()
{
try
{
Logger = NLog.LogManager.GetCurrentClassLogger();
MicrOrmLogger.Enabled = true;
TestDb = new PgDbManager {Description = new PgDbDescription { XmlRoot = Resources.Test }};
TestDb.Create();
ConnectionProvider = new MicrOrmConnectionProvider(TestDb.Description.ConnectionInfo);
}
catch (Exception e)
{
Logger.Fatal(e, "SetUp : {0} : {1}", e.GetType(), e.Message);
throw;
}
}
public static void SaveMailContacts(IDbManager db, int tenant, string user, Message message, ILogger log)
{
try
{
var contacts = new AddressCollection {message.From};
contacts.AddRange(message.To);
contacts.AddRange(message.Cc);
contacts.AddRange(message.Bcc);
var valid_contacts = (from contact in contacts
where MailContactExists(db, tenant, user, contact.Name, contact.Email) < 1
select contact).ToList();
if (!valid_contacts.Any()) return;
var last_modified = DateTime.Now;
var insert_query = new SqlInsert(ContactsTable.name)
.InColumns(ContactsTable.Columns.id_user,
ContactsTable.Columns.id_tenant,
ContactsTable.Columns.name,
ContactsTable.Columns.address,
ContactsTable.Columns.last_modified);
valid_contacts
.ForEach(contact =>
insert_query
.Values(user, tenant, contact.Name, contact.Email, last_modified));
db.ExecuteNonQuery(insert_query);
}
catch (Exception e)
{
log.Warn("SaveMailContacts(tenant={0}, userId='{1}', mail_id={2}) Exception:\r\n{3}\r\n",
tenant, user, message.Id, e.ToString());
}
}
private void RecalculateFolders(IDbManager db, int tenant, string user)
{
var folderIds = new[]
{
MailFolder.Ids.temp,
MailFolder.Ids.inbox,
MailFolder.Ids.sent,
MailFolder.Ids.drafts,
MailFolder.Ids.trash,
MailFolder.Ids.spam
};
var unreadMessagesCountByFolder =
db.ExecuteList(
new SqlQuery(MailTable.name)
.Select(MailTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.unread, true)
.Where(MailTable.Columns.is_removed, false)
.GroupBy(MailTable.Columns.folder))
.ConvertAll(
x =>
new KeyValuePair <int, int>(
Convert.ToInt32(x[0]),
Convert.ToInt32(x[1])));
var totalMessagesCountByFolder =
db.ExecuteList(
new SqlQuery(MailTable.name)
.Select(MailTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.is_removed, false)
.GroupBy(MailTable.Columns.folder))
.ConvertAll(
x =>
new KeyValuePair <int, int>(Convert.ToInt32(x[0]),
Convert.ToInt32(x[1])));
var unreadConversationsCountByFolder =
db.ExecuteList(
new SqlQuery(ChainTable.name)
.Select(ChainTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(ChainTable.Columns.unread, true)
.GroupBy(ChainTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair <int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
var totalConversationsCountByFolder =
db.ExecuteList(
new SqlQuery(ChainTable.name)
.Select(ChainTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.GroupBy(ChainTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair <int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
var foldersInfo = from folderId in folderIds
let unreadMessCount = unreadMessagesCountByFolder.Find(c => c.Key == folderId).Value
let totalMessCount = totalMessagesCountByFolder.Find(c => c.Key == folderId).Value
let unreadConvCount = unreadConversationsCountByFolder.Find(c => c.Key == folderId).Value
let totalConvCount = totalConversationsCountByFolder.Find(c => c.Key == folderId).Value
select new
{
id = folderId,
unread_messages_count = unreadMessCount,
total_messages_count = totalMessCount,
unread_conversations_count = unreadConvCount,
total_conversations_count = totalConvCount,
time_modified = DateTime.UtcNow
};
foreach (var info in foldersInfo)
{
db.ExecuteNonQuery(
new SqlInsert(FolderTable.name, true)
.InColumnValue(FolderTable.Columns.id_tenant, tenant)
.InColumnValue(FolderTable.Columns.id_user, user)
.InColumnValue(FolderTable.Columns.folder, info.id)
.InColumnValue(FolderTable.Columns.unread_messages_count, info.unread_messages_count)
.InColumnValue(FolderTable.Columns.unread_conversations_count, info.unread_conversations_count)
.InColumnValue(FolderTable.Columns.total_messages_count, info.total_messages_count)
.InColumnValue(FolderTable.Columns.total_conversations_count, info.total_conversations_count));
}
}
public MailboxDao(IDbManager dbManager) :
base(table, dbManager, -1)
{
}
public TroopManager(IDbManager dbManager)
{
this.dbManager = dbManager;
}
private void ChangeFolderCounters(IDbManager db, int tenant, string user, int folder, int unreadDiff,
int totalDiff, bool isConversation)
{
var res = 0;
if (unreadDiff != 0 || totalDiff != 0)
{
var updateQuery = new SqlUpdate(FolderTable.name)
.Where(GetUserWhere(user, tenant))
.Where(FolderTable.Columns.folder, folder);
if (unreadDiff != 0)
{
if (isConversation)
updateQuery.Set(FolderTable.Columns.unread_conversations_count + "=" +
FolderTable.Columns.unread_conversations_count + "+(" + unreadDiff + ")");
else
updateQuery.Set(FolderTable.Columns.unread_messages_count + "=" + FolderTable.Columns.unread_messages_count +
"+(" + unreadDiff + ")");
}
if (totalDiff != 0)
{
if (isConversation)
updateQuery.Set(FolderTable.Columns.total_conversations_count + "=" +
FolderTable.Columns.total_conversations_count + "+(" + totalDiff + ")");
else
{
updateQuery.Set(FolderTable.Columns.total_messages_count + "=" +
FolderTable.Columns.total_messages_count + "+(" + totalDiff + ")");
}
}
res = db.ExecuteNonQuery(updateQuery);
}
if (0 == res)
RecalculateFolders(db, tenant, user);
}
public ServerAddressDao(IDbManager dbManager, int tenant)
: base(table, dbManager, tenant)
{
}
public int CreateAuthErrorWarningAlert(IDbManager db, int tenant, string user, int mailboxId)
{
return(CreateUniqueAlert(db, tenant, user, mailboxId, AlertTypes.AuthConnectFailure));
}
public List<MailAlert> FindAlerts(IDbManager db, int tenant, string user, int mailboxId = -1, AlertTypes type = AlertTypes.Empty)
{
var searchQuery = new SqlQuery(MailAlertsTable.name)
.Select(MailAlertsTable.Columns.id, MailAlertsTable.Columns.type, MailAlertsTable.Columns.id_mailbox, MailAlertsTable.Columns.data)
.Where(GetUserWhere(user, tenant));
if (mailboxId > 0)
searchQuery.Where(MailAlertsTable.Columns.id_mailbox, mailboxId);
if (type != AlertTypes.Empty)
searchQuery.Where(MailAlertsTable.Columns.type, (int)type);
var foundAlerts = db.ExecuteList(searchQuery)
.ConvertAll(x => new MailAlert
{
id = Convert.ToInt32(x[0]),
type = (AlertTypes)x[1],
id_mailbox = Convert.ToInt32(x[2]),
data = (string)x[3]
});
return foundAlerts;
}
public AuthenticateCommandHandler(IDbManager dbManager)
{
_dbManager = dbManager;
}
public MailboxAutoreplyHistoryDao(IDbManager dbManager, int tenant, string user)
: base(table, dbManager, tenant)
{
CurrentUserId = user;
}
public override void Init(IDbManager db)
{
_db = db;
}
public MovieViewModel(IDbManager db)
{
Init(db);
AddCommand = new RelayCommand(AddCommand_Execute, AddCommand_CanExecute);
}
public Task <IDbCommand> GetDbCommandWithAssignedParametersAsync(IDbManager dbManager, CancellationToken cancellationToken)
{
throw new System.NotImplementedException();
}
protected BaseDao(ITable table, IDbManager dbManager)
{
Db = dbManager;
Table = table;
}
public int CreateQuotaErrorWarningAlert(int tenant, string user, IDbManager db = null)
{
return(db == null?CreateUniqueAlert(tenant, user, -1, AlertTypes.QuotaError) :
CreateUniqueAlert(db, tenant, user, -1, AlertTypes.QuotaError));
}
public int CreateQuotaErrorWarningAlert(IDbManager db, int tenant, string user)
{
return CreateUniqueAlert(db, tenant, user, -1, AlertTypes.QuotaError);
}
public MailInfoDao(IDbManager dbManager, int tenant, string user)
{
Db = dbManager;
Tenant = tenant;
User = user;
}
private int CreateUniqueAlert(IDbManager db, int tenant, string user, int mailboxId, AlertTypes type, object data = null)
{
var alerts = FindAlerts(db, tenant, user, mailboxId, type);
if (alerts.Any())
return -1;
var jsonData = MailUtil.GetJsonString(data);
var insertQuery = new SqlInsert(MailAlertsTable.name)
.InColumnValue(MailAlertsTable.Columns.id_tenant, tenant)
.InColumnValue(MailAlertsTable.Columns.id_user, user)
.InColumnValue(MailAlertsTable.Columns.id_mailbox, mailboxId)
.InColumnValue(MailAlertsTable.Columns.type, (int)type)
.InColumnValue(MailAlertsTable.Columns.data, jsonData);
return db.ExecuteNonQuery(insertQuery);
}
public CloseSnapshot(IMarketFeed marketFeed, IDbManager dbManager)
{
_marketFeed = marketFeed;
_dbManager = dbManager;
_instrumentPriceSafeDico = new ConcurrentDictionary <string, double>();
}
private void RecalculateFolders(IDbManager db, int tenant, string user)
{
var folderIds = new[]
{
MailFolder.Ids.temp,
MailFolder.Ids.inbox,
MailFolder.Ids.sent,
MailFolder.Ids.drafts,
MailFolder.Ids.trash,
MailFolder.Ids.spam
};
var unreadMessagesCountByFolder =
db.ExecuteList(
new SqlQuery(MailTable.name)
.Select(MailTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.unread, true)
.Where(MailTable.Columns.is_removed, false)
.GroupBy(MailTable.Columns.folder))
.ConvertAll(
x =>
new KeyValuePair<int, int>(
Convert.ToInt32(x[0]),
Convert.ToInt32(x[1])));
var totalMessagesCountByFolder =
db.ExecuteList(
new SqlQuery(MailTable.name)
.Select(MailTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.is_removed, false)
.GroupBy(MailTable.Columns.folder))
.ConvertAll(
x =>
new KeyValuePair<int, int>(Convert.ToInt32(x[0]),
Convert.ToInt32(x[1])));
var unreadConversationsCountByFolder =
db.ExecuteList(
new SqlQuery(ChainTable.name)
.Select(ChainTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(ChainTable.Columns.unread, true)
.GroupBy(ChainTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair<int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
var totalConversationsCountByFolder =
db.ExecuteList(
new SqlQuery(ChainTable.name)
.Select(ChainTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.GroupBy(ChainTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair<int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
var foldersInfo = from folderId in folderIds
let unreadMessCount = unreadMessagesCountByFolder.Find(c => c.Key == folderId).Value
let totalMessCount = totalMessagesCountByFolder.Find(c => c.Key == folderId).Value
let unreadConvCount = unreadConversationsCountByFolder.Find(c => c.Key == folderId).Value
let totalConvCount = totalConversationsCountByFolder.Find(c => c.Key == folderId).Value
select new
{
id = folderId,
unread_messages_count = unreadMessCount,
total_messages_count = totalMessCount,
unread_conversations_count = unreadConvCount,
total_conversations_count = totalConvCount,
time_modified = DateTime.UtcNow
};
foreach (var info in foldersInfo)
{
db.ExecuteNonQuery(
new SqlInsert(FolderTable.name, true)
.InColumnValue(FolderTable.Columns.id_tenant, tenant)
.InColumnValue(FolderTable.Columns.id_user, user)
.InColumnValue(FolderTable.Columns.folder, info.id)
.InColumnValue(FolderTable.Columns.unread_messages_count, info.unread_messages_count)
.InColumnValue(FolderTable.Columns.unread_conversations_count, info.unread_conversations_count)
.InColumnValue(FolderTable.Columns.total_messages_count, info.total_messages_count)
.InColumnValue(FolderTable.Columns.total_conversations_count, info.total_conversations_count));
}
}
private void UpdateMessageChainAttachmentsFlag(IDbManager db, int tenant, string user, int message_id)
{
UpdateMessageChainFlag(db, tenant, user, message_id, MailTable.Columns.attach_count, ChainTable.Columns.has_attachments);
}
public UserFolderTreeDao(IDbManager dbManager, int tenant, string user)
: base(table, dbManager, tenant)
{
CurrentUserId = user;
}
private void UpdateMessageChainImportanceFlag(IDbManager db, int tenant, string user, int message_id)
{
UpdateMessageChainFlag(db, tenant, user, message_id, MailTable.Columns.importance, ChainTable.Columns.importance);
}
public ProcessResultRepository(IDbManager DbManager)
{
_DbManager = DbManager;
}
private ChainInfo GetMessageChainInfo(IDbManager db, int tenant, string user, int message_id)
{
var info = db.ExecuteList(new SqlQuery(MailTable.name)
.Select(MailTable.Columns.chain_id, MailTable.Columns.folder, MailTable.Columns.id_mailbox)
.Where(MailTable.Columns.id, message_id)
.Where(GetUserWhere(user, tenant)));
return new ChainInfo
{
id = info[0][0] == null ? "" : info[0][0].ToString(),
folder = Convert.ToInt32(info[0][1]),
mailbox = Convert.ToInt32(info[0][2])
};
}
public DisplayImagesAddressDao(IDbManager dbManager, int tenant, string user)
: base(table, dbManager, tenant)
{
CurrentUserId = user;
}
private List<MailTag> GetMailTags(IDbManager db, int id_tenant, string id_user, Exp exp)
{
return db.ExecuteList(
new SqlQuery(MAIL_TAG)
.Select(TagFields.id, TagFields.name, TagFields.style, TagFields.addresses)
.Where(GetUserWhere(id_user, id_tenant))
.Where(exp))
.ConvertAll(r =>
new MailTag(Convert.ToInt32(r[0]), (string)r[1],
r[3] == null ? null : r[3].ToString().Split(';'), ConvertToString(r[2]), 0));
}
private List <MailFolderInfo> RecalculateFolders(IDbManager db, int tenant, string user, bool isConversation)
{
List <KeyValuePair <int, int> > unreadCount;
List <KeyValuePair <int, int> > totalCount;
var folders = new List <MailFolderInfo>();
var folderIds = new[]
{
MailFolder.Ids.temp,
MailFolder.Ids.inbox,
MailFolder.Ids.sent,
MailFolder.Ids.drafts,
MailFolder.Ids.trash,
MailFolder.Ids.spam
};
if (!isConversation)
{
unreadCount = db.ExecuteList(
new SqlQuery(MailTable.name)
.Select(MailTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.unread, true)
.Where(MailTable.Columns.is_removed, false)
.GroupBy(MailTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair <int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
totalCount = db.ExecuteList(
new SqlQuery(MailTable.name)
.Select(MailTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.is_removed, false)
.GroupBy(MailTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair <int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
}
else
{
unreadCount = db.ExecuteList(
new SqlQuery(ChainTable.name)
.Select(ChainTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(ChainTable.Columns.unread, true)
.GroupBy(ChainTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair <int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
totalCount = db.ExecuteList(
new SqlQuery(ChainTable.name)
.Select(ChainTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.GroupBy(ChainTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair <int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
}
foreach (var folder in
from folderId in folderIds
let unread = unreadCount.Find(c => c.Key == folderId).Value
let total = totalCount.Find(c => c.Key == folderId).Value
select new MailFolderInfo
{
id = folderId,
unread = unread,
total_count = total,
time_modified = DateTime.UtcNow
})
{
folders.Add(folder);
var upsertQuery =
string.Format("INSERT INTO {0} ({1}, {2}, {3}, {4}, {5})" +
"VALUES(@tid, @user, @folder, @count1, @count2)" +
"ON DUPLICATE KEY UPDATE {4} = @count1, {5} = @count2",
FolderTable.name,
FolderTable.Columns.id_tenant,
FolderTable.Columns.id_user,
FolderTable.Columns.folder,
isConversation
? FolderTable.Columns.unread_conversations_count
: FolderTable.Columns.unread_messages_count,
isConversation
? FolderTable.Columns.total_conversations_count
: FolderTable.Columns.total_messages_count);
db.ExecuteNonQuery(upsertQuery, new { tid = tenant, user, folder = folder.id, count1 = folder.unread, count2 = folder.total_count });
}
return(folders);
}
public int CreateAuthErrorWarningAlert(IDbManager db, int tenant, string user, int mailboxId)
{
return CreateUniqueAlert(db, tenant, user, mailboxId, AlertTypes.AuthConnectFailure);
}
public ActionWorker(Func <ILockable> lockDelegate, ILocation location, ILocker locker, IScheduler scheduler, IDbManager dbManager, RequirementFormula requirementFormula)
{
this.locker = locker;
this.scheduler = scheduler;
this.dbManager = dbManager;
this.requirementFormula = requirementFormula;
Location = location;
LockDelegate = lockDelegate;
}
public FilterDao(IDbManager dbManager, int tenant, string user)
: base(table, dbManager, tenant)
{
CurrentUserId = user;
}
private void UpdateMessageChainUnreadFlag(IDbManager db, int tenant, string user, int message_id)
{
UpdateMessageChainFlag(db, tenant, user, message_id, MailTable.Columns.unread, ChainTable.Columns.unread);
}
public EmployeeService(IDbManager dbManager, IEnvironmentVariables environmentVariables)
{
_dbManager = dbManager;
_environmentVariables = environmentVariables;
}
private void UpdateMessageChainFlag(IDbManager db, int tenant, string user, int message_id, string field_from, string field_to)
{
var chain = GetMessageChainInfo(db, tenant, user, message_id);
if (string.IsNullOrEmpty(chain.id) || chain.folder < 1 || chain.mailbox < 1) return;
var field_query = new SqlQuery(MailTable.name)
.SelectMax(field_from)
.Where(MailTable.Columns.is_removed, 0)
.Where(MailTable.Columns.chain_id, chain.id)
.Where(MailTable.Columns.id_mailbox, chain.mailbox)
.Where(GetUserWhere(user, tenant))
.Where(GetSearchFolders(MailTable.Columns.folder, chain.folder));
var field_val = db.ExecuteScalar<bool>(field_query);
var update_query = new SqlUpdate(ChainTable.name)
.Set(field_to, field_val)
.Where(GetUserWhere(user, tenant))
.Where(ChainTable.Columns.id, chain.id)
.Where(ChainTable.Columns.id_mailbox, chain.mailbox)
.Where(GetSearchFolders(ChainTable.Columns.folder, chain.folder));
db.ExecuteNonQuery(update_query);
}
public Forest(uint id, int capacity, uint x, uint y, IActionFactory actionFactory, IScheduler scheduler, IDbManager dbManager, Formula formula)
: base(id, x, y)
{
this.actionFactory = actionFactory;
this.scheduler = scheduler;
this.dbManager = dbManager;
this.formula = formula;
Wood = new AggressiveLazyValue(capacity)
{
Limit = capacity
};
}
private string GetChainTags(IDbManager db, string chain_id, int folder, int id_mailbox, int tenant,
string user_id)
{
const string mm_alias = "ch";
const string mtm_alias = "tm";
var mail_query = new SqlQuery(MailTable.name)
.Select(MailTable.Columns.id)
.Where(MailTable.Columns.chain_id, chain_id)
.Where(MailTable.Columns.is_removed, 0)
.Where(GetUserWhere(user_id, tenant))
.Where(MailTable.Columns.folder, folder)
.Where(MailTable.Columns.id_mailbox, id_mailbox);
var new_query = new SqlQuery(MAIL_TAG_MAIL + " " + mtm_alias)
.Select(TagMailFields.id_tag.Prefix(mtm_alias))
.Distinct()
.InnerJoin(mail_query, mm_alias,
Exp.EqColumns(MailTable.Columns.id.Prefix(mm_alias), TagMailFields.id_mail.Prefix(mtm_alias)))
.OrderBy(TagMailFields.time_created.Prefix(mtm_alias), true);
var tags = db.ExecuteList(new_query)
.ConvertAll(x => x[0].ToString());
return String.Join(",", tags.ToArray());
}
private void RecalculateFilesCount(IDbManager db, object folderId)
{
db.ExecuteNonQuery(GetRecalculateFilesCountUpdate(folderId));
}
private void ChangeFolderCounters(
IDbManager db,
int tenant,
string user,
int folder,
int unread_mess_diff,
int total_mess_diff,
int unread_conv_diff,
int total_conv_diff)
{
if (0 == unread_mess_diff && 0 == total_mess_diff && 0 == unread_conv_diff && 0 == total_conv_diff)
return;
var update_query = new SqlUpdate(MAIL_FOLDER)
.Where(GetUserWhere(user, tenant))
.Where(FolderFields.folder, folder);
if (0 != unread_mess_diff)
update_query.Set(FolderFields.unread_messages_count + "=" +
FolderFields.unread_messages_count + "+(" + unread_mess_diff + ")");
if (0 != total_mess_diff)
update_query.Set(FolderFields.total_messages_count + "=" +
FolderFields.total_messages_count + "+(" + total_mess_diff + ")");
if (0 != unread_conv_diff)
update_query.Set(FolderFields.unread_conversations_count + "=" +
FolderFields.unread_conversations_count + "+(" + unread_conv_diff + ")");
if (0 != total_conv_diff)
update_query.Set(FolderFields.total_conversations_count + "=" +
FolderFields.total_conversations_count + "+(" + total_conv_diff + ")");
if (0 == db.ExecuteNonQuery(update_query))
RecalculateFolders(db, tenant, user);
}
public NotificationManager(IActionWorker worker, IDbManager dbManager)
{
this.dbManager = dbManager;
worker.ActionRescheduled += WorkerActionRescheduled;
worker.ActionRemoved += WorkerOnActionRemoved;
}
public int CreateAuthErrorDisableAlert(IDbManager db, int tenant, string user, int mailboxId)
{
var alerts = FindAlerts(db, tenant, user, mailboxId, AlertTypes.AuthConnectFailure);
if (alerts.Any())
DeleteAlerts(db, tenant, user, alerts.Select(alert => alert.id).ToList());
return CreateUniqueAlert(db, tenant, user, mailboxId, AlertTypes.TooManyAuthError);
}
public ContactInfoDao(IDbManager dbManager, int tenant, string user)
: base(table, dbManager, tenant)
{
CurrentUserId = user;
}
public void DeleteAlerts(IDbManager db, int tenant, string user, List<long> alertIds)
{
db.ExecuteNonQuery(
new SqlDelete(MailAlertsTable.name)
.Where(GetUserWhere(user, tenant))
.Where(Exp.In(MailAlertsTable.Columns.id, alertIds)));
}
public OrderManager(IDbManager dbManager)
{
this.dbManager = dbManager;
employeeManager = new EmployeeManager(dbManager as DbManager);
}
public MailAlert GetAlert(IDbManager db, int tenant, string user, long alertId)
{
var searchQuery = new SqlQuery(MailAlertsTable.name)
.Select(MailAlertsTable.Columns.id, MailAlertsTable.Columns.type, MailAlertsTable.Columns.id_mailbox, MailAlertsTable.Columns.data)
.Where(MailAlertsTable.Columns.id, alertId)
.Where(GetUserWhere(user, tenant));
var foundAlert = db.ExecuteList(searchQuery)
.ConvertAll(x => new MailAlert
{
id = Convert.ToInt32(x[0]),
type = (AlertTypes)x[1],
id_mailbox = Convert.ToInt32(x[2]),
data = (string)x[3]
}).FirstOrDefault();
return foundAlert;
}
private void ChangeFolderCounters(
IDbManager db,
int tenant,
string user,
int folder,
int?unreadMessDiff = null,
int?totalMessDiff = null,
int?unreadConvDiff = null,
int?totalConvDiff = null)
{
if (!(unreadMessDiff.HasValue && unreadMessDiff.Value != 0) &&
!(totalMessDiff.HasValue && totalMessDiff.Value != 0) &&
!(unreadConvDiff.HasValue && unreadConvDiff.Value != 0) &&
!(totalConvDiff.HasValue && totalConvDiff.Value != 0))
{
return;
}
var updateQuery = new SqlUpdate(FolderTable.Name)
.Where(GetUserWhere(user, tenant))
.Where(FolderTable.Columns.Folder, folder);
if (unreadMessDiff.HasValue && unreadMessDiff.Value != 0)
{
var setValue = string.Format(INCR_VALUE_FORMAT,
FolderTable.Columns.UnreadMessagesCount, unreadMessDiff.Value);
updateQuery.Set(setValue);
}
if (totalMessDiff.HasValue && totalMessDiff.Value != 0)
{
var setValue = string.Format(INCR_VALUE_FORMAT,
FolderTable.Columns.TotalMessagesCount, totalMessDiff.Value);
updateQuery.Set(setValue);
}
if (unreadConvDiff.HasValue && unreadConvDiff.Value != 0)
{
var setValue = string.Format(INCR_VALUE_FORMAT,
FolderTable.Columns.UnreadConversationsCount, unreadConvDiff.Value);
updateQuery.Set(setValue);
}
if (totalConvDiff.HasValue && totalConvDiff.Value != 0)
{
var setValue = string.Format(INCR_VALUE_FORMAT,
FolderTable.Columns.TotalConversationsCount, totalConvDiff.Value);
updateQuery.Set(setValue);
}
try
{
if (0 == db.ExecuteNonQuery(updateQuery))
{
throw new Exception("Need recalculation");
}
}
catch (Exception ex)
{
_log.Error("ChangeFolderCounters() Exception: {0}", ex.ToString());
RecalculateFolders();
}
}
private int CreateAlerts(IDbManager db, int tenant, List<string> users, AlertTypes type, object data = null)
{
var result = 0;
if (!users.Any()) return result;
var jsonData = MailUtil.GetJsonString(data);
CreateInsertDelegate createInsertQuery = ()
=> new SqlInsert(MailAlertsTable.name)
.IgnoreExists(true)
.InColumns(MailAlertsTable.Columns.id_tenant,
MailAlertsTable.Columns.id_user,
MailAlertsTable.Columns.id_mailbox,
MailAlertsTable.Columns.type,
MailAlertsTable.Columns.data);
var insertQuery = createInsertQuery();
int i, usersLen;
for (i = 0, usersLen = users.Count; i < usersLen; i++)
{
var user = users[i];
insertQuery
.Values(tenant, user, -1, (int) type, jsonData);
if ((i%100 != 0 || i == 0) && i + 1 != usersLen) continue;
result += db.ExecuteNonQuery(insertQuery);
insertQuery = createInsertQuery();
}
return result;
}
private void RecalculateFolders(IDbManager db, int tenant, string user, Action <MailOperationRecalculateMailboxProgress> callback = null)
{
var folderIds = new[]
{
MailFolder.Ids.temp,
MailFolder.Ids.inbox,
MailFolder.Ids.sent,
MailFolder.Ids.drafts,
MailFolder.Ids.trash,
MailFolder.Ids.spam
};
var unreadMessagesCountByFolderQuery = new SqlQuery(MailTable.Name)
.Select(MailTable.Columns.Folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.Unread, true)
.Where(MailTable.Columns.IsRemoved, false)
.GroupBy(MailTable.Columns.Folder);
if (callback != null)
{
callback(MailOperationRecalculateMailboxProgress.CountUnreadMessages);
}
var unreadMessagesCountByFolder =
db.ExecuteList(unreadMessagesCountByFolderQuery)
.ConvertAll(
x =>
new KeyValuePair <int, int>(
Convert.ToInt32(x[0]),
Convert.ToInt32(x[1])));
var totalMessagesCountByFolderQuery = new SqlQuery(MailTable.Name)
.Select(MailTable.Columns.Folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.IsRemoved, false)
.GroupBy(MailTable.Columns.Folder);
if (callback != null)
{
callback(MailOperationRecalculateMailboxProgress.CountTotalMessages);
}
var totalMessagesCountByFolder =
db.ExecuteList(totalMessagesCountByFolderQuery)
.ConvertAll(
x =>
new KeyValuePair <int, int>(Convert.ToInt32(x[0]),
Convert.ToInt32(x[1])));
var unreadConversationsCountByFolderQuery = new SqlQuery(ChainTable.Name)
.Select(ChainTable.Columns.Folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(ChainTable.Columns.Unread, true)
.GroupBy(ChainTable.Columns.Folder);
if (callback != null)
{
callback(MailOperationRecalculateMailboxProgress.CountUreadConversation);
}
var unreadConversationsCountByFolder =
db.ExecuteList(unreadConversationsCountByFolderQuery)
.ConvertAll(
x => new KeyValuePair <int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
var totalConversationsCountByFolderQuery = new SqlQuery(ChainTable.Name)
.Select(ChainTable.Columns.Folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.GroupBy(ChainTable.Columns.Folder);
if (callback != null)
{
callback(MailOperationRecalculateMailboxProgress.CountTotalConversation);
}
var totalConversationsCountByFolder =
db.ExecuteList(totalConversationsCountByFolderQuery)
.ConvertAll(
x => new KeyValuePair <int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
if (callback != null)
{
callback(MailOperationRecalculateMailboxProgress.UpdateFoldersCounters);
}
var foldersInfo = from folderId in folderIds
let unreadMessCount = unreadMessagesCountByFolder.Find(c => c.Key == folderId).Value
let totalMessCount = totalMessagesCountByFolder.Find(c => c.Key == folderId).Value
let unreadConvCount = unreadConversationsCountByFolder.Find(c => c.Key == folderId).Value
let totalConvCount = totalConversationsCountByFolder.Find(c => c.Key == folderId).Value
select new
{
id = folderId,
unread_messages_count = unreadMessCount,
total_messages_count = totalMessCount,
unread_conversations_count = unreadConvCount,
total_conversations_count = totalConvCount,
time_modified = DateTime.UtcNow
};
foreach (var info in foldersInfo)
{
var insert = new SqlInsert(FolderTable.Name, true)
.InColumnValue(FolderTable.Columns.Tenant, tenant)
.InColumnValue(FolderTable.Columns.User, user)
.InColumnValue(FolderTable.Columns.Folder, info.id)
.InColumnValue(FolderTable.Columns.UnreadMessagesCount, info.unread_messages_count)
.InColumnValue(FolderTable.Columns.UnreadConversationsCount, info.unread_conversations_count)
.InColumnValue(FolderTable.Columns.TotalMessagesCount, info.total_messages_count)
.InColumnValue(FolderTable.Columns.TotalConversationsCount, info.total_conversations_count);
db.ExecuteNonQuery(insert);
}
}
private void ChangeFolderCounters(
IDbManager db,
int tenant,
string user,
int folder,
int unreadMessDiff,
int totalMessDiff,
int unreadConvDiff,
int totalConvDiff)
{
if (0 == unreadMessDiff && 0 == totalMessDiff && 0 == unreadConvDiff && 0 == totalConvDiff)
return;
var updateQuery = new SqlUpdate(FolderTable.name)
.Where(GetUserWhere(user, tenant))
.Where(FolderTable.Columns.folder, folder);
if (0 != unreadMessDiff)
updateQuery.Set(FolderTable.Columns.unread_messages_count + "=" +
FolderTable.Columns.unread_messages_count + "+(" + unreadMessDiff + ")");
if (0 != totalMessDiff)
updateQuery.Set(FolderTable.Columns.total_messages_count + "=" +
FolderTable.Columns.total_messages_count + "+(" + totalMessDiff + ")");
if (0 != unreadConvDiff)
updateQuery.Set(FolderTable.Columns.unread_conversations_count + "=" +
FolderTable.Columns.unread_conversations_count + "+(" + unreadConvDiff + ")");
if (0 != totalConvDiff)
updateQuery.Set(FolderTable.Columns.total_conversations_count + "=" +
FolderTable.Columns.total_conversations_count + "+(" + totalConvDiff + ")");
if (0 == db.ExecuteNonQuery(updateQuery))
RecalculateFolders(db, tenant, user);
}
public SyncController(IRestManager manager, IWritableOptions <Endpoints> options, IWritableOptions <ConnectionStrings> optionsb, IDbManager dbManager)
{
_manager = manager;
_options = options;
_optionsb = optionsb;
_dbManager = dbManager;
}
private List<MailFolderInfo> RecalculateFolders(IDbManager db, int tenant, string user, bool isConversation)
{
List<KeyValuePair<int, int>> unreadCount;
List<KeyValuePair<int, int>> totalCount;
var folders = new List<MailFolderInfo>();
var folderIds = new[]
{
MailFolder.Ids.temp,
MailFolder.Ids.inbox,
MailFolder.Ids.sent,
MailFolder.Ids.drafts,
MailFolder.Ids.trash,
MailFolder.Ids.spam
};
if (!isConversation)
{
unreadCount = db.ExecuteList(
new SqlQuery(MailTable.name)
.Select(MailTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.unread, true)
.Where(MailTable.Columns.is_removed, false)
.GroupBy(MailTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair<int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
totalCount = db.ExecuteList(
new SqlQuery(MailTable.name)
.Select(MailTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.is_removed, false)
.GroupBy(MailTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair<int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
}
else
{
unreadCount = db.ExecuteList(
new SqlQuery(ChainTable.name)
.Select(ChainTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.Where(ChainTable.Columns.unread, true)
.GroupBy(ChainTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair<int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
totalCount = db.ExecuteList(
new SqlQuery(ChainTable.name)
.Select(ChainTable.Columns.folder, "count(*)")
.Where(GetUserWhere(user, tenant))
.GroupBy(ChainTable.Columns.folder))
.ConvertAll(
x => new KeyValuePair<int, int>(Convert.ToInt32(x[0]), Convert.ToInt32(x[1])));
}
foreach (var folder in
from folderId in folderIds
let unread = unreadCount.Find(c => c.Key == folderId).Value
let total = totalCount.Find(c => c.Key == folderId).Value
select new MailFolderInfo
{
id = folderId,
unread = unread,
total_count = total,
time_modified = DateTime.UtcNow
})
{
folders.Add(folder);
var upsertQuery =
string.Format("INSERT INTO {0} ({1}, {2}, {3}, {4}, {5})" +
"VALUES(@tid, @user, @folder, @count1, @count2)" +
"ON DUPLICATE KEY UPDATE {4} = @count1, {5} = @count2",
FolderTable.name,
FolderTable.Columns.id_tenant,
FolderTable.Columns.id_user,
FolderTable.Columns.folder,
isConversation
? FolderTable.Columns.unread_conversations_count
: FolderTable.Columns.unread_messages_count,
isConversation
? FolderTable.Columns.total_conversations_count
: FolderTable.Columns.total_messages_count);
db.ExecuteNonQuery(upsertQuery, new { tid = tenant, user, folder = folder.id, count1 = folder.unread, count2 = folder.total_count });
}
return folders;
}
public DbInfoService(IDbManager repos)
{
db = repos;
}
