E6d8340a

1CREATE TABLE IF NOT EXISTS `mmsubscribers` (
2`id` int(11) NOT NULL,
3`email` varchar(50) DEFAULT NULL,
4`name` varchar(50) DEFAULT NULL,
5`hash` varchar(100) DEFAULT NULL,
6`status` varchar(50) DEFAULT NULL,
7`listid` int(11) DEFAULT NULL,
8`phone` varchar(50) DEFAULT NULL,
9`login` varchar(50) DEFAULT NULL,
10`pass` varchar(50) DEFAULT NULL,
11`language` varchar(50) DEFAULT NULL,
12`orgname` varchar(75) DEFAULT NULL,
13`orgadmin` int(11) DEFAULT NULL,
14`domain` varchar(150) DEFAULT NULL,
15`organizationId` int(11) DEFAULT NULL,
16`number` varchar(15) DEFAULT NULL,
17`department` varchar(50) DEFAULT NULL,
18`server` varchar(20) DEFAULT NULL,
19`reseller` varchar(75) DEFAULT NULL,
20`operators` varchar(50) DEFAULT NULL,
21`subscriber_type` int(11) DEFAULT NULL
22) ENGINE=InnoDB AUTO_INCREMENT=166629 DEFAULT CHARSET=utf8;
23INSERT INTO `mmsubscribers` (`id`, `email`, `name`, `hash`, `status`, 
24`listid`, `phone`, `login`, `pass`, `language`, `orgname`, `orgadmin`, 
25`domain`, `organizationId`, `number`, `department`, `server`, `reseller`, 
26`operators`, `subscriber_type`) VALUES
27(13, 'nils@gmail.com', 'Nils Nissesson', NULL, 'inactive', 2, '+4612312313', 
28'user1', 'user1', 'SV', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Reseller1', 
29NULL, NULL),
30(15, 'nils@hotmail.com', 'Nils Karlsson', 'b5q8gelmyvp4gu9sgntgvzkha0u', 'active', 2, '+47741741109874', NULL, NULL, 'EN', '', NULL, NULL, NULL, NULL, NULL, '', '', '', NULL),
31(17, 'test@testing.com', 'Test Karlsson', 'mdpte4uhkvqzew4n2megoa4qk7k', 'active', 2, '+4482798273798', NULL, NULL, 'SV', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
32(18, 'test123@testdomain.se', 'Herr Vincze', 'mhw81k96liwlfj8lkyvu2rdr9y', 'inactive', 2, '', NULL, NULL, 'SV', '', NULL, '', NULL, '', '', '', '', '', NULL),
33(146996, 'nils.nils@nils.se', 'Nils Dolk', 'dd07c78e2fa7487b283f6c4dbff7ec0fc448a4', 'active', 3, '', NULL, NULL, '', 'Test', 1, 'nils.se', 6721, '468123123454', 'Sälj', '185.XX.124.162', 'Reseller56', 'STH-UNO', 4),
34 (149277, 'bo@bosse.com', 'Bo Holgersson', 'c557202473aef551d410a00d2b1be3075e8d7e1fe', 'active', 3, '', NULL, NULL, '', 'Test', 1, 'bo.se', 6578, '4653643232436', 'Företagsförsäljning', '185.39.124.154', 'Reseller1', 'STB-uno', 4),
35
36(149824, 'support@nisse.no', 'testar', '9c5f161459236d5d216c48a47d0c2aecf1', 'active', 3, '', NULL, NULL, '', 'Test', 1, 'bo.se', 6578, '4654170399', '', '185.XX.124.162', 'Reseller1', 'Tele2', 4);
37	
38CREATE TABLE IF NOT EXISTS `driftinfo_subscriber_operator_lookup` (
39  `id` int(11) NOT NULL,
40  `drifinfo_problem_id` int(11) DEFAULT NULL,
41  `operator` varchar(75) DEFAULT NULL
42) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
43
44--
45-- Dumpning av Data i tabell `driftinfo_subscriber_operator_lookup`
46--
47
48INSERT INTO `driftinfo_subscriber_operator_lookup` (`id`, `drifinfo_problem_id`, `operator`) VALUES
49(1, 4, 'Weblink'),
50(4, 10, 'Tele2');
51	
52CREATE TABLE IF NOT EXISTS `driftinfo_subscriber_reseller_lookup` (
53  `id` int(11) NOT NULL,
54  `drifinfo_problem_id` int(11) DEFAULT NULL,
55  `reseller` varchar(75) DEFAULT NULL
56) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
57
58--
59-- Dumpning av Data i tabell `driftinfo_subscriber_reseller_lookup`
60--
61
62INSERT INTO `driftinfo_subscriber_reseller_lookup` (`id`, `drifinfo_problem_id`, `reseller`) VALUES
63(1, 4, 'Reseller1'),
64(2, 4, 'Reseller2'),
65(5, 10, 'Reseller3'),
66(6, 10, 'BestReseller'),
67(7, 10, 'BadReseller');
68	
69CREATE TABLE IF NOT EXISTS `driftinfo_subscriber_server_lookup` (
70  `id` int(11) NOT NULL,
71  `drifinfo_problem_id` int(11) DEFAULT NULL,
72  `server` varchar(50) DEFAULT NULL
73) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
74
75--
76-- Dumpning av Data i tabell `driftinfo_subscriber_server_lookup`
77--
78
79INSERT INTO `driftinfo_subscriber_server_lookup` (`id`, `drifinfo_problem_id`, `server`) VALUES
80(1, 4, '185.XX.124.162'),
81(8, 10, '172.XX.129.20'),
82(9, 10, '172.XX.129.21');
83	
84CREATE TABLE IF NOT EXISTS `driftinfo_problem` (
85  `id` int(11) NOT NULL,
86  `active` varchar(11) DEFAULT NULL,
87  `date` date DEFAULT NULL,
88  `omrade` varchar(75) DEFAULT NULL,
89  `soluno_staging_message` varchar(750) DEFAULT NULL,
90  `heading` varchar(100) DEFAULT NULL,
91  `ingress` varchar(750) DEFAULT NULL,
92  `image` varchar(1500) DEFAULT NULL,
93  `text` varchar(3000) DEFAULT NULL,
94  `beskrivning` varchar(250) DEFAULT NULL,
95  `user_owner` varchar(75) DEFAULT NULL,
96  `estimate_finished_date` date DEFAULT NULL,
97  `language` varchar(50) DEFAULT NULL,
98  `time` time DEFAULT NULL,
99  `estimate_finished_time` time DEFAULT NULL
100) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
101
102--
103-- Dumpning av Data i tabell `driftinfo_problem`
104--
105
106INSERT INTO `driftinfo_problem` (`id`, `active`, `date`, `omrade`, `soluno_staging_message`, `heading`, `ingress`, `image`, `text`, `beskrivning`, `user_owner`, `estimate_finished_date`, `language`, `time`, `estimate_finished_time`) VALUES
107(2, 'No', '2018-04-18', 'Test Problem', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-04-18', NULL, '09:04:00', '16:00:00'),
108(3, 'No', '2018-04-27', 'Operatör tysta samtal', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-04-27', NULL, '11:35:00', '14:30:00'),
109(4, 'No', '2018-05-16', 'Operator driftstörning', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-16', NULL, '11:20:00', '14:00:00'),
110(5, 'No', '2018-05-17', 'Operator driftstörning', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-17', NULL, '14:30:00', '17:00:00'),
111(6, 'No', '2018-05-18', 'Internet problem', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-18', NULL, '16:00:00', '18:00:00'),
112(7, 'No', '2018-05-25', 'Operator driftstörning', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-25', NULL, '14:30:00', '17:00:00'),
113(8, 'No', '2018-05-28', 'Operator routing problem', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-29', NULL, '16:00:00', '12:00:00'),
114(10, 'Yes', '2018-05-31', 'Operator Business  störning', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-31', NULL, '10:00:00', '14:00:00');
115	
116SELECT
117mmsubscribers.email,
118driftinfo_problem.active,
119driftinfo_problem.id,
120driftinfo_subscriber_operator_lookup.`operator`
121FROM mmsubscribers
122INNER JOIN driftinfo_subscriber_operator_lookup ON mmsubscribers.operators = driftinfo_subscriber_operator_lookup.`operator`
123INNER JOIN driftinfo_problem ON driftinfo_subscriber_operator_lookup.drifinfo_problem_id = driftinfo_problem.id
124WHERE driftinfo_problem.active = 'Yes'
125	
126SELECT
127mmsubscribers.email,
128driftinfo_problem.active,
129driftinfo_problem.id,
130driftinfo_subscriber_reseller_lookup.reseller
131FROM mmsubscribers
132INNER JOIN driftinfo_subscriber_reseller_lookup ON mmsubscribers.reseller = driftinfo_subscriber_reseller_lookup.reseller
133INNER JOIN driftinfo_problem ON driftinfo_subscriber_reseller_lookup.drifinfo_problem_id = driftinfo_problem.id
134	
135SELECT
136mmsubscribers.email,
137driftinfo_problem.active,
138driftinfo_problem.id,
139driftinfo_subscriber_server_lookup.server
140FROM mmsubscribers
141INNER JOIN driftinfo_subscriber_server_lookup ON mmsubscribers.server = driftinfo_subscriber_server_lookup.server
142INNER JOIN driftinfo_problem ON driftinfo_subscriber_server_lookup.drifinfo_problem_id = driftinfo_problem.id