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
44
45
46
47
48
| public query function findRawAudit( string entity, numeric userId, date dateFrom, date dateTo, numeric timeZoneOffset ) {
var newLine = CHR(13) & CHR(10);
var params = {};
var sql = "
select a.entity, a.entityId, a.timestamp, a.transaction, a.attribute, a.value as previousValue, u.name as username, null as currentValue, null as auditName, null as asql
from Audit a inner join User u
on a.userId = u.userId
where 0=0
";
if ( structKeyExists(arguments, 'entity') and len( arguments.entity ) ) {
sql &= newLine & "and entity = :entity";
structInsert(params, "entity", { value: arguments.entity, cfsqltype: "cf_sql_varchar" } );
}
if ( structKeyExists(arguments, 'userId') ) {
sql &= newLine & "and a.userId = :userId";
structInsert(params, "userId", { value: arguments.userId, cfsqltype: "cf_sql_integer" } );
}
if ( structKeyExists(arguments, 'dateFrom') ) {
sql &= newLine & "and timestamp > :dateFrom";
// adjust dateFrom to account for time zone difference between server and user
var dateFromUTC = DateAdd( 'h', -arguments.timeZoneOffset, arguments.dateFrom );
structInsert(params, "dateFrom", { value: arguments.dateFrom, cfsqltype: "cf_sql_timestamp" } );
}
if ( structKeyExists(arguments, 'dateTo') ) {
sql &= newLine & "and timestamp < :dateTo";
// adjust dateFrom to account for time zone difference between server and user
var dateToUTC = DateAdd( 'h', -arguments.timeZoneOffset, arguments.dateTo );
// we add a day to dateTo to arrive at an effectiveEndDateTime, namely midnight of the endDate
// so that the endDate is inclusive, as users would very likely expect
dateToUTC = dateAdd( 'd', 1, dateToUTC );
// stdout.println( "dateToUTC : #dateToUTC #" );
structInsert(params, "dateTo", { value: dateToUTC, cfsqltype: "cf_sql_timestamp" } );
}
sql &= newLine & "and a.attribute != 'version' ";
sql &= newLine & "and a.attribute != 'lastEdited' ";
sql &= newLine & "and a.attribute != 'userId' ";
sql &= newLine & "order by a.timestamp desc, a.transaction";
return queryExecute( sql, params );
}
|