Subversion Repositories SmartDukaan

Rev

Rev 3269 | Rev 3499 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 3269 Rev 3390
Line 3... Line 3...
3 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
4 
 
 4 
 
5 
<mapper namespace="in.shop2020.crm.persistence.TicketMapper">
 5 
<mapper namespace="in.shop2020.crm.persistence.TicketMapper">
6 
  <resultMap type="ticket" id="ticketResult">
 6 
  <resultMap type="ticket" id="ticketResult">
7 
    <id property="id" column="id"/>
 7 
    <id property="id" column="id"/>
- 
 
 8 
    <association property="creator" column="creatorId" javaType="agent">
- 
 
 9 
        <id property="id" column="creator_id"/>
- 
 
 10 
        <result property="name" column="creator_name"/>
- 
 
 11 
        <result property="emailId" column="creator_emailId"/>
- 
 
 12 
        <result property="password" column="creator_password"/>
- 
 
 13 
        <result property="managerId" column="creator_managerId"/>
- 
 
 14 
    </association>
- 
 
 15 
    <association property="assignee" column="assigneeId" javaType="agent">
- 
 
 16 
        <id property="id" column="assignee_id"/>
- 
 
 17 
        <result property="name" column="assignee_name"/>
- 
 
 18 
        <result property="emailId" column="assignee_emailId"/>
- 
 
 19 
        <result property="password" column="assignee_password"/>
- 
 
 20 
        <result property="managerId" column="assignee_managerId"/>
- 
 
 21 
    </association>
8 
  </resultMap>
 22 
  </resultMap>
9 
 
 23 
 
10 
  <insert id="insertTicket" parameterType="ticket" useGeneratedKeys="true" keyProperty="id">
 24 
  <insert id="insertTicket" parameterType="ticket" useGeneratedKeys="true" keyProperty="id">
11 
    INSERT INTO ticket
 25 
    INSERT INTO ticket
12 
      (customerId, openDate, closeDate, creatorId, description, assigneeId, status,
 26 
      (customerId, openDate, closeDate, creatorId, description, assigneeId, status,
13 
       priority, category, productName, airwayBillNo, orderId, customerEmailId)
 27 
       priority, category, productName, airwayBillNo, orderId, customerEmailId,
- 
 
 28 
       customerMobileNumber, customerName)
14 
    VALUES
 29 
    VALUES
15 
      (#{customerId}, NOW(),  #{closeDate},  #{creatorId}, #{description}, #{assigneeId}, #{status},
 30 
      (#{customerId}, NOW(),  #{closeDate},  #{creatorId}, #{description}, #{assigneeId}, #{status},
16 
       #{priority}, #{category}, #{productName}, #{airwayBillNo}, #{orderId}, #{customerEmailId})
 31 
       #{priority}, #{category}, #{productName}, #{airwayBillNo}, #{orderId}, #{customerEmailId},
- 
 
 32 
       #{customerMobileNumber}, #{customerName})
17 
  </insert>
 33 
  </insert>
18
34 
 
19 
  <update id="updateTicket" parameterType="ticket">
 35 
  <update id="updateTicket" parameterType="ticket">
20 
    UPDATE ticket
 36 
    UPDATE ticket
21 
    SET
 37 
    SET
22 
    <if test="assigneeId != null">
 38 
    <if test="assigneeId != null">
23 
      assigneeId = #{assigneeId},
 39 
      assigneeId = #{assigneeId},
Line 32... Line 48...
32 
      category = #{category},
 48 
      category = #{category},
33 
    </if>
 49 
    </if>
34 
    <if test="description != null">
 50 
    <if test="description != null">
35 
      description = #{description},
 51 
      description = #{description},
36 
    </if>
 52 
    </if>
- 
 
 53 
    <if test="customerEmailId != null">
- 
 
 54 
      customerEmailId = #{customerEmailId},
- 
 
 55 
    </if>
- 
 
 56 
    <if test="customerName != null">
- 
 
 57 
      customerName = #{customerName},
- 
 
 58 
    </if>
- 
 
 59 
    <if test="customerMobileNumber != null">
- 
 
 60 
      customerMobileNumber = #{customerMobileNumber},
- 
 
 61 
    </if>
37 
    closeDate = #{closeDate}
 62 
    closeDate = #{closeDate}
38 
    WHERE id = #{id}
 63 
    WHERE id = #{id}
39 
  </update>
 64 
  </update>
40 
 
 65 
 
41 
  <select id="getTickets" parameterType="long" resultType="ticket">
 - 
 
42 
    SELECT * FROM ticket
 - 
 
43 
    WHERE customerId = #{customerId}
 - 
 
44 
    ORDER BY openDate DESC
 - 
 
45 
  </select>
 - 
 
46
- 
 
47 
  <select id="getTicket" parameterType="long" resultType="ticket">
 - 
 
48 
    SELECT * FROM ticket
 - 
 
49 
    WHERE id = #{ticketId}
 - 
 
50 
  </select>
 - 
 
51
- 
 
52 
  <select id="getAssignedTickets" parameterType="long" resultType="ticket">
 - 
 
53 
    SELECT * FROM ticket
 - 
 
54 
    WHERE assigneeId = #{agentId}
 - 
 
55 
    ORDER BY openDate DESC
 - 
 
56 
  </select>
 - 
 
57 
 
 - 
 
58 
  <select id="getUnassignedTickets" resultType="ticket">
 66 
  <select id="getUnassignedTickets" resultType="ticket">
59 
    SELECT * FROM ticket
 67 
    SELECT * FROM ticket
60 
    WHERE assigneeId IS NULL
 68 
    WHERE assigneeId IS NULL
61 
    ORDER BY openDate DESC
 69 
    ORDER BY openDate DESC
62 
  </select>
 70 
  </select>
- 
 
 71 
 
- 
 
 72 
  <select id="getTickets" parameterType="searchFilter" resultType="ticket">
- 
 
 73 
    SELECT t.*,
- 
 
 74 
    c.id AS creator_id,
- 
 
 75 
    c.name AS creator_name,
- 
 
 76 
    c.emailId AS creator_emailId,
- 
 
 77 
    c.password AS creator_password,
- 
 
 78 
    c.managerId AS creator_managerId,
- 
 
 79 
    a.id AS assignee_id,
- 
 
 80 
    a.name AS assignee_name,
- 
 
 81 
    a.emailId AS assignee_emailId,
- 
 
 82 
    a.password AS assignee_password,
- 
 
 83 
    a.managerId AS assignee_managerId
- 
 
 84 
    FROM ticket t
- 
 
 85 
    LEFT JOIN agent c
- 
 
 86 
        ON (c.id = t.creatorId)
- 
 
 87 
    LEFT JOIN agent a
- 
 
 88 
        ON (a.id = t.assigneeId)
- 
 
 89 
    <where>
- 
 
 90 
    <if test="ticketId != null">
- 
 
 91 
        AND t.id = #{ticketId}
- 
 
 92 
    </if>
- 
 
 93 
    <if test="ticketAssigneeIds != null">
- 
 
 94 
        AND t.assigneeId IN
- 
 
 95 
        <foreach collection="ticketAssigneeIds" item="ticketAssigneeId"
- 
 
 96 
                 index="index" open="(" close=")" separator=",">
- 
 
 97 
            #{ticketAssigneeId}
- 
 
 98 
        </foreach>
- 
 
 99 
    </if>
- 
 
 100 
    <if test="customerId != null">
- 
 
 101 
        AND t.customerId = #{customerId}
- 
 
 102 
    </if>
- 
 
 103 
    <if test="startTimestamp != null">
- 
 
 104 
        AND t.openDate &gt; #{startTimestamp}
- 
 
 105 
    </if>
- 
 
 106 
    <if test="endTimestamp != null">
- 
 
 107 
        AND t.openDate &lt; #{endTimestamp}
- 
 
 108 
    </if>
- 
 
 109 
    <if test="customerEmailId != null">
- 
 
 110 
        AND t.customerEmailId = #{customerEmailId}
- 
 
 111 
    </if>
- 
 
 112 
    <if test="customerMobileNumber != null">
- 
 
 113 
        AND t.customerMobileNumber = #{customerMobileNumber}
- 
 
 114 
    </if>
- 
 
 115 
    </where>
- 
 
 116 
    ORDER BY t.openDate DESC
- 
 
 117 
  </select>
63 
</mapper>
 118 
</mapper>