use Encode;
use DBI;

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_id = 227;

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 $sql = 'SELECT ';
  for(my $cnt=0; $cnt<$events_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", $cgi_id);

  ## 開催場所を横につなげる 
  $sql .= " UNION ALL\n SELECT ";
  for(my $cnt=0; $cnt<$events_count; $cnt++)
  {
    $sql .=',' if ($cnt != 0);
    $sql .= sprintf("max(case t1.stage when %d then t2.place else null end)\n", $cnt+1);
  }
  $sql .= sprintf(" FROM events t1 left outer join places t2 on (t1.place_id=t2.id) WHERE tour_id=%d\n", $cgi_id);

  ## 会場を横につなげる
  $sql .= " UNION ALL\n SELECT ";
  for(my $cnt=0; $cnt<$events_count; $cnt++)
  {
    $sql .=',' if ($cnt != 0);
    $sql .= sprintf("max(case t1.stage when %d then t2.venue else null end)\n", $cnt+1);
  }
  $sql .= sprintf(" FROM events t1 left outer join venues t2 on (t1.venue_id=t2.id) WHERE tour_id=%d\n", $cgi_id);

  ## 区切り
  $sql .= " UNION ALL\n SELECT ";
  for(my $cnt=0; $cnt<$events_count; $cnt++)
  {
    $sql .=',' if ($cnt != 0);
    $sql .= "'---'";
  }
  $sql .="\n";

  ## セットリストを横につなげる
  $sql .= " UNION ALL\n SELECT ";
  my $sql2 = "SELECT ";
  for(my $cnt=0; $cnt<$events_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=1
                   GROUP BY t2.order_index
                   ORDER BY t2.order_index", $cgi_id);
  $sql .= " FROM (" . $sql2 . ") s1\n";

  ## 区切り
  $sql .= " UNION ALL\n SELECT ";
  for(my $cnt=0; $cnt<$events_count; $cnt++)
  {
    $sql .=',' if ($cnt != 0);
    $sql .= "'---'";
  }
  $sql .="\n";

  ## アンコールの演目を横につなげる
  $sql .= " UNION ALL\n SELECT ";
  $sql2 = "SELECT ";
  for(my $cnt=0; $cnt<$events_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=2
                   GROUP BY t2.order_index
                   ORDER BY t2.order_index", $cgi_id);
  $sql .= " FROM (" . $sql2 . ") s1\n";

  ## 区切り
  $sql .= " UNION ALL\n SELECT ";
  for(my $cnt=0; $cnt<$events_count; $cnt++)
  {
    $sql .=',' if ($cnt != 0);
    $sql .= "'---'";
  }
  $sql .="\n";

  ## Wアンコールの演目を横に並べる
  $sql .= " UNION ALL\n SELECT ";
  $sql2 = "SELECT ";
  for(my $cnt=0; $cnt<$events_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=3
                   GROUP BY t2.order_index
                   ORDER BY t2.order_index", $cgi_id);
  $sql .= " FROM (" . $sql2 . ") s1\n";

  print $sql;

  $dbh->disconnect();