package com.diagbot.util;

import com.diagbot.entity.*;
import com.google.common.collect.Lists;

import java.sql.*;
import java.util.List;
import java.util.ResourceBundle;

public class TZDBConn {
    private static final String DRIVER = getValue("jdbc.driverClassName");
    private static final String URL = getValue("jdbc.url");
    private static final String USERNAME = getValue("jdbc.username");
    private static final String PASSWORD = getValue("jdbc.password");

    public static final Long HOSPITAL_ID = Long.valueOf("14");//1:长兴，2：邵逸夫，3：台州市立医院

    private static Connection connection = null;
    private static PreparedStatement sta = null;
    private static ResultSet rs = null;

    /**
     * 读取属性文件中的信息
     *
     * @param key
     * @return
     */
    private static String getValue(String key) {
        // 资源包绑定
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        return bundle.getString(key);
    }

    /**
     * 加载驱动程序
     */
    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * @return 连接对象
     */
    public Connection getConnection() {
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * @param sql sql语句
     * @param obj 参数
     * @return 数据集合
     */
    public ResultSet Query(String sql, Object... obj) {
        connection = getConnection();
        try {
            sta = connection.prepareStatement(sql);
            if (obj != null) {
                for (int i = 0; i < obj.length; i++) {
                    sta.setObject(i + 1, obj[i]);
                }
            }
            rs = sta.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 关闭资源
     */
    public void close() {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (sta != null) {
                    sta.close();
                }
            } catch (SQLException e2) {
                e2.printStackTrace();
            } finally {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    /**
     * 医生信息
     *
     * @return
     */
    public List<BasDoctorInfo> getBasDoctorInfo(String sql) {
        List<BasDoctorInfo> basDoctorInfoList = Lists.newLinkedList();
        TZDBConn dbconn = new TZDBConn();
        try {
            rs = dbconn.Query(sql, null);
            while (rs.next()) {
                BasDoctorInfo basDoctorInfo = new BasDoctorInfo();
                basDoctorInfo.setDoctorId(rs.getString("ZGID"));//医生ID
                basDoctorInfo.setHospitalId(HOSPITAL_ID);//医院ID
                basDoctorInfo.setDeptId(rs.getString("KSDM"));//科室ID
                basDoctorInfo.setName(rs.getString("XM"));//医生姓名
                basDoctorInfo.setProfessor(rs.getString("MC"));//职称
                basDoctorInfo.setGroupId(rs.getString("YSZH"));//医疗组id
                basDoctorInfoList.add(basDoctorInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }

        return basDoctorInfoList;
    }

    /**
     * 从视图中获取医院科室数据，根据修改时间同步数据
     *
     * @return
     */
    public List<BasDeptInfo> getDeptInfo(String sql) {
        List<BasDeptInfo> basDeptInfoList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                BasDeptInfo basDeptInfo = new BasDeptInfo();
                basDeptInfo.setDeptId(rs.getString("ZZKSDM"));//科室编码
                basDeptInfo.setHospitalId(HOSPITAL_ID);//医院ID
                basDeptInfo.setDeptName(rs.getString("ZZKSMC"));//科室名称
                //basDeptInfoVO.setDeptType(rs.getString("ZZKSLB"));//科室类别
                basDeptInfo.setSpell(rs.getString("HZSRM1"));//首字母拼音
                //basDeptInfoVO.setStation(rs.getString("FLKSID"));//区域类别

                basDeptInfoList.add(basDeptInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return basDeptInfoList;
    }

    /**
     * 病案诊断
     *
     * @return
     */
    public List<HomeDiagnoseInfo> getHomeDiagnose(String sql) {
        List<HomeDiagnoseInfo> homeDiagnoseVOList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                HomeDiagnoseInfo homeDiagnose = new HomeDiagnoseInfo();
                homeDiagnose.setHomePageId(rs.getString("homePageId"));//病案首页ID
                homeDiagnose.setHospitalId(HOSPITAL_ID);//医院ID
                homeDiagnose.setDiagnoseOrderNo(rs.getString("diagnoseOrderNo"));//诊断序号
                homeDiagnose.setDiagnoseName(rs.getString("diagnoseType"));//诊断名称
                homeDiagnose.setDiagnoseType(rs.getString("diagnoseTypeShort"));//诊断类别
                homeDiagnose.setDiagnoseTypeShort(rs.getString("diagnoseName"));//诊断判别
                homeDiagnose.setBehospitalType(rs.getString("behospitalType"));//入院情况
                homeDiagnose.setLeaveHospitalType(rs.getString("leaveHospitalType"));//出院情况
                homeDiagnose.setPathologyDiagnose(rs.getString("pathologyDiagnose"));//病理号
                homeDiagnose.setIcdCode(rs.getString("icdCode"));//诊断编码
                homeDiagnoseVOList.add(homeDiagnose);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }

        return homeDiagnoseVOList;
    }

    /**
     * 病案手术
     *
     * @return
     */
    public List<HomeOperationInfo> getHomeOperation(String sql) {
        List<HomeOperationInfo> homeOperationVOList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                HomeOperationInfo homeOperationVO = new HomeOperationInfo();
                homeOperationVO.setHomePageId(rs.getString("homePageId"));//病案首页ID
                homeOperationVO.setHospitalId(HOSPITAL_ID);//医院ID
                homeOperationVO.setOperationOrderNo(rs.getString("operationOrderNo"));//手术序号
                homeOperationVO.setOperationDate(rs.getDate("operationDate"));//手术日期
                homeOperationVO.setOperationCode(rs.getString("operationCode"));//手术编码
                homeOperationVO.setOperationDoctorId(rs.getString("operationDoctorId"));//手术医生
                homeOperationVO.setFirstAssistantId(rs.getString("firstAssistantId"));//一助医生
                homeOperationVO.setSecondAssistantId(rs.getString("secondAssistantId"));//二助医生
                homeOperationVO.setCutLevel(rs.getString("cutLevel"));//切口等级
                homeOperationVO.setHealingLevel(rs.getString("healingLevel"));//愈合等级
                homeOperationVO.setOperationName(rs.getString("operationName"));//手术名称
                homeOperationVO.setOperationLevel(rs.getString("operationLevel"));//手术级别
                homeOperationVO.setAnaesthesiaName(rs.getString("anaesthesiaName"));//麻醉方式
                homeOperationVO.setAnaesthesiaDoctor(rs.getString("anaesthesiaDoctor"));//麻醉医师
                homeOperationVO.setShamOperationName(rs.getString("shamOperationName"));//拟手术名称
                homeOperationVOList.add(homeOperationVO);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        List<HomeOperationInfo> homeOperationList = BeanUtil.listCopyTo(homeOperationVOList, HomeOperationInfo.class);
        return homeOperationList;
    }

    /**
     * 从视图获取病案首页信息
     * 获取近几个月的数据
     */
    public List<HomePage> getHomePage(String sql) {
        List<HomePage> homePageList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                HomePage homePageVO = new HomePage();
                homePageVO.setHomePageId(rs.getString("homePageId"));//病案首页ID
                homePageVO.setHospitalId(HOSPITAL_ID);//医院ID
                homePageVO.setBehospitalCode(rs.getString("behospitalCode"));//病人住院序号
                homePageVO.setHospitalCode(rs.getString("hospitalCode"));//组织机构id
                homePageVO.setHospitalName(rs.getString("hospitalName"));//医疗机构名称
                homePageVO.setOrgCode(rs.getString("orgCode"));//医疗机构代码
                homePageVO.setPayType(rs.getString("payType"));//医疗付费方式
                homePageVO.setHealthCard(rs.getString("healthCard"));//健康卡号
                homePageVO.setBehospitalNum(rs.getString("behospitalNum"));//住院次数
                homePageVO.setFileCode(rs.getString("fileCode"));//病案号
                homePageVO.setName(rs.getString("name"));//姓名
                homePageVO.setSex(rs.getString("sex"));//性别
                homePageVO.setBirthday(rs.getDate("birthday"));//出生日期
                homePageVO.setAge(rs.getString("age").equals("-") ? null : rs.getString("BRDQNL"));//病人年龄
                homePageVO.setAgeUnit(rs.getString("ageUnit"));//年龄单位
                homePageVO.setNationality(rs.getString("nationality"));//国籍
                homePageVO.setNewbornMonth(rs.getString("newbornMonth"));//新生儿出生月数
                homePageVO.setNewbornDay(rs.getString("newbornDay"));//新生儿出生天数
                homePageVO.setNewbornWeight(rs.getString("newbornWeight"));//新生儿出生体重
                homePageVO.setNewbornBehospitalWeight(rs.getString("newbornBehospitalWeight"));//新生儿入院体重
                homePageVO.setBornAddress(rs.getString("bornAddress"));//出生地
                homePageVO.setBornPlace(rs.getString("bornPlace"));//籍贯
                homePageVO.setNation(rs.getString("nation"));//民族
                homePageVO.setIdentityCardNo(rs.getString("identityCardNo"));//身份证号
                homePageVO.setJobType(rs.getString("jobType"));//职业
                homePageVO.setMarriage(rs.getString("marriage"));//婚姻
                homePageVO.setCurAddress(rs.getString("curAddress"));//现住址
                homePageVO.setCurPhone(rs.getString("curPhone"));//现住址电话
                homePageVO.setCurPostCode(rs.getString("curPostCode"));//现住址邮编
                homePageVO.setResidenceAddress(rs.getString("residenceAddress"));//户口地址
                homePageVO.setResidencePostCode(rs.getString("residencePostCode"));//户口地址邮编
                homePageVO.setWorkAddress(rs.getString("workAddress"));//工作单位
                homePageVO.setWorkPhone(rs.getString("workPhone"));//工作单位电话
                homePageVO.setWorkPostCode(rs.getString("workPostCode"));//工作单位邮编
                homePageVO.setContactName(rs.getString("contactName"));//联系人姓名
                homePageVO.setContactRelation(rs.getString("contactRelation"));//联系人关系
                homePageVO.setContactAddress(rs.getString("contactAddress"));//联系人地址
                homePageVO.setContactPhone(rs.getString("contactPhone"));//联系人电话
                homePageVO.setBehospitalWay(rs.getString("behospitalWay"));//入院途径
                homePageVO.setBehospitalDate(rs.getDate("behospitalDate"));//入院时间
                homePageVO.setBehospitalDept(rs.getString("behospitalDept"));//入院科室
                homePageVO.setBehospitalWard(rs.getString("behospitalWard"));//入院病房
                homePageVO.setBehospitalBedId(rs.getString("behospitalBedId"));//入院床位序号
                homePageVO.setBehospitalBedCode(rs.getString("behospitalBedCode"));//入院床位号码
                homePageVO.setChangeDept(rs.getString("changeDept"));//转科科别
                homePageVO.setLeaveHospitalDate(rs.getDate("leaveHospitalDate"));//出院时间
                homePageVO.setLeaveHospitalDept(rs.getString("leaveHospitalDept"));//出院科别
                homePageVO.setLeaveHospitalWard(rs.getString("leaveHospitalWard"));//出院病房
                homePageVO.setLeaveHospitalBedId(rs.getString("leaveHospitalBedId"));//出院床位序号
                homePageVO.setLeaveHospitalBedCode(rs.getString("leaveHospitalBedCode"));//出院床位号码
                homePageVO.setBehospitalDayNum(rs.getString("behospitalDayNum"));//实际住院天数
                homePageVO.setOutpatientEmrDiagnose(rs.getString("outpatientEmrDiagnose"));//门急诊诊断
                homePageVO.setOutpatientEmrDiagnoseCode(rs.getString("outpatientEmrDiagnoseCode"));//门急诊诊断编码
                homePageVO.setPoisonFactor(rs.getString("poisonFactor"));//损伤中毒因素
                homePageVO.setPoisonFactorCode(rs.getString("poisonFactorCode"));//损伤中毒因素编码
                homePageVO.setPathologyDiagnose(rs.getString("pathologyDiagnose"));//病理诊断
                homePageVO.setPathologyDiagnoseCode(rs.getString("pathologyDiagnoseCode"));//病理诊断编码
                homePageVO.setPathologyDiagnoseId(rs.getString("pathologyDiagnoseId"));//病理诊断编号
                homePageVO.setIsMedAllergy(rs.getString("isMedAllergy"));//药物过敏
                homePageVO.setMedAllergyName(rs.getString("medAllergyName"));//过敏药物
                homePageVO.setAutopsy(rs.getString("autopsy"));//死亡患者尸检
                homePageVO.setBloodType(rs.getString("bloodType"));//血型
                homePageVO.setRh(rs.getString("rh"));//Rh
                homePageVO.setDeptDirector(rs.getString("deptDirector"));//科主任
                homePageVO.setDirectorDoctor(rs.getString("directorDoctor"));//主任医师
                homePageVO.setAttendingDoctor(rs.getString("attendingDoctor"));//主治医师
                homePageVO.setBehospitalDoctor(rs.getString("behospitalDoctor"));//住院医师
                homePageVO.setResponseNurse(rs.getString("responseNurse"));//责任护士
                homePageVO.setStudyDoctor(rs.getString("studyDoctor"));//进修医师
                homePageVO.setPracticeDoctor(rs.getString("practiceDoctor"));//实习医师
                homePageVO.setEncodeMan(rs.getString("encodeMan"));//编码员
                homePageVO.setHomePageQuality(rs.getString("homePageQuality"));//病案质量
                homePageVO.setQcDoctor(rs.getString("qcDoctor"));//质控医师
                homePageVO.setQcNurse(rs.getString("qcNurse"));//质控护士
                if (!"".equals(rs.getString("qcDate"))) {
                    homePageVO.setQcDate(DateUtil.parseDate(rs.getString("qcDate"), DateUtil.DATE_TIME_FORMAT));//质控日期
                }
                homePageVO.setLeaveHospitalType(rs.getString("leaveHospitalType"));//离院方式
                homePageVO.setAcceptOrgCode(rs.getString("acceptOrgCode"));//接收机构名称
                homePageVO.setAgainBehospitalPlan(rs.getString("againBehospitalPlan"));//31天内再住院计划
                homePageVO.setAgainBehospitalGoal(rs.getString("againBehospitalGoal"));//再住院目的
                homePageVO.setTbiBeforeDay(rs.getString("tbiBeforeDay"));//颅脑损伤患者昏迷前天数
                homePageVO.setTbiBeforeHour(rs.getString("tbiBeforeHour"));//颅脑损伤患者昏迷前小时
                homePageVO.setTbiBeforeMinute(rs.getString("tbiBeforeMinute"));//颅脑损伤患者昏迷前分钟
                homePageVO.setTbiAfterDay(rs.getString("tbiAfterDay"));//颅脑损伤患者昏迷后天数
                homePageVO.setTbiAfterHour(rs.getString("tbiAfterHour"));//颅脑损伤患者昏迷后小时
                homePageVO.setTbiAfterMinute(rs.getString("tbiAfterMinute"));//颅脑损伤患者昏迷后分钟
                homePageVO.setTotalFee(rs.getString("totalFee"));//总费用
                homePageVO.setOwnFee(rs.getString("ownFee"));//自付金额
                homePageVO.setGeneralFee(rs.getString("generalFee"));//一般医疗服务费
                homePageVO.setServiceFee(rs.getString("serviceFee"));//一般治疗服务费
                homePageVO.setNurseFee(rs.getString("nurseFee"));//护理费
                homePageVO.setOtherFee(rs.getString("otherFee"));//其他费用
                homePageVO.setPathologyFee(rs.getString("pathologyFee"));//病理诊断费
                homePageVO.setLabFee(rs.getString("labFee"));//实验室诊断费
                homePageVO.setPacsFee(rs.getString("pacsFee"));//影像学诊断费
                homePageVO.setClinicDiagnoseFee(rs.getString("clinicDiagnoseFee"));//临床诊断项目费
                homePageVO.setNotOperationFee(rs.getString("notOperationFee"));//非手术治疗项目费
                homePageVO.setClinicPhysicFee(rs.getString("clinicPhysicFee"));//临床物理治疗费
                homePageVO.setOperationTreatFee(rs.getString("operationTreatFee"));//手术治疗费
                homePageVO.setAnaesthesiaFee(rs.getString("anaesthesiaFee"));//麻醉费
                homePageVO.setOperationFee(rs.getString("operationFee"));//手术费
                homePageVO.setHealthTypeFee(rs.getString("healthTypeFee"));//康复类
                homePageVO.setChnTreatFee(rs.getString("chnTreatFee"));//中医治疗费
                homePageVO.setWesternMedFee(rs.getString("westernMedFee"));//西药费
                homePageVO.setAntibiosisFee(rs.getString("antibiosisFee"));//抗菌药物费用
                homePageVO.setChnMedFee(rs.getString("chnMedFee"));//中成药费
                homePageVO.setChnHerbFee(rs.getString("chnHerbFee"));//中草药费
                homePageVO.setBloodFee(rs.getString("bloodFee"));//血费
                homePageVO.setAlbumenFee(rs.getString("albumenFee"));//白蛋白类制品费
                homePageVO.setGlobulinFee(rs.getString("globulinFee"));//球蛋白类制品费
                homePageVO.setBloodFactorFee(rs.getString("bloodFactorFee"));//凝血因子类制品费
                homePageVO.setCellFactorFee(rs.getString("cellFactorFee"));//细胞因子类制品费
                homePageVO.setCheckMaterialFee(rs.getString("checkMaterialFee"));//检查用一次性医用材料费
                homePageVO.setTreatMaterialFee(rs.getString("treatMaterialFee"));//治疗用一次性医用材料费
                homePageVO.setOperationMaterialFee(rs.getString("operationMaterialFee"));//手术用一次性医用材料费
                homePageVO.setOtherTypeFee(rs.getString("otherTypeFee"));//其他类其他费
                homePageVO.setSingleDiagManage(rs.getString("singleDiagManage"));//单病种管理
                homePageVO.setClinicPathwayManage(rs.getString("clinicPathwayManage"));//临床路径管理
                homePageVO.setIsOutpatientBehospital(rs.getString("isOutpatientBehospital"));//门诊与住院
                homePageVO.setIsLeaveBehospital(rs.getString("isLeaveBehospital"));//入院与出院
                homePageVO.setIsOperationBeforeAfter(rs.getString("isOperationBeforeAfter"));//术前与术后
                homePageVO.setIsClinicPathology(rs.getString("isClinicPathology"));//临床与病理
                homePageVO.setIsRadiatePathology(rs.getString("isRadiatePathology"));//放射与病理
                homePageVO.setRescueSuccessNum(rs.getString("rescueNum"));//病人抢救次数
                homePageVO.setRescueSuccessNum(rs.getString("rescueSuccessNum"));//病人抢救成功次数
                homePageVO.setIsAutoLeavehospital(rs.getString("isAutoLeavehospital"));//是否为自动出院
                homePageVO.setReturnToType(rs.getString("returnToType"));//转归情况HomePage homePageVO=new HomePage();
                homePageList.add(homePageVO);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return homePageList;
    }

    /**
     * 获取病历信息
     *
     * @return
     */
    public List<BehospitalInfo> getBehospitalInfo(String sql) {
        List<BehospitalInfo> behospitalInfoList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                BehospitalInfo behospitalInfo = new BehospitalInfo();
                behospitalInfo.setBehospitalCode(rs.getString("behospitalCode"));//病人住院ID
                behospitalInfo.setHospitalId(HOSPITAL_ID);//医院ID
                behospitalInfo.setName(rs.getString("name"));//姓名
                behospitalInfo.setSex(rs.getString("sex"));//性别
                behospitalInfo.setBirthday(rs.getDate("birthday"));//出生日期
                behospitalInfo.setFileCode(rs.getString("fileCode"));//档案号
                behospitalInfo.setWardCode(rs.getString("wardCode"));//病区编码
                behospitalInfo.setWardName(rs.getString("wardName"));//病区名称
                behospitalInfo.setBehDeptId(rs.getString("behDeptId"));//住院科室ID
                behospitalInfo.setBehDeptName(rs.getString("behDeptName"));//住院科室名称
                behospitalInfo.setBedCode(rs.getString("bedCode"));//床位号
                behospitalInfo.setBedName(rs.getString("bedName"));//床位名称
                behospitalInfo.setInsuranceName(rs.getString("insuranceName"));//医保类别
                behospitalInfo.setJobType(rs.getString("jobType"));//职业
                behospitalInfo.setBehospitalDate(rs.getString("behospitalDate") != null ? DateUtil.parseDateTime(rs.getString("behospitalDate")) : null);//入院时间
                behospitalInfo.setLeaveHospitalDate(rs.getString("leaveHospitalDate") != null ? DateUtil.parseDateTime(rs.getString("leaveHospitalDate")) : null);//出院时间
                behospitalInfo.setDiagnoseIcd(rs.getString("diagnoseIcd"));//疾病ICD编码
                behospitalInfo.setDiagnose(rs.getString("diagnose"));//疾病名称
                behospitalInfo.setBehDoctorId(rs.getString("behDoctorId"));//住院医生ID
//				behospitalInfo.setBehDoctorName(rs.getString("behDoctorName"));//住院医生姓名
                behospitalInfo.setDirectorDoctorId(rs.getString("directorDoctorId"));//主任医师ID
//				behospitalInfo.setDirectorDoctorName(rs.getString("directorDoctorName"));//主任医师姓名
                behospitalInfo.setDoctorId(rs.getString("doctorId"));//主治医生ID
//				behospitalInfo.setDoctorName(rs.getString("doctorName"));//主治医生姓名

                behospitalInfoList.add(behospitalInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }

        return behospitalInfoList;
    }

    /**
     * 从视图中获取医嘱信息
     *
     * @return
     */
    public List<DoctorAdvice> getDoctorAdvice(String sql) {
        List<DoctorAdvice> doctorAdviceList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                DoctorAdvice doctorAdviceVO = new DoctorAdvice();
                doctorAdviceVO.setDoctorAdviceId(rs.getString("doctorAdviceId"));//病人医嘱ID
                doctorAdviceVO.setHospitalId(HOSPITAL_ID);//医院ID
                doctorAdviceVO.setBehospitalCode(rs.getString("behospitalCode"));//病人ID
                doctorAdviceVO.setOrderDoctorName(rs.getString("orderDoctorName"));//医生开单判别
                doctorAdviceVO.setFrequency(rs.getString("frequency"));//医嘱频率判别
                doctorAdviceVO.setParentTypeId(rs.getString("parentTypeId"));//父类医嘱ID
                doctorAdviceVO.setDoctorAdviceType(rs.getString("doctorAdviceType"));//医嘱类型判别
                doctorAdviceVO.setUsageNum(rs.getString("usageNum"));//一次使用数量
                doctorAdviceVO.setUsageUnit(rs.getString("usageUnit"));//一次用量单位
                doctorAdviceVO.setDose(rs.getString("dose"));//医嘱单次剂量
                doctorAdviceVO.setDoseUnit(rs.getString("doseUnit"));//单次剂量单位
                doctorAdviceVO.setMedModeType(rs.getString("medModeType"));//给药方式
                doctorAdviceVO.setMedicineType(rs.getString("medicineType"));//药品类别
                doctorAdviceVO.setDaFrequency(rs.getString("daFrequency"));//医嘱频率
                doctorAdviceVO.setDaDealType(rs.getString("daDealType"));//医嘱处理类型
                doctorAdviceVO.setDaStartDate(rs.getString("daStartDate") != null ? DateUtil.parseDateTime(rs.getString("daStartDate")) : null);//医嘱开始时间
                doctorAdviceVO.setDaItemName(rs.getString("daItemName"));//医嘱项目名称
                doctorAdviceVO.setDaStatus(rs.getString("daStatus"));//医嘱状态判别
                doctorAdviceVO.setDaStopDate(rs.getString("daStopDate") != null ? DateUtil.parseDateTime(rs.getString("daStopDate")) : null);//医嘱结束时间
                doctorAdviceVO.setDaGroupNo(rs.getString("daGroupNo"));//医嘱同组序号
                doctorAdviceVO.setDaPrescriptionType(rs.getString("daPrescriptionType"));//医嘱处方类型
                doctorAdviceVO.setDaMedType(rs.getString("daMedType"));//医嘱领药类型
                doctorAdviceVO.setDoctorNotice(rs.getString("doctorNotice"));//医生嘱托
                doctorAdviceVO.setDoctorId(rs.getString("doctorId"));//开单医生ID
                doctorAdviceVO.setDoctorName(rs.getString("doctorName"));//开单医生姓名

                doctorAdviceList.add(doctorAdviceVO);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return doctorAdviceList;
    }

    /**
     * 从视图中获取病历记录
     *
     * @return
     */
    public List<MedicalRecord> getMedicalRecord(String sql) {
        List<MedicalRecord> medicalRecordList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                MedicalRecord medicalRecord = new MedicalRecord();
                medicalRecord.setRecId(rs.getString("recId"));
                medicalRecord.setHospitalId(HOSPITAL_ID);//医院ID
                medicalRecord.setBehospitalCode(rs.getString("behospitalCode"));//病人住院ID
                medicalRecord.setOrgCode(rs.getString("orgCode"));//组织机构代码
                medicalRecord.setRecTypeId(rs.getString("recTypeId"));//对应his模板ID
                medicalRecord.setRecDate(rs.getString("recDate") != null ? DateUtil.parseDateTime(rs.getString("recDate")) : null);//病历日期
                if(StringUtil.isNotBlank(rs.getString("zuixinbz"))){
                    if( rs.getString("zuixinbz").equals("1")){
                        medicalRecord.setIsDeleted("N");
                    }else {
                        medicalRecord.setIsDeleted("Y");
                    }
                }
                medicalRecord.setRecTitle(rs.getString("recTitle"));//病历标题

                medicalRecordList.add(medicalRecord);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }

        return medicalRecordList;
    }

    /**
     * 从视图中获取病历记录
     *
     * @return
     */
    public Integer getCount(String sql) {
        Integer count = 0;
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return count;
    }

    /**
     * 插入数据到视图
     *
     * @return
     */
    public void setRule(String sql) {
        try {
            connection = getConnection();
            sta = connection.prepareStatement(sql);
            sta.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * 修改视图数据
     *
     * @return
     */
    public void upRule(String sql) {
        try {
            connection = getConnection();
            sta = connection.prepareStatement(sql);
            sta.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    /**
     * 从视图中获取文书内容
     *
     * @return
     */
    public List<MedicalRecordContent> getMedicalRecordContent(String sql) {
        List<MedicalRecordContent> mrContentList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                MedicalRecordContent mrContent = new MedicalRecordContent();
                mrContent.setRecId(rs.getString("tableName") + "_" + rs.getString("RECID"));
                mrContent.setHospitalId(HOSPITAL_ID);//医院ID
                mrContent.setXmlText(rs.getString("XMLTEXT"));

                mrContentList.add(mrContent);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return mrContentList;
    }

    /**
     * 从视图中获取文书类型
     *
     * @return
     */
    public List<MedRecordType> getMedicalRecordType(String sql) {
        List<MedRecordType> mrTypeList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                MedRecordType mrType = new MedRecordType();
                mrType.setTypeId(rs.getString("BLLBID"));
                mrType.setHospitalId(HOSPITAL_ID);
                mrType.setTypeName(rs.getString("BLLBMC"));
                mrType.setParentTypeId(rs.getString("SJLBID"));
                mrType.setObjName(rs.getString("BLLBFW"));
                mrType.setSpell(rs.getString("HZSRM1"));
                mrTypeList.add(mrType);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }

        return mrTypeList;
    }

    /**
     * 从视图中获取化验
     *
     * @return
     */
    public List<MedLisResult> getLisResult(String sql) {
        List<MedLisResult> medLisResultList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                MedLisResult medLisResult = new MedLisResult();
                medLisResult.setRepNo("");
                medLisResult.setHospitalId(HOSPITAL_ID);
                medLisResult.setBehospitalCode("");
                medLisResult.setItemCode("");
                medLisResult.setItemName("");
                medLisResult.setResult("");
                medLisResult.setReferenceMin("");
                medLisResult.setReferenceMax("");
                medLisResult.setUnit("");
                medLisResult.setAbnormal("");
                medLisResult.setColor("");

                medLisResultList.add(medLisResult);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }

        return medLisResultList;
    }


    /**
     * 从视图中获取化验
     *
     * @return
     */
    public List<MedPacsResult> getPacsResult(String sql) {
        List<MedPacsResult> medPacsResultList = Lists.newLinkedList();
        try {
            TZDBConn dbconn = new TZDBConn();
            ResultSet rs = dbconn.Query(sql, null);
            while (rs.next()) {
                MedPacsResult medPacsResult = new MedPacsResult();
                medPacsResult.setRepNo("");
                medPacsResult.setHospitalId(HOSPITAL_ID);
                medPacsResult.setBehospitalCode("");
                medPacsResult.setReptName("");
                medPacsResult.setPart("");
                medPacsResult.setClinicSight("");
                medPacsResult.setReptDiag("");
                medPacsResult.setRemark("");
                medPacsResult.setClinicDiag("");
                medPacsResult.setApplyDoctor("");
                medPacsResult.setCheckerDoctor("");
                medPacsResult.setReportDoctor("");
                medPacsResult.setDeptName("");

                medPacsResultList.add(medPacsResult);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return medPacsResultList;
    }

}
