<?php
// セッションクッキーに httponly 属性を設定
ini_set('session.cookie_httponly', 1);
// セッションクッキーに secure 属性を設定
ini_set('session.cookie_secure', 1);

session_name("tanzo_master");
session_start();


require_once '../include/include.php';
$title = "ユーザー詳細";
$date = new DateTime();
$date = $date->format("YmdHis");

$posted_user_id = filter_input(INPUT_POST,"user_id");

$search_shop = filter_input(INPUT_POST,"search_shop");
$search_status = filter_input(INPUT_POST,"search_status");
$search_name = filter_input(INPUT_POST,"search_name");
$search_mail = filter_input(INPUT_POST,"search_mail");
$search_pair = filter_input(INPUT_POST,"search_pair");
$search_pair_num = filter_input(INPUT_POST,"search_pair_num");
$search_date1 = filter_input(INPUT_POST,"search_date1");
$search_date_checkbox = filter_input(INPUT_POST,"search_date_checkbox");
$search_date2 = filter_input(INPUT_POST,"search_date2");
if($search_date_checkbox !== "Y"){
	$search_date2 = "";
}

if($search_shop === "すべて"){
	$search_shop = "";
}

$posted_log = "posted_user_id -> {$posted_user_id} | search_shop -> {$search_shop} | search_status -> {$search_status} | search_name -> {$search_name} | search_mail -> {$search_mail} | search_pair -> {$search_pair} | search_pair_num -> {$search_pair_num} | search_date1 -> {$search_date1} | search_date_checkbox -> {$search_date_checkbox} | search_date2 -> {$search_date2}";
Writelog($posted_log,"check");

try {
	$DLName = "User_Data_List_{$date}.csv"; //ファイル名

	header("Content-Type: application/octet-stream");
	header("Content-Disposition: attachment; filename=$DLName");
	$fp = fopen('php://output','w');
	fwrite($fp, "\xEF\xBB\xBF");

	$pdo = DBopen();

	$select_sql = "SELECT * FROM user WHERE 1";

	$WHERE = "";
	$VALUES = [];

	if(!empty($posted_user_id)){
		// 詳細でダウンロード -> 個人
		$WHERE .= " AND user_id = ?";
		$VALUES[] = $posted_user_id;
	} else {
		// リストでダウンロード -> 検索結果全体
		if(!empty($search_shop)){
			$WHERE .= " AND shop = ?";
			$VALUES[] = $search_shop;
		}

		if($search_status === "確定済"){
			$WHERE .= " AND fixed_date != ?";
			$VALUES[] = default_date;
		} else if($search_status === "調整中"){
			$WHERE .= " AND fixed_date = ?";
			$VALUES[] = default_date;
		}

		if(!empty($search_name)){
			$WHERE .= " AND CONCAT(name1,name2,kana_name1,kana_name2) LIKE ?";
			$VALUES[] = "%{$search_name}%";
		}

		if(!empty($search_mail)){
			$WHERE .= " AND mail LIKE ?";
			$VALUES[] = "%{$search_mail}%";
		}

		if($search_pair === "1"){
			// ペアあり
			$WHERE .= " AND pair != ''";

			if(!empty($search_pair_num)){
				/*
				$WHERE .= " AND pair LIKE ?";
				$VALUES[] = "%{$search_pair_num}%";
				*/
				// 完全一致であるべきか？
				$WHERE .= " AND pair = ?";
				$VALUES[] = $search_pair_num;
			}
		} else if($search_pair === "2"){
			// ペアなし
			$WHERE .= " AND pair = ''";
		} else {
			if(!empty($search_pair_num)){
				/*
				$WHERE .= " AND pair LIKE ?";
				$VALUES[] = "%{$search_pair_num}%";
				*/
				// 完全一致であるべきか？
				$WHERE .= " AND pair = ?";
				$VALUES[] = $search_pair_num;
			}
		}
	}

	if($search_date_checkbox === "Y"){
		// 期間での検索
		// スタートの 00:00:00 から エンドの 23:59:59
		// スタートが指定されていなければ default_date
		// エンドが指定されていなければ 現在の日時
		$WHERE .= " AND (created >= ? AND created <= ?)";
		if(!empty($search_date1)){
			$value_date1 = new DateTime($search_date1);
		} else {
			$value_date1 = new DateTime(default_date);
		}
		$value_date1 = $value_date1->format("Y-m-d 00:00:00");

		if(!empty($search_date2)){
			$value_date2 = new DateTime($search_date2);
		} else {
			$value_date2 = new DateTime();
		}
		$value_date2 = $value_date2->format("Y-m-d 23:59:59");

		$VALUES[] = $value_date1;
		$VALUES[] = $value_date2;
	} else {
		if(!empty($search_date1)){
			// 日付の検索
			// その日の 00:00:00 から 23:59:59
			$WHERE .= " AND (created >= ? AND created <= ?)";
			$value_date = new DateTime($search_date1);
			$value_date1 = $value_date->format("Y-m-d 00:00:00");
			$value_date2 = $value_date->format("Y-m-d 23:59:59");
			$VALUES[] = $value_date1;
			$VALUES[] = $value_date2;
		}
	}

	$select_sql .= $WHERE;

	$select_sql .= " ORDER BY user_id DESC";

	$select = $pdo->prepare($select_sql);

	$i = 0;
	foreach($VALUES as $value){
		$i++;
		$select->bindValue($i,$value,PDO::PARAM_STR);
	}
	$select->execute();

	$select_report_sql = "SELECT * FROM report WHERE user_id = ? AND deleted != 1 ORDER BY report_id DESC";
	$select_report = $pdo->prepare($select_report_sql);

	$count_report1_sql = "SELECT COUNT(*) FROM report WHERE user_id = ? AND report1 = ? AND deleted != 1";
	$count_report1 = $pdo->prepare($count_report1_sql);

	$count_report2_sql = "SELECT COUNT(*) FROM report WHERE user_id = ? AND report2 = ? AND deleted != 1";
	$count_report2 = $pdo->prepare($count_report2_sql);

	//csvに出力するリスト
	$CSV_HEADER = [
		"メールアドレス",
		"名前",
		"かな",
		"性別",
		"ペア番号",
		"店舗",
		"指輪の形状",
		"指輪の素材",
		"指輪のサイズ",
		"指輪の幅",
		"指輪の厚み",
		"備考欄",
		"サイズ確定日",
		"確定した指輪の形状",
		"確定した指輪の素材",
		"確定した指輪のサイズ",
		"確定した指輪の幅",
		"確定した指輪の厚み",
		"利き手",
		"薬指の節",
		"外す頻度",
		"飲酒",
		"フィット感の好み",
		"作成日",
		"更新日",
		"",
		"",
		"着け外し感（回数）",
		"",
		"",
		"",
		"",
		"装着感（回数）",
		"",
		"",
		"着け外しフィット率",
		"装着フィット率",
		"総合フィット率",
		"補正後フィット率",
		"むくみ率",
		"グループ分類",
	];
	fputcsv($fp, $CSV_HEADER, ',','"');
	$CSV_HEADER2 = [
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"入らない",
		"かなりきつい",
		"ちょうどよい",
		"簡単に抜ける",
		"ゆるすぎる",
		"入らない",
		"かなりきつい",
		"ちょうどよい",
		"簡単に抜ける",
		"ゆるすぎる",
	];
	fputcsv($fp, $CSV_HEADER2, ',','"');

	while($row = $select->fetch()){
		$data = [];

		$user_id = $row["user_id"];

		$mail = $row["mail"];
		$name1 = $row["name1"];
		$name2 = $row["name2"];
		$kana_name1 = $row["kana_name1"];
		$kana_name2 = $row["kana_name2"];
		$gender = $row["gender"];
		$pair = $row["pair"];
		$shop = $row["shop"];
		$ring_type = $row["ring_type"];
		$ring_material = $row["ring_material"];
		$ring_size = $row["ring_size"];
		$ring_width = $row["ring_width"];
		$ring_thickness = $row["ring_thickness"];
		$remarks = $row["remarks"];
		$fixed_date = $row["fixed_date"];
		$fixed_ring_type = $row["fixed_ring_type"];
		$fixed_ring_material = $row["fixed_ring_material"];
		$fixed_ring_size = $row["fixed_ring_size"];
		$fixed_ring_width = $row["fixed_ring_width"];
		$fixed_ring_thickness = $row["fixed_ring_thickness"];
		$report1_fit = $row["report1_fit"];
		$report2_fit = $row["report2_fit"];
		$total_fit = $row["total_fit"];
		$user_type1_value = $row["user_type1_value"];
		$user_type2_value = $row["user_type2_value"];
		$user_type3 = $row["user_type3"];
		$handedness = $row["handedness"];
		$joint = $row["joint"];
		$working = $row["working"];
		$frequency = $row["frequency"];
		$alcohol = $row["alcohol"];
		$taste = $row["taste"];
		$created = $row["created"];
		$updated = $row["updated"];

		$name = $name1.$name2;
		$kana_name = $kana_name1.$kana_name2;
		$gender = $gender = $gender == "1" ? "男性" : "女性";
		$ring_type = ring_type_list[$ring_type - 1];
		$ring_material = isset(material_list[$ring_material]) ? material_list[$ring_material] : "";
		if($fixed_date !== default_date){
			// 確定済み
			if(!empty($fixed_ring_type)){
				// 空ではない
				$fixed_ring_type = ring_type_list[$fixed_ring_type - 1];
			} else {
				$fixed_ring_type = $ring_type;
			}
			$fixed_ring_material = isset(material_list[$fixed_ring_material]) ? material_list[$fixed_ring_material] : "";
		}

		if(!empty($handedness)){
			$handedness = hand_list[$handedness - 1];
		}
		if(!empty($joint)){
			$joint = joint_list[$joint - 1];
		}
		if(!empty($working)){
			$working = working_list[$working - 1];
		}
		if(!empty($frequency)){
			$frequency = frequency_list[$frequency - 1];
		}
		if(!empty($alcohol)){
			$alcohol = alcohol_list[$alcohol - 1];
		}
		if(!empty($taste)){
			$taste = taste_list[$taste - 1];
		}

		$report1_count_data = [];
		$report2_count_data = [];
		for($rl = 1;$rl <= 5; $rl++){
			$count_report1->bindValue(1,$user_id,PDO::PARAM_STR);
			$count_report1->bindValue(2,$rl,PDO::PARAM_STR);
			$count_report1->execute();
			$count_report1_result = $count_report1->fetchColumn();
			$report1_count_data[$rl] = $count_report1_result;

			$count_report2->bindValue(1,$user_id,PDO::PARAM_STR);
			$count_report2->bindValue(2,$rl,PDO::PARAM_STR);
			$count_report2->execute();
			$count_report2_result = $count_report2->fetchColumn();
			$report2_count_data[$rl] = $count_report2_result;
		}

		$data = [
			$mail,
			$name,
			$kana_name,
			$gender,
			$pair,
			$shop,
			$ring_type,
			$ring_material,
			$ring_size,
			$ring_width,
			$ring_thickness,
			$remarks,
			$fixed_date,
			$fixed_ring_type,
			$fixed_ring_material,
			$fixed_ring_size,
			$fixed_ring_width,
			$fixed_ring_thickness,
			$handedness,
			$joint,
			$frequency,
			$alcohol,
			$taste,
			$created,
			$updated,
			$report1_count_data[1],
			$report1_count_data[2],
			$report1_count_data[3],
			$report1_count_data[4],
			$report1_count_data[5],
			$report2_count_data[1],
			$report2_count_data[2],
			$report2_count_data[3],
			$report2_count_data[4],
			$report2_count_data[5],
			$report1_fit,
			$report2_fit,
			$total_fit,
			$user_type1_value,
			$user_type2_value,
			$user_type3
		];

		$select_report->bindValue(1,$user_id,PDO::PARAM_STR);
		$select_report->execute();

		/*
		個々の記録は必要ない
		while($report_row = $select_report->fetch()){
			$report1 = $report_row["report1"]; // 着け外し
			$report2 = $report_row["report2"]; // 装着感
			$memo = $report_row["memo"]; // メモ
			$created = $report_row["created"]; // 記録日時

			$key = $report1 - 1;
			$report1_text = report_text_list[$key];

			$key = $report2 - 1;
			$report2_text = report2_text_list[$key];

			$data[] = $created;
			$data[] = $report1_text;
			$data[] = $report2_text;
			$data[] = $memo;
		}
		*/

		fputcsv($fp, $data, ',','"');
	}

	fclose($fp);

} catch(Exception $e){
	$error = $e->getMessage();
	$log = "error -> {$error} | {$posted_log}";
	Writelog($log,basename(__FILE__));
	require_once './error.php';
	exit;
}
