DictionaryCampaignsSyncByCampaign.php

<?php

namespace App\Console\Commands;

use App\Models\Advertisement;
use App\Models\Dictionary;
use App\Models\Pivots\DictionaryCampaign;
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()
    {
        $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
                            ");
        }
        $this->info('dictionary_campaigns successful!');
        //грузим изображения которых по какой то причне нет в целевых.
        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
                            ");
        $this->info('goal_ad_images successful!');

        //грузим наборы минус-фраз которых по какой то причне нет в целевых.
        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
                            ");
        $this->info('goal_negative_keyword_shared_sets successful!');

        //грузим виртуальные визитки которых по какой то причне нет в целевых.
        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 and gvc.deleted_at is null
                WHERE gvc.v_card_id is null
                            ");
        $this->info('goal_v_cards successful!');

        //грузим группы которых по какой то причне нет в целевых.
        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 ag.id, dc.external_id, dc.id,  ag.name, ag.negative_keywords, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                FROM ad_groups ag
                    INNER JOIN campaigns c on ag.campaign_id = c.id
                    INNER JOIN dictionary_campaigns dc on c.id = dc.campaign_id
                    LEFT JOIN goal_ad_groups gag on ag.id = gag.ad_group_id AND gag.dictionary_campaign_id=dc.id AND gag.deleted_at is null
                WHERE gag.ad_group_id is null
                     AND ag.deleted_at is null
                            ");
        $this->info('goal_ad_groups successful!');

        //грузим связь наборов минус-фраз к группам которых по какой то причне нет в целевых.
        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 AND gag.deleted_at is null
                        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
                            ");
        $this->info('goal_ad_group_goal_negative_keyword_shared_sets successful!');

        //грузим ключевые фразы которых по какой то причне нет в целевых.
        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 AND ag.deleted_at is null
                        INNER JOIN goal_ad_groups gag on ag.id = gag.ad_group_id AND gag.deleted_at is null
                         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
                            ");
        $this->info('goal_keywords successful!');

        //грузим расширения которых по какой то причне нет в целевых.
        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
                            ");
        $this->info('goal_sitelinks successful!');

        //грузим наборы быстрых ссылок которых по какой то причне нет в целевых.
        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 and gae.deleted_at is null
                    WHERE gae.ad_extension_id is null AND gae.deleted_at is null
                            ");
        $this->info('goal_ad_extensions successful!');

        //грузим объявления которых по какой то причне нет в целевых.
        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 AND ag.deleted_at is null
                         INNER JOIN goal_ad_groups gag on ag.id = gag.ad_group_id AND gag.deleted_at is null
                         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 and gad.deleted_at is null
                         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 and gvc.deleted_at is null
                         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.state != '" . Advertisement::STATE_ARCHIVED . "}' and ad.archived_need 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))
                            ");
        $this->info('goal_advertisements successful!');

        //обновляем связи целевых объявлений.
        if (DB::connection()->getName()=='sqlite'){
            //Для sqllite надо другй запрос писать, этот не пройдет
//            DB::update("
//                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 and gad.dictionary_campaign_id = gag.dictionary_campaign_id
//                     INNER JOIN dictionary_campaigns dc on gad.dictionary_campaign_id = dc.id
//                     INNER JOIN dictionaries d on dc.dictionary_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 gad.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)
//                        )
//                    )
//                    ");
        } else {
            DB::update("
                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 and ag.deleted_at is null
                     INNER JOIN goal_ad_groups gag on ag.id = gag.ad_group_id and gad.dictionary_campaign_id = gag.dictionary_campaign_id and gag.deleted_at is null
                     INNER JOIN dictionary_campaigns dc on gad.dictionary_campaign_id = dc.id
                     INNER JOIN dictionaries d on dc.dictionary_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 gad.dictionary_campaign_id = gvc.dictionary_campaign_id and gvc.deleted_at is null
                     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_need = CURRENT_TIMESTAMP,
                    gad.updated_at = CURRENT_TIMESTAMP

                    WHERE gad.deleted_at is null and
                    (
                        (
                            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)
                        )
                    )
                    ");
        }
        $this->info('UPDATE goal_advertisements successful!');

        //грузим связь объявлений к расширения которых по какой то причне нет в целевых.
        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 and gae.deleted_at is null
                        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 and ga.deleted_at is null
                        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
                            ");
        $this->info('goal_advertisement_goal_ad_extensions successful!');

        //Удаляем лишнии связи объявлений к расширениям.
        if (DB::connection()->getName()=='sqlite'){
//            DB::delete("
//                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");
        } else {
            DB::delete("
                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 and ga.deleted_at is null
                        INNER JOIN goal_ad_extensions gae on goal_advertisement_goal_ad_extensions.goal_ad_extension_id = gae.id and gae.deleted_at is null
                        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");
        }

        $this->info('DELETE goal_advertisement_goal_ad_extensions successful!');

        //грузим расширения которых по какой то причне нет в целевых.
        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
                            ");
        $this->info('goal_retargetinglists successful!');

        //грузим условия нацеливания на аудиторию которых по какой то причне нет в целевых.
        //грузим те, у которых есть ретаргетинг. Без него пока не рассмтариваем
        //будут те, где он не указан дорабоатем. Это будет те, где есть interest
        //таким бразом нацеливание перенесется только после того, как выгрузится ретаргетинг
        DB::insert("
                INSERT INTO goal_audience_targets(dictionary_campaign_external_id, dictionary_campaign_id,
                        goal_ad_group_external_id, goal_ad_group_id, goal_retargetinglist_external_id, goal_retargetinglist_id,
                        audience_target_id, created_at, updated_at)
                    SELECT dc.external_id, dc.id, gag.external_id, gag.id, gr.external_id, gr.id, aut.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    FROM audience_targets aut
                         INNER JOIN campaigns c on aut.campaign_id = c.id
                         INNER JOIN dictionary_campaigns dc on dc.campaign_id = c.id
                         INNER JOIN dictionaries d on dc.dictionary_id = d.id
                         INNER JOIN retargetinglists r on aut.retargetinglist_external_id = r.external_id
                         INNER JOIN goal_retargetinglists gr on r.id = gr.retargetinglist_id AND d.token_id = gr.token_id
                         LEFT JOIN ad_groups ag on c.id = ag.campaign_id AND aut.ad_group_id = ag.id AND ag.deleted_at is null
                         LEFT JOIN goal_ad_groups gag on dc.id = gag.dictionary_campaign_id AND ag.id = gag.ad_group_id AND gag.deleted_at is null
                         LEFT JOIN goal_audience_targets gaut on aut.id = gaut.audience_target_id and gaut.dictionary_campaign_id = dc.id AND gaut.deleted_at is null
                    WHERE gaut.audience_target_id is null
                            ");
        $this->info('goal_audience_targets successful!');

        //грузим условия ретаргетинга и подбора аудитории которых по какой то причне нет в целевых.
        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 AND ag.deleted_at is null
                         LEFT JOIN goal_ad_groups gag on dc.id = gag.dictionary_campaign_id AND ag.id = gag.ad_group_id AND gag.deleted_at is null
                         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
                            ");
        $this->info('goal_bid_modifiers successful!');

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

        return 0;
    }
}