PageRenderTime 45ms CodeModel.GetById 10ms app.highlight 28ms RepoModel.GetById 1ms app.codeStats 0ms

/webapp/lib/db/album.php

https://github.com/openpne/OpenPNE2
PHP | 748 lines | 472 code | 118 blank | 158 comment | 60 complexity | 0302ccddb439617c47d3f0087c407c39 MD5 | raw file
  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/**
  8 * db_album_public_flag_condition
  9 *
 10 * @param int $c_member_id target c_member_id
 11 * @param int $u viewer's c_member_id
 12 * @param string $force
 13 * @return string
 14 */
 15function db_album_public_flag_condition($c_member_id, $u = null, $force = null)
 16{
 17    $pf_cond = '';
 18    if ($force) {
 19        switch ($force) {
 20        case 'friend':
 21            $pf_cond = " AND c_album.public_flag <> 'private'";
 22            break;
 23        case 'private':
 24            $pf_cond = " AND c_album.public_flag = 'public'";
 25            break;
 26        }
 27    } else {
 28        if (!is_null($u) && $c_member_id != $u) {
 29            $is_friend = db_friend_is_friend($c_member_id, $u);
 30            if ($is_friend) {
 31                $pf_cond = " AND c_album.public_flag <> 'private'";
 32            } else {
 33                $pf_cond = " AND c_album.public_flag = 'public'";
 34            }
 35        }
 36    }
 37    return $pf_cond;
 38}
 39
 40/**
 41 * アルバム情報を取得
 42 * @param int $c_album_id
 43 */
 44function db_album_get_c_album4c_album_id($c_album_id)
 45{
 46    $sql = 'SELECT * FROM c_album WHERE c_album_id = ?';
 47    $params = array(intval($c_album_id));
 48
 49    $album = db_get_row($sql, $params);
 50
 51    return $album;
 52}
 53
 54/**
 55 * あるメンバーのアルバム一覧を取得
 56 *
 57 * @param int $c_member_id target_member_id
 58 * @param int $count
 59 * @param int $u viewer's member_id
 60 * @param string $force
 61 */
 62function db_album_get_c_album_subject_list4c_member_id($c_member_id, $count= 10, $u = null, $force = null)
 63{
 64    $pf_condition = db_album_public_flag_condition($c_member_id, $u, $force);
 65    $sql = 'SELECT * FROM c_album WHERE c_member_id = ? '
 66         . $pf_condition
 67         . ' ORDER BY u_datetime DESC';
 68
 69    $params = array(intval($c_member_id));
 70    return db_get_all_limit($sql, 0, $count, $params);
 71}
 72
 73/**
 74 * アルバム写真を取得
 75 * @param int $c_album_image_id
 76 */
 77function db_album_image_get_c_album_image4id($c_album_image_id)
 78{
 79    $sql = 'SELECT * FROM c_album_image WHERE c_album_image_id = ?';
 80    $params = array(intval($c_album_image_id));
 81
 82    return db_get_row($sql,$params);
 83}
 84
 85/**
 86 * c_album_idをキーとしてc_albumデータが存在するか判定
 87 * @param int $c_album_id
 88 * @return bool true,false
 89 */
 90function p_common_is_active_c_album_id($c_album_id)
 91{
 92    $sql = 'SELECT c_album_id FROM c_album WHERE c_album_id = ?';
 93    return (bool)db_get_one($sql, array(intval($c_album_id)));
 94}
 95
 96/**
 97 * c_album_image_idをキーとしてc_album_imageデータが存在するか判定
 98 * @param int $c_album_image_id
 99 * @return bool true,false
100 */
101function p_common_is_active_c_album_image_id($c_album_image_id)
102{
103    $sql = 'SELECT c_album_image_id FROM c_album_image WHERE c_album_image_id = ?';
104    $params = array(intval($c_album_image_id));
105
106    return (bool)db_get_one($sql,$params);
107}
108
109/**
110 * アルバムIDからアルバムに登録された写真を取得
111 *
112 * @param   int   $c_album_id
113 * @param   int   $page
114 * @param   int   $page_size
115 * @param   bool  $desc 並び順を投稿日時の降順にするかどうか(0 or 1) 
116 * @return  array
117 */
118function db_album_c_album_image_list4c_album_id($c_album_id, $page = 1, $page_size = 10, $desc = 0)
119{
120    $sql = 'SELECT * FROM c_album_image WHERE c_album_id = ? ORDER BY c_album_image_id';
121    if ($desc) {
122        $sql .= ' DESC';
123    }
124
125    $params = array(intval($c_album_id));
126    $list = db_get_all_page($sql, $page, $page_size, $params);
127
128    $sql = 'SELECT COUNT(c_album_image_id) FROM c_album_image WHERE c_album_id = ?';
129    $params = array(intval($c_album_id));
130    $total_num = db_get_one($sql, $params);
131
132    if ($total_num != 0) {
133        $total_page_num =  ceil($total_num / $page_size);
134        if ($page >= $total_page_num) {
135            $next = false;
136        } else {
137            $next = true;
138        }
139
140        if ($page <= 1) {
141            $prev = false;
142        } else {
143            $prev = true;
144        }
145    }
146    return array($list, $prev, $next, $total_num);
147}
148
149/**
150 * 指定したアルバム写真の前の写真IDを取得する
151 *
152 * @param int $c_album_id
153 * @param int $c_album_image_id
154 */
155function db_album_image_c_album_image_id_prev4c_album_id($c_album_id, $c_album_image_id)
156{
157    $sql = 'SELECT c_album_image_id FROM c_album_image WHERE c_album_id = ? AND c_album_image_id < ? ORDER BY c_album_image_id DESC';
158    $params = array(intval($c_album_id), intval($c_album_image_id));
159    return db_get_one($sql, $params);
160}
161
162/**
163 * 指定したアルバム写真の次の写真IDを取得する
164 *
165 * @param int $c_album_id
166 * @param int $c_album_image_id
167 */
168function db_album_image_c_album_image_id_next4c_album_id($c_album_id, $c_album_image_id)
169{
170    $sql = 'SELECT c_album_image_id FROM c_album_image WHERE c_album_id = ? AND c_album_image_id > ? ORDER BY c_album_image_id';
171    $params = array(intval($c_album_id), intval($c_album_image_id));
172    return db_get_one($sql, $params);
173}
174
175/**
176 * c_albumの閲覧権限チェック
177 * @param int $c_album_id
178 * @param int $c_member_id
179 */
180function pne_check_album_public_flag($c_album_id, $c_member_id)
181{
182    $c_album = db_album_get_c_album4c_album_id($c_album_id);
183    if ($c_album['c_member_id'] == $c_member_id) {
184        return true;
185    }
186
187    switch ($c_album['public_flag']) {
188    case 'public':
189        $allowed = true;
190        break;
191    case 'friend':
192        $allowed = db_friend_is_friend($c_album['c_member_id'], $c_member_id);
193        break;
194    case 'private':
195    default:
196        $allowed = false;
197        break;
198    }
199
200    return $allowed;
201}
202
203/**
204 * 特定ユーザーのアルバムリスト取得
205 *
206 * @param int $c_member_id target_c_member_id
207 * @param int $page_size
208 * @param int $page
209 * @param int $u viewer's c_member_id
210 */
211function p_fh_album_list_fh_my_album_list4c_member_id($c_member_id, $page_size, $page, $u = null)
212{
213    $pf_cond = db_album_public_flag_condition($c_member_id, $u);
214    $sql = 'SELECT * FROM c_album WHERE c_member_id = ?'. $pf_cond.
215           ' ORDER BY r_datetime DESC';
216    $params = array(intval($c_member_id));
217    $list = db_get_all_page($sql,$page, $page_size,$params);
218
219    $sql = 'SELECT COUNT(c_album_id) FROM c_album WHERE c_member_id = ?' . $pf_cond;
220    $total_num = db_get_one($sql, $params);
221
222    if ($total_num != 0) {
223        $total_page_num =  ceil($total_num / $page_size);
224        if ($page >= $total_page_num) {
225            $next = false;
226        } else {
227            $next = true;
228        }
229
230        if ($page <= 1) {
231            $prev = false;
232        } else {
233            $prev = true;
234        }
235    }
236
237    return array($list, $prev, $next, $total_num);
238}
239
240/**
241 * アルバム検索
242 * 検索ポイントはアルバムタイトル・アルバム説明
243 * 空白(全角半角問わない)でand検索可
244 */
245function p_h_album_list_all_search_c_album4c_album($keyword, $page_size, $page, $c_member_id = '')
246{
247    $params = array();
248
249    $select = 'SELECT *';
250    $from = ' FROM c_album';
251
252    //自分のアルバムだけを対象にする事も出来る
253    if ($c_member_id) {
254        $where = ' WHERE c_member_id = ?';
255        $params[] = intval($c_member_id);
256    } else {
257        $where = " WHERE public_flag = 'public'";
258    }
259
260    //and検索を実装
261    //subject,body を検索
262    if ($keyword) {
263        //全角空白を半角に統一
264        $keyword = str_replace(' ', ' ', $keyword);
265
266        $keyword_list = explode(' ', $keyword);
267        foreach ($keyword_list as $word) {
268            $word = check_search_word($word);
269
270            $where .= ' AND (subject LIKE ? OR description LIKE ?)';
271            $params[] = '%'.$word.'%';
272            $params[] = '%'.$word.'%';
273        }
274    }
275    $order = " ORDER BY r_datetime DESC";
276
277    $sql = $select . $from . $where . $order;
278
279    $list = db_get_all_page($sql, $page, $page_size, $params);
280    foreach($list as $key => $value) {
281        $list[$key]['c_member'] = db_member_c_member_with_profile($value['c_member_id']);
282    }
283
284
285    $sql = 'SELECT COUNT(c_album_id)' . $from . $where;
286    $total_num = db_get_one($sql, $params);
287
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    return array($list , $prev , $next, $total_num);
302}
303
304/**
305 * フレンドの最新アルバムリスト
306 */
307function p_h_album_list_friend_h_album_list_friend4c_member_id($c_member_id, $page_size, $page)
308{
309    $friends = db_friend_c_member_id_list($c_member_id, true);
310    if (!$friends) {
311        return array(array(), false, false, 0);
312    }
313
314    $pf_cond = db_album_public_flag_condition($c_member_id, $u);
315    $from = "c_album, c_friend";
316    $where = "c_friend.c_member_id_from = ?" .
317            " AND c_album.c_member_id = c_friend.c_member_id_to" .
318            ' AND public_flag <> \'private\'';
319
320    $sql = "SELECT c_album.* FROM {$from} WHERE {$where}" .
321            " ORDER BY c_album.r_datetime DESC";
322    $params = array(intval($c_member_id));
323    $lst = db_get_all_page($sql, $page, $page_size, $params);
324
325    foreach ($lst as $key=>$value) {
326        $lst[$key]['c_member'] = db_member_c_member4c_member_id($value['c_member_id']);
327    }
328
329    $sql = "SELECT count(*) FROM {$from} WHERE {$where}";
330    $total_num = db_get_one($sql, $params);
331
332    if ($total_num != 0) {
333        $total_page_num =  ceil($total_num / $page_size);
334        if ($page >= $total_page_num) {
335            $next = false;
336        } else {
337            $next = true;
338        }
339
340        if ($page <= 1) {
341            $prev = false;
342        } else {
343            $prev = true;
344        }
345    }
346
347    return array($lst, $prev, $next, $total_num);
348}
349
350/**
351 * フレンド最新アルバムリスト取得
352 * アルバム公開範囲を考慮
353 *
354 * @param   int $c_member_id
355 * @param   int $limit
356 * @return  array
357 */
358function p_h_home_c_album_friend_list4c_member_id($c_member_id, $limit)
359{
360    $friends = db_friend_c_member_id_list($c_member_id, true);
361    if (!$friends) {
362        return array();
363    }
364    $ids = implode(',', array_map('intval', $friends));
365
366    $sql = 'SELECT * FROM c_album' .
367            ' WHERE c_member_id IN (' . $ids . ')' .
368            ' AND public_flag <> \'private\'' .
369            ' ORDER BY u_datetime DESC';
370
371    $c_album_friend_list = db_get_all_limit($sql, 0, $limit);
372
373    foreach ($c_album_friend_list as $key => $value) {
374        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
375        $c_album_friend_list[$key]['nickname'] = $c_member['nickname'];
376    }
377
378    return $c_album_friend_list;
379}
380
381/**
382 * アルバムを追加
383 */
384function db_album_insert_c_album($c_member_id, $subject, $description,$public_flag)
385{
386    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
387    $subject = db_ktai_delete_url_session_parameter($subject);
388    $description = db_ktai_delete_url_session_parameter($description);
389
390    $data = array(
391        'c_member_id' => intval($c_member_id),
392        'subject' => $subject,
393        'description' => $description,
394        'public_flag' => $public_flag,
395        'u_datetime' => db_now(),
396        'r_datetime' => db_now(),
397        'album_cover_image' => '',
398    );
399    return db_insert('c_album', $data);
400}
401
402
403function db_album_update_c_album($c_album_id, $subject, $description, $public_flag, $image_filename = null)
404{
405    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
406    $subject = db_ktai_delete_url_session_parameter($subject);
407    $description = db_ktai_delete_url_session_parameter($description);
408
409    $data = array(
410        'subject' => $subject,
411        'description' => $description,
412        'public_flag' => $public_flag,
413        'u_datetime' => db_now(),
414    );
415    if ($image_filename) $data['album_cover_image'] = $image_filename;
416
417    $where = array(
418        'c_album_id' => intval($c_album_id),
419    );
420    return db_update('c_album', $data, $where);
421
422}
423
424function db_album_update_c_album_image($c_album_image_id,$image_filename,$image_description,$filesize)
425{
426    // 写真説明に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
427    $image_description = db_ktai_delete_url_session_parameter($image_description);
428
429    $data = array(
430        'image_description' => $image_description,
431    );
432
433     if ($image_filename) {
434        $data['image_filename'] = $image_filename;
435        $data['filesize'] = $filesize;
436     }
437
438    $where = array(
439        'c_album_image_id' => intval($c_album_image_id),
440    );
441
442    return db_update('c_album_image', $data, $where);
443
444}
445
446/**
447 * アルバムの情報を更新
448 */
449function db_album_update_c_album_cover($c_album_id,$subject,$description,$album_cover_image,$public_flag)
450{
451    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
452    $subject = db_ktai_delete_url_session_parameter($subject);
453    $description = db_ktai_delete_url_session_parameter($description);
454
455    $data = array(
456        'subject' => $subject,
457        'description' => $description,
458        'public_flag' => $public_flag,
459        'u_datetime' => db_now(),
460    );
461    if ($album_cover_image) $data['album_cover_image'] = $album_cover_image;
462
463    $where = array(
464        'c_album_id' => intval($c_album_id),
465    );
466
467    return db_update('c_album', $data, $where);
468}
469
470/**
471 * アルバムの表紙写真の名前を更新
472 */
473function db_album_update_c_album_album_cover_image($c_album_id,$album_cover_image)
474{
475    $data = array(
476        'album_cover_image' => $album_cover_image,
477        'u_datetime' => db_now(),
478    );
479
480    $where = array(
481        'c_album_id' => intval($c_album_id),
482    );
483    return db_update('c_album', $data, $where);
484}
485
486/**
487 * アルバムの更新日時を更新
488 * @param int $album_id
489 */
490function db_album_update_c_album_u_datetime($album_id)
491{
492    $data = array(
493        'u_datetime' => db_now(),
494    );
495
496    $where =array(
497        'c_album_id' => $album_id,
498    );
499    return db_update('c_album',$data,$where);
500}
501
502/**
503 * c_album_imageの新規登録
504 *
505 * @param int $c_album_id
506 * @param int $c_member_id
507 * @param string $image_filename
508 * @param string $image_description
509 * @param int $filesize
510 */
511function db_insert_c_album_image($c_album_id, $c_member_id, $image_filename, $image_description, $filesize = 0)
512{
513    // 写真説明に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
514    $image_description = db_ktai_delete_url_session_parameter($image_description);
515
516    $data = array(
517        'c_album_id' => $c_album_id,
518        'c_member_id' => $c_member_id,
519        'image_description' => $image_description,
520        'image_filename' => $image_filename,
521        'filesize' => $filesize,
522        'r_datetime' => db_now(),
523    );
524
525    $insert_id = db_insert('c_album_image', $data);
526    if ($insert_id) {
527        db_album_update_c_album_u_datetime($c_album_id);
528    }
529
530    return $insert_id;
531}
532
533/**
534 * アルバムの削除、写真も削除
535 * @param int $c_album_id
536 */
537function db_album_delete_c_album($c_album_id)
538{
539    $sql = 'SELECT image_filename FROM c_album_image WHERE c_album_id = ?';
540    $filename_list = db_get_col($sql, array($c_album_id), 'main');
541
542    $sql = 'SELECT * FROM c_album WHERE c_album_id = ?';
543    $params = array(intval($c_album_id));
544    $c_album = db_get_row($sql, $params, 'main');
545
546    //アルバムに登録された写真
547    foreach ($filename_list as $filename) {
548        db_album_image_data_delete($filename, $c_album['c_member_id']);
549    }
550
551    // アルバムの表紙
552    if ($c_album['album_cover_image']) {
553        db_album_image_data_delete($c_album['album_cover_image'], $c_album['c_member_id'], 'other');
554    }
555
556    $params = array(intval($c_album_id));
557
558    //アルバムと写真の関連
559    $sql = 'DELETE FROM c_album_image WHERE c_album_id = ?';
560    db_query($sql, $params);
561
562    // アルバム
563    $sql = 'DELETE FROM c_album WHERE c_album_id = ?';
564    db_query($sql, $params);
565
566    return;
567}
568
569/**
570 * c_album_image_idをキーとしてc_album_imageテーブルからデータを削除
571 */
572function db_album_delete_c_album_image($c_album_image_id, $c_member_id)
573{
574    $sql = 'SELECT image_filename FROM c_album_image WHERE c_album_image_id = ?';
575    $filename = db_get_one($sql, array($c_album_image_id), 'main');
576    db_album_image_data_delete($filename, $c_member_id);
577
578    $sql = 'DELETE FROM c_album_image WHERE c_album_image_id = ?';
579    $params = array(intval($c_album_image_id));
580
581    return db_query($sql, $params);
582}
583
584/**
585 * アルバム用の写真を登録
586 */
587function image_insert_c_image_album4tmp($prefix, $tmpfile, $c_member_id)
588{
589    if (!$tmpfile || preg_match('/[^\.\w]/', $tmpfile)) return false;
590
591    $path_parts = pathinfo($tmpfile);
592    $ext = $path_parts['extension'];
593    $ext = strtolower($ext);
594
595    $allowed_ext = array('jpg', 'jpeg', 'gif', 'png');
596    if (!in_array($ext, $allowed_ext)) {
597        return false;
598    }
599
600    $filename = sprintf('%s_%s.%s', $prefix, time(), $ext);
601
602    if (!OPENPNE_TMP_IMAGE_DB) {
603        $img_tmp_dir_path = OPENPNE_VAR_DIR . '/tmp/';
604        $filepath = $img_tmp_dir_path . basename($tmpfile);
605
606        if (!is_readable($filepath)) {
607            return false;
608        }
609
610        $filesize = filesize($filepath);
611        $fp = fopen($filepath, 'rb');
612        $bin = fread($fp, $filesize);
613        fclose($fp);
614
615        // 写真かどうかのチェック
616        if (!@imagecreatefromstring($bin)) {
617            return false;
618        }
619    } else {
620        $c_tmp_image = db_image_c_tmp_image4filename($tmpfile);
621        $bin = base64_decode($c_tmp_image['bin']);
622        $filesize = strlen($bin);
623    }
624
625    if (db_image_insert_c_image_album($filename, $bin,$filesize, $c_member_id)) {
626        return array($filename, $filesize);
627    }
628
629    return false;
630}
631
632function db_image_insert_c_image_album($filename, $bin, $filesize, $c_member_id, $type = '')
633{
634    $db =& db_get_instance('image');
635
636    $data = array(
637        'filename'   => $filename,
638        'bin'        => base64_encode($bin),
639        'type'       => $type,
640        'r_datetime' => db_now(),
641    );
642    $result =  $db->insert('c_image', $data, 'c_image_id');
643    if ($result) {
644        db_image_insert_c_image_size($filename, $c_member_id, $filesize);
645    }
646
647    return $result;
648}
649
650function db_album_image_data_delete($image_filename, $c_member_id, $category = '')
651{
652    if (!$image_filename) return false;
653
654    db_album_image_delete_c_image($image_filename, $c_member_id, $category);
655
656    // cacheの削除
657    image_cache_delete($image_filename);
658}
659
660function db_album_image_delete_c_image($filename, $c_member_id, $category)
661{
662    $db =& db_get_instance('image');
663
664    $sql = 'DELETE FROM c_image WHERE filename = ?';
665    $params = array($filename);
666    $db->query($sql, $params);
667
668    $sql = 'DELETE FROM c_image_size WHERE filename = ?';
669    $params = array($filename);
670    $db->query($sql, $params);
671
672    //function cacheの削除
673    if (!$category) {
674        $category = util_image_filename2category($filename);
675    }
676    pne_cache_drop('db_image_get_image_filesize', $c_member_id, $category);
677
678    return true;
679
680}
681
682function db_image_is_c_album_image4filename($filename)
683{
684    if (!$filename) return false;
685
686    $db =& db_get_instance('image');
687
688    $sql = 'SELECT c_image_id FROM c_image WHERE filename = ?';
689    $params = array($filename);
690    return (bool)$db->get_one($sql, $params);
691}
692
693/**
694 * メンバーのすべてのアルバム写真のファイルサイズの合計を取得する
695 *
696 * @param int $c_member_id
697 * @return int
698 */
699function db_album_sum_filesize4c_member_id($c_member_id)
700{
701    $sql = 'SELECT filesize FROM c_album_image WHERE c_member_id = ?';
702    $list = db_get_col($sql, array($c_member_id));
703    return array_sum($list);
704}
705
706/**
707 * メンバーが写真を投稿可能かどうか
708 *
709 * メンバーのファイルサイズの合計が OPENPNE_ALBUM_LIMIT を超過していないかどうかを返す
710 * $new_filesize を指定した場合は、メンバーのファイルサイズの合計に $new_filesize を
711 * 加算したものが OPENPNE_ALBUM_LIMIT を超過していないかどうかを返す
712 *
713 * @param int $c_member_id
714 * @param int $new_filesize
715 * @return bool
716 */
717function db_album_is_insertable4c_member_id($c_member_id, $new_filesize = 0)
718{
719    if (!OPENPNE_ALBUM_LIMIT) {
720        return true;
721    }
722
723    $size = db_album_sum_filesize4c_member_id($c_member_id) + $new_filesize;
724
725    $mb = 1048576;
726    if (OPENPNE_ALBUM_LIMIT * $mb < $size) {
727        return false;
728    }
729
730    return true;
731}
732
733/**
734 * メンバーのアルバムを削除する
735 *
736 * @param int $c_member_id
737 */
738function db_album_delete4c_member_id($c_member_id)
739{
740    $sql = 'SELECT c_album_id FROM c_album WHERE c_member_id = ?';
741    $params = array(intval($c_member_id));
742    $c_album_id_list = db_get_col($sql, $params, 'main');
743    foreach ($c_album_id_list as $c_album_id) {
744        db_album_delete_c_album($c_album_id);
745    }
746}
747
748?>