public static DataTable GetEndMatchView(Guid leagueGuid, Guid groupGuid, bool isTable)
{
string sql;
if (!isTable)
{
sql = @"SELECT match.* FROM
(SELECT DISTINCT MatchGuid FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid IS NOT NULL) item
INNER JOIN acncasino_match match ON match.MatchGuid = item.MatchGuid
WHERE match.LeagueGuid = @leagueGuid AND match.GroupGuid = @groupGuid AND match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL
ORDER BY match.PlayTime DESC";
}
else
{
sql = @"SELECT match.* FROM
(SELECT DISTINCT MatchGuid FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid IS NOT NULL) item
INNER JOIN acncasino_match match ON match.MatchGuid = item.MatchGuid
WHERE match.LeagueGuid = @leagueGuid AND match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL AND
(match.home IN (SELECT TeamGuid FROM dbo.Arsenal_RelationGroupTeam AS GroupTeam1 WHERE GroupGuid = @groupGuid)) AND
(match.away IN (SELECT TeamGuid FROM dbo.Arsenal_RelationGroupTeam AS GroupTeam2 WHERE GroupGuid = @groupGuid))
ORDER BY match.PlayTime DESC";
}
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@leagueGuid", leagueGuid), new SqlParameter("@groupGuid", groupGuid));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static void UpdateUserVideo(int uvID, int userID, string userName, Guid videoGuid, DateTime activeDate,
string userDesc, bool isPublic, SqlTransaction trans = null)
{
var sql =
@"UPDATE dbo.AcnClub_RelationUserVideo SET UserID = @userID, UserName = @userName, VideoGuid = @videoGuid,
ActiveDate = @activeDate, UserDesc = @userDesc, IsPublic = @isPublic WHERE ID = @uvID";
SqlParameter[] para =
{
new SqlParameter("@uvID", uvID),
new SqlParameter("@userID", userID),
new SqlParameter("@userName", userName),
new SqlParameter("@videoGuid", videoGuid),
new SqlParameter("@activeDate", activeDate),
new SqlParameter("@userDesc", userDesc),
new SqlParameter("@isPublic", isPublic)
};
if (trans == null)
{
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
else
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, para);
}
}
public static DataTable GetGamblerProfitView(Guid leagueGuid)
{
var sql = @"SELECT BetInfo.*, RPInfo.RPBet, RPInfo.RPBonus FROM
(SELECT UserID, UserName,
COUNT(CASE IsWin WHEN 1 THEN 1 ELSE NULL END) AS Win,
COUNT(CASE IsWin WHEN 0 THEN 0 ELSE NULL END) AS Lose,
COUNT(distinct CAST(CasinoItemGuid AS CHAR(50))) AS MatchBet,
SUM(ISNULL(Earning, 0)) AS Earning,
SUM(ISNULL(Bet, 0)) AS TotalBet
FROM dbo.vw_AcnCasino_BetInfo
WHERE (Earning IS NOT NULL) AND (Bet IS NOT NULL) AND (ItemType = 2) AND (LeagueGuid = @leagueGuid)
GROUP BY UserID, UserName) AS BetInfo
LEFT OUTER JOIN
(SELECT UserID, UserName,
COUNT(ID) AS RPBet,
COUNT(CASE EarningDesc WHEN 'RP+1' THEN 1 ELSE NULL END) AS RPBonus
FROM dbo.vw_AcnCasino_BetInfo
WHERE (Earning = 0) AND (Bet IS NULL) AND (ItemType = 1) AND (LeagueGuid = @leagueGuid)
GROUP BY UserID, UserName) AS RPInfo
ON BetInfo.UserID = RPInfo.UserID AND BetInfo.UserName = RPInfo.UserName
ORDER BY BetInfo.TotalBet DESC";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@leagueGuid", leagueGuid));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static List <Gamer> GetClubPlayers(int clubID)
{
var list = new List <Gamer>();
var sql =
"SELECT p.* FROM AcnClub_Player p INNER JOIN AcnClub_RelationUserClub uc ON p.UserID = uc.UserID WHERE uc.ClubUID = @clubID AND uc.IsActive = 1 ORDER BY Shirt+Shorts+Sock DESC";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
com.Parameters.Add(new SqlParameter("@clubID", clubID));
var sda = new SqlDataAdapter(com);
con.Open();
var dt = new DataTable();
sda.Fill(dt);
//con.Close();
foreach (DataRow dr in dt.Rows)
{
var p = new Gamer(dr);
list.Add(p);
}
}
return(list);
}
public static int InsertBet(int userid, string username, Guid casinoItemGuid, float?bet, float?betRate,
SqlTransaction trans)
{
var sql =
"INSERT INTO AcnCasino_Bet VALUES (@userid, @username, @casinoItemGuid, @bet, GETDATE(), @betRate, NULL, NULL, NULL); SELECT SCOPE_IDENTITY();";
SqlParameter[] para =
{
new SqlParameter("@userid", userid), new SqlParameter("@username", username),
new SqlParameter("@casinoItemGuid", casinoItemGuid), new SqlParameter("@bet", bet),
new SqlParameter("@betRate", betRate)
};
object obj;
if (trans != null)
{
obj = SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, para);
}
else
{
obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
return(Convert.ToInt32(obj));
}
public static List <BingoHistory> GetClubBingoHistory(int clubID)
{
var list = new List <BingoHistory>();
var sql =
"SELECT * FROM AcnClub_LogBingo WHERE ClubID = @clubID AND Result IS NOT NULL ORDER BY ActionDate DESC";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
com.Parameters.Add(new SqlParameter("@clubID", clubID));
var sda = new SqlDataAdapter(com);
con.Open();
var dt = new DataTable();
sda.Fill(dt);
//con.Close();
foreach (DataRow dr in dt.Rows)
{
var bh = new BingoHistory(dr);
list.Add(bh);
}
}
return(list);
}
public static int GetClubRemainingEquipment(int clubID)
{
var sql =
"SELECT COUNT(*) FROM AcnClub_LogBingo WHERE ClubID = @clubID AND RESULT IS NOT NULL AND ActionDate BETWEEN @fromDate AND @toDate";
var count = 0;
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
com.Parameters.Add(new SqlParameter("@clubID", clubID));
com.Parameters.Add(new SqlParameter("@fromDate", DateTime.Today));
com.Parameters.Add(new SqlParameter("@toDate", DateTime.Today.AddDays(1)));
con.Open();
count = (int)com.ExecuteScalar();
//con.Close();
}
if (ConfigGlobal.DailyClubEquipmentCount - count < 0)
{
return(0);
}
return(ConfigGlobal.DailyClubEquipmentCount - count);
}
public static void SetBonusGot(int playerID, int bonusToClub, int clubID, int luckyPlayerID)
{
var sql = "UPDATE dbo.Arsenalcn_Config SET ConfigValue = 'true' WHERE ConfigKey = 'LuckyPlayerBonusGot'; ";
sql = sql +
"UPDATE dbo.AcnClub_LogLuckyPlayer SET BonusGot = 1 WHERE [ID] = (SELECT TOP 1 [ID] FROM AcnClub_LogLuckyPlayer ORDER BY [Date] DESC); ";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
con.Open();
com.ExecuteNonQuery();
//con.Close();
}
var player = PlayerStrip.GetPlayerInfoByPlayerID(playerID);
var lPlayer = PlayerStrip.GetPlayerInfoByPlayerID(luckyPlayerID);
if (player != null && clubID > 0)
{
var ch = new ClubHistory();
ch.ClubID = clubID;
ch.ActionUserName = lPlayer.UserName;
ch.OperatorUserName = player.UserName;
ch.ActionType = ClubHistoryActionType.LuckyPlayer.ToString();
ch.ActionDescription = ClubLogic.BuildClubHistoryActionDesc(ClubHistoryActionType.LuckyPlayer,
bonusToClub.ToString(), lPlayer.UserName);
ClubLogic.SaveClubHistory(ch);
}
}
public static void InsertClub(Club club)
{
var sql =
@"INSERT INTO dbo.AcnClub_Club VALUES (@fullName, @shortName, @rankLevel, @rankScore, @logo, @slogan, @description, @creatorUid,
@creatorUserName, @managerUid, @managerUserName, @createDate, @updateDate, @isActive,
@isAppliable, @fortune, @memberCredit, @memberFortune, @memberRP, @memberLoyalty)" ;
var para = new SqlParameter[21];
para[0] = new SqlParameter("@clubID", ParseNullableParam(club.ID));
para[1] = new SqlParameter("@fullName", club.FullName);
para[2] = new SqlParameter("@shortName", club.ShortName);
para[3] = new SqlParameter("@rankLevel", club.RankLevel);
para[4] = new SqlParameter("@rankScore", club.RankScore);
para[5] = new SqlParameter("@logo", ParseNullableParam(club.LogoName));
para[6] = new SqlParameter("@slogan", club.Slogan);
para[7] = new SqlParameter("@description", ParseNullableParam(club.Description));
para[8] = new SqlParameter("@creatorUid", club.CreatorUid);
para[9] = new SqlParameter("@creatorUserName", club.CreatorUserName);
para[10] = new SqlParameter("@managerUid", club.ManagerUid);
para[11] = new SqlParameter("@managerUserName", club.ManagerUserName);
para[12] = new SqlParameter("@createDate", club.CreateDate);
para[13] = new SqlParameter("@updateDate", club.UpdateDate);
para[14] = new SqlParameter("@isActive", ParseNullableParam(club.IsActive));
para[15] = new SqlParameter("@isAppliable", ParseNullableParam(club.IsAppliable));
para[16] = new SqlParameter("@fortune", ParseNullableParam(club.Fortune));
para[17] = new SqlParameter("@memberCredit", ParseNullableParam(club.MemberCredit));
para[18] = new SqlParameter("@memberFortune", ParseNullableParam(club.MemberFortune));
para[19] = new SqlParameter("@memberRP", ParseNullableParam(club.MemberRP));
para[20] = new SqlParameter("@memberLoyalty", ParseNullableParam(club.MemberLoyalty));
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
public static void UpdateCasinoItemCloseTime(Guid matchGuid, DateTime closeTime)
{
var sql = "UPDATE AcnCasino_CasinoItem SET CloseTime = @closeTime WHERE MatchGuid = @guid";
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@closeTime", closeTime), new SqlParameter("@guid", matchGuid));
}
//public static DataTable GetRelationGroupTeamByGroupGuid(Guid groupGuid)
//{
// var sql =
// "SELECT * FROM dbo.Arsenal_RelationGroupTeam WHERE GroupGuid = @groupGuid ORDER BY TotalPoints DESC, ISNULL(HomeGoalDiff, 0) + ISNULL(AwayGoalDiff, 0) DESC, ISNULL(HomeGoalFor, 0) + ISNULL(AwayGoalFor, 0) DESC";
// var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
// new SqlParameter("@groupGuid", groupGuid));
// if (ds.Tables[0].Rows.Count == 0)
// return null;
// return ds.Tables[0];
//}
public static DataTable GetTableGroupTeamByGroupGuid(Guid groupGuid)
{
var sql =
@"SELECT GroupTeam.GroupGuid, GroupTeam.TeamGuid, GroupTeam.PositionNo, GroupTeam.TotalPlayed, GroupTeam.HomeWon,
GroupTeam.HomeDraw, GroupTeam.HomeLost, GroupTeam.HomeGoalFor, GroupTeam.HomeGoalAgainst, GroupTeam.HomeGoalDiff,
GroupTeam.HomePoints, GroupTeam.AwayWon, GroupTeam.AwayDraw, GroupTeam.AwayLost, GroupTeam.AwayGoalFor,
GroupTeam.AwayGoalAgainst, GroupTeam.AwayGoalDiff, GroupTeam.AwayPoints, GroupTeam.TotalPoints,
ISNULL(GroupTeam.HomeWon, 0) + ISNULL(GroupTeam.AwayWon, 0) AS TotalWon,
ISNULL(GroupTeam.HomeDraw, 0) + ISNULL(GroupTeam.AwayDraw, 0) AS TotalDraw,
ISNULL(GroupTeam.HomeLost, 0) + ISNULL(GroupTeam.AwayLost, 0) AS TotalLost,
ISNULL(GroupTeam.HomeGoalFor, 0) + ISNULL(GroupTeam.AwayGoalFor, 0) AS TotalGoalFor,
ISNULL(GroupTeam.HomeGoalAgainst, 0) + ISNULL(GroupTeam.AwayGoalAgainst, 0) AS TotalGoalAgainst,
ISNULL(GroupTeam.HomeGoalDiff, 0) + ISNULL(GroupTeam.AwayGoalDiff, 0) AS TotalGoalDiff
FROM dbo.Arsenal_RelationGroupTeam AS GroupTeam
WHERE GroupTeam.GroupGuid = @groupGuid ORDER BY GroupTeam.PositionNo";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@groupGuid", groupGuid));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static DataTable GetTopMatchLoss(out int months)
{
var iDay = DateTime.Today;
months = 0;
do
{
var monthStart = iDay.AddDays(1 - iDay.Day);
var nextStart = iDay.AddMonths(1);
var sql = $@"SELECT TOP 5 dbo.AcnCasino_CasinoItem.MatchGuid, dbo.AcnCasino_Match.PlayTime,
dbo.AcnCasino_CasinoItem.Earning, dbo.AcnCasino_Match.Round
FROM dbo.AcnCasino_CasinoItem INNER JOIN dbo.AcnCasino_Match ON dbo.AcnCasino_CasinoItem.MatchGuid = dbo.AcnCasino_Match.MatchGuid
WHERE (dbo.AcnCasino_CasinoItem.Earning IS NOT NULL) AND (dbo.AcnCasino_CasinoItem.ItemType = 2)
AND (dbo.AcnCasino_Match.PlayTime >= '{monthStart}') AND (dbo.AcnCasino_Match.PlayTime < '{nextStart}')
ORDER BY dbo.AcnCasino_CasinoItem.Earning";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count > 0 || months < -12)
{
return(ds.Tables[0]);
}
months--;
iDay = monthStart.AddMonths(-1);
} while (true);
}
public static DataTable GetEndMatchViewByTeamGuid(Guid teamGuid)
{
// string sql = @"SELECT match.*, teamH.TeamDisplayName as HomeDisplay, teamH.TeamEnglishName as HomeEng, teamH.TeamLogo as HomeLogo, teamA.TeamLogo as AwayLogo,
// teamH.Ground as Ground, teamH.Capacity as Capacity, teamA.TeamDisplayName as AwayDisplay, teamA.TeamEnglishName as AwayEng,
// league.LeagueLogo AS LeagueLogo, league.LeagueName AS LeagueName, league.LeagueSeason AS LeagueSeason FROM
// (select distinct MatchGuid from dbo.AcnCasino_CasinoItem where MatchGuid IS NOT NULL) item
// INNER JOIN acncasino_match match
// ON match.MatchGuid = item.MatchGuid
// INNER JOIN arsenal_team teamH
// ON match.home = teamH.TeamGuid
// INNER JOIN arsenal_team teamA
// ON match.away = teamA.TeamGuid
// INNER JOIN arsenal_league league
// ON match.LeagueGuid = league.LeagueGuid
// WHERE (match.Home = @teamGuid OR match.Away = @teamGuid) AND
// match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL
// ORDER BY match.PlayTime DESC";
var sql = @"SELECT match.* FROM
(SELECT DISTINCT MatchGuid FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid IS NOT NULL) item
INNER JOIN acncasino_match match ON match.MatchGuid = item.MatchGuid
WHERE (match.Home = @teamGuid OR match.Away = @teamGuid) AND
match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL
ORDER BY match.PlayTime DESC";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@teamGuid", teamGuid));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static Guid InsertCasinoItem(int itemType, Guid?matchGuid, string itemTitle, string itemBody,
DateTime publishTime, DateTime closeTime, Guid bankerID, string bankerName, int ownerID, string ownerName,
SqlTransaction trans)
{
var casinoItemGuid = Guid.NewGuid();
var sql =
"INSERT INTO dbo.AcnCasino_CasinoItem VALUES (@itemGuid, @itemType, @matchGuid, @itemTitle, @itemBody, getdate(), @publishTime, @closeTime, @bankerID, @bankerName, null, @ownerID, @ownerName)";
SqlParameter[] para =
{
new SqlParameter("@itemGuid", casinoItemGuid),
new SqlParameter("@itemType", itemType), new SqlParameter("@matchGuid", matchGuid),
new SqlParameter("@itemTitle", itemTitle), new SqlParameter("@itemBody", itemBody),
new SqlParameter("@publishTime", publishTime), new SqlParameter("@closeTime", closeTime),
new SqlParameter("@bankerID", bankerID), new SqlParameter("@bankerName", bankerName),
new SqlParameter("ownerID", ownerID), new SqlParameter("ownerName", ownerName)
};
if (trans != null)
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, para);
}
else
{
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
return(casinoItemGuid);
}
public static DataTable GetTopVideoPlayers()
{
var sql =
@"select top 5 ISNULL(A.username, B.username) as username, ISNULL(A.userid, B.userid) as userid, ISNULL(inactiveVideo, 0) + ISNULL(videoCount, 0) as videoCount from
(select username, userid, count(*) as inactiveVideo from AcnClub_Card where ArsenalPlayerGuid is null group by username, userid) A
full outer join
(select username, userid, count(*) as videoCount from acnclub_relationuservideo group by username, userid) B
ON A.username = B.username AND A.userid = B.userid order by videoCount desc";
var players = new DataTable();
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
var sda = new SqlDataAdapter(com);
con.Open();
sda.Fill(players);
//con.Close();
}
return(players);
}
public static List <Club> GetUserManagedClubs(int userID)
{
var sql = @"SELECT DISTINCT c.*, uc.Responsibility AS additional FROM dbo.AcnClub_RelationUserClub uc
INNER JOIN dbo.AcnClub_Club c ON uc.ClubUid = c.ClubUid
WHERE c.IsActive = 1 AND uc.UserID = @userId AND uc.IsActive = 1 AND Responsibility <20";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@userID", userID));
if (ds.Tables[0].Rows.Count == 0)
{
return(new List <Club>());
}
var list = new List <Club>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var club = new Club(dr);
if (club.AdditionalData != null && club.AdditionalData != DBNull.Value)
{
club.AdditionalData = TranslateResponsibility((int)club.AdditionalData);
}
else
{
club.AdditionalData = TranslateResponsibility(Responsibility.Manager);
}
list.Add(club);
}
return(list);
}
public static List <Card> GetMyCards(int userId)
{
var list = new List <Card>();
var sql = "SELECT * FROM dbo.AcnClub_Card WHERE UserID = @userID ORDER BY GainDate DESC";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
com.Parameters.Add(new SqlParameter("@userID", userId));
var sda = new SqlDataAdapter(com);
con.Open();
var dt = new DataTable();
sda.Fill(dt);
//con.Close();
foreach (DataRow dr in dt.Rows)
{
var un = new Card(dr);
list.Add(un);
}
}
return(list);
}
public static void SaveClub(Club club)
{
var sql =
@"UPDATE dbo.AcnClub_Club SET FullName = @fullName, ShortName = @shortName, RankLevel = @rankLevel, RankScore = @rankScore,
Logo = @logo, Slogan = @slogan, Description = @description, CreatorUid = @creatorUid, CreatorUserName = @creatorUserName,
ManagerUid = @managerUid, ManagerUserName = @managerUserName, CreateDate = @createDate, UpdateDate = @updateDate,
IsActive = @isActive, IsAppliable = @isAppliable, Fortune = @fortune, MemberCredit = @memberCredit, MemberFortune = @memberFortune,
MemberRP = @memberRP, MemberLoyalty = @memberLoyalty WHERE ClubUid = @clubID" ;
var para = new SqlParameter[21];
para[0] = new SqlParameter("@clubID", ParseNullableParam(club.ID));
para[1] = new SqlParameter("@fullName", club.FullName);
para[2] = new SqlParameter("@shortName", club.ShortName);
para[3] = new SqlParameter("@rankLevel", club.RankLevel);
para[4] = new SqlParameter("@rankScore", club.RankScore);
para[5] = new SqlParameter("@logo", ParseNullableParam(club.LogoName));
para[6] = new SqlParameter("@slogan", club.Slogan);
para[7] = new SqlParameter("@description", ParseNullableParam(club.Description));
para[8] = new SqlParameter("@creatorUid", club.CreatorUid);
para[9] = new SqlParameter("@creatorUserName", club.CreatorUserName);
para[10] = new SqlParameter("@managerUid", club.ManagerUid);
para[11] = new SqlParameter("@managerUserName", club.ManagerUserName);
para[12] = new SqlParameter("@createDate", club.CreateDate);
para[13] = new SqlParameter("@updateDate", club.UpdateDate);
para[14] = new SqlParameter("@isActive", ParseNullableParam(club.IsActive));
para[15] = new SqlParameter("@isAppliable", ParseNullableParam(club.IsAppliable));
para[16] = new SqlParameter("@fortune", ParseNullableParam(club.Fortune));
para[17] = new SqlParameter("@memberCredit", ParseNullableParam(club.MemberCredit));
para[18] = new SqlParameter("@memberFortune", ParseNullableParam(club.MemberFortune));
para[19] = new SqlParameter("@memberRP", ParseNullableParam(club.MemberRP));
para[20] = new SqlParameter("@memberLoyalty", ParseNullableParam(club.MemberLoyalty));
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
public static void UpdateBingoResultLog(int userID, BingoResult result)
{
var sql =
"UPDATE AcnClub_LogBingo SET Result = @result, ResultDetail = @resultDetail WHERE [ID] = (SELECT TOP 1 [ID] FROM AcnClub_LogBingo WHERE UserID = @userID AND Result is NULL ORDER BY ActionDate DESC)";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
object tempValue = null;
com.Parameters.Add(new SqlParameter("@userID", userID));
if (result.Result == BingoResultType.Null)
{
tempValue = DBNull.Value;
}
else
{
tempValue = (int)result.Result;
}
com.Parameters.Add(new SqlParameter("@result", tempValue));
com.Parameters.Add(new SqlParameter("@resultDetail", ClubLogic.ParseNullableParam(result.ResultDetail)));
con.Open();
com.ExecuteNonQuery();
//con.Close();
}
}
public static void DeleteMatch(Guid matchGuid)
{
var sql = "DELETE FROM dbo.AcnCasino_Match WHERE MatchGuid = @guid";
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@guid", matchGuid));
}
public static List <Gamer> GetPlayers()
{
var list = new List <Gamer>();
var sql = "SELECT * FROM AcnClub_Player ORDER BY ID DESC";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
//com.Parameters.Add(new SqlParameter("@clubID", clubID));
var sda = new SqlDataAdapter(com);
con.Open();
var dt = new DataTable();
sda.Fill(dt);
//con.Close();
foreach (DataRow dr in dt.Rows)
{
var bh = new Gamer(dr);
list.Add(bh);
}
}
return(list);
}
public static Card GetUserNumber(int id)
{
Card userNumber = null;
var sql = "SELECT * FROM dbo.AcnClub_Card WHERE [ID] = @id";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
com.Parameters.Add(new SqlParameter("@id", id));
var sda = new SqlDataAdapter(com);
con.Open();
var dt = new DataTable();
sda.Fill(dt);
//con.Close();
if (dt.Rows.Count != 0)
{
userNumber = new Card(dt.Rows[0]);
}
}
return(userNumber);
}
public static Gamer GetPlayerInfoByPlayerID(int playerID)
{
Gamer player = null;
var sql = "SELECT * FROM dbo.AcnClub_Player WHERE [ID] = @playerID";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
com.Parameters.Add(new SqlParameter("@playerID", playerID));
var sda = new SqlDataAdapter(com);
con.Open();
var dt = new DataTable();
sda.Fill(dt);
//con.Close();
if (dt.Rows.Count != 0)
{
player = new Gamer(dt.Rows[0]);
}
}
return(player);
}
public static void RemovePlayerCurrentNum(int userID, int userNumID)
{
var un = GetUserNumber(userNumID);
if (un.ArsenalPlayerGuid.HasValue)
{
var sql =
"UPDATE dbo.AcnClub_Card SET IsInUse = 0 WHERE UserID = @userID; UPDATE dbo.AcnClub_Player SET CurrentGuid = NULL WHERE UserID = @userID AND CurrentGuid = @currentGuid";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
com.Parameters.Add(new SqlParameter("@userID", userID));
com.Parameters.Add(new SqlParameter("@userNumID", userNumID));
com.Parameters.Add(new SqlParameter("@currentGuid", un.ArsenalPlayerGuid));
con.Open();
com.ExecuteNonQuery();
//con.Close();
}
}
}
public static DataTable GetUserBetHistoryView(int userid)
{
// string sql = @"SELECT Bet.*, teamH.TeamDisplayName as HomeDisplay, teamA.TeamDisplayName as AwayDisplay,
// teamH.Ground, teamH.Capacity, match.*
// FROM dbo.AcnCasino_Bet bet
// INNER JOIN dbo.AcnCasino_CasinoItem item
// ON bet.CasinoItemGuid = item.CasinoItemGuid
// INNER JOIN dbo.AcnCasino_Match match
// ON match.MatchGuid = item.MatchGuid
// INNER JOIN arsenal_team teamH
// ON match.home = teamH.TeamGuid
// INNER JOIN arsenal_team teamA
// ON match.away = teamA.TeamGuid
// WHERE UserID = @userid AND item.MatchGuid IS NOT NULL
// ORDER BY BetTime desc";
var sql = @"SELECT bet.*, match.*, item.*
FROM dbo.AcnCasino_Bet bet
INNER JOIN dbo.AcnCasino_CasinoItem item
ON bet.CasinoItemGuid = item.CasinoItemGuid
INNER JOIN dbo.AcnCasino_Match match
ON match.MatchGuid = item.MatchGuid
WHERE UserID = @userid AND item.MatchGuid IS NOT NULL
ORDER BY BetTime desc";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@userid", userid));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static bool ValidateBingoResult(int bingoHistoryID, int userID)
{
var sql =
"SELECT TOP 1 [ID] FROM AcnClub_LogBingo WHERE UserID = @userID AND Result is NULL ORDER BY ActionDate DESC";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
com.Parameters.Add(new SqlParameter("@userID", userID));
con.Open();
try
{
var id = (int)com.ExecuteScalar();
//con.Close();
return(id == bingoHistoryID);
}
catch
{
return(false);
}
}
}
public static void InsertUserVideo(int uvID, int userID, string userName, Guid videoGuid, DateTime activeDate,
string userDesc, bool isPublic, SqlTransaction trans = null)
{
var sql =
@"INSERT INTO dbo.AcnClub_RelationUserVideo (UserID, UserName, VideoGuid, ActiveDate, UserDesc, IsPublic)
VALUES (@userID, @userName, @videoGuid, @activeDate, @userDesc, @isPublic)";
SqlParameter[] para =
{
new SqlParameter(),
new SqlParameter("@userID", userID),
new SqlParameter("@userName", userName),
new SqlParameter("@videoGuid", videoGuid),
new SqlParameter("@activeDate",activeDate),
new SqlParameter("@userDesc", userDesc),
new SqlParameter("@isPublic", isPublic)
};
if (trans == null)
{
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
else
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, para);
}
}
public static DataTable GetTopRpPlayers()
{
var sql = @"select top 5 A.username, A.userid, countGot * 100 / countTotal as rp, countGot, countTotal from
(select username, userid, count(*) as countTotal from AcnClub_LogBingo group by username, userid) A
inner join
(select username, userid, count(*) as countGot from AcnClub_LogBingo where result is not null group by username, userid) B
ON A.username = B.username AND A.userid = B.userid
order by CountGot desc";
var players = new DataTable();
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
var sda = new SqlDataAdapter(com);
con.Open();
sda.Fill(players);
//con.Close();
}
return(players);
}
public static List <GamerHistory> GetUserPlayerHistory(int userID)
{
var list = new List <GamerHistory>();
var sql = "SELECT * FROM dbo.AcnClub_LogPlayer WHERE UserID = @userID ORDER BY ActionDate DESC";
using (var con = SQLConn.GetConnection())
{
var com = new SqlCommand(sql, con);
com.Parameters.Add(new SqlParameter("@userID", userID));
var sda = new SqlDataAdapter(com);
con.Open();
var dt = new DataTable();
sda.Fill(dt);
//con.Close();
foreach (DataRow dr in dt.Rows)
{
var ph = new GamerHistory(dr);
list.Add(ph);
}
}
return(list);
}
public static DataTable GetEndMatchView()
{
// string sql = @"SELECT match.*, teamH.TeamDisplayName as HomeDisplay, teamH.TeamEnglishName as HomeEng, teamH.TeamLogo as HomeLogo,
// teamH.Capacity as Capacity, teamH.Ground as Ground, teamA.TeamLogo as AwayLogo, teamA.TeamDisplayName as AwayDisplay, teamA.TeamEnglishName as AwayEng
// FROM (SELECT DISTINCT MatchGuid FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid IS NOT NULL) item
// INNER JOIN acncasino_match match
// ON match.MatchGuid = item.MatchGuid
// INNER JOIN arsenal_team teamH
// ON match.home = teamH.TeamGuid
// INNER JOIN arsenal_team teamA
// ON match.away = teamA.TeamGuid
// INNER JOIN arsenal_league league
// ON match.LeagueGuid = league.LeagueGuid
// WHERE match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL
// ORDER BY match.PlayTime DESC";
var sql = @"SELECT match.* FROM
(SELECT DISTINCT MatchGuid FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid IS NOT NULL) item
INNER JOIN acncasino_match match ON match.MatchGuid = item.MatchGuid
WHERE match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL
ORDER BY match.PlayTime DESC";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}