R4U6DfJF

1DELIMITER ;;
2CREATE PROCEDURE `daily_log_lls_ES`()
3BEGIN
4 CREATE TEMPORARY TABLE IF NOT EXISTS temp_01 
5    AS ( 
6  SELECT  a.* 
7      FROM  rep_ops_lls_main a 
8     WHERE  (a.total > 0 OR a.past_7_days > 0)
9            AND a.usp = 0 
10            AND a.market = 'ES' 
11    ) ; 
12       INSERT INTO  rep_ops_lls_LANDLORDS (ID, name, email, phone, commission, tiers, city, market, total_offers, status, login, total_avail, total_price, total_rejected, total_expired, total, offers_ap_events, USP, bookings, rejected_rate, expired_rate, acceptance_rate, first_offer, past_7_days) 
13            SELECT  a.* 
14              FROM  temp_01 a 
15         LEFT JOIN  rep_ops_lls_LANDLORDS b 
16                ON  a.id = b.id 
17             WHERE  (a.total > 0 OR a.past_7_days > 0) 
18                    AND a.usp = 0 
19                    AND a.market = 'ES' 
20                    AND b.id IS NULL 
21          ORDER BY  a.city ASC 
22                    ,a.tiers ASC ; 
23 UPDATE rep_ops_lls_LANDLORDS b
24  INNER JOIN temp_01 a 
25          ON a.id = b.id 
26    SET b.updated_at = CURRENT_TIMESTAMP(), 
27        b.ID = a.ID, 
28        b.name = a.name, 
29        b.email = a.email, 
30        b.phone = a.phone, 
31        b.commission = a.commission, 
32        b.tiers = a.tiers, 
33        b.city = a.city, 
34        b.market = a.market, 
35        b.total_offers = a.total_offers, 
36        b.status = a.status, 
37        b.login = a.login, 
38        b.total_avail = a.total_avail, 
39        b.total_price = a.total_price, 
40        b.total_rejected = a.total_rejected, 
41        b.total_expired = a.total_expired, 
42        b.total = a.total, 
43        b.offers_ap_events = a.offers_ap_events, 
44        b.USP = a.USP, 
45        b.bookings = a.bookings, 
46        b.rejected_rate = a.rejected_rate, 
47        b.expired_rate = a.expired_rate, 
48        b.acceptance_rate = a.acceptance_rate, 
49        b.first_offer = a.first_offer, 
50        b.past_7_days = a.past_7_days ; 
51   DROP TABLE temp_01 ;
52END;;
53DELIMITER ;