654 lines
50 KiB
Transact-SQL
654 lines
50 KiB
Transact-SQL
USE [AccFF]
|
||
GO
|
||
/****** Object: Table [dbo].[CostCenters] Script Date: 05/21/2009 14:10:15 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[CostCenters](
|
||
[CostCenterID] [uniqueidentifier] NOT NULL,
|
||
[Code] [int] NOT NULL,
|
||
[Name] [nvarchar](100) NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_CostCenters] PRIMARY KEY CLUSTERED
|
||
(
|
||
[CostCenterID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
|
||
CONSTRAINT [IX_CostCenters_Code] UNIQUE NONCLUSTERED
|
||
(
|
||
[Code] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
|
||
CONSTRAINT [IX_CostCenters_Name] UNIQUE NONCLUSTERED
|
||
(
|
||
[Name] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Tax] Script Date: 05/21/2009 14:10:45 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Tax](
|
||
[TaxID] [uniqueidentifier] NOT NULL,
|
||
[Name] [nvarchar](50) NOT NULL,
|
||
[Rate] [decimal](18, 5) NOT NULL,
|
||
[Type] [nchar](1) NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_Tax] PRIMARY KEY CLUSTERED
|
||
(
|
||
[TaxID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[ProductTypes] Script Date: 05/21/2009 14:10:37 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[ProductTypes](
|
||
[ProductTypeID] [uniqueidentifier] NOT NULL,
|
||
[Name] [nvarchar](100) NOT NULL,
|
||
[DiscountLimit] [decimal](18, 5) NOT NULL CONSTRAINT [DF_ProductTypes_DiscountLimit] DEFAULT ((0.1)),
|
||
[IsForSale] [bit] NOT NULL,
|
||
[IsForPurchase] [bit] NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_ProductTypes] PRIMARY KEY CLUSTERED
|
||
(
|
||
[ProductTypeID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Checkout] Script Date: 05/21/2009 14:10:10 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
SET ANSI_PADDING ON
|
||
GO
|
||
CREATE TABLE [dbo].[Checkout](
|
||
[Manager] [varchar](50) NULL,
|
||
[CheckoutTime] [datetime] NULL,
|
||
[FromDate] [datetime] NULL,
|
||
[ToDate] [datetime] NULL,
|
||
[Opening] [varchar](42) NULL,
|
||
[Receipts] [varchar](42) NULL,
|
||
[AdvanceReceived] [varchar](42) NULL,
|
||
[CCReceipts] [varchar](42) NULL,
|
||
[AdvanceAdjusted] [varchar](42) NULL,
|
||
[Payments] [varchar](42) NULL,
|
||
[AdditionalVoids] [varchar](42) NULL,
|
||
[VoidsinSystem] [varchar](42) NULL,
|
||
[DiscountsGiven] [varchar](42) NULL,
|
||
[RatesChanged] [varchar](42) NULL,
|
||
[BillPending] [varchar](42) NULL,
|
||
[NetSales] [varchar](42) NULL,
|
||
[ClosingCash] [varchar](42) NULL,
|
||
[CashDeposited] [varchar](42) NULL,
|
||
[Status] [varchar](42) NULL,
|
||
[Cashier] [uniqueidentifier] NULL
|
||
) ON [PRIMARY]
|
||
GO
|
||
SET ANSI_PADDING OFF
|
||
GO
|
||
/****** Object: Table [dbo].[UserRoles] Script Date: 05/21/2009 14:10:46 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[UserRoles](
|
||
[UserRoleID] [uniqueidentifier] NOT NULL,
|
||
[UserID] [uniqueidentifier] NOT NULL,
|
||
[Role] [nvarchar](50) NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
|
||
(
|
||
[UserRoleID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
|
||
CONSTRAINT [IX_UserRoles] UNIQUE NONCLUSTERED
|
||
(
|
||
[UserID] ASC,
|
||
[Role] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Roles] Script Date: 05/21/2009 14:10:38 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Roles](
|
||
[Role] [nvarchar](50) NOT NULL,
|
||
[Description] [nvarchar](200) NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
|
||
(
|
||
[Role] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Log] Script Date: 05/21/2009 14:10:26 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
SET ANSI_PADDING ON
|
||
GO
|
||
CREATE TABLE [dbo].[Log](
|
||
[Id] [int] IDENTITY(1,1) NOT NULL,
|
||
[Date] [datetime] NOT NULL,
|
||
[Level] [varchar](50) NOT NULL,
|
||
[Logger] [varchar](255) NOT NULL,
|
||
[Message] [varchar](4000) NOT NULL,
|
||
[Exception] [varchar](2000) NULL
|
||
) ON [PRIMARY]
|
||
GO
|
||
SET ANSI_PADDING OFF
|
||
GO
|
||
/****** Object: Table [dbo].[Waiters] Script Date: 05/21/2009 14:10:56 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Waiters](
|
||
[WaiterID] [uniqueidentifier] NOT NULL,
|
||
[Name] [nvarchar](200) NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_Waiters] PRIMARY KEY CLUSTERED
|
||
(
|
||
[WaiterID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Advances] Script Date: 05/21/2009 14:10:04 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Advances](
|
||
[AdvanceID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Advances_AdvanceID] DEFAULT (newid()),
|
||
[Amount] [decimal](18, 2) NOT NULL,
|
||
[Narration] [nvarchar](200) NOT NULL,
|
||
[CashierIn] [uniqueidentifier] NOT NULL,
|
||
[DateIn] [datetime] NOT NULL CONSTRAINT [DF_Advances_DateIN] DEFAULT (getdate()),
|
||
[CashierOut] [uniqueidentifier] NULL,
|
||
[DateOut] [datetime] NULL,
|
||
CONSTRAINT [PK_Advances] PRIMARY KEY CLUSTERED
|
||
(
|
||
[AdvanceID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Users] Script Date: 05/21/2009 14:10:51 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Users](
|
||
[UserID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Users_UserID] DEFAULT (newid()),
|
||
[Name] [nvarchar](100) NOT NULL,
|
||
[Password] [nchar](32) NOT NULL,
|
||
[LockedOut] [bit] NOT NULL,
|
||
[Email] [nvarchar](50) NULL,
|
||
[Approved] [bit] NOT NULL,
|
||
[Question] [nvarchar](50) NULL,
|
||
[Answer] [nvarchar](50) NULL,
|
||
[Comment] [nvarchar](200) NULL,
|
||
[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_Users_CreationDate] DEFAULT (getdate()),
|
||
[LastLoginDate] [datetime] NULL,
|
||
[LastActivityDate] [datetime] NULL,
|
||
[LastPasswordChangedDate] [datetime] NULL,
|
||
[LastLockoutDate] [datetime] NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
|
||
(
|
||
[UserID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
|
||
CONSTRAINT [IX_Users_Name] UNIQUE NONCLUSTERED
|
||
(
|
||
[Name] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[SaleVoucher] Script Date: 05/21/2009 14:10:42 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[SaleVoucher](
|
||
[VoucherID] [uniqueidentifier] NOT NULL,
|
||
[BillID] [nvarchar](10) NOT NULL,
|
||
[TableID] [nvarchar](10) NOT NULL,
|
||
[WaiterID] [uniqueidentifier] NOT NULL,
|
||
[CustomerID] [uniqueidentifier] NOT NULL,
|
||
[Paid] [bit] NOT NULL,
|
||
[Void] [bit] NOT NULL,
|
||
[VoidReason] [nvarchar](50) NULL,
|
||
[Printed] [bit] NOT NULL,
|
||
[Alarm] [datetime] NULL,
|
||
[KotID] [nvarchar](10) NOT NULL,
|
||
[CreditCard] [bit] NOT NULL DEFAULT ((0)),
|
||
[IsImportant] [bit] NOT NULL CONSTRAINT [DF_SaleVoucher_IsImportant] DEFAULT ((0)),
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_SaleVoucher] PRIMARY KEY CLUSTERED
|
||
(
|
||
[VoucherID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Inventory] Script Date: 05/21/2009 14:10:22 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Inventory](
|
||
[InventoryID] [uniqueidentifier] NOT NULL,
|
||
[VoucherID] [uniqueidentifier] NOT NULL,
|
||
[ProductID] [uniqueidentifier] NOT NULL,
|
||
[ComplexProductID] [uniqueidentifier] NULL,
|
||
[Quantity] [decimal](18, 5) NOT NULL,
|
||
[Rate] [decimal](18, 5) NOT NULL,
|
||
[Tax] [decimal](18, 5) NOT NULL,
|
||
[Discount] [decimal](18, 5) NOT NULL CONSTRAINT [DF_Inventory_Discount] DEFAULT ((0)),
|
||
[Amount] AS ((([Quantity]*[Rate])*((1)+[Tax]))*((1)-[Discount])),
|
||
[IsRateFinal] [bit] NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
|
||
(
|
||
[InventoryID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
|
||
CONSTRAINT [IX_Inventory_Unique] UNIQUE NONCLUSTERED
|
||
(
|
||
[VoucherID] ASC,
|
||
[ProductID] ASC,
|
||
[ComplexProductID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Ledgers] Script Date: 05/21/2009 14:10:24 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Ledgers](
|
||
[LedgerID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Ledgers_LedgerID] DEFAULT (newid()),
|
||
[Code] [int] NOT NULL,
|
||
[Name] [nvarchar](100) NOT NULL,
|
||
[Type] [nchar](1) NOT NULL,
|
||
[ParentLedgerID] [uniqueidentifier] NULL,
|
||
[CostCenterID] [uniqueidentifier] NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
|
||
(
|
||
[LedgerID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
|
||
CONSTRAINT [IX_Ledgers_Code] UNIQUE NONCLUSTERED
|
||
(
|
||
[Code] ASC,
|
||
[Type] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Customers] Script Date: 05/21/2009 14:10:18 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Customers](
|
||
[CustomerID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Customers_CustomerID] DEFAULT (newid()),
|
||
[Code] [int] NOT NULL,
|
||
[Name] [nvarchar](50) NOT NULL,
|
||
[LedgerID] [uniqueidentifier] NOT NULL,
|
||
[Address] [nvarchar](200) NOT NULL,
|
||
[Important] [bit] NOT NULL,
|
||
[Phone] [nvarchar](50) NOT NULL,
|
||
[Remarks] [nvarchar](200) NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
|
||
(
|
||
[CustomerID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Products] Script Date: 05/21/2009 14:10:34 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Products](
|
||
[ProductID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Products_ProductID] DEFAULT (newid()),
|
||
[Picture] [image] NULL,
|
||
[Code] [int] NOT NULL,
|
||
[Name] [nvarchar](100) NOT NULL,
|
||
[Units] [nvarchar](50) NOT NULL,
|
||
[ProductTypeID] [uniqueidentifier] NOT NULL,
|
||
[SaleLedgerID] [uniqueidentifier] NULL,
|
||
[SaleTaxID] [uniqueidentifier] NULL,
|
||
[SalePrice] [decimal](18, 5) NOT NULL,
|
||
[PurchaseLedgerID] [uniqueidentifier] NULL,
|
||
[PurchaseTaxID] [uniqueidentifier] NULL,
|
||
[PurchasePrice] [decimal](18, 5) NOT NULL,
|
||
[Discontinued] [bit] NOT NULL,
|
||
[MinimumLevel] [decimal](18, 2) NOT NULL,
|
||
[MaximumLevel] [decimal](18, 2) NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
[SortOrder] [int] NOT NULL CONSTRAINT [DF_Products_SortOrder] DEFAULT ((0)),
|
||
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
|
||
(
|
||
[ProductID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[ComplexProducts] Script Date: 05/21/2009 14:10:13 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[ComplexProducts](
|
||
[ComplexProductID] [uniqueidentifier] NOT NULL,
|
||
[ProductID] [uniqueidentifier] NOT NULL,
|
||
[Code] [int] NOT NULL,
|
||
[Name] [nvarchar](50) NOT NULL,
|
||
[Units] [nvarchar](50) NOT NULL,
|
||
[SalePrice] [decimal](18, 5) NOT NULL,
|
||
[SaleTaxID] [uniqueidentifier] NOT NULL,
|
||
[Quantity] [decimal](18, 5) NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_ComplexProducts] PRIMARY KEY CLUSTERED
|
||
(
|
||
[ComplexProductID] ASC,
|
||
[ProductID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Vouchers] Script Date: 05/21/2009 14:10:54 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Vouchers](
|
||
[VoucherID] [uniqueidentifier] NOT NULL,
|
||
[Code] [int] NOT NULL,
|
||
[Ref] [nvarchar](50) NOT NULL,
|
||
[Date] [datetime] NOT NULL,
|
||
[Narration] [nvarchar](200) NULL,
|
||
[UserID] [uniqueidentifier] NOT NULL,
|
||
[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_Vouchers_CreationDate] DEFAULT (getdate()),
|
||
[LastEditDate] [datetime] NOT NULL CONSTRAINT [DF_Vouchers_LastEditDate] DEFAULT (getdate()),
|
||
[Type] [nchar](1) NOT NULL,
|
||
[timestamp] [timestamp] NOT NULL,
|
||
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
|
||
(
|
||
[VoucherID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
|
||
CONSTRAINT [IX_Vouchers_Code_Type] UNIQUE NONCLUSTERED
|
||
(
|
||
[Code] ASC,
|
||
[Type] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[Payments] Script Date: 05/21/2009 14:10:28 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[Payments](
|
||
[PaymentID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Payments_PaymentID] DEFAULT (newid()),
|
||
[Date] [datetime] NOT NULL,
|
||
[Type] [nvarchar](50) NOT NULL,
|
||
[CashierID] [uniqueidentifier] NOT NULL,
|
||
[Amount] [decimal](18, 5) NOT NULL,
|
||
[Narration] [nvarchar](200) NOT NULL,
|
||
CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED
|
||
(
|
||
[PaymentID] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[SaleVoucher_Update] Script Date: 05/21/2009 14:10:00 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[SaleVoucher_Update]
|
||
(
|
||
@VoucherID 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,
|
||
@CreditCard bit,
|
||
@IsImportant bit,
|
||
@timestamp timestamp OUTPUT
|
||
)
|
||
AS
|
||
BEGIN
|
||
IF @Printed = 1 AND (SELECT Printed FROM SaleVoucher WHERE VoucherID = @VoucherID) = 0
|
||
BEGIN
|
||
SELECT @BillID = ISNULL(CAST(MAX(CAST(REPLACE(BillID, '-', '') AS int)) + 1 AS nvarchar(9)), '010001') FROM SaleVoucher 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
|
||
ELSE
|
||
BEGIN
|
||
SELECT @BillID = BillID FROM SaleVoucher WHERE VoucherID = @VoucherID
|
||
END
|
||
IF @TableID = ''
|
||
BEGIN
|
||
SELECT @TableID = 'C' + CAST(ISNULL(MAX(CAST(SUBSTRING(TableID, 2, 10) AS int)), 0) + 1 AS nvarchar(10)) FROM SaleVoucher WHERE TableID LIKE 'C%' AND Paid = 0 AND Void = 0
|
||
END
|
||
IF (SELECT Printed FROM SaleVoucher WHERE VoucherID = @VoucherID) = 1
|
||
SET @Printed = 1
|
||
UPDATE [dbo].[SaleVoucher] SET
|
||
[BillID] = @BillID,
|
||
[TableID] = @TableID,
|
||
[WaiterID] = @WaiterID,
|
||
[CustomerID] = @CustomerID,
|
||
[Paid] = @Paid,
|
||
[Void] = @Void,
|
||
[VoidReason] = @VoidReason,
|
||
[Printed] = @Printed,
|
||
[Alarm] = @Alarm,
|
||
[CreditCard] = @CreditCard,
|
||
[IsImportant] = @IsImportant
|
||
WHERE VoucherID = @VoucherID
|
||
|
||
UPDATE Vouchers SET LastEditDate = GETDATE() WHERE VoucherID = @VoucherID
|
||
|
||
SELECT @timestamp = timestamp, @KotID = KotID FROM [dbo].[SaleVoucher] WHERE VoucherID = @VoucherID
|
||
END
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[spCheckout] Script Date: 05/21/2009 14:10:01 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[spCheckout]
|
||
@Manager VarChar,
|
||
@Cashier uniqueidentifier,
|
||
@FromDate DateTime,
|
||
@ToDate DateTime,
|
||
@Opening Varchar(42),
|
||
@Receipts Varchar(42),
|
||
@AdvanceReceived Varchar(42),
|
||
@CCReceipts Varchar(42),
|
||
@AdvanceAdjusted Varchar(42),
|
||
@Payments Varchar(42),
|
||
@AdditionalVoids Varchar(42),
|
||
@VoidsinSystem Varchar(42),
|
||
@DiscountsGiven Varchar(42),
|
||
@RatesChanged Varchar(42),
|
||
@BillPending Varchar(42),
|
||
@NetSales Varchar(42),
|
||
@ClosingCash Varchar(42),
|
||
@CashDeposited Varchar(42),
|
||
@Status Varchar(42)
|
||
AS
|
||
begin
|
||
DELETE from Checkout WHERE Cashier = @Cashier AND DATEDIFF(day, CheckoutTime, GetDate()) <=0
|
||
Insert into Checkout (CheckoutTime, Manager, Cashier, FromDate, ToDate, Opening, Receipts, AdvanceReceived, CCReceipts, AdvanceAdjusted, Payments, AdditionalVoids, VoidsinSystem, DiscountsGiven, RatesChanged, BillPending,
|
||
NetSales,ClosingCash,CashDeposited,Status)Values(GetDate(), @Manager, @Cashier,@FromDate,@ToDate,@Opening,@Receipts,@AdvanceReceived,@CCReceipts,
|
||
@AdvanceAdjusted,@Payments,@AdditionalVoids,@VoidsinSystem,@DiscountsGiven,@RatesChanged,@BillPending,
|
||
@NetSales,@ClosingCash,@CashDeposited,@Status)
|
||
end
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[SaleVoucher_Insert] Script Date: 05/21/2009 14:09:59 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[SaleVoucher_Insert]
|
||
(
|
||
@VoucherID uniqueidentifier,
|
||
@BillID nvarchar(10) OUTPUT,
|
||
@TableID nvarchar(10),
|
||
@WaiterID uniqueidentifier,
|
||
@CustomerID uniqueidentifier,
|
||
@Paid bit,
|
||
@Void bit,
|
||
@VoidReason nvarchar(50),
|
||
@Printed bit,
|
||
@Alarm datetime,
|
||
@KotID nvarchar(10) OUTPUT,
|
||
@CreditCard bit,
|
||
@IsImportant bit,
|
||
@timestamp timestamp OUTPUT
|
||
)
|
||
AS
|
||
BEGIN
|
||
SELECT @KotID = ISNULL('K-' + CAST(MAX(CAST(SUBSTRING(KotID, 3,8) AS int)) + 1 AS nvarchar(8)), 'K-1') FROM [dbo].[SaleVoucher]
|
||
IF @Printed = 1
|
||
BEGIN
|
||
SELECT @BillID = ISNULL(CAST(MAX(CAST(REPLACE(BillID, '-', '') AS int)) + 1 AS nvarchar(9)), '010001') FROM SaleVoucher 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
|
||
ELSE
|
||
BEGIN
|
||
SET @BillID = @KotID
|
||
END
|
||
IF @TableID = ''
|
||
BEGIN
|
||
SELECT @TableID = 'C' + CAST(ISNULL(MAX(CAST(SUBSTRING(TableID, 2, 10) AS int)), 0) + 1 AS nvarchar(10)) FROM SaleVoucher WHERE TableID LIKE 'C%' AND Paid = 0 AND Void = 0
|
||
END
|
||
INSERT INTO [dbo].[SaleVoucher] ([VoucherID], [BillID], [TableID], [WaiterID], [CustomerID], [Paid], [Void], [VoidReason], [Printed], [Alarm], [KotID], [CreditCard] ,[IsImportant])
|
||
VALUES (@VoucherID, @BillID, @TableID, @WaiterID, @CustomerID, @Paid, @Void, @VoidReason, @Printed, @Alarm, @KotID, @CreditCard, @IsImportant)
|
||
SELECT @timestamp = timestamp FROM [dbo].[SaleVoucher] WHERE VoucherID = @VoucherID
|
||
END
|
||
GO
|
||
/****** Object: ForeignKey [FK_ComplexProducts_Products] Script Date: 05/21/2009 14:10:13 ******/
|
||
ALTER TABLE [dbo].[ComplexProducts] WITH CHECK ADD CONSTRAINT [FK_ComplexProducts_Products] FOREIGN KEY([ProductID])
|
||
REFERENCES [dbo].[Products] ([ProductID])
|
||
GO
|
||
ALTER TABLE [dbo].[ComplexProducts] CHECK CONSTRAINT [FK_ComplexProducts_Products]
|
||
GO
|
||
/****** Object: ForeignKey [FK_ComplexProducts_Tax] Script Date: 05/21/2009 14:10:13 ******/
|
||
ALTER TABLE [dbo].[ComplexProducts] WITH CHECK ADD CONSTRAINT [FK_ComplexProducts_Tax] FOREIGN KEY([SaleTaxID])
|
||
REFERENCES [dbo].[Tax] ([TaxID])
|
||
GO
|
||
ALTER TABLE [dbo].[ComplexProducts] CHECK CONSTRAINT [FK_ComplexProducts_Tax]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Customers_Ledgers] Script Date: 05/21/2009 14:10:18 ******/
|
||
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [FK_Customers_Ledgers] FOREIGN KEY([LedgerID])
|
||
REFERENCES [dbo].[Ledgers] ([LedgerID])
|
||
GO
|
||
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [FK_Customers_Ledgers]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Inventory_Products] Script Date: 05/21/2009 14:10:22 ******/
|
||
ALTER TABLE [dbo].[Inventory] WITH CHECK ADD CONSTRAINT [FK_Inventory_Products] FOREIGN KEY([ProductID])
|
||
REFERENCES [dbo].[Products] ([ProductID])
|
||
GO
|
||
ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_Inventory_Products]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Inventory_Vouchers] Script Date: 05/21/2009 14:10:22 ******/
|
||
ALTER TABLE [dbo].[Inventory] WITH CHECK ADD CONSTRAINT [FK_Inventory_Vouchers] FOREIGN KEY([VoucherID])
|
||
REFERENCES [dbo].[Vouchers] ([VoucherID])
|
||
GO
|
||
ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_Inventory_Vouchers]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Ledgers_CostCenters] Script Date: 05/21/2009 14:10:25 ******/
|
||
ALTER TABLE [dbo].[Ledgers] WITH CHECK ADD CONSTRAINT [FK_Ledgers_CostCenters] FOREIGN KEY([CostCenterID])
|
||
REFERENCES [dbo].[CostCenters] ([CostCenterID])
|
||
GO
|
||
ALTER TABLE [dbo].[Ledgers] CHECK CONSTRAINT [FK_Ledgers_CostCenters]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Ledgers_Ledgers] Script Date: 05/21/2009 14:10:25 ******/
|
||
ALTER TABLE [dbo].[Ledgers] WITH CHECK ADD CONSTRAINT [FK_Ledgers_Ledgers] FOREIGN KEY([ParentLedgerID])
|
||
REFERENCES [dbo].[Ledgers] ([LedgerID])
|
||
GO
|
||
ALTER TABLE [dbo].[Ledgers] CHECK CONSTRAINT [FK_Ledgers_Ledgers]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Payment_Users] Script Date: 05/21/2009 14:10:29 ******/
|
||
ALTER TABLE [dbo].[Payments] WITH CHECK ADD CONSTRAINT [FK_Payment_Users] FOREIGN KEY([CashierID])
|
||
REFERENCES [dbo].[Users] ([UserID])
|
||
GO
|
||
ALTER TABLE [dbo].[Payments] CHECK CONSTRAINT [FK_Payment_Users]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Products_Ledgers_Purchase] Script Date: 05/21/2009 14:10:34 ******/
|
||
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Ledgers_Purchase] FOREIGN KEY([PurchaseLedgerID])
|
||
REFERENCES [dbo].[Ledgers] ([LedgerID])
|
||
GO
|
||
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Ledgers_Purchase]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Products_Ledgers_Sale] Script Date: 05/21/2009 14:10:34 ******/
|
||
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Ledgers_Sale] FOREIGN KEY([SaleLedgerID])
|
||
REFERENCES [dbo].[Ledgers] ([LedgerID])
|
||
GO
|
||
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Ledgers_Sale]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Products_ProductTypes] Script Date: 05/21/2009 14:10:34 ******/
|
||
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_ProductTypes] FOREIGN KEY([ProductTypeID])
|
||
REFERENCES [dbo].[ProductTypes] ([ProductTypeID])
|
||
GO
|
||
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProductTypes]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Products_Tax_Purchase] Script Date: 05/21/2009 14:10:35 ******/
|
||
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Tax_Purchase] FOREIGN KEY([PurchaseTaxID])
|
||
REFERENCES [dbo].[Tax] ([TaxID])
|
||
GO
|
||
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Tax_Purchase]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Products_Tax_Sale] Script Date: 05/21/2009 14:10:35 ******/
|
||
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Tax_Sale] FOREIGN KEY([SaleTaxID])
|
||
REFERENCES [dbo].[Tax] ([TaxID])
|
||
GO
|
||
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Tax_Sale]
|
||
GO
|
||
/****** Object: ForeignKey [FK_SaleVoucher_Customers] Script Date: 05/21/2009 14:10:43 ******/
|
||
ALTER TABLE [dbo].[SaleVoucher] WITH CHECK ADD CONSTRAINT [FK_SaleVoucher_Customers] FOREIGN KEY([CustomerID])
|
||
REFERENCES [dbo].[Customers] ([CustomerID])
|
||
GO
|
||
ALTER TABLE [dbo].[SaleVoucher] CHECK CONSTRAINT [FK_SaleVoucher_Customers]
|
||
GO
|
||
/****** Object: ForeignKey [FK_SaleVoucher_Vouchers] Script Date: 05/21/2009 14:10:43 ******/
|
||
ALTER TABLE [dbo].[SaleVoucher] WITH CHECK ADD CONSTRAINT [FK_SaleVoucher_Vouchers] FOREIGN KEY([VoucherID])
|
||
REFERENCES [dbo].[Vouchers] ([VoucherID])
|
||
GO
|
||
ALTER TABLE [dbo].[SaleVoucher] CHECK CONSTRAINT [FK_SaleVoucher_Vouchers]
|
||
GO
|
||
/****** Object: ForeignKey [FK_SaleVoucher_Waiters] Script Date: 05/21/2009 14:10:43 ******/
|
||
ALTER TABLE [dbo].[SaleVoucher] WITH CHECK ADD CONSTRAINT [FK_SaleVoucher_Waiters] FOREIGN KEY([WaiterID])
|
||
REFERENCES [dbo].[Waiters] ([WaiterID])
|
||
GO
|
||
ALTER TABLE [dbo].[SaleVoucher] CHECK CONSTRAINT [FK_SaleVoucher_Waiters]
|
||
GO
|
||
/****** Object: ForeignKey [FK_Vouchers_Users_UserID] Script Date: 05/21/2009 14:10:55 ******/
|
||
ALTER TABLE [dbo].[Vouchers] WITH CHECK ADD CONSTRAINT [FK_Vouchers_Users_UserID] FOREIGN KEY([UserID])
|
||
REFERENCES [dbo].[Users] ([UserID])
|
||
GO
|
||
ALTER TABLE [dbo].[Vouchers] CHECK CONSTRAINT [FK_Vouchers_Users_UserID]
|
||
GO
|