package com.ltkj.hosp.mapper;

import java.util.Date;
import java.util.List;

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

/**
 * 体检单位部门Mapper接口
 *
 * @author ltkj_赵佳豪&李格
 * @date 2023-08-07
 */
@Mapper
public interface TjDwDeptMapper extends BaseMapper<TjDwDept> {
    /**
     * 查询体检单位部门
     *
     * @param id 体检单位部门主键
     * @return 体检单位部门
     */
    public TjDwDept selectTjDwDeptById(String id);

    /**
     * 查询体检单位部门列表
     *
     * @param tjDwDept 体检单位部门
     * @return 体检单位部门集合
     */
    public List<TjDwDept> selectTjDwDeptList(TjDwDept tjDwDept);

    /**
     * 新增体检单位部门
     *
     * @param tjDwDept 体检单位部门
     * @return 结果
     */
    public int insertTjDwDept(TjDwDept tjDwDept);

    /**
     * 修改体检单位部门
     *
     * @param tjDwDept 体检单位部门
     * @return 结果
     */
    public int updateTjDwDept(TjDwDept tjDwDept);

    /**
     * 删除体检单位部门
     *
     * @param id 体检单位部门主键
     * @return 结果
     */
    public int deleteTjDwDeptById(String id);

    /**
     * 批量删除体检单位部门
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteTjDwDeptByIds(String[] ids);


    @Select("SELECT * FROM tj_dw_dept a WHERE a.deleted=0 GROUP BY a.dw_id")
    List<TjDwDept> getDwDeptNameList();


    @Select("SELECT * FROM tj_dw_dept a WHERE a.deleted=0 AND a.dw_id=#{id}")
    List<TjDwDept> getDwDeptListByTjDwDeptId(String id);


    @Select("\n" +
            "SELECT aa.bg_type, aa.dw_name,aa.batch,aa.dept,sum(aa.people_count) people_count,sum(aa.yj_count) yj_count,(sum(aa.people_count)-sum(aa.yj_count))wj_count FROM (\n" +
            "\n" +
            "SELECT '团体' bg_type, r.cn_name dw_name,COUNT(DISTINCT a.team_no) batch,a.department dept,COUNT(*) people_count, 0 yj_count,0 wj_count  FROM tj_reservation a " +
            "LEFT JOIN dict_comp r ON a.company_id=r.drug_manufacturer_id" +
            " WHERE a.deleted=0 AND a.grouping_id IN (\n" +
            "\n" +
            "   SELECT b.id FROM tj_dw_grouping b WHERE b.deleted=0 AND b.dw_dept_id=#{dwDeptId}\n" +
            "\t \n" +
            ") AND a.create_time BETWEEN ifnull(#{beginTime},DATE_SUB(CURDATE(),INTERVAL 3 DAY)) and ifnull(#{endTime},SYSDATE())\n" +
            "\n" +
            "UNION\n" +
            "\n" +
            "SELECT '团体' bg_type, '' dw_name,'' batch,''dept,0 people_count,count(*) yj_count,0 wj_count FROM tj_order d WHERE d.deleted=0 AND d.user_id IN (\n" +
            "SELECT c.cus_id  FROM tj_reservation a LEFT JOIN tj_customer c ON a.id_card=c.cus_idcard WHERE a.deleted=0 AND a.grouping_id IN (\n" +
            "\n" +
            "   SELECT b.id FROM tj_dw_grouping b WHERE b.deleted=0 AND b.dw_dept_id=#{dwDeptId}\n" +
            "\t \n" +
            ") AND a.create_time BETWEEN ifnull(#{beginTime},DATE_SUB(CURDATE(),INTERVAL 3 DAY)) and ifnull(#{endTime},SYSDATE())  AND IFNULL(NULL,c.cus_id)\n" +
            ") AND d.group_id IN (SELECT b.id FROM tj_dw_grouping b WHERE b.deleted=0 AND b.dw_dept_id=#{dwDeptId}) AND d.create_time BETWEEN ifnull(#{beginTime},DATE_SUB(CURDATE(),INTERVAL 3 DAY)) and ifnull(#{endTime},SYSDATE())\n" +
            ")aa GROUP BY aa.bg_type")
    List<TeamTjDwBaoGaoVo> getTeamTjDwBaoGaoVoByTjDwDeptId(@Param(value = "dwDeptId") String dwDeptId,@Param(value = "beginTime") Date beginTime,@Param(value = "endTime") Date endTime);
}