QESTLab AUCOF WIP CompleteWork

--For a billing document to be picked up, it must have at least one sibling signed test report.
--That is, the test report must exist at the same level in the hierarcy as the billing document. 
--There can be more than one test report, but only one is required to be signed for the billing 
--document to be picked up.
--For example, for concrete the test report is attached to the concrete sample group, as is the 
--billing document. For workorders, one of the test reports (CWD, DDR) is attached to the workorder,
--as is the billing document.
--Note that a billing document is equivalent to a charge document.


--Drop WIP views if were already added:
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qestfilterProject_WIP_Complete')
  DROP VIEW qestfilterProject_WIP_Complete
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qestfilterLab_WIP_Complete')
  DROP VIEW qestfilterLab_WIP_Complete
GO

CREATE VIEW qestfilterProject_WIP_Complete AS 
(
	SELECT Charges.QESTOwnerLabNo, Charges.ProjectCode, Charges.ProjectName, Charges.ClientName, Sum(Charges.WIP) AS WIP FROM
	(
		SELECT DISTINCT DocumentChargeSingle.QestOwnerLabNo As QESTOwnerLabNo, DocumentChargeSingle.ProjectCode, DocumentChargeSingle.ProjectName, 
		DocumentChargeSingle.ClientName, DocumentChargeSingle.TotalCharge AS WIP, DocumentCharge.QESTUniqueParentID FROM DocumentChargeSingle 
		INNER JOIN DocumentCharge ON DocumentChargeSingle.QESTUniqueParentID = DocumentCharge.QESTUniqueID
		INNER JOIN DocumentCertificates ON (DocumentCharge.QESTUniqueParentID = DocumentCertificates.QESTUniqueParentID AND DocumentCharge.QESTParentID = DocumentCertificates.QESTParentID)
		WHERE (DocumentChargeSingle.Printed = 0 OR DocumentChargeSingle.Printed IS NULL) AND (DocumentCertificates.SignatoryID > 0)
	) AS Charges
	Group By Charges.ProjectCode, Charges.ProjectName, Charges.ClientName, Charges.QESTOwnerLabNo
)
GO

CREATE VIEW qestfilterLab_WIP_Complete AS 
(
	SELECT Charges.QESTOwnerLabNo, Sum(Charges.WIP) AS WIP, Charges.LabCode FROM
	(
		SELECT DISTINCT DocumentChargeSingle.QestOwnerLabNo As QESTOwnerLabNo, DocumentChargeSingle.TotalCharge AS WIP, 
		Laboratory.CounterPrefix AS LabCode, DocumentCharge.QESTUniqueParentID FROM DocumentChargeSingle 
		INNER JOIN Laboratory On DocumentChargeSingle.QESTOwnerLabNo = Laboratory.LabNo 
		INNER JOIN DocumentCharge ON DocumentChargeSingle.QESTUniqueParentID = DocumentCharge.QESTUniqueID
		INNER JOIN DocumentCertificates ON (DocumentCharge.QESTUniqueParentID = DocumentCertificates.QESTUniqueParentID AND DocumentCharge.QESTParentID = DocumentCertificates.QESTParentID)
		WHERE (DocumentChargeSingle.Printed = 0 OR DocumentChargeSingle.Printed IS NULL) AND (DocumentCertificates.SignatoryID > 0)
	) AS Charges
	Group By Charges.QESTOwnerLabNo, Charges.LabCode
)
GO


--Drop filters if were already added:
DELETE FROM DataFilters WHERE Name = 'WIP Complete by Project'
DELETE FROM DataFilters WHERE Name = 'WIP Complete by Lab'
GO

--Insert WIP filters to use the WIP_Complete views.
INSERT INTO DataFilters(FilterGroup, Name, SQL, Locked, SQLEdit, Grouping, Properties, GroupSQL, SearchCriteria) 
	VALUES(90000, 'WIP Complete by Project', 'SELECT   qestfilterProject_WIP_Complete.ProjectCode, qestfilterProject_WIP_Complete.ProjectName, qestfilterProject_WIP_Complete.ClientName, qestfilterProject_WIP_Complete.WIP  FROM qestfilterProject_WIP_Complete WHERE Not QESTOwnerLabNo IS NULL', 0, 0, '', '', 'SELECT Count(qestfilterProject_WIP_Complete.WIP) AS WIP_Count, Avg(qestfilterProject_WIP_Complete.WIP) AS WIP_Avg, Sum(qestfilterProject_WIP_Complete.WIP) AS WIP_Sum, Min(qestfilterProject_WIP_Complete.WIP) AS WIP_Min, Max(qestfilterProject_WIP_Complete.WIP) AS WIP_Max, StDev(qestfilterProject_WIP_Complete.WIP) AS WIP_StDevP, Var(qestfilterProject_WIP_Complete.WIP) AS WIP_VarP FROM qestfilterProject_WIP_Complete WHERE Not QESTOwnerLabNo IS NULL', '')
GO
INSERT INTO DataFilters(FilterGroup, Name, SQL, Locked, SQLEdit, Grouping, Properties, GroupSQL, SearchCriteria) 
	VALUES(90000, 'WIP Complete by Lab', 'SELECT  qestfilterLab_WIP_Complete.QESTOwnerLabNo, qestfilterLab_WIP_Complete.WIP, qestfilterLab_WIP_Complete.LabCode FROM qestfilterLab_WIP_Complete WHERE Not QESTOwnerLabNo IS NULL', 0, 0, '', '', 'SELECT Count(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Count, Avg(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Avg, Sum(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Sum, Min(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Min, Max(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Max, StDev(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_StDevP, Var(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_VarP, Count(qestfilterLab_WIP_Complete.WIP) AS WIP_Count, Avg(qestfilterLab_WIP_Complete.WIP) AS WIP_Avg, Sum(qestfilterLab_WIP_Complete.WIP) AS WIP_Sum, Min(qestfilterLab_WIP_Complete.WIP) AS WIP_Min, Max(qestfilterLab_WIP_Complete.WIP) AS WIP_Max, StDev(qestfilterLab_WIP_Complete.WIP) AS WIP_StDevP, Var(qestfilterLab_WIP_Complete.WIP) AS WIP_VarP FROM qestfilterLab_WIP_Complete WHERE Not QESTOwnerLabNo IS NULL', '')
GO


--Drop reports if were already added:
DELETE FROM Reports WHERE Name = 'Project WIP Complete Report'
DELETE FROM Reports WHERE Name = 'Lab WIP Complete Report'
GO

--Insert WIP reports to use the WIP_Complete filters
INSERT INTO Reports(QestID, QestCreatedBy, QestCreatedDate, QestModifiedBy, QestModifiedDate, QestOwnerLabNo, Name, ReportGroup, FilterName, Title, SubTitle, DocumentNo, PageWidth, PageHeight, Orientation, PageMarginLeft, PageMarginRight, PageMarginTop, PageMarginBottom, ShowLimits, LimitFontBold, LimitFontItalic, LimitFontColour, LimitShadingColour, LineHeight, TitleFontName, TitleFontSize, TitleFontBold, TitleFontItalic, SubTitleFontName, SubTitleFontSize, SubTitleFontBold, SubTitleFontItalic, HeaderFontName, HeaderFontSize, HeaderFontBold, HeaderFontItalic, DetailFontName, DetailFontSize, DetailFontBold, DetailFontItalic, Fields, Locked, StatsOnly, StatsLine, LineEachRow, GroupNewPage, GroupForLastN, LastN, LastNDateField, Chart, ChartSize1, ChartSize2, ChartSize3, ChartSize4, ChartSize5, Properties, CustomReportObject, Columns, AlternateSpecification, AlternateSpecificationName, SuppressSearchCriteria, DifferenceValues) VALUES(90001, 3, CONVERT(DATETIME, '2006-03-01 8:45:50', 102), 0, CONVERT(DATETIME, '2006-03-01 8:45:50', 102), 0, 'Project WIP Complete Report', 'Billing', 'WIP Complete by Project', '', '', '', 21, 29.7, 1, 1, 1, 1, 1, 0, 0, 0, -1, -1, 210, 'Arial', 8.25, 0, 0, 'Arial', 8.25, 0, 0, 'Arial', 8.25, -1, 0, 'Arial', 8.25, 0, 0, 'QESTOwnerLabNo,ProjectCode,ProjectName,ClientName,WIP,', 0, 0, 0, 0, 0, 0, '', '', 0, Null, Null, Null, Null, Null, '', Null, 'START[COLUMN1]
Header=Project Code
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=False
StatVarCoef=False
FieldName=ProjectCode
Alignment=0
Divider=2
Width=2.47619047619048
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN1]
START[COLUMN2]
Header=Project Name
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=False
StatVarCoef=False
FieldName=ProjectName
Alignment=0
Divider=2
Width=6.52380952380952
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN2]
START[COLUMN3]
Header=Client Name
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=False
StatVarCoef=False
FieldName=ClientName
Alignment=0
Divider=2
Width=6.47089947089947
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN3]
START[COLUMN4]
Header=WIP ($)
DataFormat=0.00
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=True
StatVarCoef=False
FieldName=WIP
Alignment=1
Divider=1
Width=2.63492063492063
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN4]
', 0, '', 0, 0)
GO

INSERT INTO Reports(QestID, QestCreatedBy, QestCreatedDate, QestModifiedBy, QestModifiedDate, QestOwnerLabNo, Name, ReportGroup, FilterName, Title, SubTitle, DocumentNo, PageWidth, PageHeight, Orientation, PageMarginLeft, PageMarginRight, PageMarginTop, PageMarginBottom, ShowLimits, LimitFontBold, LimitFontItalic, LimitFontColour, LimitShadingColour, LineHeight, TitleFontName, TitleFontSize, TitleFontBold, TitleFontItalic, SubTitleFontName, SubTitleFontSize, SubTitleFontBold, SubTitleFontItalic, HeaderFontName, HeaderFontSize, HeaderFontBold, HeaderFontItalic, DetailFontName, DetailFontSize, DetailFontBold, DetailFontItalic, Fields, Locked, StatsOnly, StatsLine, LineEachRow, GroupNewPage, GroupForLastN, LastN, LastNDateField, Chart, ChartSize1, ChartSize2, ChartSize3, ChartSize4, ChartSize5, Properties, CustomReportObject, Columns, AlternateSpecification, AlternateSpecificationName, SuppressSearchCriteria, DifferenceValues) VALUES(90001, 3, CONVERT(DATETIME, '2006-03-01 8:46:14', 102), 0, CONVERT(DATETIME, '2006-03-01 14:31:38', 102), 0, 'Lab WIP Complete Report', 'Billing', 'WIP Complete by Lab', '', '', '', 21, 29.7, 1, 1, 1, 1, 1, 0, 0, 0, -1, -1, 210, 'Arial', 8.25, 0, 0, 'Arial', 8.25, 0, 0, 'Arial', 8.25, -1, 0, 'Arial', 8.25, 0, 0, 'QESTOwnerLabNo,WIP,LabCode,', 0, 0, 0, 0, 0, 0, '', '', 0, Null, Null, Null, Null, Null, '', Null, 'START[COLUMN1]
Header=Lab No
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=False
StatVarCoef=False
FieldName=QESTOwnerLabNo
Alignment=0
Divider=2
Width=1.94708994708995
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN1]
START[COLUMN2]
Header=Lab Code
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=True
StatVarCoef=False
FieldName=LabCode
Alignment=0
Divider=1
Width=2
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=
HighlightMarginalColour=0
HighlightCritical=
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN2]
START[COLUMN3]
Header=WIP ($)
DataFormat=0.00
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=True
StatVarCoef=False
FieldName=WIP
Alignment=1
Divider=1
Width=2.55555555555556
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN3]
', 0, '', 0, 0)
GO