-------------------
ALTER PROCEDURE [dbo].[usp_getUnitSalesSummary]
@PartCategoryID INT = 0,
@Manufacturers VARCHAR(1000) = '',
@PartReferences VARCHAR(1000) = '',
@PartID INT = 0,
@PartReferenceID INT = 0,
@PropertyCodeValuesFilters VARCHAR(1000) = '',
@Grades VARCHAR(100) = '',
@CustomerID INT = 0
AS
BEGIN
	-- List of manufacturer passed in string with comma seperated manufactuerer names
	DECLARE @TblManufacturers AS TABLE(ManufacturerID INT)
	IF (@Manufacturers <> '')
	BEGIN
		INSERT INTO @TblManufacturers (ManufacturerID)
			SELECT M.ID
				FROM dbo.fnSplitString(@Manufacturers, ',') tmpM
							INNER JOIN tbl_Manufacturer M ON LTRIM(RTRIM(M.manufacturer)) = LTRIM(RTRIM(tmpM.Data))
	END
	
	DECLARE @ObjectTypeID AS INT;
	
	SELECT @ObjectTypeID = ObjectTypeID
	FROM   tbl_ObjectTypes
	WHERE  archived = 0
		   AND ObjectTypeCode = 'Part';
	
	-- List part ids for filter.
	DECLARE @TblPart AS TABLE(PartId INT)
	INSERT INTO @TblPart (PartId)
		SELECT ObjectID 
			FROM dbo.fn_getPropertyValueObjects (@PropertyCodeValuesFilters, @ObjectTypeID)
	
	SELECT   
		top 10
		pm.ID AS 'PartID',
		PartNumber,
		PartDescription,
		isnull(pm.CreatedUserID , 0) AS 'CreatedUserID',
		ISNULL(pm.AuthorisedUserID, 0) AS 'AuthorisedUserID',
		ISNULL(pm.AuthorisedDate, '') AS 'AuthorisedDate',
		ISNULL(pm.Authorised, 0) AS 'Authorised',
		m.ID AS 'ManufacturerID',
		m.man AS 'ManufacturerCode',
		m.manufacturer AS 'ManufacturerName',
		pc.PartCategoryID AS 'PartCategoryID',
		pc.PartCategoryCode AS 'PartCategoryCode',
		pc.PartCategoryName AS 'PartCategoryName',
		pc.CertifyDataSafe  AS 'CertifyDataSafe',
		pc.InterfaceID AS 'InterfaceID',
		'-' AS 'InterfaceCode',
		'-' AS 'InterfaceName',
		'-' AS 'interfacedesc',
		'' OEM1,
		'' OEM2,
		'' OEM3,
		'' OEM4,
		TotalUnitCount
	FROM 
		-- get a distinct list of Part IDs that meet our filter criteria
		(
			select 
				pm.ID,
				COUNT(1) OVER (PARTITION BY pm.id) TotalUnitCount
			from   
				tbl_PartsMaster AS pm
				INNER JOIN @TblPart tblPartIds ON tblPartIds.PartId = pm.ID
				INNER JOIN tbl_PartCategories AS pc ON pc.PartCategoryID = pm.PartCategoryID
				INNER JOIN tbl_PartPartReferences AS ppr ON pm.ID = ppr.PartID
				INNER JOIN tbl_PartReferences AS pr ON ppr.PartReferenceID = pr.PartReferenceID
				INNER JOIN tbl_Manufacturer AS m ON pr.ManufacturerID = m.ID
				
				INNER JOIN dbo.fnSplitString(@PartReferences, ',') PRIDs ON (LTRIM(RTRIM(PRIDs.Data)) = pr.Reference OR @PartReferences = '')
				
				LEFT JOIN tbl_Units AS u ON u.PartReferenceID = ppr.PartReferenceID
				LEFT JOIN tbl_ActivityRequest AR ON AR.ActID = u.ACTID
				--LEFT JOIN @TblManufacturers tblManu ON (tblManu.ManufacturerID = pm.[OEM] OR @Manufacturers = '')
				
				INNER JOIN dbo.fnSplitString(@Grades, ',') tbGrades ON (LTRIM(RTRIM(tbGrades.Data)) = u.Grade OR @Grades = '')
				-- u.Grade IN (SELECT LTRIM(RTRIM(Data)) FROM dbo.fnSplitString(@Grades, ',')) 
				-- pr.Reference IN (SELECT LTRIM(RTRIM(Data)) FROM dbo.fnSplitString(@PartReferences, ','))
			where  
				pm.archived = 0
				AND 
				pr.archived = 0
				AND 
				ppr.archived = 0
				AND 
				pc.archived = 0
				AND
				(
					pm.ID = @PartID
					OR
					@PartID = 0
				)
				AND 
				(
					pm.PartCategoryID = @PartCategoryID
					OR 
					@PartCategoryID = 0
				)
				AND 
				(
					pr.PartReferenceID = @PartReferenceID
					OR 
					@PartReferenceID = 0
				)
				AND 
				(
					AR.OID = @CustomerID 
					OR 
					@CustomerID = 0
				)
		) AS p
		INNER JOIN tbl_PartsMaster AS pm ON p.ID = pm.ID 
		INNER JOIN tbl_Manufacturer AS m ON pm.OEM = m.ID
		INNER JOIN tbl_PartCategories AS pc ON pm.PartCategoryID = pc.PartCategoryID
	WHERE    
		ISNULL(pm.archived, 0) = 0
	group by
		pm.ID,
		PartNumber,
		PartDescription,
		isnull(pm.CreatedUserID , 0),
		ISNULL(pm.AuthorisedUserID, 0),
		ISNULL(pm.AuthorisedDate, ''),
		ISNULL(pm.Authorised, 0),
		m.ID,
		m.man,
		m.manufacturer,
		pc.PartCategoryID,
		pc.PartCategoryCode,
		pc.PartCategoryName,
		pc.CertifydataSafe,
		pc.InterfaceID, TotalUnitCount
	ORDER BY 
		PartNumber;

END
/*
-- testing
-- usp_getUnitSalesSummary @CustomerID = 308, @manufacturers = 'dell', @grades = 'D'
-- usp_getUnitSalesSummary @partid = 138013
*/
GO