use Encode; use DBI; use CGI; my $dbname="nanamizuki"; my $dbhost="your.database.host.addr"; my $dbuser="db user id"; my $dbpass="db password"; my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost",$dbuser,$dbpass) or die "cannot connect database."; my $cgi = CGI->new(); my $cgi_id = $cgi->param("id"); my $events_count = 0; $dbh->{AutoCommit}=0; ## 指定ツアーの公演回数を取得 my $sth0 = $dbh->prepare('SELECT count(*) from events WHERE tour_id=?'); $sth0->bind_param( 1, $cgi_id); $sth0->execute(); $sth0->bind_col( 1, \$events_count ); $sth0->fetch(); $sth0->finish(); $dbh->commit(); my @cols; my $sth1 = $dbh->prepare( gensql($events_count, $cgi_id) ); $sth1->execute(); for(my $cnt = 0; $cnt < $events_count+2 ; $cnt++) { $cols[$cnt]=" "; $sth1->bind_col( $cnt+1, \$cols[$cnt] ); } print $cgi->header({charset=>'utf-8'}); print $cgi->start_html( { title =>encode('utf-8',decode('euc-jp','公演情報')), charset =>'utf-8', style =>{ code =>["td { white-space: nowrap; border-style: none none none solid; }", "td.R { border-style: none solid none solid; }", "td.topL { border-style: solid none none solid; }", "td.topR { border-style: solid solid none solid; }", "td.btmL { border-style: none none solid solid; }", "td.btmR { border-style: none solid solid solid; }" ] } }); print $cgi->start_table(); print "\n"; my $sel1,$sel2; my $cssclass = ""; my $data = ""; while( $sth1->fetchrow_hashref() != undef ) { print $cgi->start_Tr(); for(my $i=0; $i<$events_count+2; $i++) { if ($i == 0) { $sel1 = $cols[$i]; next; } if ($i == 1) { $sel2 = $cols[$i]; next; } $cssclass = ""; if (($sel1 == 0)&&($sel2 == 1)) { $cssclass = "topL"; $cssclass = "topR" if ($i == ($events_count+2-1)); } if (($sel1 == 0)&&($sel2 == 2)) { $cssclass = ""; $cssclass = "R" if ($i == ($events_count+2-1)); } if (($sel1 == 0)&&($sel2 == 3)) { $cssclass = "btmL"; $cssclass = "btmR" if ($i == ($events_count+2-1)); } if ($sel1 == 1) { $cssclass = ""; $cssclass = "R" if ($i == ($events_count+2-1)); } $cols[$i] = "" if ("---" eq $cols[$i]); $data = encode('utf-8', $cols[$i]); $data = encode('utf-8', sprintf("%d. %s", $sel2, $cols[$i])) if (($sel1 == 1)&&("" ne $cols[$i])); print $cgi->td( $data ) if ("" eq $cssclass); print $cgi->td( {class=>$cssclass}, $data ) if ("" ne $cssclass); } print $cgi->end_Tr(); print "\n"; } $sth1->finish(); $dbh->commit(); print $cgi->end_table(); print $cgi->end_html(); $dbh->disconnect(); exit(0); sub gensep() { my ($count) = @_; my $sql; ## 区切りSQL $sql = "SELECT 0, 3, "; for(my $cnt=0; $cnt<$count; $cnt++) { $sql .=',' if ($cnt != 0); $sql .= "'---'"; } return $sql; } sub gensql() { my ($count, $id)=@_; my $sql = ""; my $sql2 = ""; ## 公演日を横につなげる $sql = 'SELECT 0 as sel1, 1 as sel2, '; for(my $cnt=0; $cnt<$count; $cnt++) { $sql .=',' if ($cnt != 0); $sql .= sprintf("cast(max(case stage when %d then event_date else null end) as varchar) as \"col%d\"\n", $cnt+1, $cnt); } $sql .= sprintf(" FROM events WHERE tour_id=%d\n", $id); ## 開催場所、会場を横につなげる my @v1 = ( {tbl=>'places', col=>'place'}, {tbl=>'venues', col=>'venue'} ); foreach my $item ( @v1 ) { $sql .= " UNION ALL\n SELECT 0, 2, "; for(my $cnt=0; $cnt<$count; $cnt++) { $sql .=',' if ($cnt != 0); $sql .= sprintf("max(case t1.stage when %d then t2.%s else null end)\n", $cnt+1, $item->{"col"}); } $sql .= sprintf(" FROM events t1 left outer join %s t2 on (t1.%s_id=t2.id) WHERE tour_id=%d\n",$item->{"tbl"}, $item->{"col"}, $id); } $sql .= " UNION ALL\n " . &gensep($count) . "\n"; ## セットリスト、アンコール演目、Wアンコール演目を横につなげる foreach my $item (1,2,3) { $sql .= " UNION ALL\n SELECT 1 as sel1, s1.order_index as sel2, "; $sql2 = "SELECT order_index, "; for(my $cnt=0; $cnt<$count; $cnt++) { $sql2 .=',' if ($cnt != 0); $sql2 .= sprintf("max(case t1.stage when %d then t3.song_title else null end) as \"col%d\"\n", $cnt+1, $cnt); $sql .=',' if ($cnt != 0); $sql .= sprintf("s1.col%d\n", $cnt); } $sql2 .= sprintf(" FROM events t1 left join setlists t2 on (t1.id = t2.event_id) left outer join songs t3 on (t2.song_id = t3.id) WHERE tour_id=%d AND t2.list_type=%d GROUP BY t2.order_index ORDER BY t2.order_index", $id, $item); $sql .= " FROM (" . $sql2 . ") s1\n"; $sql .= " UNION ALL\n " . &gensep($count) . "\n"; } return $sql; }