<?
  class teacher {

    var $teacher, $fields, $query_order, $num_records, $records_view, $config, $ref_tables, $ref;

    function teacher() {

      global $CONF, $LNG, $EXERCISE;

      $this->config        = &$CONF;
      $this->lng           = &$LNG;
      $this->exercise      = &$EXERCISE;
      $this->ref_tables    = array();
      $this->ref_values    = array();
      $this->fields        = array( '1'  => 'id',
                                    '2'  => 'nombre',
                                    '3'  => 'apellidos',
                                    '4'  => 'cif',
                                    '5'  => 'fecha_nacimiento',
                                    '6'  => 'nacionalidad',
                                    '7'  => 'telefono',
                                    '8'  => 'email',
                                    '9'  => 'estado',
                                    '10' => 'evaluacion',
                                    '11' => 'telefonicas',
                                    '12' => 'coche',
                                    '13' => 'poblacion',
                                    '14' => 'tipo_contrato'
                                  );

      $this->query_order   = array( 'a' => 'ASC',
                                    'd' => 'DESC' );

      $this->records_view  = array( 'order'   => array('1','3','2','4','5','9','6','7','8'),
                                    'width'   => array('25', '140', '90', '90', '73', '37', '90', '80','120'),
                                    'orderby' => array(true, true, true, true, true, true, true, true, true),
                                    'search'  => array(true, true, true, true, true, false, false, true, true) );

      /*  Tablas de referencia */

      $this->ref_tables    = array( 'teacher_nationality' => 'ref_nacionalidad',
                                    'teacher_deduction'   => 'ref_retenciones',
                                    'teacher_zones'       => 'ref_zonas',
                                    'teacher_state'       => 'ref_provincias',
                                    'teacher_knowledge'   => 'ref_conocimientos',
                                    'teacher_languages'   => 'ref_idiomas',
                                    'teacher_preferences' => 'ref_preferencias',
                                    'teacher_class_type'  => 'ref_tipos_clase',
                                    'teacher_id_type'     => 'ref_identificacion',
                                    'teacher_contract'    => 'ref_contratos'
                                  );

      $this->fields_table  = array( 'teacher_zones'       => 'profesor_zonas',
                                    'teacher_knowledge'   => 'profesor_conocimientos',
                                    'teacher_languages'   => 'profesor_idiomas',
                                    'teacher_preferences' => 'profesor_preferencias',
                                    'teacher_class_type'  => 'profesor_tipo_clase',
                                    'teacher_companies'   => 'profesor_empresa'
                                  );


      $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 profesores' . $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 id, nombre, apellidos, cif, fecha_nacimiento, nacionalidad, telefono, estado, email FROM profesores' . $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'] = stripslashes($SQL_record['apellidos']);
          $records_list[$SQL_record['id']]['4'] = $SQL_record['cif'];
          $records_list[$SQL_record['id']]['5'] = $SQL_record['fecha_nacimiento'];
          $records_list[$SQL_record['id']]['6'] = $this->ref['teacher_nationality'][$SQL_record['nacionalidad']];
          $records_list[$SQL_record['id']]['7'] = $SQL_record['telefono'];
          $records_list[$SQL_record['id']]['8'] = $SQL_record['email'];
          $records_list[$SQL_record['id']]['9'] = $this->lng['form_status_' . $SQL_record['estado']];
        }
      }

      return $records_list;
    }

    //-----------------------------------------------------------------------------------
    // generateFilterQuery

    function generateFilterQuery( $filter_field = '', $filter_values = '', $adv_search = '', $rawFilter = FALSE ) {

      $SQL_filter = ( $rawFilter ? '' : ' WHERE 1 AND estado != \'2\' ');

      if ( is_array($filter_values) ) {
        if ( count($filter_values) > 0 ) {
          while ( list($id) = each($filter_values) ) {
            if ( isset( $this->fields[$id]) && !empty($filter_values[$id]) ) $SQL_filter .= ' AND ' . $this->fields[$id] . ' LIKE \'%' . addslashes($filter_values[$id]) . '%\'';
          }
        }
      }
      elseif ( trim($filter_values) != '' && isset( $this->fields[$filter_field] ) ) $SQL_filter .= ' AND ' . $this->fields[$filter_field] . ' LIKE \'%' . addslashes($filter_values) . '%\'';

      if ( is_array($adv_search) ) {

        if ( in_array($adv_search['filter_show'], array('>','<','=')) ) { 

          if ( is_numeric($adv_search['filter_number']) && $adv_search['filter_date'] > '0' ) {

            switch ( $adv_search['filter_date'] ) {

              case '365':
                $date = mktime(0,0,0, date('n'), date('j'), ( date('Y') - $adv_search['filter_number'] ) );
              break;

              case '30':
                $date = mktime(0,0,0,( date('n') - $adv_search['filter_number'] ), date('j'), date('Y') );
              break;

              default:
                $date = mktime(0,0,0,date('n'),( date('j') - ($adv_search['filter_number'] * $adv_search['filter_date']) ), date('Y') );
              break;
            }

            $SQL_filter .= ' AND fecha_alta ' . $adv_search['filter_show'] . '"' . date('Y-m-d', $date) . '"';
          }
        }
      }

      return $SQL_filter;
    }

    //-----------------------------------------------------------------------------------
    // advancedSearch

    function advancedSearch( $params ) {

      $teachers     = array();
      $referenceIds = array();
      $SQL_where    = '';
      $noValues     = false;

      if ( $params['teacher_freetime'] ) {

        if ( !empty($params['teacher_start_free_date']) ) {
          if ( ereg("^([0-9]{2})\/([0-9]{2})\/([0-9]{4})$", $params['teacher_start_free_date'], $start_date ) ) {
            $date = mktime(0, 0, 0, $start_date[2], $start_date[1], $start_date[3]);
            if ( $date < $this->exercise->exercise['start_date'] || $date > $this->exercise->exercise['end_date'] ) $noValues = true;
            else $params['teacher_start_free_date'] = $date;
          }
          else $noValues = true;
        }
        else $params['teacher_start_free_date'] = $this->exercise->exercise['start_date'];

        if ( !empty($params['teacher_end_free_date']) ) {
          if ( ereg("^([0-9]{2})\/([0-9]{2})\/([0-9]{4})$", $params['teacher_end_free_date'], $end_date ) ) {
            $date = mktime(0, 0, 0, $end_date[2], $end_date[1], $end_date[3]);
            if ( $date < $this->exercise->exercise['start_date'] || $date > $this->exercise->exercise['end_date'] ) $noValues = true;
            else $params['teacher_end_free_date'] = $date;
          }
          else $noValues = true;
        }
        else $params['teacher_end_free_date'] = $this->exercise->exercise['end_date'];

        if ( $params['teacher_end_free_date'] < $params['teacher_start_free_date'] ) $noValues = true;
      }

      if ( !$noValues ) {

        // GESTION CAMPOS DE BUSQUEDA POR DEFECTO

        if ( !empty($params['filter_by']) && trim($params['filter_name']) != '' ) {
          $SQL_where .= $this->generateFilterQuery($params['filter_by'], $params['filter_name'], '', TRUE);
        }
      }

      if ( !$noValues ) {

        while ( list($field) = each($params) ) {

          if ( is_array($params[$field]) && $this->fields_table[$field] ) {

            // GESTION DE CAMPOS MULTIPLES

            while ( list($i) = each($params[$field]) ) {

              $ids = array();

              $SQL_query = db_query('SELECT id_profesor FROM ' . $this->fields_table[$field] . ' WHERE id_campo = \'' . $params[$field][$i] . '\'');

              if ( db_num_rows($SQL_query) > 0 ) {

                while ( $SQL_values = db_fetch_array($SQL_query) ) $ids[] = $SQL_values['id_profesor'];

                if ( count($referenceIds) > 0 ) $referenceIds = array_intersect($ids, $referenceIds);
                else $referenceIds = $ids;

                if ( count($referenceIds) == 0 ) { $noValues = true; break(2); }
              }
              else { $noValues = true; break(2); }
            }

            if ( count($teachers) > 0 ) $teachers = array_intersect($referenceIds, $teachers);
            else $teachers = $referenceIds;
            if ( count($teachers) == 0 ) { $noValues = true; break; }
          }
          else {

            // GESTION CAMPOS SIMPLES

            $classField = substr($field, 1);

            if ( $params[$field] != '' && $this->fields[$classField] ) {
              $SQL_operator = ( $classField == 10 ? '>=' : '=' );
              $SQL_where   .= ' AND ' . $this->fields[$classField] . ' ' . $SQL_operator . ' \'' . addslashes($params[$field]) . '\'';
            }
          }
        }
      }

      if ( !$noValues && is_array($params['timetable'])) {

        // HORARIO COMPATIBLE

        $timeValues = $this->manageTime($params['timetable']);

        $teacherTimes = array();
        $referenceIds = array();

        while ( list($id) = each($timeValues) ) {

          $ids = array();
          ereg("^([1-7]{1})-([0-9]{1,2})-([0-9]{1,2})$", $timeValues[$id], $interval );
          $SQL_query = db_query('SELECT ph.id FROM profesor_horario ph LEFT JOIN profesores p ON ph.id = p.id WHERE ( ph.dia_inicio <= \'' . $interval[1] . '\' AND ph.dia_fin >= \'' . $interval[1] . '\' ) AND ( ph.hora_inicio <= \'' . $interval[2] . '\' AND ph.hora_fin >= \'' . $interval[3] . '\' )');

          if ( db_num_rows($SQL_query) > 0 ) {

            while ( $SQL_values = db_fetch_array($SQL_query) ) $ids[] = $SQL_values['id'];

            if ( count($referenceIds) > 0 ) $referenceIds = array_intersect($ids, $referenceIds);
            else $referenceIds = $ids;

            if ( count($referenceIds) == 0 ) { $noValues = true; break; }
          }
          else { $noValues = true; break; }
        }

        if ( count($teachers) > 0 && count($referenceIds) > 0 ) $teachers = array_intersect($referenceIds, $teachers);
        else $teachers = $referenceIds;

        if ( count($teachers) == 0  ) $noValues = true;

        if ( !$noValues && $params['teacher_freetime'] ) {

          // HORARIO DISPONIBLE

          $teacherTimes = array();
          $referenceIds = array();
          $startDate    = date('Y-m-d', $params['teacher_start_free_date']);
          $endDate      = date('Y-m-d', $params['teacher_end_free_date']);

          reset($timeValues);

          while ( list($id) = each($timeValues) ) {

            $ids = array();
            ereg("^([1-7]{1})-([0-9]{1,2})-([0-9]{1,2})$", $timeValues[$id], $interval );

            $SQL_query = db_query('SELECT pg.id_profesor, IF( pg.fecha_inicio, pg.fecha_inicio, g.fecha_inicio ) AS fecha_inicio, IF( pg.fecha_fin, pg.fecha_fin, IF( g.fecha_fin, g.fecha_fin, e.fecha_fin ) ) AS fecha_fin, ghi.dia, ghi.hora_inicio, ghi.hora_fin FROM profesores_grupo pg LEFT JOIN grupos g ON pg.id_grupo = g.id LEFT JOIN ejercicios_lectivos e ON g.id_periodo = e.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_profesor IN (' . implode(',', $teachers) . ') AND ghi.dia = \'' . $interval[1] . '\' AND ( ( ghi.hora_inicio > \'' . $interval[2] . '\' AND ghi.hora_fin > \'' . $interval[2] . '\' AND ghi.hora_fin >= \'' . $interval[3] . '\' ) OR ( ghi.hora_inicio >= \'' . $interval[2] . '\' AND ghi.hora_fin >= \'' . $interval[3] . '\' ) OR ( ghi.hora_inicio <= \'' . $interval[2] . '\' AND ghi.hora_fin > \'' . $interval[2] . '\' AND ghi.hora_fin < \'' . $interval[3] . '\' ) ) HAVING ( ( fecha_inicio > \'' . $startDate . '\' AND fecha_inicio < \'' . $endDate . '\' ) OR ( fecha_inicio <= \'' . $startDate . '\' AND fecha_fin >= \'' . $endDate . '\' ) OR ( fecha_inicio < \'' . $startDate . '\' AND fecha_fin < \'' . $endDate . '\' ) )');

            if ( db_num_rows($SQL_query) > 0 ) {

              while ( $SQL_values = db_fetch_array($SQL_query) ) { if ( !in_array($SQL_values['id_profesor'], $ids) ) $ids[] = $SQL_values['id_profesor']; }

              $teachers = array_diff($teachers, $ids);
              if ( count($teachers) == 0 ) { $noValues = true; break; }
            }
          }
        }
      }

      if ( !$noValues ) {

        if ( count($teachers) > 0 && !empty($SQL_where) ) {

          $SQL_query = db_query('SELECT id FROM profesores WHERE 1 AND estado != \'2\' ' . $SQL_where);

          if ( db_num_rows($SQL_query) > 0 ) {

            $ids = array();

            while ( $SQL_values = db_fetch_array($SQL_query) ) $ids[] = $SQL_values['id'];
            $teachers = array_intersect($ids, $teachers);
            if ( count($teachers) > 0 ) $SQL_filter = 'AND id IN (' . implode(',', $teachers) . ')';
            else $SQL_filter = ' AND id = \'0\'';
          }
          else $SQL_filter = ' AND id = \'0\'';
        }
        else $SQL_filter = $SQL_where . ( count($teachers) > 0 ? ' AND id IN (' . implode(',', $teachers) . ')' : '' );
      }
      else $SQL_filter = ' AND id = \'0\'';

      return $SQL_filter;
    }

    //-----------------------------------------------------------------------------------
    // manageTime

    function manageTime($times) {

      $intervals = array();

      if ( is_array($times) ) {
        foreach ( $times AS $time ) {
          if ( ereg("^([1-7]{1})-([0-9]{1,2})-([0-9]{1,2})$", $time ) ) $intervals[] = $time;
          elseif( ereg("^([1-7]{1})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})$", $time, $interval ) ) {
            for ( $i=$interval[1]; $i<=$interval[2]; $i++ ) $intervals[] = $i . '-' . $interval[3] . '-' . $interval[4];
          }
        }
      }

      return $intervals;
    }
    
    //-----------------------------------------------------------------------------------
    // 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 profesores_grupo WHERE id_profesor = \'' . $record . '\'');
        $SQL_values = db_fetch_array($SQL_query);

        if ( $SQL_values['total'] > 0 ) {

          if ( @db_query('UPDATE profesores SET apellidos = \'\', cif = \'\', fecha_nacimiento = \'\', nacionalidad = \'\', telefono = \'\', telefono_2 = \'\', fax = \'\', email = \'\', direccion = \'\', numero = \'\', piso_bloque = \'\', localidad = \'\', codigo_postal = \'\', poblacion = \'\', direccion_2 = \'\', numero_2 = \'\', piso_bloque_2 = \'\', localidad_2 = \'\', codigo_postal_2 = \'\', poblacion_2 = \'\', estado = \'2\' WHERE id = \'' . $record . '\'') ) $error .= 0;
          else $error .= 1;
        }
	elseif ( @db_query('DELETE FROM profesores WHERE id = \'' . $record . '\'') ) {
          $error .= 0;

          while ( list($database) = each($this->fields_table) ) @db_query('DELETE FROM ' . $this->fields_table[$database] . ' WHERE id_profesor = \'' . $record . '\'');
        }
        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 profesores 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 id, nombre AS teacher_name, apellidos AS teacher_surname, tipo_identificacion AS teacher_id_type, cif AS teacher_nif, DATE_FORMAT(fecha_nacimiento, "%d/%m/%Y") AS teacher_birthdate, nacionalidad AS teacher_nationality, telefono AS teacher_phone, telefono_2 AS teacher_phone_2, fax AS teacher_fax, email AS teacher_email, retenciones AS teacher_deduction, direccion AS teacher_address, IF(numero, numero, \'\') AS teacher_number, piso_bloque AS teacher_floor, localidad AS teacher_city, codigo_postal AS teacher_postalcod, poblacion AS teacher_state, direccion_2 AS teacher_address_2, numero_2 AS teacher_number_2, piso_bloque_2 AS teacher_floor_2, localidad_2 AS teacher_city_2, codigo_postal_2 AS teacher_postalcod_2, poblacion_2 AS teacher_state_2, telefonicas AS teacher_telephone_class, coche AS teacher_car, IF(fecha_entrevista, DATE_FORMAT(fecha_entrevista, "%d/%m/%Y"), \'\') AS teacher_date_interview, evaluacion AS teacher_evaluation, banco AS teacher_bank, MID(cuenta_bancaria,1,4) AS teacher_bank_office, MID(cuenta_bancaria,5,4) AS teacher_bank_entity, MID(cuenta_bancaria,9,2) AS teacher_bank_dc, MID(cuenta_bancaria,11) AS teacher_bank_account, cheque AS teacher_cheque, observaciones AS teacher_comments, estado AS teacher_status, IF(experiencia, experiencia, \'\') AS teacher_experience, puntos_fuertes AS teacher_strengths, puntos_debiles AS teacher_weaknessess, tipo_contrato AS teacher_contract, IF(horas_contrato > 0, horas_contrato, \'\') AS teacher_contract_hours, imagen AS teacher_photo FROM profesores WHERE id = \'' . $record . '\'');

        if ( db_num_rows($SQL_check_record) == 1 ) {

          $this->teacher = db_fetch_array($SQL_check_record);
          strip_slashes($this->teacher);
          $this->getReferenceIds($record);

          return $this->teacher;
        }
        else return false;
      }
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // getReferenceIds
    // Obtiene los valores de los campos de tipo multiple asignados a un profesor.

    function getReferenceIds ( $record ) {

      while ( list($field) = each($this->fields_table) ) { 
        $SQL_query = db_query('SELECT id_campo FROM ' . $this->fields_table[$field] . ' WHERE id_profesor = \'' . $record . '\'');
        if ( db_num_rows($SQL_query) > 0 ) {
          while ( $SQL_values = db_fetch_array($SQL_query) ) $this->teacher[$field][] = $SQL_values['id_campo'];
        }
      }
    }

    //-----------------------------------------------------------------------------------
    // 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] > Campo nombre
    //     [3] > Campo Fecha Nacimiento
    //     [4] > Campo NIF
    //     [5] > 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 unset($params['record_id']);
      }
      else $action = 'insert';

      $params['teacher_nif']          = eregi_replace("[^a-z0-9]", '', $params['teacher_nif']);
      $params['teacher_bank_office']  = str_pad(eregi_replace("[^0-9]", '', $params['teacher_bank_office']), 4, '0', STR_PAD_LEFT);
      $params['teacher_bank_entity']  = str_pad(eregi_replace("[^0-9]", '', $params['teacher_bank_entity']), 4, '0', STR_PAD_LEFT);
      $params['teacher_bank_dc']      = str_pad(eregi_replace("[^0-9]", '', $params['teacher_bank_dc']), 2, '0', STR_PAD_LEFT);
      $params['teacher_bank_account'] = eregi_replace("[^0-9]", '', $params['teacher_bank_account']);
      
      $teacher_birthdate     = $this->validateDate($params['teacher_birthdate']);
      $teacher_interview     = $this->validateDate($params['teacher_date_interview']);
      $this->validateParams($params);

      // Generacion del codigo de error

      $error  = ( $action == '' ? 1 : 0 );
      $error .= ( empty($params['teacher_name']) ? 1 : 0 );
      $error .= ( !empty($params['teacher_nif']) && !$this->checkNif($params['teacher_nif'], $params['record_id']) ? 1 : 0 );
      $error .= ( !$teacher_birthdate ? 1 : 0 );

      // Acciones sobre la BBDD

      if ( ereg("^0+$", $error) ) {

        format_record($params);

        if ( $action == 'insert' ) {
          if ( db_query('INSERT INTO profesores (nombre, apellidos, tipo_identificacion, cif, fecha_nacimiento, nacionalidad, telefono, telefono_2, fax, email, direccion, numero, piso_bloque, localidad, codigo_postal, poblacion, direccion_2, numero_2, piso_bloque_2, localidad_2, codigo_postal_2, poblacion_2, telefonicas, coche, fecha_entrevista, evaluacion, banco, cuenta_bancaria, cheque, retenciones, observaciones, estado, experiencia, fecha_alta, puntos_fuertes, puntos_debiles, tipo_contrato, horas_contrato, imagen) VALUES (\'' . $params['teacher_name'] . '\', \'' . $params['teacher_surname'] . '\', \'' . $params['teacher_id_type'] . '\', \'' . $params['teacher_nif'] . '\', \'' . $teacher_birthdate . '\', \'' . $params['teacher_nationality'] . '\', \'' . $params['teacher_phone'] . '\', \'' . $params['teacher_phone_2'] . '\', \'' . $params['teacher_fax'] . '\', \'' . $params['teacher_email'] . '\', \'' . $params['teacher_address'] . '\', \'' . $params['teacher_number'] . '\', \'' . $params['teacher_floor'] . '\', \'' . $params['teacher_city'] . '\', \'' . $params['teacher_postalcod'] . '\', \'' . $params['teacher_state'] . '\', \'' . $params['teacher_address_2'] . '\', \'' . $params['teacher_number_2'] . '\', \'' . $params['teacher_floor_2'] . '\', \'' . $params['teacher_city_2'] . '\', \'' . $params['teacher_postalcod_2'] . '\', \'' . $params['teacher_state_2'] . '\', \'' . $params['teacher_telephone_class'] . '\', \'' . $params['teacher_car'] . '\', \'' . $teacher_interview . '\', \'' . $params['teacher_evaluation'] . '\', \'' . $params['teacher_bank'] . '\', \'' . $params['teacher_bank_office'] . $params['teacher_bank_entity'] . $params['teacher_bank_dc'] . $params['teacher_bank_account'] . '\', \'' . $params['teacher_cheque'] . '\', \'' . $params['teacher_deduction'] . '\', \'' . $params['teacher_comments'] . '\', \'' . $params['teacher_status'] . '\', \'' . $params['teacher_experience'] . '\', now(), \'' . $params['teacher_strengths'] . '\', \'' . $params['teacher_weaknessess'] . '\', \'' . $params['teacher_contract'] . '\', \'' . $params['teacher_contract_hours'] . '\', \'' . $params['teacher_photo'] . '\')') ) {

            $teacher_id = db_insert_id();
            $this->manageReferenceValues('insert', $teacher_id, $params);
	    $params['record_id'] = $teacher_id;
            $error .= 0;
          }
          else $error .= 1;
        }
        elseif ( $action == 'update' ) {
          if ( db_query('UPDATE profesores SET nombre = \'' . $params['teacher_name'] . '\', apellidos = \'' . $params['teacher_surname'] . '\', tipo_identificacion = \'' . $params['teacher_id_type'] . '\', cif = \'' . $params['teacher_nif'] . '\', fecha_nacimiento = \'' . $teacher_birthdate . '\', nacionalidad = \'' . $params['teacher_nationality'] . '\', telefono = \'' . $params['teacher_phone'] . '\', telefono_2 = \'' . $params['teacher_phone_2'] . '\', fax = \'' . $params['teacher_fax'] . '\', email = \'' . $params['teacher_email'] . '\', direccion = \'' . $params['teacher_address'] . '\', numero = \'' . $params['teacher_number'] . '\', piso_bloque = \'' . $params['teacher_floor'] . '\', localidad = \'' . $params['teacher_city'] . '\', codigo_postal = \'' . $params['teacher_postalcod'] . '\', poblacion = \'' . $params['teacher_state'] . '\', direccion_2 = \'' . $params['teacher_address_2'] . '\', numero_2 = \'' . $params['teacher_number_2'] . '\', piso_bloque_2 = \'' . $params['teacher_floor_2'] . '\', localidad_2 = \'' . $params['teacher_city_2'] . '\', codigo_postal_2 = \'' . $params['teacher_postalcod_2'] . '\', poblacion_2 = \'' . $params['teacher_state_2'] . '\', telefonicas = \'' . $params['teacher_telephone_class'] . '\', coche = \'' . $params['teacher_car'] . '\', fecha_entrevista = \'' . $teacher_interview . '\', evaluacion = \'' . $params['teacher_evaluation'] . '\', banco = \'' . $params['teacher_bank'] . '\', cuenta_bancaria = \'' . $params['teacher_bank_office'] . $params['teacher_bank_entity'] . $params['teacher_bank_dc'] . $params['teacher_bank_account'] . '\', cheque = \'' . $params['teacher_cheque'] . '\', retenciones = \'' . $params['teacher_deduction'] . '\', observaciones = \'' . $params['teacher_comments'] . '\', estado = \'' . $params['teacher_status'] . '\', experiencia = \'' . $params['teacher_experience'] . '\', puntos_fuertes = \'' . $params['teacher_strengths'] . '\', puntos_debiles = \'' . $params['teacher_weaknessess'] . '\', tipo_contrato = \'' . $params['teacher_contract'] . '\', horas_contrato = \'' . $params['teacher_contract_hours'] . '\', imagen = \'' . $params['teacher_photo'] . '\'  WHERE id = \'' . $params['record_id'] . '\'') ) {

            $this->manageReferenceValues('update', $params['record_id'], $params);
            $error .= 0;
          }
          else $error .= 2;
        }
      }

      strip_slashes($params);
      return $error;
    }

    //-----------------------------------------------------------------------------------
    // 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);
    }

    //-----------------------------------------------------------------------------------
    // manageReferenceValues
    // INSERTA, ACTUALIZA y BORRA los valores de las tablas de referencia.

    function manageReferenceValues( $action, $teacher_id, $params ) {

      while ( list($field) = each($this->fields_table) ) {
        if ( is_array($params[$field]) ) {

          if ( $action == 'update' ) {
            $ids       = array();
            $values    = array();

            $SQL_query = db_query('SELECT id_campo FROM ' . $this->fields_table[$field] . ' WHERE id_profesor = \'' . $teacher_id . '\'');
            if ( db_num_rows($SQL_query) > 0 ) { while ( $SQL_values = db_fetch_array($SQL_query) ) $values[] = $SQL_values['id_campo']; }
          }

          while ( list($id) = each($params[$field]) ) {
            if ( $action == 'insert' ) @db_query('INSERT INTO ' . $this->fields_table[$field] . ' (id_profesor, id_campo) VALUES (\'' . $teacher_id . '\', \'' . $params[$field][$id] . '\')');
            elseif ( $action == 'update' ) {
              if ( !in_array($params[$field][$id], $values) ) {
                @db_query('INSERT INTO ' . $this->fields_table[$field] . ' (id_profesor, id_campo) VALUES (\'' . $teacher_id . '\', \'' . $params[$field][$id] . '\')');
                $ids[] = $params[$field][$id];
              }
              else $ids[] = $params[$field][$id];
            }
          }

          if ( $action == 'update' ) {
            $delete_ids = array_diff($values, $ids);
            if ( count($delete_ids) > 0 ) db_query('DELETE FROM ' . $this->fields_table[$field] . ' WHERE id_profesor = \'' . $teacher_id . '\' AND id_campo IN (' . implode(',', $delete_ids) . ')');
          }
        }
      }
      reset($this->fields_table);
    }

    //-----------------------------------------------------------------------------------
    // validateDate
    // Comprueba que la fecha suministrada es una fecha valida. Si es asi, devuelve la fe
    // cha formateada para MySQL.

    function validateDate( &$date ) {

      if ( !empty($date) && ereg("^([0-9]{1,2})\/([0-9]{1,2})\/([0-9]{4})$", $date, $regs ) ) { 
        if ( checkdate($regs[2], $regs[1], $regs[3]) ) return $regs[3] . '-' . $regs[2] . '-' . $regs[1];
        else { unset($date); return false; }
      }
      else { unset($date); return false; }
    }

    //-----------------------------------------------------------------------------------
    // checkNif

    function checkNif( $nif, $record = '' ) {

      $SQL_query  = db_query('SELECT count(*) AS total FROM profesores WHERE cif = \'' . $nif . '\'' . ( !empty($record) ? ' AND id != \'' . $record . '\'' : '' ) );
      $SQL_values = db_fetch_array($SQL_query);

      if ( $SQL_values['total'] > 0 ) return false;
      else return true;
    }

    //-----------------------------------------------------------------------------------
    // checkTeacherToGroup

    function checkTeacherToGroup( &$record, $group ) {

      settype($record, "integer");

      $SQL_query  = db_query('SELECT count(*) AS total FROM profesores_grupo WHERE id = \'' . $record . '\' AND id_grupo = \'' . $group . '\'');
      $SQL_values = db_fetch_array($SQL_query);
      if ( $SQL_values['total'] > 0 ) return true;
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // checkTeacherNoGroup

    function checkTeacherNoGroup( $teacher_id, $group_id, $teacher_date_start, $teacher_date_end, $exclude = '' ) {

      $SQL_teacher_date_start = date('Y-m-d', $teacher_date_start);
      $SQL_teacher_date_end   = date('Y-m-d', $teacher_date_end);

      $SQL_query = db_query('SELECT IF( pg.fecha_inicio, pg.fecha_inicio, g.fecha_inicio ) AS fecha_inicio, IF( pg.fecha_fin, pg.fecha_fin, IF( g.fecha_fin, g.fecha_fin, e.fecha_fin ) ) AS fecha_fin FROM profesores_grupo pg LEFT JOIN grupos g ON pg.id_grupo = g.id LEFT JOIN ejercicios_lectivos e ON g.id_periodo = e.id WHERE pg.id_profesor = \'' . $teacher_id . '\' AND pg.id_grupo = \'' . $group_id . '\'' . ( !empty($exclude) ? ' AND pg.id != \'' . $exclude . '\'' : '' ) . ' HAVING ( ( fecha_inicio <= "' . $SQL_teacher_date_end . '" AND fecha_fin >= "' . $SQL_teacher_date_end . '" ) OR ( fecha_inicio <= "' . $SQL_teacher_date_start . '" AND fecha_fin >= "' . $SQL_teacher_date_start . '" ) OR ( fecha_inicio = "' . $SQL_teacher_date_start . '" AND fecha_fin = "' . $SQL_teacher_date_end . '" ) )');

      if ( db_num_rows($SQL_query) == 0 ) return true;
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // getTeacherToGroupValues

    function getTeacherToGroupValues( $record ) {

      $SQL_query = db_query('SELECT pg.id, pg.id_profesor AS teacher_id, pg.id_grupo AS group_id, IF(pg.importe > 0, pg.importe, \'\') AS teacher_cost, pg.observaciones AS teacher_comments, IF( pg.fecha_inicio, UNIX_TIMESTAMP(pg.fecha_inicio), UNIX_TIMESTAMP(g.fecha_inicio) ) AS date_start, IF( pg.fecha_fin, UNIX_TIMESTAMP(pg.fecha_fin), IF(g.fecha_fin, UNIX_TIMESTAMP(g.fecha_fin), UNIX_TIMESTAMP(e.fecha_fin) ) ) AS date_end, pg.id_sustitucion AS teacher_replace_to_id, CONCAT(p.apellidos, \' \', p.nombre) AS teacher_name, estado AS teacher_status FROM profesores_grupo pg LEFT JOIN grupos g ON pg.id_grupo = g.id LEFT JOIN ejercicios_lectivos e ON g.id_periodo = e.id LEFT JOIN profesores p ON pg.id_profesor = p.id WHERE pg.id = \'' . $record . '\'');

      if ( db_num_rows($SQL_query) == 1 ) {

        $SQL_values = db_fetch_array($SQL_query);
        strip_slashes($SQL_values);

        $SQL_values['teacher_date_start'] = date('d/m/Y', $SQL_values['date_start']);
        $SQL_values['teacher_date_end']   = date('d/m/Y', $SQL_values['date_end']);

        return $SQL_values;
      }
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // getTeacherReplaceToValues

    function getTeacherReplaceToValues( $record, $group ) {
      $SQL_query = db_query('SELECT id FROM profesores_grupo WHERE id_sustitucion = \'' . $record . '\' AND id_grupo = \'' . $group . '\'');

      if ( db_num_rows($SQL_query) ) {

	$teachers = array();

        while ( $SQL_record = db_fetch_array($SQL_query) ) $teachers[] = $this->getTeacherToGroupValues($SQL_record['id']);
        return $teachers;
      }
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // deleteTeacherToGroup

    function deleteTeacherToGroup( $record, $groupValues ) {

      $error = '';
      $error .= ( $this->checkTeacherToGroup($record, $groupValues['id']) ? 0 : 1 );

      $recordValues     = $this->getTeacherToGroupValues($record);
      $recordReplaceFor = $this->getTeacherReplaceToValues($record, $groupValues['id']);

      if ( !is_array($recordReplaceFor) ) unset($recordReplaceFor);

      if ( ereg("^0+$", $error) ) {
        if ( ($recordReplaceFor['id']) ) $error .= 1;
        else {
          if ( @db_query('DELETE FROM profesores_grupo WHERE id = \'' . $record . '\'') ) { 

            // HORARIO

            $SQL_query = db_query('SELECT id_intervalo_horario FROM profesor_horario_grupo WHERE id_profesor_grupo = \'' . $record . '\'');

            if ( db_num_rows($SQL_query) > 0 ) {

              $teacherIntervals = array();

              while ( $SQL_values = db_fetch_array($SQL_query) ) $teacherIntervals[] = $SQL_values['id_intervalo_horario'];

              @db_query('DELETE FROM profesor_horario_grupo WHERE id_profesor_grupo = \'' . $record . '\' AND id_intervalo_horario IN (' . implode(',', $teacherIntervals) . ')');

              $activeIntervals = $this->getUniqueTimeIntervals($teacherIntervals);
              $delIntervalList = array_diff($teacherIntervals, $activeIntervals);

              if ( count($delIntervalList) > 0 ) @db_query('DELETE FROM grupos_horario_intervalos WHERE id IN (' . implode(',', $delIntervalList) . ')');
            }

            $delDateStart = '';
            $delDateStop  = '';
	    
            // MASTERLIST
            // Registros anteriores

            $SQL_query_start_date = db_query('SELECT id, UNIX_TIMESTAMP(fecha_fin) AS fecha_fin FROM profesores_grupo WHERE id_grupo = \'' . $groupValues['id'] . '\' AND id_profesor = \'' . $recordValues['teacher_id'] . '\' AND fecha_fin IS NOT NULL AND fecha_fin < "' . date('Y-m-d', $recordValues['date_start']) . '" ORDER BY fecha_fin DESC LIMIT 1');

            if ( db_num_rows($SQL_query_start_date) ) {
              $startDate    = db_fetch_array($SQL_query_start_date);
              $delDateStart = date('Y-m-d', $startDate['fecha_fin']);
            }
            else $delDateStart = date('Y-m-d', mktime(0, 0, 0, date('m', $recordValues['date_start']), -1, date('Y', $recordValues['date_start']) ) );

            // Registros posteriores

            $SQL_query_end_date = db_query('SELECT UNIX_TIMESTAMP(fecha_inicio) AS fecha_inicio FROM profesores_grupo WHERE id_grupo = \'' . $groupValues['id'] . '\' AND id_profesor = \'' . $recordValues['teacher_id'] . '\' AND fecha_inicio IS NOT NULL AND fecha_inicio > "' . date('Y-m-d', $recordValues['date_end']) . '" ORDER BY fecha_inicio ASC LIMIT 1');

            if ( db_num_rows($SQL_query_end_date) ) {
              $endDate     = db_fetch_array($SQL_query_end_date);
              $delDateStop = date('Y-m-d', mktime(0, 0, 0, date('m', $endDate['fecha_inicio']), -1, date('Y', $endDate['fecha_inicio']) ));
            }
            else $delDateStop = ( date('j', $recordValues['date_end'] ) > 1 ? date('Y-m-d', $recordValues['date_end']) : date('Y-m-', $recordValues['date_end']) . '02');
	    
	    db_query('DELETE FROM masterlist WHERE id_grupo = \'' . $groupValues['id'] . '\' AND id_profesor = \'' . $recordValues['teacher_id'] . '\' AND ( fecha > "' . $delDateStart . '" AND fecha < "' . $delDateStop . '" )');

	    if ( $recordValues['teacher_status'] == '2' ) $this->deleteRecord($recordValues['teacher_id']);
          }
          else $error .= 2;
        }
        return $error;
      }
      else return $error;
    }

    //-----------------------------------------------------------------------------------
    // manageTeacherToGroup

    function manageTeacherToGroup(&$params, $groupValues, &$record) {

      // Chequeo y tratamiento de los valores recibidos

      $error  = '';
      $error .= ( !is_array($groupValues) || count($groupValues) == 0 ? 1 : 0 );

      if ( empty($record) && empty($params['search_teacher_id']) ) $error .= 6;
      elseif ( !empty($record) && !$this->checkTeacherToGroup( $record, $groupValues['id']) ) $error .= 1;
      elseif ( !empty($params['search_teacher_id']) && !$this->checkRecord($params['search_teacher_id']) ) $error .= 4;
      else $error .= 0;

      if ( !empty($params['search_teacher_id']) && !empty($record) )     $action = 'replace';
      elseif ( empty($params['search_teacher_id'])  && !empty($record) ) $action = 'update';
      elseif ( !empty($params['search_teacher_id']) && empty($record) )  $action = 'insert';

      $error .= ( !$action ? 1 : 0 );

      // Fechas

      if ( ereg("^([0-9]{2})\/([0-9]{2})\/([0-9]{4})$", $params['teacher_date_start'], $date_start ) && checkdate($date_start[2], $date_start[1], $date_start[3]) ) $teacher_date_start = mktime(0,0,0,$date_start[2], $date_start[1], $date_start[3]);
      if ( !empty($params['teacher_date_end']) ) {
        if ( ereg("^([0-9]{2})\/([0-9]{2})\/([0-9]{4})$", $params['teacher_date_end'], $date_end ) && checkdate($date_end[2], $date_end[1], $date_end[3]) ) $teacher_date_end = mktime(0,0,0,$date_end[2], $date_end[1], $date_end[3]);
      }
      else $teacher_date_end = $groupValues['date_end'];

//      $last_day = mktime(0, 0, 0, date('m',$teacher_date_start), ( date('d', $teacher_date_start) - 1 ), date('Y', $teacher_date_start) );

      if ( empty($teacher_date_start) )   $error .= 1;
      elseif ( empty($teacher_date_end) ) $error .= 2;
      elseif ( ( $teacher_date_start < $groupValues['date_start'] ) || ( $teacher_date_start > $groupValues['date_end'] ) || ( $teacher_date_start > $teacher_date_end ) ) $error .= 3;
      elseif ( ( $teacher_date_end < $groupValues['date_start'] ) || ( $teacher_date_end > $groupValues['date_end'] ) || ( $teacher_date_end < $teacher_date_start ) ) $error .= 4;
      else $error .= 0;

      // Importe

      if ( !empty($params['teacher_cost']) ) {
        $params['teacher_cost'] = ereg_replace('[^0-9,\.]' , '', $params['teacher_cost']);
        $params['teacher_cost'] = ereg_replace(',' , '.', $params['teacher_cost']);

        $error .= ( !is_numeric($params['teacher_cost']) ? 1 : 0 );
      }
      else $error .= 0;

      // Preparacion de los datos

      if ( ereg("^0+$", $error) ) {

        if ( $action != 'insert' ) {
          $recordValues = $this->getTeacherToGroupValues($record);

          if ( !empty($recordValues['teacher_replace_to_id']) ) $recordReplaceTo = $this->getTeacherToGroupValues($recordValues['teacher_replace_to_id']);

          $recordReplaceFor  = $this->getTeacherReplaceToValues($record, $groupValues['id']);
          if ( !is_array($recordReplaceFor) ) unset($recordReplaceFor);
        }

        if ( $action == 'insert' ) {

          // INSERTAR PROFESOR
          //   * Se comprueba que no haya ningun profesor asignado al grupo. Si se da es-
          //     te caso, al profesor se le asigna todo el horario del grupo.
          //   * Se comprueba que no se repita el profesor en el grupo

          $SQL_query  = mysql_query('SELECT count(id) AS total FROM profesores_grupo WHERE id_grupo = \'' . $groupValues['id'] . '\'');
          $SQL_result = mysql_fetch_assoc($SQL_query);

          if ( $this->checkTeacherNoGroup( $params['search_teacher_id'], $groupValues['id'], $teacher_date_start, $teacher_date_end) ) {
            if ( @db_query('INSERT INTO profesores_grupo (id_profesor, id_grupo, importe, observaciones, fecha_inicio, fecha_fin) VALUES (\'' . $params['search_teacher_id'] . '\', \'' . $groupValues['id'] . '\', \'' . $params['teacher_cost'] . '\', \'' . addslashes($params['teacher_comments']) . '\', ' . ( $teacher_date_start > $groupValues['date_start'] ? '\'' . date('Y-m-d', $teacher_date_start) . '\'' : 'NULL' ) . ', ' . ( $teacher_date_end < $groupValues['date_end'] ? '\'' . date('Y-m-d', $teacher_date_end) . '\'' : 'NULL' ) . ')' ) ) {

              $error  .= 0;
              $record  = db_insert_id();

              if ( $SQL_result['total'] == 0 ) {

                // Horario profesor

                $teacherTimetable = array();
                $SQL_query = mysql_query('SELECT * FROM grupo_horario WHERE id = \'' . $groupValues['id'] . '\'');

                if ( mysql_num_rows($SQL_query) ) {

                  while ( $SQL_record = mysql_fetch_assoc($SQL_query) ) {
                    if ( $SQL_record['dia_inicio'] == $SQL_record['dia_fin'] )
                      $teacherTimetable[] = array( 'dia'         => $SQL_record['dia_inicio'],
                                                   'hora_inicio' => $SQL_record['hora_inicio'],
                                                   'hora_fin'    => $SQL_record['hora_fin']
                                                 );
                    else {
                      for ( $i=$SQL_record['dia_inicio']; $i<=$SQL_record['dia_fin']; $i++  )
                        $teacherTimetable[] = array( 'dia'         => $i,
                                                     'hora_inicio' => $SQL_record['hora_inicio'],
                                                     'hora_fin'    => $SQL_record['hora_fin']
                                                   );
                    }
                  }
                  reset($teacherTimetable);

                  while ( list($id) = each($teacherTimetable) ) {

                    if ( mysql_query('INSERT INTO grupos_horario_intervalos (dia, hora_inicio, hora_fin) VALUES (\'' . $teacherTimetable[$id]['dia'] . '\', \'' . $teacherTimetable[$id]['hora_inicio'] . '\', \'' . $teacherTimetable[$id]['hora_fin'] . '\')') ) {

                      $interval = mysql_insert_id();
                      mysql_query('INSERT INTO  profesor_horario_grupo (id_profesor_grupo, id_intervalo_horario) VALUES (\'' . $record . '\', \'' . $interval . '\')');
                    }
                  }
                }
              }
            }
            else $error .= 1;
          }
          else $error .= 9;
        }
        elseif ( $action == 'update' ) {

          // ACTUALIZAR DATOS PROFESOR

          if ( $this->checkTeacherNoGroup( $recordValues['teacher_id'], $groupValues['id'], $teacher_date_start, $teacher_date_end, $record) ) {

            if ( $recordReplaceTo) {
              if     ( $teacher_date_start < $recordReplaceTo['date_start'] ) $error .= 6;
              elseif ( $teacher_date_end   > $recordReplaceTo['date_end']   ) $error .= 7;
              elseif ( ( $teacher_date_start == $recordReplaceTo['date_start'] ) && ( $teacher_date_end == $recordReplaceTo['date_end'] ) ) $error .= 5;
            }
            elseif ( $recordReplaceFor ) {

              $ids = array();

              foreach ( $recordReplaceFor AS $teachers ) $ids[] = $teachers['id'];

              $SQL_query = db_query('SELECT count(*) AS total FROM profesores_grupo pg WHERE id IN (' . implode(',', $ids) . ') AND ( fecha_inicio < \'' . date('Y-m-d', $teacher_date_start) . '\' OR fecha_fin > \'' . date('Y-m-d', $teacher_date_end) . '\' OR ( fecha_inicio = \'' . date('Y-m-d', $teacher_date_start) . '\' AND fecha_fin = \'' . date('Y-m-d', $teacher_date_end) . '\' ) )');

              $SQL_record = db_fetch_array($SQL_query);
              if ( $SQL_record['total'] > '0' ) $error .= 8;
            }

            if ( ereg("^0+$", $error) ) {
              if ( !@db_query('UPDATE profesores_grupo SET fecha_inicio = ' . ( !$recordReplaceTo && ( $groupValues['date_start'] == $teacher_date_start ) ? 'NULL' : '\'' . date('Y-m-d', $teacher_date_start) . '\'' ) . ', fecha_fin = ' . ( !$recordReplaceTo && $groupValues['date_end'] == $teacher_date_end ? 'NULL' : '\'' . date('Y-m-d', $teacher_date_end) . '\'' ) . ', importe = \'' . $params['teacher_cost'] . '\', observaciones = \'' . addslashes($params['teacher_comments']) . '\' WHERE id = \'' . $record . '\'') ) $error .= 1;
            }
          }
          else $error .= 9;
        }
        elseif ( $action == 'replace' ) {

          // REEMPLAZAR PROFESOR

          if ( $recordValues['teacher_id'] == $params['search_teacher_id'] ) $error .= 4;
          elseif ( ( $teacher_date_start == $recordValues['date_start'] ) && ( $teacher_date_end == $recordValues['date_end'] ) ) $error .= 5;
          elseif ( $teacher_date_start > $recordValues['date_end'] ) $error .= 2;
          elseif ( $teacher_date_end > $recordValues['date_end'] ) $error .= 3;
          elseif ( $this->checkTeacherNoGroup( $params['search_teacher_id'], $groupValues['id'], $teacher_date_start, $teacher_date_end) ) {

            if ( @db_query('INSERT INTO profesores_grupo (id_profesor, id_grupo, importe, observaciones, fecha_inicio, fecha_fin, id_sustitucion) VALUES (\'' . $params['search_teacher_id'] . '\', \'' . $groupValues['id'] . '\', \'' . $params['teacher_cost'] . '\', \'' . addslashes($params['teacher_comments']) . '\', ' . '\'' . date('Y-m-d', $teacher_date_start) . '\', ' . '\'' . date('Y-m-d', $teacher_date_end) . '\', \'' . $record . '\')') ) {

              $newReg = db_insert_id();

              $SQL_query = db_query('SELECT id_intervalo_horario FROM profesor_horario_grupo WHERE id_profesor_grupo = \'' . $record . '\'');

              if ( db_num_rows($SQL_query) > 0 ) {
                while ( $SQL_values = db_fetch_array($SQL_query) ) @db_query('INSERT INTO profesor_horario_grupo (id_profesor_grupo, id_intervalo_horario ) VALUES (\'' . $newReg . '\', \'' . $SQL_values['id_intervalo_horario'] . '\')');
              }
              $record = $newReg;
            }
            else $error .= 1;
          }
          else $error .= 9;
        }
        return $error;
      }
      else return $error;
    }

    //-----------------------------------------------------------------------------------
    // getTeachersToGroupValues

    function getTeachersToGroupValues( $record = '', $group ) {

      $teachersValues = array();
	    
      if ( $record && $this->checkTeacherToGroup($record, $group) ) $teachersValues[$record] = $this->getTeacherToGroupValues($record);
      else {
        $SQL_query   = db_query('SELECT pg.id FROM profesores_grupo pg WHERE pg.id_grupo = \'' . $group . '\'');
        if ( db_num_rows($SQL_query) > 0 ) {
          while ( $SQL_values = db_fetch_array($SQL_query) ) $teachersValues[$SQL_values['id']] = $this->getTeacherToGroupValues($SQL_values['id']);
        }
      }
      return $teachersValues;
    }

    //-----------------------------------------------------------------------------------
    // getTeachersTimeTables

    function getTeachersTimeTables( $record = '', $group, &$DATA_SOURCE ) {

      $teacherTimeTables = $DATA_SOURCE;

      $SQL_where  = ' WHERE 1';
      $SQL_where .= ( $record && $this->checkTeacherToGroup($record, $group) ? ' AND phg.id_profesor_grupo = \'' . $record . '\'' : ' AND pg.id_grupo = \'' . $group . '\'');

      $SQL_query  = db_query('SELECT pg.id AS id_intervalo, ghi.id, ghi.dia, ghi.hora_inicio, ghi.hora_fin FROM profesor_horario_grupo phg LEFT JOIN grupos_horario_intervalos ghi ON phg.id_intervalo_horario = ghi.id LEFT JOIN profesores_grupo pg ON pg.id = phg.id_profesor_grupo' . $SQL_where . ' ORDER BY ghi.id, ghi.dia');

      if ( db_num_rows($SQL_query) > 0 ) {

        while ( $SQL_values = db_fetch_array($SQL_query) ) {
          $DATA_SOURCE[$SQL_values['id_intervalo']]['timetable'][$SQL_values['dia'] . '-' . $SQL_values['hora_inicio'] . '-' . $SQL_values['hora_fin']] = $SQL_values['id'];
        }
      }
    }

    //-----------------------------------------------------------------------------------
    // manageTeachersTimeTables

    function manageTeachersTimeTables( $params, $record, $group, $hours, $groupTimeTable ) {

      $error  = '';

      /* Comprobacion datos recibidos */

      if ( is_array($params['teacher']) ) {

        while ( list($id) = each($params['teacher']) ) {

          if ( $this->checkTeacherToGroup( $id, $group) ) {

            while ( list($time) = each($params['teacher'][$id]['timetable']) ) {
              list( $day, $start_hour, $end_hour ) = explode('-', $params['teacher'][$id]['timetable'][$time]);
              if ( $day < 1 || $day > 7 || !isset($hours[$start_hour]) || !isset($hours[$end_hour]) ) { break(2); $error .= 1; }
            }
            reset($params['teacher'][$id]['timetable']);
          }
          else { $error .= 2; break; }
	}
        reset($params['teacher']);
        $error .= 0;
      }
      else $error .= 0;

      if ( ereg("^0+$", $error) ) {

        $error .= 0;
        $this->getTeachersTimeTables( $record, $group, $teacherTimeTables );
        $definedIntervals  = array();
        $deleteIntervals   = array();
	
        /* Intervalos ya definidos en la tabla GRUPOS_HORARIO_INTERVALOS */

        $SQL_query = db_query('SELECT DISTINCT(ghi.id), concat(ghi.dia, \'-\', ghi.hora_inicio, \'-\', ghi.hora_fin) AS intervalo FROM grupos_horario_intervalos ghi LEFT JOIN profesor_horario_grupo phg ON ghi.id = phg.id_intervalo_horario LEFT JOIN profesores_grupo pg ON phg.id_profesor_grupo = pg.id WHERE pg.id_grupo = \'' . $group . '\'');

        if ( db_num_rows($SQL_query) > 0 ) {
          while ( $SQL_values = db_fetch_array($SQL_query) ) $definedIntervals[$SQL_values['intervalo']] = $SQL_values['id'];
        }

        if ( isset($params['teacher'][$record]) ) {

          /* Listados de registros a INSERTAR y BORRAR  */

          if ( !is_array($teacherTimeTables[$record]['timetable']) ) $teacherTimeTables[$record]['timetable'] = array();

          $removeTimeTableValues = array_diff(array_keys($teacherTimeTables[$record]['timetable']), $params['teacher'][$record]['timetable']);
          $insertTimeTableValues = array_diff($params['teacher'][$record]['timetable'], array_keys($teacherTimeTables[$record]['timetable']));

          /* BORRAR intervalos a periodos de profesores  */

          while ( list($key) = each($removeTimeTableValues) ) { 
            if ( !in_array($teacherTimeTables[$record]['timetable'][$key], $deleteIntervals) ) $deleteIntervals[] = $teacherTimeTables[$record]['timetable'][$removeTimeTableValues[$key]];
            @db_query('DELETE FROM profesor_horario_grupo WHERE id_profesor_grupo = \'' . $record . '\' AND id_intervalo_horario = \'' . $teacherTimeTables[$record]['timetable'][$removeTimeTableValues[$key]] . '\'');
          }

          /* ASIGNAR intervalos a periodos de profesores */
	  
          while ( list($id) = each($insertTimeTableValues) ) {

            if ( !$definedIntervals[$insertTimeTableValues[$id]] ) {

              list($dia, $hora_inicio, $hora_fin) = explode('-', $insertTimeTableValues[$id]);
              @db_query('INSERT INTO grupos_horario_intervalos (dia, hora_inicio, hora_fin) VALUES (\'' . $dia . '\', \'' . $hora_inicio . '\', \'' . $hora_fin . '\')');
              $definedIntervals[$insertTimeTableValues[$id]] = db_insert_id();
            }

            @db_query('INSERT INTO profesor_horario_grupo (id_profesor_grupo, id_intervalo_horario) VALUES (\'' . $record . '\', \'' . $definedIntervals[$insertTimeTableValues[$id]] . '\')');
          }
        }
        else {

          $removeTimeTableValues = array_values($teacherTimeTables[$record]['timetable']);
          $deleteIntervals       = array_values($teacherTimeTables[$record]['timetable']);

          /* BORRAR intervalos a periodos de profesores  */

          @db_query('DELETE FROM profesor_horario_grupo WHERE id_profesor_grupo = \'' . $record . '\' AND id_intervalo_horario IN (' . implode(',', $removeTimeTableValues) . ')');
        }

	if ( count($deleteIntervals) > 0 ) {
          $activeIntervals = $this->getUniqueTimeIntervals($deleteIntervals);
          $delIntervalList = array_diff($deleteIntervals, $activeIntervals);

          @db_query('DELETE FROM grupos_horario_intervalos WHERE id IN (' . implode(',', $delIntervalList) . ')');
        }
      }
      return $error;
    }

    function getUniqueTimeIntervals( $times ) {

      $timeIntervals = array();

      if ( is_array($times) ) {
        $SQL_query = db_query('SELECT id_intervalo_horario AS id, count( id_intervalo_horario ) AS total FROM profesor_horario_grupo WHERE id_intervalo_horario IN (' . implode(',', $times) . ') GROUP BY id_intervalo_horario;
 ');

        if ( db_num_rows($SQL_query) ) { while ( $SQL_values = db_fetch_array($SQL_query) ) $timeIntervals[] = $SQL_values['id']; }
      }
      return $timeIntervals;
    }

    //-----------------------------------------------------------------------------------
    // getTeacherTimeTable

    function getTeacherTimeTable( $record, $group ) {

      $teacherTimeTable = array();

      if ( $this->checkTeacherToGroup($record, $group) ) {

        $SQL_query  = db_query('SELECT ghi.dia, TIME_FORMAT(rf1.hora, "%H:%i") AS hora_inicio, TIME_FORMAT(rf2.hora, "%H:%i") AS hora_fin FROM profesor_horario_grupo phg LEFT JOIN grupos_horario_intervalos ghi ON phg.id_intervalo_horario = ghi.id LEFT JOIN profesores_grupo pg ON pg.id = phg.id_profesor_grupo LEFT JOIN ref_horas rf1 ON ghi.hora_inicio = rf1.id LEFT JOIN ref_horas rf2 ON ghi.hora_fin = rf2.id WHERE phg.id_profesor_grupo = \'' . $record . '\' ORDER BY ghi.dia');

        if ( db_num_rows($SQL_query) > 0 ) {

          while ( $SQL_values = db_fetch_array($SQL_query) ) {
            $teacherTimeTable[] = $this->lng['select_day_' . $SQL_values['dia']] . ' ' . $SQL_values['hora_inicio'] . ' - ' . $SQL_values['hora_fin'];
          }
        }
      }

      return $teacherTimeTable;
    }

    //-----------------------------------------------------------------------------------
    // getTeacherGroupsByDate

    function getTeacherGroupsByDate( $teacher, $start_time, $end_time ) {

      $teacherGroups = array();
      $temp_database = $this->getRealIntervals($teacher, $start_time, $end_time);
      $i = '1';

      if ( $temp_database ) {

        $SQL_teacher_date_start = date('Y-m-d', $start_time);
        $SQL_teacher_date_end   = date('Y-m-d', $end_time);

        $SQL_query = db_query('SELECT pg.id, g.nombre, pg.id_grupo, em.nombre as nombre_empresa, tpg.fecha_inicio, tpg.fecha_fin FROM profesores_grupo pg LEFT JOIN ' . $temp_database . ' tpg ON pg.id = tpg.id LEFT JOIN grupos g ON pg.id_grupo = g.id LEFT JOIN empresas em on g.id_empresa = em.id WHERE pg.id_profesor = \'' . $teacher . '\' AND ( ( tpg.fecha_inicio <= "' . $SQL_teacher_date_end . '" AND tpg.fecha_fin >= "' . $SQL_teacher_date_end . '" ) OR ( tpg.fecha_inicio <= "' . $SQL_teacher_date_start . '" AND tpg.fecha_fin >= "' . $SQL_teacher_date_start . '" ) OR ( tpg.fecha_inicio >= "' . $SQL_teacher_date_start . '" AND tpg.fecha_fin <= "' . $SQL_teacher_date_end . '" ) )');

        if ( db_num_rows($SQL_query) ) {
          while ( $SQL_record = db_fetch_array($SQL_query) ) {

            $teacherGroups[$i] = array ( 'id'        => $SQL_record['id'],
                                         'name'      => $SQL_record['nombre_empresa'] . "\n" . $SQL_record['nombre'],
                                         'company'   => $SQL_record['nombre_empresa'],
                                         'startdate' => mktime(0, 0, 0, substr($SQL_record['fecha_inicio'], 5, 2), substr($SQL_record['fecha_inicio'], 8), substr($SQL_record['fecha_inicio'], 0, 4) ),
                                         'enddate'   => mktime(0, 0, 0, substr($SQL_record['fecha_fin'], 5, 2), substr($SQL_record['fecha_fin'], 8), substr($SQL_record['fecha_fin'], 0, 4) ),
                                         'timetable' => $this->getTeacherTimeTableById($SQL_record['id'])
                                       );
            $i++;
          }
        }
      }
      return $teacherGroups;
    }

    //-----------------------------------------------------------------------------------
    // getRealIntervals
    // Obtiene los periodos de tiempo en los que un profesor ha estado asignado a un grupo

    function getRealIntervals( $teacher, $date_start, $date_end, $group = '' ) {

      $database = 'profesores_grupo_' . time();

      if ( db_query('CREATE TEMPORARY TABLE ' . $database . ' ( id int(11), fecha_inicio date default NULL, fecha_fin date default NULL, KEY id (id), KEY fecha_fin (fecha_fin), KEY fecha_inicio (fecha_inicio) )' ) ) {

        $SQL_query = db_query('SELECT pg.id, 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(e.fecha_fin) ) ) AS fecha_fin FROM profesores_grupo pg LEFT JOIN grupos g ON pg.id_grupo = g.id LEFT JOIN ejercicios_lectivos e ON g.id_periodo = e.id WHERE id_profesor = \'' . $teacher . '\'' . ( $group ? ' AND pg.id_grupo = \'' . $group . '\'' : '' ) . ' HAVING ( ( fecha_inicio <= "' . $date_end . '" AND fecha_fin >= "' . $date_end . '" ) OR ( fecha_inicio <= "' . $date_start . '" AND fecha_fin >= "' . $date_start . '" ) OR ( fecha_inicio >= "' . $date_start . '" AND fecha_fin <= "' . $date_end . '" ) ) ORDER BY fecha_inicio' );

        if ( db_num_rows($SQL_query) ) {

          while ( $SQL_record = db_fetch_array($SQL_query) ) {

            $intervals        = array();
            $teacherIntervals = array();

            $SQL_replace_query = db_query('SELECT IF (fecha_inicio, UNIX_TIMESTAMP(fecha_inicio), "' . $SQL_record['fecha_inicio'] . '") AS fecha_inicio, IF (fecha_fin, UNIX_TIMESTAMP(fecha_fin), "' . $SQL_record['fecha_fin'] . '") AS fecha_fin FROM profesores_grupo WHERE id_sustitucion = \'' . $SQL_record['id'] . '\' HAVING ( ( fecha_inicio <= "' . $date_end . '" AND fecha_fin >= "' . $date_end . '" ) OR ( fecha_inicio <= "' . $date_start . '" AND fecha_fin >= "' . $date_start . '" ) OR ( fecha_inicio >= "' . $date_start . '" AND fecha_fin <= "' . $date_end . '" ) ) ORDER BY fecha_inicio');

            if ( db_num_rows($SQL_replace_query) ) {

              while ( $SQL_replace_record = db_fetch_array($SQL_replace_query) )
                $intervals[] = array ( 'start' => $SQL_replace_record['fecha_inicio'],
                                       'end'   => $SQL_replace_record['fecha_fin']     );

              if ( count($intervals) ) {

                $i = count($teacherIntervals);

                foreach( $intervals AS $interval ) {

                  if ( ( $interval['start'] == $SQL_record['fecha_inicio'] ) && ( $interval['end'] == $SQL_record['fecha_fin'] ) ) continue;
                  if ( empty($teacherIntervals[$i]['startdate']) ) $teacherIntervals[$i] = array( 'startdate' => $SQL_record['fecha_inicio'] );

                  if ( ( $interval['start'] > $SQL_record['fecha_inicio'] ) && ( $interval['end'] < $SQL_record['fecha_fin'] ) ) {
                    $teacherIntervals[$i]['enddate'] = mktime(0,0,0,date('n', $interval['start']), ( date('j', $interval['start']) - 1 ), date('Y', $interval['start']));

                    $teacherIntervals[$i + 1]['startdate']   = mktime(0,0,0,date('n', $interval['end']), ( date('j', $interval['end']) + 1 ), date('Y', $interval['end']));
                    $i++;
                  }
                  elseif ( $interval['start'] == $SQL_record['fecha_inicio'] ) {
                    $teacherIntervals[$i]['startdate'] = mktime(0,0,0,date('n', $interval['end']), ( date('j', $interval['end']) + 1 ), date('Y', $interval['end']));
                  }
                  elseif ( $interval['end'] == $SQL_record['fecha_fin'] ) {
                    $teacherIntervals[$i]['enddate'] = mktime(0,0,0,date('n', $interval['start']), ( date('j', $interval['start']) - 1 ), date('Y', $interval['start']));
                  }

                  if ( empty($teacherIntervals[$i]['enddate']) ) $teacherIntervals[$i]['enddate'] = $SQL_record['fecha_fin'];
                }
              }
            }
            else
              $teacherIntervals[] = array( 'startdate' => $SQL_record['fecha_inicio'],
                                           'enddate'   => $SQL_record['fecha_fin']     );

            reset($teacherIntervals);

            foreach ( $teacherIntervals AS $interval  ) db_query('INSERT INTO ' . $database . ' VALUES (\'' . $SQL_record['id'] . '\', \'' . date('Y-m-d', $interval['startdate']) . '\', \'' . date('Y-m-d', $interval['enddate']) . '\')' );
          }
        }
        return $database;
      }
      else return false;
    }

    //-----------------------------------------------------------------------------------
    // getTeacherTimeTableById

    function getTeacherTimeTableById( $record ) {

      $timeTable = array();

      $SQL_query = db_query('SELECT ghi.dia, ghi.hora_inicio, ghi.hora_fin FROM profesor_horario_grupo phg LEFT JOIN grupos_horario_intervalos ghi ON phg.id_intervalo_horario = ghi.id WHERE phg.id_profesor_grupo = \'' . $record . '\'');

      if ( db_num_rows($SQL_query) ) {
        while ( $SQL_record = db_fetch_array($SQL_query) ) $timeTable[$SQL_record['dia']][] = array( 'start' => $SQL_record['hora_inicio'], 'end' => $SQL_record['hora_fin'] );
      }

      return $timeTable;
    }

    //-----------------------------------------------------------------------------------
    // manageTeacherCompany

    function manageTeacherCompany( $params, $record ) {

      $error  = '';
      $error .= ( $params['search_company_id'] ? 0 : 1 );

      if ( ereg("^0+$", $error) ) {

        if ( @db_query('INSERT INTO profesor_empresa ( id_profesor, id_campo ) VALUES (\'' . $record . '\', \'' . $params['search_company_id'] . '\')') ) $error .= 0;
        else $error .= 1;
      }

      return $error;
    }

    //-----------------------------------------------------------------------------------
    // deleteTeacherCompany

    function deleteTeacherCompany( $group, $company ) {

      @db_query('DELETE FROM profesor_empresa WHERE id_profesor = \'' . $record . '\' AND id_campo = \'' . $company . '\'');
    }

    function getGroupTeacherByDate( $group, $dates ) {

      $teachers  = array();
      $SQL_where = '';

      if ( is_array($dates) ) {
        foreach ( $dates AS $date ) {
          if ( @checkdate( substr($date,4,2), 1, substr($date,0,4) ) ) {
            $SQL_where .= ( !empty($SQL_where) ? ' OR ' : '' ) . '( fecha_inicio <= \'' . $date . '\' AND fecha_fin >= \'' . $date . '\' )';
          }
        }
      }
      else {
        if ( @checkdate( substr($date,4,2), 1, substr($date,0,4) ) ) $SQL_where = 'fecha_inicio <= \'' . $date . '\' AND fecha_fin >= \'' . $date . '\'';
      }

      if ( !empty($SQL_where) ) {

        $SQL_query = db_query('SELECT DISTINCT(pg.id_profesor), CONCAT( p.nombre, \' \', p.apellidos) AS nombre, IF(pg.fecha_inicio, DATE_FORMAT(pg.fecha_inicio, "%Y%m"), DATE_FORMAT(g.fecha_inicio, "%Y%m")) AS fecha_inicio, 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 fecha_fin 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 WHERE pg.id_grupo = \'' . $group . '\' HAVING ( ' . $SQL_where . ' ) ORDER BY p.nombre');

        if ( db_num_rows($SQL_query) ) {
          while ( $SQL_values = db_fetch_array($SQL_query) ) $teachers[] = $SQL_values['nombre'];
        }
      }

      return $teachers;
    }
  }
?>