lyfz_repos/经典版源码/document/数据库维护脚本/工资查询语句.sql

/*
--34 [renyuan]
select count(*) as cot from renyuan 
where name='KK' and dimission='在职'
--name='KK' and dimission='在职';

------------------------------------------------------------------------------------------------------------
--59 [dindanbukuanview]
-- date属于收款日期;
select count(*) as cot from dindanbukuanview 
where date>='2014-10-01' and date<='2014-10-31'
--date>='2014-10-01' and date<='2014-10-31';


------------------------------------------------------------------------------------------------------------
--60 [dindan]
select count(*) as cot from dindan


------------------------------------------------------------------------------------------------------------
--198 [singleincomemoneyview] 
-- date为补款时间;
select count(*) as cot from singleincomemoneyview 
where date>='2014-10-01' and date<='2014-10-31' and renyuan3='KK'
--date>='2014-10-01' and date<='2014-10-31' and renyuan3='KK';


------------------------------------------------------------------------------------------------------------
--61 [dindan][dindanbukuan] 
--time2为拍照时间;
select count(*) as cot from dindan 
where time2>='2014-10-01' and time2<='2014-10-31' and status='OK'

-- date属于收款日期;
select count(*) as cot from dindanbukuan 
where date<'2014-10-01'
--time2>='2014-10-01' and time2<='2014-10-31' and status='OK'***date<'2014-10-01';


------------------------------------------------------------------------------------------------------------
--142 [dindan][mywork] -datetime为工作安排时间，一般在计件式里用;
select count(*) as cot from dindan
select count(*) as cot from mywork 
where datetime>='2014-10-01' and datetime<='2014-10-31' and name='KK'
--datetime>='2014-10-01' and datetime<='2014-10-31' and name='KK';


------------------------------------------------------------------------------------------------------------
--192 [dindan][dindanjd]	-date景点的拍照日期;
select count(*) as cot from dindan
select count(*) as cot from dindanjd 
where [date]>='2014-10-01' and [date]<='2014-10-31' and [status]='OK' 
and ([waiter1]='KK' or [waiter12]='KK' or [waiter2]='KK' or [waiter22]='KK')
--[date]>='2014-10-01' and [date]<='2014-10-31' and [status]='OK' and ([waiter1]='KK' or [waiter12]='KK' or [waiter2]='KK' or [waiter22]='KK');


------------------------------------------------------------------------------------------------------------
--193 [digitalview]	-time8初修日期 -time9精修时间
select count(*) as cot from digitalview 
where ([time8]>='2014-10-01' and [time8]<='2014-10-31' and [status4]='OK' and [waiter5]='KK') 
or ([time9]>='2014-10-01' and [time9]<='2014-10-31' and [status6]='OK' and [waiter7]='KK') 
or ([time10]>='2014-10-01' and [time10]<='2014-10-31' and [status5]='OK' and [waiter4]='KK')

--([time8]>='2014-10-01' and [time8]<='2014-10-31' and [status4]='OK' and [waiter5]='KK') or ([time9]>='2014-10-01' and [time9]<='2014-10-31' and [status6]='OK' and [waiter7]='KK') or ([time10]>='2014-10-01' and [time10]<='2014-10-31' and [status5]='OK' and [waiter4]='KK')
*/

/************************************************************************/
/*
	程序编写： Jeff
	版    本:  V 1.0
	建立日期： 2015-06-10
	功能说明： 工资计算;
	备    注： 
	修改日期：
	修改说明：
*/
/************************************************************************/ 
use [db]
declare @name nvarchar(36)		--要计算工资的员工姓名;
declare @btm  nvarchar(24)		--要计算工资的起始时间;
declare @etm  nvarchar(24)		--要计算工资的结束时间;

set @name = '覃琴'
set @btm = '2015-06-01'
set @etm = '2015-06-12'

select * from renyuan where name = @name and dimission='在职'

-- 工资有时计算不到,就是因为kind字段在客户端代码里只验证3和4;若用户自己添加的话,则是中一个数字;因此经常有收款数据没有计算到;
select * from dindanbukuanview where date >= @btm and date <= @etm order by kind	

select * from singleincomemoneyview where date >= @btm and date <= @etm and renyuan3 = @name
select * from dindan where time2 >= @btm and time2 <= @etm and status='OK'
select * from dindanbukuan where date >= @btm and date <= @etm
select * from mywork where datetime >= @btm and datetime <= @etm and name = @name
select * from dindanjd where [date] >= @btm and [date] <= @btm and [status]='OK' and ([waiter1]=@name or [waiter12]=@name or [waiter2]=@name or [waiter22]=@name)
select * from digitalview 
where ([time8] >= @btm and [time8] <= @btm and [status4]='OK' and [waiter5]=@name) 
or ([time9]>= @btm and [time9]<= @btm and [status6]='OK' and [waiter7]=@name) 
or ([time10]>= @btm and [time10]<= @btm and [status5]='OK' and [waiter4]=@name)
GO
----------------------------------------------------------------------------------


/*
1.查询时间段
2.查询的员工

A.查询与该员工相关的所有订单号,与时间无关.
select * from dindan 
where ([waiter1]='' or [waiter2]='' or [waiter3]='' or [waiter4]='' or [waiter5]='' or [waiter7]='' or [waiter8]='' or [waiter9]=''
or [waiter12]='' or [waiter22]='') and ([status]='OK' or [status2]='OK' or [status4]='OK' or [status6]='OK' or [status5]='OK')

--订单号+订单金额+订单时间+拍照OK时间+选片OK时间+初修OK时间+精修OK时间+设计OK时间+所有工作状态+所有流程人员
订单号+订单金额+订单时间+所有工作时间+所有工作状态+所有流程人员

B.查询该时间段内所有的收款记录,与时间有关.
select * from dindanbukuan 
where date>='2014-10-01' and date<='2014-10-31'

订单号+收款金额+收款时间

C.查询B内出现的订单信息,
select * from dindan where id in(select id from dindanbukuan where date>='2014-10-01' and date<='2014-10-31')
订单号+订单金额+订单时间+所有工作时间+所有工作状态+所有流程人员

D.查询A,B内出现的订单的收款记录,(或者只查A,B已查不需重复查)
select * from dindanbukuan where id in(A)

select * from dindanbukuan 
where id in(select id from dindan 
where ([waiter1]='刘璐璐' or [waiter2]='刘璐璐' or [waiter3]='刘璐璐' or [waiter4]='刘璐璐' 
or [waiter5]='刘璐璐' or [waiter7]='刘璐璐' or [waiter8]='刘璐璐' or [waiter9]='刘璐璐'
or [waiter12]='刘璐璐' or [waiter22]='刘璐璐')) 
or (date>='2014-10-01' and date<='2014-10-31')

E.查询其他二销singleincomemoneyview的收款记录
select * from singleincomemoneyview where date >='' and date <='' and renyuan3=''

F.查询数码工作安排
select count(*) as cot from mywork 
where datetime>='2014-10-01' and datetime<='2014-10-31' and name='KK'

H.

*/
--------------------------------------------------------
use [db]
go
declare @name nvarchar(50)		--员工姓名;
declare @btm  nvarchar(24)		--开始时间;
declare @etm  nvarchar(24)		--结束时间;

set @name = '覃琴'
set @btm = '2015-06-01'
set @etm = '2015-06-12'

--1,首次过滤,查询该时间段内所有的收款记录,与时间有关(得到指定时间的收款订单号)
--select * from dindanbukuan where date>=@btm and date<=@etm
select * from dindanbukuanview where date>=@btm and date<=@etm order by id desc

--2,再次过滤,查找出与员工有关的且出现在1中的订单(得到与指定员工有关的收款订单号)
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 dindanbukuanview where date>=@btm and date<=@etm) order by id desc

--2,再次过滤,查找出与员工有关的且出现在1中的订单(得到与指定员工有关的收款订单号)
select id, status, status2, status3, status4, status5, status6, status7,status8,
money1, money2, money3, taoxijiage, time1,time2,time3, time4, time5, time6, time7, time8, time9, time10, ren, discount, payed1, payed2, payed3, payed4, payed5,
waiter1, waiter12, waiter13, waiter14, waiter2, waiter22, waiter23, waiter24, waiter3, waiter4, waiter5, waiter6, waiter7, waiter8, waiter9 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 dindanbukuanview where date>=@btm and date<=@etm) order by id desc

--3,再次过滤,得到与指定员工有关的指定时间的收款订单号;
select * from dindanbukuanview 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 dindanbukuanview where date>=@btm and date<=@etm)) and date>=@btm and date<=@etm order by id desc

--4,获取其他收入;
select * from singleincomemoneyview where date >= @btm and date <= @etm and renyuan3 = @name

--5,数码师工作;
select * from mywork where datetime >= @btm and datetime <= @etm and name = @name









create view [dbo].[Payroll] as select 
dbo.dindan.name1, 
dbo.dindan.name2, 
dbo.dindan.txtype, 
dbo.dindan.taoxiname,
dbo.dindanbukuan.money, 
(case when dbo.dindan.time2 > dbo.dindanbukuan.date then dbo.dindan.time2 else dbo.dindanbukuan.date end) as Paydate,
dbo.dindanbukuan.kind, 
dbo.dindanbukuan.ren,
dbo.dindanbukuan.bz, 
dbo.dindanbukuan.id,
dbo.dindanbukuan.paytype from dbo.dindan 
inner JOIN dbo.dindanbukuan ON dbo.dindan.id = dbo.dindanbukuan.id 
and dbo.dindan.status = 'OK'
GO

*/
/*
select * from (
select 
dbo.dindan.status,
dbo.dindan.name1, 
dbo.dindan.name2, 
dbo.dindan.txtype, 
dbo.dindan.taoxiname,
dbo.dindan.time2,
dbo.dindanbukuan.money, 
dbo.dindanbukuan.date, 
(case when dbo.dindan.time2 > dbo.dindanbukuan.date then dbo.dindan.time2 else dbo.dindanbukuan.date end) as Paydate,
dbo.dindanbukuan.kind, 
dbo.dindanbukuan.ren,
dbo.dindanbukuan.bz, 
dbo.dindanbukuan.id,
dbo.dindanbukuan.paytype from dbo.dindan 
INNER JOIN dbo.dindanbukuan ON dbo.dindan.id = dbo.dindanbukuan.id
) t where status = 'OK' and time2 > date
*/

-- 查询出订单拍照OK的所有订单(不是取件OK的);
--select * from [dbo].[dindan] where [dbo].[dindan].[status] = 'OK' and [dbo].[dindan].[time2] > [dbo].[dindanbukuan].[date]
--select * from 
--(select * from [dbo].[dindan] INNER JOIN dbo.dindanbukuan ON dbo.dindan.id = dbo.dindanbukuan.id ) where [dbo].[dindan].[status] = 'OK' and [dbo].[dindan].[time2] > [dbo].[dindanbukuan].[date] 



select 
dbo.dindan.name1, 
dbo.dindan.name2, 
dbo.dindan.txtype, 
dbo.dindan.taoxiname,
dbo.dindanbukuan.money, 
(case when dbo.dindan.time2 > dbo.dindanbukuan.date then dbo.dindan.time2 else dbo.dindanbukuan.date end) as Paydate,
dbo.dindanbukuan.kind, 
dbo.dindanbukuan.ren,
dbo.dindanbukuan.bz, 
dbo.dindanbukuan.id,
dbo.dindanbukuan.paytype from dbo.dindan 
left JOIN dbo.dindanbukuan ON dbo.dindan.id = dbo.dindanbukuan.id 
where dbo.dindan.status = 'OK'

select count(*) from dindan
select count(*) from dindanbukuan

select 
dbo.dindan.name1, 
dbo.dindan.name2, 
dbo.dindan.txtype, 
dbo.dindan.taoxiname,
dbo.dindanbukuan.money, 
(case when dbo.dindan.time2 > dbo.dindanbukuan.date then dbo.dindan.time2 else dbo.dindanbukuan.date end) as Paydate,
(case when dbo.dindan.time2 > dbo.dindanbukuan.date then 0 else 1 end) as Pay,
dbo.dindanbukuan.kind, 
dbo.dindanbukuan.ren,
dbo.dindanbukuan.bz, 
dbo.dindanbukuan.id,
dbo.dindanbukuan.paytype from dbo.dindan 
inner JOIN dbo.dindanbukuan ON dbo.dindan.id = dbo.dindanbukuan.id 
where dbo.dindan.status = 'OK'-- and dbo.dindan.time2 > dbo.dindanbukuan.date



---------------------------------------------------------------------------------------------------------------------------------