kaeverett

select company_connectors.id, companies.name, companies.email, companies.phone, subscriptions.created_at as "purchased_at" ,subscriptions.total_price, subscriptions.frequency, company_connectors.created_at as "connector created_at", last_synced_at as "connector last_synced_at", company_connectors.deleted_at as "connector_deleted_at" ,synced, company_connectors.enabled, authorized from company_connectors
  join connectors on connectors.id  = company_connectors.connector_id
  join companies on companies.id = company_connectors.company_id
  LEFT OUTER JOIN subscriptions on subscriptions.account_id = companies.account_id
  where connectors.name = 'xero'
  and  email NOT LIKE '%@test.%' AND email NOT LIKE '%avalara %'
  and subscriptions.revoked_at ISNULL  and subscriptions.cancelled_at ISNULL

kaeverett

-- create new accountant company and leadsource, copying over the company contact info
-- make note of old/new company_id and account_id
-- make note of bogus user_id
update users set account_id = NEW_ACCOUNT_ID where account_id = OLD_ACCOUNT_ID ;
update companies set account_id = NEW_ACCOUNT_ID where account_id = OLD_ACCOUNT_ID ;
update subscriptions set account_id = NEW_ACCOUNT_ID where account_id = OLD_ACCOUNT_ID ;
update account_licenses set account_id = NEW_ACCOUNT_ID where account_id = OLD_ACCOUNT_ID ;
-- maybe you just want to move over the leadsource to the new account as well?
delete from lead_sources where account_id = NEW_ACCOUNT_ID ;
update lead_sources set account_id = NEW_ACCOUNT_ID where account_id = OLD_ACCOUNT_ID ;

kaeverett

    select "date",
            sum("annual purchases") as "annual purchases",    sum("annual bookings") as "annual bookings",
            sum("monthly purchases") as "monthly purchases",  sum("monthly bookings") as "monthly bookings",
            sum("tfd purchases") as "tfd purchases",          sum("tfd bookings") as "tfd bookings",
            sum("filing purchases") as "filing purchases",    sum("filing bookings") as "filing bookings"
    from

      (
        (SELECT
           to_char(subscriptions.created_at, 'YYYY-MM') as "date",

kaeverett

select
    companies.name, companies.email, lead_sources.id, lead_sources.campaign_id, companies.created_at as "created company", lead_sources.created_at "updated campaignID"
       from lead_sources
         JOIN companies on companies.account_id = lead_sources.account_id
         join accounts on accounts.id = companies.account_id
         WHERE
           lead_sources.created_at > now() - INTERVAL '8 months'
           and (lead_sources.campaign_id = '70133000000y39L' OR lead_sources.campaign_id = '70133000000y39Q')
          and lead_sources.id NOT IN (9597, 9742, 9770, 9346, 9597)
          and accounts.sign_up_company_id = companies.id

kaeverett

select "date",
        sum("invited") as "invited",
        sum("claimed") as "claimed",
        (CASE(sum("invited")) WHEN 0 then 0 ELSE
         ROUND(  100.0 * ( sum(claimed) / sum(invited) ), 1) / 100 END) AS "claimed/invited",
        sum("added_tax") as "added_tax",
        (CASE(sum("invited")) WHEN 0 then 0 ELSE
         ROUND(  100.0 * ( sum(added_tax) / sum(invited) ), 1) / 100 END) AS "added_tax/invited",
        sum("filed")  as "filed",
        (CASE(sum("invited")) WHEN 0 then 0 ELSE

kaeverett

select companies.name, email, phone, first_name, last_name from companies
where
  (
    companies.id IN
    (SELECT merchants.id
     FROM merchants
       JOIN lead_sources ON lead_sources.account_id = merchants.account_id
       JOIN accounts ON accounts.sign_up_company_id = merchants.id
       JOIN tax_registrations ON tax_registrations.company_id = merchants.id
     WHERE

kaeverett

select merchants.email from merchants
  JOIN addresses on addresses.company_id = merchants.id
  join users on users.account_id = merchants.account_id
  join accounts on accounts.id = merchants.account_id
  left OUTER JOIN  subscriptions on subscriptions.account_id = merchants.account_id
     WHERE
       addresses.type = 'business'
       and merchants.email NOT LIKE '%@test.%' AND merchants.email NOT LIKE '%avalara%'
       and subscriptions.id ISNULL
       and merchants.email NOT like '%brandon.houk%'

kaeverett

require 'aws-sdk'

$errors = {}

def push_errors(target, resultQueueName, company_id, model_type, model, error_msg, error_full)
  key = "#{company_id}:#{target}:#{model_type}:#{error_msg}"
  if $errors[key] == nil
    $errors[key] = {:target => target, :resultQueueName => resultQueueName, :company_id => company_id, :model_type => model_type, :model => model, :error_msg => error_msg, :error_full => error_full, :count => 1}
  else

kaeverett

select companies.name, companies.email, companies.phone, subscriptions.total_price, subscriptions.frequency, company_connectors.created_at as "connector created_at", last_synced_at as "connector last_synced_at", synced, company_connectors.enabled, authorized from company_connectors
  join connectors on connectors.id  = company_connectors.connector_id
  join companies on companies.id = company_connectors.company_id
  LEFT OUTER JOIN subscriptions on subscriptions.account_id = companies.account_id
  where connectors.name = 'xero'
  and  email NOT LIKE '%@test.%' AND email NOT LIKE '%avalara %'
  and subscriptions.revoked_at ISNULL  and subscriptions.cancelled_at ISNULL
  and company_connectors.deleted_at ISNULL

kaeverett

# gem install restclient
require 'rest_client'
require 'base64'
require 'json/ext'

def self.get(url, params, headers)
  puts params
  puts
  RestClient.get(url + "?" + params, headers)
end