-- ******************************************************
-- Create the database
-- ******************************************************
USE Master 
GO

PRINT '';
PRINT '*** Creating Database';
GO

DECLARE @DBNameChoice NVARCHAR(500);
DECLARE @SQL_Path NVARCHAR(500);

SET @DBNameChoice = 'KOB_Test';	--ENTER your database name here

/* getting the path of the Master DB */
SELECT @SQL_Path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1)
FROM [master].[sys].[master_files] 
WHERE [database_id] = 1 
    AND [file_id] = 1;

EXECUTE (N'CREATE DATABASE [' + @DBNameChoice + '] ON  PRIMARY 
( NAME = N''' + @DBNameChoice + ''', FILENAME = N''' + @SQL_Path + @DBNameChoice + '_Data.mdf'' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N''' + @DBNameChoice + '_Log'', FILENAME = N''' + @SQL_Path + @DBNameChoice + '_Log.ldf'' , SIZE = 8192KB , FILEGROWTH = 10% )')
GO 


-- ******************************************************
-- Create tables, views, stored procedures and functions
-- ******************************************************
USE KOB_Test	--ENTER your database name here
GO

PRINT '';
PRINT '*** Creating Tables';

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkFeatureActivityKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFeature]'))
ALTER TABLE [dbo].[tblFeature] DROP CONSTRAINT [fkFeatureActivityKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkFeatureDefaultBillingTypeKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFeature]'))
ALTER TABLE [dbo].[tblFeature] DROP CONSTRAINT [fkFeatureDefaultBillingTypeKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkFeatureProjectKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFeature]'))
ALTER TABLE [dbo].[tblFeature] DROP CONSTRAINT [fkFeatureProjectKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkPaymentMethodKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblPerson]'))
ALTER TABLE [dbo].[tblPerson] DROP CONSTRAINT [fkPaymentMethodKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkCompanyKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProject]'))
ALTER TABLE [dbo].[tblProject] DROP CONSTRAINT [fkCompanyKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkProjectRevenueCategoryKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProject]'))
ALTER TABLE [dbo].[tblProject] DROP CONSTRAINT [fkProjectRevenueCategoryKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkStatusKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProject]'))
ALTER TABLE [dbo].[tblProject] DROP CONSTRAINT [fkStatusKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkProjectDetailProjectKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProjectDetail]'))
ALTER TABLE [dbo].[tblProjectDetail] DROP CONSTRAINT [fkProjectDetailProjectKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTaskFeatureKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTask]'))
ALTER TABLE [dbo].[tblTask] DROP CONSTRAINT [fkTaskFeatureKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTaskPersonKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTask]'))
ALTER TABLE [dbo].[tblTask] DROP CONSTRAINT [fkTaskPersonKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTimeCardBillingTypeKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTimeCard]'))
ALTER TABLE [dbo].[tblTimeCard] DROP CONSTRAINT [fkTimeCardBillingTypeKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTimeCardPersonKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTimeCard]'))
ALTER TABLE [dbo].[tblTimeCard] DROP CONSTRAINT [fkTimeCardPersonKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTimeCardTaskKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTimeCard]'))
ALTER TABLE [dbo].[tblTimeCard] DROP CONSTRAINT [fkTimeCardTaskKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkProjectPersonPersonKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[txrfProjectPerson]'))
ALTER TABLE [dbo].[txrfProjectPerson] DROP CONSTRAINT [fkProjectPersonPersonKey]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkProjectPersonProjectKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[txrfProjectPerson]'))
ALTER TABLE [dbo].[txrfProjectPerson] DROP CONSTRAINT [fkProjectPersonProjectKey]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jfn_TaskBillableTotal]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[jfn_TaskBillableTotal]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_WatchTasks]'))
DROP VIEW [dbo].[jvw_WatchTasks]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jfn_TimecardDateWeek]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[jfn_TimecardDateWeek]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDocument]') AND type in (N'U'))
DROP TABLE [dbo].[tblDocument]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_ProjectSummary_deleteme]'))
DROP VIEW [dbo].[jvw_ProjectSummary_deleteme]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_TC_Proj_info]'))
DROP VIEW [dbo].[vw_TC_Proj_info]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_AllocatedHours]'))
DROP VIEW [dbo].[jvw_AllocatedHours]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Hours_By_PerPrjFtr]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Hours_By_PerPrjFtr]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_ProjectSummary]'))
DROP VIEW [dbo].[jvw_ProjectSummary]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jspFeatureOriginalEstTotal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jspFeatureOriginalEstTotal]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jspFeatureCurrentEstTotal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jspFeatureCurrentEstTotal]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jspFeatureActualTotal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jspFeatureActualTotal]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwOpenHours]'))
DROP VIEW [dbo].[vwOpenHours]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwOpenInvoices]'))
DROP VIEW [dbo].[vwOpenInvoices]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectActivity]'))
DROP VIEW [dbo].[vwProjectActivity]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwHoursByPerson]'))
DROP VIEW [dbo].[vwHoursByPerson]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwRevenueReport]'))
DROP VIEW [dbo].[vwRevenueReport]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProduction]'))
DROP VIEW [dbo].[vwProduction]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectStatus_Tasks]'))
DROP VIEW [dbo].[vwProjectStatus_Tasks]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTaskBudgetOLD]'))
DROP VIEW [dbo].[vwTaskBudgetOLD]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tlkpPayroll]') AND type in (N'U'))
DROP TABLE [dbo].[tlkpPayroll]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectStatus]'))
DROP VIEW [dbo].[vwProjectStatus]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsysConfig_System]') AND type in (N'U'))
DROP TABLE [dbo].[tsysConfig_System]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectwithTotalAllocatedandTotalBillable]'))
DROP VIEW [dbo].[vwProjectwithTotalAllocatedandTotalBillable]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTaskBudget]'))
DROP VIEW [dbo].[vwTaskBudget]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsysField]') AND type in (N'U'))
DROP TABLE [dbo].[tsysField]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_TotalBillableHoursforaDate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jsp_TotalBillableHoursforaDate]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsysReport]') AND type in (N'U'))
DROP TABLE [dbo].[tsysReport]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_TotalHoursforaDate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jsp_TotalHoursforaDate]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_TotalHoursforaWeek]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jsp_TotalHoursforaWeek]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsysVersion]') AND type in (N'U'))
DROP TABLE [dbo].[tsysVersion]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_TotalBillableHoursforaWeek]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jsp_TotalBillableHoursforaWeek]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_WeekStartDate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jsp_WeekStartDate]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_WeekEndDate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jsp_WeekEndDate]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[txrfProjectPerson]') AND type in (N'U'))
DROP TABLE [dbo].[txrfProjectPerson]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwcboProject]'))
DROP VIEW [dbo].[vwcboProject]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwcboTask]'))
DROP VIEW [dbo].[vwcboTask]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jspGetHours_by_Day]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jspGetHours_by_Day]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectDetail]'))
DROP VIEW [dbo].[vwProjectDetail]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblActivity]') AND type in (N'U'))
DROP TABLE [dbo].[tblActivity]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tlkpPaymentMethod]') AND type in (N'U'))
DROP TABLE [dbo].[tlkpPaymentMethod]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblPerson]') AND type in (N'U'))
DROP TABLE [dbo].[tblPerson]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblCompany]') AND type in (N'U'))
DROP TABLE [dbo].[tblCompany]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tlkpRevenueCategory]') AND type in (N'U'))
DROP TABLE [dbo].[tlkpRevenueCategory]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tlkpStatus]') AND type in (N'U'))
DROP TABLE [dbo].[tlkpStatus]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTimeCard]') AND type in (N'U'))
DROP TABLE [dbo].[tblTimeCard]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tvalBillingType]') AND type in (N'U'))
DROP TABLE [dbo].[tvalBillingType]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblFeature]') AND type in (N'U'))
DROP TABLE [dbo].[tblFeature]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTask]') AND type in (N'U'))
DROP TABLE [dbo].[tblTask]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_qtotProjectHours_Unbilled]'))
DROP VIEW [dbo].[jvw_qtotProjectHours_Unbilled]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_qtotProjectHours_Billed]'))
DROP VIEW [dbo].[jvw_qtotProjectHours_Billed]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_qtotProjectHours]'))
DROP VIEW [dbo].[jvw_qtotProjectHours]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_qtotAllocatedHours]'))
DROP VIEW [dbo].[jvw_qtotAllocatedHours]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTaskBudgetNonBillableHours]'))
DROP VIEW [dbo].[vwTaskBudgetNonBillableHours]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTaskBudgetBillableHours]'))
DROP VIEW [dbo].[vwTaskBudgetBillableHours]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwtotPersonalTimePayrollPeriod]'))
DROP VIEW [dbo].[vwtotPersonalTimePayrollPeriod]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwtotTimecardPayrollPeriod]'))
DROP VIEW [dbo].[vwtotTimecardPayrollPeriod]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectStatus_TaskActualBillableHours]'))
DROP VIEW [dbo].[vwProjectStatus_TaskActualBillableHours]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectTotalBillableHours]'))
DROP VIEW [dbo].[vwProjectTotalBillableHours]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectTotalAllocatedHours]'))
DROP VIEW [dbo].[vwProjectTotalAllocatedHours]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblProject]') AND type in (N'U'))
DROP TABLE [dbo].[tblProject]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblProjectDetail]') AND type in (N'U'))
DROP TABLE [dbo].[tblProjectDetail]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTimeCardPayrollPeriod]'))
DROP VIEW [dbo].[vwTimeCardPayrollPeriod]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwPersonalTimePayrollPeriod]'))
DROP VIEW [dbo].[vwPersonalTimePayrollPeriod]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

/*************** CREATE TABLES ******************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDocument]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblDocument](
	[DocumentKey] [int] IDENTITY(1,1) NOT NULL,
	[ProjectKey] [int] NULL,
	[DocumentSeq] [int] NULL,
	[DocumentName] [nvarchar](255) NULL,
	[DocumentType] [nvarchar](12) NULL,
	[DocumentDescription] [nvarchar](255) NULL,
	[DocumentSize] [int] NULL,
	[DocumentDate] [datetime] NOT NULL CONSTRAINT [DF_tblDocument_DocumentDate]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED 
(
	[DocumentKey] 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].[tblActivity]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblActivity](
	[ActivityKey] [int] IDENTITY(1,1) NOT NULL,
	[ActivityName] [nvarchar](255) NULL,
	[ActivityDesc] [nvarchar](255) NULL,
	[ActivityCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tblActivi__Activ__1BFD2C07]  DEFAULT (user_name()),
	[ActivityCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tblActivi__Activ__1CF15040]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkActivityKey] PRIMARY KEY CLUSTERED 
(
	[ActivityKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tblCompany]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblCompany](
	[CompanyKey] [int] IDENTITY(1,1) NOT NULL,
	[CompanyName] [nvarchar](80) NULL,
	[CompanyTerms] [nvarchar](255) NULL,
	[CompanyCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tblCompan__Compa__1FCDBCEB]  DEFAULT (user_name()),
	[CompanyCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tblCompan__Compa__20C1E124]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkCompanyKey] PRIMARY KEY CLUSTERED 
(
	[CompanyKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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].[tlkpPaymentMethod]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tlkpPaymentMethod](
	[PaymentMethodKey] [int] IDENTITY(1,1) NOT NULL,
	[PaymentMethodName] [nvarchar](255) NULL,
	[PaymentMethodExemptFlag] [smallint] NULL,
	[PaymentMethodCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tlkpPayme__Payme__0CBAE877]  DEFAULT (user_name()),
	[PaymentMethodCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tlkpPayme__Payme__0DAF0CB0]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkPaymentMethodKey] PRIMARY KEY CLUSTERED 
(
	[PaymentMethodKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tlkpPayroll]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tlkpPayroll](
	[PayrollKey] [int] IDENTITY(1,1) NOT NULL,
	[PayrollEndDate] [smalldatetime] NULL,
	[PayrollCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tlkpPayro__Payro__108B795B]  DEFAULT (user_name()),
	[PayrollCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tlkpPayro__Payro__117F9D94]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkPayrollKey] PRIMARY KEY CLUSTERED 
(
	[PayrollKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tlkpStatus]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tlkpStatus](
	[StatusKey] [int] IDENTITY(1,1) NOT NULL,
	[StatusName] [nvarchar](255) NULL,
	[StatusActiveFlag] [smallint] NULL,
	[StatusPrioritySeq] [decimal](19, 2) NULL,
	[StatusCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tlkpStatu__Statu__182C9B23]  DEFAULT (user_name()),
	[StatusCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tlkpStatu__Statu__1920BF5C]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkStatusKey] PRIMARY KEY CLUSTERED 
(
	[StatusKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tsysConfig_System]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tsysConfig_System](
	[SysConfigKey] [int] IDENTITY(1,1) NOT NULL,
	[SysConfigDatabaseName] [nvarchar](255) NULL,
	[AutoLogoutFeatureEnabled] [tinyint] NULL,
	[AutoLogoutFlag] [smallint] NULL,
	[AutoLogoutDelay] [decimal](19, 2) NULL,
	[AutoLogoutWarnCycle] [decimal](19, 2) NULL,
	[AutoLogoutMessage] [ntext] NULL,
	[SysConfigPayrollSchedule] [nvarchar](255) NULL,
	[SysConfigCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tsysConfi__SysCo__5629CD9C]  DEFAULT (user_name()),
	[SysConfigCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tsysConfi__SysCo__571DF1D5]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkSysConfigKey] PRIMARY KEY CLUSTERED 
(
	[SysConfigKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tlkpRevenueCategory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tlkpRevenueCategory](
	[RevenueCategoryKey] [int] IDENTITY(1,1) NOT NULL,
	[RevenueCategoryDesc] [nvarchar](255) NULL,
	[RevenueCategoryCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tlkpReven__Reven__145C0A3F]  DEFAULT (user_name()),
	[RevenueCategoryCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tlkpReven__Reven__15502E78]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkRevenueCategoryKey] PRIMARY KEY CLUSTERED 
(
	[RevenueCategoryKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tsysField]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tsysField](
	[TableName] [nvarchar](255) NULL,
	[FieldNumber] [int] NULL,
	[FieldName] [nvarchar](255) NULL,
	[FieldDesc] [nvarchar](255) NULL,
	[FieldType] [nvarchar](255) NULL,
	[FieldLength] [nvarchar](255) NULL,
	[TS] [timestamp] NULL
) 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].[tsysReport]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tsysReport](
	[RptKey] [int] NOT NULL,
	[RptName] [nvarchar](50) NULL,
	[RptType] [nvarchar](10) NULL,
	[RptObjectName] [nvarchar](50) NULL,
	[Seq] [smallint] NULL,
	[DtLastRan] [smalldatetime] NULL,
	[CreatedTS] [smalldatetime] NULL,
	[TS] [timestamp] NULL,
 CONSTRAINT [PK_tsysReport] PRIMARY KEY NONCLUSTERED 
(
	[RptKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tsysVersion]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tsysVersion](
	[FrontEndVersion] [nvarchar](255) NOT NULL,
	[VersionStatus] [nvarchar](5) NULL,
	[VersionWarning] [ntext] NULL,
	[VersionCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tsysVersi__Versi__52593CB8]  DEFAULT (user_name()),
	[VersionCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tsysVersi__Versi__534D60F1]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkFrontEndVersion] PRIMARY KEY CLUSTERED 
(
	[FrontEndVersion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tvalBillingType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tvalBillingType](
	[BillingTypeKey] [int] IDENTITY(1,1) NOT NULL,
	[BillingTypeName] [nvarchar](255) NULL,
	[BillingTypeCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tvalBilli__Billi__08EA5793]  DEFAULT (user_name()),
	[BillingTypeCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tvalBilli__Billi__09DE7BCC]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkBillingTypeKey] PRIMARY KEY CLUSTERED 
(
	[BillingTypeKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblFeature]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblFeature](
	[FeatureKey] [int] IDENTITY(1,1) NOT NULL,
	[ProjectKey] [int] NOT NULL,
	[ActivityKey] [int] NULL,
	[DefaultBillingTypeKey] [int] NULL CONSTRAINT [DF_tblFeature_DefaultBillingTypeKey]  DEFAULT (1),
	[FeatureName] [nvarchar](255) NULL,
	[FeatureDesc] [nvarchar](500) NULL,
	[FeatureCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tblFeatur__Featu__3B75D760]  DEFAULT (user_name()),
	[FeatureCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tblFeatur__Featu__3C69FB99]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkFeatureKey] PRIMARY KEY NONCLUSTERED 
(
	[FeatureKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tblProject]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblProject](
	[ProjectKey] [int] IDENTITY(1,1) NOT NULL,
	[CompanyKey] [int] NULL,
	[StatusKey] [int] NULL,
	[RevenueCategoryKey] [int] NULL,
	[ProjectName] [nvarchar](80) NULL,
	[ProjectPaidByCustomer] [smallint] NULL,
	[ProjectTechnology] [nvarchar](255) NULL,
	[ProjectDeliveryDate] [smalldatetime] NULL,
	[ProjectRate] [smallmoney] NULL,
	[ProjectPONbr] [nvarchar](255) NULL,
	[ProjectContactName] [nvarchar](50) NULL,
	[ProjectCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tblProjec__Proje__300424B4]  DEFAULT (user_name()),
	[ProjectCreateTS] [smalldatetime] NOT NULL CONSTRAINT [DF__tblProjec__Proje__30F848ED]  DEFAULT (getdate()),
	[ProjectWebSubFolder] [nvarchar](255) NULL,
	[ProjectCountAsPersonalTimeFlag] [smallint] NULL CONSTRAINT [DF_tblProject_ProjectCountAsPersonalTimeFlag]  DEFAULT (0),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkProjectKey] PRIMARY KEY CLUSTERED 
(
	[ProjectKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tblTask]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblTask](
	[TaskKey] [int] IDENTITY(1,1) NOT NULL,
	[FeatureKey] [int] NOT NULL,
	[PersonKey] [int] NULL,
	[TaskPriority] [tinyint] NULL,
	[TaskObject] [nvarchar](255) NULL,
	[TaskModificationNote] [ntext] NULL,
	[TaskDateStarted] [smalldatetime] NULL,
	[TaskDateCompleted] [smalldatetime] NULL,
	[TaskOriginalEst] [decimal](19, 2) NULL,
	[TaskCurrentEst] [decimal](19, 2) NULL,
	[TaskCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tblTask__TaskCre__412EB0B6]  DEFAULT (user_name()),
	[TaskCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tblTask__TaskCre__4222D4EF]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkTaskKey] PRIMARY KEY CLUSTERED 
(
	[TaskKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tblTimeCard]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblTimeCard](
	[TimeCardKey] [int] IDENTITY(1,1) NOT NULL,
	[PersonKey] [int] NOT NULL,
	[TaskKey] [int] NULL,
	[BillingTypeKey] [int] NULL,
	[TimeCardHours] [decimal](19, 2) NULL,
	[TimeCardDate] [smalldatetime] NULL,
	[TimeCardEntryNotes] [nvarchar](255) NULL,
	[TimeCardPaymentEnteredFlag] [int] NULL,
	[TimeCardInvoice] [nvarchar](40) NULL,
	[TimeCardCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tblTimeCa__TimeC__47DBAE45]  DEFAULT (user_name()),
	[TimeCardCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tblTimeCa__TimeC__48CFD27E]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkTimeCardKey] PRIMARY KEY NONCLUSTERED 
(
	[TimeCardKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[txrfProjectPerson]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[txrfProjectPerson](
	[ProjectPersonID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectKey] [int] NOT NULL,
	[PersonKey] [int] NOT NULL,
	[LeadFlag] [smallint] NULL CONSTRAINT [DF_txrfProjectPerson_LeadFlag]  DEFAULT (0),
	[ProjectPersonCreateTS] [smalldatetime] NULL CONSTRAINT [DF__txrfProje__Proje__72C60C4A]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkProjectPersonPersonKey] PRIMARY KEY NONCLUSTERED 
(
	[ProjectPersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tblPerson]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblPerson](
	[PersonKey] [int] IDENTITY(1,1) NOT NULL,
	[PaymentMethodKey] [int] NOT NULL,
	[PersonName] [nvarchar](80) NULL,
	[PersonInactiveFlag] [smallint] NULL,
	[PersonNetworkID] [nvarchar](50) NULL,
	[PersonDefaultTimeCard] [smallint] NULL CONSTRAINT [DF_tblPerson_PersonDefaultTimeCard]  DEFAULT (0),
	[PersonDefaultReportPrompt] [smallint] NULL CONSTRAINT [DF_tblPerson_PersonDefaultReportPrompt]  DEFAULT (0),
	[PersonDefaultOpenMaximized] [smallint] NULL CONSTRAINT [DF_tblPerson_PersonDefaultOpenMaximized]  DEFAULT (0),
	[PersonCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tblPerson__Perso__24927208]  DEFAULT (user_name()),
	[PersonCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tblPerson__Perso__25869641]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkPersonKey] PRIMARY KEY CLUSTERED 
(
	[PersonKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 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].[tblProjectDetail]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblProjectDetail](
	[ProjectDetailKey] [int] IDENTITY(1,1) NOT NULL,
	[ProjectKey] [int] NOT NULL,
	[ProjectDetailAllocatedHours] [smallmoney] NULL,
	[ProjectDetailDate] [smalldatetime] NULL,
	[ProjectDetailComment] [nvarchar](500) NULL,
	[ProjectDetailCreatedBy] [nvarchar](40) NULL CONSTRAINT [DF__tblProjec__Proje__0C85DE4D]  DEFAULT (user_name()),
	[ProjectDetailCreateTS] [smalldatetime] NULL CONSTRAINT [DF__tblProjec__Proje__0D7A0286]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [pkProjectDetailKey] PRIMARY KEY NONCLUSTERED 
(
	[ProjectDetailKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO

/******************** CREATE FUNCTIONS ******************************/

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jfn_TaskBillableTotal]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[jfn_TaskBillableTotal](@TaskKey [varchar](255))
RETURNS [decimal](19, 2) WITH EXECUTE AS CALLER
AS 
BEGIN 
/*
    Author : Tyler Ashbridge	
    Date :  10/13/2004
    Known Issues : none 
    Notes : Get Total Billable Hours for a Task
    Input Parameters : @TimecardDate
    Output Parameters : None
    Change Log :  10/13/2004 -- TRA -- Function created
*/
DECLARE @TotalBillable [decimal](19, 2) 

	SELECT @TotalBillable = Sum(tblTimeCard.TimeCardHours) 
	FROM tblTimeCard
	WHERE tblTimeCard.BillingTypeKey = 1 -- Billing Key 1 is Billable
		And tblTimeCard.TaskKey = @TaskKey

	SET @TotalBillable = ISNULL(@TotalBillable,0)	

	RETURN @TotalBillable
END
' 
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jfn_TimecardDateWeek]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[jfn_TimecardDateWeek](@TimecardDate [smalldatetime])
RETURNS [int] WITH EXECUTE AS CALLER
AS 
BEGIN 
/*
    Author : Tyler Ashbridge	
    Date :  10/13/2004
    Known Issues : none 
    Notes : Push Mondays to the next Week
    Input Parameters : @TimecardDate
    Output Parameters : None
    Change Log :  10/13/2004 -- TRA -- Function created
*/
DECLARE @TimecardWeek int
	
	SET @TimecardWeek = DATEPART(Week,@TimecardDate)
	IF DATENAME(dw,@TimecardDate ) = ''Sunday''
		BEGIN
			SET @TimecardWeek = @TimecardWeek - 1
		END
	RETURN @TimecardWeek
END

' 
END
GO

/***************** CREATE VIEWS *****************************/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_qtotProjectHours_Unbilled]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[jvw_qtotProjectHours_Unbilled]
AS
SELECT tblFeature.ProjectKey, SUM(tblTimeCard.TimeCardHours) 
    AS TotalUnbilledHours
FROM tblFeature RIGHT JOIN
    (tblTask RIGHT JOIN
    tblTimeCard ON tblTask.TaskKey = tblTimeCard.TaskKey) ON 
    tblFeature.FeatureKey = tblTask.FeatureKey
WHERE (((tblTimeCard.BillingTypeKey) <> 1))
GROUP BY tblFeature.ProjectKey
HAVING (((tblFeature.ProjectKey) IS NOT NULL))

'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_qtotProjectHours_Billed]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[jvw_qtotProjectHours_Billed]
AS
SELECT tblFeature.ProjectKey, SUM(tblTimeCard.TimeCardHours) 
    AS TotalBilledHours
FROM tblFeature RIGHT JOIN
    (tblTask RIGHT JOIN
    tblTimeCard ON tblTask.TaskKey = tblTimeCard.TaskKey) ON 
    tblFeature.FeatureKey = tblTask.FeatureKey
WHERE (((tblTimeCard.BillingTypeKey) = 1))
GROUP BY tblFeature.ProjectKey
HAVING (((tblFeature.ProjectKey) IS NOT NULL))

'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_qtotProjectHours]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[jvw_qtotProjectHours]
AS
SELECT tblFeature.ProjectKey, SUM(tblTimeCard.TimeCardHours) 
    AS TotalHours
FROM tblFeature RIGHT JOIN
    (tblTask RIGHT JOIN
    tblTimeCard ON tblTask.TaskKey = tblTimeCard.TaskKey) ON 
    tblFeature.FeatureKey = tblTask.FeatureKey
GROUP BY tblFeature.ProjectKey
HAVING (((tblFeature.ProjectKey) IS NOT NULL))

'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_TC_Proj_info]'))
EXEC dbo.sp_executesql @statement = N'
CREATE  VIEW [dbo].[vw_TC_Proj_info]
AS
SELECT     TC.PersonKey, PE.PersonName, PR.ProjectKey, PR.ProjectName, TA.TaskKey, TA.TaskObject, FE.FeatureKey, FE.FeatureName, TC.TimeCardHours, 
                      TC.TimeCardDate, BT.BillingTypeName, TC.TimeCardEntryNotes
FROM         dbo.tblTimeCard TC LEFT OUTER JOIN
                      dbo.tblPerson PE ON TC.PersonKey = PE.PersonKey LEFT OUTER JOIN
                      dbo.tvalBillingType BT ON TC.BillingTypeKey = BT.BillingTypeKey LEFT OUTER JOIN
                      dbo.tblTask TA ON TC.TaskKey = TA.TaskKey LEFT OUTER JOIN
                      dbo.tblFeature FE ON TA.FeatureKey = FE.FeatureKey LEFT OUTER JOIN
                      dbo.tblProject PR ON FE.ProjectKey = PR.ProjectKey

'
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].[jvw_AllocatedHours]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[jvw_AllocatedHours] AS SELECT tblProjectDetail.ProjectKey, SUM(tblProjectDetail.ProjectDetailAllocatedHours) AS TotalAllocatedHours FROM tblProjectDetail GROUP BY tblProjectDetail.ProjectKey HAVING (((tblProjectDetail.ProjectKey) IS NOT NULL)) '
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Hours_By_PerPrjFtr]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_Hours_By_PerPrjFtr]
	@PersonKey [int],
	@ProjectKey [int],
	@FeatureKey [int]
WITH EXECUTE AS CALLER
AS
DECLARE @strSQL varchar(3000), @strWHERE varchar(2000), @strAND varchar(20), @intWHERE int 
DECLARE @strOrderBy varchar(100), @strSelect varchar(100)
BEGIN
	SELECT @intWHERE = 0 -- Initialize Variables
	SELECT @strWHERE = ''''
	SELECT @strAND = ''''
	IF (@PersonKey = 0) 
	    BEGIN
		SELECT @strSelect = ''PersonKey,PersonName,''
		SELECT @strOrderBy = '',PersonName''
	    END -- BEGIN
	ELSE
	    BEGIN
		SELECT @strSelect = ''''
		SELECT @strOrderBy = ''''
		IF (@PersonKey > 0)
		    BEGIN
			SELECT @strWHERE = @strWHERE + @strAND + '' PersonKey = '' + ltrim(str(@PersonKey))
			SELECT @strAND = '' AND''
			SELECT @intWHERE = @intWHERE + 1
		    END -- BEGIN
--		END IF
	    END -- BEGIN
--	END IF
	IF (@ProjectKey > 0)
	    BEGIN
		SELECT @strWHERE = @strWHERE + @strAND + '' ProjectKey = '' + ltrim(str(@ProjectKey))
		SELECT @strAND = ''AND''
		SELECT @intWHERE = @intWHERE + 1
	    END -- BEGIN
--	END IF

	IF (@FeatureKey > 0)
	    BEGIN
		SELECT @strWHERE = @strWHERE + @strAND + '' FeatureKey = '' + ltrim(str(@FeatureKey))
		SELECT @strAND = '' AND''
		SELECT @intWHERE = @intWHERE + 1
		IF (@ProjectKey >= 0)
		    BEGIN
			SELECT @strSelect = @strSelect + ''TaskObject,''
		    END -- BEGIN
--		END IF
	    END -- BEGIN
--	END IF

	SELECT @strSQL = 	''SELECT '' + @strSelect + ''ProjectKey, ProjectName,FeatureKey, FeatureName,
					sum(timecardhours) as TotalHours,max(timecarddate) as LastTCdate 
				 FROM vw_TC_Proj_info ''

	IF (@intWHERE > 0) 
		SELECT @strSQL = @strSQL + '' WHERE '' + @strWHERE
--	END IF

	SELECT @strSQL = @strSQL + '' GROUP BY '' + @strSelect + ''ProjectKey,ProjectName, 
						FeatureKey, FeatureName ''
	SELECT @strSQL = @strSQL + '' ORDER BY  ProjectName, FeatureName '' + @strOrderBy
	print @strSQL
	EXEC (@strSQL)

END -- BEGIN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_qtotAllocatedHours]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[jvw_qtotAllocatedHours]
AS
SELECT tblProjectDetail.ProjectKey, 
    SUM(tblProjectDetail.ProjectDetailAllocatedHours) 
    AS TotalAllocatedHours
FROM tblProjectDetail
GROUP BY tblProjectDetail.ProjectKey
HAVING (((tblProjectDetail.ProjectKey) IS NOT NULL))
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jspFeatureOriginalEstTotal]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jspFeatureOriginalEstTotal]
	@FeatureKey [int],
	@SumOfTaskOriginalEst [decimal](19, 2) OUTPUT
WITH EXECUTE AS CALLER
AS
set @SumOfTaskOriginalEst =(
	SELECT SUM(CASE WHEN tblTask.TaskOriginalEst = NULL THEN 0 ELSE tblTask.TaskOriginalEst END) AS SumOfTaskOriginalEst
	FROM tblTask
	WHERE tblTask.FeatureKey = @FeatureKey)


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jspFeatureCurrentEstTotal]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jspFeatureCurrentEstTotal]
	@FeatureKey [int],
	@SumOfTaskCurrentEst [decimal](19, 2) OUTPUT
WITH EXECUTE AS CALLER
AS
set @SumOfTaskCurrentEst =(
	SELECT SUM(CASE WHEN tblTask.TaskCurrentEst = NULL THEN 0 ELSE tblTask.TaskCurrentEst END) AS SumOfTaskCurrentEst
	FROM tblTask
	WHERE tblTask.FeatureKey = @FeatureKey)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jspFeatureActualTotal]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jspFeatureActualTotal]
	@FeatureKey [int],
	@SumOfTimeCardHours [decimal](19, 2) OUTPUT
WITH EXECUTE AS CALLER
AS
set @SumOfTimeCardHours =(
	SELECT ISNULL (SUM(tblTimeCard.TimeCardHours),0)
	AS SumOfTimeCardHours
	FROM tblTask RIGHT OUTER JOIN tblTimeCard ON tblTask.TaskKey = tblTimeCard.TaskKey
WHERE tblTimeCard.BillingTypeKey = 1 
	AND tblTask.FeatureKey = @FeatureKey)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwOpenHours]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwOpenHours]
AS
SELECT tblProject.CompanyKey, tblCompany.CompanyName, 
    tblProject.ProjectKey, tblProject.ProjectName, 
    tblPerson.PersonName, tblTimeCard.TimeCardHours, 
    tblTimeCard.TimeCardDate, tblPerson.PaymentMethodKey, 
    tlkpPaymentMethod.PaymentMethodName, 
    tblProject.ProjectPaidByCustomer, 
    tblTimeCard.TimeCardPaymentEnteredFlag
FROM tblCompany RIGHT OUTER JOIN
    tblProject ON 
    tblCompany.CompanyKey = tblProject.CompanyKey RIGHT OUTER
     JOIN
    tblFeature ON 
    tblProject.ProjectKey = tblFeature.ProjectKey RIGHT OUTER JOIN
    tblTask ON 
    tblFeature.FeatureKey = tblTask.FeatureKey RIGHT OUTER JOIN
    tlkpPaymentMethod RIGHT OUTER JOIN
    tblTimeCard LEFT OUTER JOIN
    tblPerson ON 
    tblTimeCard.PersonKey = tblPerson.PersonKey ON 
    tlkpPaymentMethod.PaymentMethodKey = tblPerson.PaymentMethodKey
     ON tblTask.TaskKey = tblTimeCard.TaskKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwOpenInvoices]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwOpenInvoices]
AS
SELECT     dbo.tblCompany.CompanyKey, dbo.tblCompany.CompanyName, dbo.tblProject.ProjectKey, dbo.tblProject.ProjectName, dbo.tblProject.ProjectRate, 
                      dbo.tblProject.ProjectPONbr, dbo.tblPerson.PersonName, dbo.tblTimeCard.TimeCardHours, dbo.tblTimeCard.TimeCardDate, 
                      dbo.tblTimeCard.BillingTypeKey, dbo.tblTimeCard.TimeCardInvoice
FROM         dbo.tblProject LEFT OUTER JOIN
                      dbo.tblCompany ON dbo.tblProject.CompanyKey = dbo.tblCompany.CompanyKey RIGHT OUTER JOIN
                      dbo.tblFeature RIGHT OUTER JOIN
                      dbo.tblTask ON dbo.tblFeature.FeatureKey = dbo.tblTask.FeatureKey ON dbo.tblProject.ProjectKey = dbo.tblFeature.ProjectKey RIGHT OUTER JOIN
                      dbo.tblTimeCard LEFT OUTER JOIN
                      dbo.tblPerson ON dbo.tblTimeCard.PersonKey = dbo.tblPerson.PersonKey LEFT OUTER JOIN
                      dbo.tvalBillingType ON dbo.tblTimeCard.BillingTypeKey = dbo.tvalBillingType.BillingTypeKey ON dbo.tblTask.TaskKey = dbo.tblTimeCard.TaskKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectActivity]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwProjectActivity]
AS
SELECT ''Invoice'' = CASE WHEN TimeCardInvoice IS NOT NULL 
    THEN ''Invoice '' + tblTimeCard.TimeCardInvoice ELSE ''Not Invoiced''
     END, tblCompany.CompanyKey, tblCompany.CompanyName, 
    tblProject.ProjectKey, tblProject.ProjectName, 
    tblPerson.PersonKey, tblPerson.PersonName, 
    tblTimeCard.TimeCardDate, tblTimeCard.TimeCardHours, 
    tblFeature.FeatureKey, tblFeature.FeatureName, 
    tblTimeCard.BillingTypeKey
FROM tblCompany RIGHT OUTER JOIN
    tblProject ON 
    tblCompany.CompanyKey = tblProject.CompanyKey RIGHT OUTER
     JOIN
    tblFeature RIGHT OUTER JOIN
    tblTask RIGHT OUTER JOIN
    tblPerson RIGHT OUTER JOIN
    tblTimeCard ON 
    tblPerson.PersonKey = tblTimeCard.PersonKey ON 
    tblTask.TaskKey = tblTimeCard.TaskKey ON 
    tblFeature.FeatureKey = tblTask.FeatureKey ON 
    tblProject.ProjectKey = tblFeature.ProjectKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectTotalBillableHours]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwProjectTotalBillableHours]
AS
SELECT     dbo.tblProject.ProjectKey, SUM(dbo.tblTimeCard.TimeCardHours) AS TotalBillableHours
FROM         dbo.tblProject LEFT OUTER JOIN
                      dbo.tblFeature ON dbo.tblProject.ProjectKey = dbo.tblFeature.ProjectKey LEFT OUTER JOIN
                      dbo.tblTask LEFT OUTER JOIN
                      dbo.tblTimeCard ON dbo.tblTask.TaskKey = dbo.tblTimeCard.TaskKey ON dbo.tblFeature.FeatureKey = dbo.tblTask.FeatureKey
WHERE     (dbo.tblTimeCard.BillingTypeKey = 1)
GROUP BY dbo.tblProject.ProjectKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwHoursByPerson]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwHoursByPerson]
AS
SELECT ''Invoice'' = CASE WHEN TimeCardInvoice IS NOT NULL 
    THEN ''Invoice '' + tblTimeCard.TimeCardInvoice ELSE ''Not Invoiced''
     END, tblCompany.CompanyKey, tblCompany.CompanyName, 
    tblProject.ProjectKey, tblProject.ProjectName, 
    tblProject.RevenueCategoryKey, 
    tlkpRevenueCategory.RevenueCategoryDesc, 
    tblPerson.PersonKey, tblPerson.PersonName, 
    tblTimeCard.TimeCardDate, tblTimeCard.TimeCardHours, 
    tvalBillingType.BillingTypeName, 
    tvalBillingType.BillingTypeKey
FROM tblTimeCard LEFT OUTER JOIN
    tvalBillingType ON 
    tblTimeCard.BillingTypeKey = tvalBillingType.BillingTypeKey LEFT
     OUTER JOIN
    tblPerson ON 
    tblTimeCard.PersonKey = tblPerson.PersonKey FULL OUTER JOIN
    tblTask ON 
    tblTimeCard.TaskKey = tblTask.TaskKey FULL OUTER JOIN
    tblFeature LEFT OUTER JOIN
    tlkpRevenueCategory RIGHT OUTER JOIN
    tblCompany RIGHT OUTER JOIN
    tblProject ON 
    tblCompany.CompanyKey = tblProject.CompanyKey ON 
    tlkpRevenueCategory.RevenueCategoryKey = tblProject.RevenueCategoryKey
     ON tblFeature.ProjectKey = tblProject.ProjectKey ON 
    tblTask.FeatureKey = tblFeature.FeatureKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwRevenueReport]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwRevenueReport]
AS
SELECT ''Invoice'' = CASE WHEN TimeCardInvoice IS NOT NULL 
    THEN ''Invoice '' + tblTimeCard.TimeCardInvoice ELSE ''Not Invoiced''
     END, tblCompany.CompanyKey, tblCompany.CompanyName, 
    tblProject.ProjectKey, tblProject.ProjectName, 
    tblProject.RevenueCategoryKey, 
    tlkpRevenueCategory.RevenueCategoryDesc, 
    tblPerson.PersonKey, tblPerson.PersonName, 
    tblTimeCard.TimeCardDate, tblTimeCard.TimeCardHours, 
    tblTimeCard.BillingTypeKey
FROM tlkpRevenueCategory RIGHT OUTER JOIN
    tblCompany RIGHT OUTER JOIN
    tblProject ON 
    tblCompany.CompanyKey = tblProject.CompanyKey RIGHT OUTER
     JOIN
    tblFeature RIGHT OUTER JOIN
    tblTask ON tblFeature.FeatureKey = tblTask.FeatureKey ON 
    tblProject.ProjectKey = tblFeature.ProjectKey RIGHT OUTER JOIN
    tblPerson RIGHT OUTER JOIN
    tblTimeCard ON 
    tblPerson.PersonKey = tblTimeCard.PersonKey ON 
    tblTask.TaskKey = tblTimeCard.TaskKey ON 
    tlkpRevenueCategory.RevenueCategoryKey = tblProject.RevenueCategoryKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTaskBudgetBillableHours]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwTaskBudgetBillableHours]
AS
SELECT     dbo.tblProject.ProjectKey, dbo.tblFeature.FeatureKey, dbo.tblTask.TaskKey, SUM(dbo.tblTimeCard.TimeCardHours) AS BillableHours
FROM         dbo.tblTimeCard RIGHT OUTER JOIN
                      dbo.tblTask ON dbo.tblTimeCard.TaskKey = dbo.tblTask.TaskKey RIGHT OUTER JOIN
                      dbo.tblFeature ON dbo.tblTask.FeatureKey = dbo.tblFeature.FeatureKey RIGHT OUTER JOIN
                      dbo.tblProject ON dbo.tblFeature.ProjectKey = dbo.tblProject.ProjectKey
WHERE     (dbo.tblTimeCard.BillingTypeKey = 1)
GROUP BY dbo.tblProject.ProjectKey, dbo.tblFeature.FeatureKey, dbo.tblTask.TaskKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProduction]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwProduction]
AS
SELECT ''Invoice'' = CASE WHEN TimeCardInvoice IS NOT NULL 
    THEN ''Invoice '' + tblTimeCard.TimeCardInvoice ELSE ''Not Invoiced''
     END, tblCompany.CompanyKey, tblCompany.CompanyName, 
    tblProject.ProjectKey, tblProject.ProjectName, 
    tblProject.RevenueCategoryKey, 
    tlkpRevenueCategory.RevenueCategoryDesc, 
    tblPerson.PersonKey, tblPerson.PersonName, 
    tblTimeCard.TimeCardDate, tblTimeCard.TimeCardHours, 
    tblTimeCard.BillingTypeKey, 
    tvalBillingType.BillingTypeName
FROM tvalBillingType RIGHT OUTER JOIN
    tlkpRevenueCategory RIGHT OUTER JOIN
    tblCompany RIGHT OUTER JOIN
    tblProject ON 
    tblCompany.CompanyKey = tblProject.CompanyKey RIGHT OUTER
     JOIN
    tblFeature RIGHT OUTER JOIN
    tblTask ON tblFeature.FeatureKey = tblTask.FeatureKey ON 
    tblProject.ProjectKey = tblFeature.ProjectKey RIGHT OUTER JOIN
    tblPerson RIGHT OUTER JOIN
    tblTimeCard ON 
    tblPerson.PersonKey = tblTimeCard.PersonKey ON 
    tblTask.TaskKey = tblTimeCard.TaskKey ON 
    tlkpRevenueCategory.RevenueCategoryKey = tblProject.RevenueCategoryKey
     ON 
    tvalBillingType.BillingTypeKey = tblTimeCard.BillingTypeKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTaskBudgetNonBillableHours]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwTaskBudgetNonBillableHours]
AS
SELECT     dbo.tblProject.ProjectKey, dbo.tblFeature.FeatureKey, dbo.tblTask.TaskKey, SUM(dbo.tblTimeCard.TimeCardHours) AS NonBillableHours
FROM         dbo.tblTimeCard RIGHT OUTER JOIN
                      dbo.tblTask ON dbo.tblTimeCard.TaskKey = dbo.tblTask.TaskKey RIGHT OUTER JOIN
                      dbo.tblFeature ON dbo.tblTask.FeatureKey = dbo.tblFeature.FeatureKey RIGHT OUTER JOIN
                      dbo.tblProject ON dbo.tblFeature.ProjectKey = dbo.tblProject.ProjectKey
WHERE     (dbo.tblTimeCard.BillingTypeKey <> 1)
GROUP BY dbo.tblProject.ProjectKey, dbo.tblFeature.FeatureKey, dbo.tblTask.TaskKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectStatus_Tasks]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwProjectStatus_Tasks]
AS
SELECT tblTimeCard.TaskKey, 
    ''AssignedWorked'' = CASE WHEN tbltask.personkey = tbltimecard.personkey
     THEN ''Assigned'' ELSE ''Worked'' END, 
    SUM(tblTimeCard.TimeCardHours) AS SumOfTimeCardHours, 
    tblPerson.PersonName
FROM tblTask RIGHT JOIN
    (tblPerson RIGHT JOIN
    tblTimeCard ON 
    tblPerson.PersonKey = tblTimeCard.PersonKey) ON 
    tblTask.TaskKey = tblTimeCard.TaskKey
WHERE (((tblTimeCard.BillingTypeKey) = 1))
GROUP BY tblTimeCard.TaskKey, 
    CASE WHEN tbltask.personkey = tbltimecard.personkey THEN ''Assigned''
     ELSE ''Worked'' END, tblPerson.PersonName
'
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTimeCardPayrollPeriod]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwTimeCardPayrollPeriod]
AS
SELECT tblTimeCard.PersonKey, timecardkey, 
    tblTimeCard.TimeCardHours, tblTimeCard.TimeCardDate, 
    tlkpPayroll.PayrollEndDate
FROM tblTimeCard INNER JOIN
    tlkpPayroll ON 
    (tblTimeCard.TimeCardDate <= tlkpPayroll.PayrollEndDate) AND 
    (tblTimeCard.TimeCardDate >= (tlkpPayroll.PayrollEndDate - 13))
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectStatus_TaskActualBillableHours]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwProjectStatus_TaskActualBillableHours]
AS
SELECT taskkey, SUM(tblTimeCard.TimeCardHours) 
    AS SumOfTimeCardHours
FROM tblTimeCard
WHERE tblTimeCard.BillingTypeKey = 1
GROUP BY tblTimeCard.TaskKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwPersonalTimePayrollPeriod]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwPersonalTimePayrollPeriod]
AS
SELECT     dbo.tblTimeCard.PersonKey, dbo.tblTimeCard.TimeCardKey, dbo.tblTimeCard.TimeCardHours, dbo.tblTimeCard.TimeCardDate, 
                      dbo.tlkpPayroll.PayrollEndDate
FROM         dbo.tblTimeCard INNER JOIN
                      dbo.tlkpPayroll ON dbo.tblTimeCard.TimeCardDate <= dbo.tlkpPayroll.PayrollEndDate AND 
                      dbo.tblTimeCard.TimeCardDate >= dbo.tlkpPayroll.PayrollEndDate - 13 INNER JOIN
                      dbo.tblTask ON dbo.tblTimeCard.TaskKey = dbo.tblTask.TaskKey INNER JOIN
                      dbo.tblFeature ON dbo.tblTask.FeatureKey = dbo.tblFeature.FeatureKey INNER JOIN
                      dbo.tblProject ON dbo.tblFeature.ProjectKey = dbo.tblProject.ProjectKey
WHERE     (dbo.tblProject.ProjectCountAsPersonalTimeFlag = - 1)
'
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_TotalBillableHoursforaDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jsp_TotalBillableHoursforaDate]
	@PersonKey [int],
	@Date [smalldatetime],
	@TotalBillableHoursforaDate [decimal](19, 2) OUTPUT
WITH EXECUTE AS CALLER
AS
Set @TotalBillableHoursforaDate = ( Select sum(timecardhours)
from tblTimeCard
Where personkey = @personkey
and     month(timecarddate) = month(@date)
and     day(timecarddate) = day(@date)
and     year(timecarddate) = year(@date)
and    timecardpaymententeredflag = 0
and    billingtypekey = 1
Group by personkey)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_TotalHoursforaDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jsp_TotalHoursforaDate]
	@PersonKey [int],
	@Date [smalldatetime],
	@TotalHoursforaDate [decimal](19, 2) OUTPUT
WITH EXECUTE AS CALLER
AS
Set @TotalHoursforaDate = ( Select sum(timecardhours)
from tblTimeCard
Where personkey = @personkey
and     month(timecarddate) = month(@date)
and     day(timecarddate) = day(@date)
and     year(timecarddate) = year(@date)
and    timecardpaymententeredflag = 0
Group by personkey)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_TotalHoursforaWeek]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jsp_TotalHoursforaWeek]
	@PersonKey [int],
	@DateforWeekOf [smalldatetime],
	@TotalHoursforaWeek [decimal](19, 2) OUTPUT
WITH EXECUTE AS CALLER
AS
Set DateFirst  1  /* Monday is the first day of the week at J Street, change from the default of Sunday */
Set @TotalHoursforaWeek = (SELECT     SUM(TimeCardHours) 
FROM         tblTimeCard
WHERE    PersonKey = @PersonKey
AND (DATEPART(ww, TimeCardDate) = DATEPART(ww, @DateforWeekOf))
AND (DATEPART(yyyy, TimeCardDate) = DATEPART(yyyy, @DateforWeekOf))
AND (TimeCardPaymentEnteredFlag = 0)
GROUP BY PersonKey)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_TotalBillableHoursforaWeek]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jsp_TotalBillableHoursforaWeek]
	@PersonKey [int],
	@DateforWeekOf [smalldatetime],
	@TotalBillableHoursforaWeek [decimal](19, 2) OUTPUT
WITH EXECUTE AS CALLER
AS
Set DateFirst  1  /* Monday is the first day of the week at J Street, change from the default of Sunday */
Set @TotalBillableHoursforaWeek = (SELECT     SUM(TimeCardHours) 
FROM         tblTimeCard
WHERE    PersonKey = @PersonKey
AND (DATEPART(ww, TimeCardDate) = DATEPART(ww, @DateforWeekOf))
AND (DATEPART(yyyy, TimeCardDate) = DATEPART(yyyy, @DateforWeekOf))
AND (TimeCardPaymentEnteredFlag = 0)
AND (BillingTypeKey = 1)
GROUP BY PersonKey)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_WeekStartDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jsp_WeekStartDate]
	@Date [smalldatetime],
	@WeekStartDate [smalldatetime] OUTPUT
WITH EXECUTE AS CALLER
AS
Set Datefirst 1   
Set @WeekStartDate = Dateadd(day,(1-datepart(dw,@date)),@date)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_WeekEndDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jsp_WeekEndDate]
	@Date [smalldatetime],
	@WeekEndDate [smalldatetime] OUTPUT
WITH EXECUTE AS CALLER
AS
Set Datefirst 1   
Set @WeekEndDate = Dateadd(day,(7-datepart(dw,@date)),@date)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwcboProject]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwcboProject]
AS
SELECT TOP 100 PERCENT tblProject.ProjectKey, 
    tblProject.ProjectName, tblCompany.CompanyName
FROM (tlkpStatus RIGHT JOIN
    tblProject ON tlkpStatus.StatusKey = tblProject.StatusKey) LEFT 
    JOIN
    tblCompany ON 
    tblProject.CompanyKey = tblCompany.CompanyKey
WHERE tlkpStatus.StatusActiveFlag <> 0
ORDER BY tblProject.ProjectName
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwcboTask]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwcboTask]
AS
SELECT TOP 100 PERCENT tblTask.TaskKey, 
    tblTask.TaskObject AS Task, 
    tblFeature.FeatureName AS Feature, 
    LEFT(CAST([TaskModificationNote] AS varchar), 100) AS [Note], 
    tblFeature.ProjectKey
FROM tblFeature RIGHT JOIN
    tblTask ON 
    tblFeature.FeatureKey = tblTask.FeatureKey
ORDER BY tblTask.TaskObject, tblFeature.FeatureName
'
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jspGetHours_by_Day]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jspGetHours_by_Day]
	@PersonKey [int],
	@SD [varchar](30),
	@ED [varchar](30)
WITH EXECUTE AS CALLER
AS
declare	@StartDate smalldatetime
declare	@EndDate smalldatetime 


select @StartDate = cast(@SD as smalldatetime)
select @EndDate = cast(@ED as smalldatetime)



select sum(TimeCardHours) as TotalHours,
	sum(
	        case 
	           when BillingTypeKey = 1 then TimeCardHours
	           else 0
	        end 
	        ) as BillableHours,
	TimeCardDate 
from tblTimeCard
where PersonKey = @PersonKey
 and TimeCardDate >= @StartDate
 and TimeCardDate <= @EndDate
group by TimeCardDate
order by TimeCardDate' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectDetail]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwProjectDetail]
AS
SELECT     dbo.tblTimeCard.TimeCardInvoice, dbo.tblCompany.CompanyKey, dbo.tblCompany.CompanyName, dbo.tblProject.ProjectKey, 
                      dbo.tblProject.ProjectName, dbo.tblPerson.PersonKey, dbo.tblPerson.PersonName, dbo.tblTimeCard.TimeCardDate, dbo.tblTimeCard.TimeCardHours, 
                      dbo.tblFeature.FeatureKey, dbo.tblFeature.FeatureName, dbo.tblTimeCard.BillingTypeKey, dbo.tblTask.TaskObject
FROM         dbo.tblCompany RIGHT OUTER JOIN
                      dbo.tblProject ON dbo.tblCompany.CompanyKey = dbo.tblProject.CompanyKey RIGHT OUTER JOIN
                      dbo.tblFeature RIGHT OUTER JOIN
                      dbo.tblTask RIGHT OUTER JOIN
                      dbo.tblPerson RIGHT OUTER JOIN
                      dbo.tblTimeCard ON dbo.tblPerson.PersonKey = dbo.tblTimeCard.PersonKey ON dbo.tblTask.TaskKey = dbo.tblTimeCard.TaskKey ON 
                      dbo.tblFeature.FeatureKey = dbo.tblTask.FeatureKey ON dbo.tblProject.ProjectKey = dbo.tblFeature.ProjectKey
'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_WatchTasks]'))
EXEC dbo.sp_executesql @statement = N'/*
    Author : Tyler Ashbridge
    Date : 10/13/2004
    Notes : Tasks Exceeding Current Estimate for SQL Reporting.
    Change Log : 10/13/2004 TRA View created
*/
CREATE VIEW [dbo].[jvw_WatchTasks]
AS
SELECT     TOP 100 PERCENT dbo.tblProject.ProjectName, dbo.tblFeature.FeatureName, dbo.tblTask.TaskObject, dbo.tblTask.TaskOriginalEst, 
                      dbo.tblTask.TaskCurrentEst, dbo.jfn_TaskBillableTotal(dbo.tblTask.TaskKey) AS TaskBillableTotal, dbo.jfn_TaskBillableTotal(dbo.tblTask.TaskKey) 
                      - ISNULL(dbo.tblTask.TaskCurrentEst, 0) AS TaskDiff
FROM         dbo.tvalBillingType RIGHT OUTER JOIN
                      dbo.tblFeature ON dbo.tvalBillingType.BillingTypeKey = dbo.tblFeature.DefaultBillingTypeKey INNER JOIN
                      dbo.tblProject ON dbo.tblFeature.ProjectKey = dbo.tblProject.ProjectKey INNER JOIN
                      dbo.tblTask ON dbo.tblFeature.FeatureKey = dbo.tblTask.FeatureKey
WHERE     (dbo.tblProject.StatusKey = 3) AND (dbo.tblFeature.DefaultBillingTypeKey = 1) AND (dbo.jfn_TaskBillableTotal(dbo.tblTask.TaskKey) 
                      - ISNULL(dbo.tblTask.TaskCurrentEst, 0) > 0)
ORDER BY dbo.tblProject.ProjectName, dbo.tblFeature.FeatureName, dbo.tblTask.TaskObject
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_ProjectSummary_deleteme]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[jvw_ProjectSummary_deleteme]
AS
SELECT tblProject.ProjectKey, tblCompany.CompanyName, 
    tblProject.ProjectName, tlkpStatus.StatusName, 
    tlkpStatus.StatusPrioritySeq, tlkpStatus.StatusKey, 
    tblProject.ProjectTechnology, tblProject.ProjectDeliveryDate, 
    jvw_qtotProjectHours.TotalHours, 
    jvw_qtotProjectHours_Billed.TotalBilledHours, 
    jvw_qtotProjectHours_Unbilled.TotalUnbilledHours
FROM jvw_qtotProjectHours_Billed RIGHT OUTER JOIN
    jvw_qtotProjectHours_Unbilled RIGHT OUTER JOIN
    tblProject ON 
    jvw_qtotProjectHours_Unbilled.ProjectKey = tblProject.ProjectKey
     ON 
    jvw_qtotProjectHours_Billed.ProjectKey = tblProject.ProjectKey LEFT
     OUTER JOIN
    jvw_qtotProjectHours ON 
    tblProject.ProjectKey = jvw_qtotProjectHours.ProjectKey RIGHT OUTER
     JOIN
    tblCompany ON 
    tblProject.CompanyKey = tblCompany.CompanyKey LEFT OUTER
     JOIN
    tlkpStatus ON 
    tblProject.StatusKey = tlkpStatus.StatusKey
WHERE (tlkpStatus.StatusActiveFlag <> 0) AND 
    (tblProject.StatusKey IS NOT NULL) OR
    (tlkpStatus.StatusKey = 6)


'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_ProjectSummary]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[jvw_ProjectSummary]
AS
SELECT tblProject.ProjectKey, tblProject.ProjectName, 
    tblProject.ProjectTechnology, tblProject.ProjectDeliveryDate, 
    tblCompany.CompanyName, tlkpStatus.StatusName, 
    tlkpStatus.StatusKey, tlkpStatus.StatusPrioritySeq, 
    jvw_qtotProjectHours.TotalHours, 
    jvw_qtotAllocatedHours.TotalAllocatedHours, 
    jvw_qtotProjectHours_Billed.TotalBilledHours
FROM tblProject LEFT OUTER JOIN
    tlkpStatus ON 
    tblProject.StatusKey = tlkpStatus.StatusKey LEFT OUTER JOIN
    jvw_qtotProjectHours_Billed ON 
    tblProject.ProjectKey = jvw_qtotProjectHours_Billed.ProjectKey LEFT
     OUTER JOIN
    jvw_qtotAllocatedHours ON 
    tblProject.ProjectKey = jvw_qtotAllocatedHours.ProjectKey LEFT OUTER
     JOIN
    jvw_qtotProjectHours ON 
    tblProject.ProjectKey = jvw_qtotProjectHours.ProjectKey LEFT OUTER
     JOIN
    tblCompany ON 
    tblProject.CompanyKey = tblCompany.CompanyKey
WHERE (tlkpStatus.StatusActiveFlag <> 0) AND 
    (tblProject.StatusKey IS NOT NULL) OR
    (tlkpStatus.StatusKey = 6)
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectTotalAllocatedHours]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwProjectTotalAllocatedHours]
AS
SELECT     TOP 100 PERCENT dbo.tblProject.ProjectKey, SUM(dbo.tblProjectDetail.ProjectDetailAllocatedHours) AS TotalAllocatedHours
FROM         dbo.tblProject LEFT OUTER JOIN
                      dbo.tblProjectDetail ON dbo.tblProject.ProjectKey = dbo.tblProjectDetail.ProjectKey
GROUP BY dbo.tblProject.ProjectKey


'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTaskBudget]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwTaskBudget]
AS
SELECT     dbo.tblProject.ProjectName, dbo.tblFeature.FeatureName, dbo.tblTask.TaskObject, dbo.tblPerson.PersonName, 
                      dbo.tblTask.TaskCurrentEst AS Estimate, CONVERT(decimal(19, 2), dbo.tblTask.TaskCurrentEst - dbo.vwTaskBudgetBillableHours.BillableHours) 
                      AS HoursRemaining, CONVERT(decimal(19, 0), dbo.vwTaskBudgetBillableHours.BillableHours / dbo.tblTask.TaskCurrentEst * 100) AS PctComplete, 
                      dbo.tblPerson.PersonKey, dbo.tblProject.ProjectKey, dbo.vwTaskBudgetBillableHours.BillableHours, 
                      dbo.vwTaskBudgetNonBillableHours.NonBillableHours, MAX(dbo.tblTimeCard.TimeCardDate) AS LatestTimeCardDate
FROM         dbo.tblFeature LEFT OUTER JOIN
                      dbo.tblProject LEFT OUTER JOIN
                      dbo.tlkpStatus ON dbo.tblProject.StatusKey = dbo.tlkpStatus.StatusKey ON dbo.tblFeature.ProjectKey = dbo.tblProject.ProjectKey RIGHT OUTER JOIN
                      dbo.vwTaskBudgetNonBillableHours RIGHT OUTER JOIN
                      dbo.tblTask ON dbo.vwTaskBudgetNonBillableHours.TaskKey = dbo.tblTask.TaskKey LEFT OUTER JOIN
                      dbo.vwTaskBudgetBillableHours ON dbo.tblTask.TaskKey = dbo.vwTaskBudgetBillableHours.TaskKey LEFT OUTER JOIN
                      dbo.tblPerson ON dbo.tblTask.PersonKey = dbo.tblPerson.PersonKey ON dbo.tblFeature.FeatureKey = dbo.tblTask.FeatureKey RIGHT OUTER JOIN
                      dbo.tblTimeCard ON dbo.tblTask.TaskKey = dbo.tblTimeCard.TaskKey
WHERE     (dbo.tlkpStatus.StatusActiveFlag = - 1)
GROUP BY dbo.tblProject.ProjectName, dbo.tblFeature.FeatureName, dbo.tblPerson.PersonName, dbo.tblTask.TaskObject, dbo.tblTask.TaskCurrentEst, 
                      dbo.tblPerson.PersonKey, dbo.tblProject.ProjectKey, dbo.vwTaskBudgetBillableHours.BillableHours, 
                      dbo.vwTaskBudgetNonBillableHours.NonBillableHours
HAVING      (dbo.tblTask.TaskCurrentEst <> 0) AND (dbo.tblTask.TaskCurrentEst IS NOT NULL)
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTaskBudgetOLD]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwTaskBudgetOLD]
AS
SELECT     dbo.tblProject.ProjectName, dbo.tblFeature.FeatureName, dbo.tblTask.TaskObject, dbo.tblPerson.PersonName, 
                      dbo.tblTask.TaskCurrentEst AS Estimate, CONVERT(decimal(19, 2), dbo.tblTask.TaskCurrentEst - dbo.vwTaskBudgetBillableHours.BillableHours) 
                      AS HoursRemaining, CONVERT(decimal(19, 0), dbo.vwTaskBudgetBillableHours.BillableHours / dbo.tblTask.TaskCurrentEst * 100) AS PctComplete, 
                      dbo.tblPerson.PersonKey, dbo.tblProject.ProjectKey, dbo.vwTaskBudgetBillableHours.BillableHours, 
                      dbo.vwTaskBudgetNonBillableHours.NonBillableHours
FROM         dbo.tblFeature LEFT OUTER JOIN
                      dbo.tblProject LEFT OUTER JOIN
                      dbo.tlkpStatus ON dbo.tblProject.StatusKey = dbo.tlkpStatus.StatusKey ON dbo.tblFeature.ProjectKey = dbo.tblProject.ProjectKey RIGHT OUTER JOIN
                      dbo.vwTaskBudgetNonBillableHours RIGHT OUTER JOIN
                      dbo.tblTask ON dbo.vwTaskBudgetNonBillableHours.TaskKey = dbo.tblTask.TaskKey LEFT OUTER JOIN
                      dbo.vwTaskBudgetBillableHours ON dbo.tblTask.TaskKey = dbo.vwTaskBudgetBillableHours.TaskKey LEFT OUTER JOIN
                      dbo.tblPerson ON dbo.tblTask.PersonKey = dbo.tblPerson.PersonKey ON dbo.tblFeature.FeatureKey = dbo.tblTask.FeatureKey RIGHT OUTER JOIN
                      dbo.tblTimeCard ON dbo.tblTask.TaskKey = dbo.tblTimeCard.TaskKey
WHERE     (dbo.tlkpStatus.StatusActiveFlag = - 1)
GROUP BY dbo.tblProject.ProjectName, dbo.tblFeature.FeatureName, dbo.tblPerson.PersonName, dbo.tblTask.TaskObject, dbo.tblTask.TaskCurrentEst, 
                      dbo.tblPerson.PersonKey, dbo.tblProject.ProjectKey, dbo.vwTaskBudgetBillableHours.BillableHours, 
                      dbo.vwTaskBudgetNonBillableHours.NonBillableHours
HAVING      (dbo.tblTask.TaskCurrentEst <> 0) AND (dbo.tblTask.TaskCurrentEst IS NOT NULL)
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwtotTimecardPayrollPeriod]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwtotTimecardPayrollPeriod]
AS
SELECT vwTimeCardPayrollPeriod.PersonKey, 
    vwTimeCardPayrollPeriod.PayrollEndDate, 
    SUM(vwTimeCardPayrollPeriod.TimeCardHours) 
    AS SumOfTimeCardHours
FROM vwTimeCardPayrollPeriod
GROUP BY vwTimeCardPayrollPeriod.PersonKey, 
    vwTimeCardPayrollPeriod.PayrollEndDate
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectStatus]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwProjectStatus]
AS
SELECT tblCompany.CompanyName, tblProject.ProjectKey, 
    tblProject.ProjectName, tblFeature.FeatureKey, 
    tblFeature.FeatureName, tblTask.TaskKey, tblTask.TaskObject, 
    tblTask.TaskOriginalEst, tblTask.TaskCurrentEst, 
    vwProjectStatus_TaskActualBillableHours.SumOfTimeCardHours,
     tlkpStatus.StatusActiveFlag
FROM tblFeature RIGHT OUTER JOIN
    vwProjectStatus_TaskActualBillableHours RIGHT OUTER JOIN
    tblTask ON 
    vwProjectStatus_TaskActualBillableHours.taskkey = tblTask.TaskKey
     ON 
    tblFeature.FeatureKey = tblTask.FeatureKey LEFT OUTER JOIN
    tblCompany RIGHT OUTER JOIN
    tblProject ON 
    tblCompany.CompanyKey = tblProject.CompanyKey ON 
    tblFeature.ProjectKey = tblProject.ProjectKey LEFT OUTER JOIN
    tlkpStatus ON tblProject.StatusKey = tlkpStatus.StatusKey
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwtotPersonalTimePayrollPeriod]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwtotPersonalTimePayrollPeriod]
AS
SELECT     PersonKey, PayrollEndDate, SUM(TimeCardHours) AS SumOfPersonalTimeHours
FROM         dbo.vwPersonalTimePayrollPeriod
GROUP BY PersonKey, PayrollEndDate
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwProjectwithTotalAllocatedandTotalBillable]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwProjectwithTotalAllocatedandTotalBillable]
AS
SELECT     dbo.tblProject.*, dbo.vwProjectTotalAllocatedHours.TotalAllocatedHours  AS TotalAllocatedHours, 
                      dbo.vwProjectTotalBillableHours.TotalBillableHours AS TotalBillableHours
FROM         dbo.tblProject LEFT OUTER JOIN
                      dbo.vwProjectTotalBillableHours ON dbo.tblProject.ProjectKey = dbo.vwProjectTotalBillableHours.ProjectKey LEFT OUTER JOIN
                      dbo.vwProjectTotalAllocatedHours ON dbo.tblProject.ProjectKey = dbo.vwProjectTotalAllocatedHours.ProjectKey

'
GO






-- ******************************************************
-- Load data
-- ******************************************************
PRINT '';
PRINT '*** Loading Data';
GO

DECLARE @data_path NVARCHAR(256)

SET @data_path = '\Projects\KOB_Training\' 	--ENTER the Server's path to the data files

PRINT 'Loading tblActivity';

EXECUTE (N'BULK INSERT [dbo].[tblActivity] FROM ''' + @data_path + N'tblActivityData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');

PRINT 'Loading tblCompany';

EXECUTE (N'BULK INSERT [dbo].[tblCompany] FROM ''' + @data_path + N'tblCompanyData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tblDocument';

EXECUTE (N'BULK INSERT [dbo].tblDocument FROM ''' + @data_path + N'tblDocumentData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tblFeature';

EXECUTE (N'BULK INSERT [dbo].tblFeature FROM ''' + @data_path + N'tblFeatureData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tblPerson';

EXECUTE (N'BULK INSERT [dbo].tblPerson FROM ''' + @data_path + N'tblPersonData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tblProject';

EXECUTE (N'BULK INSERT [dbo].tblProject FROM ''' + @data_path + N'tblProjectData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tblProjectDetail';

EXECUTE (N'BULK INSERT [dbo].tblProjectDetail FROM ''' + @data_path + N'tblProjectDetailData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tblTask';

EXECUTE (N'BULK INSERT [dbo].tblTask FROM ''' + @data_path + N'tblTaskData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tblTimeCard';

EXECUTE (N'BULK INSERT [dbo].tblTimeCard FROM ''' + @data_path + N'tblTimeCardData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tlkpPaymentMethod';

EXECUTE (N'BULK INSERT [dbo].tlkpPaymentMethod FROM ''' + @data_path + N'tlkpPaymentMethodData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tlkpPayroll';

EXECUTE (N'BULK INSERT [dbo].tlkpPayroll FROM ''' + @data_path + N'tlkpPayrollData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tlkpRevenueCategory';

EXECUTE (N'BULK INSERT [dbo].tlkpRevenueCategory FROM ''' + @data_path + N'tlkpRevenueCategoryData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tlkpStatus';

EXECUTE (N'BULK INSERT [dbo].tlkpStatus FROM ''' + @data_path + N'tlkpStatusData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tsysConfig_System';

EXECUTE (N'BULK INSERT [dbo].tsysConfig_System FROM ''' + @data_path + N'tsysConfig_SystemData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tsysField';

EXECUTE (N'BULK INSERT [dbo].tsysField FROM ''' + @data_path + N'tsysFieldData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tsysReport';

EXECUTE (N'BULK INSERT [dbo].tsysReport FROM ''' + @data_path + N'tsysReportData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tsysVersion';

EXECUTE (N'BULK INSERT [dbo].tsysVersion FROM ''' + @data_path + N'tsysVersionData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading tvalBillingType';

EXECUTE (N'BULK INSERT [dbo].tvalBillingType FROM ''' + @data_path + N'tvalBillingTypeData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');


PRINT 'Loading txrfProjectPerson';

EXECUTE (N'BULK INSERT [dbo].txrfProjectPerson FROM ''' + @data_path + N'txrfProjectPersonData.txt''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''|'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');





-- ******************************************************
-- Create Indices
-- ******************************************************
PRINT '';
PRINT '*** Creating Indices';
GO

/* indices on tblFeature */
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblFeature]') AND name = N'indFeatureProjectKey')
CREATE CLUSTERED INDEX [indFeatureProjectKey] ON [dbo].[tblFeature] 
(
	[ProjectKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblFeature]') AND name = N'indFeatureActivityKey')
CREATE NONCLUSTERED INDEX [indFeatureActivityKey] ON [dbo].[tblFeature] 
(
	[ActivityKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblFeature]') AND name = N'indFeatureDefaultBillingTypeKey')
CREATE NONCLUSTERED INDEX [indFeatureDefaultBillingTypeKey] ON [dbo].[tblFeature] 
(
	[DefaultBillingTypeKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/* indices on tblProject */
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblProject]') AND name = N'indProjectCompanyKey')
CREATE NONCLUSTERED INDEX [indProjectCompanyKey] ON [dbo].[tblProject] 
(
	[CompanyKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblProject]') AND name = N'indProjectRevenueCategoryKey')
CREATE NONCLUSTERED INDEX [indProjectRevenueCategoryKey] ON [dbo].[tblProject] 
(
	[RevenueCategoryKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblProject]') AND name = N'indProjectStatusKey')
CREATE NONCLUSTERED INDEX [indProjectStatusKey] ON [dbo].[tblProject] 
(
	[StatusKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/* indices on tblTimeCard */
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTimeCard]') AND name = N'indTimeCardPersonKey')
CREATE CLUSTERED INDEX [indTimeCardPersonKey] ON [dbo].[tblTimeCard] 
(
	[PersonKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTimeCard]') AND name = N'indTimeCardBillingTypeKey')
CREATE NONCLUSTERED INDEX [indTimeCardBillingTypeKey] ON [dbo].[tblTimeCard] 
(
	[BillingTypeKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTimeCard]') AND name = N'indTimeCardInvoice')
CREATE NONCLUSTERED INDEX [indTimeCardInvoice] ON [dbo].[tblTimeCard] 
(
	[TimeCardInvoice] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTimeCard]') AND name = N'indTimeCardTaskKey')
CREATE NONCLUSTERED INDEX [indTimeCardTaskKey] ON [dbo].[tblTimeCard] 
(
	[TaskKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


/* indices on txrfProjPerson */
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[txrfProjectPerson]') AND name = N'indProjectPersonProjectKey')
CREATE CLUSTERED INDEX [indProjectPersonProjectKey] ON [dbo].[txrfProjectPerson] 
(
	[ProjectKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[txrfProjectPerson]') AND name = N'indProjectPersonPersonKey')
CREATE NONCLUSTERED INDEX [indProjectPersonPersonKey] ON [dbo].[txrfProjectPerson] 
(
	[PersonKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


/* indices on tblProjectDetail */
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblProjectDetail]') AND name = N'indProjectDetailProjectKey')
CREATE CLUSTERED INDEX [indProjectDetailProjectKey] ON [dbo].[tblProjectDetail] 
(
	[ProjectKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/* indices on tblTask */
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTask]') AND name = N'indTaskFeatureKey')
CREATE NONCLUSTERED INDEX [indTaskFeatureKey] ON [dbo].[tblTask] 
(
	[FeatureKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTask]') AND name = N'indTaskPersonKey')
CREATE NONCLUSTERED INDEX [indTaskPersonKey] ON [dbo].[tblTask] 
(
	[PersonKey] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO




-- ******************************************************
-- Activate Referential Integrity
-- ******************************************************
PRINT '';
PRINT '*** Activating Referential Integrity';
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkFeatureActivityKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFeature]'))
ALTER TABLE [dbo].[tblFeature]  WITH NOCHECK ADD  CONSTRAINT [fkFeatureActivityKey] FOREIGN KEY([ActivityKey])
REFERENCES [dbo].[tblActivity] ([ActivityKey])
GO
ALTER TABLE [dbo].[tblFeature] CHECK CONSTRAINT [fkFeatureActivityKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkFeatureDefaultBillingTypeKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFeature]'))
ALTER TABLE [dbo].[tblFeature]  WITH NOCHECK ADD  CONSTRAINT [fkFeatureDefaultBillingTypeKey] FOREIGN KEY([DefaultBillingTypeKey])
REFERENCES [dbo].[tvalBillingType] ([BillingTypeKey])
GO
ALTER TABLE [dbo].[tblFeature] CHECK CONSTRAINT [fkFeatureDefaultBillingTypeKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkFeatureProjectKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFeature]'))
ALTER TABLE [dbo].[tblFeature]  WITH NOCHECK ADD  CONSTRAINT [fkFeatureProjectKey] FOREIGN KEY([ProjectKey])
REFERENCES [dbo].[tblProject] ([ProjectKey])
GO
ALTER TABLE [dbo].[tblFeature] CHECK CONSTRAINT [fkFeatureProjectKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkPaymentMethodKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblPerson]'))
ALTER TABLE [dbo].[tblPerson]  WITH NOCHECK ADD  CONSTRAINT [fkPaymentMethodKey] FOREIGN KEY([PaymentMethodKey])
REFERENCES [dbo].[tlkpPaymentMethod] ([PaymentMethodKey])
GO
ALTER TABLE [dbo].[tblPerson] CHECK CONSTRAINT [fkPaymentMethodKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkCompanyKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProject]'))
ALTER TABLE [dbo].[tblProject]  WITH NOCHECK ADD  CONSTRAINT [fkCompanyKey] FOREIGN KEY([CompanyKey])
REFERENCES [dbo].[tblCompany] ([CompanyKey])
GO
ALTER TABLE [dbo].[tblProject] CHECK CONSTRAINT [fkCompanyKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkProjectRevenueCategoryKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProject]'))
ALTER TABLE [dbo].[tblProject]  WITH NOCHECK ADD  CONSTRAINT [fkProjectRevenueCategoryKey] FOREIGN KEY([RevenueCategoryKey])
REFERENCES [dbo].[tlkpRevenueCategory] ([RevenueCategoryKey])
GO
ALTER TABLE [dbo].[tblProject] CHECK CONSTRAINT [fkProjectRevenueCategoryKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkStatusKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProject]'))
ALTER TABLE [dbo].[tblProject]  WITH NOCHECK ADD  CONSTRAINT [fkStatusKey] FOREIGN KEY([StatusKey])
REFERENCES [dbo].[tlkpStatus] ([StatusKey])
GO
ALTER TABLE [dbo].[tblProject] CHECK CONSTRAINT [fkStatusKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkProjectDetailProjectKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProjectDetail]'))
ALTER TABLE [dbo].[tblProjectDetail]  WITH NOCHECK ADD  CONSTRAINT [fkProjectDetailProjectKey] FOREIGN KEY([ProjectKey])
REFERENCES [dbo].[tblProject] ([ProjectKey])
GO
ALTER TABLE [dbo].[tblProjectDetail] CHECK CONSTRAINT [fkProjectDetailProjectKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTaskFeatureKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTask]'))
ALTER TABLE [dbo].[tblTask]  WITH NOCHECK ADD  CONSTRAINT [fkTaskFeatureKey] FOREIGN KEY([FeatureKey])
REFERENCES [dbo].[tblFeature] ([FeatureKey])
GO
ALTER TABLE [dbo].[tblTask] CHECK CONSTRAINT [fkTaskFeatureKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTaskPersonKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTask]'))
ALTER TABLE [dbo].[tblTask]  WITH NOCHECK ADD  CONSTRAINT [fkTaskPersonKey] FOREIGN KEY([PersonKey])
REFERENCES [dbo].[tblPerson] ([PersonKey])
GO
ALTER TABLE [dbo].[tblTask] CHECK CONSTRAINT [fkTaskPersonKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTimeCardBillingTypeKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTimeCard]'))
ALTER TABLE [dbo].[tblTimeCard]  WITH NOCHECK ADD  CONSTRAINT [fkTimeCardBillingTypeKey] FOREIGN KEY([BillingTypeKey])
REFERENCES [dbo].[tvalBillingType] ([BillingTypeKey])
GO
ALTER TABLE [dbo].[tblTimeCard] CHECK CONSTRAINT [fkTimeCardBillingTypeKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTimeCardPersonKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTimeCard]'))
ALTER TABLE [dbo].[tblTimeCard]  WITH NOCHECK ADD  CONSTRAINT [fkTimeCardPersonKey] FOREIGN KEY([PersonKey])
REFERENCES [dbo].[tblPerson] ([PersonKey])
GO
ALTER TABLE [dbo].[tblTimeCard] CHECK CONSTRAINT [fkTimeCardPersonKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkTimeCardTaskKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTimeCard]'))
ALTER TABLE [dbo].[tblTimeCard]  WITH NOCHECK ADD  CONSTRAINT [fkTimeCardTaskKey] FOREIGN KEY([TaskKey])
REFERENCES [dbo].[tblTask] ([TaskKey])
GO
ALTER TABLE [dbo].[tblTimeCard] CHECK CONSTRAINT [fkTimeCardTaskKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkProjectPersonPersonKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[txrfProjectPerson]'))
ALTER TABLE [dbo].[txrfProjectPerson]  WITH NOCHECK ADD  CONSTRAINT [fkProjectPersonPersonKey] FOREIGN KEY([PersonKey])
REFERENCES [dbo].[tblPerson] ([PersonKey])
GO
ALTER TABLE [dbo].[txrfProjectPerson] CHECK CONSTRAINT [fkProjectPersonPersonKey]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fkProjectPersonProjectKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[txrfProjectPerson]'))
ALTER TABLE [dbo].[txrfProjectPerson]  WITH NOCHECK ADD  CONSTRAINT [fkProjectPersonProjectKey] FOREIGN KEY([ProjectKey])
REFERENCES [dbo].[tblProject] ([ProjectKey])
GO
ALTER TABLE [dbo].[txrfProjectPerson] CHECK CONSTRAINT [fkProjectPersonProjectKey]
GO


-- ******************************************************
-- Database has been created
-- ******************************************************
PRINT '';
PRINT '*** The database has been created';
GO

-------------------------------------------------------------------------------------------------
--Create Standard SQL Database Users
--This script will create the standard database users for client server projects.
--
--Where you see the word ENTER, you can modify the preceding text inside the single quotes to specify text for this application.
--  
--Created:        07/13/2007 by Tyler. 
--Change Notes:   03/27/2008 Tyler and Armen cleaned up some comments
--                03/07/2008 Tyler used Eric's new version update.
--                07/13/2007 Tyler  Script created.
--                09/10/2007 Eric   Added db_executor role, assigned execute permissions for all functions and sprocs to db_exector instead of individual user.
--                09/13/2007 Eric   Added script to change the ownership of objects and notes to make it easier to change db names.
--                -------------------------------------------------------------------------------------------------
    
-------------------------------------------------------------------------------------------------
--Database Section
-------------------------------------------------------------------------------------------------
DECLARE @DB_NAME  VARCHAR(132) SET @DB_NAME = DB_NAME()

--Set @DROP_USERS to 1 if you have restored over an existing database.
--  This ensures that users get correctly hooked up to server logins.  
--Set @DROP_USERS to 0 if the database already existed and you haven't restored over an existing database.
--  This preserves any special permission that existing users or roles may have.
DECLARE @DROP_USERS AS INT SET @DROP_USERS = 0 

DECLARE @strSQL AS VARCHAR(8000)
-------------------------------------------------------------------------------------------------
--Login Section
--Creates standard users for the database, assigns passwords 
-------------------------------------------------------------------------------------------------

DECLARE @dev_username        NVARCHAR(132)
DECLARE @dev_password        NVARCHAR(132)
DECLARE @user_username       NVARCHAR(132)
DECLARE @user_password       NVARCHAR(132)

SET @dev_password =         'DevPa55wd'   --ENTER the Dev Login password
SET @user_password =        'K0Bu53rpw'   --ENTER the User Login password

SET @dev_username       = @DB_NAME  + '_Dev'        --Derived
SET @user_username      = @DB_NAME  + '_User'       --Derived

IF @DROP_USERS = 1 
BEGIN
	EXEC ('DROP LOGIN '  + @dev_Username)
	EXEC ('DROP SCHEMA ' + @dev_Username)
	EXEC ('DROP USER '   + @dev_Username)

	EXEC ('DROP LOGIN '  + @user_Username)
	EXEC ('DROP SCHEMA ' + @user_Username)
	EXEC ('DROP USER '   + @user_Username)
END

--Add _Dev Login
IF NOT EXISTS( SELECT * FROM master.dbo.syslogins WHERE loginname = @dev_username)
BEGIN
    DECLARE @logindb1 NVARCHAR(132), @loginlang1 NVARCHAR(132) SELECT @logindb1 = N'Standards', @loginlang1 = N'us_english'
    if @logindb1 is null or not exists (SELECT * FROM master.dbo.sysdatabases WHERE name = @logindb1)
        SELECT @logindb1 = @DB_NAME
    if @loginlang1 is null or (not exists (SELECT * FROM master.dbo.syslanguages WHERE name = @loginlang1) and @loginlang1 <> N'us_english')
        SELECT @loginlang1 = @@language
    EXEC sp_addlogin @dev_username, @dev_password, @logindb1, @loginlang1
END

--Add _User Login
IF NOT EXISTS( SELECT * FROM master.dbo.syslogins WHERE loginname = @user_username)
BEGIN
    DECLARE @logindb NVARCHAR(132), @loginlang NVARCHAR(132) SELECT @logindb = N'Standards', @loginlang = N'us_english'
    if @logindb is null or not exists (SELECT * FROM master.dbo.sysdatabases WHERE name = @logindb)
        SELECT @logindb = @DB_NAME
    if @loginlang is null or (not exists (SELECT * FROM master.dbo.syslanguages WHERE name = @loginlang) and @loginlang <> N'us_english')
        SELECT @loginlang = @@language
    EXEC sp_addlogin @user_username, @user_password, @logindb, @loginlang
END

-------------------------------------------------------------------------------------------------
--Grant Access Section
--Grants access to the database for the specified users
-------------------------------------------------------------------------------------------------

IF NOT EXISTS( SELECT * FROM dbo.sysusers WHERE name = @dev_username)
    EXEC sp_grantdbaccess @dev_username, @dev_username

IF NOT EXISTS( SELECT * FROM dbo.sysusers WHERE name = @user_username)
    EXEC sp_grantdbaccess @user_username, @user_username

IF NOT EXISTS( SELECT * FROM dbo.sysusers WHERE name = N'guest' and hasdbaccess = 1)
    EXEC sp_grantdbaccess N'guest'

-------------------------------------------------------------------------------------------------
--Create db_executor role
-------------------------------------------------------------------------------------------------

IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = 'db_executor')
BEGIN
    CREATE ROLE db_executor AUTHORIZATION dbo
END

-------------------------------------------------------------------------------------------------
--Assign Users to Roles Section
--Places the users in the appropriate Roles based on their intended purpose in the database
-------------------------------------------------------------------------------------------------

--Dev user gets dbo  
--Note: db_owner provides full access, but granting the extra roles insures that the Dev user owns those other schemas.
--If you need to change the name of the dev user, use ALTER USER olddbname_Dev WITH NAME = newdbname_Dev;
EXEC sp_addrolemember N'db_owner', @dev_username
EXEC sp_addrolemember N'db_datareader', @dev_username
EXEC sp_addrolemember N'db_datawriter', @dev_username
EXEC sp_addrolemember N'db_executor' , @dev_username

--User user gets read and write access
EXEC sp_addrolemember N'db_datareader', @user_username
EXEC sp_addrolemember N'db_datawriter', @user_username
EXEC sp_addrolemember N'db_executor' , @user_username


--------------------------------------------------------
--Associate schema and role ownership from users to DBO
--------------------------------------------------------

ALTER AUTHORIZATION ON ROLE::db_owner        TO dbo
ALTER AUTHORIZATION ON ROLE::db_datareader   TO dbo
ALTER AUTHORIZATION ON ROLE::db_datawriter   TO dbo

ALTER AUTHORIZATION ON SCHEMA::db_owner      TO dbo
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO dbo
ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO dbo


-------------------------------------------------------------------------------------------------
--Grant Execute Permissions Section
--Gives the db_executor role execute permissions on all J Street Created Stored Procedures and Functions
--Changing ownership of an object drops all permissions to those objects.  
-------------------------------------------------------------------------------------------------

DECLARE @ExecSQL VARCHAR(8000)
DECLARE curGrants CURSOR FOR
    SELECT 'GRANT EXECUTE ON ' + NAME + ' TO db_executor' -- 
    FROM SYSOBJECTS
    WHERE 
        TYPE IN ('FN','P')         -- stored procedures and functions
        --AND LEFT(NAME,2) <> 'sp' -- not system procs
        --AND LEFT(NAME,2) <> 'dt' -- not vss procs
        AND (
			LEFT(NAME,4)    like 'jsp_' --J Street created stored procedures
			OR LEFT(NAME,4) like 'jfn_' --J Street created user defined functions
        )

OPEN curGrants

FETCH NEXT FROM curGrants INTO @ExecSQL

    WHILE @@FETCH_STATUS = 0
    BEGIN -- this will loop thru all your own procs and grant Execute privileges on each one
        EXEC(@ExecSQL)
        --Print @ExecSQL
    FETCH NEXT FROM curGrants INTO @ExecSQL
    END

CLOSE curGrants

DEALLOCATE curGrants
GO

-- ******************************************************
-- Database users have been created
-- ******************************************************
PRINT '';
PRINT '*** The database users have been created';
GO
