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 >= #{startDate}

  		AND active_since <= #{endDate}

  	</where>

</select>

<select id="getUserByCartId" resultMap="userWithAddressResult">

    SELECT *

    FROM user

    <where>

  		active_cart_id = #{cartId}

  	</where>

</select>

</mapper>