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, a.company dw_name,COUNT(DISTINCT a.team_no) batch,a.department dept,COUNT(*) people_count, 0 yj_count,0 wj_count FROM tj_reservation a 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);
|
}
|
