use Encode;
use CGI;
use DBI;

my $dbname="nanamizuki";
my $dbhost="your.database.host.addr";
my $dbuser="db user id";
my $dbpass="b password";

my $encstr = "utf-8";

my $cgi = CGI->new();
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost",$dbuser,$dbpass) or die  "cannot connect database.";

my $sth1 = $dbh->prepare('SELECT t1.id
                                ,t1.event_date
                                ,t2.place
                                ,t3.venue
                            FROM events t1 left outer join places t2 on( t1.place_id = t2.id)
                                           left outer join venues t3 on( t1.venue_id = t3.id)
                           WHERE tour_id = ?
                           ORDER BY event_date, id');

my $sth2 = $dbh->prepare('SELECT t2.song_title
                            FROM setlists t1 LEFT OUTER JOIN songs t2 ON( t1.song_id = t2.id)
                           WHERE event_id = ?
                             AND list_type = ?
                           ORDER BY t1.order_index');

my $event_id;
my $event_date;
my $place;
my $venue;
my $song_title;
my $cgi_id = $cgi->param('id');

  $dbh->{AutoCommit}=0;

  print $cgi->header({charset=>'utf-8'});
  print $cgi->start_html( {title=>encode($encstr,decode('euc-jp','公演情報')), charset=>'utf-8'} );

  print $cgi->start_table({border=>1});

  ############ 開催場所、会場情報展開

  print $cgi->start_Tr();

  $sth1->bind_param( 1, $cgi_id);
  $sth1->execute();
  $sth1->bind_col(  1, \$event_id   );
  $sth1->bind_col(  2, \$event_date );
  $sth1->bind_col(  3, \$place      );
  $sth1->bind_col(  4, \$venue      );

  while( $sth1->fetchrow_hashref() != undef )
  {
    $event_id   = encode($encstr, $event_id  );
    $event_date = encode($encstr, $event_date);
    $place      = encode($encstr, $place     );
    $venue      = encode($encstr, $venue     );

    print $cgi->td( {nowrap=>'nowrap'}, $cgi->p( encode($encstr, decode('euc-jp','公演日：')).$event_date ),
                                        $cgi->p( encode($encstr, decode('euc-jp','開催地：')).$place      ),
                                        $cgi->p( encode($encstr, decode('euc-jp','　会場：')).$venue      )   );
  }
  $dbh->commit();

  print $cgi->end_Tr();


  ############ セットリストの展開

  print $cgi->start_Tr();

  $sth1->bind_param( 1, $cgi_id);
  $sth1->execute();
  $sth1->bind_col(  1, \$event_id   );
  $sth1->bind_col(  2, \$event_date );
  $sth1->bind_col(  3, \$place      );
  $sth1->bind_col(  4, \$venue      );

  while( $sth1->fetchrow_hashref() != undef )
  {
    $event_id   = encode($encstr, $event_id);

    print $cgi->start_td({valign=>'top'});

    ## セットリスト
    $sth2->bind_param( 1, $event_id);
    $sth2->bind_param( 2, 1        );
    $sth2->execute();
    $sth2->bind_col( 1, \$song_title );

    print $cgi->p({class=>"list"}, encode($encstr, decode('euc-jp','セットリスト')));
    print $cgi->start_ol();
    while( $sth2->fetchrow_hashref() != undef )
    {
      $song_title = encode($encstr, $song_title);
      print $cgi->li({}, $song_title);
    }
    print $cgi->end_ol();
    $dbh->commit();

    ## アンコール
    $sth2->bind_param( 1, $event_id);
    $sth2->bind_param( 2, 2        );
    $sth2->execute();
    $sth2->bind_col( 1, \$song_title );

    print $cgi->p({class=>"list"}, encode($encstr, decode('euc-jp','アンコール')));
    print $cgi->start_ol();
    while( $sth2->fetchrow_hashref() != undef )
    {
      $song_title = encode($encstr, $song_title);
      print $cgi->li({}, $song_title);
    }
    print $cgi->end_ol();
    $dbh->commit();

    ## Wアンコール
    $sth2->bind_param( 1, $event_id);
    $sth2->bind_param( 2, 3        );
    $sth2->execute();
    $sth2->bind_col( 1, \$song_title );

    print $cgi->p({class=>"list"}, encode($encstr, decode('euc-jp','Wアンコール')));
    print $cgi->start_ol();
    while( $sth2->fetchrow_hashref() != undef )
    {
      $song_title = encode($encstr, $song_title);
      print $cgi->li({}, $song_title);
    }
    print $cgi->end_ol();
    $dbh->commit();

  }
  $sth1->finish();
  $sth2->finish();
  $dbh->commit();

  print $cgi->end_td();
  print $cgi->end_Tr();

  print $cgi->end_table();

  print $cgi->end_html();

  $dbh->disconnect();