Выставление 100 баллов по физ. культуре по спискам

За спортивный разряд, абонементы и участие в командах. См. заявку https://support.sfedu.ru/Ticket/Display.html?id=212007

added A-db D-medium P-medium T-feature labels

assigned to @czen

  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,
                coalesce(rt12.rate, 0) as attendance1,
                coalesce(rt22.rate, 0) as attendance2,
                coalesce(rt11.rate, 0) as test_1,
                coalesce(rt21.rate, 0) as test_2
        from view_short_final_report
        join record_books on record_books.id = view_short_final_report.recordbookid
        join gym_100_rates on gym_100_rates.recordbookextid  = 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 vGoodGymDisciplines;
    create temporary table vGoodGymDisciplines as
    select * from public.get_gym_disciplines(18);


    insert into logs_rating (submoduleid, teacherid, rate, "action", recordbookid)
    select  view_roadmap.submoduleid,
            disciplines.authorid  as teacherid,
            view_roadmap.submodulerate as rate,
            'add',
            vGymRates.recordbookid as recordbook_id
    from
            vGymRates
            join vGoodGymDisciplines on vGoodGymDisciplines.id = vGymRates.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 = 1)
            or (view_roadmap.submoduleordernum = 2 and view_roadmap.moduleordernum = 1)
            or (view_roadmap.submoduleordernum = 1 and view_roadmap.moduleordernum = 2)
            or (view_roadmap.submoduleordernum = 2 and view_roadmap.moduleordernum = 2))
           ;

    insert into rating_table (teacherid, submoduleid, rate, date, recordbookid)
    select  disciplines.authorid  as teacherid,
            view_roadmap.submoduleid,
            view_roadmap.submodulerate as rate,
            now(),
            vGymRates.recordbookid as recordbook_id
    FROM
            vGymRates
            join vGoodGymDisciplines on vGoodGymDisciplines.id = vGymRates.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 = 1)
            or (view_roadmap.submoduleordernum = 2 and view_roadmap.moduleordernum = 1)
            or (view_roadmap.submoduleordernum = 1 and view_roadmap.moduleordernum = 2)
            or (view_roadmap.submoduleordernum = 2 and view_roadmap.moduleordernum = 2))
            left join rating_table on rating_table.recordbookid = vGymRates.recordbookid 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
    );
   
   update gym_100_rates
   set transfered  = true
   where gym_100_rates.recordbookextid  in (select vGymRates.recordbookid from vGymRates)