Subversion Repositories SmartDukaan

Rev

Rev 7636 | 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"/>
8 
  </resultMap>
9 
 
10 
  <insert id="insertTicket" parameterType="ticket" useGeneratedKeys="true" keyProperty="id">
11 
    INSERT INTO ticket
3106 mandeep.dh 12 
      (customerId, openDate, closeDate, creatorId, description, assigneeId, status,
3390 mandeep.dh 13 
       priority, category, productName, airwayBillNo, orderId, customerEmailId,
14 
       customerMobileNumber, customerName)
3024 mandeep.dh 15 
    VALUES
3106 mandeep.dh 16 
      (#{customerId}, NOW(),  #{closeDate},  #{creatorId}, #{description}, #{assigneeId}, #{status},
3390 mandeep.dh 17 
       #{priority}, #{category}, #{productName}, #{airwayBillNo}, #{orderId}, #{customerEmailId},
18 
       #{customerMobileNumber}, #{customerName})
3024 mandeep.dh 19 
  </insert>
4793 amar.kumar 20 
 
21 
  <update id="unassignAgentTickets" parameterType="int">
22 
  	UPDATE ticket
23 
  	SET assigneeId = NULL
24 
  	WHERE	assigneeId = #{assigneeId}
25 
  		AND status != 2
26 
 
27 
  </update>
28 
 
3024 mandeep.dh 29 
  <update id="updateTicket" parameterType="ticket">
30 
    UPDATE ticket
31 
    SET
3106 mandeep.dh 32 
    <if test="assigneeId != null">
33 
      assigneeId = #{assigneeId},
3024 mandeep.dh 34 
    </if>
3106 mandeep.dh 35 
    <if test="status != null">
36 
      status = #{status},
37 
    </if>
38 
    <if test="priority != null">
39 
      priority = #{priority},
40 
    </if>
41 
    <if test="category != null">
42 
      category = #{category},
43 
    </if>
44 
    <if test="description != null">
45 
      description = #{description},
46 
    </if>
3390 mandeep.dh 47 
    <if test="customerEmailId != null">
48 
      customerEmailId = #{customerEmailId},
49 
    </if>
50 
    <if test="customerName != null">
51 
      customerName = #{customerName},
52 
    </if>
53 
    <if test="customerMobileNumber != null">
54 
      customerMobileNumber = #{customerMobileNumber},
55 
    </if>
3024 mandeep.dh 56 
    closeDate = #{closeDate}
57 
    WHERE id = #{id}
58 
  </update>
59 
 
3137 mandeep.dh 60 
  <select id="getUnassignedTickets" resultType="ticket">
61 
    SELECT * FROM ticket
62 
    WHERE assigneeId IS NULL
63 
    ORDER BY openDate DESC
64 
  </select>
3390 mandeep.dh 65 
 
5168 amar.kumar 66 
  <select id="getOpenTicketCountForAgent" parameterType="long" resultType="int">
67 
  	SELECT count(*) FROM ticket
68 
  	WHERE assigneeId = #{agentId}
69 
  		AND status != 2
70 
  </select>
3390 mandeep.dh 71 
  <select id="getTickets" parameterType="searchFilter" resultType="ticket">
3546 mandeep.dh 72 
    SELECT *
73 
    FROM ticket
3390 mandeep.dh 74 
    <where>
75 
    <if test="ticketId != null">
3546 mandeep.dh 76 
        AND id = #{ticketId}
3390 mandeep.dh 77 
    </if>
78 
    <if test="ticketAssigneeIds != null">
3546 mandeep.dh 79 
        AND assigneeId IN
3390 mandeep.dh 80 
        <foreach collection="ticketAssigneeIds" item="ticketAssigneeId"
81 
                 index="index" open="(" close=")" separator=",">
82 
            #{ticketAssigneeId}
83 
        </foreach>
84 
    </if>
85 
    <if test="customerId != null">
3546 mandeep.dh 86 
        AND customerId = #{customerId}
3390 mandeep.dh 87 
    </if>
88 
    <if test="startTimestamp != null">
3546 mandeep.dh 89 
        AND openDate &gt; #{startTimestamp}
3390 mandeep.dh 90 
    </if>
91 
    <if test="endTimestamp != null">
3546 mandeep.dh 92 
        AND openDate &lt; #{endTimestamp}
3390 mandeep.dh 93 
    </if>
94 
    <if test="customerEmailId != null">
3546 mandeep.dh 95 
        AND customerEmailId = #{customerEmailId}
3390 mandeep.dh 96 
    </if>
97 
    <if test="customerMobileNumber != null">
3546 mandeep.dh 98 
        AND customerMobileNumber = #{customerMobileNumber}
3390 mandeep.dh 99 
    </if>
3546 mandeep.dh 100 
    <if test="ticketStatuses != null">
101 
        AND status IN
102 
        <foreach collection="ticketStatuses" item="ticketStatus"
103 
                 index="index" open="(" close=")" separator=",">
104 
            #{ticketStatus}
105 
        </foreach>
3499 mandeep.dh 106 
    </if>
107 
    <if test="ticketPriority != null">
108 
        AND priority = #{ticketPriority}
109 
    </if>
110 
    <if test="ticketCategory != null">
111 
        AND category = #{ticketCategory}
112 
    </if>
14882 manish.sha 113 
    <if test="ticketCategoryList != null">
114 
    	AND category IN
115 
    	<foreach collection="ticketCategoryList" item="tkCategory"
116 
    			 index="index" open="(" close=")" separator=",">
117 
    		#{tkCategory}
118 
    	</foreach>
119 
    </if>
3390 mandeep.dh 120 
    </where>
4142 mandeep.dh 121 
    ORDER BY openDate
3390 mandeep.dh 122 
  </select>
5909 amar.kumar 123 
 
124 
  <select id="getOpenTicketsMap" resultType="map">
125 
  	SELECT category, count(*) as count
126 
  	FROM ticket
127 
  	<where>
128 
  		status IN ('OPEN','REOPEN')
7636 manish.sha 129 
  		AND category IS NOT NULL
5909 amar.kumar 130 
  	</where>
131 
  	group by category
132 
  </select >
3137 mandeep.dh 133 
</mapper>