0003_post_multi_rep_positions.py 7.45 KB
Newer Older
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
# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('memopol_scores', '0002_pre_multi_rep_positions'),
        ('representatives_positions', '0002_multi_rep_positions')
    ]

    operations = [
        migrations.RunSQL(
            """
            CREATE OR REPLACE VIEW "memopol_scores_v_representative_score"
            AS SELECT
                "source"."representative_id" AS "representative_id" ,
                SUM("source"."score") AS "score"
            FROM
                (
                    SELECT
                        "memopol_scores_dossierscore"."representative_id" AS "representative_id",
                        "memopol_scores_dossierscore"."score" AS "score"
                    FROM "memopol_scores_dossierscore"
                    UNION ALL
                    SELECT
                        "representatives_positions_position_representatives"."representative_id" AS "representative_id",
                        "memopol_scores_positionscore"."score" AS "score"
                    FROM
                        "memopol_scores_positionscore"
                        INNER JOIN "representatives_positions_position_representatives"
                            ON "representatives_positions_position_representatives"."position_id" = "memopol_scores_positionscore"."position_id"
                ) "source"
            GROUP BY
                "source"."representative_id"
            """
        ),


        migrations.RunSQL(
            """
            CREATE OR REPLACE VIEW "memopol_scores_v_theme_score"
            AS SELECT
                "scoresource"."representative_id" AS "representative_id",
                "scoresource"."theme_id" AS "theme_id",
                SUM("scoresource"."score") AS "score"
            FROM
                (
                    -- Score contribution for proposals
                    SELECT
                        "representatives_votes_vote"."representative_id" AS "representative_id",
                        "proposal_themes"."theme_id" AS "theme_id",
                        "memopol_scores_votescore"."score" AS "score"
                    FROM
                        "memopol_scores_votescore"
                        INNER JOIN "representatives_votes_vote"
                            ON "representatives_votes_vote"."id" = "memopol_scores_votescore"."vote_id"
                        INNER JOIN (
                                -- Proposals with a theme
                                SELECT
                                    "representatives_votes_proposal"."id" AS "proposal_id",
                                    "memopol_themes_theme_proposals"."theme_id" AS "theme_id"
                                FROM
                                    "representatives_votes_proposal"
                                    INNER JOIN "memopol_themes_theme_proposals"
                                        ON "representatives_votes_proposal"."id" = "memopol_themes_theme_proposals"."proposal_id"
                                UNION
                                -- Proposals in a dossier with a theme
                                SELECT
                                    "representatives_votes_proposal"."id" AS "proposal_id",
                                    "memopol_themes_theme_dossiers"."theme_id" AS "theme_id"
                                FROM
                                    "representatives_votes_proposal"
                                    INNER JOIN "representatives_votes_dossier"
                                        ON "representatives_votes_dossier"."id" = "representatives_votes_proposal"."dossier_id"
                                    INNER JOIN "memopol_themes_theme_dossiers"
                                        ON "memopol_themes_theme_dossiers"."dossier_id" = "representatives_votes_dossier"."id"
                            ) "proposal_themes"
                            ON "proposal_themes"."proposal_id" = "representatives_votes_vote"."proposal_id"
                    UNION ALL
                    -- Score contribution for positions
                    SELECT
                        "representatives_positions_position_representatives"."representative_id" AS "representative_id",
                        "memopol_themes_theme_positions"."theme_id" AS "theme_id",
                        "memopol_scores_positionscore"."score" AS "score"
                    FROM
                        "memopol_scores_positionscore"
                        INNER JOIN "representatives_positions_position_representatives"
                            ON "representatives_positions_position_representatives"."position_id" = "memopol_scores_positionscore"."position_id"
                        INNER JOIN "memopol_themes_theme_positions"
                            ON "memopol_themes_theme_positions"."position_id" = "memopol_scores_positionscore"."position_id"
                ) "scoresource"
            GROUP BY
                "scoresource"."representative_id",
                "scoresource"."theme_id"
            """
        ),

        migrations.RunSQL(
            """
            CREATE OR REPLACE FUNCTION refresh_scores()
            RETURNS VOID AS $$
            BEGIN
                TRUNCATE TABLE "memopol_scores_representativescore";

                TRUNCATE TABLE "memopol_scores_dossierscore";

                TRUNCATE TABLE "memopol_scores_votescore";

                INSERT INTO "memopol_scores_votescore" ("vote_id", "score")
                SELECT "vote_id", "score" FROM "memopol_scores_v_vote_score";

                INSERT INTO "memopol_scores_dossierscore" ("representative_id", "dossier_id", "score")
                SELECT "representative_id", "dossier_id", "score" FROM "memopol_scores_v_dossier_score";

                TRUNCATE TABLE "memopol_scores_positionscore";

                INSERT INTO "memopol_scores_positionscore" ("position_id", "score")
                SELECT "position_id", "score" FROM "memopol_scores_v_position_score";

                TRUNCATE TABLE "memopol_scores_themescore";

                INSERT INTO "memopol_scores_themescore" ("representative_id", "theme_id", "score")
                SELECT
                    "representatives_representative"."id",
                    "memopol_themes_theme"."id",
                    COALESCE("memopol_scores_v_theme_score"."score", 0)
                FROM
                    "representatives_representative"
                    INNER JOIN "memopol_themes_theme" ON 1=1
                    LEFT OUTER JOIN "memopol_scores_v_theme_score"
                        ON "memopol_scores_v_theme_score"."representative_id" = "representatives_representative"."id"
                        AND "memopol_scores_v_theme_score"."theme_id" = "memopol_themes_theme"."id";

                INSERT INTO "memopol_scores_representativescore" ("representative_id", "score")
                SELECT
                    "representatives_representative"."id",
                    COALESCE("memopol_scores_v_representative_score"."score", 0)
                FROM
                    "representatives_representative"
                    LEFT OUTER JOIN "memopol_scores_v_representative_score"
                        ON "memopol_scores_v_representative_score"."representative_id" = "representatives_representative"."id";
            END;
            $$ LANGUAGE PLPGSQL;
            """
        ),

        migrations.RunSQL(
            """
            SELECT refresh_scores();
            """
        )
    ]