class FlightLogExpense < Sequel::Model(:'flight log expenses')

  code_association :expense_type_value, :'type', :flight_log_expense_type

  #Many to one association with composite keys
  n_to_o :trip_leg, :class=>:TripLeg, :prefix=>'trip leg'
  many_to_one :vendor, :class=>:Vendor, :key=>:vendor_id, :primary_key=>:'vendor id'

#### BEGIN GENERATED SECTION ####
  set_primary_key [:'kid - user', :'kid - mult', :'kid - comm', :'kid - date', :'kid - time']

  column_alias :ac_curr_bill_amount, :'ac curr bill amount'
  column_alias :ac_curr_cost, :'ac curr cost'
  column_alias :ac_currency_type, :'ac currency type'
  column_alias :ac_quantity, :'ac quantity'
  column_alias :ac_quantity_tenths, :'ac quantity tenths'
  column_alias :ac_unit, :'ac unit'
  column_alias :account_number, :'account number'
  column_alias :aircraft, :'aircraft'
  column_alias :allow_0_cost, :'allow 0 cost'
  column_alias :approved, :'approved'
  column_alias :arrival_airport, :'arrival airport'
  column_alias :auto_create, :'auto create'
  column_alias :bill_amount, :'bill amount'
  column_alias :billable, :'billable'
  column_alias :comment_req, :'comment req'
  column_alias :cost, :'cost'
  column_alias :cost_center, :'cost center'
  column_alias :cost_kid_comm, :'cost kid - comm'
  column_alias :cost_kid_date, :'cost kid - date'
  column_alias :cost_kid_mult, :'cost kid - mult'
  column_alias :cost_kid_time, :'cost kid - time'
  column_alias :cost_kid_user, :'cost kid - user'
  column_alias :crew_id, :'crew id'
  column_alias :currency_type, :'currency type'
  column_alias :description, :'description'
  column_alias :discount, :'discount'
  column_alias :discount_date, :'discount date'
  column_alias :estimated_cost, :'estimated cost'
  column_alias :fet_tax, :'fet tax'
  column_alias :fed_tax_rate, :'fed tax rate'
  column_alias :image_name, :'image name'
  column_alias :internal_1, :'internal 1'
  column_alias :internal_6, :'internal 6'
  column_alias :internal0, :'internal0'
  column_alias :internal01, :'internal01'
  column_alias :internal02, :'internal02'
  column_alias :internal03, :'internal03'
  column_alias :inv_received_date, :'inv received date'
  column_alias :invoice, :'invoice'
  column_alias :invoice_date, :'invoice date'
  column_alias :invoice_paid, :'invoice paid'
  column_alias :invoice_paid_date, :'invoice paid date'
  column_alias :invoice_received, :'invoice received'
  column_alias :invoiced, :'invoiced'
  column_alias :kid_comm, :'kid - comm'
  column_alias :kid_date, :'kid - date'
  column_alias :kid_mult, :'kid - mult'
  column_alias :kid_time, :'kid - time'
  column_alias :kid_user, :'kid - user'
  column_alias :leg_date, :'leg date'
  column_alias :onecost_type_code, :'onecost type code'
  column_alias :pay_method, :'pay method'
  column_alias :percent_markup, :'percent markup'
  column_alias :problem, :'problem'
  column_alias :quantity, :'quantity'
  column_alias :quantity_tenths, :'quantity tenths'
  column_alias :receipt, :'receipt'
  column_alias :receipt_number, :'receipt number'
  column_alias :receipt_req, :'receipt req'
  column_alias :reimburs, :'reimburs'
  column_alias :serv_kid_comm, :'serv kid - comm'
  column_alias :serv_kid_date, :'serv kid - date'
  column_alias :serv_kid_mult, :'serv kid - mult'
  column_alias :serv_kid_time, :'serv kid - time'
  column_alias :serv_kid_user, :'serv kid - user'
  column_alias :service_index, :'service index'
  column_alias :tax, :'tax'
  column_alias :taxable, :'taxable'
  column_alias :total_cost_fixed, :'total cost fixed'
  column_alias :trip_leg_kid_comm, :'trip leg kid - comm'
  column_alias :trip_leg_kid_date, :'trip leg kid - date'
  column_alias :trip_leg_kid_mult, :'trip leg kid - mult'
  column_alias :trip_leg_kid_time, :'trip leg kid - time'
  column_alias :trip_leg_kid_user, :'trip leg kid - user'
  column_alias :type, :'type'
  column_alias :unit, :'unit'
  column_alias :unit_cost, :'unit cost'
  column_alias :vendor_id, :'vendor id'
  column_alias :vendor_req, :'vendor req'
  column_alias :key0, :'key0'
  column_alias :key1, :'key1'
  column_alias :key2, :'key2'
#### END GENERATED SECTION ####

  # For Fuel Flight Log expenses
  def fuel_cost
    qty = quantity.to_i # Don't want to do this but its treating this has string for some reason
    return 0.0 if type.to_i != 1 or qty == 0
    fuel_rate * qty.to_i
  end

  def is_departure_expense?
    arrival_airport == 0
  end

  def type_string
    is_departure_expense? ? "departure" : "arrival"
  end

  def airport_fuel_lookup
    if !is_departure_expense? then
      ap_str = "ARRIVAL"
      act_str = "ARR"
      prefix = "FBO"
    else
      ap_str = "DEPART"
      act_str = "DEPT"
      prefix = "FUELER"
    end
    use_leg = @trip_leg or trip_leg

    sql = <<-SQL
    SELECT TOP 1 TL."TRIP NUMBER",TL."LEG NUMBER", TL."#{ap_str} AIRPORT ID",
      AF."COST 1", AF."QTY 1", AF."COST 2", AF."QTY 2", AF."COST 3", AF."QTY 3", AF."COST 4", AF."QTY 4",
      AF."COST 5", AF."QTY 5", AF."COST 6", AF."QTY 6", AF."COST 7", AF."QTY 7", AF."COST 8", AF."QTY 8",
      AF."COST 9", AF."QTY 9", AF."COST 10", AF."QTY 10", AF."EFFECTIVE DATE"
    FROM "TRIP LEGS" AS TL, "AIRPORT FUEL" AS AF
      WHERE (AF."FBO KID - DATE" = TL."#{prefix} KID - DATE"
      AND AF."FBO KID - TIME" = TL."#{prefix} KID - TIME"
      AND AF."FBO KID - MULT" = TL."#{prefix} KID - MULT"
      AND AF."FBO KID - COMM" = TL."#{prefix} KID - COMM"
      AND AF."FBO KID - USER" = TL."#{prefix} KID - USER"
      AND AF."VENDOR ID" = '#{vendor_id}'
      AND (AF."EFFECTIVE DATE" <= TL."#{act_str} DATE ACT LOCAL" OR AF."EFFECTIVE DATE" <= TL."#{ap_str} DATE - LOCAL"))
    AND   (TL."TRIP NUMBER" = ? AND TL."LEG NUMBER" = ?)
    ORDER BY AF."EFFECTIVE DATE" DESC
    SQL
    return db.fetch(sql, use_leg.trip_number, use_leg.leg_number).first
  end

  def build_fuel_tier_hash airport_fuel # creates a hash table of the qty and fuel prices for each tier
    h = Hash.new
    (1..10).each do |n|
      h[airport_fuel[:"qty #{n}"]] = airport_fuel[:"cost #{n}"]
    end
    h
  end

  def fuel_rate
    airport_fuel = airport_fuel_lookup
    return 0.0 if airport_fuel.nil? or airport_fuel == {}
    h = build_fuel_tier_hash airport_fuel
    h.keys.sort.reverse.each do |key|
      return (h[key] / 100.0) if quantity.to_i >= key
    end
    0.0
  end

  # Passing in trip_leg to help out airport_fuel_lookup from having to eager graph
  def fuel_passdown_hash the_trip_leg
    expense_type = type_string
    @trip_leg    = the_trip_leg
    vendor       = @trip_leg.send("#{expense_type}_fuel_vendor__company_2")
    vendor       = @trip_leg.send("#{expense_type}_fuel_vendor__company_1") if !vendor or vendor == ""
    vendor       = @trip_leg.send("#{expense_type}_fuel_vendor__vendor_id") if !vendor or vendor == ""
    airport      = @trip_leg.send("#{expense_type}_icao_val")
    fbo          = @trip_leg.send("#{expense_type}_fbo__name")
    aircraft     = (@trip_leg.aircraft ? @trip_leg.tail_number : @trip_leg.aircraft_id)
    leg_date     = (@trip_leg.dept_date_act_local and @trip_leg.dept_date_act_local != 0 ? @trip_leg.dept_date_act_local : @trip_leg.depart_date_local)

    {
      :leg_date      => leg_date,
      :trip_number   => @trip_leg.trip_number,
      :leg_number    => @trip_leg.leg_number,
      :ac            => aircraft,
      :airport       => airport,
      :fbo           => fbo,
      :vendor        => vendor,
      :fuel_rate     => fuel_rate,
      :fuel_quantity => quantity,
      :fuel_cost     => fuel_cost
    }
  end
end