SQL水晶报表存储过程

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