[PHP] Calcolo prezzi camera in range di date diverse

perlanera

Nuovo Utente
7 Set 2016
7
0
1
50
Salve a tutti,
ho un problema su una query che dovrebbe calcolarmi il totale del prezzo da una tabella (prezzi) che presenta più varianti di prezzo in riferimento a range di date diverse.
La tabella (prezzi) ha i seguenti campi:
id
price
Start_date
End_date

Es. Seleziono il range di date per la quale la query dovrebbe calcolarmi il totale:
DATE SELEZIONATE Dal 01/08/2018 al 31/08/2018
I prezzi variano per periodi dal 1 al 31 agosto.
dal 01/08/2018 al 08/08/2018 il price è di euro 90,00 x notte Tot. 7 notti x 90 = 630
dal 09/08/2018 al 15/08/2018 il price è di euro 120,00 x notte Tot 6 notti x 120 = 720
dal 16/08/2018 al 31/08/2018 il price è di euro 80,00 x notte Tot 16 notti x 80 = 1280

Il totale dovrebbe essere : 2630 ma non funziona...

ho fatto in questo modo:

$queryprezzo="SELECT SUM( price * ( 1 + DATEDIFF( LEAST( End_date, '".$dataout."' - INTERVAL 1 DAY ) , GREATEST( Start_date, '".$datain."' ) ) ) ) AS Total FROM prezzi WHERE roomid='".$id." 'AND ('".$dataout."' - INTERVAL 1 DAY BETWEEN Start_date AND End_date OR '".$datain."' BETWEEN Start_date AND End_date ) AND prezzo_adulti=".$adulti." AND prezzo_bambini=".$bambini." ";

Cosa sbaglio? Grazie a chi vorrà illuminarmi

Spero di essere stato chiaro nell'illustrazione.
 

marino51

Utente Attivo
28 Feb 2013
3.039
192
63
Lombardia
se il periodo è continuo, dal 1-8 al 31-8 sono 30 giorni, perché il 31 è giorno di check-out

tu scrivi,
dal 01/08/2018 al 08/08/2018 il price è di euro 90,00 x notte Tot. 7 notti x 90 = 630
dal 09/08/2018 al 15/08/2018 il price è di euro 120,00 x notte Tot 6 notti x 120 = 720
dal 16/08/2018 al 31/08/2018 il price è di euro 80,00 x notte Tot 16 notti x 80 = 1280

7 + 6 + 16 = 29 .... qualche conto è sbagliato

da 1 a 8, sono 8 notti a 90 euro = 720 euro
da 9 a 15, sono 7 notti a 120 euro = 840 euro
da 16 a 31, sono 15 notti a 80 euro = 1200 euro

per un totale di 2760 euro

chi sbaglia di noi due ?

poi vediamo la query ....
 

perlanera

Nuovo Utente
7 Set 2016
7
0
1
50
Ciao e Grazie per la risposta. Si in effetti hai ragione ho sbagliato a fare i calcoli.
In ogni caso la query mi restituisce un risultato completamente sballato dall'importo di euro 2760.
 

marino51

Utente Attivo
28 Feb 2013
3.039
192
63
Lombardia
il mio sviluppo è provato su MS sql server,
le particolarità sono costituite da,

DATEDIFF(day, @data_arrivo, @data_partenza)
dove,
"day" specifica che il risultato deve esere in giorni
la prima data è la minore delle due
la seconda data è la maggiore delle due

le istruzioni DECLARE potrebbero non essere necessarie in mySql

la funzione CONVERT visualizza le date nel formato più conveniente

di seguito la tabella con i prezzi di listino,
si possono avere più colonne prezzo per dettagliare ciascun servizio fornito
Codice:
CREATE TABLE LISTINO
(
  id_prezzo          bigint      NOT NULL default 0
, id_struttura       bigint      NOT NULL default 0
, id_tipologia       bigint      NOT NULL default 0
, inizio_prezzo      datetime    NOT NULL
, fine_prezzo        datetime    NOT NULL
, descrizione        varchar(40)
, prezzo_1           float                default 0
, prezzo_2           float                default 0
, prezzo_3           float                default 0
);
esempio inserito nel listino
Codice:
truncate table LISTINO
insert into LISTINO values (21, 3, 1, '2018/01/01', '2018/07/31', '', 55,  0, 0)
insert into LISTINO values (22, 3, 1, '2018/08/01', '2018/08/08', '', 90,  0, 0)
insert into LISTINO values (23, 3, 1, '2018/08/09', '2018/08/15', '', 120, 0, 0)
insert into LISTINO values (24, 3, 1, '2018/08/16', '2018/08/31', '', 80,  0, 0)
insert into LISTINO values (25, 3, 1, '2018/09/01', '2018/12/31', '', 45,  0, 0)

Codice:
Note di funzionamento -----------------------------------------------------------------------------
1 arrivo e partenza interni ad un periodo               ( giorni = @data_partenza - @data_arrivo )
2 arrivo nel periodo, partenza in un periodo successivo ( giorni = fine_prezzo    - @data_arrivo )
3 partenza nel periodo, arrivo in un periodo precedente ( giorni = @data_partenza - inizio_prezzo )
4 arrivo e partenza debordano il periodo                ( giorni = fine_prezzo    - inizio_prezzo )
blocco delle select che calcolano il prezzo dei servizi (viene lavorato solo il "prezzo_1")
Codice:
SELECT
  1 AS tipo
, id_prezzo
, id_struttura
, id_tipologia
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, inizio_prezzo
, fine_prezzo
, DATEDIFF(day, @data_arrivo, @data_partenza) AS giorni
, prezzo_1 AS prezzo_adulto
  FROM LISTINO p
 WHERE [email protected]
   AND [email protected]
   AND @data_arrivo>=inizio_prezzo AND @data_partenza<=fine_prezzo
UNION
SELECT
  2 AS tipo
, id_prezzo
, id_struttura
, id_tipologia
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, inizio_prezzo
, fine_prezzo
, 1 + DATEDIFF(day, @data_arrivo, fine_prezzo) AS giorni
, prezzo_1 AS prezzo_adulto
  FROM LISTINO p
 WHERE [email protected]
   AND [email protected]
   AND @data_arrivo BETWEEN inizio_prezzo AND fine_prezzo
   AND @data_partenza NOT BETWEEN inizio_prezzo AND fine_prezzo
UNION
SELECT
  3 AS tipo
, id_prezzo
, id_struttura
, id_tipologia
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, inizio_prezzo
, fine_prezzo
, DATEDIFF(day, inizio_prezzo, @data_partenza) AS giorni
, prezzo_1 AS prezzo_adulto
  FROM LISTINO p
 WHERE [email protected]
   AND [email protected]
   AND @data_partenza>=inizio_prezzo AND @data_partenza<=fine_prezzo
   AND @data_arrivo NOT BETWEEN inizio_prezzo AND fine_prezzo
UNION
SELECT
  4 AS tipo
, id_prezzo
, id_struttura
, id_tipologia
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, inizio_prezzo
, fine_prezzo
, 1 + DATEDIFF(day, inizio_prezzo, fine_prezzo) AS giorni
, prezzo_1 AS prezzo_adulto
  FROM LISTINO p
 WHERE [email protected]
   AND [email protected]
   AND @data_arrivo<inizio_prezzo AND @data_partenza>fine_prezzo
Lista delle date componenti il prezzo
(ha lo scopo di verificare il funzionamento, normalmente non usata)
Codice:
declare @data_arrivo   AS datetime;
declare @data_partenza AS datetime;
declare @struttura     AS int;
declare @tipologia     AS int;

set @data_arrivo   = '2018/08/01';
set @data_partenza = '2018/08/31';
set @struttura     = 3;
set @tipologia     = 1;

SELECT
  a.tipo
, a.id_prezzo
, a.id_struttura
, a.id_tipologia
, CONVERT(varchar, a.data_arrivo,   103) AS data_arrivo
, CONVERT(varchar, a.data_partenza, 103) AS data_partenza
, CONVERT(varchar, a.inizio_prezzo, 103) AS inizio_prezzo
, CONVERT(varchar, a.fine_prezzo,   103) AS fine_prezzo
, a.giorni
, a.prezzo_adulto
, a.giorni * a.prezzo_adulto AS PrTotPeriodoAdulto
  FROM (
<<<<<-----blocco delle select che calcolano il prezzo dei servizi
) a
ORDER BY a.inizio_prezzo;

Calcolo del costo del soggiorno
Codice:
declare @data_arrivo   AS datetime;
declare @data_partenza AS datetime;
declare @struttura     AS int;
declare @tipologia     AS int;

set @data_arrivo   = '2018/08/01';
set @data_partenza = '2018/08/31';
set @struttura     = 3;
set @tipologia     = 1;

SELECT
  a.id_struttura
, a.id_tipologia
, CONVERT(varchar, MIN(a.data_arrivo),   103) AS data_arrivo
, CONVERT(varchar, MAX(a.data_partenza), 103) AS data_partenza
, CONVERT(varchar, MIN(a.inizio_prezzo), 103) AS inizio_prezzo
, CONVERT(varchar, MAX(a.fine_prezzo),   103) AS fine_prezzo
, SUM( a.giorni * a.prezzo_adulto ) AS PrTotPeriodoAdulto
  FROM (
<<<<<-----blocco delle select che calcolano il prezzo dei servizi
) a
GROUP BY a.id_struttura, a.id_tipologia
essendo la query risultante, lunga e complessa, ho preferito passare i valori necessari con le variabili "@"
in questo modo si evitano tutti gli apici, trovandoli solo nella definizione del valore del parametro
il codice necessario ad ottenere la "lista delle date componenti il prezzo" e il "calcolo del costo del soggiorno"
deve essere inserito in un'unica query per ciascuna funzione, esempio,
Codice:
$sql = "
declare @data_arrivo   AS datetime;
declare @data_partenza AS datetime;
......
) a
GROUP BY a.id_struttura, a.id_tipologia
";

risultato
upload_2018-1-12_12-48-25.png
 
Ultima modifica:

marino51

Utente Attivo
28 Feb 2013
3.039
192
63
Lombardia
nel "Calcolo del costo del soggiorno" non ho copiato la riga necessaria ad evidenziare il numero dei giorni
Codice:
, CONVERT(varchar, MAX(a.fine_prezzo),   103) AS fine_prezzo
, SUM( a.giorni )                             AS GiorniSoggiorno
, SUM( a.giorni * a.prezzo_adulto )           AS PrTotPeriodoAdulto
  FROM (
ed il relativo risultato

upload_2018-1-12_13-45-1.png
 

perlanera

Nuovo Utente
7 Set 2016
7
0
1
50
Ciao e grazie per l'illustrazione. La sto provando nel db come sql adattandola ma non mi funziona. Puoi indicarmi come trasformarla in php ? Grazie
 

perlanera

Nuovo Utente
7 Set 2016
7
0
1
50
Buonasera Marino,
credo ci sia un errore di fondo relativo ai campi presenti nella mia tabella.
in ogni caso ho provato la query con php e mi da il seguente errore:

Errore nella query SELECT 2 AS tipo , id , roomid , '2018-08-01' AS Start_date , '2018-08-31' AS End_date , inizio_prezzo , fine_prezzo , 1 + DATEDIFF(day, '2018-08-01', fine_prezzo) AS giorni , prezzo_1 AS prezzo_adulto FROM prezzi p WHERE roomid='7' AND '2018-08-01' BETWEEN inizio_prezzo AND fine_prezzo AND '2018-08-31' NOT BETWEEN inizio_prezzo AND fine_prezzo:

Di seguito trovi i campi della mia tabella prezzi:

Nome tabella: prezzi

Campi tabella:
id // id del prezzo
roomid // id della camera
Start_date // Data di arrivo
End_date // Data di partenza
price // il prezzo base per notte con colazione inclusa
prezzo_adulti // prezzo adulti si riferisce alla quantità di adulti presenti nella camera ad es. 2
prezzo_bambini // prezzo adulti si riferisce alla quantità di adulti presenti nella camera ad es. 0
mezza_pensione // prezzo per notte con mezza pensione inclusa
pensione_completa // prezzo per notte con pensione completa

Ti ringrazio per la disponibilità e spero tu possa aiutarmi perché non sono un esperto di php mi ci sono avvicinato da poco.
 

marino51

Utente Attivo
28 Feb 2013
3.039
192
63
Lombardia
suggerisco che sia tu ad adattare la soluzione alla tua tabella,
mi sembra che la tua tabella sia copia della mia con nomi cambiati
io utilizzo "struttura" e "tipologia", tu solo "roomid" ebbene costa poco adattare la soluzione
buon lavoro
 

perlanera

Nuovo Utente
7 Set 2016
7
0
1
50
Ho fatto proprio ciò che mi hai appena scritto ma non va. In ogni caso grazie per tutto ciò che mi hai indicato. Buon lavoro anche a te
 

marino51

Utente Attivo
28 Feb 2013
3.039
192
63
Lombardia
riprendendo la struttura della tabella,
con un editor qualsiasi usando la funzione "sostituisci tutto", cambi i nomi con quelli che ti servono,
ed eventualmente elimini ciò che non ti serve dalle query
Codice:
CREATE TABLE LISTINO
(
  id_prezzo          bigint      NOT NULL default 0
, id_struttura       bigint      NOT NULL default 0
, id_tipologia       bigint      NOT NULL default 0
, id_camera          bigint      NOT NULL default 0
, inizio_prezzo      datetime    NOT NULL
, fine_prezzo        datetime    NOT NULL
, descrizione        varchar(40)
, pr_base            float                default 0 /* prezzo base per notte con colazione inclusa */
, pr_adulto          float                default 0 /* prezzo adulti per notte */
, pr_bambino         float                default 0 /* prezzo bambini per notte */
, pr_mezzaPens       float                default 0 /* trattamento mezza pensione */
, pr_pensCompleta    float                default 0 /* trattamento pensione completa */
);

ti ho preparato uno script php (minimo) che dovrebbe darti l'idea per svilupparlo, considera che,
utilizzo metodi miei, che ho inserito nell'estensione della classe PDO, per leggere il database
per esempio, questo metodo "$data = $db->QueryS($sql);", mi restituisce l'intero set di record selezionati

quindi dovrai sostituire la gestione del db con quella che usi

nello script ci sono le due funzioni principali "sql_lista", "sql_calcolo" che assemblano la query necessaria ad interrogare il db

dovrai ccompletare le query inserendo gli altri costi, ma hai l'esempio di un costo già inserito

di seguito lo script
Codice:
<?php

require_once 'myUtils/ClassTheBestDB.php';
$db=new TheBestDB('sqlsrv:Server=mySERVERname;Database=testDB','myUSERname','myPASSWORD');

$sql  = sql_lista(3, 1, 8, '2018/08/01', '2018/08/31');
$data = $db->QueryS($sql);

$tabella = "<table border=1>"
         . "<thead><tr>"
         . "<th>select</th>"
         . "<th>ID</th>"
         . "<th>struttura</th>"
         . "<th>tipologia</th>"
         . "<th>camera</th>"
         . "<th>arrivo</th>"
         . "<th>partenza</th>"
         . "<th>inizio prezzo</th>"
         . "<th>fine prezzo</th>"
         . "<th>giorni</th>"
         . "<th>prezzo</th>"
         . "<th>totale</th>"
         . "</tr></thead><tbody>";

echo show_var($tabella, $data);

$sql  = sql_calcolo(3, 1, 8, '2018/08/01', '2018/08/31');
$data = $db->QueryS($sql);

$tabella = "<table border=1>"
         . "<thead><tr>"
         . "<th>struttura</th>"
         . "<th>tipologia</th>"
         . "<th>camera</th>"
         . "<th>arrivo</th>"
         . "<th>partenza</th>"
         . "<th>inizio prezzo</th>"
         . "<th>fine prezzo</th>"
         . "<th>giorni</th>"
         . "<th>totale</th>"
         . "</tr></thead><tbody>";

echo show_var($tabella, $data);

echo "DONE !";


function show_var($tabella, $data)
{
  $param1 = count($data);    if(!$param1) { echo "non ho trovato record (1)"; return; }
  $param2 = count($data[0]); if(!$param2) { echo "non ho trovato record (2)"; return; }

  for ($i = 0; $i < $param1; $i++)
  {
    $tabella.='<tr>';
    for ($j = 0; $j < $param2; $j++)
    {
      $tabella.='<td width="90" align="center">'.$data[$i][$j].'</td>';
    }
    $tabella.='</tr>';
  }
  $tabella.= '</tbody></table><br /><br />';
  return $tabella;
}


function sql_lista($str, $tp, $camera, $dta, $dtp)
{
return sql_def($str, $tp, $camera, $dta, $dtp) . "
SELECT
  a.tipo
, a.id_prezzo
, a.id_struttura
, a.id_tipologia
, a.id_camera
, CONVERT(varchar, a.data_arrivo,   103) AS data_arrivo
, CONVERT(varchar, a.data_partenza, 103) AS data_partenza
, CONVERT(varchar, a.inizio_prezzo, 103) AS inizio_prezzo
, CONVERT(varchar, a.fine_prezzo,   103) AS fine_prezzo
, a.giorni
, a.prezzo_base
, a.giorni * a.prezzo_base               AS tot_pr_base
  FROM (
" . sql_base() . "
) a
ORDER BY a.inizio_prezzo;
";
}

function sql_calcolo($str, $tp, $camera, $dta, $dtp)
{
return sql_def($str, $tp, $camera, $dta, $dtp) . "
SELECT
  a.id_struttura
, a.id_tipologia
, a.id_camera
, CONVERT(varchar, MIN(a.data_arrivo),   103) AS data_arrivo
, CONVERT(varchar, MAX(a.data_partenza), 103) AS data_partenza
, CONVERT(varchar, MIN(a.inizio_prezzo), 103) AS inizio_prezzo
, CONVERT(varchar, MAX(a.fine_prezzo),   103) AS fine_prezzo
, SUM( a.giorni )                             AS GiorniSoggiorno
, SUM( a.giorni * a.prezzo_base )             AS tot_pr_base
  FROM (
" . sql_base() . "
) a
GROUP BY a.id_struttura, a.id_tipologia, a.id_camera
";
}

function sql_def($str, $tp, $camera, $dta, $dtp)
{
return "
declare @struttura     AS int;
declare @tipologia     AS int;
declare @camera        AS int;
declare @data_arrivo   AS datetime;
declare @data_partenza AS datetime;

set @struttura     = " . $str . ";
set @tipologia     = " . $tp . ";
set @camera        = " . $camera . ";
set @data_arrivo   = '" . $dta . "';
set @data_partenza = '" . $dtp . "';
";
}

function sql_base()
{
return "
SELECT
  1 AS tipo
, id_prezzo
, id_struttura
, id_tipologia
, id_camera
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, inizio_prezzo
, fine_prezzo
, DATEDIFF(day, @data_arrivo, @data_partenza) AS giorni
, pr_base AS prezzo_base
  FROM LISTINO p
 WHERE [email protected]
   AND [email protected]
   AND [email protected]
   AND @data_arrivo>=inizio_prezzo AND @data_partenza<=fine_prezzo
UNION
SELECT
  2 AS tipo
, id_prezzo
, id_struttura
, id_tipologia
, id_camera
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, inizio_prezzo
, fine_prezzo
, 1 + DATEDIFF(day, @data_arrivo, fine_prezzo) AS giorni
, pr_base AS prezzo_base
  FROM LISTINO p
 WHERE [email protected]
   AND [email protected]
   AND [email protected]
   AND @data_arrivo BETWEEN inizio_prezzo AND fine_prezzo
   AND @data_partenza NOT BETWEEN inizio_prezzo AND fine_prezzo
UNION
SELECT
  3 AS tipo
, id_prezzo
, id_struttura
, id_tipologia
, id_camera
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, inizio_prezzo
, fine_prezzo
, DATEDIFF(day, inizio_prezzo, @data_partenza) AS giorni
, pr_base AS prezzo_base
  FROM LISTINO p
 WHERE [email protected]
   AND [email protected]
   AND [email protected]
   AND @data_partenza>=inizio_prezzo AND @data_partenza<=fine_prezzo
   AND @data_arrivo NOT BETWEEN inizio_prezzo AND fine_prezzo
UNION
SELECT
  4 AS tipo
, id_prezzo
, id_struttura
, id_tipologia
, id_camera
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, inizio_prezzo
, fine_prezzo
, 1 + DATEDIFF(day, inizio_prezzo, fine_prezzo) AS giorni
, pr_base AS prezzo_base
  FROM LISTINO p
 WHERE [email protected]
   AND [email protected]
   AND [email protected]
   AND @data_arrivo<inizio_prezzo AND @data_partenza>fine_prezzo
";
}

?>

se dovessi avere ancora bisogno di supporto, per favore, non scrivere "non funziona"
ma posta
l'errore, descritto in modo completo (vedi log di php) e
lo script (o parte di esso) necessario ad individuare l'errore

questo il risultato dello script
upload_2018-1-13_10-31-52.png
 
Discussioni simili
Autore Titolo Forum Risposte Data
M Problema con php per calcolo costo percentuale PHP 7
A [PHP] calcolo numero di ore effettuate PHP 7
M [PHP] Visualizzare nell'alert il calcolo PHP 4
maxnegri [PHP]Funzione di calcolo prezzo di una camera in un range di date PHP 62
Z [PHP] Calcolo giorno PHP 5
S [PHP] Calcolo Rate PHP 2
T [PHP] Calcolo anzianità soggetti [era: Help me :( Esercizio per me impossibile] PHP 9
S calcolo php PHP 2
D calcolo fra date php PHP 2
M php / mysql calcolo prezzo base preventivo per hotel PHP 34
M Visulizzare immagine con php PHP 8
G [PHP] Creare script di prenotazione con controllo disponibilità. PHP 7
G leggere file txt e stampare con php il contenuto a video PHP 7
F Ricreare struttura php+mysql su Xampp Apache 0
Z PHP.INI - STMP per invio email con PHP Server Dedicati e VPS 0
M Array associativi php su 2 campi mysql PHP 10
G Invio form con PHP PHP 3
T fatture con voci fattura in php PHP 0
T ALTRO PROBLEMA CON ARRAY PHP PHP 1
M Collegamento tra form html e script php PHP 4
M Problemi con la stampa dei valori in php PHP 1
W [Cerco collaborazioni] Sviluppatore Web (PHP) Offerte e Richieste di Lavoro e/o Collaborazione 1
D passare valori da database sql a php PHP 1
L Ricezione dei dati su file php da modulo html PHP 6
E Inviare variabile a PHP da ciclo in JS Javascript 0
A form PHP prenotazione tramite query PHP 2
A Form php prenotazione di un azienda sanitaria locale presso studio medico PHP 1
F menù select dinamico da db in php PHP 3
L Problemi form Pagina php HTML e CSS 3
L php mysql non salva solo id PHP 21
L php mysql cerca e visualizza pagina PHP 0
F Il codice php è giusto? PHP 2
R Aggiornare record mysql con Ajax, jQuery e php Ajax 2
A invio massivo dati a file php Javascript 4
Z MySql injection PHP PHP 1
V PHP form intersecate PHP 0
I [Offro][Retribuito] Programmatore Php Offerte e Richieste di Lavoro e/o Collaborazione 0
P Funzione jQuery Ajax invio file a php jQuery 1
C Dopo chiusura del tag php la stringa html va a capo PHP 1
E Transaction php PHP 11
B ciclare file xml con PHP PHP 1
L Estrazione dati php Database 6
A Aiuto per pagina php PHP 0
E Php select option e ajax PHP 23
I Aiuto php Dependent Lookup PHP 0
T arretramento versione PHP... PHP 3
D problema php mysql PHP 1
D problema php mysql PHP 1
E Barra di avanzamento codice PHP PHP 4
G creazione menu a tendina e invio a pagina php PHP 1

Discussioni simili