public static void SetCultureAvailable(string title)
{
using (var dbManager = new DbManager("tmresource"))
{
var sql = new SqlUpdate("res_cultures");
sql.Set("available", true).Where("title", title);
dbManager.ExecuteNonQuery(sql);
}
}
public static void AddLink(string resource, string fileName, string page)
{
using (var dbManager = new DbManager("tmresource"))
{
var query = new SqlQuery("res_data");
query.Select("res_data.id")
.InnerJoin("res_files", Exp.EqColumns("res_files.id", "res_data.fileid"))
.Where("res_data.title", resource).Where("res_files.resName", fileName).Where("res_data.cultureTitle", "Neutral");
var key = dbManager.ExecuteScalar<int>(query);
var update = new SqlUpdate("res_data");
update.Set("link", page).Where("id", key);
dbManager.ExecuteNonQuery(update);
}
}
public static void UnLockModules()
{
using (var dbManager = new DbManager("tmresource"))
{
var sqlUpdate = new SqlUpdate("res_files");
sqlUpdate.Set("isLock", 0);
dbManager.ExecuteNonQuery(sqlUpdate);
}
}
public static void LockModules(string projectName, string modules)
{
using (var dbManager = new DbManager("tmresource"))
{
var sqlUpdate = new SqlUpdate("res_files");
sqlUpdate.Set("isLock", 1).Where("projectName", projectName).Where(Exp.In("moduleName", modules.Split(',')));
dbManager.ExecuteNonQuery(sqlUpdate);
}
}
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 void SetMailboxAuthError(MailBox mailbox, bool isError)
{
using (var db = GetDb())
{
var updateQuery = new SqlUpdate(MailboxTable.name)
.Where(MailboxTable.Columns.id, mailbox.MailBoxId)
.Where(MailboxTable.Columns.id_user, mailbox.UserId)
.Where(MailboxTable.Columns.id_tenant, mailbox.TenantId);
if (isError)
{
mailbox.AuthErrorDate = DateTime.UtcNow;
updateQuery.Where(MailboxTable.Columns.date_auth_error, null)
.Set(MailboxTable.Columns.date_auth_error, mailbox.AuthErrorDate.Value);
}
else
{
updateQuery.Set(MailboxTable.Columns.date_auth_error, null);
mailbox.AuthErrorDate = null;
}
db.ExecuteNonQuery(updateQuery);
}
}
public bool SaveMailBox(MailBox mailbox)
{
if (mailbox == null) throw new ArgumentNullException("mailbox");
var idMailbox = MailBoxExists(GetAddress(mailbox.EMail), mailbox.UserId, mailbox.TenantId);
using (var db = GetDb())
{
int result;
var loginDelayTime = GetLoginDelayTime(mailbox);
if (idMailbox == 0)
{
var utcNow = DateTime.UtcNow;
result = db.ExecuteScalar<int>(
new SqlInsert(MailboxTable.name)
.InColumnValue(MailboxTable.Columns.id, 0)
.InColumnValue(MailboxTable.Columns.id_tenant, mailbox.TenantId)
.InColumnValue(MailboxTable.Columns.id_user, mailbox.UserId)
.InColumnValue(MailboxTable.Columns.address, GetAddress(mailbox.EMail))
.InColumnValue(MailboxTable.Columns.name, mailbox.Name)
.InColumnValue(MailboxTable.Columns.password, EncryptPassword(mailbox.Password))
.InColumnValue(MailboxTable.Columns.msg_count_last, mailbox.MessagesCount)
.InColumnValue(MailboxTable.Columns.smtp_password,
string.IsNullOrEmpty(mailbox.SmtpPassword)
? EncryptPassword(mailbox.Password)
: EncryptPassword(mailbox.SmtpPassword))
.InColumnValue(MailboxTable.Columns.size_last, mailbox.Size)
.InColumnValue(MailboxTable.Columns.login_delay, loginDelayTime)
.InColumnValue(MailboxTable.Columns.enabled, true)
.InColumnValue(MailboxTable.Columns.imap, mailbox.Imap)
.InColumnValue(MailboxTable.Columns.begin_date, mailbox.BeginDate)
.InColumnValue(MailboxTable.Columns.service_type, mailbox.ServiceType)
.InColumnValue(MailboxTable.Columns.refresh_token, mailbox.RefreshToken)
.InColumnValue(MailboxTable.Columns.id_smtp_server, mailbox.SmtpServerId)
.InColumnValue(MailboxTable.Columns.id_in_server, mailbox.InServerId)
.InColumnValue(MailboxTable.Columns.date_created, utcNow)
.Identity(0, 0, true));
mailbox.MailBoxId = result;
mailbox.Enabled = true;
}
else
{
mailbox.MailBoxId = idMailbox;
var queryUpdate = new SqlUpdate(MailboxTable.name)
.Where(MailboxTable.Columns.id, idMailbox)
.Set(MailboxTable.Columns.id_tenant, mailbox.TenantId)
.Set(MailboxTable.Columns.id_user, mailbox.UserId)
.Set(MailboxTable.Columns.address, GetAddress(mailbox.EMail))
.Set(MailboxTable.Columns.name, mailbox.Name)
.Set(MailboxTable.Columns.password, EncryptPassword(mailbox.Password))
.Set(MailboxTable.Columns.msg_count_last, mailbox.MessagesCount)
.Set(MailboxTable.Columns.smtp_password,
string.IsNullOrEmpty(mailbox.SmtpPassword)
? EncryptPassword(mailbox.Password)
: EncryptPassword(mailbox.SmtpPassword))
.Set(MailboxTable.Columns.size_last, mailbox.Size)
.Set(MailboxTable.Columns.login_delay, loginDelayTime)
.Set(MailboxTable.Columns.is_removed, false)
.Set(MailboxTable.Columns.imap, mailbox.Imap)
.Set(MailboxTable.Columns.begin_date, mailbox.BeginDate)
.Set(MailboxTable.Columns.service_type, mailbox.ServiceType)
.Set(MailboxTable.Columns.refresh_token, mailbox.RefreshToken)
.Set(MailboxTable.Columns.id_smtp_server, mailbox.SmtpServerId)
.Set(MailboxTable.Columns.id_in_server, mailbox.InServerId);
if (mailbox.BeginDate == MinBeginDate)
{
var currentMailbox = GetMailBox(idMailbox);
if (currentMailbox == null)
throw new ItemNotFoundException("Mailbox was removed");
if (mailbox.BeginDate != currentMailbox.BeginDate)
{
foreach (var folderName in currentMailbox.ImapIntervals.Keys)
{
var imapIntervals =
new ImapIntervals(currentMailbox.ImapIntervals[folderName].UnhandledUidIntervals);
if (currentMailbox.ImapIntervals[folderName].BeginDateUid != 1)
imapIntervals.AddUnhandledInterval(new UidInterval(1,
currentMailbox.ImapIntervals[
folderName].BeginDateUid));
currentMailbox.ImapIntervals[folderName].UnhandledUidIntervals =
new List<int>(imapIntervals.ToIndexes());
currentMailbox.ImapIntervals[folderName].BeginDateUid = 1;
}
queryUpdate.Set(MailboxTable.Columns.imap_intervals, currentMailbox.ImapIntervalsJson);
}
}
result = db.ExecuteNonQuery(queryUpdate);
}
return result > 0;
}
}
public static void EditEnglish(ResWord word)
{
using (var dbManager = new DbManager("tmresource"))
{
var update = new SqlUpdate("res_data");
update.Set("textvalue", word.ValueFrom).Where("fileID", word.ResFile.FileID).Where("title", word.Title).Where("cultureTitle", "Neutral");
dbManager.ExecuteNonQuery(update);
}
}
public void SetAuthError(MailBox mailbox, bool error)
{
using (var db = GetDb())
{
var instr = new SqlUpdate(MailboxTable.name)
.Where(MailboxTable.Columns.id, mailbox.MailBoxId)
.Where(MailboxTable.Columns.id_user, mailbox.UserId)
.Where(MailboxTable.Columns.id_tenant, mailbox.TenantId);
db.ExecuteNonQuery(error
? instr.Where(MailboxTable.Columns.auth_error, null)
.Set(MailboxTable.Columns.auth_error, DateTime.UtcNow.Ticks)
: instr.Set(MailboxTable.Columns.auth_error, null));
if (mailbox.AuthError == MailBox.AuthProblemType.NoProblems)
return;
switch (mailbox.AuthError)
{
case MailBox.AuthProblemType.ConnectError:
CreateAuthErrorWarningAlert(mailbox.TenantId, mailbox.UserId, mailbox.MailBoxId);
break;
case MailBox.AuthProblemType.TooManyErrors:
CreateAuthErrorDisableAlert(mailbox.TenantId, mailbox.UserId, mailbox.MailBoxId);
EnableMaibox(mailbox, false);
break;
default:
return;
}
}
}
public bool SaveMailBox(MailBox mail_box)
{
if (mail_box == null) throw new ArgumentNullException("mail_box");
var id_mailbox = MailBoxExists(GetAddress(mail_box.EMail), mail_box.UserId, mail_box.TenantId);
using (var db = GetDb())
{
int result;
var login_delay_time = GetLoginDelayTime(mail_box);
if (id_mailbox == 0)
{
result = db.ExecuteScalar<int>(
new SqlInsert(MAIL_MAILBOX)
.InColumnValue(MailBoxFields.id, 0)
.InColumnValue(MailBoxFields.id_tenant, mail_box.TenantId)
.InColumnValue(MailBoxFields.id_user, mail_box.UserId)
.InColumnValue(MailBoxFields.address, GetAddress(mail_box.EMail))
.InColumnValue(MailBoxFields.name, mail_box.Name)
.InColumnValue(MailBoxFields.password, EncryptPassword(mail_box.Password))
.InColumnValue(MailBoxFields.msg_count_last, mail_box.MessagesCount)
.InColumnValue(MailBoxFields.smtp_password,
string.IsNullOrEmpty(mail_box.SmtpPassword)
? EncryptPassword(mail_box.Password)
: EncryptPassword(mail_box.SmtpPassword))
.InColumnValue(MailBoxFields.size_last, mail_box.Size)
.InColumnValue(MailBoxFields.login_delay, login_delay_time)
.InColumnValue(MailBoxFields.enabled, true)
.InColumnValue(MailBoxFields.imap, mail_box.Imap)
.InColumnValue(MailBoxFields.begin_date, mail_box.BeginDate)
.InColumnValue(MailBoxFields.service_type, mail_box.ServiceType)
.InColumnValue(MailBoxFields.refresh_token, mail_box.RefreshToken)
.InColumnValue(MailBoxFields.id_smtp_server, mail_box.SmtpServerId)
.InColumnValue(MailBoxFields.id_in_server, mail_box.InServerId)
.Identity(0, 0, true));
mail_box.MailBoxId = result;
}
else
{
mail_box.MailBoxId = id_mailbox;
var query_update = new SqlUpdate(MAIL_MAILBOX)
.Where(MailBoxFields.id, id_mailbox)
.Set(MailBoxFields.id_tenant, mail_box.TenantId)
.Set(MailBoxFields.id_user, mail_box.UserId)
.Set(MailBoxFields.address, GetAddress(mail_box.EMail))
.Set(MailBoxFields.name, mail_box.Name)
.Set(MailBoxFields.password, EncryptPassword(mail_box.Password))
.Set(MailBoxFields.msg_count_last, mail_box.MessagesCount)
.Set(MailBoxFields.smtp_password,
string.IsNullOrEmpty(mail_box.SmtpPassword)
? EncryptPassword(mail_box.Password)
: EncryptPassword(mail_box.SmtpPassword))
.Set(MailBoxFields.size_last, mail_box.Size)
.Set(MailBoxFields.login_delay, login_delay_time)
.Set(MailBoxFields.is_removed, false)
.Set(MailBoxFields.imap, mail_box.Imap)
.Set(MailBoxFields.begin_date, mail_box.BeginDate)
.Set(MailBoxFields.service_type, mail_box.ServiceType)
.Set(MailBoxFields.refresh_token, mail_box.RefreshToken)
.Set(MailBoxFields.id_smtp_server, mail_box.SmtpServerId)
.Set(MailBoxFields.id_in_server, mail_box.InServerId);
if (mail_box.BeginDate == MIN_BEGIN_DATE)
query_update.Set(MailBoxFields.imap_folders, "[]");
result = db.ExecuteNonQuery(query_update);
}
return result > 0;
}
}
public void SetAuthError(MailBox mailbox, bool error)
{
using (var db = GetDb())
{
var instr = new SqlUpdate(MAIL_MAILBOX)
.Where(MailBoxFields.id, mailbox.MailBoxId)
.Where(MailBoxFields.id_user, mailbox.UserId)
.Where(MailBoxFields.id_tenant, mailbox.TenantId);
db.ExecuteNonQuery(error
? instr.Where(MailBoxFields.auth_error, null)
.Set(MailBoxFields.auth_error, DateTime.UtcNow.Ticks)
: instr.Set(MailBoxFields.auth_error, null));
}
}
public Event UpdateEvent(int eventId, int calendarId, Guid ownerId, string name, string description,
DateTime utcStartDate, DateTime utcEndDate, RecurrenceRule rrule, EventAlertType alertType, bool isAllDayLong, List<ASC.Web.Core.Calendars.SharingOptions.PublicItem> publicItems)
{
using (var tr = DbManager.BeginTransaction())
{
var query = new SqlUpdate("calendar_events")
.Set("name", name)
.Set("description", description)
.Set("calendar_id", calendarId)
.Set("owner_id", ownerId)
.Set("start_date", utcStartDate.ToString("yyyy-MM-dd HH:mm:ss"))
.Set("end_date", utcEndDate.ToString("yyyy-MM-dd HH:mm:ss"))
.Set("all_day_long", isAllDayLong)
.Set("rrule", rrule.ToString())
.Where(Exp.Eq("id", eventId));
if (ownerId.Equals(SecurityContext.CurrentAccount.ID))
query = query.Set("alert_type", (int)alertType);
else
DbManager.ExecuteNonQuery(new SqlInsert("calendar_event_user", true).InColumnValue("event_id", eventId)
.InColumnValue("user_id", SecurityContext.CurrentAccount.ID)
.InColumnValue("alert_type", alertType));
DbManager.ExecuteNonQuery(query);
var userIds = DbManager.ExecuteList(new SqlQuery("calendar_event_user").Select("user_id").Where("event_id", eventId)).Select(r => new Guid(Convert.ToString(r[0])));
foreach (var usrId in userIds)
{
if (!publicItems.Exists(i => (i.IsGroup && CoreContext.UserManager.IsUserInGroup(usrId, i.Id))
|| (!i.IsGroup && i.Id.Equals(usrId))))
{
DbManager.ExecuteNonQuery(new SqlDelete("calendar_event_user").Where(Exp.Eq("user_id", usrId) & Exp.Eq("event_id", eventId)));
}
}
DbManager.ExecuteNonQuery(new SqlDelete("calendar_event_item").Where("event_id", eventId));
foreach (var item in publicItems)
{
DbManager.ExecuteNonQuery(new SqlInsert("calendar_event_item")
.InColumnValue("event_id", eventId)
.InColumnValue("item_id", item.Id)
.InColumnValue("is_group", item.IsGroup));
}
//update notifications
var baseAlertType = DbManager.ExecuteList(new SqlQuery("calendar_events").Select("alert_type").Where("id", eventId))
.Select(r => (EventAlertType)Convert.ToInt32(r[0])).First();
UpdateEventNotifications(eventId, calendarId, utcStartDate, baseAlertType, rrule, publicItems, null);
tr.Commit();
}
return GetEventById(eventId);
}
public static void UpdateDeletedData(int fileId, DateTime date)
{
using (var dbManager = new DbManager("tmresource"))
{
var sqlUpdate = new SqlUpdate("res_data");
sqlUpdate.Set("flag", 4).Where("flag", 1).Where(Exp.Lt("TimeChanges", date)).Where("fileID", fileId);
dbManager.ExecuteNonQuery(sqlUpdate);
}
}
public static void AddComment(ResWord word)
{
using (var dbManager = new DbManager("tmresource"))
{
var sqlUpdate = new SqlUpdate("res_data");
sqlUpdate.Set("description", word.TextComment).Where("title", word.Title).Where("fileID", word.ResFile.FileID).Where("cultureTitle", "Neutral");
dbManager.ExecuteNonQuery(sqlUpdate);
}
}
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);
}
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 bool EnableMaibox(DbManager db, MailBox mailbox, bool enabled)
{
var updateQuery = new SqlUpdate(MailboxTable.name)
.Where(MailboxTable.Columns.id, mailbox.MailBoxId)
.Where(GetUserWhere(mailbox.UserId, mailbox.TenantId))
.Where(MailboxTable.Columns.is_removed, false)
.Set(MailboxTable.Columns.enabled, enabled);
if (enabled)
updateQuery.Set(MailboxTable.Columns.date_auth_error, null);
var result = db.ExecuteNonQuery(updateQuery);
return result > 0;
}
