hjha-server/GameDAL/Club/ClubLeagueMatchAdo.cs

using ObjectModel.Club;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GameDAL.Game;
using MrWu.DB;
using MrWu.Debug;
using Server.DB.Sql;

namespace GameDAL.Club
{
    /// <summary>
    /// 俱乐部联赛数据逻辑
    /// </summary>
    public static class ClubLeagueMatchAdo
    {
        public static readonly dbbase sqldb = dbbase.game2018;
        public static readonly string tb_JoinInClubLeagueMatch = "JoinInClubLeagueMatch";
        public static readonly string tb_LeagueAwardRecord = "LeagueAwardRecord";
        public static readonly string tb_LeagueClubLeaguePower = "LeagueClubLeaguePower";
        public static readonly string tb_LeagueClubLiveTask = "LeagueClubLiveTask";
        public static readonly string tb_LeagueMatchBanDesk = "LeagueMatchBanDesk";
        public static readonly string tb_LeagueMatchClubScore = "LeagueMatchClubScore";


        /// <summary>
        /// 获取俱乐部权限 异步的
        /// </summary>
        /// <param name="clubId"></param>
        /// <returns></returns>
        public static Task<ClubLeaguePower> GetClubLeaguePowerAsync(int clubId)
        {
            return Task.Run(() => GetClubLeaguePower(clubId));
        }

        /// <summary>
        /// 获取俱乐部权限
        /// </summary>
        /// <param name="clubId"></param>
        /// <returns></returns>
        public static ClubLeaguePower GetClubLeaguePower(int clubId)
        {
            var sql = $"select top 1 * from {tb_LeagueClubLeaguePower} where clubId = {clubId}";
            return sqldb.ExecuteEntities(sql, p => p.ToObject<ClubLeaguePower>()).FirstOrDefault();
        }

        public static bool SaveClubLeaguePower(ClubLeaguePower model)
        {
            var sql = new StringBuilder();
            if (model.ClubLeaguePowerId < 1)
            {
                sql.Append(
                    $"insert into {tb_LeagueClubLeaguePower}({nameof(model.ClubId)},{nameof(model.IsCanCreate)},{nameof(model.IsCanJoin)}) ");
                sql.Append($"values({model.ClubId},{model.IsCanCreate.SQLValue()},{model.IsCanJoin.SQLValue()})");
                sql.AppendLine($"if @@rowcount > 0 begin");
                sql.AppendLine(
                    $"    select top 1 {nameof(model.ClubLeaguePowerId)} from {tb_LeagueClubLeaguePower} where {nameof(model.ClubLeaguePowerId)} = SCOPE_IDENTITY()");
                sql.AppendLine("end");
                var result = sqldb.ExecuteScalar(sql.ToString());
                model.ClubLeaguePowerId = result is int id ? id : throw new Exception("增加记录失败, sql:" + sql);
            }
            else
            {
                sql.Append($"update {tb_LeagueClubLeaguePower} set ");
                sql.Append($"{nameof(model.ClubId)} = {model.ClubId},");
                sql.Append($"{nameof(model.IsCanCreate)} = {model.IsCanCreate.SQLValue()},");
                sql.Append($"{nameof(model.IsCanJoin)} = {model.IsCanJoin.SQLValue()}");
                sql.Append($" where {nameof(model.ClubLeaguePowerId)} = {model.ClubLeaguePowerId}");
                if (1 != sqldb.ExecuteNonQuery(sql.ToString()))
                {
                    throw new Exception("更新记录失败，sql: " + sql);
                }
            }

            return true;
        }

        /// <summary>
        /// 保存联赛禁桌数据，每次删除数据在保存。根据ClubMatchId 删除
        /// </summary>
        /// <param name="list"></param>
        public static bool SaveBanDesks(List<LeagueMatchBanDesk> list, int clubMatchId)
        {
            return sqldb.ExecuteTransaction(false, t =>
            {
                //var todeleteItems = list.Select(p => clubMatchId).Distinct().Where(p => p > 0).ToArray();
                //if (todeleteItems.Length > 0) {
                var deleteSql = $"delete {tb_LeagueMatchBanDesk} where ClubMatchId = {clubMatchId}";
                var deletedCount = t.ExecuteNonQuery(deleteSql);
                if (deletedCount > 0)
                {
                    Console.WriteLine("SaveBanDesks delete count:" + deletedCount);
                }

                //}
                var sql = new StringBuilder();
                foreach (var p in list)
                {
                    sql.Append(
                        $"insert into {tb_LeagueMatchBanDesk}(ClubMatchId,{nameof(p.WayId)},{nameof(p.JSONString)}) ");
                    sql.AppendLine($"values({clubMatchId}, @wayId, @json)");
                    sql.AppendLine($"if @@rowcount > 0 begin");
                    sql.AppendLine(
                        $"    select top 1 {nameof(p.LeagueMatchBanDeskId)} from {tb_LeagueMatchBanDesk} where {nameof(p.LeagueMatchBanDeskId)} = SCOPE_IDENTITY()");
                    sql.AppendLine("end");
                    var result = t.ExecuteScalar(sql.ToString(), new SqlParameter("@wayId", p.WayId),
                        new SqlParameter("@json", p.GetClubsToString()));
                    p.LeagueMatchBanDeskId = result is int id ? id : throw new Exception("增加记录失败, sql:" + sql);
                    sql.Clear();
                }

                return true;
            });
        }

        /// <summary>
        /// 获取联赛禁桌数据
        /// </summary>
        /// <param name="clubMatchId"></param>
        /// <returns></returns>
        public static List<LeagueMatchBanDesk> GetLeagueMatchBanDesks(int clubMatchId)
        {
            var sql = $"select * from {tb_LeagueMatchBanDesk} where ClubMatchId = {clubMatchId}";
            return sqldb.ExecuteEntities(sql, p =>
            {
                var model = p.ToObject<LeagueMatchBanDesk>();
                model.ClubIds = model.GetClubIdsByJSON();
                return model;
            }).ToList();
        }

        /// <summary>
        /// 保存联赛俱乐部统计数据，每次删除数据在保存。根据ClubMatchId 删除
        /// </summary>
        /// <param name="list"></param>
        public static bool SaveLeagueMatchClubScore(List<LeagueMatchClubScore> list, int matchId)
        {
            return sqldb.ExecuteTransaction(false, t =>
            {
                var deleteSql =
                    $"delete {tb_LeagueMatchClubScore} where ClubMatchId = {matchId}";
                var deletedCount = t.ExecuteNonQuery(deleteSql);
                if (deletedCount > 0)
                {
                    Console.WriteLine("SaveLeagueMatchClubScore delete count:" + deletedCount);
                }

                var sql = new StringBuilder();
                foreach (var p in list)
                {
                    sql.Append(
                        $"insert into {tb_LeagueMatchClubScore}(ClubMatchId,{nameof(p.ClubName)},{nameof(p.ClubId)},{nameof(p.CardScore)},{nameof(p.DeskScore)},{nameof(p.ClubUserSumScore)},{nameof(p.JoinRoom)},[Index],{nameof(p.LeagueScore)}) ");
                    sql.AppendLine(
                        $"values({matchId}, @clubName,{p.ClubId},{p.CardScore},{p.DeskScore},{p.ClubUserSumScore},{p.JoinRoom},0,@score)");
                    sql.AppendLine($"if @@rowcount > 0 begin");
                    sql.AppendLine(
                        $"    select top 1 {nameof(p.LeagueMatchClubScoreId)} from {tb_LeagueMatchClubScore} where {nameof(p.LeagueMatchClubScoreId)} = SCOPE_IDENTITY()");
                    sql.AppendLine("end");
                    var result = t.ExecuteScalar(sql.ToString(), new SqlParameter("@clubName", p.ClubName),
                        new SqlParameter("@score", p.LeagueScore));
                    p.LeagueMatchClubScoreId = result is int id ? id : throw new Exception("增加记录失败, sql:" + sql);
                    sql.Clear();
                }

                return true;
            });
        }

        /// <summary>
        /// 获取联赛俱乐部统计数据,过期时间7天
        /// </summary>
        /// <param name="clubMatchId"></param>
        /// <returns></returns>
        public static List<LeagueMatchClubScore> GetLeagueMatchClubScores(int clubMatchId)
        {
            var sql = $"select * from {tb_LeagueMatchClubScore} where clubMatchId = {clubMatchId}";
            return sqldb.ExecuteEntities(sql, p => p.ToObject<LeagueMatchClubScore>()).ToList();
        }

        /// <summary>
        /// 保存联赛颁奖记录，清空对应clubMatchId比赛的缓存（每次都是新的数据，不用做删除操作）
        /// </summary>
        /// <param name="mode"></param>
        public static bool SaveAwardRecord(List<AwardRecord> list)
        {
            return sqldb.ExecuteTransaction(false, t =>
            {
                var sql = new StringBuilder();
                foreach (var p in list)
                {
                    sql.Append(
                        $"insert into {tb_LeagueAwardRecord}({nameof(p.ClubMatchId)},{nameof(p.ClubName)},{nameof(p.ClubId)},{nameof(p.CardScore)},{nameof(p.DeskScore)},{nameof(p.ClubUserSumScore)},{nameof(p.JoinRoom)},[{nameof(p.Index)}],{nameof(p.RecordId)},{nameof(p.LeagueScore)}) ");
                    sql.AppendLine(
                        $"values({p.ClubMatchId}, @clubName,{p.ClubId},{p.CardScore},{p.DeskScore},{p.ClubUserSumScore},{p.JoinRoom},{p.Index},{p.RecordId},@score)");
                    // sql.AppendLine($"if @@rowcount > 0 begin");
                    // sql.AppendLine(
                    //     $"    select top 1 {nameof(p.AwardRecordId)} from {tb_LeagueAwardRecord} where {nameof(p.AwardRecordId)} = SCOPE_IDENTITY()");
                    // sql.AppendLine("end");
                    var result = t.ExecuteScalar(sql.ToString(), new SqlParameter("@clubName", p.ClubName),
                        new SqlParameter("@score", p.LeagueScore));
                    //p.AwardRecordId = result is int id ? id : throw new Exception("增加记录失败, sql:" + sql);
                    sql.Clear();
                }

                return true;
            });
        }

        /// <summary>
        /// 获取联赛颁奖记录 近几条
        /// </summary>
        /// <param name="clubMatchId"></param>
        /// <param name="topNum"></param>
        /// <returns></returns>
        public static AwardRecord GetAwardByTop(int clubMatchId, int topNum = 1)
        {
            var sql = $"select top 1 * from {tb_LeagueAwardRecord} where clubMatchId = {clubMatchId}";
            return sqldb.ExecuteEntities(sql, p => p.ToObject<AwardRecord>()).FirstOrDefault();
        }

        /// <summary>
        /// 获取联赛颁奖记录。根据主键id倒序
        /// </summary>
        /// <param name="clubMatchId">比赛Id</param>
        /// <param name="dateTime"> CreateTime>dateTime </param>
        /// <returns></returns>
        public static List<AwardRecord> GetAwardRecords(int clubMatchId, DateTime dateTime)
        {
            var sql =
                $"select * from {tb_LeagueAwardRecord} where clubMatchId = {clubMatchId} and CreateTime > @datetime";
            return sqldb.ExecuteEntities(sql, p => p.ToObject<AwardRecord>(), new SqlParameter("@datetime", dateTime))
                .ToList();
        }

        /// <summary>
        /// 获取联赛颁奖记录。根据主键id倒序,根据比赛：clubMatchId缓存 时间1天
        /// </summary>
        /// <param name="clubMatchId"></param>
        /// <returns></returns>
        public static List<AwardRecord> GetAwardRecords(int clubMatchId)
        {
            var sql = $"select * from {tb_LeagueAwardRecord} where clubMatchId = {clubMatchId}";
            return sqldb.ExecuteEntities(sql, p => p.ToObject<AwardRecord>()).ToList();
        }

        /// <summary>
        /// 保存生存任务，先删除再保存
        /// </summary>
        /// <param name="lists"></param>
        public static bool SaveLeagueClubLiveTasks(List<LeagueClubLiveTask> list, int clubMatchId)
        {
            return sqldb.ExecuteTransaction(false, t =>
            {
                if (clubMatchId > 0)
                {
                    var deleteSql = $"delete {tb_LeagueClubLiveTask} where ClubMatchId = {clubMatchId}";
                    Debug.Info($"执行Sql语句{deleteSql}");
                    var deletedCount = t.ExecuteNonQuery(deleteSql);
                    if (deletedCount > 0)
                    {
                        Console.WriteLine("SaveLeagueClubLiveTasks delete count:" + deletedCount);
                    }
                }

                var sql = new StringBuilder();
                foreach (var p in list)
                {
                    sql.Append(
                        $"insert into {tb_LeagueClubLiveTask}(ClubMatchId,{nameof(p.ClubId)},{nameof(p.TaskScore)},{nameof(p.IsActive)}) ");
                    sql.AppendLine($"values({clubMatchId}, {p.ClubId},{p.TaskScore},{p.IsActive.SQLValue()})");
                    sql.AppendLine($"if @@rowcount > 0 begin");
                    sql.AppendLine(
                        $"    select top 1 LeagueClubLiveTaskId from {tb_LeagueClubLiveTask} where LeagueClubLiveTaskId = SCOPE_IDENTITY()");
                    sql.AppendLine("end");
                    var result = t.ExecuteScalar(sql.ToString());
                    p.LeagueClubLiveTaskId = result is int id ? id : throw new Exception("增加记录失败, sql:" + sql);
                    sql.Clear();
                }

                return true;
            });
        }

        /// <summary>
        /// 保存加入到联赛的俱乐部集合
        /// </summary>
        /// <param name="model"></param>
        public static bool SaveJoinInClubLeagueMatch(JoinInClubLeagueMatch model)
        {
            Debug.Info($"保存加入联赛的俱乐部集合:{model.ClubMatchId} {model.GetJoinInLeagueMatchClubsToString()}");

            var spp = GameDB.Instance.BeginTransaction(dbbase.game2018);

            string sql =
                $@"if not exists (select 1 from {tb_JoinInClubLeagueMatch} where ClubMatchId = {model.ClubMatchId})
                                insert into {tb_JoinInClubLeagueMatch}(ClubMatchId,JoinInLeagueMatchClubs) values ({model.ClubMatchId},@json)
                            else
                                update {tb_JoinInClubLeagueMatch} set JoinInLeagueMatchClubs=@json where ClubMatchId = {model.ClubMatchId}";
            List<ISqlAloneParameter<SqlDbType>> sqlpms = new List<ISqlAloneParameter<SqlDbType>>();
            sqlpms.Add(new SqlAloneParameter("@json", model.GetJoinInLeagueMatchClubsToString(),5000, SqlDbType.VarChar));
            GameDB.Instance.TransactionAddSql(spp, sql, sqlpms);

            if (!GameDB.Instance.SubTransaction(spp))
                return false;
            
            return true;
        }

        /// <summary>
        /// 获取 保存加入到联赛的俱乐部集合 对象
        /// </summary>
        /// <param name="clubMatchId"></param>
        /// <returns></returns>
        public static JoinInClubLeagueMatch GetJoinInClubLeagueMatch(int clubMatchId)
        {
            var sql = $"select top 1 * from {tb_JoinInClubLeagueMatch} where clubMatchId = {clubMatchId}";
            Debug.Info(sql);
            return sqldb.ExecuteEntities(sql, p =>
            {
                var model = p.ToObject<JoinInClubLeagueMatch>();
                model.ClubIds = model.GetClubIdsByJSON();
                return model;
            }).FirstOrDefault();
        }
    }
}