public function createUserSerialCode($personStatus)
{
$pdo = new DbConnection();
$pdo->conn = $pdo->open();
//SELECT LPAD(CONVERT(RIGHT(`code`, 4),UNSIGNED INTEGER),4,0) as newnumber FROM `person`
$sql = ' SELECT RIGHT((LEFT(CURDATE(),4)+543),2) as year_ad,';
// 58
$sql .= ' CASE status ';
$sql .= ' WHEN 1 THEN \'EMP\'';
$sql .= ' WHEN 2 THEN \'ONW\'';
$sql .= ' WHEN 3 THEN \'CUS\'';
$sql .= ' WHEN 4 THEN \'DRI\'';
$sql .= ' WHEN 0 THEN \'GEN\'';
$sql .= ' ELSE \'ERR\'';
$sql .= ' END prefix_status,';
$sql .= ' LEFT(`code`,3) as prefix,';
// DRI,EMP,CUS,ONW
$sql .= ' LPAD(CONVERT(RIGHT(`code`, 4),UNSIGNED INTEGER)+1,4,0) as new_runnumber,';
$sql .= ' RIGHT(`code`, 4) as runnumber';
$sql .= ' FROM user WHERE status =:status';
$sql .= ' ORDER BY RIGHT(`code`, 4) DESC LIMIT 0,1 ';
//echo 'sql ::=='.$sql;
$stmt = $pdo->conn->prepare($sql);
$stmt->execute(array(':status' => $personStatus));
$result = $stmt->fetch(PDO::FETCH_OBJ);
if (empty($result)) {
return '';
}
$prefix = $result->prefix;
$runnumber = $result->runnumber;
$year_ad = $result->year_ad;
$newrunnumber = $result->new_runnumber;
return $prefix . $year_ad . $newrunnumber;
}
/**
* Provides teams assigned to specified cup group in their standings order.
*
* @param WebSoccer $websoccer application context.
* @param DbConnection $db DB connection.
* @param int $roundId Cup round ID.
* @param string $groupName Cup round group name.
* @return array Array of teams with standings related statistics.
*/
public static function getTeamsOfCupGroupInRankingOrder(WebSoccer $websoccer, DbConnection $db, $roundId, $groupName)
{
$fromTable = $websoccer->getConfig("db_prefix") . "_cup_round_group AS G";
$fromTable .= " INNER JOIN " . $websoccer->getConfig("db_prefix") . "_verein AS T ON T.id = G.team_id";
$fromTable .= " LEFT JOIN " . $websoccer->getConfig("db_prefix") . "_user AS U ON U.id = T.user_id";
// where
$whereCondition = "G.cup_round_id = %d AND G.name = '%s'";
// order (do not use "Direktvergleich", but compare total score so far)
$whereCondition .= "ORDER BY G.tab_points DESC, (G.tab_goals - G.tab_goalsreceived) DESC, G.tab_wins DESC, T.st_punkte DESC";
$parameters = array($roundId, $groupName);
// select
$columns["T.id"] = "id";
$columns["T.name"] = "name";
$columns["T.user_id"] = "user_id";
$columns["U.nick"] = "user_name";
$columns["G.tab_points"] = "score";
$columns["G.tab_goals"] = "goals";
$columns["G.tab_goalsreceived"] = "goals_received";
$columns["G.tab_wins"] = "wins";
$columns["G.tab_draws"] = "draws";
$columns["G.tab_losses"] = "defeats";
$result = $db->querySelect($columns, $fromTable, $whereCondition, $parameters);
$teams = array();
while ($team = $result->fetch_array()) {
$teams[] = $team;
}
$result->free();
return $teams;
}
public function checkUser($login, $passwd)
{
try {
$connection = new DbConnection();
$pdo = $connection->connect();
} catch (ErrorException $e) {
$еггог = 'crap3';
include '../layouts/Error.html.php';
exit;
}
try {
$sql = 'SELECT id FROM users WHERE login = :login';
$s = $pdo->prepare($sql);
$s->bindValue(':login', $login);
$s->execute();
} catch (PDOException $e) {
$еггог = 'crap4';
include '../layouts/Error.html.php';
exit;
}
if ($s->rowCount() > 0) {
// $_SESSION['user'] = $username;
// echo 2;
return true;
} else {
return false;
}
}
/**
* Creates badge assignment.
*
* @param WebSoccer $websoccer Application context.
* @param DbConnection $db DB connection.
* @param int $userId ID of user.
* @param int $badgeId ID od badge.
*/
public static function awardBadge(WebSoccer $websoccer, DbConnection $db, $userId, $badgeId)
{
$badgeUserTable = $websoccer->getConfig('db_prefix') . '_badge_user';
// create assignment
$db->queryInsert(array('user_id' => $userId, 'badge_id' => $badgeId, 'date_rewarded' => $websoccer->getNowAsTimestamp()), $badgeUserTable);
// notify lucky user
NotificationsDataService::createNotification($websoccer, $db, $userId, 'badge_notification', null, 'badge', 'user', 'id=' . $userId);
}
public static function Add($category_name)
{
$con = new DbConnection();
$query = "INSERT INTO forum_categories (category_name) VALUES (?)";
$st = $con->prepare($query);
$st->bind_param("s", $category_name);
$st->execute();
$con->close();
}
public static function getCampById(WebSoccer $websoccer, DbConnection $db, $campId)
{
$fromTable = $websoccer->getConfig("db_prefix") . "_trainingslager";
// where
$whereCondition = "id = %d";
$result = $db->querySelect(self::_getColumns(), $fromTable, $whereCondition, $campId);
$camp = $result->fetch_array();
$result->free();
return $camp;
}
/**
* Provide total number of leagues.
*
* @param WebSoccer $websoccer Application context.
* @param DbConnection $db DB connection.
* @return int total number of leagues.
*/
public static function countTotalLeagues(WebSoccer $websoccer, DbConnection $db)
{
$result = $db->querySelect("COUNT(*) AS hits", $websoccer->getConfig("db_prefix") . "_liga", "1=1");
$leagues = $result->fetch_array();
$result->free();
if (isset($leagues["hits"])) {
return $leagues["hits"];
}
return 0;
}
/**
* Marks user as absent, makes his teams managable by deputy and sends deputy notification about it.
*
* @param WebSoccer $websoccer Application context.
* @param DbConnection $db DB connection.
* @param int $userId ID of user who is absent.
* @param int $deputyId ID of user's deputy during absence.
* @param int $days Number of days to be absent.
*/
public static function makeUserAbsent(WebSoccer $websoccer, DbConnection $db, $userId, $deputyId, $days)
{
// create absence record
$fromDate = $websoccer->getNowAsTimestamp();
$toDate = $fromDate + 24 * 3600 * $days;
$db->queryInsert(array('user_id' => $userId, 'deputy_id' => $deputyId, 'from_date' => $fromDate, 'to_date' => $toDate), $websoccer->getConfig('db_prefix') . '_userabsence');
// update manager reference of managed teams
$db->queryUpdate(array('user_id' => $deputyId, 'user_id_actual' => $userId), $websoccer->getConfig('db_prefix') . '_verein', 'user_id = %d', $userId);
// create notification for deputy
$user = UsersDataService::getUserById($websoccer, $db, $userId);
NotificationsDataService::createNotification($websoccer, $db, $deputyId, 'absence_notification', array('until' => $toDate, 'user' => $user['nick']), 'absence', 'user');
}
public function __Construct($dbHost, $dbUser, $dbPwd, $dbName, $Date, $fromDate, $toDate)
{
$Db = new DbConnection($dbHost, $dbUser, $dbPwd, $dbName);
$this->DB = $Db->SelectDb();
if (!empty($toDate)) {
$this->FromDate = gmdate($fromDate);
$this->ToDate = gmdate($toDate);
} else {
$this->Dte = gmdate($Date);
//echo $this->Dte;
}
}
/**
* @param array $rawData
* @param string $className
* @param DbConnection $connection
* @param App $app
* @throws \Exception
* @return Model
*/
private function hydrateData(array $rawData, $className, DbConnection $connection, App $app)
{
$hydrator = new \ReflectionClass($className);
$model = new $className();
$propertiesMetadata = static::getPropertiesMetadata($className);
foreach ($propertiesMetadata as $property => $meta) {
$p = $hydrator->getProperty($property);
$p->setAccessible(true);
if (isset($meta['ORM_Column'])) {
if ($meta['ORM_Type'] == 'date') {
$p->setValue($model, new \DateTime($rawData[$meta['ORM_Column']]));
} else {
$p->setValue($model, $rawData[$meta['ORM_Column']]);
}
} elseif (isset($meta['ORM_ManyToOne'])) {
$_data = preg_split('/[\\s]*,[\\s]*/', $meta['ORM_ManyToOne']);
$data = [];
foreach ($_data as $param) {
preg_match_all('/([\\w]+)[\\s]*=[\\s]*"(.+?)"/s', $param, $matches);
$data[$matches[1][0]] = $matches[2][0];
}
$mappedByValue = $rawData[$data['mappedBy']];
$data['mappedByValue'] = $mappedByValue;
$data['property'] = $p->getName();
$p->setValue($model, new ManyToOneRelationModel($model, $data, $app));
} elseif (isset($meta['ORM_OneToMany'])) {
$_data = preg_split('/[\\s]*,[\\s]*/', $meta['ORM_OneToMany']);
$data = [];
foreach ($_data as $param) {
preg_match_all('/([\\w]+)[\\s]*=[\\s]*"(.+?)"/s', $param, $matches);
$data[$matches[1][0]] = $matches[2][0];
}
$targetClass = 'Src\\Models\\' . $data['targetClass'];
$targetTable = static::getClassAnnotation($targetClass)['ORM_Table'];
$inversedBy = $data['inversedBy'];
$primaryColumn = static::findPrimaryColumn($className);
$id = $rawData[$primaryColumn];
$rawArray = $connection->select(['*'], [$targetTable], ':' . $inversedBy . '=' . $id);
$models = [];
foreach ($rawArray as $row) {
$models[] = $this->hydrateData($row, $targetClass, $connection, $app);
}
$p->setValue($model, $models);
}
$p->setAccessible(false);
}
$ep = $hydrator->getProperty('_exists');
$ep->setAccessible(true);
$ep->setValue($model, true);
$ep->setAccessible(false);
return $model;
}
private function send($values)
{
$db = new DbConnection();
$id = $db->store("0", $this->normalize($values), $this->ip);
if (!$id instanceof Error) {
$this->response["success"] = true;
$this->response["id"] = $id;
$this->response["message"] = $this->normalize($values);
} else {
$this->response["success"] = false;
$this->response["error"] = $id;
}
}
public static function Update($setting)
{
if (!$setting->isNull()) {
$s_value = $setting->value;
$s_id = $setting->id;
$con = new DbConnection();
$query = "UPDATE settings SET setting_value = ? WHERE setting_id = ?";
$st = $con->prepare($query);
$st->bind_param("si", $s_value, $s_id);
$st->execute();
$con->close();
}
}
/**
*
* @param WebSoccer $websoccer request context.
* @param DbConnection $db database connection.
*/
function __construct(WebSoccer $websoccer, DbConnection $db)
{
$this->_availableTexts = array();
$this->_websoccer = $websoccer;
$this->_db = $db;
// get available text messages
$fromTable = $websoccer->getConfig('db_prefix') . '_spiel_text';
$columns = 'id, aktion AS actiontype';
$result = $db->querySelect($columns, $fromTable, '1=1');
while ($text = $result->fetch_array()) {
$this->_availableTexts[$text['actiontype']][] = $text['id'];
}
$result->free();
}
public static function createDatabaseSchema($schemaName)
{
$dbConnection = new DbConnection(NULL);
$mySQLi = $dbConnection->getMySQLi();
$queryResult = $mySQLi->query("DROP DATABASE IF EXISTS `{$schemaName}`");
if ($queryResult === FALSE) {
throw new Exception("Error creating database '{$schemaName}' - " . $mySQLi->error);
}
$queryResult = $mySQLi->query("CREATE DATABASE IF NOT EXISTS `{$schemaName}`");
if ($queryResult === FALSE) {
throw new Exception("Error creating database '{$schemaName}' - " . $mySQLi->error);
}
$queryResult->close();
$dbConnection->close();
}
/**
* Singleton method
* @return DbConnection
*/
public static function getInstance()
{
if (self::$instance == null) {
self::$instance = new self(DB_HOST, DB_USER, DB_PASS, DB_NAME);
}
return self::$instance;
}
/**
*
* @param WebSoccer $websoccer request context.
* @param DbConnection $db database connection.
*/
function __construct(WebSoccer $websoccer, DbConnection $db)
{
$this->_availableTexts = array();
$this->_websoccer = $websoccer;
$this->_db = $db;
// get available text messages
$fromTable = $websoccer->getConfig('db_prefix') . '_spiel_text';
$columns = 'id, aktion AS actiontype';
// only load text messages for substitutions, because this observer does not observes anything else
$whereCondition = 'aktion = \'Auswechslung\'';
$result = $db->querySelect($columns, $fromTable, $whereCondition);
while ($text = $result->fetch_array()) {
$this->_availableTexts[$text['actiontype']][] = $text['id'];
}
$result->free();
}
public function createNewObject(array $params)
{
if (empty($params)) {
throw new \Exception('Empty params passed to create the object');
}
//todo - pass the params to sub handler and it will take care of this
$objectTypeId = $params[StorouConstants::KEY_OBJECT_TYPE_ID_INT];
$title = $params[StorouConstants::KEY_TITLE_STRING];
$content = $params[StorouConstants::KEY_CONTENT_STRING];
$createdBy = $params[StorouConstants::KEY_CREATED_BY_INT];
$createdOn = $params[StorouConstants::KEY_CREATED_ON_DATE];
$updatedOn = $params[StorouConstants::KEY_UPDATED_ON_DATE];
$status = $params[StorouConstants::KEY_STATUS_INT];
$parentObjectId = isset($params[StorouConstants::KEY_PARENT_OBJECT_ID_INT]) ? $params[StorouConstants::KEY_PARENT_OBJECT_ID_INT] : 0;
$query = 'INSERT INTO objects (objectTypeId,title,content,createdBy,createdOn,createdOn,parentObjectId,status) VALUES (?,?,?,?,?,?)';
$params = array($objectTypeId, $title, $content, $createdBy, $createdOn, $updatedOn, $parentObjectId, $status);
if ($parentObjectId > 0) {
$query = 'INSERT INTO objects (objectTypeId,title,content,createdBy,createdOn,status,parentObjectId) VALUES (?,?,?,?,?)';
$params[] = $parentObjectId;
}
$objId = DbConnection::getConnection()->insertUpdateData($query, $params);
/*
* TODO: Update revisions
*/
return $objId;
}
protected function _load()
{
$connection = DbConnection::getInstance()->getConnection();
$sth = $connection->query("SELECT * FROM {$this->_tableName}");
$sth->execute();
$this->_itemsData = $sth->fetchAll();
}
public function getRows()
{
if (!isset($this->_rows)) {
$this->_rows = $this->_DbConnection->getAllRows($this->_sql);
}
return $this->_rows;
}
public static function getInstance()
{
if (is_null(self::$instance)) {
self::$instance = new DbConnection();
}
return self::$instance;
}
public static function getInstance()
{
if (is_null(self::$instance)) {
self::$instance = new DbConnection('localhost', 'root', '', 'my_site');
}
return self::$instance;
}
public static function getConnection()
{
if (null === self::$connection) {
self::$connection = self::createConnection();
}
return self::$connection;
}
public function callData()
{
parent::conn();
if (isset($this->LoginId) && $this->LoginPassword) {
$sql = "SELECT * FROM admin WHERE ID=('{$this->LoginId}')&& PASSWORD=('{$this->LoginPassword}')";
$result = mysqli_query($this->con, $sql);
if ($result === FALSE) {
echo "error in query";
die(mysql_error());
// TODO: better error handling
}
while ($row = mysqli_fetch_array($result)) {
$this->Logid = $row['ID'];
$this->Logpwd = $row['PASSWORD'];
echo $this->Logid . "<br>";
}
if ($this->LoginId == $this->Logid && $this->LoginPassword == $this->Logpwd) {
header("Location:Admin_module.html");
} else {
echo " Please Enter Valid ID and Password <br> " . "<a href=A_login.html>Click here </a>" . " to Login Again";
}
} else {
echo " Please Enter Valid ID and Password <br> " . "<a href=A_login.html>Click here </a>" . " to Login Again";
}
}
public static function getInstance()
{
if (!self::$_instance instanceof self) {
self::$_instance = new self();
}
return self::$_instance;
}
public static function getInstance()
{
if (is_null(self::$instance)) {
self::$instance = new DbConnection(DB_URL, DB_USER, DB_PWD, DB_NAME);
}
return self::$instance;
}
/**
* @return the only instance during current request.
*/
public static function getInstance()
{
if (self::$_instance == NULL) {
self::$_instance = new DbConnection();
}
return self::$_instance;
}
public static function getInstance()
{
if (!isset(self::$_instance)) {
self::$_instance = new DbConnection();
}
return self::$_instance;
}
/**
* Deletes the row on the database, if it violates referential-integrity as
* defined at database level it will simply return false constraints.
* @return boolean
*/
public function delete()
{
$sql = "DELETE FROM {$this->_tableName}\n WHERE {$this->_idField}={$this->_id}\n LIMIT 1;";
if (!$this->_DbConnection->execute($sql)) {
return false;
}
return true;
}
public static function GetAll()
{
$types = array();
$con = new DbConnection();
$query = "SELECT type_id, type_name FROM server_types";
$st = $con->prepare($query);
$st->bind_result($t_id, $t_name);
$st->execute();
while ($st->fetch()) {
$type = new ServerType();
$type->id = $t_id;
$type->name = $t_name;
$types[] = $type;
}
$con->close();
return $types;
}
public function getBatchCount()
{
$pdo = DbConnection::getInstance()->getConnection($this->databaseName);
$sql = 'select count(*) as batchCount from mock_batches where institute_id = ' . $this->organizationId;
$prepped = $pdo->query($sql);
$row = $prepped->fetch(PDO::FETCH_ASSOC);
return $row["batchCount"];
}
