补出勤管理-保存补出勤_存储过程
-- =============================================
-- Author: YFC
-- Create date: 2020-07-12 10:09:50
-- Database: scm_main_hysj20200702
-- Description: 补出勤管理-保存补出勤【补出勤】
-- 保存补出勤_存储过程
-- =============================================
ALTER PROCEDURE [dbo].[bd_proc_saveRepair]
(
@orderNo VARCHAR(50)
,@orderId BIGINT
,@userSerial BIGINT
,@pcIp VARCHAR(20)
,@repairStartDate VARCHAR(100)
,@repairEndDate VARCHAR(100)
,@startTime INT
,@endTime INT
,@userIds VARCHAR(MAX)
,@approver1 BIGINT
,@approver2 BIGINT
,@applyType INT
,@remark VARCHAR(MAX)
,@repairType INT
,@orderNumber VARCHAR(50) -- 【杨雯雯 0520】
,@approverId VARCHAR(100) = ''
,@approveTime DATETIME = NULL
,@operateTime DATETIME = NULL
,@firstApproverUserNoAndNameList VARCHAR(MAX)
,@secondApproverUserNoAndNameList VARCHAR(MAX)
)
AS
BEGIN
DECLARE @users TABLE
(
user_serial BIGINT
)
INSERT INTO @users(user_serial)
SELECT value
FROM STRING_SPLIT(@userIds,',')
IF NOT EXISTS (
SELECT
1
FROM @users
)
BEGIN
RAISERROR ('明细数据不能为空', 16, 1);
RETURN
END
CREATE TABLE #return_table
(
user_serial BIGINT NOT NULL,
user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
)
--验证异常情况申报提交人能不能申请 begin 20220325 xiebingbing
--2020100913333640 HR
--2020100913335154 考勤员
--验证提交人是不是OA，是OA直接跳过，正常申请
IF (ISNULL(@orderNumber, '') = '')
BEGIN
--不是OA验证是否是管理员，是管理员正常申请，如果不是管理员，判断是不是正式工，是正式工不能申请，其它可以申请
DECLARE @gly_no VARCHAR(20) --管理员工号
select @gly_no=user_no from dt_user where user_serial=@userSerial
IF (ISNULL(@gly_no, '') != '')
BEGIN
IF NOT EXISTS (select 1 from WT_GLY where Gly_group
in('2020100913333640','2020100913335154')and Gly_no=@gly_no)
BEGIN
IF EXISTS (SELECT
1
FROM dt_user
WHERE user_serial=@userSerial and user_sny_lx LIKE '%正式职工%')
BEGIN
INSERT INTO #return_table (user_serial,user_no,user_name,dept_name,error_msg)
SELECT
user_serial
,user_no
,user_lname
,user_depname
,'正式员工不能在考勤系统申请异常情况申报'
FROM dt_user
WHERE user_serial=@userSerial
SELECT * FROM #return_table
RETURN
END
END
END
END
--验证异常情况申报提交人能不能申请 end 20220325 xiebingbing
--(杨雯雯1number补出勤)开始
--判断2：判断员工类型是否为“正式员工”
--新加判断杨雯雯0706
--IF(
--ISNULL(@orderNumber,'')=''
--)
--begin
--IF EXISTS (
-- SELECT 1 from dt_user WHERE user_serial in(select user_serial from @users) AND user_sny_lx LIKE '%正式职工%'
--)
--BEGIN
--INSERT INTO #return_table(user_serial,user_no,user_name,dept_name,error_msg)
--select user_serial,user_no,user_lname,user_depname,'正式职工不能进行补出勤'
--from dt_user WHERE user_serial in (SELECT user_serial FROM @users) AND user_sny_lx LIKE '%正式职工%'
--SELECT * FROM #return_table
--return
--END
--end
--(杨雯雯1number补出勤)结束
--生成日期
DECLARE @date TABLE
(
date_day DATETIME
)
DECLARE @dateFrom DATETIME = @repairStartDate
DECLARE @dateTo DATETIME = @repairEndDate
;WITH tmpDays AS
(
SELECT @dateFrom date_value
UNION ALL
SELECT date_value + 1
FROM tmpDays
WHERE date_value + 1 <= @dateTo
)
INSERT INTO @date (date_day)
SELECT CONVERT(VARCHAR(10), date_value, 120) FROM tmpDays OPTION (MAXRECURSION 0);
--日期和时间合并
DECLARE @dateAll TABLE
(
repair_date DATETIME
)
INSERT INTO @dateAll (repair_date)
SELECT DATEADD(MINUTE, @startTime, date_day) FROM @date
--开始时间和结束时间不添加
IF @startTime != @endTime
BEGIN
INSERT INTO @dateAll (repair_date)
SELECT DATEADD(MINUTE, @endTime, date_day) FROM @date
END
--考勤封账检测
--验证要用的数据
DECLARE @checkData TABLE
(
user_serial BIGINT,
repair_date DATE,
settle_year INT,
settle_month INT,
settle_date DATE,
is_lock INT DEFAULT 0,
limit INT DEFAULT 0,
is_save_time INT DEFAULT 0
)
INSERT INTO @checkData (user_serial, repair_date)
SELECT user_serial, date_day FROM @users, @date
--结算年月
UPDATE @checkData SET settle_date = dbo.bd_fn_getSettleDate(repair_date)
UPDATE @checkData SET settle_year = YEAR(settle_date), settle_month = MONTH(settle_date)
UPDATE cd
SET is_lock = 1
FROM @checkData cd
INNER JOIN bd_kq_accounts ac ON ac.user_serial = cd.user_serial AND ac.lock_status > 0 AND ac.year = cd.settle_year AND ac.month = cd.settle_month
INSERT INTO #return_table(user_serial, error_msg)
SELECT user_serial, '已封账的数据不能再修改'
FROM @checkData
WHERE ISNULL(is_lock, 0) > 0
--删除有问题的数据
DELETE FROM @checkData WHERE is_lock > 0
DELETE FROM @users WHERE user_serial IN (SELECT user_serial FROM #return_table)
--每个人申请上限检测
DECLARE @limit INT = 3
SELECT TOP 1 @limit = repair_limit FROM bd_sys_global_params
--已申请的次数
UPDATE cd
SET limit = g.repair_count
FROM @checkData cd
INNER JOIN (
SELECT
x.user_serial, x.settle_year, x.settle_month, COUNT(1) repair_count
FROM (
SELECT DISTINCT cd.user_serial, CONVERT(VARCHAR(10), lj.sj, 120) repair_date, cd.settle_year, cd.settle_month
FROM @checkData cd
INNER JOIN lr_jl lj ON lj.user_serial = cd.user_serial AND lj.settle_year = cd.settle_year AND lj.settle_month = cd.settle_month
INNER JOIN bd_repair_attendance main ON main.order_id = lj.order_id
WHERE main.is_del = 0 AND main.status != 2
) x
GROUP BY x.user_serial, x.settle_year, x.settle_month
) g ON g.user_serial = cd.user_serial
--当前申请次数
UPDATE cd
SET limit = ISNULL(limit, 0) + x.qty
FROM @checkData cd
INNER JOIN (
SELECT
YEAR(date_day) fYear, MONTH(date_day) fMonth, COUNT(1) qty
FROM @date
GROUP BY YEAR(date_day), MONTH(date_day)
) x ON x.fYear = cd.settle_year AND x.fMonth = cd.settle_month
INSERT INTO #return_table(user_serial, error_msg)
SELECT DISTINCT user_serial, CAST(settle_year AS VARCHAR(4)) + '-' + CAST(settle_month AS VARCHAR(2)) + '补出勤申请已经超过上限'
FROM @checkData
WHERE ISNULL(limit, 0) > @limit
--删除有问题的数据
DELETE FROM @checkData WHERE ISNULL(limit, 0) >= @limit
DELETE FROM @users WHERE user_serial IN (SELECT user_serial FROM #return_table)
--相同时间验证
INSERT INTO #return_table(user_serial, error_msg)
SELECT lj.user_serial, CONVERT(VARCHAR(20), repair_date, 120) + '申请时间重复'
FROM (
SELECT
repair_date, user_serial
FROM @dateAll
,@users
) repair
INNER JOIN lr_jl lj ON lj.user_serial = repair.user_serial AND lj.sj = repair.repair_date
INNER JOIN bd_repair_attendance main ON lj.order_id = main.order_id AND ISNULL(main.is_del, 0) = 0
DELETE FROM @users WHERE user_serial IN (SELECT user_serial FROM #return_table)
--保存数据
DECLARE @data TABLE
(
user_serial BIGINT,
repair_date DATETIME,
settle_year INT,
settle_month INT,
settle_date DATE
)
INSERT INTO @data (user_serial, repair_date, settle_date)
SELECT user_serial, repair_date, dbo.bd_fn_getSettleDate(repair_date)
FROM @users, @dateAll
UPDATE @data SET settle_year = YEAR(settle_date), settle_month = MONTH(settle_date)
IF EXISTS(
SELECT 1
FROM @data
)
BEGIN
DECLARE @total INT
SELECT @total = COUNT(1) FROM @users
BEGIN TRY
BEGIN TRAN
--先找到要改的表
INSERT INTO bd_repair_attendance (order_id, order_no, start_date, end_date, people_num, remark, creater_id, create_time, is_del, pc_ip,
status, approver1, approver2, apply_type, start_time, end_time, repair_type,order_number, last_approver, last_approver_time,first_approver_user_no_and_name_list,second_approver_user_no_and_name_list)
VALUES (@orderId, @orderNo, @repairStartDate, @repairEndDate, @total, @remark, @userSerial,
IIF(ISNULL(@orderNumber, '') = '', GETDATE(), @operateTime),
0, @pcIp,
IIF(ISNULL(@orderNumber, '') = '', 0, 3), @approver1, @approver2, @applyType, @startTime, @endTime, @repairType,
@orderNumber,
IIF(ISNULL(@orderNumber, '') = '', 0, @approverId),
IIF(ISNULL(@orderNumber, '') = '', NULL, @approveTime),@firstApproverUserNoAndNameList,@secondApproverUserNoAndNameList);
INSERT INTO lr_jl (sj, user_serial, bz, fx, gly_no, img_path, whether_validity, order_id, pc_ip, settle_year, settle_month)
SELECT repair_date, user_serial, NULL, 0, @userSerial, '', 0, @orderId, @pcIp, settle_year, settle_month
FROM @data
--【补出勤新加判断 杨雯雯nn早上 0723加上iden且赋值 开始】
IF (
ISNULL(@orderNumber, '') != ''
)
BEGIN
INSERT INTO kt_jl (sj, user_serial, fx, lx, yich, gly_no, jl_type, whether_validity, mode, order_id, pc_ip, pc_name, create_time, valid_date, iden)
SELECT
sj
,user_serial
,0
,0
,0
,CAST(@approver1 AS VARCHAR)
,0
,0
,1
,order_id
,pc_ip
,pc_name
,GETDATE()
,NULL
,'OA' AS iden
FROM lr_jl
WHERE order_id = @orderId
END
--【补出勤新加判断 杨雯雯nn早上 0707 结束】
COMMIT TRAN
END TRY
BEGIN CATCH
DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END;
RAISERROR (@errStr, 16, 1);
END CATCH
END
ELSE
BEGIN
IF NOT EXISTS(
SELECT 1
FROM #return_table
)
BEGIN
INSERT INTO #return_table(user_serial, error_msg)
SELECT 0, '没有明细数据，不能保存'
END
END
UPDATE rt
SET user_no = u.user_no
,user_name = u.user_lname
,dept_name = u.dep_name
FROM #return_table rt
INNER JOIN (
SELECT
user_serial, user_no, user_lname, dep_name
FROM bd_view_user_all
) u ON u.user_serial = rt.user_serial
SELECT user_serial, user_no, user_name, dept_name, error_msg FROM #return_table;
DROP TABLE #return_table;
END
内容解析
该存储过程实现了补出勤的保存功能。输入参数包括订单号、订单ID、用户序号、PC IP地址、补出勤开始日期、补出勤结束日期、开始时间、结束时间、申请人ID、审批人1、审批人2、申请类型、备注、补出勤类型、申请单号、审批人ID、审批时间、操作时间、第一和第二审批人员号和姓名列表。该过程会验证申请提交人是否具备申请资格，同时进行多项数据验证，包括考勤封账检测、每个人申请上限检测、相同时间验证等。最后，将验证结果以及相关信息保存到数据库中。
原文地址: https://www.cveoy.top/t/topic/nDEn 著作权归作者所有。请勿转载和采集!