马保山的笔记

OA

介绍

管理员工作

基础工作：

  1. 组织架构管理
  2. 人员管理
  3. 系统维护

二开：

  1. 手机批量授权

  2. 流程组设置用户可见范围

  3. 考勤管理

  4. 合同管理 （公司二叉树表获取字段值)、合同编号生成规则、合同查询表、合同权限表、合同维护表

  5. 流程发起四位编号

系统升级

2015年 oa系统迁移从**公司迁移过来：增加费用报销模块

升级到8.0：增加企业微信

升级到8.6.7：兼容edge浏览器

二开SQL语句

手机授权s

declare @usersid varchar(20)
declare @mobile varchar(50)
declare @num int
set @mobile='mobile'
set @num=1
while ( @num <= 10 )
begin
--select * from AppUsers
--select * from AppUserslog 
set  @usersid = (
select top 1 a.UserID  from Users a 
left join dbo.UsersInfo b  on a.UserID=b.UserID 
where a.UserID not in  (select userid from AppUsers) and DeleteFlag='0')
 --设置userid值,不在userid 中 和  DeleteFlag='0'  
 --dbo.UsersInfo  deleteflag 表示用户是否删除  0 代表正常用户  1 代表已删除用户

if @usersid is  null  break   --判定如果是空值终止循环
else
INSERT INTO dbo.AppUserslog  (userid,xieruriqi) VALUES (@usersid,Convert(Datetime,GetDate(),2) )  --写入log日志
INSERT INTO  dbo.AppUsers (appname,userid) VALUES (@mobile, @usersid) --插入值
set @num=@num+1  
end

用户数量大于95 自动执行

declare @yonghu int

set  @yonghu=(select COUNT(*) from  users where LonginStatus<>0)

Update   users set LonginStatus=0 where LonginStatus<>0 and @yonghu > =95

日志清除

USE [master]
 GO
ALTER DATABASE [C6] 
SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [C6] 
SET RECOVERY SIMPLE
GO
USE [C6]
GO
DBCC SHRINKFILE (N'C6_log' , 0,TRUNCATEONLY)
GO
 USE [master]
GO
ALTER DATABASE [C6] 
SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [C6] 
SET RECOVERY FULL
 GO

考勤模块

exec 菜单_考勤授权

USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[菜单_考勤授权]    Script Date: 2022/11/14 10:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	ALTER PROCEDURE [dbo].[菜单_考勤授权] as
	
	--考勤授权
	--用户授权 2020年11月5日
	declare @userid varchar(100), @num int

set @num=1
while ( @num <= 10 )
begin

	set @userid=(select  top 1 UserID from vw_Users  where DeleteFlag=0 and userid not in( select DISTINCT ObjectValue from Role2Object) )
	--人员插入


	if @userid is  null  break   --判定如果是空值终止循环
    else
	insert into Role2Object  VALUES (1421,'user',@userid)
	set @num=@num+1  
end


exec 年假_年假
USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[年假_年假]    Script Date: 2022/11/14 10:07:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[年假_年假] as
update ygqingjiadan_zibiao  set gonghao=a.gonghao from ygqingjiadan a inner join ygqingjiadan_zibiao b on a.mainid=b.mainid and b.是否同步=0
update ygqingjiadan_zibiao  set xingming=a.xingming from ygqingjiadan a inner join ygqingjiadan_zibiao b on a.mainid=b.mainid and b.是否同步=0

declare  @xingming varchar(50),
         @gonghao varchar(50),
		 @mainid varchar(50),
		 @start_time varchar(50),
		 @minute float,
		 @是否同步 varchar(50)

DECLARE cur CURSOR STATIC for				--声明静态游标
 SELECT  gonghao,mainid,minute,是否同步,start_time  FROM   ygqingjiadan_zibiao A INNER JOIN ModuleApproveFlag AS C ON A.mainid = C.ID AND C.App_Flag = 1 AND C.DelFlag <> 1 
 where   是否同步=0 and ApplyLeaveType='年假'
OPEN cur								--打开游标
FETCH next FROM cur INTO  @gonghao,@mainid,@minute,@是否同步,@start_time   --取数据
WHILE ( @@fetch_status = 0 ) 
 BEGIN

 --select * from qidiqingjienengyuannianjia 
 declare @ssickleave float,@pd float,@zhi float,@ysickleave float,@ksickleave float
 select @ysickleave=(select yholidays   from  qidiqingjienengyuannianjia where employeeid=@gonghao)
 select @ssickleave=(select sholidays  from  qidiqingjienengyuannianjia where employeeid=@gonghao)
  select @ksickleave=(select kholidays   from  qidiqingjienengyuannianjia where employeeid=@gonghao)
  --如果pd 当前申请+已申请         当前申请大于已申请 @zhi=   @ksickleave 一共可修天数 其他输出pd
 set @pd=@ysickleave+@minute
 if  (@pd>@ssickleave) begin set @zhi=@ksickleave end
                else   begin set @zhi=@pd  end

update qidiqingjienengyuannianjia set  yholidays=@pd where employeeid=@gonghao and year=DATENAME(year,GETDATE())
update ygqingjiadan_zibiao   set 是否同步=1   where gonghao=@gonghao  and start_time=@start_time and mainid=@mainid


	   FETCH NEXT   FROM cur into @gonghao,@mainid,@minute,@是否同步,@start_time     --游标移到下一个记录
			   END
		 CLOSE cur        --关闭游标
		 DEALLOCATE cur       --释放游标


exec 年假_病假
USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[年假_病假]    Script Date: 2022/11/14 10:07:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[年假_病假] as
update ygqingjiadan_zibiao  set gonghao=a.gonghao from ygqingjiadan a inner join ygqingjiadan_zibiao b on a.mainid=b.mainid and b.是否同步=0
update ygqingjiadan_zibiao  set xingming=a.xingming from ygqingjiadan a inner join ygqingjiadan_zibiao b on a.mainid=b.mainid and b.是否同步=0

declare  @xingming varchar(50),
         @gonghao varchar(50),
		 @mainid varchar(50),
		 @start_time varchar(50),
		 @minute float,
		 @是否同步 varchar(50)

DECLARE cur CURSOR STATIC for				--声明静态游标
 SELECT  gonghao,mainid,minute,是否同步,start_time  FROM   ygqingjiadan_zibiao A INNER JOIN ModuleApproveFlag AS C ON A.mainid = C.ID AND C.App_Flag = 1 AND C.DelFlag <> 1 
 where   是否同步=0 and ApplyLeaveType='病假' and  start_time>'2022-01-01'
OPEN cur								--打开游标
FETCH next FROM cur INTO  @gonghao,@mainid,@minute,@是否同步,@start_time   --取数据
WHILE ( @@fetch_status = 0 ) 
 BEGIN
 declare @ssickleave float,@pd float,@zhi float,@ysickleave float,@ksickleave float
 select @ysickleave=(select ysickleave  from  qidiqingjienengyuannianjia where employeeid=@gonghao)
 select @ssickleave=(select ssickleave  from  qidiqingjienengyuannianjia where employeeid=@gonghao)
  select @ksickleave=(select ksickleave  from  qidiqingjienengyuannianjia where employeeid=@gonghao)
  --如果pd 当前申请+已申请         当前申请大于已申请 @zhi=   @ksickleave 一共可修天数 其他输出pd
 set @pd=@ysickleave+@minute
 if  (@pd>@ssickleave) begin set @zhi=@ksickleave end
                else   begin set @zhi=@pd  end

update qidiqingjienengyuannianjia set ysickleave=@pd where employeeid=@gonghao and year=DATENAME(year,GETDATE())
--print @zhi
--declare @cs varchar(50)
--select @cs=(select ssickleave  from qidiqingjienengyuannianjia where employeeid=@gonghao  and year=DATENAME(year,GETDATE()))
--print @cs
update ygqingjiadan_zibiao   set 是否同步=1   where gonghao=@gonghao  and start_time=@start_time and mainid=@mainid


	   FETCH NEXT   FROM cur into @gonghao,@mainid,@minute,@是否同步,@start_time     --游标移到下一个记录
			   END
		 CLOSE cur        --关闭游标
		 DEALLOCATE cur       --释放游标






exec 考勤排班创建
USE [MiddleData]
GO
/****** Object:  StoredProcedure [dbo].[考勤排班创建]    Script Date: 2022/11/14 10:08:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[考勤排班创建]
as



--清空排版

create table #LH (d datetime)
--插入当前日期到2021年底
INSERT into  #LH
select dateadd(d,number,stuff(convert(varchar,getdate()-100,23),9,2,'1')) 'd'
  from master.dbo.spt_values
  where type='P' and  dateadd(d,number,stuff(convert(varchar,getdate()-100,23),9,2,'1'))<getdate()+200
 --select getdate()-30
  --创建排版表

--create table  paiban (d datetime,w varchar(20),x varchar(20))
--清空排版表
delete from paiban
--插件到排版表 星期or上班
INSERT into  paiban
select  convert(varchar(20),d,23) 'd',
       case datepart(dw,d) when 1 then '星期日'
                           when 2 then '星期一'
                           when 3 then '星期二'
                           when 4 then '星期三'
                           when 5 then '星期四'
                           when 6 then '星期五'
                           when 7 then '星期六' end 'w',
       case when datepart(dw,d) in(1,7) then '休息'
            when datepart(dw,d) in(2,3,4,5,6) then '上班' end 'x'

from #LH


--select * from #LH
drop table #LH


exec 年假_历史年假
USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[年假_历史年假]    Script Date: 2022/11/14 10:08:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[年假_历史年假] as
update ygqingjiadan_zibiao  set gonghao=a.gonghao from ygqingjiadan a inner join ygqingjiadan_zibiao b on a.mainid=b.mainid and b.是否同步=0
update ygqingjiadan_zibiao  set xingming=a.xingming from ygqingjiadan a inner join ygqingjiadan_zibiao b on a.mainid=b.mainid and b.是否同步=0

declare  @xingming varchar(50),
         @gonghao varchar(50),
		 @mainid varchar(50),
		 @start_time varchar(50),
		 @minute float,
		 @是否同步 varchar(50)
DECLARE cur CURSOR STATIC for				--声明静态游标
 SELECT  gonghao,mainid,minute,是否同步,start_time  FROM   ygqingjiadan_zibiao A INNER JOIN ModuleApproveFlag AS C ON A.mainid = C.ID AND C.App_Flag = 1 AND C.DelFlag <> 1 
 where   是否同步=0 and ApplyLeaveType='历史年假' and  start_time>'2022-01-01'
OPEN cur								--打开游标
FETCH next FROM cur INTO  @gonghao,@mainid,@minute,@是否同步,@start_time   --取数据
WHILE ( @@fetch_status = 0 ) 
 BEGIN
 declare @ssickleave float,@pd float,@zhi float,@ysickleave float,@ksickleave float
 select @ysickleave=(select ysickleave  from  qidiqingjienengyuannianjia where employeeid=@gonghao) --已休带薪病假(天)

 select @ssickleave=(select ssickleave  from  qidiqingjienengyuannianjia where employeeid=@gonghao) --剩余带薪病假(天)

  select @ksickleave=(select lishiyixiuholidays  from  qidiqingjienengyuannianjia where employeeid=@gonghao)  --历史已休年假(天)
  --如果pd 当前申请+已申请         当前申请大于已申请 @zhi=   @ksickleave 一共可修天数 其他输出pd
 set @pd=@ysickleave+@minute
 if  (@pd>@ssickleave) begin set @zhi=@ksickleave end
                else   begin set @zhi=@pd  end

update qidiqingjienengyuannianjia set lishiyixiuholidays=@pd where employeeid=@gonghao and year=DATENAME(year,GETDATE())
--print @zhi
--declare @cs varchar(50)
--select @cs=(select ssickleave  from qidiqingjienengyuannianjia where employeeid=@gonghao  and year=DATENAME(year,GETDATE()))
--print @cs
update ygqingjiadan_zibiao   set 是否同步=1   where gonghao=@gonghao  and start_time=@start_time and mainid=@mainid


	   FETCH NEXT   FROM cur into @gonghao,@mainid,@minute,@是否同步,@start_time     --游标移到下一个记录
			   END
		 CLOSE cur        --关闭游标
		 DEALLOCATE cur       --释放游标

流程组

exec 流程租

USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[流程租]    Script Date: 2022/11/14 10:09:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[流程租] as
declare @DeptName  varchar(100), @DeptID  int , @userid varchar(max)



--创建 流程组_部门 表单
if object_id('c6..流程组_部门') is  null  
Begin  
     CREATE TABLE 流程组_部门   (id int,DeptName  varchar(max)) 
     insert into  流程组_部门 VALUES (0,'启迪清洁能源'),(0,'瑞行集团'),(0,'华业阳光'),(0,'山东华业'),(0,'战略协同中心'),(0,'启迪简石'),(0,'启迪森诺'),(0,'启迪创新'),(0,'启迪清风')
End
--drop TABLE 流程组_部门
--创建临时表并插入数值

-- select * from #dept
DECLARE  @name NVARCHAR(50)    --声明变量，需要读取的数据
DECLARE cur CURSOR STATIC				--声明静态游标
FOR
    SELECT  DeptName  FROM    流程组_部门
OPEN cur								--打开游标
FETCH next FROM cur INTO  @DeptName    --取数据
WHILE ( @@fetch_status = 0 ) 
BEGIN
-- 设置需要的部门名称
--select top 1 @DeptName=DeptName from  流程组_部门
--update 流程组_部门 set id=1  where DeptName=@DeptName
--print @DeptName

select  @DeptID=DeptID from Department  where DeptDelFlag=0 and  DeptName=@DeptName
--如果临时表存在删除
if object_id('tempdb..#renyuan ') is not null  
Begin  
       DROP TABLE  #renyuan  
End

CREATE TABLE #renyuan  (userid varchar(max))
--临时表赋值
insert into #renyuan 
select userid  from RelationshipUsers where  DeptID in (
select DeptID from Department  where DeptDelFlag=0 and  DeptName=@DeptName
UNION ALL
select DeptID from Department  where DeptParentID=@DeptID
UNION ALL
select DeptID  from Department  where DeptParentID in (select DeptID from Department  where DeptParentID=@DeptID and DeptDelFlag=0 ) and DeptDelFlag=0
UNION ALL

select  DeptID  from Department  where DeptParentID in 
(select DeptID  from Department  where DeptParentID in 
(select DeptID  from Department  where DeptParentID=@DeptID and DeptDelFlag=0 ) ))

SELECT @userid=STUFF((SELECT  + userid + ',' FROM (select * from #renyuan where userid<>'liuhaipan' ) a  FOR  XML PATH('')  ), 1,0, '')

--select * from #renyuan
--每行尾部，添加管理员
set  @userid=@userid+'yg00023' 

update Filter set FilterContent=@userid where  FilterTitle=@DeptName
FETCH next FROM cur INTO  @DeptName

drop TABLE #renyuan
end


CLOSE cur								--关闭游标
DEALLOCATE cur

exec 员工同步到考勤
USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[员工同步到考勤]    Script Date: 2022/11/14 10:10:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER proc  [dbo].[员工同步到考勤] as

--创建临时表 为了公司归属查询
declare @DeptName varchar(100), @DeptID varchar(100)

set @DeptName='启迪清洁能源'
set @DeptID='1722'

if object_id('tempdb..#kaoqinqintongbulinshi ') is not null  
Begin  
       DROP TABLE  #kaoqinqintongbulinshi 
End

CREATE TABLE #kaoqinqintongbulinshi  (userid varchar(100),gs varchar(100))

insert into #kaoqinqintongbulinshi 
select userid,'启迪清能'  from RelationshipUsers where  DeptID in (
select DeptID from Department  where DeptDelFlag=0 and  DeptName=@DeptName
UNION ALL
select DeptID from Department  where DeptParentID=@DeptID
UNION ALL
select DeptID  from Department  where DeptParentID in (select DeptID from Department  where DeptParentID=@DeptID and DeptDelFlag=0 ) and DeptDelFlag=0
UNION ALL

select  DeptID  from Department  where DeptParentID in 
(select DeptID  from Department  where DeptParentID in 
(select DeptID  from Department  where DeptParentID=@DeptID and DeptDelFlag=0 ) ))


--select * from Department 


set @DeptName='华业阳光'
set @DeptID='1904'

insert into #kaoqinqintongbulinshi 
select userid,'华业阳光'  from RelationshipUsers where  DeptID in (
select DeptID from Department  where DeptDelFlag=0 and  DeptName=@DeptName
UNION ALL
select DeptID from Department  where DeptParentID=@DeptID
UNION ALL
select DeptID  from Department  where DeptParentID in (select DeptID from Department  where DeptParentID=@DeptID and DeptDelFlag=0 ) and DeptDelFlag=0
UNION ALL

select  DeptID  from Department  where DeptParentID in 
(select DeptID  from Department  where DeptParentID in 
(select DeptID  from Department  where DeptParentID=@DeptID and DeptDelFlag=0 ) ))








declare @employeename varchar(100),
--工号
@employeeid varchar(100),
--是否考勤
@kq varchar(100),

--mainid
@MainID  varchar(100),
--公司
@gs varchar(100),

--获取userID
@UserID varchar(100)
/*
select * from RelationshipUsers
select * from vw_Users where UserName in('武涛','马保山')
vw_
*/



--设置一次最多插入10个员工
/*
DECLARE @i int
set @i=0

WHILE @i<10 and @UserID<>'' 
BEGIN
*/
--select * from users where UserName in( '王亮','马保山','徐银鸿','王海燕')
--select * from vw_Users where UserName in( '王亮','马保山','徐银鸿','王海燕')
--select * from UsersInfo where userid ='yg00023'
--select * from users where userid='150007'


--select * from users where userID='12754'
--update  users set kq=0  where UserName like '徐银鸿'
--select * from users where UserName like '马保山'
--2022年5月31日 添加删除的员工不同步信息 UsersInfo DeleteFlag=1   select userID from UsersInfo where DeleteFlag=1
set @UserID=(select top 1 UserID  from Users  where kq=1 and gh  not in (select employeeid  from nianjiayuangongxinxibiaozb) and UserID not in (select userID from UsersInfo where DeleteFlag=1) and userID not in('12754','150007','12639')  )

--select gh  from Users where UserID='12768'

set @employeeid=(select gh  from Users where UserID=@UserID)
--select @employeeid
set @employeename =(select UserName  from Users where UserID=@UserID)
--select  @employeename
set @kq='是'
--设置公司
set @gs=(select gs from #kaoqinqintongbulinshi where @UserID=userid ) 

set @MainID=(select 'KQX'+cast(max(right(MainID,8))+10000001 as varchar)  from nianjiayuangongxinxibiaozb)

--select CONVERT(varchar(100), GETDATE(), 23),0,@MainID,@employeename,@employeeid,@gs,@kq

--插入新入职员工信息


select @MainID,@employeename,@employeeid,@gs,@kq
if (@UserID<>'')


--select * from nianjiayuangongxinxibiaozb

begin

INSERT INTO [dbo].[nianjiayuangongxinxibiaozb]
           ([time]
           ,[swork]
           ,[MainID]
           ,[employeename]
           ,[employeeid]
           ,[公司]
           ,[是否考勤]
           ,[是否离职])
     VALUES ( CONVERT(varchar(100), GETDATE(), 23),0,@MainID,@employeename,@employeeid,@gs,@kq,'')
	 end
/*
set @i=@i+1

END
*/
--设置离职员工考勤未否

--设置员工未离职

/*暂时放弃
select * from nianjiayuangongxinxibiaozb a
left join  Users b on   a.employeeid=b.gh 
left join vw_Users c on b.UserID=c.UserID and a.employeename=c.UserName
where  a.是否离职='' and c.DeleteFlag=1 and b.kq=1 

select * from vw_Users  where DeleteFlag = 1 and 

*/
--修改员工是否考勤



--删除临时表
DROP TABLE  #kaoqinqintongbulinshi


--select * from #kaoqinqintongbulinshi where userid='12768'
--select * from  nianjiayuangongxinxibiaozb where employeeid='12768'

BI分析

dataease 流程汇总表更新



exec 流程
USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[流程]    Script Date: 2022/11/14 10:11:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  ALTER  PROCEDURE  [dbo].[流程] as
  drop table 流程汇总表
  select * into 流程汇总表  from  流程汇总试图  --old
 --select *  from  流程汇总表
 update 流程汇总表 set App_Flag='已结束'  where App_Flag='1' 
 update 流程汇总表 set App_Flag='未结束'  where App_Flag='2' 
  -- 删除的流程计入结束
 update 流程汇总表 set App_Flag='已结束'  where DelFlag='1'
 update 流程汇总表 set DelFlag='删除'  where DelFlag='1'
 update 流程汇总表 set DelFlag='正常'  where DelFlag='0'

 --在开始步骤的设置为一结束
 update 流程汇总表 set App_Flag='已结束'  where AppD_Name='开始'

结束流程

USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[结束流程]    Script Date: 2022/11/14 10:11:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[结束流程]  @mainid VARCHAR(100)  as
--2021年8月2日更新
DECLARE @Instance_ID VARCHAR(100)
--set  @mainid='JHC00078949'
set @Instance_ID=(select Instance_ID from jhoa_approve  where  AppO_Values=@mainid  group by  AppO_Values,Instance_ID)
--Status=completed 状态执行语句   running
if EXISTS(select Status from JHOA_Approve_Instance   WHERE Instance_ID=@Instance_ID and  Status='running')
--begin 可以一次执行多个update语句,下次不在执行
begin
UPDATE JHOA_Approve_Instance SET Status='completed'  WHERE Instance_ID=@Instance_ID

--app_state=0  表示结束 1表示审批中
update jhoa_approve set app_state=0 where app_id=(select max(app_id) from jhoa_approve where appo_values=@mainid)
--流程结束值app_state=0      未结束app_over=1
update jhoa_approve set app_over=1 where appo_values=@mainid
--流程结束值 app_flag 审批结束标记:2，正在审批；1审批结束通过；0审批结束不通过；
update moduleapproveflag set app_flag=1 where id=@mainid
end

 exec matprocanchuangbiaoge 流程弹窗双击弹窗

考勤视图转表
exec [MiddleData].dbo.TongBuDaomiddleKu
exec 对接外出单同步
exec 华业对接外出单同步
exec [dbo].[考勤视图转表]

简石合同序号插入


INSERT INTO [dbo].[jianshi_bianhao]
(id,type,year,company)

select  '000' as id, t2.a1 as type , year(getdate())  as year,t1.a1 as company   from 简石合同序号查询 t1 
left join 简石合同序号查询 t2  on 1=1
where t1.a2=2 and t2.a2=1 
update jianshi_bianhao  set mainid= 'JBA0000000000'+CONVERT(varchar(10),zid)

简石-合同寻呼提醒作业
exec [dbo].[pt_QDJSMessage]

USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[pt_QDJSMessage]    Script Date: 2022/11/14 10:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[pt_QDJSMessage]  

AS
BEGIN

declare @mainid varchar(50),@remindpeople varchar(100),@content varchar(200),@djsn varchar(500),@djname varchar(100)
set @djsn='qdjshtsp2019_703c27d9-7015-47b1-bbc8-a55dde3726d2'
set @djname='启迪简石合同审批2019'
		DECLARE H_SETTLE SCROLL CURSOR FOR select mainid,remindpeople,content from qidijianshihetongshenpizd where mainid in (select distinct b.AppO_Values from jhoa_approve_instance a inner join jhoa_approve b on a.Instance_ID=b.Instance_ID where Template_ID='2d82a6bb63a54a10b56c7c0a84ef03c3' and Status='completed') and dateDiff(dd,data,getdate())>=0 and dateDiff(dd,data,getdate())<3

		OPEN H_SETTLE       --打开游标
		FETCH FIRST FROM H_SETTLE INTO @mainid,@remindpeople,@content  --游标移到第一个记录
		WHILE (@@FETCH_STATUS=0)        --检查游标是不是最后一个，如果不是则进行中间的程序
			   BEGIN

					declare @strXMLList varchar(max)   ---寻呼接受人列表  参考格式：<?xml version="1.0" encoding="gb2312"?><root><record><CallToUser>0050</CallToUser></record></root>
					declare @strSlaveXml varchar(max)  ---弹出寻呼窗口的人  参考格式：<?xml version="1.0" encoding="gb2312"?><root><record><UserID><![CDATA[0050]]></UserID></record></root>
					declare @strSender varchar(max)    ---寻呼发送者
					declare @strCallContent varchar(max)  ---寻呼内容
					declare	@dbName varchar(100)
					declare @p4 int
					declare @p5 int
					set @p4=NULL
					
					set @strSender=(select top 1 userid from users where username=@remindpeople)
					declare @app_id int,@AppT_ID varchar(256),@url varchar(8000)
					select top 1 @app_id=app_id,@AppT_ID=appt_id from jhoa_approve where appo_values= @mainid order by app_id desc
					set @url='../Jhsoft.Web.module/fceform/common/djframe.htm?djsn='+@djsn+'&djtype=TT&DjName='+@djname
					set @url+= '&opentype=2&ModuleID='+@djsn+'&IsNew=1&OperatorTypeWFHsh=FS&httpAppID='+cast(@app_id as varchar)+'&httpOID=0&paravalue='+@mainid+'&_FlowTemplateID='+@AppT_ID
					set @strCallContent='<a href = '''+@url+'''>'+@content+'</a>'
					set @dbName=DB_NAME()
					
					exec pt_CreateID @dbName=@dbName,@tbName='Call',@intOutput=1,@strID=@p4 output
					select @p4
					set @strXMLList='<?xml version="1.0" encoding="gb2312"?><root><record><CallToUser>'+@strSender+'</CallToUser></record></root>'
					set  @strSlaveXml='<?xml version="1.0" encoding="gb2312"?><root><record><UserID><![CDATA['+  @strSender  +']]></UserID></record></root>'
					exec   pt_CallInsert  @strXMLList=@strXMLList,@intCallID=@p4,@intFatherID=@p4,@strCallContent=@strCallContent,@strSender=@strSender,@strSenderDepart='',@strSmsSign='0',@strSmsTel='',@strFileID='',@strModuleID='',@strMessageID='',@strSlaveXml=''
					exec pt_CreateID @dbName=@dbName,@tbName='CallTemp',@intOutput=1,@strID=@p5 output
					select @p5
					exec pt_SendMessage @strXML=@strSlaveXml,@messageType='Call',@CallContent=@p4,@intbegincallid=@p5

			   FETCH NEXT   FROM H_SETTLE INTO @mainid,@remindpeople,@content    --游标移到下一个记录
			   END
		 CLOSE H_SETTLE        --关闭游标
		 DEALLOCATE H_SETTLE        --释放游标

END

简石寻呼


USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[pt_QDJSMessage_l]    Script Date: 2022/11/14 10:13:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pt_QDJSMessage_l] 
 @mainid  varchar(100), 
 @remindpeople  varchar(100)  ,--发送者
 @content  varchar(300) ---寻呼内容
AS

BEGIN
declare @djsn varchar(500),@djname varchar(100)
set @djsn='qdjshtsp2019_703c27d9-7015-47b1-bbc8-a55dde3726d2'
set @djname='启迪简石合同审批2019'
		DECLARE H_SETTLE SCROLL CURSOR FOR select mainid,remindpeople,content from qidijianshihetongshenpizd where mainid in (select distinct b.AppO_Values from jhoa_approve_instance a inner join jhoa_approve b on a.Instance_ID=b.Instance_ID where Template_ID='2d82a6bb63a54a10b56c7c0a84ef03c3' and Status='completed') and dateDiff(dd,data,getdate())>=0 and dateDiff(dd,data,getdate())<3


					declare @strXMLList varchar(max)   ---寻呼接受人列表  参考格式：<?xml version="1.0" encoding="gb2312"?><root><record><CallToUser>0050</CallToUser></record></root>
					declare @strSlaveXml varchar(max)  ---弹出寻呼窗口的人  参考格式：<?xml version="1.0" encoding="gb2312"?><root><record><UserID><![CDATA[0050]]></UserID></record></root>
					declare @strSender varchar(max)    ---寻呼发送者
					declare @strCallContent varchar(max)  ---寻呼内容
					declare	@dbName varchar(100)
					declare @p4 int
					declare @p5 int
					set @p4=NULL
					
					set @strSender=(select top 1 userid from users where username=@remindpeople)
					declare @app_id int,@AppT_ID varchar(256),@url varchar(8000)
					select top 1 @app_id=app_id,@AppT_ID=appt_id from jhoa_approve where appo_values= @mainid order by app_id desc
					set @url='../Jhsoft.Web.module/fceform/common/djframe.htm?djsn='+@djsn+'&djtype=TT&DjName='+@djname
					set @url+= '&opentype=2&ModuleID='+@djsn+'&IsNew=1&OperatorTypeWFHsh=FS&httpAppID='+cast(@app_id as varchar)+'&httpOID=0&paravalue='+@mainid+'&_FlowTemplateID='+@AppT_ID
					set @strCallContent='<a href = '''+@url+'''>'+@content+'</a>'
					set @dbName=DB_NAME()
					
					exec pt_CreateID @dbName=@dbName,@tbName='Call',@intOutput=1,@strID=@p4 output
					select @p4
					set @strXMLList='<?xml version="1.0" encoding="gb2312"?><root><record><CallToUser>'+@strSender+'</CallToUser></record></root>'
					set  @strSlaveXml='<?xml version="1.0" encoding="gb2312"?><root><record><UserID><![CDATA['+  @strSender  +']]></UserID></record></root>'
					exec   pt_CallInsert  @strXMLList=@strXMLList,@intCallID=@p4,@intFatherID=@p4,@strCallContent=@strCallContent,@strSender=@strSender,@strSenderDepart='',@strSmsSign='0',@strSmsTel='',@strFileID='',@strModuleID='',@strMessageID='',@strSlaveXml=''
					exec pt_CreateID @dbName=@dbName,@tbName='CallTemp',@intOutput=1,@strID=@p5 output
					select @p5
					exec pt_SendMessage @strXML=@strSlaveXml,@messageType='Call',@CallContent=@p4,@intbegincallid=@p5
end


合同查询更新
exec hetongchaxunls


USE [C6]
GO
/****** Object:  StoredProcedure [dbo].[hetongchaxunls]    Script Date: 2022/11/14 10:14:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[hetongchaxunls] as
 drop table  hetongchaxunbiao
 select * into hetongchaxunbiao--new 
 from  hetongchaxun  --old


 --更新合同归档
-- select * from hetongchaxunbiao
 update  hetongchaxunbiao set color='待归档'where AppO_Values in ( select MainID from tusenergy129zibiao  where shifouguidang='否')
Loading...