AST ALT などの項目検査数を月ごとに集計します。
sqlite3 では月ごとの集計ができないので、sqlite 3 を mysql に書き換えてから集計します。
クエリ
2021 年に施行された検査を項目別に、月ごとに集計します。
クエリは以下のようになっています。
SELECT
DATE_FORMAT(hizuke, '%Y-%m') as hizuke, koumokuNo, koumokuName,COUNT(*) as count
FROM
kekka
WHERE
'2020-12-31' < hizuke AND hizuke < '2022-01-01'
GROUP BY
DATE_FORMAT(hizuke, '%Y-%m'), koumokuNo, koumokuName
ORDER BY
hizuke, koumokuNo ;
php プログラム
<?php
class stats_koumoku{
private $alldata ;
private $allkoumoku ;
private $tukiArr ;
private $koumokuNoArr ;
private $koumokuNamearr ;
private $tableData;
public function __construct()
{
require_once('../../dbinfo/mysqlinfo.php') ;
$this -> alldata = array();
$this -> allkoumoku = array();
$this -> tukiArr = array();
$this -> koumokuNoArr = array();
$this -> koumokuNameArr = array();
$this -> tableData = array();
}
public function readData()
{
$alldata = $this -> alldata ;
try{
$dsn = 'mysql:host='.host.';dbname='.dbname.';charset=utf8';
$pdo = new PDO ($dsn, username, password ,array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$sql = "SELECT
DATE_FORMAT(hizuke, '%Y-%m') as hizuke, koumokuNo, koumokuName,COUNT(*) as count
FROM kekka
WHERE '2020-12-31' < hizuke AND hizuke < '2022-01-01'
GROUP BY DATE_FORMAT(hizuke, '%Y-%m'), koumokuNo, koumokuName
ORDER BY hizuke, koumokuNo ;";
$stmt = $pdo->prepare($sql);
$stmt->execute();
while($res = $stmt->fetch(PDO::FETCH_ASSOC)){
$hizuke = $res['hizuke'] ;
$allData[] = array( $res['hizuke'], $res['koumokuNo'], $res['koumokuName'], $res['count'] );
}
$this -> allkoumoku = $allData ;
}catch (PDOException $e){
echo('Error:'.$e->getMessage());
die();
}
}
public function tkArr()
{
$allkoumoku = $this -> allkoumoku ;
$tukiArr = $this -> tukiArr ;
$koumokuNoArr = $this -> koumokuNoArr ;
foreach( $allkoumoku as $eachline )
{
if( !in_array( $eachline[0], $tukiArr ) ){
$tukiArr[] = $eachline[0] ;
}
if( !in_array( $eachline[1], $koumokuNoArr ) ){
$koumokuNoArr[] = $eachline[1] ;
}
}
$this -> tukiArr = $tukiArr ;
$this -> koumokuNoArr = $koumokuNoArr ;
}
public function initTbl()
{
$tableData = $this -> tableData ;
$tukiArr = $this -> tukiArr ;
$koumokuNoArr = $this -> koumokuNoArr ;
$gyo = count($koumokuNoArr) ;
$retu = count($tukiArr) ;
for( $i=0; $i < $gyo; $i++) {
for( $j=0; $j < $retu; $j++) {
$tableData[$i][$j] = null ;
}
}
$this -> tableData = $tableData ;
}
public function createSheet()
{
$allkoumoku = $this -> allkoumoku ;
$tukiArr = $this -> tukiArr ;
$koumokuNoArr = $this -> koumokuNoArr ;
$tableData = $this -> tableData ;
foreach( $allkoumoku as $eachline ){
$tuki = $eachline[0] ;
$koumokuNo = $eachline[1] ;
$eachData = $eachline[3] ;
$xkey = array_search( $tuki, $tukiArr );
$ykey = array_search( $koumokuNo, $koumokuNoArr );
$tableData[$ykey][$xkey] = $eachData ;
}
$this -> tableData = $tableData ;
}
public function koumokuName()
{
$koumokuNameArr = $this -> koumokuNameArr ;
try{
$dsn = 'mysql:host=localhost;dbname=stats_lapis;charset=utf8';
$pdo = new PDO ($dsn, username, password ,array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$sql = "SELECT koumokuNo, koumokuName FROM koumokuMST ;";
$stmt = $pdo->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$koumokuNameArr[] = array( $row['koumokuNo'], $row['koumokuName'] ) ;
}
$this -> koumokuNameArr = $koumokuNameArr ;
}catch (PDOException $e){
echo('Error:'.$e->getMessage());
die();
}
}
public function createTable()
{
$tableData = $this -> tableData ;
$tukiArr = $this -> tukiArr ;
$koumokuNoArr = $this -> koumokuNoArr ;
$koumokuNameArr = $this -> koumokuNameArr ;
$lineNum = 0 ;
$tbl = "<table class='datashow'>\n";
//----------------- 一番上の行を追加 -- 年付きの行
array_unshift( $tukiArr, "項目" ); // 項目のカラムを追加
$header = "" ;
foreach( $tukiArr as $key => $tuki )
{
if( $key === 0 )
{
$eh = $tuki ;
}else{
$eh = date( "Y-m", strtotime($tuki)) ;
}
$header .= "<th>$eh</th>" ;
}
$tbl .= "<tr>{$header}</tr>\n" ;
//------------------ 各データ行を追加
foreach( $tableData as $rkey => $eachline )
{
$searchkey = array_search( $koumokuNoArr[$lineNum], array_column( $koumokuNameArr, 0));
$koumokuName = $koumokuNameArr[$searchkey][1] ;
$datas = "" ;
foreach( $eachline as $ckey => $val )
{
$datas .= "<td class='value'>$val</td>";
}
$tbl .= "<tr><th class='koumoku'>$koumokuName</th>$datas</tr>\n" ;
$lineNum++ ;
}
$tbl .= "</table>" ;
echo "<div class='container'>{$tbl}</div>" ;
}
}
//--------------------------------
$koumokust = new stats_koumoku() ;
$koumokust -> readData() ;
$koumokust -> tkArr() ;
$koumokust -> koumokuName() ;
$koumokust -> initTbl() ;
$koumokust -> createSheet() ;
$koumokust -> createTable() ;
?>
実行結果
実行すると以下のようになります。
実行時間は 3 秒程度。
外来・入院を区別する
検査科の希望では外来と入院を区別して欲しいとのこと。
ただ、外来・入院の区別は検査結果のテーブルにはなく、検査リストのテーブルにあるためそれら 2 つのテーブルをリレーションする必要があります。
リレーションすると集計にとても時間がかかるので、他の方法はないかいろいろと探したのですが、どうもダメで、結局リレーションで集計することにしました。
クエリは、
SELECT
DATE_FORMAT(kekka.hizuke, '%Y-%m') AS hizuke, koumokuNo, koumokuName, Iraika, COUNT(*) AS count
FROM
kekka
INNER JOIN
kensa
ON
kekka.hizuke = kensa.hizuke AND kekka.uketukeNo = kensa.uketukeNo
WHERE
? < kekka.hizuke AND kekka.hizuke < ? AND ( Iraika = '1' OR Iraika = '2' )
GROUP BY
DATE_FORMAT(hizuke, '%Y-%m'), koumokuNo, koumokuName, Iraika
ORDER BY
hizuke, koumokuNo, Iraika ;
実際のプログラムは、
<?php
class stats_koumoku{
private $allkoumoku ;
private $tukiArr ;
private $koumokuNoArr ;
private $koumokuNamearr ;
private $tableData;
public function __construct()
{
require_once('../../dbinfo/mysqlinfo.php') ;
$this -> allkoumoku = array();
$this -> tukiArr = array();
$this -> koumokuNoArr = array();
$this -> koumokuNameArr = array();
$this -> tableData = array();
}
public function readData( $startdate, $enddate )
{
$alldata = [] ;
try{
$dsn = 'mysql:host=localhost;dbname=stats_lapis;charset=utf8';
$pdo = new PDO ($dsn, username, password ,array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$sql = "
SELECT
DATE_FORMAT(kekka.hizuke, '%Y-%m') AS hizuke, koumokuNo, koumokuName, Iraika, COUNT(*) AS count
FROM
kekka
INNER JOIN
kensa
ON
kekka.hizuke = kensa.hizuke AND kekka.uketukeNo = kensa.uketukeNo
WHERE
? < kekka.hizuke AND kekka.hizuke < ? AND ( Iraika = '1' OR Iraika = '2' )
GROUP BY
DATE_FORMAT(hizuke, '%Y-%m'), koumokuNo, koumokuName, Iraika
ORDER BY
hizuke, koumokuNo, Iraika ;
";
$stmt = $pdo -> prepare( $sql );
$stmt->execute( array( $startdate, $enddate ) );
while($res = $stmt->fetch(PDO::FETCH_ASSOC))
{
$hizuke = $res['hizuke'] ;
$allData[] = array( $res['hizuke'], $res['koumokuNo'], $res['koumokuName'], $res['Iraika'], $res['count'] );
}
$this -> allkoumoku = $allData ;
}catch (PDOException $e){
echo('Error:'.$e->getMessage());
die();
}
}
public function tkArr()
{
$allkoumoku = $this -> allkoumoku ;
$tukiArr = $this -> tukiArr ;
$koumokuNoArr = $this -> koumokuNoArr ;
foreach( $allkoumoku as $eachline )
{
if( !in_array( $eachline[0], $tukiArr ) )
{
$tukiArr[] = $eachline[0] ;
}
if( !in_array( $eachline[1], $koumokuNoArr ) )
{
$koumokuNoArr[] = $eachline[1] ;
}
}
$this -> tukiArr = $tukiArr ;
$this -> koumokuNoArr = $koumokuNoArr ;
}
public function initTbl() // 最終的なテーブルを初期化しておく
{
$tableData = $this -> tableData ;
$tukiArr = $this -> tukiArr ;
$koumokuNoArr = $this -> koumokuNoArr ;
$gyo = count($koumokuNoArr) ;
$retu = count($tukiArr) * 2 ;
for( $i=0; $i < $gyo; $i++)
{
for( $j=0; $j < $retu; $j++)
{
$tableData[$i][$j] = null ;
}
}
$this -> tableData = $tableData ;
}
public function createSheet()
{
$allkoumoku = $this -> allkoumoku ;
$tukiArr = $this -> tukiArr ;
$koumokuNoArr = $this -> koumokuNoArr ;
$tableData = $this -> tableData ;
foreach( $allkoumoku as $eachline )
{
$tuki = $eachline[0] ;
$koumokuNo = $eachline[1] ;
$iraika = $eachline[3] ;
$eachData = $eachline[4] ;
$ykey = array_search( $koumokuNo, $koumokuNoArr );
if( $iraika === '1')
{
$xkey = array_search( $tuki, $tukiArr ) * 2 ;
}else{
$xkey = array_search( $tuki, $tukiArr ) * 2 + 1 ;
}
$tableData[$ykey][$xkey] = $eachData ;
}
$this -> tableData = $tableData ;
}
public function koumokuName()
{
$koumokuNameArr = $this -> koumokuNameArr ;
try{
$dsn = 'mysql:host=localhost;dbname=stats_lapis;charset=utf8';
$pdo = new PDO ($dsn, username, password ,array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$sql = "SELECT koumokuNo, koumokuName FROM koumokuMST ;";
$stmt = $pdo->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
$koumokuNameArr[] = array( $row['koumokuNo'], $row['koumokuName'] ) ;
}
$this -> koumokuNameArr = $koumokuNameArr ;
}catch (PDOException $e){
echo('Error:'.$e->getMessage());
die();
}
}
public function createTable()
{
$tableData = $this -> tableData ;
$tukiArr = $this -> tukiArr ;
$koumokuNoArr = $this -> koumokuNoArr ;
$koumokuNameArr = $this -> koumokuNameArr ;
$lineNum = 0 ;
$tbl = "<table class='datashow'>\n";
//----------------- 一番上の行を追加 -- 日付の行
$header = "<th rowspan='2'>項目</th>" ;
$subheader = "" ;
foreach( $tukiArr as $key => $tuki )
{
if( $key === 0 )
{
$eh = $tuki ;
}else{
$eh = date( "Y-m", strtotime($tuki)) ;
}
$header .= "<th colspan='2'>$eh</th>" ;
for( $i = 0 ; $i < 2 ; $i++ )
{
if( $i % 2 === 0 )
{
$subheader .= "<td class='nyugai'>入院</td>" ;
}else{
$subheader .= "<td class='nyugai'>外来</td>" ;
}
}
}
$tbl .= "<tr>{$header}</tr><br>$subheader\n" ;
//------------------ 各データ行を追加
foreach( $tableData as $rkey => $eachline )
{
$searchkey = array_search( $koumokuNoArr[$lineNum], array_column( $koumokuNameArr, 0));
$koumokuName = $koumokuNameArr[$searchkey][1] ;
$datas = "" ;
foreach( $eachline as $ckey => $val )
{
$datas .= "<td class='val'>$val</td>";
}
$tbl .= "<tr><th class='koumoku'>$koumokuName</th>$datas</tr>\n" ;
$lineNum++ ;
}
$tbl .= "</table>" ;
echo "<div class='container'>{$tbl}</div>" ;
}
}
//----------------------------------------------
$year = $_POST['year'] ;
$newyeardate = $year."-01-01" ;
$startdate = date('Y-m-d', strtotime('-1 day', strtotime( $newyeardate )));
$enddate = date('Y-m-d', strtotime('+1 year', strtotime( $newyeardate )));
$koumokust = new stats_koumoku() ;
$koumokust -> readData( $startdate, $enddate ) ;
$koumokust -> tkArr() ;
$koumokust -> koumokuName() ;
$koumokust -> initTbl() ;
$koumokust -> createSheet() ;
$koumokust -> createTable() ;
このプログラムに「2019」のような西暦年を外から与えます。
実行すると、
実行時間は、20 - 30 秒です。
