ここの情報は古いです。ご理解頂いた上でお取り扱いください。

Changeset 3738


Ignore:
Timestamp:
Aug 21, 2007, 1:20:30 PM (12 years ago)
Author:
ogawa
Message:

#1048:PostgreSQL対応

Location:
OpenPNE/trunk
Files:
14 edited
13 copied

Legend:

Unmodified
Added
Removed
  • OpenPNE/trunk/bin/tool_rss_cache.php

    r2641 r3738  
    117117        $values = implode(",", $values);
    118118
    119         if ($values_list != '') $values_list .= ",";
    120         $values_list .= "($values)";
     119        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     120            $values = "($values)";
     121            $sql = "INSERT INTO c_rss_cache" .
     122                    "(c_member_id, subject, body, r_datetime, link, cache_date)" .
     123                " VALUES $values";
     124            db_query($sql);
     125        } else {
     126            if ($values_list != '') $values_list .= ",";
     127            $values_list .= "($values)";
     128        }
    121129    }
    122130
    123     $sql = "INSERT INTO c_rss_cache" .
    124             "(c_member_id, subject, body, r_datetime, link, cache_date)" .
    125         " VALUES $values_list";
    126     db_query($sql);
     131    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] != 'pgsql') {
     132        $sql = "INSERT INTO c_rss_cache" .
     133                "(c_member_id, subject, body, r_datetime, link, cache_date)" .
     134            " VALUES $values_list";
     135        db_query($sql);
     136    }
    127137}
    128138
  • OpenPNE/trunk/bin/tool_vacuum_exec.cron

    r3737 r3738  
    22cd $1
    33$2 tool_vacuum_exec.php
    4 
  • OpenPNE/trunk/bin/tool_vacuum_exec.php

    r3737 r3738  
    1212    db_query($sql);
    1313}
     14
    1415?>
  • OpenPNE/trunk/lib/include/DB/pgsql.php

    r2 r3738  
    496496            return $in;
    497497        } elseif (is_bool($in)) {
    498             return $in ? 'TRUE' : 'FALSE';
     498//            return $in ? 'TRUE' : 'FALSE';
     499            return $in ? 1 : 0;
    499500        } elseif (is_null($in)) {
    500501            return 'NULL';
  • OpenPNE/trunk/setup/OpenPNE_Setup_pgsql.html

    r3737 r3738  
    216216    使用データベース、接続用ユーザー名、パスワード、サーバホスト名、データベース名を設定します。<br>
    217217    config.php
    218     <pre>
    219     // 使用するデータベースにPostgreSQLを指定。
    220     'phptype'  => 'pgsql'</pre>
    221     <dd>
     218<pre>
     219// 使用するデータベースにPostgreSQLを指定。
     220'phptype'  =&gt; 'pgsql'
     221</pre></dd>
    222222<dt>ENCRYPT_KEY</dt>
    223223        <dd>メンバーのログイン情報を暗号化する際に使用するキー(56バイト以内のASCII文字)<br>
     
    271271</pre>
    272272</div>
     273
    273274
    274275<h2 id="section4">4. サーバ設定</h2>
     
    435436<em>$GLOBALS['_OPENPNE_DISABLE_MODULES'] = array('pc', 'ktai');</em>
    436437?&gt;
    437 </pre>l
     438</pre>
    438439</div>
    439440
  • OpenPNE/trunk/webapp/lib/db/banner.php

    r1856 r3738  
    1818        $sql .= ' AND is_hidden_before = 0';
    1919    }
    20     $sql .= ' ORDER BY RAND()';
     20    $sql .= db_order_by_rand();
    2121
    2222    return db_get_row($sql);
     
    3636        $sql .= ' AND is_hidden_before = 0';
    3737    }
    38     $sql .= ' ORDER BY RAND()';
     38    $sql .= db_order_by_rand();
    3939
    4040    return db_get_row($sql);
  • OpenPNE/trunk/webapp/lib/db/bookmark.php

    r2853 r3738  
    161161
    162162    $sql = 'SELECT c_member_id_to AS c_member_id FROM c_bookmark' .
    163             ' WHERE c_member_id_from = ? ORDER BY RAND()';
     163            ' WHERE c_member_id_from = ?' . db_order_by_rand();
    164164    $params = array(intval($c_member_id));
    165165
  • OpenPNE/trunk/webapp/lib/db/common.php

    r1858 r3738  
    171171}
    172172
     173/**
     174 * MySQL: ORDER BY RAND()
     175 * PgSQL: ORDER BY RANDOM()
     176 */
     177function db_order_by_rand()
     178{
     179    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     180        $order = ' ORDER BY RANDOM()';
     181    } else {
     182        $order = ' ORDER BY RAND()';
     183    }
     184    return $order;
     185}
     186
    173187?>
  • OpenPNE/trunk/webapp/lib/db/commu.php

    r3718 r3738  
    493493    $sql = 'SELECT c_member.* FROM c_member, c_commu_member' .
    494494            ' WHERE c_member.c_member_id = c_commu_member.c_member_id' .
    495             ' AND c_commu_id = ? ORDER BY RAND()';
     495            ' AND c_commu_id = ?' . db_order_by_rand();
    496496    $params = array(intval($c_commu_id));
    497497    $lst = db_get_all_limit($sql, 0, $limit, $params);
     
    515515    $is_recurred = false;
    516516
    517     $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id " .
    518             " , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 " .
    519             " FROM c_commu_topic_comment as cctc , c_commu_topic as cct" .
    520             " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id " .
    521             " AND cct.event_flag = ?".
    522             " AND cct.c_commu_id = ?".
    523             " group by cct.c_commu_topic_id " .
    524             " order by r_datetime desc ";
     517    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     518        $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id" .
     519                " , max(cctc.image_filename1) as image_filename1, max(cctc.image_filename2) as image_filename2, max(cctc.image_filename3) as image_filename3 " .
     520                " FROM c_commu_topic_comment as cctc , c_commu_topic as cct" .
     521                " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id " .
     522                " AND cct.event_flag = ?".
     523                " AND cct.c_commu_id = ?".
     524                " group by cct.c_commu_topic_id, cct.name, cct.c_commu_id " .
     525                " order by r_datetime desc ";
     526    } else {
     527        $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id " .
     528                " , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 " .
     529                " FROM c_commu_topic_comment as cctc , c_commu_topic as cct" .
     530                " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id " .
     531                " AND cct.event_flag = ?".
     532                " AND cct.c_commu_id = ?".
     533                " group by cct.c_commu_topic_id " .
     534                " order by r_datetime desc ";
     535    }
    525536    $params = array((bool)$event_flag, intval($c_commu_id));
    526537    $list = db_get_all_limit($sql, 0, $limit, $params);
     
    619630        " WHERE c_commu_member.c_member_id = ?".
    620631        " AND c_commu.c_commu_id =  c_commu_member.c_commu_id" .
    621         " ORDER BY RAND()";
     632        db_order_by_rand();
    622633    $params = array(intval($c_member_id));
    623634    $lst = db_get_all_limit($sql, 0, $limit, $params);
     
    805816
    806817    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
    807     $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'.
    808         ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
    809         ' GROUP BY cctc.c_commu_topic_id'.
    810         ' ORDER BY r_datetime DESC';
     818    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     819        $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id'.
     820            ' FROM (' .
     821                    ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime' .
     822                    ' FROM' .
     823                        ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint .
     824                    ' WHERE ' .
     825                        ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
     826                    ' GROUP BY cct.c_commu_topic_id' .
     827                    ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct' .
     828            ' WHERE' .
     829                ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id' .
     830                ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id ' .
     831                ' AND cctc.r_datetime=sub_cct_tbl.r_datetime' .
     832            ' ORDER BY r_datetime DESC';
     833    } else {
     834        $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'.
     835            ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
     836            ' GROUP BY cctc.c_commu_topic_id'.
     837            ' ORDER BY r_datetime DESC';
     838    }
    811839    $c_commu_topic_list = db_get_all_limit($sql, 0, $limit);
    812840
     
    851879
    852880    $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)');
    853     $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'.
    854         ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
    855         ' GROUP BY cctc.c_commu_topic_id'.
    856         ' ORDER BY r_datetime DESC';
     881    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     882        $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id'.
     883            ' FROM (' .
     884                    ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime' .
     885                    ' FROM' .
     886                        ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint .
     887                    ' WHERE ' .
     888                        ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
     889                    ' GROUP BY cct.c_commu_topic_id' .
     890                    ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct' .
     891            ' WHERE' .
     892                ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id' .
     893                ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id ' .
     894                ' AND cctc.r_datetime=sub_cct_tbl.r_datetime' .
     895            ' ORDER BY r_datetime DESC';
     896    } else {
     897        $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'.
     898            ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
     899            ' GROUP BY cctc.c_commu_topic_id'.
     900            ' ORDER BY r_datetime DESC';
     901    }
    857902    $c_commu_topic_list = db_get_all_limit($sql, ($page-1)*$limit, $limit);
    858903
     
    11731218    $is_recurred = false;
    11741219
    1175     $sql = 'SELECT DISTINCT c.* FROM c_commu_member AS cm, c_commu AS c' .
    1176             ' WHERE cm.c_member_id = ? AND c.c_commu_id = cm.c_commu_id' .
    1177             ' ORDER BY RAND()';
     1220    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1221        $sql = 'SELECT DISTINCT c.*, RANDOM() FROM c_commu_member AS cm, c_commu AS c' .
     1222                ' WHERE cm.c_member_id = ? AND c.c_commu_id = cm.c_commu_id';
     1223    } else {
     1224        $sql = 'SELECT DISTINCT c.* FROM c_commu_member AS cm, c_commu AS c' .
     1225                ' WHERE cm.c_member_id = ? AND c.c_commu_id = cm.c_commu_id';
     1226    }
     1227    $sql .= db_order_by_rand();
    11781228    $params = array(intval($c_member_id));
    11791229    $c_commu_list = db_get_all_limit($sql, 0, $limit, $params);
     
    13871437    $sql .= "WHERE ccm.c_commu_id = ?" .
    13881438            " AND ccm.c_member_id = cm.c_member_id";
    1389     $sql .= " ORDER BY RAND()";
     1439    $sql .= db_order_by_rand();
    13901440    $params = array(intval($c_commu_id));
    13911441    $c_commu_member_list = db_get_all_limit($sql, 0, $limit, $params);
     
    14581508    $sql = 'SELECT c_member_id, nickname FROM c_member' .
    14591509            ' WHERE c_member_id IN ( '. $result . ') ' .
    1460             ' ORDER BY RAND()';
     1510            db_order_by_rand();
    14611511    return db_get_all_limit($sql, 0, $limit);
    14621512}
     
    16881738        $from  = ' FROM c_commu, c_commu_member';
    16891739        $order = ' ORDER BY count_commu_member DESC';
    1690         $group = ' GROUP BY c_commu_member.c_commu_id';
    1691         $sql = $select . ', COUNT(c_commu_member.c_member_id) AS count_commu_member' .
    1692             $from .
    1693             $where . ' AND c_commu_member.c_commu_id = c_commu.c_commu_id' .
    1694             $group .
    1695             $order;
     1740        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1741            $sub_tbl = ' (' .
     1742                            'SELECT c_commu_member.c_commu_id, count(c_commu_member.c_commu_member_id) as count_commu_member' .
     1743                            $from .
     1744                            $where . ' AND c_commu_member.c_commu_id = c_commu.c_commu_id' .
     1745                            ' GROUP BY c_commu_member.c_commu_id' .
     1746                        ') as sub_commu_tbl';
     1747            $sql = $select .
     1748                    ' FROM' . $sub_tbl . ', c_commu' .
     1749                    ' WHERE c_commu.c_commu_id = sub_commu_tbl.c_commu_id' .
     1750                    $order;
     1751        } else {
     1752            $group = ' GROUP BY c_commu_member.c_commu_id';
     1753            $sql = $select . ', COUNT(c_commu_member.c_member_id) AS count_commu_member' .
     1754                $from .
     1755                $where . ' AND c_commu_member.c_commu_id = c_commu.c_commu_id' .
     1756                $group .
     1757                $order;
     1758        }
    16961759        break;
    16971760    }
     
    17971860function db_commu_c_topic4c_commu_topic_id_2($c_commu_topic_id)
    17981861{
    1799     $sql = "SELECT cct.*, cctc.*, cm.nickname, cpp.pref" .
    1800         " FROM c_commu_topic as cct" .
    1801         " LEFT JOIN c_commu_topic_comment as cctc ON cct.c_commu_topic_id = cctc.c_commu_topic_id" .
    1802         " LEFT JOIN c_member as cm ON cct.c_member_id = cm.c_member_id" .
    1803         " LEFT JOIN c_profile_pref as cpp ON cct.open_pref_id = cpp.c_profile_pref_id" .
    1804         " WHERE cct.c_commu_topic_id = ?".
    1805             " AND cctc.number = 0";
     1862    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1863        $sql = "SELECT cct.*, cctc.*, cm.nickname, cpp.pref, case when cct.invite_period = '0001-01-01 BC' THEN '0000-00-00' ELSE to_char(cct.invite_period,'YYYY-MM-DD') END as invite_period" .
     1864            " FROM c_commu_topic as cct" .
     1865            " LEFT JOIN c_commu_topic_comment as cctc ON cct.c_commu_topic_id = cctc.c_commu_topic_id" .
     1866            " LEFT JOIN c_member as cm ON cct.c_member_id = cm.c_member_id" .
     1867            " LEFT JOIN c_profile_pref as cpp ON cct.open_pref_id = cpp.c_profile_pref_id" .
     1868            " WHERE cct.c_commu_topic_id = ?".
     1869                " AND cctc.number = 0";
     1870    } else {
     1871        $sql = "SELECT cct.*, cctc.*, cm.nickname, cpp.pref" .
     1872            " FROM c_commu_topic as cct" .
     1873            " LEFT JOIN c_commu_topic_comment as cctc ON cct.c_commu_topic_id = cctc.c_commu_topic_id" .
     1874            " LEFT JOIN c_member as cm ON cct.c_member_id = cm.c_member_id" .
     1875            " LEFT JOIN c_profile_pref as cpp ON cct.open_pref_id = cpp.c_profile_pref_id" .
     1876            " WHERE cct.c_commu_topic_id = ?".
     1877                " AND cctc.number = 0";
     1878    }
    18061879    $params = array(intval($c_commu_topic_id));
    18071880    $lst = db_get_row($sql, $params);
     
    24042477        'r_date' => db_now(),
    24052478    );
     2479    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2480        $open_date = '0000-01-01';
     2481        $invite_period = '0000-01-01';
     2482        if (isset($topic['open_date']) && $topic['open_date'] != '') {
     2483            $open_date = $topic['open_date'];
     2484        }
     2485       
     2486        if (isset($topic['invite_period']) && $topic['invite_period'] != '') {
     2487            $invite_period = $topic['invite_period'];
     2488        }
     2489    } else {
     2490        $open_date = $topic['open_date'];
     2491        $invete_period = $topic['invite_period'];
     2492    }
    24062493    if ($data['event_flag']) {
    24072494        $data += array(
    2408             'open_date'         => $topic['open_date'],
     2495            'open_date'         => $open_date,
    24092496            'open_date_comment' => $topic['open_date_comment'],
    24102497            'open_pref_id'      => intval($topic['open_pref_id']),
    24112498            'open_pref_comment' => $topic['open_pref_comment'],
    2412             'invite_period'     => $topic['invite_period'],
     2499            'invite_period'     => $invite_period,
    24132500        );
    24142501    }
     
    24772564        'r_date'      => db_now(),
    24782565    );
     2566    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2567        $open_date = '0000-01-01';
     2568        $invite_period = '0000-01-01';
     2569        if (isset($topic['open_date']) && $topic['open_date'] != '') {
     2570            $open_date = $topic['open_date'];
     2571        }
     2572       
     2573        if (isset($topic['invite_period']) && $topic['invite_period'] != '') {
     2574            $invite_period = $topic['invite_period'];
     2575        }
     2576    } else {
     2577        $open_date = $topic['open_date'];
     2578        $invete_period = $topic['invite_period'];
     2579    }
    24792580    if ($data['event_flag']) {
    24802581        $data += array(
    2481             'open_date'         => $topic['open_date'],
     2582            'open_date'         => $open_date,
    24822583            'open_date_comment' => $topic['open_date_comment'],
    24832584            'open_pref_id'      => intval($topic['open_pref_id']),
    24842585            'open_pref_comment' => $topic['open_pref_comment'],
    2485             'invite_period'     => $topic['invite_period'],
     2586            'invite_period'     => $invite_period,
    24862587        );
    24872588    }
     
    25592660function db_commu_is_event_join_date($c_commu_topic_id)
    25602661{
    2561     $sql = 'SELECT c_commu_topic_id FROM c_commu_topic'
    2562          . ' WHERE c_commu_topic_id = ?'
    2563          . ' AND (open_date >= ? OR open_date = \'0000-00-00\')'
    2564          . ' AND (invite_period >= ? OR invite_period = \'0000-00-00\')';
     2662    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2663        $sql = 'SELECT c_commu_topic_id FROM c_commu_topic'
     2664             . ' WHERE c_commu_topic_id = ?'
     2665             . ' AND (open_date >= ? OR open_date = \'0000-01-01\')'
     2666             . ' AND (invite_period >= ? OR invite_period = \'0000-01-01\')';
     2667    } else {
     2668        $sql = 'SELECT c_commu_topic_id FROM c_commu_topic'
     2669             . ' WHERE c_commu_topic_id = ?'
     2670             . ' AND (open_date >= ? OR open_date = \'0000-00-00\')'
     2671             . ' AND (invite_period >= ? OR invite_period = \'0000-00-00\')';
     2672    }
    25652673    $now = date('Y-m-d');
    25662674    $params = array(intval($c_commu_topic_id), $now, $now);
     
    27942902            $c_commu_id = 0)
    27952903{
    2796     $select = 'SELECT c.name AS commu_name, c.image_filename AS commu_image'
    2797             . ', ct.*, MAX(ctc.r_datetime) AS max_datetime';
     2904    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2905        $select = 'SELECT distinct on (ct.c_commu_topic_id) c.name AS commu_name, c.image_filename AS commu_image'
     2906                . ', ct.*, ctc2.max_datetime';
     2907    } else {
     2908        $select = 'SELECT c.name AS commu_name, c.image_filename AS commu_image'
     2909                . ', ct.*, MAX(ctc.r_datetime) AS max_datetime';
     2910    }
     2911
    27982912    $from = ' FROM c_commu AS c, c_commu_topic AS ct, c_commu_topic_comment AS ctc';
     2913    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2914        $from .= ', (SELECT c_commu_topic_id, max(r_datetime) as max_datetime' .
     2915                    ' FROM c_commu_topic_comment' .
     2916                    ' GROUP BY c_commu_topic_id' .
     2917                    ') AS ctc2';
     2918    }
    27992919
    28002920    $params = array();
    28012921    $where = ' WHERE ct.c_commu_topic_id = ctc.c_commu_topic_id'
    28022922           . ' AND c.c_commu_id = ct.c_commu_id';
     2923
     2924    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2925        $where .= ' AND ct.c_commu_topic_id = ctc2.c_commu_topic_id';
     2926    }
     2927
    28032928    if ($c_commu_id) {
    28042929        $where .= ' AND ct.c_commu_id = ?';
     
    28312956    $order = ' ORDER BY max_datetime DESC';
    28322957
    2833     $sql = $select . $from . $where . $group . $order;
     2958    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2959        $sql = "SELECT sub_tbl.* FROM (" . $select . $from . $where . ") as sub_tbl " . $order;
     2960    } else {
     2961        $sql = $select . $from . $where . $group . $order;
     2962    }
    28342963    $list = db_get_all_page($sql, $page, $page_size, $params);
    28352964
  • OpenPNE/trunk/webapp/lib/db/diary.php

    r3718 r3738  
    567567    $friend_ids = implode(',', $friends);
    568568
    569     $sql = 'SELECT d.c_diary_id' .
    570             ', d.subject' .
    571             ', d.c_member_id' .
    572             ', MAX(dc.r_datetime) AS r_datetime' .
    573             ', COUNT(DISTINCT dc.c_diary_comment_id) AS num_comment' .
    574         ' FROM c_diary AS d' .
    575             ' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' .
    576             ', c_diary_comment AS mydc' .
    577         ' WHERE mydc.c_member_id = ?' .
    578             ' AND mydc.c_diary_id = d.c_diary_id' .
    579             ' AND mydc.c_member_id <> d.c_member_id' .
    580             ' AND d.c_member_id NOT IN (' . $except_ids . ')' .
    581             ' AND (d.public_flag = \'public\' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . ')))' .
    582         ' GROUP BY dc.c_diary_id' .
    583         ' ORDER BY r_datetime DESC';
     569    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     570        $sql = 'SELECT d.c_diary_id' .
     571                ', d.subject' .
     572                ', d.c_member_id' .
     573                ', sub_diary_tbl.r_datetime' .
     574                ', sub_diary_tbl.num_comment' .
     575            ' FROM c_diary AS d' .
     576                ', ( SELECT dc.c_diary_id' .
     577                        ', MAX(dc.r_datetime) AS r_datetime' .
     578                        ', COUNT(DISTINCT dc.c_diary_comment_id) AS num_comment' .
     579                    ' FROM c_diary AS d' .
     580                        ' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' .
     581                        ', c_diary_comment AS mydc' .
     582                    ' WHERE mydc.c_member_id = ?' .
     583                        ' AND mydc.c_diary_id = d.c_diary_id' .
     584                        ' AND mydc.c_member_id <> d.c_member_id' .
     585                        ' AND d.c_member_id NOT IN (' . $except_ids . ')' .
     586                        ' AND (d.public_flag = \'public\' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . ')))' .
     587                    ' GROUP BY dc.c_diary_id' .
     588                ') as sub_diary_tbl' .
     589            ' WHERE' .
     590                ' sub_diary_tbl.c_diary_id = d.c_diary_id' .
     591            ' ORDER BY r_datetime DESC';
     592    } else {
     593        $sql = 'SELECT d.c_diary_id' .
     594                ', d.subject' .
     595                ', d.c_member_id' .
     596                ', MAX(dc.r_datetime) AS r_datetime' .
     597                ', COUNT(DISTINCT dc.c_diary_comment_id) AS num_comment' .
     598            ' FROM c_diary AS d' .
     599                ' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' .
     600                ', c_diary_comment AS mydc' .
     601            ' WHERE mydc.c_member_id = ?' .
     602                ' AND mydc.c_diary_id = d.c_diary_id' .
     603                ' AND mydc.c_member_id <> d.c_member_id' .
     604                ' AND d.c_member_id NOT IN (' . $except_ids . ')' .
     605                ' AND (d.public_flag = \'public\' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . ')))' .
     606            ' GROUP BY dc.c_diary_id' .
     607            ' ORDER BY r_datetime DESC';
     608    }
    584609
    585610    $params = array(intval($c_member_id));
     
    789814
    790815    $pf_cond = db_diary_public_flag_condition($c_member_id, $u);
    791     $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_diary' .
    792            ' WHERE c_member_id = ? AND r_datetime >= ? AND r_datetime < ?' . $pf_cond;
     816   
     817    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     818        $sql = "SELECT DISTINCT date_part('day', r_datetime) FROM c_diary" .
     819               " WHERE c_member_id = ? AND r_datetime >= ? AND r_datetime < ?" . $pf_cond;
     820    } else {
     821        $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_diary' .
     822               ' WHERE c_member_id = ? AND r_datetime >= ? AND r_datetime < ?' . $pf_cond;
     823    }
    793824
    794825    $date_format = '%Y-%m-%d 00:00:00';
  • OpenPNE/trunk/webapp/lib/db/file.php

    r3458 r3738  
    5959function db_file_insert_c_file($filename, $bin, $original_filename)
    6060{
     61    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     62        $bin = base64_encode($bin);
     63    }
     64
    6165    $data = array(
    6266        'filename'   => $filename,
     
    7882    $sql = 'SELECT * FROM c_file WHERE filename = ?';
    7983    $params = array($filename);
    80     return db_get_row($sql, $params);
     84   
     85    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     86        $lst = db_get_row($sql, $params);
     87        $lst['bin'] = base64_decode($lst['bin']);
     88        return $lst;
     89    } else {
     90        return db_get_row($sql, $params);
     91    }
    8192}
    8293
  • OpenPNE/trunk/webapp/lib/db/friend.php

    r3057 r3738  
    258258
    259259    $sql = 'SELECT c_member_id_to AS c_member_id FROM c_friend' .
    260             ' WHERE c_member_id_from = ? ORDER BY RAND()';
     260            ' WHERE c_member_id_from = ?' . db_order_by_rand();
    261261    $params = array(intval($c_member_id));
    262262    if ($limit) {
     
    340340
    341341    $sql = 'SELECT * FROM c_friend' .
    342             ' WHERE c_member_id_to = ? AND intro <> \'\' ORDER BY RAND()';
     342            ' WHERE c_member_id_to = ? AND intro <> \'\'' . db_order_by_rand();
    343343    $params = array(intval($c_member_id));
    344344    $list = db_get_all_limit($sql, 0, $limit, $params);
     
    455455    $sql .= " WHERE cf.c_member_id_from = ?" .
    456456            " AND cm.c_member_id=cf.c_member_id_to";
    457     $sql .= " ORDER BY RAND()";
     457    $sql .= db_order_by_rand();
    458458    $params = array(intval($c_member_id));
    459459    $c_friend_list = db_get_all_limit($sql, 0, $limit, $params);
     
    598598    $sql .= " WHERE cf.c_member_id_from = ?".
    599599            " AND cf.c_member_id_to = cm.c_member_id";
    600     $sql .= " ORDER BY RAND()";
     600    $sql .= db_order_by_rand();
    601601    $params = array(intval($c_member_id));
    602602    return db_get_all_limit($sql, 0, 50, $params);
  • OpenPNE/trunk/webapp/lib/db/member.php

    r3718 r3738  
    2626    $is_recurred = false;
    2727
    28     $sql = 'SELECT * FROM c_member WHERE c_member_id = ?';
     28    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     29        $sql = "SELECT *,case when access_date = '0001-01-01 00:00:00 BC' THEN '0000-00-00 00:00:00' ELSE to_char(access_date,'YYYY-MM-DD HH24:MI:SS') END as access_date" .
     30             " FROM c_member WHERE c_member_id = ?";
     31    } else {
     32        $sql = 'SELECT * FROM c_member WHERE c_member_id = ?';
     33    }
     34
    2935    $params = array(intval($c_member_id));
    3036    if (!$c_member = db_get_row($sql, $params))
  • OpenPNE/trunk/webapp/lib/db/message.php

    r3681 r3738  
    795795function db_message_c_message_sender_list4c_member_id($c_member_id)
    796796{
    797     $sql = "SELECT distinct c_member_id_from FROM c_message";
    798797    $where = "c_member_id_to = ?".
    799798            " AND is_deleted_to = 0" .
    800799            " AND is_send = 1";
    801     $sql .= " WHERE $where";
     800
     801    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     802        $sql = "SELECT c_member_id_from" .
     803                " FROM" .
     804                    "(" .
     805                        "SELECT distinct on(c_member_id_from) *" .
     806                        " FROM" .
     807                            " c_message" .
     808                        " WHERE " . $where .
     809                    ") as sub_member_tbl";
     810    } else {
     811        $sql = "SELECT distinct c_member_id_from FROM c_message";
     812        $sql .= " WHERE $where";
     813    }
    802814    $sql .= " ORDER BY r_datetime DESC";
    803815    $params = array(intval($c_member_id));
     
    816828function db_message_c_message_receiver_list4c_member_id($c_member_id)
    817829{
    818     $sql = "SELECT distinct c_member_id_to FROM c_message";
    819830    $where = "c_member_id_from = ?".
    820831            " AND is_deleted_from = 0" .
    821832            " AND is_send = 1";
    822     $sql .= " WHERE $where";
     833   
     834    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     835        $sql = "SELECT c_member_id_to" .
     836                " FROM" .
     837                    "(" .
     838                        "SELECT distinct on(c_member_id_to) *" .
     839                        " FROM" .
     840                            " c_message" .
     841                        " WHERE " . $where .
     842                    ") as sub_member_tbl";
     843    } else {
     844        $sql = "SELECT distinct c_member_id_to FROM c_message";
     845        $sql .= " WHERE $where";
     846    }
     847
    823848    $sql .= " ORDER BY r_datetime DESC";
     849
    824850    $params = array(intval($c_member_id));
    825851    $c_message_list = db_get_all($sql, $params);
     
    908934    }
    909935
    910     $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_message' .
    911            " WHERE $where" .
    912            ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
     936    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     937        $sql = "SELECT DISTINCT date_part('day', r_datetime) FROM c_message" .
     938               " WHERE $where" .
     939               ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
     940    } else {
     941        $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_message' .
     942               " WHERE $where" .
     943               ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
     944    }
    913945
    914946    $date_format = '%Y-%m-%d 00:00:00';
  • OpenPNE/trunk/webapp/lib/db/review.php

    r3591 r3738  
    179179        break;
    180180    case "r_num":
    181         $order = " ORDER BY write_num DESC, r_datetime DESC";
     181        $order = " ORDER BY write_num DESC, r_datetime2 DESC";
    182182        break;
    183183    }
    184184
     185    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     186        $group = " GROUP BY c_review.c_review_id" .
     187                ", c_review.title" .
     188                ", c_review.release_date" .
     189                ", c_review.manufacturer" .
     190                ", c_review.author" .
     191                ", c_review.c_review_category_id" .
     192                ", c_review.image_small" .
     193                ", c_review.image_medium" .
     194                ", c_review.image_large" .
     195                ", c_review.url" .
     196                ", c_review.asin" .
     197                ", c_review.list_price" .
     198                ", c_review.retail_price" .
     199                ", c_review.r_datetime";
     200    } else {
     201        $group = " GROUP BY c_review.c_review_id";
     202    }
    185203    $sql = "SELECT" .
    186204            " c_review.*" .
     
    189207        $from .
    190208        $where .
    191         " GROUP BY c_review.c_review_id" .
     209        $group .
    192210        $order;
    193211
  • OpenPNE/trunk/webapp/modules/admin/lib/db_admin.php

    r3695 r3738  
    740740                } else {
    741741                    if ($value['name'] == "PNE_POINT") {
    742                         $sql .= ' ORDER BY cast(value as signed)';
     742                        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     743                            $sql .= ' ORDER BY cast(value as integer)';
     744                        } else {
     745                            $sql .= ' ORDER BY cast(value as signed)';
     746                        }
    743747                    } else {
    744748                        $sql .= ' ORDER BY value';
     
    941945function p_access_analysis_month_access_analysis_month($ktai_flag)
    942946{
    943     $sql = "SELECT date_format(r_datetime, '%Y-%m-01') as ym, count(*) as count" .
    944             " FROM c_access_log " .
    945             " where ktai_flag = ?" .
    946             " group by ym";
     947    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     948        $sql = "SELECT to_char(r_datetime, 'YYYY-MM-01') as ym, count(*) as count" .
     949                " FROM c_access_log " .
     950                " where ktai_flag = ?" .
     951                " group by ym";
     952    } else {
     953        $sql = "SELECT date_format(r_datetime, '%Y-%m-01') as ym, count(*) as count" .
     954                " FROM c_access_log " .
     955                " where ktai_flag = ?" .
     956                " group by ym";
     957    }
     958
    947959    $params = array(intval($ktai_flag));           
    948960    $list = db_get_all($sql,$params);
     
    953965function p_access_analysis_day_access_analysis_day($ym, $ktai_flag)
    954966{
    955     $sql = "SELECT left(r_datetime,10) as ymd , count(*) as count" .
     967    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     968        $sql = "SELECT substr(r_datetime,1,10) as ymd , count(*) as count" .
    956969            " FROM c_access_log " .
    957             " where left(r_datetime, 7) = ?" .
     970            " where substr(r_datetime,1,7) = ?" .
    958971            " and ktai_flag = ? " .
    959972            " group by ymd";           
     973    } else {
     974        $sql = "SELECT left(r_datetime,10) as ymd , count(*) as count" .
     975                " FROM c_access_log " .
     976                " where left(r_datetime, 7) = ?" .
     977                " and ktai_flag = ? " .
     978                " group by ymd";
     979    }
    960980    $params = array(intval(substr($ym,0,7)),intval($ktai_flag));
    961981    $list = db_get_all($sql,$params);
     
    10091029    $params = array(intval($ktai_flag));
    10101030    if ($month_flag) {
    1011         $sql .= " and left(r_datetime, 7) = ? ";
     1031        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1032            $sql .= " and substr(r_datetime,1,7) = ? ";
     1033        } else {
     1034            $sql .= " and left(r_datetime, 7) = ? ";
     1035        }
     1036
    10121037        array_push($params,substr($ymd,0,7));
    10131038    } else {
    1014         $sql .= " and left(r_datetime,10) = ? ";
     1039        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1040            $sql .= " and substr(r_datetime,1,10) = ? ";
     1041        } else {
     1042            $sql .= " and left(r_datetime,10) = ? ";
     1043        }
    10151044        array_push($params,$ymd);
    10161045    }
     
    11041133    $params = array(intval($ktai_flag));
    11051134    if ($month_flag) {
     1135        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1136            $sql .= " and substr(r_datetime,1,7) = ? ";
     1137        } else {
    11061138            $sql .= " and left(r_datetime, 7) = ? ";
    1107             array_push($params,substr($ymd,0,7));
     1139        }
     1140        array_push($params,substr($ymd,0,7));
    11081141    } else {
     1142        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1143            $sql .= " and substr(r_datetime,1,10) = ? ";
     1144        } else {
    11091145            $sql .= " and left(r_datetime,10) = ? ";
    1110             array_push($params,$ymd);
     1146        }
     1147        array_push($params,$ymd);
    11111148    }
    11121149    if ($page_name!="all") {
     
    11331170    $params = array(intval($ktai_flag));
    11341171    if ($month_flag) {
    1135         $sql .= " and left(r_datetime, 7) = ? ";
     1172        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1173            $sql .= " and substr(r_datetime,1,7) = ? ";
     1174        } else {
     1175            $sql .= " and left(r_datetime, 7) = ? ";
     1176        }
    11361177        array_push($params,substr($ymd,0,7));
    11371178    } else {
    1138         $sql .= " and left(r_datetime,10) = ? ";
     1179        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1180            $sql .= " and substr(r_datetime,1,10) = ? ";
     1181        } else {
     1182            $sql .= " and left(r_datetime,10) = ? ";
     1183        }
    11391184        array_push($params,$ymd);
    11401185    }
     
    11841229    $params = array(intval($ktai_flag));
    11851230    if ($month_flag) {
     1231        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1232            $where .= " and substr(r_datetime,1, 7) = ? ";
     1233        } else {
    11861234            $where .= " and left(r_datetime, 7) = ? ";
    1187             array_push($params,substr($ymd,0,7));
     1235        }
     1236        array_push($params,substr($ymd,0,7));
    11881237    } else {
     1238        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1239            $where .= " and substr(r_datetime,1,10) = ? ";
     1240        } else {
    11891241            $where .= " and left(r_datetime,10) = ? ";
    1190             array_push($params,$ymd);
     1242        }
     1243        array_push($params,$ymd);
    11911244    }
    11921245    if ($page_name!="all") {
     
    12541307    $params = array(intval($ktai_flag));
    12551308    if ($month_flag) {
    1256         $sql .= " and left(r_datetime, 7) = ? ";
     1309        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1310            $sql .= " and substr(r_datetime,1,7) = ? ";
     1311        } else {
     1312            $sql .= " and left(r_datetime, 7) = ? ";
     1313        }
    12571314        array_push($params,substr($ymd,0,7));
    12581315    } else {
    1259         $sql .= " and left(r_datetime,10) = ? ";
     1316        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1317            $sql .= " and substr(r_datetime,1,10) = ? ";
     1318        } else {
     1319            $sql .= " and left(r_datetime,10) = ? ";
     1320        }
    12601321        array_push($params,$ymd);
    12611322    }
     
    12841345    $params = array(intval($ktai_flag));
    12851346    if ($month_flag) {
     1347        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1348            $sql .= " and substr(r_datetime,1,7) = ? ";
     1349        } else {
    12861350            $sql .= " and left(r_datetime, 7) = ? ";
    1287             array_push($params,substr($ymd,0,7));
     1351        }
     1352        array_push($params,substr($ymd,0,7));
    12881353    } else {
     1354        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1355            $sql .= " and substr(r_datetime,1,10) = ? ";
     1356        } else {
    12891357            $sql .= " and left(r_datetime,10) = ? ";
    1290             array_push($params,$ymd);
     1358        }
     1359        array_push($params,$ymd);
    12911360    }
    12921361    $sql .= " and target_c_diary_id <> 0 ";
     
    13321401    $params = array(intval($ktai_flag));
    13331402    if ($month_flag) {
    1334         $where .= " and left(r_datetime, 7) = ? ";
     1403        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1404            $where .= " and substr(r_datetime,1,7) = ? ";
     1405        } else {
     1406            $where .= " and left(r_datetime, 7) = ? ";
     1407        }
    13351408        array_push($params,substr($ymd,0,7));
    13361409    } else {
    1337         $where .= " and left(r_datetime,10) = ? ";
     1410        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1411            $where .= " and substr(r_datetime,1,10) = ? ";
     1412        } else {
     1413            $where .= " and left(r_datetime,10) = ? ";
     1414        }
    13381415        array_push($params,$ymd);
    13391416    }
     
    13971474    $where =" where ktai_flag = ? ";
    13981475    $params = array(intval($ktai_flag));
    1399         if ($month_flag) {
    1400                 $where .= " and left(r_datetime, 7) = ? ";
    1401                 array_push($params,substr($ymd,0,7));
     1476    if ($month_flag) {
     1477        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1478            $where .= " and substr(r_datetime,1,7) = ? ";
    14021479        } else {
    1403                 $where .= " and left(r_datetime,10) = ? ";
    1404                 array_push($params,$ymd);
    1405         }
    1406         if ($page_name!="all") {
    1407                 $where .= " and page_name = ? ";
    1408                 array_push($params,$page_name);
    1409         }
     1480            $where .= " and left(r_datetime, 7) = ? ";
     1481        }
     1482        array_push($params,substr($ymd,0,7));
     1483    } else {
     1484        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1485            $where .= " and substr(r_datetime,1,10) = ? ";
     1486        } else {
     1487            $where .= " and left(r_datetime,10) = ? ";
     1488        }
     1489        array_push($params,$ymd);
     1490    }
     1491    if ($page_name != "all") {
     1492        $where .= " and page_name = ? ";
     1493        array_push($params,$page_name);
     1494    }
    14101495    $sql = "select target_c_member_id , count(*) as count from c_access_log ";
    14111496    $sql .= $where;
     
    14281513    $where =" where ktai_flag = ? ";
    14291514    $params = array(intval($ktai_flag));
    1430         if ($month_flag) {
    1431                 $where .= " and left(r_datetime, 7) = ? ";
    1432                 array_push($params,substr($ymd,0,7));
     1515    if ($month_flag) {
     1516        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1517            $where .= " and substr(r_datetime,1,7) = ? ";
    14331518        } else {
    1434                 $where .= " and left(r_datetime,10) = ? ";
    1435                 array_push($params,$ymd);
    1436         }
    1437         if ($page_name != "all") {
    1438                 $where .= " and page_name = ? ";
    1439                 array_push($params,$page_name);
    1440         }
     1519            $where .= " and left(r_datetime, 7) = ? ";
     1520        }
     1521        array_push($params,substr($ymd,0,7));
     1522    } else {
     1523        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1524            $where .= " and substr(r_datetime,1,10) = ? ";
     1525        } else {
     1526            $where .= " and left(r_datetime,10) = ? ";
     1527        }
     1528        array_push($params,$ymd);
     1529    }
     1530    if ($page_name != "all") {
     1531        $where .= " and page_name = ? ";
     1532        array_push($params,$page_name);
     1533    }
    14411534    $sql = "select count(*) from c_access_log " ;
    14421535    $sql .= $where;
     
    15991692    );
    16001693   
    1601     $today = getdate();
    1602     $mmdd = $today['mon'] * 100 + $today['mday'];
    1603     $sql = 'SELECT ' . $today['year'] . ' - birth_year'
    1604          . ' - (' . $mmdd . ' < (birth_month * 100 + birth_day))'
    1605          . ' AS age FROM c_member WHERE birth_year <> 0';
     1694    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1695        $sql = "SELECT ((date_part('year', now()) - birth_year)- " .
     1696            "cast(substring(CURRENT_DATE,'.....$')<(to_char(birth_month, '00') || '-' || to_char(birth_day, '00')) as int)) " .
     1697            "AS age FROM c_member WHERE birth_year <> 0;";
     1698    } else {
     1699        $today = getdate();
     1700        $mmdd = $today['mon'] * 100 + $today['mday'];
     1701        $sql = 'SELECT ' . $today['year'] . ' - birth_year'
     1702             . ' - (' . $mmdd . ' < (birth_month * 100 + birth_day))'
     1703             . ' AS age FROM c_member WHERE birth_year <> 0';
     1704    }
    16061705    $lst = db_get_all($sql);
    16071706
     
    16471746function get_analysis_date_month($year = "", $month = "")
    16481747{
    1649     $sql = "select date_format(r_date,'%Y-%m') from c_member order by r_date";
     1748    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1749        $sql = "select to_char(r_date,'YYYY-MM') from c_member order by r_date";
     1750    } else {
     1751        $sql = "select date_format(r_date,'%Y-%m') from c_member order by r_date";
     1752    }
    16501753    $start_date = db_get_one($sql);
    16511754
     
    16571760    }while($date < date("Y-m"));   
    16581761   
    1659     $sql = "select date_format(r_date,'%Y-%m') as d from c_member";
     1762    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1763        $sql = "select to_char(r_date,'YYYY-MM') as d from c_member";
     1764    } else {
     1765        $sql = "select date_format(r_date,'%Y-%m') as d from c_member";
     1766    }
    16601767    $lst = db_get_all($sql);
    16611768    foreach ($lst as $value) {
     
    16901797    }
    16911798
    1692    
    1693     $sql = "select date_format(r_date,'%d') as d from c_member where date_format(r_date,'%Y-%m') = ?";
     1799    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1800        $sql = "select to_char(r_date,'DD') as d from c_member where to_char(r_date,'YYYY-MM') = ?";
     1801    } else {
     1802        $sql = "select date_format(r_date,'%d') as d from c_member where date_format(r_date,'%Y-%m') = ?";
     1803    }
    16941804    $params = array($date);
    16951805    $lst = db_get_all($sql,$params);
  • OpenPNE/trunk/webapp_biz/modules/biz/lib/mysql_functions.php

    r3591 r3738  
    232232    );
    233233
    234     $ids = db_get_col($sql, $param);
     234    $ids = db_get_col($sql, $params);
    235235
    236236    $schedule = array();
     
    336336    $sql = "SELECT * FROM biz_group_member";
    337337    $sql .= " WHERE c_member_id = ?";
    338     $sql .= " ORDER BY RAND()";
     338    $sql .= db_order_by_rand();
    339339
    340340    $params = array(
     
    369369
    370370//指定された条件に見合うグループのリストを得る関数
    371 function biz_getGroupList($keyword = '', $page = 0, $page_size = 20, $order = 'biz_group_id')
     371function biz_getGroupList($keyword = '', $page = 0, $page_size = 20)
    372372{
    373373
    374374    //keywordあり
    375375    if ($keyword) {
    376         $where = ' WHERE true AND (info LIKE ? OR name LIKE ?) ORDER BY ?';
     376        $where = ' WHERE true AND (info LIKE ? OR name LIKE ?) ORDER BY biz_group_id';
    377377        $sql = 'SELECT * FROM biz_group'. $where;
    378378
     
    380380            '%'.$keyword.'%',
    381381            '%'.$keyword.'%',
    382             $order,
    383382        );
    384383        $list = db_get_all_page($sql, $page, $page_size, $params);
     
    386385    } else {
    387386        $where = '';
    388         $sql = 'SELECT * FROM biz_group ORDER BY ? desc';
    389 
    390         $params = array(
    391             $order,
    392         );
    393         $list = db_get_all_page($sql, $page, $page_size, $params);
     387        $sql = 'SELECT * FROM biz_group ORDER BY biz_group_id';
     388
     389        $params = array();
     390        $list = db_get_all_page($sql, $page, $page_size);
    394391    }
    395392
     
    401398        $count = count(biz_getGroupMember($value['biz_group_id']));
    402399        $list[$key]['count'] = $count;
    403     }
    404 
    405     if ($keyword) {
    406         $params = array(
    407             '%'.$keyword.'%',
    408             '%'.$keyword.'%',
    409             $order,
    410         );
    411     } else {
    412         $params = array();
    413400    }
    414401
     
    447434    if ($id) {
    448435        $params[] = intval($id);
    449         $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = "?-?-?" AND biz_shisetsu_id = ? ORDER BY begin_time ASC';
     436        $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = \'?-?-?\' AND biz_shisetsu_id = ? ORDER BY begin_time ASC';
    450437    } else {
    451         $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = "?-?-?" ORDER BY begin_time ASC';
     438        $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = \'?-?-?\' ORDER BY begin_time ASC';
    452439    }
    453440    $list = db_get_all($sql, $params);
     
    710697        $sql = 'SELECT * FROM biz_todo WHERE biz_todo_id IN ('.$ids.')' .
    711698                ' AND due_datetime > ? AND due_datetime <= ?';
     699        $max_day = date("t", mktime(0,0,0,intval($month),1,intval($year) ));
    712700        $params = array(
    713             sprintf('%04d-%02d', intval($year), intval($month)) . '-00 00:00:00',
    714             sprintf('%04d-%02d', intval($year), intval($month)) . '-31 23:59:59'
     701            sprintf('%04d-%02d', intval($year), intval($month)) . '-01 00:00:00',
     702            sprintf('%04d-%02d', intval($year), intval($month)) . '-' . $max_day . ' 23:59:59'
    715703        );
    716704        $list = db_get_all($sql, $params);
     
    891879{
    892880    $sql = 'UPDATE biz_schedule SET title = ?,c_member_id = ?,begin_date = ?,finish_date = ?,begin_time = ?,finish_time = ?,value = ?,rep_type = ?,rep_first = ?, biz_group_id = ?, public_flag = ?, is_read = 0 WHERE biz_schedule_id = ?';
     881
     882    //登録値のセット、チェック
     883    if (!$value) {
     884        $value = '';
     885    }
     886
     887    if (!$rep_type) {
     888        $rep_type = 0;
     889    }
     890
    893891    $params = array(
    894892        $title,
     
    11151113    }
    11161114
     1115    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1116        if ($due_datetime == '') {
     1117            $due_datetime = '0000-01-01';
     1118        }
     1119
     1120        if ($biz_group_id == '') {
     1121            $biz_group_id = 0;
     1122        }
     1123    }
     1124
    11171125    $data = array(
    11181126        'c_member_id' => $member_id,
Note: See TracChangeset for help on using the changeset viewer.