public RankingViewModel GetRankingOfBet(int?cycle, int num, string userID, bool isAdmin)
{
RankingViewModel vr = new RankingViewModel();
List <Ranking> rankinglist = new List <Ranking>();
cycle = (cycle.HasValue ? cycle : 1);
DateTime sdate, edate;
sdate = DateTime.Today;
edate = DateTime.Today.AddDays(1);
switch (cycle)
{
case 1:
sdate = DateTime.Today;
edate = DateTime.Today.AddDays(1);
break;
case 2:
sdate = DateTime.Now.AddDays(((int)DateTime.Now.DayOfWeek - 1) * -1);
edate = DateTime.Today.AddDays(1);
break;
case 3:
sdate = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/") + "01");
edate = DateTime.Today.AddDays(1);
break;
case 4:
string fmonth = "";
switch (DateTime.Today.Month)
{
case 1:
case 2:
case 3:
fmonth = "01";
break;
case 4:
case 5:
case 6:
fmonth = "04";
break;
case 7:
case 8:
case 9:
fmonth = "07";
break;
case 10:
case 11:
case 12:
fmonth = "10";
break;
}
sdate = DateTime.Parse(DateTime.Now.ToString("yyyy/") + fmonth + "/01");
edate = DateTime.Today.AddDays(1);
break;
case 10:
Seasons s = new SeasonsRepository().GetNowSeason();
sdate = s.sdate;
edate = s.edate;
break;
}
string sqlquery = @"
select u.UserName, u.Email, u.Name,ars.assets
from (
SELECT top {num} ar.UserId, sum(ar.assets)*-1 as assets
FROM AssetsRecord ar
where ar.type=-1
and ar.inpdate between {sdate} and {edate}
and exists(
select *
from Users
where Id=ar.UserId
and (isnull(LockoutEndDateUtc,'19110101')<GETUTCDATE() or LockoutEnabled=0)
)
group by ar.UserId
having sum(ar.assets)>0
order by sum(ar.assets)*-1 desc
) as ars
join Users u on u.Id=ars.UserId
";
using (IDbConnection cn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SQLData"].ConnectionString.ToString()))
{
sqlquery = sqlquery.Replace("{sdate}", "'" + sdate.ToString("yyyyMMdd") + "'");
sqlquery = sqlquery.Replace("{edate}", "'" + edate.ToString("yyyyMMdd") + "'");
sqlquery = sqlquery.Replace("{num}", num.ToString());
sqlquery = sqlquery.Replace("{userID}", "'" + userID + "'");
using (var multi = cn.QueryMultiple(sqlquery))
{
rankinglist = multi.Read <Ranking>().ToList();
}
}
foreach (var r in rankinglist)
{
if (!r.ID.Equals(userID) && !isAdmin)
{
r.UserName = r.UserName.Substring(0, 3) + "*****";
}
}
vr.rankinglist = rankinglist;
return(vr);
}
public RankingViewModel GetRankingOfWin(int?cycle, int num, string userID, bool isAdmin)
{
int maxRanking = 500;
RankingViewModel vr = new RankingViewModel();
List <Ranking> rankinglist = new List <Ranking>();
cycle = (cycle.HasValue ? cycle : 1);
DateTime sdate, edate;
sdate = DateTime.Today;
edate = DateTime.Today.AddDays(1);
switch (cycle)
{
case 1:
sdate = DateTime.Today;
edate = DateTime.Today.AddDays(1);
break;
case 2:
sdate = DateTime.Now.AddDays(((int)DateTime.Now.DayOfWeek - 1) * -1);
edate = DateTime.Today.AddDays(1);
break;
case 3:
sdate = DateTime.Parse(DateTime.Now.ToString("yyyy/MM/") + "01");
edate = DateTime.Today.AddDays(1);
break;
case 4:
string fmonth = "";
switch (DateTime.Today.Month)
{
case 1:
case 2:
case 3:
fmonth = "01";
break;
case 4:
case 5:
case 6:
fmonth = "04";
break;
case 7:
case 8:
case 9:
fmonth = "07";
break;
case 10:
case 11:
case 12:
fmonth = "10";
break;
}
sdate = DateTime.Parse(DateTime.Now.ToString("yyyy/") + fmonth + "/01");
edate = DateTime.Today.AddDays(1);
break;
case 10:
Seasons s = new SeasonsRepository().GetNowSeason();
sdate = s.sdate;
edate = s.edate;
break;
}
//舊
string sqlquery = @"
select rank() over(order by ars.assets desc) ord,u.Id, u.UserName, u.Email, u.Name,ars.assets
from (
SELECT top {num} ar.UserId, sum(ar.assets) as assets
FROM AssetsRecord ar
where ar.type in (1,-4)
and ar.UserId <>{userID}
and ar.inpdate between {sdate} and {edate}
and exists(
select *
from Users
where Id=ar.UserId
and (isnull(LockoutEndDateUtc,'19110101')<GETUTCDATE() or LockoutEnabled=0)
)
group by ar.UserId
having sum(ar.assets)>0
order by sum(ar.assets) desc
union
SELECT u.Id as UserId, isnull(sum(ar.assets),0) as assets
FROM Users u
left join AssetsRecord ar on u.Id=ar.UserId
and ar.type in (1,-4)
and ar.inpdate between {sdate} and {edate}
where u.Id={userID}
and (isnull(LockoutEndDateUtc,'19110101')<GETUTCDATE() or LockoutEnabled=0)
group by u.Id
) as ars
join Users u on u.Id=ars.UserId
order by ars.assets desc
";
/* string sqlquery = @"
* select rank() over(order by ars.assets desc) ord,u.Id, u.UserName, u.Email, u.Name,ars.assets
* from (
* SELECT top {num} ar.UserId, sum(ar.assets) as assets
* FROM AssetsRecord ar
* where ar.type = 1
* and ar.UserId <>{userID}
* and ar.inpdate between {sdate} and {edate}
* and exists(
* select *
* from Users
* where Id=ar.UserId
* and (isnull(LockoutEndDateUtc,'19110101')<GETUTCDATE() or LockoutEnabled=0)
* )
* group by ar.UserId
* having sum(ar.assets)>0
* order by sum(ar.assets) desc
* union
* SELECT u.Id as UserId, isnull(sum(ar.assets),0) as assets
* FROM Users u
* left join AssetsRecord ar on u.Id=ar.UserId
* and ar.type in (1,-4)
* and ar.inpdate between {sdate} and {edate}
* where u.Id={userID}
* and (isnull(LockoutEndDateUtc,'19110101')<GETUTCDATE() or LockoutEnabled=0)
* group by u.Id
* ) as ars
* join Users u on u.Id=ars.UserId
* order by ars.assets desc
* ";*/
vr.rankinglist = new List <Ranking>();
using (IDbConnection cn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SQLData"].ConnectionString.ToString()))
{
sqlquery = sqlquery.Replace("{sdate}", "'" + sdate.ToString("yyyy-MM-dd HH:mm:ss") + "'");
sqlquery = sqlquery.Replace("{edate}", "'" + edate.ToString("yyyy-MM-dd HH:mm:ss") + "'");
sqlquery = sqlquery.Replace("{num}", maxRanking.ToString());
sqlquery = sqlquery.Replace("{userID}", "'" + userID + "'");
using (var multi = cn.QueryMultiple(sqlquery))
{
rankinglist = multi.Read <Ranking>().ToList();
}
int userBeforOrd = 0;
Ranking rk = rankinglist.Where(p => p.ID == userID).FirstOrDefault();
if (rk != null)
{
userBeforOrd = int.Parse(rk.ord);
userBeforOrd = (int)Math.Round(((double)userBeforOrd - 1) / 10) * 10;
}
//隱藏username
foreach (var r in rankinglist)
{
if (int.Parse(r.ord) <= num || r.ID == userID || int.Parse(r.ord) == userBeforOrd)
{
if (!r.ID.Equals(userID) && !isAdmin)
{
int subl = (r.UserName.Length / 2);
subl = (subl >= 3 ? 3 : subl);
r.UserName = r.UserName.Substring(0, subl) + "*****";
r.isUser = false;
}
else
{
if (r.ID.Equals(userID))
{
r.isUser = true;
}
}
int iord;
if (int.TryParse(r.ord, out iord) && iord > maxRanking)
{
r.ord = maxRanking + "+";
}
vr.rankinglist.Add(r);
}
}
}
vr.sdate = sdate;
vr.edate = edate;
return(vr);
}
