using PoJun.Dapper;
using ASHermed.SinoInterface.Api.IRepository.Test;
using ASHermed.SinoInterface.Entity.Test;
using ASHermed.SinoInterface.Repository.MySql;
using ASHermed.SinoInterface.Tools;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ASHermed.SinoInterface.Api.ContractModel.App;
using ASHermed.SinoInterface.Api.IRepository;
using ASHermed.SinoInterface.Api.ContractModel;
using ASHermed.SinoInterface.Api.ContractModel.Quest;
using ASHermed.SinoInterface.Api.ContractModel.App.Model;

namespace ASHermed.SinoInterface.Api.MySqlRepository
{

    public class OutAptInfoRepository : BaseRepositoryToASHermed_SinoInterface<Out_Apt_Info>, IOutAptInfoRepository
    {
        public int InsertOutAptInfo(Out_Apt_Info outAptInfo)
        {
            //if (outAptInfo.Apt_Time != null && outAptInfo.Apt_Time.Value.Date == DateTime.Now.Date)
            //{
            var quest = "select * from pub_quest_data Where InfoId = 3 And PatId = @PatId Order By CreateTime Desc Limit 1 ";
            var questDt = Query<Pub_Quest_Data>(quest, new { PatId = outAptInfo.Pat_Id }).FirstOrDefault();
            if (questDt != null)
            {
                outAptInfo.QuestId = questDt.Id;
            }
            //}

            StringBuilder str = new StringBuilder();
            str.Append("insert into Out_Apt_Info (EPISODE_ID,PAT_ID,HOS_ID,DEPT_ID,DOC_ID,APT_TIME,APT_PROJ,STATUS,INNER_STATUS,DISPOSE_TIME,APT_USRN,REMARK,APPLY_USER_ID,CREATE_TIME,UPDATE_TIME,SOURCE_TYPE,MATCH_CODE,Apt_Id,CHANNEL,QuestId) values (  @EPISODE_ID,@PAT_ID,@HOS_ID,@DEPT_ID,@DOC_ID,@APT_TIME,@APT_PROJ,@STATUS,@INNER_STATUS,@DISPOSE_TIME,@APT_USRN,@REMARK,@APPLY_USER_ID,@CREATE_TIME,@UPDATE_TIME,@SOURCE_TYPE, @MATCH_CODE,@Apt_Id,@CHANNEL,@QuestId)");
            var parms = new Dictionary<string, object>();
            parms.Add("@EPISODE_ID", outAptInfo.Episode_Id);
            parms.Add("@PAT_ID", outAptInfo.Pat_Id);
            parms.Add("@HOS_ID", outAptInfo.Hos_Id);
            parms.Add("@DEPT_ID", outAptInfo.Dept_ID);
            parms.Add("@DOC_ID", outAptInfo.Doc_Id);
            parms.Add("@APT_TIME", outAptInfo.Apt_Time);
            parms.Add("@APT_PROJ", outAptInfo.Apt_Proj);
            parms.Add("@STATUS", outAptInfo.Status);
            parms.Add("@INNER_STATUS", outAptInfo.Inner_Status);
            parms.Add("@DISPOSE_TIME", outAptInfo.Dispose_Time);
            parms.Add("@APT_USRN", outAptInfo.Apt_Usrn);
            parms.Add("@REMARK", outAptInfo.Remark);
            parms.Add("@APPLY_USER_ID", outAptInfo.Apply_User_Id);
            parms.Add("@CREATE_TIME", outAptInfo.Create_Time);
            parms.Add("@UPDATE_TIME", outAptInfo.Update_Time);
            parms.Add("@SOURCE_TYPE", outAptInfo.Source_Type);
            parms.Add("@MATCH_CODE", outAptInfo.Match_Code);
            parms.Add("@Apt_Id", outAptInfo.Apt_Id);
            parms.Add("@CHANNEL", outAptInfo.Channel);
            parms.Add("@QuestId", outAptInfo.QuestId);
            var result = Insert(str.ToString(), parms);
            return result;
        }

        public int UpdateOutAptInfo(Out_Apt_Info outAptInfo)
        {
            StringBuilder str = new StringBuilder();
            str.Append("update Out_Apt_Info set ");
            str.Append(@"EPISODE_ID=@EPISODE_ID,");
            str.Append(@"PAT_ID=@PAT_ID,");
            str.Append(@"HOS_ID=@HOS_ID,");
            str.Append(@"DEPT_ID=@DEPT_ID,");
            str.Append(@"DOC_ID=@DOC_ID,");
            str.Append(@"APT_TIME=@APT_TIME,");
            str.Append(@"APT_PROJ=@APT_PROJ,");
            str.Append(@"STATUS=@STATUS,");
            str.Append(@"INNER_STATUS=@INNER_STATUS,");
            str.Append(@"DISPOSE_TIME=@DISPOSE_TIME,");
            str.Append(@"APT_USRN=@APT_USRN,");
            str.Append(@"REMARK=@REMARK,");
            str.Append(@"APPLY_USER_ID=@APPLY_USER_ID,");
            //str.Append(@"CREATE_TIME=@CREATE_TIME,");
            str.Append(@"UPDATE_TIME=@UPDATE_TIME,");
            str.Append(@"SOURCE_TYPE=@SOURCE_TYPE,");
            str.Append(@"MATCH_CODE=@MATCH_CODE");
            str.Append(@" where ID=@ID");
            var parms = new Dictionary<string, object>();
            parms.Add("@EPISODE_ID", outAptInfo.Episode_Id);
            parms.Add("@PAT_ID", outAptInfo.Pat_Id);
            parms.Add("@HOS_ID", outAptInfo.Hos_Id);
            parms.Add("@DEPT_ID", outAptInfo.Dept_ID);
            parms.Add("@DOC_ID", outAptInfo.Doc_Id);
            parms.Add("@APT_TIME", outAptInfo.Apt_Time);
            parms.Add("@APT_PROJ", outAptInfo.Apt_Proj);
            parms.Add("@STATUS", outAptInfo.Status);
            parms.Add("@INNER_STATUS", outAptInfo.Inner_Status);
            parms.Add("@DISPOSE_TIME", outAptInfo.Dispose_Time);
            parms.Add("@APT_USRN", outAptInfo.Apt_Usrn);
            parms.Add("@REMARK", outAptInfo.Remark);
            parms.Add("@APPLY_USER_ID", outAptInfo.Apply_User_Id);
            // parms.Add("@CREATE_TIME", outAptInfo.Create_Time);
            parms.Add("@UPDATE_TIME", outAptInfo.Update_Time);
            parms.Add("@SOURCE_TYPE", outAptInfo.Source_Type);
            parms.Add("@MATCH_CODE", outAptInfo.Match_Code);
            parms.Add("@ID", outAptInfo.Id);
            var result = Execute(str.ToString(), parms);
            return result;
        }

        public List<SchedulingParam> SelectDeptServDoctor(int hosId, int deptId, int doctorId, int servId, int language, int useOccasion)
        {
            StringBuilder strBuilder = new StringBuilder();
            strBuilder.Append(@" select DISTINCT f.HIS_DEPT_ID, g.HIS_SERV_ID, g.CN_NAME AS CnServName, g.EN_NAME AS EnServName, g.SORT,g.ID as SERV_ID,f.APT_DEPT_ID ");
            if (doctorId != 0)
            {
                strBuilder.Append(@",d.CARE_PROV_ID ");
            }
            strBuilder.Append(@" from dic_map_dept_doctor a,dic_map_hos_dept b
                                        , dic_doctor c, dic_map_his_doctor d
                                         , dic_dept e, dic_map_his_dept f
                                          , dic_map_dept_serv g
                                        ,dic_hos h
                                 where a.IS_DELETED = false
                                        -- 过滤科室未进入门诊
                                        and b.APT_HOS_ID = a.APT_HOS_ID and b.APT_DEPT_ID = a.APT_DEPT_ID and b.IS_DELETED = false
                                        -- 过滤已删除医生
                                        and c.ID = a.APT_DOCTOR_ID and c.IS_DELETED = false
                                        -- 过滤未绑定医生
                                        and d.APT_DOCTOR_ID = c.ID and d.IS_DELETED = false
                                        -- 过滤已删除门诊
                                        and h.ID = a.APT_HOS_ID and h.IS_DELETED = false
                                        -- 过滤已删除科室
                                        and e.ID = a.APT_DEPT_ID and e.IS_DELETED = false AND e.DEPT_TYPE = 0
                                        -- 过滤未绑定科室
                                        and f.APT_DEPT_ID = e.ID and f.HIS_HOS_ID = h.HIS_HOS_ID and f.IS_DELETED = false
                                        -- 科室已绑定的服务
                                        and g.DEPT_MAP_ID = f.ID and g.IS_DELETED = false
                                        and a.USE_OCCASION =@useOccasion and b.USE_OCCASION=@useOccasion");
            if (hosId != 0)
            {
                strBuilder.Append(@" and h.ID = @hosId ");
            }
            if (deptId != 0)
            {
                strBuilder.Append(@" and e.ID = @deptId ");
            }
            if (doctorId != 0)
            {
                strBuilder.Append(@" and c.ID = @doctorId ");
            }
            if (servId != 0)
            {
                strBuilder.Append(@" and g.ID = @servId ");
            }
            var parms = new Dictionary<string, object>();
            parms.Add("@useOccasion", useOccasion);
            parms.Add("@hosId", hosId);
            parms.Add("@deptId", deptId);
            parms.Add("@doctorId", doctorId);
            parms.Add("@servId", servId);
            var result = Query<SchedulingParam>(strBuilder.ToString(), parms);
            return result?.ToList(); ;
        }

        public List<SchedulingParam> SelectDeptServDoctor(int hosId, int deptId, int language)
        {
            var sql = @"    SELECT DISTINCT
	                            f.HIS_DEPT_ID,
	                            g.HIS_SERV_ID,
                                g.ID AS ServiceId,";
            if (language == 1)
            {
                sql += @" g.CN_NAME AS CnServName, ";
            }
            else
            {
                sql += @" g.EN_NAME AS CnServName,";
            }
            sql += @"           g.SORT,
	                            g.ID AS SERV_ID,
	                            f.APT_DEPT_ID
                               -- c.id AS DocId,
                                -- c.CN_NAME AS DocName,
                                -- e.id AS DeptId,
                                -- e.CN_NAME AS DeptName,
	                            -- h.id AS HosId,
	                            -- h.CN_NAME AS HosName,
                                -- d.APT_TYPE AS AptType  
                            FROM
	                            dic_map_dept_doctor a,
	                            dic_map_hos_dept b,
	                            dic_doctor c,
	                            dic_map_his_doctor d,
	                            dic_dept e,
	                            dic_map_his_dept f,
	                            dic_map_dept_serv g,
	                            dic_hos h 
                            WHERE
	                            a.IS_DELETED = FALSE -- 过滤科室未进入门诊
	                            AND b.APT_HOS_ID = a.APT_HOS_ID 
	                            AND b.APT_DEPT_ID = a.APT_DEPT_ID 
	                            AND b.IS_DELETED = FALSE -- 过滤已删除医生
	                            AND c.ID = a.APT_DOCTOR_ID 
	                            AND c.IS_DELETED = FALSE -- 过滤未绑定医生
	                            AND d.APT_DOCTOR_ID = c.ID 
	                            AND d.IS_DELETED = FALSE -- 过滤已删除门诊
	                            AND h.ID = a.APT_HOS_ID 
	                            AND h.IS_DELETED = FALSE -- 过滤已删除科室
	                            AND e.ID = a.APT_DEPT_ID 
	                            AND e.IS_DELETED = FALSE 
	                            AND e.DEPT_TYPE = 0 -- 过滤未绑定科室
	                            AND f.APT_DEPT_ID = e.ID 
	                            AND f.HIS_HOS_ID = h.HIS_HOS_ID 
	                            AND f.IS_DELETED = FALSE -- 科室已绑定的服务
	                            AND g.DEPT_MAP_ID = f.ID 
	                            AND g.IS_DELETED = FALSE 
	                            AND a.USE_OCCASION = 0 
	                            AND b.USE_OCCASION = 0 
	                            AND (d.APT_TYPE = 2 OR d.APT_TYPE = 4) 
	                            AND h.ID = @HosId -- 诊所ID
	                            AND e.ID = @DeptId  -- 科室ID";
            return Query<SchedulingParam>(sql, new { HosId = hosId, DeptId = deptId }).ToList();
        }

        public List<SchedulingParamByLanguage> SelectDeptServDoctorLanguage(int hosId, int deptId, int doctorId, int servId, int language, int useOccasion)
        {
            StringBuilder strBuilder = new StringBuilder();
            if (language == 1)
            {
                strBuilder.Append(@"select DISTINCT f.HIS_DEPT_ID as HisDeptId, g.HIS_SERV_ID as HisServId, g.CN_NAME AS ServName, g.SORT,g.ID as ServId,f.APT_DEPT_ID as AptDeptId ");
            }
            else
            {
                strBuilder.Append(@"select DISTINCT f.HIS_DEPT_ID as HisDeptId, g.HIS_SERV_ID as HisServId, g.EN_NAME AS ServName, g.SORT,g.ID as ServId,f.APT_DEPT_ID as AptDeptId ");
            }
            if (doctorId != 0)
            {
                strBuilder.Append(@", d.CARE_PROV_ID as CareProvId");
            }
            strBuilder.Append(@" from
                                dic_map_dept_doctor a,dic_map_hos_dept b
                                ,dic_doctor c, dic_map_his_doctor d
                                ,dic_dept e, dic_map_his_dept f
                                ,dic_map_dept_serv g
                                ,dic_hos h
                                where a.IS_DELETED = false
                                and b.APT_HOS_ID = a.APT_HOS_ID and b.APT_DEPT_ID = a.APT_DEPT_ID and b.IS_DELETED = false
                                and c.ID = a.APT_DOCTOR_ID and c.IS_DELETED = false
                                and d.APT_DOCTOR_ID = c.ID and d.IS_DELETED = false
                                and h.ID = a.APT_HOS_ID and h.IS_DELETED = false
                                and e.ID = a.APT_DEPT_ID and e.IS_DELETED = false AND e.DEPT_TYPE=0
                                and f.APT_DEPT_ID = e.ID and f.HIS_HOS_ID = h.HIS_HOS_ID and f.IS_DELETED = false
                                and g.DEPT_MAP_ID = f.ID and g.IS_DELETED = false
                                and a.USE_OCCASION=@useOccasion and b.USE_OCCASION=@useOccasion ");
            if (hosId != 0)
            {
                strBuilder.Append(@" and h.ID = @hosId ");
            }
            if (deptId != 0)
            {
                strBuilder.Append(@" and e.ID = @deptId ");
            }
            if (doctorId != 0)
            {
                strBuilder.Append(@" and c.ID = @doctorId ");
            }
            if (servId != 0)
            {
                strBuilder.Append(@" and g.ID = @servId ");
            }
            var parms = new Dictionary<string, object>();
            parms.Add("@useOccasion", useOccasion);
            parms.Add("@hosId", hosId);
            parms.Add("@deptId", deptId);
            parms.Add("@doctorId", doctorId);
            parms.Add("@servId", servId);
            var result = Query<SchedulingParamByLanguage>(strBuilder.ToString(), parms);
            return result?.ToList(); ;

        }
        public List<SchedulingParamByLanguage> SelectOnlineDeptServDoctorLanguage(int hosId, int deptId, int doctorId, int servId, int language, int useOccasion)
        {
            StringBuilder strBuilder = new StringBuilder();
            if (language == 1)
            {
                strBuilder.Append(@"select DISTINCT f.HIS_DEPT_ID as HisDeptId, g.HIS_SERV_ID as HisServId, g.CN_NAME AS ServName, g.SORT,g.ID as ServId,f.APT_DEPT_ID as AptDeptId ");
            }
            else
            {
                strBuilder.Append(@"select DISTINCT f.HIS_DEPT_ID as HisDeptId, g.HIS_SERV_ID as HisServId, g.EN_NAME AS ServName, g.SORT,g.ID as ServId,f.APT_DEPT_ID as AptDeptId ");
            }
            strBuilder.Append(@", d.CARE_PROV_ID as CareProvId");
            strBuilder.Append(@" from
                                dic_map_dept_doctor a,dic_map_hos_dept b
                                ,dic_doctor c, dic_map_his_doctor d
                                ,dic_dept e, dic_map_his_dept f
                                ,dic_map_dept_serv g
                                ,dic_hos h
                                where a.IS_DELETED = false
                                and b.APT_HOS_ID = a.APT_HOS_ID and b.APT_DEPT_ID = a.APT_DEPT_ID and b.IS_DELETED = false
                                and c.ID = a.APT_DOCTOR_ID and c.IS_DELETED = false
                                and d.APT_DOCTOR_ID = c.ID and d.IS_DELETED = false
                                and h.ID = a.APT_HOS_ID and h.IS_DELETED = false
                                and e.ID = a.APT_DEPT_ID and e.IS_DELETED = false AND e.DEPT_TYPE=0
                                and f.APT_DEPT_ID = e.ID and f.HIS_HOS_ID = h.HIS_HOS_ID and f.IS_DELETED = false
                                and g.DEPT_MAP_ID = f.ID and g.IS_DELETED = false
                                and a.USE_OCCASION=@useOccasion and b.USE_OCCASION=@useOccasion ");
            if (hosId != 0)
            {
                strBuilder.Append(@" and h.ID = @hosId ");
            }
            if (deptId != 0)
            {
                strBuilder.Append(@" and e.ID = @deptId ");
            }
            if (doctorId != 0)
            {
                strBuilder.Append(@" and c.ID = @doctorId ");
            }
            if (servId != 0)
            {
                strBuilder.Append(@" and g.ID = @servId ");
            }
            var parms = new Dictionary<string, object>();
            parms.Add("@useOccasion", useOccasion);
            parms.Add("@hosId", hosId);
            parms.Add("@deptId", deptId);
            parms.Add("@doctorId", doctorId);
            parms.Add("@servId", servId);
            var result = Query<SchedulingParamByLanguage>(strBuilder.ToString(), parms);
            return result?.ToList(); ;

        }
        public List<AptData> SelectAptWithVaccineApt(int language, int userId, int aptStatus, int year, int aptType)
        {

            StringBuilder strBuilder = new StringBuilder();
            strBuilder.Append(@"SELECT
                                a.ID,
                                a.APT_TIME,
                                a.STATUS,
                                a.APT_USRN,
                                a.REMARK,
                                a.APT_TYPE,
                                a.HOS_ID,
                                b.PAT_NAME,
                                b.PHONE_NO,
                                b.BIRTHDAY,
                                b.EMAIL,
                                b.GENDER AS PAT_GENDER,
                                e.LIST_IMAGE,
                                e.DETAIL_IMAGE,
                                f.NICK_NAME AS APPLY_USER,
                                f.PHONE AS APPLY_PHONE, ");
            if (language == 1)
            {
                strBuilder.Append(@" c.CN_NAME AS HOS_NAME,
                                    d.CN_NAME AS DEPT_NAME,
                                    e.CN_NAME AS DOCTOR_NAME,
                                    g.CN_NAME AS SERV ");
            }
            else
            {
                strBuilder.Append(@" c.EN_NAME AS HOS_NAME,
                                    d.EN_NAME AS DEPT_NAME,
                                    e.EN_NAME AS DOCTOR_NAME,
                                    g.EN_NAME AS SERV ");
            }

            strBuilder.Append(@" FROM
                                (
                                select
                                a.ID, a.APT_TIME, a.`STATUS`, a.REMARK, a.MATCH_CODE, a.APT_USRN, a.PAT_ID, a.HOS_ID, a.DEPT_ID, a.DOC_ID, a.APPLY_USER_ID
                                as USER_ID, a.APT_PROJ, 0 as APT_TYPE
                                from out_apt_info a,
                                (
                                select max(ID) id from(
                                --申请部分
                                select ID, APT_TIME,`STATUS`, REMARK, MATCH_CODE, APT_USRN, PAT_ID, HOS_ID, DEPT_ID, DOC_ID, APPLY_USER_ID as
                                USER_ID, APT_PROJ, 0 as APT_TYPE
                                from out_apt_info where `STATUS` != 8 and APPLY_USER_ID = @userId

                                union

                                -- 绑定就诊人部分
                                select
                                a.ID, a.APT_TIME, a.`STATUS`, a.REMARK, a.MATCH_CODE, a.APT_USRN, a.PAT_ID, a.HOS_ID, a.DEPT_ID, a.DOC_ID, a.APPLY_USER_ID
                                as USER_ID, a.APT_PROJ, 0 as APT_TYPE
                                from out_apt_info a, pub_map_user_pat b
                                where a.`STATUS` != 8 and b.IS_DELETED = false and a.PAT_ID = b.PAT_ID and b.USER_ID
                                =@userId) u
                                GROUP BY MATCH_CODE) b
                                where a.ID = b.ID

                                union

                                -- 疫苗预约部分
                                select a.ID, a.CREATE_TIME as APT_TIME, a.`STATUS`, a.REMAKE, a.MATCH_CODE, ");
            if (language == 1)
            {
                strBuilder.Append(@" v.VACCINE_CN_NAME as APT_USRN, ");
            }
            else
            {
                strBuilder.Append(@" v.VACCINE_EN_NAME as APT_USRN, ");
            }
            strBuilder.Append(@" a.PAT_ID,a.HOS_ID,a.DEPT_ID,a.DOC_ID,a.USER_ID,'' as APT_PROJ,1 as APT_TYPE
                                from out_vaccine_appt a
                                join dic_vaccine v on a.VACCINE_ID = v.ID and v.`STATUS`= 1
                                where a.`STATUS` != 8 and a.USER_ID =@userId
                                and not EXISTS(
                                select vapt.ID
                                from out_vaccine_appt vapt
                                join out_apt_info apt on apt.MATCH_CODE= vapt.MATCH_CODE
                                where vapt.ID= a.ID
                                )
                                ) a

                                LEFT JOIN pub_pat_info b ON a.PAT_ID = b.ID
                                AND b.IS_DELETED = FALSE
                                LEFT JOIN dic_hos c ON a.HOS_ID = c.ID
                                AND c.IS_DELETED = FALSE
                                LEFT JOIN dic_dept d ON a.DEPT_ID = d.ID
                                AND d.IS_DELETED = FALSE AND d.DEPT_TYPE = 0
                                LEFT JOIN dic_doctor e ON a.DOC_ID = e.ID
                                AND e.IS_DELETED = FALSE
                                LEFT JOIN pub_user_info f ON a.USER_ID = f.ID
                                AND f.IS_DELETED = FALSE
                                LEFT JOIN dic_map_dept_serv g ON a.APT_PROJ = g.ID
                                AND G.IS_DELETED = FALSE
                                WHERE 1 = 1 ");
            if (aptStatus != 0)
            {
                if (aptStatus == -1)
                {
                    strBuilder.Append(@" and a.`STATUS` != 0 and a.APT_TIME & lt; now() ");
                }
                if (aptStatus == 0)
                {
                    strBuilder.Append(@" and(a.`STATUS`= 0 or a.`STATUS`= 1) ");
                }
                if (aptStatus == 1)
                {
                    strBuilder.Append(@" and a.`STATUS`= 4 ");
                }
            }
            if (aptType != 0)
            {
                strBuilder.Append(@" and a.APT_TYPE = @aptType ");
            }
            if (year != 0)
            {
                strBuilder.Append(@" and year(a.APT_TIME) = @year ");
            }
            strBuilder.Append(@" ORDER BY a.APT_TIME DESC ");
            var parms = new Dictionary<string, object>();
            parms.Add("@userId", userId);
            parms.Add("@aptType", aptType);
            parms.Add("@year", year);
            var result = Query<AptData>(strBuilder.ToString(), parms);
            return result?.ToList().DeEncrypt();

        }

        public List<DeptServModel> GetHisDeptServ(List<int> deptIds)
        {
            var parms = new Dictionary<string, object>();
            parms.Add("@deptIds", deptIds);
            var selectSql = @" SELECT hd.HIS_HOS_ID as HisHosId,hd.HIS_DEPT_ID as HisDeptId,ds.HIS_SERV_ID as HisServId FROM dic_map_his_dept hd INNER JOIN dic_map_dept_serv ds on hd.ID = ds.DEPT_MAP_ID where hd.IS_DELETED = 0 and ds.IS_DELETED =0 and hd.APT_DEPT_ID in @deptIds";
            var result = Query<DeptServModel>(selectSql, parms);
            return result?.ToList();
        }
        public List<AptInfos> SelectAptsByPat(string patId)
        {
            var parms = new Dictionary<string, object>();
            parms.Add("@patId", patId);
            var selectSql = @"SELECT a.*,
                               b.FIRST_NAME,
                               c.CN_NAME as HosNameCn,
                               c.EN_NAME as HosNameEn,
                               d.CN_NAME as DeptNameCn,
                               d.EN_NAME as DeptNameEn 
                        FROM out_apt_info a,
                             pub_pat_info b,
                             dic_hos c,
                             dic_dept d
                        WHERE a.PAT_ID = b.ID
                          AND a.HOS_ID = c.ID
                          and STATUS != 8
                          AND a.DEPT_ID = d.ID
                          AND c.IS_DELETED = false
                          AND d.IS_DELETED = false
                          AND a.PAT_ID = @patId
                          AND d.DEPT_TYPE = 0";
            var result = Query<AptInfos>(selectSql, parms);
            return result?.ToList();
        }

        public List<int> GetAllOutAptData()
        {
            var sql = @" SELECT ID From out_apt_info where APT_TIME > @BeginDate AND APT_TIME <@EndDate AND INNER_STATUS != 4";
            return Query<int>(sql, new { BeginDate = DateTime.Now, EndDate = DateTime.Now.AddHours(1) }).ToList();
        }

        public List<DicVaccine> Vaccine()
        {
            var sql = @"select ID,
                               VACCINE_CN_NAME as VaccineCnName,
                               VACCINE_EN_NAME as VaccineEnName,
                               VACCINE_TYPE as VaccineType,
                               CREATE_USER as CreateUser,
                               CREATE_TIME as CreateTime,
                               UPDATE_USER as UpdateUser,
                               UPDATE_TIME as UpdateTime,
                               `STATUS`
                        from dic_vaccine
                        where STATUS = 1
                        order by SORT ";
            return Query<DicVaccine>(sql).ToList();
        }

        public List<Out_Apt_Info> GetOutAptForQuest(int patId)
        {
            var sql = @" select * from out_apt_info where PAT_ID = @PatId And APT_TIME > @BeginDate And APT_TIME< @EndDate And (QuestId Is null Or QuestId=0) ";
            return Query<Out_Apt_Info>(sql, new { PatId = patId, BeginDate = DateTime.Now.Date.AddSeconds(1), EndDate = DateTime.Now.Date.AddDays(1).AddSeconds(-1) }).ToList();
        }

        public int UpdateOutAptForQuestId(List<int> id, int questId)
        {
            var sql = @" update out_apt_info Set QuestId = @QuestId where Id In @Ids ";
            return Execute(sql, new { QuestId = questId, Ids = id });
        }

        public int UpdateOutAptStatus(int id, int innerstatus)
        {
            var sql = @" update out_apt_info set INNER_STATUS = @INNERSTATUS,STATUS = @INNERSTATUS  WHERE ID = @ID ";
            return Execute(sql, new { INNERSTATUS = innerstatus, ID = id });
        }
        //小易确认线下预约使用
        public int ChangeInnerStatus(int id, int innerstatus)
        {
            var sql = @"update out_apt_info
                        set INNER_STATUS = @innerStatus,
                            UPDATE_TIME  = @updateTime
                        where ID = @id";
            return Execute(sql, new { innerStatus = innerstatus, id = id, updateTime = DateTime.Now });
        }

        public int UpdateOutAptInnerStatus(int id, int innerstatus, bool cancel = false)
        {
            var sql = @" update out_apt_info set INNER_STATUS = @INNERSTATUS ";
            if (cancel)
            {
                sql += " ,STATUS=@STATUS ";
            }
            sql += " WHERE ID = @ID ";
            return Execute(sql, new { INNERSTATUS = innerstatus, ID = id, STATUS = innerstatus });
        }
        public Out_Apt_Info GetOutAptInfo(int aptId)
        {
            var sql = @" SELECT * From out_apt_info where Id = @id";
            return Query<Out_Apt_Info>(sql, new { id = aptId }).FirstOrDefault();
        }
        public Out_Apt_Info FindByMrnUsrn(string patId, string aptUsrn)
        {
            var sql = @" select a.ID,
               EPISODE_ID,
               a.PAT_ID,
               HOS_ID,
               DEPT_ID,
               DOC_ID,
               APT_TIME,
               APT_PROJ,
               `STATUS`,
               APT_USRN,
               APPLY_USER_ID,
               CREATE_TIME,
               UPDATE_TIME,
               SOURCE_TYPE,
               REMARK,
               MATCH_CODE,
               INNER_STATUS,
               DISPOSE_TIME
        from out_apt_info a,
             pub_pat_info b
        where `STATUS` != 8
          and INNER_STATUS != 2
          and b.IS_DELETED = false
          and APT_USRN = @usrn
          and a.PAT_ID = b.ID
          and b.PAT_MRN = @mrn";

            return Query<Out_Apt_Info>(sql, new { mrn = patId, usrn = aptUsrn }).FirstOrDefault();
        }

        public Out_Apt_Info FindByCondition(string patId, string aptUsrn, int deptId, int docId, int hosId, string ProjId)
        {
            var sql = @" select a.ID,
               EPISODE_ID,
               a.PAT_ID,
               HOS_ID,
               DEPT_ID,
               DOC_ID,
               APT_TIME,
               APT_PROJ,
               `STATUS`,
               APT_USRN,
               APPLY_USER_ID,
               CREATE_TIME,
               UPDATE_TIME,
               SOURCE_TYPE,
               REMARK,
               MATCH_CODE,
               INNER_STATUS,
               DISPOSE_TIME
        from out_apt_info a,
             pub_pat_info b
        where `STATUS` != 8
          and INNER_STATUS != 2
          and b.IS_DELETED = false
          and APT_USRN = @usrn
          and a.PAT_ID = b.ID
          and b.PAT_MRN = @mrn
and a.HOS_ID = @HOS_ID and a.DEPT_ID=@DEPT_ID and a.APT_PROJ=@APT_PROJ and a.DOC_ID=@DOC_ID";

            return Query<Out_Apt_Info>(sql, new { mrn = patId, usrn = aptUsrn, HOS_ID = hosId, DEPT_ID = deptId, APT_PROJ = ProjId, DOC_ID = docId }).FirstOrDefault();
        }

        public Out_Apt_Info SelectByMatchCode(string matchCode)
        {
            var sql = @"select * from out_apt_info
                    where MATCH_CODE = @matchCode";
            return Query<Out_Apt_Info>(sql, new { matchCode = matchCode }).FirstOrDefault();
        }
        public Out_Apt_Info FindByMrnDate(string patId, string aptDate)
        {
            var sql = @"select a.ID,
               EPISODE_ID,
               a.PAT_ID,
               HOS_ID,
               DEPT_ID,
               DOC_ID,
               APT_TIME,
               APT_PROJ,
               `STATUS`,
               APT_USRN,
               APPLY_USER_ID,
               CREATE_TIME,
               UPDATE_TIME,
               SOURCE_TYPE,
               REMARK,
               MATCH_CODE,
               INNER_STATUS,
               DISPOSE_TIME
        from out_apt_info a,
             pub_pat_info b
        where `STATUS` = 4
          and b.IS_DELETED = false
          and a.APT_TIME = @aptTime
          and a.PAT_ID = b.ID
          and b.PAT_MRN = @mrn
        order by APT_TIME asc
        limit 0,1";
            return Query<Out_Apt_Info>(sql, new { aptTime = aptDate, mrn = patId }).FirstOrDefault();
        }

        public List<Dic_Vaccine_Serv> SelectVaccineServ(int hosId)
        {
            var sql = @"select * from dic_vaccine_serv
                    where HosId = @HosId and `Status` =1";
            return Query<Dic_Vaccine_Serv>(sql, new { HosId = hosId })?.ToList();
        }

        public VaccineAptInfo GetVaccineAptInfo(int id, int language)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select a.id,p.PAT_NAME as PatName,p.Id as PatId,p.BIRTHDAY as PatBirthday,p.PHONE_NO as PatPhone,c.PatType as PatType,c.VaccineBrand,c.Origin as VaccineOrigin,c.HosId,");
            if (language == 2)
            {
                sql.Append(@"v.VACCINE_EN_NAME as VaccineName,h.EN_ABBREVIATED_ADDR as HosName ");
            }
            else
            {
                sql.Append(@"v.VACCINE_CN_NAME as VaccineName,h.CN_ABBREVIATED_ADDR as HosName ");
            }
            sql.Append(@" from out_vaccine_appt a INNER JOIN out_vaccine_map_call m on a.ID = m.appvaccineid 
                            INNER JOIN out_vaccine_appt_call c on m.callid=c.ID 
                            LEFT JOIN dic_vaccine v on a.VACCINE_ID=v.id 
                            LEFT JOIN pub_pat_info p on a.PAT_ID = p.ID
                            LEFT JOIN dic_hos h on c.HosId = h.ID
                        WHERE  m.IsDelete = 0 and  a.ID = @ID");

            return Query<VaccineAptInfo>(sql.ToString(), new { ID = id })?.FirstOrDefault().DeEncrypt();
        }
        public Out_Apt_Info GetAptInfoByUsrn(string aptUsrn)
        {
            var sql = @"select * from out_apt_info
                    where APT_USRN = @APT_USRN and `STATUS` != 2 ";
            return Query<Out_Apt_Info>(sql, new { APT_USRN = aptUsrn }).FirstOrDefault();
        }

        public int UpdateVaccAptStatus(int Id, int status, bool isTcInterfaceError)
        {
            var sql = @" update out_vaccine_appt set `STATUS` = @STATUS,`UPDATE_TIME`=@UpdateTime,`IsTcInterfaceError`=@IsTcInterfaceError  WHERE ID = @ID ";
            return Execute(sql, new { STATUS = status, UpdateTime = DateTime.Now, IsTcInterfaceError = isTcInterfaceError, ID = Id });
        }
        public int BindAptId(int Id, int apptid)
        {
            var sql = @" update out_apt_info set `APT_ID` = @APT_ID WHERE ID = @ID ";
            return Execute(sql, new { APT_ID = apptid, ID = Id });
        }
        /// <summary>
        /// 获取线下前天12点短信
        /// </summary>
        /// <returns></returns>
        public List<Out_Apt_Info> SelectTomorrow()
        {
            var sql = @"SELECT *
                        FROM out_apt_info
                        WHERE TO_DAYS(APT_TIME) - TO_DAYS(NOW()) = 1
                          and INNER_STATUS & 4 > 0 ";
            return Query<Out_Apt_Info>(sql, null).ToList();
        }

        public List<ConfirmAptInfo> FindCheckedList()
        {
            var sql = @"SELECT b.PAT_NAME,
               b.PHONE_NO,
               a.ID
                FROM out_apt_info a,
                     pub_pat_info b
                WHERE a.`STATUS` = 4
                  and a.INNER_STATUS = 1
                  and a.DISPOSE_TIME < @limitTime
                  and DATE_FORMAT(a.DISPOSE_TIME, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
                  and a.APT_TIME >= NOW()
                  and b.IS_DELETED = false
                  and b.ID = a.PAT_ID";
            return Query<ConfirmAptInfo>(sql, new { limitTime = DateTime.Now.AddMinutes(-2) }).ToList();
        }

        public int InsertOutVaccineApptFlow(Out_Vaccine_Appt_Flow flow)
        {
            var sql = @" INSERT INTO `out_vaccine_appt_flow`(`REQUEST_PARAM`, `RESOUCE_TYPE`, `CREATE_USER`, `CREATE_TIME`, `STATUS`) VALUES (@REQUEST_PARAM, @RESOUCE_TYPE, @CREATE_USER, @CREATE_TIME, @STATUS) ";
            return Insert(sql, flow);
        }
        public int InsertOutVaccineAppt(Out_Vaccine_Appt model)
        {
            var sql = @" INSERT INTO `out_vaccine_appt`(`FLOW_ID`, `PAT_ID`, `USER_ID`, `VACCINE_ID`, `PAY_TYPE`, `IS_TREATMENT`, `INSURANCE_ID`, `INSURANCE_NO`, `PAY_IMAGE`, `HPV_TYPE`, `HPT_PIN`, `ORIGIN`, `STATUS`, `MATCH_CODE`, `APT_TIME`, `HOS_ID`, `DEPT_ID`, `DOC_ID`, `REMAKE`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `LANGUAGE`,`NewVaccineType`,`QueueNumber`,`Type`,`GroupId`,`IsTcInterfaceError`) 
VALUES ( @FLOW_ID, @PAT_ID, @USER_ID, @VACCINE_ID, @PAY_TYPE, @IS_TREATMENT, @INSURANCE_ID, @INSURANCE_NO, @PAY_IMAGE, @HPV_TYPE, @HPT_PIN, @ORIGIN, @STATUS, @MATCH_CODE, @APT_TIME, @HOS_ID, @DEPT_ID, @DOC_ID, @REMAKE, @CREATE_USER, @CREATE_TIME, @UPDATE_USER, @UPDATE_TIME, @LANGUAGE, @NewVaccineType,@QueueNumber,@Type,@GroupId,@IsTcInterfaceError)  ";
            return Insert(sql, model);
        }

        public List<Out_Apt_Info> SelectToday()
        {
            var sql = @"select *
                                from out_apt_info
                                where (INNER_STATUS & 8 > 0 or INNER_STATUS & 16 > 0 or INNER_STATUS & 32 > 0)
                                  and TO_DAYS(APT_TIME) = TO_DAYS(NOW())
                                  and CANCEL_REMARK is null
                                group by PAT_ID";
            return Query<Out_Apt_Info>(sql, null).ToList();
        }

        public List<Out_Apt_Info> GetPhysicalAptInfo(int patId)
        {
            var sql = @"select * from out_apt_info where PAT_ID  = @PAT_ID  and service = 'CON75' and `STATUS` != 2 and INNER_STATUS != 2 and APT_TIME > now()";
            return Query<Out_Apt_Info>(sql, new { PAT_ID = patId }).ToList();
        }

        public List<Out_Apt_Info> GetPhysicalAptInfos(List<int> patIds)
        {
            var sql = @"select * from out_apt_info where PAT_ID  in @PAT_ID  and service = 'CON75' and `STATUS` != 2 and INNER_STATUS != 2 and APT_TIME > now()";
            return Query<Out_Apt_Info>(sql, new { PAT_ID = patIds }).ToList();
        }

        public List<Out_Apt_Info> GetYearPhysicalNoticeApt()
        {
            var sql = @"select * from  out_apt_info where service = 'CON75' and DATE_FORMAT(ADDDATE(APT_TIME,350),'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') and PAT_ID not in (select PAT_ID from  out_apt_info where service = 'CON75' and DATE_FORMAT(ADDDATE(APT_TIME,350),'%Y-%m-%d') > NOW()) and INNER_STATUS in (8,32)";
            return Query<Out_Apt_Info>(sql, null).ToList();
        }

        public List<AptPatData> GetOutAptPatByUser(List<int> patIds)
        {//小红说 这里取当天内 及以后的预约做显示
            var sql = @"select Id,APT_TIME as AptTime,Service,PAT_ID as PatId,(select PAT_NAME from pub_pat_info where id = o.PAT_ID) as PatName,(select PAT_Mrn from pub_pat_info where id = o.PAT_ID) as PatMrn from out_apt_info o where APT_TIME > DATE(NOW()) and `STATUS` != 2 and INNER_STATUS != 2 and PAT_ID in @patIds";
            return Query<AptPatData>(sql, new { patIds = patIds }).ToList();
        }

        public List<AptDoc> GetHasConsultation(List<int> patIds, int language)
        {
            var sql = string.Empty;
            if (language == 1)
            {
                sql = @"select DISTINCT(a.DOC_ID),a.DOC_ID as DocId,d.CN_NAME as DocName,de.CN_NAME as DeptNames";
            }
            else
            {
                sql = @"select DISTINCT(a.DOC_ID),a.DOC_ID as DocId,d.EN_NAME as DocName,de.EN_NAME as DeptNames";
            }
            //and dd.`STATUS` in (" + status + @")
            //sql += @"d.LIST_IMAGE as DocImg,a.dept_id as DeptId,
            //                (select GROUP_CONCAT( DISTINCT(" + dept + @")) from dic_map_dept_doctor dd left join dic_dept d on APT_DEPT_ID = d.ID 
            //                where dd.APT_DOCTOR_ID = a.DOC_ID and dd.IS_DELETED = 0  GROUP BY dd.APT_DOCTOR_ID) AS DeptNames
            //         from out_apt_info a INNER JOIN dic_doctor d on a.DOC_ID = d.id
            //         where APT_TIME > DATE_SUB(CURDATE(), INTERVAL 3 MONTH) and APT_TIME<NOW() and `STATUS` != 2 and INNER_STATUS != 2
            //                and PAT_ID in (select PAT_ID from pub_map_user_pat where IS_DELETED = 0 and USER_ID = @USER_ID)";
            sql += @",d.LIST_IMAGE as DocImg,a.dept_id as DeptId from out_apt_info a INNER JOIN dic_doctor d on a.DOC_ID = d.id INNER JOIN dic_map_dept_doctor dd on a.DEPT_ID = dd.APT_DEPT_ID and a.HOS_ID=dd.APT_HOS_ID and a.DOC_ID=dd.APT_DOCTOR_ID INNER JOIN dic_dept de on a.DEPT_ID = de.id
                     where APT_TIME > DATE_SUB(CURDATE(), INTERVAL 3 MONTH) and APT_TIME<NOW() and a.`STATUS` != 2 and a.INNER_STATUS != 2
                            and PAT_ID in @patIds and dd.IS_DELETED=0";
            return Query<AptDoc>(sql, new { patIds = patIds }).ToList();
        }

        public Out_Apt_Info GetAptByCondition(AptCommitModel model)
        {
            var sql = @"select * from out_apt_info where pat_id = @pat_id and APT_TIME>@statTime and APT_TIME <@endTime and DEPT_ID = @DEPT_ID and  DOC_ID = @DOC_ID and HOS_ID = @HOS_ID and `STATUS` in (0,1,4) ";
            var statTime = model.AptTime.Value.ToString("yyyy-MM-dd");
            var endTime = Convert.ToDateTime(statTime).AddDays(1);
            return Query<Out_Apt_Info>(sql, new { pat_id = model.PatId, statTime = statTime, endTime = endTime, DEPT_ID = model.DeptId, DOC_ID = model.DocId, HOS_ID = model.HosId }).FirstOrDefault();
        }

        public async Task<AptDetail> GetOutAptInfoDetail(int aptId, int language)
        {
            var sql = string.Empty;
            if (language == 1)
            {
                sql = @"select a.id,Apt_Time,p.PAT_NAME as PatName,p.PAT_Mrn as PatMrn,p.Phone_no as Phone,p.ID as PatId,d.CN_NAME as DocName,d.LIST_IMAGE as DocImg,dept.CN_NAME as DocDept,
h.CN_ADDR as HosAddress,h.LATITUDE as HosLat,h.LONGITUDE as HosLon,P_ADDRESS_CN as HosParkingInfo,a.APPLY_USER_ID as UserId,INNER_STATUS as InnerStatus,h.CN_MOBILE as HosTel,h.CN_NAME as HosName,h.CN_ABBREVIATED_ADDR as HosShortName";
            }
            else
            {
                sql = @"select a.id,Apt_Time,p.PAT_NAME as PatName,p.PAT_Mrn as PatMrn,p.Phone_no as Phone,p.ID as PatId,d.EN_NAME as DocName,d.LIST_IMAGE as DocImg,dept.EN_NAME as DocDept,
h.EN_ADDR as HosAddress,h.LATITUDE as HosLat,h.LONGITUDE as HosLon,P_ADDRESS_EN as HosParkingInfo,a.APPLY_USER_ID as UserId,INNER_STATUS as InnerStatus,h.EN_MOBILE as HosTel,h.EN_NAME as HosName,h.EN_ABBREVIATED_ADDR as HosShortName";
            }
            sql += @" ,d.ID AS DocId,
	dept.ID AS DeptId,
	h.ID AS HosId,h.P_ADDRESS_LAT as HosParkLat,h.P_ADDRESS_LON as HosParkLon,a.`Status`,a.Create_Time as AptCreateTime,a.REMARK from out_apt_info a INNER JOIN pub_pat_info p on a.PAT_ID = p.id 
                        LEFT JOIN dic_doctor d on a.DOC_ID = d.ID
                        LEFT JOIN dic_dept dept on a.DEPT_ID = dept.ID
                        LEFT JOIN dic_hos h on a.HOS_ID = h.ID
                      where a.ID = @ID ";
            return QueryAsync<AptDetail>(sql, new { ID = aptId }).Result?.FirstOrDefault();
        }

        public VaccAptModel GetVaccAptInfo(int aptId, int language)
        {
            var sql = string.Empty;
            if (language == 1)
            {
                sql = @"select ac.VaccineBrand as VaccineBrand,VACCINE_CN_NAME as VaccineName";
            }
            else
            {
                sql = @"select ac.VaccineBrandEn as VaccineBrand,VACCINE_EN_NAME as VaccineName";
            }
            sql += @" from out_apt_info a INNER JOIN out_vaccine_appt va on a.APT_ID=va.ID INNER JOIN out_vaccine_map_call mc on mc.AppVaccineId=va.ID INNER JOIN out_vaccine_appt_call ac on mc.CallId= ac.ID INNER JOIN dic_vaccine v on va.VACCINE_ID = v.ID where a.id = @id ";
            return QueryAsync<VaccAptModel>(sql, new { id = aptId }).Result?.FirstOrDefault();
        }
    }
}
