[PHP] Calcolo prezzi camera in range di date diverse

[perlanera]

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]

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]

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]

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

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

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)

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")

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 id_struttura=@struttura
   AND id_tipologia=@tipologia
   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 id_struttura=@struttura
   AND id_tipologia=@tipologia
   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 id_struttura=@struttura
   AND id_tipologia=@tipologia
   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 id_struttura=@struttura
   AND id_tipologia=@tipologia
   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)

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

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,

$sql = "
declare @data_arrivo   AS datetime;
declare @data_partenza AS datetime;
......
) a
GROUP BY a.id_struttura, a.id_tipologia
";

risultato

 

[marino51]

nel "Calcolo del costo del soggiorno" non ho copiato la riga necessaria ad evidenziare il numero dei giorni

, 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

 

[perlanera]

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

 

[marino51]

La sto provando nel db come sql adattandola ma non mi funziona

esegui la sola select con tipo = 2 (del blocco delle 4 select)
modificata per mysql e nel caso ti dia errore, posta tutto il messaggio di errore

 

[perlanera]

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]

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]

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]

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

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

<?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 id_struttura=@struttura
   AND id_tipologia=@tipologia
   AND id_camera=@camera
   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 id_struttura=@struttura
   AND id_tipologia=@tipologia
   AND id_camera=@camera
   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 id_struttura=@struttura
   AND id_tipologia=@tipologia
   AND id_camera=@camera
   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 id_struttura=@struttura
   AND id_tipologia=@tipologia
   AND id_camera=@camera
   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

 

[perlanera]

ok grazie mille Marino proverò la tua soluzione. Buona giornata!