您的位置:首页技术开发数据库文摘 → SQL水晶报表存储过程

SQL水晶报表存储过程

时间:2010/1/28 12:02:00来源:本站整理作者:我要评论(0)

create PROC SaveColligate
@JLRQ datetime,
@NRJD varchar(50),
@GZQK varchar(500),
@ZBGB varchar(50),
@ZBRS varchar(50),
@ZHBY varchar(50),
@ZHLJ varchar(50),
@GZBY varchar(50),
@GZLJ varchar(50),
@TQSW varchar(50),
@TQXW varchar(50),
@PHCB varchar(50),

@XMMC varchar(50),
@GZNR varchar(500),

@PROJECTNAME varchar(100),
@ZYCB varchar(500),
@ZYZY varchar(50),
@TRRQ datetime,
@JHFYRQ datetime,
@JHGZTS int,
@ZCRS varchar(50),
@HXGZJH varchar(500),
@HY numeric,
@CY numeric,
@CS numeric

as
begin
   
--对综合报表中第4个表进行操作
    select @XMMC=a.XMMC,@GZNR=b.GZNR,@JLRQ=a.RQ
   
from HYGC_SHIP_DAILY as a , HYGC_SHIP_DAILY_WORK_CONTENT as b
   
where  CONVERT(VARCHAR(10),a.RQ,120) = CONVERT(VARCHAR(10),getdate(),120) and a.BH=b.BH
       
if exists(select * from T_COLLIGATE3 where XMMC=@XMMC)
           
begin
               
update T_COLLIGATE3 set XMMC=@XMMC,GZNR=@GZNR,JLRQ=@JLRQ
           
end
       
else
           
begin
               
insert into T_COLLIGATE3 (XMMC,GZNR,JLRQ) values(@XMMC,@GZNR,@JLRQ)
           
end
   
--对综合报表中第2个表进行操作
    declare @bh varchar(50)
   
declare @xmmc2 varchar(100)
   
declare @zzyc varchar(50)
   
declare @gznr2 varchar(500)
   
declare @zbgb2 varchar(50)
   
declare @rs2 varchar(50)
   
declare @cbmc varchar(50)
   
declare @swflfx varchar(50)
   
declare @xwflfx varchar(50)
   
declare @rq datetime
   
set @rq=getdate()
   
DECLARE Row_CurSor CURSOR FOR
       
select d.bh,d.ZZYC,d.GZNR,d.ZBGB,d.rs,e.cbmc,d.xmmc,d.swflfx,d.xwflfx from
        (
select a.bh,a.ZZYC,b.GZNR,a.ZBGB,c.rs,a.xmmc,a.RQ,a.SWFLFX,a.XWFLFX from HYGC_SHIP_DAILY as a ,  HYGC_SHIP_DAILY_WORK_CONTENT as b,
        HYGC_SHIP_DAILY_PERSONNEL
as c
       
where a.BH=b.BH and a.RQ='2010-1-6' and a.BH=c.BH ) as d,HYGC_SHIP_DAILY_FZCBDT as e
       
where  d.BH=e.BH
   
OPEN Row_Cursor
   
FETCH NEXT FROM Row_Cursor INTO @bh,@zzyc,@gznr2,@zbgb2,@rs2,@cbmc,@xmmc2,@swflfx,@xwflfx
   
WHILE @@Fetch_Status = 0
   
BEGIN
       
if exists (select * from T_COLLIGATE2 where JLRQ=@rq and NRJD=@zzyc)
           
begin
               
update T_COLLIGATE2 set PHCB=@cbmc
           
end
       
else
           
begin
               
insert into T_COLLIGATE2 (JLRQ,NRJD,GZQK,ZBGB,ZBRS,PHCB,BH,TQSW,TQXW) values(@rq,@xmmc2+':'+@zzyc,@gznr2,@zbgb2,@rs2,@cbmc,@bh,@swflfx,@xwflfx)
           
end
   
FETCH NEXT FROM Row_Cursor INTO @bh,@zzyc,@gznr2,@zbgb2,@rs2,@cbmc,@xmmc2,@swflfx,@xwflfx
   
end
   
CLOSE Row_Cursor
   
DEALLOCATE Row_Cursor
   
declare @year int
   
declare @year2 int
   
set @year=DATEPART(year, GETDATE())
   
set @year2=@year-1
           
declare @zhtsby int
           
declare @zhtslj int
           
declare @gztsby int
           
declare @gztslj int

           
select @zhtsby=cast(b.MTZYLJ AS decimal(5,2))+cast(b.THLJ AS decimal(5,2))+cast(b.ZCSGZYLJ AS decimal(5,2))
           
+cast(b.YZDJLJ AS decimal(5,2))+cast(b.TQDJLJ AS decimal(5,2))+cast(b.SBDJLJ AS decimal(5,2))
           
+cast(b.QTDJLJ AS decimal(5,2))+cast(b.DHLJ AS decimal(5,2))
           
from  T_COLLIGATE2 as a ,HYGC_SHIP_DAILY_CBHSTJ as b,HYGC_SHIP_DAILY as c
           
where a.BH=b.BH and a.JLRQ=c.RQ

           
select @zhtslj=sum(cast(b.MTZYLJ AS decimal(5,2)))+sum(cast(b.THLJ AS decimal(5,2)))+sum(cast(b.ZCSGZYLJ AS decimal(5,2)))
           
+sum(cast(b.YZDJLJ AS decimal(5,2)))+sum(cast(b.TQDJLJ AS decimal(5,2)))+sum(cast(b.SBDJLJ AS decimal(5,2)))
           
+sum(cast(b.QTDJLJ AS decimal(5,2)))+sum(cast(b.DHLJ AS decimal(5,2)))
           
from  T_COLLIGATE2 as a ,HYGC_SHIP_DAILY_CBHSTJ as b,HYGC_SHIP_DAILY as c
           
where a.BH=b.BH and a.JLRQ=c.RQ and c.RQ <str(@year)+'-'+'12'+'-'+'26' and C.RQ > str(@year2)+'-'+'12'+'-'+'25'
   
           
select @gztsby=cast(b.MTZYLJ AS decimal(5,2))+cast(b.THLJ AS decimal(5,2))+cast(b.ZCSGZYLJ AS decimal(5,2))
           
from  T_COLLIGATE2 as a ,HYGC_SHIP_DAILY_CBHSTJ as b,HYGC_SHIP_DAILY as c
           
where a.BH=b.BH and a.JLRQ=c.RQ

           
select @gztslj=sum(cast(b.MTZYLJ AS decimal(5,2)))+sum(cast(b.THLJ AS decimal(5,2)))+sum(cast(b.ZCSGZYLJ AS decimal(5,2)))
           
from  T_COLLIGATE2 as a ,HYGC_SHIP_DAILY_CBHSTJ as b,HYGC_SHIP_DAILY as c
           
where a.BH=b.BH and a.JLRQ=c.RQ and c.RQ <str(@year)+'-'+'12'+'-'+'26' and C.RQ > str(@year2)+'-'+'12'+'-'+'25'

           
update T_COLLIGATE2 set ZHBY=str(@zhtsby),ZHLJ=str(@zhtslj),GZBY=str(@gztsby),GZLJ=str(@gztslj) where JLRQ=@rq and NRJD=@zzyc
   
--对综合报表中第1个表进行操作
    declare @ID3 varchar(50)
   
declare @XMMC3 varchar(50)--项目名称
    declare    @CBMC3 varchar(50)--船舶名称
    declare    @UseDate3 DateTime--投入日期
    declare    @ComebackDate3 DateTime--计划复原日期
    declare    @WorkDays3 int--计划工作天数
    declare    @FollowWorkPlan3  varchar(500)--后续计划
    declare    @DRHY3 Decimal--耗油
    declare    @DRCY3 Decimal--存油
    declare    @DRCS3 Decimal--存水
    declare @GZNR3 varchar(500)--工作内容
    declare @GZDT3 varchar(500)--工作动态
    declare @rz3 datetime
   
declare @RS3 varchar(50)


   
DECLARE Row_CurSor2 CURSOR FOR
   
select DISTINCT f.XMMC,f.ZZYC,c.UseDate,c.ComebackDate,c.WorkDays,
    c.FollowWorkPlan,G.RS,d.DRHY,d.DRCY,d.DRCS,f.BH ,b.GZNR ,f.RQ
   
from
    (
select a.XMMC,a.ZZYC,a.BH,a.RQ from HYGC_SHIP_DAILY as a where
    
CONVERT(VARCHAR(10),a.RQ,120) = CONVERT(VARCHAR(10),'2009-12-29',120)) as f
   
left join T_Plan as c on f.XMMC=c.ProjectName and f.ZZYC=C.ShipName
   
left join HYGC_SHIP_DAILY_OIL_WATER as d on  f.BH=d.BH
   
left join HYGC_SHIP_DAILY_WORK_CONTENT as b on f.BH=b.BH
   
left join HYGC_SHIP_DAILY_PERSONNEL as g on f.bh=g.bh
   
OPEN Row_Cursor
   
FETCH NEXT FROM Row_Cursor2 INTO @XMMC3,@CBMC3,@UseDate3,@ComebackDate3,@WorkDays3,@FollowWorkPlan3,@RS3,@DRHY3,@DRCY3,@DRCS3,@ID3,@GZNR,@rz3
   
WHILE @@Fetch_Status = 0
   
BEGIN
       
if exists (select * from T_COLLIGATE1 where JLRQ=@rz3 and PROJECTNAME=@XMMC3 AND ZYCB=@CBMC3)
           
begin
               
update T_COLLIGATE1 set PHCB=@cbmc, GZNR=@GZNR3,TRRQ=@UseDate3,JHFYRQ=@ComebackDate3,
                JHGZTS
=@WorkDays3,ZCRS=@RS3,HXGZJH=@FollowWorkPlan3,HY=@DRHY3,CY=@DRCY3,CS=@DRCS3
           
end
       
else
           
begin
               
insert into T_COLLIGATE1 (JLRQ,XMMC,ZZYC,GZNR,TRRQ,JHFYRQ,JHGZTS3,ZCRS,HXGZJH,HY,CY,CS,BH)
               
values
                (
@rz3,@XMMC3,@CBMC3,@GZNR3,@UseDate3,@ComebackDate3,@WorkDays3,@RS3,@FollowWorkPlan3,@DRHY3,@DRCY3,@DRCS3,@ID3)
           
end


   
FETCH NEXT FROM Row_Cursor2 INTO @XMMC3,@CBMC3,@UseDate3,@ComebackDate3,@WorkDays3,@FollowWorkPlan3,@RS3,@DRHY3,@DRCY3,@DRCS3,@ID3,@GZNR3,@rz3
   
end
   
CLOSE Row_Cursor2
   
DEALLOCATE Row_Cursor2
   
   
DECLARE Row_CurSor3 CURSOR FOR
   
select DISTINCT f.XMMC,f.CBMC,f.GZDT,c.UseDate,c.ComebackDate,c.WorkDays,
    c.FollowWorkPlan,G.RS,d.DRHY,d.DRCY,d.DRCS,f.BH,f.GZDT,f.RQ
   
from
    (
select a.XMMC,i.CBMC,i.BH,a.RQ,i.GZDT from HYGC_SHIP_DAILY as a,HYGC_SHIP_DAILY_FZCBDT as i where
    
CONVERT(VARCHAR(10),a.RQ,120) = CONVERT(VARCHAR(10),'2009-12-29',120) and a.bh=i.bh) as f
   
left join T_Plan as c on f.XMMC=c.ProjectName and f.CBMC=C.ShipName
   
left join HYGC_SHIP_DAILY_OIL_WATER as d on  f.BH=d.BH
   
left join HYGC_SHIP_DAILY_PERSONNEL as g on f.bh=g.bh
   
OPEN Row_Cursor3
   
FETCH NEXT FROM Row_Cursor3 INTO @XMMC3,@CBMC3,@GZDT3,@UseDate3,@ComebackDate3,@WorkDays3,@FollowWorkPlan3,@RS3,@DRHY3,@DRCY3,@DRCS3,@ID3,@GZNR,@rz3
   
WHILE @@Fetch_Status = 0
   
BEGIN
       
if exists (select * from T_COLLIGATE1 where JLRQ=@rz3 and XMMC=@XMMC3 AND ZZYC=@CBMC3)
           
begin
               
update T_COLLIGATE1 set PHCB=@cbmc, GZNR=@GZNR3,TRRQ=@UseDate3,JHFYRQ=@ComebackDate3,
                JHGZTS
=@WorkDays3,ZCRS=@RS3,HXGZJH=@FollowWorkPlan3,HY=@DRHY3,CY=@DRCY3,CS=@DRCS3
           
end
       
else
           
begin
               
insert into T_COLLIGATE1 (JLRQ,XMMC,ZZYC,GZNR,TRRQ,JHFYRQ,JHGZTS3,ZCRS,HXGZJH,HY,CY,CS,BH)
               
values
                (
@rz3,@XMMC3,@CBMC3,@GZNR3,@UseDate3,@ComebackDate3,@WorkDays3,@RS3,@FollowWorkPlan3,@DRHY3,@DRCY3,@DRCS3,@ID3)
           
end
   
FETCH NEXT FROM Row_Cursor3 INTO @XMMC3,@CBMC3,@GZDT3,@UseDate3,@ComebackDate3,@WorkDays3,@FollowWorkPlan3,@RS3,@DRHY3,@DRCY3,@DRCS3,@ID3,@GZNR,@rz3
   
end
   
CLOSE Row_Cursor3
   
DEALLOCATE Row_Cursor3


   
DECLARE Row_CurSor4 CURSOR FOR
   
select DISTINCT f.BH ,b.GZNR ,f.RQ,f.XMMC,f.ZZYC
   
from
    (
select a.XMMC,a.ZZYC,a.BH,a.RQ from HYGC_SHIP_DAILY as a where
    
CONVERT(VARCHAR(10),a.RQ,120) = CONVERT(VARCHAR(10),'2009-12-29',120)) as f
   
left join HYGC_PROJECT_DAILY_GZNR as b on f.BH=b.BH
   
OPEN Row_Cursor4
   
FETCH NEXT FROM Row_Cursor4 INTO @ID3,@GZNR,@rz3,@XMMC3,@CBMC3
   
WHILE @@Fetch_Status = 0
   
BEGIN
       
if exists (select * from T_COLLIGATE1 where JLRQ=@rz3 and XMMC=@XMMC3 AND ZZYC=@CBMC3)
           
begin
               
update T_COLLIGATE1 set GZNR=@GZNR3
           
end
   
FETCH NEXT FROM Row_Cursor4 INTO @ID3,@GZNR,@rz3,@XMMC3,@CBMC3
   
end
   
CLOSE Row_Cursor4
   
DEALLOCATE Row_Cursor4
end
   

 

相关视频

    没有数据

相关阅读 SQL2005新建复制“找不到存储过程 错误:2812”的解决方法通用存储过程.分页存储过程如何在SQLServer 2005中列所有存储过程如何在Access中模拟SqlServer存储过程翻页SQLSERVER存储过程及调用详解mysql存储过程学习笔记用PHP调用Oracle存储过程asp存储过程使用大全

文章评论
发表评论

热门文章 没有查询到任何记录。

最新文章 没有查询到任何记录。 Oracle如何提高SQL执行效率Oracle统计信息与子分区为什么对数据库要加DISTINCTOracle SQL 常见问题方案(一)

人气排行 SQL水晶报表存储过程为什么对数据库要加DISTINCTOracle如何提高SQL执行效率MySQL从后门进企业市场常见数据库系统之比较 - Oracle数据库常见数据库系统之比较 - SYBASE 和 SQL SER用wu-ftpd架设FTP服务器常见数据库系统之比较 - DB2数据库