public DnsDto CreateFreeDnsRecords(string dkimSelector, string domainCheckerPrefix,
string spf, DbManager db = null)
{
if (string.IsNullOrEmpty(dkimSelector))
throw new ArgumentNullException("dkimSelector");
if (string.IsNullOrEmpty(domainCheckerPrefix))
throw new ArgumentNullException("domainCheckerPrefix");
if (string.IsNullOrEmpty(spf))
throw new ArgumentNullException("spf");
string privateKey, publicKey;
DnsChecker.DnsChecker.GenerateKeys(out privateKey, out publicKey);
var domainCheckValue = PasswordGenerator.GenerateNewPassword(16);
var domainCheck = domainCheckerPrefix + ": " + domainCheckValue;
var insertValuesQuery = new SqlInsert(DnsTable.name)
.InColumnValue(AddressTable.Columns.id, 0)
.InColumnValue(DnsTable.Columns.user, _user)
.InColumnValue(DnsTable.Columns.tenant, _tenant)
.InColumnValue(DnsTable.Columns.dkim_selector, dkimSelector)
.InColumnValue(DnsTable.Columns.dkim_private_key, privateKey)
.InColumnValue(DnsTable.Columns.dkim_public_key, publicKey)
.InColumnValue(DnsTable.Columns.domain_check, domainCheck)
.InColumnValue(DnsTable.Columns.spf, spf)
.Identity(0, 0, true);
var dnsRecordId = NullSafeExecuteScalar<int>(db, insertValuesQuery);
return new DnsDto(dnsRecordId, _tenant, _user, Defines.UNUSED_DNS_SETTING_DOMAIN_ID, dkimSelector,
privateKey, publicKey, domainCheck, spf);
}
private static void AddLoginEvent(EventMessage message)
{
using (var db = new DbManager(messagesDbId))
{
var i = new SqlInsert(loginEventsTable)
.InColumnValue("ip", message.IP)
.InColumnValue("login", message.Initiator)
.InColumnValue("browser", message.Browser)
.InColumnValue("mobile", message.Mobile)
.InColumnValue("platform", message.Platform)
.InColumnValue("date", message.Date)
.InColumnValue("tenant_id", message.TenantId)
.InColumnValue("user_id", message.UserId)
.InColumnValue("page", message.Page)
.InColumnValue("action", message.Action);
if (message.Description != null && message.Description.Any())
{
i = i.InColumnValue("description", JsonConvert.SerializeObject(message.Description, new JsonSerializerSettings
{
DateTimeZoneHandling = DateTimeZoneHandling.Utc
}));
}
db.ExecuteNonQuery(i);
}
}
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 WebDomainDto AddWebDomain(string name, bool isVerified, DbManager db)
{
if (string.IsNullOrEmpty(name))
throw new ArgumentNullException("name");
if (db == null)
throw new ArgumentNullException("db");
var domain = GetDomain(name, db);
if (domain != null)
throw new DuplicateNameException(String.Format("Domain with name {0}. Already added to tenant {1}.",
domain.name, domain.tenant));
var domainAddTime = DateTime.UtcNow.ToDbStyle();
var addWebDomainQuery = new SqlInsert(DomainTable.name)
.InColumnValue(DomainTable.Columns.id, 0)
.InColumnValue(DomainTable.Columns.name, name)
.InColumnValue(DomainTable.Columns.tenant, tenant)
.InColumnValue(DomainTable.Columns.is_verified, isVerified)
.InColumnValue(DomainTable.Columns.date_added, domainAddTime)
.Identity(0, 0, true);
var addedDomainId = db.ExecuteScalar<int>(addWebDomainQuery);
return new WebDomainDto(addedDomainId, name, tenant, isVerified);
}
public static void AddCulture(string cultureTitle, string name)
{
using (var dbManager = new DbManager("tmresource"))
{
var sqlInsert = new SqlInsert("res_cultures");
sqlInsert.InColumnValue("title", cultureTitle).InColumnValue("value", name);
dbManager.ExecuteNonQuery(sqlInsert);
}
}
internal override SqlStatement VisitInsert(SqlInsert insert) {
bool saveMakeUnique = this.makeUnique;
this.makeUnique = false;
bool saveUseMappedNames = this.useMappedNames;
this.useMappedNames = true;
SqlStatement stmt = base.VisitInsert(insert);
this.makeUnique = saveMakeUnique;
this.useMappedNames = saveUseMappedNames;
return stmt;
}
public void RegisterInstall(string userEmail, MobileAppType appType)
{
var query =
new SqlInsert("mobile_app_install", true)
.InColumnValue("user_email", userEmail)
.InColumnValue("app_type", (int) appType)
.InColumnValue("registered_on", DateTime.UtcNow);
using (var db = GetDbManager())
{
db.ExecuteNonQuery(query);
}
}
public void SetTimeReaded(Guid user, DateTime time, string module, int tenant)
{
if (string.IsNullOrEmpty(module)) return;
var query = new SqlInsert("feed_readed", true)
.InColumns("user_id", "timestamp", "module", "tenant_id")
.Values(user.ToString(), time, module, tenant);
using (var db = GetDb())
{
db.ExecuteNonQuery(query);
}
}
public void Accept_WithoutInto_VisitsEverything()
{
var fixture = new Fixture().Customize(new AutoMoqCustomization());
var mock = fixture.Freeze<Mock<SqlVisitor>>();
var query = new SqlInsert();
query.Accept(mock.Object);
mock.Verify(_ => _.Visit(It.IsAny<SqlInsert>()), Times.Once);
mock.Verify(_ => _.Visit(It.IsAny<SqlInto>()), Times.Never);
mock.Verify(_ => _.Visit(It.IsAny<SqlColumn>()), Times.Never);
mock.Verify(_ => _.Visit(It.IsAny<SqlValues>()), Times.Once);
}
public static void SaveFeeds(IEnumerable<FeedRow> feeds, string key, DateTime value)
{
using (var db = new DbManager(Constants.FeedDbId))
using (var tx = db.BeginTransaction())
{
db.ExecuteNonQuery(new SqlInsert("feed_last", true).InColumnValue("last_key", key).InColumnValue("last_date", value));
var now = DateTime.UtcNow;
foreach (var f in feeds)
{
if (0 >= f.Users.Count) continue;
var i = new SqlInsert("feed_aggregate", true)
.InColumnValue("id", f.Id)
.InColumnValue("tenant", f.Tenant)
.InColumnValue("product", f.ProductId)
.InColumnValue("module", f.ModuleId)
.InColumnValue("author", f.AuthorId)
.InColumnValue("group_id", f.GroupId)
.InColumnValue("created_date", f.CreatedDate)
.InColumnValue("json", f.Json)
.InColumnValue("keywords", f.Keywords)
.InColumnValue("aggregated_date", now);
db.ExecuteNonQuery(i);
if (f.ClearRightsBeforeInsert)
{
db.ExecuteNonQuery(
new SqlDelete("feed_users")
.Where("feed_id", f.Id)
);
}
foreach (var u in f.Users)
{
db.ExecuteNonQuery(
new SqlInsert("feed_users", true)
.InColumnValue("feed_id", f.Id)
.InColumnValue("user_id", u.ToString())
);
}
}
tx.Commit();
}
}
protected override void _AddAlias(MailAddressBase aliasToAdd)
{
var insertMailboxAlias = new SqlInsert(AliasTable.name)
.InColumnValue(AliasTable.Columns.address, aliasToAdd.ToString())
.InColumnValue(AliasTable.Columns.redirect, Address.ToString())
.InColumnValue(AliasTable.Columns.domain, aliasToAdd.Domain.Name)
.InColumnValue(AliasTable.Columns.created, aliasToAdd.DateCreated)
.InColumnValue(AliasTable.Columns.modified, aliasToAdd.DateCreated)
.InColumnValue(AliasTable.Columns.active, true)
.InColumnValue(AliasTable.Columns.is_group, false);
var dbManager = new PostfixAdminDbManager(Server.Id, Server.ConnectionString);
using (var db = dbManager.GetAdminDb())
{
db.ExecuteNonQuery(insertMailboxAlias);
}
}
public DkimDto CreateFreeDkim(string selector, DbManager db = null)
{
string private_key, public_key;
DnsChecker.DnsChecker.GenerateKeys(out private_key, out public_key);
var insert_values_query = new SqlInsert(DkimTable.name)
.InColumnValue(AddressTable.Columns.id, 0)
.InColumnValue(DkimTable.Columns.user, _user)
.InColumnValue(DkimTable.Columns.tenant, _tenant)
.InColumnValue(DkimTable.Columns.selector, selector)
.InColumnValue(DkimTable.Columns.private_key, private_key)
.InColumnValue(DkimTable.Columns.public_key, public_key)
.Identity(0, 0, true);
var dkim_id = NullSafeExecuteScalar<int>(db, insert_values_query);
return new DkimDto(dkim_id, _tenant, _user, DefaultDomainId, selector, private_key, public_key);
}
public void SetShare(FileShareRecord r)
{
using (var dbManager = new DbManager(FileConstant.DatabaseId))
using (var tx = dbManager.BeginTransaction())
{
if (r.Share == FileShare.None)
{
if (r.EntryType == FileEntryType.Folder)
{
var entryIDs = dbManager.ExecuteList(Query("files_thirdparty_id_mapping")
.Select("hash_id")
.Where(Exp.Like("id", r.EntryId.ToString(), SqlLike.StartWith)))
.ConvertAll(x => x[0]);
dbManager.ExecuteNonQuery(Delete("files_security")
.Where(Exp.In("entry_id", entryIDs) &
Exp.Eq("subject", r.Subject.ToString())));
}
else
{
var d2 = Delete("files_security")
.Where(Exp.Eq("entry_id", MappingID(r.EntryId, true)))
.Where("entry_type", (int) FileEntryType.File)
.Where("subject", r.Subject.ToString());
dbManager.ExecuteNonQuery(d2);
}
}
else
{
var i = new SqlInsert("files_security", true)
.InColumnValue("tenant_id", r.Tenant)
.InColumnValue("entry_id", MappingID(r.EntryId, true))
.InColumnValue("entry_type", (int) r.EntryType)
.InColumnValue("subject", r.Subject.ToString())
.InColumnValue("owner", r.Owner.ToString())
.InColumnValue("security", (int) r.Share)
.InColumnValue("timestamp", DateTime.UtcNow);
dbManager.ExecuteNonQuery(i);
}
tx.Commit();
}
}
public void SaveMessage(NotifyMessage m)
{
using (var db = GetDb())
using (var tx = db.BeginTransaction(IsolationLevel.ReadCommitted))
{
var i = new SqlInsert("notify_queue")
.InColumns("notify_id", "tenant_id", "sender", "reciever", "subject", "content_type", "content", "sender_type", "creation_date", "reply_to")
.Values(0, m.Tenant, m.From, m.To, m.Subject, m.ContentType, m.Content, m.Sender, m.CreationDate, m.ReplyTo)
.Identity(0, 0, true);
var id = db.ExecuteScalar<int>(i);
i = new SqlInsert("notify_info")
.InColumns("notify_id", "state", "attempts", "modify_date", "priority")
.Values(id, 0, 0, DateTime.UtcNow, m.Priority);
db.ExecuteNonQuery(i);
tx.Commit();
}
}
public MailGroupDto SaveMailGroup(string addressName, DateTime addressCreatedDate,
int domainId, string domainName, bool isVerified, List<MailAddressDto> addressDtoList, DbManager db)
{
if (string.IsNullOrEmpty(addressName))
throw new ArgumentNullException("addressName");
if (domainId < 0)
throw new ArgumentException("Argument domain_id less then zero.", "domainId");
if (string.IsNullOrEmpty(domainName))
throw new ArgumentNullException("domainName");
if (addressDtoList == null || !addressDtoList.Any())
throw new ArgumentException("Address dto list must be not empty.", "addressDtoList");
if(db == null)
throw new ArgumentNullException("db");
var addressDal = new MailAddressDal(tenant);
var createdTime = DateTime.UtcNow.ToDbStyle();
var addressDto = addressDal.AddMailgroupAddress(addressName, addressCreatedDate, domainId, domainName,
isVerified, db);
var insertGroupQuery = new SqlInsert(MailGroupTable.name)
.InColumnValue(MailGroupTable.Columns.id, 0)
.InColumnValue(MailGroupTable.Columns.id_tenant, tenant)
.InColumnValue(MailGroupTable.Columns.date_created, createdTime)
.InColumnValue(MailGroupTable.Columns.id_address, addressDto.id)
.InColumnValue(MailGroupTable.Columns.address, addressName
+ '@' + domainName)
.Identity(0, 0, true);
var mailGroupId = db.ExecuteScalar<int>(insertGroupQuery);
AddAddressesToMailGroup(mailGroupId, addressDtoList.Select(dto => dto.id).ToList(), db);
return new MailGroupDto(mailGroupId, tenant, addressDto.id, addressDto, addressDtoList);
}
public void Save(int deviceID, PushNotification notification)
{
var insertQuery = new SqlInsert("push_notification")
.InColumnValue("device_id", deviceID)
.InColumnValue("module", notification.Module)
.InColumnValue("action", notification.Action)
.InColumnValue("item_type", notification.Item.Type)
.InColumnValue("item_id", notification.Item.ID)
.InColumnValue("item_description", notification.Item.Description)
.InColumnValue("queued_on", DateTime.UtcNow);
if (notification.ParentItem != null)
insertQuery
.InColumnValue("parent_type", notification.ParentItem.Type)
.InColumnValue("parent_id", notification.ParentItem.ID)
.InColumnValue("parent_description", notification.ParentItem.Description);
using (var db = GetDbManager())
{
db.ExecuteNonQuery(insertQuery);
}
}
public ReportTemplate SaveTemplate(ReportTemplate template)
{
if (template == null) throw new ArgumentNullException("template");
if (template.CreateOn == default(DateTime)) template.CreateOn = DateTime.Now;
if (string.IsNullOrEmpty(template.CreateBy)) template.CreateBy = SecurityContext.CurrentAccount.ID.ToString();
var insert = new SqlInsert(table, true)
.InColumns(columns)
.Values(
template.Id,
template.ReportType,
template.Name,
template.Filter.ToXml(),
template.Cron,
template.CreateBy,
TenantUtil.DateTimeToUtc(template.CreateOn),
Tenant,
template.AutoGenerated)
.Identity(0, 0, true);
template.Id = DbManager.ExecuteScalar<int>(insert);
return template;
}
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());
}
}
public static IEnumerable<string> Save(IEnumerable<string> ips, int tenant)
{
using (var db = new DbManager(dbId))
using (var tx = db.BeginTransaction())
{
var d = new SqlDelete(ipRestrictionsTable)
.Where("tenant", tenant);
db.ExecuteNonQuery(d);
var ipsList = ips.ToList();
foreach (var ip in ipsList)
{
var i = new SqlInsert(ipRestrictionsTable)
.InColumnValue("tenant", tenant)
.InColumnValue("ip", ip);
db.ExecuteNonQuery(i);
}
tx.Commit();
return ipsList;
}
}
public void AddAddressesToMailGroup(int groupId, List <int> addressIds, DbManager db)
{
if (groupId < 0)
{
throw new ArgumentException("Argument group_id less then zero.", "groupId");
}
if (addressIds == null || !addressIds.Any())
{
throw new ArgumentException("Address ids list must be not empty.", "addressIds");
}
if (db == null)
{
throw new ArgumentNullException("db");
}
var addAddressToMailgroupQuery = new SqlInsert(MailGroupXAddressesTable.Name)
.InColumns(MailGroupXAddressesTable.Columns.AddressId, MailGroupXAddressesTable.Columns.MailGroupId);
addressIds.ForEach(addressId => addAddressToMailgroupQuery.Values(addressId, groupId));
db.ExecuteNonQuery(addAddressToMailgroupQuery);
}
private bool SaveBillingInfo(int tenant, Tuple <int, DateTime> bi)
{
var inserted = false;
if (!Equals(bi, GetBillingInfo(tenant)))
{
using (var db = GetDb())
using (var tx = db.BeginTransaction())
{
// last record is not the same
var q = new SqlQuery("tenants_tariff").SelectCount().Where("tenant", tenant).Where("tariff", bi.Item1).Where("stamp", bi.Item2);
if (bi.Item2 == DateTime.MaxValue || db.ExecuteScalar <int>(q) == 0)
{
var i = new SqlInsert("tenants_tariff")
.InColumnValue("tenant", tenant)
.InColumnValue("tariff", bi.Item1)
.InColumnValue("stamp", bi.Item2);
db.ExecuteNonQuery(i);
cache.Remove(GetTariffCacheKey(tenant));
inserted = true;
}
tx.Commit();
}
}
if (inserted)
{
var t = tenantService.GetTenant(tenant);
if (t != null)
{
// update tenant.LastModified to flush cache in documents
tenantService.SaveTenant(t);
}
}
return(inserted);
}
public void SqlInsertTest()
{
var i = new SqlInsert("Table").InColumnValue("c1", 1);
Assert.AreEqual("insert into Table(c1) values (?)", i.ToString());
i = new SqlInsert("Table").ReplaceExists(true).InColumnValue("c1", 1);
Assert.AreEqual("replace into Table(c1) values (?)", i.ToString());
i = new SqlInsert("Table").IgnoreExists(true).InColumnValue("c1", 1);
Assert.AreEqual("insert ignore into Table(c1) values (?)", i.ToString());
i = new SqlInsert("Table").InColumns("c1", "c2").Values(1, 2);
Assert.AreEqual("insert into Table(c1,c2) values (?,?)", i.ToString());
i = new SqlInsert("Table").InColumns("c1", "c2").Values(1, 2, 3, 4);
Assert.AreEqual("insert into Table(c1,c2) values (?,?),(?,?)", i.ToString());
i = new SqlInsert("Table").InColumns("c1", "c2").Values(0, 2).Identity(0, 0, true);
Assert.AreEqual("insert into Table(c2) values (?); select @@identity", i.ToString());
i = new SqlInsert("Table").Values(0, 2);
Assert.AreEqual("insert into Table values (?,?)", i.ToString());
}
public virtual int SaveProviderInfo(string providerKey, string customerTitle, AuthData authData, FolderType folderType)
{
ProviderTypes prKey;
try
{
prKey = (ProviderTypes)Enum.Parse(typeof(ProviderTypes), providerKey, true);
}
catch (Exception)
{
throw new ArgumentException("Unrecognize ProviderType");
}
authData = GetEncodedAccesToken(authData, prKey);
if (!CheckProviderInfo(ToProviderInfo(0, providerKey, customerTitle, authData, SecurityContext.CurrentAccount.ID.ToString(), folderType, TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))))
{
throw new UnauthorizedAccessException(string.Format(FilesCommonResource.ErrorMassage_SecurityException_Auth, providerKey));
}
var queryInsert = new SqlInsert(TableTitle, true)
.InColumnValue("id", 0)
.InColumnValue("tenant_id", TenantID)
.InColumnValue("provider", prKey.ToString())
.InColumnValue("customer_title", Global.ReplaceInvalidCharsAndTruncate(customerTitle))
.InColumnValue("user_name", authData.Login)
.InColumnValue("password", EncryptPassword(authData.Password))
.InColumnValue("folder_type", (int)folderType)
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))
.InColumnValue("user_id", SecurityContext.CurrentAccount.ID.ToString())
.InColumnValue("token", authData.Token)
.InColumnValue("url", authData.Url)
.Identity(0, 0, true);
return(Int32.Parse(DbManager.ExecuteScalar <string>(queryInsert)));
}
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 static void CreateAuthor(Author author, IEnumerable<string> languages, string modules)
{
using (var dbManager = new DbManager("tmresource"))
{
var sqlInsert = new SqlInsert("res_authors", true)
.InColumnValue("login", author.Login)
.InColumnValue("password", author.Password)
.InColumnValue("isAdmin", author.IsAdmin);
dbManager.ExecuteNonQuery(sqlInsert);
var delete = new SqlDelete("res_authorslang").Where("authorLogin", author.Login);
dbManager.ExecuteNonQuery(delete);
delete = new SqlDelete("res_authorsfile").Where("authorLogin", author.Login);
dbManager.ExecuteNonQuery(delete);
foreach (var lang in languages)
{
sqlInsert = new SqlInsert("res_authorslang", true)
.InColumnValue("authorLogin", author.Login)
.InColumnValue("cultureTitle", lang);
dbManager.ExecuteNonQuery(sqlInsert);
}
var resFiles = GetAllFiles();
//project1:module1-access1,module2-access2;project2:module3-access3,module4-access4
foreach (var projectData in modules.Split(';').Select(project => project.Split(':')))
{
foreach (var mod in projectData[1].Split(','))
{
//var modData = mod.Split('-');
var fileid = resFiles.Where(r => r.ModuleName == mod && r.ProjectName == projectData[0]).Select(r => r.FileID).FirstOrDefault();
sqlInsert = new SqlInsert("res_authorsfile", true)
.InColumnValue("authorLogin", author.Login)
.InColumnValue("fileId", fileid); //.InColumnValue("writeAccess", Convert.ToBoolean(modData[1]));
dbManager.ExecuteNonQuery(sqlInsert);
}
}
}
}
public MailTag SaveMailTag(int id_tenant, string id_user, MailTag tag)
{
using (var db = GetDb())
{
using (var tx = db.BeginTransaction())
{
if (tag.Id == 0)
{
tag.Id = db.ExecuteScalar<int>(
new SqlInsert(MAIL_TAG)
.InColumnValue(TagFields.id, 0)
.InColumnValue(TagFields.id_tenant, id_tenant)
.InColumnValue(TagFields.id_user, id_user)
.InColumnValue(TagFields.name, tag.Name)
.InColumnValue(TagFields.style, tag.Style)
.InColumnValue(TagFields.addresses, string.Join(";", tag.Addresses.ToArray()))
.Identity(0, 0, true));
}
else
{
db.ExecuteNonQuery(
new SqlUpdate(MAIL_TAG)
.Set(TagFields.name, tag.Name)
.Set(TagFields.style, tag.Style)
.Set(TagFields.addresses, string.Join(";", tag.Addresses.ToArray()))
.Where(TagFields.id, tag.Id));
db.ExecuteNonQuery(
new SqlDelete(MAIL_TAG_ADDRESSES)
.Where(TagAddressFields.id_tag, tag.Id));
}
if (tag.Addresses.Any())
{
var insert_query =
new SqlInsert(MAIL_TAG_ADDRESSES)
.InColumns(TagAddressFields.id_tag,
TagAddressFields.address,
TagAddressFields.id_tenant);
tag.Addresses
.ForEach(addr =>
insert_query
.Values(tag.Id, addr, id_tenant));
db.ExecuteNonQuery(insert_query);
}
tx.Commit();
}
}
return tag;
}
public void ListCanLoadMoreThanOneResult()
{
using (var dbContext = NewDbTestContext())
using (var connection = SqlConnections.NewByKey("Serenity"))
{
var fld = FakeDisplayOrderRow.Fields;
new SqlDelete(FakeDisplayOrderRow.TableName)
.Execute(connection, ExpectedRows.Ignore);
var firstID = new SqlInsert(FakeDisplayOrderRow.TableName)
.Set(fld.GroupID, 777)
.Set(fld.IsActive, 1)
.Set(fld.DisplayOrder, 5)
.ExecuteAndGetID(connection);
var secondID = new SqlInsert(FakeDisplayOrderRow.TableName)
.Set(fld.GroupID, 777)
.Set(fld.IsActive, 1)
.Set(fld.DisplayOrder, 6)
.ExecuteAndGetID(connection);
var list = connection.List<FakeDisplayOrderRow>(new Criteria(fld.GroupID) == 777);
Assert.NotNull(list);
Assert.Equal(list.Count, 2);
Assert.True((list[0].ID == firstID && list[1].ID == secondID) ||
(list[0].ID == secondID && list[1].ID == firstID));
}
}
///*******************************************************************************
///NOMBRE DE LA FUNCIÓN : Migrar_Datos_Camara
///DESCRIPCIÓN : Lee los datos de la cámara y lo inserta en ope_accesos_camaras
///PARÁMETROS : P_Banco que contiene la informacion de el nuevo banco
///CREÓ : Héctor Gabriel Galicia Luna
///FECHA_CREO : 1 Diciembre 2014
///MODIFICÓ :
///FECHA_MODIFICO :
///CAUSA_MODIFICACIÓN :
///*******************************************************************************
public static DataTable Migrar_Datos_Camara_Datos(Cls_Cat_Accesos_Camara_Negocio P_Migracion)
{
StringBuilder Mi_Sql = new StringBuilder();
DateTime Fecha;
String Str_Cadena_Ip = String.Empty;
String Query = String.Empty;
DataTable Dt_Resultados = new DataTable();
DataTable Dt_Consulta = new DataTable();
DataTable Dt_Consultar_Existencia = new DataTable();
Boolean Transaccion_Activa = false;
WebRequest Solicitud_Request;
HttpWebResponse Respuesta_Response;
Stream Archivo_Respuesta;
StreamReader Lectura;
String Respuesta_De_Servidor;
String[] Archivo;
String SqlQuery;
String SqlInsert;
String[] Linea;
int No_Acceso;
object res;
Boolean Estatus = false;
try
{
Fecha = P_Migracion.P_Dtime_Fecha;
Conexion.Iniciar_Helper();
if (!Conexion.HelperGenerico.Estatus_Transaccion())
{
Conexion.HelperGenerico.Conexion_y_Apertura();
}
else
{
Transaccion_Activa = true;
}
Str_Cadena_Ip = "http://<CamIP>/local/VE170/rep.csv?";
String StartMonth = Fecha.ToString("MM");
String EndMonth = StartMonth;
String StartDay = Fecha.ToString("dd");
String EndDay = StartDay;
String StartHour = "0";
String EndHour = "23";
Query = "StartMonth=" + StartMonth;
Query += "&EndMonth=" + EndMonth;
Query += "&StartDay=" + StartDay;
Query += "&EndDay=" + EndDay;
Query += "&StartHour=" + StartHour;
Query += "&EndHour=" + EndHour;
Query += "&DetailLevel=H";
// se consultaran las camaras activas
Mi_Sql.Append("SELECT * FROM " + Cat_Camaras.Tabla_Cat_Camaras + " where " + Cat_Camaras.Campo_Estatus + " ='ACTIVO'");
Dt_Consulta = Conexion.HelperGenerico.Obtener_Data_Table(Mi_Sql.ToString());
Mi_Sql.Clear();
Mi_Sql.Append("delete from " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras);
Conexion.HelperGenerico.Ejecutar_NonQuery(Mi_Sql.ToString());
// inicion de la transaccion
Conexion.HelperGenerico.Iniciar_Transaccion();
foreach (DataRow Registro in Dt_Consulta.Rows)
{
//Mi_Sql.Clear();
//Mi_Sql.Append("Select " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + ".*");
//Mi_Sql.Append(" From " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras);
//Mi_Sql.Append(" where (" + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "." + Ope_Accesos_Camaras_Temp.Campo_Fecha_Hora + " >= '" + Fecha.ToString("yyyy-MM-dd") + " 00:00:00" + "'");
//Mi_Sql.Append(" and " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "." + Ope_Accesos_Camaras_Temp.Campo_Fecha_Hora + " <= '" + Fecha.ToString("yyyy-MM-dd") + " 23:59:59" + "'");
//Mi_Sql.Append(")");
//Mi_Sql.Append(" And " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "." + Ope_Accesos_Camaras_Temp.Campo_Camara_Id + "='" + Registro[Cat_Camaras.Campo_Camara_ID].ToString() + "'");
//Dt_Consultar_Existencia = Conexion.HelperGenerico.Obtener_Data_Table(Mi_Sql.ToString());
// valida que no exista informacion
//if (Dt_Consultar_Existencia != null && Dt_Consultar_Existencia.Rows.Count == 0)
//{
Str_Cadena_Ip = "http://<CamIP>/local/VE170/rep.csv?";
Str_Cadena_Ip = Str_Cadena_Ip.Replace("<CamIP>", Registro[Cat_Camaras.Campo_Url].ToString()) + Query;
Mi_Sql.Clear();
Mi_Sql.Append("INSERT INTO " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "(");
Mi_Sql.Append(Ope_Accesos_Camaras_Temp.Campo_No_Acceso + ",");
Mi_Sql.Append(Ope_Accesos_Camaras_Temp.Campo_Fecha_Hora + ",");
Mi_Sql.Append(Ope_Accesos_Camaras_Temp.Campo_Cantidad + ",");
Mi_Sql.Append(Ope_Accesos_Camaras_Temp.Campo_Cantidad_Salida + ",");
Mi_Sql.Append(Ope_Accesos_Camaras_Temp.Campo_Camara_Id + ",");
Mi_Sql.Append(Ope_Accesos_Camaras_Temp.Campo_Usuario_Creo + ",");
Mi_Sql.Append(Ope_Accesos_Camaras_Temp.Campo_Fecha_Creo + ")");
Mi_Sql.Append("VALUES('$No_Acceso', '$Fecha_Hora', $Cantidad, $Salida, '$Camara_Id', '$Usuario_Creo', NOW());");
Solicitud_Request = WebRequest.Create(Str_Cadena_Ip);
Solicitud_Request.Credentials = new NetworkCredential(Registro[Cat_Camaras.Campo_Usuario].ToString(), Registro[Cat_Camaras.Campo_Contrasenia].ToString());
Respuesta_Response = (HttpWebResponse)Solicitud_Request.GetResponse();
Archivo_Respuesta = Respuesta_Response.GetResponseStream();
Lectura = new StreamReader(Archivo_Respuesta);
Respuesta_De_Servidor = Lectura.ReadToEnd();
Archivo = Respuesta_De_Servidor.Replace("\r\n", "\n").Split('\n');
SqlQuery = Mi_Sql.ToString();
SqlInsert = String.Empty;
Conexion.Iniciar_Helper();
Conexion.HelperGenerico.Conexion_y_Apertura();
res = Conexion.HelperGenerico.Obtener_Escalar("SELECT IFNULL(MAX(" + Ope_Accesos_Camaras_Temp.Campo_No_Acceso + "), 0) FROM " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras);
No_Acceso = int.Parse(res.ToString());
for (int Cont_For = 5; Cont_For < Archivo.Length; Cont_For++)
{
No_Acceso++;
Linea = Archivo[Cont_For].Split(',');
if (Linea.Length != 3)
{
break;
}
if (Linea[1] != "n/a")
{
string Hora = DateTime.Parse(Linea[0]).ToString("HH");
string Fecha_Hora = Fecha.ToString("yyyy-MM-dd") + " " + Hora;
SqlInsert += SqlQuery;
SqlInsert = SqlInsert.Replace("$No_Acceso", String.Format("{0:0000000000}", +No_Acceso))
.Replace("$Fecha_Hora", Fecha_Hora);
//// validacion para la camara de entrada
//if (Registro[Cat_Camaras.Campo_Tipo].ToString() == "ENTRADA")
//{
SqlInsert = SqlInsert.Replace("$Cantidad", Linea[1]);
// SqlInsert = SqlInsert.Replace("$Salida", "0");
//}
//else
//{
// SqlInsert = SqlInsert.Replace("$Cantidad", "0");
SqlInsert = SqlInsert.Replace("$Salida", Linea[2]);
//}
SqlInsert = SqlInsert.Replace("$Camara_Id", Registro[Cat_Camaras.Campo_Camara_ID].ToString())
.Replace("$Usuario_Creo", MDI_Frm_Apl_Principal.Nombre_Usuario);
}
}// fin del for arcnivo
if (!String.IsNullOrEmpty(SqlInsert))
{
Conexion.HelperGenerico.Ejecutar_NonQuery(SqlInsert);
}
Lectura.Close();
Archivo_Respuesta.Close();
Respuesta_Response.Close();
//}//consulta de existencia de registros
} // fin del foreach
Mi_Sql.Clear();
Mi_Sql.Append("SELECT " + Cat_Camaras.Tabla_Cat_Camaras + "." + Cat_Camaras.Campo_Nombre);
Mi_Sql.Append(", " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "." + Ope_Accesos_Camaras_Temp.Campo_Fecha_Hora);
Mi_Sql.Append(", " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "." + Ope_Accesos_Camaras_Temp.Campo_Cantidad);
Mi_Sql.Append(", " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "." + Ope_Accesos_Camaras_Temp.Campo_Cantidad_Salida);
Mi_Sql.Append(" From " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras);
Mi_Sql.Append(" left outer join " + Cat_Camaras.Tabla_Cat_Camaras + " on " + Cat_Camaras.Tabla_Cat_Camaras + "." + Cat_Camaras.Campo_Camara_ID);
Mi_Sql.Append(" = " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "." + Ope_Accesos_Camaras_Temp.Campo_Camara_Id);
Mi_Sql.Append(" where (" + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "." + Ope_Accesos_Camaras_Temp.Campo_Fecha_Hora + " >= '" + Fecha.ToString("yyyy-MM-dd") + " 00:00:00" + "'");
Mi_Sql.Append(" and " + Ope_Accesos_Camaras_Temp.Tabla_Ope_Accesos_Camaras + "." + Ope_Accesos_Camaras_Temp.Campo_Fecha_Hora + " <= '" + Fecha.ToString("yyyy-MM-dd") + " 23:59:59" + "'");
Mi_Sql.Append(")");
Dt_Resultados = Conexion.HelperGenerico.Obtener_Data_Table(Mi_Sql.ToString());
if (!Transaccion_Activa)
{
Conexion.HelperGenerico.Terminar_Transaccion();
}
}
catch (Exception e)
{
if (!Transaccion_Activa)
{
Conexion.HelperGenerico.Abortar_Transaccion();
}
throw new Exception("Cámara : " + e.Message);
}
finally
{
if (!Transaccion_Activa)
{
Conexion.HelperGenerico.Cerrar_Conexion();
}
}
return(Dt_Resultados);
}
public void SetShare(FileShareRecord r)
{
using (var DbManager = GetDb())
{
if (r.Share == FileShare.None)
{
var entryId = MappingID(r.EntryId);
if (entryId == null)
{
return;
}
using (var tx = DbManager.BeginTransaction())
{
entryId = entryId.ToString();
var files = new List <object>();
if (r.EntryType == FileEntryType.Folder)
{
var folders =
DbManager.ExecuteList(new SqlQuery("files_folder_tree").Select("folder_id").Where("parent_id", entryId))
.ConvertAll(o => (int)o[0]);
files.AddRange(DbManager.ExecuteList(Query("files_file").Select("id").Where(Exp.In("folder_id", folders))).
ConvertAll(o => o[0]));
var d1 = new SqlDelete("files_security")
.Where("tenant_id", r.Tenant)
.Where(Exp.In("entry_id", folders))
.Where("entry_type", (int)FileEntryType.Folder)
.Where("subject", r.Subject.ToString());
DbManager.ExecuteNonQuery(d1);
}
else
{
files.Add(entryId);
}
if (0 < files.Count)
{
var d2 = new SqlDelete("files_security")
.Where("tenant_id", r.Tenant)
.Where(Exp.In("entry_id", files))
.Where("entry_type", (int)FileEntryType.File)
.Where("subject", r.Subject.ToString());
DbManager.ExecuteNonQuery(d2);
}
tx.Commit();
}
}
else
{
var i = new SqlInsert("files_security", true)
.InColumnValue("tenant_id", r.Tenant)
.InColumnValue("entry_id", MappingID(r.EntryId, true).ToString())
.InColumnValue("entry_type", (int)r.EntryType)
.InColumnValue("subject", r.Subject.ToString())
.InColumnValue("owner", r.Owner.ToString())
.InColumnValue("security", (int)r.Share)
.InColumnValue("timestamp", DateTime.UtcNow);
DbManager.ExecuteNonQuery(i);
}
}
}
public void InsertCommand(SqlInsert slqInsert, T item)
{
InvokeAction(_mappedColumn, (name, value) => slqInsert.InColumnValue(name, value), item);
}
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);
}
}
public Tenant SaveTenant(Tenant t)
{
if (t == null)
{
throw new ArgumentNullException("tenant");
}
using (var db = GetDb())
using (var tx = db.BeginTransaction())
{
if (!string.IsNullOrEmpty(t.MappedDomain))
{
var baseUrl = TenantUtil.GetBaseDomain(t.HostedRegion);
if (baseUrl != null && t.MappedDomain.EndsWith("." + baseUrl, StringComparison.InvariantCultureIgnoreCase))
{
ValidateDomain(db, t.MappedDomain.Substring(0, t.MappedDomain.Length - baseUrl.Length - 1), t.TenantId, false);
}
else
{
ValidateDomain(db, t.MappedDomain, t.TenantId, false);
}
}
if (t.TenantId == Tenant.DEFAULT_TENANT)
{
var q = new SqlQuery("tenants_version")
.Select("id")
.Where(Exp.Eq("default_version", 1) | Exp.Eq("id", 0))
.OrderBy(1, false)
.SetMaxResults(1);
t.Version = db.ExecuteScalar <int>(q);
var i = new SqlInsert("tenants_tenants", true)
.InColumnValue("id", t.TenantId)
.InColumnValue("alias", t.TenantAlias.ToLowerInvariant())
.InColumnValue("mappeddomain", !string.IsNullOrEmpty(t.MappedDomain) ? t.MappedDomain.ToLowerInvariant() : null)
.InColumnValue("version", t.Version)
.InColumnValue("version_changed", t.VersionChanged)
.InColumnValue("name", t.Name ?? t.TenantAlias)
.InColumnValue("language", t.Language)
.InColumnValue("timezone", t.TimeZone.Id)
.InColumnValue("owner_id", t.OwnerId.ToString())
.InColumnValue("trusteddomains", t.GetTrustedDomains())
.InColumnValue("trusteddomainsenabled", (int)t.TrustedDomainsType)
.InColumnValue("creationdatetime", t.CreatedDateTime)
.InColumnValue("status", (int)t.Status)
.InColumnValue("statuschanged", t.StatusChangeDate)
.InColumnValue("payment_id", t.PaymentId)
.InColumnValue("last_modified", t.LastModified = DateTime.UtcNow)
.InColumnValue("industry", (int)t.Industry)
.Identity <int>(0, t.TenantId, true);
t.TenantId = db.ExecuteScalar <int>(i);
}
else
{
var u = new SqlUpdate("tenants_tenants")
.Set("alias", t.TenantAlias.ToLowerInvariant())
.Set("mappeddomain", !string.IsNullOrEmpty(t.MappedDomain) ? t.MappedDomain.ToLowerInvariant() : null)
.Set("version", t.Version)
.Set("version_changed", t.VersionChanged)
.Set("name", t.Name ?? t.TenantAlias)
.Set("language", t.Language)
.Set("timezone", t.TimeZone.Id)
.Set("owner_id", t.OwnerId.ToString())
.Set("trusteddomains", t.GetTrustedDomains())
.Set("trusteddomainsenabled", (int)t.TrustedDomainsType)
.Set("creationdatetime", t.CreatedDateTime)
.Set("status", (int)t.Status)
.Set("statuschanged", t.StatusChangeDate)
.Set("payment_id", t.PaymentId)
.Set("last_modified", t.LastModified = DateTime.UtcNow)
.Set("industry", (int)t.Industry)
.Where("id", t.TenantId);
db.ExecuteNonQuery(u);
}
if (string.IsNullOrEmpty(t.PartnerId) && string.IsNullOrEmpty(t.AffiliateId))
{
var d = new SqlDelete("tenants_partners").Where("tenant_id", t.TenantId);
db.ExecuteNonQuery(d);
}
else
{
var i = new SqlInsert("tenants_partners", true)
.InColumnValue("tenant_id", t.TenantId)
.InColumnValue("partner_id", t.PartnerId)
.InColumnValue("affiliate_id", t.AffiliateId);
db.ExecuteNonQuery(i);
}
tx.Commit();
}
//CalculateTenantDomain(t);
return(t);
}
protected override SqlExpression VisitInsert(SqlInsert expr)
{
return(expr);
}
public MailboxWithAddressDto CreateMailbox(IAccount teamlab_account,
string full_address, string password, string address_name,
DateTime address_created_date,
int domain_id, string domain_name, bool is_verified, DbManager db)
{
if (teamlab_account == null)
{
throw new ArgumentNullException("teamlab_account");
}
if (string.IsNullOrEmpty(full_address))
{
throw new ArgumentNullException("full_address");
}
if (string.IsNullOrEmpty(password))
{
throw new ArgumentNullException("password");
}
if (string.IsNullOrEmpty(address_name))
{
throw new ArgumentNullException("address_name");
}
if (domain_id < 0)
{
throw new ArgumentException("Argument domain_id less then zero.", "domain_id");
}
if (string.IsNullOrEmpty(domain_name))
{
throw new ArgumentNullException("domain_name");
}
if (db == null)
{
throw new ArgumentNullException("db");
}
const string mailbox_alias = "mb";
const string address_alias = "a";
var user_teamlab_mailbox_count_query = new SqlQuery(MailboxTable.name.Alias(mailbox_alias))
.InnerJoin(AddressTable.name.Alias(address_alias),
Exp.EqColumns(MailboxTable.Columns.id.Prefix(mailbox_alias),
AddressTable.Columns.id_mailbox.Prefix(address_alias)))
.SelectCount()
.Where(MailboxTable.Columns.is_teamlab_mailbox.Prefix(mailbox_alias), true)
.Where(MailboxTable.Columns.is_removed.Prefix(mailbox_alias), false)
.Where(MailboxTable.Columns.id_tenant.Prefix(mailbox_alias), tenant_id)
.Where(MailboxTable.Columns.id_user.Prefix(mailbox_alias), teamlab_account.ID.ToString())
.Where(AddressTable.Columns.id_domain.Prefix(address_alias), domain_id)
.Where(AddressTable.Columns.is_alias.Prefix(address_alias), false);
var user_mailboxes_count = db.ExecuteScalar <int>(user_teamlab_mailbox_count_query);
if (user_mailboxes_count >= _mailboxCountLimit)
{
throw new ArgumentOutOfRangeException("address_name", string.Format("Count of user's mailboxes must be less or equal {0}.", _mailboxCountLimit));
}
var server_information = GetTenantServer(db);
var date_created = DateTime.UtcNow.ToDbStyle();
var insert_query = new SqlInsert(MailboxTable.name)
.InColumnValue(MailboxTable.Columns.id, 0)
.InColumnValue(MailboxTable.Columns.id_tenant, tenant_id)
.InColumnValue(MailboxTable.Columns.id_user, teamlab_account.ID.ToString())
.InColumnValue(MailboxTable.Columns.address, full_address) //Todo: Talk with AK about this.
.InColumnValue(MailboxTable.Columns.name, teamlab_account.Name)
.InColumnValue(MailboxTable.Columns.password, InstanceCrypto.Encrypt(password))
.InColumnValue(MailboxTable.Columns.msg_count_last, 0)
.InColumnValue(MailboxTable.Columns.smtp_password, InstanceCrypto.Encrypt(password))
.InColumnValue(MailboxTable.Columns.size_last, 0)
.InColumnValue(MailboxTable.Columns.login_delay, Config.LoginDelayInSeconds)
.InColumnValue(MailboxTable.Columns.enabled, true)
.InColumnValue(MailboxTable.Columns.imap, true)
.InColumnValue(MailboxTable.Columns.service_type, 0)
.InColumnValue(MailboxTable.Columns.refresh_token, null)
.InColumnValue(MailboxTable.Columns.date_created, date_created)
.InColumnValue(MailboxTable.Columns.id_smtp_server, server_information.smtp_settings_id)
.InColumnValue(MailboxTable.Columns.id_in_server, server_information.imap_settings_id)
.InColumnValue(MailboxTable.Columns.is_teamlab_mailbox, true)
.Identity(0, 0, true);
var result = db.ExecuteScalar <int>(insert_query);
var created_mailbox = new MailboxDto(result, teamlab_account.ID.ToString(), tenant_id,
full_address);
var address_dal = new MailAddressDal(tenant_id);
var mailbox_address = address_dal.AddMailboxAddress(created_mailbox.id, address_name, address_created_date,
domain_id, domain_name, is_verified, db);
var result_dto = new MailboxWithAddressDto(created_mailbox, mailbox_address);
return(result_dto);
}
public CreatedResponse CreateComputer(ComputerDto computer)
{
var response = new SqlInsert <Computer>(ToComputer(computer)).Run(this.DatabaseSession);
return(response);
}
/// <summary>
/// Visits the specified <see cref="SqlInsert"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlInsert expression)
{
}
internal virtual SqlStatement VisitInsert(SqlInsert insert) {
insert.Table = (SqlTable)this.Visit(insert.Table);
insert.Expression = this.VisitExpression(insert.Expression);
insert.Row = (SqlRow)this.Visit(insert.Row);
return insert;
}
public void SetShare(FileShareRecord r)
{
if (r.Share == FileShare.None)
{
var entryId = (MappingID(r.EntryId) ?? "").ToString();
if (string.IsNullOrEmpty(entryId))
{
return;
}
using (var tx = dbManager.BeginTransaction())
{
var files = new List <string>();
if (r.EntryType == FileEntryType.Folder)
{
var folders = new List <string>();
int intEntryId;
if (int.TryParse(entryId, out intEntryId))
{
var foldersInt = dbManager.ExecuteList(new SqlQuery("files_folder_tree").Select("folder_id").Where("parent_id", entryId))
.ConvertAll(o => o[0]);
folders.AddRange(foldersInt.Select(folderInt => folderInt.ToString()));
files.AddRange(dbManager.ExecuteList(Query("files_file").Select("id").Where(Exp.In("folder_id", foldersInt))).
ConvertAll(o => o[0].ToString()));
}
else
{
folders.Add(entryId);
}
var d1 = new SqlDelete("files_security")
.Where("tenant_id", r.Tenant)
.Where(Exp.In("entry_id", folders))
.Where("entry_type", (int)FileEntryType.Folder)
.Where("subject", r.Subject.ToString());
dbManager.ExecuteNonQuery(d1);
}
else
{
files.Add(entryId);
}
if (0 < files.Count)
{
var d2 = new SqlDelete("files_security")
.Where("tenant_id", r.Tenant)
.Where(Exp.In("entry_id", files))
.Where("entry_type", (int)FileEntryType.File)
.Where("subject", r.Subject.ToString());
dbManager.ExecuteNonQuery(d2);
}
tx.Commit();
}
}
else
{
var i = new SqlInsert("files_security", true)
.InColumnValue("tenant_id", r.Tenant)
.InColumnValue("entry_id", MappingID(r.EntryId, true).ToString())
.InColumnValue("entry_type", (int)r.EntryType)
.InColumnValue("subject", r.Subject.ToString())
.InColumnValue("owner", r.Owner.ToString())
.InColumnValue("security", (int)r.Share)
.InColumnValue("timestamp", DateTime.UtcNow);
dbManager.ExecuteNonQuery(i);
}
}
public void MailContactsSave(IDbManager db, int tenant, string user, MailMessageItem mail)
{
try
{
var contacts = new List <string> {
mail.From
};
var spliter = new[] { "," };
if (!string.IsNullOrEmpty(mail.To))
{
contacts = contacts
.Concat(mail.To.Split(spliter, StringSplitOptions.RemoveEmptyEntries))
.Distinct()
.ToList();
}
if (!string.IsNullOrEmpty(mail.ReplyTo))
{
contacts = contacts
.Concat(mail.ReplyTo.Split(spliter, StringSplitOptions.RemoveEmptyEntries))
.Distinct()
.ToList();
}
if (!string.IsNullOrEmpty(mail.Cc))
{
contacts = contacts
.Concat(mail.Cc.Split(spliter, StringSplitOptions.RemoveEmptyEntries))
.Distinct()
.ToList();
}
if (!string.IsNullOrEmpty(mail.Bcc))
{
contacts = contacts
.Concat(mail.Bcc.Split(spliter, StringSplitOptions.RemoveEmptyEntries))
.Distinct()
.ToList();
}
if (!contacts.Any())
{
return;
}
var valid_contacts = (from contact in contacts
let address = Parser.ParseAddress(contact.Trim())
where Validator.ValidateSyntax(address.Email) &&
(!contact.Contains(' ') || !string.IsNullOrEmpty(address.Name)) &&
MailContactExists(db, tenant, user, address.Name, address.Email) < 1
select address).ToList();
if (!valid_contacts.Any())
{
return;
}
var last_modified = DateTime.Now;
var insert_query = new SqlInsert(MAIL_CONTACTS)
.InColumns(MailContactsFields.id_user,
MailContactsFields.id_tenant,
MailContactsFields.name,
MailContactsFields.address,
MailContactsFields.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("MailContactsSave(tenant={0}, userId='{1}', mail_id={2}) Exception:\r\n{3}\r\n",
tenant, user, mail.Id, e.ToString());
}
}
public Tenant SaveTenant(Tenant t)
{
if (t == null)
{
throw new ArgumentNullException("tenant");
}
ExecAction(db =>
{
t.LastModified = DateTime.UtcNow;
if (t.TenantId != 0)
{
// TenantId == 0 in open source version
ValidateDomain(db, t.TenantAlias, t.TenantId, true);
}
if (!string.IsNullOrEmpty(t.MappedDomain))
{
var baseUrl = ConfigurationManager.AppSettings["core.base-domain"];
if (baseUrl != null && t.MappedDomain.EndsWith("." + baseUrl, StringComparison.InvariantCultureIgnoreCase))
{
ValidateDomain(db, t.MappedDomain.Substring(0, t.MappedDomain.Length - baseUrl.Length - 1), t.TenantId, false);
}
else
{
ValidateDomain(db, t.MappedDomain, t.TenantId, false);
}
}
if (t.TenantId == Tenant.DEFAULT_TENANT)
{
var i = new SqlInsert("tenants_tenants", true)
.InColumnValue("id", t.TenantId)
.InColumnValue("alias", t.TenantAlias.ToLowerInvariant())
.InColumnValue("mappeddomain", !string.IsNullOrEmpty(t.MappedDomain) ? t.MappedDomain.ToLowerInvariant() : null)
.InColumnValue("name", t.Name ?? t.TenantAlias)
.InColumnValue("language", t.Language)
.InColumnValue("timezone", t.TimeZone.Id)
.InColumnValue("owner_id", t.OwnerId.ToString())
.InColumnValue("trusteddomains", t.GetTrustedDomains())
.InColumnValue("trusteddomainsenabled", (int)t.TrustedDomainsType)
.InColumnValue("creationdatetime", t.CreatedDateTime)
.InColumnValue("status", (int)t.Status)
.InColumnValue("statuschanged", t.StatusChangeDate)
.InColumnValue("last_modified", t.LastModified)
.Identity <int>(0, t.TenantId, true);
t.TenantId = db.ExecScalar <int>(i);
t.Version = db.ExecScalar <int>(new SqlQuery("tenants_tenants").Select("version").Where("id", t.TenantId));
}
else
{
var u = new SqlUpdate("tenants_tenants")
.Set("alias", t.TenantAlias.ToLowerInvariant())
.Set("mappeddomain", !string.IsNullOrEmpty(t.MappedDomain) ? t.MappedDomain.ToLowerInvariant() : null)
.Set("version", t.Version)
.Set("name", t.Name ?? t.TenantAlias)
.Set("language", t.Language)
.Set("timezone", t.TimeZone.Id)
.Set("owner_id", t.OwnerId.ToString())
.Set("trusteddomains", t.GetTrustedDomains())
.Set("trusteddomainsenabled", (int)t.TrustedDomainsType)
.Set("creationdatetime", t.CreatedDateTime)
.Set("status", (int)t.Status)
.Set("statuschanged", t.StatusChangeDate)
.Set("last_modified", t.LastModified)
.Where("id", t.TenantId);
db.ExecNonQuery(u);
}
});
CalculateTenantDomain(t);
return(t);
}
public void Values_IsNotNullWhenFirstCalled()
{
var insert = new SqlInsert();
Assert.NotNull(insert.Values);
}
public IEnumerable <Tag> GetNewTags(Guid subject, params FileEntry[] fileEntries)
{
using (var DbManager = GetDb())
{
var result = new List <Tag>();
if (fileEntries == null || !fileEntries.Any())
{
return(result);
}
using (var tx = DbManager.BeginTransaction())
{
var sqlQuery = Query("files_tag ft")
.Select("ft.name",
"ft.flag",
"ft.owner",
"ftl.entry_id",
"ftl.entry_type",
"ftl.tag_count",
"ft.id")
.Distinct()
.InnerJoin("files_tag_link ftl",
Exp.EqColumns("ft.tenant_id", "ftl.tenant_id") &
Exp.EqColumns("ft.id", "ftl.tag_id"))
.Where(Exp.Eq("ft.flag", (int)TagType.New));
if (subject != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("ft.owner", subject));
}
const string sqlQueryStr =
@"
CREATE TEMPORARY TABLE IF NOT EXISTS `files_tag_temporary` (
`tenant_id` INT(10) NOT NULL,
`entry_id` VARCHAR(255) NOT NULL,
`entry_type` INT(10) NOT NULL,
PRIMARY KEY (`tenant_id`, `entry_id`, `entry_type`)
);";
DbManager.ExecuteNonQuery(sqlQueryStr);
while (fileEntries.Any())
{
var insertQuery = new SqlInsert("files_tag_temporary")
.InColumns(new[] { GetTenantColumnName("files_tag_temporary"), "entry_id", "entry_type" });
foreach (var fileEntrie in fileEntries.Take(100))
{
if (fileEntrie != null)
{
insertQuery.Values(new[]
{
TenantID,
MappingID(fileEntrie.ID),
(fileEntrie is File) ? (int)FileEntryType.File : (int)FileEntryType.Folder
});
}
}
DbManager.ExecuteNonQuery(insertQuery);
fileEntries = fileEntries.Skip(100).ToArray();
}
var resultSql = sqlQuery
.InnerJoin("files_tag_temporary ftt", Exp.EqColumns("ftl.tenant_id", "ftt.tenant_id") &
Exp.EqColumns("ftl.entry_id", "ftt.entry_id") &
Exp.EqColumns("ftl.entry_type", "ftt.entry_type"));
result = DbManager.ExecuteList(resultSql).ConvertAll(ToTag).Where(x => x.EntryId != null).ToList();
DbManager.ExecuteNonQuery(Delete("files_tag_temporary"));
tx.Commit();
}
return(result);
}
}
public void ExpressionType_ReturnsSelect()
{
var query = new SqlInsert();
Assert.Equal(SqlExpressionType.Insert, query.ExpressionType);
}
private void MarkChainAsCrmLinked(DbManager db, string id_chain, int id_mailbox, int id_tenant, IEnumerable<CrmContactEntity> contact_ids)
{
var add_new_link_query = new SqlInsert(ChainXCrmContactEntity.name)
.InColumns(ChainXCrmContactEntity.Columns.id_chain,
ChainXCrmContactEntity.Columns.id_mailbox,
ChainXCrmContactEntity.Columns.id_tenant,
ChainXCrmContactEntity.Columns.entity_id,
ChainXCrmContactEntity.Columns.entity_type);
foreach (var contact_entity in contact_ids)
{
add_new_link_query.Values(id_chain, id_mailbox, id_tenant, contact_entity.Id, contact_entity.Type);
}
db.ExecuteNonQuery(add_new_link_query);
}
protected virtual SqlExpression VisitInsert(SqlInsert expr)
{
return(expr);
}
internal override SqlStatement VisitInsert(SqlInsert sin)
{
base.VisitInsert(sin);
sin.Expression = this.FlattenSelection(sin.Row, true, sin.Expression);
return(sin);
}
internal override SqlStatement VisitInsert(SqlInsert si) {
if (si.OutputKey != null) {
sb.Append("DECLARE @output TABLE(");
this.WriteName(si.OutputKey.Name);
sb.Append(" ");
sb.Append(si.OutputKey.SqlType.ToQueryString());
sb.Append(")");
this.NewLine();
if (si.OutputToLocal) {
sb.Append("DECLARE @id ");
sb.Append(si.OutputKey.SqlType.ToQueryString());
this.NewLine();
}
}
sb.Append("INSERT INTO ");
this.Visit(si.Table);
if (si.Row.Columns.Count != 0) {
// INSERT INTO table (...columns...) VALUES (...values...)
sb.Append("(");
for (int i = 0, n = si.Row.Columns.Count; i < n; i++) {
if (i > 0) sb.Append(", ");
this.WriteName(si.Row.Columns[i].Name);
}
sb.Append(")");
}
if (si.OutputKey != null) {
this.NewLine();
sb.Append("OUTPUT INSERTED.");
this.WriteName(si.OutputKey.MetaMember.MappedName);
sb.Append(" INTO @output");
}
if (si.Row.Columns.Count == 0) {
sb.Append(" DEFAULT VALUES");
}
else {
// VALUES (...values...)
this.NewLine();
sb.Append("VALUES (");
if (this.isDebugMode && si.Row.Columns.Count == 0) {
this.Visit(si.Expression);
} else {
for (int i = 0, n = si.Row.Columns.Count; i < n; i++) {
if (i > 0) sb.Append(", ");
this.Visit( si.Row.Columns[i].Expression);
}
}
sb.Append(")");
}
if (si.OutputKey != null) {
this.NewLine();
if (si.OutputToLocal) {
sb.Append("SELECT @id = ");
sb.Append(si.OutputKey.Name);
sb.Append(" FROM @output");
}
else {
sb.Append("SELECT ");
this.WriteName(si.OutputKey.Name);
sb.Append(" FROM @output");
}
}
return si;
}
/// <summary>
/// Visits the specified <see cref="SqlInsert"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlInsert expression)
{
}
private void button1_Click(object sender, EventArgs e)
{
var operStr = textBox2.Text.Trim();
var sn = textBox1.Text.ToUpper().Trim();
var csn = textBox3.Text.ToUpper().Trim();
var pid = cbProductID.SelectedValue.CastTo(Guid.Empty);
if (pid == Guid.Empty)
{
AppFramework.Context.ShowError("请选择一个产品");
return;
}
if (string.IsNullOrEmpty(operStr))
{
textBox2.Focus();
textBox2.SelectAll();
AppFramework.Context.ShowError("请输入操作员信息");
return;
}
if (string.IsNullOrEmpty(sn))
{
textBox1.Focus();
textBox1.SelectAll();
return;
}
var db = MeasurementSystemSetting.GetDbManager();
if (string.IsNullOrEmpty(csn))
{
var ccsn = db.ExecuteScalar(
new SqlQuery("SerialNumberInfo")
.Select("CustomerSN")
.Where("SN", sn)
.Where("ProductID", pid)
);
if (ccsn != null)
{
if (!AppFramework.Context.ShowConfirm($"{sn}已经绑定，是否重新绑定钢管号码？"))
{
textBox1.Clear();
textBox3.Clear();
textBox1.Focus();
textBox1.SelectAll();
return;
}
textBox3.Text = ccsn.ToString();
}
textBox3.Focus();
textBox3.SelectAll();
return;
}
var cid = db.ExecuteScalar(
new SqlQuery("SerialNumberInfo")
.Select("CustomerSN")
.Where("CustomerSN", csn)
);
if (cid != null)
{
AppFramework.Context.ShowError($"钢管{cid}已经使用，请检查。");
textBox3.Clear();
textBox3.Focus();
textBox3.SelectAll();
return;
}
var sql = default(ISqlInstruction);
var sid = db.ExecuteScalar(
new SqlQuery("SerialNumberInfo")
.Select("ItemID")
.Where("SN", sn)
.Where("ProductID", pid)
);
if (sid == null)
{
sql = new SqlInsert("SerialNumberInfo")
.InColumnValue("ItemID", Guid.NewGuid())
.InColumnValue("ProductID", pid)
.InColumnValue("SN", sn)
.InColumnValue("CustomerSN", csn);
}
else
{
sql = new SqlUpdate("SerialNumberInfo")
.Set("CustomerSN", csn)
.Where("ItemID", sid);
}
db.ExecuteNonQuery(sql);
var row = testItem.NewRow();
row["SN"] = sn;
row["CustomerSN"] = csn;
row["Operator"] = operStr;
row["DateTime"] = DateTime.Now;
testItem.Rows.InsertAt(row, 0);
textBox1.Clear();
textBox3.Clear();
textBox1.Focus();
textBox1.SelectAll();
}
public MailboxWithAddressDto CreateMailbox(IAccount teamlabAccount,
string fullAddress, string password, string addressName,
DateTime addressCreatedDate,
int domainId, string domainName, bool isVerified, DbManager db)
{
if (teamlabAccount == null)
throw new ArgumentNullException("teamlabAccount");
if(string.IsNullOrEmpty(fullAddress))
throw new ArgumentNullException("fullAddress");
if (string.IsNullOrEmpty(password))
throw new ArgumentNullException("password");
if (string.IsNullOrEmpty(addressName))
throw new ArgumentNullException("addressName");
if (domainId < 0)
throw new ArgumentException("Argument domain_id less then zero.", "domainId");
if (string.IsNullOrEmpty(domainName))
throw new ArgumentNullException("domainName");
if (db == null)
throw new ArgumentNullException("db");
const string mailbox_alias = "mb";
const string address_alias = "a";
var userTeamlabMailboxCountQuery = new SqlQuery(MailboxTable.name.Alias(mailbox_alias))
.InnerJoin(AddressTable.name.Alias(address_alias),
Exp.EqColumns(MailboxTable.Columns.id.Prefix(mailbox_alias),
AddressTable.Columns.id_mailbox.Prefix(address_alias)))
.SelectCount()
.Where(MailboxTable.Columns.is_teamlab_mailbox.Prefix(mailbox_alias), true)
.Where(MailboxTable.Columns.is_removed.Prefix(mailbox_alias), false)
.Where(MailboxTable.Columns.id_tenant.Prefix(mailbox_alias), tenant)
.Where(MailboxTable.Columns.id_user.Prefix(mailbox_alias), teamlabAccount.ID.ToString())
.Where(AddressTable.Columns.id_domain.Prefix(address_alias), domainId)
.Where(AddressTable.Columns.is_alias.Prefix(address_alias), false);
var userMailboxesCount = db.ExecuteScalar<int>(userTeamlabMailboxCountQuery);
if (userMailboxesCount >= _mailboxCountLimit)
throw new ArgumentOutOfRangeException("addressName", string.Format("Count of user's mailboxes must be less or equal {0}.", _mailboxCountLimit));
var serverInformation = GetTenantServer(db);
var dateCreated = DateTime.UtcNow.ToDbStyle();
var insertQuery = new SqlInsert(MailboxTable.name)
.InColumnValue(MailboxTable.Columns.id, 0)
.InColumnValue(MailboxTable.Columns.id_tenant, tenant)
.InColumnValue(MailboxTable.Columns.id_user, teamlabAccount.ID.ToString())
.InColumnValue(MailboxTable.Columns.address, fullAddress)
.InColumnValue(MailboxTable.Columns.name, teamlabAccount.Name)
.InColumnValue(MailboxTable.Columns.password, InstanceCrypto.Encrypt(password))
.InColumnValue(MailboxTable.Columns.msg_count_last, 0)
.InColumnValue(MailboxTable.Columns.smtp_password, InstanceCrypto.Encrypt(password))
.InColumnValue(MailboxTable.Columns.size_last, 0)
.InColumnValue(MailboxTable.Columns.login_delay, Config.LoginDelayInSeconds)
.InColumnValue(MailboxTable.Columns.enabled, true)
.InColumnValue(MailboxTable.Columns.imap, true)
.InColumnValue(MailboxTable.Columns.service_type, 0)
.InColumnValue(MailboxTable.Columns.refresh_token, null)
.InColumnValue(MailboxTable.Columns.date_created, dateCreated)
.InColumnValue(MailboxTable.Columns.id_smtp_server, serverInformation.smtp_settings_id)
.InColumnValue(MailboxTable.Columns.id_in_server, serverInformation.imap_settings_id)
.InColumnValue(MailboxTable.Columns.is_teamlab_mailbox, true)
.Identity(0, 0, true);
var result = db.ExecuteScalar<int>(insertQuery);
var createdMailbox = new MailboxDto(result, teamlabAccount.ID.ToString(), tenant,
fullAddress);
var addressDal = new MailAddressDal(tenant);
var mailboxAddress = addressDal.AddMailboxAddress(createdMailbox.id, addressName, addressCreatedDate,
domainId, domainName, isVerified, db);
var resultDto = new MailboxWithAddressDto(createdMailbox, mailboxAddress);
return resultDto;
}
public static void UpdateInternalDatabase(string hostname, MailServerInfo mailServer)
{
DemandPermission();
using (var dbManager = GetDb())
using (var transaction = dbManager.BeginTransaction())
{
var insertQuery = new SqlInsert("mail_mailbox_provider")
.InColumnValue("id", 0)
.InColumnValue("name", hostname)
.Identity(0, 0, true);
var providerId = dbManager.ExecuteScalar <int>(insertQuery);
insertQuery = new SqlInsert("mail_mailbox_server")
.InColumnValue("id", 0)
.InColumnValue("id_provider", providerId)
.InColumnValue("type", "smtp")
.InColumnValue("hostname", hostname)
.InColumnValue("port", 587)
.InColumnValue("socket_type", "STARTTLS")
.InColumnValue("username", "%EMAILADDRESS%")
.InColumnValue("authentication", "")
.InColumnValue("is_user_data", false)
.Identity(0, 0, true);
var smtpServerId = dbManager.ExecuteScalar <int>(insertQuery);
insertQuery = new SqlInsert("mail_mailbox_server")
.InColumnValue("id", 0)
.InColumnValue("id_provider", providerId)
.InColumnValue("type", "imap")
.InColumnValue("hostname", hostname)
.InColumnValue("port", 143)
.InColumnValue("socket_type", "STARTTLS")
.InColumnValue("username", "%EMAILADDRESS%")
.InColumnValue("authentication", "")
.InColumnValue("is_user_data", false)
.Identity(0, 0, true);
var imapServerId = dbManager.ExecuteScalar <int>(insertQuery);
var selectQuery = new SqlQuery("mail_server_server")
.Select("id, smtp_settings_id, imap_settings_id")
.SetMaxResults(1);
var mailServerData = dbManager
.ExecuteList(selectQuery)
.Select(r => new[] { Convert.ToInt32(r[0]), Convert.ToInt32(r[1]), Convert.ToInt32(r[2]) })
.FirstOrDefault();
var connectionString = Newtonsoft.Json.JsonConvert.SerializeObject(mailServer);
insertQuery = new SqlInsert("mail_server_server")
.InColumnValue("id", 0)
.InColumnValue("mx_record", hostname)
.InColumnValue("connection_string", connectionString)
.InColumnValue("server_type", 2)
.InColumnValue("smtp_settings_id", smtpServerId)
.InColumnValue("imap_settings_id", imapServerId);
dbManager.ExecuteNonQuery(insertQuery);
if (mailServerData != null)
{
var deleteQuery = new SqlDelete("mail_server_server")
.Where(Exp.Eq("id", mailServerData[0]));
dbManager.ExecuteNonQuery(deleteQuery);
selectQuery = new SqlQuery("mail_mailbox_server")
.Select("id_provider")
.Where(Exp.Eq("id", mailServerData[1]))
.SetMaxResults(1);
providerId = dbManager
.ExecuteList(selectQuery)
.Select(r => Convert.ToInt32(r[0]))
.FirstOrDefault();
deleteQuery = new SqlDelete("mail_mailbox_provider")
.Where(Exp.Eq("id", providerId));
dbManager.ExecuteNonQuery(deleteQuery);
deleteQuery = new SqlDelete("mail_mailbox_server")
.Where(Exp.In("id", new[] { mailServerData[1], mailServerData[2] }));
dbManager.ExecuteNonQuery(deleteQuery);
selectQuery = new SqlQuery("mail_mailbox")
.Select("id")
.Where(Exp.Eq("id_smtp_server", mailServerData[1]))
.Where(Exp.Eq("id_in_server", mailServerData[2]));
var mailboxId = dbManager
.ExecuteList(selectQuery)
.Select(r => Convert.ToInt32(r[0]))
.ToArray();
var updateQuery = new SqlUpdate("mail_mailbox")
.Set("id_smtp_server", smtpServerId)
.Set("id_in_server", imapServerId)
.Where(Exp.In("id", mailboxId));
dbManager.ExecuteNonQuery(updateQuery);
}
transaction.Commit();
Cache.Remove(CacheKey);
}
}
public static void AddAuthorLang(string login, string cultureTitle)
{
using (var dbManager = new DbManager("tmresource"))
{
var sql = new SqlInsert("res_authorslang")
.InColumnValue("cultureTitle", cultureTitle)
.InColumnValue("authorLogin", login);
dbManager.ExecuteNonQuery(sql);
}
}
public MailboxWithAddressDto CreateMailbox(IAccount teamlabAccount, string name,
string fullAddress, string password, string addressName,
DateTime addressCreatedDate,
int domainId, string domainName, bool isVerified, DbManager db)
{
if (teamlabAccount == null)
{
throw new ArgumentNullException("teamlabAccount");
}
if (string.IsNullOrEmpty(fullAddress))
{
throw new ArgumentNullException("fullAddress");
}
if (string.IsNullOrEmpty(password))
{
throw new ArgumentNullException("password");
}
if (string.IsNullOrEmpty(addressName))
{
throw new ArgumentNullException("addressName");
}
if (domainId < 0)
{
throw new ArgumentException("Argument domain_id less then zero.", "domainId");
}
if (string.IsNullOrEmpty(domainName))
{
throw new ArgumentNullException("domainName");
}
if (db == null)
{
throw new ArgumentNullException("db");
}
const string mailbox_alias = "mb";
const string address_alias = "a";
var userTeamlabMailboxCountQuery = new SqlQuery(MailboxTable.Name.Alias(mailbox_alias))
.InnerJoin(AddressTable.Name.Alias(address_alias),
Exp.EqColumns(MailboxTable.Columns.Id.Prefix(mailbox_alias),
AddressTable.Columns.MailboxId.Prefix(address_alias)))
.SelectCount()
.Where(MailboxTable.Columns.IsTeamlabMailbox.Prefix(mailbox_alias), true)
.Where(MailboxTable.Columns.IsRemoved.Prefix(mailbox_alias), false)
.Where(MailboxTable.Columns.Tenant.Prefix(mailbox_alias), tenant)
.Where(MailboxTable.Columns.User.Prefix(mailbox_alias), teamlabAccount.ID.ToString())
.Where(AddressTable.Columns.DomainId.Prefix(address_alias), domainId)
.Where(AddressTable.Columns.IsAlias.Prefix(address_alias), false);
var userMailboxesCount = db.ExecuteScalar <int>(userTeamlabMailboxCountQuery);
if (userMailboxesCount >= _mailboxCountLimit)
{
throw new ArgumentOutOfRangeException("addressName", string.Format("Count of user's mailboxes must be less or equal {0}.", _mailboxCountLimit));
}
var serverInformation = GetTenantServer(db);
var dateCreated = DateTime.UtcNow;
var insertQuery = new SqlInsert(MailboxTable.Name)
.InColumnValue(MailboxTable.Columns.Id, 0)
.InColumnValue(MailboxTable.Columns.Tenant, tenant)
.InColumnValue(MailboxTable.Columns.User, teamlabAccount.ID.ToString())
.InColumnValue(MailboxTable.Columns.Address, fullAddress)
.InColumnValue(MailboxTable.Columns.AddressName, name)
.InColumnValue(MailboxTable.Columns.Password, InstanceCrypto.Encrypt(password))
.InColumnValue(MailboxTable.Columns.MsgCountLast, 0)
.InColumnValue(MailboxTable.Columns.SmtpPassword, InstanceCrypto.Encrypt(password))
.InColumnValue(MailboxTable.Columns.SizeLast, 0)
.InColumnValue(MailboxTable.Columns.LoginDelay, Config.LoginDelayInSeconds)
.InColumnValue(MailboxTable.Columns.Enabled, true)
.InColumnValue(MailboxTable.Columns.Imap, true)
.InColumnValue(MailboxTable.Columns.OAuthType, AuthorizationServiceType.None)
.InColumnValue(MailboxTable.Columns.OAuthToken, null)
.InColumnValue(MailboxTable.Columns.DateCreated, dateCreated)
.InColumnValue(MailboxTable.Columns.SmtpServerId, serverInformation.smtp_settings_id)
.InColumnValue(MailboxTable.Columns.ServerId, serverInformation.imap_settings_id)
.InColumnValue(MailboxTable.Columns.IsTeamlabMailbox, true)
.Identity(0, 0, true);
var result = db.ExecuteScalar <int>(insertQuery);
var createdMailbox = new MailboxDto(result, teamlabAccount.ID.ToString(), tenant,
fullAddress, name);
var addressDal = new MailAddressDal(tenant);
var mailboxAddress = addressDal.AddMailboxAddress(createdMailbox.id, addressName, addressCreatedDate,
domainId, domainName, isVerified, db);
var resultDto = new MailboxWithAddressDto(createdMailbox, mailboxAddress);
return(resultDto);
}
private int CreateAlert(int tenant, string user, int mailboxId, AlertTypes type, object data = null)
{
using (var db = GetDb())
{
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 void TableIsNotSetTest2()
{
SqlInsert insert = SqlDml.Insert();
Assert.Throws <SqlCompilerException>(() => sqlDriver.Compile(insert));
}
// Method for updating chain flags, date and length.
private void UpdateChain(IDbManager db, string chainId, int folder, int mailboxId, int tenant, string user)
{
if (string.IsNullOrEmpty(chainId))
{
return;
}
var getChainQuery = new SqlQuery(MailTable.Name)
.SelectCount()
.SelectMax(MailTable.Columns.DateSent)
.SelectMax(MailTable.Columns.Unread)
.SelectMax(MailTable.Columns.AttachCount)
.SelectMax(MailTable.Columns.Importance)
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.IsRemoved, 0)
.Where(MailTable.Columns.ChainId, chainId)
.Where(MailTable.Columns.MailboxId, mailboxId)
.Where(MailTable.Columns.Folder, folder);
var chain = db.ExecuteList(getChainQuery)
.ConvertAll(x => new
{
length = Convert.ToInt32(x[0]),
date = Convert.ToDateTime(x[1]),
unread = Convert.ToBoolean(x[2]),
attach_count = Convert.ToInt32(x[3]),
importance = Convert.ToBoolean(x[4])
})
.FirstOrDefault();
if (chain == null)
{
throw new InvalidDataException("Conversation is absent in MAIL_MAIL");
}
var getStoredChainInfoQuery = new SqlQuery(ChainTable.Name)
.Select(ChainTable.Columns.Unread)
.Where(GetUserWhere(user, tenant))
.Where(ChainTable.Columns.Id, chainId)
.Where(ChainTable.Columns.Folder, folder)
.Where(MailTable.Columns.MailboxId, mailboxId);
var storedChainInfo = db.ExecuteList(getStoredChainInfoQuery);
var chainUnreadFlag = storedChainInfo.Any() && Convert.ToBoolean(storedChainInfo.First()[0]);
if (0 == chain.length)
{
var deleteFromChainQuery = new SqlDelete(ChainTable.Name)
.Where(GetUserWhere(user, tenant))
.Where(ChainTable.Columns.Id, chainId)
.Where(ChainTable.Columns.MailboxId, mailboxId)
.Where(ChainTable.Columns.Folder, folder);
var result = db.ExecuteNonQuery(deleteFromChainQuery);
_log.Debug("UpdateChain() row deleted from chain table tenant='{0}', user_id='{1}', id_mailbox='{2}', folder='{3}', chain_id='{4}'",
tenant, user, mailboxId, folder, chainId);
ChangeFolderCounters(db, tenant, user, folder, 0, 0, chainUnreadFlag ? -1 : 0, -1);
}
else
{
var updateMailQuery = new SqlUpdate(MailTable.Name)
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.IsRemoved, 0)
.Where(MailTable.Columns.ChainId, chainId)
.Where(MailTable.Columns.MailboxId, mailboxId)
.Where(MailTable.Columns.Folder, folder)
// Folder condition important because chain has different dates in different folders(Ex: Sent and Inbox).
.Set(MailTable.Columns.ChainDate, chain.date);
db.ExecuteNonQuery(updateMailQuery);
var tags = GetChainTags(db, chainId, folder, mailboxId, tenant, user);
var insertChainQuery =
new SqlInsert(ChainTable.Name, true)
.InColumnValue(ChainTable.Columns.Id, chainId)
.InColumnValue(ChainTable.Columns.MailboxId, mailboxId)
.InColumnValue(ChainTable.Columns.Tenant, tenant)
.InColumnValue(ChainTable.Columns.User, user)
.InColumnValue(ChainTable.Columns.Folder, folder)
.InColumnValue(ChainTable.Columns.Length, chain.length)
.InColumnValue(ChainTable.Columns.Unread, chain.unread)
.InColumnValue(ChainTable.Columns.HasAttachments, chain.attach_count > 0)
.InColumnValue(ChainTable.Columns.Importance, chain.importance)
.InColumnValue(ChainTable.Columns.Tags, tags);
var result = db.ExecuteNonQuery(insertChainQuery);
if (result <= 0)
{
throw new InvalidOperationException(string.Format("Invalid insert to {0}", ChainTable.Name));
}
_log.Debug("UpdateChain() row inserted to chain table tenant='{0}', user_id='{1}', id_mailbox='{2}', folder='{3}', chain_id='{4}'",
tenant, user, mailboxId, folder, chainId);
var unreadConvDiff = 0;
var totalConvDiff = 0;
if (!storedChainInfo.Any())
{
totalConvDiff = 1;
unreadConvDiff = chain.unread ? 1 : 0;
}
else
{
if (chainUnreadFlag != chain.unread)
{
unreadConvDiff = chain.unread ? 1 : -1;
}
}
ChangeFolderCounters(db, tenant, user, folder, unreadConvDiff: unreadConvDiff, totalConvDiff: totalConvDiff);
}
}
public void Add(SqlInsert statement)
{
AddCommandWithParameters(statement.ToString(), statement.Params);
}
public void SqlInsertTest()
{
var i = new SqlInsert("Table").InColumnValue("c1", 1);
Assert.AreEqual("insert into Table(c1) values (?)", i.ToString());
i = new SqlInsert("Table").ReplaceExists(true).InColumnValue("c1", 1);
Assert.AreEqual("replace into Table(c1) values (?)", i.ToString());
i = new SqlInsert("Table").IgnoreExists(true).InColumnValue("c1", 1);
Assert.AreEqual("insert ignore into Table(c1) values (?)", i.ToString());
i = new SqlInsert("Table").InColumns("c1","c2").Values(1, 2);
Assert.AreEqual("insert into Table(c1,c2) values (?,?)", i.ToString());
i = new SqlInsert("Table").InColumns("c1","c2").Values(1, 2, 3, 4);
Assert.AreEqual("insert into Table(c1,c2) values (?,?),(?,?)", i.ToString());
i = new SqlInsert("Table").InColumns("c1", "c2").Values(0, 2).Identity(0, 0, true);
Assert.AreEqual("insert into Table(c2) values (?); select @@identity", i.ToString());
i = new SqlInsert("Table").Values(0, 2);
Assert.AreEqual("insert into Table values (?,?)", i.ToString());
}
public void Columns_IsNullWhenFirstCalled()
{
var insert = new SqlInsert();
Assert.Null(insert.Columns);
}
