from pymystem3 import Mystem
import time
import configparser
import pymysql
import datetime


from datetime import date

import pylab
import matplotlib.dates

import numpy as np

try:

    conn = pymysql.connect(host='127.0.0.1',
                           database='mydb',
                           user='root',
                           password='565326',
                           autocommit=True,
                           use_unicode=True,
                           charset="utf8")
except IOError:
    print("file %s not exist")


good = ["положительные","неплохой","удобный","хороший подобранный","спокойный","преуспевающий","преуспели","преуспевать","величественный",
        "удачливый","доброжелательный","милый","красивый","классный","прикольный","испольуемый","чудесный","проработанный",
        "лучший","симпатичный","приятный","необходимый","удивительный","удивлять","благодарный","благодарить","благодарность",
        "понравившийся","понравиться","эффективный", "эффективность",
        "понятный","оригинальный","наилучший","изумлять", "раработатья","новинка","новый", "дешевле",
        "запускать", "открывать", "запуск","запустил", "создавать","понравилось","пользуемся","выпустит",
        "прибавили","любопытный", "изумленный","продемонстрировал","представил","оптимизация","выпущен","крутые"]


negative = ["осуждаемый","прокомментирванный","прокомментировал","коментировать","занимательный","обсуждаемый",
           "печальный","осудил","бесстрастный","бесчувственный","проклятый","украденный","воровать","сворованный","своровать","обвиняемый","обвинять",
            "заподозренный","подозревать","подозреваемый","непонятный","непонял","виновный","осуждаемый","раздражительный","раздражать"
            ,"украденный","заподозренный",
            "недоделанный","виновный","банальный","нереализованный","плохой","ужасный","сломанный","сломать","спамящий","спамить",
            "худший","надоевший", "ненадежный","трудный","обвинения","дольше","компромат","раздор","шпионский", "дороже","потребует","требует","платным","заблокировал","позор","тупой","добивают","ущербное"]

good1 = ["приток: валют 1", "повышение : цен на нефть 1; курса рубля 1", "динамика: положительная 1" , "увеличилась: доля экспорта 1",
         "отмена: санкций 1", "ревальвация 1"]
negative1 = ["падение: цен на нефть 1", "санкции 1", "падение : резкое 1", "девальвация", "зависимость : сырьевая 1",
             "динамика: отрицательная 1", "доля : экспорта уменьшилась 1", "колебания : курса 1"]


m = Mystem()




def StartDictionarry():


    for line in negative:
        rules = line.split(":")

        j = rules.__len__()
        if j >= 2:
            checkStr = m.lemmatize(rules[0])[0] + " :"
            splRules = (rules[1]).split(" ")
            for word in splRules:
                if word.isalpha():
                    checkStr = checkStr +" "+ m.lemmatize(str(word))[0]
                else:
                    checkStr = checkStr + " " + str(word)
        else:
            checkStr = m.lemmatize(rules[0])[0] + " 1"
        cur = conn.cursor()

        cur.execute("SELECT Word FROM mydb.Dictionary Where Word =" + "'" + checkStr + "'" ";")

        numrows = int(cur.rowcount) #kolvo sstrok
        # проверка на выборку по данному слову что она пуста
        if (numrows == 0):
            cur.execute( "insert into mydb.Dictionary (Word,Weight,Emotion,Resoursename_id) Values (" + "'"+checkStr+"'" + ",1,false,1);")




    for line in good:
        rules = line.split(":")

        j = rules.__len__()
        if j >= 2:
            checkStr = m.lemmatize(rules[0])[0] + " :"
            splRules = (rules[1]).split(" ")
            for word in splRules:
                if word.isalpha():
                    checkStr = checkStr + m.lemmatize(str(word))[0]
                else:
                    checkStr = checkStr + " " + str(word)
        else:
            checkStr = m.lemmatize(rules[0])[0] + " 1"
        cur.execute("SELECT Word FROM mydb.Dictionary Where Word =" + "'" + checkStr + "'" ";")

        numrows = int(cur.rowcount)  # kolvo sstrok
            # проверка на выборку по данному слову что она пуста
        if (numrows == 0):
            cur.execute( "insert into mydb.Dictionary (Word,Weight,Emotion,Resoursename_id) Values (" + "'"+checkStr+"'" + ",1,true,1);")



    for line in negative1:
        rules = line.split(":")

        j = rules.__len__()
        if j >= 2:
            checkStr = m.lemmatize(rules[0])[0] + " :"
            splRules = (rules[1]).split(" ")
            for word in splRules:
                if word.isalpha():
                    checkStr = checkStr + " " + m.lemmatize(str(word))[0]
                else:
                    checkStr = checkStr + " " + str(word)
        else:
            checkStr = m.lemmatize(rules[0])[0] + " 1"

        cur.execute("SELECT Word FROM mydb.Dictionary Where Word =" + "'" + checkStr + "'" ";")

        numrows = int(cur.rowcount)  # kolvo sstrok
            # проверка на выборку по данному слову что она пуста
        if (numrows == 0):
            cur.execute( "insert into mydb.Dictionary (Word,Weight,Emotion,Resoursename_id) Values (" + "'"+checkStr+"'" + ",1,false,2);")


    for line in good1:
        rules = line.split(":")

        j = rules.__len__()
        if j >= 2:
            checkStr = m.lemmatize(rules[0])[0] + " :"
            splRules = (rules[1]).split(" ")
            for word in splRules:
                if word.isalpha():
                    checkStr = checkStr + m.lemmatize(str(word))[0]
                else:
                    checkStr = checkStr + " " + str(word)
        else:
            checkStr = m.lemmatize(rules[0])[0] + " 1"

        cur.execute("SELECT Word FROM mydb.Dictionary Where Word =" + "'" + checkStr + "'" ";")

        numrows = int(cur.rowcount)  # kolvo sstrok
        # проверка на выборку по данному слову что она пуста
        if (numrows == 0):
            cur.execute( "insert into mydb.Dictionary (Word,Weight,Emotion,Resoursename_id) Values (" + "'"+checkStr+"'" + ",1,true,2);")

# Init


def addNewGoodemotian(word,resoursename):
        rules = word.split(":")

        cur = conn.cursor()
        id_resourse = 0

        cur.execute("SELECT * FROM mydb.ResourseName where CompanyName = " + "'" + resoursename + "'" + ";")
        numrows = int(cur.rowcount)
        result = cur.fetchall()
        if (numrows > 0):
            for line in result:
                id_resourse = line[1]
        else:
            return





        j = rules.__len__()
        if j >= 2:
            checkStr = m.lemmatize(rules[0])[0] + " :"
            splRules = (rules[1]).split(" ")
            for word2 in splRules:
                if word2.isalpha():
                    checkStr = checkStr + m.lemmatize(str(word2))[0]
                else:
                    checkStr = checkStr + " " + str(word2)
        else:
            checkStr = m.lemmatize(rules[0])[0] + " 1"
        cur.execute("SELECT Word FROM mydb.Dictionary Where Word =" + "'" + checkStr + "'" ";")

        numrows = int(cur.rowcount)  # kolvo sstrok
        # проверка на выборку по данному слову что она пуста
        if (numrows == 0):
            cur.execute( "insert into mydb.Dictionary (Word,Weight,Emotion,Resoursename_id) Values (" + "'"+checkStr+"'" + ",1,true, + " + "'"+ id_resourse.__str__() +"'" + ");")


def addNewBademotian(word,resoursename):
    cur = conn.cursor()
    id_resourse = 0

    cur.execute("SELECT * FROM mydb.ResourseName where CompanyName = " + "'" + resoursename + "'" + ";")
    numrows = int(cur.rowcount)
    result = cur.fetchall()
    if (numrows > 0):
        for line in result:
            id_resourse = line[1]
    else:
        return


    rules = word.split(":")

    j = rules.__len__()
    if j >= 2:
        checkStr = m.lemmatize(rules[0])[0] + " :"
        splRules = (rules[1]).split(" ")
        for word2 in splRules:
            if word2.isalpha():
                checkStr = checkStr + m.lemmatize(str(word2))[0]
            else:
                checkStr = checkStr + " " + str(word2)
    else:
        checkStr = m.lemmatize(rules[0])[0] + " 1"
        cur = conn.cursor()

    cur.execute("SELECT Word FROM mydb.Dictionary Where Word =" + "'" + checkStr + "'" ";")

    numrows = int(cur.rowcount)  # kolvo sstrok
    # проверка на выборку по данному слову что она пуста
    if (numrows == 0):
        cur.execute(
            "insert into mydb.Dictionary (Word,Weight,Emotion,Resoursename_id) Values "
            "(" + "'" + checkStr + "'" + ",1,false, + " + "'" + id_resourse.__str__() + "'" + ");")

def getTypeAction(tweetWord, Resoursename):

    word = ""

    if (tweetWord):

        word = m.lemmatize(tweetWord)[0]
        cur = conn.cursor()
        cur.execute("SELECT * FROM mydb.ResourseName where CompanyName = " + "'" + Resoursename + "'" + ";")
        numrows = int(cur.rowcount)
        result = cur.fetchall()
        if (numrows > 0):
            for line in result:
                id_resourse = line[1]
        else:
            print("resourse, not found")
            return

        checkstr = id_resourse.__str__()

        cur.execute("SELECT Word FROM mydb.Dictionary where emotion = 0 and Resoursename_id = " + "'" + checkstr + "'" + ";")

        result = cur.fetchall()

        for line in result:
            list = line[0].split(" ")
            if ((list)[0] == word):
                if ((list)[1] == ":"):
                    j = list.__len__()
                    i = 2
                    str = ""
                    while i <= j - 2:
                        str = str + list[i] + " "
                        i = i + 1
                    str = str + list[i]
                    return  [4,str]  #правило негатив
                return [3,list[1]]

        cur = conn.cursor()


        cur.execute("SELECT Word FROM mydb.Dictionary where emotion = 1 and Resoursename_id = " + "'" + checkstr + "'" +  ";")

        result = cur.fetchall()

        for line in result:
            list = line[0].split(" ")
            if ((list)[0] == word):
                if ((list)[1] == ":"):
                    j = list.__len__()
                    i = 2
                    str = ""
                    while i <= j - 2:
                        str = str + list[i] + " "
                        i = i + 1
                    str = str + list[i]
                    return [2, str]  #правило позитив
                return [1,list[1]]

    return [5, 0]





def treatmentText(text2):

    mytext = text2

    mytext = mytext.replace("  ", " ")
    mytext = mytext.replace(",", "")
    mytext = mytext.replace('.', '')
    mytext = mytext.replace("!","")
    mytext = mytext.replace("?","")
    mytext = mytext.replace('"', "")
    mytext = mytext.replace("'", "")
    mytext = mytext.replace(":", "")
    mytext = mytext.replace(";", "")
    mytext = mytext.replace("»", "")
    mytext = mytext.replace("«", "")
    mytext = mytext.replace("#", "")


    return mytext.lower()


def delToken(split_str: object) -> object:

    extend  = ["в", "с", "по","на"]
    i = 0
    tokenMass = list (split_str)

    deldate = False

    for element in split_str:
        for bad in extend:

            if element == bad:
                    del tokenMass[i]
                    deldate = True

        if not(deldate):
            i = i + 1
        deldate = False

    return(tokenMass)



def plotAllgraph(StartDate,EndDate,Resoursename):
    #Date in str "%Y,%m,%d"


    str = StartDate.split(",")
    if not (str.__len__() == 3):
        print("icorrect date")
        return
    try:
        start = datetime.datetime(int(str[0]), int(str[1]), int(str[2]))
    except:
        print(print("icorrect date"))
        return
    t1 = start
    str = EndDate.split(",")
    if not (str.__len__() == 3):
        print("icorrect date")
        return

    try:
        last = datetime.datetime(int(str[0]), int(str[1]), int(str[2]))
    except:
        print(print("icorrect date"))
        return
    t = start

    cur = conn.cursor()
    cur.execute("SELECT * FROM mydb.ResourseName where CompanyName = " + "'" + Resoursename + "'" + ";")
    numrows = int(cur.rowcount)
    result = cur.fetchall()
    if (numrows > 0):
        for line in result:
            id_resourse = line[1]
    else:
        return
    x1 = []
    x2 = []
    y1 = []
    y2 = []
    while (t < last):

        t = t1
        try:
            t1 = t.replace(t.year, t.month, t.day + 1)
        except:
            try:
                t1 = t.replace(t.year, t.month + 1, 1)
            except:
                try:
                    t1 = t.replace(t.year + 1, 1, 1)
                except:
                    print("incorrext date")
                    return

        #data id + resourse id
        cur = conn.cursor()

        cur.execute("SELECT * FROM mydb.Date where Datecol = " + "'" + t.strftime("%Y-%m-%d") + "'" " ;")
        numrows = int(cur.rowcount)
        result = cur.fetchall()
        if (numrows > 0):
            for line in result:
                id_Date = line[0]  # значит есть такая дата для ресурса или сообщения

            cur2 = conn.cursor()  # for Date
            cur2.execute("SELECT * FROM mydb.ListResourse where ResourseName_id ="+ "'"+
                               id_resourse.__str__()+ "'" +  "and Date_idDate ="+ "'"+ id_Date.__str__()+ "'" +   ";")

            result = cur2.fetchall()
            numrows = int(cur2.rowcount)
            if (numrows > 0):
                for line in result:
                    PrintDate = line[1]
                    cur.execute("SELECT * FROM mydb.Resourse where idResourse = "+ "'"+ PrintDate.__str__() + "'" +";")
                    result1 = cur.fetchall()
                    numrows = int(cur.rowcount)
                    if (numrows > 0):
                        for line in result1:
                            PrintDate = float(line[3])  # график этих значений и даты
                            x1.append(t)
                            y1.append(PrintDate)



            cur = conn.cursor() # for message
            cur.execute("SELECT * FROM mydb.Messages where Resourse_name ="+ "'"+
                              id_resourse.__str__()+ "'" +  "and Date_idDate ="+ "'"+ id_Date.__str__()+ "'" +   ";")
            result = cur.fetchall()
            numrows = int(cur.rowcount)
            Emotion = 0;
            i = 0;
            if (numrows > 0):
                for line in result:
                    if not((float(line[3])) == 0):
                        Emotion = Emotion + (float(line[3]))
                        i = i + 1
                if (i > 0):
                    PrintNumb = Emotion/i   #график этих значений и даты / как лучше делать динам массивы или что?
                    x2.append(t)
                    y2.append(PrintNumb)


    xdata_float = matplotlib.dates.date2num(x1)
    axes = pylab.subplot(1, 1, 1)

    # Пусть в качестве меток по оси X выводится только месяцы и дни


    # Отобразим данные
    pylab.plt.figure(1)
    pylab.plt.subplot(211)
    pylab.plt.plot(x1, y1)
    pylab.plt.grid(True)

    pylab.plt.subplot(212)
    pylab.plt.plot(x2, y2, 'r--')
    pylab.plt.grid(True)
    pylab.plt.show()




plotAllgraph ("2017,4, 15","2017, 5, 24","Яндекс")

from urllib.request import urlopen # Импортировали библиотеку для работы с адресами и запросами Internet.
from pandas import DataFrame, read_csv
from pandas import set_option
import datetime
import textAnalysis
import csv
import codecs
import matplotlib

def DowResourse(StartDate,EndDate,ResourseName):
    str = StartDate.split(",")
    if not (str.__len__() == 3):
        print("icorrect date")
        return
    try:
        start = datetime.datetime(int(str[0]), int(str[1]), int(str[2]))
    except:
        print(print("icorrect date"))
        return

    str = EndDate.split(",")
    if not (str.__len__() == 3):
        print("icorrect date")
        return

    try:
        last = datetime.datetime(int(str[0]), int(str[1]), int(str[2]))
    except:
        print(print("icorrect date"))
        return


    cur = textAnalysis.conn.cursor()
    id_resourse = 0

    cur.execute("SELECT * FROM mydb.ResourseName where CompanyName = " + "'" + ResourseName + "'" + ";")
    numrows = int(cur.rowcount)
    result = cur.fetchall()
    if (numrows > 0):
        for line in result:
            id_resourse = line[1]
    else:
        return

    #resourse checked

    Begin = start.strftime("%d.%m.%Y")
    End = last.strftime("%d.%m.%Y")

    Beginstr = Begin.split(".")
    Endstr = End.split(".")

    if (id_resourse == 1):
        url = "http://export.finam.ru/YNDX_170502_170512.csv?market=1&em=388383&code=YNDX&apply=0&df=" + Beginstr[0] + "&mf=" + (int(Beginstr[1]) - 1).__str__() +  "&yf=" + Beginstr[2] + "&from="+ Begin + "&dt=" + Endstr[0] + "&mt=" + (int(Endstr[1]) - 1).__str__() + "&yt=" + Endstr[2] +"&to=" + End + "&p=8&f=YNDX_170502_170512&e=.csv&cn=YNDX&dtf=1&tmf=2&MSOR=1&mstime=on&mstimever=1&sep=1&sep2=1&datf=5&at=1"
    elif (id_resourse == 2):
        url = "http://export.finam.ru/CNY000000TOD_170502_170530.csv?market=45&em=182402&code=CNY000000TOD&apply=0&df=" + Beginstr[0] + "&mf=" + (int(Beginstr[1]) - 1).__str__() +  "&yf=" + Beginstr[2] + "&from="+ Begin + "&dt=" + Endstr[0] + "&mt=" + (int(Endstr[1]) - 1).__str__() + "&yt=" + Endstr[2] +"&to=" + End + "&p=7&f=CNY000000TOD_170502_170530&e=.csv&cn=CNY000000TOD&dtf=1&tmf=2&MSOR=1&mstime=on&mstimever=1&sep=1&sep2=1&datf=5&at=1"
    elif (id_resourse == 3):
        url = "http://export.finam.ru/EUR_RUB__TOD_170502_170530.csv?market=45&em=182441&code=EUR_RUB__TOD&apply=0&df=" + Beginstr[0] + "&mf=" + (int(Beginstr[1]) - 1).__str__() +  "&yf=" + Beginstr[2] + "&from="+ Begin + "&dt=" + Endstr[0] + "&mt=" + (int(Endstr[1]) - 1).__str__() + "&yt=" + Endstr[2] +"&to=" + End + "&p=8&f=EUR_RUB__TOD_170502_170530&e=.csv&cn=EUR_RUB__TOD&dtf=1&tmf=2&MSOR=1&mstime=on&mstimever=1&sep=1&sep2=1&datf=5&at=1"

    ftpstream = urlopen(url)
    csvfile = csv.reader(codecs.iterdecode(ftpstream, 'utf-8'))





    next(csvfile, None)



    for line in csvfile:  ## запись файла в бд

        print(Convert(line[0]),float((line[5])))

        cur = textAnalysis.conn.cursor()

        cur.execute("SELECT * FROM mydb.Date where Datecol = " + "'" + Convert(line[0])+ "'" " ;")
        numrows = int(cur.rowcount)
        result = cur.fetchall()
        if (numrows > 0):
            for line1 in result:
                id_Date = line1[0]
        else:
            cur.execute("insert into mydb.Date (Datecol) Values (" + "'" + Convert(line[0])+ "'" +  ");")
            cur = textAnalysis.conn.cursor()
            cur.execute("SELECT * FROM mydb.Date where Datecol = " + "'" + Convert(line[0]) + "'"+" ;")
            result = cur.fetchall()
            if (numrows > 0):
                for line1 in result:
                    id_Date = line1[0]

        #Добавить данные в бд

        #Проверить наличие подобного столбца, добавить в лист данные
        cur.execute("select *from mydb.ListResourse where ResourseName_id =" + "'" +id_resourse.__str__() + "'" +
                    "and Date_idDate ="+ "'" +id_Date.__str__() + "'" + ";")
        numrows = int(cur.rowcount)
        if (numrows == 0):

            cur.execute("SELECT * FROM mydb.Resourse where cmp_float(high," + "'" + (float(line[3])).__str__() + "'" +
                        ") and cmp_float(low," + "'" + (float(line[4])).__str__() + "'" +  ") and cmp_float(Close_value," + "'" +
                        (float(line[5])).__str__() + "'" +  ") and cmp_float(Open_value," + "'" + (float(line[2])).__str__() + "'" + ");")

            numrows2 = int(cur.rowcount)
            result = cur.fetchall()
            Id_list = 0
            if (numrows2 > 0):
                for line in result:
                    Id_list = line[0]
            else:
                cur.execute("insert into mydb.Resourse(high, low, Close_value, Open_value) values"
                            "(" + "'" + (float(line[3])).__str__() + "'" + "," + "'" + (float(line[4])).__str__()
                            + "'" + "," + "'" + float((line[5])).__str__() + "'" + "," + "'" + float((line[2])).__str__() + "'" + ")")
                cur = textAnalysis.conn.cursor()

                cur.execute(
                    "SELECT * FROM mydb.Resourse where cmp_float(high," + "'" + (float(line[3])).__str__() + "'" +
                    ") and cmp_float(low," + "'" + (
                    float(line[4])).__str__() + "'" + ") and cmp_float(Close_value," + "'" +
                    (float(line[5])).__str__() + "'" + ") and cmp_float(Open_value," + "'" + (
                    float(line[2])).__str__() + "'" + ");")

                numrows3 = int(cur.rowcount)
                result = cur.fetchall()

                if (numrows3 > 0):
                    for line in result:
                        Id_list = line[0]
            cur.execute("insert into mydb.ListResourse(Resourse_idResourse,ResourseName_id,Date_idDate) values (" + "'" + Id_list.__str__() + "'" + "," + "'" + id_resourse.__str__() + "'" +  "," + "'" + id_Date.__str__() + "'" +  ")")


def Convert(str):
    j = str.__len__()
    i = 0
    newstr = ""
    while (i < j):
        if (i == 3 or i == 5):
            newstr = newstr + str[i] + "-"
        else:
            newstr = newstr + str[i]
        i = i + 1
    return newstr

DowResourse("2017,4, 15","2017, 5, 24","Яндекс")
# нет проверки на поиск вне текущей даты Всё все данные добавленны в Бд