sql expert needed for the query task
Budget: $10 – $30 USD
Project price Ischiahotel.net
1) Fix the control panel, only update rates
This is the link for access to the control panel, i made some words in english language
htps://www.test.ischiahotel.net/admin/oferte/
When you add a new ofer you have to select the period from and to and add more price clicking on + blu link
under the price word
Here you can add price from 1 to 14 nights, for example you can add this ofer:
From 02/01/2023 to 31/01/2023
7 nights: 350 €
3 nights: 180 €
2 nights: 140 €
this mean all people want to stay in this hotel between this days will pay this prices
for example if a person want to stay 7 nights from 22 to 29/01/2023 will pay 350 € per person in double room
For this work i have 3 tables in database
1) oferte (where we add date and details of this ofer)
2) tarife (where we add this price (350, 180, 140) in this example
3) prezzi_per_note (where we add day by day the price for each night with the minimum stay)
In the control panel we have to fx the update ofer because if this ofer increese days, for example from
02/01/2023 to 08/02/2023 i can not insert this new days in the prezzi_per_note table, i'm able only to edit
the original date.
SEARCH RESULTS
your old work extract us the price from tarife table but we had many problems and sometmes not worked,
now is beter to make su sum using price per day bringing it from prezzi_per_note table
In this case we have price for each night and the price will be sure correct
In this table we add:
– id_prezzo (in english price_id, is autoincrement feld)
– id_tarifa (is the id of tarif in the table tarif)
– id_hotel (is the id of hotel who has this price)
– id_oferta (is id of ofer from table oferte, so we know all details of it)
– data_prezzo (is the single day)
– prezzo_note (is the price per day)
– prezzo_minimo_not (is the number of night for show this price)
– prezzo_atvo (if is 0 the price is actve, if is 1 the price is not actve)
Example of search result per day with single price for each day
2023-01-20: 50 €
2023-01-21: 60 €
2023-01-22:70 €
so if someone will search from 20-22 jannuary the price per person is: 50+60+70= 180 €
All the price are based on double room, so in this case, if user select 2 adults we have to show 360 €
In the form search is possible to search for only 1 person, in this case if in the table oferte the
supplemento_singola or supplemento_doppia_uso_singola have a value we have to add this value to the
price, for example if the supplemento_singola is 10 for this period we have 180 € + 10+10+10: total price for
single room 210 €
The same is if user select 3rd and 4th bed, we will add discount for this based on the age and when user will
search in the form for 2 adults + 1 children 5 years old we will show 180+180+discount for this range age
The search result page is ricerca.php
1) Fix the control panel, only update rates
This is the link for access to the control panel, i made some words in english language
htps://www.test.ischiahotel.net/admin/oferte/
When you add a new ofer you have to select the period from and to and add more price clicking on + blu link
under the price word
Here you can add price from 1 to 14 nights, for example you can add this ofer:
From 02/01/2023 to 31/01/2023
7 nights: 350 €
3 nights: 180 €
2 nights: 140 €
this mean all people want to stay in this hotel between this days will pay this prices
for example if a person want to stay 7 nights from 22 to 29/01/2023 will pay 350 € per person in double room
For this work i have 3 tables in database
1) oferte (where we add date and details of this ofer)
2) tarife (where we add this price (350, 180, 140) in this example
3) prezzi_per_note (where we add day by day the price for each night with the minimum stay)
In the control panel we have to fx the update ofer because if this ofer increese days, for example from
02/01/2023 to 08/02/2023 i can not insert this new days in the prezzi_per_note table, i'm able only to edit
the original date.
SEARCH RESULTS
your old work extract us the price from tarife table but we had many problems and sometmes not worked,
now is beter to make su sum using price per day bringing it from prezzi_per_note table
In this case we have price for each night and the price will be sure correct
In this table we add:
– id_prezzo (in english price_id, is autoincrement feld)
– id_tarifa (is the id of tarif in the table tarif)
– id_hotel (is the id of hotel who has this price)
– id_oferta (is id of ofer from table oferte, so we know all details of it)
– data_prezzo (is the single day)
– prezzo_note (is the price per day)
– prezzo_minimo_not (is the number of night for show this price)
– prezzo_atvo (if is 0 the price is actve, if is 1 the price is not actve)
Example of search result per day with single price for each day
2023-01-20: 50 €
2023-01-21: 60 €
2023-01-22:70 €
so if someone will search from 20-22 jannuary the price per person is: 50+60+70= 180 €
All the price are based on double room, so in this case, if user select 2 adults we have to show 360 €
In the form search is possible to search for only 1 person, in this case if in the table oferte the
supplemento_singola or supplemento_doppia_uso_singola have a value we have to add this value to the
price, for example if the supplemento_singola is 10 for this period we have 180 € + 10+10+10: total price for
single room 210 €
The same is if user select 3rd and 4th bed, we will add discount for this based on the age and when user will
search in the form for 2 adults + 1 children 5 years old we will show 180+180+discount for this range age
The search result page is ricerca.php