web_server/src/main/java/thyyxxk/webserver/dao/his/querydata/QueryDrugsAndProjectsDao.java

package thyyxxk.webserver.dao.his.querydata;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import thyyxxk.webserver.entity.datamodify.GetDropdownBox;
import thyyxxk.webserver.entity.querydata.QueryDrugsAndProjects;
import thyyxxk.webserver.entity.reports.illegalchargesanalysis.IllegalChargeData;

import java.util.List;

/**
 * <p>
 * 描述
 * </p>
 *
 * @author xc
 * @date 2021-04-16 16:01
 */
@Mapper
public interface QueryDrugsAndProjectsDao {

    /**
     * 获取科室
     *
     * @return 返回code和 name
     */
    @Select("select code,rtrim(name) name from zy_ward_code")
    List<GetDropdownBox> getWard();

    /**
     * 获取收费类型
     *
     * @return 返回code 和 name
     */
    @Select("select code,rtrim(name)name from zd_charge_class")
    List<GetDropdownBox> getChergeClass();

    /**
     * 查询收费项目
     *
     * @param param 传入查询条件 时间范围 科室 费用类型 收费编码
     * @return 返回
     */
    @Select("<script>" +
            "select a.charge_code_mx chargeCode,b.name name,sum(a.charge_amount) number,sum(charge_fee) amount_of_money, " +
            "       classCode=c.name,source='住院' " +
            "from zy_detail_charge a , zd_charge_item b ,  zd_charge_class c " +
            "where  a.charge_code_mx=b.code and b.class_code=c.code " +
            "<if test=\"startTime!=null and startTime!=''\">" +
            "and charge_date&gt;=#{startTime} and charge_date&lt;#{endTime} " +
            "</if>" +
            "<if test=\"classCode!=null and classCode!=''\">" +
            "and b.class_code=#{classCode} " +
            "</if>" +
            "<if test=\"ward!=null and ward!=''\">" +
            "and a.exec_unit=#{ward} " +
            "</if>" +
            "<if test=\"chargeCode!=null and chargeCode!=''\">" +
            "and charge_code_mx=#{chargeCode} " +
            "</if>" +
            "GROUP BY a.charge_code_mx,b.name,c.name " +
            "union all " +
            "select a.charge_item_code chargeCode,b.name name,sum(a.quantity*drug_win) amount_of_money, " +
            "       sum(a.quantity*drug_win*unit_price) amountOfMoney, classCode=c.name,source='门诊' " +
            "from mz_charge_detail_b a , zd_charge_item b , zd_charge_class c " +
            "where a.charge_item_code=b.code and b.class_code=c.code " +
            "<if test=\"startTime!=null and startTime!=''\">" +
            "and a.charge_date&gt;=#{startTime} and a.charge_date&lt;#{endTime} " +
            "</if>" +
            "<if test=\"classCode!=null and classCode!=''\">" +
            "and b.class_code=#{classCode} " +
            "</if>" +
            "<if test=\"ward!=null and ward!=''\">" +
            "and a.exec_dept=#{ward} " +
            "</if>" +
            "<if test=\"chargeCode!=null and chargeCode!=''\">" +
            "and a.charge_item_code=#{chargeCode} " +
            "</if>" +
            "  and a.pay_mark='0' " +
            "group by a.charge_item_code,a.serial,b.name,c.name" +
            "</script>")
    List<QueryDrugsAndProjects> queryProjects(QueryDrugsAndProjects param);


    /**
     * @param startTime  开始时间
     * @param endTime    结束时间
     * @param chargeCode 编码
     * @return 返回查询的数据
     */
    @Select("<script>" +
            "select patient, times, name, social_no, phone, admiss_date, dis_date,CONVERT(varchar(100), charge_date, 23) charge_date_string, dept,exec_unit,yb_type, " +
            "       SUM(amount_of_money) amount_of_money,SUM(number) number,charge_code, source from (" +
            "SELECT " +
            "    rtrim(a.inpatient_no) patient,a.admiss_times times,rtrim(b.name) name,b.social_no social_no,b.employer_tel phone,c.admiss_date,c.dis_date," +
            "CONVERT(varchar(100), a.charge_date, 23)  charge_date, " +
            "    (select name from zd_unit_code where code=c.small_dept) dept, " +
            "(select name from zd_unit_code where code= a.exec_unit) exec_unit, " +
            "    (select name from zy_zd_responce_type where code=c.responce_type) yb_type, " +
            "    sum(a.charge_amount) number,sum(a.charge_fee) amount_of_money,charge_code_mx charge_code,source='住院' " +
            "FROM zy_detail_charge a,a_patient_mi b,zy_inactpatient c " +
            "where charge_code_mx = #{chargeCode} " +
            "  and a.inpatient_no=b.inpatient_no " +
            "  and a.inpatient_no=c.inpatient_no " +
            "  and a.admiss_times=c.admiss_times " +
            "  and charge_date &gt;= #{startTime} and charge_date &lt;= #{endTime} " +
            "<if test=\"dept != '' and dept !=null \">" +
            "  and a.exec_unit = #{dept} " +
            "</if>" +
            "group by charge_code_mx,a.inpatient_no,a.admiss_times,b.name,b.social_no,b.employer_tel,c.admiss_date,c.dis_date,c.small_dept,a.exec_unit,c.responce_type,a.charge_date " +
            "union all " +
            "SELECT " +
            "   rtrim(a.inpatient_no) patient,a.admiss_times times,rtrim(b.name) name ,b.social_no social_no,b.employer_tel phone,c.admiss_date,c.dis_date," +
            "CONVERT(varchar(100), a.charge_date, 23)  charge_date," +
            "    (select name from zd_unit_code where code=c.small_dept) dept, " +
            "(select name from zd_unit_code where code= a.exec_unit) exec_unit, " +
            "    (select name from zy_zd_responce_type where code=c.responce_type) yb_type, " +
            "    sum(a.charge_amount) number,sum(a.charge_fee) amount_of_money,charge_code_mx charge_code,source = '住院' " +
            "FROM zy_detail_charge a,a_patient_mi b,zy_actpatient c " +
            "where charge_code_mx = #{chargeCode} " +
            "  and a.inpatient_no=b.inpatient_no " +
            "  and a.inpatient_no=c.inpatient_no " +
            "  and a.admiss_times=c.admiss_times " +
            "  and charge_date &gt;= #{startTime} and charge_date &lt;= #{endTime} " +
            "<if test=\"dept != '' and dept !=null \">" +
            "  and a.exec_unit = #{dept} " +
            "</if>" +
            "group by charge_code_mx,a.inpatient_no,a.admiss_times,b.name,b.social_no,b.employer_tel,c.admiss_date,c.dis_date,c.small_dept,a.exec_unit,c.responce_type,a.charge_date " +
            "union all " +
            "    select rtrim(a.patient_id) patient,a.times times,rtrim(b.name) name ,b.social_no social_no,b.phone_no phone," +
            "a.charge_date admiss_date,dis_date = null,CONVERT(varchar(100), a.charge_date, 23)  charge_date, " +
            "    (select name from zd_unit_code where code = a.warn_dept) dept, " +
            "    (select name from zd_unit_code where code = a.exec_dept) exec_unit, " +
            "           yb_type = '自费',sum(a.quantity*a.drug_win) number,sum(a.quantity*drug_win*unit_price) amount_of_money,a.charge_item_code,source = '门诊' " +
            "           from mz_charge_detail_b a,mz_patient_mi b " +
            "where a.patient_id = b.patient_id " +
            "and a.times = b.times " +
            "and charge_item_code = #{chargeCode} " +
            "and charge_date &gt;= #{startTime} and charge_date &lt;= #{endTime} " +
            "<if test=\"dept != '' and dept !=null \">" +
            "and a.exec_dept = #{dept} " +
            "</if>" +
            "group by  charge_item_code,a.patient_id,a.times,b.name,social_no,phone_no,charge_date,exec_dept,a.warn_dept) temp GROUP BY " +
            "patient,times,name,social_no,phone,admiss_date,dis_date,charge_date,dept,exec_unit,yb_type,amount_of_money,number,charge_code,source" +
            "</script>")
    // 如果不希望显示 0 的话 就去掉 amount_of_money 和 number
    List<QueryDrugsAndProjects> chaXunXiangMuHuoYaoPinJuTiXinXi(@Param("startTime") String startTime,
                                                                @Param("endTime") String endTime,
                                                                @Param("chargeCode") String chargeCode,
                                                                @Param("dept") String dept);


    /**
     * 查询药品
     *
     * @param param 搜索条件 日期范围 是否为抗生素 药品大类 药品编码
     * @return 返回符合条件的数据
     */
    @Select("<script>" +
            "select a.charge_code_mx chargeCode," +
            "case a.serial when '01' then '药品(小)' when '99' then '药品(大)' end packageSpecification," +
            "b.name name,sum(a.charge_amount) number,sum(charge_fee) amountOfMoney,source='住院',classCode=c.name " +
            "from zy_detail_charge a,yp_zd_dict b,yp_zd_drug_kind c " +
            "where " +
            "1=1 " +
            "<if test=\"startTime!=null and startTime!=''\">" +
            "and charge_date&gt;=#{startTime} and charge_date&lt;#{endTime} " +
            "</if>" +
            "<if test=\"kssFlag!=null and kssFlag!=''\">" +
            "and b.kss_flag=#{kssFlag} " +
            "</if>" +
            "<if test=\"categoriesFlag!=null and categoriesFlag!=''\">" +
            "and b.categories_flag=#{categoriesFlag} " +
            "</if>" +
            " and a.charge_code_mx=b.code and a.serial=b.serial " +
            " and b.drug_kind=c.code " +
            "<if test=\"drugCode!=null and drugCode!=''\">" +
            "and b.code=#{drugCode}  " +
            "</if>" +
            "GROUP BY a.charge_code_mx,a.serial,b.name,c.name " +
            "union all " +
            "select a.charge_item_code chargeCode," +
            "case a.serial when '01' then '药品(小)' when '99' then '药品(大)' end packageSpecification," +
            "b.name name,sum(a.quantity*drug_win) number,sum(a.quantity*drug_win*unit_price) amountOfMoney,source='门诊',classCode=c.name " +
            "from mz_charge_detail_b a,yp_zd_dict b,yp_zd_drug_kind c " +
            "where " +
            "1=1 " +
            "<if test=\"startTime!='' and startTime!=null\">" +
            "and a.charge_date&gt;=#{startTime} and a.charge_date&lt;#{endTime} " +
            "</if>" +
            "<if test=\"kssFlag!=null and kssFlag!=''\">" +
            "and b.kss_flag=#{kssFlag}  " +
            "</if>" +
            "<if test=\"categoriesFlag!= null and categoriesFlag!=''\">" +
            "and b.categories_flag=#{categoriesFlag} " +
            "</if>" +
            "and a.charge_item_code=b.code " +
            "and a.serial=b.serial " +
            "and a.pay_mark='0' " +
            "and b.drug_kind=c.code " +
            "<if test=\"drugCode!=null and drugCode!='' \">" +
            "and b.code=#{drugCode} " +
            "</if>" +
            "group by a.charge_item_code,a.serial,b.name,c.name" +
            "</script>")
    List<QueryDrugsAndProjects> queryDrugs(QueryDrugsAndProjects param);


    /**
     * 查询药品或者项目名称
     *
     * @param tableName 表名
     * @param pyCode    首字母 以及 项目编码
     * @return 返回list
     */
    @Select("select rtrim(code) code,rtrim(name) name from ${tableName} where (py_code like #{pyCode} or code like #{pyCode} or name like #{pyCode})")
    List<GetDropdownBox> chaXunXiangMuhuoYaoPinCode(@Param("tableName") String tableName,
                                                    @Param("pyCode") String pyCode);


    @Select("<script>" +
            "select rtrim(b.inpatient_no) inpatient_no,rtrim(a.name) name,a.responce_type,b.admiss_times," +
            "sum(b.charge4+b.charge6+b.charge8+b.charge9+b.charge11+b.charge18) jianChaFei," +
            "sum(b.charge13) jianYanFei,sum(b.total_charge) zongFeiYong, " +
            "       (sum(b.charge4+b.charge6+b.charge8+b.charge9+b.charge11+b.charge18)+sum(b.charge13))/sum(b.total_charge) jcjyfzb, " +
            "       a.admiss_date,a.dis_date, (select name from zd_unit_code where code=a.small_dept) deptName " +
            "from zy_inactpatient a,zy_ledger_file b " +
            "where a.inpatient_no not like '%$%' and a.inpatient_no not like 'S%' and a.inpatient_no not like 'JT%' " +
            "  and a.inpatient_no=b.inpatient_no and a.admiss_times=b.admiss_times and b.settle_type&lt;&gt;1 " +
            "<if test=\"start !=null and start != '' \">" +
            " and a.admiss_date &gt;= #{start} and a.admiss_date &lt;= #{end} " +
            "</if>" +
            "<if test=\"responces.size > 0\" >" +
            "and a.responce_type in " +
            "<foreach collection='responces' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "group by b.inpatient_no,b.admiss_times,a.admiss_date,a.dis_date,a.small_dept,a.name,a.responce_type " +
            "having (sum(b.charge4+b.charge6+b.charge8+b.charge9+b.charge11+b.charge18)+sum(b.charge13))&gt;0 " +
            "order by (sum(b.charge4+b.charge6+b.charge8+b.charge9+b.charge11+b.charge18)+sum(b.charge13))/sum(b.total_charge) desc" +
            "</script>")
    List<IllegalChargeData> chaXunJianYanJianCha(@Param("start") String start,
                                                 @Param("end") String end,
                                                 @Param("responces") List<String> responces);


    @Select("<script>" +
            "select rtrim(inpatient_no) patient,admiss_times times,dis_diag_comment,dis_diag_no from ba_first_page1 where " +
            "inpatient_no in  " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</script>")
    List<QueryDrugsAndProjects> queryZhenDuan(@Param("list") List<String> list);


}