kaeverett

select companies.name as "company", datasources."createdAt" as "ds created_at", jobs.created_at as "job created_at" ,jobs.started_at, jobs.finished_at, datasources.name, datasources.connector_id, jobs.status from datasources
 join companies on datasources.company_id = companies.id
 LEFT OUTER JOIN jobs on jobs.datasource_id = datasources.id
ORDER BY datasources."createdAt" DESC
LIMIT 1000

kaeverett

select * from form_dates where form_name = 'CA BOE-401-A2'
and year > 2014 and year < 2031
and form_dates.frequency = 'Annual'
and form_dates.form_name = 'CA BOE-401-A2'

-- set due jan 31 Y+1, begin jan 1, end dec 31

DELETE from form_dates where form_name = 'CA BOE-401-A2'
and form_dates.frequency = 'Annual'
and year > 2014 and year < 2031

kaeverett

-- usage report

select connectors.name, count(company_connectors.id)
 from company_connectors
   join connectors on connectors.id = company_connectors.connector_id
where company_connectors.deleted_at ISNULL
   and company_connectors.authorized = TRUE

kaeverett

SELECT  accountant.name as "account",
        companies.name as "company",
        companies.created_at as "company_created_at",
        case
        when campaign_id = '70133000001QUSP'  then '70133000001QUSP paid search'
        when campaign_id = '70133000001QUSe'  then '70133000001QUSe display ads'

kaeverett

-- month aggregate data
-- purchases in the month

-- net new connector report
select companies.name, companies.email, connectors.name as "connector", TO_CHAR(company_connectors.created_at, 'MM-DD-YYYY') as "created_at"  from company_connectors
join companies on company_connectors.company_id = companies.id
join connectors on connectors.id = company_connectors.connector_id
join summaries on summaries.company_id=company_connectors.company_id
where
  company_connectors.created_at > date_trunc('MONTH',now())::DATE --'05/01/2016'

kaeverett

-- usage report

select connector_name, count(connector_name)

FROM   dblink('dbname=trustfilestorage user=deploy password=4BFkh59jDd9YCSu',
                        'SELECT  account_id, company_name, connector_name from company_daily_usage
                        where

kaeverett

  select "date",
          sum("PDFs generated") as "PDFs generated",
          sum("manually filed") as "manually filed",
          sum("e-filed") as "e-filed",
          sum("tax liability")

  FROM
    (
      (SELECT
         date_trunc('Month', returns.filed_on) AS "date",

kaeverett

select to_char("date", 'YYYY-MM') as "month",
       case
        when campaign_id = '70133000001QUSP'  then '70133000001QUSP paid search'
        when campaign_id = '70133000001QUSe'  then '70133000001QUSe display ads'
        when campaign_id = '70133000000e732AAA'  then '70133000000e732AAA sellers central'
        when campaign_id = '70133000001QHNP'  then '70133000001QHNP general paid search'
        when campaign_id = '70140000001LY3N'  then '70140000001LY3N organic'
        when campaign_id = '70140000000VvPs'  then '70140000000VvPs default'
        ELSE campaign_id

kaeverett

def parse_xml(d, path1, path2)
  t = nil
  d.xpath(path1).each do |e|
    t =  e.xpath(path2).text
  end
  return t
end

def append_to_file(filename, s)
  File.open(filename, 'a') { |file| file.write("#{s}\r") }

kaeverett

-- FIX CA annual calendar year
select * from form_dates where form_name = 'CA BOE-401-A2'
and year > 2015 and year < 2019
and form_dates.frequency = 'Annual'

DELETE from form_dates where form_name = 'CA BOE-401-A2'
and form_dates.frequency = 'Annual'
and year > 2015 and year < 2019

INSERT INTO form_dates (form_name, year,  begins_on, ends_on, due_on, frequency, frequency_index, created_at, updated_at)