Database

Vouchcast Database Tables & Default Table data

ver. 4.1.3.0

Table of Contents

  1. Schema
    1. Tables
      1. Organization
      2. Views
      3. Triggers
      4. Stored Procedures
  2. Default Data
    1. Organizational Data
    2. Security data
    3. Income data
    4. Expense & Accounts data
    5. Reporting data

Schema

Tables

organizational tables

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VCAS_council](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](max) NOT NULL,
    [location] [nvarchar](max) NOT NULL,
    [vendor_Id] [nvarchar](max) NOT NULL,
    [app_name] [nvarchar](max) NULL,
    [receipt_logo] [nvarchar](max) NULL,
    [receipt_footer] [nvarchar](max) NULL,
    [receipt_header] [nvarchar](max) NULL,
    [app_cover] [nvarchar](max) NULL,
    [twilio_SID] [nvarchar](max) NULL,
    [twilio_TOKEN] [nvarchar](max) NULL,
    [twilio_NUMBER] [nvarchar](max) NULL,
    [twilio_XML] [nvarchar](max) NULL,
 CONSTRAINT [PK_VCAS_council] PRIMARY KEY CLUSTERED 
([Id] 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

Default Data

REQUIRED

Organizational data

-- Location
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_council] ON 
INSERT [dbo].[VCAS_council] ([Id], [name], [location], [vendor_Id], [app_name], [receipt_logo], [receipt_footer], [receipt_header], [app_cover], [twilio_SID], [twilio_TOKEN], [twilio_NUMBER], [twilio_XML]) VALUES (1, N'Demo Ltd.', N'Roseau, Dominica', N'VC-0009', N'VOUCHCAST', N'/Content/Uploads/demo logo2317050097.png', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[VCAS_council] OFF
GO
--  Area
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_district] ON 
INSERT [dbo].[VCAS_district] ([Id], [name], [FK_location], [FK_usersId]) VALUES (1, N'Demo Ltd.', 1, 1)
SET IDENTITY_INSERT [dbo].[VCAS_district] OFF
GO

Security data

--  Admin User
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_users] ON 
INSERT [dbo].[VCAS_users] ([Id], [fullName], [userName], [email], [password], [FK_userRolesId]) VALUES (1, N'system administrator', N'system', N'system@vouchcast.com', N'$2a$12$wHF40eYzHi29l/f6/MQuQOc.7sZ8WbXRX.URC69SpAiZ6G51b1exK', 1)
SET IDENTITY_INSERT [dbo].[VCAS_users] OFF
GO
--  User Roles
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_REF_userRoles] ON 
INSERT [dbo].[VCAS_REF_userRoles] ([Id], [name]) VALUES (1, N'admin')
INSERT [dbo].[VCAS_REF_userRoles] ([Id], [name]) VALUES (2, N'cashier')
INSERT [dbo].[VCAS_REF_userRoles] ([Id], [name]) VALUES (3, N'approver')
SET IDENTITY_INSERT [dbo].[VCAS_REF_userRoles] OFF
GO
--  User Session
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_session] ON 
INSERT [dbo].[VCAS_session] ([Id], [username], [role], [location]) VALUES (1, N'demo', 1, 1)
SET IDENTITY_INSERT [dbo].[VCAS_session] OFF
GO

Income data

--  Sales item
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_REF_items] ON 
INSERT [dbo].[VCAS_REF_items] ([Id], [name], [desc]) VALUES (1, N'Sweat Shirt', N'Size: lg')
INSERT [dbo].[VCAS_REF_items] ([Id], [name], [desc]) VALUES (2, N'Denim Pants', N'Size: lg')
INSERT [dbo].[VCAS_REF_items] ([Id], [name], [desc]) VALUES (3, N'Clothing', N'Items for orders')
SET IDENTITY_INSERT [dbo].[VCAS_REF_items] OFF
GO
--  Sales item by location
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_REF_items_location] ON 
INSERT [dbo].[VCAS_REF_items_location] ([Id], [FK_councilId], [FK_REF_itemsId]) VALUES (1, 1, 1)
INSERT [dbo].[VCAS_REF_items_location] ([Id], [FK_councilId], [FK_REF_itemsId]) VALUES (2, 1, 2)
INSERT [dbo].[VCAS_REF_items_location] ([Id], [FK_councilId], [FK_REF_itemsId]) VALUES (3, 1, 3)
SET IDENTITY_INSERT [dbo].[VCAS_REF_items_location] OFF
GO
--  Sales inventory
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_inventory] ON 
INSERT [dbo].[VCAS_inventory] ([Id], [name], [desc], [dateModified], [partNumber], [label], [startStock], [currentStock], [quantity], , [unit], [unitPrice], [sellingPrice], [image], [FK_REF_itemsId], [FK_location]) VALUES (1, N'T-Shirts', N'Custom prints', CAST(N'2023-11-14T20:22:00.000' AS DateTime), N'T-00001', NULL, 20, 100, 100, NULL, N'lg', 30, 35, NULL, 1, 1)
SET IDENTITY_INSERT [dbo].[VCAS_inventory] OFF
GO
--  Customer
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_customer] ON 
INSERT [dbo].[VCAS_customer] ([Id], [firstName], [lastName], [address], [state], [phone], [email], [FK_Location]) VALUES (3, N'Guest', N'Customer', N'', N'', N'555-5555', N'', 1)
SET IDENTITY_INSERT [dbo].[VCAS_customer] OFF
GO

Expense & Accounts data

-- Bank Details
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_REF_bank_details] ON 
INSERT [dbo].[VCAS_REF_bank_details] ([Id], [name]) VALUES (1, N'N/A')
INSERT [dbo].[VCAS_REF_bank_details] ([Id], [name]) VALUES (2, N'National Bank of Dominica')
INSERT [dbo].[VCAS_REF_bank_details] ([Id], [name]) VALUES (3, N'National Credit Union')
SET IDENTITY_INSERT [dbo].[VCAS_REF_bank_details] OFF
GO
-- Payment Type
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_REF_payment_type] ON 
INSERT [dbo].[VCAS_REF_payment_type] ([Id], [name]) VALUES (1, N'Cash')
INSERT [dbo].[VCAS_REF_payment_type] ([Id], [name]) VALUES (2, N'Check')
INSERT [dbo].[VCAS_REF_payment_type] ([Id], [name]) VALUES (3, N'Debit Card')
INSERT [dbo].[VCAS_REF_payment_type] ([Id], [name]) VALUES (4, N'Credit Card')
INSERT [dbo].[VCAS_REF_payment_type] ([Id], [name]) VALUES (5, N'Bank Transfer')
SET IDENTITY_INSERT [dbo].[VCAS_REF_payment_type] OFF
GO
-- Expense & Expense by location
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_expenses] ON 
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (1, N'Electricity Bill', N'Business Expense (Monthly)')
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (2, N'Internet Bill', N'Business Expense (Monthly)')
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (3, N'Salaries', N'Business Expense (Monthly)')
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (4, N'Fuel', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (5, N'Vehicle Expenses', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (6, N'Vehicle Repairs & Maintenance', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (7, N'Telephone', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (8, N'Dues & Subscriptions', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (9, N'Professional fees', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (10, N'Packaging ', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (11, N'Advertising', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (12, N'Stationery & Printing ', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (13, N'Rent', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (14, N'Property', NULL)
INSERT [dbo].[VCAS_expenses] ([Id], [name], [desc]) VALUES (15, N'Other', NULL)
SET IDENTITY_INSERT [dbo].[VCAS_expenses] OFF
GO
SET IDENTITY_INSERT [dbo].[VCAS_REF_expense_location] ON 
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (1, 1, 1)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (2, 1, 2)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (3, 1, 3)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (4, 1, 4)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (5, 1, 5)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (6, 1, 6)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (7, 1, 7)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (8, 1, 8)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (9, 1, 9)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (10, 1, 10)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (11, 1, 11)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (12, 1, 12)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (13, 1, 13)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (14, 1, 14)
INSERT [dbo].[VCAS_REF_expense_location] ([Id], [FK_councilId], [FK_expensesId]) VALUES (15, 1, 15)
SET IDENTITY_INSERT [dbo].[VCAS_REF_expense_location] OFF
GO
-- Accounts / Charts-of-Accounts
-- ------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_debitAccounts] ON 
INSERT [dbo].[VCAS_debitAccounts] ([Id], [name], [acctNum], [amount], [FK_payment_Type], [remittance], [payee], [datetime], [attach_statement], [FK_location], [starting_balance], [type], [active]) VALUES (6, N'Checking Account', N'000', 49480, 2, N'Main Business Account', N'Business', CAST(N'2022-11-06T11:53:29.000' AS DateTime), N'Howard Bank Sample Personal Bank Statement.jpg', 1004, NULL, NULL, N'True')
INSERT [dbo].[VCAS_debitAccounts] ([Id], [name], [acctNum], [amount], [FK_payment_Type], [remittance], [payee], [datetime], [attach_statement], [FK_location], [starting_balance], [type], [active]) VALUES (7, N'Salaries', N'000', 8800, 2, N'Business Payroll', N'Business', CAST(N'2022-11-06T11:55:19.000' AS DateTime), NULL, 1004, NULL, NULL, N'True')
INSERT [dbo].[VCAS_debitAccounts] ([Id], [name], [acctNum], [amount], [FK_payment_Type], [remittance], [payee], [datetime], [attach_statement], [FK_location], [starting_balance], [type], [active]) VALUES (8, N'Bills', N'000', 2913, 2, N'Business Bills', N'Business', CAST(N'2022-11-06T11:56:48.000' AS DateTime), NULL, 1004, NULL, NULL, N'True')
INSERT [dbo].[VCAS_debitAccounts] ([Id], [name], [acctNum], [amount], [FK_payment_Type], [remittance], [payee], [datetime], [attach_statement], [FK_location], [starting_balance], [type], [active]) VALUES (11, N'Utilities', N'00000000', 85000, 5, N'Government Subvention', N'GOCD', CAST(N'2023-03-03T11:56:19.000' AS DateTime), NULL, 1005, NULL, 
INSERT [dbo].[VCAS_debitAccounts] ([Id], [name], [acctNum], [amount], [FK_payment_Type], [remittance], [payee], [datetime], [attach_statement], [FK_location], [starting_balance], [type], [active]) VALUES (3024, N'General Revenues', N'8762', 10, 3, N'For everyday Transaction ', N'Pretty Little Lady ', CAST(N'2023-11-27T16:39:33.000' AS DateTime), NULL, 1006, NULL, N'Income', N'True')
INSERT [dbo].[VCAS_debitAccounts] ([Id], [name], [acctNum], [amount], [FK_payment_Type], [remittance], [payee], [datetime], [attach_statement], [FK_location], [starting_balance], [type], [active]) VALUES (3025, N'Savings ', N'2268', 10, 5, N'For general savings', N'Pretty Little Lady ', CAST(N'2023-11-27T16:39:43.000' AS DateTime), NULL, 1006, NULL, N'Income', N'True')
SET IDENTITY_INSERT [dbo].[VCAS_debitAccounts] OFF
GO

Reporting data

-- Report & Report parameters
-- -----------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[VCAS_reports] ON 
INSERT [dbo].[VCAS_reports] ([Id], [name], [desc], [FK_REF_userRolesId], [paramCheck]) VALUES (1, N'Monthly Report.rdl', N'Monthly Report', 1, 1)
INSERT [dbo].[VCAS_reports] ([Id], [name], [desc], [FK_REF_userRolesId], [paramCheck]) VALUES (2, N'Expense Report.rdl', N'Expense Report', 1, 0)
SET IDENTITY_INSERT [dbo].[VCAS_reports] OFF
GO
SET IDENTITY_INSERT [dbo].[VCAS_REF_reports_params] ON 
INSERT [dbo].[VCAS_REF_reports_params] ([Id], [param_key], [param_value], [param_dataType], [FK_REF_reportsId], [FK_location]) VALUES (1, N'loc', N'1', N'int', 1, 1)
INSERT [dbo].[VCAS_REF_reports_params] ([Id], [param_key], [param_value], [param_dataType], [FK_REF_reportsId], [FK_location]) VALUES (2, N'date', N'1/1/2024', N'string', 1, 1)
SET IDENTITY_INSERT [dbo].[VCAS_REF_reports_params] OFF
GO