slaingod

<query>
    <name>
    </name>
    <sql>
        <select>
            <![CDATA[Date(created_at) as date]]>
        </select>
        <select>
            <![CDATA[count(id) as total]]>
        </select>
        <from>
        </from>
        <conditions>
        </conditions>
        <group>
            <![CDATA[date]]>
        </group>
        <having>
            <![CDATA[date >= :start_date and date <= :end_date]]>
        </having>
    </sql>
</query>      

  def replace_named_bind_variables_no_quotes(statement, bind_vars) #:nodoc:
    statement.gsub!(/:(\w+)/) do
      match = $1.to_sym
      if bind_vars.include?(match)
        bind_vars[match]
      else
        raise ActiveRecord::PreparedStatementInvalid, "missing value for :#{match} in #{statement}"
      end
    end
  end

  def query

    # from_xml puts it in something like { queries => {query => [{name, sql}, {name,sql}....] }}
    queries = Hash.from_xml(params[:queries])['queries']['query']
#     logger.dbg queries.inspect

        # generate the report structure
        report = []
        queries.each { |data|

            query = data['sql']
            logger.dbg query.inspect

            # Now we need to get around a bunch of ActiveRecord lameness....
            # Presumably it is done this way to satisy someone's idea of 'how you should do things'
            # rather than, 'let's help them do it, no matter how they want to get it done'

            # first, AR doesn't do bind variables for anything but conditions...
            replace_named_bind_variables_no_quotes(query['group'], params) if query['group']
            replace_named_bind_variables_no_quotes(query['having'], params) if(query['having'])

            # second, we need to join the select clauses, as :select doesn't accept an array...   
            query['select'] = query['select'].join(', ')

            # third, AR doesn't support a separate HAVING clause, you have to attach it to GROUP BY
            if query['having'] and query['group'] # you always have both...
                query['group'] = query['group'] + " HAVING " + query['having']
                query.delete('having')
            end

            # fourth, we need to intern the keys so that they pass 'inspection' by AR
            interned_query = {}
            query.each { |key, value|
                interned_query[key.intern] = value if(value != nil) # AR doesnt like :conditions => nil either...
            }
            logger.dbg interned_query.inspect

            # fifth, we need to use Creative instead of just ActiveRecord::Base because there is a bug/weirdness in reset_table_name 
            # where it can't find the abstract_class
            report << [data['name'], Creative.find(:all, interned_query)]
        }

    # output the results xml
        str = ''
        xml = Builder::XmlMarkup.new(:target => str, :indent => 1)

        xml.result {
            report.each { |query|
                xml.query {
                    keys = []
                    xml.name query[0]
                    xml.cols {
                        exemplar = query[1].first
                        exemplar.attributes.each { |key, value|
                            xml.col key
                            keys << key
                        }
                    }
                    xml.rows {
                        query[1].each {  |row|
                            xml.r {
                                keys.each { |key|
                                    xml.v row.attributes_before_type_cast[key]
                                }    
                            }
                        }
                    }
                }
            }
        }
        render :xml => str

  end