USE [testdb]
GO
/****** Object:  StoredProcedure [dbo].[AddInvoiceProduct]    Script Date: 02/09/2017 23:09:47 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[AddInvoiceProduct](@Qty integer, @StockNumber varchar(15),
                                           @ProductTitle varchar(75), @CoverPrice money)
AS

DECLARE @ProductCount int
DECLARE @CurrentQty int

BEGIN

  SELECT @CurrentQty = Quantity
    FROM InvoiceProduct
   WHERE StockNumber = @StockNumber
     AND DateProcessed IS NULL
      
  IF @CurrentQty IS NULL
     BEGIN
 	 INSERT InvoiceProduct
	   ( Quantity, StockNumber, ProductTitle, CoverPrice )
	 VALUES
	  ( @Qty, @StockNumber, @ProductTitle, @CoverPrice )
     END
  ELSE
     BEGIN
     UPDATE InvoiceProduct
        SET Quantity = @CurrentQty + @Qty
      WHERE StockNumber = @StockNumber
        AND DateProcessed IS NULL
     END

  RETURN

END

GO
/****** Object:  StoredProcedure [dbo].[CreateNewPurchaseOrder]    Script Date: 02/09/2017 23:09:48 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[CreateNewPurchaseOrder](@NewPurchaseOrderID integer OUTPUT,
                                               @StoreID integer OUTPUT)

AS
DECLARE @StoreName nvarchar(30)
DECLARE @StoreAddress1 nvarchar(30)
DECLARE @StoreAddress2 nvarchar(30)
DECLARE @StoreCity nvarchar(30)
DECLARE @StoreZip nvarchar(15)
DECLARE @StoreState nvarchar(20)
DECLARE @StoreCountry nvarchar(20)
DECLARE @StorePhone nvarchar(14)
DECLARE @StoreFax nvarchar(14)
DECLARE @NextPONumber int
DECLARE @POType int
DECLARE @ShipToAddress nvarchar(256)
DECLARE @CurrentDate datetime
DECLARE @SupplierID int
DECLARE @CurrencyID int
DECLARE @ExchangeRate int

BEGIN

  SET @CurrencyID = 0
  SET @ExchangeRate = 1
  SET @SupplierID = 1
  SET @POType = 2
  SELECT @CurrentDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
  
  SELECT @StoreID = StoreID, @StoreName =StoreName, @StoreAddress1 = StoreAddress1,
         @StoreAddress2 = StoreAddress2, @StoreCity = StoreCity, 
         @StoreZip = StoreZip, @StoreState = StoreState, @StoreCountry = StoreCountry,
         @StorePhone = StorePhone, @StoreFax = StoreFax 
    FROM [gccnp].dbo.Configuration
    
  SET @ShipToAddress = @StoreName + CHAR(13) + CHAR(10)
  SET @ShipToAddress = @ShipToAddress + @StoreAddress1 + CHAR(13) + CHAR(10)
  IF @StoreAddress2 IS NOT NULL
     BEGIN
     SET @ShipToAddress = @ShipToAddress + @StoreAddress2 + CHAR(13) + CHAR(10)
     END
  SET @ShipToAddress = @ShipToAddress + @StoreCity + ', ' + @StoreState + ' '
  SET @ShipToAddress = @ShipToAddress + @StoreZip + CHAR(13) + CHAR(10)
  SET @ShipToAddress = @ShipToAddress + 'Phone: ' + @StorePhone + CHAR(13) + CHAR(10)
  IF @StoreFax IS NOT NULL
     BEGIN
     SET @ShipToAddress = @ShipToAddress + 'Fax: ' + @StoreFax + CHAR(13) + CHAR(10)
     END

  SELECT @NextPONumber = ISNULL(MAX(CAST(RIGHT(PONumber, 7) AS INT)), 0) + 1 
    FROM [gccnp].dbo.PurchaseOrder
   WHERE ISNUMERIC(PONumber) <> 0

  INSERT INTO [gccnp].dbo.PurchaseOrder
  ( StoreID, POType, PONumber, WorksheetID, POTitle, [To], ShipTo, DateCreated, 
    RequiredDate, Requisitioner, Remarks, ShipVia, FOBPoint, Terms, Freight, ConfirmingTo,
    TaxRate, Shipping, CurrentOtherFees, CurrentShipping, OtherFees,
    CostDistributionMethod, EstOtherFees, EstShipping, 
    ParentPOId, OriginPOID, [Status],
    SupplierID, OtherStoreID, otherPOID, CurrencyID, 
    ExchangeRate, InventoryLocation,
    IsPlaced, LastUpdated, MasterPO)
  VALUES
  ( @StoreID, @POType, @NextPONumber, 0, N'', N'', @ShipToAddress, GETDATE(),
    @CurrentDate, N'', N'',N'',N'',N'',N'',N'',
    0, CONVERT(money, 0), CONVERT(money, 0), CONVERT(money, 0), CONVERT(money, 0),
    0, CONVERT(money, 0),CONVERT(money, 0),
    0, 0, 2, 
    @SupplierID, 0, 0, @CurrencyID,
    @ExchangeRate, 0, 
    0, GETDATE(), '0')  

   SELECT @NewPurchaseOrderID = ID FROM [gccnp].dbo.PurchaseOrder Where PONumber = CONVERT(nvarchar(20), @NextPONumber)
  RETURN
  
END

GO
/****** Object:  StoredProcedure [dbo].[ProcessDiamondInvoice]    Script Date: 02/09/2017 23:09:48 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[ProcessDiamondInvoice]

AS

DECLARE @Qty int
DECLARE @StockNumber varchar(15)
DECLARE @NewPOID int
DECLARE @StoreID int
DECLARE @ItemProcessed bit

BEGIN

  DECLARE InvoiceCursor CURSOR FOR
    SELECT Quantity, StockNumber
      FROM InvoiceProduct
    WHERE DateProcessed IS NULL
    ORDER BY StockNumber
    FOR UPDATE OF DateProcessed
    
    OPEN InvoiceCursor
    
     FETCH NEXT FROM InvoiceCursor
         INTO @Qty, @StockNumber
    
    IF @@fetch_status = 0
       BEGIN
       EXEC CreateNewPurchaseOrder @NewPOID OUTPUT,  @StoreID OUTPUT
       END

    WHILE (@@fetch_status = 0)
      BEGIN
        
      EXEC UpdateProductQuantity @Qty, @StockNumber, @StoreID, @NewPOID, @ItemProcessed OUTPUT
    
      IF @ItemProcessed = 1
		BEGIN
		  UPDATE InvoiceProduct
			 SET DateProcessed = getdate()
		   WHERE CURRENT OF InvoiceCursor   
		END
		
      FETCH NEXT FROM InvoiceCursor
       INTO @Qty, @StockNumber

      END

  CLOSE InvoiceCursor

  DEALLOCATE InvoiceCursor 
    
  RETURN
  
END

GO
/****** Object:  StoredProcedure [dbo].[UpdateProductQuantity]    Script Date: 02/09/2017 23:09:48 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[UpdateProductQuantity](@Qty integer, @StockNumber varchar(15),
                                              @StoreID int, @PurchaseOrderID int,
                                              @ItemProcessed bit OUTPUT)

AS
DECLARE @ItemID int
DECLARE @ItemDescription nvarchar(30)
DECLARE @ItemCost money
DECLARE @QtyOnHand int
DECLARE @PurchaseOrderEntryID int

BEGIN

  SELECT @ItemID = I.ID, @ItemDescription = [Description], @ItemCost = I.Cost,
         @QtyOnHand = I.Quantity
    FROM [gccnp].dbo.Item I
   INNER JOIN [gccnp].dbo.SupplierList SL
   ON I.ID = SL.ItemID
  WHERE SL.ReorderNumber = @StockNumber
  
  IF @ItemID IS NULL
     BEGIN
     SET @ItemProcessed = 0
	 END
  ELSE
     BEGIN
     SET @ItemProcessed = 1
     	 	
	 INSERT [gccnp].dbo.PurchaseOrderEntry
		(
		 PurchaseOrderID, StoreID, ItemID, ItemDescription, QuantityOrdered,
		 QuantityReceived, QuantityReceivedToDate, OrderNumber, Price, TaxRate, 
		 InventoryOfflineID, LastUpdated
		)
	  VALUES
		(
		 @PurchaseOrderID, @StoreID, @ItemID, @ItemDescription, @Qty,
		 0, @Qty, N'', @ItemCost, 0, 0, GETDATE()
		)
	  
	  SET @PurchaseOrderEntryID = SCOPE_IDENTITY() 

	  UPDATE [gccnp].dbo.Item
		 SET Item.Quantity = @QtyOnHand + @Qty,
			 Item.LastReceived = GETDATE(),
			 Item.LastUpdated = GETDATE()
	   WHERE Item.ID = @ItemID
	     
      INSERT [gccnp].dbo.InventoryTransferLog
		(ReferenceID, ReferenceEntryID, ItemID, DetailID, Quantity,
		 DateTransferred, StoreID, CashierID, [Type], ReasonCodeID, Cost)
	  VALUES 
		(@PurchaseOrderID, @PurchaseOrderEntryID, @ItemID, 0, @Qty,
		 GetDate(), @StoreID, 1, 1, 0, @ItemCost)
    END
    
  RETURN
  
END
