path: root/music.php

<?php 

require_once 'config.php';
require_once 'connect.php';

class Tag 
{
    public $uid; 
    public $name;
}

class Album 
{
    public $uid; 
    public $name;
}

class Music 
{
    public $uid;   // unique id
    public $path;  // 路径
    public $title;  // 标题名
    public $time;  // 创建时间
    public $cover; // 封面
    public $project; // 封面
    public $array_tags;  // 标签名
    public $array_albums; // 从属的专辑
}

function fetch_tag_by_id($uid) 
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_tags . " where id=" . $uid);
    while($row = mysqli_fetch_assoc($result)){
        $tag = new Tag; 
        $tag->uid = $uid;
        $tag->name = $row['name'];
        return $tag;
    }
    return null;
}

function fetch_tag_by_name($name)
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_tags . " where name='" . $name . "'");
    if($result == NULL || $result->num_rows == 0)
        return null;
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $tag = new Tag; 
        $tag->uid = $row['id'];
        $tag->name = $row['name'];
        array_push($arr, $tag);
    }
    return $arr;
}

function fetch_all_tags() {
    $sql = "SELECT * FROM ". Config::$tb_tags;
    $result = execute_sql($sql);
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $tag = new Tag; 
        $tag->uid = $row['id'];
        $tag->name = $row['name'];
        array_push($arr, $tag);
    }
    return $arr;
}

function fetch_album_by_id($uid)
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_album . " where id=" . $uid);
    while($row = mysqli_fetch_assoc($result)){
        $album = new Album; 
        $album->uid = $uid;
        $album->name = $row['name'];
        return $album;
    }
    return null;
}

function fetch_album_by_name($name)
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_album . " where name='" . $name . "'");
    if($result == NULL || $result->num_rows == 0)
        return null;
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $album = new Album; 
        $album->uid = $row['id'];
        $album->name = $row['name'];
        array_push($arr, $album);
    }
    return $arr;
}

function insert_tag($name) 
{
    execute_sql("INSERT INTO " . Config::$tb_tags . " (name) values('" . $name . "')");
}

function insert_album($name)
{
    execute_sql("INSERT INTO " . Config::$tb_album . " (name) values('" . $name . "')");
}

function add_unique_tag($name) {
    $arr = fetch_tag_by_name($name);
    if($arr == null || count($arr) == 0) {
        insert_tag($name);
        $arr = fetch_tag_by_name($name);
    }
    return $arr[0];
}

function add_unique_album($name) {
    $arr = fetch_album_by_name($name);
    if($arr == null || count($arr) == 0) {
        insert_album($name);
        $arr = fetch_album_by_name($name);
    }
    return $arr[0];
}

// 获得某个音乐的tags
function fetch_music_tags($uid) {
    $sql = "SELECT * FROM " . Config::$tb_music_tag . ' WHERE music_id=' . $uid;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
        return null;   
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $tagId = $row['tag_id'];
        $tag = fetch_tag_by_id($tagId);
        array_push($arr, $tag);
    }
    return $arr;
}

// 获得某个音乐从属的专辑
function fetch_music_albums ($uid){
    $sql = "SELECT * FROM " . Config::$tb_album_music . ' WHERE music_id=' . $uid;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
        return null;   
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $albumId = $row['album_id'];
        $album = fetch_album_by_id($albumId);
        array_push($arr, $album);
    }
    return $arr;
}

function fetch_music_by_id($uid) {
    $sql = "SELECT * FROM " . Config::$tb_music . " WHERE id=" . $uid;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
    {
        return null;   
    }
    while($row = mysqli_fetch_assoc($result)){
        $music = new Music(); 
        $music->uid = $row['id'];
        $music->path = $row['path'];
        $music->cover = $row['cover'];
        $music->project = $row['project'];
        $music->title = $row['title'];
        $music->time = $row['time'];
        $music->array_tags = fetch_music_tags($music->uid);
        $music->array_albums = fetch_music_albums($music->uid);
        return $music;
    }
}

// 从第from个记录开始的count个
function fetch_range_music($from=0, $count=100) {
    $sql = "SELECT id FROM " . Config::$tb_music .  "  ORDER  BY time DESC LIMIT " . $from . ", " . $count ;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
    {
        return null;   
    }
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $id = $row['id'];
        $music = fetch_music_by_id($id);
        array_push($arr, $music);
    }
    
    return $arr;
}

//搜索
 
function fetch_musics_by_name($name, $from=0, $count=100) {
    $sql = "SELECT id FROM " . Config::$tb_music . " WHERE title LIKE '%" . $name . "%' ORDER BY time DESC  LIMIT " . $from . ", " . $count;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return ;
    $arr = array(); 
    while($row = mysqli_fetch_assoc($result)){
        $music_id = $row['id'];
        $music = fetch_music_by_id($music_id);
        array_push($arr, $music);
    }
    return $arr;
}

function get_music_count_by_name($name) {
    $sql = "SELECT COUNT(*) as count FROM " . Config::$tb_music . " WHERE title LIKE '%" . $name . "%'";
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return 0;
    $row = mysqli_fetch_assoc($result);
    if($row != null){
        return $row['count']; 
    }
    return 0;
}

function fetch_musics_by_tag($tag_id, $from=0, $count=100) {
    $sql = "SELECT music_id FROM " . Config::$tb_music_tag . " WHERE tag_id=" . $tag_id . " ORDER  BY music_id DESC LIMIT " . $from . ", " . $count;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return ;
    $arr = array(); 
    while($row = mysqli_fetch_assoc($result)){
        $music_id = $row['music_id'];
        $music = fetch_music_by_id($music_id);
        array_push($arr, $music);
    }
    return $arr;
}

function get_music_count_by_tag($tag_id) {
    $sql = "SELECT COUNT(*) as count FROM " . Config::$tb_music_tag . " WHERE tag_id=" . $tag_id ;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return 0;
    $row = mysqli_fetch_assoc($result);
    if($row != null){
        return $row['count']; 
    }
    return 0;
}

function fetch_musics_by_album($album_id, $from=0, $count=100) {
    $sql = "SELECT music_id FROM " . Config::$tb_album_music .  " WHERE album_id=" . $album_id . " ORDER  BY time DESC LIMIT " . $from . ", " . $count;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return ;
    $arr = array(); 
    while($row = mysqli_fetch_assoc($result)){
        $music_id = $row['music_id'];
        $music = fetch_music_by_id($music_id);
        array_push($arr, $music);
    }
    return $arr;
}

function get_all_music_count() {
    $sql = "SELECT COUNT(*) as count FROM " . Config::$tb_music;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return 0;
    $row = mysqli_fetch_assoc($result);
    if($row != null){
        return $row['count']; 
    }
    return 0;
}

function add_unique_music_tag( $music_id, $tag_id )
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_music_tag . " where music_id=" . $music_id . " and tag_id=" . $tag_id );
    if($result == NULL || $result->num_rows == 0)
    {
        execute_sql("INSERT INTO " . Config::$tb_music_tag . " (music_id, tag_id) values (" . $music_id . ", " . $tag_id . ")");
    }
}

function add_unique_music_album( $music_id, $album_id )
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_album_music . " where music_id=" . $music_id . " and album_id=" . $album_id );
    if($result == NULL || $result->num_rows == 0)
    {
        execute_sql("INSERT INTO " . Config::$tb_album_music . " (music_id, album_id) values (" . $music_id . ", " . $album_id . ")");
    }
}

function get_last_id() {
    $result = execute_sql("select @@IDENTITY");
    while($row = mysqli_fetch_assoc($result)){
        println($row);
    }
}

// 提交上来的表单中构建music
function get_music_from_post() 
{
    $music = new Music; 

    $music_file = $_FILES["musicFile"];
    $cover_image = $_FILES["coverImage"];
    $music_proj = $_FILES["musicProj"];

    if(is_upload_notempty($music_file)) {
        $res_music =  upload_file(Config::$music_upload_dir, $music_file);
        if($res_music[0] == 0)
            return null;
        $music->path = $res_music[1];
    }
    else  // 必须有音乐文件
    {
        return null; 
    }

    if(is_upload_notempty($cover_image)) {
        $res_cover =  upload_file(Config::$cover_upload_dir, $cover_image);
        if($res_cover[0] == 0)
            return null;
        $music->cover = $res_cover[1];
    }

    if(is_upload_notempty($music_proj)) {
        $res_proj =  upload_file(Config::$project_upload_dir, $music_proj);
        if($res_proj[0] == 0)
            return null;
        $music->project = $res_proj[1];
    }


    if($_POST["tags"] != null && $_POST["tags"] != "")
        $tags_name = explode(',', $_POST["tags"]);

    if($_POST["albums"] != null && $_POST["albums"] != "")
        $albums_id = explode(',', $_POST["albums"]);
    
    $music->array_tags = array();
    if($tags_name != null && count($tags_name) > 0) {
        foreach($tags_name as $tag_name){
            $tag = add_unique_tag($tag_name); 
            array_push($music->array_tags, $tag);
        }
    }

    $music->array_albums = array();
    if($albums_id != null && count($albums_id) > 0) {
        foreach($albums_id as $album_id){
            $album = fetch_album_by_id($album_id);
            if($album == null)
                continue;
            array_push($music->array_albums, $album);
        }
    }

    $music->time = time(); 

    $music->title = html_escape_string($_POST["title"]);

    $music->uid = -1; // 未插入数据库

    //println($music);

    return $music;
}

// 将music写入数据库（插入或更新）
function write_music_to_db($music) {
    if ($music == null )
        return ;

    if($music->uid == -1){// 插入新的

        $sql = "INSERT INTO " . Config::$tb_music . " (path, title, time, cover, project) values (" .
        "'". $music->path . "',".
        "'". $music->title . "',".
        $music->time . ",".
        "'". $music->cover . "',".
        "'". $music->project . "'".
        ")" ;
        //println($sql);
        execute_sql($sql);

        $id = get_last_insert_id();

        foreach($music->array_tags as $tag){
            add_unique_music_tag($id, $tag->uid);
        }

        foreach($music->array_albums as $album){
            add_unique_music_album($id, $album->uid);
        }

    }
    else // 更新 
    {



    }

}

?>