/**
* Query to insert multiple rows
* @param string $table
* @param array $columns
* @param mysqli $db
* @param unknown_type $priority
* @throws Exception
*/
public function __construct($table = NULL, array $columns = NULL, $db = NULL, $priority = NULL)
{
//Process Database connection
if ($db instanceof mysqli) {
$this->setDb($db);
} else {
try {
$this->setDb(Staple_DB::get());
} catch (Exception $e) {
$this->setDb(new mysqli());
}
}
//No DB = Bad
if (!$this->db instanceof mysqli) {
throw new Exception('Unable to create database object', Staple_Error::DB_ERROR);
}
//Set Table
if (isset($table)) {
$this->setTable($table);
}
//Set Data
if (isset($columns)) {
$this->setColumns($columns);
}
}
function __construct()
{
$this->db = Staple_DB::get();
$this->systemMessages = $this->loadSystemMessages();
$this->privateMessages = $this->loadPrivateMessages();
$this->allPrivateMessages = $this->loadAllPrivateMessages();
$this->totalPrivateMessages = $this->countPrivateMessages();
$this->supervisorMessages = $this->loadSupervisorMessages();
$this->expiredSystemMessages = $this->loadExpiredSystemMessages();
$this->expiredPrivateMessages = $this->loadExpiredPrivateMessages();
}
function __construct($year, $month, $inactive = null)
{
$this->db = Staple_DB::get();
if ($inactive != null) {
$staffIds = $this->getStaffIds(1);
} else {
$staffIds = $this->getStaffIds();
}
$data = array();
if (count($staffIds) > 0) {
foreach ($staffIds as $key => $value) {
$data[$value] = $this->getTimesheet($key, $year, $month);
$data[$value]['id'] = $key;
}
}
$this->timesheets = $data;
}
function getYears()
{
$db = Staple_DB::get();
//Get user ID from Auth
$user = new userModel();
$userId = $user->getId();
//$sql = "SELECT YEAR(FROM_UNIXTIME(inTime)) AS 'year' FROM timeEntries WHERE userId = $userId GROUP BY year ORDER by year ASC";
$sql = "SELECT YEAR(FROM_UNIXTIME(inTime)) AS 'year' FROM timeEntries GROUP BY year ORDER by year ASC";
if ($db->query($sql)->num_rows > 0) {
$query = $db->query($sql);
$data = array();
while ($result = $query->fetch_assoc()) {
$data[$result['year']] = $result['year'];
}
return $data;
} else {
return array();
}
}
public function Log($errmsg, $errsql = NULL, $applicationID = NULL)
{
$db = Staple_DB::get();
$dbenc = Staple_Config::getValue('encrypt', 'key');
$columns = 'occurred,error';
$values = "NOW(), '" . $db->escape_string($errmsg) . "'";
if (isset($errsql)) {
$ssnregex = '/^\\d{3}\\-\\d{2}\\-\\d{4}$/';
$errsql = preg_replace($ssnregex, 'SSN', $errsql);
$columns .= ',`sql`';
$values .= ",AES_ENCRYPT('" . $db->escape_string($errsql) . "','" . $db->real_escape_string($dbenc) . "')";
}
if (isset($applicationID)) {
$columns .= ',applicationID';
$values .= ",'" . (int) $applicationID . "'";
}
$sql = "INSERT INTO log_database_err ({$columns}) VALUES ({$values})";
if (($result = $db->query($sql)) === true) {
return true;
} else {
return false;
}
}
function __construct()
{
$this->db = Staple_DB::get();
}
function genSetNewBatch()
{
$this->db = Staple_DB::get();
$user = new userModel();
$userId = $user->getId();
$oldKey = $user->getBatchId();
$key = sha1(time() . $user->getUsername() . rand(999, 9999999999.0));
//Check if key exists
$sql = "SELECT id FROM accounts WHERE batchId = '" . $this->db->real_escape_string($key) . "'";
if ($this->db->query($sql)->fetch_row() > 0) {
//Key already in use
return false;
} else {
//Set new key in user account
$sql = "UPDATE accounts SET batchId='" . $this->db->real_escape_string($key) . "' WHERE id={$userId}";
if ($this->db->query($sql)) {
//Log Audit
$audit = new auditModel();
$audit->setAction('Timesheet Validation');
$audit->setUserId($userId);
$audit->setItem('Batch: ' . $oldKey);
$audit->save();
return true;
} else {
return false;
}
}
}
/**
* Executes the query.
* @return mysqli_result | bool
*/
public function Execute()
{
if ($this->db instanceof mysqli) {
return $this->db->query($this->build());
} else {
try {
$this->db = Staple_DB::get();
} catch (Exception $e) {
//@todo try for a default connection if no staple connection
throw new Exception('No Database Connection', Staple_Error::DB_ERROR);
}
if ($this->db instanceof mysqli) {
return $this->db->query($this->build());
}
}
return false;
}
function _overlap($inTime, $outTime, $id = null)
{
//Checks to see if the times entered fit within any other time entry for that user.
$this->db = Staple_DB::get();
$auth = Staple_Auth::get();
$user = new userModel($auth->getAuthId());
$userId = $user->getId();
/*
$dateString = strtotime(date("Y-m-d", $inTime));
$nextDateString = $dateString + 86400;
*/
$date = new DateTime();
$dateString = $inTime;
$nextDateString = $date->setTimestamp($inTime)->setTime(23, 59, 59);
$nextDateString = $nextDateString->format('U');
//Find the earliest time for the given date.
$sql = "\n SELECT inTime FROM timeEntries WHERE inTime > '" . $this->db->real_escape_string($dateString) . "' AND userId = '" . $this->db->real_escape_string($userId) . "' ORDER BY inTime ASC LIMIT 1\n ";
$query = $this->db->query($sql);
$result = $query->fetch_assoc();
$firstInTime = $result['inTime'];
//Find the latest time for the given date.
$sql = "\n SELECT outTime FROM timeEntries WHERE outTime > '" . $this->db->real_escape_string($dateString) . "' AND outTime < '" . $this->db->real_escape_string($nextDateString) . "' AND userId = '" . $this->db->real_escape_string($userId) . "' ORDER BY outTime DESC LIMIT 1\n ";
if ($this->db->query($sql)->num_rows > 0) {
$query = $this->db->query($sql);
$result = $query->fetch_assoc();
$lastOutTime = $result['outTime'];
} else {
$lastOutTime = null;
}
if ($id == null) {
$sql = "SELECT inTime, outTime FROM timeEntries WHERE userId = '" . $this->db->real_escape_string($userId) . "'";
} else {
$sql = "SELECT inTime, outTime FROM timeEntries WHERE userId = '" . $this->db->real_escape_string($userId) . "' AND id <> '" . $this->db->real_escape_string($id) . "'";
}
$query = $this->db->query($sql);
$data = array();
while ($result = $query->fetch_assoc()) {
$data[] = $result;
}
$overlap = 0;
foreach ($data as $entry) {
if ($inTime == $entry['inTime'] && $outTime == $entry['outTime']) {
$overlap++;
}
if ($inTime > $entry['inTime'] && $inTime < $entry['outTime']) {
$overlap++;
}
if ($outTime > $entry['inTime'] && $outTime < $entry['outTime']) {
$overlap++;
}
if ($inTime < $firstInTime && $outTime > $lastOutTime) {
//$overlap++;
}
}
if ($overlap > 0) {
return false;
} else {
return true;
}
}
function __construct()
{
$this->db = Staple_DB::get();
$auth = Staple_Auth::get();
$username = $auth->getAuthId();
$sql = "SELECT id, username, firstName, lastName, authLevel, batchId, supervisorId, type FROM accounts WHERE username = '******'";
if ($this->db->query($sql)->fetch_row() > 0) {
$query = $this->db->query($sql);
$result = $query->fetch_assoc();
$this->setid($result['id']);
$this->setUsername($result['username']);
$this->setFirstName($result['firstName']);
$this->setLastName($result['lastName']);
$this->setAuthLevel($result['authLevel']);
$this->setBatchId($result['batchId']);
$this->setSupervisorId($result['supervisorId']);
$this->setType($result['type']);
} else {
return false;
}
}
/**
* Gets the access level for the supplied $uid.
* @param string $uid
* @return int
* @see Staple_AuthAdapter::getLevel()
*/
public function getLevel($uid)
{
if ($this->checkConfig($this->_settings)) {
if (array_key_exists('rolefield', $this->_settings)) {
$db = Staple_DB::get();
$sql = 'SELECT ' . $db->real_escape_string($this->_settings['rolefield']) . '
FROM ' . $db->real_escape_string($this->_settings['authtable']) . '
WHERE ' . $db->real_escape_string($this->_settings['uidfield']) . ' = ' . '\'' . $db->real_escape_string($uid) . '\';';
$result = $db->query($sql);
if ($result !== false) {
$myrow = $result->fetch_array();
$level = (int) $myrow[$this->_settings['rolefield']];
if ($level < 0) {
return 0;
} else {
return $level;
}
} else {
return 0;
}
} else {
return 1;
}
}
}
/**
* Converts a PHP data type into a compatible MySQL string.
* @param mixed $inValue
* @return string
*/
public static function convertTypes($inValue, Staple_DB $db = NULL)
{
if (!$db instanceof mysqli) {
try {
$db = Staple_DB::get();
} catch (Exception $e) {
throw new Exception('No Database Connection', Staple_Error::DB_ERROR);
}
}
//Decided to error on the side of caution and represent floats as strings in SQL statements
if (is_string($inValue) || is_float($inValue)) {
return "'" . $db->real_escape_string($inValue) . "'";
} elseif (is_bool($inValue)) {
return $inValue ? 'TRUE' : 'FALSE';
} elseif (is_null($inValue)) {
return 'NULL';
} elseif (is_array($inValue)) {
return "'" . $db->real_escape_string(implode(" ", $inValue)) . "'";
} elseif ($inValue instanceof DateTime) {
return "'" . $db->real_escape_string($inValue->format('Y-m-d H:i:s')) . "'";
} else {
return "'" . $db->real_escape_string((string) $inValue) . "'";
}
}
/**
* @param string $last_query
*/
protected function setLastQuery($last_query)
{
self::$last_query = $last_query;
}
function __construct()
{
$this->db = Staple_DB::get();
$auth = Staple_Auth::get();
$this->username = $auth->getAuthId();
}
/**
* @return Staple_DB $_modelDB
*/
public function getModelDB()
{
if (isset($this->_modelDB)) {
return $this->_modelDB;
} else {
return Staple_DB::get();
}
}
