using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;

public partial class DatabaseClass
{
    #region Addresses
    public int Addresses_Add(int CustomerID, bool IsBilling, string FirstName, string LastName, string Address, string City, string State, string Country, string ZipCode, string Phone, string EmailAddress)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Addresses (CustomerID, IsBilling, FirstName, LastName, Address, City, State, Country, ZipCode, Phone, EmailAddress) VALUES (@CustomerID, @IsBilling, @FirstName, @LastName, @Address, @City, @State, @Country, @ZipCode, @Phone, @EmailAddress); SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        cmd.Parameters.AddWithValue("@IsBilling", IsBilling);
        cmd.Parameters.AddWithValue("@FirstName", FirstName);
        cmd.Parameters.AddWithValue("@LastName", LastName);
        cmd.Parameters.AddWithValue("@Address", Address);
        cmd.Parameters.AddWithValue("@City", City);
        cmd.Parameters.AddWithValue("@State", State);
        cmd.Parameters.AddWithValue("@Country", Country);
        cmd.Parameters.AddWithValue("@ZipCode", ZipCode);
        cmd.Parameters.AddWithValue("@Phone", Phone);
        cmd.Parameters.AddWithValue("@EmailAddress", EmailAddress);
        int AddressID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return AddressID;
    }

    public void Addresses_Update(int AddressID, int CustomerID, bool IsBilling, string FirstName, string LastName, string Address, string City, string State, string Country, string ZipCode, string Phone, string EmailAddress)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Addresses SET CustomerID = @CustomerID, IsBilling = @IsBilling, FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, State = @State, Country = @Country, ZipCode = @ZipCode, Phone = @Phone, EmailAddress = @EmailAddress WHERE AddressID=@AddressID", Connection);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        cmd.Parameters.AddWithValue("@IsBilling", IsBilling);
        cmd.Parameters.AddWithValue("@FirstName", FirstName);
        cmd.Parameters.AddWithValue("@LastName", LastName);
        cmd.Parameters.AddWithValue("@Address", Address);
        cmd.Parameters.AddWithValue("@City", City);
        cmd.Parameters.AddWithValue("@State", State);
        cmd.Parameters.AddWithValue("@Country", Country);
        cmd.Parameters.AddWithValue("@ZipCode", ZipCode);
        cmd.Parameters.AddWithValue("@Phone", Phone);
        cmd.Parameters.AddWithValue("@EmailAddress", EmailAddress);
        cmd.Parameters.AddWithValue("@AddressID", AddressID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Addresses_Delete(int AddressID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM Addresses WHERE AddressID=@AddressID", Connection);
        cmd.Parameters.AddWithValue("@AddressID", AddressID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataTable Addresses_GetByCustomerID(int CustomerID, bool IsBilling)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Addresses WHERE CustomerID=@CustomerID AND IsBilling=@IsBilling", Connection);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        cmd.Parameters.AddWithValue("@IsBilling", IsBilling);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataRow Addresses_GetByAddressID(int AddressID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Addresses WHERE AddressID=@AddressID", Connection);
        cmd.Parameters.AddWithValue("@AddressID", AddressID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }
    #endregion

    #region Categories
    public DataRow Categories_Get(int CategoryID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE CategoryID=@CategoryID", Connection);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataTable Categories_GetAll()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Categories ORDER BY CanonicalName", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public int Categories_Add(int ParentCategoryID, string CategoryName, bool UseCategoryLanding, string CanonicalName)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Categories (ParentCategoryID,CategoryName,UseCategoryLanding,CanonicalName) VALUES (@ParentCategoryID,@CategoryName,@UseCategoryLanding,@CanonicalName) SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@ParentCategoryID", ParentCategoryID);
        cmd.Parameters.AddWithValue("@CategoryName", CategoryName);
        cmd.Parameters.AddWithValue("@UseCategoryLanding", UseCategoryLanding);
        cmd.Parameters.AddWithValue("@CanonicalName", CanonicalName);
        int CategoryID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return CategoryID;
    }

    public void Categories_Update(int CategoryID, int ParentCategoryID, string CategoryName, bool UseCategoryLanding, string CanonicalName)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Categories SET ParentCategoryID=@ParentCategoryID, CategoryName=@CategoryName, UseCategoryLanding=@UseCategoryLanding, CanonicalName=@CanonicalName WHERE CategoryID=@CategoryID", Connection);
        cmd.Parameters.AddWithValue("@ParentCategoryID", ParentCategoryID);
        cmd.Parameters.AddWithValue("@CategoryName", CategoryName);
        cmd.Parameters.AddWithValue("@UseCategoryLanding", UseCategoryLanding);
        cmd.Parameters.AddWithValue("@CanonicalName", CanonicalName);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Categories_UpdateSortOrder(int CategoryID, int SortOrder)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Categories SET SortOrder=@SortOrder WHERE CategoryID=@CategoryID", Connection);
        cmd.Parameters.AddWithValue("@SortOrder", SortOrder);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataTable Categories_GetTwoLevelsDeep()
    {
        SqlCommand cmd = new SqlCommand("SELECT c1.*,c2.CategoryName as ParentName,c2.ParentCategoryId as ParentParentId,c3.CategoryName as ParentParentName FROM Categories c1 LEFT OUTER JOIN Categories c2 ON c1.ParentCategoryID = c2.CategoryID LEFT OUTER JOIN Categories c3 ON c2.ParentCategoryId = c3.CategoryId ORDER BY c1.SortOrder", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Categories_GetBySelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Categories c INNER JOIN CategorySelectorMap csm ON c.CategoryID=csm.CategoryID WHERE csm.SelectorID=@SelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Categories_Search(string srchCanonicalName)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE CanonicalName LIKE @CanonicalName ORDER BY CanonicalName", Connection);
        cmd.Parameters.AddWithValue("@CanonicalName", srchCanonicalName);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Categories_GetChildren(int CategoryID)
    {
        SqlCommand cmd = new SqlCommand("SELECT c.*, (SELECT COUNT(*) FROM Categories c2 WHERE c2.ParentCategoryID=c.CategoryID) as CategoryCount, (SELECT COUNT(DISTINCT SelectorID) FROM CategorySelectorMap WHERE CategoryID=c.CategoryID) as ProductCount FROM Categories c WHERE c.ParentCategoryID=@ParentCategoryID ORDER BY SortOrder", Connection);
        cmd.Parameters.AddWithValue("@ParentCategoryID", CategoryID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Categories_GetRootCategories()
    {
        SqlCommand cmd = new SqlCommand("SELECT c.*, (SELECT COUNT(*) FROM Categories c2 WHERE c2.ParentCategoryID=c.CategoryID) as CategoryCount, (SELECT COUNT(*) FROM CategorySelectorMap WHERE CategoryID=c.CategoryID) as ProductCount FROM Categories c WHERE c.ParentCategoryID=0 ORDER BY SortOrder", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Categories_GetBreadcrumbTrail(int SelectedCategoryID, int BaseCategoryID, bool IncludeBase)
    {
        SqlCommand cmd = new SqlCommand("GetBreadcrumbTrail", Connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@SelectedCategoryID", SelectedCategoryID);
        cmd.Parameters.AddWithValue("@BaseCategoryID", BaseCategoryID);
        cmd.Parameters.AddWithValue("@IncludeBase", IncludeBase);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void Categories_Delete(int CategoryID)
    {
        DataRow drCategory = Categories_Get(CategoryID);
        DataRow drParent = Categories_Get((int)drCategory["ParentCategoryID"]);

        SqlCommand cmd = new SqlCommand("DELETE FROM Categories WHERE CategoryID=@CategoryID", Connection);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();

        // also go ahead and update any categories that depended on this one to now be in the parent
        DataTable dtChildren = Categories_GetChildren(CategoryID);

        foreach (DataRow dr in dtChildren.Rows)
        {
            Categories_Update((int)dr["CategoryID"],
                              (int)drParent["CategoryID"],
                              (string)dr["CategoryName"],
                              (bool)dr["UseCategoryLanding"],
                              (string)drParent["CanonicalName"] + ":" + (string)dr["CategoryName"]);
        }
    }


    #endregion

    #region CategorySelectorMap
    public void CategorySelectorMap_Delete(int CategoryID, int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM CategorySelectorMap WHERE CategoryID=@CategoryID AND SelectorID=@SelectorID;", Connection);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    public void CategorySelectorMap_DeleteByCategory(int CategoryID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM CategorySelectorMap WHERE CategoryID=@CategoryID;", Connection);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataRow CategorySelectorMap_Get(int SelectorID, int CategoryID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM CategorySelectorMap WHERE SelectorID=@SelectorID AND CategoryID=@CategoryID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public void CategorySelectorMap_Add(int SelectorID, int CategoryID)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO CategorySelectorMap (SelectorID, CategoryID) VALUES (@SelectorID, @CategoryID)", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void CategorySelectorMap_UpdateSortOrder(int CategorySelectorMapID, int SortOrder)
    {
        SqlCommand cmd = new SqlCommand("UPDATE CategorySelectorMap SET SortOrder=@SortOrder WHERE CategorySelectorMapID=@CategorySelectorMapID", Connection);
        cmd.Parameters.AddWithValue("@SortOrder", SortOrder);
        cmd.Parameters.AddWithValue("@CategorySelectorMapID", CategorySelectorMapID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region Discounts
    public DataTable Discounts_GetAll()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Discounts ORDER BY DiscountTitle", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataRow Discounts_Get(int DiscountID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Discounts WHERE DiscountID=@DiscountID", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public int Discounts_Add(string DiscountTitle, string DiscountCode, DateTime StartDate, DateTime EndDate, bool IsOneTimeDiscount, bool Redeemed, decimal RequiredSubtotal, decimal DiscountDollarAmount, int DiscountPercentage, bool GrantsFreeShipping, bool AppliesToAllItems)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Discounts (DiscountTitle, DiscountCode, StartDate, EndDate, IsOneTimeDiscount, Redeemed, RequiredSubtotal, DiscountDollarAmount, DiscountPercentage, GrantsFreeShipping, AppliesToAllItems) VALUES (@DiscountTitle, @DiscountCode, @StartDate, @EndDate, @IsOneTimeDiscount, @Redeemed, @RequiredSubtotal, @DiscountDollarAmount, @DiscountPercentage, @GrantsFreeShipping, @AppliesToAllItems); SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@DiscountTitle", DiscountTitle);
        cmd.Parameters.AddWithValue("@DiscountCode", DiscountCode);
        cmd.Parameters.AddWithValue("@StartDate", StartDate);
        cmd.Parameters.AddWithValue("@EndDate", EndDate);
        cmd.Parameters.AddWithValue("@IsOneTimeDiscount", IsOneTimeDiscount);
        cmd.Parameters.AddWithValue("@Redeemed", Redeemed);
        cmd.Parameters.AddWithValue("@RequiredSubtotal", RequiredSubtotal);
        cmd.Parameters.AddWithValue("@DiscountDollarAmount", DiscountDollarAmount);
        cmd.Parameters.AddWithValue("@DiscountPercentage", DiscountPercentage);
        cmd.Parameters.AddWithValue("@GrantsFreeShipping", GrantsFreeShipping);
        cmd.Parameters.AddWithValue("@AppliesToAllItems", AppliesToAllItems);
        int DiscountID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return DiscountID;
    }

    public void Discounts_Update(int DiscountID, string DiscountTitle, string DiscountCode, DateTime StartDate, DateTime EndDate, bool IsOneTimeDiscount, bool Redeemed, decimal RequiredSubtotal, decimal DiscountDollarAmount, int DiscountPercentage, bool GrantsFreeShipping, bool AppliesToAllItems)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Discounts SET DiscountTitle=@DiscountTitle, DiscountCode=@DiscountCode, StartDate=@StartDate, EndDate=@EndDate, IsOneTimeDiscount=@IsOneTimeDiscount, Redeemed=@Redeemed, RequiredSubtotal=@RequiredSubtotal, DiscountDollarAmount=@DiscountDollarAmount, DiscountPercentage=@DiscountPercentage, GrantsFreeShipping=@GrantsFreeShipping, AppliesToAllItems=@AppliesToAllItems WHERE DiscountID=@DiscountID", Connection);
        cmd.Parameters.AddWithValue("@DiscountTitle", DiscountTitle);
        cmd.Parameters.AddWithValue("@DiscountCode", DiscountCode);
        cmd.Parameters.AddWithValue("@StartDate", StartDate);
        cmd.Parameters.AddWithValue("@EndDate", EndDate);
        cmd.Parameters.AddWithValue("@IsOneTimeDiscount", IsOneTimeDiscount);
        cmd.Parameters.AddWithValue("@Redeemed", Redeemed);
        cmd.Parameters.AddWithValue("@RequiredSubtotal", RequiredSubtotal);
        cmd.Parameters.AddWithValue("@DiscountDollarAmount", DiscountDollarAmount);
        cmd.Parameters.AddWithValue("@DiscountPercentage", DiscountPercentage);
        cmd.Parameters.AddWithValue("@GrantsFreeShipping", GrantsFreeShipping);
        cmd.Parameters.AddWithValue("@AppliesToAllItems", AppliesToAllItems);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Discounts_Delete(int DiscountID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM DiscountItemMap WHERE DiscountID=@DiscountID", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM Discounts WHERE DiscountID=@DiscountID";
        cmd.ExecuteNonQuery();

        cmd.Dispose();
    }

    public DataRow Discounts_MatchDiscount(string DiscountCode)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Discounts WHERE DiscountCode=@DiscountCode AND Redeemed=0 AND StartDate <= getDate() AND EndDate > getDate()", Connection);
        cmd.Parameters.AddWithValue("@DiscountCode", DiscountCode);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataTable Discounts_Search(string srchQuery)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Discounts WHERE DiscountTitle LIKE @DiscountTitle OR DiscountCode LIKE @DiscountCode ORDER BY DiscountTitle", Connection);
        cmd.Parameters.AddWithValue("@DiscountTitle", srchQuery);
        cmd.Parameters.AddWithValue("@DiscountCode", srchQuery);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void Discounts_RedeemDiscount(int DiscountID)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Discounts SET Redeemed=1 WHERE DiscountID=@DiscountID", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region DiscountItemMap
    public void DiscountItemMap_Add(int DiscountID, int ItemID)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO DiscountItemMap (DiscountID, ItemID) VALUES (@DiscountID, @ItemID)", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        cmd.Parameters.AddWithValue("@ItemID", ItemID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void DiscountItemMap_Delete(int DiscountID, int ItemID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM DiscountItemMap WHERE DiscountID=@DiscountID AND ItemID=@ItemID", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        cmd.Parameters.AddWithValue("@ItemID", ItemID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void DiscountItemMap_UpdateDiscountID(int DiscountID)
    {
        SqlCommand cmd = new SqlCommand("UPDATE DiscountItemMap SET DiscountID=@DiscountID WHERE DiscountID=0", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataTable DiscountItemMap_GetItemIDsDiscountID(int DiscountID)
    {
        SqlCommand cmd = new SqlCommand("SELECT ItemID FROM DiscountItemMap WHERE DiscountID=@DiscountID", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable DiscountItemMap_SearchForItemsNotIncluded(int DiscountID, string srchSKU, string srchName)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Items WHERE SKU LIKE @SKU AND Name LIKE @Name AND ItemID NOT IN (SELECT ItemID FROM DiscountItemMap WHERE DiscountID=@DiscountID) ORDER BY Name", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        cmd.Parameters.AddWithValue("@SKU", srchSKU);
        cmd.Parameters.AddWithValue("@Name", srchName);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable DiscountItemMap_GetAllItemsNotIncluded(int DiscountID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Items WHERE ItemID NOT IN (SELECT ItemID FROM DiscountItemMap WHERE DiscountID=@DiscountID) ORDER BY Name", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }
    #endregion

    #region Items
    public DataRow Items_GetByItemIDAndQuantity(int ItemID, int Quantity)
    {
        SqlCommand cmd = new SqlCommand("SELECT TOP 1 i.*, i.Price as ItemPrice, p.Price as VolumePrice FROM Items i LEFT OUTER JOIN VolumePricingTableItem p ON i.VolumePricingTableID = p.VolumePricingTableID WHERE i.ItemID=@ItemID AND (p.Quantity <= @Quantity OR p.Quantity IS NULL) ORDER BY p.Quantity DESC", Connection);
        cmd.Parameters.AddWithValue("@ItemID", ItemID);
        cmd.Parameters.AddWithValue("@Quantity", Quantity);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataTable Items_GetBySelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Items WHERE SelectorID=@SelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Items_GetByDiscountID(int DiscountID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Items i INNER JOIN DiscountItemMap dim ON i.ItemID = dim.itemID WHERE dim.DiscountID=@DiscountID", Connection);
        cmd.Parameters.AddWithValue("@DiscountID", DiscountID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataRow Items_Get(int ItemID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Items WHERE ItemID=@ItemID", Connection);
        cmd.Parameters.AddWithValue("@ItemID", ItemID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public int Items_Add(int SelectorID, int Inventory, string SKU, string Name, decimal Price, decimal Weight, int? VolumePricingTableID, bool Active, int LowInventory)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Items (SelectorID,Inventory,SKU,[Name],Price,Weight,VolumePricingTableID,Active,LowInventory) VALUES (@SelectorID,@Inventory,@SKU,@Name,@Price,@Weight,@VolumePricingTableID,@Active,@LowInventory); SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@Inventory", Inventory);
        cmd.Parameters.AddWithValue("@SKU", SKU);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@Price", Price);
        cmd.Parameters.AddWithValue("@Weight", Weight);
        cmd.Parameters.AddWithValue("@VolumePricingTableID", (VolumePricingTableID == null ? DBNull.Value : (object)VolumePricingTableID.Value));
        cmd.Parameters.AddWithValue("@Active", Active);
        cmd.Parameters.AddWithValue("@LowInventory", LowInventory);
        int ItemID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return ItemID;
    }

    public void Items_Update(int ItemID, int SelectorID, int? Inventory, string SKU, string Name, decimal Price, decimal Weight, int? VolumePricingTableID, bool? Active, int? LowInventory)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Items SET", Connection);
        if (SelectorID != null && SelectorID != 0)
        {
            cmd.CommandText += " SELECTORID=@SELECTORID,";
            cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        }
        if (Inventory != null)
        {
            cmd.CommandText += " Inventory=@Inventory,";
            cmd.Parameters.AddWithValue("@Inventory", Inventory);
        }
        if (LowInventory != null)
        {
            cmd.CommandText += " LowInventory=@LowInventory,";
            cmd.Parameters.AddWithValue("@LowInventory", LowInventory);
        }
        if (SKU != null && SKU != string.Empty)
        {
            cmd.CommandText += " SKU=@SKU,";
            cmd.Parameters.AddWithValue("@SKU", SKU);
        }
        if (Name != null && Name != string.Empty)
        {
            cmd.CommandText += " [Name]=@Name,";
            cmd.Parameters.AddWithValue("@Name", Name);
        }
        if (Price != null && Price != 0)
        {
            cmd.CommandText += " Price=@Price,";
            cmd.Parameters.AddWithValue("@Price", Price);
        }
        if (Weight != null && Weight != 0)
        {
            cmd.CommandText += " Weight=@Weight,";
            cmd.Parameters.AddWithValue("@Weight", Weight);
        }
        if (VolumePricingTableID != null && VolumePricingTableID != 0)
        {
            cmd.CommandText += " VolumePricingTableID=@VolumePricingTableID,";
            cmd.Parameters.AddWithValue("@VolumePricingTableID", (VolumePricingTableID == null ? DBNull.Value : (object)VolumePricingTableID.Value));
        }
        if (Active != null)
        {
            cmd.CommandText += " Active=@Active,";
            cmd.Parameters.AddWithValue("@Active", Active);

        }
        cmd.CommandText += " WHERE ItemID=@ItemID";
        cmd.Parameters.AddWithValue("@ItemID", ItemID);
        cmd.CommandText = cmd.CommandText.Replace(", WHERE", " WHERE");
        cmd.ExecuteNonQuery();
    }

    public void Items_Delete(int ItemID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM Items WHERE ItemID=@ItemID", Connection);
        cmd.Parameters.AddWithValue("@ItemID", ItemID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataTable Items_GetBySelectorIDForDetailPage(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Items WHERE SelectorID=@SelectorID AND Active=1 ORDER BY Name", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Items_Search(string Name, string SKU)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Items WHERE", Connection);
        if (Name != string.Empty)
        {
            cmd.CommandText += " AND Name=@Name";
        }
        if (SKU != string.Empty)
        {
            cmd.CommandText += " AND SKU = @SKU";
        }
        cmd.CommandText += " AND Active=1 ORDER BY Name";
        cmd.CommandText = cmd.CommandText.Replace("WHERE AND", "WHERE");
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@SKU", SKU);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void Items_DecrementInventory(int ItemID, int QuantityToDecrement)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Items SET Inventory=Inventory-@QuantityToDecrement WHERE ItemID=@ItemID", Connection);
        cmd.Parameters.AddWithValue("@QuantityToDecrement", QuantityToDecrement);
        cmd.Parameters.AddWithValue("@ItemID", ItemID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    #endregion

    #region OrderAddresses
    public int OrderAddresses_AddByAddressID(int AddressID)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO OrderAddresses (IsBilling,FirstName,LastName,Address,City,State,Country,ZipCode,Phone,EmailAddress) SELECT IsBilling,FirstName,LastName,Address,City,State,Country,ZipCode,Phone,EmailAddress FROM Addresses WHERE AddressID=@AddressID; SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@AddressID", AddressID);
        int OrderAddressID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return OrderAddressID;
    }

    public DataRow OrderAddresses_GetByOrderAddressID(int OrderAddressID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM OrderAddresses WHERE OrderAddressID=@OrderAddressID", Connection);
        cmd.Parameters.AddWithValue("@OrderAddressID", OrderAddressID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public void OrderAddresses_Update(int OrderAddressID, string FirstName, string LastName, string Address, string City, string State, string Country, string ZipCode, string Phone, string EmailAddress)
    {
        SqlCommand cmd = new SqlCommand("UPDATE OrderAddresses SET FirstName=@FirstName, LastName=@LastName, Address=@Address, City=@City, State=@State, Country=@Country, ZipCode=@ZipCode, Phone=COALESCE(@Phone,Phone), EmailAddress=COALESCE(@EmailAddress,EmailAddress) WHERE OrderAddressID=@OrderAddressID", Connection);
        cmd.Parameters.AddWithValue("@OrderAddressID", OrderAddressID);
        cmd.Parameters.AddWithValue("@FirstName", FirstName);
        cmd.Parameters.AddWithValue("@LastName", LastName);
        cmd.Parameters.AddWithValue("@Address", Address);
        cmd.Parameters.AddWithValue("@City", City);
        cmd.Parameters.AddWithValue("@State", State);
        cmd.Parameters.AddWithValue("@Country", Country);
        cmd.Parameters.AddWithValue("@ZipCode", ZipCode);
        cmd.Parameters.AddWithValue("@Phone", Phone);
        cmd.Parameters.AddWithValue("@EmailAddress", EmailAddress);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region OrderItems

    public void OrderItems_Update(int OrderItemID, int OrderID, string SKU, string Name, decimal Price, int Weight, int? Quantity, string Customization, string QuickBooksListID)
    {
        SqlCommand cmd = new SqlCommand("UPDATE OrderItems SET", Connection);
        if (OrderID != null && OrderID != 0)
        {
            cmd.CommandText += " OrderID=@OrderID,";
            cmd.Parameters.AddWithValue("@OrderID", OrderID);
        }
        if (SKU != null && SKU != string.Empty)
        {
            cmd.CommandText += " SKU=@SKU,";
            cmd.Parameters.AddWithValue("@SKU", SKU);
        }
        if (Name != null && Name != string.Empty)
        {
            cmd.CommandText += " [Name]=@Name,";
            cmd.Parameters.AddWithValue("@Name", Name);
        }
        if (Price != null && Price != 0)
        {
            cmd.CommandText += " Price=@Price,";
            cmd.Parameters.AddWithValue("@Price", Price);
        }
        if (Weight != null && Weight != 0)
        {
            cmd.CommandText += " Weight=@Weight,";
            cmd.Parameters.AddWithValue("@Weight", Weight);
        }
        if (Quantity != null && Quantity != -999)
        {
            cmd.CommandText += " Quantity=@Quantity,";
            cmd.Parameters.AddWithValue("@Quantity", Quantity);
        }
        if (Customization != null && Customization != string.Empty)
        {
            cmd.CommandText += " Customization=@Customization,";
            cmd.Parameters.AddWithValue("@Customization", Customization);
        }
        if (QuickBooksListID != null && QuickBooksListID != string.Empty)
        {
            cmd.CommandText += " QuickBooksListID=@QuickBooksListID,";
            cmd.Parameters.AddWithValue("@QuickBooksListID", QuickBooksListID);
        }
        cmd.CommandText += " WHERE OrderItemID=@OrderItemID";
        cmd.Parameters.AddWithValue("@OrderItemID", OrderItemID);
        cmd.CommandText = cmd.CommandText.Replace(", WHERE", " WHERE");
        cmd.ExecuteNonQuery();
    }

    public DataTable OrderItems_Search(string Name, string SKU, int? OrderID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM OrderItems WHERE", Connection);
        if (Name != string.Empty)
        {
            cmd.CommandText += " AND Name=@Name";
            cmd.Parameters.AddWithValue("@Name", Name);
        }
        if (SKU != string.Empty)
        {
            cmd.CommandText += " AND SKU = @SKU";
            cmd.Parameters.AddWithValue("@SKU", SKU);
        }
        if (OrderID != null)
        {
            cmd.CommandText += " AND OrderID = @OrderID";
            cmd.Parameters.AddWithValue("@OrderID", OrderID);
        }
        cmd.CommandText += " ORDER BY Name";
        cmd.CommandText = cmd.CommandText.Replace("WHERE AND", "WHERE");
       
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }
    public DataTable OrderItems_GetByOrderID(int OrderID)
    {
        SqlCommand cmd = new SqlCommand("SELECT *, Price * Quantity AS Cost FROM OrderItems WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable OrderItems_GetForAdmin(int OrderID)
    {
        SqlCommand cmd = new SqlCommand("SELECT *, [Name] + '<br>' + COALESCE(Customization,'') as ItemName,Price * Quantity AS Cost FROM OrderItems WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void OrderItems_Add(int OrderID, int ItemID, int Quantity, decimal Price, string Customization)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO OrderItems ([OrderID], [SKU], [Name], [Price], [Weight], [Quantity], [Customization]", Connection);
        cmd.CommandText += " )SELECT @OrderID, SKU, Name, @Price, Weight, @Quantity, @Customization";
        cmd.CommandText += " FROM Items WHERE ItemID=@ItemID";
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.Parameters.AddWithValue("@ItemID", ItemID);
        cmd.Parameters.AddWithValue("@Quantity", Quantity);
        cmd.Parameters.AddWithValue("@Price", Price);
        cmd.Parameters.AddWithValue("@Customization", Customization);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region Orders
    public int Orders_Add(int CustomerID, int BillingAddressID, int ShippingAddressID, decimal ShippingAmount, string ShippingMethod, decimal TaxAmount, decimal Amount, string IPAddress, string PaymentMethod, string CardType, string LastFour, string PONumber, string UserComments, string TxnID)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Orders (CustomerID, BillingAddressID, ShippingAddressID, ShippingAmount, ShippingMethod, TaxAmount, Amount, PaymentMethod, PONumber, CardType, LastFour, IPAddress, UserComments, TxnID) VALUES (@CustomerID, @BillingAddressID, @ShippingAddressID, @ShippingAmount, @ShippingMethod, @TaxAmount, @Amount, @PaymentMethod, @PONumber, @CardType, @LastFour, @IPAddress, @UserComments, @TxnID); SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        cmd.Parameters.AddWithValue("@BillingAddressID", BillingAddressID);
        cmd.Parameters.AddWithValue("@ShippingAddressID", ShippingAddressID);
        cmd.Parameters.AddWithValue("@ShippingAmount", ShippingAmount);
        cmd.Parameters.AddWithValue("@ShippingMethod", ShippingMethod);
        cmd.Parameters.AddWithValue("@TaxAmount", TaxAmount);
        cmd.Parameters.AddWithValue("@Amount", Amount);
        cmd.Parameters.AddWithValue("@IPAddress", IPAddress);
        cmd.Parameters.AddWithValue("@PaymentMethod", PaymentMethod);
        cmd.Parameters.AddWithValue("@CardType", CardType);
        cmd.Parameters.AddWithValue("@LastFour", LastFour);
        cmd.Parameters.AddWithValue("@PONumber", PONumber);
        cmd.Parameters.AddWithValue("@UserComments", UserComments);
        cmd.Parameters.AddWithValue("@TxnID", TxnID);
        int OrderID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return OrderID;
    }

    public DataRow Orders_GetByOrderID(int OrderID)
    {
        SqlCommand cmd = new SqlCommand("SELECT *, os.StatusName FROM Orders o INNER JOIN OrderStatus os ON o.OrderStatusID=os.OrderStatusID WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataTable Orders_GetByCustomerID(int CustomerID)
    {
        SqlCommand cmd = new SqlCommand("SELECT o.*, os.StatusName FROM Orders o INNER JOIN OrderStatus os ON o.OrderStatusID=os.OrderStatusID WHERE CustomerID=@CustomerID ORDER BY Timestamp DESC", Connection);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataRow Orders_GetOrderAndBillingAddress(int OrderID)
    {
        SqlCommand cmd = new SqlCommand("SELECT o.*,a.* FROM Orders o INNER JOIN OrderAddresses a ON o.BillingAddressID = a.OrderAddressID WHERE o.OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataTable Orders_GetNewest()
    {
        SqlCommand cmd = new SqlCommand("SELECT o.*,billing.LastName + ', ' + billing.FirstName AS CombinedName, s.StatusName FROM Orders o INNER JOIN OrderAddresses billing ON o.BillingAddressID = billing.OrderAddressID INNER JOIN OrderStatus s ON o.OrderStatusID = s.OrderStatusID WHERE o.OrderStatusID=1 ORDER BY TimeStamp DESC", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Orders_GetAll()
    {
        SqlCommand cmd = new SqlCommand("SELECT o.*,billing.LastName + ', ' + billing.FirstName AS CombinedName, s.StatusName FROM Orders o INNER JOIN OrderAddresses billing ON o.BillingAddressID = billing.OrderAddressID INNER JOIN OrderStatus s ON o.OrderStatusID = s.OrderStatusID ORDER BY OrderID DESC", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Orders_Search(int? OrderID, int? OrderStatusID, string FirstName, string LastName, DateTime? OrderDateFrom, DateTime? OrderDateTo)
    {
        SqlCommand cmd = new SqlCommand("SELECT o.*,billing.LastName + ', ' + billing.FirstName AS CombinedName, s.StatusName FROM Orders o INNER JOIN OrderAddresses billing ON o.BillingAddressID = billing.OrderAddressID INNER JOIN OrderStatus s ON o.OrderStatusID = s.OrderStatusID WHERE", Connection);
        cmd.CommandText += " o.OrderStatusID=COALESCE(@OrderStatusID,o.OrderStatusID) AND TimeStamp > COALESCE(@OrderDateFrom, '1/1/1900') AND TimeStamp < COALESCE(@OrderDateTo, '1/1/2500') AND OrderID=COALESCE(@OrderID,OrderID)";
        if(FirstName != null && FirstName != string.Empty)
            cmd.CommandText += " AND FirstName LIKE @FirstName";
        if (LastName != null && LastName != String.Empty)
            cmd.CommandText += " AND LastName LIKE @LastNAme";
        cmd.Parameters.AddWithValue("@OrderID", (OrderID == null ? DBNull.Value : (object)OrderID));
        cmd.Parameters.AddWithValue("@OrderStatusID", (OrderStatusID == null ? DBNull.Value : (object)OrderStatusID));
        cmd.Parameters.AddWithValue("@FirstName", FirstName);
        cmd.Parameters.AddWithValue("@LastName", LastName);
        cmd.Parameters.AddWithValue("@OrderDateFrom", (OrderDateFrom == null ? DBNull.Value : (object)OrderDateFrom));
        cmd.Parameters.AddWithValue("@OrderDateTo", (OrderDateTo == null ? DBNull.Value : (object)OrderDateTo));
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void Orders_UpdateForProcessing(int OrderID, string AdminComments, DateTime? DateShipped, int? OrderStatusID, bool? IsAuthorized, bool? IsCaptured)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET AdminComments=COALESCE(@AdminComments,AdminComments), DateShipped=COALESCE(@DateShipped, DateShipped), OrderStatusID=COALESCE(@OrderStatusID,OrderStatusID), IsAuthorized=COALESCE(@IsAuthorized, IsAuthorized), IsCaptured=COALESCE(@IsCaptured, IsCaptured) WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@AdminComments", (AdminComments == null ? DBNull.Value : (object)AdminComments));
        cmd.Parameters.AddWithValue("@DateShipped", (DateShipped == null ? DBNull.Value : (object)DateShipped));
        cmd.Parameters.AddWithValue("@OrderStatusID", (OrderStatusID == null ? DBNull.Value : (object)OrderStatusID));
        cmd.Parameters.AddWithValue("@IsAuthorized", (IsAuthorized == null ? DBNull.Value : (object)IsAuthorized));
        cmd.Parameters.AddWithValue("@IsCaptured", (IsCaptured == null ? DBNull.Value : (object)IsCaptured));
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_UpdateForPayment(int OrderID, string PaymentRefCode, bool? IsAuthorized, bool? IsCaptured, string LastResponseCode, string LastResponseText)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET PaymentRefCode=@PaymentRefCode, IsAuthorized=COALESCE(@IsAuthorized, IsAuthorized), IsCaptured=COALESCE(@IsCaptured, IsCaptured), LastResponseCode=@LastResponseCode, LastResponseText=@LastResponseText WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@PaymentRefCode", PaymentRefCode);
        cmd.Parameters.AddWithValue("@IsAuthorized", (IsAuthorized == null ? DBNull.Value : (object)IsAuthorized));
        cmd.Parameters.AddWithValue("@IsCaptured", (IsCaptured == null ? DBNull.Value : (object)IsCaptured));
        cmd.Parameters.AddWithValue("@LastResponseCode", LastResponseCode);
        cmd.Parameters.AddWithValue("@LastResponseText", LastResponseText);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_SetTrackingNumber(int OrderID, string TrackingNumber)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET TrackingNumber=@TrackingNumber WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@TrackingNumber", TrackingNumber);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_Ship(int OrderID, string TrackingNumber)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET TrackingNumber=@TrackingNumber, IsShipped = 'true' WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@TrackingNumber", TrackingNumber);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_VoidShipment(int OrderID)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET IsShipped = 'false' WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_SetOrderStatusID(int OrderID, int OrderStatusID)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET OrderStatusID=@OrderStatusID WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@OrderStatusID", OrderStatusID);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_SetRefundAmount(int OrderID, decimal RefundAmount)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET RefundAmount=@RefundAmount WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@RefundAmount", RefundAmount);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_SetAdminComment(int OrderID, string AdminComment)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET AdminComments=@AdminComment WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@AdminComment", AdminComment);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_SetDateShipped(int OrderID, DateTime DateShipped)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET DateShipped=@DateShipped WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@DateShipped", DateShipped);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_SetTxnID(int OrderID, string TxnID)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET TxnID=@TxnID WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@TxnID", TxnID);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Orders_SetDateOfExport(int OrderID, DateTime DateOfExport)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Orders SET DateOfExport=@DateOfExport WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@DateOfExport", DateOfExport);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    #endregion

    #region OrderDiscounts
    public DataTable OrderDiscounts_GetByOrderID(int OrderID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM OrderDiscounts WHERE OrderID=@OrderID", Connection);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void OrderDiscounts_Add(int OrderID, string DiscountName, decimal DiscountAmount)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO OrderDiscounts (OrderID, DiscountName, DiscountAmount) VALUES (@OrderID, @DiscountName, @DiscountAmount)", Connection);
        cmd.Parameters.AddWithValue("@OrderID", OrderID);
        cmd.Parameters.AddWithValue("@DiscountName", DiscountName);
        cmd.Parameters.AddWithValue("@DiscountAmount", DiscountAmount);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region OrderStatus
    public DataTable OrderStatus_GetAll()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM OrderStatus", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }
    #endregion

    #region Customizations
    public DataRow Customizations_Get(int CustomizationID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Customizations WHERE CustomizationID=@CustomizationID", Connection);
        cmd.Parameters.AddWithValue("@CustomizationID", CustomizationID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataTable Customizations_GetBySelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Customizations WHERE SelectorID=@SelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public int Customizations_Add(int SelectorID, string Name, string Prompt, int ControlType)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Customizations (SelectorID, Name, Prompt, ControlType) VALUES (@SelectorID, @Name, @Prompt, @ControlType); SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@Prompt", Prompt);
        cmd.Parameters.AddWithValue("@ControlType", ControlType);
        int CustomizationID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return CustomizationID;
    }

    public void Customizations_Update(int CustomizationID, int SelectorID, string Name, string Prompt, int ControlType)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Customizations SET SelectorID=@SelectorID, Name=@Name,Prompt=@Prompt,ControlType=@ControlType WHERE CustomizationID=@CustomizationID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@Prompt", Prompt);
        cmd.Parameters.AddWithValue("@ControlType", ControlType);
        cmd.Parameters.AddWithValue("@CustomizationID", CustomizationID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Customizations_Delete(int CustomizationID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM Customizations WHERE CustomizationID=@CustomizationID", Connection);
        cmd.Parameters.AddWithValue("@CustomizationID", CustomizationID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region CustomizationOptions
    public DataTable CustomizationOptions_GetByCustomizationID(int CustomizationID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM CustomizationOptions WHERE CustomizationID=@CustomizationID", Connection);
        cmd.Parameters.AddWithValue("@CustomizationID", CustomizationID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable CustomizationOptions_GetBySelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT co.CustomizationID, co.CustomizationOptionID, co.Price, co.OptionName + (CASE WHEN co.Price > 0 THEN '[+$' + CONVERT(varchar(12),co.Price,1) + ']' WHEN co.Price < 0 THEN ' [-$' + CONVERT(varchar(12),co.Price,1) + ']' ELSE '' END) as OptionName FROM CustomizationOptions co INNER JOIN Customizations c ON co.CustomizationID = c.CustomizationID WHERE c.SelectorID=@SelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void CustomizationOptions_Add(int CustomizationID, string OptionName, decimal Price)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO CustomizationOptions (CustomizationID, OptionName, Price) VALUES (@CustomizationID, @OptionName, @Price); SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@CustomizationID", CustomizationID);
        cmd.Parameters.AddWithValue("@OptionName", OptionName);
        cmd.Parameters.AddWithValue("@Price", Price);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void CustomizationOptions_Update(int CustomizationOptionID, int CustomizationID, string OptionName, decimal Price)
    {
        SqlCommand cmd = new SqlCommand("UPDATE CustomizationOptions SET CustomizationID=@CustomizationID, OptionName=@OptionName, Price=@Price WHERE CustomizationOptionID=@CustomizationOptionID;", Connection);
        cmd.Parameters.AddWithValue("@CustomizationID", CustomizationID);
        cmd.Parameters.AddWithValue("@OptionName", OptionName);
        cmd.Parameters.AddWithValue("@Price", Price);
        cmd.Parameters.AddWithValue("@CustomizationOptionID", CustomizationOptionID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void CustomizationOptions_Delete(int CustomizationOptionID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM CustomizationOptions WHERE CustomizationOptionID=@CustomizationOptionID", Connection);
        cmd.Parameters.AddWithValue("@CustomizationOptionID", CustomizationOptionID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void CustomizationOptions_DeleteByCustomizationID(int CustomizationID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM CustomizationOptions WHERE CustomizationID=@CustomizationID", Connection);
        cmd.Parameters.AddWithValue("@CustomizationID", CustomizationID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public decimal CustomizationOptions_GetPriceByCustomizationOptionID(int CustomizationOptionID)
    {
        SqlCommand cmd = new SqlCommand("SELECT Price FROM CustomizationOptions WHERE CustomizationOptionID=@CustomizationOptionID", Connection);
        cmd.Parameters.AddWithValue("@CustomizationOptionID", CustomizationOptionID);
        decimal Price = (decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return Price;
    }
    #endregion

    #region Selectors
    public int Selectors_Add(string SelectorType, string SKU, string Name, string Description, string TextPrice, bool Active, bool Browsable)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Selectors (SelectorType, SKU, Name, Description, TextPrice, Active, Browsable) VALUES (@SelectorType, @SKU, @Name, @Description, @TextPrice, @Active, @Browsable); SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@SelectorType", SelectorType);
        cmd.Parameters.AddWithValue("@SKU", SKU);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@Description", Description);
        cmd.Parameters.AddWithValue("@TextPrice", TextPrice);
        cmd.Parameters.AddWithValue("@Active", Active);
        cmd.Parameters.AddWithValue("@Browsable", Browsable);
        int SelectorID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return SelectorID;
    }

    public void Selectors_Update(int SelectorID, string SelectorType, string SKU, string Name, string Description, string TextPrice, bool Active, bool Browsable)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Selectors SET SelectorType = @SelectorType, SKU = @SKU, Name = @Name, Description = @Description, TextPrice = @TextPrice, Active = @Active, Browsable = @Browsable WHERE SelectorID = @SelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorType", SelectorType);
        cmd.Parameters.AddWithValue("@SKU", SKU);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@Description", Description);
        cmd.Parameters.AddWithValue("@TextPrice", TextPrice);
        cmd.Parameters.AddWithValue("@Active", Active);
        cmd.Parameters.AddWithValue("@Browsable", Browsable);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataTable Selectors_GetByCategoryID(int CategoryID)
    {
        SqlCommand cmd = new SqlCommand("SELECT s.* FROM Selectors s INNER JOIN CategorySelectorMap csm ON s.SelectorID = csm.SelectorID WHERE csm.CategoryID=@CategoryID ORDER BY csm.SortOrder", Connection);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataRow Selectors_GetBySelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT TOP 1 * FROM Selectors s LEFT OUTER JOIN PhotoSets ps ON s.SelectorID=ps.SelectorID WHERE s.SelectorID=@SelectorID ORDER BY ps.SortOrder", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataRow Selectors_GetForPreview(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT TOP 1 csm.CategorySelectorMapID, s.* FROM CategorySelectorMap csm INNER JOIN Selectors s ON csm.SelectorID = s.SelectorID WHERE s.SelectorID = @SelectorID ORDER BY csm.SortOrder", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataRow dr = GetDataTable(cmd).Rows[0];
        cmd.Dispose();

        return dr;
    }

    public DataTable Selectors_GetForSort(int CategoryID)
    {
        SqlCommand cmd = new SqlCommand("SELECT s.Name as Name, csm.CategorySelectorMapID FROM CategorySelectorMap csm INNER JOIN Selectors s ON csm.SelectorID = s.SelectorID WHERE csm.CategoryID=@CategoryID ORDER BY csm.SortOrder", Connection);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Selectors_GetAll()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Selectors ORDER BY Name", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataRow Selectors_GetByCategorySelectorMapID(int CategorySelectorMapID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM CategorySelectorMap csm INNER JOIN Selectors s ON csm.SelectorID = s.SelectorID WHERE csm.CategorySelectorMapID=@CategorySelectorMapID", Connection);
        cmd.Parameters.AddWithValue("@CategorySelectorMapID", CategorySelectorMapID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataTable Selectors_GetForBrowsing(int CategoryID, string AttributeIDList, int AttributeCount)
    {
        if (AttributeIDList != null)
        {
            SqlCommand cmd = new SqlCommand("SELECT ROW_NUMBER() OVER (ORDER BY s.SelectorID) as RowNumber, s.SKU, s.Name, s.TextPrice, (SELECT TOP 1 CategorySelectorMapID FROM CategorySelectorMap csm WHERE csm.SelectorID = s.SelectorID ORDER BY SortOrder) AS CategorySelectorMapID, (SELECT TOP 1 Filename FROM PhotoSets ps WHERE ps.SelectorID=s.SelectorID ORDER BY SortOrder) AS Photo FROM Selectors s WHERE", Connection);
            if (CategoryID != 0)
            {
                cmd.CommandText += " AND csm.CategoryID = @CategoryID";
                cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
            }
            cmd.CommandText += " AND s.Active=1 AND s.Browsable=1";
            if (AttributeIDList != null)
            {
                cmd.CommandText += " AND s.SelectorID IN (SELECT SelectorID FROM AttributeSelectorMap where AttributeID in (" + AttributeIDList + ") GROUP BY SelectorID HAVING COUNT(AttributeID) = " + AttributeCount + ")";
            }

            cmd.CommandText = cmd.CommandText.Replace("WHERE AND", "WHERE");
            DataTable dt = GetDataTable(cmd);
            cmd.Dispose();

            return dt;
        }
        else
        {
            SqlCommand cmd = new SqlCommand("SELECT ROW_NUMBER() OVER (ORDER BY csm.SortOrder) as RowNumber, csm.CategorySelectorMapID, s.SKU, s.Name, s.TextPrice, (SELECT TOP 1 Filename FROM PhotoSets ps WHERE ps.SelectorID=s.SelectorID ORDER BY SortOrder) AS Photo FROM Selectors s INNER JOIN CategorySelectorMap csm ON s.SelectorID = csm.SelectorID INNER JOIN Categories c ON csm.CategoryID = c.CategoryID WHERE", Connection);
            if (CategoryID != 0)
            {
                cmd.CommandText += " AND csm.CategoryID = @CategoryID";
                cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
            }
            cmd.CommandText += " AND s.Active=1 AND s.Browsable=1";
            if (AttributeIDList != null)
                cmd.CommandText += " AND s.SelectorID IN (SELECT SelectorID FROM AttributeSelectorMap where AttributeID in (" + AttributeIDList + ") GROUP BY SelectorID HAVING COUNT(AttributeID) = " + AttributeCount + ")";

            cmd.CommandText = cmd.CommandText.Replace("WHERE AND", "WHERE");
            DataTable dt = GetDataTable(cmd);
            cmd.Dispose();

            return dt;
        }
    }

    public DataTable Selectors_GetTopResultsByCategoryID(int NumResults, int CategoryID)
    {
        SqlCommand cmd = new SqlCommand("SELECT TOP (@NumResults) csm.CategorySelectorMapID, s.*, (SELECT TOP 1 Filename FROM PhotoSets ps WHERE ps.SelectorID=s.SelectorID ORDER BY SortOrder) as Photo, @CategoryID as CategoryID FROM Selectors s INNER JOIN CategorySelectorMap csm ON s.SelectorID = csm.SelectorID WHERE s.Active=1 AND s.Browsable=1 AND csm.CategoryID=@CategoryID ORDER BY csm.SortOrder", Connection);
        cmd.Parameters.AddWithValue("@NumResults", NumResults);
        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Selectors_GetRelatedBySelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT s.*, COALESCE((SELECT TOP 1 Filename FROM PhotoSets ps WHERE ps.SelectorID=s.SelectorID ORDER BY SortOrder), 'noPhoto.gif') as Photo, (SELECT TOP 1 CategorySelectorMapID FROM CategorySelectorMap WHERE SelectorID=s.SelectorID) as CategorySelectorMapId FROM Selectors s INNER JOIN RelatedSelectors rs ON s.SelectorID=rs.RelatedSelectorID WHERE rs.SelectorID=@SelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Selectors_Search(string srchSKU, string srchName)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Selectors WHERE Name LIKE @Name AND SKU LIKE @SKU", Connection);
        cmd.Parameters.AddWithValue("@SKU", srchSKU);
        cmd.Parameters.AddWithValue("@Name", srchName);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Selectors_SearchInclusive(int SearchTop, string srchSKU, string srchQuery)
    {
        SqlCommand cmd = new SqlCommand("SELECT TOP (@SearchTop) ROW_NUMBER() OVER (ORDER BY s.Name) as RowNumber, s.*, (SELECT TOP 1 Filename FROM PhotoSets WHERE SelectorID=s.SelectorID ORDER BY SortOrder) as Photo, (SELECT TOP 1 CategorySelectorMapID FROM CategorySelectorMap WHERE SelectorID=s.SelectorID ORDER BY SortOrder) AS CategorySelectorMapID FROM Selectors s WHERE s.Active=1 AND s.Browsable=1 AND (s.SKU = @SKU OR s.Name LIKE @Query OR s.Description LIKE @Query)", Connection);
        cmd.Parameters.AddWithValue("@SearchTop", SearchTop);
        cmd.Parameters.AddWithValue("@SKU", srchSKU);
        cmd.Parameters.AddWithValue("@Query", srchQuery);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void Selectors_Delete(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM Items WHERE SelectorID=@SelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);

        cmd.CommandText = "DELETE FROM RelatedSelectors WHERE SelectorID=@SelectorID";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM RelatedSelectors WHERE RelatedSelectorID=@SelectorID";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM AttributeSelectorMap WHERE SelectorID=@SelectorID";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM PhotoSets WHERE SelectorID=@SelectorID";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM CategorySelectorMap WHERE SelectorID=@SelectorID";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM CustomizationOptions WHERE CustomizationID IN (SELECT CustomizationID FROM Customizations WHERE SelectorID=@SelectorID)";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM Customizations WHERE SelectorID=@SelectorID";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM Items WHERE SelectorID=@SelectorID";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM Selectors WHERE SelectorID=@SelectorID";
        cmd.ExecuteNonQuery();

        cmd.Dispose();
    }

    public DataTable Selectors_GetOrphans()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Selectors WHERE SelectorID NOT IN (SELECT SelectorID FROM CategorySelectorMap)", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    #endregion

    #region VolumePricingTable
    public void VolumePricingTable_Add(string Name)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO VolumePricingTable (Name) VALUES (@Name); SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@Name", Name);
        int VolumePricingTableID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        cmd = new SqlCommand("UPDATE VolumePricingTableItem SET VolumePricingTableID=@VolumePricingTableID WHERE VolumePricingTableID=0", Connection);
        cmd.Parameters.AddWithValue("@VolumePricingTableID", VolumePricingTableID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void VolumePricingTable_Update(int VolumePricingTableID, string Name)
    {
        SqlCommand cmd = new SqlCommand("UPDATE VolumePricingTable SET Name=@Name WHERE VolumePricingTableID=@VolumePricingTableID", Connection);
        cmd.Parameters.AddWithValue("@VolumePricingTableID", VolumePricingTableID);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataTable VolumePricingTable_GetAll()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM VolumePricingTable ORDER BY Name", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataRow VolumePricingTable_Get(int VolumePricingTableID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM VolumePricingTable WHERE VolumePricingTableID=@VolumePricingTableID", Connection);
        cmd.Parameters.AddWithValue("@VolumePricingTableID", VolumePricingTableID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public void VolumePricingTable_Delete(int VolumePricingTableID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM VolumePricingTableItem WHERE VolumePricingTableID=@VolumePricingTableID", Connection);
        cmd.Parameters.AddWithValue("@VolumePricingTableID", VolumePricingTableID);
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM VolumePricingTable WHERE VolumePricingTableID=@VolumePricingTableID";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "UPDATE Items SET VolumePricingTableID=NULL WHERE VolumePricingTableID=@VolumePricingTableID";
        cmd.ExecuteNonQuery();

        cmd.Dispose();
    }
    #endregion

    #region VolumePricingTableItem
    public DataTable VolumePricingTableItem_GetByItemID(int ItemID)
    {
        SqlCommand cmd = new SqlCommand("SELECT vpi.* FROM VolumePricingTableItem vpi INNER JOIN Items i ON vpi.VolumePricingTableID = i.VolumePricingTableID WHERE i.ItemID=@ItemID ORDER BY vpi.Quantity", Connection);
        cmd.Parameters.AddWithValue("@ItemID", ItemID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable VolumePricingTableItem_GetByVolumePricingTableID(int VolumePricingTableID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM VolumePricingTableItem WHERE VolumePricingTableID=@VolumePricingTableID", Connection);
        cmd.Parameters.AddWithValue("@VolumePricingTableID", VolumePricingTableID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void VolumePricingTableItem_Add(int VolumePricingTableID, int Quantity, decimal Price)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO VolumePricingTableItem (VolumePricingTableID, Quantity, Price) VALUES (@VolumePricingTableID, @Quantity, @Price)", Connection);
        cmd.Parameters.AddWithValue("@VolumePricingTableID", VolumePricingTableID);
        cmd.Parameters.AddWithValue("@Quantity", Quantity);
        cmd.Parameters.AddWithValue("@Price", Price);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void VolumePricingTableItem_Update(int VolumePricingTableItemID, int VolumePricingTableID, int Quantity, decimal Price)
    {
        SqlCommand cmd = new SqlCommand("UPDATE VolumePricingTableItem SET VolumePricingTableID=@VolumePricingTableID, Quantity=@Quantity, Price=@Price WHERE VolumePricingTableItemID=@VolumePricingTableItemID", Connection);
        cmd.Parameters.AddWithValue("@VolumePricingTableID", VolumePricingTableID);
        cmd.Parameters.AddWithValue("@Quantity", Quantity);
        cmd.Parameters.AddWithValue("@Price", Price);
        cmd.Parameters.AddWithValue("@VolumePricingTableItemID", VolumePricingTableItemID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void VolumePricingTableItem_Delete(int VolumePricingTableItemID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM VolumePricingTableItem WHERE VolumePricingTableItemID=@VolumePricingTableItemID", Connection);
        cmd.Parameters.AddWithValue("@VolumePricingTableItemID", VolumePricingTableItemID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region RelatedSelectors
    public DataTable RelatedSelectors_GetBySelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT s.* FROM RelatedSelectors rs INNER JOIN Selectors s ON rs.RelatedSelectorID=s.SelectorID WHERE rs.SelectorID=@SelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void RelatedSelectors_Delete(int SelectorID, int RelatedSelectorID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM RelatedSelectors WHERE SelectorID=@SelectorID AND RelatedSelectorID=@RelatedSelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@RelatedSelectorID", RelatedSelectorID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataRow RelatedSelectors_Get(int SelectorID, int RelatedSelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM RelatedSelectors WHERE SelectorID=@SelectorID AND RelatedSelectorID=@RelatedSelectorID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@RelatedSelectorID", RelatedSelectorID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public void RelatedSelectors_Add(int SelectorID, int RelatedSelectorID)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO RelatedSelectors (SelectorID, RelatedSelectorID) VALUES (@SelectorID, @RelatedSelectorID)", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@RelatedSelectorID", RelatedSelectorID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region PhotoSets
    public DataRow PhotoSets_Get(int PhotoSetID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM PhotoSets WHERE PhotoSetID=@PhotoSetID", Connection);
        cmd.Parameters.AddWithValue("@PhotoSetID", PhotoSetID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataTable PhotoSets_GetBySelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM PhotoSets WHERE SelectorID=@SelectorID ORDER BY SortOrder ASC", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void PhotoSets_Add(int SelectorID, string Filename, string Caption)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO PhotoSets (SelectorID, Filename, Caption) VALUES (@SelectorID, @Filename, @Caption)", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@Filename", Filename);
        cmd.Parameters.AddWithValue("@Caption", Caption);
        cmd.ExecuteNonQuery();
    }

    public void PhotoSets_Update(int PhotoSetID, int SelectorID, string Filename, string Caption)
    {
        SqlCommand cmd = new SqlCommand("UPDATE PhotoSets SET SelectorID=@SelectorID, Filename=@Filename, Caption=@Caption WHERE PhotoSetID=@PhotoSetID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@Filename", Filename);
        cmd.Parameters.AddWithValue("@Caption", Caption);
        cmd.Parameters.AddWithValue("@PhotoSetID", PhotoSetID);
        cmd.ExecuteNonQuery();
    }

    public void PhotoSets_Delete(int PhotoSetId)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM PhotoSets WHERE PhotoSetID=@PhotoSetID", Connection);
        cmd.Parameters.AddWithValue("@PhotoSetID", PhotoSetId);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void PhotoSets_SetDefaultPhoto(int SelectorID, int PhotoSetID)
    {
        SqlCommand cmd = new SqlCommand("UPDATE PhotoSets SET SortOrder = 1 WHERE SelectorID=@SelectorID AND PhotoSetID <> @PhotoSetID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@PhotoSetID", PhotoSetID);
        cmd.ExecuteNonQuery();

        cmd.CommandText = "UPDATE PhotoSets SET SortOrder = 0 WHERE PhotoSetID = @PhotoSetID";
        cmd.ExecuteNonQuery();

        cmd.Dispose();
    }
    #endregion

    #region Customers
    public DataRow Customers_GetNonOneTimeByEmailAddress(string EmailAddress, bool? Active)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE EmailAddress=@EmailAddress AND OneTimeCustomer=0 AND Active=COALESCE(@Active,Active)", Connection);
        cmd.Parameters.AddWithValue("@EmailAddress", EmailAddress);
        cmd.Parameters.AddWithValue("@Active", (Active == null ? DBNull.Value : (object)Active));
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public DataRow Customers_GetNonOneTimeByEmailAddressAndPassword(string EmailAddress, string Password)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE EmailAddress=@EmailAddress AND pwdcompare(@Password, Password) = 1 AND OneTimeCustomer=0 AND Active=1", Connection);
        cmd.Parameters.AddWithValue("@EmailAddress", EmailAddress);
        cmd.Parameters.AddWithValue("@Password", Password);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public int Customers_Add(string EmailAddress, string Password, bool OneTimeCustomer, bool Active)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Customers (EmailAddress,Password,OneTimeCustomer,Active) VALUES (@EmailAddress,pwdencrypt(@Password),@OneTimeCustomer,@Active) SELECT @@IDENTITY;", Connection);
        cmd.Parameters.AddWithValue("@EmailAddress", EmailAddress);
        cmd.Parameters.AddWithValue("@Password", Password);
        cmd.Parameters.AddWithValue("@OneTimeCustomer", OneTimeCustomer);
        cmd.Parameters.AddWithValue("@Active", Active);
        int CustomerID = (int)(decimal)cmd.ExecuteScalar();
        cmd.Dispose();

        return CustomerID;
    }

    public DataRow Customers_Get(int CustomerID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE CustomerID=@CustomerID", Connection);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public void Customers_UpdateQuickBooksListID(int CustomerID, string QuickBooksListID)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Customers SET QuickBooksListID=@QuickBooksListID WHERE CustomerID=@CustomerID", Connection);
        cmd.Parameters.AddWithValue("@QuickBooksListID", QuickBooksListID);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Customers_UpdateQuickBooksStatusID(int CustomerID, int CustomerQuickBooksStatusID)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Customers SET CustomerQuickBooksStatusID=@CustomerQuickBooksStatusID WHERE CustomerID=@CustomerID", Connection);
        cmd.Parameters.AddWithValue("@CustomerQuickBooksStatusID", CustomerQuickBooksStatusID);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Customers_UpdatePassword(int CustomerID, string Password)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Customers SET Password=pwdencrypt(@Password) WHERE CustomerID=@CustomerID", Connection);
        cmd.Parameters.AddWithValue("@Password", Password);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region Variables
    public DataTable Variables_GetAll()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Variables ORDER BY Variable", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void Variables_Update(string Variable, string Value)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Variables SET Value=@Value WHERE Variable=@Variable", Connection);
        cmd.Parameters.AddWithValue("@Value", Value);
        cmd.Parameters.AddWithValue("@Variable", Variable);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public string Variables_GetOptions(string Variable)
    {
        SqlCommand cmd = new SqlCommand("SELECT Options FROM Variables WHERE Variable=@Variable", Connection);
        cmd.Parameters.AddWithValue("@Variable", Variable);
        string Options = (string)cmd.ExecuteScalar();
        cmd.Dispose();

        return Options;
    }
    #endregion

    #region AttributeGroups
    public void AttributeGroups_Add(string GroupName)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO AttributeGroups (GroupName) VALUES (@GroupName)", Connection);
        cmd.Parameters.AddWithValue("@GroupName", GroupName);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void AttributeGroups_Update(int AttributeGroupID, string GroupName)
    {
        SqlCommand cmd = new SqlCommand("UPDATE AttributeGroups SET GroupName=@GroupName WHERE AttributeGroupID=@AttributeGroupID", Connection);
        cmd.Parameters.AddWithValue("@GroupName", GroupName);
        cmd.Parameters.AddWithValue("@AttributeGroupID", AttributeGroupID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataTable AttributeGroups_GetAll()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM AttributeGroups ORDER BY GroupName", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataRow AttributeGroups_Get(int AttributeGroupID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM AttributeGroups WHERE AttributeGroupID=@AttributeGroupID", Connection);
        cmd.Parameters.AddWithValue("@AttributeGroupID", AttributeGroupID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public void AttributeGroups_Delete(int AttributeGroupID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM AttributeSelectorMap WHERE AttributeID in (SELECT AttributeID FROM Attributes WHERE AttributeGroupID=@AttributeGroupID)", Connection);
        cmd.Parameters.AddWithValue("@AttributeGroupID", AttributeGroupID);
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM Attributes WHERE AttributeGroupID=@AttributeGroupID";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM AttributeGroups WHERE AttributeGroupID=@AttributeGroupID";
        cmd.ExecuteNonQuery();

        cmd.Dispose();
    }
    #endregion

    #region Attributes
    public DataTable Attributes_GetAll()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Attributes a INNER JOIN AttributeGroups ag ON a.AttributeGroupID = ag.AttributeGroupID ORDER BY ag.GroupName, a.Name", Connection);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataRow Attributes_Get(int AttributeID)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Attributes WHERE AttributeID=@AttributeID", Connection);
        cmd.Parameters.AddWithValue("@AttributeID", AttributeID);
        DataRow dr = GetDataRow(cmd);
        cmd.Dispose();

        return dr;
    }

    public void Attributes_Add(int AttributeGroupID, string Name)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Attributes (AttributeGroupID, Name) VALUES (@AttributeGroupID, @Name)", Connection);
        cmd.Parameters.AddWithValue("@AttributeGroupID", AttributeGroupID);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void Attributes_Update(int AttributeID, int AttributeGroupID, string Name)
    {
        SqlCommand cmd = new SqlCommand("UPDATE Attributes SET AttributeGroupID=@AttributeGroupID, Name=@Name WHERE AttributeID=@AttributeID", Connection);
        cmd.Parameters.AddWithValue("@AttributeGroupID", AttributeGroupID);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@AttributeID", AttributeID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public DataTable Attributes_GetBySelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT a.AttributeID, ag.GroupName, a.Name FROM AttributeSelectorMap asm INNER JOIN Attributes a ON asm.AttributeID = a.AttributeID INNER JOIN AttributeGroups ag ON a.AttributeGroupID = ag.AttributeGroupID WHERE asm.SelectorID=@SelectorID ORDER BY ag.GroupName, a.Name", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public DataTable Attributes_GetAllNotAssociatedToSelectorID(int SelectorID)
    {
        SqlCommand cmd = new SqlCommand("SELECT a.AttributeID, ag.GroupName + ': ' + a.Name as FullName FROM Attributes a INNER JOIN AttributeGroups ag ON a.AttributeGroupID = ag.AttributeGroupID WHERE a.AttributeID NOT IN (SELECT AttributeID FROM AttributeSelectorMap WHERE SelectorID=@SelectorID) ORDER BY ag.GroupName, a.Name", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;
    }

    public void Attributes_Delete(int AttributeID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM AttributeSelectorMap WHERE AttributeID=@AttributeID", Connection);
        cmd.Parameters.AddWithValue("@AttributeID", AttributeID);
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM Attributes WHERE AttributeID=@AttributeID";
        cmd.ExecuteNonQuery();

        cmd.Dispose();
    }

    public DataTable Attributes_GetValidSubAttributes(int CategoryID, string ExistingAttributeList, int AttributeCount)
    {
        SqlCommand cmd;

        if (AttributeCount == 0)
        {
            cmd = new SqlCommand(@"
            SELECT ag.AttributeGroupID, ag.GroupName, a.AttributeID, a.Name, COUNT(csm.SelectorID) as ProductCount
            FROM AttributeGroups ag
            INNER JOIN Attributes a ON ag.AttributeGroupID=a.AttributeGroupID
            INNER JOIN AttributeSelectorMap asm ON a.AttributeID = asm.AttributeID
            INNER JOIN CategorySelectorMap csm ON asm.SelectorID = csm.SelectorID
            WHERE csm.CategoryID=@CategoryID
            GROUP BY ag.AttributeGroupID, ag.GroupName, a.AttributeID, a.Name, ag.SortOrder, a.SortOrder
            ORDER BY ag.GroupName, a.Name", Connection);
        }
        else
        {
            cmd = new SqlCommand(@"
            SELECT ag.AttributeGroupID, ag.GroupName, a.AttributeID, a.Name, COUNT(csm.SelectorID) as ProductCount
            FROM AttributeGroups ag
            INNER JOIN Attributes a ON ag.AttributeGroupID=a.AttributeGroupID
            INNER JOIN AttributeSelectorMap asm ON a.AttributeID = asm.AttributeID
            INNER JOIN CategorySelectorMap csm ON asm.SelectorID = csm.SelectorID
            WHERE csm.CategoryID=@CategoryID
            AND csm.SelectorID IN 
            (SELECT asm2.SelectorID
             FROM AttributeSelectorMap asm2
             INNER JOIN Attributes a2 ON asm2.AttributeID = a2.AttributeID
             INNER JOIN AttributeGroups ag2 ON a2.AttributeGroupID = ag2.AttributeGroupID
             WHERE ag2.AttributeGroupID IN (SELECT AttributeGroupID FROM Attributes WHERE AttributeID IN (" + ExistingAttributeList + @"))
             GROUP BY asm2.SelectorID
             HAVING COUNT(a2.AttributeID) >= @AttributeCount
            )
            AND a.AttributeGroupID NOT IN (SELECT AttributeGroupID FROM Attributes WHERE AttributeID IN (" + ExistingAttributeList + @"))
            GROUP BY ag.AttributeGroupID, ag.GroupName, a.AttributeID, a.Name, ag.SortOrder, a.SortOrder
            ORDER BY ag.GroupName, a.Name", Connection);
        }

        cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
        cmd.Parameters.AddWithValue("@AttributeCount", AttributeCount);
        DataTable dt = GetDataTable(cmd);
        cmd.Dispose();

        return dt;

    }
    #endregion

    #region AttributeSelectorMap
    public void AttributeSelectorMap_Delete(int SelectorID, int AttributeID)
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM AttributeSelectorMap WHERE SelectorID=@SelectorID AND AttributeID=@AttributeID", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@AttributeID", AttributeID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

    public void AttributeSelectorMap_Add(int SelectorID, int AttributeID)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO AttributeSelectorMap (SelectorID, AttributeID) VALUES (@SelectorID, @AttributeID)", Connection);
        cmd.Parameters.AddWithValue("@SelectorID", SelectorID);
        cmd.Parameters.AddWithValue("@AttributeID", AttributeID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    #endregion

    #region System Functions - Large queries that are procedural or span many tables
    public void System_RebuildProductCounts()
    {
        // first get a list of categories
        DataTable dtCategories = Categories_GetAll();

        // and reset their product counts to -1
        foreach (DataRow dr in dtCategories.Rows)
            dr["ProductCount"] = -1;

        SqlCommand cmd = new SqlCommand("UPDATE Categories SET ProductCount=@ProductCount WHERE CategoryID=@CategoryID", Connection);
        cmd.Parameters.Add("@ProductCount", SqlDbType.Int);
        cmd.Parameters.Add("@CategoryID", SqlDbType.Int);

        foreach (DataRow dr in dtCategories.Rows)
        {
            int ProdCount = System_GetProductCount(dtCategories, dr);
            cmd.Parameters["@ProductCount"].Value = ProdCount;
            cmd.Parameters["@CategoryID"].Value = dr["CategoryID"];
            cmd.ExecuteNonQuery();
        }

        cmd.Dispose();
    }

    private int System_GetProductCount(DataTable dtCategories, DataRow drCurrent)
    {
        if ((int)drCurrent["ProductCount"] > -1)
            return (int)drCurrent["ProductCount"];
        else
        {
            DataTable dtChildren = Categories_GetChildren((int)drCurrent["CategoryID"]);
            int Count = Selectors_GetByCategoryID((int)drCurrent["CategoryID"]).Rows.Count;

            foreach (DataRow drChild in dtChildren.Rows)
                Count += System_GetProductCount(dtCategories, drChild);

            drCurrent["ProductCount"] = Count;
            return Count;
        }
    }
    #endregion
}