mp_audio_manage/app/Services/Book/BookService.php

<?php

namespace App\Services\Book;

use App\Consts\ErrorConst;
use App\Facade\Site;
use App\Libs\Utils;
use getID3;
use GuzzleHttp\Client;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;
use OSS\Core\OssException;
use OSS\OssClient;

class BookService
{

    public function __construct() {

    }

    public function getBookList($data) {
        $bid = getProp($data, 'bid');
        $book_name = getProp($data, 'book_name');
        $status = getProp($data, 'status');
        $page = getProp($data, 'page', 1);
        $per_page = getProp($data, 'pageSize', 15);

        $query = DB::table('books as b')->leftJoin('book_configs as bc', 'b.id', '=', 'bc.bid')
        ->where('b.size', '<', 30000)->where('b.status', 1)
        ->whereIn('bc.cp_source', ['ycsd', 'yqsd'])->select('b.status', 'bc.*', 
        DB::raw('(select count(id) from mp_book_version where bid = b.id) as version_count'));

        if ($bid) {
            $query->where('b.id', $bid);
        }

        if ($book_name) {
            $query->where('bc.book_name', 'like', "%{$book_name}%");
        }

        if ($status !== '') {
            $query->where('b.status', $status);
        }

        $result = $query->orderBy('b.id')->paginate($per_page);

        return $result;
    }

    public function getAllBooks($data) {
        $bid = getProp($data, 'bid');
        $book_name = getProp($data, 'book_name');

        $query = DB::table('books as b')->leftJoin('book_configs as bc', 'b.id', '=', 'bc.bid')
        ->where('b.size', '<', 30000)->where('b.status', 1)
        ->whereIn('bc.cp_source', ['ycsd', 'yqsd'])->select('bc.bid', 'bc.book_name', DB::raw('(select count(id) from chapters where is_check=1 and is_deleted=0 and bid = b.id) as chapter_count'));
        if ($bid) {
            $query->where('bc.bid', $bid);
        }

        if ($book_name) {
            $query->where('bc.book_name', 'like', "%{$book_name}%");
        }

        return $query->orderBy('bc.bid', 'desc')->get()->map(function($value) {
            return (array)$value;
        })->toArray();
    }

    public function getBookVersion($data) {
        $bid = getProp($data, 'bid');
        if (!$bid) Utils::throwError('20003:请选择书籍');

        return DB::table('mp_book_version')->where('bid', $bid)->orderBy('id', 'desc')->select('bid', 'book_name', 'id as version_id', 'version_name')
        ->get()->map(function($value) {
            $value = (array)$value;
            $value['version_name'] = $value['book_name']."【{$value['version_name']}】";
            return $value;
        })->toArray();
    }

    public function overseasBookList($data) {
        $bid = getProp($data, 'bid');
        $book_name = getProp($data, 'book_name');
        $status = getProp($data, 'status');
        $page = getProp($data, 'page', 1);
        $per_page = getProp($data, 'pageSize', 15);

        $query = DB::table('mp_book_translation_push_records as a')->leftJoin('books as b', 'a.bid', '=', 'b.id')->leftJoin('book_configs as bc', 'b.id', '=', 'bc.bid')
        ->select('a.version_id as bid', 'a.version_name as book_name', 'b.status', 'bc.origin_book_name');

        if ($bid) {
            $query->where('a.version_id', $bid);
        }

        if ($book_name) {
            $query->where('bc.book_name', 'like', "%{$book_name}%");
        }

        if ($status !== '') {
            $query->where('b.status', $status);
        }

        $result = $query->orderBy('a.id')->paginate($per_page);

        return $result;
    }

    public function overseasBooks($data) {
        $bid = getProp($data, 'bid');
        $book_name = getProp($data, 'book_name');

        $query = DB::table('mp_book_translation_push_records')->select('version_id as bid', 'version_name as book_name');
        if ($bid) {
            $query->where('version_id', $bid);
        }

        if ($book_name) {
            $query->where('version_name', 'like', "%{$book_name}%");
        }

        return $query->orderBy('id', 'desc')->get()->map(function($value) {
            return (array)$value;
        })->toArray();
    }


    public function addBookVersion($data) {
        $bid = getProp($data, 'bid');
        if (!$bid) Utils::throwError('20003:请选择书籍');

        $version_name = trim(getProp($data, 'version_name'));
        if (!$version_name) Utils::throwError('20003:请填写版本名');

        // 兼容海外翻译小说
        if (strlen($bid) >= 8) {        // 海外翻译小说的书籍ID>=8位数(即AI翻译后台的版本ID,version_bid为真正的bid)
            $origin_bid = DB::table('mp_book_translation_version')->where('id', $bid)->value('bid');
            $book_name = DB::table('book_configs')->where('bid', $origin_bid)->value('book_name');

            $all_chapters = DB::table('chapters_ai_translation')->where('version_id', $bid)->where('bid', $origin_bid)->where('is_push', 1)->select('id', 'name', 'sequence', 'charge_length as size')->orderBy('sequence')->get();
        }else {
            $book_name = DB::table('book_configs')->where('bid', $bid)->value('book_name');
            if (DB::table('mp_book_version')->where('bid', $bid)->where('version_name', $version_name)->exists()) Utils::throwError('20003:版本名已存在');
            
            $all_chapters = DB::table('chapters')->where('bid', $bid)->where('is_check', 1)->where('is_deleted', 0)->select('id', 'name', 'sequence', 'size', 'chapter_content_id')->get();
        }

        try {
            DB::beginTransaction();

            $version_id = DB::table('mp_book_version')->insertGetId([
                'bid' => $bid,
                'book_name' => $book_name,
                'version_name' => $version_name,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ]);
            if (!$version_id) {
                DB::rollBack();
                Utils::throwError('20003:创建版本失败');
            }

            $insert_data = [];
            foreach ($all_chapters as $chapter) {
                $insert_data[] = [
                    'bid'                   => $bid,
                    'book_name'             => $book_name,
                    'version_id'            => $version_id,
                    'version_name'          => $version_name, 
                    'cid'                   => getProp($chapter, 'id'),
                    'chapter_name'          => getProp($chapter, 'name'),
                    'sequence'              => getProp($chapter, 'sequence'),
                    'size'                  => getProp($chapter, 'size'),
                    'generate_status'       => '待制作',
                    'created_at'            => date('Y-m-d H:i:s'),
                    'updated_at'            => date('Y-m-d H:i:s'),
                ];
            }

            $boolen = DB::table('mp_chapter_audios')->insert($insert_data);
            if (!$boolen) {
                DB::rollBack();
                Utils::throwError('20003:带入章节失败');
            }

        }catch (\Exception $e) {
            DB::rollBack();
            Utils::throwError('20003:'.$e->getMessage());

        }

        DB::commit();
        return [
            'bid'           => $bid,
            'version_id'    => $version_id,
            'version_name'  => $book_name."【{$version_name}】",
        ];
    }

    public function editBookVersion($data) {
        $version_id = getProp($data, 'version_id');
        $version_name = trim(getProp($data, 'version_name'));
        if (!$version_id) Utils::throwError('20003:请选择版本');
        if (!$version_name) Utils::throwError('20003:请填写版本名');

        DB::beginTransaction();
        $boolen = DB::table('mp_book_version')->where('id', $version_id)->update([
            'version_name' => $version_name,
            'updated_at' => date('Y-m-d H:i:s'),
        ]);
        if (!$boolen) {
            DB::rollBack();
            Utils::throwError('20003:修改书籍版本名称失败');
        }

        $boolen1 = DB::table('mp_chapter_audios')->where('version_id', $version_id)->update([
            'version_name' => $version_name,
            'updated_at' => date('Y-m-d H:i:s')
        ]);
        if (!$boolen1) {
            DB::rollBack();
            Utils::throwError('20003:修改章节版本名称失败');
        }

        DB::commit();
        return true;
    }

    public function getChapterList($data) {
        $bid = getProp($data, 'bid');
        $version_id = getProp($data, 'version_id');
        $sequence = getProp($data, 'sequence');
        $page = getProp($data, 'page', 1);
        $per_page = getProp($data, 'pageSize', 15);
        if (!$bid) Utils::throwError('20003:请选择书籍');
        if (!$version_id) Utils::throwError('20003:请选择版本');
        $generate_status = getProp($data, 'generate_status');

        if (strlen($bid) >= 8) {        // 兼容海外书籍
            $version_bid = DB::table('mp_book_translation_version')->where('id', $bid)->value('bid');
            $book = DB::table('books as b')->leftJoin('book_configs as bc', 'b.id', '=', 'bc.bid')->where('b.id', $version_bid)
            ->select('bc.book_name', 'bc.cover', 'b.size', 'b.chapter_count', 'b.intro')->first();
        }else {
            $book = DB::table('books as b')->leftJoin('book_configs as bc', 'b.id', '=', 'bc.bid')->where('b.id', $bid)
            ->select('bc.book_name', 'bc.cover', 'b.size', 'b.chapter_count', 'b.intro')->first();
        }
        if (!$book) Utils::throwError('20003:书籍不存在');
        $header = (array)$book;
        $header['cover'] = addPrefix($header['cover']);
        $header['intro'] = filterIntro($header['intro']);
        $role_info = DB::table('mp_book_version')->where('id', $version_id)->where('bid', $bid)->value('role_info');
        if ($role_info) {
            foreach(json_decode($role_info, true) as $role => $timbre) {
                // 获取音色信息
                $voice_type = getProp($timbre, 'timbre_type');
                $timbre_info = DB::table('mp_timbres')->where('timbre_type', $voice_type)->first();
                $header['role_info'][] = [
                    'role'                  => $role,
                    'voice_type'            => $voice_type,
                    'voice_name'            => getProp($timbre, 'timbre_name'),
                    'emotion'               => getProp($timbre, 'emotion'),
                    'emotion_type'          => getProp($timbre, 'emotion_type'),
                    'speed_ratio'           => getProp($timbre, 'speed_ratio'),
                    'loudness_ratio'        => getProp($timbre, 'loudness_ratio'),
                    'emotion_scale'         => getProp($timbre, 'emotion_scale'),
                    'pitch'                 => getProp($timbre, 'pitch'),
                    'language'              => getProp($timbre_info,'language'),
                    'dialect'               => getProp($timbre_info,'dialect'),
                    'emotion'               => getProp($timbre_info,'emotion'),
                    'first_category_id'     => getProp($timbre_info,'first_category_id'),
                    'first_category_name'   => getProp($timbre_info, 'first_category_name'),
                    'second_category_id'    => getProp($timbre_info,'second_category_id'),
                    'second_category_name'  => getProp($timbre_info, 'second_category_name'),
                    'third_category_id'     => getProp($timbre_info,'third_category_id'),
                    'third_category_name'   => getProp($timbre_info,'third_category_name'),
                    'audio_url'             => getProp($timbre_info,'audio_url'),
                    'label'                 => getProp($timbre_info, 'label'),
                ];
            }
        }else {
            $header['role_info'] = [];
        }
        $query = DB::table('mp_chapter_audios')->where('bid', $bid)->where('version_id', $version_id);
        if ($generate_status) {
            $query->where('generate_status', $generate_status);
        }
        if ($sequence) $query->where('sequence', $sequence);
        
        $list = $query->orderBy('sequence', 'asc')->paginate($per_page);

        return ['header' => $header, 'list' => $list];

    }

    public function getChapterContent($data) {
        $bid = getProp($data, 'bid');
        $cid = getProp($data, 'cid');

        if (!$cid) Utils::throwError('20003:请选择章节');
        if (strlen($bid) >= 8) {
            $content = DB::table('chapters_ai_translation')->where('version_id', $bid)->where('id', $cid)->value('translation');
        }else {
            $content = DB::table('chapters as c')->leftJoin('chapter_contents as cc', 'c.chapter_content_id', '=', 'cc.id')->where('c.id', $cid)->value('cc.content');
            $content = filterContent($content);
        }
        if (!$content) Utils::throwError('20003:章节内容不存在');

        return $content;
    }

    public function getVersionList($data) {
        $version_id = getProp($data, 'version_id');
        $version_name = getProp($data, 'version_name');
        $book_name = getProp($data, 'book_name');
        $page = getProp($data, 'page', 1);
        $per_page = getProp($data, 'pageSize', 15);

        $query = DB::table('mp_book_version as v')->leftJoin('book_configs as bc', 'v.bid', '=', 'bc.bid');
        if ($version_id) $query->where('v.id', $version_id);
        if ($version_name) $query->where('v.version_name', 'like', '%'.$version_name.'%');
        if ($book_name) $query->where('bc.book_name', 'like', '%'.$book_name.'%');
        $list = $query->select('v.*', 'bc.book_name')->paginate($per_page);

        return $list;
    }


    public function getTaskList($data) {
        $task_id = getProp($data, 'task_id');
        $task_name = getProp($data, 'task_name');
        $generate_status = getProp($data, 'generate_status');
        $page = getProp($data, 'page', 1);
        $per_page = getProp($data, 'pageSize', 15);

        $query = DB::table('mp_audio_tasks')->select('id', 'task_name', 'generate_status', 'bid', 'version_id', 'cid', 'created_at');
        if ($task_id) $query->where('id', $task_id);
        if ($task_name) $query->where('task_name', 'like', '%'.$task_name.'%');
        if ($generate_status) $query->where('generate_status',  $generate_status);
        $list = $query->orderBy('id', 'desc')->paginate($per_page);

        return $list;
    }

    public function getTaskDetail($data) {
        $task_id = getProp($data, 'task_id');

        $result = DB::table('mp_audio_tasks')->where('id', $task_id)->select('id as task_id', 'task_name', 'generate_status', 'generate_json', 'bid', 'version_id', 'cid')->first();
        if (!$result) Utils::throwError('20003:任务不存在');
        $result = (array)$result;
        $paragraph_arr = DB::table('mp_chapter_paragraph_audios')->where('bid', $result['bid'])->where('version_id', $result['version_id'])->where('cid', $result['cid'])->get();
        $list = [];
        foreach ($paragraph_arr as $key => $value) {
            $arr = (array)$value;
            $list[$arr['sequence']] = $arr;
        }
        $result['generate_json'] = json_decode($result['generate_json'], true);
        foreach ($result['generate_json'] as $key => &$value) {
            $sequence = getProp($value, 'sequence');
            $arr = isset($list[$sequence]) ? $list[$sequence] : [];
            if ($arr) {
                $value['role'] = $arr['role'];
                $value['text'] = $arr['text'];
                $value['voice_type'] = $arr['voice_type'];
                $value['speed_ratio'] = $arr['speed_ratio'];
                $value['loudness_ratio'] = $arr['loudness_ratio'];
                $value['emotion_scale'] = $arr['emotion_scale'];
            }
        }

        return $result;
    }

    public function audioEffectList($data) {
        $audio_effect_name = getProp($data, 'audio_effect_name');
        $page = getProp($data, 'page', 1);
        $per_page = getProp($data, 'pageSize', 15);
        
        $query = DB::table('mp_audio_effects')->where('is_enabled', 1);
        if ($audio_effect_name) $query->where('audio_effect_name', 'like', '%'.$audio_effect_name.'%');

        return $query->paginate($per_page);
    }

    public function audioEffects($data) {
        return DB::table('mp_audio_effects')->where('is_enabled', 1)->select('id as audio_effect_id', 'audio_effect_name', 'audio_effect_url', 'playtime_seconds')->get()->map(function ($value) {
            return (array)$value;
        })->toArray();
    }

    public function uploadAudioEffect($data, $file) {
        $audio_effect_name = getProp($data, 'audio_effect_name');

        if (!$file) Utils::throwError('20003:请上传音频文件');

        // 获取音频时长
        $filename = $file->getPathname();
        try {
            $getID3 = new getID3();
            $ThisFileInfo = $getID3->analyze($filename);
            $seconds = floor($ThisFileInfo['playtime_seconds']) ?? 0;
            if ($seconds <= 0) $seconds = 1;
        } catch (\Exception $e) {
            Log::info('音频文件解析失败: '.$e->getMessage());
            Utils::throwError('20003:音频文件解析失败');
        }

        // 验证文件格式(常见音频格式)
        if (!in_array($file->getClientOriginalExtension(), ['mp3', 'wav', 'flac', 'm4a', 'ogg', 'wma', 'cda', 'aiff', 'au', 'aac', 'amr'])) Utils::throwError('20003:音频格式错误');
        if (!$audio_effect_name) Utils::throwError('20003:请输入音频效果名称');
        if (DB::table('mp_audio_effects')->where('audio_effect_name', $audio_effect_name)->exists()) Utils::throwError('20003:音效名称已存在');
        
        // // 上传文件到oss
        // $audio_effect_url = uploadFile('audio_effects', $file, $audio_effect_name);
        // 上传文件到tos
        $audio_effect_url = uploadFileByTos('effects', $file, $audio_effect_name);

        if (!$audio_effect_url) Utils::throwError('20003:上传音频文件失败');

        $boolen = DB::table('mp_audio_effects')->insert([
            'audio_effect_name' => $audio_effect_name,
            'audio_effect_url'  => $audio_effect_url,
            'is_enabled'        => 1,
            'playtime_seconds'  => $seconds,
            'created_at'        => date('Y-m-d H:i:s'),
            'updated_at'        => date('Y-m-d H:i:s')
        ]);
        if (!$boolen) Utils::throwError('20003:添加音效失败');
        return ['audio_effect_name' => $audio_effect_name, 'audio_effect_url' => $audio_effect_url];
    }

    public function editAudioEffect($data, $file) {
        $audio_effect_id = getProp($data, 'audio_effect_id');
        if (!$audio_effect_id) Utils::throwError('20003:请选择音效');
        $audio_effect_name = getProp($data, 'audio_effect_name');
        $exists_id = DB::table('mp_audio_effects')->where('audio_effect_name', $audio_effect_name)->value('id');
        if ($exists_id && $exists_id != $audio_effect_id) Utils::throwError('20003:音效名称已存在');

        $update_data = [
            'audio_effect_name' => $audio_effect_name,
            'updated_at' => date('Y-m-d H:i:s')
        ];
        if ($file) {
            // 获取音频时长
            $filename = $file->getPathname();
            try {
                $getID3 = new getID3();
                $ThisFileInfo = $getID3->analyze($filename);
                $seconds = floor($ThisFileInfo['playtime_seconds']) ?? 0;
                if ($seconds <= 0) $seconds = 1;
            } catch (\Exception $e) {
                Log::info('音频文件解析失败: '.$e->getMessage());
                Utils::throwError('20003:音频文件解析失败');
            }
            $update_data['playtime_seconds'] = $seconds;

            // 验证文件格式(常见音频格式)
            if (!in_array($file->getClientOriginalExtension(), ['mp3', 'wav', 'flac', 'm4a', 'ogg', 'wma', 'cda', 'aiff', 'au', 'aac', 'amr'])) Utils::throwError('20003:音频格式错误');
            if (!$audio_effect_name) Utils::throwError('20003:请输入音频效果名称');
            if (DB::table('mp_audio_effects')->where('audio_effect_name', $audio_effect_name)->exists()) Utils::throwError('20003:音效名称已存在');
            
            // // 上传文件到oss
            // $audio_effect_url = uploadFile('audio_effects', $file, $audio_effect_name);
            // 上传文件到tos
            $audio_effect_url = uploadFileByTos('effects', $file, $audio_effect_name);

            if (!$audio_effect_url) Utils::throwError('20003:上传音频文件失败');
            $update_data['audio_effect_url'] = $audio_effect_url;
        }

        return DB::table('mp_audio_effects')->where('id', $audio_effect_id)->update($update_data);
    }

    public function bgmList($data) {
        $bgm_name = getProp($data, 'bgm_name');
        $page = getProp($data, 'page', 1);
        $per_page = getProp($data, 'pageSize', 15);
        
        $query = DB::table('mp_bgms')->where('is_enabled', 1);
        if ($bgm_name) $query->where('bgm_name', 'like', '%'.$bgm_name.'%');

        return $query->paginate($per_page);
    }

    public function bgms($data) {
        return DB::table('mp_bgms')->where('is_enabled', 1)->select('id as bgm_id', 'bgm_name', 'bgm_url', 'playtime_seconds')->get()->map(function ($value) {
            return (array)$value;
        })->toArray();
    }

    public function uploadBgm($data, $file) {
        $bgm_name = getProp($data, 'bgm_name');

        if (!$file) Utils::throwError('20003:请上传音频文件');

        // 获取音频时长
        $filename = $file->getPathname();
        try {
            $getID3 = new getID3();
            $ThisFileInfo = $getID3->analyze($filename);
            $seconds = floor($ThisFileInfo['playtime_seconds']) ?? 0;
            if ($seconds <= 0) $seconds = 1;
        } catch (\Exception $e) {
            Log::info('音频文件解析失败: '.$e->getMessage());
            Utils::throwError('20003:音频文件解析失败');
        }

        // 验证文件格式(常见音频格式)
        if (!in_array($file->getClientOriginalExtension(), ['mp3', 'wav', 'flac', 'm4a', 'ogg', 'wma', 'cda', 'aiff', 'au', 'aac', 'amr'])) Utils::throwError('20003:音频格式错误');
        if (!$bgm_name) Utils::throwError('20003:请输入音频效果名称');
        if (DB::table('mp_bgms')->where('bgm_name', $bgm_name)->exists()) Utils::throwError('20003:bgm名称已存在');
        
        // // 上传文件到oss
        // $bgm_url = uploadFile('bgms', $file, $bgm_name);
        // 上传文件到tos
        $bgm_url = uploadFileByTos('bgm', $file, $bgm_name);
        if (!$bgm_url) Utils::throwError('20003:上传音频文件失败');

        $boolen = DB::table('mp_bgms')->insert([
            'bgm_name' => $bgm_name,
            'bgm_url' => $bgm_url,
            'is_enabled' => 1,
            'created_at' => date('Y-m-d H:i:s'),
            'updated_at' => date('Y-m-d H:i:s')
        ]);
        if (!$boolen) Utils::throwError('20003:添加bgm失败');
        return ['bgm_name' => $bgm_name, 'bgm_url' => $bgm_url];
    }

    public function editBgm($data, $file) {
        $bgm_id = getProp($data, 'bgm_id');
        if (!$bgm_id) Utils::throwError('20003:请选择bgm');
        $bgm_name = getProp($data, 'bgm_name');
        $exists_id = DB::table('mp_bgms')->where('bgm_name', $bgm_name)->value('id');
        if ($exists_id && $exists_id != $bgm_id) Utils::throwError('20003:bgm名称已存在');

        $update_data = [
            'bgm_name'      => $bgm_name,
            'updated_at'    => date('Y-m-d H:i:s')
        ];
        if ($file) {
            // 获取音频时长
            $filename = $file->getPathname();
            try {
                $getID3 = new getID3();
                $ThisFileInfo = $getID3->analyze($filename);
                $seconds = floor($ThisFileInfo['playtime_seconds']) ?? 0;
                if ($seconds <= 0) $seconds = 1;
            } catch (\Exception $e) {
                Log::info('音频文件解析失败: '.$e->getMessage());
                Utils::throwError('20003:音频文件解析失败');
            }
            $update_data['playtime_seconds'] = $seconds;

            // 验证文件格式(常见音频格式)
            if (!in_array($file->getClientOriginalExtension(), ['mp3', 'wav', 'flac', 'm4a', 'ogg', 'wma', 'cda', 'aiff', 'au', 'aac', 'amr'])) Utils::throwError('20003:音频格式错误');
            if (!$bgm_name) Utils::throwError('20003:请输入音频效果名称');
            if (DB::table('mp_bgms')->where('bgm_name', $bgm_name)->exists()) Utils::throwError('20003:bgm名称已存在');
            
            // // 上传文件到oss
            // $bgm_url = uploadFile('bgms', $file, $bgm_name);
            // 上传文件到tos
            $bgm_url = uploadFileByTos('bgm', $file, $bgm_name);
            
            if (!$bgm_url) Utils::throwError('20003:上传音频文件失败');
            $update_data['bgm_url'] = $bgm_url;
        }

        return DB::table('mp_bgms')->where('id', $bgm_id)->update($update_data);
    }
}