DictionaryCampaignsSyncByCampaign.php
12.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
<?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_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_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
");
//грузим связь объевлений к расширения которых по какой то причне нет в целевых.
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("
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.dictionary_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;
}
}