<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="go.kr.project.carInspectionPenalty.history.mapper.VehicleApiHistoryMapper">

    <!-- ==================== 자동차 기본정보 조회 이력 ==================== -->

    <!-- 자동차 기본정보 조회 이력 목록 조회 -->
    <select id="selectCarBassMatterInqireList" parameterType="VehicleApiHistorySearchVO" resultType="CarBassMatterInqireVO">
        SELECT
            t.CAR_BASS_MATTER_INQIRE_ID,
            t.INFO_SYS_ID,
            t.INFO_SYS_IP_ADDR,
            t.SGG_CD,
            t.LINK_INFO_CD,
            t.PIC_ID,
            t.PIC_IP_ADDR,
            t.PIC_NM,
            t.DMND_LEVY_CRTR_YMD,
            t.DMND_INQ_SE_CD,
            t.DMND_VHRNO,
            t.DMND_VIN,
            t.LINK_RSLT_CD,
            t.LINK_RSLT_DTL,
            t.YRIDNW,
            t.REG_YMD,
            t.ERSR_REG_SE_CD,
            t.ERSR_REG_SE_NM,
            t.ERSR_REG_YMD,
            t.REG_DTL_CD,
            t.DSPLVL,
            t.USGSRHLD_STDG_CD,
            t.USGSRHLD_DONG_CD,
            t.USGSRHLD_MTN_YN,
            t.USGSRHLD_LNBR,
            t.USGSRHLD_HO,
            t.USGSRHLD_ADDR_NM,
            t.USGSRHLD_ROAD_NM_CD,
            t.USGSRHLD_UDGD_BLDG_SE_CD,
            t.USGSRHLD_BMNO,
            t.USGSRHLD_BSNO,
            t.USGSRHLD_WHOL_ADDR,
            t.RPRS_OWNR_MBR_SE_CD,
            t.RPRS_OWNR_NM,
            t.RPRSV_OWNR_IDECNO,
            t.RPRS_OWNR_TELNO,
            t.OWNR_STDG_CD,
            t.OWNR_DONG_CD,
            t.OWNR_MTN_YN,
            t.OWNR_LNBR,
            t.OWNR_HO,
            t.OWNR_ADDR_NM,
            t.OWNR_ROAD_NM_CD,
            t.OWNR_UDGD_BLDG_SE_CD,
            t.OWNR_BMNO,
            t.OWNR_BSNO,
            t.OWNR_WHOL_ADDR,
            t.REAR_VHRNO,
            t.USE_FUEL_CD,
            t.USG_SE_CD,
            t.MTRS_FOM_NM,
            t.BFR_VHRNO,
            t.VHRNO,
            t.VIN,
            t.ATMB_NM,
            t.VHCL_TOTL_WT,
            t.VEAG_END_YMD,
            t.CHG_YMD,
            t.CARMDL_ASORT_CD,
            t.CARMDL_TYPE_CD,
            t.CARMDL_SE_CD,
            t.MXMM_LDG,
            t.CARMDL_ASORT_NM,
            t.CARMDL_TYPE_NM,
            t.CARMDL_CLSF_NM,
            t.FRST_REG_YMD,
            t.FOM_NM,
            t.ACQS_YMD,
            t.ACQS_END_YMD,
            t.FBCTN_YMD,
            t.TRANSR_REG_YMD,
            t.SPCF_REG_STTS_CD,
            t.COLOR_NM,
            t.MRTG_CNT,
            t.SZR_CNT,
            t.STRCT_CHG_CNT,
            t.NOPLT_CSDY_YN,
            t.NOPLT_CSDY_AVTSMT_YMD,
            t.SRC_SE_CD,
            t.NOPLT_SPCFCT_CD,
            t.ACQS_AMT,
            t.INSP_VLD_PD_BGNG_YMD,
            t.INSP_VLD_PD_END_YMD,
            t.USGSRHLD_GRC_CD,
            t.RDCPCT_CNT,
            t.SPMNNO,
            t.DRVNG_DSTNC,
            t.FRST_REG_APLY_RCPT_NO,
            t.VLNT_ERSR_PRVNTC_AVTSMT_YMD,
            t.OGNZ_NM,
            t.PRCS_IMPRTY_RSN_CD,
            t.PRCS_IMPRTY_RSN_DTLS,
            t.CBD_LT,
            t.CBD_BT,
            t.CBD_HG,
            t.FRST_MXMM_LDG,
            t.FUEL_CNSMPRT,
            t.ELCTY_CMPND_FUEL_CNSMPRT,
            t.REG_DT,
            t.RGTR,
            u.USER_NM AS rgtrNm
        FROM tb_car_bass_matter_inqire t
        LEFT JOIN tb_user u ON t.RGTR = u.USER_ID
        WHERE 1=1
        <if test='searchStartDate != null and searchStartDate != ""'>
            AND DATE_FORMAT(t.REG_DT, '%Y%m%d') <![CDATA[>=]]> #{searchStartDate}
        </if>
        <if test='searchEndDate != null and searchEndDate != ""'>
            AND DATE_FORMAT(t.REG_DT, '%Y%m%d') <![CDATA[<=]]> #{searchEndDate}
        </if>
        <if test='searchVhrno != null and searchVhrno != ""'>
            AND (t.DMND_VHRNO LIKE CONCAT('%', #{searchVhrno}, '%') OR t.VHRNO LIKE CONCAT('%', #{searchVhrno}, '%'))
        </if>
        <if test='searchVin != null and searchVin != ""'>
            AND (t.DMND_VIN LIKE CONCAT('%', #{searchVin}, '%') OR t.VIN LIKE CONCAT('%', #{searchVin}, '%'))
        </if>
        <if test='searchLinkRsltCd != null and searchLinkRsltCd != ""'>
            AND t.LINK_RSLT_CD = #{searchLinkRsltCd}
        </if>
        <if test='searchCarFfnlgTrgtId != null and searchCarFfnlgTrgtId != ""'>
            AND t.CAR_FFNLG_TRGT_ID = #{searchCarFfnlgTrgtId}
        </if>
        ORDER BY t.CAR_BASS_MATTER_INQIRE_ID DESC
        <if test='pagingYn == "Y"'>
            LIMIT #{startIndex}, #{perPage}
        </if>
    </select>

    <!-- 자동차 기본정보 조회 이력 총 개수 조회 -->
    <select id="selectCarBassMatterInqireListTotalCount" parameterType="VehicleApiHistorySearchVO" resultType="int">
        SELECT COUNT(*)
        FROM tb_car_bass_matter_inqire t
        LEFT JOIN tb_user u ON t.RGTR = u.USER_ID
        WHERE 1=1
        <if test='searchStartDate != null and searchStartDate != ""'>
            AND DATE_FORMAT(t.REG_DT, '%Y%m%d') <![CDATA[>=]]> #{searchStartDate}
        </if>
        <if test='searchEndDate != null and searchEndDate != ""'>
            AND DATE_FORMAT(t.REG_DT, '%Y%m%d') <![CDATA[<=]]> #{searchEndDate}
        </if>
        <if test='searchVhrno != null and searchVhrno != ""'>
            AND (t.DMND_VHRNO LIKE CONCAT('%', #{searchVhrno}, '%') OR t.VHRNO LIKE CONCAT('%', #{searchVhrno}, '%'))
        </if>
        <if test='searchVin != null and searchVin != ""'>
            AND (t.DMND_VIN LIKE CONCAT('%', #{searchVin}, '%') OR t.VIN LIKE CONCAT('%', #{searchVin}, '%'))
        </if>
        <if test='searchLinkRsltCd != null and searchLinkRsltCd != ""'>
            AND t.LINK_RSLT_CD = #{searchLinkRsltCd}
        </if>
        <if test='searchCarFfnlgTrgtId != null and searchCarFfnlgTrgtId != ""'>
            AND t.CAR_FFNLG_TRGT_ID = #{searchCarFfnlgTrgtId}
        </if>
    </select>

    <!-- 자동차 기본정보 조회 이력 상세 조회 -->
    <select id="selectCarBassMatterInqireOne" parameterType="String" resultType="CarBassMatterInqireVO">
        SELECT *
        FROM tb_car_bass_matter_inqire
        WHERE CAR_BASS_MATTER_INQIRE_ID = #{carBassMatterInqireId}
    </select>

    <!-- ==================== 자동차 등록원부(갑) 조회 이력 ==================== -->

    <!-- 자동차 등록원부(갑) 조회 이력 목록 조회 -->
    <select id="selectCarLedgerFrmbkList" parameterType="VehicleApiHistorySearchVO" resultType="CarLedgerFrmbkVO">
        SELECT
            t.CAR_LEDGER_FRMBK_ID,
            t.INFO_SYS_ID,
            t.INFO_SYS_IP_ADDR,
            t.SGG_CD,
            t.LINK_INFO_CD,
            t.PIC_ID,
            t.PIC_IP_ADDR,
            t.PIC_NM,
            t.DMND_VHRNO,
            t.DMND_PRVC_RLS,
            t.DMND_CVLPR_NM,
            t.DMND_CVLPR_IDECNO,
            t.DMND_CVLPR_STDG_CD,
            t.DMND_PATH_SE_CD,
            t.DMND_DSCTN_INDCT,
            t.DMND_INQ_SE_CD,
            t.LINK_RSLT_CD,
            t.LINK_RSLT_DTL,
            t.LEDGER_GROUP_NO,
            t.LEDGER_INDIV_NO,
            t.VHMNO,
            t.VHRNO,
            t.VIN,
            t.CARMDL_ASORT_CD,
            t.CARMDL_ASORT_NM,
            t.ATMB_NM,
            t.COLOR_CD,
            t.COLOR_NM,
            t.NOPLT_SPCFCT_CD,
            t.NOPLT_SPCFCT_NM,
            t.USG_SE_CD,
            t.USG_SE_NM,
            t.MTRS_FOM_NM,
            t.FOM_NM,
            t.ACQS_AMT,
            t.REG_DTL_CD,
            t.REG_DTL_NM,
            t.FRST_REG_YMD,
            t.VEAG_END_YMD,
            t.YRIDNW,
            t.SPMNNO_1,
            t.SPMNNO_2,
            t.FBCTN_YMD,
            t.DRVNG_DSTNC,
            t.INSP_VLD_PD_BGNG_YMD,
            t.INSP_VLD_PD_END_YMD,
            t.CHCK_VLD_PD_BGNG_YMD,
            t.CHCK_VLD_PD_END_YMD,
            t.REG_APLY_SE_NM,
            t.FRST_REG_APLY_RCPT_NO,
            t.NOPLT_CSDY_AVTSMT_YMD,
            t.NOPLT_CSDY_YN,
            t.BSS_USE_PD_YMD,
            t.OCTHT_ERSR_PRVNTC_AVTSMT_YMD,
            t.ERSR_REG_YMD,
            t.ERSR_REG_SE_CD,
            t.ERSR_REG_SE_NM,
            t.MRTG_CNT,
            t.SZR_CNT,
            t.STRCT_CHG_CNT,
            t.USGSRHLD_ADDR_1,
            t.USGSRHLD_ADDR_DTL_1,
            t.OWNR_ADDR,
            t.OWNR_ADDR_DTL,
            t.INDVDL_BZMN_YN,
            t.RPRS_OWNR_TELNO,
            t.RPRS_OWNR_NM,
            t.RPRS_OWNR_MBR_SE_CD,
            t.RPRSV_OWNR_IDECNO,
            t.TAXXMPT_TRPR_SE_CD,
            t.TAXXMPT_APLCN_SE_CD,
            t.SPCABL_MTTR_CNT,
            t.USGSRHLD_DONG_NM,
            t.PRVNTC_CNT,
            t.XPORT_FLFL_YN_DCLR_YMD,
            t.ISSU_NO,
            t.FRST_TRNSFR_YMD,
            t.DRIV_SRGBTRY_IDNTF_NO,
            t.PRCS_IMPRTY_RSN_CD,
            t.PRCS_IMPRTY_RSN_DTLS,
            t.REG_DT,
            t.RGTR,
            u.USER_NM AS rgtrNm
        FROM tb_car_ledger_frmbk t
        LEFT JOIN tb_user u ON t.RGTR = u.USER_ID
        WHERE 1=1
        <if test='searchStartDate != null and searchStartDate != ""'>
            AND DATE_FORMAT(t.REG_DT, '%Y%m%d') <![CDATA[>=]]> #{searchStartDate}
        </if>
        <if test='searchEndDate != null and searchEndDate != ""'>
            AND DATE_FORMAT(t.REG_DT, '%Y%m%d') <![CDATA[<=]]> #{searchEndDate}
        </if>
        <if test='searchVhrno != null and searchVhrno != ""'>
            AND (t.DMND_VHRNO LIKE CONCAT('%', #{searchVhrno}, '%') OR t.VHRNO LIKE CONCAT('%', #{searchVhrno}, '%'))
        </if>
        <if test='searchVin != null and searchVin != ""'>
            AND t.VIN LIKE CONCAT('%', #{searchVin}, '%')
        </if>
        <if test='searchLinkRsltCd != null and searchLinkRsltCd != ""'>
            AND t.LINK_RSLT_CD = #{searchLinkRsltCd}
        </if>
        <if test='searchCarFfnlgTrgtId != null and searchCarFfnlgTrgtId != ""'>
            AND t.CAR_FFNLG_TRGT_ID = #{searchCarFfnlgTrgtId}
        </if>
        ORDER BY t.CAR_LEDGER_FRMBK_ID DESC
        <if test='pagingYn == "Y"'>
            LIMIT #{startIndex}, #{perPage}
        </if>
    </select>

    <!-- 자동차 등록원부(갑) 조회 이력 총 개수 조회 -->
    <select id="selectCarLedgerFrmbkListTotalCount" parameterType="VehicleApiHistorySearchVO" resultType="int">
        SELECT COUNT(*)
        FROM tb_car_ledger_frmbk t
        LEFT JOIN tb_user u ON t.RGTR = u.USER_ID
        WHERE 1=1
        <if test='searchStartDate != null and searchStartDate != ""'>
            AND DATE_FORMAT(t.REG_DT, '%Y%m%d') <![CDATA[>=]]> #{searchStartDate}
        </if>
        <if test='searchEndDate != null and searchEndDate != ""'>
            AND DATE_FORMAT(t.REG_DT, '%Y%m%d') <![CDATA[<=]]> #{searchEndDate}
        </if>
        <if test='searchVhrno != null and searchVhrno != ""'>
            AND (t.DMND_VHRNO LIKE CONCAT('%', #{searchVhrno}, '%') OR t.VHRNO LIKE CONCAT('%', #{searchVhrno}, '%'))
        </if>
        <if test='searchVin != null and searchVin != ""'>
            AND t.VIN LIKE CONCAT('%', #{searchVin}, '%')
        </if>
        <if test='searchLinkRsltCd != null and searchLinkRsltCd != ""'>
            AND t.LINK_RSLT_CD = #{searchLinkRsltCd}
        </if>
        <if test='searchCarFfnlgTrgtId != null and searchCarFfnlgTrgtId != ""'>
            AND t.CAR_FFNLG_TRGT_ID = #{searchCarFfnlgTrgtId}
        </if>
    </select>

    <!-- 자동차 등록원부(갑) 조회 이력 상세 조회 -->
    <select id="selectCarLedgerFrmbkOne" parameterType="String" resultType="CarLedgerFrmbkVO">
        SELECT *
        FROM tb_car_ledger_frmbk
        WHERE CAR_LEDGER_FRMBK_ID = #{carLedgerFrmbkId}
    </select>

    <!-- ==================== 자동차 등록원부(갑) 상세 조회 이력 ==================== -->

    <!-- 자동차 등록원부(갑) 상세 조회 이력 목록 조회 -->
    <select id="selectCarLedgerFrmbkDtlList" parameterType="String" resultType="CarLedgerFrmbkDtlVO">
        SELECT *
        FROM tb_car_ledger_frmbk_dtl
        WHERE CAR_LEDGER_FRMBK_ID = #{carLedgerFrmbkId}
        ORDER BY REG_DT DESC
    </select>

    <!-- 자동차 등록원부(갑) 상세 조회 이력 상세 조회 -->
    <select id="selectCarLedgerFrmbkDtlOne" parameterType="String" resultType="CarLedgerFrmbkDtlVO">
        SELECT *
        FROM tb_car_ledger_frmbk_dtl
        WHERE CAR_LEDGER_FRMBK_DTL_ID = #{carLedgerFrmbkDtlId}
    </select>

    <!-- ==================== 최신 이력 ID 조회 ==================== -->

    <!-- 차량번호로 최신 자동차 기본정보 조회 이력 ID 조회 -->
    <select id="selectLatestCarBassMatterInqireIdByVhclno" parameterType="String" resultType="String">
        SELECT CAR_BASS_MATTER_INQIRE_ID
        FROM tb_car_bass_matter_inqire
        WHERE DMND_VHRNO = #{vhclno} OR VHRNO = #{vhclno}
        ORDER BY REG_DT DESC
        LIMIT 1
    </select>

    <!-- 차량번호로 최신 자동차 등록원부(갑) 조회 이력 ID 조회 -->
    <select id="selectLatestCarLedgerFrmbkIdByVhclno" parameterType="String" resultType="String">
        SELECT CAR_LEDGER_FRMBK_ID
        FROM tb_car_ledger_frmbk
        WHERE DMND_VHRNO = #{vhclno} OR VHRNO = #{vhclno}
        ORDER BY REG_DT DESC
        LIMIT 1
    </select>

</mapper>
