Architecture Guide: Windows Forms, Generics, Auto-Mapper, Entity Framework, Framework Design, and many more..

USE [Demo_Main]
GO
/****** Object:  StoredProcedure [dbo].[InsertWeeklyOfMonthLeafWeight]    Script Date: 10/27/2010 10:47:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author		:	G.R.N.Gunathilake
-- Create date	:	10/18/2010 1:56 PM	
-- Description	:	This SP for Insert Weekly Weight Summary Data of Month into central Database
-- =============================================
ALTER PROCEDURE [dbo].[InsertWeeklyOfMonthLeafWeight]
--	(
--		@FactoryId AS Int,
--		@Year AS int
--	)
AS

BEGIN	
	SET NOCOUNT ON;	
	DECLARE @FactoryId as varchar(20)
	DECLARE @FactoryName as varchar(50)
	DECLARE @Year As Int
	DECLARE @Month as int	
	DECLARE @Week as int
	DECLARE @Weight as Float
	DECLARE @DisplayedWeight as Float
	DECLARE @GainWeight as Float
	DECLARE @ProjectedWeight as Float	
	DECLARE @ProcessedWeight as Float
	DECLARE @isWeekOfMonthWeightExist as int

	--BEGIN	
		DECLARE recWeeklyWeightSummary CURSOR FOR 
		SELECT FactoryId,FactoryName,Year,Month,Week
		,SUM(Weight),SUM(DisplayedWeight)
		,SUM(GainWeight),SUM(ProjectedWeight)
		,SUM(ProcessedWeight)
		FROM dbo.DailyLeafWeight
		GROUP BY
		FactoryId,FactoryName,Year,Month,Week
		
		OPEN recWeeklyWeightSummary

		FETCH NEXT FROM recWeeklyWeightSummary INTO 
			@FactoryId,@FactoryName,@Year,@Month, @Week,
			@Weight,@DisplayedWeight,@GainWeight,@ProjectedWeight,@ProcessedWeight
		
		WHILE (@@FETCH_STATUS=0)
			BEGIN
				SET @isWeekOfMonthWeightExist = (SELECT Count(*) FROM dbo.WeeklyLeafWeight 
										WHERE FactoryId = @FactoryId and FactoryName = @FactoryName and Year = @Year and Month = @Month and Week=@Week)
			
				IF 	@isWeekOfMonthWeightExist = 0 
					BEGIN	
						INSERT INTO dbo.WeeklyLeafWeight
						SELECT @FactoryId,@FactoryName,@Year,@Month,@Week,
								@Weight,@DisplayedWeight,@GainWeight,@ProjectedWeight,@ProcessedWeight								
					END
				IF 	@isWeekOfMonthWeightExist = 1 
					BEGIN
						UPDATE dbo.WeeklyLeafWeight SET 
							Weight = @Weight,
							DisplayedWeight = @DisplayedWeight,
							GainWeight = @GainWeight,
							ProjectedWeight = @ProjectedWeight,
							ProcessedWeight =0 
						WHERE FactoryId = @FactoryId and FactoryName = @FactoryName and Year = @Year and Month = @Month and Week = @Week
					END				
					

				FETCH NEXT FROM recWeeklyWeightSummary INTO 
						@FactoryId,@FactoryName,@Year,@Month,@Week,
						@Weight,@DisplayedWeight,@GainWeight,@ProjectedWeight,@ProcessedWeight		
			END
		CLOSE recWeeklyWeightSummary;
		DEALLOCATE recWeeklyWeightSummary;
	END
--END