/**
* Funktion zum endgültigem Löschen von Produkten
* @param type $id
* @param type $db
*/
function deleteProduct($id, $db)
{
$user_id = $_SESSION['user_id'];
//debug($id);
try {
$db->beginTransaction();
$stmt = $db->prepare('SELECT ' . 'products.product_image_path ' . 'FROM products ' . 'WHERE products.product_id = ?');
$stmt->execute(array($id));
$image_path = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt = $db->prepare('DELETE ' . 'FROM package_has_products ' . 'WHERE package_has_products.product_id = ? ' . 'AND package_has_products.user_id = ?');
$stmt->execute(array($id, $user_id));
$stmt = $db->prepare('DELETE ' . 'FROM products ' . 'WHERE products.product_id = ?');
$stmt->execute(array($id));
$stmt = $db->prepare('DELETE ' . 'FROM product_has_category ' . 'WHERE product_has_category.product_id = ?');
$stmt->execute(array($id));
$stmt = $db->prepare('SELECT ' . 'product_has_properties.properties_id ' . 'FROM product_has_properties ' . 'WHERE product_has_properties.product_id = ?');
$stmt->execute(array($id));
$properties = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($properties as $key => $property) {
$stmt = $db->prepare('DELETE ' . 'FROM properties ' . 'WHERE property_id = ?');
$stmt->execute(array($property['properties_id']));
}
$stmt = $db->prepare('DELETE ' . 'FROM product_has_properties ' . 'WHERE product_has_properties.product_id = ?');
$stmt->execute(array($id));
$stmt = $db->prepare('DELETE ' . 'FROM product_has_subcategory ' . 'WHERE product_has_subcategory.product_id = ?');
$stmt->execute(array($id));
$stmt = $db->prepare('DELETE ' . 'FROM user_has_products ' . 'WHERE user_has_products.product_id = ? ' . 'AND user_has_products.user_id = ?');
$stmt->execute(array($id, $user_id));
$db->commit();
} catch (PDOException $e) {
$db->rollBack();
$e->getMessage();
}
if (empty($e)) {
$img = '';
if (is_dir($image_path['product_image_path']) && ($pp = opendir($image_path['product_image_path']))) {
while (($file = readdir($pp)) !== false) {
if ($file != "." && $file != "..") {
$img[] = $file;
}
}
closedir($pp);
}
foreach ($img as $key => $image) {
unlink($image_path['product_image_path'] . $image);
}
rmdir($image_path['product_image_path']);
return true;
} else {
//var_dump($e);
return false;
}
}
/**
* Funktion zum laden von ähnlichen Artikeln für die Detailseite
* @param type $id ProduktID
* @param type $category KategorieID
* @param type $subcategory UnterkategorieID
* @param type $db Datenbankobject
* @return type Array / Bool
*/
function getSimilarProducts($productId, $userId, $category, $subcategory, $db)
{
$actualdate = date('Y-m-d H:i:s');
$stmt = $db->prepare('SELECT ' . 'product_has_subcategory.product_id, ' . 'product_has_category.product_id, ' . 'products.product_name, ' . 'products.product_description, ' . 'products.product_google_description, ' . 'products.product_image_path, ' . 'products.product_price, ' . 'products.product_start, ' . 'products.product_end, ' . 'products.product_status, ' . 'subcategories.subcategory_id, ' . 'subcategories.subcategory_name, ' . 'categories.category_id, ' . 'categories.category_name ' . 'FROM product_has_subcategory ' . 'JOIN product_has_category ON product_has_category.product_id = product_has_subcategory.product_id ' . 'JOIN categories ON categories.category_id = product_has_category.category_id ' . 'JOIN subcategories ON subcategories.subcategory_id = product_has_subcategory.subcategory_id ' . 'JOIN products ON products.product_id = product_has_subcategory.product_id ' . 'JOIN user_has_products ON products.product_id = user_has_products.product_id ' . 'WHERE product_has_subcategory.subcategory_id = ' . $subcategory . ' ' . 'AND product_has_subcategory.product_id != ' . $productId . ' ' . 'AND user_has_products.user_id = ' . $userId . ' ' . 'AND products.product_status = 1');
$stmt->execute();
$subcategoryResult = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($subcategoryResult as $key => $res) {
$dateStart = explode(' ', $res['product_start']);
$dateEnd = explode(' ', $res['product_end']);
$dateStart[0] = explode('-', $dateStart[0]);
$dateStart[1] = explode(':', $dateStart[1]);
$dateEnd[0] = explode('-', $dateEnd[0]);
$dateEnd[1] = explode(':', $dateEnd[1]);
$stmpStart = mktime($dateStart[1][0], $dateStart[1][1], $dateStart[1][2], $dateStart[0][1], $dateStart[0][2], $dateStart[0][0]);
$stmpEnd = mktime($dateEnd[1][0], $dateEnd[1][1], $dateEnd[1][2], $dateEnd[0][1], $dateEnd[0][2], $dateEnd[0][0]);
$subcategoryResult[$key]['product_timestamp_start'] = $stmpStart;
$subcategoryResult[$key]['product_timestamp_end'] = $stmpEnd;
}
if (empty($subcategoryResult)) {
$stmt = $db->prepare('SELECT ' . 'product_has_category.product_id, ' . 'product_has_subcategory.product_id, ' . 'products.product_name, ' . 'products.product_description, ' . 'products.product_google_description, ' . 'products.product_image_path, ' . 'products.product_price, ' . 'products.product_start, ' . 'products.product_end, ' . 'categories.category_id, ' . 'categories.category_name, ' . 'subcategories.subcategory_id, ' . 'subcategories.subcategory_name ' . 'FROM product_has_category ' . 'JOIN product_has_subcategory ON product_has_subcategory.product_id = product_has_category.product_id ' . 'JOIN subcategories ON subcategories.subcategory_id = product_has_subcategory.subcategory_id ' . 'JOIN categories ON categories.category_id = product_has_category.category_id ' . 'JOIN products ON products.product_id = product_has_category.product_id ' . 'JOIN user_has_products ON products.product_id = user_has_products.product_id ' . 'WHERE product_has_category.category_id = ' . $category . ' ' . 'AND product_has_category.product_id != ' . $productId . ' ' . 'AND user_has_products.user_id = ' . $userId . ' ' . 'AND products.product_status = 1');
$stmt->execute();
}
$categoryResult = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($categoryResult as $key => $res) {
$dateStart = explode(' ', $res['product_start']);
$dateEnd = explode(' ', $res['product_end']);
$dateStart[0] = explode('-', $dateStart[0]);
$dateStart[1] = explode(':', $dateStart[1]);
$dateEnd[0] = explode('-', $dateEnd[0]);
$dateEnd[1] = explode(':', $dateEnd[1]);
$stmpStart = mktime($dateStart[1][0], $dateStart[1][1], $dateStart[1][2], $dateStart[0][1], $dateStart[0][2], $dateStart[0][0]);
$stmpEnd = mktime($dateEnd[1][0], $dateEnd[1][1], $dateEnd[1][2], $dateEnd[0][1], $dateEnd[0][2], $dateEnd[0][0]);
$categoryResult[$key]['product_timestamp_start'] = $stmpStart;
$categoryResult[$key]['product_timestamp_end'] = $stmpEnd;
}
if (empty($subcategoryResult) && !empty($categoryResult)) {
$result = $categoryResult;
return $result;
}
if (!empty($subcategoryResult) && empty($categoryResult)) {
$result = $subcategoryResult;
return $result;
}
if (empty($subcategoryResult) && empty($categoryResult)) {
return false;
}
}
/**
*
* @return type
*/
public function getMandatoryFields()
{
$mandatoryQuery = "SELECT name FROM cfg_forms_fields WHERE mandatory = '1' " . "AND field_id IN (\n SELECT\n fi.field_id\n FROM\n cfg_forms_fields fi, cfg_forms_blocks fb, cfg_forms_blocks_fields_relations fbf, cfg_forms_sections fs, cfg_forms f\n WHERE\n fi.field_id = fbf.field_id\n AND\n fbf.block_id = fb.block_id\n AND\n fb.section_id = fs.section_id\n AND\n fs.form_id = f.form_id\n AND\n f.route = :route\n )";
$stmt = $this->dbconn->prepare($mandatoryQuery);
$stmt->bindParam(':route', $this->formRoute, \PDO::PARAM_STR);
$stmt->execute();
$mandatoryFieldList = $stmt->fetchAll(\PDO::FETCH_ASSOC);
return array_column($mandatoryFieldList, 'name');
}
/**
* Prints out the title of the supplied topic
*
* @param type $db DB Connection to use
* @param type $xtopic The topic to get the title of
* @param type $xbegin
*/
function get_title($db, $xtopic, $xbegin)
{
if (is_numeric($xtopic) && is_numeric($xbegin)) {
$query = $db->prepare("SELECT topic_title FROM topics WHERE topic_id = ?");
$query->execute(array($xtopic));
if ($query->rowCount() < 1) {
die;
}
$topic = $query->fetch(PDO::FETCH_ASSOC);
echo $topic['topic_title'];
}
}
/**
* SQL query in the traditional from
*
* @param type $query
* @param type $params
* @return type
*/
public function raw_query($query, $params = null)
{
if (!empty($query)) {
$this->prepared_query = $this->db->prepare($query);
$this->prepared_query->execute($params);
return $this->prepared_query;
} elseif ($this->mode == 'dev') {
throw new Exception('Empty query');
} else {
die;
}
}
/**
* Funktion zum zufälligen Laden eines Produktes
* @param type $db
* @return type
*/
function getRandomProduct($db)
{
$stmt = $db->prepare('SELECT ' . 'product_id, ' . 'product_iframe ' . 'FROM ' . 'products ' . 'WHERE products.product_proof = ? ' . 'AND products.product_featured = ? ' . 'AND products.product_id = ? ');
$rand = rand(1, 4);
if ($stmt != false) {
$stmt->execute(array(1, 0, $rand));
$result = $stmt->fetch(PDO::FETCH_ASSOC);
if ($result) {
return $result;
} else {
return false;
}
}
}
public function call($sql)
{
try {
//$this->con->beginTransaction();
$stmt = $this->con->prepare($sql);
$this->con->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE);
$this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt->execute();
$retorno = $stmt->fetch(PDO::FETCH_NAMED);
//$this->con->commit();
Conect::destroy();
return $retorno;
} catch (Exception $ex) {
throw $ex;
}
}
/**
* Function to add a record in the history table. The function receives a PDO object,
* creates the insert query via prepared statements, to ensure data validity, and
* registers it in the database.
* @param type $pdo PDO object to use for the insertion
* @param type $data Data array containing the information to be entered in the
* database
* @return boolean For error checking purposes, return true on success and
* false on error
*/
function store_game_in_db($pdo, $data)
{
if (!isset($data['first_player_name']) || !isset($data['second_player_name']) || !isset($data['player_won']) || !isset($data['game_date'])) {
return false;
}
try {
$stmt = $pdo->prepare('insert into history (first_player_name, second_player_name, player_won, game_date) ' . 'values (:first_player_name, :second_player_name, :player_won, :game_date)');
$stmt->bindValue(":first_player_name", $data['first_player_name']);
$stmt->bindValue(":second_player_name", $data['second_player_name']);
$stmt->bindValue(":player_won", $data['player_won']);
$stmt->bindValue(":game_date", $data['game_date']);
$stmt->execute();
} catch (PDOException $e) {
return false;
}
return true;
}
/**
*
*
*/
public function execute($sql, $values = null)
{
//
$stmt = $this->_pdo->prepare($sql);
//
if (is_array($values)) {
foreach ($values as $token => $value) {
$stmt->bindValue($token, $value);
}
}
//
try {
$stmt->execute();
} catch (PDOException $ex) {
throw new Exception($ex->getMessage(), intval($ex->getCode()));
}
//
return $stmt;
}
/**
*
* @param type $connection
* @param type $sql
* @param type $values
* @param type $conditions
* @return string
*/
function execUpdate($connection, $sql, $values, $conditions)
{
$updValues = mixVal_Cond($values, $conditions);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$stmt = $connection->prepare($sql);
$stmt->execute($updValues);
$filas = $stmt->rowCount();
return $filas;
} catch (Exception $ex) {
echo "Error code: " . $ex->getCode() . " __ Error message: " . $ex->getMessage();
return "Error";
}
}
/**
* Funzione per controllare accesso alle pagine
* limitandolo soloa gli utenti che hanno effettuato il login
*
* @param type $mysqli Connessione al db
* @return boolean
*/
function login_check($mysqli)
{
// Verifica che tutte le variabili di sessione siano impostate correttamente
if (isset($_SESSION['user_id'], $_SESSION['username'], $_SESSION['login_string'])) {
$user_id = $_SESSION['user_id'];
$login_string = $_SESSION['login_string'];
$username = $_SESSION['username'];
$user_browser = $_SERVER['HTTP_USER_AGENT'];
// reperisce la stringa 'user-agent' dell'utente.
if ($stmt = $mysqli->prepare("SELECT password FROM users WHERE id = ? LIMIT 1")) {
$stmt->bind_param('i', $user_id);
// esegue il bind del parametro '$user_id'.
$stmt->execute();
// Esegue la query creata.
$stmt->store_result();
if ($stmt->num_rows == 1) {
// se l'utente esiste
$stmt->bind_result($password);
// recupera le variabili dal risultato ottenuto.
$stmt->fetch();
$login_check = hash('sha512', $password . $user_browser);
if ($login_check == $login_string) {
return true;
// Login eseguito!!!!
} else {
return false;
// Login NON eseguito
}
} else {
return false;
// Login non eseguito
}
} else {
return false;
// Login non eseguito
}
} else {
return false;
// Login non eseguito
}
}
/**
* Function to get all papers completed in time frame to scrape for marks, and then compare the class_totals and finish reports.
*
* @param type $mysqli - database object
* @param type $username - user for db access
* @param type $password - the users password
* @param type $rootpath - root path of site
* @param type $userid - the user running the script
* @param type $start_dateSQL - start date range of papers checked
* @param type $end_dateSQL - end date range of papers checked
* @param type $server - the server we are checking
* @param type $paperid - the papers we want to check (optional, all if not supplied)
*/
public function process_papers($mysqli, $username, $password, $rootpath, $userid, $start_dateSQL, $end_dateSQL, $server, $paperid = '')
{
$papers = array();
if ($paperid != '') {
$result = $mysqli->prepare("SELECT crypt_name, property_id, paper_title, DATE_FORMAT(start_date,'%d/%m/%Y'), DATE_FORMAT(start_date,'%Y%m%d%H%i%s'), DATE_FORMAT(end_date,'%Y%m%d%H%i%s') FROM properties WHERE property_id = ?");
$result->bind_param('i', $paperid);
} else {
$result = $mysqli->prepare("SELECT crypt_name, property_id, paper_title, DATE_FORMAT(start_date,'%d/%m/%Y'), DATE_FORMAT(start_date,'%Y%m%d%H%i%s'), DATE_FORMAT(end_date,'%Y%m%d%H%i%s') FROM properties WHERE paper_type = '2' AND start_date > {$start_dateSQL} AND end_date < {$end_dateSQL} AND deleted IS NULL ORDER BY start_date");
}
$result->execute();
$result->bind_result($crypt_name, $paperID, $title, $display_start_date, $start_date, $end_date);
while ($result->fetch()) {
$papers[] = array('crypt_name' => $crypt_name, 'paperID' => $paperID, 'title' => $title, 'display_start_date' => $display_start_date, 'start_date' => $start_date, 'end_date' => $end_date);
}
$result->close();
$paper_no = count($papers);
$current_no = 0;
$result = $mysqli->prepare("DELETE FROM class_totals_test_local WHERE user_id = ?");
$result->bind_param('i', $userid);
$result->execute();
$result = $mysqli->prepare("SELECT surname, first_names, username FROM users WHERE id = ? LIMIT 1");
foreach ($papers as $paper) {
$url = $server . $rootpath . "/reports/class_totals.php?paperID=" . $paper['paperID'] . "&startdate=" . $paper['start_date'] . "&enddate=" . $paper['end_date'] . "&repmodule=&repcourse=%&sortby=student_id&module=1&folder=&percent=100&absent=0&direction=asc&studentsonly=1";
$output = $this->getData($url, $username, $password);
$marks_set = $this->parseRawMarks($output);
$current_no++;
$insert = $mysqli->prepare("INSERT INTO class_totals_test_local(user_id, paper_id, status) VALUES(?, ?, 'in_progress')");
$insert->bind_param('ii', $userid, $paper['paperID']);
$insert->execute();
$insert->close();
$errors = '';
if ($marks_set === false) {
$marks_set = array();
$errors = "<ul><li>Couldn't access class_totals</li>\n";
}
foreach ($marks_set as $mark) {
$url = $server . $rootpath . "/paper/finish.php?id=" . $paper['crypt_name'] . "&metadataID=" . $mark['metadataID'] . "&userID=" . $mark['userID'] . "&surname=Test&log_type=2&percent=" . str_replace('%', '', $mark['percent']) . "&disable_mappings=1";
$output = $this->getData($url, $username, $password);
$script_mark = $this->parseScript($output);
if ($script_mark === false) {
if ($errors == '') {
$errors = '<ul>';
}
$errors .= "<li>Couldn't access finish</li>\n";
}
if ($script_mark != $mark['mark']) {
$result->bind_param('i', $mark['userID']);
$result->execute();
$result->store_result();
$result->bind_result($tmp_surname, $tmp_first_names, $tmp_username);
$result->fetch();
if ($errors == '') {
$errors = '<ul>';
}
$errors .= "<li>Problem with " . $mark['userID'] . " {$tmp_surname}, {$tmp_first_names} ({$tmp_username}) - {$script_mark} / " . $mark['mark'] . "</li>";
}
}
if ($errors != '') {
$errors .= '</ul>';
$status = 'failure';
} else {
$status = 'success';
}
$update = $mysqli->prepare("UPDATE class_totals_test_local SET status = ?, errors = ? WHERE user_id = ? AND paper_id = ?");
$update->bind_param('ssii', $status, $errors, $userid, $paper['paperID']);
$update->execute();
$update->close();
}
$result->close();
}
