0613_SPLR.md

✅ 납품기업관리 CRUD

• 요구사항

  • 임원진 및 영업팀 소속 구성원만 납품기업 정보 CRUD 가능

• SQL (MySQL 기준)

  • 전체 데이터 목록조회

    <select id="splrlist" resultType="kr.happyjob.study.busSpm.model.SplrModel">
        SELECT
                  sp.splr_no
                , sp.splr_name
                , sp.splr_tel
                , sp.splr_mng
                , sp.splr_hp
                , sp.splr_zip
                , sp.bk_cd
                , dc.detail_name AS bk_name
                , sp.splr_acc
                , sp.splr_add
                , sp.splr_add_dt
                , sp.splr_indst
                , sp.splr_indst_no
                , sp.splr_memo
                , sp.splr_email
        FROM tb_splr sp
        INNER JOIN tb_detail_code dc ON dc.group_code = 'bk_cd' AND dc.detail_code = sp.bk_cd
        <where>
            <if test="(sname != null) and (!sname.equals(''))">
                <choose>
                    <when test="searchKey eq 'sp_name'.toString()">
                        and sp.splr_name Like CONCAT('%', #{sname}, '%')
                    </when>
                    <when test="searchKey eq 'sp_indst_no'.toString()">
                        and sp.splr_indst_no LIKE CONCAT('%', #{sname}, '%')
                    </when>
                    <otherwise>
                        and ( sp.splr_name Like CONCAT('%', #{sname}, '%') or sp.splr_indst_no LIKE CONCAT('%', #{sname}, '%') )
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY sp.splr_no
        LIMIT #{pageindex}, #{pageSize}
    </select>

  • 전체 데이터 목록 카운트 조회 (페이지네이션 기능 구현을 위함)

    <select id="countsplrlist" resultType="int">
        SELECT COUNT(*)
        FROM tb_splr sp
        INNER JOIN tb_detail_code dc ON dc.group_code = 'bk_cd' AND dc.detail_code = sp.bk_cd
        <where>
            <if test="(sname != null) and (!sname.equals(''))">
                <choose>
                    <when test="searchKey eq 'sp_name'.toString()">
                        and sp.splr_name Like CONCAT('%', #{sname}, '%')
                    </when>
                    <when test="searchKey eq 'sp_indst_no'.toString()">
                        and sp.splr_indst_no LIKE CONCAT('%', #{sname}, '%')
                    </when>
                    <otherwise>
                        and ( sp.splr_name Like CONCAT('%', #{sname}, '%') or sp.splr_indst_no LIKE CONCAT('%', #{sname}, '%') )
                    </otherwise>
                </choose>
            </if>
        </where>
    </select>

  • 데이터 1건 조회

    <select id="splrselectone" resultType="kr.happyjob.study.busSpm.model.SplrModel">
        SELECT
                  sp.splr_no
                , sp.splr_name
                , sp.splr_tel
                , sp.splr_mng
                , sp.splr_hp
                , sp.splr_zip
                , sp.bk_cd
                , dc.detail_name AS bk_name
                , sp.splr_acc
                , sp.splr_add
                , sp.splr_add_dt
                , sp.splr_indst
                , sp.splr_indst_no
                , sp.splr_memo
                , sp.splr_email
        FROM tb_splr sp
        INNER JOIN tb_detail_code dc ON dc.group_code = 'bk_cd' AND dc.detail_code = sp.bk_cd
        WHERE sp.splr_no = #{splr_no}              
    </select>