sp_acurrentaccountreport.sql

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

CREATE PROCEDURE sp_ACurrentAccountReport -- 个人销售明细表
        @loginName varchar(30) 
	,@ip varchar(20) 
	,@machineName varchar(30) 
	,@id varchar(10)      -- 编码 	
	,@idType int          -- 编码类型  0:部门编码;1:人员编码 		
	,@beginDate datetime      -- 开始时间
	,@endDate datetime        -- 结束时间
AS DECLARE 
	 @error_var int 
	,@rowcount_var int
        ,@deptName varchar(30)     -- 部门名称
        ,@empName varchar(10)	   -- 姓名
        ,@customerId varchar(10) -- 单位编码                
        ,@customerName varchar(30) -- 单位名称        
	,@lastAR  float  -- 上月应收款
	,@lastAP  float  -- 上月应付款
	,@thisAR  float  -- 本月应收款
	,@thisAP  float  -- 本月应付款
        ,@balance float  -- 本月增减
	,@lmDate datetime -- 最后发生日期        
	,@countNum int    -- 记录数

--accountReceivable  应收款
--accountPayable    应付款

SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT 
	IF @error_var <> 0 RETURN -102 
	IF @rowcount_var = 0 RETURN -103 


SELECT @lastAR = 0;
SELECT @lastAP = 0;
SELECT @thisAR = 0;
SELECT @thisAP = 0;
SELECT @balance = 0;

-- 创建临时表
CREATE TABLE #Tmp_ACurrentAccountReport 
(        deptName varchar(30)          -- 部门名称
        ,empName varchar(10)	       -- 姓名
        ,customerName varchar(30)      -- 单位名称        
        ,lastAR float          -- 上月应收款
        ,lastAP float          -- 上月应付款
        ,thisAR float          -- 本月应收款
        ,thisAP float          -- 本月应付款                        
        ,balance float         -- 本月增减   
	,lmDate datetime       -- 最后发生日期                     
        ,beginDate datetime    -- 开始日期
        ,endDate datetime      -- 结束日期        
        ,countNum int          -- 记录数
)   

IF  @idType = 0   -- 部门编码
BEGIN
	DECLARE customer_cur CURSOR FOR 
	SELECT distinct customerId customerId 
 	  FROM Invoice
	 WHERE Invoice.deptId = @id
	   AND Invoice.invoiceDate <= @endDate

END
   
IF  @idType = 1   -- 个人编码
BEGIN
	DECLARE customer_cur CURSOR FOR 
	SELECT distinct customerId customerId
 	  FROM Invoice
	 WHERE Invoice.employeeId = @id
	   AND Invoice.invoiceDate <= @endDate
END
   
OPEN customer_cur

FETCH NEXT FROM customer_cur
INTO @customerId

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

		  -- 姓名
		    
                  -- 单位名称
		    SELECT @customerName = customerName
		      FROM Customer
		     WHERE customerId = @customerId	
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 上月应收款
		    SELECT @lastAR = sum(Invoice.invoiceMoney - MoneyFlow.lmMoney)
		      FROM Invoice,MoneyFlow
		     WHERE Invoice.invoiceId = MoneyFlow.invoiceId
		       AND Invoice.receiptType = 0
		       AND Invoice.deptId = @id		       
		       AND Invoice.customerId = @customerId
		       AND Invoice.invoiceDate < @beginDate
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    
		    
                  -- 上月应付款
		    SELECT @lastAP = sum(Invoice.invoiceMoney - MoneyFlow.lmMoney)
		      FROM Invoice,MoneyFlow
		     WHERE Invoice.invoiceId = MoneyFlow.invoiceId
		       AND Invoice.receiptType = 2
		       AND Invoice.deptId = @id		       
		       AND Invoice.customerId = @customerId
		       AND Invoice.invoiceDate < @beginDate
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    

                  -- 本月应收款
		    SELECT @thisAR = sum(Invoice.invoiceMoney - MoneyFlow.lmMoney)
		      FROM Invoice,MoneyFlow
		     WHERE Invoice.invoiceId = MoneyFlow.invoiceId
		       AND Invoice.receiptType = 0
		       AND Invoice.deptId = @id		       
		       AND Invoice.customerId = @customerId
		       AND Invoice.invoiceDate >= @beginDate
		       AND Invoice.invoiceDate <= @endDate		       
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    

                  -- 本月应付款
		    SELECT @thisAP = sum(Invoice.invoiceMoney - MoneyFlow.lmMoney)
		      FROM Invoice,MoneyFlow
		     WHERE Invoice.invoiceId = MoneyFlow.invoiceId
		       AND Invoice.receiptType = 2
		       AND Invoice.deptId = @id		       
		       AND Invoice.customerId = @customerId
		       AND Invoice.invoiceDate >= @beginDate
		       AND Invoice.invoiceDate <= @endDate		       
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    		    

                  -- 本月增减
		    SELECT @balance = (@thisAR - @thisAP) - (@lastAR - @lastAP);
		    
                  -- 最后发生日期
		    SELECT @lmDate = max(MoneyFlow.lmDate)
		      FROM MoneyFlow,Invoice
		     WHERE MoneyFlow.invoiceId = Invoice.invoiceId
		       AND Invoice.deptId = @id
		       AND Invoice.customerId = @customerId		       	       		     
		       AND MoneyFlow.lmDate <= @endDate		       
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    		    
		    
		    
       END	            


       IF  @idType = 1   -- 个人编码
       BEGIN
       
		  -- 部门名称
		    SELECT @deptName = deptName
		      FROM Department,Employee
		     WHERE Department.deptId = Employee.deptId  
		       AND Employee.employeeId = @id
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

		  -- 姓名
		    SELECT @empName = empName
		    FROM Employee
		    WHERE employeeId = @id  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 
		    
                  -- 单位名称
		    SELECT @customerName = customerName
		      FROM Customer
		     WHERE customerId = @customerId	
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 上月应收款
		    SELECT @lastAR = sum(Invoice.invoiceMoney - MoneyFlow.lmMoney)
		      FROM Invoice,SalesSlip,MoneyFlow
		     WHERE Invoice.invoiceId = MoneyFlow.invoiceId
		       AND Invoice.receiptType = 0
		       AND Invoice.receiptId = SalesSlip.slipId
		       AND SalesSlip.proposer = @id		       		     
		       AND Invoice.customerId = @customerId
		       AND Invoice.invoiceDate < @beginDate
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    
		    
                  -- 上月应付款
		    SELECT @lastAP = sum(Invoice.invoiceMoney - MoneyFlow.lmMoney)
		      FROM Invoice,Purch,MoneyFlow
		     WHERE Invoice.invoiceId = MoneyFlow.invoiceId
		       AND Invoice.receiptType = 2
		       AND Invoice.receiptId = Purch.purchId
		       AND Purch.proposer = @id		       		     
		       AND Invoice.customerId = @customerId
		       AND Invoice.invoiceDate < @beginDate
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    

                  -- 本月应收款
		    SELECT @thisAR = sum(Invoice.invoiceMoney - MoneyFlow.lmMoney)
		      FROM Invoice,SalesSlip,MoneyFlow
		     WHERE Invoice.invoiceId = MoneyFlow.invoiceId
		       AND Invoice.receiptType = 0
		       AND Invoice.receiptId = SalesSlip.slipId
		       AND SalesSlip.proposer = @id		       		     
		       AND Invoice.customerId = @customerId
		       AND Invoice.invoiceDate >= @beginDate
		       AND Invoice.invoiceDate <= @endDate		       
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    

                  -- 本月应付款
		    SELECT @thisAP = sum(Invoice.invoiceMoney - MoneyFlow.lmMoney)
		      FROM Invoice,Purch,MoneyFlow
		     WHERE Invoice.invoiceId = MoneyFlow.invoiceId
		       AND Invoice.receiptType = 2
		       AND Invoice.receiptId = Purch.purchId
		       AND Purch.proposer = @id		       		     
		       AND Invoice.customerId = @customerId
		       AND Invoice.invoiceDate >= @beginDate
		       AND Invoice.invoiceDate <= @endDate		       
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    		    

                  -- 本月增减
		    SELECT @balance = (@thisAR - @thisAP) - (@lastAR - @lastAP);
		    
                  -- 最后发生日期
		    SELECT @lmDate = max(MoneyFlow.lmDate)
		      FROM MoneyFlow,Invoice,SalesSlip
		     WHERE MoneyFlow.invoiceId = Invoice.invoiceId
		       AND Invoice.receiptId = SalesSlip.slipId
		       AND SalesSlip.proposer = @id		       		     
		       AND Invoice.customerId = @customerId		       	       		     		       
		       AND MoneyFlow.lmDate <= @endDate		       
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    		    
		    
		    
       END	            
	            INSERT INTO #Tmp_ACurrentAccountReport(deptName,empName,customerName,lastAR,lastAP,thisAR,thisAP,balance,lmDate,beginDate,endDate)
	                                   VALUES(@deptName,@empName,@customerName,@lastAR,@lastAP,@thisAR,@thisAP,@balance,@lmDate,@beginDate,@endDate)
	FETCH NEXT FROM customer_cur
        INTO @customerId
        
END

CLOSE customer_cur

DEALLOCATE customer_cur

SELECT @countNum  = count(*)
FROM #Tmp_ACurrentAccountReport

UPDATE #Tmp_ACurrentAccountReport
   SET countNum = @countNum

SELECT deptName,empName,customerName,lastAR,lastAP,thisAR,thisAP,balance,lmDate,beginDate,endDate,countNum
  FROM #Tmp_ACurrentAccountReport
RETURN 0


















GO