#!/usr/bin/perl
#
# Anime recording system foltia
# http://www.dcc-jpl.com/soft/foltia/
#
#schedulecheck.pl
#
#DBの予約から定期的に予約キューを作り出します
#
# DCC-JPL Japan/foltia project
#
#

use DBI;
use DBD::Pg;
use Schedule::At;
use Time::Local;

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

require "foltialib.pl";

#予約番組探し
#$now = &epoch2foldate(`date +%s`);
#$now = &epoch2foldate($now);
#$checkrangetime = $now   + 15*60;#15分後まで
#$checkrangetime =  &epoch2foldate($checkrangetime);

	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;;



#startweektype解析
$DBQuery =  "SELECT tid, startweektype, starttime, lengthmin FROM foltia_program WHERE startweektype is not null AND startweektype != ''";
	 $sth = $dbh->prepare($DBQuery);
	$sth->execute();
	
while ( @weekly= $sth->fetchrow_array() ) {
	$typekey = substr( $weekly[1], 0, index( $weekly[1], ":" ) );
	$typeval = substr( $weekly[1], index( $weekly[1], ":" )+1  );
	
	$nowtime = time();
	$folnow = &epoch2foldate($nowtime);
	
	if ( $typekey eq "week" ) {
		
		$recstaid = substr( $typeval, 0, index( $typeval, "," ) );
		$recweek = substr( $typeval, index( $typeval, "," )+1  );
		
		for ( $i = 0; $i < 7; $i++  ) {
			$t = $nowtime + $i*24*60*60;
			$weekday = (localtime($t))[6];
			if ( $recweek =~ /$weekday/ ) {
				($s, $mi, $h, $d, $mo, $y, $w) = localtime($t);
    			$mo++; $y += 1900;
    			
    			$startdt = sprintf("%04d%02d%02d%s", $y, $mo, $d, $weekly[2]);
    			if ( $folnow > $startdt ) { next; } #もう過ぎてる
    			
    			$DBQuery = "SELECT count(*) FROM foltia_subtitle WHERE tid = $weekly[0] AND startdatetime = $startdt";
    			$sth2 = $dbh->prepare($DBQuery);
    			$sth2->execute();
    			@stcount = $sth2->fetchrow_array;
    			if ( $stcount[0] != 0 ) { next; } #登録済み
    			
    			#登録する
    			
    			#pid取得
    			$DBQuery = "SELECT count(pid), min(pid) FROM  foltia_subtitle";
    			$sth2 = $dbh->prepare($DBQuery);
    			$sth2->execute();
    			@st = $sth2->fetchrow_array;
    			if ( $st[0] == 0 ) { $insertpid = -1; }
    			else {
					if ( $st[1] > 0 ) { $insertpid = -1; }
					else { $insertpid = $st[1]-1; }
				}
				
				#next話数
				$DBQuery = "SELECT count(countno), max(countno) FROM  foltia_subtitle WHERE tid = $weekly[0]";
    			$sth2 = $dbh->prepare($DBQuery);
    			$sth2->execute();
    			@st = $sth2->fetchrow_array;
    			if ( $st[0] == 0 ) { $insertcountno = 1; }
    			else {
					$insertcountno = $st[1]+1;
				}
				
				
				#epgからタイトル検索
				$DBQuery = "SELECT foltia_epg.epgtitle FROM foltia_epg, foltia_station WHERE foltia_epg.ontvchannel = foltia_station.ontvcode AND foltia_epg.startdatetime = $startdt AND foltia_station.stationid = $recstaid";
    			$sth2 = $dbh->prepare($DBQuery);
    			$sth2->execute();
    			if ( @st = $sth2->fetchrow_array ) {
					$insertsubtitle = $dbh->quote($st[0]);
				}else { $insertsubtitle = "NULL"; }
				
				
				#insert
				$DBQuery = "INSERT INTO foltia_subtitle  (pid ,tid ,stationid , countno ,subtitle , startdatetime ,enddatetime ,startoffset , lengthmin ) values ( $insertpid, $weekly[0], $recstaid, $insertcountno , $insertsubtitle  , $startdt , " . &calcoffsetdate($startdt, $weekly[3])  . " , 0 ,$weekly[3] )";
    			$sth2 = $dbh->prepare($DBQuery);
    			$sth2->execute();
			}
		}
	} elsif ( $typekey eq "keyword" ) {
		
		if ( $typeval eq "" ) { next; }
		
		$sql_keyword = $dbh->quote("%$typeval%");
		
		#EPGを検索
		$DBQuery = "SELECT startdatetime, enddatetime, lengthmin, epgtitle, stationid FROM foltia_epg, foltia_station WHERE foltia_epg.ontvchannel = foltia_station.ontvcode AND startdatetime > $folnow AND (epgtitle || epgdesc) LIKE $sql_keyword AND foltia_station.stationrecch != -10 ORDER BY startdatetime ASC";
		
    	$sth2 = $dbh->prepare($DBQuery);
		$sth2->execute();
		while ( ($startdatetime, $enddatetime, $lengthmin, $epgtitle, $stationid) = $sth2->fetchrow_array() ) {
			
			#既に登録済み
			$DBQuery = "SELECT count(*) FROM foltia_subtitle WHERE tid = $weekly[0] AND startdatetime = $startdatetime AND lengthmin = $lengthmin";
		   	$sth3 = $dbh->prepare($DBQuery);
			$sth3->execute();
			@stcount = $sth3->fetchrow_array;
			if ( $stcount[0] != 0 ) { next; } #登録済み
			
			
			#重複を消しちまう
			$DBQuery = "DELETE FROM foltia_subtitle WHERE tid = $weekly[0] AND stationid = $stationid AND enddatetime > $startdatetime AND startdatetime < $enddatetime";
			$sth3 = $dbh->prepare($DBQuery);
			$sth3->execute();
			
    		
			#登録する
			
			#pid取得
			$DBQuery = "SELECT count(pid), min(pid) FROM  foltia_subtitle";
			$sth3 = $dbh->prepare($DBQuery);
			$sth3->execute();
			@st = $sth3->fetchrow_array;
			if ( $st[0] == 0 ) { $insertpid = -1; }
			else {
				if ( $st[1] > 0 ) { $insertpid = -1; }
				else { $insertpid = $st[1]-1; }
			}
			
			#next話数
			$DBQuery = "SELECT count(countno), max(countno) FROM  foltia_subtitle WHERE tid = $weekly[0]";
			$sth3 = $dbh->prepare($DBQuery);
			$sth3->execute();
			@st = $sth3->fetchrow_array;
			if ( $st[0] == 0 ) { $insertcountno = 1; }
			else {
				$insertcountno = $st[1]+1;
			}
			
			#insert
			
			$insertsubtitle = $dbh->quote($epgtitle);
			
			$DBQuery = "INSERT INTO foltia_subtitle  (pid ,tid ,stationid , countno ,subtitle , startdatetime ,enddatetime ,startoffset , lengthmin ) values ( $insertpid, $weekly[0], $stationid, $insertcountno , $insertsubtitle  , $startdatetime , $enddatetime , 0 , $lengthmin )";
			$sth3 = $dbh->prepare($DBQuery);
			$sth3->execute();
			
			
		}
	}
}

#XMLゲット&更新
system("$toolpath/perl/getxml2db.pl");


$wmi = `whoami`;
chomp($wmi);
if ( $wmi ne "apache" ) {
	die ( "Add Queue must be Run in apache. You are $wmi.\n" );
}

#予約番組探し
$DBQuery =  "SELECT count(*)  FROM foltia_tvrecord ";


	 $sth = $dbh->prepare($DBQuery);
	$sth->execute();
 @titlecount= $sth->fetchrow_array;

 if ($titlecount[0]  == 0 ){
exit;
}else{

$DBQuery =  "SELECT  tid ,stationid  FROM foltia_tvrecord ";
	 $sth = $dbh->prepare($DBQuery);
	$sth->execute();
while (($tid,$stationid  ) = $sth->fetchrow_array()) {
#キュー再投入
system ("$toolpath/perl/addatq.pl $tid $stationid  ");
&writelog("schedulecheck  $toolpath/perl/addatq.pl $tid $stationid ");

}#while


}