<?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.hzs.member.handlebusiness.mapper.CuHandleBusinessMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.hzs.common.domain.member.handleBusiness.CuHandleBusiness">
        <id column="PK_ID" property="pkId"/>
        <result column="DEL_FLAG" property="delFlag"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="MODIFIED_TIME" property="modifiedTime"/>
        <result column="PK_COUNTRY" property="pkCountry"/>
        <result column="PK_CREATOR" property="pkCreator"/>
        <result column="PK_MODIFIED" property="pkModified"/>
        <result column="ORDER_NUMBER" property="orderNumber"/>
        <result column="STORED_VALUE" property="storedValue"/>
        <result column="SIGN_TYPE" property="signType"/>
        <result column="INITIATOR" property="initiator"/>
        <result column="DEPARTMENT" property="department"/>
        <result column="EDIT_TYPE" property="editType"/>
        <result column="BUSINESS_NO" property="businessNo"/>
        <result column="APPROVE_STATUS" property="approveStatus"/>
        <result column="PROCESS_TYPE" property="processType"/>
        <result column="PROCESS_TYPE" property="pkAccount"/>
    </resultMap>

    <resultMap id="HandleBusinessVO" type="com.hzs.member.handlebusiness.vo.HandleBusinessShowVO">
        <id column="PK_ID" property="pkMember"/>
        <result column="DEL_FLAG" property="delFlag"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="MODIFIED_TIME" property="modifiedTime"/>
        <result column="PK_COUNTRY" property="pkCountry"/>
        <result column="PK_CREATOR" property="pkCreator"/>
        <result column="PK_MODIFIED" property="pkModified"/>
        <result column="member_code" property="memberCode"/>
        <result column="member_name" property="memberName"/>
        <result column="phone" property="phone"/>
        <result column="pk_register_grade" property="pkRegisterGrade"/>
        <result column="grade_name" property="pkRegisterGradeVal"/>
        <result column="pk_awards" property="pkAwards"/>
        <result column="AWARDS_NAME" property="pkAwardsVal"/>
        <result column="vertexName" property="vertexName"/>
        <result column="teamName" property="teamName"/>
        <result column="center_member_code" property="centerMemberCode"/>
        <result column="center_member_name" property="centerMemberName"/>
        <result column="PAY_TIME" property="payTime"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="EDIT_NUMBER" property="editNumber"/>
        <result column="IS_REGION" property="isRegion"/>
    </resultMap>

    <!--   查询办理业务列表-->
    <select id="queryHandleBusinessList" parameterType="com.hzs.member.handlebusiness.vo.HandleBusinessShowVO"
            resultMap="HandleBusinessVO">
        SELECT
        cm.PK_ID,
        cm.member_code,
        cm.member_name,
        cm.phone,
        cm.pk_register_grade,
        bg.grade_name,
        cm.pk_awards,
        awa.AWARDS_NAME,
        ver.VERTEX_NAME as vertexName,
        ct.TEAM_NAME as teamName,
        center.member_code center_member_code,
        center.member_name center_member_name,
        cm.PAY_TIME,
        cm.CREATION_TIME,
        cm.EDIT_NUMBER,
        cm.IS_REAL_NAME isRealName,
        cm.IS_REGION isRegion
        FROM
        cu_member cm
        LEFT JOIN cu_member_team ct ON ct.pk_id = cm.pk_team_code
        LEFT JOIN cu_member team ON team.pk_id = ct.pk_member
        LEFT JOIN cu_member center ON center.pk_id = cm.pk_center_code
        left join bd_grade bg on bg.pk_id = cm.pk_settle_grade
        left join bd_awards awa on awa.pk_id= cm.PK_AWARDS
        left join bd_vertex ver on ver.pk_id=ct.PK_BD_VERTEX
        WHERE
        cm.del_flag = 0
        and cm.PK_SETTLE_COUNTRY=#{pkCountry}
        <if test="systemType != null">
            and cm.system_type = #{systemType}
        </if>
        <if test="memberCode != null and memberCode != ''">
            and cm.member_code=#{memberCode}
        </if>
        <if test="memberName != null and memberName != ''">
            and cm.member_name=#{memberName}
        </if>
        <if test="pkAwards != null">
            and cm.pk_awards=#{pkAwards}
        </if>
        <if test="pkRegisterGrade != null">
            and cm.pk_settle_grade=#{pkRegisterGrade}
        </if>
        <if test="vertexId != null">
            and ver.PK_ID=#{vertexId}
        </if>
        <if test="teamId != null">
            and ct.PK_ID=#{teamId}
        </if>
        <if test="editNumber != null">
            and cm.EDIT_NUMBER=#{editNumber}
        </if>
        <if test="phone != null and phone != ''">
            and cm.phone=#{phone}
        </if>
        <if test="centerMemberCode != null and centerMemberCode != ''">
            and center.member_code=#{centerMemberCode}
        </if>
        <if test="centerMemberName != null and centerMemberName != ''">
            and center.member_name=#{centerMemberName}
        </if>
        <if test="startDatePay != null">
            and cm.PAY_TIME >= #{startDatePay, jdbcType=TIMESTAMP}
        </if>
        <if test="endDatePay != null">
            and cm.PAY_TIME &lt; #{endDatePay, jdbcType=TIMESTAMP}
        </if>
        <if test="startDate != null ">
            and cm.CREATION_TIME &gt;= #{startDate, jdbcType=TIMESTAMP}
        </if>
        <if test="endDate != null ">
            and cm.CREATION_TIME &lt; #{endDate, jdbcType=TIMESTAMP}
        </if>
        order by cm.CREATION_TIME desc
    </select>

    <select id="selectByOrderNumber" resultMap="BaseResultMap">
        select cm.ORDER_NUMBER,
               cm.STORED_VALUE,
               cm.SIGN_TYPE,
               cm.INITIATOR,
               cm.DEPARTMENT,
               cm.EDIT_TYPE,
               cm.BUSINESS_NO,
               cm.APPROVE_STATUS,
               cm.PROCESS_TYPE,
               cm.PK_ACCOUNT
        FROM CU_HANDLE_BUSINESS cm
        where cm.ORDER_NUMBER = #{orderNumber}
    </select>

</mapper>
