public function exportPAES($request) { require_once R3_CLASS_DIR . 'obj.global_result_table.php'; require_once R3_CLASS_DIR . 'obj.global_plain_table.php'; $id = empty($request['id']) ? $this->id : (int) $request['id']; set_time_limit(5 * 60); ini_set('memory_limit', '2G'); ignore_user_abort(true); $db = ezcDbInstance::get(); $driverInfo = $this->auth->getConfigValue('APPLICATION', 'EXPORT_PAES', array()); if (!isset($driverInfo[$request['driver']]['driver'])) { throw new Exception(_("Invalid driver \"{$request['driver']}\"")); } $exportDriverName = $driverInfo[$request['driver']]['driver']; $exportDriver = R3ExportPAES::factory($exportDriverName, $this->auth, isset($driverInfo[$request['driver']]['params']) ? $driverInfo[$request['driver']]['params'] : null); $q = $db->createSelectQuery(); $q->select('*')->from('global_strategy_data')->where("gst_id={$id}"); $globalStrategyData['general'] = $db->query($q)->fetch(PDO::FETCH_ASSOC); $globalStrategyData['general']['gst_reduction_target_text'] = $globalStrategyData['general']['gst_reduction_target_absolute'] ? _('Riduzione assoluta') : _('Riduzione "pro capite"'); $budgetEuro = $globalStrategyData['general']['gst_budget'] == '' ? '' : '€' . R3NumberFormat($globalStrategyData['general']['gst_budget'], 2, true); if ($globalStrategyData['general']['gst_budget_text_1'] != '' && $globalStrategyData['general']['gst_budget'] != '') { $globalStrategyData['general']['gst_budget_text_1'] = sprintf('%s - %s', $budgetEuro, $globalStrategyData['general']['gst_budget_text_1']); } else { $globalStrategyData['general']['gst_budget_text_1'] = $budgetEuro . $globalStrategyData['general']['gst_budget_text_1']; } if ($globalStrategyData['general']['gst_budget_text_2'] != '' && $globalStrategyData['general']['gst_budget'] != '') { $globalStrategyData['general']['gst_budget_text_2'] = sprintf('%s - %s', $budgetEuro, $globalStrategyData['general']['gst_budget_text_2']); } else { $globalStrategyData['general']['gst_budget_text_2'] = $budgetEuro . $globalStrategyData['general']['gst_budget_text_2']; } $q = $db->createSelectQuery(); $q->select('*')->from('global_plain_data')->where("gp_id=" . (int) $globalStrategyData['general']['gp_id']); $actionPlanData['general'] = $db->query($q)->fetch(PDO::FETCH_ASSOC); if (isset($actionPlanData['general']['gp_approval_date'])) { $actionPlanData['general']['gp_approval_date'] = ' ' . SQLDateToStr($actionPlanData['general']['gp_approval_date'], 'd/m/Y'); } // SHEET 2: EMISSION INVENTORY $udm_divider = 1000; // MWh (in db data are stored in kWh) $inventoryTableKinds = array('CONSUMPTION', 'EMISSION', 'ENERGY_PRODUCTION', 'HEATH_PRODUCTION'); $emissionInventoryData = array(); for ($i = 1; $i <= 2; $i++) { $ge_id = $i == 1 ? $globalStrategyData['general']['ge_id'] : $globalStrategyData['general']['ge_id_2']; if ($ge_id != '') { $q = $db->createSelectQuery(); $q->select('*, ge_green_electricity_purchase/1000 AS ge_green_electricity_purchase')->from('global_entry_data')->where('ge_id=' . (int) $ge_id); $emissionInventoryData[$i]['general'] = $db->query($q)->fetch(PDO::FETCH_ASSOC); $emissionInventoryData[$i]['general']['gst_emission_factor_text'] = $globalStrategyData['general']['gst_emission_factor_type_ipcc'] ? _('Fattori di emissione standard in linea con i principi IPCC') : _('Fattori LCA (valutazione del ciclo di vita)'); $emissionInventoryData[$i]['general']['gst_emission_unit_text'] = $globalStrategyData['general']['gst_emission_unit_co2'] ? _('Emissioni di CO2') : _('Emissioni equivalenti di CO2'); foreach ($inventoryTableKinds as $kind) { $emissionInventoryData[$i][$kind]['header'] = R3EcoGisGlobalTableHelper::getParameterList($kind, array('show_udm' => true)); $emissionInventoryData[$i][$kind]['rows'] = R3EcoGisGlobalTableHelper::getCategoriesData($ge_id, $kind, $udm_divider); } } } $ext = '.' . (isset($driverInfo[$request['driver']]['output_format']) ? $driverInfo[$request['driver']]['output_format'] : 'xlsx'); $fileName = R3_TMP_DIR . date('YmdHis') . '.' . md5(time()) . $ext; $opt = array('GENERAL' => $globalStrategyData, 'ACTION_PLAN' => $actionPlanData); for ($i = 1; $i <= 2; $i++) { if (isset($emissionInventoryData[$i])) { $opt["EMISSION_INVENTORY_{$i}"] = $emissionInventoryData[$i]; } } if ($globalStrategyData['general']['gp_id'] != '') { $opt['GLOBAL_PLAN'] = R3EcoGisGlobalPlainTableHelper::getData($this->do_id, $globalStrategyData['general']['gp_id']); } $opt['METADATA'] = array('creator' => $this->auth->getUserName(), 'title' => _('TEMPLATE') . ' - ' . _('POWER BY R3-EcoGIS 2')); $opt['SHEET-NAME'] = array('GENERAL' => _('Strategia generale'), 'EMISSION_INVENTORY_1' => _('Inventario base emissioni (1)'), 'EMISSION_INVENTORY_2' => _('Inventario base emissioni (2)'), 'ACTION_PLAN' => _("Piano d'azione SEAP")); $opt['logger'] = new R3ExportLogger(); // Close immediatly the session to allow concurrency session session_write_close(); $exportDriver->export($fileName, R3_SMARTY_TEMPLATE_DIR_DOC . $driverInfo[$request['driver']]['template'], $opt); $httpFileName = basename($fileName); $url = "getfile.php?type=tmp&file={$httpFileName}&disposition=download&name=PAES_" . date('Y-m-d') . $ext; return array('status' => R3_AJAX_NO_ERROR, 'url' => $url); }
static function getInventoryTotals($ge_id, array $opt = array(), array $type = array('EMISSION'), $divider = 1000) { //$ge_id_2=null, array $type=array('EMISSION'), $divider=1000) { $result = array(); R3EcoGisHelper::includeHelperClass('obj.global_result_table.php'); foreach ($type as $t) { $result[$t] = R3EcoGisGlobalTableHelper::getCategoriesData($ge_id, $t, $divider); // Remove unused data if (isset($result[$t]['data'])) { unset($result[$t]['data']); } if (isset($result[$t]['sum']['source'])) { unset($result[$t]['sum']['source']); } // Calcolo pro capite if (isset($opt['citizen']) && $opt['citizen'] > 0) { $result[$t]['sum']['total_citizen'] = $result[$t]['sum']['total'] / $opt['citizen']; } } return $result; }
/** * Return the data for a single customer */ public function getData($id = null) { $lang = R3Locale::getLanguageID(); if ($id === null) { $id = $this->id; } $db = ezcDbInstance::get(); $vlu = array(); if ($this->new_udm_divider != '') { $lastDivider = $this->auth->setConfigValue('SETTINGS', 'GLOBAL_RESULT_LAST_UDM_DIVIDER', $this->new_udm_divider, array('permanent' => true)); } $lastDivider = $this->auth->getConfigValue('SETTINGS', 'GLOBAL_RESULT_LAST_UDM_DIVIDER', 1); $this->udm_divider = initVar('udm_divider', $lastDivider); if ($this->act != 'add') { $sql = "SELECT mu_type FROM ecogis.global_entry_data WHERE ge_id=" . (int) $this->ge_id; $vlu['mu_type'] = $db->query($sql)->fetchColumn(); $vlu['udm_divider'] = $this->udm_divider; $vlu['merge_municipality_data'] = $this->merge_municipality_data; $vlu['header'] = R3EcoGisGlobalTableHelper::getParameterList($this->kind); $vlu['header']['parameter_count'] = R3EcoGisGlobalTableHelper::getParameterCount($this->kind); $vlu['data'] = R3EcoGisGlobalTableHelper::getCategoriesData($this->ge_id, $this->kind, $this->udm_divider, true, $this->gc_id, $this->merge_municipality_data); } else { $vlu = array(); } $this->data = $vlu; // Save the data (prevent multiple sql) return $vlu; }
public function loadEmissionDataFrominventory($ge_id) { $db = ezcDbInstance::get(); R3EcoGisHelper::includeHelperClass('obj.global_result_table.php'); // Emissioni $data = R3EcoGisGlobalTableHelper::getCategoriesData($ge_id, 'EMISSION', 1); $result['emission']['total'] = $data['table_sum']; // Consumi $consumptionData = R3EcoGisGlobalTableHelper::getCategoriesData($ge_id, 'CONSUMPTION', 1); // Ricavo ID PAES Elettricità (dovrebbe essere 1 nei db standard) $sql = "SELECT ges_id\r\n FROM global_energy_source ges\r\n INNER JOIN global_energy_type get ON ges.get_id=get.get_id WHERE get_code='ELECTRICITY'"; $electricityGesId = $db->query($sql)->fetchColumn(); if (!isset($data['sum']['source'][$electricityGesId])) { $result['consumption']['electricity'] = null; } else { $result['consumption']['electricity'] = isset($consumptionData['sum']['source'][$electricityGesId]) ? $consumptionData['sum']['source'][$electricityGesId] : 0; } $result['consumption']['total'] = $consumptionData['sum']['total']; // Produzione $productionData = R3EcoGisGlobalTableHelper::getCategoriesData($ge_id, 'ENERGY_PRODUCTION', 1); $result['production']['electricity'] = $productionData['production_sum']['tot']; $result['production']['production_emission'] = $productionData['production_emission_sum']['tot']; // Energia verde $sql = "SELECT ge_green_electricity_purchase, ge_green_electricity_co2_factor\r\n FROM global_entry\r\n WHERE ge_id=?"; $stmt = $db->prepare($sql); $stmt->execute(array($ge_id)); $data = $stmt->fetch(PDO::FETCH_ASSOC); $result['green_energy']['consumption'] = $data['ge_green_electricity_purchase']; $result['green_energy']['factor'] = $data['ge_green_electricity_co2_factor']; $result['green_energy']['emission'] = $result['green_energy']['consumption'] * $result['green_energy']['factor']; // Energia rinnovabile $sql = "SELECT ges.ges_id, ges_name_1 AS ges_name\r\nFROM global_energy_source ges\r\nINNER JOIN global_energy_type get ON ges.get_id=get.get_id\r\nINNER JOIN global_energy_source_type gest ON ges.ges_id=gest.ges_id\r\nINNER JOIN global_type gt ON gt.gt_id=gest.gt_id\r\nWHERE get_code='RENEWABLE' AND gt_code='CONSUMPTION'\r\nORDER BY gest_order"; $renewableConsumption = 0; foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) { $tot = 0; foreach ($consumptionData['data'] as $data) { if (isset($data['sum']['source'][$row['ges_id']])) { $tot += $data['sum']['source'][$row['ges_id']]; } } $gesIds[$row['ges_id']] = array('id' => $row['ges_id'], 'name' => $row['ges_name'], 'tot' => $tot); $renewableConsumption += $tot; } $result['green_energy']['production'] = $renewableConsumption; return $result; }