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 HLSchoolController
    {
        private readonly static string _admsConnection = ConfigurationManager.ConnectionStrings["CoreDBString"].ConnectionString;
        private readonly static string _misConnection = ConfigurationManager.ConnectionStrings["ConnectionPfcMisDBString"].ConnectionString;
        //中间库
        private static string _hlzjMiddleConnection = ConfigurationManager.ConnectionStrings["HLZJMiddleDBString"].ConnectionString;


        /// <summary>
        /// 往oracle中间库中同步数校部门信息
        /// </summary>
        /// <param name="_hlzjMiddleConnection">中间库连接地址</param>
        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(_hlzjMiddleConnection))
                {
                    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(_hlzjMiddleConnection))
                {
                    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(_hlzjMiddleConnection))
                {
                    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(_hlzjMiddleConnection))
                {
                    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 == 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(_hlzjMiddleConnection))
                {
                    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(_hlzjMiddleConnection))
                {
                    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 == 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(_hlzjMiddleConnection))
                {
                    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 SyncDormitoryBuild()
        {
            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(_hlzjMiddleConnection))
                {
                    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())");
                }
            }
        }


        #region 实体
        
        #endregion


        #region 公共方法
        
        #endregion
    }
}
