lyfz_repos/经典版源码/document/数据库维护脚本/新工资查询视图-20161129.sql

-- 新工资查询视图;
CREATE view [dbo].[Payroll] as select 
dbo.dindanbukuan.id,						-- 订单号;
dbo.dindan.name1,							-- 顾客姓名1;	
dbo.dindan.name2,							-- 顾客姓名2;
dbo.dindan.txtype,							-- 套系类型;
dbo.dindan.taoxiname,						-- 套系名称;
dbo.dindanbukuan.kind as PayKind,			-- 收款类型:== 1 订单二销; == 2 加挑（不会在这里出现，在dindanbukuan2）; == 3 预约补款; == 4 预约收款;
dbo.dindanbukuan.money,						-- 收款金额;
dbo.dindanbukuan.date as Paydate,			-- 顾客付款日期;
dbo.dindan.status2 as SelectStatus,			-- 选片状态;
dbo.dindan.status as PhotoStatus,			-- 拍照状态;
-- 摄影师工资日期: if ( 如果拍照OK日期 > 收款日期 ) 摄影师工资日期 = 拍照OK日期 else 摄影师工资日期 = 顾客付款日期;
(case when dbo.dindan.time2 > dbo.dindanbukuan.date then dbo.dindan.time2 else dbo.dindanbukuan.date end) as PhotogOfPaydate,
dbo.dindanbukuan.ren,						-- 收款人;
dbo.dindanbukuan.bz as PayDesc,				-- 备注：实际是收款类型;
dbo.dindanbukuan.paytype					-- 顾客支付方式（现金、POS机……）
FROM dbo.dindan INNER JOIN dbo.dindanbukuan 
ON dbo.dindan.id = dbo.dindanbukuan.id-- and dbo.dindan.status = 'OK'		-- 成立条件：订单id = 补款id & 订单状态=OK;
GO

-- 查询指定日期范围的所有前期收款;
select * from [Payroll] where (PayKind = '3' or PayKind = '4') and Paydate >= '2016-10-01' and Paydate <= '2016-10-31'

-- 查询指定日期范围的所有后期收款;
select * from [Payroll] where (PayKind <> '3' and PayKind <> '4') and Paydate >= '2016-10-01' and Paydate <= '2016-10-31'

-- 查询拍照OK的收款,并且指定时间范围;
select * from [Payroll] where PhotoStatus = 'OK' and PhotogOfPaydate >= '2016-10-01' and PhotogOfPaydate <= '2016-10-31'

-- 查询选片OK的选片二销收款,并且指定时间范围;
select * from [Payroll] where SelectStatus = 'OK' and PayDesc = '选片二销' and Paydate >= '2016-10-01' and Paydate <= '2016-10-31'

/* 示例 */
use [db]
go
declare @name nvarchar(50)		--员工姓名;
declare @btm  nvarchar(24)		--开始时间;
declare @etm  nvarchar(24)		--结束时间;
set @name = '陈显亭'
set @btm = '2016-10-01'
set @etm = '2016-10-31'

select * from Payroll where id in
(select id from dindan where 
([waiter1]=@name			--摄影
or [waiter2]=@name			--化妆
or [waiter3]=@name			--选片
or [waiter4]=@name			--设计
or [waiter5]=@name			--初修
or [waiter5]=@name			--接单
or [waiter7]=@name			--精修
or [waiter8]=@name			--看样
or [waiter9]=@name			--????
or [waiter12]=@name			--摄影助理
or [waiter22]=@name)		--化妆助理	
and id in (select id from Payroll where Paydate >= @btm and Paydate <= @etm)) and Paydate >= @btm and Paydate <= @etm order by id desc

/* 获取指定摄影师工资日期内的所有订单详情 */
declare @name nvarchar(50)		--员工姓名;
declare @btm  nvarchar(24)		--开始时间;
declare @etm  nvarchar(24)		--结束时间;
set @name = '陈显亭'
set @btm = '2016-10-01'
set @etm = '2016-10-31'
select * from dindan where 
([waiter1]=@name			--摄影
or [waiter2]=@name			--化妆
or [waiter3]=@name			--选片
or [waiter4]=@name			--设计
or [waiter5]=@name			--初修
or [waiter5]=@name			--接单
or [waiter7]=@name			--精修
or [waiter8]=@name			--看样
or [waiter9]=@name			--????
or [waiter12]=@name			--摄影助理
or [waiter22]=@name)		--化妆助理	
and id in (select id from Payroll where PhotoStatus = 'OK' and PhotogOfPaydate >= @btm and PhotogOfPaydate <= @etm)

/* 获取指定收款日期*/
declare @name nvarchar(50)		--员工姓名;
declare @btm  nvarchar(24)		--开始时间;
declare @etm  nvarchar(24)		--结束时间;
set @name = '陈显亭'
set @btm = '2016-10-01'
set @etm = '2016-10-31'
select * from dindan where 
([waiter1]=@name			--摄影
or [waiter2]=@name			--化妆
or [waiter3]=@name			--选片
or [waiter4]=@name			--设计
or [waiter5]=@name			--初修
or [waiter5]=@name			--接单
or [waiter7]=@name			--精修
or [waiter8]=@name			--看样
or [waiter9]=@name			--????
or [waiter12]=@name			--摄影助理
or [waiter22]=@name)		--化妆助理	
and id in (select id from Payroll where Paydate >= @btm and Paydate <= @etm)

declare @name nvarchar(50)		--员工姓名;
declare @btm  nvarchar(24)		--开始时间;
declare @etm  nvarchar(24)		--结束时间;
set @name = '陈显亭'
set @btm = '2016-10-01'
set @etm = '2016-10-31'
select * from Payroll where (Paydate >= @btm and Paydate <= @etm) or (PhotoStatus = 'OK' and PhotogOfPaydate >= @btm and PhotogOfPaydate <= @etm) order by id

/* 查询出在指定收款时间范围内订单，再过筛选指定员工有在订单中服务过的所有订单详情 */
declare @name nvarchar(50)		--员工姓名;
declare @btm  nvarchar(24)		--开始时间;
declare @etm  nvarchar(24)		--结束时间;
set @name = '陈显亭'
set @btm = '2016-10-01'
set @etm = '2016-10-31'
select * from dindan where 
([waiter1]=@name			--摄影
or [waiter2]=@name			--化妆
or [waiter3]=@name			--选片
or [waiter4]=@name			--设计
or [waiter5]=@name			--初修
or [waiter5]=@name			--接单
or [waiter7]=@name			--精修
or [waiter8]=@name			--看样
or [waiter9]=@name			--????
or [waiter12]=@name			--摄影助理
or [waiter22]=@name)		--化妆助理	
and id in (
select distinct id from Payroll where (Paydate >= @btm and Paydate <= @etm) or (PhotoStatus = 'OK' and PhotogOfPaydate >= @btm and PhotogOfPaydate <= @etm))