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.TicketMapper">

  <resultMap type="ticket" id="ticketResult">

    <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="assignee" column="assigneeId" javaType="agent">

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

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

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

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

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

    </association>

  </resultMap>

  <insert id="insertTicket" parameterType="ticket" useGeneratedKeys="true" keyProperty="id">

    INSERT INTO ticket

      (customerId, openDate, closeDate, creatorId, description, assigneeId, status,

       priority, category, productName, airwayBillNo, orderId, customerEmailId,

       customerMobileNumber, customerName)

    VALUES

      (#{customerId}, NOW(),  #{closeDate},  #{creatorId}, #{description}, #{assigneeId}, #{status},

       #{priority}, #{category}, #{productName}, #{airwayBillNo}, #{orderId}, #{customerEmailId},

       #{customerMobileNumber}, #{customerName})

  </insert>

  <update id="updateTicket" parameterType="ticket">

    UPDATE ticket

    SET

    <if test="assigneeId != null">

      assigneeId = #{assigneeId},

    </if>

    <if test="status != null">

      status = #{status},

    </if>

    <if test="priority != null">

      priority = #{priority},

    </if>

    <if test="category != null">

      category = #{category},

    </if>

    <if test="description != null">

      description = #{description},

    </if>

    <if test="customerEmailId != null">

      customerEmailId = #{customerEmailId},

    </if>

    <if test="customerName != null">

      customerName = #{customerName},

    </if>

    <if test="customerMobileNumber != null">

      customerMobileNumber = #{customerMobileNumber},

    </if>

    <if test="status != null">

      status = #{status},

    </if>

    <if test="priority != null">

      priority = #{priority},

    </if>

    <if test="category != null">

      category = #{category},

    </if>

    closeDate = #{closeDate}

    WHERE id = #{id}

  </update>

  <select id="getUnassignedTickets" resultType="ticket">

    SELECT * FROM ticket

    WHERE assigneeId IS NULL

    ORDER BY openDate DESC

  </select>

  <select id="getTickets" parameterType="searchFilter" resultType="ticket">

    SELECT t.*,

    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,

    a.id AS assignee_id,

    a.name AS assignee_name,

    a.emailId AS assignee_emailId,

    a.password AS assignee_password,

    a.managerId AS assignee_managerId

    FROM ticket t

    LEFT JOIN agent c

        ON (c.id = t.creatorId)

    LEFT JOIN agent a

        ON (a.id = t.assigneeId)

    <where>

    <if test="ticketId != null">

        AND t.id = #{ticketId}

    </if>

    <if test="ticketAssigneeIds != null">

        AND t.assigneeId IN 

        <foreach collection="ticketAssigneeIds" item="ticketAssigneeId"

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

            #{ticketAssigneeId}

        </foreach>

    </if>

    <if test="customerId != null">

        AND t.customerId = #{customerId}

    </if>

    <if test="startTimestamp != null">

        AND t.openDate > #{startTimestamp}

    </if>

    <if test="endTimestamp != null">

        AND t.openDate < #{endTimestamp}

    </if>

    <if test="customerEmailId != null">

        AND t.customerEmailId = #{customerEmailId}

    </if>

    <if test="customerMobileNumber != null">

        AND t.customerMobileNumber = #{customerMobileNumber}

    </if>

    <if test="ticketStatus != null">

        AND status = #{ticketStatus}

    </if>

    <if test="ticketPriority != null">

        AND priority = #{ticketPriority}

    </if>

    <if test="ticketCategory != null">

        AND category = #{ticketCategory}

    </if>

    </where>

    ORDER BY t.openDate DESC

  </select>

</mapper>