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;
}
