<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTO Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.no1.wms.account.AccountMapper">
    <resultMap id="accountResultMap" type="AccountDto">
        <id column="id"                     property="id"/>
        <result column="name"               property="name"/>
        <result column="employee_number"    property="employeeNumber"/>
        <result column="password"           property="password"/>
        <result column="email"              property="email"/>
        <result column="gender"             property="gender"/>
        <result column="birth"              property="birth"/>
        <result column="telephone"          property="telephone"/>
        <result column="address"            property="address"/>
        <result column="activation"         property="activation"/>
        <result column="personal_authority_id" property="personalAuthorityId" />
        <result column="group_authority_id" property="groupAuthorityId" />
        <result column="department_id" property="departmentId" />
        <result column="position_id" property="positionId" />
        <association property="groupAuthorityDto"  javaType="authorityDto">
            <id column="auth2.id" property="id" />
            <result column="group_auth_name" property="name" />
        </association>
        <association property="personalAuthorityDto"  javaType="authorityDto">
            <id column="auth.id" property="id" />
            <result column="personal_auth_name" property="name" />
        </association>
        <association property="departmentDto"  javaType="departmentDto">
            <id column="dept_id" property="id" />
            <result column="dept_name" property="name" />
        </association>
        <association property="positionDto"  javaType="positionDto">
            <id column="pos_id" property="id" />
            <result column="pos_name" property="name" />
        </association>
    </resultMap>
    <select id="selectById" resultMap="accountResultMap" parameterType="accountDto">
        SELECT
            acc.id, acc.name,
            acc.employee_number, acc.password, acc.email, acc.gender,
            acc.birth, acc.telephone, acc.address, acc.activation,
            acc.personal_authority_id , acc.group_authority_id,
            acc.department_id, acc.position_id,
            auth2.name group_auth_name, auth.name personal_auth_name,
            dept.name dept_name, pos.name pos_name
        from account acc join authority auth join authority auth2 join department dept join position pos
        on acc.personal_authority_id = auth.id and acc.group_authority_id = auth2.id
        and dept.id = acc.department_id and acc.position_id = pos.id
        where acc.id = #{id}
    </select>
    <select id="selectByLogin" resultMap="accountResultMap" parameterType="accountDto">
        SELECT
            id, name, activation, password,
            personal_authority_id , group_authority_id
        from account
        where employee_number = #{employeeNumber} and password = #{password}
    </select>

    <select id="selectAll" resultMap="accountResultMap" parameterType="map">
        SELECT
            acc.id, acc.name,
            acc.employee_number, acc.password, acc.email, acc.gender,
            acc.birth, acc.telephone, acc.address, acc.activation,
            auth2.name group_auth_name, auth.name personal_auth_name
        from account acc join authority auth join authority auth2 on acc.personal_authority_id = auth.id and acc.group_authority_id = auth2.id
        <where>
            <choose>
                <when test="searchn == 1"> acc.activation = 1 and acc.name like concat('%',#{search},'%')</when>
                <when test="searchn == 0"> acc.activation = 1 and acc.employee_number like concat('%',#{search},'%') </when>
            </choose>
        </where>
        order by acc.id desc limit #{start}, #{perPage}

    </select>
    <insert id="insert" parameterType="accountDto">
        insert into account
        (
            id, employee_number, password, email, group_authority_id,
            personal_authority_id, name, gender, birth, telephone, address,
            department_id, position_id, activation
        )
        VALUES
        (
           UUID(), #{employeeNumber}, #{employeeNumber},
           #{email}, #{groupAuthorityId},
           #{personalAuthorityId},#{name},#{gender},
           #{birth},#{telephone}, #{address},
           #{departmentId},#{positionId},#{activation}
        )
    </insert>

    <insert id="insertToAuthority" parameterType="AuthorityDto">
        insert into authority
        (
            id, name, account, authority, product_category,
            product, prices, vendor, warehouse, stock, plan_in,
            product_in, product_out, board, activation, is_group_authority)
        VALUES (
                   #{id}, #{name}, #{account},
                   #{authority}, #{productCategory},
                   #{product},#{prices},#{vendor},
                   #{warehouse},#{stock}, #{planIn},
                   #{productIn},#{productOut},#{board},
                   #{activation}, #{isGroupAuthority}
               )
    </insert>

    <select id="selectDeptAll" resultType="departmentDto" parameterType="map">
        select * from department
        where name like concat('%',#{search},'%')
        order by name limit  #{start}, #{perPage}
    </select>

    <select id="selectPosAll" resultType="positionDto" parameterType="map">
        select * from position
        where name like concat('%',#{search},'%')
        order by name limit  #{start}, #{perPage}
    </select>
    <update id="resetPassword" parameterType="accountDto">
        update account SET
                           password = ( SELECT employee_number from account where id = #{id} )
        WHERE id = #{id}
    </update>

    <update id="update" parameterType="accountDto">
        update account SET
             name = #{name},
             employee_number = #{employeeNumber},
             email = #{email},
             personal_authority_id = #{personalAuthorityId},
             gender = #{gender},
             birth = #{birth},
             telephone = #{telephone},
             address = #{address},
             department_id = #{departmentId},
             position_id = #{positionId},
             activation = #{activation}
        WHERE id = #{id}
    </update>
    <update id="delete" parameterType="accountDto">
        update account SET activation = false WHERE id = #{id}
    </update>
    <update id="passReset" parameterType="accountDto">
        update account SET password = #{password} WHERE employee_number = #{employeeNumber}
    </update>

    <select id="count" parameterType="map" resultType="java.lang.Integer">
        select count(*) from ( SELECT 1 from account
        <where>
            <choose>
                <when test="searchn == 1"> activation = 1 and name like concat('%',#{search},'%')</when>
                <when test="searchn == 0"> activation = 1 and employee_number like concat('%',#{search},'%') </when>
            </choose>
        </where>
         ) t
    </select>
</mapper>