<?php
require_once 'Zend/Controller/Action.php' ;

class AdministratorController extends Zend_Controller_Action {

  private $auth;
  public function init(){
    $this->config = new Zend_Config_Ini($_SERVER['DOCUMENT_ROOT'] .'config.ini', 'production', $config);
    $baseurl = $this->view->baseUrl=$this->_request->getBaseUrl();
    $this->view->addHelperPath($_SERVER['DOCUMENT_ROOT'] .'application/views/helpers/admin');
    $this->auth = Zend_Auth::getInstance();
    $this->view->identity = $this->auth->getIdentity();
    $this->db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
  }

  public function indexAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
  }

  /* Lehrer */

  public function lehrerAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    $sql = "SELECT COUNT(lehrer.id) as countLehrer,
            (SELECT COUNT(lehrer.id) FROM lehrer WHERE lehrer.status='inaktiv') as countLehrerInaktiv, 
            (SELECT COUNT(lehrer.id) FROM lehrer WHERE lehrer.status='aktiv' AND lehrer.freigeschaltet='yes') as countLehrerAktiv, 
            (SELECT COUNT(lehrer.id) FROM lehrer WHERE lehrer.freigeschaltet='no') as countLehrerFrei,
            (SELECT COUNT(lehrer_daten.id) FROM lehrer_daten WHERE lehrer_daten.agb_akzeptiert='nein') as countLehrerAGB FROM lehrer";
    $countMailbox = "SELECT COUNT(*) as allMsg,
		                 (SELECT COUNT(*) FROM support_box WHERE support_box.von='lehrer' 
		                 AND support_box.gelesen='nein') as newMsg 
		                 FROM support_box WHERE support_box.von='lehrer'
		                 ORDER BY support_box.erstellt DESC";
    $this->view->countmsg = $db->fetchAll($countMailbox);
    $this->view->countresult = $db->fetchAll($sql);
  }

  public function lehrerGetNamesAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    $sql = "SELECT id,username FROM lehrer WHERE freigeschaltet='yes' AND status='aktiv' AND username LIKE '%".addslashes($_GET["q"])."%' LIMIT 10";
    $result = $db->fetchAll($sql);
    foreach ($result as $value) {
      echo $value["username"]."<br>";
    }
  }

  public function lehrerGetOrtAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    $sql = "SELECT PLZ FROM orte WHERE PLZ LIKE '%".addslashes($_GET["q"])."%' LIMIT 25";
    foreach ($result as $value) {
      echo $value["PLZ"]."<br>";
    }
  }

  public function lehrerNeuregistrierungAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "SELECT id,name_komplett,username, email, ort, land, telnr FROM lehrer WHERE freigeschaltet='no' LIMIT 20";
    $this->view->freiresult = $db->fetchAll($sql);
    echo $this->view->render('administrator/lehrer/neureg.phtml');
  }

  public function lehrerfreischaltenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    if(!is_numeric($this->_getParam("id"))) {
      $this->_redirect("/administrator/lehrerneuregistrieung");
      die;
    }

    $update = array(
    'status' => 'aktiv',
    'freigeschaltet' => 'yes'
    );
    $updatewhere[] = "id = '".$this->_getParam("id")."'";
    $db->update('lehrer', $update, $updatewhere);
    $db->delete('register_secret', 'userid = '.$this->_getParam("id").'');
    $this->_redirect("/administrator/lehreruebersicht");
  }

  public function lehrerUebersichtAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "select *,
              (SELECT COUNT(lehrer_has_schueler.lehrer_id) FROM lehrer_has_schueler WHERE lehrer_has_schueler.lehrer_id=lehrer.id) AS countSchueler, 
              (SELECT COUNT(lehrer_has_schueler.schueler_id) FROM lehrer_has_schueler left join schueler on schueler.id=lehrer_has_schueler.schueler_id WHERE lehrer_has_schueler.lehrer_id=lehrer.id AND schueler.status='inaktiv') as countInaktivSchueler  from lehrer
              left join lehrer_has_lehrer_daten on lehrer.id=lehrer_has_lehrer_daten.lehrer_id
              left join lehrer_daten on lehrer_daten.id=lehrer_has_lehrer_daten.lehrer_daten_id
              left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id
              left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id
              left join lehrer_has_lehrer_faecher on lehrer.id=lehrer_has_lehrer_faecher.lehrer_id
              left join lehrer_faecher on lehrer_faecher.id=lehrer_has_lehrer_faecher.lehrer_faecher_id
              WHERE lehrer.status='aktiv' AND lehrer.freigeschaltet='yes' ORDER BY lehrer.id ASC LIMIT 10";

    $this->view->result = $db->fetchAll($sql);
    echo $this->view->render('administrator/lehrer/lehreruebersicht.phtml');

  }

  public function lehrerInaktivUebersichtAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "select *,
              (SELECT COUNT(lehrer_has_schueler.lehrer_id) FROM lehrer_has_schueler WHERE lehrer_has_schueler.lehrer_id=lehrer.id) AS countSchueler, 
              (SELECT COUNT(lehrer_has_schueler.schueler_id) FROM lehrer_has_schueler left join schueler on schueler.id=lehrer_has_schueler.schueler_id WHERE lehrer_has_schueler.lehrer_id=lehrer.id AND schueler.status='inaktiv') as countInaktivSchueler  from lehrer
              left join lehrer_has_lehrer_daten on lehrer.id=lehrer_has_lehrer_daten.lehrer_id
              left join lehrer_daten on lehrer_daten.id=lehrer_has_lehrer_daten.lehrer_daten_id
              left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id
              left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id
              left join lehrer_has_lehrer_faecher on lehrer.id=lehrer_has_lehrer_faecher.lehrer_id
              left join lehrer_faecher on lehrer_faecher.id=lehrer_has_lehrer_faecher.lehrer_faecher_id
              WHERE lehrer.status='inaktiv' AND lehrer.freigeschaltet='yes' ORDER BY lehrer.id ASC LIMIT 10";

    $this->view->result = $db->fetchAll($sql);
    echo $this->view->render('administrator/lehrer/lehrerinaktivuebersicht.phtml');

  }

  public function lehrerGeloeschtAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "select *,
              (SELECT COUNT(lehrer_has_schueler.lehrer_id) FROM lehrer_has_schueler WHERE lehrer_has_schueler.lehrer_id=lehrer.id) AS countSchueler, 
              (SELECT COUNT(lehrer_has_schueler.schueler_id) FROM lehrer_has_schueler left join schueler on schueler.id=lehrer_has_schueler.schueler_id WHERE lehrer_has_schueler.lehrer_id=lehrer.id AND schueler.status='inaktiv') as countInaktivSchueler  from lehrer
              left join lehrer_has_lehrer_daten on lehrer.id=lehrer_has_lehrer_daten.lehrer_id
              left join lehrer_daten on lehrer_daten.id=lehrer_has_lehrer_daten.lehrer_daten_id
              left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id
              left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id
              left join lehrer_has_lehrer_faecher on lehrer.id=lehrer_has_lehrer_faecher.lehrer_id
              left join lehrer_faecher on lehrer_faecher.id=lehrer_has_lehrer_faecher.lehrer_faecher_id
              WHERE lehrer.status='geloescht' AND lehrer.freigeschaltet='yes' ORDER BY lehrer.id ASC LIMIT 10";

    $this->view->result = $db->fetchAll($sql);
    echo $this->view->render('administrator/lehrer/lehrergeloescht.phtml');

  }

  public function lehrerInaktivSetzenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    if($this->_hasParam("id") && $this->_hasParam("sure") && $this->_getParam("sure") == "yes" && is_numeric($this->_getParam("id")))
    {
      $data = array(
      'status' => 'inaktiv'
      );
      $where[] = "id = '".$this->_getParam("id")."'";
      $db->update('lehrer', $data, $where);
      $this->_redirect("administrator/lehrerinaktivuebersicht");
    } else if ($this->_hasParam("id") && is_numeric($this->_getParam("id")) && $this->_getParam("sure") != "yes"){
      $sql = "select * FROM lehrer
              WHERE lehrer.status='aktiv' AND lehrer.freigeschaltet='yes' AND lehrer.id='".$this->_getParam("id")
      . "' LIMIT 1";
      $this->view->result = $db->fetchRow($sql);
      echo $this->view->render('administrator/lehrer/lehrerinaktivsetzen.phtml');
    } else {
      $this->_redirect("/administrator/lehreruebersicht");
    }
  }

  public function lehrerAktivSetzenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    if($this->_hasParam("id") && $this->_hasParam("sure") && $this->_getParam("sure") == "yes" && is_numeric($this->_getParam("id")))
    {
      $data = array(
      'status' => 'aktiv'
      );
      $where[] = "id = '".$this->_getParam("id")."'";
      $db->update('lehrer', $data, $where);
      $this->_redirect("administrator/lehreruebersicht");
    } else if ($this->_hasParam("id") && is_numeric($this->_getParam("id")) && $this->_getParam("sure") != "yes"){
      $sql = "select * FROM lehrer
              WHERE lehrer.status='inaktiv' AND lehrer.freigeschaltet='yes' AND lehrer.id='".$this->_getParam("id")
      . "' LIMIT 1";
      $this->view->result = $db->fetchRow($sql);
      echo $this->view->render('administrator/lehrer/lehreraktivsetzen.phtml');
    } else {
      $this->_redirect("/administrator/lehreruebersicht");
    }
  }

  public function lehrerLoeschenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    if($this->_hasParam("id") && $this->_hasParam("sure") && $this->_getParam("sure") == "yes" && is_numeric($this->_getParam("id")))
    {
      $data = array(
      'status' => 'geloescht'
      );
      $where[] = "id = '".$this->_getParam("id")."'";
      $db->update('lehrer', $data, $where);
      $this->_redirect("administrator/lehrergeloescht");
    } else if ($this->_hasParam("id") && is_numeric($this->_getParam("id")) && $this->_getParam("sure") != "yes"){
      $sql = "select * FROM lehrer
                  WHERE lehrer.id='".$this->_getParam("id")
      . "' LIMIT 1";
      $this->view->result = $db->fetchRow($sql);
      echo $this->view->render('administrator/lehrer/lehrerloeschen.phtml');
    } else {
      $this->_redirect("/administrator/lehreruebersicht");
    }
  }

  public function lehrerReaktivierenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    if($this->_hasParam("id") && $this->_hasParam("sure") && $this->_getParam("sure") == "yes" && is_numeric($this->_getParam("id")))
    {
      $data = array(
      'status' => 'aktiv'
      );
      $where[] = "id = '".$this->_getParam("id")."'";
      $db->update('lehrer', $data, $where);
      $this->_redirect("administrator/lehreruebersicht");
    } else if ($this->_hasParam("id") && is_numeric($this->_getParam("id")) && $this->_getParam("sure") != "yes"){
      $sql = "select * FROM lehrer
              WHERE lehrer.id='".$this->_getParam("id")
      . "' LIMIT 1";
      $this->view->result = $db->fetchRow($sql);
      echo $this->view->render('administrator/lehrer/lehrerreaktivieren.phtml');
    } else {
      $this->_redirect("/administrator/lehrergeloescht");
    }
  }

  public function lehrerDatenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    if($this->_hasParam("id") && $this->_hasParam("modal") && is_numeric($this->_getParam("id")))
    {	 /* AND lehrer.id=".$this->_getParam('id')."*/
      $sql = "select lehrer.id, lehrer.name_komplett AS l_name_komplett,
		    			lehrer_rechnungsdaten.preis_lehrer AS l_preis_lehrer, 
		    			lehrer_rechnungsdaten.preis_neutral AS l_preis_neutral, 
		    			lehrer_rechnungsdaten.preis_schueler AS l_preis_schueler, 
		    			schueler.id AS s_id, 
		    			schueler.name_komplett AS s_name_komplett, 
		    			schueler_rechnungsdaten.preis_lehrer AS s_preis_lehrer, 
		    			schueler_rechnungsdaten.preis_neutral AS s_preis_neutral,
		    			schueler_rechnungsdaten.preis_schueler AS s_preis_schueler, 
		    			lehrer_has_schueler.lehrer_id AS l_v_id, 
		    			lehrer_has_schueler.schueler_id AS s_v_id 
              		from lehrer
              		left join lehrer_has_schueler on lehrer.id=lehrer_has_schueler.lehrer_id
              		left join schueler on schueler.id=lehrer_has_schueler.schueler_id
              		left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id
          	  		left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id
	            	left join schueler_has_schueler_rechnungsdaten on schueler.id=schueler_has_schueler_rechnungsdaten.schueler_id
	            	left join schueler_rechnungsdaten on schueler_rechnungsdaten.id=schueler_has_schueler_rechnungsdaten.schueler_rechnungsdaten_id
              		WHERE lehrer.id=lehrer_has_schueler.lehrer_id AND lehrer.id=".$this->_getParam('id')." LIMIT 1";
      $this->view->result2 = $db->fetchAssoc($sql);
      $sql2 = "select *,
              (SELECT COUNT(lehrer_has_schueler.lehrer_id) FROM lehrer_has_schueler WHERE lehrer_has_schueler.lehrer_id=lehrer.id) AS countSchueler, 
              (SELECT COUNT(lehrer_has_schueler.schueler_id) FROM lehrer_has_schueler left join schueler on schueler.id=lehrer_has_schueler.schueler_id WHERE lehrer_has_schueler.lehrer_id=lehrer.id AND schueler.status='inaktiv') as countInaktivSchueler  from lehrer
              left join lehrer_has_lehrer_daten on lehrer.id=lehrer_has_lehrer_daten.lehrer_id
              left join lehrer_daten on lehrer_daten.id=lehrer_has_lehrer_daten.lehrer_daten_id
              left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id
              left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id
              left join lehrer_has_lehrer_faecher on lehrer.id=lehrer_has_lehrer_faecher.lehrer_id
              left join lehrer_faecher on lehrer_faecher.id=lehrer_has_lehrer_faecher.lehrer_faecher_id
              WHERE  lehrer.id=".$this->_getParam('id')." LIMIT 1";
      $this->view->result = $db->fetchRow($sql2);
      echo $this->view->render('administrator/lehrer/lehrerdetail.phtml');
    } else if (false){
      //echo $this->view->render('administrator/lehrer/lehrerreaktivieren.phtml');
    } else {
      $this->_redirect("/administrator/lehrer");
    }
  }

  public function lehrerVerknuepfenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));


    $sql = "select lehrer.id, lehrer.name_komplett AS l_name_komplett, lehrer_rechnungsdaten.preis_lehrer AS l_preis_lehrer, lehrer_rechnungsdaten.preis_neutral AS l_preis_neutral, lehrer_rechnungsdaten.preis_schueler AS l_preis_schueler, schueler.id AS s_id, schueler.name_komplett AS s_name_komplett, schueler_rechnungsdaten.preis_lehrer AS s_preis_lehrer, schueler_rechnungsdaten.preis_neutral AS s_preis_neutral ,schueler_rechnungsdaten.preis_schueler AS s_preis_schueler, lehrer_has_schueler.lehrer_id AS l_v_id, lehrer_has_schueler.schueler_id AS s_v_id
from lehrer
      left join lehrer_has_schueler on lehrer.id=lehrer_has_schueler.lehrer_id
      left join schueler on schueler.id=lehrer_has_schueler.schueler_id
      left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id
	  left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id
  	  left join schueler_has_schueler_rechnungsdaten on schueler.id=schueler_has_schueler_rechnungsdaten.schueler_id
	  left join schueler_rechnungsdaten on schueler_rechnungsdaten.id=schueler_has_schueler_rechnungsdaten.schueler_rechnungsdaten_id
	  WHERE lehrer.id=lehrer_has_schueler.lehrer_id";
    $this->view->result = $db->fetchAll($sql);
    echo $this->view->render('administrator/lehrer/lehrerverknuepfen.phtml');

  }

  public function lehrerLoescheVerknuepfungAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    if($this->_hasParam("l")
    && $this->_hasParam("s")
    && is_numeric($this->_getParam("l"))
    && is_numeric($this->_getParam("s")))
    {
      $where = array("lehrer_id = ".$this->_getParam("l"), "schueler_id = ".$this->_getParam("s"));
      $db->delete("lehrer_has_schueler", $where);
      $checkVermittlung = "select * from lehrer_has_schueler WHERE schueler_id= ".$this->_getParam("s");
      if(count($db->fetchRow($checkVermittlung)) > 0) {
        $update = array(
        'vermittelt' => 'nein'
        );
        $where = array("id = " .$this->_getParam("s"));
        $db->update("schueler",$update,$where);
      }

      $this->_redirect("/administrator/lehrerverknuepfen");
    } else {
      $this->_redirect("/administrator/lehrerverknuepfen");
    }
  }

  public function lehrerSucheAction(){
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $this->view->identity = $this->auth->getIdentity();
    if($this->_hasParam("submit")
    && $this->_getParam("submit") == "Suchen") {
      /* Variable holen
      Escapen
      Zusammenführung WHERE-Clause SQL String */

      if($this->_getParam("vorname") != null) {
        $where[] = "lehrer.vorname LIKE '%"
        .addslashes($this->_getParam("vorname"))."%'";
      }

      if($this->_getParam("nachname") != null) {
        $where[] = "lehrer.nachname LIKE '%"
        .addslashes($this->_getParam("nachname"))."%'";
      }
      if($this->_getParam("id") != null && is_numeric($this->_getParam("id"))) {
        $where[] = "lehrer.id=".$this->_getParam("id");
      }

      if($this->_getParam("ort") != null) {
        $where[] = "lehrer.ort LIKE '%"
        .addslashes($this->_getParam("ort"))."%'";
      }

      if($this->_getParam("plz") != null && is_numeric($this->_getParam("plz"))) {
        $where[] = "lehrer.plz LIKE '"
        .addslashes($this->_getParam("plz"))."%'";
      }

      if($this->_getParam("fach") != null && $this->_getParam("fach") != "nothing") {
        if($this->_getParam("klasse") >= 1 && is_numeric($this->_getParam("klasse"))) {
          $where[] = "lehrer_faecher.".addslashes($this->_getParam("fach"))." >= ".$this->_getParam("klasse");
        } else {
          $where[] = "lehrer_faecher.".addslashes($this->_getParam("fach"))." >= 1";
        }
      }
      if($this->_getParam("preisbei") != null && $this->_getParam("preisbei") != "nothing") {
        if($this->_getParam("preis") >= 1 && is_numeric($this->_getParam("preis"))) {
          $where[] = "lehrer_rechnungsdaten.".addslashes($this->_getParam("preisbei"))." >= ".$this->_getParam("preis");
        } else {
          $where[] = "lehrer_rechnungsdaten.".addslashes($this->_getParam("preisbei"))." >= 1";
        }
      }

      if($this->_getParam("sfach") != null) {
        $where[] = "lehrer_faecher.sonstiges LIKE '%"
        .addslashes($this->_getParam("sfach"))."%'";
      }

      if($this->_getParam("limit") != null && is_numeric($this->_getParam("limit"))) {
        $limit[] = $this->_getParam("limit");
      }

      $sql = "select *, lehrer.id AS l_id, lehrer.ort AS l_ort, lehrer.plz AS l_plz, lehrer.vorname AS l_vorname,
			  lehrer.nachname AS l_nachname
			  from lehrer
              left join lehrer_has_lehrer_daten on lehrer.id=lehrer_has_lehrer_daten.lehrer_id
              left join lehrer_daten on lehrer_daten.id=lehrer_has_lehrer_daten.lehrer_daten_id
              left join lehrer_has_lehrer_faecher on lehrer.id=lehrer_has_lehrer_faecher.lehrer_id
              left join lehrer_faecher on lehrer_faecher.id=lehrer_has_lehrer_faecher.lehrer_faecher_id
              left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id
	  		  left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id
              WHERE ";

      /* Sortierungsprüfung */
      if($this->_hasParam("sort") && $this->_hasParam("sortby")
      && $this->_getParam("sortby") != null && $this->_getParam("sort") != null) {
        switch ($this->_getParam("sortby")) {
          case "id":
            $sortby = "lehrer.id";
            break;
          case "vorname":
            $sortby = "lehrer.vorname";
            break;
          case "nachname":
            $sortby = "lehrer.nachname";
            break;
          case "ort":
            $sortby = "lehrer.ort";
            break;
          case "plz":
            $sortby = "lehrer.plz";
            break;
          case "status":
            $sortby = "lehrer.status";
            break;
          case "preis_lehrer":
            $sortby = "lehrer_rechnungsdaten.preis_lehrer";
            break;
          case "preis_neutral":
            $sortby = "lehrer_rechnungsdaten.preis_neutral";
            break;
          case "preis_schueler":
            $sortby = "lehrer_rechnungsdaten.preis_schueler";
            break;
          default:
            $sortby = "lehrer.id";
            break;
        }
        if($this->_getParam("sort") == "DESC") { // Wenn sortierung DESC
          $sort  = " ORDER BY ".$sortby." DESC "; // Anhang für SQL
          $sortqry = "/sort/ASC";
        } elseif ($this->_getParam("sort") == "ASC")  { // Wenn sortierung ASC
          $sort  = " ORDER BY ".$sortby." ASC "; // Anhang für SQL
          $sortqry = "/sort/DESC";
        }
      } else { // Dieser Teil wenn kein Sort übergeben wird
        $sort = " ORDER BY lehrer.id ASC "; // Anhang für SQL
        $sortqry = "/sort/DESC";
      }

      /* Querystring bauen, sortierung festlegen und an viewer übergeben */
      $params = $this->_getAllParams();
      foreach($params as $key => $value)
      {
        if($key != "controller" && $key != "action" && $key != "module" && $key != "sort")
        {
          $qrystring .= "/".$key."/".$value;
          $viewArray[$key] = $value;
        }
      }
      $qrystring .= $sortqry;
      $this->view->qryarray = $viewArray;
      $this->view->qrystring = $qrystring;
      echo $qrystring;
      $clauseCounter = 0;
      foreach($where as $value) {
        if($clauseCounter > 0)
        $sql .= "AND ".$value." ";
        else
        $sql .= $value." ";
        $clauseCounter++;
      }
      $sql .= $sort;
      /* DB - Select */
      echo $sql; // Debug
      $searchResult = $this->db->fetchAll($sql);
      $this->view->searched = true;
      if(count($searchResult) > 0)
      $this->view->searchResult = $searchResult;
      else
      $this->view->searchResult = "nothingfound";
    }
    echo $this->view->render('administrator/lehrer/lehrersuche.phtml');
  }

  /* Schüler */
  public function schuelerAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($this->db);
    /* Anstehende Administration */
  }

  public function schuelerAddAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $this->view->identity = $this->auth->getIdentity();
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($this->db);

    echo $this->view->render('administrator/schueler/schueleradd.phtml');
  }

  public function schuelerAddDataAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    //var_dump($this->auth->getIdentity());
    $data = $this->_getAllParams();
    $this->schuelerAddDataToDB($data,$this->auth->getIdentity());
  }

  private function schuelerAddDataToDB($formdata,$session)
  {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $schueler = array(
    'username' => $formdata["username"],
    'password' => md5($formdata["password"]),
    'vorname' => $formdata["vorname"],
    'nachname' => $formdata["nachname"],
    'elternteil_vorname' => $formdata["vornameelt"],
    'elternteil_nachname' => $formdata["vornameelt2"],
    'elternteil_gebdate' => $formdata["eltgebdate"],
    'gebdate' => $formdata["gebdate"],
    'vertragspartner' => $formdata["vertragspartner"],
    'name_komplett' => $formdata["vorname"] . " " . $formdata["nachname"],
    'email' => $formdata["email"],
    'ort' => $formdata["ort"],
    'plz' => $formdata["plz"],
    'strasse' => $formdata["strasse"],
    'geschlecht' => $formdata["geschlecht"],
    'telnr' => $formdata["telnr"],
    'telnr_unformatiert' => $formdata["telnr"],
    'telnr_geschaeftlich' => $formdata["telnrg"],
    'telnr_geschaeftlich_unformatiert' => $formdata["telnrg"],
    'mobilnr' => $formdata["mobil"],
    'mobilnr_unformatiert' => $formdata["mobil"],
    'mobilbesitzer' => $formdata["mobilbesitzer"],
    'faxnr' => $formdata["fax"],
    'faxnr_unformatiert' => $formdata["fax"],
    'status' => 'aktiv',
    );

    $datum = explode(".",$formdata["gebdate"]);
    $datum = $datum[2]."-".$datum[1]."-".$datum[0];
    $schueler_daten = array(
    'preis_lehrer' => $formdata["preis_lehrer"],
    'preis_schueler' => $formdata["preis_schueler"],
    'preis_neutral' => $formdata["preis_neutral"],
    'unterrichtbei_lehrer' => $formdata["unterrichtbei_lehrer"],
    'unterrichtbei_schueler' => $formdata["unterrichtbei_schueler"],
    'unterrichtbei_neutral' => $formdata["unterrichtbei_neutral"],
    'kfz' => $formdata["kfz"],
    'geburtsdatum' => $datum,
    'kfz' => $formdata["kfz"],
    'entdeckt_durch' => $formdata["entdeckt"],
    'entdeckt_zusatz' => $formdata["erfahrung"],
    'empfohlen_durch' => $formdata["empfehler"],
    'angelegt' => $this->generateSqlTimestamp(),
    'vertragspartner' => $formdata["vertragspartner"],
    'bemerkungen' => $formdata["bemerkungen"],
    'entdeckt_durch' => $formdata["entdeckt"],
    'entdeckt_zusatz' => $formdata["entdecktzusatz"],
    'angelegt_durch' => $session->id,
    'preis_lehrer' => $formdata["preis_lehrer"],
    'preis_neutral' => $formdata["preis_neutral"],
    'preis_schueler' => $formdata["preis_schueler"],
    'sachbearbeiter' => $this->view->identity->id
    );
    $schueler_faecher = array(
    'chemie' => $formdata["chemie"],
    'c_kurs' => $formdata["chemiekurs"],
    'deutsch' => $formdata["deutsch"],
    'd_kurs' => $formdata["deutschkurs"],
    'englisch' => $formdata["englisch"],
    'e_kurs' => $formdata["englischkurs"],
    'franzoesisch' => $formdata["franzoesisch"],
    'f_kurs' => $formdata["franzoesischkurs"],
    'latein' => $formdata["latein"],
    'l_kurs' => $formdata["lateinkurs"],
    'mathematik' => $formdata["mathematik"],
    'm_kurs' => $formdata["mathematikkurs"],
    'physik' => $formdata["phyisk"],
    'p_kurs' => $formdata["physikkurs"],
    'rechtskunde' => $formdata["jura"],
    'r_kurs' => $formdata["jurakurs"],
    'spanisch' => $formdata["spanisch"],
    's_kurs' => $formdata["spanischkurs"],
    'wirtschaft' => $formdata["bwr"],
    'w_kurs' => $formdata["bwrkurs"],
    'sonstiges' => $formdata["ssonstiges2"],
    'schultyp' => $formdata["schultyp"],
    'schultyp_sonstiges' => $formdata["ssonstiges"]
    );
    if($formdata["preis_lehrer"] == null) $formdata["preis_lehrer"] = "0";
    if($formdata["preis_neutral"] == null) $formdata["preis_neutral"] = "0";
    if($formdata["preis_schueler"] == null) $formdata["preis_schueler"] = "0";
    $schueler_rechnungsdaten = array(
    'rechnung_firma' => $formdata["rechnung_firma"],
    'rechnung_vorname' => $formdata["rechnung_vorname"],
    'rechnung_nachname' => $formdata["rechnung_nachname"],
    'rechnung_ort' => $formdata["rechnung_ort"],
    'rechnung_plz' => $formdata["rechnung_plz"],
    'rechnung_strasse' => $formdata["rechnung_strasse"],
    'rechnung_ktoinhaber' => $formdata["rechnung_ktoinhaber"],
    'rechnung_kto' => $formdata["rechnung_kto"],
    'rechnung_blz' => $formdata["rechnung_blz"],
    'rechnung_bkinstitut' => $formdata["rechnung_bkinstitut"],
    'rechnung_bankeinzug' => $formdata["bankeinzug"],
    'rechnung_posten' => $formdata["rechnung_posten"]
    );
    /* Insert into db and read out id's for relationships */
    $db->insert('schueler',$schueler);
    $schuelerID = $db->lastInsertId();
    $db->insert('schueler_daten',$schueler_daten);
    $schuelerDatenID = $db->lastInsertId();
    $db->insert('schueler_rechnungsdaten',$schueler_rechnungsdaten);
    $schuelerRechnungsdatenID = $db->lastInsertId();
    $db->insert('schueler_faecher',$schueler_faecher);
    $schuelerFaecherID = $db->lastInsertId();

    /* Adding Relationship id's */
    $schueler_has_schueler_daten = array(
    'schueler_id' => $schuelerID,
    'schueler_daten_id' => $schuelerDatenID
    );
    $db->insert('schueler_has_schueler_daten',$schueler_has_schueler_daten);
    $schueler_has_schueler_faecher = array(
    'schueler_id' => $schuelerID,
    'schueler_faecher_id' => $schuelerFaecherID
    );
    $db->insert('schueler_has_schueler_faecher',$schueler_has_schueler_faecher);
    $schueler_has_schueler_rechnungsdaten = array(
    'schueler_id' => $schuelerID,
    'schueler_rechnungsdaten_id' => $schuelerRechnungsdatenID
    );
    $db->insert('schueler_has_schueler_rechnungsdaten',$schueler_has_schueler_rechnungsdaten);
    $this->_redirect("/administrator/schueler/schueleruebersicht");
  }

  public function schuelerUebersichtAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "select *
              from schueler
              left join schueler_has_schueler_faecher on schueler.id=schueler_has_schueler_faecher.schueler_id
              left join schueler_faecher on schueler_faecher.id=schueler_has_schueler_faecher.schueler_faecher_id
              WHERE schueler.status='aktiv' ORDER BY schueler.id ASC LIMIT 10";
    $this->view->result = $db->fetchAll($sql);
    $this->view->identity = $this->auth->getIdentity();
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);

    echo $this->view->render('administrator/schueler/schueleruebersicht.phtml');
  }

  public function schuelerDatenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    if($this->_hasParam("id") && $this->_hasParam("modal") && is_numeric($this->_getParam("id")))
    {	 /* AND lehrer.id=".$this->_getParam('id')."*/
      $sql = "select *,
              (SELECT COUNT(lehrer_has_schueler.schueler_id) FROM lehrer_has_schueler WHERE lehrer_has_schueler.schueler_id=schueler.id) AS countLehrer, 
              (SELECT COUNT(lehrer_has_schueler.lehrer_id) FROM lehrer_has_schueler left join lehrer on lehrer.id=lehrer_has_schueler.lehrer_id WHERE lehrer_has_schueler.schueler_id=schueler.id AND lehrer.status='inaktiv') as countInaktivLehrer  from schueler
              left join schueler_has_schueler_daten on schueler.id=schueler_has_schueler_daten.schueler_id
              left join schueler_daten on schueler_daten.id=schueler_has_schueler_daten.schueler_daten_id
              left join schueler_has_schueler_rechnungsdaten on schueler.id=schueler_has_schueler_rechnungsdaten.schueler_id
              left join schueler_rechnungsdaten on schueler_rechnungsdaten.id=schueler_has_schueler_rechnungsdaten.schueler_rechnungsdaten_id
              left join schueler_has_schueler_faecher on schueler.id=schueler_has_schueler_faecher.schueler_id
              left join schueler_faecher on schueler_faecher.id=schueler_has_schueler_faecher.schueler_faecher_id
              WHERE  schueler.id=".$this->_getParam('id')." LIMIT 1";
      $this->view->result = $db->fetchRow($sql);
      //var_dump($db->fetchRow($sql));
      echo $this->view->render('administrator/schueler/schuelerdetail.phtml');
    } else if (false){
      //echo $this->view->render('administrator/lehrer/lehrerreaktivieren.phtml');
    } else {
      $this->_redirect("/administrator/schueler");
    }
  }

  public function schuelerUnvermitteltAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "select * from schueler
              left join lehrer_has_schueler on schueler.id=lehrer_has_schueler.schueler_id
              left join schueler_has_schueler_daten on schueler.id=schueler_has_schueler_daten.schueler_id
              left join schueler_daten on schueler_daten.id=schueler_has_schueler_daten.schueler_daten_id
              left join schueler_has_schueler_faecher on schueler.id=schueler_has_schueler_faecher.schueler_id
              left join schueler_faecher on schueler_faecher.id=schueler_has_schueler_faecher.schueler_faecher_id
              WHERE schueler.status='aktiv' AND schueler.vermittelt='nein'
              ORDER BY schueler.id ASC LIMIT 10";

    /*$sql = "select * from schueler
    left join lehrer_has_schueler on schueler.id=lehrer_has_schueler.schueler_id
    left join schueler_has_schueler_daten on schueler.id=schueler_has_schueler_daten.schueler_id
    left join schueler_daten on schueler_daten.id=schueler_has_schueler_daten.schueler_daten_id

    WHERE schueler.status='aktiv' AND schueler.vermittelt='nein'
    AND schueler.id=2 ORDER BY schueler.id ASC LIMIT 10";
    $query = $db->fetchRow($sql);

    foreach ($faecher as $key => $value) {
    if($value > 0 )
    $f_angebot .= strtoupper(substr($key,0,1))." ";
    }*/
    //echo $f_angebot;

    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    $this->view->result = $db->fetchAll($sql);
    echo $this->view->render('administrator/schueler/schuelerunvermittelt.phtml');

  }

  public function schuelerUnbestaetigtAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    /*$sql = "select * from schueler
    left join lehrer_has_schueler on schueler.id=lehrer_has_schueler.schueler_id
    left join schueler_has_schueler_daten on schueler.id=schueler_has_schueler_daten.schueler_id
    left join schueler_daten on schueler_daten.id=schueler_has_schueler_daten.schueler_daten_id
    WHERE schueler.status='aktiv' AND lehrer_has_schueler.schueler_vermittelt='ja' AND
    lehrer_has_schueler.schueler_bestaetigt='nein'
    ORDER BY schueler.id ASC LIMIT 10";*/
    $sql = "select schueler.name_komplett as name_komplett, schueler.id as id,lehrer.name_komplett as l_name, lehrer.id as l_id from schueler
              left join lehrer_has_schueler on schueler.id=lehrer_has_schueler.schueler_id
              left join lehrer on lehrer.id=lehrer_has_schueler.lehrer_id
              left join schueler_has_schueler_daten on schueler.id=schueler_has_schueler_daten.schueler_id
              left join schueler_daten on schueler_daten.id=schueler_has_schueler_daten.schueler_daten_id
              WHERE schueler.status='aktiv' AND lehrer_has_schueler.schueler_vermittelt='ja' AND 
              lehrer_has_schueler.schueler_bestaetigt='nein' 
              ORDER BY schueler.id ASC LIMIT 10";

    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    $this->view->result = $db->fetchAll($sql);
    echo $this->view->render('administrator/schueler/schuelerunbestaetigt.phtml');

  }

  public function schuelerNachfrageAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "select schueler.name_komplett as name_komplett, schueler.id as id,lehrer.name_komplett as l_name, lehrer.id as l_id from schueler
             left join lehrer_has_schueler on schueler.id=lehrer_has_schueler.schueler_id
              left join lehrer on lehrer.id=lehrer_has_schueler.lehrer_id
              left join schueler_has_schueler_daten on schueler.id=schueler_has_schueler_daten.schueler_id
              left join schueler_daten on schueler_daten.id=schueler_has_schueler_daten.schueler_daten_id
              WHERE schueler.status='aktiv' AND lehrer_has_schueler.schueler_vermittelt='ja' AND 
              lehrer_has_schueler.schueler_bestaetigt='ja' AND lehrer_has_schueler.schueler_nachfrage='nein' 
              ORDER BY schueler.id ASC LIMIT 10";
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    $this->view->result = $db->fetchAll($sql);
    echo $this->view->render('administrator/schueler/schuelernachfrage.phtml');

  }
  /*if($this->_hasParam("l_id")
  && $this->_hasParam("s_id")
  && is_numeric($this->_getParam("l_id"))
  && is_numeric($this->_getParam("s_id")))
  {
  $data = array(
  'lehrer_id' => $this->_getParam("l_id"),
  'schueler_id' => $this->_getParam("s_id"),
  'schueler_vermittelt' => 'ja',
  'schueler_bestaetigt' => 'nein',
  'verlinkt_am' => $this->generateSqlTimestamp()
  );
  $db->insert('lehrer_has_schueler',$data);
  $update = array(
  'vermittelt' => 'ja'
  );
  $where[] = "id = " .$this->_getParam("s_id");
  $db->update("schueler",$update,$where);
  $this->_redirect("/administrator/lehrerverknuepfen");
  } else {
  $this->_redirect("/administrator/lehrerverknuepfen");
  }*/

  public function nachfrageErfuelltAction(){
    if($this->_hasParam("lid")
    && $this->_hasParam("sid")
    && is_numeric($this->_getParam("lid"))
    && is_numeric($this->_getParam("sid")))
    {
      $update = array(
      'schueler_nachfrage' => 'ja',
      'nachgefragt_am' => $this->generateSqlTimestamp()
      );
      $where[] = "lehrer_id = " .$this->_getParam("lid");
      $where[] = "schueler_id = " .$this->_getParam("sid");
      $this->db->update("lehrer_has_schueler",$update,$where);
      $this->_redirect("/administrator/schuelernachfrage");
    } else {
      $this->_redirect("/administrator/schuelernachfrage");
    }
  }

  public function schuelerInaktivUebersichtAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "select * from schueler WHERE schueler.status='inaktiv' ORDER BY schueler.id ASC LIMIT 10";
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    $this->view->result = $db->fetchAll($sql);
    echo $this->view->render('administrator/schueler/schuelerinaktivuebersicht.phtml');

  }

  public function schuelerGeloeschtAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "select * from schueler
              WHERE schueler.status='geloescht' ORDER BY schueler.id ASC LIMIT 10";
    $this->view->identity = $this->auth->getIdentity();
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    $this->view->result = $db->fetchAll($sql);
    echo $this->view->render('administrator/schueler/schuelergeloescht.phtml');

  }

  public function schuelerInaktivSetzenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    $this->view->identity = $this->auth->getIdentity();
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);

    if($this->_hasParam("id") && $this->_hasParam("sure") && $this->_getParam("sure") == "yes" && is_numeric($this->_getParam("id")))
    {
      $data = array(
      'status' => 'inaktiv'
      );
      $where[] = "id = '".$this->_getParam("id")."'";
      $db->update('schueler', $data, $where);
      $this->_redirect("administrator/schuelerinaktivuebersicht");
    } else if ($this->_hasParam("id") && is_numeric($this->_getParam("id")) && $this->_getParam("sure") != "yes"){
      $sql = "select * FROM schueler
              WHERE schueler.status='aktiv' AND schueler.id='".$this->_getParam("id")
      . "' LIMIT 1";
      $this->view->result = $db->fetchRow($sql);
      echo $this->view->render('administrator/schueler/schuelerinaktivsetzen.phtml');
    } else {
      $this->_redirect("/administrator/schueleruebersicht");
    }
  }

  public function schuelerAktivSetzenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    if($this->_hasParam("id") && $this->_hasParam("sure") && $this->_getParam("sure") == "yes" && is_numeric($this->_getParam("id")))
    {
      $data = array(
      'status' => 'aktiv'
      );
      $where[] = "id = '".$this->_getParam("id")."'";
      $db->update('schueler', $data, $where);
      $this->_redirect("administrator/schueleruebersicht");
    } else if ($this->_hasParam("id") && is_numeric($this->_getParam("id")) && $this->_getParam("sure") != "yes"){
      $sql = "select * FROM schueler
              WHERE schueler.status='inaktiv' AND schueler.id='".$this->_getParam("id")
      . "' LIMIT 1";
      $this->view->result = $db->fetchRow($sql);
      echo $this->view->render('administrator/schueler/schueleraktivsetzen.phtml');
    } else {
      $this->_redirect("/administrator/schueleruebersicht");
    }
  }

  public function schuelerLoeschenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    if($this->_hasParam("id") && $this->_hasParam("sure") && $this->_getParam("sure") == "yes" && is_numeric($this->_getParam("id")))
    {
      $data = array(
      'status' => 'geloescht'
      );
      $where[] = "id = '".$this->_getParam("id")."'";
      $db->update('schueler', $data, $where);
      $this->_redirect("administrator/schuelergeloescht");
    } else if ($this->_hasParam("id") && is_numeric($this->_getParam("id")) && $this->_getParam("sure") != "yes"){
      $sql = "select * FROM schueler
              WHERE schueler.id='".$this->_getParam("id")
      . "' LIMIT 1";
      $this->view->result = $db->fetchRow($sql);
      echo $this->view->render('administrator/schueler/schuelerloeschen.phtml');
    } else {
      $this->_redirect("/administrator/schueleruebersicht");
    }
  }

  public function schuelerReaktivierenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    if($this->_hasParam("id") && $this->_hasParam("sure") && $this->_getParam("sure") == "yes" && is_numeric($this->_getParam("id")))
    {
      $data = array(
      'status' => 'aktiv'
      );
      $where[] = "id = '".$this->_getParam("id")."'";
      $db->update('schueler', $data, $where);
      $this->_redirect("administrator/schueleruebersicht");
    } else if ($this->_hasParam("id") && is_numeric($this->_getParam("id")) && $this->_getParam("sure") != "yes"){
      $sql = "select * FROM schueler
              WHERE schueler.id='".$this->_getParam("id")
      . "' LIMIT 1";
      $this->view->result = $db->fetchRow($sql);
      echo $this->view->render('administrator/schueler/schuelerreaktivieren.phtml');
    } else {
      $this->_redirect("/administrator/schuelergeloescht");
    }
  }

  public function schuelerVerknuepfenAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));


    $sql = "select lehrer.id, lehrer.name_komplett AS l_name_komplett, lehrer_rechnungsdaten.preis_lehrer AS l_preis_lehrer, lehrer_rechnungsdaten.preis_neutral AS l_preis_neutral, lehrer_rechnungsdaten.preis_schueler AS l_preis_schueler, schueler.id AS s_id, schueler.name_komplett AS s_name_komplett, schueler_rechnungsdaten.preis_lehrer AS s_preis_lehrer, schueler_rechnungsdaten.preis_neutral AS s_preis_neutral ,schueler_rechnungsdaten.preis_schueler AS s_preis_schueler, lehrer_has_schueler.lehrer_id AS l_v_id, lehrer_has_schueler.schueler_id AS s_v_id
from lehrer
              left join lehrer_has_schueler on lehrer.id=lehrer_has_schueler.lehrer_id
              left join schueler on schueler.id=lehrer_has_schueler.schueler_id

              left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id
	  left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id
  	  
	  left join schueler_has_schueler_rechnungsdaten on schueler.id=schueler_has_schueler_rechnungsdaten.schueler_id
	  left join schueler_rechnungsdaten on schueler_rechnungsdaten.id=schueler_has_schueler_rechnungsdaten.schueler_rechnungsdaten_id

WHERE lehrer.id=lehrer_has_schueler.lehrer_id";
    $this->view->vmCounter = $this->schuelerGetVermittlungsCount($db);
    $this->view->result = $db->fetchAll($sql);
    $this->view->sID = $this->_getParam("id");
    echo $this->view->render('administrator/schueler/schuelerverknuepfen.phtml');
  }

  public function schuelerLoescheVerknuepfungAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    if($this->_hasParam("l")
    && $this->_hasParam("s")
    && is_numeric($this->_getParam("l"))
    && is_numeric($this->_getParam("s")))
    {
      $where = array("lehrer_id = ".$this->_getParam("l"), "schueler_id = ".$this->_getParam("s"));
      $db->delete("lehrer_has_schueler", $where);
      $checkVermittlung = "select * from lehrer_has_schueler WHERE schueler_id= ".$this->_getParam("s");
      if(!$db->fetchRow($checkVermittlung)) {
        $update = array(
        'vermittelt' => 'nein'
        );
        $where = array("id = " .$this->_getParam("s"));
        $db->update("schueler",$update,$where);
      }

      $this->_redirect("/administrator/schuelerverknuepfen");
    } else {
      $this->_redirect("/administrator/schuelerverknuepfen");
    }
  }

  public function schuelerGetVermittlungsCount($db) {
    $sql = "select count(vermittelt)
            AS schueler_vermittelt, (select count(schueler_bestaetigt) 
            from lehrer_has_schueler WHERE schueler_bestaetigt='nein' AND schueler_vermittelt != 'nein') 
            AS schueler_unbestaetigt, (select count(schueler_nachfrage) 
            from lehrer_has_schueler WHERE schueler_bestaetigt='ja' AND schueler_vermittelt= 'ja' 
            AND schueler_nachfrage='nein') 
            AS schueler_nachfrage
            from schueler WHERE vermittelt='nein'";
    return $db->fetchrow($sql);
  }

  /* public function testAction() {
  if (!$this->auth->hasIdentity())
  $this->_redirect("/administrator/login");
  $this->view->identity = $this->auth->getIdentity();
  Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
  $db = new Zend_Db_Adapter_Pdo_Mysql(array(
  'host'     => $this->config->database->host,
  'username' => $this->config->database->username,
  'password' => $this->config->database->password,
  'dbname'   => $this->config->database->name
  ));

  $sql = "select lehrer.id, lehrer.name_komplett AS l_name_komplett, lehrer_rechnungsdaten.preis_lehrer AS l_preis_lehrer, lehrer_rechnungsdaten.preis_neutral AS l_preis_neutral, lehrer_rechnungsdaten.preis_schueler AS l_preis_schueler, schueler.id AS s_id, schueler.name_komplett AS s_name_komplett, schueler_rechnungsdaten.preis_lehrer AS s_preis_lehrer, schueler_rechnungsdaten.preis_neutral AS s_preis_neutral ,schueler_rechnungsdaten.preis_schueler AS s_preis_schueler
  from lehrer
  left join lehrer_has_schueler on lehrer.id=lehrer_has_schueler.lehrer_id
  left join schueler on schueler.id=lehrer_has_schueler.schueler_id

  left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id
  left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id

  left join schueler_has_schueler_rechnungsdaten on schueler.id=schueler_has_schueler_rechnungsdaten.schueler_id
  left join schueler_rechnungsdaten on schueler_rechnungsdaten.id=schueler_has_schueler_rechnungsdaten.schueler_rechnungsdaten_id

  WHERE lehrer.id=lehrer_has_schueler.lehrer_id";
  //$this->view->result = $db->fetchRow($sql);
  var_dump($db->fetchAll($sql));
  }*/

  public function showSupportBoxAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    $sqlgetmsg = "SELECT *, DATE_FORMAT(erstellt, '%e.%c.%y %k:%i:%s' ) AS eingang,(SELECT lehrer.name_komplett FROM lehrer WHERE lehrer.id=support_box.lehrer_id) as lehrerName FROM support_box WHERE support_box.von='lehrer' AND support_box.geantwortet='nein' ORDER BY support_box.erstellt DESC";
    $sqlsendedmsg = "SELECT *, DATE_FORMAT(erstellt, '%e.%c.%y %k:%i:%s' ) AS eingang,(SELECT lehrer.name_komplett FROM lehrer WHERE lehrer.id=support_box.lehrer_id) as lehrerName FROM support_box WHERE support_box.von='lehrer' AND support_box.geantwortet='ja' ORDER BY support_box.erstellt DESC";

    $update = array(
    'gelesen' => 'ja'
    );
    $where[] = "von = 'lehrer'";
    $db->update('support_box', $update, $where);

    $result = $db->fetchAll($sqlgetmsg);
    $result2 = $db->fetchAll($sqlsendedmsg);
    $this->view->result = $result;
    $this->view->result2 = $result2;
  }

  public function deleteMsgAction(){
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    $db->delete('support_box', 'id = '.$this->_getParam("msgid"));
    $this->_redirect("/administrator/showsupportbox");
  }

  public function replymsgAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    if($this->_hasParam("send") && is_numeric($this->_getParam("msgid")) && is_numeric($this->_getParam("to")))
    {
      $msgdaten = array(
      'lehrer_id' => $this->_getParam("to"),
      'admin_id' => $this->auth->getIdentity()->id,
      'nachricht' => $this->_getParam("nachricht"),
      'von' => 'admin'
      );

      $update = array(
      'geantwortet' => 'ja'
      );
      $where[] = "id = '".$this->_getParam("msgid")."'";

      $db->update('support_box', $update, $where);
      $db->insert('support_box',$msgdaten);
      $this->_redirect("/administrator/showsupportbox");
      //echo $this->auth->id;
    } else if (is_numeric($this->_getParam("msgid")) && is_numeric($this->_getParam("to"))) {
      $this->view->msgid = $this->_getParam("msgid");
      $this->view->to = $this->_getParam("to");
      echo $this->view->render('administrator/lehrer/lehrerreply.phtml');
    } else {
      $this->_redirect("/administrator/showsupportbox");
    }
  }

  public function getLehrerByNameAction()
  {
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $this->config = new Zend_Config_Ini($_SERVER['DOCUMENT_ROOT'] .'config.ini', 'production', $config);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    if(strlen($this->_getParam("name")) < 2) die();
    $ajax = addslashes($this->_getParam("name"));
    //$ajax = "Bau";
    $sql = "select lehrer.id as l_id,lehrer.name_komplett AS l_name, lehrer_rechnungsdaten.preis_lehrer AS l_p_l,"
    ." lehrer_rechnungsdaten.preis_neutral AS l_p_n, lehrer_rechnungsdaten.preis_schueler AS l_p_s"
    ." FROM lehrer left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id "
    ." left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id WHERE "
    ." lehrer.name_komplett LIKE '%".$ajax."%' ORDER BY lehrer.name_komplett ASC LIMIT 15";
    $query = $db->query($sql);
    foreach($db->fetchAll($sql) as $value) {
      echo "<li onclick='filllehrer(\"".$value['l_id']."\",\"".htmlentities($value['l_name'])."\",\"".$value["l_p_l"]."\",\"".$value["l_p_n"]."\",\"".$value["l_p_s"]."\");'>".htmlentities($value['l_name'])." (".$value['l_id'].")</li>";
    }
  }

  public function getLehrerByIdAction()
  {
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $this->config = new Zend_Config_Ini($_SERVER['DOCUMENT_ROOT'] .'config.ini', 'production', $config);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    if(strlen($this->_getParam("name")) < 0) die();
    $ajax = addslashes($this->_getParam("name"));
    //$ajax = "Bau";
    $sql = "select lehrer.id as l_id,lehrer.name_komplett AS l_name, lehrer_rechnungsdaten.preis_lehrer AS l_p_l,"
    ." lehrer_rechnungsdaten.preis_neutral AS l_p_n, lehrer_rechnungsdaten.preis_schueler AS l_p_s"
    ." FROM lehrer left join lehrer_has_lehrer_rechnungsdaten on lehrer.id=lehrer_has_lehrer_rechnungsdaten.lehrer_id "
    ." left join lehrer_rechnungsdaten on lehrer_rechnungsdaten.id=lehrer_has_lehrer_rechnungsdaten.lehrer_rechnungsdaten_id WHERE "
    ." lehrer.id LIKE '".$ajax."%' ORDER BY lehrer.name_komplett ASC LIMIT 15";
    $query = $db->query($sql);
    foreach($db->fetchAll($sql) as $value) {
      echo "<li onclick='filllehrer(\"".$value['l_id']."\",\"".htmlentities($value['l_name'])."\",\"".$value["l_p_l"]."\",\"".$value["l_p_n"]."\",\"".$value["l_p_s"]."\");'>".htmlentities($value['l_name'])." (".$value['l_id'].")</li>";
    }
  }

  public function getSchuelerByNameAction()
  {
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $this->config = new Zend_Config_Ini($_SERVER['DOCUMENT_ROOT'] .'config.ini', 'production', $config);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    if(strlen($this->_getParam("name")) < 2) die();
    $ajax = addslashes($this->_getParam("name"));
    //$ajax = "Bau";
    $sql = "select schueler.id as s_id,schueler.name_komplett AS s_name, schueler_rechnungsdaten.preis_schueler AS s_p_l,"
    ." schueler_rechnungsdaten.preis_neutral AS s_p_n, schueler_rechnungsdaten.preis_schueler AS s_p_s"
    ." FROM schueler left join schueler_has_schueler_rechnungsdaten on schueler.id=schueler_has_schueler_rechnungsdaten.schueler_id "
    ." left join schueler_rechnungsdaten on schueler_rechnungsdaten.id=schueler_has_schueler_rechnungsdaten.schueler_rechnungsdaten_id WHERE "
    ." schueler.name_komplett LIKE '%".$ajax."%' ORDER BY schueler.name_komplett ASC LIMIT 15";
    $query = $db->query($sql);
    foreach($db->fetchAll($sql) as $value) {
      echo "<li onclick='fillschueler(\"".$value['s_id']."\",\"".htmlentities($value['s_name'])."\",\"".$value["s_p_l"]."\",\"".$value["s_p_n"]."\",\"".$value["s_p_s"]."\");'>".htmlentities($value['s_name'])." (".$value['s_id'].")</li>";
    }
  }

  public function getSchuelerByIdAction()
  {
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $this->config = new Zend_Config_Ini($_SERVER['DOCUMENT_ROOT'] .'config.ini', 'production', $config);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    if(strlen($this->_getParam("name")) < 0) die();
    $ajax = addslashes($this->_getParam("name"));
    //$ajax = "Bau";
    $sql = "select schueler.id as s_id,schueler.name_komplett AS s_name, schueler_rechnungsdaten.preis_schueler AS s_p_s,"
    ." schueler_rechnungsdaten.preis_neutral AS s_p_n, schueler_rechnungsdaten.preis_lehrer AS s_p_l"
    ." FROM schueler left join schueler_has_schueler_rechnungsdaten on schueler.id=schueler_has_schueler_rechnungsdaten.schueler_id "
    ." left join schueler_rechnungsdaten on schueler_rechnungsdaten.id=schueler_has_schueler_rechnungsdaten.schueler_rechnungsdaten_id WHERE "
    ." schueler.id LIKE '".$ajax."%' ORDER BY schueler.name_komplett ASC LIMIT 15";
    $query = $db->query($sql);
    foreach($db->fetchAll($sql) as $value) {
      echo "<li onclick='fillschueler(\"".$value['s_id']."\",\"".htmlentities($value['s_name'])."\",\"".$value["s_p_l"]."\",\"".$value["s_p_n"]."\",\"".$value["s_p_s"]."\");'>".htmlentities($value['s_name'])." (".$value['s_id'].")</li>";
    }
  }

  public function verknuepfeLehrerAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    if($this->_hasParam("l_id")
    && $this->_hasParam("s_id")
    && is_numeric($this->_getParam("l_id"))
    && is_numeric($this->_getParam("s_id")))
    {
      $data = array(
      'lehrer_id' => $this->_getParam("l_id"),
      'schueler_id' => $this->_getParam("s_id"),
      'schueler_vermittelt' => 'ja',
      'schueler_bestaetigt' => 'nein',
      'verlinkt_am' => $this->generateSqlTimestamp()
      );
      $db->insert('lehrer_has_schueler',$data);
      $update = array(
      'vermittelt' => 'ja'
      );
      $where[] = "id = " .$this->_getParam("s_id");
      $db->update("schueler",$update,$where);
      $this->_redirect("/administrator/lehrerverknuepfen");
    } else {
      $this->_redirect("/administrator/lehrerverknuepfen");
    }
  }

  public function nachfrageSchuelerAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    /* if($this->_hasParam("l_id")
    && $this->_hasParam("s_id")
    && is_numeric($this->_getParam("l_id"))
    && is_numeric($this->_getParam("s_id")))
    {
    $data = array(
    'lehrer_id' => $this->_getParam("l_id"),
    'schueler_id' => $this->_getParam("s_id"),
    'schueler_vermittelt' => 'ja',
    'schueler_bestaetigt' => 'nein',
    'verlinkt_am' => $this->generateSqlTimestamp()
    );
    $db->insert('lehrer_has_schueler',$data);
    $update = array(
    'vermittelt' => 'ja'
    );
    $where[] = "id = " .$this->_getParam("s_id");
    $db->update("schueler",$update,$where);
    $this->_redirect("/administrator/schuelerverknuepfen");
    } else {
    $this->_redirect("/administrator/schuelerverknuepfen");
    }*/
  }

  public function verknuepfeSchuelerAction() {
    if (!$this->auth->hasIdentity())
    $this->_redirect("/administrator/login");
    $this->view->identity = $this->auth->getIdentity();
    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));
    if($this->_hasParam("l_id")
    && $this->_hasParam("s_id")
    && is_numeric($this->_getParam("l_id"))
    && is_numeric($this->_getParam("s_id")))
    {
      $data = array(
      'lehrer_id' => $this->_getParam("l_id"),
      'schueler_id' => $this->_getParam("s_id"),
      'schueler_vermittelt' => 'ja',
      'schueler_bestaetigt' => 'nein',
      'verlinkt_am' => $this->generateSqlTimestamp()
      );
      $db->insert('lehrer_has_schueler',$data);
      $update = array(
      'vermittelt' => 'ja'
      );
      $where[] = "id = " .$this->_getParam("s_id");
      $db->update("schueler",$update,$where);
      $this->_redirect("/administrator/schuelerverknuepfen");
    } else {
      $this->_redirect("/administrator/schuelerverknuepfen");
    }
  }

  /* login - logout */
  public function loginAction() {
    if ($this->auth->hasIdentity()) {
      // bereits eingeloggt?
      $this->_redirect("/administrator");
    }
    require_once 'Zend/Auth/Adapter/DbTable.php';
    $this->view->message = '';
    $this->view->title = "Log in";

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $this->view->message = '';
    if ($this->_request->isPost()) {
      // collect the data from the user
      Zend_Loader::loadClass('Zend_Filter_StripTags');
      $f = new Zend_Filter_StripTags();
      $username = $f->filter($this->_request->getPost('username'));
      $password = $f->filter($this->_request->getPost('password'));
      if (empty($username)) {
        $this->view->message = 'Please provide a username.';
      } else {
        // setup Zend_Auth adapter for a database table
        Zend_Loader::loadClass('Zend_Auth_Adapter_DbTable');
        $authAdapter = new Zend_Auth_Adapter_DbTable($db);
        $authAdapter->setTableName('verwaltung_user');
        $authAdapter->setIdentityColumn('username');
        $authAdapter->setCredentialColumn('password');
        // Set the input credential values to authenticate against
        $authAdapter->setIdentity($username);
        $authAdapter->setCredential(md5($password));
        // do the authentication
        $auth = Zend_Auth::getInstance();
        $result = $auth->authenticate($authAdapter);
        if ($result->isValid()) {

          // success: store database row to auth's storage
          // system. (Not the password though!)
          // workaround gebaut zum speichern von eigenen daten!
          $data = $authAdapter->getResultRowObject(null, 'password');
          $data->role = 'admin';
          $auth->getStorage()->write($data);
          $result = $authAdapter->authenticate();
          $this->_redirect('/administrator');
        } else {
          // failure: clear database row from session
          $this->view->message = 'Login failed.';
        }
      }
    }
    $this->view->title = "Log in";
  }

  public function logoutAction() {
    Zend_Auth::getInstance()->clearIdentity();
    $this->_redirect('/administrator/login');
  }

  /* Einige Funktionen die Zend nicht kann :) */
  private function generateSqlTimestamp() {
    require_once 'Zend/Date.php';
    // YYYY-MM-DD HH:MM:SS
    $date = Zend_Date::now();
    return $date->toString("yyyy-MM-dd HH:mm:ss");
  }

  public function testAction() {

    Zend_Controller_Front::getInstance()->setParam('noViewRenderer', true);
    $this->config = new Zend_Config_Ini($_SERVER['DOCUMENT_ROOT'] .'config.ini', 'production', $config);
    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => $this->config->database->host,
    'username' => $this->config->database->username,
    'password' => $this->config->database->password,
    'dbname'   => $this->config->database->name
    ));

    $sql = "select * from schueler
              left join lehrer_has_schueler on schueler.id=lehrer_has_schueler.schueler_id
              left join schueler_has_schueler_daten on schueler.id=schueler_has_schueler_daten.schueler_id
              left join schueler_daten on schueler_daten.id=schueler_has_schueler_daten.schueler_daten_id
	             left join schueler_has_schueler_faecher on schueler.id=schueler_has_schueler_faecher.schueler_id
              left join schueler_faecher on schueler_faecher.id=schueler_has_schueler_faecher.schueler_faecher_id	
              WHERE schueler.status='aktiv' AND schueler.vermittelt='nein'
              AND schueler.id=2 ORDER BY schueler.id ASC LIMIT 10";
    $query = $db->fetchRow($sql);
    $faecher = array(
    "chemie" => $query["chemie"],
    "deutsch" => $query["deutsch"],
    "englisch" => $query["englisch"],
    "franzoesisch" => $query["franzoesisch"],
    "latein" => $query["latein"],
    "mathematik" => $query["mathematik"],
    "physik" => $query["physik"],
    "rechtskunde" => $query["rechtskunde"],
    "spanisch" => $query["spanisch"],
    "wirtschaft" => $query["wirtschaft"]
    );
    foreach ($faecher as $key => $value) {
      if($value > 0 )
      $f_angebot .= strtoupper(substr($key,0,1))." ";
    }
    echo $f_angebot;
  }
}

?>