-- Add Stored Procedures

CREATE PROC qest_BuildTreeItem @Key nvarchar(255), @ParentKey nvarchar(255), @Order int, @caption nvarchar(100) AS
 INSERT INTO dbo.qestTreeStructure([Key],ParentKey,[Order],Caption) VALUES(@Key,@ParentKey,@Order,@caption) 
GO

CREATE PROC qest_BuildDocTreeItem @FilterID INT, @Order int, @Caption nvarchar(100), @SearchDefault nvarchar(100) AS
 DECLARE @TreeKey AS nvarchar(255)
 SELECT @TreeKey = 'ID80000000<' + RIGHT('00000000' + CAST(@FilterID AS nvarchar), 8) + @SearchDefault + '>'
 EXEC qest_BuildTreeItem @Key = @TreeKey, @ParentKey = 'ID80000000', @Order = @Order, @Caption = @Caption
GO

CREATE PROC qest_BuildBillingTreeItem @FilterID INT, @Order int, @Caption nvarchar(100), @SearchDefault nvarchar(100) AS
 DECLARE @TreeKey AS nvarchar(255)
 SELECT @TreeKey = 'ID80001000<' + RIGHT('00000000' + CAST(@FilterID AS nvarchar), 8) + @SearchDefault + '>'
 EXEC qest_BuildTreeItem @Key = @TreeKey, @ParentKey = 'ID80001000', @Order = @Order, @Caption = @Caption
GO

CREATE PROC qest_BuildDocFilter @Name nvarchar(100), @Qestids nvarchar(100), @TableName nvarchar(100), @SearchCriteria nvarchar(255) AS
 INSERT INTO dbo.DataFilters(FilterGroup,Name,SQL,Locked,SQLEdit,Grouping,Properties,GroupSQL,SearchCriteria)
 VALUES(80000,@Name,'SELECT ' + @TableName + '.QestID, ' + @TableName + '.QestComplete, SampleRegister.ProjectCode+'' - ''+SampleRegister.ProjectName AS ProjectCodeName, SampleRegister.QestID AS QestIDSampleRegister, SampleRegister.SampleID, SampleRegister.ClientCode, SampleRegister.ClientName, SampleRegister.ProjectCode, SampleRegister.ProjectName, SampleRegister.DateSampled, SampleRegister.WorkOrderID
FROM ' + @TableName + ' LEFT JOIN SampleRegister ON ' + @TableName + '.QestUniqueParentID = SampleRegister.QestUniqueID
WHERE SampleRegister.QestID = 1001 AND ' + @TableName + '.QestID IN(' + @Qestids + ') AND ( SampleRegister.ClientCode = {''Client ID''(ClientCode:20001)} AND SampleRegister.ProjectCode = {''Project ID''(ProjectCode:20002|ClientCode)} AND SampleRegister.WorkOrderID = {''WorkOrder ID''} AND SampleRegister.SampleID = {''Sample ID''} AND SampleRegister.DateSampled Between {#Date#} AND ' + @TableName + '.QestComplete = {Complete})
ORDER BY  SampleRegister.ProjectCode+'' - ''+SampleRegister.ProjectName, SampleRegister.WorkOrderID, SampleRegister.SampleID ASC'
 ,1,1,'SampleRegister.ProjectCode+'' - ''+SampleRegister.ProjectName,SampleRegister.WorkOrderID,','','',@SearchCriteria)
GO

CREATE PROC qest_BuildConcreteFilter AS
 INSERT INTO dbo.DataFilters(FilterGroup,Name,SQL,Locked,SQLEdit,Grouping,Properties,GroupSQL,SearchCriteria)
 VALUES(80000,'Concrete Compression Tests', 'SELECT DocumentConcreteDestructiveSpecimen.AgeDays, DocumentConcreteDestructiveSpecimen.TestDate, DocumentConcreteDestructive.QestID, DocumentConcreteDestructive.SampleID, DocumentConcreteDestructive.FieldSheetNo, SampleRegister.QestID AS QestIDSampleRegister
FROM (DocumentConcreteDestructiveSpecimen LEFT JOIN DocumentConcreteDestructive ON DocumentConcreteDestructiveSpecimen.QestUniqueParentID = DocumentConcreteDestructive.QestUniqueID) LEFT JOIN SampleRegister ON DocumentConcreteDestructive.QestUniqueParentID = SampleRegister.QestUniqueID
WHERE SampleRegister.QestID = 1601 AND DocumentConcreteDestructive.QestID = 16010 AND ( DocumentConcreteDestructive.SampleID = {''Sample ID''} AND DocumentConcreteDestructive.FieldSheetNo = {''Field Sheet ID''} AND DocumentConcreteDestructiveSpecimen.TestDate = {#Test Date#})
ORDER BY DocumentConcreteDestructiveSpecimen.Type ASC, DocumentConcreteDestructiveSpecimen.AgeDays ASC, DocumentConcreteDestructiveSpecimen.FieldSheetAndID ASC'
 ,1,1,'DocumentConcreteDestructiveSpecimen.Type,','','','Default:Default|DocumentConcreteDestructiveSpecimen.TestDate=[Today]|')
GO

CREATE PROC qest_BuildConcreteReportFilter AS
 INSERT INTO dbo.DataFilters(FilterGroup,Name,SQL,Locked,SQLEdit,Grouping,Properties,GroupSQL,SearchCriteria)
 VALUES(80000,'Concrete Test Reports', 'SELECT DocumentConcreteDestructiveSpecimen.TestDate, DocumentCertificates.QestID, SampleRegister.QestID AS QestIDSampleRegister, SampleRegister.SampleID, SampleRegister.ClientCode, SampleRegister.ProjectCode  FROM ((DocumentCertificates LEFT JOIN SampleRegister ON DocumentCertificates.QestUniqueParentID = SampleRegister.QestUniqueID) LEFT JOIN DocumentConcreteDestructive ON DocumentCertificates.QestUniqueParentID = DocumentConcreteDestructive.QestUniqueParentID) LEFT JOIN DocumentConcreteDestructiveSpecimen ON DocumentConcreteDestructive.QestUniqueID = DocumentConcreteDestructiveSpecimen.QestUniqueParentID WHERE SampleRegister.QestID = 1601 AND DocumentConcreteDestructive.QestID = 16010 AND DocumentCertificates.QestID = 18969 AND ( SampleRegister.ClientCode = {''Client ID''(ClientCode:20001)} AND SampleRegister.ProjectCode = {''Project ID''(ProjectCode:20002|ClientCode)} AND SampleRegister.SampleID = {''Sample ID''} AND DocumentConcreteDestructiveSpecimen.TestDate = {#Test Date#}) ORDER BY  SampleRegister.SampleID ASC'
 ,1,1,'','','','Default:Default|DocumentConcreteDestructiveSpecimen.TestDate=[Today]|')
GO

CREATE PROC qest_BuildWOCompleteFilter AS
 INSERT INTO dbo.DataFilters(FilterGroup,Name,SQL,Locked,SQLEdit,Grouping,Properties,GroupSQL,SearchCriteria)
 VALUES(80000,'Field Tests Ready to Report', 'SELECT WorkOrders.QestID, WorkOrders.QestUniqueID FROM WorkOrders WHERE WorkOrders.QestID = 101 AND (SELECT ISNULL(COUNT(*), 0) FROM SampleRegister WHERE QestUniqueParentID = WorkOrders.QestUniqueID AND (QestComplete = 0 OR QestComplete IS NULL)) = 0 AND (SELECT ISNULL(COUNT(*), 0) FROM DocumentCertificates WHERE QestUniqueParentID = WorkOrders.QestUniqueID AND (QestComplete = 0 OR QestComplete IS NULL)) > 0 ORDER BY WorkOrders.WorkOrderID'
 ,1,1,'','','','')
GO

CREATE PROC qest_BuildWOReportFilter AS
 INSERT INTO dbo.DataFilters(FilterGroup,Name,SQL,Locked,SQLEdit,Grouping,Properties,GroupSQL,SearchCriteria)
 VALUES(80000,'Field Test Reports', 'SELECT DocumentCertificates.QestID, DocumentCertificates.QestUniqueID FROM DocumentCertificates WHERE DocumentCertificates.QestID IN (10056,10281) AND (DocumentCertificates.QestComplete = 0 OR DocumentCertificates.QestComplete IS NULL) AND (SELECT ISNULL(COUNT(*), 0) FROM SampleRegister WHERE QestUniqueParentID = DocumentCertificates.QestUniqueParentID AND (QestComplete = 0 OR QestComplete IS NULL)) = 0'
 ,1,1,'','','','')
GO

CREATE PROC qest_BuildBillingFilter @Name nvarchar(100), @Printed nvarchar(1) AS
 INSERT INTO dbo.DataFilters(FilterGroup,Name,SQL,Locked,SQLEdit,Grouping,Properties,GroupSQL,SearchCriteria)
 VALUES(80000,@Name,'SELECT  DocumentInvoice.QestID, DocumentInvoice.InvoiceNo, DocumentInvoice.DateTime, DocumentInvoice.ClientCode, DocumentInvoice.ClientName, DocumentInvoice.ProjectCode, DocumentInvoice.ProjectName, DocumentInvoice.Printed FROM DocumentInvoice WHERE DocumentInvoice.QestID = 18019 AND ( DocumentInvoice.Printed = ' + @Printed + ') ORDER BY  DocumentInvoice.ClientName ASC, DocumentInvoice.InvoiceNo ASC'
 ,1,0,'','','SELECT Count(DocumentInvoice.QestID) AS QestID_Count, Avg(DocumentInvoice.QestID) AS QestID_Avg, Sum(DocumentInvoice.QestID) AS QestID_Sum, Min(DocumentInvoice.QestID) AS QestID_Min, Max(DocumentInvoice.QestID) AS QestID_Max, StDev(DocumentInvoice.QestID) AS QestID_StDevP, Var(DocumentInvoice.QestID) AS QestID_VarP FROM DocumentInvoice WHERE DocumentInvoice.QestID = 18019 AND ( DocumentInvoice.Printed = ' + @Printed + ')','')
GO



-- Delete existing tree items
DELETE FROM qestTreeStructure
GO


-- Add tree items
EXEC qest_BuildTreeItem @Key='ID2000000000020001', @ParentKey='', @order=1, @Caption ='Clients'
EXEC qest_BuildTreeItem @Key='ID2000000000020002', @ParentKey='', @order=2, @Caption ='Projects'
EXEC qest_BuildTreeItem @Key='ID00001000', @ParentKey='', @order=3, @Caption ='Work Orders'
EXEC qest_BuildTreeItem @Key='ID10000000', @ParentKey='', @order=4, @Caption ='Samples'
EXEC qest_BuildTreeItem @Key='ID80000000', @ParentKey='', @order=5, @Caption ='Tests/Documents'
EXEC qest_BuildTreeItem @Key='ID80001000', @ParentKey='', @order=6, @Caption ='Billing'
EXEC qest_BuildTreeItem @Key='ID30000000', @ParentKey='', @order=7, @Caption ='Equipment'
EXEC qest_BuildTreeItem @Key='ID20000000', @ParentKey='', @order=8, @Caption ='Lists'
EXEC qest_BuildTreeItem @Key='ID90000000', @ParentKey='', @order=9, @Caption ='Reports & Charts'
EXEC qest_BuildTreeItem @Key='ID70000000', @ParentKey='', @order=10, @Caption ='Specifications'


-- Delete system filters
DELETE FROM DataFilters WHERE Locked = 1
GO

-- Add Work Order Filter
INSERT INTO dbo.DataFilters(FilterGroup,Name,SQL,Locked,SQLEdit,Grouping,Properties,GroupSQL,SearchCriteria)
 VALUES(100,'Default',
 'SELECT WorkOrders.QestID, WorkOrders.QestComplete, WorkOrders.WorkOrderID, WorkOrders.ProjectCode, WorkOrders.ProjectName, WorkOrders.ClientCode, WorkOrders.ClientName, WorkOrders.WorkDate
FROM WorkOrders WHERE WorkOrders.QestID = 101 AND ( WorkOrders.ClientCode = {''Client ID''(ClientCode:20001)} AND WorkOrders.ProjectCode = {''Project ID''(ProjectCode:20002|ClientCode)} AND WorkOrders.WorkOrderID = {''WorkOrder ID''} AND WorkOrders.ClientRequestID = {''Client Req No''} AND WorkOrders.WorkDate Between {#Date#} AND WorkOrders.QestComplete = {Complete})
ORDER BY WorkOrders.ProjectCode+'' - ''+WorkOrders.ProjectName, WorkOrders.WorkOrderID ASC'
 ,1,1,'WorkOrders.ProjectCode+'' - ''+WorkOrders.ProjectName,','','','Default|WorkOrders.QestComplete=0|')

-- Add Sample Filter
INSERT INTO dbo.DataFilters(FilterGroup,Name,SQL,Locked,SQLEdit,Grouping,Properties,GroupSQL,SearchCriteria)
 VALUES(1000,'Default',
 'SELECT SampleRegister.QestID, SampleRegister.QestComplete, SampleRegister.SampleID, SampleRegister.ClientCode, SampleRegister.ClientName, SampleRegister.ProjectCode, SampleRegister.ProjectName, SampleRegister.DateSampled, SampleRegister.WorkOrderID, SampleRegister.ProjectCode+'' - ''+SampleRegister.ProjectName AS ProjectCodeName FROM SampleRegister WHERE SampleRegister.QestID IN(1001,1601) AND ( SampleRegister.ClientCode = {''Client ID''(ClientCode:20001)} AND SampleRegister.ProjectCode = {''Project ID''(ProjectCode:20002|ClientCode)} AND SampleRegister.WorkOrderID = {''WorkOrder ID''} AND SampleRegister.SampleID = {''Sample ID''} AND SampleRegister.DateSampled Between {#Date#} AND SampleRegister.QestComplete = {Complete}) ORDER BY SampleRegister.ProjectCode+'' - ''+SampleRegister.ProjectName, SampleRegister.SampleID ASC'
 ,1,1,'SampleRegister.ProjectCode+'' - ''+SampleRegister.ProjectName,','','','Default|SampleRegister.QestComplete=0|')

-- Add Concrete Only Sample Filter
INSERT INTO dbo.DataFilters(FilterGroup,Name,SQL,Locked,SQLEdit,Grouping,Properties,GroupSQL,SearchCriteria)
 VALUES(1000,'Concrete Samples',
 'SELECT SampleRegister.QestID, SampleRegister.QestComplete, SampleRegister.SampleID, SampleRegister.ClientCode, SampleRegister.ClientName, SampleRegister.ProjectCode, SampleRegister.ProjectName, SampleRegister.DateSampled, DocumentConcreteDestructive.FieldSheetNo, SampleRegister.ProjectCode+'' - ''+SampleRegister.ProjectName AS ProjectCodeName FROM (SampleRegister LEFT JOIN DocumentConcreteDestructive ON DocumentConcreteDestructive.QestUniqueParentID = SampleRegister.QestUniqueID) WHERE SampleRegister.QestID IN(1601) AND ( SampleRegister.ClientCode = {''Client ID''(ClientCode:20001)} AND SampleRegister.ProjectCode = {''Project ID''(ProjectCode:20002|ClientCode)} AND DocumentConcreteDestructive.FieldSheetNo = {''Field Sheet ID''} AND SampleRegister.SampleID = {''Sample ID''} AND SampleRegister.DateSampled Between {#Date#} AND SampleRegister.QestComplete = {Complete}) ORDER BY SampleRegister.ProjectCode+'' - ''+SampleRegister.ProjectName, SampleRegister.SampleID ASC'
 ,1,1,'SampleRegister.ProjectCode+'' - ''+SampleRegister.ProjectName,','','','')


-- Add Document Filters

DECLARE @filterUID AS int
DECLARE @OrderId AS int

EXEC qest_BuildConcreteFilter
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 1, @SearchDefault = 'Default:Default', @Caption = 'Concrete Compression Tests'

EXEC qest_BuildConcreteReportFilter
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 2, @SearchDefault = 'Default:Default', @Caption = 'Concrete Test Reports'

EXEC qest_BuildDocFilter @Name='Field Density Tests', @Qestids='110241,10302', @TableName = 'DocumentAggSoilFieldDensity', @SearchCriteria = 'Default:Data Entry|DocumentAggSoilFieldDensity.QestComplete=0|'
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 10, @SearchDefault = 'Default:Data Entry', @Caption = 'Field Density Tests'

EXEC qest_BuildDocFilter @Name='Hilf Tests', @Qestids='10055,10057', @TableName = 'DocumentConvertedWetDensity', @SearchCriteria = 'Default:Oversize and Moisture Prep|DocumentConvertedWetDensity.QestComplete=0|
Default:Default|DocumentConvertedWetDensity.QestComplete=0|'
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 20, @SearchDefault = 'Default:Oversize and Moisture Prep', @Caption = 'Oversize and Moisture Prep (Hilf)'
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 30, @SearchDefault = 'Default:Default', @Caption = 'Hilf Density Tests'

EXEC qest_BuildDocFilter @Name='Compaction Tests', @Qestids='10280,10282', @TableName = 'DocumentAggSoilCompaction', @SearchCriteria = 'Default:Oversize and Moisture Prep|DocumentAggSoilCompaction.QestComplete=0|
Default:Compaction Results|DocumentAggSoilCompaction.QestComplete=0|'
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 21, @SearchDefault = 'Default:Oversize and Moisture Prep', @Caption = 'Oversize and Moisture Prep (Full)'
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 50, @SearchDefault = 'Default:Compaction Results', @Caption = 'Compaction Results'

EXEC qest_BuildDocFilter @Name='MDD Tests', @Qestids='10032,10033,10034,10035', @TableName = 'DocumentMaximumDryDensity', @SearchCriteria = 'Default:Default|DocumentMaximumDryDensity.QestComplete=0|'
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 31, @SearchDefault = 'Default:Default', @Caption = 'Maximum Dry Density Tests'

EXEC qest_BuildDocFilter @Name='Moisture Tests', @Qestids='10060,10061,10062,10064,10066,10067', @TableName = 'DocumentMoistureContent', @SearchCriteria = 'Default:Moisture Completion|DocumentMoistureContent.QestComplete=0|'
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 40, @SearchDefault = 'Default:Moisture Completion', @Caption = 'Moisture Completion'

EXEC qest_BuildWOCompleteFilter
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 60, @SearchDefault = 'Default:Default', @Caption = 'Field Tests Ready to Report'

EXEC qest_BuildWOReportFilter
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildDocTreeItem @FilterID = @FilterUID, @Order = 60, @SearchDefault = 'Default:Default', @Caption = 'Field Test Reports'

EXEC qest_BuildBillingFilter @Name='Unprinted Invoices', @Printed = '0'
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildBillingTreeItem @FilterID = @FilterUID, @Order = 10, @SearchDefault = 'Default:Default', @Caption = 'Unprinted Invoices'

EXEC qest_BuildBillingFilter @Name='Printed Invoices', @Printed = '1'
SELECT @filterUID = @@IDENTITY
EXEC qest_BuildBillingTreeItem @FilterID = @FilterUID, @Order = 20, @SearchDefault = 'Default:Default', @Caption = 'Printed Invoices'



-- Remove Stored Procedures
DROP PROCEDURE qest_BuildTreeItem
GO

DROP PROCEDURE qest_BuildDocTreeItem
GO

DROP PROCEDURE qest_BuildBillingTreeItem
GO

DROP PROCEDURE qest_BuildDocFilter
GO

DROP PROCEDURE qest_BuildBillingFilter
GO

DROP Procedure qest_BuildConcreteFilter
GO

DROP Procedure qest_BuildConcreteReportFilter
GO

DROP Procedure qest_BuildWOCompleteFilter
GO

DROP Procedure qest_BuildWOReportFilter
GO