public function create()
{
$key = $this->getPrimary();
//if it does exists, then update, else insert
if ($this->exists()) {
$this->update();
return $this->{$key};
} else {
#####################################
$columnstmp = $this->getColumns();
foreach ($columnstmp as $colname) {
$columns[$colname] = $this->{$colname};
}
$table = $this->getTable();
$qmarks = array_fill(0, count($columns), '?');
$sql = "INSERT INTO {$table} (" . join(',', array_keys($columns)) . ") VALUES(" . join(',', $qmarks) . ")";
$columns = array_values($columns);
//values must be integer indexed array
$database = SomeFactory::getDBO();
$statement = $database->prepare($sql);
$this->sql = $sql;
$success = $statement->execute($columns);
ob_start();
print_r($columns);
$this->columnsdebug = ob_get_clean();
return $this->{$key};
#####################################
}
}
/**
* @return true if user is created, false if not.
*/
public function create()
{
$user = SomeFactory::getUser();
if ($user->getUserrole() === SomeUser::ROLE_HEADTEACHER) {
someloader('some.user.user');
$someuser = new SomeUser();
$this->userdata = array('username' => SomeRequest::getVar('tunnus', ''), 'fname' => SomeRequest::getVar('etunimi', ''), 'lname' => SomeRequest::getVar('sukunimi', ''), 'unit' => SomeRequest::getVar('yksikko', ''), 'email' => SomeRequest::getVar('sposti', ''), 'phone' => SomeRequest::getVar('puh', ''), 'password' => SomeRequest::getVar('salasana', ''));
$someuser->setUsername($this->userdata['username']);
// DO THE PASSWORD HASHING HERE
$someuser->setPassword($this->userdata['password']);
$someuser->setUserrole('teacher');
$this->userdata['userrole'] = $someuser->getUserrole();
$someuser->create();
//Yritetään lisäksi tehdä uusi tuutori
$db = SomeFactory::getDBO();
$stmt = $db->prepare("INSERT INTO tuutori VALUES(?, ?, ?, ?, ?, ?, 'Tuutori')");
$ok = $stmt->execute(array($this->userdata['username'], $this->userdata['fname'], $this->userdata['lname'], $this->userdata['email'], $this->userdata['phone'], $this->userdata['unit']));
if ($someuser->getId() > 0 && $ok > 0) {
$this->userdata['id'] = $someuser->getId();
return true;
} else {
return false;
}
}
}
protected function loadAllUsers()
{
$sql = "SELECT * FROM someuser";
$database = SomeFactory::getDBO();
$result = $database->query($sql);
$this->users = $result->fetchAll(PDO::FETCH_ASSOC);
}
public function login()
{
$username = SomeRequest::getVar('username', null);
$password = SomeRequest::getVar('password', null);
// IF PASSWORD IS HASHED and optionally SALTED
// only load the user and check password match in the php code
$sql = "SELECT * FROM someuser WHERE username=? and password=?";
$database = SomeFactory::getDBO();
$stmt = $database->prepare($sql);
$ok = $stmt->execute(array($username, $password));
if ($ok) {
$row = $stmt->fetch();
if ($row['id']) {
//
$this->userdata = $row;
$user = SomeFactory::getUser();
$user->setId($row['id']);
$user->setUsername(trim($row['username']));
$user->setUserrole(trim($row['userrole']));
$user->setEmail(trim($row['email']));
$user->setHomepage(trim($row['homepage']));
return true;
} else {
echo "Käyttäjää ei löytynyt";
$this->errors['notfound'] = "user {$username} not found from database. Check username and password";
}
}
return false;
}
public function getNameData()
{
$namedata;
$user = SomeFactory::getUser();
$db = SomeFactory::getDBO();
$stmt = null;
//Jos kirjautunut ja opiskelija
if ($user->getId() && $user->getUserrole() === 'student') {
$statement = $db->prepare("SELECT etunimi, sukunimi FROM opiskelija WHERE opnro=?");
$ok = $statement->execute(array($user->getUsername()));
if ($ok) {
$namedata = $statement->fetch(PDO::FETCH_ASSOC);
}
} else {
if ($user->getId() && $user->getUserrole() === 'teacher' || $user->getId() && $user->getUserrole() === 'headteacher') {
$statement = $db->prepare("SELECT etunimi, sukunimi FROM tuutori WHERE tunnus=?");
$ok = $statement->execute(array($user->getUsername()));
if ($ok) {
$namedata = $statement->fetch(PDO::FETCH_ASSOC);
}
} else {
echo "You do not have permission!!!!";
}
}
return $namedata;
}
/**
* @return SomeSession instance
*/
public static function getSession()
{
someloader('some.session.session');
$conf = SomeFactory::getConfiguration();
$session_handler = $conf->get('session_handler', 'session');
//only on postgres
if ($session_handler !== 'file' && $conf->get('databasedriver', 'database') === 'pdopostgres') {
try {
$database = SomeFactory::getDBO();
if (!$database) {
$session_handler = 'file';
} else {
$session_table = $conf->get('session_table', 'session');
$sql = "select * from information_schema.tables where table_schema='public' " . "and table_type='BASE TABLE' AND table_name='{$session_table}'";
$st = $database->query($sql);
if (!$st->fetch()) {
var_dump($database->errorInfo());
echo "THERE IS NOT SESSION TABLE somesession WILL USE FILE AS SESSION STORAGE. Change configuration.xml";
$session_handler = 'file';
}
}
} catch (Exception $e) {
$session_handler = 'file';
}
//echo $session_handler;
}
return SomeSession::getInstance($session_handler);
}
function gc($lifetime = 1440)
{
// Get the database connection object and verify its connected.
$database = SomeFactory::getDBO();
// Determine the timestamp threshold with which to purge old sessions.
$past = time() - $lifetime;
// Remove expired sessions from the database.
$database->query('DELETE FROM somesession' . ' WHERE `expiry` < ' . (int) $past);
return true;
}
/**
* delete row.
*
* @return boolean
*/
public function delete()
{
$sql = "DELETE FROM " . $this->getTable() . " WHERE " . $this->getPrimary() . '=?';
$database = SomeFactory::getDBO();
$statement = $database->prepare($sql);
$key = $this->getPrimary();
return $statement->execute(array($this->{$key}));
$this->sql = $sql;
return true;
}
public function delete()
{
//VielŠ kerran varmistus kŠyttŠjŠn oikeuksista
$user = SomeFactory::getUser();
if ($user->getUserrole() === SomeUser::ROLE_HEADTEACHER) {
//On oikeudet. Poistetaan ensin kŠyttŠjŠ someuser-taulusta
$someuser = new SomeUser();
$tunnus = SomeRequest::getVar('tunnus', '');
$db = SomeFactory::getDBO();
$stmt = $db->prepare("SELECT id FROM someuser WHERE username=?");
$ok = $stmt->execute(array($tunnus));
$id = $stmt->fetch(PDO::FETCH_ASSOC);
if ($id) {
//Saatiin id, voidaan poistaa kŠyttŠjŠ
$someuser->setId($id['id']);
$ryhmat;
$stmt = $db->prepare("SELECT tunnus FROM hops_ryhma WHERE tuutori = ?");
$ok = $stmt->execute(array($tunnus));
if ($ok) {
$i = 0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$ryhmat[$i++] = $row;
}
}
var_dump($ryhmat);
$i = 0;
if ($ryhmat != null) {
foreach ($ryhmat as $ryhma) {
$stmt = $db->prepare("UPDATE opiskelija SET hopsryhma = null WHERE hopsryhma = ?");
$ok = $stmt->execute(array($ryhma['tunnus']));
$stmt = $db->prepare("DELETE FROM hops_ryhma WHERE tunnus = ?");
$ok = $stmt->execute(array($ryhma['tunnus']));
if ($ok) {
echo "RyhmŠn poisto onnistui";
}
}
}
//Poistetaan myšs itse tuutori omasta taulustaan
$stmt = $db->prepare("DELETE FROM tuutori WHERE tunnus=?");
$ok = $stmt->execute(array($tunnus));
if ($ok) {
$someuser->delete();
return true;
}
} else {
return false;
}
} else {
return false;
}
}
public function createSessionTable()
{
$sql = "\n\t\t CREATE TABLE somesession (\n sesskey character(32) NOT NULL,\n expiry integer NOT NULL,\n value text\n)";
$database = SomeFactory::getDBO();
$database->query($sql);
}
public function getConnected()
{
if ($this->connected !== null) {
return $this->connected;
}
// if not valid database configuration, return false;
if (!$this->isDatabaseFilled()) {
$this->connected = false;
return false;
}
try {
$database = SomeFactory::getDBO();
//
if (!$database) {
$this->connected = false;
return false;
}
} catch (Exception $e) {
$this->errors['database'] = $e->getMessage();
$this->connected = false;
return false;
}
$database = SomeFactory::getDBO();
$st = $database->query("SELECT NOW() as now");
if ($st->fetch()) {
$this->connected = true;
return true;
} else {
$this->errors[] = "Can not connect to database with {$driver}, {$host} and {$database}";
$this->connected = false;
return false;
}
}
/**
* create someuser table to mysql
*/
public function installSomeUserTable()
{
$sql = "CREATE TABLE IF NOT EXISTS `someuser` (\n `id` INT NOT NULL AUTO_INCREMENT,\n `username` varchar(32) DEFAULT NULL,\n `password` char(32) DEFAULT NULL,\n `userrole` char(32) DEFAULT NULL,\n `email` text,\n `homepage` text,\n UNIQUE KEY `id` (`id`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=1";
$database = SomeFactory::getDBO();
return $database->query($sql);
}
public function saveEndForm()
{
$data = $_POST;
$user = SomeFactory::getUser();
$db = SomeFactory::getDBO();
$stmt = null;
if ($user->getId() && $user->getUserrole() === 'teacher') {
foreach ($data['ryhmat'] as $tiedot) {
$stmt = $db->prepare("UPDATE loppuraportit \n\t SET alkup_koko = ?, palautetut = ?, osallistuneet_ryhma = ?, osallistuneet_yks = ?, tavoittamattomat = ?, poisjaaneet = ?, i = ?, ii = ?, iii = ?, iv = ?, v = ?\n\t WHERE tuutori = '" . $user->getUsername() . "' AND hopsryhma = '" . $tiedot['tunnus'] . "'");
$ok = $stmt->execute(array($tiedot['alkup_koko'], $tiedot['pal_hopsit'], $tiedot['osallistuneet'], $tiedot['yks_tapaamiset'], $tiedot['tavoittamattomat'], $tiedot['poissa'], $tiedot['i'], $tiedot['ii'], $tiedot['iii'], $tiedot['iv'], $tiedot['v']));
$stmt = $db->prepare("INSERT INTO loppuraportit VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ok = $stmt->execute(array($user->getUsername(), $tiedot['tunnus'], $tiedot['alkup_koko'], $tiedot['pal_hopsit'], $tiedot['osallistuneet'], $tiedot['yks_tapaamiset'], $tiedot['tavoittamattomat'], $tiedot['poissa'], $tiedot['i'], $tiedot['ii'], $tiedot['iii'], $tiedot['iv'], $tiedot['v']));
}
}
}
public function create()
{
//Tarkistetaan, ollaanko ylituutori
$user = SomeFactory::getUser();
if ($user->getUserrole() === SomeUser::ROLE_HEADTEACHER) {
//Ollaan. Halutaan luoda uusi ryhmä.
//Haetaan oleelliset muuttujat post-variablesta
//uuden ryhmän tunnus
$ryhma_tunnus = SomeRequest::getVar('tunnus', '');
//Ryhmän tuutorin tunnus
$tuutori_tunnus = SomeRequest::getVar('tuutori_tunnus', '');
if (!empty($ryhma_tunnus) && !empty($tuutori_tunnus)) {
//Saatiin jotain, luodaan uusi ryhmä
$db = SomeFactory::getDBO();
$stmt = $db->prepare("INSERT INTO hops_ryhma VALUES(?,?)");
$ok = $stmt->execute(array($ryhma_tunnus, $tuutori_tunnus));
if ($ok) {
return true;
} else {
return false;
}
} else {
return false;
}
} else {
return false;
}
}
/**
* create someuser table to postgres
*/
public function installSomeUserTable()
{
$sql = "\n\t\tCREATE TABLE someuser (\n id SERIAL,\n username character varying(32),\n \"password\" character(32),\n userrole character(32),\n email text,\n homepage text\n\t)\n\t\t";
$database = SomeFactory::getDBO();
$database->query($sql);
}
public function getCourseData($year)
{
$coursedata;
$tunnus = null;
$user = SomeFactory::getUser();
if ($user) {
$tunnus = $user->getUsername();
} else {
$this->data = array("Kukaan ei ole kirjautunut!!");
return false;
}
$db = SomeFactory::getDBO();
//Tässä kohtaa lasketaan minkä vuosien kurssisuorituksia haetaan.
$syksylisa = 0;
$kevatlisa = 0;
if ($year == 1) {
$kevatlisa = 1;
} else {
if ($year == 2) {
$syksylisa = 1;
$kevatlisa = 2;
} else {
if ($year == 3) {
$syksylisa = 2;
$kevatlisa = 3;
}
}
}
$statement = $db->prepare("SELECT k.tunnus, k.nimi, k.op, os.kausi FROM kurssi as k JOIN on_suorittanut as os ON k.tunnus = os.tunnus JOIN opiskelija as o ON o.opnro = os.opnro WHERE os.opnro=? AND ((os.vuosi = o.avuosi+? AND os.kausi ='Syksy') OR (os.vuosi=o.avuosi+? AND os.kausi='Kevät'))");
$ok = $statement->execute(array($tunnus, $syksylisa, $kevatlisa));
if ($ok) {
$i = 1;
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
$coursedata[$i++] = $row;
}
}
return $coursedata;
}
