<?php

/**
  Copyright 2022 Click Olinawa. All Rights Reserved.
**/

// エラーを出力する
//ini_set('display_errors', "On");

//DBエスケープ(POSTで受け取ったデータを)
foreach($_REQUEST as $key => $val){
$arg = filter_input_array(INPUT_GET,FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_BACKTICK | FILTER_FLAG_STRIP_HIGH);
}

include("../lib/common.cgi");
require_once("../lib/htmltemplate.php");
include("./common/site.php");
include("./common/cm.php");
include("/home/politician-cafe/lib/auth.php");
include("./common/navi.php");
include("./common/footer.php");
//ここまで

//管理者用メニュー
if(isset($arg['ut']) >= "6"){
$arg['kanri'] = "1";
}

if($arg['ha'] == "1"){
$sql_ha .= "";
}

//情報抽出
$sql_s1 = "SELECT * FROM giin where g_id = '{$arg['gid']}' and g_f = '1' limit 1;";
//echo "{$sql_s1}<hr>";
$result_s1 = mysqli_query($cnn,$sql_s1);
while ($data_s1 = mysqli_fetch_assoc($result_s1)){

//GOOD数
$sql_g = "SELECT count(gb_hyouka) as goodc FROM good_bad
where 
gb_g_id = '{$data_s1['g_id']}'
and gb_hyouka = '1'
;";
//echo "{$sql_g}<hr>";
$result_g = mysqli_query($cnn,$sql_g);
while ($data_g = mysqli_fetch_assoc($result_g)){
	$data_s1['good'] = $data_g['goodc'];
}

//BAD数
$sql_b = "SELECT count(gb_hyouka) as badc FROM good_bad
where 
gb_g_id = '{$data_s1['g_id']}'
and gb_hyouka = '2'
;";
//echo "{$sql_b}<hr>";
$result_b = mysqli_query($cnn,$sql_b);
while ($data_b = mysqli_fetch_assoc($result_b)){
	$data_s1['bad'] = $data_b['badc'];
}



//公約数
$sql_s2 = "SELECT k_id FROM kouyaku where
k_g_id = '{$data_s1['g_id']}'
;";
//echo "{$sql_s2}<hr>";
$result_s2 = mysqli_query($cnn,$sql_s2);
$data_s1['kouyaku'] = mysqli_num_rows($result_s2);

//評価数
$sql_s3 = "SELECT h_id FROM hyouka where
h_g_id = '{$data_s1['g_id']}'
;";
//echo "{$sql_s3}<hr>";
$result_s3 = mysqli_query($cnn,$sql_s3);
$data_s1['hyouka'] = mysqli_num_rows($result_s3);

//活動数
$sql_s4 = "SELECT ka_id FROM katudou where
ka_g_id = '{$data_s1['g_id']}'
;";
//echo "{$sql_s4}<hr>";
$result_s4 = mysqli_query($cnn,$sql_s4);
$data_s1['katudou'] = mysqli_num_rows($result_s4);

//活動数(公約にない）
$sql_s42 = "SELECT ko_id FROM koudou where
ko_g_id = '{$data_s1['g_id']}'
;";
//echo "{$sql_s4}<hr>";
$result_s42 = mysqli_query($cnn,$sql_s42);
$data_s1['koudou'] = mysqli_num_rows($result_s42);

//選挙出馬履歴
$sql_r1 = "SELECT js_id,sy_tousen,js_se_id,js_url FROM syutuba,jissi_senkyo where sy_js_id = js_id and sy_g_id = '{$arg['gid']}' order by sy_id DESC;";
$result_r1 = mysqli_query($cnn,$sql_r1);
$rows_r1 = mysqli_num_rows($result_r1);

if($rows_r1 > 0){
$arg['rearu'] = "1";
while ($data_r1 = mysqli_fetch_assoc($result_r1)){

//当落
if($data_r1['sy_tousen'] == "1"){
	$data_r1['kekka'] = "当選";
}
if($data_r1['sy_tousen'] == "2"){
	$data_r1['kekka'] = "落選";
}
if($data_r1['sy_tousen'] == "3"){
	$data_r1['kekka'] = "比例";
}

//選挙名
$sql_r2 = "SELECT js_se_id,se_name,js_touhyou_date,a1_name,a2_name FROM jissi_senkyo,senkyo_kind,senkyo_ku,area_1,area_2 
where 
js_se_id = se_id
and js_s_id = s_id
and js_a1_id = a1_id
and js_a2_id = a2_id
and js_id = '{$data_r1['js_id']}'
;";
$result_r2 = mysqli_query($cnn,$sql_r2);
while ($data_r2 = mysqli_fetch_assoc($result_r2)){
if($data_r2['js_se_id'] == "2001"){
$senkyo_name = "{$data_r2['se_name']}"."選挙";
}
if($data_r2['js_se_id'] == "1"){
//$data_s1['koku'] = "1";
$senkyo_name = "{$data_r2['se_name']}"."選挙";
}
if($data_r2['js_se_id'] == "14"){
$senkyo_name = "{$data_r2['a1_name']}"."{$data_r2['se_name']}"."選挙";
}
if($data_r2['js_se_id'] == "19" || $data_r2['js_se_id'] == "20" ||$data_r2['js_se_id'] == "21" ||$data_r2['js_se_id'] == "22" ||$data_r2['js_se_id'] == "23" ||$data_r2['js_se_id'] == "24" ||$data_r2['js_se_id'] == "25"){
$senkyo_name = "{$data_r2['a1_name']}"."{$data_r2['a2_name']}"."{$data_r2['se_name']}"."選挙";
}

$data_r1['senkyo_date'] = "{$data_r2['js_touhyou_date']}";
$data_r1['senkyo_name'] = "{$senkyo_name}";

}

$arg['data_r1'][] = $data_r1;
}
}

$sql_syu = "SELECT js_id,js_koku_id,se_name,a1_name,a2_name,js_se_id,s_name FROM syutuba,jissi_senkyo,giin,senkyo_kind,senkyo_ku,area_1,area_2 where
sy_js_id = js_id
and sy_g_id = g_id
and js_se_id = se_id
and js_s_id = s_id
and js_a1_id = a1_id
and js_a2_id = a2_id
and js_touhyou_date >= CURRENT_DATE()
and sy_g_id = '{$arg['gid']}'
;";
//echo "{$sql_syu}<hr>";
$result_syu = mysqli_query($cnn,$sql_syu);
$rows_syu = mysqli_num_rows($result_syu);

if($rows_syu > 0){
$data_s1['syutuba'] = "1";
while ($data_syu = mysqli_fetch_assoc($result_syu)){

//echo "{$data_syu['js_se_id']}<hr>";

if($data_syu['js_se_id'] == "2001"){
$senkyo_name = "{$data_syu['se_name']}"."選挙出馬";
$data_s1['koku'] = "2";
}
if($data_syu['js_se_id'] == "1"){
$data_s1['koku'] = "1";
$senkyo_name = "{$data_syu['se_name']}"."選挙"."・{$data_syu['s_name']}"."出馬";
}
if($data_syu['js_se_id'] == "14"){
$senkyo_name = "{$data_syu['a1_name']}"."{$data_syu['se_name']}"."選挙出馬";
$data_s1['koku'] = "2";
}
if($data_syu['js_se_id'] == "19" || $data_syu['js_se_id'] == "20" ||$data_syu['js_se_id'] == "21" ||$data_syu['js_se_id'] == "22" ||$data_syu['js_se_id'] == "23" ||$data_syu['js_se_id'] == "24" ||$data_syu['js_se_id'] == "25"){
$senkyo_name = "{$data_syu['a1_name']}"."{$data_syu['a2_name']}"."{$data_syu['se_name']}"."選挙出馬";
$data_s1['koku'] = "2";
}

//echo "{$senkyo_name}";
$data_s1['js_id'] = $data_syu['js_id'];
$data_s1['senkyo_name'] = $senkyo_name;

//$arg['data_syu'][] = $data_syu;
}
}










//画像の有無
$filename_1 = "/home/politician-cafe/www/gimg/{$data_s1['g_sess']}_1.jpg";
//echo "{$filename_1}<hr>";
if (file_exists($filename_1)) {
$data_s1['img_jpg_1'] = "1";
}else{
$data_s1['img_jpg_1'] = "0";
}
//画像の有無END

//政党
if($data_s1['g_t_id'] != "0"){
$sql_s1t = "SELECT * FROM tou where t_id = '{$data_s1['g_t_id']}';";
//echo "{$sql_s1t}<hr>";
$result_s1t = mysqli_query($cnn,$sql_s1t);

while ($data_s1t = mysqli_fetch_assoc($result_s1t)){
$data_s1['t_name'] = $data_s1t['t_name'];
}
}
//選挙区文
if($data_s1['g_se_id'] != "0"){
$sql_s1s = "SELECT * FROM senkyo_kind where se_id = '{$data_s1['g_se_id']}';";
//echo "{$sql_s1s}<hr>";
$result_s1s = mysqli_query($cnn,$sql_s1s);

while ($data_s1s = mysqli_fetch_assoc($result_s1s)){
$data_s1['se_name'] = $data_s1s['se_name'];

//何県の知事？
if(($data_s1['g_se_id'] == "14" || $data_s1['g_se_id'] == "15" || $data_s1['g_se_id'] == "16" || $data_s1['g_se_id'] == "17" || $data_s1['g_se_id'] == "1002" || $data_s1['g_se_id'] == "1003" || $data_s1['g_se_id'] == "1004" || $data_s1['g_se_id'] == "1005") && $data_s1['g_a1_id'] != "0"){
$sql_s1s2 = "SELECT * FROM area_1 where a1_id = '{$data_s1['g_a1_id']}';";
//echo "{$sql_s1s2}";
$result_s1s2 = mysqli_query($cnn,$sql_s1s2);
while ($data_s1s2 = mysqli_fetch_assoc($result_s1s2)){
$data_s1['f_name'] = $data_s1s2['a1_name'];
}
}

//市町村？
if(($data_s1['g_se_id'] == "19" || $data_s1['g_se_id'] == "20" || $data_s1['g_se_id'] == "21" || $data_s1['g_se_id'] == "22" || $data_s1['g_se_id'] == "23" || $data_s1['g_se_id'] == "24" || $data_s1['g_se_id'] == "25"|| $data_s1['g_se_id'] == "1006" || $data_s1['g_se_id'] == "1007" || $data_s1['g_se_id'] == "1008" || $data_s1['g_se_id'] == "1009" || $data_s1['g_se_id'] == "1010" || $data_s1['g_se_id'] == "1011") && $data_s1['g_a2_id'] != "0"){
$sql_s1s3 = "SELECT * FROM area_2 where a2_id = '{$data_s1['g_a2_id']}';";
//echo "{$sql_s1s3}";
$result_s1s3 = mysqli_query($cnn,$sql_s1s3);
while ($data_s1s3 = mysqli_fetch_assoc($result_s1s3)){
$data_s1['f_name'] = $data_s1s3['a2_name'];
}
}


}
}

$namae = "{$data_s1['g_name_1']}"."{$data_s1['g_name_2']}";
//ページ名
$arg['site_master']['title'] = "「{$namae}」の公約・政策一覧｜政治家・議員情報サイト「先生の通信簿」";
//誕生日から年齢作成
// 現在日付
$now = date('Ymd');
$now = (int)$now;
$birthday = date('Ymd',  strtotime($data_s1['g_bd']));
$birthday =(int)$birthday;

// 年齢
//echo "NOW:{$now}---BD:{$birthday}\n";
$data_s1['age'] = floor(($now - $birthday) / 10000);

//生年月日変換
$data_s1['g_bd'] = date('Y年m月d日',  strtotime($data_s1['g_bd']));

//性別
if($data_s1['g_sex'] == "1"){
	$data_s1['sex'] = "男性";
}else{
	$data_s1['sex'] = "女性";
}





$arg['data_s1'][] = $data_s1;
}

//公約がないとエラーなるから・・・
$sql_s5 = "SELECT k_id FROM kouyaku where k_g_id = '{$arg['gid']}';";
//echo "{$sql_s5}<hr>";
$result_s5 = mysqli_query($cnn,$sql_s5);
$rows_s5 = mysqli_num_rows($result_s5);

if($rows_s5 > 0){
$kouyaku_aru = "1";
}




if(isset($kouyaku_aru)){
$arg['kouyaku_aru'] = "1";
//公約絞り込み用クエリー
if($arg['kr']){
$sql_kr = "and k_kr_id = '{$arg['kr']}'";
}
//公約絞り込み用クエリーEND



//公約

//評価ありのみ
if (!empty($arg['ha']) && $arg['ha'] == "1") {
$sql_s2 = "SELECT k_id,kr_name,k_title,k_title,k_text,k_js_id FROM kouyaku,kouyaku_rui,giin,hyouka 
where k_kr_id = kr_id 
and k_g_id = g_id
and k_id = h_k_id
and k_g_id = '{$arg['gid']}'
{$sql_kr}
group by k_id
order by k_date DESC
;";
}else{
//すべて
$sql_s2 = "SELECT k_id,kr_name,k_title,k_title,k_text,k_js_id FROM kouyaku,kouyaku_rui,giin 
where k_kr_id = kr_id and k_g_id = g_id and
k_g_id = '{$arg['gid']}'
{$sql_kr}
order by k_date DESC
;";
}


//echo "{$sql_s2}<hr>";
$result_s2 = mysqli_query($cnn,$sql_s2);
$row_s2 = mysqli_num_rows($result_s2);

if($row_s2 > 0){
//評価あるの？
$sql_s2hh = "SELECT h_id FROM hyouka 
where h_g_id = '{$arg['gid']}';";
$result_s2hh = mysqli_query($cnn,$sql_s2hh);
$row_s2hh = mysqli_num_rows($result_s2hh);

if($row_s2hh > 0){
	$arg['hyou_aru'] = 1;
}
}

while ($data_s2 = mysqli_fetch_assoc($result_s2)){

//どの選挙の公約
$sql_s42 = "SELECT * FROM jissi_senkyo,senkyo_kind,senkyo_ku,area_1,area_2
where
js_se_id = se_id
and js_s_id = s_id
and js_a1_id = a1_id
and js_a2_id = a2_id
and js_id = '{$data_s2['k_js_id']}'
;";
//echo "{$sql_s42}";
$result_s42 = mysqli_query($cnn,$sql_s42);
$rows_s42 = mysqli_num_rows($result_s42);

if($rows_s42 > 0){
$data_s2['senkyomei'] = "1";
while ($data_s42 = mysqli_fetch_assoc($result_s42)){

if($data_s42['js_se_id'] == "2001"){
$data_s2['senkyo_name'] = "{$data_s42['se_name']}"."選挙";
}
if($data_s42['js_se_id'] == "14"){
$data_s2['senkyo_name'] = "{$data_s42['a1_name']}{$data_s42['se_name']}"."選挙";
}
if($data_s42['js_se_id'] == "15"){
$sql_s42t = "SELECT * FROM jissi_senkyo,senkyo_kind,senkyo_ku_togi,area_1
where
js_se_id = se_id
and js_s_id = st_id
and js_a1_id = a1_id
and js_id = '{$data_s2['k_js_id']}'
;";
//echo "{$sql_s42t}";
$result_s42t = mysqli_query($cnn,$sql_s42t);
$rows_s42t = mysqli_num_rows($result_s42t);
while ($data_s42t = mysqli_fetch_assoc($result_s42t)){
$data_s2['senkyo_name'] = "{$data_s42t['a1_name']}{$data_s42t['se_name']}"."選挙";
}
}
if($data_s42['js_se_id'] == "1"){
$data_s2['senkyo_name'] = "{$data_s42['se_name']}"."選挙";
}
if($data_s42['js_se_id'] == "19" || $data_s42['js_se_id'] == "20" ||$data_s42['js_se_id'] == "21" ||$data_s42['js_se_id'] == "22" ||$data_s42['js_se_id'] == "23" ||$data_s42['js_se_id'] == "24" || $data_s42['js_se_id'] == "25"){
$data_s2['senkyo_name'] = "{$data_s42['a1_name']}"."{$data_s42['a2_name']}"."{$data_s42['se_name']}"."選挙";
}

}
}


//参院選用
$sql_s42s = "SELECT * FROM jissi_senkyo,senkyo_kind,senkyo_ku_san
where
js_se_id = se_id
and js_s_id = ss_id
and js_se_id = '2'
and js_id = '{$data_s2['k_js_id']}'
;";
//echo "{$sql_s42s}<hr>";
$result_s42s = mysqli_query($cnn,$sql_s42s);
$rows_s42s = mysqli_num_rows($result_s42s);

if($rows_s42s > 0){
$data_s2['senkyomei'] = "1";
while ($data_s42s = mysqli_fetch_assoc($result_s42s)){

$data_s2['senkyo_name'] = "{$data_s42s['se_name']}"."選挙";

}
}




//どの選挙の公約END

//GOOD数
$sql_g = "SELECT count(gb_hyouka) as goodc FROM good_bad
where 
gb_k_id = '{$data_s2['k_id']}'
and gb_g_id = '{$arg['gid']}'
and gb_hyouka = '1'
;";
//echo "{$sql_g}<hr>";
$result_g = mysqli_query($cnn,$sql_g);
while ($data_g = mysqli_fetch_assoc($result_g)){
	$data_s2['good'] = $data_g['goodc'];
}

//BAD数
$sql_b = "SELECT count(gb_hyouka) as badc FROM good_bad
where 
gb_k_id = '{$data_s2['k_id']}'
and gb_g_id = '{$arg['gid']}'
and gb_hyouka = '2'
;";
//echo "{$sql_b}<hr>";
$result_b = mysqli_query($cnn,$sql_b);
while ($data_b = mysqli_fetch_assoc($result_b)){
	$data_s2['bad'] = $data_b['badc'];
}


//この公約の平均評価
$sql_s3 = "SELECT AVG(h_ritsu) as h_avg,count(h_id) as hcnt FROM hyouka
where 
h_k_id = '{$data_s2['k_id']}'
and h_g_id = '{$arg['gid']}'
;";
//echo "{$sql_s3}<hr>";
$result_s3 = mysqli_query($cnn,$sql_s3);
while ($data_s3 = mysqli_fetch_assoc($result_s3)){
	$data_s2['t_hyouka'] = round($data_s3['h_avg']);
	$data_s2['hcnt'] = round($data_s3['hcnt']);
}

//この公約の平均評価END

//活動報告数
$sql_s6 = "SELECT ka_id FROM katudou where ka_g_id = '{$arg['gid']}' and ka_k_id = '{$data_s2['k_id']}';";
//echo "{$sql_s6}<hr>";
$result_s6 = mysqli_query($cnn,$sql_s6);
$rows_s6 = mysqli_num_rows($result_s6);

$data_s2['katudousu'] = $rows_s6;
//活動報告数END

$arg['data_s2'][] = $data_s2;
}



//ここから機能

//公約カテゴリーリスト
//公約がない場合にエラーになるから対策

$sql_s4 = "SELECT kr_id,kr_name FROM kouyaku_rui,kouyaku
where kr_id = k_kr_id
and k_g_id = '{$arg['gid']}'
group by kr_id,kr_name
order by kr_id
;";
//echo "{$sql_s4}<hr>";
$result_s4 = mysqli_query($cnn,$sql_s4);
$rows_s4 = mysqli_num_rows($result_s4);

while ($data_s4 = mysqli_fetch_assoc($result_s4)){
$arg['data_s4'][] = $data_s4;
}


//公約カテゴリーリストここまで
}

//JSON用公約リスト
$sql_s2j = "SELECT k_id,k_title FROM kouyaku where
k_g_id = '{$arg['gid']}' order by k_date DESC limit 10
;";
//echo "{$sql_s2j}<hr>";

$result_s2j = mysqli_query($cnn,$sql_s2j);
// 総件数を取得
$total_rows = mysqli_num_rows($result_s2j);

$row_s2j = 1;
while ($data_s2j = mysqli_fetch_assoc($result_s2j)){
$data_s2j['cnt'] = $row_s2j;

    // 最後のデータなら endd=1 を追加
    if ($row_s2j === $total_rows) {
        $data_s2j['endd'] = 1;
    }

$arg['data_s2j'][] = $data_s2j;
$row_s2j++;
}



include("/home/politician-cafe/lib/giin_pv.php");
htmltemplate::t_include("./tmpl/political_measures.html",$arg);