lyfz_repos/铂金版源码/工资管理/工资管理统计.sql

--update tb_ErpPayment set Pay_ViceNumber = (select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number = Pay_OrdNumber And Ordpg_Sights = Pay_ShootingName) where Pay_ViceNumber is null
--GO
--update tb_ErpPayment set Pay_ViceNumber = '' where Pay_ViceNumber is null

--订单前期提成  应收、实收、全款

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_CreateName,
Pay_Type,
Pay_DividedShop,
Pay_PaymentMethod,
Pay_ReceivableProject,
Pay_CreateDatetime from [dbo].[tb_ErpPayment] where Pay_Type =0 

--实收  前期的 全款、预约收款、补款
select * from [dbo].[tb_ErpPayment] where Pay_Type =0 and Pay_Category in('预约收款','预约补款','全款') and Pay_OpenSingle like '%20150507102012365%'

--实收  后期收款
select * from [dbo].[tb_ErpPayment] where Pay_Type =0 and Pay_Category ='后期收款' and Pay_OpenSingle like '%20150507102012365%'
--其他消费收款
select * from [dbo].[tb_ErpPayment] where Pay_Type =1

--礼服租售收款
select * from [dbo].[tb_ErpPayment] where Pay_Type =2

--订单前期 应收
with t as
(
 select OrdPe_OrderNumber, dbo.AggregateString([OrdPe_OrdersPerson]+'{$$},{$$}s') as OrdPe_OrdersPerson from dbo.tb_ErpOrdersPerson
 group by OrdPe_OrderNumber 
)
select Ord_DividedShop,Ord_Number,Ord_SinceOrderNumber,Ord_Mc_Number,Ord_Type,Ord_Class,Ord_OrderClass,Ord_PhotographyCategory,Ord_SeriesName,Ord_SeriesPrice,Ord_PackageName,Ord_PackagePrice,Ord_CreateDateTime 
,OrdPe_OrdersPerson from dbo.tb_ErpOrder
left join t on Ord_Number= OrdPe_OrderNumber
 where Ord_Class=1 
 
 --会员充值记录表
 select Mcrr_Number,Mcrr_RechargeNumber,Mcrr_RechargeName,Mcrr_RechargeAmount,Mcrr_DonateAmount,Mcrr_DonateDiscount,Mcrr_PaymentMethod,Mcrr_OrderPerson
 ,Mcrr_CreateDatetime,Mcrr_CreateName,Mcrr_DividedShop
  from tb_ErpMemberCardRechargeRecord
 
 --会员服务收款表
 select Mcpt_PaymentNumber,
Mcpt_Number,
Mcpt_PaymentType ,
Mcpt_PaymentAmount,
Mcpt_PaymentMethod,Mcpt_PackageClass ,Mcpt_PackagePrice ,
Mcpt_Discount,
Mcpt_DiscountAmount ,
Mcpt_OrderPerson ,
Mcpt_CreateDatetime ,
Mcpt_CreateName ,
Mcpt_DividedShop 
from tb_ErpMemberCardPayment
 --计件提成表
 select Pcr_OrderNumber,
Pcr_DigitalDivision ,
Pcr_Date ,
Pcr_CompletionContents ,
Pcr_Quantity ,
Pcr_Type ,
Pcr_MarkKey ,
Pcr_CreateTime,
Pcr_EntryPeople ,
Pcr_DividedShop 
 from tb_ErpPieceCommissionRecords 
 
  --奖罚记录表
 select Rp_Type,
Rp_RelevantPeople,
Rp_Money,
Rp_Status ,
Rp_RPDatetime ,
Rp_Reason ,
Rp_Remark ,
Rp_CreateDatetime,
Rp_CreateName,
Rp_DividedShop
from tb_ErpRewardPunishment


select * from tb_ErpSatisfactionSurveySet  order by sfss_type
 --满意度调查表
 select Sfs_OrderNumber,
Sfs_OrderNumberDeputy,
Sfs_EmployeeID,
Sfs_Type,
Sfs_SurveySetID,
Sfs_ReturningMattersSetID,
Sfs_RatingSetID,
Sfs_OtherID,
Sfs_Remark,
Sfs_CreateName,
Sfs_CreateTime
,Sfss_Name
,Sfrms_Contents
,Sfrs_Name,Sfrs_Scores
 from tb_ErpSatisfactionSurvey 
 left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.ID
 left join tb_ErpSatisfactionReturningMattersSet on Sfs_ReturningMattersSetID=tb_ErpSatisfactionReturningMattersSet.ID
 left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID
 where Sfs_SurveySetID=1
 --统计门市总分值
  select Sfs_OrderNumber,
 (sum(Sfrs_Scores)/count(1)) as sumScores
 from tb_ErpSatisfactionSurvey 
 left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID
 where  Sfs_SurveySetID=1
 group by Sfs_OrderNumber
 

 
 select * from tb_ErpSatisfactionSurvey 
 select * from tb_ErpSatisfactionSurveySet --满意度调查对象设置表
 select * from tb_ErpSatisfactionReturningMattersSet where Sfrms_SurveySetID=1 --满意度调查回访事项设置表
 select * from tb_ErpSatisfactionRatingSet 
 select Sfss_Name,Sfss_MarkID from tb_ErpSatisfactionSurveySet

 
select * from Vw_ErpSatisfactionSurvey

 select * from [dbo].[vw_Select_OrdersPersonList]


select * from dbo.tb_ErpOrdersPhotography where id=1519
select * from  dbo.tb_ErpOrderDigital 


--组合订单信息
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_Select_OrdersPersonList]'))
DROP VIEW [dbo].[vw_Select_OrdersPersonList]
GO
CREATE VIEW dbo.vw_Select_OrdersPersonList
as
with 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   
),
 t as
(
 select OrdPe_OrderNumber, dbo.AggregateString([OrdPe_OrdersPerson]+'{$$},{$$}s') as OrdPe_OrdersPerson from dbo.tb_ErpOrdersPerson
 group by OrdPe_OrderNumber 
)
select Ord_DividedShop,Ord_Number,Ordv_ViceNumber,Ordpg_Sights,Ord_SinceOrderNumber,Ord_Mc_Number,Ord_Type,Ord_Class,Ord_OrderClass,Ord_PhotographyCategory,Ord_SeriesName,Ord_SeriesPrice,Ord_PackageName,Ord_PackagePrice,Ord_CreateDateTime 
,OrdPe_OrdersPerson
,Ordv_DigitalNumber,Ordv_ClothingStatus,Ordv_ClothingName,
Ordv_ClothingTime,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
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
where Ord_Class=1 
GO

select * from [dbo].[vw_Select_OrdersPersonList]
where PhotographyStatus=1 and (OrdPe_OrdersPerson like '%四%' or Ordpg_Sights like '%四%' or Ord_OrderClass like '%四%' or Ord_PhotographyCategory like '%四%' or Ord_SeriesName like '%四%' or Ord_SeriesPrice like '%四%' )--ord_number='ET15102900001' 



/*IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_Select_OrdersPersonList]'))
DROP VIEW [dbo].[vw_Select_OrdersPersonList]
GO
CREATE VIEW dbo.vw_Select_OrdersPersonList
WITH SCHEMABINDING
as
select OrdPe_OrderNumber, dbo.FunTrim(dbo.AggregateString([OrdPe_OrdersPerson]+'{$$},{$$}s'),'/') as OrdPe_OrdersPerson from dbo.tb_ErpOrdersPerson
 group by OrdPe_OrderNumber 
 GO*/
 
 --下面的脚本给我们的视图建立索引：
--CREATE UNIQUE CLUSTERED INDEX
--idx_OrdersPersonListView ON vw_Index_OrdersPersonList(OrdPe_OrderNumber)

--8大时间查询
--订单信息
--联合查询
Declare @OrdersPerson varchar(max); 
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 top 10000 ordNumber from ord where RDateTime>='2016-01-01' and RDateTime<'2016-03-01' group by ordNumber
)
--select  @OrdersPerson=isnull(@OrdersPerson,'''0''')+','''+ cast(ordNumber as nvarchar(20))+'''' from orderList 
--select @OrdersPerson
,
p as
(
 select  max(Ordpg_Number) as Ordpg_Number,Ordpg_ViceNumber, 
 dbo.AggregateString(Ordpg_Sights+'{$$},{$$}s') as Ordpg_Sights,
 dbo.AggregateString(Ordpg_SightsLevel+'{$$},{$$}s') as Ordpg_SightsLevel,
  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   
),
 t as
(
 select OrdPe_OrderNumber, dbo.AggregateString([OrdPe_OrdersPerson]+'{$$},{$$}s') as OrdPe_OrdersPerson from dbo.tb_ErpOrdersPerson
 group by OrdPe_OrderNumber 
)
select Ord_DividedShop,Ord_Number,Ordv_ViceNumber,Ordpg_Sights,Ordpg_SightsLevel,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 Ord_Class=1  and Ord_Number in(select ordNumber from orderList)


--当前时间段的订单数
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>='2015-01-01' and RDateTime<'2016-03-01' group by ordNumber
)
select Ord_Type,count(1)  as sumCount  from dbo.tb_ErpOrder where Ord_Class=1   and Ord_Number in(select ordNumber from orderList) group by Ord_Type

--当前时间段相关的所有订单数
select Ord_Type,count(1) as sumCount   from dbo.tb_ErpOrder where Ord_Class=1  and Ord_CreateDateTime>='2016-01-01' and Ord_CreateDateTime<'2016-03-01' group by Ord_Type

--获取订单拍摄景点和阶段等级统计信息
--联合查询

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>='2016-01-01' and RDateTime<'2016-03-01' group by ordNumber
)
 select  Ordpg_Number,
 dbo.AggregateString(Ordpg_SightsLevel+'{$$},{$$}s') as Ordpg_SightsLevel
 ,count(1) as sumCount
  from dbo.tb_ErpOrdersPhotography
  where Ordpg_Number in(select ordNumber from orderList)
 group by Ordpg_Number   
 
 
 --获取订单拍摄景点和阶段人员统计信息
--联合查询

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>='2016-01-01' and RDateTime<'2016-03-01' group by ordNumber
)
 select  Ordpg_Number,Ordpg_ViceNumber, 
 Ordpg_Sights,
 Ordpg_SightsLevel,
Ordpg_Photographer,
Ordpg_PhotographyTime,
Ordpg_PhotographyStatus,
Ordpg_PhotographyAssistant,
Ordpg_MakeupArtist,
Ordpg_MakeupAssistant,
Ordpg_BootDivision,
Ordpg_BootDivisionAssistant
  from dbo.tb_ErpOrdersPhotography
  where Ordpg_Number in(select ordNumber from orderList)
 
 

--订单收款记录
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 
)
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_CreateName,
Pay_Type,
Pay_DividedShop,
Pay_PaymentMethod,
Pay_ReceivableProject,
Pay_CreateDatetime from [dbo].[tb_ErpPayment] where Pay_Type =0  
 and Pay_OrdNumber in(select ordNumber from ord where RDateTime>='2016-01-01' and RDateTime<'2016-02-01' group by ordNumber)
 

--礼服租售订单表
select 
Dsro_Number,
Dsro_Type,
Dsro_Amount,
Dsro_CustomerNumber,
Cus_Name,
Dsro_CreateDateTime,
Dsro_CreateName,
Dsro_DividedShop,
Pay_Category,
(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 tb_ErpDressSaleRentalOrder
 left join dbo.tb_ErpCustomer on Dsro_CustomerNumber=Cus_CustomerNumber
 left join [dbo].[tb_ErpPayment] on Dsro_Number=Pay_OrdNumber
   where Dsro_Type<2 and  Pay_Type =2  and  Pay_CreateDatetime>='2016-09-01' and Pay_CreateDatetime<'2016-11-01' 

--其它销费
select Tsorder_Number,Tsorder_Name,Tsorder_Money,Tsorder_Quantity,Tsorder_Category,Tsorder_OpenSingle
,Tsorder_CustomerNumber,Cus_Name,Tsorder_CreateDatetime,Tsorder_DividedShop,
  Pay_Category,
(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 tb_ErpTwoSalesOrder
 left join [dbo].[tb_ErpPayment] on Tsorder_Number=Pay_OrdNumber
 left join tb_ErpCustomer on Tsorder_CustomerNumber=Cus_CustomerNumber
where Pay_Type =1 and  Pay_CreateDatetime>='2015-09-01' and Pay_CreateDatetime<'2016-11-30' 



--计件提成金额查询记录
with WageCommissionSet as
(
SELECT [Wcs_TypeCode]
      ,[Wcs_Percentage]
  FROM [dbo].[tb_ErpWageCommissionSet]
  where [Wcs_Type]='计件式提成'
)
 select Pcr_OrderNumber,
Pcr_DigitalDivision ,
Pcr_Date ,
Pcr_CompletionContents,
(Wcs_Percentage * Pcr_Quantity) as sum_Percentage,
Pcr_Quantity ,
Pcr_Type ,
--Pcr_MarkKey ,
Pcr_CreateTime,
Pcr_EntryPeople ,
Pcr_DividedShop 
 from tb_ErpPieceCommissionRecords 
 left join WageCommissionSet on Pcr_CompletionContents=[Wcs_TypeCode]
 where  Pcr_CreateTime>='2016-09-01' and Pcr_CreateTime<'2016-11-01' 
 
 select * from [tb_ErpWageCommissionSet] where  [Wcs_Type]='计件式提成'

 --会员卡充值 
  with cTb as
  (
  select Mc_Number,Mc_CradNumber,Cus_Name,Mc_CustomerNumber,Mc_CardType from dbo.tb_ErpMemberCard
left join dbo.tb_ErpCustomer on Mc_CustomerNumber=Cus_CustomerNumber
where len(Mc_CradNumber)>0
  )
select
 Mcrr_Number,
Mc_CradNumber,Cus_Name,Mc_CustomerNumber,Mc_CardType,
Mcrr_RechargeNumber,
Mcrr_RechargeName, 
Mcrr_RechargeAmount,
Mcrr_DonateAmount,
Mcrr_DonateDiscount,
Mcrr_PaymentMethod,
Mcrr_OrderPerson,
Mcrr_CreateDatetime ,
Mcrr_CreateName,
Mcrr_DividedShop
from tb_ErpMemberCardRechargeRecord 
left join cTb on Mcrr_Number=Mc_Number
 where  Mcrr_CreateDatetime>='2016-09-01' and Mcrr_CreateDatetime<'2016-11-01' 
 
 
--会员服务卡收款表
 with cTb as
  (
  select Mc_Number,Mc_CradNumber,Cus_Name,Mc_CustomerNumber,Mc_CardType from dbo.tb_ErpMemberCard
left join dbo.tb_ErpCustomer on Mc_CustomerNumber=Cus_CustomerNumber
where len(Mc_CradNumber)>0
  )
select 
Mc_CradNumber,Cus_Name,Mc_CustomerNumber,Mc_CardType,
Mcpt_PaymentNumber,
Mcpt_Number ,
Mcpt_PaymentType,
Mcpt_PaymentAmount,
Mcpt_PaymentMethod,
Mcpt_PackageClass,
Mcpt_PackagePrice,
Mcpt_Discount,
Mcpt_DiscountAmount,
Mcpt_OrderPerson,
Mcpt_CreateDatetime,
Mcpt_CreateName,
Mcpt_DividedShop
 from tb_ErpMemberCardPayment
 left join cTb on Mcpt_Number=Mc_Number
 where  Mcpt_CreateDatetime>='2015-01-01' and Mcpt_CreateDatetime<'2016-11-01' 
 
 --满意度分值查询
 --满意分值统计
 -- select * from  tb_ErpSatisfactionSurvey  where Sfs_Type=0  --门市满意分值
 --select * from  tb_ErpSatisfactionSurvey  where Sfs_Type>0 and Sfs_Type<7  --其它流程满意分值 and Sfs_SurveySetID=7 --Sfs_SurveySetID=9

  --联合查询
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>='2016-10-01' and RDateTime<'2016-11-01' 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