mapper 父子孙查询

最新推荐文章于 2024-11-19 11:12:37 发布

煎饼果子就咖啡

最新推荐文章于 2024-11-19 11:12:37 发布

阅读量258

点赞数 3

CC 4.0 BY-SA版权

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/weixin_49498516/article/details/141221885

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qc.hcd.serve.mcDrug.mapper.McInstallMapper">

    <resultMap type="com.qc.hcd.serve.api.mcDrug.McInstall" id="McInstallResult">
        <result property="id" column="id"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="basicId" column="basic_id"/>
        <result property="idCard" column="id_card"/>
        <result property="name" column="name"/>
        <result property="bedName" column="bed_name"/>
    </resultMap>

    <resultMap type="com.qc.hcd.serve.api.mcDrug.vo.McInstallVo" id="queryByIdResult">
        <result property="id" column="p_id"/>
        <result property="basicId" column="p_basic_id"/>
        <result property="idCard" column="p_id_card"/>
        <result property="name" column="p_name"/>
        <result property="bedName" column="p_bed_name"/>
        <collection property="mcDrugMList" ofType="com.qc.hcd.serve.api.mcDrug.vo.McDrugMVo" resultMap="childResultMap">
        </collection>
    </resultMap>

    <resultMap id="childResultMap" type="com.qc.hcd.serve.api.mcDrug.vo.McDrugMVo">
        <result property="id" column="c_id"/>
        <result property="drugName" column="c_drug_name"/>
        <result property="drugSpecs" column="c_drug_specs"/>
        <result property="basicId" column="c_basic_id"/>
        <result property="idCard" column="c_id_card"/>
        <result property="drugCode" column="c_drug_code"/>
        <result property="notes" column="c_notes"/>
        <result property="frequencyDay" column="c_frequency_day"/>
        <result property="frequencyOrder" column="c_frequency_order"/>
        <collection property="mcInstallList" ofType="com.qc.hcd.serve.api.mcDrug.McDrugInstall"
                    resultMap="grandchildResultMap">
        </collection>
    </resultMap>

    <resultMap id="grandchildResultMap" type="com.qc.hcd.serve.api.mcDrug.McDrugInstall">
        <result property="id" column="g_id"/>
        <result property="delFlag" column="g_del_flag"/>
        <result property="createBy" column="g_create_by"/>
        <result property="createTime" column="g_create_time"/>
        <result property="updateBy" column="g_update_by"/>
        <result property="updateTime" column="g_update_time"/>
        <result property="keepDrugId" column="g_keep_drug_id"/>
        <result property="setTime" column="g_set_time"/>
        <result property="setNum" column="g_set_num"/>
    </resultMap>


    <delete id="realDeleteByBasicId" parameterType="java.lang.String">
        delete from mc_install where basic_id = #{basicId}
    </delete>


    <select id="queryById" resultMap="queryByIdResult"
            parameterType="java.lang.String">
         select
         a.id as p_id,
         a.basic_id as p_basic_id,
         a.id_card as p_id_card,
         a.name as p_name,
         a.bed_Name as p_bed_name,
         b.id as c_id,
         b.drug_name as c_drug_name,
         b.drug_specs as c_drug_specs,
         b.basic_id as c_basic_id,
         b.id_card as c_id_card,
         b.drug_code as c_drug_code,
         b.notes as c_notes,
         b.frequency_day as c_frequency_day,
         b.frequency_order as c_frequency_order,
         c.id as g_id,
         c.del_flag as g_del_flag,
         c.create_by as g_create_by,
         c.create_time as g_create_time,
         c.update_by as g_update_by,
         c.update_time as g_update_time,
         c.keep_drug_id as g_keep_drug_id,
         c.set_time as g_set_time,
         c.set_num as g_set_num
         from mc_install a
         left join mc_drug_m b on a.id = b.install_id
         left join mc_drug_install c on b.id = c.keep_drug_id
         where a.id=#{id}
    </select>
    <select id="listForRegistration" resultType="com.qc.hcd.serve.api.mcDrug.vo.McInstallVo">
        select
        a.basic_id,
        a.id_card,
        a.name,
        (select t2.bed_name from mc_register t1 left join mc_zone_bed t2 on t1.bed_id = t2.id where t1.status='2' and t1.basic_id = a.basic_id) AS bed_Name,
        c.set_time as use_time,
        b.drug_name,
        c.set_num as quantity_of_drugs,
        CONCAT(b.frequency_day, '天', b.frequency_order,'次' ) as medication_frequency,
        b.drug_code as drug_code,
        d.create_time AS last_medication_time,
        b.drug_code,
        CURDATE() as use_date,
        '1' as type_of_medication
        from mc_install a
        left join mc_drug_m b on a.basic_id = b.basic_id
        left join mc_drug_install c on b.id = c.keep_drug_id
        LEFT JOIN (

        SELECT t1.*
        FROM mc_drug_registration AS t1
        JOIN (
        SELECT basic_id, MAX(create_time) AS max_date
        FROM mc_drug_registration
        GROUP BY basic_id
        ) AS t2
        ON t1.basic_id = t2.basic_id AND t1.create_time = t2.max_date

        ) d ON a.basic_id = d.basic_id

        <where>
            a.del_flag = '0' and b.del_flag = '0' and c.del_flag = '0'
            <if test="bo.name != null and bo.name != ''">
                and a.name like CONCAT('%', #{bo.name} ,'%')
            </if>
            <if test="bo.useTime != null and bo.useTime != ''">
                and c.set_time = #{bo.useTime}
            </if>
        </where>
        ORDER BY a.create_time DESC
        <![CDATA[LIMIT #{bo.pageSize} OFFSET #{bo.num}]]>

    </select>
    <select id="queryList" resultType="com.qc.hcd.serve.api.mcDrug.vo.McInstallVo">
        select
        distinct
        a.id,
        a.create_time,
        a.basic_id,
        a.id_card,
        a.name,
        d.bed_name,
        b.sex,
        b.birth,
        b.phone
        from mc_install a
        left join hcd_basic_info b
        on a.basic_id = b.basic_id
        left join mc_register c
        on c.basic_id = a.basic_id and c.status = '2'
        left join mc_zone_bed d
        on d.id = c.bed_id
        where a.del_flag = '0' and b.del_flag = '0'
        <if test="bo.name != null and bo.name !='' ">
            and a.name like concat('%',#{bo.name},'%')
        </if>
        <if test="bo.idCard != null and bo.idCard !='' ">
            and a.idCard =#{bo.idCard}
        </if>
        <if test="bo.startDate != null and bo.startDate !='' ">
            <![CDATA[and a.create_time >= #{bo.startDate}]]>
        </if>
        <if test="bo.endDate != null and bo.endDate !='' ">
            <![CDATA[and a.create_time <= #{bo.endDate}]]>
        </if>
        order by a.create_time desc
    </select>


</mapper>

父

package com.qc.hcd.serve.api.mcDrug.vo;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.qc.common.excel.annotation.ExcelDictFormat;
import com.qc.common.excel.convert.ExcelDictConvert;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.Date;
import java.util.List;


/**
 * 药品缴存设置信息视图对象 mc_install
 *
 * @author qc
 * @date 2024-07-30
 */
@Data
@ApiModel("药品缴存设置信息视图对象")
@ExcelIgnoreUnannotated
public class McInstallVo {

    private static final long serialVersionUID = 1L;

    /**
     * 主键
     */
    @ExcelProperty(value = "主键")
    @ApiModelProperty("主键")
    private String id;

    /**
     * 老人id
     */
    @ExcelProperty(value = "老人id")
    @ApiModelProperty("老人id")
    private String basicId;

    /**
     * 老人身份证号
     */
    @ExcelProperty(value = "老人身份证号")
    @ApiModelProperty("老人身份证号")
    private String idCard;

    /**
     * 姓名
     */
    @ExcelProperty(value = "姓名")
    @ApiModelProperty("姓名")
    private String name;

    /**
     * 床位号
     */
    @ExcelProperty(value = "床位号")
    @ApiModelProperty("床位号")
    private String bedName;

    /**
     * 用药时间
     */
    @ApiModelProperty("用药时间")
    private String useTime;

    /**
     * 用药时间
     */
    @ApiModelProperty("药名")
    private String drugName;

    /**
     * 数量
     */
    @ApiModelProperty("数量")
    private String quantityOfDrugs;

    /**
     * 服药频率
     */
    @ApiModelProperty("服药频率")
    private String medicationFrequency;

    /**
     * 上一次吃药时间
     */
    @ApiModelProperty("上一次吃药时间")
    private String lastMedicationTime;

    /**
     * 药物编码
     */
    @ApiModelProperty("药物编码")
    private String drugCode;

    /**
     * 用药日期
     */
    @ApiModelProperty("用药日期")
    private String useDate;

    /**
     * 用药类型
     */
    @ApiModelProperty("用药类型")
    private String typeOfMedication;
    private String phone;
    private String sex;
    /*
     * 创建时间
     * */
    private String createTime;
    private String birth;
    private List<McDrugMVo> mcDrugMList;


}

子：

package com.qc.hcd.serve.api.mcDrug.vo;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.qc.common.excel.annotation.ExcelDictFormat;
import com.qc.common.excel.convert.ExcelDictConvert;
import com.qc.hcd.serve.api.mcDrug.McDrugInstall;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.Date;
import java.util.List;


/**
 * 老人药品设置视图对象 mc_drug_m
 *
 * @author qc
 * @date 2024-07-29
 */
@Data
@ApiModel("老人药品设置视图对象")
@ExcelIgnoreUnannotated
public class McDrugMVo {

    private static final long serialVersionUID = 1L;

    /**
     * 主键
     */
    @ExcelProperty(value = "主键")
    @ApiModelProperty("主键")
    private String id;

    /**
     * 药名
     */
    @ExcelProperty(value = "药名")
    @ApiModelProperty("药名")
    private String drugName;

    /**
     * 规格
     */
    @ExcelProperty(value = "规格")
    @ApiModelProperty("规格")
    private String drugSpecs;

    /**
     * 老人id
     */
    @ExcelProperty(value = "老人id")
    @ApiModelProperty("老人id")
    private String basicId;

    /**
     * 老人身份证号
     */
    @ExcelProperty(value = "老人身份证号")
    @ApiModelProperty("老人身份证号")
    private String idCard;

    /**
     * 药品编码
     */
    @ExcelProperty(value = "药品编码")
    @ApiModelProperty("药品编码")
    private String drugCode;

    /**
     * 备注
     */
    @ExcelProperty(value = "备注")
    @ApiModelProperty("备注")
    private String notes;

    /**
     * 天频率
     */
    @ExcelProperty(value = "天频率")
    @ApiModelProperty("天频率")
    private String frequencyDay;

    /**
     * 次频率
     */
    @ExcelProperty(value = "次频率")
    @ApiModelProperty("次频率")
    private String frequencyOrder;

    /**
     * 设置id
     */
    private String installId;


    /*
     * 药品设置集合
     */
    private List<McDrugInstall> mcInstallList;


}

孙：

package com.qc.hcd.serve.api.mcDrug;

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.Date;
import java.math.BigDecimal;

import com.qc.common.core.web.domain.BaseEntity;

/**
 * 用药设置对象 mc_drug_install
 *
 * @author qc
 * @date 2024-07-29
 */
@Data
@EqualsAndHashCode(callSuper = true)
@TableName("mc_drug_install")
public class McDrugInstall extends BaseEntity {

private static final long serialVersionUID=1L;

    /**
     * 主键
     */
     @TableId(value = "id")
     private String id;
    /**
     * 删除标志（0代表存在 2代表删除）
     */
     @TableLogic
     @TableField(value = "del_flag")
     private String delFlag;
    /**
     * 缴存药物id
     */
     @TableField(value = "keep_drug_id")
     private String keepDrugId;
    /**
     * 时间
     */
     @TableField(value = "set_time")
     private String setTime;
    /**
     * 数量
     */
     @TableField(value = "set_num")
     private String setNum;
    /**
     * 设置id
     */
    @TableField(value = "install_id")
    private String installId;

}