· 8 years ago · Feb 13, 2018, 08:30 PM
1DROP FUNCTION IF EXISTS public.full_ticket(INTEGER, INTEGER);
2
3/*
4 * Argumento: INTEGER
5 * Valor de retorno: TABLE
6 * Función: Retorna toda la data necesaria para construir el formulario del ticket
7 */
8
9CREATE OR REPLACE FUNCTION public.full_ticket(IN p_ticket_id INTEGER, IN p_user_id INTEGER)
10 RETURNS TABLE(
11 id INTEGER,
12 parent_id INTEGER,
13 created_by_id INTEGER,
14 department_id INTEGER,
15 room_id INTEGER,
16 assigned_to_id INTEGER,
17 ticket_type_id INTEGER,
18 ticket_state_id INTEGER,
19 priority_id INTEGER,
20 severity_id INTEGER,
21 customer_id INTEGER,
22 title VARCHAR,
23 content TEXT,
24 deadline VARCHAR,
25 is_archived BOOLEAN,
26 created_at VARCHAR,
27 updated_at VARCHAR,
28 timer JSONB,
29 departments JSONB,
30 rooms JSONB,
31 coworkers JSONB,
32 ticket_types JSONB,
33 ticket_states JSONB,
34 priorities JSONB,
35 severities JSONB,
36 files JSONB,
37 tasks JSONB,
38 call JSONB,
39 contacts JSONB,
40 places JSONB,
41 parents JSONB
42 ) AS
43$BODY$
44DECLARE
45 v_created_by_id INTEGER;
46 v_department_id INTEGER;
47 v_room_id INTEGER;
48 v_ticket_type_id INTEGER;
49 query TEXT := '';
50BEGIN
51 IF p_ticket_id IS NULL THEN
52 RAISE EXCEPTION 'Valor nulo para el ticket'
53 USING HINT = 'Por favor ingrese el ID del ticket';
54 END IF;
55
56 SELECT
57 COALESCE(t.created_by_id, 0)::INTEGER AS department_id,
58 COALESCE(t.department_id, 0)::INTEGER AS department_id,
59 COALESCE(t.room_id, 0)::INTEGER AS department_id,
60 COALESCE(t.ticket_type_id, 0)::INTEGER AS ticket_type_id
61 FROM tickets t
62 WHERE t.id = p_ticket_id
63 INTO v_created_by_id, v_department_id, v_room_id, v_ticket_type_id;
64
65 query := '
66 WITH RECURSIVE parents AS(
67 SELECT
68 t.id
69 FROM tickets t
70 WHERE t.id = ' || p_ticket_id || '
71 UNION
72 SELECT
73 c.parent_id
74 FROM tickets c
75 INNER JOIN parents p ON p.id = c.id
76 ), timer_last_call AS(
77 SELECT
78 tc.call_id,
79 row_to_json(tc.*)::JSONB AS timer_last_call
80 FROM timer_calls tc
81 GROUP BY tc.call_id, tc.*
82 ), last_call AS(
83 SELECT
84 c.id,
85 c.call_type_id,
86 c.ticket_id,
87 c.user_id,
88 c.title,
89 c.description,
90 c.schedule,
91 c.its_done,
92 c.created_at,
93 c.updated_at,
94 COALESCE(tlc.timer_last_call, ''{}'')::JSONB AS timer_last_call
95 FROM calls c
96 LEFT JOIN timer_last_call tlc ON c.id = tlc.call_id
97 WHERE created_at = (
98 SELECT
99 MAX(created_at)
100 FROM calls
101 WHERE
102 ticket_id = ' || p_ticket_id || ' AND
103 user_id = ' || p_user_id || ' AND
104 its_done = False
105 )
106 ORDER BY created_at DESC
107 LIMIT 1
108 ), ticket_timer AS(
109 SELECT
110 *
111 FROM timer_tickets_list ttl
112 WHERE ttl.ticket_id = ' || p_ticket_id || ' AND ttl.user_id = ' || p_user_id || '
113 ), all_users AS(
114 SELECT
115 au.id AS user_id,
116 au.first_name,
117 au.last_name,
118 au.username,
119 au.first_name,
120 au.last_name,
121 up.profile_image_url
122 FROM auth_user au
123 LEFT JOIN user_profiles up ON au.id = up.user_id
124 ), all_rooms AS(
125 SELECT
126 ur.department_id,
127 array_to_json(array_agg(ur.*))::JSONB AS rooms
128 FROM user_rooms('|| v_created_by_id ||', ' || v_department_id || ') ur
129 GROUP BY ur.department_id
130 ), all_ticket_types AS(
131 SELECT
132 tt.id,
133 tt.ticket_type_name,
134 tt.description,
135 tt.status,
136 i.class_name AS icon
137 FROM ticket_types tt
138 LEFT JOIN icons i ON tt.icon_id = i.id
139 ), group_ticket_types AS(
140 SELECT
141 ttd.department_id,
142 array_to_json(array_agg(att.*))::JSONB AS ticket_types
143 FROM all_ticket_types att
144 RIGHT JOIN ticket_types_departments ttd ON att.id = ttd.ticket_type_id
145 GROUP BY ttd.department_id
146 ), all_ticket_states AS(
147 SELECT
148 ts.id,
149 ts.ticket_state_name,
150 ts.color AS background_color,
151 contrast_yiq(ts.color) AS font_color,
152 ts.weight,
153 ts.protect,
154 ts.ticket_open_time,
155 ts.ticket_without_movement_time,
156 ts.entity_id,
157 ts.ticket_state_type_id,
158 ts.ticket_type_department_id
159 FROM ticket_states ts
160 RIGHT JOIN ticket_types_departments ttd ON ts.ticket_type_department_id = ttd.id
161 WHERE ttd.department_id = ' || v_department_id || ' AND ttd.ticket_type_id = ' || v_ticket_type_id || '
162 ORDER BY ts.weight
163 ), all_files AS(
164 SELECT
165 f.id,
166 f.filename,
167 f.filepath,
168 f.filesize,
169 f.filemime,
170 f.created_at,
171 f.updated_at,
172 f.ticket_id
173 FROM files f
174 ORDER BY created_at DESC
175 ), group_files AS(
176 SELECT
177 af.ticket_id,
178 array_to_json(array_agg(af.*))::JSONB AS files
179 FROM all_files af
180 GROUP BY af.ticket_id
181 ), ticket_tasks_timer AS(
182 SELECT
183 tttl.ticket_task_id,
184 COALESCE(row_to_json(tttl.*)::JSONB, ''{}'')::JSONB AS ticket_tasks_timer
185 FROM timer_ticket_tasks_list tttl
186 WHERE tttl.user_id = ' || p_user_id || '
187 GROUP BY tttl.ticket_task_id, tttl.*
188 ), tasks AS(
189 SELECT
190 tt.id,
191 tt.ticket_id,
192 tt.title,
193 tt.weight,
194 tt.its_done,
195 ttt.ticket_tasks_timer
196 FROM ticket_tasks tt
197 LEFT JOIN ticket_tasks_timer ttt ON tt.id = ttt.ticket_task_id
198 WHERE tt.ticket_id = ' || p_ticket_id || '
199 ORDER BY tt.weight
200 )
201 SELECT
202 t.id,
203 t.parent_id,
204 t.created_by_id,
205 t.department_id,
206 t.room_id,
207 t.assigned_to_id,
208 t.ticket_type_id,
209 t.ticket_state_id,
210 t.priority_id,
211 t.severity_id,
212 t.customer_id,
213 t.title,
214 t.content,
215 to_char(t.deadline, ''YYYY-MM-DD HH24:MI:SS'')::VARCHAR AS deadline,
216 t.is_archived,
217 to_char(t.created_at, ''YYYY-MM-DD HH24:MI:SS'')::VARCHAR AS created_at,
218 to_char(t.updated_at, ''YYYY-MM-DD HH24:MI:SS'')::VARCHAR AS updated_at,
219 (SELECT COALESCE(row_to_json(tt.*)::JSONB, ''{}'')::JSONB FROM ticket_timer tt),
220 (SELECT COALESCE(array_to_json(array_agg(d.*))::JSONB, ''[]'')::JSONB FROM departments d),
221 COALESCE(ar.rooms, ''[]'')::JSONB AS rooms,
222 (SELECT COALESCE(array_to_json(array_agg(c.*))::JSONB, ''[]'')::JSONB AS coworkers FROM coworkers(' || v_created_by_id || ', ' || v_department_id ||', ' || v_room_id || ') c),
223 COALESCE(gtt.ticket_types, ''[]'')::JSONB AS ticket_types,
224 (SELECT COALESCE(array_to_json(array_agg(ats.*))::JSONB, ''[]'')::JSONB FROM all_ticket_states ats),
225 (SELECT COALESCE(array_to_json(array_agg(p.*))::JSONB, ''[]'')::JSONB AS priorities FROM priorities p),
226 (SELECT COALESCE(array_to_json(array_agg(s.*))::JSONB, ''[]'')::JSONB AS severities FROM severities s),
227 COALESCE(gf.files, ''[]'')::JSONB AS files,
228 (SELECT COALESCE(array_to_json(array_agg(t.*))::JSONB, ''[]'')::JSONB AS tasks FROM tasks t),
229 (SELECT COALESCE(row_to_json(lc.*)::JSONB, ''{}'')::JSONB AS call FROM last_call lc),
230 (SELECT rcl.contacts FROM recommended_contacts_list rcl WHERE user_id = ' || p_user_id || '),
231 (SELECT COALESCE(array_to_json(array_agg(json_build_object(''id'', p.id, ''place'', p.place)))::JSONB, ''[]'')::JSONB AS places FROM places p WHERE p.user_id = ' || p_user_id || '),
232 (SELECT COALESCE(array_to_json(array_agg(p.*))::JSONB, ''[]''):: JSONB as parents FROM parents p WHERE p.id IS NOT NULL)
233 FROM tickets t
234 LEFT JOIN all_rooms ar ON t.department_id = ar.department_id
235 LEFT JOIN group_ticket_types gtt ON t.department_id = gtt.department_id
236 LEFT JOIN group_files gf ON t.id = gf.ticket_id
237 WHERE t.id = ' || p_ticket_id;
238
239 -- RAISE NOTICE 'created_by_id: %', v_created_by_id;
240 -- RAISE NOTICE 'department_id: %', v_department_id;
241 -- RAISE NOTICE 'room_id: %', v_room_id;
242 -- RAISE NOTICE 'ticket_type_id: %', v_ticket_type_id;
243 -- RAISE NOTICE '%', query;
244 RETURN QUERY EXECUTE query;
245END;
246$BODY$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER COST 10 ROWS 1000;
247
248COMMENT ON FUNCTION check_assigned_to(INTEGER) IS 'Retorna toda la data necesaria para construir el formulario del ticket';