-- =============================================
-- Author:  Duane Urban
-- Create date: 08/23/2009
-- Description: Inserts a new product, returns
-- the ID number of the inserted record and sends a
-- success or failure message to the client.
-- =============================================
CREATE PROCEDURE [dbo].[PD_AdminInsertProduct]
(
  @ProductCategory int,
  @ProductName nvarchar(50),
  @ProductDescription nvarchar(MAX),
  @ProductPrice money,
  @ProductSalePrice money,
  @ProductInventoryCount int,
  @ProductUpdatedBy int,
  @ProductActive int,
  @ProductID int OUTPUT,
  @Message nvarchar(250) OUTPUT
)
AS BEGIN SET NOCOUNT ON
BEGIN TRANSACTION
 BEGIN TRY
   DECLARE @ProductModelNumber int
   DECLARE @TheModelNumber int
   -- Execute SPROC to get new model number
   EXEC dbo.PD_GetMaxModelNumber @ProductCategory, @ModelNumber = @TheModelNumber OUTPUT
   SELECT @ProductModelNumber = @TheModelNumber
   -- Insert the product
   INSERT INTO PD_StoreProducts
      (ProductCategory, ProductModelNumber, ProductName,
       ProductPrice, ProductSalePrice, ProductInventoryCount,
       ProductLastUpdate, ProductInsertDate, ProductUpdatedBy,
       ProductActive)
       VALUES      
      (@ProductCategory, @ProductModelNumber, @ProductName,
       @ProductPrice, @ProductSalePrice, @ProductInventoryCount,
       GetDate(), GetDate(), @ProductUpdatedBy,
       @ProductActive)
   -- Get the id number of the inserted product
   SELECT @ProductID = @@IDENTITY
   -- Insert the product description
   INSERT INTO PD_StoreProductsDescription
      (ProductDescriptionID, ProductDescription)
   VALUES
      (@ProductID, @ProductDescription)
   -- Insert the product id into the product image table with
   -- empty chars for image data      
   INSERT INTO PD_StoreProductsImages
      (ProductImageID, ProductImageOne, ProductImageTwo,
       ProductImageThree, ProductImageFour)
   VALUES
    (@ProductID, '','','','')
   COMMIT TRANSACTION
   -- Send a success message back to the client
   SET @Message = 'Product Added: Please go to Step 2 to add product image(s).'
 END TRY
 BEGIN CATCH
   -- On error rollback the transaction
   ROLLBACK TRANSACTION
   DECLARE @Msg NVARCHAR(MAX)
   -- Write the error message to the log 
   SELECT @Msg=ERROR_MESSAGE()
   RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG
   -- Send an error message back to the client
   SET @Message = 'Error Occured: The product was not added to the database.<br />
                   If this problem persists please contact technical support.'
   -- Set the returned product id number to 0 to avoid db null issues in code
   SET @ProductID = '0' 
 END CATCH           
END

-- =============================================
-- Author:  Duane Urban
-- Create date: 08/23/2009
-- Description: Retrieves the Max product model number and
-- increments it by 1 or sends a default value if no records exist.
-- =============================================
CREATE PROCEDURE [dbo].[PD_GetMaxModelNumber]
(
 @CategoryID int,
 @ModelNumber int OUTPUT
 )
AS BEGIN SET NOCOUNT ON
 DECLARE @xInt int
 SET @xInt = (SELECT MAX(ProductModelNumber)
              FROM  PD_StoreProducts
              WHERE ProductCategory = @CategoryID)
 IF @xInt IS NULL
  -- A default value if no records exist
  SET @ModelNumber = '1000001'
 ELSE
  -- Increment the model number by 1
  SET @ModelNumber =  @xInt + 1
END