kaeverett

class SortBrms 
    # TODO add/account for missing BRMs
    # TODO add team size/velocity
    # TODO estimate delivery date
    def initialize
        @VIABILITY =    100000000000
        @POST_CUTOVER = 10000000000
        @ALL_HOTELS =   1000000000
        @VIABILITY =    100000000
        @BACKPLANE =    10000000

kaeverett

create table reservation (
        id varchar(255) not null,
        account_id varchar(255),
        arrival_date date,
        confirmation_code varchar(255),
        create_time timestamp,
        departure_date date,
        property_id varchar(255),
        reservation_alias varchar(255),
        status varchar(255),

kaeverett

-- ROOMS
select aggregate_rooms_view.day, sum(total) as "total rooms",
       sum(occupied) as "occupied",
       avg(percentage_occupied) as "% rooms occupied",
       sum(available) as "available",
       sum(out_of_order) as "out_of_order",
       sum(walk_in) as "walk_in"
from aggregate_rooms_view
--where aggregate_rooms_view.tenant_id = 10 and aggregate_rooms_view.property_id = 1 and (aggregate_rooms_view.day = '2019-07-01' or aggregate_rooms_view.day = date('2019-07-01') - interval '1 year') --or
where  (aggregate_rooms_view.day = '2019-07-01' or aggregate_rooms_view.day = date('2019-07-01') - interval '1 year') --or

kaeverett

create view aggregate_rooms_view as (
  select day,
         tenant_id,
         property_id,
         room_type,
         sum(on_the_market::int) as "total",
         sum(available::int) as "available",
         sum(on_the_market::int) - sum(available::int) as "unavailable",
         sum(off_the_market::int) as "off_the_market",

kaeverett

class Mom < ApplicationRecord
  #  Mom.seed => Mom.simulate => Mom.remove
   def self.remove
     RawRoom.all.each do |r|
       r.destroy
     end
     RawReservation.all.each do |r|
       r.destroy
     end
     HistoricalAccount.all.each do |a|

kaeverett

\d+ raw_rooms;
                                                                 Table "public.raw_rooms"
        Column        |            Type             | Collation | Nullable |                Default                | Storage  | Stats target | Description 
----------------------+-----------------------------+-----------+----------+---------------------------------------+----------+--------------+-------------
 id                   | bigint                      |           | not null | nextval('raw_rooms_id_seq'::regclass) | plain    |              | 
 tenant_id            | integer                     |           |          |                                       | plain    |              | 
 property_id          | integer                     |           |          |                                       | plain    |              | 
 building             | character varying           |           |          |                                       | extended |              | 
 floor                | charact

kaeverett

select  accounts.ztb_crm_id,companies.name, TO_CHAR(subscriptions.created_at, 'YYYY-MM-DD:HH:MM:SS') as "purchased_at" , subscriptions.market_type, subscriptions.code
from subscriptions
join companies on companies.account_id = subscriptions.account_id
join accounts on accounts.id = subscriptions.account_id
where subscriptions.created_at > '2017-11-28'
  and accounts.sign_up_company_id = companies.id
  and subscriptions.code = 'TFBasicPostPayAnnualDiscounted'
ORDER BY subscriptions.created_at ASC

kaeverett

select "month",
        "connector",
        sum("leads") as "new leads",
        sum("tax") as "first data count",
        (CASE(sum("leads")) WHEN 0 then 0 ELSE
         ROUND(  100.0 * ( sum(tax) / sum(leads) ), 1) / 100 END) AS "data/leads",
        sum("filed") as "filed count",
        (CASE(sum("leads")) WHEN 0 then 0 ELSE
         ROUND(  100.0 * ( sum(filed) / sum(leads) ), 1) / 100 END) AS "filed/leads",
        sum("subscription") as "buy count",

kaeverett

select companies.name, accounts.created_at, accounts.avatax_account_id, accounts.ztb_crm_id from accounts
join companies on companies.account_id = accounts.id
where accounts.sign_up_company_id = companies.id

kaeverett

select TO_CHAR(items.created_at, 'YYYY') as "month", count(*), sum(items.sales_tax) from items
join datasources on items.datasource_id = datasources.id
where datasources.file_header_mapping_id NOTNULL
and items.refund = FALSE
and       items.created_at > now() - INTERVAL '3 years'
GROUP BY TO_CHAR(items.created_at, 'YYYY')



select TO_CHAR(items.created_at, 'YYYY') as "month", count(*), sum(items.sales_tax) from items