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

Changeset 3651


Ignore:
Timestamp:
Aug 7, 2007, 4:21:51 PM (13 years ago)
Author:
ToshihiroK
Message:

PostgreSQL対応修正分コミット

Location:
OpenPNE/branches/prj/postgres/2_9_1
Files:
3 added
13 edited

Legend:

Unmodified
Added
Removed
  • OpenPNE/branches/prj/postgres/2_9_1/bin/tool_rss_cache.php

    r2641 r3651  
    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/branches/prj/postgres/2_9_1/lib/include/DB/pgsql.php

    r2 r3651  
    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/branches/prj/postgres/2_9_1/webapp/lib/db/banner.php

    r1856 r3651  
    1818        $sql .= ' AND is_hidden_before = 0';
    1919    }
    20     $sql .= ' ORDER BY RAND()';
     20
     21    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     22        $sql .= ' ORDER BY RANDOM()';
     23    } else {
     24        $sql .= ' ORDER BY RAND()';
     25    }
    2126
    2227    return db_get_row($sql);
     
    3641        $sql .= ' AND is_hidden_before = 0';
    3742    }
    38     $sql .= ' ORDER BY RAND()';
     43    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     44        $sql .= ' ORDER BY RANDOM()';
     45    } else {
     46        $sql .= ' ORDER BY RAND()';
     47    }
    3948
    4049    return db_get_row($sql);
  • OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/bookmark.php

    r2853 r3651  
    160160    $is_recurred = false;
    161161
    162     $sql = 'SELECT c_member_id_to AS c_member_id FROM c_bookmark' .
    163             ' WHERE c_member_id_from = ? ORDER BY RAND()';
     162    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     163        $sql = 'SELECT c_member_id_to AS c_member_id FROM c_bookmark' .
     164                ' WHERE c_member_id_from = ? ORDER BY RANDOM()';
     165    } else {
     166        $sql = 'SELECT c_member_id_to AS c_member_id FROM c_bookmark' .
     167                ' WHERE c_member_id_from = ? ORDER BY RAND()';
     168    }
     169
    164170    $params = array(intval($c_member_id));
    165171
  • OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/commu.php

    r3532 r3651  
    491491
    492492    $is_recurred = false;
    493     $sql = 'SELECT c_member.* FROM c_member, c_commu_member' .
    494             ' WHERE c_member.c_member_id = c_commu_member.c_member_id' .
    495             ' AND c_commu_id = ? ORDER BY RAND()';
     493    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     494        $sql = 'SELECT c_member.* FROM c_member, c_commu_member' .
     495                ' WHERE c_member.c_member_id = c_commu_member.c_member_id' .
     496                ' AND c_commu_id = ? ORDER BY RANDOM()';
     497    } else {
     498        $sql = 'SELECT c_member.* FROM c_member, c_commu_member' .
     499                ' WHERE c_member.c_member_id = c_commu_member.c_member_id' .
     500                ' AND c_commu_id = ? ORDER BY RAND()';
     501    }
    496502    $params = array(intval($c_commu_id));
    497503    $lst = db_get_all_limit($sql, 0, $limit, $params);
     
    515521    $is_recurred = false;
    516522
    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 ";
     523    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     524        $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id" .
     525                " , max(cctc.image_filename1) as image_filename1, max(cctc.image_filename2) as image_filename2, max(cctc.image_filename3) as image_filename3 " .
     526                " FROM c_commu_topic_comment as cctc , c_commu_topic as cct" .
     527                " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id " .
     528                " AND cct.event_flag = ?".
     529                " AND cct.c_commu_id = ?".
     530                " group by cct.c_commu_topic_id, cct.name, cct.c_commu_id " .
     531                " order by r_datetime desc ";
     532    } else {
     533        $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id " .
     534                " , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 " .
     535                " FROM c_commu_topic_comment as cctc , c_commu_topic as cct" .
     536                " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id " .
     537                " AND cct.event_flag = ?".
     538                " AND cct.c_commu_id = ?".
     539                " group by cct.c_commu_topic_id " .
     540                " order by r_datetime desc ";
     541    }
     542
    525543    $params = array((bool)$event_flag, intval($c_commu_id));
    526544    $list = db_get_all_limit($sql, 0, $limit, $params);
     
    615633    $is_recurred = false;
    616634
    617     $sql = "SELECT c_commu.c_commu_id, c_commu.image_filename, c_commu.name, c_commu.c_member_id_admin" .
    618         " FROM c_commu ,c_commu_member " .
    619         " WHERE c_commu_member.c_member_id = ?".
    620         " AND c_commu.c_commu_id =  c_commu_member.c_commu_id" .
    621         " ORDER BY RAND()";
     635    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     636        $sql = "SELECT c_commu.c_commu_id, c_commu.image_filename, c_commu.name, c_commu.c_member_id_admin" .
     637            " FROM c_commu ,c_commu_member " .
     638            " WHERE c_commu_member.c_member_id = ?".
     639            " AND c_commu.c_commu_id =  c_commu_member.c_commu_id" .
     640            " ORDER BY RANDOM()";
     641    } else {
     642        $sql = "SELECT c_commu.c_commu_id, c_commu.image_filename, c_commu.name, c_commu.c_member_id_admin" .
     643            " FROM c_commu ,c_commu_member " .
     644            " WHERE c_commu_member.c_member_id = ?".
     645            " AND c_commu.c_commu_id =  c_commu_member.c_commu_id" .
     646            " ORDER BY RAND()";
     647    }
     648
    622649    $params = array(intval($c_member_id));
    623650    $lst = db_get_all_limit($sql, 0, $limit, $params);
     
    805832
    806833    $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';
     834
     835    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     836        $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id'.
     837            ' FROM (' .
     838                    ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime' .
     839                    ' FROM' .
     840                        ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint .
     841                    ' WHERE ' .
     842                        ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
     843                    ' GROUP BY cct.c_commu_topic_id' .
     844                    ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct' .
     845            ' WHERE' .
     846                ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id' .
     847                ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id ' .
     848                ' AND cctc.r_datetime=sub_cct_tbl.r_datetime' .
     849            ' ORDER BY r_datetime DESC';
     850    } else {
     851        $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'.
     852            ' 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'.
     853            ' GROUP BY cctc.c_commu_topic_id'.
     854            ' ORDER BY r_datetime DESC';
     855    }
     856
    811857    $c_commu_topic_list = db_get_all_limit($sql, 0, $limit);
    812858
     
    851897
    852898    $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';
     899   
     900    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     901        $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id'.
     902            ' FROM (' .
     903                    ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime' .
     904                    ' FROM' .
     905                        ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint .
     906                    ' WHERE ' .
     907                        ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'.
     908                    ' GROUP BY cct.c_commu_topic_id' .
     909                    ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct' .
     910            ' WHERE' .
     911                ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id' .
     912                ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id ' .
     913                ' AND cctc.r_datetime=sub_cct_tbl.r_datetime' .
     914            ' ORDER BY r_datetime DESC';
     915    } else {
     916        $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'.
     917            ' 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'.
     918            ' GROUP BY cctc.c_commu_topic_id'.
     919            ' ORDER BY r_datetime DESC';
     920    }
     921
    857922    $c_commu_topic_list = db_get_all_limit($sql, ($page-1)*$limit, $limit);
    858923
     
    11731238    $is_recurred = false;
    11741239
    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()';
     1240    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1241        $sql = 'SELECT DISTINCT c.*, RANDOM() FROM c_commu_member AS cm, c_commu AS c' .
     1242                ' WHERE cm.c_member_id = ? AND c.c_commu_id = cm.c_commu_id' .
     1243                ' ORDER BY RANDOM()';
     1244    } else {
     1245        $sql = 'SELECT DISTINCT c.* FROM c_commu_member AS cm, c_commu AS c' .
     1246                ' WHERE cm.c_member_id = ? AND c.c_commu_id = cm.c_commu_id' .
     1247                ' ORDER BY RAND()';
     1248    }
     1249
    11781250    $params = array(intval($c_member_id));
    11791251    $c_commu_list = db_get_all_limit($sql, 0, $limit, $params);
     
    13871459    $sql .= "WHERE ccm.c_commu_id = ?" .
    13881460            " AND ccm.c_member_id = cm.c_member_id";
    1389     $sql .= " ORDER BY RAND()";
     1461
     1462    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1463        $sql .= " ORDER BY RANDOM()";
     1464    } else {
     1465        $sql .= " ORDER BY RAND()";
     1466    }
     1467
    13901468    $params = array(intval($c_commu_id));
    13911469    $c_commu_member_list = db_get_all_limit($sql, 0, $limit, $params);
     
    14561534
    14571535    $result = implode(',', array_map('intval', $result));
    1458     $sql = 'SELECT c_member_id, nickname FROM c_member' .
    1459             ' WHERE c_member_id IN ( '. $result . ') ' .
    1460             ' ORDER BY RAND()';
     1536
     1537    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1538        $sql = 'SELECT c_member_id, nickname FROM c_member' .
     1539                ' WHERE c_member_id IN ( '. $result . ') ' .
     1540                ' ORDER BY RANDOM()';
     1541    } else {
     1542        $sql = 'SELECT c_member_id, nickname FROM c_member' .
     1543                ' WHERE c_member_id IN ( '. $result . ') ' .
     1544                ' ORDER BY RAND()';
     1545    }
     1546
    14611547    return db_get_all_limit($sql, 0, $limit);
    14621548}
     
    16861772
    16871773    case 'count':
    1688         $from  = ' FROM c_commu, c_commu_member';
    1689         $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;
     1774        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1775            $from  = ' FROM c_commu, c_commu_member';
     1776            $sub_tbl = ' (' .
     1777                            'SELECT c_commu_member.c_commu_id, count(c_commu_member.c_commu_member_id) as count_commu_member' .
     1778                            $from .
     1779                            $where . ' AND c_commu_member.c_commu_id = c_commu.c_commu_id' .
     1780                            ' GROUP BY c_commu_member.c_commu_id' .
     1781                        ') as sub_commu_tbl';
     1782            $order = ' ORDER BY count_commu_member DESC';
     1783            $sql = $select .
     1784                    ' FROM' . $sub_tbl . ', c_commu' .
     1785                    ' WHERE c_commu.c_commu_id = sub_commu_tbl.c_commu_id' .
     1786                    $order;
     1787        } else {
     1788            $from  = ' FROM c_commu, c_commu_member';
     1789            $order = ' ORDER BY count_commu_member DESC';
     1790            $group = ' GROUP BY c_commu_member.c_commu_id';
     1791            $sql = $select . ', COUNT(c_commu_member.c_member_id) AS count_commu_member' .
     1792                $from .
     1793                $where . ' AND c_commu_member.c_commu_id = c_commu.c_commu_id' .
     1794                $group .
     1795                $order;
     1796        }
    16961797        break;
    16971798    }
     
    17971898function db_commu_c_topic4c_commu_topic_id_2($c_commu_topic_id)
    17981899{
    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";
     1900    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1901        $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" .
     1902            " FROM c_commu_topic as cct" .
     1903            " LEFT JOIN c_commu_topic_comment as cctc ON cct.c_commu_topic_id = cctc.c_commu_topic_id" .
     1904            " LEFT JOIN c_member as cm ON cct.c_member_id = cm.c_member_id" .
     1905            " LEFT JOIN c_profile_pref as cpp ON cct.open_pref_id = cpp.c_profile_pref_id" .
     1906            " WHERE cct.c_commu_topic_id = ?".
     1907                " AND cctc.number = 0";
     1908    } else {
     1909        $sql = "SELECT cct.*, cctc.*, cm.nickname, cpp.pref" .
     1910            " FROM c_commu_topic as cct" .
     1911            " LEFT JOIN c_commu_topic_comment as cctc ON cct.c_commu_topic_id = cctc.c_commu_topic_id" .
     1912            " LEFT JOIN c_member as cm ON cct.c_member_id = cm.c_member_id" .
     1913            " LEFT JOIN c_profile_pref as cpp ON cct.open_pref_id = cpp.c_profile_pref_id" .
     1914            " WHERE cct.c_commu_topic_id = ?".
     1915                " AND cctc.number = 0";
     1916    }
     1917
    18061918    $params = array(intval($c_commu_topic_id));
    18071919    $lst = db_get_row($sql, $params);
     
    24022514        'r_date' => db_now(),
    24032515    );
     2516
     2517    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2518        $open_date = '0000-01-01';
     2519        $invite_period = '0000-01-01';
     2520        if (isset($topic['open_date']) && $topic['open_date'] != '') {
     2521            $open_date = $topic['open_date'];
     2522        }
     2523       
     2524        if (isset($topic['invite_period']) && $topic['invite_period'] != '') {
     2525            $invite_period = $topic['invite_period'];
     2526        }
     2527    } else {
     2528        $open_date = $topic['open_date'];
     2529        $invete_period = $topic['invite_period'];
     2530    }
    24042531    if ($data['event_flag']) {
    24052532        $data += array(
    2406             'open_date'         => $topic['open_date'],
     2533            'open_date'         => $open_date,
    24072534            'open_date_comment' => $topic['open_date_comment'],
    24082535            'open_pref_id'      => intval($topic['open_pref_id']),
    24092536            'open_pref_comment' => $topic['open_pref_comment'],
    2410             'invite_period'     => $topic['invite_period'],
     2537            'invite_period'     => $invite_period,
    24112538        );
    24122539    }
     
    24752602        'r_date'      => db_now(),
    24762603    );
     2604
     2605    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2606        $open_date = '0000-01-01';
     2607        $invite_period = '0000-01-01';
     2608        if (isset($topic['open_date']) && $topic['open_date'] != '') {
     2609            $open_date = $topic['open_date'];
     2610        }
     2611       
     2612        if (isset($topic['invite_period']) && $topic['invite_period'] != '') {
     2613            $invite_period = $topic['invite_period'];
     2614        }
     2615    } else {
     2616        $open_date = $topic['open_date'];
     2617        $invete_period = $topic['invite_period'];
     2618    }
     2619
    24772620    if ($data['event_flag']) {
    24782621        $data += array(
    2479             'open_date'         => $topic['open_date'],
     2622            'open_date'         => $open_date,
    24802623            'open_date_comment' => $topic['open_date_comment'],
    24812624            'open_pref_id'      => intval($topic['open_pref_id']),
    24822625            'open_pref_comment' => $topic['open_pref_comment'],
    2483             'invite_period'     => $topic['invite_period'],
     2626            'invite_period'     => $invite_period,
    24842627        );
    24852628    }
     
    25572700function db_commu_is_event_join_date($c_commu_topic_id)
    25582701{
    2559     $sql = 'SELECT c_commu_topic_id FROM c_commu_topic'
    2560          . ' WHERE c_commu_topic_id = ?'
    2561          . ' AND (open_date >= ? OR open_date = \'0000-00-00\')'
    2562          . ' AND (invite_period >= ? OR invite_period = \'0000-00-00\')';
     2702    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     2703        $sql = 'SELECT c_commu_topic_id FROM c_commu_topic'
     2704             . ' WHERE c_commu_topic_id = ?'
     2705             . ' AND (open_date >= ? OR open_date = \'0000-01-01\')'
     2706             . ' AND (invite_period >= ? OR invite_period = \'0000-01-01\')';
     2707    } else {
     2708        $sql = 'SELECT c_commu_topic_id FROM c_commu_topic'
     2709             . ' WHERE c_commu_topic_id = ?'
     2710             . ' AND (open_date >= ? OR open_date = \'0000-00-00\')'
     2711             . ' AND (invite_period >= ? OR invite_period = \'0000-00-00\')';
     2712    }
    25632713    $now = date('Y-m-d');
    25642714    $params = array(intval($c_commu_topic_id), $now, $now);
  • OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/diary.php

    r3057 r3651  
    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/branches/prj/postgres/2_9_1/webapp/lib/db/file.php

    r3458 r3651  
    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/branches/prj/postgres/2_9_1/webapp/lib/db/friend.php

    r3057 r3651  
    257257    $is_recurred = false;
    258258
    259     $sql = 'SELECT c_member_id_to AS c_member_id FROM c_friend' .
    260             ' WHERE c_member_id_from = ? ORDER BY RAND()';
     259    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     260        $sql = 'SELECT c_member_id_to AS c_member_id FROM c_friend' .
     261                ' WHERE c_member_id_from = ? ORDER BY RANDOM()';
     262    } else {
     263        $sql = 'SELECT c_member_id_to AS c_member_id FROM c_friend' .
     264                ' WHERE c_member_id_from = ? ORDER BY RAND()';
     265    }
     266
    261267    $params = array(intval($c_member_id));
    262268    if ($limit) {
     
    339345    $is_recurred = false;
    340346
    341     $sql = 'SELECT * FROM c_friend' .
    342             ' WHERE c_member_id_to = ? AND intro <> \'\' ORDER BY RAND()';
     347    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     348        $sql = 'SELECT * FROM c_friend' .
     349                ' WHERE c_member_id_to = ? AND intro <> \'\' ORDER BY RANDOM()';
     350    } else {
     351        $sql = 'SELECT * FROM c_friend' .
     352                ' WHERE c_member_id_to = ? AND intro <> \'\' ORDER BY RAND()';
     353    }
     354
    343355    $params = array(intval($c_member_id));
    344356    $list = db_get_all_limit($sql, 0, $limit, $params);
     
    455467    $sql .= " WHERE cf.c_member_id_from = ?" .
    456468            " AND cm.c_member_id=cf.c_member_id_to";
    457     $sql .= " ORDER BY RAND()";
     469    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     470        $sql .= " ORDER BY RANDOM()";
     471    } else {
     472        $sql .= " ORDER BY RAND()";
     473    }
    458474    $params = array(intval($c_member_id));
    459475    $c_friend_list = db_get_all_limit($sql, 0, $limit, $params);
     
    598614    $sql .= " WHERE cf.c_member_id_from = ?".
    599615            " AND cf.c_member_id_to = cm.c_member_id";
    600     $sql .= " ORDER BY RAND()";
     616    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     617        $sql .= " ORDER BY RANDOM()";
     618    } else {
     619        $sql .= " ORDER BY RAND()";
     620    }
    601621    $params = array(intval($c_member_id));
    602622    return db_get_all_limit($sql, 0, 50, $params);
  • OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/member.php

    r3532 r3651  
    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/branches/prj/postgres/2_9_1/webapp/lib/db/message.php

    r3057 r3651  
    790790function db_message_c_message_sender_list4c_member_id($c_member_id)
    791791{
    792     $sql = "SELECT distinct c_member_id_from FROM c_message";
    793792    $where = "c_member_id_to = ?".
    794793            " AND is_deleted_to = 0" .
    795794            " AND is_send = 1";
    796     $sql .= " WHERE $where";
     795
     796    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     797        $sql = "SELECT c_member_id_from" .
     798                " FROM" .
     799                    "(" .
     800                        "SELECT distinct on(c_member_id_from) *" .
     801                        " FROM" .
     802                            " c_message" .
     803                        " WHERE " . $where .
     804                    ") as sub_member_tbl";
     805    } else {
     806        $sql = "SELECT distinct c_member_id_from FROM c_message";
     807        $sql .= " WHERE $where";
     808    }
     809
    797810    $sql .= " ORDER BY r_datetime DESC";
    798811    $params = array(intval($c_member_id));
     
    811824function db_message_c_message_receiver_list4c_member_id($c_member_id)
    812825{
    813     $sql = "SELECT distinct c_member_id_to FROM c_message";
    814826    $where = "c_member_id_from = ?".
    815827            " AND is_deleted_from = 0" .
    816828            " AND is_send = 1";
    817     $sql .= " WHERE $where";
     829   
     830    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     831        $sql = "SELECT c_member_id_to" .
     832                " FROM" .
     833                    "(" .
     834                        "SELECT distinct on(c_member_id_to) *" .
     835                        " FROM" .
     836                            " c_message" .
     837                        " WHERE " . $where .
     838                    ") as sub_member_tbl";
     839    } else {
     840        $sql = "SELECT distinct c_member_id_to FROM c_message";
     841        $sql .= " WHERE $where";
     842    }
     843
    818844    $sql .= " ORDER BY r_datetime DESC";
     845
    819846    $params = array(intval($c_member_id));
    820847    $c_message_list = db_get_all($sql, $params);
     
    903930    }
    904931
    905     $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_message' .
    906            " WHERE $where" .
    907            ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
     932    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     933        $sql = "SELECT DISTINCT date_part('day', r_datetime) FROM c_message" .
     934               " WHERE $where" .
     935               ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
     936    } else {
     937        $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_message' .
     938               " WHERE $where" .
     939               ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
     940    }
    908941
    909942    $date_format = '%Y-%m-%d 00:00:00';
  • OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/review.php

    r3532 r3651  
    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     $sql = "SELECT" .
    186             " c_review.*" .
    187             ", MAX(c_review_comment.r_datetime) as r_datetime2" .
    188             ", COUNT(c_review_comment.c_review_comment_id) AS write_num" .
    189         $from .
    190         $where .
    191         " GROUP BY c_review.c_review_id" .
    192         $order;
     185    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     186        $sql = "SELECT" .
     187                " c_review.*" .
     188                ", MAX(c_review_comment.r_datetime) as r_datetime2" .
     189                ", COUNT(c_review_comment.c_review_comment_id) AS write_num" .
     190            $from .
     191            $where .
     192            " GROUP BY c_review.c_review_id" .
     193                ", c_review.title" .
     194                ", c_review.release_date" .
     195                ", c_review.manufacturer" .
     196                ", c_review.author" .
     197                ", c_review.c_review_category_id" .
     198                ", c_review.image_small" .
     199                ", c_review.image_medium" .
     200                ", c_review.image_large" .
     201                ", c_review.url" .
     202                ", c_review.asin" .
     203                ", c_review.list_price" .
     204                ", c_review.retail_price" .
     205                ", c_review.r_datetime" .
     206            $order;
     207    } else {
     208        $sql = "SELECT" .
     209                " c_review.*" .
     210                ", MAX(c_review_comment.r_datetime) as r_datetime2" .
     211                ", COUNT(c_review_comment.c_review_comment_id) AS write_num" .
     212            $from .
     213            $where .
     214            " GROUP BY c_review.c_review_id" .
     215            $order;
     216    }
    193217
    194218    $lst = db_get_all_page($sql, $page, $page_size, $params);
  • OpenPNE/branches/prj/postgres/2_9_1/webapp/modules/admin/lib/db_admin.php

    r3548 r3651  
    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    }
     980
    960981    $params = array(intval(substr($ym,0,7)),intval($ktai_flag));
    961982    $list = db_get_all($sql,$params);
     
    10091030    $params = array(intval($ktai_flag));
    10101031    if ($month_flag) {
    1011         $sql .= " and left(r_datetime, 7) = ? ";
     1032        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1033            $sql .= " and substr(r_datetime,1,7) = ? ";
     1034        } else {
     1035            $sql .= " and left(r_datetime, 7) = ? ";
     1036        }
     1037
    10121038        array_push($params,substr($ymd,0,7));
    10131039    } else {
    1014         $sql .= " and left(r_datetime,10) = ? ";
     1040        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1041            $sql .= " and substr(r_datetime,1,10) = ? ";
     1042        } else {
     1043            $sql .= " and left(r_datetime,10) = ? ";
     1044        }
    10151045        array_push($params,$ymd);
    10161046    }
     
    11041134    $params = array(intval($ktai_flag));
    11051135    if ($month_flag) {
     1136        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1137            $sql .= " and substr(r_datetime,1,7) = ? ";
     1138        } else {
    11061139            $sql .= " and left(r_datetime, 7) = ? ";
    1107             array_push($params,substr($ymd,0,7));
     1140        }
     1141       
     1142        array_push($params,substr($ymd,0,7));
    11081143    } else {
     1144        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1145            $sql .= " and substr(r_datetime,1,10) = ? ";
     1146        } else {
    11091147            $sql .= " and left(r_datetime,10) = ? ";
    1110             array_push($params,$ymd);
     1148        }
     1149
     1150        array_push($params,$ymd);
    11111151    }
    11121152    if ($page_name!="all") {
     
    11331173    $params = array(intval($ktai_flag));
    11341174    if ($month_flag) {
    1135         $sql .= " and left(r_datetime, 7) = ? ";
     1175        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1176            $sql .= " and substr(r_datetime,1,7) = ? ";
     1177        } else {
     1178            $sql .= " and left(r_datetime, 7) = ? ";
     1179        }
     1180
    11361181        array_push($params,substr($ymd,0,7));
    11371182    } else {
    1138         $sql .= " and left(r_datetime,10) = ? ";
     1183        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1184            $sql .= " and substr(r_datetime,1,10) = ? ";
     1185        } else {
     1186            $sql .= " and left(r_datetime,10) = ? ";
     1187        }
     1188
    11391189        array_push($params,$ymd);
    11401190    }
     
    11841234    $params = array(intval($ktai_flag));
    11851235    if ($month_flag) {
     1236        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1237            $where .= " and substr(r_datetime,1, 7) = ? ";
     1238        } else {
    11861239            $where .= " and left(r_datetime, 7) = ? ";
    1187             array_push($params,substr($ymd,0,7));
     1240        }
     1241
     1242        array_push($params,substr($ymd,0,7));
    11881243    } else {
     1244        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1245            $where .= " and substr(r_datetime,1,10) = ? ";
     1246        } else {
    11891247            $where .= " and left(r_datetime,10) = ? ";
    1190             array_push($params,$ymd);
     1248        }
     1249
     1250        array_push($params,$ymd);
    11911251    }
    11921252    if ($page_name!="all") {
     
    12541314    $params = array(intval($ktai_flag));
    12551315    if ($month_flag) {
    1256         $sql .= " and left(r_datetime, 7) = ? ";
     1316        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1317            $sql .= " and substr(r_datetime,1,7) = ? ";
     1318        } else {
     1319            $sql .= " and left(r_datetime, 7) = ? ";
     1320        }
     1321
    12571322        array_push($params,substr($ymd,0,7));
    12581323    } else {
    1259         $sql .= " and left(r_datetime,10) = ? ";
     1324        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1325            $sql .= " and substr(r_datetime,1,10) = ? ";
     1326        } else {
     1327            $sql .= " and left(r_datetime,10) = ? ";
     1328        }
    12601329        array_push($params,$ymd);
    12611330    }
     
    12841353    $params = array(intval($ktai_flag));
    12851354    if ($month_flag) {
     1355        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1356            $sql .= " and substr(r_datetime,1,7) = ? ";
     1357        } else {
    12861358            $sql .= " and left(r_datetime, 7) = ? ";
    1287             array_push($params,substr($ymd,0,7));
     1359        }
     1360
     1361        array_push($params,substr($ymd,0,7));
    12881362    } else {
     1363        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1364            $sql .= " and substr(r_datetime,1,10) = ? ";
     1365        } else {
    12891366            $sql .= " and left(r_datetime,10) = ? ";
    1290             array_push($params,$ymd);
     1367        }
     1368
     1369        array_push($params,$ymd);
    12911370    }
    12921371    $sql .= " and target_c_diary_id <> 0 ";
     
    13321411    $params = array(intval($ktai_flag));
    13331412    if ($month_flag) {
    1334         $where .= " and left(r_datetime, 7) = ? ";
     1413        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1414            $where .= " and substr(r_datetime,1,7) = ? ";
     1415        } else {
     1416            $where .= " and left(r_datetime, 7) = ? ";
     1417        }
     1418
    13351419        array_push($params,substr($ymd,0,7));
    13361420    } else {
    1337         $where .= " and left(r_datetime,10) = ? ";
     1421        if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1422            $where .= " and substr(r_datetime,1,10) = ? ";
     1423        } else {
     1424            $where .= " and left(r_datetime,10) = ? ";
     1425        }
     1426
    13381427        array_push($params,$ymd);
    13391428    }
     
    13981487    $params = array(intval($ktai_flag));
    13991488        if ($month_flag) {
     1489            if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1490                $where .= " and substr(r_datetime,1,7) = ? ";
     1491            } else {
    14001492                $where .= " and left(r_datetime, 7) = ? ";
    1401                 array_push($params,substr($ymd,0,7));
     1493            }
     1494
     1495            array_push($params,substr($ymd,0,7));
    14021496        } else {
     1497            if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1498                $where .= " and substr(r_datetime,1,10) = ? ";
     1499            } else {
    14031500                $where .= " and left(r_datetime,10) = ? ";
    1404                 array_push($params,$ymd);
     1501            }
     1502
     1503            array_push($params,$ymd);
    14051504        }
    14061505        if ($page_name!="all") {
     
    14291528    $params = array(intval($ktai_flag));
    14301529        if ($month_flag) {
     1530            if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1531                $where .= " and substr(r_datetime,1,7) = ? ";
     1532            } else {
    14311533                $where .= " and left(r_datetime, 7) = ? ";
    1432                 array_push($params,substr($ymd,0,7));
     1534            }
     1535
     1536            array_push($params,substr($ymd,0,7));
    14331537        } else {
     1538            if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1539                $where .= " and substr(r_datetime,1,10) = ? ";
     1540            } else {
    14341541                $where .= " and left(r_datetime,10) = ? ";
    1435                 array_push($params,$ymd);
     1542            }
     1543
     1544            array_push($params,$ymd);
    14361545        }
    14371546        if ($page_name != "all") {
     
    15191628    $page_size = intval($page_size);
    15201629
    1521     $limit = "";
    1522 
    1523     //page_sizeが0の時は全て表示(pagerなし)
    1524     if ($page_size != 0) {
    1525         $limit = " OFFSET ".($page_size*($page-1))." LIMIT ".$page_size;
    1526     }
    15271630    $where = " WHERE true ";
    15281631
     
    15421645
    15431646    $sql = $select . $where . $order;
    1544     $list = db_get_all_limit($sql, 0, $limit, $params);
     1647    if ($page_size > 0) {
     1648        $list = db_get_all_page($sql, $page, $page_size, $params);
     1649    } else {
     1650        $list = db_get_all($sql, $params);
     1651    }
    15451652   
    15461653    $sql = "select count(*) from c_member".$where;
     
    16031710    );
    16041711   
    1605     $sql = "SELECT ((YEAR(CURDATE()) - birth_year)- " .
    1606         "(RIGHT(CURDATE(),5)<CONCAT(birth_month,'-',birth_day))) " .
    1607         "AS age FROM c_member WHERE birth_year <> 0;";
     1712    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1713        $sql = "SELECT ((date_part('year', now()) - birth_year)- " .
     1714            "cast(substring(CURRENT_DATE,'.....$')<(to_char(birth_month, '00') || '-' || to_char(birth_day, '00')) as int)) " .
     1715            "AS age FROM c_member WHERE birth_year <> 0;";
     1716    } else {
     1717        $sql = "SELECT ((YEAR(CURDATE()) - birth_year)- " .
     1718            "(RIGHT(CURDATE(),5)<CONCAT(birth_month,'-',birth_day))) " .
     1719            "AS age FROM c_member WHERE birth_year <> 0;";
     1720    }
     1721
    16081722    $lst = db_get_all($sql);
    16091723
     
    16491763function get_analysis_date_month($year = "", $month = "")
    16501764{
    1651     $sql = "select date_format(r_date,'%Y-%m') from c_member order by r_date";
     1765    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1766        $sql = "select to_char(r_date,'YYYY-MM') from c_member order by r_date";
     1767    } else {
     1768        $sql = "select date_format(r_date,'%Y-%m') from c_member order by r_date";
     1769    }
     1770
    16521771    $start_date = db_get_one($sql);
    16531772
     
    16591778    }while($date < date("Y-m"));   
    16601779   
    1661     $sql = "select date_format(r_date,'%Y-%m') as d from c_member";
     1780    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1781        $sql = "select to_char(r_date,'YYYY-MM') as d from c_member";
     1782    } else {
     1783        $sql = "select date_format(r_date,'%Y-%m') as d from c_member";
     1784    }
    16621785    $lst = db_get_all($sql);
    16631786    foreach ($lst as $value) {
     
    16921815    }
    16931816
    1694    
    1695     $sql = "select date_format(r_date,'%d') as d from c_member where date_format(r_date,'%Y-%m') = ?";
     1817    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1818        $sql = "select to_char(r_date,'DD') as d from c_member where to_char(r_date,'YYYY-MM') = ?";
     1819    } else {
     1820        $sql = "select date_format(r_date,'%d') as d from c_member where date_format(r_date,'%Y-%m') = ?";
     1821    }
    16961822    $params = array($date);
    16971823    $lst = db_get_all($sql,$params);
     
    20512177   
    20522178    $sql = $select . $from . $where . $order;
    2053    
     2179
    20542180    $list = db_get_all_limit($sql,($page-1)*$page_size,$page_size,$params);
    20552181   
     
    21272253    $page_size = intval($page_size);
    21282254   
    2129     $where = " where c_commu_topic_id = ? ";
     2255    $where = " where ct.c_commu_topic_id = ? ";
    21302256    $params[] = $c_commu_topic_id;
    21312257   
     
    21382264   
    21392265    $sql = $select . $from . $where . $order;
    2140    
    21412266    $list = db_get_all_limit($sql,($page-1)*$page_size,$page_size,$params);
    21422267   
  • OpenPNE/branches/prj/postgres/2_9_1/webapp_biz/modules/biz/lib/mysql_functions.php

    r3532 r3651  
    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    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     339        $sql .= " ORDER BY RANDOM()";
     340    } else {
     341        $sql .= " ORDER BY RAND()";
     342    }
    339343
    340344    $params = array(
     
    369373
    370374//指定された条件に見合うグループのリストを得る関数
    371 function biz_getGroupList($keyword = '', $page = 0, $page_size = 20, $order = 'biz_group_id')
     375function biz_getGroupList($keyword = '', $page = 0, $page_size = 20)
    372376{
    373377
    374378    //keywordあり
    375379    if ($keyword) {
    376         $where = ' WHERE true AND (info LIKE ? OR name LIKE ?) ORDER BY ?';
     380        $where = ' WHERE true AND (info LIKE ? OR name LIKE ?) ORDER BY biz_group_id';
    377381        $sql = 'SELECT * FROM biz_group'. $where;
    378382
     
    380384            '%'.$keyword.'%',
    381385            '%'.$keyword.'%',
    382             $order,
    383386        );
    384387        $list = db_get_all_page($sql, $page, $page_size, $params);
     
    386389    } else {
    387390        $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);
     391        $sql = 'SELECT * FROM biz_group ORDER BY biz_group_id';
     392
     393        $params = array();
     394        $list = db_get_all_page($sql, $page, $page_size);
    394395    }
    395396
     
    401402        $count = count(biz_getGroupMember($value['biz_group_id']));
    402403        $list[$key]['count'] = $count;
    403     }
    404 
    405     if ($keyword) {
    406         $params = array(
    407             '%'.$keyword.'%',
    408             '%'.$keyword.'%',
    409             $order,
    410         );
    411     } else {
    412         $params = array();
    413404    }
    414405
     
    447438    if ($id) {
    448439        $params[] = intval($id);
    449         $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = "?-?-?" AND biz_shisetsu_id = ? ORDER BY begin_time ASC';
     440        $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = \'?-?-?\' AND biz_shisetsu_id = ? ORDER BY begin_time ASC';
    450441    } else {
    451         $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = "?-?-?" ORDER BY begin_time ASC';
     442        $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = \'?-?-?\' ORDER BY begin_time ASC';
    452443    }
    453444    $list = db_get_all($sql, $params);
     
    710701        $sql = 'SELECT * FROM biz_todo WHERE biz_todo_id IN ('.$ids.')' .
    711702                ' AND due_datetime > ? AND due_datetime <= ?';
     703        $max_day = date("t", mktime(0,0,0,intval($month),1,intval($year) ));
    712704        $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'
     705            sprintf('%04d-%02d', intval($year), intval($month)) . '-01 00:00:00',
     706            sprintf('%04d-%02d', intval($year), intval($month)) . '-' . $max_day . ' 23:59:59'
    715707        );
    716708        $list = db_get_all($sql, $params);
     
    891883{
    892884    $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 = ?';
     885
     886    //登録値のセット、チェック
     887    if (!$value) {
     888        $value = '';
     889    }
     890
     891    if (!$rep_type) {
     892        $rep_type = 0;
     893    }
     894
    893895    $params = array(
    894896        $title,
     
    11151117    }
    11161118
     1119    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
     1120        if ($due_datetime == '') {
     1121            $due_datetime = '0000-01-01';
     1122        }
     1123
     1124        if ($biz_group_id == '') {
     1125            $biz_group_id = 0;
     1126        }
     1127    }
     1128
    11171129    $data = array(
    11181130        'c_member_id' => $member_id,
Note: See TracChangeset for help on using the changeset viewer.