USE [SyteLine_BI]
GO
/****** Object:  StoredProcedure [dbo].[PV_SP_FactQualityCMSTypeH]    Script Date: 7/31/2019 1:25:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--select * from FactQualityCMSTypeH
-- DASHBOARD 2: : Tỷ lệ cọc ko phù hợp

ALTER PROC [dbo].[PV_SP_FactQualityCMSTypeH] --'PVD'
    @site_ref NVARCHAR(10)
AS
DELETE FactQualityCMSTypeH
WHERE Site = @site_ref;
INSERT INTO FactQualityCMSTypeH
SELECT  MONTH,
       SUM(TypeA) TypeA,
       SUM(TypeB) TypeB,
       SUM(TypeC) TypeC,
       SUM(TypeH) TypeH,
       A.CreatedBySite SITE,
       SUM(TypeA) + SUM(TypeB) + SUM(TypeC) + SUM(TypeH) SumQty,
       SUM(TypeH) / (SUM(TypeA) + SUM(TypeB) + SUM(TypeC) + SUM(TypeH)) * 100 PercentTypeH,
       yEAR
FROM
(
    SELECT MONTH(A.ManufactureDate) MONTH,
           YEAR(A.ManufactureDate) yEAR,
           case when  A.Quanlity = 'A' then  COUNT(A.ItemNo_) else 0 end TypeA,
            case when  A.Quanlity = 'B' then  COUNT(A.ItemNo_) ELSE 0 END TypeB,
           case when  A.Quanlity = 'C' then  COUNT(A.ItemNo_)  ELSE 0 END TypeC,
            case when  A.Quanlity = 'H' then  COUNT(A.ItemNo_) ELSE 0 END TypeH,
           A.CreatedBySite
	    FROM NEXTG.dbo.Item_Serials A WITH (NOLOCK)

            WHERE A.CreatedBySite = @site_ref
                  AND A.SerialNo_ IS NOT NULL
                  AND A.ItemNo_ IS NOT NULL
                  AND A.ManufactureDate IS NOT NULL
                  AND A.Quanlity IN ( 'A', 'B', 'C', 'H' )
				   AND a.statusid=1
				   and    dbo.FAB_IsPileItem(a.ItemNo_) = 1
			--	   AND dbo.FAB_ComparePeriod(A.ManufactureDate,2019,7)=1

            GROUP BY  ItemNo_ ,
                      YEAR(A.ManufactureDate) ,
           MONTH(A.ManufactureDate) ,  A.CreatedBySite,quanlity,FactoryNo_

) A
GROUP BY MONTH,
         A.CreatedBySite,
         yEAR;