VyXs5ARR

1use SEDIG_perm
2
3declare @UpgkPk uniqueidentifier = '0e352f5b-9fb1-4a6b-9ad8-8867536822a2'
4declare @school uniqueidentifier
5declare @PeriodBegin datetime
6declare @PeriodEnd datetime
7declare @intFrom int
8declare @intTo int
9
10if (OBJECT_ID(N'tempdb..#classes') is not null)
11drop table #classes
12create table #classes (
13	c_pk uniqueidentifier,
14    class_nom int null,
15    class_lit varchar(100) null,
16    pupil_count int null,
17    val5 int null,
18    val4 int null,
19    val3 int null,
20    val2 int null,
21    valna int null,
22    total_count int null,
23    illmiss_count int null,
24    miss_count int null
25)
26if (OBJECT_ID(N'tempdb..#final_assessments') is not null)
27drop table #final_assessments
28create table #final_assessments (
29	p_pk uniqueidentifier,
30    class uniqueidentifier,
31    presence int null,
32    attested varchar(3) null
33)
34if (OBJECT_ID(N'tempdb..#subjects') is not null)
35drop table #subjects
36create table #subjects (
37	s_pk uniqueidentifier,
38    cl_pk uniqueidentifier,
39    cl_full varchar(3) null,
40    s_ugroup uniqueidentifier,
41    s_pgroup uniqueidentifier
42)
43if (OBJECT_ID(N'tempdb..#absence') is not null)
44drop table #absence
45create table #absence (
46	p_id uniqueidentifier,
47    l_id uniqueidentifier,
48    s_id uniqueidentifier,
49    cl_id uniqueidentifier,
50    presence VARCHAR(1) null,
51    l_time datetime,
52    s_ugroup uniqueidentifier,
53    s_pgroup uniqueidentifier
54)
55
56-- select 
57--     @PeriodBegin = ДатаНачала,
58--     @PeriodEnd = ДатаКонца
59-- from 
60--     УчебныйПериодГруппыКлассов with(nolock)
61-- where 
62--     primaryKey = @UpgkPk
63
64-- заполняем с какой по какую дату и номер, классы и школу
65select 
66	@PeriodBegin = ДатаНачала,
67    @PeriodEnd = ДатаКонца,
68    @intFrom = гкдп.НомерС,
69    @intTo = гкдп.НомерПо,
70    @school = гкдп.Школа
71from 
72    УчебныйПериодГруппыКлассов упгк with(nolock)
73    join ГруппаКлассовДляПериодов гкдп with(nolock) on гкдп.primaryKey = упгк.ГруппаКлассов
74where
75    упгк.primaryKey = @UpgkPk
76    and гкдп.Актуально = 'да'
77
78-- добавляем классы с кол-вом учеников в них
79insert into #classes 
80	(c_pk, class_nom, class_lit, pupil_count)
81	select distinct 
82		к.primaryKey, к.Номер, isnull(к.Литера,''), count(рп.primaryKey) 
83from 
84    Класс к with(nolock)
85    join РольПользователя рп with(nolock) on рп.Класс = к.primaryKey
86	join Пользователь п with(nolock) on п.primaryKey = рп.Пользователь
87where 
88    к.Номер >= @intFrom 
89    and к.Номер <= @intTo
90    and к.Актуально = 'да'
91    and к.Школа = @school
92	and (рп.ДатаНачала is null or рп.ДатаНачала <= @PeriodEnd)
93	and (рп.ДатаКонца is null or рп.ДатаКонца >= @PeriodBegin)
94	and п.Актуально='да' 
95group by к.primaryKey, к.Номер, isnull(к.Литера, '')
96-- считываем итоговые оценки за выбранный период
97insert into #final_assessments 
98	(class, presence, attested, p_pk)
99	select distinct 
100		рп.Класс, ио.Оценка, ио.Аттестован, рп.primaryKey
101from 
102    ИтоговаяОценка ио with(nolock)
103	join РольПользователя рп with(nolock) on рп.primaryKey = ио.Ученик
104	join Пользователь п with(nolock) on п.primaryKey = рп.Пользователь
105	join Класс к with(nolock) on к.primaryKey = рп.Класс
106where 
107	ио.Актуально='да'
108	and (рп.ДатаНачала is null or рп.ДатаНачала <= @PeriodEnd)
109	and (рп.ДатаКонца is null or рп.ДатаКонца >= @PeriodBegin)
110	and п.Актуально = 'да' 
111	and ио.Период = @UpgkPk
112	and к.Актуально = 'да'
113-- оставляем только наименьшую оценку либо Н/А у каждого уч-ка
114delete fa 
115from 
116    #final_assessments fa
117where (fa.presence != (
118select min(fa0.presence) from #final_assessments fa0 
119        where fa0.p_pk = fa.p_pk 
120        and fa0.presence is not null
121        and (fa0.attested is null or fa0.attested='да'))
122    or exists (
123select 1 from #final_assessments fa1 
124        where fa1.p_pk = fa.p_pk
125        and fa1.attested = 'нет'))
126        and fa.attested = 'да'
127-- вставляем кол-во отличников/хорошистов/итд в результирующую таблицу
128update #classes 
129	set val5 = (
130	select count(fa.p_pk)
131		from #final_assessments fa 
132        where fa.class = c_pk 
133            and fa.attested != 'нет'
134            and fa.presence = 5),
135	val4 = (
136select count(fa.p_pk)
137	from #final_assessments fa 
138        where fa.class = c_pk 
139            and fa.attested != 'нет'
140            and fa.presence = 4),
141	val3 = (
142select count(fa.p_pk)
143	from #final_assessments fa 
144        where fa.class = c_pk 
145            and fa.attested != 'нет'
146            and fa.presence = 3),
147	val2 = (
148select count(fa.p_pk)
149	from #final_assessments fa 
150        where fa.class = c_pk 
151            and fa.attested != 'нет'
152            and fa.presence in (2, 1)),
153	valna = (
154select count(fa.p_pk)
155	from #final_assessments fa 
156        where fa.class = c_pk 
157            and fa.attested = 'нет')
158
159-- сначала получим все предметы, по которым будем смотреть уроки
160-- УЧЕБНЫЕ
161insert into #subjects 
162	(cl_pk, s_pk, cl_full, s_ugroup)
163	select distinct 
164		к.primaryKey, пупг.primaryKey, угк.ВесьКласс, угк.primaryKey
165from 
166    Класс к with(nolock)
167    join УчебнаяГруппаКласса угк with(nolock) on угк.Класс = к.primaryKey
168    join ПредметУПГруппы пупг with(nolock) on пупг.УчебнаяГруппаКласса = угк.primaryKey
169where 
170к.primaryKey in (select c_pk from #classes)
171    and угк.Актуально = 'да' 
172    and пупг.Актуально = 'да'
173    and пупг.ПотоковаяУчебнаяГруппа is null
174-- и ПОТОКОВЫЕ
175insert into #subjects 
176	(cl_pk, s_pk, s_pgroup)
177	select distinct 
178		к.primaryKey, пупг.primaryKey, пуг.primaryKey
179from 
180    Класс к with(nolock)
181    join КлассВГруппе квг with(nolock) on квг.Класс = к.primaryKey
182    join ПотоковаяУчебнаяГруппа пуг with(nolock) on пуг.primaryKey = квг.ПотоковаяУчебнаяГруппа
183    join ПредметУПГруппы пупг with(nolock) on пупг.ПотоковаяУчебнаяГруппа = пуг.primaryKey
184where 
185к.primaryKey in (select c_pk from #classes)
186    and пуг.Актуально = 'да' 
187    and квг.Актуально = 'да'
188    and пупг.Актуально = 'да'
189    and пупг.УчебнаяГруппаКласса is null
190
191-- теперь вычитываем все уроки с присутствием учеников на каждом уроке
192insert into #absence 
193	(l_id, cl_id, p_id, s_id, l_time, s_ugroup, s_pgroup, presence)
194	select distinct 
195		у.primaryKey, s.cl_pk, рп.primaryKey, s.s_pk, у.ДатаПроведения, s.s_ugroup, s_pgroup, cast(о.ОтметкаОПрисутствии as varchar(1))
196from 
197    Урок у with(nolock)
198    join #subjects s on s.s_pk = у.ПредметУПГруппы
199    join РольПользователя рп with(nolock) on рп.Класс = s.cl_pk
200    join КолонкаОтметокНаУроке кону with(nolock) on кону.Урок = у.primaryKey
201    join Отметка о with(nolock) on о.Колонка = кону.primaryKey
202    left join ЗаменаВремениУрока зву with(nolock) on зву.primaryKey = у.Замена
203where 
204    у.ДатаПроведения between @PeriodBegin and @PeriodEnd
205    and у.Актуально = 'да'
206    and (рп.ДатаКонца is null or рп.ДатаКонца >= @PeriodBegin)
207    and кону.ВидОтметки = 'Присутствие'
208    and кону.Актуально = 'да'
209    and (о.Актуально is null or о.Актуально = 'да')
210    and (о.Ученик is null or о.Ученик = рп.primaryKey)
211    and (у.Замена is null or зву.Новый='Да')
212-- добавляем справки об отсутсвии
213-- простые справки без уточнений
214update #absence 
215	set presence = CAST(СОО.ОтметкаОПрисутствии as VARCHAR(1))
216from 
217    #absence a
218    join СправкаОбОтсутствии соо with(nolock) on соо.ученик = p_id
219where 
220    соо.Актуально = 'да'
221and not exists(select 1 from ПредметСправкаОбОтсутствии with(nolock) where СправкаОбОтсутствии = СОО.primaryKey)
222and not exists(select 1 from ВремяУрокаСправкаОбОтсутствии with(nolock) where СправкаОбОтсутствии = СОО.primaryKey)
223    and l_time between соо.ДатаНачала and соо.ДатаКонца
224
225-- замена по предмету
226update #absence 
227	set presence = CAST(СОО.ОтметкаОПрисутствии as VARCHAR(1))
228from 
229    #absence a
230    join СправкаОбОтсутствии соо with(nolock) on соо.ученик = p_id
231    join ПредметСправкаОбОтсутствии псоо with(nolock) on псоо.СправкаОбОтсутствии = соо.primaryKey
232where 
233    соо.Актуально = 'да'
234    and псоо.Актуально = 'да'
235    and s_id = псоо.Предмет
236    and l_time between соо.ДатаНачала and соо.ДатаКонца
237
238-- замена по времени урока
239update #absence 
240	set presence = CAST(СОО.ОтметкаОПрисутствии as VARCHAR(1))
241from 
242    #absence a
243    join СправкаОбОтсутствии соо with(nolock) on соо.ученик = p_id
244    join ВремяУрокаСправкаОбОтсутствии вусоо with(nolock) on вусоо.СправкаОбОтсутствии = соо.primaryKey
245    join ВремяУрока ву with(nolock) on ву.primaryKey = вусоо.ВремяУрока
246    join Урок у with(nolock) on у.primaryKey = a.l_id
247where 
248    соо.Актуально = 'да'
249    and вусоо.Актуально = 'да'
250    and ву.Начало <= у.Начало 
251    and ву.Конец >= у.Конец
252    and l_time between соо.ДатаНачала and соо.ДатаКонца
253-- обновляем в результирующей таблице все поля
254update #classes
255	set illmiss_count = (
256	select count(a.p_id) from #absence a
257		where a.cl_id=c_pk
258		and a.presence='Б'),
259    miss_count = (
260select count(a.p_id) from #absence a
261		where a.cl_id=c_pk
262		and a.presence='Н'),
263    total_count = (
264select count(a.p_id) from #absence a
265		where a.cl_id=c_pk
266		and a.presence in('Б','У','Н'))
267
268select c_pk 'КлассПк', 
269    class_nom 'КлассНомер', 
270    class_lit 'КлассЛитера',
271	pupil_count 'Кол-во учащихся', 
272    val5 'Окончили на 5',
273    val4 'Окончили на 4',
274    val3 'Окончили на 3',
275	val2 'Окончили на 2 и 1',
276    valna 'н/а',
277    total_count 'Все пропуски',
278    illmiss_count 'Болели',
279    miss_count 'Неуважительные'   
280from #classes