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="<%=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
%>
<%
Response.Write GetButton("Odśwież", "O", "", "btnRefresh", "btnRefresh", "", "subRefresh")
%>
<script language="VBScript">
Sub subRefresh()
baco.Submit
End Sub
</script>
|
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> </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> </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]