Taste Test Report

<% Response.Expires = 0 Set conn = server.CreateObject("Adodb.connection") set rs = server.CreateObject("adodb.recordset") set dict = server.CreateObject("Scripting.dictionary") Conn.Open connectionstring Dim MenuID MenuID = "3.14.3.3" CheckAccessToThisPage(MenuID) 'VERIFY IF FOR EXPORT TO EXCEL if request("ExportToExcel") = "y" then blnExportToExcel = true tblBorder = 1 end if %> Taste Test Report <% if not blnExportToExcel then Response.Write MenuPath(MenuID) %> bgcolor=brown<%end if%>>

color=white<%end if%>> Taste Tests Report

<% dim categoryIDArray(20) 'get search criteria location = trim(request("warehousename")) category = trim(request("category")) entry = trim(request("entry")) dateFrom = request("From_MM") &"/"& request("From_DD") & "/" & request("From_CCYY") dateTo = request("To_MM") &"/"& request("To_DD") & "/" & request("To_CCYY") If trim(replace(request("batchfields"),", ","")) <> "" then BatchNumber = trim(replace(request("batchfields"),", ","")) else BatchNumber = trim(request("batchString")) end if QS = "location=" & location & "&category=" & category & "&entry="& entry QS = QS & "&datefrom=" & datefrom & "&dateTo=" & dateTo & "&BatchString=" & batchNumber 'if there is no date posted, get from querystring if not isdate(dateFrom) then dateFrom = trim(request("dateFrom")) if not isdate(dateTo) then dateTo = trim(request("DateTo")) if not isdate(dateFrom) then dateFrom = date() if not isdate(dateTo) then dateTo = date() if not blnExportToExcel then %>

Product: <% 'make category list sql = "select distinct Category from QA_categories" rs.Open sql, conn %>

Plant: <% 'make warehouse list sql = "select distinct warehousename from warehouse" rs.Open sql, conn %>

Entry date from: <% call get_month("From_MM",month(dateFrom)) call get_day("From_DD",day(dateFrom)) call get_year("From_CCYY",year(dateFrom)) %> To: <% call get_month("To_MM",month(dateTo)) call get_day("To_DD",day(dateTo)) call get_year("To_CCYY",year(dateTo)) %>

Search for entry: Leave blank for all

Batch code:

<%=replace(BatchFields,"#name#","BatchFields")%>

Or:

<% end if 'end of not blnExportToExcel if not blnExportToExcel then %>

<% end if %> <% 'IF EXPORT TO EXCEL, DON'T WRITE ASP HEADER, MENU PATH... if blnExportToExcel then Response.AddHeader "Content-Disposition","attachment; filename=TasteReport_" & year(now) & month(now) & day(now) & ".xls" Response.ContentType = "application/vnd.ms-excel" end if %> <% 'make the header for the report sql = "select distinct categoryName,disabled,categoryOrder,datatype from tasteControl where QACategory='" & category & "' order by categoryOrder" set rs = conn.Execute(sql) countID = 0 do while not rs.EOF %> <% 'make the list of categoryIDs categoryIDArray(countID) = trim(rs("CategoryOrder")) countID = countID + 1 rs.MoveNext loop rs.close 'make dictionary of options values sql = "Select distinct fieldID,optionField from tasteControl where QACategory='" & category & "'" rs.Open sql, conn do while not rs.EOF dict.add trim(rs("fieldID")),trim(rs("optionField")) rs.MoveNext loop 'do not show edit column on excel sheet if not blnExportToExcel then %> <% end if rs.close sql = "select s.*,c.datatype,c.fieldID from tasteSample s, tasteControl c where " 'make sql query for the report if entry = "" and batchNumber = "" then if location <> "" and category <> "" then sql = sql & " s.warehousename ='" & location & "' and s.QACategory ='" & category & "' and" elseif location <> "" and category = "" then sql = sql & " s.warehousename ='" & location & "' and" elseif location = "" and category <> "" then sql = sql & " s.QACategory ='" & category & "' and" else sql = sql & " s.SampleNumber is not null and" end if if isdate(dateFrom) and isdate(dateTo) then sql = sql & " s.timestamp between '" & dateFrom & " 00:00' and '" & dateTo & " 23:59' and" end if end if if entry <> "" then sql = sql & " SampleNumber in ( select SampleNumber from tasteSample where value like '%" & entry & "%'" sql = sql & ") and" end if if BatchNumber <> "" then sql = sql & " BatchNumber = '" & batchNumber & "' and" end if sql = sql &" s.QACategory = c.QAcategory and s.CategoryID = c.CategoryOrder and (s.value = c.value or c.value ='')" sql = sql & " order by sampleNumber,categoryID" rs.Open sql, conn 'Response.Write sql &"

" 'make report do while not rs.EOF %>

<% i = 0 SampleNumber = trim(rs("SampleNumber")) do while SampleNumber = trim(rs("SampleNumber")) 'check whether the category field has been disabled 'if it is disabled then show nothing if cint(categoryIDArray(i)) = cint(rs("categoryID")) then %> <% rs.MoveNext else %> <% end if i = i + 1 'if iterate more than the number of categories, exit loop if rs.EOF or i > countID then exit do 'to handle empty value on the last category so that it will change row if not rs.eof and cint(categoryIDArray(i)) > cint(rs("categoryID")) then rs.movenext end if loop if not blnExportToExcel then %> <% end if %> <% loop rs.Close %>

Item Qwik ID	Plant/WH loc	Batch #	Date Entered	Initials	width=150 <%else%> width=50 <%end if%> align=center style="BORDER-RIGHT: black thin solid; BORDER-TOP: black thin solid; BORDER-LEFT: black thin solid; BORDER-BOTTOM: black thin solid"> <%=rs("categoryName")%> <% if cbool(rs("disabled")) then Response.Write " disabled" end if %>	Edit
<%=rs("ItemQwikID")%>	<%=rs("warehousename")%>	<%=rs("BatchNumber")%>	<%=formatDateTime(rs("timestamp"),vbSHortDate)%>	<%=rs("initials")%>	<%'cint(categoryIDArray(i)) &" "& cint(rs("categoryID"))%> <% if trim(rs("Datatype")) = "string" then Response.Write "" & trim(rs("value")) else Response.Write ""& dict.Item(trim(rs("fieldID"))) end if %>

<% if SampleNumber = "" then Response.Write "No submission" end if 'DON'T DISPLAY BUTTON ON EXCEL SHEET if not blnExportToExcel then %>

?ExportToExcel=y&<%=QS%>';">

<% end if %> <% if rs.State = 1 then rs.Close set conn = nothing %>