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

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

TOP >初心者向けPHP・データベース入門 >4.1月毎売上集計一覧の作成

月毎売上集計一覧の作成

 2024-06-02 (更新日:2024-06-09)

<学習する内容>

 WHERE句にEXTRACT(YEAR_MONTH FROM workdate)関数を使って月毎の売上集計を作成します。

1)売上テーブルの作成 (テーブル名: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);

2)抽出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;

<解説>
EXTRACT(YEAR_MONTH FROM salesDate) 日付関数がポイントとなります。
この関数は、日付データを年と月のデータを抽出します。
例えば、2024-04-16 → 202404になります。
この関数はsalesMonthというエイリアスを設定しているのでそのままGROUP BY salesMonthと使えます。 よって、2024-04-07の売上や2024-4-24といった売上もこの202404として判定されこの年月のGROUP BYでsum(***)合計集計されます。

3)売上一覧の作成

下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名:saleslist.php
配置先:c:\xampp\htdocs\
配置先URL:http://localhost/saleslist.php
サンプルプログラム名: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.226.72.194'
					,now()
				) on duplicate key update
					ipaddress = '3.226.72.194'		
			;");
							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');は 新たに表示されたページの高さ、横幅、メニューバーとツールバーを表示させない、スクロールバーは表示させる記述となります。

4)新規サンプルデータの登録

登録用のコマンドをご利用ください。  

5)売上明細の作成

下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名:sales_detail.php
配置先:c:\xampp\htdocs\
配置先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になのでこれに合わせて検索可能なフォーマットに変換しているわけです。

6)この章まで終えて

 ここまでくるとほとんどのことをプログラム化できます。あとは今まで理解したことを使ってコーディングしていくかです。 1つSQLでほしいデータが抽出できない場合中間的にテーブルを作成して対応していくとか、やり方がわからない場合googleで徹底的に調べるとか、 少なくとも調べるためのキーワードを考えることができると思います。また、様々な関数も覚えていなくてもgoogleで検索できるようになっていると思います。
 レベルを上げるには配列の理解と操作の習得によりかなりプログラムも簡素化、そして見やすく、処理の高速化を図れます。 以降の章については、この配列の操作、DBのチューニング、操作性の向上ためのJavacriptの解説を追加していきます。