Old SQL [pronounced /skuːl/]

# Old SQL Reports YAML
# This is an example report. Replace it with your actual reports.
#
# 'description' is the value displayed in the 'SELECT A REPORT' drop down list.
#
# The 'report_sql' file should be located under config/old_sql/report_sql.
# See config/old_sql/report_sql/user_old_sql_demo.sql for an example.
#
# The 'report_processor' parameter is optional, and should point to a class in lib/old_sql.
# See lib/old_sql/user_old_sql_demo_processor.rb for an example.
# 
# 'report_processor' names should be capitalized and separated with underscores. The file
# itself should follow normal ruby class naming conventions.
#
# 'report_design' is also optional, and should point to a file in config/old_sql/report_design.
# See config/old_sql/report_design/user_old_sql_demo.CSV for an example.
#
# 'report_view' is optional. It overrides the default_report_view defined in the initializer. It
# can be set to jqgrid, table, or chart.
#
# 'query_vars' are variables that are passed into the SQL file and can be parsed using ERB.
#
# Optionally use 'report_db' to specify a class that extends ActiveRecord::Base. Use the require
# path for your class. E.g. foo/my_alternative_db.
#
# The 'fields' are the headers for the report.

user_jqgrid:
  description: User jqGrid
  report_sql: user_old_sql_demo
  report_design: user_old_sql_demo.CSV
  report_view: jqgrid
  query_vars:
    - 
      old_sql_admin: 0
  fields: 
    - 'id'
    - 'name'

user_table:
  description: User HTML Table
  report_sql: user_old_sql_demo
  report_processor: User_Old_Sql_Demo_Processor
  report_view: table
  fields: 
    - 'id'
    - 'name'

user_chart:
  description: User Chart
  report_sql: user_old_sql_demo
  report_design: user_old_sql_demo.yml
  report_view: chart
  fields: 
    - 'id'
    - 'name'

SELECT u.id, u.name
FROM users u
WHERE u.created_at > date('<%=start_date.gsub('/','-')%>')
AND u.created_at < date('<%=end_date.gsub('/','-')%>')

# Old SQL Example Report Design Document
#
# Text not in quotes are column names from the SQL query.
# Quoted text are labels that will appear in the report.
# Expressions like ( id + id ) * 10 below must be single 
# space delimited. So (id+id) will not parse correctly.
#
# NaN and Infinity are returned as zero. 
#
# Comments are lines beginning with a hash #. They are 
# stripped when this document is parsed.
#
# Values (either columns or expressions) are rounded by default. 
# To disable this set round_report_values to false in
# config/initializers/old_sql.rb. You can also change the
# precision, which is 2 by default.
#
# Tabs or spaces can optionally be used between "cells" for 
# ease of reading.
#
# All uppercase labels are bolded and converted to capitalized
# words for the jqgrid, and HTML table report views.

id,							name
"EXAMPLE MATH OPERATION",	id + id
"Totals",					"..."
"Expression",				( id + id ) * 10
"Divide by Zero 1",			0 / 0
"Divide by Zero 2",			1.0 / 0
"Divide by Zero 3",			0.0 / 0.0
"Rounding",					200.0 / 43.0

# Old SQL Chart Design Example
#
# The key for the YAML entry can either be pie or bar.
# 'title' is a label describing the data.
# 'data' can be either a column name, or a formula.

pie:
  - 
    title: Id
    data: id
  - 
    title: Id + 20
    data: id + 20
  - 
    title: Id + 50
    data: id + 50
  - 
    title: Id + 70
    data: id + 70
  - 
    title: Id + 10
    data: id + 10  

require 'old_sql/report_processor/base'

module OldSql
  module ReportProcessor
    class UserOldSqlDemoProcessor < OldSql::ReportProcessor::Base

      def parse(resultset)
        init(resultset)
 
        Rails.logger.debug "REC: #{@rec}"
 
        add_row([@rec['id'], @rec['name']])
        add_row(['Totals', '...'])
 
        @data
      end
    end
  end
end

SELECT
	IFNULL(SUM(f.total),0) AS foo_total,
	IFNULL(SUM(CASE WHEN f.attempts IS NOT NULL THEN 1 ELSE 0 END),0) AS foo_attempts,
	IFNULL(bar_totals.bar_count,0) AS bar_count
FROM
	foo f
JOIN
	(SELECT COUNT(id) AS bar_count FROM bar) AS bar_totals
WHERE
	1=1
	AND f.created_at > '<%=start_date%>'
	AND f.created_at < '<%=end_date%>'

rails g old_sql:copy_assets

rake old_sql:print:range[user_table,'development','2011-03-01','2011-09-01']

rake old_sql:print:today[user_table,'development']

rake old_sql:print:week[user_table,'development']