1 | /* |
---|
2 | Copyright (C) 2014 |
---|
3 | Alejandro Mujica (amujica@cenditel.gob.ve) |
---|
4 | José Ruiz (jruiz@cenditel.gob.ve) |
---|
5 | Julie Vera (jvera@cenditel.gob.ve) |
---|
6 | |
---|
7 | CENDITEL Fundación Centro Nacional de Desarrollo e Investigación en |
---|
8 | Tecnologías Libres |
---|
9 | |
---|
10 | Este programa es software libre; Usted puede usarlo bajo los términos de la |
---|
11 | licencia de software GPL versión 2.0 de la Free Software Foundation. |
---|
12 | |
---|
13 | Este programa se distribuye con la esperanza de que sea útil, pero SIN |
---|
14 | NINGUNA GARANTÍA; tampoco las implícitas garantías de MERCANTILIDAD o |
---|
15 | ADECUACIÓN A UN PROPÓSITO PARTICULAR. |
---|
16 | Consulte la licencia GPL para más detalles. Usted debe recibir una copia |
---|
17 | de la GPL junto con este programa; si no, escriba a la Free Software |
---|
18 | Foundation Inc. 51 Franklin Street,5 Piso, Boston, MA 02110-1301, USA. |
---|
19 | */ |
---|
20 | |
---|
21 | /* |
---|
22 | Autor: Alejandro J. Mujica |
---|
23 | Fecha de creación: 12/06/2014 |
---|
24 | Este archivo contiene la implementación de las diversas consultas |
---|
25 | a base de datos efectuadas. |
---|
26 | */ |
---|
27 | |
---|
28 | # include <queries.H> |
---|
29 | |
---|
30 | # include <sstream> |
---|
31 | # include <stdexcept> |
---|
32 | |
---|
33 | # include <dbQuery.H> |
---|
34 | # include <autoConnection.H> |
---|
35 | # include <strQuery.H> |
---|
36 | |
---|
37 | const std::string DB::PRODUCT_TABLE_NAME = "produccion_producto"; |
---|
38 | const std::string DB::INPUT_TABLE_NAME = "produccion_insumo"; |
---|
39 | const std::string DB::PLANT_TABLE_NAME = "unidadecon_subunidad_economica"; |
---|
40 | const std::string DB::COMPANY_TABLE_NAME = "unidadecon_unidad_economica"; |
---|
41 | const std::string DB::COMPANY_ACTIVITY_CIIU_TABLE_NAME = |
---|
42 | "unidadecon_actividad_ciiu"; |
---|
43 | const std::string DB::PRODUCT_INPUT_TABLE_NAME = "produccion_producto_t_insumo"; |
---|
44 | const std::string DB::MEASUREMENT_UNIT_TABLE_NAME = "comun_unida_medida"; |
---|
45 | const std::string DB::INPUT_SUPPLIER_TABLE_NAME = |
---|
46 | "cmproveedores_proveedorinsumo"; |
---|
47 | const std::string DB::SUPPLIER_TABLE_NAME = "cmproveedores_proveedor"; |
---|
48 | const std::string DB::PRODUCTION_QUANTITIES_TABLE_NAME = |
---|
49 | "produccion_produccion_precios_r"; |
---|
50 | const std::string DB::ACQ_PRICE_TABLE_NAME = "produccion_consumo_precios_r"; |
---|
51 | const std::string DB::COUNTRY_TABLE_NAME = "comun_pais"; |
---|
52 | const std::string DB::PARISH_TABLE_NAME = "comun_parroquia"; |
---|
53 | const std::string DB::TOWN_TABLE_NAME = "comun_municipio"; |
---|
54 | const std::string DB::STATE_TABLE_NAME = "comun_entidad"; |
---|
55 | const std::string DB::COMPANY_OCCUPANCY_TABLE_NAME = |
---|
56 | "unidadecon_personal_ocupado"; |
---|
57 | |
---|
58 | const std::string DB::ID = "id"; |
---|
59 | const std::string DB::NAME = "nombre"; |
---|
60 | const std::string DB::COMPANY_NAME = "razon_social"; |
---|
61 | const std::string DB::TECHNICAL_SPECIFICATION = "esp_tec"; |
---|
62 | const std::string DB::TARIFF_CODE = "codigo_aran_id"; |
---|
63 | const std::string DB::MEASUREMENT_UNIT_ID = "unidad_medida_id"; |
---|
64 | const std::string DB::TRADEMARK = "marca"; |
---|
65 | const std::string DB::PLANT_ID = "planta_id_id"; |
---|
66 | const std::string DB::CODE = "codigo"; |
---|
67 | const std::string DB::PARISH_CODE = "parroquia_codigo"; |
---|
68 | const std::string DB::TOWN_CODE = "municipio_codigo"; |
---|
69 | const std::string DB::STATE_CODE = "entidad_codigo"; |
---|
70 | const std::string DB::COMPANY_RIF = "unidad_economica_rif"; |
---|
71 | const std::string DB::UE_RIF = "ue_rif"; |
---|
72 | const std::string DB::RIF = "rif"; |
---|
73 | const std::string DB::NATIONALITY = "nacional"; |
---|
74 | const std::string DB::WORKDAY = "horas_turnos"; |
---|
75 | const std::string DB::ACQ_PRICE = "cost_adq"; |
---|
76 | const std::string DB::NAC_ACQ_PRICE = "prec_adq_nac"; |
---|
77 | const std::string DB::CIIU_ID = "ciiu_id"; |
---|
78 | const std::string DB::ACTIVE = "activo"; |
---|
79 | const std::string DB::MAIN = "principal"; |
---|
80 | const std::string DB::INPUT_ID = "insumo_id_id"; |
---|
81 | const std::string DB::PRODUCT_ID = "producto_id_id"; |
---|
82 | const std::string DB::SUPPLIER_ID = "proveedor_id_id"; |
---|
83 | const std::string DB::PRODUCED_QUANTITY = "cant_producido"; |
---|
84 | const std::string DB::PRICE = "precio_venta"; |
---|
85 | const std::string DB::BOUGHT_QUANTITY = "cantidad_comprada_r"; |
---|
86 | const std::string DB::QUANTITY = "cantidad"; |
---|
87 | const std::string DB::ORIG_COUNTRY_ID= "pais_origen_id"; |
---|
88 | const std::string DB::PROC_COUNTRY_ID = "pais_procedencia_id"; |
---|
89 | const std::string DB::YEAR_R = "anho_r"; |
---|
90 | const std::string DB::YEAR = "anho"; |
---|
91 | const std::string DB::USED_CAPACITY = "capacidad_utilizada"; |
---|
92 | const std::string DB::INSERTION_DATE = "fecha_insercion"; |
---|
93 | const std::string DB::OPERATING = "operativo"; |
---|
94 | const std::string DB::ADMINISTRATIVE = "administrativo"; |
---|
95 | |
---|
96 | std::string single_quote(const std::string & str) |
---|
97 | { |
---|
98 | return "'" + str + "'"; |
---|
99 | } |
---|
100 | |
---|
101 | std::string double_quote(const std::string & str) |
---|
102 | { |
---|
103 | return "\"" + str + "\""; |
---|
104 | } |
---|
105 | |
---|
106 | std::string concat(const std::string & table, const std::string & field, |
---|
107 | const char & connector) |
---|
108 | { |
---|
109 | return table + connector + field; |
---|
110 | } |
---|
111 | |
---|
112 | std::string as(const std::string & field, const std::string & alias) |
---|
113 | { |
---|
114 | return field + " AS " + alias; |
---|
115 | } |
---|
116 | |
---|
117 | std::string concatas(const std::string & table, const std::string & field, |
---|
118 | const std::string & alias) |
---|
119 | { |
---|
120 | return as(concat(table, field), alias); |
---|
121 | } |
---|
122 | |
---|
123 | std::string concatas(const std::string & table, const std::string & field) |
---|
124 | { |
---|
125 | return concatas(table, field, concat(table, field, '_')); |
---|
126 | } |
---|
127 | |
---|
128 | std::string op(const std::string & attr, const std::string & value, |
---|
129 | const std::string & oper) |
---|
130 | { |
---|
131 | return attr + " " + oper + " " + value; |
---|
132 | } |
---|
133 | |
---|
134 | std::string id_to_str(const db_id_t & id) |
---|
135 | { |
---|
136 | std::stringstream sstr; |
---|
137 | sstr << id; |
---|
138 | return sstr.str(); |
---|
139 | } |
---|
140 | |
---|
141 | std::string between(const std::string & field, const std::string & beg, |
---|
142 | const std::string & end) |
---|
143 | { |
---|
144 | return field + " BETWEEN " + single_quote(beg) + " AND " + single_quote(end); |
---|
145 | } |
---|
146 | |
---|
147 | std::string in_year(const std::string & field, const std::string & year) |
---|
148 | { |
---|
149 | std::string beg = year + "-01-01"; |
---|
150 | std::string end = year + "-12-31"; |
---|
151 | return between(field, beg, end); |
---|
152 | } |
---|
153 | |
---|
154 | Product load_product_by_id(const db_id_t & id) |
---|
155 | { |
---|
156 | AutoConnection conn; |
---|
157 | DBQuery query(conn); |
---|
158 | StrQuery str_query; |
---|
159 | |
---|
160 | str_query.addSelect(concatas(DB::PRODUCT_TABLE_NAME, DB::ID)); |
---|
161 | str_query.addSelect(concatas(DB::PRODUCT_TABLE_NAME, DB::NAME)); |
---|
162 | str_query.addSelect(concatas(DB::PRODUCT_TABLE_NAME, DB::TARIFF_CODE)); |
---|
163 | str_query.addSelect(concatas(DB::MEASUREMENT_UNIT_TABLE_NAME, DB::NAME)); |
---|
164 | str_query.addSelect(concatas(DB::PLANT_TABLE_NAME, DB::ID)); |
---|
165 | str_query.addSelect(concatas(DB::COMPANY_TABLE_NAME, DB::RIF)); |
---|
166 | str_query.addSelect(concatas(DB::COMPANY_TABLE_NAME, DB::COMPANY_NAME)); |
---|
167 | str_query.addSelect(concatas(DB::STATE_TABLE_NAME, DB::NAME)); |
---|
168 | |
---|
169 | str_query.addFrom(DB::PRODUCT_TABLE_NAME); |
---|
170 | str_query.addFrom(DB::MEASUREMENT_UNIT_TABLE_NAME); |
---|
171 | str_query.addFrom(DB::PLANT_TABLE_NAME); |
---|
172 | str_query.addFrom(DB::COMPANY_TABLE_NAME); |
---|
173 | str_query.addFrom(DB::STATE_TABLE_NAME); |
---|
174 | str_query.addFrom(DB::TOWN_TABLE_NAME); |
---|
175 | str_query.addFrom(DB::PARISH_TABLE_NAME); |
---|
176 | |
---|
177 | str_query.addWhere(op(concat(DB::PRODUCT_TABLE_NAME, DB::ID), |
---|
178 | id_to_str(id))); |
---|
179 | str_query.addWhere(op(concat(DB::MEASUREMENT_UNIT_TABLE_NAME, DB::ID), |
---|
180 | concat(DB::PRODUCT_TABLE_NAME, |
---|
181 | DB::MEASUREMENT_UNIT_ID))); |
---|
182 | str_query.addWhere(op(concat(DB::PLANT_TABLE_NAME, DB::ID), |
---|
183 | concat(DB::PRODUCT_TABLE_NAME, DB::PLANT_ID))); |
---|
184 | str_query.addWhere(op(concat(DB::PLANT_TABLE_NAME, DB::ID), |
---|
185 | concat(DB::PRODUCT_TABLE_NAME, DB::PLANT_ID))); |
---|
186 | str_query.addWhere(op(concat(DB::COMPANY_TABLE_NAME, DB::RIF), |
---|
187 | concat(DB::PLANT_TABLE_NAME, DB::UE_RIF))); |
---|
188 | str_query.addWhere(op(concat(DB::COMPANY_TABLE_NAME, DB::PARISH_CODE), |
---|
189 | concat(DB::PARISH_TABLE_NAME, DB::CODE))); |
---|
190 | str_query.addWhere(op(concat(DB::PARISH_TABLE_NAME, DB::TOWN_CODE), |
---|
191 | concat(DB::TOWN_TABLE_NAME, DB::CODE))); |
---|
192 | str_query.addWhere(op(concat(DB::TOWN_TABLE_NAME, DB::STATE_CODE), |
---|
193 | concat(DB::STATE_TABLE_NAME, DB::CODE))); |
---|
194 | |
---|
195 | if (not query.exec(str_query)) |
---|
196 | { |
---|
197 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
198 | throw std::logic_error(msg); |
---|
199 | } |
---|
200 | |
---|
201 | Product product; |
---|
202 | |
---|
203 | if (not query.next()) |
---|
204 | return product; |
---|
205 | |
---|
206 | product.set_company_name( |
---|
207 | query.getValue(concat(DB::COMPANY_TABLE_NAME, DB::COMPANY_NAME, '_'))); |
---|
208 | product.set_company_rif( |
---|
209 | query.getValue(concat(DB::COMPANY_TABLE_NAME, DB::RIF, '_'))); |
---|
210 | product.set_company_location( |
---|
211 | query.getValue(concat(DB::STATE_TABLE_NAME, DB::NAME, '_'))); |
---|
212 | product.set_id( |
---|
213 | std::atol(query.getValue(concat(DB::PRODUCT_TABLE_NAME, DB::ID, '_')))); |
---|
214 | product.set_name(query.getValue(concat(DB::PRODUCT_TABLE_NAME, |
---|
215 | DB::NAME, '_'))); |
---|
216 | product.set_tariff_code( |
---|
217 | query.getValue(concat(DB::PRODUCT_TABLE_NAME, DB::TARIFF_CODE, '_'))); |
---|
218 | product.set_measurement_unit( |
---|
219 | query.getValue(concat(DB::MEASUREMENT_UNIT_TABLE_NAME, DB::NAME, '_'))); |
---|
220 | |
---|
221 | return product; |
---|
222 | } |
---|
223 | |
---|
224 | void list_input_ids(const db_id_t & product_id, const std::string & year, |
---|
225 | List<db_id_t> & input_ids) |
---|
226 | { |
---|
227 | AutoConnection conn; |
---|
228 | DBQuery query(conn); |
---|
229 | StrQuery str_query; |
---|
230 | |
---|
231 | str_query.addSelect(DB::INPUT_ID); |
---|
232 | str_query.addFrom(DB::PRODUCT_INPUT_TABLE_NAME); |
---|
233 | str_query.addWhere(op(DB::PRODUCT_ID, id_to_str(product_id))); |
---|
234 | str_query.addWhere(op(DB::QUANTITY, "0", "<>")); |
---|
235 | str_query.addWhere(op(DB::YEAR, year)); |
---|
236 | |
---|
237 | if (not query.exec(str_query)) |
---|
238 | { |
---|
239 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
240 | throw std::logic_error(msg); |
---|
241 | } |
---|
242 | |
---|
243 | while (query.next()) |
---|
244 | input_ids.append(std::atol(query.getValue(DB::INPUT_ID))); |
---|
245 | } |
---|
246 | |
---|
247 | void list_inputs(const db_id_t & input_id, const std::string & year, |
---|
248 | List<Input> & inputs) |
---|
249 | { |
---|
250 | AutoConnection conn; |
---|
251 | DBQuery query(conn); |
---|
252 | StrQuery str_query; |
---|
253 | |
---|
254 | str_query.addSelect(concatas(DB::INPUT_TABLE_NAME, DB::ID)); |
---|
255 | str_query.addSelect(concatas(DB::INPUT_TABLE_NAME, DB::NAME)); |
---|
256 | str_query.addSelect(concatas(DB::INPUT_TABLE_NAME, DB::TARIFF_CODE)); |
---|
257 | str_query.addSelect(concatas(DB::MEASUREMENT_UNIT_TABLE_NAME, DB::NAME)); |
---|
258 | |
---|
259 | str_query.addFrom(DB::INPUT_TABLE_NAME); |
---|
260 | str_query.addFrom(DB::MEASUREMENT_UNIT_TABLE_NAME); |
---|
261 | |
---|
262 | str_query.addWhere(op(concat(DB::INPUT_TABLE_NAME, DB::ID), |
---|
263 | id_to_str(input_id))); |
---|
264 | str_query.addWhere(op(concat(DB::INPUT_TABLE_NAME, |
---|
265 | DB::MEASUREMENT_UNIT_ID), |
---|
266 | concat(DB::MEASUREMENT_UNIT_TABLE_NAME, DB::ID))); |
---|
267 | |
---|
268 | if (not query.exec(str_query)) |
---|
269 | { |
---|
270 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
271 | throw std::logic_error(msg); |
---|
272 | } |
---|
273 | |
---|
274 | if (not query.next()) |
---|
275 | { |
---|
276 | std::stringstream msg; |
---|
277 | msg << "Input with id = " << input_id << " doesn't exist in database"; |
---|
278 | throw std::domain_error(msg.str()); |
---|
279 | } |
---|
280 | |
---|
281 | Input input; |
---|
282 | |
---|
283 | input.set_id( |
---|
284 | std::atol(query.getValue(concat(DB::INPUT_TABLE_NAME, DB::ID, '_')))); |
---|
285 | input.set_name(query.getValue(concat(DB::INPUT_TABLE_NAME, DB::NAME, '_'))); |
---|
286 | input.set_tariff_code( |
---|
287 | query.getValue(concat(DB::INPUT_TABLE_NAME, DB::TARIFF_CODE, '_'))); |
---|
288 | input.set_measurement_unit( |
---|
289 | query.getValue(concat(DB::MEASUREMENT_UNIT_TABLE_NAME, DB::NAME, '_'))); |
---|
290 | |
---|
291 | str_query.clear(); |
---|
292 | query.clear(); |
---|
293 | |
---|
294 | str_query.addSelect(concatas(DB::SUPPLIER_TABLE_NAME, DB::RIF)); |
---|
295 | str_query.addSelect(concatas(DB::SUPPLIER_TABLE_NAME, DB::NAME)); |
---|
296 | str_query.addSelect(concatas(DB::SUPPLIER_TABLE_NAME, DB::NATIONALITY)); |
---|
297 | str_query.addSelect(concatas(DB::COUNTRY_TABLE_NAME, DB::NAME)); |
---|
298 | |
---|
299 | str_query.addFrom(DB::INPUT_SUPPLIER_TABLE_NAME); |
---|
300 | str_query.addFrom(DB::SUPPLIER_TABLE_NAME); |
---|
301 | str_query.addFrom(DB::COUNTRY_TABLE_NAME); |
---|
302 | |
---|
303 | str_query.addWhere(op(concat(DB::INPUT_SUPPLIER_TABLE_NAME, DB::YEAR_R), |
---|
304 | year)); |
---|
305 | str_query.addWhere(op(concat(DB::INPUT_SUPPLIER_TABLE_NAME, DB::INPUT_ID), |
---|
306 | id_to_str(input_id))); |
---|
307 | str_query.addWhere(op(concat(DB::INPUT_SUPPLIER_TABLE_NAME, DB::SUPPLIER_ID), |
---|
308 | concat(DB::SUPPLIER_TABLE_NAME, DB::ID))); |
---|
309 | str_query.addWhere(op(concat(DB::COUNTRY_TABLE_NAME, DB::ID), |
---|
310 | concat(DB::SUPPLIER_TABLE_NAME, |
---|
311 | DB::ORIG_COUNTRY_ID))); |
---|
312 | |
---|
313 | str_query.addOrderBy(concat(DB::SUPPLIER_TABLE_NAME, DB::RIF)); |
---|
314 | |
---|
315 | if (not query.exec(str_query)) |
---|
316 | { |
---|
317 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
318 | throw std::logic_error(msg); |
---|
319 | } |
---|
320 | |
---|
321 | std::string last_rif = ""; |
---|
322 | |
---|
323 | while (query.next()) |
---|
324 | { |
---|
325 | std::string rif = |
---|
326 | query.getValue(concat(DB::SUPPLIER_TABLE_NAME, DB::RIF, '_')); |
---|
327 | |
---|
328 | std::string nationality = query.getValue(concat(DB::SUPPLIER_TABLE_NAME, |
---|
329 | DB::NATIONALITY, '_')); |
---|
330 | |
---|
331 | if (nationality != "E" and rif == last_rif) |
---|
332 | continue; |
---|
333 | |
---|
334 | last_rif = rif; |
---|
335 | |
---|
336 | input.set_company_rif(rif); |
---|
337 | input.set_company_name( |
---|
338 | query.getValue(concat(DB::SUPPLIER_TABLE_NAME, DB::NAME, '_')) |
---|
339 | ); |
---|
340 | input.set_company_location( |
---|
341 | query.getValue(concat(DB::COUNTRY_TABLE_NAME, DB::NAME, '_')) |
---|
342 | ); |
---|
343 | input.set_nationality(nationality); |
---|
344 | inputs.append(input); |
---|
345 | } |
---|
346 | } |
---|
347 | |
---|
348 | void product_from_input(const Input & input, List<Product> & products) |
---|
349 | { |
---|
350 | AutoConnection conn; |
---|
351 | DBQuery query(conn); |
---|
352 | StrQuery str_query; |
---|
353 | |
---|
354 | str_query.addSelect(concatas(DB::PRODUCT_TABLE_NAME, DB::ID)); |
---|
355 | |
---|
356 | str_query.addFrom(DB::INPUT_TABLE_NAME); |
---|
357 | str_query.addFrom(DB::PRODUCT_TABLE_NAME); |
---|
358 | str_query.addFrom(DB::PLANT_TABLE_NAME); |
---|
359 | |
---|
360 | str_query.addWhere(op(concat(DB::INPUT_TABLE_NAME, DB::ID), |
---|
361 | id_to_str(input.get_id()))); |
---|
362 | str_query.addWhere(op(concat(DB::INPUT_TABLE_NAME, DB::TARIFF_CODE), |
---|
363 | concat(DB::PRODUCT_TABLE_NAME, DB::TARIFF_CODE))); |
---|
364 | str_query.addWhere(op(concat(DB::PLANT_TABLE_NAME, DB::ID), |
---|
365 | concat(DB::PRODUCT_TABLE_NAME, DB::PLANT_ID))); |
---|
366 | str_query.addWhere(op(concat(DB::PLANT_TABLE_NAME, DB::UE_RIF), |
---|
367 | single_quote(input.get_company_rif()))); |
---|
368 | |
---|
369 | if (not query.exec(str_query)) |
---|
370 | { |
---|
371 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
372 | throw std::logic_error(msg); |
---|
373 | } |
---|
374 | |
---|
375 | while (query.next()) |
---|
376 | products.append(load_product_by_id( |
---|
377 | std::atol(query.getValue(concat(DB::PRODUCT_TABLE_NAME, DB::ID, '_'))))); |
---|
378 | } |
---|
379 | |
---|
380 | real get_unitarian_request(const db_id_t & input_id, |
---|
381 | const db_id_t & product_id) |
---|
382 | { |
---|
383 | AutoConnection conn; |
---|
384 | DBQuery query(conn); |
---|
385 | StrQuery str_query; |
---|
386 | |
---|
387 | str_query.addSelect(DB::QUANTITY); |
---|
388 | |
---|
389 | str_query.addFrom(DB::PRODUCT_INPUT_TABLE_NAME); |
---|
390 | |
---|
391 | str_query.addWhere(op(DB::PRODUCT_ID, id_to_str(product_id))); |
---|
392 | str_query.addWhere(op(DB::INPUT_ID, id_to_str(input_id))); |
---|
393 | |
---|
394 | if (not query.exec(str_query)) |
---|
395 | { |
---|
396 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
397 | throw std::logic_error(msg); |
---|
398 | } |
---|
399 | |
---|
400 | if (not query.next()) |
---|
401 | return 0.0; |
---|
402 | |
---|
403 | return std::atof(query.getValue(0)); |
---|
404 | } |
---|
405 | |
---|
406 | real get_acquisition_price(const std::string & nationality, |
---|
407 | const db_id_t & input_id, |
---|
408 | const std::string & year) |
---|
409 | { |
---|
410 | AutoConnection conn; |
---|
411 | DBQuery query(conn); |
---|
412 | StrQuery str_query; |
---|
413 | |
---|
414 | std::string price_field = DB::NAC_ACQ_PRICE; |
---|
415 | |
---|
416 | if (nationality == "E") |
---|
417 | price_field = DB::ACQ_PRICE; |
---|
418 | |
---|
419 | str_query.addSelect(price_field); |
---|
420 | |
---|
421 | str_query.addFrom(DB::ACQ_PRICE_TABLE_NAME); |
---|
422 | |
---|
423 | str_query.addWhere(op(DB::INPUT_ID, id_to_str(input_id))); |
---|
424 | str_query.addWhere(in_year(DB::YEAR, year)); |
---|
425 | |
---|
426 | if (not query.exec(str_query)) |
---|
427 | { |
---|
428 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
429 | throw std::logic_error(msg); |
---|
430 | } |
---|
431 | |
---|
432 | if (not query.next()) |
---|
433 | return 0.0; |
---|
434 | |
---|
435 | return std::atof(query.getValue(0)); |
---|
436 | } |
---|
437 | |
---|
438 | real get_declared_bought_quantity(const std::string & rif, |
---|
439 | const std::string & company_name, |
---|
440 | const db_id_t & input_id, |
---|
441 | const std::string & year) |
---|
442 | { |
---|
443 | AutoConnection conn; |
---|
444 | |
---|
445 | DBQuery query(conn); |
---|
446 | |
---|
447 | std::string sum = "SUM(" + concat(DB::INPUT_SUPPLIER_TABLE_NAME, |
---|
448 | DB::BOUGHT_QUANTITY) + ")"; |
---|
449 | |
---|
450 | std::string str_query = "SELECT " + sum |
---|
451 | + " FROM " + DB::INPUT_SUPPLIER_TABLE_NAME + ", " |
---|
452 | + DB::SUPPLIER_TABLE_NAME + |
---|
453 | + " WHERE " + |
---|
454 | op(concat(DB::INPUT_SUPPLIER_TABLE_NAME, DB::YEAR_R), |
---|
455 | year) + " AND " + |
---|
456 | op(concat(DB::SUPPLIER_TABLE_NAME, DB::ID), |
---|
457 | concat(DB::INPUT_SUPPLIER_TABLE_NAME, |
---|
458 | DB::SUPPLIER_ID)) + " AND " + |
---|
459 | op(concat(DB::INPUT_SUPPLIER_TABLE_NAME, DB::INPUT_ID), |
---|
460 | id_to_str(input_id)) + " AND "; |
---|
461 | |
---|
462 | std::string group = " GROUP BY "; |
---|
463 | |
---|
464 | if (rif.size() > 0) |
---|
465 | { |
---|
466 | str_query.append(op(concat(DB::SUPPLIER_TABLE_NAME, DB::RIF), |
---|
467 | single_quote(rif))); |
---|
468 | group.append(concat(DB::SUPPLIER_TABLE_NAME, DB::RIF)); |
---|
469 | } |
---|
470 | else |
---|
471 | { |
---|
472 | str_query.append(op(concat(DB::SUPPLIER_TABLE_NAME, DB::NAME), |
---|
473 | single_quote(company_name))); |
---|
474 | group.append(concat(DB::SUPPLIER_TABLE_NAME, DB::NAME)); |
---|
475 | } |
---|
476 | |
---|
477 | str_query.append(group); |
---|
478 | |
---|
479 | if (not query.exec(str_query)) |
---|
480 | { |
---|
481 | std::string msg = "Cannot execute query: " + str_query; |
---|
482 | throw std::logic_error(msg); |
---|
483 | } |
---|
484 | |
---|
485 | if (not query.next()) |
---|
486 | return 0.0; |
---|
487 | |
---|
488 | return std::atof(query.getValue(0)); |
---|
489 | } |
---|
490 | |
---|
491 | Aleph::pair<real, real> |
---|
492 | get_produced_quantity_and_price(const db_id_t & product_id, |
---|
493 | const std::string & year) |
---|
494 | { |
---|
495 | AutoConnection conn; |
---|
496 | DBQuery query(conn); |
---|
497 | StrQuery str_query; |
---|
498 | |
---|
499 | str_query.addSelect(DB::PRODUCED_QUANTITY); |
---|
500 | str_query.addSelect(DB::PRICE); |
---|
501 | |
---|
502 | str_query.addFrom(DB::PRODUCTION_QUANTITIES_TABLE_NAME); |
---|
503 | |
---|
504 | str_query.addWhere(op(DB::PRODUCT_ID, id_to_str(product_id))); |
---|
505 | str_query.addWhere(in_year(DB::YEAR, year)); |
---|
506 | |
---|
507 | if (not query.exec(str_query)) |
---|
508 | { |
---|
509 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
510 | throw std::logic_error(msg); |
---|
511 | } |
---|
512 | |
---|
513 | if (not query.next()) |
---|
514 | return Aleph::pair<real, real>(0.0, 0.0); |
---|
515 | |
---|
516 | return Aleph::pair<real, real>(std::atof(query.getValue(0)), |
---|
517 | std::atof(query.getValue(1))); |
---|
518 | } |
---|
519 | |
---|
520 | void list_product_ids(Good * good, List<Aleph::pair<long, long>> & list) |
---|
521 | { |
---|
522 | AutoConnection conn; |
---|
523 | DBQuery query(conn); |
---|
524 | StrQuery str_query; |
---|
525 | |
---|
526 | const std::string & rif = good->get_company_rif(); |
---|
527 | |
---|
528 | const std::string & tariff_code = good->get_tariff_code(); |
---|
529 | |
---|
530 | str_query.addSelect(concatas(DB::PRODUCT_INPUT_TABLE_NAME, DB::PRODUCT_ID)); |
---|
531 | str_query.addSelect(concatas(DB::PRODUCT_INPUT_TABLE_NAME, DB::INPUT_ID)); |
---|
532 | |
---|
533 | str_query.addFrom(DB::SUPPLIER_TABLE_NAME); |
---|
534 | str_query.addFrom(DB::INPUT_SUPPLIER_TABLE_NAME); |
---|
535 | str_query.addFrom(DB::PRODUCT_INPUT_TABLE_NAME); |
---|
536 | str_query.addFrom(DB::INPUT_TABLE_NAME); |
---|
537 | str_query.addFrom(DB::PRODUCT_TABLE_NAME); |
---|
538 | |
---|
539 | str_query.addWhere(op(concat(DB::SUPPLIER_TABLE_NAME, DB::RIF), |
---|
540 | single_quote(rif))); |
---|
541 | str_query.addWhere(op(concat(DB::SUPPLIER_TABLE_NAME, DB::ID), |
---|
542 | concat(DB::INPUT_SUPPLIER_TABLE_NAME, |
---|
543 | DB::SUPPLIER_ID))); |
---|
544 | str_query.addWhere(op(concat(DB::INPUT_SUPPLIER_TABLE_NAME, DB::INPUT_ID), |
---|
545 | concat(DB::INPUT_TABLE_NAME, DB::ID))); |
---|
546 | str_query.addWhere(op(concat(DB::INPUT_TABLE_NAME, DB::TARIFF_CODE), |
---|
547 | single_quote(tariff_code))); |
---|
548 | str_query.addWhere(op(concat(DB::INPUT_SUPPLIER_TABLE_NAME, DB::INPUT_ID), |
---|
549 | concat(DB::PRODUCT_INPUT_TABLE_NAME, DB::INPUT_ID))); |
---|
550 | str_query.addWhere(op(concat(DB::PRODUCT_INPUT_TABLE_NAME, DB::PRODUCT_ID), |
---|
551 | concat(DB::PRODUCT_TABLE_NAME, DB::ID))); |
---|
552 | str_query.addWhere(op(concat(DB::PRODUCT_INPUT_TABLE_NAME, DB::QUANTITY), |
---|
553 | "0", "<>")); |
---|
554 | |
---|
555 | if (not query.exec(str_query)) |
---|
556 | { |
---|
557 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
558 | throw std::logic_error(msg); |
---|
559 | } |
---|
560 | |
---|
561 | while (query.next()) |
---|
562 | list.append(Aleph::pair<long, long>(std::atol(query.getValue(0)), |
---|
563 | std::atol(query.getValue(1)))); |
---|
564 | } |
---|
565 | |
---|
566 | void list_requesters(Good * good, List<Aleph::pair<Product, long>> & list) |
---|
567 | { |
---|
568 | List<Aleph::pair<long, long>> ids; |
---|
569 | |
---|
570 | list_product_ids(good, ids); |
---|
571 | |
---|
572 | for(List <Aleph::pair<long, long>>::Iterator it(ids); it.has_current(); |
---|
573 | it.next()) |
---|
574 | { |
---|
575 | Aleph::pair<long, long> & p = it.get_current(); |
---|
576 | |
---|
577 | Product product = load_product_by_id(p.first); |
---|
578 | |
---|
579 | list.append(Aleph::pair<Product, long>(product, p.second)); |
---|
580 | } |
---|
581 | } |
---|
582 | |
---|
583 | real get_product_used_capacity(const db_id_t & product_id, |
---|
584 | const std::string & year) |
---|
585 | { |
---|
586 | AutoConnection conn; |
---|
587 | DBQuery query(conn); |
---|
588 | StrQuery str_query; |
---|
589 | |
---|
590 | str_query.addSelect(concatas(DB::PLANT_TABLE_NAME, DB::ID)); |
---|
591 | str_query.addSelect(concatas(DB::PLANT_TABLE_NAME, DB::USED_CAPACITY)); |
---|
592 | |
---|
593 | str_query.addFrom(DB::PRODUCT_TABLE_NAME); |
---|
594 | str_query.addFrom(DB::PLANT_TABLE_NAME); |
---|
595 | |
---|
596 | str_query.addWhere(op(concat(DB::PRODUCT_TABLE_NAME, DB::ID), |
---|
597 | id_to_str(product_id))); |
---|
598 | str_query.addWhere(op(concat(DB::PLANT_TABLE_NAME, DB::ID), |
---|
599 | concat(DB::PRODUCT_TABLE_NAME, DB::PLANT_ID))); |
---|
600 | |
---|
601 | if (not query.exec(str_query)) |
---|
602 | { |
---|
603 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
604 | throw std::logic_error(msg); |
---|
605 | } |
---|
606 | |
---|
607 | if (not query.next()) |
---|
608 | return 0; |
---|
609 | |
---|
610 | db_id_t plant_id = std::atol(query.getValue(0)); |
---|
611 | real plant_capacity = std::atof(query.getValue(1)); |
---|
612 | |
---|
613 | query.clear(); |
---|
614 | str_query.clear(); |
---|
615 | |
---|
616 | str_query.addSelect(concatas(DB::PRODUCT_TABLE_NAME, DB::ID)); |
---|
617 | str_query.addSelect(concatas(DB::PRODUCTION_QUANTITIES_TABLE_NAME, |
---|
618 | DB::PRODUCED_QUANTITY)); |
---|
619 | str_query.addSelect(concatas(DB::PRODUCTION_QUANTITIES_TABLE_NAME, |
---|
620 | DB::PRICE)); |
---|
621 | |
---|
622 | str_query.addFrom(DB::PRODUCT_TABLE_NAME); |
---|
623 | str_query.addFrom(DB::PRODUCTION_QUANTITIES_TABLE_NAME); |
---|
624 | |
---|
625 | str_query.addWhere(op(concat(DB::PRODUCT_TABLE_NAME, DB::PLANT_ID), |
---|
626 | id_to_str(plant_id))); |
---|
627 | str_query.addWhere(op(concat(DB::PRODUCT_TABLE_NAME, DB::ID), |
---|
628 | concat(DB::PRODUCTION_QUANTITIES_TABLE_NAME, |
---|
629 | DB::PRODUCT_ID))); |
---|
630 | str_query.addWhere(in_year(DB::YEAR, year)); |
---|
631 | |
---|
632 | if (not query.exec(str_query)) |
---|
633 | { |
---|
634 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
635 | throw std::logic_error(msg); |
---|
636 | } |
---|
637 | |
---|
638 | real total_amount = 0.0; |
---|
639 | |
---|
640 | real amount = 0.0; |
---|
641 | |
---|
642 | while (query.next()) |
---|
643 | { |
---|
644 | db_id_t p_id = std::atol(query.getValue(0)); |
---|
645 | real quantity = std::atof(query.getValue(1)); |
---|
646 | real price = std::atof(query.getValue(2)); |
---|
647 | |
---|
648 | total_amount += quantity * price; |
---|
649 | |
---|
650 | if (p_id == product_id) |
---|
651 | amount = quantity * price; |
---|
652 | } |
---|
653 | |
---|
654 | real proportion = total_amount == 0.0 ? 0.0 : amount / total_amount; |
---|
655 | |
---|
656 | return plant_capacity * proportion; |
---|
657 | } |
---|
658 | |
---|
659 | std::tuple<real, real, real> |
---|
660 | get_product_staff_numbers(const std::string & rif, |
---|
661 | const std::string & year, |
---|
662 | const db_id_t & product_id) |
---|
663 | { |
---|
664 | AutoConnection conn; |
---|
665 | DBQuery query(conn); |
---|
666 | StrQuery str_query; |
---|
667 | |
---|
668 | /* Se le suma una unidad al año porque el registro de empleos se hace al |
---|
669 | año siguiente. |
---|
670 | */ |
---|
671 | int y = std::atoi(year.c_str()) + 1; |
---|
672 | |
---|
673 | std::stringstream sstr_year; |
---|
674 | |
---|
675 | sstr_year << y; |
---|
676 | |
---|
677 | std::string max_date = sstr_year.str() + "-12-31"; |
---|
678 | |
---|
679 | str_query.addSelect(DB::ADMINISTRATIVE); |
---|
680 | str_query.addSelect(DB::OPERATING); |
---|
681 | str_query.addSelect(DB::WORKDAY); |
---|
682 | |
---|
683 | str_query.addFrom(DB::COMPANY_OCCUPANCY_TABLE_NAME); |
---|
684 | |
---|
685 | str_query.addWhere(op(DB::COMPANY_RIF, single_quote(rif))); |
---|
686 | str_query.addWhere(op(DB::ACTIVE, "TRUE")); |
---|
687 | str_query.addWhere(op(DB::INSERTION_DATE, single_quote(max_date), "<=")); |
---|
688 | |
---|
689 | str_query.addOrderBy(DB::INSERTION_DATE); |
---|
690 | |
---|
691 | str_query.setOrderByOption("DESC"); |
---|
692 | |
---|
693 | if (not query.exec(str_query)) |
---|
694 | { |
---|
695 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
696 | throw std::logic_error(msg); |
---|
697 | } |
---|
698 | |
---|
699 | if (not query.next()) |
---|
700 | { |
---|
701 | query.clear(); |
---|
702 | str_query.clear(); |
---|
703 | |
---|
704 | str_query.addSelect(DB::ADMINISTRATIVE); |
---|
705 | str_query.addSelect(DB::OPERATING); |
---|
706 | str_query.addSelect(DB::WORKDAY); |
---|
707 | |
---|
708 | str_query.addFrom(DB::COMPANY_OCCUPANCY_TABLE_NAME); |
---|
709 | |
---|
710 | str_query.addWhere(op(DB::COMPANY_RIF, single_quote(rif))); |
---|
711 | str_query.addWhere(op(DB::ACTIVE, "TRUE")); |
---|
712 | str_query.addWhere(op(DB::INSERTION_DATE, single_quote(max_date), ">")); |
---|
713 | |
---|
714 | str_query.addOrderBy(DB::INSERTION_DATE); |
---|
715 | |
---|
716 | str_query.setOrderByOption("ASC"); |
---|
717 | |
---|
718 | if (not query.exec(str_query)) |
---|
719 | { |
---|
720 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
721 | throw std::logic_error(msg); |
---|
722 | } |
---|
723 | |
---|
724 | if (not query.next()) |
---|
725 | return std::tuple<real, real, real>(0.0, 0.0, 0.0); |
---|
726 | } |
---|
727 | |
---|
728 | real administrative = std::atof(query.getValue(0)); |
---|
729 | real operating = std::atof(query.getValue(1)); |
---|
730 | real workday = std::atof(query.getValue(2)); |
---|
731 | |
---|
732 | query.clear(); |
---|
733 | str_query.clear(); |
---|
734 | |
---|
735 | str_query.addSelect(concatas(DB::PRODUCT_TABLE_NAME, DB::ID)); |
---|
736 | str_query.addSelect(concatas(DB::PRODUCTION_QUANTITIES_TABLE_NAME, |
---|
737 | DB::PRODUCED_QUANTITY)); |
---|
738 | str_query.addSelect(concatas(DB::PRODUCTION_QUANTITIES_TABLE_NAME, |
---|
739 | DB::PRICE)); |
---|
740 | |
---|
741 | str_query.addFrom(DB::PRODUCT_TABLE_NAME); |
---|
742 | str_query.addFrom(DB::PLANT_TABLE_NAME); |
---|
743 | str_query.addFrom(DB::PRODUCTION_QUANTITIES_TABLE_NAME); |
---|
744 | |
---|
745 | str_query.addWhere(op(concat(DB::PLANT_TABLE_NAME, DB::UE_RIF), |
---|
746 | single_quote(rif))); |
---|
747 | str_query.addWhere(op(concat(DB::PLANT_TABLE_NAME, DB::ID), |
---|
748 | concat(DB::PRODUCT_TABLE_NAME, DB::PLANT_ID))); |
---|
749 | str_query.addWhere(op(concat(DB::PRODUCT_TABLE_NAME, DB::ID), |
---|
750 | concat(DB::PRODUCTION_QUANTITIES_TABLE_NAME, |
---|
751 | DB::PRODUCT_ID))); |
---|
752 | str_query.addWhere(in_year(DB::YEAR, year)); |
---|
753 | |
---|
754 | if (not query.exec(str_query)) |
---|
755 | { |
---|
756 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
757 | throw std::logic_error(msg); |
---|
758 | } |
---|
759 | |
---|
760 | real total_amount = 0.0; |
---|
761 | |
---|
762 | real amount = 0.0; |
---|
763 | |
---|
764 | while (query.next()) |
---|
765 | { |
---|
766 | db_id_t p_id = std::atol(query.getValue(0)); |
---|
767 | real quantity = std::atof(query.getValue(1)); |
---|
768 | real price = std::atof(query.getValue(2)); |
---|
769 | |
---|
770 | total_amount += quantity * price; |
---|
771 | |
---|
772 | if (p_id == product_id) |
---|
773 | amount = quantity * price; |
---|
774 | } |
---|
775 | |
---|
776 | real proportion = total_amount == 0.0 ? 0.0 : amount / total_amount; |
---|
777 | |
---|
778 | administrative *= proportion; |
---|
779 | operating *= proportion; |
---|
780 | |
---|
781 | return std::tuple<real, real, real>(workday, administrative, operating); |
---|
782 | } |
---|
783 | |
---|
784 | real get_external_sales(const std::string & tariff_code, |
---|
785 | const std::string & seller_rif, |
---|
786 | const std::string & year, |
---|
787 | const std::string & buyers_to_exclude) |
---|
788 | { |
---|
789 | AutoConnection conn; |
---|
790 | DBQuery query(conn); |
---|
791 | StrQuery str_query; |
---|
792 | |
---|
793 | str_query.addSelect(concatas(DB::INPUT_SUPPLIER_TABLE_NAME, |
---|
794 | DB::BOUGHT_QUANTITY)); |
---|
795 | str_query.addSelect(concatas(DB::ACQ_PRICE_TABLE_NAME, DB::NAC_ACQ_PRICE)); |
---|
796 | |
---|
797 | str_query.addFrom(DB::INPUT_TABLE_NAME); |
---|
798 | str_query.addFrom(DB::INPUT_SUPPLIER_TABLE_NAME); |
---|
799 | str_query.addFrom(DB::SUPPLIER_TABLE_NAME); |
---|
800 | str_query.addFrom(DB::ACQ_PRICE_TABLE_NAME); |
---|
801 | str_query.addFrom(DB::PLANT_TABLE_NAME); |
---|
802 | |
---|
803 | str_query.addWhere(op(concat(DB::INPUT_TABLE_NAME, DB::TARIFF_CODE), |
---|
804 | single_quote(tariff_code))); |
---|
805 | str_query.addWhere(op(concat(DB::INPUT_TABLE_NAME, DB::ID), |
---|
806 | concat(DB::INPUT_SUPPLIER_TABLE_NAME, |
---|
807 | DB::INPUT_ID))); |
---|
808 | str_query.addWhere(op(concat(DB::INPUT_SUPPLIER_TABLE_NAME, |
---|
809 | DB::YEAR_R), year)); |
---|
810 | str_query.addWhere(op(concat(DB::INPUT_SUPPLIER_TABLE_NAME, |
---|
811 | DB::SUPPLIER_ID), |
---|
812 | concat(DB::SUPPLIER_TABLE_NAME, DB::ID))); |
---|
813 | str_query.addWhere(op(concat(DB::SUPPLIER_TABLE_NAME, DB::RIF), |
---|
814 | single_quote(seller_rif))); |
---|
815 | str_query.addWhere(op(concat(DB::ACQ_PRICE_TABLE_NAME, DB::INPUT_ID), |
---|
816 | concat(DB::INPUT_TABLE_NAME, DB::ID))); |
---|
817 | str_query.addWhere(in_year(DB::YEAR, year)); |
---|
818 | |
---|
819 | str_query.addWhere(op(concat(DB::INPUT_TABLE_NAME, DB::PLANT_ID), |
---|
820 | concat(DB::PLANT_TABLE_NAME, DB::ID))); |
---|
821 | |
---|
822 | if (buyers_to_exclude.size() > 0) |
---|
823 | { |
---|
824 | |
---|
825 | std::string exclude = concat(DB::PLANT_TABLE_NAME, DB::UE_RIF) + |
---|
826 | " NOT IN (" + buyers_to_exclude + ")"; |
---|
827 | |
---|
828 | str_query.addWhere(exclude); |
---|
829 | } |
---|
830 | |
---|
831 | if (not query.exec(str_query)) |
---|
832 | { |
---|
833 | std::string msg = "Cannot execute query: " + std::string(str_query); |
---|
834 | throw std::logic_error(msg); |
---|
835 | } |
---|
836 | |
---|
837 | real external_sales = 0.0; |
---|
838 | |
---|
839 | while (query.next()) |
---|
840 | { |
---|
841 | external_sales += std::atof(query.getValue(0)) * |
---|
842 | std::atof(query.getValue(1)); |
---|
843 | } |
---|
844 | |
---|
845 | return external_sales; |
---|
846 | } |
---|
847 | |
---|