| | |
|---|
| | | * @param tjNumber 体检单号 |
|---|
| | | * @return |
|---|
| | | */ |
|---|
| | | @Select("SELECT @num := @num + 1 AS xh, d.dept_name ksmc, d.official_web ksdz, e.tjxm FROM sys_dept d JOIN ( select aa.ksmc,GROUP_CONCAT( DISTINCT aa.pro_name ORDER BY aa.pro_name DESC SEPARATOR ';' ) tjxm from ( SELECT (case when f.ksmc in ('临检室','化验室','','') or c.pro_name like '%测定%' then '检验科' when c.pro_name like '%心电图%' then '心电图室' when f.ksmc='x光室' or c.pro_name like '%X线摄影%' then '放射科' when c.pro_name like '%色觉%' then '眼科' \t\t\t\twhen c.pro_name like '%超%' or c.pro_name like '%多普勒%' then 'B超室' when (replace(f.ksmc,' ','')='' or f.ksmc is null) and c.dept_id<>'100' then d.dept_name when c.pro_name like '%CT%' then 'CT室' else '体检中心' end ) ksmc, c.pro_name FROM tj_order a JOIN tj_order_detail b ON b.order_id = a.order_id JOIN tj_project c ON c.pro_id = b.pro_id left join his_mxfyxm e on e.mxfyxmbm=c.his_xmbm left join ltkj_his_dept f on f.ksbm = CONCAT('00',(case when e.hsks is null then '' else e.hsks end)) join sys_dept d on d.dept_id=c.dept_id WHERE a.tj_number = #{tjNumber} ) aa GROUP BY ksmc ) e ON e.ksmc = d.dept_name , (SELECT @num := 0) a WHERE d.parent_id = '100' ORDER BY xh") |
|---|
| | | List<Map<String ,String >> selectDjdInfo(@Param("tjNumber")String tjNumber); |
|---|
| | | // @Select("SELECT @num := @num + 1 AS xh, d.dept_name ksmc, d.official_web ksdz, e.tjxm FROM sys_dept d JOIN " + |
|---|
| | | // "( select aa.ksmc,GROUP_CONCAT( DISTINCT aa.pro_name ORDER BY aa.pro_name DESC SEPARATOR ';' ) tjxm from " + |
|---|
| | | // "( SELECT (case when f.ksmc in ('临检室','化验室','','') or c.pro_name like '%测定%' then '检验科' " + |
|---|
| | | // " when c.pro_name like '%心电图%' then '心电图室' when f.ksmc='x光室' or c.pro_name like '%X线摄影%' then '放射科' when c.pro_name like '%色觉%' then '眼科' " + |
|---|
| | | // "\t\t\t\twhen c.pro_name like '%超%' or c.pro_name like '%多普勒%' then 'B超室' when (replace(f.ksmc,' ','')='' or f.ksmc is null) and c.dept_id<>'100' then " + |
|---|
| | | // " d.dept_name when c.pro_name like '%CT%' then 'CT室' else '体检中心' end ) ksmc, c.pro_name FROM tj_order a JOIN tj_order_detail b ON b.order_id = a.order_id " + |
|---|
| | | // "JOIN tj_project c ON c.pro_id = b.pro_id left join his_mxfyxm e on e.mxfyxmbm=c.his_xmbm left join ltkj_his_dept f on f.ksbm = CONCAT('00'," + |
|---|
| | | // "(case when e.hsks is null then '' else e.hsks end)) join sys_dept d on d.dept_id=c.dept_id WHERE a.tj_number = #{tjNumber} ) aa GROUP BY ksmc ) e ON e.ksmc = " + |
|---|
| | | // "d.dept_name , (SELECT @num := 0) a WHERE d.parent_id = '100' ORDER BY xh") |
|---|
| | | // List<Map<String ,String >> selectDjdInfo(@Param("tjNumber")String tjNumber); |
|---|
| | | |
|---|
| | | |
|---|
| | | @Select("SELECT \n" + |
|---|
| | | " ROW_NUMBER() OVER (ORDER BY d.djd_num) AS xh, " + |
|---|
| | | " REPLACE(d.dept_name, '门诊', '') AS ksmc,\n" + |
|---|
| | | " ifnull(d.official_web,'') AS ksdz,\n" + |
|---|
| | | " e.tjxm,\n" + |
|---|
| | | " e.tjjg\n" + |
|---|
| | | "FROM sys_dept d\n" + |
|---|
| | | "JOIN (\n" + |
|---|
| | | " SELECT\n" + |
|---|
| | | " c.dept_id,\n" + |
|---|
| | | " GROUP_CONCAT(DISTINCT c1.pro_name ORDER BY c1.pro_name DESC SEPARATOR ';') AS tjxm,\n" + |
|---|
| | | " SUM(b.ord_price) AS tjjg\n" + |
|---|
| | | " FROM tb_transition b\n" + |
|---|
| | | " JOIN tj_project c ON c.pro_id = b.parent_pro_id\n" + |
|---|
| | | " JOIN tj_project c1 ON c1.pro_id = b.parent_pro_id\n" + |
|---|
| | | " WHERE trim(b.tj_num) = #{tjNumber} \n" + |
|---|
| | | " AND c.pro_parent_id = 0\n" + |
|---|
| | | " AND c1.pro_name NOT IN ('主任医师诊查', '(tj)体检费(物理检查)')\n" + |
|---|
| | | " AND c1.pro_name NOT LIKE '%主任医师诊查%'\n" + |
|---|
| | | " GROUP BY c.dept_id\n" + |
|---|
| | | ") e ON e.dept_id = d.dept_id\n" + |
|---|
| | | "ORDER BY d.djd_num") |
|---|
| | | List<Map<String ,Object >> selectDjdInfo(@Param("tjNumber")String tjNumber); |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * 查询导检单用户信息 |
|---|
| | | * @param tjNumber |
|---|
| | | * @return |
|---|
| | | */ |
|---|
| | | @Select("select c.tj_number tjh,a.cus_name tjname, b.dict_label sex,a.create_time tjsj,ifnull(d.cn_name,'') dwmc,a.cus_phone lxdh\n" + |
|---|
| | | @Select("select c.tj_number tjh,a.cus_name tjname, b.dict_label sex,c.create_time tjsj,ifnull(c.firm_name,'') dwmc,a.cus_phone lxdh,ifnull(c.firm_dept_name,'') dwbm,\n" + |
|---|
| | | "ifnull((select dict_label from sys_dict_data where dict_type ='dict_team' and status=0 and dict_value=c.tj_type),'') tjlx,\n" + |
|---|
| | | "( (\n" + |
|---|
| | | " SELECT\n" + |
|---|
| | | " sum( b1.ord_price ) \n" + |
|---|
| | | " FROM\n" + |
|---|
| | | " tb_transition b1\n" + |
|---|
| | | " JOIN tj_project c1 ON c1.pro_id = b1.parent_pro_id \n" + |
|---|
| | | " AND c1.pro_parent_id = 0 \n" + |
|---|
| | | " WHERE\n" + |
|---|
| | | " b1.tj_num = c.tj_number \n" + |
|---|
| | | " )*(IFNULL(c.discount,10)/10)) tjze,c.card_id ,CONCAT(cast(a.age as char),e.dict_label) nl,(SELECT tb.pac_name FROM tb_transition tb" + |
|---|
| | | " WHERE tb.tj_num = c.tj_number GROUP BY tb.pac_id)tcmc,a.his_jzkh\n" + |
|---|
| | | "from tj_order c \n" + |
|---|
| | | " join tj_customer a on a.cus_id= c.user_id \n" + |
|---|
| | | "\tjoin tj_customer a on a.cus_id= c.user_id \n" + |
|---|
| | | " join (select dict_value,dict_label from sys_dict_data where dict_type='sys_user_sex') b on b.dict_value = a.cus_sex\n" + |
|---|
| | | " left join dict_comp d on d.drug_manufacturer_id = c.firm_id\n" + |
|---|
| | | "where c.tj_number=#{tjNumber}") |
|---|
| | | "\tleft join dict_comp d on d.drug_manufacturer_id = c.firm_id\n" + |
|---|
| | | "join (select dict_label,dict_value from sys_dict_data where dict_type='dict_ageunit') e on e.dict_value = a.age_unit\n" + |
|---|
| | | "where trim(c.tj_number)=#{tjNumber}") |
|---|
| | | Map<String ,Object > selectDjdUserInfo(@Param("tjNumber")String tjNumber); |
|---|
| | | |
|---|
| | | |
|---|
