1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
|
Function calcMoyenneTO(id_hotel As Integer, enquete_annee As Integer, enquete_mois As Integer) As Double
Dim moyTO As Double
Dim id_zone As Integer
If (teste_critere(id_hotel, enquete_annee, enquete_mois, id_zone) = True) Then
moyTO = renvoieMoyenneZoneTO(id_zone, enquete_annee, enquete_mois, "TO")
Else
moyTO = -1
End If
calcMoyenneTO = moyTO
End Function
Function renvoieMoyenneZoneTO(id_zone As Integer, enquete_annee As Integer, enquete_mois As Integer, AvgTOZone As String) As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim requete As DAO.QueryDef
Dim total_TO As Double
Dim moyenne_TO As Double
total_TO = 0
moyenne_TO = 0
Set db = CurrentDb
Set requete = db.CreateQueryDef("", _
"SELECT tbl_Hotels.IdZone, " + _
"IIf([NbReChambreDispo]<>0 And [nbrechambrelouee]<>0 And [NbreJourOuverture]<>0,[NbreChambreLouee]/[NbreChambreDispo],0) AS [TO], " + _
"IIf([NbreChambreLouee]<>0,[nbreArrivee]/[NbreChambreLouee],0) AS IF, " + _
"IIf([TO]<>0 And [PMC]<>0,[TO]*[PMC],0) AS RevPar, " + _
"tbl_Donnees.Annee, tbl_Donnees.Mois, tbl_Donnees.PMC, tbl_Donnees.IdHotel, tbl_Donnees.Annee, tbl_Donnees.Mois " + _
"FROM tbl_Hotels INNER JOIN tbl_Donnees ON tbl_Hotels.IdHotel = tbl_Donnees.IdHotel " + _
"WHERE (((tbl_Hotels.IdZone)=" + CStr(id_zone) + ") AND tbl_Donnees.Annee=" + CStr(enquete_annee) + _
" AND tbl_Donnees.mois=" + CStr(enquete_mois) + ");")
Set rs = requete.OpenRecordset
While Not (rs.EOF)
total_TO = total_TO + rs.Fields!TO
rs.MoveNext
Wend
moyenne_TO = total_TO / rs.RecordCount
renvoieMoyenneZoneTO = moyenne_TO
rs.Close
requete.Close
db.Close
End Function
Function calcMoyenneIF(id_hotel As Integer, enquete_annee As Integer, enquete_mois As Integer) As Double
Dim moyIF As Double
Dim id_zone As Integer
If (teste_critere(id_hotel, enquete_annee, enquete_mois, id_zone) = True) Then
moyIF = renvoieMoyenneZoneIF(id_zone, enquete_annee, enquete_mois, "IF")
Else
moyIF = -1
End If
calcMoyenneIF = moyIF
End Function |
Partager