include/db/analyticsDb.php

   1 <?php
   2 /**
   3  * DBクラス
   4  *
   5  * PHP versions 5
   6  *
   7  * LICENSE: This source file is licensed under the terms of the GNU General Public License.
   8  *
   9  * @package    Magic3 Framework
  10  * @author     平田直毅(Naoki Hirata) <naoki@aplo.co.jp>
  11  * @copyright  Copyright 2006-2011 Magic3 Project.
  12  * @license    http://www.gnu.org/copyleft/gpl.html  GPL License
  13  * @version    SVN: $Id: analyticsDb.php 4908 2012-05-17 12:44:08Z fishbone $
  14  * @link       http://www.magic3.org
  15  */
  16 require_once(M3_SYSTEM_INCLUDE_PATH . '/db/baseDb.php');
  17
  18 class analyticsDb extends BaseDb
  19 {
  20         const MAX_URL_LENGTH = 180;                                     // URLの長さ最大値
  21
  22         /**
  23          * サイト解析状況を取得
  24          *
  25          * @param string $key           キーとなる項目値
  26          * @return string $value        値
  27          */
  28         function getStatus($key)
  29         {
  30                 $retValue = '';
  31                 $queryStr  = 'SELECT as_value FROM _analyze_status ';
  32                 $queryStr .=   'WHERE as_id  = ?';
  33                 $ret = $this->selectRecord($queryStr, array($key), $row);
  34                 if ($ret) $retValue = $row['as_value'];
  35                 return $retValue;
  36         }
  37         /**
  38          * サイト解析状況を更新
  39          *
  40          * @param string $key           キーとなる項目値
  41          * @param string $value         値
  42          * @return                                      true = 正常、false=異常
  43          */
  44         function updateStatus($key, $value)
  45         {
  46                 $now = date("Y/m/d H:i:s");     // 現在日時
  47
  48                 // トランザクションスタート
  49                 $startTran = false;                     // この関数でトランザクションを開始したかどうか
  50                 if (!$this->isInTransaction()){
  51                         $this->startTransaction();
  52                         $startTran = true;
  53                 }
  54
  55                 $queryStr  = 'SELECT as_value FROM _analyze_status ';
  56                 $queryStr .=   'WHERE as_id = ? ';
  57                 $ret = $this->selectRecord($queryStr, array($key), $row);
  58                 if ($ret){
  59                         $queryStr  = 'UPDATE _analyze_status ';
  60                         $queryStr .=   'SET as_value = ?, ';
  61                         $queryStr .=     'as_update_dt = ? ';
  62                         $queryStr .=   'WHERE as_id = ? ';
  63                         $ret = $this->execStatement($queryStr, array($value, $now, $key));
  64                 } else {
  65                         $queryStr  = 'INSERT INTO _analyze_status (';
  66                         $queryStr .=   'as_id, ';
  67                         $queryStr .=   'as_value, ';
  68                         $queryStr .=   'as_update_dt ';
  69                         $queryStr .= ') VALUES (';
  70                         $queryStr .=   '?, ?, ?';
  71                         $queryStr .= ')';
  72                         $ret = $this->execStatement($queryStr, array($key, $value, $now));
  73                 }
  74                 // トランザクション確定
  75                 if ($startTran) $ret = $this->endTransaction();
  76                 return $ret;
  77         }
  78         /**
  79          * 最も古いアクセスログを取得
  80          *
  81          * @param array         $row            取得レコード
  82          * @param bool                                  true=成功、false=失敗
  83          */
  84         function getOldAccessLog(&$row)
  85         {
  86                 $queryStr  = 'SELECT * FROM _access_log ';
  87                 $queryStr .=   'ORDER BY al_serial';
  88                 $ret = $this->selectRecord($queryStr, array(), $row);
  89                 return $ret;
  90         }
  91         /**
  92          * 時間単位で一日分の集計処理を行う
  93          *
  94          * @param date          $date           集計する日付
  95          * @param bool                                  true=成功、false=失敗
  96          */
  97         public function calcDatePv($date)
  98         {
  99                 // 一旦データをすべて削除
 100                 $queryStr  = 'DELETE FROM _analyze_page_view ';
 101                 $queryStr .=   'WHERE ap_date = ? ';
 102                 $ret = $this->execStatement($queryStr, array($date));
 103                 if (!$ret) return false;
 104                 $queryStr  = 'DELETE FROM _analyze_daily_count ';
 105                 $queryStr .=   'WHERE aa_date = ? ';
 106                 $ret = $this->execStatement($queryStr, array($date));
 107                 if (!$ret) return false;
 108
 109                 // ##### 時間単位で集計 #####
 110                 for ($i = 0; $i < 24; $i++){
 111                         // 時間範囲
 112                         $startDt = $date . ' ' . $i . ':0:0';
 113                         if ($i < 23){
 114                                 $endDt = $date . ' ' . ($i + 1) . ':0:0';
 115                         } else {
 116                                 $endDt = date("Y/m/d", strtotime("$date 1 day")) . ' 0:0:0';            // 翌日
 117                         }
 118
 119                         $params = array();
 120                         $queryStr  = 'SELECT COUNT(*) AS total,al_uri,al_path FROM _access_log ';
 121                         $queryStr .=   'WHERE (? <= al_dt AND al_dt < ?) ';
 122                         $params[] = $startDt;
 123                         $params[] = $endDt;
 124                         //$queryStr .=  'GROUP BY al_uri ';
 125                         $queryStr .=  'GROUP BY al_uri, al_path ';                      // 2011/6/2 PostgreSQL9対応
 126                         $queryStr .=  'ORDER BY total DESC';
 127                         $ret = $this->selectRecords($queryStr, $params, $rows);
 128
 129                         // 集計データを登録
 130                         if ($ret){                      // データありのとき
 131                                 for ($j = 0; $j < count($rows); $j++){
 132                                         $total = $rows[$j]["total"];
 133                                         $path = $rows[$j]["al_path"];
 134
 135                                         // URLの長さのチェック
 136                                         $rowUpdated = false;            // 更新したかどうか
 137                                         $url = $this->makeTruncStr($rows[$j]["al_uri"], self::MAX_URL_LENGTH);
 138                                         if ($url != $rows[$j]["al_uri"]){               // URLが長いときは省略形で登録
 139                                                 $queryStr  = 'SELECT * FROM _analyze_page_view ';
 140                                                 $queryStr .=   'WHERE ap_type = ? ';
 141                                                 $queryStr .=     'AND ap_url = ? ';
 142                                                 $queryStr .=     'AND ap_date = ? ';
 143                                                 $queryStr .=     'AND ap_hour = ?';
 144                                                 $ret = $this->selectRecord($queryStr, array(0/*すべてのデータ*/, $url, $date, $i), $row);
 145                                                 if ($ret){
 146                                                         $serial = $row['ap_serial'];
 147                                                         $count = $row['ap_count'] + $total;
 148
 149                                                         $queryStr  = 'UPDATE _analyze_page_view ';
 150                                                         $queryStr .=   'SET ap_count = ? ';
 151                                                         $queryStr .=   'WHERE ap_serial = ? ';
 152                                                         $ret = $this->execStatement($queryStr, array($count, $serial));
 153                                                         if (!$ret) return false;                // エラー発生
 154
 155                                                         $rowUpdated = true;             // 更新したかどうか
 156                                                 }
 157                                         }
 158                                         if (!$rowUpdated){                      // データ更新していないとき
 159                                                 $queryStr  = 'INSERT INTO _analyze_page_view (';
 160                                                 $queryStr .=   'ap_type, ';
 161                                                 $queryStr .=   'ap_url, ';
 162                                                 $queryStr .=   'ap_date, ';
 163                                                 $queryStr .=   'ap_hour, ';
 164                                                 $queryStr .=   'ap_count, ';
 165                                                 $queryStr .=   'ap_path ';
 166                                                 $queryStr .= ') VALUES (';
 167                                                 $queryStr .=   '?, ?, ?, ?, ?, ?';
 168                                                 $queryStr .= ')';
 169                                                 $ret = $this->execStatement($queryStr, array(0/*すべてのデータ*/, $url, $date, $i, $total, $path));
 170                                                 if (!$ret) return false;                // エラー発生
 171                                         }
 172                                 }
 173                         }
 174                 }
 175
 176                 // ##### 訪問数を集計 #####
 177                 // 時間範囲
 178                 $startDt = $date . ' 0:0:0';
 179                 $endDt = date("Y/m/d", strtotime("$date 1 day")) . ' 0:0:0';            // 翌日
 180
 181                 // 一日あたりURLごとの集計
 182                 $params = array();
 183                 $queryStr  = 'SELECT COUNT(DISTINCT al_session) AS total,al_uri,al_path FROM _access_log ';
 184                 $queryStr .=   'WHERE (? <= al_dt AND al_dt < ?) ';
 185                 $params[] = $startDt;
 186                 $params[] = $endDt;
 187                 //$queryStr .=  'GROUP BY al_uri ';
 188                 $queryStr .=  'GROUP BY al_uri, al_path ';              // 2011/6/2 PostgreSQL9対応
 189                 $queryStr .=  'ORDER BY total DESC';
 190                 $ret = $this->selectRecords($queryStr, $params, $rows);
 191
 192                 // 集計データを登録
 193                 if ($ret){                      // データありのとき
 194                         for ($j = 0; $j < count($rows); $j++){
 195                                 $total = $rows[$j]["total"];
 196                                 $path = $rows[$j]["al_path"];
 197
 198                                 // URLの長さのチェック
 199                                 $rowUpdated = false;            // 更新したかどうか
 200                                 $url = $this->makeTruncStr($rows[$j]["al_uri"], self::MAX_URL_LENGTH);
 201                                 if ($url != $rows[$j]["al_uri"]){               // URLが長いときは省略形で登録
 202                                         $queryStr  = 'SELECT * FROM _analyze_daily_count ';
 203                                         $queryStr .=   'WHERE aa_type = ? ';
 204                                         $queryStr .=     'AND aa_url = ? ';
 205                                         $queryStr .=     'AND aa_date = ? ';
 206                                         $ret = $this->selectRecord($queryStr, array(0/*訪問数*/, $url, $date), $row);
 207                                         if ($ret){
 208                                                 $serial = $row['aa_serial'];
 209                                                 $count = $row['aa_count'] + $total;
 210
 211                                                 $queryStr  = 'UPDATE _analyze_daily_count ';
 212                                                 $queryStr .=   'SET aa_count = ? ';
 213                                                 $queryStr .=   'WHERE aa_serial = ? ';
 214                                                 $ret = $this->execStatement($queryStr, array($count, $serial));
 215                                                 if (!$ret) return false;                // エラー発生
 216
 217                                                 $rowUpdated = true;             // 更新したかどうか
 218                                         }
 219                                 }
 220                                 if (!$rowUpdated){                      // データ更新していないとき
 221                                         $queryStr  = 'INSERT INTO _analyze_daily_count (';
 222                                         $queryStr .=   'aa_type, ';
 223                                         $queryStr .=   'aa_url, ';
 224                                         $queryStr .=   'aa_date, ';
 225                                         $queryStr .=   'aa_count, ';
 226                                         $queryStr .=   'aa_path ';
 227                                         $queryStr .= ') VALUES (';
 228                                         $queryStr .=   '?, ?, ?, ?, ?';
 229                                         $queryStr .= ')';
 230                                         $ret = $this->execStatement($queryStr, array(0/*訪問数*/, $url, $date, $total, $path));
 231                                         if (!$ret) return false;        // エラー発生
 232                                 }
 233                         }
 234                 }
 235                 // 一日あたりアクセスポイントごとの集計
 236                 $params = array();
 237                 //$queryStr  = 'SELECT COUNT(DISTINCT al_session) AS total,al_uri,al_path FROM _access_log ';
 238                 $queryStr  = 'SELECT COUNT(DISTINCT al_session) AS total, al_path FROM _access_log ';           // 2011/6/2 PostgreSQL9対応
 239                 $queryStr .=   'WHERE (? <= al_dt AND al_dt < ?) ';
 240                 $params[] = $startDt;
 241                 $params[] = $endDt;
 242                 $queryStr .=  'GROUP BY al_path ';
 243                 $queryStr .=  'ORDER BY total DESC';
 244                 $ret = $this->selectRecords($queryStr, $params, $rows);
 245
 246                 // 集計データを登録
 247                 if ($ret){                      // データありのとき
 248                         for ($j = 0; $j < count($rows); $j++){
 249                                 $total = $rows[$j]["total"];
 250                                 $path = $rows[$j]["al_path"];
 251
 252                                 // FCKEditorからのアクセスは、アクセスポイントを使用しないので除く
 253                                 if (!empty($path)){
 254                                         $queryStr  = 'INSERT INTO _analyze_daily_count (';
 255                                         $queryStr .=   'aa_type, ';
 256                                         $queryStr .=   'aa_url, ';
 257                                         $queryStr .=   'aa_date, ';
 258                                         $queryStr .=   'aa_count, ';
 259                                         $queryStr .=   'aa_path ';
 260                                         $queryStr .= ') VALUES (';
 261                                         $queryStr .=   '?, ?, ?, ?, ?';
 262                                         $queryStr .= ')';
 263                                         $ret = $this->execStatement($queryStr, array(0/*訪問数*/, ''/*アクセスポイント指定*/, $date, $total, $path));
 264                                         if (!$ret) return false;        // エラー発生
 265                                 }
 266                         }
 267                 }
 268                 // 一日あたりすべてのアクセスポイントの集計
 269                 // MySQL v5.0.22でエラー発生(2010/12/3)
 270                 // SQLエラーメッセージ(Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause error code: 42000)
 271                 // 「GROUP BY」を付けると回避可能
 272                 $params = array();
 273                 //$queryStr  = 'SELECT COUNT(DISTINCT al_session) AS total,al_uri,al_path FROM _access_log ';           // NG
 274                 $queryStr  = 'SELECT COUNT(DISTINCT al_session) AS total FROM _access_log ';            // OK
 275                 $queryStr .=   'WHERE (? <= al_dt AND al_dt < ?) ';
 276                 $params[] = $startDt;
 277                 $params[] = $endDt;
 278                 $queryStr .=  'ORDER BY total DESC';
 279                 $ret = $this->selectRecord($queryStr, $params, $row);
 280
 281                 // 集計データを登録
 282                 if ($ret && $row["total"] > 0){                 // データありのとき
 283                         $total = $row["total"];
 284
 285                         $queryStr  = 'INSERT INTO _analyze_daily_count (';
 286                         $queryStr .=   'aa_type, ';
 287                         $queryStr .=   'aa_url, ';
 288                         $queryStr .=   'aa_date, ';
 289                         $queryStr .=   'aa_count, ';
 290                         $queryStr .=   'aa_path ';
 291                         $queryStr .= ') VALUES (';
 292                         $queryStr .=   '?, ?, ?, ?, ?';
 293                         $queryStr .= ')';
 294                         $ret = $this->execStatement($queryStr, array(0/*訪問数*/, ''/*アクセスポイント指定*/, $date, $total, ''/*すべてのアクセスポイント*/));
 295                         if (!$ret) return false;        // エラー発生
 296                 }
 297                 // ##### 訪問者数を集計 #####
 298                 // 時間範囲
 299                 $startDt = $date . ' 0:0:0';
 300                 $endDt = date("Y/m/d", strtotime("$date 1 day")) . ' 0:0:0';            // 翌日
 301
 302                 // 1日あたりURLごとの集計
 303                 $params = array();
 304                 $queryStr  = 'SELECT COUNT(DISTINCT al_cookie_value) AS total,al_uri,al_path FROM _access_log ';
 305                 $queryStr .=   'WHERE (? <= al_dt AND al_dt < ?) ';
 306                 $params[] = $startDt;
 307                 $params[] = $endDt;
 308                 //$queryStr .=  'GROUP BY al_uri ';
 309                 $queryStr .=  'GROUP BY al_uri, al_path ';                              // 2011/6/2 PostgreSQL9対応
 310                 $queryStr .=  'ORDER BY total DESC';
 311                 $ret = $this->selectRecords($queryStr, $params, $rows);
 312
 313                 // 集計データを登録
 314                 if ($ret){                      // データありのとき
 315                         for ($j = 0; $j < count($rows); $j++){
 316                                 $total = $rows[$j]["total"];
 317                                 $path = $rows[$j]["al_path"];
 318
 319                                 // URLの長さのチェック
 320                                 $rowUpdated = false;            // 更新したかどうか
 321                                 $url = $this->makeTruncStr($rows[$j]["al_uri"], self::MAX_URL_LENGTH);
 322                                 if ($url != $rows[$j]["al_uri"]){               // URLが長いときは省略形で登録
 323                                         $queryStr  = 'SELECT * FROM _analyze_daily_count ';
 324                                         $queryStr .=   'WHERE aa_type = ? ';
 325                                         $queryStr .=     'AND aa_url = ? ';
 326                                         $queryStr .=     'AND aa_date = ? ';
 327                                         $ret = $this->selectRecord($queryStr, array(1/*訪問者数*/, $url, $date), $row);
 328                                         if ($ret){
 329                                                 $serial = $row['aa_serial'];
 330                                                 $count = $row['aa_count'] + $total;
 331
 332                                                 $queryStr  = 'UPDATE _analyze_daily_count ';
 333                                                 $queryStr .=   'SET aa_count = ? ';
 334                                                 $queryStr .=   'WHERE aa_serial = ? ';
 335                                                 $ret = $this->execStatement($queryStr, array($count, $serial));
 336                                                 if (!$ret) return false;                // エラー発生
 337
 338                                                 $rowUpdated = true;             // 更新したかどうか
 339                                         }
 340                                 }
 341                                 if (!$rowUpdated){                      // データ更新していないとき
 342                                         $queryStr  = 'INSERT INTO _analyze_daily_count (';
 343                                         $queryStr .=   'aa_type, ';
 344                                         $queryStr .=   'aa_url, ';
 345                                         $queryStr .=   'aa_date, ';
 346                                         $queryStr .=   'aa_count, ';
 347                                         $queryStr .=   'aa_path ';
 348                                         $queryStr .= ') VALUES (';
 349                                         $queryStr .=   '?, ?, ?, ?, ?';
 350                                         $queryStr .= ')';
 351                                         $ret = $this->execStatement($queryStr, array(1/*訪問者数*/, $url, $date, $total, $path));
 352                                         if (!$ret) return false;        // エラー発生
 353                                 }
 354                         }
 355                 }
 356
 357                 // 1日あたりアクセスポイントごとの集計
 358                 $params = array();
 359                 //$queryStr  = 'SELECT COUNT(DISTINCT al_cookie_value) AS total,al_uri,al_path FROM _access_log ';
 360                 $queryStr  = 'SELECT COUNT(DISTINCT al_cookie_value) AS total, al_path FROM _access_log ';              // 2011/6/2 PostgreSQL9対応
 361                 $queryStr .=   'WHERE (? <= al_dt AND al_dt < ?) ';
 362                 $params[] = $startDt;
 363                 $params[] = $endDt;
 364                 $queryStr .=  'GROUP BY al_path ';
 365                 $queryStr .=  'ORDER BY total DESC';
 366                 $ret = $this->selectRecords($queryStr, $params, $rows);
 367
 368                 // 集計データを登録
 369                 if ($ret){                      // データありのとき
 370                         for ($j = 0; $j < count($rows); $j++){
 371                                 $total = $rows[$j]["total"];
 372                                 $path = $rows[$j]["al_path"];
 373
 374                                 // FCKEditorからのアクセスは、アクセスポイントを使用しないので除く
 375                                 if (!empty($path)){
 376                                         $queryStr  = 'INSERT INTO _analyze_daily_count (';
 377                                         $queryStr .=   'aa_type, ';
 378                                         $queryStr .=   'aa_url, ';
 379                                         $queryStr .=   'aa_date, ';
 380                                         $queryStr .=   'aa_count, ';
 381                                         $queryStr .=   'aa_path ';
 382                                         $queryStr .= ') VALUES (';
 383                                         $queryStr .=   '?, ?, ?, ?, ?';
 384                                         $queryStr .= ')';
 385                                         $ret = $this->execStatement($queryStr, array(1/*訪問者数*/, ''/*アクセスポイント指定*/, $date, $total, $path));
 386                                         if (!$ret) return false;        // エラー発生
 387                                 }
 388                         }
 389                 }
 390
 391                 // 1日あたりすべてのアクセスの集計
 392                 $params = array();
 393                 //$queryStr  = 'SELECT COUNT(DISTINCT al_cookie_value) AS total,al_uri,al_path FROM _access_log ';              // NG
 394                 $queryStr  = 'SELECT COUNT(DISTINCT al_cookie_value) AS total FROM _access_log ';               // OK
 395                 $queryStr .=   'WHERE (? <= al_dt AND al_dt < ?) ';
 396                 $params[] = $startDt;
 397                 $params[] = $endDt;
 398                 $queryStr .=  'ORDER BY total DESC';
 399                 $ret = $this->selectRecord($queryStr, $params, $row);
 400
 401                 // 集計データを登録
 402                 if ($ret && $row["total"] > 0){                 // データありのとき
 403                         $total = $row["total"];
 404
 405                         $queryStr  = 'INSERT INTO _analyze_daily_count (';
 406                         $queryStr .=   'aa_type, ';
 407                         $queryStr .=   'aa_url, ';
 408                         $queryStr .=   'aa_date, ';
 409                         $queryStr .=   'aa_count, ';
 410                         $queryStr .=   'aa_path ';
 411                         $queryStr .= ') VALUES (';
 412                         $queryStr .=   '?, ?, ?, ?, ?';
 413                         $queryStr .= ')';
 414                         $ret = $this->execStatement($queryStr, array(1/*訪問者数*/, ''/*アクセスポイント指定*/, $date, $total, ''/*すべてのアクセスポイント*/));
 415                         if (!$ret) return false;        // エラー発生
 416                 }
 417                 return true;
 418         }
 419         /**
 420          * 省略文字列を作成
 421          *
 422          * @param string  $str          変換元文字列
 423          * @param int     $len          文字列長
 424          * @return string                       作成した文字列
 425          */
 426         function makeTruncStr($str, $len)
 427         {
 428                 if (strlen($str) > $len) $addStr = '...';
 429                 $destStr = substr($str, 0, $len) . $addStr;
 430                 return $destStr;
 431         }
 432         /**
 433          * アクセスログにアクセス管理用クッキー値が存在しているかどうか
 434          *
 435          * @param string $cookieValue   クッキー値
 436          * @param int $serialNo                 制限用シリアルNo(負の場合は制限なし)
 437          * @return                                              true=存在する、false=存在しない
 438          */
 439         function isExistsCookieValueInAccessLog($cookieValue, $serialNo = -1)
 440         {
 441                 $params = array();
 442                 $queryStr  = 'SELECT * FROM _access_log ';
 443                 $queryStr .=   'WHERE al_cookie_value = ? '; $params[] = $cookieValue;
 444                 if ($serialNo >= 0) $queryStr .=   'AND al_serial < ? '; $params[] = $serialNo; // シリアルNoで制限
 445                 return $this->isRecordExists($queryStr, $params);
 446         }
 447         /**
 448          * アクセスログのアクセス解析値を更新
 449          *
 450          * @param int   $serialNo               ログシリアルNo
 451          * @param bool  $isFirst                最初のアクセスかどうか
 452          * @param bool  $isCrawler              クローラーかどうか
 453          * @return bool                                 true=更新成功、false=更新失敗
 454          */
 455         function updateAccessLog($serialNo, $isFirst, $isCrawler)
 456         {
 457                 // トランザクション開始
 458                 $this->startTransaction();
 459
 460                 $queryStr  = 'UPDATE _access_log ';
 461                 $queryStr .=   'SET ';
 462                 $queryStr .=     'al_analyzed = true, ';        // アクセス解析終了
 463                 $queryStr .=     'al_is_first = ?, ';
 464                 $queryStr .=     'al_crawler = ? ';
 465                 $queryStr .=   'WHERE al_serial = ?';
 466                 $params = array(intval($isFirst), intval($isCrawler), $serialNo);
 467                 $this->execStatement($queryStr, $params);
 468
 469                 // トランザクション終了
 470                 return $this->endTransaction();
 471         }
 472 }
 473 ?>