CREATION DE STATISTIQUE!

<%
Action = right(UCase(Request("type")),3)
If Action <> "" Then ' Arrivée du formulaire
   ' 2. Tri de la Base de Données

     dbPath = Server.MapPath("mdb/satisfaction.mdb")
   ' Création de l'objet permettant la connexion
     Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
   ' Connexion
     OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};" _
   & " DBQ=" & dbPath

if ((Request("jd") <> 0) and (Request("md") <> 0) and (Request("ad") <> 0) and (Request("jf") <> 0) and (Request("mf") <> 0) and (Request("af") <> 0) and request("moy") = 0)  then

SQLQuery = "SELECT * FROM stat WHERE date BETWEEN {d '"&Request("ad")&"-"&Request("md")&"-"&Request("jd")&"'} AND {d '"&Request("af")&"-"&Request("mf")&"-"&Request("jf")&"'} ORDER by date"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQLQuery, OBJdbConnection, 1, 2
      
        NbRecord = Rs.RecordCount - 1
        redim tabdate(NbRecord)
        redim tab1(NbRecord)
        redim tab2(NbRecord)
        redim tab3(NbRecord)
        redim tab4(NbRecord)

            
      i = 0
      While Not rs.eof
         tabdate(i) = rs("date")
         tab1(i) = (int(rs("champ1")*100))/100
         tab2(i) = (int(rs("champ2")*100))/100
         tab3(i) = (int(rs("champ3")*100))/100
         tab4(i) = (int(rs("champ4")*100))/100
         i=i+1
         Rs.MoveNext
      wend
rs.close


elseif (Request("moy") <> 0 ) then

  if(Request("moy") = 1) then valint = "ww"
  if(Request("moy") = 2) then valint = "m"
  if(Request("moy") = 3) then valint = "yyyy"
  if(Request("moy") = 4) then valint = "d"

  if ((Request("jd") <> 0) and (Request("md") <> 0) and (Request("ad") <> 0) and (Request("jf") <> 0) and (Request("mf") <> 0) and (Request("af") <> 0))  then
  ad = Request("ad")
  md = Request("md")
  jd = Request("jd")
  af = Request("af")
  mf = Request("mf")
  jf = Request("jf")
  else
  ad = 2003
  md = 1
  jd = 1
  af = year(date)
  mf = month(date)
  jf = day(date)
  end if

    seriald = dateSerial(ad,md,jd)
    serialf = dateSerial(af,mf,jf)

    diffdate = dateDiff(valint , seriald, serialf)

        redim tabdate(diffdate)
        redim tab1(diffdate)
        redim tab2(diffdate)
        redim tab3(diffdate)
        redim tab4(diffdate)

        i = 0
    for dx = 0 to diffdate

    countdated = dateAdd(valint,dx,seriald)
      ac = year(countdated)
      mc = month(countdated)
      jc = day(countdated)

    countdatef = dateAdd(valint,dx+1,seriald)
    countdatef = dateAdd("d",-1,countdatef)
      acf = year(countdatef)
      mcf = month(countdatef)
      jcf = day(countdatef)


    Set rs = Server.CreateObject("ADODB.Recordset")
    SQLQuery = "SELECT AVG(Champ1) AS moyenne1, AVG(Champ2) AS moyenne2, AVG(Champ3) AS moyenne3, AVG(Champ4) AS moyenne4 FROM stat WHERE date BETWEEN #"&ac&"-"&mc&"-"&jc&"# AND #"&acf&"-"&mcf&"-"&jcf&"#"
    rs.Open SQLQuery, OBJdbConnection, 1, 2

    if (rs("moyenne1") <> "") then
         
         tabdate(i) = jc&"/"&mc&"/"&ac
         if (valint = "yyyy") then
         tabdate(i) = "xx/xx/"&ac
         end if
         if (valint = "m") then
         tabdate(i) = "xx/"&mc&"/"&ac
         end if

         tab1(i) = (int(rs("moyenne1")*100))/100
         tab2(i) = (int(rs("moyenne2")*100))/100
         tab3(i) = (int(rs("moyenne3")*100))/100
         tab4(i) = (int(rs("moyenne4")*100))/100
         i = i + 1
    end if

    rs.close

    next

        redim PRESERVE tabdate(i-1)
        redim PRESERVE tab1(i-1)
        redim PRESERVE tab2(i-1)
        redim PRESERVE tab3(i-1)
        redim PRESERVE tab4(i-1)

 else
    Set rs = Server.CreateObject("ADODB.Recordset")
SQLQuery = "SELECT * FROM stat ORDER by date"
rs.Open SQLQuery, OBJdbConnection, 1, 2
NbRecord = Rs.RecordCount - 1
        redim tabdate(NbRecord)
        redim tab1(NbRecord)
        redim tab2(NbRecord)
        redim tab3(NbRecord)
        redim tab4(NbRecord)

       i = 0
      While Not rs.eof
         tabdate(i) = rs("date")
         tab1(i) = (int(rs("champ1")*100))/100
         tab2(i) = (int(rs("champ2")*100))/100
         tab3(i) = (int(rs("champ3")*100))/100
         tab4(i) = (int(rs("champ4")*100))/100
         i=i+1
         Rs.MoveNext
      wend
rs.close

end if

   ' 3. Création du fichier

   Set FSO = Server.CreateObject("Scripting.FileSystemObject")
   dim dir : dir = Server.MapPath("files")
   set inF = FSO.CreateTextFile(dir&"/"&Request("type"))

   ' Fichier au format Excel (.slk)
   if right(Request("type"),3)="slk" then
      inF.writeLine"ID;PWXL;N;E" ' Début du codage excel
      ' chr (34) remplace les guillements à la suite de l'instruction
      inF.writeLine"C;Y1;X1;K"&chr(34)&("Date")&chr(34)
      inF.writeLine"C;Y1;X2;K"&chr(34)&("Qualité d'accueil et d'écoute")&chr(34)
      inF.writeLine"C;Y1;X3;K"&chr(34)&("Qualité des réponses fournies")&chr(34)
      inF.writeLine"C;Y1;X4;K"&chr(34)&("Délai de résolution satisfaisant")&chr(34)
      inF.writeLine"C;Y1;X5;K"&chr(34)&("Facilité à joindre la hotline")&chr(34)
      i=2 ' Affichage à partir de la ligne 2
      
       for x = 0 to Ubound(tab1)
         inF.writeLine"C;Y"&x+2&";X1;K"&chr(34)&(tabdate(x))&chr(34)
         inF.writeLine"C;Y"&x+2&";X2;K"&chr(34)&(tab1(x))&chr(34)
         inF.writeLine"C;Y"&x+2&";X3;K"&chr(34)&(tab2(x))&chr(34)
         inF.writeLine"C;Y"&x+2&";X4;K"&(chr(34)&tab3(x))&chr(34)
         inF.writeLine"C;Y"&x+2&";X5;K"&chr(34)&(tab4(x))&chr(34)
       next
      inF.writeLine"E" ' Fin du codage excel

   ' ou fichier au format texte (.txt)
   else
   inF.writeLine("Notes :")
   inF.writeLine("champ1 = Qualité d'accueil et d'écoute"&chr(09)&chr(09)&chr(09)&"1 = Mauvais")
   inF.writeLine("champ2 = Qualité des réponses fournies"&chr(09)&chr(09)&chr(09)&"2 = Moyen")
   inF.writeLine("champ3 = Délai de résolution satisfaisant"&chr(09)&chr(09)&"3 = Satisfaisant")
   inF.writeLine("champ4 = Facilité à joindre la hotline"&chr(09)&chr(09)&chr(09)&"4 = Excellent")
   inF.writeLine("--------------------------------------------------------------------------")
   inF.writeLine("")
   inF.writeLine("")
   inF.writeLine     ("+----------+--------+--------+--------+--------+")
      inF.writeLine  ("|   Date   | champ1 | champ2 | champ3 | champ4 |")
   inF.writeLine     ("+----------+--------+--------+--------+--------+")
      for x = 0 to Ubound(tab1)

         tabdatelong = Len(tabdate(x))
         for xi = 1 to (10-tabdatelong)
           espace = espace & chr(32)
         next
        tabdate(x) = tabdate(x) & espace
        espace = ""
        
         tabdatelong = Len(tab1(x))
         for xi = 2 to (8-tabdatelong)
           espace = espace & chr(32)
         next
        tab1(x) = tab1(x) & espace
        espace = ""
        
         tabdatelong = Len(tab2(x))
         for xi = 2 to (8-tabdatelong)
           espace = espace & chr(32)
         next
        tab2(x) = tab2(x) & espace
        espace = ""
        
         tabdatelong = Len(tab3(x))
         for xi = 2 to (8-tabdatelong)
           espace = espace & chr(32)
         next
        tab3(x) = tab3(x) & espace
        espace = ""
        
         tabdatelong = Len(tab4(x))
         for xi = 2 to (8-tabdatelong)
           espace = espace & chr(32)
         next
        tab4(x) = tab4(x) & espace
        espace = ""
        

         inF.writeLine("|"&tabdate(x)&"| "&tab1(x)&"| "&tab2(x)&"| "&tab3(x)&"| "&tab4(x)&"|")
       next
      inF.writeLine     ("+----------+--------+--------+--------+--------+")
    inF.writeLine("")
    inF.writeLine("Fichier Généré automatiquement le "&date)
   end if
   inF.close

   ' 4. Fermeture de la connexion à la BD

   OBJdbConnection.close : set OBJdbConnection = nothing

   Response.Redirect"files/"&Request("type")
   end if
%>

Télécharger le zip