<?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="com.oqpo.api.mapper.loanmng.UnpaidMngMapper">

    <select id="selectUnpaidMngGridList" resultType="com.oqpo.api.entity.settmng.StlMgntBaseInfoEntity">
        /* UnpaidMngMapper.selectUnpaidMngGridList */
        SELECT A.sttl_mgnt_unq_no,
            A.store_id, FN_STORE_NM(A.brand_id, A.store_id) AS store_nm,
            A.brand_id, FN_BRAND_NM(A.brand_id) AS brand_nm,
            A.sttl_st_cd, FN_CODE_NM('STTL_ST_CD', A.sttl_st_cd) AS sttl_st_nm,
            DATE_FORMAT(A.sttl_req_dt, '%Y.%m.%d') AS sttl_req_dt,
            A.sttl_req_amt, A.sttl_amt, C.acct_bal
        FROM stl_mgnt_base_info A
        INNER JOIN store_base_info B ON A.brand_id = B.brand_id AND A.store_id = B.store_id
        INNER JOIN loan_mgnt_base_info C ON B.loan_mgnt_unq_no = C.loan_mgnt_unq_no
        WHERE 1 = 1
        AND A.sttl_st_cd = 'ST10'       /* 정산요청 */
        AND A.brand_id = #{sBrandId}
        <if test="sStoreId != null and sStoreId != ''">
            AND A.store_id = #{sStoreId}
        </if>
        <if test="fromDt != null and fromDt != '' and toDt != null and toDt != '' ">
            AND A.sttl_req_dt BETWEEN #{fromDt} AND #{toDt}
        </if>
        <choose>
            <when test="gridRequest.sidx != null and gridRequest.sidx != ''">
                <if test="gridRequest.sidx == 'VIEW_NUM'.toString()">
                    <if test="gridRequest.sord == 'asc'.toString()">
                        ORDER BY A.sttl_mgnt_unq_no ASC
                    </if>
                    <if test="gridRequest.sord == 'desc'.toString()">
                        ORDER BY A.sttl_mgnt_unq_no DESC
                    </if>
                </if>
            </when>
            <otherwise>
                ORDER BY A.sttl_mgnt_unq_no ASC
            </otherwise>
        </choose>
        <if test="gridRequest.pagingYn == true">
            limit #{gridRequest.gridFirst}, #{gridRequest.gridSize}
        </if>
    </select>

    <select id="selectUnpaidMngGridCnt" resultType="int">
        /* UnpaidMngMapper.selectUnpaidMngGridCnt */
        SELECT COUNT(*)
        FROM stl_mgnt_base_info A
        INNER JOIN store_base_info B ON A.brand_id = B.brand_id AND A.store_id = B.store_id
        INNER JOIN loan_mgnt_base_info C ON B.loan_mgnt_unq_no = C.loan_mgnt_unq_no
        WHERE 1 = 1
        AND A.sttl_st_cd = 'ST10'       /* 정산요청 */
        AND A.brand_id = #{sBrandId}
        <if test="sStoreId != null and sStoreId != ''">
            AND A.store_id = #{sStoreId}
        </if>
        <if test="fromDt != null and fromDt != '' and toDt != null and toDt != '' ">
            AND A.sttl_req_dt BETWEEN #{fromDt} AND #{toDt}
        </if>
    </select>

    <select id="selectStlMgntBaseInfo" resultType="com.oqpo.api.entity.settmng.StlMgntBaseInfoEntity">
        /* UnpaidMngMapper.selectStlMgntBaseInfo */
        SELECT A.sttl_mgnt_unq_no,
            DATE_FORMAT(A.sttl_req_dt, '%Y.%m.%d') AS sttl_req_dt,
            A.store_id, FN_STORE_NM(A.brand_id, A.store_id) AS store_nm,
            A.brand_id, FN_BRAND_NM(A.brand_id) AS brand_nm,
            A.sttl_st_cd, FN_CODE_NM('STTL_ST_CD', A.sttl_st_cd) AS sttl_st_nm, A.sttl_req_amt,
            DATE_FORMAT(A.sttl_dt, '%Y.%m.%d') AS sttl_dt,
            A.sttl_amt, A.sttl_reg_mgr_nm, A.sttl_reg_mgr_id,
            DATE_FORMAT(A.sys_reg_dttm,'%Y.%m.%d %H:%i:%s') AS sys_reg_dttm, A.sys_reg_id, FN_USER_NM(A.sys_reg_id) AS sys_reg_nm,
            DATE_FORMAT(A.sys_chg_dttm,'%Y.%m.%d %H:%i:%s') AS sys_chg_dttm, A.sys_chg_id
        FROM stl_mgnt_base_info A
        WHERE A.sttl_mgnt_unq_no = #{sttlMgntUnqNo}
    </select>

    <select id="selectStlMgntDtlPtclList" resultType="com.oqpo.api.entity.settmng.StlMgntDtlPtclEntity">
        /* UnpaidMngMapper.selectStlMgntDtlPtclList */
        SELECT A.sttl_mgnt_unq_no, A.sttl_mgnt_dtl_no,
            A.brand_id, FN_BRAND_NM(A.brand_id) AS brand_nm,
            A.store_id, FN_STORE_NM(A.brand_id, A.store_id) AS store_nm,
            A.item_id, A.item_nm, A.unit, A.unit_amt,
            A.podr_qty, A.dlv_qty,
            A.sttl_dvsn, FN_CODE_NM('STTL_DVSN', A.sttl_dvsn) AS sttl_dvsn_nm,  /* 정산구분 */
            CASE WHEN A.sttl_dvsn = 'SW00' THEN A.whs_qty WHEN A.sttl_dvsn = 'SC00' THEN A.col_qty END AS whs_col_qty,     /* 입고/수거수량 */
            DATE_FORMAT(CASE WHEN A.sttl_dvsn = 'SW00' THEN A.dlv_cmplt_dt WHEN A.sttl_dvsn = 'SC00' THEN A.col_dt END, '%Y.%m.%d') AS whs_col_qty,     /* 입고/수거일자 */
            CASE WHEN A.sttl_dvsn = 'SW00' THEN A.whs_amt WHEN A.sttl_dvsn = 'SC00' THEN A.col_amt END AS whs_col_qty,     /* 입고/수거금액 */
            A.dlv_qty * A.unit_amt AS sttl_req_amt,      /* 정산요청금액 : 납품수량 * 수량 */
            CASE WHEN A.sttl_dvsn = 'SW00' THEN A.pch_podr_unq_no WHEN A.sttl_dvsn = 'SC00' THEN A.rtn_podr_unq_no END AS podr_unq_no,      /* 발주고유번호 */
            CASE WHEN A.sttl_dvsn = 'SW00' THEN A.pch_podr_dtl_no WHEN A.sttl_dvsn = 'SC00' THEN A.rtn_podr_dtl_no END AS podr_dtl_no       /* 발주상세번호 */
        FROM stl_mgnt_dtl_ptcl A
        WHERE A.sttl_mgnt_unq_no = #{sttlMgntUnqNo}
        ORDER BY A.sttl_mgnt_dtl_no ASC
    </select>

</mapper>
