Subversion Repositories SmartDukaan

Rev

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

Rev 3499 Rev 3546
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>
 - 
 
22 
  </resultMap>
 8 
  </resultMap>
23 
 
 9 
 
24 
  <insert id="insertTicket" parameterType="ticket" useGeneratedKeys="true" keyProperty="id">
 10 
  <insert id="insertTicket" parameterType="ticket" useGeneratedKeys="true" keyProperty="id">
25 
    INSERT INTO ticket
 11 
    INSERT INTO ticket
26 
      (customerId, openDate, closeDate, creatorId, description, assigneeId, status,
 12 
      (customerId, openDate, closeDate, creatorId, description, assigneeId, status,
Line 73... Line 59...
73 
  </update>
 59 
  </update>
74 
 
 60 
 
75 
  <select id="getUnassignedTickets" resultType="ticket">
 61 
  <select id="getUnassignedTickets" resultType="ticket">
76 
    SELECT * FROM ticket
 62 
    SELECT * FROM ticket
77 
    WHERE assigneeId IS NULL
 63 
    WHERE assigneeId IS NULL
- 
 
 64 
    AND category != 'COD_VERIFICATION'
78 
    ORDER BY openDate DESC
 65 
    ORDER BY openDate DESC
79 
  </select>
 66 
  </select>
80 
 
 67 
 
81 
  <select id="getTickets" parameterType="searchFilter" resultType="ticket">
 68 
  <select id="getTickets" parameterType="searchFilter" resultType="ticket">
82 
    SELECT t.*,
 69 
    SELECT *
83 
    c.id AS creator_id,
 - 
 
84 
    c.name AS creator_name,
 - 
 
85 
    c.emailId AS creator_emailId,
 - 
 
86 
    c.password AS creator_password,
 - 
 
87 
    c.managerId AS creator_managerId,
 - 
 
88 
    a.id AS assignee_id,
 - 
 
89 
    a.name AS assignee_name,
 - 
 
90 
    a.emailId AS assignee_emailId,
 - 
 
91 
    a.password AS assignee_password,
 - 
 
92 
    a.managerId AS assignee_managerId
 - 
 
93 
    FROM ticket t
 70 
    FROM ticket
94 
    LEFT JOIN agent c
 - 
 
95 
        ON (c.id = t.creatorId)
 - 
 
96 
    LEFT JOIN agent a
 - 
 
97 
        ON (a.id = t.assigneeId)
 - 
 
98 
    <where>
 71 
    <where>
99 
    <if test="ticketId != null">
 72 
    <if test="ticketId != null">
100 
        AND t.id = #{ticketId}
 73 
        AND id = #{ticketId}
101 
    </if>
 74 
    </if>
102 
    <if test="ticketAssigneeIds != null">
 75 
    <if test="ticketAssigneeIds != null">
103 
        AND t.assigneeId IN
76 
        AND assigneeId IN
104 
        <foreach collection="ticketAssigneeIds" item="ticketAssigneeId"
 77 
        <foreach collection="ticketAssigneeIds" item="ticketAssigneeId"
105 
                 index="index" open="(" close=")" separator=",">
 78 
                 index="index" open="(" close=")" separator=",">
106 
            #{ticketAssigneeId}
 79 
            #{ticketAssigneeId}
107 
        </foreach>
 80 
        </foreach>
108 
    </if>
 81 
    </if>
109 
    <if test="customerId != null">
 82 
    <if test="customerId != null">
110 
        AND t.customerId = #{customerId}
 83 
        AND customerId = #{customerId}
111 
    </if>
 84 
    </if>
112 
    <if test="startTimestamp != null">
 85 
    <if test="startTimestamp != null">
113 
        AND t.openDate &gt; #{startTimestamp}
 86 
        AND openDate &gt; #{startTimestamp}
114 
    </if>
 87 
    </if>
115 
    <if test="endTimestamp != null">
 88 
    <if test="endTimestamp != null">
116 
        AND t.openDate &lt; #{endTimestamp}
 89 
        AND openDate &lt; #{endTimestamp}
117 
    </if>
 90 
    </if>
118 
    <if test="customerEmailId != null">
 91 
    <if test="customerEmailId != null">
119 
        AND t.customerEmailId = #{customerEmailId}
 92 
        AND customerEmailId = #{customerEmailId}
120 
    </if>
 93 
    </if>
121 
    <if test="customerMobileNumber != null">
 94 
    <if test="customerMobileNumber != null">
122 
        AND t.customerMobileNumber = #{customerMobileNumber}
 95 
        AND customerMobileNumber = #{customerMobileNumber}
123 
    </if>
 96 
    </if>
124 
    <if test="ticketStatus != null">
 97 
    <if test="ticketStatuses != null">
- 
 
 98 
        AND status IN
- 
 
 99 
        <foreach collection="ticketStatuses" item="ticketStatus"
- 
 
 100 
                 index="index" open="(" close=")" separator=",">
125 
        AND status = #{ticketStatus}
 101 
            #{ticketStatus}
- 
 
 102 
        </foreach>
126 
    </if>
 103 
    </if>
127 
    <if test="ticketPriority != null">
 104 
    <if test="ticketPriority != null">
128 
        AND priority = #{ticketPriority}
 105 
        AND priority = #{ticketPriority}
129 
    </if>
 106 
    </if>
130 
    <if test="ticketCategory != null">
 107 
    <if test="ticketCategory != null">
131 
        AND category = #{ticketCategory}
 108 
        AND category = #{ticketCategory}
132 
    </if>
 109 
    </if>
133 
    </where>
 110 
    </where>
134 
    ORDER BY t.openDate DESC
 111 
    ORDER BY openDate DESC
135 
  </select>
 112 
  </select>
136 
</mapper>
 113 
</mapper>