import re
import sqlite3
from collections import Counter

import jieba
from pyecharts import options as opts
from pyecharts.charts import Bar, Page, Line, Pie, WordCloud
from pyecharts.globals import CurrentConfig

# CurrentConfig.ONLINE_HOST = "https://cdn.kesci.com/lib/pyecharts_assets/"

CurrentConfig.ONLINE_HOST = "http://127.0.0.1:5000/static/js/"

dbConn = sqlite3.connect('./db.sqlite3')

######################################################################################
# 柱状图
# 列出小区二手房均价最高的和最低的10个小区
# x:小区名称 y:二手房总价
top10_sql = 'select avg(price) as avgprice, position from house_data group by position order by avgprice desc limit 0,10'
cursor = dbConn.execute(top10_sql)

top10_position = []
top10_price = []
for it in cursor:
    price = it[0]
    position = it[1]

    top10_position.append(position)
    top10_price.append(price)

top10Bar = (Bar(init_opts=opts.InitOpts(width='1000px', height='600px'))
            .set_global_opts(title_opts=opts.TitleOpts(title="二手房均价最高的10个小区"),
                             yaxis_opts=opts.AxisOpts(name='价格(万)'),
                             xaxis_opts=opts.AxisOpts(name='小区', axislabel_opts={'rotate': 30}),
                             )
            .add_xaxis(top10_position)
            .add_yaxis('', top10_price)
            )

low10_sql = 'select avg(price) as avgprice, position from house_data group by position order by avgprice asc limit 0,10'
cursor = dbConn.execute(low10_sql)
low10_position = []
low10_price = []
for it in cursor:
    price = it[0]
    position = it[1]

    low10_position.append(position)
    low10_price.append(price)

barLow10 = (Bar()
            .set_global_opts(title_opts=opts.TitleOpts(title="二手房均价最低的10个小区"), yaxis_opts=opts.AxisOpts(name='价格(万)'),
                             xaxis_opts=opts.AxisOpts(name='小区', axislabel_opts={'rotate': 30}))
            .add_xaxis(low10_position)
            .add_yaxis('', low10_price)
            )
page = Page(layout=Page.DraggablePageLayout)
page.add(top10Bar, barLow10)
page.render('./templates/bar.html')

######################################################################################
# 折线图
# x:面积 y:二手房单价
cursor = dbConn.execute("SELECT price, area from house_data order by area ASC limit 0,20")
# cursor = dbConn.execute("select avg(price) as avgprice, area from house_data group by area order by avgprice asc limit 0,20")

data_area = []
data_price = []

for it in cursor:
    price = it[0]
    area = it[1]

    data_area.append(area)
    data_price.append(price)
line = (Line()
        .set_global_opts(title_opts=opts.TitleOpts(title="面积-价格"),
                         yaxis_opts=opts.AxisOpts(name='价格(万)'),
                         xaxis_opts=opts.AxisOpts(name='面积(平米)'))
        .add_xaxis(data_area)
        .add_yaxis('', data_price)
        )
line.render('./templates/line.html')
######################################################################################


######################################################################################
# 折线图
# x:建成时间 y:二手房单价
cursor = dbConn.execute("SELECT price, buildtime from house_data order by buildtime ASC limit 0, 20")
# cursor = dbConn.execute("select avg(price) as avgprice, buildtime from house_data group by buildtime order by avgprice asc limit 0,20")

data_buildtime = []
data_price = []

for it in cursor:
    price = it[0]
    buildtime = it[1]

    data_buildtime.append(buildtime)
    data_price.append(price)
line = (Line()
        .set_global_opts(title_opts=opts.TitleOpts(title="建成时间-价格"),
                         yaxis_opts=opts.AxisOpts(name='价格(万)'),
                         xaxis_opts=opts.AxisOpts(name='建成时间(年)'))
        .add_xaxis(data_buildtime)
        .add_yaxis('', data_price)
        )
line.render('./templates/line2.html')
######################################################################################

######################################################################################
# 饼图
# 装修情况 二手房单价
cursor = dbConn.execute("SELECT * from house_data limit 0,20")

data_buildstatus = []
data_price = []

for it in cursor:
    data_buildstatus.append(it[6])
    data_price.append(it[2])

pie = Pie()
data_pair = [list(z) for z in zip(data_buildstatus, data_price)]
pie.add("", data_pair=data_pair)

pie.set_global_opts(title_opts=opts.TitleOpts(title="装修情况-价格"))

pie.render('./templates/pie.html')

######################################################################################


######################################################################################
# 饼图
# 户型 二手房单价
cursor = dbConn.execute("SELECT * from house_data limit 0,20")

data_housetype = []
data_price = []

for it in cursor:
    data_housetype.append(it[7])
    data_price.append(it[2])

pie = Pie()
data_pair = [list(z) for z in zip(data_housetype, data_price)]
pie.add("", data_pair=data_pair)

pie.set_global_opts(title_opts=opts.TitleOpts(title="户型-价格"))

pie.render('./templates/pie2.html')

######################################################################################

######################################################################################
# 词云图

cursor = dbConn.execute("SELECT * from house_data limit 0,200")

text = []

for it in cursor:
    data = re.sub('\W*', '', it[0])
    text.extend(jieba.cut(data, cut_all=False))

wordcloud = WordCloud()
counter = Counter(text)

data = []
for key, value in counter.items():
    data.append((key, value))

wordcloud.add("", data_pair=data, word_size_range=[1, 100])
wordcloud.render("./templates/wordcloud.html")

######################################################################################

dbConn.close()