'''
Created on 06.04.2011

@author: jwh
'''

from PySide.QtCore import *
from PySide.QtGui import *


class DatabaseLogic(object):
    '''
    classdoc
    '''


    def __init__(self,db):

        self.db=db
        self.db.host = "localhost"
        self.db.user = "postgres"
        self.db.password = "kingkong1"
        self.db.database = "vfs1"
        self.db.connect()
        self.cur = self.db.connection.cursor()

    def fillLocTable(self):
        self.cur.execute('''select * from "config"."Location" ;''')
        locs_table = QTableWidget()
        locs_table.setRowCount(self.cur.rowcount)
        locs_table.setColumnCount(5)
        for j in range(self.cur.rowcount):
            b=self.cur.fetchone()
            for i in range(len(b)):
                newItem = QTableWidgetItem(str(b[i]))
                locs_table.setItem(j,i, newItem)

        locs_table.setColumnCount(len(b))

        self.cur.execute('''select attname from pg_attribute,pg_class where attrelid = pg_class.oid and relname = 'Location' and attnum>0;''')

        header_temp=self.cur.fetchall()
        header_items=[]

        for i in range(len(header_temp)):
            header_items.append("".join(header_temp[i]))

        for i in range(len(header_items)):
            newItem = QTableWidgetItem(str(header_items[i]))
            locs_table.setHorizontalHeaderItem(i,newItem)
        #self.cur.close()
        print "test"
        return locs_table

    def fillVfsTable(self):
        self.cur.execute('''select "VFSID", "UID", "Name", "Description", "RootFolderID", "SchemaName" from "config"."VirtualFS" ;''')
        vfs_table=QTableWidget();
        vfs_table.setRowCount(self.cur.rowcount)
        vfs_table.setColumnCount(5)

        for j in range(self.cur.rowcount):
            b=self.cur.fetchone()
            for i in range(len(b)):
                newItem = QTableWidgetItem(str(b[i]))
                vfs_table.setItem(j,i, newItem)

        self.cur.execute('''select attname from pg_attribute,pg_class where attrelid = pg_class.oid and relname = 'VirtualFS' and attnum>0 and attname not like '%Type%';''')
        #Die Encryption-IDs entfernen


        #Columnnames in eine Liste fetchen

        vfs_temp=self.cur.fetchall()
        vfs_items=[]

        for i in range(len(vfs_temp)):
                vfs_items.append("".join(vfs_temp[i]))



        for i in range(len(vfs_items)):
            newItem = QTableWidgetItem(str(vfs_items[i]))
            vfs_table.setHorizontalHeaderItem(i,newItem)

        return vfs_table


    def fillUserTree(self):
        users_tree=QTreeWidget()
        self.cur.execute("select usename from pg_user;")

        top_temp=self.cur.fetchall()
        top_items=[]

        for i in range(len(top_temp)):
            top_items.append("".join(top_temp[i]))
            #top_items[i]=top_items[i].join(top_temp[i])



        items = []
        for i in range(len(top_items)):
            items.append(QTreeWidgetItem(users_tree))
            items[-1].setText(0,top_items[i])

        users_tree.insertTopLevelItems(0,items)

        return users_tree

    def fillGroupTree(self):
        groups_tree=QTreeWidget()
        self.cur.execute("select groname from pg_group;")


        top_temp=self.cur.fetchall()
        top_items=[]

        for i in range(len(top_temp)):
            top_items.append("".join(top_temp[i]))


        items = []
        for i in range(len(top_items)):
            items.append(QTreeWidgetItem(groups_tree))
            items[-1].setText(0,top_items[i])

        groups_tree.insertTopLevelItems(0,items)

        grp_tmp=[]

        for i in range(len(top_items)):
            self.cur.execute("select grolist from pg_group where groname = '"+top_items[i]+"' ;")
            #for j in range(self.cur.)
            grp_tmp.append(self.cur.fetchall())

        #print grp_tmp
        #print len(grp_tmp)
        #print grp_tmp[0][0][0]
        #print len(grp_tmp[0][0][0])
        #print grp_tmp[0][0][0][1]
        #print grp_tmp[1][0][0]


        grp_list=[]
        tmp=[]

        for i in range(len(grp_tmp)):
            for j in range(len(grp_tmp[i][0][0])):
                tmp.append(int(grp_tmp[i][0][0][j]))
            grp_list.append(tmp)
            tmp=[]

        print grp_list




        tmp=[]
        str=""
        str_list=[]

        for i in range(len(grp_list)):
            for j in range(len(grp_list[i])):
                self.cur.execute("select usename from pg_user where usesysid = %s ;",(grp_list[i][j],))
                str=self.cur.fetchall()
                tmp.append(str)
            str_list.append(tmp)
            tmp=[]

        print str_list

        #self.cur.execute("select usename from pg_user, pg_group where grolist @> array[usesysid];")
        #grp_temp=self.cur.fetchall()
        grp_temp=str_list
        grp_items=[]

        for i in range(len(grp_temp)):
            for j in range(len(grp_temp[i])):
                tmp.append("".join(grp_temp[i][j][0]))
            grp_items.append(tmp)
            tmp=[]

        print grp_items
        print len(grp_items)


        items = []
        for i in range(len(grp_items)):
            for j in range(len(grp_items[i])):
                groups_tree.itemAt(0,i).addChild(QTreeWidgetItem(groups_tree.itemAt(0,i)).setText(0,grp_items[i][j]))
                #items.append(QTreeWidgetItem(groups_tree.itemAt(0,i)))
                #items[-1].setText(0,grp_items[i][j])
            #items=[]

        #groups_tree.insertTopLevelItems(0,items)

        return groups_tree

    def refresh_usr_tree(self):
        self.fillUserTree()

    def refresh_grp_tree(self):
        self.fillGroupTree()