narsil/Tanshu.Accounts.PointOfSale/Script.txt

CREATE TABLE [dbo].[ComplexProducts](
	[ComplexProductID] [uniqueidentifier] NOT NULL,
	[ProductID] [uniqueidentifier] NOT NULL,
	[ChildID] [uniqueidentifier] NOT NULL,
	[Quantity] [decimal](18, 5) NOT NULL,
	[timestamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_ComplexProducts] PRIMARY KEY CLUSTERED 
(
	[ComplexProductID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]






CREATE TABLE [dbo].[ComplexProducts_tracking](
	[ComplexProductID] [uniqueidentifier] NOT NULL,
	[sync_row_is_tombstone] [int] NULL DEFAULT ((0)),
	[sync_row_timestamp] [timestamp] NOT NULL,
	[sync_update_peer_key] [int] NULL DEFAULT ((0)),
	[sync_update_peer_timestamp] [bigint] NULL,
	[sync_create_peer_key] [int] NULL DEFAULT ((0)),
	[sync_create_peer_timestamp] [bigint] NULL,
	[last_change_datetime] [datetime] NULL DEFAULT (getdate()),
PRIMARY KEY CLUSTERED 
(
	[ComplexProductID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Alter table Products add [IsComplex] [bit] NOT NULL DEFAULT((0))

---------------- Before 18 Jul 2008 ---------------------

Insert into Roles (Role, Description, CompanyID) Values('Master/Products','Allow user to access product form','7860acfb-e03e-4af5-8a5f-91186921da2d')


CREATE TABLE [dbo].[ProductRates](
	[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ProductRates_Id]  DEFAULT (newid()),
	[ProductID] [uniqueidentifier] NULL,
	[NewRate] [decimal](18, 5) NULL,
	[UserID] [uniqueidentifier] NULL,
	[Date] [datetime] NULL,
	[timestamp] [timestamp] NULL,
 CONSTRAINT [PK_ProductRates] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[ProductRates_tracking](
	[ID] [uniqueidentifier] NOT NULL,
	[sync_row_is_tombstone] [int] NULL DEFAULT ((0)),
	[sync_row_timestamp] [timestamp] NOT NULL,
	[sync_update_peer_key] [int] NULL DEFAULT ((0)),
	[sync_update_peer_timestamp] [bigint] NULL,
	[sync_create_peer_key] [int] NULL DEFAULT ((0)),
	[sync_create_peer_timestamp] [bigint] NULL,
	[last_change_datetime] [datetime] NULL DEFAULT (getdate()),
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]




Alter Table TransactionSale Add [CreditCard] [bit] NOT NULL CONSTRAINT [DF_TransactionSale_CreditCard]  DEFAULT ((0))






















-----------Paid Update--------------------




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[TransactionSale_Update]
(
	@TransactionID uniqueidentifier,
	@BillID nvarchar(10) OUTPUT,
	@TableID nvarchar(10),
	@WaiterID uniqueidentifier,
	@CustomerID uniqueidentifier,
	@Paid bit,
	@Void bit,
	@VoidReason nvarchar(50),
	@Printed bit OUTPUT,
	@Alarm datetime,
	@KotID nvarchar(10) OUTPUT,
	@CompanyID uniqueidentifier,
	@timestamp timestamp OUTPUT
 )
AS
BEGIN
	IF @Printed = 1 AND (SELECT Printed FROM TransactionSale WHERE TransactionID = @TransactionID) = 0 
	BEGIN
		SELECT @BillID = ISNULL(CAST(MAX(CAST(REPLACE(BillID, '-', '') AS int)) + 1 AS nvarchar(9)), '010001') FROM TransactionSale WHERE BillID LIKE '__-____'
		IF LEN(@BillID) = 5
		SET @BillID = '0' + @BillID
		SET @BillID = SUBSTRING(@BillID, 1, 2) + '-' + SUBSTRING(@BillID, 3, 7) 
		IF SUBSTRING(@BillID,3,7) = '-0000'
		SET @BillID = SUBSTRING(@BillID, 1, 2) + '-0001'
	END
	IF (SELECT Printed FROM TransactionSale WHERE TransactionID = @TransactionID) = 1
		SET @Printed = 1
	UPDATE [dbo].[TransactionSale] SET 
		[BillID] = @BillID, 
		[TableID] = @TableID, 
		[WaiterID] = @WaiterID, 
		[CustomerID] = @CustomerID, 
		[Paid] = @Paid, 
		[Void] = @Void, 
		[VoidReason] = @VoidReason, 
		[Printed] = @Printed, 
		[Alarm] = @Alarm, 
		[KotID] = @KotID,
		[CompanyID] = @CompanyID 
		WHERE TransactionID = @TransactionID

Update BasicTransactions Set LastEditDate=GetDate() WHERE TransactionID = @TransactionID

	SELECT @timestamp = timestamp FROM [dbo].[TransactionSale] WHERE TransactionID = @TransactionID
END