public int GetCasesCount( String searchText, int contactID, bool?isClosed, IEnumerable <String> tags) { var cacheKey = TenantID.ToString(CultureInfo.InvariantCulture) + "cases" + SecurityContext.CurrentAccount.ID.ToString() + searchText + contactID; if (tags != null) { cacheKey += String.Join("", tags.ToArray()); } if (isClosed.HasValue) { cacheKey += isClosed.Value; } var fromCache = _cache.Get <string>(cacheKey); if (fromCache != null) { return(Convert.ToInt32(fromCache)); } var withParams = !(String.IsNullOrEmpty(searchText) && contactID <= 0 && isClosed == null && (tags == null || !tags.Any())); var exceptIDs = CRMSecurity.GetPrivateItems(typeof(Cases)).ToList(); int result; if (withParams) { var whereConditional = WhereConditional(exceptIDs, searchText, contactID, isClosed, tags); result = whereConditional != null?Db.ExecuteScalar <int>(Query("crm_case").Where(whereConditional).SelectCount()) : 0; } else { var countWithoutPrivate = Db.ExecuteScalar <int>(Query("crm_case").SelectCount()); var privateCount = exceptIDs.Count; if (privateCount > countWithoutPrivate) { _log.ErrorFormat(@"Private cases count more than all cases. Tenant: {0}. CurrentAccount: {1}", TenantID, SecurityContext.CurrentAccount.ID); privateCount = 0; } result = countWithoutPrivate - privateCount; } if (result > 0) { _cache.Insert(cacheKey, result, TimeSpan.FromSeconds(30)); } return(result); }
public virtual int SaveOrUpdateInvoice(Invoice invoice) { _cache.Remove(new Regex(TenantID.ToString(CultureInfo.InvariantCulture) + "invoice.*")); return SaveOrUpdateInvoiceInDb(invoice); }
public IEnumerable <Tag> SaveTags(params Tag[] tags) { var result = new List <Tag>(); if (tags == null) { return(result); } tags = tags.Where(x => x != null && !x.EntryId.Equals(null) && !x.EntryId.Equals(0)).ToArray(); if (tags.Length == 0) { return(result); } lock (syncRoot) { using (var DbManager = GetDb()) using (var tx = DbManager.BeginTransaction()) { var mustBeDeleted = DbManager.ExecuteList(Query("files_tag ft") .Select("ftl.tag_id", "ftl.entry_id", "ftl.entry_type") .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) & Exp.Le("create_on", TenantUtil.DateTimeNow().AddMonths(-3)))); foreach (var row in mustBeDeleted) { DbManager.ExecuteNonQuery(Delete("files_tag_link") .Where(Exp.Eq("tag_id", row[0]) & Exp.Eq("entry_id", row[1]) & Exp.Eq("entry_type", row[2]))); } DbManager.ExecuteNonQuery(Delete("files_tag").Where(Exp.EqColumns("0", Query("files_tag_link l").SelectCount().Where(Exp.EqColumns("tag_id", "id"))))); var createOn = TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()); var cacheTagId = new Dictionary <String, int>(); foreach (var t in tags) { int id; var cacheTagIdKey = String.Join("/", new[] { TenantID.ToString(), t.Owner.ToString(), t.TagName, ((int)t.TagType).ToString(CultureInfo.InvariantCulture) }); if (!cacheTagId.TryGetValue(cacheTagIdKey, out id)) { id = DbManager.ExecuteScalar <int>(Query("files_tag") .Select("id") .Where("owner", t.Owner.ToString()) .Where("name", t.TagName) .Where("flag", (int)t.TagType)); if (id == 0) { var i1 = Insert("files_tag") .InColumnValue("id", 0) .InColumnValue("name", t.TagName) .InColumnValue("owner", t.Owner.ToString()) .InColumnValue("flag", (int)t.TagType) .Identity(1, 0, true); id = DbManager.ExecuteScalar <int>(i1); } cacheTagId.Add(cacheTagIdKey, id); } t.Id = id; result.Add(t); var i2 = Insert("files_tag_link") .InColumnValue("tag_id", id) .InColumnValue("entry_id", MappingID(t.EntryId, true)) .InColumnValue("entry_type", (int)t.EntryType) .InColumnValue("create_by", ASC.Core.SecurityContext.CurrentAccount.ID) .InColumnValue("create_on", createOn) .InColumnValue("tag_count", t.Count); DbManager.ExecuteNonQuery(i2); } tx.Commit(); } } return(result); }
public int GetDealsCount(String searchText, Guid responsibleID, int milestoneID, IEnumerable <String> tags, int contactID, DealMilestoneStatus?stageType, bool?contactAlsoIsParticipant, DateTime fromDate, DateTime toDate) { var cacheKey = TenantID.ToString(CultureInfo.InvariantCulture) + "deals" + SecurityContext.CurrentAccount.ID.ToString() + searchText + responsibleID + milestoneID + contactID; if (tags != null) { cacheKey += String.Join("", tags.ToArray()); } if (stageType.HasValue) { cacheKey += stageType.Value; } if (contactAlsoIsParticipant.HasValue) { cacheKey += contactAlsoIsParticipant.Value; } if (fromDate != DateTime.MinValue) { cacheKey += fromDate.ToString(); } if (toDate != DateTime.MinValue) { cacheKey += toDate.ToString(); } var fromCache = _cache.Get <string>(cacheKey); if (fromCache != null) { return(Convert.ToInt32(fromCache)); } var withParams = !(String.IsNullOrEmpty(searchText) && responsibleID == Guid.Empty && milestoneID <= 0 && (tags == null || !tags.Any()) && contactID <= 0 && stageType == null && contactAlsoIsParticipant == null && fromDate == DateTime.MinValue && toDate == DateTime.MinValue); ICollection <int> exceptIDs = CRMSecurity.GetPrivateItems(typeof(Deal)).ToList(); int result; if (withParams) { var whereConditional = WhereConditional(exceptIDs, searchText, responsibleID, milestoneID, tags, contactID, stageType, contactAlsoIsParticipant); var sqlQuery = GetDealSqlQuery(whereConditional); if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue) { sqlQuery.Having(Exp.Between("close_date", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate))); result = Db.ExecuteList(sqlQuery).Count; } else if (whereConditional == null) { result = 0; } else { result = Db.ExecuteList(sqlQuery).Count; } } else { var countWithoutPrivate = Db.ExecuteScalar <int>(Query("crm_deal").SelectCount()); var privateCount = exceptIDs.Count; if (privateCount > countWithoutPrivate) { _log.Error("Private deals count more than all deals"); privateCount = 0; } result = countWithoutPrivate - privateCount; } if (result > 0) { _cache.Insert(cacheKey, result, TimeSpan.FromSeconds(30)); } return(result); }
private void ResetCache() { cache.Remove(TenantID.ToString(CultureInfo.InvariantCulture)); }
public int GetInvoicesCount( String searchText, InvoiceStatus?status, DateTime issueDateFrom, DateTime issueDateTo, DateTime dueDateFrom, DateTime dueDateTo, EntityType entityType, int entityID, String currency) { var cacheKey = TenantID.ToString(CultureInfo.InvariantCulture) + "invoice" + SecurityContext.CurrentAccount.ID.ToString() + searchText; var fromCache = _cache.Get(cacheKey); if (fromCache != null) { return(Convert.ToInt32(fromCache)); } var withParams = hasParams(searchText, status, issueDateFrom, issueDateTo, dueDateFrom, dueDateTo, entityType, entityID, currency); var exceptIDs = CRMSecurity.GetPrivateItems(typeof(Invoice)).ToList(); int result; using (var db = GetDb()) { if (withParams) { var whereConditional = WhereConditional(null, exceptIDs, searchText, status, issueDateFrom, issueDateTo, dueDateFrom, dueDateTo, entityType, entityID, currency); result = whereConditional != null?db.ExecuteScalar <int>(Query("crm_invoice").Where(whereConditional).SelectCount()) : 0; } else { var countWithoutPrivate = db.ExecuteScalar <int>(Query("crm_invoice").SelectCount()); var privateCount = exceptIDs.Count; if (privateCount > countWithoutPrivate) { _log.ErrorFormat(@"Private invoice count more than all cases. Tenant: {0}. CurrentAccount: {1}", TenantID, SecurityContext.CurrentAccount.ID); privateCount = 0; } result = countWithoutPrivate - privateCount; } } if (result > 0) { _cache.Insert(cacheKey, result, new CacheDependency(null, new[] { _invoiceCacheKey }), Cache.NoAbsoluteExpiration, TimeSpan.FromSeconds(30)); } return(result); }
public virtual int SaveTask(Task newTask) { _cache.Remove(new Regex(TenantID.ToString(CultureInfo.InvariantCulture) + "tasks.*")); return(SaveTaskInDb(newTask)); }
public int GetTasksCount( String searchText, Guid responsibleId, int categoryId, bool?isClosed, DateTime fromDate, DateTime toDate, EntityType entityType, int entityId) { int result = 0; Log.DebugFormat("Starting GetTasksCount: {0}", DateTime.Now.ToString()); var cacheKey = TenantID.ToString(CultureInfo.InvariantCulture) + "tasks" + SecurityContext.CurrentAccount.ID.ToString() + searchText + responsibleId + categoryId + fromDate.ToString(CultureInfo.InvariantCulture) + toDate.ToString(CultureInfo.InvariantCulture) + (int)entityType + entityId; if (!String.IsNullOrEmpty(_cache.Get <String>(cacheKey))) { Log.DebugFormat("End GetTasksCount: {0}. From cache", DateTime.Now.ToString()); return(Convert.ToInt32(_cache.Get <String>(cacheKey))); } if (!String.IsNullOrEmpty(searchText)) { var tasks = GetCrudeTasks(searchText, responsibleId, categoryId, isClosed, fromDate, toDate, entityType, entityId, 0, 0, null); if (CRMSecurity.IsAdmin) { result = tasks.Count(); } else { result = CRMSecurity.FilterRead(tasks).Count(); } } else { if (CRMSecurity.IsAdmin) { var sqlQuery = Query("crm_task tbl_tsk").SelectCount(); sqlQuery = WhereConditional(sqlQuery, "tbl_tsk", responsibleId, categoryId, isClosed, fromDate, toDate, entityType, entityId, 0, 0, null); result = Db.ExecuteScalar <int>(sqlQuery); } else { var taskIds = new List <int>(); var sqlQuery = Query("crm_task tbl_tsk") .Select("tbl_tsk.id") .LeftOuterJoin("crm_contact tbl_ctc", Exp.EqColumns("tbl_tsk.contact_id", "tbl_ctc.id")) .Where(Exp.Or(Exp.Eq("tbl_ctc.is_shared", Exp.Empty), Exp.Gt("tbl_ctc.is_shared", 0))); sqlQuery = WhereConditional(sqlQuery, "tbl_tsk", responsibleId, categoryId, isClosed, fromDate, toDate, entityType, entityId, 0, 0, null); // count tasks without entityId and only open contacts taskIds = Db.ExecuteList(sqlQuery).Select(item => Convert.ToInt32(item[0])).ToList(); Log.DebugFormat("End GetTasksCount: {0}. count tasks without entityId and only open contacts", DateTime.Now.ToString()); sqlQuery = Query("crm_task tbl_tsk") .Select("tbl_tsk.id") .InnerJoin("crm_contact tbl_ctc", Exp.EqColumns("tbl_tsk.contact_id", "tbl_ctc.id")) .InnerJoin("core_acl tbl_cl", Exp.EqColumns("tbl_ctc.tenant_id", "tbl_cl.tenant") & Exp.Eq("tbl_cl.subject", ASC.Core.SecurityContext.CurrentAccount.ID.ToString()) & Exp.Eq("tbl_cl.action", CRMSecurity._actionRead.ID.ToString()) & Exp.EqColumns("tbl_cl.object", "CONCAT('ASC.CRM.Core.Entities.Company|', tbl_ctc.id)")) .Where(Exp.Eq("tbl_ctc.is_shared", 0)) .Where(Exp.Eq("tbl_ctc.is_company", 1)); sqlQuery = WhereConditional(sqlQuery, "tbl_tsk", responsibleId, categoryId, isClosed, fromDate, toDate, entityType, entityId, 0, 0, null); // count tasks with entityId and only close contacts taskIds.AddRange(Db.ExecuteList(sqlQuery).Select(item => Convert.ToInt32(item[0])).ToList()); Log.DebugFormat("End GetTasksCount: {0}. count tasks with entityId and only close contacts", DateTime.Now.ToString()); sqlQuery = Query("crm_task tbl_tsk") .Select("tbl_tsk.id") .InnerJoin("crm_contact tbl_ctc", Exp.EqColumns("tbl_tsk.contact_id", "tbl_ctc.id")) .InnerJoin("core_acl tbl_cl", Exp.EqColumns("tbl_ctc.tenant_id", "tbl_cl.tenant") & Exp.Eq("tbl_cl.subject", ASC.Core.SecurityContext.CurrentAccount.ID.ToString()) & Exp.Eq("tbl_cl.action", CRMSecurity._actionRead.ID.ToString()) & Exp.EqColumns("tbl_cl.object", "CONCAT('ASC.CRM.Core.Entities.Person|', tbl_ctc.id)")) .Where(Exp.Eq("tbl_ctc.is_shared", 0)) .Where(Exp.Eq("tbl_ctc.is_company", 0)); sqlQuery = WhereConditional(sqlQuery, "tbl_tsk", responsibleId, categoryId, isClosed, fromDate, toDate, entityType, entityId, 0, 0, null); // count tasks with entityId and only close contacts taskIds.AddRange(Db.ExecuteList(sqlQuery).Select(item => Convert.ToInt32(item[0])).ToList()); Log.DebugFormat("End GetTasksCount: {0}. count tasks with entityId and only close contacts", DateTime.Now.ToString()); sqlQuery = Query("crm_task tbl_tsk") .Select("tbl_tsk.id") .InnerJoin("core_acl tbl_cl", Exp.EqColumns("tbl_tsk.tenant_id", "tbl_cl.tenant") & Exp.Eq("tbl_cl.subject", ASC.Core.SecurityContext.CurrentAccount.ID.ToString()) & Exp.Eq("tbl_cl.action", CRMSecurity._actionRead.ID.ToString()) & Exp.EqColumns("tbl_cl.object", "CONCAT('ASC.CRM.Core.Entities.Deal|', tbl_tsk.entity_id)")) .Where(!Exp.Eq("tbl_tsk.entity_id", 0) & Exp.Eq("tbl_tsk.entity_type", (int)EntityType.Opportunity) & Exp.Eq("tbl_tsk.contact_id", 0)); sqlQuery = WhereConditional(sqlQuery, "tbl_tsk", responsibleId, categoryId, isClosed, fromDate, toDate, entityType, entityId, 0, 0, null); // count tasks with entityId and without contact taskIds.AddRange(Db.ExecuteList(sqlQuery).Select(item => Convert.ToInt32(item[0])).ToList()); Log.DebugFormat("End GetTasksCount: {0}. count tasks with entityId and without contact", DateTime.Now.ToString()); sqlQuery = Query("crm_task tbl_tsk") .Select("tbl_tsk.id") .InnerJoin("core_acl tbl_cl", Exp.EqColumns("tbl_tsk.tenant_id", "tbl_cl.tenant") & Exp.Eq("tbl_cl.subject", ASC.Core.SecurityContext.CurrentAccount.ID.ToString()) & Exp.Eq("tbl_cl.action", CRMSecurity._actionRead.ID.ToString()) & Exp.EqColumns("tbl_cl.object", "CONCAT('ASC.CRM.Core.Entities.Cases|', tbl_tsk.entity_id)")) .Where(!Exp.Eq("tbl_tsk.entity_id", 0) & Exp.Eq("tbl_tsk.entity_type", (int)EntityType.Case) & Exp.Eq("tbl_tsk.contact_id", 0)); sqlQuery = WhereConditional(sqlQuery, "tbl_tsk", responsibleId, categoryId, isClosed, fromDate, toDate, entityType, entityId, 0, 0, null); // count tasks with entityId and without contact taskIds.AddRange(Db.ExecuteList(sqlQuery).Select(item => Convert.ToInt32(item[0])).ToList()); result = taskIds.Distinct().Count(); Log.DebugFormat("End GetTasksCount: {0}. count tasks with entityId and without contact", DateTime.Now.ToString()); Log.Debug("Finish"); } } if (result > 0) { _cache.Insert(cacheKey, result, TimeSpan.FromMinutes(1)); } return(result); }
public int GetContactsCount(String searchText, IEnumerable <String> tags, int contactStatus, ContactListViewType contactListView) { var cacheKey = TenantID.ToString() + "contacts" + SecurityContext.CurrentAccount.ID.ToString() + searchText + contactStatus + (int)contactListView; if (tags != null) { cacheKey += String.Join("", tags.ToArray()); } var fromCache = _cache.Get(cacheKey); if (fromCache != null) { return(Convert.ToInt32(fromCache)); } var withParams = !(String.IsNullOrEmpty(searchText) && (tags == null || !tags.Any()) && contactStatus <= 0 && contactListView == ContactListViewType.All); int result; if (withParams) { ICollection <int> excludedContactIDs; switch (contactListView) { case ContactListViewType.Person: excludedContactIDs = CRMSecurity.GetPrivateItems(typeof(Person)).ToList(); break; case ContactListViewType.Company: excludedContactIDs = CRMSecurity.GetPrivateItems(typeof(Company)).ToList(); break; default: excludedContactIDs = CRMSecurity.GetPrivateItems(typeof(Company)).Union(CRMSecurity.GetPrivateItems(typeof(Person))).ToList(); break; } var whereConditional = WhereConditional(excludedContactIDs, searchText, tags, contactStatus, contactListView); if (whereConditional != null) { result = DbManager.ExecuteScalar <int>(Query("crm_contact").Where(whereConditional).SelectCount()); } else { result = 0; } } else { var countWithoutPrivate = DbManager.ExecuteScalar <int>(Query("crm_contact").SelectCount()); var privateCount = CRMSecurity.GetPrivateItemsCount(typeof(Person)) + CRMSecurity.GetPrivateItemsCount(typeof(Company)); if (privateCount > countWithoutPrivate) { _log.Error("Private contacts count more than all contacts"); privateCount = 0; } result = countWithoutPrivate - privateCount; } if (result > 0) { _cache.Insert(cacheKey, result, new CacheDependency(null, new[] { _contactCacheKey }), Cache.NoAbsoluteExpiration, TimeSpan.FromSeconds(30)); } return(result); }