public function resetSave()
{
if (($confirm = Request::get('fldConfirm')) == '' || $confirm != 'RESET CONFIG') {
return Widget::popupWrapper('Invalid response, Reset cancelled', -1, 'Action Cancelled') . $this->index();
} else {
DB::exec(DB::DEF, 'DELETE FROM tblConfig');
return Widget::popupWrapper('All configuration data has been erased', -1, 'Reset Complete') . $this->index();
}
}
public function index()
{
$html = '<h2 title="You are able to edit all your defaults ***WARNING*** please know what you are doing">JSON Configuration Editor</h2>';
if (($currentConfigKey = Request::get('fldCfgKey')) == '') {
$currentConfigKey = DB::oneValue(DB::DEF, 'SELECT fldKey FROM tblConfig ORDER BY 1 LIMIT 1');
}
if ($currentConfigKey === false || $currentConfigKey == '') {
return $html . 'No Configuration available yet';
}
$html .= Tag::table(['border' => '0', 'height' => '100%', 'width' => '100%']) . Tag::tr() . Tag::td(['nowrap' => 'nowrap', 'valign' => 'top']) . $this->editConfigForm($currentConfigKey) . Tag::_td() . Tag::td(['width' => '100%', 'valign' => 'top']) . $this->editJSONEditForm($currentConfigKey) . Tag::_td() . Tag::_tr() . Tag::tr() . Tag::td(['nowrap' => 'nowrap', 'valign' => 'top', 'colspan' => 2]) . $this->addForm() . Tag::_td() . Tag::_table();
return $html;
}
private static function getFromDB($key, $scope = self::USER_SCOPE)
{
$uid = self::getScope($scope);
if (($serializedValue = DB::oneValue(DB::DEF, self::SELECT_SQL, [$key, $uid])) !== false) {
self::$configItemsObjects[$key] = json_decode($serializedValue, true);
} else {
if ($uid !== self::GLOBAL_SCOPE) {
if (($serializedValue = DB::oneValue(DB::DEF, self::SELECT_SQL, [$key, self::GLOBAL_SCOPE])) !== false) {
self::$configItemsObjects[$key] = json_decode($serializedValue, true);
}
}
}
}
public static function valid($key)
{
$sql = 'SELECT COUNT(*) FROM tblCrossSiteProtection WHERE fldUniqueID=?';
$cnt = DB::oneValue(DB::DEF, $sql, $key);
if ($cnt > 0) {
$sql = 'DELETE FROM tblCrossSiteProtection WHERE fldUniqueID=? OR fldExpiryDate<?';
DB::exec(DB::DEF, $sql, [$key, time()]);
return true;
} else {
$sql = 'DELETE FROM tblCrossSiteProtection WHERE fldExpiryDate<?';
DB::exec(DB::DEF, $sql, time());
self::$log->error('CSRFGuard failed: ' . $key . ' not available ' . $_SERVER['SCRIPT_NAME']);
return false;
}
}
public static function initialize()
{
$dbType = Cfg::get('local-driver');
switch ($dbType) {
case DB::SQLITE:
$dbFileName = Cfg::get('local-host');
echo "Checking that the file {$dbFileName} exists\n";
if (file_exists($dbFileName)) {
echo "Database exists ({$dbFileName})\n";
} else {
echo "Creating empty database\n";
touch($dbFileName);
}
break;
case DB::MYSQL:
$fldHostName = Cfg::get('local-host');
$fldDBName = Cfg::get('local-db');
$fldUsername = Cfg::get('local-user');
$fldPassword = Cfg::get('local-pass');
try {
$dbh = new \PDO("mysql:host={$fldHostName}", $fldUsername, $fldPassword);
$dbh->exec("CREATE DATABASE IF NOT EXISTS {$fldDBName}") or die(print_r($dbh->errorInfo(), true));
} catch (PDOException $e) {
die("DB ERROR: " . $e->getMessage());
}
break;
default:
die("Unsupported DB Type: {$dbType}");
}
if (count(\Jackbooted\DB\DBMaintenance::getTableList()) == 0) {
// Put in the base data
$sqlFileName = Cfg::get('tmp_path') . '/base_database.sql';
if (file_exists($sqlFileName)) {
echo "Running the commands in {$sqlFileName} against the database\n";
foreach (explode(';', file_get_contents($sqlFileName)) as $statement) {
DB::exec(DB::DEF, $statement);
}
} else {
die("Base Database file does not exists ({$sqlFileName}) aborting\n");
}
} else {
die("Database already seems to be set up.");
}
echo "audititing Table - AlertsDAO\n";
(new \App\Models\AlertsDAO())->auditTable();
return '';
}
public static function migrate()
{
$maxRun = 0;
$runItems = [];
foreach (DBTable::factory(DB::DEF, 'SELECT * FROM tblMigration') as $row) {
if ((int) $row['fldRun'] > $maxRun) {
$maxRun = (int) $row['fldRun'];
}
if (!isset($runItems[$row['fldClass']])) {
$runItems[$row['fldClass']] = [];
}
$runItems[$row['fldClass']][] = $row['fldMethod'];
}
$maxRun += 1;
$html = '';
// Go through all the migration classes
foreach (Cfg::get('migration', []) as $migrationClass) {
$clazz = new \ReflectionClass($migrationClass);
// If new class then just add empty list
if (!isset($runItems[$migrationClass])) {
$runItems[$migrationClass] = [];
}
// get a list of methods to run
$methodList = [];
foreach ($clazz->getMethods() as $method) {
if (in_array($method->name, $runItems[$migrationClass])) {
continue;
}
if (strpos($method->name, 'migrate') !== 0) {
continue;
}
// Add the name to the list
$methodList[] = $method->name;
}
// Sort so that it will be date ordered
sort($methodList);
foreach ($methodList as $method) {
if (($result = call_user_func([$migrationClass, $method])) === false) {
$html .= "There is a problem running {$migrationClass}::{$method}<br/>\n";
} else {
$html .= $result;
DB::exec(DB::DEF, 'INSERT INTO tblMigration (fldMigrationID,fldRun,fldClass,fldMethod) VALUES (?,?,?,?)', [DBMaintenance::dbNextNumber(DB::DEF, 'tblMigration'), $maxRun, $migrationClass, $method]);
}
}
}
return $html;
}
public static function cleanup($numDays = 5)
{
$oneDay = 60 * 60 * 24;
time() - $numDays * $oneDay;
DB::exec(DB::DEF, 'DELETE FROM ' . self::$dao->tableName . ' WHERE fldTimeStamp<?', Stopwatch::timeToDB(time() - $numDays * $oneDay));
}
public function sendPW()
{
$sql = 'SELECT fldUserID FROM tblUser WHERE fldUser=?';
if (($id = DB::oneValue(DB::DEF, $sql, Request::get('fldEmail'))) === false) {
$msg = 'This email does not exist on this system.<br>' . 'Either choose a new email address or register as new customer.' . $this->forgotPassword();
} else {
$pw = Password::passGen(10, Password::MEDIUM);
if (DB::driver() == DB::MYSQL) {
$sql = 'UPDATE tblUser SET fldPassword=PASSWORD(?) WHERE fldUserID=?';
DB::exec(DB::DEF, $sql, [$pw, $id]);
} else {
$sql = 'UPDATE tblUser SET fldPassword=? WHERE fldUserID=?';
DB::exec(DB::DEF, $sql, [hash('md5', $pw), $id]);
}
// Update the Database with the new Password combo
$boss = Cfg::get('boss');
$desc = Cfg::get('desc');
// create the email message to notify about a password request
$body = '<h3>User requested password<br>Email: <b>%s</b></h3><br>From %s';
Mailer::envelope()->format(Mailer::HTML_TEXT)->from(Request::get('fldEmail'))->to($boss)->subject('User requested password')->body(sprintf($body, Request::get('fldEmail'), $desc))->send();
$body = <<<TXT
Message from %s
Here are your login details
Password: %s
Regards
%s
TXT;
// create the email message to notify the user of his/her login details
Mailer::envelope()->from($boss)->to(Request::get('fldEmail'))->subject('Login Request ' . $desc)->body(sprintf($body, $desc, $pw, $desc))->send();
$msg = 'Soon you will receive an email that will contain your login details.';
}
return Widget::popupWrapper($msg, -1);
}
protected function getRowCount()
{
return DB::oneValue($this->db, $this->countSql);
}
public function auditTable()
{
if (in_array(null, [$this->db, $this->tableName, $this->tableStructure])) {
return false;
}
if (!isset(self::$tableList[$this->db])) {
self::$tableList[$this->db] = array_flip(DBMaintenance::getTableList());
}
if (!isset(self::$tableList[$this->db][$this->tableName])) {
DB::exec($this->db, $this->tableStructure);
DBMaintenance::addTableToNextNumber($this->tableName, $this->keyFormat, $this->tableName);
self::$tableList[$this->db][$this->tableName] = 1;
}
return true;
}
public function xls($tName = '')
{
if (($tableName = Request::get('tblName', $tName)) == '') {
exit;
}
XLS::output(DB::query(DB::DEF, 'SELECT * FROM ' . $tableName), $tableName);
}
protected function exec($qry, $params = null)
{
//echo '<pre>' . $qry . "\n";
//print_r ( $params );
//echo '</pre>';
return DB::exec($this->db, $qry, $params);
}
public static function cleanup($numDays = 5)
{
$keepSeconds = time() - $numDays * 24 * 60 * 60;
$deletedRecords = DB::exec(DB::DEF, 'DELETE from tblCronQueue WHERE fldRunTime<?', $keepSeconds);
return [0, "Deleted: {$deletedRecords}"];
}
public function editAccountSave()
{
$uid = Request::get('fldUserID', G::get('fldUserID'));
$messages = [];
$sqls = [];
$params = [];
$pw = Request::get('fldPassword');
$pwCheck = Request::get('fldPassword_CHK');
$pwOld = Request::get('fldPassword_OLD');
if ($pw != '' && $pwCheck != '') {
if (!$this->checkOldPassword($uid, $pwOld)) {
$messages[] = '<font color=red>Old Password is not correct<font>';
} else {
if ($pw != $pwCheck) {
$messages[] = '<font color=red>Passwords are not the same<font>';
} else {
if ($pwOld == $pw) {
$messages[] = '<font color=red>No Change, old and new passwords same<font>';
} else {
if (DB::driver() == DB::MYSQL) {
$sqls[] = 'UPDATE tblUser SET fldPassword=PASSWORD(?),fldModified=UNIX_TIMESTAMP() WHERE fldUserID=?';
$params[] = [$pw, $uid];
} else {
$sqls[] = 'UPDATE tblUser SET fldPassword=?,fldModified=strftime(\'%s\',\'now\') WHERE fldUserID=?';
$params[] = [hash('md5', $pw), $uid];
}
}
}
}
}
$sqls[] = 'UPDATE tblUser SET fldSalutation=?,fldModified=' . time() . ' WHERE fldUserID=?';
$params[] = [Request::get('fldSalutation'), $uid];
if (Request::get('fldFirstName') == '') {
$messages[] = '<font color=red>First name cannot be empty<font>';
} else {
$sqls[] = 'UPDATE tblUser SET fldFirstName=?,fldModified=' . time() . ' WHERE fldUserID=?';
$params[] = [Request::get('fldFirstName'), $uid];
}
if (Request::get('fldLastName') == '') {
$messages[] = '<font color=red>Last name cannot be empty<font>';
} else {
$sqls[] = 'UPDATE tblUser SET fldLastName=?,fldModified=' . time() . ' WHERE fldUserID=?';
$params[] = [Request::get('fldLastName'), $uid];
}
if (Request::get('fldTimeZone') != '') {
$sqls[] = 'UPDATE tblUser SET fldTimeZone=?,fldModified=' . time() . ' WHERE fldUserID=?';
$params[] = [Request::get('fldTimeZone'), $uid];
}
if (Request::get('fldUser') != '') {
$sqls[] = 'UPDATE tblUser SET fldUser=?,fldModified=' . time() . ' WHERE fldUserID=?';
$params[] = [Request::get('fldUser'), $uid];
}
if (Request::get('fldLevel') != '') {
$sqls[] = 'UPDATE tblUser SET fldLevel=?,fldModified=' . time() . ' WHERE fldUserID=?';
$params[] = [Request::get('fldLevel'), $uid];
}
if (count($messages) != 0) {
return join('<br>', $messages) . $this->editAccount();
} else {
foreach ($sqls as $idx => $sql) {
DB::exec(DB::DEF, $sql, $params[$idx]);
}
if ($uid == G::get('fldUserID')) {
foreach (DB::oneRow(DB::DEF, 'SELECT * FROM tblUser WHERE fldUserID=?', $uid) as $key => $val) {
G::set($key, $val);
}
}
return 'Sucessfully updated user account details' . $this->editAccount();
}
}
/** Function to load the user Group details
* @returns boolean
* @private
*/
function _loadGroupTable()
{
// Load the first group because it is the Global Group
$sql = DB::limit("SELECT * FROM tblGroup", 0, 1);
$tab = new DBTable(DB::DEF, $sql, null, DB::FETCH_ASSOC);
if ($tab->isEmpty()) {
return false;
}
$fldGroup = [];
$fldGroup[$tab->getValue("fldGroupID")] = $tab->getValue("fldName");
// get the groups that are related to this client
$sql = "SELECT g.* FROM tblGroup g, tblUserGroupMap map " . "WHERE map.fldUserID='" . $this->prefs->get("fldIserID") . "' " . "AND map.fldGroupID=g.fldGroupID ";
$tab = new DBTable(DB::DEF, $sql, null, DB::FETCH_ASSOC);
if (!$tab->isEmpty()) {
for ($i = 0; $i < $tab->getRowCount(); $i++) {
$fldGroup[$tab->getValue("fldGroupID", $i)] = $tab->getValue("fldName", $i);
}
}
$this->prefs->put("fldGroup", $fldGroup);
// return true/success
return TRUE;
}
public function fileChecksumRebase()
{
DB::exec(DB::DEF, 'TRUNCATE tblFileCheck');
$dirList = PHPExt::dirSearch(Cfg::get('site_path'), '/^[^_].*$/');
$len = strlen(Cfg::get('site_path')) + 1;
$fileCount = 0;
foreach ($dirList as $fullPath) {
$fileCount++;
DB::exec(DB::DEF, 'INSERT INTO tblFileCheck VALUES(?,?,?,?)', [DBMaintenance::dbNextNumber(DB::DEF, 'tblFileCheck'), substr($fullPath, $len), filesize($fullPath), sha1_file($fullPath)]);
}
return "Updated {$fileCount} files<br/>" . $this->fileChecksum();
}
/**
* Construct a table in memory.
*
* @param object $resultSet Pass n result set from PDO query.
*
* @since 1.0
*/
public function __construct($dbh, $qry, $params = null, $fetch = DB::FETCH_BOTH)
{
parent::__construct();
$this->fetch = $fetch;
if (is_object($dbh)) {
$this->table = $dbh->fetchAll($fetch);
} else {
$cacheKey = $dbh . ' ' . $qry . ' ' . serialize($params);
if (($cacheValue = self::$dataCache->get($cacheKey)) !== false) {
$this->table = $cacheValue;
} else {
if (($resultSet = DB::query($dbh, $qry, $params)) === false) {
return;
}
$this->table = $resultSet->fetchAll($fetch);
self::$dataCache->set($cacheKey, $this->table);
}
}
}
private static function getGroupIDs($uid)
{
$qry = 'SELECT fldGroupID FROM tblUserGroupMap WHERE fldUserID=?';
$groups = DBTable::factory(DB::DEF, $qry, $uid, DB::FETCH_NUM)->getColumn(0);
$groups[] = DB::oneValue(DB::DEF, 'SELECT fldGroupID FROM tblGroup LIMIT 1');
return $groups;
}
