1 | <?php |
---|
2 | /** |
---|
3 | * @copyright 2005-2008 OpenPNE Project |
---|
4 | * @license http://www.php.net/license/3_01.txt PHP License 3.01 |
---|
5 | */ |
---|
6 | |
---|
7 | function db_review_c_review_list4member($c_member_id, $count = 10) |
---|
8 | { |
---|
9 | $sql = 'SELECT rev.c_review_id, rev.title, com.r_datetime' . |
---|
10 | ' FROM c_review_comment AS com LEFT JOIN c_review AS rev USING(c_review_id)' . |
---|
11 | ' WHERE com.c_member_id = ? ORDER BY com.r_datetime DESC'; |
---|
12 | $params = array(intval($c_member_id)); |
---|
13 | return db_get_all_limit($sql, 0, $count, $params); |
---|
14 | } |
---|
15 | |
---|
16 | function db_review_c_friend_review_list4c_member_id($c_member_id, $limit) |
---|
17 | { |
---|
18 | $friends = db_friend_c_member_id_list($c_member_id, true); |
---|
19 | if (!$friends) { |
---|
20 | return array(); |
---|
21 | } |
---|
22 | $ids = implode(',', array_map('intval', $friends)); |
---|
23 | |
---|
24 | $sql = 'SELECT * FROM c_review INNER JOIN c_review_comment USING (c_review_id)' |
---|
25 | . ' WHERE c_review_comment.c_member_id IN (' . $ids . ')' |
---|
26 | . ' ORDER BY c_review_comment.r_datetime DESC'; |
---|
27 | $list = db_get_all_limit($sql, 0, $limit); |
---|
28 | foreach ($list as $key => $value) { |
---|
29 | $list[$key] += db_member_c_member4c_member_id_LIGHT($value['c_member_id']); |
---|
30 | } |
---|
31 | return $list; |
---|
32 | } |
---|
33 | |
---|
34 | function db_review_c_friend_review_list_more4c_member_id($c_member_id, $page, $page_size) |
---|
35 | { |
---|
36 | $friends = db_friend_c_member_id_list($c_member_id, true); |
---|
37 | if (!$friends) { |
---|
38 | return array(); |
---|
39 | } |
---|
40 | $ids = implode(',', array_map('intval', $friends)); |
---|
41 | |
---|
42 | $sql = 'SELECT * FROM c_review INNER JOIN c_review_comment USING (c_review_id)' |
---|
43 | . ' WHERE c_review_comment.c_member_id IN (' . $ids . ')' |
---|
44 | . ' ORDER BY c_review_comment.r_datetime DESC'; |
---|
45 | $list = db_get_all_page($sql, $page, $page_size, $params); |
---|
46 | foreach ($list as $key => $value) { |
---|
47 | $list[$key] += db_member_c_member4c_member_id_LIGHT($value['c_member_id']); |
---|
48 | } |
---|
49 | |
---|
50 | $sql = 'SELECT COUNT(*) FROM c_review_comment WHERE c_member_id IN (' . $ids . ')'; |
---|
51 | $total_num = db_get_one($sql); |
---|
52 | |
---|
53 | if ($total_num != 0) { |
---|
54 | $total_page_num = ceil($total_num / $page_size); |
---|
55 | if ($page >= $total_page_num) { |
---|
56 | $next = false; |
---|
57 | } else { |
---|
58 | $next = true; |
---|
59 | } |
---|
60 | if ($page <= 1) { |
---|
61 | $prev = false; |
---|
62 | } else { |
---|
63 | $prev = true; |
---|
64 | } |
---|
65 | } |
---|
66 | |
---|
67 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
68 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
69 | |
---|
70 | return array($list, $prev, $next, $total_num, $start_num, $end_num); |
---|
71 | } |
---|
72 | |
---|
73 | function db_review_new_commu_review4c_commu_id($c_commu_id , $limit) |
---|
74 | { |
---|
75 | $sql = "SELECT ccr.c_review_id, cr.title, ccr.r_datetime" . |
---|
76 | " FROM c_commu_review AS ccr , c_review AS cr" . |
---|
77 | " WHERE ccr.c_review_id = cr.c_review_id" . |
---|
78 | " AND ccr.c_commu_id = ?". |
---|
79 | " ORDER BY ccr.r_datetime DESC"; |
---|
80 | $params = array(intval($c_commu_id)); |
---|
81 | return db_get_all_limit($sql, 0, $limit, $params); |
---|
82 | } |
---|
83 | |
---|
84 | function do_review_review_add_category_disp() |
---|
85 | { |
---|
86 | $sql = 'SELECT c_review_category_id, category_disp FROM c_review_category'; |
---|
87 | return db_get_assoc($sql); |
---|
88 | } |
---|
89 | |
---|
90 | function do_review_add_search_result($keyword, $category_id, $page) |
---|
91 | { |
---|
92 | $sql = 'SELECT category FROM c_review_category WHERE c_review_category_id = ?'; |
---|
93 | $params = array(intval($category_id)); |
---|
94 | if (!$category = db_get_one($sql, $params)) { |
---|
95 | return null; |
---|
96 | } |
---|
97 | |
---|
98 | require_once 'OpenPNE/Amazon.php'; |
---|
99 | $amazon =& new OpenPNE_Amazon(AMAZON_ACCESS_KEY_ID, AMAZON_SECRET_ACCESS_KEY, AMAZON_AFFID); |
---|
100 | $amazon->setLocale(AMAZON_LOCALE); |
---|
101 | $amazon->setBaseUrl(AMAZON_BASEURL); |
---|
102 | if (OPENPNE_USE_HTTP_PROXY) { |
---|
103 | $amazon->setProxy(OPENPNE_HTTP_PROXY_HOST, OPENPNE_HTTP_PROXY_PORT); |
---|
104 | } |
---|
105 | |
---|
106 | $options = array( |
---|
107 | 'Keywords' => $keyword, |
---|
108 | 'ItemPage' => $page, |
---|
109 | 'ResponseGroup' => 'Large', |
---|
110 | ); |
---|
111 | $products = $amazon->ItemSearch($category, $options); |
---|
112 | |
---|
113 | if (PEAR::isError($products)) { |
---|
114 | return null; |
---|
115 | } |
---|
116 | if (empty($products['Request']['IsValid']) || $products['Request']['IsValid'] !== 'True') { |
---|
117 | return null; |
---|
118 | } |
---|
119 | |
---|
120 | foreach ($products['Item'] as $key => $value) { |
---|
121 | if (is_array($value['ItemAttributes']['Author'])) { |
---|
122 | $authors = array_unique($value['ItemAttributes']['Author']); |
---|
123 | $products['Item'][$key]['author'] = implode(', ', $authors); |
---|
124 | } |
---|
125 | if (is_array($value['ItemAttributes']['Artist'])) { |
---|
126 | $artists = array_unique($value['ItemAttributes']['Artist']); |
---|
127 | $products['Item'][$key]['artist'] = implode(', ', $artists); |
---|
128 | } |
---|
129 | } |
---|
130 | |
---|
131 | $product_page = $products['Request']['ItemSearchRequest']['ItemPage']; |
---|
132 | $product_pages = $products['TotalPages']; |
---|
133 | $total_num = $products['TotalResults']; |
---|
134 | |
---|
135 | return array($products['Item'], $product_page, $product_pages, $total_num); |
---|
136 | } |
---|
137 | |
---|
138 | function db_review_write_product4asin($asin) |
---|
139 | { |
---|
140 | require_once 'OpenPNE/Amazon.php'; |
---|
141 | $amazon =& new OpenPNE_Amazon(AMAZON_TOKEN, AMAZON_AFFID); |
---|
142 | $amazon->setLocale(AMAZON_LOCALE); |
---|
143 | $amazon->setBaseUrl(AMAZON_BASEURL); |
---|
144 | if (OPENPNE_USE_HTTP_PROXY) { |
---|
145 | $amazon->setProxy(OPENPNE_HTTP_PROXY_HOST, OPENPNE_HTTP_PROXY_PORT); |
---|
146 | } |
---|
147 | $keyword = mb_convert_encoding($keyword, "UTF-8", "auto"); |
---|
148 | |
---|
149 | $options = array(); |
---|
150 | $options['ResponseGroup'] = 'Large'; |
---|
151 | $result = $amazon->ItemLookup($asin, $options); |
---|
152 | if (PEAR::isError($result)) { |
---|
153 | return false; |
---|
154 | } |
---|
155 | if (empty($result['Request']['IsValid']) || $result['Request']['IsValid'] !== 'True') { |
---|
156 | return null; |
---|
157 | } |
---|
158 | |
---|
159 | $product = $result['Item'][0]; |
---|
160 | if (is_array($product['ItemAttributes']['Author'])) { |
---|
161 | $authors = array_unique($product['ItemAttributes']['Author']); |
---|
162 | $product['author'] = implode(', ', $authors); |
---|
163 | } |
---|
164 | if (is_array($product['ItemAttributes']['Artist'])) { |
---|
165 | $artists = array_unique($product['ItemAttributes']['Artist']); |
---|
166 | $product['artist'] = implode(', ', $artists); |
---|
167 | } |
---|
168 | |
---|
169 | return $product; |
---|
170 | } |
---|
171 | |
---|
172 | /** |
---|
173 | * orderby: |
---|
174 | * r_datetime => 作成順 |
---|
175 | * r_num => 登録数順 |
---|
176 | */ |
---|
177 | function db_review_search_result4keyword_category($keyword, $category_id , $orderby, $page = 1, $page_size = 30) |
---|
178 | { |
---|
179 | $from = " FROM c_review INNER JOIN c_review_comment USING (c_review_id)"; |
---|
180 | |
---|
181 | $wheres = array(); |
---|
182 | $params = array(); |
---|
183 | if ($keyword) { |
---|
184 | $wheres[] = 'c_review.title LIKE ?'; |
---|
185 | $params[] = '%'.check_search_word($keyword).'%'; |
---|
186 | } |
---|
187 | if ($category_id) { |
---|
188 | $wheres[] = 'c_review.c_review_category_id = ?'; |
---|
189 | $params[] = intval($category_id); |
---|
190 | } |
---|
191 | if ($wheres) { |
---|
192 | $where = ' WHERE ' . implode(' AND ', $wheres); |
---|
193 | } else { |
---|
194 | $where = ''; |
---|
195 | } |
---|
196 | |
---|
197 | switch ($orderby) { |
---|
198 | case "r_datetime": |
---|
199 | default: |
---|
200 | $order = " ORDER BY r_datetime2 DESC"; |
---|
201 | break; |
---|
202 | case "r_num": |
---|
203 | $order = " ORDER BY write_num DESC, r_datetime2 DESC"; |
---|
204 | break; |
---|
205 | } |
---|
206 | |
---|
207 | if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { |
---|
208 | $group = " GROUP BY c_review.c_review_id" . |
---|
209 | ", c_review.title" . |
---|
210 | ", c_review.release_date" . |
---|
211 | ", c_review.manufacturer" . |
---|
212 | ", c_review.author" . |
---|
213 | ", c_review.c_review_category_id" . |
---|
214 | ", c_review.image_small" . |
---|
215 | ", c_review.image_medium" . |
---|
216 | ", c_review.image_large" . |
---|
217 | ", c_review.url" . |
---|
218 | ", c_review.asin" . |
---|
219 | ", c_review.list_price" . |
---|
220 | ", c_review.retail_price" . |
---|
221 | ", c_review.r_datetime"; |
---|
222 | } else { |
---|
223 | $group = " GROUP BY c_review.c_review_id"; |
---|
224 | } |
---|
225 | $sql = "SELECT" . |
---|
226 | " c_review.*" . |
---|
227 | ", MAX(c_review_comment.r_datetime) as r_datetime2" . |
---|
228 | ", COUNT(c_review_comment.c_review_comment_id) AS write_num" . |
---|
229 | $from . |
---|
230 | $where . |
---|
231 | $group . |
---|
232 | $order; |
---|
233 | |
---|
234 | $lst = db_get_all_page($sql, $page, $page_size, $params); |
---|
235 | |
---|
236 | //$lstに書き込み数を追加 + レビュー書き込み内容と日付を追加 |
---|
237 | foreach ($lst as $key => $value) { |
---|
238 | $sql = "SELECT body FROM c_review_comment" . |
---|
239 | " WHERE c_review_id = ?" . |
---|
240 | " ORDER BY r_datetime DESC"; |
---|
241 | $p2 = array(intval($value['c_review_id'])); |
---|
242 | $lst[$key]['body'] = db_get_one($sql, $p2); |
---|
243 | } |
---|
244 | |
---|
245 | $sql = "SELECT COUNT(DISTINCT c_review.c_review_id)" . $from . $where; |
---|
246 | $total_num = db_get_one($sql, $params); |
---|
247 | |
---|
248 | if ($total_num != 0) { |
---|
249 | $total_page_num = ceil($total_num / $page_size); |
---|
250 | if ($page >= $total_page_num) { |
---|
251 | $next = false; |
---|
252 | } else { |
---|
253 | $next = true; |
---|
254 | } |
---|
255 | if ($page <= 1) { |
---|
256 | $prev = false; |
---|
257 | } else { |
---|
258 | $prev = true; |
---|
259 | } |
---|
260 | } |
---|
261 | |
---|
262 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
263 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
264 | |
---|
265 | return array($lst, $prev, $next, $total_num, $start_num, $end_num); |
---|
266 | } |
---|
267 | |
---|
268 | function db_review_list_product_c_review4c_review_id($c_review_id) |
---|
269 | { |
---|
270 | $sql = 'SELECT * FROM c_review AS cr, c_review_category AS crc' . |
---|
271 | ' WHERE cr.c_review_category_id = crc.c_review_category_id' . |
---|
272 | ' AND c_review_id = ?'; |
---|
273 | $params = array(intval($c_review_id)); |
---|
274 | return db_get_row($sql, $params); |
---|
275 | } |
---|
276 | |
---|
277 | function db_review_list_product_c_review_list4c_review_id($c_review_id, $page, $page_size=30) |
---|
278 | { |
---|
279 | $sql = "SELECT crc.*, cm.c_member_id, cm.nickname, cm.image_filename" . |
---|
280 | " FROM c_review_comment AS crc, c_member AS cm" . |
---|
281 | " WHERE crc.c_member_id = cm.c_member_id" . |
---|
282 | " AND c_review_id = ?" . |
---|
283 | " ORDER BY r_datetime desc"; |
---|
284 | $params = array(intval($c_review_id)); |
---|
285 | $list = db_get_all_page($sql, $page, $page_size, $params); |
---|
286 | |
---|
287 | $total_num = db_review_count_c_review_comment4c_review_id($c_review_id); |
---|
288 | if ($total_num != 0) { |
---|
289 | $total_page_num = ceil($total_num / $page_size); |
---|
290 | if ($page >= $total_page_num) { |
---|
291 | $next = false; |
---|
292 | } else { |
---|
293 | $next = true; |
---|
294 | } |
---|
295 | if ($page <= 1) { |
---|
296 | $prev = false; |
---|
297 | } else { |
---|
298 | $prev = true; |
---|
299 | } |
---|
300 | } |
---|
301 | |
---|
302 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
303 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
304 | |
---|
305 | return array($list, $prev, $next, $total_num, $start_num, $end_num); |
---|
306 | } |
---|
307 | |
---|
308 | function db_review_list_product_c_review_list4c_member_id($c_member_id, $page, $page_size=30) |
---|
309 | { |
---|
310 | $sql = "SELECT cr.*, crc.*, crc2.category_disp" . |
---|
311 | " FROM c_review_comment AS crc, c_review AS cr, c_review_category AS crc2" . |
---|
312 | " WHERE crc.c_review_id = cr.c_review_id" . |
---|
313 | " AND cr.c_review_category_id = crc2.c_review_category_id" . |
---|
314 | " AND crc.c_member_id = ?" . |
---|
315 | " ORDER BY crc.r_datetime DESC"; |
---|
316 | $params = array(intval($c_member_id)); |
---|
317 | $list = db_get_all_page($sql, $page, $page_size, $params); |
---|
318 | |
---|
319 | foreach ($list as $key => $value) { |
---|
320 | $list[$key]['write_num'] = db_review_count_c_review_comment4c_review_id($value['c_review_id']); |
---|
321 | } |
---|
322 | |
---|
323 | $sql = "SELECT COUNT(*) FROM c_review_comment WHERE c_member_id = ?"; |
---|
324 | $params = array(intval($c_member_id)); |
---|
325 | $total_num = db_get_one($sql, $params); |
---|
326 | |
---|
327 | if ($total_num != 0) { |
---|
328 | $total_page_num = ceil($total_num / $page_size); |
---|
329 | if ($page >= $total_page_num) { |
---|
330 | $next = false; |
---|
331 | } else { |
---|
332 | $next = true; |
---|
333 | } |
---|
334 | if ($page <= 1) { |
---|
335 | $prev = false; |
---|
336 | } else { |
---|
337 | $prev = true; |
---|
338 | } |
---|
339 | } |
---|
340 | |
---|
341 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
342 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
343 | |
---|
344 | return array($list, $prev, $next, $total_num, $start_num, $end_num); |
---|
345 | } |
---|
346 | |
---|
347 | function db_review_add_write_c_review_comment4asin_c_member_id($asin, $c_member_id) |
---|
348 | { |
---|
349 | $sql = "SELECT * FROM c_review AS cr, c_review_comment AS crc" . |
---|
350 | " WHERE cr.c_review_id = crc.c_review_id" . |
---|
351 | " AND cr.asin = ?" . |
---|
352 | " AND crc.c_member_id = ?"; |
---|
353 | $params = array($asin, intval($c_member_id)); |
---|
354 | return db_get_row($sql, $params); |
---|
355 | } |
---|
356 | |
---|
357 | function db_review_clip_list_h_review_clip_list4c_member_id($c_member_id, $page, $page_size=30) |
---|
358 | { |
---|
359 | $sql = "SELECT * FROM c_review AS cr, c_review_clip AS crc" . |
---|
360 | " WHERE crc.c_review_id = cr.c_review_id" . |
---|
361 | " AND c_member_id = ?" . |
---|
362 | " ORDER BY crc.r_datetime"; |
---|
363 | $params = array(intval($c_member_id)); |
---|
364 | $list = db_get_all_page($sql, $page, $page_size, $params); |
---|
365 | |
---|
366 | //カテゴリの表示名を取得 |
---|
367 | $category_disp = do_review_review_add_category_disp(); |
---|
368 | |
---|
369 | //$lstに書き込み数 + カテゴリ名 を追加 |
---|
370 | foreach ($list as $key => $value) { |
---|
371 | $list[$key]['write_num'] = db_review_count_c_review_comment4c_review_id($value['c_review_id']); |
---|
372 | $list[$key]['category_disp'] = $category_disp[$value['c_review_category_id']]; |
---|
373 | } |
---|
374 | |
---|
375 | $sql = "SELECT COUNT(*) FROM c_review_clip WHERE c_member_id = ?"; |
---|
376 | $params = array(intval($c_member_id)); |
---|
377 | $total_num = db_get_one($sql, $params); |
---|
378 | |
---|
379 | if ($total_num != 0) { |
---|
380 | $total_page_num = ceil($total_num / $page_size); |
---|
381 | if ($page >= $total_page_num) { |
---|
382 | $next = false; |
---|
383 | } else { |
---|
384 | $next = true; |
---|
385 | } |
---|
386 | if ($page <= 1) { |
---|
387 | $prev = false; |
---|
388 | } else { |
---|
389 | $prev = true; |
---|
390 | } |
---|
391 | } |
---|
392 | |
---|
393 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
394 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
395 | |
---|
396 | return array($list, $prev, $next, $total_num, $start_num, $end_num); |
---|
397 | } |
---|
398 | |
---|
399 | function db_review_c_member_review_c_member_review4c_commu_id($c_commu_id, $page, $page_size=20) |
---|
400 | { |
---|
401 | $sql = " SELECT cr.*, ccr.*, crc.category_disp " . |
---|
402 | " FROM c_commu_review as ccr, c_review as cr, c_review_category as crc" . |
---|
403 | " WHERE ccr.c_review_id = cr.c_review_id" . |
---|
404 | " AND cr.c_review_category_id = crc.c_review_category_id" . |
---|
405 | " AND ccr.c_commu_id = ?" . |
---|
406 | " ORDER BY ccr.r_datetime"; |
---|
407 | $params = array(intval($c_commu_id)); |
---|
408 | $list = db_get_all_page($sql, $page, $page_size, $params); |
---|
409 | |
---|
410 | $sql = "SELECT COUNT(*) FROM c_commu_review WHERE c_commu_id = ?"; |
---|
411 | $total_num = db_get_one($sql, $params); |
---|
412 | |
---|
413 | if ($total_num != 0) { |
---|
414 | $total_page_num = ceil($total_num / $page_size); |
---|
415 | if ($page >= $total_page_num) { |
---|
416 | $next = false; |
---|
417 | } else { |
---|
418 | $next = true; |
---|
419 | } |
---|
420 | if ($page <= 1) { |
---|
421 | $prev = false; |
---|
422 | } else { |
---|
423 | $prev = true; |
---|
424 | } |
---|
425 | } |
---|
426 | |
---|
427 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
428 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
429 | |
---|
430 | return array($list, $prev, $next, $total_num, $start_num, $end_num); |
---|
431 | } |
---|
432 | |
---|
433 | function db_review_c_member_review_add_confirm_c_member_review4c_review_id($c_review_id, $c_member_id) |
---|
434 | { |
---|
435 | if (!$c_review_id) { |
---|
436 | return array(); |
---|
437 | } |
---|
438 | $c_review_id_str = implode(',', array_map('intval', $c_review_id)); |
---|
439 | $sql = "SELECT * FROM c_review as cr, c_review_comment as crc , c_review_category as crc2 " . |
---|
440 | " WHERE cr.c_review_id = crc.c_review_id " . |
---|
441 | " AND cr.c_review_category_id = crc2.c_review_category_id " . |
---|
442 | " AND crc.c_member_id = ?". |
---|
443 | " AND cr.c_review_id IN (".$c_review_id_str.")"; |
---|
444 | $params = array(intval($c_member_id)); |
---|
445 | return db_get_all($sql, $params); |
---|
446 | } |
---|
447 | |
---|
448 | function db_review_c_review_add_c_review_category_id4category($category) |
---|
449 | { |
---|
450 | $sql = "SELECT c_review_category_id FROM c_review_category WHERE category = ?"; |
---|
451 | $params = array($category); |
---|
452 | return db_get_one($sql, $params); |
---|
453 | } |
---|
454 | |
---|
455 | function db_review_edit_c_review_comment4c_review_comment_id_c_member_id($c_review_comment_id, $c_member_id) |
---|
456 | { |
---|
457 | $sql = "SELECT * FROM c_review_comment " . |
---|
458 | " WHERE c_review_comment_id = ?" . |
---|
459 | " AND c_member_id = ?"; |
---|
460 | $params = array(intval($c_review_comment_id), intval($c_member_id)); |
---|
461 | return db_get_row($sql, $params); |
---|
462 | } |
---|
463 | |
---|
464 | function db_review_edit_c_review_comment4c_review_comment_id($c_review_comment_id) |
---|
465 | { |
---|
466 | $sql = 'SELECT * FROM c_review_comment WHERE c_review_comment_id = ?'; |
---|
467 | $params = array(intval($c_review_comment_id)); |
---|
468 | return db_get_row($sql, $params); |
---|
469 | } |
---|
470 | |
---|
471 | function db_review_clip_add_c_review_id4c_review_id_c_member_id($c_review_id, $c_member_id) |
---|
472 | { |
---|
473 | $sql = "SELECT c_review_clip_id FROM c_review_clip" . |
---|
474 | " WHERE c_review_id = ?" . |
---|
475 | " AND c_member_id = ?"; |
---|
476 | $params = array(intval($c_review_id), intval($c_member_id)); |
---|
477 | return db_get_one($sql, $params); |
---|
478 | } |
---|
479 | |
---|
480 | function db_review_c_member_review_c_review_id4c_review_id_c_member_id($c_review_id, $c_member_id, $c_commu_id) |
---|
481 | { |
---|
482 | $sql = "SELECT c_commu_review_id FROM c_commu_review" . |
---|
483 | " WHERE c_commu_id = ?" . |
---|
484 | " AND c_review_id = ?" . |
---|
485 | " AND c_member_id = ?"; |
---|
486 | $params = array(intval($c_commu_id), intval($c_review_id), intval($c_member_id)); |
---|
487 | return db_get_one($sql, $params); |
---|
488 | } |
---|
489 | |
---|
490 | function db_review_edit_c_review4c_review_comment_id($c_review_comment_id) |
---|
491 | { |
---|
492 | $sql = "SELECT c_review.* FROM c_review, c_review_comment" . |
---|
493 | " WHERE c_review_comment.c_review_comment_id=?". |
---|
494 | " AND c_review_comment.c_review_id=c_review.c_review_id"; |
---|
495 | $params = array(intval($c_review_comment_id)); |
---|
496 | return db_get_row($sql, $params); |
---|
497 | } |
---|
498 | |
---|
499 | function db_review_common_c_review_id4c_review_comment_id($c_review_comment_id) |
---|
500 | { |
---|
501 | $sql = 'SELECT c_review_id FROM c_review_comment WHERE c_review_comment_id = ?'; |
---|
502 | $params = array(intval($c_review_comment_id)); |
---|
503 | return db_get_one($sql, $params); |
---|
504 | } |
---|
505 | |
---|
506 | function db_review_count_c_review_comment4c_review_id($c_review_id) |
---|
507 | { |
---|
508 | $sql = 'SELECT COUNT(*) FROM c_review_comment WHERE c_review_id = ?'; |
---|
509 | $params = array(intval($c_review_id)); |
---|
510 | return db_get_one($sql, $params); |
---|
511 | } |
---|
512 | |
---|
513 | |
---|
514 | /** |
---|
515 | * @copyright 2005-2008 OpenPNE Project |
---|
516 | * @license http://www.php.net/license/3_01.txt PHP License 3.01 |
---|
517 | */ |
---|
518 | |
---|
519 | function do_c_review_add_insert_c_review($product, $c_review_category_id) |
---|
520 | { |
---|
521 | $sql = 'SELECT c_review_id FROM c_review WHERE asin = ?'; |
---|
522 | $params = array($product['ASIN']); |
---|
523 | if ($c_review_id = db_get_one($sql, $params)) { |
---|
524 | return $c_review_id; |
---|
525 | } |
---|
526 | |
---|
527 | $data = array( |
---|
528 | 'title' => $product['ItemAttributes']['Title'], |
---|
529 | 'release_date' => $product['ItemAttributes']['PublicationDate'], |
---|
530 | 'manufacturer' => $product['ItemAttributes']['Manufacturer'], |
---|
531 | 'author' => $product['author'], |
---|
532 | 'c_review_category_id' => intval($c_review_category_id), |
---|
533 | 'image_small' => $product['SmallImage']['URL'], |
---|
534 | 'image_medium' => $product['MediumImage']['URL'], |
---|
535 | 'image_large' => $product['LargeImage']['URL'], |
---|
536 | 'url' => $product['DetailPageURL'], |
---|
537 | 'asin' => $product['ASIN'], |
---|
538 | 'list_price' => $product['ListPrice']['FormattedPrice'], |
---|
539 | 'retail_price' => $product['OfferSummary']['LowestUsedPrice']['FormattedPrice'], |
---|
540 | 'r_datetime' => db_now(), |
---|
541 | ); |
---|
542 | |
---|
543 | //TODO:暫定処理 |
---|
544 | foreach ($data as $key => $value) { |
---|
545 | if (is_null($value)) { |
---|
546 | $data[$key] = ''; |
---|
547 | } |
---|
548 | } |
---|
549 | return db_insert('c_review', $data); |
---|
550 | } |
---|
551 | |
---|
552 | function do_c_review_add_insert_c_review_comment($c_review_id , $c_member_id, $body, $satisfaction_level) |
---|
553 | { |
---|
554 | $data = array( |
---|
555 | 'c_review_id' => intval($c_review_id), |
---|
556 | 'c_member_id' => intval($c_member_id), |
---|
557 | 'body' => $body, |
---|
558 | 'satisfaction_level' => intval($satisfaction_level), |
---|
559 | 'r_datetime' => db_now(), |
---|
560 | 'u_datetime' => db_now(), |
---|
561 | ); |
---|
562 | return db_insert('c_review_comment', $data); |
---|
563 | } |
---|
564 | |
---|
565 | function do_h_review_edit_update_c_review_comment($c_review_comment_id, $body, $satisfaction_level) |
---|
566 | { |
---|
567 | $data = array( |
---|
568 | 'body' => $body, |
---|
569 | 'satisfaction_level' => intval($satisfaction_level), |
---|
570 | 'u_datetime' => db_now(), |
---|
571 | ); |
---|
572 | $where = array('c_review_comment_id' => intval($c_review_comment_id)); |
---|
573 | return db_update('c_review_comment', $data, $where); |
---|
574 | } |
---|
575 | |
---|
576 | function do_h_review_edit_delete_c_review_comment($c_review_comment_id) |
---|
577 | { |
---|
578 | $sql = 'DELETE FROM c_review_comment WHERE c_review_comment_id = ?'; |
---|
579 | $params = array(intval($c_review_comment_id)); |
---|
580 | db_query($sql, $params); |
---|
581 | } |
---|
582 | |
---|
583 | function do_h_review_clip_add_insert_c_review_clip($c_review_id, $c_member_id) |
---|
584 | { |
---|
585 | $data = array( |
---|
586 | 'c_member_id' => intval($c_member_id), |
---|
587 | 'c_review_id' => intval($c_review_id), |
---|
588 | 'r_datetime' => db_now(), |
---|
589 | ); |
---|
590 | return db_insert('c_review_clip', $data); |
---|
591 | } |
---|
592 | |
---|
593 | function do_c_member_review_insert_c_commu_review($c_review_id, $c_member_id, $c_commu_id) |
---|
594 | { |
---|
595 | $data = array( |
---|
596 | 'c_commu_id' => intval($c_commu_id), |
---|
597 | 'c_review_id' => intval($c_review_id), |
---|
598 | 'c_member_id' => intval($c_member_id), |
---|
599 | 'r_datetime' => db_now(), |
---|
600 | ); |
---|
601 | return db_insert('c_commu_review', $data); |
---|
602 | } |
---|
603 | |
---|
604 | function do_h_review_clip_list_delete_c_review_clip($c_member_id , $c_review_clips) |
---|
605 | { |
---|
606 | if (!is_array($c_review_clips)) { |
---|
607 | return false; |
---|
608 | } |
---|
609 | $ids = implode(',', array_map('intval', $c_review_clips)); |
---|
610 | |
---|
611 | $sql = 'DELETE FROM c_review_clip WHERE c_member_id = ?' . |
---|
612 | ' AND c_review_id IN ('.$ids.')'; |
---|
613 | $params = array(intval($c_member_id)); |
---|
614 | return db_query($sql, $params); |
---|
615 | } |
---|
616 | |
---|
617 | function do_delete_c_review4c_review_id($c_review_id) |
---|
618 | { |
---|
619 | $params = array(intval($c_review_id)); |
---|
620 | |
---|
621 | // c_review |
---|
622 | $sql = 'DELETE FROM c_review WHERE c_review_id = ?'; |
---|
623 | db_query($sql, $params); |
---|
624 | |
---|
625 | // c_review_clip |
---|
626 | $sql = 'DELETE FROM c_review_clip WHERE c_review_id = ?'; |
---|
627 | db_query($sql, $params); |
---|
628 | |
---|
629 | // c_commu_review |
---|
630 | $sql = 'DELETE FROM c_commu_review WHERE c_review_id = ?'; |
---|
631 | db_query($sql, $params); |
---|
632 | } |
---|
633 | |
---|
634 | //コミュニティのおすすめレビューを削除 |
---|
635 | function db_review_delete_c_review4c_review_id($c_review_id) |
---|
636 | { |
---|
637 | $sql = 'DELETE FROM c_review WHERE c_review_id = ?'; |
---|
638 | $params = array(intval($c_review_id)); |
---|
639 | db_query($sql, $params); |
---|
640 | } |
---|
641 | |
---|
642 | //コミュニティのおすすめレビューを削除 |
---|
643 | function db_review_delete_c_commu_review4c_commu_review_id($c_commu_review_id) |
---|
644 | { |
---|
645 | $sql = 'DELETE FROM c_commu_review WHERE c_commu_review_id = ?'; |
---|
646 | $params = array(intval($c_commu_review_id)); |
---|
647 | db_query($sql, $params); |
---|
648 | } |
---|
649 | |
---|
650 | //コミュニティのおすすめレビューを取得(一つ) |
---|
651 | function db_review_get_c_commu_review_one4c_commu_review_id($c_commu_review_id) |
---|
652 | { |
---|
653 | $sql = 'SELECT * FROM c_commu_review WHERE c_commu_review_id = ?'; |
---|
654 | $params = array(intval($c_commu_review_id)); |
---|
655 | return db_get_row($sql, $params); |
---|
656 | } |
---|
657 | |
---|
658 | function db_review_c_review_id4asin($asin) |
---|
659 | { |
---|
660 | $sql = 'SELECT c_review_id FROM c_review' |
---|
661 | . ' WHERE asin = ?'; |
---|
662 | return db_get_one($sql, array($asin)); |
---|
663 | } |
---|
664 | |
---|
665 | function db_review_update_old_c_review($c_review_id) |
---|
666 | { |
---|
667 | $timestamp = date('Y-m-d H:i:s', strtotime('-1 month')); |
---|
668 | $sql = 'SELECT * FROM c_review' |
---|
669 | . ' WHERE c_review_id = ?' |
---|
670 | . ' AND r_datetime < ?'; |
---|
671 | $params = array(intval($c_review_id), $timestamp); |
---|
672 | $c_review = db_get_row($sql, $params); |
---|
673 | |
---|
674 | if ($c_review) { |
---|
675 | $asin = $c_review['asin']; |
---|
676 | $product = db_review_write_product4asin($asin); |
---|
677 | if ($product) { |
---|
678 | $data = array( |
---|
679 | 'title' => $product['ItemAttributes']['Title'], |
---|
680 | 'release_date' => $product['ItemAttributes']['PublicationDate'], |
---|
681 | 'manufacturer' => $product['ItemAttributes']['Manufacturer'], |
---|
682 | 'author' => $product['author'], |
---|
683 | 'image_small' => $product['SmallImage']['URL'], |
---|
684 | 'image_medium' => $product['MediumImage']['URL'], |
---|
685 | 'image_large' => $product['LargeImage']['URL'], |
---|
686 | 'url' => $product['DetailPageURL'], |
---|
687 | 'asin' => $product['ASIN'], |
---|
688 | 'list_price' => $product['ListPrice']['FormattedPrice'], |
---|
689 | 'retail_price' => $product['OfferSummary']['LowestUsedPrice']['FormattedPrice'], |
---|
690 | 'r_datetime' => db_now(), |
---|
691 | ); |
---|
692 | |
---|
693 | foreach ($data as $key => $value) { |
---|
694 | if (is_null($value)) { |
---|
695 | $data[$key] = ''; |
---|
696 | } |
---|
697 | } |
---|
698 | $where = array('c_review_id' => intval($c_review_id)); |
---|
699 | db_update('c_review', $data, $where); |
---|
700 | } |
---|
701 | } |
---|
702 | } |
---|
703 | ?> |
---|