-
Notifications
You must be signed in to change notification settings - Fork 0
/
row.sql
229 lines (220 loc) · 7.69 KB
/
row.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
-- ------------------------
-- Contacts
-- ------------------------
-- contact
select n.id as contact_id,
a.name as company_name
-- as first_name,
-- as last_name,
'Y' as is_company,
-- as is_individual,
-- as email,
n.website as website,
n.phone as business_phone,
n.phone_home as home_phone,
-- as mobile_phone,
-- as other_phone,
-- as fax,
-- as title,
-- as last_update_date,
-- as last_update_user,
'row' as legacy_data_source_name
-- as isactive
from row.companies n;
---------------------------------
---Business
-- this one can be done by code we need contact_id
-- from contact table
---------------------------------
select n.id as business_id,
contact.contact_id as contact_id, -- from contact_id above
-- as ownership_type,
-- as location_type,
-- as business_status,
-- as district,
-- as open_date,
-- as business_description,
-- as closed_date,
-- as federal_id_number,
-- as state_id_number,
-- as dba,
'companies' as legacy_data_source_name
from row.companies n;
-- contact_address
select n.id as contact_id,
n.address,
-- as address_type,
-- as street_number,
-- as pre_direction,
-- as street_name,
-- as street_type,
-- as post_direction,
-- as unit_suite_number,
-- as address_line_3,
-- as po_box,
n.city as city,
n.state as state_code,
-- as province,
n.zip as zip,
-- as county_code,
-- as country_code,
-- as country_type,
-- as last_update_date,
-- as last_update_user
from row.companies n;
-- contact_note
select n.id as contact_id,
n.notes as note_text
-- as note_title,
-- as note_user,
-- as note_date
from row.companies n
where n.notes is not null;
--
-- ------------------------
-- Contacts
-- ------------------------
-- contact
select n.id as contact_id,
-- as company_name
n.fname as first_name,
n.lname as last_name,
-- as is_company,
-- as is_individual,
n.email as email,
n.website as website,
n.work_phone as business_phone,
-- as home_phone,
n.cell_phone as mobile_phone,
-- as other_phone,
n.fax as fax,
-- as title,
-- as last_update_date,
-- as last_update_user,
'contacts' as legacy_data_source_name
-- as isactive
from row.contacts n;
-- contact_address
select n.id as contact_id,
n.address,
-- as address_type,
-- as street_number,
-- as pre_direction,
-- as street_name,
-- as street_type,
-- as post_direction,
-- as unit_suite_number,
-- as address_line_3,
-- as po_box,
n.city as city,
n.state as state_code,
-- as province,
n.zip as zip,
-- as county_code,
-- as country_code,
-- as country_type,
-- as last_update_date,
-- as last_update_user
from row.contacts n;
-- contact_note
select n.id as contact_id,
n.notes as note_text,
-- as note_title,
-- as note_user,
-- as note_date
from row.contacts n
where n.notes is not null;
--
------------------------------
---business_contact
------------------------------
select n.compnay_id as business_id,
n.contact_id as contact_id,
c.type_id as contact_type
from row.company_contacts n
join row.contacts c on n.contact_id=c.id;
--
--------------------------------
-- Contact for bond_companies
-- bond_companies has no contacts
--------------------------------
select n.id as contact_id,
n.name as company_name
-- as first_name,
-- as last_name,
'Y' as is_company,
-- as is_individual,
-- as email,
-- as website,
-- as business_phone,
-- as home_phone,
-- as mobile_phone,
-- as other_phone,
-- as fax,
-- as title,
-- as last_update_date,
-- as last_update_user,
'bond_compnaies' as legacy_data_source_name,
-- as isactive
from row.bond_companies n;
--------------------------------
-- bond
--------------------------------
select b.id as bond_id,
b.bond_num as bond_number,
b.type as bond_type,
case when b.expire_date<now() then 'expired' else 'not expired yet' end as bond_status,
-- as issue_date,
b.expire_date as expire_date,
-- as release_date,
b.amount as amount,
-- as global_entity_account_number,
c.company_id as obligee_contact_id,
c.contact_id as principal_contact_id,
b.bond_company_id as surety_contact_id
from row.bonds b
left join bond_companies bc on bc.id=b.bond_company_id -- there are bond_company_id of -1
left join company_contacts c on c.id=b.company_contact_id
where b.bond_num is not null
and b.bond_company_id is not null;
--------------------------------
-- inspection
--------------------------------
select i.id as inspection_number,
'Excavation' as inspection_type,
i.status as inspection_status,
-- as create_date,
-- as requested_for_date,
-- as scheduled_for_date,
-- as attempt_number,
case when i.status='Completed' then 1 else 0 end as completed,
-- as last_update_date,
-- as last_update_user,
i.inspector_id as inspector,
i.date as inspected_date_start,
i.date as inspected_date_end,
i.notes as comment,
-- as inspection_case_number
from row.inspections i
left join inspectors u on i.inspector_id=u.user_id;
--------------------------------
-- permit
--------------------------------
select p.permit_num as permit_number,
'Excavation' as permit_type,
p.permit_type as permit_sub_type,
p.status as permit_status,
-- as district,
p.date as apply_date,
p.project as permit_description,
p.start_date as issue_date,
-- as expire_date,
-- as last_update_date,
-- as last_inspection_date,
-- as valuation,
-- as square_footage,
'row' as legacy_data_source_name,
-- as project_number,
concat_ws(' ', i.first_name, i.last_name) as assigned_to
from row.excavpermits p
left join inspectors i on p.reviewer_id=i.user_id;