package com.ltkj.hosp.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ltkj.hosp.domain.TjReservation;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;


/**
 * 体检预约Mapper接口
 *
 * @author ltkj
 * @date 2022-11-28
 */
@Mapper
public interface TjReservationMapper extends BaseMapper<TjReservation> {
    /**
     * 查询体检预约
     *
     * @param id 体检预约主键
     * @return 体检预约
     */
    public TjReservation selectTjReservationById(String id);

    /**
     * 查询体检预约列表
     *
     * @param tjReservation 体检预约
     * @return 体检预约集合
     */
    public List<TjReservation> selectTjReservationList(TjReservation tjReservation);



    public List<TjReservation> getTjReservationList(TjReservation tjReservation);

    /**
     * 新增体检预约
     *
     * @param tjReservation 体检预约
     * @return 结果
     */
    public int insertTjReservation(TjReservation tjReservation);

    /**
     * 修改体检预约
     *
     * @param tjReservation 体检预约
     * @return 结果
     */
    public int updateTjReservation(TjReservation tjReservation);

    /**
     * 删除体检预约
     *
     * @param id 体检预约主键
     * @return 结果
     */
    public int deleteTjReservationById(Integer id);

    /**
     * 批量删除体检预约
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteTjReservationByIds(Integer[] ids);



    @Select("SELECT * FROM tj_reservation WHERE deleted = 1")
    public List<TjReservation> selectTjReservationByDelete();


    @Select("SELECT * FROM (\n" +
            "SELECT  a.id_card,a.pac_id,a.company,a.company_id,a.team_no,a.grouping_id FROM tj_reservation a WHERE a.deleted=0 AND a.company_id=#{compId} \n" +
            "UNION\n" +
            "SELECT  c.cus_idcard id_card, o.pac_id pac_id,o.firm_name company,o.firm_id company_id,o.firm_id team_no,IFNULL(o.pac_id,o.group_id) grouping_id FROM tj_order o " +
            "JOIN tj_customer c ON c.cus_id=o.user_id WHERE o.deleted=0 AND o.firm_id=#{compId} AND o.tj_type=1 AND o.pac_id !=0) aa GROUP BY aa.id_card")
    List<TjReservation> getFirimSfList(String compId);


    @Select("SELECT * FROM (\n" +
            "SELECT a.`name`,a.phoe,a.sex, a.id_card,a.pac_id,a.company,a.company_id,a.team_no,a.grouping_id FROM tj_reservation a " +
            "WHERE a.deleted=0 AND (a.team_no=#{teamNo} or a.company_id=#{teamNo}) AND a.pac_id=#{pacId}\n" +
            "UNION\n" +
            "SELECT c.cus_name name,c.cus_phone phone,c.cus_sex sex,c.cus_idcard id_card, o.pac_id pac_id,o.firm_name company,o.firm_id company_id,o.order_id team_no,IFNULL(o.pac_id,o.group_id) grouping_id FROM " +
            "tj_order o \n" +
            "JOIN tj_customer c ON c.cus_id=o.user_id WHERE o.deleted=0" +
            " AND (o.firm_id=(SELECT DISTINCT r.company_id FROM tj_reservation r " +
            "WHERE r.deleted=0 AND  ( r.team_no=#{teamNo} or r.company_id=#{teamNo})) or o.firm_id=#{teamNo}) \n" +
            "AND o.tj_type=1 AND o.pac_id =#{pacId}) aa GROUP BY aa.id_card;")
    List<TjReservation> getTeamTjPeopleList(@Param("teamNo") String teamNo,@Param("pacId") String pacId);
}