# -*- coding: utf-8 -*-
"""
Created on Wed Mar 21 09:23:51 2018

@author: kazin
"""

import pyodbc as cn
import numpy as np
import pandas as pd
from sklearn import preprocessing

#Connecting to database
server = 'facil.database.windows.net'
database = 'main'
username = 'facildatabase'
password = 'DifficultPassword69.'
driver = '{ODBC Driver 11 for SQL Server}'
cnxn = cn.connect('DRIVER=' + driver + ';SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
print(cnxn)

##Importing Data to dataframes form daatabase
nutsComplete = pd.read_sql('SELECT * from dbo.NutsComplete', con = cnxn)
boltsComplete = pd.read_sql('SELECT * from dbo.BoltsComplete', con = cnxn)
contracts = pd.read_sql('SELECT * from dbo.contracts', con = cnxn)
coatings = pd.read_sql('SELECT * from dbo.coatings', con = cnxn)
geometryCoating = pd.read_sql('SELECT * from dbo.geometryCoating', con = cnxn)
countryCodes = pd.read_sql('SELECT * from dbo.countryCodes', con = cnxn)
rfqPortal = pd.read_sql('SELECT * from dbo.rfqPortal', con = cnxn)

###Exporting dataframes to .csv files
#nutsComplete.to_csv('nutsComplete.csv', sep='\t', encoding='utf-8')
#boltsComplete.to_csv('boltsComplete.csv', sep= '\t', encoding='utf-8')
#contracts.to_csv('contracts.csv', sep= '\t', encoding='utf-8')
#coatings.to_csv('coatings.csv', sep= '\t', encoding='utf-8')
#geometryCoating.to_csv('geometryCoating.csv', sep= '\t', encoding='utf-8')
#countryCodes.to_csv('countryCodes.csv', sep= '\t', encoding='utf-8')


#KPI Calculation for boltsComplete
headers = list(boltsComplete)
KpiBolts =[]
boltsComplete = boltsComplete.astype('float64')

for x in range(len(headers)):
          KpiBolts.append((boltsComplete['Pprice'].corr(boltsComplete[headers[x]].astype(float))))

labels = ['ColumnName','Value']
KPIBolts = pd.DataFrame(np.column_stack([headers, KpiBolts]),
                    columns=['ColumnName','Value'])
##Exporting the KPI calculation boltsComplete  values into .csv
KPIBolts.to_csv('KpiBolts.csv',sep=',',encoding='utf-8')
#Replacing all the nan values with 0
boltsComplete.fillna(0, inplace = True)

##Normalizing the data
scaler = preprocessing.MinMaxScaler(feature_range=(-1,1))
scaled = scaler.fit_transform(boltsComplete)
BoltsNormalizedDataFrame = pd.DataFrame(scaled,columns=headers)

#KPI Calculation for nutsComplete
headers = list(nutsComplete)
KpiNuts =[]
nutsComplete['Has_Washer'] = nutsComplete['Has_Washer'].astype(int)
nutsComplete = nutsComplete.astype('float64')
for x in range(len(headers)):
          KpiNuts.append((nutsComplete['Pprice'].corr(nutsComplete[headers[x]].astype(float))))

labels = ['ColumnName','Value']
KPIN = pd.DataFrame(np.column_stack([headers, KpiNuts]),
                    columns=['ColumnName','Value'])
##Exporting the KPI calculation nutsComplete values into .csv
KPIN.to_csv('KpiNuts.csv',sep=',',encoding='utf-8')

#Replacing all the nan values with 0
nutsComplete.fillna(0, inplace = True)
scaler = preprocessing.MinMaxScaler(feature_range=(-1,1))
scaled = scaler.fit_transform(nutsComplete)
NutsNormalizedDataFrame = pd.DataFrame(scaled, columns = headers)

#Creating matrix for most valuables KPI in Bolts DataFrame
HeadDiameter_matrix = BoltsNormalizedDataFrame['HeadDiameter'].as_matrix()
Length_matrix = BoltsNormalizedDataFrame['Length'].as_matrix()
TotalLength_matrix = BoltsNormalizedDataFrame['TotalLength'].as_matrix()
ShoulderDiameter_matrix = BoltsNormalizedDataFrame['ShoulderDiameter'].as_matrix()
Weight_matrix = BoltsNormalizedDataFrame['Weight'].as_matrix()
TEGWNE_matrix = BoltsNormalizedDataFrame['TEGWNE'].as_matrix()

#Creating matrix for most valuables KPI in Nuts DataFrame
Diameter_matrix = NutsNormalizedDataFrame['Diameter'].as_matrix()
Height_matrix = NutsNormalizedDataFrame['Height'].as_matrix()
TEGWNE_Nuts_matrix = NutsNormalizedDataFrame['TEGWNE'].as_matrix()

####NeuralNetwork implementation
X = np.array((Diameter_matrix, Height_matrix, TEGWNE_Nuts_matrix))
#Input Values are the matrixes so we begin use them in Sigmoid

class Neural_Network(object):
    def __init__(self):
        #paramaters
        self.input = 3
        self.outputSize = 1
        self.hiddenSize = 3

        #Weights
        self.W1 = np.random.randn(self.inputSize, self.hiddenSize)
        self.W2 = np.random.randn(self.hiddenSize, self.outputSize)

        def forward(self, X):
            #Forward propagation
            self.z = np.dot(X, self.W1)
            self.z2 = self.sigmoid(self.z)
            self.z3 = np.dot(self.z2, self.W2)
            o = self.sigmoid(self.z3)
            return o

        def sigmoid(self, s):
            return 1/(1+np.exp(-s))

        def sigmoidPrime(self, s):
            return s* (1 - s)

        def backward(self, X, y, o):
            # backward propgate through the network
            self.o_error = y - o # error in output
            self.o_delta = self.o_error*self.sigmoidPrime(o) # applying derivative of sigmoid to error

            self.z2_error = self.o_delta.dot(self.W2.T) # z2 error: how much our hidden layer weights contributed to output error
            self.z2_delta = self.z2_error*self.sigmoidPrime(self.z2) # applying derivative of sigmoid to z2 error

            self.W1 += X.T.dot(self.z2_delta) # adjusting first set (input --> hidden) weights
            self.W2 += self.z2.T.dot(self.o_delta) # adjusting second set (hidden --> output) weights

        def train (self, X, y):
            o = self.forward(X)
            self.backward(X, y, o)

NN = Neural_Network()
for i in range(1000): # trains the NN 1,000 times
  print ("Input: " + str(X))
  print ("Actual Output: " + str(Y))
  print ("Predicted Output: " + str(NN.forward(X)))
  print ("Loss: " + str(np.mean(np.square(Y - NN.forward(X))))) # mean sum squared loss
  print (" ")
  NN.train(X, y)