aboutsummaryrefslogtreecommitdiffstats
path: root/vid/src/main/webapp/WEB-INF/conf/sql.properties
blob: ac52f3c95c7828087e8ec70b42a816ce510573d8 (plain)
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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
###
# ================================================================================
# eCOMP Portal SDK
# ================================================================================
# Copyright (C) 2017 AT&T Intellectual Property
# ================================================================================
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# 
#      http://www.apache.org/licenses/LICENSE-2.0
# 
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# ================================================================================
###
#SQL Statements for PostgreSQL

#ReportLoader.java

load.custom.report.xml = SELECT cr.report_xml FROM cr_report cr WHERE rep_id=?

# need to copy to oracle
load.remoteDB.schema = SELECT 'local' SCHEMA_ID, 'local' SCHEMA_DESC, null DATASOURCE_TYPE  FROM dual union SELECT a.SCHEMA_ID, a.SCHEMA_DESC, DATASOURCE_TYPE  FROM SCHEMA_INFO a  where schema_id <> 'local' order by schema_id
load.remoteDB.schema.where = SELECT a.SCHEMA_ID, a.SCHEMA_DESC, DATASOURCE_TYPE  FROM SCHEMA_INFO a where schema_id = '[schema_id]'
#formfield rendering

formfield.id.name.sql.prefix=SELECT id, name FROM (
formfield.id.name.sql=SELECT id, name

formfield.id.name.sql.suffix=

report.security.create= SELECT coalesce(cr.owner_id, cr.create_id) AS owner_id, cr.create_id,  DATE_FORMAT(cr.create_date, '%m/%d/%Y') create_date, maint_id,  DATE_FORMAT(cr.maint_date, '%m/%d/%Y')  update_date, cr.public_yn FROM cr_report cr WHERE cr.rep_id = [rw.getReportID()]

db.update.report.xml = SELECT cr.rep_id, cr.report_xml FROM cr_report cr WHERE rep_id=? FOR UPDATE

update.custom.report.rec = UPDATE cr_report SET title='[Utils.oracleSafe(rw.getReportName())]', descr='[Utils.oracleSafe(rw.getReportDescr())]', public_yn='[(rw.isPublic()]', menu_id='[rw.getMenuID()]', menu_approved_yn='[(rw.isMenuApproved()]', owner_id=[rw.getOwnerID()], maint_id=[rw.getUpdateID()], maint_date=STR_TO_DATE('[rw.getUpdateDate()]', '[Globals.getOracleTimeFormat()]'), dashboard_type_yn='[(rw.isDashboardType()]', dashboard_yn= '[(rw.getReportType().equals(AppConstants.RT_DASHBOARD)]' WHERE rep_id = [rw.getReportID()]

is.report.already.scheduled = select rep_id from cr_report_schedule where rep_id = ?

create.custom.report.rec = INSERT INTO cr_report(rep_id, title, descr, public_yn, menu_id, menu_approved_yn, report_xml, owner_id, create_id, create_date, maint_id, maint_date, dashboard_type_yn, dashboard_yn, folder_id) VALUES([rw.getReportID()], '[Utils.oracleSafe(rw.getReportName())]', '[Utils.oracleSafe(rw.getReportDescr())]', '[rw.isPublic()]', '[rw.getMenuID()]', '[rw.isMenuApproved()]', '<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>', [rw.getOwnerID()], [rw.getCreateID()], STR_TO_DATE('[rw.getCreateDate()]', '[Globals.getOracleTimeFormat()]'), [rw.getUpdateID()], STR_TO_DATE('[rw.getUpdateDate()]', '[Globals.getOracleTimeFormat()]'), '[rw.isDashboardType()]', '[rw.getReportType().equals(AppConstants.RT_DASHBOARD)]',[rw.getFolderId()]) 

get.user.report.names = SELECT cr.rep_id, cr.title FROM cr_report cr WHERE coalesce(cr.owner_id, cr.create_id) = [userID]							
								
get.report.owner.id = SELECT coalesce(cr.owner_id, cr.create_id) AS owner FROM cr_report cr WHERE rep_id = ?						

delete.report.record.log = DELETE FROM cr_report_log WHERE rep_id = [reportID]

delete.report.record.users = DELETE FROM cr_report_schedule_users WHERE rep_id = [reportID]

delete.report.record.schedule = DELETE FROM cr_report_schedule WHERE rep_id = [reportID]

delete.report.record.access = DELETE FROM cr_report_access WHERE rep_id = [reportID]

delete.report.record.email = DELETE FROM cr_report_email_sent_log WHERE rep_id = [reportID]

delete.report.record.favorite = DELETE FROM cr_favorite_reports WHERE rep_id = [reportID]

delete.report.record.report = DELETE FROM cr_report WHERE rep_id = [reportID]
																						
load.quick.links =  select finalcr.rep_id, finalcr.title, finalcr.descr from (SELECT cr.rep_id, cr.title, cr.descr FROM (SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra LEFT JOIN cr_report cr ON cr.rep_id = ra.rep_id WHERE cr.menu_id LIKE '%[nvls(menuId)]%' AND cr.menu_approved_yn = 'Y' AND (coalesce(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL) UNION SELECT cr.rep_id, cr.title, cr.descr FROM cr_report cr WHERE cr.menu_id LIKE '%[nvls(menuId)]%' AND (coalesce(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' or EXISTS (select * from fn_user_role where user_id=[userID] and role_id in (1)))) finalcr ORDER BY finalcr.title

load.folder.reports = SELECT cr.rep_id, cr.rep_id report_id, concat([rep_title_sql] , (CASE WHEN cr.public_yn = 'Y' THEN '' ELSE '[PRIVATE_ICON]' END),cr.title,'</a>') title, cr.descr, concat(au.first_name,' ',au.last_name) owner_name, DATE_FORMAT(cr.create_date, '%m/%d/%Y') create_date, CASE WHEN coalesce(cr.owner_id, cr.create_id) = [userID] THEN 'N' ELSE coalesce(ra.read_only_yn, 'Y') END read_only_yn, CASE WHEN coalesce(cr.owner_id, cr.create_id) = [userID] THEN 'Y' ELSE 'N' END user_is_owner_yn FROM cr_report cr JOIN app_user au ON coalesce(cr.owner_id, cr.create_id) = au.user_id AND cr.folder_id= '[folderId]' LEFT JOIN (SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra ON cr.rep_id = ra.rep_id
#If roleList.toString() is '' PostgreSQL returns an error - needs to be null instead of empty

load.folder.reports.user = AND coalesce(cr.owner_id, cr.create_id) = [userID]	

load.folder.reports.publicsql = AND (coalesce(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL)

load.quick.download.links = SELECT a.file_name, b.title,DATE_FORMAT(a.dwnld_start_time, '%W %d-%m-%Y %H:%i:%s') as time, a.dwnld_start_time FROM cr_report_dwnld_log a, cr_report b where a.user_id = [userID] and a.rep_id = b.rep_id and (a.dwnld_start_time) >= STR_TO_DATE(DATE_FORMAT(now() -  INTERVAL 1 DAY, '%m/%d/%Y'), '%m/%d/%Y') and a.record_ready_time is not null order by a.dwnld_start_time

load.reports.to.schedule = SELECT cr.rep_id, Initcap(cr.title), cr.descr FROM cr_report cr LEFT OUTER JOIN (SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra ON cr.rep_id = ra.rep_id AND (cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL or cr.owner_id = [userID] )ORDER BY Initcap(cr.title)
		
load.reports.to.add.in.dashboard = SELECT cr.rep_id, cr.title, cr.descr FROM cr_report cr LEFT OUTER JOIN (SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra ON cr.rep_id = ra.rep_id AND (coalesce(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL) AND (cr.dashboard_yn = 'N' or cr.dashboard_yn is null) ORDER BY cr.title

load.my.recent.links = select rep_id, title, descr, form_fields from ( select rep_id, title, descr, form_fields from  (select cr.rep_id, cr.title, a.form_fields,  cr.descr, a.log_time, a.user_id, a.action, a.action_value from cr_report_log a, cr_report cr where user_id = [userID] AND action = 'Report Execution Time' and a.rep_id = cr.rep_id order by log_time desc) AS x) AS y where LIMIT 1, 6

create.report.log.entry = INSERT INTO cr_report_log (rep_id, log_time, user_id, action, action_value, form_fields) VALUES([reportID], now(), [userID], '[action]' , '[executionTime]', '[form_fields]')

create.report.log.entry.exec.time = INSERT INTO cr_report_log (rep_id, log_time, user_id, action, action_value, form_fields) VALUES([reportID], NOW() + INTERVAL 1 SECOND, [userID], '[action]' , '[executionTime]', '[formFields]')

clear.report.log.entries = DELETE FROM cr_report_log WHERE rep_id = ? and user_id = ?

load.report.log.entries = SELECT x.log_time, x.user_id, (CASE WHEN x.action = 'Report Execution Time' THEN  concat('<a href=\"[AppUtils.getRaptorActionURL()]report.run.container&c_master=',x.rep_id,'&',x.form_fields,'&fromReportLog=Y&display_content=Y&noFormFields=Y&refresh=Y\">',x.action,'</a>') ELSE x.action END) action, (CASE WHEN x.action = 'Report Execution Time' THEN  action_value  ELSE 'N/A' END) time_taken, (CASE WHEN x.action = 'Report Execution Time' THEN concat('<a href=\"[AppUtils.getRaptorActionURL()]report.run.container&c_master=',x.rep_id,'&',x.form_fields,'&fromReportLog=Y&display_content=Y&noFormFields=Y&refresh=Y\"><img src=\"[AppUtils.getImgFolderURL()]test_run.gif" width=\"12\" height=\"12\" border=0 alt=\"Run report\"/></a>') ELSE 'N/A' END) run_image, x.name FROM  (SELECT rl.rep_id, DATE_FORMAT(rl.log_time, '%m/%d/%Y %h:%i:%s %p') log_time, rl.action_value, concat(fuser.last_name ,', ',fuser.first_name) name, rl.user_id, rl.action, rl.form_fields FROM cr_report_log rl, fn_user fuser WHERE rl.rep_id = [nvls(reportId)] and rl.action != 'Report Run' and fuser.user_id = rl.user_id ORDER BY rl.log_time DESC) x  LIMIT 100

does.user.can.schedule.report = select crs.sched_user_id, count(*) from cr_report_schedule crs where sched_user_id = [userId] group by crs.sched_user_id having count(*) >= [Globals.getScheduleLimit()]

does.user.can.schedule = select crs.schedule_id from cr_report_schedule crs where schedule_id = [scheduleId]

get.system.date.time = select DATE_FORMAT(now(),'%m/%d/%Y %H:%i:%s')

get.next.day.date.time = select DATE_FORMAT(NOW() + INTERVAL 1 DAY,'%m/%d/%Y %H:%i:%s')

get.next.fifteen.minutes.date.time = select DATE_FORMAT(NOW() + INTERVAL 15 MINUTES,'%m/%d/%Y %H:%i:%s')

get.next.thirty.minutes.date.time = select DATE_FORMAT(NOW() + INTERVAL 30 MINUTES,'%m/%d/%Y %H:%i:%s')

get.template.file = select template_file from cr_report_template_map where report_id = [reportId]

load.pdf.img.lookup = select image_id, image_loc from cr_raptor_pdf_img

load.action.img.lookup = select image_id, image_loc from cr_raptor_action_img


#ActionHandler.java

report.values.map.def.a = SELECT x FROM (SELECT DISTINCT 

report.values.map.def.b = TO_CHAR([colName], '[nvl(displayFormat, AppConstants.DEFAULT_DATE_FORMAT)]')

report.values.map.def.c = [colName] 

report.values.map.def.d =  x FROM [rdef.getTableById(tableId).getTableName()] WHERE [colName] IS NOT NULL ORDER BY 1) xx LIMIT <= [Globals.getDefaultPageSize()]

test.sched.cond.popup = SELECT 1 WHERE EXISTS ([sql])

download.all.email.sent = Select user_id, rep_id from CR_REPORT_EMAIL_SENT_LOG where gen_key='[pdfAttachmentKey.trim()]' and log_id =[report_email_sent_log_id.trim()] and (now() - sent_date) < '1 day' limit 1

download.all.gen.key = select schedule_id from cr_report_email_sent_log u where U.GEN_KEY = '[pdfAttachmentKey]'

download.all.retrieve = SELECT au.user_id FROM (SELECT rs.schedule_id, rs.rep_id FROM cr_report_schedule rs WHERE rs.enabled_yn='Y' AND rs.run_date IS NOT NULL  AND rs.schedule_id = [scheduleId]) x, cr_report r, app_user au WHERE x.rep_id = r.rep_id  AND au.user_id IN (SELECT rsu.user_id FROM cr_report_schedule_users rsu WHERE rsu.schedule_id = x.schedule_id and rsu.schedule_id = [scheduleId]  UNION SELECT ur.user_id FROM fn_user_role ur WHERE ur.role_id IN (SELECT rsu2.role_id FROM cr_report_schedule_users rsu2 WHERE rsu2.schedule_id = x.schedule_id and  rsu2.schedule_id = [scheduleId]))

download.all.insert = insert into cr_report_dwnld_log (user_id,rep_id,file_name,dwnld_start_time,filter_params) values (?,?,?,?,?)

#ReportWrapper.java

report.wrapper.format = SELECT coalesce(cr.owner_id, cr.create_id) owner_id, cr.create_id, DATE_FORMAT(cr.create_date, '[Globals.getOracleTimeFormat()]') create_date, maint_id, DATE_FORMAT(cr.maint_date, '[Globals.getOracleTimeFormat()]') update_date, cr.menu_id, cr.menu_approved_yn FROM cr_report cr WHERE cr.rep_id= [reportID]

generate.subset.sql = SELECT [colNames.toString()] FROM (SELECT  [colNames.toString()] FROM ([reportSQL]) AS x ) AS y 

report.sql.only.first.part = SELECT [colNames.toString()] FROM (SELECT [colNames.toString()] FROM (

report.sql.only.second.part.a = [startRow] 

report.sql.only.second.part.b = [pageSize]
#MYSQL: LIMIT [startRow], [pageSize]
#ORacle: rownum >= [startRow] and rownum <= ([startRow]+[pageSize])
#Postgre: limit [pageSize] offset [startRow] 

report.sql.only.second.part.b.noorderby =  LIMIT [startRow]

generate.sql.visual.select = SELECT

generate.sql.visual.count = COUNT(*) cnt

generate.sql.visual.dual = 
#No DUAL table in PostgreSQL so this is blank

#ReportRuntime.java

load.crosstab.report.data = SELECT  [colNames.toString()] FROM ( [reportSQL] 

#RaptorRunHandler.java

generate.sql.handler = SELECT  x.* from ([sql]) AS x LIMIT 2

generate.sql.select = SELECT [colNames.toString()] FROM (SELECT  [colNames.toString()] FROM ([sql]) AS y) AS x 
			
#ReportSchedule.java

load.schedule.data = SELECT rs.enabled_yn, DATE_FORMAT(rs.start_date, '%m/%d/%Y') start_date, DATE_FORMAT(rs.end_date, '%m/%d/%Y') end_date, DATE_FORMAT(rs.run_date, '%m/%d/%Y') run_date, coalesce(DATE_FORMAT(rs.run_date, '%h'), '12') run_hour, coalesce(DATE_FORMAT(rs.run_date, '%i'), '00') run_min, coalesce(DATE_FORMAT(rs.run_date, '%p'), 'AM') run_ampm, rs.recurrence, rs.conditional_yn, rs.notify_type, rs.max_row, rs.initial_formfields, rs.schedule_id, coalesce(DATE_FORMAT(rs.end_date, '%h'), '11') end_hour, coalesce(DATE_FORMAT(rs.end_date, '%i'), '45') end_min, coalesce(DATE_FORMAT(rs.end_date, '%p'), 'PM') end_ampm, encrypt_yn, attachment_yn FROM cr_report_schedule rs WHERE rs.rep_id = [reportID]

load.schedule.getid = SELECT rsu.user_id, concat(fuser.last_name,', ',fuser.first_name), fuser.login_id FROM cr_report_schedule_users rsu, fn_user fuser WHERE rsu.rep_id = [reportID]  AND rsu.schedule_id = [getScheduleID()] and rsu.user_id IS NOT NULL and rsu.user_id = fuser.user_id

load.schedule.users = SELECT rsu.role_id FROM cr_report_schedule_users rsu WHERE rsu.rep_id = [reportID] AND rsu.schedule_id = [getScheduleID()] AND rsu.role_id IS NOT NULL

new.schedule.data = select coalesce(max(schedule_id),0)+1  AS sequence from cr_report_schedule

new.report.data = select coalesce(max(rep_id),0)+1  AS rep_id from cr_report

execute.update = DELETE FROM cr_report_schedule_users WHERE rep_id = [reportID] and schedule_id = [getScheduleID()]

execute.update.users = INSERT INTO cr_report_schedule_users (schedule_id, rep_id, user_id, role_id, order_no) VALUES([getScheduleID()], [reportID], [emailToUsers.get(i)).getId()], NULL, [(i + 1)])

execute.update.roles = INSERT INTO cr_report_schedule_users (schedule_id, rep_id, user_id, role_id, order_no) VALUES([getScheduleID()], [reportID], NULL, [emailToRoles.get(i)).getId()], [((emailToUsers.size() + i + 1)])

execute.update.activity = INSERT into cr_schedule_activity_log (schedule_id, notes, run_time) values ([getScheduleID()],'Submitted:Schedule',TO_DATE('[getRunDate()] [getRunHour()]:[getRunMin()] [getRunAMPM()]', 'MM/DD/YYYY HH:MI AM'))
 
delete.schedule.data = SELECT 1 FROM cr_report_schedule WHERE rep_id = [reportID] and sched_user_id = [getScheduleUserID()] and schedule_id = [getScheduleID()]
 
delete.schedule.data.users = DELETE FROM cr_report_schedule_users WHERE rep_id = [reportID]  and schedule_id = [getScheduleID()]
 
delete.schedule.data.id = DELETE FROM cr_report_schedule where rep_id = [reportID] and sched_user_id = [getScheduleUserID()] and schedule_id = [getScheduleID()]

load.cond.sql = SELECT condition_large_sql FROM cr_report_schedule WHERE schedule_id=?

load.cond.sql.select = SELECT condition_sql FROM cr_report_schedule WHERE schedule_id = [scheduleId]

persist.cond.sql.update = update cr_report_schedule set condition_large_sql = '' where  schedule_id = [scheduleId]
#EMPTY CLOB() changed to ''

persist.cond.sql.large = SELECT condition_large_sql FROM cr_report_schedule cr WHERE schedule_id=? FOR UPDATE

persist.cond.sql.set = update cr_report_schedule set condition_sql = ? where schedule_id = [scheduleId]

#DataCache.java

get.data.view.actions = SELECT ts.web_view_action FROM cr_table_source ts WHERE ts.web_view_action IS NOT NULL

get.public.report.id.names = SELECT rep_id, title FROM cr_report WHERE public_yn = 'Y' ORDER BY title

get.private.accessible.names.a = SELECT cr.rep_id, cr.title FROM cr_report cr WHERE cr.rep_id not in (select rep_id from cr_report_access cra where user_id =  '[user_id]' 

get.private.accessible.names.if =  OR role_id in (

get.private.accessible.names.b =  ) AND public_yn = 'N' and cr.owner_id = '[user_id]' order by 2 

get.group.accessible.names.a = SELECT cr.rep_id, cr.title FROM cr_report cr  WHERE cr.rep_id  in (select rep_id from cr_report_access cra where user_id =  '[user_id]'

get.group.accessible.names.b =  )  AND public_yn = 'N' order by 2 

get.report.table.sources.a = SELECT table_name, display_name, pk_fields, web_view_action, large_data_source_yn, filter_sql FROM cr_table_source 

get.report.table.sources.where =  where SOURCE_DB= '[dBInfo]'

get.report.table.sources.if =  where SOURCE_DB is null or SOURCE_DB = '[AppConstants.DB_LOCAL]'

get.report.table.sources.else =  ORDER BY table_name

grab.report.table.a = SELECT ts.table_name, ts.display_name, ts.pk_fields, ts.web_view_action, ts.large_data_source_yn, ts.filter_sql FROM cr_table_source ts  WHERE 

grab.report.table.if = ts.SOURCE_DB= '[dBInfo]'

grab.report.table.else = (ts.SOURCE_DB is null or ts.SOURCE_DB = '[AppConstants.DB_LOCAL]')

grab.report.table.b =  except SELECT ts.table_name, ts.display_name, ts.pk_fields,  ts.web_view_action,  ts.large_data_source_yn, ts.filter_sql from cr_table_source ts where table_name in (select table_name from  cr_table_role where role_id not IN [sb.toString()]) and 

grab.report.table.c =  ORDER BY 1 

get.report.table.crjoin = SELECT src_table_name, dest_table_name, join_expr FROM cr_table_join

get.report.table.joins = SELECT tj.src_table_name, tj.dest_table_name, tj.join_expr FROM cr_table_join tj WHERE ((EXISTS (SELECT 1 FROM cr_table_role trs WHERE trs.table_name=tj.src_table_name AND trs.role_id IN [sb.toString()])) OR (NOT EXISTS (SELECT 1 FROM cr_table_role trs WHERE trs.table_name=tj.src_table_name))) AND ((EXISTS (SELECT 1 FROM cr_table_role trd WHERE trd.table_name=tj.dest_table_name AND trd.role_id IN [sb.toString()])) OR (NOT EXISTS (SELECT 1 FROM cr_table_role trd WHERE trd.table_name=tj.dest_table_name)))

generate.report.table.col = SELECT a.table_name, a.column_name, a.data_type, a.label FROM user_column_def a WHERE a.table_name = '[tableName.toUpperCase()]' ORDER BY a.column_id

generate.db.user.sql.a = SELECT utc.table_name, utc.column_name, utc.data_type, 

generate.db.user.sql.if = utc.column_name FROM user_tab_columns utc 

generate.db.user.sql.else = coalesce(x.label, utc.column_name) FROM user_tab_columns utc 

generate.db.user.sql.b = WHERE utc.table_name = '[tableName.toUpperCase()]' 

generate.db.user.sql.c = AND utc.table_name = x.table_name AND utc.column_name = x.column_name 

generate.db.user.sql.d = ORDER BY utc.column_id 

#SearchHandler.java

load.report.search.result = SELECT cr.rep_id, cr.rep_id report_id, [rep_title_sql] title, cr.descr, concat(au.first_name,' ',au.last_name) owner_name, DATE_FORMAT(cr.create_date, '%m/%d/%Y') create_date, CASE WHEN coalesce(cr.owner_id, cr.create_id) = [userID] THEN 'N' ELSE coalesce(ra.read_only_yn, 'Y') END read_only_yn, CASE WHEN coalesce(cr.owner_id, cr.create_id) = [userID] THEN 'Y' ELSE 'N' END user_is_owner_yn, case when report_xml like '%<allowSchedule>N</allowSchedule>%' then 'N' when report_xml like '%<allowSchedule>Y</allowSchedule>%' or 1 = (select distinct 1 from cr_report_schedule where rep_id = cr.rep_id) then 'Y' else 'N' end FROM cr_report cr JOIN fn_user au ON coalesce (cr.owner_id, cr.create_id) = au.user_id [fReportID] [fReportName] LEFT JOIN(SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra ON ra.rep_id = cr.rep_id

load.report.search.instr = WHERE cr.menu_id LIKE '%[menuId]%'

load.report.search.result.user = WHERE coalesce(cr.owner_id, cr.create_id) = [userID]

load.report.search.result.public = WHERE (coalesce(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL)

load.report.search.result.fav =  WHERE cr.rep_id in (select rep_id from cr_favorite_reports where user_id = [userID]

load.report.search.result.sort = ORDER BY CASE coalesce(cr.owner_id, cr.create_id) WHEN [userID] THEN ' ' WHEN 'upper(concat(au.first_name,' ',au.last_name))' ELSE 'upper(cr.title)' END

load.folder.report.result = SELECT cr.rep_id, cr.rep_id report_id, concat([rep_title_sql] , (CASE WHEN cr.public_yn = 'Y' THEN '' ELSE '[PRIVATE_ICON]' END),cr.title,'</a>') title, cr.descr, concat(au.first_name,' ',au.last_name) owner_name, TO_CHAR(cr.create_date, 'MM/DD/YYYY') create_date, CASE WHEN coalesce(cr.owner_id, cr.create_id) = [userID] THEN 'N' ELSE coalesce(ra.read_only_yn, 'Y') END read_only_yn, CASE WHEN coalesce(cr.owner_id, cr.create_id) = [userID] THEN 'Y' ELSE 'N' END user_is_owner_yn FROM cr_report cr JOIN fn_user au ON coalesce (cr.owner_id, cr.create_id) = au.user_id AND TO_CHAR(cr.rep_id, 'FM99999999') like coalesce('%[fReportID]%', TO_CHAR(cr.rep_id, 'FM99999999')) AND UPPER(cr.title) LIKE UPPER('%[fReportName]%') LEFT JOIN(SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra ON ra.rep_id = cr.rep_id

load.folder.report.result.sort =  ORDER BY CASE coalesce(cr.owner_id, cr.create_id) WHEN [userID] THEN ' ' WHEN '(concat(au.first_name,' ',au.last_name))' ELSE 'cr.title' END

#WizardProcessor.java

process.filter.add.edit = '[argValue]'

#ReportDefinition.java

persist.report.adhoc = SELECT nextval('[Globals.getAdhocReportSequence()]') AS sequence

#Globals.java

initialize.roles = SELECT 1 WHERE EXISTS (SELECT 1 FROM cr_table_role)

initialize.version = SELECT cr_raptor.get_version

# scheduler


scheduler.available.schedules = SELECT x.rep_id, x.schedule_id, x.conditional_yn, x.condition_large_sql, x.notify_type, x.max_row, x.initial_formfields, x.processed_formfields, r.title, x.user_id FROM ( SELECT rs.rep_id, rs.schedule_id, rs.sched_user_id user_id, rs.conditional_yn, rs.condition_large_sql, rs.notify_type, rs.max_row, rs.initial_formfields, rs.processed_formfields  FROM cr_report_schedule rs  WHERE rs.enabled_yn='Y'  AND rs.start_date <= [currentDate]  AND  (rs.end_date >= [currentDate] or rs.end_date is null )  AND rs.run_date IS NOT NULL  ) x, cr_report r  WHERE x.rep_id = r.rep_id

random.string = select ( concat('Z' , round(random() * 1000000000000) ) )


scheduler.user.emails = SELECT au.user_id FROM (SELECT rs.schedule_id, rs.rep_id FROM cr_report_schedule rs WHERE rs.enabled_yn='Y' AND rs.start_date <= now() AND rs.end_date  >= now() AND rs.run_date IS NOT NULL AND rs.schedule_id = [p_schedule_id] ) x, cr_report r, fn_user au WHERE x.rep_id = r.rep_id AND au.user_id IN (SELECT rsu.user_id FROM cr_report_schedule_users rsu WHERE rsu.schedule_id = x.schedule_id and rsu.schedule_id = [p_schedule_id] UNION SELECT ur.user_id FROM fn_user_role ur WHERE ur.role_id IN (SELECT rsu2.role_id FROM cr_report_schedule_users rsu2 WHERE rsu2.schedule_id = x.schedule_id and rsu2.schedule_id = [p_schedule_id]))  


# my logins

app.query = SELECT APP_ID, ML_APP_NAME, MOTS_ID from fn_app

user.log.query = SELECT DISTINCT  IFNULL(SBCID, '') CUID, '' AWID, CONCAT('"',IFNULL(SBCID, ''),'"') APPLICATIONUSERID,  CONCAT('"',IFNULL(FIRST_NAME, ''),'"') FIRST_NAME, CONCAT('"',substr(IFNULL(MIDDLE_NAME, ''), 0, 1),'"') MIDDLE_INITIAL, CONCAT('"',IFNULL(LAST_NAME, ''),'"') LAST_NAME, IFNULL(DATE_FORMAT(LAST_LOGIN_DATE, '%Y/%m/%d'), '') LAST_LOGON_DATE, DATE_FORMAT(CREATED_DATE, '%Y/%m/%d') ACCOUNT_ACTIVATION_DATE, IFNULL(DATE_FORMAT(MODIFIED_DATE, '%Y/%m/%d'), '') LAST_DATE_ACCOUNT_MODIFIED,  '' LAST_PASSWORD_CHANGE_DATE, CONCAT('"',IFNULL(FIRST_NAME, ''),' ',IFNULL(MIDDLE_NAME, ''),' ',IFNULL(LAST_NAME, ''),'"')  FULL_USER_NAME, '' NT_ID, IFNULL(EMAIL, '') EMAIL   FROM FN_USER FU, FN_USER_ROLE FUR WHERE FU.USER_ID \= FUR.USER_ID and FUR.app_id \= ? and ACTIVE_YN \= 'Y' and sbcid is not null order by 1

profile.log.query = SELECT DISTINCT CONCAT('"' , ROLE_NAME , '"') PROFILE_NAME, '""' SECURITY_SETTINGS FROM FN_ROLE FR, FN_USER_ROLE FUR WHERE FUR.ROLE_ID \= FR.ROLE_ID and FR.ACTIVE_YN \= 'Y' and ((FUR.APP_ID \= 1 and FR.ROLE_NAME <> 'Standard User') or (FUR.APP_ID \= ? and FUR.APP_ID <> 1))  ORDER BY 1

user.profile.log.query = SELECT DISTINCT IFNULL(SBCID, '') CUID, '' AWID, CONCAT('"' , IFNULL(SBCID, '') , '"') APPLICATIONUSERID , CONCAT('"' , ROLE_NAME , '"')  PROFILE_NAME  FROM FN_USER A, FN_USER_ROLE B, FN_ROLE C WHERE A.USER_ID \= B.USER_ID AND B.ROLE_ID \= C.ROLE_ID AND A.ACTIVE_YN \= 'Y' AND C.ACTIVE_YN \= 'Y' AND a.sbcid is not null  AND ((B.APP_ID \= 1 and C.ROLE_NAME <> 'Standard User') or (B.APP_ID \= ? and B.APP_ID <> 1)) ORDER BY 1

all.accounts.log.query = SELECT DISTINCT IFNULL(SBCID, '') CUID, (case when A.ACTIVE_YN\='Y' then 'ACTIVE' else 'INACTIVE' end) ACTIVE_YN, CONCAT('"' , IFNULL(SBCID, '') , '"') APPLICATIONUSERID , IFNULL(DATE_FORMAT(LAST_LOGIN_DATE, '%Y/%m/%d'), '') LAST_LOGON_DATE, '' LAST_PASSWORD_CHANGE_DATE, CONCAT('"' , ROLE_NAME , '"')  PROFILE_NAME  FROM FN_USER A, FN_USER_ROLE B, FN_ROLE C WHERE A.USER_ID \= B.USER_ID AND B.ROLE_ID \= C.ROLE_ID AND a.sbcid is not null  AND ((B.APP_ID \= 1 and C.ROLE_NAME <> 'Standard User') or (B.APP_ID \= ? and B.APP_ID <> 1)) ORDER BY 1

# basic sql

seq.next.val = SELECT nextval('[sequenceName]') AS id

current.date = now()

nvl = IFNULL

# report security
report.user.access = SELECT ra.role_id, ra.user_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.rep_id = [reportID]
add.user.access = INSERT INTO cr_report_access (rep_id, order_no, role_id, user_id, read_only_yn) VALUES([reportID], IFNULL((select order_no from (SELECT MAX(order_no) AS order_no FROM cr_report_access WHERE rep_id=[reportID]) AS temp), 0)+1, NULL, [userID], '[readOnlyAccess]')
update.user.access = UPDATE cr_report_access SET read_only_yn='[readOnlyAccess]' WHERE rep_id=[reportID] AND user_id=[userID]
remove.user.access = DELETE FROM cr_report_access WHERE rep_id=[reportID] AND user_id=[userID]
add.role.access = INSERT INTO cr_report_access (rep_id, order_no, role_id, user_id, read_only_yn) VALUES([reportID], IFNULL((select order_no from (SELECT MAX(order_no) AS order_no FROM cr_report_access WHERE rep_id=[reportID]) AS temp), 0)+1, [roleID], NULL, '[readOnlyAccess]')
update.role.access = UPDATE cr_report_access SET read_only_yn='[readOnlyAccess]' WHERE rep_id=[reportID] AND role_id=[roleID]
remove.role.access = DELETE FROM cr_report_access WHERE rep_id=[reportID] AND role_id=[roleID]