#!/usr/bin/perl
#
# Anime recording system foltia
# http://www.dcc-jpl.com/soft/foltia/
#
# usage :getxml2db [tid]
#しょぼいカレンダー<http://cal.syoboi.jp/>から番組データXMLを取得しfoltia DBにインポートする
#
#オプション
#tid:
#
# DCC-JPL Japan/foltia project
#
#

use LWP::Simple;
use Jcode;
use Time::Local;
use DBI;
use DBD::Pg;
use Digest::MD5 qw(md5_hex);

$path = $0;
$path =~ s/getxml2db.pl$//i;
if ($pwd  ne "./"){
push( @INC, "$path");
}
require "foltialib.pl";


$processes =  &processfind("getxml2db.pl");
if ($processes > 1 ){
#print "process  found:$processes\n";
&writelog("getxml2db   processes exist. exit:");
exit;
}else{
#print "process NOT found:$processes\n";
&writelog("getxml2db  Normal launch.");
}

$tid = $ARGV[0];

$uri="http://cal.syoboi.jp/tid/$tid/time?Filter=ChAll&Filter2=All";

	my $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;;
	 $sth = $dbh->prepare("SET NAMES 'utf-8'");
	 $sth->execute();

#$dbh->{AutoCommit} = 0;

$content = LWP::Simple::get("$uri");

while ($content =~ /<tr class=.+? id=\"PID(\d+)\">\n+<!-- \d+ -->\n+<td class=nobr><a target=\"_blank\" href=\".*?\">(.+?)<\/a><\/td><td class=nobr><a href=\"\/\?date=(\d\d\d\d)\/(\d\d)\/(\d\d)\">\d\d\d\d-\d\d-\d\d\(.+?\)<\/a>&nbsp;(\d\d):(\d\d).*<\/td><td align=right>(\d+).*?<\/td><td align=right>(\d*?)<\/td>\n+<td>([^<]*).*?<\/td><td>.*?<\/td><td class=nobr>.*?<\/td><td class=nobr>.*?<\/td><\/tr>/mg){

	#1    2       3  4  5  6  7  8       9        10
	#pid, chname, y, m, d, h, m, length, countno, subtitle
#<tr class=past id="PID118198">
#<!-- 1118198 -->
#<td class=nobr><a target="_blank" href="http://www.at-x.com/program/index.html">AT-X</a></td><td class=nobr><a href="/?date=2008/10/22">2008-10-22(水)</a>&nbsp;10:00</td><td align=right>30</td><td align=right>2</td>

#<td>憎 発露 ―にくしみのはつろ―<div class="peComment"></div></td><td></td><td class=nobr><a class="btn" onclick="showHistory('118198',this)">2008-09-08</a></td><td class=nobr></td></tr>

$programSubTitle = $10;
$programSubTitle =~ s/\&lt\;/</gi;
$programSubTitle =~ s/\&gt\;/>/gi;
$programSubTitle =~ s/\&amp\;/\&/gi;
#Jcode::convert($programSubTitle,'euc');
$programSubTitle = $dbh->quote($programSubTitle);

$offsetmin = 0;  #TODO

$length = $8;

if ( $6 >= 24 ) {
	$recstartdate = &calcoffsetdate ( $3 . $4 . $5. '00' . $7, $6*60);
} else {
	$recstartdate = $3 . $4 . $5. $6 . $7;
}
$recenddate = &calcoffsetdate($recstartdate, $length);
$pid = $1;
$countno = $9;

$chname = $2;
#Jcode::convert($chname,'euc');
$stationid = &getstationid($chname);
#サブタイトル追加-------------------------------------------------

if ( 0 ) { #tid確認省略
				#番組があるか確認
				$DBQuery =  "SELECT count(*) FROM foltia_program WHERE tid = '$item{TID}'";
					 $sth = $dbh->prepare($DBQuery);
					$sth->execute();
				 @titlecount= $sth->fetchrow_array;
				 
				 if ($titlecount[0] == 0){
				#なければ追加

				#200412012359
				$nomalstarttime = substr($sttime,8,4);
				$DBQuery =  "insert into  foltia_program values ($item{TID},$programtitle,'','$nomalstarttime','$length','','','3','1','')";
				# $sth = $dbh->prepare($DBQuery);
				# $sth->execute();
				$oserr = $dbh->do($DBQuery);
				&writelog("getxml2db  ADD TV Progtam:$item{TID}:$programtitle");


				}else{
				#2006/2/26 
				#あったら、タイトル確認して
				$DBQuery =  "SELECT title FROM foltia_program WHERE tid = '$item{TID}'";
					 $sth = $dbh->prepare($DBQuery);
					$sth->execute();
				 @titlearray = $sth->fetchrow_array;
				#更新などされてたらupdate
				#print "$titlearray[0] / $programtitle\n";
				 if ($titlearray[0] ne "$programtitlename" ){
				 	$DBQuery =  "UPDATE  foltia_program  SET 	title = $programtitle where  tid = '$item{TID}' ";
				#	  $sth = $dbh->prepare($DBQuery);
				#	$sth->execute();
					$oserr = $dbh->do($DBQuery);
					&writelog("getxml2db  UPDATE TV Progtam:$item{TID}:$programtitle");
				 }#end if update
				}# end if TID

} #tid確認省略ここまで

#PIDがあるか確認
$DBQuery =  "SELECT count(*) FROM foltia_subtitle WHERE tid = '$tid' AND pid =  '$pid' ";
	 $sth = $dbh->prepare($DBQuery);
	$sth->execute();
 @subticount= $sth->fetchrow_array;
 if ($subticount[0]  >= 1){
	#PIDあったら上書き更新
 }else{
	#なければ追加
	
	#こっちに入る時刻はオフセットされた時刻!
	#そのままキューに入る形で
	if ($countno eq ""){
	$DBQuery = "insert into foltia_subtitle values ( '$pid','$tid','$stationid',null,$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length','test.m2t')";
	}else{
	$DBQuery = "insert into foltia_subtitle values ( '$pid','$tid','$stationid','$countno',$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length','test.m2t')";
	}
		 $sth = $dbh->prepare($DBQuery);
		$sth->execute();
	#$oserr = $dbh->do($DBQuery);
  }print "$pid/";

#print "$DBQuery\n\n\n";
#debug 20050803
#&writelog("getxml2db $DBQuery");


#}#if
}#foreach

#$oserr = $dbh->commit;
##	$dbh->disconnect();