<?php

     // Diese Klasse ist f�r die Datenbankverbindung und Abfrage zust�dig

     class DATABASE
     {
           var $_sql = "";
           var $_result = 0;
           var $_errno = 0;
           var $_error = "";
           var $_objDB;


// Erstellt die DB Verbindung
    FUNCTION mk_new_connection(){
            try
            {
                $this->_objDB = NEW PDO(DB_TYPE.":dbname=".DB.";host=".DB_HOST, DB_USER, DB_PASS);
            }
            catch (PDOException $e)
            {
                die ('Fehler beim �ffnen der Datenbank: ' . $e->getMessage());
            }
                //@mysql_connect(DB_HOST, DB_USER, DB_PASS) OR die(mysql_error());
                //mysql_select_db(DB) OR die(mysql_error());
}

    FUNCTION __sleep(){

    }
           public FUNCTION getMenue(){
                RETURN $this->_objDB->query("
                SELECT
                      POSGROUPID AS id,
                      DESCRIPTION AS bezeichnung
                FROM
                      POSGROUP
                ORDER BY
                      DESCRIPTION ASC");
           }

           public FUNCTION getProdukte($param){
                $sql = "
                SELECT
                    produkt_id AS id,
                    preis,
                    bieter
                FROM
                    produkte_online
                WHERE
                    kategorie_id = :param";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("param",$param);
                $select->EXECUTE();
                RETURN $select;
           }

           public FUNCTION getProduktAuktion($param){
                $sql = "
                SELECT
                    preis,
                    bieter
                FROM
                    produkte_online
                WHERE
                    produkt_id = :param
                    ";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("param",$param);
                $select->EXECUTE();
                RETURN $select;
           }

           public FUNCTION getPic($param){
                $sql = "
                SELECT
                    bild, width, height
                FROM
                    bilder
                WHERE
                    id = :param";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("param",$param);
                $select->EXECUTE();
                RETURN $select;
           }

           public FUNCTION getNick($param){
            $sql = "
                SELECT
                    DECODE(USERNAME, :key)AS nick
                FROM
                    addressbook
                WHERE
                    MYID = :param";
            $select = $this->_objDB->PREPARE($sql);
            $select->bindParam("param",$param);
            $select->bindParam("key",get_key());
            $select->EXECUTE();
            RETURN $select;
           }

           public FUNCTION doGebot($user,$articel){
            $sql = "
                UPDATE
                    kontostand
                SET
                    kontostand = kontostand-'0.5'
                WHERE
                    userID = :user
                LIMIT 1";
                $this->_objDB->beginTransaction();
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("user",$user);
                $select->EXECUTE();
                $intIsError += $select->errorCode();
            $sql = "
                UPDATE
                    produkte_online
                SET
                    bieter = :user ,
                    preis = preis - 0.01
                WHERE
                    produkt_id = :articel
                LIMIT 1";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("user",$user);
                $select->bindParam("articel",$articel);
                $select->EXECUTE();
                $intIsError += $select->errorCode();
                IF ($intIsError== "00000") {
                    $this->_objDB->commit();
                }ELSE {
                    $this->_objDB->ROLLBACK();
                }
                RETURN $select;
           }

           public FUNCTION getProduktInfos($param){
                $sql = "
                SELECT
                    POS_NAME AS name,
                    POS_DESC AS besch,
                    bildlink AS bild,
                    POS_PRICE AS preis
                FROM
                    article
                WHERE
                    POSITIONID = :param";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("param",$param);
                $select->EXECUTE();
                RETURN $select;
           }

           public FUNCTION getProOnline($param){
                $sql = "
                SELECT
                           COUNT(kategorie_id) AS anzahl
                    FROM
                           produkte_online
                    WHERE
                           kategorie_id = :id
                    AND
                           online_status = '1'";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("id",$param);
                $select->EXECUTE();
                RETURN $select;
           }

           public FUNCTION checkUser($param){
                $sql = "
                SELECT MYID
                FROM ADDRESSBOOK
                WHERE USERNAME = ENCODE(:param, :KEY)";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("param",$param);
                $select->bindParam("KEY",get_key());
                $select->EXECUTE();
                RETURN $select;
           }

           public FUNCTION checkMail($param){
                $sql = "
                SELECT MYID
                FROM ADDRESSBOOK
                WHERE EMAIL = :param";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("param",$param);
                $select->EXECUTE();
                RETURN $select;
           }

           public FUNCTION createNewUser(   $PREFIX,
                                            $FIRSTNAME,
                                            $LASTNAME,
                                            $ADDRESS,
                                            $POSTALCODE,
                                            $CITY,
                                            $USERNAME,
                                            $PASSWORD,
                                            $EMAIL){
                $sql = "INSERT INTO addressbook(
                                                       PREFIX,
                                                       FIRSTNAME,
                                                       LASTNAME,
                                                       ADDRESS,
                                                       POSTALCODE,
                                                       CITY,
                                                       USERNAME,
                                                       PASSWORD,
                                                       EMAIL)
                                 VALUES(
                                        :PREFIX,
                                        :FIRSTNAME,
                                        :LASTNAME,
                                        :ADDRESS,
                                        :POSTALCODE,
                                        :CITY,
                                        ENCODE(:USERNAME, :KEY),
                                        ENCODE(:PASSWORD, :KEY),
                                        :EMAIL)";
                $this->_objDB->beginTransaction();
                $select = $this->PREPARE($sql);
                $select->bindParam("PREFIX",$PREFIX);
                $select->bindParam("FIRSTNAME",$FIRSTNAME);
                $select->bindParam("LASTNAME",$LASTNAME);
                $select->bindParam("ADDRESS",$ADDRESS);
                $select->bindParam("POSTALCODE",$POSTALCODE);
                $select->bindParam("CITY",$CITY);
                $select->bindParam("USERNAME",$USERNAME);
                $select->bindParam("PASSWORD",$PASSWORD);
                $select->bindParam("EMAIL",$EMAIL);
                $select->bindParam("KEY",get_key());
                $select->EXECUTE();
                $intIsError += $select->errorCode();
                $sql = "INSERT INTO kontostand(
                            userID,
                            kontostand)
                    VALUES (:id, :stand)";
                $tempo = 0.00;
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("id",$this->_objDB->lastInsertId());
                $select->bindParam("stand",$tempo);
                $select->EXECUTE();

                $intIsError += $select->errorCode();
                IF ($intIsError == "00000") {
                    $this->_objDB->commit();
                }ELSE {
                    $this->_objDB->ROLLBACK();
                }
                RETURN $select;

           }

           public FUNCTION checkLogin($name, $pw){
                $sql = "SELECT
                             MYID,
                             DECODE(USERNAME, :key)AS USERNAME,
                             aktiviert,
                             gesperrt
                       FROM
                             ADDRESSBOOK
                       WHERE
                             USERNAME = ENCODE(:name, :key)
                       AND
                             PASSWORD = ENCODE(:pw, :key)";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("name",$name);
                $select->bindParam("pw",$pw);
                $select->bindParam("key",get_key());
                $select->EXECUTE();
                RETURN $select;
           }

           public FUNCTION getTimeLeft($param){
                $sql = "SELECT
                            TO_DAYS(end_time)-TO_DAYS(NOW()) AS tage,
                            TIME_TO_SEC(end_time) - TIME_TO_SEC(NOW()) AS zeit
                        FROM
                            produkte_online
                        WHERE
                            produkt_id = :param";
                $select = $this->_objDB->PREPARE($sql);
                $select->bindParam("param",$param);
                $select->EXECUTE();
                RETURN $select;

           }

           public FUNCTION getGuthaben($param){
            $sql = "SELECT kontostand
                    FROM kontostand
                    WHERE userID = :param";
            $select = $this->_objDB->PREPARE($sql);
            $select->bindParam("param",$param);
            $select->EXECUTE();
            RETURN $select;
           }

           public FUNCTION getMail($param){
            $sql = "SELECT EMAIL
                    FROM addressbook
                    WHERE USERNAME = ENCODE(:param, :key)";
            $select = $this->_objDB->PREPARE($sql);
            $select->bindParam("param",$param);
            $select->bindParam("key",get_key());
            $select->EXECUTE();
            RETURN $select;
           }

           public FUNCTION countKategorie($param){
            $sql = "SELECT
                           COUNT(kategorie) AS anzahl
                    FROM
                           produkte
                    WHERE
                           {:param} = kategorie";
            $select = $this->_objDB->PREPARE($sql);
            $select->bindParam("param",$param);
            $select->EXECUTE();
            RETURN $select;
           }

?>