using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web;
using Dapper;
using DigitalSchoolApi.Models;
using Learun.Application.Organization;
using Learun.Util;
using Microsoft.AspNet.SignalR.Client;
using Newtonsoft.Json;
using Convert = System.Convert;

namespace DigitalSchoolApi.Controllers
{
    /// <summary>
    /// 长阳宿舍管理--晚归预警
    /// </summary>
    public class CYDormitoryReturnController
    {
        private readonly static string _admsConnection = ConfigurationManager.ConnectionStrings["CoreDBString"].ConnectionString;
        private readonly static string _misConnection = ConfigurationManager.ConnectionStrings["ConnectionPfcMisDBString"].ConnectionString;
        private static string _cyzjMiddleConnection = ConfigurationManager.ConnectionStrings["CYZJMiddleDBString"].ConnectionString;


        public static string TestOracle()
        {
            try
            {
                using (IDbConnection conn = new OracleConnection(_cyzjMiddleConnection))
                {
                    try
                    {
                        string result = "";
                        List<string> testtable = "ODS_DZZQKJCSJ,ODS_DYFZQKJCSJ,ODS_DJHDDYGBXXSJ,ODS_DJHDSHYKSJ,ODS_DJHDDYDHSJ,ODS_DJHDDYRCSJ,ODS_DJHDDYZTDRSJ,ODS_CJSXHDSJ,ODS_ZZCJSTHDSJ,ODS_DYHDSJ".Split(',').ToList();
                        foreach (var sqlitem in testtable)
                        {
                            string _sql = $"SELECT COUNT(*) FROM {sqlitem.ToUpper()} WHERE  IsPush = '0' ";
                            result+=conn.ExecuteScalar<string>(_sql);
                        }

                        return result;
                    }
                    catch (Exception e)
                    {
                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),998,'中间库读取异常','" +
                                e.Message + "',getdate())");
                        }

                        return "";
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return "";
            }
        }

        /// <summary>
        /// 超过设置的晚归时间后，推送到负责人
        /// </summary>
        /// <returns></returns>
        public static void LateReturnPush()
        {
            try
            {
                using (IDbConnection admscon = new SqlConnection(_admsConnection))
                {
                    admscon.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),998,'寝室晚归预警','开始推送',getdate())");

                    //读取信息推送管理-晚归预警推送（10）的配置
                    var informationPushEntity = admscon.QueryFirstOrDefault<Sys_InformationPushEntity>("select top 1 * from Sys_InformationPush where PushItem='10'");
                    if (informationPushEntity != null && informationPushEntity.Status == true)
                    {
                        using (IDbConnection conn = new SqlConnection(_misConnection))
                        {
                            //晚归规则
                            var ruleEntity = conn
                                .QueryFirstOrDefault<Acc_DormitoryRuleEntity>("select top 1 * from Acc_DormitoryRule where IsEnable=1");
                            if (ruleEntity != null)
                            {
                                //晚归时间
                                var LateReturnTime = ruleEntity.LateReturnTime;

                                //判断当前时间是否超过晚归时间
                                var datenow = DateTime.Now.ToString("yyyy-MM-dd");
                                //晚归时间
                                DateTime lateReturnTime = Convert.ToDateTime(datenow + " " + LateReturnTime);
                                //大于晚归时间
                                if (DateTime.Now > lateReturnTime)
                                {
                                    //获取晚归学生
                                    string sql =
                                        $@"select distinct d.StudentId,s.StuName,p.Class from [dbo].[Acc_DormitoryReturn] t join [dbo].[Acc_DormitoryBuild] d on t.DormitoryBuildId=d.Id
left join [dbo].[Acc_DormitoryBuild] p on d.ParentId=p.id
left join [dbo].[StuInfoBasic] s on d.StudentId=s.StuNo
where DateDiff(dd,t.CheckDate,getdate())=0 and t.OutTime<'{lateReturnTime}' and (t.ReturnTime is null or len(t.ReturnTime)=0)
";
                                    var returnList = conn.Query<DormitoryReturn>(sql);
                                    if (returnList.Count() > 0)
                                    {
                                        //推送人
                                        var pushUser = ruleEntity.PushUser.Split(',').ToList();
                                        var title = "寝室晚归预警";
                                        var contents = "";
                                        if (ruleEntity.ClassDiredctor == 1)
                                        {
                                            //推送班主任
                                            //根据班级分组
                                            var groupList = returnList.GroupBy(x => x.Class);
                                            foreach (var group in groupList)
                                            {
                                                var stunames = string.Join(",", group.Select(x => x.StuName + "(" + x.StudentId + ")").ToList());
                                                //获取班主任
                                                var ClassDiredctorNo =
                                                    conn.QueryFirstOrDefault<ClassInfoEntity>(
                                                            $"select * from ClassInfo where ClassNo='{group.Key}'")
                                                        ?.ClassDiredctorNo;
                                                var teacher = admscon.Query<UserEntity>($"select top 1 * from lr_base_user where f_encode='{ClassDiredctorNo}' and F_EnabledMark=1 and F_Description='教师'");
                                                if (teacher != null)
                                                {
                                                    contents = "班级：" + ClassDiredctorNo + "，学生：" + stunames;
                                                    //微信推送
                                                    try
                                                    {
                                                        PushWeixin(teacher.ToList(), title);
                                                    }
                                                    catch (Exception e)
                                                    {
                                                    }
                                                    PushFeixing(teacher.ToList(), title, contents);
                                                    //消息提醒
                                                    PushMessage(teacher.ToList(), title, contents);
                                                }
                                            }
                                        }

                                        //推送人
                                        var uids = string.Join("','", pushUser);
                                        var userList = admscon.Query<UserEntity>($"select * from lr_base_user where F_EnabledMark=1 and F_DeleteMark<>1 and f_userid in ('{uids}')");
                                        contents = "学生：";
                                        foreach (var stu in returnList)
                                        {
                                            contents += stu.StuName + "(" + "班级:" + stu.Class + ")" + "、";
                                        }

                                        if (contents.Contains("、"))
                                        {
                                            contents = contents.Substring(0, contents.Length - 1);
                                        }

                                        //微信推送
                                        try
                                        {
                                            PushWeixin(userList.ToList(), title);
                                        }
                                        catch (Exception e)
                                        {
                                        }

                                        //飞星推送
                                        PushFeixing(userList.ToList(), title, contents);

                                        //消息提醒
                                        PushMessage(userList.ToList(), title, contents);


                                    }
                                }


                            }

                        }
                    }
                }

            }
            catch (Exception ex)
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    conn.Execute(
                        $"insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),998,'寝室晚归预警推送发生异常','{ex.Message}',getdate())");
                }

            }

        }
        /// <summary>
        /// 推送微信
        /// </summary>
        /// <param name="needpostuserlist"></param>
        /// <param name="title"></param>
        public static void PushWeixin(List<UserEntity> needpostuserlist, string title)
        {
            using (IDbConnection admscon = new SqlConnection(_admsConnection))
            {
                var WeChatConfigentity = admscon.QueryFirstOrDefault<WeChatConfigEntity>("select top 1 * from WeChatConfig where IsEnable=1");
                if (WeChatConfigentity != null)
                {
                    var wechatemplete = admscon.QueryFirstOrDefault<WeChatTemplateEntity>($"select top 1 * from WeChatTemplate where WeID='{WeChatConfigentity.ID}' and TCode='task'");
                    if (wechatemplete != null)
                    {
                        string appid = WeChatConfigentity.APPId;
                        string secret = WeChatConfigentity.secret;
                        string weixintaskurl = wechatemplete.TUrl;
                        string weixintasktempid = wechatemplete.TempId;
                        var responsejson = HttpMethods.HttpGet("https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=" + appid + "&secret=" + secret);
                        foreach (UserEntity userinfo in needpostuserlist)
                        {
                            if (userinfo != null && !string.IsNullOrEmpty(userinfo.OpenIdForWeixin))
                            {
                                //执行推送任务
                                if (!string.IsNullOrEmpty(appid) && !string.IsNullOrEmpty(secret) && !string.IsNullOrEmpty(weixintaskurl) && !string.IsNullOrEmpty(weixintasktempid))
                                {
                                    if (!string.IsNullOrEmpty(responsejson))
                                    {
                                        var weixintokenobj = JsonConvert.DeserializeObject<dynamic>(responsejson);
                                        if (string.IsNullOrEmpty(weixintokenobj.errcode))
                                        {
                                            string access_token = weixintokenobj.access_token;
                                            string jsondata = "{\"touser\":\"" + userinfo.OpenIdForWeixin + "\"," +
                                                              "\"template_id\":\"" + weixintasktempid + "\"," +
                                                              "\"url\":\"" + weixintaskurl + "\"," +
                                                              "\"data\":{" +
                                                              "\"first\": {\"value\":\"您有新的寝室晚归预警\",\"color\":\"#173177\"}," +
                                                              "\"keyword1\":{\"value\":\"寝室晚归预警\",\"color\":\"#173177\"}," +
                                                              "\"keyword2\": {\"value\":\"" + title + "\",\"color\":\"#173177\"}," +
                                                              "\"keyword3\": {\"value\":\"待查看\",\"color\":\"#173177\"}," +
                                                              "\"keyword4\": {\"value\":\"您有新的寝室晚归预警需要查看【" + title + "】\",\"color\":\"#173177\"}" +
                                                              "}" +
                                                              "}";

                                            string pushresult = HttpMethods.HttpPost("https://api.weixin.qq.com/cgi-bin/message/template/send?access_token=" + access_token, jsondata);
                                        }
                                    }
                                }
                            }
                        }

                    }
                }

            }
        }
        /// <summary>
        /// 飞星推送
        /// </summary>
        /// <param name="needpostuserlist"></param>
        /// <param name="title"></param>
        /// <param name="contents"></param>
        public static void PushFeixing(List<UserEntity> needpostuserlist, string title, string contents)
        {
            Task.Run(async () =>
            {
                using (var hubConnection = new HubConnection(
                    ConfigurationManager.AppSettings["CommunicationServeraddress"]))
                {
                    var hubProxy = hubConnection.CreateHubProxy("SignalRHub");
                    await hubConnection.Start();
                    await hubProxy.Invoke("PushAnnouncement", "System", title,
                        ReplaceHtml(HttpUtility.HtmlDecode(contents)).Length < 20 ? ReplaceHtml(HttpUtility.HtmlDecode(contents)) : ReplaceHtml(HttpUtility.HtmlDecode(contents)).Substring(0, 20), "latereturnpush",
                        string.Join(",", needpostuserlist.Select(m => m.F_UserId)), "");
                }
            });
        }

        /// <summary>
        /// 推送消息提醒
        /// </summary>
        /// <param name="needpostuserlist"></param>
        /// <param name="title"></param>
        /// <param name="contents"></param>
        public static void PushMessage(List<UserEntity> needpostuserlist, string title, string contents)
        {
            using (IDbConnection admscon = new SqlConnection(_admsConnection))
            {
                foreach (UserEntity userinfo in needpostuserlist)
                {
                    string sql =
                        $"insert into MessageRemind(MessageId,ReceiptId,ReceiptName,SenderId,SenderName,TheTitle,TheContent,ConnectionUrl,SendTime,ReadSigns) values(newid(),'{userinfo.F_UserId}','{userinfo.F_RealName}','Syatem','管理员','晚归预警','{contents}','/LogisticsManagement/DormitoryReturn/Index',getdate(),0)";
                    admscon.Execute(sql);
                }

            }
        }

        /// <summary>
        /// 往oracle中间库中同步数校部门信息
        /// </summary>
        public static void SyncDepartment()
        {
            try
            {
                IEnumerable<DepartmentEntity> departmentList = null;

                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    departmentList = conn.Query<DepartmentEntity>("select t.F_DepartmentId,t.F_ParentId,t.F_EnCode,t.F_FullName,t.F_Manager,t.F_SchoolMasterId,t.F_Order from LR_Base_Department t where t.F_DeleteMark=0 and t.F_EnabledMark=1 ");
                }

                using (IDbConnection conn = new OracleConnection(_cyzjMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_Department");//success
                        foreach (var item in departmentList)
                        {
                            var sql2 = new StringBuilder();
                            sql2.Append("insert into DS_Department(Id,ParentId,Code,Name,ManagerId,SMId,\"ORDER\") ");
                            sql2.Append(" VALUES('" + item.F_DepartmentId + "','" + (item.F_ParentId == null ? "0" : item.F_ParentId) + "','" + item.F_EnCode + "','" + item.F_FullName + "','" + (item.F_Manager == null ? "" : item.F_Manager) + "','" + (item.F_SchoolMasterId == null ? "" : item.F_SchoolMasterId) + "'," + (item.F_Order == null ? 0 : item.F_Order) + ")");
                            conn.Execute(sql2.ToString());

                        }

                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步部门信息完成','部门同步数量：" +
                                departmentList.Count() + "条',getdate())");
                        }
                    }
                    catch (Exception e)
                    {
                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步部门信息异常','" +
                                e.Message + "',getdate())");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    conn.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步部门信息异常','错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 系部
        /// </summary>
        public static void SyncDept()
        {
            try
            {
                IEnumerable<CdDeptEntity> entityList = null;

                using (IDbConnection conn = new SqlConnection(_misConnection))
                {
                    entityList = conn.Query<CdDeptEntity>("select t.* from CdDept t where 1=1 ");
                }

                using (IDbConnection conn = new OracleConnection(_cyzjMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_Dept");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_Dept(Id,DeptNo,DeptName,DMId) VALUES('" + item.DeptId + "','" + item.DeptNo + "','" + item.DeptName + "','" + (item.DeptDirector == null ? "" : item.DeptDirector) + "')";
                            conn.Execute(sql2);
                        }

                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步系部信息完成','系部同步数量：" +
                                entityList.Count() + "条',getdate())");
                        }
                    }
                    catch (Exception e)
                    {
                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步系部信息异常','" +
                                e.Message + "',getdate())");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    conn.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步系部信息异常','错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 专业
        /// </summary>
        public static void SyncMajor()
        {
            try
            {
                IEnumerable<CdMajorEntity> entityList = null;

                using (IDbConnection conn = new SqlConnection(_misConnection))
                {
                    entityList = conn.Query<CdMajorEntity>("select t.ID,t.MajorNo,t.MajorName,t.LengthOfSchooling,t.DeptNo from CdMajor t where t.CheckMark=1 ");
                }

                using (IDbConnection conn = new OracleConnection(_cyzjMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_Major");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_Major(Id,DeptNo,MajorNo,zymc,A0425) VALUES('" + item.ID + "','" + (item.DeptNo == null ? "" : item.DeptNo) + "','" + item.MajorNo + "','" + item.MajorName + "'," + item.LengthOfSchooling + ")";
                            conn.Execute(sql2);
                        }

                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步专业信息完成','专业同步数量：" +
                                entityList.Count() + "条',getdate())");
                        }
                    }
                    catch (Exception e)
                    {
                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步专业信息异常','" +
                                e.Message + "',getdate())");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    conn.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步专业信息异常','错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 班级
        /// </summary>
        public static void SyncClassInfo()
        {
            try
            {
                IEnumerable<ClassInfoEntity> entityList = null;

                using (IDbConnection conn = new SqlConnection(_misConnection))
                {
                    entityList = conn.Query<ClassInfoEntity>("select t.* from ClassInfo t where t.CheckMark=1 ");
                }

                using (IDbConnection conn = new OracleConnection(_cyzjMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_ClassInfo");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_ClassInfo(Id,DeptNo,MajorNo,bjmc,bj,nj,ClassDiredctorNo,ClassTutorNo) VALUES('" + item.ClassId + "','" + (item.DeptNo == null ? "" : item.DeptNo) + "','" + item.MajorNo + "','" + item.ClassName + "','" + item.ClassNo + "','" + item.Grade + "','" + item.ClassDiredctorNo + "','" + (item.ClassTutorNo == null ? "" : item.ClassTutorNo) + "')";
                            conn.Execute(sql2);
                        }

                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步班级信息完成','班级同步数量：" +
                                entityList.Count() + "条',getdate())");
                        }
                    }
                    catch (Exception e)
                    {
                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步班级信息异常','" +
                                e.Message + "',getdate())");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    conn.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步班级信息异常','错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 教室
        /// </summary>
        public static void SyncClassRoom()
        {
            try
            {
                IEnumerable<ClassroomInfoEntity> entityList = null;

                using (IDbConnection conn = new SqlConnection(_misConnection))
                {
                    entityList = conn.Query<ClassroomInfoEntity>("select t.* from ClassroomInfo t where t.CheckMark=1 ");
                }

                using (IDbConnection conn = new OracleConnection(_cyzjMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_ClassRoom");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_ClassRoom(Id,ClassroomNo,ClassroomName,ContainStuNum) VALUES('" + item.ClassroomId + "','" + item.ClassroomNo + "','" + item.ClassroomName + "'," + item.ContainStuNum + ")";
                            conn.Execute(sql2);
                        }

                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步教室信息完成','教室同步数量：" +
                                entityList.Count() + "条',getdate())");
                        }
                    }
                    catch (Exception e)
                    {
                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步教室信息异常','" +
                                e.Message + "',getdate())");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    conn.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步教室信息异常','错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 学生
        /// </summary>
        public static void SyncStuInfo()
        {
            try
            {
                IEnumerable<StuInfoBasicEntity> entityList = null;

                using (IDbConnection conn = new SqlConnection(_misConnection))
                {
                    var basedbname = string.Empty;
                    if (_admsConnection.IndexOf("database=") > 0)
                    {
                        var aa = _admsConnection.Split(';').First(x => x.Contains("database="));
                        basedbname = aa.Substring(aa.IndexOf("=") + 1);
                    }
                    else if (_admsConnection.IndexOf("Initial Catalog=") > 0)
                    {
                        var aa = _admsConnection.Split(';').First(x => x.Contains("Initial Catalog="));
                        basedbname = aa.Substring(aa.IndexOf("=") + 1);
                    }

                    var sqlstring = new StringBuilder();
                    sqlstring.Append("select t.StuId,t.StuNo,t.DeptNo,t.MajorNo,t.Grade,t.ClassNo,t.StuName,t.GenderNo,t.Birthday,t.IdentityCardNo,t.mobile,u.F_UserId from StuInfoBasic t ");
                    sqlstring.Append(" left join " + basedbname + ".dbo.LR_Base_User u on t.StuNo=u.F_Account and t.StuName=u.F_RealName and t.IdentityCardNo=u.F_IdentityCardNo ");
                    sqlstring.Append(" where t.CheckMark=1 and u.F_UserId is not null and u.F_DeleteMark = 0 and u.F_EnabledMark=1  ");
                    entityList = conn.Query<StuInfoBasicEntity>(sqlstring.ToString());
                }

                using (IDbConnection conn = new OracleConnection(_cyzjMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_StuInfo");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_StuInfo(Id,xsxh,xsxm,DeptNo,MajorNo,bj,nj,A0111,A0107,sfzh,phone) VALUES('" + item.F_UserId + "','" + item.StuNo + "','" + item.StuName + "','" + item.DeptNo + "','" + item.MajorNo + "','" + item.ClassNo + "','" + item.Grade + "',to_date('" + item.Birthday + "', 'yyyy-mm-dd hh24:mi:ss'),'" + (item.GenderNo == true ? 1 : 0) + "','" + item.IdentityCardNo + "','" + (item.mobile == null ? "" : item.mobile) + "')";
                            conn.Execute(sql2);
                        }

                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步学生信息完成','学生同步数量：" +
                                entityList.Count() + "条',getdate())");
                        }
                    }
                    catch (Exception e)
                    {
                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步学生信息异常','" +
                                e.Message + "',getdate())");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    conn.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步学生信息异常','错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 教师
        /// </summary>
        public static void SyncEmpInfo()
        {
            try
            {
                IEnumerable<EmpInfoEntity> entityList = null;

                using (IDbConnection conn = new SqlConnection(_misConnection))
                {
                    var basedbname = string.Empty;
                    if (_admsConnection.IndexOf("database=") > 0)
                    {
                        var aa = _admsConnection.Split(';').First(x => x.Contains("database="));
                        basedbname = aa.Substring(aa.IndexOf("=") + 1);
                    }
                    else if (_admsConnection.IndexOf("Initial Catalog=") > 0)
                    {
                        var aa = _admsConnection.Split(';').First(x => x.Contains("Initial Catalog="));
                        basedbname = aa.Substring(aa.IndexOf("=") + 1);
                    }

                    var sqlstring = new StringBuilder();
                    sqlstring.Append("select t.EmpId,t.EmpNo,t.EmpName,t.GenderNo,t.Birthday,t.DeptNo,t.IdentityCardNo,t.mobile,t.F_DepartmentId,u.F_UserId from EmpInfo t ");
                    sqlstring.Append(" left join " + basedbname + ".dbo.LR_Base_User u on t.EmpNo=u.F_Account ");
                    sqlstring.Append(" where t.CheckMark=1 and u.F_UserId is not null and u.F_DeleteMark = 0 and u.F_EnabledMark=1 ");
                    entityList = conn.Query<EmpInfoEntity>(sqlstring.ToString());
                }

                using (IDbConnection conn = new OracleConnection(_cyzjMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_EmpInfo");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_EmpInfo(Id,jgh,xm,DepartmentId,DeptNo,A0111,A0107,sfzh,phone) VALUES('" + item.F_UserId + "','" + item.EmpNo + "','" + item.EmpName + "','" + item.F_DepartmentId + "','" + (item.DeptNo == null ? "" : item.DeptNo) + "',to_date('" + item.Birthday + "', 'yyyy-mm-dd hh24:mi:ss'),'" + (item.GenderNo == true ? 1 : 0) + "','" + item.IdentityCardNo + "','" + item.mobile + "')";
                            conn.Execute(sql2);
                        }

                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步教师信息完成','教师同步数量：" +
                                entityList.Count() + "条',getdate())");
                        }
                    }
                    catch (Exception e)
                    {
                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步教师信息异常','" +
                                e.Message + "',getdate())");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    conn.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步教师信息异常','错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 宿舍
        /// </summary>
        public static void SyncDormitoryBuild()
        {
            try
            {
                IEnumerable<Acc_DormitoryBuildEntity> entityList = null;

                using (IDbConnection conn = new SqlConnection(_misConnection))
                {
                    var basedbname = string.Empty;
                    if (_admsConnection.IndexOf("database=") > 0)
                    {
                        var aa = _admsConnection.Split(';').First(x => x.Contains("database="));
                        basedbname = aa.Substring(aa.IndexOf("=") + 1);
                    }
                    else if (_admsConnection.IndexOf("Initial Catalog=") > 0)
                    {
                        var aa = _admsConnection.Split(';').First(x => x.Contains("Initial Catalog="));
                        basedbname = aa.Substring(aa.IndexOf("=") + 1);
                    }

                    var sqlstring = new StringBuilder();
                    sqlstring.Append("select t.* from Acc_DormitoryBuild t ");
                    sqlstring.Append(" where 1=1 and t.DNo is not null ");
                    entityList = conn.Query<Acc_DormitoryBuildEntity>(sqlstring.ToString());
                }

                using (IDbConnection conn = new OracleConnection(_cyzjMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_DormitoryBuild");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_DormitoryBuild(ID,DNo,Name,ApartmentId,UnitId,FloorId,Functionary,Leader,BuildType,jg,phone,Address,Campus,A0107,ParentID,HasToilet,Starred,sort,RoomType,CheckInStu,xsxh,Dept,Major,Class,bj) VALUES('" + item.ID + "','" + item.DNo + "','" + item.Name + "','" + item.ApartmentId + "','" + item.UnitId + "','" + item.FloorId + "','" + item.Functionary + "','" + item.Leader + "','" + item.BuildType + "'," + (item.Price == null ? 0 : item.Price) + ",'" + item.Phone + "','" + item.Address + "','" + item.Campus + "','" + item.Sex + "','" + item.ParentID + "','" + item.HasToilet + "','" + item.Starred + "'," + (item.sort == null ? 0 : item.sort) + "," + (item.RoomType == null ? 0 : item.RoomType) + "," + (item.CheckInStu == null ? 0 : item.CheckInStu) + ",'" + item.StudentID + "','" + item.Dept + "','" + item.Major + "','" + item.Class + "','" + item.MainClass + "')";
                            conn.Execute(sql2);
                        }

                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步宿舍信息完成','宿舍同步数量：" +
                                entityList.Count() + "条',getdate())");
                        }
                    }
                    catch (Exception e)
                    {
                        using (IDbConnection conncore = new SqlConnection(_admsConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步宿舍信息异常','" +
                                e.Message + "',getdate())");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    conn.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'往长阳中间库同步宿舍信息异常','错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }


        #region 实体
        //信息推送管理
        public class Sys_InformationPushEntity
        {
            public string Id { get; set; }
            /// <summary>
            /// 开关状态
            /// </summary>
            public bool? Status { get; set; }
        }

        //晚归规则
        public class Acc_DormitoryRuleEntity
        {
            public string LateReturnTime { get; set; }
            public string PushUser { get; set; }
            public int? ClassDiredctor { get; set; }

        }

        public class DormitoryReturn
        {
            public string StudentId { get; set; }
            public string StuName { get; set; }
            public string Class { get; set; }

        }

        #endregion


        #region 公共方法

        public static string ReplaceHtml(string html)
        {
            if (!string.IsNullOrEmpty(html))
            {
                string result = Regex.Replace(html, @"<[^>]*>", "").Replace("&nbsp;", "");
                return result;
            }
            else
            {
                return html;
            }
        }

        #endregion
    }
}
