Пример #1
0
        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]);
        }
Пример #2
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);
            }
        }
Пример #3
0
        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]);
        }
Пример #4
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);
        }
Пример #5
0
        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));
        }
Пример #6
0
        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);
        }
Пример #7
0
        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);
        }
Пример #8
0
        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);
            }
        }
Пример #9
0
        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);
        }
Пример #10
0
        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));
        }
Пример #11
0
        //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]);
        }
Пример #12
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);
        }
Пример #13
0
        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]);
        }
Пример #14
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);
        }
Пример #15
0
        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);
        }
Пример #16
0
        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);
        }
Пример #17
0
        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);
        }
Пример #18
0
        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);
        }
Пример #19
0
        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();
            }
        }
Пример #20
0
        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));
        }
Пример #21
0
        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);
        }
Пример #22
0
        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);
        }
Пример #23
0
        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);
        }
Пример #24
0
        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();
                }
            }
        }
Пример #25
0
        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]);
        }
Пример #26
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);
                }
            }
        }
Пример #27
0
        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);
            }
        }
Пример #28
0
        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);
        }
Пример #29
0
        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);
        }
Пример #30
0
        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]);
        }