'************************************************************************************
Function funcDatabase_uspLogHourlyData(vstrSQL_Connection,IntMachineID,IntStation,IntBatchID,intGoodCount,intScrapCount,LastHour,dtmPLCTimestamp,IntCaller)
'Description:	 Read 01 information from the database and put the result into a TAG
'Function Number: XX
'Parameters:
'External Variables:
'Comments:
'			1 - Used as my Template ADODB Communications
'History:
'	20 Dec 2005 - Creation of the Function by Alan Cannon

	'Variable Declaration
	Dim vobjADOConnection		' ADO Connection object
	Dim vobjADOCmd					' ADO Command object
	Dim oResultCode					' oReturn Code from the Stored procedure
	Dim oResultText					' oReturn text from the Stored Procedure
	Dim vdatetime							' DateTime Calculation
	Const adVarChar = 200
	Const adInteger = 3
	Const adDBTimeStamp =135
	Const adParamInput = &H0001
	Const adParamOutput = &H0002
	Const adCmdStoredProc = 4

	' Setup local Variables defaults
		oResultCode = 0
		oResultText = ""
	'Inform the Begining of the Function Execution
		funcLog 1,"uspLogHourlyData","Execution Start"

	'Initialise the ADO Connection
		Set vobjADOConnection = CreateObject("ADODB.Connection")	
		Set vobjADOCmd = CreateObject("ADODB.Command")
	'Inform the action
		funcLog 1,"uspLogHourlyData","Connecting with the Database"	
	'Enable Execution Error Tratement
		On Error Resume Next	 	 	 	 	 	

	'Open the Local Database Server	
		vobjADOConnection.Open vstrSQL_Connection
	
	'Error and Information Tratement
		If (err.number <> 0) Then
			'Inform Database Communication Error
				funcLog err.number,"uspLogHourlyData ", CStr(err.description)+"-"+CStr("Open Connection Error")
			'Disable Execution Error Tratement
				On Error Goto 0
    				vobjADOConnection.Close
			    	Set vobjADOConnection = Nothing				
				Exit Function
		End If

	'Enable Execution Error Tratement
		On Error Resume Next	
    	vobjADOCmd.ActiveConnection = vobjADOConnection
    	vobjADOCmd.CommandText = "uspLogHourlyData" 'Stored Procedure Name Here 
    	vobjADOCmd.CommandType = adCmdStoredProc 

    'Add Input Parameters
    	vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@intMachine", adInteger, adParamInput, 8, intMachineID)
    	vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@intStation", adInteger, adParamInput, 8, intStation)  
    	vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@BatchID", adInteger, adParamInput, 8, IntBatchID)      	
    	vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@intGoodCount", adInteger, adParamInput, 8, intGoodCount)
    	vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@intScrapCount", adInteger, adParamInput, 8, intScrapCount)    
    	vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@intLastHour", adInteger, adParamInput, 8, LastHour)      	  	
      vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@dtmPLCTimestamp", adDBTimeStamp, adParamInput, 30, dtmPLCTimestamp)
  
    'Add Output Parameters   
    	vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@intResultCode", adInteger, adParamOutput, 8, 0)
    	vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@strResultText", adVarChar, adParamOutput, 255, 0)   	     	
     On Error Resume Next      
    'Execute the function
    	vobjADOCmd.Execute
    
	'Error and Information Tratement
		If (err.number <> 0) Then
			'Inform Database Communication Error
				funcLog err.number,"uspLogHourlyData ", CStr(err.description)+"-"+CStr("Execute Error")
			'Disable Execution Error Tratement
				On Error Goto 0
				    	vobjADOConnection.Close
    					Set vobjADOConnection = Nothing
				Exit Function
		End If

	'Enable Execution Error Treatment and Parameter Closing Statements
		On Error Resume Next   
				oResultCode =  vobjADOCmd.Parameters("@intResultCode")
				oResultText = vobjADOCmd.Parameters("@strResultText")
				funcLog 1,"uspLogHourlyData", "Results Received Was [" +CStr(oResultCode)+"-"+CStr(oResultText)+"]"
    	vobjADOConnection.Close
    	Set vobjADOConnection = Nothing
    	$StoreHourlyLogCode[IntCaller] = oResultCode 
    
	'Inform the End of the Funciton Execution
		funcLog 1,"uspLogHourlyData","Execution Finish" 
End Function
 <><><><>  Sql Code   <><><><><>

-- =============================================
-- Author:		Alan Cannon
-- Create date: 13-March-2012
-- Modified date: 06-Nov-2022 - Modified for Pactiv Tables
-- Description:	Log Hourly Values On Event
-- =============================================
ALTER PROCEDURE [dbo].[uspLogHourlyData]
	-- Add the parameters for the stored procedure here
	-- Inputs
		@intMachine int,
		@intStation int,
		@BatchID int,
		@intGoodCount int,
		@intScrapCount int,
		@intLastHour int,
		@dtmPLCTimestamp datetime,
	
	--Outputs
		@intResultCode int output,			-- Result Code Standard Return Value 0 = fail, 1=pass
		@strResultText nvarchar(255) output	-- Text to return if we fail the call
AS
Declare @strTemp varchar(100)
Declare @Year int, @Month int, @Day int, @intHourTest int
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Set @intResultCode = 0
	Set @strResultText = ''

    -- Insert statements for procedure here
	begin try
		-- Verify that all required parameters were provided
		If IsNull(@intMachine,0) = 0 RAISERROR(51001,16,1,'Machine')			--Parameter %s not supplied.
		If Isnull(@intStation,0) = 0 RAISERROR(51001,16,1,'Station')			--Parameter %s not supplied.
		If @dtmPLCTimestamp is Null RAISERROR(51001,16,1,'PLCTimestamp')		--Parameter %s not supplied.
		--if IsNull(@intLastHour,0)=0 RAISERROR(51001,16,1,'Hour to Log')			--Parameter %s not supplied.

		-- Verify that the Machine exists
		if @intMachine not in (select Machine from StatsStationDefinition where Machine=@intMachine)
			begin
				set @strTemp='Machine ='+LTRIM(str(@intMachine))				--Parameter %s is invalid.
				RAISERROR(51002,16,1,@strTemp)
		    end
		-- Verify that the station exists
		if @intStation not in (select StationID from StatsStationDefinition where Machine=@intMachine)
			begin
				set @strTemp='Station='+LTRIM(str(@intStation))					--Parameter %s is invalid.
				RAISERROR(51002,16,1,@strTemp)
		    end
		-- Parse the DateTime Elements
		Set @Year = DatePart(Year,@dtmPLCTimestamp)
		Set @Month = DatePart(Month,@dtmPLCTimestamp)
		Set @Day = DatePart(Day,@dtmPLCTimestamp)

		-- Test if the Requesting Hour Exisit Before inserting another Record, Else Just Update the Hour
		Set @intHourTest = (Select [UID] from [dbo].[tblProdCounts_PkgStations] Where MachineID = @intMachine and BatchId = @BatchID and [Hour] = @intLastHour And datepart(Year,[TimeStamp])= @Year and datepart(Month,[TimeStamp])= @Month AND datepart(Day,[TimeStamp])= @Day)
		If IsNull(@intHourTest,0)=0 Set @intHourTest = 0

		If @intHourTest >0
		Begin 
			Update [dbo].[tblProdCounts_PkgStations] Set [GoodCount] =@intGoodCount
			,[ScrapCount]=@intScrapCount
			Where [UID] = @intHourTest
		END

		If @intHourTest = 0 
			Begin
				INSERT INTO [dbo].[tblProdCounts_PkgStations]
					([BatchId],[MachineID],[StationID],[Year],[Month],[Day],[Hour],[GoodCount],[ScrapCount],[TimeStamp])
				VALUES
					(@BatchID,@intMachine,@intStation,@Year,@Month,@Day,@intLastHour,@intGoodCount,@intScrapCount,@dtmPLCTimestamp)
			End
		-- All Passed Set the oResult to the Caller
		Set @intResultCode = 1

	end try

	begin catch
		-- Set return values
		select @intResultCode=error_number(), @strResultText=error_message()

		-- Prior to logging message we need to roll back any started 
		-- transactions
		if (xact_state()) <> 0
			rollback transaction

		-- Insert message into the error log
	    insert into tblErrorLog
			(MsgDate, 
			 MsgNumber, 
			 MsgText, 
			 MsgSeverity, 
			 MsgProcedure)
			values
			(getdate(),
			 error_number(),
			 error_message(),
			 error_severity(),
			 error_procedure())
	end catch


END