Forum « Pb d'affichage de données »

Forum « Programmation ASP » (archives)

Pb d'affichage de données

Envoyé: 27 octobre 2005, 6h42 par karen

Bonjour à tous ,

J'ai récupéré le programme d'une personne sur une mission et je dois faire des modif

J'ai un problème d'affichage des champs de la requête.

Lorsque je fais un document.write rs.field(1), je n'ai pas le même résultat que lui dans son tableau.

voilà le source :

<%@ Language=VBScript %>
<html>

<form method="POST" action="index.asp">

<SCRIPT LANGUAGE="JavaScript">

</script>
<%
strCnn = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=;Data Source=HQ-SQL02"
Set Conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
conn.ConnectionString = strCnn
conn.Open
%>

<%
If session("PassOK")= "OK" then

sql="SELECT DISTINCT CAST(SUBSTRING(CAST(IDMONTH AS CHAR(6)),1,LEN(IDMONTH)-4) AS DECIMAL(2,0)) FROM [LECTA_SALES].DBO.[MONTH]"
rs.open sql,conn
ListeMois= "<option>" & request.form("MOIS") & "</option>"
ValeurMois= " AND SUBSTRING([LECTA_SALES].DBO.MONTH.MONTHDESC,1,LEN([LECTA_SALES].DBO.MONTH.MONTHDESC)-4) ='" & request.form("MOIS") & "'"
TableMois="[SALES_ODS].DBO.[SAFE_DATA_" & ucase(left(request.form("MOIS"),3)) & "]"
while not rs.eof
ListeMois= ListeMois & "<option>" & MonthName(rs.fields(0)) & "</option>"
rs.movenext
wend
rs.close

sql="SELECT DISTINCT IDYEAR FROM [LECTA_SALES].DBO.[YEAR]"
rs.open sql,conn
ListeAnnee= "<option>" & request.form("ANNEE") & "</option>"
ValeurAnnee= " AND [LECTA_SALES].DBO.YEAR.IDYEAR =" & request.form("ANNEE")
while not rs.eof
ListeAnnee= ListeAnnee & "<option>" & rs.fields(0) & "</option>"
rs.movenext
wend
rs.close

ValeurCompany= " AND [LECTA_SALES].DBO.COMPANY.COMPDES = '" & session("COMP") & "'"

sql="SELECT DISTINCT FORMDES FROM [LECTA_SALES].DBO.[FORMPRES]"
rs.open sql,conn
if request.form("FORMAT")<>"All Format" and request.form("FORMAT")<>"" then
ListeFormat= "<option>" & request.form("FORMAT") & "</option><option>All Format</option>"
'ValeurFormat= " AND [LECTA_SALES].DBO.FORMPRES.FORMDES = '" & request.form("FORMAT") & "'"
ValeurFormat= " AND " & TableMois & ".FORMCOD = '" & left(request.form("FORMAT"),1) & "'"
else
ListeFormat= "<option>All Format</option>"
end if
while not rs.eof
ListeFormat= ListeFormat & "<option>" & rs.fields(0) & "</option>"
rs.movenext
wend
rs.close

if request.form("INVDEL")="Invoiced" or request.form("INVDEL")="" then
ListeCountry= "<option>Invoiced</option>"
ValeurCountry= " AND [LECTA_SALES].DBO.COUNTRY.CTRYCOD=" & TableMois & ".CTRYINVCOD "
else
ListeCountry= "<option>Delivered</option>"
ValeurCountry= " AND [LECTA_SALES].DBO.COUNTRY.CTRYCOD=" & TableMois & ".CTRYCOD "
end if
%>

<p><select size="1" name="MOIS">
<%Response.Write ListeMois%>
</select>
<select size="1" name="ANNEE">
<%Response.Write ListeAnnee%>
</select>
<b><font face="Verdana"><% response.write session("COMP")%></font></b>
<select size="1" name="FORMAT">
<%Response.Write ListeFormat%>
</select>
<select size="1" name="INVDEL">
<%Response.Write ListeCountry%>
<option>Invoiced</option>
<option>Delivered</option>
</select>
<input name='Filter' type='Submit' value='Filter'>
</p>

<%
if request.form("Validation")<>"" then

Dim Mailer
Set Mailer = Server.CreateObject("CDONTS.NewMail")
Mailer.To = "workflow@lecta.com"
Mailer.From = "Workflow"
Mailer.Subject = "Validation of " & session("COMP") & "'s the Values"
Mailer.Body = "Validation of the Values for " & request.form("MOIS") & " - " & request.form("ANNEE") & " - " & session("COMP") & " are OK"
Mailer.Importance=2
Mailer.Send
response.write "<p align=center><b><font face='Verdana'>A validation mail has been sent</font></b></p>"

sql= "DELETE FROM [SALES_ODS].DBO.CHECK_TABLE_LOADED WHERE IDTABLELOADED = '" & _
request.form("MOIS") & "-" & request.form("ANNEE") & "-" & session("COMP") & "'"

conn.Execute sql

end if

if request.form("MOIS")<>"" and request.form("ANNEE")<>"" then

sql="SELECT distinct" & _
" [LECTA_SALES].[dbo].[FACT_TABLE].[invhnum]," & _
" [LECTA_SALES].[dbo].[FACT_TABLE].[invdate]" & _
" FROM [LECTA_SALES].[dbo].[FACT_TABLE]," & TableMois & "," & _
" [LECTA_SALES].DBO.[COMPANY], [LECTA_SALES].DBO.[DATE]," & _
" [LECTA_SALES].DBO.[MONTH]," & _
" [LECTA_SALES].DBO.[QUARTER]," & _
" [LECTA_SALES].DBO.[YEAR]" & _
" WHERE" & _
" ([LECTA_SALES].DBO.YEAR.IDYEAR=[LECTA_SALES].DBO.QUARTER.IDYEAR" & _
" AND [LECTA_SALES].DBO.MONTH.IDQUARTER=[LECTA_SALES].DBO.QUARTER.IDQUARTER" & _
" AND [LECTA_SALES].DBO.MONTH.IDMONTH=[LECTA_SALES].DBO.[DATE].IDMONTH" & _
" AND " & TableMois & ".INVDATE=[LECTA_SALES].DBO.[DATE].INVDATE" & _
" AND " & TableMois & ".COMPCOD = [LECTA_SALES].DBO.COMPANY.COMPCOD)" & _
" AND ([LECTA_SALES].[dbo].[FACT_TABLE].[invhnum]=" & TableMois & ".[invhnum])" & _
ValeurAnnee & ValeurCompany & _
" AND (RTRIM (cast(month([LECTA_SALES].[dbo].[FACT_TABLE].[invdate]) as char(2))) + cast(year([LECTA_SALES].[dbo].[FACT_TABLE].[invdate]) as char(4))) <>" & _
" (SELECT distinct [LECTA_SALES].DBO.[DATE].[IDMONTH]" & _
" FROM [LECTA_SALES].DBO.[DATE]," & _
" [LECTA_SALES].DBO.[MONTH]," & _
" [LECTA_SALES].DBO.[QUARTER]," & _
" [LECTA_SALES].DBO.[YEAR]" & _
" WHERE ([LECTA_SALES].DBO.YEAR.IDYEAR=[LECTA_SALES].DBO.QUARTER.IDYEAR" & _
" AND [LECTA_SALES].DBO.MONTH.IDQUARTER=[LECTA_SALES].DBO.QUARTER.IDQUARTER" & _
" AND [LECTA_SALES].DBO.MONTH.IDMONTH=[LECTA_SALES].DBO.[DATE].IDMONTH" & _
" AND (SUBSTRING([LECTA_SALES].DBO.MONTH.MONTHDESC,1,LEN([LECTA_SALES].DBO.MONTH.MONTHDESC)-4) ='" & request.form("MOIS") & "'" & _
" AND [LECTA_SALES].DBO.YEAR.IDYEAR = " & request.form("ANNEE") & ")))"

rs.open sql,conn
if rs.bof= false then
rs.MoveFirst
Response.write "<p><b><font face='Verdana' color='#FF0000' size='2'>WARNING : We inform you that if you validate these values, the following invoices will be updated.</font></b></p>"

Response.Write "<div align='center'><table border='1' width='1%' id='table1' style='font-family: Verdana; font-size: 8pt; border-collapse:collapse'>"

while not rs.eof
Response.Write "<tr>"
Response.Write "<td bgcolor='#FF0000'><b>"
Response.Write rs.fields(0)
Response.Write "</b></td>"
Response.Write "<td bgcolor='#FF0000'><b>"
Response.Write rs.fields(1)
Response.Write "</b></td>"
Response.Write "</tr>"
rs.movenext

wend

end if
rs.close

sql= "SELECT COUNT(*) FROM [SALES_ODS].DBO.CHECK_TABLE_LOADED WHERE IDTABLELOADED = '" & _
request.form("MOIS") & "-" & request.form("ANNEE") & "-" & session("COMP") & "'"
rs.open sql,conn
if rs.fields(0)<>0 then
response.write "<input name='Validation' type='Submit' value='Validation of the Values " & request.form("MOIS") & " - " & request.form("ANNEE") & " - " & session("COMP") & "'></p>"
else
response.write "<p><b><font face='Verdana' color='#FF0000' size='2'>You can't validated those values.</font></b></p>"
end if
rs.close

sql="SELECT [LECTA_SALES].DBO.COUNTRY.CTRYDES AS COUNTRY," & _
" SUM(" & TableMois & ".[GSPREUT]* " & TableMois & ".[INVOWGT]) AS [GROSS_SALES_PRICE]," & _
" SUM(" & TableMois & ".[TRNBEUT]* " & TableMois & ".[INVOWGT]/1000) AS [TURNOVER_BONUS]," & _
" SUM(" & TableMois & ".[NSPREUT]* " & TableMois & ".[INVOWGT]/1000) AS [NET_SALES_PRICE]," & _
" SUM(" & TableMois & ".[COMMEUT]* " & TableMois & ".[INVOWGT]/1000) AS [SELLING_COST]," & _
" SUM(" & TableMois & ".[DISTEUT]* " & TableMois & ".[INVOWGT]/1000) AS [DISTR_COST]," & _
" SUM(" & TableMois & ".[EXWPEUT]* " & TableMois & ".[INVOWGT]/1000) AS [EX_WORKS_PRICE]," & _
" SUM(" & TableMois & ".[EXWMEUT]* " & TableMois & ".[INVOWGT]/1000) AS [EX_WORKS_MARGIN]," & _
" SUM(" & TableMois & ".INVOWGT) /1000 AS TONS" & _

" FROM " & TableMois & ", [LECTA_SALES].DBO.[COUNTRY], [LECTA_SALES].DBO.[COMPANY]," & _
" [LECTA_SALES].DBO.[DATE], [LECTA_SALES].DBO.[MONTH], [LECTA_SALES].DBO.[QUARTER], [LECTA_SALES].DBO.[YEAR]" & _
" WHERE ([LECTA_SALES].DBO.YEAR.IDYEAR=[LECTA_SALES].DBO.QUARTER.IDYEAR " & _
" AND [LECTA_SALES].DBO.MONTH.IDQUARTER=[LECTA_SALES].DBO.QUARTER.IDQUARTER" & _
" AND [LECTA_SALES].DBO.MONTH.IDMONTH=[LECTA_SALES].DBO.[DATE].IDMONTH " & _
" AND " & TableMois & ".INVDATE=[LECTA_SALES].DBO.[DATE].INVDATE)" & _
" AND " & TableMois & ".COMPCOD = [LECTA_SALES].DBO.COMPANY.COMPCOD" & _
ValeurCountry & ValeurMois & ValeurAnnee & ValeurCompany & ValeurFormat & _
" AND " & TableMois & ".[DATEPROCESS] = " & _
" ( SELECT MAX(" & TableMois & ".[DATEPROCESS])" & _
" FROM " & TableMois & ", [LECTA_SALES].DBO.[COMPANY], [LECTA_SALES].DBO.[DATE], [LECTA_SALES].DBO.[MONTH], [LECTA_SALES].DBO.[QUARTER], [LECTA_SALES].DBO.[YEAR]" & _
" WHERE ([LECTA_SALES].DBO.YEAR.IDYEAR=[LECTA_SALES].DBO.QUARTER.IDYEAR " & _
" AND [LECTA_SALES].DBO.MONTH.IDQUARTER=[LECTA_SALES].DBO.QUARTER.IDQUARTER" & _
" AND [LECTA_SALES].DBO.MONTH.IDMONTH=[LECTA_SALES].DBO.[DATE].IDMONTH " & _
" AND " & TableMois & ".INVDATE=[LECTA_SALES].DBO.[DATE].INVDATE)" & _
" AND " & TableMois & ".COMPCOD = [LECTA_SALES].DBO.COMPANY.COMPCOD" & _
ValeurMois & ValeurAnnee & ValeurCompany & " )" & _
" GROUP BY [LECTA_SALES].DBO.COUNTRY.CTRYDES"

rs.open sql,conn

' affichage du nom des champs

Response.Write "<div align='center'><table border='1' width='1%' id='table1' style='font-family: Verdana; font-size: 8pt; border-collapse:collapse'>"
Response.Write "<tr>"
for i=1 to rs.fields.count step 1
Response.Write "<td bgcolor='#C0C0C0'><b>"
Response.Write rs.fields(i-1).name
Response.Write "</b></td>"
next
Response.Write "</tr>"

nb_enr=0 'le nb d'enregistrement pour le calcul des moyennes
tons_tot=0 'total des TONS
GROSS_SALES_PRICE_tot=0 'total GROSS_SALES_PRICE
SELLING_COST_tot=0 'total SELLING_COST
DISTR_COST_tot=0 'total DISTR_COST
TURNOVER_BONUS_tot=0 'total TURNOVER_BONUS
EX_WORKS_PRICE_tot=0 'total EX_WORKS_PRICE_tot
EX_WORKS_MARGIN_tot=0 'total EX_WORKS_MARGIN
NET_SALES_PRICE_tot=0 'total NET_SALES_PRICE

'calcul des totaux

rs.MoveFirst
do while not rs.EOF

nb_enr=nb_enr+1

tons_tot=tons_tot+formatnumber(rs.Fields("tons"))

GROSS_SALES_PRICE_tot=GROSS_SALES_PRICE_tot+formatnumber(rs.Fields("GROSS_SALES_PRICE"))

TURNOVER_BONUS_tot=TURNOVER_BONUS_tot+formatnumber(rs.Fields("TURNOVER_BONUS"))

SELLING_COST_tot=SELLING_COST_tot+formatnumber(rs.Fields("SELLING_COST"))

DISTR_COST_tot=DISTR_COST_tot+formatnumber(rs.Fields("DISTR_COST"))

EX_WORKS_PRICE_tot=EX_WORKS_PRICE_tot+formatnumber(rs.Fields("EX_WORKS_PRICE"))

EX_WORKS_MARGIN_tot=EX_WORKS_MARGIN_tot+formatnumber(rs.Fields("EX_WORKS_MARGIN"))

NET_SALES_PRICE_tot=NET_SALES_PRICE_tot+formatnumber(rs.Fields("NET_SALES_PRICE"))

rs.MoveNext
loop

'affichage du premier champs pour comparer avec les valeurs du tableau
rs.MoveFirst
do while not rs.EOF
Response.Write round(rs.Fields(1))
Response.Write " "
rs.MoveNext
loop

if rs.bof= false then
rs.MoveFirst
lg=1
while not rs.eof
lg=lg+1

' affichage des pays avec le lien
Response.Write "<tr>" '1 ere ligne
Response.Write "<td><a href=javascript:popup('\\Form01.asp?Qry=" & replace(rs.fields(0)," ","%20") & "&Qry1=" & replace(request.form("MOIS")," ","%20") & "&Qry2=" & replace(request.form("ANNEE")," ","%20") & "&Qry3=" & replace(session("COMP")," ","%20") & "&Qry4=" & replace(request.form("FORMAT")," ","%20") & "&Qry5=" & replace(request.form("INVDEL")," ","%20") & "')>"
Response.Write rs.fields(0)
Response.Write "</a></td>"'1ere colonne

for i=2 to rs.fields.count-1 step 1
Response.Write "<td><P ALIGN='RIGHT'>" '2 eme colonne
if IsNull(rs.fields("TONS")) then
Tons=0
else
Tons=FormatNumber(rs.fields("TONS"),3)
end if
if IsNull(rs.fields(i-1)) then
Col=0
else
Col=FormatNumber(rs.fields(i-1),3)
end if
if Tons=0 then
Response.Write "0"
else
Response.Write (Round(FormatNumber(Col/Tons,3)))
end if
Response.Write "</P></td>"
next

Response.Write "<td><P ALIGN='RIGHT'>" '3eme colonne
Response.Write Round(Tons)
Response.Write "</P></td>"
Response.Write "</tr>"

rs.movenext

wend

' affichage des totaux à la dernière ligne du tableau

response.write "<tr>"
response.write "<td><b>TOTAUX</td><td><b><P ALIGN='right'>"&GROSS_SALES_PRICE_tot&"</td><td><P ALIGN='right'><b>"&TURNOVER_BONUS_tot&"</td>"
response.write "<td><P ALIGN='right'><b>"&NET_SALES_PRICE_tot&"</td><td><P ALIGN='right'><b>"&SELLING_COST_tot&"</td><td><P ALIGN='right'><b>"&DISTR_COST_tot&"</td><td><P ALIGN='right'><b>"&EX_WORKS_PRICE_tot&"</td>"
response.write "<td><P ALIGN='right'><b>"&EX_WORKS_MARGIN_tot&"</td>"
response.write "<td><P ALIGN='right'><b>"&tons_tot&"</td>"
response.write "</tr></p>"

rs.close

end if

else

response.write "<p><b><font face='Verdana' color='#FF0000' size='2'>Select a month and a year.</font></b></p>"

end if
else
%>

<SCRIPT LANGUAGE='JavaScript'>

</script>

<%
end if

conn.close

%>

</FORM>
</html>

Pourriez-vous m'aider.

Merci d'avance

Karen