public static function getGaugeData($gpr_id)
{
$db = ezcDbInstance::get();
$lang = R3Locale::getLanguageID();
$gpr_id = (int) $gpr_id;
$sql = "SELECT gpr_gauge_type FROM ecogis.global_plain_row WHERE gpr_id={$gpr_id}";
$gaugeType = $db->query($sql)->FetchColumn();
switch ($gaugeType) {
case 'P':
// Percentuale
$sql = "SELECT gpm_id, gpm_date, gpm_value_1, gpm_value_2\r\n FROM ecogis.global_plain_monitor gpm\r\n WHERE gpr_id={$gpr_id}\r\n ORDER BY gpm_date";
$result = array();
$result['P']['header'] = array('title' => _('% Completamento azione'), 'unit' => '%', 'unit_value' => 1);
foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) {
$result['P']['data'][$row['gpm_id']] = array('gpg_id' => null, 'date' => $row['gpm_date'], 'date_fmt' => SQLDateToStr($row['gpm_date'], 'd/m/Y'), 'value_1' => $row['gpm_value_1'], 'value_1_fmt' => R3NumberFormat($row['gpm_value_1'], 2), 'value_2' => $row['gpm_value_2'], 'value_2_fmt' => R3NumberFormat($row['gpm_value_2'], 2), 'tot_value' => $row['gpm_value_1'] * 1, 'tot_value_fmt' => R3NumberFormat($row['gpm_value_1'] * 1, 2));
}
break;
case 'G':
// Indicatore
$sql = "SELECT gpg.gpg_id, gpg_name_{$lang} AS gpg_name, gpgu_1.gpgu_name_{$lang} AS gpgu_name_1, \r\n gpgu_2.gpgu_name_{$lang} AS gpgu_name_2, gpg_value_1, gpg_value_2, gpg_value_3, gpm_id, gpm_date, \r\n gpm_value_1, gpm_value_2\r\n FROM ecogis.global_plain_gauge gpg\r\n LEFT JOIN ecogis.global_plain_gauge_udm gpgu_1 ON gpg.gpgu_id_1=gpgu_1.gpgu_id\r\n LEFT JOIN ecogis.global_plain_gauge_udm gpgu_2 ON gpg.gpgu_id_2=gpgu_2.gpgu_id\r\n LEFT JOIN ecogis.global_plain_monitor gpm ON gpg.gpg_id=gpm.gpg_id\r\n WHERE gpg.gpr_id={$gpr_id}\r\n ORDER BY gpg_name, gpm_date";
$sql = "SELECT gpg_id, gpg_name_{$lang} AS gpg_name, gpgu_name_1_{$lang} AS gpgu_name_1, \r\n gpgu_name_2_{$lang} AS gpgu_name_2, gpg_value_1, gpg_value_2, gpg_value_3, gpm_id, gpm_date, \r\n gpm_value_1, gpm_value_2\r\n FROM ecogis.global_plain_gauge_full_data\r\n WHERE gpr_id={$gpr_id}\r\n ORDER BY gpg_name, gpm_date";
$result = array();
foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) {
$result[$row['gpg_id']]['header'] = array('title' => $row['gpg_name'], 'unit_1' => $row['gpgu_name_1'], 'unit_2' => $row['gpgu_name_2'], 'value_1' => $row['gpg_value_1'], 'value_2' => $row['gpg_value_2'], 'value_3' => $row['gpg_value_3']);
$energyVariation = self::calculateEnergyVariation($row['gpg_value_1'], $row['gpg_value_2'], $row['gpm_value_2'], $row['gpm_value_1']);
$emissionVariation = self::calculateEmissionVariation($energyVariation, $row['gpg_value_3']);
$result[$row['gpg_id']]['data'][$row['gpm_id']] = array('gpg_id' => $row['gpg_id'], 'date' => $row['gpm_date'], 'date_fmt' => SQLDateToStr($row['gpm_date'], 'd/m/Y'), 'value_1' => $row['gpm_value_1'], 'value_1_fmt' => R3NumberFormat($row['gpm_value_1'], 2), 'value_2' => $row['gpm_value_2'], 'value_2_fmt' => R3NumberFormat($row['gpm_value_2'], 2), 'energy_variation' => $energyVariation, 'energy_variation_fmt' => R3NumberFormat($energyVariation, 2), 'emission_variation' => $emissionVariation, 'emission_variation_fmt' => R3NumberFormat($emissionVariation, 2));
}
break;
default:
// No action
return null;
}
return $result;
}
public function convert2Locale($vlu)
{
if (!is_array($vlu)) {
return $vlu;
}
setLangInfo(array('thousands_sep' => "."));
foreach ($this->fields as $key => $field) {
if (isset($field['name'])) {
$key = $field['name'];
if (isset($field['dec']) && !isset($field['precision'])) {
$field['precision'] = $field['dec'];
}
if (array_key_exists($key, $vlu)) {
if ($vlu[$key] === null) {
$vlu[$key] = '';
} else {
$type = strToLower($field['type']);
switch ($field['type']) {
case 'real':
case 'double':
case 'number':
case 'float':
$vlu[$key] = R3NumberFormat($vlu[$key], isset($field['precision']) ? $field['precision'] : null, true);
break;
case 'integer':
$vlu[$key] = R3NumberFormat($vlu[$key], isset($field['precision']) ? $field['precision'] : 0, true);
break;
case 'date':
$vlu[$key] = SQLDateToStr($vlu[$key], R3Locale::getPhpDateFormat());
break;
case 'datetime':
case 'now':
$vlu[$key] = SQLDateToStr($vlu[$key], R3Locale::getPhpDateTimeFormat());
break;
case 'boolean':
if ($vlu[$key] === true) {
$vlu[$key] = 'T';
} else {
if ($vlu[$key] === false) {
$vlu[$key] = 'F';
}
}
}
}
}
}
}
return $vlu;
}
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);
}
public function CreateTableRow($row, $links = null, $styles = null, $events = null, $id = null)
{
// Replace Standart Styles
$class_normal = $this->baseclass . '_normal';
$class_over = $this->baseclass . '_over';
$class_td = $this->baseclass . '_td';
if (count($styles) > 0) {
if (array_key_exists('normal', $styles)) {
if (is_array($styles['normal'])) {
$class_normal = implode(' ', $styles['normal']);
} else {
$class_normal = $styles['normal'];
}
}
if (array_key_exists('over', $styles)) {
$class_over = $styles['over'];
}
if (array_key_exists('td', $styles)) {
$class_td = $styles['td'];
}
}
// Get Additional Events
$AddEvents = '';
if ($events !== null && count($events) > 0) {
while (list($event, $action) = each($events)) {
if (strtolower($event) != 'onmouseover' && strtolower($event) != 'onmouseout') {
$AddEvents = " {$event}=\"{$action};\" ";
}
}
}
if ($id !== null) {
$id = "id=\"{$id}\" ";
}
$res = "<tr {$id}class=\"{$class_normal}\" onmouseover=\"this.className='{$class_over}';\" onmouseout=\"this.className='{$class_normal}';\" {$AddEvents}>\n";
$cont = 0;
foreach ($this->fields as $field) {
$hint = '';
if ($field['visible']) {
$type = strtoupper($field['type']);
if ($type == 'LINK') {
$value = $this->CreateLinksCell($links);
$res .= " <td class=\"{$class_td}\" align=\"center\">{$value}</td>\n";
} else {
if ($field['align'] == '') {
$align = $this->GetDefaultAlign($field['type']);
} else {
$align = strtolower($field['align']);
}
if ($field['type'] != 'CALCULATED') {
// if (is_array($row)) $value = $row[$field['field']];
// else if (is_object($row)) $value = $row->getField($field['field']);
if (is_array($row)) {
$value = $row[$field['field']];
} else {
$value = $row->getField($field['field']);
}
// PH: do not escape anything different from a string
// TODO: check if a not empty string is returned as empty string. Give a warning mentioning the charset in that case
if ($this->SetHtmlEntities && is_string($value)) {
$value = HtmlEntities($value, ENT_QUOTES, $this->charset);
}
$format = $field['format'];
$number_format = $field['number_format'];
// Corregge il campo data
if ($field['type'] == 'DATE') {
if ($value == '0000-00-00') {
$value = '';
} else {
$value = SQLDateToStr($value, $format === false ? 'd/m/Y' : $format);
}
} else {
if ($field['type'] == 'TIME') {
if ($value == '00:00:00') {
$value = '';
} else {
$value = SQLDateToStr($value, $format === false ? 'H:i:s' : $format);
}
} else {
if ($field['type'] == 'DATETIME') {
if ($value == '0000-00-00 00:00:00') {
$value = '';
} else {
$value = SQLDateToStr($value, $format === false ? 'd/m/Y H:i:s' : $format);
}
} else {
if (strpos($field['type'], 'URL') !== false) {
if (strpos($field['type'], 'MAILTO') !== false) {
$value = sprintf("<a href=\"mailto:%s\">%s</a>", $value, $value);
} else {
$value = sprintf("<a href=\"%s\" target=\"_BLANK\">%s</a>", $value, $value);
}
} else {
if ($field['type'] == 'EURO') {
// DEPRECATED!
$value = valueToEuro($value);
} else {
if ($field['type'] == 'CHECKBOX') {
if (!isset($_SESSION[$this->name][$field['field']])) {
$_SESSION[$this->name][$field['field']] = array();
}
if (!isset($_SESSION[$this->name][$field['field'] . '_off'])) {
$_SESSION[$this->name][$field['field'] . '_off'] = array();
}
if (in_array($value, $_SESSION[$this->name][$field['field']]) || $_SESSION[$this->name][$field['field'] . '_header'] == 'T' && !in_array($value, $_SESSION[$this->name][$field['field'] . '_off'])) {
$checked = 'checked';
} else {
$checked = '';
}
// if (1==1) $checked = 'checked';
// else $checked = '';
// $value = "<input type=\"checkbox\" name=\"" . $field['field'] . "[]\" id=\"" . $field['field'] . "_$value\" value=\"$value\" $checked onclick=\"simpletable_onCheckboxClick(this, '" . $field['field'] . "', '" . $value . "')\">";
$tmp_value = '';
// - checkbox
$tmp_value .= "<input type=\"checkbox\" name=\"" . $field['field'] . "[]\" id=\"" . $field['field'] . "_{$value}\" value=\"{$value}\" onClick=\"simpletable_setHeaderCheckboxStatus(this);\" {$checked}>";
// - hidden var (to store/remove old status)
$tmp_value .= "<input type=\"hidden\" name=\"" . $field['field'] . "_hd[]\" id=\"" . $field['field'] . "_hd_{$value}\" value=\"{$value}\">";
$value = $tmp_value;
//if (isset($field['header_checkbox']) && $field['header_checkbox'] == true) {
//$label = "<input type=\"checkbox\" name=\"" . $field['field'] . "_header\" value=\"T\" onclick='simpletable_onHeaderCheckboxClick()'>" . $label;
//unset($field['sortable']);
//}
} else {
// Format or number_format
if ($value != '') {
if ($format !== false) {
$value = sprintf($format, $value);
} else {
if ($number_format !== false) {
if (!defined("__R3_LOCALE__")) {
require_once 'r3locale.php';
}
$localeInfo = getLocaleInfo();
if (!is_array($number_format)) {
$number_format = array('decimals' => $number_format, 'dec_point' => $localeInfo['decimal_point'], 'thousands_sep' => $localeInfo['thousands_sep']);
} else {
$number_format = array_merge(array('decimals' => 0, 'dec_point' => $localeInfo['decimal_point'], 'thousands_sep' => $localeInfo['thousands_sep']), $number_format);
}
if ($number_format['decimals'] === null && is_numeric($value)) {
$diff = round($value - (int) $value, 10);
if ($diff == 0) {
$number_format['decimals'] = 0;
} else {
$number_format['decimals'] = strlen($diff) - 2;
// -2 is 0. of the number
}
}
$value = number_format($value, $number_format['decimals'], $number_format['dec_point'], $number_format['thousands_sep']);
}
}
}
}
}
}
}
}
}
} else {
// Calculated fields
if (isset($this->calcvalues[$cont]['value'])) {
$value = $this->calcvalues[$cont]['value'];
} else {
$value = '';
}
if (isset($this->calcvalues[$cont]['hint'])) {
$hint = 'title="' . htmlspecialchars($this->calcvalues[$cont]['hint']) . '"';
}
}
// Replace spaces from value with
if ($field['type'] != 'CALCULATED' && $field['type'] != 'CHECKBOX' && strpos($field['type'], 'URL') === false && $field['wrap'] != true) {
$value = str_replace(" ", " ", $value);
}
// add new line
if ($field['wrap']) {
$value = nl2br($value);
}
$res .= " <td class=\"{$class_td}\" align=\"{$align}\" {$hint}>{$value}</td>\n";
}
}
$cont++;
}
$res .= "</tr>\n";
// Clear calculated array
unset($this->calcvalues);
return $res;
}
public function convert2Locale(array $vlu)
{
// Change to locale
$oldMessageLocale = setlocale(LC_MESSAGES, 0);
$oldNumericLocale = setlocale(LC_NUMERIC, 0);
setlocale(LC_NUMERIC, getLocaleInfo(R3Locale::getLanguageCode()));
setLangInfo(array('thousands_sep' => "."));
foreach ($this->fields as $key => $field) {
if (isset($field['name'])) {
$key = $field['name'];
if (isset($field['dec']) && !isset($field['precision'])) {
$field['precision'] = $field['dec'];
}
if (isset($field['is_primary_key']) && $field['is_primary_key'] === true) {
continue;
}
if (array_key_exists($key, $vlu)) {
if ($vlu[$key] === null) {
$vlu[$key] = '';
// IE Fix in json response
} else {
$type = strToLower($field['type']);
switch ($field['type']) {
case 'real':
case 'double':
case 'number':
case 'float':
$vlu[$key] = R3NumberFormat($vlu[$key], isset($field['precision']) ? $field['precision'] : null, true);
break;
case 'integer':
$vlu[$key] = R3NumberFormat($vlu[$key], isset($field['precision']) ? $field['precision'] : 0, true);
break;
case 'date':
$vlu[$key] = SQLDateToStr($vlu[$key], R3Locale::getPhpDateFormat());
break;
case 'datetime':
case 'now':
$vlu[$key] = SQLDateToStr($vlu[$key], R3Locale::getPhpDateTimeFormat());
break;
case 'boolean':
if ($vlu[$key] === true) {
$vlu[$key] = 'T';
} else {
if ($vlu[$key] === false) {
$vlu[$key] = 'F';
}
}
}
}
}
}
}
setlocale(LC_MESSAGES, $oldMessageLocale);
setlocale(LC_NUMERIC, $oldNumericLocale);
return $vlu;
}
public static function getChangeLogData($table, $row_id)
{
$db = ezcDbInstance::get();
if (strpos($table, '.') === false) {
$schema = 'ecogis';
// Get from DB connection
} else {
list($schema, $table) = explode('.', $table);
}
$sql = "SELECT get_log_table(" . $db->quote($schema) . ", " . $db->quote($table) . ")";
$clt_id = (int) $db->query($sql)->fetchColumn();
$sql = "SELECT cl_user, cl_action, cl_timestamp, us_login, us_name\r\n FROM ecogis.change_log cl\r\n LEFT JOIN auth.users us ON cl.cl_user=us.us_id\r\n WHERE clt_id={$clt_id} AND cl_row_id=" . (int) $row_id;
$result = array('ins_user_id' => null, 'ins_user_login' => null, 'ins_user_name' => null, 'ins_user' => null, 'ins_date' => null, 'ins_date_fmt' => null, 'mod_user_id' => null, 'mod_user_login' => null, 'mod_user_name' => null, 'mod_user' => null, 'mod_date' => null, 'mod_date_fmt' => null);
$result['last_change_time'] = null;
foreach ($db->query($sql, PDO::FETCH_ASSOC) as $row) {
if ($row['cl_action'] == 'I') {
$result['ins_user_id'] = $row['cl_user'];
$result['ins_user_login'] = $row['us_login'];
$result['ins_user'] = $result['ins_user_name'] = $row['us_name'];
$result['ins_date'] = $row['cl_timestamp'];
$result['ins_date_fmt'] = SQLDateToStr($row['cl_timestamp'], 'd/m/Y H:i');
$result['last_change_time'] = SQLDateToStr($row['cl_timestamp'], 'YmdHis');
} else {
if ($row['cl_action'] == 'U') {
$result['mod_user_id'] = $row['cl_user'];
$result['mod_user_login'] = $row['us_login'];
$result['mod_user'] = $result['mod_user_name'] = $row['us_name'];
$result['mod_date'] = $row['cl_timestamp'];
$result['mod_date_fmt'] = SQLDateToStr($row['cl_timestamp'], 'd/m/Y H:i');
$result['last_change_time'] = SQLDateToStr($row['cl_timestamp'], 'YmdHis');
}
}
}
return $result;
}
/**
* Return the filter values (list form)
*/
public function getFilterValues()
{
$filters = array();
$filters = array();
if ($this->auth->getParam('mu_id') == '') {
$filters['pr_values'] = R3EcoGisHelper::getProvinceList($this->do_id, array('join_with_simulation' => true));
$filters['mu_values'] = R3EcoGisHelper::getMunicipalityAndMunicipalityCollectionList($this->do_id, null, null, array('join_with_simulation' => true));
} else {
$filters['mu_values'] = array($this->auth->getParam('mu_id') => '');
}
if (count($filters['mu_values']) == 1) {
$mu_id = key($filters['mu_values']);
$filters['fr_values'] = R3EcoGisHelper::getFractionList($this->do_id, $mu_id, array('used_by' => 'building'));
$filters['st_values'] = R3EcoGisHelper::getStreetList($this->do_id, $mu_id, array('used_by' => 'building'));
} else {
$mu_id = null;
}
$filters['do_id'] = $this->do_id;
$filters['pr_id'] = $this->pr_id;
$filters['mu_id'] = $this->mu_id;
$filters['mu_name'] = $this->mu_name;
$filters['sw_title'] = $this->sw_title;
$filters['sw_date_from'] = SQLDateToStr($this->sw_date_from, R3Locale::getPhpDateFormat());
$filters['sw_date_to'] = SQLDateToStr($this->sw_date_to, R3Locale::getPhpDateFormat());
return $filters;
}
