版本号：V1.0.4
发布日期：2023-09.18
更新者：ZPX
功能描述：彤帆程序匹配莱特数据库所需脚本
注意：如果已经有该存储过程或表请勿替换掉客户现有的脚本。
第一个：
create proc  [dbo].[HoliDays_Details]
@empID nvarchar(10),--工号
@itemID nvarchar(10)--项目号
--@empName nvarchar(10) out,--姓名
--@deptName nvarchar(10) out,--部门名称
--@lastMonth nvarchar(10) out,--上月剩余年假天数
--@applayed nvarchar(10) out,--当月已请年假天数
--@applaying nvarchar(10) out--当月流程中正在申请的年假天数
as
declare @WorkdaytoH real
--一天合多少小时
SELECT distinct @WorkdaytoH=Convert(int,paraValue) FROM HrSysParameters WHERE paraID='workhoursPerDay'
--select CONVERT(varchar(7),GETDATE(),120)
select distinct * from  (
select thePeriod,
convert(decimal(18,2),ISNULL((SELECT TOP 1 ISNULL(f.remainDays,0)/480.0 as remainDays  FROM empSpecialHolidays f 
WHERE f.empID=t1.empID AND f.itemID=t1.itemID AND f.thePeriod<t1.thePeriod
order by seq desc),0) )'MonthRemain'
,convert(decimal(18,2),ISNULL(t2.reqDays,0)) 'Using'
,convert(decimal(18,2),ISNULL(t3.reqDays,0)) 'Used' from
--上月剩余
(
select empID,thePeriod,itemID,sum(usedDays)/(@WorkdaytoH*60) usedDays
FROM empSpecialHolidays 
where empID=@empID and itemID=@itemID and usedDays<>0
group by empID,itemID,thePeriod
)t1
left join
--申请中的
(select iwer.empid,iwr.itemid,CONVERT(varchar(7),iwr.reqDate,120) as reqDate,sum(iwer.reqDays) as reqDays
from interimWtEmpRequests as iwer,interimWtRequests as iwr
where iwer.reqid=iwr.reqid and iwer.reqid not in(
select regid from _sql_Flow_Details where States in ('2','11'))
and iwr.itemid=@itemID and iwer.empid=@empID
group by iwer.empid,iwr.itemid,CONVERT(varchar(7),iwr.reqDate,120) 
) t2 on t1.thePeriod=t2.reqDate
--已申请的
left join(
select empid,itemid,reqDate,sum(reqDays) as reqDays from 
(SELECT  wer.empID,wr.itemID,CONVERT(varchar(7),wr.reqDate,120) as reqDate
,sum(wer.reqDays) as reqDays FROM wtEmpRequests as wer,wtRequests as wr
WHERE wer.reqID=wr.reqID AND wr.itemID=@itemID and wer.empID=@empID
group by wer.empID,wr.itemID,CONVERT(varchar(7),wr.reqDate,120)
union all
SELECT  empID,itemid,thePeriod, SUM(usedDays)/480.0 AS a FROM  dbo.empSpecialHolidays
WHERE  (itemID = @itemID) and empid=@empID AND operateType<>6 
group by empID,itemid,thePeriod)t group by empid,itemid,reqDate
) t3 on t1.thePeriod=t3.reqDate
where thePeriod<(select theperiod from HrSysPeriodModuals where sysID='03' and workstatus='0') 
union all
select distinct t1.theperiod1,convert(decimal(18,2),t1.remainDays) as 'MonthRemain',convert(decimal(18,2),ISNULL(t2.reqDays,0)) 'Using',convert(decimal(18,2),ISNULL(t3.reqDays,0)) 'Used'
from 
(
select empid,remainDays,thePeriod,theperiod1 from (select * from ( 
select top 1 t.empID,t.remainDays/480.0 as remainDays,t.theperiod,t.theperiod1 from (select a.*,b.thePeriod as theperiod1
from empSpecialHolidays a,(select empid,max(seq) as seq,m.thePeriod
from empSpecialHolidays,(select theperiod from HrSysPeriodModuals where sysID='03' and workstatus='0')m
where itemid=@itemID and empSpecialHolidays.thePeriod<=m.thePeriod group by empID,m.thePeriod) as b
where a.itemid=@itemID and a.empid=b.empid and a.seq=b.seq) as t
 where empID=@empID and itemID=@itemID and itemID='h03' order by seq desc) as temp1
 union
(select top 1 t.empID,t.remainDays/480.0 as remainDays,t.theperiod,t.theperiod1 from (select a.*,b.thePeriod as theperiod1
from empSpecialHolidays a,(select empid,max(seq) as seq,m.thePeriod
from empSpecialHolidays,(select theperiod from HrSysPeriodModuals where sysID='03' and workstatus='0')m
where itemid=@itemID and empSpecialHolidays.thePeriod<=m.thePeriod group by empID,m.thePeriod) as b
where a.itemid=@itemID and a.empid=b.empid and a.seq=b.seq) as t
 where empID=@empID and itemID=@itemID and itemID<>'h03'
order by seq desc) ) as temp

)t1
left join 
(
--当前月流程中
select iwer.empid,iwr.itemid,CONVERT(varchar(7),iwr.reqDate,120) as reqDate,sum(iwer.reqDays) as reqDays
from interimWtEmpRequests as iwer,interimWtRequests as iwr
where iwer.reqid=iwr.reqid and iwer.reqid not in(
select regid from _sql_Flow_Details where States in ('2','11'))
and iwr.itemid=@itemID and iwer.empid=@empID
and CONVERT(varchar(7),iwr.reqDate,120) in (select theperiod from HrSysPeriodModuals where sysID='03' and workstatus='0')
group by iwer.empid,iwr.itemid,CONVERT(varchar(7),iwr.reqDate,120) 
)t2 on t1.empid=t2.empID--t2.reqDate=t1.thePeriod
left join 
(
---当前月已申请
select empid,itemid, reqDate ,sum(reqDays) as reqDays from
(SELECT  wer.empID,wr.itemID,/*CONVERT(varchar(7),wr.reqDate,120)*/th.thePeriod as reqDate,sum(wer.reqDays) as reqDays FROM wtEmpRequests as wer,wtRequests as wr,(select top 1 * from HrSysPeriods order by thePeriod desc) as th
WHERE wer.reqID=wr.reqID 
AND wr.itemID=@itemID and wer.empID=@empID
and CONVERT(varchar(10),wr.reqDate,23) between CONVERT(char(10),th.startDate,23) and CONVERT(char(10),th.endDate,23)
group by wer.empID,wr.itemID,/*CONVERT(varchar(7),wr.reqDate,120)*/th.thePeriod
union all 
SELECT  empID,itemid,thePeriod, SUM(usedDays)/480.0 AS a FROM  dbo.empSpecialHolidays
WHERE  (itemID = @itemID)  AND operateType<>6  AND (CONVERT(char(7), thePeriod, 23)) = (select theperiod from HrSysPeriodModuals where sysID='03' and workstatus='0')and empID=@empID
GROUP BY empID,itemid,thePeriod)t group by empid,itemid,reqDate
)t3 on t1.empID=t3.empID--t3.reqDate=t1.thePeriod
where t1.remainDays<>0/*t2.reqDays<>0 or t3.reqDays<>0*/) as temp where thePeriod>='2019-01'
GO


第二个：
CREATE TABLE [dbo].[PendingInfo](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmpId] [nvarchar](20) NOT NULL,
	[pEmpId] [nvarchar](20) NOT NULL,
	[pEmpName] [nvarchar](20) NOT NULL,
	[StartDate] [date] NOT NULL,
	[EndDate] [date] NOT NULL,
	[CancelDate] [date] NULL,
	[Status] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PendingInfo] ADD  DEFAULT ('') FOR [CancelDate]
GO

ALTER TABLE [dbo].[PendingInfo] ADD  DEFAULT ((0)) FOR [Status]
GO