USE [taupd_BIZAPP]
GO
/****** Object: StoredProcedure [dbo].[plan_scheduling] Script Date: 2021/6/29 11:16:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: gxb
-- Create date: 2020-10-28
-- Description: 企业微信 值班消息提示，每天上午9:30执行作业
-- exec plan_scheduling
-- =============================================
ALTER PROCEDURE [dbo].[plan_scheduling]
AS
BEGIN
--RETURN
DECLARE @PK_ins_leader UNIQUEIDENTIFIER,@PK_emp UNIQUEIDENTIFIER,@msg_str VARCHAR(MAX),@to_user VARCHAR(MAX),@job_no VARCHAR(50),@leader_job_no VARCHAR(50),@sch_type VARCHAR(50),@pdate DATE,@PK_bill UNIQUEIDENTIFIER,@bill_name VARCHAR(50),@emp_name VARCHAR(50),@leader_name VARCHAR(50)
SET @bill_name='值班表'
--获取提前两天发的消息
DECLARE c CURSOR FOR
SELECT PK_sch_dtl FROM dbo.bd_sch_dtl WHERE dl=0 AND DATEDIFF(dd,GETDATE(),pdate)=2
OPEN c
FETCH NEXT FROM c INTO @PK_bill
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @PK_ins_leader=PK_ins_leader,@PK_emp=a.PK_emp,@job_no=b.user_weixin,@emp_name=b.user_name,@leader_job_no=c.job_no,@leader_name=c.emp_name,@pdate=pdate,
@sch_type=(CASE sch_type WHEN '上午' THEN '上午8：30' WHEN '下午' THEN '下午14：30' WHEN '夜班' THEN '晚上' END)
FROM dbo.bd_sch_dtl a JOIN dbo.bd_user b ON a.PK_emp=b.PK_user JOIN dbo.bd_employee c ON a.PK_ins_leader=c.PK_emp WHERE PK_sch_dtl=@PK_bill ORDER BY pdate desc
DECLARE @org_name VARCHAR(50),@leader_org_name VARCHAR(50),@PK_org UNIQUEIDENTIFIER,@emp_job_no VARCHAR(50)
SELECT @org_name=org_name,@PK_org=PK_org,@emp_job_no=job_no FROM dbo.vBd_employee WHERE PK_emp=@PK_emp
SELECT @leader_org_name=org_name FROM dbo.vBd_employee WHERE PK_emp=@PK_ins_leader
--SELECT @emp_job_no=job_no FROM dbo.vBd_employee WHERE PK_emp=@PK_emp
DECLARE @str_msg2 VARCHAR(500),@send_time DATETIME
IF(@sch_type='晚上')
BEGIN
IF EXISTS(SELECT * FROM dbo.bd_workdate WHERE @pdate=work_date)
SELECT @str_msg2=CASE is_worktime WHEN 2 THEN '晚20：30-次日早8：30' ELSE '晚17：30-次日早8：30' END
,@sch_type=CASE is_worktime WHEN 2 THEN '晚上20：30' ELSE '晚上17：30' END
FROM dbo.bd_workdate WHERE @pdate=work_date
ELSE
BEGIN
SET @str_msg2='晚17：30-次日早8：30'
SET @sch_type='晚上17：30'
END
SET @send_time=DATEADD(minute,-2310,CONVERT(datetime,@pdate))
END
ELSE IF(@sch_type='上午8：30')
BEGIN
SET @str_msg2='上午8：30-14：30'
SET @send_time=DATEADD(minute,-2310,CONVERT(datetime,@pdate))
END
ELSE IF(@sch_type='下午14：30')
BEGIN
SET @str_msg2='下午14：30-20：30'
SET @send_time=DATEADD(minute,-2310,CONVERT(datetime,@pdate))
END
--'工号为'+@emp_job_no+'的'+
SET @msg_str=@emp_name+'同志（'+@org_name+'），请于 '+CONVERT(VARCHAR(50),DATEPART(YEAR,@pdate))+'年'+CONVERT(VARCHAR(50),DATEPART(month,@pdate))+'月'
+CONVERT(VARCHAR(50),DATEPART(day,@pdate))+'日('+DATENAME(weekday,@pdate)+')'+@sch_type+' 准时到岗值班，并做好值班记录。'
PRINT @str_msg2
SET @msg_str=@msg_str--+'值班时间：'+@str_msg2
PRINT @msg_str
--'6FC60AF3-ADD0-43C0-AA40-C8F2D2426F42' 主任
SET @to_user=(SELECT DISTINCT STUFF(
(SELECT '|'+job_no FROM dbo.bd_employee a WITH(NOLOCK) JOIN md_taupd.dbo.bd_user b ON a.PK_emp=b.PK_user JOIN md_taupd.dbo.bd_roleuser c ON b.PK_user=c.PK_user
WHERE PK_role IN ('32C55C32-6A44-4957-B06C-0E697F98F0D1','DBD15F68-F2D2-4126-A65C-9C76DD08ED21','6FC60AF3-ADD0-43C0-AA40-C8F2D2426F42') AND c.PK_org=@PK_org FOR XML PATH(''))
,1,1,'') AS a)
SET @to_user=@to_user+'|'+@job_no
PRINT @to_user
INSERT INTO wx_app.dbo.WX_msg
([PK_wx_msg]
,[PK_bill]
,[bill_name]
,[msg_user]
,[msg_content]
,[msg_send_time],is_success)
VALUES
(NEWID()
,@PK_bill
,@bill_name
,@to_user
,@msg_str
,@send_time,0)
FETCH NEXT FROM c INTO @PK_bill
END
CLOSE c
DEALLOCATE c
PRINT 'b'
--获取提前一天发的消息（明天上午值班）
DECLARE c CURSOR FOR
SELECT PK_sch_dtl FROM dbo.bd_sch_dtl WHERE dl=0 AND DATEDIFF(dd,GETDATE(),pdate)=1 AND sch_type='上午'
OPEN c
FETCH NEXT FROM c INTO @PK_bill
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @job_no=b.user_weixin,@emp_name=b.user_name,@leader_job_no=c.job_no,@leader_name=c.emp_name,@pdate=pdate,
@sch_type=(CASE sch_type WHEN '上午' THEN '上午8：30' WHEN '下午' THEN '下午14：30' WHEN '夜班' THEN '晚上' END)
FROM dbo.bd_sch_dtl a JOIN dbo.bd_user b ON a.PK_emp=b.PK_user JOIN dbo.bd_employee c ON a.PK_ins_leader=c.PK_emp WHERE PK_sch_dtl=@PK_bill AND sch_type='上午' ORDER BY pdate desc
SELECT @org_name=org_name,@PK_org=PK_org FROM dbo.vBd_employee WHERE job_no=@job_no
SELECT @leader_org_name=org_name FROM dbo.vBd_employee WHERE job_no=@leader_job_no
IF(@sch_type='上午8：30')
BEGIN
SET @str_msg2='上午8：30-14：30'
SET @send_time=DATEADD(minute,-270,CONVERT(datetime,@pdate))
END
SET @msg_str=@emp_name+'同志（'+@org_name+'），请于 '+CONVERT(VARCHAR(50),DATEPART(YEAR,@pdate))+'年'+CONVERT(VARCHAR(50),DATEPART(month,@pdate))+'月'
+CONVERT(VARCHAR(50),DATEPART(day,@pdate))+'日('+DATENAME(weekday,@pdate)+')'+@sch_type+' 准时到岗值班，并做好值班记录。'
PRINT 'c'
PRINT @str_msg2
SET @msg_str=@msg_str--+'值班时间：'+@str_msg2
PRINT @msg_str
--'6FC60AF3-ADD0-43C0-AA40-C8F2D2426F42' 主任
SET @to_user=(SELECT DISTINCT STUFF(
(SELECT '|'+job_no FROM dbo.bd_employee a WITH(NOLOCK) JOIN md_taupd.dbo.bd_user b ON a.PK_emp=b.PK_user JOIN md_taupd.dbo.bd_roleuser c ON b.PK_user=c.PK_user
WHERE PK_role IN ('32C55C32-6A44-4957-B06C-0E697F98F0D1','DBD15F68-F2D2-4126-A65C-9C76DD08ED21','6FC60AF3-ADD0-43C0-AA40-C8F2D2426F42') AND c.PK_org=@PK_org FOR XML PATH(''))
,1,1,'') AS a)
SET @to_user=@job_no
INSERT INTO wx_app.dbo.WX_msg
([PK_wx_msg]
,[PK_bill]
,[bill_name]
,[msg_user]
,[msg_content]
,[msg_send_time],is_success)
VALUES
(NEWID()
,@PK_bill
,@bill_name
,@to_user
,@msg_str
,@send_time,0)
FETCH NEXT FROM c INTO @PK_bill
END
CLOSE c
DEALLOCATE c
--获取明天值班的值班领导
DECLARE c CURSOR FOR
SELECT PK_sch_dtl FROM dbo.bd_sch_dtl WHERE dl=0 AND DATEDIFF(dd,GETDATE(),pdate)=1
OPEN c
FETCH NEXT FROM c INTO @PK_bill
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @job_no=b.user_weixin,@emp_name=b.user_name,@leader_job_no=c.user_weixin,@leader_name=c.user_name,@pdate=pdate,
@sch_type=(CASE sch_type WHEN '上午' THEN '上午8：30' WHEN '下午' THEN '下午14：30' WHEN '夜班' THEN '晚上' END)
FROM dbo.bd_sch_dtl a JOIN dbo.bd_user b ON a.PK_emp=b.PK_user JOIN dbo.bd_user c ON a.PK_ins_leader=c.PK_user WHERE PK_sch_dtl=@PK_bill AND DATEDIFF(dd,GETDATE(),pdate)=1 ORDER BY pdate desc
SELECT @org_name=org_name,@PK_org=PK_org FROM dbo.vBd_employee WHERE job_no=@job_no
SELECT @leader_org_name=org_name FROM dbo.vBd_employee WHERE job_no=@leader_job_no
IF(@sch_type='晚上')
BEGIN
IF EXISTS(SELECT * FROM dbo.bd_workdate WHERE @pdate=work_date)
SELECT @str_msg2=CASE is_worktime WHEN 2 THEN '晚20：30-次日早8：30' ELSE '晚17：30-次日早8：30' END
,@sch_type=CASE is_worktime WHEN 2 THEN '晚上20：30' ELSE '晚上17：30' END
FROM dbo.bd_workdate WHERE @pdate=work_date
ELSE
BEGIN
SET @str_msg2='晚17：30-次日早8：30'
SET @sch_type='晚上17：30'
END
SET @send_time=DATEADD(minute,-870,CONVERT(datetime,@pdate))
END
ELSE IF(@sch_type='上午8：30')
BEGIN
SET @str_msg2='上午8：30-14：30'
SET @send_time=DATEADD(minute,-870,CONVERT(datetime,@pdate))
END
ELSE IF(@sch_type='下午14：30')
BEGIN
SET @str_msg2='下午14：30-20：30'
SET @send_time=DATEADD(minute,-870,CONVERT(datetime,@pdate))
END
SET @msg_str=@leader_name+'同志'+'，请于 '+CONVERT(VARCHAR(50),DATEPART(YEAR,@pdate))+'年'+CONVERT(VARCHAR(50),DATEPART(month,@pdate))+'月'
+CONVERT(VARCHAR(50),DATEPART(day,@pdate))+'日('+DATENAME(weekday,@pdate)+')'+@sch_type+' 准时到岗值班，并做好值班记录。'
PRINT @str_msg2
SET @msg_str=@msg_str--+'值班时间：'+@str_msg2
PRINT @msg_str
--'6FC60AF3-ADD0-43C0-AA40-C8F2D2426F42' 主任
INSERT INTO wx_app.dbo.WX_msg
([PK_wx_msg]
,[PK_bill]
,[bill_name]
,[msg_user]
,[msg_content]
,[msg_send_time],is_success)
VALUES
(NEWID()
,@PK_bill
,@bill_name
,@leader_job_no
,@msg_str
,@send_time,0)
FETCH NEXT FROM c INTO @PK_bill
END
CLOSE c
DEALLOCATE c
--获取当天发的消息
DECLARE c CURSOR FOR
SELECT PK_sch_dtl FROM dbo.bd_sch_dtl WHERE dl=0 AND sch_type IN('夜班','下午') AND DATEDIFF(dd,GETDATE(),pdate)=0
OPEN c
FETCH NEXT FROM c INTO @PK_bill
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @job_no=b.user_weixin,@emp_name=b.user_name,@leader_job_no=c.job_no,@leader_name=c.emp_name,@pdate=pdate,
@sch_type=(CASE sch_type WHEN '上午' THEN '上午8：30' WHEN '下午' THEN '下午14：30' WHEN '夜班' THEN '晚上' END)
FROM dbo.bd_sch_dtl a JOIN dbo.bd_user b ON a.PK_emp=b.PK_user JOIN dbo.bd_employee c ON a.PK_ins_leader=c.PK_emp WHERE PK_sch_dtl=@PK_bill AND sch_type IN('夜班','下午') ORDER BY pdate desc
SELECT @org_name=org_name,@PK_org=PK_org FROM dbo.vBd_employee WHERE job_no=@job_no
SELECT @leader_org_name=org_name FROM dbo.vBd_employee WHERE job_no=@leader_job_no
IF(@sch_type='晚上')
BEGIN
IF EXISTS(SELECT * FROM dbo.bd_workdate WHERE @pdate=work_date)
SELECT @str_msg2=CASE is_worktime WHEN 2 THEN '晚20：30-次日早8：30' ELSE '晚17：30-次日早8：30' END
,@sch_type=CASE is_worktime WHEN 2 THEN '晚上20：30' ELSE '晚上17：30' END
FROM dbo.bd_workdate WHERE @pdate=work_date
ELSE
BEGIN
SET @str_msg2='晚17：30-次日早8：30'
SET @sch_type='晚上17：30'
END
SET @send_time=DATEADD(minute,570,CONVERT(datetime,@pdate))
END
ELSE IF(@sch_type='下午14：30')
BEGIN
SET @str_msg2='下午14：30-20：30'
SET @send_time=DATEADD(minute,570,CONVERT(datetime,@pdate))
END
SET @msg_str=@emp_name+'同志（'+@org_name+'），请于 '+CONVERT(VARCHAR(50),DATEPART(YEAR,@pdate))+'年'+CONVERT(VARCHAR(50),DATEPART(month,@pdate))+'月'
+CONVERT(VARCHAR(50),DATEPART(day,@pdate))+'日('+DATENAME(weekday,@pdate)+')'+@sch_type+' 准时到岗值班，并做好值班记录。'
PRINT @str_msg2
SET @msg_str=@msg_str--+'值班时间：'+@str_msg2
PRINT @msg_str
--'6FC60AF3-ADD0-43C0-AA40-C8F2D2426F42' 主任
SET @to_user=(SELECT DISTINCT STUFF(
(SELECT '|'+job_no FROM dbo.bd_employee a WITH(NOLOCK) JOIN md_taupd.dbo.bd_user b ON a.PK_emp=b.PK_user JOIN md_taupd.dbo.bd_roleuser c ON b.PK_user=c.PK_user
WHERE PK_role IN ('32C55C32-6A44-4957-B06C-0E697F98F0D1','DBD15F68-F2D2-4126-A65C-9C76DD08ED21','6FC60AF3-ADD0-43C0-AA40-C8F2D2426F42') AND c.PK_org=@PK_org FOR XML PATH(''))
,1,1,'') AS a)
SET @to_user=@job_no
INSERT INTO wx_app.dbo.WX_msg
([PK_wx_msg]
,[PK_bill]
,[bill_name]
,[msg_user]
,[msg_content]
,[msg_send_time],is_success)
VALUES
(NEWID()
,@PK_bill
,@bill_name
,@to_user
,@msg_str
,@send_time,0)
FETCH NEXT FROM c INTO @PK_bill
END
CLOSE c
DEALLOCATE c
END怎么将提示信息框修改成为 固定的：“尊敬的部门负责人，还有一名离职人员的交接单您没有审批通过，距离系统自动确认时间还有1天，请您尽快处理。”
这个存储过程和你的有你相关问题的查询嘛？