lift-server/lift-common/src/main/java/cn.com.ty.lift.common/constants/SqlConstants.java

package cn.com.ty.lift.common.constants;

public interface SqlConstants {
    //根据用户id和维保计划时间和状态查询用户那一天的各种类型维保任务(待保养/保养中/已完成/计划超期)
    String QUERY_MAINTENANCE_PLAN_EVERYDAY =
            "select         mr.id recordId," +
                    "       p.project_name projectName," +
                    "       l.registration_code registrationCode," +
                    "       l.device_position devicePosition," +
                    "       l.lift_type liftType," +
                    "       l.category category," +
                    "       ui.name workerName," +
                    "       mp.plan_date planDate," +
                    "       mr.recovery_date recoveryDate," +
                    "       mr.has_evaluate hasEvaluate" +
                    "          from maintenance_plan mp" +
                    "           left join lift l on mp.lift_id = l.id" +
                    "           left join user_info ui on mp.worker_id = ui.user_id" +
                    "           left join project p on mp.project_id = p.id" +
                    "           left join maintenance_record mr on mr.mt_plan_id = mp.id" +
                    "       where mp.plan_date = #{planDate}" +
                    "             and mp.status = #{status}" +
                    "             and mp.project_id in (select project_id from project_user where user_id = #{userId})";

    //查询法规超期维保列表。
    String QUERY_MAINTENANCE_PLAN_EVERYDAY_OVERDUE =
            "select         mp.status           planStatus," +
                    "       l.id                liftId," +
                    "       p.project_name      projectName," +
                    "       l.registration_code registrationCode," +
                    "       l.device_position   devicePosition," +
                    "       l.lift_type         liftType," +
                    "       l.category          category," +
                    "       ui.name             name" +
                    "   from maintenance_plan mp" +
                    "         left join lift l on mp.lift_id = l.id" +
                    "         left join user_info ui on mp.worker_id = ui.user_id" +
                    "         left join project p on mp.project_id = p.id" +
                    "   where plan_date = #{planDate}" +
                    "   and work_date is null" +
                    "   and TimeStampDiff(DAY, DATE_FORMAT(plan_date, '%Y%m%d'), DATE_FORMAT(CURDATE(), '%Y%m%d')) > 15" +
                    "   and mp.project_id in (select project_id from project_user where user_id = #{userId})";

    //扫码，查询电梯信息
    String QUERY_LIFT_INFO_BY_LIFT_ID =
            "select         l.id                id," +
                    "       l.registration_code registrationCode," +
                    "       l.device_position   devicePosition," +
                    "       p.project_name      projectName," +
                    "       if(l.lift_type = 1, '直梯', '扶梯')         liftType," +
                    "       ui.name             workerName," +
                    "       ui.mobile           workerMobile," +
                    "       p.project_status    projectStatus" +
                    "   from project_lift_relevance plr" +
                    "         left join lift l on plr.lift_id = l.id" +
                    "         left join project p on plr.project_id = p.id" +
                    "         left join user_info ui on plr.worker_id = ui.user_id" +
                    "   where l.id = #{liftId}";

    //扫码，查询维保记录列表
    String QUERY_MAINTENANCE_RECORD_BY_LIFT_ID =
            "select         mr.id                 recordId," +
                    "       mr.plan_date          planDate," +
                    "       mr.work_date          workDate," +
                    "       ui.name               workerName," +
                    "       mr.maintenance_advice maintenanceAdvice," +
                    "       e.advice              consumerAdvice," +
                    "       mr.status             status," +
                    "       mr.lift_id            liftId," +
                    "       mr.lift_type          liftType" +
                    "   from maintenance_record mr" +
                    "         left join user_info ui on mr.worker_id = ui.user_id" +
                    "         left join evaluation e on mr.id = e.record_id and e.source = '1'" +
                    "   where mr.lift_id = #{liftId}" +
                    "       order by mr.work_date desc";

    //物管。根据用户id和月份，查询那一月每天的(待保养/保养中/已完成/计划超期)的数量
    String QUERY_MAINTENANCE_PLAN_MONTH_TASK_NUM =
            "select     DATE_FORMAT(plan_date, '%d') day, status status, count(status) count" +
                    "   from maintenance_plan" +
                    "   where project_id in (select project_id from project_user where user_id = #{userId})" +
                    "       and DATE_FORMAT(plan_date, '%Y-%m') = #{yearMonth}" +
                    "       group by plan_date, status";

    //物管。动态消息，当日急修完成是否已经评价列表
    String QUERY_EMERGENCY_REPAIR_DYNAMIC_MESSAGE =
            "select     er.id id, l.device_position devicePosition, er.recovery_date recoveryDate, has_evaluate hasEvaluate" +
                    "    from emergency_repair er" +
                    "       left join lift l on er.lift_id = l.id" +
                    "    where DATE_FORMAT(er.recovery_date, '%Y%m%d') = DATE_FORMAT(CURDATE(), '%Y%m%d')" +
                    "       and project_id in (select project_id from project_user where user_id = #{userId})";

    //物管。动态消息，当日维保完成是否已经评价列表
    String QUERY_MAINTENANCE_SUCCESS_DYNAMIC_MESSAGE =
            "select     mr.id id,l.device_position devicePosition, has_evaluate hasEvaluate" +
                    "    from maintenance_record mr" +
                    "        left join lift l on mr.lift_id = l.id" +
                    "    where DATE_FORMAT(mr.recovery_date, '%Y%m%d')  = DATE_FORMAT(CURDATE(), '%Y%m%d')" +
                    "      and project_id in (select project_id from project_user where user_id = #{userId})";

    //物管。根据用户Id查询加入的项目列表
    String QUERY_JOIN_PROJECT_LIST =
            "select     id projectId, project_name projectName, mt_company_id mtCompanyId" +
                    "    from project" +
                    "   where id in (select project_id from project_user where user_id = #{userId})";

    //物管。查询急修/维保id是否有评价权限
    String QUERY_EVALUATE_AUTH =
            "select     temp.id id, pu.user_role userRole" +
                    "   from ${table} temp" +
                    "         left join project_user pu on pu.project_id = temp.project_id" +
                    "   where user_role in (21, 22)" +
                    "  and user_id =#{userId}";

    //物管。查询用户加入的项目的急修列表
    String QUERY_REPAIR_LIST =
            "select                  distinct er.id               repairId," +
                    "                p.project_name      projectName," +
                    "                er.is_critical      isCritical," +
                    "                er.repair_reason    repairReason," +
                    "                er.source           source," +
                    "                er.caller_date      callerDate," +
                    "                l.registration_code registrationCode," +
                    "                l.lift_type         liftType," +
                    "                l.category          category," +
                    "                l.device_position   devicePosition," +
                    "                ui.name             workerName," +
                    "                TimeStampDiff(SECOND ,er.assign_time,ifnull(er.taking_time, now())) * 1000 AS waitDiff," +
                    "                er.create_date" +
                    "    from emergency_repair er" +
                    "         left join project_user pu on pu.project_id = er.project_id" +
                    "         left join project p on er.project_id = p.id" +
                    "         left join lift l on er.lift_id = l.id" +
                    "         left join project_lift_relevance plr on er.lift_id = plr.lift_id" +
                    "         left join user_info ui on er.worker_id = ui.user_id" +
                    "    where er.status = #{status}" +
                    "            and er.project_id in (select er.project_id" +
                    "                                  from project_user pu" +
                    "                                  where pu.user_id = #{userId}) order by er.create_date desc";

    //根据区域名和区域主管名，模糊查询区域列表信息
    String QUERY_REGIONS =
            "select r.area_name     areaName" +
                    "     , r.area_code     areaCode" +
                    "     , r.city_code     cityCode" +
                    "     , r.remark        remark" +
                    "     , r.province_code provinceCode" +
                    "     , r.clerk         clerk" +
                    "     , r.mt_company_id mtCompanyId" +
                    "     , ui.name         name" +
                    "     , r.id            id" +
                    "   from region r" +
                    "         left join user_info ui on r.user_id = ui.user_id" +
                    "     where mt_company_id = #{mtCompanyId}" +
                    "       and (r.area_name like '%${name}%' or ui.name like '%${name}%')" +
                    "   order by create_time desc";

    //根据用户id查询待维保数量
    String QUERY_MAINTENANCE_COUNT_BY_USER_ID =
            "select     count(1)" +
                    "   from maintenance_plan" +
                    "   where plan_date = CURDATE()" +
                    "  and project_id in (select project_id from project_user where user_id = #{userId})";

    String QUERY_REPAIR_COUNT_BY_USER_ID =
            "select count(1)" +
                    "   from emergency_repair" +
                    "   where (DATE_FORMAT(caller_date, '%Y%m%d') = CURDATE() or DATE_FORMAT(create_date, '%Y%m%d') = CURDATE())" +
                    "  and project_id in (select project_id from project_user where user_id = #{userId})";
}