ALTER TRIGGER [dbo].[Trigger_Plant_Device_WR01] ON [be4e_weiden].[dbo].[Data_Table_000004] AFTER INSERT,UPDATE NOT FOR REPLICATION AS 
BEGIN 
   IF (SELECT VALUE FROM be4e_weiden.DBO.xxx_Plant_Device_Properties WHERE Value_Key='Stop_Trigger')=1 BEGIN RETURN END
    
   DECLARE @OLD_Date_and_Time DATETIME 
   DECLARE @Date_and_Time DATETIME 
   DECLARE @Time_Zone_Offset INT
   DECLARE @datePTZ DATETIME
   DECLARE @utcDayStart DATETIME
   DECLARE @logDate DATETIME
   DECLARE @fixedYield BIGINT 
   SET @Date_and_Time=(SELECT TOP 1 Date_and_Time FROM INSERTED) 
   SET @Time_Zone_Offset =(SELECT Value FROM xxx_Plant_Device_Properties WHERE Value_Key='Time_Zone_Offset') 
   
   -- filter yield for faulty values
   IF OBJECT_ID('COMMONDB.dbo.log_yield_changes') IS NOT NULL BEGIN
		print 'search log entry: ' + convert(varchar, @Date_and_Time, 20)
		SET @logDate = (SELECT TOP 1 date FROM COMMONDB.dbo.log_yield_changes WHERE date=@Date_and_Time AND db='be4e_weiden')
		
		print 'logDate: ' + convert(varchar, @logDate, 20)
   END
   ELSE BEGIN 
	print 'no log yield changes object found'
   END
   
   IF @logDate IS NULL BEGIN
     EXECUTE COMMONDB.dbo.FILTER_INVERTER_YIELD 
	         @database = N'be4e_weiden',
			 @udid = 'Data_Table_000004',
		     @starttime = @Date_and_Time
	 SET @fixedYield = (SELECT COL_SR_196628 FROM dbo.Data_Table_000004 WHERE Date_and_Time = @Date_and_Time)
	 --print 'fixed Yield: ' + cast(@fixedYield AS varchar(20))
	 
   END
   --- 
   DELETE FROM xxx_Last_Data WHERE Table_Name='Data_Table_000004'
   
   DECLARE @OLD_Value_COL_SR_196620_P_AC bigint 
   DECLARE @NEW_Value_COL_SR_196620_P_AC bigint 
   SET @NEW_Value_COL_SR_196620_P_AC=(SELECT TOP 1 COL_SR_196620 FROM Data_Table_000004 WHERE Date_and_Time=@Date_and_Time) 
   IF (SELECT 1 FROM xxx_Plant_Device_Properties WHERE Value_Key='Plant_Device_Rebuild' AND Value=0) IS NOT NULL 
   BEGIN 
      SET @OLD_Date_and_Time=(SELECT TOP 1 Date_and_Time FROM DELETED) 
      SET @OLD_Value_COL_SR_196620_P_AC=(SELECT TOP 1 COL_SR_196620 FROM DELETED) 
      IF (SELECT 1 FROM xxx_Plant_Device WHERE Date_and_Time=@OLD_Date_and_Time) IS NOT NULL 
      BEGIN 
         UPDATE xxx_Plant_Device SET Inverter_Power_AC=Inverter_Power_AC-@OLD_Value_COL_SR_196620_P_AC WHERE Date_and_Time=@OLD_Date_and_Time 
      END 
   END 
   SET @Date_and_Time=(SELECT TOP 1 Date_and_Time FROM INSERTED) 
   IF (SELECT 1 FROM xxx_Plant_Device WHERE Date_and_Time=@Date_and_Time) IS NOT NULL 
   BEGIN 
       UPDATE xxx_Plant_Device SET Inverter_Power_AC=ISNULL(Inverter_Power_AC,0)+@NEW_Value_COL_SR_196620_P_AC WHERE Date_and_Time=@Date_and_Time 
       DELETE FROM xxx_Last_Data WHERE 
         Date_and_Time=@OLD_Date_and_Time AND 
         Table_Name='Data_Table_000004' AND 
         Value_Type='COL_SR_196620' AND 
         Value_Name='Inverter_Power_AC' 
   END
   ELSE
   BEGIN
       INSERT INTO xxx_Plant_Device (Date_and_Time,Inverter_Power_AC) VALUES(@Date_and_Time,@NEW_Value_COL_SR_196620_P_AC)
   END
   INSERT INTO xxx_Last_Data (Date_and_Time,Table_Name,Value_Value,Value_Type,Value_Name) VALUES(@Date_and_Time,'Data_Table_000004',@NEW_Value_COL_SR_196620_P_AC,'COL_SR_196620','Inverter_Power_AC') 
   DECLARE @OLD_Value_COL_SR_196626_P_DC bigint 
   DECLARE @NEW_Value_COL_SR_196626_P_DC bigint 
   SET @NEW_Value_COL_SR_196626_P_DC=(SELECT TOP 1 COL_SR_196626 FROM Data_Table_000004 WHERE Date_and_Time=@Date_and_Time) 
   IF (SELECT 1 FROM xxx_Plant_Device_Properties WHERE Value_Key='Plant_Device_Rebuild' AND Value=0) IS NOT NULL 
   BEGIN 
      SET @OLD_Date_and_Time=(SELECT TOP 1 Date_and_Time FROM DELETED) 
      SET @OLD_Value_COL_SR_196626_P_DC=(SELECT TOP 1 COL_SR_196626 FROM DELETED) 
      IF (SELECT 1 FROM xxx_Plant_Device WHERE Date_and_Time=@OLD_Date_and_Time) IS NOT NULL 
      BEGIN 
         UPDATE xxx_Plant_Device SET Inverter_Power_DC=Inverter_Power_DC-@OLD_Value_COL_SR_196626_P_DC WHERE Date_and_Time=@OLD_Date_and_Time 
      END 
   END 
   SET @Date_and_Time=(SELECT TOP 1 Date_and_Time FROM INSERTED)
   
   IF (SELECT 1 FROM xxx_Plant_Device WHERE Date_and_Time=@Date_and_Time) IS NOT NULL 
   BEGIN 
       UPDATE xxx_Plant_Device SET Inverter_Power_DC=ISNULL(Inverter_Power_DC,0)+@NEW_Value_COL_SR_196626_P_DC WHERE Date_and_Time=@Date_and_Time 
       DELETE FROM xxx_Last_Data WHERE 
         Date_and_Time=@OLD_Date_and_Time AND 
         Table_Name='Data_Table_000004' AND 
         Value_Type='COL_SR_196626' AND 
         Value_Name='Inverter_Power_DC' 
   END 
   ELSE 
   BEGIN 
       INSERT INTO xxx_Plant_Device (Date_and_Time,Inverter_Power_DC) VALUES(@Date_and_Time,@NEW_Value_COL_SR_196626_P_DC) 
   END 
   INSERT INTO xxx_Last_Data (Date_and_Time,Table_Name,Value_Value,Value_Type,Value_Name) VALUES(@Date_and_Time,'Data_Table_000004',@NEW_Value_COL_SR_196626_P_DC,'COL_SR_196626','Inverter_Power_DC') 
   --##########################################
   --###  E_TODAY calculation from E_TOTAL  ###
   --##########################################
   SET @Date_and_Time=(SELECT TOP 1 Date_and_Time FROM INSERTED) 
   DECLARE @NEW_Value_COL_E_TODAY_CALC_E_TODAY BIGINT
   IF @fixedYield IS NULL BEGIN
	  SET @NEW_Value_COL_E_TODAY_CALC_E_TODAY = (SELECT COL_SR_196628 FROM INSERTED)
   END
   ELSE BEGIN
	  SET @NEW_Value_COL_E_TODAY_CALC_E_TODAY = @fixedYield
	  print 'using fixed yield for @NEW_Value_COL_E_TODAY_CALC_E_TODAY'
   END  
   -- get date in plant tz
   SET @datePTZ = (SELECT webportal.dbo.GET_TZ_DATE_FROM_UTC_DATE(CONVERT(varchar,@Date_and_Time,20), @Time_Zone_Offset))
   SET @utcDayStart = (SELECT webportal.dbo.GET_UTC_DATE_FROM_TZ_DATE(DATEADD(hour, 0, DATEDIFF(DAY, 0, @datePTZ)), @Time_Zone_Offset));
   
   IF (@NEW_Value_COL_E_TODAY_CALC_E_TODAY) IS NULL 
   BEGIN
      --print 'new etoday is null'
      --IF (SELECT COUNT(Date_and_Time) FROM Data_Table_000004 
	--	   WHERE Date_and_Time>@utcDayStart 
	--	     AND Date_and_Time<webportal.dbo.GET_UTC_TIME(CONVERT(VARCHAR(20),@Date_and_Time,104)+' 23:59:59',@Time_Zone_Offset) 
	--	     AND COL_SR_196628 IS NOT NULL)=0 
    --  BEGIN
    --     print 'count = 0'
    --     print convert(varchar, @utcDayStart, 20)
    --     print convert(varchar, @Date_and_Time, 20)
         SET @NEW_Value_COL_E_TODAY_CALC_E_TODAY=(SELECT MAX (COL_E_TODAY_CALC) 
												  FROM Data_Table_000004 
												  WHERE Date_and_Time >= @utcDayStart 
												    AND Date_and_Time < @Date_and_Time)
		 IF  @NEW_Value_COL_E_TODAY_CALC_E_TODAY IS NULL BEGIN
			SET @NEW_Value_COL_E_TODAY_CALC_E_TODAY = 0
		 END
    --   END
    --  ELSE
    --  BEGIN
    --     SET @NEW_Value_COL_E_TODAY_CALC_E_TODAY=0 
    --  END
   END
   ELSE
   BEGIN
    
    --print convert(varchar, @utcDayStart, 20)
    SET @NEW_Value_COL_E_TODAY_CALC_E_TODAY = @NEW_Value_COL_E_TODAY_CALC_E_TODAY - (SELECT min((T1.COL_SR_196628)) 
																				     FROM Data_Table_000004 AS T1 
																					 WHERE T1.Date_and_Time >= @utcDayStart
																					   AND T1.Date_and_Time < @Date_and_Time
																					   AND T1.COL_SR_196628 IS NOT NULL)
		IF @NEW_Value_COL_E_TODAY_CALC_E_TODAY IS NULL BEGIN
			SET @NEW_Value_COL_E_TODAY_CALC_E_TODAY = 0
		END 
   END
   UPDATE Data_Table_000004 Set COL_E_TODAY_CALC = @NEW_Value_COL_E_TODAY_CALC_E_TODAY WHERE Date_and_Time = @Date_and_Time 
   --#########################################
   --###                 END               ###
   --#########################################
   DECLARE @OLD_Value_COL_E_TODAY_CALC_E_TODAY BIGINT 
   IF (SELECT 1 FROM xxx_Plant_Device_Properties WHERE Value_Key='Plant_Device_Rebuild' AND Value=0) IS NOT NULL 
   BEGIN 
      SET @OLD_Date_and_Time=(SELECT TOP 1 Date_and_Time FROM DELETED) 
      SET @OLD_Value_COL_E_TODAY_CALC_E_TODAY=(SELECT TOP 1 COL_E_TODAY_CALC FROM DELETED) 
      IF (SELECT 1 FROM xxx_Plant_Device WHERE Date_and_Time=@OLD_Date_and_Time) IS NOT NULL 
      BEGIN 
         UPDATE xxx_Plant_Device SET Energy_Yield=Energy_Yield-@OLD_Value_COL_E_TODAY_CALC_E_TODAY WHERE Date_and_Time=@OLD_Date_and_Time 
      END 
   END 
   SET @Date_and_Time=(SELECT TOP 1 Date_and_Time FROM INSERTED) 
   IF (SELECT 1 FROM xxx_Plant_Device WHERE Date_and_Time=@Date_and_Time) IS NOT NULL 
   BEGIN 
       UPDATE xxx_Plant_Device SET Energy_Yield=ISNULL(Energy_Yield,0)+@NEW_Value_COL_E_TODAY_CALC_E_TODAY WHERE Date_and_Time=@Date_and_Time 
       DELETE FROM xxx_Last_Data WHERE 
         Date_and_Time=@OLD_Date_and_Time AND 
         Table_Name='Data_Table_000004' AND 
         Value_Type='COL_E_TODAY_CALC' AND 
         Value_Name='Energy_Yield' 
   END 
   ELSE 
   BEGIN 
       INSERT INTO xxx_Plant_Device (Date_and_Time,Energy_Yield) VALUES(@Date_and_Time,@NEW_Value_COL_E_TODAY_CALC_E_TODAY) 
   END 
   INSERT INTO xxx_Last_Data (Date_and_Time,Table_Name,Value_Value,Value_Type,Value_Name) VALUES(@Date_and_Time,'Data_Table_000004',@NEW_Value_COL_E_TODAY_CALC_E_TODAY,'COL_E_TODAY_CALC','Energy_Yield') 
    DECLARE @Inverter_Power_AC real 
    DECLARE @Radiation_Cell_Tilt real 
    DECLARE @DC_Power_Installed REAL 
    SET @Inverter_Power_AC=(SELECT Inverter_Power_AC FROM xxx_Plant_Device WHERE Date_and_Time=@Date_and_Time) 
    SET @DC_Power_Installed=(SELECT Value FROM xxx_Plant_Device_Properties WHERE Value_Key='DC_Power_Installed') 
    SET @Radiation_Cell_Tilt=(SELECT Radiation_Cell_Tilt FROM xxx_Plant_Device WHERE Date_and_Time=@Date_and_Time) 
    UPDATE xxx_Plant_Device SET Performance_Ratio=(@Inverter_Power_AC/(@DC_Power_Installed*(@Radiation_Cell_Tilt+0.00000000001) / 1000)*100) WHERE Date_And_Time=@Date_and_Time 
    DECLARE @Inverter_Power_DC real 
    SET @Inverter_Power_DC=(SELECT Inverter_Power_DC FROM xxx_Plant_Device WHERE Date_and_Time=@Date_and_Time) 
    UPDATE xxx_Plant_Device SET INV_Efficency=@Inverter_Power_AC/(@Inverter_Power_DC+0.00000000001) *100 WHERE Date_And_Time=@Date_and_Time 
END 
