def __btnSaveClicked(self):
if self._currentCodeList == self._storedCodeList:
return
try:
codeType = self._codeType
codeList = self._currentCodeList
# DB Data handling
# 1) delete all data with the specific code_type
# 2) create all data with the specific code_type
db_connection = mysqlHandler.dbConnect()
cursor = mysqlHandler.dbCursor(db_connection)
sql = 'delete from myasset.common_code where code_type = %s'
cursor.execute(sql, codeType)
sql = 'insert into myasset.common_code(code_type, code_id, code_name) values (%s, %s, %s)'
cursor.executemany(sql, codeList)
db_connection.commit()
# 3) Get the data from the code table newly.
sql = 'select code_type, code_id, code_name from myasset.common_code order by code_id'
cursor_dict = mysqlHandler.dbDictCursor(db_connection)
cursor_dict.execute(sql)
# object: dictionary
self._codeAllList = cursor_dict.fetchall()
finally:
db_connection.close()
self._storedCodeList = self._currentCodeList.copy()
self._codeTableWidget.sortByColumn(0, Qt.AscendingOrder)
self.enableOperation(False)
self._btnSave.setEnabled(False)
def setCodeTypeList(self):
# self._codeTree.header().setVisible(False)
self._codeTree.setColumnCount(2)
self._codeTree.setHeaderLabels(['코드유형 ID', '코드유형 명칭'])
self._codeTree.header().setSectionResizeMode(QHeaderView.Stretch)
self._codeTree.setAlternatingRowColors(True)
db_connection = mysqlHandler.dbConnect()
cursor_dict = mysqlHandler.dbDictCursor(db_connection)
sql = 'select code_type, code_type_name from myasset.common_code_hdr order by code_type_name'
cursor_dict.execute(sql)
# object: dictionary
self._codeTypeList = cursor_dict.fetchall()
topNode = QTreeWidgetItem(self._codeTree)
topNode.setText(0, '공통코드')
for r in self._codeTypeList:
item = QTreeWidgetItem(topNode)
item.setText(0, r.get('code_type'))
item.setText(1, r.get('code_type_name'))
self._codeTree.expandAll()
sql = 'select code_type, code_id, code_name from myasset.common_code order by code_id'
cursor_dict.execute(sql)
# object: dictionary
self._codeAllList = cursor_dict.fetchall()
db_connection.close()
def setTableWidgetData(self):
try:
db_connection = mysqlHandler.dbConnect()
cursor = mysqlHandler.dbDictCursor(db_connection)
sql = 'select report_id, report_class, report_name, query_stmt' \
' from myasset.sql_report order by report_class, report_name'
rowCount = cursor.execute(sql)
finally:
db_connection.close()
self._queryTableWidget.setRowCount(rowCount)
self._queryTableWidget.setColumnCount(4)
column_headers = [
'Report Class', 'Report Name', 'Query Statement', 'Report ID'
]
self._queryTableWidget.setHorizontalHeaderLabels(column_headers)
result = cursor.fetchall()
for r, row in enumerate(result):
self._queryTableWidget.setItem(
r, 0, QTableWidgetItem(row.get('report_class')))
self._queryTableWidget.setItem(
r, 1, QTableWidgetItem(row.get('report_name')))
self._queryTableWidget.setItem(
r, 2, QTableWidgetItem(row.get('query_stmt')))
self._queryTableWidget.setItem(
r, 3, QTableWidgetItem(str(row.get('report_id'))))
self._queryTableWidget.resizeColumnsToContents()
self._queryTableWidget.resizeRowsToContents()
def getTable_Org(self):
db_connection = mysqlHandler.dbConnect()
cursor_dict = mysqlHandler.dbDictCursor(db_connection)
sql = 'select company, super_org2, super_org1, org from myasset.it_user_org ' \
' order by company, super_org2, super_org1, org'
cursor_dict.execute(sql)
db_connection.close()
self._codeTypeList = cursor_dict.fetchall()
def getTable_User(self):
db_connection = mysqlHandler.dbConnect()
cursor_dict = mysqlHandler.dbDictCursor(db_connection)
sql = 'select org, user_name, region from myasset.it_user ' \
' order by org, user_name'
cursor_dict.execute(sql)
db_connection.close()
self._userAllList = cursor_dict.fetchall()
def initUIData(self):
# Open DB Connection
self.db_connection = mysqlHandler.dbConnect()
self.cursor_dict = mysqlHandler.dbDictCursor(self.db_connection)
self.getData_MonitorSize()
self.getData_Supplier()
# Close DB Connection
self.db_connection.close()
def __btnRunClicked(self):
db_connection = mysqlHandler.dbConnect()
cursor = mysqlHandler.dbDictCursor(db_connection)
sql = self._inputQuery.toPlainText()
cursor.execute(sql)
result = cursor.fetchall()
self._dataframe = pd.DataFrame(result)
model = pandasModel(self._dataframe)
self._tableView.setModel(model)
db_connection.close()
def initUIData(self):
# Open DB Connection
db_connection = mysqlHandler.dbConnect()
cursor_dict = mysqlHandler.dbDictCursor(db_connection)
# Codes
sql = 'select code_type, code_id, code_name from myasset.common_code ' \
'order by code_type, code_id'
cursor_dict.execute(sql)
codeDict = cursor_dict.fetchall()
# to create dictionary with key-value pair (code id, code name)
self._dictJobType = {}
self._dictDiskType = {}
self._dictDiskCapacity = {}
self._dictLocation = {}
for i in codeDict:
if i.get('code_type') == 'job_type':
self._dictJobType[i.get('code_id')] = i.get('code_name')
if i.get('code_type') == 'disk_type':
self._dictDiskType[i.get('code_id')] = i.get('code_name')
if i.get('code_type') == 'disk_capacity':
self._dictDiskCapacity[i.get('code_id')] = i.get('code_name')
if i.get('code_type') == 'location':
self._dictLocation[i.get('code_id')] = i.get('code_name')
self.fillItems_JobType()
self.fillItems_DiskType()
self.fillItems_DiskCapacity()
self.fillItems_Location()
# Region
sql = 'select region, region_name from myasset.region order by region_name'
cursor_dict.execute(sql)
codeDict = cursor_dict.fetchall()
self._dictRegion = {}
for i in codeDict:
self._dictRegion[i.get('region')] = i.get('region_name')
self.fillItems_Region()
# Org
# sql = 'select org, super_org1, super_org2 from myasset.it_user_org order by org, super_org1, super_org2'
sql = 'select org from myasset.it_user_org order by org'
cursor_dict.execute(sql)
self._orgList = cursor_dict.fetchall()
self.fillItems_Org(self._orgList)
# Close DB Connection
db_connection.close()
def dbLoad(self, specificUserName=None):
try:
db_connection = mysqlHandler.dbConnect()
cursor_dict = mysqlHandler.dbDictCursor(db_connection)
sql = 'select ' \
' user_name, ' \
' job_type, ' \
' org, ' \
' regular_exchange, ' \
' region, ' \
' location, ' \
' extra_disk, ' \
' extra_disk_type, ' \
' extra_disk_capacity, ' \
' user_note '
if specificUserName:
sql += ' from myasset.it_user where user_name = %s'
cursor_dict.execute(sql, specificUserName)
else:
sql += ' from myasset.it_user order by user_name'
cursor_dict.execute(sql)
finally:
db_connection.close()
data = cursor_dict.fetchall() # List with Dictionary items
# to add key-value pairs : code id(key)에 대응하는 name(value)
for i, row in enumerate(data):
row['job_type_name'] = self._dictJobType.get(row.get('job_type'))
row['location_name'] = self._dictLocation.get(row.get('location'))
row['extra_disk_type_name'] = self._dictDiskType.get(
row.get('extra_disk_type'))
row['extra_disk_capacity_name'] = self._dictDiskCapacity.get(
row.get('extra_disk_capacity'))
row['region_name'] = self._dictRegion.get(row.get('region'))
return data