yc_douyin_capi/app/Console/Sync/SyncChapters.php

<?php

namespace App\Console\Sync;

use App\Libs\BatchUpdateTrait;
use App\Models\Book\Book;
use App\Models\Book\Chapter;
use App\Models\Book\ChapterContent;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class SyncChapters extends Command
{
    use BatchUpdateTrait;

    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'sync:chapters {--bids=}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '从内容平台同步章节';

    public function handle()
    {
        // 传参
        $bids = $this->option('bids');
        $bids = filterValidIds(explode(',', $bids));
        dLog('sync')->info('sync chapters start...');

        // 获取书籍列表
        $books = $this->getBooks($bids);

        // 执行更新
        $this->runSync($books);
        dLog('sync')->info('sync chapters end...');
    }

    /**
     * @param $bids
     * @return mixed
     */
    private function getBooks($bids)
    {
        // 组装查询条件
        $query = Book::select('id', 'zw_id')->where('zw_id', '>', 0);
        if ($bids) {
            $query->whereIn('id', $bids);
        } else {
            // 连载
            $query->where('status', 0);
        }

        // 查询
        return $query->orderBy('id')->get();
    }

    /**
     * @param $books
     * @return void
     */
    private function runSync($books): void
    {
        // 判空
        if ($books->isEmpty()) {
            return;
        }

        // 循环执行
        foreach ($books as $book) {
            // 这本书有点问题
            if (in_array($book->zw_id, [141873, 125717])) {
                continue;
            }

            dLog('sync')->info('sync book chapters start: ', [$book->id, $book->zw_id]);

            // 同步章节
            $this->syncChapters($book->id, $book->zw_id);

            dLog('sync')->info('sync book chapters end: ', [$book->id, $book->zw_id]);

            // 执行更新书籍数据脚本
            $this->call('book:after:spider', ['--bid' => $book->id]);
        }
    }

    /**
     * 制作新增章节的同步
     *
     * @param $bid
     * @param $zwBid
     * @return void
     */
    private function syncChapters($bid, $zwBid)
    {
        // 判空
        if (empty($bid) || empty($zwBid)) {
            return;
        }

        // 获取内容平台书籍所有章节列表
        $zwChapters = $this->getZwChapters($zwBid);
        if (empty($zwChapters)) {
            return;
        }

        // 获取抖音平台书籍所有章节（及内容）列表
        $chapters        = Chapter::where('bid', $bid)->get();
        $chapterContents = ChapterContent::select('id', 'bid', 'zw_bid', 'zw_chapter_id')
                                         ->where('bid', $bid)
                                         ->where('zw_chapter_id', '>', 0)
                                         ->get();

        // 校验章节数据
        $diffData = $this->getDiffChapters($bid, $zwBid, $zwChapters, $chapters, $chapterContents);

        // 写入新章节
        $chaptersInData = getProp($diffData, 'chaptersInData', []);
        if ($chaptersInData) {
            Chapter::insert($chaptersInData);
        }

        // 更新章节
        $chaptersUpData = getProp($diffData, 'chaptersUpData', []);
        if ($chaptersInData) {
            $this->batchUpdateDb('chapters', $chaptersUpData);
        }

        // 写入新章节内容（内容数据较大，分批获取）
        $newZwChaptersContentIds = getProp($diffData, 'newZwChaptersContentIds', []);
        $zwChaptersContentData   = getProp($diffData, 'zwChaptersContentData', []);
        $this->saveNewChaptersContent($newZwChaptersContentIds, $bid, $zwBid, $zwChaptersContentData);

        //更新章节的content_chapter_id
        $this->updateChaptersContentId($bid);
    }

    /**
     * 书籍章节比较
     *
     * @param $bid
     * @param $zwBid
     * @param $zwChapters
     * @param $chapters
     * @param $chapterContents
     * @return array
     */
    private function getDiffChapters($bid, $zwBid, $zwChapters, $chapters, $chapterContents): array
    {
        if (empty($zwChapters)) {
            return [];
        }

        // 循环章节数据
        $chaptersData = [];
        if ($chapters) {
            foreach ($chapters as $chapter) {
                $id          = (int)getProp($chapter, 'id');
                $zwChapterId = (int)getProp($chapter, 'zw_chapter_id');

                // 当前已存在的章节
                $chaptersData[$zwChapterId] = ['id' => $id];
            }
        }

        // 循环章节内容表
        $chapterContentsData = [];
        if ($chapterContents) {
            foreach ($chapterContents as $chapterContent) {
                $bid         = (int)getProp($chapterContent, 'bid');
                $zwChapterId = (int)getProp($chapterContent, 'zw_chapter_id');
                $zwBid       = (int)getProp($chapterContent, 'zw_bid');

                // 当前已存在的章节内容
                $chapterContentsData[$zwChapterId] = ['bid' => $bid, 'zw_bid' => $zwBid];
            }
        }

        // 循环内容平台章节列表数据
        $now            = date('Y-m-d H:i:s');
        $chaptersUpData = $chaptersInData = $newZwChaptersContentIds = $zwChaptersContentData = [];
        foreach ($zwChapters as $zwChapter) {
            // 内容平台章节id
            $zwChapterId        = (int)getProp($zwChapter, 'id');
            $zwChapterContentId = (int)getProp($zwChapter, 'chapter_content_id');
            $name               = getProp($zwChapter, 'name');

            // 章节基本数据，为了同步章节内容时用
            $zwChaptersContentData[$zwChapterContentId] = [
                'bid'           => $bid,
                'chapter_name'  => $name,
                'zw_bid'        => $zwBid,
                'zw_chapter_id' => $zwChapterId,
            ];

            // 基本数据
            $item = [
                'bid'              => $bid,
                'name'             => $name,
                'sequence'         => (int)getProp($zwChapter, 'sequence'),
                'size'             => (int)getProp($zwChapter, 'size'),
                'is_vip'           => (int)getProp($zwChapter, 'is_vip'),
                'is_check'         => 1,
                'is_deleted'       => (int)getProp($zwChapter, 'is_deleted'),
                'zw_bid'           => $zwBid,
                'zw_chapter_id'    => $zwChapterId,
                'recent_update_at' => $now,
                'post_time'        => $now,
                'check_time'       => $now,
                'created_at'       => $now,
                'updated_at'       => $now,
            ];

            // 区分章节写入还是更新
            if (!isset($chaptersData[$zwChapterId])) {
                $chaptersInData[] = $item;
            } else {
                // 去掉无需更新的字段
                unset($item['bid'], $item['zw_bid'], $item['zw_chapter_id'], $item['recent_update_at'],
                    $item['post_time'], $item['check_time'], $item['created_at']);

                // 加上当前章节id
                $item['id']       = (int)getProp($chaptersData[$zwChapterId], 'id');
                $chaptersUpData[] = $item;
            }

            // 区分章节内容写入还是更新
            if (!isset($chapterContentsData[$zwChapterId])) {
                $newZwChaptersContentIds[] = $zwChapterContentId;
            }
        }

        return compact('chaptersUpData', 'chaptersInData', 'newZwChaptersContentIds', 'zwChaptersContentData');
    }

    /**
     * 写入章节内容
     *
     * @param $zwChaptersContentIds
     * @param $bid
     * @param $zwBid
     * @param $zwChaptersContentData
     * @return void
     */
    private function saveNewChaptersContent($zwChaptersContentIds, $bid, $zwBid, $zwChaptersContentData)
    {
        if (empty($zwChaptersContentIds)) {
            return;
        }

        // 当前时间
        $now = date('Y-m-d H:i:s');

        // 切分id
        $zwChapterContentIdsArr = array_chunk($zwChaptersContentIds, 100);

        // 循环切分数据
        foreach ($zwChapterContentIdsArr as $contentIds) {

            // 获取内容平台章节内容
            $zwChapterContents = $this->getZwChaptersContent($contentIds);
            if (empty($zwChapterContents)) {
                continue;
            }

            // 组装写入数据
            $chaptersContentInsertData = [];
            foreach ($zwChapterContents as $zwChapterContent) {
                $zwChapterContentId   = (int)getProp($zwChapterContent, 'id');
                $zwChapterContentData = getProp($zwChaptersContentData, $zwChapterContentId, []);

                // 组装写入数据
                $chaptersContentInsertData[] = [
                    'bid'           => $bid,
                    'zw_bid'        => $zwBid,
                    'zw_chapter_id' => (int)getProp($zwChapterContentData, 'zw_chapter_id'),
                    'chapter_name'  => getProp($zwChapterContentData, 'chapter_name'),
                    'content'       => formatContent(getProp($zwChapterContent, 'content')),
                    'created_at'    => $now,
                    'updated_at'    => $now,
                ];
            }

            // 执行写入
            ChapterContent::insert($chaptersContentInsertData);
        }
    }

    /**
     * 更新章节chapter_content_id
     *
     * @param $bid
     * @return void
     */
    private function updateChaptersContentId($bid)
    {
        if (empty($bid)) {
            return;
        }

        // 获取新章节
        $chapters        = $this->getChaptersByBid($bid);
        $chapterContents = $this->getChapterContentsByBid($bid);
        if (empty($chapters) || empty($chapterContents)) {
            return;
        }

        $chaptersData        = $this->buildChaptersData($chapters);
        $chapterContentsData = $this->buildChaptersData($chapterContents);

        // 组装更新数据-更新章节表chapter_content_id
        $chaptersUpdateData = [];
        foreach ($chaptersData as $key => $value) {
            $chapterContent       = getProp($chapterContentsData, $key, []);
            $chaptersUpdateData[] = [
                'id'                 => getProp($value, 'id'),
                'chapter_content_id' => (int)getProp($chapterContent, 'id'),
            ];
        }

        // 执行批量更新
        $this->batchUpdateDb('chapters', $chaptersUpdateData);
    }

    /**
     * @param $chapters
     * @return array
     */
    private function buildChaptersData($chapters): array
    {
        if (empty($chapters)) {
            return [];
        }

        $result = [];
        foreach ($chapters as $chapter) {
            $bid         = getProp($chapter, 'bid');
            $zwBid       = getProp($chapter, 'zw_bid');
            $zwChapterId = getProp($chapter, 'zw_chapter_id');
            $key         = $bid . '_' . $zwBid . '_' . $zwChapterId;
            if (!isset($result[$key])) {
                $result[$key] = $chapter;
            }
        }

        return $result;
    }

    /**
     * @param $bid
     * @return array
     */
    private function getChaptersByBid($bid): array
    {
        if (empty($bid)) {
            return [];
        }

        $chapters = Chapter::select('id', 'bid', 'zw_bid', 'zw_chapter_id')->where('bid', $bid)->get();
        return $chapters ? $chapters->toArray() : [];
    }

    /**
     * @param $bid
     * @return array
     */
    private function getChapterContentsByBid($bid): array
    {
        if (empty($bid)) {
            return [];
        }

        $chapters = ChapterContent::select('id', 'bid', 'zw_bid', 'zw_chapter_id')->where('bid', $bid)->get();
        return $chapters ? $chapters->toArray() : [];
    }

    /**
     * 查询内容平台章节列表
     *
     * @param $bid
     * @return array
     */
    private function getZwChapters($bid): array
    {
        if (empty($bid)) {
            return [];
        }

        $result = DB::connection('zw_content_mysql')
                    ->table('chapters')
                    ->select('id', 'bid', 'name', 'sequence', 'size', 'is_vip', 'chapter_content_id', 'is_deleted', 'is_draft')
                    ->where('bid', $bid)
//                    ->where('is_draft', 0)
//                    ->where('is_check', 1)
//                    ->where('is_deleted', 0)
                    ->orderBy('sequence')
                    ->get();
        return $result ? $result->toArray() : [];
    }

    /**
     * @param $zwChapterContentIds
     * @return array
     */
    private function getZwChaptersContent($zwChapterContentIds): array
    {
        if (empty($zwChapterContentIds)) {
            return [];
        }

        $result = DB::connection('zw_content_mysql')
                    ->table('chapter_contents')
                    ->select('id', 'content')
                    ->whereIn('id', $zwChapterContentIds)
                    ->get();
        return $result ? $result->toArray() : [];
    }
}