#!/usr/bin/perl

##------------------------------------------------------------------------------
## Written by milano@edge.co.jp
## Copyright (C) 1997-2001 Livin' on the EDGE Limited.,
##           and milano@edge.co.jp
##------------------------------------------------------------------------------
## Time-stamp: <2001-05-19 21:32:59 milano>
##------------------------------------------------------------------------------

use strict;
use CGI;
use CGI::Carp qw/fatalsToBrowser/;

use HTML::Template;
use DBI;

use lib qw(/usr/local/apache/cgi-bin/site_perl);
use Kabegami::Setup;
use Kabegami::Utils;

my $tmpl_page = $TMPL_PATH . "c1.html";

my $q = new CGI;
my %in = $q->Vars();

my $dbh2 = DBI->connect($DSN2, $DB_USER, $DB_PASS) or die $DBI::errstr;
## サブカテゴリ列挙
my $all_arrayref = $dbh2->selectall_arrayref("select category, sub_category, name, image, comment from category where category = ? and sub_category != '00' and sub_category != '90' and validity_flg = 1 order by sort_order desc, sub_category", undef, $in{c1});

my $sth2_1 = $dbh2->prepare("select kid, pid from kabegami where category1 = ? or category2 = ? or category3 = ? order by kid");
my $sth2_2 = $dbh2->prepare("select type, celler_flg, count(*) from kabegami where validity_flg = 1 and if(web_expire = '0000-00-00', date_add(now(), interval 1 day), web_expire) > now() and (category1 = ? or category2 = ? or category3 = ?) group by type, celler_flg");

my $category_name = $dbh2->selectrow_array("select name from category where category = ? and sub_category = '00'", undef, $in{c1});

my @array;
for (my $i = 0; $i < @{$all_arrayref}; $i++) {
    my %hash;
    my $category_id = $all_arrayref->[$i][0] . $all_arrayref->[$i][1];

    $sth2_1->execute($category_id, $category_id, $category_id);
    my ($kid_first, $pid_first) = $sth2_1->fetchrow_array();
    my $hash = pid2hash($pid_first);
    my $path = pid2path($pid_first);

    $hash{name} = $all_arrayref->[$i][2];
    $hash{image} = (-e $HTML_PATH . "img/c1/$all_arrayref->[$i][0]$all_arrayref->[$i][1].jpg")
	? qq|<A href="/p/c2.pl?c1=$all_arrayref->[$i][0]&c2=$all_arrayref->[$i][1]"><IMG src="/img/c1/$all_arrayref->[$i][0]$all_arrayref->[$i][1].jpg" alt="$all_arrayref->[$i][2]" border="0" width="75" height="55"></A>|
	    : qq|<A href="/p/c2.pl?c1=$all_arrayref->[$i][0]&c2=$all_arrayref->[$i][1]"><IMG src="$FILE_SERVER[$hash]wallpaper/${path}${kid_first}/thumbnail.jpg" width="75" height="55" alt="$all_arrayref->[$i][2]" border="0"</A>|;
    $hash{comment} = $all_arrayref->[$i][4];

    $sth2_2->execute($category_id, $category_id, $category_id);
    $hash{F} = 0;
    $hash{P} = 0;
    $hash{S} = 0;
    $hash{celler} = 0;
    while (my ($type, $celler_flg, $number)= $sth2_2->fetchrow_array()) {
	$hash{$type} += $number;
	$hash{celler} += $number if $celler_flg;
    }

    push @array, \%hash;
}

$sth2_1->finish();
$sth2_2->finish();

my $filter = sub {
    my $text_ref = shift;
    $$text_ref =~ s/<TMPL_VAR name=category>/$in{c1}/g;
    $$text_ref =~ s/<TMPL_VAR name=sub_category>/00/g;
};
my $tmpl = new HTML::Template(filename => $tmpl_page,
                              filter => $filter,
                              loop_context_vars => 1,
                              cache => 0,
                              die_on_bad_params => $DEBUG);
$tmpl->param(loop => \@array,
#	     category_name => $category_name,
	     category_link => qq|<A href="/p/c1.pl?c1=$in{c1}">$category_name</A>|,
	 );

$dbh2->disconnect();

print $q->header(-type => 'text/html; charset=euc-jp'),
    $tmpl->output();