PHPとSQLiteでi-mobileのデータをグラフ化する方法

i-mobileでクリック保証のアフィリエイトだけど数値が並んでいるだけでグラフでは確認できない。なのでSQLiteの練習もかねて、グラフ化を行った際のメモ。

実装したい機能

• 詳細レポートの日別データCSVをアップロードできるフォーム
• アップロードしたデータをSQLiteに保存
• 既に登録されている日付分は上書き保存せず、差分だけ保存する
• データリセット機能
• highcharts.jsを使ってグラフ表示

下準備

以下からhighcharts.jsを用意してアップロードしとく。

http://www.highcharts.com/

ソースコード

<?
if( !is_file('affiliate.sqlite') ){
    $flg = 1;
}

$db = new SQLite3('affiliate.sqlite');

if( $flg ){
    $query = "CREATE TABLE 'i-mobile' ( 'seq' INTEGER PRIMARY KEY, 'ymd' DATE , 'pv' INT, 'imp' INT, 'click' INT, 'ctr' FLOAT, 'cpm' FLOAT, 'income' INT );";
    $result = $db->query($query);
}


/* リセット時 */
if( $_GET['mode'] == 'reset' ){
    $query = 'DELETE FROM `i-mobile`';

    $result = $db->query($query);

    if( !$result ){
        $msg = 'reset error!';
    } else {
        header('Location:' . $_SERVER['PHP_SELF'] . '?mode=reseted');
    }
}


/* ファイルアップロード時 */
if (is_uploaded_file($_FILES["upfile"]["tmp_name"])) {

    $msg = '';

    do {

        /* アップロード */
        if( !move_uploaded_file($_FILES["upfile"]["tmp_name"], $_FILES["upfile"]["name"]) ){
            $msg = 'file upload error!';
            break;
        }

        chmod( $_FILES["upfile"]["name"], 0644);
        setlocale(LC_ALL, 'ja_JP.UTF-8');
        $data = file_get_contents($_FILES["upfile"]["name"]);
        $data = mb_convert_encoding($data, 'UTF-8', 'sjis-win');
        $temp = tmpfile();
        $meta = stream_get_meta_data($temp);
        fwrite($temp, $data);
        rewind($temp);
        $file = new SplFileObject($meta['uri']);
        $file->setFlags(SplFileObject::READ_CSV);
        $csv  = array();
        foreach($file as $line) {
            $csv[] = $line;
        }
        fclose($temp);
        $file = null;
        unlink($_FILES["upfile"]["name"]);


        /* 既に入力されている日付の呼び出し */
        $query = '
            SELECT ymd 
            FROM `i-mobile`
        ';
        $result = $db->query($query);

        if( !$result ){
            $msg = 'select error!';
            break;
        }

        while ($row = $result->fetchArray()) {
            $readData[$row['ymd']] = 1;
        }


        /* 書き込み */
        $insertData = '';
        for( $i = 1; $i < count($csv); $i++ ){

            if( !$readData[$csv[$i][0]] ){ //既に登録されている分は不要

                $query = '
                    INSERT INTO `i-mobile` 
                        (ymd, pv, imp, click, ctr, cpm, income) 
                    VALUES 
                        ("' . $csv[$i][0] . '","' . $csv[$i][1] . '","' . $csv[$i][2] . '", "' . $csv[$i][3] . '","' . str_replace('%','',$csv[$i][4]) . '", "' . str_replace('￥','',$csv[$i][5]) . '", "' . $csv[$i][6] . '")
                ';

                $result = $db->query($query);

                if( !$result ){
                    $msg = 'insert error!';
                    break;
                }
            }
        }


        if( $msg == '' ){
            header('Location:' . $_SERVER['PHP_SELF'] . '?mode=uploaded');
        }

    } while (0);
}


/* 読み込み */
$query = '
    SELECT * 
    FROM `i-mobile` 
    ORDER BY ymd ASC 
';
$result = $db->query($query);

$highchartsData['ymd'] = '';
$highchartsData['pv'] = '';
$highchartsData['imp'] = '';
$highchartsData['click'] = '';
$highchartsData['ctr'] = '';
$highchartsData['cpm'] = '';
$highchartsData['income'] = '';

while ($row = $result->fetchArray()) {
    $affiliateData[$row['id']]['ymd'] = $row['ymd'];
    $affiliateData[$row['id']]['pv'] = $row['pv'];
    $affiliateData[$row['id']]['imp'] = $row['imp'];
    $affiliateData[$row['id']]['click'] = $row['click'];
    $affiliateData[$row['id']]['ctr'] = $row['ctr'];
    $affiliateData[$row['id']]['cpm'] = $row['cpm'];
    $affiliateData[$row['id']]['income'] = $row['income'];

    $highchartsData['ymd'] .= '"'. $row['ymd'] . '",';
    $highchartsData['pv'] .= $row['pv'] . ',';
    $highchartsData['imp'] .= $row['imp'] . ',';
    $highchartsData['click'] .= $row['click'] . ',';
    $highchartsData['ctr'] .= $row['ctr'] . ',';
    $highchartsData['cpm'] .= $row['cpm'] . ',';
    $highchartsData['income'] .= $row['income'] . ',';
}
?>

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<title>i-mobileグラフ化</title>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<link href="//netdna.bootstrapcdn.com/twitter-bootstrap/2.3.2/css/bootstrap-combined.min.css" rel="stylesheet">
<script src="//netdna.bootstrapcdn.com/twitter-bootstrap/2.3.2/js/bootstrap.min.js"></script>
<script src="./highcharts/js/highcharts.js"></script>
<script>
$(function(){
    $('#line').highcharts({
        chart: {
            type: 'line',
            marginRight: 130,
            marginBottom: 25
        },
        title: {
            text: 'i-mobile',
            x:-20 //center
        },
        xAxis: {
            categories: [<? echo rtrim($highchartsData['ymd'], ',');?>]
        },
        yAxis: {
            title: {
                align: 'middle',
                rotation: -90

            },
            plotLines: [{
                value: 0,
                width: 1,
                color: '#808080'
            }]
        },
        legend: {
            layout: 'vertical',
            align: 'right',
            verticalAlign: 'top',
            x: -80,
            y: 0,
            borderWidth: 0
        },
        series: [{
            name: 'ページ表示回数',
            data: [<? echo rtrim($highchartsData['pv'], ',');?>]
        }, {
            name: '広告表示回数',
            data: [<? echo rtrim($highchartsData['imp'], ',');?>]
        }, {
            name: 'ページCTR',
            data: [<? echo rtrim($highchartsData['ctr'], ',');?>]
        }, {
            name: 'ページCPM',
            data: [<? echo rtrim($highchartsData['cpm'], ',');?>]
        }, {
            name: '収益金額',
            data: [<? echo rtrim($highchartsData['income'], ',');?>]
        }]
    });
});
function dataReset(){
    if( confirm('データをリセットしますか？') ){
        location.href = '<?=$_SERVER["PHP_SELF"]?>?mode=reset'
    } else {
        return false;
    }
}
</script>
</head>
<body>
    <? if( $msg ){ ?>
        <div class="alert alert-error">
            <?=$msg?>
        </div>
    <? } ?>
    <? if( $_GET['mode'] == 'uploaded' || $_GET['mode'] == 'reseted' ){?>
        <div class="alert alert-success">
            <?=( $_GET['mode'] == 'uploaded' ) ? 'CSVファイルのアップロードが完了しました。': 'データのリセットが完了しました。';?>
        </div>
    <? } ?>
    <form enctype="multipart/form-data" method="post" action="">
        <input type="file" name="upfile"><br />
        <input type="submit" value="Upload" class="btn btn-info">
        <input type="button" value="Reset" class="btn btn-danger" onclick="return dataReset();">
    </form>
    <div id="line"><!--グラフ領域--></div>
</body>
</html>

その他

ソースをそのまま保存して、highchats.jsのパスを書き換えてアップロードするだけで動くはず。

気付いた点としてSQLiteのバージョンが3.7.11以前のものだと、MySQLでよく使う以下の形のバルクインサートができない模様。

INSERT INTO table 
    (aaa, bbb) 
VALUES 
    (aaa,bbb), (aaa,bbb)

以下のような形ならバルクインサート可能みたいだけど、1回に挿入できるのは500件まで。

INSERT INTO member
SELECT 0 AS id, 'foo' AS name
UNION ALL SELECT 1, 'bar'
UNION ALL SELECT 2, 'baz'
UNION ALL SELECT 3, 'qux';

500件以上なら分ける処理とか入れるのが面倒くさかったので1回ずつ挿入にした。

また、データのリセット時にTRUNCATEを使おうとしたけどSQLiteには無いらしい。DELETE FROM tableで対応。

BBSと違って複数人数が使うものでもないし、日別データならデータ数も少ないし、こういうページにSQLiteが向いていそう。