ALTER PROCEDURE [dbo].[SPRpt_AdminReportSummary]
@StartDate DATETIME,
@EndDate DATETIME,
@ModelID INT = 0,
@TestID INT = 0,
@Category varchar(100) = '',
@SupplierID INT = 0,
@FactoryID INT = 0,
@isCosmeticManufacturer BIT = 0,
@isFunctionalManufacturer BIT = 0,
@RegionID INT = 0,
@StateID INT = 0,
@ServiceCentreID INT = 0,
@isCosmeticServiceCenter BIT = 0,
@isFunctionalServiceCenter BIT = 0,
@callFromChart bit=0
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
if(@callFromChart=1)
set @Category='' ;
DECLARE @cosmeticTestAttributeID int;
DECLARE @functionalTestAttributeID int;
SET @cosmeticTestAttributeID = (select AttributeID from LookupMaster where AttributeType = 'IsCosmeticOrFunctional' and code ='cosmetic');
SET @functionalTestAttributeID = (select AttributeID from LookupMaster where AttributeType = 'IsCosmeticOrFunctional' and code ='functional');
create table #ReportTable
([TestResultCycleID] int,[Model Number] nvarchar(50),[Commercial Name] nvarchar(50),[Source]varchar(14),
[Supplier]nvarchar(50),[Factory]nvarchar(100),[Region]nvarchar(50),[State]nvarchar(50),
[Service Centre]nvarchar(50),[IMEI 1]nvarchar(20),[IMEI 2]nvarchar(20),[Date]nvarchar(4000),
[Time]nvarchar(10),[Age]int,[Cycle Result]nvarchar(50), [Repeat]nvarchar(3),
[TestName]nvarchar(75),[TestResult]int,[TestID]int,[UserTypeID]int,
[SupplierID]int,[ServiceCenterID]int,[RegionID]int,[StateID]int,[LineID]int,
[FactoryID]int,[ModelId]int,[TestingCycleTypeID]int,[ProductionQuantity]int,
[DefectQuantity]int,[TestFilterType]nvarchar(23),[CosmeticFunctionalOrder]int,[OriginalTestName]nvarchar(50),
[CountOrder]int,[Production/Service Date Range]nvarchar(4000),[% Defect Rate]decimal)
CREATE CLUSTERED INDEX IDX_ReportTable_TRCI ON #ReportTable ([TestResultCycleID])
CREATE NONCLUSTERED INDEX [NCI1]
ON #ReportTable ([Model Number])
INCLUDE ([Source],[Supplier],[Region],[State],[Service Centre],[ModelId]);
IF(@callFromChart=1)
BEGIN
Insert into #ReportTable
SELECT *, (FORMAT(@StartDate,'dd-MMM-yyyy') + ' to ' + FORMAT(@EndDate,'dd-MMM-yyyy')) [Production/Service Date Range],
(CASE WHEN ProductionQuantity>0 THEN CAST((DefectQuantity*1.0/ProductionQuantity * 100) AS decimal(18,2)) ELSE 0 END) [% Defect Rate]
FROM fnAdminReportSummary(@StartDate, @EndDate) mr
WHERE (TRY_CAST(mr.Date as date) between TRY_CAST(@StartDate as date) and TRY_CAST(@EndDate as date))
END
ELSE
BEGIN
Insert into #ReportTable
SELECT *, (FORMAT(@StartDate,'dd-MMM-yyyy') + ' to ' + FORMAT(@EndDate,'dd-MMM-yyyy')) [Production/Service Date Range],
(CASE WHEN ProductionQuantity>0 THEN CAST((DefectQuantity*1.0/ProductionQuantity * 100) AS decimal(18,2)) ELSE 0 END) [% Defect Rate]
FROM fnAdminReportSummary(@StartDate, @EndDate) mr
WHERE (
isnull(mr.RegionID, 0) = (CASE WHEN @RegionID > 0 THEN @RegionID ELSE isnull(mr.RegionID, 0) END)
AND isnull(mr.FactoryID, 0) = (CASE WHEN @FactoryID > 0 THEN @FactoryID ELSE isnull(mr.FactoryID, 0) END)
AND isnull(mr.SupplierID, 0) = (CASE WHEN @SupplierID > 0 THEN @SupplierID ELSE isnull(mr.SupplierID, 0) END)
AND isnull(mr.RegionID, 0) = (CASE WHEN @RegionID > 0 THEN @RegionID ELSE isnull(mr.RegionID, 0) END)
AND isnull(mr.StateID, 0) = (CASE WHEN @StateID > 0 THEN @StateID ELSE isnull(mr.StateID, 0) END)
AND isnull(mr.ServiceCenterID, 0) = (CASE WHEN @ServiceCentreID > 0 THEN @ServiceCentreID ELSE isnull(mr.ServiceCenterID, 0) END)
AND isnull(mr.ModelID, 0) = (CASE WHEN @ModelID > 0 THEN @ModelID ELSE isnull(mr.ModelID, 0) END)
AND isnull(mr.TestFilterType, '0') in (
(CASE WHEN (@isCosmeticManufacturer > 0) THEN 'ManufacturerCosmetic' END),
(CASE WHEN (@isFunctionalManufacturer > 0) THEN 'ManufacturerFunctional' END),
(CASE WHEN (@isCosmeticServiceCenter > 0) THEN 'ServiceCentreCosmetic' END),
(CASE WHEN (@isFunctionalServiceCenter > 0) THEN 'ServiceCentreFunctional' END),
(CASE WHEN @isCosmeticManufacturer = 0 AND @isFunctionalManufacturer = 0 AND
@isCosmeticServiceCenter = 0 AND @isFunctionalServiceCenter = 0 THEN isnull(mr.TestFilterType, '0') END))
AND TRY_CAST(mr.Date as date) between TRY_CAST(@StartDate as date) and TRY_CAST(@EndDate as date)
AND isnull(mr.TestID, 0) = (CASE WHEN @TestID > 0 THEN @TestID ELSE isnull(mr.TestID, 0) END)
AND (CASE WHEN @Category <> '' THEN mr.TestingCycleTypeID ELSE 0 END) in (select [data] from dbo.split(@Category, ',')))
END
DECLARE @columns NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += ', ' + QUOTENAME(TestName)
FROM (
select distinct
TestName
, CosmeticFunctionalOrder, [countorder], [OriginalTestName] from #ReportTable WITH (NOLOCK)
) As temp
ORDER BY
CosmeticFunctionalOrder, [OriginalTestName], [countorder];
print @columns;
SET @columns = STUFF(@columns, 1, 2, '')
print @columns;
DECLARE @sql NVARCHAR(MAX);
SET @sql = '
SELECT ' + @columns +' , [Model Number], ModelId, Source, Supplier, Region, State, [Service Centre] INTO #tableTests
FROM
(
SELECT [Model Number], ModelId, Source, Supplier, Region, State, [Service Centre],
/*(CASE WHEN [IsFunctionalOrCosmetic]= 14 THEN ''Functional#''
when [IsFunctionalOrCosmetic] = 13 THEN ''Cosmetic#'' else '''' END) + Test AS*/ TestName, TestResult
FROM #ReportTable WITH (NOLOCK)
) AS j PIVOT (sum(TestResult) FOR TestName IN (' + @columns + ')) AS p
CREATE CLUSTERED INDEX IDX_tableTests_TRCI ON #tableTests ([Model Number])
CREATE NONCLUSTERED INDEX [NCI2]
ON #tableTests (Source)
INCLUDE ([Supplier],[Region],[State],[Service Centre],[ModelId]);
--select * from #ReportTable order by testresultcycleid, [model number], [commercial name];
SELECT distinct RT.[Model Number] [Model],
(CASE WHEN RT.[Source] = ''Manufacturer'' THEN RT.Supplier WHEN RT.[Source] = ''Service Centre'' THEN RT.[Service Centre] END) [ODM],
RT.[Source] [Category], RT.[Region], RT.[State], RT.[Service Centre], RT.[Production/Service Date Range],
isnull(RT.[ProductionQuantity], 0) [Production Qty/Service Call], RT.[Age], isnull(RT.[DefectQuantity],0) [Total Defect Quantity], [% Defect Rate], '+@columns+'
FROM #tableTests TT WITH (NOLOCK)
INNER JOIN #ReportTable RT WITH (NOLOCK) on
isnull(TT.[Model Number],'''')=isnull(RT.[Model Number],'''') and isnull(TT.Source,'''')=isnull(RT.Source,'''') and isnull(TT.Supplier,'''')=isnull(RT.Supplier,'''')
and isnull(TT.Region,'''') = isnull(RT.Region,'''') and isnull(TT.State,'''')=isnull(RT.State,'''')
and isnull(TT.[Service Centre],'''') = isnull(RT.[Service Centre],'''') and RT.ModelId = TT.ModelId
/*SELECT distinct RT.[Model Number], RT.[Commercial Name], RT.[Source], RT.[Region], RT.[State], RT.[Service Centre],
RT.[Supplier], RT.[Factory], RT.[IMEI 1], RT.[IMEI 2], RT.[Date], RT.[Time], RT.[Age], RT.[Repeat], RT.[Cycle Result], RT.[ProductionQuantity], RT.[DefectQuantity], '+@columns+'
INTO tblSummary
FROM #tableTests TT WITH (NOLOCK)
INNER JOIN #ReportTable RT WITH (NOLOCK) on TT.TestResultCycleID = RT.TestResultCycleID*/
DROP TABLE #tableTests;
/*select * from tblSummary
drop table tblSummary
group by [Model Number]*/ '
print @sql;
exec sp_executesql @sql
DROP TABLE #ReportTable;
END</admin>