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