CREATE PROCEDURE dbo.proc_dept_report
@com_id char(4),@parentdept_id char(6),@adt_year int,@adt_month int,@plan_flag char(1),@lj_flag char(1) AS
declare @bq_st datetime,@bq_ed datetime,@bn_st datetime,@bj_st datetime /*当天起止时间及本月起时间*/
declare @tq_st datetime,@tq_ed datetime,@tn_st datetime,@tj_st datetime /*同期起止时间*/
declare @ai_year int,@ai_month int, @ai_month_st int
declare @ls_err1 nvarchar(4000),@ls_sql nvarchar(8000),@ls_sql1 nvarchar(4000),@ls_sql2 nvarchar(4000),@ls_sql3 nvarchar(4000)
declare @adt_month_st int,@adt_month_ed int,@li_rc int
/*计算当前月所在季度的*/
select @adt_month_st=(CASE when @adt_month between 1 and 3 THEN 1 when @adt_month between 4 and 6 THEN 4 when @adt_month between 7 and 9 THEN 7 when @adt_month between 10 and 12 THEN 10 end)
select @adt_month_ed=(CASE when @adt_month between 1 and 3 THEN 3 when @adt_month between 4 and 6 THEN 6 when @adt_month between 7 and 9 THEN 9 when @adt_month between 10 and 12 THEN 12 end)
SELECT @bn_st=min(date_begin),@bq_ed= max(date_end) FROM ctlm16 WHERE ( com_id = @com_id ) AND ( fiscal_year = @adt_year ) AND ( fiscal_period between 1 and @adt_month)
SELECT @bq_st=date_begin FROM ctlm16 WHERE ( com_id = @com_id ) AND ( fiscal_year = @adt_year ) AND ( fiscal_period = @adt_month)
SELECT @bj_st=date_begin FROM ctlm16 WHERE ( com_id = @com_id ) AND ( fiscal_year = @adt_year ) AND ( fiscal_period = @adt_month_st)
SELECT @tn_st=min(date_begin),@tq_ed= max(date_end) FROM ctlm16 WHERE ( com_id = @com_id ) AND ( fiscal_year = @adt_year -1 ) AND ( fiscal_period between 1 and @adt_month)
sELECT @tq_st=date_begin FROM ctlm16 WHERE ( com_id = @com_id ) AND ( fiscal_year = @adt_year -1) AND ( fiscal_period = @adt_month)
SELECT @tj_st=date_begin FROM ctlm16 WHERE ( com_id = @com_id ) AND ( fiscal_year = @adt_year -1) AND ( fiscal_period = @adt_month_st)
/*本期计划*/
if @plan_flag='Y' and @adt_month>1 /*如果滚动计划,取上月累计未完成*/
begin
set @ai_month=1
while (@ai_month < @adt_month)
begin
set @ls_sql1 =@ls_sql1+'sat50.qty'+convert(varchar(2),@ai_month)+'-'+'sat50.qty_done'+convert(varchar(2),@ai_month)+'+'
set @ls_sql2 =@ls_sql2 +'sat50.amt'+convert(varchar(2),@ai_month)+'-'+'sat50.amt_done'+convert(varchar(2),@ai_month)+'+'
set @ls_sql3 =@ls_sql3 +'sat50.return_amt'+convert(varchar(2),@ai_month)+'-'+'sat50.ret_amt_done'+convert(varchar(2),@ai_month)+'+'
set @ai_month=@ai_month+1
end
end
set @ls_sql1 =@ls_sql1 +'sat50.qty'+convert(varchar(2),@adt_month)+'+0 as bq_plan_qty,'
set @ls_sql2 =@ls_sql2 +'sat50.amt'+convert(varchar(2),@adt_month)+'+0 as bq_plan_amt,'
set @ls_sql3 =@ls_sql3 +'sat50.return_amt'+convert(varchar(2),@adt_month)+'+0 as bq_plan_return_amt,'
if @adt_month>6 and @lj_flag='Y'
set @ai_month_st = 7
else
set @ai_month_st = 1
set @ai_month = @ai_month_st
while (@ai_month <= @adt_month) /*本年累计*/
begin
if @ai_month <> @ai_month_st
begin
set @ls_sql1 =@ls_sql1 +'sat50.qty'+convert(varchar(2),@ai_month)+'+'
set @ls_sql2 =@ls_sql2 +'sat50.amt'+convert(varchar(2),@ai_month)+'+'
set @ls_sql3 =@ls_sql3 +'sat50.return_amt'+convert(varchar(2),@ai_month)+'+'
end
else
begin
set @ls_sql1 =@ls_sql1 +'sat50.qty'+convert(varchar(2),@ai_month)+'+0 as bn_plan_qty,'
set @ls_sql2 =@ls_sql2 +'sat50.amt'+convert(varchar(2),@ai_month)+'+0 as bn_plan_amt,'
set @ls_sql3 =@ls_sql3 +'sat50.return_amt'+convert(varchar(2),@ai_month)+'+0 as bn_plan_return_amt,'
end
set @ai_month = @ai_month+1
end
if @plan_flag='Y' and @adt_month_st>1 /*如果滚动计划,取上季度累计未完成,***紧接着要加上下边的本季计划*/
begin
set @ai_month=1
while (@ai_month < @adt_month_st )
begin
set @ls_sql1 =@ls_sql1 +'sat50.qty'+convert(varchar(2),@ai_month)+'-'+'sat50.qty_done'+convert(varchar(2),@ai_month)+'+'
set @ls_sql2 =@ls_sql2 +'sat50.amt'+convert(varchar(2),@ai_month)+'-'+'sat50.amt_done'+convert(varchar(2),@ai_month)+'+'
set @ls_sql3 =@ls_sql3 +'sat50.return_amt'+convert(varchar(2),@ai_month)+'-'+'sat50.ret_amt_done'+convert(varchar(2),@ai_month)+'+'
set @ai_month=@ai_month+1
end
end
set @ai_month = @adt_month_st
while (@ai_month <= @adt_month_ed) /*本季计划*/
begin
if @ai_month <> @adt_month_ed
begin
set @ls_sql1 =@ls_sql1 +'sat50.qty'+convert(varchar(2),@ai_month)+'+'
set @ls_sql2 =@ls_sql2 +'sat50.amt'+convert(varchar(2),@ai_month)+'+'
set @ls_sql3 =@ls_sql3 +'sat50.return_amt'+convert(varchar(2),@ai_month)+'+'
end
else
begin
set @ls_sql1 =@ls_sql1 +'sat50.qty'+convert(varchar(2),@ai_month)+'+0 as bj_plan_qty,'
set @ls_sql2 =@ls_sql2 +'sat50.amt'+convert(varchar(2),@ai_month)+'+0 as bj_plan_amt,'
set @ls_sql3 =@ls_sql3 +'sat50.return_amt'+convert(varchar(2),@ai_month)+'+0 as bj_plan_return_amt,'
end
set @ai_month = @ai_month +1
end
set @ai_month = 1
while (@ai_month <= 12) /*本年度*/
begin
if @ai_month <> 12
begin
set @ls_sql1 =@ls_sql1 +'sat50.qty'+convert(varchar(2),@ai_month)+'+'
set @ls_sql2 =@ls_sql2 +'sat50.amt'+convert(varchar(2),@ai_month)+'+'
set @ls_sql3 =@ls_sql3 +'sat50.return_amt'+convert(varchar(2),@ai_month)+'+'
end
else
begin
set @ls_sql1 =@ls_sql1 +'sat50.qty'+convert(varchar(2),@ai_month)+'+0 as nd_plan_qty,'
set @ls_sql2 =@ls_sql2 +'sat50.amt'+convert(varchar(2),@ai_month)+'+0 as nd_plan_amt,'
set @ls_sql3 =@ls_sql3 +'sat50.return_amt'+convert(varchar(2),@ai_month)+'+0 as nd_plan_return_amt '
end
set @ai_month = @ai_month+1
end
set @ls_sql1 ='select category_no=sat50.category_no,parentdept_id=ctlm08.parentdept_id,dept_id=sat50.dept_id,'+@ls_sql1+@ls_sql2+@ls_sql3+',bq_real_qty=0.00,bn_real_qty=0.00,bj_real_qty=0.00,bq_real_amt=0.00,bn_real_amt=0.00,bj_real_amt=0.00,bq_real_return_amt=0.00,bn_real_return_amt=0.00,bj_real_return_amt=0.00,tq_real_qty=0.00,tn_real_qty=0.00,tj_real_qty=0.00,tq_real_amt=0.00,tn_real_amt=0.00,tj_real_amt=0.00,tq_real_return_amt=0.00,tn_real_return_amt=0.00,tj_real_return_amt=0.00 from sat50,ctlm08 where sat50.com_id =@com_id and sat50.year=@adt_year and ctlm08.com_id=sat50.com_id and ctlm08.dept_id = sat50.dept_id and ctlm08.parentdept_id= @parentdept_id'
/*计算完成*/
/*产品类 计算销售完成 以开单为准 发出*/
set @ls_sql2 ='select category_no=sat49.category_no,parentdept_id=ctlm08.parentdept_id,dept_id=sam20_a.dept_id,bq_plan_qty=0.00,bn_plan_qty=0.00,bj_plan_qty=0.00,nd_plan_qty=0.00,bq_plan_amt=0.00,bn_plan_amt=0.00,bj_plan_amt=0.00,nd_plan_amt=0.00,bq_plan_return_amt=0.00,bn_plan_return_amt=0.00,bj_plan_return_amt=0.00,nd_plan_return_amt=0.00,bq_real_qty=round(sum(CASE when sat04.date_of_fill between @bq_st and @bq_ed THEN sat05.qty_d_order*sat49.qty_rate else 0.00 end),5),bn_real_qty=round(sum(CASE when sat04.date_of_fill between @bn_st and @bq_ed THEN sat05.qty_d_order*sat49.qty_rate else 0.00 end),5),bj_real_qty=round(sum(CASE when sat04.date_of_fill between @bj_st and @bq_ed THEN sat05.qty_d_order*sat49.qty_rate else 0.00 end),5),bq_real_amt=round(sum(CASE when sat04.date_of_fill between @bq_st and @bq_ed THEN sat05.amt_d_order else 0.00 end),5),bn_real_amt=round(sum(CASE when sat04.date_of_fill between @bn_st and @bq_ed THEN sat05.amt_d_order else 0.00 end),5),bj_real_amt=round(sum(CASE when sat04.date_of_fill between @bj_st and @bq_ed THEN sat05.amt_d_order else 0.00 end),5),bq_real_return_amt=0.00,bn_real_return_amt=0.00,bj_real_return_amt=0.00,tq_real_qty=round(sum(CASE when sat04.date_of_fill between @tq_st and @tq_ed THEN sat05.qty_d_order*sat49.qty_rate else 0.00 end),5),tn_real_qty=round(sum(CASE when sat04.date_of_fill between @tn_st and @tq_ed THEN sat05.qty_d_order*sat49.qty_rate else 0.00 end),5),tj_real_qty=round(sum(CASE when sat04.date_of_fill between @tj_st and @tq_ed THEN sat05.qty_d_order*sat49.qty_rate else 0.00 end),5),tq_real_amt=round(sum(CASE when sat04.date_of_fill between @tq_st and @tq_ed THEN sat05.amt_d_order else 0.00 end),5),tn_real_amt=round(sum(CASE when sat04.date_of_fill between @tn_st and @tq_ed THEN sat05.amt_d_order else 0.00 end),5),tj_real_amt=round(sum(CASE when sat04.date_of_fill between @tj_st and @tq_ed THEN sat05.amt_d_order else 0.00 end),5),tq_real_return_amt=0.00,tn_real_return_amt=0.00,tj_real_return_amt=0.00 from sat04,sat05,sat49,sam20_a,sam21 ,ctlm08 where sat04.com_id=sat05.com_id and sat04.p_g_order_no=sat05.p_g_order_no and sat49.com_id=sat05.com_id and sat49.item_no=sat05.item_no and sam20_a.com_id = sam21.com_id and sam20_a.s_man_code = sam21.s_man_code and sam20_a.com_id =sat04.com_id AND sam20_a.c_code=sat04.c_code and sam21.item_no=sat05.item_no and sam20_a.com_id = ctlm08.com_id and sam20_a.dept_id=ctlm08.dept_id and sat04.com_id=@com_id AND (sat04.date_of_fill between @bn_st and @bq_ed or sat04.date_of_fill between @tn_st and @tq_ed ) and ctlm08.parentdept_id = @parentdept_id group by sat49.category_no,ctlm08.parentdept_id,sam20_a.dept_id'
/*产品类 计算销售回款*/
set @ls_sql3 ='select category_no=sat49.category_no,parentdept_id=ctlm08.parentdept_id,dept_id=sam20_a.dept_id,bq_plan_qty=0.00,bn_plan_qty=0.00,bj_plan_qty=0.00,nd_plan_qty=0.00,bq_plan_amt=0.00,bn_plan_amt=0.00,bj_plan_amt=0.00,nd_plan_amt=0.00,bq_plan_return_amt=0.00,bn_plan_return_amt=0.00,bj_plan_return_amt=0.00,nd_plan_return_amt=0.00,bq_real_qty=0.00,bn_real_qty=0.00,bj_real_qty=0.00,bq_real_amt=0.00,bn_real_amt=0.00,bj_real_amt=0.00,bq_real_return_amt=round(sum(CASE when sat_assign.income_date between @bq_st and @bq_ed THEN sat_assign.assign_amt else 0.00 end),5),bn_real_return_amt=round(sum(CASE when sat_assign.income_date between @bn_st and @bq_ed THEN sat_assign.assign_amt else 0.00 end),5),bj_real_return_amt=round(sum(CASE when sat_assign.income_date between @bj_st and @bq_ed THEN sat_assign.assign_amt else 0.00 end),5),tq_real_qty=0.00,tn_real_qty=0.00,tj_real_qty=0.00,tq_real_amt=0.00,tn_real_amt=0.00,tj_real_amt=0.00,tq_real_return_amt=round(sum(CASE when sat_assign.income_date between @tq_st and @tq_ed THEN sat_assign.assign_amt else 0.00 end),5),tn_real_return_amt=round(sum(CASE when sat_assign.income_date between @tn_st and @tq_ed THEN sat_assign.assign_amt else 0.00 end),5),tj_real_return_amt=round(sum(CASE when sat_assign.income_date between @tj_st and @tq_ed THEN sat_assign.assign_amt else 0.00 end),5)from sat_assign,sat49, sam20_a,sam21,ctlm08 where sat49.com_id=sat_assign.com_id and sat49.item_no=sat_assign.item_no and sam20_a.com_id = sam21.com_id and sam20_a.s_man_code = sam21.s_man_code and sam20_a.com_id =sat_assign.com_id AND sam20_a.c_code=sat_assign.corr_id and sam21.item_no=sat_assign.item_no and sam20_a.com_id=ctlm08.com_id and sam20_a.dept_id=ctlm08.dept_id and sat_assign.com_id=@com_id and (sat_assign.income_date between @bn_st and @bq_ed or sat_assign.income_date between @tn_st and @tq_ed) and sat_assign.assign_type ="4" and ctlm08.parentdept_id =@parentdept_id group by sat49.category_no,ctlm08.parentdept_id,sam20_a.dept_id'
set @ls_sql ='select category_no=a.category_no,category_name=sat48.category_name,unit=sat48.unit,dept_id=a.dept_id,dept_name=ctlm08.dept_name,bq_plan_qty=sum(a.bq_plan_qty),bn_plan_qty=sum(a.bn_plan_qty),bj_plan_qty=sum(a.bj_plan_qty),nd_plan_qty=sum(a.nd_plan_qty),bq_plan_amt=sum(a.bq_plan_amt),bn_plan_amt=sum(a.bn_plan_amt),bj_plan_amt=sum(a.bj_plan_amt),nd_plan_amt=sum(a.nd_plan_amt),bq_plan_return_amt=sum(a.bq_plan_return_amt),bn_plan_return_amt=sum(a.bn_plan_return_amt),bj_plan_return_amt=sum(a.bj_plan_return_amt),nd_plan_return_amt=sum(a.nd_plan_return_amt),bq_real_qty=sum(a.bq_real_qty),bn_real_qty=sum(a.bn_real_qty),bj_real_qty=sum(a.bj_real_qty),bq_real_amt=sum(a.bq_real_amt),bn_real_amt=sum(a.bn_real_amt),bj_real_amt=sum(a.bj_real_amt),bq_real_return_amt=sum(a.bq_real_return_amt),bn_real_return_amt=sum(a.bn_real_return_amt),bj_real_return_amt=sum(a.bj_real_return_amt),tq_real_qty=sum(a.tq_real_qty),tn_real_qty=sum(a.tn_real_qty),tj_real_qty=sum(a.tj_real_qty),tq_real_amt=sum(a.tq_real_amt),tn_real_amt=sum(a.tn_real_amt),tj_real_amt=sum(a.tj_real_amt),tq_real_return_amt=sum(a.tq_real_return_amt),tn_real_return_amt=sum(a.tn_real_return_amt),tj_real_return_amt=sum(a.tj_real_return_amt) from ('+ @ls_sql1 + ' union '+@ls_sql2+' union '+@ls_sql3 +') as a,sat48,ctlm08 where a.category_no=sat48.category_no and a.dept_id=ctlm08.dept_id and sat48.com_id=@com_id and ctlm08.com_id=@com_id group by a.category_no,sat48.category_name,sat48.unit,a.dept_id,ctlm08.dept_name '
exec(@ls_sql) |
发表于 2008-3-7 18:33:11
楼主