SET NOCOUNT Off
Create Table #jzc_DATA2 (
FItemID int null,
Fnumber varchar(255) null,
Fname varchar(255) null,
Fmodel varchar(255) null,
Funitid int null,
Funitname varchar(255) null,
FCustID int null,
Fcustname varchar(255) null,
FYWYID int null,
FYWYname varchar(255) null,
FOutQty float null,
FOutAmount Decimal(28,2) null)
Insert Into #jzc_DATA2 (FItemID,FCustID,FYWYID,FOutQty,FOutAmount)
Select v2.FItemID,v1.FSupplyID,v1.FEmpid,sum(v2.FQty),sum(v2.FconsignAmount)
From ICStockBill v1,ICStockBillEntry v2
Where v1.FTranType =21 And v1.FCancelLation=0 and v1.FInterID = v2.FInterID
AND v1.FDate >='2008-09-01' AND v1.FDate <='2008-09-30'
group by v1.Fempid,v1.fSupplyid,v2.fitemid
update t1 set t1.fnumber=t2.fnumber,t1.fname=t2.fname,t1.fmodel=t2.fmodel,t1.funitid=t2.funitid from #jzc_DATA2 t1,t_icitem t2 where t1.fitemid=t2.fitemid
update t1 set t1.fcustname=t2.fname from #jzc_DATA2 t1,t_Organization t2 where t1.fcustid=t2.FItemID
update t1 set t1.fywyname=t2.fname from #jzc_DATA2 t1,t_emp t2 where t1.fywyid=t2.FItemID
update t1 set t1.funitname=t2.fname from #jzc_DATA2 t1,t_unitgroup t2 where t1.Funitid=t2.Fdefaultunitid
Insert Into #jzc_DATA2
Select 200000+fywyid,'业务员小计','','','','',100000,'',Fywyid,'',sum(FoutQty),sum(FoutAmount)
From #jzc_DATA2 Group By Fywyid --业务员合计
Insert Into #jzc_DATA2
Select 100000+fcustid,'客户小计','','','','',fcustid,'',Fywyid,'',sum(FoutQty),sum(FoutAmount)
From #jzc_DATA2 where fcustid<>100000 Group By Fywyid,fcustid --在业务员下分客户小计
--delete #jzc_DATA2 where fnumber='客户小计' and
Insert Into #jzc_DATA2
Select 300000,'总计','','','','',299999,'',199999,'',sum(FoutQty),sum(FoutAmount)
From #jzc_DATA2 --总计
update t1 set t1.fnumber=t2.Fname+'业绩小计' from #jzc_DATA2 t1,t_emp t2 where t1.Fywyid=t2.fitemid and t1.fnumber='业务员小计'
update t1 set t1.fnumber=t2.Fname+'销售小计' from #jzc_DATA2 t1,t_Organization t2 where t1.FcustID=t2.FItemID and t1.fnumber='客户小计'
select Fywyname as '业务员',Fcustname AS '客户',Fnumber as '商品编码',Fname AS '商品名称',Fmodel AS '规格型号',Funitname AS '单位',FOutQty AS '数量',FOutAMOUNT AS '金额'
from #jzc_DATA2 order by Fywyid,Fcustid,Fitemid
--select * from #jzc_data2 order by fywyid,Fcustid,Fitemid
DROP TABLE #jzc_DATA2
--delete #jzc_DATA2
--select * from icstockbill where ftrantype=21 order by fEMPid and fEMPid=0 --43499
delete t2 from icstockbill t1,icstockbillentry t2
where t1.finterid=t2.finterid and t1.ftrantype=1 and t2.finterid<=9322 and t2.finterid>=7435
打开icstockbillentry 粘贴EXECL中的sheet3表
select * from icstockbill
我想让结果在datagrid控件中显示出来.
