Sql代码 收藏代码
1. USE [fau]
2. GO
3. /****** 对象: StoredProcedure [dbo].[sp_Check_Emp_Holiday] 脚本日期: 06/09/2010 13:34:40 ******/
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
8.
9. CREATE PROCEDURE [dbo].[sp_Check_Emp_Holiday]
10. as
11. declare @E_ID int,
12. @E_TYPE nvarchar(50),
13. @E_HIRE_DATE datetime,
14. @E_SOCIAL_WORK_DATE datetime
15. declare @Holiday numeric(4, 2),
16. @Days int
17. --定义一个游标并打开它
18. begin
19. declare mycursor cursor for select E_ID,E_TYPE,E_SOCIAL_WORK_DATE,E_HIRE_DATE from dbo.EMPLOYEE_INFO
20. open mycursor
21. --开始提取记录，放入指定的变量
22. fetch next from mycursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
23. while @@fetch_status = 0
24. begin
25. set @E_SOCIAL_WORK_DATE = DATEDIFF(day,@E_SOCIAL_WORK_DATE,getdate())
26. set @Holiday=0
27. /*员工类型为Loacl*/
28. if (@E_TYPE=N'Local')
29. begin
30. /*1.员工加入佛吉亚工作未满一年时，年假的计算按月份比例计算（每月为1.25天）, 15日前入职（含15日）不计算当月年假，15日后入职员工当月年假计算为1.25天。*/
31.
32. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
33. begin
34. set @Days=DATEPART(day, @E_HIRE_DATE)
35. if (@Days<=15)
36. set @Holiday=1.25*DATEDIFF(month,@E_HIRE_DATE,getdate())
37. else
38. set @Holiday=1.25*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
39. end
40. /* 员工加入佛吉亚后的第二年至第五年*/
41. /*员工加入佛吉亚后的第十一年至第二十年*/
42. --else if (CONVERT(int,@E_HIRE_DATE,0)/365.0>10 and CONVERT(int,@E_HIRE_DATE,0)/365.0<=20)
43. else if (dbo.ISOyear(@E_HIRE_DATE)>1 and dbo.ISOyear(@E_HIRE_DATE)<=5)
44. set @Holiday=15
45. /* 员工加入佛吉亚后的第六年至第十年*/
46. else if (dbo.ISOyear(@E_HIRE_DATE)>5 and dbo.ISOyear(@E_HIRE_DATE)<=10)
47. set @Holiday=17
48. /*员工加入佛吉亚后的第十一年至第二十年*/
49. else if (dbo.ISOyear(@E_HIRE_DATE)>10 and dbo.ISOyear(@E_HIRE_DATE)<=20)
50. set @Holiday=20
51. else
52. set @Holiday=20
53. end
54. /*当员工类型为Expats*/
55. else if (@E_TYPE=N'Expats')
56. begin
57. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
58. begin
59. set @Days=DATEPART(day, @E_HIRE_DATE)
60. if (@Days<=15)
61. set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
62. else
63. set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
64. end
65. else
66. begin
67. set @Holiday=24
68. end
69. end
70. /*当员工类型为Secondee*/
71. else if (@E_TYPE='Secondee')
72. begin
73. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
74. begin
75. set @Days=DATEPART(day, @E_HIRE_DATE)
76. if (@Days<=15)
77. set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
78. else
79. set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
80. end
81. else
82. begin
83. set @Holiday=24
84. end
85. end
86. /*当员工类型为VIE*/
87. else if (@E_TYPE=N'VIE')
88. begin
89. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
90. begin
91. set @Days=DATEPART(day, @E_HIRE_DATE)
92. if (@Days<=15)
93. set @Holiday=30.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
94. else
95. set @Holiday=30.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
96. end
97. else
98. begin
99. set @Holiday=30
100. end
101. end
102. /*当员工类型为spcial*/
103. else if (@E_TYPE=N'special')
104. begin
105. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
106. begin
107. set @Days=DATEPART(day, @E_HIRE_DATE)
108. if (@Days<=15)
109. set @Holiday=20.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
110. else
111. set @Holiday=20.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
112. end
113. else
114. begin
115. set @Holiday=20
116. end
117. end
118. /*当员工类型为spcial2*/
119. else if (@E_TYPE=N'special2')
120. begin
121. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
122. begin
123. set @Days=DATEPART(day, @E_HIRE_DATE)
124. if (@Days<=15)
125. set @Holiday=24.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
126. else
127. set @Holiday=24.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
128. end
129. else
130. begin
131. set @Holiday=24
132. end
133. end
134. /*当员工类型为spcial3*/
135. else if (@E_TYPE=N'special3')
136. begin
137. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
138. begin
139. set @Days=DATEPART(day, @E_HIRE_DATE)
140. if (@Days<=15)
141. set @Holiday=26.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
142. else
143. set @Holiday=26.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
144. end
145. else
146. begin
147. set @Holiday=26
148. end
149. end
150. --update dbo.EMPLOYEE_HOLIDAY set H_CREATE_NUM=@Holiday where H_STATUS='Y' and E_ID=@E_ID
151. exec dbo.SP_MODIFY_HOLIDAY @E_ID,@Holiday
152. fetch next from mycursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
153. end
154. --关闭游标，释放内存
155. close mycursor
156. deallocate mycursor
157. end
158.
159.
160.
161. USE [fau]
162. GO
163. /****** 对象: StoredProcedure [dbo].[SP_EMP_HOLIDAY] 脚本日期: 06/09/2010 13:35:15 ******/
164. SET ANSI_NULLS ON
165. GO
166. SET QUOTED_IDENTIFIER ON
167. GO
168.
169. CREATE PROCEDURE [dbo].[SP_EMP_HOLIDAY]
170. as
171.
172. declare @E_ID int,
173. @E_TYPE nvarchar(50),
174. @E_HIRE_DATE datetime,
175. @E_SOCIAL_WORK_DATE datetime
176. declare @Holiday numeric(4, 2),
177. @Days int
178. --定义一个游标并打开它
179. declare tablecursor cursor for select E_ID,E_TYPE,E_SOCIAL_WORK_DATE,E_HIRE_DATE from dbo.EMPLOYEE_INFO
180.
181. begin
182. open tablecursor
183. --开始提取记录，放入指定的变量
184. fetch next from tablecursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
185. while @@fetch_status = 0
186. begin
187. update dbo.EMPLOYEE_HOLIDAY
188. set H_STATUS='N'
189. where E_ID=@E_ID
190. set @E_SOCIAL_WORK_DATE = DATEDIFF(day,@E_SOCIAL_WORK_DATE,getdate())
191. set @Holiday=0
192. /*员工类型为Loacl*/
193. if (@E_TYPE=N'Local')
194. begin
195. /*1.员工加入佛吉亚工作未满一年时，年假的计算按月份比例计算（每月为1.25天）, 15日前入职（含15日）不计算当月年假，15日后入职员工当月年假计算为1.25天。*/
196.
197. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
198. begin
199. set @Days=DATEPART(day, @E_HIRE_DATE)
200. if (@Days<=15)
201. set @Holiday=1.25*DATEDIFF(month,@E_HIRE_DATE,getdate())
202. else
203. set @Holiday=1.25*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
204. end
205. /* 员工加入佛吉亚后的第二年至第五年*/
206. else if (dbo.ISOyear(@E_HIRE_DATE)>1 and dbo.ISOyear(@E_HIRE_DATE)<=5)
207. set @Holiday=15
208. /* 员工加入佛吉亚后的第六年至第十年*/
209. else if (dbo.ISOyear(@E_HIRE_DATE)>5 and dbo.ISOyear(@E_HIRE_DATE)<=10)
210. set @Holiday=17
211. /*员工加入佛吉亚后的第十一年至第二十年*/
212. else if (dbo.ISOyear(@E_HIRE_DATE)>10 and dbo.ISOyear(@E_HIRE_DATE)<=20)
213. set @Holiday=20
214. else
215. set @Holiday=20
216. end
217. /*当员工类型为Expats*/
218. else if (@E_TYPE=N'Expats')
219. begin
220. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
221. begin
222. set @Days=DATEPART(day, @E_HIRE_DATE)
223. if (@Days<=15)
224. set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
225. else
226. set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
227. end
228. else
229. begin
230. set @Holiday=24
231. end
232. end
233. /*当员工类型为Secondee*/
234. else if (@E_TYPE='Secondee')
235. begin
236. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
237. begin
238. set @Days=DATEPART(day, @E_HIRE_DATE)
239. if (@Days<=15)
240. set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
241. else
242. set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
243. end
244. else
245. begin
246. set @Holiday=24
247. end
248. end
249. /*当员工类型为VIE*/
250. else if (@E_TYPE=N'VIE')
251. begin
252. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
253. begin
254. set @Days=DATEPART(day, @E_HIRE_DATE)
255. if (@Days<=15)
256. set @Holiday=30.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
257. else
258. set @Holiday=30.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
259. end
260. else
261. begin
262. set @Holiday=30
263. end
264. end
265. /*当员工类型为spcial*/
266. else if (@E_TYPE=N'special')
267. begin
268. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
269. begin
270. set @Days=DATEPART(day, @E_HIRE_DATE)
271. if (@Days<=15)
272. set @Holiday=20.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
273. else
274. set @Holiday=20.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
275. end
276. else
277. begin
278. set @Holiday=20
279. end
280. end
281. /*当员工类型为spcial2*/
282. else if (@E_TYPE=N'special2')
283. begin
284. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
285. begin
286. set @Days=DATEPART(day, @E_HIRE_DATE)
287. if (@Days<=15)
288. set @Holiday=24.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
289. else
290. set @Holiday=24.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
291. end
292. else
293. begin
294. set @Holiday=24
295. end
296. end
297. /*当员工类型为spcial3*/
298. else if (@E_TYPE=N'special3')
299. begin
300. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
301. begin
302. set @Days=DATEPART(day, @E_HIRE_DATE)
303. if (@Days<=15)
304. set @Holiday=26.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
305. else
306. set @Holiday=26.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
307. end
308. else
309. begin
310. set @Holiday=26
311. end
312. end
313. -- print(@Holiday)
314. insert into dbo.EMPLOYEE_HOLIDAY (E_ID,EH_YEAR,H_CREATE_NUM,H_CREATE_DATE,H_STATUS) values
315. (@E_ID,year(getdate()),@Holiday,getdate(),N'Y')
316. fetch next from tablecursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
317. end
318. --关闭游标，释放内存
319. close tablecursor
320. deallocate tablecursor
321. end
322.
323. USE [fau]
324. GO
325. /****** 对象: StoredProcedure [dbo].[SP_MODIFY_HOLIDAY] 脚本日期: 06/09/2010 13:35:49 ******/
326. SET ANSI_NULLS ON
327. GO
328. SET QUOTED_IDENTIFIER ON
329. GO
330.
331. CREATE PROCEDURE [dbo].[SP_MODIFY_HOLIDAY]
332. (
333. @E_ID int,
334. @Holiday numeric(4, 2)
335. )
336. AS
337. declare @H_CREATE_NUM numeric(4, 2)
338. declare mycursor1 cursor for select H_CREATE_NUM from dbo.EMPLOYEE_HOLIDAY where E_ID=@E_ID and H_STATUS='Y'
339. BEGIN
340. open mycursor1
341. --开始提取记录，放入指定的变量
342. fetch next from mycursor1 into @H_CREATE_NUM
343. while @@fetch_status = 0
344. begin
345. if(@H_CREATE_NUM!=@Holiday)
346. begin
347. /*print @E_ID
348. print @H_CREATE_NUM;
349. print @Holiday;*/
350. update dbo.EMPLOYEE_HOLIDAY set H_STATUS='N' where E_ID=@E_ID and H_STATUS='Y'
351. insert into dbo.EMPLOYEE_HOLIDAY (E_ID,EH_YEAR,H_CREATE_NUM,H_CREATE_DATE,H_STATUS) values
352. (@E_ID,year(getdate()),@Holiday,getdate(),N'Y')
353. end
354. break;
355. end
356. --关闭游标，释放内存
357. close mycursor1
358. deallocate mycursor1
359. END
360.
361. USE [fau]
362. GO
363. /****** 对象: UserDefinedFunction [dbo].[ISOyear] 脚本日期: 06/09/2010 13:36:06 ******/
364. SET ANSI_NULLS ON
365. GO
366. SET QUOTED_IDENTIFIER ON
367. GO
368.
369. CREATE FUNCTION [dbo].[ISOyear] (@DATE datetime)
370. RETURNS int
371. WITH EXECUTE AS CALLER
372. AS
373. BEGIN
374. DECLARE @ISOyear int;
375. if(Year(Getdate())>Year(@DATE))
376. set @ISOyear=Year(Getdate())-Year(@DATE)
377. if(Month(Getdate())=Month(@DATE))
378. begin
379. if(Day(Getdate())>=Day(@DATE))
380. set @ISOyear=@ISOyear+1
381. end
382. else if (Month(Getdate())>Month(@DATE))
383. set @ISOyear=@ISOyear+1
384. RETURN(@ISOyear);
385. END;
386.
387.
388. set ANSI_NULLS ON
389. set QUOTED_IDENTIFIER ON
390. go
391.
392.
393. ALTER PROCEDURE [dbo].[sp_alterdiagram]
394. (
395. @diagramname sysname,
396. @owner_id int = null,
397. @version int,
398. @definition varbinary(max)
399. )
400. WITH EXECUTE AS 'dbo'
401. AS
402. BEGIN
403. set nocount on
404.
405. declare @theId int
406. declare @retval int
407. declare @IsDbo int
408.
409. declare @UIDFound int
410. declare @DiagId int
411. declare @ShouldChangeUID int
412.
413. if(@diagramname is null)
414. begin
415. RAISERROR ('Invalid ARG', 16, 1)
416. return -1
417. end
418.
419. execute as caller;
420. select @theId = DATABASE_PRINCIPAL_ID();
421. select @IsDbo = IS_MEMBER(N'db_owner');
422. if(@owner_id is null)
423. select @owner_id = @theId;
424. revert;
425.
426. select @ShouldChangeUID = 0
427. select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname
428.
429. if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
430. begin
431. RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
432. return -3
433. end
434.
435. if(@IsDbo <> 0)
436. begin
437. if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
438. begin
439. select @ShouldChangeUID = 1 ;
440. end
441. end
442.
443. -- update dds data
444. update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;
445.
446. -- change owner
447. if(@ShouldChangeUID = 1)
448. update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;
449.
450. -- update dds version
451. if(@version is not null)
452. update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;
453.
454. return 0
455. END
456.
457.
458.
459.
460. set ANSI_NULLS ON
461. set QUOTED_IDENTIFIER ON
462. go
463.
464.
465. ALTER PROCEDURE [dbo].[sp_creatediagram]
466. (
467. @diagramname sysname,
468. @owner_id int = null,
469. @version int,
470. @definition varbinary(max)
471. )
472. WITH EXECUTE AS 'dbo'
473. AS
474. BEGIN
475. set nocount on
476.
477. declare @theId int
478. declare @retval int
479. declare @IsDbo int
480. declare @userName sysname
481. if(@version is null or @diagramname is null)
482. begin
483. RAISERROR (N'E_INVALIDARG', 16, 1);
484. return -1
485. end
486.
487. execute as caller;
488. select @theId = DATABASE_PRINCIPAL_ID();
489. select @IsDbo = IS_MEMBER(N'db_owner');
490. revert;
491.
492. if @owner_id is null
493. begin
494. select @owner_id = @theId;
495. end
496. else
497. begin
498. if @theId <> @owner_id
499. begin
500. if @IsDbo = 0
501. begin
502. RAISERROR (N'E_INVALIDARG', 16, 1);
503. return -1
504. end
505. select @theId = @owner_id
506. end
507. end
508. -- next 2 line only for test, will be removed after define name unique
509. if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
510. begin
511. RAISERROR ('The name is already used.', 16, 1);
512. return -2
513. end
514.
515. insert into dbo.sysdiagrams(name, principal_id , version, definition)
516. VALUES(@diagramname, @theId, @version, @definition) ;
517.
518. select @retval = @@IDENTITY
519. return @retval
520. END
521.
522.
523. set ANSI_NULLS ON
524. set QUOTED_IDENTIFIER ON
525. go
526.
527.
528. ALTER PROCEDURE [dbo].[sp_dropdiagram]
529. (
530. @diagramname sysname,
531. @owner_id int = null
532. )
533. WITH EXECUTE AS 'dbo'
534. AS
535. BEGIN
536. set nocount on
537. declare @theId int
538. declare @IsDbo int
539.
540. declare @UIDFound int
541. declare @DiagId int
542.
543. if(@diagramname is null)
544. begin
545. RAISERROR ('Invalid value', 16, 1);
546. return -1
547. end
548.
549. EXECUTE AS CALLER;
550. select @theId = DATABASE_PRINCIPAL_ID();
551. select @IsDbo = IS_MEMBER(N'db_owner');
552. if(@owner_id is null)
553. select @owner_id = @theId;
554. REVERT;
555.
556. select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname
557. if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
558. begin
559. RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
560. return -3
561. end
562.
563. delete from dbo.sysdiagrams where diagram_id = @DiagId;
564.
565. return 0;
566. END
567.
568.
569. set ANSI_NULLS ON
570. set QUOTED_IDENTIFIER ON
571. go
572.
573.
574. ALTER PROCEDURE [dbo].[sp_helpdiagrams]
575. (
576. @diagramname sysname = NULL,
577. @owner_id int = NULL
578. )
579. WITH EXECUTE AS N'dbo'
580. AS
581. BEGIN
582. DECLARE @user sysname
583. DECLARE @dboLogin bit
584. EXECUTE AS CALLER;
585. SET @user = USER_NAME();
586. SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
587. REVERT;
588. SELECT
589. [Database] = DB_NAME(),
590. [Name] = name,
591. [ID] = diagram_id,
592. [Owner] = USER_NAME(principal_id),
593. [OwnerID] = principal_id
594. FROM
595. sysdiagrams
596. WHERE
597. (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
598. (@diagramname IS NULL OR name = @diagramname) AND
599. (@owner_id IS NULL OR principal_id = @owner_id)
600. ORDER BY
601. 4, 5, 1
602. END
603.
扫一扫
1763
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
