from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.ext.sqlsoup import SqlSoup

import datetime
import sys

sql = """SELECT COUNT(click.id) AS click_count,
           COUNT(transaction.id) AS transaction_count,
           SUM(transaction.sale) AS sales_total,
           SUM(transaction.commission) AS commission_total,
                    store.id AS id,
                    store.name AS label
           FROM analytics2_clickfact as click
           LEFT JOIN analytics2_transactiondimension as transaction on click.transaction_id = transaction.id
           INNER JOIN store as store on click.store_id = store.id
           %s
           GROUP BY store.id
           ORDER BY commission_total DESC
           LIMIT 50
      """

engine = create_engine('mysql://root:0411@localhost/analytics2',
                       echo = True)

db = SqlSoup(MetaData(engine))

def main():
    click = db.analytics2_clickfact
    date = db.analytics2_datedimension
    transaction = db.analytics2_transactiondimension
    campaign = db.analytics2_campaigndimension
    page = db.analytics2_pagedimension
    remotehost = db.analytics2_remotehostdimension
    type = db.analytics2_typedimension

    startdate = datetime.date(month=9, day=11, year=2009)
    enddate = datetime.date(month=9, day=11, year=2009)

    #join = db.join(click, date, click.clicked_id==date.id)
    #join = db.join(join, transaction)

    #query = join.\
    #    filter(date.date>=startdate).\
    #    filter(date.date<=enddate).\
    #    group_by(click.store_id)

    #query = db.join(query, transaction, isouter=True).query()


    #query = db.join(click, date, click.clicked_id==date.id).query() #.filter(transaction.commission!=None)

    #query = query.query()
    from sqlalchemy import select, func
    from sqlalchemy.sql import and_, or_, not_

    click_table = click._table
    transaction_table = transaction._table
    date_table = date._table

    facts = select([click_table.c.id, func.sum(transaction_table.c.commission)],
                   and_(date.date>=startdate, date.date<=enddate),
                   from_obj=[click_table.join(transaction_table, isouter=False).join(date_table, click_table.c.clicked_id==date_table.c.id)],
                   group_by=[click_table.c.store_id])

    print dir(facts)
    facts = facts.limit(5)

    for fact in facts.execute():
        print fact

if __name__ == '__main__':
    main()