test/app/Http/Controllers/Admin/SocialStatisticsController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Logics\Admin\SocialLogic;
use App\Http\Models\HootsuiteHistory;
use App\Http\Models\HootsuiteUser;
use App\Http\Models\PrSocial;
use App\Http\Models\Site;
use App\Http\Models\Social;
use Facebook\Facebook;
use GuzzleHttp\Client;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Laravel\Socialite\Facades\Socialite;

class SocialStatisticsController extends Controller
{
    public function socialStatistics($siteId)
    {
        $site = Site::query()->where('id', $siteId)->first();

        $startDate = HootsuiteHistory::query()->where('site_id', $siteId)->orderBy('publish_at')->value('publish_at') ?? date('Y-m-01 00:00:00', strtotime('first day of -1 month'));

        $ym = date('Y-m-01 00:00:00', strtotime($startDate));
        $ym2 = date('Y-m-t 23:59:59', strtotime('first day of -1 month'));

        $this->getSitePublishStatus($siteId, $ym, $ym2);

        $date1 = date('Y-m', strtotime('first day of -3 month'));
        $date2 = date('Y-m', strtotime('first day of -2 month'));
        $date3 = date('Y-m', strtotime('first day of -1 month'));

        $twitterCountList = $this->getTwitterCountList($siteId, $ym, $ym2, $date1, $date2, $date3);
        $facebookCountList = $this->getFacebookCountList($siteId, $ym, $ym2, $date1, $date2, $date3);
        $linkedInCountList = $this->getLinkedInCountList($siteId, $ym, $ym2, $date1, $date2, $date3);
        $pinCountList = $this->getPinCountList($siteId, $ym, $ym2, $date1, $date2, $date3);
        $instagramCountList = $this->getInstagramCount($siteId, $ym, $ym2, $date1, $date2, $date3);
        $youtubeCountList = $this->getYoutubeCountList($siteId, $ym, $ym2, $date1, $date2, $date3);

        $dataList = [
            [
                'type' => 'twitter',
                'data' => [
                    'data_list' => $twitterCountList,
                    'hide' => 0,
                ]
            ],
            [
                'type' => 'facebook',
                'data' => [
                    'data_list' => $facebookCountList,
                    'hide' => 0,
                ]
            ],
            [
                'type' => 'linkedIn',
                'data' => [
                    'data_list' => $linkedInCountList,
                    'hide' => 0,
                ]
            ],

            [
                'type' => 'pin',
                'data' => [
                    'data_list' => $pinCountList,
                    'hide' => 0,
                ]
            ],
            [
                'type' => 'ins',
                'data' => [
                    'data_list' => $instagramCountList,
                    'hide' => 0,
                ]
            ],
            [
                'type' => 'youtube',
                'data' => [
                    'data_list' => $youtubeCountList,
                    'hide' => 0,
                ]
            ],
        ];
        $lastMonth = date('Ym', strtotime('first day of -1 month'));

        foreach ($dataList as $item) {
            if (PrSocial::query()->where(['site_id' => $site->id, 'ym' => $lastMonth])->where(['type' => $item['type']])->exists()) {
                PrSocial::query()->where(['site_id' => $site->id, 'ym' => $lastMonth])->where(['type' => $item['type']])->update([
                    'data_list' => json_encode($item['data']['data_list']),
                    'hide' => $item['data']['hide'],
                ]);
            } else {
                PrSocial::query()->create([
                    'ym' => $lastMonth,
                    'type' => $item['type'],
                    'site_id' => $site->id,
                    'old_id' => $site->old_id,
                    'data_list' => $item['data']['data_list'],
                    'hide' => $item['data']['hide']
                ]);
            }
        }
        $this->saveNextSocial($site, $dataList);
        return response()->json(['message' => '操作成功']);
    }

    public function getSitePublishStatus($siteId, $ym, $ym2)
    {
        $list = DB::table('hootsuite_history')
            ->where('site_id', $siteId)
            ->select('id', 'site_id', 'url', 'publish_at', 'social_ids')
            ->whereBetween('publish_at', [$ym, [$ym2]])
            ->get();

        foreach ($list as $key => $value) {

            $socialIdList = json_decode($value->social_ids);
            if (empty($socialIdList)) {
                $twitter = strstr($value->url, 'twitter');
                if (!empty($twitter)) {
                    $value->is_twitter = 1;
                }
                $facebook = strstr($value->url, 'facebook');
                if (!empty($facebook)) {
                    $value->is_facebook = 1;
                }
                $linkEdin = strstr($value->url, 'linkedin');
                if (!empty($linkEdin)) {
                    $value->is_linkedIn = 1;
                }
                $pin = strstr($value->url, 'pin');
                if (!empty($pin)) {
                    $value->is_pin = 1;
                }
                $youtube = strstr($value->url, 'youtube');
                if (!empty($youtube)) {
                    $value->is_youtube = 1;
                }
                $instagram = strstr($value->url, 'instagram');
                if (!empty($instagram)) {
                    $value->is_instagram = 1;
                }
            } else {
                $result = $this->getSocialId($value->site_id, $socialIdList);
                foreach ($result as $kk => $vv) {
                    if ($vv == 'TWITTER') {
                        $value->is_twitter = 1;
                    }
                    if ($vv == 'FACEBOOKPAGE') {
                        $value->is_facebook = 1;
                    }
                    if ($vv == 'LINKEDINCOMPANY') {
                        $value->is_linkedIn = 1;
                    }
                    if ($vv == 'PINTEREST') {
                        $value->is_pin = 1;
                    }
                }
            }
            $update = [];
            if (!empty($value->is_twitter)) {
                $update['is_twitter'] = $value->is_twitter ?? 0;
            }
            if (!empty($value->is_facebook)) {
                $update['is_facebook'] = $value->is_facebook ?? 0;
            }
            if (!empty($value->is_linkedIn)) {
                $update['is_linkedIn'] = $value->is_linkedIn ?? 0;
            }
            if (!empty($value->is_pin)) {
                $update['is_pin'] = $value->is_pin ?? 0;
            }
            if (!empty($value->is_youtube)) {
                $update['is_youtube'] = $value->is_youtube ?? 0;
            }
            if (!empty($value->is_instagram)) {
                $update['is_instagram'] = $value->is_instagram ?? 0;
            }

            if (!empty($update)) {
                DB::table('hootsuite_history')->where('id', $value->id)->update($update);
            }
        }
    }

    public function getSocialId($siteId, $socialIdsList)
    {
        $array = [];
        $hootSuiteUser = HootsuiteUser::query()->where(['site_id' => $siteId])->first();
        $socialProfiles = $hootSuiteUser->social_profiles ?? [];
        foreach ($socialProfiles as $key => $value) {
            foreach ($socialIdsList as $kk => $vv) {
                if ($value['id'] == $vv) {
                    $array[] = $value['type'];
                }
            }
        }

        return $array;
    }

    public function getTwitterCountList($siteId, $ym, $ym2, $date1, $date2, $date3)
    {
        $list1 = DB::table('hootsuite_history')
            ->where('site_id', $siteId)
            ->where('is_twitter', 1)
            ->whereBetween('publish_at', [$ym, $ym2])
            ->selectRaw('DATE_FORMAT(publish_at,"%Y-%m") as date, count(*) as twitter_count,site_id')
            ->groupBy('date', 'site_id')->get()->toArray();

        $twitterCount = [null, null, null];
        if (empty($list1)) {
            return $twitterCount;
        }

        $dateList = $this->getDateList($ym);

        foreach ($dateList as $key => $value) {
            $sum = 0;
            foreach ($list1 as $kk => $vv) {
                if (strtotime($value['date']) >= strtotime($vv->date)) {
                    $sum += $vv->twitter_count;
                }
                $dateList[$key]['sum'] = $sum;
            }
        }

        foreach ($dateList as $key => $value) {
            if ($value['date'] == $date1) {
                $twitterCount[2] = $value['sum'];
            }
            if ($value['date'] == $date2) {
                $twitterCount[1] = $value['sum'];
            }
            if ($value['date'] == $date3) {
                $twitterCount[0] = $value['sum'];
            }
        }
        return $twitterCount;
    }

    public function getFacebookCountList($siteId, $ym, $ym2, $date1, $date2, $date3)
    {
        $list2 = DB::table('hootsuite_history')
            ->where('site_id', $siteId)
            ->where('is_facebook', 1)
            ->whereBetween('publish_at', [$ym, $ym2])
            ->selectRaw('DATE_FORMAT(publish_at,"%Y-%m") as date, count(*) as facebook_count,site_id')
            ->groupBy('date', 'site_id')->get()->toArray();

        $facebookCount = [null, null, null];

        if (empty($list2)) {
            return $facebookCount;
        }

        $dateList = $this->getDateList($ym);

        foreach ($dateList as $key => $value) {
            $sum = 0;
            foreach ($list2 as $kk => $vv) {
                if (strtotime($value['date']) >= strtotime($vv->date)) {
                    $sum += $vv->facebook_count;
                }
                $dateList[$key]['sum'] = $sum;
            }
        }

        foreach ($dateList as $key => $value) {
            if ($value['date'] == $date1) {
                $facebookCount[2] = $value['sum'];
            }
            if ($value['date'] == $date2) {
                $facebookCount[1] = $value['sum'];
            }
            if ($value['date'] == $date3) {
                $facebookCount[0] = $value['sum'];
            }
        }

        return $facebookCount;
    }

    public function getLinkedInCountList($siteId, $ym, $ym2, $date1, $date2, $date3)
    {
        $list3 = DB::table('hootsuite_history')
            ->where('site_id', $siteId)
            ->where('is_linkedIn', 1)
            ->whereBetween('publish_at', [$ym, $ym2])
            ->selectRaw('DATE_FORMAT(publish_at,"%Y-%m") as date, count(*) as linkedIn_count,site_id')
            ->groupBy('date', 'site_id')->get()->toArray();

        $linkedInCount = [null, null, null];

        if (empty($list3)) {
            return $linkedInCount;
        }

        $dateList = $this->getDateList($ym);

        foreach ($dateList as $key => $value) {
            $sum = 0;

            foreach ($list3 as $kk => $vv) {
                if (strtotime($value['date']) >= strtotime($vv->date)) {
                    $sum += $vv->linkedIn_count;
                }
                $dateList[$key]['sum'] = $sum;
            }
        }

        foreach ($dateList as $key => $value) {
            if ($value['date'] == $date1) {
                $linkedInCount[2] = $value['sum'];
            }
            if ($value['date'] == $date2) {
                $linkedInCount[1] = $value['sum'];
            }
            if ($value['date'] == $date3) {
                $linkedInCount[0] = $value['sum'];
            }
        }

        return $linkedInCount;
    }

    public function getPinCountList($siteId, $ym, $ym2, $date1, $date2, $date3)
    {
        $list4 = DB::table('hootsuite_history')
            ->where('site_id', $siteId)
            ->where('is_pin', 1)
            ->whereBetween('publish_at', [$ym, $ym2])
            ->selectRaw('DATE_FORMAT(publish_at,"%Y-%m") as date, count(*) as pin_count,site_id')
            ->groupBy('date', 'site_id')->get()->toArray();

        $pinCount = [null, null, null];
        if (empty($list4)) {
            return $pinCount;
        }

        $dateList = $this->getDateList($ym);

        foreach ($dateList as $key => $value) {
            $sum = 0;

            foreach ($list4 as $kk => $vv) {
                if (strtotime($value['date']) >= strtotime($vv->date)) {
                    $sum += $vv->pin_count;
                }
                $dateList[$key]['sum'] = $sum;
            }
        }
        foreach ($dateList as $key => $value) {
            if ($value['date'] == $date1) {
                $pinCount[2] = $value['sum'];
            }
            if ($value['date'] == $date2) {
                $pinCount[1] = $value['sum'];
            }
            if ($value['date'] == $date3) {
                $pinCount[0] = $value['sum'];
            }
        }

        return $pinCount;
    }

    public function getYoutubeCountList($siteId, $ym, $ym2, $date1, $date2, $date3)
    {
        $list5 = DB::table('hootsuite_history')
            ->where('site_id', $siteId)
            ->where('is_youtube', 1)
            ->whereBetween('publish_at', [$ym, $ym2])
            ->selectRaw('DATE_FORMAT(publish_at,"%Y-%m") as date, count(*) as youtube_count,site_id')
            ->groupBy('date', 'site_id')->get()->toArray();

        $youtubeCount = [null, null, null];
        if (empty($list5)) {
            return $youtubeCount;
        }

        $dateList = $this->getDateList($ym);

        foreach ($dateList as $key => $value) {
            $sum = 0;
            foreach ($list5 as $kk => $vv) {
                if (strtotime($value['date']) >= strtotime($vv->date)) {
                    $sum += $vv->youtube_count;
                }
                $dateList[$key]['sum'] = $sum;
            }
        }

        foreach ($dateList as $key => $value) {
            if ($value['date'] == $date1) {
                $youtubeCount[2] = $value['sum'];
            }
            if ($value['date'] == $date2) {
                $youtubeCount[1] = $value['sum'];
            }
            if ($value['date'] == $date3) {
                $youtubeCount[0] = $value['sum'];
            }
        }
        return $youtubeCount;
    }

    public function getInstagramCount($siteId, $ym, $ym2, $date1, $date2, $date3)
    {
        $list6 = DB::table('hootsuite_history')
            ->where('site_id', $siteId)
            ->where('is_instagram', 1)
            ->whereBetween('publish_at', [$ym, $ym2])
            ->selectRaw('DATE_FORMAT(publish_at,"%Y-%m") as date, count(*) as instagram_count,site_id')
            ->groupBy('date', 'site_id')->get()->toArray();

        $instagramCount = [null, null, null];
        if (empty($list6)) {
            return $instagramCount;
        }

        $dateList = $this->getDateList($ym);

        foreach ($dateList as $key => $value) {
            $sum = 0;
            foreach ($list6 as $kk => $vv) {
                if (strtotime($value['date']) >= strtotime($vv->date)) {
                    $sum += $vv->instagram_count;
                }
                $dateList[$key]['sum'] = $sum;
            }
        }

        foreach ($dateList as $key => $value) {
            if ($value['date'] == $date1) {
                $instagramCount[2] = $value['sum'];
            }
            if ($value['date'] == $date2) {
                $instagramCount[1] = $value['sum'];
            }
            if ($value['date'] == $date3) {
                $instagramCount[0] = $value['sum'];
            }
        }
        return $instagramCount;
    }

    //提前保存下个月的
    private function saveNextSocial($site, $dataList)
    {
        $ym = date('Ym');

        foreach ($dataList as $item) {
            $data = PrSocial::query()->where(['site_id' => $site->id, 'ym' => $ym, 'type' => $item['type']])->first();
            $item['data']['data_list'][0] = null;
            if ($data) {
                $data->data_list = $item['data']['data_list'];
                $data->hide = $item['data']['hide'];
                $data->save();
            } else {
                PrSocial::query()->create([
                    'ym' => $ym,
                    'type' => $item['type'],
                    'site_id' => $site->id,
                    'old_id' => $site->old_id,
                    'data_list' => $item['data']['data_list'],
                    'hide' => $item['data']['hide']
                ]);
            }
        }
    }

    public function getDateList($ym)
    {
        $startTime = strtotime(date("Y-m", strtotime("-1 months", strtotime($ym))));
        $endTime = strtotime(date('Y-m', strtotime("first day of -1 month")));

        $dateList = [];
        $i = 0;
        while ($startTime < $endTime) {
            $startTime = strtotime('+1 Month', $startTime);
            $i++;
            $dateList[$i]['date'] = date('Y-m', $startTime);
        }
        return $dateList;
    }
}