lyfz_repos/铂金版源码/DBFile/SQL/流程客户.sql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_ReservationPhotographyTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_ReservationPhotographyTime]
GO
CREATE function [dbo].[fn_ReservationPhotographyTime](@CreateDateTime datetime,@ReservationTime datetime, @ordtype char(1))
/****** 
--获取预约拍照时间 如果是成长套系时返回开单时按排的预计拍照时间
--如果不是成长套系时返回的是开单时间

创建人：刘工
创建日期：2015-7-30 

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

******/
Returns datetime
As
Begin
Declare @ReturnDate datetime 
   set @ReturnDate=@CreateDateTime
	if(@ordtype='1' and len(@ReservationTime)>=10)
	set @ReturnDate=@ReservationTime
	Return @ReturnDate
End		
GO
--摄影视图 记录拍摄状态  拍摄时间
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_PhotographyView]'))
DROP VIEW [dbo].[Vw_PhotographyView]
GO
CREATE VIEW [dbo].[Vw_PhotographyView]
AS
 select 
  max(Ordpg_Number) as Ordpg_Number,
  Ordpg_ViceNumber,
  max(Ordpg_PhotographyTime) as Ordpg_PhotographyTime,
  --拍摄状态 0:未拍  Ordpg_PhotographyStatus=Ordpg_PhotographyCount: OK(已拍)   否则:拍照中
  sum(CONVERT(int,Ordpg_PhotographyStatus)) as Ordpg_PhotographyStatus, 
  Count(Ordpg_PhotographyStatus) as Ordpg_PhotographyCount
   from tb_ErpOrdersPhotography
   group by Ordpg_ViceNumber
GO
--订单商品取件状态视图 
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_OrderProductPickupView]'))
DROP VIEW [dbo].[Vw_OrderProductPickupView]
GO
CREATE VIEW [dbo].[Vw_OrderProductPickupView]
AS
  select 
  max(OPlist_OrderNumber) as OPlist_OrderNumber,
  OPlist_ViceNumber,
  sum(CONVERT(int,[OPlist_PickupStatus])) as OPlist_PickupStatus,
  max(OPlist_PickupTime) as OPlist_PickupTime,
  sum(CONVERT(int,OPlist_CompletedStatus)) as OPlist_CompletedStatus,
  max(OPlist_CompletedTime) as OPlist_CompletedTime,
  count(id) productCount
   from [tb_ErpOrderProductList]
  where OPlist_Type ='2'
  group by OPlist_ViceNumber
 GO 
--订单流程客户 只显示未取件的客户
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_OrderProcessCustomer]'))
DROP VIEW [dbo].[Vw_OrderProcessCustomer]
GO
CREATE VIEW [dbo].[Vw_OrderProcessCustomer]
AS
--订单流程客户 只显示未取件的客户
SELECT tb_ErpOrder.ID,dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Number, dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
dbo.tb_ErpOrder.Ord_Class,CONVERT(int,Ord_Type) as Ord_Type, Ord_OrderClass,
CASE Ord_SinceOrderNumber WHEN '' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 

Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
[Age_String] AS 年龄,
[Age_Day]  AS 天,
[Age_Year] AS 年,

dbo.tb_ErpOrder.Ord_PhotographyCategory AS 套系类别, 
dbo.tb_ErpOrder.Ord_CustomerSource AS 客户来源, 
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(select stuff((select ',' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('')),1,1,'')) as 接单人,
--dbo.fn_ChineseToSpell((select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('')),1,1,''))) as 接单人拼音,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime([dbo].[Vw_PhotographyView].Ordpg_PhotographyTime)) AS 拍摄时间, 
(case when (isnull([dbo].[Vw_PhotographyView].Ordpg_PhotographyStatus,1)) =0  then '未拍' 
 else case when (isnull([dbo].[Vw_PhotographyView].Ordpg_PhotographyStatus,1)) = isnull([dbo].[Vw_PhotographyView].Ordpg_PhotographyCount,1) then 'OK' else '拍照中' end end) AS 拍照状态,

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_EarlyRepairTime)) AS 初修时间, 
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_FilmSelectionTime)) AS 选片时间, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_DesignerTime)) AS 设计时间, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_RefinementTime)) AS 精修时间, 
dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修状态, 

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignTime)) AS 看设计时间, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
(case when (isnull([dbo].[Vw_OrderProductPickupView].[OPlist_PickupStatus],1)) =0  then '未取' 
 else case when (isnull([dbo].[Vw_OrderProductPickupView].[OPlist_PickupStatus],1)) = isnull([dbo].[Vw_OrderProductPickupView].productCount,1) then 'OK' else '部分取件' end end) AS 取件状态,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime([dbo].[Vw_OrderProductPickupView].OPlist_PickupTime)) AS 取件日期, 
(case when (isnull([dbo].[Vw_OrderProductPickupView].OPlist_CompletedStatus,1)) =0  then '未完' 
 else case when (isnull([dbo].[Vw_OrderProductPickupView].OPlist_CompletedStatus,1)) = isnull([dbo].[Vw_OrderProductPickupView].productCount,1) then 'OK' else '部分完件' end end) AS 齐件状态,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime([dbo].[Vw_OrderProductPickupView].OPlist_CompletedTime)) AS 齐件日期, 

dbo.fn_ReservationPhotographyTime(Ord_CreateDateTime,(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime)) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_ReservationPhotographyTime is not null order by Ordpg_ReservationPhotographyTime DESC),Ord_Type) AS 预约日期
,Ord_CreateDateTime
FROM 
dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital  ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join [dbo].[Vw_PhotographyView] on dbo.tb_ErpOrderDigital.Ordv_ViceNumber=[dbo].[Vw_PhotographyView].Ordpg_ViceNumber 
left join [dbo].[Vw_OrderProductPickupView] on dbo.tb_ErpOrderDigital.Ordv_ViceNumber=[dbo].[Vw_OrderProductPickupView].OPlist_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
where CONVERT(int,Ord_Type) < 3 and (case when (isnull([dbo].[Vw_OrderProductPickupView].[OPlist_PickupStatus],1)) =0  then '未取' 
 else case when (isnull([dbo].[Vw_OrderProductPickupView].[OPlist_PickupStatus],1)) = isnull([dbo].[Vw_OrderProductPickupView].productCount,1) then 'OK' else '部分取件' end end) <>'OK'
GO
--订单流程客户 显示已取件和未取件的所有客户 数据效多查询时较慢
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vw_OrderProcessCustomerAll]'))
DROP VIEW [dbo].[Vw_OrderProcessCustomerAll]
GO
CREATE VIEW [dbo].[Vw_OrderProcessCustomerAll]
AS
--订单流程客户 显示已取件和未取件的所有客户 数据效多查询时较慢
SELECT tb_ErpOrder.ID,dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Number, dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
dbo.tb_ErpOrder.Ord_Class,CONVERT(int,Ord_Type) as Ord_Type, Ord_OrderClass,
CASE Ord_SinceOrderNumber WHEN '' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 

Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
[Age_String] AS 年龄,
[Age_Day]  AS 天,
[Age_Year] AS 年,

dbo.tb_ErpOrder.Ord_PhotographyCategory AS 套系类别, 
dbo.tb_ErpOrder.Ord_CustomerSource AS 客户来源, 
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(select stuff((select ',' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('')),1,1,'')) as 接单人,
--dbo.fn_ChineseToSpell((select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('')),1,1,''))) as 接单人拼音,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime([dbo].[Vw_PhotographyView].Ordpg_PhotographyTime)) AS 拍摄时间, 
(case when (isnull([dbo].[Vw_PhotographyView].Ordpg_PhotographyStatus,1)) =0  then '未拍' 
 else case when (isnull([dbo].[Vw_PhotographyView].Ordpg_PhotographyStatus,1)) = isnull([dbo].[Vw_PhotographyView].Ordpg_PhotographyCount,1) then 'OK' else '拍照中' end end) AS 拍照状态,

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_EarlyRepairTime)) AS 初修时间, 
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_FilmSelectionTime)) AS 选片时间, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_DesignerTime)) AS 设计时间, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_RefinementTime)) AS 精修时间, 
dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修状态, 

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignTime)) AS 看设计时间, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
(case when (isnull([dbo].[Vw_OrderProductPickupView].[OPlist_PickupStatus],1)) =0  then '未取' 
 else case when (isnull([dbo].[Vw_OrderProductPickupView].[OPlist_PickupStatus],1)) = isnull([dbo].[Vw_OrderProductPickupView].productCount,1) then 'OK' else '部分取件' end end) AS 取件状态,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime([dbo].[Vw_OrderProductPickupView].OPlist_PickupTime)) AS 取件日期, 
(case when (isnull([dbo].[Vw_OrderProductPickupView].OPlist_CompletedStatus,1)) =0  then '未完' 
 else case when (isnull([dbo].[Vw_OrderProductPickupView].OPlist_CompletedStatus,1)) = isnull([dbo].[Vw_OrderProductPickupView].productCount,1) then 'OK' else '部分完件' end end) AS 齐件状态,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime([dbo].[Vw_OrderProductPickupView].OPlist_CompletedTime)) AS 齐件日期, 

dbo.fn_ReservationPhotographyTime(Ord_CreateDateTime,(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime)) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_ReservationPhotographyTime is not null order by Ordpg_ReservationPhotographyTime DESC),Ord_Type) AS 预约日期
,Ord_CreateDateTime
FROM 
dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital  ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join [dbo].[Vw_PhotographyView] on dbo.tb_ErpOrderDigital.Ordv_ViceNumber=[dbo].[Vw_PhotographyView].Ordpg_ViceNumber 
left join [dbo].[Vw_OrderProductPickupView] on dbo.tb_ErpOrderDigital.Ordv_ViceNumber=[dbo].[Vw_OrderProductPickupView].OPlist_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
where CONVERT(int,Ord_Type) < 3 
GO