Restaurant POS : POSmen
 
Search this site

A Stored Procedure Sample for MS SQL Server 2005

USE [POSmen]
GO

/****** Object:  StoredProcedure [dbo].[sp_rCheck]    Script Date: 02/16/2009 21:38:48 ******/
-- Print Guest Check Simply.
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE sp_rCheck
 @Ordno numeric(9), @Indi varchar(1)
AS

DECLARE @Msg nvarchar(50), @DcAmt money, @Prt char(1), @TipRate numeric, @TenderName varchar(50)

IF OBJECT_ID('zCheck') IS NOT NULL
BEGIN
 DROP TABLE zCheck
END

SELECT Invoice.Ordno, Invoice.TableID, Invoice.AliasID, Invoice.InvDate,
  Invoice.Taxable, [Order].Seq, [Order].MenuID, [Order].Qty,
  [Order].Price, [Order].DC, [Order].DCx, [Order].[Time],
        [Order].Name2, Invoice.UserID, Invoice.Station, Invoice.[Server], [Order].Description,
  [Order].Price * [Order].Qty AS Amount, Invoice.Tax, Invoice.Printed, Invoice.Code,
        '>>  Ticket #: ' + CONVERT(varchar, Invoice.Ticketno) + '  <<' AS MyOrdno,

  CASE WHEN LEN(RTRIM([Order].Description) + RTRIM([Order].Name2)) <= 22 THEN
   RTRIM([Order].Description) + ' ' + RTRIM([Order].Name2)
  ELSE
   RTRIM([Order].Description) + CHAR(13) + CHAR(10) + RTRIM([Order].Name2)
  END AS MenuName,  Isnull(@Msg, '') + Convert(varchar, Code) AS Msg,
  Invoice.Guests, @DcAmt AS DcAmt, @Prt AS Prt, @TipRate AS TipRate, @TenderName AS TenderName
INTO zCheck
FROM    Invoice INNER JOIN
        [Order] ON Invoice.Ordno = [Order].Ordno
WHERE   Invoice.Ordno = @Ordno
 
SELECT @DcAmt = Sum(DCx) FROM zCheck
SELECT @TipRate = TipRate FROM Company

UPDATE zCheck
SET  DcAmt = @DcAmt, TipRate = @TipRate

IF @Indi='N'
BEGIN

SELECT  MenuID, Sum(Qty) AS Qty, Price, Min(Seq) as Seq
INTO  #T
FROM  zCheck
GROUP BY MenuID, Price
ORDER BY MenuID, Price

UPDATE  zCheck
SET   Qty = #T.Qty, Prt = 'X'
FROM  zCheck INNER JOIN #T
ON   #T.MenuID = zCheck.MenuID and #T.Price = zCheck.Price
WHERE  #T.Seq = zCheck.Seq

DELETE  zCheck
FROM  zCheck
WHERE  Prt Is NULL

UPDATE  zCheck
SET   DCx = DCx * Qty, Amount = Price * Qty

END

UPDATE  zCheck
SET   Msg = RTrim(Payment.Description) + ' ' + Isnull(Convert(varchar, Code),'')
FROM  zCheck INNER JOIN Payment
ON   Payment.Ordno = zCheck.Ordno
WHERE  Payment.Ordno = @Ordno
    AND RTRIM(Payment.Description) = 'C O M P L I M E N T A R Y'

UPDATE  zCheck
SET   TenderName = Payment.TenderName
FROM  zCheck INNER JOIN Payment
ON   Payment.Ordno = zCheck.Ordno
WHERE  zCheck.Ordno = @Ordno