stand-alone stock levels report for products with and without attributes (classic ASP)

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.
10 years ago
Hello-

Last night I created and implemented a basic ASP page that will give a full report of stock levels of all products...with and without attributes..and with direct links to edit each....works great.

I'll probably extend it to allow for inline editing of the stock values at some point, but this, alone, is a massive time-saver.

It was built with actual physical inventory counting in mind..while in warehouse counting actual physical inventory it's great to have a master list of what the numbers SHOULD be...then you can easily factor shrinkage.

It works with nopcommerce 3.20, but you'll have to run it on a server that supports classic ASP and configure your connectionstring accordingly. With a little more work, the table html could all be response.written, and therefore output as an Excel spreadsheet using:

Response.ContentType = "application/vnd.ms-excel"



<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Current Stock Levels as of <%=now()%></title>
</head>

<body link="#0000FF" vlink="#0000FF" alink="#FF0000">

<table width="600" border="1" id="table55" cellspacing="0" cellpadding="4" bordercolor="#CCCCCC">

<%
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open Application("my_ConnectionString")


SQLStmt = "SELECT [id],[sku],[name],[stockquantity] FROM [Product] where published='True' and deleted='False' order by [name]; "

Set RS = Connection.Execute(SQLStmt)

numrecs=-1
If Not rs.EOF Then
  theData=rs.GetRows()
  numrecs=uBound(theData,2)
Else
  response.write "<p><font color=red face=Tahoma size=2><b><big>Strange.....no products returned.</font></b>"
  response.end
End If

For i=0 to numrecs

if theData(3,i)=10000 then thestockvalue="----" else thestockvalue=theData(3,i)

%>
<tr>
<th bgcolor="#dadada" align=left colspan=3><font face="Tahoma" size="3"><a href="https://www.mywebsite.com/Admin/Product/Edit/<%=theData(0,i)%>" target="_self"><%=theData(2,i)%></a></font></th>
</tr>
<tr>
<td><b><font face="Tahoma" size="2">SKU</font></b></td>
<td><b><font face="Tahoma" size="2">Product Name</font></b></td>
<td align=center><b><font face="Tahoma" size="2"># On Hand</font></b></td>
</tr>
<tr>
<td><font face="Tahoma" size="2"><%=theData(1,i)%></font></td>
<td><font face="Tahoma" size="2"><%=theData(2,i)%></font></td>
<td align=center><font face="Tahoma" size="2"><%=thestockvalue%></font></td>
</tr>
<tr>
<td colspan=3>&nbsp;</td>
</tr>
<%


SQLStmt2 = "SELECT [sku],[ManufacturerPartNumber],[stockquantity] FROM [ProductVariantAttributeCombination] where [productid]=" & theData(0,i) & "; "
Set RS2 = Connection.Execute(SQLStmt2)

numrecs2=-1
If rs2.EOF Then

' skip it

Else
  theData2=rs2.GetRows()
  numrecs2=uBound(theData2,2)
%>
<tr>
<td bgcolor="#f0f0f0" align=center colspan=3><b><font face="Tahoma" size="1">VARIANTS</font></b></td>
</tr>
<tr>
<td><b><font face="Tahoma" size="2">SKU</font></b></td>
<td><b><font face="Tahoma" size="2">Mfg. Part #</font></b></td>
<td align=center><b><font face="Tahoma" size="2"># On Hand</font></b></td>
</tr>
<%
For x=0 to numrecs2
%>
<tr>
<td><font face="Tahoma" size="2"><%=theData2(0,x)%></font></td>
<td><font face="Tahoma" size="2"><%=theData2(1,x)%></font></td>
<td align=center><font face="Tahoma" size="2"><%=theData2(2,x)%></font></td>
</tr>

<%
  Next
%>
<tr>
<td colspan=3>&nbsp;</td>
</tr>
<%
End If

Next
    Set rs=Nothing
    Set rs2=Nothing
    connection.Close
    Set Connection=Nothing
%>

</table>
</body>
</html>
An upvote on a helpful post means "thank you" in every language. I believe in this community. For every question I ask, I try to answer at least five others.
2
7 years ago
I've improved the script to include inline editing of the stock quantity and the re-order levels, as well as making the displayed quantity of any products that are at or below stock appear in RED.

Here's a screenshot of the output.

It makes it much easier to receive new merchandise into stock as it is delivered, as well as keeping an eye on overall inventory levels.
You just have to change the items in bold, and create a connection string in your global.asa in a separate site that can run ASP.

Example connectionstring in global.asa:
  Application("my_ConnectionString") = "Provider=sqloledb;" & _
           "Data Source=123.45.67.890,1433;" & _
           "Initial Catalog=YOUR_DATABASE_NAME;" & _
           "User ID=YOUR_DB_USERNAME;" & _
           "Password=YOUR_DB_PASSWORD"


<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Current Stock Levels as of <%=now()%></title>
</head>
<body link="#0000FF" vlink="#0000FF" alink="#FF0000">
<h2><font face="Tahoma">Current Stock Levels as of <%=now()%></font></h2>

<table width="700" border="1" cellspacing="0" cellpadding="4" bordercolor="#CCCCCC">
<%
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open Application("my_ConnectionString")


SQLStmt = "SELECT [id],[sku],[name],[stockquantity],[NotifyAdminForQuantityBelow] FROM [Product] where  [Published] = 'True' AND [Deleted] = 'False' order by [SKU] ASC"
Set RS = Connection.Execute(SQLStmt)

numrecs=-1
If Not rs.EOF Then
  theData=rs.GetRows()
  numrecs=uBound(theData,2)
Else
  ' response.write "<p><font color=red face=Tahoma size=2><b><big><i>Strange</i>... no products returned.</font></b>"
  ' response.end
End If

  For i=0 to numrecs
if theData(3,i)=10000 then thestockvalue="----" else thestockvalue=theData(3,i)
%>
<tr>
<th bgcolor="#dadada" align=left colspan=4><font face="Tahoma" size="3"><a name="<%=theData(2,i)%>" href="https://www.mydomain.com/Admin/Product/Edit/<%=theData(0,i)%>" target="_self"><%=theData(2,i)%></a></font></th>
</tr>
<tr>
<td><b><font face="Tahoma" size="2">SKU</font></b></td>
<td><b><font face="Tahoma" size="2">Product Name</font></b></td>
<td align=center><b><font face="Tahoma" size="2">Re-order Level</font></b></td>
<td align=center><b><font face="Tahoma" size="2"># On Hand</font></b></td>
</tr>

<tr>
<td><font face="Tahoma" size="2"><%=theData(1,i)%></font></td>
<td align=left><font face="Tahoma" size="2"><%=theData(2,i)%></font></td>
<td align=center>
<% if thestockvalue="----" then %>
<font face="Tahoma" size="2">----</font>
<% else %>

<form method="POST" action="update_product_reorder_levels.asp" style="line-height: 50%; margin-bottom: 0;">
  <input type="text" name="NotifyAdminForQuantityBelow" size="4" value="<%=theData(4,i)%>" style="font-weight: bold;">
  <input type="submit" value="Update" name="B1" style="font-size: 8pt; font-weight: bold">
  <input type="hidden" name="Id" value="<%=theData(0,i)%>">
  <input type="hidden" name="bookmark" value="<%=theData(2,i)%>">
  <input type="hidden" name="returnpage" value="stock_levels.asp">
</form>

<% end if %>
</td>
<td align=center>
<% if thestockvalue="----" then %>
<font face="Tahoma" size="2"><%=thestockvalue%></font>
<% else 

  if theData(3,i) <= theData(4,i) then
     stylecolor=" color: red;"
  else
    stylecolor=" color: black;"
  end if
%>
<form method="POST" action="update_product_stock_quantities.asp" style="line-height: 50%; margin-bottom: 0;">
  <input type="text" name="stockquantity" size="4" value="<%=thestockvalue%>" style="font-weight: bold;<%=stylecolor%>">
  <input type="submit" value="Update" name="B1" style="font-size: 8pt; font-weight: bold">
  <input type="hidden" name="Id" value="<%=theData(0,i)%>">
  <input type="hidden" name="bookmark" value="<%=theData(2,i)%>">
  <input type="hidden" name="returnpage" value="stock_levels.asp">
</form>
<% end if %>

</td>
</tr>
<tr>
<td colspan=4>&nbsp;</td>
</tr>
<%
SQLStmt2 = "SELECT [sku],[ManufacturerPartNumber],[stockquantity],[NotifyAdminForQuantityBelow],[Id] FROM [ProductVariantAttributeCombination] where [productid]=" & theData(0,i) & " order by [ManufacturerPartNumber]; "
Set RS2 = Connection.Execute(SQLStmt2)

numrecs2=-1
If rs2.EOF Then

' skip it

Else
  theData2=rs2.GetRows()
  numrecs2=uBound(theData2,2)
%>
<tr>
<td bgcolor="#f0f0f0" align=center colspan=4><b><font face="Tahoma" size="1">VARIANTS</font></b></td>
</tr>
<tr>
<td><b><font face="Tahoma" size="2">SKU</font></b></td>
<td><b><font face="Tahoma" size="2">Mfg. Part #</font></b></td>
<td align=center><b><font face="Tahoma" size="2">Re-order Level</font></b></td>
<td align=center><b><font face="Tahoma" size="2"># On Hand</font></b></td>
</tr>
<%
    For x=0 to numrecs2

  if theData2(2,x) <= theData2(3,x) then
    stylecolor=" color: red;"
  else
    stylecolor=" color: black;"
  end if
%>

<tr>
<td><font face="Tahoma" size="2"><%=theData2(0,x)%></font></td>
<td><font face="Tahoma" size="2"><%=theData2(1,x)%></font></td>
<td align=center>

<form method="POST" action="update_attributecombination_reorder_levels.asp" style="line-height: 50%; margin-bottom: 0;">
  <input type="text" name="NotifyAdminForQuantityBelow" size="4" value="<%=theData2(3,x)%>" style="font-weight: bold;">
  <input type="submit" value="Update" name="B1" style="font-size: 8pt; font-weight: bold">
  <input type="hidden" name="Id" value="<%=theData2(4,x)%>">
  <input type="hidden" name="bookmark" value="<%=theData(2,i)%>">
  <input type="hidden" name="returnpage" value="stock_levels.asp">
</form>

</td>
<td align=center>
  <form method="POST" action="update_attributecombination_stock_quantities.asp" style="line-height: 50%; margin-bottom: 0;">
  <input type="text" name="stockquantity" size="4" value="<%=theData2(2,x)%>" style="font-weight: bold;<%=stylecolor%>">
  <input type="submit" value="Update" name="B2" style="font-size: 8pt; font-weight: bold">
  <input type="hidden" name="Id" value="<%=theData2(4,x)%>">
  <input type="hidden" name="bookmark" value="<%=theData(2,i)%>">
  <input type="hidden" name="returnpage" value="stock_levels.asp">
  </form>
</td>
</tr>

<%
    Next  ' x loop
%>
<tr>
<td colspan=4>&nbsp;</td>
</tr>
<%
End If

  Next  ' i loop


    Set rs=Nothing
    Set rs2=Nothing
    connection.Close
    Set Connection=Nothing
%>
</table>
&nbsp;
</body>
</html>




It now includes these two scripts for updating the number of units on hand (stock quantity) and re-order level (NotifyAdminForQuantityBelow):

update_attributecombination_reorder_levels.asp

<%
Id=Request.form("Id")
thereorderlevel=request.form("NotifyAdminForQuantityBelow")
bookmark=request.form("bookmark")
returnpage=request.form("returnpage")

Set rs = Server.CreateObject("ADODB.Recordset")
q = "UPDATE [ProductVariantAttributeCombination] SET [NotifyAdminForQuantityBelow] = " & thereorderlevel
q = q &" WHERE [Id] = " & Id
'response.write q & "<br>"
'response.end
rs.Open q, Application("my_ConnectionString")

response.redirect returnpage & "#" & bookmark
%>



update_attributecombination_stock_quantities.asp
<%
ID=Request.form("Id")
thestockvalue=request.form("stockquantity")
bookmark=request.form("bookmark")
returnpage=request.form("returnpage")

Set rs = Server.CreateObject("ADODB.Recordset")
q = "UPDATE [ProductVariantAttributeCombination] SET [StockQuantity] = " & thestockvalue
q = q &" WHERE [ID] = " & ID
'response.write q & "<br>"
'response.end
rs.Open q, Application("my_ConnectionString")


response.redirect returnpage & "#" & bookmark
%>
An upvote on a helpful post means "thank you" in every language. I believe in this community. For every question I ask, I try to answer at least five others.
0
7 years ago
I forgot to say that the main script must be named: stock_levels.asp in order for the update scripts to return you to the right place...
An upvote on a helpful post means "thank you" in every language. I believe in this community. For every question I ask, I try to answer at least five others.
0
7 years ago
And for anybody wondering....

I have two sites configured:
nopSite
ASPsite

The ASPsite includes ONLY the 3 ASP files and the global.asa:

global.asa
stock_levels.asp
update_attributecombination_stock_quantities.asp
update_attributecombination_reorder_levels.asp

I use Windows authentication to prevent unauthorized access to the ASPsite site, and I access the stock_levels.asp from a link which I added into our nopcommerce admin Products menu, like:

\Administration\sitemap.config

<siteMapNode title="Adjust Stock Levels (offsite)" url="http://123.45.67.890/stock_levels.asp" />
An upvote on a helpful post means "thank you" in every language. I believe in this community. For every question I ask, I try to answer at least five others.
0
7 years ago
This is what I have been looking for. Will this work with 3.8 and I am using ASPX. ASP is not running on server.

I need to integrate this with out scanner so I can scan products and update inventory.

Thanks in advance.

Tracy
0
7 years ago
It's written in VBScript/HTML, so there's probably some tool available to convert it to ASPX forms and code-behinds.

Also, you can enable ASP on your ASPX site and from what I understand, if you are using ASPX Forms Webpages it is possible to run VBscript within ASPX applications by using an embedded code block.

Otherwise, to use it you'd just have to set up a second website running ASP in order to run the 3 pages(plus global.asa).
An upvote on a helpful post means "thank you" in every language. I believe in this community. For every question I ask, I try to answer at least five others.
0
7 years ago
Oh, and yes, it was written to work with version 3.40, but I'm almost positive that the fields in the database are all still named the same in version 3.80.
An upvote on a helpful post means "thank you" in every language. I believe in this community. For every question I ask, I try to answer at least five others.
0
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.