public static List <StatisticUser> GetUserStatisticForLang(DateTime from, DateTime till)
{
using (var dbManager = DbManager.FromHttpContext(Dbid))
{
var sql = new SqlQuery(ResDataTable + " as r1");
sql.SelectCount().Select(ResCultureTable + ".title", "r1.authorLogin", "sum(LENGTH(r2.textvalue) - LENGTH(REPLACE(r2.textvalue, ' ', '')) + 1)")
.InnerJoin(ResDataTable + " as r2", Exp.And(Exp.EqColumns("r1.fileID", "r2.fileID"), Exp.EqColumns("r1.title", "r2.title")))
.InnerJoin(ResCultureTable, Exp.EqColumns("r1.cultureTitle", ResCultureTable + ".title"))
.Where(!Exp.Eq("r1.flag", 4))
.Where(!Exp.Eq("r1.flag", 3))
.Where(!Exp.Eq("r1.authorLogin", "Console"))
.Where(!Exp.Eq("r1.cultureTitle", "Neutral"))
.Where(Exp.Ge("r1.timeChanges", from))
.Where(Exp.Le("r1.timeChanges", till))
.Where("r2.cultureTitle", "Neutral")
.GroupBy("title", "authorLogin")
.OrderBy("title", true)
.OrderBy("authorLogin", true);
return(dbManager.ExecuteList(sql).ConvertAll(GetUserStatisticFromDB));
}
}
public void DeleteFile(object fileId)
{
var file = GetFileById(fileId);
if (file == null)
{
return;
}
var id = MakeId(file);
using (var db = GetDb())
using (var tx = db.BeginTransaction())
{
var hashIDs = db.ExecuteList(Query("files_thirdparty_id_mapping")
.Select("hash_id")
.Where(Exp.Like("id", id, SqlLike.StartWith)))
.ConvertAll(x => x[0]);
db.ExecuteNonQuery(Delete("files_tag_link").Where(Exp.In("entry_id", hashIDs)));
db.ExecuteNonQuery(Delete("files_tag").Where(Exp.EqColumns("0", Query("files_tag_link l").SelectCount().Where(Exp.EqColumns("tag_id", "id")))));
db.ExecuteNonQuery(Delete("files_security").Where(Exp.In("entry_id", hashIDs)));
db.ExecuteNonQuery(Delete("files_thirdparty_id_mapping").Where(Exp.In("hash_id", hashIDs)));
tx.Commit();
}
if (!(file is ErrorEntry))
{
SharpBoxProviderInfo.Storage.DeleteFileSystemEntry(file);
}
}
public List <Project> GetOpenProjectsWithTasks(Guid participantId)
{
var query = new SqlQuery(ProjectsTable + " p")
.Select(ProjectColumns.Select(c => "p." + c).ToArray())
.InnerJoin(TasksTable + " t", Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id"))
.Where("p.tenant_id", Tenant)
.Where("p.status", ProjectStatus.Open)
.OrderBy("p.title", true)
.GroupBy("p.id");
if (!participantId.Equals(Guid.Empty))
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("ppp.tenant", "p.tenant_id") & Exp.EqColumns("ppp.project_id", "p.id") & Exp.Eq("ppp.removed", false))
.Where("ppp.participant_id", participantId);
}
return(Db.ExecuteList(query).ConvertAll(converter));
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.TagId != 0)
{
if (filter.TagId == -1)
{
query.LeftOuterJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "p.id"));
query.Where("ptag.tag_id", null);
}
else
{
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.Equals(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.Equals(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.Any())
{
query.Where(Exp.In("p.status", filter.ProjectStatuses));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
List <int> projIds;
if (FactoryIndexer <ProjectsWrapper> .TrySelectIds(s => s.MatchAll(filter.SearchText), out projIds))
{
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) & Exp.EqColumns("tenant", "p.tenant_id"));
query.Where(Exp.Eq("p.private", false) | Exp.Eq("p.responsible_id", CurrentUserID) | (Exp.Eq("p.private", true) & Exp.Exists(isInTeam)));
}
return(query);
}
public List <Participant> GetTeam(IEnumerable <Project> projects)
{
return(Db.ExecuteList(
new SqlQuery(ParticipantTable + " pp")
.InnerJoin(ProjectsTable + " pt", Exp.EqColumns("pp.tenant", "pt.tenant_id") & Exp.EqColumns("pp.project_id", "pt.id"))
.Select("distinct pp.participant_id, pp.security, pp.project_id")
.Select(Exp.EqColumns("pp.project_id", "pt.id"))
.Where("pp.tenant", Tenant)
.Where(Exp.In("pp.project_id", projects.Select(r => r.ID).ToArray()))
.Where("pp.removed", false))
.ConvertAll(ToParticipant));
}
public List <Page> GetPages(string category)
{
var q = PageQuery(EqExp.Eq("c.categoryname", category))
.InnerJoin("wiki_categories c", Exp.EqColumns("p.pagename", "c.pagename") & Exp.EqColumns("p.tenant", "c.tenant"))
.OrderBy("p.pagename", true);
return(ExecQuery(q));
}
internal Bookmark RemoveBookmarkFromFavourite(long bookmarkID)
{
var tx = DbManager.BeginTransaction();
try
{
var userBookmarkID = DbManager.ExecuteScalar <long>(
new SqlQuery()
.Select("UserBookmarkID")
.From("bookmarking_userbookmark")
.Where("BookmarkID", bookmarkID)
.Where("Tenant", Tenant)
.Where("UserID", GetCurrentUserId()));
var raiting = GetUserBookmarksCount(bookmarkID);
DbManager.ExecuteNonQuery(
new SqlDelete("bookmarking_userbookmark")
.Where("UserBookmarkID", userBookmarkID)
.Where("Tenant", Tenant));
DbManager.ExecuteNonQuery(
new SqlDelete("bookmarking_userbookmarktag")
.Where("UserBookmarkID", userBookmarkID)
.Where("Tenant", Tenant));
if (raiting <= 1)
{
DbManager.ExecuteNonQuery(
new SqlDelete("bookmarking_bookmarktag")
.Where("BookmarkID", bookmarkID)
.Where("Tenant", Tenant));
DbManager.ExecuteNonQuery(
new SqlDelete("bookmarking_comment")
.Where("BookmarkID", bookmarkID)
.Where("Tenant", Tenant));
DbManager.ExecuteNonQuery(
new SqlDelete("bookmarking_bookmark")
.Where("ID", bookmarkID)
.Where("Tenant", Tenant));
DbManager.ExecuteNonQuery(
new SqlDelete("bookmarking_tag")
.Where("tenant", Tenant)
.Where(!Exp.Exists(new SqlQuery("bookmarking_bookmarktag b").Select("b.tagid").Where("b.Tenant", Tenant).Where(Exp.EqColumns("b.TagID", "bookmarking_tag.TagID"))))
);
tx.Commit();
return(null);
}
var bookmark = GetBookmarkByID(bookmarkID);
tx.Commit();
return(bookmark);
}
catch
{
tx.Rollback();
}
return(null);
}
public int GetByFilterCount(TaskFilter filter, bool isAdmin, bool checkAccess)
{
using (var db = new DbManager(DatabaseId))
{
var query = new SqlQuery(MessagesTable + " t")
.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.Select("t.id")
.GroupBy("t.id")
.Where("t.tenant_id", Tenant);
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
var queryCount = new SqlQuery().SelectCount().From(query, "t1");
return db.ExecuteScalar<int>(queryCount);
}
}
private SqlQuery CreateQuery()
{
return new SqlQuery(MessagesTable + " t")
.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.LeftOuterJoin(CommentsTable + " pc", Exp.EqColumns("pc.target_uniq_id", "concat('Message_', cast(t.id as char))") & Exp.EqColumns("t.tenant_id", "pc.tenant_id"))
.Select(ProjectDao.ProjectColumns.Select(c => "p." + c).ToArray())
.Select("t.id", "t.title", "t.status", "t.create_by", "t.create_on", "t.last_modified_by", "t.last_modified_on", "t.content")
.Select("max(coalesce(pc.create_on, t.create_on)) comments")
.GroupBy("t.id")
.Where("t.tenant_id", Tenant);
}
public void DeleteFolder(object folderId)
{
var dropboxFolder = GetDropboxFolder(folderId);
var id = MakeId(dropboxFolder);
using (var db = GetDb())
using (var tx = db.BeginTransaction())
{
var hashIDs = db.ExecuteList(Query("files_thirdparty_id_mapping")
.Select("hash_id")
.Where(Exp.Like("id", id, SqlLike.StartWith)))
.ConvertAll(x => x[0]);
db.ExecuteNonQuery(Delete("files_tag_link").Where(Exp.In("entry_id", hashIDs)));
db.ExecuteNonQuery(Delete("files_tag").Where(Exp.EqColumns("0", Query("files_tag_link l").SelectCount().Where(Exp.EqColumns("tag_id", "id")))));
db.ExecuteNonQuery(Delete("files_security").Where(Exp.In("entry_id", hashIDs)));
db.ExecuteNonQuery(Delete("files_thirdparty_id_mapping").Where(Exp.In("hash_id", hashIDs)));
tx.Commit();
}
if (!(dropboxFolder is ErrorFolder))
{
DropboxProviderInfo.Storage.DeleteItem(dropboxFolder);
}
DropboxProviderInfo.CacheReset(MakeDropboxPath(dropboxFolder), true);
var parentFolderPath = GetParentFolderPath(dropboxFolder);
if (parentFolderPath != null)
{
DropboxProviderInfo.CacheReset(parentFolderPath);
}
}
public List <TimeSpend> GetByFilter(TaskFilter filter)
{
var query = CreateQuery();
if (filter.Max != 0 && !filter.Max.Equals(int.MaxValue))
{
query.SetFirstResult((int)filter.Offset);
query.SetMaxResults((int)filter.Max * 2);
}
if (filter.MyProjects || filter.MyMilestones)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("t.project_id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
if (filter.Milestone.HasValue || filter.MyMilestones)
{
query.InnerJoin(MilestonesTable + " pm", Exp.EqColumns("pm.tenant_id", "t.tenant_id") & Exp.EqColumns("pm.project_id", "t.project_id"));
query.InnerJoin(TasksTable + " pt", Exp.EqColumns("pt.tenant_id", "t.tenant_id") & Exp.EqColumns("pt.id", "t.relative_task_id") & Exp.EqColumns("pt.milestone_id", "pm.id"));
if (filter.Milestone.HasValue)
{
query.Where("pm.id", filter.Milestone);
}
else if (filter.MyMilestones)
{
query.Where(Exp.Gt("pm.id", 0));
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (!string.IsNullOrEmpty(filter.SortBy))
{
var sortColumns = filter.SortColumns["TimeSpend"];
sortColumns.Remove(filter.SortBy);
query.OrderBy("t." + filter.SortBy, filter.SortOrder);
foreach (var sort in sortColumns.Keys)
{
query.OrderBy("t." + sort, sortColumns[sort]);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.person_id", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.person_id") & Exp.EqColumns("cug.tenant", "t.tenant_id"));
query.Where("cug.groupid", filter.DepartmentId);
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue) &&
!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Between("t.date", filter.FromDate, filter.ToDate));
}
if (filter.PaymentStatuses.Any())
{
query.Where(Exp.In("payment_status", filter.PaymentStatuses));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.note", filter.SearchText, SqlLike.AnyWhere));
}
query.GroupBy("t.id");
using (var db = new DbManager(DatabaseId))
{
return(db.ExecuteList(query).ConvertAll(r => ToTimeSpend(r)));
}
}
public static IEnumerable <AuditEvent> GetByFilter(
Guid?userId = null,
ProductType?productType = null,
ModuleType?moduleType = null,
ActionType?actionType = null,
MessageAction?action = null,
EntryType?entry = null,
string target = null,
DateTime?from = null,
DateTime?to = null,
int startIndex = 0,
int limit = 0)
{
var q = new SqlQuery("audit_events a")
.Select(auditColumns.Select(x => "a." + x).ToArray())
.LeftOuterJoin("core_user u", Exp.EqColumns("a.user_id", "u.id"))
.Select("u.firstname", "u.lastname")
.Where("a.tenant_id", TenantProvider.CurrentTenantID)
.OrderBy("a.date", false);
if (startIndex > 0)
{
q.SetFirstResult(startIndex);
}
if (limit > 0)
{
q.SetMaxResults(limit);
}
if (userId.HasValue && userId.Value != Guid.Empty)
{
q.Where("a.user_id", userId.Value.ToString());
}
var isNeedFindEntry = entry.HasValue && entry.Value != EntryType.None && target != null;
if (action.HasValue && action.Value != MessageAction.None)
{
q.Where("a.action", (int)action);
}
else
{
IEnumerable <KeyValuePair <MessageAction, MessageMaps> > actions = new List <KeyValuePair <MessageAction, MessageMaps> >();
var isFindActionType = actionType.HasValue && actionType.Value != ActionType.None;
if (productType.HasValue && productType.Value != ProductType.None)
{
var productMapper = AuditActionMapper.Mappers.FirstOrDefault(m => m.Product == productType.Value);
if (productMapper != null)
{
if (moduleType.HasValue && moduleType.Value != ModuleType.None)
{
var moduleMapper = productMapper.Mappers.FirstOrDefault(m => m.Module == moduleType.Value);
if (moduleMapper != null)
{
actions = moduleMapper.Actions;
}
}
else
{
actions = productMapper.Mappers.SelectMany(r => r.Actions);
}
}
}
else
{
actions = AuditActionMapper.Mappers
.SelectMany(r => r.Mappers)
.SelectMany(r => r.Actions);
}
if (isFindActionType || isNeedFindEntry)
{
actions = actions
.Where(a => (!isFindActionType || a.Value.ActionType == actionType.Value) && (!isNeedFindEntry || (entry.Value == a.Value.EntryType1) || entry.Value == a.Value.EntryType2))
.ToList();
}
if (isNeedFindEntry)
{
FindByEntry(q, entry.Value, target, actions);
}
else
{
var keys = actions.Select(x => (int)x.Key).ToList();
q.Where(Exp.In("a.action", keys));
}
}
var hasFromFilter = (from.HasValue && from.Value != DateTime.MinValue);
var hasToFilter = (to.HasValue && to.Value != DateTime.MinValue);
if (hasFromFilter || hasToFilter)
{
if (hasFromFilter)
{
if (hasToFilter)
{
q.Where(Exp.Between("a.date", from, to));
}
else
{
q.Where(Exp.Ge("a.date", from));
}
}
else if (hasToFilter)
{
q.Where(Exp.Le("a.date", to));
}
}
using (var db = DbManager.FromHttpContext(dbid))
{
return(db
.ExecuteList(q)
.Select(ToAuditEvent)
.Where(x => x != null));
}
}
public List <Invoice> GetCrudeInvoices(
String searchText,
InvoiceStatus?status,
DateTime issueDateFrom,
DateTime issueDateTo,
DateTime dueDateFrom,
DateTime dueDateTo,
EntityType entityType,
int entityID,
String currency,
int from,
int count,
OrderBy orderBy)
{
var invoicesTableAlias = "i_tbl";
var sqlQuery = GetInvoiceSqlQuery(null, invoicesTableAlias);
var withParams = hasParams(searchText, status, issueDateFrom, issueDateTo, dueDateFrom, dueDateTo, entityType, entityID, currency);
var whereConditional = WhereConditional(invoicesTableAlias, new List <int>(), searchText, status, issueDateFrom, issueDateTo, dueDateFrom, dueDateTo, entityType, entityID, currency);
// WhereConditional(CRMSecurity.GetPrivateItems(typeof(Invoice)).ToList(), searchText);
if (withParams && whereConditional == null)
{
return(new List <Invoice>());
}
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
if (orderBy != null && Enum.IsDefined(typeof(InvoiceSortedByType), orderBy.SortedBy))
{
switch ((InvoiceSortedByType)orderBy.SortedBy)
{
case InvoiceSortedByType.Number:
sqlQuery.OrderBy(invoicesTableAlias + ".number", orderBy.IsAsc);
break;
case InvoiceSortedByType.Status:
sqlQuery.OrderBy(invoicesTableAlias + ".status", orderBy.IsAsc);
break;
case InvoiceSortedByType.DueDate:
sqlQuery.OrderBy(invoicesTableAlias + ".due_date", orderBy.IsAsc);
break;
case InvoiceSortedByType.IssueDate:
sqlQuery.OrderBy(invoicesTableAlias + ".issue_date", orderBy.IsAsc);
break;
case InvoiceSortedByType.Contact:
sqlQuery.LeftOuterJoin("crm_contact c_tbl", Exp.EqColumns(invoicesTableAlias + ".contact_id", "c_tbl.id") & Exp.EqColumns(invoicesTableAlias + ".tenant_id", "c_tbl.tenant_id"))
.OrderBy("case when c_tbl.display_name is null then 1 else 0 end, c_tbl.display_name", orderBy.IsAsc)
.OrderBy(invoicesTableAlias + ".number", true);
break;
default:
sqlQuery.OrderBy(invoicesTableAlias + ".number", true);
break;
}
}
else
{
sqlQuery.OrderBy(invoicesTableAlias + ".number", true);
}
return(Db.ExecuteList(sqlQuery).ConvertAll(ToInvoice));
}
public static List <StatisticUser> GetUserStatisticForModules(string login, DateTime from, DateTime till)
{
using (var dbManager = DbManager.FromHttpContext(Dbid))
{
var sql = new SqlQuery(ResDataTable + " as r1");
sql.SelectCount().Select("concat_ws(':', res_files.projectName,res_files.moduleName)", "r1.authorLogin", "sum(length(r2.textvalue))")
.InnerJoin(ResDataTable + " as r2", Exp.And(Exp.EqColumns("r1.fileID", "r2.fileID"), Exp.EqColumns("r1.title", "r2.title")))
.InnerJoin(ResFilesTable, Exp.EqColumns("r1.fileid", ResFilesTable + ".id"))
.Where(!Exp.Eq("r1.flag", 4))
.Where(!Exp.Eq("r1.flag", 3))
.Where(!Exp.Eq("r1.authorLogin", "Console"))
.Where(!Exp.Eq("r1.cultureTitle", "Neutral"))
.Where(Exp.Ge("r1.timeChanges", from))
.Where(Exp.Le("r1.timeChanges", till))
.Where("r2.cultureTitle", "Neutral")
.Where(Exp.Eq("r1.authorLogin", login))
.GroupBy("r1.fileid", "r1.authorLogin")
.OrderBy("r1.fileid", true)
.OrderBy("r1.authorLogin", true);
return(dbManager.ExecuteList(sql).Select(r => new StatisticUser {
WordsCount = Convert.ToInt32(r[0]), Module = (string)r[1], Login = (string)r[2], SignCount = Convert.ToInt32(r[3])
}).ToList());
}
}
public List <Page> SearchPagesByContent(string content)
{
if (string.IsNullOrEmpty(content))
{
return(new List <Page>());
}
IEnumerable <string> pagenames = null;
List <int> pages;
if (FactoryIndexer <WikiWrapper> .TrySelectIds(r => r.MatchAll(content), out pages))
{
return(GetPagesById(pages));
}
var keys = content.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.Select(k => k.Trim())
.Where(k => 3 <= k.Length);
var where = Exp.Empty;
foreach (var k in keys)
{
@where &= Exp.Like("h.pagename", k) | Exp.Like("h.body", k);
}
var q = Query("wiki_pages p")
.Select("p.pagename")
.InnerJoin("wiki_pages_history h", Exp.EqColumns("p.tenant", "h.tenant") & Exp.EqColumns("p.pagename", "h.pagename") & Exp.EqColumns("p.version", "h.version"))
.Where(@where)
.OrderBy("p.modified_on", false)
.SetMaxResults(MAX_FIND);
pagenames = db
.ExecuteList(q)
.ConvertAll(r => (string)r[0]);
return(GetPages(pagenames));
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
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 (filter.MessageStatus.HasValue)
{
query.Where("t.status", filter.MessageStatus.Value);
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
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) & !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;
}
private SqlQuery PageQuery(Exp where)
{
return(Query("wiki_pages p")
.InnerJoin("wiki_pages_history h", Exp.EqColumns("p.tenant", "h.tenant") & Exp.EqColumns("p.pagename", "h.pagename") & Exp.Eq("h.version", 1))
.Select("p.pagename", "p.version", "p.modified_by", "p.modified_on", "h.create_by", "p.id")
.Where(where));
}
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 void DeleteFile(object fileId)
{
var boxFile = GetBoxFile(fileId);
if (boxFile == null)
{
return;
}
var id = MakeId(boxFile.Id);
using (var db = GetDb())
using (var tx = db.BeginTransaction())
{
var hashIDs = db.ExecuteList(Query("files_thirdparty_id_mapping")
.Select("hash_id")
.Where(Exp.Like("id", id, SqlLike.StartWith)))
.ConvertAll(x => x[0]);
db.ExecuteNonQuery(Delete("files_tag_link").Where(Exp.In("entry_id", hashIDs)));
db.ExecuteNonQuery(Delete("files_tag").Where(Exp.EqColumns("0", Query("files_tag_link l").SelectCount().Where(Exp.EqColumns("tag_id", "id")))));
db.ExecuteNonQuery(Delete("files_security").Where(Exp.In("entry_id", hashIDs)));
db.ExecuteNonQuery(Delete("files_thirdparty_id_mapping").Where(Exp.In("hash_id", hashIDs)));
tx.Commit();
}
if (!(boxFile is ErrorFile))
{
BoxProviderInfo.Storage.DeleteItem(boxFile);
}
BoxProviderInfo.CacheReset(boxFile.Id, true);
var parentFolderId = GetParentFolderId(boxFile);
if (parentFolderId != null)
{
BoxProviderInfo.CacheReset(parentFolderId);
}
}
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 List <Project> GetByFilter(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var teamQuery = new SqlQuery(ParticipantTable + " pp")
.SelectCount()
.InnerJoin("core_user cup", Exp.EqColumns("cup.tenant", "pp.tenant") & Exp.EqColumns("cup.id", "pp.participant_id"))
.Where(Exp.EqColumns("pp.tenant", "p.tenant_id"))
.Where(Exp.EqColumns("pp.project_id", "p.id"))
.Where("pp.removed", false)
.Where("cup.status", EmployeeStatus.Active);
var query = new SqlQuery(ProjectsTable + " p")
.Select(ProjectColumns.Select(c => "p." + c).ToArray())
.Select(new SqlQuery(MilestonesTable + " m").SelectCount().Where(Exp.EqColumns("m.tenant_id", "p.tenant_id") & Exp.EqColumns("m.project_id", "p.id")).Where(Exp.Eq("m.status", MilestoneStatus.Open)))
.Select(new SqlQuery(TasksTable + " t").SelectCount().Where(Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id")).Where(!Exp.Eq("t.status", TaskStatus.Closed)))
.Select(new SqlQuery(TasksTable + " t").SelectCount().Where(Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id")))
.Select(teamQuery)
.Select("p.private")
.Where("p.tenant_id", Tenant);
if (filter.Max > 0 && filter.Max < 150000)
{
query.SetFirstResult((int)filter.Offset);
query.SetMaxResults((int)filter.Max * 2);
}
query.OrderBy("(case p.status when 2 then 1 when 1 then 2 else 0 end)", true);
if (!string.IsNullOrEmpty(filter.SortBy))
{
var sortColumns = filter.SortColumns["Project"];
sortColumns.Remove(filter.SortBy);
query.OrderBy("p." + filter.SortBy, filter.SortOrder);
foreach (var sort in sortColumns.Keys)
{
query.OrderBy("p." + sort, sortColumns[sort]);
}
}
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
return(Db.ExecuteList(query).ConvertAll(ToProjectFull));
}
public IEnumerable <Tag> GetNewTags(Guid subject, Folder parentFolder, bool deepSearch)
{
using (var DbManager = GetDb())
{
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().Where(Exp.Exists(
new SqlQuery("files_security fs")
.Select("fs.entry_id")
.Where(
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.EqColumns("f.tenant_id", "ftl.tenant_id") &
!Exp.Eq("f.create_by", subject) &
Exp.EqColumns("f.id", "ftl.entry_id") &
Exp.Eq("ftl.entry_type", (int)FileEntryType.File))
.Select(GetRootFolderType("folder_id")))
.Where(r => ParseRootFolderType(r[7]) == FolderType.USER).ToList()
.ConvertAll(ToTag);
tempTags = tempTags.Concat(tmpShareFileTags);
var tmpShareFolderTags = DbManager.ExecuteList(
shareQuery().InnerJoin("files_folder f",
Exp.EqColumns("f.tenant_id", "ftl.tenant_id") &
!Exp.Eq("f.create_by", subject) &
Exp.EqColumns("f.id", "ftl.entry_id") &
Exp.Eq("ftl.entry_type", (int)FileEntryType.Folder))
.Select(GetRootFolderType("parent_id")))
.Where(r => ParseRootFolderType(r[7]) == FolderType.USER).ToList()
.ConvertAll(ToTag);
tempTags = tempTags.Concat(tmpShareFolderTags);
var tmpShareSboxTags = DbManager.ExecuteList(
shareQuery()
.InnerJoin("files_thirdparty_id_mapping m",
Exp.EqColumns("m.tenant_id", "ftl.tenant_id") &
Exp.EqColumns("m.hash_id", "ftl.entry_id"))
.InnerJoin("files_thirdparty_account ac",
Exp.EqColumns("ac.tenant_id", "m.tenant_id") &
Exp.Sql("m.id Like concat('sbox-', ac.id, '%') or m.id Like concat('box-', ac.id, '%') or m.id Like concat('spoint-', ac.id, '%') or m.id Like concat('drive-', ac.id, '%')") &
!Exp.Eq("ac.user_id", subject) &
Exp.Eq("ac.folder_type", FolderType.USER)
)
).ConvertAll(ToTag);
tempTags = tempTags.Concat(tmpShareSboxTags);
}
else if (parentFolder.FolderType == FolderType.Projects)
{
tempTags = tempTags.Concat(
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(ToTag));
}
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(ToTag);
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(ToTag);
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 folderIds = DbManager.ExecuteList(querySelect);
var thirdpartyFolderIds = folderIds.ConvertAll(r => "sbox-" + r[0])
.Concat(folderIds.ConvertAll(r => "box-" + r[0]))
.Concat(folderIds.ConvertAll(r => "spoint-" + r[0]))
.Concat(folderIds.ConvertAll(r => "drive-" + r[0]));
var newTagsForSBox = DbManager.ExecuteList(getBaseSqlQuery()
.InnerJoin("files_thirdparty_id_mapping mp",
Exp.EqColumns("mp.tenant_id", "ftl.tenant_id") &
Exp.EqColumns("ftl.entry_id", "mp.hash_id"))
.Where(Exp.In("mp.id", thirdpartyFolderIds.ToList()) &
Exp.Eq("ft.owner", subject) &
Exp.Eq("ftl.entry_type", (int)FileEntryType.Folder)))
.ConvertAll(ToTag);
result.AddRange(newTagsForSBox);
}
return(result);
}
}
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.HasValue)
{
if (taskStatus.Value == 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.EqColumns("p.tenant_id", "ppp.tenant") & 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)));
}
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]);
}
));
}
public void DeleteFolder(object folderId)
{
var folder = ProviderInfo.GetFolderById(folderId);
using (var dbManager = new DbManager(FileConstant.DatabaseId))
{
using (var tx = dbManager.BeginTransaction())
{
var hashIDs = dbManager.ExecuteList(Query("files_thirdparty_id_mapping").Select("hash_id").Where(Exp.Like("id", folder.ServerRelativeUrl, SqlLike.StartWith))).ConvertAll(x => x[0]);
dbManager.ExecuteNonQuery(Delete("files_tag_link").Where(Exp.In("entry_id", hashIDs)));
dbManager.ExecuteNonQuery(Delete("files_tag").Where(Exp.EqColumns("0", Query("files_tag_link l").SelectCount().Where(Exp.EqColumns("tag_id", "id")))));
dbManager.ExecuteNonQuery(Delete("files_security").Where(Exp.In("entry_id", hashIDs)));
dbManager.ExecuteNonQuery(Delete("files_thirdparty_id_mapping").Where(Exp.In("hash_id", hashIDs)));
tx.Commit();
}
}
ProviderInfo.DeleteFolder((string)folderId);
}
public void DeleteFolder(object folderId)
{
var folder = GetFolderById(folderId);
var id = MakeId(folder);
using (var tx = DbManager.BeginTransaction())
{
var hashIDs = DbManager.ExecuteList(Query("files_thirdparty_id_mapping")
.Select("hash_id")
.Where(Exp.Like("id", id, SqlLike.StartWith)))
.ConvertAll(x => x[0]);
DbManager.ExecuteNonQuery(Delete("files_tag_link").Where(Exp.In("entry_id", hashIDs)));
DbManager.ExecuteNonQuery(Delete("files_tag").Where(Exp.EqColumns("0", Query("files_tag_link l").SelectCount().Where(Exp.EqColumns("tag_id", "id")))));
DbManager.ExecuteNonQuery(Delete("files_security").Where(Exp.In("entry_id", hashIDs)));
DbManager.ExecuteNonQuery(Delete("files_thirdparty_id_mapping").Where(Exp.In("hash_id", hashIDs)));
tx.Commit();
}
if (!(folder is ErrorEntry))
{
GoogleDriveProviderInfo.Storage.DeleteFileSystemEntry(folder);
}
}
public Page GetPage(string pagename, int version)
{
var q = Query("wiki_pages_history h")
.Select("h.pagename", "h.version", "h.create_by", "h.create_on")
.Select(new SqlQuery("wiki_pages_history t").Select("t.create_by").Where(Exp.EqColumns("h.tenant", "t.tenant") & Exp.EqColumns("h.pagename", "t.pagename") & Exp.Eq("t.version", 1)))
.Select(new SqlQuery("wiki_pages p").Select("p.id").Where(Exp.EqColumns("p.tenant", "h.tenant") & Exp.EqColumns("p.pagename", "h.pagename")))
.Select("h.body")
.Where("h.pagename", pagename)
.OrderBy("h.version", false)
.SetMaxResults(1);
if (0 < version)
{
q.Where("h.version", version);
}
return(ExecQuery(q)
.SingleOrDefault());
}
public IEnumerable <VoipCall> GetMissedCalls(Guid agent, long count = 0, DateTime?from = null)
{
using (var db = GetDb())
{
var query = GetCallsQuery(new VoipCallFilter {
Agent = agent, SortBy = "date", SortOrder = true, Type = "missed"
});
var subQuery = new SqlQuery("crm_voip_calls tmp")
.SelectMax("tmp.dial_date")
.Where(Exp.EqColumns("ca.tenant_id", "tmp.tenant_id"))
.Where(Exp.EqColumns("ca.number_from", "tmp.number_from") | Exp.EqColumns("ca.number_from", "tmp.number_to"))
.Where(Exp.Lt("tmp.status", VoipCallStatus.Missed));
if (from.HasValue)
{
query.Where(Exp.Ge("ca.dial_date", TenantUtil.DateTimeFromUtc(from.Value)));
}
if (count != 0)
{
query.SetMaxResults((int)count);
}
query.Select(subQuery, "tmp_date");
query.Having(Exp.Sql("ca.dial_date >= tmp_date") | Exp.Eq("tmp_date", null));
return(db.ExecuteList(query).ConvertAll(ToCall));
}
}
public List <Page> GetPageHistory(string pagename)
{
var q = Query("wiki_pages_history h")
.Select("h.pagename", "h.version", "h.create_by", "h.create_on")
.Select(new SqlQuery("wiki_pages_history t").Select("t.create_by").Where(Exp.EqColumns("h.tenant", "t.tenant") & Exp.EqColumns("h.pagename", "t.pagename") & Exp.Eq("t.version", 1)))
.Select(new SqlQuery("wiki_pages p").Select("p.id").Where(Exp.EqColumns("p.tenant", "h.tenant") & Exp.EqColumns("p.pagename", "h.pagename")))
.Where("h.pagename", pagename)
.OrderBy("h.version", false);
return(ExecQuery(q));
}
public List <Project> GetByParticipiant(Guid participantId, ProjectStatus status)
{
var query = Query(ProjectsTable)
.Select(ProjectColumns)
.InnerJoin(ParticipantTable, Exp.EqColumns("id", "project_id") & Exp.Eq("removed", false) & Exp.EqColumns("tenant_id", "tenant"))
.Where("status", status)
.Where("participant_id", participantId.ToString())
.OrderBy("title", true);
return(Db.ExecuteList(query).ConvertAll(converter));
}
public static List <StatisticModule> GetStatistic()
{
using (var dbManager = DbManager.FromHttpContext(Dbid))
{
var sql = new SqlQuery(ResDataTable + " r1")
.Select("sum(LENGTH(r1.textvalue) - LENGTH(REPLACE(r1.textvalue, ' ', '')) + 1)")
.Select("rc.value", "rf.projectName")
.Select("sum(LENGTH(r2.textvalue) - LENGTH(REPLACE(r2.textvalue, ' ', '')) + 1)")
.InnerJoin(ResFilesTable + " as rf", Exp.EqColumns("rf.id", "r1.fileid"))
.InnerJoin(ResCultureTable + " as rc", Exp.EqColumns("r1.cultureTitle", "rc.title"))
.InnerJoin(ResDataTable + " as r2", Exp.And(Exp.EqColumns("r1.fileID", "r2.fileID"), Exp.EqColumns("r1.title", "r2.title")))
.Where(Exp.Lt("r1.flag", 3))
.Where("r1.resourceType", "text")
.Where("rf.isLock", 0)
.Where("r2.cultureTitle", "Neutral")
.Where(!Exp.In("rf.id", new List <int> {
259, 260, 261
}))
.GroupBy("rc.value", "rf.projectName")
.OrderBy("rc.value", true)
.OrderBy("rf.projectName", true);
var stat = dbManager.ExecuteList(sql);
var allStat = new List <StatisticModule>();
foreach (var culture in stat.Select(data => (string)data[1]).Distinct())
{
var cultureData = new StatisticModule {
Culture = culture
};
foreach (var project in stat.Select(data => (string)data[2]).Distinct())
{
var data = stat.Where(r => (string)r[1] == culture && (string)r[2] == project).ToList();
cultureData.Counts.Add(project, new Tuple <int, int>(data.Sum(r => Convert.ToInt32(r[0])), data.Sum(r => Convert.ToInt32(r[3]))));
}
allStat.Add(cultureData);
}
return(allStat);
}
}