＜学習する内容＞
WHERE句にEXTRACT(YEAR_MONTH FROM workdate)関数を使って月毎の売上集計を作成します。
１）売上テーブルの作成 (テーブル名：saleslist)
項
項目名
カラム名
型
バイト数
1
ID
ID
INT
11
2
部署コード
sectionCode
SMALLINT
4
3
担当者コード
staffCode
SMALLINT
4
4
得意先コード
customerCode
INT
11
5
案件名
ankenName
VARCHAR
50
6
売上日
salesDate
DATE
3
7
売上金額
sales
INT
11
8
利益金額
profit
INT
11
＜SQL＞
create table saleslist (ID INT primary key auto_increment,sectionCode smallint(4),
staffCode smallint,customerCode int,ankenName varchar(50),sales int,profit int);
２）抽出SQL文の作成
・案件一覧抽出
SELECT ,c.sectionName
,d.lastName
,b.customerName
,a.ankenName
,a.salesDate
,a.sales
,a.profit
FROM saleslist AS a
LEFT JOIN Mcustomer AS b ON a.customerCode = b.customerCode
LEFT JOIN Msection AS c ON a.sectionCode = c.sectionCode
LEFT JOIN Mstaff AS d ON a.staffCode = d.staffCode
WHERE salesDate >= '2024-04-01'
AND salesDate <= '2024-09-30';
・2024年4月から2024年9月までの月毎の売上集計抽出
SELECT sum(a.sales) AS sales
,sum(a.profit) AS profit
,c.sectionName
,d.lastName
,EXTRACT(YEAR_MONTH FROM salesDate) AS salesMonth
FROM saleslist AS a
LEFT JOIN Msetion AS c ON a.sectionCode = c.sectionCode
LEFT JOIN Mstaff AS d ON a.staffCode = d.staffCode
WHERE salesDate >= '2024-04-01'
AND salesDate < '2024-10-01'
GROUP BY salesMonth;
＜解説＞
３）売上一覧の作成
下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名：saleslist.php
配置先URL：
http://localhost/saleslist.php
<?php
//DBへの接続
include('/xampp/data/conn.php');
//POSTの受信
$fnc = filter_input(INPUT_POST, 'fnc');
$select_id = filter_input(INPUT_POST, 'select_id');
$select_date = filter_input(INPUT_POST, 'select_date');
$select_sectionCode = filter_input(INPUT_POST, 'select_sectionCode');
$select_staffCode = filter_input(INPUT_POST, 'select_staffCode');
//年度option
$day = date("Y-m-01"); //当月1日
$num = 10; //表示期数
for ($i = 0;$i <= $num;$i++) {
$s = $i * 6; //半期6か月毎
$FY = date("Y",strtotime("-$s month",strtotime($day)));
$month = date("n",strtotime("-$s month",strtotime($day)));
$date = date("Y-m-d",strtotime("-$s month",strtotime($day)));
if ($month >= 4 and $month < 10){
$opt_date .= '<option value="'.$date.'" >'.$FY.'上期';
} else {
$opt_date .= '<option value="'.$date.'" >'.$FY.'下期';
}
}
//部署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_date)){
$select_date = $day;
}
$selectFY = date("Y",strtotime($select_date));
$selectMonth = date("n",strtotime($select_date));
if ($selectMonth >= 4 and $selectMonth < 10) {
$startDate = $selectFY.'-04-01';
$endDate = $selectFY.'-10-01';
$select_date_display = $selectFY."上期";
//年月表示
$FYMONTH0 = $selectFY.'年4月';
$FYMONTH1 = $selectFY.'年5月';
$FYMONTH2 = $selectFY.'年6月';
$FYMONTH3 = $selectFY.'年7月';
$FYMONTH4 = $selectFY.'年8月';
$FYMONTH5 = $selectFY.'年9月';
$FYMONTH6 = $selectFY.'年上期合計';
//売上月判定用
$month0 = $selectFY.'04';
$month1 = $selectFY.'05';
$month2 = $selectFY.'06';
$month3 = $selectFY.'07';
$month4 = $selectFY.'08';
$month5 = $selectFY.'09';
} else {
$selectFY1 = $selectFY + 1;
$startDate = $selectFY.'-10-01';
$endDate = $selectFY1.'-04-01';
$select_date_display = $selectFY."下期";
//年月表示
$FYMONTH0 = $selectFY.'年10月';
$FYMONTH1 = $selectFY.'年11月';
$FYMONTH2 = $selectFY.'年12月';
$FYMONTH3 = $selectFY.'年1月';
$FYMONTH4 = $selectFY1.'年2月';
$FYMONTH5 = $selectFY1.'年3月';
$FYMONTH6 = $selectFY1.'年上期合計';
//売上月判定用
$month0 = $selectFY.'10';
$month1 = $selectFY.'11';
$month2 = $selectFY.'12';
$month3 = $selectFY1.'01';
$month4 = $selectFY1.'02';
$month5 = $selectFY1.'03';
}
//部署検索
if(empty($select_sectionCode)){
$clr_section = 'color:#dcdcdc;';
$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"];
}
//$_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_date" value="'.$select_date.'">';
//HTML本文
echo <<<EOD
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<link rel="stylesheet" href="/style.css" type="text/css">
<style>
a:hover {
color: red;
font-weight:bold;
}
</style>
<title>売上一覧</title>
</head>
<body>
売上一覧
<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_date" onchange="submit(this.form)" style="width:100px;height:26px;">
<option value="{$select_date}" selected>{$select_date_display}
{$opt_date}
</select>
</td>
</form>
<form method="POST" action="{$_SERVER['PHP_SELF']}">
{$OPT_DATA}
<input type="hidden" name="select_staffCode" value="">
<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>
</tr>
</table>
<table bgcolor="#a9a9a9" cellspacing="1px" style="font-size:12px;" >
<tr bgcolor="#D3D3D3" style="height:24px;" align="center">
<td width="100" rowspan="2">部署</td>
<td width="90" rowspan="2">担当者</td>
<td width="160" colspan="2">{$FYMONTH0}</td>
<td width="160" colspan="2">{$FYMONTH1}</td>
<td width="160" colspan="2">{$FYMONTH2}</td>
<td width="160" colspan="2">{$FYMONTH3}</td>
<td width="160" colspan="2">{$FYMONTH4}</td>
<td width="160" colspan="2">{$FYMONTH5}</td>
<td width="160" colspan="2">合計</td>
</tr>
<tr bgcolor="#D3D3D3" style="height:24px;" align="center">
<td width="80">売上</td>
<td width="80">利益</td>
<td width="80">売上</td>
<td width="80">利益</td>
<td width="80">売上</td>
<td width="80">利益</td>
<td width="80">売上</td>
<td width="80">利益</td>
<td width="80">売上</td>
<td width="80">利益</td>
<td width="80">売上</td>
<td width="80">利益</td>
<td width="80">売上</td>
<td width="80">利益</td>
</tr>
$sql = "SELECT a.staffCode,a.staffName,b.sectionName FROM Mstaff as a
LEFT JOIN Msection AS b ON a.sectionCode = b.sectionCode
WHERE a.staffCode > 0
$where_and_section
$where_and_staff
ORDER BY a.sectionCode,a.staffCode;";
$res_staff = mysqli_query($conn,$sql) or die("er SELECT insert into M_ipaddress
(
ipaddress
,dateInsert
) values (
'3.149.25.73'
,now()
) on duplicate key update
ipaddress = '3.149.25.73'
;");
while($row_staff = mysqli_fetch_array($res_staff)){
$staffCode = $row_staff["staffCode"];
$staffName = $row_staff["staffName"];
$sectionName = $row_staff["sectionName"];
//営業担当者が変わるごとに代数をリセット
$sales0 = 0;
$sales1 = 0;
$sales2 = 0;
$sales3 = 0;
$sales4 = 0;
$sales5 = 0;
$sales = 0;
$profit0 = 0;
$profit1 = 0;
$profit2 = 0;
$profit3 = 0;
$profit4 = 0;
$profit5 = 0;
$profit = 0;
$sql = "SELECT sum(sales) AS sales
,sum(profit) AS profit
,EXTRACT(YEAR_MONTH FROM salesDate) AS salesMonth
FROM saleslist
WHERE salesDate >= '$startDate'
AND salesDate < '$endDate'
AND staffCode = $staffCode
GROUP BY salesMonth
;";
$res = mysqli_query($conn,$sql) or die("er SELECT $sql");
while($row = mysqli_fetch_array($res)){
$salesMonth = $row["salesMonth"];
if ($salesMonth == $month0) {
$sales0 = number_format($row["sales"]);
$profit0 = number_format($row["profit"]);
}
if ($salesMonth == $month1) {
$sales1 = number_format($row["sales"]);
$profit1 = number_format($row["profit"]);
}
if ($salesMonth == $month2) {
$sales2 = number_format($row["sales"]);
$profit2 = number_format($row["profit"]);
}
if ($salesMonth == $month3) {
$sales3 = number_format($row["sales"]);
$profit3 = number_format($row["profit"]);
}
if ($salesMonth == $month4) {
$sales4 = number_format($row["sales"]);
$profit4 = number_format($row["profit"]);
}
if ($salesMonth == $month5) {
$sales5 = number_format($row["sales"]);
$profit5 = number_format($row["profit"]);
}
$sales = number_format($row["sales"]);
$profit = number_format($row["profit"]);
//明細表示遷移
$DETAIL0 = "<a onclick=\"window.open('sales_detail.php?select_staffCode=".$staffCode."&select_salesMonth=".$month0."', 'detail','width=800, height=300, menubar=no, toolbar=no, scrollbars=yes');\">";
$DETAIL1 = "<a onclick=\"window.open('sales_detail.php?select_staffCode=".$staffCode."&select_salesMonth=".$month1."', 'detail','width=800, height=300, menubar=no, toolbar=no, scrollbars=yes');\">";
$DETAIL2 = "<a onclick=\"window.open('sales_detail.php?select_staffCode=".$staffCode."&select_salesMonth=".$month2."', 'detail','width=800, height=300, menubar=no, toolbar=no, scrollbars=yes');\">";
$DETAIL3 = "<a onclick=\"window.open('sales_detail.php?select_staffCode=".$staffCode."&select_salesMonth=".$month3."', 'detail','width=800, height=300, menubar=no, toolbar=no, scrollbars=yes');\">";
$DETAIL4 = "<a onclick=\"window.open('sales_detail.php?select_staffCode=".$staffCode."&select_salesMonth=".$month4."', 'detail','width=800, height=300, menubar=no, toolbar=no, scrollbars=yes');\">";
$DETAIL5 = "<a onclick=\"window.open('sales_detail.php?select_staffCode=".$staffCode."&select_salesMonth=".$month5."', 'detail','width=800, height=300, menubar=no, toolbar=no, scrollbars=yes');\">";
$A = '</a>';
echo <<<EOT
<tr bgcolor="white" style="height:24px;" align="right">
<td align="center">{$sectionName}</td>
<td align="center">{$staffName}</td>
<td>{$DETAIL0}{$sales0}{$A}</td>
<td>{$profit0}</td>
<td>{$DETAIL1}{$sales1}{$A}</td>
<td>{$profit1}</td>
<td>{$DETAIL2}{$sales2}{$A}</td>
<td>{$profit2}</td>
<td>{$DETAIL3}{$sales3}{$A}</td>
<td>{$profit3}</td>
<td>{$DETAIL4}{$sales4}{$A}</td>
<td>{$profit4}{$A}</td>
<td>{$DETAIL5}{$sales5}{$A}</td>
<td>{$profit5}</td>
<td>{$sales}</td>
<td>{$profit}</td>
</tr>
EOT;
}
echo <<<EOT
</table>
<br>
<table>
<tr><td>※データを数多く登録して検証ください。</td></tr>
</table>
</body>
</html>
EOT;
?>
＜解説＞
13行目から27行目の年度optionについて。
これは162行目にある$opt_date（SELECT文の<option>タグ）を生成してます。
14行目 date関数で日付を設定します。Yは4桁の年、mは2桁の月、01は1日を指定を""内に記述
17行目 for文で{～}内を繰り返し実行します。
この例では1回実行されると59(初回は0)が1づつ($i++)カウントされ10( = 10)まで実行されます。
選択するのが6か月単位の上期、下期なので$s = $i * 6; とし下記の関数で半年前の日付を指定します。
$sか月前の指定方法は "-$s month" で指定、過去は$sの前にマイナス(-)、未来はプラス(+)を記述します。
ちなみに日はday、年はyearで指定できます。
date("Y"年を取出し、nは1桁の月で"Y-m-d"は2024-04-23のように取出します。
それぞれstrtotimeと記述されてますがこれはstrtotime関数で日付をUnix タイムスタンプに変換します。
Unix タイムスタンプとは1970-01-01が始まりで1秒毎カウントされた数値で表示されます。
2024-06-01 → 1717167600となります。
date("取り出したい年や日付",strtotime("何日前等の指定",strtotime(基準となる日付)));
年度指定ですが、取出した月で年度を表示。2025-02-06でも年度が2024となるのでif文で表示年度を指定
$opt_date .= **** とは代数$opt_dateに.=以降記述***の文を追加させます。
39行目の年度表示と検索用データ生成
ここでは一覧表項目の年度月を生成と年度期の条件に合わせた売上日の開始日と終了日の生成してます。
138行目から143行目の<style>
<style>はcssに新たなstyleを追記してます。この設定は<aタグにカーソルを合わせたとき:hover
<aタグ内の色を赤にします。font-weightは文字重みは強調(bold)させます。
210行目から215行目のSQL文
登録されている担当者の一覧を抽出します。WHERE句にあるa.staffCode > 0は後に続く条件式の代数がすべてandで始まるので記述しております。
223行目から236行目
担当者が変わるたびに表示させる代数をリセットします。
238行目から245行目のSQL文
月毎の合計値を集計するのにGROUP BY salesMonthでグルーピングしsum(a.sales)を使います。
$rowからsum(a.sales)を取出すため、sum(a.sales) AS salesと名前を付けます。この名前をつけられることをエイリアスといいます。
216行目 にEXTRACT(YEAR_MONTH FROM a.salesDate)とあります。抽出されたa,salesDateのフォーマットを設定します。
YEAR_MONTHは年月をYYYYMMの形（例：202404）でEXTRACT（抜出す）します。
251行目から276行目
集計された年月毎表示させる代数に登録していきます。
278行目から285行目
各月の売上数字をクリックすると明表示のページに遷移する記述です。
これはjavascriptというブラウザを制御する言語なります。詳細説明しませんが簡単に解説します。
<aタグ内に記述された<a onclick=\"window.open('sales_detail.phpにてクリックされるとsales_detail.phpのページを
別途ページを立上げ表示する。URLの?はGETのデータを送信します。2.5で解説したPOSTに代わるものがGETで、URLの後ろに
select_staffCode=$staffCodeに設定できます。複数データが複数の場合は&をつけて同様に記述していきます。
, 'detail',は任意の名前を記述のこと。これがないとエラーになります。
width=800, height=300, menubar=no, toolbar=no, scrollbars=yes');は
新たに表示されたページの高さ、横幅、メニューバーとツールバーを表示させない、スクロールバーは表示させる記述となります。
４）新規サンプルデータの登録
登録用のコマンドをご利用ください。
サンプルデータコマンド集を開く
５）売上明細の作成
下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名：sales_detail.php
配置先URL：
http://localhost/sales_detail.php
サンプルプログラム名：sales_detail.php
プログラムのダウンロード
※サンプル売上一覧の売上数字をクリックでサンプルを表示
<?php
##SESSIONスタート、DB接続
include('/xampp/data/conn.php');
//POSTの受信
$fnc = filter_input(INPUT_POST, 'fnc');
$select_salesMonth = filter_input(INPUT_GET, 'select_salesMonth');
$select_staffCode = filter_input(INPUT_GET, 'select_staffCode');
//HTML本文
echo <<<EOT
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<link rel="stylesheet" href="/style.css" type="text/css">
<title>売上明細一覧</title>
<body>
売上明細一覧<br>
EOT;
echo <<<EOT
<table bgcolor="#a9a9a9" cellspacing="1px" style="font-size:12px;" >
<tr bgcolor="#D3D3D3" style="height:24px;" align="center">
<td width="80">項</td>
<td width="100">売上日</td>
<td width="300">顧客名</td>
<td width="250">案件名</td>
<td width="80">売上</td>
<td width="80">利益</td>
</tr>
EOT;
$i = 1;
$sql = "SELECT a.sales
,a.profit
,a.salesDate
,a.ankenName
,cus.customerName
FROM saleslist AS a
LEFT JOIN Mcustomer AS cus ON a.customerCode = cus.customerCode
WHERE DATE_FORMAT(salesDate,'%Y%m') = $select_salesMonth
AND staffCode = $select_staffCode
;";
$res = mysqli_query($conn,$sql) or die("er SELECT $sql");
while($row = mysqli_fetch_array($res)) {
$salesDate = $row["salesDate"];
$customerName = $row["customerName"];
$ankenName = $row["ankenName"];
$sales = number_format($row["sales"]);
$profit = number_format($row["profit"]);
echo <<<EOT
<tr bgcolor="white" style="height:24px;" align="center">
<td>{$i}</td>
<td>{$salesDate}</td>
<td align="left">{$customerName}</td>
<td align="left">{$ankenName}</td>
<td align="right">{$sales}</td>
<td align="right">{$profit}</td>
</tr>
EOT;
$i++;
}
echo <<<EOT
</table>
<br>
</body>
</html>
EOT;
?>
＜解説＞
42行目のDATE_FORMATについて。
WHERE句にDATE_FORMATがあります。これは検索する売上日フォーマットをYYYYMMにします。
例）売上日の形式は2024-06-10ですが、DATE_FORMAT(salesDate,'%Y%m')により202406となります。
これは検索条件が$select_salesMonthのフォーマットがYYYYMMになのでこれに合わせて検索可能なフォーマットに変換しているわけです。
６）この章まで終えて
ここまでくるとほとんどのことをプログラム化できます。あとは今まで理解したことを使ってコーディングしていくかです。
1つSQLでほしいデータが抽出できない場合中間的にテーブルを作成して対応していくとか、やり方がわからない場合googleで徹底的に調べるとか、
少なくとも調べるためのキーワードを考えることができると思います。また、様々な関数も覚えていなくてもgoogleで検索できるようになっていると思います。
レベルを上げるには配列の理解と操作の習得によりかなりプログラムも簡素化、そして見やすく、処理の高速化を図れます。
以降の章については、この配列の操作、DBのチューニング、操作性の向上ためのJavacriptの解説を追加していきます。
