service.data.impl/src/main/resources/db-changelog-1.1.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<changeSet author="pether" id="1414872417007-202">
<createView
viewName="view_document_data_committee_report_url">select id,committee_report_url_xml from document_data where committee_report_url_xml is not null</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-203">
<modifyDataType columnName="proposal" newDataType="${clob.type}"
tableName="committee_proposal_data" />
</changeSet>
<changeSet author="pether" id="1414872417007-204">
<createView viewName="view_riksdagen_committee">select distinct detail as EMBEDDED_ID_DETAIL, org_code as EMBEDDED_ID_ORG_CODE, count(org_code) as total_assignments from assignment_data where org_code is not null and assignment_type='uppdrag' group by detail,org_code</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-205">
<createView viewName="view_riksdagen_goverment">select distinct detail as EMBEDDED_ID_DETAIL from assignment_data where role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen'</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-206">
<createView
viewName="view_riksdagen_goverment_roles">select distinct detail as EMBEDDED_ID_DETAIL, role_code,count(detail) as total_assignments from assignment_data where role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen' group by detail,role_code</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-207">
<createView
viewName="view_riksdagen_committee_decisions">select id,title,header,hangar_id,label as committee_report,committee_document_data.rm,end_number,issue_number,org,created_date,public_date,committee_proposal_url_xml,decision_type,ballot_id,against_proposal_parties,against_proposal_number,winner from committee_proposal_data LEFT JOIN committee_document_data ON committee_document_data.label=committee_report and committee_proposal_data.rm=committee_document_data.rm</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-208">
<createView
viewName="view_riksdagen_goverment_proposals">select id,title,sub_title,status,org,hangar_id,label,made_public_date,number_value,document_status_url_xml from document_data where document_type='PROP'</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-209">
<createView
viewName="view_riksdagen_member_proposals">select * from document_element where document_type='MOT'</createView>
</changeSet>
<changeSet author="pether (generated)" id="1416258476613-210">
<createTable tableName="document_proposal_container">
<column name="hjid" type="INT8">
<constraints nullable="false" />
</column>
<column name="proposal_document_proposal_c_0" type="INT8" />
</createTable>
</changeSet>
<changeSet author="pether (generated)" id="1416258476613-211">
<createTable tableName="document_proposal_data">
<column name="hjid" type="INT8">
<constraints nullable="false" />
</column>
<column name="chamber" type="VARCHAR(255)" />
<column name="committee" type="VARCHAR(255)" />
<column name="decision_type" type="VARCHAR(255)" />
<column name="designation" type="INT4" />
<column name="processed_in" type="VARCHAR(255)" />
<column name="proposal_number" type="INT4" />
<column name="wording" type="VARCHAR(255)" />
<column name="wording_2" type="VARCHAR(255)" />
<column name="wording_3" type="VARCHAR(255)" />
<column name="wording_4" type="VARCHAR(255)" />
</createTable>
</changeSet>
<changeSet author="pether (generated)" id="1416258476613-212">
<addColumn tableName="document_status_container">
<column name="document_proposal_document_s_0" type="INT8" />
</addColumn>
<dropColumn columnName="document_proposal" tableName="document_status_container" />
</changeSet>
<changeSet author="pether (generated)" id="1416258476613-213">
<addPrimaryKey columnNames="hjid"
constraintName="document_proposal_container_pkey" tableName="document_proposal_container" />
</changeSet>
<changeSet author="pether (generated)" id="1416258476613-214">
<addPrimaryKey columnNames="hjid" constraintName="document_proposal_data_pkey"
tableName="document_proposal_data" />
</changeSet>
<changeSet author="pether (generated)" id="1416258476613-215">
<addForeignKeyConstraint baseColumnNames="proposal_document_proposal_c_0"
baseTableName="document_proposal_container" constraintName="fk_m55tt4vaimgb5qk7xj9mgxmry"
deferrable="false" initiallyDeferred="false" onDelete="NO ACTION"
onUpdate="NO ACTION" referencedColumnNames="hjid"
referencedTableName="document_proposal_data" />
</changeSet>
<changeSet author="pether (generated)" id="1416258476613-216">
<addForeignKeyConstraint baseColumnNames="document_proposal_document_s_0"
baseTableName="document_status_container" constraintName="fk_iirofquegnrpnuonvnydf6wfb"
deferrable="false" initiallyDeferred="false" onDelete="NO ACTION"
onUpdate="NO ACTION" referencedColumnNames="hjid"
referencedTableName="document_proposal_container" />
</changeSet>
<changeSet author="pether" id="1414872417007-217">
<modifyDataType columnName="wording" newDataType="${clob.type}"
tableName="document_proposal_data" />
<modifyDataType columnName="wording_2" newDataType="${clob.type}"
tableName="document_proposal_data" />
<modifyDataType columnName="wording_3" newDataType="${clob.type}"
tableName="document_proposal_data" />
<modifyDataType columnName="wording_4" newDataType="${clob.type}"
tableName="document_proposal_data" />
</changeSet>
<changeSet author="pether" id="1414872417007-218">
<createView
viewName="view_riksdagen_committee_roles">select distinct detail as EMBEDDED_ID_DETAIL, role_code,count(detail) as total_assignments from assignment_data where org_code is not null and assignment_type='uppdrag' group by detail,role_code</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-219">
<dropView viewName="view_riksdagen_committee" /> <createView viewName="view_riksdagen_committee">SELECT DISTINCT assignment_data.detail AS embedded_id_detail, assignment_data.org_code AS embedded_id_org_code,count(assignment_data.org_code) AS total_assignments,min(from_date) as first_assignment_date,max(to_date) as last_assignment_date FROM assignment_data WHERE assignment_data.org_code IS NOT NULL AND assignment_data.assignment_type = 'uppdrag' GROUP BY assignment_data.detail, assignment_data.org_code</createView>
<createView
viewName="view_riksdagen_committee_parliament_member_proposal">SELECT view_riksdagen_committee.embedded_id_detail,
view_riksdagen_committee.embedded_id_org_code,
view_riksdagen_committee.total_assignments,
document_data.id,
document_data.committee_report_url_xml,
document_data.document_status_url_www,
document_data.document_status_url_xml,
document_data.document_type,
document_data.document_url_html,
document_data.document_url_text,
document_data.final_number,
document_data.hangar_id,
document_data.label,
document_data.made_public_date,
document_data.number_value,
document_data.org,
document_data.rm,
document_data.status,
document_data.sub_title,
document_data.sub_type,
document_data.temp_label,
document_data.title
FROM view_riksdagen_committee
LEFT JOIN document_data ON view_riksdagen_committee.embedded_id_org_code = document_data.org
WHERE document_data.document_type = 'MOT'</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-220">
<dropView viewName="view_riksdagen_goverment_roles" /> <createView
viewName="view_riksdagen_goverment_roles">select distinct detail as EMBEDDED_ID_DETAIL, role_code,count(detail) as total_assignments,min(from_date) as first_assignment_date,max(to_date) as last_assignment_date from assignment_data where role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen' group by detail,role_code</createView>
<dropView viewName="view_riksdagen_committee_roles" /> <createView
viewName="view_riksdagen_committee_roles">select distinct detail as EMBEDDED_ID_DETAIL, role_code,count(detail) as total_assignments,min(from_date) as first_assignment_date,max(to_date) as last_assignment_date from assignment_data where org_code is not null and assignment_type='uppdrag' group by detail,role_code</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-221">
<dropView viewName="view_riksdagen_goverment" /> <createView viewName="view_riksdagen_goverment">select distinct detail as name_id,count(assignment_data.detail) AS total_assignments,min(from_date) as first_assignment_date,max(to_date) as last_assignment_date from assignment_data where role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen' group by detail</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-222">
<createView viewName="view_riksdagen_party_member">select * from sweden_political_party LEFT JOIN person_data ON person_data.party = sweden_political_party.short_code where person_data.party = sweden_political_party.short_code and short_code !='-'</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-223">
<createView viewName="view_riksdagen_party">select distinct party_id,party_name,short_code,count(party_id) as head_count from view_riksdagen_party_member group by party_id,party_name,short_code</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-224">
<dropView viewName="view_riksdagen_party" /> <createView viewName="view_riksdagen_party">select distinct party_id as party_number,party_name,short_code as party_id,website,registered_date,count(party_id) as head_count from view_riksdagen_party_member group by party_id,party_name,short_code,website,registered_date</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-225">
<dropView viewName="view_riksdagen_goverment" /> <createView viewName="view_riksdagen_goverment">select distinct detail as name_id,count(assignment_data.detail) AS total_assignments,min(from_date) as first_assignment_date,max(to_date) as last_assignment_date,sum((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) as total_days_served ,(CASE WHEN max(to_date) > CURRENT_DATE THEN true ELSE false END) as active,sum((CASE WHEN to_date > CURRENT_DATE THEN 1 ELSE 0 END)) as current_member_size from assignment_data where role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen' and status !='LEDIG' group by detail</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-226">
<dropView viewName="view_riksdagen_committee_parliament_member_proposal" />
<dropView viewName="view_riksdagen_committee" /> <createView viewName="view_riksdagen_committee">SELECT DISTINCT assignment_data.detail AS embedded_id_detail, assignment_data.org_code AS embedded_id_org_code,count(assignment_data.org_code) AS total_assignments,min(from_date) as first_assignment_date,max(to_date) as last_assignment_date,sum((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) as total_days_served ,(CASE WHEN max(to_date) > CURRENT_DATE THEN true ELSE false END) as active,sum((CASE WHEN to_date > CURRENT_DATE THEN 1 ELSE 0 END)) as current_member_size FROM assignment_data WHERE assignment_data.org_code IS NOT NULL AND assignment_data.assignment_type = 'uppdrag' GROUP BY assignment_data.detail, assignment_data.org_code</createView>
<createView
viewName="view_riksdagen_committee_parliament_member_proposal">SELECT view_riksdagen_committee.embedded_id_detail,
view_riksdagen_committee.embedded_id_org_code,
view_riksdagen_committee.total_assignments,
document_data.id,
document_data.committee_report_url_xml,
document_data.document_status_url_www,
document_data.document_status_url_xml,
document_data.document_type,
document_data.document_url_html,
document_data.document_url_text,
document_data.final_number,
document_data.hangar_id,
document_data.label,
document_data.made_public_date,
document_data.number_value,
document_data.org,
document_data.rm,
document_data.status,
document_data.sub_title,
document_data.sub_type,
document_data.temp_label,
document_data.title
FROM view_riksdagen_committee
LEFT JOIN document_data ON view_riksdagen_committee.embedded_id_org_code = document_data.org
WHERE document_data.document_type = 'MOT'</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-227">
<createView
viewName="view_riksdagen_goverment_role_member">select detail as EMBEDDED_ID_DETAIL, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date as total_days_served,(CASE WHEN to_date > CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen'</createView>
<createView
viewName="view_riksdagen_committee_role_member">select detail as EMBEDDED_ID_DETAIL, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date as total_days_served,(CASE WHEN to_date > CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where org_code is not null and assignment_type='uppdrag'</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-228">
<createView viewName="view_riksdagen_politician">select view_riksdagen_party_member.id as id,max(first_name) as first_name,max(last_name) as last_name,max(party) as party,min(assignment_data.from_date) as first_assignment_date,max(assignment_data.to_date) as last_assignment_date,sum((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) as total_days_served,sum( CASE WHEN assignment_type='kammaruppdrag' THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_parliament_seat,sum( CASE WHEN (org_code is not null and assignment_type='uppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_committee,sum( CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_government,sum( CASE WHEN (detail='Europaparlamentet') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_eu_parliament, (CASE WHEN max(to_date) > CURRENT_DATE THEN true ELSE false END) as active,count(*) total_assignments,sum((CASE WHEN to_date > CURRENT_DATE THEN 1 ELSE 0 END)) as current_assignments from assignment_data left join view_riksdagen_party_member on assignment_data.intressent_id = view_riksdagen_party_member.id group by view_riksdagen_party_member.id</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-229">
<dropView viewName="view_riksdagen_politician" /> <createView viewName="view_riksdagen_politician">select view_riksdagen_party_member.id as person_id,max(first_name) as first_name,max(last_name) as last_name,max(party) as party,min(assignment_data.from_date) as first_assignment_date,max(assignment_data.to_date) as last_assignment_date,sum((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) as total_days_served,sum( CASE WHEN assignment_type='kammaruppdrag' THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_parliament_seat,sum( CASE WHEN (org_code is not null and assignment_type='uppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_committee,sum( CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_government,sum( CASE WHEN (detail='Europaparlamentet') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_eu_parliament, (CASE WHEN max(to_date) > CURRENT_DATE THEN true ELSE false END) as active,count(*) total_assignments,sum((CASE WHEN to_date > CURRENT_DATE THEN 1 ELSE 0 END)) as current_assignments from assignment_data left join view_riksdagen_party_member on assignment_data.intressent_id = view_riksdagen_party_member.id group by view_riksdagen_party_member.id</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-230">
<dropView viewName="view_riksdagen_politician" /> <createView viewName="view_riksdagen_politician">select view_riksdagen_party_member.id as person_id,max(first_name) as first_name,max(last_name) as last_name,max(party) as party,min(assignment_data.from_date) as first_assignment_date,max(assignment_data.to_date) as last_assignment_date,sum((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) as total_days_served,sum( CASE WHEN assignment_type='kammaruppdrag' THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_parliament,sum( CASE WHEN (org_code is not null and assignment_type='uppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_committee,sum( CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_government,sum( CASE WHEN (detail='Europaparlamentet') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_eu, (CASE WHEN max(to_date) > CURRENT_DATE THEN true ELSE false END) as active,count(*) total_assignments,sum((CASE WHEN to_date > CURRENT_DATE THEN 1 ELSE 0 END)) as current_assignments,(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND detail='Europaparlamentet' THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_eu,(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_government,(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (org_code is not null and assignment_type='uppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_committee,(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND assignment_type='kammaruppdrag' THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_parliament from assignment_data left join view_riksdagen_party_member on assignment_data.intressent_id = view_riksdagen_party_member.id group by view_riksdagen_party_member.id</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-231">
<createView viewName="view_riksdagen_party_summary">select party,
min(first_assignment_date) as first_assignment_date,
max(last_assignment_date) as last_assignment_date,
sum(total_days_served::int8)::int8 as total_days_served,
sum(total_days_served_parliament::int8)::int8 as total_days_served_parliament,
sum(total_days_served_committee::int8)::int8 as total_days_served_committee,
sum(total_days_served_government::int8)::int8 as total_days_served_government,
sum(total_days_served_eu::int8)::int8 as total_days_served_eu,
bool_or(active) as active,
bool_or(active_eu) as active_eu,
bool_or(active_government) as active_government ,
bool_or(active_committee) as active_committee,
bool_or(active_parliament) as active_parliament,
sum(total_assignments::int8)::int8 as total_assignments,
sum(current_assignments::int8)::int8 as current_assignments
from view_riksdagen_politician group by party</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-232">
<dropView viewName="view_riksdagen_party_summary" /> <createView viewName="view_riksdagen_party_summary">select party,
min(first_assignment_date) as first_assignment_date,
max(last_assignment_date) as last_assignment_date,
sum(total_days_served::int8)::int8 as total_days_served,
sum(total_days_served_parliament::int8)::int8 as total_days_served_parliament,
sum(total_days_served_committee::int8)::int8 as total_days_served_committee,
sum(total_days_served_government::int8)::int8 as total_days_served_government,
sum(total_days_served_eu::int8)::int8 as total_days_served_eu,
bool_or(active) as active,
bool_or(active_eu) as active_eu,
bool_or(active_government) as active_government ,
bool_or(active_committee) as active_committee,
bool_or(active_parliament) as active_parliament,
sum(CASE WHEN active THEN 1 ELSE 0 END) as total_active,
sum(CASE WHEN active_eu THEN 1 ELSE 0 END) as total_active_eu,
sum(CASE WHEN active_government THEN 1 ELSE 0 END) as total_active_government ,
sum(CASE WHEN active_committee THEN 1 ELSE 0 END) as total_active_committee,
sum(CASE WHEN active_parliament THEN 1 ELSE 0 END) as total_active_parliament,
sum(total_assignments::int8)::int8 as total_assignments,
sum(current_assignments::int8)::int8 as current_assignments
from view_riksdagen_politician group by party</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-233">
<dropView viewName="view_riksdagen_party_summary" />
<dropView viewName="view_riksdagen_politician" /> <createView viewName="view_riksdagen_politician">select view_riksdagen_party_member.id as person_id,max(first_name) as first_name,max(last_name) as last_name,max(party) as party,min(assignment_data.from_date) as first_assignment_date,max(assignment_data.to_date) as last_assignment_date,sum((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) as total_days_served,sum( CASE WHEN assignment_type='kammaruppdrag' and assignment_data.status !='LEDIG' THEN ((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE (CASE WHEN assignment_type='kammaruppdrag' and assignment_data.status ='LEDIG' THEN -((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) END) as total_days_served_parliament,sum( CASE WHEN (org_code is not null and assignment_type='uppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_committee,sum( CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_government,sum( CASE WHEN (detail='Europaparlamentet') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_eu, (CASE WHEN max(to_date) > CURRENT_DATE THEN true ELSE false END) as active,count(*) total_assignments,sum((CASE WHEN to_date > CURRENT_DATE THEN 1 ELSE 0 END)) as current_assignments,(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND detail='Europaparlamentet' THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_eu,(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_government,(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (org_code is not null and assignment_type='uppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_committee,(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status !='LEDIG' THEN 1 ELSE (CASE WHEN to_date > CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status ='LEDIG' THEN -1 ELSE 0 END) END) > 0 THEN true ELSE false END) as active_parliament from assignment_data left join view_riksdagen_party_member on assignment_data.intressent_id = view_riksdagen_party_member.id group by view_riksdagen_party_member.id</createView>
<createView viewName="view_riksdagen_party_summary">select party,
min(first_assignment_date) as first_assignment_date,
max(last_assignment_date) as last_assignment_date,
sum(total_days_served::int8)::int8 as total_days_served,
sum(total_days_served_parliament::int8)::int8 as total_days_served_parliament,
sum(total_days_served_committee::int8)::int8 as total_days_served_committee,
sum(total_days_served_government::int8)::int8 as total_days_served_government,
sum(total_days_served_eu::int8)::int8 as total_days_served_eu,
bool_or(active) as active,
bool_or(active_eu) as active_eu,
bool_or(active_government) as active_government ,
bool_or(active_committee) as active_committee,
bool_or(active_parliament) as active_parliament,
sum(CASE WHEN active THEN 1 ELSE 0 END) as total_active,
sum(CASE WHEN active_eu THEN 1 ELSE 0 END) as total_active_eu,
sum(CASE WHEN active_government THEN 1 ELSE 0 END) as total_active_government ,
sum(CASE WHEN active_committee THEN 1 ELSE 0 END) as total_active_committee,
sum(CASE WHEN active_parliament THEN 1 ELSE 0 END) as total_active_parliament,
sum(total_assignments::int8)::int8 as total_assignments,
sum(current_assignments::int8)::int8 as current_assignments
from view_riksdagen_politician group by party</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-234">
<createView
viewName="view_riksdagen_politician_document">select hjid as id,document_document_status_con_0 as doc_id,document_type,label,made_public_date,org,number_value,rm,status,sub_title,sub_type,temp_label,title,role_description,person_reference_id,reference_name,party_short_code,order_number from (select * from document_status_container left join document_data on document_status_container.document_document_status_con_0 = document_data.id) as e3 left join (select document_person_reference_da_0.hjid as id,role_description,person_reference_id,reference_name,party_short_code,order_number,document_person_reference_li_1 from document_person_reference_da_0 left join (select document_person_reference_co_0.hjid as person_id_ref from document_status_container left join document_person_reference_co_0 on document_status_container.hjid = document_person_reference_co_0.hjid) e2 on document_person_reference_da_0.document_person_reference_li_1 = e2.person_id_ref) e4 on e3.document_person_reference_co_1 = e4.document_person_reference_li_1</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-235">
<dropView viewName="view_riksdagen_party_summary" />
<dropView viewName="view_riksdagen_politician" /> <createView viewName="view_riksdagen_politician">select
view_riksdagen_party_member.id as person_id,
max(first_name) as first_name,
max(last_name) as last_name,
max(party) as party,
max(born_year) as born_year,
max(gender) as gender,
min(assignment_data.from_date) as first_assignment_date,
max(assignment_data.to_date) as last_assignment_date,
sum((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) as total_days_served,
sum( CASE WHEN assignment_type='kammaruppdrag' and assignment_data.status !='LEDIG' THEN ((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE (CASE WHEN assignment_type='kammaruppdrag' and assignment_data.status ='LEDIG' THEN -((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) END) as total_days_served_parliament,
sum( CASE WHEN (org_code is not null and assignment_type='uppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_committee,
sum( CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_government,
sum( CASE WHEN (detail='Europaparlamentet') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_eu,
(CASE WHEN max(to_date) > CURRENT_DATE THEN true ELSE false END) as active,
count(*) total_assignments,
sum((CASE WHEN to_date > CURRENT_DATE THEN 1 ELSE 0 END)) as current_assignments,
sum( CASE WHEN (assignment_type='talmansuppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_speaker,
CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (assignment_type='talmansuppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END as active_speaker,
sum( CASE WHEN (assignment_type='partiuppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date) ELSE 0 END) as total_days_served_party,
CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (assignment_type='partiuppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END as active_party,
sum((CASE WHEN to_date > CURRENT_DATE and org_code is not null and assignment_type='uppdrag' THEN 1 ELSE 0 END)) as current_committee_assignments,
sum((CASE WHEN to_date > CURRENT_DATE and (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END)) as current_ministry_assignments,
sum((CASE WHEN to_date > CURRENT_DATE and assignment_type='partiuppdrag' THEN 1 ELSE 0 END)) as current_party_assignments,
sum((CASE WHEN to_date > CURRENT_DATE and assignment_type='talmansuppdrag' THEN 1 ELSE 0 END)) as current_speaker_assignments,
sum((CASE WHEN org_code is not null and assignment_type='uppdrag' THEN 1 ELSE 0 END)) as total_committee_assignments,
sum((CASE WHEN assignment_type='partiuppdrag' THEN 1 ELSE 0 END)) as total_party_assignments,
sum((CASE WHEN assignment_type='talmansuppdrag' THEN 1 ELSE 0 END)) as total_speaker_assignments,
sum((CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END)) as total_ministry_assignments,
(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND detail='Europaparlamentet' THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_eu,
(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_government,(
(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (org_code is not null and assignment_type='uppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END)) as active_committee,
(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status !='LEDIG' THEN 1 ELSE (CASE WHEN to_date > CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status ='LEDIG' THEN -1 ELSE 0 END) END) > 0 THEN true ELSE false END) as active_parliament
from assignment_data left join view_riksdagen_party_member on assignment_data.intressent_id = view_riksdagen_party_member.id group by view_riksdagen_party_member.id</createView>
<createView viewName="view_riksdagen_party_summary">select
party,
min(first_assignment_date) as first_assignment_date,
max(last_assignment_date) as last_assignment_date,
sum(total_days_served::int8)::int8 as total_days_served,
sum(total_days_served_parliament::int8)::int8 as total_days_served_parliament,
sum(total_days_served_committee::int8)::int8 as total_days_served_committee,
sum(total_days_served_government::int8)::int8 as total_days_served_government,
sum(total_days_served_eu::int8)::int8 as total_days_served_eu,
sum(total_days_served_speaker::int8)::int8 as total_days_served_speaker,
sum(total_days_served_party::int8)::int8 as total_days_served_party,
bool_or(active) as active,
bool_or(active_eu) as active_eu,
bool_or(active_government) as active_government ,
bool_or(active_committee) as active_committee,
bool_or(active_parliament) as active_parliament,
bool_or(active_speaker) as active_speaker,
bool_or(active_party) as active_party,
sum(CASE WHEN active THEN 1 ELSE 0 END) as total_active,
sum(CASE WHEN active_eu THEN 1 ELSE 0 END) as total_active_eu,
sum(CASE WHEN active_government THEN 1 ELSE 0 END) as total_active_government,
sum(CASE WHEN active_committee THEN 1 ELSE 0 END) as total_active_committee,
sum(CASE WHEN active_parliament THEN 1 ELSE 0 END) as total_active_parliament,
sum(total_assignments::int8)::int8 as total_assignments,
sum(total_party_assignments::int8)::int8 as total_party_assignments,
sum(total_committee_assignments::int8)::int8 as total_committee_assignments,
sum(total_ministry_assignments::int8)::int8 as total_ministry_assignments,
sum(total_speaker_assignments::int8)::int8 as total_speaker_assignments,
sum(current_assignments::int8)::int8 as current_assignments,
sum(current_party_assignments::int8)::int8 as current_party_assignments,
sum(current_committee_assignments::int8)::int8 as current_committee_assignments,
sum(current_ministry_assignments::int8)::int8 as current_ministry_assignments,
sum(current_speaker_assignments::int8)::int8 as current_speaker_assignments
from view_riksdagen_politician group by party</createView>
</changeSet>
</databaseChangeLog>