package com.ltkj.hosp.mapper;

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

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ltkj.hosp.domain.LtkjJianchajianyanTree;
import com.ltkj.hosp.domain.TjCustomer;
import com.ltkj.hosp.domain.TjOrder;
import com.ltkj.hosp.vodomain.LineChartsVo;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 体检记录Mapper接口
 *
 * @author ltkj
 * @date 2022-11-21
 */
@Mapper
public interface TjOrderMapper extends BaseMapper<TjOrder> {
    /**
     * 查询体检记录
     *
     * @param orderId 体检记录主键
     * @return 体检记录
     */
    public TjOrder selectTjOrderByOrderId(Long orderId);

    /**
     * 查询体检记录列表
     *
     * @param tjOrder 体检记录
     * @return 体检记录集合
     */
    public List<TjOrder> selectTjOrderList(TjOrder tjOrder);


    public List<TjOrder> selectExportOrderList(TjOrder tjOrder);

//    /*
//    * 根据tjNumber查询体检记录
//    * */
//    public List<TjOrder> selectTjOrderListByTjNumber(Long tjNumber);

    /**
     * 新增体检记录
     *
     * @param tjOrder 体检记录
     * @return 结果
     */
    public int insertTjOrder(TjOrder tjOrder);

    /**
     * 修改体检记录
     *
     * @param tjOrder 体检记录
     * @return 结果
     */
    public int updateTjOrder(TjOrder tjOrder);

    /**
     * 删除体检记录
     *
     * @param orderId 体检记录主键
     * @return 结果
     */
    public int deleteTjOrderByOrderId(Long orderId);

    /**
     * 批量删除体检记录
     *
     * @param orderIds 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteTjOrderByOrderIds(Long[] orderIds);


    @Select(" select * from tj_order where 1 = 1 and deleted=0  and user_id = #{cusId}")
    List<TjOrder> getOrderList(Long cusId);


    List<TjOrder> getOrderList1();

//    @Select("select aa.date,\n" +
//            "      sum(if(c.tj_type='团队', ifnull(c.coun,0),0)) as tdcoun,\n" +
//            "      sum(if(c.tj_type='个人', ifnull(c.coun,0),0)) as grcoun,\n" +
//            "      sum(if(c.tj_type='报告', ifnull(c.coun,0),0)) as bgcoun \n" +
//            "  from (\n" +
//            "      SELECT date_sub(CURDATE(),interval @i:=@i+1 day) as date\n" +
//            "      from (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all \n" +
//            "            select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all   \n" +
//            "            select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all  \n" +
//            "            select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all  \n" +
//            "            select 1 union all select 1) as a,\n" +
//            "            (select @i:= -1) b) aa \n" +
//            "  left join (\n" +
//            "      select date_format(create_time, '%Y-%m-%d') dat, tj_type, count(*) coun\n" +
//            "      from tj_order where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)\n" +
//            "      group by date_format(create_time, '%Y-%m-%d'),tj_type\n" +
//            "      union\n" +
//            "      select date_format(report_time, '%Y-%m-%d') dat, '报告' tj_type, count(*) coun\n" +
//            "      from tj_order where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)\n" +
//            "      group by date_format(report_time, '%Y-%m-%d')\n" +
//            "      ) c on c.dat =aa.date\n" +
//            "group by aa.date")
//    List<Map<Object, Object>> getLine();

    @Select("select aa.date,\n" +
            "                  sum(if(c.tj_type='1', ifnull(c.coun,0),0)) as tdcoun,\n" +
            "                  sum(if(c.tj_type='2', ifnull(c.coun,0),0)) as grcoun,\n" +
            "                  sum(if(c.tj_type='3', ifnull(c.coun,0),0)) as bgcoun \n" +
            "              from (\n" +
            "                  SELECT date_sub(CURDATE(),interval @i:=@i+1 day) as date\n" +
            "                  from (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all \n" +
            "                        select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all \n" +
            "                        select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all \n" +
            "                        select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all \n" +
            "                        select 1 union all select 1) as a,\n" +
            "                        (select @i:= -1) b) aa\n" +
            "              left join (\n" +
            "                     select date_format(create_time, '%Y-%m-%d') dat, tj_type, count(*) coun\n" +
            "                  from tj_order where deleted=0 AND tj_type=2 AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)\n" +
            "                  group by date_format(create_time, '%Y-%m-%d')\t\n" +
            "\t\t\t\tunion\n" +
            "                  select date_format(create_time, '%Y-%m-%d') dat,tj_type, count(*) coun\n" +
            "                  from tj_order where deleted=0 AND tj_type=1 AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)\n" +
            "                  group by date_format(create_time, '%Y-%m-%d')\t\t\t\t\t\n" +
            "\t\t\t\tunion\n" +
            "                  select date_format(create_time, '%Y-%m-%d') dat, 3 tj_type, count(*) coun\n" +
            "                  from tj_order where deleted=0 AND !ISNULL(report_time) AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)\n" +
            "                  group by date_format(create_time, '%Y-%m-%d')\n" +
            "                  ) c on c.dat =aa.date\n" +
            "            group by aa.date")
    List<LineChartsVo> getLine();


    @Select("SELECT * FROM tj_order WHERE tj_number RLIKE #{tjNum} AND deleted='0' ORDER BY create_time DESC")
    public List<TjOrder> getOrderListByLikeTjNum(@Param("tjNum") String tjNum);


    @Select("SELECT * FROM tj_order WHERE user_id = #{cusId} AND deleted='0' ORDER BY create_time DESC")
    public List<TjOrder> getOrderListByCusId(@Param("cusId") String cusId);

    @Select("SELECT * FROM tj_order WHERE tj_number = #{tjNum} AND deleted='0' ")
    public TjOrder getOrderByTjNum(String tjNum);

    @Select("SELECT * FROM tj_order WHERE card_id = #{cardId} AND deleted='0' ")
    public TjOrder getOrderByCardId(String cardId);

    List<TjOrder> getTjOrderList(@Param("tjNum")String tjNum,
                                 @Param("checkStatus")Integer checkStatus,
                                 @Param("compId")Long compId,
                                 @Param("beginTime") Date beginTime,
                                 @Param("endTime") Date endTime);


    List<TjOrder> getWCsTjOrderList(@Param("tjNum")String tjNum,
                                 @Param("compId")Long compId,
                                 @Param("beginTime") Date beginTime,
                                 @Param("endTime") Date endTime);

    List<TjOrder> getYCsTjOrderList(@Param("tjNum")String tjNum,
                                    @Param("compId")Long compId,
                                    @Param("beginTime") Date beginTime,
                                    @Param("endTime") Date endTime);


    List<TjOrder> getCsTjOrderList1(@Param("tjNum")String tjNum,
                                   @Param("checkStatus")Integer checkStatus,
                                   @Param("compId")Long compId,
                                   @Param("beginTime") Date beginTime,
                                   @Param("endTime") Date endTime);

    @Select("SELECT * FROM tj_order WHERE  deleted='0' and user_id = #{userId}  AND !ISNULL(finish_time)")
    List<TjOrder> getTjOrderListByCusId(Long userId);

    @Select("SELECT * FROM tj_order WHERE  deleted='0' and user_id = #{userId}  AND !ISNULL(finish_time)")
    List<TjOrder> getCsTjOrderListByCusId(Long userId);


    @Select("SELECT * FROM tj_order WHERE  deleted='0' and  user_id = #{userId}  AND !ISNULL(finish_time) " +
            "and !ISNULL(confirm_time) ")
    List<TjOrder> getCsTjOrderListByCusId1(Long userId);


    @Select("SELECT GROUP_CONCAT( DISTINCT b.pro_name  SEPARATOR ';' ) aa FROM tj_order_remark a" +
            " LEFT JOIN tj_project b ON a.pro_id=b.pro_id WHERE a.deleted=0 AND a.tj_number= #{tjNum} AND a.type !=2")
    String getHistoryTjOrderProByTjNum(String tjNum);


    @Select("SELECT * FROM tj_order WHERE  deleted='0' and user_id =#{userId}" +
            "  AND !ISNULL(finish_time) ORDER BY create_time DESC LIMIT 1")
    TjOrder getLastTjOrderListByCusId(Long userId);


    //存储过程专用接口(总检)
    List<List<?>> getCunChuGuoChengCustomerList(Map<String,Object> map);


    //存储过程专用接口(初审)
    List<List<?>> getCsCunChuGuoChengCustomerList(Map<String,Object> map);


    @Select("SELECT count(*) FROM tj_order a WHERE  a.deleted=0 and  a.card_id=#{cardId}")
    int selectTjOrderByCardId(String cardId);


    @Select("SELECT a.card_id FROM tj_order a WHERE a.deleted=0")
    List<String> selectTjOrderByCardId1();


    @Select("SELECT a.order_id FROM tj_order_detail a WHERE a.order_id=#{orderId} AND !ISNULL(a.flowing_water_id) GROUP BY a.order_id ")
    String  isPayByTjNum(String orderId);

    /**
     * 查询导检单详情
     * @param tjNumber 体检单号
     * @return
     */
    @Select("SELECT  @num :=  @num + 1 AS xh, d.dept_name ksmc, d.official_web ksdz, e.tjxm FROM sys_dept d JOIN ( select aa.ksmc,GROUP_CONCAT( DISTINCT aa.pro_name ORDER BY aa.pro_name DESC SEPARATOR ';' ) tjxm  from ( SELECT (case when f.ksmc in ('临检室','化验室','','') or c.pro_name like '%测定%' then '检验科'      when c.pro_name like '%心电图%' then '心电图室'  when f.ksmc='x光室' or c.pro_name like '%X线摄影%'  then '放射科'  when c.pro_name like '%色觉%' then '眼科' \t\t\t\twhen c.pro_name like '%超%' or c.pro_name like '%多普勒%' then 'B超室'  when (replace(f.ksmc,' ','')='' or f.ksmc is null) and c.dept_id<>'100' then  d.dept_name when c.pro_name like '%CT%' then 'CT室'  else '体检中心' end ) ksmc, c.pro_name FROM tj_order a JOIN tj_order_detail b ON b.order_id = a.order_id JOIN tj_project c ON c.pro_id = b.pro_id  left join his_mxfyxm e on e.mxfyxmbm=c.his_xmbm left join ltkj_his_dept f on f.ksbm = CONCAT('00',(case when e.hsks is null then '' else e.hsks end)) join sys_dept d on d.dept_id=c.dept_id WHERE a.tj_number =  #{tjNumber} ) aa  GROUP BY ksmc ) e ON e.ksmc = d.dept_name , (SELECT @num := 0) a WHERE d.parent_id = '100' ORDER BY xh")
    List<Map<String ,String >> selectDjdInfo(@Param("tjNumber")String tjNumber);

    /**
     * 查询导检单用户信息
     * @param tjNumber
     * @return
     */
    @Select("select c.tj_number tjh,a.cus_name tjname, b.dict_label sex,a.create_time tjsj,ifnull(d.cn_name,'') dwmc,a.cus_phone lxdh\n" +
            "from tj_order c \n" +
            " join tj_customer a on  a.cus_id= c.user_id \n" +
            " join (select dict_value,dict_label from  sys_dict_data where dict_type='sys_user_sex') b on b.dict_value = a.cus_sex\n" +
            " left join dict_comp d on d.drug_manufacturer_id = c.firm_id\n" +
            "where c.tj_number=#{tjNumber}")
    Map<String ,Object > selectDjdUserInfo(@Param("tjNumber")String tjNumber);


    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} AND a.type='检查' GROUP BY a.sqdh")
    List<String> getHuoQuJcsqdh(String cardId);
    @Delete("DELETE FROM ltkj_jianchajianyan_tree WHERE card_id=#{cardId} AND type='检查'")
    Boolean delHuoQuJcsqdh(String cardId);



    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} AND a.type='检验' GROUP BY a.sqdh")
    List<String> getHuoQuJysqdh(String cardId);



    @Delete("DELETE FROM ltkj_jianchajianyan_tree WHERE card_id=#{cardId} AND type='检验'")
    Boolean delHuoQuJysqdh(String cardId);

    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} AND a.type='处置' GROUP BY a.sqdh")
    List<String> getHuoQuZlsqdh(String cardId);
    @Delete("DELETE FROM ltkj_jianchajianyan_tree  WHERE card_id=#{cardId} AND type='处置'")
    Boolean delHuoQuZlsqdha(String cardId);


    List<Map<String, Object>> getTjTongJiJieKou(Map<String, Object> map);

    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} and a.jxbz=#{bldh} AND a.type='检查' GROUP BY a.sqdh")
    List<String> getHuoQuBlJcsqdh(@Param("cardId") String cardId,@Param("bldh") String bldh);
    @Delete("DELETE FROM ltkj_jianchajianyan_tree  WHERE card_id=#{cardId} and jxbz=#{bldh} AND type='检查'")
    Boolean delHuoQuBlJcsqdh(@Param("cardId")String cardId, @Param("bldh")String bldh);

    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} and a.jxbz=#{bldh} AND a.type='检验' GROUP BY a.sqdh")
    List<String> getHuoQuBlJysqdh(@Param("cardId")String cardId,@Param("bldh") String bldh);
    @Delete("DELETE FROM ltkj_jianchajianyan_tree  WHERE card_id=#{cardId} and jxbz=#{bldh}  AND type='检验'")
    Boolean delHuoQuBlJysqdh(@Param("cardId")String cardId,@Param("bldh") String bldh);

    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} and a.jxbz=#{bldh}  AND a.type='处置' GROUP BY a.sqdh")
    List<String> getHuoQuBlZlsqdh(@Param("cardId")String cardId,@Param("bldh") String bldh);
    @Delete("DELETE FROM ltkj_jianchajianyan_tree  WHERE card_id=#{cardId} and jxbz=#{bldh} AND type='处置'")
    Boolean delHuoQuZlsqdh(@Param("cardId")String cardId,@Param("bldh") String bldh);


    List<List<?>> getgetBghsList(Map<String,Object> map);


    @Select({"<script>","SELECT * FROM ltkj_jianchajianyan_tree a WHERE  a.card_id=#{cardId} AND a.jxbz IN ","<foreach collection='bldh' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>" })
    List<LtkjJianchajianyanTree> getBuLuXmXinXi(@Param("bldh") List<String> bldh,@Param("cardId")String cardId);

    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} and a.jxbz=#{bldh} AND a.type='检查' AND a.service = 'pacs' GROUP BY a.sqdh")
    List<String> getBlJcsqdhByPacsAndBldh(@Param("cardId") String cardId,@Param("bldh") String bldh);

    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} and a.jxbz=#{bldh} AND a.type='检验' AND a.service = 'lis' GROUP BY a.sqdh")
    List<String> getBlJysqdhByLisAndBldh(@Param("cardId") String cardId,@Param("bldh") String bldh);

    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} AND a.type='检查' AND a.service = 'pacs' GROUP BY a.sqdh")
    List<String> getBlJcsqdhByPacs(String cardId);

    @Select("SELECT a.sqdh FROM ltkj_jianchajianyan_tree a WHERE a.card_id=#{cardId} AND a.type='检验' AND a.service = 'lis' GROUP BY a.sqdh")
    List<String> getBlJysqdhByLis(String cardId);


    @Select("SELECT\n" +
            " DISTINCT a.tj_number \n" +
            "FROM\n" +
            "  tj_order_remark a\n" +
            "  JOIN sys_dept b ON b.dept_id = a.dept_id \n" +
            "WHERE\n" +
            "  a.deleted = 0 \n" +
            "  AND b.org_type = 1 \n" +
            "  AND a.type !=1")
    List<String> getDingShiTongBuJianYanTjNum();


    @Select("  SELECT\n" +
            " DISTINCT a.tj_number \n" +
            "FROM\n" +
            "  tj_order_remark a\n" +
            "  JOIN sys_dept b ON b.dept_id = a.dept_id \n" +
            "WHERE\n" +
            "  a.deleted = 0 \n" +
            "  AND b.org_type = 2 \n" +
            "  AND a.type !=1")
    List<String> getDingShiTongBuJianChaTjNum();
}