Subversion Repositories SmartDukaan

<?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="in.shop2020.user.persistence.UserMapper">

<resultMap type="user" id="userResult">
                <id property="id" column="id"/>
</resultMap>

<resultMap type="userWithAddress" id="userWithAddressResult">
                <id property="id" column="id"/>
                <collection property="addresses" javaType="ArrayList" column="id" ofType="address" select="getAddressByUserId"/>
</resultMap>

<resultMap type="usercommunication" id="usercommunicationResult">
                <id property="id" column="id"/>
</resultMap>



<select id="getUserNameById" parameterType="String" resultMap="userWithAddressResult">
    SELECT name
    FROM user
    <where>
        id = #{id}
        </where>
</select>

<insert id="createAnonymousUser" parameterType="user" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user
    (jsession_id, active_cart_id, email, password, name, communication_email, 
    is_anonymous, sex, trust_level,active_since)
    VALUES
    (#{jsession_id}, #{active_cart_id}, #{email}, #{password}, #{name}, 
    #{communication_email}, #{is_anonymous}, #{sex}, #{trust_level}, #{activeSince})
</insert>

<select id="getUserById" parameterType="long" resultMap="userWithAddressResult">
    SELECT *
    FROM user
    <where>
        id = #{userId}
        </where>
</select>

<select id="getAddressByUserId" parameterType="int" resultType="address">
    SELECT *
    FROM address
    <where>
        user_id = #{id}
        </where>
        LIMIT 1;
</select>

<select id="getUserByEmail" parameterType="String" resultMap="userWithAddressResult">
    SELECT *
    FROM user
    <where>
        email = #{email}
        </where>
</select>

<select id="getUserByMobileNumber" parameterType="String" resultMap="userWithAddressResult">
    SELECT *
    FROM user
    <where>
        mobile_number = #{mobileNumber}
        </where>
        LIMIT 1
</select>

<insert id="createUser" parameterType="user" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user
      (email, password, name, default_address_id, communication_email, active_cart_id, jsession_id,
       is_anonymous, date_of_birth, sex, mobile_number, source, source_start_time, trust_level, active_since)
    VALUES
      (#{email}, #{password}, #{name}, #{default_address_id}, #{communication_email}, #{active_cart_id}, 
       #{jsession_id}, #{is_anonymous}, #{date_of_birth}, #{sex}, #{mobile_number},#{source}, 
       #{source_start_time}, #{trust_level}, #{activeSince})
  </insert>

<update id="setLastLogin" parameterType="map">
        UPDATE user
        SET last_login = #{date}
        <where>
                id = #{userId}
        </where>
</update>

<update id="setLastLogout" parameterType="map">
        UPDATE user
        SET last_logout = #{date}
        <where>
                id = #{userId}
        </where>
</update>

<update id="increaseTrustLevel" parameterType="map">
        UPDATE user
        SET trust_level = trust_level + #{trustLevelDelta}
        <where>
                id = #{userId}
        </where>
</update>

<select id="getTrustLevel" parameterType="long" resultType="double">
        SELECT trust_level
        FROM user
        <where>
                id = #{userId}
        </where>
</select>

<update id="updateUser" parameterType="user">
    UPDATE user
    SET email=#{email}, password=#{password}, name=#{name}, communication_email=#{communication_email}, 
        jsession_id=#{jsession_id}, is_anonymous=#{is_anonymous}, date_of_birth=#{date_of_birth}, 
        sex=#{sex}, mobile_number=#{mobile_number},     trust_level=#{trust_level}
        <where>
                id = #{id}
        </where>        
</update>

<update id="setDefaultAddress" parameterType="map">
        UPDATE user
        SET default_address_id = #{addressId}
        <where>
                id = #{userId}
        </where>
</update>

<insert id="insertUserCommunication" parameterType="usercommunication" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO usercommunication
      (user_id, communication_type, order_id, airwaybill_no, reply_to, product_name, subject,
       message, communication_timestamp)
    VALUES
      (#{user_id}, #{communication_type}, #{order_id}, #{airwaybill_no}, #{reply_to}, #{product_name}, 
       #{subject}, #{message}, #{communication_timestamp})
</insert>

<select id="getUserCommunicationById" parameterType="long" resultType="usercommunication">
    SELECT *
    FROM usercommunication
    <where>
        id = #{id}
        </where>
</select>

<select id="getUserCommunicationByUser" parameterType="long" resultType="usercommunication">
    SELECT *
    FROM usercommunication
    <where>
        user_id = #{userId}
        </where>
        ORDER BY id
</select>

<select id="getAllUserCommunications" resultType="usercommunication">
    SELECT *
    FROM usercommunication
    ORDER BY id 
</select>

<delete id="removeUserCommunication" parameterType="long">
    DELETE from usercommunication
    <where>
        id = #{id}
        </where>
 </delete>

<update id="updatePassword" parameterType="map">
        UPDATE user
        SET password = #{newPassword}
        <where>
                id = #{userId}
        </where>
</update>

<select id="getUserCount" resultType="long">
    SELECT count(*)
    FROM user
</select>

<select id="getUserCountByType" resultType="long">
    SELECT count(*)
    FROM user
    <where>
                is_anonymous = #{userType}
        </where>
</select>

<select id="getAllUsers" parameterType="map" resultType="user">
    SELECT *
    FROM user
    <where>
                is_anonymous = #{userType}
                AND active_since &gt;= #{startDate}
                AND active_since &lt;= #{endDate}
        </where>
</select>

<select id="getUserByCartId" resultMap="userWithAddressResult">
    SELECT *
    FROM user
    <where>
                active_cart_id = #{cartId}
        </where>
</select>

</mapper>