public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var q1 = new SqlQuery("files_file f")
.Select(FileColumns().Select(f => "f." + f).ToArray())
.Select(DocumentsDbHelper.GetRootFolderType("folder_id"))
.Select("null, null, null")
.Where(
Exp.Eq("f.tenant_id", filter.Tenant)
& Exp.Eq("f.current_version", 1)
& Exp.Between("f.modified_on", filter.Time.From, filter.Time.To));
var q2 = new SqlQuery("files_file f")
.Select(FileColumns().Select(f => "f." + f).ToArray())
.Select(DocumentsDbHelper.GetRootFolderType("folder_id"))
.LeftOuterJoin("files_security s",
Exp.EqColumns("s.entry_id", "f.id") &
Exp.Eq("s.tenant_id", filter.Tenant) &
Exp.Eq("s.entry_type", (int)FileEntryType.File)
)
.Select("s.timestamp, s.owner, s.subject")
.Where(Exp.Eq("f.tenant_id", filter.Tenant) &
Exp.Eq("f.current_version", 1) &
Exp.Lt("s.security", 3) &
Exp.Between("s.timestamp", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var files = db.ExecuteList(q1.UnionAll(q2)).ConvertAll(ToFile);
return(files
.Where(f => f.RootFolderType != FolderType.TRASH && f.RootFolderType != FolderType.BUNCH)
.Select(f => new Tuple <Feed, object>(ToFeed(f), f)));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("blogs_posts p")
.Select(BlogColumns().Select(p => "p." + p).ToArray())
.LeftOuterJoin("blogs_comments c",
Exp.EqColumns("c.post_id", "p.id") &
Exp.Eq("c.inactive", 0) &
Exp.Eq("c.tenant", filter.Tenant)
)
.Select(BlogCommentColumns().Select(c => "c." + c).ToArray())
.Where("p.tenant", filter.Tenant)
.Where(Exp.Between("p.created_when", filter.Time.From, filter.Time.To) |
Exp.Between("c.created_when", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(query)
.ConvertAll(ToComment);
var groupedBlogs = comments.GroupBy(c => c.Post.ID);
return(groupedBlogs
.Select(b => new Tuple <Post, IEnumerable <Comment> >(b.First().Post, b))
.Select(ToFeed));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var q1 = new SqlQuery("projects_messages d")
.Select(DiscussionColumns().Select(d => "d." + d).ToArray())
.Select(ProjectColumns().Select(p => "p." + p).ToArray())
.Select(CommentColumns().Select(c => "c." + c).ToArray())
.InnerJoin("projects_projects p", Exp.EqColumns("p.id", "d.project_id") & Exp.Eq("p.tenant_id", filter.Tenant))
.LeftOuterJoin("projects_comments c",
Exp.EqColumns("c.target_uniq_id", "concat('Message_', d.id)") &
Exp.Eq("c.tenant_id", filter.Tenant) & Exp.Eq("c.inactive", 0))
.Where("d.tenant_id", filter.Tenant)
.Where(Exp.Between("d.create_on", filter.Time.From, filter.Time.To));
var q2 = new SqlQuery("projects_messages d")
.Select(DiscussionColumns().Select(d => "d." + d).ToArray())
.Select(ProjectColumns().Select(p => "p." + p).ToArray())
.Select(CommentColumns().Select(c => "c." + c).ToArray())
.InnerJoin("projects_projects p", Exp.EqColumns("p.id", "d.project_id") & Exp.Eq("p.tenant_id", filter.Tenant))
.LeftOuterJoin("projects_comments c",
Exp.EqColumns("c.target_uniq_id", "concat('Message_', d.id)") &
Exp.Eq("c.tenant_id", filter.Tenant) & Exp.Eq("c.inactive", 0))
.Where("d.tenant_id", filter.Tenant)
.Where(Exp.Between("c.create_on", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(q1.Union(q2)).ConvertAll(ToComment);
var groupedDiscussions = comments.GroupBy(c => c.Discussion.ID);
return(groupedDiscussions
.Select(d => new Tuple <Message, IEnumerable <ProjectComment> >(d.First().Discussion, d))
.Select(ToFeed));
}
}
public async Task <IActionResult> Index(FeedRequestForm form)
{
if (form.IsEmpty())
{
ViewBag.FeedItems = ImmutableArray <FeedItem> .Empty;
}
else
{
IEnumerable <string> urls = form.Urls.SplitLines();
var filter = new FeedFilter
{
ContentKeyWords = form.ContentKeyWords.SplitLines(),
Categories = form.Categories.SplitLines(),
DateFrom = null,
DateTo = null
};
IEnumerable <string> emails = form.Emails.SplitLines();
var feedItems = await _service.LoadAsync(urls, filter);
_service.MailAsync(emails, feedItems);
ViewBag.FeedItems = feedItems;
}
return(View());
}
public async Task TestGet()
{
var newActivity = new Stream.Activity("1", "test", "1");
var first = await this._user1.AddActivity(newActivity);
newActivity = new Stream.Activity("1", "test", "2");
var second = await this._user1.AddActivity(newActivity);
newActivity = new Stream.Activity("1", "test", "3");
var third = await this._user1.AddActivity(newActivity);
Thread.Sleep(AddDelay * 3);
var activities = await this._user1.GetActivities(0, 2);
Assert.IsNotNull(activities);
Assert.AreEqual(2, activities.Count());
Assert.AreEqual(third.Id, activities.First().Id);
Assert.AreEqual(second.Id, activities.Skip(1).First().Id);
activities = await this._user1.GetActivities(1, 2);
Assert.AreEqual(second.Id, activities.First().Id);
//$id_offset = ['id_lt' => $third_id];
activities = await this._user1.GetActivities(0, 2, FeedFilter.Where().IdLessThan(third.Id));
Assert.AreEqual(second.Id, activities.First().Id);
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query =
new SqlQuery("projects_tasks t")
.Select(TaskColumns().Select(t => "t." + t).ToArray())
.LeftOuterJoin("projects_tasks_responsible r", Exp.EqColumns("r.task_id", "t.id") & Exp.Eq("r.tenant_id", filter.Tenant))
.Select("group_concat(distinct r.responsible_id)")
.InnerJoin("projects_projects p", Exp.EqColumns("p.id", "t.project_id") & Exp.Eq("p.tenant_id", filter.Tenant))
.Select(ProjectColumns().Select(p => "p." + p).ToArray())
.LeftOuterJoin("projects_comments c", Exp.EqColumns("c.target_uniq_id", "concat('Task_', convert(t.id, char))") & Exp.Eq("c.tenant_id", filter.Tenant) & Exp.Eq("c.inactive", 0))
.Select(CommentColumns().Select(c => "c." + c).ToArray())
.Where("t.tenant_id", filter.Tenant)
.Where(Exp.Between("t.create_on", filter.Time.From, filter.Time.To) | Exp.Between("c.create_on", filter.Time.From, filter.Time.To))
.GroupBy("c.id, t.id");
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(query)
.ConvertAll(ToComment);
var groupedTasks = comments.GroupBy(c => c.Task.ID);
return(groupedTasks
.Select(t => new Tuple <Task, IEnumerable <ProjectComment> >(t.First().Task, t))
.Select(ToFeed));
}
}
public async Task <FeedPageListModel> GetFeedsAsync(FeedFilterModel criterias)
{
if (criterias == null)
{
criterias = new FeedFilterModel();
}
var filterRequest = new FeedFilter()
{
Page = criterias.Page,
PageSize = _pagerOptions.PageSize,
Keyword = criterias.Search
};
try
{
var feedPageList = await _feedService.GetAsync(filterRequest);
var feeds = await _feedModelService.MapFeedsResultToModelAsync(feedPageList.Collections);
var feedPage = new FeedPageListModel(feeds)
{
Filter = criterias,
TotalPage = feedPageList.TotalPage,
TotalResult = feedPageList.TotalResult
};
return(feedPage);
}
catch (Exception)
{
throw;
}
}
public async Task <SearchInGroupResult> LiveSearchInGroupAsync(FeedFilter filter)
{
var groupOfSearch = await _feedRepository.GetInGroupAsync(filter);
await SetPicturesForSearchResultAsync(groupOfSearch);
return(groupOfSearch);
}
public static async Task GetFeed(int page, GameFilter game, TypeFilter type, FeedFilter filter, GameBananaCategory category, GameBananaCategory subcategory, int perPage)
{
error = false;
if (feed == null)
{
feed = new Dictionary <string, GameBananaModList>();
}
// Remove oldest key if more than 15 pages are cached
if (feed.Count > 15)
{
feed.Remove(feed.Aggregate((l, r) => DateTime.Compare(l.Value.TimeFetched, r.Value.TimeFetched) < 0 ? l : r).Key);
}
using (var httpClient = new HttpClient())
{
var requestUrl = GenerateUrl(page, game, type, filter, category, subcategory, perPage);
if (feed.ContainsKey(requestUrl) && feed[requestUrl].IsValid)
{
CurrentFeed = feed[requestUrl];
return;
}
try
{
var response = await httpClient.GetAsync(requestUrl);
var responseString = await response.Content.ReadAsStringAsync();
responseString = responseString.Replace(@"""_aModManagerIntegrations"": []", @"""_aModManagerIntegrations"": {}");
var records = JsonConvert.DeserializeObject <ObservableCollection <GameBananaRecord> >(responseString);
CurrentFeed = new GameBananaModList();
CurrentFeed.Records = records;
// Get record count from header
var numRecords = response.GetHeader("X-GbApi-Metadata_nRecordCount");
if (numRecords != -1)
{
var totalPages = Convert.ToInt32(Math.Ceiling(numRecords / Convert.ToDouble(perPage)));
if (totalPages == 0)
{
totalPages = 1;
}
CurrentFeed.TotalPages = totalPages;
}
}
catch (Exception e)
{
error = true;
exception = e;
return;
}
if (!feed.ContainsKey(requestUrl))
{
feed.Add(requestUrl, CurrentFeed);
}
else
{
feed[requestUrl] = CurrentFeed;
}
}
}
public async Task <SearchInGroupResultModel> AdvancedSearchAsync(FeedFilterModel criterias)
{
if (criterias == null)
{
criterias = new FeedFilterModel();
}
var currentDateTime = DateTimeOffset.UtcNow;
DateTimeOffset?createdDateFrom = criterias.HoursCreatedFrom.HasValue ? currentDateTime.AddHours(-criterias.HoursCreatedFrom.Value) : null;
DateTimeOffset?createdDateTo = criterias.HoursCreatedTo.HasValue ? currentDateTime.AddHours(-criterias.HoursCreatedTo.Value) : null;
var filter = new FeedFilter
{
Page = criterias.Page,
PageSize = _pagerOptions.AdvancedSearchPageSize,
Keyword = criterias.Search,
FilterType = criterias.FilterType.HasValue ? (FeedFilterType)criterias.FilterType : null,
CreatedDateFrom = createdDateFrom,
CreatedDateTo = createdDateTo
};
if (!string.IsNullOrEmpty(criterias.UserIdentityId))
{
filter.CreatedById = await _userManager.DecryptUserIdAsync(criterias.UserIdentityId);
}
var groupOfSearch = await _feedService.SearchInGroupAsync(filter);
var searchResult = new SearchInGroupResultModel
{
Articles = await _feedModelService.MapFeedsResultToModelAsync(groupOfSearch.Articles),
TotalArticle = groupOfSearch.TotalArticle,
TotalArticlePage = groupOfSearch.TotalArticlePage,
Products = await _feedModelService.MapFeedsResultToModelAsync(groupOfSearch.Products),
TotalProduct = groupOfSearch.TotalProduct,
TotalProductPage = groupOfSearch.TotalProductPage,
Farms = await _feedModelService.MapFeedsResultToModelAsync(groupOfSearch.Farms),
TotalFarm = groupOfSearch.TotalFarm,
TotalFarmPage = groupOfSearch.TotalFarmPage,
Users = await _feedModelService.MapFeedsResultToModelAsync(groupOfSearch.Users),
TotalUser = groupOfSearch.TotalUser,
TotalUserPage = groupOfSearch.TotalUserPage,
};
if (!string.IsNullOrEmpty(criterias.UserIdentityId))
{
filter.CreatedById = await _userManager.DecryptUserIdAsync(criterias.UserIdentityId);
var createdUser = await _userManager.FindByIdentityIdAsync(criterias.UserIdentityId);
if (createdUser != null)
{
searchResult.UserFilterByName = createdUser.DisplayName;
}
}
return(searchResult);
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var now = DateTime.UtcNow;
var list = (from u in CoreContext.UserManager.GetUsers(EmployeeStatus.Active, EmployeeType.User)
where u.BirthDate.HasValue && u.BirthDate.Value.Month.Equals(now.Month) && u.BirthDate.Value.Day.Equals(now.Day)
orderby u.DisplayUserName()
select u).ToList();
return(list.Select(c => new Tuple <Feed, object>(ToFeed(c, now), c)));
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var list = (from u in CoreContext.UserManager.GetUsers(EmployeeStatus.Active, EmployeeType.User)
where u.CreateDate >= filter.Time.From && u.CreateDate <= filter.Time.To
orderby u.DisplayUserName()
select u)
.ToList();
return(list.Select(c => new Tuple <Feed, object>(ToFeed(c), c)));
}
public static FeedFilterDto ToDto(this FeedFilter @this)
{
return(new FeedFilterDto
{
Categories = @this.Categories,
ContentKeyWords = @this.ContentKeyWords,
DateFrom = @this.DateFrom,
DateTo = @this.DateTo
});
}
public override IEnumerable <Tuple <Feed.Aggregator.Feed, object> > GetFeeds(FeedFilter filter)
{
var files = FileDao.GetFeeds(filter.Tenant, filter.Time.From, filter.Time.To)
.Where(f => f.Item1.RootFolderType != FolderType.TRASH && f.Item1.RootFolderType != FolderType.BUNCH)
.ToList();
var folderIDs = files.Select(r => r.Item1.FolderID).ToArray();
var folders = FolderDao.GetFolders(folderIDs, checkShare: false);
return(files.Select(f => new Tuple <Feed.Aggregator.Feed, object>(ToFeed(f, folders.FirstOrDefault(r => r.ID.Equals(f.Item1.FolderID))), f)));
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("crm_contact")
.Select(ContactColumns().ToArray())
.Where("tenant_id", filter.Tenant)
.Where(Exp.Between("create_on", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var contacts = db.ExecuteList(query).ConvertAll(ToContact);
return contacts.Select(c => new Tuple<Feed, object>(ToFeed(c), c));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("crm_contact")
.Select(ContactColumns().ToArray())
.Where("tenant_id", filter.Tenant)
.Where(Exp.Between("create_on", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var contacts = db.ExecuteList(query).ConvertAll(ToContact);
return(contacts.Select(c => new Tuple <Feed, object>(ToFeed(c), c)));
}
}
public static async Task GetFeed(int page, TypeFilter type, FeedFilter filter, GameBananaCategory category, GameBananaCategory subcategory, bool pending, int perPage)
{
error = false;
if (feed == null)
{
feed = new Dictionary <string, GameBananaModList>();
}
using (var httpClient = new HttpClient())
{
var requestUrl = GenerateUrl(page, type, filter, category, subcategory, pending, perPage);
if (feed.ContainsKey(requestUrl) && feed[requestUrl].IsValid)
{
CurrentFeed = feed[requestUrl];
return;
}
try
{
var response = await httpClient.GetAsync(requestUrl);
var records = JsonSerializer.Deserialize <ObservableCollection <GameBananaRecord> >(await response.Content.ReadAsStringAsync());
CurrentFeed = new();
CurrentFeed.Records = records;
// Get record count from header
var numRecords = response.GetHeader("X-GbApi-Metadata_nRecordCount");
if (numRecords != -1)
{
var totalPages = Math.Ceiling(numRecords / Convert.ToDouble(perPage));
if (totalPages == 0)
{
totalPages = 1;
}
CurrentFeed.TotalPages = totalPages;
}
}
catch (Exception e)
{
error = true;
exception = e;
return;
}
if (!feed.ContainsKey(requestUrl))
{
feed.Add(requestUrl, CurrentFeed);
}
else
{
feed[requestUrl] = CurrentFeed;
}
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("projects_projects p")
.Select("p.id", "p.title", "p.description", "p.status", "p.status_changed", "p.responsible_id")
.Select("p.private", "p.create_by", "p.create_on", "p.last_modified_by", "p.last_modified_on")
.Where("tenant_id", filter.Tenant)
.Where(Exp.Between("create_on", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var projects = db.ExecuteList(query).ConvertAll(ToProject);
return projects.Select(p => new Tuple<Feed, object>(ToFeed(p), p));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("projects_projects p")
.Select("p.id", "p.title", "p.description", "p.status", "p.status_changed", "p.responsible_id")
.Select("p.private", "p.create_by", "p.create_on", "p.last_modified_by", "p.last_modified_on")
.Where("tenant_id", filter.Tenant)
.Where(Exp.Between("create_on", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var projects = db.ExecuteList(query).ConvertAll(ToProject);
return(projects.Select(p => new Tuple <Feed, object>(ToFeed(p), p)));
}
}
public async Task <SearchInGroupResult> SearchInGroupAsync(FeedFilter filter)
{
var groupOfSearch = await _feedRepository.GetInGroupAsync(filter);
var createdByIds = groupOfSearch.Articles.Select(x => x.CreatedById)
.Concat(groupOfSearch.Products.Select(x => x.CreatedById))
.Concat(groupOfSearch.Farms.Select(x => x.CreatedById)).Distinct();
var createdByPictures = await _userPhotoRepository.GetUserPhotoByUserIdsAsync(createdByIds, UserPictureType.Avatar);
var createdByUsers = await _userRepository.GetNameByIdsAsync(createdByIds);
await SetPicturesForSearchResultAsync(groupOfSearch, createdByUsers, createdByPictures);
return(groupOfSearch);
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("forum_post p")
.Select(PostColumns().Select(p => "p." + p).ToArray())
.InnerJoin("forum_topic t", Exp.EqColumns("p.topic_id", "t.id"))
.Where("p.tenantId", filter.Tenant)
.Where(!Exp.EqColumns("p.create_date", "t.create_date"))
.Where(Exp.Between("p.create_date", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var posts = db.ExecuteList(query).ConvertAll(ToPost);
return posts.Select(c => new Tuple<Feed, object>(ToFeed(c), c));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("forum_post p")
.Select(PostColumns().Select(p => "p." + p).ToArray())
.InnerJoin("forum_topic t", Exp.EqColumns("p.topic_id", "t.id"))
.Where("p.tenantId", filter.Tenant)
.Where(!Exp.EqColumns("p.create_date", "t.create_date"))
.Where(Exp.Between("p.create_date", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var posts = db.ExecuteList(query).ConvertAll(ToPost);
return(posts.Select(c => new Tuple <Feed, object>(ToFeed(c), c)));
}
}
public async Task <FeedPageListModel> GetUserFeedsAsync(ClaimsPrincipal claimsPrincipal, FeedFilterModel criterias)
{
if (criterias == null)
{
criterias = new FeedFilterModel();
}
if (string.IsNullOrEmpty(criterias.UserIdentityId))
{
return(new FeedPageListModel(new List <FeedModel>())
{
Filter = criterias
});
}
var currentUserId = GetCurrentUserId(claimsPrincipal);
var userId = await _userManager.DecryptUserIdAsync(criterias.UserIdentityId);
var filterRequest = new FeedFilter
{
Page = criterias.Page,
PageSize = _pagerOptions.PageSize,
Keyword = criterias.Search,
CreatedById = userId,
CanGetInactived = currentUserId == userId
};
try
{
var feedPageList = await _feedService.GetAsync(filterRequest);
var feeds = await _feedModelService.MapFeedsResultToModelAsync(feedPageList.Collections);
var feedPage = new FeedPageListModel(feeds)
{
Filter = criterias,
TotalPage = feedPageList.TotalPage,
TotalResult = feedPageList.TotalResult
};
return(feedPage);
}
catch (Exception)
{
throw;
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("crm_deal d")
.Select(DealColumns().Select(d => "d." + d).ToArray())
.Where("d.tenant_id", filter.Tenant)
.Where(Exp.Between("d.create_on", filter.Time.From, filter.Time.To))
.LeftOuterJoin("crm_contact c",
Exp.EqColumns("c.id", "d.contact_id")
& Exp.Eq("c.tenant_id", filter.Tenant)
)
.Select(ContactColumns().Select(c => "c." + c).ToArray());
using (var db = new DbManager(Constants.CrmDbId))
{
var deals = db.ExecuteList(query).ConvertAll(ToDeal);
return deals.Select(d => new Tuple<Feed, object>(ToFeed(d), d));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("crm_deal d")
.Select(DealColumns().Select(d => "d." + d).ToArray())
.Where("d.tenant_id", filter.Tenant)
.Where(Exp.Between("d.create_on", filter.Time.From, filter.Time.To))
.LeftOuterJoin("crm_contact c",
Exp.EqColumns("c.id", "d.contact_id")
& Exp.Eq("c.tenant_id", filter.Tenant)
)
.Select(ContactColumns().Select(c => "c." + c).ToArray());
using (var db = new DbManager(Constants.CrmDbId))
{
var deals = db.ExecuteList(query).ConvertAll(ToDeal);
return(deals.Select(d => new Tuple <Feed, object>(ToFeed(d), d)));
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var q = new SqlQuery("projects_milestones" + " m")
.Select("m.id", "m.title", "m.description", "m.deadline", "m.responsible_id", "m.status",
"m.status_changed")
.Select("m.is_key", "m.create_by", "m.create_on", "m.last_modified_by", "m.last_modified_on")
.Where("m.tenant_id", filter.Tenant)
.Where(Exp.Between("m.create_on", filter.Time.From, filter.Time.To))
.InnerJoin("projects_projects" + " p",
Exp.EqColumns("m.project_id", "p.id") & Exp.Eq("m.tenant_id", filter.Tenant))
.Select("p.id", "p.title", "p.description", "p.status", "p.status_changed", "p.responsible_id")
.Select("p.private", "p.create_by", "p.create_on", "p.last_modified_by", "p.last_modified_on");
using (var db = new DbManager(DbId))
{
var milestones = db.ExecuteList(q).ConvertAll(ToMilestone);
return milestones.Select(m => new Tuple<Feed, object>(ToFeed(m), m));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("crm_case c")
.Select(CasesColumns().Select(c => "c." + c).ToArray())
.LeftOuterJoin("crm_entity_contact ec",
Exp.EqColumns("ec.entity_id", "c.id")
& Exp.Eq("ec.entity_type", 7)
)
.Select("group_concat(distinct cast(ec.contact_id as char))")
.Where("c.tenant_id", filter.Tenant)
.Where(Exp.Between("c.create_on", filter.Time.From, filter.Time.To))
.GroupBy("c.id");
using (var db = new DbManager(DbId))
{
var cases = db.ExecuteList(query).ConvertAll(ToCases);
return(cases.Select(c => new Tuple <Feed, object>(ToFeed(c), c)));
}
}
public IList <FeedObj> GetFeeds(FeedFilter filter)
{
using (var db = GetInnerTrackContext())
{
var items = db.Feeds.AsQueryable();
if (filter.Id.HasValue)
{
items = items.Where(i => i.Id == filter.Id.Value);
}
if (filter.ProjectId.HasValue)
{
items = items.Where(i => i.Projects.Any(p => p.Id == filter.ProjectId.Value));
}
return(items.ToList());
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var q = new SqlQuery("projects_milestones" + " m")
.Select("m.id", "m.title", "m.description", "m.deadline", "m.responsible_id", "m.status",
"m.status_changed")
.Select("m.is_key", "m.create_by", "m.create_on", "m.last_modified_by", "m.last_modified_on")
.Where("m.tenant_id", filter.Tenant)
.Where(Exp.Between("m.create_on", filter.Time.From, filter.Time.To))
.InnerJoin("projects_projects" + " p",
Exp.EqColumns("m.project_id", "p.id") & Exp.Eq("m.tenant_id", filter.Tenant))
.Select("p.id", "p.title", "p.description", "p.status", "p.status_changed", "p.responsible_id")
.Select("p.private", "p.create_by", "p.create_on", "p.last_modified_by", "p.last_modified_on");
using (var db = new DbManager(DbId))
{
var milestones = db.ExecuteList(q).ConvertAll(ToMilestone);
return(milestones.Select(m => new Tuple <Feed, object>(ToFeed(m), m)));
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query =
new SqlQuery("crm_task t")
.Select(TaskColumns().Select(t => "t." + t).ToArray())
.Where("t.tenant_id", filter.Tenant)
.Where(Exp.Between("t.create_on", filter.Time.From, filter.Time.To))
.LeftOuterJoin("crm_contact c",
Exp.EqColumns("c.id", "t.contact_id")
& Exp.Eq("c.tenant_id", filter.Tenant)
)
.Select(ContactColumns().Select(c => "c." + c).ToArray());
using (var db = new DbManager(DbId))
{
var tasks = db.ExecuteList(query).ConvertAll(ToTask);
return tasks.Select(t => new Tuple<Feed, object>(ToFeed(t), t));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var q1 = new SqlQuery("files_folder f")
.Select(FolderColumns().Select(f => "f." + f).ToArray())
.Select(DocumentsDbHelper.GetRootFolderType("parent_id"))
.Select("null, null, null")
.Where(
Exp.Eq("f.tenant_id", filter.Tenant) &
Exp.Eq("f.folder_type", 0) &
Exp.Between("f.create_on", filter.Time.From, filter.Time.To)
);
var q2 = new SqlQuery("files_folder f")
.LeftOuterJoin("files_security s",
Exp.EqColumns("s.entry_id", "f.id") &
Exp.Eq("s.tenant_id", filter.Tenant) &
Exp.Eq("s.entry_type", (int)FileEntryType.Folder)
)
.Select(FolderColumns().Select(f => "f." + f).ToArray())
.Select(DocumentsDbHelper.GetRootFolderType("parent_id"))
.Select("s.timestamp, s.owner, s.subject")
.Where(
Exp.Eq("f.tenant_id", filter.Tenant) &
Exp.Eq("f.folder_type", 0) &
Exp.Lt("s.security", 3) &
Exp.Between("s.timestamp", filter.Time.From, filter.Time.To)
);
List <Tuple <Folder, SmallShareRecord> > folders;
using (var db = new DbManager(DbId))
{
folders = db.ExecuteList(q1.UnionAll(q2))
.ConvertAll(ToFolder)
.Where(f => f.Item1.RootFolderType != FolderType.TRASH && f.Item1.RootFolderType != FolderType.BUNCH)
.ToList();
}
var parentFolderIDs = folders.Select(r => r.Item1.ParentFolderID).ToArray();
var parentFolders = new FolderDao(Tenant, DbId).GetFolders(parentFolderIDs, checkShare: false);
return(folders.Select(f => new Tuple <Feed, object>(ToFeed(f, parentFolders.FirstOrDefault(r => r.ID.Equals(f.Item1.ParentFolderID))), f)));
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var q = new SqlQuery("projects_project_participant" + " pp")
.Select("pp.participant_id", "pp.created", "pp.updated")
.InnerJoin("projects_projects" + " p",
Exp.EqColumns("p.id", "pp.project_id") & Exp.Eq("p.tenant_id", filter.Tenant))
.Select("p.id", "p.title", "p.description", "p.status", "p.status_changed", "p.responsible_id")
.Select("p.private", "p.create_by", "p.create_on", "p.last_modified_by", "p.last_modified_on")
.Where("pp.removed", 0)
.Where("pp.tenant", filter.Tenant)
.Where(Exp.Between("pp.created", filter.Time.From, filter.Time.To) &
!Exp.Between("p.create_on - pp.created", -10, 10));
using (var db = new DbManager(DbId))
{
var participants = db.ExecuteList(q).ConvertAll(ToParticipant);
return participants.Select(p => new Tuple<Feed, object>(ToFeed(p), p));
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("crm_case c")
.Select(CasesColumns().Select(c => "c." + c).ToArray())
.LeftOuterJoin("crm_entity_contact ec",
Exp.EqColumns("ec.entity_id", "c.id")
& Exp.Eq("ec.entity_type", 7)
)
.Select("group_concat(distinct cast(ec.contact_id as char))")
.Where("c.tenant_id", filter.Tenant)
.Where(Exp.Between("c.create_on", filter.Time.From, filter.Time.To))
.GroupBy("c.id");
using (var db = new DbManager(DbId))
{
var cases = db.ExecuteList(query).ConvertAll(ToCases);
return cases.Select(c => new Tuple<Feed, object>(ToFeed(c), c));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var q = new SqlQuery("projects_project_participant" + " pp")
.Select("pp.participant_id", "pp.created", "pp.updated")
.InnerJoin("projects_projects" + " p",
Exp.EqColumns("p.id", "pp.project_id") & Exp.Eq("p.tenant_id", filter.Tenant))
.Select("p.id", "p.title", "p.description", "p.status", "p.status_changed", "p.responsible_id")
.Select("p.private", "p.create_by", "p.create_on", "p.last_modified_by", "p.last_modified_on")
.Where("pp.removed", 0)
.Where("pp.tenant", filter.Tenant)
.Where(Exp.Between("pp.created", filter.Time.From, filter.Time.To) &
!Exp.Between("p.create_on - pp.created", -10, 10));
using (var db = new DbManager(DbId))
{
var participants = db.ExecuteList(q).ConvertAll(ToParticipant);
return(participants.Select(p => new Tuple <Feed, object>(ToFeed(p), p)));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query =
new SqlQuery("crm_task t")
.Select(TaskColumns().Select(t => "t." + t).ToArray())
.Where("t.tenant_id", filter.Tenant)
.Where(Exp.Between("t.create_on", filter.Time.From, filter.Time.To))
.LeftOuterJoin("crm_contact c",
Exp.EqColumns("c.id", "t.contact_id")
& Exp.Eq("c.tenant_id", filter.Tenant)
)
.Select(ContactColumns().Select(c => "c." + c).ToArray());
using (var db = new DbManager(DbId))
{
var tasks = db.ExecuteList(query).ConvertAll(ToTask);
return(tasks.Select(t => new Tuple <Feed, object>(ToFeed(t), t)));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var filterTimeInterval = TimeSpan.FromTicks(filter.Time.To.Ticks - filter.Time.From.Ticks).TotalMinutes;
var q = new SqlQuery("projects_project_participant" + " pp")
.Select("pp.participant_id", "pp.created", "pp.updated")
.InnerJoin("projects_projects" + " p",
Exp.EqColumns("p.id", "pp.project_id") & Exp.Eq("p.tenant_id", filter.Tenant))
.Select("p.id", "p.title", "p.description", "p.status", "p.status_changed", "p.responsible_id")
.Select("p.private", "p.create_by", "p.create_on", "p.last_modified_by", "p.last_modified_on")
.Where("pp.removed", 0)
.Where("pp.tenant", filter.Tenant)
.Where(Exp.Between("pp.created", filter.Time.From, filter.Time.To) &
!Exp.Between("TIMESTAMPDIFF(MINUTE,p.create_on, pp.created)", -filterTimeInterval, filterTimeInterval));
using (var db = DbManager.FromHttpContext(DbId))
{
var participants = db.ExecuteList(q).ConvertAll(ToParticipant);
return(participants.Select(p => new Tuple <Feed, object>(ToFeed(p), p)));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("forum_topic t")
.Select(TopicColumns().Select(t => "t." + t).ToArray())
.LeftOuterJoin("forum_post p",
Exp.EqColumns("t.id", "p.topic_id") &
Exp.Eq("p.tenantId", filter.Tenant)
)
.Select(PostColumns().Select(p => "p." + p).ToArray())
.Where("t.tenantid", filter.Tenant)
.Where(Exp.Between("p.create_date", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var posts = db.ExecuteList(query).ConvertAll(ToPost);
var groupedTopics = posts.GroupBy(c => c.Topic.ID);
return(groupedTopics
.Select(b => new Tuple <Topic, IEnumerable <Post> >(b.First().Topic, b))
.Select(ToFeed));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("events_feed e")
.Select(EventColumns().Select(e => "e." + e).ToArray())
.LeftOuterJoin("events_comment c",
Exp.EqColumns("c.feed", "e.id") &
Exp.Eq("c.tenant", filter.Tenant)
)
.Select(EventCommentColumns().Select(c => "c." + c).ToArray())
.Where("e.tenant", filter.Tenant)
.Where(Exp.Between("e.date", filter.Time.From, filter.Time.To) |
Exp.Between("c.date", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(query).ConvertAll(ToComment);
var groupedEvents = comments.GroupBy(c => c.Feed.Id);
return(groupedEvents
.Select(e => new Tuple <Event, IEnumerable <EventComment> >(e.First().Feed, e))
.Select(ToFeed));
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var q = new SqlQuery("bookmarking_bookmark b")
.Select("b.id", "b.url", "b.name", "b.description", "b.usercreatorid", "b.date")
.LeftOuterJoin("bookmarking_comment c",
Exp.EqColumns("b.id", "c.bookmarkId") &
Exp.Eq("c.inactive", 0) &
Exp.Eq("c.tenant", filter.Tenant)
)
.Select("c.id", "c.content", "c.userId", "c.datetime")
.Where("b.tenant", filter.Tenant)
.Where(Exp.Between("b.date", filter.Time.From, filter.Time.To) |
Exp.Between("c.datetime", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(q).ConvertAll(ToComment);
var groupedBookmarks = comments.GroupBy(c => c.Bookmark.ID);
return groupedBookmarks
.Select(b => new Tuple<Bookmark, IEnumerable<Comment>>(b.First().Bookmark, b))
.Select(ToFeed);
}
}
public override void CreateFeedFilter(FeedFilter feedFilter)
{
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "bx_CreateFeedFilter";
query.CommandType = CommandType.StoredProcedure;
query.CreateParameter<Guid>("@AppID", feedFilter.AppID, SqlDbType.UniqueIdentifier);
query.CreateParameter<int>("@UserID", feedFilter.UserID, SqlDbType.Int);
query.CreateParameter<int?>("@FriendUserID", feedFilter.FriendUserID, SqlDbType.Int);
query.CreateParameter<int?>("@ActionType", feedFilter.ActionType, SqlDbType.Int);
query.ExecuteNonQuery();
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("forum_topic t")
.Select(TopicColumns().Select(t => "t." + t).ToArray())
.LeftOuterJoin("forum_post p",
Exp.EqColumns("t.id", "p.topic_id") &
Exp.Eq("p.tenantId", filter.Tenant)
)
.Select(PostColumns().Select(p => "p." + p).ToArray())
.Where("t.tenantid", filter.Tenant)
.Where(Exp.Between("p.create_date", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var posts = db.ExecuteList(query).ConvertAll(ToPost);
var groupedTopics = posts.GroupBy(c => c.Topic.ID);
return groupedTopics
.Select(b => new Tuple<Topic, IEnumerable<Post>>(b.First().Topic, b))
.Select(ToFeed);
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("blogs_posts p")
.Select(BlogColumns().Select(p => "p." + p).ToArray())
.LeftOuterJoin("blogs_comments c",
Exp.EqColumns("c.post_id", "p.id") &
Exp.Eq("c.inactive", 0) &
Exp.Eq("c.tenant", filter.Tenant)
)
.Select(BlogCommentColumns().Select(c => "c." + c).ToArray())
.Where("p.tenant", filter.Tenant)
.Where(Exp.Between("p.created_when", filter.Time.From, filter.Time.To) |
Exp.Between("c.created_when", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(query).ConvertAll(ToComment);
var groupedBlogs = comments.GroupBy(c => c.Post.ID);
return groupedBlogs
.Select(b => new Tuple<Post, IEnumerable<Comment>>(b.First().Post, b))
.Select(ToFeed);
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("events_feed e")
.Select(EventColumns().Select(e => "e." + e).ToArray())
.LeftOuterJoin("events_comment c",
Exp.EqColumns("c.feed", "e.id") &
Exp.Eq("c.tenant", filter.Tenant)
)
.Select(EventCommentColumns().Select(c => "c." + c).ToArray())
.Where("e.tenant", filter.Tenant)
.Where(Exp.Between("e.date", filter.Time.From, filter.Time.To) |
Exp.Between("c.date", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(query).ConvertAll(ToComment);
var groupedEvents = comments.GroupBy(c => c.Feed.Id);
return groupedEvents
.Select(e => new Tuple<Event, IEnumerable<EventComment>>(e.First().Feed, e))
.Select(ToFeed);
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var q1 = new SqlQuery("files_file f")
.Select(FileColumns().Select(f => "f." + f).ToArray())
.Select(DocumentsDbHelper.GetRootFolderType("folder_id"))
.Select("null, null, null")
.Where(
Exp.Eq("f.tenant_id", filter.Tenant)
& Exp.Eq("f.current_version", 1)
& Exp.Between("f.modified_on", filter.Time.From, filter.Time.To));
var q2 = new SqlQuery("files_file f")
.Select(FileColumns().Select(f => "f." + f).ToArray())
.Select(DocumentsDbHelper.GetRootFolderType("folder_id"))
.LeftOuterJoin("files_security s",
Exp.EqColumns("s.entry_id", "f.id") &
Exp.Eq("s.tenant_id", filter.Tenant) &
Exp.Eq("s.entry_type", (int)FileEntryType.File)
)
.Select("s.timestamp, s.owner, s.subject")
.Where(Exp.Eq("f.tenant_id", filter.Tenant) &
Exp.Eq("f.current_version", 1) &
Exp.Lt("s.security", 3) &
Exp.Between("s.timestamp", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var files = db.ExecuteList(q1.UnionAll(q2)).ConvertAll(ToFile);
return files
.Where(f => f.RootFolderType != FolderType.TRASH && f.RootFolderType != FolderType.BUNCH)
.Select(f => new Tuple<Feed, object>(ToFeed(f), f));
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var query =
new SqlQuery("projects_tasks t")
.Select(TaskColumns().Select(t => "t." + t).ToArray())
.LeftOuterJoin("projects_tasks_responsible r", Exp.EqColumns("r.task_id", "t.id") & Exp.Eq("r.tenant_id", filter.Tenant))
.Select("group_concat(distinct r.responsible_id)")
.InnerJoin("projects_projects p", Exp.EqColumns("p.id", "t.project_id") & Exp.Eq("p.tenant_id", filter.Tenant))
.Select(ProjectColumns().Select(p => "p." + p).ToArray())
.LeftOuterJoin("projects_comments c", Exp.EqColumns("c.target_uniq_id", "concat('Task_', convert(t.id, char))") & Exp.Eq("c.tenant_id", filter.Tenant) & Exp.Eq("c.inactive", 0))
.Select(CommentColumns().Select(c => "c." + c).ToArray())
.Where("t.tenant_id", filter.Tenant)
.Where(Exp.Between("t.create_on", filter.Time.From, filter.Time.To) | Exp.Between("c.create_on", filter.Time.From, filter.Time.To))
.GroupBy("c.id, t.id");
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(query)
.ConvertAll(ToComment);
var groupedTasks = comments.GroupBy(c => c.Task.ID);
return groupedTasks
.Select(t => new Tuple<Task, IEnumerable<ProjectComment>>(t.First().Task, t))
.Select(ToFeed);
}
}
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter)
{
var q =
new SqlQuery("projects_messages d")
.Select(DiscussionColumns().Select(d => "d." + d).ToArray())
.InnerJoin("projects_projects p",
Exp.EqColumns("p.id", "d.project_id") &
Exp.Eq("p.tenant_id", filter.Tenant)
)
.Select(ProjectColumns().Select(p => "p." + p).ToArray())
.LeftOuterJoin("projects_comments c",
Exp.EqColumns("substring_index(c.target_uniq_id, '_', -1)", "d.id") &
Exp.Eq("substring_index(c.target_uniq_id, '_', 1)", "Message") &
Exp.Eq("c.tenant_id", filter.Tenant) &
Exp.Eq("c.inactive", 0))
.Select(CommentColumns().Select(c => "c." + c).ToArray())
.Where("d.tenant_id", filter.Tenant)
.Where(Exp.Between("d.create_on", filter.Time.From, filter.Time.To) |
Exp.Between("c.create_on", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(q).ConvertAll(ToComment);
var groupedDiscussions = comments.GroupBy(c => c.Discussion.ID);
return groupedDiscussions
.Select(d => new Tuple<Message, IEnumerable<ProjectComment>>(d.First().Discussion, d))
.Select(ToFeed);
}
}
public abstract IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter);
