· 8 years ago · Jun 16, 2018, 07:04 PM
1DELIMITER ;;
2CREATE DEFINER=`mkt-team`@`%` PROCEDURE `daily_log_offers`()
3BEGIN
4
5 CREATE TABLE IF NOT EXISTS `rep_ops_lls_DAILY_OFFERS` (
6 `id` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
7 `offer_id` int(4) unsigned NOT NULL,
8 `external_reference` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
9 `Rent` decimal(14,4) DEFAULT NULL,
10 `avail` varchar(11) NOT NULL DEFAULT '',
11 `price` varchar(11) NOT NULL DEFAULT '',
12 `rejected` varchar(11) NOT NULL DEFAULT '',
13 `expired` varchar(11) NOT NULL DEFAULT '',
14 `uniplaces_booking` varchar(3) NOT NULL DEFAULT '',
15 `neighborhood` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
16 `offer_type` enum('bedroom','entire-property','bed') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
17 `property_id` int(4) unsigned NOT NULL,
18 `property_yype` enum('studio','apartment','house') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
19 `bonaparte` varchar(22) NOT NULL DEFAULT '',
20 `status` varchar(12) DEFAULT NULL,
21 `score` bigint(11) NOT NULL DEFAULT '0',
22 `benchmark` varchar(25) NOT NULL DEFAULT '',
23 `available_from` date DEFAULT NULL,
24 `available_now` varchar(3) NOT NULL DEFAULT '',
25 `last_update` date DEFAULT NULL,
26 `days_last_update` varchar(7) DEFAULT NULL,
27 `contract_type` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
28 `bills` varchar(16) NOT NULL DEFAULT '',
29 `wifi` varchar(3) DEFAULT NULL,
30 `minimum_nights` smallint(2) unsigned DEFAULT NULL,
31 `resident_ap` varchar(3) NOT NULL DEFAULT '',
32 `total_bookings` decimal(23,0) NOT NULL DEFAULT '0',
33 `total_rejected` decimal(23,0) NOT NULL DEFAULT '0',
34 `total_expired` decimal(23,0) NOT NULL DEFAULT '0',
35 `total_accepted` decimal(23,0) NOT NULL DEFAULT '0',
36 `total_bookings_30` decimal(23,0) NOT NULL DEFAULT '0',
37 `total_rejected_30` decimal(23,0) NOT NULL DEFAULT '0',
38 `total_expired_30` decimal(23,0) NOT NULL DEFAULT '0',
39 `total_accepted_30` decimal(23,0) NOT NULL DEFAULT '0',
40 `total_awaiting` decimal(23,0) NOT NULL DEFAULT '0',
41 `city` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
42 `market` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
43 ) ;
44 TRUNCATE rep_ops_lls_DAILY_OFFERS;
45
46 INSERT INTO rep_ops_lls_DAILY_OFFERS
47 SELECT ao.*
48 FROM rep_ops_lls_ao ao
49 LEFT JOIN rep_ops_lls_LANDLORDS ll
50 ON ll.id = ao.id ;
51END;;
52DELIMITER ;