IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROCE_UpdateAggregationCustomer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PROCE_UpdateAggregationCustomer]
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		����
-- Create date: 2015-07-16
-- Description:	���¾ۺϿͻ�����ָ�������Ŀͻ�
-- =============================================
CREATE  PROCEDURE [dbo].[PROCE_UpdateAggregationCustomer]
(
 @WhereString varchar(2000) =N'',  --����ֵ Ϊ [GP_OrderNumber]������,[GP_CustomerGroupID]�ͻ���ID �ֶε�ֵ
 @ReturnI int output            --���ظ��¼�¼��
)
AS
BEGIN    
  DECLARE @M_Cus_CustomerNumber varchar(2000)
  DECLARE @GP_CustomerGroupID varchar(2000)
    --������ʼ��ͽ����� 
    delete [tempTB_AggregationCustomer] where len(@WhereString)>0 and (GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
 insert into [tempTB_AggregationCustomer] 
 select * from [Vw_AggregationCustomer]
   where len(@WhereString)>0 and (GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
  set @ReturnI=(select count(*) from [tempTB_AggregationCustomer] where GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
 
 ---����group by Cus_Name,M_Cus_CustomerNumber��Ŀͻ�����ʱ��--
set @M_Cus_CustomerNumber=(select top 1 M_Cus_CustomerNumber from [tempTB_AggregationCustomer] where GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
delete [tempTB_GroupByAggregationCustomer]  where  M_Cus_CustomerNumber =@M_Cus_CustomerNumber;
set @GP_CustomerGroupID=(select top 1 GP_CustomerGroupID from [tempTB_AggregationCustomer] where GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
delete [tempTB_GroupByAggregationCustomer]  where  GP_CustomerGroupID =@GP_CustomerGroupID;
with t as(		
SELECT  
max(GP_CustomerGroupID) as GP_CustomerGroupID
from tempTB_AggregationCustomer
 where M_Cus_CustomerNumber =@M_Cus_CustomerNumber
  group by Cus_Name,M_Cus_CustomerNumber
  ),t2 as(
  select * from tempTB_AggregationCustomer 
  where GP_CustomerGroupID in (select GP_CustomerGroupID from t)
  )
 insert into [tempTB_GroupByAggregationCustomer] select * from t2
 -----
 END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROCE_UpdateAggregationCustomerAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PROCE_UpdateAggregationCustomerAll]
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		����
-- Create date: 2015-08-09
-- Description:	���¾ۺϿͻ��������пͻ�
-- =============================================
CREATE  PROCEDURE [dbo].[PROCE_UpdateAggregationCustomerAll]
(
 @ReturnI int output            --���ظ��¼�¼��
)
AS
BEGIN    
 truncate table [dbo].[tempTB_AggregationCustomer]
  insert into [tempTB_AggregationCustomer]  select * from [Vw_AggregationCustomer]
 truncate table [dbo].[tempTB_GroupByAggregationCustomer]
 insert into [tempTB_GroupByAggregationCustomer] select * from [Vw_GroupByAggregationCustomer]
 set @ReturnI=(select count(*) from [tempTB_AggregationCustomer])
 -----
 END
GO