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