【PHPでECサイト④】購入履歴・購入明細

## はじめに
今回は、購入履歴を実装していきます。
**※当ページは、[【PHPでECサイト】](https://qiita.com/taqmi/items/877292fc8c902f86e20e)で作られたものを前提にしています。**

### バージョン
PHP：7.4.5
phpMyAdmin：5.0.2
MySQL：5.7.30

### 今回作成するファイル
**html**
- history.php
- detail.php

**model**
- histories.php

**view**
- history_view.php
- detail_view.php

## テーブルの作成

```sql:sample_histories
CREATE TABLE `sample_histories` (
    `order_id` int(11) NOT NULL,
    `user_id` int(11) NOT NULL,
    `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `sample_histories`
  MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (`order_id`),
  ADD KEY `user_id` (`user_id`);
```

```sql:sample_details
CREATE TABLE `sample_details` (
    `order_id` int(11) NOT NULL,
    `item_id` int(11) NOT NULL,
    `amount` int(11) NOT NULL,
    `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `sample_details`
  ADD KEY `item_id` (`item_id`);
```

## 定義
```php:const.php
define('HISTORY_URL', '/order_history.php');
define('DETAIL_URL', 'order_detail.php');
```
## カートの購入処理に追記

```php:carts.php
// 購入処理
function purchase_carts($db, $carts){
  if(validate_cart_purchase($carts) === false){
    return false;
  }
  // 購入後、カートの中身削除&在庫変動&購入履歴・明細にデータを挿入
  $db->beginTransaction();
  try {
    insert_history($db, $carts[0]['user_id']);
    $order_id = $db->lastInsertId();
    
    foreach($carts as $cart){ 
      insert_detail($db, $order_id, $cart['item_id'], $cart['price'], $cart['amount']);   
      if(update_stock($db, $cart['item_id'], $cart['stock'] - $cart['amount']) === false){
          set_error($cart['name'] . 'の購入に失敗しました。');
        }
      }
      delete_user_carts($db, $carts[0]['user_id']);
      $db->commit();
  }catch(PDOException $e){
    $db->rollback();
    throw $e;
  }
}

// 購入履歴へINSERT
function insert_history($db, $user_id){
  $sql = "
    INSERT INTO
      sample_histories(
        user_id
      )
    VALUES(?)
  ";
  return execute_query($db, $sql, array($user_id));
}

// 購入明細にINSERT
function insert_detail($db, $order_id, $item_id, $price, $amount){
  $sql = "
    INSERT INTO
      sample_details(
        order_id,
        item_id,
        price,
        amount
      )
    VALUES(?,?,?,?)
  ";
  return execute_query($db, $sql, array($order_id, $item_id, $price, $amount));
}
```

## 購入履歴

### Model
```php:histories.php
<?php
require_once MODEL_PATH. 'functions.php';
require_once MODEL_PATH. 'db.php';

// ユーザ毎の購入履歴
function get_history($db, $user_id){
    $sql = "
      SELECT
        sample_histories.order_id,
        sample_histories.created,
        SUM(sample_details.price * sample_details.amount) AS total
      FROM
        sample_histories
      JOIN
        sample_details
      ON
        sample_histories.order_id = sample_details.order_id
      WHERE
        user_id = ?
      GROUP BY
        order_id
      ORDER BY
        created desc
    ";
    return fetch_all_query($db, $sql, array($user_id));
}
```

### View

```php:history_view.php
<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="UTF-8">
    <title>購入履歴</title>
  </head>

  <body>
    <h1>購入履歴</h1>

    <!-- メッセージ・エラーメッセージ -->
    <?php include VIEW_PATH. 'templates/messages.php'; ?>

    <!-- 購入履歴 -->
    <?php if(!empty($histories)){ ?>
    <table>
      <thead>
        <tr>
          <th>注文番号</th>
          <th>購入日時</th>
          <th>合計金額</th>
          <th></th>
        </tr>
      </thead>
      <tbody>
      <?php foreach($histories as $history){ ?>
        <tr>
          <td><?php print($history['order_id']); ?></td>
          <td><?php print($history['created']); ?></td>
          <td><?php print($history['total']); ?></td>
          <td>
            <form method="post" action="detail.php">
              <input type="submit" value="購入明細表示">
              <input type="hidden" name="order_id" value="<?php print($history['order_id']); ?>">
            </form>
          </td>
        </tr>
      <?php } ?>
      </tbody>
    </table>
    <?php }else{ ?>
    <p>購入履歴がありません。</p>
    <?php } ?>
  </body>
</html>
```

### Controller

```php:history.php
<?php
require_once '../conf/const.php';
require_once MODEL_PATH. 'functions.php';
require_once MODEL_PATH. 'users.php';
require_once MODEL_PATH. 'items.php';
require_once MODEL_PATH. 'carts.php';
require_once MODEL_PATH. 'histories.php';

session_start();

if(is_logined() === false){
  redirect_to(LOGIN_URL);
}

$db = get_db_connect();
$user = get_login_user($db);
$histories = get_history($db, $user['user_id']);

$order_id = get_post('order_id');

include_once VIEW_PATH. 'history_view.php';
```

## 購入明細

### Model
```php:histories.php
// ユーザ毎の購入明細
function get_detail($db, $order_id){
    $sql = "
      SELECT
        sample_details.price,
        sample_details.amount,
        sample_details.created,
        SUM(sample_details.price * sample_details.amount) AS subtotal,
        sample_items.name
      FROM
        sample_details
      JOIN
        sample_items
      ON
        sample_details.item_id = sample_items.item_id
      WHERE
        order_id = ?
      GROUP BY
        sample_details.price, sample_details.amount, sample_details.created, sample_items.name
    ";
    return fetch_all_query($db, $sql, array($order_id));
}
```

### View
```php:detail_view.php
<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="UTF-8">
    <title>購入明細</title>
  </head>

  <body>
    <h1>購入明細</h1>

    <!-- メッセージ・エラーメッセージ -->
    <?php include VIEW_PATH. 'templates/messages.php'; ?>

    <!-- 購入明細 -->
    <table>
      <thead>
        <tr>
          <th>注文番号</th>
          <th>購入日時</th>
          <th>合計金額</th>
        </tr>
      </thead>
      <tbody>
      <?php foreach($histories as $history){ ?>
        <tr>
          <td><?php print($history['order_id']); ?></td>
          <td><?php print($history['created']); ?></td>
          <td><?php print($history['total']); ?></td>
          <td>
            <form method="post" action="detail.php">
              <input type="submit" value="購入明細表示">
              <input type="hidden" name="order_id" value="<?php print($history['order_id']); ?>">
            </form>
          </td>
        </tr>
      <?php } ?>
      </tbody>
    </table>

    <!-- 購入明細 -->
    <table>
      <thead>
        <tr>
          <th>商品名</th>
          <th>価格</th>
          <th>購入数</th>
          <th>小計</th>
        </tr>
      </thead>
      <tbody>
      <?php foreach($details as $detail){ ?>
        <tr>
          <td><?php print($detail['name']); ?></td>
          <td><?php print($detail['price']); ?></td>
          <td><?php print($detail['amount']); ?></td>
          <td><?php print($detail['subtotal']); ?></td>
        </tr>
      <?php } ?>
      </tbody>
    </table>
  </body>
</html>
```

### Controller

```php:detail.php
<?php
require_once '../conf/const.php';
require_once MODEL_PATH. 'functions.php';
require_once MODEL_PATH. 'users.php';
require_once MODEL_PATH. 'items.php';
require_once MODEL_PATH. 'carts.php';
require_once MODEL_PATH. 'histories.php';

session_start();

if(is_logined() === false){
  redirect_to(LOGIN_URL);
}

$db = get_db_connect();
$user = get_login_user($db);
$histories = get_history($db, $user['user_id']);

$order_id = get_post('order_id');
$details = get_detail($db, $order_id);

include_once VIEW_PATH. 'detail_view.php';
```