ぼどレポ: Google Visualization API を使ったボドゲ会カレンダの更新

今回の記事もボドゲとは関係なしです。

以前『Blogger API v3の使い方』の最後にちょっと書いた『ボドゲ会カレンダの簡易登録』を
実装してみました。

仕組みとしては、下記の3ステップで行います。

ボドゲ会情報をGoogle ドライブのスプレッドシートに登録
Google Visualization APIを使ってそのデータを習得
javascriptでテーブルの形式に編集して画面表示

では順番に解説していきます。

１．googleドライブにボドゲ会情報を登録します

たくさんありますね。

以降はページ側にjavascriptコードを書きます。

２．visualization APIを読み込むコード

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
    //<![CDATA[
    <!--

    //visualization　APIの読込
    google.load("visualization", "1", {packages:['table']});
    //-->
    //]]>
</script>

こちらが今回の肝である『visualization API』を読み込むコードです。

３．スプレッドシートからデータを取得するコード

//データソース URL
//本番用
var dataSource = 'https://docs.google.com/spreadsheet/ccc?key=0Ajmd9EuHdR9ldF9zZ3pYZl9VTUxyQU5USDEwbDZmWmc&gid=0&pub=0';

//Query Language
//※今月以降の情報のみ取得する
var today = new Date();
var queryLanguage = 
    'SELECT B, C, D, E, F, G, H, I ' + 
    'WHERE ' +
        'YEAR(B) >= ' + today.getFullYear() + ' AND ' + 
        'MONTH(B) >= ' + today.getMonth() + ' ' +
    'ORDER BY B ';

/***********************
 * ページ読込完了後の処理
 ***********************/
google.setOnLoadCallback(function() {
    $("#result").html('<img src="' + base64img + '" />');
    //google spreadsheetの情報を取得しdispCalendar関数を実行する
    var query = new google.visualization.Query(dataSource);
    query.setQuery(queryLanguage);
    query.send(dispCalendar);
});

dataSourceにスプレッドシートのURLを指定し、queryLanguage にそのデータを取得するため命令をSQLに似たQuery Languageという言語で記述します。今回は『開催日が今月以降の登録データ』を取得します。

参考：Query Language Reference
https://developers.google.com/chart/interactive/docs/querylanguage?hl=ja

setOnLoadCallbackでデータ取得処理を実行し、取得したデータに対してdispCalendar関数を実行します。

４．テーブルに編集して画面表示するコード

/***********************
 * カレンダテーブル表示
 ***********************/
function dispCalendar(response){
    var el = document.getElementById('result');

    if(response.isError()) {
        //エラーの場合はエラーメッセージを出力して処理を終了する
        el.innerHTML = response.getDetailedMessage();
        return;
    }

    //テーブルデータ
    //※indexは下記の通り
    //日付:0
    //曜日:1(未使用)
    //時間:2
    //名称:3
    //リンク:4
    //最寄駅:5
    //住所:6
    //施設名:7
    var data = response.getDataTable();

    //日付フォーマットパターン
    var formatter1 = new google.visualization.DateFormat({pattern: 'M/d(E)'});

    //日付と時間を結合するパターン
    var formatter2 = new google.visualization.PatternFormat(
        '{0}<br /><span style="font-size: x-small;">{1}</span>');
    //名称とリンクを結合するパターン
    var formatter3 = new google.visualization.PatternFormat(
        '<b>{0}</b><br /><span style="font-size: x-small;">' +
        '<a href="{1}" target="_blank">{1}</a></span>');
    //住所情報を結合するパターン
    var formatter4 = new google.visualization.PatternFormat(
        '最寄駅：{0}<br /><span style="font-size: x-small;">' +
        '<a href="https://maps.google.co.jp/maps?q={1}" target="_blank">' +
        '{1}<br />{2}</a></span>');

    //各パターンを適用する
    formatter1.format(data, 0);
    formatter2.format(data, [0, 2]);
    formatter3.format(data, [3, 4]);
    formatter4.format(data, [5, 6, 7]);
    
    var today = new Date();
    var tmpToday = new Date();
    var yesterday = new Date(tmpToday.setDate(today.getDate() - 1));

    //日付によってセルの色を変えるパターンを適用する
    //今日以前の日付は灰色・今日日付はピンク
    var formatterC = new google.visualization.ColorFormat();
    formatterC.addRange(0, yesterday, null, 'lightgrey');
    formatterC.addRange(yesterday, today, null, 'pink');
    formatterC.format(data, 0);
    
    //列名を設定する
    data.setColumnLabel(0, '日時');
    data.setColumnLabel(5, '会場');
    
    //曜日フォーマットの変換
    for(var i = 0; i < data.getNumberOfRows(); i++){
        var dispDate = data.getFormattedValue(i, 0)
            .replace('Sun','日')
            .replace('Mon','月')
            .replace('Tue','火')
            .replace('Wed','水')
            .replace('Thu','木')
            .replace('Fri','金')
            .replace('Sat','土')
            .replace('土','<span style="color: blue;">土</span>')
            .replace('日','<span style="color: red;">日</span>');
        data.setFormattedValue(i, 0, dispDate);
    }

    //表示する列を指定
    var view = new google.visualization.DataView(data);
    view.setColumns([0, 3, 5]);
    
    //テーブルを表示
    var visualization = new google.visualization.Table(el); 
    visualization.draw(view, {allowHtml: true});
}

まあこのへんは長いけど泥臭いコードであんまり面白みが無いですね。
簡単に処理を説明します。
◆スプレッドシートのデータの見た目を変更
　APIのDateFormatとPatternFormat関数を利用します。
　例：2014/02/01 → 2/1(土)　に変換、
　　　2/1(土)と10:00～21:00を１つのセルにまとめる、などです。

◆セルの色を変更
　APIのColorFormat関数を利用します。

　開催日が『過去日付』の場合は灰色で、『今日』の場合はピンクに変更します。

◆列名を変更する
　indexが0と5の列名を変更します。（indexは0から始まるので1つ目の列が0になります。）

◆曜日フォーマットを変更する
　『土』・『日』の文字色を青と赤に変更します。

◆テーブルを表示する
　indexが0,3,5の列だけ表示します。

参考：Google Visualization API Reference
https://developers.google.com/chart/interactive/docs/reference?hl=ja

５．コードまとめ

<html>
    <head>
        <meta http-equiv="content-type" content="text/html;charset=utf-8">
        <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
        <script type="text/javascript" src="http://www.google.com/jsapi"></script>
        <script type="text/javascript">
            //<![CDATA[
            <!--

            //visualization　APIの読込
            google.load("visualization", "1", {packages:['table']});
            
            /**********************
             * 定数・変数の定義
             **********************/
            //データソース URL
            //本番用
            var dataSource = 'https://docs.google.com/spreadsheet/ccc?key=0Ajmd9EuHdR9ldF9zZ3pYZl9VTUxyQU5USDEwbDZmWmc&gid=0&pub=0';

            //Query Language
            //※今月以降の情報のみ取得する
            var today = new Date();
            var queryLanguage = 
                'SELECT B, C, D, E, F, G, H, I ' + 
                'WHERE ' +
                    'YEAR(B) >= ' + today.getFullYear() + ' AND ' + 
                    'MONTH(B) >= ' + today.getMonth() + ' ' +
                'ORDER BY B ';
            
            //ローディングGIF
            var base64img = 'data:image/gif;base64,R0lGODlhPAA8AMYAAP////77+//k4/7V0v7n5f7j4v7PzP7Sz//29f7z8v7h3//h3//z8v7v7v/5+f7b2P/t7P7Y1v7X1f7g3v7X1P7q6f/q6f7l4/25s/2vqf7Nyf/19P3Dvv/6+f7q6P7b2f20rv25tP7Dvv/29v63svyGffx+dfyOhv2+uv22sf6+uvyFffyNhvyQiPx2bf2IgP7MyPx/dv2Zkv7c2v7Lx/2qpP67tv7U0fyPh/6yrf7Dv//u7f/g3vtoXfteU/7j4ft3bvtuZPt7cvyGfvySivyKgf2Ykf24s/709P2po/xwZvx5cP2zrf2/u/2Hf/2oovtmW/2yrPyJgf26tf2knv7CvvtvZf3IxP2tp//r6f/s6/7W1P7a2PySi/7Myfyak/txZ/tnXft3bfuAd/2uqP7Rzvt/dvx5bwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH/C05FVFNDQVBFMi4wAwEAAAAh+QQJCgBoACwAAAAAPAA8AAAH/oAAgoOEhYaGAQIDAwSHjo+QkZAFBpUGBZKZmpsHlgYDm6GRCAkIj56Vj6SmopGUlQqOqAaOC5YCrY8MqA2Hs4cNqAy5hwSooIa/hgOojcSFwc3JqIbGvM/LqAcBhcqDDp2eyNiE0Z4L3dSEtsLkhg+zw4PeALuoD+6GDrP48+qC8FA5yGfolScIg8JVGgdhFiZiDAggPBRAYaUIg6xVcgZAgjZuhyZQqPCI3Sd5hSzQK7CII4BZFg5dwJChpoZD++4NLOTRUj9IAStJMLSBQ82jGTYYsjdLAEhBCMIdYAUpaqWphDocQIqUZKGcswwc8EAoQIMGTyOZRUvoAwiu/kiVFgxrKQJVcg1owj164FEDZnS3uevwdm+GEBckEbDoCeWzCoZBUNgUwKSnXuQm7NXQoRWDoGLTEiOMVIRXYhCYPbiLrYGIwxMIyp5Nu7btZ4kAh2VkewSJEiaCCxd+AgUAg3QrPZydYrjz4SoY0x0ne8Xz6yek765t/bpzFhqTG3BJsLl36Md1z+JtOwWL8yxU3J5Pv759cqkNrCZ4oYWLFzBg85kngpGDQAwuJOiCDDO04oACYWGGDQ0KVlgDa5AsRpdjxMxQYYUx2CDJX8kViA0CJnxY4Qs3PILcLBJgSMwFOKhYYQ44lRjTIBtooIFckmxwwAFAAqBDija6/rDDUnQVkBYPPfjgQw88SPIDEEEEAcQPhCCQQ5INfsXPToQIIaWUQ0hCRJZZFmHIDkaouKQhJg3AoSBHnHnmIEgkocQSTBDCJptNHDKDEwomoYtEjmwQpZ5CDPIEFJRCEcUgUgyqZZGE0DBFmLlQoaeUVQxiRaVQLDHIFZoGgYVtWYzqwwqEoEopIWtqqgV3sm5Rq62EcNFqF7R5IesXhdgKRSFUtMrFbGCMGganyhaihRiajiGbsaOSYUi1haTQahkEkTEqGIeAW4gZmqZA0BajepEusIaUoemzBIkqJbLzoupIs1m+OhsPW1TpiLqG/MAFl/cRcmqlZzQcyqSVDF4q8SZRnHGGxQQFAgAh+QQJCgBpACwAAAAAPAA8AIb////++/v/5OP+1dL+5+X+4+L+z8z+0s//9vX+8/L+4d//4d//8/L+7+7/+fn+29j/7ez+2Nb+19X/6un+6uj/+vn9tK79r6n9ubP+19T9ubT+wr77cmj7XlP8hn7/4N7+5uT9lo/8fnX8job+z8v+1tT7aF3/9fT/9vb8hn38npf/39z/6+n8mpP+zMn+zsv9rqn9rqj9vrr7Z138hX3+1tP9pJ77e3L9lo79trH9uLP+9PT+0Mz9xsL+7u3+4uD8cGb7Zlv+5eP8f3b8dm38gnn8jIT9mJH8kIj9npf7cWf9u7b9raj7d277gHf+w7/8jYb9raf7bmT8j4f+vrr9qKL9xcD7d23+y8f+sq3+6+r8i4P/7u3+3Nr9mZL9n5j9qaP8eW/7b2X9iID9sqz9urX+1NH9h3/7f3YAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/oAAgoOEhYaGAQIDAwSHjo+QkZAFBpUGBZKZmpsHlgYDm6GRCAkIj56Vj6SmopGUlQqOqAaOC5YCrY8MqA2Hs4cNqAy5hwSooIa/hgOojcSFwc3JqIbGvM/LqAcBhcqDDp2eyNiE0Z4L3dSEtsLkhg+zw4PeALuoD+6GDrP48+qC8FA5yGfolScIg8JVGgdhFiZiDAggPBRAYaUIg6xVcgZAgjZuhyAQkHeI3SeShCbQK7CII4BZE4Axg3Vo372BhTxa6gcpYCUJ+nxawknI3iwBIAUhCHeAFaSllZoSSjSrEkpBNqseoDC1QYOkkQJ4BTvBosBDBqtGcEoOwcyq/pceNXg7a5u7inA/9YpEwKylq7mManMZ1qSnvdgazloAVhMDoXbJ4d0JOBQEZg/YYkMAb8BEgqBDix5NOlciuscIE6xwwMKF17BhY8gAIG3eh6EPxN4dO4PfquNAu+bNG8PvWcEJDiceW4PGvAZUu9PNvHdt1OKk5zugoboG2qXDix9PHtsGDh08fCAIIoSIESSwlfDQoX4HEyfcoUghor8IFSu0wkIL9hXogjsv+KcgDChsEoMJBRZYgjsrKKhgCjJI4gJ6Edo3Q37koECDhQqOUMMjNnRY4A3r5QMCDiQqmMMhLKhY3ww6ELIDDzzsoIlbA7DVw4gxiuCDISXY/mgDiIL8AEQQQQDxgyRCDEEEEUMIQQgKORQZYCE1RkgDC4YUASWURkhyxJVXImGID0mQeKQhBNanxISGLHHmmYOcwEQTTsw4CJtsPnHIClD4F8MjJcSwgSM7PLlnEYNEIcWlUggKwBSEYqnZIC9Q8WUuVewJpRWDXIGpFE4MgkWnRGRBmhamBrEFIateSsianXIx2ha1YjRIrlIQ0gWsXohGQq1fFEJsIWDA2kVoYZgqho+45loIFyJ0OgZoy5pKhiHPFlIGrGYQRIapYRxSbiFndFoGQRGYGh+52hpiRqfTElQqlM26m68h0V4pa2g/RDClI+8aIkQXWpZHiKqYEqIhcSiWYqrpxZnkgAYaG7sTCAAh+QQJCgBkACwAAAAAPAA8AIb////++/v/5OP+1dL+5+X+4+L+z8z+0s//9vX+8/L+4d//4d//8/L+7+7/+fn+29j/7ez+2Nb+19X/6un+6uj/9fT9pJ77cmj7XlP9r6n7Z137cWf9rqj9xcD8eXD7Zlv8jIT+4uD9vrn9ubP8cGb+9PT/+vn9tK79w77+6un+6+r9oJn+z8v+4N7+zcn9sqz+19T7b2X+5eP9qKL8gnn+vrn9u7b/9vb+29n+4+H7d277bmT+5uT8hn38fnX8i4P8iYH8kov9npf9lo/+w779n5j8eW/9v7v8f3b9iID+u7b9xsL9ubT+sq38dm39urX9lo79raf9yMT+zsv9trH/7Ov8kor+7u3+2tj/39z8npf+0c77f3b7d238hX38job+vrr+1tP8jYb9h38AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/oAAgoOEhYaGAQIDAwSHjo+QkZAFBpUGBZKZmpsHlgYDm6GRCAkIj56Vj6SmopGUlQqOqAaOC5YCrY8MqA2Hs4cNqAy5hwSooIa/hgOojcSFwc3JqIbGvM/LqAcBhcqDDp2eyNiE0Z4L3dSEtsLkhg+zw4PeALuoD+6GDrP48+qC8FA5yGfolScIg8JVGgdhFiZiDAggPBRAYaUIg6xVcgZAgjZuhyAQkHeI3SeShCbQK7CII4BZE4Axg3Vo372BhTxa6gcpYCUJ+nxawknI3iwBIAUhCHeAFaSllZoSSjSrEkpBNqseoDC1QYOkkQJ4BTvBosBDBqtGcEoOwcyq/pceNXg7a5u7inA/9YpEwKylq7mManMZ1qSnvdgazloAVhMDoXbJ4d0JOBQEZg/YYkMAb8BEgqBDix5NOlcFCxcwqF69+kIG0on8WmIEwALr26wtjE6bl4AG3MAvjJY9a8Bv4Lc3DM8rjgPy2xx2M7fUyMKG5xuik2aZl3bp7+DDi8fWwcMHECEINhCRYUSsZxFAfJj/gUQJdyZOZNifAUWKViqsQN+ALLjTAn8IumDCJi+QMOCAGJGTAoIIngCDJCyY9yB9MdxHTn4UIjiCDI/MsOGANKSXTwM1hIjgAYeocOJ8MdhASAUHHFCBJjfAAMMNhOCgn4sZ7FhIBDPO/uChIDnosMMOOuQgCQ89+OBDDzwQYsIBRP5XiIwP/qCCIUA8+WQQkghhpZVDGFIBESEaWUgR9BkRYSFHmGnmIAi8gEQSShCy5ppLHJICE/y58EgEL3TgSAVO6gnEIE04YakTTwwCxaBXAnlICzB4mUsUej4pxSA+XOpEEoNMwakPVJBWRak7WEGIqpYSoianV4xmBa1Y3IorIVm8qoVoW9Cqm7CqFsLBq1mExkWpXcgpCK5OFHKFF5x+AVqypcZaCLaGgPFqGARRUSoXh5BriBicgkEQFqVu0e6whoTBabQEkfrksoa4a8izVooLWg5YSOmIwIbwkEWW4wmq6hgREIdS6aWZVrzJE2OMkTFBgQAAIfkECQoAZAAsAAAAADwAPACG/////vv7/+Tj/tXS/ufl/uPi/s/M/tLP//b1/vPy/uHf/+Hf//Py/u/u//n5/tvY/+3s/tbU/a+p/sO//tjW/tfV/JqT+15T+3Jo/+rp/+vp/IZ+/aSe//X0+2hd/bm0+3xz/+De/szJ+3Fn/a6o+2dd/IV9/sK+/vT0/amj/Hlw+2Zb/bOt/aii+29l/Hlv/bKs/cjE+4B3+25k/JKL/uPh/trY+3du/+zr/aWe/tHO/bax+3dt/a2n/ImB/b+7//r5/+bk/uXj/H92/HZt/bSu/JKK/I+H/ZmS/sO+/b65+392//b2/rey/IZ9/I6G/b66/tvZ/H51/r66/r65/rKt/svH/uDe/+7t/ZiR/tza/urp/cO+/tTR/s3J/Yd//bmz/bq1/tfU/I2GAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB/6AAIKDhIWGhgECAwMEh46PkJGQBQaVBgWSmZqbB5YGA5uhkQgJCI+elY+kpqKRlJUKjqgGjguWAq2PDKgNh7OHDagMuYcEqKCGv4YDqI3EhcHNyaiGxrzPy6gHAYXKgw6dnsjYhNGeC93UhLbC5IYPs8OD3gC7qA/uhg6z+PPqgvBQOchn6JUnCIPCVRoHYRYmYhEkTHAUQGElCoOsVXIGoII2bocgEJB3yMKFkxgiHMpAr8AijgBmZQDGDNYhDSdzXtigwZBHS/0gBaxUQd9QSwMLRdCpk0MHQgjCHWAFKWqlqYQSzapEchBOpjk9fMjaoAFISQHKngWQwaLAQ/4cwOoEEYIggppbLz0SMULuSQ9PyVXM+6lXJBIl/KokZ2/WAZiROpgEK8Jdw1kL1mqKYEJnicDYBgPt2upEXxN18yGANwAhwdewY8ueTQxFChUrcuvWrYLF7ERuxTVqsbv47hayDRI2QMCF8ecqZAffOsD58+IvpC+3NADG9eIwkm/fCKDFi+8vws92SZgR7ffw48snF0PGDBo17FL45JqYDRozBDjDDaA9I1olD5CWCQ45COigDpZtpYBmkOxwg4MO2uBOY9pAdogO9mEoIA8FEnPgLAMYdkgPIjroQ36q/ZTXQ4Xg0GKAPPwA1SJURQJEEEEAQUhbhCVFiA039v5QoBBDEEHEEEJI0kAREkhQhIoABKCcJwraiKEROBhyhJNOIiFJElVWqYRRsxhJCAcCLqGhIROQSeYgTDThxBNQEJJmmlEcwgBe6Dhiww4xOIJAk3YeMcgOUkQqxRSDUPGnlUKGNNIzVdjppBWDmCCpFE8McsWlEhwwGxaeEpEFIaNGSgial5aYTxatagFrrIRsgSoXsXXRagqFxCpFIV6gugVsX3gqRY8AGFtIB1T+CcZrwnoahiHSFiIGqlHmE4anXxzSbSEfXCoGQVp42oW5vBoixKXLEtSpk8TCO6ojyVapKmxCaBGuvpI+0sAWWM4HgKiSjqFwKJBKSunDmwhMMcYYExMUCAAh+QQJCgBnACwAAAAAPAA8AIb////++/v/5OP+1dL+5+X+4+L+z8z+0s//4N7+19T/9vX+8/L9pJ77XlP8mpP/4d/+1tT+zMn+7+7/8/L9rqj7cmj7cWf7aF3/9fT/+fn8hX3+29j/6+n8hn7+wr7/7ez+2Nb9s639xcH7Z137fHP+19X9n5j7Zlv8eXD9uLP/6un+4d/+6+r8jIT7e3L9qaP+9PT8cGb9vLf9qKL8g3r+4uD+z8v8eW/9sqz7b2X8i4P9xcD9raj7gHf7bmT9trH9raf7d237f3b+y8f9iID8dm39mZL+5eP+3Nr8f3b/7u39qqT+1NH9urX8fnX+sq38j4f+w7//9vb/8O/+5uT8hn39mJH9lo78npf9h3/9xsL/+vn9tK79ubP9r6n+zsv+7u39npf/39z+1tP8jYb+vrr9ubQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/oAAgoOEhYaGAQIDAwSHjo+QkZAFBpUGBZKZmpsHlgYDm6GRCAkIj56VjwoLCqKSDA2xDo6oBo4PlgKujxCxvhGHtYcSqBO7hxS+sRXBqIcDqI3HhRHKsRSGwoUEtRLThhbWFxiF2oMZnZ6g39TWDbOE5oK4xeyGGu4Q8c6DE7Ub9gxxcNdhnydCG2plCGgIljUPg9JVWgfgQy1Mx0CEEOEIwwhrJAZxsyQNQAlUBwI4+kDAmCMTJ2KiAHEohTtCBRaVBFBLxTBolVYcYhGz6IkWLAy5UKZBUkJLJQxleOppYSEQRo2+gEEIwccGI0xFUpDuQKtBiWpVckmIaNai/jFkEMIQIQK5TAEkSFA5SIVEhYdmvDVKowZDBUDVXnpk48bgmDG4sgvwF9UAb5Fw5HhMk50/tQd2RoIB860NexZrPeAbCoQOozkkf6PsaQPbXTsc6zAcUEHCAR8YCh9OvLjxaRh49PDBvHnzHj+MJ6qsrhEQ59idAylOSbEnAkGyi+9RnLraAeHFYxdS3ru6H+qxRyfe3b0B60LiC5nPPXEtRscFKOCABLIzBBFFGHHEYSB8Etw0SBhRxIRFJHHWbH/Z5ooSS1DoIROoqbUCa5I0kYSHHiJhz2e1hCYJEwiiSKETF05Dm2KXPfKEjB5CsWBvJ3mHUSFK8DihE1EQ/iJFAglIoUkAU0xBol/eWUUIEkY+USMVVTjhRBVUSEJWJWYREkB99RApoxVKGHKFl15i4ZQnnREyFWCGvEBhFioaogWccA6yxQFcdJGAQZb4ZMgEiT3wCBJNDOGIFF0CekVEXmTqxaGCBGlJSiu1NM0PgHr5xSBcaOpFF4Ok5smQw4FRqhNhEKJqpgjhOVwYs4ph662EsGgJQMONMSs2v6paCD2e3GYPGaVq4GSymhaCjmXCGVtqGYbc6oUhI3mCmT1llErGId4+Ew1DYpQ6BrrAGkJMs8KR6iWy3cZrCLOwBkSFGGE6kq4jCkxQY4EApKqpGQiHcoCqnDasSQJmBpgRcUCBAAAh+QQJCgBnACwAAAAAPAA8AIb/////9fT9pJ77cmj9r6n7XlP7Z13+4uD+2db/9vX+8/L9qKL7Zlv9oJn/4d/+z8z/5OP+2Nb+z8v+7+7/8/L9sqz8eXD+1dL+5+X8eW/8cGb+9PT/+fn+0s/9n5j8i4P+29j+6+r8jIT9xcD7cWf/7ez+4+L+2tj9trH9yMX7b2X8g3r9rqj+19X++/v7bmT7gHf9u7b/6un+4d//7Ov8kov8gnn9raj7d279wLv9raf8ioH+4+H+0c77f3b7d238kor9yMT9iID8dm3+u7b+sq38fnX9h3/9urX+zsv8job8npf+5uT/39z8hn3/9vb+7u39rqn+1tP+vrr8hX39lo79xsL/+vn/5uT/8O/9tK79npf+vrn9w778jYb+29n+4N7+w77+6un+5eP+zcn9ubT+19QAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/oAAgoOEhYaGAQIDAwSHjo+QkZACBZUFApKZmpsGlgUDm6GRBwgHj56VjwkKCaKSCwyxDY6oBY4OD7kQro8Rsb8Sh7WHE7nGFLyHFb+xFsKohxfGuRjJhhLMsRWGw4UY07kT1oYZ2Robhd2DHB3gF+PX2Qwe6dCEuODI8IUf8hGE6gBQAPcAxD5DIeSJAGhPEAiCHA4agpVtxKBOlkgMKkHQhLUTKFI42qAi24pBLDyxGNQCXAcXjkpg0HdIwIubME4ciiGPkAASJFYOIiiDmLRcMw7RuMn0RQ0ahmww+yDpobEWhjhYnRax0ImmTW8EIHSgJAMVpiIlaPegQ6tB/i4gEMxFc9BSsExx5CC0QYIEdJlcTJgAc5AMtnO7FtKBt+kOHhITHJ37wKOjHj4a38QxFp4LxAQviIuE4odmnfAGzu1QTVOixj32cSTooHCoE0Ca/ug87vM0EHVdBckMBPLBBA8vlJDIvLnz59CtJaggZIj169eFEIEeF7S7akWwi8de5LkJyuAwGBnPXshz75QvrGcv/sh79NMuIKEvHol5/MaAdwR/R/gHnQmThdZadAw26OCD1iShhBFLMBFZBA8oN04TSxjhoRFOPLGPb8YA5woUUXyoohSyzTWDbZJM4YSKKjaxj2oEsSaJFBPS+CEVInoGnzGiPYKCjypW/mHhcS2hZ1khUCDpIRVWEHIFFlhcoYkLWWQB42HoKTZIE1KiEKQgE2hBAAFajAbJWrm4RYgL580VHABR0rgFFIZwseaaXVQ1zT+FaAXRISx86IWNhnzx559wQdDBBQsCQNQhFEzmwCNNTJGEI1eo+SgXg9RJDUsuwUiITHdu0sGja4IxCGjvCDLbNE82FwCsBIRBCEGEbGWMmBKFwasYv4JDCI4lOjcGr2QUAuw9BLVqTRmwaqFlstMU6l2tBz0LqxmGTEvIN+C4CY8ZsJZxiLmEJPhApeOIAesY7yprSDHTWMvLq2tGm2+3h+BTmXMTiKGutPoekgAFb0E4a34SDYdi6rwVh4LBBZQ2FwgAIfkECQoAZAAsAAAAADwAPACG/////vT0/amj/Hlw/bOt/aii+2Zb+29l/uPh/tvY/+De/tfU/a2n+25k/aWe/JqT+15T/aSe/trY/tHO/szJ/tbU/bax+4B3+3Fn/a6o+392+3du//X0/+vp+2hd/JKK/IV9/+zr/JKL/cjE/Hlv/sK+/tza/ru2/szI+3dt/IqB/bKs+3ty+2dd/+3s/ufl//Py/HZt/YiA/b+7/biz/u/u/tXS/s/M/uHf/+7t/ZmS/ImB//n5//b1/vv7/+Tj/H92/sTA/+rp/tLP/rKt/JCI/uXj/uPi/tTR/Yd//bq1/H51/+Hf/ZiR/svH//b2/rey/I6G/b66/I2G/r66/uDe/bmz/a+p/cO+/tjW/urp/bSu//r5/s3J/r65/tfV/tvZ//Dv/sO+/bm0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB/6AAIKDhIWGhgECAwMEh46PkJGQBQaVBgWSmZqbB5YGA5uhkQgJCI+elY8KCwqikgwNsQ6OqAaODxC5Ea6PErG/E4e1hxS5xhW8hxa/sRfCqIcYxrkZyYYTzLEWhsOFGdO5FNaGGtkbHIXdgx0e4Bjj19kNu4TqgrjgyPCFH/IS9dAGVQAHAcQ+QyHkiQDoiRAIgh0OGoKVbcSgTpZIDCpBkB4vEydQOOKQIpuKQSs8rRjEAlwLdIdcvIDxSECMmzJMHJohj1ABEiRWDiJI41ANGzeS4jiU46bTGDpyGNrB7IOkh8ZYGOKRIKnXGzwMmXj6dEUPQghKNkhhKpKCFv65WrQa5OPH1680CzUl6xRIEEIcJkyAKYkDBQqEAQgZcvdrWENE+D4tYkRiD6SNvR55hCSJ5JtAzsLzwTjzDRs1JClZ8lknPBimh7zY1MMmXyT7XGRm4sOViSZPl4geR/prgrzJnHhuUvlgj642XEicTr269evWnkCJsqS7d+9RpFyvWzqzjdkWvqv/vq36EdN3X4BYTz+K9fLwT8+nr37K/fxe2UAFf+pRYd17AN6A3hQETmHgdUdgZt5s2FVo4YUYjlOFFVdgkZpzWZwmnTVaYHHFiVdswcU+xXl1nCscdIHijM2No1tjOPSmyQJbzDijFvvAlplskhjBoY8oqv7IIn53ofbIEEjO6MWH+/TwRX6bGcJBlCduAQYhPoQRho6ShDkmIYvB9xghWnA5xIqD9FDaEMM9ImdSdIKJYGPIDbKlj2IkJsiVLkrSlVdZbHWoY4fIeOIYQBoiRGN0/TDEeYQ0JsQhMEjIxCNaLFCFIy169cUgeyo4CKFeDUFmITL1KUqqSY0IAH42DHLjV1lWx0NjCWR6FyGLerXmdMUm1SelgwhpXHU1NPapsF8VwgSf1EmI56sAMDsID0zmKlG08Rni7SAvNEYlPOl+JW4h5w6iraoHOZvUuoPEKwi5Xsk6zp7TmjvsIdcm1atlMNQJ78CH9JBwhoXgCnEoqQtSOLEmL9iA6XSBAAAh+QQJCgBmACwAAAAAPAA8AIb/////9fT9raj7gHf9trH9raf7bmT7d23+5eP+3dr+4uD+2db+sq38dm39qqT9n5j7Zlv9qaP+3Nr+1NH+z8v+2Nb9urX9iID8eW/9sqz9h3/8f3b/9vX+6+r8cGb9mJH8i4P/7u39mZL+y8f7f3b9xcD9qKL/39z9vrr8fnX8kIj8gnn7b2X+9PT+wr79r6n+1tT9rqj8job+w7/9u7b+zMn7cmj7XlP8mpP+7u38npf8j4f/6+n8hn7+t7L/9vb9pJ78hn39xsL9uLP7aF39lo/+5uT/4N7+1tP8jYb7cWf+vrr8hX37Z139npf+zsv/+vn+0s/9ubP+19T++/v/5OP+z8z+1dL+5+X+4+L9tK79ubT/7ez+29j/8/L/+fn+4d/+7+7+19X/6un/8O//4d8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/oAAgoOEhYaGAQIDAwSHjo+QkZAFBpUGBZKZmpsHlgYDm6GRCAkIj56VjwoLCqKSDA2xDo6oBo4PELkRro8Ssb8Th7WHFLnGFbyHFr+xF8KohxjGuRnJhhPMsRaGw4UZ07kU1oYa2Rschd2DHR7gGOPX2Q27hOqCuODI8IUf8hL10AZVAAcBxD5DIeSJAOiJEAiCHQ4agpVtxKBOlkgMKkHQhLUTKMQd4pAim4pBBDw1ErQCHIsWjly8gPEoRoqbMk4cmiGPUAESJFYKIkjjUA0bN5LiOJTjptMUOnIY2sHsg6SHxlYY4tEjqdcbPAydePrUxw9CCEo2SGEqkgIW/rlYtBoUAMjXrzQLNSXrNIgQQhwmTECXqQUFCjAHDSFy92tYQwT4Pi1iROIRpI29AnmEJInkm0HOwgvAOPMNJTUkLWHyWSc8GKabxNj0wyZfJPtcZMYRwNUJJ0+ZiB4XoMlXJnmTPfHspPLBI0xOu5BIvbr169itQYki5YX379+lTMFOpUoUK+jTp7+CBUAU8PDBR7meRb199Vi0xN8v5fr5+/ddod9+8G3hH4ABTkEgfONZVx+C+Lm3xYJbNEjfFRCyl92GHHboITxcYNgFYftwUIEVV3AxjhddqBcFFftQ8R96XXjhyhdgABjGPlwACAaMmmAxo302wuMFglG0/hdJGBgiCeQ4Mma4oyMPIigGifBwIAaEVmRxyBcQRjEGIVSQQcaTkZR5JiFjDGnfF4YcCWAWaHLwXxRYOmIneniSWSWRhoBpXxdwFrJlel1I0mJ6+hDyxaLqFVpIGesVWcgY9w1SXhQaDnLfmHE2aUUZj3iBhYqHRKmeGIP8qSQAh6b3oiNcYGGpK3+ih6p76l0xSI/2eXmdoOol6ql9hECanqTUKYverZkOImex1oVxH6mERDsIpYBSJyqfaAKgrSBfuOmrRNba9+qx6hmCxX1T7vNur4eMO8i3Vqy7on3xZousIemmdys8VWJriL3bpicsdRx4kae/7erp8IeFEAx5LsWauIpxKFhc0alEgQAAIfkECQoAZQAsAAAAADwAPACG//////b1/bKs/YiA/ru2/rKt/HZt/H51/ubk/t7c/uPh/tvY/bax/a6p/aSe+25k/a2o/9/c/tbT/tHO/trY/r66/I6G+392/I2G/IZ9//b2/+zr+3du/a6o/Z6X/JKK/u7t/J6X/s7L/Yd//cjE/a2n/urp/tfU/uHf/IV9/ZaP/bq1/ImB+3dt//X0/cXA/bOt/tjW/s3J/a+p/bmz/cbC/b+7/s/L/Hlw+2Zb/aCZ/cO+/ZaO/uvq/IyE/tLP//r5/vT0/amj/bSu/tzZ/bu2/HBm/b65/u/u/uLg/aii/uXj/bm0/Hlv+29l/Z+Y/sO+/IuD/uDe/vv7/+Tj/tXS/s/M/ufl+2hd+15T+3Jo/uPi+2dd+3Fn/+3s//Py//n5/tfV/+rp//Dv/+HfAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB/6AAIKDhIWGhgECAwMEh46PkJGQBQaVBgWSmZqbB5YGA5uhkQgJCI+elY8KCwqikgwHsQ2OqAaODg+5EK6PEbG/Eoe1hxO5xhS8hxW/sRbCqIcXxrkMyYYSzLEVhsOFDNO5E9aGGNkZGoXdgxsc4Bfj19kHHenQhLjgyPCFHvIRhOoAUAD34MM+QyDkhQBoT9AHghsOGoKVTcSgTpZGDCJBsIQ1EydQONKQIpuKQSs8rRjEAlwLF45ewIjxSMaMmzRMHKohj1CBESNWDiJo49ANHDmS6jjk4qbTGTtgFuLBzIOkh8ZYGOrhI6nXHD0MmXj69AcQQghKHkhhKpKCFv65WrQaFETI1680CzUl63QIEUIaJEhAl8nFhAlSBRUxcvdrWEM/+D49gkRiEqSNvSp5tISJ5JtDzsILwjhzjiY3JJ0Y8lknvBimnQjYBMQm3yX7XmR+EsSVCShPQ+8L4uRrlLzJpHiGUvlgkiinX0icTr269evWplCpYqW7d+9Vrlx34QBLlvPo0WuZAWDL9/fft1h3kL5++g4/4OuvYp2L/f9a5Kffe/xV599/9XVxxYDviVcdfQje1x53A4aHnQNdRNgFPdh16OGHII7jBXcLBCBRADFYUYUX43yxwHc/TLHPFAJ2t8AXroCBgn7NiagfCjJqckWN7+EIzxcD/v7gICRIUKhfjDMSSWCPhrjHYBgmHhRAGAxaIZ8hYDD4gxiETDHGGEFKYiaahIgh5XdgGIKkflukCUAAAv6QJSR4dqdnmVbCZyQhYb63QJyFcOndApK86B1yg4DhKJyHkAHeoG3CN4h2P1g4CHxkyukkGY98cQWLh9D4XhiDBGrFkop6B+UhXlyBqSiudocqAEQWCIAX8H1ZXaHfMfrpe4RM6h2i1CnbHaaaDjJnsdUhAR+phEQ7iKVFUuekn3YCoK0gYEjp6z7WNmjIuIIs+B6V47gL3iHsCvLtqxJN2x28gtQLQLre3TpOoNiui2yl3gl74hd7GvzdIwEwHGIhvQ5OHIqrS1qcyRVVeCpRIAAh+QQJCgBnACwAAAAAPAA8AIb/////9vb+t7L8job9vrr9trH8fnX8hX3+7+7/6+r+5eP+3dr+0s/9r6n+zcn9qaP8dm39sqz+6un+1NH+3Nr+19T9ubP9h3/9urX9ubT9tK7/+vn/7u38f3b+w779mJH/9fT9w77+4N78jYb+y8f+sq3/8/L+5+X/7ez9vrn+vrr8j4f/9vX9yMT+2tj/4d/+z8z+1dL+29n+w7/+0c77gHf7bmT9pZ7/+fn+29j+vrn/7Ov8kov/5OP++/v9raj+6uj9v7v7d27+4+L+2Nb+4+H9raf7f3b7d239pJ78kor+9PT8cGb7Zlv8eXD9s639qKL7b2X8eW//4N77fHP7XlP7cmj7aF38hn7+1tT+4d/+wr78mpP7Z13+zMn+19X7e3L/6un/8O/9uLP/6+n7cWf9rqgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/oAAgoOEhYaGAQIDAwSHjo+QkZAFBpUGBZKZmpsHlgYDm6GRCAkIj56VjwoLCqKSDA2xDo6oBo4PELkRro8Ssb+thrWHE7nGFLyHFb+xFofDhhfGuRjJhgrMsRXCqIYY07kT1oYZ2RobhdCDHB3gF+PX2Q2zhOqCuODI8IUe8hL13QZRAAfhwz5DIOSFAOiJ0AeCHA4agpVNxKBOlkYMIkGwhDUTJ1A42qAhW4pBKjypGLQCnAEWjloUcPHoBYybMUwckiGPUIERI1YOIjjjEI0aNpLeOITjplMYOXAY0sHMg6SHxlYY2sEjqVcbOwyZePq0hw9CCEo20GAqkgID/rkMBBME4sfXrzQLNSXrlAEQQhsUKECXicWECTAHBRFy92tYQ0P4PiWSeF8RpI29GnmEIIbkmwzOwgPBOLONIzQknWDwWSc8F6aRYNLkwybftuNaZE4CwpWJHE9D7wOB5KuSvMlQeM5RGV4RJadbSJxOvbr169Z89PD8OcaJ60seMGlCvnx5J08ARP78dIh1KObjm4/Amr3TGNajyN/vpL59GPhVp99+8Ulxwn83fVcdfATOpx53knmHHRRSNCjFLthlqOGGHI6jHFTNjTMFFVVYUdRHwPUl2jggXFHFi1VgkYUrOGghGW7WbAHjjlz0pslqre2TxY47XtGAJJ2x/ifcaF0QuaMVXjyy3mdfhGjNFGA4uWMSTCkZBiE+iCHGipGEOSYhYzSpZRVkiPXZEGSyUB8DVhoiJ2jNgZDEmjPqxVdUhnzxVA6SpHgTEYaQcYCTbRpiG4CuFRIGX4Nox4CEg/D1pSFZlAEjF4+AJNIhPvh30xeDTAmDggAIGhyZhGxhRp+8qHrTqACYGiAAKPDlnnV7DUoIpYMY6pRU1Rl7U6QAECvIWGQRSh0CfL1QiLOCPOoUs/tACBqs2AKAg6kATkctWaxmSpYhB5KF4zjt3ndIuIJ4u6pE0Dr1riD0AnDuttNNae286x7y6K/TsWBCnfwWfIjCDGuoa4eiDKiaLsWZnBADphIFAgAh+QQJCgBtACwAAAAAPAA8AIb/////+vn+0s/9ubP+19T9r6n9tK7/9vX+8/L+5uT+3tz+4+L+z8z+4d/9rqj8fnX+t7L/8/L+7+7+1tP/39z+5+X+1dL8jYb+vrr++/v+7u38hn3/4d/+29j9npf/+fn/7ez9ubT+zsv9trH+2Nb9lo78hX3/9vb+y8f+u7b+3Nr/6un9xsL+1NH9iID8dm39qqT+19X/7u39mZL/5OP9sqz+6uj+w7/8f3b+5eP+sq39h3/9urX9qaP9mJH/9fT9raj7d277bmT7gHf9raf7d237f3b+4uD8g3r7Zlv8eXD9xcH+9PT8cGb8jIT9xcD9oJn9s637b2X+z8v8gnn9qKL/8O/+zcn9u7b+6+r/4N77aF37XlP7fHP8i4P9uLP8eW/9n5j7e3L+wr79pJ7/6+n+1tT8hn78mpP7Z137cmj+zMn7cWcAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/oAAgoOEhYaGAQIDAwSHjo+QkZACBZUFApKZmpsGlgUDm6GRBwgHj56VjwkKCaKSCwyxDY6oBY4OD7kQro8Rsb8Sh7WHE7nGFLyHFb+xFsKohxfGuRjJhhLMsRWGw4UY07kT1oYW2QIZhd2DGhvgF+PX2Qwc6dCEuODI8IUd8hGE6gBQAPfAwz5DH+R1AGhPkAeCGg4agpUNxKBOlkIMEkFwhLUIFSweyiAgG4lBBDw1ElQCnIkTjlCkUPGIwy8L/wytkEdIQIgQKwURZHGohYsXSGEcSpitwwdDMZgtjPTQWAlDMmYg3fpChiFf8hjQQDfoQEkGAkxFSmAil4lW/mVrcOVKsxDTsAJsEMogQQJZSScmTIA56AaOuVy9Tgz7i4TafTmOIt6q45GEcozP7TtwePKLHS0kVTjrb58Kzw94bMpgU14weCgm93i8KUK/X5rhHXjA1UfdZCDKdaA9LoePzygkKl/OvLlzaxloYA5rYVvzH0CCCNnOnfsQjxQZ/1rQnEj3891HkBbPwBnzIujjD1kv3v1y+PHPG1nGXlv5/OeBN5081T1HhBEAGuHRcww26OCD4wTHwHASHYFEEkosMY5tzOQ2DhNNJCFiEk6cJMoHDYT12jhPjOgiFExsMhpjOY1DgosuNhGFJJeJ56E1TEiBo4tKTPFIeGHF/kCcNUdQMaSLVSzl4wp7WWHFX5H8cMUVPxCChZBPJpHFV4wtgKVZsaQliRZbcMHFFloQwkQVYZpIyF2/OAWVVJKY4KabXRiShRdDjmlIa+3VSMhO2QwSnQAFDvLnn18cQgIYI4bRS0iOkJRNDIMgaR0AYkz6ZpeHPFGDna4gGYtIAKxn3ximckGGc3jGMpUgPA3ip6llNHcbM4r2KogZtZ7BHDbZ0EOIsYKgUasZyw2IFpYAQAtAGWmYqoZyzDIz6iDaAuBArWtIxN9Nh5QLABumOiARWMC026gha5hKrUThOWuIuwBI6+atyx0QwZLk3nuIFmbECWFPzNj3cCaiCU4cSgUWRCpRIAAh+QQJCgBqACwAAAAAPAA8AIb////++/v/5OP+1dL+5+X+4+L+z8z+0s//9vX+8/L+7+7/6+r+4d/+zcn9r6n/8/L+5eP+6un9ubT+19T/9fT9tK7/4d/+29j+w77/+fn/7ez+4N7+2Nb+vrn/+vn+zsv9vrr/39z/6un+29n+1tP8job8fnX9rqn+19X+7u38npf/9vb+t7L+6uj9xsL8hn3+5uT9trH8jYb8hX3+vrr9rqj9npf9sqz8f3b8dm39iID+u7b+sq39urX9h3/+4+H8ioH7bmT7gHf9yMX7d278kov+2tj9yMT9pZ77d23+0c78iYH9raf/8O/+9PT+0Mz9v7v/7Ov+4uD8cGb7Zlv8i4P8g3r8kor9pJ77aF37cmj9u7b7f3b7XlP8gnn7Z139xcD9qKL+6+r8jIT9n5j7b2X8eXD+z8v8eW/7cWcAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/oAAgoOEhYaGAQIDAwSHjo+QkZAFBpUGBZKZmpsHlgYDm6GRCAkIj56VjwoLCqKSlJUMjqgGjg0OuAeujw+orYa0hxC4xBG7hwSooMCohxLEuBPHhgq0jYXBhRPQuBDThgOoBwHYzYQUFdwS39S0FuWehbfcxuyFF7QPhNmCEdwOGOwZykDrwj5zgjD8oyDQECxPGgZ1srQMwIZ/uo49IBDxUICJljgMSmbpGoAO3Cp4cPQBRIhHFijqMySCH4ACi0wC+DfiEIkSJoKeOEQQ1YUMhlB4MhhJIbEOhlKoCErVRApDvWgZEEBuEIKJB0xFUpDOQYVfglawqFr1ZaGi/loPtCAUQIGCrpI8QICwcpCLF2yrXnWoNaRYezCABqYaQ1W4wuPsrQC82IQMEpIIgEQ181uIyjNobAoQkxbaaR8W11jh6gE+S5HZrZhR1YbbYxrCXTjMDoYNyx8aCh9OvLjxaYkea2VkHMENHDmiS5euY8fNwqgwEecxvfv0HpuxVxRuwrt5HeELj29Y3nx3HySxV9LZkLv77zeV02JunIeP+z70cNyABBZoIDu5GbBbQz8AEYQQQ3zjmiexfUMBEUFkGEQRRriSAQNanXbMERqWiARDmmhWWGfTGFFiiUQ0NpZ+4uA1DQVJvFiiEEo88pBWKPD2zQ9L6FgiE0Rh/neACHQ10YSNkDjxxBNOEAJFjkYGEQVWhRVg41eVhCWJFFNQQcUUUpzDRJYdvlUQUoUoZQlTkFRhpplWGBLFFTpuaUhpn7A4SE0IUYBFFlo4QMidd25xiBFcaIgFLxw58hEqKAyCRRecdlHDIF4wemaVhxwRQ5u7/FhJRwB80WkXWgwChqhUhGEcXHMS8iqnhNgpqhjFveYJi7t2QQgHtI5BXDWovKPrroWQQatIwtFYoSDFFiJGGaKaIRyzntAHQLaF3EDrGQ3FV8l62EJrCBqi3tBQVpaI2O6rh5whKrUCPeSsIeQaIq2Ztg6HwANCPouvI1JwkOaBhLjaaRoQEIeyaaefVrxJDWmkkXFDgQAAOw==';
            
            /***********************
             * ページ読込完了後の処理
             ***********************/
            google.setOnLoadCallback(function() {
                $("#result").html('<img src="' + base64img + '" />');
                //google spreadsheetの情報を取得しdispCalendar関数を実行する
                var query = new google.visualization.Query(dataSource);
                query.setQuery(queryLanguage);
                query.send(dispCalendar);
            });
            
            /***********************
             * カレンダテーブル表示
             ***********************/
            function dispCalendar(response){
                var el = document.getElementById('result');

                if(response.isError()) {
                    //エラーの場合はエラーメッセージを出力して処理を終了する
                    el.innerHTML = response.getDetailedMessage();
                    return;
                }

                //テーブルデータ
                //※indexは下記の通り
                //日付:0
                //曜日:1(未使用)
                //時間:2
                //名称:3
                //リンク:4
                //最寄駅:5
                //住所:6
                //施設名:7
                var data = response.getDataTable();

                //日付フォーマットパターン
                var formatter1 = new google.visualization.DateFormat({pattern: 'M/d(E)'});

                //日付と時間を結合するパターン
                var formatter2 = new google.visualization.PatternFormat(
                    '{0}<br /><span style="font-size: x-small;">{1}</span>');
                //名称とリンクを結合するパターン
                var formatter3 = new google.visualization.PatternFormat(
                    '<b>{0}</b><br /><span style="font-size: x-small;">' +
                    '<a href="{1}" target="_blank">{1}</a></span>');
                //住所情報を結合するパターン
                var formatter4 = new google.visualization.PatternFormat(
                    '最寄駅：{0}<br /><span style="font-size: x-small;">' +
                    '<a href="https://maps.google.co.jp/maps?q={1}" target="_blank">' +
                    '{1}<br />{2}</a></span>');

                //各パターンを適用する
                formatter1.format(data, 0);
                formatter2.format(data, [0, 2]);
                formatter3.format(data, [3, 4]);
                formatter4.format(data, [5, 6, 7]);
                
                var today = new Date();
                var tmpToday = new Date();
                var yesterday = new Date(tmpToday.setDate(today.getDate() - 1));

                //日付によってセルの色を変えるパターンを適用する
                //今日以前の日付は灰色・今日日付はピンク
                var formatterC = new google.visualization.ColorFormat();
                formatterC.addRange(0, yesterday, null, 'lightgrey');
                formatterC.addRange(yesterday, today, null, 'pink');
                formatterC.format(data, 0);
                
                //列名を設定する
                data.setColumnLabel(0, '日時');
                data.setColumnLabel(5, '会場');
                
                //曜日フォーマットの変換
                for(var i = 0; i < data.getNumberOfRows(); i++){
                    var dispDate = data.getFormattedValue(i, 0)
                        .replace('Sun','日')
                        .replace('Mon','月')
                        .replace('Tue','火')
                        .replace('Wed','水')
                        .replace('Thu','木')
                        .replace('Fri','金')
                        .replace('Sat','土')
                        .replace('土','<span style="color: blue;">土</span>')
                        .replace('日','<span style="color: red;">日</span>');
                    data.setFormattedValue(i, 0, dispDate);
                }

                //表示する列を指定
                var view = new google.visualization.DataView(data);
                view.setColumns([0, 3, 5]);
                
                //テーブルを表示
                var visualization = new google.visualization.Table(el); 
                visualization.draw(view, {allowHtml: true});
            }

            //-->
            //]]>
        </script>
    </head>
    <body>
        <div id="result" ></div>
    </body>
</html>

★まとめ

今回の対応により、スプレッドシートにボドゲ会情報を登録するだけでブログのカレンダが自動更新されます。今までは、htmlを直接編集していたのでかなりの負荷軽減になりました。

あとはtwitter BOTへの登録が未だ手動なのがちょっと面倒です。理想はスプレッドシートに登録した情報を自動でつぶやいてくれればいいんですが。

ちょくちょく時間を見つけて改善していきたいと思います。

ぼどレポ

ページ

2014年2月11日火曜日

Google Visualization API を使ったボドゲ会カレンダの更新