public async Task <IEnumerable <ShowDisplay> > GetRandomAsync(int skip, int take)
{
try
{
var rating = from r in _context.Reviews
group r by r.ShowId into g
select new { ShowId = g.Key, Count = (float)g.Count(), Sum = (float)g.Sum(s => s.Value) };
var result = await(from s in _context.Shows
join r in rating on s.Id equals r.ShowId into ShowGroup
from sg in ShowGroup.DefaultIfEmpty()
select new ShowDisplay
{
Id = s.Id,
Title = s.Title,
Description = s.Description,
CoverImg = s.CoverImg,
ReleaseDate = s.ReleaseDate,
Score = sg == null ? 0 : (sg.Sum / sg.Count)
}).OrderBy(s => Guid.NewGuid()).Skip(skip).Take(take).AsNoTracking().ToListAsync();
return(result);
}
catch
{
return(new List <ShowDisplay>());
}
}
public IActionResult Show(int id)
{
if (!_context.Groups.Any(g => g.ID == id))
{
return(NotFound());
}
ViewData["groupId"] = id;
ShowGroup model = new ShowGroup();
model.Group = _context.Groups.Single(g => g.ID == id);
model.Items = new List <DownloadItem>();
List <DownloadItem> items = _context.Items.Where(i => i.DownloadGroupID == id).ToList();
foreach (DownloadItem item in items)
{
if (item.State == DownloadItem.States.Downloading || item.State == DownloadItem.States.Error)
{
string path = System.IO.Path.Combine(_settings.DownloadFolder, model.Group.Name, "files", item.Name);
long size = System.IO.File.Exists(path) ? new System.IO.FileInfo(path).Length : 0;
int perc = (int)(size * 100 / item.Size);
item.SetPercentage(perc);
}
model.Items.Add(item);
}
model.Items = _context.Items.Where(i => i.DownloadGroupID == id).OrderBy(i => i.Name).ToList();
return(View(model));
}
public ActionResult GetMyGroup(AddGroupFromApp groupInfo)
{
Console.WriteLine("show all of the group i have");
List <ShowGroup> showGroups = new List <ShowGroup>();
using (SqlConnection sqlConnection = new SqlConnection(Global.connect_string))
{
sqlConnection.Open();
//1.先確認Token並把UserId抓下來
//int userId = CheckToken(sqlConnection, groupInfo.Token);
int userId = -1;
//1.先確認Token並把UserId抓下來
string strCheckToken = "SELECT [UserId] FROM [UserInfo] WHERE [Token]=@Token AND [Email]=@ACCOUNT";
using (SqlCommand cmdReadUserId = new SqlCommand(strCheckToken, sqlConnection))
{
cmdReadUserId.Parameters.AddWithValue("@Token", groupInfo.Token);
string[] account = groupInfo.Token.Split("/");
cmdReadUserId.Parameters.AddWithValue("@ACCOUNT", account[0]);
using (SqlDataReader readUserId = cmdReadUserId.ExecuteReader())
{
if (readUserId.HasRows)
{
if (readUserId.Read())
{
userId = (int)readUserId[0];
}
else
{
return(CreatedAtAction("GetMyGroup", new { errorcode = -402, msg = "can't find user" }));
}
}
}
}
if (userId == -1)
{
return(CreatedAtAction("GetMyGroup", new { errorcode = -402, msg = "can't find user" }));
}
Console.WriteLine("UserId" + userId);
//2.使用GroupId查詢List
string getMyGroup = @"
SELECT [GroupId],[GroupName],[GroupIntro],[InvitedCode],[GroupPhoto]
FROM [GroupInfo]
WHERE [GroupId] IN
(SELECT GroupId
FROM [Group_User]
WHERE UserId =@id AND (IsManager=0 OR IsManager=1) AND (Accepted=1 OR Accepted=2))";
using (SqlCommand cmd = new SqlCommand(getMyGroup, sqlConnection))
{
cmd.Parameters.AddWithValue("@id", userId);
using (SqlDataReader readGroup = cmd.ExecuteReader())
{
if (readGroup.HasRows)
{
while (readGroup.Read())
{
ShowGroup newObj = new ShowGroup();
newObj.GroupId = readGroup.GetInt32(0);
newObj.GroupName = readGroup.GetString(1);
newObj.GroupIntro = readGroup.GetString(2);
newObj.InvitedCode = readGroup.GetString(3);
string photoRoute = null;
if (!readGroup.IsDBNull(4))
{
photoRoute = Global.group_photo_url + readGroup.GetString(4);
}
newObj.GroupPhoto = photoRoute;
showGroups.Add(newObj);
}
//Console.WriteLine("3");
//readGroup.NextResult();
//Console.WriteLine("4");
}
else
{
return(CreatedAtAction("GetMyGroup", new { errorcode = -402, msg = "you dont have any group" }));
}
}
}
}
//Console.WriteLine(showGroups.ElementAt<ShowGroup>(0));
//Console.WriteLine(showGroups.ElementAt<ShowGroup>(1));
return(CreatedAtAction("GetMyGroup", new { errorcode = -1, msg = "success get group", MyGroups = showGroups }));
}
public async Task <IEnumerable <ShowDisplay> > GetSearchAsync(int skip, int take, string quote, string tag, string order)
{
var rating = from r in _context.Reviews
group r by r.ShowId into g
select new { ShowId = g.Key, Score = ((float)g.Sum(s => s.Value) / (float)g.Count()) };
var query = from s in _context.Shows
join r in rating on s.Id equals r.ShowId into ShowGroup
from sg in ShowGroup.DefaultIfEmpty()
orderby sg.Score descending
select new ShowDisplay
{
Id = s.Id,
Title = s.Title,
Description = s.Description,
CoverImg = s.CoverImg,
ReleaseDate = s.ReleaseDate,
Score = sg == null ? 0 : sg.Score
};
if (quote != "" && quote != null)
{
query = query.Where(s => s.Title.Contains(quote));
}
if (tag != "" && tag != null)
{
var tags = tag.Split(' ');
var allTags = (from ta in _context.Tags
where tags.Any(t => ta.Name.Replace(" ", "-") == t)
select ta);
var tagsQ = from tc in _context.TagConnections
join at in allTags on tc.TagId equals at.Id
group tc.ShowId by tc.ShowId into g
select new { ShowId = g.Key, Count = g.Count() };
query = from s in query
join t in tagsQ on s.Id equals t.ShowId
where t.Count == tags.Count()
select s;
}
switch (order)
{
case "rdate":
query = query.OrderByDescending(s => s.ReleaseDate);
break;
case "mviews":
var mostv = (from v in _context.Views
group v.ShowId by v.ShowId into g
select new { ShowId = g.Key, Views = g.Count() });
query = from s in query
join t in mostv on s.Id equals t.ShowId into ShowGroup
from sg in ShowGroup.DefaultIfEmpty()
orderby sg.Views descending
select s;
break;
case "popular":
var trending = (from v in _context.Views
where v.ViewDate >= DateTime.Now.AddDays(-31)
group v.ShowId by v.ShowId into g
select new { ShowId = g.Key, Views = g.Count() });
query = from s in query
join t in trending on s.Id equals t.ShowId into ShowGroup
from sg in ShowGroup.DefaultIfEmpty()
orderby sg.Views descending
select s;
break;
case "rating":
break;
case "alph":
query = query.OrderBy(s => s.Title);
break;
default:
var deford = (from v in _context.Views
where v.ViewDate >= DateTime.Now.AddDays(-31)
group v.ShowId by v.ShowId into g
select new { ShowId = g.Key, Views = g.Count() });
query = from s in query
join t in deford on s.Id equals t.ShowId into ShowGroup
from sg in ShowGroup.DefaultIfEmpty()
orderby sg.Views descending
select s;
break;
}
try
{
var result = await query.Skip(skip).Take(take).AsNoTracking().ToListAsync();
return(result);
}
catch
{
var result = new List <ShowDisplay>();
return(result);
}
}
public async Task <NavPage> GetSearchAsync(string quote, string tag, string order, int pageid)
{
int count = 0;
var query = from s in _context.Shows
select s;
if (quote != "" && quote != null)
{
query = query.Where(s => s.Title.Contains(quote));
}
if (tag != "" && tag != null)
{
var tags = tag.Split(' ');
var allTags = (from ta in _context.Tags
where tags.Any(t => ta.Name.Replace(" ", "-") == t)
select ta);
var tagsQ = from tc in _context.TagConnections
join at in allTags on tc.TagId equals at.Id
group tc.ShowId by tc.ShowId into g
select new { ShowId = g.Key, Count = g.Count() };
query = from s in query
join t in tagsQ on s.Id equals t.ShowId
where t.Count == tags.Count()
select s;
}
switch (order)
{
case "rdate":
query.OrderByDescending(s => s.ReleaseDate);
break;
case "mviews":
var mostv = (from v in _context.Views
group v.ShowId by v.ShowId into g
select new { ShowId = g.Key, Views = g.Count() });
query = from s in query
join t in mostv on s.Id equals t.ShowId into ShowGroup
from sg in ShowGroup.DefaultIfEmpty()
orderby sg.Views descending
select s;
break;
case "popular":
var trending = (from v in _context.Views
where v.ViewDate >= DateTime.Now.AddDays(-31)
group v.ShowId by v.ShowId into g
select new { ShowId = g.Key, Views = g.Count() });
query = from s in query
join t in trending on s.Id equals t.ShowId into ShowGroup
from sg in ShowGroup.DefaultIfEmpty()
orderby sg.Views descending
select s;
break;
case "rating":
break;
case "alph":
query.OrderByDescending(s => s.Title);
break;
default:
var deford = (from v in _context.Views
where v.ViewDate >= DateTime.Now.AddDays(-31)
group v.ShowId by v.ShowId into g
select new { ShowId = g.Key, Views = g.Count() });
query = from s in query
join t in deford on s.Id equals t.ShowId into ShowGroup
from sg in ShowGroup.DefaultIfEmpty()
orderby sg.Views descending
select s;
break;
}
try
{
count = await query.AsNoTracking().CountAsync();
}
catch
{
count = 0;
}
return(setValues(pageid, count));
}