NeoFly DEALS

deals.sql

WITH deals AS (
    WITH deals_shortlist AS (
        WITH departure_goods AS (
            WITH search_options AS (
                WITH input AS (
                    select 
                        10 as dep_airport_distance, -- distance to other airports to include in departure airport search
                        500 as arr_airport_distance, -- distance to arrival airports from center
                        'CAT11' as career_name, -- your NeoFly career name
                        'DOG22' as tail_number, -- this plane will be used as center. bu you can also modify the code bellow to set a specific airport
                        1030 + 170 as reserve_weight_lbs -- how much of plane's weight is already used for fuel or other stuff
                )
                select 
                    a.lonx - (dep_airport_distance / 2 / 0.53996 / 111) as dep_lonx_min,
                    a.lonx + (dep_airport_distance / 2 / 0.53996 / 111) as dep_lonx_max,
                    a.lonx - (arr_airport_distance / 2 / 0.53996 / 111) as arr_lonx_min,
                    a.lonx + (arr_airport_distance / 2 / 0.53996 / 111) as arr_lonx_max,
                    a.laty - (dep_airport_distance / 2 / 0.53996 / 111) as dep_laty_min,
                    a.laty + (dep_airport_distance / 2 / 0.53996 / 111) as dep_laty_max,
                    a.laty - (arr_airport_distance / 2 / 0.53996 / 111) as arr_laty_min,
                    a.laty + (arr_airport_distance / 2 / 0.53996 / 111) as arr_laty_max,
                    h.MaxPayloadlbs max_payload,
                    input.reserve_weight_lbs
                from input, career c
                         inner join hangar h on h.tailNumber = input.tail_number and h.status = 0 and h.owner = c.id
                         inner join airport a on
                             -- the center of search (uncomment by removing -- to enable) 
                             a.ident = h.Location -- use hangar plane location as center
                             -- a.ident = 'EYVI' -- use custom location as center
                where c.name = input.career_name
            )
            select search_options.arr_lonx_min, search_options.arr_lonx_max, search_options.arr_laty_min, search_options.arr_laty_max, 
                   dep.ident as dep_ident, dep_gm.unitprice as dep_price, CAST(replace(dep_gm.unitweight, ',', '.') as float) as unit_w, 
                   dep_gm.quantity as dep_qty, dep_gm.name as good_name,
                   dep.lonx as dep_lonx, dep.laty as dep_laty, dep.name as dep_name,
                   search_options.max_payload, search_options.reserve_weight_lbs,
                   dep.has_avgas as dep_avgas, dep.has_jetfuel as dep_jetfuel,
                   dep.num_runway_end_ils >= 1 dep_ils, dep.num_runway_hard > 0 dep_hard, dep.rating as dep_rating
            from search_options, airport dep
            inner join goodsMarket dep_gm on dep.ident = dep_gm.location 
                                         and dep_gm.quantity > 0 and dep_gm.tradetype = 0
                                         and dep_gm.refreshdate >= date('now', '-22 hours')
            where dep.lonx between search_options.dep_lonx_min and search_options.dep_lonx_max 
              and dep.laty between search_options.dep_laty_min and search_options.dep_laty_max
        )
        select dg.good_name,
               dg.dep_price as dep_price,
               dg.dep_ident as dep, 
               arr_gm.location arr,
               min(dg.dep_qty, arr_gm.quantity) as qty,
               arr_gm.unitprice - dg.dep_price as unit_profit,
               (arr_gm.unitprice - dg.dep_price) / dg.unit_w as unit_profit_per_w,
               dg.unit_w,
               dg.arr_lonx_min, dg.arr_lonx_max, dg.arr_laty_min, dg.arr_laty_max,
               dg.dep_lonx, dg.dep_laty, dg.dep_name,
               dg.max_payload, dg.reserve_weight_lbs,
               dep_avgas, dep_jetfuel, dep_ils, dep_hard, dep_rating
        from departure_goods dg
        inner join goodsMarket arr_gm on arr_gm.name = dg.good_name and arr_gm.tradetype = 1
                                     and arr_gm.unitprice > dg.dep_price
                                     and arr_gm.refreshdate >= date('now', '-22 hours')
        order by (arr_gm.unitprice - dg.dep_price) / dg.unit_w desc
        limit 10000
    )
    select sl.good_name,
           sl.dep, sl.dep_name,
           sl.arr, arr_a.name as arr_name,
           sl.qty,
           sl.unit_profit,
           sl.max_payload - sl.reserve_weight_lbs as free_weight,
           sl.unit_w unit_weight,
           min(cast((sl.max_payload - sl.reserve_weight_lbs) / sl.unit_w as integer), sl.qty) as can_carry_quantity,
           sl.dep_price,
           sl.dep_laty || ',' || sl.dep_lonx as dep_coord,
           arr_a.laty || ',' || arr_a.lonx as arr_coord,
           dep_avgas, dep_jetfuel, dep_ils, dep_hard, dep_rating,
           arr_a.has_avgas as arr_avgas, arr_a.has_jetfuel as arr_jetfuel, 
           arr_a.num_runway_end_ils >= 1 arr_ils, arr_a.num_runway_hard > 0 arr_hard, arr_a.rating as arr_rating,
           arr_a.longest_runway_length as arr_rw_len
    from deals_shortlist sl
    inner join airport arr_a on arr_a.ident = sl.arr and arr_a.lonx between sl.arr_lonx_min and sl.arr_lonx_max 
                            and arr_a.laty between sl.arr_laty_min and sl.arr_laty_max
)
select d.good_name,
    d.dep, 
-- display departure name and other things (uncomment by removing -- to enable) 
--     d.dep_name, 
--     dep_avgas, dep_jetfuel, dep_ils, dep_hard, dep_rating,
    d.arr, d.arr_name,
--     arr_avgas, arr_jetfuel, arr_ils, arr_hard, arr_rating,
    d.arr_rw_len,
    d.can_carry_quantity as buy_qty,
    d.qty as max_qty,
    d.unit_weight,
    round(min(free_weight, d.unit_weight * d.qty), 4) as buy_weight,
    round(d.unit_weight * d.qty, 4) as max_weight,
    d.unit_profit,
    d.can_carry_quantity * d.unit_profit as buy_profit,
    d.qty * d.unit_profit as max_profit,
    d.can_carry_quantity * d.dep_price as buy_price,
    dep_coord,
    arr_coord
from deals d
where 1=1 
-- filter out final results to include only specific departure airport (uncomment by removing -- to enable)
--     and dep = 'ESSU'
--     and dep_avgas is true
--     and dep_jetfuel is true
--     and dep_ils is true
--     and dep_hard is true
    and dep_rating > 1
-- filter out arrival airport name or options
--      and arr = 'ESGN' 
--     and arr_avgas is true
--     and arr_jetfuel is true
--     and arr_ils is true
--     and arr_hard is true
    and arr_rating > 1
    and arr_rw_len > 1000
order by buy_profit desc;