Raport w aspie ( proszę o pomoc

0

Pomoże ktoś w ogarnięciu tematu ? ponoć sprawa jest prosta , dopiero się uczę i jeszcze nie ogarniam. Wkleiłem istniejący raport który muszę przerobić.

zbuduj raport który bedzie wyświetlał zlecenia o typie 331
w statusie otwarte z możliwą filtracją po dacie (domyślnie ma się otwierać bez filtrowania)

do wyświetlenia będą pola freetextfield_14, freetextfield_15, itemcode (do tego jego robis z tabeli items- pole description), itemcount i itemcountactual.

[code]

<%
Option Explicit
Response.Expires = -1
%>

<%
Dim seclevel: seclevel = env.UserProp("JobLevel")
If seclevel < 10 Then Response.Redirect "BacoNoRights.asp"

Dim log: Set log = env.CreateAppLogEntry(Session, Request)
Dim bi: Set bi = CreateObject("BacoInput.Report")
bi.Init Server, Response, env

Dim ClientCode: ClientCode = Request("ClientCode")
Dim Client_Id: Client_Id = Request("ClientBrowser")
Dim sColumnLabels()
Dim vData
Dim sQuery, sWhere
Dim i
Dim ProjectNr : ProjectNr = Request("ProjectNr")
Dim tools: Set tools = env.Tools
Dim URL
Dim checkedOrNot : checkedOrNot = ""
Dim PODueDateChange : PODueDateChange = env.FormatDateTime(Request("PODueDateChange"),vbShortDate)
Dim PODetailsData
Dim dteReportDateFrom
Dim dteReportDateTo
'reDim sColumnLabels(13)
reDim sColumnLabels(13)

if Request("ReportDateFrom") <> "" then
dteReportDateFrom= env.FormatDateTime(Request("ReportDateFrom"),vbShortDate)
else
' ustawienie domyślne daty
dteReportDateFrom= DateAdd("m", 0, DateAdd("d", -DatePart("d", Now)+1, Now))

end if

if Request("ReportDateTo") <> "" then
dteReportDateTo= env.FormatDateTime(Request("ReportDateTo"),vbShortDate)
else
' ustawienie domyślne daty
dteReportDateTo= DateAdd("d", +8, Now)
end if

sColumnLabels(1) = "Nr zlecenia"
sColumnLabels(2) = "Typ zadania"

sColumnLabels(3) = "Kod i nazwa klienta"
sColumnLabels(4) = "Nr materiału"
sColumnLabels(5) = "Ilość"
sColumnLabels(6) = "Wykonano"
sColumnLabels(7) = "Do wykonania"
sColumnLabels(8) = "Wartość"
sColumnLabels(9) = "Cena"
sColumnLabels(10) = "Ilość wsadów"
sColumnLabels(11) = "Data Odbioru"
sColumnLabels(12) = "Uzgodnienia"
sColumnLabels(13) = "Zmiana daty"

%>

<html> <head> <link rel="stylesheet" type="text/css" href="&lt;%=env.GetStyleSheet()%">"> <%WriteButtonMouseOver%> </head> <body>

<%
URL = "CSFAMDaysRequestReport_WBUDOWIE.asp?ProjectNr=" & Request("Project")
' response.write URL
BHAddButton term.String(364, "Printable"), "btnPrint_hdr.gif","window.open'CSFAMDaysRequestReport_WBUDOWIE.asp?ProjectNr=" & Request("Project") &" ', '_new'", "P", hboScript
WriteTitle hpProject, htReport, Empty, "Zestawienie zleceń i rezerwacji do produkcji (dniami)" , Empty
%>

<form id="Baco" action="CSFAMDaysRequestReport_WBUDOWIE.asp">
<% Response.Write GetButton("Odśwież", "O", "", "btnRefresh", "btnRefresh", "", "subRefresh") %> <script language="VBScript"> Sub subRefresh() baco.Submit End Sub </script>
</td>
Data od: <% bi.WriteDateField "ReportDateFrom", dteReportDateFrom %> Data do: <% bi.WriteDateField "ReportDateTo", dteReportDateTo %>
Zakład:
 <td class="Field">
 <%
   Dim Project_Des
   Dim Project_Id
   'Response.Write ProjectNr

   If len(ProjectNr) > 0 Then
          Project_id = ProjectNr
   End If

      If Project_Id = "" then
          Project_Id = Request("Project")
      End If

      If IsEmpty(Project_id) then
         Project_id = Request("Project")
      End If

      If Project_id <> "" Then Project_Des = conn.Query("Select Description From PRProject WITH (NOLOCK) WHERE ProjectNr=?",Project_id)
      bi.WriteBrowser "Project", Project_id, Project_Des, "Projects", _
                      "p.ProjectNr,p.Description", 0, , _
                      ,"p.YesNoField1 = 1 AND p.ProjectNr IN (SELECT projectnr FROM PRMember WITH(NOLOCK) WHERE res_id = " & env.HumresID & ")",,,,,,20
   %>
 </td>
   <td width="10%"><b> Zmiana daty ZP:
 </td>

   <td class="Field">
 <%
    bi.WriteDateField "PODueDateChange",PODueDateChange
 %>
 </td>
</tr> </tr> </table> </table>

<% Dim CurrentDate %>

    <tr class= "Header">
        <%
           For i = 1 to Ubound(sColumnLabels)
               Response.Write "<th>" & sColumnLabels(i) & "</th>" & vbNewLine
           Next
        %>
    </tr>

    <%
      Dim SumaIlosc, SumaWykonano, SumaDoWykonania, SumaWartosc 
      Dim SumaWsad
      sQuery = " SELECT (CASE Type WHEN 202 THEN [FreeTextField_15] " & _
	   "	      	       WHEN 201 THEN cast( HID as varchar(60)) " & _
               "	     END) AS 'Nr zlecenia', " & _
               "        (CASE Type WHEN 201 THEN 'R' " & _
	   "	      	       WHEN 202 THEN 'ZP' " & _
               "	     END) AS Typ, HID," & _
               "	    cmp_code + ' - ' + cmp_name AS 'Klient', " & _
               "        FreeTextField_01 AS 'Nr materiału', " & _
               "        (CASE Type WHEN 201 THEN ISNULL(ItemCount_2,0)  " & _
	   "	      	       WHEN 202 THEN ISNULL(ItemCount,0)  " & _
               "	    END) AS 'Ilość'," & _
               " 	    (SELECT ISNULL(SUM(ISNULL(FreeIntField_04,0)),0) FROM Absences A WITH(NOLOCK) WHERE A.FreeGuidField_02 = Absences.ID AND Type=203) AS 'Wykonano', " & _
               "        (CASE Type WHEN 201 THEN ISNULL(ItemCount,0) - (SELECT ISNULL(SUM(ISNULL(B.itemcount,0)),0) FROM ABSENCES B WITH(NOLOCK) WHERE B.csfamrelatedreservationguid=ABSENCES.ID AND B.TYPE=202) " & _
	   "	      	       WHEN 202 THEN ISNULL(ItemCount,0) - (SELECT ISNULL(SUM(ISNULL(FreeIntField_04,0)),0) FROM Absences A WITH(NOLOCK) WHERE A.FreeGuidField_02 = Absences.ID AND Type=203) " & _
               "        END) AS 'Do wykonania', " & _
               "   	    ISNULL(Amount,0) * ISNULL(ItemCount,0) AS 'Wartość', " & _
               "	    ISNULL(Amount,0) AS 'Cena', " & _
               "        StartDate,Description,EndDate, Absences.ID,ISNULL(Absences.FreeNumberField_02,0), " & _
               "        absences.ID, absences.customerid " & _
               " FROM Absences WITH(NOLOCK) " & _
               " LEFT JOIN cicmpy WITH(NOLOCK) ON cicmpy.cmp_wwn = Absences.CustomerID " & _
               " INNER JOIN PrMember WITH(NOLOCK) ON PRMember.res_id = Absences.EmpID AND PRMember.ProjectNr = Absences.ProjectNumber "

      sWhere = " WHERE (([Type] = 202 AND Absences.Status <> 4) OR ([Type] = 201 AND Absences.Status = 1)) " & _
               " AND ProjectNumber in (select ProjectNr from prmember where res_id =" & env.UserProp("HumresID") & ")"


      If Project_Id <> "" Then
         sWhere = " WHERE (([Type] = 202 AND Absences.Status <> 4) OR ([Type] = 201 AND Absences.Status = 1)) "
         sWhere = sWhere & "AND Absences.ProjectNumber ='" & Project_id & "' "
      End if
      ' oooooooooooooooooooooooooooooooo
      ' 03.V.2011 
      dim sYear
      dim sMonth
      dim sDay
      dim sReportDateFrom
      dim sReportDateTo
      
      sYear = Year(dteReportDateFrom)
      sMonth = Month(dteReportDateFrom)
      if len(sMonth)=1 then sMonth = "0" & sMonth
      sDay = day(dteReportDateFrom)
      if len(sDay)=1 then sDay = "0" & sDay
      sReportDateFrom = sYear & sMonth & sDay
      
      sYear = Year(dteReportDateTo)
      sMonth = Month(dteReportDateTo)
      if len(sMonth)=1 then sMonth = "0" & sMonth
      sDay = day(dteReportDateTo)
      if len(sDay)=1 then sDay = "0" & sDay
      sReportDateTo = sYear & sMonth & sDay
      
      sWhere = sWhere & " AND ((Absences.StartDate >='" & sReportDateFrom & "' AND Absences.StartDate<='" & sReportDateTo & "') or freeboolfield_04=1) "
      ' oooooooooooooooooooooooooooooooo
      sWhere = sWhere & " AND absences.status <> 2 ORDER BY (CASE Type WHEN 201 THEN Absences.EndDate " & _
	            "	      	       WHEN 202 THEN Absences.StartDate " & _
                        "	     END) "

response.write sQuery & " " & sWhere

      vData = conn.Query(sQuery & sWhere,,2)

      If Not IsEmpty(vData) Then
         CurrentDate = vdata(1,11)
         SumaIlosc = 0
         SumaWykonano = 0
         SumaDoWykonania = 0
         SumaWartosc = 0
         SumaWsad = 0
         Response.Write "<tr class=""Dataoutline1""><td >" & CurrentDate & "</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>"
         For i = 1 to Ubound(vData)
             If vdata(i,2) = "ZP" then
               If CurrentDate <> vdata(i,11) Then
                  CurrentDate = vdata(i,11)
                  If SumaIlosc <> 0 or  SumaWykonano <> 0 or   SumaDoWykonania <> 0 Then
                     Response.Write "<tr class = ""Total""><td><B>PODSUMOWANIE </td><td></td><td></td><td></td><td align=""right"">" & FormatNumber(SumaIlosc,,<sub>-1) & "</td><td align=""right"">" & FormatNumber(SumaWykonano</sub><sub>-1) & "</td><td align=""right"">" & FormatNumber(SumaDoWykonania</sub><sub>-1) & "</td><td align=""right"">" & FormatNumber(SumaWartosc</sub><sub>-1) & "</td><td></td><td align=""right"">" &  FormatNumber(SumaWsad</sub>,,-1)  & "</td><td></td><td></td><td></td></tr>"
                  End If
                  Response.Write "<tr><td>&nbsp;</td></tr>"
                  Response.Write "<tr class=""Dataoutline1""><td >" & CurrentDate & "</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>"
                  SumaIlosc = 0
                  SumaWykonano = 0
                  SumaDoWykonania = 0
                  SumaWartosc = 0
                  SumaWsad = 0
               End if
             Else
                 If CurrentDate <> vdata(i,13) Then
                   CurrentDate = vdata(i,13)
                   If SumaIlosc <> 0 or  SumaWykonano <> 0 or  SumaDoWykonania <> 0 Then
                      Response.Write "<tr class = ""Total""><td><B>PODSUMOWANIE </td><td></td><td></td><td></td><td align=""right"">" & FormatNumber(SumaIlosc,,<sub>-1) & "</td><td align=""right"">" & FormatNumber(SumaWykonano</sub><sub>-1) & "</td><td align=""right"">" & FormatNumber(SumaDoWykonania</sub><sub>-1) & "</td><td align=""right"">" & FormatNumber(SumaWartosc</sub><sub>-1) & "</td><td></td><td align=""right"">" &  FormatNumber(SumaWsad</sub>,,-1)  & "</td><td></td><td></td><td></td></tr>"
                   End If
                   Response.Write "<tr><td>&nbsp;</td></tr>"
                   Response.Write "<tr class=""Dataoutline1""><td >" & CurrentDate & "</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>"
                   SumaIlosc = 0
                   SumaWykonano = 0
                   SumaDoWykonania = 0
                   SumaWartosc = 0
                   SumaWsad = 0
                 End if
             End If

             If i mod 2 = 1 Then
                 response.write "<tr class=""DataDark"">"
             Else
                 response.write "<tr class=""DataLight"">"
             End If

             Response.Write "<td>"
             If vdata(i,2)="ZP" Then
                Response.Write "<a href=CSFamzlecenie.asp?Action=1&ID=" & vdata(i,16) & " style='color=black; text-decoration:none;'> "
             Else 
                Response.Write "<a href=CSFAMrezerwacja.asp?Action=1&ID=" & vdata(i,16) & " style='color=black; text-decoration:none;'> "
             End if
             Response.Write vdata(i,1)
             If vdata(i,2)="ZP" Then
                Response.Write "</a>"
             End if
             Response.Write "</td>"

             Response.Write "<td align=""center"">" & vdata(i,2) & "</td>"
             Response.Write "<td><a href=CRMAccountCard.asp?Action=1&ID=" & vdata(i,17) & " style='color=black; text-decoration:none;'>" & vdata(i,4) & "</a></td>"
             Response.Write "<td align=center><B>" & vdata(i,5) & "</B></td>"
             Response.Write "<td align=""Right"">" & vdata(i,6) & "</td>"
             Response.Write "<td align=""Right"">" & vdata(i,7) & "</td>"
             Response.Write "<td align=""Right"">" & vdata(i,8) & "</td>"
             Response.Write "<td align=""Right"">" & FormatNumber(vdata(i,9),,,,-1) & "</td>"
             Response.Write "<td align=""Right"">" & FormatNumber(vdata(i,10),,,,-1) & "</td>"
             Response.Write "<td align=Right>" & FormatNumber(vdata(i,15),,,,-1) & "</td>"
             Response.Write "<td align=""Right"">" & day(vdata(i,13))& "-" & month(vdata(i,13)) & "-" & year(vdata(i,13)) & "</td>"
             Response.Write "<td>" & vdata(i,12) & "</td>"
             Response.Write "<td>"
               if vdata(i,2) = "ZP" then
               PODetailsData = PODueDateChange & "_" & vdata(i, 14)
               'Response.Write PODetailsData
                   if len(PODueDateChange) > 0 Then
                    Response.Write "<input type=""checkbox"" id=""ck_" & PODueDateChange & "_" & vdata(i, 14) & """ " & checkedOrNot &" onclick='GetMessage(""" & PODetailsData & """)'>"
                   End if
               else

                End if
             Response.Write "</td></tr>"

             SumaIlosc = SumaIlosc + vdata(i,6)
             SumaWykonano = SumaWykonano + vdata(i,7)
             SumaDoWykonania = SumaDoWykonania + vdata(i,8)
             SumaWartosc = SumaWartosc + vdata(i,9)
             SumaWsad = SumaWsad + vdata(i,15)
         Next
            Response.Write "<tr class = ""Total""><td><B>PODSUMOWANIE </td><td></td><td></td><td></td><td align=""right"">" & FormatNumber(SumaIlosc,,<sub>-1) & "</td><td align=""right"">" & FormatNumber(SumaWykonano</sub><sub>-1) & "</td><td align=""right"">" & FormatNumber(SumaDoWykonania</sub><sub>-1) & "</td><td align=""right"">" & FormatNumber(SumaWartosc</sub><sub>-1) & "</td><td></td><td align=""right"">" & FormatNumber(SumaWsad</sub>,,-1) & "</td><td></td><td></td><td></td></tr>"
      End If
    %>
</table> <script language="vbscript">
    <%Response.Write env.GetDateFormatter%>

Dim xmlHttpObject

Sub GetMessage(ValueID)
	GetXmlDocEx xmlHttpObject, "CSFAMAjaxPODate.asp?ZPDATA=" & ValueID,"ProcessXmlHttp","POST",TRUE,"","",""
End Sub

Sub GetXmlDocEx(XmlhttpObject, Url, CallBackFunction, HttpAction, AsyncFlag,RequestHeaderKey,RequestHeaderVal, ContentBody)
	If Len(Trim(Url)) = 0 then Exit Sub

	On Error Resume Next
	' Instantiate the XMLHTTP object
	Set XmlHttpObject = CreateObject("Microsoft.XmlHttp")

	If Not IsEmpty(XmlHttpObject) then
		' Sets the CallBack function when the asynchronous flag is true
		If Not IsEmpty(CallBackFunction) Then
			XmlHttpObject.OnReadyStateChange = getRef(CallBackFunction)
		End if

		' Set the HTTP method, url and asynch flag
		XmlHttpObject.Open HttpAction,url,AsyncFlag

		' Pass in Request header and Request header value if necessary
		If IsArray(RequestHeaderKey) And IsArray(RequestHeaderVal) Then
			For i = 0 To UBound(RequestHeaderKey)
				XmlHttpObject.SetRequestHeader RequestHeaderKey(i),RequestHeaderVal(i)
			Next
		End if

		' Send it over now
		XmlHttpObject.Send ContentBody
	End If
End Sub

Sub ProcessXmlHttp
	If xmlHttpObject.Readystate = 4 then
		If xmlHttpObject.Status = 200 then
                    'response.write "#"
                    End if
	End if
End sub
</script> </form> </body> </html>

[/code]

3

Przez Twoje spaghetti zachciało mi się jeść przed porą obiadową :/

1 użytkowników online, w tym zalogowanych: 0, gości: 1