import json
from bokeh.models.layouts import LayoutDOM, Row, Column, Spacer, WidgetBox
from bokeh.models.widgets import Widget
import urllib
import pandas as pd
import numpy as np
import pymssql
import matplotlib as plt
from bokeh.io import gridplot

plt.interactive(False)
from datetime import datetime, timedelta
from bokeh.plotting import figure
from bokeh.models import NumeralTickFormatter, Legend
from runpy import run_path
# import GetData
from bokeh.plotting import figure, show, output_file, ColumnDataSource
from bokeh.resources import CDN
from bokeh.embed import file_html
from bokeh.models import PanTool, WheelZoomTool, BoxZoomTool, ResetTool, LassoSelectTool, SaveTool, CrosshairTool, \
    HoverTool, Label
from urllib import urlopen

server = "marketxls.cnrjxpjg4y05.us-east-2.rds.amazonaws.com"
print server
user = "marketxls"
password = "YgKvgthSDX20"
cnxn = pymssql.connect(server, user, password, "marketxls")


def mtext(p, x, y, text, text_color):
    p.text(x, y, text=[text],
           text_color=text_color, text_align="center", text_font_size="8pt", alpha=0.9, text_font="PT Sans")


def mtext_white(p, x, y, text):
    p.text(x, y, text=[text],
           text_color="black", text_align="center", text_font_size="7pt")


def mtext1(p, x, y, text):
    p.text(x, y, text=[text],
           text_color="black", text_align="center", text_font_size="10pt", alpha=0.9)


Returns_Data_query = """
SELECT [ticker_symbol]
      ,[Five_Year_Price]
      ,[Five_Year_Date]
      ,[Three_Year_Price]
      ,[Three_Year_Date]
      ,[One_Year_Price]
      ,[One_Year_Date]
      ,[Six_Month_Price]
      ,[Six_Month_Date]
      ,[Three_Month_Price]
      ,[Three_Month_Date]
      ,[One_Month_Price]
      ,[One_Month_Date]
      ,[Fifteen_Day_Price]
      ,[Fifteen_Days_Date]
      ,[Seven_Day_Price]
      ,[Seven_Days_Date]
      ,[One_Day_Price]
      ,[One_Day_Date]
  FROM [marketxls].[dbo].[v_ticker_price_date_for_returns1]"""

snp_symbols_q = """
  SELECT [Symbol]
      ,[Name]
      ,[ticker_industry]

FROM [marketxls].[dbo].[snp500]
INNER JOIN [marketxls].[dbo].[ticker_detail] ON [Symbol] =[ticker_symbol];

"""

Returns_Data = pd.read_sql(Returns_Data_query, cnxn)
ticker_data = pd.read_sql(snp_symbols_q, cnxn)
ticker_data.columns = ['ticker_symbol', 'Name', 'Industry']
# print ticker_data

newdf = pd.merge(ticker_data, Returns_Data, on='ticker_symbol')
# print newdf

newdf['Seven_Day_Return'] = (newdf['One_Day_Price'] - newdf['Seven_Day_Price']) / newdf['Seven_Day_Price']
newdf['Fifteen_Day_Return'] = (newdf['One_Day_Price'] - newdf['Fifteen_Day_Price']) / newdf['Fifteen_Day_Price']
newdf['Three_Month_Return'] = (newdf['One_Day_Price'] - newdf['Three_Month_Price']) / newdf['Three_Month_Price']
newdf['Six_Month_Return'] = (newdf['One_Day_Price'] - newdf['Six_Month_Price']) / newdf['Six_Month_Price']
newdf['One_Year_Return'] = (newdf['One_Day_Price'] - newdf['One_Year_Price']) / newdf['One_Year_Price']
newdf['Three_Year_Return'] = (newdf['One_Day_Price'] - newdf['Three_Year_Price']) / newdf['Three_Year_Price']
newdf['Five_Year_Return'] = (newdf['One_Day_Price'] - newdf['Five_Year_Price']) / newdf['Five_Year_Price']

cols_to_keep = ['ticker_symbol', 'Name', 'Industry', 'Seven_Day_Return',
                'Fifteen_Day_Return', 'Three_Month_Return', 'Six_Month_Return',
                'One_Year_Return', 'Three_Year_Return', 'Five_Year_Return']
data = newdf[cols_to_keep].dropna()
data.index = np.arange(len(data))

plots = []
for category in cols_to_keep[3:9]:
    print category

    width = 350
    gap = width / float(len(data.index))
    colors = ["red", "brown", "pink", "purple",
              "orange", "red", "blue", "green"]
    category_data = data[category]

    tools = [HoverTool(tooltips=[
        ("Company name", "@name"),
        ("Value", "@percent"),
    ]), PanTool(), WheelZoomTool(),
        BoxZoomTool(), ResetTool(),
        LassoSelectTool(), SaveTool(),
        CrosshairTool()]

    p = figure(plot_width=350, plot_height=250,
               title=category.replace('_', ' '),
               responsive=True, tools=tools)
    p.grid.grid_line_alpha = 0.3
    p.yaxis.axis_label = 'Return %'
    p.xaxis.visible = False
    # add a new formatter to y axis for percent view
    p.yaxis[0].formatter = NumeralTickFormatter(format="0.00%")
    p.title.align = 'center'

    # divide rendering of text labels and circles with tooltips
    # because not necessary to add circles in loop,
    # when there is data source
    pos = 0
    for i in range(len(data)):
        mtext(p, pos, category_data[i],
              data['ticker_symbol'][i],
              colors[len(plots)])
        pos += gap

    # data source is a wrapper on DataFrame or any other structures, which
    # can be used as a param to DataSource constructor
    # so now add a dict, but can add a DataFrame from this dict
    source = ColumnDataSource(
        data=dict(
            name=data['Name'],
            percent=(category_data * 100).astype('int32').apply(str).apply(lambda x: x + '%'),
            x=np.arange(0, width, gap),
            y=category_data
        )
    )
    # when add a data source, there are 2 ways:
    #   - add own data for x and y axis
    #   - just prompt needed columns from data source
    p.circle(x='x', y='y', size=15, color='black', source=source, alpha = 0)

    plots.append(p)

mygplot = gridplot([plots[0], plots[1],
                    plots[2], plots[3],
                    plots[4], plots[5]],
                   ncols=3, plot_width=330, plot_height=240,
                   toolbar_location='right', merge_tools=True, responsive=True)

html = file_html(mygplot, CDN, "Returns Screener")

with open('returns_screener.html', 'w+') as f:
    f.write(html)