private function loadAllReceiptIdsOrderByDate()
{
$databaseHelper = new DatabaseHelper();
$this->allReceiptIdsOrderByDate = array();
$receiptIdsDb = $databaseHelper->query("SELECT `id` FROM `receipt` ORDER BY `timestamp` DESC");
if (count($receiptIdsDb) > 0) {
foreach ($receiptIdsDb as $receiptIdDb) {
$this->allReceiptIdsOrderByDate[] = $receiptIdDb['id'];
}
}
}
/**
* Performs a query to the database and returns the representing list of objects
*
* This method will run the query and then build the list of objects that represent
* the records of the table. It will also init the objects with the basic properties
* like the reference to the global App object
*
* @param string $query The query to perform
*
* @return array The list of objects representing the records
*/
protected function _queryObjectList($query)
{
// query database
$result = $this->database->query($query);
// fetch objects and execute init callback
$objects = array();
while ($object = $this->database->fetchObject($result, $this->class)) {
$objects[$object->{$this->key}] = $this->_initObject($object);
}
$this->database->freeResult($result);
return $objects;
}
/**
* @param $tableName
* @param array $tblFields
* @param array $tblIndex
* @return $this
*/
public function createTable($tableName, array $tblFields, array $tblIndex)
{
$params = array_merge($tblFields, $tblIndex);
if (empty($params)) {
return $this;
}
$sql = array();
$sql[] = 'CREATE TABLE IF NOT EXISTS `' . $tableName . '`';
$sql[] = '(' . implode(",\n ", $params) . ')';
$sql[] = 'COLLATE=\'utf8_general_ci\' ENGINE=MyISAM;';
$sqlString = implode(' ', $sql);
$this->_db->query($sqlString);
return $this;
}
/**
* @param $data
* @param $table
* @param null $keyId
* @param string $keyField
* @return bool|mixed
*/
protected function _update($data, $table, $keyId = null, $keyField = 'id')
{
if (empty($data)) {
return false;
}
$keyId = $keyId ? $keyId : $data['id'];
if (isset($data[$keyField])) {
unset($data[$keyField]);
}
$sql = $this->_getSelect()->update($table)->where($keyField . ' = ?', $keyId);
foreach ($data as $key => $value) {
$value = is_null($value) ? 'NULL' : $this->_quote($value);
$sql->set('`' . $key . '` = ' . $value);
}
return $this->_dbHelper->query((string) $sql);
}
public function loadProducts()
{
$databaseHelper = new DatabaseHelper();
$receiptProductsDb = $databaseHelper->query("SELECT `id` FROM `receipt_product` WHERE receiptId = " . $this->id);
if (count($receiptProductsDb) > 0) {
foreach ($receiptProductsDb as $receiptProductDb) {
$ReceiptProduct = new ReceiptProduct($receiptProductDb['id']);
$this->receiptProducts[] = array('id' => $ReceiptProduct->getId(), 'productId' => $ReceiptProduct->getProductId(), 'label' => $ReceiptProduct->getLabel(), 'priceInVat' => $ReceiptProduct->getPriceInVat(), 'priceExVat' => $ReceiptProduct->getPriceExVat(), 'amount' => $ReceiptProduct->getAmount(), 'totalPriceInVat' => $ReceiptProduct->getTotalPriceInVat(), 'totalPriceExVat' => $ReceiptProduct->getTotalPriceExVat());
}
}
}
public function returnProductGridAsArray()
{
$gridArray = array();
$databaseHelper = new DatabaseHelper();
//create basic array
$maxGridSize = $databaseHelper->query("SELECT MAX( regRow ) AS maxrow, MAX( regCol ) AS maxcol FROM `product` ");
if ((int) $maxGridSize[0]['maxrow'] > 0 && (int) $maxGridSize[0]['maxcol'] > 0) {
for ($r = 1; $r <= (int) $maxGridSize[0]['maxrow']; $r++) {
for ($c = 1; $c <= (int) $maxGridSize[0]['maxcol']; $c++) {
$gridArray[$r][$c] = 0;
}
}
}
//fill basic array with products
$allProducts = $databaseHelper->query("SELECT id, regRow, regCol FROM `product` ORDER BY regRow, regCol");
if (count($allProducts) > 0) {
foreach ($allProducts as $product) {
$gridArray[$product['regRow']][$product['regCol']] = $product['id'];
}
}
return $gridArray;
}
/**
* Finds geographical features around the object's location
*
* Searches the geonames database for a specified number of features of the specified type within the specified
* distance of the object's position ordered by either closest or furthest first. All search criteria may be
* omitted to return an unrestrained (very large and not recommended) result set.
*
* @uses LatLng::convertMetersToDegreesLatitude
* @uses LatLng::convertMetersToDegreesLongitude
* @uses DatabaseHelper::getInstance
* @uses DatabaseHelper::query
* @uses DistantGeographicalFeature
*
* @param LatLng $position The co-ordinates from which the search should be based.
* @param null|string $featureType The type of feature to be searched for. Options are 'landmark', 'city'
* (all city sizes), 'majorCity', 'minorCity', NULL (all feature types).
* Default: NULL.
* @param null|int $searchRadius The search radius in meters from the object's position. Default = NULL.
* @param null|int $noOfResults The number of results to return. Default = NULL.
* @param string $order The order of the search results. Input options are 'asc' (closest first)
* or 'desc' furthest first. Default = 'asc.
*
* @return GeographicalFeature[]
* @throws ArgumentOutOfRangeException For featureType, searchRadius, order, noOfResults.
* @throws InvalidOperationException For executing the method without setting the database credentials at the
* class level.
* @throws MyInvalidArgumentException For searchRadius, noOfResults.
*/
public static final function findFeatures(LatLng $position, $featureType = NULL, $searchRadius = NULL, $noOfResults = NULL, $order = 'asc')
{
if (is_null(self::$databaseCredentials)) {
throw new InvalidOperationException('Database credentials must be set at the class level to allow this action to take place.');
}
// Validates user input and builds the syntax relevant to the type of feature to search for.
switch (strtolower($featureType)) {
case 'landmark':
$featureTypeSyntax = " feature_class != 'P' ";
break;
case 'city':
$featureTypeSyntax = " feature_class = 'P' ";
break;
case 'majorcity':
$featureTypeSyntax = " feature_class = 'P' AND population = 1 ";
break;
case 'minorcity':
$featureTypeSyntax = " feature_class = 'P' AND population = 0 ";
break;
case NULL:
$featureTypeSyntax = "";
break;
default:
throw new ArgumentOutOfRangeException("INPUT: featureType. The supplied value type is invalid. Either 'landmark', 'city', 'majorCity',\n 'minorCity', or NULL expected.", $featureType);
break;
}
// Validates user input then builds the syntax to set a search radius.
if (isset($searchRadius)) {
if (!is_numeric($searchRadius)) {
throw new MyInvalidArgumentException("INPUT: searchRadius. The supplied value type is invalid. A numeric value is expected.");
}
if ($searchRadius <= 0) {
throw new ArgumentOutOfRangeException('INPUT: searchRadius. The supplied value is out of acceptable range. >0 expected.', $searchRadius);
}
$maxFeatureSearchDistanceInDegreesLatitude = LatLng::convertMetersToDegreesLatitude($searchRadius, $position->getLatitude());
$maxFeatureSearchDistanceInDegreesLongitude = LatLng::convertMetersToDegreesLongitude($searchRadius, $position->getLatitude());
$searchRadiusSyntax = <<<MYSQL
latitude
BETWEEN :latitude - {$maxFeatureSearchDistanceInDegreesLatitude}
AND :latitude + {$maxFeatureSearchDistanceInDegreesLatitude}
AND
longitude
BETWEEN :longitude - {$maxFeatureSearchDistanceInDegreesLongitude}
AND :longitude + {$maxFeatureSearchDistanceInDegreesLongitude}
MYSQL;
$havingSyntax = " HAVING distance < {$searchRadius} ";
} else {
$searchRadiusSyntax = '';
$havingSyntax = '';
}
// Assembles the syntax for the WHERE clause combining feature and radius components built above.
if ($featureTypeSyntax || $searchRadiusSyntax) {
$whereSyntax = ' WHERE ';
$whereSyntax .= $featureTypeSyntax;
if ($featureTypeSyntax && $searchRadiusSyntax) {
$whereSyntax .= ' AND ';
}
$whereSyntax .= $searchRadiusSyntax;
} else {
$whereSyntax = '';
}
// Validates user input and builds the syntax to set the result order
switch (strtolower($order)) {
case 'asc':
$orderSyntax = ' ASC ';
break;
case 'desc':
$orderSyntax = ' DESC ';
break;
default:
throw new ArgumentOutOfRangeException("INPUT: order. The supplied value is invalid. Order can be either 'asc' or 'desc'", $order);
}
// Validates user input and builds the syntax to limit the number of results.
if (isset($noOfResults)) {
if (is_numeric($noOfResults)) {
if ($noOfResults > 0) {
$limitSyntax = " LIMIT {$noOfResults}";
} else {
throw new ArgumentOutOfRangeException("INPUT: noOfResults. The supplied value is invalid. Must be 1 or higher.", $noOfResults);
}
} else {
throw new MyInvalidArgumentException("INPUT: noOfResults. The supplied value type is invalid. A numeric value is expected.");
}
} else {
$limitSyntax = '';
}
// Assemble the final query from literal and dynamic components (built and assembled above based on user
// criteria).
$findFeaturesQuery = <<<MYSQL
SELECT
f.latitude,
f.longitude,
f.name AS name,
f.feature_class AS featureClass,
ft.feature_type AS featureType,
c.county_name AS county,
s.state_abbreviation AS state,
ROUND(6378137 *
ACOS(COS(RADIANS(:latitude))
* COS(RADIANS(latitude))
* COS(RADIANS(longitude) - RADIANS(:longitude))
+ SIN(RADIANS(:latitude))
* SIN(RADIANS(latitude)))) AS distance
FROM
features f
LEFT JOIN feature_types ft
ON f.feature_type_id = ft.feature_type_id
LEFT JOIN counties c
ON f.county_id = c.county_id AND f.state_id = c.state_id
LEFT JOIN states s
ON f.state_id = s.state_id
{$whereSyntax}
{$havingSyntax}
ORDER BY
distance {$orderSyntax}
{$limitSyntax}
MYSQL;
// The query parameters
$findFeaturesParams = array('latitude' => $position->getLatitude(), 'longitude' => $position->getLongitude());
// Connect to the database, run the query, build and return an array of DistantGeographicalFeature objects.
$DBH = call_user_func_array('DatabaseHelper::getInstance', self::$databaseCredentials);
$findFeaturesResult = DatabaseHelper::query($DBH, $findFeaturesQuery, $findFeaturesParams);
$features = array();
while ($feature = $findFeaturesResult->fetch(PDO::FETCH_ASSOC)) {
try {
if ($feature['featureClass'] == 'P') {
$city = $feature['name'];
} else {
$featurePosition = new LatLng($feature['latitude'], $feature['longitude']);
$cityArray = self::findFeatures($geonamesCredentials, $featurePosition, 'majorCity', $searchRadius, 1, 'asc');
if ($cityArray) {
$city = $cityArray[0]->getCity();
} else {
$city = NULL;
}
}
$features[] = new GeographicalFeature(new LatLng($feature['latitude'], $feature['longitude']), $feature['name'], $feature['featureType'], $city, $feature['county'], $feature['state']);
} catch (Exception $e) {
continue;
}
}
return $features;
}
/**
* Saves the dune to the database.
*
* @return bool|int On successful new save of existing update returns the dune ID. On no change returns FALSE.
* @throws InvalidOperationException If database credentials have not been set at the class level.
*/
public final function save()
{
if (is_null(self::$databaseCredentials)) {
throw new InvalidOperationException('Database credentials must be set at the class level to allow this action to take place.');
}
/** @var PDO $DBH */
$DBH = call_user_func_array('DatabaseHelper::getInstance', self::$databaseCredentials);
$storeNewDuneQuery = <<<MYSQL
INSERT INTO
dunes
(latitude, longitude, site_id, crest, toe)
VALUES
(:latitude, :longitude, :selectedSiteId, :crest, :toe)
ON DUPLICATE KEY UPDATE
site_id = VALUES(site_id),
crest = VALUES(crest),
toe = VALUES(toe)
MYSQL;
$storeNewDuneParams = array('latitude' => $this->position->getLatitude(), 'longitude' => $this->position->getLongitude(), 'selectedSiteId' => $this->siteId, 'crest' => $this->crestHeight, 'toe' => $this->toeHeight);
DatabaseHelper::query($DBH, $storeNewDuneQuery, $storeNewDuneParams);
$duneId = $DBH->lastInsertId();
if ($duneId && $duneId > 0) {
return $duneId;
} else {
return FALSE;
}
}
/**
* Saves a new site and any contained dunes to the database.
*
* @uses Site:saveDunes
* @throws InvalidOperationException If the database credentials have not been set.
*/
public final function saveSite()
{
if (is_null(self::$databaseCredentials)) {
throw new InvalidOperationException('Database credentials must be set at the class level to allow this action to take place.');
}
$saveNewSiteQuery = <<<MYSQL
INSERT IGNORE INTO
sites
SET
site = :siteName,
last_import = NULL
MYSQL;
$saveNewSiteParams = array('siteName' => $this->siteName);
$DBH = call_user_func_array('DatabaseHelper::getInstance', self::$databaseCredentials);
DatabaseHelper::query($DBH, $saveNewSiteQuery, $saveNewSiteParams);
$siteId = $DBH->lastInsertId();
if ($siteId == 0) {
$siteId = self::getIdByName($this->siteName);
}
$this->siteId = $siteId;
$this->saveSiteDunes();
}
public static function getDateTimeFromId($fileId)
{
if (is_null(self::$databaseCredentials)) {
throw new InvalidOperationException('Database credentials must be set at the class level to allow this action to take place.');
}
if (!is_int($fileId)) {
$argumentType = gettype($fileId);
throw new MyInvalidArgumentException("INPUT: selectedFileId. The supplied value for selectedFileId was invalid. Integer expected. {$argumentType} received.");
}
$DBH = call_user_func_array('DatabaseHelper::getInstance', self::$databaseCredentials);
$dateTimeQuery = <<<MYSQL
SELECT
date_time
FROM
twl_files
WHERE
file_id = {$fileId}
MYSQL;
$dateTimeParams = array('selectedFileId' => $fileId);
$dateTimeResult = DatabaseHelper::query($DBH, $dateTimeQuery, $dateTimeParams);
return $dateTimeResult->fetchColumn();
}
$closestTimeSeriesParams = array('latitude' => $dunes[$i]['la'], 'longitude' => $dunes[$i]['lo'], 'latitudeNBoundary' => $dunes[$i]['la'] + $latitudeBoundary, 'latitudeSBoundary' => $dunes[$i]['la'] - $latitudeBoundary, 'longitudeEBoundary' => $dunes[$i]['lo'] + $longitudeBoundary, 'longitudeWBoundary' => $dunes[$i]['lo'] - $longitudeBoundary);
$closestTimeSeriesResult = DatabaseHelper::query($DBH, $closestTimeSeriesQuery, $closestTimeSeriesParams);
$dunes[$i]['tsId'] = $closestTimeSeriesResult->fetchColumn();
if (empty($dunes[$i]['tsId'])) {
exit;
}
$duneImpactQuery = <<<MYSQL
SELECT
IF(MAX(twl) > :toeHeight, TRUE, FALSE) AS toe_impact,
if(MAX(twl) > :crestHeight, TRUE, FALSE) AS crest_impact
FROM
twl_data_points
WHERE
file_id = :selectedFileId
AND
time_series_id = :timeSeriesId
MYSQL;
$duneImpactParams = array('toeHeight' => $dunes[$i]['toe'], 'crestHeight' => $dunes[$i]['crest'], 'selectedFileId' => $fileId, 'timeSeriesId' => $dunes[$i]['tsId']);
$duneImpactResult = DatabaseHelper::query($DBH, $duneImpactQuery, $duneImpactParams);
$duneImpact = $duneImpactResult->fetch(PDO::FETCH_ASSOC);
if ($duneImpact['crest_impact']) {
$dunes[$i]['im'] = '2';
} elseif ($duneImpact['toe_impact']) {
$dunes[$i]['im'] = '1';
} else {
$dunes[$i]['im'] = '0';
}
unset($dunes[$i]['toe'], $dunes[$i]['crest']);
}
$data = array('meta' => array('selectedSiteId' => $siteId, 'selectedFileId' => $fileId, 'availableFiles' => $availableFiles), 'dune' => $dunes);
print json_encode($data);
/**
* Saves the current data point object to the Database.
*
* @param int $fileId The database ID of the file this point originated from.
* @param int $timeSeriesId The database ID of the time series this point belongs to.
*
* @throws InvalidOperationException If database credentials have not been set.
*/
public final function saveDataPoint($fileId, $timeSeriesId)
{
if (is_null(self::$databaseCredentials)) {
throw new InvalidOperationException('Database credentials must be set at the class level to allow this action to take place.');
}
$DBH = call_user_func_array('DatabaseHelper::getInstance', self::$databaseCredentials);
$saveDataPointQuery = <<<MYSQL
INSERT IGNORE INTO
twl_data_points
SET
file_id = :selectedFileId,
time_series_id = :timeSeriesId,
date_time = :dateTime,
hs = :hs,
pp = :pp,
twl = :twl,
twl05 = :twl05,
twl95 = :twl95,
runup = :runup,
runup05 = :runup05,
runup95 = :runup95,
setup = :setup,
swash = :swash,
inc_swash = :incSwash,
infrag_swash = :infragSwash
MYSQL;
$saveDataPointParams = array('selectedFileId' => $fileId, 'timeSeriesId' => $timeSeriesId, 'dateTime' => $this->getMysqlDateTime(), 'hs' => $this->hs, 'pp' => $this->pp, 'twl' => $this->twl, 'twl05' => $this->twl05, 'twl95' => $this->twl95, 'runup' => $this->runup, 'runup05' => $this->runup05, 'setup' => $this->setup, 'swash' => $this->swash, 'incSwash' => $this->incSwash, 'infragSwash' => $this->infragSwash);
DatabaseHelper::query($DBH, $saveDataPointQuery, $saveDataPointParams);
}
public function delete()
{
if ($this->getId() > 0) {
$databaseHelper = new DatabaseHelper();
$databaseHelper->query("DELETE FROM `receipt_product` WHERE id = " . $this->getId());
}
}
