mrqc-sys/src/main/java/com/diagbot/util/TZDBConnForHis.java

package com.diagbot.util;

import com.diagbot.dto.SentEntryRecordDTO;
import com.diagbot.entity.*;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;

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

public class TZDBConnForHis {
	private static final String DRIVER = getValue("jdbc.driverClassName1");
    private static final String URL = getValue("jdbc.url1");
    private static final String USERNAME = getValue("jdbc.username1");
    private static final String PASSWORD = getValue("jdbc.password1");

	public static final Long HOSPITAL_ID=Long.valueOf("35");//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<BasDeptInfo> getDeptInfo(String sql) {
		List<BasDeptInfo> basDeptInfoList=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				BasDeptInfo basDeptInfo=new BasDeptInfo();
				basDeptInfo.setDeptId(rs.getString("deptId"));//科室编码
				basDeptInfo.setHospitalId(HOSPITAL_ID);//医院ID
				basDeptInfo.setDeptName(rs.getString("deptName"));//科室名称
				basDeptInfo.setParentDeptId(rs.getString("partentDeptId"));//父类科室名称
				basDeptInfo.setDeptType(rs.getString("deptType"));//科室类别
				basDeptInfo.setStation(rs.getString("station"));//区域类别
				basDeptInfo.setSpell(rs.getString("spell"));//首字母拼音

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

	/**
	 * 从视图中获取医院医生信息
	 * @return
	 */
	public List<BasDoctorInfo> getDoctorInfo(String sql) {
		List<BasDoctorInfo> basDoctorInfos=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				BasDoctorInfo basDoctorInfo =new BasDoctorInfo();
				basDoctorInfo.setDoctorId(rs.getString("user_id"));//医生id
				basDoctorInfo.setHospitalId(Long.valueOf("35"));
				basDoctorInfo.setDeptId(rs.getString("dept_code"));//医生所在科室
				basDoctorInfo.setName(rs.getString("name"));//医师姓名
				basDoctorInfo.setProfessor(rs.getString("title"));//医师职称
				basDoctorInfo.setOccup(rs.getString("certificate"));//是否有职业医师资格
				basDoctorInfos.add(basDoctorInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close();
		}
		return basDoctorInfos;
	}

	/**
	 * 查询文书数量
	 * @return
	 */
	public Integer getCount(String sql) {
		Integer count =0;
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				count=rs.getInt("count");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close();
		}
		return count;
	}

	/**
	 * 病案诊断
	 * @return
	 */
	public List<HomeDiagnoseInfo> getHomeDiagnose(String sql){
		List<HomeDiagnoseInfo> homeDiagnoseVOList=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			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("diagnoseName"));//诊断名称
				homeDiagnose.setDiagnoseType(rs.getString("diagnoseType"));//诊断类别
				homeDiagnose.setDiagnoseTypeShort(rs.getString("diagnoseTypeShort"));//诊断判别
				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 {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			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(StringUtils.isNotBlank(rs.getString("operationDate"))?DateUtil.parseDateTime(rs.getString("operationDate")):null);//手术日期
				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.setShamOperationName(rs.getString("shamOperationName"));//拟手术名称
				homeOperationVO.setAnaesthesiaDoctor(rs.getString("anaesthesiaDoctor"));//麻醉医师
				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 {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			ResultSet rs =dbconn.Query(sql, null);
//			if (!rs.next()){
//                System.out.println("返回查询结果为空");
//            }else {
                while(rs.next()){
                    HomePage homePageVO=new HomePage();
                    homePageVO.setHomePageId(rs.getString("homePageId"));//病案首页编号
                    homePageVO.setHospitalId(HOSPITAL_ID);//医院ID
                    homePageVO.setBehospitalCode(rs.getString("behospitalCode")+"_"+rs.getString("behospitalNum"));//病人住院序号
                    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("age"));//病人年龄
                    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(StringUtils.isNotBlank(rs.getString("behospitalDate"))?DateUtil.parseDateTime(rs.getString("behospitalDate")):null);//入院时间
                    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(StringUtils.isNotBlank(rs.getString("leaveHospitalDate"))?DateUtil.parseDateTime(rs.getString("leaveHospitalDate")):null);//出院时间
                    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();

                    homePageVO.setIsPhysicalRestraint(rs.getString("isPhysicalRestraint"));//住院期间身体约束
                    homePageVO.setTbiBehospitalBeforeTime(rs.getString("tbiBehospitalBeforeTime"));//颅脑损伤患者昏迷时间(入院前)
                    homePageVO.setIsPhysicalRestraint(rs.getString("isPhysicalRestraint"));//颅脑损伤患者昏迷时间(入院后)
                    homePageVO.setIsFallBed(rs.getString("isFallBed"));//住院期间是否发生跌倒或坠床
                    homePageVO.setIsNosocomialInfection(rs.getString("isNosocomialInfection"));//医院感染
                    homePageVO.setIsIntoIcu(rs.getString("isIntoIcu"));//入住ICU情况
                    homePageVO.setIsComplications(rs.getString("isComplications"));//并发症情况
                    homePageVO.setIsPressureSore(rs.getString("isPressureSore"));//是否发生压疮
                    homePageVO.setIsBehospitalPressureSore(rs.getString("isBehospitalPressureSore"));//是否住院期间发生压疮
                    homePageVO.setIsUnplannedReoperation(rs.getString("isUnplannedReoperation"));//非计划再次手术
                    homePageVO.setTreatmentResults(rs.getString("treatmentResults"));//治疗结果
                    homePageVO.setComplicationsResults(rs.getString("complicationsResults"));//并发症
                    homePageVO.setInfectionSite(rs.getString("infectionSite"));//感染部位
                    homePageVO.setIsBeInDanger(rs.getString("isBeInDanger"));//住院期间有无告病危
                    homePageList.add(homePageVO);
                }
//            }
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close();
		}
		return homePageList;
	}


	/**
	 * 从视图中获取医嘱信息
	 * @return
	 */
	public List<DoctorAdvice> getDoctorAdvice(String sql){
		List<DoctorAdvice> doctorAdviceList=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			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")+"_"+rs.getString("behospitalNum"));//病人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(StringUtils.isNotBlank(rs.getString("daStartDate"))?DateUtil.parseDateTime(rs.getString("daStartDate")):null);//医嘱开始时间
				doctorAdviceVO.setDaItemName(rs.getString("daItemName"));//医嘱项目名称
				doctorAdviceVO.setDaStatus(rs.getString("daStatus"));//医嘱状态判别
				doctorAdviceVO.setDaStopDate(StringUtils.isNotBlank(rs.getString("daStopDate"))?DateUtil.parseDateTime(rs.getString("daStopDate")):null);//医嘱结束时间
				doctorAdviceVO.setDaGroupNo(rs.getString("daGroupCode"));//医嘱同组序号
				doctorAdviceVO.setDaPrescriptionType(rs.getString("daPrescriptionType"));//医嘱处方类型
				doctorAdviceVO.setDaMedType(rs.getString("daMedType"));//医嘱领药类型
				doctorAdviceVO.setDoctorNotice(rs.getString("doctoryNotice"));//医生嘱托
//				doctorAdviceVO.setDoctorId(rs.getString("KDYSID"));//开单医生ID
				doctorAdviceVO.setDoctorName(rs.getString("doctorName"));//开单医生姓名

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



	/**
	 * 从视图中获取化验信息
	 * @return
	 */
	public List<MedLisInfo> getLisInfo(String sql){
		List<MedLisInfo> medLisInfos=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				MedLisInfo medLisInfo=new MedLisInfo();
				medLisInfo.setRepNo(rs.getString("repNo"));//报告单号
				medLisInfo.setHospitalId(HOSPITAL_ID+"");//医院ID
				medLisInfo.setBehospitalCode(rs.getString("behospitalCode")+"_"+rs.getString("behospitalNum"));//住院病人ID
				medLisInfo.setRepName(rs.getString("repName"));//报告名称
				medLisInfo.setRepType(rs.getString("repType"));//报告类型代码
				medLisInfo.setRepTypeName(rs.getString("repTypeName"));//检查类型名称
				medLisInfo.setSlideType(rs.getString("slideType"));//标本名称
				medLisInfo.setRepDate(rs.getString("repDate"));//发布时间
				medLisInfo.setCheckDate(rs.getString("checkDate"));//报告检查时间
				medLisInfo.setApplyDoctor(rs.getString("applyDoctor"));//申请医生
				medLisInfo.setDeptName(rs.getString("deptName"));//开单科室名称
				medLisInfo.setDeptId(rs.getString("deptId"));//开单科室代码
				medLisInfo.setRegisterFlag(rs.getString("registerFlag"));//报告单状态
				medLisInfos.add(medLisInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close();
		}
		return medLisInfos;
	}



	/**
	 * 从视图中获取化验结果
	 * @return
	 */
	public List<MedLisResult> getLisResult(String sql){
		List<MedLisResult> medLisResultList=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				MedLisResult medLisResult=new MedLisResult();
				medLisResult.setRepNo(rs.getString("repNo"));//报告单号
				medLisResult.setHospitalId(HOSPITAL_ID);//医院ID
				medLisResult.setBehospitalCode(rs.getString("behospitalCode")+"_"+rs.getString("behospitalNum"));//住院病人ID
				medLisResult.setItemCode(rs.getString("itemCode"));//报告代码
				medLisResult.setItemName(rs.getString("itemName"));//报告名称
				medLisResult.setResult(rs.getString("result"));//检验结果
				medLisResult.setReferenceMin(rs.getString("referenceMin"));//最小参考值
				medLisResult.setReferenceMax(rs.getString("referenceMax"));//最大参考值
				medLisResult.setUnit(rs.getString("unit"));//单位
				medLisResult.setAbnormal(rs.getString("abnormal"));//异常标志
				medLisResult.setColor(rs.getString("color"));//异常颜色

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

		return medLisResultList;
	}

	/**
	 * 获取检查信息
	 * @param sql
	 * @return
	 */
	public List<MedPacsInfo> getPacsInfo(String sql){
		List<MedPacsInfo> medPacsInfos=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				MedPacsInfo medPacsInfo=new MedPacsInfo();
				medPacsInfo.setRepNo(rs.getString("repNo"));//报告单号
				medPacsInfo.setHospitalId(HOSPITAL_ID+"");//医院ID
				medPacsInfo.setBehospitalCode(rs.getString("behospitalCode")+"_"+rs.getString("behospitalNum"));//住院病人ID
				medPacsInfo.setRepName(rs.getString("repName"));//报告名称
				medPacsInfo.setRepType(rs.getString("repType"));//报告类型代码
				medPacsInfo.setRepTypeName(rs.getString("repTypeName"));//检查类型名称
				medPacsInfo.setPart(rs.getString("part"));//检查部位
				medPacsInfo.setRepDate(rs.getString("repDate"));//发布时间
				medPacsInfo.setCheckDate(rs.getString("checkDate"));//报告检查时间
				medPacsInfo.setApplyDoctor(rs.getString("applyDoctor"));//申请医生
				medPacsInfo.setChecker(rs.getString("checker"));//报告审核医生
				medPacsInfo.setReportDoctor(rs.getString("reportDoctor"));//报告医生
				medPacsInfo.setDeptName(rs.getString("deptName"));//开单科室名称
				medPacsInfo.setDeptId(rs.getString("deptId"));//开单科室代码
				medPacsInfos.add(medPacsInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close();
		}
		return medPacsInfos;
	}

	/**
	 * 从视图中获取检查结果
	 * @return
	 */
	public List<MedPacsResult> getPacsResult(String sql){
		List<MedPacsResult> medPacsResultList=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				MedPacsResult medPacsResult=new MedPacsResult();
				medPacsResult.setRepNo(rs.getString("repNo"));//报告单号
				medPacsResult.setHospitalId(HOSPITAL_ID);
				medPacsResult.setBehospitalCode(rs.getString("behospitalCode")+"_"+rs.getString("behospitalNum"));//住院病人ID
				medPacsResult.setReptName(rs.getString("repName"));//报告名称
				medPacsResult.setPart(rs.getString("part"));//检查部位
				medPacsResult.setClinicSight(rs.getString("clinicSight"));//检查所见
				medPacsResult.setReptDiag(rs.getString("reptDiag"));//检查结果（诊断）
				medPacsResult.setClinicDiag(rs.getString("clinicDiag"));//临床诊断
				medPacsResult.setApplyDoctor(rs.getString("applyDoctor"));//申请医生
				medPacsResult.setCheckerDoctor(rs.getString("checker"));//审核医生
				medPacsResult.setReportDoctor(rs.getString("reportDoctor"));//报告医生
				medPacsResult.setDeptName(rs.getString("deptName"));//科室名称

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

	/**
	 * 从视图中获取入院登记
	 * @return
	 */
	public List<BehospitalInfo> getBehospitalInfo(String sql){
		List<BehospitalInfo> behospitalInfoList=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				BehospitalInfo behospitalInfo=new BehospitalInfo();
				behospitalInfo.setBehospitalCode(rs.getString("behospitalCode")+"_"+rs.getString("behospitalNum"));//病人住院ID
				behospitalInfo.setHospitalId(HOSPITAL_ID);
				behospitalInfo.setName(rs.getString("Name"));//姓名
				behospitalInfo.setSex(rs.getString("Sex"));//性别
				behospitalInfo.setBirthday(StringUtils.isNotBlank(rs.getString("Birthday"))?DateUtil.parseDate(rs.getString("Birthday")):null);//出生日期
				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("charge_type"));//医保类别
				behospitalInfo.setJobType(rs.getString("jobType"));//职业
				behospitalInfo.setBehospitalDate(StringUtils.isNotBlank(rs.getString("behospitalDate"))?DateUtil.parseDateTime(rs.getString("behospitalDate")):null);//入院时间
				behospitalInfo.setLeaveHospitalDate(StringUtils.isNotBlank(rs.getString("leaveHospitalDate"))?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"));//主治医生姓名
				behospitalInfo.setIsPlacefile(rs.getString("isPlacefile"));//是否归档
				behospitalInfo.setPlacefileDate(StringUtils.isNotBlank(rs.getString("placefileDate"))?DateUtil.parseDateTime(rs.getString("placefileDate")):null);//归档时间
//				behospitalInfo.setInstate(rs.getString("Instate"));//病人状态
				behospitalInfoList.add(behospitalInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close();
		}
		return behospitalInfoList;
	}

	/**
	 * 从视图中获取文书信息
	 * @return
	 */
	public List<MedicalRecord> getMedicalRecord(String sql){
		List<MedicalRecord> medicalRecordList=Lists.newLinkedList();
		try {
			TZDBConnForHis dbconn=new TZDBConnForHis();
			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")+"_"+rs.getString("behospitalNum"));//病人住院ID
                medicalRecord.setOrgCode(rs.getString("orgCode"));//组织机构代码
                medicalRecord.setRecTypeId(rs.getString("recTypeId"));//病历类别编号
                medicalRecord.setRecDate(StringUtils.isNotBlank(rs.getString("recDate"))?DateUtil.parseDateTime(rs.getString("recDate")):null);//病历生成日期
                medicalRecord.setRecTitle(rs.getString("recTitle"));//病历模板名称
                medicalRecordList.add(medicalRecord);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close();
		}
		return medicalRecordList;
	}

	/**
	 * 关闭数据库
	 * @param conn
	 */
	public void closeConnection(Connection conn){
		try{
			if(conn != null){
				conn.close();
			}
		}
		catch(Exception e){
			System.out.println("数据库关闭失败");
			e.printStackTrace();
		}
	}

	/**
	 * 新赠评分插入表中
	 * @return
	 */
	public Integer setQcMessAge(SentEntryRecordDTO sentEntryRecordDTO) throws SQLException {
		try {
			String patientId=sentEntryRecordDTO.getBehospitalCode().split("_")[0];
			String visitId=sentEntryRecordDTO.getBehospitalCode().split("_")[1];
			int re=0;
			connection = getConnection();
			connection.setAutoCommit(false);//事物开始
			PreparedStatement state=connection.prepareStatement("insert into MEDICAL_QC_MSG " +
					"(PATIENT_ID,VISIT_ID,DOCTOR_IN_CHARGE,QA_EVENT_TYPE,QC_MSG_CODE,MESSAGE,ISSUED_BY,ISSUED_DATE_TIME,TOPIC_ID,DEPT_STAYED,MSG_STATUS)"+
					"values(?,?,?,?,?,?,?,to_date(?,'yyyy-mm-dd hh24:mi:ss'),?,?,?)");
			state.setString(1, patientId);
			state.setString(2, visitId);
			state.setString(3,StringUtils.isNotEmpty(sentEntryRecordDTO.getDoctorName())?sentEntryRecordDTO.getDoctorName():sentEntryRecordDTO.getCheckName());
			state.setString(4,StringUtils.isNotEmpty(sentEntryRecordDTO.getModeName())?sentEntryRecordDTO.getModeName():"其他");
			state.setInt(5,1511);
			state.setString(6,sentEntryRecordDTO.getMsg());
			state.setString(7,"评价质控");
			state.setString(8, DateUtil.formatDateTime(DateUtil.now()));
			state.setString(9,StringUtils.isNotEmpty(sentEntryRecordDTO.getModeName())?sentEntryRecordDTO.getModeName():"其他");
			state.setString(10,sentEntryRecordDTO.getDeptId());
			state.setInt(11,0);
			re = state.executeUpdate();
			if(re < 0){               //插入失败
				connection.rollback();      //回滚
				state.close();
				closeConnection(connection);
				return re;
			}
			connection.commit();            //插入正常
			state.close();
			closeConnection(connection);
			return re;

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally {
			close();
		}

	}

	/**
	 * 删除评分表中的对应数据
	 * @return
	 */
	public Integer DeleteQcMessAge(SentEntryRecordDTO sentEntryRecordDTO) throws SQLException {
		try {
			String patientId=sentEntryRecordDTO.getBehospitalCode().split("_")[0];
			String visitId=sentEntryRecordDTO.getBehospitalCode().split("_")[1];
			int re=0;
			connection = getConnection();
			connection.setAutoCommit(false);//事物开始
			String sql ="delete MEDICAL_QC_MSG  where  PATIENT_ID = '"+patientId+"' and VISIT_ID = '"
					+visitId+"' and MESSAGE = '"+sentEntryRecordDTO.getMsg()+"'";
			PreparedStatement state=connection.prepareStatement(sql);
			re = state.executeUpdate();
			if(re < 0){               //删除失败
				connection.rollback();      //回滚
				state.close();
				closeConnection(connection);
				return re;
			}
			connection.commit();            //删除正常
			state.close();
			closeConnection(connection);
			return re;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally {
			close();
		}

	}

	/**
	 * 修改评分表中对应数据
	 * @return
	 */
	public Integer UpdateQcMessAge(SentEntryRecordDTO sentEntryRecordDTO,String OldMsg) throws SQLException {
		try {
			String patientId=sentEntryRecordDTO.getBehospitalCode().split("_")[0];
			String visitId=sentEntryRecordDTO.getBehospitalCode().split("_")[1];
			int re=0;
			connection = getConnection();
			connection.setAutoCommit(false);//事物开始
			String sql =" update MEDICAL_QC_MSG set MESSAGE = '"+sentEntryRecordDTO.getMsg()+"' where PATIENT_ID = '"+patientId+"' and VISIT_ID = '"
					+visitId+"' and MESSAGE = '"+OldMsg+"'" ;
			PreparedStatement state=connection.prepareStatement(sql);
			re = state.executeUpdate();
			if(re < 0){               //修改失败
				connection.rollback();      //回滚
				state.close();
				closeConnection(connection);
				return re;
			}
			connection.commit();            //修改正常
			state.close();
			closeConnection(connection);
			return re;

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally {
			close();
		}

	}

}