<?php
  /**
  * Sistema Integral de Planificación y Presupuesto (SIPP)
  * @file sipesonapre.module
  * Drupal part Module to Sistema Integral de Planificación y Presupuesto (SIPP)
  * Copyright 2013 Sistema Automatizado para la Planificación Estratégico-Situacional en la Administración Pública Venezolana (CENDITEL)
  *
  * This program is free software; you can redistribute it and/or modify
  * it under the terms of the GNU General Public License as published by
  * the Free Software Foundation; either version 2 of the License, or
  * (at your option) any later version.
  * 
  * This program is distributed in the hope that it will be useful,
  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  * GNU General Public License for more details.
  * 
  * You should have received a copy of the GNU General Public License
  * along with this program; if not, write to the Free Software
  * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
  *
  * @author Cenditel Merida - PHD. Juan Vizcarrondo
  * @date 2016-08-15 // (a&#241;o-mes-dia)
  * @version 0.2 // (0.1)
  *
  */
function sipesonapre_form_alter(&$form, $form_state, $form_id) {
  if ($form_id == 'proyectos_operativos_admin_ae_settings') {
    $node_type = content_types('accion_especifica');
    $fields = $node_type['fields'];
    $accion_especifica_number_fields = variable_get('accion_especifica_number_fields', 4);
    $fuentes_id = array();
    $k = 1;
    $form['sipesonapre'] = array(
      '#type' => 'fieldset',
      '#title' => t('ONAPRE (Códigos)'),
      '#collapsible' => TRUE,
      '#collapsed' => FALSE,
      '#weight' => -1,
    );
    $form['sipesonapre']['onapre_encode_ISO_8859_1'] = array(
      '#type' => 'checkbox',
      '#title' => t('Convertir ISO-8859-1'),
      '#default_value' => variable_get('onapre_encode_ISO_8859_1', FALSE),
    );
    if (module_exists('workflow')) {
      $wid = workflow_get_workflow_for_type('proyectos_operativos');
      if ($wid){
        $states = workflow_get_states($wid);
        if (count ($states)) {
          $form['sipesonapre']['onapre_workflow_export'] = array(
            '#type' => 'select',
            '#title' => t('Estado aprobado'),
            '#default_value' => variable_get('onapre_workflow_export', array()),
            '#description' => t('Mostrar solo los proyectos con estos estados. Si no selecciona ninguna opción se mostraran los proyectos sin importar su estado'),
            '#options' => $states,
            '#multiple' => TRUE,
          );
        }
      }
    }

    if ($accion_especifica_number_fields > 0) {
      for($i = 0; $i < $accion_especifica_number_fields; $i++) {
        $arreglo_financiamiento['financiamiento_' . $i] = variable_get('accion_especifica_financiamiento_label_' . $i, '') ? variable_get('accion_especifica_financiamiento_label_' . $i, '') : '';
        $fuentes = variable_get('accion_especifica_financiamiento_options_' . $i, array());
        foreach($fuentes as $id) {
          if ($id) {
            $form['sipesonapre']['onapre_' . $id] = array(
              '#type' => 'textfield',
              '#title' => $fields[$id]['widget']['label'],
              '#required' => TRUE,
              '#default_value' => variable_get('onapre_' . $id, ''),
            );
          }
        }
      }
    }
  }
}
 
function _sipesonapre_get_fuentes($nid = 0, $type = 'table') {
  $cell_separator = ",";
  $row_separator = "\n";

  $node_type = content_types('accion_especifica');
  $fields = $node_type['fields'];
  $accion_especifica_number_fields = variable_get('accion_especifica_number_fields', 4);
  $fuentes_id = array();
  $k = 1;
  $codigos = array();
  if ($accion_especifica_number_fields > 0) {
    for($i = 0; $i < $accion_especifica_number_fields; $i++) {
      $arreglo_financiamiento['financiamiento_' . $i] = variable_get('accion_especifica_financiamiento_label_' . $i, '') ? variable_get('accion_especifica_financiamiento_label_' . $i, '') : '';
      $fuentes = variable_get('accion_especifica_financiamiento_options_' . $i, array());
      foreach($fuentes as $id) {
        if ($id) {
          $fuentes_id['campo_' . $k . '_total'] = $id;
          $codigos[$id] = variable_get('onapre_' . $id, '');
          $k++;
        }
      }
    }
  }
  $estados = variable_get('onapre_workflow_export', array());
  $nestados = count($estados);
  $select =array();
  $select['node_nid'] = 'node.nid AS node_nid';
  //Se agrega la cantidad de AE
  $select['node_node_data_field_proyecto_accion_esp_nid'] = 'COUNT(node_node_data_field_proyecto_accion_esp.nid) AS node_node_data_field_proyecto_accion_esp_nid';
  $from = array();
  $from[] = '{node} AS node';
  $join = array();  
  $join[] = 'LEFT JOIN {content_field_proyecto_accion_esp} AS node_data_field_proyecto_accion_esp ON node.vid = node_data_field_proyecto_accion_esp.vid';  
  $join[] = 'LEFT JOIN {node} AS node_node_data_field_proyecto_accion_esp ON node_data_field_proyecto_accion_esp.field_proyecto_accion_esp_nid = node_node_data_field_proyecto_accion_esp.nid';
  $join[] = 'LEFT JOIN {content_type_accion_especifica} AS node_node_data_field_proyecto_accion_esp_node_data_field_accion_esp_transferencias ON node_node_data_field_proyecto_accion_esp.vid = node_node_data_field_proyecto_accion_esp_node_data_field_accion_esp_transferencias.vid';
  if ($nestados) {
    $join[] = 'LEFT JOIN {workflow_node} AS westados ON node.vid = westados.nid';
  }
  $where = array();
  $where[] = "node.type in ('proyectos_operativos')";
  if ($nestados) {
    $where[] = "westados.sid in (" . implode(', ', $estados) . ")";
  }
  $groupby = array();
  $groupby[] = 'node_nid';
  $i = 0;
  foreach ($fuentes_id as $id) {
    $select['campo_' . $i . 'total'] = 'SUM(node_node_data_field_proyecto_accion_esp_node_data_field_accion_esp_transferencias.' . $id . '_value) AS campo_' . $i . '_total';
    $i++;
  }
  $sql = 'SELECT ' . implode(', ', $select) . ' FROM ' . implode(', ', $from) . ' ' . implode (' ', $join) . ' WHERE ' . implode(' AND ', $where) . ' GROUP BY ' . implode(', ', $groupby);
  $result = db_query($sql);
  if ($type == 'table') {
    $header = array(
      array('data' => t('PROY_PROY_ID')),
      array('data' => t('FUENTE_ID')),
      array('data' => t('MONTO')),
    );
    $rows = array();
  }
  elseif ($type == 'cvs') {
    $cadena = '"PROY_PROY_ID"' . $cell_separator . '"FUENTE_ID"' . $cell_separator . '"MONTO"' . $row_separator;
  }
  elseif ($type == 'data') {
    $arreglo = array();
  }
  while ($data = db_fetch_array($result)) {
    $fuentes_fin = array();
    $nid = $data['node_nid'];
    foreach ($data as $id => $valor) {
      if ($valor && isset($fuentes_id[$id])) {
        if (!isset($fuentes_fin[$codigos[$fuentes_id[$id]]])) {
          $fuentes_fin[$codigos[$fuentes_id[$id]]] = 0;
        }
        $fuentes_fin[$codigos[$fuentes_id[$id]]] += $valor;
      }
    }
    if ($type == 'table') {
      $row_nid = array('data' => $nid,);
    }
    elseif ($type == 'cvs') {
      $texto_base = '"' . $nid . '"' . $cell_separator;
    }
    elseif ($type == 'data') {
      $datos_other = array();
    }
    foreach($fuentes_fin as $id => $valor) {
      if ($type == 'table') {
        $row = array();
        $row[] = $row_nid;
        $row[] = array('data' => $id,);
        $row[] = array('data' => $valor,);
        $rows[] = $row;
      }
      elseif ($type == 'cvs') {
        $cadena .= $texto_base . '"' . $id . '"' . $cell_separator . '"' . $valor . '"' . $row_separator;
      }
      elseif ($type == 'data') {
        $item = new stdClass();
        $item->PROY_PROY_ID = $nid;
        $item->FUENTE_ID = $id;
        $item->MONTO = $valor;
        $arreglo[] = $item;
      } 
    }
  }
  if ($type == 'table') {

    if (empty($rows)) {
      $rows[] = array(array('data' => t('No existen datos almacenados en el sistema.'), 'colspan' => 3));
    }
    return theme('table', $header, $rows, array('id' => 'fuentes_proy'));
  }
  elseif ($type == 'cvs') {
    $filename = 'fuentes_proyectos_' . time() . '.csv';
    if (variable_get('onapre_encode_ISO_8859_1', FALSE)) {
      header('Content-type: text/plain; charset=utf-8');
      $cadena = utf8_decode ($cadena);
    }
    else {
      header('Content-type: text/plain');
    }
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    print $cadena;
    exit();
  }
  elseif ($type == 'data') {
    return $arreglo;
  }
}
/*
 * Implementation of hook_menu()
 */
function sipesonapre_menu() {
  $items = array();
  $items['onapre/proyectos/fuentes'] = array(
    'title' => 'Fuentes Proyectos ONAPRE',
    'page callback' => '_sipesonapre_get_fuentes',
    'page arguments' => array('0', 'table'),
    'access arguments' => array('consultar onapre'),
    'type' => MENU_CALLBACK,
  );
  $items['onapre/proyectos/fuentes/cvs'] = array(
    'title' => 'Fuentes Proyectos ONAPRE CVS',
    'page callback' => '_sipesonapre_get_fuentes',
    'page arguments' => array('0', 'cvs'),
    'access arguments' => array('consultar onapre'),
    'type' => MENU_CALLBACK,
  );
  return $items;
}
/*
 * Implementation of hook_perm
 */
function sipesonapre_perm() {
  return array(
    'consultar onapre',
  );
}

/**
 * Implementation of hook_services_resources().
 */
function sipesonapre_services_resources() {
  $resources['sipesonapre'] = array();
  $resources['sipesonapre']['retrieve'] = array(
    'help' => 'Retrieves Fuentes Proyectos ONAPRE.',
    'callback' => 'sipesonapre_retrieve',
    'access callback' => 'sipesonapre_access',
    'access arguments' => array('view'),
    'access arguments append' => TRUE,
  );

  return $resources;
}

/**
 * Check the access permission to a given views.
 *
 * @param $op
 *  String. The operation that's going to be performed.
 * @param $args
 *  Array. The arguments that will be passed to the callback.
 * @return
 *  Boolean. TRUE if the user is allowed to load the given view.
 */
function sipesonapre_access($op = 'view') {
  return user_access('consultar onapre');
}

/**
 * Callback for retrieving fuentes onapre.
 *
 *
 * @return
 *  Array. The views return.
 */
function sipesonapre_retrieve() {
  $result = _sipesonapre_get_fuentes($nid = 0, 'data');
  return $result;
}