added docs

Feature/api doc

See merge request tjohn/cc-data!36

.dockerignore

@@ -0,0 +1,2 @@
+node_modules
+npm-debug.log

.gitignore

@@ -1,3 +1,7 @@
+# credentials
+.env
+config.json
+
 # compiled output
 /dist
 

.vscode/launch.json

@@ -0,0 +1,23 @@
+{
+  // Verwendet IntelliSense zum Ermitteln möglicher Attribute.
+  // Zeigen Sie auf vorhandene Attribute, um die zugehörigen Beschreibungen anzuzeigen.
+  // Weitere Informationen finden Sie unter https://go.microsoft.com/fwlink/?linkid=830387
+  "version": "0.2.0",
+  "configurations": [
+    {
+      "console": "integratedTerminal",
+      "internalConsoleOptions": "neverOpen",
+      "name": "NodeJS: nodemon debug",
+      "program": "${workspaceFolder}/backend/index.js",
+      "request": "launch",
+      // "restart": true,
+      "runtimeExecutable": "node",
+      // "runtimeExecutable": "nodemon",
+      "skipFiles": [
+        "<node_internals>/**"
+      ],
+      "type": "node",
+      "envFile": "${workspaceFolder}/backend/.env",
+    }
+  ]
+}

Dockerfile

@@ -0,0 +1,24 @@
+FROM node:12
+
+# Create app directory
+RUN mkdir -p /usr/src/app
+WORKDIR /usr/src/app
+
+# Install app dependencies
+# A wildcard is used to ensure both package.json AND package-lock.json are copied
+# where available (npm@5+)
+COPY backend/package*.json ./
+
+RUN npm install
+
+# Bundle app source
+COPY backend .
+
+EXPOSE 3000
+ENV PORT=3000
+ENV METEOSTAT_API_KEY=LMlDskju
+ENV DB_HOST=lhinderberger.dev
+ENV DB_USER=root
+ENV DB_PASSWORD=devtest
+ENV DB_PORT=3306
+CMD [ "node", "index.js" ]

README.md

@@ -24,7 +24,7 @@ Campus Cup AKMC  Data  Traveloptimizer
  - temperature=NUMBER,NUMBER
  - raindays=NUMBER,NUMBER
  - sunhours=NUMBER,NUMBER
- - percipitation=NUMBER,NUMBER
+ - precipitation=NUMBER,NUMBER
 
 __Examples:__
 http://localhost:3000/v1/search?from=2020-06-14&to=2020-07-29&temperature=27,29&raindays=8,12&sunhours=250,300

backend/.env

@@ -1,5 +0,0 @@
-METEOSTAT_API_KEY=LMlDskju
-DB_HOST=localhost
-DB_USER=root
-DB_PASSWORD=devtest
-DB_PORT=3306

backend/.env.sample

@@ -0,0 +1,10 @@
+PORT=3000
+METEOSTAT_API_KEY=LMlDskju
+METEOSTAT_API_KEY_V2=O9X1xxKjheNwF1vfLcdRMmQ9JlobOugL
+DB_HOST=lhinderberger.dev
+DB_USER=root
+DB_PASSWORD=devtest
+DB_PORT=3306
+DATABASE=travopti
+GOOGLE_CLOUD_APIS=AIzaSyCeMBLfpqTp0IVB7Xipx6ekRQFUBjPacQc
+SHOW_MATCH_VALUE=1

backend/Dockerfile

@@ -0,0 +1,27 @@
+FROM node:12 
+
+# Create app directory
+WORKDIR /usr/src/app
+
+# COPY package.json .
+# For npm@5 or later, copy package-lock.json as well
+COPY package.json package-lock.json ./
+
+# Install app dependencies
+RUN npm install
+
+# Bundle app source
+COPY . .
+
+ENV PORT=3000
+ENV METEOSTAT_API_KEY=LMlDskju
+ENV DB_HOST=127.0.0.1
+ENV DB_USER=root
+ENV DB_PASSWORD=devtest
+ENV DB_PORT=3306
+ENV DATABASE=travopti
+
+EXPOSE 3000
+
+# Start Node server
+CMD [ "node", "./index.js" ]

backend/app.js

@@ -1,276 +0,0 @@
-const express = require('express')
-const moment = require('moment')
-const _ = require('lodash')
-const db = require('./mysql')
-const score = require('./score')
-const transformer = require('./transformer')
-const climate = require('./climate')
-const base = require('./base64')
-
-const app = express()
-const port = 3000
-//const multiplier_temp = 5
-const multiplier = {
-  temperature_mean: 5,
-  percipitation: 3.5,
-  raindays: 3,
-  sunhours: 2.5,
-}
-
-const samplePresets = [
-  {
-    id: 29837,
-    parameter: "temperature",
-    label: "warm",
-    values: [22, 25]
-  }
-]
-
-app.get('/', (req, res) => res.send('Hello Timo!'))
-app.get('/v1/regions', (req, res) => getAllRegions().then(x => res.json({ data: x })))
-app.get('/v1/presets', (req, res) => res.json({ data: samplePresets}))
-app.get('/v1/search', searchHandler)
-app.get('/v1/update/climate', climateUpdateHandler)
-
-app.listen(port, () => console.log(`Travopti backend listening at http://localhost:${port}`))
-
-function climateUpdateHandler(req, res) {
-  let parameter = []
-  if (req.query.startDate) parameter.push(req.query.startDate)
-  if (req.query.endDate) parameter.push(req.query.endDate)
-  climate.update(...parameter).then(x => {
-    res.send(x)
-  }).catch(e => {
-    let result = {
-      message: 'error during update process. check backend logs.',
-      error: e
-    }
-    res.send(result)
-  }
-
-  )
-}
-
-function searchHandler(req, res) {
-  let response = {}
-  
-  response.meta = {
-    params: req.params,
-    query: req.query,
-    headers: req.headers
-  }
-  
-  let q = req.query.q ? base.base64ToObj(req.query.q) : req.query
-  console.log('Q:', q)
-  
-  let queryObj = {}
-  if (q.temperature) queryObj['temperature_mean'] = q.temperature
-  if (q.percipitation) queryObj['percipitation'] = q.percipitation
-  if (q.raindays) queryObj['raindays'] = q.raindays
-  if (q.sunhours) queryObj['sunhours'] = q.sunhours
-  
-  scoreAndSearch(q.from, q.to, queryObj).then(searchResults => {
-    response.data = searchResults
-    res.json(response)
-  }).catch(e => {
-    console.log(e)
-    res.json(e.message)
-  })
-}
-
-async function scoreAndSearch(from, to, queries) {
-  // TODO break funtion into parts when implementing non-climate queries and modularize (new file)
-
-  console.log('search')
-
-  // get Min and Max values for each Parameter
-  const minMax = await getClimateMinMax()
-  
-  // randomize if empty queries
-  if (_.isEmpty(queries)) {
-    let t = _.round(_.random(minMax.min.temperature_mean, minMax.max.temperature_mean-5),0)
-    let p = _.round(_.random(minMax.min.percipitation, minMax.max.percipitation - 50), 0)
-    let r = _.round(_.random(minMax.min.raindays, minMax.max.raindays - 5), 0)
-    let s = _.round(_.random(minMax.min.sunhours, minMax.max.sunhours - 50), 0)
-    queries.temperature_mean = `${t},${t + 5}`
-    queries.percipitation = `${p},${p + 50}`
-    queries.raindays = `${r},${r + 5}`
-    queries.sunhours = `${s},${s + 50}`
-  }
-  queries = oldToNewQuerySyntax(queries)
-  console.log(queries)
-  
-  // TODO simplify and remove support for old query syntaax
-  let monthFrom = 0
-  let monthTo = 0
-  let dayFrom = 0
-  let dayTo = 0
-  
-  if (_.isNumber(from) && _.isNumber(to)) {
-    let dateFrom = moment(from).toDate()
-    let dateTo = moment(to).toDate()
-    monthFrom = dateFrom.getMonth()
-    monthTo = dateTo.getMonth()
-    dayFrom = dateFrom.getDay()
-    dayTo = dateTo.getDay()
-    if (moment(dateFrom).add(23, 'hours').isAfter(moment(dateTo))) throw new Error("ERR: 'to' must be at least one day after 'from'.")
-  } else {
-    //  to still support old query syntax 
-    let re = /([12]\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01]))/i;
-    monthFrom = Number(from.split("-")[1])
-    monthTo = Number(to.split("-")[1])
-    dayFrom = Number(from.split("-")[2])
-    dayTo = Number(to.split("-")[2])
-    if (!from.match(re) || !to.match(re)) throw new Error("ERR: invalid parameter:",from,to)
-    if (moment(from, 'YYYY-MM-DD').add(23, 'hours').isAfter(moment(to, 'YYYY-MM-DD'))) throw new Error("ERR: 'to' must be at least one day after 'from'.")
-  }
-  
-  // -- Prepare search --
-  // to calculate average if traveldates are in more than one month
-  let travelPeriods = []
-  if (monthFrom === monthTo) {
-    let element = {
-      month: monthFrom,
-      days: dayTo - dayFrom
-    }
-    travelPeriods.push(element)
-  } else {
-    for (let index = monthFrom; index <= monthTo; index++) {
-      let element = {}
-      if (index === monthFrom) {
-        element = {
-          month: index,
-          days: 32 - dayFrom
-        }
-      } else if (index === monthTo) {
-        element = {
-          month: index,
-          days: dayTo
-        }
-      } else {
-        element = {
-          month: index,
-          days: 30
-        }
-      }
-      travelPeriods.push(element)
-    }
-  }
-
-  // calculate detail scores
-  let detailScores = await Promise.all(travelPeriods.map(async period => {
-    period.climate = await getAllRegionsWithClimatePerMonth(period.month)
-    period.scores = {}
-    Object.entries(queries).forEach(([key, value]) => {
-      // console.log('key',key)
-      // console.log('val', value)
-      period.scores[key] = calculateScores(key, period.climate, value[0], value[1], minMax)
-    });
-    return period
-  }));
-  
-  
-  // calculate ratio and transform into target object
-  return {
-    results: transformer.transform(detailScores),
-    debug: {
-      detailScores: detailScores,
-      minMax: minMax
-    }
-  }
-}
-
-function calculateScores(type, regionDataRows, searchLowParam, searchMaxParam, minMax) {
-  console.log('calculateScores for', type)
-  let result = regionDataRows.map(x => {
-    const sc = Math.round(score.calculateScoreRange(minMax.min[type], minMax.max[type], multiplier[type], x[type], searchLowParam, searchMaxParam) * 100) / 100
-
-    return {
-      region_id: x.region_id,
-      type: type,
-      value: x[type],
-      score: x[type] === null ? null : sc
-    }
-  
-  })
-  return result
-}
-
-async function getClimateMinMax() {
-  console.log('getClimateMinMax')
-  const sqlMin = `SELECT 
-  MIN(temperature_mean) AS temperature_mean,
-  MIN(temperature_mean_min) AS temperature_mean_min, 
-  MIN(temperature_mean_max) AS temperature_mean_max, 
-  MIN(percipitation) AS percipitation, 
-  MIN(raindays) AS raindays, 
-  MIN(sunshine) AS sunhours
-  FROM region_climate`
-  const sqlMax = `SELECT 
-  MAX(temperature_mean) AS temperature_mean,
-  MAX(temperature_mean_min) AS temperature_mean_min, 
-  MAX(temperature_mean_max) AS temperature_mean_max, 
-  MAX(percipitation) AS percipitation, 
-  MAX(raindays) AS raindays, 
-  MAX(sunshine) AS sunhours
-  FROM region_climate`
-  const [qResMin, qResMax] = await Promise.all([getQueryRows(sqlMin), getQueryRows(sqlMax)])
-  //console.log(qResMin)
-  return { min: qResMin[0], max: qResMax[0] } 
-}
-
-async function getQueryRows(sql) {
-  //console.log('getQueryRows')
-  const [rows, fields] = await db.execute(sql)
-  return rows
-}
-
-function getAllRegions() {
-  const sql = `SELECT 
-  regions.id AS region_id,
-  regions.region AS name,
-  regions.country_id AS country_id,
-  countries.country AS country,
-  regions.meteostat_id AS meteostat_id
-  FROM regions 
-  JOIN countries 
-  ON regions.country_id = countries.id`
-  return getQueryRows(sql)
-}
-
-function getClimatePerRegionAndMonth(regionId, month) {
-  console.log('getClimatePerRegionAndMonth')
-  const sql = `SELECT region_id, AVG(temperature_mean), AVG(temperature_mean_min), AVG(temperature_mean_max), AVG(percipitation), AVG(sunshine) FROM region_climate WHERE month = ${month} AND region_id = ${regionId}`
-  return getQueryRows(sql)
-}
-
-function getAllRegionsWithClimatePerMonth(month) {
-  console.log('getAllRegionsWithClimatePerMonth')
-  const sql = `SELECT 
-	region_climate.region_id AS region_id,
-	regions.country_id AS country_id,
-	regions.region AS name,
-	ROUND(AVG(region_climate.temperature_mean), 1) AS temperature_mean, 
-	ROUND(AVG(region_climate.temperature_mean_min), 1) AS temperature_mean_min, 
-	ROUND(AVG(region_climate.temperature_mean_max), 1) AS temperature_mean_max, 
-	ROUND(AVG(region_climate.percipitation), 1) AS percipitation, 
-	ROUND(AVG(region_climate.raindays), 1) AS raindays, 
-	ROUND(AVG(region_climate.sunshine), 1) AS sunhours
-	FROM region_climate JOIN regions ON region_climate.region_id = regions.id WHERE region_climate.month = ${month} GROUP BY region_id`
-  return getQueryRows(sql)
-}
-
-function oldToNewQuerySyntax(queries) {
-  let res = {}
-  try {
-    if (queries.temperature_mean) res.temperature_mean = [queries.temperature_mean.split(',')[0], queries.temperature_mean.split(',')[1]]
-    if (queries.percipitation) res.percipitation = [queries.percipitation.split(',')[0], queries.percipitation.split(',')[1]]
-    if (queries.raindays) res.raindays = [queries.raindays.split(',')[0], queries.raindays.split(',')[1]]
-    if (queries.sunhours) res.sunhours = [queries.sunhours.split(',')[0], queries.sunhours.split(',')[1]]
-    console.log('queries successfully transformed');
-  } catch (error) {
-    console.log('queries are ok');
-    return queries
-  }
-  return res
-}

backend/climate.js

@@ -1,124 +0,0 @@
-require('dotenv').config()
-const mysql = require('mysql2/promise');
-const axios = require('axios')
-
-const rangeStartDate = '2010-01'
-const rangeEndDate = '2018-12'
-
-exports.update = async function (startDate = rangeStartDate, endDate = rangeEndDate) {
-  console.log('update climate with:', startDate, endDate);
-  
-  const connection = await mysql.createConnection({
-    host: process.env.DB_HOST,
-    user: process.env.DB_USER,
-    password: process.env.DB_PASSWORD,
-    port: process.env.DB_PORT,
-    database: 'travopti'
-  });
-  const [result, fields] = await connection.execute(`SELECT * FROM regions WHERE meteostat_id IS NOT NULL`)
-
-  // let temp = await Promise.all(result.map(x => createClimateObject(x)))
-  // let final = temp.reduce((total, element) => total.concat(element), [])
-
-  // await writeToDatabase(connection, final)
-  
-  let temp2 = await Promise.all(result.map(x => createClimateObjectFrom(x, startDate, endDate)))
-  let final2 = temp2.reduce((total, element) => total.concat(element), [])
-
-  await writeToDatabase(connection, final2)
-
-  connection.end();
-  let response = 'database update complete. see backend logs for info.'
-  console.log(response)
-  return response
-}
-
-// async function createClimateObject(src) {
-//   let response
-//   try {
-//     response = await axios.get(`https://api.meteostat.net/v1/climate/normals?station=${src.meteostat_id}&key=${process.env.METEOSTAT_API_KEY}`)
-//   } catch (error) {
-//     console.log("skipping: couldn't find results for following region: ")
-//     console.log(src.region + " with meteostat_id " + src.meteostat_id)
-//     return []
-//   }
-//   if (!response.data.data) {
-//     console.log("skipping: no data for station meteostat_id " + src.meteostat_id + " (" + src.region + ")")
-//     return []
-//   }
-//   let results = []
-//   for (let index = 1; index <= 12; index++) {
-//     let result = {
-//       region: src.region,
-//       region_id: src.id,
-//       month: index,
-//       temperature: Object.values(response.data.data.temperature)[index - 1] ? Object.values(response.data.data.temperature)[index - 1] : null,
-//       temperature_min: Object.values(response.data.data.temperature_min)[index - 1] ? Object.values(response.data.data.temperature_min)[index - 1] : null,
-//       temperature_max: Object.values(response.data.data.temperature_max)[index - 1] ? Object.values(response.data.data.temperature_max)[index - 1] : null,
-//       precipitation: Object.values(response.data.data.precipitation)[index - 1] ? Object.values(response.data.data.precipitation)[index - 1] : null,
-//       sunshine: Object.values(response.data.data.sunshine)[index - 1] ? Object.values(response.data.data.sunshine)[index - 1] : null,
-//     }
-//     results.push(result)
-//   }
-//   return results
-// }
-
-async function createClimateObjectFrom(src, startDate, endDate) {
-  let response
-  try {
-    response = await axios.get(`https://api.meteostat.net/v1/history/monthly?station=${src.meteostat_id}&start=${startDate}&end=${endDate}&key=${process.env.METEOSTAT_API_KEY}`)
-  } catch (error) {
-    console.log("skipping createClimateObjectFrom: couldn't find results for following region: ")
-    console.log(src.region + " with meteostat_id " + src.meteostat_id)
-    console.log(error)
-    return []
-  }
-  if (!response.data.data) {
-    console.log("skipping: no data for station meteostat_id " + src.meteostat_id + " (" + src.region + ")")
-    return []
-  }
-  let results = response.data.data.map(element => {
-    let result = {
-      region: src.region,
-      region_id: src.id,
-      year: element.month.split("-")[0],
-      month: element.month.split("-")[1],
-      temperature: element.temperature_mean,
-      temperature_min: element.temperature_mean_min,
-      temperature_max: element.temperature_mean_max,
-      precipitation: element.precipitation,
-      raindays: element.raindays,
-      sunshine: element.sunshine,
-      humidity: element.humidity ? element.humidity : null 
-    }
-    //console.log(result)
-    return result
-  })
-  return results
-}
-
-async function writeToDatabase(dbConnection, climateObjArr) {
-  climateObjArr.forEach(async (element) => {
-    //console.log(element)
-    try {
-      if (!element.year) {
-        await dbConnection.execute(`
-          REPLACE INTO region_climate (region_id, year, month, temperature_mean, temperature_mean_min, temperature_mean_max, percipitation, sunshine)
-          VALUES (${element.region_id}, 0, ${element.month}, ${element.temperature}, ${element.temperature_min}, ${element.temperature_max}, ${element.precipitation}, ${element.sunshine});`)
-      } else {
-        await dbConnection.execute(`
-            REPLACE INTO region_climate (region_id, year, month, temperature_mean, temperature_mean_min, temperature_mean_max, percipitation, sunshine, humidity, raindays)
-            VALUES (${element.region_id}, ${element.year}, ${element.month}, ${element.temperature}, ${element.temperature_min}, ${element.temperature_max}, ${element.precipitation}, ${element.sunshine}, ${element.humidity}, ${element.raindays});`)
-      }
-    } catch (error) {
-      if (error.code !== 'ER_DUP_ENTRY') {
-        console.log("element which causes problems: ")
-        console.log(element)
-        console.log("query which causes problems: ")
-        console.log(error)
-      } else {
-        console.log(element.region + ": " + error.sqlMessage)
-      }
-    }
-  });
-}

backend/index.js

@@ -0,0 +1,94 @@
+const express = require("express");
+const bodyParser = require("body-parser");
+const path = require("path");
+const morgan = require("morgan");
+const dbConnection = require("./util/dbConnection");
+const fs = require("fs");
+const cors = require("cors");
+require('dotenv').config()
+
+// credentials
+const port = process.env.PORT
+
+// Router
+const search = require("./routes/search");
+const regions = require("./routes/regions");
+const countries = require("./routes/countries");
+const places = require("./routes/place");
+const update = require("./routes/update");
+
+const app = express();
+
+// Swagger API doc set up
+const swaggerJsdoc = require('swagger-jsdoc');
+const swaggerUi = require('swagger-ui-express');
+const swaggerOptions = {
+    swaggerDefinition: {
+        openapi: "3.0.0",
+        info: {
+            title: "TravOpti API",
+            version: "1.0.0",
+            description:
+                "Enable intrest controlled region searching with this API\n" +
+                "No API Key required.",
+            license: {
+                name: "Licensing Pending",
+                url: "https://www.youtube.com/watch?v=dQw4w9WgXcQ&feature=youtu.be"
+            },
+            contact: {
+                name: "travOpti",
+                url: "https://travopti.de/home",
+                email: "feedback@travopti.de"
+            }
+        },
+        servers: [
+            {
+                url: "https://travopti.de/api/v1"
+            }
+        ]
+    },
+    apis: [
+        "./Routes/*.js",
+        "./Models/handleClimateUpdate.js",
+        "./Models/handleClimateUpdateV2.js",
+    ]
+};
+const swaggerDocs = swaggerJsdoc(swaggerOptions);
+
+(async () => {
+    try {
+        // Connect to MariaDB
+        const dbConn = await dbConnection();
+
+        // Express middleware
+        app.use(morgan("dev"));
+        app.use(express.static(path.join(__dirname, "../../dist")));
+        app.use(bodyParser.json());
+        app.use(cors());
+        app.use('/api/v1/doc', swaggerUi.serve, swaggerUi.setup(swaggerDocs, {explorer: false, docExpansion: "list"}));
+
+        // Express routes
+        app.use(search(dbConn));
+        app.use(regions(dbConn));
+        app.use(countries(dbConn));
+        app.use(places(dbConn));
+        app.use(update(dbConn))
+
+        app.use((err, req, res, next) => {
+            // 500
+            if (true) {
+                next();
+            } else {
+                res.status(500).send();
+            }
+        });
+
+        // Start webserver
+        app.listen(port, () => {
+            console.log(`Travopti backend listening at http://localhost:${port}`)
+        });
+    } catch (error) {
+        console.error("Failed to start the webserver");
+        console.error(error);
+    }
+})();

backend/models/getCountries.js

@@ -0,0 +1,10 @@
+module.exports = async (dbConn) => {
+    const countries = await dbConn.query(
+        `SELECT countries.id AS country_id,
+                countries.country AS name,
+                countries.description
+        FROM countries`
+    );
+    return countries;
+};
+

backend/models/getCountryById.js

@@ -0,0 +1,12 @@
+module.exports = async (dbConn, id) => {
+    const country = await dbConn.query(
+        `SELECT countries.id AS country_id,
+                countries.country AS name,
+                countries.description
+        FROM countries
+        WHERE countries.id = ?`,
+        [id]
+    );
+    return country;
+};
+

backend/models/getPlace.js

@@ -0,0 +1,19 @@
+const axios = require("axios")
+const getPlacePhoto = require("./getPlacePhoto.js")
+
+const fields = "photos,place_id,name,rating,geometry" // Parameters for Google Place API
+
+module.exports = async (q) => {
+    const res = await axios.get(
+        `https://maps.googleapis.com/maps/api/place/findplacefromtext/json?inputtype=textquery&fields=${fields}&input=${q}&key=${process.env.GOOGLE_CLOUD_APIS}`)
+
+    // Photo url is not returned by default since it overuses Google Place API
+    /*
+    for (let candidate of res.data.candidates) {
+        for (let photo of candidate.photos) {
+            photo.url = await getPlacePhoto(photo.photo_reference)
+        }
+    }
+    */
+    return res.data
+}

backend/models/getPlaceNearby.js

@@ -0,0 +1,20 @@
+const axios = require("axios")
+const getPlacePhoto = require("./getPlacePhoto.js")
+
+const radius = 20000 // Search radius in meters
+const rankby = "prominence" // Sorting of results
+const types = "tourist_attraction" // Category which shall be searched
+
+module.exports = async (lat, lng) => {
+    const res = await axios.get(
+        `https://maps.googleapis.com/maps/api/place/nearbysearch/json?location=${lat},${lng}&radius=${radius}&type=${types}&rankby=${rankby}&key=${process.env.GOOGLE_CLOUD_APIS}`
+    );
+    // Photo url is not returned by default since it overuses Google Place API
+    /*
+    for (let result of res.data.results) {
+        for (let photo of result.photos) {
+            photo.url = await getPlacePhoto(photo.photo_reference)
+        }
+    }*/
+    return res.data
+}

backend/models/getPlacePhoto.js

@@ -0,0 +1,10 @@
+const axios = require("axios")
+
+module.exports = async (photoref) => {
+    const res = await axios.get(
+        `https://maps.googleapis.com/maps/api/place/photo?photoreference=${photoref}&maxwidth=1600&key=${process.env.GOOGLE_CLOUD_APIS}`
+    );
+
+    const url = res.request.res.responseUrl
+    return url
+}

backend/models/getRegionById.js

@@ -0,0 +1,61 @@
+const arrayFormatting = require("../util/databaseArrayFormatting.js")
+
+module.exports = async (dbConn, id) => {
+    const res = await dbConn.query(
+            `SELECT regions.id AS region_id,
+                regions.region AS name,
+                countries.country AS country,
+                regions.description AS description,
+                regions.lon AS lon,
+                regions.lat AS lat,
+                rcma.temperature_mean AS temperature_mean,
+                rcma.temperature_mean_min AS temperature_mean_min,
+                rcma.temperature_mean_max AS temperature_mean_max,
+                rcma.precipitation AS precipitation,
+                rcma.rain_days AS rain_days,
+                rcma.sun_hours AS sun_hours,
+                rcma.humidity AS humidity,
+                regions_byt.average_per_day_costs AS average_per_day_costs,
+                rtma.avg_price_relative AS avg_price_relative,
+                regions_byt.accommodation_costs AS accommodation_costs,
+                regions_byt.food_costs AS food_costs,
+                regions_byt.water_costs AS water_costs,
+                regions_byt.local_transportation_costs AS local_transportation_costs,
+                regions_byt.entertainment_costs AS entertainment_costs,
+                regions_byt.alcohol_costs AS alcohol_costs
+        FROM regions
+        LEFT JOIN countries ON regions.country_id = countries.id
+        LEFT JOIN (SELECT rcma.region_id, 
+                GROUP_CONCAT(rcma.temperature_mean ORDER BY rcma.month SEPARATOR ', ') AS temperature_mean, 
+                GROUP_CONCAT(rcma.temperature_mean_min ORDER BY rcma.month SEPARATOR ', ') AS temperature_mean_min, 
+                GROUP_CONCAT(rcma.temperature_mean_max ORDER BY rcma.month SEPARATOR ', ') AS temperature_mean_max, 
+                GROUP_CONCAT(rcma.precipitation ORDER BY rcma.month SEPARATOR ', ') AS precipitation, 
+                GROUP_CONCAT(rcma.rain_days ORDER BY rcma.month SEPARATOR ', ') AS rain_days, 
+                GROUP_CONCAT(rcma.sun_hours ORDER BY rcma.month SEPARATOR ', ') AS sun_hours, 
+                GROUP_CONCAT(rcma.humidity ORDER BY rcma.month SEPARATOR ', ') AS humidity
+              FROM region_climate_monthly_avg AS rcma
+              GROUP BY rcma.region_id) rcma ON rcma.region_id = regions.id
+        LEFT JOIN regions_byt ON regions.id = regions_byt.region_id
+        LEFT JOIN (SELECT rtma.region_id,
+              GROUP_CONCAT(rtma.avg_price_relative ORDER BY rtma.month SEPARATOR ', ') AS avg_price_relative
+              FROM regions_trivago_monthly_avg AS rtma
+              GROUP BY rtma.region_id) rtma
+        ON regions.id = rtma.region_id
+        WHERE regions_byt.travelstyle = 1
+        AND regions.id = ?`,
+        [id]
+    );
+    const region = res[0]
+
+    region.avg_price_relative = arrayFormatting(region.avg_price_relative);
+    region.temperature_mean = arrayFormatting(region.temperature_mean);
+    region.temperature_mean_min = arrayFormatting(region.temperature_mean_min);
+    region.temperature_mean_max = arrayFormatting(region.temperature_mean_max);
+    region.precipitation = arrayFormatting(region.precipitation);
+    region.rain_days = arrayFormatting(region.rain_days);
+    region.sun_hours = arrayFormatting(region.sun_hours);
+    region.humidity = arrayFormatting(region.humidity);
+
+    return region;
+};
+

backend/models/getRegionNearbyById.js

@@ -0,0 +1,21 @@
+const arrayFormatting = require("../util/databaseArrayFormatting.js")
+
+module.exports = async (dbConn, id) => {
+    const region_nearby = await dbConn.query(
+        `SELECT id as place_id,
+        region_id as region_id,
+        name as place_name,
+        lon as lon,
+        lat as lat,
+        rating as rating,
+        vicinity as vicinity,
+        photo_reference as photo_reference,
+        img_url as img_url
+        FROM regions_nearby
+        WHERE region_id = ?`,
+        [id]
+    );
+
+    return region_nearby;
+};
+

backend/models/getRegions.js

@@ -0,0 +1,69 @@
+const arrayFormatting = require("../util/databaseArrayFormatting.js");
+const { allTagsWithValues } = require("./getTags.js");
+
+module.exports = async (dbConn) => {
+    const sqlRegions = `SELECT regions.id AS region_id,
+                regions.region AS name,
+                countries.country AS country,
+                regions.description AS description,
+                regions.lon AS lon,
+                regions.lat AS lat,
+                rcma.temperature_mean AS temperature_mean,
+                rcma.temperature_mean_min AS temperature_mean_min,
+                rcma.temperature_mean_max AS temperature_mean_max,
+                rcma.precipitation AS precipitation,
+                rcma.rain_days AS rain_days,
+                rcma.sun_hours AS sun_hours,
+                rcma.humidity AS humidity,
+                regions_byt.average_per_day_costs AS average_per_day_costs,
+                rtma.avg_price_relative AS avg_price_relative,
+                regions_byt.accommodation_costs AS accommodation_costs,
+                regions_byt.food_costs AS food_costs,
+                regions_byt.water_costs AS water_costs,
+                regions_byt.local_transportation_costs AS local_transportation_costs,
+                regions_byt.entertainment_costs AS entertainment_costs,
+                regions_byt.alcohol_costs AS alcohol_costs
+        FROM regions
+        LEFT JOIN countries ON regions.country_id = countries.id
+        LEFT JOIN (SELECT rcma.region_id, 
+                GROUP_CONCAT(rcma.temperature_mean ORDER BY rcma.month SEPARATOR ', ') AS temperature_mean, 
+                GROUP_CONCAT(rcma.temperature_mean_min ORDER BY rcma.month SEPARATOR ', ') AS temperature_mean_min, 
+                GROUP_CONCAT(rcma.temperature_mean_max ORDER BY rcma.month SEPARATOR ', ') AS temperature_mean_max, 
+                GROUP_CONCAT(rcma.precipitation ORDER BY rcma.month SEPARATOR ', ') AS precipitation, 
+                GROUP_CONCAT(rcma.rain_days ORDER BY rcma.month SEPARATOR ', ') AS rain_days, 
+                GROUP_CONCAT(rcma.sun_hours ORDER BY rcma.month SEPARATOR ', ') AS sun_hours, 
+                GROUP_CONCAT(rcma.humidity ORDER BY rcma.month SEPARATOR ', ') AS humidity
+              FROM region_climate_monthly_avg AS rcma
+              GROUP BY rcma.region_id) rcma ON rcma.region_id = regions.id
+        LEFT JOIN regions_byt ON regions.id = regions_byt.region_id
+        LEFT JOIN (SELECT rtma.region_id,
+              GROUP_CONCAT(rtma.avg_price_relative ORDER BY rtma.month SEPARATOR ', ') AS avg_price_relative
+              FROM regions_trivago_monthly_avg AS rtma
+              GROUP BY rtma.region_id) rtma
+        ON regions.id = rtma.region_id
+        WHERE regions_byt.travelstyle = 1`
+
+    const [regions, tags] = await Promise.all([dbConn.query(sqlRegions), allTagsWithValues(dbConn)])
+
+    for (k = 0; k < regions.length; k++) {
+        regions[k].avg_price_relative = arrayFormatting(regions[k].avg_price_relative);
+        regions[k].temperature_mean = arrayFormatting(regions[k].temperature_mean);
+        regions[k].temperature_mean_min = arrayFormatting(regions[k].temperature_mean_min);
+        regions[k].temperature_mean_max = arrayFormatting(regions[k].temperature_mean_max);
+        regions[k].precipitation = arrayFormatting(regions[k].precipitation);
+        regions[k].rain_days = arrayFormatting(regions[k].rain_days);
+        regions[k].sun_hours = arrayFormatting(regions[k].sun_hours);
+        regions[k].humidity = arrayFormatting(regions[k].humidity);
+    }
+
+
+    return regions.map(region => {
+        region.tags = tags.filter(tag => tag.region_id === region.region_id).map(tag => {
+            delete tag.region_id
+            return tag
+        })
+
+        return region
+    });
+};
+

backend/models/getSearchPresets.js

@@ -0,0 +1,21 @@
+module.exports = async (dbConn) => {
+    let presets = await dbConn.query(
+            `SELECT search_presets.id AS preset_id,
+                search_presets.parameter AS parameter,
+                search_presets.name AS tag_label,
+                CASE
+                WHEN value_2 is NULL THEN value_1
+                ELSE CONCAT(search_presets.value_1,"|",search_presets.value_2)
+         END AS "value"
+         FROM search_presets`
+    );
+
+    for (k = 0; k < presets.length; k++) {
+        const value = presets[k].value
+        presets[k].value = value.split("|");
+        for (i = 0; i < presets[k].value.length; i++) {
+            presets[k].value[i] = parseFloat(presets[k].value[i])
+        }
+    }
+    return presets;
+};

backend/models/getTags.js

@@ -0,0 +1,22 @@
+exports.getUniqueTags = async (dbConn) => {
+  let tags = await dbConn.query(
+    `SELECT DISTINCT name FROM region_feedback;`
+  );
+  return tags;
+};
+
+exports.allTagsWithValues = async (dbConn) => {
+  let tags = await dbConn.query(
+    `SELECT region_id, name, value FROM region_feedback;`
+  );
+  return tags;
+};
+
+exports.getTagsByRegionId = async (dbConn, id) => {
+  let tags = await dbConn.query(
+      `SELECT region_id, name, value 
+       FROM region_feedback
+       WHERE region_id = ${id};`
+  );
+  return tags;
+};

backend/models/handleClimateUpdate.js

@@ -0,0 +1,89 @@
+const axios = require('axios')
+const _ = require('lodash')
+
+// Constants
+// TODO: Automatically retrieve dates via aviable Data from database and get rid of "random" dates
+const rangeStartDate = '2019-01' // If no date is given, this date will be used as startDate
+const rangeEndDate = '2020-05'// If no date is given, this date will be used as endDate
+
+// TODO: call method periodically, not over API (fine for prototyping, tho)
+module.exports = async (dbConn, startDate = rangeStartDate, endDate = rangeEndDate) => {
+  console.log('update climate with:', startDate, endDate);
+
+  const result = await dbConn.query(`SELECT * FROM regions WHERE meteostat_id IS NOT NULL`)
+
+  const climateObject = await Promise.all(result.map(src => {
+    return createClimateObjectFrom(src, startDate, endDate)
+  }))
+  const climateObjectArr = climateObject.reduce((total, element) => total.concat(element), [])
+
+  await writeToDatabase(dbConn, climateObjectArr)
+
+  const res = `region_climate update complete. see backend logs for info.`
+  return res
+}
+
+async function createClimateObjectFrom(src, startDate, endDate) {
+  let res
+  if (src.meteostat_id === '') {
+    console.log("skipping: no meteostat id ")
+    return []
+  }
+  try {
+    res = await axios.get(`https://api.meteostat.net/v1/history/monthly?station=${src.meteostat_id}&start=${startDate}&end=${endDate}&key=${process.env.METEOSTAT_API_KEY}`)
+  } catch (error) {
+    console.log("error while getting data from meteostat: couldn't find results for following region: ")
+    console.log(src.region + " with meteostat_id " + src.meteostat_id)
+    console.log(error)
+    return []
+  }
+  if (!res.data.data) {
+    console.log("skipping: no data for station with meteostat_id " + src.meteostat_id + " (" + src.region + ")")
+    return []
+  }
+  const retVal = res.data.data.map(element => {
+    let result = {
+      region: src.region,
+      region_id: src.id,
+      year: element.month.split("-")[0],
+      month: element.month.split("-")[1],
+      temperature_mean: element.temperature_mean,
+      temperature_mean_min: element.temperature_mean_min,
+      temperature_mean_max: element.temperature_mean_max,
+      precipitation: element.precipitation,
+      rain_days: element.raindays,
+      sun_hours: element.sunshine,
+      humidity: element.humidity ? element.humidity : null
+    }
+    return result
+  })
+  return retVal
+}
+
+async function writeToDatabase(dbConn, climateObjArr) {
+  for (const element of climateObjArr) {
+    try {
+      await dbConn.query(`
+      INSERT INTO region_climate 
+      (region_id, year, month, temperature_mean, temperature_mean_min, temperature_mean_max, precipitation, sun_hours, humidity, rain_days)
+      VALUES (${element.region_id}, ${element.year}, ${element.month}, ${element.temperature_mean}, ${element.temperature_mean_min}, ${element.temperature_mean_max}, ${element.precipitation}, ${element.sun_hours}, ${element.humidity}, ${element.rain_days})
+      ON DUPLICATE KEY UPDATE
+      temperature_mean = ${element.temperature_mean}, 
+      temperature_mean_min = ${element.temperature_mean_min}, 
+      temperature_mean_max = ${element.temperature_mean_max}, 
+      precipitation = ${element.precipitation}, 
+      sun_hours = ${element.sun_hours}, 
+      humidity = ${element.humidity}, 
+      rain_days = ${element.rain_days};`)
+    } catch (error) {
+      if (error.code !== 'ER_DUP_ENTRY') {
+        console.log("element which causes problems: ")
+        console.log(element)
+        console.log("query which causes problems: ")
+        console.log(error)
+      } else {
+        console.log(element.region + ": " + error.sqlMessage)
+      }
+    }
+  }
+};

backend/models/handleClimateUpdateV2.js

@@ -0,0 +1,110 @@
+const axios = require('axios')
+const _ = require('lodash')
+
+// Constants
+// TODO: Automatically retrieve dates via aviable Data from database and get rid of "random" dates
+const rangeStartDate = '2019-01-01' // If no date is given, this date will be used as startDate
+const rangeEndDate = '2019-12-31'// If no date is given, this date will be used as endDate
+
+// TODO: call method periodically, not over API (fine for prototyping, tho)
+module.exports = async (dbConn, startDate = rangeStartDate, endDate = rangeEndDate) => {
+    console.log('update climate with:', startDate, endDate);
+
+    const result = await dbConn.query(`SELECT id, region, lon, lat FROM regions`)
+
+    const climateObject = await Promise.all(result.map(src => {
+        return createClimateObjectFrom(src, startDate, endDate)
+    }))
+    const climateObjectArr = climateObject.reduce((total, element) => total.concat(element), [])
+
+    await writeToDatabase(dbConn, climateObjectArr)
+
+    const res = `region_climate update v2 complete. see backend logs for info.`
+    return climateObjectArr
+}
+
+async function createClimateObjectFrom(src, startDate, endDate) {
+    let res
+    try {
+        res = await axios.get(
+            `https://api.meteostat.net/v2/point/daily?lat=${src.lat}&lon=${src.lon}&start=${startDate}&end=${endDate}`,
+            {
+                headers: {
+                    "x-api-key": process.env.METEOSTAT_API_KEY_V2
+                }//,
+                //httpsAgent: agent
+            })
+    } catch (error) {
+        console.log("error while getting data from meteostat: couldn't find results for following region: ")
+        console.log(src.region,"with coords:",src.lon,src.lat)
+        console.log(error)
+        return []
+    }
+    if (!res.data.data) {
+        console.log("skipping: no data for station with meteostat_id " + src.meteostat_id + " (" + src.region + ")")
+        return []
+    }
+    const retVal = res.data.data.map(element => {
+        let result = {
+            region: src.region,
+            region_id: src.id,
+            year: element.date.split("-")[0],
+            month: element.date.split("-")[1],
+            day: element.date.split("-")[2],
+            temperature_mean: element.tavg,
+            temperature_mean_min: element.tmin,
+            temperature_mean_max: element.tmax,
+            precipitation: element.prcp,
+            rain_days: element.prcp > 2 ? 1:0, // More than 2mm => rainday
+            sun_hours: element.tsun/60
+        }
+        //console.log(result)
+        return result
+    })
+    return retVal
+}
+
+async function writeToDatabase(dbConn, climateObjArr) {
+    for (const element of climateObjArr) {
+        //console.log(element)
+        try {
+            await dbConn.query(`
+      INSERT INTO region_climate_day
+      (region_id, year, month, day, temperature_mean, temperature_mean_min, temperature_mean_max, precipitation, sun_hours, rain_days)
+      VALUES (${element.region_id}, ${element.year}, ${element.month}, ${element.day}, ${element.temperature_mean}, ${element.temperature_mean_min}, ${element.temperature_mean_max}, ${element.precipitation}, ${element.sun_hours}, ${element.rain_days})
+      ON DUPLICATE KEY UPDATE
+      temperature_mean = ${element.temperature_mean}, 
+      temperature_mean_min = ${element.temperature_mean_min}, 
+      temperature_mean_max = ${element.temperature_mean_max}, 
+      precipitation = ${element.precipitation}, 
+      sun_hours = ${element.sun_hours}, 
+      rain_days = ${element.rain_days};`)
+        } catch (error) {
+            if (error.code !== 'ER_DUP_ENTRY') {
+                console.log("element which causes problems: ")
+                console.log(element)
+                console.log("query which causes problems: ")
+                console.log(error)
+            } else {
+                console.log(element.region + ": " + error.sqlMessage)
+            }
+        }
+    }
+};
+
+/*
+INSERT INTO region_climate
+(region_id, YEAR, MONTH, temperature_mean, temperature_mean_min, temperature_mean_max, precipitation, rain_days, sun_hours)
+SELECT
+region_id,
+YEAR,
+MONTH,
+ROUND(AVG(temperature_mean),2) AS temperature_mean,
+MIN(temperature_mean_min) AS temperature_mean_min,
+MAX(temperature_mean_max) AS temperature_mean_max,
+ROUND(SUM(precipitation),2) AS precipitation,
+SUM(rain_days) AS rain_days,
+SUM(sun_hours) AS sun_hours
+FROM region_climate_day
+GROUP BY region_id, YEAR, month
+ */

backend/models/handleRegionLonLat.js

@@ -0,0 +1,34 @@
+const axios = require("axios")
+const getRegions = require("../models/getRegions.js")
+
+const fields = "geometry" // Parameters for Google Places API
+
+module.exports = async (dbConn) => {
+    const regions = await getRegions(dbConn)
+
+    for (let region of regions) {
+        try {
+            const q = region.name
+
+            const place = await axios.get(
+                `https://maps.googleapis.com/maps/api/place/findplacefromtext/json?inputtype=textquery&fields=geometry&input=${q}&key=${process.env.GOOGLE_CLOUD_APIS}`)
+
+            const region_id = region.region_id
+            const lon = parseFloat(place.data.candidates[0].geometry.location.lng)
+            const lat = parseFloat(place.data.candidates[0].geometry.location.lat)
+
+            await dbConn.query(
+                `UPDATE regions
+            SET lon=${lon}, lat=${lat}
+            WHERE id = ${region_id}`
+            );
+
+            console.log("Updating coordinates for region: ", region_id, q, "     ", "lon:", lon, "lat", lat)
+        } catch (e) {
+            console.log(e)
+        }
+    }
+
+    const res = "lon lat update finished"
+    return res
+}

backend/models/handleUpdateRegionNearby.js

@@ -0,0 +1,48 @@
+const axios = require("axios")
+const getRegions = require("../models/getRegions.js")
+const getPlaceNearby = require("../models/getPlaceNearby.js")
+
+module.exports = async (dbConn) => {
+    const regions = await getRegions(dbConn)
+
+    for (let region of regions) {
+        try {
+            const region_id = region.region_id
+            const region_lon = region.lon
+            const region_lat = region.lat
+
+            console.log("Updating nearby for region: ", region_id, region.name)
+
+            const places = await getPlaceNearby(region_lat, region_lon)
+
+            for (let result of places.results) {
+                const name = result.name
+                const rating = result.rating === undefined ? null : result.rating
+                const lon = result.geometry.location.lng
+                const lat = result.geometry.location.lat
+                const photo_ref = result.photos[0].photo_reference
+                const vicinity = result.vicinity
+
+                console.log("# New tourist attraction:", region_id, region.name, name)
+
+                await dbConn.query(
+                    `INSERT INTO regions_nearby
+                 (region_id,name,lon,lat,rating,vicinity,photo_reference)
+                 VALUES
+                 (${region_id},"${name}",${lon},${lat},${rating},"${vicinity}","${photo_ref}")
+                 ON DUPLICATE KEY UPDATE
+                 lon = ${lon},
+                 lat = ${lat},
+                 rating = ${rating},
+                 vicinity = "${vicinity}",
+                 photo_reference = "${photo_ref}"`
+                );
+            }
+        } catch (e) {
+            console.log(e)
+        }
+    }
+
+    const res = "region nearby update finished"
+    return res
+}

backend/models/handleUpdateRegionNearbyById.js

@@ -0,0 +1,51 @@
+const axios = require("axios")
+const getRegionById = require("../models/getRegionById.js")
+const getPlaceNearby = require("../models/getPlaceNearby.js")
+
+module.exports = async (dbConn, id) => {
+    const region = await getRegionById(dbConn, id)
+
+    try {
+        const region_id = region.region_id
+        const region_lon = region.lon
+        const region_lat = region.lat
+
+        console.log("Updating nearby for region: ", region_id, region.name)
+
+        const places = await getPlaceNearby(region_lat, region_lon)
+
+        for (let result of places.results) {
+            try {
+                const name = result.name
+                const rating = result.rating === undefined ? null : result.rating
+                const lon = result.geometry.location.lng
+                const lat = result.geometry.location.lat
+                const photo_ref = result.photos[0].photo_reference
+                const vicinity = result.vicinity
+
+                console.log("# New tourist attraction:", region_id, region.name, name)
+
+                await dbConn.query(
+                    `INSERT INTO regions_nearby
+                 (region_id,name,lon,lat,rating,vicinity,photo_reference)
+                 VALUES
+                 (${region_id},"${name}",${lon},${lat},${rating},"${vicinity}","${photo_ref}")
+                 ON DUPLICATE KEY UPDATE
+                 lon = ${lon},
+                 lat = ${lat},
+                 rating = ${rating},
+                 vicinity = "${vicinity}",
+                 photo_reference = "${photo_ref}"`
+                );
+            } catch (e) {
+                console.log(e)
+            }
+        }
+    } catch (e) {
+        console.log(e)
+    }
+
+
+    const res = "region nearby by id update finished"
+    return res
+}

backend/models/handleUpdateRegionNearbyImgUrl.js

@@ -0,0 +1,32 @@
+const getRegionNearbyById = require("../models/getRegionNearbyById.js")
+const getPlacePhoto = require("../models/getPlacePhoto.js")
+
+module.exports = async (dbConn) => {
+    try {
+        const region_ids = await dbConn.query(`
+            SELECT distinct region_id
+            FROM regions_nearby
+            ORDER BY region_id`)
+
+
+        for (let region_id of region_ids) {
+            const nearby = await getRegionNearbyById(dbConn, region_id.region_id)
+
+            for (let place of nearby) {
+                const url = await getPlacePhoto(place.photo_reference)
+
+                console.log("# Setting image Url:", region_id, place.place_name, url)
+
+                await dbConn.query(`
+                    UPDATE regions_nearby
+                    SET img_url = "${url}"
+                    WHERE id = ${place.place_id}`)
+            }
+        }
+    } catch (e) {
+        console.log(e)
+    }
+
+    const res = "region nearby img url update finished"
+    return res
+}

backend/models/handleUpdateRegionNearbyImgUrlById.js

@@ -0,0 +1,33 @@
+const getRegionNearbyById = require("../models/getRegionNearbyById.js")
+const getPlacePhoto = require("../models/getPlacePhoto.js")
+
+module.exports = async (dbConn,id) => {
+    try {
+        const region_ids = await dbConn.query(`
+            SELECT distinct region_id
+            FROM regions_nearby
+            WHERE region_id = ?`,
+            [id])
+
+
+        for (let region_id of region_ids) {
+            const nearby = await getRegionNearbyById(dbConn, region_id.region_id)
+
+            for (let place of nearby) {
+                const url = await getPlacePhoto(place.photo_reference)
+
+                console.log("# Setting image Url:", region_id, place.place_name, url)
+
+                await dbConn.query(`
+                    UPDATE regions_nearby
+                    SET img_url = "${url}"
+                    WHERE id = ${place.place_id}`)
+            }
+        }
+    } catch (e) {
+        console.log(e)
+    }
+
+    const res = "region nearby img url update finished"
+    return res
+}

backend/mysql.js

@@ -1,92 +0,0 @@
-var mysql = require('mysql2/promise');
-require('dotenv').config()
-
-// var connection = mysql.createConnection({
-//   host: process.env.DB_HOST,
-//   user: process.env.DB_USER,
-//   password: process.env.DB_PASSWORD,
-//   port: process.env.DB_PORT,
-//   database: 'travopti'
-// });
-
-const pool =  mysql.createPool({
-  connectionLimit: 10,
-  host: process.env.DB_HOST,
-  user: process.env.DB_USER,
-  password: process.env.DB_PASSWORD,
-  port: process.env.DB_PORT,
-  database: 'travopti',
-  // typeCast: function (field, next) {
-  //   if (field.type == "INT") {
-  //     var value = field.string();
-  //     return (value === null) ? null : Number(value);
-  //   }
-  //   return next();
-  // }
-  decimalNumbers: true
-});
-
-pool.getConnection()
-  .then(function (connection) {
-    console.log(`Connected to database: ${process.env.DB_HOST}`);
-    //pool.releaseConnection(connection)
-  })
-  .catch(function (error) {
-    console.error(error.message);
-  });
-
-module.exports = pool;
-
-// let travoptidb = {}
-// travoptidb.all = () => {
-//   return new Promise((resolve, reject) => {
-//     pool.query(`SELECT * FROM regions`, (err, results) => {
-//       if (err) {
-//         return reject(err)
-//       }
-//       return resolve(results)
-//     })
-//   })
-// }
-
-// connection.connect((err) => {
-//   if (err) throw err;
-//   console.log('Database connected!')
-// });
-
-
-// exports.getRegions = () => {
-//   let sql = `SELECT * FROM regions`;
-//   console.log(connection.state)
-//   if (connection.state === 'disconnected') {
-//     setTimeout(() => console.log('waiting...'), 1000);
-//   }
-//   console.log('executed')
-//   let res = {}
-//   connection.query(sql, (error, results, fields) => {
-//     if (error) {
-//       return console.error(error.message);
-//     }
-//     console.log('innercallback(1)')
-//     res = results[0]
-//   });
-//   console.log('outsidecallback(2)')
-//   return res;
-// }
-
-// exports.getBYTdata = () => {
-//   connection.query(`SELECT * FROM regions_byt`, (error, results, fields) => {
-//     if (error) {
-//       return console.error(error.message);
-//     }
-//     console.log(results[0])
-//     nres = results.map((obj) => {
-//       return obj.region
-//     })
-//     //console.log(nres);
-//   });
-// }
-
-// exports.end = () => connection.end();
-
-// module.exports = connection;

backend/package-lock.json

@@ -4,6 +4,45 @@
   "lockfileVersion": 1,
   "requires": true,
   "dependencies": {
+    "@apidevtools/json-schema-ref-parser": {
+      "version": "8.0.0",
+      "resolved": "https://registry.npmjs.org/@apidevtools/json-schema-ref-parser/-/json-schema-ref-parser-8.0.0.tgz",
+      "integrity": "sha512-n4YBtwQhdpLto1BaUCyAeflizmIbaloGShsPyRtFf5qdFJxfssj+GgLavczgKJFa3Bq+3St2CKcpRJdjtB4EBw==",
+      "requires": {
+        "@jsdevtools/ono": "^7.1.0",
+        "call-me-maybe": "^1.0.1",
+        "js-yaml": "^3.13.1"
+      }
+    },
+    "@apidevtools/openapi-schemas": {
+      "version": "2.0.3",
+      "resolved": "https://registry.npmjs.org/@apidevtools/openapi-schemas/-/openapi-schemas-2.0.3.tgz",
+      "integrity": "sha512-QoPaxGXfgqgGpK1p21FJ400z56hV681a8DOcZt3J5z0WIHgFeaIZ4+6bX5ATqmOoCpRCsH4ITEwKaOyFMz7wOA=="
+    },
+    "@apidevtools/swagger-methods": {
+      "version": "3.0.1",
+      "resolved": "https://registry.npmjs.org/@apidevtools/swagger-methods/-/swagger-methods-3.0.1.tgz",
+      "integrity": "sha512-1Vlm18XYW6Yg7uHunroXeunWz5FShPFAdxBbPy8H6niB2Elz9QQsCoYHMbcc11EL1pTxaIr9HXz2An/mHXlX1Q=="
+    },
+    "@apidevtools/swagger-parser": {
+      "version": "9.0.1",
+      "resolved": "https://registry.npmjs.org/@apidevtools/swagger-parser/-/swagger-parser-9.0.1.tgz",
+      "integrity": "sha512-Irqybg4dQrcHhZcxJc/UM4vO7Ksoj1Id5e+K94XUOzllqX1n47HEA50EKiXTCQbykxuJ4cYGIivjx/MRSTC5OA==",
+      "requires": {
+        "@apidevtools/json-schema-ref-parser": "^8.0.0",
+        "@apidevtools/openapi-schemas": "^2.0.2",
+        "@apidevtools/swagger-methods": "^3.0.0",
+        "@jsdevtools/ono": "^7.1.0",
+        "call-me-maybe": "^1.0.1",
+        "openapi-types": "^1.3.5",
+        "z-schema": "^4.2.2"
+      }
+    },
+    "@jsdevtools/ono": {
+      "version": "7.1.2",
+      "resolved": "https://registry.npmjs.org/@jsdevtools/ono/-/ono-7.1.2.tgz",
+      "integrity": "sha512-qS/a24RA5FEoiJS9wiv6Pwg2c/kiUo3IVUQcfeM9JvsR6pM8Yx+yl/6xWYLckZCT5jpLNhslgjiA8p/XcGyMRQ=="
+    },
     "@sindresorhus/is": {
       "version": "0.14.0",
       "resolved": "https://registry.npmjs.org/@sindresorhus/is/-/is-0.14.0.tgz",
@@ -25,6 +64,16 @@
       "integrity": "sha512-rr+OQyAjxze7GgWrSaJwydHStIhHq2lvY3BOC2Mj7KnzI7XK0Uw1TOOdI9lDoajEbSWLiYgoo4f1R51erQfhPQ==",
       "dev": true
     },
+    "@types/geojson": {
+      "version": "7946.0.7",
+      "resolved": "https://registry.npmjs.org/@types/geojson/-/geojson-7946.0.7.tgz",
+      "integrity": "sha512-wE2v81i4C4Ol09RtsWFAqg3BUitWbHSpSlIo+bNdsCJijO9sjme+zm+73ZMCa/qMC8UEERxzGbvmr1cffo2SiQ=="
+    },
+    "@types/node": {
+      "version": "13.13.12",
+      "resolved": "https://registry.npmjs.org/@types/node/-/node-13.13.12.tgz",
+      "integrity": "sha512-zWz/8NEPxoXNT9YyF2osqyA9WjssZukYpgI4UYZpOjcyqwIUqWGkcCionaEb9Ki+FULyPyvNFpg/329Kd2/pbw=="
+    },
     "abbrev": {
       "version": "1.1.1",
       "resolved": "https://registry.npmjs.org/abbrev/-/abbrev-1.1.1.tgz",
@@ -93,6 +142,14 @@
         "picomatch": "^2.0.4"
       }
     },
+    "argparse": {
+      "version": "1.0.10",
+      "resolved": "https://registry.npmjs.org/argparse/-/argparse-1.0.10.tgz",
+      "integrity": "sha512-o5Roy6tNG4SL/FOkCAN6RzjiakZS25RLYFrcMttJqbdd8BWrnA+fGz57iN5Pb06pvBGvl5gQ0B48dJlslXvoTg==",
+      "requires": {
+        "sprintf-js": "~1.0.2"
+      }
+    },
     "array-flatten": {
       "version": "1.1.1",
       "resolved": "https://registry.npmjs.org/array-flatten/-/array-flatten-1.1.1.tgz",
@@ -109,8 +166,15 @@
     "balanced-match": {
       "version": "1.0.0",
       "resolved": "https://registry.npmjs.org/balanced-match/-/balanced-match-1.0.0.tgz",
-      "integrity": "sha1-ibTRmasr7kneFk6gK4nORi1xt2c=",
-      "dev": true
+      "integrity": "sha1-ibTRmasr7kneFk6gK4nORi1xt2c="
+    },
+    "basic-auth": {
+      "version": "2.0.1",
+      "resolved": "https://registry.npmjs.org/basic-auth/-/basic-auth-2.0.1.tgz",
+      "integrity": "sha512-NF+epuEdnUYVlGuhaxbbq+dvJttwLnGY+YixlXlME5KpQ5W3CnXA5cVTneY3SPbPDRkcjMbifrwmFYcClgOZeg==",
+      "requires": {
+        "safe-buffer": "5.1.2"
+      }
     },
     "binary-extensions": {
       "version": "2.0.0",
@@ -155,7 +219,6 @@
       "version": "1.1.11",
       "resolved": "https://registry.npmjs.org/brace-expansion/-/brace-expansion-1.1.11.tgz",
       "integrity": "sha512-iCuPHDFgrHX7H2vEI/5xpz07zSHB00TpugqhmYtVmMO6518mCuRMoOYFldEBl0g187ufozdaHgWKcYFb61qGiA==",
-      "dev": true,
       "requires": {
         "balanced-match": "^1.0.0",
         "concat-map": "0.0.1"
@@ -207,6 +270,11 @@
         }
       }
     },
+    "call-me-maybe": {
+      "version": "1.0.1",
+      "resolved": "https://registry.npmjs.org/call-me-maybe/-/call-me-maybe-1.0.1.tgz",
+      "integrity": "sha1-JtII6onje1y95gJQoV8DHBak1ms="
+    },
     "camelcase": {
       "version": "5.3.1",
       "resolved": "https://registry.npmjs.org/camelcase/-/camelcase-5.3.1.tgz",
@@ -301,11 +369,15 @@
       "integrity": "sha512-dOy+3AuW3a2wNbZHIuMZpTcgjGuLU/uBL/ubcZF9OXbDo8ff4O8yVp5Bf0efS8uEoYo5q4Fx7dY9OgQGXgAsQA==",
       "dev": true
     },
+    "commander": {
+      "version": "5.0.0",
+      "resolved": "https://registry.npmjs.org/commander/-/commander-5.0.0.tgz",
+      "integrity": "sha512-JrDGPAKjMGSP1G0DUoaceEJ3DZgAfr/q6X7FVk4+U5KxUSKviYGM2k6zWkfyyBHy5rAtzgYJFa1ro2O9PtoxwQ=="
+    },
     "concat-map": {
       "version": "0.0.1",
       "resolved": "https://registry.npmjs.org/concat-map/-/concat-map-0.0.1.tgz",
-      "integrity": "sha1-2Klr13/Wjfd5OnMDajug1UBdR3s=",
-      "dev": true
+      "integrity": "sha1-2Klr13/Wjfd5OnMDajug1UBdR3s="
     },
     "configstore": {
       "version": "5.0.1",
@@ -344,6 +416,15 @@
       "resolved": "https://registry.npmjs.org/cookie-signature/-/cookie-signature-1.0.6.tgz",
       "integrity": "sha1-4wOogrNCzD7oylE6eZmXNNqzriw="
     },
+    "cors": {
+      "version": "2.8.5",
+      "resolved": "https://registry.npmjs.org/cors/-/cors-2.8.5.tgz",
+      "integrity": "sha512-KIHbLJqu73RGr/hnbrO9uBeixNGuvSQjul/jdFvS/KFSIH1hWVd1ng7zOHx+YrEfInLG7q4n6GHQ9cDtxv/P6g==",
+      "requires": {
+        "object-assign": "^4",
+        "vary": "^1"
+      }
+    },
     "crypto-random-string": {
       "version": "2.0.0",
       "resolved": "https://registry.npmjs.org/crypto-random-string/-/crypto-random-string-2.0.0.tgz",
@@ -394,6 +475,14 @@
       "resolved": "https://registry.npmjs.org/destroy/-/destroy-1.0.4.tgz",
       "integrity": "sha1-l4hXRCxEdJ5CBmE+N5RiBYJqvYA="
     },
+    "doctrine": {
+      "version": "3.0.0",
+      "resolved": "https://registry.npmjs.org/doctrine/-/doctrine-3.0.0.tgz",
+      "integrity": "sha512-yS+Q5i3hBf7GBkd4KG8a7eBNNWNGLTaEwwYWUijIYM7zrlYDM0BFXHjjPWlWZ1Rg7UaddZeIDmi9jF3HmqiQ2w==",
+      "requires": {
+        "esutils": "^2.0.2"
+      }
+    },
     "dot-prop": {
       "version": "5.2.0",
       "resolved": "https://registry.npmjs.org/dot-prop/-/dot-prop-5.2.0.tgz",
@@ -455,6 +544,11 @@
       "resolved": "https://registry.npmjs.org/esprima/-/esprima-4.0.1.tgz",
       "integrity": "sha512-eGuFFw7Upda+g4p+QHvnW0RyTX/SVeJBDM/gCtMARO0cLuT2HcEKnTPvhjV6aGeqrCB/sbNop0Kszm0jsaWU4A=="
     },
+    "esutils": {
+      "version": "2.0.3",
+      "resolved": "https://registry.npmjs.org/esutils/-/esutils-2.0.3.tgz",
+      "integrity": "sha512-kVscqXk4OCp68SZ0dkgEKVi6/8ij300KBWTJq32P/dYeWTSwK41WyTxalN1eRmA5Z9UU/LX9D7FWSmV9SAYx6g=="
+    },
     "etag": {
       "version": "1.8.1",
       "resolved": "https://registry.npmjs.org/etag/-/etag-1.8.1.tgz",
@@ -548,6 +642,11 @@
       "resolved": "https://registry.npmjs.org/fresh/-/fresh-0.5.2.tgz",
       "integrity": "sha1-PYyt2Q2XZWn6g1qx+OSyOhBWBac="
     },
+    "fs.realpath": {
+      "version": "1.0.0",
+      "resolved": "https://registry.npmjs.org/fs.realpath/-/fs.realpath-1.0.0.tgz",
+      "integrity": "sha1-FQStJSMVjKpA20onh8sBQRmU6k8="
+    },
     "fsevents": {
       "version": "2.1.3",
       "resolved": "https://registry.npmjs.org/fsevents/-/fsevents-2.1.3.tgz",
@@ -572,6 +671,19 @@
         "pump": "^3.0.0"
       }
     },
+    "glob": {
+      "version": "7.1.6",
+      "resolved": "https://registry.npmjs.org/glob/-/glob-7.1.6.tgz",
+      "integrity": "sha512-LwaxwyZ72Lk7vZINtNNrywX0ZuLyStrdDtabefZKAY5ZGJhVtgdznluResxNmPitE0SAO+O26sWTHeKSI2wMBA==",
+      "requires": {
+        "fs.realpath": "^1.0.0",
+        "inflight": "^1.0.4",
+        "inherits": "2",
+        "minimatch": "^3.0.4",
+        "once": "^1.3.0",
+        "path-is-absolute": "^1.0.0"
+      }
+    },
     "glob-parent": {
       "version": "5.1.1",
       "resolved": "https://registry.npmjs.org/glob-parent/-/glob-parent-5.1.1.tgz",
@@ -645,6 +757,11 @@
         "toidentifier": "1.0.0"
       }
     },
+    "httpolyglot": {
+      "version": "0.1.2",
+      "resolved": "https://registry.npmjs.org/httpolyglot/-/httpolyglot-0.1.2.tgz",
+      "integrity": "sha1-5NNH/omEpi9GfUBg31J/GFH2mXs="
+    },
     "iconv-lite": {
       "version": "0.4.24",
       "resolved": "https://registry.npmjs.org/iconv-lite/-/iconv-lite-0.4.24.tgz",
@@ -671,6 +788,15 @@
       "integrity": "sha1-khi5srkoojixPcT7a21XbyMUU+o=",
       "dev": true
     },
+    "inflight": {
+      "version": "1.0.6",
+      "resolved": "https://registry.npmjs.org/inflight/-/inflight-1.0.6.tgz",
+      "integrity": "sha1-Sb1jMdfQLQwJvJEKEHW6gWW1bfk=",
+      "requires": {
+        "once": "^1.3.0",
+        "wrappy": "1"
+      }
+    },
     "inherits": {
       "version": "2.0.3",
       "resolved": "https://registry.npmjs.org/inherits/-/inherits-2.0.3.tgz",
@@ -777,6 +903,15 @@
       "integrity": "sha512-VjSeb/lHmkoyd8ryPVIKvOCn4D1koMqY+vqyjjUfc3xyKtP4dYOxM44sZrnqQSzSds3xyOrUTLTC9LVCVgLngw==",
       "dev": true
     },
+    "js-yaml": {
+      "version": "3.13.1",
+      "resolved": "https://registry.npmjs.org/js-yaml/-/js-yaml-3.13.1.tgz",
+      "integrity": "sha512-YfbcO7jXDdyj0DGxYVSlSeQNHbD7XPWvrVWeVUujrQEoZzWJIRrCPoyk6kL6IAjAG2IolMK4T0hNUe0HOUs5Jw==",
+      "requires": {
+        "argparse": "^1.0.7",
+        "esprima": "^4.0.0"
+      }
+    },
     "json-buffer": {
       "version": "3.0.0",
       "resolved": "https://registry.npmjs.org/json-buffer/-/json-buffer-3.0.0.tgz",
@@ -806,6 +941,16 @@
       "resolved": "https://registry.npmjs.org/lodash/-/lodash-4.17.15.tgz",
       "integrity": "sha512-8xOcRHvCjnocdS5cpwXQXVzmmh5e5+saE2QGoeQmbKmRS6J3VQppPOIt0MnmE+4xlZoumy0GPG0D0MVIQbNA1A=="
     },
+    "lodash.get": {
+      "version": "4.4.2",
+      "resolved": "https://registry.npmjs.org/lodash.get/-/lodash.get-4.4.2.tgz",
+      "integrity": "sha1-LRd/ZS+jHpObRDjVNBSZ36OCXpk="
+    },
+    "lodash.isequal": {
+      "version": "4.5.0",
+      "resolved": "https://registry.npmjs.org/lodash.isequal/-/lodash.isequal-4.5.0.tgz",
+      "integrity": "sha1-QVxEePK8wwEgwizhDtMib30+GOA="
+    },
     "long": {
       "version": "4.0.0",
       "resolved": "https://registry.npmjs.org/long/-/long-4.0.0.tgz",
@@ -842,6 +987,30 @@
         }
       }
     },
+    "mariadb": {
+      "version": "2.4.0",
+      "resolved": "https://registry.npmjs.org/mariadb/-/mariadb-2.4.0.tgz",
+      "integrity": "sha512-78zrj9SpF6I3eVWMMkdm+SEfcsMb/uWVKPo7pKhhCfuGywEf3I1dK0ewSTjD0SyTEgSEuWn/H/I4TIErGgYTCQ==",
+      "requires": {
+        "@types/geojson": "^7946.0.7",
+        "@types/node": "^13.9.8",
+        "denque": "^1.4.1",
+        "iconv-lite": "^0.5.1",
+        "long": "^4.0.0",
+        "moment-timezone": "^0.5.31",
+        "please-upgrade-node": "^3.2.0"
+      },
+      "dependencies": {
+        "iconv-lite": {
+          "version": "0.5.2",
+          "resolved": "https://registry.npmjs.org/iconv-lite/-/iconv-lite-0.5.2.tgz",
+          "integrity": "sha512-kERHXvpSaB4aU3eANwidg79K8FlrN77m8G9V+0vOR3HYaRifrlwMEpT7ZBJqLSEIHnEgJTHcWK82wwLwwKwtag==",
+          "requires": {
+            "safer-buffer": ">= 2.1.2 < 3"
+          }
+        }
+      }
+    },
     "media-typer": {
       "version": "0.3.0",
       "resolved": "https://registry.npmjs.org/media-typer/-/media-typer-0.3.0.tgz",
@@ -885,7 +1054,6 @@
       "version": "3.0.4",
       "resolved": "https://registry.npmjs.org/minimatch/-/minimatch-3.0.4.tgz",
       "integrity": "sha512-yJHVQEhyqPLUTgt9B83PXu6W3rx4MvvHvSUvToogpwoGDOUQ+yDrR0HRot+yOCdCO7u4hX3pWft6kWBBcqh0UA==",
-      "dev": true,
       "requires": {
         "brace-expansion": "^1.1.7"
       }
@@ -901,6 +1069,33 @@
       "resolved": "https://registry.npmjs.org/moment/-/moment-2.26.0.tgz",
       "integrity": "sha512-oIixUO+OamkUkwjhAVE18rAMfRJNsNe/Stid/gwHSOfHrOtw9EhAY2AHvdKZ/k/MggcYELFCJz/Sn2pL8b8JMw=="
     },
+    "moment-timezone": {
+      "version": "0.5.31",
+      "resolved": "https://registry.npmjs.org/moment-timezone/-/moment-timezone-0.5.31.tgz",
+      "integrity": "sha512-+GgHNg8xRhMXfEbv81iDtrVeTcWt0kWmTEY1XQK14dICTXnWJnT0dxdlPspwqF3keKMVPXwayEsk1DI0AA/jdA==",
+      "requires": {
+        "moment": ">= 2.9.0"
+      }
+    },
+    "morgan": {
+      "version": "1.10.0",
+      "resolved": "https://registry.npmjs.org/morgan/-/morgan-1.10.0.tgz",
+      "integrity": "sha512-AbegBVI4sh6El+1gNwvD5YIck7nSA36weD7xvIxG4in80j/UoK8AEGaWnnz8v1GxonMCltmlNs5ZKbGvl9b1XQ==",
+      "requires": {
+        "basic-auth": "~2.0.1",
+        "debug": "2.6.9",
+        "depd": "~2.0.0",
+        "on-finished": "~2.3.0",
+        "on-headers": "~1.0.2"
+      },
+      "dependencies": {
+        "depd": {
+          "version": "2.0.0",
+          "resolved": "https://registry.npmjs.org/depd/-/depd-2.0.0.tgz",
+          "integrity": "sha512-g7nH6P6dyDioJogAAGprGpCtVImJhpPk/roCzdb3fIh61/s/nPsfR6onyMwkCAR/OlC3yBC0lESvUoQEAssIrw=="
+        }
+      }
+    },
     "ms": {
       "version": "2.0.0",
       "resolved": "https://registry.npmjs.org/ms/-/ms-2.0.0.tgz",
@@ -1017,6 +1212,11 @@
       "integrity": "sha512-2s47yzUxdexf1OhyRi4Em83iQk0aPvwTddtFz4hnSSw9dCEsLEGf6SwIO8ss/19S9iBb5sJaOuTvTGDeZI00BQ==",
       "dev": true
     },
+    "object-assign": {
+      "version": "4.1.1",
+      "resolved": "https://registry.npmjs.org/object-assign/-/object-assign-4.1.1.tgz",
+      "integrity": "sha1-IQmtx5ZYh8/AXLvUQsrIv7s2CGM="
+    },
     "on-finished": {
       "version": "2.3.0",
       "resolved": "https://registry.npmjs.org/on-finished/-/on-finished-2.3.0.tgz",
@@ -1025,15 +1225,24 @@
         "ee-first": "1.1.1"
       }
     },
+    "on-headers": {
+      "version": "1.0.2",
+      "resolved": "https://registry.npmjs.org/on-headers/-/on-headers-1.0.2.tgz",
+      "integrity": "sha512-pZAE+FJLoyITytdqK0U5s+FIpjN0JP3OzFi/u8Rx+EV5/W+JTWGXG8xFzevE7AjBfDqHv/8vL8qQsIhHnqRkrA=="
+    },
     "once": {
       "version": "1.4.0",
       "resolved": "https://registry.npmjs.org/once/-/once-1.4.0.tgz",
       "integrity": "sha1-WDsap3WWHUsROsF9nFC6753Xa9E=",
-      "dev": true,
       "requires": {
         "wrappy": "1"
       }
     },
+    "openapi-types": {
+      "version": "1.3.5",
+      "resolved": "https://registry.npmjs.org/openapi-types/-/openapi-types-1.3.5.tgz",
+      "integrity": "sha512-11oi4zYorsgvg5yBarZplAqbpev5HkuVNPlZaPTknPDzAynq+lnJdXAmruGWP0s+dNYZS7bjM+xrTpJw7184Fg=="
+    },
     "p-cancelable": {
       "version": "1.1.0",
       "resolved": "https://registry.npmjs.org/p-cancelable/-/p-cancelable-1.1.0.tgz",
@@ -1065,6 +1274,20 @@
       "resolved": "https://registry.npmjs.org/parseurl/-/parseurl-1.3.3.tgz",
       "integrity": "sha512-CiyeOxFT/JZyN5m0z9PfXw4SCBJ6Sygz1Dpl0wqjlhDEGGBP1GnsUVEL0p63hoG1fcj3fHynXi9NYO4nWOL+qQ=="
     },
+    "path": {
+      "version": "0.12.7",
+      "resolved": "https://registry.npmjs.org/path/-/path-0.12.7.tgz",
+      "integrity": "sha1-1NwqUGxM4hl+tIHr/NWzbAFAsQ8=",
+      "requires": {
+        "process": "^0.11.1",
+        "util": "^0.10.3"
+      }
+    },
+    "path-is-absolute": {
+      "version": "1.0.1",
+      "resolved": "https://registry.npmjs.org/path-is-absolute/-/path-is-absolute-1.0.1.tgz",
+      "integrity": "sha1-F0uSaHNVNP+8es5r9TpanhtcX18="
+    },
     "path-to-regexp": {
       "version": "0.1.7",
       "resolved": "https://registry.npmjs.org/path-to-regexp/-/path-to-regexp-0.1.7.tgz",
@@ -1076,12 +1299,25 @@
       "integrity": "sha512-q0M/9eZHzmr0AulXyPwNfZjtwZ/RBZlbN3K3CErVrk50T2ASYI7Bye0EvekFY3IP1Nt2DHu0re+V2ZHIpMkuWg==",
       "dev": true
     },
+    "please-upgrade-node": {
+      "version": "3.2.0",
+      "resolved": "https://registry.npmjs.org/please-upgrade-node/-/please-upgrade-node-3.2.0.tgz",
+      "integrity": "sha512-gQR3WpIgNIKwBMVLkpMUeR3e1/E1y42bqDQZfql+kDeXd8COYfM8PQA4X6y7a8u9Ua9FHmsrrmirW2vHs45hWg==",
+      "requires": {
+        "semver-compare": "^1.0.0"
+      }
+    },
     "prepend-http": {
       "version": "2.0.0",
       "resolved": "https://registry.npmjs.org/prepend-http/-/prepend-http-2.0.0.tgz",
       "integrity": "sha1-6SQ0v6XqjBn0HN/UAddBo8gZ2Jc=",
       "dev": true
     },
+    "process": {
+      "version": "0.11.10",
+      "resolved": "https://registry.npmjs.org/process/-/process-0.11.10.tgz",
+      "integrity": "sha1-czIwDoQBYb2j5podHZGn1LwW8YI="
+    },
     "proxy-addr": {
       "version": "2.0.6",
       "resolved": "https://registry.npmjs.org/proxy-addr/-/proxy-addr-2.0.6.tgz",
@@ -1214,6 +1450,11 @@
       "integrity": "sha512-sauaDf/PZdVgrLTNYHRtpXa1iRiKcaebiKQ1BJdpQlWH2lCvexQdX55snPFyK7QzpudqbCI0qXFfOasHdyNDGQ==",
       "dev": true
     },
+    "semver-compare": {
+      "version": "1.0.0",
+      "resolved": "https://registry.npmjs.org/semver-compare/-/semver-compare-1.0.0.tgz",
+      "integrity": "sha1-De4hahyUGrN+nvsXiPavxf9VN/w="
+    },
     "semver-diff": {
       "version": "3.1.1",
       "resolved": "https://registry.npmjs.org/semver-diff/-/semver-diff-3.1.1.tgz",
@@ -1285,10 +1526,15 @@
       "integrity": "sha512-VUJ49FC8U1OxwZLxIbTTrDvLnf/6TDgxZcK8wxR8zs13xpx7xbG60ndBlhNrFi2EMuFRoeDoJO7wthSLq42EjA==",
       "dev": true
     },
+    "sprintf-js": {
+      "version": "1.0.3",
+      "resolved": "https://registry.npmjs.org/sprintf-js/-/sprintf-js-1.0.3.tgz",
+      "integrity": "sha1-BOaSb2YolTVPPdAVIDYzuFcpfiw="
+    },
     "sqlstring": {
-      "version": "2.3.1",
-      "resolved": "https://registry.npmjs.org/sqlstring/-/sqlstring-2.3.1.tgz",
-      "integrity": "sha1-R1OT/56RR5rqYtyvDKPRSYOn+0A="
+      "version": "2.3.2",
+      "resolved": "https://registry.npmjs.org/sqlstring/-/sqlstring-2.3.2.tgz",
+      "integrity": "sha512-vF4ZbYdKS8OnoJAWBmMxCQDkiEBkGQYU7UZPtL8flbDRSNkhaXvRJ279ZtI6M+zDaQovVU4tuRgzK5fVhvFAhg=="
     },
     "statuses": {
       "version": "1.5.0",
@@ -1359,6 +1605,39 @@
         "has-flag": "^3.0.0"
       }
     },
+    "swagger-jsdoc": {
+      "version": "4.0.0",
+      "resolved": "https://registry.npmjs.org/swagger-jsdoc/-/swagger-jsdoc-4.0.0.tgz",
+      "integrity": "sha512-wHrmRvE/OQa3d387YIrRNPvsPwxkJc0tAYeCVa359gUIKPjC4ReduFhqq/+4erLUS79kY1T5Fv0hE0SV/PgBig==",
+      "requires": {
+        "commander": "5.0.0",
+        "doctrine": "3.0.0",
+        "glob": "7.1.6",
+        "js-yaml": "3.13.1",
+        "swagger-parser": "9.0.1"
+      }
+    },
+    "swagger-parser": {
+      "version": "9.0.1",
+      "resolved": "https://registry.npmjs.org/swagger-parser/-/swagger-parser-9.0.1.tgz",
+      "integrity": "sha512-oxOHUaeNetO9ChhTJm2fD+48DbGbLD09ZEOwPOWEqcW8J6zmjWxutXtSuOiXsoRgDWvORYlImbwM21Pn+EiuvQ==",
+      "requires": {
+        "@apidevtools/swagger-parser": "9.0.1"
+      }
+    },
+    "swagger-ui-dist": {
+      "version": "3.28.0",
+      "resolved": "https://registry.npmjs.org/swagger-ui-dist/-/swagger-ui-dist-3.28.0.tgz",
+      "integrity": "sha512-aPkfTzPv9djSiZI1NUkWr5HynCUsH+jaJ0WSx+/t19wq7MMGg9clHm9nGoIpAtqml1G51ofI+I75Ym72pukzFg=="
+    },
+    "swagger-ui-express": {
+      "version": "4.1.4",
+      "resolved": "https://registry.npmjs.org/swagger-ui-express/-/swagger-ui-express-4.1.4.tgz",
+      "integrity": "sha512-Ea96ecpC+Iq9GUqkeD/LFR32xSs8gYqmTW1gXCuKg81c26WV6ZC2FsBSPVExQP6WkyUuz5HEiR0sEv/HCC343g==",
+      "requires": {
+        "swagger-ui-dist": "^3.18.1"
+      }
+    },
     "term-size": {
       "version": "2.2.0",
       "resolved": "https://registry.npmjs.org/term-size/-/term-size-2.2.0.tgz",
@@ -1471,11 +1750,24 @@
         "prepend-http": "^2.0.0"
       }
     },
+    "util": {
+      "version": "0.10.4",
+      "resolved": "https://registry.npmjs.org/util/-/util-0.10.4.tgz",
+      "integrity": "sha512-0Pm9hTQ3se5ll1XihRic3FDIku70C+iHUdT/W926rSgHV5QgXsYbKZN8MSC3tJtSkhuROzvsQjAaFENRXr+19A==",
+      "requires": {
+        "inherits": "2.0.3"
+      }
+    },
     "utils-merge": {
       "version": "1.0.1",
       "resolved": "https://registry.npmjs.org/utils-merge/-/utils-merge-1.0.1.tgz",
       "integrity": "sha1-n5VxD1CiZ5R7LMwSR0HBAoQn5xM="
     },
+    "validator": {
+      "version": "12.2.0",
+      "resolved": "https://registry.npmjs.org/validator/-/validator-12.2.0.tgz",
+      "integrity": "sha512-jJfE/DW6tIK1Ek8nCfNFqt8Wb3nzMoAbocBF6/Icgg1ZFSBpObdnwVY2jQj6qUqzhx5jc71fpvBWyLGO7Xl+nQ=="
+    },
     "vary": {
       "version": "1.1.2",
       "resolved": "https://registry.npmjs.org/vary/-/vary-1.1.2.tgz",
@@ -1493,8 +1785,7 @@
     "wrappy": {
       "version": "1.0.2",
       "resolved": "https://registry.npmjs.org/wrappy/-/wrappy-1.0.2.tgz",
-      "integrity": "sha1-tSQ9jz7BqjXxNkYFvA0QNuMKtp8=",
-      "dev": true
+      "integrity": "sha1-tSQ9jz7BqjXxNkYFvA0QNuMKtp8="
     },
     "write-file-atomic": {
       "version": "3.0.3",
@@ -1518,6 +1809,25 @@
       "version": "3.1.1",
       "resolved": "https://registry.npmjs.org/yallist/-/yallist-3.1.1.tgz",
       "integrity": "sha512-a4UGQaWPH59mOXUYnAG2ewncQS4i4F43Tv3JoAM+s2VDAmS9NsK8GpDMLrCHPksFT7h3K6TOoUNn2pb7RoXx4g=="
+    },
+    "z-schema": {
+      "version": "4.2.3",
+      "resolved": "https://registry.npmjs.org/z-schema/-/z-schema-4.2.3.tgz",
+      "integrity": "sha512-zkvK/9TC6p38IwcrbnT3ul9in1UX4cm1y/VZSs4GHKIiDCrlafc+YQBgQBUdDXLAoZHf2qvQ7gJJOo6yT1LH6A==",
+      "requires": {
+        "commander": "^2.7.1",
+        "lodash.get": "^4.4.2",
+        "lodash.isequal": "^4.5.0",
+        "validator": "^12.0.0"
+      },
+      "dependencies": {
+        "commander": {
+          "version": "2.20.3",
+          "resolved": "https://registry.npmjs.org/commander/-/commander-2.20.3.tgz",
+          "integrity": "sha512-GpVkmM8vF2vQUkj2LvZmD35JxeJOLCwJ9cUkugyk2nuhbv3+mJvpLYYt+0+USMxE+oj+ey/lJEnhZw75x/OMcQ==",
+          "optional": true
+        }
+      }
     }
   }
 }

backend/package.json

@@ -2,19 +2,28 @@
   "name": "cc-data-backend",
   "version": "1.0.0",
   "description": "",
-  "main": "app.js",
+  "main": "index.js",
   "scripts": {
-    "start": "nodemon ./app.js"
+    "start": "nodemon ./index.js"
   },
   "author": "",
   "license": "ISC",
   "dependencies": {
     "axios": "^0.19.2",
+    "body-parser": "^1.19.0",
+    "cors": "^2.8.5",
     "dotenv": "^8.2.0",
     "express": "^4.17.1",
+    "httpolyglot": "^0.1.2",
     "lodash": "^4.17.15",
+    "mariadb": "^2.4.0",
     "moment": "^2.26.0",
-    "mysql2": "^2.1.0"
+    "morgan": "^1.10.0",
+    "mysql2": "^2.1.0",
+    "path": "^0.12.7",
+    "sqlstring": "^2.3.2",
+    "swagger-jsdoc": "^4.0.0",
+    "swagger-ui-express": "^4.1.4"
   },
   "devDependencies": {
     "nodemon": "^2.0.4"

backend/routes/countries.js

@@ -0,0 +1,54 @@
+/**
+ * @swagger
+ * tags:
+ *   name: Countries
+ *   description: Access country data.
+ */
+
+const router = require("express").Router();
+
+// Models
+const getCountries = require("../models/getCountries.js");
+const getCountryById = require("../models/getCountryById.js");
+
+// Utils
+const sqlSanitzer = require("../util/sqlstring_sanitizer.js")
+
+module.exports = dbConn => {
+    /**
+     * @swagger
+     * /countries:
+     *  get:
+     *   summary: Get all countries
+     *   tags: [Countries]
+     *   responses:
+     *    "200":
+     *     description: Returns aviable data for all countries
+     */
+    router.get("/api/v1/countries", async (req, res) => {
+        res.json(await getCountries(dbConn));
+    });
+
+    /**
+     * @swagger
+     * /countries/{id}:
+     *  get:
+     *   summary: Get a specific country by id
+     *   tags: [Countries]
+     *   parameters:
+     *   - name: "id"
+     *     in: "path"
+     *     required: true
+     *     type: int
+     *     example: 23
+     *   responses:
+     *    "200":
+     *     description: Returns aviable data for the country
+     *     example: test
+     */
+    router.get("/api/v1/countries/:id", async (req, res) => {
+        const id = sqlSanitzer(req.params.id);
+        res.json(await getCountryById(dbConn, id))
+    });
+    return router;
+};

backend/routes/place.js

@@ -0,0 +1,95 @@
+/**
+ * @swagger
+ * tags:
+ *   name: Places
+ *   description: Access to the Google Place API via the Key used in backend. Only for manual use in the prototype application!
+ */
+
+const router = require("express").Router()
+
+// Models
+const getPlace = require("../models/getPlace.js")
+const getPlaceNearby = require("../models/getPlaceNearby.js")
+const getPlacePhoto = require("../models/getPlacePhoto.js")
+
+// Utils
+const sqlSanitzer = require("../util/sqlstring_sanitizer.js")
+
+module.exports = dbConn => {
+    /**
+     * @swagger
+     * /place:
+     *  get:
+     *   summary: Get a specific place
+     *   tags: [Places]
+     *   parameters:
+     *   - name: "q"
+     *     in: "query"
+     *     required: true
+     *     type: int
+     *     description: "Querystring, by which the place is searched"
+     *     example: Berlin
+     *   responses:
+     *    "200":
+     *     description: Returns a place from the google places API.
+     */
+    router.get("/api/v1/place", async (req, res) => {
+        const place = await getPlace(req.query.q)
+        res.json(place)
+    });
+
+    /**
+     * @swagger
+     * /place/nearby:
+     *  get:
+     *   summary: Get nearby touristic places
+     *   tags: [Places]
+     *   parameters:
+     *   - name: "lat"
+     *     in: "query"
+     *     required: true
+     *     type: float
+     *     description: "Latitiude"
+     *     example: 52.520365
+     *   - name: "lng"
+     *     in: "query"
+     *     required: true
+     *     type: float
+     *     description: "Longitude"
+     *     example: 13.403509
+     *   responses:
+     *    "200":
+     *     description: Returns nearby places from the google places API.
+     */
+    router.get("/api/v1/place/nearby", async (req, res) => {
+        const lat = req.query.lat
+        const lng = req.query.lng
+        const place = await getPlaceNearby(lat, lng)
+        res.json(place)
+    });
+
+    /**
+     * @swagger
+     * /place/photo:
+     *  get:
+     *   summary: Get a photo for a place
+     *   tags: [Places]
+     *   parameters:
+     *   - name: "photoref"
+     *     in: "query"
+     *     required: true
+     *     type: int
+     *     description: "Photo_Reference which is returned for a place by Google Places API"
+     *     example: CmRaAAAAbupojmH94negtiCnLGdfx2azxhVTEDI1rtTrYnQ7KclEI-Yy9_YGxN9h63AKrCzd22kk5z-UiK7fS4-zXnO5OqfNRZu2hrmfcp8b77rItediibAVovOOA5LnyJ9YYuofEhAAr0Im0zuiAtbDKPjbPUSBGhTFkSrH6FZxenbo1bCkdCXaUMhOug
+     *   responses:
+     *    "200":
+     *     description: Returns the matching url to the photo.
+     */
+    router.get("/api/v1/place/photo", async (req, res) => {
+        const photoref = req.query.photoref
+        const photo = await getPlacePhoto(photoref)
+        res.json(photo)
+    });
+
+    return router;
+};

backend/routes/regions.js

@@ -0,0 +1,112 @@
+/**
+ * @swagger
+ * tags:
+ *   name: Regions
+ *   description: Access region data.
+ */
+
+const router = require("express").Router();
+
+// Models
+const getRegions = require("../models/getRegions.js");
+const getRegionById = require("../models/getRegionById.js");
+const getRegionNearbyById = require("../models/getRegionNearbyById.js")
+
+// Utils
+const path = require("path");
+const fs = require("fs");
+const _ = require('lodash')
+const sqlSanitzer = require("../util/sqlstring_sanitizer.js")
+
+module.exports = dbConn => {
+    /**
+     * @swagger
+     * /regions:
+     *  get:
+     *   summary: Get all regions
+     *   tags: [Regions]
+     *   responses:
+     *    "200":
+     *     description: Returns available data for all regions
+     */
+    router.get("/api/v1/regions", async (req, res) => {
+        const data = await getRegions(dbConn)
+        if (req.query.randomize) {
+            const randomize = sqlSanitzer(req.query.randomize)
+            res.json(_.sampleSize(data, randomize))
+        } else {
+            res.json(data);
+        }
+    });
+
+    /**
+     * @swagger
+     * /regions/{id}:
+     *  get:
+     *   summary: Get a specific region by id
+     *   tags: [Regions]
+     *   parameters:
+     *   - name: "id"
+     *     in: "path"
+     *     required: true
+     *     type: int
+     *   responses:
+     *    "200":
+     *     description: Returns available data for the region
+     */
+    router.get("/api/v1/regions/:id", async (req, res) => {
+        console.log(typeof req.params.id)
+        const id = sqlSanitzer(req.params.id);
+        console.log(id)
+       res.json(await getRegionById(dbConn, id))
+    });
+
+    /**
+     * @swagger
+     * /regions/{id}/image:
+     *  get:
+     *   summary: Get image for specific region
+     *   tags: [Regions]
+     *   parameters:
+     *   - name: "id"
+     *     in: "path"
+     *     required: true
+     *     type: int
+     *   responses:
+     *    "200":
+     *     description: Returns the image for a specific region
+     *    "404":
+     *     description: Returns a placeholder image for the region
+     */
+    router.get('/api/v1/regions/:id/image', (req, res) => {
+        console.log("HERE")
+        if (fs.existsSync(path.join(__dirname, `../data/regions/images/${req.params.id}.jpg`))) {
+            console.log("EXISTS")
+            res.status(200).sendFile(path.join(__dirname, `../data/regions/images/${req.params.id}.jpg`))
+        } else {
+            console.log("NOT EXISTS")
+            res.status(404).sendFile(path.join(__dirname, `../data/regions/images/x.png`))
+        }
+    })
+
+    /**
+     * @swagger
+     * /regions/{id}/nearby:
+     *  get:
+     *   summary: Get nearby places of a specific region by id
+     *   tags: [Regions]
+     *   parameters:
+     *   - name: "id"
+     *     in: "path"
+     *     required: true
+     *     type: int
+     *   responses:
+     *    "200":
+     *     description: Returns all nearby places for the region
+     */
+    router.get("/api/v1/regions/:id/nearby", async (req,res) => {
+        const id = sqlSanitzer(req.params.id);
+        res.json(await getRegionNearbyById(dbConn,id))
+    });
+    return router;
+};

backend/routes/search.js

@@ -0,0 +1,182 @@
+/**
+ * @swagger
+ * tags:
+ *   name: Search
+ *   description: Access the search algorithm and the data provided for searching.
+ */
+
+const router = require("express").Router();
+
+// Models
+const getRegions = require('../models/getRegions.js');
+const getSearchPresets = require("../models/getSearchPresets.js");
+
+// Utils
+const _ = require('lodash')
+const base64 = require("../util/base64.js")
+const scoreAndSearch = require("../util/scoreAndSearch.js");
+const oldToNewQuerySyntax = require("../util/oldToNewQuerySyntax.js")
+const { getUniqueTags } = require("../models/getTags.js");
+
+module.exports = dbConn => {
+  /**
+   * @swagger
+   * /search:
+   *  get:
+   *   summary: Get Searchresults
+   *   tags: [Search]
+   *   parameters:
+   *   - name: "q"
+   *     in: "query"
+   *     required: true
+   *     type: int
+   *     description: "Base64 encoded JS-Object with searchparameters"
+   *     example: eyJmcm9tIjoxNTkzNjQ4MDAwMDAwLCJ0byI6MTU5NDI1MjgwMDAwMCwidGFncyI6W119
+   *   responses:
+   *    "200":
+   *     description: Returns the region information and scores for the searchresults
+   */
+  router.get("/api/v1/search", searchHandler(dbConn));
+
+  /**
+   * @swagger
+   * /search/presets:
+   *  get:
+   *   summary: Get the presets for the search parameters
+   *   tags: [Search]
+   *   responses:
+   *    "200":
+   *     description: Returns all presets for the search parameters
+   */
+  router.get("/api/v1/search/presets", presetHandler(dbConn));
+
+  /**
+   * @swagger
+   * /search/tags:
+   *  get:
+   *   summary: Get the existing searchtags
+   *   tags: [Search]
+   *   responses:
+   *    "200":
+   *     description: Returns all existing searchtags
+   */
+  router.get("/api/v1/search/tags", tagsHandler(dbConn));
+
+  return router;
+};
+
+function tagsHandler(dbConn) {
+  return function (req, res) {
+    getUniqueTags(dbConn).then(tags => {
+      res.json(tags.map(tag => tag.name))
+    }).catch(error => {
+      // TODO error handling
+    })
+  }
+}
+
+function presetHandler(dbConn) {
+  return function (req, res) {
+    getSearchPresets(dbConn).then(presets => {
+      res.json(presets)
+    }).catch(error => {
+      // TODO error handling
+    })
+  }
+}
+
+function searchHandler(dbConn) {
+  return async function (req, res) {
+    let response = {}
+
+    response.meta = {
+      params: req.params,
+      query: req.query,
+      headers: req.headers
+    }
+
+    // SWITCH TO SUPPORT OLD AND NEW BASE64 BASED QUERY SYNTAX
+    let q = req.query.q ? base64.base64ToObj(req.query.q) : req.query
+    console.log('Q:', q)
+
+    // transform syntax and seperate climate queries from price queries
+    if (!req.query.q) {
+      q = oldToNewQuerySyntax(q)
+    }
+    // CHOOSE PARAMS WHICH SHALL BE PASSED TO SCORE AND SEARCH
+    let scoreQueryObj = prepareQueries(q)
+
+    let [regions] = await Promise.all([getRegions(dbConn)])
+    let data = {
+      regions: regions,
+    }
+
+    // FILTER if query contains filterString
+    if (q.textfilter) {
+      data.regions = filterByString(data.regions, q.textfilter, q.fulltext)
+    }
+
+    scoreAndSearch(data, q.from, q.to, scoreQueryObj).then(searchResults => {
+
+      // only dev:
+      if (process.env.SHOW_MATCH_VALUE === '1') searchResults.forEach(reg => reg.name = `${reg.name} (${_.round(reg.score * 10, 1)}% match)`)
+
+      // FILTER NULLSCORES
+      if (!_.get(q, 'showRegionsWithNullScore', false)) {
+        console.log('without null scores');
+        searchResults.forEach(el => console.log('region:', el.name, 'score:', el.score))
+        searchResults = searchResults.filter(el => !_.some(el.scores, score => _.isNaN(score.score) || _.isNil(score.score) || score.score <= 0))
+      }
+      // SEND RESPONSE
+      res.json(searchResults)
+
+    }).catch(e => {
+      // TODO error handling
+      console.log(e)
+      res.status(400).send(e.message)
+    })
+  }
+}
+
+function filterByString(searchResults, filterString, boolFulltext) {
+  return _.filter(searchResults, region => {
+    // console.log("filtering: filterString, boolFulltext");
+    // console.log(filterString, boolFulltext);
+    filterString = filterString.toLowerCase()
+    let name = region.name.toLowerCase()
+    let country = region.country.toLowerCase()
+    if (boolFulltext) {
+      let desc = region.description.toLowerCase()
+      return name.includes(filterString) || country.includes(filterString) || desc.includes(filterString)
+    }
+    return name.includes(filterString) || country.includes(filterString)
+  })
+}
+
+function prepareQueries(queries) {
+  let q = {
+    climate: {},
+    costs: {},
+    others: {}
+  }
+  // climate
+  if (queries.temperature_mean_max) q.climate.temperature_mean_max = queries.temperature_mean_max
+  if (queries.precipitation) q.climate.precipitation = queries.precipitation
+  if (queries.rain_days) q.climate.rain_days = queries.rain_days
+  if (queries.sun_hours) q.climate.sun_hours = queries.sun_hours
+
+  // costs
+  if (queries.accommodation_costs) q.costs.accommodation_costs = queries.accommodation_costs
+  if (queries.food_costs) q.costs.food_costs = queries.food_costs
+  if (queries.alcohol_costs) q.costs.alcohol_costs = queries.alcohol_costs
+  if (queries.water_costs) q.costs.water_costs = queries.water_costs
+  if (queries.local_transportation_costs) q.costs.local_transportation_costs = queries.local_transportation_costs
+  if (queries.entertainment_costs) q.costs.entertainment_costs = queries.entertainment_costs
+  if (queries.average_per_day_costs) q.costs.average_per_day_costs = queries.average_per_day_costs
+
+  // others
+  if (queries.avg_price_relative) q.others.avg_price_relative = queries.avg_price_relative
+  if (queries.tags) q.others.tags = queries.tags
+
+  return q
+}

backend/routes/update.js

@@ -0,0 +1,135 @@
+/**
+ * @swagger
+ * tags:
+ *   name: Update
+ *   description: Endpoint for updating region specific data. Only for manual use in the prototype application!
+ */
+
+const router = require("express").Router();
+
+// Models
+const handleClimateUpdate = require("../models/handleClimateUpdate.js")
+const handleClimateUpdateV2 = require("../models/handleClimateUpdateV2.js")
+const handleUpdateRegionNearby = require("../models/handleUpdateRegionNearby.js")
+const handleUpdateRegionNearbyById = require("../models/handleUpdateRegionNearbyById.js")
+const handleUpdateRegionNearbyImgUrl = require("../models/handleUpdateRegionNearbyImgUrl.js")
+const handleUpdateRegionNearbyImgUrlById = require("../models/handleUpdateRegionNearbyImgUrlById.js")
+
+// Utils
+const sqlSanitzer = require("../util/sqlstring_sanitizer.js")
+
+module.exports = dbConn => {
+    /**
+     * @swagger
+     * /update/climate/v1:
+     *  put:
+     *   summary: Pull monthly data from meteostat API V1
+     *   tags: [Update]
+     *   responses:
+     *    "200":
+     *     description: Update information is logged in backend
+     */
+    router.put("/api/v1/update/climate/v1", async (req, res) => {
+        const update = await handleClimateUpdate(dbConn)
+        res.json(update)
+    });
+
+    /**
+     * @swagger
+     * /update/climate/v2:
+     *  put:
+     *   summary: Pull daily data from meteostat API V2. Data is written to Travopti database and must be processed manually before it can be used.
+     *   tags: [Update]
+     *   responses:
+     *    "200":
+     *     description: Update information is logged in backend
+     */
+    router.put("/api/v1/update/climate/v2", async (req, res) => {
+        const update = await handleClimateUpdateV2(dbConn)
+        res.json(update)
+    });
+
+    /**
+     * @swagger
+     * /update/regions/all/nearby:
+     *  put:
+     *   summary: Updates all nearby data for all regions
+     *   tags: [Update]
+     *   responses:
+     *    "200":
+     *     description: Updates all nearby data for all regions
+     */
+    router.put("/api/v1/update/regions/all/nearby", async (req, res) => {
+        res.json(await handleUpdateRegionNearby(dbConn))
+    });
+
+    /**
+     * @swagger
+     * /update/regions/all/lonlat:
+     *  put:
+     *   summary: Updates all coordinate data for all regions
+     *   tags: [Update]
+     *   responses:
+     *    "200":
+     *     description: Updates all coordinate data for all regions
+     */
+    router.put("/api/v1/update/regions/all/lonlat", async (req,res) => {
+        res.json(await handleRegionLonLat(dbConn))
+    });
+
+    /**
+     * @swagger
+     * /update/regions/all/nearby/image:
+     *  put:
+     *   summary: Updates the nearby image urls for all regions
+     *   tags: [Update]
+     *   responses:
+     *    "200":
+     *     description: Updates the nearby image urls for all regions
+     */
+    router.put("/api/v1/update/regions/all/nearby/image", async (req, res) => {
+        res.json(await handleUpdateRegionNearbyImgUrl(dbConn))
+    });
+
+    /**
+     * @swagger
+     * /update/regions/{id}/nearby:
+     *  put:
+     *   summary: Updates the nearby data for a specific region
+     *   tags: [Update]
+     *   parameters:
+     *   - name: "id"
+     *     in: "path"
+     *     required: true
+     *     type: int
+     *   responses:
+     *    "200":
+     *     description: Updates the nearby data for a specific region
+     */
+    router.put("/api/v1/update/regions/:id/nearby", async (req, res) => {
+        const id = sqlSanitzer(req.params.id);
+        res.json(await handleUpdateRegionNearbyById(dbConn, id))
+    });
+
+    /**
+     * @swagger
+     * /update/regions/{id}/nearby/image:
+     *  put:
+     *   summary: Updates the nearby image urls for a specific region
+     *   tags: [Update]
+     *   parameters:
+     *   - name: "id"
+     *     in: "path"
+     *     required: true
+     *     type: int
+     *   responses:
+     *    "200":
+     *     description: Updates the nearby image urls for a specific region
+     */
+    router.put("/api/v1/update/regions/:id/nearby/image", async (req, res) => {
+        const id = sqlSanitzer(req.params.id);
+        res.json(await handleUpdateRegionNearbyImgUrlById(dbConn, id))
+    });
+
+    return router
+}

backend/score.js

@@ -1,31 +0,0 @@
-/**
- * 
- */
-const multiplier_temperature = 5;
-
-
-/**
- * 
- * @param  {...any} scores expects objects which contains score and their weight
- */
-exports.calculateAvgScore = (...scores) => {
-  return avgScore = scores.reduce((total, score) => total += score) / scores.length;
-}
-
-exports.calculateScoreRange = (min, max, multiplier, regionVal, sLowVal, sHighVal) => {
-  //console.log('scores.calculateScoreRange:', min, max, multiplier, regionVal, sLowVal, sHighVal)
-  // return full score when in range
-  if (regionVal >= sLowVal && regionVal <= sHighVal) return 10;
-  // choose value with smallest distance
-  let sVal = Math.abs(regionVal - sLowVal) < Math.abs(regionVal - sHighVal) ? sLowVal : sHighVal;
-  //console.log('nearest value',sVal, regionVal)
-  return this.calculateScore(min, max, multiplier, regionVal, sVal);
-}
-
-exports.calculateScore = (min, max, multiplier, regionVal, searchVal) => {
-
-  let score = 1 - (Math.abs(searchVal - regionVal) / (max - min) * multiplier);
-  return score <= 0 ? 0 : score * 10;
-}
-
-console.log('test score calculation. result: ' + this.calculateScoreRange(-15, 45, 5, 24, 15, 22))

backend/settings.js

@@ -0,0 +1,62 @@
+module.exports = {
+  scoring: {
+    // parameter: [transition range, transition function, transistion slope exponent]
+    temperature_mean_max: [12, 'easeInOut', 2],
+    precipitation: [60, 'easeInOut', 2], // [170, 'easeInOut', 2],
+    rain_days: [5, 'easeInOut', 2],
+    sun_hours: [80, 'easeInOut', 2],
+
+    accommodation_costs: [30, 'linear', null],
+    food_costs: [25, 'linear', null],
+    alcohol_costs: [15, 'linear', null],
+    water_costs: [15, 'linear', null],
+    local_transportation_costs: [20, 'linear', null],
+    entertainment_costs: [20, 'easeInOut', 0.6],
+    average_per_day_costs: [100, 'linear', null],
+
+    avg_price_relative: [30, 'easeOut', 2],
+  },
+  boundaries: {
+    climate: {
+      min: {
+        temperature_mean: -9.6,
+        temperature_mean_min: -14.5,
+        temperature_mean_max: -4.7,
+        precipitation: 0,
+        rain_days: 0,
+        sun_hours: 3
+      },
+      max: {
+        temperature_mean: 38.3,
+        temperature_mean_min: 33.5,
+        temperature_mean_max: 43.7,
+        precipitation: 1145,
+        rain_days: 28,
+        sun_hours: 416
+      }
+    },
+    static: {
+      max: {
+        accommodation_costs: 500,
+        food_costs: 100,
+        alcohol_costs: 100,
+        water_costs: 100,
+        local_transportation_costs: 100,
+        entertainment_costs: 100,
+        average_per_day_costs: 1000,
+        avg_price_relative: 100
+
+      },
+      min: {
+        accommodation_costs: 0,
+        food_costs: 0,
+        alcohol_costs: 0,
+        water_costs: 0,
+        local_transportation_costs: 0,
+        entertainment_costs: 0,
+        average_per_day_costs: 0,
+        avg_price_relative: 0
+      }
+    }
+  }
+}

backend/transformer.js

@@ -1,77 +0,0 @@
-const _ = require('lodash')
-const fs = require('fs')
-
-
-exports.transform = (data) => {
-  // get data
-  // let data = JSON.parse(fs.readFileSync('transformer-test.json'));
-  const types = Object.keys(data[0].scores)
-
-  // STEP 1  Create Response Array with region names from first climate object
-  let byRegion = data[0].climate.map(el => {
-    return {
-      region_id: el.region_id,
-      country_id: el.country_id,
-      name: el.name,
-    }
-  })
-
-  // STEP 2  Prepare flat scoreobject array and set days property
-  scoreObjs = _.flatten(_.map(data, (period) => {
-    return _.reduce(period.scores, (arr, el) => {
-      return arr.concat(el)
-    }).map(element => {
-      element.days = period.days
-      return element
-    })
-  }))
-
-  // STEP 3  Collect scoreobjects for each region
-  let results = byRegion.map(region => {
-    let scores = []
-    types.forEach(typ => {
-      let tempScores = _.filter(scoreObjs, { 'region_id': region.region_id, 'type': typ })
-      if (_.some(tempScores, { 'score': null })) {
-        console.log("found 'null' scores! skipping...")
-        //console.log(tempScores)
-        return
-      }
-      let averagedScore = {
-        region_id: region.region_id,
-        type: typ,
-        value: 0,
-        score: 0,
-        days: 0
-      }
-      tempScores.forEach(el => {
-        averagedScore.value += (el.value * el.days)
-        averagedScore.score += (el.score * el.days)
-        averagedScore.days += (el.days)
-      })
-
-      averagedScore.value = _.round(averagedScore.value / averagedScore.days, 1)
-      averagedScore.score = _.round(averagedScore.score / averagedScore.days, 1)
-      delete averagedScore.region_id
-      delete averagedScore.days
-      scores.push(averagedScore)
-
-    })
-    region.scores = scores
-
-    // STEP 4  Calculate Average Score
-    region.score = calculateAverage(region.scores)
-    //console.log(region)
-    return region
-  })
-  
-  // console.log(results)
-  return _.orderBy(results, 'score', 'desc')
-  //end
-}
-
-function calculateAverage(scores) {
-  let sum = 0
-  scores.forEach(el => sum += el.score)
-  //console.log(sum)
-  return _.round(sum / scores.length, 2)
-}

backend/util/base64.js

@@ -14,10 +14,20 @@ exports.base64ToObj = function(base64) {
   return JSON.parse(atob(base64));
 }
 
+/**
+ * Decodes a base64 encoded object.
+ * @param base64 encoded object
+ * @returns {string} decoded object
+ */
 function atob(base64) {
   return Buffer.from(base64, 'base64').toString('binary')
 }
 
+/**
+ * Encodes an object as base64 string.
+ * @param string The object to encode
+ * @returns {string} base64 encoded object
+ */
 function btoa(string) {
   return Buffer.from(string).toString('base64')
 }

backend/util/databaseArrayFormatting.js

@@ -0,0 +1,15 @@
+/**
+ * Seperate Strings created via GROUP_CONCAT by database into an array
+ * @param array String with comma-seperated values
+ * @returns [float] array of float values
+ */
+module.exports = (array) => {
+    if (array !== null && array !== undefined) {
+        const value = array
+        array = value.split(",");
+        for (i = 0; i < array.length; i++) {
+            array[i] = parseFloat(array[i])
+        }
+    }
+    return array;
+}

backend/util/dbConnection.js

@@ -0,0 +1,47 @@
+const mariadb = require("mariadb");
+let dbConn;
+let conPool;
+// mariadb doc: https://github.com/MariaDB/mariadb-connector-nodejs/blob/master/documentation/promise-api.md
+
+async function reconnect() {
+    try {
+        dbConn = await conPool.getConnection();
+    } catch (e) {
+        if (e.code === "ECONNREFUSED") {
+            let err = new Error("Lost connection to the database");
+            err.code = "ERR_DB_CONN_LOST";
+            throw err;
+        } else {
+            throw e;
+        }
+    }
+}
+
+module.exports = async config => {
+    conPool = mariadb.createPool({
+        host: process.env.DB_HOST,
+        user: process.env.DB_USER,
+        password: process.env.DB_PASSWORD,
+        port: process.env.DB_PORT,
+        database: process.env.DATABASE,
+        connectionLimit: 10
+    });
+
+    dbConn = await conPool.getConnection();
+    return {
+        async query(q, p) {
+            let res;
+            try {
+                res = await dbConn.query(q, p);
+            } catch (e) {
+                if (e.code === "ER_CMD_CONNECTION_CLOSED") {
+                    await reconnect();
+                    await this.query(q, p);
+                } else {
+                    throw e;
+                }
+            }
+            return res;
+        }
+    };
+};

backend/util/getAllRegionsWithClimatePerMonth.js

@@ -0,0 +1,24 @@
+module.exports = function (dbConn) {
+	return async function getAllRegionsWithClimatePerMonth(month) {
+		console.log('getAllRegionsWithClimatePerMonth')
+		const sql = `SELECT 
+	region_climate.region_id AS region_id,
+	region_climate.month AS month,
+	countries.country AS country,
+	regions.region AS name,
+	ROUND(AVG(region_climate.temperature_mean), 1) AS temperature_mean, 
+	ROUND(AVG(region_climate.temperature_mean_min), 1) AS temperature_mean_min, 
+	ROUND(AVG(region_climate.temperature_mean_max), 1) AS temperature_mean_max, 
+	ROUND(AVG(region_climate.precipitation), 1) AS precipitation, 
+	ROUND(AVG(region_climate.raindays), 1) AS rain_days, 
+	ROUND(AVG(region_climate.sunshine), 1) AS sun_hours
+	FROM region_climate 
+	JOIN regions ON region_climate.region_id = regions.id 
+	JOIN countries ON regions.country_id = countries.id 
+	WHERE region_climate.month = ${month} GROUP BY region_id`
+		let response = await dbConn.query(sql)
+		console.log(response[0]);
+
+		return response
+	}
+}

backend/util/getClimateMinMax.js

@@ -0,0 +1,22 @@
+exports.getClimateMinMax = async function (dbConn) {
+  console.log('getClimateMinMax')
+  const sqlMin = `SELECT 
+      MIN(temperature_mean) AS temperature_mean,
+      MIN(temperature_mean_min) AS temperature_mean_min, 
+      MIN(temperature_mean_max) AS temperature_mean_max, 
+      MIN(precipitation) AS precipitation, 
+      MIN(rain_days) AS rain_days, 
+      MIN(sun_hours) AS sun_hours
+      FROM region_climate`
+  const sqlMax = `SELECT 
+      MAX(temperature_mean) AS temperature_mean,
+      MAX(temperature_mean_min) AS temperature_mean_min, 
+      MAX(temperature_mean_max) AS temperature_mean_max, 
+      MAX(precipitation) AS precipitation, 
+      MAX(rain_days) AS rain_days, 
+      MAX(sun_hours) AS sun_hours
+      FROM region_climate`
+  const [qResMin, qResMax] = await Promise.all([dbConn.query(sqlMin), dbConn.query(sqlMax)])
+  // console.log(qResMin)
+  return { min: qResMin[0], max: qResMax[0] }
+}

backend/util/getClimatePerRegionAndMonth.js

@@ -0,0 +1,5 @@
+function getClimatePerRegionAndMonth(regionId, month) {
+  console.log('getClimatePerRegionAndMonth')
+  const sql = `SELECT region_id, AVG(temperature_mean), AVG(temperature_mean_min), AVG(temperature_mean_max), AVG(precipitation), AVG(sunshine) FROM region_climate WHERE month = ${month} AND region_id = ${regionId}`
+  return getQueryRows(sql)
+}

backend/util/oldToNewQuerySyntax.js

@@ -0,0 +1,38 @@
+const _ = require('lodash')
+
+module.exports = function (queries) {
+    let res = _.clone(queries)
+    console.log(res);
+
+    // try {
+    if (queries.temperature_mean_max) res.temperature_mean_max = [Number(queries.temperature_mean_max.split(',')[0]), Number(queries.temperature_mean_max.split(',')[1])]
+    if (queries.precipitation) res.precipitation = [Number(queries.precipitation.split(',')[0]), Number(queries.precipitation.split(',')[1])]
+    if (queries.rain_days) res.rain_days = [Number(queries.rain_days.split(',')[0]), Number(queries.rain_days.split(',')[1])]
+    if (queries.sun_hours) res.sun_hours = [Number(queries.sun_hours.split(',')[0]), Number(queries.sun_hours.split(',')[1])]
+
+    if (queries.accommodation_costs) res.accommodation_costs = [Number(queries.accommodation_costs.split(',')[0]), Number(queries.accommodation_costs.split(',')[1])]
+    if (queries.food_costs) res.food_costs = [Number(queries.food_costs.split(',')[0]), Number(queries.food_costs.split(',')[1])]
+    if (queries.alcohol_costs) res.alcohol_costs = [Number(queries.alcohol_costs.split(',')[0]), Number(queries.alcohol_costs.split(',')[1])]
+    if (queries.water_costs) res.water_costs = [Number(queries.water_costs.split(',')[0]), Number(queries.water_costs.split(',')[1])]
+    if (queries.local_transportation_costs) res.local_transportation_costs = [Number(queries.local_transportation_costs.split(',')[0]), Number(queries.local_transportation_costs.split(',')[1])]
+    if (queries.entertainment_costs) res.entertainment_costs = [Number(queries.entertainment_costs.split(',')[0]), Number(queries.entertainment_costs.split(',')[1])]
+    if (queries.average_per_day_costs) res.average_per_day_costs = [Number(queries.average_per_day_costs.split(',')[0]), Number(queries.average_per_day_costs.split(',')[1])]
+    
+    if (queries.avg_price_relative) res.avg_price_relative = [Number(queries.avg_price_relative.split(',')[0]), Number(queries.avg_price_relative.split(',')[1])]
+    if (queries.tags) {
+        res.tags = []
+        if (queries.tags.includes(',')) {
+            res.tags.push(...queries.tags.split(',').map(el => el.trim()))
+        } else {
+            res.tags.push(queries.tags)
+        } 
+    }
+    // console.log(res);
+    
+    //     console.log('queries successfully transformed');
+    // } catch (error) {
+    //     console.log('oldToNewQuerySyntax error');
+    //     return queries
+    // }
+    return res
+}

backend/util/score.js

@@ -0,0 +1,66 @@
+const _ = require('lodash')
+
+exports.calculateAvgScore = (...scores) => {
+  return avgScore = scores.reduce((total, score) => total += score) / scores.length;
+}
+
+exports.calculateScoreRange = (transitionRange, regionVal, sLowVal, sHighVal) => {
+  //console.log('scores.calculateScoreRange:', min, max, multiplier, regionVal, sLowVal, sHighVal)
+  // return full score when in range
+  if (regionVal >= sLowVal && regionVal <= sHighVal) return 10;
+  // choose value with smallest distance
+  let sVal = Math.abs(regionVal - sLowVal) < Math.abs(regionVal - sHighVal) ? sLowVal : sHighVal;
+  return this.calculateScore(transitionRange, regionVal, sVal);
+}
+
+exports.calculateScore = (transitionRange, regionVal, searchVal) => {
+  let score = 1 - (Math.abs(searchVal - regionVal) / transitionRange);
+  return (score) * 10;
+  //return score <= 0 ? 0 : score * 10;
+}
+
+exports.linear = function (x, exponent) {
+  if (x < 0) return 0
+  if (x > 10) return 10
+  return x
+}
+
+exports.easeOut = function (x, exponent) {
+  if (x < 0) return 0
+  if (x > 10) return 10
+  return (1 - Math.pow(1 - (x / 10), exponent)) * 10
+}
+
+exports.easeInOut = function (sc, exponent) {
+  const x = (sc ) / 10
+  // console.log(sc, x);
+  if (x<0) return 0
+  if (x>1) return 10
+  return x < 0.5 ? Math.pow(2, exponent-1) * Math.pow(x,exponent) * 10 : (1 - Math.pow(-2 * x + 2, exponent)/2) * 10
+}
+
+exports.easeInOutAsymmetric = function (sc, exponent) {
+  const x = (sc ) / 10
+  // console.log(sc, x);
+  if (x<0) return 0
+  if (x>1) return 10
+  return x < 0.5 ? (2 * x) - 0.5 * 10 : (1 - Math.pow(-2 * x + 2, exponent)/2) * 10
+}
+
+exports.sigmoid = function (x, exponent) {
+  // const sigm = (1 / (1 + Math.pow(Math.E, 5 * -x))) * 10 + 5
+  // const sigm = 10 / (1 + Math.pow(Math.E, 1.2 * -x + 6))
+  const sigm = 10 / (1 + 8 * Math.pow(Math.E, 3/4 * -x))
+  console.log('sigmoid (IN/OUT):', _.round(x,3), _.round(sigm, 3))
+  return sigm
+}
+
+exports.increaseTransitionForHighValues = function (transitionRange, searchVal) {
+  //console.log(transitionRange);
+  // console.log(transitionRange);
+  // console.log(((Math.pow(searchVal / 20, 2) / 100) + 1));
+  // console.log(((Math.pow(searchVal / 20, 2) / 100) + 1) * transitionRange);
+  let transFactor = ((Math.pow(searchVal / 20, 2) / 100) + 1)
+  
+  return transFactor >= 4 ? 4 * transitionRange : transFactor * transitionRange
+}