using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Web;
using Dapper;
using DigitalSchoolApi.App_Data;
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 TLMSchoolController
    {
        private readonly static string _admsConnection = ConfigurationManager.ConnectionStrings["CoreDBString"].ConnectionString;
        private readonly static string _misConnection = ConfigurationManager.ConnectionStrings["ConnectionPfcMisDBString"].ConnectionString;
        private static string _tlmzyMiddleConnection = ConfigurationManager.ConnectionStrings["TLMZYMiddleDBString"].ConnectionString;


        /// <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);
                                                }


                                            }
                                        }

                                        if (ruleEntity.CoachTeach == 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 CoachTeachNo =
                                                    conn.QueryFirstOrDefault<ClassInfoEntity>(
                                                            $"select * from ClassInfo where ClassNo='{group.Key}'")
                                                        ?.ClassTutorNo;
                                                var coachteacher = admscon.Query<UserEntity>(
                                                    $"select top 1 * from lr_base_user where f_encode='{CoachTeachNo}' and F_EnabledMark=1 and F_Description='教师'");
                                                if (coachteacher != null)
                                                {
                                                    contents = "班级：" + CoachTeachNo + "，学生：" + stunames;
                                                    //微信推送
                                                    try
                                                    {
                                                        PushWeixin(coachteacher.ToList(), title);
                                                    }
                                                    catch (Exception e)
                                                    {
                                                    }

                                                    PushFeixing(coachteacher.ToList(), title, contents);
                                                    //消息提醒
                                                    PushMessage(coachteacher.ToList(), title, contents);
                                                }
                                            }
                                        }

                                        if (ruleEntity.Defend == 1)
                                        {
                                            var Department = ruleEntity.DefendId;
                                            var bwcPeoPle = admscon.Query<UserEntity>($"select * from lr_base_user where F_EnabledMark=1 and F_DeleteMark<>1 and F_DepartmentId ='{Department}'");
                                            if (bwcPeoPle != null)
                                            {
                                                contents = "学生：";
                                                foreach (var stu in returnList)
                                                {
                                                    contents += stu.StuName + "(" + "班级:" + stu.Class + ")" + "、";
                                                }

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

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

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

                                                //消息提醒
                                                PushMessage(bwcPeoPle.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>
        /// 锁定超过60天未登录的教师用户
        /// </summary>
        public static void CloseAccount()
        {
            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(),55555,'关闭账号','开始推送',getdate())");

                    DateTime Aborttime = DateTime.Now.AddDays(-60);
                    var LoginList = admscon.Query<CloseUserEntity>(
                            @" select t.F_UserId,t.F_EnCode,t.F_Account,t.F_RealName,l.LastTime FROM LR_Base_User t 
                                left join LR_Base_Department b on t.F_DepartmentId=b.F_DepartmentId 
                                left join (select F_OperateUserId,max(F_OperateTime) as LastTime from LR_BASE_LOG group by F_OperateUserId) l 
                                on t. F_UserId=l.F_OperateUserId
                                WHERE t.F_DeleteMark = 0 AND t.F_CompanyId = '207fa1a9-160c-4943-a89b-8fa4db0547ce' and t.F_Description='教师' and  t.F_EnabledMark=1 ");
                    var EnCode = LoginList.Where(x => x.LastTime <= Aborttime).Select(x => x.F_EnCode).ToList();
                    foreach (var item in EnCode)
                    {
                        admscon.Execute("update LR_Base_User set F_EnabledMark=0 where F_Description='教师' and  F_EnabledMark=1 and F_Account ='" + item + "'");
                    }
                    admscon.Execute(
                        $"insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),55555,'本次关闭账号" + EnCode.Count + "个','{EnCode.Count}',getdate())");
                }
            }
            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(),55555,'关闭账号发生异常','{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}','System','管理员','晚归预警','{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(_tlmzyMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_Department");//success
                        foreach (var item in departmentList)
                        {
                            var sql2 = $"insert into DS_Department(Id,ParentId,Code,Name,ManagerId,SMId,IOrder) VALUES('" + item.F_DepartmentId + "','" + (item.F_ParentId == null ? "" : 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);
                        }

                        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(_tlmzyMiddleConnection))
                {
                    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(_tlmzyMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_Major");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_Major(Id,DeptNo,MajorNo,MajorName,EduSystem) 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(_tlmzyMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_ClassInfo");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_ClassInfo(Id,DeptNo,MajorNo,ClassName,ClassNo,Grade,ClassDiredctorNo,ClassTutorNo) VALUES('" + item.ClassId + "','" + item.DeptNo + "','" + item.MajorNo + "','" + item.ClassName + "','" + item.ClassNo + "','" + item.Grade + "','" + (item.ClassDiredctorNo == null ? "" : 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(_tlmzyMiddleConnection))
                {
                    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(_tlmzyMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_StuInfo");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_StuInfo(Id,StuNo,StuName,DeptNo,MajorNo,ClassNo,Grade,Birth,GenderNo,IdentityNo,Mobile) 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 + "')";
                            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(_tlmzyMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_EmpInfo");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_EmpInfo(Id,EmpNo,EmpName,DepartmentId,DeptNo,Birth,GenderNo,IdentityNo,Mobile) 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 SyncDorm()
        {
            try
            {
                IEnumerable<Acc_DormitoryBuildEntity> entityList = null;

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

                using (IDbConnection conn = new OracleConnection(_tlmzyMiddleConnection))
                {
                    try
                    {
                        conn.Execute($"truncate table DS_DORMITORY");//success
                        foreach (var item in entityList)
                        {
                            var sql2 = $"insert into DS_DORMITORY(id, dparentid, stuno, dno, dname, dtype) VALUES('" + item.ID + "','" + item.ParentID + "','" + item.StudentID + "','" + item.DNo + "','" + item.Name + "','" + item.BuildType + "')";
                            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 SendEmailToTeacher(string fromUserName, string fromPassword, string fromName, string smtpServer, int smtpPort)
        {
            try
            {
                //发送教职工提醒邮件成功数量
                int num = 0;
                //发送邮件，一次件数，一次发5条
                int sendtemp = 0;
                using (IDbConnection conncore = 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 emplist = conncore.Query<EmpInfoEntity>($@"select top 3 t.*,u.F_UserId from EmpInfo t 
                        left join {basedbname}.dbo.LR_Base_User u on t.EmpNo=u.F_Account 
                        where t.CheckMark=1 and t.EMail is not null and u.F_UserId is not null  ").ToList();

                    //保存通知公告应读人员
                    if (SaveNewsShouldRead())
                    {
                        foreach (var empitem in emplist)
                        {
                            //判断邮箱是否有效
                            if (!string.IsNullOrEmpty(empitem.EMail))
                            {
                                using (IDbConnection conncore2 = new SqlConnection(_admsConnection))
                                {
                                    //获取未读的通知公告
                                    var NotReadNum = conncore2.QueryFirstOrDefault<int>($@"select count(t.SRRewId) from LR_OA_NewsShouldRead t 
                                    left join LR_OA_NewsRead r on t.NewsId=r.NewsId and t.RUserId=r.RUserId 
                                    where t.RUserId='{empitem.F_UserId}' and r.RNewsId is null");
                                    //获取未处理的流程
                                    var TaskListNum = conncore2.QueryFirstOrDefault<int>($@"select count(t.F_Id) FROM
	                               ( 
		                                SELECT F_TaskId FROM LR_NWF_TaskRelation r1
                                        LEFT JOIN LR_NWF_Task t1 ON r1.F_TaskId = t1.F_Id 
                                        WHERE r1.F_Mark = 0 AND r1.F_Result = 0 AND (r1.F_UserId  = '{empitem.F_UserId}' ) 
										GROUP BY F_TaskId
	                                ) r
                                LEFT JOIN LR_NWF_Task t ON t.F_Id = r.F_TaskId
                                LEFT JOIN LR_NWF_Process p ON p.F_Id = t.F_ProcessId
                                WHERE t.F_IsFinished = 0  AND (p.F_IsFinished = 0 OR t.F_Type = 2 OR t.F_Type = 4 OR t.F_Type = 6)");
                                    //获取本月登录次数
                                    var LoginNum = conncore2.QueryFirstOrDefault<int>($@"select count(bb.day) from (
                                select DAY(t.F_OperateTime) as day from LR_Base_Log t where t.F_CategoryId=1 and t.F_OperateUserId='{empitem.F_UserId}' and YEAR(t.F_OperateTime)='{DateTime.Now.Year}' and MONTH(t.F_OperateTime)='{DateTime.Now.Month}'
                                group by DAY(t.F_OperateTime)
                                ) bb");
                                    //发送邮件
                                    try
                                    {
                                        //var sendResult = MailHelper.Send2(fromUserName, fromPassword, fromName, smtpServer, smtpPort, empitem.EMail, "教职工提醒",
                                        //    $@"尊敬的用户：<br/>
                                        //             &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;
                                        //            1、你有{NotReadNum}个通知公告未读；<br/>
                                        //             &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;
                                        //            2、你有{TaskListNum}个流程没有处理；<br/>
                                        //             &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;
                                        //            3、学院要求每日登陆一次智慧校园，请及时登录，本月登陆{LoginNum}次。<br/>
                                        //            &nbsp;&nbsp;&nbsp;&nbsp;
                                        //            谢谢!
                                        //        ");
                                        var sendResult = MailHelper.Send2(fromUserName, fromPassword, fromName, smtpServer, smtpPort, empitem.EMail, "教职工提醒",
                                            $@"尊敬的用户：<br/>
                                                     &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;
                                                    通知公告未读{NotReadNum}条；<br/>
                                                    &nbsp;&nbsp;&nbsp;&nbsp;
                                                    谢谢!
                                                ");
                                        //var sendResult = MailHelper.Send2(fromUserName, fromPassword, fromName, smtpServer, smtpPort, "18335927079@163.com", "教职工提醒",
                                        //    $@"1、你有{NotReadNum}个通知公告未读；2、你有{TaskListNum}个流程没有处理；3、学院要求每日登陆一次智慧校园，请及时登录，本月登陆{LoginNum}次。");

                                        using (IDbConnection conncore3 = new SqlConnection(_admsConnection))
                                        {
                                            conncore3.Execute(
                                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),997,'发送邮件提醒','" + fromUserName + "给" + empitem.EmpName + "老师(" + empitem.EmpNo + ")(" + empitem.EMail + ")发送成功，结果为：" +
                                                sendResult + "',getdate())");
                                        }
                                        if (sendResult)
                                        {
                                            num++;
                                            sendtemp++;
                                            if (sendtemp >= 5)
                                            {
                                                sendtemp = 0;
                                                Thread.Sleep(300000);//等待5分钟
                                            }
                                        }

                                    }
                                    catch (Exception e)
                                    {
                                        using (IDbConnection conncore3 = new SqlConnection(_admsConnection))
                                        {
                                            conncore3.Execute(
                                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),997,'发送邮件提醒','" + fromUserName + "给" + empitem.EmpName + "老师(" + empitem.EmpNo + ")(" + empitem.EMail + ")发送异常：" +
                                                e.Message + "',getdate())");
                                        }
                                    }
                                }
                            }
                        }

                        using (IDbConnection conncore4 = new SqlConnection(_admsConnection))
                        {
                            conncore4.Execute("insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),997,'发送邮件提醒','" + fromUserName + "给老师发送成功数量为" + num + "个',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(),997,'发送邮件提醒','错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 发送邮件提醒
        /// </summary>
        public static void SendEmailToTeacher2(string fromUserName, string fromPassword, string fromName, string smtpServer, int smtpPort, List<EmpInfoEntity> emplist)
        {
            try
            {
                //发送教职工提醒邮件成功数量
                int num = 0;
                using (IDbConnection conncore = new SqlConnection(_misConnection))
                {
                    foreach (var empitem in emplist)
                    {
                        //判断邮箱是否有效
                        if (!string.IsNullOrEmpty(empitem.EMail))
                        {
                            using (IDbConnection conncore2 = new SqlConnection(_admsConnection))
                            {
                                //获取未读的通知公告
                                var NotReadNum = conncore2.QueryFirstOrDefault<int>($@"select count(t.SRRewId) from LR_OA_NewsShouldRead t 
                                left join LR_OA_NewsRead r on t.NewsId=r.NewsId and t.RUserId=r.RUserId 
                                left join LR_OA_News n on t.NewsId=n.F_NewsId 
                                left join lr_base_user u on t.RUserId=u.F_UserId 
                                where t.RUserId='{empitem.F_UserId}' and r.RNewsId is null and n.F_NewsId is not null
                                and n.F_TypeId = 2 and n.F_DeleteMark = 0  and n.F_EnabledMark = 1
                                and n.F_ReleaseTime > CONVERT(datetime, SUBSTRING(u.F_Account, 1, 4) + '-08-31', 120) ");
                                //获取未处理的流程
                                var TaskListNum = conncore2.QueryFirstOrDefault<int>($@"select count(t.F_Id) FROM
	                            ( 
		                            SELECT F_TaskId FROM LR_NWF_TaskRelation r1
                                    LEFT JOIN LR_NWF_Task t1 ON r1.F_TaskId = t1.F_Id 
                                    WHERE r1.F_Mark = 0 AND r1.F_Result = 0 AND (r1.F_UserId  = '{empitem.F_UserId}' ) 
									GROUP BY F_TaskId
	                            ) r
                            LEFT JOIN LR_NWF_Task t ON t.F_Id = r.F_TaskId
                            LEFT JOIN LR_NWF_Process p ON p.F_Id = t.F_ProcessId
                            WHERE t.F_IsFinished = 0  AND (p.F_IsFinished = 0 OR t.F_Type = 2 OR t.F_Type = 4 OR t.F_Type = 6)");
                                //获取本月登录次数
                                var LoginNum = conncore2.QueryFirstOrDefault<int>($@"select count(bb.day) from (
                            select DAY(t.F_OperateTime) as day from LR_Base_Log t where t.F_CategoryId=1 and t.F_OperateUserId='{empitem.F_UserId}' and YEAR(t.F_OperateTime)='{DateTime.Now.Year}' and MONTH(t.F_OperateTime)='{DateTime.Now.Month}'
                            group by DAY(t.F_OperateTime)
                            ) bb");
                                //发送邮件
                                try
                                {
                                    var sendResult = MailHelper.Send2(fromUserName, fromPassword, fromName, smtpServer, smtpPort, empitem.EMail, "教职工提醒",
                                        $@"尊敬的用户：<br/>
                                                 &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;
                                                1、您有{NotReadNum}个通知公告未读；<br/>
                                                 &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;
                                                2、您有{TaskListNum}个流程没有处理；<br/>
                                                 &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;
                                                3、学院要求每日登陆一次智慧校园，请及时登陆，本月已登陆{LoginNum}天。<br/>
                                                &nbsp;&nbsp;&nbsp;&nbsp;
                                                谢谢!
                                            ");

                                    //using (IDbConnection conncore3 = new SqlConnection(_admsConnection))
                                    //{
                                    //    conncore3.Execute(
                                    //        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),997,'发送邮件提醒','" + fromUserName + "给" + empitem.EmpName + "老师(" + empitem.EmpNo + ")(" + empitem.EMail + ")发送成功，结果为：" +
                                    //        sendResult + "',getdate())");
                                    //}
                                    if (sendResult)
                                    {
                                        num++;
                                        Thread.Sleep(60000);//等待1分钟
                                    }

                                }
                                catch (Exception e)
                                {
                                    using (IDbConnection conncore3 = new SqlConnection(_admsConnection))
                                    {
                                        conncore3.Execute(
                                            "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),997,'发送邮件提醒','" + fromUserName + "给" + empitem.EmpName + "老师(" + empitem.EmpNo + ")(" + empitem.EMail + ")发送异常：" +
                                            e.Message + "',getdate())");
                                    }
                                }
                            }
                        }
                    }

                    using (IDbConnection conncore4 = new SqlConnection(_admsConnection))
                    {
                        conncore4.Execute("insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),997,'发送邮件提醒','" + fromUserName + "给老师发送成功数量为" + num + "个',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(),997,'发送邮件提醒','开始发送邮件-错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 保存通知公告应读人员
        /// </summary>
        public static bool SaveNewsShouldRead()
        {
            var result = false;
            using (IDbConnection connection = new SqlConnection(_admsConnection))
            {
                try
                {
                    var sql =
               @"SELECT case when (F_SendDeptId is null or len(F_SendDeptId)=0) and (F_SendPostId is null or len(F_SendDeptId)=0) then '1' else '' end  as F_Send,
 F_SendDeptId,F_SendPostId,* FROM LR_OA_News t WHERE t.F_TypeId = 2 and t.F_DeleteMark=0  and t.F_EnabledMark=1
  and t.f_Newsid not in (SELECT distinct Newsid from [dbo].[LR_OA_NewsShouldRead])";

                    var list = connection.Query<NewsEntity>(sql);
                    foreach (var news in list)
                    {
                        if (!string.IsNullOrEmpty(news.F_Send) && news.F_Send == "1")
                        {
                            //接收人是全部教师
                            connection.Execute($@"insert into LR_OA_NewsShouldRead
SELECT newid(),'{news.F_NewsId}',f_userid from lr_base_user where f_description='教师'  and 
f_userid not in (
SELECT ruserid from [dbo].[LR_OA_NewsShouldRead] where newsid='{news.F_NewsId}'
)
");
                        }
                        else
                        {
                            //接收部门
                            if (!string.IsNullOrEmpty(news.F_SendDeptId))
                            {
                                var deptids = string.Join("','", news.F_SendDeptId.Split(','));
                                connection.Execute($@"insert into LR_OA_NewsShouldRead
SELECT newid(),'{news.F_NewsId}',f_userid from lr_base_user where f_description='教师' and F_DepartmentId in ('{deptids}')
and f_userid not in (
SELECT ruserid from [dbo].[LR_OA_NewsShouldRead] where newsid='{news.F_NewsId}'
)");
                                //岗位
                                if (!string.IsNullOrEmpty(news.F_SendPostId))
                                {
                                    var postids = string.Join("','", news.F_SendPostId.Split(','));
                                    connection.Execute($@"insert into LR_OA_NewsShouldRead
SELECT newid(),'{news.F_NewsId}',t.f_userid from lr_base_user t 
join LR_Base_UserRelation r on r.f_category='2' and t.F_UserId=r.f_userid 
where t.f_description='教师' and r.f_objectid in ('{postids}')
 and t.F_DepartmentId not in ('{deptids}')
and t.f_userid not in (
SELECT ruserid from [dbo].[LR_OA_NewsShouldRead] where newsid='{news.F_NewsId}'
)");
                                }
                            }
                            //接收部门空 ， 接收岗位不为空
                            if (string.IsNullOrEmpty(news.F_SendDeptId) && !string.IsNullOrEmpty(news.F_SendPostId))
                            {
                                var postids = string.Join("','", news.F_SendPostId.Split(','));
                                connection.Execute($@"insert into LR_OA_NewsShouldRead
SELECT newid(),'{news.F_NewsId}',t.f_userid from lr_base_user t 
join LR_Base_UserRelation r on r.f_category='2' and t.F_UserId=r.f_userid 
where t.f_description='教师' and r.f_objectid in ('{postids}') 
and t.f_userid not in (
SELECT ruserid from [dbo].[LR_OA_NewsShouldRead] where newsid='{news.F_NewsId}'
)");
                            }
                        }

                    }

                    result = true;

                    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(),997,'保存通知公告应读人员','保存成功',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(),997,'保存通知公告应读人员','错误信息：" +
                            e.Message + "',getdate())");
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 分配发送邮件
        /// </summary>
        public static void AssignSendEmail(List<EmailManagementEntity> emailManagementList)
        {
            try
            {
                //保存通知公告应读人员
                if (SaveNewsShouldRead())
                {
                    using (IDbConnection conncore = 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 emplist = conncore.Query<EmpInfoEntity>($@"select t.*,u.F_UserId from EmpInfo t 
                        left join {basedbname}.dbo.LR_Base_User u on t.EmpNo=u.F_Account 
                        where t.CheckMark=1 and t.EMail is not null and len(t.EMail)>0 
                        and ((len(t.IsInActiveStatus)>0 and t.IsInActiveStatus <> 0) or len(t.IsInActiveStatus)<=0) 
                        and u.F_UserId is not null  ").ToList();
                        if (emplist.Any())
                        {
                            //每个邮箱分配多个教师（前面都是平均数，最后一个是剩余的）
                            //平均数
                            var average = emplist.Count() / emailManagementList.Count();
                            foreach (var entity in emailManagementList)
                            {
                                var index = emailManagementList.IndexOf(entity);
                                if (index == emailManagementList.Count() - 1)//最后一个
                                {
                                    var emplisttemp = emplist.Skip(index * average).ToList();
                                    using (IDbConnection conncore4 = new SqlConnection(_admsConnection))
                                    {
                                        conncore4.Execute("insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),997,'发送邮件提醒','" + entity.F_Account + "开始给老师发送，发送数量为" + emplisttemp.Count() + "个',getdate())");
                                    }
                                    //发送
                                    SendEmailToTeacher2(entity.F_Account, entity.F_AuthorizeCode, entity.F_SenderName, entity.F_SMTPServer, entity.F_SMTPPort.Value, emplisttemp);
                                }
                                else
                                {
                                    var emplisttemp = emplist.Skip(index * average).Take(average).ToList();
                                    using (IDbConnection conncore4 = new SqlConnection(_admsConnection))
                                    {
                                        conncore4.Execute("insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),997,'发送邮件提醒','" + entity.F_Account + "开始给老师发送，发送数量为" + emplisttemp.Count() + "个',getdate())");
                                    }
                                    //发送
                                    SendEmailToTeacher2(entity.F_Account, entity.F_AuthorizeCode, entity.F_SenderName, entity.F_SMTPServer, entity.F_SMTPPort.Value, emplisttemp);
                                }
                            }
                            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(),997,'发送邮件提醒','结束发送邮件',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(),997,'发送邮件提醒','分配发送邮件-错误信息：" +
                        e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 固定资产：自动更新已提折旧月数
        /// </summary>
        public static void UpdateHaveDepreciationMonths()
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_admsConnection))
                {
                    var sql = "select * from Ass_FixAssets where Status = 2 and NetValue > 0 ";
                    var list = conn.Query<Ass_FixAssetsEntity>(sql);
                    var total = 0;
                    foreach (var item in list)
                    {
                        //计算：已提折旧月数
                        var haveDepreciationMonths = 0;
                        if (item.FinanceEntryDate.HasValue)
                        {
                            haveDepreciationMonths = getDistanceMonth(item.FinanceEntryDate.Value.Year, item.FinanceEntryDate.Value.Month, item.FinanceEntryDate.Value.Day) - 1;
                            if (haveDepreciationMonths < 0)
                            {
                                haveDepreciationMonths = 0;
                            }
                        }
                        //计算：累计折旧
                        var accumulatedDepreciation = Convert.ToDecimal((Convert.ToDecimal(item.MonthlyDepreciation) * Convert.ToInt32(haveDepreciationMonths)).ToString("f2"));
                        //计算：净值
                        var netValue = Convert.ToDecimal(item.Value) - Convert.ToDecimal(accumulatedDepreciation);
                        if (netValue < 0)
                        {
                            netValue = 0;
                        }
                        var sqlInsert = $"update Ass_FixAssets set HaveDepreciationMonths={haveDepreciationMonths},AccumulatedDepreciation={accumulatedDepreciation},NetValue={netValue},AutoUpdateTime='{DateTime.Now}' where FAId='{item.FAId}' ";
                        conn.Execute(sqlInsert);
                        total++;
                    }
                    conn.Execute(
                        $"insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),996,'固定资产：自动更新已提折旧月数','已完成固定资产更新数量为：{total}',getdate())");
                }
            }
            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(),996,'固定资产：自动更新已提折旧月数','异常：{ex.Message}',getdate())");
                }

            }
        }
        /// <summary>
        /// 某日期-今 的相差月数
        /// </summary>
        /// <returns></returns>
        public static int getDistanceMonth(int compareYear, int compareMonth, int compareDay)
        {
            var date = DateTime.Now;
            //var date = new DateTime(2023, 2, 28);
            var year = date.Year;
            var month = date.Month;
            var day = date.Day;
            var yearToMonth = (year - compareYear) * 12;
            var monthToMonth = month - compareMonth;
            var dayToMonth = 0;
            if (day >= compareDay)
            {
                dayToMonth = 0;
            }
            else
            {
                dayToMonth = -1;
            }
            return yearToMonth + monthToMonth + dayToMonth;
        }



        #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 int? CoachTeach { get; set; }
            public int? Defend { get; set; }
            public string DefendId { get; set; }
        }

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

        }

        public class UserRelationEntity
        {
            public string F_UserRelationId { get; set; }
            public string F_UserId { get; set; }
            public int? F_Category { get; set; }
            public string F_ObjectId { get; set; }
            public DateTime? F_CreateDate { get; set; }
            public string F_CreateUserId { get; set; }
            public string F_CreateUserName { get; set; }
        }

        public class CloseUserEntity
        {
            public string F_UserId { get; set; }
            public string F_EnCode { get; set; }
            public string F_Account { get; set; }
            public string F_RealName { get; set; }
            public DateTime? LastTime { 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
    }
}
