using Adf.Core.Db;
using Adf.Core.Entity;
using Adf.Core.Util;
using Aspose.Cells;
using CompanyServer.CustomModel;
using CYQ.Data;
using CYQ.Data.Table;
using CYQ.Data.Tool;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using System.Windows.Forms;

namespace CompanyServer
{
    public class SyncDataHelper
    {

        //private static readonly string jkConnection = @"Data Source=192.168.2.64;Initial Catalog=Adf_Report_Shai;Persist Security Info=True;User ID=sa;Password=pass1234";
        private static readonly string jkGetConnection = ConfigurationManager.ConnectionStrings["JKGetConnection"].ConnectionString;
        private static readonly string jkSendConnection = ConfigurationManager.ConnectionStrings["JKSendConnection"].ConnectionString;

        //private static readonly string jkGetConnection = @"Data Source=yejkzjk.hpe.cn,8962;Initial Catalog=HealthMonitor_Exchange_TSYBQQ;Persist Security Info=True;User ID=YBQQUser;Password=yybq@2021";
        //private static readonly string jkSendConnection = @"Data Source=yejkzjk.hpe.cn,8962;Initial Catalog=HealthMonitor_Exchange_YBQQHZ;Persist Security Info=True;User ID=YBQQUser;Password=yybq@2021";

        private static readonly SyncDataHelper instance = new SyncDataHelper();
        private static string logpath = Application.StartupPath + "\\log\\";
        /// <summary>
        /// 显式的静态构造函数用来告诉C#编译器在其内容实例化之前不要标记其类型
        /// </summary>
        static SyncDataHelper() { }

        private SyncDataHelper() { }

        public static SyncDataHelper Instance
        {
            get
            {
                return instance;
            }
        }

        /// <summary>
        /// 查询黄浦区公立幼儿园全部数据
        /// </summary>
        const string BasicSqlForYQZB = @" SELECT   * FROM   report_school WHERE  report_school.CountyName='黄浦区' AND  SchoolNature='公立' AND SchoolTypeName='幼儿园' {0}  ";

        /// <summary>
        /// 健康平台中学校的信息
        /// </summary>
        const string BasicSqlForJKOrg = @" SELECT  * from  JK_OrgInfo WHERE  JK_OrgInfo.IsDeleted=0";

        /// <summary>
        /// 健康平台学校所有学生信息
        /// </summary>
        const string BasicSqlForJKChildren = @" SELECT
	                                                * 
                                                FROM
	                                                (
	                                                SELECT
		                                                JK_OrgInfo.SchoolID,
		                                                JK_OrgInfo.SchoolName,
		                                                JK_ChildInfo.* 
	                                                FROM
		                                                JK_OrgInfo
		                                                JOIN JK_ChildInfo ON JK_OrgInfo.OrgID = JK_ChildInfo.OrgID 
		                                                AND JK_OrgInfo.AreaID = JK_ChildInfo.AreaID 
		                                                AND JK_OrgInfo.AreaName= JK_ChildInfo.AreaName 
	                                                WHERE
		                                                JK_OrgInfo.SchoolID= '{0}' 
		                                                AND JK_OrgInfo.IsDeleted= {1} 
	                                                AND JK_ChildInfo.IsDeleted={1} 
                                                    AND JK_ChildInfo.Status='{2}'
	                                                ) TEMP";

        /// <summary>
        /// 缺勤中对应学校的学生信息
        /// </summary>
        //const string BasicSqlForQQStudent = @"SELECT
        //                                     studentTemp.*,
        //                                     report_yqzb_other.OtherUserId 
        //                                    FROM
        //                                     (
        //                                     SELECT MAX
        //                                      ( report_student.studentcode ) studentcode,
        //                                      MAX ( report_student.createdate )  createdate,
        //                                      MAX(report_student.schoolcode) schoolcode
        //                                     FROM
        //                                      report_student
        //                                      JOIN report_school ON report_student.SchoolCode = report_school.schoolcode 
        //                                     WHERE
        //                                      report_school.CountyName= '黄浦区' 
        //                                      AND SchoolNature = '公立' 
        //                                      AND SchoolTypeName = '幼儿园' 
        //                                      AND  report_student.idcard!= '' 
        //                                     GROUP BY
        //                                      report_student.idcard 
        //                                     ) studentTemp
        //                                     JOIN report_yqzb_other ON studentTemp.studentcode= report_yqzb_other.YQZBStudentCode
        //                                        WHERE studentTemp.schoolcode='{0}' ";

        const string BasicSqlForQQStudent = @"SELECT
	                                            studentTemp.*,
	                                            report_yqzb_other.OtherUserId 
                                            FROM
	                                            (
	                                            SELECT  report_student.studentcode  studentcode,
		                                            report_student.createdate  createdate,
		                                             report_student.schoolcode  schoolcode,
													report_school.SchoolId SchoolID
	                                            FROM
		                                            report_student
		                                            JOIN report_school ON report_student.SchoolCode = report_school.schoolcode 
	                                            WHERE
		                                            report_school.CountyName= '黄浦区' 
		                                            AND SchoolNature = '公立' 
		                                            AND SchoolTypeName = '幼儿园' 
		                                            AND  report_student.idcard!= '' 
	                                                AND report_school.schoolcode='{0}'
                                                     AND report_student.IsValid=1
	                                            ) studentTemp
	                                            JOIN report_yqzb_other ON studentTemp.studentcode= report_yqzb_other.YQZBStudentCode ";

        const string BasicSendJKAbsenceSql = @"
                                            SELECT
	                                            report_student_absence.*,
	                                            report_yqzb_other.OtherUserId 
                                            FROM
	                                            report_student_absence
	                                            JOIN ( SELECT * FROM report_school WHERE report_school.CountyName= '黄浦区' AND SchoolNature = '公立' AND SchoolTypeName = '幼儿园' ) school ON report_student_absence.SchoolCode= school.SchoolCode 
	                                            AND CONVERT(VARCHAR(32),report_student_absence.CreateDate,23) ='{0}'
	                                            JOIN report_student ON report_student_absence.IdCard= report_student.idcard
	                                            JOIN report_yqzb_other ON report_yqzb_other.YQZBStudentCode= report_student.studentcode
                                            ";
        const string BasicSendJKAbsenceSqlTwoDayAgo = @"
                                            SELECT
	                                            report_student_absence.*,
	                                            report_yqzb_other.OtherUserId 
                                            FROM
	                                            report_student_absence
	                                            JOIN ( SELECT * FROM report_school WHERE report_school.CountyName= '黄浦区' AND SchoolNature = '公立' AND SchoolTypeName = '幼儿园' ) school ON report_student_absence.SchoolCode= school.SchoolCode 
	                                            AND CONVERT(VARCHAR(32),report_student_absence.ModifyDate,23) ='{0}'
	                                            JOIN report_student ON report_student_absence.IdCard= report_student.idcard
	                                            JOIN report_yqzb_other ON report_yqzb_other.YQZBStudentCode= report_student.studentcode
                                                WHERE CONVERT(VARCHAR(32),report_student_absence.AbsenceDate,23)='{1}' OR CONVERT(VARCHAR(32),report_student_absence.AbsenceDate,23)='{2}'
                                            ";



        const string BasicClassInfoForJKSql = @"SELECT  JK_ClassInfo.ClassName,JK_ClassInfo.Boy,JK_ClassInfo.Girl FROM  JK_ClassInfo WHERE  JK_ClassInfo.SchoolID={0}";
        const string BasicClassInfoForYQZBSql = @"SELECT  *FROM  report_schoolclass WHERE  report_schoolclass.SchoolCode ='{0}' ";



        const string DiseaseInfosForYQZBSql = @"  
                                                SELECT   report_absencedetail.AbsenceCode
                                                ,report_absencedetail.DiseaseName  
                                                ,report_absencedetail.OtherDiseaseName 
                                                ,report_diseasetype.DiseaseTypeCode SecondaryDiseaseCode
                                                ,report_diseasetype.DiseaseTypeName SecondaryDiseaseName
                                                ,diseaseroot.DiseaseTypeCode PrimaryDiseaseCode
                                                ,diseaseroot.DiseaseTypeName PrimaryDiseaseName
                                                FROM    report_absencedetail 
                                                 JOIN report_diseasetype on report_absencedetail.DiseaseTypeCode=report_diseasetype.DiseaseTypeCode AND report_absencedetail.AbsenceCode='{0}'
                                                 JOIN report_diseasetype diseaseroot  ON diseaseroot.DiseaseTypeCode =report_diseasetype.ParentCode   
                                                ";

        /// <summary>
        /// 获取健康平台基础数据
        /// </summary>
        public void GetJKBasicsAndUpdateYQZBBasic(string yqzbConn)
        {
            //1.先从中间库读取到相关数据
            //2.循环中间库然后根据学校id 读取到对应的社区信息
            //3.更新社区名称
            //4.更新学校信息
            using (MAction action = new MAction(BasicSqlForJKOrg, jkGetConnection))
            {
                //中间库学校数据
                MDataTable jkOrgInfos = action.Select();

                if (jkOrgInfos != null && jkOrgInfos.Rows.Count > 0)
                {

                    MDataRow jkServiceConfig = DbService.GetOne("report_JKService_Config", " Platform='JKPlatform' ") ?? new MDataRow();
                    //健康平台服务同步配置表有数据，且GetJKBasicStatus 状态为未进行才可以同步
                    if (jkServiceConfig != null && jkServiceConfig.Get("GetDataStatus", "0") == "0")
                    {
                        using (MAction checkAction = new MAction("report_JKService_Config", yqzbConn))
                        {
                            jkServiceConfig.Set("GetDataStatus", 1);

                            checkAction.BeginTransation();
                            checkAction.Data.LoadFrom(jkServiceConfig);
                            if (!checkAction.Update(" Platform='JKPlatform' "))
                            {
                                LogHelper.WriteLog("更新同步配置jkServiceConfig:失败", logpath);
                                checkAction.RollBack();
                                checkAction.EndTransation();
                                return;
                            }
                            checkAction.EndTransation();

                            //循环中间库数据
                            foreach (MDataRow item in jkOrgInfos.Rows)
                            {
                                using (MAction qqAction = new MAction("report_school", yqzbConn))
                                {
                                    //根据中间库数据中缺勤的学校ID查找缺勤中的学校信息
                                    MDataRow schoolInfo = DbService.GetOne(string.Format(BasicSqlForYQZB, " AND report_school.SchoolId='" + item["SchoolID"] + "'"), "") ?? new MDataRow();
                                    #region 更新社区基本数据
                                    //学校社区ID不为空的情况下再去更新 （目前健康平台的数据没有社区概念貌似）
                                    //if (!string.IsNullOrEmpty(schoolInfo.Get("CommunityName", "")))
                                    //{
                                    //    try
                                    //    {
                                    //        MDataRow communityInfo = DbService.GetOne("report_community", "CommunityCode='" + schoolInfo.Get("CommunityName", "") + "'") ?? new MDataRow();
                                    //        //更新社区信息
                                    //        communityInfo.Set("CommunityName", item["ComName"]);
                                    //        communityInfo.Set("ModifyDate", DateTime.Now);

                                    //        action.ResetTable("report_community");
                                    //        action.BeginTransation();
                                    //        action.Data.LoadFrom(communityInfo);
                                    //        if (!action.Update("CommunityCode='" + schoolInfo.Get("CommunityName", "") + "'"))
                                    //        {
                                    //            action.RollBack();
                                    //        }
                                    //    }
                                    //    catch (Exception)
                                    //    {

                                    //        throw;
                                    //    }
                                    //    finally
                                    //    {
                                    //        action.EndTransation();
                                    //    }
                                    //}
                                    #endregion

                                    #region 更新学校基本数据
                                    try
                                    {
                                        string cityCountry = schoolInfo.Get("CityName", "上海市") + schoolInfo.Get("CountyName", "黄浦区");
                                        schoolInfo.Set("SchoolName", cityCountry + item["OrgName"] + item["AreaName"]);//上海市黄浦区**幼儿园总部
                                                                                                                       //schoolInfo.Set("Contacts", item["OrgContact"]);
                                                                                                                       //schoolInfo.Set("ContactsPhone", item["OrgPhone"]);
                                        schoolInfo.Set("ModifyDate", DateTime.Now);

                                        qqAction.BeginTransation();
                                        qqAction.Data.LoadFrom(schoolInfo);
                                        if (!qqAction.Update(" SchoolId='" + item["SchoolID"] + "' "))
                                        {
                                            qqAction.RollBack();
                                        }
                                        qqAction.EndTransation();
                                        #region 处理学校班级信息


                                        action.ResetTable(string.Format(BasicClassInfoForJKSql, item["SchoolID"]));

                                        var classInfos = action.Select() ?? new MDataTable();
                                        List<MDataRow> jkClassDataRow = classInfos.Rows.ToList<MDataRow>();

                                        var yqzbClassInfos = DbService.GetTable(string.Format(BasicClassInfoForYQZBSql, schoolInfo.Get("SchoolCode", ""))) ?? new MDataTable();
                                        List<MDataRow> yqzbClassInfosRow = yqzbClassInfos.Rows.ToList<MDataRow>();


                                        //该学校中相同的班级
                                        var sameClassInfos = jkClassDataRow.Where(o => yqzbClassInfosRow.Any(g => g.Get("SchoolClassName", "") == o.Get("ClassName", "")));
                                        qqAction.ResetTable("report_schoolclass");
                                        foreach (MDataRow sameClass in sameClassInfos)
                                        {
                                            qqAction.BeginTransation();
                                            qqAction.Set("SchoolCode", schoolInfo.Get("SchoolCode", ""));
                                            qqAction.Set("SchoolClassName", sameClass.Get("ClassName", ""));
                                            qqAction.Set("FemaleNumber", sameClass.Get("Girl", "0"));
                                            qqAction.Set("MaleNumber", sameClass.Get("Boy", "0"));
                                            if (!qqAction.Update(" report_schoolclass.SchoolClassName='" + sameClass.Get("ClassName", "") + "'  and  report_schoolclass.SchoolCode='" + schoolInfo.Get("SchoolCode", "") + "' "))
                                            {
                                                LogHelper.WriteLog("更新学校班级数据:失败" + schoolInfo.Get("SchoolCode", ""), logpath);
                                                qqAction.RollBack();
                                            }
                                            qqAction.EndTransation();
                                        }
                                        var diffClassInfos = jkClassDataRow.Except(sameClassInfos);

                                        foreach (MDataRow diffClass in diffClassInfos)
                                        {
                                            qqAction.BeginTransation();
                                            qqAction.Set("SchoolClassCode", Guid.NewGuid().ToString());
                                            qqAction.Set("SchoolCode", schoolInfo.Get("SchoolCode", ""));
                                            qqAction.Set("SchoolClassName", diffClass.Get("ClassName", ""));
                                            qqAction.Set("FemaleNumber", diffClass.Get("Girl", "0"));
                                            qqAction.Set("MaleNumber", diffClass.Get("Boy", "0"));
                                            if (!qqAction.Insert(InsertOp.None))
                                            {
                                                LogHelper.WriteLog("新增学校班级数据:失败" + schoolInfo.Get("SchoolCode", ""), logpath);
                                                qqAction.RollBack();
                                            }
                                            qqAction.EndTransation();
                                        }
                                        #endregion
                                    }
                                    catch (Exception ex)
                                    {
                                        LogHelper.WriteLog("更新学校基本数据:" + ex.Message, logpath);
                                        qqAction.EndTransation();
                                    }
                                    #endregion

                                    #region MyRegion 更新学校所属的学员信息

                                    /************
                                     * 1.查询出健康平台中未删除的学生数据，循环或批量去更新缺勤中学生信息
                                     * 2.找出被删除的学生,从健康和缺勤学生的关联表和学生基础信息表删除（其余学生关联表暂时不动）             
                                     * 3.数据要根据中间表学生id来处理，其次才是身份证号
                                     * *********/
                                    action.ResetTable(string.Format(BasicSqlForJKChildren, item["SchoolID"], 0, "在园"));
                                    //健康中该学校的所有学生数据
                                    MDataTable jkChildrenInfos = action.Select();
                                    List<MDataRow> childList = jkChildrenInfos.Rows.ToList<MDataRow>();
                                    MDataTable QQChildrenInfos = DbService.GetTable(string.Format(BasicSqlForQQStudent, schoolInfo.Get("SchoolCode", ""))) ?? new MDataTable();
                                    List<MDataRow> QQChildrenList = QQChildrenInfos.Rows.ToList<MDataRow>();

                                    //学生信息相同的数据
                                    IEnumerable<MDataRow> sameData = childList.Where(o => QQChildrenList.Count(g => g.Get("OtherUserId", "") == o.Get("ChildID", "") && g.Get("SchoolID", "") == o.Get("SchoolID", "")) == 1);
                                    //需要新增的健康平台学生数据
                                    IEnumerable<MDataRow> diffData = childList.Except(sameData);

                                    //循环缺勤中该学校的学生数据（按理来说，childList的数据就是健康平台中在校的学生数据，sameData是双方在校数据相同的人 那QQChildrenList中跟sameData 不相等的人那就是需要删除掉的人，diffData就是需要加上去的人）
                                    foreach (MDataRow mDatas in QQChildrenList)
                                    {
                                        qqAction.ResetTable("report_student");
                                        qqAction.BeginTransation();
                                        //[studentid], [studentcode], [schoolcode], [studentname], [idcard], [studentnumber], [grade], [class], [gender], [age], [createdate],
                                        //[createusercode], [modifydate], [modifyusercode], [OtherUserId]
                                        //.FirstOrDefault(o => o.Get("ChildID", "") == mDatas.Get("OtherUserId", ""));
                                        MDataRow jkChildrenInfo = sameData.FirstOrDefault(o => o.Get("ChildID", "") == mDatas.Get("OtherUserId", "") && o.Get("SchoolID", "") == o.Get("SchoolID", ""));
                                        if (jkChildrenInfo == null)
                                        {
                                            LogHelper.WriteLog("OtherUserId:" + mDatas.Get("OtherUserId", "") + "|schoolcode:" + schoolInfo.Get("SchoolCode", "") + "|SchoolID:" + item["SchoolID"], logpath);
                                            continue;
                                        }
                                        qqAction.Set("studentName", jkChildrenInfo.Get("ChildName", ""));
                                        qqAction.Set("idcard", jkChildrenInfo.Get("IDCode", ""));
                                        //mDatas.Set("studentnumber", jkChildrenInfo.Get("ChildName",""));
                                        qqAction.Set("grade", jkChildrenInfo.Get("Grade", ""));
                                        qqAction.Set("class", jkChildrenInfo.Get("Class", ""));
                                        qqAction.Set("gender", jkChildrenInfo.Get("Gender", ""));
                                        qqAction.Set("createdate", jkChildrenInfo.Get("CreateTime", ""));

                                        string birthday = jkChildrenInfo.Get("Birthday", "");
                                        if (!string.IsNullOrEmpty(birthday))
                                        {
                                            var birthdayDate = Convert.ToDateTime(birthday);

                                            var ageYear = DateTime.Now.Year - birthdayDate.Year;

                                            var ageMonth = DateTime.Now.Month - birthdayDate.Month;
                                            if (ageMonth >= 0)
                                            {
                                                ageYear++;
                                            }
                                            qqAction.Set("age", ageYear);

                                        }

                                        qqAction.Set("modifydate", jkChildrenInfo.Get("UpdateTime", ""));
                                        qqAction.Data.LoadFrom(mDatas);
                                        if (!qqAction.Update(" studentcode='" + mDatas.Get("studentcode", "") + "' "))
                                        {
                                            LogHelper.WriteLog("更新学生基本数据:" + mDatas.Get("studentcode", "") + "失败!", logpath);
                                            qqAction.RollBack();
                                        }
                                        qqAction.EndTransation();
                                    }

                                    string schoolCode = schoolInfo.Get("schoolcode", "");
                                    foreach (MDataRow mDatas in diffData)
                                    {
                                        #region 插入新增的学生信息

                                        string studentCode = Guid.NewGuid().ToString();
                                        qqAction.ResetTable("report_student");
                                        qqAction.Set("studentcode", studentCode);
                                        qqAction.Set("schoolcode", schoolInfo.Get("schoolcode", ""));
                                        qqAction.Set("studentName", mDatas.Get("ChildName", ""));
                                        qqAction.Set("idcard", mDatas.Get("IDCode", ""));
                                        //mDatas.Set("studentnumber", jkChildrenInfo.Get("ChildName",""));
                                        qqAction.Set("grade", mDatas.Get("Grade", ""));
                                        qqAction.Set("class", mDatas.Get("Class", ""));
                                        qqAction.Set("gender", mDatas.Get("Gender", ""));
                                        qqAction.Set("createdate", mDatas.Get("CreateTime", ""));
                                        qqAction.Set("createusercode", "admin");
                                        qqAction.Set("modifydate", mDatas.Get("UpdateTime", ""));
                                        string birthday = mDatas.Get("Birthday", "");
                                        if (!string.IsNullOrEmpty(birthday))
                                        {
                                            var birthdayDate = Convert.ToDateTime(birthday);

                                            var ageYear = DateTime.Now.Year - birthdayDate.Year;

                                            var ageMonth = DateTime.Now.Month - birthdayDate.Month;
                                            if (ageMonth >= 0)
                                            {
                                                ageYear++;
                                            }
                                            qqAction.Set("age", ageYear);
                                        }

                                        string gender = mDatas.Get("Gender", "");

                                        string sql = "select count(*) as studentpeoples from report_student where schoolcode=" + DbService.SetQuotesValue(schoolCode);
                                        MDataRow schoolRow = new MDataRow(DBTool.GetColumns("report_school"));
                                        int studentpeoples = DbService.GetOne(sql, "").Get("studentpeoples", 0) + 1;
                                        schoolRow.Set("studentpeoples", studentpeoples);
                                        sql = "select count(*) as num from report_student where schoolcode=" + DbService.SetQuotesValue(schoolCode) + " and gender=" + DbService.SetQuotesValue(gender);

                                        int peopleNum = DbService.GetOne(sql, "").Get("num", 0) + 1;

                                        if (gender == "女")
                                        {
                                            schoolRow.Set("femalenumber", peopleNum);
                                        }
                                        if (gender == "男")
                                        {
                                            schoolRow.Set("malenumber", peopleNum);
                                        }

                                        int GradeNumber = DbService
                                            .GetTable(
                                                "select grade from report_student where schoolcode=" + DbService.SetQuotesValue(schoolCode) +
                                                " group by grade", 0).Rows.Count;
                                        schoolRow.Get("GradeNumber", GradeNumber);
                                        int ClassNumber = DbService
                                            .GetTable(
                                                "select class from report_student where schoolcode=" + DbService.SetQuotesValue(schoolCode) +
                                                " group by class", 0).Rows.Count;
                                        schoolRow.Get("ClassNumber", ClassNumber);

                                        try
                                        {
                                            qqAction.BeginTransation();
                                            if (qqAction.Insert(InsertOp.None))
                                            {

                                                //切换从表
                                                qqAction.ResetTable("report_school");
                                                string sWhere = "schoolcode=" + DbService.SetQuotesValue(schoolCode);
                                                qqAction.Data.LoadFrom(schoolRow);
                                                qqAction.Update(sWhere);

                                                #region 创建关联数据
                                                qqAction.ResetTable("report_yqzb_other");
                                                qqAction.Set("YQZBStudentCode", studentCode);
                                                qqAction.Set("OtherUserId", mDatas.Get("ChildID", ""));
                                                if (!qqAction.Insert())
                                                {
                                                    LogHelper.WriteLog("新增学生ID关联表错误:" + studentCode + "|" + mDatas.Get("ChildID", ""), logpath);
                                                    qqAction.RollBack();
                                                }
                                                qqAction.EndTransation();
                                                #endregion
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            qqAction.RollBack();
                                            LogHelper.WriteLog("定时更新基础数据->插入新增的学生信息:" + studentCode + "失败", logpath);
                                        }

                                        #endregion


                                    }

                                    //action.ResetTable(string.Format(BasicSqlForJKChildren, item["SchoolID"], 1));

                                    //List<MDataRow> delStudentInfo = (action.Select() ?? new MDataTable()).Rows.ToList<MDataRow>();

                                    //从缺勤学校所有学生数据中，找出来跟健康平台学校不匹配的所有学生数据，这些数据就是健康平台已经不在校的学生。
                                    List<MDataRow> sameQQStudentInfos = QQChildrenList.Where(o => childList.Any(g => g.Get("ChildID", "") == o.Get("OtherUserId", "") && g.Get("SchoolID", "") == o.Get("SchoolID", ""))).ToList();

                                    List<MDataRow> delQQStudentInfos = QQChildrenList.Except(sameQQStudentInfos).ToList();
                                    if (delQQStudentInfos != null && delQQStudentInfos.Count() > 0)
                                    {
                                        foreach (var mDatas in delQQStudentInfos)
                                        {
                                            try
                                            {
                                                string sWhere = " studentcode=" + DbService.SetQuotesValue(mDatas.Get("studentcode", ""));
                                                MDataRow studentRow = DbService.GetOne("report_student", sWhere);
                                                if (studentRow == null)
                                                {
                                                    LogHelper.WriteLog("更新基础信息失败:" + sWhere + " 不存在", logpath);
                                                    continue;
                                                }
                                                string schoolcode = studentRow.Get("schoolcode", "");

                                                string gender = studentRow.Get("gender", "");

                                                string sql = "select count(*) as studentpeoples from report_student where schoolcode=" + DbService.SetQuotesValue(schoolcode);
                                                //MDataRow schoolRow = new MDataRow(DBTool.GetColumns("report_school"));
                                                MDataRow schoolRow = DbService.GetOne("report_school", "");
                                                int studentpeoples = DbService.GetOne(sql, "").Get("studentpeoples", 0) - 1;
                                                if (studentpeoples < 0)
                                                {
                                                    LogHelper.WriteLog(schoolcode + "人员数量为空，无法继续删除", logpath);
                                                    continue;
                                                    //studentpeoples = 0;
                                                }
                                                schoolRow.Set("studentpeoples", studentpeoples);

                                                sql = "select count(*) as num from report_student where schoolcode=" + DbService.SetQuotesValue(schoolcode) + " and gender=" + DbService.SetQuotesValue(gender);
                                                int num = DbService.GetOne(sql, "").Get("num", 0) - 1;
                                                if (num < 0)
                                                {
                                                    LogHelper.WriteLog(schoolcode + " |" + gender + "人员数量为空，无法继续删除", logpath);
                                                    continue;
                                                }
                                                if (gender.Equals("女"))
                                                {
                                                    schoolRow.Set("femalenumber", num);
                                                }
                                                if (gender.Equals("男"))
                                                {
                                                    schoolRow.Set("malenumber", num);
                                                }

                                                qqAction.ResetTable("report_student");
                                                qqAction.BeginTransation();
                                                try
                                                {
                                                    //切换从表
                                                    qqAction.ResetTable("report_school");
                                                    sWhere = "schoolcode=" + DbService.SetQuotesValue(schoolcode);
                                                    qqAction.Data.LoadFrom(schoolRow);
                                                    qqAction.Update(sWhere);

                                                    qqAction.ResetTable("report_student");
                                                    //qqAction.Set("IsValid", 0);
                                                    //sWhere = " studentcode=" + DbService.SetQuotesValue(mDatas.Get("studentcode", ""));
                                                    //qqAction.Data.LoadFrom(schoolRow);
                                                    //qqAction.Update(sWhere);


                                                    //qqAction.Set("IsValid", 0);
                                                    sWhere = " studentcode=" + DbService.SetQuotesValue(mDatas.Get("studentcode", ""));
                                                    qqAction.Data.LoadFrom(schoolRow);
                                                    qqAction.Delete(sWhere);


                                                    qqAction.ResetTable("report_yqzb_other");

                                                    string report_yqzb_otherSQL = " YQZBStudentCode=" + DbService.SetQuotesValue(mDatas.Get("studentcode", ""));
                                                    qqAction.Delete(report_yqzb_otherSQL);
                                                    qqAction.EndTransation();
                                                }
                                                catch (Exception ex)
                                                {
                                                    LogHelper.WriteLog("更新基础信息失败+" + ex.Message, logpath);

                                                    qqAction.RollBack();
                                                }
                                            }
                                            catch (Exception ex)
                                            {
                                                LogHelper.WriteLog("更新基础信息失败+" + ex.Message, logpath);
                                            }
                                        }

                                    }

                                }

                                #endregion
                            }

                            jkServiceConfig.Set("GetDataStatus", 0);

                            checkAction.BeginTransation();
                            checkAction.Data.LoadFrom(jkServiceConfig);
                            if (!checkAction.Update(" Platform='JKPlatform' "))
                            {
                                LogHelper.WriteLog("更新同步配置jkServiceConfig:失败", logpath);
                                checkAction.RollBack();
                                checkAction.EndTransation();
                                return;
                            }
                            checkAction.EndTransation();
                        }
                    }
                }
            }
        }

        public CYQ.Data.Cache.CacheManage cache = CYQ.Data.Cache.CacheManage.Instance;
        /// <summary>
        /// 发送前一日健康缺勤数据
        /// </summary>
        /// <param name="yqzbConn"></param>
        public void SendJKBasicAbsenceData(string yqzbConn)
        {
            cache.Clear();//清除所有缓存项
            //早上七点回传昨天的缺勤数据
            MDataTable yqzbAbsenceDataTable = DbService.GetTable(string.Format(BasicSendJKAbsenceSql, DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")), bCache: false) ?? new MDataTable();//
            List<MDataRow> yqzbAbsenceList = yqzbAbsenceDataTable.Rows.ToList<MDataRow>();
            #region 新增数据
            if (yqzbAbsenceList != null && yqzbAbsenceList.Count > 0)
            {
                MDataRow jkServiceConfig = DbService.GetOne("report_JKService_Config", " Platform='JKPlatform' ") ?? new MDataRow();
                //健康平台服务同步配置表有数据，且GetJKBasicStatus 状态为未进行才可以同步
                if (jkServiceConfig != null && jkServiceConfig.Get("SendDataStatus", "0") == "0")
                {
                    using (MAction checkAction = new MAction("report_JKService_Config", yqzbConn))
                    {
                        jkServiceConfig.Set("SendDataStatus", 1);
                        checkAction.BeginTransation();
                        checkAction.Data.LoadFrom(jkServiceConfig);
                        if (!checkAction.Update(" Platform='JKPlatform' "))
                        {
                            LogHelper.WriteLog("更新同步配置jkServiceConfig:失败", logpath);
                            checkAction.RollBack();
                            checkAction.EndTransation();
                            return;
                        }
                        checkAction.EndTransation();
                        using (MAction yqzbAction = new MAction("TBQQ_ChildAbsence", jkSendConnection))
                        {
                            foreach (MDataRow item in yqzbAbsenceList)
                            {
                                string absenceCode = item.Get("AbsenceCode", "");

                                yqzbAction.ResetTable("TBQQ_ChildAbsence");
                                yqzbAction.BeginTransation();
                                yqzbAction.Set("ChildID", item.Get("OtherUserId", ""));
                                yqzbAction.Set("ChildName", item.Get("StudentName", ""));
                                yqzbAction.Set("IDCode", item.Get("IdCard", ""));
                                yqzbAction.Set("AbsenceNumber", item.Get("AbsenceNumber", ""));
                                yqzbAction.Set("AbsenceDate", item.Get("AbsenceDate", ""));
                                yqzbAction.Set("Casenature", item.Get("Casenature", ""));
                                yqzbAction.Set("DisinfectionDate", item.Get("DisinfectionDate", ""));
                                if (item.Get("isOlations", "") == "1")
                                {
                                    yqzbAction.Set("isOlations", "是");
                                }
                                yqzbAction.Set("isOlations", (item.Get("isOlations", "") == "1" ? "是" : ""));
                                yqzbAction.Set("Visitinghospital", item.Get("Visitinghospital", ""));
                                yqzbAction.Set("VisitingDate", item.Get("VisitingDate", ""));
                                yqzbAction.Set("Injuredlocation", item.Get("Injuredlocation", ""));
                                yqzbAction.Set("Remark", item.Get("Remark", ""));
                                yqzbAction.Set("DisposalContent", item.Get("DisposalContent", ""));

                                if (!string.IsNullOrEmpty(absenceCode))
                                {
                                    Dictionary<string, object> diseaseDict = GetSchoolDiseaseInfos(absenceCode);
                                    if (diseaseDict.ContainsKey("jbxx"))
                                    {
                                        yqzbAction.Set("Disease", JsonConvert.SerializeObject(diseaseDict["jbxx"]));
                                    }
                                    if (diseaseDict.ContainsKey("zzxx"))
                                    {
                                        yqzbAction.Set("Symptom", JsonConvert.SerializeObject(diseaseDict["zzxx"]));
                                    }
                                    if (diseaseDict.ContainsKey("shxx"))
                                    {
                                        yqzbAction.Set("Hurt", JsonConvert.SerializeObject(diseaseDict["shxx"]));
                                    }
                                    if (diseaseDict.ContainsKey("shyy"))
                                    {
                                        yqzbAction.Set("Reason", JsonConvert.SerializeObject(diseaseDict["shyy"]));
                                    }
                                }

                                yqzbAction.Set("ParentName", item.Get("ParentName", ""));
                                yqzbAction.Set("ParentPhoneNumber", item.Get("ParentPhoneNumber", ""));

                                yqzbAction.Set("CreateTime", item.Get("CreateDate", ""));
                                yqzbAction.Set("UpdateTime", item.Get("ModifyDate", ""));
                                yqzbAction.Set("IsDeleted", 0);

                                if (!yqzbAction.Insert())
                                {
                                    LogHelper.WriteLog("回传缺勤数据插入失败:" + item.Get("AbsenceCode", ""), logpath);
                                    yqzbAction.RollBack();
                                }
                                yqzbAction.EndTransation();
                            }
                        }
                        jkServiceConfig.Set("SendDataStatus", 0);

                        checkAction.BeginTransation();
                        checkAction.Data.LoadFrom(jkServiceConfig);
                        if (!checkAction.Update(" Platform='JKPlatform' "))
                        {
                            LogHelper.WriteLog("更新同步配置jkServiceConfig:失败", logpath);
                            checkAction.RollBack();
                            checkAction.EndTransation();
                            return;
                        }
                        checkAction.EndTransation();

                    }
                }
            }
            #endregion

            //更新前两天的被修改的数据到中间库
            MDataTable twoDayAgoYQZBData = DbService.GetTable(string.Format(BasicSendJKAbsenceSqlTwoDayAgo, DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd"), DateTime.Now.AddDays(-2).ToString("yyyy-MM-dd"), DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd")), bCache: false) ?? new MDataTable();
            if (twoDayAgoYQZBData != null && twoDayAgoYQZBData.Rows.Count > 0)
            {
                MDataRow jkServiceConfig = DbService.GetOne("report_JKService_Config", " Platform='JKPlatform' ") ?? new MDataRow();
                //健康平台服务同步配置表有数据，且GetJKBasicStatus 状态为未进行才可以同步
                if (jkServiceConfig != null && jkServiceConfig.Get("SendDataStatus", "0") == "0")
                {
                    using (MAction checkAction = new MAction("report_JKService_Config", yqzbConn))
                    {
                        jkServiceConfig.Set("SendDataStatus", 1);
                        checkAction.BeginTransation();
                        checkAction.Data.LoadFrom(jkServiceConfig);
                        if (!checkAction.Update(" Platform='JKPlatform' "))
                        {
                            LogHelper.WriteLog("更新同步配置jkServiceConfig:失败", logpath);
                            checkAction.RollBack();
                            checkAction.EndTransation();
                            return;
                        }
                        checkAction.EndTransation();
                        using (MAction yqzbAction = new MAction("TBQQ_ChildAbsence", jkSendConnection))
                        {

                            foreach (MDataRow item in twoDayAgoYQZBData.Rows)
                            {
                                string absenceCode = item.Get("AbsenceCode", "");

                                yqzbAction.ResetTable("TBQQ_ChildAbsence");
                                yqzbAction.BeginTransation();
                                yqzbAction.Set("ChildID", item.Get("OtherUserId", ""));
                                yqzbAction.Set("ChildName", item.Get("StudentName", ""));
                                yqzbAction.Set("IDCode", item.Get("IdCard", ""));
                                yqzbAction.Set("AbsenceNumber", item.Get("AbsenceNumber", ""));
                                yqzbAction.Set("AbsenceDate", item.Get("AbsenceDate", ""));
                                yqzbAction.Set("Casenature", item.Get("Casenature", ""));
                                yqzbAction.Set("DisinfectionDate", item.Get("DisinfectionDate", ""));
                                //yqzbAction.Set("isOlations", (item.Get("isOlations", "") == "1" ? "是" : ""));

                                yqzbAction.Set("isOlations", "");
                                if (item.Get("isOlations", "") == "1")
                                {
                                    yqzbAction.Set("isOlations", "是");
                                }
                                yqzbAction.Set("Visitinghospital", item.Get("Visitinghospital", ""));
                                yqzbAction.Set("VisitingDate", item.Get("VisitingDate", ""));
                                yqzbAction.Set("Injuredlocation", item.Get("Injuredlocation", ""));
                                yqzbAction.Set("Remark", item.Get("Remark", ""));
                                yqzbAction.Set("DisposalContent", item.Get("DisposalContent", ""));

                                if (!string.IsNullOrEmpty(absenceCode))
                                {
                                    Dictionary<string, object> diseaseDict = GetSchoolDiseaseInfos(absenceCode);
                                    if (diseaseDict.ContainsKey("jbxx"))
                                    {
                                        yqzbAction.Set("Disease", JsonConvert.SerializeObject(diseaseDict["jbxx"]));
                                    }
                                    if (diseaseDict.ContainsKey("zzxx"))
                                    {
                                        yqzbAction.Set("Symptom", JsonConvert.SerializeObject(diseaseDict["zzxx"]));
                                    }
                                    if (diseaseDict.ContainsKey("shxx"))
                                    {
                                        yqzbAction.Set("Hurt", JsonConvert.SerializeObject(diseaseDict["shxx"]));
                                    }
                                    if (diseaseDict.ContainsKey("shyy"))
                                    {
                                        yqzbAction.Set("Reason", JsonConvert.SerializeObject(diseaseDict["shyy"]));
                                    }
                                }

                                yqzbAction.Set("ParentName", item.Get("ParentName", ""));
                                yqzbAction.Set("ParentPhoneNumber", item.Get("ParentPhoneNumber", ""));

                                yqzbAction.Set("CreateTime", item.Get("CreateDate", ""));
                                yqzbAction.Set("UpdateTime", item.Get("ModifyDate", ""));
                                yqzbAction.Set("IsDeleted", 0);

                                string aaa = " ChildID=" + item.Get("OtherUserId", "") + " and   CONVERT(VARCHAR(32),AbsenceDate,23)='" + Convert.ToDateTime(item.Get("AbsenceDate", DateTime.Now)).ToString("yyyy-MM-dd") + "'";


                                if (!yqzbAction.Update(" ChildID=" + item.Get("OtherUserId", "") + " and   CONVERT(VARCHAR(32),AbsenceDate,23)='" + Convert.ToDateTime(item.Get("AbsenceDate", DateTime.Now)).ToString("yyyy-MM-dd") + "' "))
                                {
                                    LogHelper.WriteLog("回传缺勤数据更新失败:" + item.Get("AbsenceCode", ""), logpath);
                                    yqzbAction.RollBack();
                                }
                                yqzbAction.EndTransation();
                            }
                        }
                        jkServiceConfig.Set("SendDataStatus", 0);

                        checkAction.BeginTransation();
                        checkAction.Data.LoadFrom(jkServiceConfig);
                        if (!checkAction.Update(" Platform='JKPlatform' "))
                        {
                            LogHelper.WriteLog("更新同步配置jkServiceConfig:失败", logpath);
                            checkAction.RollBack();
                            checkAction.EndTransation();
                            return;
                        }
                        checkAction.EndTransation();

                    }
                }

            }


        }

        /// <summary>
        /// 根据疾病编号查询疾病详细信息
        /// </summary>
        /// <param name="absenceCode"></param>
        /// <returns></returns>
        public Dictionary<string, object> GetSchoolDiseaseInfos(string absenceCode)
        {
            LogHelper.WriteLog(jkGetConnection, logpath);
            LogHelper.WriteLog(jkSendConnection, logpath);

            Dictionary<string, object> keyValues = new Dictionary<string, object>();

            if (!string.IsNullOrEmpty(absenceCode))
            {
                MDataTable mDataTable = DbService.GetTable(string.Format(DiseaseInfosForYQZBSql, absenceCode), bCache: false) ?? new MDataTable();
                List<AllDiseaseModel> allDiseaseModels = mDataTable.ToList<AllDiseaseModel>();
                var primaryDiseaseInfos = allDiseaseModels.GroupBy(o => new { o.PrimaryDiseaseCode, o.PrimaryDiseaseName }).Select(o => new
                {
                    PrimaryDiseaseCode = o.Key.PrimaryDiseaseCode,
                    PrimaryDiseaseName = o.Key.PrimaryDiseaseName
                });
                foreach (var item in primaryDiseaseInfos)
                {
                    PrimaryDiseaseModel primaryDiseaseModel = new PrimaryDiseaseModel()
                    {
                        PrimaryDiseaseCode = item.PrimaryDiseaseCode,
                        PrimaryDiseaseName = item.PrimaryDiseaseName,
                        DiseaseData = new List<SecondaryDiseaseModel>()
                    };

                    //根据一级疾病code 和名称查询出来二级疾病的数据
                    var secondaryDiseaseInfos = allDiseaseModels.Where(o => o.PrimaryDiseaseCode == item.PrimaryDiseaseCode && o.PrimaryDiseaseName == item.PrimaryDiseaseName);
                    //把二级疾病数据根据code和名称分组，因为会存在同一人在二级疾病信息中同时出现两个病症
                    var secondaryDisease = secondaryDiseaseInfos.GroupBy(o => new { o.SecondaryDiseaseCode, o.SecondaryDiseaseName }).Select(o => new { SecondaryDiseaseCode = o.Key.SecondaryDiseaseCode, SecondaryDiseaseName = o.Key.SecondaryDiseaseName }).ToList();
                    //根据二级疾病分组后的数据去查询三级数据
                    foreach (var secondItem in secondaryDisease)
                    {
                        SecondaryDiseaseModel secondaryDiseaseModel = new SecondaryDiseaseModel()
                        {
                            SecondaryDiseaseCode = secondItem.SecondaryDiseaseCode,
                            SecondaryDiseaseName = secondItem.SecondaryDiseaseName,
                            SecondaryDiseaseData = new List<DiseaseModel>()
                        };
                        //根据二级数据查出三级数据并转换成三级的实体
                        List<DiseaseModel> diseaseInfos = allDiseaseModels.Where(o => o.SecondaryDiseaseCode == secondItem.SecondaryDiseaseCode && o.SecondaryDiseaseName == secondItem.SecondaryDiseaseName).Select(o => new DiseaseModel { OtherDiseaseName = o.OtherDiseaseName, DiseaseName = o.DiseaseName }).ToList();
                        //将三级数据放入二级实体字段
                        secondaryDiseaseModel.SecondaryDiseaseData = diseaseInfos;
                        //将二级数据加入一级实体字段
                        primaryDiseaseModel.DiseaseData.Add(secondaryDiseaseModel);
                    }

                    //将一级数据加入字典
                    keyValues.Add(primaryDiseaseModel.PrimaryDiseaseCode, primaryDiseaseModel);
                }
                string aaa = JsonConvert.SerializeObject(keyValues);
            }
            return keyValues;
        }

    }
}
