HYTOS

        elif self._connecterss:

            return []

                # sql = "select * from Points where Components_UID='{}' order by [Index]".format(component)

                sql = "select a.* from Points a \

                            join Components b on a.Components_UID=b.[UID] where Drawings_UID='{}' order by Components_UID, [Index]".format(

                    self.activeDrawing.UID)

    def getDocumentNameList(self):

        '''

            @brief      get documentName list

            @author     euisung

            @date       2018.12.11

        '''

                sql = 'select DISTINCT(PNID_NO) from LINE_DATA_LIST'

                cursor.execute(sql)

                sql = 'select DISTINCT(PNID_NO) from EQUIPMENT_DATA_LIST'

                cursor.execute(sql)

                sql = 'select DISTINCT(PNID_NO) from INSTRUMENT_DATA_LIST'

                cursor.execute(sql)

                sql = 'select DISTINCT(PNID_NO) from NOTE_DATA_LIST'

                cursor.execute(sql)

                sql = 'select DISTINCT(PNID_NO) from VALVE_DATA_LIST'

                result = list(set(result))

        @brief      get line documentName list

        @author     kyouho

        @date       2018.08.14

    '''

    def getEquipDocumentNameList(self):

        result = []

        conn = self.project.database.connect()

        with conn:

            try:

                # Get a cursor object

                cursor = conn.cursor()

                sql = 'select DISTINCT(PNID_NO) from EQUIPMENT_DATA_LIST'

                cursor.execute(sql)

                rows = cursor.fetchall()

                for row in rows:

                    result.append(row[0])

            # Catch the exception

            except Exception as ex:

                print('error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                          sys.exc_info()[-1].tb_lineno))

        return result

    '''

                        where D.Category='Equipment'"

                    sql += f" where A.Drawings_UID=(select UID from Drawings where Name='{drawing}')"

    '''

        @brief      get equipment data list

        @author     humkyung

        @date       2018.05.03

    '''

    def getEquipmentDataList(self, docName=None):

        result = []

        conn = self.project.database.connect()

        with conn:

            try:

                # Get a cursor object

                cursor = conn.cursor()

                sql = 'select a.*,C.Attribute,B.Value  from EQUIPMENT_DATA_LIST a left join Attributes B on a.UID=B.Components_UID ' \

                      'left join SymbolAttribute C on B.SymbolAttribute_UID=C.UID'

                if docName is not None:

                    sql += " where PNID_NO = '{}'".format(docName)

                cursor.execute(sql)

                col_names = [desc[0] for desc in cursor.description]  # get coloumn name

                rows = cursor.fetchall()

                for row in rows:

                    matches = [res for res in result if res['UID'] == row[0]]

                    if matches:

                        matches[0][row[len(row) - 2]] = row[len(row) - 1]

                    else:

                        data = dict(zip(col_names[:-2], row[:-2]))

                        if row[len(row) - 2] is not None:

                            data[row[len(row) - 2]] = row[len(row) - 1]

                        result.append(data)

            # Catch the exception

            except Exception as ex:

                from App import App

                message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                              sys.exc_info()[-1].tb_lineno)

                App.mainWnd().addMessage.emit(MessageType.Error, message)

        return result

    def get_valve_data_list(self, docName=None):

                cursor = conn.cursor()

                sql = 'select a.UID,a.ITEM_NO,a.PNID_NO,a.MainSize,a.SubSize,c.Attribute,b.Value from VALVE_DATA_LIST a left join Attributes b on a.uid=b.Components_UID ' \

                      'left join SymbolAttribute c on b.SymbolAttribute_UID=c.UID'

                if docName is not None:

                    sql += " where PNID_NO = '{}'".format(docName)

                    matches = [res for res in result if res['UID'] == row[0]]

                        matches[0][row[5]] = row[6]

                        data = {'UID': row[0], 'ITEM_NO': row[1], 'PNID_NO': row[2], 'MainSize': row[3],

                                'SubSize': row[4]}

                        data[row[5]] = row[6]

    def getInstrumentDataList(self, docName=None):

                cursor = conn.cursor()

                # sql = 'select UID, ITEM_NO, SERVICE, FLOW_RATE, PRESSURE, TEMPERATURE, TYPE, RANGE, NOR_LEVEL_MM, NOR_LEVEL_PERCENT, DEL_PRESS, SHUT_OFF, LOCATION, PNID_NO, REV from INSTRUMENT_DATA_LIST'

                sql = 'select * from INSTRUMENT_DATA_LIST'

                if docName is not None:

                    sql += " where PNID_NO = '{}'".format(docName)

                    data = []

                    for index in range(len(cursor.description)):

                        data.append(row[index])

                    result.append(data)

    '''

        @brief      get Note data list

        @author     kyouho

        @date       2018.10.10

    '''

    def getNoteDataList(self, docName=None):

                cursor = conn.cursor()

                sql = 'select UID, NOTE_NO, DESCRIPTION, PNID_NO from NOTE_DATA_LIST'

                if docName is not None:

                    sql += " where PNID_NO = '{}'".format(docName)

                sql += ' ORDER BY NOTE_NO ASC'

                    data = []

                    for index in range(len(cursor.description)):

                        data.append(row[index])

                    result.append(data)

                sql = "delete from EQUIPMENT_DATA_LIST where PNID_NO = '{}'".format(drawing_name)

                cursor.execute(sql)

                sql = "delete from VALVE_DATA_LIST where PNID_NO = '{}'".format(drawing_name)

                cursor.execute(sql)

                sql = "delete from INSTRUMENT_DATA_LIST where PNID_NO = '{}'".format(drawing_name)

                cursor.execute(sql)

                sql = "delete from NOTE_DATA_LIST where PNID_NO = '{}'".format(drawing_name)

                cursor.execute(sql)

    '''

        @brief      set Note data list

        @author     kyoyho

        @date       2018.10.10

    '''

    def setNoteDataList(self, dataList):

        conn = self.project.database.connect()

        with conn:

            try:

                # Get a cursor object

                cursor = conn.cursor()

                for data in dataList:

                    sql = self.project.database.to_sql(

                        "insert into NOTE_DATA_LIST(UID, NOTE_NO, DESCRIPTION, PNID_NO) values(?, ?, ?, ?)")

                    param = tuple(data)

                    cursor.execute(sql, param)

                conn.commit()

            # Catch the exception

            except Exception as ex:

                # Roll back any change if something goes wrong

                conn.rollback()

                from App import App

                message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                              sys.exc_info()[-1].tb_lineno)

                App.mainWnd().addMessage.emit(MessageType.Error, message)

    def add_column_into_table(self, table, columnName):

        '''

            @brief  add column into table

            @author euisung

            @date   2019.03.13

        '''

        err = False

        conn = self.project.database.connect()

        with conn:

            try:

                # Get a cursor object

                cursor = conn.cursor()

                sql = "alter table {} add {}".format(table, columnName)

                cursor.execute(sql)

                conn.commit()

            # Catch the exception

            except Exception as ex:

                # Roll back any change if something goes wrong

                conn.rollback()

                err = True

                print('error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                          sys.exc_info()[-1].tb_lineno))

            finally:

                return (True if not err else False)

    def edit_column_Name_in_table(self, table, columnName, newColName):

        '''

            @brief  edit column into table

            @author euisung

            @date   2019.03.13

        '''

        err = False

        colNames = AppDocData.instance().getColNames(table)

        for index in range(len(colNames)):

            if colNames[index] == columnName:

                colNames[index] = newColName

                break

        conn = self.project.database.connect()

        with conn:

            try:

                # Get a cursor object

                cursor = conn.cursor()

                sql = "ALTER TABLE {} RENAME TO {}".format(table, table + '_orig')

                cursor.execute(sql)

                value = []

                for colName in colNames:

                    value.append(colName + ' TEXT')

                sql = "CREATE TABLE {}({}, CONSTRAINT '{}_PK' PRIMARY KEY('UID'))".format(table, ','.join(value), table)

                cursor.execute(sql)

                sql = "INSERT INTO {} SELECT * FROM {}".format(table, table + '_orig')

                cursor.execute(sql)

                sql = "DROP TABLE {}".format(table + '_orig')

                cursor.execute(sql)

                conn.commit()

            # Catch the exception

            except Exception as ex:

                # Roll back any change if something goes wrong

                conn.rollback()

                err = True

                print('error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                          sys.exc_info()[-1].tb_lineno))

            finally:

                return (True if not err else False)

                sql = database.to_sql('delete from EQUIPMENT_DATA_LIST where PNID_NO=?')

                cursor.execute(sql,(self.name,))

                sql = database.to_sql('delete from VALVE_DATA_LIST where PNID_NO=?')

                cursor.execute(sql, (self.name,))

                sql = database.to_sql('delete from NOTE_DATA_LIST where PNID_NO=?') 

                cursor.execute(sql, (self.name,))

            subprocess.call(command, shell=True)

        """

        @brief      auto resize columns with contents

        @author     humkyung

        @date       2018.10.11

        """

        for col in sheet.columns:

            max_length = 0

            column = col[0].column  # Get the column name

            for cell in col:

                try:  # Necessary to avoid error on empty cells

                    if len(str(cell.value)) > max_length:

                        max_length = len(cell.value)

                except:

                    pass

            adjusted_width = (max_length + 2) * 1.2

            sheet.column_dimensions[column].width = adjusted_width

        docData = AppDocData.instance()

        documentNameData = docData.getDocumentNameList()

        result = self.ui.comboBoxDoc.findText(docData.imgName)

            self.ui.comboBoxDoc.addItem(docData.imgName)

        self.valveColumnListAll = app_doc_data.getColNames(QItemDataExportDialog.DATA_LIST[2])

        self.instColumnListAll = app_doc_data.getColNames(QItemDataExportDialog.DATA_LIST[3])

        self.noteColumnListAll = app_doc_data.getColNames(QItemDataExportDialog.DATA_LIST[4])

            self.settingLineData()

            self.settingEquipmentData()

            self.set_valve_data()

            self.settingInstrumentData()

            self.settingNoteData()

            dataList = app_doc_data.getEquipmentDataList(text)

        from EngineeringTextItem import QEngineeringTextItem

            # get column headers

    def settingInstrumentData(self):

        instTable = self.ui.tableWidgetInstrumentDataList

        docData = AppDocData.instance()

        dataList = docData.getInstrumentDataList(text)

        instTable.setRowCount(len(dataList))

            for dataIndex in range(len(data)):

                item = QTableWidgetItem(data[dataIndex] if data[dataIndex] is not None else '')

                item.setFlags(Qt.ItemIsEnabled)

                instTable.setItem(row, dataIndex, item)

    '''

        @brief      setting note data

        @author     kyouho

        @date       2018.10.10

    '''

    def settingNoteData(self):

        noteTable = self.ui.tableWidgetNoteDataList

        docData = AppDocData.instance()

        dataList = docData.getNoteDataList(text)

        noteTable.setRowCount(len(dataList))

            for dataIndex in range(len(data)):

                item = QTableWidgetItem(data[dataIndex] if data[dataIndex] is not None else '')

                item.setFlags(Qt.ItemIsEnabled)

                noteTable.setItem(row, dataIndex, item)

        self.settingInstrumentData()

        self.settingNoteData()

    def saveSceneData(self):

        """

        @brief      save Line Data at scene

        @author     kyouho

        @date       2018.08.13

        """

        if not self.parent.graphicsView.hasImage():

            return

        from EngineeringLineNoTextItem import QEngineeringLineNoTextItem

        items = [item for item in self.parent.graphicsView.scene.items() if type(item) is QEngineeringLineNoTextItem]

        for item in items:

            text = item.text()

            self.sceneLineData[text] = item.getLineDataList()

        from EngineeringEquipmentItem import QEngineeringEquipmentItem

        items = [item for item in self.parent.graphicsView.scene.items() if type(item) is QEngineeringEquipmentItem]

        for item in items:

            self.sceneEquipData[str(item.uid)] = item.getEquipmentDataList()

        from SymbolSvgItem import SymbolSvgItem

        items = [item for item in self.parent.graphicsView.scene.items() if type(item) is SymbolSvgItem]

        for item in items:

            self.scene_valve_data[str(item.uid)] = item

        from EngineeringInstrumentItem import QEngineeringInstrumentItem

        items = [item for item in self.parent.graphicsView.scene.items() if type(item) is QEngineeringInstrumentItem]

        for item in items:

            self.sceneInstData[str(item.uid)] = item.getInstrumentDataList()

        from EngineeringNoteItem import QEngineeringNoteItem

        items = [item for item in self.parent.graphicsView.scene.items() if type(item) is QEngineeringNoteItem]

        for item in items:

            loopIndex = 1

            for note in item.getNoteDataList():

                self.sceneNoteData[str(item.uid) + "-" + str(loopIndex)] = note

                loopIndex += 1

        @brief      save note Data

        @author     kyouho

        @date       2018.10.10

    '''

    def saveNoteDataList(self):

        noteTable = self.ui.tableWidgetNoteDataList

        docData = AppDocData.instance()

        dataLists = []

        for rowIndex in range(noteTable.rowCount()):

            dataList = []

            for columnIndex in range(noteTable.columnCount()):

                widgetItem = noteTable.item(rowIndex, columnIndex)

                if widgetItem is not None:

                    dataList.append(widgetItem.text())

                else:

                    dataList.append('')

            dataLists.append(dataList)

        docData.setNoteDataList(dataLists)

    '''

            for edited in editedCol:

                docData.edit_column_Name_in_table(dbListName[index], edited[0], edited[1])

            for added in addedCol:

                docData.add_column_into_table(dbListName[index], added)

        dataList = docData.getEquipmentDataList()

        dataList = docData.getEquipmentDataList()

        dataList = docData.getEquipmentDataList()

CREATE TABLE IF NOT EXISTS EQUIPMENT_DATA_LIST (

    UID                       VARCHAR (37) CONSTRAINT EQUIPMENT_DATA_LIST_PK PRIMARY KEY,

    ITEM_NO                   TEXT,

    SERVICE                   TEXT,

    NO_REQ                    TEXT,

    FLUID                     TEXT,

    DESC_OF_PART              TEXT,

    OPERATION_CONDITION_TEMP  TEXT,

    OPERATION_CONDITION_PRESS TEXT,

    DESIGN_CONDITION_TEMP     TEXT,

    DESIGN_CONDITION_PRESS    TEXT,

    MATERIAL                  TEXT,

    WEIGHT                    TEXT,

    POWER                     TEXT,

    INSULATION                TEXT,

    PNID_NO                   TEXT,

    REV                       TEXT

);

CREATE TABLE IF NOT EXISTS INSTRUMENT_DATA_LIST (

    UID               VARCHAR (37),

    ITEM_NO           TEXT,

    SERVICE           TEXT,

    FLOW_RATE         TEXT,

    PRESSURE          TEXT,

    TEMPERATURE       TEXT,

    TYPE              TEXT,

    RANGE             TEXT,

    NOR_LEVEL_MM      TEXT,

    NOR_LEVEL_PERCENT TEXT,

    DEL_PRESS         TEXT,

    SHUT_OFF          TEXT,

    LOCATION          TEXT,

    PNID_NO           TEXT,

    REV               TEXT,

    CONSTRAINT EQUIPMENT_DATA_LIST_PK PRIMARY KEY (

        UID

    )

);

CREATE TABLE IF NOT EXISTS VALVE_DATA_LIST (

    UID      VARCHAR (37),

    ITEM_NO  TEXT,

    MainSize TEXT,

    SubSize  TEXT,

    PNID_NO  TEXT,

    CONSTRAINT VALVE_DATA_LIST_PK PRIMARY KEY (

        UID

    )

);

CREATE TABLE EQUIPMENT_DATA_LIST (

    UID                       VARCHAR (37) CONSTRAINT EQUIPMENT_DATA_LIST_PK PRIMARY KEY,

    ITEM_NO                   TEXT,

    SERVICE                   TEXT,

    NO_REQ                    TEXT,

    FLUID                     TEXT,

    DESC_OF_PART              TEXT,

    OPERATION_CONDITION_TEMP  TEXT,

    OPERATION_CONDITION_PRESS TEXT,

    DESIGN_CONDITION_TEMP     TEXT,

    DESIGN_CONDITION_PRESS    TEXT,

    MATERIAL                  TEXT,

    WEIGHT                    TEXT,

    POWER                     TEXT,

    INSULATION                TEXT,

    PNID_NO                   VARCHAR(256),

    REV                       TEXT

);

CREATE TABLE INSTRUMENT_DATA_LIST (

    UID               VARCHAR (37) PRIMARY KEY,

    ITEM_NO           TEXT,

    SERVICE           TEXT,

    FLOW_RATE         TEXT,

    PRESSURE          TEXT,

    TEMPERATURE       TEXT,

    TYPE              TEXT,

    RANGE             TEXT,

    NOR_LEVEL_MM      TEXT,

    NOR_LEVEL_PERCENT TEXT,

    DEL_PRESS         TEXT,

    SHUT_OFF          TEXT,

    LOCATION          TEXT,

    PNID_NO           VARCHAR(256),

    REV               TEXT

);

CREATE TABLE VALVE_DATA_LIST (

    UID      VARCHAR (37),

    ITEM_NO  TEXT,

    MainSize TEXT,

    SubSize  TEXT,

    PNID_NO  VARCHAR(256) NOT NULL,

    CONSTRAINT VALVE_DATA_LIST_PK PRIMARY KEY (

        UID

    )

);

        appDocData = AppDocData.instance()

        cols = ['UID', 'ITEM_NO', 'PNID_NO']

        values = ['?','?','?']

        param = [str(self.uid), self.tag_no, appDocData.activeDrawing.name]

        """ save attributes to database """

        _attrs = self.getAttributes()

        for key,value in _attrs.items():

            cols.append(key.Attribute)

            values.append('?')

            param.append(value)

        sql = 'insert into EQUIPMENT_DATA_LIST({}) values({})'.format(','.join(cols), ','.join(values))

        res.append((sql, (tuple(param),)))

    '''

        @brief      return equip Data List

        @author     kyouho

        @date       2018.08.14

    '''

    def getEquipmentDataList(self):

        dataList = []

        try:

            from AppDocData import AppDocData

            docData = AppDocData.instance()

            attrs = self.attrs

            for index in range(len(QEngineeringEquipmentItem.EQUIP_COLUMN_LIST)):

                dataList.append('')

            dataList[0] = str(self.uid)

            dataList[14] = docData.imgName

            for attr in attrs:

                attrInfo = docData.getSymbolAttributeByUID(attr.UID)

                attrName = attrInfo[0]

                if QEngineeringEquipmentItem.EQUIP_COLUMN_LIST.count(attrName):

                    colIndex = QEngineeringEquipmentItem.EQUIP_COLUMN_LIST.index(attrName)

                    if type(attr) is UserInputAttribute:

                        dataList[colIndex] = attr.text if attr.text is not None else ''

                    elif type(attr) is QEngineeringTextItem:

                        dataList[colIndex] = attr.text()

                    else:

                        dataList[colIndex] = attr.uid

        except Exception as ex:

            print('error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))

        return dataList

        appDocData = AppDocData.instance()

        cols = ['UID', 'PNID_NO']

        values = ['?', '?']

        param = [(str(self.uid), appDocData.activeDrawing.name)]

        sql = 'insert into INSTRUMENT_DATA_LIST({}) values({})'.format(','.join(cols), ','.join(values))

        res.append((sql, tuple(param)))

        '''

        return note numbers which combines of digits

        '''

        cols = ['UID', 'NOTE_NO', 'DESCRIPTION', 'PNID_NO']

        values = ['?','?', '?', '?']

        params = []

        noteContentsList = self.findNoteContents(self.text())

        for key in noteContentsList.keys():

            params.append((str(self.uid), key, ' '.join(noteContentsList[key]), app_doc_data.activeDrawing.name))

        sql = 'insert into NOTE_DATA_LIST({}) values({})'.format(','.join(cols), ','.join(values))

        res.append((sql, tuple(params)))

        cols = ['UID', 'ITEM_NO', 'PNID_NO', 'MainSize', 'SubSize']

        values = ['?', '?', '?', '?', '?']

        main_size = self.prop('Main Size')

        sub_size = self.prop('Sub Size')

        param = [(str(self.uid), self.name, appDocData.activeDrawing.name, main_size if main_size else '',

                  sub_size if sub_size else '')]

        sql = 'insert into VALVE_DATA_LIST({}) values({})'.format(','.join(cols), ','.join(values))

        res.append((sql, tuple(param)))

        for pt in pts:

            self._pol.append(QPointF(pt[0], pt[1]))

        self._pol.append(QPointF(pts[0][0], pts[0][1]))  # close path

            if component['Area']:

        appDocData = AppDocData.instance()

        cols = ['UID', 'ITEM_NO', 'MainSize', 'SubSize', 'PNID_NO']

        values = ['?', '?', '?', '?', '?']

        size = self.prop('Size')

        param = [(str(self.uid), self.name, size if size else '', size if size else '', appDocData.activeDrawing.name)]

        sql = 'insert into VALVE_DATA_LIST({}) values({})'.format(','.join(cols), ','.join(values))

        res.append((sql, tuple(param)))