#!/usr/local/bin/perl

# $Id$

use strict;

BEGIN {
    package FJ::C::Arbeit::Sitemap::Index;

    use FJ::Controller;

    use base qw(FJ::Controller);

    #
    # PLUGINS
    #
    __PACKAGE__->plugins(qw(List::SEOPage));

    #
    # TEMPLATES
    #
    __PACKAGE__->templates(
        default => { file => 'tmpl/arbeit/sitemap/index.tmpl' },
    );

    #
    # PREPARE
    #
    sub prepare {
        my ($self) = @_;
        return 'default';
    }

    #
    # METHODS
    #
    sub default {
        my ($self) = @_;
        my $seo_page = $self->get_seo_page();

        #職種一覧取得
        my @shokushus = FJ::M::Shokushu->load_iterator_by_sql(
            _sql => <<SQL,
        SELECT DISTINCT
            shokushu.*
        FROM shokushu
        INNER JOIN job ON job.shokushu=shokushu.id
            AND job.permisson='allow'
            AND (job.keitai1 IN ('1', '8') OR job.keitai2 IN ('1', '8') OR job.keitai3 IN ('1', '8'))
        WHERE valid=1
        ORDER BY pattern
SQL
        )->as_array;

        #都道府県一覧取得
        my @prefectures = FJ::M::MPrefecture->load_iterator_by_sql(
            _sql => <<SQL,
        SELECT DISTINCT
        mp.*
        FROM job
        INNER JOIN job_place jp ON jp.job_id=job.id
        INNER JOIN m_place ON m_place.place_id=jp.place_id
        INNER JOIN m_prefecture mp ON m_place.prefecture_id=mp.prefecture_id
        WHERE 
            job.permisson='allow'
            AND (job.keitai1 IN ('1', '8') OR job.keitai2 IN ('1', '8') OR job.keitai3 IN ('1', '8'))
        ORDER BY sort
SQL
        )->as_array;

        #地域一覧取得
        my $areas = FJ::M::MArea->load_iterator(
        );
        my @areas;
        while (my $area = $areas->next){
            if(grep{$area->{area_id} eq $_->{area_id}} @prefectures){
                push @areas, {
                    area_id     => $area->{area_id},
                    area_name   => $area->{area_name},
                    prefectures => [grep{$area->{area_id} eq $_->{area_id}}@prefectures],
                }
            }
        }

        #こだわりワード一覧取得（求人有無チェック）
        my @job_focus_words = FJ::M::MFocusWord->load_iterator_by_sql(
            _sql => <<SQL,
        SELECT DISTINCT mfw.*
        FROM m_focus_word mfw
        INNER JOIN company_focus_word cfw ON mfw.focus_word_id=cfw.focus_word_id
        INNER JOIN company ON cfw.company_id=company.id AND permisson='allow'
        UNION 
        SELECT DISTINCT mfw.*
        FROM m_focus_word mfw
        INNER JOIN job_focus_word jfw ON mfw.focus_word_id=jfw.focus_word_id
        INNER JOIN job ON jfw.job_id=job.id
            AND job.permisson='allow'
            AND (job.keitai1 IN ('1', '8') OR job.keitai2 IN ('1', '8') OR job.keitai3 IN ('1', '8'))
SQL
        )->as_array;

        #スキル一覧取得（求人有無チェック）
        my @job_skills = FJ::M::MJobSkill->load_iterator_by_sql(
            _sql => <<SQL,
        SELECT DISTINCT
        mjs.*
        FROM m_job_skill mjs
        INNER JOIN m_job_sub_skill mjss ON mjs.job_skill_id=mjss.job_skill_id AND mjss.valid=1
        INNER JOIN job_sub_skill_job_count skill_count ON skill_count.job_sub_skill_id=mjss.job_sub_skill_id
        INNER JOIN job ON job.id=skill_count.job_id
            AND job.permisson='allow'
            AND (job.keitai1 IN ('1', '8') OR job.keitai2 IN ('1', '8') OR job.keitai3 IN ('1', '8'))
        WHERE mjs.valid=1
SQL
        )->as_array;


        $self->set_common_seo_template_params();

        $self->template->param(
            shokushus => \@shokushus,
            areas => \@areas,
            job_focus_words => \@job_focus_words,
            job_skills => \@job_skills,
        );

        $self->template->param(
            footer => $seo_page->show_footer(
                page_title => 'アルバイト サイトマップ',
            ),
            sc_tag => $self->show_sc_tag(
                pageName => q{document.URL},
                channel => q{"list"},
            ),
            _no_escape => 1,
        );
    }

    1;
} # End of package definition scope.

FJ::C::Arbeit::Sitemap::Index->new()->process();