#
#
# Author:: Mark Harrison-Ball <Mark.Harrison-Ball@rockstargames.com>
# Date:: 20 Februray 2013 (AP3)
# Purpose:
#			~ Parse Tracking Doc and upload to Database
#

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

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


class TrackingFileDef
    attr_reader :File
    attr_reader :HeaderInfo
    
    def initialize(file, headerInfo)
      @File = file
      @HeaderInfo = headerInfo      
    end

end

class TrackingDocUpdate
   def initialize(g_Log)
      @g_Log = g_Log
      @dBconnection = DatabaseConnection.new(@g_Log)
    end
    
    #-----------------------------------------------------------------------------
    # DB Functions
    #-----------------------------------------------------------------------------
    def connectDB()
      sourceDB = "gradingStats"
      server = "nycw-mhb-sql"
      @dBconnection.connect(server, sourceDB)
    end
        
    def closeDB()
      @dBconnection.close()
    end
    
    def parseTrackingData(excel_data, headerinfo, projectID)
      trackingData = Hash.new
      # REBUILD DATA
      excel_data.each do |key, value|
        fbxnames = [] 
        # THis is a lsit of fbx files per worksheet
        value.each do |concat|
        if concat[1] != "BLENDOUT" then
            #puts "\t#{concat[1]} : #{concat[2]} - #{concat[3]}"
            if (concat[2] != nil and concat[3] != nil) then
              fbxnames  << concat[1]
            end
          end
        end        
        fbxnames = fbxnames.uniq
               
        # Holds a list of fbs names per stand
        fbxNameList = Hash.new
        # Loop through are valid FBX names for each Strand  
        fbxnames.each do |fbx|
          shotIndex = 0
          bShot = false
          fbxRangeList = Array.new          
          value.each do |concat|
            if concat[1] == fbx then
              bShot = true   
              puts "#{concat[1]} = Shot #{shotIndex} "
              fbxRangeList << [concat[2].to_i,concat[3].to_i,shotIndex]              
            end
            
            if (concat[1] != fbx and bShot == true)
              shotIndex += 1
              bShot = false
            end
            
           # Add to our has table
          fbxNameList[fbx] = fbxRangeList
          end
        end 
        trackingData[key] = fbxNameList  
      end
      uploadDB(trackingData, headerinfo, projectID)    
    end
    
    def uploadDB(trackingData, headerinfo, projectID)
      @dBconnection = DatabaseConnection.new( nil )
      connectDB() 
      # Update HEader INFO
      query = "DELETE ExcelModified
                INSERT INTO ExcelModified (UpdatedBy, Modified , Revision)
                VALUES ('#{headerinfo.user}','#{headerinfo.time}',#{headerinfo.revision})                
      "
      @dBconnection.read_data(query)
      
      
      trackingData.each do | key, value |
        value.each do |fbxKey, ranges|        
          query = "DECLARE @FbxID INT
                  IF NOT EXISTS(SELECT * FROM FBX WHERE FBXname='#{fbxKey}')   
                  BEGIN
                    INSERT INTO FBX (FBXname, IsUpdate, ProjectID )
                    VALUES ('#{fbxKey}',1,#{projectID})	
                    SET @FbxID = @@IDENTITY
                  END
                  ELSE
                  BEGIN
                    UPDATE FBX                      	
                    SET IsUpdate = 0,
                        ProjectID = #{projectID}
                        WHERE FBXname='#{fbxKey}'
                    SET @FbxID = (SELECT FbxID FROM FBX WHERE FBXname='#{fbxKey}')
                  END                  
                  DELETE FROM ExcelRanges
                  WHERE FbxID=@FbxID                
                      "                    
          ranges.each do | rangeList |
            query += "INSERT INTO ExcelRanges (FbxID, RangeIn, RangeOut, ShotIndex)
                    VALUES (@FbxID, #{rangeList[0]},#{rangeList[1]},#{rangeList[2]}) 
                   "
          end
          @dBconnection.read_data(query)
        end
      end
      
      closeDB()
    end
 
   
    # public
    def parseExcelDoc(excelfile, headerinfo, projectID)
        _parse(excelfile, headerinfo, projectID)

        
    end

private    
   def _parse(excelfile, headerinfo, projectID)
    # EDL PARSER AND TIME CONVERSION
      excel = ExcelUtils.new(excelfile, true, @g_Log )
      excel.open()
      # Get a list of valid Sheet Indexs by header name
      sheetIndexs = excel.getValidSheetsByHeader('Cutscene Name')        
      # List of header Columns we want to retrieve
      lookups = ["Cutscene Name","FBX NAME","RANGE IN","RANGE OUT"]
      # Grab a data to parse
      excel_data = excel.getDatafromRowNeaders(lookups, sheetIndexs)
      # close our Excel Instance
      
      parseTrackingData(excel_data, headerinfo, projectID )
      
      excel.close()
  end
end


    
    




if ( __FILE__ == "ggsgsdl"  ) then

  # Test DEBUG
  excel = TrackingDocUpdate.new(@log)
  excel.parseExcelDoc()
  
end