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