Subversion Repositories SmartDukaan

Rev

Rev 3269 | Rev 3499 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
3024 mandeep.dh 1 
<?xml version="1.0" encoding="UTF-8"?>
2 
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
3 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
4 
 
5 
<mapper namespace="in.shop2020.crm.persistence.TicketMapper">
6 
  <resultMap type="ticket" id="ticketResult">
7 
    <id property="id" column="id"/>
3390 mandeep.dh 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>
3024 mandeep.dh 22 
  </resultMap>
23 
 
24 
  <insert id="insertTicket" parameterType="ticket" useGeneratedKeys="true" keyProperty="id">
25 
    INSERT INTO ticket
3106 mandeep.dh 26 
      (customerId, openDate, closeDate, creatorId, description, assigneeId, status,
3390 mandeep.dh 27 
       priority, category, productName, airwayBillNo, orderId, customerEmailId,
28 
       customerMobileNumber, customerName)
3024 mandeep.dh 29 
    VALUES
3106 mandeep.dh 30 
      (#{customerId}, NOW(),  #{closeDate},  #{creatorId}, #{description}, #{assigneeId}, #{status},
3390 mandeep.dh 31 
       #{priority}, #{category}, #{productName}, #{airwayBillNo}, #{orderId}, #{customerEmailId},
32 
       #{customerMobileNumber}, #{customerName})
3024 mandeep.dh 33 
  </insert>
3390 mandeep.dh 34 
 
3024 mandeep.dh 35 
  <update id="updateTicket" parameterType="ticket">
36 
    UPDATE ticket
37 
    SET
3106 mandeep.dh 38 
    <if test="assigneeId != null">
39 
      assigneeId = #{assigneeId},
3024 mandeep.dh 40 
    </if>
3106 mandeep.dh 41 
    <if test="status != null">
42 
      status = #{status},
43 
    </if>
44 
    <if test="priority != null">
45 
      priority = #{priority},
46 
    </if>
47 
    <if test="category != null">
48 
      category = #{category},
49 
    </if>
50 
    <if test="description != null">
51 
      description = #{description},
52 
    </if>
3390 mandeep.dh 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>
3024 mandeep.dh 62 
    closeDate = #{closeDate}
63 
    WHERE id = #{id}
64 
  </update>
65 
 
3137 mandeep.dh 66 
  <select id="getUnassignedTickets" resultType="ticket">
67 
    SELECT * FROM ticket
68 
    WHERE assigneeId IS NULL
69 
    ORDER BY openDate DESC
70 
  </select>
3390 mandeep.dh 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>
3137 mandeep.dh 118 
</mapper>