＜学習する内容＞
日報の一覧表の表示画面を作成します。
１）日報一覧作成
下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名：dailyreport_list.php
配置先URL：
http://localhost/dailyreport_list.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:#c0c0c0;';
$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:#c0c0c0;";
$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.'">';
//ページ対応
$page = filter_input(INPUT_POST, 'page'); //ページ指定
$add = filter_input(INPUT_POST, 'add'); //ページを戻るか進めるか
if(empty($page)){
$page = 0;
$offset = 0;
}
if($add == 1){ //ページを進める
$page = $page + 1;
}
if($add == -1){ //ページを戻す
$page = $page - 1;
}
$offset = $page * 5; //5行のデータを表示 20にすると２０行表示
//合計件数の集計
$sql = "SELECT a.id
from dailyreport AS a
left join Msection AS b
ON a.sectionCode = b.sectionCode
left join Mstaff AS c
ON a.staffCode = c.staffCode
left join Mcustomer AS d
ON a.customerCode = d.customerCode
WHERE a.id > 0
$where_and_section
$where_and_staff
$where_and_customer
$where_and_class
$where_and_work
ORDER BY workDate DESC
;";
$res = mysqli_query($conn,$sql) or die("er select $sql");
$cnt = mysqli_affected_rows($conn);
$cnt_all = mysqli_num_rows($res);
$cnt = ceil($cnt_all/5);
$page_add1 = $page + 1; //表示させるページ
//HTML本文
echo <<<EOD
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<link rel="stylesheet" href="/style.css" type="text/css">
<title>日報一覧</title>
</head>
<body>
日報一覧<br>
<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>
<td width="37">
EOD;
if($page > 0){ //戻るページがあれば下記を表示
echo <<<EOD
<form method="POST" action="{$_SERVER['PHP_SELF']}">
<td width="25px">
{$OPT_DATA}
<input type="hidden" name="add" value="-1">
<input type="hidden" name="page" value="{$page}">
<input type="submit" value="<" style="width:25px;">
</td>
</form>
EOD;
}else{
echo <<<EOD
<td width="25px"></td>
EOD;
}
if($page_add1 < $cnt){ //進めるページがあれば下記を表示
echo <<<EOD
<form method="POST" action="{$_SERVER['PHP_SELF']}">
<td width="25px">
{$OPT_DATA}
<input type="hidden" name="add" value="1">
<input type="hidden" name="page" value="{$page}">
<input type="submit" value=">" style="width:25px;">
</td>
</form>
EOD;
}else{
echo <<<EOD
<td width="25px"></td>
EOD;
}
echo <<<EOD
<td width="150" align="right"> {$page_add1} / {$cnt}ページ {$cnt_all}件<td>
</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="90">実施日</td>
<td width="250">得意先</td>
<td width="400">内容（先頭50文字）</td>
<td width="60">修正</td>
</tr>
EOD;
$sql = "SELECT a.id
,b.sectionName
,c.staffName
,d.customerName
,a.workDate
,a.class
,a.comment
from dailyreport AS a
left join Msection AS b
ON a.sectionCode = b.sectionCode
left join Mstaff AS c
ON a.staffCode = c.staffCode
left join Mcustomer AS d
ON a.customerCode = d.customerCode
WHERE a.id > 0
$where_and_section
$where_and_staff
$where_and_customer
$where_and_class
$where_and_work
ORDER BY workDate DESC
LIMIT 5 OFFSET $offset
;";
$res = mysqli_query($conn,$sql) or die("er select $sql");
while($row = mysqli_fetch_array($res)){
$id = $row["id"];
$sectionName = $row["sectionName"];
$staffName = $row["staffName"];
$workDate = $row["workDate"];
$workDate = date("Y/n/j",strtotime($workDate));
$class = $row["class"];
$customerName = $row["customerName"];
if($class == 2){
$customerName = '社内作業';
}
$comment = $row["comment"];
$comment = substr($comment,0,50);
echo <<<EOD
<tr bgcolor="white" style="height:24px;" align="center">
<td>{$sectionName}</td>
<td>{$staffName}</td>
<td>{$workDate}</td>
<td align="left">{$customerName}</td>
<td align="left">{$comment}</td>
<form method="POST" action="dailyreport_input.php">
{$OPT_DATA}
<input type="hidden" name="set_id" value="{$id}">
<td>
<input type="submit" style="width:60px;" value="修正">
</td>
</form>
</tr>
EOD;
}
echo <<<EOD
</table>
</body>
</html>
EOD;
?>
＜解説＞
38行目等
<option>タグに style="color:#2E2E2E;"で文字の色（少し薄めの黒）が設定されてます。
これは検索項目名を薄めのグレーで表示しており、このstyleの設定がないとプルダウンに
表示される文字も薄めのグレーで表示されてしまうための対処です。
354行目
action="dailyreport_input.php"が入力画面に遷移する設定にしてます。これにより一覧表より
明細の確認や修正が可能となります。
