Exemple #1
0
 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'];
         }
     }
 }
Exemple #2
0
 /**
  * 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;
 }
Exemple #3
0
 /**
  * @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;
 }
Exemple #4
0
 /**
  * @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);
 }
Exemple #5
0
 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());
         }
     }
 }
Exemple #6
0
 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;
    }
Exemple #8
0
    /**
     * 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;
        }
    }
Exemple #9
0
    /**
     * 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();
    }
Exemple #10
0
    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();
    }
Exemple #11
0
    $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);
    }
Exemple #13
0
 public function delete()
 {
     if ($this->getId() > 0) {
         $databaseHelper = new DatabaseHelper();
         $databaseHelper->query("DELETE FROM `receipt_product` WHERE id = " . $this->getId());
     }
 }