public function prepareFieldValue($act, $key, $value, $type, $size = null, $default = null)
{
$db = ezcDbInstance::get();
switch ($type) {
case 'real':
case 'double':
case 'number':
case 'float':
if ((string) $value == '') {
return $this->null2text($default);
}
if (!is_numeric($value)) {
$value = forceFloat($value, null, '.');
}
return (string) $value;
case 'year':
case 'integer':
if ((string) $value == '') {
return $this->null2text($default);
}
if (!is_numeric($value)) {
$value = forceInteger($value);
}
return (string) $value;
case 'boolean':
if ($value == '') {
return 'FALSE';
}
return strtoupper(substr($value, 0, 1)) == 'T' || $value == 1 || $value === true ? 'TRUE' : 'FALSE';
case 'lookup':
case 'domain':
if ((string) $value == '') {
return $this->null2text($default);
}
return $value;
case 'date':
if ($value == '') {
return $this->null2text($default);
}
return $db->quote(forceISODate($value, null, 'ISO'));
case 'string':
case 'color':
case 'text':
case 'memo':
if ($value == '') {
return $this->null2text($default, true, true);
}
if ($size > 0) {
return $db->quote(mb_substr($value, 0, $size));
}
return $db->quote($value);
case 'geometry':
if ($value == '') {
return $this->null2text($default);
}
return $db->quote($value);
default:
return $value;
}
}
public static function addProduct($data)
{
$db = ezcDbInstance::get();
// Aggiungo fattore di conversione
list($es_id, $udm_id) = array_values(self::getEnergySourceAndUdm($data['do_id'], $data['et_code']));
$fields = array('es_id' => (int) $es_id, 'udm_id' => (int) $udm_id, 'esu_kwh_factor' => 1, 'esu_co2_factor' => (double) forceFloat($data['esu_co2_factor'], null, '.'), 'do_id' => (int) $data['do_id'], 'esu_is_private' => 'TRUE', 'esu_is_consumption' => 'TRUE', 'esu_is_production' => 'FALSE');
if ($data['ges_id'] > 0) {
$fields['ges_id'] = (int) $data['ges_id'];
}
$sql = 'INSERT INTO energy_source_udm (' . implode(', ', array_keys($fields)) . ' ' . ') VALUES (' . implode(', ', array_values($fields)) . ')';
$db->exec($sql);
$esu_id = $db->lastInsertId('energy_source_udm_esu_id_seq');
// Aggiungo prodotto
$fields = array('us_id' => (int) $data['us_id'], 'up_name_1' => $db->quote($data['up_name_1']), 'up_name_2' => $data['up_name_2'] == '' ? 'NULL' : $db->quote($data['up_name_2']), 'up_order' => (int) $data['up_order'], 'esu_id' => $esu_id);
$sql = 'INSERT INTO utility_product (' . implode(', ', array_keys($fields)) . ' ' . ') VALUES (' . implode(', ', array_values($fields)) . ')';
$db->exec($sql);
return $db->lastInsertId('utility_product_up_id_seq');
}
/**
* Ajax request to submit data
* @param array $request the request
* @return array ajax format status
*/
public function submitFormData($request)
{
$errors = array();
$db = ezcDbInstance::get();
$request = array_merge(array('ac_object_id' => null, 'gc_id' => null), $request);
$request['wo_id'] = null;
$request['ac_id'] = $request['id'];
if (isset($request['mu_name'])) {
$request['mu_id'] = R3EcoGisHelper::getMunicipalityIdByName($this->do_id, $request['mu_name'], $this->auth->getParam('mu_id'));
}
if ($this->act != 'del') {
$request['ac_green_electricity_purchase'] = forceFloat($request['ac_green_electricity_purchase_mwh'], null, '.') * 1000;
// Convert MWh to kWh
$request['ac_co2_reduction'] = forceFloat($request['ac_co2_reduction_tco2'], null, '.') * 1000;
// Convert tCO2 to kCO2
$tmpGesIdConsumption = array();
$tmpEsIdConsumption = array();
$tmpUdmIdConsumption = array();
$tmpAcExpectedEnergySaving = array();
$tmpAcExpectedEnergySavingMwh = array();
for ($i = 0; $i < count($request['es_id_consumption']); $i++) {
if ($request['udm_id_consumption'][$i] != '' || $request['ac_expected_energy_saving'][$i] != '') {
$tmpGesIdConsumption[] = $request['ges_id_consumption'][$i];
$tmpEsIdConsumption[] = $request['es_id_consumption'][$i];
$tmpUdmIdConsumption[] = $request['udm_id_consumption'][$i];
$tmpAcExpectedEnergySaving[] = $request['ac_expected_energy_saving'][$i];
$tmpAcExpectedEnergySavingMwh[] = $request['ac_expected_energy_saving_mwh'][$i];
}
}
$request['ges_id_consumption'] = $tmpGesIdConsumption;
$request['es_id_consumption'] = $tmpEsIdConsumption;
$request['udm_id_consumption'] = $tmpUdmIdConsumption;
$request['ac_expected_energy_saving'] = $tmpAcExpectedEnergySaving;
$request['ac_expected_energy_saving_mwh'] = $tmpAcExpectedEnergySavingMwh;
$request['esu_id_consumption'] = R3EcoGisHelper::getMultipleEnergySourceUdmID($_SESSION['do_id'], $request['es_id_consumption'], $request['udm_id_consumption'], $request['mu_id']);
$request['esu_id_production'] = R3EcoGisHelper::getEnergySourceUdmID($_SESSION['do_id'], $request['es_id_production'], $request['udm_id_production'], $request['mu_id'], true);
$request['emo_id'] = self::getEnergyMeterObjectByID($request['mu_id'], $request['ac_object_id'], $request['gc_id']);
if (isset($request['mu_name']) && $request['mu_name'] != '' && $request['mu_id'] == '') {
$errors['mu_name'] = array('CUSTOM_ERROR' => _('Il comune immesso non è stato trovato'));
}
if (!isset($request['gc_id_parent']) || $request['gc_id_parent'] == '') {
$errors['gc_id_parent'] = array('CUSTOM_ERROR' => _('Il campo "Macro-settore" è obbligatorio'));
}
$errors = $this->checkFormData($request, $errors);
$selectedRelatedActions = array();
if (isset($request['related_required_action_id'])) {
for ($i = 0; $i < count($request['related_required_action_id']); $i++) {
if ($request['related_required_action_id'][$i] > 0 && in_array($request['related_required_action_id'][$i], $selectedRelatedActions)) {
$errors['related_required_action_' . $i] = array('CUSTOM_ERROR' => _("L'azione ") . $this->getActionName($request['related_required_action_id'][$i]) . _(" è già stata selezionata"));
}
array_push($selectedRelatedActions, $request['related_required_action_id'][$i]);
}
}
if (isset($request['related_action_id'])) {
for ($i = 0; $i < count($request['related_action_id']); $i++) {
if ($request['related_action_id'][$i] > 0 && in_array($request['related_action_id'][$i], $selectedRelatedActions)) {
$errors['related_action_' . $i] = array('CUSTOM_ERROR' => _("L'azione ") . $this->getActionName($request['related_action_id'][$i]) . _(" è già stata selezionata"));
}
array_push($selectedRelatedActions, $request['related_action_id'][$i]);
}
}
if (isset($request['related_excluded_action_id'])) {
for ($i = 0; $i < count($request['related_excluded_action_id']); $i++) {
if ($request['related_excluded_action_id'][$i] > 0 && in_array($request['related_excluded_action_id'][$i], $selectedRelatedActions)) {
$errors['related_required_action_' . $i] = array('CUSTOM_ERROR' => _("L'azione ") . $this->getActionName($request['related_excluded_action_id'][$i]) . _(" è già stata selezionata"));
}
array_push($selectedRelatedActions, $request['related_excluded_action_id'][$i]);
}
}
if (isset($request['enable_benefit_year']) && $request['enable_benefit_year'] == 'T' && isset($request['benefit_year'])) {
$startBenefitYear = (int) substr($request['ac_benefit_start_date'], 0, 4);
$endBenefitYear = (int) substr($request['ac_benefit_end_date'], 0, 4);
$lastBenefitPerc = 0;
for ($i = 0; $i < count($request['benefit_year']); $i++) {
if ($request['benefit_year'][$i] != '' && $request['benefit_year'][$i] < $startBenefitYear) {
$errors['benefit_year_' . $i] = array('CUSTOM_ERROR' => sprintf(_("L'anno del beneficio \"%s\" è antecedente al %s (anno inizio beneficio)"), $request['benefit_year'][$i], $startBenefitYear));
} else {
if ($request['benefit_year'][$i] != '' && $request['benefit_year'][$i] > $endBenefitYear) {
$errors['benefit_year_' . $i] = array('CUSTOM_ERROR' => sprintf(_("L'anno del beneficio \"%s\" è oltre al %s (anno fine beneficio)"), $request['benefit_year'][$i], $endBenefitYear));
}
}
if ($request['benefit_benefit'][$i] != '' && $request['benefit_benefit'][$i] < 0 || $request['benefit_benefit'][$i] != '' && $request['benefit_benefit'][$i] > 100) {
$errors['benefit_benefit_' . $i] = array('CUSTOM_ERROR' => _("Il valore del beneficio deve essere compreso tra 0 e 100"));
}
}
}
}
if (count($errors) > 0) {
return $this->getAjaxErrorResult($errors);
} else {
setLocale(LC_ALL, 'C');
$db->beginTransaction();
$id = $this->applyData($request);
$sql = "DELETE FROM ecogis.action_catalog_energy WHERE ac_id=" . $db->quote($id, PDO::PARAM_INT);
$db->exec($sql);
if ($this->act != 'del') {
for ($i = 0; $i < count($request['esu_id_consumption']); $i++) {
$sql = "INSERT INTO ecogis.action_catalog_energy (ac_id, esu_id, ace_expected_energy_saving) " . " VALUES (" . $db->quote($id, PDO::PARAM_INT) . ", " . $db->quote($request['esu_id_consumption'][$i], PDO::PARAM_INT) . ", " . $db->quote($request['ac_expected_energy_saving'][$i], PDO::PARAM_INT) . ")";
$db->exec($sql);
}
}
$sql = "DELETE FROM ecogis.action_catalog_dependencies WHERE ac_id=" . $db->quote($id, PDO::PARAM_INT);
$db->exec($sql);
if ($this->act != 'del') {
$relatedActions = array();
if (isset($request['related_action_id'])) {
for ($i = 0; $i < count($request['related_action_id']); $i++) {
array_push($relatedActions, array('related_action_id' => $request['related_action_id'][$i], 'acd_required' => 'D'));
}
}
if (isset($request['related_required_action_id'])) {
for ($i = 0; $i < count($request['related_required_action_id']); $i++) {
array_push($relatedActions, array('related_action_id' => $request['related_required_action_id'][$i], 'acd_required' => 'R'));
}
}
if (isset($request['related_excluded_action_id'])) {
for ($i = 0; $i < count($request['related_excluded_action_id']); $i++) {
array_push($relatedActions, array('related_action_id' => $request['related_excluded_action_id'][$i], 'acd_required' => 'E'));
}
}
foreach ($relatedActions as $relatedAction) {
$sql = "INSERT INTO ecogis.action_catalog_dependencies (ac_id, ac_related_id, acd_type) " . " VALUES (" . $db->quote($id, PDO::PARAM_INT) . ", " . $db->quote($relatedAction['related_action_id'], PDO::PARAM_INT) . ", " . $db->quote($relatedAction['acd_required'], PDO::PARAM_BOOL) . ")";
if ($relatedAction['related_action_id'] > 0) {
$db->exec($sql);
}
}
}
$sql = "DELETE FROM ecogis.action_catalog_benefit_year WHERE ac_id=" . $db->quote($id);
$db->exec($sql);
if ($this->act != 'del') {
if (isset($request['enable_benefit_year']) && $request['enable_benefit_year'] == 'T') {
for ($i = 0; $i < count($request['benefit_year']); $i++) {
$year = forceInteger($request['benefit_year'][$i]);
$benefit = forceFloat($request['benefit_benefit'][$i], null, '.');
if ($year > 1970 && $request['benefit_benefit'][$i] != '') {
$sql = "INSERT INTO ecogis.action_catalog_benefit_year (ac_id, acby_year, acby_benefit) " . "VALUES ({$id}, {$year}, {$benefit})";
$db->exec($sql);
}
}
}
}
$db->commit();
R3EcoGisCacheHelper::resetMapPreviewCache(null);
R3EcoGisEventNotifier::notifyDataChanged($this, array('data_changed' => true));
if ($this->bu_id == '') {
return array('status' => R3_AJAX_NO_ERROR, 'js' => "submitFormDataDoneActionCatalog({$id})");
} else {
return array('status' => R3_AJAX_NO_ERROR, 'js' => "submitFormDataDoneActionCatalogFromBuilding({$id})");
}
}
}
public function submitFormDataDetail($request)
{
$db = ezcDbInstance::get();
if (isset($request['gpm_date']) && isset($request['gpm_value_1'])) {
// Insert details
$sql = "DELETE FROM ecogis.global_plain_monitor WHERE gpr_id={$request['gpr_id']}";
$db->exec($sql);
$sql = "INSERT INTO ecogis.global_plain_monitor (gpr_id, gpg_id, gpm_date, gpm_value_1, gpm_value_2) VALUES (?, ?, ?, ?, ?)";
$stmt = $db->prepare($sql);
for ($i = 0; $i < count($request['gpg_id']); $i++) {
$gpg_id = empty($request['gpg_id'][$i]) ? null : $request['gpg_id'][$i];
$gpm_date = forceISODate($request['gpm_date'][$i]);
$gpm_value_1 = forceFloat($request['gpm_value_1'][$i], null, '.');
$gpm_value_2 = forceFloat($request['gpm_value_2'][$i], null, '.');
if (!empty($gpm_date) && !empty($gpm_value_1)) {
$stmt->execute(array($request['gpr_id'], $gpg_id, $gpm_date, $gpm_value_1, $gpm_value_2));
}
}
}
}
public function performActionCatalogCalc($request)
{
$energySavingData = R3EcoGisHelper::getEnergySourceUdmData($_SESSION['do_id'], $request['es_id_consumption'], $request['udm_id_consumption'], $request['mu_id']);
$energyProductionData = null;
$ac_expected_co2_reduction_total = 0;
$ac_expected_co2_reduction = array();
$ac_expected_energy_saving_mwh = array();
foreach ($energySavingData as $i => $val) {
$request['ac_expected_energy_saving'][$i] = forceFloat($request['ac_expected_energy_saving'][$i], null, '.');
if (isset($request['co_production_co2_factor'])) {
$ac_expected_co2_reduction_total += $request['ac_expected_energy_saving'][$i] == '' ? null : $request['ac_expected_energy_saving'][$i] * forceFloat($request['co_production_co2_factor'][$i], null, '.');
$ac_expected_co2_reduction[$i] = R3NumberFormat($request['ac_expected_energy_saving'][$i] == '' ? null : $request['ac_expected_energy_saving'][$i] * forceFloat($request['co_production_co2_factor'][$i], null, '.'), null, true);
} else {
$ac_expected_co2_reduction_total += $request['ac_expected_energy_saving'][$i] == '' ? null : $request['ac_expected_energy_saving'][$i] * $energySavingData[$i]['esu_co2_factor'] / 1000;
$ac_expected_co2_reduction[$i] = R3NumberFormat($request['ac_expected_energy_saving'][$i] == '' ? null : $request['ac_expected_energy_saving'][$i] * $energySavingData[$i]['esu_co2_factor'] / 1000, null, true);
}
$ac_expected_energy_saving_mwh[$i] = R3NumberFormat($request['ac_expected_energy_saving'][$i] == '' ? null : $request['ac_expected_energy_saving'][$i] * $energySavingData[$i]['esu_kwh_factor'] / 1000, null, true);
}
$energyProductionMWh = null;
return array('status' => R3_AJAX_NO_ERROR, 'data' => array('ac_expected_co2_reduction_total' => R3NumberFormat($ac_expected_co2_reduction_total, null, true), 'ac_expected_co2_reduction' => $ac_expected_co2_reduction, 'ac_expected_energy_saving_mwh' => $ac_expected_energy_saving_mwh, 'ac_expected_renewable_energy_production_mwh' => R3NumberFormat($energyProductionMWh, null, true)));
}
/**
* Ajax request to submit data
* @param array $request the request
* @return array ajax format status
*/
public function submitFormData($request)
{
$request = array_merge(array('insert_type' => 'free', 'reinsert' => ''), $request);
$db = ezcDbInstance::get();
$data = array();
$errors = array();
if ($request['kind'] == 'street_lighting' && $this->act == 'add') {
// Valore finto per bypassare controlli automatici
$request['em_id'] = '-1';
}
$checkDate = false;
if ($this->act != 'del') {
switch ($request['insert_type']) {
case 'free':
$checkDate = true;
$data[] = array('act' => $request['act'], 'co_start_date' => forceISODate($request['co_start_date_free']), 'co_end_date' => forceISODate($request['co_end_date_free']), 'co_value' => forceFloat($request['co_value_free'], null, '.'), 'co_bill' => forceFloat($request['co_bill_free'], null, '.'), 'co_bill_is_calculated' => 'F', 'em_id' => $request['em_id'], 'co_id' => forceInteger($request['id'], 0, false, '.'));
break;
case 'month':
for ($i = 0; $i < 12; $i++) {
if ($request['co_start_date_month'][$i] != '' && $request['co_end_date_month'][$i] != '' && $request['co_value_month'][$i] != '' && $request['co_bill_month'][$i] != '') {
$data[] = array('act' => $request['act'], 'co_start_date' => forceISODate($request['co_start_date_month'][$i]), 'co_end_date' => forceISODate($request['co_end_date_month'][$i]), 'co_value' => forceFloat($request['co_value_month'][$i], null, '.'), 'co_bill' => forceFloat($request['co_bill_month'][$i], null, '.'), 'co_bill_is_calculated' => 'F', 'em_id' => $request['em_id']);
}
}
if (count($data) == 0) {
$errors['dummy'] = array('CUSTOM_ERROR' => _('Impossibile salvare. Nessun dato valido inserito'));
}
break;
case 'year':
$data[] = array('act' => $request['act'], 'co_start_date' => forceISODate($request['co_start_date_year']), 'co_end_date' => forceISODate($request['co_end_date_year']), 'co_value' => forceFloat($request['co_value_year'], null, '.'), 'co_bill' => forceFloat($request['co_bill_year'], null, '.'), 'co_bill_is_calculated' => 'F', 'em_id' => $request['em_id']);
break;
}
foreach ($data as $values) {
$errors = $this->checkFormData($values, $errors);
}
if ($checkDate) {
// Verifica data inserimento per bollette singole
if (forceISODate($request['co_start_date_free']) > forceISODate($request['co_end_date_free'])) {
$errors['co_end_date_bis'] = array('CUSTOM_ERROR' => _("La data di fine periodo precedenta a quella di inizio"));
}
}
}
if (count($errors) > 0) {
return $this->getAjaxErrorResult($errors);
} else {
if ($this->act == 'del') {
$request['co_id'] = forceInteger($request['id'], 0, false, '.');
if ($request['kind'] == 'street_lighting') {
// Ricavo contatore finto per cancellazione
$db->beginTransaction();
$em_id = $db->query("SELECT em_id FROM consumption WHERE co_id={$request['co_id']}")->fetchColumn();
$id = $this->applyData($request);
if ($db->query("SELECT COUNT(*) FROM consumption WHERE em_id={$em_id}")->fetchColumn() == 0) {
$db->exec("DELETE FROM energy_meter WHERE em_id={$em_id}");
}
$db->commit();
} else {
$id = $this->applyData($request);
}
} else {
$db->beginTransaction();
if ($request['kind'] == 'street_lighting' && $this->act == 'add') {
$em_id = R3EcoGisHelper::getDummyEnergyMeter('STREET_LIGHTING', $request['sl_id']);
if ($em_id == '') {
// Aggiungo contatore finto per tratto stradale
$electricityData = $this->getElectricityUDMData($_SESSION['do_id']);
$em_id = R3EcoGisHelper::addDummyEnergyMeter(array('esu_id' => $electricityData['esu_id'], 'em_object_id' => $request['sl_id']), 'STREET_LIGHTING');
}
$data[0]['em_id'] = $em_id;
}
$id = array();
foreach ($data as $values) {
$id[] = $this->applyData($values);
}
$db->commit();
}
R3EcoGisEventNotifier::notifyDataChanged($this, array('data_changed' => true));
return array('status' => R3_AJAX_NO_ERROR, 'js' => "submitFormDataDoneConsumption(" . json_encode($id) . ", '{$request['reinsert']}')");
}
}
