#! /usr/bin/env python
# --*-- coding:utf-8 --*--

# 将druid 查询转化为 SQL

from ext.parser import QueryParser
from config.setting import *
from ext.util import get_datatime


class QueryConverter(object):

    def __init__(self):
        pass

    def getSQL(self, druidQuery):
        parser = QueryParser(druidQuery)
        sql_list = list()
        sql_list.append("select")

        parser_data = parser.get_data()
        group_data = parser.get_group()
        filter_items = parser.get_filter()
        filterKeys = filter_items.keys()
        order_items = parser.get_sort()
        pagination = parser.get_pagination()

        for group in group_data:
            sql_list.append(group)
            sql_list.append(',')

        for data in parser_data:
            sql_list.append(DATAMAP.get(data).get("formula"))
            sql_list.append(',')

        for filterkey in filterKeys:
            sql_list.append(filterkey)
            sql_list.append(',')

        sql_list.pop()

        sql_list.append("from")
        sql_list.append("ym_detail")
        sql_list.append("where")
        sql_list.append("unix_timestamp(time_stamp)")
        sql_list.append("between")
        sql_list.append(str(parser.get_time().get("start") - 28800))
        sql_list.append("and")
        sql_list.append(str(parser.get_time().get("end") - 28800))

        if group_data:
            sql_list.append("group by")
            for group in group_data:
                sql_list.append(group)
                sql_list.append(',')
            sql_list.pop()

        if filter_items:
            sql_list.append("having")
            for filter_key in filterKeys:
                filter_item = filter_items.get(filter_key)
                for filteroperate in filter_item:
                    filtervalue = filter_item.get(filteroperate)
                    if isinstance(filtervalue, list):
                        filtervalue = str(tuple(filtervalue)).replace('u','')
                    else:
                        pass
                    sql_list.append(filter_key)
                    sql_list.append(OPERATEMAP.get(filteroperate))
                    if filteroperate == '$match':
                        filtervalue = "'{0}'".format(filtervalue)
                    else:
                        if filter_key in ("week", "month", "day"):
                            filtervalue = "'{0}'".format(filtervalue)
                        else:
                            filtervalue = str(filtervalue)
                    sql_list.append(filtervalue)
                    sql_list.append('and')
            sql_list.pop()

        if order_items:
            sql_list.append("order by")
            for order_item in order_items:
                sql_list.append(order_item.get("orderBy"))
                sql_list.append(ORDERMAP.get(str(order_item.get("order"))))
                sql_list.append(',')
            sql_list.pop()

        sql_list.append("limit")
        offset = pagination.get('offset', 0)
        page = pagination.get('page')
        size = pagination.get('size')
        if page != 0:
            sql_list.append(str(page * size))
            sql_list.append(',')
            sql_list.append(str((page+1) * size))
        else:
            sql_list.append(str((page+1) * size))
        return ' '.join(sql_list)

if __name__ == '__main__':
    print QueryConverter().getSQL({"filters":{"$and":{"week":{"$eq":"01-3"}}},"sort":[{"orderBy":"conversion","order":-1}],"data":["click","conversion"],"group":["offer_id","aff_manager","week"],"settings":{"return_format":"json","data_source":"ymds_druid_datasource","report_id":"8631614495385992522","pagination":{"page":0,"size":1000000},"time":{"start":1430105882,"end":1430134682,"timezone":0}}})
@@ -267,16 +267,44 @@
 Query):%0A
+        columnList = list()%0A
         
@@ -707,32 +707,69 @@
 t.append(group)%0A
+            columnList.append(group)%0A
             sql_
@@ -777,32 +777,32 @@
 ist.append(',')%0A
-
 %0A        for dat
@@ -877,24 +877,60 @@
 %22formula%22))%0A
+            columnList.append(data)%0A
             
@@ -1004,35 +1004,130 @@
     
-sql_list.append(filterkey)%0A
+if filterkey not in sql_list:%0A                sql_list.append(filterkey)%0A                columnList.append(filterkey)%0A    
     
@@ -3502,16 +3502,28 @@
   return
+ columnList,
  ' '.joi
@@ -3617,20 +3617,20 @@
 $and%22:%7B%22
-week
+year
 %22:%7B%22$eq%22
@@ -3635,12 +3635,12 @@
 q%22:%22
+2
 01
--3
+5
 %22%7D%7D%7D
@@ -3750,20 +3750,20 @@
 nager%22,%22
-week
+year
 %22%5D,%22sett
@@ -3927,14 +3927,14 @@
 1430
-105882
+964811
 ,%22en
@@ -3944,14 +3944,14 @@
 1430
-134682
+965111
 ,%22ti

remove filterkey in filterItem if filterkey is exist in group already change getSql method from get sql to get column and sql