number of products / number in stock

[compunerdy]

I would like a conlib I can place that shows how many products we sell and how many of those are in stock.

Like..

1000 products currently offered with 950 of those currently in stock and ready to ship.

[m_plugables]

Create an empty file StockInfo.ascx in the ConLib folder and put the following code in it

Code: Select all

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="StockInfo.ascx.cs" Inherits="ConLib_StockInfo" %>
<asp:Label ID="StockInfo" runat="server" />

Create another empty file StockInfo.ascx.cs in the ConLib folder and put the following code in it

Code: Select all

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CommerceBuilder.Products;
using CommerceBuilder.Utility;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using CommerceBuilder.Common;

public partial class ConLib_StockInfo : System.Web.UI.UserControl
{
    private bool _showLowStock = true;

    public bool ShowLowStock 
    {
        get { return _showLowStock; }
        set { _showLowStock = value; }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        int totalProducts = 0;
        int inStockProducts = 0;

        string selectQuery1 = "SELECT COUNT(ProductId) FROM ac_Products WHERE InventoryModeId = @inventorymode OR InStock > 0 OR (InventoryModeId = @inventorymode1 AND ProductId IN (SELECT ProductId FROM ac_ProductVariants WHERE InStock > 0 AND Available = 'true' ))";
        string selectQuery2 = "SELECT COUNT(ProductId) FROM ac_Products WHERE InventoryModeId = @inventorymode OR InStock > InStockWarningLevel OR (InventoryModeId = @inventorymode1 AND ProductId IN (SELECT ProductId FROM ac_ProductVariants WHERE InStock > InStockWarningLevel AND Available = 'true' ))";
        string selectTotalCountQuery = "SELECT COUNT(ProductId) FROM ac_Products";

        Database database = Token.Instance.Database;
        DbCommand selectCommand = null;
        selectCommand = database.GetSqlStringCommand(selectTotalCountQuery);
        totalProducts = AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));
        selectCommand = null;
        if (ShowLowStock)
        {
            selectCommand = database.GetSqlStringCommand(selectQuery1);
        }
        else
        {
            selectCommand = database.GetSqlStringCommand(selectQuery2);
        }
        database.AddInParameter(selectCommand, "@inventorymode", System.Data.DbType.Int16,Convert.ToInt16(InventoryMode.None));
        database.AddInParameter(selectCommand, "@inventorymode1", System.Data.DbType.Int16, Convert.ToInt16(InventoryMode.Variant));
        inStockProducts = AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));

        string stockInfoMessage = "{0} product{1} currently offered with {2} of {3} are in stock and ready to ship";
        string sTemp = string.Empty;
        string tTemp = string.Empty;
        if(totalProducts > 1)
        {
            sTemp = "s";
            tTemp = "those";
        }
        else
            tTemp = "that";
        StockInfo.Text = String.Format(stockInfoMessage,totalProducts,sTemp,inStockProducts,tTemp);
    }
}

Now you can use the control like any other ConLib control. The control exposes a single property ShowLowStock. Set this property if you want to include the products with low inventory level into the available products list. For example
[[ConLib:StockInfo ShowLowStock="true"]]

[compunerdy]

Thank you very much!!!

My numbers seem a bit off but that might be because I use a lot of items with options and kits.

Edit: After looking closer it looks like it doesnt count items that have inventory tracked variants as multiple items but just as one. I dont know if there would be a way to make it figure these into the totals or not. Depending on how you look at it I guess it works more correct the way it is setup now. Your items listed with 0 inventory in able wont match what the conlib displays exactly but its easy to see why.