[PHP] Calcolo prezzi camera in range di date diverse

perlanera

Nuovo Utente
7 Set 2016
7
0
1
53
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.203
207
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
53
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.203
207
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 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)
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.203
207
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
53
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
53
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.203
207
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
53
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.203
207
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 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
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
F Cerco Hosting con VECCHIE versioni di php Hosting 0
Cosina Captcha php PHP 1
S passare un valore da un form a un file .php con metodo post PHP 4
N php msyql PHP 6
N php problemi a visualizzare video PHP 3
A menu a tendina php PHP 1
D protezione cartelle: blocco visualizzazione/scaricamento contenuto, ma abilitazione utilizzo dati da parte di file .php presenti sul sito Web Server 1
F Php date_diff PHP 1
K [PHP] Aggiungere caratteri ad una stringa in base alla lunghezza della stessa PHP 2
C Wp-admin a file php WordPress 5
Lino80 [Retribuito] Cerco programmatore php per modifica/inserimento funzione/valori da un plugin importer wordpress Offerte e Richieste di Lavoro e/o Collaborazione 0
csi Inviare file jpg in locale alla stampante con php PHP 0
M Passaggio variabili array php su un tasto jq PHP 3
E Php aggiornamento tabella PHP 9
G phpmailer e php 8.1 con estensione mysqli PHP 6
M Invio dati database via email php PHP 0
K [php] Problema con inner join PHP 4
K [php]form invio dati PHP 0
P Codifica caratteri speciali mysql php PHP 0
K [PHP] Problema con variabili concatenate. PHP 1
E Stampante termica escpos-php PHP 6
JeiMax Modifica codice php personalizzato PHP 2
G Come modificare un pdf in php PHP 1
U Link a doppio file PHP PHP 0
E PHP & jQuery PHP 8
N Passare array da php a javascript PHP 5
F Applicazione PHP/MySQL per prenotazioni: limitare il numero massimo di posti prenotabili PHP 20
L tipo boolean non funzionante su mariadb (mysql). E codice php 7.4. PHP 0
U PHP creare un file excel dopo ricerca nel DB PHP 0
M PHP/MySQL - Estrarre valori min e max di ogni gruppo PHP 5
F Php e fatturazione elettronica PHP 0
P lanciare script asp (o php) da jquery Javascript 1
Couting95 inserire dati da un file di testo in una tabella in php PHP 1
P Data scraping in PHP non funziona PHP 4
C Calcoli matematici in php PHP 5
F Scrivere dei dati in word con php PHP 0
D PHP leggere cartella di Windows PHP 1
I dominio aruba versione php server linux Domini 3
G Colorare menu select attraverso ricerca php PHP 0
L PHP motore di ricerca nel sito PHP 1

Discussioni simili