package com.yeejoin.amos.api.openapi.face.orm.dao;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yeejoin.amos.api.openapi.face.orm.entity.MidEquipRegistrationInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

/**
 * 特种设备基本信息-注册登记信息  Mapper 接口
 *
 * @author Zhang Yingbin
 * @date 2022-07-19
 */
public interface MidEquipRegistrationInfoMapper extends BaseMapper<MidEquipRegistrationInfo> {

    @DS("tzs")
    @Select("<script>" +
            "SELECT DISTINCT sequenceCode FROM " +
            "(SELECT concat ( fi.\"PRODUCE_UNIT_CREDIT_CODE\", '-', fi.\"FACTORY_NUM\" ) sequenceCode " +
            "FROM idx_biz_jg_factory_info fi, idx_biz_jg_other_info oi " +
            "WHERE oi.\"RECORD\" = fi.\"RECORD\" " +
            " AND oi.\"SUPERVISORY_CODE\" IS NOT NULL " +
            " AND oi.\"CLAIM_STATUS\" = '已认领' " +
            " AND fi.\"PRODUCE_UNIT_CREDIT_CODE\" IS NOT NULL " +
            " AND fi.\"FACTORY_NUM\" IS NOT NULL) WHERE sequenceCode in " +
            "<foreach item='item' index='index' collection='sequenceCodes' open='(' separator=',' close=')'>" +
            "  #{item}" +
            "</foreach>" +
            "</script>")
    List<String> getUsedCountBySeqCodes(@Param("sequenceCodes") Set<String> sequenceCodes);

    @DS("tzs")
    @Select("<script>" +
            "SELECT DISTINCT" +
            "  sequenceCode," +
            "  record," +
            "  unitName," +
            "  factoryNum," +
            "  cylinderVariety," +
            "  cylinderVarietyName," +
            "  unitInnerCode," +
            "  informationManageCode," +
            "  appId," +
            "  creditCode," +
            "  orgBranchCode," +
            "  fillingUnitName," +
            "  regionCode " +
            "FROM" +
            "  (" +
            "  SELECT" +
            "    CONCAT ( fi.\"PRODUCE_UNIT_CREDIT_CODE\", '-', fi.\"FACTORY_NUM\" ) AS sequenceCode," +
            "    fi.\"RECORD\" AS record," +
            "    ui.\"ESTATE_UNIT_NAME\" AS unitName," +
            "    ui.\"USE_UNIT_NAME\" AS fillingUnitName," +
            "    fi.\"FACTORY_NUM\" AS factoryNum," +
            "    ri.\"EQU_DEFINE\" AS cylinderVariety," +
            "    ( SELECT NAME FROM tz_equipment_category WHERE code = ri.\"EQU_DEFINE\" ) AS cylinderVarietyName," +
            "    ui.\"USE_INNER_CODE\" AS unitInnerCode," +
            "    oi.\"INFORMATION_MANAGE_CODE\" AS informationManageCode," +
            "    ( SELECT app_id FROM tz_base_enterprise_info WHERE use_code = ( SELECT \"USE_UNIT_CREDIT_CODE\" FROM idx_biz_jg_use_info WHERE \"RECORD\" = fi.\"RECORD\" ) ) AS appId," +
            "    ui.\"USE_UNIT_CREDIT_CODE\" AS creditCode," +
            "    si.\"ORG_BRANCH_CODE\" AS orgBranchCode," +
            "    ( SELECT ui.\"PROVINCE\" || '#' || ui.\"CITY\" || '#' || ui.\"COUNTY\" ) AS regionCode " +
            "  FROM" +
            "    idx_biz_jg_factory_info fi" +
            "    JOIN idx_biz_jg_other_info oi ON oi.\"RECORD\" = fi.\"RECORD\"" +
            "    JOIN idx_biz_jg_use_info ui ON ui.\"RECORD\" = fi.\"RECORD\"" +
            "    JOIN idx_biz_jg_register_info ri ON ri.\"RECORD\" = fi.\"RECORD\" " +
            "    JOIN idx_biz_jg_supervision_info si ON si.\"RECORD\" = fi.\"RECORD\" " +
            "  WHERE" +
            "    oi.\"CLAIM_STATUS\" = '已认领' " +
            "    AND fi.\"PRODUCE_UNIT_CREDIT_CODE\" IS NOT NULL " +
            "    AND fi.\"FACTORY_NUM\" IS NOT NULL " +
            "  ) subquery " +
            "WHERE sequenceCode = #{sequenceCode} " +
            "LIMIT 1" +
            "</script>")
    Map<String, Object> getCylUseInfoBySeqCode(@Param("sequenceCode") String sequenceCode);
}
