finfunction.sql

来自「一个以前收集的基于C/S架构的ERP客户端源代码」· SQL 代码 · 共 443 行 · 第 1/2 页

Drop procedure sdRpt_FinFun
----财务取数
create procedure sdRpt_FinData
  @FMonth varchar(6),
  @Glkm varchar(18),
  @Period varchar(18),
  @Type varchar(18),
  @PerInc int
AS
declare 
  @AmtValue decimal(20, 2),
  @QtyValue decimal(20, 4) 
set @AmtValue = 0
set @QtyValue = 0
if @PerInc = -1
begin
  select top 1 @FMonth = FCMonth from sdFc where FCMonth < @FMonth order by FcMonth Desc
end
else if @PerInc = 1
begin
  select top 1 @FMonth = FCMonth from sdFc where FCMonth > @FMonth order by FcMonth
end

  if @Period = '年初'  ----年初
  begin
     if @Type = '金额' ----金额
     begin 
        select top 1 @AmtValue = isNull(GlbBoyAmt, 0) 
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '外币金额'
     begin           ----外币金额
        select top 1 @AmtValue = isNull(GlbBoyWbAmt, 0) 
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '数量'
     begin           ----数量
        select top 1 @QtyValue = isNull(GlbBoyQty, 0) 
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end   
  end 
  else if @Period = '期初'  ----期初
  begin
     if @Type = '金额' ----金额
     begin 
        select top 1 @AmtValue = isNull(GlbBopAmt, 0 ) 
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '外币金额'
     begin 
        select top 1 @AmtValue = isNull(GlbBopWbAmt, 0 ) 
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '数量'
     begin 
        select top 1 @QtyValue = isNull(GlbBopQty, 0 ) 
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
  end
  else if @Period = '借方'  ----本期借方金额
  begin
     if @Type = '金额'
     begin
        select top 1 @AmtValue = isNull(GlbDopAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '外币金额'
     begin
        select top 1 @AmtValue = isNull(GlbDopWbAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '数量'
     begin
        select top 1 @QtyValue = isNull(GlbDopQty, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
  end
  else if @Period = '贷方'
  begin
     else if @Type = '金额'  ----贷方金额
     begin
        select top 1 @AmtValue = isNull(GlbCopAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end 
     else if @Type = '外币金额'  ----贷方外币金额
     begin
        select top 1 @AmtValue = isNull(GlbCopWbAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end 
     else if @Type = '数量'  ----贷方数量
     begin
        select top 1 @QtyValue = isNull(GlbCopQty, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end 
  end 
  else if @Period = '本年'  ----本年借方金额
  begin
     if @Type = '借方金额'
     begin
        select top 1 @AmtValue = isNull(GlbDoyAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '借方外币金额'
     begin
        select top 1 @AmtValue = isNull(GlbDoyWbAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth 
     end
     else if @Type = '借方数量'
     begin
        select top 1 @QtyValue = isNull(GlbDoyQty, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth 
     end
     else if @Type = '贷方金额'  ----本年贷方金额
     begin
        select top 1 @AmtValue = isNull(GlbCoyAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '贷方外币金额'  ----
     begin
        select top 1 @AmtValue = isNull(GlbCoyWbAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '贷方数量'  ---
     begin
        select top 1 @QtyValue = isNull(GlbCoyQty, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
  end 
  else if @Period = '累计'  ----累计金额
  begin
     if @Type = '金额'
     begin
        select top 1 @AmtValue = isNull(GlbAoyAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth 
     end
     else if @Type = '外币金额'
     begin
        select top 1 @AmtValue = isNull(GlbAoyWbAmt, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
     else if @Type = '数量'
     begin
        select top 1 @QtyValue = isNull(GlbAoyQty, 0)
        from sdglb
        where GlbGlkm = @Glkm and GlbFMonth = @FMonth
     end
  end
if @AmtValue <> 0
   select Value = @AmtValue
else if @QtyValue <>0
   select Value = @QtyValue
else
   select Value = 0
-------------------------------------------------------------------------------
Drop procedure sdRpt_FinSum
create procedure sdRpt_FinSum
  @FMonth varchar(6),
  @Glkm varchar(18),
  @Glkm2 varchar(18),
  @Period varchar(18),
  @Type varchar(18),
  @PerInc int
AS
declare 
  @AmtValue decimal(20, 2),
  @QtyValue decimal(20, 4) 
set @Glkm2 = @Glkm2+'99999999999999999'
set @AmtValue = 0
set @QtyValue = 0
if @PerInc = -1
begin
  select top 1 @FMonth = FCMonth from sdFc where FCMonth < @FMonth order by FcMonth Desc
end
else if @PerInc = 1
begin
  select top 1 @FMonth = FCMonth from sdFc where FCMonth > @FMonth order by FcMonth
end

  if @Period = '年初'  ----年初
  begin
     if @Type = '金额' ----金额
     begin 
        select @AmtValue = sum(GlbBoyAmt) 
        from sdglb
        where GlbFMonth = @FMonth
              and glbGlkm in (select GlkmCode from sdGlkm 
                              where GlkmCode >= @Glkm and GlkmCode <= @Glkm2
                                    and glkmMx = 1)     
     end
     else if @Type = '外币金额'
     begin           ----外币金额
        select @AmtValue = sum(GlbBoyWbAmt) 
        from sdglb
        where GlbFMonth = @FMonth
              and glbGlkm in (select GlkmCode from sdGlkm 
                              where GlkmCode >= @Glkm and GlkmCode <= @Glkm2
                                    and glkmMx = 1)     
     end
     else if @Type = '数量'
     begin           ----数量
        select @QtyValue = sum(GlbBoyQty) 
        from sdglb