lyfz_repos/铂金版源码/DBFile/Update_AggregationCustomer.sql

update [dbo].[tb_SMSTemplates] set ST_IsEnabled=1 where ST_SendEvent=114 and ST_Type=1 and ST_Category=3
GO
update [dbo].[tb_SMSTemplates] set ST_Category=0 where ST_SendEvent=114 and ST_Type=1
GO
 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_getvarchar200]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_getvarchar200]
GO
CREATE function [dbo].[fn_getvarchar200](@FID varchar(800))
/****** 
字符转换为varchar 200

创建人：刘工
创建日期：2014-6-23 14:55:29 

修改人：
修改说明：
修改日期：

******/

Returns varchar(200)
As
Begin
	return @FID
End
GO
--客户组成交时间
  if not exists
(select * from syscolumns where id=object_id('tb_ErpCustomerGroup') and name='GP_TransactionTime')
begin
 alter table tb_ErpCustomerGroup add GP_TransactionTime datetime NULL
end
GO

--添加tb_ErpOrderDigital 中的 Ordv_SelectedPhotoType 字段 用于保存上次选片时用的什么相片选片
  if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderDigital') and name='Ordv_SelectedPhotoType')
begin
 alter table tb_ErpOrderDigital add Ordv_SelectedPhotoType  nvarchar(100) NULL
end
GO

if not exists
(select * from syscolumns where id=object_id('tb_ErpCustomerGroup') and name='GP_LossType')
begin
	alter table tb_ErpCustomerGroup add GP_LossType  nvarchar(100) NULL
end
GO
if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderProductList') and name='OPlist_UniquelyIdentity')
begin
	alter table tb_ErpOrderProductList add OPlist_UniquelyIdentity  nvarchar(100) NULL
end
GO
 if not exists
(select * from syscolumns where id=object_id('tb_SMSRecords') and name='SR_OpenID')
begin
	alter table tb_SMSRecords add SR_OpenID  varchar(100) NULL
end
GO
if  exists
(select * from syscolumns where id=object_id('tb_ErpOrder') and name='Ord_PhotographyCategory' and [length]<=100) 
begin
  alter table tb_ErpOrder alter column Ord_PhotographyCategory nvarchar(100)
end
GO
if  exists
(select * from syscolumns where id=object_id('tb_ErpCustomer') and name='Cus_NamePinyin' and [length]<=20) 
begin
  alter table tb_ErpCustomer alter column Cus_NamePinyin nvarchar(20)
end
GO
if  exists
(select * from syscolumns where id=object_id('tb_ErpShopBulletin') and name='Bulletin_Accessory' and [length]>0) 
begin
  alter table tb_ErpShopBulletin alter column Bulletin_Accessory nvarchar(max)
end
GO
if  exists
(select * from syscolumns where id=object_id('tb_SMSRecords') and name='SR_SMSSendIdentity' and [length]<=100) 
begin
  alter table [dbo].[tb_SMSRecords] alter column [SR_SMSSendIdentity] nvarchar(100)
end
GO
  if  exists
(select * from syscolumns where id=object_id('tb_SMSRecords') and name='SR_Content' and [length]<=1000) 
begin
  alter table [dbo].[tb_SMSRecords] alter column [SR_Content] nvarchar(800)
end
GO
  if  exists
(select * from syscolumns where id=object_id('tb_SMSRecords') and name='SR_RecipientNumber' and [length]>0) 
begin
  alter table [dbo].[tb_SMSRecords] alter column [SR_RecipientNumber] nvarchar(max)
end
GO
 if not exists
(select * from syscolumns where id=object_id('tb_ErpUser') and name='User_OpenID')
begin
	alter table tb_ErpUser add User_OpenID  varchar(200) NULL
end
GO
if not exists
(select * from syscolumns where id=object_id('tb_ErpCustomer') and name='Cus_OpenID')
begin
	alter table tb_ErpCustomer add Cus_OpenID  varchar(200) NULL
end
GO
IF  EXISTS (SELECT * FROM tb_ErpSystemCategory WHERE Sc_ClassCode='BEBCADBFBCDFBGJHC')
  begin
    delete from tb_ErpSystemCategory where Sc_ClassCode='BEBCADBFBCDFBGJHC'
  end
GO
IF  EXISTS (SELECT * FROM tb_ErpSystemCategory WHERE Sc_ClassCode='BEBCADBFBDDEGGABF')
  begin
   delete from tb_ErpSystemCategory where Sc_ClassCode='BEBCADBFBDDEGGABF'
  end
GO
IF  EXISTS (SELECT * FROM tb_ErpSystemCategory WHERE Sc_ClassCode='BEBCBFBICGDHCGBEJ')
  begin
    delete from tb_ErpSystemCategory where Sc_ClassCode='BEBCBFBICGDHCGBEJ'
  end
GO
update [dbo].[tb_ErpCompanyInfo] set Company_MicroShareAccount='',Company_MicroSharePassword='' where Company_MicroShareAccount='windylel' and Company_MicroSharePassword='19880528'
GO
 if  exists
(select * from syscolumns where id=object_id('tb_ErpSystemLogs') and name='Slogs_Content' and [length]>0) 
begin
  alter table [dbo].[tb_ErpSystemLogs] alter column [Slogs_Content] nvarchar(max)
end
GO
 if  exists
(select * from syscolumns where id=object_id('tb_ErpSystemLogs') and name='Slogs_CreateName' and [length]>0 and [length]<200) 
begin
  alter table [dbo].[tb_ErpSystemLogs] alter column [Slogs_CreateName] nvarchar(100)
end
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_ErpCustomer]'))
DROP VIEW [dbo].[View_ErpCustomer]
GO
CREATE VIEW [dbo].[View_ErpCustomer]
AS
SELECT  
      [Cus_CustomerNumber]
      ,[Cus_CustomizeNumber]
      --,[Cus_Type]
      --,[Cus_ServiceType]
      ,[Cus_Grade]
      ,[Cus_Name]
      ,Cus_NamePinyin as [Cus_Name_py]
      ,[Cus_Sex]
      ,dbo.Fun_ConvertSex([Cus_Sex]) as [Cus_Sex_cs]
      ,[Cus_Birthday]
      ,[Cus_BirthdayLunar]
      ,dbo.Fun_GetGregorianCalendar([Cus_Birthday],[Cus_BirthdayLunar]) as GregorianCalendar_Birthday
      ,dbo.Fun_GetGregorianCalendarNowYearBirthday([Cus_Birthday],[Cus_BirthdayLunar]) as GregorianCalendar_NowYearBirthday
      ,[Cus_DayForMarriage]
      ,[Cus_DayForMarriageLunar]
      ,dbo.Fun_GetGregorianCalendar([Cus_DayForMarriage],[Cus_DayForMarriageLunar]) as GregorianCalendar_DayForMarriage
      ,dbo.Fun_GetGregorianCalendarNowYearBirthday([Cus_DayForMarriage],[Cus_DayForMarriageLunar]) as GregorianCalendar_NowYearDayForMarriage
      --,[Cus_Relations] 关系
      ,[Cus_IDNumber]
      ,[Cus_QQ]
      ,[Cus_MicroSignal]
      ,[Cus_Telephone]
      ,[Cus_FixedPhone]
      ,[Cus_Region]
      ,[Cus_Address]
      ,[Cus_WorkUnit]
      ,[Cus_BabyWeight]
      ,[Cus_BornHospital]
      ,[Cus_Zodiac]
      ,[Cus_CustomerSource]
      ,[Cus_Status]
     -- ,[Cus_LossReason]客户流失原因
      ,[Cus_DegreeOfIntent]
      ,[Cus_TrackName]
      --,[Cus_Remark]
      ,[Cus_CreateDateTime]
      ,[Cus_CreateName]
      ,[Cus_UpdateDateTime]
      ,[Cus_UpdateName]
      ,[Cus_OpenID]
  FROM  tb_ErpCustomer 

GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_CustomerGroupAndCustomerGroupMembersAndErpCustomer]'))
DROP VIEW [dbo].[View_CustomerGroupAndCustomerGroupMembersAndErpCustomer]
GO
CREATE VIEW [dbo].[View_CustomerGroupAndCustomerGroupMembersAndErpCustomer]
AS
SELECT  [tb_ErpCustomerGroup].[id]
      ,[GP_OrderNumber]
      ,[GP_CustomerGroupID]
      ,[GP_GroupType]
      ,[GP_CustomerType]
      ,[GP_CustomerStatus]
      ,[GP_LossReason]
      ,[GP_DegreeOfIntent]
      ,[GP_CustomerSource]
      ,[GP_IntroducerCustomerID]
      ,[GP_Remark]
      ,[GP_BelongsPersonID]
      ,[GP_CreateDatetime]
      ,[GP_UpdateDatetime]

      ,[GM_CustomerGroupID]
      ,[GM_CustomerID]
      ,[GM_Master]
      ,[GM_ProtagonistCustomer]
      ,[GM_RelatedPersonID]
      ,[GM_Relation]
      ,[GM_IsOrder]
      ,[GM_IsOrderNumber]
      
      ,[Cus_CustomerNumber]
      ,[Cus_CustomizeNumber]
      ,[Cus_Grade]
      ,[Cus_Name]
      ,[Cus_Name_py]
      ,[Cus_Sex]
      ,[Cus_Sex_cs]
      ,[Cus_Birthday]
      ,[Cus_BirthdayLunar]
      ,[GregorianCalendar_Birthday]
      ,[GregorianCalendar_NowYearBirthday]
      ,[dbo].[Fun_GetAgeYear](GregorianCalendar_Birthday) as Age_Year
      ,[dbo].[Fun_GetAgeDay](GregorianCalendar_Birthday) as Age_Day
      ,dbo.Fun_GetAgeString(GregorianCalendar_Birthday) as Age_String
      ,[Cus_DayForMarriage]
      ,[Cus_DayForMarriageLunar]
      ,GregorianCalendar_DayForMarriage
      ,GregorianCalendar_NowYearDayForMarriage
      ,[Cus_IDNumber]
      ,[Cus_QQ]
      ,[Cus_MicroSignal]
      ,[Cus_Telephone]
      ,[Cus_FixedPhone]
      ,[Cus_Region]
      ,[Cus_Address]
      ,[Cus_WorkUnit]
      ,[Cus_BabyWeight]
      ,[Cus_BornHospital]
      ,[Cus_Zodiac]
      ,[Cus_CustomerSource]
      ,[Cus_Status]
      ,[Cus_DegreeOfIntent]
      ,[Cus_TrackName]
      ,[Cus_CreateDateTime]
      ,[Cus_CreateName]
      ,[Cus_UpdateDateTime]
      ,[Cus_UpdateName]
      ,[Cus_OpenID]
  FROM [tb_ErpCustomerGroup]
  left join
  dbo.tb_ErpCustomerGroupMembers
  on [tb_ErpCustomerGroup].[GP_CustomerGroupID]=dbo.tb_ErpCustomerGroupMembers.[GM_CustomerGroupID]
 left join
  [dbo].[View_ErpCustomer]
  on dbo.tb_ErpCustomerGroupMembers.GM_CustomerID=[dbo].[View_ErpCustomer].Cus_CustomerNumber

GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_CustomerGroupMembersAndErpCustomer]'))
DROP VIEW [dbo].[View_CustomerGroupMembersAndErpCustomer]
GO

CREATE VIEW [dbo].[View_CustomerGroupMembersAndErpCustomer]
AS
SELECT  [tb_ErpCustomerGroupMembers].[id]
      ,[GM_CustomerGroupID]
      ,[GM_CustomerID]
      ,[GM_Master]
      ,[GM_ProtagonistCustomer]
      ,[GM_RelatedPersonID]
      ,[GM_Relation]
      ,[GM_IsOrder]
      ,[GM_IsOrderNumber]
      ,[Cus_CustomerNumber]
      ,[Cus_CustomizeNumber]
      ,[Cus_Grade]
      ,[Cus_Name]
      ,[Cus_Name_py]
      ,[Cus_Sex]
      ,[Cus_Sex_cs]
      ,[Cus_Birthday]
      ,[Cus_BirthdayLunar]
      ,GregorianCalendar_Birthday
      ,GregorianCalendar_NowYearBirthday
      ,[dbo].[Fun_GetAgeYear](GregorianCalendar_Birthday) as Age_Year
      ,[dbo].[Fun_GetAgeDay](GregorianCalendar_Birthday) as Age_Day
      ,dbo.Fun_GetAgeString(GregorianCalendar_Birthday) as Age_String
      ,[Cus_DayForMarriage]
      ,[Cus_DayForMarriageLunar]
      ,GregorianCalendar_DayForMarriage
      ,GregorianCalendar_NowYearDayForMarriage
      ,[Cus_IDNumber]
      ,[Cus_QQ]
      ,[Cus_MicroSignal]
      ,[Cus_Telephone]
      ,[Cus_FixedPhone]
      ,[Cus_Region]
      ,[Cus_Address]
      ,[Cus_WorkUnit]
      ,[Cus_BabyWeight]
      ,[Cus_BornHospital]
      ,[Cus_Zodiac]
      ,[Cus_CustomerSource]
      ,[Cus_Status]
      ,[Cus_DegreeOfIntent]
      ,[Cus_TrackName]
      ,[Cus_CreateDateTime]
      ,[Cus_CreateName]
      ,[Cus_UpdateDateTime]
      ,[Cus_UpdateName]
      ,[Cus_OpenID]
	 ,[dbo].[Vw_ErpMemberCardSimpleInfo].[Mc_CradNumber]
	 ,[dbo].[Vw_ErpMemberCardSimpleInfo].[Mc_CardType]
  FROM [tb_ErpCustomerGroupMembers]
  left join [View_ErpCustomer] 
  on [tb_ErpCustomerGroupMembers].[GM_CustomerID]=[View_ErpCustomer].Cus_CustomerNumber
  left join
		[dbo].[Vw_ErpMemberCardSimpleInfo]
		on [View_ErpCustomer].Cus_CustomerNumber=[dbo].[Vw_ErpMemberCardSimpleInfo].[Mc_CustomerNumber]

GO

if not exists
(select * from syscolumns where id=object_id('tb_ErpCustomerGroup') and name='GP_LossType')
begin
	alter table tb_ErpCustomerGroup add GP_LossType  nvarchar(100) NULL
end
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_AggregationCustomer]'))
DROP VIEW [dbo].[Vw_AggregationCustomer]
GO
--[GM_CustomerID]==GM_RelatedPersonID 表示本人
CREATE VIEW [dbo].[Vw_AggregationCustomer]
as	
with t as(
 select 
    GM_CustomerGroupID
     ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Name]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_Name]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_Name
  ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Name_py]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_Name_py]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/'))  as Cus_Name_py
   ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Sex_cs]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_Sex_cs]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_Sex_cs
  ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Telephone]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_Telephone]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_Telephone
--OPenID
       ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_OpenID]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_OpenID]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_OpenID
 --地区
  ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_Region]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_Region]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_Region
  --地址
  ,CONVERT(nvarchar(2000),dbo.FunTrim(dbo.AggregateString([Cus_Address]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_Address]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_Address
  --微信号
  ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_MicroSignal]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_MicroSignal]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_MicroSignal
   --QQ号
  ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_QQ]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_QQ]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_QQ
   --工作单位
  ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_WorkUnit]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_WorkUnit]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_WorkUnit
   --客户来源
  ,CONVERT(nvarchar(200),dbo.FunTrim(dbo.AggregateString([Cus_CustomerSource]+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')
     +'/'+ dbo.AggregateString([Cus_CustomerSource]+'{$$}/{where}'+[GM_CustomerID]+'{$$}<>{$$}'+GM_RelatedPersonID+'{$$}s'),'/')) as Cus_CustomerSource
--年龄字符串
 ,CONVERT(nvarchar(200),dbo.AggregateString(CONVERT(nvarchar(10),[Age_String])+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_ProtagonistCustomer])+'{$$}={$$}1{$$}i')) as Age_String
--儿童年龄天数 int 型
,CONVERT(int,dbo.AggregateString(CONVERT(nvarchar(10),[Age_Day])+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')) as Age_Day
  --儿童年龄岁数 int 型
,CONVERT(int,dbo.AggregateString(CONVERT(nvarchar(10),[Age_Year])+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')) as Age_Year
  --儿童生日或主联系人生日 原始输入
,CONVERT(nvarchar(10),dbo.AggregateString(CONVERT(nvarchar(10),[Cus_Birthday])+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')) as Cus_Birthday
  --儿童生日或主联系人生日 是否农历
,CONVERT(nvarchar(10),dbo.AggregateString(CONVERT(varchar(1),[Cus_BirthdayLunar])+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')) as Cus_BirthdayLunar
  --儿童生日或主联系人生日 统一转为公历
,CONVERT(datetime,dbo.AggregateString(CONVERT(nvarchar(10),[GregorianCalendar_Birthday])+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')) as GregorianCalendar_Birthday
  --儿童当前年生日日期或主联系人当前年生日日期 统一转为公历
,CONVERT(datetime,dbo.AggregateString(CONVERT(nvarchar(10),[GregorianCalendar_NowYearBirthday])+'{$$}/{where}'+[GM_CustomerID]+'{$$}={$$}'+GM_RelatedPersonID+'{$$}s')) as GregorianCalendar_NowYearBirthday
  --主联系人婚期 原始输入
,CONVERT(nvarchar(10),dbo.AggregateString(CONVERT(nvarchar(10),[Cus_DayForMarriage])+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as Cus_DayForMarriage
  --主联系人婚期 是否农历
,CONVERT(nvarchar(1),dbo.AggregateString(CONVERT(nvarchar(1),[Cus_DayForMarriageLunar])+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as Cus_DayForMarriageLunar
  --主联系人婚期 统一转为公历
,CONVERT(datetime,dbo.AggregateString(CONVERT(nvarchar(10),[GregorianCalendar_DayForMarriage])+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as GregorianCalendar_DayForMarriage
  --主联系人当前年婚期日期 统一转为公历
,CONVERT(datetime,dbo.AggregateString(CONVERT(nvarchar(10),[GregorianCalendar_NowYearDayForMarriage])+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as GregorianCalendar_NowYearDayForMarriage

--以下是主要联系人
,CONVERT(nvarchar(200),dbo.AggregateString([Cus_CustomerNumber]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_CustomerNumber
,CONVERT(nvarchar(200),dbo.AggregateString([Cus_Name]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Name
,CONVERT(nvarchar(200),dbo.AggregateString([Cus_Name_py]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Name_py
,CONVERT(nvarchar(200),dbo.AggregateString([Cus_Sex_cs]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Sex_cs
,CONVERT(nvarchar(200),dbo.AggregateString([Cus_Telephone]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Telephone
--主要联系人微信号
,CONVERT(nvarchar(200),dbo.AggregateString([Cus_MicroSignal]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_MicroSignal
,CONVERT(nvarchar(200),dbo.AggregateString([Cus_QQ]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_QQ
--主要联系人地区
,CONVERT(nvarchar(200),dbo.AggregateString([Cus_Region]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i')) as M_Cus_Region
--主要联系人地址
,dbo.AggregateString([Cus_Address]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i') as M_Cus_Address
--主要联系人工作单位
,dbo.AggregateString([Cus_WorkUnit]+'{$$}/{where}'+CONVERT(nvarchar(1),[GM_Master])+'{$$}={$$}1{$$}i') as M_Cus_WorkUnit
--客户组中成员个数
,count(Cus_CustomerNumber) as Ord_ViceOrderCount
	 from 
	   dbo.View_CustomerGroupMembersAndErpCustomer
	 group by GM_CustomerGroupID   
)
select 
[tb_ErpCustomerGroup].id
,[GP_OrderNumber]
,[GP_CustomerGroupID]
,[GP_GroupType]
,[GP_CustomerType]
,[GP_CustomerStatus]
,[GP_LossType]
,[GP_LossReason]
,[GP_DegreeOfIntent]
,[GP_CustomerSource]
,GP_CreateDatetime
,GP_UpdateDatetime
,GP_ISIntentionCustomer
,GP_ReminderTime
,GP_RemindEvent
,[GP_IntroducerCustomerID],t.*
,[dbo].[Vw_ErpMemberCardSimpleInfo].[Mc_CradNumber]
,[dbo].[Vw_ErpMemberCardSimpleInfo].[Mc_CardType]
,1 as v160604
from 
	  [tb_ErpCustomerGroup]
	  left join
	   t
		on [tb_ErpCustomerGroup].[GP_CustomerGroupID]=t.[GM_CustomerGroupID]
		left join
		[dbo].[Vw_ErpMemberCardSimpleInfo]
		on t.M_Cus_CustomerNumber=[dbo].[Vw_ErpMemberCardSimpleInfo].[Mc_CustomerNumber]
GO
--根据聚合后的客户视图创建聚合后的客户临时表
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tempTB_AggregationCustomer]'))
DROP table [dbo].[tempTB_AggregationCustomer]
GO
select * into tempTB_AggregationCustomer from [Vw_AggregationCustomer]
GO

--唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_GP_CustomerGroupID] ON [dbo].[tempTB_AggregationCustomer] 
(
	[GP_CustomerGroupID] desc
)
--索引
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_GP_OrderNumber] ON [dbo].[tempTB_AggregationCustomer] 
(
	[GP_OrderNumber] desc
)
GO
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_Cus_Name] ON [dbo].[tempTB_AggregationCustomer] 
(
	[Cus_Name] desc
)
GO
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_Cus_Name_py] ON [dbo].[tempTB_AggregationCustomer] 
(
	Cus_Name_py desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_Cus_Telephone] ON [dbo].[tempTB_AggregationCustomer] 
(
	Cus_Telephone desc
)
GO
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_GP_CustomerSource] ON [dbo].[tempTB_AggregationCustomer] 
(
	[GP_CustomerSource] desc
)
GO
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_M_Cus_Name] ON [dbo].[tempTB_AggregationCustomer] 
(
	[M_Cus_Name] desc
)
GO
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_M_Cus_Name_py] ON [dbo].[tempTB_AggregationCustomer] 
(
	M_Cus_Name_py desc
)
GO
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_M_Cus_Telephone] ON [dbo].[tempTB_AggregationCustomer] 
(
	M_Cus_Telephone desc
)
GO
CREATE NONCLUSTERED INDEX [IX_tempTB_AggregationCustomer_Mc_CradNumber] ON [dbo].[tempTB_AggregationCustomer] 
(
	Mc_CradNumber desc
)
GO

--创建视图 dbo.tempTB_AggregationCustomer 表的Vw_GroupByAggregationCustomer视图
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_GroupByAggregationCustomer]'))
DROP VIEW [dbo].[Vw_GroupByAggregationCustomer]
GO
CREATE VIEW [Vw_GroupByAggregationCustomer]
AS
with t as(		
SELECT  
max(GP_CustomerGroupID) as GP_CustomerGroupID
from tempTB_AggregationCustomer
  group by Cus_Name,M_Cus_CustomerNumber
  )
  select * from tempTB_AggregationCustomer 
  where GP_CustomerGroupID in (select GP_CustomerGroupID from t)
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tempTB_GroupByAggregationCustomer]'))
DROP table [dbo].[tempTB_GroupByAggregationCustomer]
GO
select * into tempTB_GroupByAggregationCustomer from [Vw_GroupByAggregationCustomer]
GO

--唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_GP_CustomerGroupID] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	[GP_CustomerGroupID] desc
)
--索引
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_GP_OrderNumber] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	[GP_OrderNumber] desc
)
GO
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_Cus_Name] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	[Cus_Name] desc
)
GO
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_Cus_Name_py] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	Cus_Name_py desc
)
go
CREATE NONCLUSTERED INDEX [IX_tempTB_GroupByAggregationCustomer_Cus_Telephone] ON [dbo].[tempTB_GroupByAggregationCustomer] 
(
	Cus_Telephone desc
)
GO

--------