Changeset 3651
- Timestamp:
- Aug 7, 2007, 4:21:51 PM (13 years ago)
- Location:
- OpenPNE/branches/prj/postgres/2_9_1
- Files:
-
- 3 added
- 13 edited
-
bin/tool_rss_cache.php (modified) (1 diff)
-
bin/tool_vacuum_exec.cron (added)
-
bin/tool_vacuum_exec.php (added)
-
lib/include/DB/pgsql.php (modified) (1 diff)
-
setup/OpenPNE_Setup_pgsql.html (added)
-
webapp/lib/db/banner.php (modified) (2 diffs)
-
webapp/lib/db/bookmark.php (modified) (1 diff)
-
webapp/lib/db/commu.php (modified) (13 diffs)
-
webapp/lib/db/diary.php (modified) (2 diffs)
-
webapp/lib/db/file.php (modified) (2 diffs)
-
webapp/lib/db/friend.php (modified) (4 diffs)
-
webapp/lib/db/member.php (modified) (1 diff)
-
webapp/lib/db/message.php (modified) (3 diffs)
-
webapp/lib/db/review.php (modified) (1 diff)
-
webapp/modules/admin/lib/db_admin.php (modified) (21 diffs)
-
webapp_biz/modules/biz/lib/mysql_functions.php (modified) (10 diffs)
Legend:
- Unmodified
- Added
- Removed
-
OpenPNE/branches/prj/postgres/2_9_1/bin/tool_rss_cache.php
r2641 r3651 117 117 $values = implode(",", $values); 118 118 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 } 121 129 } 122 130 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 } 127 137 } 128 138 -
OpenPNE/branches/prj/postgres/2_9_1/lib/include/DB/pgsql.php
r2 r3651 496 496 return $in; 497 497 } elseif (is_bool($in)) { 498 return $in ? 'TRUE' : 'FALSE'; 498 // return $in ? 'TRUE' : 'FALSE'; 499 return $in ? 1 : 0; 499 500 } elseif (is_null($in)) { 500 501 return 'NULL'; -
OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/banner.php
r1856 r3651 18 18 $sql .= ' AND is_hidden_before = 0'; 19 19 } 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 } 21 26 22 27 return db_get_row($sql); … … 36 41 $sql .= ' AND is_hidden_before = 0'; 37 42 } 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 } 39 48 40 49 return db_get_row($sql); -
OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/bookmark.php
r2853 r3651 160 160 $is_recurred = false; 161 161 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 164 170 $params = array(intval($c_member_id)); 165 171 -
OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/commu.php
r3532 r3651 491 491 492 492 $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 } 496 502 $params = array(intval($c_commu_id)); 497 503 $lst = db_get_all_limit($sql, 0, $limit, $params); … … 515 521 $is_recurred = false; 516 522 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 525 543 $params = array((bool)$event_flag, intval($c_commu_id)); 526 544 $list = db_get_all_limit($sql, 0, $limit, $params); … … 615 633 $is_recurred = false; 616 634 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 622 649 $params = array(intval($c_member_id)); 623 650 $lst = db_get_all_limit($sql, 0, $limit, $params); … … 805 832 806 833 $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 811 857 $c_commu_topic_list = db_get_all_limit($sql, 0, $limit); 812 858 … … 851 897 852 898 $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 857 922 $c_commu_topic_list = db_get_all_limit($sql, ($page-1)*$limit, $limit); 858 923 … … 1173 1238 $is_recurred = false; 1174 1239 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 1178 1250 $params = array(intval($c_member_id)); 1179 1251 $c_commu_list = db_get_all_limit($sql, 0, $limit, $params); … … 1387 1459 $sql .= "WHERE ccm.c_commu_id = ?" . 1388 1460 " 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 1390 1468 $params = array(intval($c_commu_id)); 1391 1469 $c_commu_member_list = db_get_all_limit($sql, 0, $limit, $params); … … 1456 1534 1457 1535 $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 1461 1547 return db_get_all_limit($sql, 0, $limit); 1462 1548 } … … 1686 1772 1687 1773 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 } 1696 1797 break; 1697 1798 } … … 1797 1898 function db_commu_c_topic4c_commu_topic_id_2($c_commu_topic_id) 1798 1899 { 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 1806 1918 $params = array(intval($c_commu_topic_id)); 1807 1919 $lst = db_get_row($sql, $params); … … 2402 2514 'r_date' => db_now(), 2403 2515 ); 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 } 2404 2531 if ($data['event_flag']) { 2405 2532 $data += array( 2406 'open_date' => $ topic['open_date'],2533 'open_date' => $open_date, 2407 2534 'open_date_comment' => $topic['open_date_comment'], 2408 2535 'open_pref_id' => intval($topic['open_pref_id']), 2409 2536 'open_pref_comment' => $topic['open_pref_comment'], 2410 'invite_period' => $ topic['invite_period'],2537 'invite_period' => $invite_period, 2411 2538 ); 2412 2539 } … … 2475 2602 'r_date' => db_now(), 2476 2603 ); 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 2477 2620 if ($data['event_flag']) { 2478 2621 $data += array( 2479 'open_date' => $ topic['open_date'],2622 'open_date' => $open_date, 2480 2623 'open_date_comment' => $topic['open_date_comment'], 2481 2624 'open_pref_id' => intval($topic['open_pref_id']), 2482 2625 'open_pref_comment' => $topic['open_pref_comment'], 2483 'invite_period' => $ topic['invite_period'],2626 'invite_period' => $invite_period, 2484 2627 ); 2485 2628 } … … 2557 2700 function db_commu_is_event_join_date($c_commu_topic_id) 2558 2701 { 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 } 2563 2713 $now = date('Y-m-d'); 2564 2714 $params = array(intval($c_commu_topic_id), $now, $now); -
OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/diary.php
r3057 r3651 567 567 $friend_ids = implode(',', $friends); 568 568 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 } 584 609 585 610 $params = array(intval($c_member_id)); … … 789 814 790 815 $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 } 793 824 794 825 $date_format = '%Y-%m-%d 00:00:00'; -
OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/file.php
r3458 r3651 59 59 function db_file_insert_c_file($filename, $bin, $original_filename) 60 60 { 61 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 62 $bin = base64_encode($bin); 63 } 64 61 65 $data = array( 62 66 'filename' => $filename, … … 78 82 $sql = 'SELECT * FROM c_file WHERE filename = ?'; 79 83 $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 } 81 92 } 82 93 -
OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/friend.php
r3057 r3651 257 257 $is_recurred = false; 258 258 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 261 267 $params = array(intval($c_member_id)); 262 268 if ($limit) { … … 339 345 $is_recurred = false; 340 346 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 343 355 $params = array(intval($c_member_id)); 344 356 $list = db_get_all_limit($sql, 0, $limit, $params); … … 455 467 $sql .= " WHERE cf.c_member_id_from = ?" . 456 468 " 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 } 458 474 $params = array(intval($c_member_id)); 459 475 $c_friend_list = db_get_all_limit($sql, 0, $limit, $params); … … 598 614 $sql .= " WHERE cf.c_member_id_from = ?". 599 615 " 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 } 601 621 $params = array(intval($c_member_id)); 602 622 return db_get_all_limit($sql, 0, 50, $params); -
OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/member.php
r3532 r3651 26 26 $is_recurred = false; 27 27 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 29 35 $params = array(intval($c_member_id)); 30 36 if (!$c_member = db_get_row($sql, $params)) -
OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/message.php
r3057 r3651 790 790 function db_message_c_message_sender_list4c_member_id($c_member_id) 791 791 { 792 $sql = "SELECT distinct c_member_id_from FROM c_message";793 792 $where = "c_member_id_to = ?". 794 793 " AND is_deleted_to = 0" . 795 794 " 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 797 810 $sql .= " ORDER BY r_datetime DESC"; 798 811 $params = array(intval($c_member_id)); … … 811 824 function db_message_c_message_receiver_list4c_member_id($c_member_id) 812 825 { 813 $sql = "SELECT distinct c_member_id_to FROM c_message";814 826 $where = "c_member_id_from = ?". 815 827 " AND is_deleted_from = 0" . 816 828 " 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 818 844 $sql .= " ORDER BY r_datetime DESC"; 845 819 846 $params = array(intval($c_member_id)); 820 847 $c_message_list = db_get_all($sql, $params); … … 903 930 } 904 931 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 } 908 941 909 942 $date_format = '%Y-%m-%d 00:00:00'; -
OpenPNE/branches/prj/postgres/2_9_1/webapp/lib/db/review.php
r3532 r3651 179 179 break; 180 180 case "r_num": 181 $order = " ORDER BY write_num DESC, r_datetime DESC";181 $order = " ORDER BY write_num DESC, r_datetime2 DESC"; 182 182 break; 183 183 } 184 184 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 } 193 217 194 218 $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 740 740 } else { 741 741 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 } 743 747 } else { 744 748 $sql .= ' ORDER BY value'; … … 941 945 function p_access_analysis_month_access_analysis_month($ktai_flag) 942 946 { 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 947 959 $params = array(intval($ktai_flag)); 948 960 $list = db_get_all($sql,$params); … … 953 965 function p_access_analysis_day_access_analysis_day($ym, $ktai_flag) 954 966 { 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" . 956 969 " FROM c_access_log " . 957 " where left(r_datetime,7) = ?" .970 " where substr(r_datetime,1,7) = ?" . 958 971 " and ktai_flag = ? " . 959 972 " 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 960 981 $params = array(intval(substr($ym,0,7)),intval($ktai_flag)); 961 982 $list = db_get_all($sql,$params); … … 1009 1030 $params = array(intval($ktai_flag)); 1010 1031 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 1012 1038 array_push($params,substr($ymd,0,7)); 1013 1039 } 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 } 1015 1045 array_push($params,$ymd); 1016 1046 } … … 1104 1134 $params = array(intval($ktai_flag)); 1105 1135 if ($month_flag) { 1136 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1137 $sql .= " and substr(r_datetime,1,7) = ? "; 1138 } else { 1106 1139 $sql .= " and left(r_datetime, 7) = ? "; 1107 array_push($params,substr($ymd,0,7)); 1140 } 1141 1142 array_push($params,substr($ymd,0,7)); 1108 1143 } else { 1144 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1145 $sql .= " and substr(r_datetime,1,10) = ? "; 1146 } else { 1109 1147 $sql .= " and left(r_datetime,10) = ? "; 1110 array_push($params,$ymd); 1148 } 1149 1150 array_push($params,$ymd); 1111 1151 } 1112 1152 if ($page_name!="all") { … … 1133 1173 $params = array(intval($ktai_flag)); 1134 1174 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 1136 1181 array_push($params,substr($ymd,0,7)); 1137 1182 } 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 1139 1189 array_push($params,$ymd); 1140 1190 } … … 1184 1234 $params = array(intval($ktai_flag)); 1185 1235 if ($month_flag) { 1236 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1237 $where .= " and substr(r_datetime,1, 7) = ? "; 1238 } else { 1186 1239 $where .= " and left(r_datetime, 7) = ? "; 1187 array_push($params,substr($ymd,0,7)); 1240 } 1241 1242 array_push($params,substr($ymd,0,7)); 1188 1243 } else { 1244 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1245 $where .= " and substr(r_datetime,1,10) = ? "; 1246 } else { 1189 1247 $where .= " and left(r_datetime,10) = ? "; 1190 array_push($params,$ymd); 1248 } 1249 1250 array_push($params,$ymd); 1191 1251 } 1192 1252 if ($page_name!="all") { … … 1254 1314 $params = array(intval($ktai_flag)); 1255 1315 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 1257 1322 array_push($params,substr($ymd,0,7)); 1258 1323 } 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 } 1260 1329 array_push($params,$ymd); 1261 1330 } … … 1284 1353 $params = array(intval($ktai_flag)); 1285 1354 if ($month_flag) { 1355 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1356 $sql .= " and substr(r_datetime,1,7) = ? "; 1357 } else { 1286 1358 $sql .= " and left(r_datetime, 7) = ? "; 1287 array_push($params,substr($ymd,0,7)); 1359 } 1360 1361 array_push($params,substr($ymd,0,7)); 1288 1362 } else { 1363 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1364 $sql .= " and substr(r_datetime,1,10) = ? "; 1365 } else { 1289 1366 $sql .= " and left(r_datetime,10) = ? "; 1290 array_push($params,$ymd); 1367 } 1368 1369 array_push($params,$ymd); 1291 1370 } 1292 1371 $sql .= " and target_c_diary_id <> 0 "; … … 1332 1411 $params = array(intval($ktai_flag)); 1333 1412 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 1335 1419 array_push($params,substr($ymd,0,7)); 1336 1420 } 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 1338 1427 array_push($params,$ymd); 1339 1428 } … … 1398 1487 $params = array(intval($ktai_flag)); 1399 1488 if ($month_flag) { 1489 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1490 $where .= " and substr(r_datetime,1,7) = ? "; 1491 } else { 1400 1492 $where .= " and left(r_datetime, 7) = ? "; 1401 array_push($params,substr($ymd,0,7)); 1493 } 1494 1495 array_push($params,substr($ymd,0,7)); 1402 1496 } else { 1497 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1498 $where .= " and substr(r_datetime,1,10) = ? "; 1499 } else { 1403 1500 $where .= " and left(r_datetime,10) = ? "; 1404 array_push($params,$ymd); 1501 } 1502 1503 array_push($params,$ymd); 1405 1504 } 1406 1505 if ($page_name!="all") { … … 1429 1528 $params = array(intval($ktai_flag)); 1430 1529 if ($month_flag) { 1530 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1531 $where .= " and substr(r_datetime,1,7) = ? "; 1532 } else { 1431 1533 $where .= " and left(r_datetime, 7) = ? "; 1432 array_push($params,substr($ymd,0,7)); 1534 } 1535 1536 array_push($params,substr($ymd,0,7)); 1433 1537 } else { 1538 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1539 $where .= " and substr(r_datetime,1,10) = ? "; 1540 } else { 1434 1541 $where .= " and left(r_datetime,10) = ? "; 1435 array_push($params,$ymd); 1542 } 1543 1544 array_push($params,$ymd); 1436 1545 } 1437 1546 if ($page_name != "all") { … … 1519 1628 $page_size = intval($page_size); 1520 1629 1521 $limit = "";1522 1523 //page_sizeが0の時は全て表示(pagerなし)1524 if ($page_size != 0) {1525 $limit = " OFFSET ".($page_size*($page-1))." LIMIT ".$page_size;1526 }1527 1630 $where = " WHERE true "; 1528 1631 … … 1542 1645 1543 1646 $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 } 1545 1652 1546 1653 $sql = "select count(*) from c_member".$where; … … 1603 1710 ); 1604 1711 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 1608 1722 $lst = db_get_all($sql); 1609 1723 … … 1649 1763 function get_analysis_date_month($year = "", $month = "") 1650 1764 { 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 1652 1771 $start_date = db_get_one($sql); 1653 1772 … … 1659 1778 }while($date < date("Y-m")); 1660 1779 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 } 1662 1785 $lst = db_get_all($sql); 1663 1786 foreach ($lst as $value) { … … 1692 1815 } 1693 1816 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 } 1696 1822 $params = array($date); 1697 1823 $lst = db_get_all($sql,$params); … … 2051 2177 2052 2178 $sql = $select . $from . $where . $order; 2053 2179 2054 2180 $list = db_get_all_limit($sql,($page-1)*$page_size,$page_size,$params); 2055 2181 … … 2127 2253 $page_size = intval($page_size); 2128 2254 2129 $where = " where c _commu_topic_id = ? ";2255 $where = " where ct.c_commu_topic_id = ? "; 2130 2256 $params[] = $c_commu_topic_id; 2131 2257 … … 2138 2264 2139 2265 $sql = $select . $from . $where . $order; 2140 2141 2266 $list = db_get_all_limit($sql,($page-1)*$page_size,$page_size,$params); 2142 2267 -
OpenPNE/branches/prj/postgres/2_9_1/webapp_biz/modules/biz/lib/mysql_functions.php
r3532 r3651 232 232 ); 233 233 234 $ids = db_get_col($sql, $param );234 $ids = db_get_col($sql, $params); 235 235 236 236 $schedule = array(); … … 336 336 $sql = "SELECT * FROM biz_group_member"; 337 337 $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 } 339 343 340 344 $params = array( … … 369 373 370 374 //指定された条件に見合うグループのリストを得る関数 371 function biz_getGroupList($keyword = '', $page = 0, $page_size = 20 , $order = 'biz_group_id')375 function biz_getGroupList($keyword = '', $page = 0, $page_size = 20) 372 376 { 373 377 374 378 //keywordあり 375 379 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'; 377 381 $sql = 'SELECT * FROM biz_group'. $where; 378 382 … … 380 384 '%'.$keyword.'%', 381 385 '%'.$keyword.'%', 382 $order,383 386 ); 384 387 $list = db_get_all_page($sql, $page, $page_size, $params); … … 386 389 } else { 387 390 $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); 394 395 } 395 396 … … 401 402 $count = count(biz_getGroupMember($value['biz_group_id'])); 402 403 $list[$key]['count'] = $count; 403 }404 405 if ($keyword) {406 $params = array(407 '%'.$keyword.'%',408 '%'.$keyword.'%',409 $order,410 );411 } else {412 $params = array();413 404 } 414 405 … … 447 438 if ($id) { 448 439 $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'; 450 441 } 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'; 452 443 } 453 444 $list = db_get_all($sql, $params); … … 710 701 $sql = 'SELECT * FROM biz_todo WHERE biz_todo_id IN ('.$ids.')' . 711 702 ' AND due_datetime > ? AND due_datetime <= ?'; 703 $max_day = date("t", mktime(0,0,0,intval($month),1,intval($year) )); 712 704 $params = array( 713 sprintf('%04d-%02d', intval($year), intval($month)) . '-0 000:00:00',714 sprintf('%04d-%02d', intval($year), intval($month)) . '- 3123: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' 715 707 ); 716 708 $list = db_get_all($sql, $params); … … 891 883 { 892 884 $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 893 895 $params = array( 894 896 $title, … … 1115 1117 } 1116 1118 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 1117 1129 $data = array( 1118 1130 'c_member_id' => $member_id,
Note: See TracChangeset
for help on using the changeset viewer.