package com.rongwei.sfcommon.sys.dao;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.rongwe.scentity.domian.PointCheckDo;
import com.rongwe.scentity.vo.PointCheckItemVo;
import com.rongwe.scentity.vo.PointCheckVo;
import com.rongwei.rwcommon.base.BaseDao;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

/**
 * 点检任务表
 *
 * @author chglee
 * @email 1992lcg@163.com
 * @date 2020-07-17 09:33:23
 */
public interface PointCheckDao extends BaseDao<PointCheckDo> {

    @Select("select DISTINCT a.*,e.NAME 'USERNAME' from sys_user_role a left join sys_user_org b on a.USERID=b.USERID join \n" +
            "(select * from sys_user_org where USERID=#{userId}) c on c.ORGID=b.ORGID \n" +
            "left join sys_user e on e.ID=a.USERID " +
            "left join sys_role d on d.ID=a.ROLEID where d.CODE='gdzg' and d.DELETED='0' and a.DELETED='0' and b.DELETED='0'")
    List<Map<String, Object>> getGdzgByUserId(@Param("userId") String userId);

    @Select("select DISTINCT b.USERID from sys_role a left join sys_user_role b on a.ID=b.ROLEID left join sys_user_org c on b.USERID=c.USERID\n" +
            "where a.CODE='gdzg' and c.ORGID=#{orgId} and a.DELETED='0' and b.DELETED='0' and c.DELETED='0'")
    List<Map<String, Object>> getGdzgByOrgId(@Param("orgId") String orgId);

    @Select("<script>select * from (select a.*,b.CHECKITEMNAME,b.CHECKITEMCODE,b.CHECKITEMTYPE,c.NAME AS CHECKITEMTYPENAME, \n" +
            " b.CHARGEORGID,b.CHARGEORGNAME,b.CHARGEORGPID,b.CHARGEORGPNAME,d.NAME AS SHIFTNAME \n" +
            " from asp_point_check a left join asp_check_items b on a.CHECKITEMID=b.ID \n" +
            " left join sys_dict c on c.DICTTYPE='asp_check_item_type' \n" +
            " left join sys_dict d on d.DICTTYPE='check-shift'  \n" +
            " where a.DELETED='0' and b.DELETED='0' and d.VALUE = a.SHIFT and c.VALUE = b.CHECKITEMTYPE and a.CHECKITEMID is not null \n" +
            " and EXISTS (select ID from asp_check_items_dutyuser where CHECKITEMID = b.ID and DUTYUSERID = #{checkuserid} and DELETED='0' and DUTYUSERATTRIBUTE='production') \n" +
            "<if test='checkstatus!=null'>" +
            " and a.CHECKSTATUS = #{checkstatus} " +
            "</if> " +
            "<if test='shift!=null'>" +
            " and a.SHIFT = #{shift} " +
            "</if> " +
            "<if test='checkitemid!=null'>" +
            " and a.CHECKITEMID = #{checkitemid} " +
            "</if> " +
            " union \n" +
            " select a.*,b.CHECKITEMNAME,b.CHECKITEMCODE,b.CHECKITEMTYPE,c.NAME AS CHECKITEMTYPENAME, \n" +
            " b.CHARGEORGID,b.CHARGEORGNAME,b.CHARGEORGPID,b.CHARGEORGPNAME,d.NAME AS SHIFTNAME \n" +
            " from asp_point_check a left join asp_check_items b on a.CHECKITEMID=b.ID \n" +
            " left join sys_dict c on c.DICTTYPE='asp_check_item_type' \n" +
            " left join sys_dict d on d.DICTTYPE='check-shift'  \n" +
            " where a.DELETED='0' and b.DELETED='0' and d.VALUE = a.SHIFT and c.VALUE = b.CHECKITEMTYPE  and a.CHECKITEMID is not null " +
            " and not EXISTS (select ID from asp_check_items_dutyuser where CHECKITEMID = b.ID and DUTYUSERID = #{checkuserid} and DELETED='0' and DUTYUSERATTRIBUTE='production')\n" +
            " and EXISTS (select ID from sys_user_org where ORGID = b.CHARGEORGCID and USERID = #{checkuserid} and DELETED='0') " +
            "<if test='checkstatus!=null'>" +
            "and a.CHECKSTATUS = #{checkstatus} " +
            "</if> " +
            "<if test='shift!=null'>" +
            " and a.SHIFT = #{shift} " +
            "</if> " +
            "<if test='checkitemid!=null'>" +
            " and a.CHECKITEMID = #{checkitemid} " +
            "</if> " +
            ") tb " +
            " order by tb.CREATEDATE desc,tb.SHIFT asc" +
            "</script>")
    IPage<PointCheckVo> pointCheckSUlist(Page page, @Param("checkstatus") String checkstatus, @Param("checkuserid") String checkuserid, @Param("shift") String shift, @Param("checkitemid") String checkitemid);

    @Select("<script>select a.*,b.CHECKITEMNAME,b.CHECKITEMCODE,b.CHECKITEMTYPE,c.NAME AS CHECKITEMTYPENAME, \n" +
            " b.CHARGEORGID,b.CHARGEORGNAME,b.CHARGEORGPID,b.CHARGEORGPNAME,d.NAME AS SHIFTNAME \n" +
            " from asp_point_check a left join asp_check_items b on a.CHECKITEMID=b.ID \n" +
            " left join sys_dict c on c.DICTTYPE='asp_check_item_type' \n" +
            " left join sys_dict d on d.DICTTYPE='check-shift'  \n" +
            " left join (select USERID,GROUP_CONCAT(ORGID) AS 'CHECKUSERORGID' from sys_user_org where DELETED='0' GROUP BY USERID) e on e.USERID=a.CHECKUSERID " +
            " where a.DELETED='0' and b.DELETED='0' and d.VALUE = a.SHIFT and c.VALUE = b.CHECKITEMTYPE \n" +
            "<if test='checkuserid!=null'>" +
            " and a.CHECKUSERID=#{checkuserid} " +
            "</if> " +
            "<if test='checkuserorgid!=null'>" +
            " and e.CHECKUSERORGID like '%${checkuserorgid}%' " +
            "</if> " +
            "<if test='checkstatus!=null'>" +
            " and a.CHECKSTATUS = #{checkstatus} " +
            "</if> " +

            "<if test='checkitemname!=null'>" +
            " and b.CHECKITEMNAME like '%${checkitemname}%' " +
            "</if> " +
            "<if test='checkitemtypename!=null'>" +
            " and c.NAME like '%${checkitemtypename}%' " +
            "</if> " +
            "<if test='shift!=null'>" +
            " and a.SHIFT = #{shift} " +
            "</if> " +
            "<if test='checktime!=null'>" +
            " and a.CHECKTIME = #{checktime} " +
            "</if> " +
            "<if test='checkitemid!=null'>" +
            " and a.CHECKITEMID = #{checkitemid} " +
            "</if> " +

            " order by a.CREATEDATE desc,a.SHIFT asc" +
            "</script>")
    IPage<PointCheckVo> pointHasCheckSUlist(Page page, @Param("checkstatus") String checkstatus, @Param("checkuserid") String checkuserid, @Param("checkuserorgid") String checkuserorgid
            , @Param("checkitemtypename") String checkitemtypename, @Param("checkitemname") String checkitemname, @Param("shift") String shift, @Param("checktime") String checktime, @Param("checkitemid") String checkitemid);

    @Select("<script>select a.*,b.CHECKITEMNAME,b.CHECKITEMCODE,b.CHECKITEMTYPE,c.NAME AS CHECKITEMTYPENAME," +
            "b.CHARGEORGID,b.CHARGEORGNAME,b.CHARGEORGPID,b.CHARGEORGPNAME,d.NAME AS SHIFTNAME " +
            "from asp_point_check a left join asp_check_items b on a.CHECKITEMID=b.ID \n" +
            "left join sys_dict c on c.DICTTYPE='asp_check_item_type' " +
            "left join sys_dict d on d.DICTTYPE='check-shift' " +
            "where a.DELETED='0' and b.DELETED='0' and c.VALUE = b.CHECKITEMTYPE and d.VALUE = a.SHIFT " +
            "<if test='id!=null'>" +
            "and a.ID = #{id}" +
            "</if>" +
            "</script>")
    PointCheckVo pointCheckSUInfo(@Param("id") String id);

    @Select("select a.*,b.NAME AS MANAGEMENTNAME from asp_point_check_item a " +
            "left join sys_dict b on a.MANAGEMENT=b.VALUE and b.DICTTYPE='point_check_manage'" +
            "where a.POINTCHECKID = #{pointcheckid}")
    List<PointCheckItemVo> pointCheckItemList(@Param("pointcheckid") String pointcheckid);

    @Select("select a.*,b.CHECKITEMNAME,b.CHECKITEMCODE,b.CHECKITEMTYPE,b.CHECKITEMAREAID,b.CHARGEORGID,b.CHARGEORGNAME,b.CHARGEORGPID,b.CHARGEORGPNAME,c.NAME AS SHIFTNAME \n" +
            "from asp_point_check a left join asp_check_items b on a.CHECKITEMID=b.id left join sys_dict c on a.SHIFT = c.VALUE " +
            "where a.DELETED='0' and c.DICTTYPE='check-shift' and DATE_FORMAT(a.CREATEDATE,'%Y-%m-%d') < DATE_FORMAT(NOW(),'%Y-%m-%d')  and a.CHECKSTATUS='10'")
    List<PointCheckVo> getDelays();

    @Select("select * from asp_point_check where CHECKITEMID=#{checkitemid} and CHECKSTATUS='20' and SHIFT !=#{shift} ORDER BY CHECKTIME DESC")
    List<PointCheckDo> getLastPointChecks(@Param("checkitemid") String checkitemid, @Param("shift") String shift);

    @Select("<script>select " +
            "a.*," +
            "b.CHECKITEMNAME, " +
            "b.CHECKITEMCODE, " +
            "b.CHECKITEMTYPE, " +
            "b.CHECKITEMAREAID, " +
            "b.CHARGEORGID, " +
            "b.CHARGEORGNAME, " +
            "b.CHARGEORGPID, " +
            "b.CHARGEORGPNAME, " +
            "b.CHARGEORGCID, " +
            "b.CHARGEORGCNAME " +
            "from " +
            "asp_point_check a " +
            "left join " +
            "asp_check_items b " +
            "on a.CHECKITEMID=b.id " +
            " left join (select USERID,GROUP_CONCAT(ORGID) AS 'CHECKUSERORGID' from sys_user_org where DELETED='0' GROUP BY USERID) e on e.USERID=a.CHECKUSERID " +
            "where a.DELETED = '0' and CHECKSTATUS = '20' and CHECKWARNSTATUS='20' " +
            "<if test='checkuserid!=null'>" +
            " and a.CHECKUSERID=#{checkuserid} " +
            "</if> " +
            "<if test='checkuserorgid!=null'>" +
            " and (e.CHECKUSERORGID like '%${checkuserorgid}%' or b.CHARGEORGID like '%${checkuserorgid}%') " +
            "</if> " +
            "<if test='beginDate!=null'>" +
            "and a.CREATEDATE <![CDATA[ >= ]]> #{beginDate}" +
            "</if>" +
            "<if test='endDate!=null'>" +
            "and a.CREATEDATE <![CDATA[ <= ]]> #{endDate}" +
            "</if>" +
            "<if test='checkitemid!=null'>" +
            " and a.CHECKITEMID = #{checkitemid} " +
            "</if> " +
            "order by a.CREATEDATE desc,a.SHIFT asc " +
            "</script>")
    IPage<PointCheckVo> notPointCheckList(Page page, @Param("beginDate") String beginDate, @Param("endDate") String endDate
            , @Param("checkuserid") String checkuserid, @Param("checkuserorgid") String checkuserorgid, @Param("checkitemid") String checkitemid);




    @Select("SELECT\n" +
            "\ta.*,\n" +
            "\tb.CHECKITEMNAME,\n" +
            "\tb.CHECKITEMCODE,\n" +
            "\tb.CHECKITEMTYPE,\n" +
            "\tc.NAME AS CHECKITEMTYPENAME,\n" +
            "\tb.CHARGEORGID,\n" +
            "\tb.CHARGEORGNAME,\n" +
            "\tb.CHARGEORGPID,\n" +
            "\tb.CHARGEORGPNAME,\n" +
            "\td.NAME AS SHIFTNAME \n" +
            "FROM\n" +
            "\tasp_point_check a\n" +
            "\tLEFT JOIN asp_check_items b ON a.CHECKITEMID = b.ID\n" +
            "\tLEFT JOIN sys_dict c ON c.DICTTYPE = 'asp_check_item_type'\n" +
            "\tLEFT JOIN sys_dict d ON d.DICTTYPE = 'check-shift' \n" +
            "WHERE\n" +
            "\ta.DELETED = '0' \n" +
            "\tAND b.DELETED = '0' \n" +
            "\tAND d.VALUE = a.SHIFT \n" +
            "\tAND c.VALUE = b.CHECKITEMTYPE \n" +
            "\tAND a.CHECKSTATUS = #{checkstatus} \n" +
            "\tAND a.CHECKITEMID = #{checkitemid}\n" +
            "\tORDER BY CREATEDATE DESC")
    IPage<PointCheckVo> sparePointHasCheckSUlist(Page page, @Param("checkstatus") String checkstatus,  @Param("checkitemid") String checkitemid);


    @Select("SELECT\n" +
            "\ta.*,\n" +
            "\tb.CHECKITEMNAME,\n" +
            "\tb.CHECKITEMCODE,\n" +
            "\tb.CHECKITEMTYPE,\n" +
            "\tc.NAME AS CHECKITEMTYPENAME,\n" +
            "\tb.CHARGEORGID,\n" +
            "\tb.CHARGEORGNAME,\n" +
            "\tb.CHARGEORGPID,\n" +
            "\tb.CHARGEORGPNAME,\n" +
            "\td.NAME AS SHIFTNAME \n" +
            "FROM\n" +
            "\tasp_point_check a\n" +
            "\tLEFT JOIN asp_check_items b ON a.CHECKITEMID = b.ID\n" +
            "\tLEFT JOIN sys_dict c ON c.DICTTYPE = 'asp_check_item_type'\n" +
            "\tLEFT JOIN sys_dict d ON d.DICTTYPE = 'check-shift' \n" +
            "WHERE\n" +
            "\ta.DELETED = '0' \n" +
            "\tAND b.DELETED = '0' \n" +
            "\tAND d.VALUE = a.SHIFT \n" +
            "\tAND c.VALUE = b.CHECKITEMTYPE \n" +
            "\tAND a.CHECKITEMID IS NOT NULL \n" +
            "\tAND a.CHECKSTATUS = #{checkstatus} \n" +
            "\tAND a.SHIFT = #{shift} \n" +
            "\tAND a.CHECKITEMID = #{checkitemid}")
    IPage<PointCheckVo> sparePointCheckSUlist(Page page, @Param("checkstatus") String checkstatus, @Param("shift") String shift, @Param("checkitemid") String checkitemid);


    @Select("<script>select " +
            "a.*," +
            "b.CHECKITEMNAME, " +
            "b.CHECKITEMCODE, " +
            "b.CHECKITEMTYPE, " +
            "b.CHECKITEMAREAID, " +
            "b.CHARGEORGID, " +
            "b.CHARGEORGNAME, " +
            "b.CHARGEORGPID, " +
            "b.CHARGEORGPNAME, " +
            "b.CHARGEORGCID, " +
            "b.CHARGEORGCNAME " +
            "from " +
            "asp_point_check a " +
            "left join " +
            "asp_check_items b " +
            "on a.CHECKITEMID=b.id " +
            "where a.DELETED = '0' and CHECKSTATUS = '20' and CHECKWARNSTATUS='20' " +
            "<if test='beginDate!=null'>" +
            "and a.CREATEDATE <![CDATA[ >= ]]> #{beginDate}" +
            "</if>" +
            "<if test='endDate!=null'>" +
            "and a.CREATEDATE <![CDATA[ <= ]]> #{endDate}" +
            "</if>" +
            "<if test='checkitemid!=null'>" +
            " and a.CHECKITEMID = #{checkitemid} " +
            "</if> " +
            "order by a.CREATEDATE desc,a.SHIFT asc " +
            "</script>")
    IPage<PointCheckVo> spareNotPointCheckList(Page<PointCheckVo> page, @Param("beginDate") String beginDate, @Param("endDate") String endDate, @Param("checkitemid") String checkitemid);
}