<?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="cn.ey.biz.module.system.dal.mapper.user.AdminUserMapper">

    <!-- 自定义ORM映射规则 -->
    <resultMap id="BaseResultMap" type="cn.ey.biz.module.system.dal.dataobject.user.AdminUserDO">
        <result column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="nickname" property="nickname"/>
        <result column="remark" property="remark"/>
        <result column="dept_id" property="deptId"/>
        <result column="post_ids" property="postIds" typeHandler="cn.ey.biz.framework.mybatis.core.type.JsonLongSetTypeHandler"/>
        <result column="private_email" property="email"/>
        <result column="mobile" property="mobile"/>
        <result column="gender" property="sex"/>
        <result column="avatar" property="avatar"/>
        <result column="status" property="status"/>
        <result column="login_ip" property="loginIp"/>
        <result column="tenant_id" property="tenantId"/>
        <result column="login_date" property="loginDate"/>
        <result column="user_code" property="userCode"/>
        <result column="gui" property="gui"/>
        <result column="gpn" property="gpn"/>
        <result column="lpn" property="lpn"/>
        <result column="en_first_name" property="enFirstName"/>
        <result column="en_last_name" property="enLastName"/>
        <result column="en_middle_name" property="enMiddleName"/>
        <result column="cn_first_name" property="cnFirstName"/>
        <result column="cn_last_name" property="cnLastName"/>
        <result column="display_name" property="displayName"/>
        <result column="citizenship" property="citizenship"/>
        <result column="second_citizenship" property="secondCitizenship"/>
        <result column="business_email" property="businessEmail"/>
        <result column="private_phone" property="privatePhone"/>
        <result column="business_phone" property="businessPhone"/>
        <result column="employee_status" property="employeeStatus"/>
        <result column="employee_class" property="employeeClass"/>
        <result column="employee_type" property="employeeType"/>
        <result column="base_location" property="baseLocation"/>
        <result column="conseloer_gpn" property="conseloerGpn"/>

        <result column="rank_code" property="rankCode"/>
        <result column="date_of_birth" property="dateOfBirth"/>
        <result column="country_of_birth" property="countryOfBirth"/>
        <result column="region_of_birth" property="regionOfBirth"/>
        <result column="region" property="region"/>
        <result column="country" property="country"/>
        <result column="geo_region" property="geoRegion"/>
        <result column="legal_name" property="legalName"/>
        <result column="legal_entity" property="legalEntity"/>
        <result column="branch" property="branch"/>
        <result column="buesiness_unit" property="buesinessUnit"/>
        <result column="mgmt_region" property="mgmtRegion"/>
        <result column="operating_unit" property="operatingUnit"/>
        <result column="mgmt_unit" property="mgmtUnit"/>
        <result column="sub_mgmt_unit" property="subMgmtUnit"/>
        <result column="code_block" property="codeBlock"/>
        <result column="overtime_rlihiniliyy" property="overtimeRlihiniliyy"/>
        <result column="effictive_date" property="effictiveDate"/>
        <result column="resource_type" property="resourceType"/>
        <result column="work_location" property="workLocation"/>
        <result column="pay_type" property="payType"/>
        <result column="sub_legal_entity" property="subLegalEntity"/>
        <result column="service_line" property="serviceLine"/>
        <result column="sub_service_line" property="subServiceLine"/>
        <result column="is_contract_worker" property="isContractWorker"/>
        <result column="cost_rate" property="costRate"/>
        <result column="billing_rat" property="billingRate"/>

        <result column="net_standard_revene_rate" property="netStandardReveneRate"/>
        <result column="standard_hours" property="standardHours"/>
        <result column="partner_gpn_name" property="partnerGpnName"/>
        <result column="supervisor" property="supervisor"/>
        <result column="grade_Id" property="gradeId"/>
        <result column="competency" property="competency"/>
        <result column="speciality" property="speciality"/>
        <result column="person_Id" property="personId"/>
        <result column="cost_center" property="costCenter"/>
        <result column="quit_date" property="quitDate"/>
        <result column="weekly_days" property="weeklyDays"/>
        <result column="market_segment" property="marketSegment"/>

        <result column="currency" property="currency"/>
        <result column="effective_time" property="effectiveTime"/>
        <result column="failure_time" property="failureTime"/>
        <result column="version" property="version"/>
        <result column="create_time" property="createTime"/>
        <result column="department_id" property="departmentId"/>
    </resultMap>
    <sql id="Base_Column_List">
        id, username, password, nickname, remark, dept_id, post_ids, private_email,
        mobile, gender,avatar, status, login_ip,login_date, tenant_id, user_code, gui,
        gpn, lpn , en_first_name , en_last_name , en_middle_name , cn_first_name , cn_last_name
        , display_name , citizenship , second_citizenship , business_email , private_phone, business_phone
        , employee_status , employee_class, employee_type , base_location, conseloer_gpn, rank_code, date_of_birth,
        country_of_birth, region_of_birth, region, country , geo_region, legal_name, legal_entity, branch,
        buesiness_unit
        , mgmt_region, operating_unit, mgmt_unit , sub_mgmt_unit, code_block, service_line, sub_service_line
        , overtime_rlihiniliyy, effictive_date, resource_type, work_location , pay_type, sub_legal_entity
        , is_contract_worker, cost_rate, billing_rate, net_standard_revene_rate, standard_hours, original_business_unit
        , partner_gpn_name, speciality, supervisor, grade_Id, person_Id, cost_center, quit_date, weekly_days
        , market_segment, currency, effective_time, failure_time, department_id,create_time, user_id_hr, competency,version,
        market_segment_code,city_code
    </sql>
    <select id="getUserPageList" resultMap="BaseResultMap">
        select
        su.id, su.username, su.password, su.nickname, su.remark, su.dept_id, su.post_ids, su.private_email,
        su.mobile, su.gender,su.avatar, su.status, su.login_ip,su.login_date, su.tenant_id, su.user_code, su.gui,
        su.gpn, su.lpn , su.en_first_name , su.en_last_name , su.en_middle_name , su.cn_first_name , su.cn_last_name
        , su.display_name , su.citizenship , su.second_citizenship , su.business_email , su.private_phone,
        su.business_phone
        , su.employee_status , su.employee_class, su.employee_type , su.base_location, su.conseloer_gpn, su.rank_code,
        su.date_of_birth,
        su.country_of_birth, su.region_of_birth, su.region, su.country , su.geo_region, su.legal_name, su.legal_entity,
        su.branch,
        su.buesiness_unit
        , su.mgmt_region, su.operating_unit, su.mgmt_unit , su.sub_mgmt_unit, su.code_block, su.service_line,
        su.sub_service_line
        , su.overtime_rlihiniliyy, su.effictive_date, su.resource_type, su.work_location , su.pay_type,
        su.sub_legal_entity
        , su.is_contract_worker, su.cost_rate, su.billing_rate, su.net_standard_revene_rate, su.standard_hours,
        su.original_business_unit
        , su.partner_gpn_name, su.speciality, su.supervisor, su.grade_Id, su.person_Id, su.cost_center, su.quit_date,
        su.weekly_days
        , su.market_segment, su.currency, su.effective_time, su.failure_time, su.department_id, su.user_id_hr,
        su.competency,su.version,su.create_time,
        t2.market_segment_code,t2.city_code
        from system_users su
        LEFT JOIN dict_business_unit t2
        ON su.buesiness_unit = t2.bu_code and t2.new_version=1 and t2.status=0 and t2.deleted=0
        where  su.deleted=0
        <include refid="queryByPage"/>
        order by su.id desc
    </select>

    <sql id="queryByPage">

        <if test="pageReqVO.username!=null and pageReqVO.username!=''">
            and su.username like  concat('%', #{pageReqVO.username},'%')
        </if>
        <if test="pageReqVO.mobile!=null and pageReqVO.mobile!=''">
            and su.mobile like  concat('%', #{pageReqVO.mobile},'%')
        </if>
        <if test="pageReqVO.status!=null">
            and su.status=#{pageReqVO.status}
        </if>
        <if test="pageReqVO.nickName!=null and pageReqVO.nickName!=''">
            and su.nickname like  concat('%', #{pageReqVO.nickName},'%')
        </if>
        <if test="pageReqVO.createStartTime!=null and pageReqVO.createStartTime!=''  and pageReqVO.createEndTime!=null  and pageReqVO.createEndTime!=''">
            and su.create_time BETWEEN #{pageReqVO.createStartTime} AND #{pageReqVO.createEndTime}
        </if>
        <if test="pageReqVO.deptIds!=null and pageReqVO.deptIds.size() > 0">
            and su.dept_id
            in
            <foreach collection="pageReqVO.deptIds and pageReqVO.deptIds.size() > 0" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
        </if>
        <if test="pageReqVO.mrCode!=null and pageReqVO.mrCode.size() > 0">
            and su.mgmt_region
            in
            <foreach collection="pageReqVO.mrCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
        </if>

        <if test="pageReqVO.ouCode!=null and pageReqVO.ouCode.size() > 0">
            and su.operating_unit
            in
            <foreach collection="pageReqVO.ouCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
        </if>
        <if test="pageReqVO.muCode!=null and pageReqVO.muCode.size() > 0">
            and su.mgmt_unit
            in
            <foreach collection="pageReqVO.muCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
        </if>
        <if test="pageReqVO.businessEmail!=null and pageReqVO.businessEmail!=''">
            and su.business_email=#{pageReqVO.businessEmail}
        </if>
        <if test="pageReqVO.gpn!=null and pageReqVO.gpn!=''">
            and su.gpn=#{pageReqVO.gpn}
        </if>
        <if test="pageReqVO.lpn!=null and pageReqVO.lpn!=''">
            and su.lpn=#{pageReqVO.lpn}
        </if>
        <if test="pageReqVO.gui!=null and pageReqVO.gui!=''">
            and su.gui=#{pageReqVO.gui}
        </if>

        <if test="pageReqVO.employeeStatus!=null and pageReqVO.employeeStatus!=''">
            and su.employee_status=#{pageReqVO.employeeStatus}
        </if>

        <if test="pageReqVO.employeeClass!=null and pageReqVO.employeeClass!=''">
            and su.employee_class=#{pageReqVO.employeeClass}
        </if>
        <if test="pageReqVO.employeeType!=null and pageReqVO.employeeType!=''">
            and su.employee_type=#{pageReqVO.employeeType}
        </if>
        <if test="pageReqVO.serviceLineTree!=null and pageReqVO.serviceLineTree.size() > 0">
            and(
            <foreach collection="pageReqVO.serviceLineTree" item="item" separator="or">
                (
                <if test="item.slCode!=null and item.slCode!=''">
                    su.service_line = #{item.slCode}
                </if>
                <if test="item.sslCode!=null and item.sslCode!=''">
                    <if test="item.slCode!=null and item.slCode!=''">
                        and
                    </if>
                    su.sub_service_line = #{item.sslCode}
                </if>
                <if test="item.competencyCode!=null and item.competencyCode!=''">
                    <if test="(item.slCode!=null and item.slCode!='')||(item.sslCode!=null and item.sslCode!='')">
                        and
                    </if>
                    su.competency = #{item.competencyCode}
                </if>

                <if test="item.smuCode!=null and item.smuCode!=''">
                    <if test="(item.competencyCode!=null and item.competencyCode!='')||(item.slCode!=null and item.slCode!='')||(item.sslCode!=null and item.sslCode!='')">
                        and
                    </if>
                    su.sub_mgmt_unit = #{item.smuCode}
                </if>
                )
            </foreach>
            )
        </if>

        <if test="pageReqVO.regionTree!=null and pageReqVO.regionTree.size() > 0">
            and(
            <foreach collection="pageReqVO.regionTree" item="item" separator="or">
                (
                <if test="item.marketSegmentCode!=null and item.marketSegmentCode!=''">
                    t2.market_segment_code = #{item.marketSegmentCode}
                </if>
                <if test="item.cityCode!=null and item.cityCode!=''">
                    <if test="item.marketSegmentCode!=null and item.marketSegmentCode!=''">
                        and
                    </if>
                    t2.city_code = #{item.cityCode}
                </if>
                <if test="item.buCode!=null and item.buCode!=''">
                    <if test="(item.marketSegmentCode!=null and item.marketSegmentCode!='')||(item.cityCode!=null and item.cityCode!='')">
                        and
                    </if>
                    t2.bu_code = #{item.buCode}
                </if>
                )
            </foreach>
            )
        </if>
    </sql>
</mapper>

