CREATE PROCEDURE proc_weber_seorder_sum
@datefrom datetime,
@dateto datetime
AS
SET NOCOUNT ON
--销售订单明细表
select c.fnumber cust_number,m.fbillno,''+convert(char(10),m.fdate,121) fdate,d.fentryid,i.fnumber,i.fname,i.fmodel,d.fqty,99 as FSumSort
into #tmp_weber
from SEOrder m,SEOrderentry d,t_icitem i,t_Organization c
where m.finterid=d.finterid and d.fitemid=i.fitemid and m.fcustid=c.fitemid and m.fdate between @datefrom and @dateto
union
select c.fnumber,m.fbillno,'','','','','',sum(d.fqty) qty,99 as fsumsort
from SEOrder m,SEOrderentry d,t_icitem i,t_Organization c
where m.finterid=d.finterid and d.fitemid=i.fitemid and m.fcustid=c.fitemid and m.fdate between @datefrom and @dateto
group by c.fnumber,c.fname,m.fbillno WITH ROLLUP
update #tmp_weber
set cust_number='总 计▼',fbillno=convert(char(10),@dateto,121),fentryid=(null),fsumsort=101
from #tmp_weber
where cust_number is null
update #tmp_weber
set fbillno='客户小计▼',fdate=convert(char(10),@dateto,121),fentryid=(null),fsumsort=100
from #tmp_weber
where fbillno is null
update #tmp_weber
set fdate='订单小计▼',fentryid=(null),fsumsort=101
from #tmp_weber
where fentryid=0
select t.cust_number,c.fname as cfname,t.fbillno,t.fentryid,t.fdate,t.fnumber,t.fname,t.fmodel,t.fqty,t.fsumsort
from #tmp_weber t
left join t_Organization c on t.cust_number=c.fnumber
drop table #tmp_weber
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
@datefrom datetime,
@dateto datetime
AS
SET NOCOUNT ON
--销售订单明细表
select c.fnumber cust_number,m.fbillno,''+convert(char(10),m.fdate,121) fdate,d.fentryid,i.fnumber,i.fname,i.fmodel,d.fqty,99 as FSumSort
into #tmp_weber
from SEOrder m,SEOrderentry d,t_icitem i,t_Organization c
where m.finterid=d.finterid and d.fitemid=i.fitemid and m.fcustid=c.fitemid and m.fdate between @datefrom and @dateto
union
select c.fnumber,m.fbillno,'','','','','',sum(d.fqty) qty,99 as fsumsort
from SEOrder m,SEOrderentry d,t_icitem i,t_Organization c
where m.finterid=d.finterid and d.fitemid=i.fitemid and m.fcustid=c.fitemid and m.fdate between @datefrom and @dateto
group by c.fnumber,c.fname,m.fbillno WITH ROLLUP
update #tmp_weber
set cust_number='总 计▼',fbillno=convert(char(10),@dateto,121),fentryid=(null),fsumsort=101
from #tmp_weber
where cust_number is null
update #tmp_weber
set fbillno='客户小计▼',fdate=convert(char(10),@dateto,121),fentryid=(null),fsumsort=100
from #tmp_weber
where fbillno is null
update #tmp_weber
set fdate='订单小计▼',fentryid=(null),fsumsort=101
from #tmp_weber
where fentryid=0
select t.cust_number,c.fname as cfname,t.fbillno,t.fentryid,t.fdate,t.fnumber,t.fname,t.fmodel,t.fqty,t.fsumsort
from #tmp_weber t
left join t_Organization c on t.cust_number=c.fnumber
drop table #tmp_weber
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO