#!/usr/bin/perl
#################################################################
#02.11.9-2:44 PM
#################################################################
$|=1;
use DBI;

require '/home/yymail/public_html/lib/jcode.pl';
require '/home/yymail/public_html/lib/cgi-lib.pl';
require '/home/movie/public_html/lib/moviealive.pl';
$sendmail = '/usr/sbin/sendmail';
$cgifile = "rank_person.film";

&ReadParse(*in);
$mode = $in{'mode'};
$regname = $in{'regname'};
$freename = $in{'freename'};
$spellname = $in{'spellname'};
$sex = $in{'sex'};
$birthy = $in{'birthy'};
$birthm = $in{'birthm'};
$birthd = $in{'birthd'};
$country1 = $in{'country1'};
$country2 = $in{'country2'};
$info = $in{'info'};
$mid = $in{'mid'};
$point = $in{'point'};
$msg = $in{'msg'};
$acount = $in{'acount'};

$offset = $in{'offset'};
$limit = $in{'limit'};#何個ずつ見るか
unless($limit){ $limit=30; }#何個ずつ見るか
$limitn = $limit+1;#内部処理用
$offset_p = $offset - $limit;
$offset_n = $offset + $limit;
unless($offset){ $offset=0; }

$login = $in{'login'};
$loginacount = $in{'loginacount'};
$loginpass = $in{'loginpass'};

&getmember;
#################################################
	$ds = 'dbi:Pg:dbname=moviealive';
	$user = 'pgsql';
	$dbpass = '';
	$dbh = DBI->connect($ds,$user)or exitError("Cannot connect.");

	$limittime = time - 90*24*60*60;

	#映画情報
	$sth = $dbh->prepare("select person.mid,AVG(review.point) from person,review where person.mid = review.mid and review.time > $limittime group by person.mid order by AVG(review.point) DESC limit $limitn offset $offset");
	$sth->execute;
	$num = $sth->{'NUM_OF_FIELDS'};
	while($ref = $sth->fetchrow_arrayref){
		$rankdata = join(':-snichcode-:',@$ref);
		push(@rankdata,$rankdata);
	}

	$rank = 0;
	foreach(@rankdata){
		($r_mid,$rankpoint) = split(/:-snichcode-:/,$_);
		unless($rankpoint == $p_rankpoint){$rank++;}
		$p_rankpoint = $rankpoint;
		$rankgraph = int($rankpoint * 10);
		$rankpoint = $rankgraph / 10;

		$sth = $dbh->prepare("select regname from person where mid = '$r_mid'");
		$sth->execute;
		$num = $sth->{'NUM_OF_FIELDS'};
		while($ref = $sth->fetchrow_arrayref){$personname = @$ref[0];}
		$rankin = "$rank:-snichcode-:$r_mid:-snichcode-:$personname:-snichcode-:$rankpoint:-snichcode-:$rankgraph";
		push(@rankdisp,$rankin);
	}

	#映画レビューを読み込み
	$sth = $dbh->prepare("select person.mid,person.regname,review.time,review.acount,review.point,review.msg from person,review where person.mid = review.mid ORDER BY time DESC limit $limit");
	$sth->execute;
	$num = $sth->{'NUM_OF_FIELDS'};
	while($ref = $sth->fetchrow_arrayref){
		$personreviewdata = join(':-snichcode-:',@$ref);
		($r_1,$r_2,$r_3,$r_4,$r_5,$r_6) = split(/:-snichcode-:/,$personreviewdata);
		if($r_4){
			$sth2 = $dbh->prepare("select regname from members where acount = '$r_4'");
			$sth2->execute;
			$num2 = $sth2->{'NUM_OF_FIELDS'};
			while($ref2 = $sth2->fetchrow_arrayref){$writername = @$ref2[0];}
			$personreviewdata .= ":-snichcode-:$writername";
		}
		push(@personreview,$personreviewdata);
		$r_4 = "";
	}


	#DB問い合わせ終わり
	$sth->finish;
	$dbh->disconnect;

#ヘッダー設定##########################################
print "Content-type: text/html\n\n";
open(TEMP,"<temp_euc.html");
while(<TEMP>){
	if($_ =~ /<!--title-->/){
		print "<title>[MovieAlive]俳優評価ランキング</title>\n";
	}elsif($_ =~ /<!--login-->/){
		&login;
	}elsif($_ =~ /<!--menu-->/){
		&disp_menu;
	}elsif($_ =~ /<!--recently-->/){
		&recently;
	}elsif($_ =~ /<!--here-->/){
		print <<"EOF";
		<hr size="1" color="#ff9900">
		<font size="2"><b>$aisatsu</b></font>
		<hr size="1" color="#ff9900">
		<table border="0" cellpadding="1" cellspacing="3" width="100%"><tr><td valign="top">
		<font size="2">このランキングでは90日以内に書き込まれたレビューを集計してランキングしています。</font>
		</td></tr></table>
		<hr size="1" color="#ff9900">
EOF

		if ($rankdisp[$limit]){
		##次頁へジャンプ
			print <<"EOF";
			<a href="$cgifile?offset=$offset_n">次の$limit件</a><br>
EOF
		}
		##前頁へジャンプ
		if ($offset_p >= 0) {
			print <<"EOF";
			<a href="$cgifile?offset=$offset_p">前の$limit件</a><br>
EOF
		}

print <<"EOF";
				<table border="0" cellpadding="0" cellspacing="0" width="100%">
				<tr>
				<td bgcolor="#ff9900" width="49%">
					<table border="0" cellpadding="1" cellspacing="1" width="100%" height="100%">
					<tr>
					<td align="center" bgcolor="white" width="30"><font size=2>順</font></td>
					<td align="center" bgcolor="white"><font size=2>映画名</font></td>
					<td align="center" bgcolor="white" width="30"><font size=2>点</font></td>
					</tr>
EOF

	$halflimit = $limit /2;
	
	$ckon = 0;
	foreach(@rankdisp){
		unless($plusflag){$ckon++;}
		if($ckon > $halflimit){
			print <<"EOF";
					</table>
				</td>
				<td width="2%"><br></td>
				<td bgcolor="#ff9900" width="49%">
					<table border="0" cellpadding="1" cellspacing="1" width="100%" height="100%">
					<tr>
					<td align="center" bgcolor="white" width="30"><font size=2>順</font></td>
					<td align="center" bgcolor="white"><font size=2>映画名</font></td>
					<td align="center" bgcolor="white" width="30"><font size=2>点</font></td>
					</tr>
EOF
			$ckon = 0;
			$plusflag++;
		}
		($r_rank,$r_mid,$r_name,$r_point,$r_graph) = split(/:-snichcode-:/,$_);
		print <<"EOF";
					<tr>
					<td align="center" valign="middle" bgcolor="white" rowspan="2" width="30"><font size=2><b>$r_rank</b></font></td>
					<td bgcolor="white"><font size=2><a href="disp_person.film?mid=$r_mid">$r_name</a></font></td>
					<td align="center" bgcolor="white" width="30"><font size=2>$r_point</font></td>
					</tr>
					<tr>
					<td align="left" valign="middle" bgcolor="white" height="15" colspan="2"><img src="images/graph.gif" height="5" width="$r_graph%"></td>
					</tr>
EOF
	}

print <<"EOF";
					</table>
				</td>
			</tr>
		</table>
		<hr size="1" color="#ff9900">
EOF
	}else{
		print $_;
	}
}
close(TEMP);
exit(0);

#-------------------------------------------------------------------------------------------

sub error {
		print <<"EOF";
		<center>◆ｴﾗｰ通知◆</center><br>
		<hr size="1" color="#ff9900">
		▼$_[0]入力画面にお戻り下さい。
		<hr size="1" color="#ff9900">
		$emoji[0]<a href="$cgifile?mode=mode_a&regname=$regname&email=$email&ken=$ken&sex=$sex&tel=$tel&msg=$msg&pass=$pass" $ackey="0">戻る</a><br>
		<hr size="1" color="#ff9900">
EOF
	exit(0);
}