protected override void _AddDkim(DkimRecordBase dkimToAdd)
{
var dbManager = new PostfixAdminDbManager(Server.Id, Server.ConnectionString);
using (var db = dbManager.GetAdminDb())
{
var dkimId = db.ExecuteScalar<int>(
new SqlQuery(DkimTable.name)
.Select(DkimTable.Columns.id)
.Where(DkimTable.Columns.domain_name, Name));
if (dkimId == 0)
{
var insertDkim = new SqlInsert(DkimTable.name)
.InColumnValue(DkimTable.Columns.domain_name, Name)
.InColumnValue(DkimTable.Columns.selector, dkimToAdd.Selector)
.InColumnValue(DkimTable.Columns.private_key, dkimToAdd.PrivateKey)
.InColumnValue(DkimTable.Columns.public_key, dkimToAdd.PublicKey);
db.ExecuteNonQuery(insertDkim);
}
else
{
var updateDkim = new SqlUpdate(DkimTable.name)
.Where(DkimTable.Columns.id, dkimId)
.Set(DkimTable.Columns.selector, dkimToAdd.Selector)
.Set(DkimTable.Columns.private_key, dkimToAdd.PrivateKey)
.Set(DkimTable.Columns.public_key, dkimToAdd.PublicKey);
db.ExecuteNonQuery(updateDkim);
}
}
}
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 AddResource(string cultureTitle, string resType, DateTime date, ResWord word, bool isConsole, string authorLogin, bool updateIfExist = true)
{
using (var db = new DbManager("tmresource"))
{
var resData = db.ExecuteScalar<string>(GetQuery("res_data", cultureTitle, word));
var resReserve = db.ExecuteScalar<string>(GetQuery("res_reserve", cultureTitle, word));
//нет ключа
if (string.IsNullOrEmpty(resData))
{
//добавляем в основную таблицу
db.ExecuteNonQuery(Insert("res_data", cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
//добавляем в резервную таблицу
if (isConsole) db.ExecuteNonQuery(Insert("res_reserve", cultureTitle, word));
}
else if(updateIfExist)
{
var isChangeResData = resData != word.ValueFrom;
var isChangeResReserve = resReserve != word.ValueFrom;
//при работе с консолью изменилось по сравнению с res_data и res_reserve, либо при работе с сайтом изменилось по сравнению с res_reserve
if ((isConsole && isChangeResData && isChangeResReserve) || !isConsole)
{
// изменилась нейтральная культура - выставлен флаг у всех ключей из выбранного файла с выбранным title
if (cultureTitle == "Neutral")
{
var update = new SqlUpdate("res_data")
.Set("flag", 3)
.Where("fileID", word.ResFile.FileID)
.Where("title", word.Title);
db.ExecuteNonQuery(update);
}
// изменилась не нейтральная культура
db.ExecuteNonQuery(Insert("res_data", cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
if (isConsole) db.ExecuteNonQuery(Update("res_reserve", cultureTitle, word));
}
else if (isChangeResData)
{
db.ExecuteNonQuery(Update("res_reserve", cultureTitle, word));
}
}
}
}
public static void AddResource(string cultureTitle, string resType, DateTime date, ResWord word, bool isConsole, string authorLogin)
{
using (var db = new DbManager("tmresource"))
{
var resData = db.ExecuteScalar<string>(GetQuery("res_data", cultureTitle, word));
var resReserve = db.ExecuteScalar<string>(GetQuery("res_reserve", cultureTitle, word));
if (string.IsNullOrEmpty(resData))
{
db.ExecuteNonQuery(Insert("res_data", cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
if (isConsole) db.ExecuteNonQuery(Insert("res_reserve", cultureTitle, word));
}
else
{
var isChangeResData = resData != word.ValueFrom;
var isChangeResReserve = resReserve != word.ValueFrom;
if ((isConsole && isChangeResData && isChangeResReserve) || !isConsole)
{
if (cultureTitle == "Neutral")
{
var update = new SqlUpdate("res_data")
.Set("flag", 3)
.Where("fileID", word.ResFile.FileID)
.Where("title", word.Title);
db.ExecuteNonQuery(update);
}
db.ExecuteNonQuery(Insert("res_data", cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
if (isConsole) db.ExecuteNonQuery(Update("res_reserve", cultureTitle, word));
}
else if (isConsole && isChangeResData && !isChangeResReserve)
{
db.ExecuteNonQuery(Update("res_reserve", cultureTitle, word));
}
}
}
}
public void SetDomainChecked(int domainId)
{
if (domainId < 0)
throw new ArgumentException("Argument domain_id less then zero.", "domainId");
var updateDomain = new SqlUpdate(DomainTable.name)
.Set(string.Format("{0}=UTC_TIMESTAMP()", DomainTable.Columns.date_checked))
.Where(DomainTable.Columns.id, domainId);
using (var db = GetDb())
{
db.ExecuteNonQuery(updateDomain);
}
}
public void SetDomainDisabled(int domainId, int disabledDays)
{
if (domainId < 0)
throw new ArgumentException("Argument domain_id less then zero.", "domainId");
if(disabledDays < 1)
disabledDays = 1;
var updateDomain = new SqlUpdate(DomainTable.name)
.Set(string.Format("{0}=DATE_ADD(UTC_TIMESTAMP(), INTERVAL {1} DAY)", DomainTable.Columns.date_checked, disabledDays))
.Where(DomainTable.Columns.id, domainId);
using (var db = GetDb())
{
db.ExecuteNonQuery(updateDomain);
}
}
public bool LockMailbox(int mailbox_id, Int64 utc_ticks_time, bool is_additional_proccessed_check_needed, DbManager external_db)
{
var update_query = new SqlUpdate(MailboxTable.name)
.Set(MailboxTable.Columns.time_checked, utc_ticks_time)
.Set(MailboxTable.Columns.is_processed, true)
.Where(MailboxTable.Columns.id, mailbox_id);
if (is_additional_proccessed_check_needed)
{
update_query = update_query
.Where(MailboxTable.Columns.is_processed, false)
.Where(MailboxTable.Columns.is_removed, false);
}
if (external_db == null)
{
using (var db = GetDb())
{
return db.ExecuteNonQuery(update_query) > 0;
}
}
return external_db.ExecuteNonQuery(update_query) > 0;
}
public bool LockMailbox(int mailboxId, bool isAdditionalProccessedCheckNeeded, DbManager dbManager)
{
_log.Debug("LockMailbox(MailboxId = {0}, checkAnotherProcess = {1})", mailboxId, isAdditionalProccessedCheckNeeded);
var utcNow = DateTime.UtcNow;
bool success;
var updateQuery = new SqlUpdate(MailboxTable.name)
.Set(MailboxTable.Columns.date_checked, utcNow)
.Set(MailboxTable.Columns.is_processed, true)
.Where(MailboxTable.Columns.id, mailboxId);
if (isAdditionalProccessedCheckNeeded)
{
updateQuery = updateQuery
.Where(MailboxTable.Columns.is_processed, false)
.Where(MailboxTable.Columns.is_removed, false);
}
if (dbManager == null)
{
using (var db = GetDb())
{
success = db.ExecuteNonQuery(updateQuery) > 0;
}
}
else
{
success = dbManager.ExecuteNonQuery(updateQuery) > 0;
}
_log.Debug("LockMailbox(MailboxId = {0}) {1}", mailboxId, success ? "SUCCEEDED" : "FAILED");
return success;
}
public virtual int UpdateProviderInfo(int linkId, string customerTitle, AuthData authData, FolderType folderType)
{
var oldprovider = GetProviderInfoInernal(linkId);
//for google docs
authData = GetEncodedAccesToken(authData, oldprovider.ProviderName);
if (!CheckProviderInfo(ToProviderInfo(0, oldprovider.ProviderName, customerTitle, authData, SecurityContext.CurrentAccount.ID.ToString(), folderType, TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))))
throw new UnauthorizedAccessException("Can't authorize at " + oldprovider.ProviderName + " provider with given credentials");
var queryUpdate = new SqlUpdate(TableTitle)
.Set("customer_title", customerTitle)
.Set("user_name", authData.Login)
.Set("password", EncryptPassword(authData.Password))
.Set("folder_type", (int) folderType)
.Set("create_on", TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))
.Set("user_id", SecurityContext.CurrentAccount.ID.ToString())
.Set("token", authData.Token)
.Where("id", linkId)
.Where("tenant_id", TenantID);
return DbManager.ExecuteNonQuery(queryUpdate) == 1 ? linkId : default(int);
}
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);
}
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 void UpdateCrmLinkedMailboxId(string chain_id, int id_tenant, int old_mailbox_id, int new_mailbox_id)
{
if (old_mailbox_id < 1)
throw new ArgumentException("old_mailbox_id must be > 0");
if (new_mailbox_id < 1)
throw new ArgumentException("new_mailbox_id must be > 0");
if (old_mailbox_id == new_mailbox_id) return;
using (var db = GetDb())
{
var update_old_chain_id_query = new SqlUpdate(ChainXCrmContactEntity.name)
.Set(ChainXCrmContactEntity.Columns.id_mailbox, new_mailbox_id)
.Where(ChainXCrmContactEntity.Columns.id_chain, chain_id)
.Where(ChainXCrmContactEntity.Columns.id_mailbox, old_mailbox_id)
.Where(ChainXCrmContactEntity.Columns.id_tenant, id_tenant);
db.ExecuteNonQuery(update_old_chain_id_query);
}
}
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 IDictionary<int, NotifyMessage> GetMessages(int count)
{
lock (syncRoot)
{
using (var db = GetDb())
using (var tx = db.BeginTransaction())
{
var q = new SqlQuery("notify_queue q")
.InnerJoin("notify_info i", Exp.EqColumns("q.notify_id", "i.notify_id"))
.Select("q.notify_id", "q.tenant_id", "q.sender", "q.reciever", "q.subject", "q.content_type", "q.content", "q.sender_type", "q.creation_date", "q.reply_to")
.Where(Exp.Eq("i.state", MailSendingState.NotSended) | (Exp.Eq("i.state", MailSendingState.Error) & Exp.Lt("i.modify_date", DateTime.UtcNow - NotifyServiceCfg.AttemptsInterval)))
.OrderBy("i.priority", true)
.OrderBy("i.notify_id", true)
.SetMaxResults(count);
var messages = db
.ExecuteList(q)
.ToDictionary(
r => Convert.ToInt32(r[0]),
r => new NotifyMessage
{
Tenant = Convert.ToInt32(r[1]),
From = (string)r[2],
To = (string)r[3],
Subject = (string)r[4],
ContentType = (string)r[5],
Content = (string)r[6],
Sender = (string)r[7],
CreationDate = Convert.ToDateTime(r[8]),
ReplyTo = (string)r[9],
});
var u = new SqlUpdate("notify_info").Set("state", MailSendingState.Sending).Where(Exp.In("notify_id", messages.Keys));
db.ExecuteNonQuery(u);
tx.Commit();
return messages;
}
}
}
public void ResetStates()
{
using (var db = GetDb())
{
var u = new SqlUpdate("notify_info").Set("state", 0).Where("state", 1);
db.ExecuteNonQuery(u);
}
}
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 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);
}
}
public List<int> ReleaseLockedMailboxes(int timeoutInMinutes)
{
// Reset is_processed field for potentially crushed aggregators
var query = new SqlQuery(MailboxTable.name)
.Select(MailboxTable.Columns.id)
.Where(MailboxTable.Columns.is_processed, true)
.Where(string.Format("{0} is not null AND TIMESTAMPDIFF(MINUTE, {0}, UTC_TIMESTAMP()) > {1}",
MailboxTable.Columns.date_checked, timeoutInMinutes));
using (var db = GetDb())
{
var oldTasksList =
db.ExecuteList(query)
.ConvertAll(r => Convert.ToInt32(r[0]));
if (oldTasksList.Any())
{
var updateQuery = new SqlUpdate(MailboxTable.name)
.Set(MailboxTable.Columns.is_processed, false)
.Where(Exp.In(MailboxTable.Columns.id, oldTasksList.ToArray()));
var rowAffected = db.ExecuteNonQuery(updateQuery);
if (rowAffected == 0)
_log.Debug("ResetLockedMailboxes() No one locked mailboxes couldn't be released.");
else if (rowAffected != oldTasksList.Count)
_log.Debug("ResetLockedMailboxes() Some locked mailboxes couldn't be released.");
}
return oldTasksList;
}
}
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 void DisableMailboxes(int tenant, string user = "")
{
using (var db = GetDb())
{
var updateAccountQuery = new SqlUpdate(MailboxTable.name)
.Where(MailboxTable.Columns.id_tenant, tenant)
.Where(MailboxTable.Columns.is_removed, false)
.Where(MailboxTable.Columns.enabled, true)
.Set(MailboxTable.Columns.is_processed, false)
.Set(MailboxTable.Columns.enabled, false);
if (!string.IsNullOrEmpty(user))
updateAccountQuery.Where(MailboxTable.Columns.id_user, user);
db.ExecuteNonQuery(updateAccountQuery);
}
}
public static void UnLockModules()
{
using (var dbManager = new DbManager("tmresource"))
{
var sqlUpdate = new SqlUpdate("res_files");
sqlUpdate.Set("isLock", 0);
dbManager.ExecuteNonQuery(sqlUpdate);
}
}
public virtual int UpdateProviderInfo(int linkId, string customerTitle)
{
var queryUpdate = new SqlUpdate(TableTitle)
.Set("customer_title", customerTitle)
.Where("id", linkId)
.Where("tenant_id", TenantID);
return DbManager.ExecuteNonQuery(queryUpdate) == 1 ? linkId : default(int);
}
public static void SetAuthorOnline(string login)
{
using (var dbManager = new DbManager("tmresource"))
{
var sql = new SqlUpdate("res_authors")
.Set("lastVisit", DateTime.UtcNow)
.Where("login", login);
dbManager.ExecuteNonQuery(sql);
}
}
public void MergeDublicate(int fromContactID, int toContactID)
{
var fromContact = GetByID(fromContactID);
var toContact = GetByID(toContactID);
if (fromContact == null || toContact == null)
throw new ArgumentException();
using (var tx = DbManager.BeginTransaction())
{
ISqlInstruction q = Update("crm_task")
.Set("contact_id", toContactID)
.Where(Exp.Eq("contact_id", fromContactID));
DbManager.ExecuteNonQuery(q);
// crm_entity_contact
q = new SqlQuery("crm_entity_contact l")
.From("crm_entity_contact r")
.Select("l.entity_id", "l.entity_type", "l.contact_id")
.Where(Exp.EqColumns("l.entity_id", "r.entity_id") & Exp.EqColumns("l.entity_type", "r.entity_type"))
.Where("l.contact_id", fromContactID)
.Where("r.contact_id", toContactID);
DbManager.ExecuteList(q)
.ForEach(row =>
DbManager.ExecuteNonQuery(new SqlDelete("crm_entity_contact").Where("entity_id", row[0]).Where("entity_type", row[1]).Where("contact_id", row[2]))
);
q = new SqlUpdate("crm_entity_contact")
.Set("contact_id", toContactID)
.Where("contact_id", fromContactID);
DbManager.ExecuteNonQuery(q);
// crm_deal
q = Update("crm_deal")
.Set("contact_id", toContactID)
.Where("contact_id", fromContactID);
DbManager.ExecuteNonQuery(q);
// crm_relationship_event
q = Update("crm_relationship_event")
.Set("contact_id", toContactID)
.Where("contact_id", fromContactID);
DbManager.ExecuteNonQuery(q);
// crm_entity_tag
q = new SqlQuery("crm_entity_tag l")
.Select("l.tag_id")
.From("crm_entity_tag r")
.Where(Exp.EqColumns("l.tag_id", "r.tag_id") & Exp.EqColumns("l.entity_type", "r.entity_type"))
.Where("l.entity_id", fromContactID)
.Where("r.entity_id", toContactID);
var dublicateTagsID = DbManager.ExecuteList(q).ConvertAll(row => row[0]);
q = new SqlDelete("crm_entity_tag").Where(Exp.Eq("entity_id", fromContactID) & Exp.Eq("entity_type", (int)EntityType.Contact) & Exp.In("tag_id", dublicateTagsID));
DbManager.ExecuteNonQuery(q);
q = new SqlUpdate("crm_entity_tag").Set("entity_id", toContactID).Where("entity_id", fromContactID).Where("entity_type", (int)EntityType.Contact);
DbManager.ExecuteNonQuery(q);
// crm_field_value
q = Query("crm_field_value l")
.From("crm_field_value r")
.Select("l.field_id")
.Where(Exp.EqColumns("l.tenant_id", "r.tenant_id") & Exp.EqColumns("l.field_id", "r.field_id") & Exp.EqColumns("l.entity_type", "r.entity_type"))
.Where("l.entity_id", fromContactID)
.Where("r.entity_id", toContactID);
var dublicateCustomFieldValueID = DbManager.ExecuteList(q).ConvertAll(row => row[0]);
q = Delete("crm_field_value")
.Where("entity_id", fromContactID)
.Where(Exp.In("entity_type", new[] { (int)EntityType.Contact, (int)EntityType.Person, (int)EntityType.Company }))
.Where(Exp.In("field_id", dublicateCustomFieldValueID));
DbManager.ExecuteNonQuery(q);
q = Update("crm_field_value")
.Set("entity_id", toContactID)
.Where("entity_id", fromContactID)
.Where("entity_type", (int)EntityType.Contact);
DbManager.ExecuteNonQuery(q);
// crm_contact_info
q = Query("crm_contact_info l")
.From("crm_contact_info r")
.Select("l.id")
.Where(Exp.EqColumns("l.tenant_id", "r.tenant_id"))
.Where(Exp.EqColumns("l.type", "r.type"))
.Where(Exp.EqColumns("l.is_primary", "r.is_primary"))
.Where(Exp.EqColumns("l.category", "r.category"))
.Where(Exp.EqColumns("l.data", "r.data"))
.Where("l.contact_id", fromContactID)
.Where("r.contact_id", toContactID);
var dublicateContactInfoID = DbManager.ExecuteList(q).ConvertAll(row => row[0]);
q = Delete("crm_contact_info")
.Where("contact_id", fromContactID)
.Where(Exp.In("id", dublicateContactInfoID));
DbManager.ExecuteNonQuery(q);
q = Update("crm_contact_info")
.Set("contact_id", toContactID)
.Where("contact_id", fromContactID);
DbManager.ExecuteNonQuery(q);
MergeContactInfo(fromContact, toContact);
// crm_contact
if ((fromContact is Company) && (toContact is Company))
{
q = Update("crm_contact")
.Set("company_id", toContactID)
.Where("company_id", fromContactID);
DbManager.ExecuteNonQuery(q);
}
q = Delete("crm_contact").Where("id", fromContactID);
DbManager.ExecuteNonQuery(q);
tx.Commit();
}
CoreContext.AuthorizationManager.RemoveAllAces(fromContact);
}
public void SetState(int id, MailSendingState result)
{
using (var db = GetDb())
using (var tx = db.BeginTransaction())
{
if (result == MailSendingState.Sended)
{
var d = new SqlDelete("notify_info").Where("notify_id", id);
db.ExecuteNonQuery(d);
if (NotifyServiceCfg.DeleteSendedMessages)
{
d = new SqlDelete("notify_queue").Where("notify_id", id);
db.ExecuteNonQuery(d);
}
}
else
{
if (result == MailSendingState.Error)
{
var q = new SqlQuery("notify_info").Select("attempts").Where("notify_id", id);
var attempts = db.ExecuteScalar<int>(q);
if (NotifyServiceCfg.MaxAttempts <= attempts + 1)
{
result = MailSendingState.FatalError;
}
}
var u = new SqlUpdate("notify_info")
.Set("state", (int)result)
.Set("attempts = attempts + 1")
.Set("modify_date", DateTime.UtcNow)
.Where("notify_id", id);
db.ExecuteNonQuery(u);
}
tx.Commit();
}
}
public DkimDto LinkDkimToDomain(int dkim_id, int domain_id, DbManager db)
{
var dkim_dto = GetDkim(dkim_id, db);
if(dkim_dto == null)
throw new InvalidOperationException(String.Format("Record with dkim id: {0} not found in db.", dkim_id));
if (dkim_dto.id_domain != domain_id)
{
var update_query = new SqlUpdate(DkimTable.name)
.Set(DkimTable.Columns.id_domain, domain_id)
.Where(DkimTable.Columns.id, dkim_id);
var rows_affected = db.ExecuteNonQuery(update_query);
if (rows_affected == 0)
throw new InvalidOperationException(String.Format("Record with dkim id: {0} not found in db.", dkim_id));
dkim_dto.id_domain = domain_id;
return dkim_dto;
}
return dkim_dto;
}
public void SqlUpdateTest()
{
var update = new SqlUpdate("Table")
.Set("Column1", 1)
.Set("Column1", 2)
.Set("Column2", 3)
.Set("Column3 = Column3 + 2");
Assert.AreEqual("update Table set Column1 = ?, Column2 = ?, Column3 = Column3 + 2", update.ToString());
update = new SqlUpdate("Table")
.Set("Column1", 1)
.Set("Column1", 2)
.Set("Column3", new SqlQuery("Table2").Select("x").Where("y", 5));
Assert.AreEqual("update Table set Column1 = ?, Column3 = (select x from Table2 where y = ?)", update.ToString());
}
public void SetConversationsImportanceFlags(int tenant, string user, bool important, List<int> ids)
{
var chains_info_query = new SqlQuery(MailTable.name)
.Select(MailTable.Columns.chain_id)
.Select(MailTable.Columns.folder)
.Select(MailTable.Columns.id_mailbox)
.Where(GetUserWhere(user, tenant))
.Where(new InExp(MailTable.Columns.id, ids.Select(x => (object)x).ToArray()));
var update_mail_query = new SqlUpdate(MailTable.name)
.Set(MailTable.Columns.importance, important)
.Where(GetUserWhere(user, tenant));
using (var db = GetDb())
{
using (var tx = db.BeginTransaction(IsolationLevel.ReadUncommitted))
{
var chains_info = db.ExecuteList(chains_info_query)
.ConvertAll(i => new ChainInfo{
id = (string)i[0],
folder = Convert.ToInt32(i[1]),
mailbox = Convert.ToInt32(i[2])});
chains_info = chains_info.Distinct().ToList();
if(!chains_info.Any())
throw new Exception("no chain messages belong to current user");
var where_chain_builder = new StringBuilder("(");
for (int i = 0; i < chains_info.Count; ++i)
{
var chain = chains_info[i];
if(i > 0)
{
where_chain_builder.Append(" or ");
}
where_chain_builder.AppendFormat("({0} = '{1}' and {2} = {3}", MailTable.Columns.chain_id, chain.id,
MailTable.Columns.id_mailbox, chain.mailbox);
if (chain.folder == MailFolder.Ids.inbox || chain.folder == MailFolder.Ids.sent)
{
where_chain_builder.AppendFormat(" and ({0} = {1} or {0} = {2}))", MailTable.Columns.folder,
MailFolder.Ids.inbox, MailFolder.Ids.sent);
}
else
{
where_chain_builder.AppendFormat(" and {0} = {1})", MailTable.Columns.folder, chain.folder);
}
}
where_chain_builder.Append(")");
db.ExecuteNonQuery(update_mail_query.Where(new SqlExp(where_chain_builder.ToString())));
foreach (var message in ids)
{
UpdateMessageChainImportanceFlag(db, tenant, user, message);
}
tx.Commit();
}
}
}
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 void UpdateCrmLinkedChainId(int id_mailbox, int id_tenant, string old_chain_id, string new_chain_id)
{
if (string.IsNullOrEmpty(old_chain_id))
throw new ArgumentNullException("old_chain_id");
if (string.IsNullOrEmpty(new_chain_id))
throw new ArgumentNullException("new_chain_id");
if (old_chain_id.Equals(new_chain_id)) return;
using (var db = GetDb())
{
var update_old_chain_id_query = new SqlUpdate(ChainXCrmContactEntity.name)
.Set(ChainXCrmContactEntity.Columns.id_chain, new_chain_id)
.Where(ChainXCrmContactEntity.Columns.id_chain, old_chain_id)
.Where(ChainXCrmContactEntity.Columns.id_mailbox, id_mailbox)
.Where(ChainXCrmContactEntity.Columns.id_tenant, id_tenant);
db.ExecuteNonQuery(update_old_chain_id_query);
}
}
