sp_materialdayreport.sql

(Java+SQL)-大型企业JAVA的ERP系统

CREATE PROCEDURE sp_MaterialDayReport -- 物资进出日报表
        @loginName varchar(30) 
	,@ip varchar(20) 
	,@machineName varchar(30) 
	,@wearhouseId varchar(10) -- 仓库编码
	,@beginDate datetime    -- 开始时间
	,@endDate datetime    -- 结束时间				
AS DECLARE 
	 @error_var int 
	,@rowcount_var int
	,@wearhouseName varchar(30)  -- 仓库名称	
        ,@productId varchar(10)    -- 产品编码	
	,@productName varchar(30)  -- 产品名称
	,@productDesc varchar(30)  -- 规格/型号		
        ,@stockQty float           -- 现有库存量                
	,@inQty float              -- 今日进货量
	,@removeQty float          -- 今日调拨出库量
        ,@salesQty  float          -- 今日销售出库
	,@undrawQty float          -- 未提
        ,@inRecord varchar(50)     -- 进货记录
        ,@salesRecord varchar(50)  -- 销售记录    
        ,@countNum int             -- 记录数
                                
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT 
	IF @error_var <> 0 RETURN -102 
	IF @rowcount_var = 0 RETURN -103 

SELECT @stockQty = 0;
SELECT @inQty = 0;
SELECT @removeQty = 0;
SELECT @salesQty = 0;
SELECT @undrawQty = 0;


-- 创建临时表
CREATE TABLE #Tmp_MaterialDayReport 
(        
	wearhouseId varchar(10)   -- 仓库编码		
	,wearhouseName varchar(30)   -- 仓库编码			
        ,productId varchar(10)    -- 产品编码	
	,productName varchar(30)  -- 产品名称
	,productDesc varchar(30)  -- 规格/型号	
        ,stockQty float           -- 现有库存量                
	,inQty float              -- 今日进货量
	,removeQty float          -- 今日调拨出库量
        ,salesQty  float          -- 今日销售出库
	,undrawQty float          -- 未提
        ,inRecord varchar(50)     -- 进货记录
        ,salesRecord varchar(50)  -- 销售记录    
	,beginDate datetime         -- 开始时间  
	,endDate datetime         -- 结束时间  
        ,countNum int             -- 记录数
)   

DECLARE product_cur CURSOR FOR 
SELECT DISTINCT LocationState.productId productId FROM LocationState,ProductLocation,Inventory
 WHERE LocationState.locationId = ProductLocation.locationId
   AND LocationState.productId = Inventory.productId
   AND ProductLocation.wearhouseId = @wearhouseId
   AND Inventory.inventoryDate >= @beginDate
   AND Inventory.inventoryDate <= @endDate
OPEN product_cur

FETCH NEXT FROM product_cur
INTO @productId


WHILE @@FETCH_STATUS = 0
BEGIN

		  -- 仓库名称
		    SELECT @wearhouseName = wearhouseName
		      FROM Wearhouse
		     WHERE wearhouseId = @wearhouseId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 
		    
		  -- 品名
		    SELECT @productName = productName
		      FROM Product
		     WHERE productId = @productId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 
		    
		  -- 规格
		    SELECT @productDesc = productDesc
		      FROM Product
		     WHERE productId = @productId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 		    

		  -- 现有库存量
		    SELECT @stockQty = sum(quantity)
		      FROM LocationState
		     WHERE productId = @productId
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

                  -- 今日进货量
		    SELECT @inQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 1 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 今日调拨出库量
		    SELECT @removeQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 2 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   
		    
                  -- 今日销售出库
		    SELECT @salesQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 0 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    

	            INSERT INTO #Tmp_MaterialDayReport(wearhouseId,wearhouseName,productId,productName,productDesc,stockQty,inQty,removeQty,salesQty,undrawQty,inRecord,salesRecord,beginDate,endDate)	          
	                                   VALUES(@wearhouseId,@wearhouseName,@productId,@productName,@productDesc,@stockQty,@inQty,@removeQty,@salesQty,@undrawQty,@inRecord,@salesRecord,@beginDate,@endDate)	            
	FETCH NEXT FROM product_cur
        INTO @productId
END

CLOSE product_cur

DEALLOCATE product_cur

SELECT @countNum  = count(*)
FROM #Tmp_MaterialDayReport

UPDATE #Tmp_MaterialDayReport
   SET countNum = @countNum

SELECT wearhouseId,wearhouseName,productId,productName,productDesc,stockQty,inQty,removeQty,salesQty,
       undrawQty,inRecord,salesRecord,beginDate,endDate,countNum
  FROM #Tmp_MaterialDayReport ORDER BY productId
RETURN 0


















GO