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); }
/** * 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; }
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; }
private static function getCategoriesDataMunicipality($ge_id, $kind, $divider, $returnAsLocale, $gc_id) { $db = ezcDbInstance::get(); $lang = R3Locale::getLanguageID(); $ge_id = (int) $ge_id; $decimals = $divider == 1 ? 0 : 1; $em_is_production = $kind == 'ENERGY_PRODUCTION' || $kind == 'HEATH_PRODUCTION' ? 'T' : 'F'; $sql = "SELECT mu_id, ge_year, ge_national_efe, ge_local_efe FROM global_entry WHERE ge_id={$ge_id}"; list($mu_id, $year, $nationalEFE, $localEFE) = $db->query($sql)->fetch(PDO::FETCH_NUM); $mu_id = (int) $mu_id; $year = (int) $year; // EFE migliore: Locale inventario, nazionale inventario, locale globale, nazionale if ($localEFE != '') { $efe = $localEFE; } else { if ($nationalEFE != '') { $efe = $nationalEFE; } else { $efe = R3EcoGisHelper::getElectricityCO2Factor($_SESSION['do_id'], $mu_id); } } $sql = "SELECT gest_id, get.get_id, BOOL2TEXT(get_show_label) AS get_show_label, get_name_{$lang} AS get_name, ges.ges_id, ges_name_{$lang} AS ges_name\r\n FROM ecogis.global_type gt\r\n INNER JOIN ecogis.global_energy_source_type gest ON gt.gt_id=gest.gt_id\r\n INNER JOIN ecogis.global_energy_source ges ON gest.ges_id=ges.ges_id\r\n INNER JOIN ecogis.global_energy_type get ON get.get_id=ges.get_id\r\n WHERE gt_code='{$kind}'\r\n ORDER BY gest_order, get_order, ges_order"; $parameters = array(); $globalSumSourceDefault = array(); foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) { $parameters[$row['ges_id']] = $row; $globalSumSourceDefault[$row['ges_id']] = null; } $sql = "SELECT gc1.gc_id AS main_id, gc1.gc_code AS main_code, gc1.gc_name_{$lang} AS main_name, gc1.gc_show_label AS main_show_label,\r\n gc2.gc_id AS gc_id, gc2.gc_code AS gc_code, gc2.gc_name_{$lang} AS gc_name, gc2.gc_total_only\r\n FROM global_category gc1\r\n INNER JOIN global_category gc2 ON gc2.gc_parent_id=gc1.gc_id\r\n INNER JOIN ecogis.global_category_type gcat ON gc2.gc_id=gcat.gc_id\r\n INNER JOIN global_type gt ON gt.gt_id=gcat.gt_id\r\n WHERE gt_code='{$kind}' "; if ($gc_id !== null) { $gc_id = (int) $gc_id; $sql .= " AND gc2.gc_id={$gc_id} "; } $sql .= "ORDER BY gc1.gc_order, gc1.gc_name_{$lang}, gc1.gc_id, gcat_order, gc2.gc_order, gc2.gc_name_{$lang}, gc2.gc_id"; $categories = array(); foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) { $categories[$row['gc_id']] = $row; } $fieldName = $kind == 'EMISSION' ? 'co_value_co2' : 'co_value_kwh'; $data = array(); $buildingProduction = array(); //Ricavo dati inseriti da form (non edifici e non illuminazione) e i totali $sql = "SELECT 'GLOBAL' AS kind, ge_id, gs_id, gs_name_{$lang} AS gs_name, gc_id, ges_id, co_value_kwh, co_value_co2, NULL AS gs_tot_value, the_geom IS NOT NULL AS has_geometry\r\n FROM ecogis.consumption_year_global\r\n WHERE mu_id={$mu_id} AND ge_id={$ge_id} AND ge_year={$year}\r\n\r\n UNION\r\n\r\n SELECT 'GLOBAL' AS kind, ge_id, gs_id, gs_name_{$lang} AS gs_name, gc.gc_id, NULL AS ges_id, NULL AS co_value_kwh, NULL AS co_value_co2, gs_tot_value, the_geom IS NOT NULL AS has_geometry\r\n FROM ecogis.global_subcategory gs\r\n INNER JOIN ecogis.global_category gc ON gs.gc_id=gc.gc_id\r\n WHERE ge_id={$ge_id} \r\n\r\n ORDER BY gs_name"; foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) { $gcIdList[$row['gc_id']] = $row['gc_id']; $data[$row['gc_id']][$row['gs_id']]['header'] = array('kind' => $row['kind'], 'id' => $row['gs_id'], 'name' => $row['gs_name'], 'sum' => R3EcoGisGlobalTableHelper::applyDivider($row['gs_tot_value'], $divider), 'co2_sum' => null, 'has_geometry' => $row['has_geometry']); if ($row['ges_id'] != '') { if ($kind == 'EMISSION' && self::getEnergyTypeBySourceId($row['ges_id']) == 'ELECTRICITY') { $data[$row['gc_id']][$row['gs_id']]['data'][$row['ges_id']] = R3EcoGisGlobalTableHelper::applyDivider($efe * $row['co_value_kwh'], $divider); } else { $data[$row['gc_id']][$row['gs_id']]['data'][$row['ges_id']] = R3EcoGisGlobalTableHelper::applyDivider($row[$fieldName], $divider); } $data[$row['gc_id']][$row['gs_id']]['co2_value'][$row['ges_id']] = $row['co_value_co2']; } } // Ricavo dati edifici $sql = "SELECT 'BUILDING' AS kind, 10000000+bu_id as bu_id, bu_name_{$lang} AS bu_name, gc_id, ges_id, co_value_kwh, co_value_co2, the_geom IS NOT NULL AS has_geometry\r\n FROM consumption_year_building \r\n WHERE mu_id={$mu_id} AND co_year={$year} AND ges_id IS NOT NULL AND em_is_production='{$em_is_production}'\r\n ORDER BY bu_name"; foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) { if ($em_is_production) { $buildingProduction[$row['gc_id']][$row['bu_id']] = $row['co_value_kwh']; } $data[$row['gc_id']][$row['bu_id']]['header'] = array('kind' => $row['kind'], 'id' => $row['bu_id'], 'name' => $row['bu_name'], 'sum' => null, 'co2_sum' => null, 'has_geometry' => $row['has_geometry']); if ($kind == 'EMISSION' && $row['co_value_co2'] > 0 && self::getEnergyTypeBySourceId($row['ges_id']) == 'ELECTRICITY') { // Applico efe locale o nazionale se presenti nell'inventario $data[$row['gc_id']][$row['bu_id']]['data'][$row['ges_id']] = R3EcoGisGlobalTableHelper::applyDivider($efe * $row['co_value_kwh'], $divider); } else { $data[$row['gc_id']][$row['bu_id']]['data'][$row['ges_id']] = R3EcoGisGlobalTableHelper::applyDivider($row[$fieldName], $divider); } } //Ricavo dati illuminazione pubblica $sql = "SELECT 'STREET_LIGHTING' AS kind, 11000000+sl_id as sl_id, sl_full_name_{$lang} AS sl_name, gc_id, ges_id, co_value_kwh, co_value_co2, the_geom IS NOT NULL AS has_geometry\r\n FROM consumption_year_street_lighting\r\n WHERE mu_id={$mu_id} AND co_year={$year} AND ges_id IS NOT NULL AND em_is_production='{$em_is_production}'\r\n ORDER BY sl_name"; foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) { $data[$row['gc_id']][$row['sl_id']]['header'] = array('kind' => $row['kind'], 'id' => $row['sl_id'], 'name' => $row['sl_name'], 'sum' => null, 'co2_sum' => null, 'has_geometry' => $row['has_geometry']); if ($kind == 'EMISSION' && self::getEnergyTypeBySourceId($row['ges_id']) == 'ELECTRICITY') { $data[$row['gc_id']][$row['sl_id']]['data'][$row['ges_id']] = R3EcoGisGlobalTableHelper::applyDivider($efe * $row['co_value_kwh'], $divider); } else { $data[$row['gc_id']][$row['sl_id']]['data'][$row['ges_id']] = R3EcoGisGlobalTableHelper::applyDivider($row[$fieldName], $divider); } } // Ricavo i dati aggiuntivi per produzione elettricità e calore/freddo (Nel db i valori sono in kWH) $sql = "SELECT gs.gc_id, gs_id, gs_tot_production_value, gs_tot_emission_value, gs_tot_emission_factor\r\n FROM global_subcategory gs\r\n INNER JOIN global_category gc on gs.gc_id=gc.gc_id\r\n INNER JOIN global_category_type gcat on gc.gc_id=gcat.gc_id\r\n INNER JOIN global_type gt on gt.gt_id=gcat.gt_id\r\n WHERE gt_code='{$kind}' AND ge_id={$ge_id} AND gs_tot_production_value IS NOT NULL"; $productionData = array(); $productionSum = array(); $productionEmissionSum = array(); $productionEmissionSumFactor = array(); // Imposto array (serve per export) foreach ($categories as $gc_id => $dummy) { $productionSum['category'][$gc_id] = null; $productionEmissionSum['category'][$gc_id] = null; } // Add building production data $productionTot = 0; $productionEmissionTot = 0; foreach ($buildingProduction as $gc_id => $buildingProductionData) { foreach ($buildingProductionData as $bu_id => $val) { $val = R3EcoGisGlobalTableHelper::applyDivider($val, $divider); if (isset($categories[$gc_id])) { $productionData[$gc_id][$bu_id]['production'] = $returnAsLocale ? R3NumberFormat($val, $decimals, true) : $val; if (!isset($productionSum['category'][$gc_id])) { $productionSum['category'][$gc_id] = 0; $productionEmissionSum['category'][$gc_id] = 0; } $productionSum['category'][$gc_id] += $val; $productionTot += $val; } } } $canSumFactor = array(); foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) { $val = R3EcoGisGlobalTableHelper::applyDivider($row['gs_tot_production_value'], $divider); $emissionVal = R3EcoGisGlobalTableHelper::applyDivider($row['gs_tot_emission_value'], $divider); $emissionFactorVal = R3EcoGisGlobalTableHelper::applyDivider($row['gs_tot_emission_factor'], $divider); $productionData[$row['gc_id']][$row['gs_id']]['production'] = $returnAsLocale ? R3NumberFormat($val, $decimals, true) : $val; $productionData[$row['gc_id']][$row['gs_id']]['production_emission'] = $returnAsLocale ? R3NumberFormat($emissionVal, $decimals, true) : $emissionVal; $productionData[$row['gc_id']][$row['gs_id']]['production_emission_factor'] = $returnAsLocale ? R3NumberFormat($emissionFactorVal, $decimals, true) : $emissionFactorVal; if (!isset($productionSum['category'][$row['gc_id']])) { $productionSum['category'][$row['gc_id']] = 0; $productionEmissionSum['category'][$row['gc_id']] = 0; } $productionSum['category'][$row['gc_id']] += $val; $productionEmissionSum['category'][$row['gc_id']] += $emissionVal; // Solo se ho un singolo entry posso sommare i fattori di conversione if (!isset($canSumFactor[$row['gc_id']])) { $canSumFactor[$row['gc_id']] = $emissionVal; $productionEmissionSumFactor['category'][$row['gc_id']] = $emissionFactorVal; } else { $productionEmissionSumFactor['category'][$row['gc_id']] = 'N/A'; } $productionTot += $val; $productionEmissionTot += $emissionVal; } $productionSum['tot'] = $returnAsLocale ? R3NumberFormat($productionTot, $decimals, true) : $productionTot; $productionEmissionSum['tot'] = $returnAsLocale ? R3NumberFormat($productionEmissionTot, $decimals, true) : $productionEmissionTot; if (isset($productionSum['category']) && $returnAsLocale) { foreach ($productionSum['category'] as $key => $val) { $productionSum['category'][$key] = R3NumberFormat($val, $decimals, true); } foreach ($productionEmissionSum['category'] as $key => $val) { $productionEmissionSum['category'][$key] = R3NumberFormat($val, $decimals, true); } } // Generazione tabella $result = array(); foreach ($categories as $gc_id => $cat) { $result[$cat['main_id']]['code'] = $cat['main_code']; $result[$cat['main_id']]['name'] = $cat['main_name']; $result[$cat['main_id']]['sub_total'] = 'T'; $result[$cat['main_id']]['sub_total_label'] = _('Totale parziale') . ' ' . mb_strtolower($cat['main_name'], 'UTF-8'); $result[$cat['main_id']]['show_label'] = $cat['main_show_label'] ? 'T' : 'F'; $result[$cat['main_id']]['options']['xls_style'] = 'category-header'; $result[$cat['main_id']]['options']['xls_style_sub_total_header'] = 'subtotal-header'; $result[$cat['main_id']]['options']['xls_style_sub_total_data'] = 'subtotal-data'; $result[$cat['main_id']]['options']['xls_style_sub_total_data_sum'] = 'subtotal-data-sum'; $result[$cat['main_id']]['options']['xls_style_category'] = 'category'; $result[$cat['main_id']]['options']['xls_style_category_data'] = 'category-data'; $result[$cat['main_id']]['options']['xls_style_category_sum'] = 'category-sum'; $result[$cat['main_id']]['sum'] = array(); $result[$cat['main_id']]['categories'][$cat['gc_id']]['header'] = array('id' => $cat['gc_id'], 'code' => $cat['gc_code'], 'name' => $cat['gc_name'], 'total_only' => $cat['gc_total_only'] ? 'T' : 'F', 'sum' => '', 'method' => self::getGlobalMethod($ge_id, $cat['gc_id'])); if (isset($data[$gc_id])) { $row = array(); $sum = array(); foreach ($parameters as $ges_id => $dummy) { $sum[$ges_id] = ''; } foreach ($data[$gc_id] as $id => $data2) { $row[$id]['header'] = $data2['header']; $row[$id]['header']['sum'] = ''; foreach ($parameters as $ges_id => $param) { if (isset($data2['data'][$ges_id])) { $row[$id]['data'][$ges_id] = $data2['data'][$ges_id]; if (isset($data2['co2_value'][$ges_id])) { $row[$id]['co2_value'][$ges_id] = $data2['co2_value'][$ges_id]; } else { $row[$id]['co2_value'][$ges_id] = 0; } $row[$id]['header']['sum'] += $data2['data'][$ges_id]; $sum[$ges_id] += $data2['data'][$ges_id]; $result[$cat['main_id']]['categories'][$cat['gc_id']]['header']['sum'] += $data2['data'][$ges_id]; } else { $row[$id]['data'][$ges_id] = ''; } } if ($data2['header']['sum'] != '') { $row[$id]['header']['sum'] = $data2['header']['sum'] == '' ? '' : $data2['header']['sum']; $result[$cat['main_id']]['categories'][$cat['gc_id']]['header']['sum'] += $data2['header']['sum']; } } // Check sum $result[$cat['main_id']]['categories'][$cat['gc_id']]['sum'] = $sum; $result[$cat['main_id']]['categories'][$cat['gc_id']]['sub_categories'] = $row; } else { $sum = array(); foreach ($parameters as $ges_id => $dummy) { $sum[$ges_id] = ''; } $result[$cat['main_id']]['categories'][$cat['gc_id']]['sum'] = $sum; } } $tableSum = self::getTableSum($result); // Formatto numeri $mainCategorySum = array(); $globalSum = array('label' => _('Totale'), 'total' => null, 'source' => $globalSumSourceDefault); foreach ($result as $key1 => $val1) { $mainCategorySum[$key1]['total'] = null; foreach ($val1['categories'] as $key2 => $val2) { // Totale di categoria $mainCategorySum[$key1]['total'] += $result[$key1]['categories'][$key2]['header']['sum']; // totale $globalSum['total'] += $result[$key1]['categories'][$key2]['header']['sum']; // totale $result[$key1]['categories'][$key2]['header']['sum'] = $returnAsLocale ? R3NumberFormat($result[$key1]['categories'][$key2]['header']['sum'], $decimals, true) : $result[$key1]['categories'][$key2]['header']['sum']; foreach ($val2['sum'] as $key3 => $val3) { // Totale parziale categoria $result[$key1]['categories'][$key2]['sum'][$key3] = $returnAsLocale ? R3NumberFormat($result[$key1]['categories'][$key2]['sum'][$key3], $decimals, true) : $result[$key1]['categories'][$key2]['sum'][$key3]; } if (isset($val2['sub_categories'])) { foreach ($val2['sub_categories'] as $key3 => $val3) { // Totale sottocategoria $result[$key1]['categories'][$key2]['sub_categories'][$key3]['header']['sum'] = $returnAsLocale ? R3NumberFormat($result[$key1]['categories'][$key2]['sub_categories'][$key3]['header']['sum'], $decimals, true) : $result[$key1]['categories'][$key2]['sub_categories'][$key3]['header']['sum']; foreach ($val3['data'] as $key4 => $val4) { if (!isset($mainCategorySum[$key1]['source'][$key4])) { $mainCategorySum[$key1]['source'][$key4] = null; } if (!isset($globalSum['source'][$key4])) { $globalSum['source'][$key4] = null; } if ($result[$key1]['categories'][$key2]['sub_categories'][$key3]['data'][$key4] != null) { $mainCategorySum[$key1]['source'][$key4] += $result[$key1]['categories'][$key2]['sub_categories'][$key3]['data'][$key4]; $globalSum['source'][$key4] += $result[$key1]['categories'][$key2]['sub_categories'][$key3]['data'][$key4]; } // Dato $result[$key1]['categories'][$key2]['sub_categories'][$key3]['data'][$key4] = $returnAsLocale ? R3NumberFormat($result[$key1]['categories'][$key2]['sub_categories'][$key3]['data'][$key4], $decimals, true) : $result[$key1]['categories'][$key2]['sub_categories'][$key3]['data'][$key4]; } } } } $result[$key1]['sum'] = $mainCategorySum[$key1]; } if ($returnAsLocale) { // Conversione in locale foreach ($result as $key => $val) { $result[$key]['sum']['total'] = R3NumberFormat($result[$key]['sum']['total'], $decimals, true); if (isset($val['sum']['source'])) { foreach ($val['sum']['source'] as $key2 => $val2) { $result[$key]['sum']['source'][$key2] = R3NumberFormat($val2, $decimals, true); } } } $globalSum['total'] = R3NumberFormat($globalSum['total'], $decimals, true); if (isset($globalSum['source'])) { foreach ($globalSum['source'] as $key => $val) { $globalSum['source'][$key] = R3NumberFormat($val, $decimals, true); } } } return array('data' => $result, 'table_sum' => $tableSum, 'sum' => $globalSum, 'production_data' => $productionData, 'production_sum' => $productionSum, 'production_emission_sum' => $productionEmissionSum, 'production_emission_sum_factor' => $productionEmissionSumFactor); }
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; }