<?
  class group {

    var $exercise, $group, $company, $fields, $query_order, $records_view, $num_records, $config, $ref_tables, $ref, $errors;

    function group() {

      global $CONF, $LNG, $EXERCISE;

      $this->config        = &$CONF;
      $this->lng           = &$LNG;
      $this->exercise      = &$EXERCISE;
      $this->fields        = array( '1'  => 'g.id',
                                    '2'  => 'g.nombre',
                                    '3'  => 'g.fecha_inicio',
                                    '4'  => 'g.fecha_fin',
                                    '5'  => 'g.nivel',
                                    '6'  => 'g.tipo',
                                    '7'  => 'g.unidades_mes',
                                    '8'  => 'g.importe_unidad',
                                    '9'  => 'e.nombre',
                                    '10' => 'e.id' );

      $this->query_order   = array( 'a' => 'ASC',
                                    'd' => 'DESC' );

      $this->records_view  = array( 'order'   => array('1','2','9','5','6','11', '12', '7','8'),
                                    'width'   => array('35', '120', '150', '25', '80', '150', '150', '40', '40'),
                                    'orderby' => array(true, true, true, true, true, false, false, true, true),
                                    'search'  => array(true, true, true, false, true, false, false, true, false) );

      $this->ref_tables    = array( 'group_type'     => 'ref_tipo_curso',
                                    'group_state'    => 'ref_provincias',
                                    'group_language' => 'ref_idiomas'
                                  );

      $this->getReferenceValues();
    }

    //-----------------------------------------------------------------------------------
    // getReferenceValues
    // Obtiene el listado de los datos de las tablas de referencia.

    function getReferenceValues() {
      if ( count($this->ref_tables) > 0 ) {
        while ( list($field) = each($this->ref_tables) ) {
          $SQL_query = db_query('SELECT id, nombre FROM ' . $this->ref_tables[$field] . ' ORDER BY nombre');
          if ( db_num_rows($SQL_query) > 0 ) {
            while ( $SQL_values = db_fetch_array($SQL_query) ) $this->ref[$field][$SQL_values['id']] = $SQL_values['nombre'];
          }
        }
        reset($this->ref_tables);
      }
    }

    //-----------------------------------------------------------------------------------
    // getRecordList
    // Obtiene un listado de registros de la BBDD.

    function getRecordList(&$page, $order_field = 1, $order_type = 'd', $SQL_records_filter = '') {

      $records_list = array();

      if ( !isset( $this->fields[$order_field] ) )     $order_field = 1;
      if ( !isset( $this->query_order[$order_type] ) ) $order_type  = 'd';
      if ( empty($SQL_records_filter) ) $this->generateFilterQuery();

      $SQL_records_query  = db_query('SELECT COUNT(*) AS total FROM grupos g LEFT JOIN empresas e ON g.id_empresa = e.id' . $SQL_records_filter);
      $SQL_records        = db_fetch_array($SQL_records_query);

      if ( $SQL_records['total'] > 0 ) {

        $page              = check_records_page($page, $SQL_records['total']);
        $this->num_records = $SQL_records['total'];
        $SQL_records_order = ' ORDER BY ' . $this->fields[$order_field] . ' ' . $this->query_order[$order_type];
        $SQL_records_limit = ' LIMIT ' . ( ( $page - 1 ) * $this->config['max_num_regs_list'] ) . ', ' . $this->config['max_num_regs_list'];

        $SQL_query         = db_query('SELECT g.id, g.nombre, e.nombre AS nombre_empresa, g.fecha_inicio, g.fecha_fin, IF( g.nivel, g.nivel, \'\') AS nivel, g.tipo, IF(g.unidades_mes, g.unidades_mes, \'\') AS unidades_mes, g.importe_unidad FROM grupos g LEFT JOIN empresas e ON g.id_empresa = e.id' . $SQL_records_filter . $SQL_records_order . $SQL_records_limit);

        while ( $SQL_record = db_fetch_array($SQL_query) ) {
          $records_list[$SQL_record['id']]['1'] = $SQL_record['id'];
          $records_list[$SQL_record['id']]['2'] = stripslashes($SQL_record['nombre']);
          $records_list[$SQL_record['id']]['3'] = $SQL_record['fecha_inicio'];
          $records_list[$SQL_record['id']]['4'] = $SQL_record['fecha_fin'];
          $records_list[$SQL_record['id']]['5'] = $SQL_record['nivel'];
          $records_list[$SQL_record['id']]['6'] = $this->ref['group_type'][$SQL_record['tipo']];
          $records_list[$SQL_record['id']]['7'] = $SQL_record['unidades_mes'];
          $records_list[$SQL_record['id']]['8'] = $SQL_record['importe_unidad'];
          $records_list[$SQL_record['id']]['9'] = stripslashes($SQL_record['nombre_empresa']);
          $records_list[$SQL_record['id']]['11'] = implode(', ', array_values($this->getTeachersToGroup($SQL_record['id'])));
          $records_list[$SQL_record['id']]['12'] = implode(', ', $this->getGroupTimeTable( $SQL_record['id'] ) );
        }
      }

      return $records_list;
    }

    //-----------------------------------------------------------------------------------
    // generateFilterQuery

    function generateFilterQuery( $filter_field = '', $filter_values = '', $exactSearch = false ) {

      $filterQuery   = ' WHERE 1 AND g.id_periodo = \'' . $this->exercise->exercise['id'] . '\'';
      $filter_values = ( $exactSearch ? '\'' . addslashes($filter_values) . '\'' : '\'%' . addslashes($filter_values) . '%\'' );

      if ( !empty($filter_values) && isset( $this->fields[$filter_field] ) ) $filterQuery .= ' AND ' . $this->fields[$filter_field] . ' LIKE ' . $filter_values;

      return $filterQuery;
    }

    //-----------------------------------------------------------------------------------
    // deleteRecord
    // Elimina un registro de la BBDD.

    function deleteRecord($record) {

      $error = '';
	    
      if ( $this->checkRecord($record) ) {

        $error .= 0;

        $SQL_query  = db_query('SELECT count(*) AS total FROM alumnos_grupos WHERE id_grupo = \'' . $record . '\'');
	$SQL_values = db_fetch_array($SQL_query);

        $error .= ( $SQL_values['total'] > 0 ? 1 : 0 );

        $SQL_query  = db_query('SELECT count(*) AS total FROM profesores_grupo WHERE id_grupo = \'' . $record . '\'');
	$SQL_values = db_fetch_array($SQL_query);

        $error .= ( $SQL_values['total'] > 0 ? 1 : 0 );

        if ( ereg("^0+$", $error) ) {
          if ( db_query('DELETE FROM grupos WHERE id = \'' . $record . '\'') ) {

            @db_query('DELETE FROM grupo_horario WHERE id = \'' . $record . '\'');
            @db_query('DELETE FROM informe_evaluacion WHERE id_grupo = \'' . $record . '\'');
            @db_query('DELETE FROM masterlist WHERE id_grupo = \'' . $record . '\'');

            $error .= '0';
          }
          else $error .= '1';
        }
      }
      else $error .= 1;

      return $error;
    }

    //-----------------------------------------------------------------------------------
    // checkRecord
    // Comprueba si existe o no un registro

    function checkRecord(&$record) {

      settype($record, "integer");

      $SQL_check_record = db_query('SELECT count(*) AS total FROM grupos WHERE id = \'' . $record . '\'');
      $SQL_num_records  = db_fetch_array($SQL_check_record);

      if ( $SQL_num_records['total'] == 1 ) return true;
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // getRecordValues
    // Obtiene los valores para el registro indicado.

    function getRecordValues($record) {

      if ( $this->checkRecord($record) ) {

        $SQL_check_record = db_query('SELECT g.id, g.id_empresa AS company_id, e.nombre AS company_name, g.nombre AS group_name, IF( g.nivel > 0, g.nivel, \'\' ) AS group_level, IF( g.nivel_objetivo, g.nivel_objetivo, \'\' ) AS group_target_level, g.alerta AS group_alert, UNIX_TIMESTAMP(g.fecha_inicio) AS date_start, IF(g.fecha_fin, UNIX_TIMESTAMP(g.fecha_fin), UNIX_TIMESTAMP(el.fecha_fin) ) AS date_end, DATE_FORMAT(g.fecha_inicio, "%d/%m/%Y") AS group_date_start, IF(g.fecha_fin, DATE_FORMAT(g.fecha_fin, "%d/%m/%Y"), DATE_FORMAT(el.fecha_fin, "%d/%m/%Y")) AS group_date_end, g.tipo AS group_type, IF(g.unidades_mes, g.unidades_mes, \'\') AS group_units, IF(g.importe_unidad > 0, g.importe_unidad, \'\') AS group_cost, g.observaciones AS group_comments, g.direccion AS group_address, g.numero AS group_number, g.piso_bloque AS group_floor, g.localidad AS group_city, g.codigo_postal AS group_postalcod, g.poblacion AS group_state, g.situacion AS group_how_arrive, g.materiales AS group_equipment, id_idioma AS group_language, aula AS group_class FROM grupos g LEFT JOIN empresas e ON g.id_empresa = e.id LEFT JOIN ejercicios_lectivos el ON g.id_periodo = el.id WHERE g.id = \'' . $record . '\'');

        if ( db_num_rows($SQL_check_record) == 1 ) {

          $this->group = db_fetch_array($SQL_check_record);
          $this->group['group_date_end'] = date('d/m/Y', $this->group['date_end']);
          strip_slashes($this->group);

          return $this->group;
        }
        else return false;
      }
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // manageRecord
    // - Recibe los parametros enviados desde el formulario mediante metodo POST.
    // - Determina el tipo de accion a realizar (INSERT o UPDATE).
    // - Trata los valores recibidos.
    // - Genera un codigo de error correspondiente a las acciones realizadas.
    //
    //     [1] > Tipo de accion
    //     [2] > Codigo empresa
    //     [3] > Campo Nombre
    //     [4] > Campo Fecha Inicio
    //     [5] > Campo Fecha Fin
    //     [6] > Resultado de la accion sobre la BBDD.

    function manageRecord(&$params) {

      $action = '';
      $error  = '';

      // Chequeo y tratamiento de los valores recibidos

      if ( isset($params['record_id']) ) {
        if ( ereg("^[0-9]+$", $params['record_id'] ) && $params['record_id'] > 0 ) { if ( $this->checkRecord($params['record_id']) ) $action = 'update'; }
      }
      else $action = 'insert';
      
      if ( !empty($params['group_date_start']) && ereg("^([0-9]{2})\/([0-9]{2})\/([0-9]{4})$", $params['group_date_start'], $regs ) ) { if ( checkdate($regs[2], $regs[1], $regs[3]) ) $group_date_start = mktime(0,0,0,$regs[2], $regs[1], $regs[3]); }
      if ( !empty($params['group_date_end']) && ereg("^([0-9]{2})\/([0-9]{2})\/([0-9]{4})$", $params['group_date_end'], $regs ) ) { if ( checkdate($regs[2], $regs[1], $regs[3]) ) $group_date_end = mktime(0,0,0,$regs[2], $regs[1], $regs[3]); }

      $params['group_alert'] = ( $params['group_alert'] == '1' ? '1' : '0' );

      // Niveles

      $this->manageLevel($params['group_level']);
      $this->manageLevel($params['group_target_level']);
      $this->manageHours($params['group_units']);
      $this->validateParams($params);

      // Generacion del codigo de error

      $error  = ( $action == '' ? 1 : 0 );
      $error .= ( !$this->checkCompany($params['company_id']) ? 1 : 0 );
      $error .= ( empty($params['group_name']) ? 1 : 0 );

      if ( empty($group_date_start) ) $error .= 1;
      elseif ( ( $group_date_start < $this->exercise->exercise['start_date'] ) || ( $group_date_start > $this->exercise->exercise['end_date'] ) ) $error .= 4;
      elseif ( !empty($params['group_date_end']) ) {
        if ( empty($group_date_end) ) $error .= 2;
        elseif ( ( $group_date_end < $this->exercise->exercise['start_date'] ) || ( $group_date_end > $this->exercise->exercise['end_date'] ) ) $error .= 5;
        elseif ( $group_date_start > $group_date_end ) $error .= 3;
        else $error .= 0;
      }
      else $error .= 0;

      if ( $params['group_level'] && ( $params['group_level'] < '0' || $params['group_level'] > '10' ) ) $error .= 1;
      elseif ( $params['group_target_level'] && ( $params['group_target_level'] < '0' || $params['group_target_level'] > '10' ) ) $error .= 2;
      else $error .= 0;

      // Acciones sobre la BBDD

      if ( ereg("^0+$", $error) ) {

        format_record($params);

        if ( $action == 'insert' ) {
          if ( db_query('INSERT INTO grupos ( id_periodo, id_empresa, nombre, fecha_inicio, fecha_fin, nivel, nivel_objetivo, tipo, unidades_mes, importe_unidad, direccion, numero, piso_bloque, localidad, codigo_postal, poblacion, situacion, observaciones, materiales, id_idioma, aula, alerta) VALUES (\'' . $this->exercise->exercise['id'] . '\', \'' . $params['company_id'] . '\', \'' . $params['group_name'] . '\', \'' . date('Y-m-d', $group_date_start) . '\', ' . ( !empty($group_date_end) && $group_date_end < $this->exercise->exercise['end_date'] ? '\'' . date('Y-m-d', $group_date_end) . '\'' : 'NULL' ) . ', \'' . $params['group_level'] . '\', \'' . $params['group_target_level'] . '\', \'' . $params['group_type'] . '\', \'' . $params['group_units'] . '\', \'' . $params['group_cost'] . '\', \'' . $params['group_address'] . '\', \'' . $params['group_number'] . '\', \'' . $params['group_floor'] . '\', \'' . $params['group_city'] . '\', \'' . $params['group_postalcod'] . '\', \'' . $params['group_state'] . '\', \'' . $params['group_how_arrive'] . '\', \'' . $params['group_comments'] . '\', \'' . $params['group_equipment'] . '\', \'' . $params['group_language'] . '\', \'' . $params['group_class'] . '\', \'' . $params['group_alert'] . '\')') ) { 
            $params['record_id'] = db_insert_id(); $error .= 0;

	    if ( $params['group_alert'] == '1' ) @db_query('INSERT INTO alertas (id_grupo, fecha, descripcion) VALUES (\'' . $params['record_id'] . '\', \'' . date('Y-m-d', $group_date_end) . '\', \'' . $this->lng['form_alert_text'] . $params['group_name'] . '\')');
          }
          else $error .= 1;
        }
        elseif ( $action == 'update' ) {

          $recordValues = $this->getRecordValues($params['record_id']);

          if ( db_query('UPDATE grupos SET nombre = \'' . $params['group_name'] . '\', fecha_inicio = \'' . date('Y-m-d', $group_date_start) . '\', fecha_fin = ' . ( !empty($group_date_end) && $group_date_end < $this->exercise->exercise['end_date'] ? '\'' . date('Y-m-d', $group_date_end) . '\'' : 'NULL' ) . ', nivel = \'' . $params['group_level'] . '\', nivel_objetivo = \'' . $params['group_target_level'] . '\', tipo = \'' . $params['group_type'] . '\', unidades_mes = \'' . $params['group_units'] . '\', importe_unidad = \'' . $params['group_cost'] . '\', direccion = \'' . $params['group_address'] . '\', numero = \'' . $params['group_number'] . '\', piso_bloque = \'' . $params['group_floor'] . '\', localidad = \'' . $params['group_city'] . '\', codigo_postal = \'' . $params['group_postalcod'] . '\', poblacion = \'' . $params['group_state'] . '\', situacion = \'' . $params['group_how_arrive'] . '\', observaciones = \'' . $params['group_comments'] . '\', materiales = \'' . $params['group_equipment'] . '\', id_idioma = \'' . $params['group_language'] . '\', aula = \'' . $params['group_class'] . '\', alerta = \'' . $params['group_alert'] . '\' WHERE id = \'' . $params['record_id'] . '\'') ) {
            $error .= 0;

            if ( $params['group_alert'] != $recordValues['group_alert'] ) {

              if ( ( $params['group_alert'] == '1' ) && ( $recordValues['group_alert'] == '0' ) ) @db_query('INSERT INTO alertas (id_grupo, fecha, descripcion) VALUES (\'' . $params['record_id'] . '\', \'' . date('Y-m-d', $group_date_end) . '\', \'' . $this->lng['form_alert_text'] . $params['group_name'] . '\')');
              else @db_query('DELETE FROM alertas WHERE id_grupo = \'' . $params['record_id'] . '\'');
            }
            elseif( 
                    ( ( $params['group_alert'] == '1' ) && ( $recordValues['group_alert'] == '1' ) ) && 
                    ( $group_date_end != $recordValues['date_end'] ) ) {
                    @db_query('UPDATE alertas SET fecha = \'' . date('Y-m-d', $group_date_end) . '\' WHERE id_grupo = \'' . $params['record_id'] . '\''); }
          }
          else $error .= 2;
        }
      }

      strip_slashes($params);
      return $error;
    }

    //-----------------------------------------------------------------------------------
    // manageHours

    function manageHours( &$hour ) {

      if ( !empty($hour) ) {
        $hour = ereg_replace("[^0-9\.,]", '', $hour);
        $hour = ereg_replace(',', '.', $hour);

        if ( ereg("^([0-9]+)\.([0-9]+)$", $hour, $hours) ) {
          settype($hours[2], "integer");
          if ( $hours[2] > 59  ) $hour = $hours[1] + floor($hours[2] / 60 ) + ( ( $hours[2] % 60 ) / 100 );
        }
      }
    }

    //-----------------------------------------------------------------------------------
    // mangeLevel

    function manageLevel( &$level ) {

      $level = ereg_replace("[^0-9\.,]", '', $level);
      $level = ereg_replace(',', '.', $level);

      if ( $level < '0' || $level > '10' ) $level = '';
    }

    //-----------------------------------------------------------------------------------
    // checkCompany

    function checkCompany( $record ) {

      settype($record, "integer");

      $SQL_query  = db_query('SELECT count(*) AS total FROM empresas WHERE id = \'' . $record . '\'');
      $SQL_values = db_fetch_array($SQL_query);

      if ( $SQL_values['total'] == 1 ) return true;
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // validateParams
    // Comprueba los datos recibidos de los campos de tipo multiple con los valores de
    // las tablas de referencia.

    function validateParams( &$params ) {

      while ( list($field) = each($this->ref_tables) ) {
        if ( !empty($params[$field]) ) {
          if ( is_array($params[$field]) ) {
            while ( list($id) = each($params[$field]) ) { if ( !$this->ref[$field][$params[$field][$id]] ) unset($params[$field][$id]); }
          }
          else { if ( !$this->ref[$field][$params[$field]] ) unset($params[$field]); }
        }
      }
      reset($this->ref_tables);
    }

    //-----------------------------------------------------------------------------------
    // companyGroups

    function companyGroups( $record, $exercise ) {

      $SQL_query = db_query('SELECT count(*) AS total FROM grupos WHERE id_empresa = \'' . $record . '\' AND id_periodo = \'' . $exercise . '\'');
      $SQL_values = db_fetch_array($SQL_query);

      if ( $SQL_values['total'] > 0 ) return true;
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // haveTimeTable

    function haveTimeTable( $record ) {

      $SQL_query  = db_query('SELECT count(*) AS total FROM grupo_horario WHERE id = \'' . $record . '\'');
      $SQL_values = db_fetch_array($SQL_query);
      if ( $SQL_values['total'] > 0 ) return true;
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // copyGroupToExercise

    function copyGroupToExercise( $record ) {

      $exercise = $this->exercise->getNextExerciseId(date('Y-m-d', $this->exercise->exercise['end_date']));

      $error .= ( $exercise ? 0 : 1 );
      $error .= ( $this->checkRecord($record) ? 0 : 1 );

      if ( $exercise ) {
        $SQL_query  = db_query('SELECT count(id_padre) AS total FROM grupos WHERE id_periodo = \'' . $exercise .'\' AND id_padre = \'' . $record . '\'');
        $SQL_values = db_fetch_array($SQL_query);

        $error .= ( $SQL_values['total'] > 0 ? 1 : 0 );
      }

      if ( ereg("^0+$", $error) ) {

        if ( @db_query('INSERT INTO grupos (id_periodo, id_empresa, id_padre, nombre, fecha_inicio, nivel, tipo, unidades_mes, importe_unidad, direccion, numero, piso_bloque, localidad, codigo_postal, poblacion, situacion, observaciones, materiales, id_idioma, aula) SELECT ' . $exercise . ', id_empresa, ' . $record . ', nombre, "' . date('Y-m-d', $this->exercise->exercise['start_date']) . '", nivel, tipo, unidades_mes, importe_unidad, direccion, numero, piso_bloque, localidad, codigo_postal, poblacion, situacion, observaciones, materiales, id_idioma, aula FROM grupos WHERE id = \'' . $record . '\'') ) {

          $newRecord = db_insert_id();

          @db_query('INSERT INTO grupo_horario (id, dia_inicio, dia_fin, hora_inicio, hora_fin) SELECT ' . $newRecord . ', dia_inicio, dia_fin, hora_inicio, hora_fin FROM grupo_horario WHERE id = \'' . $record . '\'');
          @db_query('INSERT INTO alumnos_grupos (id_alumno, id_grupo, id_nivel, id_colectivo, observaciones) SELECT ag.id_alumno, ' . $newRecord . ', ag.id_nivel, ag.id_colectivo, ag.observaciones FROM alumnos_grupos ag LEFT JOIN alumnos a ON ag.id_alumno = a.id WHERE ag.id_grupo = \'' . $record . '\' AND ag.fecha_fin IS NULL AND a.estado != \'2\'');

          $SQL_query = @db_query('SELECT pg.id_profesor, pg.id_grupo, pg.importe, pg.observaciones, phg.id_intervalo_horario, ghi.dia, ghi.hora_inicio, ghi.hora_fin FROM profesores_grupo pg LEFT JOIN profesor_horario_grupo phg ON pg.id = phg.id_profesor_grupo LEFT JOIN grupos_horario_intervalos ghi ON phg.id_intervalo_horario = ghi.id LEFT JOIN profesores p ON pg.id_profesor = p.id WHERE pg.id_grupo = \'' . $record . '\' AND pg.fecha_fin IS NULL AND p.estado != \'2\'');

          if ( db_num_rows($SQL_query) > 0 ) {

            $groupIntervals = array();
            $insertTeachers = array();

            while ( $SQL_values =  db_fetch_array($SQL_query) ) {

              if ( !$insertTeachers[$SQL_values['id_profesor']] ) {
                @db_query('INSERT INTO profesores_grupo (id_profesor, id_grupo, importe, observaciones) VALUES (\'' . $SQL_values['id_profesor'] . '\', \'' . $newRecord . '\', \'' . $SQL_values['importe'] . '\', \'' . $SQL_values['observaciones'] . '\')');
                $newTeacher       = db_insert_id();
                $insertTeachers[$SQL_values['id_profesor']] = $newTeacher;
              }
              else $newTeacher = $insertTeachers[$SQL_values['id_profesor']];

              if ( !empty($SQL_values['id_intervalo_horario']) ) {

                $recordInterval = $SQL_values['dia'] . '-' . $SQL_values['hora_inicio'] . '-' . $SQL_values['hora_fin'];

                if ( !$groupIntervals[$recordInterval] ) {
                  @db_query('INSERT INTO grupos_horario_intervalos (dia, hora_inicio, hora_fin) VALUES (\'' . $SQL_values['dia'] . '\', \'' . $SQL_values['hora_inicio'] . '\', \'' . $SQL_values['hora_fin'] . '\')');

                  $recordIntervalId   = db_insert_id();
                  $groupIntervals[$recordInterval] = $recordIntervalId;
                }
                else $recordIntervalId = $groupIntervals[$recordInterval];

                db_query('INSERT INTO profesor_horario_grupo (id_profesor_grupo, id_intervalo_horario) VALUES (' . $newTeacher . ', ' . $recordIntervalId . ')');
              }
            }
          }
        }
        else $error .= 1;
      }

      return $error;
    }

    //-----------------------------------------------------------------------------------
    // getGroupsByDateAndType

    function getGroupsByDateAndType( $startDate, $endDate, $type ) {

      if ( empty($endDate) ) $endDate = $startDate;
      if ( !is_array($type) ) $type = array();

      for ( $i=0; $i<count($type); $i++ ) settype($type[$i], "integer");

      $gropsByDate = array();

      if ( $startDate <= $endDate ) {

        $start_date = mktime(0, 0, 0, substr($startDate, 4, 2), 1, substr($startDate, 0, 4));
        $end_date   = mktime(0, 0, 0, substr($endDate, 4, 2), date('t', mktime(0, 0, 0, substr($endDate, 4, 2), 1, substr($endDate, 0, 4))), substr($endDate, 0, 4));

        $SQL_query = mysql_query('SELECT g.id, g.nombre, DATE_FORMAT(g.fecha_inicio, "%Y%m") AS inicio_periodo, IF(g.fecha_fin, DATE_FORMAT(g.fecha_fin, "%Y%m"), DATE_FORMAT(el.fecha_fin, "%Y%m")) AS fin_periodo, UNIX_TIMESTAMP(g.fecha_inicio) AS fecha_inicio, IF(g.fecha_fin, UNIX_TIMESTAMP(g.fecha_fin), UNIX_TIMESTAMP(el.fecha_fin) ) AS fecha_fin FROM grupos g LEFT JOIN ejercicios_lectivos el ON g.id_periodo = el.id WHERE g.id_periodo = \'' . $this->exercise->exercise['id'] . '\' AND g.tipo IN ( ' . implode(', ', $type) . ' ) HAVING ( ( inicio_periodo > \'' . $startDate . '\' AND inicio_periodo < \'' . $endDate . '\' ) OR ( inicio_periodo <= \'' . $startDate . '\' AND fin_periodo >= \'' . $endDate . '\' ) OR ( inicio_periodo < \'' . $startDate . '\' AND fin_periodo < \'' . $endDate . '\' ) )');

        if ( mysql_num_rows($SQL_query) ) {

          while ( $SQL_group = mysql_fetch_assoc($SQL_query) ) {

            # El grupo debe tener asignado un horario para que sea mostrado

            $SQL_timetable_query = mysql_query('SELECT dia_inicio, dia_fin FROM grupo_horario WHERE id = \'' . $SQL_group['id'] . '\'');

            if ( mysql_num_rows($SQL_timetable_query) ) {

              # Dias a la semana que el grupo tiene clase

              $classDays = array();

              while ( $SQL_timetable = mysql_fetch_assoc($SQL_timetable_query) ) { 
                for ( $i=$SQL_timetable['dia_inicio']; $i<=$SQL_timetable['dia_fin']; $i++ ) if ( !in_array($i, $classDays) ) $classDays[] = $i;
              }

              # Asignacion de grupos a dias

              $g_date       = ( $SQL_group['fecha_inicio'] > $start_date ? $SQL_group['fecha_inicio'] : $start_date );
              $g_date_end   = ( $SQL_group['fecha_fin'] < $end_date ? $SQL_group['fecha_fin'] : $end_date );

              while ( $g_date <= $g_date_end ) {

                $day_of_week = date('w', $g_date);
                $day_of_week = ( $day_of_week == 0 ? '7' : $day_of_week );
                if ( in_array($day_of_week, $classDays) ) $groupsByDate[$g_date][$SQL_group['id']]['name'] = stripslashes($SQL_group['nombre']);
                $g_date      = mktime(0, 0, 0, date('m', $g_date), ( date('d', $g_date) + 1 ), date('Y', $g_date));
              }

              # Profesores del grupo

              $SQL_teachers_query = mysql_query('SELECT DISTINCT(pg.id_profesor), p.nombre, ghi.dia, IF(pg.fecha_inicio, DATE_FORMAT(pg.fecha_inicio, "%Y%m"), DATE_FORMAT(g.fecha_inicio, "%Y%m")) AS inicio_periodo, IF(pg.fecha_fin, DATE_FORMAT(pg.fecha_fin, "%Y%m"), IF( g.fecha_fin, DATE_FORMAT(g.fecha_fin, "%Y%m"), DATE_FORMAT(el.fecha_fin, "%Y%m") ) ) AS fin_periodo, IF(pg.fecha_inicio, UNIX_TIMESTAMP(pg.fecha_inicio), UNIX_TIMESTAMP(g.fecha_inicio) ) AS fecha_inicio, IF(pg.fecha_fin, UNIX_TIMESTAMP(pg.fecha_fin), IF( g.fecha_fin, UNIX_TIMESTAMP(g.fecha_fin), UNIX_TIMESTAMP(el.fecha_fin) ) ) AS fecha_fin, p.nombre FROM profesores_grupo pg LEFT JOIN grupos g ON pg.id_grupo = g.id LEFT JOIN ejercicios_lectivos el ON g.id_periodo = el.id LEFT JOIN profesores p ON pg.id_profesor = p.id LEFT JOIN profesor_horario_grupo phg ON pg.id = phg.id_profesor_grupo LEFT JOIN grupos_horario_intervalos ghi ON phg.id_intervalo_horario = ghi.id WHERE pg.id_grupo = \'' . $SQL_group['id'] . '\' AND ghi.dia IS NOT NULL GROUP BY id_profesor, dia, fecha_inicio, fecha_fin HAVING ( ( inicio_periodo > \'' . $startDate . '\' AND inicio_periodo < \'' . $endDate . '\' ) OR ( inicio_periodo <= \'' . $startDate . '\' AND fin_periodo >= \'' . $endDate . '\' ) OR ( inicio_periodo < \'' . $startDate . '\' AND fin_periodo < \'' . $endDate . '\' ) ) ORDER BY pg.id_profesor');


              if ( mysql_num_rows($SQL_teachers_query) ) {

                $teachersToGroup = array();

                while ( $SQL_teacher = mysql_fetch_assoc($SQL_teachers_query) ) {
                  $teachersToGroup[$SQL_teacher['id_profesor']]['name']       = stripslashes( $SQL_teacher['nombre'] );
                  $teachersToGroup[$SQL_teacher['id_profesor']]['startdate']  = $SQL_teacher['fecha_inicio'];
                  $teachersToGroup[$SQL_teacher['id_profesor']]['enddate']    = $SQL_teacher['fecha_fin'];
                  $teachersToGroup[$SQL_teacher['id_profesor']]['weekdays'][] = $SQL_teacher['dia'];
                }

                while ( list($teacher) = each($teachersToGroup) ) {

                  $t_date       = ( $teachersToGroup[$teacher]['startdate'] > $start_date ? $teachersToGroup[$teacher]['startdate'] : $start_date );
                  $t_date_end   = ( $teachersToGroup[$teacher]['enddate']   < $end_date   ? $teachersToGroup[$teacher]['enddate']   : $end_date );

                  while ( $t_date<=$t_date_end ) {

                    $day_of_week = date('w', $t_date);
                    $day_of_week = ( $day_of_week == 0 ? '7' : $day_of_week );
                    if ( in_array($day_of_week, $teachersToGroup[$teacher]['weekdays']) && $groupsByDate[$t_date] ) $groupsByDate[$t_date][$SQL_group['id']]['teachers'][] = $teachersToGroup[$teacher]['name'];
                    $t_date      = mktime(0, 0, 0, date('m', $t_date), ( date('d', $t_date) + 1 ), date('Y', $t_date));
                  }
                }
              }
            }
          }
        }
      }

      return $groupsByDate;
    }

    function getTeachersToGroup( $group ) {

      $teachers = array();

      $SQL_query = db_query('SELECT DISTINCT(pg.id_profesor), CONCAT(p.apellidos, \' \', p.nombre) AS nombre FROM profesores_grupo pg LEFT JOIN profesores p ON pg.id_profesor = p.id WHERE id_grupo = \'' . $group . '\' AND pg.id_sustitucion = \'0\'');

      if ( db_num_rows($SQL_query) ) {
        while ( $SQL_record = db_fetch_array($SQL_query) ) $teachers[$SQL_record['id_profesor']] = $SQL_record['nombre'];
      }

      return $teachers;
    }

    function getGroupTimeTable( $group ) {

      $timeTable = array();

      $SQL_query = db_query('SELECT gh.dia_inicio, gh.dia_fin, TIME_FORMAT(rf1.hora, "%H:%i") AS hora_inicio, TIME_FORMAT(rf2.hora, "%H:%i") AS hora_fin FROM grupo_horario gh LEFT JOIN ref_horas rf1 ON gh.hora_inicio = rf1.id LEFT JOIN ref_horas rf2 ON gh.hora_fin = rf2.id  WHERE gh.id = \'' . $group . '\'');

      if ( db_num_rows($SQL_query) ) {

        while ( $SQL_record = db_fetch_array($SQL_query) ) $timeTable[] = $this->lng['list_day_' . $SQL_record['dia_inicio']] . ( $SQL_record['dia_fin'] > $SQL_record['dia_inicio'] ? ' - ' . $this->lng['list_day_' . $SQL_record['dia_fin']] : '' ) . ' [' . $SQL_record['hora_inicio'] . ' - ' . $SQL_record['hora_fin'] . ']';
      }

      return($timeTable);
    }
  }
?>