PDOのまとめノート2

More than 3 years have passed since last update.


参照/引用

「PHPとMySQLのツボとコツがゼッタイにわかる本」


  • サンプルソース


データベース


db.php


<?php
class DB{
private $USER = "root";
private $PW = "******";
private $dns = "mysql:dbname=salesmanagement;host=localhost;charset=utf8";

private function Connectdb(){
try{
$pdo = new PDO($this->dns,$this->USER,$this->PW);
return $pdo;
}catch(Exception $e){
return false;
}
}

protected function executeSQL($sql, $array){
try{
if(!$pdo = $this->Connectdb())return false;
$stmt = $pdo->prepare($sql);
$stmt->execute($array);
return $stmt;
}catch(Exception $e){
return false;
}
}
}
?>




スクリーンショット 2015-09-23 11.13.46.png


goodsのクラス


DBGoods.php

<?php

require_once('db.php');
class DBGoods extends DB{
//goodsテーブルのCRUD担当
public function SelectGoodsAll(){
$sql = "SELECT * FROM goods";
$res = parent::executeSQL($sql, null);
$data = "<table class='recordlist' id='goodsTable'>";
$data .= "<tr><th>ID</th><th>商品名</th><th>単価</th><th></th><th></th></tr>\n";
foreach($rows = $res->fetchAll(PDO::FETCH_NUM) as $row){
$data .= "<tr>";
for($i=0;$i<count($row);$i++){
$data .= "<td>{$row[$i]}</td>";
}
//更新ボタンのコード
$data .= <<<eof
<td><form method='post' action=''>
<input type='hidden' name='id' value='{$row[0]}'>
<input type='submit' name='update' value='更新'>
</form></td>
eof;
//削除ボタンのコード
$data .= <<<eof
<td><form method='post' action=''>
<input type='hidden' name='id' id='Deleteid' value='{$row[0]}'>
<input type='submit' name='delete' id='delete' value='削除'
onClick='return CheckDelete()'>
</form></td>
eof;
$data .= "</tr>\n";
}
$data .= "</table>\n";
return $data;
}

public function InsertGoods(){
$sql = "INSERT INTO goods VALUES(?,?,?)";
$array = array($_POST['GoodsID'],$_POST['GoodsName'],$_POST['Price']);
parent::executeSQL($sql, $array);
}

public function UpdateGoods(){
$sql = "UPDATE Goods SET GoodsName=?, Price=? WHERE GoodsID=?";
//array関数の引数の順番に注意する
$array = array($_POST['GoodsName'],$_POST['Price'],$_POST['GoodsID']);
parent::executeSQL($sql, $array);
}

public function GoodsNameForUpdate($GoodsID){
return $this->FieldValueForUpdate($GoodsID, "GoodsName");
}

public function PriceForUpdate($GoodsID){
return $this->FieldValueForUpdate($GoodsID, "Price");
}

private function FieldValueForUpdate($GoodsID, $field){
//private関数 上の2つの関数で使用している
$sql = "SELECT {$field} FROM goods WHERE GoodsID=?";
$array = array($GoodsID);
$res = parent::executeSQL($sql, $array);
$rows = $res->fetch(PDO::FETCH_NUM);
return $rows[0];
}

public function DeleteGoods($GoodsID){
$sql = "DELETE FROM goods WHERE GoodsID=?";
$array = array($GoodsID);
parent::executeSQL($sql, $array);
}
}
?>



goods.phpのページ


goods.php

<?php

require_once('DBGoods.php');
$dbGoods = new DBGoods();
//更新処理
if(isset($_POST['submitUpdate'])){
$dbGoods->UpdateGoods();
}
//更新用フォーム要素の表示
if(isset($_POST['update'])){
//更新対象の値を取得
$dbGoodsId = $_POST['id'];
$dbGoodsName = $dbGoods->GoodsNameForUpdate($_POST['id']);
$Price = $dbGoods->PriceForUpdate($_POST['id']);
//クラスを記述することで表示/非表示を設定
$entryCss = "class='hideArea'";
$updateCss = "";
}else{
$entryCss = "";
$updateCss = "class='hideArea'";
}
//削除処理
if(isset($_POST['delete'])){
$dbGoods->DeleteGoods($_POST['id']);
}
//新規登録処理
if(isset($_POST['submitEntry'])){
$dbGoods->InsertGoods();
}
//テーブルデータの一覧表示
$data = $dbGoods->SelectGoodsAll();
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>売上管理システム</title>
<link rel="stylesheet" type="text/css" href="style.css" />
<script type="text/javascript">
function CheckDelete(){
return confirm("削除してもよろしいですか?");
}
</script>
</head>
<body>
<div id="menu">
<ul>
<li><a href="salesinfo.php">売上情報</a></li>
<li><a href="salesinfoEntry.php">伝票の新規作成</a></li>
<li><a href="bill.php">請求書</a></li>
<li><a href="customer.php">顧客マスタ</a></li>
<li><a href="goods.php">商品マスタ</a></li>
</ul>
</div>
<h1>商品マスター</h1>
<div id="entry" <?php echo $entryCss;?>>
<form action="" method="post">
<h2>新規登録</h2>
<label><span class="entrylabel">ID</span><input type='text' name='GoodsID' size="10" required></label>
<label><span class="entrylabel">商品名</span><input type='text' name='GoodsName' size="30" required></label>
<label><span class="entrylabel">単価</span><input type='text' name='Price' size="10" required></label>
<input type='submit' name='submitEntry' value='  新規登録  '>
</form>
</div>
<div id="update" <?php echo $updateCss;?>>
<form action="" method="post">
<h2>更新</h2>
<p>GoodsID: <?php echo $dbGoodsId;?></p>
<input type="hidden" name="GoodsID" value="<?php echo $dbGoodsId;?>" />
<label><span class="entrylabel">商品名</span><input type='text' name='GoodsName'
size="30" value="<?php echo $dbGoodsName;?>" required></label>
<label><span class="entrylabel">単価</span><input type='text' name='Price'
size="10" value="<?php echo $Price;?>" required></label>
<input type='submit' name='submitUpdate' value='  更新  '>
</form>
</div>
<div>
<?php echo $data;?>
</div>
</body>
</html>



スクリーンショット 2015-09-23 11.26.55.png


customerのクラス


DBCustomer.php

<?php

require_once('db.php');
class DBCustomer extends DB{
//customerテーブルのCRUD担当
public function UpdateCustomer(){
$sql = "UPDATE customer SET CustomerName=?, TEL=?, Email=? WHERE CustomerID=?";
//array関数の引数の順番に注意すること
$array = array($_POST['CustomerName'], $_POST['TEL'], $_POST['Email'],$_POST['CustomerID']);
parent::executeSQL($sql, $array);
}

public function CustomerNameForUpdate($CustomerID){
return $this->FieldValueForUpdate($CustomerID, "CustomerName");
}

public function TELForUpdate($CustomerID){
return $this->FieldValueForUpdate($CustomerID, "TEL");
}

public function EmailForUpdate($CustomerID){
return $this->FieldValueForUpdate($CustomerID, "Email");
}

private function FieldValueForUpdate($CustomerID, $field){
//引数の値を取得
$sql = "SELECT {$field} FROM customer WHERE CustomerID=?";
$array = array($CustomerID);
$res = parent::executeSQL($sql, $array);
$rows = $res->fetch(PDO::FETCH_NUM);
return $rows[0];
}

public function DeleteCustomer($CustomerID){
$sql = "DELETE FROM customer WHERE CustomerID=?";
$array = array($CustomerID);
parent::executeSQL($sql, $array);
}

public function InsertCustomer(){
$sql = "INSERT INTO customer VALUE(?,?,?,?)";
$array = array($_POST['CustomerID'],$_POST['CustomerName'],$_POST['TEL'],$_POST['Email']);
parent:: executeSQL($sql, $array);
}

public function SelectCustomerAll(){
$sql = "SELECT * FROM customer";
$res = parent::executeSQL($sql, null);
$data = "<table class='recordlist'>";
$data .="<tr><th>ID</th><th>顧客名</th><th>TEL</th><th>Email</th><th></th><th></th></tr>\n";
foreach($rows=$res->fetchAll(PDO::FETCH_NUM) as $row){
$data .= "<tr>";
for($i=0;$i<count($row);$i++){
$data .= "<td>{$row[$i]}</td>";
}
//更新ボタンのコード
$data .= <<<eof
<td><form method='post' action=''>
<input type='hidden' name='id' value='{$row[0]}'>
<input type='submit' name='update' value='更新'>
</form></td>
eof;
//削除ボタンのコード
$data .= <<<eof
<td><form method='post' action=''>
<input type='hidden' name='id' id='Deleteid' value='{$row[0]}'>
<input type='submit' name='delete' id='delete' value='削除' onClick='return CheckDelete()'>
</form></td>
eof;
$data .= "</tr>\n";
}
$data .= "</table>\n";
return $data;
}
}
?>



customer.phpのページ


customer.php


<?php
require_once('DBCustomer.php');
$dbCustomer = new DBCustomer();
//更新処理
if(isset($_POST['submitUpdate'])){
$dbCustomer->UpdateCustomer();
}
//更新用フォーム要素の表示
if(isset($_POST['update'])){
//更新対象の値を取得
$dbCustomerId = $_POST['id'];
$dbCustomerName = $dbCustomer->CustomerNameForUpdate($_POST['id']);
$tel = $dbCustomer->TELForUpdate($_POST['id']);
$email = $dbCustomer->EmailForUpdate($_POST['id']);
//クラスを記述することで表示/非表示を設定
$entryCss = "class='hideArea'";
$updateCss = "";
}else{
$entryCss = "";
$updateCss = "class='hideArea'";
}
//削除処理
if(isset($_POST['delete'])){
$dbCustomer->DeleteCustomer($_POST['id']);
}
//新規登録処理
if(isset($_POST['submitEntry'])){
$dbCustomer->InsertCustomer();
}
//全レコードの表示
$data = $dbCustomer->SelectCustomerAll();
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>売上管理システム</title>
<link rel="stylesheet" type="text/css" href="style.css" />
<script type="text/javascript">
function CheckDelete(){
return confirm("削除してもよろしいですか?");
}
</script>
</head>
<body>
<div id="menu">
<ul>
<li><a href="salesinfo.php">売上情報</a></li>
<li><a href="salesinfoEntry.php">伝票の新規作成</a></li>
<li><a href="bill.php">請求書</a></li>
<li><a href="customer.php">顧客マスタ</a></li>
<li><a href="goods.php">商品マスタ</a></li>
</ul>
</div>
<h1>顧客マスタ</h1>
<div id="entry" <?php echo $entryCss;?>>
<form action="" method="post">
<h2>新規登録</h2>
<label><span class="entrylabel">ID</span><input type='text' name='CustomerID'
size="10" required></label>
<label><span class="entrylabel">顧客名</span><input type='text' name='CustomerName'
size="30" required></label>
<label><span class="entrylabel">TEL</span><input type='tel' name='TEL' size="15"></label>
<label><span class="entrylabel">Email</span><input type='mail' name='Email' size="40"></label>
<input type='submit' name='submitEntry' value='  新規登録  '>
</form>
</div>
<div id="update" <?php echo $updateCss;?>>
<form action="" method="post">
<h2>更新</h2>
<p>CustomerID: <?php echo $dbCustomerId;?></p>
<input type="hidden" name="CustomerID" value="<?php echo $dbCustomerId;?>" />
<label><span class="entrylabel">顧客名</span><input type='text' name='CustomerName'
size="30" value="<?php echo $dbCustomerName;?>" required></label>
<label><span class="entrylabel">TEL</span><input type='tel' name='TEL'
size="15" value="<?php echo $tel;?>"></label>
<label><span class="entrylabel">Email</span><input type='mail' name='Email'
size="40" value="<?php echo $email;?>"></label>
<input type='submit' name='submitUpdate' value='  更新  '>
</form>
</div>
<div>
<?php echo $data;?>
</div>

</body>
</html>




スクリーンショット 2015-09-23 11.37.34.png


salesinfoのクラス


DBSalesinfo.php

<?php

require_once('db.php');
class DBSalesInfo extends DB{
//salesinfoテーブルのCRUD担当
public function ListGoods(){
//商品名リストの作成
$sql = "SELECT GoodsID,GoodsName,Price FROM goods ORDER BY GoodsID";
$res = parent::executeSQL($sql,null);
$list = "<select name='GoodsID'>\n";
$list .= "<option value='-99'>--選択してください--</option>\n";
foreach($rows=$res->fetchAll(PDO::FETCH_ASSOC) as $row){
$list .= "<option value='{$row['GoodsID']}'>{$row['GoodsName']}</option>\n";
}
$list .= "</select>\n";
return $list;
}

public function InsertSalesinfo(){
//顧客IDと商品IDが選択されていたら登録
if($_POST['CustomerID']>0 && $_POST['GoodsID']>0){
$sql ="INSERT INTO salesinfo VALUES(?,?,?,?,?)";
$array = array(null, $_POST['SalesDate'], $_POST['CustomerID'],
$_POST['GoodsID'], $_POST['Quantity']);
parent::executeSQL($sql,$array);
}
}

private function getSalesinfo($salesDate, $customerID){
//結果セットを取得
$sql = <<<eof
SELECT salesinfo.id,salesinfo.SalesDate,customer.CustomerName,goods.GoodsName,goods.Price,
salesinfo.Quantity,goods.Price*salesinfo.Quantity
FROM salesinfo INNER JOIN customer ON salesinfo.CustomerID=customer.CustomerID
INNER JOIN goods ON salesinfo.GoodsID=goods.GoodsID
WHERE salesinfo.SalesDate=? and salesinfo.CustomerID=?
ORDER BY salesinfo.id
eof;
$array = array($salesDate, $customerID);
$res = parent::executeSQL($sql,$array);
return $res;
}

public function SelectSalesinfo($salesDate, $customerID){
//日付と顧客IDで売上情報を抽出(更新・削除ボタン付き)
$res = $this->getSalesinfo($salesDate, $customerID);
$data = "<table id='entryslip'>\n";
$data .= "<tr><th>ID</th><th>日付</th><th>顧客名</th><th>商品名</th>
<th>単価</th><th>数量</th><th>金額</th></tr>
\n";
foreach($rows = $res->fetchAll(PDO::FETCH_NUM)as $row){
$data .= "<tr>";
for($i=0;$i<count($row);$i++){
$data .= "<td>{$row[$i]}</td>";
}
$data .= "</tr>\n";
}
$data .= "</table>\n";
return $data;
}

public function ListCustomerWithSelected($CustomerID){
//顧客名リストの作成(引数の値を表示)
$sql = "SELECT CustomerID,CustomerName FROM customer ORDER BY CustomerID";
$res = parent::executeSQL($sql,null);
$list = "<select name='CustomerID'>\n";
$list .= "<option value='-99'>-- 選択してください --</option>\n";
foreach($rows=$res->fetchAll(PDO::FETCH_NUM) as $row){
$selected = ($row[0] == $CustomerID)?'selected':'';
$list .= "<option value='{$row[0]}' {$selected}>{$row[1]}</option>\n";
}
$list .= "</select>\n";
return $list;
}

public function ListCustomer(){
//顧客名リストの作成
$sql = "SELECT CustomerID,CustomerName FROM customer ORDER BY CustomerID";
$res = parent::executeSQL($sql,null);
$list = "<select name='CustomerID'>\n";
$list .= "<option value='-99'>-- 選択してください --</option>\n";
foreach($rows=$res->fetchAll(PDO::FETCH_NUM) as $row){
$list .= "<option value='{$row[0]}'>{$row[1]}</option>\n";
}
$list .= "</select>\n";
return $list;
}

public function DeleteDetail(){
$sql = "DELETE FROM salesinfo WHERE ID=?";
$array = array($_POST['id']);
parent::executeSQL($sql,$array);
}

public function UpdateDetail(){
$sql = "UPDATE salesinfo SET SalesDate=?, CustomerID=?, GoodsID=?, Quantity=? WHERE id=?";
$array = array($_POST['SalesDate'],$_POST['CustomerID'],$_POST['GoodsID'],
$_POST['Quantity'],$_POST['id']);
parent::executeSQL($sql,$array);
}

private function FieldValueForUpdate($id, $field){
//引数の値を取得
$sql = "SELECT {$field} FROM salesinfo WHERE id=?";
$array = array($id);
$res = parent::executeSQL($sql, $array);
$rows = $res->fetch(PDO::FETCH_NUM);
return $rows[0];
}

public function getSalesDate($id){
return $this->FieldValueForUpdate($id, "SalesDate");
}

public function getCustomerID($id){
return $this->FieldValueForUpdate($id, "CustomerID");
}

public function getGoodsID($id){
return $this->FieldValueForUpdate($id, "GoodsID");
}

public function getQuantity($id){
return $this->FieldValueForUpdate($id, "Quantity");
}

public function ListGoodsWithSelected($GoodsID){
//商品名リストの作成(引数の値を表示)
$sql = "SELECT GoodsID,GoodsName FROM goods ORDER BY GoodsID";
$res = parent::executeSQL($sql,null);
$list = "<select name='GoodsID'>\n";
$list .= "<option value='-99'>--選択してください--</option>\n";
foreach($rows=$res->fetchAll(PDO::FETCH_ASSOC) as $row){
$selected = ($row['GoodsID'] == $GoodsID)?'selected':'';
$list .= "<option value='{$row['GoodsID']}' {$selected}>{$row['GoodsName']}</option>\n";
}
$list .= "</select>\n";
return $list;
}

public function SelectSalesinfoWithButton($salesDate, $customerID){
//日付と顧客IDで売上情報を抽出(更新・削除ボタン付き)
$res = $this->getSalesinfo($salesDate, $customerID);
$data = "<table>\n";
$data .= "<tr><th>ID</th><th>日付</th><th>顧客名</th><th>商品名</th>
<th>単価</th><th>数量</th><th>金額</th><th></th><th></th></tr>
\n";
foreach($rows = $res->fetchAll(PDO::FETCH_NUM)as $row){
$data .= "<tr>";
for($i=0;$i<count($row);$i++){
$data .= "<td>{$row[$i]}</td>";
}
$data .= <<<eof
<td><form method='post' action=''>
<input type='hidden' name='id' value='{$row[0]}'>
<input type='submit' name='updatedetail' value='更新'></form></td>
<td><form method='post' action=''>
<input type='hidden' name='id' value='{$row[0]}'>
<input type='submit' name='deletedetail' value='削除' onClick='return CheckDelete()'></form>
</td></tr>\n
eof;
$data .= "</tr>\n";
}
$data .= "</table>\n";
return $data;
}

public function TotalAmount($SalesDate, $CustomerID){
//伝票の合計額
$sql = <<<eof
SELECT sum(salesinfo.Quantity*goods.Price)
FROM salesinfo INNER JOIN goods ON salesinfo.GoodsID = goods.GoodsID
WHERE salesinfo.SalesDate = ? AND salesinfo.CustomerID = ?
eof;
$array = array($SalesDate, $CustomerID);
$res = parent::executeSQL($sql,$array);
$row = $res->fetch(PDO::FETCH_NUM);
return $row[0];
}

public function DeleteSlip(){
$sql = "DELETE FROM salesinfo WHERE SalesDate=? AND CustomerID=?";
$array = array($_POST['SalesDate'],$_POST['CustomerID']);
parent::executeSQL($sql,$array);
}

public function SelectSlips($salesDate){
//日付で抽出
$sql = <<<eof
SELECT distinct salesinfo.SalesDate,salesinfo.CustomerID,customer.CustomerName
FROM salesinfo INNER JOIN customer ON salesinfo.CustomerID=customer.CustomerID
WHERE salesinfo.SalesDate=?
ORDER BY customer.CustomerID
eof;
$array = array($salesDate);
$res = parent::executeSQL($sql,$array);
$data = "";
foreach($rows = $res->fetchAll(PDO::FETCH_NUM)as $row){
$data .= "<tr>";
for($i=0;$i<count($row);$i++){
$data .= "<td>{$row[$i]}</td>";
}
$data .= <<<eof
<td><form method='post' action=''>
<input type='hidden' name='SalesDate' value='{$row[0]}'>
<input type='hidden' name='CustomerID' value='{$row[1]}'>
<input type='submit' name='detail' value='詳細'></form></td>
<td><form method='post' action=''>
<input type='hidden' name='SalesDate' value='{$row[0]}'>
<input type='hidden' name='CustomerID' value='{$row[1]}'>
<input type='submit' name='delete' value='削除' onClick='return CheckDelete()'></form>
</td></tr>\n
eof;
}
return $data;
}
}
?>



salesinfo.phpのページ


salesinfo.php


<?php
require('DBSalesInfo.php');
$slipDetail = "";
$total = "";
$SalesDate = new DateTime('NOW');
$SalesDate = $SalesDate->format("Y-m-d");
$dbSalesInfo = new DBSalesInfo();
//日付の設定
if(isset($_POST['SalesDate'])){
$SalesDate = $_POST['SalesDate'];
}
//←ボタンの処理(1日前を表示)
if(isset($_POST['prev'])){
$SalesDate = new DateTime($_POST['SalesDate']);
//日付の減算処理はsubメソッド
$SalesDate->sub(new DateInterval('P1D'));
$SalesDate = $SalesDate ->format("Y-m-d");
}
//→ボタンの処理(1日後を表示)
if(isset($_POST['next'])){
$SalesDate = new DateTime($_POST['SalesDate']);
//日付の加算処理はaddメソッド
$SalesDate->add(new DateInterval('P1D'));
$SalesDate = $SalesDate ->format("Y-m-d");
}
//伝票明細の削除
if(isset($_POST['deletedetail'])){
$SalesDate = $dbSalesInfo->getSalesDate($_POST['id']);
$dbSalesInfo->DeleteDetail();
}
//明細行の更新
if(isset($_POST['submit_updatedetail'])){
$SalesDate = $_POST['SalesDate'];
$dbSalesInfo->UpdateDetail();
}
//明細行の更新ボタンの処理(更新用フォームの表示とデータの設定)
$updateCss = "class='hideArea'";
if(isset($_POST{'updatedetail'})){
//フォーム要素の仕込み
$updateCss = "";
$id = $_POST['id'];
$SalesDate = $dbSalesInfo->getSalesDate($id);
$CustomerID = $dbSalesInfo->getCustomerID($id);
$CustomerList = $dbSalesInfo->ListCustomerWithSelected($CustomerID);
$GoodsID = $dbSalesInfo->getGoodsID($id);
$GoodsList = $dbSalesInfo->ListGoodsWithSelected($GoodsID);
$Quantity = $dbSalesInfo->getQuantity($id);
}
//詳細ボタンの処理(日付と顧客IDで伝票を抽出して合計額を表示)
if(isset($_POST['detail'])){
$SalesDate = $_POST['SalesDate'];
$CustomerID = $_POST['CustomerID'];
$slipDetail = $dbSalesInfo->SelectSalesinfoWithButton($SalesDate,$CustomerID);
$total = $dbSalesInfo->TotalAmount($SalesDate, $CustomerID); //伝票の合計額
//number_format関数は3桁区切りの文字列を返す
$total =($total==null)?"" :"合計金額:" .number_format($total) ."円";
}
//伝票の削除
if(isset($_POST['delete'])){
$dbSalesInfo->DeleteSlip();
}
//指定日の伝票一覧(無条件に表示)
$slips = $dbSalesInfo->SelectSlips($SalesDate);
if($slips == ""){
$slips = "<tr><td>伝票はありません</td></tr>\n";
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>売上管理システム</title>
<link rel="stylesheet" type="text/css" href="style.css" />
<script type="text/javascript">
function CheckDelete(){
return confirm("削除してもよろしいですか?");
}
</script>
</head>
<body>
<div id="menu">
<ul>
<li><a href="salesinfo.php">売上情報</a></li>
<li><a href="salesinfoEntry.php">伝票の新規作成</a></li>
<li><a href="bill.php">請求書</a></li>
<li><a href="customer.php">顧客マスタ</a></li>
<li><a href="goods.php">商品マスタ</a></li>
</ul>
</div>
<h1>売上情報</h1>
<div id="search">
<form method="post" action="">
<label>日付<input type="date" id="SalesDate" name="SalesDate"
value="<?php echo $SalesDate;?>" required></label>
<input type="submit" value="  検索  " name="submit" />
<input type="submit" value="←" id="prev" name="prev" />
<input type="submit" value="→" id="next" name="next" />
</form>
</div>
<div id="sliplist">
<table>
<?php echo $slips;?>
</table>
</div>
<div id="update" <?php echo $updateCss;?>>
<form method="post" action="">
<label>日付<input type="date" id="SalesDate" name="SalesDate"
value="<?php echo $SalesDate;?>" required></label>
<label>顧客名<?php echo $CustomerList;?></label>
<label>商品名<?php echo $GoodsList;?></label>
<label>数量<input type="number" min="0" id="Quantity" name="Quantity"
value="<?php echo $Quantity;?>" required></label>
<input type="hidden" name="id" value="<?php echo $id;?>" />
<input type="submit" value="更新" name="submit_updatedetail" />
</form>
</div>
<div id="detail">
<?php echo $slipDetail;?>

<div id="totalAmount">
<?php echo $total;?>
</div>
</div>
</body>
</html>


スクリーンショット 2015-09-23 11.41.14.png


salesinfoEntry.phpのページ


salesinfoEntry.php

<?php

require('DBSalesInfo.php');
$slip = "";
$SalesDate = new DateTime('NOW');
$SalesDate = $SalesDate->format('Y-m-d');
$CustomerID = "";
$dbSalesInfo = new DbSalesInfo();
//商品名リストの作成
$GoodsList = $dbSalesInfo->ListGoods();
if(isset($_POST['submit'])){
//新規登録処理
$SalesDate = $_POST['SalesDate'];
$CustomerID = $_POST['CustomerID'];
$dbSalesInfo->InsertSalesinfo();
//新規登録後だけ登録データを表示
$slip = $dbSalesInfo->SelectSalesinfo($SalesDate, $CustomerID);
//顧客名リストの作成(選択者を表示)
$CustomerList = $dbSalesInfo->ListCustomerWithSelected($CustomerID);
}else{
//顧客名リストの作成
$CustomerList = $dbSalesInfo->ListCustomer();
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>売上管理システム</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>
<div id="menu">
<ul>
<li><a href="salesinfo.php">売上情報</a></li>
<li><a href="salesinfoEntry.php">伝票の新規作成</a></li>
<li><a href="bill.php">請求書</a></li>
<li><a href="customer.php">顧客マスタ</a></li>
<li><a href="goods.php">商品マスタ</a></li>
</ul>
</div>
<h1>売上伝票の新規作成</h1>
<div id="entry">
<form method="post" action="">
<label>日付<input type="date" id="SalesDate" name="SalesDate"
value="<?php echo $SalesDate;?>" required></label>
<label>顧客名<?php echo $CustomerList;?></label>
<label>商品名<?php echo $GoodsList;?></label>
<label>数量<input type="number" min="0" id="Quantity"
name="Quantity" required></label>
<input type="submit" value="  登録  " name="submit" />
</form>
</div>
<div class="ClearFloat"></div>
<?php echo $slip;?>
</body>
</html>



スクリーンショット 2015-09-23 11.20.26.png


billのクラス


DBBill.php

<?php

require_once('db.php');
class DBBill extends DB{
//bill.phpを担当するクラス
private function SelectCustomers($startDate, $endDate){
//指定期間に存在する顧客一覧の結果セットを取得
$sql = <<<eof
SELECT distinct salesinfo.CustomerID,customer.CustomerName
FROM salesinfo INNER JOIN customer ON salesinfo.CustomerID=customer.CustomerID
WHERE salesinfo.SalesDate BETWEEN ? AND ?
ORDER BY salesinfo.CustomerID
eof;
$array = array($startDate, $endDate);
$res = parent::executeSQL($sql, $array);
return $res;
}

public function SelectTagOfCustomers($startDate, $endDate){
$rows = $this->SelectCustomers($startDate, $endDate)->fetchAll(PDO::FETCH_NUM);
if(count($rows)==0) return "";
$tag = "<select name='CustomerID' id='CustomerID'>\n";
$tag .= "<option value='-99'>-- 選択してください --</option>\n";
foreach($rows as $row){
$tag .= "<option value='{$row[0]}'>{$row[1]}</option>\n";
}
$tag .= "</select>\n";
return $tag;
}

public function getCustomerName($CustomerID){
$sql = "SELECT CustomerName FROM customer WHERE CustomerID=?";
$array = array($CustomerID);
$res = parent::executeSQL($sql,$array);
$row = $res->fetch(PDO::FETCH_NUM);
return $row[0];
}

public function TotalAmount($startDate, $endDate, $CustomerID){
//請求書の合計額
$sql = <<<eof
SELECT sum(salesinfo.Quantity*goods.Price)
FROM salesinfo INNER JOIN goods ON salesinfo.GoodsID = goods.GoodsID
WHERE (salesinfo.SalesDate BETWEEN ? AND ?) AND salesinfo.CustomerID = ?
eof;
$array = array($startDate, $endDate, $CustomerID);
$res = parent::executeSQL($sql,$array);
$row = $res->fetch(PDO::FETCH_NUM);
return $row[0];
}

private function getSalesinfo($startDate, $endDate, $CustomerID){
$sql = <<<eof
SELECT salesinfo.id,salesinfo.SalesDate,salesinfo.GoodsID,goods.GoodsName,
goods.Price,salesinfo.Quantity,(goods.Price*salesinfo.Quantity)
FROM salesinfo INNER JOIN goods ON salesinfo.GoodsID=goods.GoodsID
WHERE salesinfo.SalesDate BETWEEN ? AND ?
AND salesinfo.CustomerID=?
ORDER BY salesinfo.SalesDate,salesinfo.id
eof;
$array = array($startDate, $endDate, $CustomerID);
$res = parent::executeSQL($sql, $array);
return $res;
}

public function SelectSalesinfo($startDate, $endDate, $CustomerID){
//$fieldCount = 7;
$tag = "<table>\n";
$tag .= "<tr><th>ID</th><th>日付</th><th>顧客名</th><th>商品名</th>
<th>単価</th><th>数量</th><th>金額</th><th></th><th></th></tr>
\n";
$res = $this->getSalesinfo($startDate, $endDate, $CustomerID);
foreach($rows = $res->fetchAll(PDO::FETCH_NUM)as $row){
$tag .= "<tr>";
//次の行のcount関数の引数は$rows[0]にすること
for($i=0;$i<count($rows[0]);$i++){
$tag .= "<td>{$row[$i]}</td>";
}
$tag .= "</tr>\n";
}
$tag .= "</table>\n";
return $tag;
}
}
?>



bill.phpのページ


bill.php


<?php
require_once('DBBill.php');
$startDate = "";
$endDate = "";
$TagCustomer = "";
$customerName = "";
$detail = "";
$total = "";
$dbBill = new DBBill();
if(isset($_POST['submit'])){
$startDate = $_POST['startDate'];
$endDate = $_POST['endDate'];
$TagCustomer = $dbBill->SelectTagOfCustomers($startDate, $endDate);
if(isset($_POST['CustomerID'])){
$customerName = $dbBill->getCustomerName($_POST['CustomerID']);
$total = $dbBill->TotalAmount($startDate, $endDate, $_POST['CustomerID']);
$total =($total==null)?"" :"合計金額:" .number_format($total) ."円";
$detail = $dbBill->SelectSalesinfo($startDate, $endDate, $_POST['CustomerID']);
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>売上管理システム</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>
<div id="menu">
<ul>
<li><a href="salesinfo.php">売上情報</a></li>
<li><a href="salesinfoEntry.php">伝票の新規作成</a></li>
<li><a href="bill.php">請求書</a></li>
<li><a href="customer.php">顧客マスタ</a></li>
<li><a href="goods.php">商品マスタ</a></li>
</ul>
</div>
<h1>請求情報</h1>
<div id="search">
<form method="post" action="">
<label>請求期間<input type="date" id="startDate" name="startDate"
value="<?php echo $startDate;?>" required></label>
<label><input type="date" id="endDate" name="endDate"
value="<?php echo $endDate;?>" required></label>
<?php echo $TagCustomer;?>
<input type="submit" value="  検索  " name="submit" />
</form>
</div>
<div id="detail">
<p><?php echo $customerName;?></p>
<div id="totalAmount">
<?php echo $total;?>
</div>
<?php echo $detail;?>
</div>
</body>
</html>