setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$startObj = new DateTime('2025-04-01 00:00:00');
$endObj = new DateTime('2025-04-17 23:59:59');
/* ---------- segmentos que tornam a diária inválida ------------- */
$segExcluidos = [
'Cortesia - agente de viagens','Cortesia - outros',
'Fracionado - distrato','Fracionado - semana',
'Uso da casa - funcionário','Uso da casa - proprietário',
'Reserva Teste','Back to home - semana (fracionado)',
'Cortesia - influencer','Fracionado - voucher cortesia',
'Vazias','Viagens de inspeção - cortesia'
];
/* placeholders :seg1,:seg2,... */
$segPlace=[];$params=[ ':start'=>$startObj->format('Y-m-d H:i:s'),
':end' =>$endObj->format('Y-m-d H:i:s') ];
foreach($segExcluidos as $i=>$seg){ $k=':seg'.($i+1); $segPlace[]=$k; $params[$k]=$seg; }
$notIn=implode(',',$segPlace);
/* ---------- consulta traz TODAS as diárias --------------------- */
$sql="
SELECT
id,
endOfDayDate AS dataDiaria,
originNumber AS voucher,
roomTypeName,
roomingNightCount AS RN,
amount AS Diarias,
mealPlanAmountPaxChd AS AeB,
amount+mealPlanAmountPaxChd AS DiariaPensao,
/* flag de validade (1 = válida, 0 = inválida) */
CASE WHEN dailyType='Regular' AND marketSegmentName NOT IN ($notIn) THEN 1 ELSE 0 END AS valida
FROM RoomingNights
WHERE endOfDayDate BETWEEN :start AND :end
AND reservationStatus <> 2
ORDER BY endOfDayDate, originNumber";
$st=$pdo->prepare($sql); $st->execute($params);
$rows=$st->fetchAll(PDO::FETCH_ASSOC);
/* ---------- totais globais ------------------------------------ */
$totValid=$totInvalid=0; $totDia=$totAb=$totTot=0;
foreach($rows as $r){
if($r['valida']){
$totValid += $r['RN']; // normalmente 1 por linha
$totDia += $r['Diarias'];
$totAb += $r['AeB'];
$totTot += $r['DiariaPensao'];
}else{
$totInvalid += $r['RN'];
}
}
$ticket = $totValid ? $totTot/$totValid : 0;
/* helpers */
function f2($v){return number_format((float)$v,2,',','.');}
function f0($v){return number_format((int)$v,0,'','.');}
?>
Diárias (com GUID)