drug-report/app/Console/Commands/Export/ExportQuestionBankDataCommand.php

<?php


namespace App\Console\Commands\Export;


use App\Servers\QuestionBank\QuestionChapterService;
use Illuminate\Console\Command;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;

class ExportQuestionBankDataCommand extends Command
{
    use ExportTrait;

    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'export:questionBankData';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '题库数据导出';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {

        $head = [
            '用户id',
            '手机号',
            '注册时间',
            '第一次学习时间',
            '第一天做题数量',
            '学习进度',
        ];

        //正式
        $dataBaseInfos = [
            //saas-company
            'company' => [
                'host' => '120.24.49.2',
                'port' => 3306,
                'db_name' => 'company_61',
                'db_username' => 'company_61',
                'db_password' => 'NKD4saLKN7zWKrzz'
            ]
        ];

        $companyConnectionConfig = [
            'driver' => 'mysql',
            'host' => $dataBaseInfos['company']['host'],
            'port' => $dataBaseInfos['company']['port'],
            'database' => $dataBaseInfos['company']['db_name'],
            'username' => $dataBaseInfos['company']['db_username'],
            'password' => $dataBaseInfos['company']['db_password'],
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'unix_socket' => env('DB_SOCKET', ''),
            'prefix' => env('DB_PREFIX', ''),
            'strict' => false,
            'sticky' => true,
            'engine' => null,
            'options' => [],
        ];

        Config::set("database.connections.company", $companyConnectionConfig);

        $tableNameCustomer = 'kailin_custom';

        $tableStudyProc    = 'kailin_question_point_study_progress';
        $tableAnswerTopic  = 'kailin_question_user_topic_answer_statistics';
        //$tableAnswerTopic  = 'kailin_question_user_topic_answer_record';
        $tableAnswerRealTopic  = 'kailin_question_user_real_topic_answer_record';

        $tableNameChapter = 'kailin_question_chapter';



        $directory = storage_path('exports/');
        $fileName  = '统计-'. Carbon::today()->toDateString();

        $this->createdFile($directory, $fileName);

        $this->putRow($head);

        $allUserInfos = DB::connection('company')->table($tableNameCustomer)->get(['uid', 'phone', 'insert_time'])->toArray();


        $dataCount = count($allUserInfos);
        $this->info('开始处理');
        $this->info("总共有：{$dataCount} 用户需要处理");
        $bar = $this->output->createProgressBar($dataCount);


        foreach ($allUserInfos as $allUserInfo) {

            $uid = $allUserInfo['uid'];
            //通过uid获取第一次的学习时间

            $firstStudyTime = '';

            $studyInfo =  DB::connection('company')->table($tableStudyProc)->where('user_id', $uid)->first();

            if ($studyInfo) {
                $firstStudyTime = $studyInfo['insert_time'];
            }

            $regDate = date("Y-m-d", $allUserInfo['insert_time']);

            $startTime = strtotime($regDate . " 00:00:00"); // 获取指定日期的开始时间戳
            $endTime = strtotime($regDate . " 23:59:59"); // 获取指定日期的结束时间戳

            //通过uid获取学员注册当天的答题数量

            $answerNum  = DB::connection('company')->table($tableAnswerTopic)
                ->where('user_id', $uid)
                ->where('insert_time', '>=', $startTime)
                ->where('insert_time', '<=', $endTime)
                ->count();

            //获取学员学习了考点的章节id
            $chapters = DB::connection('company')->table($tableNameChapter)->get(['id', 'parent_id'])->toArray();
            $questionChapterService = new QuestionChapterService();

//            $allSonChapterIds       = $questionChapterService->getSonChapters($chapters, $chapterId);


            $faguiChapterIds = $questionChapterService->getSonChapters($chapters, 3);
            array_unshift($faguiChapterIds, 3);//放到数组前面

            $y1ChapterIds = $questionChapterService->getSonChapters($chapters, 4);
            array_unshift($y1ChapterIds, 4);//放到数组前面

            $y2ChapterIds = $questionChapterService->getSonChapters($chapters, 5);
            array_unshift($y2ChapterIds, 5);//放到数组前面

            $yzChapterIds = $questionChapterService->getSonChapters($chapters, 6);
            array_unshift($yzChapterIds, 6);//放到数组前面

            $zy1ChapterIds = $questionChapterService->getSonChapters($chapters, 7);
            array_unshift($zy1ChapterIds, 7);//放到数组前面

            $zy2ChapterIds = $questionChapterService->getSonChapters($chapters, 8);
            array_unshift($zy2ChapterIds, 8);//放到数组前面

            $zyzChapterIds = $questionChapterService->getSonChapters($chapters, 9);
            array_unshift($zyzChapterIds, 9);//放到数组前面




            $resultChapterIds =  DB::connection('company')->table($tableStudyProc)->where('user_id', $uid)->orderBy('chapter_id', 'asc')->pluck('chapter_id')->toArray();

            $resultChapterIds = array_values(array_unique($resultChapterIds));

            $needExecuteFgChapterIds = [];
            $needExecuteY1ChapterIds = [];
            $needExecuteY2ChapterIds = [];
            $needExecuteYzChapterIds = [];
            $needExecuteZy1ChapterIds = [];
            $needExecuteZy2ChapterIds = [];
            $needExecuteZyzChapterIds = [];

            if (!empty($resultChapterIds)) {

                foreach ($resultChapterIds as $resultChapterId) {
                    if (in_array($resultChapterId, $faguiChapterIds)) {
                        $needExecuteFgChapterIds [] =  $resultChapterId;
                    } elseif (in_array($resultChapterId, $y1ChapterIds)) {
                        $needExecuteY1ChapterIds [] = $resultChapterId;
                    } elseif (in_array($resultChapterId, $y2ChapterIds)) {
                        $needExecuteY2ChapterIds [] = $resultChapterId;
                    } elseif (in_array($resultChapterId, $yzChapterIds)) {
                        $needExecuteYzChapterIds [] = $resultChapterId;
                    } elseif (in_array($resultChapterId, $zy1ChapterIds)) {
                        $needExecuteZy1ChapterIds [] = $resultChapterId;
                    } elseif (in_array($resultChapterId, $zy2ChapterIds)) {
                        $needExecuteZy2ChapterIds [] = $resultChapterId;
                    } elseif (in_array($resultChapterId, $zyzChapterIds)) {
                        $needExecuteZyzChapterIds [] = $resultChapterId;
                    }
                }

                $study_proc = '';

                if (!empty($needExecuteFgChapterIds)) {
                    $needChapterFgId = end($needExecuteFgChapterIds);
                    $needChapterFgId = $this->getChapterName($needChapterFgId, $tableNameChapter);
                    $study_proc = $needChapterFgId;
                }

                if (!empty($needExecuteY1ChapterIds)) {
                    $needChapterY1Id = end($needExecuteY1ChapterIds);
                    $needChapterY1Id = $this->getChapterName($needChapterY1Id, $tableNameChapter);
                    if (!empty($study_proc)) {
                        $study_proc = $study_proc . ','. $needChapterY1Id;
                    } else {
                        $study_proc = $needChapterY1Id;
                    }
                }

                if (!empty($needExecuteY2ChapterIds)) {
                    $needChapterY2Id = end($needExecuteY2ChapterIds);
                    $needChapterY2Id = $this->getChapterName($needChapterY2Id, $tableNameChapter);
                    if (!empty($study_proc)) {
                        $study_proc = $study_proc . ','. $needChapterY2Id;
                    } else {
                        $study_proc = $needChapterY2Id;
                    }
                }

                if (!empty($needExecuteYzChapterIds)) {
                    $needChapterYzId = end($needExecuteYzChapterIds);
                    $needChapterYzId = $this->getChapterName($needChapterYzId, $tableNameChapter);
                    if (!empty($study_proc)) {
                        $study_proc = $study_proc . ','. $needChapterYzId;
                    } else {
                        $study_proc = $needChapterYzId;
                    }
                }

                if (!empty($needExecuteZy1ChapterIds)) {
                    $needChapterZy1Id = end($needExecuteZy1ChapterIds);
                    $needChapterZy1Id = $this->getChapterName($needChapterZy1Id, $tableNameChapter);
                    if (!empty($study_proc)) {
                        $study_proc = $study_proc . ','. $needChapterZy1Id;
                    } else {
                        $study_proc = $needChapterZy1Id;
                    }
                }

                if (!empty($needExecuteZy2ChapterIds)) {
                    $needChapterZy2Id = end($needExecuteZy2ChapterIds);
                    $needChapterZy2Id = $this->getChapterName($needChapterZy2Id, $tableNameChapter);
                    if (!empty($study_proc)) {
                        $study_proc = $study_proc . ','. $needChapterZy2Id;
                    } else {
                        $study_proc = $needChapterZy2Id;
                    }
                }

                if (!empty($needExecuteZy2ChapterIds)) {
                    $needChapterZyzId = end($needExecuteZy2ChapterIds);
                    $needChapterZyzId = $this->getChapterName($needChapterZyzId, $tableNameChapter);
                    if (!empty($study_proc)) {
                        $study_proc = $study_proc . ','. $needChapterZyzId;
                    } else {
                        $study_proc = $needChapterZyzId;
                    }
                }


                //$study_proc = implode(',', $resultChapterIds);
            } else {
                $study_proc = '';
            }



            $exportData = [];
            $exportData['uid'] = $uid;
            $exportData['phone'] = $allUserInfo['phone'];
            $exportData['reg_date'] = date("Y-m-d H:i:s", $allUserInfo['insert_time']) ;
            $exportData['first_study_date'] = !empty($firstStudyTime) ? date("Y-m-d H:i:s", $firstStudyTime) : '';
            $exportData['first_answer_num'] = $answerNum;
            $exportData['study_proc'] = $study_proc;

            $this->putRow($exportData);

            $bar->advance();
        }

        $bar->finish();

        $this->info('');
        $this->info('导出完成');


    }

    public function getChapterName($chapterId, $tableNameChapter)
    {
        $infos = DB::connection('company')->table($tableNameChapter)->where('id', $chapterId)->first();

        $baseName = $infos['name'];
        $level    = $infos['level'];

        if ($level == 3) {
            //父级id也就是第二级的id
            $thirdChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $chapterId)->value('parent_id');
            $secondLevelName = DB::connection('company')->table($tableNameChapter)->where('id', $thirdChapterParentId)->value('name');
            //dd($secondLevelName . '-' . $baseName);
            return $secondLevelName . '->' . $baseName;
        } elseif ($level == 4) {
            //父级id也就是第三级的id
            $fourthLevelChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $chapterId)->value('parent_id');
            $thirdLevelInfo = DB::connection('company')->table($tableNameChapter)->where('id', $fourthLevelChapterParentId)->first();
            $thirdLevelName = $thirdLevelInfo['name'];
            $thirdLevelId = $thirdLevelInfo['id'];
            //父级id也就是第二级的id
            $thirdLevelChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $thirdLevelId)->value('parent_id');

            $secondLevelName = DB::connection('company')->table($tableNameChapter)->where('id', $thirdLevelChapterParentId)->value('name');
            //dd($secondLevelName . '--' . $thirdLevelName .'--'. $baseName);
            return $secondLevelName . '->' . $thirdLevelName .'->'. $baseName;

        } elseif ($level == 5) {
            $fifthChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $chapterId)->value('parent_id');
            $fourthLevelInfo = DB::connection('company')->table($tableNameChapter)->where('id', $fifthChapterParentId)->first();
            $fourthLevelName = $fourthLevelInfo['name'];
            $fourthLevelId = $fourthLevelInfo['id'];

            $fourthChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $fourthLevelId)->value('parent_id');

            $thirdLevelInfo = DB::connection('company')->table($tableNameChapter)->where('id', $fourthChapterParentId)->first();
            $thirdLevelName = $thirdLevelInfo['name'];
            $thirdLevelId = $thirdLevelInfo['id'];

            $thirdChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $thirdLevelId)->value('parent_id');

            $secondLevelName = DB::connection('company')->table($tableNameChapter)->where('id', $thirdChapterParentId)->value('name');
            //dd($secondLevelName . '-' . $thirdLevelName . '-'. $fourthLevelName . '-' .  $baseName);
            return $secondLevelName . '->' . $thirdLevelName . '->'. $fourthLevelName . '->' .  $baseName;
        } elseif ($level == 6) {
            $sixChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $chapterId)->value('parent_id');

            $fifthLevelInfo = DB::connection('company')->table($tableNameChapter)->where('id', $sixChapterParentId)->first();

            $fifthLevelName = $fifthLevelInfo['name'];
            $fifthLevelId = $fifthLevelInfo['id'];

            $fifthChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $fifthLevelId)->value('parent_id');

            $fourthLevelInfo = DB::connection('company')->table($tableNameChapter)->where('id', $fifthChapterParentId)->first();
            $fourthLevelName = $fourthLevelInfo['name'];
            $fourthLevelId = $fourthLevelInfo['id'];

            $fourthChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $fourthLevelId)->value('parent_id');

            $thirdLevelInfo = DB::connection('company')->table($tableNameChapter)->where('parent_id', $fourthChapterParentId)->first();
            $thirdLevelName = $thirdLevelInfo['name'];
            $thirdLevelId = $thirdLevelInfo['id'];
            $thirdChapterParentId = DB::connection('company')->table($tableNameChapter)->where('id', $thirdLevelId)->value('parent_id');
            $secondLevelName = DB::connection('company')->table($tableNameChapter)->where('id', $thirdChapterParentId)->value('name');
//dd($secondLevelName . '-' . $thirdLevelName . '-'. $fourthLevelName . '-' . $fifthLevelName . '-' . $baseName);
            return $secondLevelName . '->' . $thirdLevelName . '->'. $fourthLevelName . '->' . $fifthLevelName . '->' . $baseName;
        }


    }

}