if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SaleSat]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SaleSat]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE sp_SaleSat
@begindate datetime,
@enddate datetime,
@cCusCode varchar (40),   --客戶名稱
@cWhCode varchar (40)   --倉庫編碼
AS
--年初轉存
SELECT ccusname as cCusCode,cWhCode as cWhCode,cInvCode as cInvCode,  
sum(rukusl) as ncsl  into #temp0 
FROM v_test  
WHERE ddate<convert(datetime,convert(char(4),year(getdate()))+'/01/01') 
and cWhCode like '%'+@cWhCode+'%' and ccusname like '%'+@cCusCode+'%' and cvouchtype='34'
group by ccusname,cWhCode,cInvCode
--期初庫存

SELECT ccusname as cCusCode,cWhCode as cWhCode,cInvCode as cInvCode,   
sum(rukusl) - sum(chukusl) as qichusl into #temp1 
FROM v_test
WHERE ddate>=convert(datetime,convert(char(4),year(getdate()))+'/01/01') 
and ddate<@begindate and ccusname like '%'+@cCusCode+'%' 
and cWhCode like '%'+@cWhCode+'%' and ccusname is not null
group by ccusname,cWhCode,cInvCode

--入庫
SELECT ccusname as cCusCode,cWhCode as cWhCode,cInvCode as cInvCode,   
sum(rukusl) as rukusl  into #temp2
FROM v_test  
WHERE ddate>=@begindate and ddate<=@enddate and ccusname like '%'+@cCusCode+'%' 
and cWhCode like '%'+@cWhCode+'%' and ccusname is not null
group by ccusname,cWhCode,cInvCode
 --出庫
SELECT ccusname as cCusCode,cWhCode as cWhCode,cInvCode as cInvCode,   
sum(chukusl) as chukusl into #temp3
FROM v_test  
WHERE ddate>=@begindate and ddate<=@enddate and ccusname like '%'+@cCusCode+'%' 
and cWhCode like '%'+@cWhCode+'%' and ccusname is not null
group by ccusname,cWhCode,cInvCode

 
--期末
SELECT ccusname as cCusCode,cWhCode as cWhCode,cInvCode as cInvCode,   
sum(rukusl) - sum(chukusl) as qimosl into #temp4 
FROM v_test  
WHERE ddate>=convert(datetime,convert(char(4),year(getdate()))+'/01/01') and ddate<=@enddate and ccusname like '%'+@cCusCode+'%' 
and cWhCode like '%'+@cWhCode+'%' and ccusname is not null
group by ccusname,cWhCode,cInvCode

select #temp4.cCusCode,#temp4.cWhCode,Warehouse.cWhName,#temp4.cInvCode,Inventory.cInvName,
(case when #temp1.qichusl is null then 0 else #temp1.qichusl end) + (case when #temp0.ncsl is null then 0 else #temp0.ncsl end ) as qichusl,
case when Inventory.iInvNCost is null then 0 else Inventory.iInvNCost end as qcjg,
case when Inventory.iInvNCost*#temp1.qichusl is null then 0 else Inventory.iInvNCost*#temp1.qichusl end as qcje,
case when #temp2.rukusl is null then 0 else #temp2.rukusl end as rukusl,
case when Inventory.iInvNCost is null then 0 else Inventory.iInvNCost end as rkjg,
case when Inventory.iInvNCost*#temp2.rukusl is null then 0 else Inventory.iInvNCost*#temp2.rukusl end as rkje,
case when #temp3.chukusl is null then 0 else #temp3.chukusl end as chukusl,
case when Inventory.iInvNCost is null then 0 else Inventory.iInvNCost end as ckjg,
case when Inventory.iInvNCost*#temp3.chukusl is null then 0 else Inventory.iInvNCost*#temp3.chukusl end as ckje,
(case when #temp4.qimosl is null then 0 else #temp4.qimosl end) + (case when #temp0.ncsl is null then 0 else #temp0.ncsl end) as qimosl,
case when Inventory.iInvNCost is null then 0 else Inventory.iInvNCost end as qmjg,
case when Inventory.iInvNCost*#temp4.qimosl is null then 0 else Inventory.iInvNCost*#temp4.qimosl end as qmje
from #temp4 LEFT OUTER JOIN #temp0 on #temp4.cWhCode = #temp0.cWhCode and #temp4.cInvCode = dbo.#temp0.cInvCode 
            LEFT OUTER JOIN #temp1 on #temp4.cCusCode = #temp1.cCusCode and #temp4.cWhCode = #temp1.cWhCode and #temp4.cInvCode = dbo.#temp1.cInvCode
            LEFT OUTER JOIN #temp2 on #temp4.cCusCode = #temp2.cCusCode and #temp4.cWhCode = #temp2.cWhCode and #temp4.cInvCode = #temp2.cInvCode
	    LEFT OUTER JOIN #temp3 on #temp4.cCusCode = #temp3.cCusCode and #temp4.cWhCode = #temp3.cWhCode and #temp4.cInvCode = #temp3.cInvCode 
            LEFT OUTER JOIN Inventory on #temp4.cInvCode = Inventory.cInvCode
            LEFT OUTER JOIN Warehouse on #temp4.cWhCode = Warehouse.cWhCode
drop table #temp0
drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO