/
Raw_Report.sql
43 lines (37 loc) · 1.13 KB
/
Raw_Report.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DELIMITER //
CREATE PROCEDURE Raw_Report_2 (IN dateFrom dateTime, IN dateTo dateTime, IN userId int)
BEGIN
IF userId > 0 THEN SET @SQLCOMM = '
SELECT users.UserName Usuario, fecha, hora, Actions.actionName Evento, station
FROM Activity
inner join Users users
on userId = users.id
inner join Actions
on Activity.actionId = Actions.id
where
fecha >= date_format(?, ''%d/%m/%Y'') and fecha <= date_format(?, ''%d/%m/%Y'') and Activity.userId = ?
Order by Activity.id';
PREPARE stmt1 FROM @SQLCOMM;
set @a = dateFrom;
set @b = dateTo;
set @c = userId;
EXECUTE stmt1 USING @a, @b, @c;
DEALLOCATE PREPARE stmt1;
ELSE SET @SQLCOMM = '
SELECT users.UserName Usuario, fecha, hora, Actions.actionName Evento, station
FROM Activity
inner join Users users
on userId = users.id
inner join Actions
on Activity.actionId = Actions.id
where
fecha >= date_format(?, ''%d/%m/%Y'') and fecha <= date_format(?, ''%d/%m/%Y'')
Order by Activity.userId, Activity.id';
PREPARE stmt1 FROM @SQLCOMM;
set @a = dateFrom;
set @b = dateTo;
EXECUTE stmt1 USING @a, @b;
DEALLOCATE PREPARE stmt1;
END IF;
END//
DELIMITER ;