• first/last nonregular stop
• json description

The table "Fahrplan" has been altered. The folowing columns have been added:

'connection_description_json',
'connection_lastregular_arrival_stop',
'connection_lastregular_arrival_stop_lon',
'connection_lastregular_arrival_stop_lat',
'connection_lastregular_arrival_datetime',
'return_description_json',
'return_firstregular_departure_stop',
'return_firstregular_departure_stop_lon',
'return_firstregular_departure_stop_lat',
'return_firstregular_departure_datetime'

The functionality of the two JSON columns are descibed here: https://github.com/bahnzumberg/hermes/wiki/Connection-and-Return-Description
The JSONs allow us to translate all elements like "Zug" or "Bus", etc.

The others are needed for the itinerary as well.

Please enhance the api towards the surface (suchseite), so we can implement the additional functionality.

In Tours.js in line 794 "const query_con = knex('fahrplan').select().where({hashed_url: tour.hashed_url, provider: tour.provider, city_slug: city});" the select should be restricted to the columns, which are really used.

Error handling, when no Tour is found for given ID: zuugle-api cannot handle calls with an unknown ID and crashes

Union of three SQLs:

1. menu_lang: First filters on menu_lang=tours.text_lang and multiply the search weight multiplied with 100
2. TLD derived: filter on all languages derived from the TLD excluding the one already handled in union 1 (menu_lang). Search_weight multiplied with 10
3. All the rest: Every language not handled above, will be used in the query now

Over this union, there has to be a query, which is doing the ORDER BY and the LIMIt & OFFSET

The UAT website https://www2.zuugle.si/suche?sort=relevanz is not showing any results.

We have a problem with loading the tours, but there are two tours in UAT database. Still they are not shown.

Tracking issue for:

• https://github.com/bahnzumberg/zuugle-api/security/code-scanning/3

Tracking issue for:

• https://github.com/bahnzumberg/zuugle-api/security/code-scanning/4

Tracking issue for:

• https://github.com/bahnzumberg/zuugle-api/security/code-scanning/17

What do the columns country_at, country_de, country_de, ... do? They are calculated in the loading process in sync.js. They have been used in the search. Are they used in the frontend?

If not used, please remove columns & code.
If necessary, please document the function in the sync.js file.

Remove from sync and from table

On my local machine, if i activate authentication in file authentiate.js i get an axios error (comes from suchseite of course) and white page. (potential fix : axios configuarion ?) .

Main point in this issue:
my local environment works normally without this code , but this file should be checked for the UAT installation, if it is necessary there and if so make sure it is activated in that environment.

The call in the client side does not return the expected response (returns empty), while on the BE side , it is producing a whole list of all available tours in the local DB.

Bitte neue API "minimal" erstellen. Dieser muss man die folgenden Werte übermitteln, um eine Antwort zu erhalten:

• key
• tour_provider
• tour_id
• city_slug

Das Resultat ist eine einzelne Zeile, mit den notwendigen Informationen.

Siehe dazu die neu angelegte View: SELECT * FROM zuugle_api.minimal;

On https://www.zuugle.at/suche?city=wien the hike "Kennenlernen am Wasserleitungsweg" (https://www.bahn-zum-berg.at/wienerwald/kennenlernen-am-wasserleitungsweg/) is shown (at least) two times - see screenshot:

Depending on https://github.com/orgs/bahnzumberg/projects/2/views/1?pane=issue&itemId=31349270 the columns "internal_status" and "connection_url", "return_url" in table fahrplan should be removed.
In table tours the column "children" and "internal_status" should be removed. If "publishing_date" is not used, remove it too.

After I pulled recent API changes, i get the following error when doing the import-data-full

• first/last nonregular stop
• json description

Remove table city_favourites from db (database.sql) and code (cities.js)

1. "p-limit": "^5.0.0", requires ESM.

I was unable to run npm run import-data-full under 20.3.0 or 19.8. The only solution which worked was to downgrade p-limit to 3.1, which doesn't require ESM.

2. The documentation does not provide any details about TourenDB. Where can I get it?

Tracking issue for:

• https://github.com/bahnzumberg/zuugle-api/security/code-scanning/61

What is the PostgreSQL table city_favourites used for?

The column month_order is always 1 in table tour.

SELECT
DISTINCT month_order
FROM tour

The command "npm run import-files" in the api directory is throwing errors: "Protocol error (Page.navigate): Cannot navigate to invalid URL"

Issue:
conditional rendering using Redux state works only for one of the conditions and not for the other.

Detailed description:
From our Postgres database we pull a value named totalTours through an axios call (see loadTotalTours()), this value we save in a state inside redux store (same name), this works fine but when we do access it (inside Start component) and attempt to conditionally render the Header -depending on the value of totalTours- we get the expected behaviour for a value of >0 but not when the value == 0, for that we get an error (“Rendered fewer hooks than expected. This may be caused by an accidental early return statement.”).

Related Files: (frontend "zuugle_suchseite" repo)
Start/Start.js, Start/Header.js, reducers/tours.js, actions/tourActions.js, actions/types.js, actions/crudActions.js

Tracking issue for:

• https://github.com/bahnzumberg/zuugle-api/security/code-scanning/52

Searching for "schnee" gives me autosuggestions - see first picture - when I have selected a city.
The second screenshot shows the same search for "schnee", but shows no auto suggestions.

Enhance the daily load by adding a daily import of the data in the table "interface_duplicate_tours_to_search". Import a full load of this table. It contains the tours, which are so similar to another, that we can look at them as duplicates.

Prerequisite: https://github.com/orgs/bahnzumberg/projects/4/views/1?pane=issue&itemId=31487720

in the API repo: Translate the templates/tour-details.html into the other languages (sl, en, fr, it)

https://github.com/pgvector/pgvector

Integrated with OpenAI: https://www.crunchydata.com/blog/whats-postgres-got-to-do-with-ai

Tracking issue for:

• https://github.com/bahnzumberg/zuugle-api/security/code-scanning/107

Translation feature for PDF - analog to the suchseite feature.

Once the translation is working, the PDF button should be active for all languages (undo https://github.com/orgs/bahnzumberg/projects/2/views/1?pane=issue&itemId=30353558).

uncomment code in authenticate.js and solve any open issues

Import column "allow_gpx_download" from vw_provider_to_search to enable https://github.com/orgs/bahnzumberg/projects/2?pane=issue&itemId=34295492

The function mergeToursWithFahrplan() is doing nothing other than updating the columns country_at, country_de, country_ch, country_it, country_fr and country_si. Is my analysis correct?

If yes, then we should deactivate it completely and remove the columns from the table as well.

Prevent SQL injection at the search. Maybe this helps: https://www.stackhawk.com/blog/react-command-injection-examples-and-prevention/

Tracking issue for:

• https://github.com/bahnzumberg/zuugle-api/security/code-scanning/120

In sync.js there is the function syncGPXdata. The purpose of the function is to fetch datachunks from the remote "vw_gpx_to_search" and store these into the local table "gpx".

The amount of data is huge. A delta load mechanism is worth thinking about. As a first step the while loop should run in a limited way in parallel. 5 to 10 instances can read and write in parallel without a problem.

export async function syncGPXdata(){
    try {
        await knex.raw(`TRUNCATE gpx;`);
    } catch(err){
        console.log('error: ', err)
    }

    let limit = 5000;
    const query_count = await knexTourenDb('vw_gpx_to_search').count('* as anzahl'); 
    let count_gpx = query_count[0]["anzahl"];
    let count_chunks = round(count_gpx / limit, 0);
    let counter = 0;

    console.log('GPX data count_chunks:', count_chunks)
    console.log('Info: Handling ', count_gpx.toLocaleString("de-de"), ' rows with gpx datapoints.');

    /* The following loop has to be parallised */
    while(counter < count_chunks){
        const result_query = knexTourenDb('vw_gpx_to_search').select('provider', 'hashed_url', 'typ', 'waypoint', 'lat', 'lon', 'ele').whereRaw(`ROUND(lat*lon*10000) % ${count_chunks} = ${counter}`);
        const result = await result_query;
            
        try {
            await knex('gpx').insert([...result]);
        } catch(err){
            console.log('error: ', err)
        }
        counter++;
    }
}

Remove the column from api , review related notes in issue #256

Front end seems to be impacted by changes in the API, after I pulled changes to API made last week , I am currently getting this (see screenshot)

move the file up one level

Abdul just showed me with this link, taht Autosuggestion is not working from the API side (or is wrongly calöled): https://www2.zuugle.at/api/searchPhrases?search=aben&city=amstetten&language=en

Change the load so, that the GPX coordinates are stored in a table in the PostgreSQL database. From there the GPX files should be created.