using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using Sog;
using ProtoCSStruct;
using MySql.Data.MySqlClient;
using LitJson;
using SimpleHttpServer;
using Sog.IO;

namespace Operation
{
    public class MySqlDBOperator : DBOperator
    {
        private MySqlDB db;
        private const int BuffLengthMax = 500 * 1024;

        public MySqlDBOperator(string dbname, string ip, string user, string password)
        {
            db = new MySqlDB(dbname, ip, user, password);
        }

        //销毁的时候置空
        public override void Dispose()
        {
            db.Dispose();
            db = null;
        }

        public override void KeepAlive()
        {
            string strSql = "select Id from mail_box where Id=0";

            MySqlDataReader reader = db.ExecReader(strSql);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.KeepAlive no record in account table, return reader is null!");
                return;
            }

            //记住一定要Close
            reader.Close();

            TraceLog.Trace("MySqlDBOperator.KeepAlive exec at {0}", DateTime.Now);
        }

        public override int SelectAccount(JsonData builder, string account)
        {
            string sql = "";
            if (!string.IsNullOrEmpty(account))
            {
                sql = string.Format("select * from account where account='{0}'", account);
            }
            else
            {
                sql = "select * from account";
            }

            var reader = db.ExecReader(sql);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBDelAccount 连接数据库异常");
                builder["ret"] = 6;
                builder["msg"] = "error";
                return -1;
            }

            List<Account> admins = new List<Account>();
            while (reader.Read())
            {
                Account admin = new Account();
                admin.account = reader.GetString("account");
                admin.name = reader.GetString("name");
                admin.valid = reader.GetInt32("valid");
                admin.lastLoginTime = reader.GetString("lastLoginTime");
                admin.cTime = reader.GetString("createTime");
                admins.Add(admin);
            }

            reader.Close();
            foreach (var admin in admins)
            {
                var roles = AdminSvc.GetRoles(admin.account);
                if (roles != null)
                {
                    admin.roles.AddRange(roles);
                }
            }

            JsonData data = new JsonData();
            foreach (var admin in admins)
            {
                data.Add(admin.ToJson());
            }

            builder["data"] = data;
            return 0;
        }

        public override void CreateAccount(JsonData jsondata, string account, string name, string password)
        {
            string sql = string.Format("select * from account where account='{0}'", account);
            MySqlDataReader reader = db.ExecReader(sql);
            if (reader == null)
            {
                jsondata["ret"] = 6;
                jsondata["msg"] = "创建账号失败";
                return;
            }

            if (reader.HasRows)
            {
                reader.Close();
                jsondata["ret"] = 6;
                jsondata["msg"] = "账号已存在";
                return;
            }

            reader.Close();

            DateTime now = DateTime.Now;
            var nowStr = now.ToString("yyyy-MM-dd HH:mm:ss");
            string insert = string.Format(
                "insert into account set account='{0}',password='{1}',name='{2}',createTime='{3}'", account,
                password, name, nowStr);
            reader = db.ExecReader(insert);
            if (reader == null)
            {
                jsondata["ret"] = 6;
                jsondata["msg"] = "创建账号失败";
                return;
            }

            reader.Close();
        }

        public override void DBDelAccount(string account, JsonData jsondata)
        {
            string Sqlfoundaccount = string.Format("select account from account");
            MySqlDataReader reader = db.ExecReader(Sqlfoundaccount);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBDelAccount 连接数据库异常");
                jsondata["ret"] = 6;
                jsondata["msg"] = "error";
                return;
            }

            jsondata["data"] = new JsonData();
            while (reader.Read())
            {
                JsonData one = new JsonData();
                one.Add(reader.GetString("account"));
                jsondata["data"].Add(one);
            }

            reader.Close();

            if (account != null)
            {
                //删除Account
                string deleteSql = string.Format("delete from account where account='{0}'", account);
                reader = db.ExecReader(deleteSql);
                if (reader == null)
                {
                    TraceLog.Trace("MySqlDBOperator.DBDelAccount 连接数据库异常");
                    jsondata["ret"] = 6;
                    jsondata["msg"] = "error";
                    return;
                }

                reader.Close();

                //检查是否删除成功
                string selectSql = string.Format("select * from account where account='{0}'", account);
                reader = db.ExecReader(selectSql);
                if (reader == null)
                {
                    TraceLog.Trace("MySqlDBOperator.DBDelAccount 连接数据库异常");
                    jsondata["ret"] = 6;
                    jsondata["msg"] = "error";
                    return;
                }

                if (reader.HasRows == false)
                {
                    jsondata["ret"] = 3;
                    jsondata["msg"] = "delete success";
                }

                reader.Close();
                return;
            }

            jsondata["ret"] = 3;
            jsondata["msg"] = "delete success";
            return;
        }

        public override void DBLogin(string account, string passwd, JsonData builder)
        {
            string strSql = string.Format("select password, jurisdiction, valid from account where account = \"{0}\"",
                account);

            MySqlDataReader reader = db.ExecReader(strSql); //运行sql语句得出返回值给reader
            if (reader == null)
            {
                builder["ret"] = 6;
                builder["msg"] = "系统错误";
                return;
            }

            bool ret = reader.Read();
            if (ret == false)
            {
                builder["ret"] = 3;
                builder["msg"] = "无效的用户名或密码";
                reader.Close();
                return;
            }

            string dbpasswd = reader.GetString("password");
            int dbvalid = reader.GetInt32("valid");
            string auth = reader.GetString("jurisdiction");

            reader.Close(); //关掉reader

            if (dbpasswd != passwd||dbvalid != 1)
            {
                builder["ret"] = 3;
                builder["msg"] = "无效的用户名或密码";
                return;
            }

            long nowTime = OperationServerUtils.GetTimeSecond(); //获取时间戳

            string str = nowTime.ToString() + account +
                         OperationServerUtils.GetOperationServerData().m_app.Rand.NextDouble().ToString();
            string token = str;

            if (auth != "")
            {
                builder["auth"] = auth;
            }

            TraceLog.Trace("MySqlDBOperator.DBLogin account {0} token {1}", account, token);

            builder["ret"] = 0;
            builder["token"] = token;
            builder["account"] = account;

            var tokenList = OperationServerUtils.GetOperationServerData().m_tokenList;
            var accountList = OperationServerUtils.GetOperationServerData().m_accountList;

            if (tokenList.ContainsKey(token)) //键值里面如果没key
            {
                tokenList[token] = nowTime;
                accountList[token] = account;
            }
            else
            {
                tokenList.Add(token, nowTime);
                accountList.Add(token, account);
            }

            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?account", MySqlDbType.VarChar) { Value = account },
                new MySqlParameter("?lastLoginTime", MySqlDbType.DateTime) { Value = DateTime.Now },

            }.ToArray();

            try
            {
                string editSql = $"UPDATE account SET lastLoginTime=?lastLoginTime WHERE account=?account;";

                db.ExecNonQuery(editSql, MySqlParameters);
            }
            catch (Exception)
            {
                TraceLog.Error("MySqlDBOperator.DBLogin account {0} : 更新写入数据库错误", account);
            }

            TraceLog.Debug("MySqlDBOperator.DBLogin account {0} success", account);

            return;
        }

        public override void DBCheckAuth(string account, JsonData builder)
        {
            string strSql = string.Format("select jurisdiction from account where account = \"{0}\"", account);

            MySqlDataReader reader = db.ExecReader(strSql); //运行sql语句得出返回值给reader
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBCheckAuth 数据库连接异常");
                builder["ret"] = 6;
                builder["msg"] = "error";
                return;
            }

            if (reader.HasRows == false)
            {
                TraceLog.Trace("MySqlDBOperator.DBCheckAuth 无效的用户名或密码");
                builder["ret"] = 3;
                builder["msg"] = "无效的用户名或密码";
                reader.Close(); //关掉reader
                return;
            }

            string auth = reader.GetString("jurisdiction");

            reader.Close(); //关掉reader

            builder["auth"] = auth;
            builder["ret"] = 0;

            return;
        }

        public override void DBDeleteNotice(JsonData Jsondata, string id, string noticeId)
        {
            string sqlStr = "SELECT * from notice ";
            bool isNotice = false;

            if (!String.IsNullOrEmpty(id))
            {
                sqlStr += " WHERE id = " + id;
            }

            if (!String.IsNullOrEmpty(noticeId))
            {
                sqlStr += " WHERE noticeId = " + noticeId;
                isNotice = true;
            }

            MySqlDataReader reader = db.ExecReader(sqlStr);

            //通知chat删除
            while (reader.Read())
            {
                var Id = reader.GetInt32("id");
                var popUp = reader.GetInt32("popUp");
                var realmlist = reader.GetString("realmlist");
                SSSysNoticeDel notice = new SSSysNoticeDel();
                notice.Id = Id;
                notice.Noticetype = popUp == 1 ? NoticeType.Popup : NoticeType.Click;

                // 改为发送到不同 version
                OperationServerUtils.GetPacketSender().Broadcast((int)ServerType.Version,
                    (int)SSGameMsgID.ChatDelNotice, ref notice, 0, 0);

                OperationServerUtils.SendToWorld((int)SSGameMsgID.ChatDelNotice, ref notice, 0, 0, realmlist);
            }

            reader.Close();

            if (!isNotice)
            {
                //通知数据库删除
                var MySqlParameters = new List<MySqlParameter>()
                {
                    new MySqlParameter("?id", MySqlDbType.Int32) { Value = id }
                }.ToArray();

                string editSql = $"DELETE FROM notice WHERE  id=?id;";
                db.ExecNonQuery(editSql, MySqlParameters);
            }
            else
            {
                //通知数据库删除
                var MySqlParameters = new List<MySqlParameter>()
                {
                    new MySqlParameter("?noticeId", MySqlDbType.Int32) { Value = noticeId }
                }.ToArray();

                string editSql = $"DELETE FROM notice WHERE  noticeId=?noticeId;";
                db.ExecNonQuery(editSql, MySqlParameters);
            }

            Jsondata["ret"] = 0;
            Jsondata["msg"] = "操作成功";
        }

        public override void DBSendEmailCokie(JsonData Jsondata, HttpResponse rsp, HttpRequest request,
            HttpQueryParams query)
        {
            TraceLog.Trace("MySqlDBOperator.DBSendEmailCokie url {0} ,param count {1}", request.Url, query.Count);

            string startDate = query.GetValue("startDate");
            string endDate = (query.GetValue("endDate") + " 23:59:59");
            string sqlStr =
                string.Format("select * from mail_box where(dater >= '{0}' and  dater <= '{1}') ORDER BY dater",
                    startDate, endDate);

            TraceLog.Trace("MySqlDBOperator.DBSendEmailCokie start {0} ,end {1}", startDate, endDate);

            MySqlDataReader reader = db.ExecReader(sqlStr);

            TraceLog.Trace("MySqlDBOperator.DBSendEmailCokie reader {0}", reader);
            if (reader == null)
            {
                TraceLog.Trace("数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return;
            }

            Jsondata["ret"] = 0;
            JsonData columnsArray = new JsonData();
            Jsondata["columns"] = columnsArray;
            columnsArray.Add("日期");
            columnsArray.Add("收件人");
            columnsArray.Add("标题");
            columnsArray.Add("内容");
            //columnsArray.Add("金币数量");
            //columnsArray.Add("钻石数量");
            columnsArray.Add("道具");
            columnsArray.Add("货币");
            //columnsArray.Add("道具数量");
            columnsArray.Add("状态");

            JsonData msgdata = new JsonData();
            Jsondata["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();
            while (reader.Read())
            {
                DateTime dateId1 = reader.GetDateTime("dater"); //格式为2017/1/11 19:30:49
                //TraceLog.Trace("dateId1 {0}", dateId1);
                String dateStr = dateId1.ToString("u").Substring(0, 19); //格式为2017-01-11 19:30:49
                //TraceLog.Trace("dater {0}", dateStr);
                string name = reader.GetString("name");
                //TraceLog.Trace("name {0}", name);
                string title = reader.GetString("title");
                string content = reader.GetString("content");
                //int chip = reader.GetInt32("chip");
                //int diamond = reader.GetInt32("diamond");
                string itemstr = reader.GetString("itemstr");
                string curstr = reader.GetString("curstr");
                // int itemCount1 = reader.GetInt32("itemCount1");
                int status = reader.GetInt32("status");

                JsonData linedata1 = new JsonData();
                linedata1.Add(dateStr);
                linedata1.Add(name);
                linedata1.Add(title);
                linedata1.Add(content);
                //linedata1.Add(chip);
                //linedata1.Add(diamond);
                linedata1.Add(itemstr);
                linedata1.Add(curstr);
                linedata1.Add(status);

                msgdata.Add(linedata1);
            }

            TraceLog.Trace("MySqlDBOperator.DBSendEmailCokie success");
            return;
        }

        public override void DBChangeAuthority(string account, string jurisdiction, JsonData Jsondata)
        {
            string strSql = string.Format("update account set jurisdiction='{0}' where account='{1}'", jurisdiction,
                account);
            MySqlDataReader reader = db.ExecReader(strSql); //运行sql语句得出返回值给reader

            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBChangeAuthority 数据库连接异常");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "errror";
                return;
            }

            reader.Close();

            string Sql = string.Format("select jurisdiction from account where account = \"{0}\"", account);
            MySqlDataReader readr = db.ExecReader(Sql); //运行sql语句得出返回值给reader
            if (readr == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBChangeAuthority 数据库连接异常");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "errror";
                return;
            }

            string[] juris = new string[account.Length];

            while (readr.Read())
            {
                for (var i = 0; i < account.Length; i++)
                {
                    juris[i] = readr.GetString("jurisdiction");
                    if (jurisdiction == juris[i])
                    {
                        Jsondata["ret"] = 0;
                        Jsondata["msg"] = "权限更改成功！";
                    }
                }
            }

            readr.Close();
        }

        public override bool DBSelectMailWithRule(int id, bool isDiscard, ref SSGetMailWithRuleRes res)
        {
            //TickMailBoxTemp();
            // 计算日期 
            TimeSpan ts = DateTime.Now - new DateTime(1970, 1, 1, 0, 0, 0);
            int nowTime = Convert.ToInt32(ts.TotalMinutes); //获取当前时间戳

            //string strSql = $"select * from mail_box where dater >= '{Dt}'";
            string strSql = "";
            if (isDiscard)
            {
                int timebef = nowTime - (15 * 24 * 60); //过去15天之内的
                DateTime startTime = new DateTime(1970, 1, 1, 0, 0, 0);
                string Dt = startTime.AddMinutes(timebef).ToString("yyyy-MM-dd HH:mm:ss");
                strSql = $"select * from mail_box where dater >='{Dt}'  and status=4";
            }
            else
            {
                string nowTimeStr = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                strSql =
                    $"select * from mail_box where (datee>='{nowTimeStr}' or timeLimit>0) and dater <='{nowTimeStr}' and Id > '{id}' and status=0"; //邮件再有效期内
            }

            strSql += " order by Id asc";

            TraceLog.Debug("MySqlDBOperator.DBSelectMailWithRule sqlStr:{0}", strSql);

            MySqlDataReader reader = db.ExecReader(strSql);
            if (reader == null)
            {
                TraceLog.Error("MySqlDBOperator.DBSelectMailWithRule error: return reader is null!");
                return false;
            }

            if (!reader.HasRows)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectMailWithRule DB has no record");
                reader.Close();
                return false;
            }

            try
            {
                while (reader.Read())
                {
                    if (res.RuleMail.Count >= 16)
                    {
                        TraceLog.Debug("MySqlDBOperator.DBSelectMailWithRule res.RuleMail.Count = {0}",
                            res.RuleMail.Count);
                        break;
                    }

                    var mail = new SSSendMailWithRule();
                    MailStatus status = (MailStatus)reader.GetInt32("status");
                    mail.Seq = reader.GetInt32("Id");

                    DateTime insertTime = reader.GetDateTime("dater"); //dater是邮件插入mail_box的时间
                    DateTime DateStart = new DateTime(1970, 1, 1, 0, 0, 0);
                    int lastDateTime = Convert.ToInt32((insertTime - DateStart).TotalMinutes); //获取最后一组数据的时间戳
                    int timeInterval = nowTime - lastDateTime;

                    int timeLimit = reader.GetInt32("timeLimit"); //分钟
                    if (timeLimit <= 0)
                    {
                        // 邮件有效期
                        DateTime endTime = reader.GetDateTime("datee");
                        timeLimit = Convert.ToInt32((endTime - DateTime.Now).TotalMinutes);
                    }
                    else
                    {
                        int dayMinute = 24 * 60;
                        if (timeLimit < dayMinute) //最小 1 天
                        {
                            timeLimit = dayMinute;
                        }

                        if (timeLimit > 15 * dayMinute) //最长15天
                        {
                            timeLimit = 15 * dayMinute;
                        }
                    }


                    // 拉取还在有效时间内的邮件,要减掉已经过去的时间
                    timeInterval = (int)(timeInterval / 60);
                    mail.RuleParam1 = timeLimit - timeInterval > 0 ? (timeLimit - timeInterval) : 0;
                    if (mail.RuleParam1 == 0)
                    {
                        TraceLog.Debug(
                            "MySqlDBOperator.DBSelectMailWithRule mail.RuleParam1 == 0 continue, mailSeq {0} timeLimit {1} timeInterval {2}",
                            mail.Seq, timeLimit, timeInterval);
                        continue;
                    }

                    // 发送类型
                    string strName = reader.GetString("name");
                    if (strName == "all")
                    {
                        mail.RuleType = (int)SendMailRuleType.All;
                    }
                    else if (strName == "ios")
                    {
                        mail.RuleType = (int)SendMailRuleType.Ios;
                    }
                    else if (strName == "android")
                    {
                        mail.RuleType = (int)SendMailRuleType.Android;
                    }
                    else
                    {
                        mail.RuleType = (int)SendMailRuleType.None;
                        mail.TargetUid.SetString(strName);
                    }

                    mail.Mail.MailType = 0;
                    //货币
                    string curstr = reader.GetString("curstr");
                    if (!string.IsNullOrWhiteSpace(curstr))
                    {
                        foreach (var lt in curstr.Split('#'))
                        {
                            var ln = lt.Split('|');

                            var curid = ln[0];
                            var curnum = ln[1].Toint32(0);
                            if (string.IsNullOrEmpty(curid) || curnum == 0)
                            {
                                break;
                            }

                            var currency = new TypeIDValueString
                            {
                                Type = (int)GoodsType.Items,
                                Id = new FixedStructString64(curid),
                                Value = curnum
                            };
                            mail.Mail.AddGoods.Add(ref currency);
                        }
                    }

                    //道具
                    string itemstr = reader.GetString("itemstr");
                    if (!string.IsNullOrWhiteSpace(itemstr))
                    {
                        foreach (var lt in itemstr.Split('#'))
                        {
                            var ln = lt.Split('|');

                            var itemid = ln[0];
                            var itemnum = ln[1].Toint32(0);
                            if (string.IsNullOrEmpty(itemid)|| itemnum == 0)
                            {
                                break;
                            }

                            var currency = new TypeIDValueString
                            {
                                Type = (int)GoodsType.Items,
                                Id = new FixedStructString64(itemid),
                                Value = itemnum
                            };
                            mail.Mail.AddGoods.Add(ref currency);
                        }
                    }
                    
                    //武器
                    string equipStr = reader.GetString("equipStr");
                    if (!string.IsNullOrWhiteSpace(equipStr))
                    {
                        foreach (var lt in equipStr.Split('#'))
                        {
                            var ln = lt.Split('|');

                            var equipid = ln[0];
                            var equipnum = ln[1].Toint32(0);
                            if (string.IsNullOrEmpty(equipid)|| equipnum == 0)
                            {
                                break;
                            }

                            var currency = new TypeIDValueString
                            {
                                Type = (int)GoodsType.Equipment,
                                Id = new FixedStructString64(equipid),
                                Value = equipnum
                            };
                            mail.Mail.AddGoods.Add(ref currency);
                        }
                    }

                    //自定义道具
                    string customItemStr = reader.GetString("customItemStr");
                    if (!string.IsNullOrWhiteSpace(customItemStr))
                    {
                        foreach (var custom in customItemStr.Split('#'))
                        {
                            var customItem = custom.Split('|');
                            if (customItem.Length != 3)
                            {
                                TraceLog.Error("MySqlDBOperator.DBSelectMailWithRule equipItem.Length != 3: continue!");
                                continue;
                            }

                            var customtype = customItem[0].Toint32(0);
                            var customId = customItem[1];
                            var customnum = customItem[2].Toint32(0);

                            // todo: 这里其实有问题, 有些东西不支持邮件发
                            if (customtype <= (int)GoodsType.None || customtype >= (int)GoodsType.Size)
                            {
                                TraceLog.Error(
                                    "MySqlDBOperator.DBSelectMailWithRule customtype:{0},customId:{1},customnum:{2}",
                                    customtype, customId, customnum);
                                break;
                            }

                            if (customnum == 0)
                            {
                                TraceLog.Error(
                                    "MySqlDBOperator.DBSelectMailWithRule customtype:{0},customId:{1},customnum:{2}",
                                    customtype, customId, customnum);
                                break;
                            }

                            var goods = new TypeIDValueString { Type = customtype, Id = new FixedStructString64(customId), Value = customnum };
                            mail.Mail.AddGoods.Add(ref goods);
                        }
                    }

                    string realmlist = reader.GetString("realmlist");
                    //服务器列表
                    if (realmlist.Contains(','))
                    {
                        var realms = realmlist.Split(',');
                        if (realms.Length > 6)
                        {
                            continue;
                        }

                        foreach (var _RealmID in realms)
                        {
                            int RealmID = Convert.ToInt32(_RealmID);
                            if (RealmID != 0)
                            {
                                mail.Mail.Realmlist.Add(RealmID);
                            }
                        }
                    }
                    else
                    {
                        if (realmlist != "" && realmlist != null)
                        {
                            int RealmID = Convert.ToInt32(realmlist);
                            if (RealmID != 0)
                            {
                                mail.Mail.Realmlist.Add(RealmID);
                            }
                        }
                    }

                    mail.Mail.MailID = reader.GetUInt32("Id");
                    mail.Mail.Title.SetString(reader.GetString("title"));
                    mail.Mail.Content.SetString(reader.GetString("content"));
                    mail.Mail.Language.SetString(reader.GetString("language"));
                    mail.Mail.Uuid.SetString(reader.GetString("uuid"));
                    mail.Mail.IsSendAllUser = reader.GetInt32("isSendToAll");
                    mail.Mail.Status = (uint)reader.GetInt32("status");

                    ExtMail ext = new ExtMail();
                    //请封装
                    int gameDataIndex = reader.GetOrdinal("data");
                    if (!reader.IsDBNull(gameDataIndex))
                    {
                        byte[] buffer = new byte[50 * 1024];
                        long gameDataLength = reader.GetBytes(gameDataIndex, 0, buffer, 0, buffer.Length);
                        byte[] gameDataByte = new byte[gameDataLength];
                        Buffer.BlockCopy(buffer, 0, gameDataByte, 0, (int)gameDataLength);

                        StructMessageParseUtils.ParseFrom(ref ext, gameDataByte);
                    }

                    // 有数据
                    if (ext.Havedata)
                    {
                        for (int i = 0; i < ext.LangContentList.Count; i++)
                        {
                            mail.LangContent.Add(ext.LangContentList[i]);
                        }

                        mail.Mail.ApkVersion.SetString(ext.VersionParam.GetString());
                        mail.Mail.PlayerCreateTimeLimit = ext.PlayerCreateTimeLimit;
                        mail.RealmStr = ext.RealmStr;
                        //timeParamStr = ext.TimeParamStr.GetString();
                    }

                    res.RuleMail.Add(ref mail);

                    TraceLog.Trace(
                        "MySqlDBOperator.DBSelectMailWithRule mailUuid {0} mailId {1} name {2} title {3} content {4} status {5} expirTime {6} sendTime {7} isSendAllUser {8}" +
                        " seg {9} realm {10} Language {11} coin {12} item {13} customItem {14}",
                        mail.Mail.Uuid, mail.Mail.MailID, strName, mail.Mail.Title, mail.Mail.Content,
                        mail.Mail.Status, mail.Mail.ExpirationTime, mail.Mail.SendTime, mail.Mail.IsSendAllUser,
                        mail.Seq, mail.RealmStr, mail.Mail.Language, curstr, itemstr, customItemStr);
                }
            }
            catch (Exception ex)
            {
                if (reader != null)
                    reader.Close();

                TraceLog.Error("MySqlDBOperator.DBSelectMailWithRule error:{0}!", ex.Message);
            }

            reader.Close();
            return true;
        }

        public override bool DBUpdateMailStatus(int newStatus, int id, string uuid)
        {
            string sqlStr = $"update mail_box set status={newStatus} where uuid='{uuid}' and Id={id} ";
            MySqlDataReader reader = db.ExecReader(sqlStr);
            if (reader == null)
            {
                TraceLog.Error(
                    "MySqlDBOperator.DBUpdateMailStatus  record id:{0} in table mail_box , return reader is null!", id);
                return false;
            }

            reader.Close();
            return true;
        }

        public override int DBSelectMaxIdPlayerOp()
        {
            int ret = 0;
            string sqlCount = "select id from player_op order by id desc limit 1";
            MySqlDataReader reader = db.ExecReader(sqlCount);
            if (reader == null)
            {
                TraceLog.Error("MySqlDBOperator.DBSelectMaxIdPlayerOp reader == null!");
                return ret;
            }

            try
            {
                while (reader.Read())
                {
                    ret = reader.GetInt32("id");
                    break;
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                reader.Close();
                TraceLog.Error("MySqlDBOperator.DBSelectMaxIdPlayerOp reader.Read error {0}!", ex.Message);
            }

            return ret;
        }

        public override List<DBPlayerDataOp> DBSelectPlayOpData(long uid, long currentMaxId)
        {
            List<DBPlayerDataOp> retList = new List<DBPlayerDataOp>();
            string sqlStr = $"select * from player_op where uid={uid} and Id > {currentMaxId} ";
            MySqlDataReader reader = db.ExecReader(sqlStr);
            if (reader == null)
            {
                TraceLog.Error("MySqlDBOperator.DBSelectPlayOpData reader == null!");
                return retList;
            }

            try
            {
                while (reader.Read())
                {
                    DBPlayerDataOp dBPlayerDataOp = new DBPlayerDataOp();
                    dBPlayerDataOp.Id = reader.GetInt32("Id");
                    dBPlayerDataOp.Type = (OpPlayDataHandleType)reader.GetInt32("type");
                    dBPlayerDataOp.OpType = reader.GetInt32("opType");
                    dBPlayerDataOp.OpId = reader.GetInt32("opId");
                    dBPlayerDataOp.OpNum = reader.GetInt32("opNum");
                    dBPlayerDataOp.UniqueId = (reader.GetString("uniqueId").Tolong());

                    ExtPlayerOp exdata = new ExtPlayerOp();
                    int contentIndex = reader.GetOrdinal("exData");

                    byte[] buffer = new byte[BuffLengthMax];
                    long contentLength = reader.GetBytes(contentIndex, 0, buffer, 0, buffer.Length);
                    byte[] contentByte = new byte[contentLength];
                    Buffer.BlockCopy(buffer, 0, contentByte, 0, (int)contentLength);
                    StructMessageParseUtils.ParseFrom(ref exdata, contentByte);
                    if (exdata.Havedata)
                    {
                        /// 前端修改处理一下

                    }

                    retList.Add(dBPlayerDataOp);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                reader.Close();
                TraceLog.Error("MySqlDBOperator.DBSelectPlayOpData reader.Read error {0}!", ex.Message);
            }

            return retList;
        }

        public override void DBInsertBanUser(ref SSGMSetFreezeTimeRes res)
        {
            long strbantim = res.FreezeTime;
            long struidd = res.Uid;
            int freezeReason = (int)res.FreezeReason;
            string freezeReasonStr = res.FreezeReasonStr.GetString();

            string strbantime = AppTime.ConvertUnixTimeToDateTime(strbantim * 1000).ToString("u").Substring(0, 19);
            string struid = struidd.ToString();

            string sql =
                "insert into ban_box set dater=?dater,UID=?UID,banDay=?banDay,type=1,reasonType=?reasonType,otherReason=?otherReason;";

            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?dater", MySqlDbType.DateTime) { Value = DateTime.Now },
                new MySqlParameter("?UID", MySqlDbType.VarChar) { Value = struid },
                new MySqlParameter("?banDay", MySqlDbType.VarChar) { Value = strbantime },
                new MySqlParameter("?reasonType", MySqlDbType.Int32) { Value = freezeReason },
                new MySqlParameter("?otherReason", MySqlDbType.VarChar) { Value = freezeReasonStr },
            }.ToArray();

            try
            {
                MySqlDataReader readr = db.ExecReader(sql, MySqlParameters);
                if (readr != null)
                {
                    readr.Close();
                }
            }
            catch (Exception)
            {
                TraceLog.Error("MySqlDBOperator.DBInsertBanUser sql {0} failed", sql);
            }
        }

        public override void DBInsertBanChat(ref SSEnableSendMsgRes res)
        {
            long strbantim = res.EnableTime;
            long struidd = res.Uid;

            string strbantime = AppTime.ConvertUnixTimeToDateTime(strbantim * 1000).ToString("u").Substring(0, 19);
            string struid = struidd.ToString();

            string sql = "insert into ban_box set dater=?dater,UID=?UID,banDay=?banDay,type=2;";

            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?dater", MySqlDbType.DateTime) { Value = DateTime.Now },
                new MySqlParameter("?UID", MySqlDbType.VarChar) { Value = struid },
                new MySqlParameter("?banDay", MySqlDbType.VarChar) { Value = strbantime },

            }.ToArray();

            try
            {
                MySqlDataReader readr = db.ExecReader(sql, MySqlParameters);
                if (readr != null)
                {
                    readr.Close();
                }
            }
            catch (Exception ex)
            {
                TraceLog.Error("MySqlDBOperator.DBInsertBanChat sql {0} failed : {1}", sql, ex.Message);
            }
        }

        public override void DBNoticeBackgrounds(string language, ref SSNoticeBackGroundsRes res)
        {
            TraceLog.Debug("MySqlDBOperator.DBNoticeBackgrounds: Language:{0}", language);

            string sqlStr = "SELECT * from noticebackgrounds ";
            if (language != "")
            {
                sqlStr += " WHERE language = '" + language + "' and state = 1";
            }

            MySqlDataReader reader = db.ExecReader(sqlStr);

            try
            {
                while (reader.Read())
                {
                    var picObj = new NoticeBackGrounds();

                    picObj.Language.SetString(reader.GetString("language"));
                    picObj.Pic.SetString(OperationServerUtils.GetNewPicUrl(reader.GetString("pic")));

                    res.PicList.Add(picObj);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                reader.Close();
                TraceLog.Error("MySqlDBOperator.DBNoticeBackgrounds reader.Read error {0}!", ex.Message);
            }
        }

        public override void DBDeleteNoticeBackgrounds(int id)
        {
            TraceLog.Debug("MySqlDBOperator.DBDeleteNoticeBackgrounds: id:{0}", id);

            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?id", MySqlDbType.Int32) { Value = id }

            }.ToArray();

            string editSql = $"DELETE FROM noticebackgrounds WHERE id=?id;";

            TraceLog.Debug("MySqlDBOperator.DBDeleteNoticeBackgrounds: sql:{0}", editSql);

            try
            {
                db.ExecNonQuery(editSql, MySqlParameters);
            }
            catch (Exception e)
            {
                TraceLog.Error("MySqlDBOperator.DBDeleteNoticeBackgrounds error : {0}", e.Message);
                return;
            }
        }

        public override void DBSelectNoticeBackGrounds(string language, string id, JsonData Jsondata)
        {
            string sqlStr = "SELECT * from noticebackgrounds ";

            if (!String.IsNullOrEmpty(language))
            {
                sqlStr += " WHERE language= '" + language + "'";
            }

            if (!String.IsNullOrEmpty(id))
            {
                sqlStr += " WHERE id = " + id;
            }

            MySqlDataReader reader = db.ExecReader(sqlStr);

            if (reader == null)
            {
                TraceLog.Error("MySqlDBOperator.DBSelectNoticeBackGrounds : reader is null!");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "数据库连接失败";
                return;
            }

            JsonData msgdata = new JsonData();
            Jsondata["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();

            try
            {
                while (reader.Read())
                {
                    JsonData linedata1 = new JsonData();
                    linedata1["id"] = reader.GetInt32("id").ToString();
                    linedata1["language"] = reader.GetString("language");
                    linedata1["pic"] = reader.GetString("pic");
                    linedata1["startTime"] = reader.GetDateTime("startTime").ToString("yyyy-MM-dd HH:mm:ss");
                    ;
                    linedata1["endTime"] = reader.GetDateTime("endTime").ToString("yyyy-MM-dd HH:mm:ss");
                    ;
                    linedata1["state"] = reader.GetInt32("state").ToString();
                    linedata1["valid"] =
                        ExComm.CheckDateTime(reader.GetDateTime("startTime"), reader.GetDateTime("endTime"));

                    msgdata.Add(linedata1);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                reader.Close();
                TraceLog.Error("MySqlDBOperator.DBSelectNoticeBackGrounds reader.Read error {0}!", ex.Message);
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "数据库数据失败";
                return;
            }

            Jsondata["ret"] = 0;
        }

        public override void DBUpdateNoticeBackGrounds(string language, string pic, int id, int state, string startTime,
            string endTime, JsonData Jsondata)
        {
            try
            {
                var MySqlParameters = new List<MySqlParameter>()
                {
                    new MySqlParameter("?id", MySqlDbType.Int32) { Value = id },
                    new MySqlParameter("?state", MySqlDbType.Int32) { Value = state },
                    new MySqlParameter("?language", MySqlDbType.VarChar) { Value = language },
                    new MySqlParameter("?pic", MySqlDbType.VarChar) { Value = pic },
                    new MySqlParameter("?startTime", MySqlDbType.DateTime) { Value = startTime },
                    new MySqlParameter("?endTime", MySqlDbType.DateTime) { Value = endTime },
                };

                string editSql =
                    $"INSERT INTO noticebackgrounds(language,pic,state,startTime,endTime) VALUES(?language,?pic,?state,?startTime,?endTime);";

                if (id != 0)
                {
                    editSql =
                        $"UPDATE noticebackgrounds SET language=?language,pic=?pic,state=?state,startTime=?startTime,endTime=?endTime WHERE id=?id ;";
                }

                TraceLog.Trace("MySqlDBOperator.DBUpdateNoticeBackGrounds sql {0}", editSql);

                db.ExecNonQuery(editSql, MySqlParameters.ToArray());
            }
            catch (Exception e)
            {
                TraceLog.Error("MySqlDBOperator.DBUpdateNoticeBackGrounds error : {0}", e.Message);
                Jsondata["ret"] = 3;
                Jsondata["msg"] = "数据库操作错误！";
                return;
            }
        }

        public override bool DBUpdateNoticeBackGroundsState(int id, int state)
        {
            try
            {
                var MySqlParameters = new List<MySqlParameter>()
                {
                    new MySqlParameter("?id", MySqlDbType.Int32) { Value = id },
                    new MySqlParameter("?state", MySqlDbType.Int32) { Value = state },

                }.ToArray();

                string editSql = $"UPDATE noticebackgrounds SET state=?state WHERE id=?id ;";

                TraceLog.Trace("MySqlDBOperator.DBUpdateNoticeBackGroundsState sql {0}", editSql);

                db.ExecNonQuery(editSql, MySqlParameters);
            }
            catch (Exception e)
            {
                TraceLog.Error("MySqlDBOperator.DBUpdateNoticeBackGroundsState error :", e.Message);
                return false;
            }

            return true;
        }

        public override void BanNotice(int noticeId, int state, JsonData Jsondata)
        {
            string sqlStr = $"SELECT * from notice WHERE noticeId={noticeId}";

            MySqlDataReader reader = db.ExecReader(sqlStr);
            bool isData = false;

            //通知chat删除 禁用公告
            while (reader.Read())
            {
                var Id = reader.GetInt32("id");
                var popUp = reader.GetInt32("popUp");
                var type = reader.GetInt32("noticeType");
                var realmlist = reader.GetString("realmlist");

                if (state == 0)
                {
                    SSSysNoticeDel notice = new SSSysNoticeDel();

                    notice.Id = Id;
                    notice.Noticetype = popUp == 1 ? NoticeType.Popup : NoticeType.Click;

                    //if (type == 1)
                    {//删除时通知全部服务器
                        OperationServerUtils.GetPacketSender().Broadcast((int)ServerType.Version,
                            (int)SSGameMsgID.ChatDelNotice, ref notice, 0, 0);
                    }
                    //else
                    {
                        OperationServerUtils.SendToWorld((int)SSGameMsgID.ChatDelNotice, ref notice, 0, 0, realmlist);
                    }
                }

                isData = true;
            }

            if (reader != null)
            {
                reader.Close();
            }

            if (isData)
            {
                string sqlStr1 = $"UPDATE notice SET state={state} WHERE noticeId={noticeId}";

                try
                {
                    reader = db.ExecReader(sqlStr1);
                }
                catch (Exception ex)
                {
                    if (reader != null)
                        reader.Close();

                    TraceLog.Error("MySqlDBOperator.BanNotice db noticeId{0} - error:{1}", noticeId, ex.Message);
                    Jsondata["ret"] = 1;
                    Jsondata["msg"] = "操作错误，数据库修改失败！";
                }

                if (reader != null)
                    reader.Close();
            }

            Jsondata["ret"] = 0;
            Jsondata["msg"] = "操作成功！";
        }

        public override void DBSelectBanUser(int type, string startDate, string endDate, JsonData Jsondata)
        {
            TraceLog.Trace("MySqlDBOperator.DBSelectBanUser type {0} startDate {1} endDate {2}", type, startDate,
                endDate);

            string sqlStr =
                string.Format(
                    "select * from ban_box where type={2} and (dater >= '{0}' and  dater <= '{1}') ORDER BY dater DESC ",
                    startDate, endDate, type);

            MySqlDataReader reader = db.ExecReader(sqlStr);

            TraceLog.Trace("MySqlDBOperator.DBSelectBanUser sqlStr {0}", sqlStr);

            if (reader == null)
            {
                TraceLog.Trace("数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return;
            }

            Jsondata["ret"] = 0;

            JsonData msgdata = new JsonData();
            Jsondata["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();

            while (reader.Read())
            {
                DateTime dateId1 = reader.GetDateTime("dater"); //格式为2017/1/11 19:30:49
                //TraceLog.Trace("dateId1 {0}", dateId1);
                String dateStr = dateId1.ToString("u").Substring(0, 19); //格式为2017-01-11 19:30:49
                //TraceLog.Trace("dater {0}", dateStr);
                string uid = reader.GetString("UID");
                //TraceLog.Trace("name {0}", name);
                string banDay = reader.GetString("banDay");
                int reason = reader.GetInt32("reasonType");
                string reasonStr = reader.GetString("otherReason");
                JsonData linedata1 = new JsonData();
                linedata1["dateStr"] = dateStr;
                linedata1["uid"] = uid;
                linedata1["banDay"] = banDay;
                linedata1["reasonType"] = reason;
                if (reasonStr.Contains("op:"))
                {
                    linedata1["admin"] = reasonStr.Split("op:")[1];
                }
                else
                {
                    linedata1["otherStr"] = reasonStr;
                }

                msgdata.Add(linedata1);
            }

            reader.Close();
            TraceLog.Trace("MySqlDBOperator.DBSelectBanUser success");

            return;
        }

        public override bool DBSelectNotice(int noticeId, JsonData Jsondata)
        {
            TraceLog.Trace("MySqlDBOperator.DBSelectNotice noticeId {0}", noticeId);

            string sqlStr = "SELECT * from notice ";

            if (noticeId > 0)
            {
                sqlStr += " WHERE noticeId = " + noticeId;
            }
            else
            {
                sqlStr += ";";
            }

            MySqlDataReader reader = db.ExecReader(sqlStr);

            try
            {
                if (reader == null)
                {
                    Jsondata["ret"] = 6;
                    Jsondata["msg"] = "数据库链接失败";
                    return false;
                }

                if (reader.HasRows)
                {
                    Jsondata["ret"] = 8;
                    Jsondata["msg"] = "noticeId 已经存在";
                    reader.Close();
                    return false;
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null)
                    reader.Close();

                TraceLog.Error("MySqlDBOperator.DBSelectNotice db noticeId{0} - error:{1}", noticeId, ex.Message);
                Jsondata["ret"] = 1;
                Jsondata["msg"] = "操作错误，数据库查询失败！";
                return false;
            }

            return true;
        }

        public override void DBUpdateNotice(Dictionary<string, string> from, byte[] dataByte, JsonData Jsondata)
        {
            TraceLog.Debug("MySqlDBOperator.DBUpdateNotice: fromCount:{0}", from.Count);

            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?id", MySqlDbType.Int32) { Value = from["id"] },
                new MySqlParameter("?state", MySqlDbType.Int32) { Value = from["state"] },
                new MySqlParameter("?startTime", MySqlDbType.DateTime) { Value = from["startTime"] },
                new MySqlParameter("?endTime", MySqlDbType.DateTime) { Value = from["endTime"] },
                new MySqlParameter("?title", MySqlDbType.VarChar) { Value = from["title"] },
                new MySqlParameter("?content", MySqlDbType.VarChar) { Value = from["content"] },
                new MySqlParameter("?icon", MySqlDbType.VarChar) { Value = from["icon"] },
                new MySqlParameter("?pic", MySqlDbType.VarChar) { Value = from["pic"] },
                new MySqlParameter("?language", MySqlDbType.VarChar) { Value = from["language"] },
                new MySqlParameter("?jump", MySqlDbType.Int32) { Value = from["jump"].Toint32(0) },
                new MySqlParameter("?popUp", MySqlDbType.Int32) { Value = from["popUp"] },
                new MySqlParameter("?sort", MySqlDbType.Int32) { Value = from["sort"] },
                new MySqlParameter("?link", MySqlDbType.VarChar) { Value = from["link"] },
                new MySqlParameter("?realmlist", MySqlDbType.VarChar) { Value = from["realmlist"] },
                new MySqlParameter("?triggertype", MySqlDbType.Int32) { Value = from["triggertype"].Toint32(0) },
                new MySqlParameter("?startTimeDay", MySqlDbType.Int32) { Value = from["startTimeDay"].Toint32(0) },
                new MySqlParameter("?startTimefine", MySqlDbType.VarChar) { Value = from["startTimefine"] },
                new MySqlParameter("?endTimeDay", MySqlDbType.Int32) { Value = from["endTimeDay"].Toint32(0) },
                new MySqlParameter("?endTimefine", MySqlDbType.VarChar) { Value = from["endTimefine"] },
                new MySqlParameter("?dungeonid", MySqlDbType.Int32) { Value = from["dungeonid"].Toint32(0) },
                new MySqlParameter("?Intervaldays", MySqlDbType.Int32) { Value = from["Intervaldays"].Toint32(0) },
                new MySqlParameter("?isshowtimetext", MySqlDbType.Int32) { Value = from["isshowtimetext"].Toint32(0) },
                new MySqlParameter("?aotupop", MySqlDbType.Int32) { Value = from["aotupop"] },
                new MySqlParameter("?Giftbagcustoms", MySqlDbType.Int32) { Value = from["Giftbagcustoms"] },
                new MySqlParameter("?data", MySqlDbType.Blob) { Value = dataByte },
                new MySqlParameter("?noticeId", MySqlDbType.Int32) { Value = from["noticeId"] },
                new MySqlParameter("?noticeType", MySqlDbType.Int32) { Value = from["noticeType"] },
                new MySqlParameter("?jumpParam", MySqlDbType.Int32) { Value = from["jumpParam"].Toint32(0) },
                new MySqlParameter("?customRealmStr", MySqlDbType.VarChar) { Value = from["customRealmStr"] },
                new MySqlParameter("?timeParamStr", MySqlDbType.VarChar) { Value = from["timeParamStr"] },
                new MySqlParameter("?areaNameList", MySqlDbType.VarChar) { Value = from["areaNameList"] },
                new MySqlParameter("?pageType", MySqlDbType.Int32) { Value = from["pageType"].Toint32(0) },
                new MySqlParameter("?seq", MySqlDbType.Int32) { Value = from["seq"].Toint32(0) },
            }.ToArray();


            if (from["id"].Toint32(0) != 0)
            {
                string editSql =
                    $"UPDATE notice SET state=?state,startTime=?startTime,endTime=?endTime,title=?title,content=?content,icon=?icon,pic=?pic,jump=?jump,jumpParam=?jumpParam,popUp=?popUp,sort=?sort,link=?link ,realmlist=?realmlist,triggertype=?triggertype,openserverstartday=?startTimeDay,openserverstarttime=?startTimefine,openserverendday=?endTimeDay,openserverendtime=?endTimefine,dungeonid=?dungeonid,updatetime=NOW(),Intervaldays=?Intervaldays,isshowtimetext=?isshowtimetext,aotupop=?aotupop,giftbagcustoms=?Giftbagcustoms,data=?data,noticeId=?noticeId,customRealmStr=?customRealmStr,timeParamStr=?timeParamStr,areaNameList=?areaNameList,noticeType=?noticeType,pageType=?pageType,seq=?seq WHERE id=?id;";

                db.ExecNonQuery(editSql, MySqlParameters);
            }
            //新增
            else
            {
                string editSql =
                    $"INSERT INTO notice (state,startTime,endTime,title,content,icon,pic,language,jump,popUp,sort,link,realmlist,triggertype,openserverstartday,openserverstarttime,openserverendday,openserverendtime,dungeonid,updatetime,Intervaldays,isshowtimetext,aotupop,giftbagcustoms,data,noticeId,jumpParam,customRealmStr,timeParamStr,areaNameList,noticeType,pageType,seq) VALUES (?state,?startTime,?endTime,?title,?content,?icon,?pic,?language,?jump,?popUp,?sort,?link,?realmlist,?triggertype,?startTimeDay,?startTimefine,?endTimeDay,?endTimefine,?dungeonid,NOW(),?Intervaldays,?isshowtimetext,?aotupop,?Giftbagcustoms,?data,?noticeId,?jumpParam,?customRealmStr,?timeParamStr,?areaNameList,?noticeType,?pageType,?seq);";

                db.ExecNonQuery(editSql, MySqlParameters);
            }

            Jsondata["ret"] = 0;
            Jsondata["msg"] = "操作成功";

            return;
        }

        public override void DBSelectUserName(string account, string user, string pageno, JsonData jsonData)
        {
            string Sqlfoundaccount = (user == "")
                ? string.Format(
                    "select account, jurisdiction, valid, lastLoginTime, createTime from account order by account")
                : string.Format(
                    "select account, jurisdiction, valid, lastLoginTime, createTime from account where account = \"{0}\"",
                    user);

            MySqlDataReader reader = db.ExecReader(Sqlfoundaccount);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectUserName 连接数据库异常");
                jsonData["ret"] = 6;
                jsonData["msg"] = "error";
                return;
            }

            JsonData data = new JsonData();
            jsonData["data"] = data;
            data.Add("");
            data.Clear();

            int pageNo = (pageno == "") ? 0 : int.Parse(pageno);
            int i = 0;

            while (reader.Read())
            {
                //if (pageNo * 10 <= i && i < (pageNo + 1) * 10)
                {
                    JsonData rdata = new JsonData();

                    rdata["account"] = reader.GetString("account");
                    rdata["jurisdiction"] = reader.GetString("jurisdiction");
                    rdata["valid"] = reader.GetString("valid");
                    rdata["lastLoginTime"] = reader.GetString("lastLoginTime");
                    rdata["createTime"] = reader.GetString("createTime");

                    data.Add(rdata);
                }

                i++;
            }

            reader.Close();

            jsonData["ret"] = 0;
            jsonData["msg"] = "success";

            return;
        }

        public override int DBUpdateUserName(string account, string user, string pass, string newPass,
            string jurisdiction, JsonData jsondata)
        {
            string sqlStr = string.Format("select * from account where account='{0}' and password= '{1}'", user, pass);

            TraceLog.Trace("MySqlDBOperator.DBUpdateUserName sqlStr {0}", sqlStr);

            MySqlDataReader reader = db.ExecReader(sqlStr);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBUpdateUserName 数据库连接失败");
                jsondata["ret"] = 6;
                jsondata["msg"] = "error";
                return 6;
            }

            if (reader.HasRows == false)
            {
                TraceLog.Trace("MySqlDBOperator.DBUpdateUserName 用户不存在或密码错误");
                jsondata["ret"] = 4;
                jsondata["msg"] = "用户不存在或密码错误";
                reader.Close();
                return 4;
            }

            if (user == null || user == "" || pass == "")
            {
                TraceLog.Trace("MySqlDBOperator.DBUpdateUserName 用户名和密码不能为空");
                jsondata["ret"] = 4;
                jsondata["msg"] = "用户名和密码不能为空！";
                reader.Close();
                return 4;
            }

            if (newPass == "" || newPass == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBUpdateUserName 用户名和密码不能为空");
                jsondata["ret"] = 4;
                jsondata["msg"] = "用户名和密码不能为空！";
                reader.Close();
                return 4;
            }

            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?account", MySqlDbType.VarChar) { Value = user },
                new MySqlParameter("?password", MySqlDbType.VarChar) { Value = newPass },
                new MySqlParameter("?jurisdiction", MySqlDbType.VarChar) { Value = jurisdiction },

            }.ToArray();

            try
            {
                string editSql =
                    $"UPDATE account SET password=?password, jurisdiction=?jurisdiction WHERE account=?account;";
                db.ExecNonQuery(editSql, MySqlParameters);
            }
            catch (Exception)
            {
                TraceLog.Error("MySqlDBOperator.DBUpdateUsername: 更新写入数据库错误。");
            }

            return 0;
        }

        public override int DBDeleteUserName(string account, string user, JsonData jsondata)
        {
            string Sqlfoundaccount = string.Format("select account, jurisdiction from account");
            MySqlDataReader reader = db.ExecReader(Sqlfoundaccount);

            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBDeleteUserName 连接数据库异常");
                jsondata["ret"] = 6;
                jsondata["msg"] = "error";
                return 6;
            }

            JsonData msgdata = new JsonData();
            jsondata["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();

            while (reader.Read())
            {
                JsonData one = new JsonData();

                one.Add(reader.GetString("account"));
                //one.Add(reader.GetString("jurisdiction"));
                msgdata.Add(one);
            }

            reader.Close();

            if (user != null)
            {
                string selectSql = string.Format("select * from account where account='{0}'", user);
                reader = db.ExecReader(selectSql);

                if (reader == null)
                {
                    TraceLog.Trace("MySqlDBOperator.DBDeleteUserName 连接数据库异常");
                    jsondata["ret"] = 6;
                    jsondata["msg"] = "error";
                    return 6;
                }

                if (reader.HasRows == false)
                {
                    TraceLog.Trace("MySqlDBOperator.DBDeleteUserName 账户不存在");
                    jsondata["ret"] = 0;
                    jsondata["msg"] = "账户不存在";
                    reader.Close();
                    return 0;
                }

                reader.Close();

                string deleteSql = string.Format("delete from account where account='{0}'", user);
                reader = db.ExecReader(deleteSql);

                if (reader == null)
                {
                    TraceLog.Trace("MySqlDBOperator.DBDeleteUserName 连接数据库异常");
                    jsondata["ret"] = 6;
                    jsondata["msg"] = "error";
                    return 6;
                }

                reader.Close();

                reader = db.ExecReader(selectSql);

                if (reader == null)
                {
                    TraceLog.Trace("MySqlDBOperator.DBDeleteUserName 连接数据库异常");
                    jsondata["ret"] = 6;
                    jsondata["msg"] = "error";
                    return 6;
                }

                if (reader.HasRows == false)
                {
                    jsondata["ret"] = 3;
                    jsondata["msg"] = "删除成功";
                }

                reader.Close();
                return 0;
            }

            jsondata["ret"] = 3;
            jsondata["msg"] = "删除成功";

            return 3;
        }

        public override int DBRecalledEmail(string uuid, string sid, string table, JsonData Jsondata)
        {
            int id = sid == "" ? 0 : int.Parse(sid);
            if (table == "")
            {
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "撤回失败";
                return 6;
            }

            TraceLog.Trace("MySqlDBOperator.DBRecalledEmail uuid:{0} id:{1}", uuid, id);

            string strSql;



            if (!string.IsNullOrEmpty(uuid))
            {
                strSql = string.Format("update {0} set status={1} where uuid='{2}'", table, (int)MailStatus.ReCall,
                    uuid);
            }
            else if (id > 0)
            {
                strSql = string.Format("update {0} set status={1} where Id={2}", table, (int)MailStatus.ReCall, id);
            }
            else
            {
                Jsondata["ret"] = 2;
                Jsondata["msg"] = "uuid/id 参数错误！";
                return 2;
            }

            //将撤回状态写入数据库
            TraceLog.Trace("MySqlDBOperator.DBRecalledEmail sql is {0}", strSql);
            MySqlDataReader reader = db.ExecReader(strSql);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBRecalledEmail no record in account table, return reader is null!");
                Jsondata["ret"] = 4;
                Jsondata["msg"] = "数据库操作失败！";
                return 4;
            }

            reader.Close();

            // 从op邮件cache中删除邮件, 防止邮件被重复修改错误
            //List<long> removeKeyList = new List<long>();
            //var data = OperationServerUtils.GetMailDBData();
            //foreach (var item in data.mailTable)
            //{
            //    if(item.Value.ruleMail.Mail.Uuid.GetString() == uuid)
            //    {
            //        removeKeyList.Add(item.Key);
            //    }
            //}
            //foreach (long key in removeKeyList)
            //{
            //    data.mailTable.Remove(key);
            //}

            // 将撤回邮件信息发送给邮件服务器
            SSDiscardMailWithRuleReq req = new SSDiscardMailWithRuleReq();
            req.Uuid.SetString(uuid);
            req.MailID = id;
            OperationServerUtils.SendToWorld((int)SSGameMsgID.DiscardMailWithRuleReq, ref req, 0, 0, "");

            Jsondata["ret"] = 0;
            Jsondata["msg"] = "操作成功";

            return 0;
        }

        public override int DBAddUserName(string account, string token, string user, string pass, string jurisdiction,
            JsonData jsondata)
        {
            string strSql = string.Format("select * from account where account='{0}'", user);

            TraceLog.Trace("MySqlDBOperator.DBAddUserName sql is {0}", strSql);

            MySqlDataReader reader = db.ExecReader(strSql);

            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBAddUserName 数据库连接失败");
                jsondata["ret"] = 6;
                jsondata["msg"] = "error";
                return 6;
            }

            if (reader.HasRows == true)
            {
                TraceLog.Trace("MySqlDBOperator.DBAddUserName 用户已存在，请重新创建");
                jsondata["ret"] = 4;
                jsondata["msg"] = "用户已存在，请重新创建！";
                reader.Close();
                return 4;
            }

            reader.Close();

            MySqlParameter createTime = new MySqlParameter("?createTime", MySqlDbType.DateTime)
                { Value = DateTime.Now };
            string sqlInsert =
                string.Format(
                    "insert into account set account= '{0}', name='{1}', password='{2}', jurisdiction='{3}', createTime=?createTime",
                    user, user, pass, jurisdiction);
            MySqlDataReader readerInsert = db.ExecReader(sqlInsert, createTime);

            TraceLog.Trace("MySqlDBOperator.DBAddUserName sqlInsert is {0}", sqlInsert);

            if (readerInsert == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBAddUserName 数据库连接失败");
                jsondata["ret"] = 6;
                jsondata["msg"] = "error";
                return 6;
            }

            jsondata["ret"] = 3;
            jsondata["msg"] = "创建成功!";

            readerInsert.Close();

            TraceLog.Trace("MySqlDBOperator.DBAddUserName Success");
            return 0;
        }

        public override void DBSelectPlayerOp(int pageSize, int pageIndex, int type, string opUser, JsonData jsonData)
        {
            int count = 0;

            jsonData["pageSize"] = pageSize;
            jsonData["pageIndex"] = pageIndex;
            jsonData["ret"] = 0;

            string sqlCount = "select count(id) as sum from player_op";
            MySqlDataReader readerCount = db.ExecReader(sqlCount);

            TraceLog.Trace("MySqlDBOperator.DBAddUserName sql is {0}", sqlCount);

            if (readerCount == null)
            {
                TraceLog.Error("MySqlDBOperator.DBSelectPlayerOp 数据库连接失败");
                jsonData["ret"] = 6;
                jsonData["msg"] = "数据库连接失败";
                return;
            }
            else
            {
                while (readerCount.Read())
                {
                    count = readerCount.GetInt32("sum");
                    break;
                }

                readerCount.Close();
            }

            jsonData["count"] = count;

            string sql = $"select * from player_op where 1=1";
            if (type != 0)
            {
                sql = sql + $" and type={type}";
            }

            if (!string.IsNullOrEmpty(opUser))
            {
                sql = sql + $" and opUser='{opUser}'";
            }

            sql = sql + $" limit {(pageIndex - 1) * pageSize}, {pageSize}";
            MySqlDataReader reader = db.ExecReader(sql);

            JsonData msgdata = new();
            jsonData["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();

            if (reader == null)
            {
                TraceLog.Error("MySqlDBOperator.DBSelectPlayerOp 数据库连接失败");
                jsonData["ret"] = 6;
                jsonData["msg"] = "数据库连接失败";
                return;
            }
            else
            {
                while (reader.Read())
                {
                    var tempData = new JsonData();
                    tempData["id"] = reader.GetUInt32("id");
                    tempData["uid"] = reader.GetInt32("uid");
                    tempData["uidList"] = reader.GetString("uidList");
                    tempData["type"] = reader.GetInt32("type");
                    tempData["uniqueId"] = reader.GetString("uniqueId");
                    tempData["opUser"] = reader.GetString("opUser");
                    tempData["opId"] = reader.GetInt32("opId");
                    tempData["opType"] = reader.GetInt32("opType");
                    tempData["opNum"] = reader.GetInt32("opNum");
                    tempData["createTime"] = reader.GetDateTime("createtime").ToString();

                    ExtPlayerOp ext = new ExtPlayerOp();
                    try
                    {
                        //请封装
                        int gameDataIndex = reader.GetOrdinal("exData");
                        if (!reader.IsDBNull(gameDataIndex))
                        {
                            //这个需要大一点
                            byte[] buffer = new byte[64 * 1024];
                            long gameDataLength = reader.GetBytes(gameDataIndex, 0, buffer, 0, buffer.Length);
                            byte[] gameDataByte = new byte[gameDataLength];
                            Buffer.BlockCopy(buffer, 0, gameDataByte, 0, (int)gameDataLength);

                            StructMessageParseUtils.ParseFrom(ref ext, gameDataByte);
                        }
                    }
                    catch (Exception ex)
                    {
                        if (reader != null)
                            reader.Close();

                        TraceLog.Error("MySqlDBOperator.DBSelectPlayerOp parse ExtPlayerOp error! error:{0}",
                            ex.Message);
                        jsonData["ret"] = 2;
                        jsonData["msg"] = "数据解析失败";
                        break;
                    }

                    if (ext.Havedata)
                    {
                        //tempData["paramInt"] = ext.ParamInt;
                        tempData["paramStr"] = ext.ParamStr.GetString();
                    }

                    msgdata.Add(tempData);
                }

                reader.Close();
            }
        }

        public override int DBSelectNotice(int page, int pageCount, int popUp, string noticeId, JsonData Jsondata)
        {
            JsonData sysdata = new JsonData();

            // 表格中的公告
            Jsondata["sysdata"] = sysdata;
            sysdata.Add("");
            sysdata.Clear();
            //SortedList<uint, SysNoticeDesc> descList = SysNoticeDescMgr.Instance.ItemTable;
            //if (popUp == 0)
            //{
            //    foreach (var item in descList)
            //    {
            //        JsonData linedataSys = new JsonData();

            //        linedataSys["noticeId"] = item.Key;
            //        linedataSys["state"] = item.Value.state;
            //        linedataSys["startTime"] = item.Value.startTime;
            //        linedataSys["endTime"] = item.Value.endTime;
            //        linedataSys["title"] = item.Value.title;
            //        linedataSys["content"] = item.Value.content;
            //        linedataSys["icon"] = item.Value.icon;
            //        linedataSys["pic"] = item.Value.pic;
            //        linedataSys["jump"] = item.Value.jump;
            //        linedataSys["popUp"] = item.Value.popUp;
            //        linedataSys["link"] = item.Value.link;
            //        linedataSys["language"] = item.Value.language;
            //        linedataSys["triggertype"] = item.Value.triggertype;
            //        linedataSys["jumpParam"] = item.Value.jumpParam;
            //        linedataSys["pageType"] = item.Value.pageType;

            //        if (item.Value.realmlists.Contains(0))
            //        {
            //            linedataSys["realmlist"] = "0";
            //        }
            //        else
            //        {
            //            linedataSys["realmlist"] = string.Join(',', item.Value.realmlists);
            //        }

            //        linedataSys["openserverstartday"] = item.Value.openserverstartday;
            //        linedataSys["openserverstarttime"] = item.Value.openserverstarttime;
            //        linedataSys["openserverendday"] = item.Value.openserverendday;
            //        linedataSys["openserverendtime"] = item.Value.openserverendtime;
            //        linedataSys["valid"] = OperationServerUtils.CheckTime(DateTime.Parse(item.Value.startTime),
            //            DateTime.Parse(item.Value.endTime));
            //        sysdata.Add(linedataSys);
            //    }

            //    pageCount = pageCount - descList.Count;
            //}

            //if (page > 0)
            //{
            //    page = page - 1;
            //}

            // 查询总数
            string countSqlStr = $"SELECT count(distinct noticeId) as num from notice where popUp={popUp}";
            MySqlDataReader countReader = db.ExecReader(countSqlStr);
            TraceLog.Trace("MySqlDBOperator.DBSelectNotice sql is {0}", countSqlStr);

            if (countReader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectNotice 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return 6;
            }

            int count = 0;
            while (countReader.Read())
            {
                count = countReader.GetInt32("num");
                break;
            }

            //if (popUp == 0)
            //{
            //    count += descList.Count;
            //}

            Jsondata["count"] = count;
            countReader.Close();

            int skip = (page -1) * pageCount;
            string sqlStr = "SELECT * from notice ";

            if (!string.IsNullOrEmpty(noticeId))
            {
                sqlStr += " WHERE noticeId = " + noticeId + ";";
            }
            else
            {
                sqlStr += $"WHERE popUp = {popUp} ORDER BY noticeId DESC limit {skip},{pageCount};";
            }

            MySqlDataReader reader = db.ExecReader(sqlStr);
            TraceLog.Trace("MySqlDBOperator.DBSelectNotice sqlStr {0}", sqlStr);

            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectNotice 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return 6;
            }

            JsonData msgdata = new JsonData();
            Jsondata["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();

            JsonData contentList = null;
            int noId = 0;

            while (reader.Read())
            {
                JsonData textParamList = new JsonData();
                textParamList.Add("");
                textParamList.Clear();
                JsonData areaNameList = new JsonData();
                areaNameList.Add("");
                areaNameList.Clear();

                if (noId != reader.GetInt32("noticeId") || noId == 0)
                {
                    noId = reader.GetInt32("noticeId");
                    JsonData linedata1 = new JsonData();

                    contentList = new JsonData();
                    contentList.Add("");
                    contentList.Clear();
                    linedata1["contentList"] = contentList;
                    linedata1["state"] = reader.GetInt32("state");
                    linedata1["startTime"] = reader.GetDateTime("startTime").ToString("yyyy-MM-dd HH:mm:ss");
                    linedata1["endTime"] = reader.GetDateTime("endTime").ToString("yyyy-MM-dd HH:mm:ss");
                    linedata1["link"] = reader.GetString("link");
                    linedata1["jump"] = reader.GetInt32("jump");
                    linedata1["sort"] = reader.GetInt32("sort");
                    linedata1["popUp"] = reader.GetInt32("popUp");
                    linedata1["triggertype"] = reader.GetInt32("triggertype");
                    linedata1["noticeId"] = reader.GetInt32("noticeId");
                    linedata1["realmlist"] = reader.GetString("realmlist");
                    linedata1["openserverstartday"] = reader.GetInt32("openserverstartday");
                    linedata1["openserverstarttime"] = reader.GetString("openserverstarttime");
                    linedata1["openserverendday"] = reader.GetInt32("openserverendday");
                    linedata1["openserverendtime"] = reader.GetString("openserverendtime");
                    linedata1["intervaldays"] = reader.GetString("Intervaldays");
                    linedata1["timetext"] = reader.GetInt32("isshowtimetext");
                    linedata1["dungeonid"] = reader.GetInt32("dungeonid");
                    linedata1["aotupop"] = reader.GetInt32("aotupop");
                    linedata1["giftbagcustoms"] = reader.GetInt32("giftbagcustoms");
                    linedata1["jumpParam"] = reader.GetInt32("jumpParam");
                    linedata1["type"] = reader.GetInt32("noticeType");
                    linedata1["valid"] = OperationServerUtils.CheckTime(reader.GetDateTime("startTime"),
                        reader.GetDateTime("endTime"));
                    linedata1["customRealmStr"] = reader.GetString("customRealmStr");
                    linedata1["timeParamStr"] = reader.GetString("timeParamStr");
                    linedata1["pageType"] = reader.GetInt32("pageType");

                    linedata1["areaNameList"] = areaNameList;
                    string areaNameListStr = reader.GetString("areaNameList");
                    if (areaNameListStr.Contains("|"))
                    {
                        foreach (var item in areaNameListStr.Split("|"))
                        {
                            if (!string.IsNullOrWhiteSpace(item))
                                areaNameList.Add(item);
                        }
                    }

                    ExtNotice ext = new ExtNotice();
                    //请封装
                    int gameDataIndex = reader.GetOrdinal("data");
                    if (!reader.IsDBNull(gameDataIndex))
                    {
                        byte[] buffer = new byte[2 * 1024];
                        long gameDataLength = reader.GetBytes(gameDataIndex, 0, buffer, 0, buffer.Length);
                        byte[] gameDataByte = new byte[gameDataLength];
                        Buffer.BlockCopy(buffer, 0, gameDataByte, 0, (int)gameDataLength);

                        StructMessageParseUtils.ParseFrom(ref ext, gameDataByte);
                    }

                    linedata1["popType"] = ext.Type;
                    linedata1["textParamList"] = textParamList;

                    for (int i = 0; i < ext.TextParams.Count; i++)
                    {
                        string str = ext.TextParams[i].GetString();
                        textParamList.Add(str);
                    }

                    msgdata.Add(linedata1);
                }

                // 多语言部分contentList
                JsonData Listdata = new JsonData();
                Listdata["id"] = reader.GetInt32("id");
                Listdata["title"] = reader.GetString("title");
                Listdata["content"] = reader.GetString("content");
                Listdata["icon"] = reader.GetString("icon");
                Listdata["pic"] = reader.GetString("pic");
                Listdata["language"] = reader.GetString("language");

                if (contentList != null)
                {
                    contentList.Add(Listdata);
                }
            }

            reader.Close();

            Jsondata["ret"] = 0;

            TraceLog.Trace("MySqlDBOperator.DBSelectNotice success");

            return 0;
        }

        public override int DBShowNoticeDayNotice(string noticeId, JsonData Jsondata)
        {
            string sqlStr = "SELECT * from notice ";
            if (!String.IsNullOrEmpty(noticeId))
            {
                sqlStr += " WHERE noticeId = " + noticeId;
            }

            MySqlDataReader reader = db.ExecReader(sqlStr);
            TraceLog.Trace("MySqlDBOperator.DBShowNoticeDayNotice sqlStr {0}", sqlStr);

            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBShowNoticeDayNotice 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return 6;
            }

            JsonData month = new JsonData();
            Jsondata["month"] = month;
            month.Add("");
            month.Clear();
            month.Add(DateTime.Now.ToString("yyyy-MM-dd"));
            month.Add(DateTime.Now.AddMonths(1).ToString("yyyy-MM-dd"));

            JsonData msgdata = new JsonData();
            Jsondata["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();

            JsonData realmjosn = new JsonData();
            Jsondata["realm"] = realmjosn;
            realmjosn.Add("");
            realmjosn.Clear();

            while (reader.Read())
            {
                var openserverstartday = reader.GetInt32("openserverstartday");
                var openserverstarttime = reader.GetString("openserverstarttime");
                var openserverendday = reader.GetInt32("openserverendday");
                var openserverendtime = reader.GetString("openserverendtime");
                var Intervaldays = reader.GetInt32("Intervaldays");
                var _realmlist = reader.GetString("realmlist");
                var Realm = OperationServerUtils.GetOperationServerData().m_allRealm.Values.ToList();

                //不是全服
                if (!(_realmlist == "0" || _realmlist == ""))
                {
                    var realmlist = _realmlist.Split(',').Select(s => int.Parse(s)).ToList();
                    Realm = Realm.FindAll(f => realmlist.Exists(e => e == f.realmId));
                }

                //  openTime staTime  两个时间轮着走
                foreach (var r in Realm)
                {
                    var openTime = OperationServerUtils.StampToDateTime(r.openTime);
                    var openTimestr = openTime.ToString("yyyy-MM-dd HH:mm:ss");

                    if (openTime.Hour < 5)
                    {
                        openTime = openTime.AddDays(-1);
                    }

                    openTime = new DateTime(openTime.Year, openTime.Month, openTime.Day, 5, 0, 0);

                    //开服推迟显示时间
                    OperationServerUtils.AddTimes(ref openTime, openserverstartday, openserverstarttime);

                    realmjosn.Add(
                        $"{r.realmName}   开服时间： {openTimestr} <br/> 持续时间{openserverendday}:{openserverendtime}  间隔天数{Intervaldays}");
                    JsonData realm = new JsonData();

                    var thisTime = DateTime.Now;

                    for (int i = 0; i < 30; i++)
                    {
                        var strsta = openTime.ToString("yyyy-MM-dd HH:mm:ss");
                        //持续天数
                        OperationServerUtils.AddTimes(ref openTime, openserverendday, openserverendtime);

                        var strend = openTime.ToString("yyyy-MM-dd HH:mm:ss");
                        var timesta = "";

                        //未开始
                        if (thisTime < DateTime.Parse(strsta))
                        {
                            timesta = "#";
                        }
                        //已结束
                        else if (thisTime > DateTime.Parse(strend))
                        {
                            timesta = "##";
                        }
                        //正在进行中
                        else
                        {
                            timesta = "###";
                        }

                        realm.Add($" {strsta}  -> {strend} {timesta}");

                        if (Intervaldays == 0 && i == 0)
                        {
                            break;
                        }

                        //间隔天数
                        openTime = openTime.AddDays(Intervaldays);
                    }

                    msgdata.Add(realm);
                }

                reader.Close();
                TraceLog.Trace("MySqlDBOperator.DBShowNoticeDayNotice success");

                return 0;
            }

            return 0;
        }

        public override int DBSelectNoticeLamp(string id, JsonData Jsondata)
        {
            string sqlStr = "SELECT * from noticelamp ";

            if (!String.IsNullOrEmpty(id))
            {
                sqlStr += " WHERE id = " + id;
            }

            MySqlDataReader reader = db.ExecReader(sqlStr);

            TraceLog.Trace("MySqlDBOperator.DBSelectNoticeLamp sqlStr {0}", sqlStr);

            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectNoticeLamp 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return 6;
            }

            JsonData msgdata = new JsonData();
            Jsondata["code"] = 0;

            Jsondata["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();
            int count = 0;
            while (reader.Read())
            {
                //id  content cycleTimeType   triggerTime showInterval    showCount realms
                JsonData linedata1 = new JsonData();
                linedata1["id"] = reader.GetString("id");
                linedata1["content"] = reader.GetString("content");
                linedata1["cycleTimeType"] = reader.GetString("cycleTimeType");
                linedata1["triggerTime"] = reader.GetString("triggerTime");
                linedata1["showInterval"] = reader.GetString("showInterval");
                linedata1["showCount"] = reader.GetString("showCount");
                linedata1["realms"] = reader.GetString("realms");
                msgdata.Add(linedata1);
                count++;
            }

            Jsondata["count"] = count;

            reader.Close();
            TraceLog.Trace("MySqlDBOperator.DBSelectNoticeLamp success");

            return 0;
        }

        public override int DBSendNoticeLamp(string strTriggerTime, string content, int showCount, int cycleTimeType,
            int showInterval, string Realmliststr, JsonData Jsondata)
        {
            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?TriggerTime", MySqlDbType.VarChar) { Value = strTriggerTime },
                new MySqlParameter("?showCount", MySqlDbType.Int32) { Value = showCount },
                new MySqlParameter("?cycleTimeType", MySqlDbType.Int32) { Value = cycleTimeType },
                new MySqlParameter("?showInterval", MySqlDbType.Int32) { Value = showInterval },
                new MySqlParameter("?content", MySqlDbType.VarChar) { Value = content },
                new MySqlParameter("?realms", MySqlDbType.VarChar) { Value = Realmliststr }
            }.ToArray();

            string editSql =
                $"INSERT INTO noticelamp(content,cycleTimeType,triggerTime,showInterval,showCount,realms) VALUES(?content,?cycleTimeType,?TriggerTime,?showInterval,?showCount,?realms);";

            db.ExecNonQuery(editSql, MySqlParameters);

            Jsondata["ret"] = 0;
            Jsondata["msg"] = "succ";

            return 0;
        }

        public override string DBCheckJurisdiction(string account, JsonData Jsondata)
        {
            string sql = string.Format("select jurisdiction from account where valid = \'1\' and account=?account");

            MySqlParameter p_account = new MySqlParameter("?account", MySqlDbType.VarChar, 200) { Value = account };
            MySqlDataReader reader = db.ExecReader(sql, p_account);

            TraceLog.Trace("MySqlDBOperator.DBCheckJurisdiction sql {0}", sql);

            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBCheckJurisdiction 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "数据库操作失败";
                return "";
            }

            if (reader.HasRows == false)
            {
                TraceLog.Trace("MySqlDBOperator.DBCheckJurisdiction 账号不存在");
                Jsondata["ret"] = 3;
                Jsondata["msg"] = "无效的用户名或密码";
                reader.Close();
                return "";
            }

            bool ret = reader.Read();

            string jurisdiction = reader.GetString("jurisdiction");

            reader.Close();

            if (jurisdiction == "")
            {
                TraceLog.Trace("MySqlDBOperator.DBCheckJurisdiction 当前用户没有权限");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "当前用户没有权限";
            }

            return jurisdiction;
        }

        public override bool DBCheckEmailExist(JsonData Jsondata, MailJsonObj mailContent)
        {
            string sql = string.Format("select * from mail_box order by Id desc limit 0,1");
            MySqlDataReader last = db.ExecReader(sql);
            TraceLog.Trace("MySqlDBOperator.DBCheckEmailExist sql {0}", sql);

            if (last == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBCheckEmailExist:数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "数据库操作失败";
                return false;
            }

            string lastUuid = "";
            bool reter = last.Read();
            if (reter)
            {
                lastUuid = last.GetString("uuid");
            }

            last.Close();

            if (lastUuid == mailContent.uuid)
            {
                Jsondata["ret"] = 4;
                Jsondata["msg"] = "发送重复";
                return true;
            }



            return false;
        }

        public override bool DBCheckEmailTempExist(JsonData Jsondata, MailJsonObj mailContent)
        {
            string sql = string.Format("select * from mail_box_temp order by Id desc limit 0,1");
            MySqlDataReader last = db.ExecReader(sql);
            TraceLog.Trace("MySqlDBOperator.DBCheckEmailExist sql {0}", sql);

            if (last == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBCheckEmailExist:数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "数据库操作失败";
                return false;
            }

            string lastUuid = "";
            bool reter = last.Read();
            if (reter)
            {
                lastUuid = last.GetString("uuid");
            }

            last.Close();

            if (lastUuid == mailContent.uuid)
            {
                Jsondata["ret"] = 4;
                Jsondata["msg"] = "发送重复";
                return true;
            }



            return false;
        }


        public override int DBInsertIntoMailBoxTemp(string _name, string title11, string content11, string realmlist11,
            string herolist11, string language, string uuid, string curstr11, string itemstr11,
            string timeLimit11, int isSendToAll, string equipStr, byte[] dataByte, string customItemStr)
        {
            return InsertMailBoxTemp(_name, title11, content11, realmlist11,
                herolist11, language, uuid, curstr11, itemstr11,
                timeLimit11, isSendToAll, equipStr, dataByte, customItemStr, 0);
        }

        public override int DBInsertIntoMailBoxTempReview(string _name, string title11, string content11,
            string realmlist11,
            string herolist11, string language, string uuid, string curstr11, string itemstr11,
            string timeLimit11, int isSendToAll, string equipStr, byte[] dataByte, string customItemStr)
        {
            return InsertMailBoxTemp(_name, title11, content11, realmlist11,
                herolist11, language, uuid, curstr11, itemstr11,
                timeLimit11, isSendToAll, equipStr, dataByte, customItemStr, -1);
        }

        private int InsertMailBoxTemp(string _name, string title11, string content11, string realmlist11,
            string herolist11, string language, string uuid, string curstr11, string itemstr11,
            string timeLimit11, int isSendToAll, string equipStr, byte[] dataByte, string customItemStr, int status)
        {
            string[] startEndTime = timeLimit11.Split("#");
            string startTime = startEndTime[0]; //开始时间
            string endTime = startEndTime[1]; //结束时间

            string sql =
                $"insert into mail_box_temp set dater=?dater,datee=?datee,name=?name,title=?title,content=?content,realmlist=?realmlist,herolist=?herolist,data=?data,equipStr='{equipStr}',language='{language}',uuid='{uuid}',curstr='{curstr11}',itemstr='{itemstr11}',timeLimit=0,status='{status}', isSendToAll={isSendToAll},customItemStr='{customItemStr}'";

            MySqlParameter p_dater = new MySqlParameter("?dater", MySqlDbType.DateTime)
                { Value = DateTime.Parse(startTime) }; //开始时间
            MySqlParameter p_datee = new MySqlParameter("?datee", MySqlDbType.DateTime)
                { Value = DateTime.Parse(endTime) }; //结束时间

            MySqlParameter p_name = new MySqlParameter("?name", MySqlDbType.VarChar, 100) { Value = _name };
            MySqlParameter p_title = new MySqlParameter("?title", MySqlDbType.VarChar, 100) { Value = title11 };
            MySqlParameter p_content = new MySqlParameter("?content", MySqlDbType.VarChar, 1000) { Value = content11 };
            MySqlParameter p_realmlist = new MySqlParameter("?realmlist", MySqlDbType.VarChar, 1000) { Value = "" };
            MySqlParameter p_herolist = new MySqlParameter("?herolist", MySqlDbType.VarChar, 1000)
                { Value = herolist11 };
            MySqlParameter p_data = new MySqlParameter("?data", MySqlDbType.Blob) { Value = dataByte };

            TraceLog.Trace("MySqlDBOperator.DBInsertIntoMailBoxTemp insert sql {0}", sql);

            MySqlDataReader readr = db.ExecReader(sql, p_dater, p_datee, p_name, p_title, p_content, p_realmlist,
                p_herolist, p_data);

            if (readr == null)
                return 6;

            readr.Close();

            TraceLog.Trace("MySqlDBOperator.DBInsertIntoMailBox success");
            return 0;
        }





        public override int DBInsertIntoMailBox(string _name, string title11, string content11, string realmlist11,
            string herolist11, string language, string uuid, string curstr11, string itemstr11,
            string timeLimit11, int isSendToAll, string equipStr, byte[] dataByte, string customItemStr)
        {
            return MailDBUtils.DBInsertIntoMailBox(db, _name, title11, content11, realmlist11,
                herolist11, language, uuid, curstr11, itemstr11,
                timeLimit11, isSendToAll, equipStr, dataByte, customItemStr);
        }

        public override void DBSelectEmail(string startDate, string endDate, int page, int pageCount, JsonData Jsondata)
        {
            TraceLog.Trace("MySqlDBOperator.DBSelectEmail startDate {0} ,endDate {1}, page {2}, pageCount {3}",
                startDate, endDate, page, pageCount);

            string countSqlStr =
                string.Format("select count(Id) as num from mail_box where(dater >= '{0}' and  dater <= '{1}') ",
                    startDate, endDate);
            MySqlDataReader countReader = db.ExecReader(countSqlStr);
            TraceLog.Trace("MySqlDBOperator.DBSelectEmail sql {0}", countSqlStr);

            if (countReader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return;
            }

            int count = 0;
            while (countReader.Read())
            {
                count = countReader.GetInt32("num");
                break;
            }

            countReader.Close();

            string countTempSqlStr =
                string.Format(
                    "select count(Id) as num from mail_box_temp where(dater >= '{0}' and status=0 and  dater <= '{1}') ",
                    startDate, endDate);
            MySqlDataReader countTempReader = db.ExecReader(countTempSqlStr);
            if (countTempReader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return;
            }

            while (countTempReader.Read())
            {
                count += countTempReader.GetInt32("num");
                break;
            }

            countTempReader.Close();

            Jsondata["count"] = count;


            if (page > 0)
            {
                page = page - 1;
            }

            int skip = page * pageCount;
            string sqlStr =
                string.Format(
                    "select * from mail_box where(dater >= '{0}' and  dater <= '{1}')  ORDER BY dater DESC limit {2}, {3} ",
                    startDate, endDate, skip, pageCount);
            string sqlTempStr =
                string.Format(
                    "select * from mail_box_temp where(dater >= '{0}' and  dater <= '{1}' and  status=0)  ORDER BY dater DESC limit {2}, {3} ",
                    startDate, endDate, skip, pageCount);

            MySqlDataReader reader = db.ExecReader(sqlStr);


            TraceLog.Trace("MySqlDBOperator.DBSelectEmail sqlStr {0}", sqlStr);
            TraceLog.Trace("MySqlDBOperator.DBSelectEmail sqlStrTemp {0}", sqlTempStr);


            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return;
            }

            Jsondata["ret"] = 0;
            JsonData msgdata = new();
            Jsondata["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();
            try
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var _data = formatMail(reader);
                        _data["is_sync"] = 1;
                        msgdata.Add(_data);
                    }
                }

            }
            catch (Exception ex)
            {
                if (reader != null)
                    reader.Close();

                TraceLog.Error("MySqlDBOperator.DBSelectEmail parse ExtMail error {0} ", ex.Message);
                TraceLog.Exception(ex);
                return;
            }

            reader.Close();

            MySqlDataReader readerTemp = db.ExecReader(sqlTempStr);

            if (readerTemp == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return;
            }

            try
            {
                if (readerTemp.HasRows)
                {
                    while (readerTemp.Read())
                    {
                        var _data = formatMail(readerTemp);
                        _data["is_sync"] = 0;
                        msgdata.Add(_data);
                    }
                }
            }
            catch (Exception ex)
            {
                if (readerTemp != null)
                    readerTemp.Close();

                TraceLog.Error("MySqlDBOperator.DBSelectEmail template parse ExtMail error {0} ", ex.Message);
                TraceLog.Exception(ex);
                return;
            }

            readerTemp.Close();
            TraceLog.Trace("MySqlDBOperator.DBSelectEmail template success");

            return;
        }

        public override int DBReviewEmail(string uuid, string id, int status, JsonData payload)
        {
            string sql = " update mail_box_temp set status=?status where uuid=?uuid and Id=?id";
            var ages = new List<MySqlParameter>
            {
                new MySqlParameter("?status", MySqlDbType.Int32) { Value = status },
                new MySqlParameter("?id", MySqlDbType.Int32) { Value = id },
                new MySqlParameter("?uuid", MySqlDbType.String) { Value = uuid },
            }.ToArray();
            var reader = db.ExecReader(sql, ages);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                payload["ret"] = 6;
                payload["msg"] = "error";
                return -1;
            }

            payload["ret"] = 0;
            payload["msg"] = "successful";
            reader.Close();
            return 0;
        }

        public override int DBDeleteRefuseMail(string uuid, string id, JsonData payload)
        {

            string sql = "delete from mail_box_temp where uuid=?uuid and Id=?Id";
            var args = new List<MySqlParameter>
            {
                new MySqlParameter("?Id", MySqlDbType.Int32) { Value = int.Parse(id) },
                new MySqlParameter("?uuid", MySqlDbType.String) { Value = uuid },
            }.ToArray();
            var reader = db.ExecReader(sql, args);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                payload["ret"] = 6;
                payload["msg"] = "error";
                return -1;
            }

            payload["ret"] = 0;
            payload["msg"] = "successful";
            reader.Close();
            return 0;
        }

        public override void DBSelectReviewEmail(string startDate, string endDate, int page, int pageCount,
            JsonData Jsondata)
        {

            Jsondata["ret"] = 0;
            JsonData msgdata = new();
            Jsondata["data"] = msgdata;
            msgdata.Add("");
            msgdata.Clear();
            string countTempSqlStr =
                string.Format(
                    "select count(Id) as num from mail_box_temp where (status =-1 or status =-2) and (dater >= '{0}' and dater <= '{1}') ",
                    startDate, endDate);
            MySqlDataReader countTempReader = db.ExecReader(countTempSqlStr);
            if (countTempReader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return;
            }

            var count = 0;
            while (countTempReader.Read())
            {
                count = countTempReader.GetInt32("num");
                break;
            }

            countTempReader.Close();
            Jsondata["count"] = count;
            if (page > 0)
            {
                page = page - 1;
            }

            int skip = page * pageCount;
            string sql =
                string.Format(
                    "select * from mail_box_temp where (status =-1 or status =-2) and (dater >= '{0}' and  dater <= '{1}') ORDER BY dater DESC limit {2}, {3} ",
                    startDate, endDate, skip, pageCount);
            TraceLog.Trace("MySqlDBOperator.DBSelectEmail sqlStrTemp {0}", sql);

            MySqlDataReader readerTemp = db.ExecReader(sql);
            if (readerTemp == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                Jsondata["ret"] = 6;
                Jsondata["msg"] = "error";
                return;
            }

            try
            {
                if (readerTemp.HasRows)
                {
                    while (readerTemp.Read())
                    {
                        var _data = formatMail(readerTemp);
                        msgdata.Add(_data);
                    }
                }
            }
            catch (Exception ex)
            {
                if (readerTemp != null)
                    readerTemp.Close();

                TraceLog.Error("MySqlDBOperator.DBSelectEmail template parse ExtMail error {0} ", ex.Message);
                TraceLog.Exception(ex);
                return;
            }

            readerTemp.Close();

            return;
        }



        private JsonData formatMail(MySqlDataReader reader)
        {
            var _data = new JsonData();
            var dater = reader.GetDateTime("dater");
            _data["dateStr"] = dater.ToString("yyyy-MM-dd HH:mm:ss");
            int datee = reader.GetOrdinal("datee");
            _data["timeLimit"] = reader.GetInt32("timeLimit");
            if (!reader.IsDBNull(datee))
            {
                _data["endTimeStr"] = reader.GetString("datee");
            }
            else
            {
                string Dt = dater.AddMinutes((int)_data["timeLimit"]).ToString("yyyy-MM-dd HH:mm:ss");
                _data["endTimeStr"] = Dt;
            }

            _data["name"] = reader.GetString("name");
            _data["title"] = reader.GetString("title");
            _data["content"] = reader.GetString("content");
            _data["itemstr"] = reader.GetString("itemstr");
            _data["curstr"] = reader.GetString("curstr");
            _data["equipStr"] = reader.GetString("equipStr");
            _data["herolist"] = reader.GetString("herolist");
            _data["customItemStr"] = reader.GetString("customItemStr");
            _data["status"] = reader.GetInt32("status");
            _data["language"] = reader.GetString("language");
            _data["uuid"] = reader.GetString("uuid");
            _data["id"] = reader.GetInt32("Id");


            ExtMail ext = new ExtMail();

            //请封装
            int gameDataIndex = reader.GetOrdinal("data");
            if (!reader.IsDBNull(gameDataIndex))
            {
                //这个需要大一点
                byte[] buffer = new byte[64 * 1024];
                long gameDataLength = reader.GetBytes(gameDataIndex, 0, buffer, 0, buffer.Length);
                byte[] gameDataByte = new byte[gameDataLength];
                Buffer.BlockCopy(buffer, 0, gameDataByte, 0, (int)gameDataLength);

                StructMessageParseUtils.ParseFrom(ref ext, gameDataByte);
            }

            JsonData langContentList = new JsonData();
            langContentList.Add("");
            langContentList.Clear();
            _data["contentList"] = langContentList;

            if (ext.Havedata)
            {
                for (int i = 0; i < ext.LangContentList.Count; i++)
                {
                    var lang = new JsonData();
                    lang["title"] = ext.LangContentList[i].Title.GetString();
                    lang["content"] = ext.LangContentList[i].Content.GetString();
                    lang["language"] = ext.LangContentList[i].Language.GetString();
                    lang["senderName"] = ext.LangContentList[i].SenderName.GetString();
                    langContentList.Add(lang);

                    if (ext.LangContentList[i].Language.GetString() == "jp" &&
                        string.IsNullOrEmpty(_data["title"].ToString()))
                    {
                        _data["title"] = ext.LangContentList[i].Title.GetString();
                        _data["content"] = ext.LangContentList[i].Content.GetString();
                    }
                }

                _data["versionParam"] = ext.VersionParam.GetString();
                _data["playerCreateTime"] = ext.PlayerCreateTimeLimit;
                _data["realmlist"] = ext.RealmStr.GetString();
                _data["timeParamStr"] = ext.TimeParamStr.GetString();
            }

            return _data;

        }

        public override bool DBInsertPlayerOp(long uid, int type, string opUser, int opId, int opType, int opNum,
            string uniqueId, int paramInt, string paramStr, JsonData jsonData)
        {
            MySqlDataReader readerinsert = null;

            try
            {
                ExtPlayerOp extData = new ExtPlayerOp();
                extData.Havedata = false;

                if (paramInt != 0 || !string.IsNullOrEmpty(paramStr))
                {
                    extData.Havedata = true;
                    extData.ParamStr.SetString(paramStr);
                    //extData.ParamInt = paramInt;
                }

                byte[] dataByte = StructMessageParseUtils.ToByteArray(ref extData);

                MySqlParameter p_data = new MySqlParameter("?extData", MySqlDbType.Blob) { Value = dataByte };
                MySqlParameter p_datetime = new MySqlParameter("?createtime", MySqlDbType.DateTime)
                    { Value = DateTime.Now };

                string sql = string.Format(
                    "insert into player_op set uid= {0}, uidList='{1}', type={2}, opUser='{3}',opId={4},opType={5},opNum={6},uniqueId='{7}',createtime=?createtime, exData=?extData",
                    uid, "", type, opUser, opId, opType, opNum, uniqueId);

                readerinsert = db.ExecReader(sql, p_datetime, p_data);
            }
            catch (Exception ex)
            {
                TraceLog.Error("MySqlDBOperator.DBInsertPlayerOp error:{0}", ex.Message);

                if (readerinsert != null)
                    readerinsert.Close();

                return false;
            }

            if (readerinsert == null)
            {
                TraceLog.Error("MySqlDBOperator.DBInsertPlayerOp 数据库插入失败");
                jsonData["ret"] = 6;
                jsonData["msg"] = "数据库插入失败";
                return false;
            }

            readerinsert.Close();

            return true;
        }


        public override int DbInsertItemCost(JsonData jsonData, int itemId, float cn, float kr, float jp, float en)
        {
            string selectSql = "select * from item_cost where itemId=" + itemId;
            MySqlDataReader readr = db.ExecReader(selectSql);
            if (readr == null)
            {
                TraceLog.Error("MySqlDBOperator.DBInsertPlayerOp 数据库插入失败");
                jsonData["ret"] = 6;
                jsonData["msg"] = "数据库插入失败";
                return -1;
            }

            bool exist = readr.HasRows;

            readr.Close();

            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?itemId", MySqlDbType.Int32) { Value = itemId },
                new MySqlParameter("?cn", MySqlDbType.Float) { Value = cn },
                new MySqlParameter("?kr", MySqlDbType.Float) { Value = kr },
                new MySqlParameter("?jp", MySqlDbType.Float) { Value = jp },
                new MySqlParameter("?en", MySqlDbType.Float) { Value = jp },
            }.ToArray();
            if (exist)
            {
                string updateSql = "update item_cost set cn=?cn,kr=?kr,jp=?jp,en=?en where itemId=?itemId;";
                try
                {
                    MySqlDataReader upReadr = db.ExecReader(updateSql, MySqlParameters);
                    if (upReadr != null)
                    {
                        upReadr.Close();
                    }
                }
                catch (Exception)
                {
                    TraceLog.Error("MySqlDBOperator.DbInsertItemCost sql {0} failed", updateSql);
                }

                return 0;
            }

            string sql = "insert into item_cost set itemId=?itemId,cn=?cn,kr=?kr,jp=?jp,en=?en;";
            try
            {
                MySqlDataReader InsetrReadr = db.ExecReader(sql, MySqlParameters);
                if (InsetrReadr != null)
                {
                    InsetrReadr.Close();
                }
            }
            catch (Exception)
            {
                TraceLog.Error("MySqlDBOperator.DbInsertItemCost sql {0} failed", sql);
            }

            return 0;
        }

        public override int DbSelectItemCostAll(JsonData jsondata)
        {

            string sql = $"select * from item_cost";
            MySqlDataReader reader = db.ExecReader(sql);
            TraceLog.Trace("MySqlDBOperator.DbSelectItemCost sql {0}", sql);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                jsondata["ret"] = 6;
                jsondata["msg"] = "error";
                return -1;
            }

            JsonData datas = new JsonData();
            while ((reader.Read()))
            {
                JsonData data = new JsonData();
                data["kr"] = reader.GetDouble("kr");
                data["cn"] = reader.GetDouble("cn");
                data["jp"] = reader.GetDouble("jp");
                data["en"] = reader.GetDouble("en");
                data["itemId"] = reader.GetInt32("itemId");
                string nameKey = string.Format("item{0}", reader.GetInt32("itemId"));
                var lang = LanguageDescMgr.Instance.GetConfig(nameKey);
                if (lang != null)
                {
                    string name = lang.ZH;
                    data["name"] = name;
                }
                else
                {
                    data["name"] = nameKey;
                }

                datas.Add(data);
            }

            jsondata["data"] = datas;
            reader.Close();
            return 0;
        }

        public override int DbDeleteItemCost(JsonData jsondata, int itemId)
        {
            string sql = $"delete from item_cost where itemId='{itemId}'";

            MySqlDataReader reader = db.ExecReader(sql);
            TraceLog.Trace("MySqlDBOperator.DbDeleteItemCost sql {0}", sql);

            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                jsondata["ret"] = 6;
                jsondata["msg"] = "error";
                return -1;
            }

            reader.Close();

            return 0;
        }



        public override int DbInsertPayment(JsonData builder, string admin, string owner, string realm, string dep,
            int uid, string itemId, int num)
        {


            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?itemId", MySqlDbType.String) { Value = itemId },
                new MySqlParameter("?uid", MySqlDbType.Int64) { Value = uid },
                new MySqlParameter("?num", MySqlDbType.Int32) { Value = num },
                new MySqlParameter("?owner", MySqlDbType.String) { Value = owner },
                new MySqlParameter("?admin", MySqlDbType.String) { Value = admin },
                new MySqlParameter("?realm", MySqlDbType.String) { Value = realm },
                new MySqlParameter("?dep", MySqlDbType.String) { Value = dep },
            }.ToArray();
            string sql =
                "insert into welfare_payment set itemId=?itemId,uid=?uid,num=?num,admin=?admin,dep=?dep,realm=?realm,owner=?owner;";
            try
            {
                MySqlDataReader InsetrReadr = db.ExecReader(sql, MySqlParameters);
                if (InsetrReadr != null)
                {
                    InsetrReadr.Close();
                }
            }
            catch (Exception e)
            {
                TraceLog.Error("MySqlDBOperator.DbInsertPayment sql {0} failed,e={1}", sql, e.Message);
            }

            return 0;
        }

        public override int DbSelectPaymentAll(JsonData jsondata, RepeatedFixedStructString128_10 param)
        {
            string sql = "select * from welfare_payment ";
            string where = "";
            var u = param.Get(0).ToString();

            String or = "";
            string w = "";
            if (!String.IsNullOrEmpty(u))
            {
                where = " uid=" + int.Parse(u);
                or = " or ";
                w = " where ";
            }

            var name = param.Get(1).ToString();

            if (!String.IsNullOrEmpty(name))
            {
                where += or + "owner='" + name + "'";
                or = " or ";
                w = " where ";
            }

            sql += w + where;
            MySqlDataReader reader = db.ExecReader(sql);
            TraceLog.Trace("MySqlDBOperator.DbSelectItemCost sql {0}", sql);
            if (reader == null)
            {
                TraceLog.Trace("MySqlDBOperator.DBSelectEmail 数据库连接失败");
                jsondata["ret"] = 6;
                jsondata["msg"] = "error";
                return -1;
            }

            JsonData datas = new JsonData();
            while ((reader.Read()))
            {
                JsonData data = new JsonData();
                data["admin"] = reader.GetString("admin");
                data["num"] = reader.GetInt32("num");
                data["uid"] = reader.GetInt64("uid");
                string itemIds = reader.GetString("itemId");
                data["owner"] = reader.GetString("owner");
                data["dep"] = reader.GetString("dep");
                data["realm"] = reader.GetString("realm");
                data["itemId"] = itemIds;
                string[] ids = itemIds.Split(",");
                string names = "";
                foreach (string id in ids)
                {
                    var desc = PayDiamondDescMgr.Instance.GetConfig(int.Parse(id));
                    string nameKey = desc?.itemName ?? "";
                    var lang = LanguageDescMgr.Instance.GetConfig(nameKey);
                    if (lang != null)
                    {
                        names = names + "\r\r" + lang.ZH;
                    }
                    else
                    {
                        names = names + "\r\r" + nameKey;
                    }
                }

                data["name"] = names;
                datas.Add(data);
            }

            jsondata["data"] = datas;
            reader.Close();

            return 0;
        }


        public override int InsertOperationLog(string account, string method, string url, byte[] data)
        {

            var MySqlParameters = new List<MySqlParameter>()
            {
                new MySqlParameter("?account", MySqlDbType.String) { Value = account },
                new MySqlParameter("?method", MySqlDbType.String) { Value = method },
                new MySqlParameter("?url", MySqlDbType.String) { Value = url },
                new MySqlParameter("?data", MySqlDbType.MediumBlob) { Value = data },
                new MySqlParameter("?ctime", MySqlDbType.DateTime) { Value = DateTime.Now },
            }.ToArray();
            string sql =
                "insert into operation_log set account=?account,method=?method,url=?url,data=?data,ctime=?ctime;";
            try
            {
                MySqlDataReader InsetrReadr = db.ExecReader(sql, MySqlParameters);
                if (InsetrReadr != null)
                {
                    InsetrReadr.Close();
                }
            }
            catch (Exception e)
            {
                TraceLog.Error("MySqlDBOperator.InsertOperationLog sql {0} failed,e={1}", sql, e.Message);
            }

            return 0;
        }

        public override int SelectOperationLog(JsonData payload, int pageIndex, int pageSize, string account,
            string method, string url, string startStr, string endStr)
        {
            string sql = "select * from operation_log ";
            string argsSql = "";
            string and = "";
            payload["count"] = 0;

            var MySqlParameters = new List<MySqlParameter>();
            if (account != "")
            {
                argsSql = " account=?account ";
                and = "and";
                MySqlParameters.Add(new MySqlParameter("?account", MySqlDbType.String) { Value = account });
            }

            if (method != "")
            {
                argsSql += and + " method=?method ";
                and = "and";
                MySqlParameters.Add(new MySqlParameter("?method", MySqlDbType.String) { Value = method });
            }

            if (url != "")
            {
                argsSql += and + " url=?url ";
                and = "and";
                MySqlParameters.Add(new MySqlParameter("?url", MySqlDbType.String) { Value = url });
            }

            DateTime start = default;
            DateTime end = default;
            if (startStr != "" && endStr != "")
            {
                start = DateTime.ParseExact(startStr, "yyyy-MM-dd HH:mm:ss",
                    CultureInfo.InvariantCulture);
                end = DateTime.ParseExact(endStr, "yyyy-MM-dd HH:mm:ss",
                    CultureInfo.InvariantCulture);
                argsSql += and +
                           $" ctime between '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and '{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
            }

            string countSql = "select count(id) as num  from operation_log";
            if (!string.IsNullOrEmpty(argsSql))
            {
                countSql += " where " + argsSql;
            }
            var reader = db.ExecReader(countSql, MySqlParameters.ToArray());
            var count = 0;
            while (reader.Read())
            {
                 count = reader.GetInt32("num");
                payload["count"] = count;
                break;
            }

            reader.Close();
            if (!string.IsNullOrEmpty(argsSql))
            {
                sql +="where "+argsSql;
            }

            if (count < pageSize || count < (pageIndex * pageSize))
            {
                pageIndex = 1;
            }

            sql += $" order by ctime desc limit {(pageIndex - 1) * pageSize}, {pageSize}";
            JsonData list = new JsonData();
            try
            {
                reader = db.ExecReader(sql, MySqlParameters.ToArray());
                int BuffLengthMax = 500 * 1024;

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32("id");
                        string _account = reader.GetString("account");
                        string _method = reader.GetString("method");
                        string _url = reader.GetString("url");
                        int dataIndex = reader.GetOrdinal("data");
                        var ctime = reader.GetDateTime("ctime");
                        SSHttpApiDbReq record = new SSHttpApiDbReq();
                        byte[] buffer = new byte[BuffLengthMax];
                        long dataLen = reader.GetBytes(dataIndex, 0, buffer, 0, buffer.Length);
                        byte[] data = new byte[dataLen];
                        Buffer.BlockCopy(buffer, 0, data, 0, (int)dataLen);
                        data = ZipUtils.DecompressBytes(data);
                        StructMessageParseUtils.ParseFrom(ref record, data);
                        string dataStr = record.Data.ToString();
                        JsonData json = new JsonData();
                        var name = Resolver.GetUrlName(_url);

                        json["url"] = _url;
                        json["name"] = name;
                        json["account"] = _account;
                        json["method"] = _method;
                        json["id"] = id;
                        json["ctime"] = ctime.ToString("yyyy-MM-dd HH:mm:ss");
                        json["data"] = dataStr;
                        list.Add(json);

                    }

                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }



            payload["data"] = list;
            payload["ret"] = 0;
            payload["pageSize"] = pageSize;
            payload["pageIndex"] = pageIndex;

            return 0;

        }


        public override int InsertPreset(int id, string account, string realms, string items, string start, string end)
        {

            bool exist = false;
            if (id > 0)
            {
                string selectSql = "select * from player_preset where id=" + id;
                MySqlDataReader selectReader = null;
                try
                {
                    selectReader = db.ExecReader(selectSql);
                    if (selectReader == null)
                    {
                        return -1;
                    }

                    exist = selectReader.HasRows;
                    selectReader.Close();
                }
                catch (Exception e)
                {
                    TraceLog.Error("dbOperator.InsertPreset error  sql={0},info={1}", selectSql, e.Message);
                }
                finally
                {
                    if (selectReader != null)
                    {
                        selectReader.Close();
                    }
                }
            }

            if (exist)
            {
                var updateParams = new List<MySqlParameter>()
                {
                    new("?account", MySqlDbType.String) { Value = account },
                    new("?realms", MySqlDbType.String) { Value = realms },
                    new("?items", MySqlDbType.String) { Value = items },
                    new("?start_time", MySqlDbType.String) { Value = start },
                    new("?end_time", MySqlDbType.String) { Value = end },
                }.ToArray();
                var updateSql = "update player_preset set account=?account,realms=?realms,items=?items,start_time=?start_time,end_time=?end_time WHERE id=" + id;
                try
                {
                    MySqlDataReader updateReadr = db.ExecReader(updateSql, updateParams);
                    if (updateReadr != null)
                    {
                        updateReadr.Close();
                    }
                }
                catch (Exception e)
                {
                    TraceLog.Error("MySqlDBOperator.InsertOperationLog sql {0} failed,e={1}", updateSql, e.Message);
                }

                return 0;
            }

            var insertParams = new List<MySqlParameter>()
            {
                new("?account", MySqlDbType.String) { Value = account },
                new("?realms", MySqlDbType.String) { Value = realms },
                new("?items", MySqlDbType.String) { Value = items },
                new("?start_time", MySqlDbType.String) { Value = start },
                new("?end_time", MySqlDbType.String) { Value = end },
            }.ToArray();
            string sql =
                "insert into player_preset set account=?account,realms=?realms,items=?items,start_time=?start_time,end_time=?end_time;";
            MySqlDataReader insetrReadr = null;
            try
            {
                insetrReadr = db.ExecReader(sql, insertParams);
            }
            catch (Exception e)
            {
                TraceLog.Error("MySqlDBOperator.InsertOperationLog sql {0} failed,e={1}", sql, e.Message);
            }
            finally
            {
                insetrReadr?.Close();
            }

            return 0;
        }
        
        public override JsonData FindAllPreset(long Id)
        {
            
            string sql = "select * from player_preset";
            if (Id > 0)
            {
                sql += " where id=" + Id;
            }
            MySqlDataReader reader = null;
            JsonData data = new JsonData();
            try
            {
                reader = db.ExecReader(sql);
                if (reader == null)
                {
                    return data;
                }
                long now = OperationServerUtils.GetApp().GetTimeSecond();
                DateTime currentDate = DateTimeOffset.FromUnixTimeSeconds(now).LocalDateTime;
                while (reader.Read())
                {
                    long id = reader.GetInt64("id");
                    string account = reader.GetString("account");
                    string realms = reader.GetString("realms");
                    string items = reader.GetString("items");
                    string startTime = reader.GetString("start_time");
                    string endTime = reader.GetString("end_time");
                    string status = "生效中";
                    DateTime dateTimeEnd =
                        DateTime.ParseExact(endTime, AppTime.TIME_FORMAT_STYLE, CultureInfo.InvariantCulture);
                    DateTime dateTimeStart = DateTime.ParseExact(startTime, AppTime.TIME_FORMAT_STYLE,
                        CultureInfo.InvariantCulture);
                    if (AppTime.GetTimeSecond(currentDate) < AppTime.GetTimeSecond(dateTimeStart))
                    {
                        status = "等待中";
                    }
                    if (AppTime.GetTimeSecond(currentDate) > AppTime.GetTimeSecond(dateTimeEnd))
                    {
                        status = "已过期";
                    }
                    JsonData item = new JsonData();
                    item["id"] = id+"";
                    item["account"] = account;
                    item["realms"] =  realms;
                    item["items"] = items;
                    item["start_time"] = startTime;
                    item["end_time"] = endTime;
                    item["status"] = status;
                    data.Add(item);
                }
            }
            catch (Exception e)
            {
                TraceLog.Error("MySqlDBOperator.SelectPreSet sql {0} failed,e={1}", sql, e.Message);
            }
            finally
            {
                reader?.Close();
            }

            return data;
        }


        public override PresetData OnGetPresetWithRule(int reqVersion)
        {
            long now = OperationServerUtils.GetApp().GetTimeSecond();
            DateTime currentDate = DateTimeOffset.FromUnixTimeSeconds(now).LocalDateTime;
            var currentVersion = PlayerPresetOp.Version;
            PresetData presetData = new PresetData();
            presetData.Req = currentVersion;
            string sql = "select * from player_preset";
            MySqlDataReader reader = null;
            try
            {
                reader = db.ExecReader(sql);
                if (reader == null)
                {
                    return presetData;
                }

                while (reader.Read())
                {
                    long id = reader.GetInt64("id");
                    string account = reader.GetString("account");
                    string realms = reader.GetString("realms");
                    string items = reader.GetString("items");
                    var itemsList = items.Split("#");
                    string startTime = reader.GetString("start_time");
                    //
                    string endTime = reader.GetString("end_time");
                    DateTime dateTimeEnd =
                        DateTime.ParseExact(endTime, AppTime.TIME_FORMAT_STYLE, CultureInfo.InvariantCulture);
                    DateTime dateTimeStart = DateTime.ParseExact(startTime, AppTime.TIME_FORMAT_STYLE,
                        CultureInfo.InvariantCulture);
                    if (AppTime.GetTimeSecond(dateTimeEnd) < AppTime.GetTimeSecond(currentDate))
                    {
                        continue;
                    }

                    var preset = new PresetDataOne();
                    foreach (var item in itemsList)
                    {
                        var temp = item.Split("|");
                        TypeIDValue32 tpv = new()
                        {
                            Type = int.Parse(temp[0]),
                            Id = int.Parse(temp[1]),
                            Value = int.Parse(temp[2])
                        };
                        preset.Items.Add(tpv);
                    }

                    var realmList = realms.Split(",");
                    foreach (var ls in realmList)
                    {
                        if (!string.IsNullOrEmpty(ls))
                        {
                            preset.ReamIds.Add(long.Parse(ls));
                        }
                    }

                    preset.Id = id;
                    preset.Start_time = AppTime.GetTimeSecond(dateTimeStart);
                    preset.End_time = AppTime.GetTimeSecond(dateTimeEnd);
                    presetData.Data.Add(preset);
                }
            }
            catch (Exception e)
            {
                TraceLog.Error("MySqlDBOperator.SelectPreSet sql {0} failed,e={1}", sql, e.Message);
            }
            finally
            {
                reader?.Close();
            }

            return presetData;
        }

        public override int DeletePreSet(long id)
        {
            string sql = "delete from player_preset where id =" + id;
            MySqlDataReader reader = null;
            try
            {
                reader = db.ExecReader(sql);
                if (reader == null)
                {
                    return 0;
                }
            }
            catch (Exception e)
            { TraceLog.Error("MySqlDBOperator.DeletePreSet sql {0} failed,e={1}", sql, e.Message);
            }
            finally
            {
                reader?.Close();
            }

            return 0;
        }


    }
}