/*Trailblazer Database Setup scripts */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[shipvia]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[shipvia](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ShipMethod] [nvarchar](50) NULL,
	[ShippingCost] [money] NULL,
	[flag] [int] DEFAULT 0,
 CONSTRAINT [PK_shipvia] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Costs]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Costs](
	[CostVal] [money] NOT NULL,
 CONSTRAINT [PK_Costs] PRIMARY KEY CLUSTERED 
(
	[CostVal] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[statuses]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[statuses](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[status_order] [int] NOT NULL,
	[status_name] [nvarchar](50) NULL,
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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[security_levels]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[security_levels](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[security_level_name] [nvarchar](50) NULL,
	[security_level_description] [nvarchar](max) NULL,
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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[priority]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[priority](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[priority_name] [nvarchar](50) NOT NULL,
	[level] [int] NOT NULL,
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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employees]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](30) NULL,
	[LastName] [nvarchar](50) NULL,
	[Title] [nvarchar](20) NULL,
	[Password] [nvarchar](20) NULL,
	[EmailName] [nvarchar](50) NULL,
	[Extension] [nvarchar](30) NULL,
	[Address] [nvarchar](255) NULL,
	[City] [nvarchar](50) NULL,
	[State] [nvarchar](50) NULL,
	[Region] [nvarchar](50) NULL,
	[PostalCode] [nvarchar](20) NULL,
	[Country] [nvarchar](50) NULL,
	[HomePhone] [nvarchar](30) NULL,
	[WorkPhone] [nvarchar](30) NULL,
	[DepartmentName] [nvarchar](50) NULL,
	[Birthdate] [datetime] NULL,
	[SupervisorID] [nvarchar](50) NULL,
	[EmrgcyContactName] [nvarchar](50) NULL,
	[EmrgcyContactPhone] [nvarchar](30) NULL,
	[Photograph] [image] NULL,
	[Note] [ntext] NULL,
	[securityLevel] [int] NULL,
	[site] [int] NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Customers](
	[CustomerID] [int]  NOT NULL,
	[OrganizationName] [nvarchar](50) NULL,
	[Username] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Address] [nvarchar](255) NULL,
	[City] [nvarchar](50) NULL,
	[State] [nvarchar](50) NULL,
	[Region] [nvarchar](50) NULL,
	[PostalCode] [nvarchar](20) NULL,
	[Country] [nvarchar](50) NULL,
	[ContactName] [nvarchar](50) NULL,
	[ContactTitle] [nvarchar](50) NULL,
	[PhoneNumber] [nvarchar](30) NULL,
	[FaxNumber] [nvarchar](30) NULL,
	[Note] [ntext] NULL,
	[MobilePhone] [nvarchar](30) NULL,
	[EmailName] [nvarchar](50) NULL,
	[ReferredBy] [nvarchar](255) NULL,
	[Photograph] [image] NULL,
	[PaymentTerms] [nvarchar](50) NULL,
	[EntryDate] [datetime] NULL,
	[DeliveryRoute] [nvarchar](50) NULL,
	[activated] [bit] NULL,
	[password] [nvarchar](50) NULL,
	[Trader] [bit] 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] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Manufacturers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Manufacturers](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[manufacturer] [nvarchar](50) NULL,
 CONSTRAINT [PK_Manufacturers] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Models]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Models](
	[modelid] [int] IDENTITY(1,1) NOT NULL,
	[manufacturerID] [int] NOT NULL,
	[ModelName] [nvarchar](100) NULL,
 CONSTRAINT [PK_Models_1] PRIMARY KEY CLUSTERED 
(
	[modelid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PaymentTerms]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PaymentTerms](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PaymentTerms] [nvarchar](50) NOT NULL,
	[NumberOfDays] [float] NULL,
 CONSTRAINT [PK_PaymentTerms] 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]
END
GO
/****** Object:  Table [dbo].[RepairData]    Script Date: 01/18/2011 14:06:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RepairData](
	[OrderID] [int] NOT NULL,
	[ShipVia] [nvarchar](50) NULL,
	[FreightCharge] [money] NULL,
	[CodCharge] [money] NULL,
	[InvoiceID] [int] NULL,
	[DepositDue] [money] NULL,
	[ServiceDate] [datetime] NULL,
	[CustomerID] [nvarchar](50) NULL,
	[ProblemDescription] [ntext] NULL,
	[EquipmentDescription] [ntext] NULL,
	[SerialNumber] [nvarchar](50) NULL,
	[EmployeeID] [int] NULL,
	[DateReceived] [datetime] NULL,
	[ReceivedBy] [int] NULL,
	[DatePromised] [datetime] NULL,
	[EstimatedCost] [money] NULL,
	[WithBezel] [bit] NULL,
	[WithPowerCord] [bit] NULL,
	[ManufacturerName] [int] NULL,
	[Description] [nvarchar](50) NULL,
	[ModelNumber] [nvarchar](50) NULL,
	[Comments] [ntext] NULL,
	[BenchFee] [money] NULL,
	[PartsReplaced] [ntext] NULL,
	[Note] [ntext] NULL,
	[PartsCost] [money] NULL,
	[TaxCost] [money] NULL,
	[ActualCost] [money] NULL,
	[DatePartsOrdered] [datetime] NULL,
	[DatePartsReceived] [datetime] NULL,
	[PartsOrdered] [bit] NULL,
	[PartsReceived] [bit] NULL,
	[ActionTakenToRepair] [ntext] NULL,
	[AmountPaid] [money] NULL,
	[AmountBilled] [money] NULL,
	[TotalCost] [money] NULL,
	[DateDelivered] [datetime] NULL,
	[PrevServiceDate] [datetime] NULL,
	[WarrantyExpDate] [datetime] NULL,
	[AuthorizedBy] [nvarchar](50) NULL,
	[WarrantyWork] [bit] NULL,
	[WithSignalCable] [bit] NULL,
	[PaidInFull] [bit] NULL,
	[AssignedTechnician] [int] NULL,
	[RepairCompleted] [bit] NULL,
	[UneconomicalToRepair] [bit] NULL,
	[NoProblemFound] [bit] NULL,
	[NoPartsAvailable] [bit] NULL,
	[PaidDate] [datetime] NULL,
	[DateCompleted] [datetime] NULL,
	[Status] [int] NULL,
	[Priority] [int] NULL,
	[repairTime] [datetime] NULL,
	[NewManufacturer] [nvarchar](50) NULL,
	[NewModel] [nvarchar](50) NULL,
	[invoiced] [bit] NULL,
	[site] [int] NULL,
	[couriername] [nvarchar](50) NULL,
	[shippingtime] [nvarchar](20) NULL,
	[trackingno] [nvarchar](50) NULL,
 CONSTRAINT [PK_RepairData] PRIMARY KEY CLUSTERED 
(
	[OrderID] 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

ALTER TABLE [dbo].[RepairData] ADD  CONSTRAINT [DF_RepairData_RepairCompleted]  DEFAULT ((0)) FOR [RepairCompleted]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[configuration]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[configuration](
	[confid] [int] IDENTITY(1,1) NOT NULL,
	[cms] [bit] NULL,
	[name] [nvarchar](50) NULL,
	[value] [text] NULL,
 CONSTRAINT [PK_configuration] PRIMARY KEY CLUSTERED 
(
	[confid] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[cms](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NULL,
	[title] [nvarchar](50) NULL,
	[value] [text] NULL,
	[active] [bit] NULL,
 CONSTRAINT [PK_cms] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Invoices]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Invoices](
	[ID] [nvarchar](20) NOT NULL,
	[custid] [int] NULL,
	[gendate] [datetime] NULL,
	[notes] [text] NULL,
	[printed] [bit] NULL,
	[printdate] [datetime] NULL,
	[paid] [bit] NULL,
 CONSTRAINT [PK_Invoices] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvoiceItems]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[InvoiceItems](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceID] [nvarchar](20) NULL,
	[repairID] [int] NULL,
	[note] [nvarchar](500) NULL,
 CONSTRAINT [PK_InvoiceItems] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[lastInvoice]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[lastInvoice](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[invid] [int] NOT NULL,
 CONSTRAINT [PK_lastInvoice_1] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwRepairsUnassigned]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwRepairsUnassigned]
AS
SELECT     dbo.RepairData.OrderID, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, dbo.RepairData.DateReceived, dbo.RepairData.RepairCompleted, 
                      dbo.priority.priority_name, dbo.RepairData.Priority
FROM         dbo.Models RIGHT OUTER JOIN
                      dbo.priority RIGHT OUTER JOIN
                      dbo.RepairData ON dbo.priority.[level] = dbo.RepairData.Priority ON dbo.Models.modelid = dbo.RepairData.ModelNumber LEFT OUTER JOIN
                      dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id
WHERE     (dbo.RepairData.Status = 1)
' 
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwInvoice]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwInvoice]
AS
SELECT     dbo.Invoices.ID, dbo.Customers.OrganizationName, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.Trader, 
                      dbo.Invoices.gendate, dbo.Invoices.notes, dbo.Invoices.printed, dbo.Invoices.printdate, dbo.Invoices.paid
FROM         dbo.Customers RIGHT OUTER JOIN
                      dbo.Invoices ON dbo.Customers.CustomerID = dbo.Invoices.custid
' 
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCompletedRepairsNotInvoiced]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwCompletedRepairsNotInvoiced]
AS
SELECT     dbo.RepairData.OrderID, dbo.Customers.OrganizationName, dbo.Customers.City, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, 
                      dbo.RepairData.SerialNumber, dbo.RepairData.DateCompleted, dbo.RepairData.TotalCost, dbo.RepairData.CustomerID
FROM         dbo.RepairData LEFT OUTER JOIN
                      dbo.Models ON dbo.RepairData.ModelNumber = dbo.Models.modelid LEFT OUTER JOIN
                      dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id FULL OUTER JOIN
                      dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID
WHERE     (dbo.RepairData.invoiced = 0 OR
                      dbo.RepairData.invoiced IS NULL) AND (dbo.RepairData.RepairCompleted = 1) AND (dbo.Customers.Trader = 1)
' 
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCustomerswithRepairsNotYetInvoiced]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwCustomerswithRepairsNotYetInvoiced]
AS
SELECT DISTINCT dbo.RepairData.CustomerID, dbo.Customers.OrganizationName
FROM         dbo.RepairData LEFT OUTER JOIN
                      dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID
WHERE     (dbo.RepairData.invoiced = 0 OR
                      dbo.RepairData.invoiced IS NULL) AND (dbo.RepairData.RepairCompleted = 1) AND (dbo.Customers.Trader = 1)
' 
GO
CREATE VIEW [dbo].[vwCustomerTickets]
AS
SELECT     dbo.RepairData.OrderID, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, CONVERT(VARCHAR(10), dbo.RepairData.DateReceived, 103) 
                      AS Received, dbo.RepairData.RepairCompleted, dbo.priority.priority_name, dbo.statuses.status_name, dbo.RepairData.CustomerID, 
                      dbo.RepairData.SerialNumber
FROM         dbo.Models RIGHT OUTER JOIN
                      dbo.statuses RIGHT OUTER JOIN
                      dbo.RepairData ON dbo.statuses.id = dbo.RepairData.Status LEFT OUTER JOIN
                      dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id ON 
                      dbo.Models.modelid = dbo.RepairData.ModelNumber LEFT OUTER JOIN
                      dbo.priority ON dbo.RepairData.Priority = dbo.priority.id

GO

SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[menus](
	[id] [int] NOT NULL,
	[title] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[url] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[seclevel] [int] NULL,
	[order] [int] NULL,
 CONSTRAINT [PK_menus] 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]


GO
/****** Object:  Table [dbo].[sites]    Script Date: 12/07/2010 22:15:42 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sites]') AND type in (N'U'))
DROP TABLE [dbo].[sites]

GO
SET ANSI_PADDING OFF

CREATE TABLE [dbo].[sites](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[sitename] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[address1] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[address2] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[city] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[postcode] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[tel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
	[fax] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
	[email] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[siteURL] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[logo] [nvarchar](150) COLLATE Latin1_General_CI_AS NULL,	
 CONSTRAINT [PK_sites] 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]

GO
/****** Object:  View [dbo].[vwEmployees]    Script Date: 12/07/2010 22:14:51 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwEmployees]'))
DROP VIEW [dbo].[vwEmployees]
/****** Object:  View [dbo].[vwEmployees]    Script Date: 12/07/2010 22:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwEmployees]
AS
SELECT     dbo.Employees.EmployeeID, dbo.Employees.UserName, dbo.Employees.FirstName + ' ' + dbo.Employees.LastName AS name, dbo.sites.sitename, 
                      dbo.security_levels.security_level_description
FROM         dbo.Employees LEFT OUTER JOIN
                      dbo.security_levels ON dbo.Employees.securityLevel = dbo.security_levels.id LEFT OUTER JOIN
                      dbo.sites ON dbo.Employees.site = dbo.sites.id

GO


GO
/****** Object:  View [dbo].[vwTechswithRepairs]    Script Date: 12/07/2010 22:15:12 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTechswithRepairs]'))
DROP VIEW [dbo].[vwTechswithRepairs]

/****** Object:  View [dbo].[vwTechswithRepairs]    Script Date: 12/07/2010 22:11:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwTechswithRepairs]
AS
SELECT     EmployeeID, FirstName, LastName,
                          (SELECT     COUNT(1) AS Expr1
                            FROM          dbo.RepairData
                            WHERE      (AssignedTechnician = dbo.Employees.EmployeeID) AND (RepairCompleted = 0 OR
                                                   RepairCompleted IS NULL)) AS repairs
FROM         dbo.Employees
WHERE     ((SELECT     COUNT(1) AS Expr1
                         FROM         dbo.RepairData AS RepairData_1
                         WHERE     (AssignedTechnician = dbo.Employees.EmployeeID) AND (RepairCompleted = 0 OR
                                               RepairCompleted IS NULL)) > 0)
GO





INSERT INTO [dbo].[Employees]
           ([UserName]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Title]
           ,[Password]
           ,[EmailName]
           ,[Extension]
           ,[Address]
           ,[City]
           ,[State]
           ,[Region]
           ,[PostalCode]
           ,[Country]
           ,[HomePhone]
           ,[WorkPhone]
           ,[DepartmentName]
           ,[Birthdate]
           ,[SupervisorID]
           ,[EmrgcyContactName]
           ,[EmrgcyContactPhone]
           ,[Photograph]
           ,[Note]
           ,[securityLevel]
           ,[site])
     VALUES ('admin','','','','','admin','','','','','','','','','','','','','','','','','',5,1)
     
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(1,'View Repairs','~/TicketList.aspx',3,1)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(2,'Book in Repair','~/TicketForm.aspx',4,1)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(3,'View Invoices','~/Invoices.aspx',4,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(4,'Uninvoiced Repairs','~/TicketsnotInvoiced.aspx',4,4)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(5,'Customers','~/CustomerList.aspx',4,5)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(6,'Employees','~/Employee_List.aspx',5,6)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(7,'Payment Terms','~/PaymentMethods.aspx',5,7)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(8,'Status Levels','~/StatusList.aspx',5,8)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(9,'Manufacturers','~/Manufacturers.aspx',5,9)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(10,'Shipping','~/Shipping.aspx',5,10)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(11,'Import Manufacturers','~/ManufacturersImport.aspx',5,11)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(12,'View Repair Statistics','~/RepairStats.aspx',5,12)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(13,'Configuration','~/configuration.aspx',5,13)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(14,'CMS','~/ConfigCMS.aspx',5,14)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(15,'Book in Repair','~/TicketForm.aspx',5,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(16,'View Repairs','~/TicketListAdmin.aspx',4,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(17,'View Repairs','~/TicketListAdmin.aspx',5,1)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(18,'View Invoices','~/Invoices.aspx',5,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(19,'Uninvoiced Repairs','~/TicketsnotInvoiced.aspx',5,4)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(20,'Customers','~/CustomerList.aspx',5,5)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(21,'My Profile','~/Employee_Edit.aspx',3,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(22,'My Profile','~/Employee_Edit.aspx',4,6)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(23,'My Profile','~/Employee_Edit.aspx',5,15)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(24,'Change Password','~/EmployeeChangePassword.aspx',3,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(25,'Change Password','~/EmployeeChangePassword.aspx',4,7)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(26,'Change Password','~/EmployeeChangePassword.aspx',5,16)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(27,'Change Password','~/CustomerChangePassword.aspx',0,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(28,'My Profile','~/CustomerAmendDetails.aspx',0,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(29,'Book in Repair','~/CustomerTicketBookin.aspx',0,1)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(30,'My Profile','~/CustomerAmendDetails.aspx',99,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(31,'Change Password','~/CustomerChangePassword.aspx',99,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(32,'Company Sites','~/Sites.aspx',5,10)

SET IDENTITY_INSERT [configuration] ON
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(1,0,'defaulttitle','Micro Services Ltd')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(2,0,'default body','<p>If you are a customer, please log in through the Customer Accounts button. Once logged in, you will be able to see any repairs that are currently in our system, and be able to check their status.</p>  <p><span lang="en-gb">Trailblazer is a web based .NET application that  keeps track of all the repairs that you have in your shop, and  allows customers to log in and check the status of their booked in  items. If you have trade customers with multiple items in for  repair at any time, this becomes a powerful tool, enabling them to  check the repair status at any time, and from any location.</span></p>')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(3,0,'sidebar','<h5>Intro:</h5>  <p>Trailblazer is designed to make tracking of repairs simple. It allows customers to log in and see the status of the repairs they have in the shop.</p>')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(4,0,'CompanyName','Micro Services Ltd')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(5,0,'adminemail','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(6,0,'address1','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(7,0,'address2','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(8,0,'City','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(9,0,'Postcode','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(10,0,'Tel','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(11,0,'url','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(12,0,'companyreg','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(13,0,'VATNo','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(14,0,'mailserver','mail.domain.com')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(15,0,'mailusername','admin@domain.com')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(16,0,'mailpassword','password')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(17,0,'notifytech','1')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(18,0,'smsusername','yours')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(19,0,'smspassword','yours')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(20,0,'licencekey','yours')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(21,0,'completethreshhold','6')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(22,0,'VATRatePercent','20')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(23,0,'SalesTaxName','VAT')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(24,0,'currencysymbol','£')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(25,0,'enforcepeerreview','1')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(26,0,'truncatecomments','300')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(27,0,'pcrepairmode','1')
SET IDENTITY_INSERT [configuration] OFF


SET IDENTITY_INSERT [security_levels] ON

INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(1,'Minimum','Read Only')
INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(2,'','User Support')
INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(3,'','Technician')
INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(4,'','Manager')
INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(5,'Maximum','Administrator')
SET IDENTITY_INSERT [security_levels] OFF


SET IDENTITY_INSERT [PaymentTerms] ON
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(1,'C.O.D.',0.000000000000000e+000)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(2,'Net 120',1.200000000000000e+002)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(3,'Net 150',1.500000000000000e+002)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(4,'Net 180',1.800000000000000e+002)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(5,'Net 30',3.000000000000000e+001)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(6,'Net 60',6.000000000000000e+001)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(7,'Net 90',9.000000000000000e+001)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(8,'Upon Receipt',8.000000000000000e+000)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(9,'Credit',9.000000000000000e+001)
SET IDENTITY_INSERT [PaymentTerms] Off

SET IDENTITY_INSERT [priority] ON
INSERT INTO [priority] ([id],[priority_name],[level])VALUES(1,'Low',1)
INSERT INTO [priority] ([id],[priority_name],[level])VALUES(2,'Medium',2)
INSERT INTO [priority] ([id],[priority_name],[level])VALUES(3,'High',3)
INSERT INTO [priority] ([id],[priority_name],[level])VALUES(4,'Urgent',10)
SET IDENTITY_INSERT [priority] Off

SET IDENTITY_INSERT [shipvia] ON
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(1,'Royal Mail',8.0000)
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(2,'UPS',15.0000)
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(3,'Pickup in Store',0.0000)
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(4,'Courier',35.0000)
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(5,'Our Driver',8.0000)
SET IDENTITY_INSERT [shipvia] Off

SET IDENTITY_INSERT [statuses] ON
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(0,0,'Booked in by Customer')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(1,1,'Booked in')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(2,2,'Assigned to Technician')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(3,3,'Awaiting Parts')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(4,4,'Awaiting Customer Decision')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(5,5,'Repair In Progress')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(6,6,'Repair Complete')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(7,7,'Repair Complete')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(8,8,'Shipped')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(9,9,'Invoiced')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(10,10,'Paid')
SET IDENTITY_INSERT [statuses] Off


INSERT INTO [Costs] ([CostVal])VALUES(0.0000)
INSERT INTO [Costs] ([CostVal])VALUES(5.0000)
INSERT INTO [Costs] ([CostVal])VALUES(10.0000)
INSERT INTO [Costs] ([CostVal])VALUES(15.0000)
INSERT INTO [Costs] ([CostVal])VALUES(20.0000)
INSERT INTO [Costs] ([CostVal])VALUES(25.0000)
INSERT INTO [Costs] ([CostVal])VALUES(30.0000)
INSERT INTO [Costs] ([CostVal])VALUES(35.0000)
INSERT INTO [Costs] ([CostVal])VALUES(40.0000)
INSERT INTO [Costs] ([CostVal])VALUES(45.0000)
INSERT INTO [Costs] ([CostVal])VALUES(50.0000)
INSERT INTO [Costs] ([CostVal])VALUES(55.0000)
INSERT INTO [Costs] ([CostVal])VALUES(60.0000)
INSERT INTO [Costs] ([CostVal])VALUES(65.0000)
INSERT INTO [Costs] ([CostVal])VALUES(70.0000)
INSERT INTO [Costs] ([CostVal])VALUES(75.0000)
INSERT INTO [Costs] ([CostVal])VALUES(80.0000)
INSERT INTO [Costs] ([CostVal])VALUES(85.0000)
INSERT INTO [Costs] ([CostVal])VALUES(90.0000)
INSERT INTO [Costs] ([CostVal])VALUES(95.0000)
INSERT INTO [Costs] ([CostVal])VALUES(100.0000)
INSERT INTO [Costs] ([CostVal])VALUES(105.0000)
INSERT INTO [Costs] ([CostVal])VALUES(110.0000)
INSERT INTO [Costs] ([CostVal])VALUES(115.0000)
INSERT INTO [Costs] ([CostVal])VALUES(120.0000)
INSERT INTO [Costs] ([CostVal])VALUES(125.0000)
INSERT INTO [Costs] ([CostVal])VALUES(130.0000)
INSERT INTO [Costs] ([CostVal])VALUES(135.0000)
INSERT INTO [Costs] ([CostVal])VALUES(140.0000)
INSERT INTO [Costs] ([CostVal])VALUES(145.0000)
INSERT INTO [Costs] ([CostVal])VALUES(150.0000)
INSERT INTO [Costs] ([CostVal])VALUES(155.0000)
INSERT INTO [Costs] ([CostVal])VALUES(160.0000)
INSERT INTO [Costs] ([CostVal])VALUES(165.0000)
INSERT INTO [Costs] ([CostVal])VALUES(170.0000)
INSERT INTO [Costs] ([CostVal])VALUES(175.0000)
INSERT INTO [Costs] ([CostVal])VALUES(180.0000)
INSERT INTO [Costs] ([CostVal])VALUES(185.0000)
INSERT INTO [Costs] ([CostVal])VALUES(190.0000)
INSERT INTO [Costs] ([CostVal])VALUES(200.0000)
INSERT INTO [Costs] ([CostVal])VALUES(205.0000)
INSERT INTO [Costs] ([CostVal])VALUES(210.0000)
INSERT INTO [Costs] ([CostVal])VALUES(215.0000)
INSERT INTO [Costs] ([CostVal])VALUES(220.0000)
INSERT INTO [Costs] ([CostVal])VALUES(225.0000)
INSERT INTO [Costs] ([CostVal])VALUES(230.0000)
INSERT INTO [Costs] ([CostVal])VALUES(235.0000)
INSERT INTO [Costs] ([CostVal])VALUES(240.0000)
INSERT INTO [Costs] ([CostVal])VALUES(245.0000)
INSERT INTO [Costs] ([CostVal])VALUES(250.0000)
INSERT INTO [Costs] ([CostVal])VALUES(255.0000)
INSERT INTO [Costs] ([CostVal])VALUES(260.0000)
INSERT INTO [Costs] ([CostVal])VALUES(265.0000)
INSERT INTO [Costs] ([CostVal])VALUES(270.0000)
INSERT INTO [Costs] ([CostVal])VALUES(275.0000)
INSERT INTO [Costs] ([CostVal])VALUES(280.0000)
INSERT INTO [Costs] ([CostVal])VALUES(285.0000)
INSERT INTO [Costs] ([CostVal])VALUES(290.0000)
INSERT INTO [Costs] ([CostVal])VALUES(300.0000)

SET IDENTITY_INSERT [Manufacturers] ON
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(1,'Dell')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(3,'Macintosh')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(4,'Hewlett Packard')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(5,'Yuraku')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(6,'Asus')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(7,'ABS Computer Technologies(Parent:Newegg)')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(8,'Acer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(9,'Gateway')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(10,'Aigo')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(11,'Viewsonic')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(12,'AMAX')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(14,'IBM')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(15,'eMachines')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(16,'Packard Bell')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(17,'Amstrad')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(18,'Apple')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(19,'AVADirect')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(20,'Averatec')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(21,'AXIOO International')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(22,'Axiomtek')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(23,'Belinea')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(24,'BenQ')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(25,'Cerise Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(26,'Chip PC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(27,'Clevo')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(28,'Commodore Gaming')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(29,'Cray')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(30,'CyberPower PC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(31,'Data General')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(32,'Alienware')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(33,'Dera')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(34,'Doxx Computer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(35,'Egenera')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(36,'Falcon Northwest')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(37,'Fujitsu')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(38,'Gericom')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(39,'Gigabyte')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(40,'Groupe Bull')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(41,'HLBS Tech (P) Limited')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(42,'Hasee')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(43,'Hewlett-Packard')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(44,'Compaq')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(45,'VoodooPC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(46,'HCL Infosystems Ltd')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(47,'Hitachi')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(48,'Integrated Intellect Corporation')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(49,'IGEL')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(50,'Jetta International')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(51,'Kohjinsha')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(52,'Kontron AG')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(53,'Lanix')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(54,'Lanner Inc')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(55,'LanSlide Gaming PCs')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(56,'Lenovo')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(57,'LG')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(58,'Maingear')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(59,'MDG Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(60,'Mecer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(61,'Medion')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(62,'Mesh Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(63,'Micro-Star International (MSI)')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(64,'Micro Center')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(65,'Mitac International Corp.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(66,'Motion Computing Inc.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(67,'NEC Corp')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(68,'Nedfield NV(formerly: Tulip Computers)')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(69,'NEO')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(70,'Neos Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(71,'Olidata')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(72,'Olivetti')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(73,'Oracle Corporation')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(74,'Sun Microsystems')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(75,'Origin PC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(76,'Panasonic')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(77,'Polywell')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(78,'Puget Systems')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(79,'Q2')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(80,'Redfox')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(81,'Sager Notebook Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(82,'Samsung Electronics')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(83,'Sharp')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(84,'Shuttle Inc.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(85,'SGI')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(86,'Socket Mobile Inc.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(87,'Sony Corp')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(88,'Systemax')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(89,'Circuit City')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(90,'CompUSA')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(91,'TigerDirect')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(92,'System76')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(93,'TabletKiosk')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(94,'Tadpole Computer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(95,'TIME')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(96,'Toshiba')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(97,'TriGem Computer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(98,'Inc')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(99,'Tyan')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(100,'Unisys')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(101,'Velocity Micro')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(102,'Viglen')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(103,'Vigor Gaming')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(104,'WidowPC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(105,'Wipro Infotech')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(106,'Wyse Technology Inc.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(107,'Xitrix Computer Corporation')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(108,'Zenith Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(109,'Zoostorm')

SET IDENTITY_INSERT [Manufacturers] OFF

SET IDENTITY_INSERT [lastInvoice] ON
INSERT INTO [lastInvoice] ([id],[invid])VALUES(1,2000)
SET IDENTITY_INSERT [lastInvoice] OFF

SET IDENTITY_INSERT [sites] ON
INSERT INTO [sites] ([id],[sitename],[address1],[address2],[city],[postcode],[tel],[fax],[email],[siteURL],[logo])VALUES(1,'default',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
SET IDENTITY_INSERT [sites] OFF



   /****** Object:  Table [dbo].[lastCustomer]    Script Date: 12/10/2010 13:52:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[lastCustomer](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[custid] [int] NOT NULL,
 CONSTRAINT [PK_lastCustomer_1] 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]

/****** Object:  Table [dbo].[lastRepair]    Script Date: 12/10/2010 13:52:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[lastRepair](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[repid] [int] NOT NULL,
 CONSTRAINT [PK_lastRepair_1] 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]

SET IDENTITY_INSERT [lastCustomer] ON
INSERT INTO [lastCustomer] ([id],[custid])VALUES(1,100)
SET IDENTITY_INSERT [lastCustomer] OFF
SET IDENTITY_INSERT [lastRepair] ON
INSERT INTO [lastRepair] ([id],[repid])VALUES(1,3000)
SET IDENTITY_INSERT [lastRepair] OFF


/****** Object:  View [dbo].[vwCustswithRepairs]    Script Date: 12/15/2010 18:57:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwCustswithRepairs]
AS
SELECT     CustomerID, OrganizationName, FirstName, LastName,
                          (SELECT     COUNT(1) AS Expr1
                            FROM          dbo.RepairData
                            WHERE      (CustomerID = dbo.Customers.CustomerID) AND (RepairCompleted = 0 OR
                                                   RepairCompleted IS NULL)) AS repairs
FROM         dbo.Customers
WHERE     ((SELECT     COUNT(1) AS Expr1
                         FROM         dbo.RepairData AS RepairData_1
                         WHERE     (CustomerID = dbo.Customers.CustomerID) AND (RepairCompleted = 0 OR
                                               RepairCompleted IS NULL)) > 0)


GO
/*  26th Jan 2011 */
ALTER TABLE RepairData
 ADD 	[techsignoff] [bit] NULL,
	[techsignoff2] [bit] NULL,
	[tech2id] [int] NULL,
	[externalref] nvarchar(150) NULL;
	
	
	/****** Object:  View [dbo].[vwRepairsbyEmployees]    Script Date: 12/08/2010 18:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwRepairsbyEmployees]
AS
SELECT     dbo.RepairData.OrderID, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, CONVERT(VARCHAR(10), dbo.RepairData.DateReceived, 103) 
                      AS Received, dbo.RepairData.RepairCompleted, dbo.priority.priority_name, dbo.statuses.status_name, dbo.RepairData.CustomerID, 
                      dbo.RepairData.SerialNumber, dbo.RepairData.AssignedTechnician, dbo.RepairData.UneconomicalToRepair, dbo.RepairData.externalref

FROM         dbo.Models RIGHT OUTER JOIN
                      dbo.statuses RIGHT OUTER JOIN
                      dbo.RepairData ON dbo.statuses.id = dbo.RepairData.Status LEFT OUTER JOIN
                      dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id ON 
                      dbo.Models.modelid = dbo.RepairData.ModelNumber LEFT OUTER JOIN
                      dbo.priority ON dbo.RepairData.Priority = dbo.priority.id

GO
	
/****** Object:  View [dbo].[vwCustomerswithRepairsNotYetInvoiced]    Script Date: 01/26/2011 13:33:29 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCustomerswithRepairsNotYetInvoiced]'))
DROP VIEW [dbo].[vwCustomerswithRepairsNotYetInvoiced]
GO

/****** Object:  View [dbo].[vwCustomerswithRepairsNotYetInvoiced]    Script Date: 01/27/2011 13:45:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwCustomerswithRepairsNotYetInvoiced]
AS
SELECT DISTINCT dbo.RepairData.CustomerID, dbo.Customers.OrganizationName
FROM         dbo.RepairData LEFT OUTER JOIN
                      dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID
WHERE     (dbo.RepairData.invoiced = 0 OR
                      dbo.RepairData.invoiced IS NULL) AND (dbo.RepairData.RepairCompleted = 1) AND (dbo.Customers.Trader = 1)

GO

/****** Object:  View [dbo].[vwRepairsAll]    Script Date: 01/27/2011 13:48:08 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwRepairsAll]'))
DROP VIEW [dbo].[vwRepairsAll]
GO

/****** Object:  View [dbo].[vwRepairsAll]    Script Date: 01/27/2011 13:48:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwRepairsAll]
AS
SELECT     dbo.RepairData.OrderID, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, CONVERT(VARCHAR(10), dbo.RepairData.DateReceived, 103) 
                      AS Received, dbo.RepairData.RepairCompleted, dbo.priority.priority_name, dbo.RepairData.Priority, dbo.statuses.status_name, 
                      CASE WHEN dbo.Customers.OrganizationName <> '' THEN Customers.OrganizationName ELSE dbo.Customers.FirstName + ' ' + dbo.Customers.LastName
                       END AS cust, dbo.Customers.LastName, dbo.RepairData.site, dbo.sites.sitename, dbo.RepairData.SerialNumber, 
                      dbo.Employees.FirstName + ' ' + dbo.Employees.LastName AS empname, dbo.Employees.UserName, dbo.RepairData.Status, 
                      dbo.RepairData.AssignedTechnician, dbo.RepairData.externalref, dbo.Customers.FirstName
FROM         dbo.sites RIGHT OUTER JOIN
                      dbo.Employees RIGHT OUTER JOIN
                      dbo.RepairData ON dbo.Employees.EmployeeID = dbo.RepairData.AssignedTechnician ON dbo.sites.id = dbo.RepairData.site LEFT OUTER JOIN
                      dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID LEFT OUTER JOIN
                      dbo.statuses ON dbo.RepairData.Status = dbo.statuses.id LEFT OUTER JOIN
                      dbo.priority ON dbo.RepairData.Priority = dbo.priority.[level] LEFT OUTER JOIN
                      dbo.Models ON dbo.RepairData.ModelNumber = dbo.Models.modelid LEFT OUTER JOIN
                      dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id

GO


/****** Object:  View [dbo].[vwCustswithRepairs]    Script Date: 02/10/2011 21:46:09 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCustswithRepairs]'))
DROP VIEW [dbo].[vwCustswithRepairs]
GO

/****** Object:  View [dbo].[vwCustswithRepairs]    Script Date: 02/10/2011 21:46:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwCustswithRepairs]
AS
SELECT     CustomerID, OrganizationName, FirstName, LastName,
                          (SELECT     COUNT(1) AS Expr1
                            FROM          dbo.RepairData
                            WHERE      (CustomerID = dbo.Customers.CustomerID) AND (RepairCompleted = 0 OR
                                                   RepairCompleted IS NULL)) AS repairs, PhoneNumber, MobilePhone, EmailName
FROM         dbo.Customers
WHERE     ((SELECT     COUNT(1) AS Expr1
                         FROM         dbo.RepairData AS RepairData_1
                         WHERE     (CustomerID = dbo.Customers.CustomerID) AND (RepairCompleted = 0 OR
                                               RepairCompleted IS NULL)) > 0)

GO


/****** Object:  View [dbo].[vwCompletedRepairsNotInvoiced]    Script Date: 02/13/2011 18:06:26 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCompletedRepairsNotInvoiced]'))
DROP VIEW [dbo].[vwCompletedRepairsNotInvoiced]
GO

/****** Object:  View [dbo].[vwCompletedRepairsNotInvoiced]    Script Date: 02/13/2011 18:06:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwCompletedRepairsNotInvoiced]
AS
SELECT     dbo.RepairData.OrderID, dbo.Customers.OrganizationName, dbo.Customers.City, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, 
                      dbo.RepairData.SerialNumber, dbo.RepairData.DateCompleted, dbo.RepairData.TotalCost, dbo.RepairData.CustomerID, 
                      dbo.RepairData.AmountBilled

FROM         dbo.RepairData LEFT OUTER JOIN
                      dbo.Models ON dbo.RepairData.ModelNumber = dbo.Models.modelid LEFT OUTER JOIN
                      dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id LEFT OUTER JOIN
                      dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID
WHERE     (dbo.RepairData.invoiced = 0 OR
                      dbo.RepairData.invoiced IS NULL) AND (dbo.RepairData.RepairCompleted = 1) AND (dbo.Customers.Trader = 1)

GO



/****** Object:  Table [security]    Script Date: 02/22/2011 15:18:20 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[security]') AND type in (N'U'))
DROP TABLE dbo.[security]
GO

/****** Object:  Table [security]    Script Date: 02/22/2011 15:18:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[security](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NULL,
	[description] [nvarchar](100) NULL,
	[readonlyuser] [bit] NULL,
	[reportuser] [bit] NULL,
	[technician] [bit] NULL,
	[manager] [bit] NULL,
	[administrator] [bit] NULL,
 CONSTRAINT [PK_security] 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]

GO

SET IDENTITY_INSERT [security] ON
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('1','serial Number','Serial Number for repair item','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('2','Equipment Description','Equipment Description text box','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('3','AssignedTechnician','Drop down selection for assigning technician to repair job','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('4','Priority','Priority selection drop down list','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('5','Status','Status selection drop down list','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('6','Complete','Checkbox for setting repair as completed','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('7','Date Completed','Date picker to set completed date','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('8','Manufacturer','Manufacturer selection - drop down list','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('9','Model','Model selection - drop down list','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('10','ProblemDescription','Text area specifying nature of problem with repair job','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('11','DatePromised','Date used as a guide for when repair shop be delivered back to customer','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('12','assignpeer','Drop down list used to select technician to review repair once complete','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('13','Delete','Repair item delete button ','0','0','0','0','1')
SET IDENTITY_INSERT [security] OFF


INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(33,'Security Restrictions','~/FieldRestrictions.aspx',5,10)


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[partsused](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[repairID] [int] NOT NULL,
	[partID] [int] NOT NULL,
	[quantity] [int] NOT NULL,
 CONSTRAINT [PK_partsused] 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]

GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[partsstore](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[partno] [nvarchar](50) NOT NULL,
	[partname] [nvarchar](50) NOT NULL,
	[description] [nvarchar](200) NOT NULL,
	[cost] [decimal](18, 2) NOT NULL,
	[quant] [int] NOT NULL,
	[reorder] [int] NOT NULL,
	[partsource] [nvarchar](50) NULL,
	[sourcepartcode] [nvarchar](50) NULL,
 CONSTRAINT [PK_partsstore] 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]

GO




/****** Object:  Table [dbo].[messages]    Script Date: 02/28/2011 11:47:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[messages](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[status] [int] NULL,
	[smsmessage] [nvarchar](200) NULL,
	[emailmessage] [nvarchar](500) NULL,
	[sendoncomplete] [bit] NULL,
	[sendsms] [bit] NULL,
	[sendemail] [bit] NULL,
 CONSTRAINT [PK_messages] 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]

GO


/****** Object:  Table [dbo].[partscategory]    Script Date: 02/28/2011 11:47:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[partscategory](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[categoryname] [nvarchar](50) NOT NULL,
	[description] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_partscategory] 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]

GO


SET IDENTITY_INSERT  [messages] ON
INSERT [messages](id,status,smsmessage,emailmessage,sendoncomplete,sendsms,sendemail) VALUES('2','2','Your item has now been assigned to a technician','Your item has now been assigned to a technician','0','0','0')
INSERT [messages](id,status,smsmessage,emailmessage,sendoncomplete,sendsms,sendemail) VALUES('3','3','Your repair item requires your decision. Please contact us to discuss your repair.','Your repair item requires your decision. Please contact us to discuss your repair.','0','0','1')
INSERT [messages](id,status,smsmessage,emailmessage,sendoncomplete,sendsms,sendemail) VALUES('4','6','Your repair is now complete.','From #company#. Your repair no. #orderno# is now complete.  #manufacturer#  #model#  #serial#','1','1','1')
INSERT [messages](id,status,smsmessage,emailmessage,sendoncomplete,sendsms,sendemail) VALUES('5','9','You have now been sent an invoice for your repair item','You have now been sent an invoice for your repair item #orderno#','0','1','1')
SET IDENTITY_INSERT  [messages] OFF