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 loadUsers(self):
try:
db_connection = mysqlHandler.dbConnect()
cursor = mysqlHandler.dbCursor(db_connection)
sql = 'select user_name, ' \
'job_type, ' \
'org, ' \
'regular_exchange, ' \
'region, ' \
'location, ' \
'extra_disk, ' \
'extra_disk_type, ' \
'extra_disk_capacity, ' \
'user_note ' \
' from myasset.it_user order by user_name'
cursor.execute(sql)
data = cursor.fetchall()
column_headers = [
'user_name', 'job_type', 'org', 'regular_exchange', 'region',
'location', 'extra_disk', 'extra_disk_type',
'extra_disk_capacity', 'user_note'
]
# self._userModel._data = result
self._userModel = UserTableModel(self, data, column_headers)
self._tableView.setModel(self._userModel)
finally:
db_connection.close()
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 __clickedRemove(self):
if self.__USER_EXIST__ == True:
inputUserName = self._userNameTextBox.text()
try:
db_connection = mysqlHandler.dbConnect()
cursor = mysqlHandler.dbCursor(db_connection)
sql = 'delete from myasset.it_user where user_name = %s'
cursor.execute(sql, inputUserName)
db_connection.commit()
# to get rid of the user in the user list object
self.removeUserList(inputUserName)
self.showUserTable()
msg = f'사용자 [{inputUserName}]을(를) 삭제하였습니다.' # f-string
self._msgLabel.setText(msg)
self.initUIWidgets()
self.__USER_EXIST__ == False
self.__CURRENT_USER_NAME = None
finally:
db_connection.close()
def __btnAddClicked(self):
orgText = self._textBoxOrg.text()
middleOrgText = self._textBoxMiddleOrg.text() # super org 1
superOrgText = self._textBoxSuperOrg.text() # super org 2
companyText = self._textBoxCompany.text()
dbData = [companyText, orgText, middleOrgText, superOrgText]
try:
db_connection = mysqlHandler.dbConnect()
cursor = mysqlHandler.dbCursor(db_connection)
sql = 'insert into myasset.it_user_org(company, org, super_org1, super_org2) ' \
'values (%s, %s, %s, %s)'
cursor.execute(sql, dbData)
db_connection.commit()
__DB_TRANSACTION_FLAG__ = True
except Exception as e:
__DB_TRANSACTION_FLAG__ = False
print(e)
finally:
db_connection.close()
if __DB_TRANSACTION_FLAG__ == True:
self.reInitData()
def __btnRemoveClicked(self):
__DB_TRANSACTION_FLAG__ = None
orgText = self._textBoxOrg.text()
middleOrgText = self._textBoxMiddleOrg.text() # super org 1
superOrgText = self._textBoxSuperOrg.text() # super org 2
companyText = self._textBoxCompany.text()
dbData = [companyText, orgText, middleOrgText, superOrgText]
try:
db_connection = mysqlHandler.dbConnect()
cursor = mysqlHandler.dbCursor(db_connection)
sql = 'delete from myasset.it_user_org ' \
' where company = %s and org = %s and super_org1 = %s and super_org2 = %s'
cursor.execute(sql, dbData)
db_connection.commit()
__DB_TRANSACTION_FLAG__ = True
except Exception as e:
__DB_TRANSACTION_FLAG__ = False
print(e)
finally:
db_connection.close()
if __DB_TRANSACTION_FLAG__ == True:
self.reInitData()
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
def __clickedSave(self):
inputUserName = self._userNameTextBox.text()
if self.__CURRENT_USER_NAME != inputUserName and self.isExistent(
inputUserName) == True:
QMessageBox.warning(
self, 'Mismatched Information',
'기존 사용자입니다. 사용자 정보를 조회 후 (변경)저장해 주십시오.\n'
'사용자명을 입력 후 엔터키를 누르거나 조회 버튼을 눌러 사용자 정보를 조회하십시오.')
msg = '잘못된 사용자와 사용자 정보입니다.'
self._msgLabel.setText(msg)
return
# Set user data
dataList = []
dataList.append(inputUserName)
dataList.append(str(self._jobComboBox.currentData()))
# rowIndex = self._orgComboBox.currentIndex()
# org = self._orgModel.item(rowIndex, 0) # 0 - first column
# dataList.append(org.text())
dataList.append(self._orgComboBox.currentText())
if self._regularYesButton.isChecked() == True:
dataList.append('대상')
elif self._regularNoButton.isChecked() == True:
dataList.append('비대상')
dataList.append(self._regionComboBox.currentData())
dataList.append(str(self._locationComboBox.currentData()))
if self._diskYesButton.isChecked() == True:
dataList.append('Y')
dataList.append(str(self._diskTypeComboBox.currentData()))
dataList.append(str(self._diskCapacityComboBox.currentData()))
elif self._diskNoButton.isChecked() == True:
dataList.append('N')
dataList.append(None)
dataList.append(None)
dataList.append(self._noteEdit.text())
newUserFlag = None
try:
db_connection = mysqlHandler.dbConnect()
cursor = mysqlHandler.dbCursor(db_connection)
if self.__USER_EXIST__ == True and self.__CURRENT_USER_NAME == inputUserName: # Run "Update"
dataList.append(inputUserName) # user name in where-clause
sql = 'update myasset.it_user set ' \
'user_name = %s, job_type = %s, org = %s, regular_exchange = %s, region = %s, ' \
'location = %s, extra_disk = %s, extra_disk_type = %s, extra_disk_capacity = %s, user_note = %s ' \
'where user_name = %s'
newUserFlag = False
elif self.__USER_EXIST__ == False or self.__CURRENT_USER_NAME != inputUserName: # Run "Insert" as a new user
sql = 'insert into myasset.it_user ' \
'(user_name, job_type, org, regular_exchange, region, location, extra_disk, extra_disk_type, extra_disk_capacity, user_note) values ' \
'(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
newUserFlag = True
cursor.execute(sql, dataList)
db_connection.commit()
except Exception as e:
newUserFlag = None
print(e)
finally:
db_connection.close()
msg = f'사용자 [{inputUserName}]을(를) 저장하였습니다.' # f-string
self._msgLabel.setText(msg)
self.__USER_EXIST__ = True
self.__CURRENT_USER_NAME = inputUserName
self._btnRemove.setEnabled(True)
self.updateUsersList(dataList, newUserFlag)
self.showUserTable()