-
Notifications
You must be signed in to change notification settings - Fork 38
/
cron_udp.hjson
300 lines (300 loc) · 12.4 KB
/
cron_udp.hjson
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
## UDP (Unizin Data Platform) queries for MyLA cron job
{
"metadata":
'''
select 'canvasdatadate' as pkey, min(dag_run) as pvalue from report.publish_info pi2
''',
"user" :
'''
select
(
cast(%(canvas_data_id_increment)s as bigint)
+
cast(p2.lms_ext_id as bigint)
) as user_id,
case
when pe.email_address is not null then lower(split_part(pe.email_address , '@', 1))
else p2.sis_ext_id end as sis_name,
cast(co.lms_int_id as bigint) as course_id,
cg.le_current_score as current_grade,
cg.le_final_score as final_grade,
case
when cse.role = 'Student' then 'StudentEnrollment'
when cse.role = 'TeachingAssistant' then 'TaEnrollment'
when cse.role = 'Teacher' then 'TeacherEnrollment'
else '' end
as enrollment_type
from entity.course_section_enrollment cse
left join entity.course_section cs
on cse.course_section_id = cs.course_section_id
left join keymap.course_offering co
on cs.le_current_course_offering_id = co.id
left join entity.person p
on cse.person_id = p.person_id
left join keymap.person p2
on p.person_id = p2.id
left join entity.person_email pe
on p.person_id = pe.person_id
left join entity.course_grade cg
on cse.course_section_id = cg.course_section_id and cse.person_id = cg.person_id
where
co.lms_int_id in %(course_ids)s
and cse.role in ('Student', 'TeachingAssistant', 'Teacher')
and cse.role_status = 'Enrolled'
and cse.enrollment_status = 'Active'
order by user_id
''',
"assignment_groups":
'''
with assignment_details as (
select la.due_date, title, la.course_offering_id, la.learner_activity_id, la.points_possible, la.learner_activity_group_id
from entity.learner_activity la, keymap.course_offering co
where
la.visibility = 'everyone'
and la.status = 'published'
and la.course_offering_id = co.id
and co.lms_int_id in %(course_ids)s
), assignment_grp as (
select lg.*
from entity.learner_activity_group lg, keymap.course_offering co
where
lg.status = 'available'
and lg.course_offering_id = co.id
and co.lms_int_id in %(course_ids)s
), assign_more as (
select distinct(a.learner_activity_group_id), da.group_points
from assignment_details a
join (
select learner_activity_group_id, sum(points_possible) as group_points
from assignment_details
group by learner_activity_group_id
) as da
on a.learner_activity_group_id = da.learner_activity_group_id
), grp_full as (
select a.group_points, b.learner_activity_group_id
from assign_more a
right join assignment_grp b
on a.learner_activity_group_id = b.learner_activity_group_id
), assign_rules as (
select distinct ad.learner_activity_group_id, agr.drop_lowest_amount as drop_lowest, agr.drop_highest_amount as drop_highest
from grp_full ad
join entity.learner_activity_group agr
on ad.learner_activity_group_id = agr.learner_activity_group_id
), assignment_grp_points as (
select ag.*, am.group_points AS group_points, ar.drop_lowest as drop_lowest, ar.drop_highest as drop_highest
from assignment_grp ag
join grp_full am on ag.learner_activity_group_id = am.learner_activity_group_id
join assign_rules ar on ag.learner_activity_group_id = ar.learner_activity_group_id
)
select
cast(lag_km.lms_int_id as BIGINT) as id,
cast(co_km.lms_int_id as BIGINT) as course_id,
cast(agp.group_weight as float) as weight,
agp.name as name,
agp.group_points as group_points,
agp.drop_lowest as drop_lowest,
agp.drop_highest as drop_highest
from assignment_grp_points agp,
keymap.course_offering co_km,
keymap.learner_activity_group lag_km
where agp.course_offering_id = co_km.id
and agp.learner_activity_group_id = lag_km.id
order by id
''' ,
"assignment":
'''
with assignment_info as
(
select
la.due_date as due_date,
la.due_date at time zone 'America/Detroit' as local_date,
la.title as name,
cast(co.lms_int_id as BIGINT) as course_id,
cast(la_km.lms_int_id as BIGINT) as id,
la.points_possible as points_possible,
cast(lag_km.lms_int_id as BIGINT) as assignment_group_id
from
entity.learner_activity la,
keymap.course_offering co,
keymap.learner_activity la_km,
keymap.learner_activity_group lag_km
where
la.visibility = 'everyone'
and la.status = 'published'
and la.course_offering_id = co.id
and co.lms_int_id in %(course_ids)s
and la.learner_activity_id = la_km.id
and la.learner_activity_group_id = lag_km.id
)
select * from assignment_info
order by id
''',
"assignment_weight":
'''
select
cast(co_km.lms_int_id as bigint) as course_id,
case when sum(group_weight) > 1
then
cast(1 as boolean)
else
cast(0 as boolean)
end as consider_weight
from
entity.learner_activity_group lag,
keymap.course_offering co_km
where
lag.course_offering_id = co_km.id
and co_km.lms_int_id in %(course_ids)s
group by co_km.lms_int_id
''',
"term":
'''
select
cast(ka.lms_int_id as BIGINT) as id,
cast(ka.lms_ext_id as BIGINT) as canvas_id,
a.name as name,
a.le_term_begin_date::timestamp without time zone as date_start,
a.le_term_end_date::timestamp without time zone as date_end
from
entity.academic_term as a
left join keymap.academic_term as ka on ka.id = a.academic_term_id
where
ka.lms_ext_id is not null
order by id
''',
/* start_at and conclude_at contain only the date information, with time values truncated
Mapping all courses with a NULL term into the term called No Term
*/
"course":
'''
WITH no_term_temp as (SELECT academic_term_id from entity.academic_term where name = 'No Term')
SELECT
cast(co2.lms_int_id as BIGINT) as id,
cast(co2.lms_ext_id as BIGINT) as canvas_id,
cast(at2.lms_int_id as BIGINT) as enrollment_term_id,
co.le_code as name,
co.le_start_date::timestamp without time zone as start_at,
co.le_end_date::timestamp without time zone as conclude_at
FROM
entity.course_offering co
LEFT OUTER JOIN entity.academic_session as3 on (co.academic_session_id = as3.academic_session_id),
keymap.course_offering co2,
keymap.academic_term at2,
no_term_temp
WHERE co2.lms_int_id in %(course_ids)s
and co.course_offering_id = co2.id
and (
(co.academic_session_id is null and co.academic_term_id is not null and (co.academic_term_id = at2.id))
or
(co.academic_session_id is null and co.academic_term_id is null and at2.id = no_term_temp.academic_term_id)
or
(co.academic_session_id = as3.academic_session_id and at2.id = as3.academic_term_id)
)
''',
"resource":
'''
select
cast(f_km.lms_int_id as BIGINT) as id,
f.status as file_state,
f.display_name as display_name
from entity.file f, keymap.file f_km, keymap.course_offering co_km
where
f.course_offering_id = co_km.id
and f.file_id = f_km.id
and co_km.lms_int_id in %(course_ids)s
order by id
''',
"submission":
'''
create temporary table all_assign_sub as (
with enrollment as
(
select
distinct cse.person_id as user_id
from entity.course_section_enrollment cse
left join entity.course_section cs
on cse.course_section_id = cs.course_section_id
left join keymap.course_offering co
on cs.le_current_course_offering_id = co.id
where
co.lms_int_id in %(course_ids)s
and cse.role_status ='Enrolled'
and cse."role" = 'Student'
and cse.enrollment_status = 'Active'
),
submission as
(
select
la.status,
la.visibility,
la2.lms_int_id as assignment_id,
cast(co.lms_int_id as BIGINT) as course_id,
la.title as assignment_title,
lar.published_score as published_score,
lar.response_date as submitted_at,
lar.graded_date as graded_at,
lar.posted_at at time zone 'America/Detroit' as grade_posted_local_date,
lar.grading_status as submission_workflow_state,
la.title as title,
lar.learner_activity_result_id as learner_activity_result_id,
lar.person_id as short_user_id,
cast(lar2.lms_int_id as BIGINT) as submission_id,
(cast(%(canvas_data_id_increment)s as bigint) + cast(p.lms_ext_id as bigint)) as canvas_user_id
from entity.learner_activity_result lar
join enrollment on lar.person_id= enrollment.user_id
join enrollment e on lar.person_id = e.user_id
join keymap.learner_activity_result lar2 on lar.learner_activity_result_id = lar2.id
left join entity.learner_activity la on lar.learner_activity_id = la.learner_activity_id
left join keymap.learner_activity la2 on la.learner_activity_id = la2.id
left join keymap.course_offering co on co.id = la.course_offering_id
join keymap.person p on p.id = lar.person_id
where
co.lms_int_id in %(course_ids)s
and la.status = 'published'
)
select
cast(submission_id as BIGINT) AS id,
assignment_id AS assignment_id,
course_id,
canvas_user_id,
(
case
when
(grade_posted_local_date is null or submission_workflow_state != 'graded')
then
null
else
round(published_score,1)
end
) AS score,
submitted_at AS submitted_at,
graded_at AS graded_date,
grade_posted_local_date
from
submission
);
select
f.id::bigint,
f.assignment_id::bigint assignment_id,
f.course_id,
f.canvas_user_id::bigint as user_id,
f.score::float,
f.submitted_at,
f.graded_date,
f.grade_posted_local_date,
cast(f1.avg_score as float) as avg_score
from
all_assign_sub f join
(
select
assignment_id,
round(avg(score),1) as avg_score
from
all_assign_sub
group by assignment_id
) as f1
on f.assignment_id = f1.assignment_id
where f.id is not null
order by assignment_id, user_id
'''
}