{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install mysqlclient\n",
    "import sys\n",
    "from PyQt5.QtWidgets import *\n",
    "from PyQt5.uic import loadUiType\n",
    "import MySQLdb as msd\n",
    "import datetime\n",
    "from xlrd import *\n",
    "from xlsxwriter import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "ui, _ = loadUiType('app_design.ui')\n",
    "loginUi, _ = loadUiType('login.ui')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "class LoginCls(QMainWindow, loginUi):\n",
    "    \n",
    "    def __init__(self):\n",
    "        QMainWindow.__init__(self)\n",
    "        self.setupUi(self)\n",
    "        self.loginBtn.clicked.connect(self.handleLogin)\n",
    "        \n",
    "    def handleLogin(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        loginUsername = self.loginUsername.text()\n",
    "        loginUserPass = self.loginUserPass.text()\n",
    "        \n",
    "        sql = \"SELECT * FROM users\"\n",
    "        self.cur.execute(sql)\n",
    "        data = self.cur.fetchall()\n",
    "        for d in data:\n",
    "            if d[1] == loginUsername and d[3] == loginUserPass:\n",
    "                self.mainWindow = Library()\n",
    "                self.close()\n",
    "                self.mainWindow.show()\n",
    "                break\n",
    "            else:\n",
    "                self.loginError.setText('username or password is invalid...')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Library(QMainWindow, ui):\n",
    "    \n",
    "    def __init__(self):\n",
    "        QMainWindow.__init__(self)\n",
    "        self.setupUi(self)\n",
    "        self.buttons()\n",
    "        self.handleUiTab()\n",
    "        self.show_categories()\n",
    "        self.show_author()\n",
    "        self.show_publisher()\n",
    "        self.bookCategoryForEditDeleteTab()\n",
    "        self.authorForEditDeleteTab()\n",
    "        self.publisherForEditDeleteTab()\n",
    "        self.bookCategoryForAddTab()\n",
    "        self.authorForAddTab()\n",
    "        self.publisherForAddTab()\n",
    "        self.show_client()\n",
    "        self.showAllBooks()\n",
    "        self.show_day_operations()\n",
    "    \n",
    "    def buttons(self):\n",
    "        self.dayOperationBtn.clicked.connect(self.day_operationsTab)\n",
    "        self.booksBtn.clicked.connect(self.booksTab)\n",
    "        self.userBtn.clicked.connect(self.usersTab)\n",
    "        self.settingsBtn.clicked.connect(self.settingsTab)\n",
    "        self.clientBtn.clicked.connect(self.clientTab)\n",
    "        \n",
    "        self.addBookBtn.clicked.connect(self.addBook)\n",
    "        self.bookSearchBtn.clicked.connect(self.searchBook)\n",
    "        self.updateBookInfo.clicked.connect(self.editBook)\n",
    "        self.deleteBookBtn.clicked.connect(self.deleteBook)\n",
    "        \n",
    "        self.addCategoryBtn.clicked.connect(self.add_categories)\n",
    "        self.addAuthorBtn.clicked.connect(self.add_author)\n",
    "        self.addPublisherBtn.clicked.connect(self.add_publisher)\n",
    "        \n",
    "        self.addUserBtn.clicked.connect(self.addNewUser)\n",
    "        self.userLogin.clicked.connect(self.UserLogin)\n",
    "        self.userUpadteBtn.clicked.connect(self.updateUser)\n",
    "        \n",
    "        self.addClientBtn.clicked.connect(self.add_client)\n",
    "        self.searchClientBtn.clicked.connect(self.search_client)\n",
    "        self.updateClientBtn.clicked.connect(self.update_client)\n",
    "        self.deleteClientBtn.clicked.connect(self.delete_client)\n",
    "        \n",
    "        self.dayOprBtn.clicked.connect(self.addDayOperations)\n",
    "        \n",
    "        self.dayoperationsExcel.clicked.connect(self.export_day_operations)\n",
    "        self.booksExcel.clicked.connect(self.export_books)\n",
    "        self.clientExcel.clicked.connect(self.export_clients)\n",
    "    \n",
    "    def handleUiTab(self):\n",
    "        self.tabWidget.tabBar().setVisible(False)\n",
    "    \n",
    "    def day_operationsTab(self):\n",
    "        self.tabWidget.setCurrentIndex(0)\n",
    "    \n",
    "    def booksTab(self):\n",
    "        self.tabWidget.setCurrentIndex(1)\n",
    "        \n",
    "    def clientTab(self):\n",
    "        self.tabWidget.setCurrentIndex(2)\n",
    "    \n",
    "    def usersTab(self):\n",
    "        self.tabWidget.setCurrentIndex(3)\n",
    "    \n",
    "    def settingsTab(self):\n",
    "        self.tabWidget.setCurrentIndex(4)\n",
    "    \n",
    "    ############# book operation #############\n",
    "    def addBook(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        bookName = self.bookTitle.text()\n",
    "        bookcode = self.bookCode.text()\n",
    "        bookcategory = self.bookCategory.currentText()\n",
    "        bookauthor = self.bookAuthor.currentText()\n",
    "        bookpublisher = self.bookPublisher.currentText()\n",
    "        bookPrice = self.bookPrice.text()\n",
    "        bookDescription = self.addBookTabDescription.toPlainText()\n",
    "        \n",
    "        self.cur.execute('''\n",
    "            INSERT INTO book(book_name, book_description, book_code, book_category, book_author, book_publisher, book_price) \n",
    "            VALUES (%s, %s, %s, %s, %s, %s, %s)\n",
    "        ''',(bookName, bookDescription, bookcode, bookcategory, bookauthor, bookpublisher, bookPrice))\n",
    "        self.db.commit()\n",
    "        self.statusBar().showMessage('new book is added successfully..')\n",
    "        \n",
    "        self.bookTitle.setText('')\n",
    "        self.bookCode.setText('')\n",
    "        self.bookCategory.setCurrentIndex(0)\n",
    "        self.bookAuthor.setCurrentIndex(0)\n",
    "        self.bookPublisher.setCurrentIndex(0)\n",
    "        self.bookPrice.setText('')\n",
    "        self.addBookTabDescription.setPlainText('')\n",
    "        self.showAllBooks()\n",
    "    \n",
    "    def searchBook(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        searchedBookName = self.searchBookName.text()\n",
    "        sql = \"SELECT * FROM book WHERE book_name=%s\"\n",
    "        self.cur.execute(sql, [(searchedBookName)])\n",
    "        data = self.cur.fetchone()\n",
    "        self.searchBookTitle.setText(data[1])\n",
    "        self.searchBookCode.setText(data[3])\n",
    "        self.searchBookPrice.setText(str(data[7]))\n",
    "        self.editDelBookTabDescription.setPlainText(str(data[2]))\n",
    "    \n",
    "    def editBook(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        searchBookTitle = self.searchBookName.text()\n",
    "        \n",
    "        bookName = self.searchBookTitle.text()\n",
    "        bookcode = self.searchBookCode.text()\n",
    "        bookcategory = self.availableCategory.currentText()\n",
    "        bookauthor = self.availablAuthor.currentText()\n",
    "        bookpublisher = self.availablePublisher.currentText()\n",
    "        bookPrice = self.searchBookPrice.text()\n",
    "        bookDescription = self.editDelBookTabDescription.toPlainText()\n",
    "        \n",
    "        self.cur.execute('''\n",
    "        UPDATE book SET book_name=%s, book_description=%s, book_code=%s, book_category=%s, book_author=%s, book_publisher=%s, book_price=%s WHERE book_name=%s\n",
    "        ''', (bookName,bookDescription,bookcode,bookcategory,bookauthor,bookpublisher,bookPrice,searchBookTitle))\n",
    "        self.db.commit()\n",
    "        self.statusBar().showMessage('book is updated..')\n",
    "        self.showAllBooks()\n",
    "    \n",
    "    def deleteBook(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        searchBookTitle = self.searchBookName.text()\n",
    "        permission = QMessageBox.warning(self, 'delete book', 'do you want to delete the book', QMessageBox.Yes|QMessageBox.No)\n",
    "        if permission == QMessageBox.Yes:\n",
    "            delQuery = \"DELETE FROM book WHERE book_name=%s\"\n",
    "            self.cur.execute(delQuery, [(searchBookTitle)])\n",
    "            self.db.commit()\n",
    "            self.statusBar().showMessage('book is deleted..')\n",
    "            self.showAllBooks()\n",
    "        \n",
    "        self.searchBookName.setText('')\n",
    "        self.searchBookTitle.setText('')\n",
    "        self.searchBookCode.setText('')\n",
    "        self.availableCategory.setCurrentIndex(0)\n",
    "        self.availablAuthor.setCurrentIndex(0)\n",
    "        self.availablePublisher.setCurrentIndex(0)\n",
    "        self.searchBookPrice.setText('')\n",
    "        self.editDelBookTabDescription.setPlainText('')\n",
    "    \n",
    "    def showAllBooks(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        self.cur.execute(\"SELECT book_code,book_name,book_author,book_publisher,book_category,book_price FROM book\")\n",
    "        data = self.cur.fetchall()\n",
    "        if data:\n",
    "            row_count = 0\n",
    "            self.tableWidget_3.setRowCount(row_count)\n",
    "            self.tableWidget_3.insertRow(row_count)\n",
    "            for row, form in enumerate(data):\n",
    "                for col, item in enumerate(form):\n",
    "                    self.tableWidget_3.setItem(row, col, QTableWidgetItem(str(item)))\n",
    "                row_count += 1\n",
    "                self.tableWidget_3.insertRow(row_count)\n",
    "        self.db.close()\n",
    "        \n",
    "    # *********** day operation ************\n",
    "    # **************************************\n",
    "    \n",
    "    def addDayOperations(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        bookName = self.dayOprBook.text()\n",
    "        clientName = self.dayOprClient.text()\n",
    "        typ = self.dayOprTypSel.currentText()\n",
    "        day = self.dayOprDaySel.currentIndex() + 1\n",
    "        fromdate = datetime.date.today()\n",
    "        todate = fromdate + datetime.timedelta(days=3)\n",
    "        \n",
    "        self.cur.execute(\"INSERT INTO dayoperations(bookname,type,days,fromDate,toDate,clientName) VALUES(%s, %s, %s, %s, %s, %s)\",(bookName,typ,day,fromdate,todate,clientName))\n",
    "        self.db.commit()\n",
    "        self.statusBar().showMessage('new operation is added..')\n",
    "        self.show_day_operations()\n",
    "    \n",
    "    def show_day_operations(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        self.cur.execute(\"SELECT bookname,clientName,type,fromDate,toDate FROM dayoperations\")\n",
    "        data = self.cur.fetchall()\n",
    "        if data:\n",
    "            row_count = 0\n",
    "            self.tableWidget_2.setRowCount(row_count)\n",
    "            self.tableWidget_2.insertRow(row_count)\n",
    "            for row, form in enumerate(data):\n",
    "                for col, item in enumerate(form):\n",
    "                    self.tableWidget_2.setItem(row, col, QTableWidgetItem(str(item)))\n",
    "                row_count += 1\n",
    "                self.tableWidget_2.insertRow(row_count)\n",
    "        self.db.close()\n",
    "            \n",
    "        \n",
    "     ############# user operation #############\n",
    "    def addNewUser(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        username = self.regUsername.text()\n",
    "        mail = self.regEmail.text()\n",
    "        password = self.regPass.text()\n",
    "        rePassword = self.regPassAgain.text()\n",
    "        \n",
    "        if password == rePassword:\n",
    "            self.cur.execute(\"INSERT INTO users(username, useremail, userspassword) VALUES(%s, %s, %s)\", (username, mail, password))\n",
    "            self.db.commit()\n",
    "            self.statusBar().showMessage('new user is added successfully.....')\n",
    "            self.errorMsg.setText('')\n",
    "        else:\n",
    "            self.errorMsg.setText('password is not matched....')\n",
    "            self.statusBar().showMessage(' ')\n",
    "    \n",
    "    def UserLogin(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        loginUsername = self.userloginUsername.text()\n",
    "        loginUserPass = self.userloginPass.text()\n",
    "        \n",
    "        sql = \"SELECT * FROM users\"\n",
    "        self.cur.execute(sql)\n",
    "        data = self.cur.fetchall()\n",
    "        for d in data:\n",
    "            if d[1] == loginUsername and d[3] == loginUserPass:\n",
    "                self.edituserInfo.setEnabled(True)\n",
    "                self.statusBar().showMessage('login successful...')\n",
    "                \n",
    "                self.editUserUsername.setText(d[1])\n",
    "                self.editUserMail.setText(d[2])\n",
    "                self.editUserPass.setText(d[3])\n",
    "                break\n",
    "            else:\n",
    "                self.statusBar().showMessage('username or password is invalid...')\n",
    "    \n",
    "    def updateUser(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        updateUsername = self.editUserUsername.text()\n",
    "        updateUserMail = self.editUserMail.text()\n",
    "        updateUserPass = self.editUserPass.text()\n",
    "        updateUserPassAgain = self.lineEdit_17.text()\n",
    "        beforeUpdateUsername = self.userloginUsername.text()\n",
    "        \n",
    "        if updateUserPass == updateUserPassAgain:\n",
    "            self.cur.execute('''\n",
    "            UPDATE users SET username=%s, useremail=%s, userspassword=%s WHERE username=%s\n",
    "            ''',(updateUsername, updateUserMail, updateUserPass, beforeUpdateUsername))\n",
    "            self.db.commit()\n",
    "            self.statusBar().showMessage('user information is updated')\n",
    "        else:\n",
    "            self.statusBar().showMessage('password is not matched')\n",
    "    \n",
    "    ############# client operation #############\n",
    "    \n",
    "    def add_client(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        clientName = self.newClientName.text()\n",
    "        clientEmail = self.newClientEmail.text()\n",
    "        clientId = self.newClientId.text()\n",
    "        \n",
    "        self.cur.execute(\"INSERT INTO client(clientName, clientEmail, clientNid) VALUES(%s, %s, %s)\", (clientName, clientEmail, clientId))\n",
    "        self.db.commit()\n",
    "        self.statusBar().showMessage('new client is added')\n",
    "        self.show_client()\n",
    "    def search_client(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        searchclient = self.searchClient.text()\n",
    "        sql = \"SELECT * FROM client WHERE clientNid=%s\"\n",
    "        self.cur.execute(sql, [(searchclient)])\n",
    "        data = self.cur.fetchone()\n",
    "        self.updelClientName.setText(data[1])\n",
    "        self.updelClientEmail.setText(data[2])\n",
    "        self.updelClientId.setText(data[3])\n",
    "        \n",
    "    def update_client(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        upclientName = self.updelClientName.text()\n",
    "        upclientEmail = self.updelClientEmail.text()\n",
    "        upclientId = self.updelClientId.text()\n",
    "        searchedclientId = self.searchClient.text()\n",
    "        if upclientName and upclientEmail and upclientId:\n",
    "             self.cur.execute(\"UPDATE client SET clientName=%s, clientEmail=%s, clientNid=%s WHERE clientNid=%s\", (upclientName,upclientEmail,upclientId,searchedclientId))\n",
    "             self.db.commit()\n",
    "             self.statusBar().showMessage('client information is updated...')\n",
    "             self.show_client()\n",
    "        else:\n",
    "            self.clientUperror.setText('fields are required....')\n",
    "            \n",
    "    def show_client(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT * FROM client\")\n",
    "        data = self.cur.fetchall()\n",
    "        if data:\n",
    "            row_count = 0\n",
    "            self.tableWidget.setRowCount(row_count)\n",
    "            self.tableWidget.insertRow(row_count)\n",
    "            for row, form in enumerate(data):\n",
    "                for col, item in enumerate(form):\n",
    "                    self.tableWidget.setItem(row, col, QTableWidgetItem(str(item)))\n",
    "                row_count += 1\n",
    "                self.tableWidget.insertRow(row_count)\n",
    "        self.db.close()\n",
    "        \n",
    "    def delete_client(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "\n",
    "        searchedclientId = self.searchClient.text()\n",
    "        if searchedclientId:\n",
    "            permission = QMessageBox.warning(self,'delete client', 'are you sure to delete the client', QMessageBox.Yes|QMessageBox.No)\n",
    "            if permission == QMessageBox.Yes:\n",
    "                sql = \"DELETE FROM client WHERE clientNid=%s\"\n",
    "                self.cur.execute(sql, [(searchedclientId)])\n",
    "                self.db.commit()\n",
    "                self.statusBar().showMessage('information is deleted successfully...')\n",
    "                self.show_client()\n",
    "        else:\n",
    "            self.clientUperror.setText('client id field is required...')\n",
    "        \n",
    "    \n",
    "    ############# setting operation #############\n",
    "    \n",
    "    def add_author(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        authorname = self.addAuthor.text()\n",
    "        self.cur.execute(\"INSERT INTO author(author_name) VALUES (%s)\", (authorname,))\n",
    "        self.db.commit()\n",
    "        self.statusBar().showMessage('new author is added successfully...')\n",
    "        self.show_author()\n",
    "        self.authorForEditDeleteTab()\n",
    "        self.authorForAddTab()\n",
    "        print('author is added successfully...')\n",
    "    \n",
    "    def show_author(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT author_name FROM author\") \n",
    "        data = self.cur.fetchall()\n",
    "        dataSize = len(data)\n",
    "        if data:\n",
    "            row_count = 0\n",
    "            self.allAuthors_2.setRowCount(row_count)\n",
    "            self.allAuthors_2.insertRow(row_count)\n",
    "            for row, form in enumerate(data):\n",
    "                for col, item in enumerate(form):\n",
    "                    self.allAuthors_2.setItem(row, col, QTableWidgetItem(str(item)))\n",
    "                row_count += 1\n",
    "                self.allAuthors_2.insertRow(row_count)\n",
    "    \n",
    "    def add_categories(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        categoryname = self.categoryName.text()\n",
    "        self.cur.execute(\"INSERT INTO category(category_name) VALUES (%s)\", (categoryname,))\n",
    "        self.db.commit()\n",
    "        self.statusBar().showMessage('new book category is added successfully...')\n",
    "        self.show_categories()\n",
    "        self.bookCategoryForEditDeleteTab()\n",
    "        self.bookCategoryForAddTab()\n",
    "        print('book category is added successfully...')\n",
    "        \n",
    "    def show_categories(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT category_name FROM category\") \n",
    "        data = self.cur.fetchall()\n",
    "        dataSize = len(data)\n",
    "        if data:\n",
    "            row_count = 0\n",
    "            self.allCategories.setRowCount(row_count)\n",
    "            self.allCategories.insertRow(row_count)\n",
    "            for row, form in enumerate(data):\n",
    "                for col, item in enumerate(form):\n",
    "                    self.allCategories.setItem(row, col, QTableWidgetItem(str(item)))\n",
    "                row_count += 1\n",
    "                self.allCategories.insertRow(row_count)\n",
    "                \n",
    "    \n",
    "    def add_publisher(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        publishername = self.addPublisher.text()\n",
    "        self.cur.execute(\"INSERT INTO publisher(publisher_name) VALUES (%s)\", (publishername,))\n",
    "        self.db.commit()\n",
    "        self.statusBar().showMessage('new publisher is added successfully...')\n",
    "        self.show_publisher()\n",
    "        self.publisherForEditDeleteTab()\n",
    "        self.publisherForAddTab()\n",
    "        print('publisher is added successfully...')\n",
    "    \n",
    "    def show_publisher(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT publisher_name FROM publisher\") \n",
    "        data = self.cur.fetchall()\n",
    "        dataSize = len(data)\n",
    "        if data:\n",
    "            row_count = 0\n",
    "            self.allPublishers.setRowCount(row_count)\n",
    "            self.allPublishers.insertRow(row_count)\n",
    "            for row, form in enumerate(data):\n",
    "                for col, item in enumerate(form):\n",
    "                    self.allPublishers.setItem(row, col, QTableWidgetItem(str(item)))\n",
    "                row_count += 1\n",
    "                self.allPublishers.insertRow(row_count)\n",
    "    \n",
    "    def bookCategoryForAddTab(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT category_name FROM category\")\n",
    "        data = self.cur.fetchall()\n",
    "        for d in data:\n",
    "            self.bookCategory.addItem(d[0])\n",
    "    \n",
    "    def authorForAddTab(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT author_name FROM author\")\n",
    "        data = self.cur.fetchall()\n",
    "        for d in data:\n",
    "            self.bookAuthor.addItem(d[0])\n",
    "    \n",
    "    def publisherForAddTab(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT publisher_name FROM publisher\")\n",
    "        data = self.cur.fetchall()\n",
    "        for d in data:\n",
    "            self.bookPublisher.addItem(d[0])\n",
    "    \n",
    "    def bookCategoryForEditDeleteTab(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT category_name FROM category\")\n",
    "        data = self.cur.fetchall()\n",
    "        for d in data:\n",
    "            self.availableCategory.addItem(d[0])\n",
    "    \n",
    "    def authorForEditDeleteTab(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT author_name FROM author\")\n",
    "        data = self.cur.fetchall()\n",
    "        for d in data:\n",
    "            self.availablAuthor.addItem(d[0])\n",
    "    \n",
    "    def publisherForEditDeleteTab(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT publisher_name FROM publisher\")\n",
    "        data = self.cur.fetchall()\n",
    "        for d in data:\n",
    "            self.availablePublisher.addItem(d[0])\n",
    "    \n",
    "    # *********** Export ***********\n",
    "    # *****************************\n",
    "    def export_day_operations(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        self.cur.execute(\"SELECT bookname,clientName,type,fromDate,toDate FROM dayoperations\")\n",
    "        data = self.cur.fetchall()\n",
    "        wb = Workbook('day_operations.xlsx')\n",
    "        sheet1 = wb.add_worksheet()\n",
    "        sheet1.write(0,0, 'bookname')\n",
    "        sheet1.write(0,1, 'clientName')\n",
    "        sheet1.write(0,2, 'type')\n",
    "        sheet1.write(0,3, 'fromDate')\n",
    "        sheet1.write(0,4, 'toDate')\n",
    "        row_num = 1\n",
    "        for row in data:\n",
    "            col_num = 0\n",
    "            for item in row:\n",
    "                sheet1.write(row_num, col_num,str(item))\n",
    "                col_num +=1\n",
    "            row_num += 1\n",
    "        wb.close()\n",
    "        self.statusBar().showMessage('data is downloaded successfully....')\n",
    "    def export_books(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        \n",
    "        self.cur.execute(\"SELECT book_code,book_name,book_author,book_publisher,book_category,book_price FROM book\")\n",
    "        data = self.cur.fetchall()\n",
    "        wb = Workbook('allBooks.xlsx')\n",
    "        sheet1 = wb.add_worksheet()\n",
    "        sheet1.write(0,0, 'book_code')\n",
    "        sheet1.write(0,1, 'book_name')\n",
    "        sheet1.write(0,2, 'book_author')\n",
    "        sheet1.write(0,3, 'book_publisher')\n",
    "        sheet1.write(0,4, 'book_category')\n",
    "        sheet1.write(0,5, 'book_price')\n",
    "        row_num = 1\n",
    "        for row in data:\n",
    "            col_num = 0\n",
    "            for item in row:\n",
    "                sheet1.write(row_num, col_num,str(item))\n",
    "                col_num +=1\n",
    "            row_num += 1\n",
    "        wb.close()\n",
    "        self.statusBar().showMessage('data is downloaded successfully....')\n",
    "    def export_clients(self):\n",
    "        self.db = msd.connect(host='localhost', user='root', password='riju000346>R#', db='library_management')\n",
    "        self.cur = self.db.cursor()\n",
    "        self.cur.execute(\"SELECT clientName,clientEmail,clientNid FROM client\")\n",
    "        data = self.cur.fetchall()\n",
    "        wb = Workbook('allClients.xlsx')\n",
    "        sheet1 = wb.add_worksheet()\n",
    "        sheet1.write(0,0, 'clientName')\n",
    "        sheet1.write(0,1, 'clientEmail')\n",
    "        sheet1.write(0,2, 'clientNid')\n",
    "        row_num = 1\n",
    "        for row in data:\n",
    "            col_num = 0\n",
    "            for item in row:\n",
    "                sheet1.write(row_num, col_num,str(item))\n",
    "                col_num +=1\n",
    "            row_num += 1\n",
    "        wb.close()\n",
    "        self.statusBar().showMessage('data is downloaded successfully....')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def main():\n",
    "    app = QApplication(sys.argv)\n",
    "    win = LoginCls()\n",
    "    win.show()\n",
    "    app.exec_()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if __name__ == '__main__':\n",
    "    main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
