2EBDGDCL

1DROP TEMPORARY TABLE IF EXISTS sa_with_external_nodes;
2CREATE TEMPORARY TABLE sa_with_external_nodes (PRIMARY KEY (site_area_id))
3SELECT DISTINCT sa.site_area_id FROM rabota_db.site_area sa 
4JOIN site s ON sa.parent_id = s.site_id
5RIGHT JOIN rabota_db.site_area_demand_source_settings sds ON sa.site_area_id = sds.site_area_id
6WHERE sa.parent_id = 216564
7AND 
8(
9sa.use_header_bidding = 1 
10OR
11s.use_header_bidding = 1
12)
13AND sds.chain_status <> 1
14AND sds.status <> 1
15AND sds.deleted <> 0
16AND sds.end_date_ptz IS NOT NULL
17;
18
19SELECT * FROM sa_with_external_nodes;
20
21
22DROP TEMPORARY TABLE IF EXISTS sa_without_external_nodes;
23CREATE TEMPORARY TABLE sa_without_external_nodes (PRIMARY KEY (site_area_id))
24SELECT sa.site_area_id FROM rabota_db.site_area sa 
25LEFT JOIN
26   sa_with_external_nodes saen ON saen.site_area_id = sa.site_area_id
27WHERE sa.parent_id = 216564
28AND saen.site_area_id IS NULL
29;
30
31SELECT * FROM sa_without_external_nodes;
32
33INSERT INTO rabota_db.`site_area_demand_source_settings` (
34    `site_area_id`, 
35    `demand_source_id`, 
36    `status`, 
37    `chain_id`, 
38    `chain_type`, 
39    `chain_status`, 
40    `order`, 
41    `manual_percent`,
42    `modify_date`,
43    `start_date_ptz`
44)
45    SELECT
46        sa.`site_area_id`,
47        2,
48        1,
49        1,
50        'manual',
51        1,
52        1,
53        100,
54        NOW() AS modify_date,
55        DATE(NOW()) AS start_date_ptz
56      
57FROM sa_without_external_nodes sa;