--创建视图
--按订单查询
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_OrderCustomerList]'))
DROP VIEW [dbo].[Vw_OrderCustomerList]
GO
CREATE VIEW [Vw_OrderCustomerList]
AS
with t as(
SELECT Plu_OrdNumber
,sum(Plu_Amount)as Plu_Amount
  FROM [dbo].[tb_ErpPlusPickItems]
  group by Plu_OrdNumber
)
, t2 as(
SELECT  [dbo].[tb_ErpOrder].[ID]
      ,[Ord_Number]
      ,[Ord_DividedShop]
      ,[Ord_Type]
      ,dbo.fn_CheckOrderType([Ord_Type]) as Ord_Type_CH
      ,[Ord_Class]
      ,[Ord_OrderClass]
      ,[Ord_PhotographyCategory]
      ,[Ord_CustomerSource]
      ,[Ord_SeriesName]
      ,[Ord_SeriesPrice]
      ,[Ord_CreateDateTime]
      ,[Ord_CreateName]
      ,[Ord_UpdateDateTime]
      ,[Ord_UpdateName]
      ,t.Plu_Amount
  FROM [dbo].[tb_ErpOrder]
  left join
  t
  on [tb_ErpOrder].[Ord_Number]=t.Plu_OrdNumber
  )
  select t2.*
     ,(isnull(t2.Plu_Amount,0)+isnull(t2.Ord_SeriesPrice,0))as yingFuPrice
        ,[GP_CustomerGroupID]
      ,[GP_GroupType]
      ,[GP_CustomerType]
      ,[GP_CustomerStatus]
      ,[GP_LossReason]
      ,[GP_DegreeOfIntent]
      ,[GP_CustomerSource]
      ,[GP_IntroducerCustomerID]
      ,[GM_CustomerGroupID]
      ,[Cus_Name]
      ,[Cus_Name_py]
      ,[Cus_Sex_cs]
      ,[Cus_Telephone]
      ,[Cus_Region]
      ,[Age_String]
      ,[Age_Day]
      ,[Age_Year]
      ,[GregorianCalendar_Birthday]
      ,[GregorianCalendar_NowYearBirthday]
      ,[GregorianCalendar_DayForMarriage]
      ,[GregorianCalendar_NowYearDayForMarriage]
      ,[M_Cus_CustomerNumber]
      ,[M_Cus_Name]
      ,[M_Cus_Name_py]
      ,[M_Cus_Sex_cs]
      ,[M_Cus_Telephone]
      ,[M_Cus_MicroSignal]
      ,[M_Cus_QQ]
      ,[M_Cus_Region]
      ,[M_Cus_Address]
      ,[Ord_ViceOrderCount]
      ,[Mc_CradNumber]
      ,[Mc_CardType]
   from t2
  left join
  [dbo].[tempTB_AggregationCustomer]
  on t2.[Ord_Number]=dbo.[tempTB_AggregationCustomer].[GP_OrderNumber]
GO
--按人查询
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_PersonalCustomerList]'))
DROP VIEW [dbo].[Vw_PersonalCustomerList]
GO
CREATE VIEW [Vw_PersonalCustomerList]
AS
with t as(
SELECT Plu_OrdNumber
,sum(Plu_Amount)as Plu_Amount
  FROM [dbo].[tb_ErpPlusPickItems]
  group by Plu_OrdNumber
)
, t2 as(
SELECT  [dbo].[tb_ErpOrder].[ID]
      ,[Ord_Number]
      ,[Ord_DividedShop]
      ,[Ord_Type]
      ,dbo.fn_CheckOrderType([Ord_Type]) as Ord_Type_CH
      ,[Ord_Class]
      ,[Ord_OrderClass]
      ,[Ord_PhotographyCategory]
      ,[Ord_CustomerSource]
      ,[Ord_SeriesName]
      ,[Ord_SeriesPrice]
      ,[Ord_CreateDateTime]
      ,[Ord_CreateName]
      ,[Ord_UpdateDateTime]
      ,[Ord_UpdateName]
      ,t.Plu_Amount
  FROM [dbo].[tb_ErpOrder]
  left join
  t
  on [tb_ErpOrder].[Ord_Number]=t.Plu_OrdNumber
  )
  select t2.*
     ,(isnull(t2.Plu_Amount,0)+isnull(t2.Ord_SeriesPrice,0))as yingFuPrice
     ,[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]
      ,[Age_Year]
      ,[Age_Day]
      ,[Age_String]
      ,[Cus_DayForMarriage]
      ,[Cus_DayForMarriageLunar]
      ,[GregorianCalendar_DayForMarriage]
      ,[GregorianCalendar_NowYearDayForMarriage]
      ,[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]
   from t2
  left join
  dbo.View_CustomerGroupAndCustomerGroupMembersAndErpCustomer
  on t2.[Ord_Number]=dbo.View_CustomerGroupAndCustomerGroupMembersAndErpCustomer.[GP_OrderNumber]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_MemorialDayEmployeeList]'))
DROP VIEW [dbo].[Vw_MemorialDayEmployeeList]
GO
CREATE VIEW [Vw_MemorialDayEmployeeList]
AS
SELECT [ID]
      ,[User_Name]
      ,[User_Sex]
      ,[User_Telephone]
      ,User_DateOfBirth
      ,dbo.Fun_GetGregorianCalendarNowYearBirthday([User_DateOfBirth],[User_BirthdayLunar]) as GregorianCalendar_NowYearBirthday
  FROM [dbo].[tb_ErpUser]
  where User_Status='在职' 
  and len(User_DateOfBirth)>=10
  and len(User_DateOfBirth)>=10
  and len(User_Telephone)>=11
GO