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'
   
#### 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 ####

  def fuel_rate  # Takes a FlightLogExpense for fuel
    return 0 if type != 1
    my_type = (arrival_airport == 1 ? "ARRIVAL" : "DEPART")
    airport_fuel = airport_fuel_lookup vendor_id, my_type
    (airport_fuel ? fuel_rate_by_quantity(quantity, airport_fuel) : 0)
  end

  def airport_fuel_lookup vendor_id, type #ARRIVAL or DEPART
    str = (type == "DEPART" ? "DEPT" : "ARR")
    prefix = (type == "DEPART" ? "FUELER" : "FBO")
    sql = <<-SQL
    SELECT TOP 1 TL."TRIP NUMBER",TL."LEG NUMBER", TL."#{type} 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."#{str} DATE ACT LOCAL" OR AF."EFFECTIVE DATE" <= TL."#{type} DATE - LOCAL"))
    AND   (TL."TRIP NUMBER" = ? AND TL."LEG NUMBER" = ?)
    ORDER BY AF."EFFECTIVE DATE" DESC
    SQL
    return db.fetch(sql, trip_leg.trip_number, trip_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_by_quantity qty, airport_fuel # gives the cost associated with the qty of fuel purchased
    return 0 if qty == nil or qty == 0 or airport_fuel == nil
    h = build_fuel_tier_hash airport_fuel
    h.keys.sort.reverse.each do |key|
       return (h[key] / 100.0) if (qty and key) and qty >= key
    end
    0
  end
end