Выставление доп. баллов за посещения занятий по физ. культуре весной 2023

assigned to @czen


-- (2506, 58506, 27389, 124010, 124011, 124176, 165709)

    DROP TABLE IF EXISTS vGymRates;
    CREATE TEMPORARY TABLE vGymRates AS (
        SELECT  view_short_final_report.disciplineid,
                view_short_final_report.recordbookid,
                coalesce(view_short_final_report.totalrate, 0) as totalrate,
                39 - coalesce(rt22.rate, 0) as remainingrate_attendance2,
                coalesce(rt11.rate, 0) + coalesce(rt12.rate, 0) as totalrate_semester1,
                11 - coalesce(rt21.rate, 0) as remainingrate_testing2
        from view_short_final_report
        join record_books on record_books.id = view_short_final_report.recordbookid
        join disciplines on disciplines.id = view_short_final_report.disciplineid
        join view_roadmap vr11 on vr11.disciplineid = disciplines.id
            and vr11.moduletype = 'regular'
            and vr11.submoduleordernum = 1
            and vr11.moduleordernum = 1
        left join rating_table rt11 on rt11.recordbookid = record_books.id and rt11.submoduleid = vr11.submoduleid
        join view_roadmap vr12 on vr12.disciplineid = disciplines.id
            and vr12.moduletype = 'regular'
            and vr12.submoduleordernum = 2
            and vr12.moduleordernum = 1
        left join rating_table rt12 on rt12.recordbookid = record_books.id and rt12.submoduleid = vr12.submoduleid
        join view_roadmap vr21 on vr21.disciplineid = disciplines.id
            and vr21.moduletype = 'regular'
            and vr21.submoduleordernum = 1
            and vr21.moduleordernum = 2
        left join rating_table rt21 on rt21.recordbookid = record_books.id and rt21.submoduleid = vr21.submoduleid
        join view_roadmap vr22 on vr22.disciplineid = disciplines.id
            and vr22.moduletype = 'regular'
            and vr22.submoduleordernum = 2
            and vr22.moduleordernum = 2
        left join rating_table rt22 on rt22.recordbookid = record_books.id and rt22.submoduleid = vr22.submoduleid
        join students_groups on record_books.id = students_groups.recordbookid and disciplines.semesterid = students_groups.semesterid
        WHERE view_short_final_report.removerate = 0
          AND students_groups.state < 'leave'
          AND view_short_final_report.semesterrid = 18
          AND view_short_final_report.subjectid in (2506, 58506, 27389, 124010, 124011, 124176, 165709)
    );
   
select * from vGymRates;   

    DROP TABLE IF EXISTS vSimpleGymBooks;
    CREATE TEMPORARY TABLE vSimpleGymBooks AS (
        SELECT books.*, vGymRates.disciplineid FROM
            ( SELECT distinct vGymRates.recordbookid
              from vGymRates
              GROUP BY vGymRates.recordbookid
              HAVING COUNT(vGymRates.disciplineid) = 1 ) books
                JOIN vGymRates ON vGymRates.recordbookid = books.recordbookid
    );

    DELETE FROM vGymRates
        WHERE vGymRates.recordbookid not in (
            SELECT vSimpleGymBooks.recordbookid FROM vSimpleGymBooks
            );

    DROP TABLE IF EXISTS vNewGymAttendance;
    CREATE TEMPORARY TABLE vNewGymAttendance AS (
        select attendance.lesson_id,
               attendance.recordbook_id,
               attendance.record_date,
               attendance.student_attended,
               disciplines.id as disciplineid
        from attendance
             join (SELECT attendance.lesson_id,
                     attendance.recordbook_id,
                     MAX(attendance.record_date) as maxdate,
                     disciplines.id as disciplineid
              FROM attendance
                       LEFT JOIN lesson_rate ON lesson_rate.lessonid = attendance.lesson_id AND lesson_rate.recordbookid = attendance.recordbook_id
                       JOIN lessons on lessons.id = attendance.lesson_id
                       JOIN disciplines on lessons.discipline_id = disciplines.id
              WHERE disciplines.subjectid in (2506, 58506, 27389, 124010, 124011, 124176, 165709)
                AND lesson_rate.id IS NULL
                and disciplines.semesterid  = 18
              group by attendance.lesson_id , attendance.recordbook_id, disciplines.id
             ) att_grouped
             on att_grouped.lesson_id = attendance.lesson_id and att_grouped.recordbook_id = attendance.recordbook_id
                 and att_grouped.maxdate = attendance.record_date
                        LEFT JOIN lesson_rate ON lesson_rate.lessonid = attendance.lesson_id AND lesson_rate.recordbookid = attendance.recordbook_id
                        JOIN lessons on lessons.id = attendance.lesson_id and lessons.lesson_deleted = 0
                        JOIN disciplines on lessons.discipline_id = disciplines.id
        where attendance.student_attended = 1
          AND disciplines.subjectid  in (2506, 58506, 27389, 124010, 124011, 124176, 165709)
          AND lesson_rate.id IS null
          and disciplines.semesterid  = 18
          and lessons.lesson_date < '2023-07-30'
          and lessons.lesson_date >= '2023-02-08'
    );


    DROP TABLE IF EXISTS vAttendanceToProcess;
    CREATE TEMPORARY TABLE vAttendanceToProcess AS (
        SELECT att.*,
               case
               	   WHEN vGymRates.remainingrate_attendance2 = 0 and vGymRates.totalrate_semester1 >= 12 and vGymRates.remainingrate_testing2 > 0 
               	   then att.row <= ceil(cast( LEAST (vGymRates.remainingrate_testing2, 9) as decimal)/3)
               	   else false
                   END as "for_extra_test",
               vGymRates.disciplineid as "RateDiscipline"
        FROM (
                 SELECT vNewGymAttendance.*, row_number() OVER (PARTITION BY recordbook_id ORDER BY record_date ASC) AS row
                 FROM vNewGymAttendance
                 WHERE student_attended = 1
             ) att
                 LEFT JOIN vGymRates ON vGymRates.recordbookid = att.recordbook_id
        ORDER BY att.record_date ASC
    );

   select * from vAttendanceToProcess;
    

    DROP TABLE IF EXISTS vGoodGymDisciplines;
    create temporary table vGoodGymDisciplines as
    select * from public.get_gym_disciplines(18);

    -- semester rating
    INSERT INTO lesson_rate (lessonid, recordbookid, submoduleid, teacherid, rate_diff, "date")
    select vAttendanceToProcess.lesson_id as lessonid,
           vAttendanceToProcess.recordbook_id as recordbookid,
           view_roadmap.submoduleid as submoduleid,
           disciplines.authorid as teacherid,
           3 as rate_diff,
           vAttendanceToProcess.record_date as "date"
    from vAttendanceToProcess
             --join disciplines on disciplines.id = vAttendanceToProcess.disciplineid
             join vGoodGymDisciplines on vGoodGymDisciplines.id = vAttendanceToProcess."RateDiscipline"
             join disciplines on disciplines.id = vGoodGymDisciplines.id
             join view_roadmap on view_roadmap.disciplineid = disciplines.id
        and view_roadmap.moduletype = 'regular'
        and view_roadmap.submoduleordernum = 1
        and view_roadmap.moduleordernum = 2
    WHERE vAttendanceToProcess.for_extra_test is true;

    insert into logs_rating (submoduleid, teacherid, rate, "action", recordbookid)
    select  view_roadmap.submoduleid,
            disciplines.authorid  as teacherid,
            agg.toadd as rate,
            'add',
            agg.recordbook_id as recordbook_id
    FROM
        (select count(vAttendanceToProcess.lesson_id) * 3 as toadd,
                vAttendanceToProcess.recordbook_id,
                vAttendanceToProcess."RateDiscipline" as disciplineid
         from vAttendanceToProcess
                  join disciplines on disciplines.id = vAttendanceToProcess."RateDiscipline"
         WHERE vAttendanceToProcess.for_extra_test is true
         group by vAttendanceToProcess.recordbook_id, vAttendanceToProcess."RateDiscipline") agg
            --join disciplines on disciplines.id = agg.disciplineid
            join vGoodGymDisciplines on vGoodGymDisciplines.id = agg.disciplineid
            join disciplines on disciplines.id = vGoodGymDisciplines.id
            join view_roadmap on view_roadmap.disciplineid = disciplines.id
            and view_roadmap.moduletype = 'regular'
            and view_roadmap.submoduleordernum = 1
            and view_roadmap.moduleordernum = 2;

    insert into rating_table (teacherid, submoduleid, rate, date, recordbookid)
    select  disciplines.authorid  as teacherid,
            view_roadmap.submoduleid,
            LEAST(agg.toadd + coalesce(rating_table.rate, 0), 11, view_roadmap.submodulerate) as rate,
            now(),
            agg.recordbook_id as recordbook_id
    FROM
        (select count(vAttendanceToProcess.lesson_id) * 3 as toadd,
                vAttendanceToProcess.recordbook_id,
                vAttendanceToProcess."RateDiscipline" as disciplineid
         from vAttendanceToProcess
                  join disciplines on disciplines.id = vAttendanceToProcess."RateDiscipline"
         WHERE vAttendanceToProcess.for_extra_test is true
         group by vAttendanceToProcess.recordbook_id, vAttendanceToProcess."RateDiscipline") agg
            --join disciplines on disciplines.id = agg.disciplineid
            join vGoodGymDisciplines on vGoodGymDisciplines.id = agg.disciplineid
            join disciplines on disciplines.id = vGoodGymDisciplines.id
            join view_roadmap on view_roadmap.disciplineid = disciplines.id
            and view_roadmap.moduletype = 'regular'
            and view_roadmap.submoduleordernum = 1
            and view_roadmap.moduleordernum = 2
            left join rating_table on rating_table.recordbookid = agg.recordbook_id and rating_table.submoduleid = view_roadmap.submoduleid
    on conflict (recordbookid, submoduleid)
        do update set rate =  excluded.rate,
                      date = now();

    update disciplines set islocked = 1
    where disciplines.id in (
        select vGoodGymDisciplines.id from vGoodGymDisciplines
                                           join disciplines on disciplines.id = vGoodGymDisciplines.id
                                           join modules on modules.disciplineid = disciplines.id
                                           join submodules on submodules.moduleid = modules.id
                                           left join rating_table on rating_table.submoduleid = submodules.id
        group by vGoodGymDisciplines.id
        having count(rating_table.rate) > 0
    );

    update submodules set isused = 1
    where submodules.id in (
        select submodules.id from submodules
                                      join modules on modules.id = submodules.moduleid
                                      join vGoodGymDisciplines on vGoodGymDisciplines.id = modules.disciplineid
                                      left join rating_table on rating_table.submoduleid = submodules.id
        group by submodules.id
        having count(rating_table.rate) > 0
    );

added 50m of time spent

Выставление доп. баллов за посещения занятий по физ. культуре весной 2023

Child items

Activity

Admin message

Выставление доп. баллов за посещения занятий по физ. культуре весной 2023

Child items

Linked items

Activity