<% Function FormatMediumDate(DateValue) Dim strYYYY Dim strMM Dim strDD strYYYY = CStr(DatePart("yyyy", DateValue)) strMM = CStr(DatePart("m", DateValue)) If Len(strMM) = 1 Then strMM = "0" & strMM strDD = CStr(DatePart("d", DateValue)) If Len(strDD) = 1 Then strDD = "0" & strDD FormatMediumDate = strYYYY & " " & strMM & " " & strDD End Function %> <% Server.ScriptTimeout = 500 stOrder = request("Order") liensPer = liensPer&"Order="&stOrder &"&" stFormat = request("Format") stNom = RemoveJunk(Request("Nom")) stPrenom = RemoveJunk(Request("Prenom")) stSexe = Request("Sexe") stStatut = Request("Statut") iAge1 = Request("Age1") iAge2 = Request("Age2") if stNom <> "" then sSqlPer = sSqlPer & "Personnes.Nomreleve24 Like '" & stNom & "%' and " stext = stext &"Nom : " & stNom & " . " liensPer = liensPer &"Nom="&stNom &"&" end if if stPrenom <> "" then sSqlPer = sSqlPer & "Personnes.Prenomreleve24 Like '" & stPrenom & "%' and " stext = stext &"Prenom : " & stPrenom & " . " liensPer = liensPer &"Prenom="&stPrenom &"&" end if if iAge1 <> "" and iAge2 <> "" then sSqlPer = sSqlPer & "( year (date()) - year(Personnes.DDN) >= '" & iAge1 & "' and year (date()) - year(Personnes.DDN) <= '" & iAge2 & "' ) and " liensPer = liensPer &"Age1="&iAge1&"&Age2="&iAge2&"&" end if if iAge1 <> "" and iAge2 = "" then sSqlPer = sSqlPer & "( year (date()) - year(Personnes.DDN) = '" & iAge1 & "') and " liensPer = liensPer &"Age1="&iAge1&"&" end if '******Format excel******************************** stFormat = request("Format") 'The following 2 lines are what makes this happen... if stFormat = "E" then Response.ContentType ="application/vnd.ms-excel" Response.AddHeader "Content-Disposition", "attachment; filename=Releve24.xls" end if %>
<% '********************************** MontantT = 0 '*******NASR24********************************** sSQLP = "SELECT DISTINCT NASR24 FROM Personnes " if sSqlPer <> "" then sSQLP = sSQLP &"WHERE "& Left(sSqlPer, Len(sSqlPer) - 4)&"" end if sSQLP = sSQLP &" " Set rsQueryP = Conn.Execute(sSQLP) Do While Not rsQueryP.EOF '*******voir dans la table personnes les personnes avec NASR24******************** if rsQueryP("NASR24") <> "" then sSQLE = "SELECT * FROM Personnes where NASR24 = '"&rsQueryP("NASR24")&"' and (year (date()) - year(Personnes.DDN) <= 18) " Set rsQueryE = Conn.Execute(sSQLE) Do While Not rsQueryE.EOF nbrJours = 0 MontantT = 0 RembT = 0 sSQLA = " SELECT Inscriptions.* ,Personnes.* " sSQLA = sSQLA & "FROM (Personnes INNER JOIN Inscriptions ON Personnes.CodeM = Inscriptions.CodeM) INNER JOIN Activites ON Inscriptions.CodeA = Activites.CodeA " sSQLA = sSQLA & "WHERE Activites.Relve24 = True and Activites.anneeA = '2005' AND Personnes.CodeM = "&rsQueryE("CodeM")&" " Set rsQueryA = Conn.Execute(sSQLA) Do While Not rsQueryA.EOF if rsQueryA("NbrJours") <> "" then nbrJours = nbrJours + rsQueryA("NbrJours") end if '***** calculer les montant paye et le nombre de jours pour chaque enfant***************** sSQLIns = "SELECT SUM (Acomptes) as sAcomptes , SUM(Remb) as Remb1 FROM Payments where CodeIns = "&rsQueryA("CodeIns")&" and AnnulerR = false " Set rsQueryIns = Conn.Execute(sSQLIns) if Not rsQueryIns.EOF then if rsQueryIns("sAcomptes") <> "" then MontantT = MontantT + rsQueryIns("sAcomptes") end if if rsQueryIns("Remb1") <> "" then RembT = RembT + rsQueryIns("Remb1") end if end if rsQueryA.MoveNext Loop '*******voir si la personne est inscrit ou pas pour afficher l'information*********************** sSQLExist = " SELECT Inscriptions.* ,Personnes.* " sSQLExist = sSQLExist & "FROM (Personnes INNER JOIN Inscriptions ON Personnes.CodeM = Inscriptions.CodeM) INNER JOIN Activites ON Inscriptions.CodeA = Activites.CodeA " sSQLExist = sSQLExist & "WHERE Activites.Relve24 = True and Activites.anneeA = '2005' AND Personnes.CodeM = "&rsQueryE("CodeM")&" " Set rsQueryExist = Conn.Execute(sSQLExist) if not rsQueryExist.eof then if nbrJours <> 0 and MontantT <> 0 then %> <% end if end if rsQueryE.MoveNext Loop end if rsQueryP.MoveNext Loop %>
Nom Prenom DDN Nbrjours Prix NomR24 PrenomR24 NAS Adresse Ville Province Codepostal
<%=rsQueryE("Nom")%> <%=rsQueryE("Prenom")%> <% = FormatMediumDate(rsQueryE("DDN")) %> <%=nbrJours%> <% MontantAfficher = MontantT - RembT if (MontantAfficher <> "" and MontantAfficher > "0" ) then Response.Write FormatNumber(MontantAfficher, 2) else Response.Write "00.00" end if %> <%=rsQueryE("Nomreleve24")%> <%=rsQueryE("Prenomreleve24")%> <%=rsQueryP("NASR24")%> <%=rsQueryE("Adresse")%> <% if rsQueryE("Ville") <> "" then strSql1 = "SELECT * FROM Ville where CodeV = "& rsQueryE("Ville") Set rsQuery1 = Conn.Execute(strSql1) if not rsQuery1.eof then sNomV = rsQuery1("Nom") Response.Write sNomV end if end if %> <%=rsQueryE("Province")%> <%=rsQueryE("CodePostal")%>