![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
USE
[
DEV_WXT
]
GO
/* ***** Object: StoredProcedure [dbo].[usp_GetPeriodInOutList] Script Date: 08/19/2010 17:15:58 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [ dbo ] . [ usp_GetPeriodInOutList ]
(
@strSiteCode nvarchar ( 200 ) = '' ,
@BeginDate nvarchar ( 100 ) = '' ,
@EndDate nvarchar ( 100 ) = '' ,
@Brand nvarchar ( 200 ) = '' ,
@MacNo nvarchar ( 200 ) = ''
)
AS
declare @EndDate1 nvarchar ( 10 )
select @EndDate1 = convert ( char ( 10 ), dateadd ( day , 1 , @EndDate ), 121 )
-- print @EndDate1
-- -insert trx detail
select A.sitecode,a.trxno,a.closedate,b.fromsubinv as subinv,b.brand,b.itemno,
b.unitcost * b.applyqty as InAmount,b.applyqty as InQty,
convert ( decimal ( 19 , 2 ), 0 ) as OutAmount, 0 as OutQty into # temp
from dbo.SYS_TrxHeadHist a,dbo.SYS_TrxLineHist b
where a.SiteCode = b.SiteCode and a.TrxNo = B.TrxNo and a.SiteCode = @strSiteCode
and a.Status = ' AP '
and b.FromSubinv = '' and b.ToSubinv <> '' and (b.Brand = @Brand or @Brand = '' )
and (b.ItemNo = @MacNo or @MacNo = '' )
and a.CloseDate < @EndDate1 AND b.ApplyQty > 0
insert into # temp
select A.SiteCode,a.TrxNo,a.CloseDate,b.FromSubinv as subinv,b.Brand,b.ItemNo,
convert ( decimal ( 19 , 2 ), 0 ) as InAmount, 0 as InQty,
b.UnitCost * ( - b.ActualQty) as OutAmount, - b.ApplyQty as OutQty
from dbo.SYS_TrxHeadHist a,dbo.SYS_TrxLineHist b
where a.SiteCode = b.SiteCode and a.TrxNo = B.TrxNo and a.SiteCode = @strSiteCode
AND a.Status = ' AP '
and b.FromSubinv <> '' and b.ToSubinv = '' and (b.Brand = @Brand or @Brand = '' )
and (b.ItemNo = @MacNo or @MacNo = '' )
and a.CloseDate < @EndDate1 AND b.ApplyQty < 0
-- -得到期末数量,金额
select sitecode AS SITECODE,brand as Brand,itemno as MACNO,
convert ( nvarchar ( 10 ), '' ) as Type, convert ( nvarchar ( 100 ), '' ) as MACNAME, convert ( nvarchar ( 4000 ), '' ) as Model,
0 as BeginQty, convert ( decimal ( 19 , 2 ), 0 ) as BeginAmount,
0 as PeriodInQty, convert ( decimal ( 19 , 2 ), 0 ) as PeriodInAmount,
0 as PeriodOutQty, convert ( decimal ( 19 , 2 ), 0 ) as PeriodOutAmount,
sum (InQty) - sum (OutQty) as EndQty, sum (InAmount) - sum (OutAmount) as EndAmount
into #temp_r from # temp group by sitecode,brand,itemno
-- 更新期初数量,金额
select brand,itemno,
sum (InQty) - sum (OutQty) as BeginQty, sum (InAmount) - sum (OutAmount) as BeginAmount
into #temp1 from # temp where closedate < @BeginDate group by brand,itemno
update #temp_r set BeginQty = b.BeginQty,BeginAmount = b.BeginAmount from #temp_r a,#temp1 b
where a.Brand = b.brand and a.MACNO = b.itemno
-- 更新本期入库数量,金额
select brand,itemno,
sum (InQty) as InQty, sum (InAmount) as InAmount
into #temp2 from # temp where closedate >= @BeginDate group by brand,itemno
update #temp_r set PeriodInQty = b.InQty,PeriodInAmount = b.InAmount from #temp_r a,#temp2 b
where a.Brand = b.brand and a.MACNO = b.itemno
-- 更新本期出库数量,金额
select brand,itemno,
sum (OutQty) as OutQty, sum (OutAmount) as OutAmount
into #temp3 from # temp where closedate >= @BeginDate group by brand,itemno
update #temp_r set PeriodOutQty = b.OutQty,PeriodOutAmount = b.OutAmount from #temp_r a,#temp3 b
where a.Brand = b.brand and a.MACNO = b.itemno
-- 更新配件类型,料品描述, 型号
update #temp_r set TYPE = b.TYPE,MACNAME = b.ItemDesc,MODEL = dbo.GetModelList(b.sitecode,b.BRAND,b.ItemNo)
from #temp_r a,SYS_SiteItem b
WHERE a.BRAND = b.BRAND and a.macno = b.ItemNo
SELECT * FROM #temp_r order by BRAND,MACNO
drop table #temp_r,# temp ,#temp1,#temp2,#temp3
GO
/* ***** Object: StoredProcedure [dbo].[usp_GetPeriodInOutList] Script Date: 08/19/2010 17:15:58 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [ dbo ] . [ usp_GetPeriodInOutList ]
(
@strSiteCode nvarchar ( 200 ) = '' ,
@BeginDate nvarchar ( 100 ) = '' ,
@EndDate nvarchar ( 100 ) = '' ,
@Brand nvarchar ( 200 ) = '' ,
@MacNo nvarchar ( 200 ) = ''
)
AS
declare @EndDate1 nvarchar ( 10 )
select @EndDate1 = convert ( char ( 10 ), dateadd ( day , 1 , @EndDate ), 121 )
-- print @EndDate1
-- -insert trx detail
select A.sitecode,a.trxno,a.closedate,b.fromsubinv as subinv,b.brand,b.itemno,
b.unitcost * b.applyqty as InAmount,b.applyqty as InQty,
convert ( decimal ( 19 , 2 ), 0 ) as OutAmount, 0 as OutQty into # temp
from dbo.SYS_TrxHeadHist a,dbo.SYS_TrxLineHist b
where a.SiteCode = b.SiteCode and a.TrxNo = B.TrxNo and a.SiteCode = @strSiteCode
and a.Status = ' AP '
and b.FromSubinv = '' and b.ToSubinv <> '' and (b.Brand = @Brand or @Brand = '' )
and (b.ItemNo = @MacNo or @MacNo = '' )
and a.CloseDate < @EndDate1 AND b.ApplyQty > 0
insert into # temp
select A.SiteCode,a.TrxNo,a.CloseDate,b.FromSubinv as subinv,b.Brand,b.ItemNo,
convert ( decimal ( 19 , 2 ), 0 ) as InAmount, 0 as InQty,
b.UnitCost * ( - b.ActualQty) as OutAmount, - b.ApplyQty as OutQty
from dbo.SYS_TrxHeadHist a,dbo.SYS_TrxLineHist b
where a.SiteCode = b.SiteCode and a.TrxNo = B.TrxNo and a.SiteCode = @strSiteCode
AND a.Status = ' AP '
and b.FromSubinv <> '' and b.ToSubinv = '' and (b.Brand = @Brand or @Brand = '' )
and (b.ItemNo = @MacNo or @MacNo = '' )
and a.CloseDate < @EndDate1 AND b.ApplyQty < 0
-- -得到期末数量,金额
select sitecode AS SITECODE,brand as Brand,itemno as MACNO,
convert ( nvarchar ( 10 ), '' ) as Type, convert ( nvarchar ( 100 ), '' ) as MACNAME, convert ( nvarchar ( 4000 ), '' ) as Model,
0 as BeginQty, convert ( decimal ( 19 , 2 ), 0 ) as BeginAmount,
0 as PeriodInQty, convert ( decimal ( 19 , 2 ), 0 ) as PeriodInAmount,
0 as PeriodOutQty, convert ( decimal ( 19 , 2 ), 0 ) as PeriodOutAmount,
sum (InQty) - sum (OutQty) as EndQty, sum (InAmount) - sum (OutAmount) as EndAmount
into #temp_r from # temp group by sitecode,brand,itemno
-- 更新期初数量,金额
select brand,itemno,
sum (InQty) - sum (OutQty) as BeginQty, sum (InAmount) - sum (OutAmount) as BeginAmount
into #temp1 from # temp where closedate < @BeginDate group by brand,itemno
update #temp_r set BeginQty = b.BeginQty,BeginAmount = b.BeginAmount from #temp_r a,#temp1 b
where a.Brand = b.brand and a.MACNO = b.itemno
-- 更新本期入库数量,金额
select brand,itemno,
sum (InQty) as InQty, sum (InAmount) as InAmount
into #temp2 from # temp where closedate >= @BeginDate group by brand,itemno
update #temp_r set PeriodInQty = b.InQty,PeriodInAmount = b.InAmount from #temp_r a,#temp2 b
where a.Brand = b.brand and a.MACNO = b.itemno
-- 更新本期出库数量,金额
select brand,itemno,
sum (OutQty) as OutQty, sum (OutAmount) as OutAmount
into #temp3 from # temp where closedate >= @BeginDate group by brand,itemno
update #temp_r set PeriodOutQty = b.OutQty,PeriodOutAmount = b.OutAmount from #temp_r a,#temp3 b
where a.Brand = b.brand and a.MACNO = b.itemno
-- 更新配件类型,料品描述, 型号
update #temp_r set TYPE = b.TYPE,MACNAME = b.ItemDesc,MODEL = dbo.GetModelList(b.sitecode,b.BRAND,b.ItemNo)
from #temp_r a,SYS_SiteItem b
WHERE a.BRAND = b.BRAND and a.macno = b.ItemNo
SELECT * FROM #temp_r order by BRAND,MACNO
drop table #temp_r,# temp ,#temp1,#temp2,#temp3