2TvQnBgt

1DELIMITER ;;
2CREATE DEFINER=`ops-team`@`%` PROCEDURE `daily_log_lls_2`()
3BEGIN
4 CREATE TEMPORARY TABLE IF NOT EXISTS temp_01 
5    AS ( 
6  SELECT  a.* 
7      FROM  rep_ops_lls_main_2 a 
8     WHERE  
9     		(a.rejected > 0 
10  			OR a.expired > 0 
11   			OR a.ss > 0 
12   			OR a.av_offers > 0 
13   			OR a.move_out > 0 
14   			OR a.price > 0)
15    ) ; 
16       INSERT INTO  rep_ops_lls_LANDLORDS_2
17       (ID, 
18       name, 
19       email, 
20       phone, 
21       tiers, 
22       commission, 
23       city, 
24       market, 
25       total_offers, 
26       status, 
27       login, 
28       total_bookings,
29       rejection_rate,
30       expiration_rate,
31       acceptance_rate,
32       first_offer,
33       bs,
34       usp,
35       clean,
36       rejected,
37       rejected_date,
38       expired,
39       expired_date,
40       ss,
41       av_offers,
42       av_code,
43       move_out,
44       price) 
45            SELECT  a.* 
46              FROM  temp_01 a 
47         LEFT JOIN  rep_ops_lls_LANDLORDS_2 b 
48                ON  a.id = b.id 
49             WHERE  (a.rejected > 0 
50                    OR a.expired > 0 
51   					OR a.ss > 0 
52   					OR a.av_offers > 0 
53   					OR a.move_out > 0 
54   					OR a.price > 0)
55                    AND b.id IS NULL 
56          ORDER BY  a.city ASC 
57                    ,a.tiers ASC ; 
58 UPDATE rep_ops_lls_LANDLORDS_2 b
59  INNER JOIN temp_01 a 
60          ON a.id = b.id 
61    SET b.updated_at = CURRENT_TIMESTAMP(), 
62        b.ID = a.ID,
63		b.name = a.name,
64		b.email = a.email,
65		b.phone = a.phone,
66		b.tiers = a.tiers,
67		b.commission = a.commission,
68		b.city = a.city,
69		b.market = a.market,
70		b.total_offers = a.total_offers,
71		b.status = a.status,
72		b.login = a.login,
73		b.total_bookings = a.total_bookings,
74		b.rejection_rate = a.rejection_rate,
75		b.expiration_rate = a.expiration_rate,
76		b.acceptance_rate = a.acceptance_rate,
77		b.first_offer = b.first_offer,
78		b.bs = a.bs,
79		b.usp = a.usp,
80		b.clean = a.clean,
81		b.rejected = a.rejected,
82		b.rejected_date = a.rejected_date,
83		b.expired = a.expired,
84		b.expired_date = a.expired_date,
85		b.ss = a.ss,
86		b.av_offers = a.av_offers,
87		b.av_code = a.av_code,
88		b.move_out = a.move_out,
89		b.price = a.price;
90   DROP TABLE temp_01 ;
91END;;
92DELIMITER ;