lyfz_repos/铂金版源码/工资管理/工资统计订单信息 存储过程.sql

USE [LYFZERPDB]
GO
EXEC	[dbo].[PROCE_WageStatisticsOrder]
		@datatimeStar = N'2016-01-01',
		@datatimeEnd = N'2016-11-01',
		@storeID='''LDPHN_TW4M2R'',''SSN001'''
GO
EXEC	[dbo].[PROCE_WageStatisticsOrderReceiptRecord]
		@datatimeStar = N'2016-10-01',
		@datatimeEnd = N'2016-10-31',
		@storeID=''
GO
EXEC	[dbo].[PROCE_WageStatisticsSatisfactionScore]
		@datatimeStar = N'2016-09-01',
		@datatimeEnd = N'2016-11-01'
GO


--工资统计订单信息 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROCE_WageStatisticsOrder]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PROCE_WageStatisticsOrder]
GO
CREATE  PROCEDURE [dbo].[PROCE_WageStatisticsOrder]
(
 @datatimeStar datetime,            --开始时间
 @datatimeEnd datetime,     --结束时间
  @storeID varchar(1000) --门店iD
)
AS
 --工资统计订单信息
BEGIN    
    --处理开始点和结束点
    --Declare @MaxRecord int; 
    --Declare @TotalCountSql nvarchar(2000); 
   --8大时间查询
--订单信息
--联合查询
Declare @sqlString varchar(max);
set @sqlString=''
if(len(@storeID)>0)
set @sqlString='Ord_DividedShop in ('+@storeID+') and '

--select  @OrdersPerson=isnull(@OrdersPerson,'''0''')+','''+ cast(ordNumber as nvarchar(20))+'''' from orderList --数据大于1万条时效率太低
Declare @OrdersPerson varchar(max); 
set @OrdersPerson='
with ord as(
--订单时间
select Ord_Number as ordNumber,Ord_CreateDateTime as RDateTime from dbo.tb_ErpOrder where Ord_Class=1
UNION
--拍照时间
select  Ordpg_Number as ordNumber,max(Ordpg_PhotographyTime) as RDateTime from dbo.tb_ErpOrdersPhotography where Ordpg_PhotographyTime>0 group by Ordpg_Number  
UNION
--初修时间
select Ordv_Number as ordNumber,max(Ordv_EarlyRepairTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_EarlyRepairTime>0 group by Ordv_Number 
UNION
--精修时间
select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number 
UNION
--设计修时间
select Ordv_Number as ordNumber,max(Ordv_DesignerTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_DesignerTime>0 group by Ordv_Number 
UNION
--选片时间
select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number 
UNION
--取件时间
select  OPlist_OrderNumber as ordNumber,  max(OPlist_PickupTime) as RDateTime  from [tb_ErpOrderProductList]  where OPlist_Type =2 and OPlist_PickupTime>0  group by OPlist_OrderNumber
UNION
--收款时间
select Pay_OrdNumber as ordNumber,Pay_CreateDatetime as RDateTime from [dbo].[tb_ErpPayment] where Pay_Type =0 
),
orderList as
(
select ordNumber from ord where RDateTime>='''+CONVERT(nvarchar(10),@datatimeStar)+''' and RDateTime<'''+CONVERT(nvarchar(10),@datatimeEnd)+''' group by ordNumber
), t as
(
 select OrdPe_OrderNumber, dbo.AggregateString([OrdPe_OrdersPerson]+''{$$},{$$}s'') as OrdPe_OrdersPerson from dbo.tb_ErpOrdersPerson
 group by OrdPe_OrderNumber 
),
p as
(
 select  max(Ordpg_Number) as Ordpg_Number,Ordpg_ViceNumber, 
 dbo.AggregateString(Ordpg_Sights+''{$$},{$$}s'') as Ordpg_Sights,
  dbo.AggregateString(Ordpg_Photographer+''{$$},{$$}s'') as Ordpg_Photographer,
  max(Ordpg_PhotographyTime) as Ordpg_PhotographyTime,
  (case when (sum(CONVERT(int,Ordpg_PhotographyStatus)))=(count(1)) then 1 else 0 end) as PhotographyStatus ,
  dbo.AggregateString(Ordpg_PhotographyAssistant+''{$$},{$$}s'') as Ordpg_PhotographyAssistant,
  dbo.AggregateString(Ordpg_MakeupArtist+''{$$},{$$}s'') as Ordpg_MakeupArtist,
  dbo.AggregateString(Ordpg_MakeupAssistant+''{$$},{$$}s'') as Ordpg_MakeupAssistant,
  dbo.AggregateString(Ordpg_BootDivision+''{$$},{$$}s'') as Ordpg_BootDivision,
  dbo.AggregateString(Ordpg_BootDivisionAssistant+''{$$},{$$}s'') as Ordpg_BootDivisionAssistant
  from dbo.tb_ErpOrdersPhotography
 group by Ordpg_ViceNumber   
)
select Ord_DividedShop,Ord_Number,Ordv_ViceNumber,Ordpg_Sights,Ord_SinceOrderNumber,Ord_Type,Ord_Class,Ord_OrderClass,Ord_PhotographyCategory,Ord_SeriesName,Ord_SeriesPrice,Ord_CreateDateTime 
,OrdPe_OrdersPerson
,Ordv_DigitalNumber
,Ordv_EarlyRepairName,Ordv_EarlyRepairTime,ordv_EarlyRepairStatus,
Ordv_FilmSelectionName,Ordv_FilmSelectionTime,Ordv_FilmSelectionStatus,Ordv_DesignerName,
Ordv_DesignerTime,Ordv_DesignerStatus,Ordv_RefinementName,Ordv_RefinementTime,Ordv_RefinementStatus,
Ordv_LookDesignName,Ordv_LookDesignTime,Ordv_LookDesignStatus
,Ordpg_Photographer
,Ordpg_PhotographyTime,PhotographyStatus
,Ordpg_PhotographyAssistant,Ordpg_MakeupArtist
,Ordpg_MakeupAssistant,Ordpg_BootDivision,Ordpg_BootDivisionAssistant 
,[GP_CustomerSource],[Cus_Name]
,[dbo].[fun_GetPickupStatusStatus]([OPlist_PickupStatus],productCount) AS PickupStatus,[OPlist_PickupTime]
from dbo.tb_ErpOrder 
left join t on Ord_Number= OrdPe_OrderNumber
left join dbo.tb_ErpOrderDigital on Ord_Number=Ordv_Number
left join p on Ordv_ViceNumber=Ordpg_ViceNumber
left join [dbo].[Vw_OrderProductPickupView] on Ordv_ViceNumber=OPlist_ViceNumber
left join [dbo].[tempTB_AggregationCustomer] on Ord_Number=[GP_OrderNumber]
where '+@sqlString+' Ord_Class=1  and Ord_Number in(select ordNumber from orderList)
'
exec(@OrdersPerson)
END
GO

--工资统计订单收款记录 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROCE_WageStatisticsOrderReceiptRecord]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PROCE_WageStatisticsOrderReceiptRecord]
GO
CREATE  PROCEDURE [dbo].[PROCE_WageStatisticsOrderReceiptRecord]
(
 @datatimeStar datetime,            --开始时间
 @datatimeEnd datetime,     --结束时间
 @storeID varchar(1000) --门店iD
)
AS
 --工资统计订单收款记录
BEGIN    
    --处理开始点和结束点
    Declare @sqlString varchar(max);
set @sqlString=''
if(len(@storeID)>0)
set @sqlString='Pay_DividedShop in ('+@storeID+') and '

Declare @OrdersPerson varchar(max); 
set @OrdersPerson='
with ord as(
--订单时间
select Ord_Number as ordNumber,Ord_CreateDateTime as RDateTime from dbo.tb_ErpOrder where Ord_Class=1 
UNION
--拍照时间
select  Ordpg_Number as ordNumber,max(Ordpg_PhotographyTime) as RDateTime from dbo.tb_ErpOrdersPhotography where Ordpg_PhotographyTime>0 group by Ordpg_Number  
UNION
--初修时间
select Ordv_Number as ordNumber,max(Ordv_EarlyRepairTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_EarlyRepairTime>0 group by Ordv_Number 
UNION
--精修时间
select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number 
UNION
--设计修时间
select Ordv_Number as ordNumber,max(Ordv_DesignerTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_DesignerTime>0 group by Ordv_Number 
UNION
--选片时间
select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number 
UNION
--取件时间
select  OPlist_OrderNumber as ordNumber,  max(OPlist_PickupTime) as RDateTime  from [tb_ErpOrderProductList]  where OPlist_Type =2 and OPlist_PickupTime>0  group by OPlist_OrderNumber
UNION
--收款时间
select Pay_OrdNumber as ordNumber,Pay_CreateDatetime as RDateTime from [dbo].[tb_ErpPayment] where Pay_Type =0 
),
orderList as
(
select ordNumber from ord where RDateTime>='''+CONVERT(nvarchar(10),@datatimeStar)+''' and RDateTime<'''+CONVERT(nvarchar(10),@datatimeEnd)+''' group by ordNumber
)

select Pay_OrdNumber,
Pay_ViceNumber,
Pay_ShootingName,
Pay_Category,
Pay_TwoPinsCategory,
(Pay_AmountOf  - Pay_DiscountAmount) as PaidAmount,
Pay_AmountOf,
Pay_PaymentDiscount,
Pay_DiscountAmount,  
Pay_OpenSingle,
Pay_ThePayee,
Pay_Type,
Pay_DividedShop,
Pay_PaymentMethod,
Pay_ReceivableProject,
Pay_CreateDatetime from [dbo].[tb_ErpPayment] where '+@sqlString+'  Pay_Type =0  
 and Pay_OrdNumber in(select ordNumber from orderList)'
 exec(@OrdersPerson)
END
GO

--工资统计满意度分值 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROCE_WageStatisticsSatisfactionScore]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PROCE_WageStatisticsSatisfactionScore]
GO
CREATE  PROCEDURE [dbo].[PROCE_WageStatisticsSatisfactionScore]
(
 @datatimeStar datetime,            --开始时间
 @datatimeEnd datetime     --结束时间
)
AS
 --工资统计满意度分值
BEGIN    
    --处理开始点和结束点
 --联合查询
Declare @OrdersPerson varchar(max); 
set @OrdersPerson='
with ord as(
--订单时间
select Ord_Number as ordNumber,Ord_CreateDateTime as RDateTime from dbo.tb_ErpOrder where Ord_Class=1 
UNION
--拍照时间
select  Ordpg_Number as ordNumber,max(Ordpg_PhotographyTime) as RDateTime from dbo.tb_ErpOrdersPhotography where Ordpg_PhotographyTime>0 group by Ordpg_Number  
UNION
--初修时间
select Ordv_Number as ordNumber,max(Ordv_EarlyRepairTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_EarlyRepairTime>0 group by Ordv_Number 
UNION
--精修时间
select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number 
UNION
--设计修时间
select Ordv_Number as ordNumber,max(Ordv_DesignerTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_DesignerTime>0 group by Ordv_Number 
UNION
--选片时间
select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number 
UNION
--取件时间
select  OPlist_OrderNumber as ordNumber,  max(OPlist_PickupTime) as RDateTime  from [tb_ErpOrderProductList]  where OPlist_Type =2 and OPlist_PickupTime>0  group by OPlist_OrderNumber
UNION
--收款时间
select Pay_OrdNumber as ordNumber,Pay_CreateDatetime as RDateTime from [dbo].[tb_ErpPayment] where Pay_Type =0 
)
,orderList as
(
select ordNumber from ord where RDateTime>='''+CONVERT(nvarchar(10),@datatimeStar)+''' and RDateTime<'''+CONVERT(nvarchar(10),@datatimeEnd)+''' group by ordNumber
)

 --门市
    select Sfs_OrderNumber,'''' as Sfs_OrderNumberDeputy,
 (sum(Sfrs_Scores)/count(1)) as sumScores
 ,max(Sfss_MarkID) as MarkID
 from tb_ErpSatisfactionSurvey 
 left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID
 left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.id
 where Sfs_Type=0 and Sfs_OrderNumber in (select ordNumber from orderList)
 group by Sfs_OrderNumber
 union
 --其它流程
     select max(Sfs_OrderNumber) as Sfs_OrderNumber,Sfs_OrderNumberDeputy,
 (sum(Sfrs_Scores)/count(1)) as sumScores
 ,max(Sfss_MarkID) as MarkID
 from tb_ErpSatisfactionSurvey 
 left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID
 left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.id
 where (len(Sfs_OrderNumberDeputy)>0 and Sfs_Type>0 and Sfs_Type<7) and Sfs_OrderNumber in (select ordNumber from orderList)
 group by Sfs_OrderNumberDeputy'
 exec (@OrdersPerson)
END
GO