导出进销存汇总表

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*

--exec up_进销存汇总_export '%','2011-06-25','2011-07-24','','',''
--执行完成后汇总数据存储在表temp_jxc_export中

*/
CREATE     PROCEDURE [dbo].[up_进销存汇总_export] 
@arg_isforeign varchar(10),    
@arg_dtbe  datetime ,
@arg_dten datetime,
@arg_wrhus varchar(40),
@arg_acctypeid  varchar(10),
@arg_partid varchar(10)

AS
begin
if isnull(@arg_wrhus,'')='' or rtrim(@arg_wrhus)='' set @arg_wrhus='%'
if isnull(@arg_acctypeid,'')='' or rtrim(@arg_acctypeid)='' set @arg_acctypeid='%'
if isnull(@arg_partid,'')='' or rtrim(@arg_partid)=''
 set @arg_partid='%'
else
 set @arg_partid=rtrim(@arg_partid) + '%'

create table #temp_wrkhus_ini
 (part_id varchar(40) null,
  price_plan decimal(18,2)  null,
  invent_amnt decimal(18,2) null,
  invent_cost decimal(18,2) null)
create table #temp_wrkhus_in
 (part_id varchar(40) null,
  price_plan decimal(18,2)  null,
  invent_amnt decimal(18,2) null,
  invent_cost decimal(18,2) null)
create table #temp_wrkhus_out
 (part_id varchar(40) null,
  price_plan decimal(18,2)  null,
  invent_amnt decimal(18,2) null,
  invent_cost decimal(18,2) null)
create table #temp_wrkhus_oh
 (part_id varchar(40) null,
  price_plan decimal(18,2)  null,
  invent_amnt decimal(18,2) null,
  invent_cost decimal(18,2) null)

create table #temp_wrkhus_bal
(
 part_id  varchar(40) null,
 price_plan decimal(18,2) null,
 invent_amnt decimal(18,2) null,
 invent_cost decimal(18,2) null
)
create table #temp_wrkhus_all
 (part_id varchar(40) null,
  price_plan decimal(18,2)  null,
  amnt_ini  decimal(18,2)  null,
  cost_ini  decimal(18,2)  null,
  amnt_in  decimal(18,2)  null,
  cost_in  decimal(18,2)  null,
  amnt_out  decimal(18,2)  null,
  cost_out  decimal(18,2)  null,
  amnt_oh  decimal(18,2)  null,
  cost_oh  decimal(18,2)  null,)
------------期初余额--------------------------
--up_im_current_wrhus_list
insert #temp_wrkhus_ini
select a.part_id,a.inventbal_price_plan,sum(a.inventbal_amnt) as amnt_ini ,sum(a.inventbal_cost) as cost_ini
from f_invent_bal a join
 (select d.wrhus_id,d.part_id,d.inventbal_price_plan,max(d.inventbal_date) as dt
  from f_invent_bal d join  f_part e on d.part_id=e.part_id
  where d.wrhus_id like @arg_wrhus and d.inventbal_date <@arg_dtbe  and e.partacctype_id like @arg_acctypeid
    and e.part_id like @arg_partid
  group by d.wrhus_id,d.part_id, d.inventbal_price_plan) b
 on a.wrhus_id=b.wrhus_id and a.part_id=b.part_id and
  a.inventbal_price_plan=b.inventbal_price_plan and a.inventbal_date=b.dt
 left outer join f_part c
 on a.part_id=c.part_id
where (a.inventbal_amnt <> 0 and a.inventbal_amnt is not null) or (a.inventbal_cost <> 0 and a.inventbal_cost is not null)
group by a.part_id,a.inventbal_price_plan
order by a.part_id,a.inventbal_price_plan

----------期间增加明细---------------------------------
  insert #temp_wrkhus_in
  select b.part_id,
   b.part_price_plan,
   sum(case when a.inventb_whycat = 'CT' then -1 * isnull(b.inventbi_amnt,0)
              else isnull(b.inventbi_amnt,0) end),
   sum(case when a.inventb_whycat = 'CT' then -1 * isnull(b.inventbi_cost_plan, 0)
              else isnull(b.inventbi_cost_plan,0) end)
    from f_invent_bill a join f_invent_bill_items b on a.inventb_id = b.inventb_id
      left outer join f_part c on b.part_id = c.part_id
   where ( (a.inventb_tocat = 'KC' and a.inventb_to like @arg_wrhus ) or (a.inventb_whycat = 'CT' and a.inventb_fm like @arg_wrhus )) --(a.inventb_tocat = 'KC' or a.inventb_whycat = 'CT')
     and a.inventb_status = 'E'
     and a.inventb_whycat <> 'ST'
     and a.inventb_date >= @arg_dtbe
     and a.inventb_date < @arg_dten 
     and c.partacctype_id like @arg_acctypeid 
--     and a.inventb_to like @arg_wrhus
     and b.part_id like @arg_partid   
group by b.part_id,b.part_price_plan
order by b.part_id,b.part_price_plan

----------期间减少明细---------------------------------

  insert #temp_wrkhus_out
  select b.part_id,
         b.part_price_plan,
   sum(case when a.inventb_whycat = 'ST' then -1 * isnull(b.inventbi_amnt,0)
              else isnull(b.inventbi_amnt,0) end),
   sum(case when a.inventb_whycat = 'ST' then -1 * isnull(b.inventbi_cost_plan, 0)
              else isnull(b.inventbi_cost_plan,0) end)
--   sum(b.inventbi_cost_plan)
 from f_invent_bill a join f_invent_bill_items b on a.inventb_id = b.inventb_id
   left outer join f_part c on b.part_id = c.part_id
   where ((a.inventb_fmcat = 'KC' and a.inventb_fm like @arg_wrhus ) or (a.inventb_whycat = 'ST' and a.inventb_to like @arg_wrhus ) )
  and a.inventb_whycat <> 'CT'
  and a.inventb_status = 'E'
  and a.inventb_date >= @arg_dtbe
  and a.inventb_date < @arg_dten 
  and c.partacctype_id like @arg_acctypeid 
  and b.part_id like @arg_partid
group by b.part_id,b.part_price_plan
order by b.part_id,b.part_price_plan

------------期末余额--------------------------
--up_im_current_wrhus_list
insert #temp_wrkhus_oh
select a.part_id,a.inventbal_price_plan,sum(a.inventbal_amnt) as amnt_ini ,sum(a.inventbal_cost) as cost_ini
from f_invent_bal a join
 (select d.wrhus_id,d.part_id,d.inventbal_price_plan,max(d.inventbal_date) as dt
  from f_invent_bal d join  f_part e on d.part_id=e.part_id
  where d.wrhus_id like @arg_wrhus and d.inventbal_date <@arg_dten  and e.partacctype_id like @arg_acctypeid
    and e.part_id like @arg_partid
  group by d.wrhus_id,d.part_id, d.inventbal_price_plan) b
 on a.wrhus_id=b.wrhus_id and a.part_id=b.part_id and
  a.inventbal_price_plan=b.inventbal_price_plan and a.inventbal_date=b.dt
 left outer join f_part c
 on a.part_id=c.part_id
where (a.inventbal_amnt <> 0 and a.inventbal_amnt is not null) or (a.inventbal_cost <> 0 and a.inventbal_cost is not null)
group by a.part_id,a.inventbal_price_plan
order by a.part_id,a.inventbal_price_plan

----取得用户手工输入的盘点数量 090602
insert #temp_wrkhus_bal (part_id,price_plan,invent_amnt)
select a.part_id,a.part_price,sum(a.partb_amnt) as part_amnt
from f_part_tbal a join
 (select d.part_id,d.part_price,max(d.partb_date) as dt
  from f_part_tbal d join  f_part e on d.part_id=e.part_id
  where d.partb_date <=@arg_dten  and e.partacctype_id like @arg_acctypeid
    and e.part_id like @arg_partid
  group by d.part_id,d.part_price) b
 on a.part_id=b.part_id and
  a.part_price=b.part_price and a.partb_date=b.dt
 left outer join f_part c
 on a.part_id=c.part_id
--where (a.partb_amnt <> 0 and a.partb_amnt is not null)
group by a.part_id,a.part_price
order by a.part_id,a.part_price

------取得最全行-------------------------

insert #temp_wrkhus_all  (part_id , price_plan )
select distinct a.part_id , a.price_plan
from (
select part_id , price_plan from #temp_wrkhus_ini
union
select part_id , price_plan from #temp_wrkhus_in
union
select part_id , price_plan from #temp_wrkhus_out
union
select part_id , price_plan from #temp_wrkhus_oh
union
select part_id , price_plan from #temp_wrkhus_bal ) a

------购建报表-------------------------

update #temp_wrkhus_all set amnt_ini=#temp_wrkhus_ini.invent_amnt , cost_ini=#temp_wrkhus_ini.invent_cost
  from #temp_wrkhus_ini
  where #temp_wrkhus_all.part_id=#temp_wrkhus_ini.part_id and #temp_wrkhus_all.price_plan=#temp_wrkhus_ini.price_plan

update #temp_wrkhus_all set amnt_in=#temp_wrkhus_in.invent_amnt , cost_in=#temp_wrkhus_in.invent_cost
  from #temp_wrkhus_in
  where #temp_wrkhus_all.part_id=#temp_wrkhus_in.part_id and #temp_wrkhus_all.price_plan=#temp_wrkhus_in.price_plan

update #temp_wrkhus_all set amnt_out=#temp_wrkhus_out.invent_amnt , cost_out=#temp_wrkhus_out.invent_cost
  from #temp_wrkhus_out
  where #temp_wrkhus_all.part_id=#temp_wrkhus_out.part_id and #temp_wrkhus_all.price_plan=#temp_wrkhus_out.price_plan

update #temp_wrkhus_all set amnt_oh=#temp_wrkhus_oh.invent_amnt , cost_oh=#temp_wrkhus_oh.invent_cost  from #temp_wrkhus_oh
  where #temp_wrkhus_all.part_id=#temp_wrkhus_oh.part_id and #temp_wrkhus_all.price_plan=#temp_wrkhus_oh.price_plan

---判断是否已存在进销存汇总数据导出表temp_jxc_export
DECLARE @TP_COUNT INT
SELECT @TP_COUNT = 0
SELECT @TP_COUNT = COUNT(*)
 FROM SYS.OBJECTS
 WHERE SYS.OBJECTS.NAME = 'temp_jxc_export'

IF (@TP_COUNT <> 0)
BEGIN
 DELETE FROM temp_jxc_export

 INSERT INTO temp_jxc_export
 select a.part_id, f.part_name ,f.PARTACCTYPE_ID, f.part_specs ,f.part_unit , a.price_plan  ,
       a.amnt_ini,  a.cost_ini,
       a.amnt_in , a.cost_in,
       a.amnt_out, a.cost_out,
       a.amnt_oh , a.cost_oh,
       isnull(g.invent_amnt,0) as partb_amnt,
      @arg_dten  as partb_date
 from #temp_wrkhus_all a left outer join f_part f on a.part_id = f.part_id
  left outer join #temp_wrkhus_bal g on  (a.part_id = g.part_id and a.price_plan = g.price_plan )
  where a.part_id in          
   (
    select distinct part_id
     from f_invent_bal f
     where f.inventbal_foreign like @arg_isforeign)
 order by a.part_id , a.price_plan
END
ELSE
BEGIN
 select a.part_id, f.part_name ,f.PARTACCTYPE_ID, f.part_specs ,f.part_unit , a.price_plan  ,
       a.amnt_ini,  a.cost_ini,
       a.amnt_in , a.cost_in,
       a.amnt_out, a.cost_out,
       a.amnt_oh , a.cost_oh,
       isnull(g.invent_amnt,0) as partb_amnt,
      @arg_dten  as partb_date
     into temp_jxc_export
 from #temp_wrkhus_all a left outer join f_part f on a.part_id = f.part_id
  left outer join #temp_wrkhus_bal g on  (a.part_id = g.part_id and a.price_plan = g.price_plan )
  where a.part_id in          
   (
    select distinct part_id
     from f_invent_bal f
     where f.inventbal_foreign like @arg_isforeign
 )
 order by a.part_id , a.price_plan
END

drop table #temp_wrkhus_all
drop table #temp_wrkhus_ini
drop table #temp_wrkhus_in
drop table #temp_wrkhus_out
drop table #temp_wrkhus_oh
drop table #temp_wrkhus_bal

end