<?php
	require("__include-common.php");
	require("../account/__logincheck.php");
	html_header(HTML_TITLE);
	if (kakeibo_PAGE_HEADER == 'YES') {
		page_header();
	} else {
		page_header_return_index();
	}
	contents_header();
	cross_proc();
	page_footer();
	html_footer();
	exit();
function cross_proc() {
	$con = my_mysqli_connect(_DB_SCHEMA);
	$sql = "select distinct c_keiyakuSha";
	$sql .= " from m_chokin where c_delete = 0";
	$sql .= " and id_account = '" . $_SESSION['current_id'] . "'";
	$sql .= " order by c_keiyakuSha";
	$rs_keiyakusha = my_mysqli_query($sql);
	if (mysqli_num_rows($rs_keiyakusha) == 0) {
		error_msg ("＊＊＊ 該当データがありません。");
	}
	$keiyakusha = array();
	$keiyakuKinTotal = array();
	while ($rec_keiyakusha = mysqli_fetch_array($rs_keiyakusha)) {
		$keiyakusha[] = $rec_keiyakusha['c_keiyakuSha'];
		$keiyakuKinTotal[$rec_keiyakusha['c_keiyakuSha']] = 0;
		$todayKinTotal[$rec_keiyakusha['c_keiyakuSha']] = 0;
		$mankiKinTotal[$rec_keiyakusha['c_keiyakuSha']] = 0;
	}

	$sql = "select v_chokin.* ";
	$sql .= ", (YEAR(current_date)-YEAR(c_keiyakuYMD))*12 + MONTH(current_date)-MONTH(c_keiyakuYMD) - (DAY(current_date)+1<DAY(c_keiyakuYMD)) as todayKeikaMonth";
	$sql .= ", (YEAR(c_mankiYMD)-YEAR(c_keiyakuYMD))*12 + MONTH(c_mankiYMD)-MONTH(c_keiyakuYMD) - (DAY(c_mankiYMD)+1<DAY(c_keiyakuYMD)) as mankiKeikaMonth";
	$sql .= " from v_chokin";
	$sql .= " where c_delete = 0";
	$sql .= " and id_mankitype <> 80";
	$sql .= " and id_account = '" . $_SESSION['current_id'] . "'";

	$sql2 = "select c_keiyakuSha, id_ginkou, c_ginkouName";
	$sql2 .= ", case when id_shurui = 50 and id_mankitype <> 20 and c_mankiYMD < current_date then ("; // 月積 解約 満期
	$sql2 .= " c_keiyakuKin * mankiKeikaMonth )";
	$sql2 .= " when id_shurui = 50 then (";								// 月積
	$sql2 .= " c_keiyakuKin * todayKeikaMonth )";
	$sql2 .= " else ( c_keiyakuKin )";
	$sql2 .= " end as keiyakuKin";

	$sql2 .= ", case when id_shurui = 50 and id_mankitype <> 20 and c_mankiYMD < current_date then ("; // 月積 解約 満期
	$sql2 .= " truncate(c_keiyakuKin * pow(1 + c_ritsu/100, (mankiKeikaMonth)/23.7) * mankiKeikaMonth, 1) )";
	$sql2 .= " when id_shurui = 50 then (";								 // 月積
	$sql2 .= " truncate(c_keiyakuKin * pow(1 + c_ritsu/100, (todayKeikaMonth)/23.7) * todayKeikaMonth, 1) )";
	$sql2 .= " when id_mankitype <> 20 and c_mankiYMD < current_date then (";			 // 定期 解約 満期
	$sql2 .= " truncate(c_keiyakuKin * pow(1 + c_ritsu/100, (mankiKeikaMonth)/12), 1) )";
	$sql2 .= " else ( truncate(c_keiyakuKin * pow(1 + c_ritsu/100, (todayKeikaMonth)/12), 1) )";	 // 定期
	$sql2 .= " end as todayKin";
	$sql2 .= ", case when id_shurui = 50 then (";	// 月
	$sql2 .= " truncate(c_keiyakuKin * pow(1 + c_ritsu/100, (mankiKeikaMonth)/23.7) * mankiKeikaMonth, 1) )";
	$sql2 .= " else ( truncate(c_keiyakuKin * pow(1 + c_ritsu/100, (mankiKeikaMonth)/12), 1) )";
	$sql2 .= " end as mankiKin";
	$sql2 .= ' from (' . $sql . ') A';

	$sql3 = "select id_ginkou, c_ginkouName";
	foreach ($keiyakusha as $name) {
		$sql3 .= ", sum(case when (c_keiyakuSha = '".$name."') then keiyakuKin else 0 end) 'K".$name."'";
		$sql3 .= ", sum(case when (c_keiyakuSha = '".$name."') then todayKin else 0 end) 'T".$name."'";
		$sql3 .= ", sum(case when (c_keiyakuSha = '".$name."') then mankiKin else 0 end) 'M".$name."'";
	}
	$sql3 .= ", sum(keiyakuKin) as K合計";
	$sql3 .= ", sum(todayKin) as T合計";
	$sql3 .= ", sum(mankiKin) as M合計";
	$sql3 .= ' from (' . $sql2 . ') B';
	$sql3 .= " group by c_ginkouName";
	$rs = my_mysqli_query_debug_print($sql3);

	$keiyakusha[] = '合計';
	$keiyakuKinTotal['合計'] = 0;
	$todayKinTotal['合計'] = 0;
	$mankiKinTotal['合計'] = 0;
	if (mysqli_num_rows($rs) <> 0) {
		$colspan = count($keiyakusha);
?>
	<div id="cross_body">
	<table id="cross_table">
		<tr class="cross_header">
		<th rowspan=2>金融機関</th>
		<th colspan="<?= $colspan ?>" style="text-align: center;">元金</th>
		<th colspan="<?= $colspan ?>" style="text-align: center;">現在額</th>
		<th colspan="<?= $colspan ?>" style="text-align: center;">満期額</th>
		</tr>
		<tr class="cross_header">
<?php
	for ($ix=0; $ix<3; $ix++) {
		foreach ($keiyakusha as $name) {
?>
		<?php if ($name <> '合計') { ?>
			<th class="cross_sub_title">
			<a href="list.php?meigi=<?= urlencode($name) ?>&key=__reset__&subt=off&kaiyaku=off&sort=cat"><?= $name ?></a>
			</th>
		<?php } else { ?>
			<th class="cross_sub_title_goukei">
			<?= $name ?>
			</th>
		<?php } ?>
<?php
		}
	}
?>
		</tr>
<?php
	while ($rec = mysqli_fetch_array($rs)) {
?>
		<tr class="cross_data">
		<td class="cross_ginkou_name"><a href="list.php?cat=<?= $rec['id_ginkou'] ?>&key=__reset__&subt=off&kaiyaku=off&sort=meigi"><?= $rec['c_ginkouName'] ?></a></td>
<?php
	foreach ($keiyakusha as $name) {
		$keiyakuKinTotal[$name] += $rec['K'.$name];
?>
		<?php if ($name <> '合計') { ?>
			<td class="cross_row_data">
			<a href="list.php?cat=<?= $rec['id_ginkou'] ?>&meigi=<?= urlencode($name) ?>&key=__reset__&subt=off&kaiyaku=off&sort=kouza"><?= number_format($rec['K'.$name]) ?></a>
			</td>
		<?php } else { ?>
			<td class="cross_row_total">
			<?= number_format($rec['K'.$name]) ?>
			</td>
		<?php } ?>
<?php
	}
?>
<?php
	foreach ($keiyakusha as $name) {
		$todayKinTotal[$name] += $rec['T'.$name];
?>
		<?php if ($name <> '合計') { ?>
			<td class="cross_row_data">
			<a href="list.php?cat=<?= $rec['id_ginkou'] ?>&meigi=<?= urlencode($name) ?>&key=__reset__&subt=off&kaiyaku=off&sort=kouza"><?= number_format($rec['T'.$name]) ?></a>
			</td>
		<?php } else { ?>
			<td class="cross_row_total">
			<?= number_format($rec['T'.$name]) ?>
			</td>
		<?php } ?>
<?php
	}
?>
<?php
	foreach ($keiyakusha as $name) {
		$mankiKinTotal[$name] += $rec['M'.$name];
?>
		<?php if ($name <> '合計') { ?>
			<td class="cross_row_data">
			<a href="list.php?cat=<?= $rec['id_ginkou'] ?>&meigi=<?= urlencode($name) ?>&key=__reset__&subt=off&kaiyaku=off&sort=kouza"><?= number_format($rec['M'.$name]) ?></a>
			</td>
		<?php } else { ?>
			<td class="cross_row_total">
			<?= number_format($rec['M'.$name]) ?>
			</td>
		<?php } ?>
<?php
	}
?>
		</tr>
<?php
	}
?>
		<tr class="cross_total">
		<td class="cross_total_goukei">合計</td>
<?php
	foreach ($keiyakusha as $name) {
?>
		<?php if ($name <> '合計') { ?>
			<td class="cross_total_sub" style="text-align: right;"><?= number_format($keiyakuKinTotal[$name]) ?></td>
		<?php } else { ?>
			<td class="cross_total_all" style="text-align: right;"><?= number_format($keiyakuKinTotal[$name]) ?></td>
		<?php } ?>
<?php
	}
?>
<?php
	foreach ($keiyakusha as $name) {
?>
		<?php if ($name <> '合計') { ?>
			<td class="cross_total_sub" style="text-align: right;"><?= number_format($todayKinTotal[$name]) ?></td>
		<?php } else { ?>
			<td class="cross_total_all" style="text-align: right;"><?= number_format($todayKinTotal[$name]) ?></td>
		<?php } ?>
<?php
	}
?>
<?php
	foreach ($keiyakusha as $name) {
?>
		<?php if ($name <> '合計') { ?>
			<td class="cross_total_sub" style="text-align: right;"><?= number_format($mankiKinTotal[$name]) ?></td>
		<?php } else { ?>
			<td class="cross_total_all" style="text-align: right;"><?= number_format($mankiKinTotal[$name]) ?></td>
		<?php } ?>
<?php
	}
?>
		</tr>
	</table>
	</div>
<?php
	}
}
?>
