sp_salescollectreport.sql

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

CREATE PROCEDURE sp_SalesCollectReport -- 销售汇总表
        @loginName varchar(30) 
	,@ip varchar(20) 
	,@machineName varchar(30) 
	,@classId varchar(10)     -- 产品分类编码	
	,@deptId varchar(10)      -- 部门编码
	,@beginDate datetime      -- 开始时间
	,@endDate datetime        -- 结束时间				
AS DECLARE 
	 @error_var int 
	,@rowcount_var int
        ,@deptName varchar(30)     -- 部门名称
        ,@productId varchar(10)    -- 产品编码
	,@productName  varchar(30) -- 产品名称
        ,@quantity float           -- 数量	
        ,@totalPrice float         -- 金额
	,@countNum int             -- 记录号
                                
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT 
	IF @error_var <> 0 RETURN -102 
	IF @rowcount_var = 0 RETURN -103 


SELECT @totalPrice = 0;

-- 创建临时表
CREATE TABLE #Tmp_SalesCollectReport 
(        deptName varchar(30)      -- 部门名称
	,productName  varchar(30)  -- 产品名称
        ,quantity float            -- 数量	
        ,totalPrice float          -- 金额
        ,beginDate datetime        -- 开始日期
        ,endDate datetime          -- 结束日期        
        ,countNum int
)   

DECLARE product_cur CURSOR FOR 
SELECT productId FROM Production
 WHERE classId = @classId
   
OPEN product_cur

FETCH NEXT FROM product_cur
INTO @productId


WHILE @@FETCH_STATUS = 0
BEGIN
		  -- 部门名称
		    SELECT @deptName = deptName
		      FROM Department
		     WHERE deptId = @deptId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

                  -- 产品名称
		    SELECT @productName = productName
		      FROM Production
		     WHERE productId = @productId	
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 数量
		    SELECT @quantity = sum(quantity)
		      FROM SlipProduct,SalesSlip
		     WHERE SlipProduct.productId = @productId
		       AND SlipProduct.slipId = SalesSlip.slipId
		       AND SalesSlip.deptId = @deptId
		       AND SalesSlip.slipDate >= @beginDate
		       AND SalesSlip.slipDate <= @endDate
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    

                  -- 金额
		    SELECT @totalPrice = sum(totalPrice)
		      FROM SalesSlip,SlipProduct
		     WHERE SalesSlip.deptId = @deptId
		       AND SalesSlip.slipDate >= @beginDate
		       AND SalesSlip.slipDate <= @endDate
		       AND SalesSlip.slipId = SlipProduct.slipId
		       AND SlipProduct.productId = @productId		       
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    

	            INSERT INTO #Tmp_SalesCollectReport(deptName,productName,quantity,totalPrice,beginDate,endDate)
	                                   VALUES(@deptName,@productName,@quantity,@totalPrice,@beginDate,@endDate)

	FETCH NEXT FROM product_cur
        INTO @productId
END

CLOSE product_cur

DEALLOCATE product_cur

SELECT @countNum  = count(*)
FROM #Tmp_SalesCollectReport

UPDATE #Tmp_SalesCollectReport
   SET countNum = @countNum

SELECT deptName,productName,quantity,totalPrice,beginDate,endDate,countNum
  FROM #Tmp_SalesCollectReport
RETURN 0