DictionaryCampaignsSyncByCampaign.php

<?php

namespace App\Console\Commands;

use App\Models\AdGroup;
use App\Models\Campaigns;
use App\Models\Dictionary;
use App\Models\Keyword;
use App\Models\Pivots\DictionaryCampaign;
use App\Models\Pivots\GoalAdGroup;
use App\Models\Pivots\GoalKeyword;
use App\Models\Tokens;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class DictionaryCampaignsSyncByCampaign extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'dictionarycampaigns:sync';

    /**
     * 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 int
     */
    public function handle()
    {
//        Campaigns::has('dictionaryCampaignSynchronizedNeedSynced')->forManaged()->get()->each(function (Campaigns $campaign) {
//            $campaign->load([
//                'groupsForNotKeywordsLoadable.keywords',
//                'dictionaryCampaignSynchronizedNeedSynced'
//            ]);
////хоть и есть нужные, а тут они не выберутся. Видимо потому что был сбой, а в кампании записано что все хорошо
//            $campaign->dictionaryCampaignSynchronizedNeedSynced->each(function (DictionaryCampaign $dictionaryCampaign) use ($campaign) {
//
//                $campaign->groupsForNotKeywordsLoadable->each(function (AdGroup $adGroup) use ($dictionaryCampaign) {
//
//                    $goalAdGroup = GoalAdGroup::updateOrCreateByMain($adGroup, $dictionaryCampaign);
//
//                    $data_keywords = $adGroup->keywords->map(function (Keyword $keyword) use ($goalAdGroup, $dictionaryCampaign) {
//                        return GoalKeyword::getDataByMain($keyword, $goalAdGroup, $dictionaryCampaign);
//                    })->toArray();
//
//                    GoalKeyword::upsert($data_keywords, [
//                        'dictionary_campaign_id',
//                        'goal_ad_group_id',
//                        'keyword_id',
//                    ], [
//                        'dictionary_campaign_id',
//                        'dictionary_campaign_external_id',
//                        'goal_ad_group_id',
//                        'goal_ad_group_external_id',
//                        'keyword_id',
//                    ]);
//
//                    $goalKeywordQuery = GoalKeyword::where('dictionary_campaign_id', $dictionaryCampaign->getKey())
//                        ->where('goal_ad_group_id', $goalAdGroup->getKey());
//
//                    if (count($data_keywords)) {
//                        $goalKeywordQuery->whereNotIn('keyword_id', array_column($data_keywords, 'keyword_id'));
//                    }
//
//                    $goalKeywords = $goalKeywordQuery->get();
//
//                    $goalKeywords->each(function (GoalKeyword $goalKeyword) {
//                        $goalKeyword->delete();
//                    });
//
//                });
//
//                $dictionaryCampaign->update([
//                    'synced_need' => null,
//                ]);
//
//            });
//
//        });

        $cities = Dictionary::where('token_id', '>', 0)->get();

        foreach ($cities as $city) {
            //грузиим РК которых по какой то причне нет в целевых.
            DB::insert("
                INSERT INTO dictionary_campaigns(campaign_id, dictionary_id, name, negative_keywords, excluded_sites, created_at, updated_at)
                SELECT c.id, {$city->id}, c.name, c.negative_keywords, c.excluded_sites, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                FROM campaigns c
                 LEFT JOIN dictionary_campaigns dc on c.id = dc.campaign_id AND dc.dictionary_id={$city->id}
                WHERE dc.campaign_id is null AND c.manage=1
                            ");
        }

        //грузим изображения которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_ad_images(ad_image_id, token_id, created_at, updated_at)
                SELECT ai.id, t.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                FROM ad_images ai
                    INNER JOIN tokens t on t.type != '" . Tokens::MAIN . "'
                    LEFT JOIN goal_ad_images gai on ai.id = gai.ad_image_id and t.id = gai.token_id
                WHERE gai.id is null and gai.deleted_at is null
                            ");

        //грузим наборы минус-фраз которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_negative_keyword_shared_sets(negative_keyword_shared_set_id, token_id, created_at, updated_at)
                SELECT nkss.id, t.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                FROM negative_keyword_shared_sets nkss
                    INNER JOIN tokens t on t.type != '" . Tokens::MAIN . "'
                    LEFT JOIN goal_negative_keyword_shared_sets gnkss on nkss.id = gnkss.negative_keyword_shared_set_id and t.id = gnkss.token_id
                WHERE gnkss.id is null and nkss.deleted_at is null
                            ");

        //грузим виртуальные визитки которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_v_cards(v_card_id, dictionary_campaign_external_id, dictionary_campaign_id, created_at, updated_at)
                SELECT vc.id, dc.external_id, dc.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                FROM v_cards vc
                    INNER JOIN campaigns c on vc.campaign_id = c.id
                    INNER JOIN dictionary_campaigns dc on c.id = dc.campaign_id
                    LEFT JOIN goal_v_cards gvc on vc.id = gvc.v_card_id AND gvc.dictionary_campaign_id = dc.id
                WHERE gvc.v_card_id is null
                            ");

        //грузим группы которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_ad_groups(ad_group_id, dictionary_campaign_external_id, dictionary_campaign_id, name, negative_keywords, created_at, updated_at)
                SELECT a.id, dc.external_id, dc.id,  a.name, a.negative_keywords, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                FROM ad_groups a
                    INNER JOIN campaigns c on a.campaign_id = c.id
                    INNER JOIN dictionary_campaigns dc on c.id = dc.campaign_id
                    LEFT JOIN goal_ad_groups gag on a.id = gag.ad_group_id AND gag.dictionary_campaign_id=dc.id
                WHERE gag.ad_group_id is null
                            ");

        //грузим связь наборов минус-фраз к группам которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_ad_group_goal_negative_keyword_shared_sets(goal_ad_group_id, goal_negative_keyword_shared_set_id, created_at, updated_at)
                    SELECT gag.id, gnkss.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    FROM ad_group_negative_keyword_shared_sets agnkss
                        INNER JOIN negative_keyword_shared_sets nkss on agnkss.negative_keyword_shared_set_id = nkss.id and nkss.deleted_at is null
                        INNER JOIN goal_negative_keyword_shared_sets gnkss on nkss.id = gnkss.negative_keyword_shared_set_id
                        INNER JOIN ad_groups ag on agnkss.ad_group_id = ag.id and ag.deleted_at is null
                        INNER JOIN goal_ad_groups gag on ag.id = gag.ad_group_id
                        INNER JOIN dictionary_campaigns dc on gag.dictionary_campaign_id = dc.id
                        INNER JOIN dictionaries d on dc.dictionary_id = d.id and d.token_id = gnkss.token_id
                        LEFT JOIN goal_ad_group_goal_negative_keyword_shared_sets gaggnkss on gnkss.id = gaggnkss.goal_negative_keyword_shared_set_id and gag.id = gaggnkss.goal_ad_group_id
                    WHERE gaggnkss.goal_negative_keyword_shared_set_id is null
                            ");

        //грузим ключевые фразы которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_keywords(dictionary_campaign_external_id, goal_ad_group_external_id, dictionary_campaign_id,
                          goal_ad_group_id, keyword_id, created_at, updated_at)
                    SELECT gag.dictionary_campaign_external_id, gag.external_id, gag.dictionary_campaign_id, gag.id, k.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    FROM keywords k
                        INNER JOIN ad_groups ag on k.ad_group_id = ag.id
                        INNER JOIN goal_ad_groups gag on ag.id = gag.ad_group_id
                         LEFT JOIN goal_keywords gk on k.id = gk.keyword_id AND gk.goal_ad_group_id=gag.id
                    WHERE gk.keyword_id is null AND k.deleted_at is null
                            ");

        //грузим расширения которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_sitelinks(sitelink_id, token_id, created_at, updated_at)
                    SELECT s.id, t.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    FROM sitelinks s
                        INNER JOIN tokens t on t.type != '" . Tokens::MAIN . "'
                        LEFT JOIN goal_sitelinks gs on s.id = gs.sitelink_id and t.id = gs.token_id
                    WHERE gs.sitelink_id is null AND gs.deleted_at is null
                            ");

        //грузим наборы быстрых ссылок которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_ad_extensions(ad_extension_id, token_id, created_at, updated_at)
                    SELECT ae.id, t.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    FROM ad_extensions ae
                        INNER JOIN tokens t on t.type != '" . Tokens::MAIN . "'
                        LEFT JOIN goal_ad_extensions gae on ae.id = gae.ad_extension_id and t.id = gae.token_id
                    WHERE gae.ad_extension_id is null AND gae.deleted_at is null
                            ");

        //грузим объявления которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_advertisements(dictionary_campaign_external_id, goal_ad_group_external_id, dictionary_campaign_id,
                          goal_ad_group_id, advertisement_id, goal_v_card_id, goal_v_card_external_id, goal_sitelink_id, goal_sitelink_external_id, created_at, updated_at)
                    SELECT gag.dictionary_campaign_external_id, gag.external_id, gag.dictionary_campaign_id, gag.id, ad.id, gvc.id, gvc.external_id, gs.id, gs.external_id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    FROM advertisements ad
                         INNER JOIN ad_groups ag on ad.ad_group_id = ag.id
                         INNER JOIN goal_ad_groups gag on ag.id = gag.ad_group_id
                         INNER JOIN dictionaries d on gag.dictionary_campaign_id = d.id
                         LEFT JOIN goal_advertisements gad on ad.id = gad.advertisement_id AND gad.goal_ad_group_id=gag.id
                         LEFT JOIN v_cards vc on ad.v_card_external_id = vc.external_id
                         LEFT JOIN goal_v_cards gvc on vc.id = gvc.v_card_id and gag.dictionary_campaign_id = gvc.dictionary_campaign_id
                         LEFT JOIN sitelinks s on ad.sitelink_external_id = s.external_id
                         LEFT JOIN goal_sitelinks gs on s.id = gs.sitelink_id and gs.token_id = d.token_id
                         WHERE gad.advertisement_id is null and ad.campaign_id is not null
                            and (ad.sitelink_external_id is null or (ad.sitelink_external_id is not null and s.id is not null))
                            and (ad.v_card_external_id is null or (ad.v_card_external_id is not null and vc.id is not null))
                            ");

        //обновляем связи целевых объявлений.
        DB::insert("
                UPDATE goal_advertisements gad
                     INNER JOIN advertisements ad on ad.id = gad.advertisement_id
                     INNER JOIN ad_groups ag on ad.ad_group_id = ag.id
                     INNER JOIN goal_ad_groups gag on ag.id = gag.ad_group_id
                     INNER JOIN dictionaries d on gag.dictionary_campaign_id = d.id
                     LEFT JOIN v_cards vc on ad.v_card_external_id = vc.external_id
                     LEFT JOIN goal_v_cards gvc on vc.id = gvc.v_card_id and gag.dictionary_campaign_id = gvc.dictionary_campaign_id
                     LEFT JOIN sitelinks s on ad.sitelink_external_id = s.external_id
                     LEFT JOIN goal_sitelinks gs on s.id = gs.sitelink_id and gs.token_id = d.token_id


                SET gad.goal_v_card_id = gvc.id,
                    gad.goal_v_card_external_id = gvc.external_id,
                    gad.goal_sitelink_id = gs.id,
                    gad.goal_sitelink_external_id = gs.external_id,
                    gad.updated_at = CURRENT_TIMESTAMP

                    WHERE (
                        (
                            gad.goal_sitelink_id != gs.id
                            or
                            (gad.goal_sitelink_id is null and gs.id is not null)
                            or
                            (gad.goal_sitelink_id is not null and gs.id is null)
                        )
                        or
                        (
                            gad.goal_v_card_id != gvc.id
                            or
                            (gad.goal_v_card_id is null and gvc.id is not null)
                            or
                            (gad.goal_v_card_id is not null and gvc.id is null)
                        )
                    )
                    ");

        //грузим связь объявлений к расширения которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_advertisement_goal_ad_extensions(goal_advertisement_id, goal_ad_extension_id, created_at, updated_at)
                    SELECT ga.id, gae.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    FROM advertisement_ad_extensions aae
                        INNER JOIN ad_extensions ae on aae.ad_extension_id = ae.id and ae.deleted_at is null
                        INNER JOIN goal_ad_extensions gae on ae.id = gae.ad_extension_id
                        INNER JOIN advertisements a on aae.advertisement_id = a.id and a.deleted_at is null
                        INNER JOIN goal_advertisements ga on a.id = ga.advertisement_id
                        INNER JOIN dictionary_campaigns dc on ga.dictionary_campaign_id = dc.id
                        INNER JOIN dictionaries d on dc.dictionary_id = d.id and d.token_id = gae.token_id
                        LEFT JOIN goal_advertisement_goal_ad_extensions gagae on gae.id = gagae.goal_ad_extension_id and ga.id = gagae.goal_advertisement_id
                    WHERE gagae.goal_ad_extension_id is null
                            ");

        //Удаляем лишнии связи объявлений к расширениям.
        DB::insert("
                DELETE goal_advertisement_goal_ad_extensions
                    FROM goal_advertisement_goal_ad_extensions
                        INNER JOIN goal_advertisements ga on goal_advertisement_goal_ad_extensions.goal_advertisement_id = ga.id
                        INNER JOIN goal_ad_extensions gae on goal_advertisement_goal_ad_extensions.goal_ad_extension_id = gae.id
                        INNER JOIN advertisements a on ga.advertisement_id = a.id
                        LEFT JOIN advertisement_ad_extensions aae on a.id = aae.advertisement_id and gae.ad_extension_id = aae.ad_extension_id
                    WHERE aae.ad_extension_id is null");

        //грузим расширения которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_retargetinglists(retargetinglist_id, token_id, created_at, updated_at)
                    SELECT r.id, t.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    FROM retargetinglists r
                        INNER JOIN tokens t on t.type != '" . Tokens::MAIN . "'
                        LEFT JOIN goal_retargetinglists gt on r.id = gt.retargetinglist_id and t.id = gt.token_id
                    WHERE gt.retargetinglist_id is null AND gt.deleted_at is null
                            ");

        //грузим условия ретаргетинга и подбора аудитории которых по какой то причне нет в целевых.
        DB::insert("
                INSERT INTO goal_bid_modifiers(dictionary_campaign_external_id, goal_ad_group_external_id, dictionary_campaign_id,
                          goal_ad_group_id, bid_modifier_id, created_at, updated_at)
                    SELECT dc.external_id, gag.external_id, dc.id, gag.id, bm.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    FROM bid_modifiers bm
                         INNER JOIN campaigns c on bm.campaign_id = c.id
                         INNER JOIN dictionary_campaigns dc on dc.campaign_id = c.id
                         LEFT JOIN ad_groups ag on c.id = ag.campaign_id AND bm.ad_group_id = ag.id
                         LEFT JOIN goal_ad_groups gag on dc.id = gag.dictionary_campaign_id AND ag.id = gag.ad_group_id
                         LEFT JOIN goal_bid_modifiers gbm on bm.id = gbm.bid_modifier_id and gbm.dictionary_campaign_id = dc.id
                    WHERE gbm.bid_modifier_id is null
                            ");

        DictionaryCampaign::needSynced()->update([
            'synced_need' => null,
        ]);

        return 0;
    }
}