-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathlogistics-spreadsheet.sql
191 lines (191 loc) · 7.23 KB
/
logistics-spreadsheet.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
SELECT
*
FROM
(
-- Quotes
SELECT
quote.id AS doc_id,
quote.date AS doc_date,
quote.type AS doc_type,
status.status_name AS doc_status,
quote.document_number AS doc_num,
quote.element_name AS doc_name,
quote.location_id AS location_id,
venue.display_string AS doc_location,
am.display_string AS account_manager,
method.method_name AS method,
method.id AS method_id,
quote.notes AS doc_notes,
findoc.center_addr_string AS onsite_details,
'24a2d590-2fb8-11e9-9163-4676808eeebb' AS pdf_report_type
FROM
(
-- Load In
SELECT
id,
load_in_date,
load_in_date AS date,
'Load In' AS type,
document_number,
element_name,
status_id,
person_responsible_id,
shipping_method_id AS method_id,
venue_id,
is_deleted,
notes,
location_id,
show_start
FROM
st_prj_project_element
WHERE
def_id = '9bfb850c-b117-11df-b8d5-00e08175e43e' -- Quote
UNION
ALL -- Load Out
SELECT
id,
load_out_date,
load_out_date AS date,
'Load Out' AS type,
document_number,
element_name,
status_id,
person_responsible_id,
return_method_id AS method_id,
venue_id,
is_deleted,
notes,
location_id,
show_start
FROM
st_prj_project_element
WHERE
def_id = '9bfb850c-b117-11df-b8d5-00e08175e43e' -- Quote
) AS quote
LEFT JOIN st_biz_status_option AS status ON (status.id = quote.status_id)
LEFT JOIN st_biz_shipping_method AS method ON (method.id = quote.method_id)
LEFT JOIN st_biz_managed_resource AS am ON (am.id = quote.person_responsible_id)
LEFT JOIN st_biz_managed_resource AS venue ON (venue.id = quote.venue_id)
LEFT JOIN st_fin_document AS findoc ON (quote.id = findoc.id)
WHERE
quote.is_deleted = 0
AND (
status.id != '78a1508c-aee7-11df-b8d5-00e08175e43e' -- Cancelled
AND status.id != '8b47ca2c-aee7-11df-b8d5-00e08175e43e'
) -- Closed
UNION
ALL -- RPOs
SELECT
rpo.id AS doc_id,
rpo.date AS doc_date,
rpo.type AS doc_type,
"" AS doc_status,
rpo.document_number AS doc_num,
rpo.element_name AS doc_name,
rpo.location_id AS location_id,
vendor.display_string AS doc_location,
am.display_string AS account_manager,
method.method_name AS method,
method.id AS method_id,
rpo.notes AS doc_notes,
"" AS onsite_details,
"7bcf4f50-b5fc-11e8-b74c-0030489e8f64" AS pdf_report_type
FROM
(
-- Pick Up
SELECT
id,
planned_start_date,
planned_start_date AS date,
'PUP' AS type,
document_number,
element_name,
-- status_id,
shipping_method_id AS method_id,
person_responsible_id,
vendor_id,
is_deleted,
location_id,
notes -- show_start
FROM
st_prj_project_element
WHERE
def_id = 'c2eaed0c-b0bc-11df-b8d5-00e08175e43e' -- RPO
UNION
ALL -- Drop Off
SELECT
id,
planned_end_date,
planned_end_date AS date,
'DOFF' AS type,
document_number,
element_name,
-- status_id,
null AS method_id,
person_responsible_id,
vendor_id,
is_deleted,
location_id,
notes -- show_start
FROM
st_prj_project_element
WHERE
def_id = 'c2eaed0c-b0bc-11df-b8d5-00e08175e43e' -- RPO
) AS rpo
LEFT JOIN st_biz_managed_resource AS am ON (am.id = rpo.person_responsible_id)
LEFT JOIN st_biz_managed_resource AS vendor ON (vendor.id = rpo.vendor_id)
LEFT JOIN st_biz_shipping_method AS method ON (method.id = rpo.method_id)
WHERE
rpo.is_deleted = 0
UNION
ALL
SELECT
sales.id AS doc_id,
sales.planned_start_date AS doc_date,
"SALE" AS doc_type,
status.status_name AS doc_status,
sales.document_number AS doc_num,
sales.element_name AS doc_name,
sales.location_id AS location_id,
venue.display_string AS doc_location,
am.display_string AS account_manager,
method.method_name AS method,
method.id AS method_id,
sales.notes AS doc_notes,
"" AS onsite_details,
"7bcf4f50-b5fc-11e8-b74c-0030489e8f64" AS pdf_report_type
FROM
st_prj_project_element AS sales
LEFT JOIN st_biz_status_option AS status ON (status.id = sales.status_id)
LEFT JOIN st_biz_managed_resource AS am ON (am.id = sales.person_responsible_id)
LEFT JOIN st_biz_managed_resource AS venue ON (venue.id = sales.venue_id)
LEFT JOIN st_biz_shipping_method AS method ON (method.id = sales.shipping_method_id)
WHERE
sales.def_id = '6f36f740-a565-11e3-a128-00259000d29a' -- sales
AND sales.is_deleted = 0
AND (
status.id != '78a1508c-aee7-11df-b8d5-00e08175e43e' -- Cancelled
AND status.id != '8b47ca2c-aee7-11df-b8d5-00e08175e43e'
) -- Closed
) AS main
WHERE
main.method_id IN (
'd6093248-3592-11e1-99fd-00e08175e43e',
-- Action Movers
'f29c2cb0-6058-11e8-bc06-0030489e8f64',
-- Day and Ross
'88037050-605a-11e8-bc06-0030489e8f64',
-- Loungeworks
'6b774ba0-605f-11e8-bc06-0030489e8f64',
-- Loungeworks Warehouse Courier
'0ee30788-3593-11e1-99fd-00e08175e43e',
-- Other
'cf3a44e0-5ec9-11e8-bc06-0030489e8f64',
-- Outside Carrier
'09e14a88-3593-11e1-99fd-00e08175e43e' -- See Notes
)
AND main.location_id = '2f49c62c-b139-11df-b8d5-00e08175e43e' -- Vancouver
-- AND main.doc_date > $ P { START_DATE }
-- AND main.doc_date <= DATE_ADD($ P { END_DATE }, INTERVAL 1 DAY)
ORDER BY
main.doc_date ASC;