#!/usr/bin/python                                                              
# -*- coding: utf-8 -*-                                                        

import os, sys, datetime, time
from PyQt4 import QtCore, QtGui
from bibl_gui import Ui_MainWindow
from database_info import Ui_Form
from search import Ui_Form as SearchUiForm
from add_form3 import Ui_Dialog as AddUiForm
from select_database import Ui_sel_database as SelectDbForm
from connect_database import Ui_selectDB as ConnectDbForm
from search_form import Ui_searchallForm as SearchAllForm

import db_settings as conn_var
dbname = conn_var.dbname
user = conn_var.user
host = conn_var.host
passwd = conn_var.password

import psycopg2

#Словарь с колонками таблицы
columns = {"authors":"Автор(ы)", "name_orig":"Название (ориг.)", "name_alt":"Название (альт.)", "source":"Источник", "editor":"Издатель", "publ_location":"Место издания", "publication":"Издательство", "year":"Год", "volume":"Том", "number":"Номер", "issue":"Выпуск", "series":"Серия", "part":"Часть", "pages":"Стр.", "tables":"Таблицы", "maps":"Карты", "illustrations":"Иллюстрации", "references":"Ссылки", "series area":"Область серии", "language":"Язык", "type":"Тип", "ref_code":"Код ссылки", "udk":"УДК", "zool_rec":"Zool. Rec.", "issn":"ISSN", "isbn":"ISBN", "common_keywords":"Ключевые слова", "taxon_keywords":"Таксономические ключевые слова", "publ_number":"Номер публикации", "publ_date":"Дата публикации", "publ_country":"Страна публикации", "num_ed_mpk":"Номер издательства МПК", "main_mpk_ind":"Основной индекс МПК", "pat_owner":"Собственник патента", "file_path":"Расположение файла", "file_size":"Размер файла", "abstract":"Реферат", "full_txt":"Полный текст", "uid":"УИН"}

dict_query = """
SELECT "%s", count(*) AS "count" FROM articles GROUP BY "%s" ORDER BY "%s";
"""
size_query_long = """
SELECT relname AS name, relfilenode AS oid, (relpages * 8192 / (1024*1024))::int as size_mb, reltuples  as count 
      FROM pg_class 
      WHERE relname NOT LIKE 'pg%' 
      ORDER BY relpages DESC;
"""

size_query_short = """
select pg_size_pretty(pg_database_size('bibliography'));
"""

table_size_query = """
select pg_size_pretty(pg_relation_size('articles'));
"""

fts_query = """
SELECT 
uid, authors, year, name_orig, name_alt, file_path,
 source,type, common_keywords, abstract, main_mpk_ind,
 volume, number, pages, publ_location, publication, language, publ_date,  
ts_headline(full_txt, to_tsquery('russian', '%s'), 'MaxWords=60, MinWords=30, ShortWord=3'),
 rank
from (SELECT uid, authors, year, name_orig, name_alt, file_path, source, type, full_txt, common_keywords, abstract, main_mpk_ind, volume, number, pages, publ_location, publication, language, publ_date, ts_rank_cd(fts, query) AS rank
FROM articles, to_tsquery('%s') query
WHERE query @@@ fts
ORDER BY rank DESC) as FOO;
"""

abstract_query = """
SELECT
abstract
FROM
articles
WHERE
uid = %i
"""

fts_query_short = """
SELECT
uid, authors, year, name_orig,  ts_rank_cd(fts, query), volume, type, fulltxt_presence 
FROM articles, to_tsquery('%s') query
WHERE query @@@ fts
ORDER BY year DESC;
"""

query_standart  = """
SELECT
uid, authors, year, name_orig, source, volume, type, fulltxt_presence
FROM articles 
ORDER BY authors 
ASC;
"""

class About_Form(QtGui.QWidget, Ui_Form):

    def __init__(self):
        QtGui.QWidget.__init__(self)
        self.setupUi(self)

    def about_show(self):
        self.show()

    def slotFile(self):
        filename=QtGui.QFileDialog.getOpenFileName()

class SearchAll_Form(QtGui.QWidget, SearchAllForm):

    def __init__(self):
        QtGui.QWidget.__init__(self)
        self.setupUi(self)

    def searchall_show(self):
        self.show()


class Search_Form(QtGui.QWidget, SearchUiForm):

    def __init__(self):
        QtGui.QWidget.__init__(self)
        self.setupUi(self)

    def search_show(self):
        self.show()
    
    def fulltext_search(self):
        text = self.lineEdit.text
        return text

class Connect_Form(QtGui.QWidget, ConnectDbForm):

    def __init__(self):
        QtGui.QWidget.__init__(self)
        self.setupUi(self)
        self.lineEditName.setText(dbname)
        self.lineEditUser.setText(user)
        self.lineEditAddress.setText(host)
        self.lineEditPasswd.setText(passwd)
        
    def connect_show(self):
        self.show()

    def connect_to(self):
        dbname = self.lineEditName.text()
        user = self.lineEditUser.text()
        host = self.lineEditAddress.text()
        passwd = self.lineEditPasswd.text()
        
        conn = psycopg2.connect("dbname='%s' user='%s' host='%s'  password='%s'" % (dbname, user, host, passwd))
        return conn

class Add_Form(QtGui.QScrollArea, AddUiForm):

    def __init__(self):
        QtGui.QWidget.__init__(self)
        self.setupUi(self)
    
    def add_show(self):
        self.show()

class Select_Database(QtGui.QWidget, SelectDbForm):

    def __init__(self):
        QtGui.QWidget.__init__(self)
        self.setupUi(self)

    def select_show(self):
        self.show()

class MainView(QtGui.QMainWindow):

    def __init__(self, parent = None):
        QtGui.QMainWindow.__init__(self, parent)
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)
        self.uiAbout = About_Form()
        self.uiSearch = Search_Form()
        self.uiSearchAll = SearchAll_Form()
        self.uiConnect = Connect_Form()
        self.uiSearch.lineEdit.setText(u'[Пишите сюда]')
        self.uiAdd = Add_Form()
        self.uiSelectDb = Select_Database()
      
          
        self.ui.tableWidget.hide()
        
        
        self.connect_server()
        self.fts_model("st")
        #self.fts_('st')
        #self.uiTable = self.createTable(self.fts_model("st"))
        #self.ui.horizontalLayout_3.addWidget(self.uiTable)
        
        
        #self.uiTable.cur.execute("select authors, year, name_orig from articles")
        #self.uiTable.dbdata = self.uiTable.cur.fetchall()
       
        self.connect(self.ui.exit_, QtCore.SIGNAL('triggered()'), QtCore.SLOT('close()'))
        
        self.connect(self.ui.database_info, QtCore.SIGNAL('triggered()'), self.uiAbout.about_show)
        
        self.connect(self.ui.about, QtCore.SIGNAL('triggered()'), self.db_info)
        
        self.connect(self.ui.toolBarOpenDatabase, QtCore.SIGNAL('triggered()'), self.uiConnect.connect_show)
        self.connect(self.uiConnect.buttonBox, QtCore.SIGNAL("accepted()"), self.uiConnect.connect_to)

        
        self.connect(self.uiConnect.buttonBox, QtCore.SIGNAL("rejected()"), self.uiConnect.close)
        
       
        self.connect(self.ui.open_, QtCore.SIGNAL('triggered()'), self.uiAbout.slotFile)

        
        self.connect(self.ui.searchFulltext, QtCore.SIGNAL('triggered()'), self.uiSearch.search_show)
        self.connect(self.ui.searchCompl, QtCore.SIGNAL('triggered()'), self.uiSearchAll.searchall_show)
        
        
        self.connect(self.ui.toolBarNewEntry, QtCore.SIGNAL('triggered()'), self.ui.add_dataDOCK.show)

        
        
        
        
        self.connect(self.uiSearch.pushButton, QtCore.SIGNAL('clicked()'), self.uiSearch.fulltext_search)
        self.connect(self.uiSearch.pushButton, QtCore.SIGNAL('clicked()'), self.fts_model)
        self.connect(self.uiSearch.lineEdit, QtCore.SIGNAL('returnPressed()'), self.fts_model)

        
        self.connect(self.ui.tableWidget, QtCore.SIGNAL('itemClicked(QTableWidgetItem *)'), self.set_statusbar)
        self.connect(self.ui.tableWidget, QtCore.SIGNAL('itemClicked(QTableWidgetItem *)'), self.select_abstract)
        
        
        self.connect(self.ui.actionJapan,  QtCore.SIGNAL('triggered()'), self.dict_info)


    def connect_server(self):
        
        try:
            self.conn = psycopg2.connect("dbname='%s' user='%s' host='%s'  password='%s'" % (dbname, user, host, passwd))
        except psycopg2.OperationalError:
            self.uiConnect.show()
            
        self.cur = self.conn.cursor()

    def createTable(self, datain):
        tv = QtGui.QTableView()
        tm = BiblTableModel(datain, self)
        tm.reset()
        tv.setModel(tm)
        vh = tv.verticalHeader()
        vh.setVisible(True)
        hh = tv.horizontalHeader()
        hh.setStretchLastSection(True)
           
        return tv


    def select_abstract(self):

        uid = int(self.ui.tableWidget.item(self.ui.tableWidget.currentRow(), 7).text())
        self.cur.execute(abstract_query % uid)
        abstract = self.cur.fetchone()[0]
        self.ui.textBrowser.clear()
        if abstract is None:
            abstract = ' '
        
        self.ui.textBrowser.append(QtCore.QString(abstract.decode("utf-8")))
    

    def set_statusbar(self):

        mess = u"Строка: %i, столбец: %i, Значение: %s" % (self.ui.tableWidget.currentRow(), self.ui.tableWidget.currentColumn(), self.ui.tableWidget.currentItem().text())
        self.statusBar().showMessage(mess)
        
    def fts_model(self, *args):
        tx = unicode(self.uiSearch.lineEdit.text())
        tx_search = tx.replace(" ", ",")
        if len(args) == 0:
            start_time = datetime.datetime.now()
            self.cur.execute(fts_query_short % (tx_search))
            differ = str(datetime.datetime.now() - start_time)
            differ = differ.split(':')[-1]
            differ = float(differ)
            self.ui.horizontalLayout_3.removeWidget(self.uiTable)
            self.uiTable = self.createTable(self.cur.fetchall())
            self.ui.horizontalLayout_3.addWidget(self.uiTable)
            
            
        elif args[0] == 'st':
            self.statusBar().showMessage(u'Стандартный вид')
            tx = u'Стандартный вид'
            start_time = datetime.datetime.now()
            self.cur.execute(query_standart)
            differ = str(datetime.datetime.now() - start_time)
            differ = differ.split(':')[-1]
            differ = float(differ)
            
            self.uiTable = self.createTable(self.cur.fetchall())
            self.ui.horizontalLayout_3.addWidget(self.uiTable)
            
        
        
   
    def fts_(self, *args):
        
        tx = unicode(self.uiSearch.lineEdit.text())
        tx_search = tx.replace(" ", ",")
        
        
        if len(args) == 0:
            start_time = datetime.datetime.now()
            self.cur.execute(fts_query_short % (tx_search))
            differ = str(datetime.datetime.now() - start_time)
            differ = differ.split(':')[-1]
            differ = float(differ)
            
            
        elif args[0] == 'st':
            self.statusBar().showMessage(u'Стандартный вид')
            tx = u'Стандартный вид'
            start_time = datetime.datetime.now()
            self.cur.execute(query_standart)
            differ = str(datetime.datetime.now() - start_time)
            differ = differ.split(':')[-1]
            differ = float(differ)
            



        self.ui.tableWidget.setRowCount(self.cur.rowcount)
        self.ui.tableWidget.setUpdatesEnabled(False)
        
        for i in xrange(self.cur.rowcount):
            l = self.cur.fetchone()
            uid = l[0]
            auth = l[1]
            year = l[2]
            name_orig = l[3]
            source = l[4]
            volume = l[5]
            type = l[6]
            pdf = l[7]
            
                    
            if pdf == True:
                
                pdf = 'True'
            else:
                pdf = 'False'
            
            if len(args) == 0:
                name_alt = str(round(l[4], 3))
            else:
                name_alt = l[4]
            
            columns = [pdf, auth, year, name_orig, source, volume, type, uid]
          
            for c in xrange(len(columns)):
                if columns[c] is None:
                    columns[c] = ''
                
                        
                try:
                    columns[c] = columns[c].decode("utf-8")
                except AttributeError:
                    columns[c] = str(columns[c])
                
                table_item = QtGui.QTableWidgetItem(columns[c])
                self.ui.tableWidget.setItem(i, c, table_item)
            

                 
        

        self.ui.tableWidget.setUpdatesEnabled(True)
        self.ui.textBrowser.clear()
        self.ui.textBrowser.append(QtCore.QString(u"""Вы искали: <b>"%s"</b><p>Найдено <b>%i</b> записей</p><p>Время выполнения запроса: 
<b>%.4g сек.</b></p>""" % (tx, self.cur.rowcount, differ)))
        
        
        
    def db_info(self):

        QtGui.QMessageBox.about(self, "About", u"""<b>База данных с полнотекстовым поиском</b><p>Программа</p>""")
 
    def dict_info(self):
        print "Menu clicked"
        
class BiblTableModel(QtCore.QAbstractTableModel):
    def __init__(self, datain, parent=None, *args):
        QtCore.QAbstractTableModel.__init__(self, parent, *args)
        
        self.dbdata = datain 
    def rowCount(self, parent):
        
        return len(self.dbdata)

    def columnCount(self, parent):
        
        return len(self.dbdata[0])

    def data(self, index, role):
        
        if not index.isValid():
            return QtCore.QVariant()
        elif role != QtCore.Qt.DisplayRole:
            return QtCore.QVariant()
        
        print QtCore.QString(u"cell ")+str(index.row())+"-"+str(index.column())
        if isinstance(self.dbdata[index.row()][index.column()], bool):
            return QtCore.QVariant(self.dbdata[index.row()][index.column()])
        elif isinstance(self.dbdata[index.row()][index.column()], int):
            return QtCore.QVariant(self.dbdata[index.row()][index.column()])
        else:                           
            return QtCore.QVariant((self.dbdata[index.row()][index.column()]).decode("utf-8"))

    
        

def main():

    app = QtGui.QApplication(sys.argv)
    app.setStyle('cleanlooks')
    window=MainView()
    window.setWindowTitle(u'База данных')
    window.show()
    sys.exit(app.exec_())


        
if __name__ == "__main__":
    main()

def fts_model(self, *args):
        tx = unicode(self.uiSearch.lineEdit.text())
        tx_search = tx.replace(" ", ",")
        if len(args) == 0:
            start_time = datetime.datetime.now()
            self.cur.execute(fts_query_short % (tx_search))
            differ = str(datetime.datetime.now() - start_time)
            differ = differ.split(':')[-1]
            differ = float(differ)
#тут мы показываем нашей модели, что данные теперь другие,
            self.uiTable.model().dbdata = self.cur.fetchall()
#а тут - что надо обновить данные.
            self.uiTable.model().reset()