EleKitService/RDH.Data/PatientInfoBLL.ext.cs

using RDH.Data.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RDH.Data.BLL
{
    public partial class PatientInfoBLL : IPatientInfoBLL
    {
        #region For Join

        private const string PATIENT_INFO_TABLE_NAME = "PATIENT_INFO";
        private const string PATIENT_INFO_MODEL_NAME = "PatientInfo";
        private const string PATIENT_INFO_KEY = "KEY";
        private const string PATIENT_INFO_REF_KEY = "SECTION_KEY";
        private const bool PATIENT_INFO_IS_SNAPSHOT = false;

        protected Dictionary<string, string> SectionColumnPropMaps = new Dictionary<string, string>();
        private const string SECTION_TABLE_NAME = "SECTION";
        private const string SECTION_MODEL_NAME = "Section";
        private const string SECTION_KEY = "KEY";
        private const bool SECTION_IS_SNAPSHOT = false;

        protected void InitSectionMaps()
        {
            SectionColumnPropMaps.Clear();
            SectionColumnPropMaps.Add("KEY", "Key");
            SectionColumnPropMaps.Add("CREATE_BY", "CreateBy");
            SectionColumnPropMaps.Add("CREATE_TIME", "CreateTime");
            SectionColumnPropMaps.Add("IS_ACTIVE", "IsActive");
            SectionColumnPropMaps.Add("IS_DELETE", "IsDelete");
            SectionColumnPropMaps.Add("SECTION_ID", "SectionId");
            SectionColumnPropMaps.Add("SECTION_NAME", "SectionName");
            SectionColumnPropMaps.Add("SECTION_LEADER", "SectionLeader");
            SectionColumnPropMaps.Add("SECTION_STATES", "SectionStates");
            SectionColumnPropMaps.Add("SECTION_DESCRIPTION", "SectionDescription");//COLUMN_MAPS
        }
        #endregion


        /// <summary>
        /// Time:2013-11-06 19:00:56
        /// Aouthor：
        /// Description：
        /// </summary>
        /// <returns>
        /// return All Item Info
        /// </returns>
        public IEnumerable<PatientInfo> ListPatientInfo()
        {
            InitSectionMaps();
            var ListPatientInfo = SqlMapper.Query<PatientInfo, Section, PatientInfo>
                (ConnectionFactory.Current.GetSessionConnection(), GenerateJoinSql(),
                (pt, sect) =>
                {
                    pt.Section = sect;
                    return pt;
                }, splitOn: "split1");
            return ListPatientInfo;

        }

        public IEnumerable<PatientInfo> ListPatientInfoByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            InitSectionMaps();
            StringBuilder SectionValues = new StringBuilder();
            foreach (var c in SectionColumnPropMaps)
            {
                SectionValues.AppendFormat("sect.\"{0}\" \"{1}\",", c.Key, c.Value);
            }
            SectionValues.Remove(SectionValues.Length - 1, 1);


            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TP.KEY as Key,TP.SNAPSHOT_KEY as SnapshotKey,TP.START_TIME as StartTime,TP.END_TIME as EndTime ,");
            strSql.Append("TP.CREATE_BY as CreateBy,TP.CREATE_TIME as CreateTime,TP.PATIENT_ID as PatientId,TP.SOURCE as sOURCE,");
            strSql.Append("TP.ENCOUNTER_ID as EncounterId,TP.IN_TIME AS InTime,TP.OUT_TIME as OutTime, TP.SECTION_KEY as SectionKey,TP.NAME as Name,");
            strSql.AppendFormat("TP.BIRTHDAY as Birthday,TP.GENDER as Gender,TP.IDENTITY as Identity,TP.MEDICIAL_CARD as MedicialCard,TP.ADDRESS as Address,TP.TELEPHONE as Telephone,'' as split1, {0} FROM ( ", SectionValues);
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.SNAPSHOT_KEY desc");
            }
            strSql.Append(")AS num, T.*  from PATIENT_INFO T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" AND " + strWhere);
            }
            strSql.Append(" ) TP left join section sect on tp.SECTION_KEY = SECT.KEY ");
            strSql.AppendFormat(" WHERE TP.num between {0} and {1}", startIndex, endIndex);
            var List = SqlMapper.Query<PatientInfo, Section, PatientInfo>(ConnectionFactory.Current.GetSessionConnection(),
                strSql.ToString(), (pi, sect) =>
            {
                pi.Section = sect;
                return pi;
            }, splitOn: "split1");

            return List;
        }

        /// <summary>
        /// 根据指定的时间范围获取带有未发医嘱的患者
        /// </summary>
        public List<PatientInfo> ListPatientInfoWithFormulary(DateTime? formularyTimeStart, DateTime? formularyTimeEnd) 
        {
            Object oParam;
            StringBuilder sqlSelect = new StringBuilder();
            sqlSelect.AppendFormat("SELECT {0} FROM {1} WHERE key IN "
                + "(SELECT f.patient_key FROM sate_formulary f WHERE (f.precessed IS NULL OR f.precessed=:isPrecessed)",
                BuildGetSql(),
                this.TABLE_NAME);
            if (formularyTimeStart != null)
            {
                sqlSelect.Append(" AND f.formulary_date BETWEEN :timeStart AND :timeEnd");
                oParam = new
                {
                    isPrecessed = false,
                    timeStart = formularyTimeStart.Value,
                    timeEnd = formularyTimeEnd.GetValueOrDefault()
                };
            }
            else
            {
                oParam = new
                {
                    isPrecessed = false
                };
            }
            sqlSelect.Append(")");
            var rlt = SqlMapper.Query<PatientInfo>(ConnectionFactory.Current.GetSessionConnection(),
                sqlSelect.ToString());
            return rlt == null ? null : rlt.ToList();
        }

        private string GenerateJoinSql()
        { 
            StringBuilder PatientInfoValues = new StringBuilder();
            foreach (var c in ColumnPropMaps)
            {
                PatientInfoValues.AppendFormat("pt.\"{0}\" \"{1}\",", c.Key, c.Value);
            }
            PatientInfoValues.Remove(PatientInfoValues.Length - 1, 1);


            StringBuilder SectionValues = new StringBuilder();
            foreach (var c in SectionColumnPropMaps)
            {
                SectionValues.AppendFormat("sect.\"{0}\" \"{1}\",", c.Key, c.Value);
            }
            SectionValues.Remove(SectionValues.Length - 1, 1);

            StringBuilder from = new StringBuilder();
            from.Append(TABLE_NAME);
            from.Append(" PT ");
            from.Append(" LEFT JOIN ");
            from.Append(SECTION_TABLE_NAME);
            from.Append(" sect ");
            from.AppendFormat(" ON PT.{0} = sect.{1} ",PATIENT_INFO_REF_KEY, SECTION_KEY);

            return string.Format("SELECT {0},'' as split1,{1} FROM {2}", PatientInfoValues, SectionValues, from);
        }

        public PatientInfo GetByPatientEncounterId(string encounterId) 
        {
            var sql = BuildBaseSql();
            return SqlMapper.Query<PatientInfo>(ConnectionFactory.Current.GetSessionConnection(), sql + "  WHERE (ENCOUNTER_ID = :EncounterId or PATIENT_ID =:EncounterId )", new { EncounterId = encounterId }).FirstOrDefault();
        
        }

        public IEnumerable<PatientInfo> SearchPatients(String filterText) 
        {
            return SqlMapper.Query<PatientInfo>(ConnectionFactory.Current.GetSessionConnection(),
                String.IsNullOrEmpty(filterText) 
                    ? BuildBaseSql()
                    : String.Format("{0} WHERE NAME LIKE '%'||:filterText||'%' OR ENCOUNTER_ID LIKE '%'||:filterText||'%'",
                        BuildBaseSql()),
                new { filterText = filterText });
        }
    }
}