＜学習する内容＞
得意先一覧に最終日報の表示を作成します。
２）得意先一覧作成
下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名：customerlist3_4.php
配置先URL：
http://localhost/customerlist3_4.php
<?php
//DBへの接続
include('/xampp/data/conn.php');
//POSTの受信
$fnc = filter_input(INPUT_POST, 'fnc');
$select_id = filter_input(INPUT_POST, 'select_id');
$select_sectionCode = filter_input(INPUT_POST, 'select_sectionCode');
$select_staffCode = filter_input(INPUT_POST, 'select_staffCode');
$select_class = filter_input(INPUT_POST, 'select_class');
$select_workDate = filter_input(INPUT_POST, 'select_workDate');
$select_comment = filter_input(INPUT_POST, 'select_comment');
$select_customerName = filter_input(INPUT_POST, 'select_customerName');
$select_customerName = mb_convert_kana($select_customerName,'A'); //全角化
//変数リセット
$OPT_DATA = null;
$opt_staff = null;
$class_display = null;
$opt_section = null;
$workDate = null;
$opt_date = null;
//変数表示変換
if($select_class == 1){
$select_class_display = '得意先訪問';
}
if($select_class == 2){
$select_class_display = '社内作業';
}
//部署option
$sql = "SELECT sectionCode,sectionName FROM Msection;";
$res = mysqli_query($conn,$sql) or die("error$sql");
$opt_section = '<option style="color:#2E2E2E;" value="">解除';
while($row = mysqli_fetch_array($res)){
$sectionCode_opt = $row["sectionCode"];
$sectionName_opt = $row["sectionName"];
$opt_section .= '<option style="color:#2E2E2E;" value="'.$sectionCode_opt.'">'.$sectionName_opt;
}
//部署検索
if(empty($select_sectionCode)){
$clr_section = 'color:#c0c0c0;';
$select_sectionName = '部署検索';
$where_and_section = null;
}else{
$clr_section = null;
$where_and_section = "and a.sectionCode = $select_sectionCode";
$sql = "SELECT sectionCode,sectionName FROM Msection where sectionCode = $select_sectionCode;";
$res = mysqli_query($conn,$sql) or die("error $sql");
$row = mysqli_fetch_array($res);
$select_sectionName = $row["sectionName"];
}
//担当option
if(strlen($select_sectionCode)){
$sql = "SELECT staffCode,staffName FROM Mstaff where sectionCode = $select_sectionCode;";
$res = mysqli_query($conn,$sql) or die("error $sql");
$opt_staff = '<option style="color:#2E2E2E;" value="">解除';
while($row = mysqli_fetch_array($res)){
$staffCode_opt = $row["staffCode"];
$staffName_opt = $row["staffName"];
$opt_staff .= '<option style="color:#2E2E2E;" value="'.$staffCode_opt.'">'.$staffName_opt;
}
}
//担当者名検索
if(empty($select_staffCode)){
$clr_staff = 'color:#dcdcdc;';
$select_staffName = '担当検索';
$where_and_staff = null;
}else{
$clr_staff = null;
$where_and_staff = "and a.staffCode = $select_staffCode";
$sql = "SELECT staffCode,staffName FROM Mstaff where staffCode = $select_staffCode;";
$res = mysqli_query($conn,$sql) or die("error $sql");
$row = mysqli_fetch_array($res);
$select_staffName = $row["staffName"];
}
//実施日option
$day0 = date("Y-m-d");
$cnt = 60; //60日前まで表示
$opt_date = '<option style="color:#2E2E2E;" value="">解除';
for( $i = 0 ; $i <= $cnt ; $i++ ){
$day = date("Y-m-d",strtotime("-$i day",strtotime($day0)));
$opt_date .= '<option style="color:#2E2E2E;" value="'.$day.'">'.$day;
}
//実施日検索
if(empty($select_workDate)){
$select_workDate_display = '実施日検索';
$clr_workDate = 'color:#c0c0c0;';
$where_and_work = null;
}else{
$clr_workDate = null;
$where_and_work = "and a.workDate = '$select_workDate'";
$select_workDate_display = date("Y-m-d",strtotime($select_workDate));
}
//分類検索
if(empty($select_class)){
$select_class_display = '分類検索';
$clr_class = 'color:#c0c0c0;';
$where_and_class = null;
}else{
$clr_class = null;
$where_and_class = "and a.class = $select_class";
if($select_class == 1){
$select_class_display = '得意先訪問';
}
if($select_class == 2){
$select_class_display = '社内作業';
}
}
//得意先検索
$select_customerName = filter_input(INPUT_POST, 'select_customerName');
$select_customerName = mb_convert_kana($select_customerName,'AK'); //文字の全角化
if(empty($select_customerName)){
$clr_customer = "color:#a9a9a9;";
$where_and_customer = null;
}else{
$clr_customer = null;
$where_and_customer = "where d.customerName like '%$select_customerName%'";
}
//$_POSTデータの保持
$OPT_DATA .= '<input type="hidden" name="select_sectionCode" value="'.$select_sectionCode.'">';
$OPT_DATA .= '<input type="hidden" name="select_staffCode" value="'.$select_staffCode.'">';
$OPT_DATA .= '<input type="hidden" name="select_class" value="'.$select_class.'">';
$OPT_DATA .= '<input type="hidden" name="select_workDate" value="'.$select_workDate.'">';
$OPT_DATA .= '<input type="hidden" name="select_customerName" value="'.$select_customerName.'">';
//HTML本文
echo <<<EOT
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<link rel="stylesheet" href="/style.css" type="text/css">
<title>得意先一覧</title>
</head>
<body>
得意先一覧<br>
EOT;
echo <<<EOT
<table style="font-size:12px;" >
<tr style="height:24px;" align="center">
<form method="POST" action="{$_SERVER['PHP_SELF']}">
{$OPT_DATA}
<td width="70">
<SELECT name="select_sectionCode" onchange="submit(this.form)" style="width:100px;height:26px;{$clr_section}">
<option value="{$select_sectionCode}" selected>{$select_sectionName}
{$opt_section}
</select>
</td>
</form>
<form method="POST" action="{$_SERVER['PHP_SELF']}">
{$OPT_DATA}
<td width="70">
<SELECT name="select_staffCode" onchange="submit(this.form)" style="width:100px;height:26px;{$clr_staff}">
<option value="{$select_staffCode}" selected>{$select_staffName}
{$opt_staff}
</select>
</td>
</form>
<form method="POST" action="{$_SERVER['PHP_SELF']}">
{$OPT_DATA}
<td width="70">
<SELECT name="select_class" onchange="submit(this.form)" style="width:100px;height:26px;{$clr_class}">
<option value="{$select_class}" selected>{$select_class_display}
<option style="color:#2E2E2E;" value="1">得意先訪問
<option style="color:#2E2E2E;" value="2">社内作業
<option style="color:#2E2E2E;" value="">解除
</select>
</td>
</form>
<form method="POST" action="{$_SERVER['PHP_SELF']}">
{$OPT_DATA}
<td width="70">
<SELECT name="select_workDate" onchange="submit(this.form)" style="width:100px;height:26px;{$clr_workDate}">
<option value="{$select_workDate}" selected>{$select_workDate_display}
{$opt_date}
</select>
</td>
</form>
<form method="POST" action="{$_SERVER['PHP_SELF']}">
{$OPT_DATA}
<td width="200px">
<input type="text" name="select_customerName" style="width:200px;height:20px;"
placeholder="得意先名検索" onfocus="this.placeholder=''" onblur="this.placeholder='得意先名検索'"
value="{$select_customerName}"></input>
</td>
<td width="60px">
<input type="submit" value="検索" style="width:100%;"></input>
</td>
</form>
<form method="POST" action="{$_SERVER['PHP_SELF']}">
<td width="60px">
<input type="submit" value="クリア" style="width:60px;">
</td>
</form>
</form>
</tr>
</table>
<table bgcolor="#a9a9a9" cellspacing="1px" style="font-size:12px;" >
<tr bgcolor="#D3D3D3" style="height:24px;" align="center">
<td width="100">部署</td>
<td width="90">担当者</td>
<td width="250">得意先</td>
<td width="250">住所</td>
<td width="120">最終訪問日</td>
<td width="80">訪問件数</td>
</tr>
EOT;
$sql = "SELECT b.sectionName,c.staffName,a.customerName,a.customerCode,report.workDate,a.address1,a.address2,report2.cnt
FROM Mcustomer AS a
LEFT JOIN dailyreport AS report ON a.customerCode = report.customerCode
LEFT JOIN (SELECT customerCode,COUNT(id) AS cnt
FROM dailyreport
GROUP BY customerCode) AS report2
ON a.customerCode = report2.customerCode
LEFT JOIN Msection AS b ON report.sectionCode = b.sectionCode
LEFT JOIN Mstaff AS c ON report.staffCode = c.staffCode
where report.workDate = (SELECT MAX(workDate) FROM dailyreport where customerCode = report.customerCode)
$where_and_section
$where_and_staff
$where_and_customer
$where_and_class
$where_and_work
GROUP BY a.customerCode
ORDER BY report.workDate DESC
;";
$res = mysqli_query($conn,$sql) or die("er SELECT $sql");
while($row = mysqli_fetch_array($res)){
$sectionName = $row["sectionName"];
$staffName = $row["staffName"];
$workDate = $row["workDate"];
$workDate = date("Y/n/j",strtotime($workDate));
$customerCode = $row["customerCode"];
$customerName = $row["customerName"];
$address1 = $row["address1"];
$cnt = $row["cnt"];
//今年度訪問件数集計
echo <<<EOT
<tr bgcolor="white" style="height:24px;" align="center">
<td>{$sectionName}</td>
<td>{$staffName}</td>
<td align="left">{$customerName}</td>
<td align="left">{$address1}</td>
<td>{$workDate}</td>
<td>{$cnt}</td>
</tr>
EOT;
}
echo <<<EOT
</table>
</body>
</html>
EOT;
?>
＜解説＞
229行目からのSQL文で232、233行目と236行目がポイントです。
232行目に記述のSELECT文の結果（得意先毎の訪問件数）をMcustomerにLEFT JOIN させるというSQLです。
SELECT COUNT(id) AS cnt,customerCode FROM dailyreport GROUP BY customerCode
BROUP BY というのはcustomerCode(得意先)毎 グループ化します。この結果、得意先コード毎の訪問件数をcount(id)（idの数）にて訪問件数を抽出してます。
この得意先コードをreport2.customerCodeとしてMcustomerとリレーションすることによりreport2.cntとして抽出されます。
AS report1 や AS report2 とはエイリアßスでこのSQL文内に限りASの手前にあるテーブルに違う名前をつけることができます。今回はreport1や2と名付けましたが
他のテーブル同様fとかTとかGGとSQL文の予約語以外は自由に命名できます。
次に233行目ですが、得意先毎の最終訪問日を抽出します。
全体のSQL文のWHERE句として該当する得意先の最終訪問日（ MAX(workDate) で抽出されます。）
WHERE report.workDate = (SELECT MAX(workDate) FROM dailyreport
where customerCode = report.customerCode)
この意味は、report.workDateが抽出されているreport.customerCodeと同じの最終訪問日のデータを
抽出しなさいというWHERE句になります。
