<?php
/* $Id$ */

include_once 'config.inc.php';
require_once 'persistens.php';
require_once 'helper.php';

class Pgsql extends PDO implements WebcalSupport {

	private $user;

    function __construct($dns = NULL) {
		if ($dns)
			$db_dns = "pgsql:$dns";
		else
			$db_dns = 'pgsql:'.DNS;
        try {
            parent::__construct($db_dns);
            $locale = iconv_get_encoding('internal_encoding');
            parent::exec("set client_encoding = '$locale'");
        }
        catch (PDOException $ex) {
            throw new Exception($ex->getMessage());
        }
    }

	private function getMsg($resource) {
		$err = $resource->errorInfo();
		if (count($err) > 2)
			return $err[2];
		else
			return "";
	}  

	function initDatabase($name, $pwd, $uid) {
        $locale = "select pg_encoding_to_char(encoding) as encoding from pg_database where datname='template0'";
	    $usr = "create user $uid with encrypted password '$pwd'";
    	$init = "create database $name with encoding 'utf8' template template0 owner $uid";
		try {
            $sth = $this->prepare($locale);
            $sth->execute();
            $res = $sth->fetch();
            if (strcasecmp($res['encoding'], 'UTF8') !== 0)
                throw new Exception("Template0 encoding [".$res['encoding']."]. Must be UTF8");
			$this->exec($usr);
			$this->exec($init);
		}
		catch (PDOException $ex) {
            throw new Exception($ex->getMessage());
        }
	}

    function createDatabase($name) {
        $sql = "select count(*) as exist from pg_database where datname=?";
        $sth = $this->prepare($sql);
        $sth->execute(array($name));
        $res = $sth->fetch();
        if ($res['exist'] != 0) {
   			$this->beginTransaction();
            include_once 'db_create.postgresql.php';
            $db = make_sql_stm();
            foreach ($db as $sql) {
                $sth->closeCursor();
                $sth = $this->prepare($sql);
                if (! $sth) {
                    $this->rollBack();
                    $err = "$sql\n";
                    foreach ($this->errorInfo() as $info)
                        $err .= "$info\n";
                    throw new Exception($err);
                }
              	$sth->execute();
                //throw new Exception(var_export($sth, true));
                if ($sth->errorCode() && $sth->errorCode() != '00000') {
                    $this->rollBack();
                    $err = "$sql\n";
                    foreach ($this->errorInfo() as $info)
                        $err .= "$info\n";
                    throw new Exception($err);
                }
			}
            $this->commit();
        }
        else
            throw new Exception("$name: Database does not exist");
    }

    function getViewStyle($uid) {
        $sql = "select userview from users u where u.uid = ?";
        $sth = $this->prepare($sql);
        $sth->execute(array($uid));
        $res = $sth->fetch();
        return $res[0];
    }

    function getRole($uid) {
        $sql = "select userrole from users u where u.uid = ?";
        $sth = $this->prepare($sql);
        $sth->execute(array($uid));
        $res = $sth->fetch();
        return $res[0];
    }

    function getTimeout($uid) {
        $sql = "select timeout from users u where u.uid = ?";
        $sth = $this->prepare($sql);
        $sth->execute(array($uid));
        $res = $sth->fetch();
        return $res[0];
    }

    function getTimezone($uid) {
        $sql = "select timezone from users u where u.uid = ?";
        $sth = $this->prepare($sql);
        $sth->execute(array($uid));
        $res = $sth->fetch();
        return $res[0];
    }

    function getStartWeek($uid) {
        $sql = "select weekstart from users u where u.uid = ?";
        $sth = $this->prepare($sql);
        $sth->execute(array($uid));
        $res = $sth->fetch();
        return ($res[0]) ? 'MO' : 'SU';
    }

    function getStartHour($uid) {
        $sql = "select daystart from users u where u.uid = ?";
        $sth = $this->prepare($sql);
        $sth->execute(array($uid));
        $res = $sth->fetch();
        return $res[0];
    }

    function getEndHour($uid) {
        $sql = "select daystart from users u where u.uid = ?";
        $sth = $this->prepare($sql);
        $sth->execute(array($uid));
        $res = $sth->fetch();
        return $res[0];
    }

    function authenticate($uid, $pwd) {
        $res = array();
        $sql = "select userrole, timeout, userview, weekstart, daystart,
        dayend, timezone, seckey, pubkey, c.id as id, name, color, config
        from users u left join calendar c on c.uid = u.id where
        u.uid = ? and u.pwd = ?";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            throw new Exception($err);
        }
        $sth->execute(array($uid,$pwd));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            throw new Exception($err);
        }
        $res = $sth->fetchAll(PDO::FETCH_ASSOC);
        
        /*
        if ($res[0]['config']) {
            //$res[0]['config'] = base64_decode($res[0]['config']);
            //$res[0]['config'] = quoted_printable_decode($res[0]['config']);
            $res[0]['config'] = $res[0]['config'];
		}
		*/

        return $res;
    }

    function addUser($data) {
        //$fp = fopen('/tmp/davical.log', 'a');
        //fwrite($fp, "New user\nuid: {$data['uid']}\npwd: {$data['pwd']}\ntimezone: {$data['timezone']}\nuserrole: {$data['userrole']}\nseckey: {$data['seckey']}\npubkey: {$data['pubkey']}");
        //fclose($fp);
        $sql = "insert into users (uid, pwd, timezone, userrole, seckey, pubkey, " .
            "timeout, userview, weekstart, daystart, dayend) values (?,?,?,?,?,?,?,?,?,?,?)";
        $sth = $this->prepare($sql);
        if (! $sth) {
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $this->beginTransaction();
        $sth->execute(array($data['uid'],$data['pwd'],$data['timezone'],
            $data['userrole'],$data['seckey'],$data['pubkey'],
            $data['timeout'], $data['view'], ($data['week_start'] == false) ? 0 : 1,
            $data['start'], $data['end']));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $this->rollBack();
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $this->commit();
        return TRUE;
    }

    function setUserSettings($uid, $data) {
        $sql = "update users set userrole = ?, timeout = ?, userview = ?,
            weekstart = ?, daystart = ?, dayend = ?, timezone = ?
            where uid = ?";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $this->beginTransaction();
        $sth->execute(array($data['userrole'],$data['timeout'],
            $data['userview'],$data['weekstart'],$data['daystart'],
            $data['dayend'],$data['timezone'],$uid));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $this->rollBack();
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $this->commit();
        return TRUE;
    }

    function changeDefault($data) {
        $this->beginTransaction();
        foreach ($data as $column => $default) {
            $sql = "alter table users alter $column set default $default";
            //echo "$sql<br/>";
            $res = $this->exec($sql);
            if ($res === FALSE) {
                $err = "$sql\n";
                foreach ($this->errorInfo() as $info)
                    $err .= "$info\n";
                $this->rollBack();
                return $err;
            }
        }
        $this->commit();
        return TRUE;
    }

    function deleteUser($uid) {
        $sql = "delete from users where uid = ?";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $this->beginTransaction();
        $sth->execute(array($uid));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $this->rollBack();
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $this->commit();
        return TRUE;
    }

    function addCalendar($uid, CalendarInfo $cal) {
        //$fp = fopen('/tmp/add_calender.log', 'a');
        //fwrite($fp, "New calendar\nUID: $uid\nname: {$cal->name}\nColor: {$cal->color}\nConfig: {$cal->config}\n");
        $this->beginTransaction();
        $sql = "select id from users where uid = ?";
        //fwrite($fp, "Get ID: $sql\n");
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->execute(array($uid));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->bindColumn(1, $id);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->fetch(PDO::FETCH_BOUND);
        //fwrite($fp, "Returned users ID: $id\n");
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->closeCursor();
        $sql = "insert into calendar (uid, name, color, config)
            values ($id,?,?,?)";
        //var_dump($sql);
        //fwrite($fp, "Insert into calendar: $sql\n");
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        //$sth->execute(array($cal->name,$cal->color,base64_encode($cal->config)));
        $sth->execute(array($cal->name, $cal->color, $cal->config));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->closeCursor();
        $sth = $this->prepare('select max(id) from calendar');
        $sth->execute();
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->bindColumn(1, $id);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->fetch(PDO::FETCH_BOUND);
        //fwrite($fp, "Returned ID for created calendar: $id\n");
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->closeCursor();
        $this->commit();
/*        $sql = "select * from calendar where id = $id";
        $sth = $this->prepare($sql);
        $sth->execute();
        $result = $sth->fetchAll(PDO::FETCH_ASSOC);
        if ($result)
            fwrite($fp, var_export($result[0], TRUE) . "\n");
        else
            fwrite($fp, "No date found in calendar relation\n");
        fclose($fp);*/
        return $id;
    }

    function deleteCalendar($uid, $id) {
        $this->beginTransaction();
        $sql = "select id from users where uid = ?";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->execute(array($uid));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->bindColumn(1, $uid);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->fetch(PDO::FETCH_BOUND);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->closeCursor();
        $sql = "delete from calendar where id = ? and uid = $uid";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->execute(array($id));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $this->commit();
        return TRUE;
    }

    function updateCalendar($uid, $id, CalendarInfo $cal) {
        //echo "$uid:$id:".var_export($cal,true)."<br/>";
        $this->beginTransaction();
        $sql = "select id from users where uid = ?";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->execute(array($uid));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->bindColumn(1, $uid);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->fetch(PDO::FETCH_BOUND);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->closeCursor();
        $sql = "update calendar set name = ?, color = ?, config = ?
        where id = ? and uid = $uid";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        //$sth->execute(array($cal->name,$cal->color,base64_encode($cal->config),$id));
        $sth->execute(array($cal->name,$cal->color, $cal->config,$id));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $this->commit();
        return TRUE;
    }

    function changePassword($uid, $pwd) {
        $this->beginTransaction();
        $sql = "update users set pwd = ? where uid = ?";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $sth->execute(array($pwd,$uid));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $this->rollBack();
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $this->commit();
        return TRUE;
    }

    function getAllUsers($limit, $offset) {
		if ($limit == -1) {
        	$sql = "select u.uid, r.name as userrole from users u, roles r
        	where u.userrole = r.id limit all offset ?";
		}
		else {
        	$sql = "select u.uid, r.name as userrole from users u, roles r
        	where u.userrole = r.id limit ? offset ?";
		}
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        if ($limit == -1)
        	$sth->execute(array($offset));
        else
        	$sth->execute(array($limit,$offset));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }

    function getRoles() {
        $sql = "select id, name from roles order by id desc";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $sth->execute();
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }

    function getRoleName($id) {
        $sql = "select name from roles where id = ?";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $sth->execute(array($id));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        return $sth->fetch(PDO::FETCH_ASSOC);
    }

    function getVersion() {
        $version = array('version' => 0);
        $sql = "select count(*) as exist from pg_tables where tablename = 'about'";
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $sth->execute();
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $res = $sth->fetch();
        if ($res['exist'] > 0) {
            $sql = "select version from about where id = 1";
            $sth = $this->prepare($sql);
            if (! $sth) {
                $err = "$sql\n";
                foreach ($this->errorInfo() as $info)
                    $err .= "$info\n";
                return $err;
            }
            $sth->execute();
            if ($sth->errorCode() && $sth->errorCode() != '00000') {
                $err = "$sql\n";
                foreach ($this->errorInfo() as $info)
                    $err .= "$info\n";
                return $err;
            }
            $res = $sth->fetch();
            $version = $res;
        }
        return $version;
    }

    function execute($sql) {
        $sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $sth->execute();
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        return true;
    }

    function getCalendarConfig($id) {
		if ($id == -1) {
			$sql = "select id, config from calendar";
		}
		else {
			$sql = "select id, config from calendar where id = ?";
		}
        $sth = $this->prepare($sql);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
       		$err .= $this->getMsg($sth)."\n";
            return $err;
        }
        $sth->execute(array($id));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
       		$err .= $this->getMsg($sth)."\n";
            return $err;
        }
        return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	function nextTableNumber($name) {
		$sql = "select count(*) as found from pg_tables where tableowner = 'webcal' " .
		       "and tablename like ?";
		$sth = $this->prepare($sql);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
       		$err .= $this->getMsg($sth)."\n";
            return $err;
        }
        $sth->execute(array($name . '%'));
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
       		$err .= $this->getMsg($sth)."\n";
            return $err;
        }
        $res = $sth->fetch(PDO::FETCH_ASSOC);
        return $res['found'];
	}
	
    function getLdapConfig() {
        $sql = "select enable, dns, tls, base_dn, user_attr from ldap";
		$sth = $this->prepare($sql);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
       		$err .= $this->getMsg($sth)."\n";
            return $err;
        }
        $sth->execute();
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $err = "$sql\n";
       		$err .= $this->getMsg($sth)."\n";
            return $err;
        }
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    
    function setLdapConfig(array $config) {
       	$old_config = $this->getLdapConfig();
	    if (! is_array($old_config) && $old_config)
		    return $old_config;
        else if (is_array($old_config))
            $sql = "update ldap set enable=?, dns=?, tls=?, base_dn=?, user_attr=?";
        else
            $sql = "insert into ldap values(?, ?, ?, ?, ?)";
        $this->beginTransaction();
		$sth = $this->prepare($sql);
        if (! $sth) {
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            $this->rollBack();
            return $err;
        }
        $sth->execute($config);
        if ($sth->errorCode() && $sth->errorCode() != '00000') {
            $this->rollBack();
            $err = "$sql\n";
            foreach ($this->errorInfo() as $info)
                $err .= "$info\n";
            return $err;
        }
        $this->commit();
        return true;
    }

}