?xml version="1.0"?>

<queryset>

    <fullquery name="portal::new.get_elements">
        <querytext>
          select pd.name, pe.element_id
          from portal_datasources pd, portal_elements pe, portal_pages pp
          where pp.portal_id = :portal_id
            and pe.page_id = pp.page_id
            and pd.datasource_id = pe.datasource_id
        </querytext>
    </fullquery>

    <fullquery name="portal::delete.delete_permissions">
        <querytext>
            delete
            from acs_permissions
            where object_id = :portal_id
        </querytext>
    </fullquery>

    <fullquery name="portal::get_not_cached.select_portal_info">
        <querytext>
            select *
            from portals
            where portal_id = :portal_id
        </querytext>
    </fullquery>

    <fullquery name="portal::get_user_portal_id.select_portal_id">
        <querytext>
            select portal_id
            from portals
            where owner_id = :owner_id
              and package_id = :package_id
        </querytext>
    </fullquery>

    <fullquery name="portal::get_user_portal_id.select_user_name">
        <querytext>
          select first_names || ' ' || last_name as user_name
          from persons
          where person_id = :owner_id
        </querytext>
    </fullquery>

    <fullquery name="portal::set_values.update_portal">
        <querytext>
            update portals
            set name = :name,
                theme_id = :theme_id,
                template_id = :template_id
            where portal_id = :portal_id
        </querytext>
    </fullquery>

    <fullquery name="portal::get_render_data.portal_select">
        <querytext>
            select portals.name,
                   portals.portal_id,
                   portals.theme_id,
                   portal_pages.page_id
            from portals, portal_pages
            where portals.portal_id = :portal_id
              and portal_pages.sort_key = :page_num
              and portal_pages.portal_id = :portal_id
        </querytext>
    </fullquery>

    <fullquery name="portal::configure.portal_and_page_info_select">
        <querytext>
            select portals.name,
                   portals.portal_id,
                   portal_layouts.filename as template,
                   portal_pages.name as page_name,
                   portal_pages.layout_id as layout_id
            from portals,
                 portal_layouts,
                 portal_pages
            where portal_pages.portal_id = :portal_id
            and portal_pages.page_id = :page_id
            and portal_pages.portal_id = portals.portal_id
            and portal_pages.layout_id = portal_layouts.layout_id
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.hide_update">
        <querytext>
            update portal_elements
            set state = 'hidden'
            where element_id = :element_id
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.revert_max_page_id_select">
        <querytext>
            select max(page_id)
            from portal_pages
            where portal_id = :portal_id
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.revert_min_page_id_select">
        <querytext>
            select min(page_id)
            from portal_pages
            where portal_id = :portal_id
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.revert_move_elements_for_del">
        <querytext>
            select element_id
            from portal_elements
            where page_id = :max_page_id
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.revert_get_source_page_info">
        <querytext>
            select name,
                   layout_id,
                   sort_key
            from portal_pages
            where page_id = :source_page_id
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.revert_get_target_page_id">
        <querytext>
            select page_id
            from portal_pages
            where portal_id = :portal_id
            and sort_key = :sort_key
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.revert_page_update">
        <querytext>
            update portal_pages
            set name = :name,
                layout_id = :layout_id
            where page_id = :target_page_id
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.revert_get_source_elements">
        <querytext>
            select portal_elements.region,
                   portal_elements.sort_key,
                   portal_elements.state,
                   portal_datasources.datasource_id,
                   portal_datasources.name,
                   portal_elements.name as pretty_name
            from portal_elements,
                 portal_datasources
            where portal_elements.page_id = :source_page_id
            and portal_elements.datasource_id = portal_datasources.datasource_id
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.revert_get_target_element">
        <querytext>
            select portal_pages.element_id
            from portal_elements,
                 portal_pages
            where portal_pages.portal_id = :portal_id
            and portal_elements.page_id = portal_pages.page_id
            and portal_elements.datasource_id = :datasource_id
            and portal_elements.name = :name
        </querytext>
    </fullquery>

    <fullquery name="portal::configure_dispatch.revert_element_update">
        <querytext>
            update portal_elements
            set region = :region,
                sort_key = :sort_key,
                state = :state,
                page_id = :target_page_id
            where element_id = :target_element_id
        </querytext>
    </fullquery>

    <fullquery name="portal::get_master_template_id.get_master_template_id">
        <querytext>
            select portal_id as master_template_id
            from portals
            where package_id = :package_id
              and owner_id = 0
        </querytext>
    </fullquery>

    <fullquery name="portal::get_admin_portal_id.get_admin_portal_id">
        <querytext>
            select portal_id as admin_portal_id
            from portals
            where package_id = :package_id
              and owner_id = :package_id
        </querytext>
    </fullquery>

    <fullquery name="portal::get_page_count.select_page_count">
        <querytext>
            select count(*)
            from portal_pages
            where portal_id = :portal_id
        </querytext>
    </fullquery>

    <fullquery name="portal::get_page_list.select_page_ids">
        <querytext>
            select page_id
            from portal_pages
            where portal_id = :portal_id
            order by sort_key
        </querytext>
    </fullquery>

    <fullquery name="portal::navbar.list_page_nums_select">
        <querytext>
            select name,
                   sort_key as page_num
            from portal_pages
            where portal_id = :portal_id
            order by sort_key
        </querytext>
    </fullquery>

    <fullquery name="portal::get_theme_id.get_theme_id_select">
        <querytext>
            select theme_id
            from portals
            where portal_id = :portal_id
        </querytext>
    </fullquery>

    <fullquery name="portal::datasource_list.select_datasources">
        <querytext>
            select pd.datasource_id, pd.name
            from portal_datasources pd, portal_datasource_map pdm
            where pdm.package_id = :package_id
              and pd.datasource_id = pdm.datasource_id
            order by pd.name
        </querytext>
    </fullquery>

    <fullquery name="portal::initialize.select_portal_datasources">
        <querytext>
            select admin_p, name
            from portal_datasources
            where owner = 'portal'
        </querytext>
    </fullquery>

</queryset>