alter PROCEDURE usp_sumgoods(@supplierid VARCHAR(50)) as
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMain]') and xtype in (N'FN', N'IF', N'TF'))
--
drop function [dbo].[GetMain]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sumgoods]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[sumgoods]
DECLARE @sql VARCHAR(MAX)
SET @sql = 'CREATE view [dbo].[sumgoods]
with schemabinding
as
(
select id,mainid,goodsNo,(select modal from dbo.bas_goods where goodsno = a.goodsNo) as modal,goodscnname,color,
(select cnname from dbo.bas_qtyunit where id=qtyunit) as qtyunit,swareqty,thispurchaseqty,Inqty,backqty,
(inqty-backqty) as realInqty,waitOutqty,freezeqty,safeqty from
(select id,mainid,goodsNo,cnname as goodscnname,color,qtyunit,
case when goodstype=1 then
isnull((select isnull(balanceqty,0) from dbo.bas_goods_center where warehouseid=2 and style=1 and goodsid=a.goodsid),0)
when goodstype=2 then
isnull((select isnull(balanceqty,0) from dbo.M_property_warehouse where warehouseid=1 and style=1 and wid=a.goodsid),0)
end as swareqty, thispurchaseqty,
isnull((select SUM(m.instockqty) from dbo.wm_stock_goods m where m.purchasegoodsid =a.id and instocktype<17),0) as inqty,
isnull((select sum(outstockqty) from dbo.wm_stock_goods_out m where purchasecontractgoodsid=a.id and outtype=4 and m.printId=a.printid),0) as backqty,
case when goodstype=2 then isnull(dbo.Get_material_needqty(a.goodsid,1,1),0)
when goodstype=1 then isnull(dbo.Get_material_needqty(a.goodsid,2,1),0) end
as waitOutqty,
(case when goodstype=1 then (select freezeqty from dbo.bas_goods_center where style=1 and warehouseid=1 and goodsid=a.goodsid)
when goodstype=2 then (select freezeqty from dbo.M_property_warehouse where style=1 and warehouseid=1 and wid=a.goodsid) end) as freezeqty,
isnull((case when goodstype=1 then (select isnull(safeqty,0) from dbo.bas_goods where id=a.goodsid)
when goodstype=2 then (select isnull(savesafeqty,0) from dbo.M_property where id=a.goodsid) end ),0) as safeqty
from dbo.purchase_contract_goods a where mainid in
(select id from dbo.purchase_contract
where (type=1 or type=2 or type=11 or type=12 or type=13 or type=14) and status=8 and instockstatus<4 and supplierid = '+@supplierid+')) a where (inqty-backqty)