web_server/src/main/java/thyyxxk/webserver/dao/his/casefrontsheet/SheetSearchDao.java

package thyyxxk.webserver.dao.his.casefrontsheet;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import thyyxxk.webserver.entity.casefrontsheet.StatisticsBySeason;
import thyyxxk.webserver.entity.dictionary.CodeName;

import java.util.List;

@Mapper
public interface SheetSearchDao {
    @Select("select top 10 rtrim(code) as code, rtrim(name) as name from " +
            "(SELECT ROW_NUMBER() OVER(ORDER BY code) AS 'Number',* from " +
            "zd_icd9_cm3 with(nolock) where ${method} like #{content} and del_flag=0) a " +
            "where a.[Number]>10*(#{page}-1)")
    List<CodeName> searchSurgery(@Param("method") String method, @Param("content") String content, @Param("page") int page);

    @Select("select top 10 rtrim(code) as code, rtrim(name) as name from " +
            "(SELECT ROW_NUMBER() OVER(ORDER BY code) AS 'Number',* from " +
            "zd_icd_code_new with(nolock) where ${method} like #{content} and del_flag=0 ) a " +
            "where a.[Number]>10*(#{page}-1) order by weight desc")
    List<CodeName> searchClinicdiag(@Param("method") String method, @Param("content") String content, @Param("page") int page);

    @Select("select top 10 rtrim(code) code, rtrim(name) name from " +
            "(SELECT ROW_NUMBER() OVER(order by code) AS 'Number',  * " +
            "from zd_icd_m_code with(nolock) where ${method} like #{content}) a " +
            "where a.[Number]>10*(#{page})")
    List<CodeName> searchPathologicDiag(@Param("method") String method, @Param("content") String content, @Param("page") int page);

    @Select("select top 10 rtrim(code) code, rtrim(name) name from " +
            "(SELECT ROW_NUMBER() OVER(ORDER BY code) AS 'Number',  * " +
            "from zd_district_code with(nolock) where ${method} like #{content}) a " +
            "where a.[Number]>10*(#{page})")
    List<CodeName> searchBirthPlace(@Param("method") String method, @Param("content") String content, @Param("page") int page);

    @Select("select top 10 rtrim(code) code, rtrim(name) name from " +
            "(SELECT ROW_NUMBER() OVER(ORDER BY code) AS 'Number',  * " +
            "from zd_icd_code_new with(nolock) where PATINDEX('%[V-Y]%',code)=1 and ${method} like #{content}) a " +
            "where a.[Number]>10*(#{page}) order by weight desc")
    List<CodeName> searchHurtReason(@Param("method") String method, @Param("content") String content, @Param("page") int page);

    @Select("select top 10 rtrim(code) code, rtrim(name) name, rtrim(yb_code) ybCode, " +
            "deptName=(select rtrim(t.name) from zd_unit_code t where t.code=dept_code) from " +
            "(SELECT ROW_NUMBER() OVER(ORDER BY code) AS 'Number',  * " +
            "from a_employee_mi with(nolock) where isnull(del_flag,0)=0 and ${method} like #{content}) a " +
            "where a.[Number]>10*(#{page})")
    List<CodeName> searchEmployee(@Param("method") String method, @Param("content") String content, @Param("page") int page);

    @Select("select rtrim(dis_diag) from zy_dis_diag_yb where inpatient_no=#{bah} and admiss_times=#{times} and dis_diag_no=1")
    String selectMainMedinsDiag(@Param("bah") String bah, @Param("times") int times);

    @Select("select yb_code from zd_icd_code_new where code=#{code} and del_flag=0")
    String selectMedinsDiagReflect(@Param("code") String code);

    @Select("select top 1 disefamily_code from t_si_disefamily_diagnose with(nolock) where dise_code=#{code} or disefamily_code=#{code}")
    String selectDisefamilyCode(@Param("code") String code);

    @Select("select oprn_code as code, oprn_name as name, oprn_grade as ssfz from " +
            "t_si_operation_disefamily with(nolock) where disefamily_code=#{code} order by oprn_grade desc")
    List<CodeName> selectOperationDisefamilies(@Param("code") String code);

    @Select("select " +
            "rtrim(a.inpatient_no) as patNo,hospId='H43010500370',hospName='长沙泰和医院', " +
            "rtrim(isnull(b.occupation_code,'90')) as occupation,country='CHN', " +
            "marriage=case when b.marry_code='1' then '10' when b.marry_code='2' then '20' when b.marry_code='3' then '40' " +
            "when b.marry_code='4' then 30 when b.marry_code='9' then '90' end, " +
            "a.admiss_times as times,(rtrim(a.inpatient_no)+'-'+cast(admiss_times as varchar)) as bah, " +
            "rtrim(a.name) as name,a.sex AS gender,  " +
            "birthdate=convert(varchar(8),b.birth_date,112),age=(datediff(year,b.birth_date,getdate())), " +
            "b.social_no,admissDate=convert(varchar(8),a.admiss_date,112),disDate=convert(varchar(8),a.dis_date,112), " +
            "inHospDays=datediff(day,a.admiss_date,a.dis_date),  " +
            "disdiagName=isnull((select rtrim(dis_diag_comment) from zy_dis_diag_yb tmp where tmp.inpatient_no=a.inpatient_no and " +
            "tmp.admiss_times=a.admiss_times and tmp.dis_diag_no=1), " +
            "(select rtrim(dis_diag_comment) from ba_first_page1 tmp where tmp.inpatient_no=a.inpatient_no and " +
            "tmp.admiss_times=a.admiss_times and tmp.dis_diag_no=1)), " +
            "disdiagCode=isnull((select rtrim(dis_diag) from zy_dis_diag_yb tmp where tmp.inpatient_no=a.inpatient_no and " +
            "tmp.admiss_times=a.admiss_times and tmp.dis_diag_no=1), " +
            "(select rtrim(dis_diag) from ba_first_page1 tmp where tmp.inpatient_no=a.inpatient_no and " +
            "tmp.admiss_times=a.admiss_times and tmp.dis_diag_no=1)), " +
            "dismissWay=(select isnull(lyfs,1) from batj_ba2 tmp where tmp.zyh=a.inpatient_no and tmp.zycs=a.admiss_times), " +
            "a.total_charge " +
            "from zy_inactpatient a, a_patient_mi b " +
            "where responce_type in ('03','09','04','ad') and  " +
            "dis_date>=#{begntime} and " +
            "dis_date<=#{endtime} and " +
            "a.inpatient_no=b.inpatient_no")
    List<StatisticsBySeason> selectStatisticsBySeason(@Param("begntime") String begntime,
                                                      @Param("endtime") String endtime);

    @Select("select count(1) from zd_icd_code_new where yb_code=#{code} and del_flag=0")
    int medinsDiagExist(@Param("code") String code);

    @Select("select rtrim(dis_diag) code, rtrim(dis_diag_comment) name from zy_dis_diag_yb where " +
            "inpatient_no=#{patNo} and admiss_times=#{times} and dis_diag_no=1")
    CodeName selectMainDisDiag(@Param("patNo") String patNo, @Param("times") String times);

    @Select("select rtrim(yb_code) as code, rtrim(yb_name) as name " +
            "from zd_icd_code_new where yb_code like '${code}%' and del_flag=0 ")
    List<CodeName> selectMedinsDiags(@Param("code") String code);
}