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.crm.persistence.ActivityMapper">

  <resultMap type="activity" id="activityResult">

    <id property="id" column="id"/>

    <association property="creator" column="creatorId" javaType="agent">

        <id property="id" column="creator_id"/>

        <result property="name" column="creator_name"/>

        <result property="emailId" column="creator_emailId"/>

        <result property="password" column="creator_password"/>

        <result property="managerId" column="creator_managerId"/>

    </association>

    <association property="ticketAssignee" column="ticketAssigneeId" javaType="agent">

        <id property="id" column="ticketAssignee_id"/>

        <result property="name" column="ticketAssignee_name"/>

        <result property="emailId" column="ticketAssignee_emailId"/>

        <result property="password" column="ticketAssignee_password"/>

        <result property="managerId" column="ticketAssignee_managerId"/>

    </association>

  </resultMap>

  <insert id="insertActivity" parameterType="activity" useGeneratedKeys="true" keyProperty="id">

    INSERT INTO activity

      (customerId, ticketAssigneeId, description, ticketId, creatorId, type, creationTimestamp,

       ticketPriority, ticketStatus, ticketCategory, ticketDescription, userEmailId, customerMobileNumber,

       isRead, customerEmailId, customerName)

    VALUES

      (#{customerId}, #{ticketAssigneeId}, #{description}, #{ticketId}, #{creatorId}, #{type}, NOW(),

       #{ticketPriority}, #{ticketStatus}, #{ticketCategory}, #{ticketDescription}, #{userEmailId},

       #{customerMobileNumber}, #{isRead}, #{customerEmailId}, #{customerName})

  </insert>

  <select id="getActivities" parameterType="searchFilter" resultType="activity">

    SELECT a.*,

    c.id AS creator_id,

    c.name AS creator_name,

    c.emailId AS creator_emailId,

    c.password AS creator_password,

    c.managerId AS creator_managerId,

    t.id AS ticketAssignee_id,

    t.name AS ticketAssignee_name,

    t.emailId AS ticketAssignee_emailId,

    t.password AS ticketAssignee_password,

    t.managerId AS ticketAssignee_managerId

    FROM activity a

    LEFT JOIN agent c

        ON (c.id = a.creatorId)

    LEFT JOIN agent t

        ON (t.id = a.ticketAssigneeId)

    <where>

    <if test="ticketId != null">

        AND a.ticketId = #{ticketId}

    </if>

    <if test="activityId != null">

        AND a.id = #{activityId}

    </if>

    <if test="activityCreatorIds != null">

        AND a.creatorId IN 

        <foreach collection="activityCreatorIds" item="activityCreatorId"

                 index="index" open="(" close=")" separator=",">

            #{activityCreatorId}

        </foreach>

    </if>

    <if test="customerId != null">

        AND a.customerId = #{customerId}

    </if>

    <if test="startTimestamp != null">

        AND a.creationTimestamp > #{startTimestamp}

    </if>

    <if test="endTimestamp != null">

        AND a.creationTimestamp < #{endTimestamp}

    </if>

    <if test="customerEmailId != null">

        AND a.customerEmailId = #{customerEmailId}

    </if>

    <if test="customerMobileNumber != null">

        AND a.customerMobileNumber = #{customerMobileNumber}

    </if>

    <if test="isActivityRead != null">

        AND a.isRead = #{isActivityRead}

    </if>

    </where>

    ORDER BY a.creationTimestamp DESC

  </select>

  <update id="markAsRead" parameterType="long">

    UPDATE activity

    SET isRead = 1

    WHERE id = #{activityId}

  </update>

</mapper>