#!/usr/bin/perl
#
# Anime recording system foltia
# http://www.dcc-jpl.com/soft/foltia/
#
# usage :2007-12-tvstation-updatedb.pl
#しょぼいカレンダー<http://cal.syoboi.jp/>の局名変更に伴うDB更新一括処理プログラム
#
#参考記事
# http://syobocal.g.hatena.ne.jp/gae/20071015
#
# DCC-JPL Japan/foltia project
#
#
use DBI;
use DBD::Pg;
$path = $0;
$path =~ s/2007-12-tvstation-updatedb.pl$//i;
if ($pwd  ne "./"){
push( @INC, "$path");
}
require "foltialib.pl";

	 $data_source = sprintf("dbi:%s:dbname=%s;host=%s;port=%d",
		$DBDriv,$DBName,$DBHost,$DBPort);
	 $dbh = DBI->connect($data_source,$DBUser,$DBPass) ||die $DBI::error;;


#旧の局名のそれぞれ局ID取得

$oldchibatvstationid = &getdbstationid("千葉テレビ");
$oldsaitamatvstationid = &getdbstationid("テレビ埼玉");
$oldtokyomxtvstationid =  &getdbstationid("東京MXテレビ");

#新局IDそれぞれ取得
$newchibatvstationid = &getdbstationid("チバテレビ");
unless ($newchibatvstationid >= 1){
	print "error チバテレビ not defineed yet.\n";
	exit;
}
$newsaitamatvstationid = &getdbstationid("テレ玉");
unless ($newsaitamatvstationid >= 1){
	print "error テレ玉 not defineed yet.\n";
	exit;
}
$newtokyomxtvstationid =  &getdbstationid("TOKYO MX");
unless ($newtokyomxtvstationid >= 1){
	print "error TOKYO MX not defineed yet.\n";
	exit;
}

#旧名を新名へ
$DBQuery =  "UPDATE foltia_station SET stationname = 'チバテレビ' WHERE stationname = '千葉テレビ'";
&runsql($DBQuery);
print "$DBQuery \n";
$DBQuery = "UPDATE foltia_station SET stationname = 'TOKYO MX' WHERE stationname = '東京MXテレビ'";
&runsql($DBQuery);
print "$DBQuery \n";
$DBQuery =  "UPDATE foltia_station SET stationname = 'テレ玉' WHERE stationname = 'テレビ埼玉'";
&runsql($DBQuery);
print "$DBQuery \n";

#新IDで入ってしまっているサブタイトルを旧IDに変更
$DBQuery = "UPDATE foltia_subtitle SET stationid = $oldchibatvstationid WHERE stationid = $newchibatvstationid ";
&runsql($DBQuery);
print "$DBQuery \n";
$DBQuery = "UPDATE foltia_subtitle SET stationid =$oldsaitamatvstationid WHERE stationid = $newsaitamatvstationid ";
&runsql($DBQuery);
print "$DBQuery \n";
$DBQuery = "UPDATE foltia_subtitle SET stationid = $oldtokyomxtvstationid WHERE stationid = $newtokyomxtvstationid ";
&runsql($DBQuery);
print "$DBQuery \n";


#新名新IDを削除
$DBQuery = "DELETE FROM foltia_station WHERE stationid = $newchibatvstationid ";
&runsql($DBQuery);
print "$DBQuery \n";
$DBQuery = "DELETE FROM foltia_station WHERE stationid = $newsaitamatvstationid ";
&runsql($DBQuery);
print "$DBQuery \n";
$DBQuery = "DELETE FROM foltia_station WHERE stationid = $newtokyomxtvstationid ";
&runsql($DBQuery);
print "$DBQuery \n";

sub getdbstationid{
my $stationname = $_[0];
my $DBQuery = "";
my $sth = "";
my @subticount;
my $returnvalue = "";
$DBQuery =  "SELECT stationid FROM foltia_station WHERE stationname = '$stationname' ";
$sth = $dbh->prepare($DBQuery);
$sth->execute();
@subticount = $sth->fetchrow_array;

if ($subticount[0] >= 1){
	$returnvalue = $subticount[0];
	print "$stationname ID is $returnvalue \n";
return $returnvalue;
}#endif
}#end sub

sub runsql{
my $DBQuery = $_[0];
my $sth = "";
$sth = $dbh->prepare($DBQuery);
$sth->execute();
}#end sub