Using ruby to parse .bex files from Billings Pro

#!/usr/bin/env ruby
# bex_parser.rb

# If you are a Ruby Programmer, this script may be interesting to you.

require 'rubygems'
require 'hpricot'
require 'mysql'
require 'time'

# Set the File to read from the slips that were exported.
doc = Hpricot::XML(File.read("Desktop/slips.bex"))
# Loop through the top element, silly since there is only one, but needed
(doc/'plist').each do |sx|

  # Loop through all the time slips that were exported and exist in the .bex file.
  (sx/'dict array dict').each do |sm|

    # Loop through each component of time slip
    (sm/'array').each do |st|

      # Setting the billing comment from the position, may be in 1 or 2 [0][1]
      ((st.parent/'key')[1].inner_html == 'comment') ? @billing_comment = (st.parent/'string')[0].inner_html : @billing_comment = ''
      ((st.parent/'key')[2].inner_html == 'comment') ? @billing_comment = (st.parent/'string')[1].inner_html : @billing_comment = ''

      # Setting the category from the position, may be in 1 or 2 [0][1]
      ((st.parent/'key')[1].inner_html == 'category.name') ? @billing_category = (st.parent/'string')[0].inner_html : @billing_category = ''

      if ((st.parent/'string')[2].inner_html == 'Billings')
        @billing_description = ((st.parent/'string')[2].inner_html == 'Billings' ? (st.parent/'string')[3].inner_html : (st.parent/'string')[2].inner_html)
        @billing_worker = ((st.parent/'string')[2].inner_html == 'Billings' ? (st.parent/'string')[4].inner_html : (st.parent/'string')[3].inner_html)
      else
        @billing_description = ((st.parent/'string')[4].inner_html == 'Billings' ? (st.parent/'string')[5].inner_html : (st.parent/'string')[4].inner_html)
        @billing_worker = ((st.parent/'string')[4].inner_html == 'Billings' ? (st.parent/'string')[6].inner_html : (st.parent/'string')[5].inner_html)
      end

      # Look through each time entry for each timeslip.
      (st/'dict').each do |sz|
        @time_uuid = (sz/'string')[1].inner_html
        @time_start = (sz/'date')[2].inner_html
        @time_end = (sz/'date')[1].inner_html
        @time_duration = (Time.parse(@time_end) - Time.parse(@time_start)) / 3600 

        begin
          db = Mysql.real_connect("localhost", "username", "password", "dbname")
          sth = db.prepare(
                            "INSERT INTO timeslips
                              (uuid, begin, end, title, duration, worker, description, category)
                            VALUES
                              (?,?,?,?,?,?,?,?)
                            ON DUPLICATE KEY UPDATE
                              count=count + 1, begin=?, end=?, title=?, duration=?, worker=?, description=?, category=?"
                            )
          sth.execute(@time_uuid, @time_start, @time_end, @billing_description, @time_duration, @billing_worker, @billing_comment, @billing_category, @time_start, @time_end, @billing_description, @time_duration, @billing_worker, @billing_comment, @billing_category)

        # puts "Number of rows inserted: #{db.affected_rows}"
        rescue Mysql::Error => e
          puts "Error code: #{e.errno}"
          puts "Error message: #{e.error}"
          puts "Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")

        ensure
          db.close if db
        end
      end # Ends (st/'dict').each
    end # Ends (sm/'array').each
  end # Ends (sx/'dict array dict').each
end # (doc/'plist').each

Advertisement