source: OpenPNE/branches/work/asano/pne_album/webapp/lib/db/album.php @ 6362

<?php
/**
 * @copyright 2005-2007 OpenPNE Project
 * @license   http://www.php.net/license/3_01.txt PHP License 3.01
 */

/**
 * db_album_public_flag_condition
 * 
 * @param int $c_member_id target c_member_id
 * @param int $u viewer's c_member_id
 * @param string $force
 * @return string
 */
function db_album_public_flag_condition($c_member_id, $u = null, $force = null)
{
    $pf_cond = '';
    if ($force) {
        switch ($force) {
        case 'friend':
            $pf_cond = " AND c_album.public_flag <> 'private'";
            break;
        case 'private':
            $pf_cond = " AND c_album.public_flag = 'public'";
            break;
        }
    } else {
        if (!is_null($u) && $c_member_id != $u) {
            $is_friend = db_friend_is_friend($c_member_id, $u);
            if ($is_friend) {
                $pf_cond = " AND c_album.public_flag <> 'private'";
            } else {
                $pf_cond = " AND c_album.public_flag = 'public'";
            }
        }
    }
    return $pf_cond;
}

//// c_album

/**
 * アルバム情報を取得
 * @param int $c_album_id
 */
function db_album_get_c_album4c_album_id($c_album_id)
{
    $sql = 'SELECT * FROM c_album WHERE c_album_id = ?';
    $params = array(intval($c_album_id));
    
    $album = db_get_row($sql, $params);

    return $album;
}

/**
 * あるメンバーのアルバムのサブジェクト一覧を取得
 * @param int $c_member_id target_member_id
 * @param int $count
 * @param int $u viewer's member_id
 */
function db_album_get_c_album_subject_list4c_member_id($c_member_id, $count= 10 ,$u = null)
{
    $pf_condition = db_album_public_flag_condition($c_member_id, $u);
    $sql = 'SELECT subject,c_album_id FROM c_album WHERE c_member_id = ? ' 
        . $pf_condition
        . ' ORDER BY r_datetime DESC';
    
    $params = array(intval($c_member_id));        
    return db_get_all_limit($sql, 0, $count, $params);
}

/**
 * アルバム画像を取得
 * @param int $c_album_image_id
 */
function db_album_image_get_c_album_image4id($c_album_image_id)
{
    $sql = 'SELECT * FROM c_album_image WHERE c_album_image_id = ?';
    $params = array(intval($c_album_image_id));

    return db_get_row($sql,$params);
}

/**
 * c_album_idをキーとしてc_albumデータが存在するか判定
 * @param int $c_album_id
 * @return bool true,false
 */
function p_common_is_active_c_album_id($c_album_id)
{
    $sql = 'SELECT c_album_id FROM c_album WHERE c_album_id = ?';
    return (bool)db_get_one($sql, array(intval($c_album_id)));
}

/**
 * c_album_image_idをキーとしてc_album_imageデータが存在するか判定
 * @param int $c_album_image_id
 * @return bool true,false
 */
function p_common_is_active_c_album_image_id($c_album_image_id)
{
    $sql = 'SELECT c_album_image_id FROM c_album_image WHERE c_album_image_id = ?';
    $params = array(intval($c_album_image_id));
    
    return (bool)db_get_one($sql,$params);
}

/**
 * アルバムIDからアルバムに登録された画像を取得
 * @param   int   $c_album_id
 * @return  array
 */
function db_album_get_c_album_page4id($c_album_id,$page_size=10,$page=0)
{
    $select = 'SELECT ab.*,i.c_album_image_id,i.image_filename,i.image_description';
    $from  = ' FROM c_album AS ab,c_album_image AS i';
    $where = ' WHERE ab.c_album_id = i.c_album_id' .
             ' AND ab.c_album_id = ?';
    $orderby = ' ORDER BY i.c_album_image_id ASC';
    $sql = $select .$from . $where .$orderby;
    
    $params = array(intval($c_album_id));
    $list = db_get_all_limit($sql, $page_size * ($page - 1), $page_size, $params);
    
    $sql = 'SELECT COUNT(c_album_image_id) FROM c_album_image WHERE c_album_id = ?';
    $params = array(intval($c_album_id));
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }

        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    return array($list, $prev, $next, $total_num);
}

/**
 * 指定したアルバム画像の前の画像IDを取得する
 * @param int $c_member_id
 * @param int $c_album_id
 * @param int $c_album_image_id
 * @param int $u
 */
function db_album_image_c_album_image_id_prev4c_album_id($c_member_id, $c_album_id, $c_album_image_id, $u = null)
{
    $public_flag_condition = db_album_public_flag_condition($c_member_id, $u);
    
    $select  = 'SELECT c_album_image.c_album_image_id ';
    $from    = ' FROM c_album_image, c_album';
    $where   = ' WHERE c_album.c_album_id = c_album_image.c_album_id' . 
                ' AND c_album.c_member_id = ?' .
                ' AND c_album.c_album_id = ?' .
                ' AND c_album_image.c_album_image_id < ?'
                . $public_flag_condition;
    $orderby = ' ORDER BY c_album_image.c_album_image_id DESC';
    $sql = $select . $from . $where . $orderby;
    $params = array(intval($c_member_id),intval($c_album_id),intval($c_album_image_id));
        
    return db_get_one($sql, $params);
}

/**
 * 指定したアルバム画像の次の画像IDを取得する
 * @param int $c_member_id
 * @param int $c_album_id
 * @param int $c_album_image_id
 * @param int $u
 */
function db_album_image_c_album_image_id_next4c_album_id($c_member_id, $c_album_id, $c_album_image_id, $u = null)
{
    $public_flag_condition = db_album_public_flag_condition($c_member_id, $u);
    
    $select  = 'SELECT c_album_image.c_album_image_id ';
    $from    = ' FROM c_album_image, c_album';
    $where   = ' WHERE c_album.c_album_id = c_album_image.c_album_id' . 
                ' AND c_album.c_member_id = ?' .
                ' AND c_album.c_album_id = ?' .
                ' AND c_album_image.c_album_image_id > ?'
                . $public_flag_condition;
    $orderby = ' ORDER BY c_album_image.c_album_image_id ASC';
    $sql = $select . $from . $where . $orderby;
    $params = array(intval($c_member_id),intval($c_album_id),intval($c_album_image_id));
        
    return db_get_one($sql, $params);
}

/**
 * c_albumの閲覧権限チェック
 * @param int $c_album_id
 * @param int $c_member_id
 */
function pne_check_album_public_flag($c_album_id, $c_member_id)
{
    $c_album = db_album_get_c_album4c_album_id($c_album_id);
    if ($c_album['c_member_id'] == $c_member_id) {
        return true;
    }

    switch ($c_album['public_flag']) {
    case 'public':
        $allowed = true;
        break;
    case 'friend':
        $allowed = db_friend_is_friend($c_album['c_member_id'], $c_member_id);
        break;
    case 'private':
    default:
        $allowed = false;
        break;
    }

    return $allowed;
}

/*
function p_fh_album_list_album_image_list4c_album_id($c_album_id, $page_size, $page)
{
    $sql = 'SELECT * FROM c_album_image WHERE c_album_id = ?';
    $params = array(intval($c_album_id));
    $list = db_get_all_page($sql, $page, $page_size, $params);

    
    $sql = 'SELECT COUNT(*) FROM c_album_image WHERE c_album_id = ?';
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }

        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    return array($list, $prev, $next, $total_num);
}
*/

//取得相册列表
/*
function p_fh_album_list_fh_album_list4c_member_id($c_member_id, $page_size, $page,$u = null)
{
    $pf_cond = db_album_public_flag_condition($c_member_id, $u);
    $sql = 'SELECT * FROM c_album WHERE c_member_id = ?'.$pf_cond.
           ' ORDER BY r_datetime DESC';
    $params = array(intval($c_member_id));
    $list = db_get_row($sql,$params);
    
//    print "<pre>";
//    echo "IN DATABASE<br><br>";
//    print_r ($list);
//    echo "IN DATABASE<br><br>";
//    print "</pre>";
    
    $sql = 'SELECT COUNT(*) FROM c_album WHERE c_member_id = ?' . $pf_cond;
    $params1 = array();
    $total_num = db_get_one($sql, $params);
    
    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }

        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    
    $temp = array($list, $prev, $next, $total_num); 
    

    return $temp;
}
*/

/*
function db_album_category_c_album_category4c_album_id($c_album_id)
{
    $sql = 'SELECT category_name FROM c_album_category AS cac,c_album_category_album AS caca WHERE cac.c_album_category_id = caca.c_album_category_id AND caca.c_album_id = ?';
    $params = array(intval($c_album_id));
    return db_get_one($sql, $params);
}
*/

/**
 * 特定ユーザーのアルバムリスト取得
 * @param int $c_member_id target_c_member_id
 * @param int $page_size
 * @param int $page
 * @param int $u viewer's c_member_id
 */
function p_fh_album_list_fh_my_album_list4c_member_id($c_member_id, $page_size, $page, $u = null)
{
    $pf_cond = db_album_public_flag_condition($c_member_id, $u);
    $sql = 'SELECT * FROM c_album WHERE c_member_id = ?'. $pf_cond.
           ' ORDER BY r_datetime DESC';
    $params = array(intval($c_member_id));
    $list = db_get_all_page($sql,$page, $page_size,$params);
    
    $sql = 'SELECT COUNT(c_album_id) FROM c_album WHERE c_member_id = ?' . $pf_cond;
    $total_num = db_get_one($sql, $params);
    
    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }

        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    
    return array($list, $prev, $next, $total_num);
}

/**
 * アルバム検索
 * 検索ポイントはアルバムタイトル・アルバム説明
 * 空白（全角半角問わない）でand検索可
 */
function p_h_album_list_all_search_c_album4c_album($keyword, $page_size, $page, $c_member_id = '')
{
    $params = array();

    $select = 'SELECT *';
    $from = ' FROM c_album';

    //自分のアルバムだけを対象にする事も出来る
    if ($c_member_id) {
        $where = ' WHERE c_member_id = ?';
        $params[] = intval($c_member_id);
    } else {
        $where = " WHERE public_flag = 'public'";
    }

    //and検索を実装
    //subject,body を検索
    if ($keyword) {
        //全角空白を半角に統一
        $keyword = str_replace('　', ' ', $keyword);

        $keyword_list = explode(' ', $keyword);
        foreach ($keyword_list as $word) {
            $word = check_search_word($word);
            
            $where .= ' AND (subject LIKE ? OR description LIKE ?)';
            $params[] = '%'.$word.'%';
            $params[] = '%'.$word.'%';
        }
    }
    $order = " ORDER BY r_datetime DESC";

    $sql = $select . $from . $where . $order;
    
    $list = db_get_all_page($sql, $page, $page_size, $params);
    foreach($list as $key => $value) {
        $list[$key]['c_member'] = db_common_c_member_with_profile($value['c_member_id']);
    }


    $sql = 'SELECT COUNT(c_album_id)' . $from . $where;
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }
        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    return array($list , $prev , $next, $total_num);
}

/**
 * フレンドの最新アルバムリスト
 */
function k_p_h_album_list_friend_h_album_list_friend4c_member_id($c_member_id, $page_size, $page)
{
    $from = "c_album, c_friend";
    $where = "c_friend.c_member_id_from = ?" .
            " AND c_album.c_member_id = c_friend.c_member_id_to";

    $sql = "SELECT c_album.* FROM {$from} WHERE {$where}" .
            " ORDER BY c_album.r_datetime DESC";
    $params = array(intval($c_member_id));
    $lst = db_get_all_page($sql, $page, $page_size, $params);

    foreach ($lst as $key=>$value) {
        //$lst[$key]['count_comments'] = db_album_count_c_album_comment4c_album_id($value['c_album_id']);
        $lst[$key]['c_member'] = db_common_c_member4c_member_id($value['c_member_id']);
    }

    $sql = "SELECT count(*) FROM {$from} WHERE {$where}";
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }

        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }

    return array($lst, $prev, $next, $total_num);
}

/**
 * アルバムを追加
 */
function db_album_insert_c_album($c_member_id, $subject, $description,$public_flag)
{
    $data = array(
        'c_member_id' => intval($c_member_id),
        'subject' => $subject,
        'description' => $description,
        'public_flag' => $public_flag,
        'r_datetime' => db_now(),
    );
    
    return db_insert('c_album', $data);
}


function db_album_update_c_album($c_album_id,$subject,$description,$public_flag,$image_filename)
{
    $data = array(
        'subject' => $subject,
        'description' => $description,
        'public_flag' => $public_flag,
    );
    if ($image_filename) $data['album_cover_image'] = $image_filename;

    $where = array(
        'c_album_id' => intval($c_album_id),
    );
    return db_update('c_album', $data, $where);
    
}

function db_album_update_c_album_image($c_album_image_id,$image_filename,$image_description)
{
    $data = array(
        'image_description' => $image_description,
    );
    if ($image_filename) $data['image_filename'] = $image_filename;

    $where = array(
        'c_album_image_id' => intval($c_album_image_id),
    );
    
    return db_update('c_album_image', $data, $where);
}

/**
 * アルバムの情報を更新
 */
function db_album_update_c_album_cover($c_album_id,$subject,$description,$album_cover_image,$public_flag)
{
    $data = array(
        'subject' => $subject,
        'description' => $description,
        'public_flag' => $public_flag,
    );
    if ($album_cover_image) $data['album_cover_image'] = $album_cover_image;

    $where = array(
        'c_album_id' => intval($c_album_id),
    );

    return db_update('c_album', $data, $where);
}

/**
 * アルバムの表紙画像の名前を更新
 */
function db_album_update_c_album_album_cover_image($c_album_id,$album_cover_image)
{
    $data = array(
        'album_cover_image' => $album_cover_image,
    );

    $where = array(
        'c_album_id' => intval($c_album_id),
    );
    return db_update('c_album', $data, $where);
}

/**
 * アルバムを編集
 */
/*
function db_album_insert_c_album_image($c_album_image_id,$image_filename_1 = '',$image_description)
{
    $data = array(
            'c_album_image_id'=>$c_album_image_id,
            'image_description'=>$image_description,
            'r_datetime' => db_now(),
    );
    if ($image_filename_1) $data['image_filename'] = $image_filename_1;

    $where = array(
        'c_album_image_id' => intval($c_album_image_id),
    );

    return db_insert('c_album_image', $data, $where);
}
*/

/**
 * c_album_imageの新規登録
 * @param int $c_album_id
 * @param string $image_filename
 * @param string $image_description
 */
function db_insert_c_album_image($c_album_id, $image_filename, $image_description)
{
    $data = array(
            'c_album_id'=>$c_album_id,
            'image_description'=>$image_description,
            'r_datetime' => db_now(),
    );
    if ($image_filename) $data['image_filename'] = $image_filename;
        
    return db_insert('c_album_image', $data);
}

/**
 * アルバムの削除、画像も削除
 * @param int $c_album_id
 */
function db_album_delete_c_album($c_album_id)
{
    //function cacheの削除
    $c_album_image = db_album_get_c_album4id($c_album_id);
    pne_cache_drop('db_album_get_c_album_list4c_member_id', $c_album_image['c_member_id'], 5);

    //アルバムに登録された画像
    foreach($c_album_image as $key=>$value){
        db_album_image_data_delete($c_album_image[$key]['image_filename']);
    }

    // アルバムの表紙
    $c_album = db_album_get_c_album4c_album_id($c_album_id);
    if($c_album['album_cover_image']){
        db_album_image_data_delete($c_album['album_cover_image']);
    }

    $params = array(intval($c_album_id));
    
    //アルバムと画像の関連
    $sql = 'DELETE FROM c_album_image WHERE c_album_id = ?';
    db_query($sql, $params);

    // アルバム
    $sql = 'DELETE FROM c_album WHERE c_album_id = ?';
    db_query($sql, $params);

    return;
}

/**
 * c_album_image_idをキーとしてc_album_imageテーブルからデータを削除
 */
function db_album_delete_c_album_image($c_album_image_id)
{
    $sql = 'DELETE FROM c_album_image WHERE c_album_image_id = ?';
    $params = array(intval($c_album_image_id));

    return db_query($sql, $params);
}

/**
 * アルバム用の画像を登録
 */
function image_insert_c_image_album4tmp($prefix, $tmpfile)
{
    if (!$tmpfile || preg_match('/[^\.\w]/', $tmpfile)) return false;

    $path_parts = pathinfo($tmpfile);
    $ext = $path_parts['extension'];
    $ext = strtolower($ext);

    $allowed_ext = array('jpg', 'jpeg', 'gif', 'png');
    if (!in_array($ext, $allowed_ext)) {
        return false;
    }

    $filename = sprintf('%s_%s.%s', $prefix, time(), $ext);

    if (!OPENPNE_TMP_IMAGE_DB) {
        $img_tmp_dir_path = OPENPNE_VAR_DIR . '/tmp/';
        $filepath = $img_tmp_dir_path . basename($tmpfile);

        if (db_image_insert_c_image_album2($filename, $filepath)) {
            return $filename;
        }
    } else {
        $c_tmp_image = c_tmp_image4filename($tmpfile);

        $params = array(
            'filename' => $filename,
            'bin' => $c_tmp_image['bin'],
            'r_datetime' => db_now(),
            'type' => '',
        );
        
        if (db_insert("c_image_album", $params)) {
            return $filename;
        }
    }
    return false;
}

function db_image_insert_c_image_album2($filename, $filepath)
{
    if (!is_readable($filepath)) return false;

    $fp = fopen($filepath, 'rb');
    $image_data = fread($fp, filesize($filepath));
    fclose($fp);

    // 画像かどうかのチェック
    if (!@imagecreatefromstring($image_data)) return false;

    //TODO:typeフィールドを使う
    return db_image_insert_c_image_album($filename, $image_data);
}

function db_image_insert_c_image_album($filename, $bin, $type = '')
{
    $db =& db_get_instance('image');

    $data = array(
        'filename'   => $filename,
        'bin'        => base64_encode($bin),
        'type'       => $type,
        'r_datetime' => db_now(),
    );
    return $db->insert('c_image_album', $data, 'c_image_id');
}

//管理画面用に残しておく
function db_image_c_album_image_list($page, $page_size, &$pager)
{
    $db =& db_get_instance('image');

    $sql = 'SELECT c_image_id FROM c_image_album ORDER BY c_image_id DESC';
    $id_list = db_get_col_page($sql, $page, $page_size);

    $c_image_list = array();
    foreach ($id_list as $c_image_id) {
        $sql = 'SELECT c_image_id, filename, r_datetime FROM c_image_album WHERE c_image_id = ?';
        $params = array(intval($c_image_id));
        $c_image_list[] = $db->get_row($sql, $params);
    }

    $sql = 'SELECT COUNT(*) FROM c_image_album';
    $total_num = $db->get_one($sql);

    $pager = admin_make_pager($page, $page_size, $total_num);
    return $c_image_list;
}

/*
function db_image_c_album_image4c_image_id($c_image_id)
{
    $db =& db_get_instance('image');

    $sql = 'SELECT * FROM c_image_album WHERE c_image_id = ?';
    $params = array(intval($c_image_id));
    return $db->get_row($sql, $params);
}
*/

function db_album_image_data_delete($image_filename)
{
    if (!$image_filename) return false;

    db_album_image_delete_c_image($image_filename);

    // cacheの削除
    image_cache_delete($image_filename);
}



function db_album_image_delete_c_image($filename)
{
    $db =& db_get_instance('image');

    $sql = 'DELETE FROM c_image_album WHERE filename = ?';
    $params = array($filename);
    return $db->query($sql, $params);
}

function db_image_is_c_album_image4filename($filename)
{
    if (!$filename) return false;

    $db =& db_get_instance('image');

    $sql = 'SELECT c_image_id FROM c_image_album WHERE filename = ?';
    $params = array($filename);
    return (bool)$db->get_one($sql, $params);
}

/*
function admin_insert_c_image_album($upfile_obj, $filename)
{
    if ($upfile_obj &&
        is_uploaded_file($upfile_obj['tmp_name']) &&
        db_image_insert_c_image_album2($filename, $upfile_obj['tmp_name']) > 0)
    {
        return $filename;
    }

    return false;
}
*/

?>