'''
Created on 17. maj 2018

@author: robert.niyo
'''

import sys
import pymysql.cursors
from PyQt5.QtWidgets import*
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.Qt import center
from PyQt5 import QtSql


cnx= pymysql.connect(user ='root',
                      password= '1234',
                      host='127.0.0.1',
                      database='Normal')
cursor = cnx.cursor()

class window(QWidget):
    def __init__(self):
        super().__init__()
        self.title = 'Normal konkurrenter'
        self.left = 10
        self.top = 10
        self.width = 800
        self.height = 400
        self.initUI()

    def initUI(self):
        self.setWindowTitle(self.title)
        self.setGeometry(self.left, self.top, self.width, self.height)

        self.grid = QVBoxLayout(self)
        self.tabs = QTabWidget()
        self.tab1 = QWidget()
        self.tab2 = QWidget()
        self.tabs.addTab(self.tab1, "Tysk Database",)
        self.tabs.addTab(self.tab2, "Tilføj ny butik")

        self.createTable1()
        self.tab1.grid = QVBoxLayout(self)
        self.label = QLabel("Butikker")
        self.loadButton = QPushButton("Hent data")
        self.loadButton.clicked.connect(self.loadSQLData)
        self.tab1.grid.addWidget(self.label)
        self.tab1.grid.addWidget(self.loadButton)
        self.tab1.grid.addWidget(self.tableWidget1)
        self.tab1.setLayout(self.tab1.grid)
        self.label=QLabel(self)
        self.label.setPixmap(QPixmap('normal.png'))
        self.label.setGeometry(10,10,800,400)

        name = QLabel("Butiks navn:")
        zipcode = QLabel("Post nr:")
        city = QLabel("By:")
        adress = QLabel("Adresse:")

        self.nameEdit = QLineEdit()
        self.nameEdit.setPlaceholderText('Skriv butik navn')
        self.nameEdit.setMaximumWidth(150)
        self.zipcodeEdit = QLineEdit()
        self.zipcodeEdit.setPlaceholderText('Skriv postnr')
        self.zipcodeEdit.setMaximumWidth(150)
        self.cityEdit = QLineEdit()
        self.cityEdit.setPlaceholderText('Skriv by navn')
        self.cityEdit.setMaximumWidth(150)
        self.adressEdit = QLineEdit()
        self.adressEdit.setPlaceholderText('Skriv butiks adresse')
        self.adressEdit.setMaximumWidth(150)

#         varaiblenavn = self.nameEdit.text()
#         self.nameEdit.clear()

        self.tab2.grid = QGridLayout()
        self.tab2.grid.setSpacing(10)

        self.tab2.grid.addWidget(name, 1, 0, 1, 2, Qt.AlignRight)
        self.tab2.grid.addWidget(self.nameEdit, 1, 3, 1, 2, Qt.AlignLeft)
        self.tab2.setLayout(self.tab2.grid)

        self.tab2.grid.addWidget(zipcode, 2, 0, 1, 2, Qt.AlignRight)
        self.tab2.grid.addWidget(self.zipcodeEdit, 2, 3, 1, 2, Qt.AlignLeft)
        self.tab2.setLayout(self.tab2.grid)

        self.tab2.grid.addWidget(city, 3, 0, 1, 2, Qt.AlignRight)
        self.tab2.grid.addWidget(self.cityEdit, 3, 3, 1, 2, Qt.AlignLeft)
        self.tab2.setLayout(self.tab2.grid)

        self.tab2.grid.addWidget(adress, 4, 0, 1, 2, Qt.AlignRight)
        self.tab2.grid.addWidget(self.adressEdit, 4, 3, 1, 2, Qt.AlignLeft)
        self.tab2.setLayout(self.tab2.grid)



        self.saveButton = QPushButton("Gem", self)
        self.saveButton.clicked.connect(self.AddData)
        self.tab2.grid.addWidget(self.saveButton, 9, 1, 1, 2, Qt.AlignRight)

        self.grid.addWidget(self.tabs)
        self.setLayout(self.grid)

        self.setGeometry(self.left, self.top, self.width, self.height)
        self.show()

#     def clickMethod(self):
#         print( + self.line.text())
    def createTable1(self):
        self.object1 = QWidget()
        self.object1.setObjectName("object")
        self.tableWidget1 = QTableWidget(self.object1)
        self.tableWidget1.setObjectName("tableWidget")
        self.tableWidget1.setEditTriggers(QAbstractItemView.NoEditTriggers)

    def loadSQLData(self):
        cursor.execute("SELECT* FROM tyskkonkurrenter")
        results=cursor.fetchall()

#         self.tableWidget1.setRowCount(0)
        self.tableWidget1.setColumnCount(5)

        item = QTableWidgetItem("Butiks navn")
        self.tableWidget1.setHorizontalHeaderItem(0, item)
        item = QTableWidgetItem("Post nr.")
        self.tableWidget1.setHorizontalHeaderItem(1, item)
        item = QTableWidgetItem("By")
        self.tableWidget1.setHorizontalHeaderItem(2, item)
        item = QTableWidgetItem("Adresse")

        self.tableWidget1.setHorizontalHeaderItem(3, item)

        for row_number, row_data in enumerate(results):
            self.tableWidget1.insertRow(row_number)
            for column_number, data in enumerate(row_data):
                self.tableWidget1.setItem(row_number, column_number, QTableWidgetItem(str(data)))


#         self.tableWidget1.setColumnWidth(0,50) #Butik id
        self.tableWidget1.setColumnWidth(0,250) #Butiksnavn
        self.tableWidget1.setColumnWidth(1,80) #Postnr
        self.tableWidget1.setColumnWidth(2,150) #By
        self.tableWidget1.setColumnWidth(3,300)


    def AddData(self):

        with cnx:

            cursor.execute("INSERT INTO tyskkonkurrenter(Navne_Butik,Postnr,By_Navn, Adresse)"
                    "VALUES ( ' %s', '%s', '%s', '%s')" % (''.join(self.nameEdit.text()),
                                                ''.join(self.zipcodeEdit.text()),
                                                ''.join(self.cityEdit.text()),
                                                ''.join(self.adressEdit.text())))
            QMessageBox.about(self,'connection','Data indsættes i databassen. klik på knap Tysk database for at se det' )
            self.close()

    def closeEvent(self, event):
        close = QMessageBox.question(self,
                                     "QUIT",
                                     "Sikker?",
                                      QMessageBox.Yes | QMessageBox.No)
        if close == QMessageBox.Yes:
            event.accept()
        else:
            event.ignore()



if __name__ == '__main__':

    app = QApplication(sys.argv)
    Window = window()
    sys.exit(app.exec_())
    cnx.close()