JSC销售Prc。_amountincludetax-CSDN博客

本文链接：https://blog.csdn.net/tzjly/article/details/7439747

USE [frcs]
GO
/****** 对象: StoredProcedure [dbo].[prc_FuRuiBIAmountData]    脚本日期: 04/09/2012 09:25:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[prc_FuRuiBIAmountData]
@year int,   ---年份
@type varchar(10), ---类型
@value int,   ---值
@deptID int   ---部门ID
AS
declare @months varchar(40)
declare @sql varchar(8000)
declare @sql2 varchar(8000)
declare @FuzzyField varchar(100)

begin
set @months = '1'
if @type= 'm'
begin
  set @months = STR( @value )
end
if @type = 's'
begin
  set @months = str(@value*3-2)+','+str(@value*3-1)+','+str(@value*3)
end
if @type = 'y'
begin
  set @months = '1,2,3,4,5,6,7,8,9,10,11,12'
end
----------------------部门参数的ID的查询-------
set @FuzzyField=''
  if @deptID='1406'
  begin
   set @FuzzyField='zg'
  end
  if @deptID='1447'
  begin
   set @FuzzyField='fm'
  end
  if @deptID='1460'
  begin
   set @FuzzyField ='qp'
  end
  if @deptID='35910'
  begin
   set @FuzzyField='qz'
  end

-----------------------------------得到根据部门和日期汇总的销售信息---------------------------------------------
set @sql ='select isnull(a.TotalHT,0) as TotalHT,
     isnull(b.TotalKP,0) as TotalKP,
     isnull(c.TotalHK,0) as TotalHK,
     isnull(b.TotalKP-c.TotalHK,0) as TotalYS,
     isnull(d.TotalFH,0) as TotalFH from '

---- 得到总的合同金额
set @sql=@sql+'(select sum(FTotalamount) as TotalHT
from t_RPContract
where FDepartment ='+str(@deptID)+' and fchangemark=0
and year(fdate)='+str(@year)+' and month(fdate) in ('+@months+')) a,'

  ---- 得到总的开票金额
set @sql=@sql+'(select sum(b2.FStdAmountincludetax) as TotalKP
from IcSale a2
left join ICSaleEntry b2 on b2.Finterid=a2.Finterid
where FDeptID = '+str(@deptID)+' and year(a2.fdate)='+str(@year)+'
    and month(a2.fdate) in ('+@months+') ) b,'
print @sql
---- 得到总的回款金额
set @sql=@sql+'(select ((sum(case fpre when 0 then freceiveamount else 0 end))-
(sum(case fpre when -1 then freceiveamount else 0 end))) as TotalHK
from t_rp_newreceivebill as a3
where a3.frp=1 and a3.fdepartment='+str(@deptID)+' and year(a3.fdate)='+str(@year)+'
    and month(a3.fdate) in ('+@months+')) c,'
print @sql
---- 得到总的发货金额

set @sql=@sql+'(select sum(b1.fconsignamount) as TotalFH
    from icstockbill a1
    inner join icstockbillentry b1 on a1.finterid=b1.finterid
    inner join t_department c1 on a1.fdeptid=c1.fitemid
    left join t_emp d1 on d1.fitemid=a1.fempid
    where a1.ftrantype =21 and c1.fname like ''%'+@FuzzyField+'%''
    and d1.fname is not null and year(a1.fdate)='+str(@year)+'
    and month(a1.fdate) in ('+@months+')) d'

------------------------------------得到根据部门、人员和日期汇总的销售信息--------------------------------------------

set @sql2 ='select Name,
    isnull(sum(TotalHT),0) as TotalHT,
    isnull(sum(TotalKP),0) as TotalKP,
    isnull(sum(TotalHK),0) as TotalHK,
    isnull(sum(TotalYS),0) as TotalYS,
    isnull(sum(TotalFH),0) as TotalFH
    from (
      select Isnull(ISNULL(A1.FName,A2.FName),ISNULL(A3.Fname,A4.Fname)) as name,
      isnull(A1.totalamount,0) as TotalHT,
      isnull(A2.BillAmout,0) as TotalKP,
      isnull(A3.FAmount_CN,0) as TotalHK,
      (isnull(A2.BillAmout,0)-isnull(A3.FAmount_CN,0)) as TotalYS,
      isnull(A4.totalFH,0) as TotalFH
      from
      (
       select b4.fname,sum(a4.FTotalamount) as totalamount
       from t_RPContract a4
       left join t_emp b4 on a4.FEmployee=b4.fitemid
       where FDepartment ='+str(@deptID)+' and fchangemark=0
       and year(fdate)='+str(@year)+' and month(fdate) in ('+@months+')
       group by b4.fname
      ) A1
      full join
      (
       select c5.fname,sum(b5.FStdAmountincludetax) as BillAmout
       from IcSale a5
       left join ICSaleEntry b5 on b5.Finterid=a5.Finterid
       left join t_emp c5 on a5.fempID=c5.fitemid
       where FDeptID = '+str(@deptID)+' and year(a5.fdate)='+str(@year)+'
       and month(a5.fdate) in ('+@months+')
       group by c5.fname
      ) A2 on A1.FName=A2.FName
      full join
      (
       select b6.fname,((sum(case fpre when 0 then freceiveamount else 0 end))-
       (sum(case fpre when -1 then freceiveamount else 0 end))) as FAmount_CN
       from t_rp_newreceivebill as a6
       left join t_emp b6 on a6.Femployee=b6.fitemid
       where a6.frp=1 and fdepartment='+str(@deptID)+' and year(a6.fdate)='+str(@year)+'
       and month(a6.fdate) in ('+@months+')
       group by b6.fname
      ) A3 on A2.FName=A3.FName
      full join
      (
       select d7.fname,sum(b7.fconsignamount) as totalFH
       from icstockbill a7
       inner join icstockbillentry b7 on a7.finterid=b7.finterid
       inner join t_department c7 on a7.fdeptid=c7.fitemid
       left join t_emp d7 on d7.fitemid=a7.fempid
       where a7.ftrantype =21 and c7.fname like ''%'+@FuzzyField+'%''
       and d7.fname is not null
       and year(a7.fdate)='+str(@year)+' and month(a7.fdate) in ('+@months+')
       group by d7.fname
      )A4 on A4.Fname=A3.Fname
  )A5 group by Name'
exec (@sql)
exec (@sql2)
end