sybase 存储过程

 

CREATE PROCEDURE dbo.month_proc
AS
    BEGIN
    declare @gy_count                       decimal(38,2)
    declare @jrncb                          int
    declare @jrntl_id                       varchar(10)
    declare @jrntl_id_name                  varchar(100)
    declare @jrnbr                          varchar(10)
    declare @jrnbr_name                     varchar(100)
    declare @jrntr_cod                      varchar(10)
    declare @jrntr_cod_name                 varchar(100)
    declare @scvvhamt0                      decimal(38,2)
    declare @jrnac_dat                      varchar(20)
    declare @scvvhccy0                      varchar(10)
    declare @scvvhccy0_name                 varchar(100)
    declare @fh_work                        decimal(38,2)
    declare @zh_work                        decimal(38,2)
    declare @wd_work                        decimal(38,2)
    declare @organ_zh_id                    varchar(10)
    declare @organ_zh_name                  varchar(100)
    declare @jrntl_count                    int
    declare @wd_count                       decimal(38,2)
    declare @wd_money                       decimal(38,2)
    declare @wd_fh_work                     decimal(38,2)
    declare @wd_zh_work                     decimal(38,2)
    declare @wd_wd_work                     decimal(38,2)
    declare @percent_count                  varchar(10)
    declare @percent_money                  varchar(10)
    declare @percent_fh                     varchar(10)
    declare @percent_zh                     varchar(10)
    declare @percent_wd                     varchar(10)
    declare @wd                             varchar(10)
    declare @date                           varchar(20)
    declare @year                           varchar(10)
    declare @month                          varchar(10)
    declare @day                            varchar(10)
    declare @rn                             int
    declare @busi_count                     decimal(38,2)
    declare @wd_busi_count                  decimal(38,2)
    declare @percent_busi                   varchar(10)
    declare @busi                           varchar(10)
   
    --取得临时表gy_work_day的数据的日期
    select top 1 @date=substring(jrnac_dat,1,6) from gy_business_day
    select @year=substring(@date,1,4)
    select @month=substring(@date,5,6)
    select top 1 @day=substring(jrnac_dat,7,8) from gy_business_day
    --判断是否到月底并处理数据
    if @month='01' or @month='03' or @month='05' or @month='07' or @month='08' or @month='10' or @month='12'
    begin
    if @day='31'
    begin
    declare month_cur cursor for select gy_count, jrncb, jrntl_id, jrntl_id_name, jrnbr, jrnbr_name, jrntr_cod, jrntr_cod_name, scvvhamt0, jrnac_dat, scvvhccy0, scvvhccy0_name, fh_work, zh_work, wd_work, organ_zh_id, organ_zh_name, busi_count from gy_business_month where jrnac_dat=@year+@month order by jrnbr,jrntr_cod
    open month_cur
    --循环读取游标
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    while @@sqlstatus=0
    begin
    if @wd<>@jrnbr or @busi<>@jrntr_cod
    select @wd_count=wd_count, @wd_money=scvvhamt0, @wd_fh_work=fh_work, @wd_zh_work=zh_work, @wd_wd_work=wd_work, @wd_busi_count=busi_count from wd_business_month where jrnbr=@jrnbr and jrntr_cod=@jrntr_cod and jrnac_dat=@jrnac_dat
   
    if @wd_count=0
    select @percent_count='0.00%'
    else
    select @percent_count=convert(varchar,convert(decimal(19,2),round(@gy_count*1.0*100/@wd_count,2)))+'%'
   
    if @wd_money=0
    select @percent_money='0.00%'
    else
    select @percent_money=convert(varchar,convert(decimal(19,2),round(@scvvhamt0*100/@wd_money,2)))+'%'
   
    if @wd_fh_work=0
    select @percent_fh='0.00%'
    else
    select @percent_fh=convert(varchar,convert(decimal(19,2),round(@fh_work*100/@wd_fh_work,2)))+'%'
   
    if @wd_zh_work=0
    select @percent_zh='0.00%'
    else
    select @percent_zh=convert(varchar,convert(decimal(19,2),round(@zh_work*100/@wd_zh_work,2)))+'%'
   
    if @wd_wd_work=0
    select @percent_wd='0.00%'
    else
    select @percent_wd=convert(varchar,convert(decimal(19,2),round(@wd_work*100/@wd_wd_work,2)))+'%'
   
    if @wd_busi_count=0
    select @percent_busi='0.00%'
    else
    select @percent_busi=convert(varchar,convert(decimal(19,2),round(@busi_count*100/@wd_busi_count,2)))+'%'
   
    insert into gy_business_percent_month values(@percent_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @percent_money, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @percent_fh, @percent_zh, @percent_wd, @organ_zh_id, @organ_zh_name, @percent_busi)
    select @wd=@jrnbr
    select @busi=@jrntr_cod
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    end
    --关闭游标
    close month_cur
    --释放游标
    deallocate cursor month_cur
    end
    end
   
    if @month='04' or @month='06' or @month='09' or @month='11'
    begin
    if @day='30'
    begin
    declare month_cur cursor for select gy_count, jrncb, jrntl_id, jrntl_id_name, jrnbr, jrnbr_name, jrntr_cod, jrntr_cod_name, scvvhamt0, jrnac_dat, scvvhccy0, scvvhccy0_name, fh_work, zh_work, wd_work, organ_zh_id, organ_zh_name, busi_count from gy_business_month where jrnac_dat=@year+@month order by jrnbr,jrntr_cod
    open month_cur
    --循环读取游标
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    while @@sqlstatus=0
    begin
    if @wd<>@jrnbr or @busi<>@jrntr_cod
    select @wd_count=wd_count, @wd_money=scvvhamt0, @wd_fh_work=fh_work, @wd_zh_work=zh_work, @wd_wd_work=wd_work, @wd_busi_count=busi_count from wd_business_month where jrnbr=@jrnbr and jrntr_cod=@jrntr_cod and jrnac_dat=@jrnac_dat
   
    if @wd_count=0
    select @percent_count='0.00%'
    else
    select @percent_count=convert(varchar,convert(decimal(19,2),round(@gy_count*1.0*100/@wd_count,2)))+'%'
   
    if @wd_money=0
    select @percent_money='0.00%'
    else
    select @percent_money=convert(varchar,convert(decimal(19,2),round(@scvvhamt0*100/@wd_money,2)))+'%'
   
    if @wd_fh_work=0
    select @percent_fh='0.00%'
    else
    select @percent_fh=convert(varchar,convert(decimal(19,2),round(@fh_work*100/@wd_fh_work,2)))+'%'
   
    if @wd_zh_work=0
    select @percent_zh='0.00%'
    else
    select @percent_zh=convert(varchar,convert(decimal(19,2),round(@zh_work*100/@wd_zh_work,2)))+'%'
   
    if @wd_wd_work=0
    select @percent_wd='0.00%'
    else
    select @percent_wd=convert(varchar,convert(decimal(19,2),round(@wd_work*100/@wd_wd_work,2)))+'%'
   
    if @wd_busi_count=0
    select @percent_busi='0.00%'
    else
    select @percent_busi=convert(varchar,convert(decimal(19,2),round(@busi_count*100/@wd_busi_count,2)))+'%'
   
    insert into gy_business_percent_month values(@percent_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @percent_money, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @percent_fh, @percent_zh, @percent_wd, @organ_zh_id, @organ_zh_name, @percent_busi)
    select @wd=@jrnbr
    select @busi=@jrntr_cod
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    end
    --关闭游标
    close month_cur
    --释放游标
    deallocate cursor month_cur
    end
    end
   
    select @rn=convert(int,@year)
    if @rn/4=0 and @month='02'
    begin
    if @day='29'
    begin
    declare month_cur cursor for select gy_count, jrncb, jrntl_id, jrntl_id_name, jrnbr, jrnbr_name, jrntr_cod, jrntr_cod_name, scvvhamt0, jrnac_dat, scvvhccy0, scvvhccy0_name, fh_work, zh_work, wd_work, organ_zh_id, organ_zh_name, busi_count from gy_business_month where jrnac_dat=@year+@month order by jrnbr,jrntr_cod
    open month_cur
    --循环读取游标
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    while @@sqlstatus=0
    begin
    if @wd<>@jrnbr or @busi<>@jrntr_cod
    select @wd_count=wd_count, @wd_money=scvvhamt0, @wd_fh_work=fh_work, @wd_zh_work=zh_work, @wd_wd_work=wd_work, @wd_busi_count=busi_count from wd_business_month where jrnbr=@jrnbr and jrntr_cod=@jrntr_cod and jrnac_dat=@jrnac_dat
   
    if @wd_count=0
    select @percent_count='0.00%'
    else
    select @percent_count=convert(varchar,convert(decimal(19,2),round(@gy_count*1.0*100/@wd_count,2)))+'%'
   
    if @wd_money=0
    select @percent_money='0.00%'
    else
    select @percent_money=convert(varchar,convert(decimal(19,2),round(@scvvhamt0*100/@wd_money,2)))+'%'
   
    if @wd_fh_work=0
    select @percent_fh='0.00%'
    else
    select @percent_fh=convert(varchar,convert(decimal(19,2),round(@fh_work*100/@wd_fh_work,2)))+'%'
   
    if @wd_zh_work=0
    select @percent_zh='0.00%'
    else
    select @percent_zh=convert(varchar,convert(decimal(19,2),round(@zh_work*100/@wd_zh_work,2)))+'%'
   
    if @wd_wd_work=0
    select @percent_wd='0.00%'
    else
    select @percent_wd=convert(varchar,convert(decimal(19,2),round(@wd_work*100/@wd_wd_work,2)))+'%'
   
    if @wd_busi_count=0
    select @percent_busi='0.00%'
    else
    select @percent_busi=convert(varchar,convert(decimal(19,2),round(@busi_count*100/@wd_busi_count,2)))+'%'
   
    insert into gy_business_percent_month values(@percent_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @percent_money, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @percent_fh, @percent_zh, @percent_wd, @organ_zh_id, @organ_zh_name, @percent_busi)
    select @wd=@jrnbr
    select @busi=@jrntr_cod
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    end
    --关闭游标
    close month_cur
    --释放游标
    deallocate cursor month_cur
    end
    end
   
    if @rn/4<>0 and @month='02'
    begin
    if @day='28'
    begin
    declare month_cur cursor for select gy_count, jrncb, jrntl_id, jrntl_id_name, jrnbr, jrnbr_name, jrntr_cod, jrntr_cod_name, scvvhamt0, jrnac_dat, scvvhccy0, scvvhccy0_name, fh_work, zh_work, wd_work, organ_zh_id, organ_zh_name, busi_count from gy_business_month where jrnac_dat=@year+@month order by jrnbr,jrntr_cod
    open month_cur
    --循环读取游标
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    while @@sqlstatus=0
    begin
    if @wd<>@jrnbr or @busi<>@jrntr_cod
    select @wd_count=wd_count, @wd_money=scvvhamt0, @wd_fh_work=fh_work, @wd_zh_work=zh_work, @wd_wd_work=wd_work, @wd_busi_count=busi_count from wd_business_month where jrnbr=@jrnbr and jrntr_cod=@jrntr_cod and jrnac_dat=@jrnac_dat
   
    if @wd_count=0
    select @percent_count='0.00%'
    else
    select @percent_count=convert(varchar,convert(decimal(19,2),round(@gy_count*1.0*100/@wd_count,2)))+'%'
   
    if @wd_money=0
    select @percent_money='0.00%'
    else
    select @percent_money=convert(varchar,convert(decimal(19,2),round(@scvvhamt0*100/@wd_money,2)))+'%'
   
    if @wd_fh_work=0
    select @percent_fh='0.00%'
    else
    select @percent_fh=convert(varchar,convert(decimal(19,2),round(@fh_work*100/@wd_fh_work,2)))+'%'
   
    if @wd_zh_work=0
    select @percent_zh='0.00%'
    else
    select @percent_zh=convert(varchar,convert(decimal(19,2),round(@zh_work*100/@wd_zh_work,2)))+'%'
   
    if @wd_wd_work=0
    select @percent_wd='0.00%'
    else
    select @percent_wd=convert(varchar,convert(decimal(19,2),round(@wd_work*100/@wd_wd_work,2)))+'%'
   
    if @wd_busi_count=0
    select @percent_busi='0.00%'
    else
    select @percent_busi=convert(varchar,convert(decimal(19,2),round(@busi_count*100/@wd_busi_count,2)))+'%'
   
    insert into gy_business_percent_month values(@percent_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @percent_money, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @percent_fh, @percent_zh, @percent_wd, @organ_zh_id, @organ_zh_name, @percent_busi)
    select @wd=@jrnbr
    select @busi=@jrntr_cod
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    end
    --关闭游标
    close month_cur
    --释放游标
    deallocate cursor month_cur
    end
    end
    END