public function getElectricityUDMData($do_id)
{
$db = ezcDbInstance::get();
$esuData = R3EcoGisHelper::getEnergySourceAndUdm($do_id, 'ELECTRICITY');
$sql = "SELECT * " . "FROM ecogis.energy_source es " . "INNER JOIN ecogis.energy_type et ON es.et_id = et.et_id " . "INNER JOIN ecogis.energy_source_udm esu ON es.es_id = esu.es_id " . "INNER JOIN ecogis.udm ON esu.udm_id = udm.udm_id " . "WHERE esu.es_id={$esuData['es_id']} AND \r\n esu.udm_id={$esuData['udm_id']} AND \r\n et_code='ELECTRICITY' AND \r\n esu_is_private IS FALSE AND \r\n esu_is_consumption IS TRUE \r\n AND esu.do_id IS NULL";
return $db->query($sql)->fetch(PDO::FETCH_ASSOC);
}
public function loadNationalElectricityFactor($do_id)
{
$db = ezcDbInstance::get();
$esData = R3EcoGisHelper::getEnergySourceAndUdm($do_id, 'ELECTRICITY');
$sql = "SELECT esu_co2_factor\r\n FROM energy_source_udm\r\n WHERE es_id=? AND udm_id=? AND do_id=? AND esu_is_private IS FALSE AND mu_id IS NULL";
$stmt = $db->prepare($sql);
$stmt->execute(array($esData['es_id'], $esData['udm_id'], $_SESSION['do_id']));
$electricityConversionFactor = $stmt->fetch(PDO::FETCH_ASSOC);
if ($electricityConversionFactor === false) {
throw new Exception("Conversion factor for es_id={$esData['es_id']}, udm_id={$esData['udm_id']}, do_id={$_SESSION['do_id']} not found");
}
return $electricityConversionFactor['esu_co2_factor'];
}
public static function getProductionEnergySourceList($do_id)
{
$db = ezcDbInstance::get();
$lang = R3Locale::getLanguageID();
$opt = $defaultOptions = array('order' => 'gest_order, ges_order, ges_name, ges.ges_id, es_order, es_name, es.es_id, udm_order, udm_name, udm.udm_id');
// Ricavo fonte geotermica
$geotermic_es_id = $db->query("SELECT es_id FROM ecogis.energy_source WHERE es_uuid='8def63c2-1116-4426-8490-8270a258c98f'")->fetchColumn();
$esData = R3EcoGisHelper::getEnergySourceAndUdm($do_id, 'ELECTRICITY');
$sql = "SELECT ges.ges_id, ges_name_{$lang} AS ges_name, es.es_id, es_name_{$lang} AS es_name, udm.udm_id,\r\n udm.udm_name_{$lang} AS udm_name, esu_id, esu_kwh_factor, esu_co2_factor\r\n FROM ecogis.global_energy_source ges\r\n INNER JOIN ecogis.global_energy_source_type gest ON ges.ges_id=gest.ges_id\r\n INNER JOIN ecogis.global_type gt ON gt.gt_id=gest.gt_id AND gt_code='CONSUMPTION'\r\n INNER JOIN ecogis.energy_source_udm esu ON ges.ges_id=esu.ges_id\r\n INNER JOIN ecogis.energy_source es ON esu.es_id=es.es_id\r\n INNER JOIN ecogis.udm ON esu.udm_id=udm.udm_id\r\n WHERE esu.do_id IS NOT NULL AND esu.mu_id IS NULL AND esu.es_id IN ({$esData['es_id']}, {$geotermic_es_id})\r\n ORDER BY gest_order, ges_order, ges_name, ges.ges_id, es_order, es_name, es.es_id, udm_order, udm_name, udm.udm_id";
$result = array();
foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) {
$result[$row['ges_id']]['name'] = $row['ges_name'];
$result[$row['ges_id']]['source'][$row['es_id']]['name'] = $row['es_name'];
$result[$row['ges_id']]['source'][$row['es_id']]['udm'][$row['udm_id']] = array('name' => $row['udm_name'], 'id' => $row['esu_id'], 'kwh_factor' => $row['esu_kwh_factor'], 'co2_factor' => $row['esu_co2_factor']);
}
return $result;
}
public static function getEnergySourceAndUdm($do_id, $et_code)
{
return R3EcoGisHelper::getEnergySourceAndUdm($do_id, $et_code);
}
