BEGIN TRANSACTION
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK415A8D773E16E17E]') AND parent_object_id = OBJECT_ID(N'[dbo].[Vouchers]'))
ALTER TABLE [dbo].[Vouchers] DROP CONSTRAINT [FK415A8D773E16E17E]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Waiters]') AND type in (N'U'))
DROP TABLE [dbo].[Waiters]
GO
ALTER TABLE dbo.Vouchers DROP COLUMN WaiterID
GO
ALTER PROCEDURE [dbo].[VoucherUpdate]
@Pax int,
@UserID uniqueidentifier,
@TableID uniqueidentifier,
@CustomerID uniqueidentifier,
@Narration nvarchar(255),
@Void bit,
@VoidReason nvarchar(255),
@Printed bit,
@VoucherType int,
@VoucherID uniqueidentifier
AS
BEGIN
	DECLARE @LastEditDate datetime;
	DECLARE @Date datetime;
	DECLARE @BillID int;
	SELECT @Printed = CASE WHEN @Printed = 1 THEN 1 ELSE Printed END, 
		@BillID = BillID, @Date = Date, @LastEditDate = GETDATE() 
	FROM Vouchers WHERE VoucherID = @VoucherID;

	IF @Printed = 1 AND @BillID IS NULL
	BEGIN
        IF @VoucherType IN (1,3)
			SELECT @BillID = ISNULL(MAX(BillID) + 1, 1) FROM Vouchers WHERE VoucherType IN (1, 3);
		ELSE
			SELECT @BillID = ISNULL(MAX(BillID) + 1, 1) FROM Vouchers WHERE VoucherType = @VoucherType;
        IF @BillID % 10000 = 0
            SELECT @BillID = @BillID + 1;
		SELECT @Date = @LastEditDate;
 	END

	UPDATE Vouchers SET Pax = @Pax, UserID = @UserID, TableID = @TableID,
		CustomerID = @CustomerID, Narration = @Narration, Void = @Void,
		VoidReason = @VoidReason, Printed = @Printed, VoucherType = @VoucherType, LastEditDate = @LastEditDate,
		Date = @Date, BillID = @BillID
		WHERE VoucherID = @VoucherID;
END
GO
ALTER PROCEDURE [dbo].[VoucherInsert]
@Pax int,
@UserID uniqueidentifier,
@TableID uniqueidentifier,
@CustomerID uniqueidentifier,
@Narration nvarchar(255),
@Void bit,
@VoidReason nvarchar(255),
@Printed bit,
@VoucherType int,
@VoucherID uniqueidentifier
AS
BEGIN
	DECLARE @Date datetime;
	DECLARE @BillID int;
	DECLARE @KotID int;
    SELECT @Date = GETDATE(), @BillID = null;

    SELECT @KotID = ISNULL(MAX(KotID) + 1, 1) FROM Vouchers;
	IF @Printed = 1
	BEGIN
        IF @VoucherType IN (1,3)
			SELECT @BillID = ISNULL(MAX(BillID) + 1, 1) FROM Vouchers WHERE VoucherType IN (1, 3);
		ELSE
			SELECT @BillID = ISNULL(MAX(BillID) + 1, 1) FROM Vouchers WHERE VoucherType = @VoucherType;
        IF @BillID % 10000 = 0
            SELECT @BillID = @BillID + 1;
 	END
	INSERT INTO Vouchers (VoucherID, Date, Pax, UserID, CreationDate, LastEditDate, BillID, TableID, CustomerID, Narration, Void, VoidReason, Printed, VoucherType, KotID)
		VALUES (@VoucherID, @Date, @Pax, @UserID, @Date, @Date, @BillID, @TableID, @CustomerID, @Narration, @Void, @VoidReason, @Printed, @VoucherType, @KotID);
END
GO
COMMIT