DROP PROCEDURE [dbo].[job_StockCosts_StockTake_Weighted];
DROP PROCEDURE [dbo].[job_StockCosts_StockTake_Fixed];
DROP PROCEDURE [dbo].[job_StockCosts_StockTake_FIFO];
GO

CREATE PROCEDURE [dbo].[job_StockCosts_StockTake_Weighted] ( @StockTakeDate DATETIME )                                                                                                 
AS                                                                                                                                                                             
BEGIN                                                                                                                                                                          
	-- Version: 1.0.0.1                                                                                                                                                           
	-- Author : bvonfintel                                                                                                                                                        
	-- Date   : 2017/12/20                                                                                                                                                        
                                                                                                                                                                               
 UPDATE STOCK1                                                                                                                                                                 
    SET TotalLessWastage = ROUND( 0 + ISNULL( STOCK1.Prepped, 0 ) + ( ISNULL( STOCK1.UnPrepped, 0 )  - ISNULL( STOCK1.UnPrepped, 0 ) * ISNULL( STOCK.Wastage, 0 ) / 100 ), 4 ), 
        Prepped          = ISNULL( STOCK1.Prepped, 0 ),                                                                                                                         
        Unprepped        = ISNULL( STOCK1.Unprepped, 0 )                                                                                                                        
	  FROM STOCK1                                                                                                                                                                 
		   JOIN STOCK ON STOCK.STOCKNO = STOCK1.KeyStockNo                                                                                                                          
	WHERE STOCK1.Date = @StockTakeDate;                                                                                                                                           
                                                                                                                                                                               
	UPDATE STOCK1                                                                                                                                                                 
	   SET BulkUnitPrice	= ROUND( COALESCE( CASE CALC.Quantity WHEN 0 THEN 0 ELSE CALC.TotalPrice / CALC.Quantity END, STOCK.UNITCOST ), 4 ),                                    
		   TotalStockValue	= ROUND( COALESCE( CASE CALC.Quantity WHEN 0 THEN 0 ELSE CALC.TotalPrice / CALC.Quantity END, STOCK.UNITCOST ) * TotalLessWastage, 4 )                  
	  FROM STOCK1                                                                                                                                                                 
		   JOIN STOCK ON STOCK.STOCKNO = STOCK1.KeyStockNo                                                                                                                          
		   LEFT JOIN ( SELECT S.Date AS StockTakeDate, C.STOCKNO, COUNT(1) AS NumTrans, SUM( Qty ) AS Quantity, SUM( Qty * UnitPrice ) AS TotalPrice                                
						  FROM STOCK1 S                                                                                                                                                     
							   JOIN vw_StockCostCalculation C ON C.STOCKNO = S.KeyStockNo AND                                                                                                 
																 C.StockLocation = S.fkStockLocationID AND                                                                                                      
																 C.DATE BETWEEN DATEADD( DAY, -30, S.Date ) AND S.Date                                                                                          
						 GROUP BY S.Date, C.STOCKNO                                                                                                                                         
		   ) AS CALC ON CALC.STOCKNO = STOCK1.KeyStockNo AND                                                                                                                        
						CALC.StockTakeDate = STOCK1.Date                                                                                                                                    
	WHERE STOCK1.Date = @StockTakeDate;                                                                                                                                           
END;                                                                                                                                                          

GO

CREATE PROCEDURE [dbo].[job_StockCosts_StockTake_Fixed] ( @StockTakeDate DATETIME )                                               
AS                                                                                                                        
BEGIN                                                                                                                     
	-- Version: 1.0.0.1                                                                                                      
	-- Author : bvonfintel                                                                                                   
	-- Date   : 2017/12/20                                                                                                   
                                                                                                                          
 UPDATE STOCK1                                                                                                                                                                 
    SET TotalLessWastage = ROUND( 0 + ISNULL( STOCK1.Prepped, 0 ) + ( ISNULL( STOCK1.UnPrepped, 0 )  - ISNULL( STOCK1.UnPrepped, 0 ) * ISNULL( STOCK.Wastage, 0 ) / 100 ), 4 ), 
        Prepped          = ISNULL( STOCK1.Prepped, 0 ),                                                                                                                         
        Unprepped        = ISNULL( STOCK1.Unprepped, 0 )                                                                                                                        
	  FROM STOCK1                                                                                                                                                                 
		   JOIN STOCK ON STOCK.STOCKNO = STOCK1.KeyStockNo                                                                                                                          
	WHERE STOCK1.Date = @StockTakeDate;                                                                                                                                           
                                                                                                                          
	UPDATE STOCK1                                                                                                            
	   SET BulkUnitPrice	= ROUND( COALESCE( CALC.LastUnitPrice, STOCK.UNITCOST ), 4 ),                                      
		   TotalStockValue	= ROUND( ROUND( COALESCE( CALC.LastUnitPrice, STOCK.UNITCOST ), 4 ) * TotalLessWastage, 4 )        
	  FROM STOCK1                                                                                                            
		   JOIN STOCK ON STOCK.STOCKNO = STOCK1.KeyStockNo                                                                     
		   LEFT JOIN ( SELECT S.Date, S.KeyStockNo, S.BulkUnitPrice,                                                           
							   ( SELECT TOP 1 UNITPRICE                                                                                  
								   FROM vw_StockCostCalculation C                                                                          
								  WHERE C.STOCKNO = S.KeyStockNo                                                                           
									AND C.StockLocation = S.fkStockLocationID                                                                
									AND C.Event IN ( 'P', 'M' )                                                                          
									AND C.DATE < S.Date                                                                                      
								  ORDER BY C.DATE DESC                                                                                     
								) AS LastUnitPrice                                                                                         
						  FROM STOCK1 S ) AS CALC ON CALC.Date = STOCK1.Date AND                                                       
													 CALC.KeyStockNo = STOCK1.KeyStockNo                                                             
                                                                                                                          
	WHERE STOCK1.Date = @StockTakeDate;                                                                                      
END;                                                                                                               

GO

CREATE PROCEDURE [dbo].[job_StockCosts_StockTake_FIFO] ( @StockTakeDate DATETIME )                                                                             
AS                                                                                                                                                     
BEGIN                                                                                                                                                  
	-- Version: 1.0.0.1                                                                                                                                   
	-- Author : bvonfintel                                                                                                                                
	-- Date   : 2017/12/20                                                                                                                                
                                                                                                                                                       
 UPDATE STOCK1                                                                                                                                                                 
    SET TotalLessWastage = ROUND( 0 + ISNULL( STOCK1.Prepped, 0 ) + ( ISNULL( STOCK1.UnPrepped, 0 )  - ISNULL( STOCK1.UnPrepped, 0 ) * ISNULL( STOCK.Wastage, 0 ) / 100 ), 4 ), 
        Prepped          = ISNULL( STOCK1.Prepped, 0 ),                                                                                                                         
        Unprepped        = ISNULL( STOCK1.Unprepped, 0 )                                                                                                                        
	  FROM STOCK1                                                                                                                                                                 
		   JOIN STOCK ON STOCK.STOCKNO = STOCK1.KeyStockNo                                                                                                                          
	WHERE STOCK1.Date = @StockTakeDate;                                                                                                                                           
                                                                                                                                                              
                                                                                                                                                              
	SELECT STOCK1.DATE AS StocktakeDate, TotalLessWastage, TotalStockValue,  a.*                                                                          
	   INTO #tmp_stock_take_fifo_st                                                                                                                       
	   FROM STOCK1                                                                                                                                        
			JOIN vw_StockCostCalculation a ON a.STOCKNO			= STOCK1.KeyStockNo AND                                                                           
											  a.StockLocation	= STOCK1.fkStockLocationID AND                                                                                  
											  a.DATE BETWEEN DATEADD( DAY, -180,  STOCK1.Date ) AND DATEADD( DAY, 1, STOCK1.Date )                                            
	 WHERE STOCK1.Date = @StockTakeDate;                                                                                                                  
                                                                                                                                                       
  CREATE INDEX IDX_TMP_1 ON #tmp_stock_take_fifo_st ( STOCKNO, StockLocation, DATE );                                                                  
                                                                                                                                                       
	SELECT *,                                                                                                                                             
	       COALESCE( ( SELECT CASE WHEN SUM( Qty ) < 0 THEN SUM( Qty ) ELSE 0 END                                                                         
	                     FROM #tmp_stock_take_fifo_st b                                                                                                   
	                    WHERE b.STOCKNO = a.STOCKNO                                                                                                       
	                      AND b.StockLocation = a.StockLocation                                                                                           
	                      AND b.DATE > a.DATE                                                                                                             
	                      AND b.DATE <= ( SELECT MAX( DATE )                                                                                              
	                                        FROM #tmp_stock_take_fifo_st c                                                                                
	                                       WHERE c.STOCKNO = b.STOCKNO                                                                                    
	                                         AND c.StockLocation = b.StockLocation                                                                        
	                                         AND c.QTY < 0 )  ),0 ) AS Neg                                                                                
                                                                                                                                                       
	  INTO #tmp_stock_take_fifo                                                                                                                           
	  FROM #tmp_stock_take_fifo_st a;                                                                                                                     
                                                                                                                                                       
	 CREATE INDEX IDX_TMP_2 ON #tmp_stock_take_fifo ( STOCKNO, StockLocation, DATE );                                                                     
                                                                                                                                                       
	-- Update the StockTake values based on the                                                                                                           
	UPDATE STOCK1                                                                                                                                         
	   SET TotalStockValue = ISNULL( ROUND( PrevValue + ( CALC.TotalLessWastage - PrevQty ) * UnitPrice , 4 ), STOCK1.TotalLessWastage * STOCK.UNITCOST ) 
	  FROM STOCK1                                                                                                                                         
		   JOIN STOCK ON STOCK.STOCKNO = STOCK1.KeyStockNo                                                                                                  
		   LEFT JOIN                                                                                                                                        
	  (                                                                                                                                                   
	SELECT a.*,                                                                                                                                           
		   COALESCE( ( SELECT SUM( CASE WHEN QTY < 0 THEN 0 ELSE QTY + Neg END )                                                                            
						 FROM #tmp_stock_take_fifo b                                                                                                                
						WHERE b.STOCKNO = a.STOCKNO                                                                                                                 
						  AND b.StockLocation = a.StockLocation                                                                                                     
						  AND b.DATE > a.DATE ) , 0 ) AS PrevQty,                                                                                                   
		   COALESCE( ( SELECT SUM( CASE WHEN QTY < 0 THEN 0 ELSE ( QTY + Neg ) * UnitPrice END )                                                            
						 FROM #tmp_stock_take_fifo b                                                                                                                
						WHERE b.STOCKNO = a.STOCKNO                                                                                                                 
						  AND b.StockLocation = a.StockLocation                                                                                                     
						  AND b.DATE > a.DATE ) , 0 ) AS PrevValue                                                                                                  
	  FROM #tmp_stock_take_fifo a                                                                                                                         
	 ) AS CALC ON CALC.STOCKNO = STOCK1.KeyStockNo AND                                                                                                    
				  CALC.StockLocation = STOCK1.fkStockLocationID AND                                                                                             
				  CALC.StocktakeDate = STOCK1.Date AND                                                                                                          
				  CALC.TotalLessWastage BETWEEN PrevQty AND                                                                                                     
				  PrevQty + Qty + Neg                                                                                                                           
	 WHERE STOCK1.Date = @StockTakeDate;                                                                                                                  
END;                                                                                                                                                   

GO


SELECT routine_name, LAST_ALTERED FROM information_schema.ROUTINES 
where ROUTINE_NAME LIKE '%stockcosts_stocktake%'

