Beispiel #1
0
        /// <summary>
        /// Danh sách top user thắng nhiều (top) theo từng Game Id
        /// </summary>
        /// <param name="zoneId"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public List <UserInfo> GetTopUserByZoneID(int zoneId, int top)
        {
            var rs  = new List <UserInfo>();
            var sql = "select b.id,b.username, b.fullname,sum(a.cash) as total_cash_win from newDB.game_history a inner join newDB.view_user_info b on a.user_id = b.id where a.cash > 0 AND (trans_type=1 or trans_type=18) AND game_id = @zoneId" +
                      " group by a.user_id order by total_cash_win desc" +
                      " limit 0, @top";
            MySqlConnection conn      = new MySqlConnection(Constants.DBConnection);
            MySqlCommand    MyCommand = new MySqlCommand(sql, conn);

            MyCommand.Parameters.AddWithValue("zoneId", zoneId);
            MyCommand.Parameters.AddWithValue("top", top);
            MySqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                UserInfo u = new UserInfo();
                u.uid      = MyReader.GetInt64("id");
                u.userName = MyReader.GetString("username");
                u.fullName = MyReader.GetString("fullname");
                u.cashWin  = MyReader.GetInt64("total_cash_win");
                rs.Add(u);
            }
            MyReader.Close();
            conn.Close();
            return(rs);
        }
Beispiel #2
0
        /// <summary>
        /// Báo cáo Danh sách user nạp nhiều tiền nhất (theo tổng số tiền Su đã nạp)
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public List <ReportUserChargeMoneyItem> ReportUserChargedMost(DateTime from, DateTime to, int top)
        {
            var             rs        = new List <ReportUserChargeMoneyItem>();
            string          sql       = String.Format("SELECT h.user_id, u.username, u.fullname, u.ip, u.gameCash, u.vCash, SUM(h.cash) AS 'SuNap' FROM newDB.game_history h INNER JOIN newDB.view_user_info u ON h.user_id = u.id WHERE h.trans_type = 4 and h.time > '{0}' and h.time < '{1}' group by h.user_id, u.username, u.fullname order by SUM(h.cash) desc limit 0, {2}", from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"), top);
            MySqlConnection conn      = new MySqlConnection(Constants.DBConnection);
            MySqlCommand    MyCommand = new MySqlCommand(sql, conn);
            MySqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                ReportUserChargeMoneyItem item1 = new ReportUserChargeMoneyItem();
                item1.user_id  = MyReader.GetInt32("user_id");
                item1.username = MyReader.GetString("username");
                item1.fullname = MyReader.GetString("fullname");
                item1.ip       = MyReader.GetString("ip");
                item1.gameCash = MyReader.GetInt64("gameCash");
                item1.vCash    = MyReader.GetInt64("vCash");
                if (String.IsNullOrEmpty(item1.fullname))
                {
                    item1.fullname = item1.username;
                }
                item1.moenyCharged = MyReader.GetInt64("SuNap");
                rs.Add(item1);
            }
            MyReader.Close();
            conn.Close();
            return(rs);
        }
Beispiel #3
0
        public List <UserInfo> GetTopNapTien()
        {
            var           rs        = new List <UserInfo>();
            var           sql       = "SELECT b.id, b.fullname, b.username, ABS(SUM(a.cash)) AS cashWin FROM [portal].[dbo].game_history a INNER JOIN [portal].[dbo].view_user_info b ON a.user_id = b.id WHERE trans_type = 4 AND a.cash > 0 GROUP BY b.id, b.fullname, b.username ORDER BY ABS(SUM(a.cash)) DESC";
            SqlConnection conn      = new SqlConnection(Constants.DBConnection);
            SqlCommand    MyCommand = new SqlCommand(sql, conn);
            SqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                var u = new UserInfo
                {
                    uid      = MyReader.GetInt64(MyReader.GetOrdinal("id")),
                    userName = MyReader.GetString(MyReader.GetOrdinal("username")),
                    fullName = MyReader.GetString(MyReader.GetOrdinal("fullname")),
                    cashWin  = MyReader.GetInt64(MyReader.GetOrdinal("cashWin"))
                };
                rs.Add(u);
            }
            MyReader.Close();
            conn.Close();
            return(rs);
        }
Beispiel #4
0
        public List <UserInfo> GetTopExp()
        {
            var           rs        = new List <UserInfo>();
            var           sql       = "SELECT top 100 * FROM [portal].[dbo].view_user_info ORDER BY EXP DESC";
            SqlConnection conn      = new SqlConnection(Constants.DBConnection);
            SqlCommand    MyCommand = new SqlCommand(sql, conn);
            SqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                var u = new UserInfo
                {
                    uid      = MyReader.GetInt64(MyReader.GetOrdinal("id")),
                    userName = MyReader.GetString(MyReader.GetOrdinal("username")),
                    fullName = MyReader.GetString(MyReader.GetOrdinal("fullname")),
                    cashWin  = MyReader.GetInt64(MyReader.GetOrdinal("EXP"))
                };
                rs.Add(u);
            }
            MyReader.Close();
            conn.Close();
            return(rs);
        }
Beispiel #5
0
        /// <summary>
        /// Danh sách lịch sử các trận đã chơi theo game Id
        /// </summary>
        /// <param name="zoneId"></param>
        /// <param name="skip"></param>
        /// <param name="take"></param>
        /// <returns></returns>
        public GameHistoryResult GetGameHistoryByZoneID(int zoneId, int skip, int take)
        {
            var rs            = new GameHistoryResult();
            var lst           = new List <GameHistoryItem>();
            var totalRecord   = 0;
            var sql           = "SELECT a.*, b.username, b.fullname FROM [portal].[dbo].game_history a inner join [portal].[dbo].view_user_info b on a.user_id = b.id WHERE game_id = @zoneId ORDER BY id DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY";
            var sqlTotalQuery = "SELECT count(*) as totalRecord FROM [portal].[dbo].game_history WHERE game_id = @zoneId";
            var conn          = new SqlConnection(Constants.DBConnection);
            var MyCommand     = new SqlCommand(sql, conn);

            MyCommand.Parameters.AddWithValue("zoneId", zoneId);
            MyCommand.Parameters.AddWithValue("skip", skip);
            MyCommand.Parameters.AddWithValue("take", take);
            SqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                var item1 = new GameHistoryItem
                {
                    id           = MyReader.GetInt32(MyReader.GetOrdinal("id")),
                    user_id      = MyReader.GetInt32(MyReader.GetOrdinal("user_id")),
                    cash         = MyReader.GetInt64(MyReader.GetOrdinal("cash")),
                    current_cash = MyReader.GetInt64(MyReader.GetOrdinal("current_cash")),
                    description  = MyReader.GetString(MyReader.GetOrdinal("description")),
                    game_id      = MyReader.GetInt32(MyReader.GetOrdinal("game_id")),
                    trans_type   = MyReader.GetInt32(MyReader.GetOrdinal("trans_type")),
                    time         = MyReader.GetDateTime(MyReader.GetOrdinal("time")),
                    before_cash  = MyReader.GetInt64(MyReader.GetOrdinal("before_cash")),
                    username     = MyReader.GetString(MyReader.GetOrdinal("username")),
                    fullname     = MyReader.GetString(MyReader.GetOrdinal("fullname"))
                };
                lst.Add(item1);
            }
            //close reader cũ
            MyReader.Close();
            MyCommand = new SqlCommand(sqlTotalQuery, conn);
            MyCommand.Parameters.AddWithValue("zoneId", zoneId);
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                totalRecord = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord"));
            }
            conn.Close();
            rs.data        = lst;
            rs.totalRecord = totalRecord;
            return(rs);
        }
Beispiel #6
0
        /// <summary>
        /// Danh sách lịch sử các trận đã chơi theo game Id
        /// </summary>
        /// <param name="zoneId"></param>
        /// <param name="skip"></param>
        /// <param name="take"></param>
        /// <returns></returns>
        public GameHistoryResult GetGameHistoryByZoneID(int zoneId, int skip, int take)
        {
            var             rs            = new GameHistoryResult();
            var             lst           = new List <GameHistoryItem>();
            int             totalRecord   = 0;
            string          sql           = "SELECT a.*, b.username, b.fullname FROM newDB.game_history a inner join newDB.view_user_info b on a.user_id = b.id WHERE game_id = @zoneId ORDER BY id DESC LIMIT @skip, @take";
            string          sqlTotalQuery = "SELECT count(*) as totalRecord FROM newDB.game_history WHERE game_id = @zoneId";
            MySqlConnection conn          = new MySqlConnection(Constants.DBConnection);
            MySqlCommand    MyCommand     = new MySqlCommand(sql, conn);

            MyCommand.Parameters.AddWithValue("zoneId", zoneId);
            MyCommand.Parameters.AddWithValue("skip", skip);
            MyCommand.Parameters.AddWithValue("take", take);
            MySqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                GameHistoryItem item1 = new GameHistoryItem();
                item1.id           = MyReader.GetInt64("id");
                item1.user_id      = MyReader.GetInt64("user_id");
                item1.cash         = MyReader.GetInt64("cash");
                item1.current_cash = MyReader.GetInt64("current_cash");
                item1.description  = MyReader.GetString("description");
                item1.game_id      = MyReader.GetInt32("game_id");
                item1.trans_type   = MyReader.GetInt32("trans_type");
                item1.time         = MyReader.GetDateTime("time");
                item1.before_cash  = MyReader.GetInt64("before_cash");
                item1.username     = MyReader.GetString("username");
                item1.fullname     = MyReader.GetString("fullname");
                lst.Add(item1);
            }
            //close reader cũ
            MyReader.Close();
            MyCommand = new MySqlCommand(sqlTotalQuery, conn);
            MyCommand.Parameters.AddWithValue("zoneId", zoneId);
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                totalRecord = MyReader.GetInt32("totalRecord");
            }
            conn.Close();
            rs.data        = lst;
            rs.totalRecord = totalRecord;
            return(rs);
        }
Beispiel #7
0
        public List <UserInfo> GetGiaoDich(out int total, int type, int skip, int take, int uid = 0)
        {
            var rs  = new List <UserInfo>();
            var sql = "SELECT b.id, b.fullname, b.username, a.description, a.time, a.trans_type FROM [portal].[dbo].game_history a INNER JOIN [portal].[dbo].view_user_info b ON a.user_id = b.id WHERE a.trans_type = " + type;

            var sqlTotalQuery = " SELECT Count(a.id) as totalRecord FROM [portal].[dbo].game_history a WHERE a.trans_type = " + type;

            if (uid > 0)
            {
                sql           += " AND user_id = " + uid;
                sqlTotalQuery += " AND user_id = " + uid;
            }

            sql += " ORDER BY a.id desc OFFSET " + skip + " ROWS FETCH NEXT " + take + " ROWS ONLY";

            var           conn      = new SqlConnection(Constants.DBConnection);
            var           MyCommand = new SqlCommand(sql, conn);
            SqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                var u = new UserInfo
                {
                    uid          = MyReader.GetInt64(MyReader.GetOrdinal("id")),
                    userName     = MyReader.GetString(MyReader.GetOrdinal("username")),
                    fullName     = MyReader.GetString(MyReader.GetOrdinal("fullname")),
                    description  = MyReader.GetString(MyReader.GetOrdinal("description")),
                    dateRegister = MyReader.GetString(MyReader.GetOrdinal("time")),
                    typyPlay     = MyReader.GetInt32(MyReader.GetOrdinal("trans_type"))
                };
                rs.Add(u);
            }
            MyReader.Close();

            MyCommand = new SqlCommand(sqlTotalQuery, conn);
            MyReader  = MyCommand.ExecuteReader();
            total     = 0;
            while (MyReader.Read())
            {
                total = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord"));
            }

            conn.Close();
            return(rs);
        }
Beispiel #8
0
        /// <summary>
        /// Select một user theo user_id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserInfo SelectOne(long id)
        {
            var             user      = new UserInfo();
            string          sql       = "select * from newDB.view_user_info where id = " + id;
            MySqlConnection conn      = new MySqlConnection(Constants.DBConnection);
            MySqlCommand    MyCommand = new MySqlCommand(sql, conn);
            MySqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                user.uid          = MyReader.GetInt64("id");
                user.userName     = MyReader.GetString("username");
                user.fullName     = MyReader.GetString("fullname");
                user.dateRegister = MyReader.GetDateTime("register_date");
                user.isMale       = MyReader.GetInt32("sex") == 1;
                user.level        = MyReader.GetInt32("level_id");
                user.cash         = MyReader.GetInt64("gameCash");
                user.vcash        = MyReader.GetInt64("vCash");
                user.playsNumber  = MyReader.GetInt32("totalGame");
                user.playsWin     = MyReader.GetInt32("totalWin");
                if (!MyReader.IsDBNull(8))
                {
                    user.email = MyReader.GetString("email");
                }
                if (!MyReader.IsDBNull(7))
                {
                    user.phone = MyReader.GetString("mobile");
                }
                if (!MyReader.IsDBNull(21))
                {
                    user.cmnd = MyReader.GetString("cmnd");
                }
                user.ipAddress = MyReader.GetString("ip");
                user.isMobile  = MyReader.GetInt32("isMobile");
                user.lastLogin = MyReader.GetDateTime("last_login");
            }
            //close reader cũ
            MyReader.Close();
            conn.Close();

            return(user);
        }
Beispiel #9
0
        public GameHistoryResult ExportExcel(DateTime?from, DateTime?to)
        {
            var rs          = new GameHistoryResult();
            var lst         = new List <GameHistoryItem>();
            int totalRecord = 0;
            var sql         = "SELECT a.*, b.username, b.fullname FROM [newDB].[dbo].game_history a inner join [newDB].[dbo].view_user_info b on a.user_id = b.id WHERE a.trans_type = 16";

            if (from != null && to != null)
            {
                sql += " and time >= '" + from.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' and time <= '" + to.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            sql += " order by id desc";
            var           conn      = new SqlConnection(Constants.DBConnection);
            var           MyCommand = new SqlCommand(sql, conn);
            SqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                var item1 = new GameHistoryItem
                {
                    id           = MyReader.GetInt32(MyReader.GetOrdinal("id")),
                    user_id      = MyReader.GetInt32(MyReader.GetOrdinal("user_id")),
                    cash         = MyReader.GetInt64(MyReader.GetOrdinal("cash")),
                    current_cash = MyReader.GetInt64(MyReader.GetOrdinal("current_cash")),
                    description  = MyReader.GetString(MyReader.GetOrdinal("description")),
                    game_id      = MyReader.GetInt32(MyReader.GetOrdinal("game_id")),
                    trans_type   = MyReader.GetInt32(MyReader.GetOrdinal("trans_type")),
                    time         = MyReader.GetDateTime(MyReader.GetOrdinal("time")),
                    before_cash  = MyReader.GetInt64(MyReader.GetOrdinal("before_cash")),
                    username     = MyReader.GetString(MyReader.GetOrdinal("username")),
                    fullname     = MyReader.GetString(MyReader.GetOrdinal("fullname"))
                };
                item1.timestring = item1.time.Value.ToShortDateString();
                lst.Add(item1);
            }
            //close reader cũ
            MyReader.Close();

            rs.data = lst;
            return(rs);
        }
Beispiel #10
0
        /// <summary>
        /// Báo cáo Danh sách top người chơi thắng nhiều nhất (tính theo số trận thắng)
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <param name="top"></param>
        /// <param name="vCash"></param>
        /// <returns></returns>
        public List <ReportUserPlayGameItem> ReportUserPlayWin(DateTime from, DateTime to, int top, bool vCash)
        {
            var    rs  = new List <ReportUserPlayGameItem>();
            string sql = String.Format("select user_id, username, fullname, ip, gameCash, vCash, count(0) as 'totalGame' from newDB.view_user_history_total where is_active = 1 and time > '{0}' and time < '{1}' and cash > 0 group by user_id, username, fullname, ip order by COUNT(0) desc limit 0, {2}", from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"), top);

            if (vCash)
            {
                sql = String.Format("select user_id, username, fullname, ip, gameCash, vCash, count(0) as 'totalGame' from newDB.view_user_history_total_vCash where is_active = 1 and time > '{0}' and time < '{1}' and cash > 0 group by user_id, username, fullname, ip order by COUNT(0) desc limit 0, {2}", from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"), top);
            }
            MySqlConnection conn      = new MySqlConnection(Constants.DBConnection);
            MySqlCommand    MyCommand = new MySqlCommand(sql, conn);
            MySqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                ReportUserPlayGameItem item1 = new ReportUserPlayGameItem();
                item1.user_id  = MyReader.GetInt32("user_id");
                item1.username = MyReader.GetString("username");
                item1.fullname = MyReader.GetString("fullname");
                item1.ip       = MyReader.GetString("ip");
                item1.gameCash = MyReader.GetInt64("gameCash");
                item1.vCash    = MyReader.GetInt64("vCash");
                if (String.IsNullOrEmpty(item1.fullname))
                {
                    item1.fullname = item1.username;
                }
                item1.totalGame = MyReader.GetInt32("totalGame");
                rs.Add(item1);
            }
            //close reader cũ
            MyReader.Close();
            conn.Close();
            return(rs);
        }
Beispiel #11
0
        /// <summary>
        /// Danh sách user, sắp xếp theo đăng ký mới nhất
        /// </summary>
        /// <param name="id"></param>
        /// <param name="username"></param>
        /// <param name="fullname"></param>
        /// <param name="phone"></param>
        /// <param name="email"></param>
        /// <param name="cmnd"></param>
        /// <param name="regFrom"></param>
        /// <param name="regTo"></param>
        /// <param name="mobile"></param>
        /// <param name="active"></param>
        /// <param name="limit"></param>
        /// <param name="take"></param>
        /// <returns></returns>
        public UserInfoSearchResult Search(long id, string username, string fullname, string phone, string email, string cmnd, DateTime regFrom, DateTime regTo, int mobile, bool?active, int limit, int take)
        {
            UserInfoSearchResult rs = new UserInfoSearchResult();
            var lst         = new List <UserInfo>();
            int totalRecord = 0;


            string sql           = "select * from newDB.view_user_info where register_date >= '" + regFrom.ToString(Constants.DateFormat) + "' and register_date <= '" + regTo.ToString(Constants.DateFormat) + "'";
            string sqlTotalQuery = "select count(*) as totalRecord from newDB.view_user_info where register_date >= '" + regFrom.ToString(Constants.DateFormat) + "' and register_date <= '" + regTo.ToString(Constants.DateFormat) + "'";

            if (id > 0)
            {
                sql           += " and id = " + id.ToString();
                sqlTotalQuery += " and id = " + id.ToString();
            }
            if (!String.IsNullOrEmpty(username))
            {
                sql           += " and username like '%" + username + "%'";
                sqlTotalQuery += " and username like '%" + username + "%'";
            }
            if (!String.IsNullOrEmpty(fullname))
            {
                sql           += " and fullname like '%" + fullname + "%'";
                sqlTotalQuery += " and fullname like '%" + fullname + "%'";
            }
            if (!String.IsNullOrEmpty(phone))
            {
                sql           += " and mobile like '%" + phone + "%'";
                sqlTotalQuery += " and mobile like '%" + phone + "%'";
            }
            if (!String.IsNullOrEmpty(email))
            {
                sql           += " and email like '%" + email + "%'";
                sqlTotalQuery += " and email like '%" + email + "%'";
            }
            if (!String.IsNullOrEmpty(cmnd))
            {
                sql           += " and cmnd like '%" + cmnd + "%'";
                sqlTotalQuery += " and cmnd like '%" + cmnd + "%'";
            }
            if (mobile > 0)
            {
                sql           += " and isMobile = " + mobile.ToString();
                sqlTotalQuery += " and isMobile = " + mobile.ToString();
            }
            if (active != null)
            {
                if (Convert.ToBoolean(active))
                {
                    sql           += " and is_active = 1";
                    sqlTotalQuery += " and is_active = 1";
                }
                else
                {
                    sql           += " and is_active = 2";
                    sqlTotalQuery += " and is_active = 2";
                }
            }
            sql += " order by id desc";
            sql += " limit " + limit + ", " + take;
            MySqlConnection conn      = new MySqlConnection(Constants.DBConnection);
            MySqlCommand    MyCommand = new MySqlCommand(sql, conn);
            MySqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                UserInfo user = new UserInfo();
                user.uid          = MyReader.GetInt64("id");
                user.userName     = MyReader.GetString("username");
                user.fullName     = MyReader.GetString("fullname");
                user.dateRegister = MyReader.GetDateTime("register_date");
                user.isMale       = MyReader.GetInt32("sex") == 1;
                user.level        = MyReader.GetInt32("level_id");
                user.cash         = MyReader.GetInt64("gameCash");
                user.vcash        = MyReader.GetInt64("vCash");
                user.playsNumber  = MyReader.GetInt32("totalGame");
                user.playsWin     = MyReader.GetInt32("totalWin");
                if (!MyReader.IsDBNull(8))
                {
                    user.email = MyReader.GetString("email");
                }
                if (!MyReader.IsDBNull(7))
                {
                    user.phone = MyReader.GetString("mobile");
                }
                if (!MyReader.IsDBNull(21))
                {
                    user.cmnd = MyReader.GetString("cmnd");
                }
                user.ipAddress = MyReader.GetString("ip");
                user.isMobile  = MyReader.GetInt32("isMobile");
                user.lastLogin = MyReader.GetDateTime("last_login");
                lst.Add(user);
            }
            //close reader cũ
            MyReader.Close();
            MyCommand = new MySqlCommand(sqlTotalQuery, conn);
            MyReader  = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                totalRecord = MyReader.GetInt32("totalRecord");
            }
            conn.Close();
            rs.data        = lst;
            rs.totalRecord = totalRecord;
            return(rs);
        }
Beispiel #12
0
        /// <summary>
        /// Danh sách user nạp thẻ, sms
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <param name="skip"></param>
        /// <param name="take"></param>
        /// <returns></returns>
        public ChargeResult GetCharged(DateTime from, DateTime to, int skip, int take)
        {
            var             rs          = new ChargeResult();
            var             data        = new List <ChargeItem>();
            int             totalRecord = 0;
            string          sql         = "SELECT b.id AS 'user_id', b.username,b.fullname,'card' as 'type', cardNumber, cardSerial, cp,cardPrice, refNo, tranNo, source, dateCreated FROM portal.a_paycard a inner join portal.user b on a.username=b.id where a.cardPrice > 0 and a.dateCreated >= @date1 and dateCreated <= @date2 union all SELECT b.id AS 'user_id', b.username, b.fullname, 'sms' AS 'type', '' as 'cardNumber','' as 'cardSerial', telco as 'cp',amount as 'cardPrice', '' as 'refNo', '' as 'tranNo', '' as 'source' , responeTime as 'dateCreated' FROM portal.a_smsplus a INNER JOIN portal.user b ON a.targetUser=b.username WHERE amount > 0 AND a.responeTime >= @date3 AND a.responeTime <= @date4 ORDER BY dateCreated DESC LIMIT @skip, @take";
            MySqlConnection conn        = new MySqlConnection(Constants.DBConnection);
            MySqlCommand    MyCommand   = new MySqlCommand(sql, conn);

            MyCommand.Parameters.AddWithValue("date1", from.ToString(Constants.DateFormat));
            MyCommand.Parameters.AddWithValue("date2", to.ToString(Constants.DateFormat));
            MyCommand.Parameters.AddWithValue("date3", from.ToString(Constants.DateFormat));
            MyCommand.Parameters.AddWithValue("date4", to.ToString(Constants.DateFormat));
            MyCommand.Parameters.AddWithValue("skip", skip);
            MyCommand.Parameters.AddWithValue("take", take);
            MySqlDataReader MyReader;

            conn.Open();
            MyReader = MyCommand.ExecuteReader();
            int stt = 1;

            while (MyReader.Read())
            {
                ChargeItem item1 = new ChargeItem();
                item1.Stt        = stt;
                item1.uid        = MyReader.GetInt64("user_id");
                item1.username   = MyReader.GetString("username");
                item1.fullname   = MyReader.GetString("fullname");
                item1.type       = MyReader.GetString("type");
                item1.cardNumber = MyReader.GetString("cardNumber");
                item1.cardSerial = MyReader.GetString("cardSerial");
                item1.telco      = MyReader.GetString("cp");
                item1.Price      = MyReader.GetInt64("cardPrice");
                item1.refNo      = MyReader.GetString("refNo");
                item1.tranNo     = MyReader.GetString("tranNo");
                item1.source     = MyReader.GetString("source");
                item1.time       = MyReader.GetDateTime("dateCreated");
                item1.timeString = item1.time.ToString("dd-MM-yyyy");

                data.Add(item1);
                stt++;
            }
            MyReader.Close();
            sql       = "Select count(*) as totalRecord from portal.a_paycard where cardPrice > 0 and dateCreated >= @date1 and dateCreated <= @date2";
            MyCommand = new MySqlCommand(sql, conn);
            MyCommand.Parameters.AddWithValue("date1", from.ToString(Constants.DateFormat));
            MyCommand.Parameters.AddWithValue("date2", to.ToString(Constants.DateFormat));
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                totalRecord += MyReader.GetInt32("totalRecord");
            }
            MyReader.Close();
            sql       = "Select count(*) as totalRecord from portal.a_smsplus WHERE amount > 0 AND responeTime >= @date3 AND responeTime <= @date4";
            MyCommand = new MySqlCommand(sql, conn);
            MyCommand.Parameters.AddWithValue("date3", from.ToString(Constants.DateFormat));
            MyCommand.Parameters.AddWithValue("date4", to.ToString(Constants.DateFormat));
            MyReader = MyCommand.ExecuteReader();
            while (MyReader.Read())
            {
                totalRecord += MyReader.GetInt32("totalRecord");
            }
            MyReader.Close();
            conn.Close();
            rs.data        = data;
            rs.totalRecord = totalRecord;
            return(rs);
        }
Beispiel #13
0
        /// <summary>
        /// Duyệt một yêu cầu đổi thưởng nào đó
        /// </summary>
        /// <param name="id"></param>
        /// <param name="approval"></param>
        /// <returns></returns>
        public bool Approval(long id, bool approval)
        {
            var rs = false;

            //
            if (approval)
            {
                //lấy mã thẻ, cập nhật history, chèn sms offline
                int             valueCard = 0;
                int             telcoId   = 0;
                int             user_id   = 0;
                string          sql       = "SELECT user_id, valueCard, telcoId FROM newDB.game_history WHERE id = @id";
                MySqlConnection conn      = new MySqlConnection(Constants.DBConnection);
                MySqlCommand    MyCommand = new MySqlCommand(sql, conn);
                MyCommand.Parameters.AddWithValue("id", id);
                MySqlDataReader MyReader;
                conn.Open();
                MyReader = MyCommand.ExecuteReader();
                while (MyReader.Read())
                {
                    valueCard = MyReader.GetInt32("valueCard");
                    telcoId   = MyReader.GetInt32("telcoId");
                    user_id   = MyReader.GetInt32("user_id");
                }
                MyReader.Close();
                //lấy mã thẻ từ hệ thống
                string sqlCardInfo = "SELECT * FROM portal.exchangeCardInfo WHERE value = '" + valueCard + "' AND telcoId = '" + telcoId + "' AND used = 0 LIMIT 0,1";
                MyCommand = new MySqlCommand(sqlCardInfo, conn);
                MyReader  = MyCommand.ExecuteReader();
                int    cardId     = 0;
                string cardSerial = "";
                string cardNo     = "";
                bool   hetThe     = true;
                while (MyReader.Read())
                {
                    cardId     = MyReader.GetInt32("id");
                    cardSerial = MyReader.GetString("serial");
                    cardNo     = MyReader.GetString("cardNo");
                    hetThe     = false;
                }
                MyReader.Close();
                if (hetThe)
                {
                    rs = false;
                }
                else
                {
                    //cập nhật mã thẻ đã sử dụng
                    string sqlUpdateCard = "UPDATE portal.exchangeCardInfo set used = 1, dateUse='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE id='" + cardId + "'";
                    MyCommand = new MySqlCommand(sqlUpdateCard, conn);
                    int rowUpdate = MyCommand.ExecuteNonQuery();

                    if (rowUpdate > 0)
                    {
                        //update thẻ này được sử dụng cho user nào
                        string sqlUpdateLog = "INSERT INTO portal.exchangeHistory(cardId, userId) VALUES ('" + cardId + "','" + user_id + "')";
                        MyCommand = new MySqlCommand(sqlUpdateLog, conn);
                        rowUpdate = MyCommand.ExecuteNonQuery();
                        //cập nhật lại bản ghi là đã duyệt
                        string sqlUpdateHistory = "UPDATE newDB.game_history set description = @description, trans_type = @trans_type WHERE id = @id";
                        MyCommand = new MySqlCommand(sqlUpdateHistory, conn);
                        MyCommand.Parameters.AddWithValue("description", "Duyệt đổi thẻ cào " + cardSerial);
                        MyCommand.Parameters.AddWithValue("trans_type", 16);
                        MyCommand.Parameters.AddWithValue("id", id);
                        rowUpdate = MyCommand.ExecuteNonQuery();
                        //cập nhật vào mess offline
                        string telcoName = "";
                        sql       = "select * from portal.exchangeTelco WHERE id = '" + telcoId + "'";
                        MyCommand = new MySqlCommand(sql, conn);
                        MyReader  = MyCommand.ExecuteReader();
                        while (MyReader.Read())
                        {
                            telcoName = MyReader.GetString("name");
                        }
                        MyReader.Close();
                        sql       = "insert into newDB.offlinemessage (userIDSend,userIDReceive,mes,datetimeSend) values (@userIDSend,@userIDReceive,@mes,now());";
                        MyCommand = new MySqlCommand(sql, conn);
                        MyCommand.Parameters.AddWithValue("userIDSend", 8934);
                        MyCommand.Parameters.AddWithValue("userIDReceive", user_id);
                        MyCommand.Parameters.AddWithValue("mes", "Duyệt đổi thẻ, Nhà mạng " + telcoName + ", Mệnh giá " + valueCard + " VND, Mã thẻ: " + cardNo + ", Serial: " + cardSerial + " lúc " + DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"));
                        rowUpdate = MyCommand.ExecuteNonQuery();
                        if (rowUpdate > 0)
                        {
                            rs = true;
                        }
                    }
                }
                conn.Close();
            }
            else
            {
                //hoàn trả tiền
                long            cashReturn = 0;
                int             user_id    = 0;
                string          sql        = "SELECT * FROM newDB.game_history WHERE id = @id";
                MySqlConnection conn       = new MySqlConnection(Constants.DBConnection);
                MySqlCommand    MyCommand  = new MySqlCommand(sql, conn);
                MyCommand.Parameters.AddWithValue("id", id);
                MySqlDataReader MyReader;
                conn.Open();
                MyReader = MyCommand.ExecuteReader();
                while (MyReader.Read())
                {
                    cashReturn = MyReader.GetInt32("cash");
                    user_id    = MyReader.GetInt32("user_id");
                }
                MyReader.Close();
                if (cashReturn < 0)
                {
                    cashReturn = cashReturn * (-1);
                }
                //lấy ra số tiền hiện tại của user
                long currentCash = 0;
                sql       = "SELECT gameCash FROM newDB.g_user WHERE user_id = @user_id";
                MyCommand = new MySqlCommand(sql, conn);
                MyCommand.Parameters.AddWithValue("user_id", user_id);
                MyReader = MyCommand.ExecuteReader();
                while (MyReader.Read())
                {
                    currentCash = MyReader.GetInt64("gameCash");
                }
                MyReader.Close();
                //Cập nhật số tiền
                string sqlUpdateCash = "UPDATE newDB.g_user SET  gameCash = gameCash + " + cashReturn + " WHERE user_id='" + user_id + "' ";
                MyCommand = new MySqlCommand(sqlUpdateCash, conn);
                int rowUpdate = MyCommand.ExecuteNonQuery();
                if (rowUpdate > 0)
                {
                    long beforCash = currentCash;
                    long afterCash = beforCash + cashReturn;
                    sql       = "INSERT into newDB.game_history (user_id,cash,current_cash,description,game_id,trans_type,tax,taxPercent,before_cash) values (@user_id,@cash,@current_cash,@description,@game_id, @trans_type,@tax,@taxPercent,@before_cash);";
                    MyCommand = new MySqlCommand(sql, conn);
                    MyCommand.Parameters.AddWithValue("user_id", user_id);
                    MyCommand.Parameters.AddWithValue("cash", cashReturn);
                    MyCommand.Parameters.AddWithValue("current_cash", afterCash);
                    MyCommand.Parameters.AddWithValue("description", "Hoàn lại tiền vì không duyệt đổi thưởng");
                    MyCommand.Parameters.AddWithValue("game_id", 0);
                    MyCommand.Parameters.AddWithValue("trans_type", 16);
                    MyCommand.Parameters.AddWithValue("tax", 0);
                    MyCommand.Parameters.AddWithValue("taxPercent", 0);
                    MyCommand.Parameters.AddWithValue("before_cash", beforCash);
                    rowUpdate = MyCommand.ExecuteNonQuery();
                    if (rowUpdate > 0)
                    {
                        //xóa record yêu cầu đổi thưởng cũ đi
                        sql       = "DELETE from newDB.game_history where id = @id";
                        MyCommand = new MySqlCommand(sql, conn);
                        MyCommand.Parameters.AddWithValue("id", id);
                        rowUpdate = MyCommand.ExecuteNonQuery();
                        if (rowUpdate > 0)
                        {
                            rs = true;
                        }
                    }
                }

                conn.Close();
            }
            return(rs);
        }