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

package com.diagbot.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.ResourceBundle;

import javax.print.DocFlavor.STRING;

import com.diagbot.entity.BasDeptInfo;
import com.diagbot.entity.BasDoctorInfo;
import com.diagbot.entity.BehospitalInfo;
import com.diagbot.entity.DoctorAdvice;
import com.diagbot.entity.HomeDiagnoseInfo;
import com.diagbot.entity.HomeOperationInfo;
import com.diagbot.entity.HomePage;
import com.diagbot.entity.MedRecordType;
import com.diagbot.entity.MedicalRecord;
import com.diagbot.entity.MedicalRecordContent;
import com.diagbot.vo.FBasDeptInfoVO;
import com.diagbot.vo.FBasDoctorInfoVO;
import com.diagbot.vo.FBehospitalInfoVO;
import com.diagbot.vo.FDoctorAdviceVO;
import com.diagbot.vo.FHomeDiagnoseVO;
import com.diagbot.vo.FHomeOperationVO;
import com.diagbot.vo.FHomePageVO;
import com.diagbot.vo.FMedicalRecordContentVO;
import com.diagbot.vo.FMedicalRecordTypeVO;
import com.diagbot.vo.FMedicalRecordVO;
import com.google.common.collect.Lists;

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");
    
    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() {
		List<BasDoctorInfo> basDoctorInfoList=Lists.newLinkedList();
		TZDBConn dbconn=new TZDBConn();
		try {
			String sql="select * from gi_userinfo where CJCXRQ BETWEEN TO_DATE('2020-03-01', 'yyyy-MM-dd') and TO_DATE('2020-03-30', 'yyyy-MM-dd')";
			rs =dbconn.Query(sql, null);
			while(rs.next()){
				FBasDoctorInfoVO basDoctorInfoVO=new FBasDoctorInfoVO();
				basDoctorInfoVO.setDoctorId(rs.getString("YHRYBH"));//医生ID
				basDoctorInfoVO.setHospitalId(Long.valueOf("3"));//医院ID
				//basDoctorInfoVO.setDeptId(rs.getString("ZZKSID"));//科室ID
				basDoctorInfoVO.setName(rs.getString("YHRYMC"));//医生姓名
				basDoctorInfoVO.setProfessor(rs.getString("YHRYZC"));//职称
				
				BasDoctorInfo basDoctorInfo=new BasDoctorInfo();
				BeanUtil.copyProperties(basDoctorInfoVO, basDoctorInfo);
				
				basDoctorInfoList.add(basDoctorInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return basDoctorInfoList;
	}
	
	/**
	 * 从视图中获取医院科室数据，根据修改时间同步数据
	 * @return
	 */
	public List<BasDeptInfo> getDeptInfo() {
		List<BasDeptInfo> basDeptInfoList=Lists.newLinkedList();
		try {
			TZDBConn dbconn=new TZDBConn();
			String sql="select * from hi_deptinfo";
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				FBasDeptInfoVO basDeptInfoVO=new FBasDeptInfoVO();
				basDeptInfoVO.setDeptId(rs.getString("ZZKSDM"));//科室编码
				basDeptInfoVO.setHospitalId(Long.valueOf("3"));//医院ID
				basDeptInfoVO.setDeptName(rs.getString("ZZKSMC"));//科室名称
				//basDeptInfoVO.setDeptType(rs.getString("ZZKSLB"));//科室类别
				basDeptInfoVO.setSpell(rs.getString("HZSRM1"));//首字母拼音
				//basDeptInfoVO.setStation(rs.getString("FLKSID"));//区域类别
				
				BasDeptInfo basDeptInfo=new BasDeptInfo();
				BeanUtil.copyProperties(basDeptInfoVO, basDeptInfo);
				
				basDeptInfoList.add(basDeptInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return basDeptInfoList;
	}
	
	/**
	 * 病案诊断
	 * @return
	 */
	public List<HomeDiagnoseInfo> getHomeDiagnose(){
		List<HomeDiagnoseInfo> homeDiagnoseVOList=Lists.newLinkedList();
		try {
			DBConn dbconn=new DBConn();
			//String sql="select * from br_recdiagnose where CJCXRQ BETWEEN TO_DATE('2020-02-01', 'yyyy-MM-dd') and TO_DATE('2020-04-30', 'yyyy-MM-dd')";
			String sql="select * from br_recdiagnose where BASYID IN (select BASYID from br_rechome WHERE BRZYID IN (select BRZYID from br_inpatientinfo where ZYKSID IN('1020500','1009100','1030200')))";
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				FHomeDiagnoseVO homeDiagnoseVO=new FHomeDiagnoseVO();
				homeDiagnoseVO.setHomePageId(rs.getString("BASYID"));//病案首页ID
				homeDiagnoseVO.setHospitalId(Long.valueOf("3"));//医院ID
				homeDiagnoseVO.setDiagnoseOrderNo(rs.getString("BAZDXH"));//诊断序号
				homeDiagnoseVO.setDiagnoseName(rs.getString("ZDJBMC"));//诊断名称
				homeDiagnoseVO.setDiagnoseType(rs.getString("ZDLBDM"));//诊断类别
				homeDiagnoseVO.setDiagnoseTypeShort(rs.getString("ZCZDPB"));//诊断判别
				homeDiagnoseVO.setBehospitalType(rs.getString("RYQKBM"));//入院情况
				//homeDiagnoseVO.setLeaveHospitalType(rs.getString("RYQKBM"));//出院情况
				//homeDiagnoseVO.setPathologyDiagnose(rs.getString("BASYID"));//病理号
				homeDiagnoseVO.setIcdCode(rs.getString("ICDM"));//诊断编码
				
				homeDiagnoseVO.setGmtCreate(new Date());
				
				HomeDiagnoseInfo homeDiagnoseInfo=new HomeDiagnoseInfo();
				BeanUtil.copyProperties(homeDiagnoseVO, homeDiagnoseInfo);
				homeDiagnoseVOList.add(homeDiagnoseInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return homeDiagnoseVOList;
	}
	
	/**
	 * 病案手术
	 * @return
	 */
	public List<HomeOperationInfo> getHomeOperation() {
		List<FHomeOperationVO> homeOperationVOList=Lists.newLinkedList();
		try {
			DBConn dbconn=new DBConn();
			//String sql="select * from br_recoperation where CJCXRQ BETWEEN TO_DATE('2020-02-01', 'yyyy-MM-dd') and TO_DATE('2020-04-30', 'yyyy-MM-dd')";
			String sql="select * from br_recoperation where BASYID IN (select BASYID from br_rechome WHERE BRZYID IN (select BRZYID from br_inpatientinfo where ZYKSID IN('1020500','1009100','1030200')))";
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				FHomeOperationVO homeOperationVO=new FHomeOperationVO();
				homeOperationVO.setHomePageId(rs.getString("BASYID"));//病案首页ID
				homeOperationVO.setHospitalId(Long.valueOf("3"));//医院ID
				homeOperationVO.setOperationOrderNo(rs.getString("BRSSXH"));//手术序号
				homeOperationVO.setOperationDate(rs.getDate("BRSSRQ"));//手术日期
				homeOperationVO.setOperationCode(rs.getString("SSDMID"));//手术编码
				homeOperationVO.setOperationDoctorId(rs.getString("SSYSID"));//手术医生
				homeOperationVO.setFirstAssistantId(rs.getString("YZHSID"));//一助医生
				homeOperationVO.setSecondAssistantId(rs.getString("EZHSID"));//二助医生
				homeOperationVO.setCutLevel(rs.getString("QKDJDM"));//切口等级
				homeOperationVO.setHealingLevel(rs.getString("QKDJDM"));//愈合等级
				homeOperationVO.setOperationName(rs.getString("BRSSMC"));//手术名称
				homeOperationVO.setOperationLevel(rs.getString("SSJBID"));//手术级别
				homeOperationVO.setAnaesthesiaName(rs.getString("MZFFMC"));//麻醉方式
				homeOperationVO.setShamOperationName(rs.getString("NSSMC"));//拟手术名称
				homeOperationVO.setGmtCreate(new Date());
				
				homeOperationVOList.add(homeOperationVO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		List<HomeOperationInfo> homeOperationList=BeanUtil.listCopyTo(homeOperationVOList, HomeOperationInfo.class);
		return homeOperationList;
	}
	
	/**
	 * 从视图获取病案首页信息
	 */
	public List<HomePage> getHomePage() {
		List<HomePage> homePageList=Lists.newLinkedList();
		try {
			DBConn dbconn=new DBConn();
			//String sql="select * from br_rechome WHERE DateDiff(dd,CJCXRQ,getdate())>120 AND DateDiff(dd,CJCXRQ,getdate())<=200 AND RYKSID IN('1040200','1050000') and BRZYID IN (select top 2000 BRZYID from br_inpatientinfo where ZYKSID IN('1040200','1050000') order by CJCXRQ desc) ORDER BY CJCXRQ DESC";
			//String sql="select top 500 * from br_rechome WHERE RYKSID in ('1020500','1009100','1030200') and BRZYID is not NULL ORDER BY CJCXRQ DESC";
			//String sql="select * from br_rechome WHERE RYKSID in ('1020500','1009100','1030200') and BRZYID is not NULL AND DATEDIFF([month],CJCXRQ,getdate())=6 ORDER BY CJCXRQ desc ";
			String sql="select * from br_rechome WHERE RYKSID in ('1020500','1009100','1030200') and BRZYID is not NULL AND DATEDIFF([month],CJCXRQ,getdate())=0 ORDER BY CJCXRQ desc ";
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				FHomePageVO homePageVO=new FHomePageVO();
				homePageVO.setHomePageId(rs.getString("BASYID"));//病案首页ID
				homePageVO.setHospitalId(Long.valueOf("3"));//医院ID
				homePageVO.setBehospitalCode(rs.getString("BRZYID"));//病人住院序号
				homePageVO.setHospitalCode(rs.getString("ZZJGID"));//组织机构id
				homePageVO.setHospitalName(rs.getString("ZZJGMC"));//医疗机构名称
				homePageVO.setOrgCode(rs.getString("ZZJGBH"));//医疗机构代码
				homePageVO.setPayType(rs.getString("YLFKLB"));//医疗付费方式
				homePageVO.setHealthCard(rs.getString("BRJKKH"));//健康卡号
				homePageVO.setBehospitalNum(rs.getString("BRZYCS"));//住院次数
				homePageVO.setFileCode(rs.getString("BRBABH"));//病案号
				homePageVO.setName(rs.getString("BRBAXM"));//姓名
				homePageVO.setSex(rs.getString("BRBAXB"));//性别
				homePageVO.setBirthday(rs.getDate("BRCSRQ"));//出生日期
				homePageVO.setAge(rs.getString("BRDQNL"));//病人年龄
				homePageVO.setAgeUnit(rs.getString("BRNLDW"));//年龄单位
				homePageVO.setNationality(rs.getString("BRBAGJ"));//国籍
				homePageVO.setNewbornMonth(rs.getString("YENLYS"));//新生儿出生月数
				homePageVO.setNewbornDay(rs.getString("YENLTS"));//新生儿出生天数
				homePageVO.setNewbornWeight(rs.getString("YECSTZ"));//新生儿出生体重
				homePageVO.setNewbornBehospitalWeight(rs.getString("YERYTZ"));//新生儿入院体重
				homePageVO.setBornAddress(rs.getString("BRCSDZ"));//出生地
				homePageVO.setBornPlace(rs.getString("BRBAJG"));//籍贯
				homePageVO.setNation(rs.getString("BRBAMZ"));//民族
				homePageVO.setIdentityCardNo(rs.getString("BRSFZH"));//身份证号
				homePageVO.setJobType(rs.getString("BRBASF"));//职业
				homePageVO.setMarriage(rs.getString("BRHYZK"));//婚姻
				homePageVO.setCurAddress(rs.getString("BRLXDZ"));//现住址
				homePageVO.setCurPhone(rs.getString("BRLXDH"));//现住址电话
				homePageVO.setCurPostCode(rs.getString("LXDZYB"));//现住址邮编
				homePageVO.setResidenceAddress(rs.getString("BRHKDZ"));//户口地址
				homePageVO.setResidencePostCode(rs.getString("HKDZYB"));//户口地址邮编
				homePageVO.setWorkAddress(rs.getString("GZDWMC"));//工作单位
				homePageVO.setWorkPhone(rs.getString("GZDWDH"));//工作单位电话
				homePageVO.setWorkPostCode(rs.getString("GZDWYB"));//工作单位邮编
				homePageVO.setContactName(rs.getString("LXRYXM"));//联系人姓名
				homePageVO.setContactRelation(rs.getString("LXRYGX"));//联系人关系
				homePageVO.setContactAddress(rs.getString("LXRYDZ"));//联系人地址
				homePageVO.setContactPhone(rs.getString("LXRYDH"));//联系人电话
				homePageVO.setBehospitalWay(rs.getString("RYLYDM"));//入院途径
				homePageVO.setBehospitalDate(rs.getDate("BRRYRQ"));//入院时间
				homePageVO.setBehospitalDept(rs.getString("RYKSID"));//入院科室
				homePageVO.setBehospitalWard(rs.getString("RYBQID"));//入院病房
				homePageVO.setBehospitalBedId(rs.getString("RYCWID"));//入院床位序号
				homePageVO.setBehospitalBedCode(rs.getString("RYCWHM"));//入院床位号码
				homePageVO.setChangeDept(rs.getString("BRZKKB"));//转科科别
				homePageVO.setLeaveHospitalDate(rs.getDate("BRCYRQ"));//出院时间
				homePageVO.setLeaveHospitalDept(rs.getString("CYKSID"));//出院科别
				homePageVO.setLeaveHospitalWard(rs.getString("CYBQID"));//出院病房
				homePageVO.setLeaveHospitalBedId(rs.getString("CYCWID"));//出院床位序号
				homePageVO.setLeaveHospitalBedCode(rs.getString("CYCWHM"));//出院床位号码
				homePageVO.setBehospitalDayNum(rs.getString("SJZYTS"));//实际住院天数
				homePageVO.setOutpatientEmrDiagnose(rs.getString("BRMZZD"));//门急诊诊断
				homePageVO.setOutpatientEmrDiagnoseCode(rs.getString("MZZDDM"));//门急诊诊断编码
				homePageVO.setPoisonFactor(rs.getString("SSZDYSMC"));//损伤中毒因素
				homePageVO.setPoisonFactorCode(rs.getString("SSZDYSBM"));//损伤中毒因素编码
				homePageVO.setPathologyDiagnose(rs.getString("BLZDMC"));//病理诊断
				homePageVO.setPathologyDiagnoseCode(rs.getString("BLZDBM"));//病理诊断编码
				homePageVO.setPathologyDiagnoseId(rs.getString("BLZDBH"));//病理诊断编号
				homePageVO.setIsMedAllergy(rs.getString("YWYWGM"));//药物过敏
				homePageVO.setMedAllergyName(rs.getString("BRGMYW"));//过敏药物
				homePageVO.setAutopsy(rs.getString("BRSFSJ"));//死亡患者尸检
				homePageVO.setBloodType(rs.getString("BRBAXX"));//血型
				homePageVO.setRh(rs.getString("BRBARH"));//Rh
				homePageVO.setDeptDirector(rs.getString("KZR"));//科主任
				homePageVO.setDirectorDoctor(rs.getString("ZRYS"));//主任医师
				homePageVO.setAttendingDoctor(rs.getString("ZZYS"));//主治医师
				homePageVO.setBehospitalDoctor(rs.getString("ZYYS"));//住院医师
				homePageVO.setResponseNurse(rs.getString("ZRHS"));//责任护士
				homePageVO.setStudyDoctor(rs.getString("JXYS"));//进修医师
				homePageVO.setPracticeDoctor(rs.getString("SXYS"));//实习医师
				homePageVO.setEncodeMan(rs.getString("BMY"));//编码员
				homePageVO.setHomePageQuality(rs.getString("BRBAZL"));//病案质量
				homePageVO.setQcDoctor(rs.getString("BAZKYS"));//质控医师
				homePageVO.setQcNurse(rs.getString("BAZKHS"));//质控护士
				if(!"".equals(rs.getString("BAZKRQ"))){
					homePageVO.setQcDate(DateUtil.parseDate(rs.getString("BAZKRQ"),DateUtil.DATE_TIME_FORMAT));//质控日期
				}
				homePageVO.setLeaveHospitalType(rs.getString("BRLYFS"));//离院方式
				homePageVO.setAcceptOrgCode(rs.getString("ZYJGMC"));//接收机构名称
				homePageVO.setAgainBehospitalPlan(rs.getString("SSYZZY"));//31天内再住院计划
				homePageVO.setAgainBehospitalGoal(rs.getString("SSYZZYMD"));//再住院目的
				homePageVO.setTbiBeforeDay(rs.getString("RYQHMTS"));//颅脑损伤患者昏迷前天数
				homePageVO.setTbiBeforeHour(rs.getString("RYQHMXS"));//颅脑损伤患者昏迷前小时
				homePageVO.setTbiBeforeMinute(rs.getString("RYQHMFZ"));//颅脑损伤患者昏迷前分钟
				homePageVO.setTbiAfterDay(rs.getString("RYQHMFZ"));//颅脑损伤患者昏迷后天数
				homePageVO.setTbiAfterHour(rs.getString("RYHHMXS"));//颅脑损伤患者昏迷后小时
				homePageVO.setTbiAfterMinute(rs.getString("RYQHMFZ"));//颅脑损伤患者昏迷后分钟
				homePageVO.setTotalFee(rs.getString("ZFY"));//总费用
				homePageVO.setOwnFee(rs.getString("ZFJE"));//自付金额
				homePageVO.setGeneralFee(rs.getString("YBYLFWF"));//一般医疗服务费
				homePageVO.setServiceFee(rs.getString("YBZLCZF"));//一般治疗服务费
				homePageVO.setNurseFee(rs.getString("HLF"));//护理费
				//homePageVO.setOtherFee(rs.getString("QTFY"));//其他费用
				homePageVO.setPathologyFee(rs.getString("BLZDF"));//病理诊断费
				homePageVO.setLabFee(rs.getString("SYSZDF"));//实验室诊断费
				homePageVO.setPacsFee(rs.getString("YXXZDF"));//影像学诊断费
				homePageVO.setClinicDiagnoseFee(rs.getString("LCZDXMF"));//临床诊断项目费
				homePageVO.setNotOperationFee(rs.getString("FSSZLXMF"));//非手术治疗项目费
				homePageVO.setClinicPhysicFee(rs.getString("LCWLZLF"));//临床物理治疗费
				homePageVO.setOperationTreatFee(rs.getString("SSZLF"));//手术治疗费
				homePageVO.setAnaesthesiaFee(rs.getString("MZF"));//麻醉费
				homePageVO.setOperationFee(rs.getString("SSF"));//手术费
				homePageVO.setHealthTypeFee(rs.getString("KFF"));//康复类
				homePageVO.setChnTreatFee(rs.getString("ZYZLF"));//中医治疗费
				homePageVO.setWesternMedFee(rs.getString("XYF"));//西药费
				homePageVO.setAntibiosisFee(rs.getString("KJYWF"));//抗菌药物费用
				homePageVO.setChnMedFee(rs.getString("ZCYF"));//中成药费
				homePageVO.setChnHerbFee(rs.getString("CYF"));//中草药费
				homePageVO.setBloodFee(rs.getString("XF"));//血费
				homePageVO.setAlbumenFee(rs.getString("BDBLZPF"));//白蛋白类制品费
				homePageVO.setGlobulinFee(rs.getString("QDBLZPF"));//球蛋白类制品费
				homePageVO.setBloodFactorFee(rs.getString("NXYZLZPF"));//凝血因子类制品费
				homePageVO.setCellFactorFee(rs.getString("XBYZLZPF"));//细胞因子类制品费
				homePageVO.setCheckMaterialFee(rs.getString("JCYYCXYYCLF"));//检查用一次性医用材料费
				homePageVO.setTreatMaterialFee(rs.getString("ZLYYCXYYCLF"));//治疗用一次性医用材料费
				homePageVO.setOperationMaterialFee(rs.getString("SSYYCXYYCLF"));//手术用一次性医用材料费
				homePageVO.setOtherTypeFee(rs.getString("QTF"));//其他类其他费
				homePageVO.setSingleDiagManage(rs.getString("DBZGL"));//单病种管理
				homePageVO.setClinicPathwayManage(rs.getString("SSLCLJGL"));//临床路径管理
				homePageVO.setIsOutpatientBehospital(rs.getString("MZZYFH"));//门诊与住院
				homePageVO.setIsLeaveBehospital(rs.getString("RYCYFH"));//入院与出院
				homePageVO.setIsOperationBeforeAfter(rs.getString("SQSHFH"));//术前与术后
				homePageVO.setIsClinicPathology(rs.getString("LCBLFH"));//临床与病理
				homePageVO.setIsRadiatePathology(rs.getString("FSBLFH"));//放射与病理
				homePageVO.setRescueSuccessNum(rs.getString("BRQJCS"));//病人抢救次数
				homePageVO.setRescueSuccessNum(rs.getString("QJCGCS"));//病人抢救成功次数
				homePageVO.setIsAutoLeavehospital(rs.getString("ZDCYPB"));//是否为自动出院
				homePageVO.setReturnToType(rs.getString("CYQKDM"));//转归情况
				
				homePageVO.setGmtCreate(new Date());
				
				HomePage homePage=new HomePage();
				BeanUtil.copyProperties(homePageVO, homePage);
				homePageList.add(homePage);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return homePageList;
	}
	
	/**
	 * 获取病历信息
	 * @return
	 */
	public List<BehospitalInfo> getBehospitalInfo() {
		List<BehospitalInfo> behospitalInfoList=Lists.newLinkedList();
		try {
			DBConn dbconn=new DBConn();
			//String sql="select * from br_inpatientinfo where CJCXRQ BETWEEN TO_DATE('2020-01-01', 'yyyy-MM-dd') and TO_DATE('2020-01-20', 'yyyy-MM-dd')";
			//String sql="select top 2000 * from br_inpatientinfo where ZYKSID IN('1040200','1050000') order by CJCXRQ desc";
			String sql="select * from br_inpatientinfo where ZYKSID IN('1020500','1009100','1030200')";
			//String sql="select * from br_inpatientinfo where DateDiff(dd,CJCXRQ,getdate())=1";
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				BehospitalInfo behospitalInfo=new BehospitalInfo();
				behospitalInfo.setBehospitalCode(rs.getString("BRZYID"));//病人住院ID
				behospitalInfo.setHospitalId(Long.valueOf("3"));//医院ID
				behospitalInfo.setName(rs.getString("BRDAXM"));//姓名
				behospitalInfo.setSex(rs.getString("BRDAXB").equals("M")?"男":"女");//性别
				behospitalInfo.setBirthday(rs.getDate("BRCSRQ"));//出生日期
				behospitalInfo.setFileCode(rs.getString("BRDABH"));//档案号
				behospitalInfo.setWardCode(rs.getString("ZYBQID"));//病区编码
				behospitalInfo.setWardName(rs.getString("ZYBQMC"));//病区名称
				behospitalInfo.setBehDeptId(rs.getString("ZYKSID"));//住院科室ID
				behospitalInfo.setBehDeptName(rs.getString("ZYKSMC"));//住院科室名称
				behospitalInfo.setBedCode(rs.getString("ZYCWID"));//床位号
				behospitalInfo.setBedName(rs.getString("ZYCWHM"));//床位名称
				behospitalInfo.setInsuranceName(rs.getString("BRLBID"));//医保类别
				behospitalInfo.setJobType(rs.getString("BRXZID"));//职业
				behospitalInfo.setBehospitalDate(rs.getDate("BRRYRQ"));//入院时间
				behospitalInfo.setLeaveHospitalDate(rs.getDate("BRCYRQ"));//出院时间
				behospitalInfo.setDiagnoseIcd(rs.getString("JBDMID"));//疾病ICD编码
				behospitalInfo.setDiagnose(rs.getString("JBMSXX"));//疾病名称
				behospitalInfo.setDoctorId(rs.getString("ZZYSID"));//医生ID
				behospitalInfo.setDoctorName(rs.getString("ZZYSXM"));//医生姓名
				
				behospitalInfo.setGmtCreate(new Date());
				
				behospitalInfoList.add(behospitalInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return behospitalInfoList;
	}
	
	/**
	 * 从视图中获取医嘱信息
	 * @return
	 */
	public List<DoctorAdvice> getDoctorAdvice(){
		List<DoctorAdvice> doctorAdviceList=Lists.newLinkedList();
		
		try {
			DBConn dbconn=new DBConn();
			//String sql="select * from br_doctadvice where CJCXRQ BETWEEN TO_DATE('2020-03-01', 'yyyy-MM-dd') and TO_DATE('2020-03-30', 'yyyy-MM-dd')";
			//String sql="select top 2000 * from br_doctadvice order by CJCXRQ desc";
			String sql="select * from br_doctadvice  where DateDiff(dd,CJCXRQ,getdate())>100 AND DateDiff(dd,CJCXRQ,getdate())<=200 AND BRZYID IN (select BRZYID from br_inpatientinfo where ZYKSID IN('1020500','1009100','1030200')) ORDER BY CJCXRQ desc";
			//String sql="select * from br_doctadvice  where DateDiff(dd,CJCXRQ,getdate())=1";
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				FDoctorAdviceVO doctorAdviceVO=new FDoctorAdviceVO();
				doctorAdviceVO.setDoctorAdviceId(rs.getString("BRYZID"));//病人医嘱ID
				doctorAdviceVO.setHospitalId(Long.valueOf("3"));//医院ID
				doctorAdviceVO.setBehospitalCode(rs.getString("BRZYID"));//病人ID
				doctorAdviceVO.setOrderDoctorName(rs.getString("YSKDPB"));//医生开单判别
				doctorAdviceVO.setFrequency(rs.getString("YZPLPB"));//医嘱频率判别
				doctorAdviceVO.setParentTypeId(rs.getString("FLYZID"));//父类医嘱ID
				doctorAdviceVO.setDoctorAdviceType(rs.getString("YZLXPB"));//医嘱类型判别
				doctorAdviceVO.setUsageNum(rs.getString("YCSYSL"));//一次使用数量
				doctorAdviceVO.setUsageUnit(rs.getString("YCYLDW"));//一次用量单位
				doctorAdviceVO.setDose(rs.getString("YZDCJL"));//医嘱单次剂量
				doctorAdviceVO.setDoseUnit(rs.getString("DCJLDW"));//单次剂量单位
				doctorAdviceVO.setMedModeType(rs.getString("GYFSID"));//给药方式
				doctorAdviceVO.setDaFrequency(rs.getString("YZPLID"));//医嘱频率
				doctorAdviceVO.setDaDealType(rs.getString("YZCLLX"));//医嘱处理类型
				doctorAdviceVO.setDaStartDate(rs.getDate("YZKSSJ"));//医嘱开始时间
				doctorAdviceVO.setDaItemName(rs.getString("YZXMMC"));//医嘱项目名称
				doctorAdviceVO.setDaStatus(rs.getString("YZZTPB"));//医嘱状态判别
				doctorAdviceVO.setDaStopDate(rs.getDate("YZJSSJ"));//医嘱结束时间
				doctorAdviceVO.setDaGroupNo(rs.getString("YZTZXH"));//医嘱同组序号
				doctorAdviceVO.setDaPrescriptionType(rs.getString("YZCFLX"));//医嘱处方类型
				doctorAdviceVO.setDaMedType(rs.getString("YZLYLX"));//医嘱领药类型
				doctorAdviceVO.setDoctorNotice(rs.getString("YSZTSM"));//医生嘱托
				doctorAdviceVO.setDoctorId(rs.getString("KDYSID"));//开单医生ID
				doctorAdviceVO.setDoctorName(rs.getString("KDYSMC"));//开单医生姓名
				
				//doctorAdviceVO.set
				
				DoctorAdvice doctorAdvice=new DoctorAdvice();
				BeanUtil.copyProperties(doctorAdviceVO, doctorAdvice);
				
				doctorAdviceList.add(doctorAdvice);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return doctorAdviceList;
	}

	/**
	 * 从视图中获取文书类型
	 * @return
	 */
	public List<MedicalRecord> getMedicalRecord(){
		List<MedicalRecord> medicalRecordList=Lists.newLinkedList();
		try {
			DBConn dbconn=new DBConn();
			//String sql="select * from ar_medirecclass where CJCXRQ BETWEEN TO_DATE('2020-02-01', 'yyyy-MM-dd') and TO_DATE('2020-04-30', 'yyyy-MM-dd')";
			//String sql="select * from mr_medicalrecords  where DateDiff(dd,CJCXRQ,getdate())=1";
			String sql="select * from mr_medicalrecords  where BRZYID IN (select BRZYID from br_inpatientinfo where ZYKSID IN('1020500','1009100','1030200'))";
			//String sql="select * from mr_medicalrecords  where BRZYID IN (select top 2000 BRZYID from br_inpatientinfo where ZYKSID IN('1040200','1050000') order by CJCXRQ desc)";
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				MedicalRecord medicalRecord=new MedicalRecord();
				medicalRecord.setRecId(rs.getString("BLJLID"));
				medicalRecord.setHospitalId(Long.valueOf("3"));//医院ID
				medicalRecord.setBehospitalCode(rs.getString("BRZYID"));//病人住院ID
				medicalRecord.setOrgCode(rs.getString("ZZJGDM"));//组织机构代码
				medicalRecord.setRecTypeId(rs.getString("BLMBID"));//对应his模板ID
				medicalRecord.setRecDate(rs.getString("BCJLSJ"));//病历日期
				medicalRecord.setRecTitle(rs.getString("BLJLMC"));//病历标题
				medicalRecord.setModeId(Long.valueOf(rs.getString("BLLBID")));//对应his类别ID
				medicalRecord.setGmtCreate(new Date());
				
				medicalRecordList.add(medicalRecord);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return medicalRecordList;
	}
	
	/**
	 * 从视图中获取文书内容
	 * @return
	 */
	public List<MedicalRecordContent> getMedicalRecordContent(){
		List<MedicalRecordContent> mrContentList=Lists.newLinkedList();
		try {
			DBConn dbconn=new DBConn();
			//String sql="select * from mr_mrcontent where CJCXRQ BETWEEN TO_DATE('2020-02-01', 'yyyy-MM-dd') and TO_DATE('2020-02-20', 'yyyy-MM-dd')";
			//String sql="select * from mr_mrcontent where DateDiff(dd,CJCXRQ,getdate())=0";
			//String sql="select * from mr_mrcontent WHERE BLJLID in (select BLJLID from mr_medicalrecords  where BRZYID IN (select BRZYID from br_inpatientinfo where ZYKSID IN('1020500','1009100','1030200')))";
			String sql="select * from mr_mrcontent WHERE BLJLID in (select BLJLID from mr_medicalrecords  where BRZYID IN (select BRZYID from br_inpatientinfo where ZYKSID IN('1020500','1009100','1030200')) AND BLJLMC='0110 查房记录(SOAP)')";
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				MedicalRecordContent mrContent=new MedicalRecordContent();
				mrContent.setHospitalId(Long.valueOf("3"));
				mrContent.setRecId(rs.getString("BLJLID"));
				mrContent.setXmlText(rs.getString("BLJLNR"));
				mrContent.setGmtCreate(new Date());
				mrContentList.add(mrContent);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mrContentList;
	}
	
	/**
	 * 从视图中获取文书类型
	 * @return
	 */
	public List<MedRecordType> getMedicalRecordType(){
		List<FMedicalRecordTypeVO> mrTypeVOList=Lists.newLinkedList();
		try {
			DBConn dbconn=new DBConn();
			//String sql="select * from ar_medirecclass where CJCXRQ BETWEEN TO_DATE('2020-02-01', 'yyyy-MM-dd') and TO_DATE('2020-02-20', 'yyyy-MM-dd')";
			String sql="select * from ar_medirecclass where SFZFPB='1'";
			ResultSet rs =dbconn.Query(sql, null);
			while(rs.next()){
				FMedicalRecordTypeVO mrTypeVO=new FMedicalRecordTypeVO();
				mrTypeVO.setTypeId(rs.getString("BLLBID"));
				mrTypeVO.setHospitalId(Long.valueOf("3"));
				mrTypeVO.setTypeName(rs.getString("BLLBMC"));
				mrTypeVO.setParentTypeId(rs.getString("SJLBID"));
				mrTypeVO.setObjName(rs.getString("BLLBFW"));
				mrTypeVO.setSpell(rs.getString("HZSRM1"));
				mrTypeVOList.add(mrTypeVO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		List<MedRecordType> medicalRecordTypeList=BeanUtil.listCopyTo(mrTypeVOList, MedRecordType.class);
		return medicalRecordTypeList;
	}
	
	public static void main(String[] args) {
		TZDBConn dbconn=new TZDBConn();
		//List<DoctorAdvice> doctorAdviceList=dbconn.getDoctorAdvice();
		
		dbconn.getBehospitalInfo();
	}
	
}