gtav-src/tools_ng/techart/script/Ruby/Utils/CutsceneGrader/GradingStats.rb

#
#
# Author:: Mark Harrison-Ball <Mark.Harrison-Ball@rockstargames.com>
# Date:: 15 September 2013 (AP3)
# Purpose:
#			~ Reads Cutscene Info


#-----------------------------------------------------------------------------
# Uses
#-----------------------------------------------------------------------------

path = File.expand_path $0
path = File.dirname(path)

#Database
require "#{path}/../../Global/database/DatabaseConnection.rb"


# Multipliers
CHAR_FRAMES_PER_DAY = 0
WIDE_CAM_X = 0
MID_CAM_X = 0
HOURLY_PERCENTAGE = 0

# Default Project settings
PROJECTID = 1

# DATABASE CONSTANTS
DB_CHAR = 1
DB_PROP = 2
DB_VEH = 3

class GradingStats

    def initialize(g_Log)
      @g_Log = g_Log
      @dBconnection = DatabaseConnection.new(@g_Log)
    end

    
    # Update this for auotmation mode
    def generateCutStats( cutDefList  )
      #setProjectID(projName) # set our project id (hardcoded for now)   
      resetUpdate()      
      connectDB() 
        cutDefList.each do |cutDefInfo|
          sendCutInfoDB( cutDefInfo )            
        end  
      closeDB()
    end
    
   
    
    
    # Used to update Cutscene Concut Info
    def sendCutInfoDB(cutsceneDef)
      
      query = "
        DECLARE @UserID INT
        IF EXISTS(SELECT * FROM Users WHERE UserName='#{cutsceneDef.fileHeader.user}')   
          SET @UserID = (SELECT UserID FROM Users WHERE UserName='#{cutsceneDef.fileHeader.user}') 
        ELSE
        BEGIN
          INSERT INTO Users (UserName)
          VALUES ('#{cutsceneDef.fileHeader.user}')
          SET @UserID = @@IDENTITY
        END
      
      
        IF EXISTS(SELECT * FROM Cutscene WHERE CutsceneName='#{cutsceneDef.cutInfo.name}' AND ProjectID=#{cutsceneDef.project})
        UPDATE Cutscene 
          SET ProjectID=#{cutsceneDef.project},
              CutsceneName='#{cutsceneDef.cutInfo.name}',
              Shots=#{cutsceneDef.cutInfo.shots.count},
              Frames=#{cutsceneDef.cutInfo.frames},
              Duration=#{cutsceneDef.cutInfo.duration},
              Strand='#{cutsceneDef.cutInfo.missionStrand}',
              LastUpdated='#{cutsceneDef.fileHeader.time}',
              UserName='#{cutsceneDef.fileHeader.user}',
              Revision='#{cutsceneDef.fileHeader.revision}',
              Changelist='#{cutsceneDef.fileHeader.changelist}',
              UserID=@UserID
              
          WHERE CutsceneName='#{cutsceneDef.cutInfo.name}' AND ProjectID=#{cutsceneDef.project}
        ELSE
        BEGIN
          INSERT INTO Cutscene (ProjectID, CutsceneName, ShotlistID,  Shots, Frames, Duration, Strand, LastUpdated, UserName, Revision, Changelist, UserID )
          VALUES(#{cutsceneDef.project},
                  '#{cutsceneDef.cutInfo.name}',
                  NEWID(),
                  #{cutsceneDef.cutInfo.shots.count},
                  #{cutsceneDef.cutInfo.frames},
                  #{cutsceneDef.cutInfo.duration},
                  '#{cutsceneDef.cutInfo.missionStrand}',
                  '#{cutsceneDef.fileHeader.time}',
                  '#{cutsceneDef.fileHeader.user}',
                  '#{cutsceneDef.fileHeader.revision}',
                  '#{cutsceneDef.fileHeader.changelist}',
                  @UserID
          ) 
          
        END 
        
        
        IF (SELECT CutsceneTypeID FROM Cutscene WHERE CutsceneName='#{cutsceneDef.cutInfo.name}' AND ProjectID=#{cutsceneDef.project}) is null
          begin 
            INSERT INTO CutType (IsConcat, sectionTimeDuration, sectionMethodIndex ,Audio)
            VALUES ('#{cutsceneDef.cutInfo.isConcat}', #{cutsceneDef.cutInfo.sectionTimeDuration}, #{cutsceneDef.cutInfo.sectionMethodIndex}, '#{cutsceneDef.cutInfo.audio}')
            UPDATE Cutscene
              set CutsceneTypeID = @@IDENTITY
            WHERE CutsceneName='#{cutsceneDef.cutInfo.name}'
          end
          else
          begin
            update CutType
              set IsConcat = '#{cutsceneDef.cutInfo.isConcat}',
                sectionTimeDuration = #{cutsceneDef.cutInfo.sectionTimeDuration},
                sectionMethodIndex = #{cutsceneDef.cutInfo.sectionMethodIndex},
                Audio = '#{cutsceneDef.cutInfo.audio}'
            Where CutsceneTypeID = (SELECT CutsceneTypeID FROM Cutscene WHERE CutsceneName='#{cutsceneDef.cutInfo.name}' AND ProjectID=#{cutsceneDef.project})
          end
        
        
        
        
        
        
        
        SELECT ShotlistID FROM Cutscene WHERE CutsceneName='#{cutsceneDef.cutInfo.name}' AND ProjectID=#{cutsceneDef.project}
        
        
        
      "
      
      
      
      # We will def have a ID
      shotlistID = @dBconnection.read_data(query)['ShotlistID']
      #puts shotlistID
      #if shotlistID == nil
      #  puts 'AInt good'
      #  break
      #end
   
      # Delete our shot list order, must be a better way to track this 
      query ="DELETE FROM ShotParent
          WHERE ShotlistID='#{shotlistID}'
      "
      #puts query
      @dBconnection.read_data(query)
   
      # added Shot List reference
      cutsceneDef.cutInfo.shots.each_with_index do |part, index|  
      
        # Lets now add any new shot entries we have, else update the existing ones
        query = "
              DECLARE @ShotID INT
              DECLARE @CutID INT

              IF NOT EXISTS(SELECT * FROM Shots WHERE RelativePath='#{part.relativePath}')
              BEGIN
                BEGIN tran
                  INSERT INTO Shots (RelativePath, ShotPath, ShotName, TotalDuration, Frames, RangeStart, RangeEnd, FlagID, ErrorMsg ) 
                  Values ('#{part.relativePath}', '#{part.shotPath}','#{part.shotName}', #{part.duration},#{part.frames},#{part.iRangeStart},#{part.iRangeEnd},#{part.flags},'#{part.errorMsg}')
                  SET @ShotID = @@IDENTITY
                commit 
              END
              ELSE
              BEGIN                               
                UPDATE Shots
                  SET RelativePath='#{part.relativePath}',
                      ShotPath='#{part.shotPath}',
                      ShotName='#{part.shotName}',
                      TotalDuration=#{part.duration},
                      Frames=#{part.frames},
                      RangeStart=#{part.iRangeStart},
                      RangeEnd=#{part.iRangeEnd},
                      FlagID=#{part.flags},                      
                      ErrorMsg='#{part.errorMsg}'
                  WHERE ShotPath='#{part.shotPath}'
                  
              END
              
              -- Set Parent Shot Relationship
              SET @ShotID=(SELECT ShotID FROM Shots WHERE RelativePath='#{part.relativePath}')
              IF NOT EXISTS(SELECT * FROM ShotParent WHERE ShotID=@ShotID AND ShotlistID='#{shotlistID}')
              INSERT INTO ShotParent (ShotlistID, ShotID)
                VALUES ('#{shotlistID}',@ShotID)
                
              -- Set Shot position Information
              IF NOT EXISTS(SELECT * FROM ShotPosition WHERE ShotID=@ShotID)
              BEGIN 
                INSERT INTO ShotPosition (ShotID, vOffset_X, vOffset_Y, vOffset_Z, fRotation )
                Values (@ShotID, #{part.offset[0]}, #{part.offset[1]}, #{part.offset[2]}, #{part.rotation})
              END
              ELSE
              BEGIN
                UPDATE ShotPosition
                  SET vOffset_X=#{part.offset[0]},
                      vOffset_Y=#{part.offset[1]},
                      vOffset_Z=#{part.offset[2]},
                      fRotation=#{part.rotation}                  
                  WHERE ShotID=@ShotID
              END
              
              -- return SHot ID Value  
              SELECT @ShotID
        "
        #puts query
        
        shotID = @dBconnection.read_data(query)['']
       

        #SET @ShotID=(SELECT ShotID FROM Shots WHERE CutID=@CutID)
        
        
        
        # Remove any old references from our shotModels link
        query = "         
          DELETE FROM ShotModels
          WHERE ShotID='#{shotID}'

          DELETE FROM ObjectIDEvents
          WHERE ShotID='#{shotID}'
        "
        @dBconnection.read_data(query)
        
        # Add any new models and link to our shotID
        updateModelEntries(part, shotID)

        # Update our SHot Events
        updateEventEntries(part, shotID)
        


      end
      
    end
    
    def updateEventEntries(part, shotID)
      #puts part
      #puts part.pCutsceneEventList
      if part.pCutsceneEventList.count > 0
        part.pCutsceneEventList.each do |event|
          
          query = "
            INSERT INTO ObjectIDEvents
            VALUES
            (#{shotID}, #{event.fTime}, #{event.iEventId} ,#{event.iEventArgsIndex}, #{event.iObjectId})
          "
          #puts query
          @dBconnection.read_data(query)
        end
        
      end
      
    end
    
    # Update Our Models
    def updateModelEntries(part, shotID)
      if part.models.count > 0
        part.models.each do |model|
          #puts model.name
          query = "
            DECLARE @ModelID INT
            
            IF NOT EXISTS(SELECT Name FROM CutModel WHERE Name='#{model.name}')
            BEGIN
              INSERT INTO CutModel
              (StreamingName,  Handle, Name, Type )
              VALUES (
              '#{model.streamingName}', '#{model.handle}','#{model.name}','#{model.type}')
              SET  @ModelID = @@IDENTITY 
            END
              ELSE
            BEGIN
              SET @ModelID=(SELECT ModelID FROM CutModel WHERE Name='#{model.name}')   
            END
            
            
            -- Check composite primary key and make sure we only add unique entries..
            IF NOT EXISTS(SELECT * FROM ShotModels WHERE ShotID=#{shotID} AND ModelID=@ModelID)            
            INSERT INTO ShotModels
            VALUES
            (#{shotID}, @ModelID, #{model.objectID} ,1)
            ELSE
            UPDATE ShotModels
              SET [References]=(SELECT [REFERENCES] FROM ShotModels WHERE ShotID=#{shotID} and ModelID=@ModelID)+1
            WHERE ShotID=#{shotID} and ModelID=@ModelID            
            "
          #puts query
          @dBconnection.read_data(query)
        end
      end
    end
    
   
    

    # Set to use Triggers!!!!
    def updateGradingEntry(cutInfo)
      manDays = (cutInfo.length*cutInfo.characters.length)+
                (cutInfo.length*cutInfo.props.length)+
                (cutInfo.length*cutInfo.vehicles.length).to_f
      
      # Add New Entry
      query = "IF EXISTS(SELECT * FROM Cuts WHERE Name='#{cutInfo.name}' AND ProjectID=#{PROJECTID})
        UPDATE Cuts 
          SET Frames=#{cutInfo.length},
              Characters=#{cutInfo.characters.length}, 
              Props=#{cutInfo.props.length}, 
              Vehicles =#{cutInfo.vehicles.length},
              Error =#{cutInfo.error},
              [Strand Total] = #{manDays}
          WHERE Name='#{cutInfo.name}' AND ProjectID=#{PROJECTID}
        ELSE
        INSERT INTO Cuts (Name, Frames, Characters, Props, Vehicles, Error, ProjectID, [Strand Total])
        VALUES ('#{cutInfo.name}',
                    #{cutInfo.length},
                    #{cutInfo.characters.length},
                    #{cutInfo.props.length},
                    #{cutInfo.vehicles.length},
                    #{cutInfo.error},
                    #{PROJECTID},
                    #{manDays})
        "

      @dBconnection.read_data(query)    

    end
    

    
    # CHeck the Database to see if we need to run an update
    def requireUpdate()
      connectDB()
      result = nil
      begin
        query  = "SELECT * FROM RemoteUpdate WHERE [GradingRequest]='True'"
        result = @dBconnection.read_data( query )
      rescue => msg 
        @g_Log.error(msg)
      end
      closeDB()
      return result      
    end
    
    # Set the Database so RUn update to false 
    def resetUpdate()
      updatedTime = Time.new
      # 2013-10-05 00:00:00.000
      timeformat = updatedTime.strftime("%Y-%m-%d %H:%M:%S")
      connectDB()
      begin
        query  = "IF EXISTS(SELECT * FROM RemoteUpdate WHERE [GradingRequest]='True')
        UPDATE RemoteUpdate
          SET [GradingRequest]='False',
          [GradingDate]='#{timeformat}'"
        @dBconnection.read_data( query )
      rescue => msg 
        @g_Log.error(msg)
      end
      closeDB()
    end
    
    
    
    def connectDB()
      sourceDB = "gradingStats"
      server = "nycw-mhb-sql"
      @dBconnection.connect(server, sourceDB)
    end
    
    def closeDB()
      @dBconnection.close()
    end

end