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);
}
