use aqgl
alter table wgsr add je float
alter table zbkh add je float
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- SELECT * FROM WGSR update wgsr set je=10
ALTER procedure P_AJCWGKHTJ -- EXEC P_AJCWGKHTJ '2001-01-01','2009-01-01'
@daks varchar(30),
@dajs varchar(30)
as
DECLARE @LX VARCHAR(30)
declare @str varchar(4000)
set @str='SELECT xh 人员编号, xm 姓名, djrKQH 卡号, zw 职务, lb 人员类别 '
DECLARE CR CURSOR FOR
SELECT LX from AJCWGLX ORDER BY ID
OPEN CR
FETCH NEXT FROM CR INTO @LX
while (@@fetch_status=0)
BEGIN
SET @STR=@STR + ',(SELECT COUNT(WGBH) FROM WGSR
WHERE WGLX=''' + @LX +''' AND WGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') AS '''+@LX +''''
--------------------------------------------------------------
FETCH NEXT FROM CR INTO @LX
END
CLOSE CR
DEALLOCATE CR
SET @STR=@STR+', (SELECT SUM(WGKF) FROM WGSR
WHERE WGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') 扣分合计
, (SELECT SUM(je) FROM WGSR
WHERE WGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') 罚款合计
FROM ( SELECT xh,kqh as djrkqh,bm,xm,zw,lb,zy from daxx where bm='''+'安监处'+''') as daxx
order by xh '
--print @str --select * from dksr
exec (@str)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- SELECT * FROM WGSR
ALTER procedure P_AJCWGKHzb -- EXEC P_AJCWGKHTJ '2001-01-01','2009-01-01'
@daks varchar(30),
@dajs varchar(30)
as
DECLARE @LX VARCHAR(30)
declare @str varchar(4000)
set @str='SELECT xh 人员编号, xm 姓名, djrKQH 卡号, zw 职务, lb 人员类别 '
DECLARE CR CURSOR FOR
SELECT LX from AJCWGLX ORDER BY ID
OPEN CR
FETCH NEXT FROM CR INTO @LX
while (@@fetch_status=0)
BEGIN
SET @STR=@STR + ',(SELECT COUNT(WGBH) FROM WGSR
WHERE WGLX=''' + @LX +''' AND zWGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') AS '''+@LX +''''
--------------------------------------------------------------
FETCH NEXT FROM CR INTO @LX
END
CLOSE CR
DEALLOCATE CR
SET @STR=@STR+', (SELECT SUM(WGKF) FROM WGSR
WHERE zWGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') 扣分合计
, (SELECT SUM(je) FROM WGSR
WHERE zWGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') 罚款合计
FROM ( SELECT xh,kqh as djrkqh,bm,xm,zw,lb,zy from daxx where bm='''+'安监处'+''') as daxx
order by xh '
--print @str --select * from dksr
exec (@str)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- SELECT * FROM zbkh update zbkh set je=10
ALTER procedure P_AJCqtzbKHtj -- EXEC P_AJCqtzbKHtj '2001-01-01','2009-01-01'
@daks varchar(30),
@dajs varchar(30)
as
DECLARE @LX VARCHAR(30)
declare @str varchar(4000)
set @str='SELECT xh 受奖人编号, xm 受奖人姓名, djrKQH 卡号, zw 职务, lb 人员类别 '
DECLARE CR CURSOR FOR
SELECT LX from AJCqtzb ORDER BY ID
OPEN CR
FETCH NEXT FROM CR INTO @LX
while (@@fetch_status=0)
BEGIN
SET @STR=@STR + ',(SELECT COUNT(WGBH) FROM zbkh
WHERE WGLX=''' + @LX +''' AND WGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') AS '''+@LX +''''
--------------------------------------------------------------
FETCH NEXT FROM CR INTO @LX
END
CLOSE CR
DEALLOCATE CR
SET @STR=@STR+', (SELECT SUM(WGKF) FROM zbkh
WHERE WGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') 分值合计
, (SELECT SUM(je) FROM zbkh
WHERE WGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') 奖励合计
FROM ( SELECT xh,kqh as djrkqh,bm,xm,zw,lb,zy from daxx where bm='''+'安监处'+''') as daxx
order by xh '
--print @str --select * from dksr
exec (@str)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure P_AjCgrzbhz -- select * from swsr where jf<>0 and djrkqh='1497' group by wglx
@daks varchar(30),
@dajs varchar(30)
as
declare @str1 varchar(4000)
set @str1='SELECT xh 人员编号,bm 部门名称,
xm 姓名,
djrKQH 卡号,
zw 职务,
lb 人员类别 ,
(SELECT COUNT(SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and wglx in ('''+'三违'+''','''+'违章作业'+''','''+'违章指挥'+''','''+'违犯纪律'+''') and
wgcd='+'''一般'''+' and DJRKQH=DAXX.DJRKQH ) 一般,
(SELECT COUNT(SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and wglx in ('''+'三违'+''','''+'违章作业'+''','''+'违章指挥'+''','''+'违犯纪律'+''')
and wgcd='+'''严重'''+' and DJRKQH=DAXX.DJRKQH ) 严重,
(SELECT COUNT(SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and wglx in ('''+'三违'+''','''+'违章作业'+''','''+'违章指挥'+''','''+'违犯纪律'+''') and
wgcd='+'''典型严违'''+' and DJRKQH=DAXX.DJRKQH ) 典型,
(SELECT COUNT(SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and (wglx='+'''军事化管理'''+' or wglx='+'''军管'''+' ) and DJRKQH=DAXX.DJRKQH) 军管 ,
(SELECT COUNT(SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and wglx='+'''A卡'''+' and DJRKQH=DAXX.DJRKQH) A卡,
(SELECT COUNT(SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and wglx='+'''五级隐患'''+' and DJRKQH=DAXX.DJRKQH) 五级隐患,
(SELECT COUNT(SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and wglx='+'''隐患联责'''+' and DJRKQH=DAXX.DJRKQH ) 隐患联责,
(SELECT COUNT(distinct SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and wglx='+'''停止作业'''+' and DJRKQH=DAXX.DJRKQH ) 停止作业,
(SELECT COUNT(SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and wglx='+'''信息处罚'''+' and DJRKQH=DAXX.DJRKQH ) 信息处罚,
(SELECT COUNT(SWBH) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and wglx='+'''其它'''+' and DJRKQH=DAXX.DJRKQH ) 其它,
(SELECT COUNT(dkbh) FROM dksr WHERE rq>=''' +@daks +''' and rq<='''+@dajs +
''' and dksr.KQH=DAXX.DJRKQH ) D卡,
(SELECT sum(fkje) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and DJRKQH=DAXX.DJRKQH and fkje is not null) 罚款金额,
(SELECT sum(jf) FROM SWSR WHERE wzrq>=''' +@daks +''' and wzrq<='''+@dajs +
''' and DJRKQH=DAXX.DJRKQH and jf is not null) 指标加分
FROM ( SELECT xh,kqh as djrkqh,bm,xm,zw,lb,zy from daxx where bm='''+'安监处'+''') as daxx
order by xh '
exec (@str1)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- SELECT * FROM WGSR
ALTER procedure P_AJCWGKHzb -- EXEC P_AJCWGKHTJ '2001-01-01','2009-01-01'
@daks varchar(30),
@dajs varchar(30)
as
DECLARE @LX VARCHAR(30)
declare @str varchar(4000)
set @str='SELECT xh 人员编号, xm 姓名, djrKQH 卡号, zw 职务, lb 人员类别 '
DECLARE CR CURSOR FOR
SELECT LX from AJCWGLX ORDER BY ID
OPEN CR
FETCH NEXT FROM CR INTO @LX
while (@@fetch_status=0)
BEGIN
SET @STR=@STR + ',(SELECT COUNT(WGBH) FROM WGSR
WHERE WGLX=''' + @LX +''' AND zWGRKH=DAXX.DJRKQH
AND wGrq>=''' +@daks +''' and wGrq<='''+@dajs +''') AS '''+@LX +''''
--------------------------------------------------------------
FETCH NEXT FROM CR INTO @LX
END
CLOSE CR
DEALLOCATE CR
SET @STR=@STR+'
FROM ( SELECT xh,kqh as djrkqh,bm,xm,zw,lb,zy from daxx where bm='''+'安监处'+''') as daxx
order by xh '
--print @str --select * from dksr
exec (@str)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
煤矿安全事务管理系统源程序
需积分: 10 22 浏览量
2008-02-23
12:03:38
上传
评论
收藏 792KB RAR 举报
安全管理1113.rar （92个子文件） 
数据库更新20071113.sql 8KB
源程序20071113 frm_DBAJYHZ.frm 35KB MDIMain.frx 20KB frm_SWCX.frm 46KB FRM_SETMM.log 48B frm_AJYZBHZ6.frm 34KB
dkcx.et 6KB frm_zytj.frm 30KB Frm_档案信息.frm 31KB FrmBMXX.frm 13KB Frm_YHPC.frm 42KB SWCx3.et 7KB frm_AJYZBHZ8.frm 34KB Frm_zbcx.frm 12KB Frm_wgcx.frm 12KB wgzy_zy.et 6KB
aaa.xls 208B frm_lbgrhz.frm 19KB Frm_dr.log 152B Module1.bas 4KB Frm_档案信息.log 999B Frm_dr1.frm 22KB Frm_SWSR.frm 73KB frm_登录.frx 4B FrmBMsz.frm 13KB MDIMain.frm 26KB Form1.frm 477B FXJFFCX.et 6KB frm_wzl.frm 31KB Frm_dr.frm 11KB C_ini.cls 4KB Frm_wgsr.frm 46KB Frm_DkSR.frm 34KB frm_ZBHZ.frm 19KB Frm_LZLB.frm 31KB Frmzwsz.frm 11KB lbgrhz.et 6KB GRZBHZ.et 7KB fkd.et 7KB 安全管理.PDM 6KB frm_lbdwhz.frm 18KB FrmWGCD.frm 11KB Frmgwsz.log 92B fkds.et 7KB Frmgwsz.frm 10KB WZL.et 7KB frm_人员分布.log 209B
安全管理20071105.exe 2.09MB report.frm 6KB FrmDDXX.frm 10KB sysini.cng 34B MDIMain.log 76B FRM_SETMM.frm 8KB frm_AJYZBHZ3.frm 34KB FrmBMsz.frx 157B MSSCCPRJ.SCC 340B Frm_zbkh.frm 44KB Printtest.vbp 615B Formpetest.frm 8KB frm_GRWZTJ.frm 35KB wgzy_dw.et 6KB frm_FXJFKCX.frm 21KB FrmBMXX.log 52B lbDWhz.et 6KB frm_登录.frm 7KB frm_AJYZBHZ2.frm 35KB Frm_dy.frm 57KB SWCx2.et 6KB FrmWGLX.frm 12KB zbhz.et 6KB DBAJYHZ.et 7KB Printtest.vbw 49B frm_AJYZBHZ4.frm 34KB frm用户管理.log 53B 安全管理.vbw 2KB frm_DKCX.frm 22KB ajyzbhz.et 6KB Frmzysz.frm 10KB frm_登录.log 47B frm_AJYZBHZ.frm 35KB frm_AJYZBHZ5.frm 32KB 安全管理20071106.exe 2.09MB frm_人员分布.frm 29KB report.log 47B frm_AJYZBHZ7.frm 34KB GRWZHZ.et 7KB frm用户管理.frm 16KB frm_AJYZBHZ1.frm 35KB 安全管理.vbp 2KB Frm_zbkhcx.frm 12KB 安全管理20071113.exe 2.09MB SWCx1.et 6KB资源评论
