public static bool HaveUserActivity(int tenantID, Guid productID, int action)
{
var q = new SqlQuery("webstudio_useractivity").Where("tenantid", tenantID);
if (productID != default(Guid))
{
q.Where("productid", productID.ToString());
}
if (action != UserActivityConstants.AllActionType)
{
q.Where("actiontype", action);
}
using (var db = GetDbManager())
{
return db.ExecuteScalar<int>(q.SelectCount()) > 0;
}
}
public bool IsInstallRegistered(string userEmail, MobileAppType? appType)
{
var query = new SqlQuery("mobile_app_install")
.SelectCount()
.Where("user_email", userEmail);
if (appType.HasValue)
query.Where("app_type", (int) appType.Value);
using (var db = GetDbManager())
{
return db.ExecuteScalar<int>(query) > 0;
}
}
//NOTE: in tuple return first = total count, second = user readed count
public CommentCount GetCountWithReaded(string key, Guid userId)
{
using (DbManager dbMan = GetManager())
{
SqlQuery query = new SqlQuery(CommentTable).Select("count(id)", "count(user_id)")
.LeftOuterJoin(CommentReadedTable, Exp.EqColumns("id", "comment_id"))
.Where("comment_key", key);
if (userId != Guid.Empty)
query = query.Where(Exp.Eq("user_id", userId.ToString()) | Exp.Eq("user_id", null));
return
dbMan.ExecuteList(query).Select(x => new CommentCount(Convert.ToInt64(x[0]), Convert.ToInt64(x[1])))
.SingleOrDefault();
}
}
public static bool GetIsMobileAppUser(Guid userId, DeviceType? type)
{
string userEmail = CoreContext.UserManager.GetUsers(userId).Email;
var query = new SqlQuery("push_app_user")
.SelectCount()
.Where("user_email", userEmail);
if (type.HasValue)
query.Where("device_type", (int)type.Value);
using (var db = new DbManager("core"))
{
return db.ExecuteScalar<int>(query) > 0;
}
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "p.id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.HasUserId || (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty))
{
var existParticipant = new SqlQuery(ParticipantTable + " ppp").Select("ppp.participant_id").Where(Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.Eq("ppp.tenant", Tenant));
if (filter.DepartmentId != Guid.Empty)
{
existParticipant.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "ppp.participant_id") & Exp.EqColumns("cug.tenant", "ppp.tenant"));
existParticipant.Where("cug.groupid", filter.DepartmentId);
}
if (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty)
{
existParticipant.Where(Exp.Eq("ppp.participant_id", filter.ParticipantId.ToString()));
}
query.Where(Exp.Exists(existParticipant));
}
if (filter.UserId != Guid.Empty)
{
query.Where("responsible_id", filter.UserId);
}
if (filter.Follow)
{
query.InnerJoin(FollowingProjectTable + " pfpp", Exp.EqColumns("p.id", "pfpp.project_id"));
query.Where(Exp.Eq("pfpp.participant_id", CurrentUserID));
}
if (filter.ProjectStatuses.Count != 0)
{
query.Where(Exp.Eq("p.status", filter.ProjectStatuses.First()));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
if (FullTextSearch.SupportModule(FullTextSearch.ProjectsModule))
{
var projIds = FullTextSearch.Search(FullTextSearch.ProjectsModule.Match(filter.SearchText));
query.Where(Exp.In("p.id", projIds));
}
else
{
query.Where(Exp.Like("p.title", filter.SearchText, SqlLike.AnyWhere));
}
}
query.GroupBy("p.id");
if (checkAccess)
{
query.Where(Exp.Eq("p.private", false));
}
else if (!isAdmin)
{
var isInTeam = new SqlQuery(ParticipantTable).Select("security").Where(Exp.EqColumns("p.id", "project_id") & Exp.Eq("removed", false) & Exp.Eq("participant_id", CurrentUserID));
query.Where(Exp.Eq("p.private", false) | Exp.Eq("p.responsible_id", CurrentUserID) | (Exp.Eq("p.private", true) & Exp.Exists(isInTeam)));
}
return query;
}
public static List<ResWord> SearchAll(string projectName, string moduleName, string languageTo, string searchText, string searchType)
{
using (var dbManager = new DbManager("tmresource"))
{
var sql = new SqlQuery("res_data")
.Select("title", "fileid", "textValue", "resName", "moduleName", "projectName")
.InnerJoin("res_files", Exp.EqColumns("res_files.ID", "res_data.fileID"))
.Where("cultureTitle", languageTo)
.Where("flag != 4")
.Where(Exp.Like(searchType, searchText))
.Where("resourceType", "text")
.OrderBy("textValue", true);
if (!string.IsNullOrEmpty(projectName) && projectName != "All")
{
sql.Where("projectName", projectName);
if (!string.IsNullOrEmpty(moduleName) && moduleName != "All")
sql.Where("moduleName", moduleName);
}
return dbManager.ExecuteList(sql).ConvertAll(r => GetSearchWord(r));
}
}
public static IEnumerable<ResWord> GetListResWords(ResCurrent current, string search)
{
using (var dbManager = new DbManager("tmresource"))
{
var exist = new SqlQuery("res_data rd3")
.Select("rd3.title")
.Where("rd3.fileid = rd1.fileid")
.Where("rd3.title = concat('del_', rd1.title)")
.Where("rd3.cultureTitle = rd1.cultureTitle");
var sql = new SqlQuery("res_data rd1")
.Select("rd1.title", "rd1.fileid", "rd1.textValue", "rd1.description", "rd1.flag", "rd1.link", "rf.resName", "rd2.id", "rd2.flag", "rd2.textValue")
.LeftOuterJoin("res_data rd2", Exp.EqColumns("rd1.fileid", "rd2.fileid") & Exp.EqColumns("rd1.title", "rd2.title") & Exp.Eq("rd2.cultureTitle", current.Language.Title))
.InnerJoin("res_files rf", Exp.EqColumns("rf.ID", "rd1.fileID"))
.Where("rf.moduleName", current.Module.Name)
.Where("rf.projectName", current.Project.Name)
.Where("rd1.cultureTitle", "Neutral")
.Where("rd1.flag != 4")
.Where("rd1.resourceType", "text")
.Where(!Exp.Like("rd1.title", @"del\_", SqlLike.StartWith) & !Exp.Exists(exist))
.OrderBy("rd1.id", true);
if (!String.IsNullOrEmpty(search))
sql.Where(Exp.Like("rd1.textvalue", search));
return dbManager.ExecuteList(sql).ConvertAll(r =>
{
var word = GetWord(r);
word.ResFile.FileName = Convert.ToString(r[6]);
if (r[7] != null)
{
word.Status = (int)r[8] == 3 ? WordStatusEnum.Changed : WordStatusEnum.Translated;
word.ValueTo = Convert.ToString(r[9]);
}
else
{
word.Status = WordStatusEnum.Untranslated;
}
return word;
}).OrderBy(r => r.ValueFrom);
}
}
private List<MailMessageItem> GetFilteredChains(
IDbManager db,
int id_tenant,
string id_user,
MailFilter filter,
DateTime? utc_chain_from_date,
int from_message,
bool? prev_flag,
out bool has_more)
{
var res = new List<MailMessageItem>();
var chains_to_skip = new List<ChainInfo>();
var skip_flag = false;
var chunck_index = 0;
var sort_order = filter.SortOrder == "ascending";
if (prev_flag.GetValueOrDefault(false))
sort_order = !sort_order;
var query_messages = new SqlQuery(MailTable.name)
.Select(
MailTable.Columns.id,
MailTable.Columns.from,
MailTable.Columns.to,
MailTable.Columns.reply,
MailTable.Columns.subject,
MailTable.Columns.importance,
MailTable.Columns.date_sent,
MailTable.Columns.size,
MailTable.Columns.attach_count,
MailTable.Columns.unread,
MailTable.Columns.is_answered,
MailTable.Columns.is_forwarded,
MailTable.Columns.is_from_crm,
MailTable.Columns.is_from_tl,
MailTable.Columns.folder_restore,
MailTable.Columns.folder,
MailTable.Columns.chain_id,
MailTable.Columns.id_mailbox,
MailTable.Columns.chain_date)
.Where(GetUserWhere(id_user, id_tenant))
.Where(MailTable.Columns.is_removed, false)
.ApplyFilter(filter)
.OrderBy(MailTable.Columns.chain_date, sort_order);
if (null != utc_chain_from_date)
{
query_messages = query_messages.Where(sort_order ?
Exp.Ge(MailTable.Columns.chain_date, utc_chain_from_date) :
Exp.Le(MailTable.Columns.chain_date, utc_chain_from_date));
skip_flag = true;
}
// We are increasing the size of the page to check whether it is necessary to show the Next button.
while (res.Count < filter.PageSize + 1)
{
query_messages.SetFirstResult(chunck_index * chunk_size * filter.PageSize).SetMaxResults(chunk_size * filter.PageSize);
chunck_index++;
var tenant_obj = CoreContext.TenantManager.GetTenant(id_tenant);
var list = db
.ExecuteList(query_messages)
.ConvertAll(r =>
ConvertToConversation(r, tenant_obj));
if (0 == list.Count)
break;
foreach (var item in list)
{
var chain_info = new ChainInfo {id = item.ChainId, mailbox = item.MailboxId};
// Skip chains that was stored before and within from_message's chain.
if (skip_flag)
{
var tenant = CoreContext.TenantManager.GetTenant(id_tenant);
if (item.ChainDate != TenantUtil.DateTimeFromUtc(tenant, utc_chain_from_date.GetValueOrDefault()))
skip_flag = false;
else
{
if (item.Id == from_message)
skip_flag = false;
chains_to_skip.Add(chain_info);
continue;
}
}
if (chains_to_skip.Contains(chain_info))
continue;
var already_contains = false;
foreach(var chain in res){
if(chain.ChainId == item.ChainId && chain.MailboxId == item.MailboxId){
already_contains = true;
if(chain.Date < item.Date)
res[res.IndexOf(chain)] = item;
break;
}
}
if(!already_contains)
res.Add(item);
if (filter.PageSize + 1 == res.Count)
break;
}
var is_all_needed_conversation_gathered = filter.PageSize + 1 == res.Count;
if (is_all_needed_conversation_gathered)
break;
var is_enough_messages_for_page = chunk_size*filter.PageSize <= list.Count;
if (!is_enough_messages_for_page)
break;
}
has_more = res.Count > filter.PageSize;
if (has_more)
res.RemoveAt(filter.PageSize);
return res;
}
public IEnumerable<SubscriptionMethod> GetSubscriptionMethods(int tenant, string sourceId, string actionId, string recipientId)
{
if (sourceId == null) throw new ArgumentNullException("sourceId");
if (actionId == null) throw new ArgumentNullException("actionId");
var q = new SqlQuery("core_subscriptionmethod")
.Select("tenant", "recipient", "sender")
.Where(Exp.Eq("tenant", -1) | Exp.Eq("tenant", tenant))
.Where("source", sourceId)
.Where("action", actionId)
.OrderBy("tenant", true);
if (recipientId != null) q.Where("recipient", recipientId);
var methods = ExecList(q)
.ConvertAll(r =>
{
return new SubscriptionMethod
{
Tenant = Convert.ToInt32(r[0]),
SourceId = sourceId,
ActionId = actionId,
RecipientId = (string)r[1],
Methods = Convert.ToString(r[2]).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries),
};
});
var result = methods.ToList();
var common = new Dictionary<string, SubscriptionMethod>();
foreach (var m in methods)
{
var key = m.SourceId + m.ActionId + m.RecipientId;
if (m.Tenant == Tenant.DEFAULT_TENANT)
{
m.Tenant = tenant;
common.Add(key, m);
}
else
{
SubscriptionMethod r;
if (common.TryGetValue(key, out r))
{
result.Remove(r);
}
}
}
return result;
}
public IEnumerable<Tag> GetNewTags(Guid subject, Folder parentFolder, bool deepSearch)
{
using (var DbManager = GetDbManager())
{
if (parentFolder == null || parentFolder.ID == null)
throw new ArgumentException("folderId");
var result = new List<Tag>();
var monitorFolderIds = new[] {parentFolder.ID}.AsEnumerable();
var getBaseSqlQuery = new Func<SqlQuery>(() =>
{
var fnResult =
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)
fnResult.Where(Exp.Eq("ft.owner", subject));
return fnResult;
});
var tempTags = Enumerable.Empty<Tag>();
if (parentFolder.FolderType == FolderType.SHARE)
{
var shareQuery =
new Func<SqlQuery>(() => getBaseSqlQuery().InnerJoin("files_security fs",
Exp.EqColumns("fs.tenant_id", "ftl.tenant_id") &
Exp.EqColumns("fs.entry_id", "ftl.entry_id") &
Exp.EqColumns("fs.entry_type", "ftl.entry_type")));
var tmpShareFileTags = DbManager.ExecuteList(
shareQuery().InnerJoin("files_file f",
!Exp.Eq("f.create_by", subject) &
Exp.EqColumns("f.id", "ftl.entry_id") &
Exp.Eq("ftl.entry_type", FileEntryType.File))
.Select(GetRootFolderType("folder_id")))
.Where(r => ParseRootFolderType(r[7]) == FolderType.USER).ToList()
.ConvertAll(r => ToTag(r));
tempTags = tempTags.Concat(tmpShareFileTags);
var tmpShareFolderTags = DbManager.ExecuteList(
shareQuery().InnerJoin("files_folder f",
!Exp.Eq("f.create_by", subject) &
Exp.EqColumns("f.id", "ftl.entry_id") &
Exp.Eq("ftl.entry_type", FileEntryType.Folder))
.Select(GetRootFolderType("parent_id")))
.Where(r => ParseRootFolderType(r[7]) == FolderType.USER).ToList()
.ConvertAll(r => ToTag(r));
tempTags = tempTags.Concat(tmpShareFolderTags);
var tmpShareSboxTags = DbManager.ExecuteList(
shareQuery()
.InnerJoin("files_thirdparty_id_mapping m",
Exp.EqColumns("m.hash_id", "ftl.entry_id"))
.InnerJoin("files_thirdparty_account ac",
Exp.Sql("m.id Like concat('sbox-', ac.id, '%')") &
!Exp.Eq("ac.user_id", subject) &
Exp.Eq("ac.folder_type", FolderType.USER)
)
).ConvertAll(r => ToTag(r));
tempTags = tempTags.Concat(tmpShareSboxTags);
}
else if (parentFolder.FolderType == FolderType.Projects)
tempTags = DbManager.ExecuteList(getBaseSqlQuery().InnerJoin("files_bunch_objects fbo",
Exp.EqColumns("fbo.tenant_id", "ftl.tenant_id") &
Exp.EqColumns("fbo.left_node", "ftl.entry_id") &
Exp.Eq("ftl.entry_type", (int) FileEntryType.Folder))
.Where(Exp.Eq("fbo.tenant_id", TenantID) & Exp.Like("fbo.right_node", "projects/project/", SqlLike.StartWith)))
.ConvertAll(r => ToTag(r));
if (tempTags.Any())
{
if (!deepSearch) return tempTags;
monitorFolderIds = monitorFolderIds.Concat(tempTags.Where(x => x.EntryType == FileEntryType.Folder).Select(x => x.EntryId));
result.AddRange(tempTags);
}
var subFoldersSqlQuery = new SqlQuery("files_folder_tree")
.Select("folder_id")
.Where(Exp.In("parent_id", monitorFolderIds.ToArray()));
if (!deepSearch)
subFoldersSqlQuery.Where(Exp.Eq("level", 1));
monitorFolderIds = monitorFolderIds.Concat(DbManager.ExecuteList(subFoldersSqlQuery).ConvertAll(x => x[0]));
var newTagsForFolders = DbManager.ExecuteList(getBaseSqlQuery()
.Where(Exp.In("ftl.entry_id", monitorFolderIds.ToArray()) &
Exp.Eq("ftl.entry_type", (int) FileEntryType.Folder)))
.ConvertAll(r => ToTag(r));
result.AddRange(newTagsForFolders);
var newTagsForFiles = DbManager.ExecuteList(getBaseSqlQuery()
.InnerJoin("files_file ff",
Exp.EqColumns("ftl.tenant_id", "ff.tenant_id") &
Exp.EqColumns("ftl.entry_id", "ff.id"))
.Where(Exp.In("ff.folder_id", (deepSearch
? monitorFolderIds.ToArray()
: new[] {parentFolder.ID})) &
Exp.Eq("ftl.entry_type", (int) FileEntryType.File)))
.ConvertAll(r => ToTag(r));
result.AddRange(newTagsForFiles);
if (parentFolder.FolderType == FolderType.USER || parentFolder.FolderType == FolderType.COMMON)
{
var folderType = parentFolder.FolderType;
var querySelect = new SqlQuery("files_thirdparty_account")
.Select("id")
.Where("tenant_id", TenantID)
.Where("folder_type", (int) folderType);
if (folderType == FolderType.USER)
querySelect = querySelect.Where(Exp.Eq("user_id", subject.ToString()));
var sboxFolderIds = DbManager.ExecuteList(querySelect).ConvertAll(r => "sbox-" + r[0]);
var newTagsForSBox = DbManager.ExecuteList(getBaseSqlQuery()
.InnerJoin("files_thirdparty_id_mapping mp", Exp.EqColumns("ftl.entry_id", "mp.hash_id"))
.Where(Exp.In("mp.id", sboxFolderIds) &
Exp.Eq("ft.owner", subject) &
Exp.Eq("ftl.entry_type", (int) FileEntryType.Folder)))
.ConvertAll(r => ToTag(r));
result.AddRange(newTagsForSBox);
}
return result;
}
}
public IList<object[]> BuildProjectWithoutOpenMilestone(ReportFilter filter)
{
IntersectUsersAndProjects(filter);
IntersectTagsAndProjects(filter);
var query = new SqlQuery("projects_projects p")
.LeftOuterJoin("projects_milestones m", Exp.EqColumns("p.id", "m.project_id") & Exp.EqColumns("p.tenant_id", "m.tenant_id"))
.Select("p.id")
.Where("p.tenant_id", Tenant)
.GroupBy(1)
.Having(Exp.Eq("sum(case m.status when 0 then 1 else 0 end)", 0));
if (filter.HasProjectIds)
{
query.Where(Exp.In("p.id", filter.ProjectIds));
}
var ids = DbManager
.ExecuteList(query)
.ConvertAll(r => Convert.ToInt32(r[0]))
.ToArray();
filter.SetProjectIds(ids);
return BuildProjectListReport(filter);
}
public IList<object[]> BuildProjectListReport(ReportFilter filter)
{
IntersectUsersAndProjects(filter);
IntersectTagsAndProjects(filter);
var query = new SqlQuery("projects_projects p")
.Select("p.id")
.Select("p.title")
.Select("p.responsible_id")
.Select("p.status")
.Select(new SqlQuery("projects_milestones m").SelectCount().Where(Exp.EqColumns("m.project_id", "p.id")))
.Select(new SqlQuery("projects_tasks t").SelectCount().Where(Exp.EqColumns("t.project_id", "p.id")))
.Select(new SqlQuery("projects_project_participant pp").SelectCount().Where(Exp.EqColumns("pp.project_id", "p.id") & Exp.Eq("removed", false)))
.Select("p.private")
.Where("p.tenant_id", Tenant)
.OrderBy("p.title", true);
if (filter.HasProjectStatuses)
{
query.Where(Exp.In("p.status", filter.ProjectStatuses));
}
if (filter.HasProjectIds)
{
query.Where(Exp.In("p.id", filter.ProjectIds));
}
return DbManager
.ExecuteList(query)
.ConvertAll(r => new object[] { Convert.ToInt32(r[0]), (string)r[1], ToGuid(r[2]), ToString((ProjectStatus)Convert.ToInt32(r[3])), (long)r[4], (long)r[5], (long)r[6], Convert.ToBoolean(r[7]), });
}
public IList<object[]> BuildTimeReport(ReportFilter filter)
{
var query = new SqlQuery("projects_time_tracking t")
.LeftOuterJoin("projects_tasks r", Exp.EqColumns("t.relative_task_id", "r.id") & Exp.EqColumns("r.tenant_id", "t.tenant_id"))
.Select("t.person_id", "t.project_id", "t.relative_task_id", "r.title")
.SelectSum("t.hours")
.Where("t.tenant_id", Tenant)
.Where(!Exp.Eq("t.relative_task_id", 0))
.Where(Exp.Between("t.date", filter.GetFromDate(true), filter.GetToDate(true)))
.GroupBy(1, 2, 3);
if (filter.HasProjectIds)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
if (filter.HasUserId)
{
query.Where(Exp.In("t.person_id", filter.GetUserIds()));
}
return DbManager.ExecuteList(query);
}
public IList<object[]> BuildTaskListReport(ReportFilter filter)
{
IntersectTagsAndProjects(filter);
var query = new SqlQuery("projects_tasks t")
.InnerJoin("projects_projects p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("t.project_id", "p.id"))
.LeftOuterJoin("projects_milestones m", Exp.EqColumns("m.tenant_id", "t.tenant_id") & Exp.EqColumns("t.milestone_id", "m.id"))
.LeftOuterJoin("projects_tasks_responsible ptr", Exp.EqColumns("ptr.tenant_id", "t.tenant_id") & Exp.EqColumns("ptr.task_id", "t.id"))
.Select("p.id", "p.title")
.Select("m.id", "m.title", "m.deadline", "m.status")
.Select("t.id", "t.title", "COALESCE(ptr.responsible_id, t.responsible_id)", "t.status", "t.deadline", "substring(t.description, 0, 510)")
.Where("t.tenant_id", Tenant)
.OrderBy("p.title", true)
.OrderBy("p.id", true)
.OrderBy("m.status", true)
.OrderBy("m.deadline", true)
.OrderBy("m.title", true)
.OrderBy("m.id", true)
.OrderBy("t.sort_order", false)
.OrderBy("t.status", true)
.OrderBy("t.priority", true)
.OrderBy("t.create_on", true);
if (filter.HasProjectIds)
{
query.Where(Exp.In("p.id", filter.ProjectIds));
}
if (filter.HasTaskStatuses)
{
query.Where(Exp.In("t.status", filter.TaskStatuses));
}
if (filter.HasUserId)
{
query.Where(Exp.In("ptr.responsible_id", filter.GetUserIds()));
}
if (filter.GetToDate() != DateTime.MaxValue)
{
query.Where(Exp.Between("t.deadline", new DateTime(1900, 1, 1), filter.GetToDate()));
}
if(!filter.NoResponsible)
{
query.Where(!Exp.Eq("ptr.responsible_id", null) & !Exp.Eq("t.responsible_id", Guid.Empty));
}
query.GroupBy("t.id");
return DbManager
.ExecuteList(query)
.ConvertAll(r => new object[] { Convert.ToInt32(r[0]), (string)r[1],
r[2] != null ? Convert.ToInt32(r[2]) : 0, (string)r[3], r[4] != null ? (TenantUtil.DateTimeFromUtc((DateTime)r[4])).ToString("d") : null, r[5] != null ? Convert.ToInt32(r[5]) : -1,
Convert.ToInt32(r[6]), (string)r[7], ToGuid(r[8]), (TaskStatus)Convert.ToInt32(r[9]), r[10] != null && !DateTime.MinValue.Equals(r[10]) ? (TenantUtil.DateTimeFromUtc((DateTime)r[10])).ToString("d") : null,
ASC.Common.Utils.HtmlUtil.GetText((string)r[11], 500) });
}
public IList<object[]> BuildUsersStatisticsReport(ReportFilter filter)
{
var query = new SqlQuery("projects_tasks t")
.Select("r.responsible_id")
.InnerJoin("projects_tasks_responsible r", Exp.EqColumns("r.task_id", "t.id") & Exp.EqColumns("r.tenant_id", "t.tenant_id"))
.SelectSum("0")
.SelectSum("case t.status when 2 then 0 else 1 end")
.SelectSum("case t.status when 2 then 1 else 0 end")
.Where("t.tenant_id", Tenant)
.GroupBy(1);
if (filter.HasUserId)
{
query.Where(Exp.In("r.responsible_id", filter.GetUserIds()));
}
else
{
query.Where(!Exp.Eq("r.responsible_id", Guid.Empty.ToString()));
}
if (filter.HasProjectIds)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
return DbManager
.ExecuteList(query)
.ConvertAll(r => new object[] { ToGuid(r[0]), Convert.ToInt64(r[1]), Convert.ToInt64(r[2]), Convert.ToInt64(r[3]) });
}
private static SqlQuery GetUserGroupRefQuery(int tenant, DateTime from)
{
var q = new SqlQuery("core_usergroup").Select("userid", "groupid", "ref_type", "removed", "last_modified", "tenant");
if (tenant != Tenant.DEFAULT_TENANT) q.Where("tenant", tenant);
if (from != default(DateTime)) q.Where(Exp.Ge("last_modified", from));
return q;
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin)
{
if (filter.MilestoneStatuses.Count != 0)
{
query.Where("t.status", filter.MilestoneStatuses.First());
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where(Exp.Eq("t.responsible_id", filter.UserId));
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.ParticipantId.HasValue)
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("pt.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("pt.id", "ptr1.task_id"));
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.ToString()));
query.LeftOuterJoin(TasksTable + " as pt", Exp.EqColumns("pt.milestone_id", "t.id") & Exp.EqColumns("pt.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
if (!isAdmin)
{
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = Exp.Sql("ppp.security IS NOT NULL") & Exp.Eq("ppp.removed", false);
var canReadMilestones = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Eq("t.responsible_id", CurrentUserID);
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadMilestones));
}
return query;
}
private void IntersectUsersAndProjects(ReportFilter filter)
{
if (filter.HasUserId)
{
var query = new SqlQuery("projects_project_participant")
.Select("project_id")
.Where(Exp.In("participant_id", filter.GetUserIds()) & Exp.Eq("removed", false))
.GroupBy(1);
if (filter.HasProjectIds)
{
query.Where(Exp.In("project_id", filter.ProjectIds));
}
var ids = DbManager
.ExecuteList(query)
.ConvertAll(r => Convert.ToInt32(r[0]))
.ToArray();
filter.SetProjectIds(ids);
filter.UserId = Guid.Empty;
filter.DepartmentId = Guid.Empty;
}
}
private SqlQuery WhereConditional(
SqlQuery sqlQuery,
Guid responsibleID,
int categoryID,
bool? isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
if (responsibleID != Guid.Empty)
sqlQuery.Where(Exp.Eq("responsible_id", responsibleID));
if (entityID > 0)
switch (entityType)
{
case EntityType.Contact:
var isCompany = Convert.ToBoolean(DbManager.ExecuteScalar(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID))));
if (isCompany)
return WhereConditional(sqlQuery, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy);
else
return WhereConditional(sqlQuery, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, from, count, orderBy);
case EntityType.Person:
sqlQuery.Where(Exp.Eq("contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
sqlQuery.Where(Exp.Eq("contact_id", entityID));
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In("contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq("entity_id", entityID) &
Exp.Eq("entity_type", (int)entityType));
break;
}
if (isClosed.HasValue)
sqlQuery.Where("is_closed", isClosed);
if (categoryID > 0)
sqlQuery.Where(Exp.Eq("category_id", categoryID));
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Between("deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
else if (fromDate != DateTime.MinValue)
sqlQuery.Where(Exp.Ge("deadline", TenantUtil.DateTimeToUtc(fromDate)));
else if (toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Le("deadline", TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
if (0 < from && from < int.MaxValue)
sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue)
sqlQuery.SetMaxResults(count);
sqlQuery.OrderBy("is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy))
switch ((TaskSortedByType)orderBy.SortedBy)
{
case TaskSortedByType.Title:
sqlQuery
.OrderBy("title", orderBy.IsAsc)
.OrderBy("deadline", true);
break;
case TaskSortedByType.DeadLine:
sqlQuery.OrderBy("deadline", orderBy.IsAsc)
.OrderBy("title", true);
break;
case TaskSortedByType.Category:
sqlQuery.OrderBy("category_id", orderBy.IsAsc)
.OrderBy("deadline", true)
.OrderBy("title", true);
break;
}
else
sqlQuery.OrderBy("deadline", true)
.OrderBy("title", true);
return sqlQuery;
}
public IList<object[]> BuildMilestonesReport(ReportFilter filter)
{
IntersectTagsAndProjects(filter);
var query = new SqlQuery()
.From("projects_projects p")
.InnerJoin("projects_milestones m", Exp.EqColumns("p.id", "m.project_id") & Exp.EqColumns("m.tenant_id", "p.tenant_id"))
.Select("p.id", "p.title", "m.id", "m.title", "m.deadline", "m.status")
.Where("m.status", MilestoneStatus.Open)
.Where("p.tenant_id", Tenant)
.OrderBy("p.title", true)
.OrderBy("m.deadline", false);
if (filter.HasProjectIds)
{
query.Where(Exp.In("p.id", filter.ProjectIds));
}
if (filter.GetFromDate() != DateTime.MinValue)
{
query.Where(Exp.Ge("m.deadline", filter.GetFromDate()));
}
if (filter.GetToDate() != DateTime.MaxValue)
{
query.Where(Exp.Le("m.deadline", filter.GetToDate()));
}
return DbManager
.ExecuteList(query)
.ConvertAll(r => new object[] { Convert.ToInt32(r[0]), (string)r[1], Convert.ToInt32(r[2]), (string)r[3], ((DateTime)r[4]).ToString("d"), ToString((MilestoneStatus)Convert.ToInt32(r[5])) });
}
public IEnumerable<Tag> GetNewTags(Guid subject, Folder parentFolder, bool deepSearch)
{
var folderId = SharpBoxDaoSelector.ConvertId(parentFolder.ID);
var fakeFolderId = parentFolder.ID.ToString();
var entryIDs = DbManager.ExecuteList(Query("files_thirdparty_id_mapping")
.Select("hash_id")
.Where(Exp.Like("id", fakeFolderId, SqlLike.StartWith)))
.ConvertAll(x => x[0]);
if (!entryIDs.Any()) return new List<Tag>();
var sqlQuery = new SqlQuery("files_tag ft")
.Select("ft.name",
"ft.flag",
"ft.owner",
"ftl.entry_id",
"ftl.entry_type",
"ftl.tag_count",
"ft.id")
.Distinct()
.LeftOuterJoin("files_tag_link ftl",
Exp.EqColumns("ft.tenant_id", "ftl.tenant_id") &
Exp.EqColumns("ft.id", "ftl.tag_id"))
.Where(Exp.Eq("ft.tenant_id", TenantID) &
Exp.Eq("ftl.tenant_id", TenantID) &
Exp.Eq("ft.flag", (int) TagType.New) &
Exp.In("ftl.entry_id", entryIDs));
if (subject != Guid.Empty)
sqlQuery.Where(Exp.Eq("ft.owner", subject));
var tags = DbManager.ExecuteList(sqlQuery).ConvertAll(r => new Tag
{
TagName = Convert.ToString(r[0]),
TagType = (TagType) r[1],
Owner = new Guid(r[2].ToString()),
EntryId = MappingID(r[3]),
EntryType = (FileEntryType) r[4],
Count = Convert.ToInt32(r[5]),
Id = Convert.ToInt32(r[6])
});
if (deepSearch) return tags;
var folderFileIds = new[] {fakeFolderId}
.Concat(GetFolderSubfolders(folderId).Select(x => MakeId(x)))
.Concat(GetFolderFiles(folderId).Select(x => MakeId(x)));
return tags.Where(tag => folderFileIds.Contains(tag.EntryId.ToString()));
}
private List<IProviderInfo> GetProvidersInfoInternal(FolderType folderType)
{
var querySelect = new SqlQuery(TableTitle)
.Select("id", "provider", "customer_title", "user_name", "password", "token", "user_id", "folder_type", "create_on")
.Where("tenant_id", TenantID)
.Where("folder_type", (int) folderType);
if (folderType == FolderType.USER)
querySelect = querySelect.Where(Exp.Eq("user_id", SecurityContext.CurrentAccount.ID.ToString()));
return DbManager.ExecuteList(querySelect).ConvertAll<IProviderInfo>(ToProviderInfo);
}
private SqlQuery WhereConditional(
SqlQuery sqlQuery,
String alias,
Guid responsibleID,
int categoryID,
bool? isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
var aliasPrefix = !String.IsNullOrEmpty(alias) ? alias + "." : "";
if (responsibleID != Guid.Empty)
sqlQuery.Where(Exp.Eq("responsible_id", responsibleID));
if (entityID > 0)
switch (entityType)
{
case EntityType.Contact:
var isCompany = true;
using (var db = GetDb())
{
isCompany = db.ExecuteScalar<bool>(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID)));
}
if (isCompany)
return WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy);
else
return WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, from, count, orderBy);
case EntityType.Person:
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In(aliasPrefix + "contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq(aliasPrefix + "entity_id", entityID) &
Exp.Eq(aliasPrefix + "entity_type", (int)entityType));
break;
}
if (isClosed.HasValue)
sqlQuery.Where(aliasPrefix + "is_closed", isClosed);
if (categoryID > 0)
sqlQuery.Where(Exp.Eq(aliasPrefix + "category_id", categoryID));
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Between(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
else if (fromDate != DateTime.MinValue)
sqlQuery.Where(Exp.Ge(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate)));
else if (toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Le(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
if (0 < from && from < int.MaxValue)
sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue)
sqlQuery.SetMaxResults(count);
sqlQuery.OrderBy(aliasPrefix + "is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy))
{
switch ((TaskSortedByType)orderBy.SortedBy)
{
case TaskSortedByType.Title:
sqlQuery
.OrderBy(aliasPrefix + "title", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true);
break;
case TaskSortedByType.DeadLine:
sqlQuery.OrderBy(aliasPrefix + "deadline", orderBy.IsAsc)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Category:
sqlQuery.OrderBy(aliasPrefix + "category_id", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.ContactManager:
sqlQuery.LeftOuterJoin("core_user u", Exp.EqColumns(aliasPrefix + "responsible_id", "u.id"))
.OrderBy("case when u.lastname is null or u.lastname = '' then 1 else 0 end, u.lastname", orderBy.IsAsc)
.OrderBy("case when u.firstname is null or u.firstname = '' then 1 else 0 end, u.firstname", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Contact:
sqlQuery.LeftOuterJoin("crm_contact c_tbl", Exp.EqColumns(aliasPrefix + "contact_id", "c_tbl.id"))
.OrderBy("case when c_tbl.display_name is null then 1 else 0 end, c_tbl.display_name", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
}
}
else
{
sqlQuery
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
}
return sqlQuery;
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin)
{
if (filter.Follow)
{
var objects = new List<String>(NotifySource.Instance.GetSubscriptionProvider().GetSubscriptions(NotifyConstants.Event_NewCommentForMessage, NotifySource.Instance.GetRecipientsProvider().GetRecipient(CurrentUserID.ToString())));
if (filter.ProjectIds.Count != 0)
{
objects = objects.Where(r => r.Split('_')[2] == filter.ProjectIds[0].ToString(CultureInfo.InvariantCulture)).ToList();
}
var ids = objects.Select(r => r.Split('_')[1]).ToArray();
query.Where(Exp.In("t.id", ids));
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("ppp.tenant", "t.tenant_id") & Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " pt", Exp.EqColumns("pt.project_id", "t.project_id"));
query.Where("pt.tag_id", filter.TagId);
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.create_by", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.EqColumns("cug.tenant", "t.tenant_id") & Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.create_by"));
query.Where("cug.groupid", filter.DepartmentId);
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Between("t.create_on", filter.FromDate, filter.ToDate.AddDays(1)));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
if (!isAdmin)
{
var isInTeam = new SqlQuery(ParticipantTable).Select("security").Where(Exp.EqColumns("p.id", "project_id") & Exp.Eq("removed", false) & Exp.Eq("participant_id", CurrentUserID) & !Exp.Eq("security & " + (int)ProjectTeamSecurity.Messages, (int)ProjectTeamSecurity.Messages));
query.Where(Exp.Eq("p.private", false) | Exp.Eq("p.responsible_id", CurrentUserID) | (Exp.Eq("p.private", true) & Exp.Exists(isInTeam)));
}
return query;
}
public static List<ResWord> GetListResWords(ResFile resFile, string to, string search)
{
using (var dbManager = new DbManager("tmresource"))
{
var sql = new SqlQuery("res_data")
.Select("title", "fileid", "textValue", "description", "flag", "link")
.InnerJoin("res_files", Exp.EqColumns("res_files.ID", "res_data.fileID"))
.Where("moduleName", resFile.ModuleName)
.Where("projectName", resFile.ProjectName)
.Where("cultureTitle", to)
.Where("flag != 4")
.Where("resourceType", "text")
.OrderBy("res_data.id", true);
if (!String.IsNullOrEmpty(resFile.FileName))
sql.Where("resName", resFile.FileName);
if (!String.IsNullOrEmpty(search))
sql.Where(Exp.Like("textvalue", search));
return dbManager.ExecuteList(sql).ConvertAll(r => GetWord(r));
}
}
private Exp WhereConditional(
ICollection<int> exceptIDs,
String searchText,
int contactID,
bool? isClosed,
IEnumerable<String> tags)
{
var conditions = new List<Exp>();
var ids = new List<int>();
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
if (keywords.Length > 0)
{
var modules = SearchDao.GetFullTextSearchModule(EntityType.Case, searchText);
if (FullTextSearch.SupportModule(modules))
{
ids = FullTextSearch.Search(modules);
if (ids.Count == 0) return null;
}
else
conditions.Add(BuildLike(new[] {"title"}, keywords));
}
}
if (contactID > 0)
{
var sqlQuery = new SqlQuery("crm_entity_contact")
.Select("entity_id")
.Where(Exp.Eq("contact_id", contactID) & Exp.Eq("entity_type", (int)EntityType.Case));
if (ids.Count > 0)
sqlQuery.Where(Exp.In("entity_id", ids));
using (var db = GetDb())
{
ids = db.ExecuteList(sqlQuery).Select(item => Convert.ToInt32(item[0])).ToList();
}
if (ids.Count == 0) return null;
}
if (isClosed.HasValue)
conditions.Add(Exp.Eq("is_closed", isClosed));
if (tags != null && tags.Any())
{
ids = SearchByTags(EntityType.Case, ids.ToArray(), tags);
if (ids.Count == 0) return null;
}
if (ids.Count > 0)
{
if (exceptIDs.Count > 0)
{
ids = ids.Except(exceptIDs).ToList();
if (ids.Count == 0) return null;
}
conditions.Add(Exp.In("id", ids));
}
else if (exceptIDs.Count > 0)
{
conditions.Add(!Exp.In("id", exceptIDs.ToArray()));
}
if (conditions.Count == 0) return null;
return conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j);
}
public List<MailAlert> FindAlerts(IDbManager db, int tenant, string user, int mailboxId = -1, AlertTypes type = AlertTypes.Empty)
{
var searchQuery = new SqlQuery(MailAlertsTable.name)
.Select(MailAlertsTable.Columns.id, MailAlertsTable.Columns.type, MailAlertsTable.Columns.id_mailbox, MailAlertsTable.Columns.data)
.Where(GetUserWhere(user, tenant));
if (mailboxId > 0)
searchQuery.Where(MailAlertsTable.Columns.id_mailbox, mailboxId);
if (type != AlertTypes.Empty)
searchQuery.Where(MailAlertsTable.Columns.type, (int)type);
var foundAlerts = db.ExecuteList(searchQuery)
.ConvertAll(x => new MailAlert
{
id = Convert.ToInt32(x[0]),
type = (AlertTypes)x[1],
id_mailbox = Convert.ToInt32(x[2]),
data = (string)x[3]
});
return foundAlerts;
}
private int GetFoldersCount(object parentId, bool withSubfoldes)
{
var q = new SqlQuery("files_folder_tree").SelectCount().Where("parent_id", parentId);
if (withSubfoldes)
{
q.Where(Exp.Gt("level", 0));
}
else
{
q.Where("level", 1);
}
using (var dbManager = GetDb())
{
return dbManager.ExecuteScalar<int>(q);
}
}
public List<int> GetTaskCount(List<int> projectId, TaskStatus? taskStatus, bool isAdmin)
{
var query = new SqlQuery(TasksTable + " t")
.Select("t.project_id").SelectCount()
.Where(Exp.In("t.project_id", projectId))
.Where("t.tenant_id", Tenant)
.GroupBy("t.project_id");
if (taskStatus != null)
{
if (taskStatus == TaskStatus.Open)
query.Where(!Exp.Eq("t.status", TaskStatus.Closed));
else
query.Where("t.status", TaskStatus.Closed);
}
if (!isAdmin)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.LeftOuterJoin(TasksResponsibleTable + " ptr", Exp.EqColumns("t.tenant_id", "ptr.tenant_id") & Exp.EqColumns("t.id", "ptr.task_id") & Exp.Eq("ptr.responsible_id", CurrentUserID))
.LeftOuterJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.Eq("ppp.participant_id", CurrentUserID))
.Where(Exp.Eq("p.private", false) | !Exp.Eq("ptr.responsible_id", null) | (Exp.Eq("p.private", true) & !Exp.Eq("ppp.security", null) & !Exp.Eq("ppp.security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks)));
}
using (var db = new DbManager(DatabaseId))
{
var result = db.ExecuteList(query);
return projectId.ConvertAll(
pid =>
{
var res = result.Find(r => Convert.ToInt32(r[0]) == pid);
return res == null ? 0 : Convert.ToInt32(res[1]);
}
);
}
}
private static SqlQuery GetGroupQuery(int tenant, DateTime from)
{
var q = new SqlQuery("core_group").Select("id", "name", "parentid", "categoryid", "removed", "last_modified", "tenant");
if (tenant != Tenant.DEFAULT_TENANT) q.Where("tenant", tenant);
if (from != default(DateTime)) q.Where(Exp.Ge("last_modified", from));
return q;
}
