PHP+MySQLで家計簿システム開発#33【メニュー切替と年間レポートページ実装】

<!--書き換え前-->
<header class="l-header">
  <h1 class="l-header__title"><a href="./index.php">家計簿アプリ</a></h1>
  <div class="l-header__icon">
    <a href="./index.php">
      <i class="fa-solid fa-house"></i>
    </a>
    <a href="./account.php">
      <i class="fa-solid fa-user"></i>
    </a>
    <a href="./logout.php" id="logoutButton" onclick="logoutConfirm();">
      <i class="fa-solid fa-arrow-right-from-bracket"></i>
    </a>
  </div>
</header>

  <header class="l-header">
    <h1 class="l-header__title"><a href="./index.php">家計簿アプリ</a></h1>
    <div class="l-header__menu">
      <div class="p-hamburger-button" onclick="onToggleNavigation();" id="hamburgerButton">
        <i class="fa-solid fa-list-ul"></i>
        <i class="fa-solid fa-xmark"></i>
      </div>
      <div class="c-layer"></div>

      <ul class="p-navigation" id="navigation">
        <li>
          <a href="./index.php">
            <i class="fa-solid fa-house"></i>ホーム画面
          </a>
        </li>
        <li>
          <a href="./account.php">
            <i class="fa-solid fa-user"></i>ユーザー情報</a>
        </li>
        <li>
          <a href="./item-edit.php?editItem=1">
            <i class="fa-solid fa-pen"></i>選択項目の編集</a>
        </li>
        <li>
          <a href="./item-report.php"><i class="fa-solid fa-chart-simple"></i>項目別レポート</a>
        </li>
        <li>
          <a href="./amount-report.php"><i class="fa-solid fa-chart-simple"></i>年間収支レポート</a>
        </li>
        <li>
        <li>
          <a href="./logout.php" id="logoutButton" onclick="logoutConfirm();">
            <i class="fa-solid fa-arrow-right-from-bracket"></i>ログアウト
          </a>
        </li>
      </ul>
    </div>
  </header>

const onToggleNavigation = () => {
  const hamburgerButton = document.getElementById("hamburgerButton");
  const navigation = document.getElementById("navigation");
  if (hamburgerButton.classList.contains("is-open")) {
    //閉じるボタンクリック時
    hamburgerButton.classList.remove("is-open");
    navigation.classList.remove("is-open");
    body.classList.remove("is-fixed");
    hamburgerButton.classList.add("is-close");
    navigation.classList.add("is-close");
  } else {
    //開くボタンクリック時
    hamburgerButton.classList.remove("is-close");
    navigation.classList.remove("is-close");
    hamburgerButton.classList.add("is-open");
    navigation.classList.add("is-open");
    body.classList.add("is-fixed");
  }
};

//以下をelseif内から移動させる
$table_list = ['spending_category', 'income_category', 'creditcard', 'qr'];

if (isset($_POST['year']) && isset($_POST['item']) && isset($_POST['item_table'])) :
  //検索ボタンが押下されたとき（省略）
elseif (isset($_GET['year']) && isset($_GET['item']) && $_GET['item'] !== '' && isset($_GET['num']) && $_GET['num'] < 4) :
  //円グラフ横の表から遷移されたとき（省略）
else :
  //追記↓↓
  $stmt = $db->prepare('SELECT id FROM spending_category WHERE user_id = ? ORDER BY id ASC LIMIT 1');
  $stmt->bind_param('i', $user_id);
  $count = sql_check($stmt, $db);
  $stmt->bind_result($id);
  $stmt->fetch();
  $stmt->close();
  $year = date('Y');
  $item = $id;
  $num = 0;
  $item_table = $table_list[$num];
  //どちらにも当てはまらない場合はエラー付きパラメータを付けて返す(以下2行削除)
  // header('Location: ./index.php?dataOperation=error');
  // exit();
endif;

<form class="p-form--bar-graph all-report u-flex-box" action="" method="POST" name="graphSearch">
  <input type="submit" name="prev" class="fas" value="">
  <input type="hidden" name="year" value="2023-01" readonly>
  <input type="text" name="showyear" value="2023年" readonly>
  <input type="submit" name="next" class="fas" value="">
  <div class="p-form__flex-input">
    <input id="spending" type="radio" name="type" value="0" onchange="" required>
    <label for="spending">支出</label>
    <input type="radio" name="type" id="income" value="1" onchange="">
    <label for="income">収入</label>
  </div>
</form>

<div class="sum">
  <p>
    年間支出金額：
    <span>¥100,000</span>
  </p>
</div>

if (isset($_POST['prev'])) :
  $type = filter_input(INPUT_POST, 'type', FILTER_SANITIZE_NUMBER_INT);
  $now_year = filter_input(INPUT_POST, 'year', FILTER_SANITIZE_NUMBER_INT);
  $base_year = strtotime($now_year);
  $year = date('Y', strtotime('-1 year', $base_year));
elseif (isset($_POST['next'])) :
  $type = filter_input(INPUT_POST, 'type', FILTER_SANITIZE_NUMBER_INT);
  $now_year = filter_input(INPUT_POST, 'year', FILTER_SANITIZE_NUMBER_INT);
  $base_year = strtotime($now_year);
  $year = date('Y', strtotime('+1 year', $base_year));
else :
  $type = '0';
  $year = date('Y');
endif;

<form class="p-form--bar-graph all-report u-flex-box" action="" method="POST" name="graphSearch">
  <input type="submit" name="prev" class="fas" value="">

  <!--以下2行のvalueの2023を送られてきたデータになるように修正-->
  <input type="hidden" name="year" value="<?php echo $year; ?>-01" readonly>
  <input type="text" name="showyear" value="<?php echo $year; ?>年" readonly>

  <input type="submit" name="next" class="fas" value="">
  <div class="p-form__flex-input">
    <!--以下のinput要素2箇所に送られてきたタイプ（支出か収入）を判断してchecked属性を出力する処理追加-->
    <input id="spending" type="radio" name="type" value="0" onchange="" required <?php echo $type === '0' ? 'checked' : ''; ?>>
    <label for="spending">支出</label>
    <input type="radio" name="type" id="income" value="1" onchange="" <?php echo $type === '1' ? 'checked' : ''; ?>>
    <label for="income">収入</label>
  </div>
</form>

<input id="spending" type="radio" name="type" value="0" onchange="submit(this.form)" required <?php echo $type === '0' ? 'checked' : ''; ?>>
<input type="radio" name="type" id="income" value="1" onchange="submit(this.form)" <?php echo $type === '1' ? 'checked' : ''; ?>>

if (isset($_POST['prev'])) :
  $type = filter_input(INPUT_POST, 'type', FILTER_SANITIZE_NUMBER_INT);
  $now_year = filter_input(INPUT_POST, 'year', FILTER_SANITIZE_NUMBER_INT);
  $base_year = strtotime($now_year);
  $year = date('Y', strtotime('-1 year', $base_year));
elseif (isset($_POST['next'])) :
  $type = filter_input(INPUT_POST, 'type', FILTER_SANITIZE_NUMBER_INT);
  $now_year = filter_input(INPUT_POST, 'year', FILTER_SANITIZE_NUMBER_INT);
  $base_year = strtotime($now_year);
  $year = date('Y', strtotime('+1 year', $base_year));

//追加
elseif (isset($_POST['type'])) :
  $type = filter_input(INPUT_POST, 'type', FILTER_SANITIZE_NUMBER_INT);
  $now_year = filter_input(INPUT_POST, 'year', FILTER_SANITIZE_NUMBER_INT);
  $base_year = strtotime($now_year);
  $year = date('Y', $base_year);
//追加ここまで

else :
  $type = '0';
  $year = date('Y');
endif;

<div class="sum">
  <?php
  $sql = 'SELECT SUM(amount) FROM records WHERE user_id=? AND type=? AND date LIKE ?';
  $stmt = $db->prepare($sql);
  $year_param = $year . '%';
  $stmt->bind_param('iis', $user_id, $type, $year_param);
  $stmt->execute();
  $stmt->bind_result($amount_sum);
  $stmt->fetch(); ?>
  <p>
    年間<?php echo $type === '0' ? '支出' : '収入'; ?>金額：
    <span>¥<?php echo number_format($amount_sum); ?></span>
  </p>
  <?php $stmt->close(); ?>
</div>

<?php
//すべての月が0円のデータ配列を生成
$month_list = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
$data = array();
for ($a = 0; $a < count($month_list); $a++) :
  $data[] = [
    'year_month' => $year . '-' .  $month_list[$a],
    'amount' => '0'
  ];
endfor;

//選択された年のデータを抽出
$sql = "SELECT LEFT(date, 7) as month, SUM(amount)
FROM records
WHERE user_id=? AND type=? AND date LIKE ?
GROUP BY month
ORDER BY month ASC";
$stmt = $db->prepare($sql);
$stmt->bind_param('iis', $user_id, $type, $year_param);
$stmt->execute();
$stmt->bind_result($month, $sum);
while ($stmt->fetch()) :
  //取得したデータをデータ配列に再代入（1円以上のデータ月のみ）
  $key = array_search($month, array_column($data, 'year_month'));
  $data[$key]['amount'] = $sum;
endwhile;

//JSON形式に変換
$year_month = [];
$sum = [];
for ($i = 0; $i < count($month_list); $i++) :
  $year_month[] = str_replace($year . '-', '', $data[$i]['year_month']) . '月';
  $sum[] = $data[$i]['amount'];
endfor;
$json_month = json_encode($year_month);
$json_amount = json_encode($sum);
?>

//以下を削除
const itemTable = document.getElementById('itemTable');
const selectForm = document.graphSearch.item;
//key:テーブル名、値:optgroupのlabelの連想配列
const tableList = {
  'spending_category': '支出カテゴリー',
  'income_category': '収入カテゴリー',
  'creditcard': 'クレジットカード',
  'qr': 'スマホ決済'
};
const onChangeItem = () => {
  //選択されたoptionが何番目の要素か
  const num = document.graphSearch.item.selectedIndex;
  //選択されたoptionの親要素optgroupのラベル取得
  const optGroupLabel = selectForm.options[num].parentNode.label;
  //取得したoptgroupラベルと一致する連想配列の値を検索し、そのkey名を返す
  const key = Object.keys(tableList).find((key) => tableList[key] === optGroupLabel);
  //テーブル名を入れるinputのvalueを書き換え
  itemTable.value = key;
}

//書き換え前
labels: <?php echo $json_year_month; ?>,

//書き換え後
labels: <?php echo $json_month; ?>,