<% FUNCTION fixQuotes( theString ) fixQuotes = REPLACE( theString, "'", "''" ) END FUNCTION ' Get the Form Variables ' Open Database Connection Server.MapPath(".") dbPath = "E:/hshome/webcaddy/database/storedbexchange.mdb" Set Con = Server.CreateObject("ADODB.Connection") Con.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath selection = "ALL" projectiondate = Now() + 90 lookbackdays = 180 %> Manage Stock
<%IF session( "iDebugLevel" ) > 1 THEN %> Session Debug Level Set to:<%=session( "iDebugLevel" )%>
Form Count:<%=Request.Form.Count%>
<%for i=1 to Request.Form.Count%> Form Key #<%=i%> named: <%=Request.Form.Key(i)%> = <%=Request.Form.Item(Request.Form.Key(i))%>
<%next%> <%END IF%> <% FormCount = Request.Form.Count IF FormCount > 1 THEN lookbackdays = Request("lookbackdays") projectiondate = Request("projectiondate") selection = Request("selection") FOR i = 5 to (FormCount) newstocklevel = Request.Form.Item(Request.Form.Key(i)) if newstocklevel = "" then newstocklevel = 0 end if sqlString = "UPDATE products SET " &_ "product_stock = " & newstocklevel & " "&_ "WHERE product_id = " & Request.Form.Key(i) IF session( "iDebugLevel" ) > 0 THEN %> sqlString: <%=sqlstring%>
<%END IF Con.Execute sqlString NEXT END IF %>
<% sqlString = "SELECT product_id, product_name, product_stock FROM Products " &_ "ORDER BY product_name ASC " IF session( "iDebugLevel" ) > 1 THEN %> sqlString: <%=sqlstring%>
<%END IF SET RS = Con.Execute( sqlString ) %>
ManageStock -

Lookback days:     Projection Date: >  
Display Filter:    Number of Days:<%=DateDiff("d",now(),projectiondate)%>

<% TotalItems=0 TotalBuild=0 WHILE NOT RS.EOF DailySell = -1 sqlString = "SELECT Sum(order_quantity) AS TotalSold " &_ "FROM ship INNER JOIN orders ON ship.ship_shipID = orders.order_shipID " &_ "WHERE (((ship.ship_shipped) Between Now()- " & lookbackdays & " And Now()) " &_ "AND ((orders.order_ProductID) = " & RS( "product_id") & " ))" IF session( "iDebugLevel" ) > 1 THEN %> sqlString: <%=sqlstring%>
<%END IF SET salesrate = Con.Execute (sqlString) If isnull(RS("product_stock")) then productstock = 0 else productstock = RS("product_stock") end if If isnull(salesrate("TotalSold")) then DailySell = 0 TotalSold = 0 DaysToGo = -1 else TotalSold = salesrate("TotalSold") DailySell = TotalSold/lookbackdays DaysToGo = productstock/DailySell end if ProjectionDays = DateDiff("d",now(),projectiondate) required = DailySell*ProjectionDays buildneed = required - RS("product_stock") if buildneed > 0 then buildState = "NOT OK" else buildstate = "OK" end if IF session( "iDebugLevel" ) > 1 THEN %> productstock: <%=productstock%> DailySell: <%=DailySell%> required: <%=required%>
RS("product_stock"): <%=RS("product_stock")%> salesrate( "TotalSold"): <%=salesrate( "TotalSold")%>
TotalSold: <%=TotalSold%> RecordCount: <%=salesrate.state%>
<%END IF%> <% if ((selection = "OK" AND buildstate = "OK" ) OR (selection = "NOT OK" AND buildstate = "NOT OK" ) OR selection = "ALL" ) then if (totalsold > 0 OR selection = "ALL" ) then TotalItems=TotalItems+1%> <% end if end if RS.MoveNext WEND %>
Product Sold In Stock Days to go Required Build
"> <%=RS( "product_name" )%>   <%=TotalSold%>  type="text" size="5" value="<%=productstock%>"> <%=round(DaysToGo,0)%>  <%if productstock < required then %> <%=round(required,0)%> <%else%> <%=round(required,0)%>   <%end if%> <%if buildneed > 0 then%> <%if round(buildneed,0) = 0 then%> <%TotalBuild = TotalBuild + 1%> 1 <%else%> <%TotalBuild = TotalBuild + round(buildneed,0)%> <%=round(buildneed,0)%> <%end if%> <%else %> OK <%end if%>

Total Items:<%=TotalItems%> Total Build:<%=TotalBuild%>