webdata
DX推進をサポートする技術者向け情報提供サイト

初心者向けPHP・データベース入門

TOP >初心者向けPHP・データベース入門 >3.4

日報システム作成(得意先一覧に日報表示追加表示)

 2024-04-29 (更新日:2024-05-19)

<学習する内容>

 得意先一覧に最終日報の表示を作成します。

2)得意先一覧作成


下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名:customerlist3_4.php
配置先:c:\xampp\htdocs\
配置先URL:http://localhost/customerlist3_4.php

サンプルプログラム名: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}">
								</td>
								<td width="60px">
									<input type="submit" value="検索" style="width:100%;">
								</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 align="left">{$address1}
                                <td>{$workDate}
                                <td>{$cnt}
							</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句になります。