Changeset 3738
- Timestamp:
- Aug 21, 2007, 1:20:30 PM (15 years ago)
- Location:
- OpenPNE/trunk
- Files:
-
- 14 edited
- 13 copied
-
bin/tool_rss_cache.php (modified) (1 diff)
-
bin/tool_vacuum_exec.cron (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/bin/tool_vacuum_exec.cron) (1 diff)
-
bin/tool_vacuum_exec.php (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/bin/tool_vacuum_exec.php) (1 diff)
-
lib/include/DB/pgsql.php (modified) (1 diff)
-
setup/OpenPNE_Setup_pgsql.html (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/OpenPNE_Setup_pgsql.html) (3 diffs)
-
setup/sql/postgres74 (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74)
-
setup/sql/postgres74/install (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74/install)
-
setup/sql/postgres74/install/install-2.8-create_tables.sql (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74/install/install-2.8-create_tables.sql)
-
setup/sql/postgres74/install/install-2.8-insert_data.sql (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74/install/install-2.8-insert_data.sql)
-
setup/sql/postgres74/option (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74/option)
-
setup/sql/postgres74/option/pnebiz-header.sql (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74/option/pnebiz-header.sql)
-
setup/sql/postgres74/update (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74/update)
-
setup/sql/postgres74/update/update01-for2.9.0-insert_c_cmd.sql (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74/update/update01-for2.9.0-insert_c_cmd.sql)
-
setup/sql/postgres74/update/update02-for2.9.2-diary_number.sql (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74/update/update02-for2.9.2-diary_number.sql)
-
setup/sql/postgres74/update/update03-for2.9.2-diary_category_index.sql (copied) (copied from OpenPNE/branches/prj/postgres/2_9_1/setup/sql/postgres74/update/update03-for2.9.2-diary_category_index.sql)
-
webapp/lib/db/banner.php (modified) (2 diffs)
-
webapp/lib/db/bookmark.php (modified) (1 diff)
-
webapp/lib/db/common.php (modified) (1 diff)
-
webapp/lib/db/commu.php (modified) (15 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) (2 diffs)
-
webapp/modules/admin/lib/db_admin.php (modified) (16 diffs)
-
webapp_biz/modules/biz/lib/mysql_functions.php (modified) (10 diffs)
Legend:
- Unmodified
- Added
- Removed
-
OpenPNE/trunk/bin/tool_rss_cache.php
r2641 r3738 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/trunk/bin/tool_vacuum_exec.cron
r3737 r3738 2 2 cd $1 3 3 $2 tool_vacuum_exec.php 4 -
OpenPNE/trunk/bin/tool_vacuum_exec.php
r3737 r3738 12 12 db_query($sql); 13 13 } 14 14 15 ?> -
OpenPNE/trunk/lib/include/DB/pgsql.php
r2 r3738 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/trunk/setup/OpenPNE_Setup_pgsql.html
r3737 r3738 216 216 使用データベース、接続用ユーザー名、パスワード、サーバホスト名、データベース名を設定します。<br> 217 217 config.php 218 219 220 'phptype' => 'pgsql'</pre> 221 <dd>218 <pre> 219 // 使用するデータベースにPostgreSQLを指定。 220 'phptype' => 'pgsql' 221 </pre></dd> 222 222 <dt>ENCRYPT_KEY</dt> 223 223 <dd>メンバーのログイン情報を暗号化する際に使用するキー(56バイト以内のASCII文字)<br> … … 271 271 </pre> 272 272 </div> 273 273 274 274 275 <h2 id="section4">4. サーバ設定</h2> … … 435 436 <em>$GLOBALS['_OPENPNE_DISABLE_MODULES'] = array('pc', 'ktai');</em> 436 437 ?> 437 </pre> l438 </pre> 438 439 </div> 439 440 -
OpenPNE/trunk/webapp/lib/db/banner.php
r1856 r3738 18 18 $sql .= ' AND is_hidden_before = 0'; 19 19 } 20 $sql .= ' ORDER BY RAND()';20 $sql .= db_order_by_rand(); 21 21 22 22 return db_get_row($sql); … … 36 36 $sql .= ' AND is_hidden_before = 0'; 37 37 } 38 $sql .= ' ORDER BY RAND()';38 $sql .= db_order_by_rand(); 39 39 40 40 return db_get_row($sql); -
OpenPNE/trunk/webapp/lib/db/bookmark.php
r2853 r3738 161 161 162 162 $sql = 'SELECT c_member_id_to AS c_member_id FROM c_bookmark' . 163 ' WHERE c_member_id_from = ? ORDER BY RAND()';163 ' WHERE c_member_id_from = ?' . db_order_by_rand(); 164 164 $params = array(intval($c_member_id)); 165 165 -
OpenPNE/trunk/webapp/lib/db/common.php
r1858 r3738 171 171 } 172 172 173 /** 174 * MySQL: ORDER BY RAND() 175 * PgSQL: ORDER BY RANDOM() 176 */ 177 function db_order_by_rand() 178 { 179 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 180 $order = ' ORDER BY RANDOM()'; 181 } else { 182 $order = ' ORDER BY RAND()'; 183 } 184 return $order; 185 } 186 173 187 ?> -
OpenPNE/trunk/webapp/lib/db/commu.php
r3718 r3738 493 493 $sql = 'SELECT c_member.* FROM c_member, c_commu_member' . 494 494 ' WHERE c_member.c_member_id = c_commu_member.c_member_id' . 495 ' AND c_commu_id = ? ORDER BY RAND()';495 ' AND c_commu_id = ?' . db_order_by_rand(); 496 496 $params = array(intval($c_commu_id)); 497 497 $lst = db_get_all_limit($sql, 0, $limit, $params); … … 515 515 $is_recurred = false; 516 516 517 $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id " . 518 " , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 " . 519 " FROM c_commu_topic_comment as cctc , c_commu_topic as cct" . 520 " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id " . 521 " AND cct.event_flag = ?". 522 " AND cct.c_commu_id = ?". 523 " group by cct.c_commu_topic_id " . 524 " order by r_datetime desc "; 517 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 518 $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id" . 519 " , max(cctc.image_filename1) as image_filename1, max(cctc.image_filename2) as image_filename2, max(cctc.image_filename3) as image_filename3 " . 520 " FROM c_commu_topic_comment as cctc , c_commu_topic as cct" . 521 " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id " . 522 " AND cct.event_flag = ?". 523 " AND cct.c_commu_id = ?". 524 " group by cct.c_commu_topic_id, cct.name, cct.c_commu_id " . 525 " order by r_datetime desc "; 526 } else { 527 $sql = "SELECT cct.c_commu_topic_id , cct.name, MAX(cctc.r_datetime) as r_datetime , cct.c_commu_id " . 528 " , cctc.image_filename1, cctc.image_filename2, cctc.image_filename3 " . 529 " FROM c_commu_topic_comment as cctc , c_commu_topic as cct" . 530 " WHERE cctc.c_commu_topic_id = cct.c_commu_topic_id " . 531 " AND cct.event_flag = ?". 532 " AND cct.c_commu_id = ?". 533 " group by cct.c_commu_topic_id " . 534 " order by r_datetime desc "; 535 } 525 536 $params = array((bool)$event_flag, intval($c_commu_id)); 526 537 $list = db_get_all_limit($sql, 0, $limit, $params); … … 619 630 " WHERE c_commu_member.c_member_id = ?". 620 631 " AND c_commu.c_commu_id = c_commu_member.c_commu_id" . 621 " ORDER BY RAND()";632 db_order_by_rand(); 622 633 $params = array(intval($c_member_id)); 623 634 $lst = db_get_all_limit($sql, 0, $limit, $params); … … 805 816 806 817 $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)'); 807 $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'. 808 ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'. 809 ' GROUP BY cctc.c_commu_topic_id'. 810 ' ORDER BY r_datetime DESC'; 818 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 819 $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id'. 820 ' FROM (' . 821 ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime' . 822 ' FROM' . 823 ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . 824 ' WHERE ' . 825 ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'. 826 ' GROUP BY cct.c_commu_topic_id' . 827 ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct' . 828 ' WHERE' . 829 ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id' . 830 ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id ' . 831 ' AND cctc.r_datetime=sub_cct_tbl.r_datetime' . 832 ' ORDER BY r_datetime DESC'; 833 } else { 834 $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'. 835 ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'. 836 ' GROUP BY cctc.c_commu_topic_id'. 837 ' ORDER BY r_datetime DESC'; 838 } 811 839 $c_commu_topic_list = db_get_all_limit($sql, 0, $limit); 812 840 … … 851 879 852 880 $hint = db_mysql_hint('USE INDEX (r_datetime_c_commu_id)'); 853 $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'. 854 ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'. 855 ' GROUP BY cctc.c_commu_topic_id'. 856 ' ORDER BY r_datetime DESC'; 881 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 882 $sql = 'SELECT sub_cct_tbl.c_commu_topic_id, cct.c_commu_id, sub_cct_tbl.r_datetime, cct.c_member_id'. 883 ' FROM (' . 884 ' SELECT cct.c_commu_topic_id, MAX(cctc.r_datetime) as r_datetime' . 885 ' FROM' . 886 ' c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . 887 ' WHERE ' . 888 ' cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'. 889 ' GROUP BY cct.c_commu_topic_id' . 890 ') as sub_cct_tbl, c_commu_topic_comment as cctc , c_commu_topic as cct' . 891 ' WHERE' . 892 ' cct.c_commu_topic_id=sub_cct_tbl.c_commu_topic_id' . 893 ' AND cctc.c_commu_topic_id = cct.c_commu_topic_id ' . 894 ' AND cctc.r_datetime=sub_cct_tbl.r_datetime' . 895 ' ORDER BY r_datetime DESC'; 896 } else { 897 $sql = 'SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id'. 898 ' FROM c_commu_topic as cct, c_commu_topic_comment as cctc'. $hint . ' WHERE cct.c_commu_id IN (' . $ids . ') AND cctc.c_commu_topic_id = cct.c_commu_topic_id'. 899 ' GROUP BY cctc.c_commu_topic_id'. 900 ' ORDER BY r_datetime DESC'; 901 } 857 902 $c_commu_topic_list = db_get_all_limit($sql, ($page-1)*$limit, $limit); 858 903 … … 1173 1218 $is_recurred = false; 1174 1219 1175 $sql = 'SELECT DISTINCT c.* FROM c_commu_member AS cm, c_commu AS c' . 1176 ' WHERE cm.c_member_id = ? AND c.c_commu_id = cm.c_commu_id' . 1177 ' ORDER BY RAND()'; 1220 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1221 $sql = 'SELECT DISTINCT c.*, RANDOM() FROM c_commu_member AS cm, c_commu AS c' . 1222 ' WHERE cm.c_member_id = ? AND c.c_commu_id = cm.c_commu_id'; 1223 } else { 1224 $sql = 'SELECT DISTINCT c.* FROM c_commu_member AS cm, c_commu AS c' . 1225 ' WHERE cm.c_member_id = ? AND c.c_commu_id = cm.c_commu_id'; 1226 } 1227 $sql .= db_order_by_rand(); 1178 1228 $params = array(intval($c_member_id)); 1179 1229 $c_commu_list = db_get_all_limit($sql, 0, $limit, $params); … … 1387 1437 $sql .= "WHERE ccm.c_commu_id = ?" . 1388 1438 " AND ccm.c_member_id = cm.c_member_id"; 1389 $sql .= " ORDER BY RAND()";1439 $sql .= db_order_by_rand(); 1390 1440 $params = array(intval($c_commu_id)); 1391 1441 $c_commu_member_list = db_get_all_limit($sql, 0, $limit, $params); … … 1458 1508 $sql = 'SELECT c_member_id, nickname FROM c_member' . 1459 1509 ' WHERE c_member_id IN ( '. $result . ') ' . 1460 ' ORDER BY RAND()';1510 db_order_by_rand(); 1461 1511 return db_get_all_limit($sql, 0, $limit); 1462 1512 } … … 1688 1738 $from = ' FROM c_commu, c_commu_member'; 1689 1739 $order = ' ORDER BY count_commu_member DESC'; 1690 $group = ' GROUP BY c_commu_member.c_commu_id'; 1691 $sql = $select . ', COUNT(c_commu_member.c_member_id) AS count_commu_member' . 1692 $from . 1693 $where . ' AND c_commu_member.c_commu_id = c_commu.c_commu_id' . 1694 $group . 1695 $order; 1740 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1741 $sub_tbl = ' (' . 1742 'SELECT c_commu_member.c_commu_id, count(c_commu_member.c_commu_member_id) as count_commu_member' . 1743 $from . 1744 $where . ' AND c_commu_member.c_commu_id = c_commu.c_commu_id' . 1745 ' GROUP BY c_commu_member.c_commu_id' . 1746 ') as sub_commu_tbl'; 1747 $sql = $select . 1748 ' FROM' . $sub_tbl . ', c_commu' . 1749 ' WHERE c_commu.c_commu_id = sub_commu_tbl.c_commu_id' . 1750 $order; 1751 } else { 1752 $group = ' GROUP BY c_commu_member.c_commu_id'; 1753 $sql = $select . ', COUNT(c_commu_member.c_member_id) AS count_commu_member' . 1754 $from . 1755 $where . ' AND c_commu_member.c_commu_id = c_commu.c_commu_id' . 1756 $group . 1757 $order; 1758 } 1696 1759 break; 1697 1760 } … … 1797 1860 function db_commu_c_topic4c_commu_topic_id_2($c_commu_topic_id) 1798 1861 { 1799 $sql = "SELECT cct.*, cctc.*, cm.nickname, cpp.pref" . 1800 " FROM c_commu_topic as cct" . 1801 " LEFT JOIN c_commu_topic_comment as cctc ON cct.c_commu_topic_id = cctc.c_commu_topic_id" . 1802 " LEFT JOIN c_member as cm ON cct.c_member_id = cm.c_member_id" . 1803 " LEFT JOIN c_profile_pref as cpp ON cct.open_pref_id = cpp.c_profile_pref_id" . 1804 " WHERE cct.c_commu_topic_id = ?". 1805 " AND cctc.number = 0"; 1862 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1863 $sql = "SELECT cct.*, cctc.*, cm.nickname, cpp.pref, case when cct.invite_period = '0001-01-01 BC' THEN '0000-00-00' ELSE to_char(cct.invite_period,'YYYY-MM-DD') END as invite_period" . 1864 " FROM c_commu_topic as cct" . 1865 " LEFT JOIN c_commu_topic_comment as cctc ON cct.c_commu_topic_id = cctc.c_commu_topic_id" . 1866 " LEFT JOIN c_member as cm ON cct.c_member_id = cm.c_member_id" . 1867 " LEFT JOIN c_profile_pref as cpp ON cct.open_pref_id = cpp.c_profile_pref_id" . 1868 " WHERE cct.c_commu_topic_id = ?". 1869 " AND cctc.number = 0"; 1870 } else { 1871 $sql = "SELECT cct.*, cctc.*, cm.nickname, cpp.pref" . 1872 " FROM c_commu_topic as cct" . 1873 " LEFT JOIN c_commu_topic_comment as cctc ON cct.c_commu_topic_id = cctc.c_commu_topic_id" . 1874 " LEFT JOIN c_member as cm ON cct.c_member_id = cm.c_member_id" . 1875 " LEFT JOIN c_profile_pref as cpp ON cct.open_pref_id = cpp.c_profile_pref_id" . 1876 " WHERE cct.c_commu_topic_id = ?". 1877 " AND cctc.number = 0"; 1878 } 1806 1879 $params = array(intval($c_commu_topic_id)); 1807 1880 $lst = db_get_row($sql, $params); … … 2404 2477 'r_date' => db_now(), 2405 2478 ); 2479 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 2480 $open_date = '0000-01-01'; 2481 $invite_period = '0000-01-01'; 2482 if (isset($topic['open_date']) && $topic['open_date'] != '') { 2483 $open_date = $topic['open_date']; 2484 } 2485 2486 if (isset($topic['invite_period']) && $topic['invite_period'] != '') { 2487 $invite_period = $topic['invite_period']; 2488 } 2489 } else { 2490 $open_date = $topic['open_date']; 2491 $invete_period = $topic['invite_period']; 2492 } 2406 2493 if ($data['event_flag']) { 2407 2494 $data += array( 2408 'open_date' => $ topic['open_date'],2495 'open_date' => $open_date, 2409 2496 'open_date_comment' => $topic['open_date_comment'], 2410 2497 'open_pref_id' => intval($topic['open_pref_id']), 2411 2498 'open_pref_comment' => $topic['open_pref_comment'], 2412 'invite_period' => $ topic['invite_period'],2499 'invite_period' => $invite_period, 2413 2500 ); 2414 2501 } … … 2477 2564 'r_date' => db_now(), 2478 2565 ); 2566 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 2567 $open_date = '0000-01-01'; 2568 $invite_period = '0000-01-01'; 2569 if (isset($topic['open_date']) && $topic['open_date'] != '') { 2570 $open_date = $topic['open_date']; 2571 } 2572 2573 if (isset($topic['invite_period']) && $topic['invite_period'] != '') { 2574 $invite_period = $topic['invite_period']; 2575 } 2576 } else { 2577 $open_date = $topic['open_date']; 2578 $invete_period = $topic['invite_period']; 2579 } 2479 2580 if ($data['event_flag']) { 2480 2581 $data += array( 2481 'open_date' => $ topic['open_date'],2582 'open_date' => $open_date, 2482 2583 'open_date_comment' => $topic['open_date_comment'], 2483 2584 'open_pref_id' => intval($topic['open_pref_id']), 2484 2585 'open_pref_comment' => $topic['open_pref_comment'], 2485 'invite_period' => $ topic['invite_period'],2586 'invite_period' => $invite_period, 2486 2587 ); 2487 2588 } … … 2559 2660 function db_commu_is_event_join_date($c_commu_topic_id) 2560 2661 { 2561 $sql = 'SELECT c_commu_topic_id FROM c_commu_topic' 2562 . ' WHERE c_commu_topic_id = ?' 2563 . ' AND (open_date >= ? OR open_date = \'0000-00-00\')' 2564 . ' AND (invite_period >= ? OR invite_period = \'0000-00-00\')'; 2662 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 2663 $sql = 'SELECT c_commu_topic_id FROM c_commu_topic' 2664 . ' WHERE c_commu_topic_id = ?' 2665 . ' AND (open_date >= ? OR open_date = \'0000-01-01\')' 2666 . ' AND (invite_period >= ? OR invite_period = \'0000-01-01\')'; 2667 } else { 2668 $sql = 'SELECT c_commu_topic_id FROM c_commu_topic' 2669 . ' WHERE c_commu_topic_id = ?' 2670 . ' AND (open_date >= ? OR open_date = \'0000-00-00\')' 2671 . ' AND (invite_period >= ? OR invite_period = \'0000-00-00\')'; 2672 } 2565 2673 $now = date('Y-m-d'); 2566 2674 $params = array(intval($c_commu_topic_id), $now, $now); … … 2794 2902 $c_commu_id = 0) 2795 2903 { 2796 $select = 'SELECT c.name AS commu_name, c.image_filename AS commu_image' 2797 . ', ct.*, MAX(ctc.r_datetime) AS max_datetime'; 2904 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 2905 $select = 'SELECT distinct on (ct.c_commu_topic_id) c.name AS commu_name, c.image_filename AS commu_image' 2906 . ', ct.*, ctc2.max_datetime'; 2907 } else { 2908 $select = 'SELECT c.name AS commu_name, c.image_filename AS commu_image' 2909 . ', ct.*, MAX(ctc.r_datetime) AS max_datetime'; 2910 } 2911 2798 2912 $from = ' FROM c_commu AS c, c_commu_topic AS ct, c_commu_topic_comment AS ctc'; 2913 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 2914 $from .= ', (SELECT c_commu_topic_id, max(r_datetime) as max_datetime' . 2915 ' FROM c_commu_topic_comment' . 2916 ' GROUP BY c_commu_topic_id' . 2917 ') AS ctc2'; 2918 } 2799 2919 2800 2920 $params = array(); 2801 2921 $where = ' WHERE ct.c_commu_topic_id = ctc.c_commu_topic_id' 2802 2922 . ' AND c.c_commu_id = ct.c_commu_id'; 2923 2924 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 2925 $where .= ' AND ct.c_commu_topic_id = ctc2.c_commu_topic_id'; 2926 } 2927 2803 2928 if ($c_commu_id) { 2804 2929 $where .= ' AND ct.c_commu_id = ?'; … … 2831 2956 $order = ' ORDER BY max_datetime DESC'; 2832 2957 2833 $sql = $select . $from . $where . $group . $order; 2958 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 2959 $sql = "SELECT sub_tbl.* FROM (" . $select . $from . $where . ") as sub_tbl " . $order; 2960 } else { 2961 $sql = $select . $from . $where . $group . $order; 2962 } 2834 2963 $list = db_get_all_page($sql, $page, $page_size, $params); 2835 2964 -
OpenPNE/trunk/webapp/lib/db/diary.php
r3718 r3738 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/trunk/webapp/lib/db/file.php
r3458 r3738 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/trunk/webapp/lib/db/friend.php
r3057 r3738 258 258 259 259 $sql = 'SELECT c_member_id_to AS c_member_id FROM c_friend' . 260 ' WHERE c_member_id_from = ? ORDER BY RAND()';260 ' WHERE c_member_id_from = ?' . db_order_by_rand(); 261 261 $params = array(intval($c_member_id)); 262 262 if ($limit) { … … 340 340 341 341 $sql = 'SELECT * FROM c_friend' . 342 ' WHERE c_member_id_to = ? AND intro <> \'\' ORDER BY RAND()';342 ' WHERE c_member_id_to = ? AND intro <> \'\'' . db_order_by_rand(); 343 343 $params = array(intval($c_member_id)); 344 344 $list = db_get_all_limit($sql, 0, $limit, $params); … … 455 455 $sql .= " WHERE cf.c_member_id_from = ?" . 456 456 " AND cm.c_member_id=cf.c_member_id_to"; 457 $sql .= " ORDER BY RAND()";457 $sql .= db_order_by_rand(); 458 458 $params = array(intval($c_member_id)); 459 459 $c_friend_list = db_get_all_limit($sql, 0, $limit, $params); … … 598 598 $sql .= " WHERE cf.c_member_id_from = ?". 599 599 " AND cf.c_member_id_to = cm.c_member_id"; 600 $sql .= " ORDER BY RAND()";600 $sql .= db_order_by_rand(); 601 601 $params = array(intval($c_member_id)); 602 602 return db_get_all_limit($sql, 0, 50, $params); -
OpenPNE/trunk/webapp/lib/db/member.php
r3718 r3738 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/trunk/webapp/lib/db/message.php
r3681 r3738 795 795 function db_message_c_message_sender_list4c_member_id($c_member_id) 796 796 { 797 $sql = "SELECT distinct c_member_id_from FROM c_message";798 797 $where = "c_member_id_to = ?". 799 798 " AND is_deleted_to = 0" . 800 799 " AND is_send = 1"; 801 $sql .= " WHERE $where"; 800 801 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 802 $sql = "SELECT c_member_id_from" . 803 " FROM" . 804 "(" . 805 "SELECT distinct on(c_member_id_from) *" . 806 " FROM" . 807 " c_message" . 808 " WHERE " . $where . 809 ") as sub_member_tbl"; 810 } else { 811 $sql = "SELECT distinct c_member_id_from FROM c_message"; 812 $sql .= " WHERE $where"; 813 } 802 814 $sql .= " ORDER BY r_datetime DESC"; 803 815 $params = array(intval($c_member_id)); … … 816 828 function db_message_c_message_receiver_list4c_member_id($c_member_id) 817 829 { 818 $sql = "SELECT distinct c_member_id_to FROM c_message";819 830 $where = "c_member_id_from = ?". 820 831 " AND is_deleted_from = 0" . 821 832 " AND is_send = 1"; 822 $sql .= " WHERE $where"; 833 834 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 835 $sql = "SELECT c_member_id_to" . 836 " FROM" . 837 "(" . 838 "SELECT distinct on(c_member_id_to) *" . 839 " FROM" . 840 " c_message" . 841 " WHERE " . $where . 842 ") as sub_member_tbl"; 843 } else { 844 $sql = "SELECT distinct c_member_id_to FROM c_message"; 845 $sql .= " WHERE $where"; 846 } 847 823 848 $sql .= " ORDER BY r_datetime DESC"; 849 824 850 $params = array(intval($c_member_id)); 825 851 $c_message_list = db_get_all($sql, $params); … … 908 934 } 909 935 910 $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_message' . 911 " WHERE $where" . 912 ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?'; 936 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 937 $sql = "SELECT DISTINCT date_part('day', r_datetime) FROM c_message" . 938 " WHERE $where" . 939 ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?'; 940 } else { 941 $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_message' . 942 " WHERE $where" . 943 ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?'; 944 } 913 945 914 946 $date_format = '%Y-%m-%d 00:00:00'; -
OpenPNE/trunk/webapp/lib/db/review.php
r3591 r3738 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 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 186 $group = " GROUP BY c_review.c_review_id" . 187 ", c_review.title" . 188 ", c_review.release_date" . 189 ", c_review.manufacturer" . 190 ", c_review.author" . 191 ", c_review.c_review_category_id" . 192 ", c_review.image_small" . 193 ", c_review.image_medium" . 194 ", c_review.image_large" . 195 ", c_review.url" . 196 ", c_review.asin" . 197 ", c_review.list_price" . 198 ", c_review.retail_price" . 199 ", c_review.r_datetime"; 200 } else { 201 $group = " GROUP BY c_review.c_review_id"; 202 } 185 203 $sql = "SELECT" . 186 204 " c_review.*" . … … 189 207 $from . 190 208 $where . 191 " GROUP BY c_review.c_review_id".209 $group . 192 210 $order; 193 211 -
OpenPNE/trunk/webapp/modules/admin/lib/db_admin.php
r3695 r3738 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 } 960 980 $params = array(intval(substr($ym,0,7)),intval($ktai_flag)); 961 981 $list = db_get_all($sql,$params); … … 1009 1029 $params = array(intval($ktai_flag)); 1010 1030 if ($month_flag) { 1011 $sql .= " and left(r_datetime, 7) = ? "; 1031 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1032 $sql .= " and substr(r_datetime,1,7) = ? "; 1033 } else { 1034 $sql .= " and left(r_datetime, 7) = ? "; 1035 } 1036 1012 1037 array_push($params,substr($ymd,0,7)); 1013 1038 } else { 1014 $sql .= " and left(r_datetime,10) = ? "; 1039 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1040 $sql .= " and substr(r_datetime,1,10) = ? "; 1041 } else { 1042 $sql .= " and left(r_datetime,10) = ? "; 1043 } 1015 1044 array_push($params,$ymd); 1016 1045 } … … 1104 1133 $params = array(intval($ktai_flag)); 1105 1134 if ($month_flag) { 1135 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1136 $sql .= " and substr(r_datetime,1,7) = ? "; 1137 } else { 1106 1138 $sql .= " and left(r_datetime, 7) = ? "; 1107 array_push($params,substr($ymd,0,7)); 1139 } 1140 array_push($params,substr($ymd,0,7)); 1108 1141 } else { 1142 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1143 $sql .= " and substr(r_datetime,1,10) = ? "; 1144 } else { 1109 1145 $sql .= " and left(r_datetime,10) = ? "; 1110 array_push($params,$ymd); 1146 } 1147 array_push($params,$ymd); 1111 1148 } 1112 1149 if ($page_name!="all") { … … 1133 1170 $params = array(intval($ktai_flag)); 1134 1171 if ($month_flag) { 1135 $sql .= " and left(r_datetime, 7) = ? "; 1172 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1173 $sql .= " and substr(r_datetime,1,7) = ? "; 1174 } else { 1175 $sql .= " and left(r_datetime, 7) = ? "; 1176 } 1136 1177 array_push($params,substr($ymd,0,7)); 1137 1178 } else { 1138 $sql .= " and left(r_datetime,10) = ? "; 1179 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1180 $sql .= " and substr(r_datetime,1,10) = ? "; 1181 } else { 1182 $sql .= " and left(r_datetime,10) = ? "; 1183 } 1139 1184 array_push($params,$ymd); 1140 1185 } … … 1184 1229 $params = array(intval($ktai_flag)); 1185 1230 if ($month_flag) { 1231 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1232 $where .= " and substr(r_datetime,1, 7) = ? "; 1233 } else { 1186 1234 $where .= " and left(r_datetime, 7) = ? "; 1187 array_push($params,substr($ymd,0,7)); 1235 } 1236 array_push($params,substr($ymd,0,7)); 1188 1237 } else { 1238 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1239 $where .= " and substr(r_datetime,1,10) = ? "; 1240 } else { 1189 1241 $where .= " and left(r_datetime,10) = ? "; 1190 array_push($params,$ymd); 1242 } 1243 array_push($params,$ymd); 1191 1244 } 1192 1245 if ($page_name!="all") { … … 1254 1307 $params = array(intval($ktai_flag)); 1255 1308 if ($month_flag) { 1256 $sql .= " and left(r_datetime, 7) = ? "; 1309 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1310 $sql .= " and substr(r_datetime,1,7) = ? "; 1311 } else { 1312 $sql .= " and left(r_datetime, 7) = ? "; 1313 } 1257 1314 array_push($params,substr($ymd,0,7)); 1258 1315 } else { 1259 $sql .= " and left(r_datetime,10) = ? "; 1316 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1317 $sql .= " and substr(r_datetime,1,10) = ? "; 1318 } else { 1319 $sql .= " and left(r_datetime,10) = ? "; 1320 } 1260 1321 array_push($params,$ymd); 1261 1322 } … … 1284 1345 $params = array(intval($ktai_flag)); 1285 1346 if ($month_flag) { 1347 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1348 $sql .= " and substr(r_datetime,1,7) = ? "; 1349 } else { 1286 1350 $sql .= " and left(r_datetime, 7) = ? "; 1287 array_push($params,substr($ymd,0,7)); 1351 } 1352 array_push($params,substr($ymd,0,7)); 1288 1353 } else { 1354 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1355 $sql .= " and substr(r_datetime,1,10) = ? "; 1356 } else { 1289 1357 $sql .= " and left(r_datetime,10) = ? "; 1290 array_push($params,$ymd); 1358 } 1359 array_push($params,$ymd); 1291 1360 } 1292 1361 $sql .= " and target_c_diary_id <> 0 "; … … 1332 1401 $params = array(intval($ktai_flag)); 1333 1402 if ($month_flag) { 1334 $where .= " and left(r_datetime, 7) = ? "; 1403 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1404 $where .= " and substr(r_datetime,1,7) = ? "; 1405 } else { 1406 $where .= " and left(r_datetime, 7) = ? "; 1407 } 1335 1408 array_push($params,substr($ymd,0,7)); 1336 1409 } else { 1337 $where .= " and left(r_datetime,10) = ? "; 1410 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1411 $where .= " and substr(r_datetime,1,10) = ? "; 1412 } else { 1413 $where .= " and left(r_datetime,10) = ? "; 1414 } 1338 1415 array_push($params,$ymd); 1339 1416 } … … 1397 1474 $where =" where ktai_flag = ? "; 1398 1475 $params = array(intval($ktai_flag)); 1399 1400 $where .= " and left(r_datetime, 7) = ? ";1401 array_push($params,substr($ymd,0,7));1476 if ($month_flag) { 1477 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1478 $where .= " and substr(r_datetime,1,7) = ? "; 1402 1479 } else { 1403 $where .= " and left(r_datetime,10) = ? "; 1404 array_push($params,$ymd); 1405 } 1406 if ($page_name!="all") { 1407 $where .= " and page_name = ? "; 1408 array_push($params,$page_name); 1409 } 1480 $where .= " and left(r_datetime, 7) = ? "; 1481 } 1482 array_push($params,substr($ymd,0,7)); 1483 } else { 1484 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1485 $where .= " and substr(r_datetime,1,10) = ? "; 1486 } else { 1487 $where .= " and left(r_datetime,10) = ? "; 1488 } 1489 array_push($params,$ymd); 1490 } 1491 if ($page_name != "all") { 1492 $where .= " and page_name = ? "; 1493 array_push($params,$page_name); 1494 } 1410 1495 $sql = "select target_c_member_id , count(*) as count from c_access_log "; 1411 1496 $sql .= $where; … … 1428 1513 $where =" where ktai_flag = ? "; 1429 1514 $params = array(intval($ktai_flag)); 1430 1431 $where .= " and left(r_datetime, 7) = ? ";1432 array_push($params,substr($ymd,0,7));1515 if ($month_flag) { 1516 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1517 $where .= " and substr(r_datetime,1,7) = ? "; 1433 1518 } else { 1434 $where .= " and left(r_datetime,10) = ? "; 1435 array_push($params,$ymd); 1436 } 1437 if ($page_name != "all") { 1438 $where .= " and page_name = ? "; 1439 array_push($params,$page_name); 1440 } 1519 $where .= " and left(r_datetime, 7) = ? "; 1520 } 1521 array_push($params,substr($ymd,0,7)); 1522 } else { 1523 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1524 $where .= " and substr(r_datetime,1,10) = ? "; 1525 } else { 1526 $where .= " and left(r_datetime,10) = ? "; 1527 } 1528 array_push($params,$ymd); 1529 } 1530 if ($page_name != "all") { 1531 $where .= " and page_name = ? "; 1532 array_push($params,$page_name); 1533 } 1441 1534 $sql = "select count(*) from c_access_log " ; 1442 1535 $sql .= $where; … … 1599 1692 ); 1600 1693 1601 $today = getdate(); 1602 $mmdd = $today['mon'] * 100 + $today['mday']; 1603 $sql = 'SELECT ' . $today['year'] . ' - birth_year' 1604 . ' - (' . $mmdd . ' < (birth_month * 100 + birth_day))' 1605 . ' AS age FROM c_member WHERE birth_year <> 0'; 1694 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1695 $sql = "SELECT ((date_part('year', now()) - birth_year)- " . 1696 "cast(substring(CURRENT_DATE,'.....$')<(to_char(birth_month, '00') || '-' || to_char(birth_day, '00')) as int)) " . 1697 "AS age FROM c_member WHERE birth_year <> 0;"; 1698 } else { 1699 $today = getdate(); 1700 $mmdd = $today['mon'] * 100 + $today['mday']; 1701 $sql = 'SELECT ' . $today['year'] . ' - birth_year' 1702 . ' - (' . $mmdd . ' < (birth_month * 100 + birth_day))' 1703 . ' AS age FROM c_member WHERE birth_year <> 0'; 1704 } 1606 1705 $lst = db_get_all($sql); 1607 1706 … … 1647 1746 function get_analysis_date_month($year = "", $month = "") 1648 1747 { 1649 $sql = "select date_format(r_date,'%Y-%m') from c_member order by r_date"; 1748 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1749 $sql = "select to_char(r_date,'YYYY-MM') from c_member order by r_date"; 1750 } else { 1751 $sql = "select date_format(r_date,'%Y-%m') from c_member order by r_date"; 1752 } 1650 1753 $start_date = db_get_one($sql); 1651 1754 … … 1657 1760 }while($date < date("Y-m")); 1658 1761 1659 $sql = "select date_format(r_date,'%Y-%m') as d from c_member"; 1762 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1763 $sql = "select to_char(r_date,'YYYY-MM') as d from c_member"; 1764 } else { 1765 $sql = "select date_format(r_date,'%Y-%m') as d from c_member"; 1766 } 1660 1767 $lst = db_get_all($sql); 1661 1768 foreach ($lst as $value) { … … 1690 1797 } 1691 1798 1692 1693 $sql = "select date_format(r_date,'%d') as d from c_member where date_format(r_date,'%Y-%m') = ?"; 1799 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1800 $sql = "select to_char(r_date,'DD') as d from c_member where to_char(r_date,'YYYY-MM') = ?"; 1801 } else { 1802 $sql = "select date_format(r_date,'%d') as d from c_member where date_format(r_date,'%Y-%m') = ?"; 1803 } 1694 1804 $params = array($date); 1695 1805 $lst = db_get_all($sql,$params); -
OpenPNE/trunk/webapp_biz/modules/biz/lib/mysql_functions.php
r3591 r3738 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 $sql .= db_order_by_rand(); 339 339 340 340 $params = array( … … 369 369 370 370 //指定された条件に見合うグループのリストを得る関数 371 function biz_getGroupList($keyword = '', $page = 0, $page_size = 20 , $order = 'biz_group_id')371 function biz_getGroupList($keyword = '', $page = 0, $page_size = 20) 372 372 { 373 373 374 374 //keywordあり 375 375 if ($keyword) { 376 $where = ' WHERE true AND (info LIKE ? OR name LIKE ?) ORDER BY ?';376 $where = ' WHERE true AND (info LIKE ? OR name LIKE ?) ORDER BY biz_group_id'; 377 377 $sql = 'SELECT * FROM biz_group'. $where; 378 378 … … 380 380 '%'.$keyword.'%', 381 381 '%'.$keyword.'%', 382 $order,383 382 ); 384 383 $list = db_get_all_page($sql, $page, $page_size, $params); … … 386 385 } else { 387 386 $where = ''; 388 $sql = 'SELECT * FROM biz_group ORDER BY ? desc'; 389 390 $params = array( 391 $order, 392 ); 393 $list = db_get_all_page($sql, $page, $page_size, $params); 387 $sql = 'SELECT * FROM biz_group ORDER BY biz_group_id'; 388 389 $params = array(); 390 $list = db_get_all_page($sql, $page, $page_size); 394 391 } 395 392 … … 401 398 $count = count(biz_getGroupMember($value['biz_group_id'])); 402 399 $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 400 } 414 401 … … 447 434 if ($id) { 448 435 $params[] = intval($id); 449 $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = "?-?-?"AND biz_shisetsu_id = ? ORDER BY begin_time ASC';436 $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = \'?-?-?\' AND biz_shisetsu_id = ? ORDER BY begin_time ASC'; 450 437 } else { 451 $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = "?-?-?"ORDER BY begin_time ASC';438 $sql = 'SELECT * FROM biz_shisetsu_schedule WHERE date = \'?-?-?\' ORDER BY begin_time ASC'; 452 439 } 453 440 $list = db_get_all($sql, $params); … … 710 697 $sql = 'SELECT * FROM biz_todo WHERE biz_todo_id IN ('.$ids.')' . 711 698 ' AND due_datetime > ? AND due_datetime <= ?'; 699 $max_day = date("t", mktime(0,0,0,intval($month),1,intval($year) )); 712 700 $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'701 sprintf('%04d-%02d', intval($year), intval($month)) . '-01 00:00:00', 702 sprintf('%04d-%02d', intval($year), intval($month)) . '-' . $max_day . ' 23:59:59' 715 703 ); 716 704 $list = db_get_all($sql, $params); … … 891 879 { 892 880 $sql = 'UPDATE biz_schedule SET title = ?,c_member_id = ?,begin_date = ?,finish_date = ?,begin_time = ?,finish_time = ?,value = ?,rep_type = ?,rep_first = ?, biz_group_id = ?, public_flag = ?, is_read = 0 WHERE biz_schedule_id = ?'; 881 882 //登録値のセット、チェック 883 if (!$value) { 884 $value = ''; 885 } 886 887 if (!$rep_type) { 888 $rep_type = 0; 889 } 890 893 891 $params = array( 894 892 $title, … … 1115 1113 } 1116 1114 1115 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 1116 if ($due_datetime == '') { 1117 $due_datetime = '0000-01-01'; 1118 } 1119 1120 if ($biz_group_id == '') { 1121 $biz_group_id = 0; 1122 } 1123 } 1124 1117 1125 $data = array( 1118 1126 'c_member_id' => $member_id,
Note: See TracChangeset
for help on using the changeset viewer.