def delAsset(self):
listRow = []
rowSelected = 0
for i in range(self.tblAsset.rowCount()):
if self.tblAsset.item(i, 0).isSelected() == True:
rowSelected = rowSelected + 1
listRow.append(self.tblAsset.item(i, 0).text())
if rowSelected > 0:
ans = QtWidgets.QMessageBox.question(
self, 'Delete Assets', "Do you want to delete some assets?",
QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No,
QtWidgets.QMessageBox.No)
if ans == QtWidgets.QMessageBox.Yes:
cur = connection.connection()
con = connection.con
for j in range(len(listRow)):
for k in range(self.tblAsset.rowCount()):
if self.tblAsset.item(k, 0).text() == listRow[j]:
cur.execute("delete from tAssets where AssetNo='" +
listRow[j] + "'")
connection.con.commit()
print(listRow[j])
self.tblAsset.removeRow(k)
break
else:
continue
else:
QtWidgets.QMessageBox.about(
self, "Delete Asset",
"Please select one or more item in the asset table!")
def editableData(self):
for i in range(self.parent.tblAsset.rowCount()):
if self.parent.tblAsset.item(i, 0).isSelected() == True:
self.txtAssetNo.setText(self.parent.tblAsset.item(i, 0).text())
self.txtSN.setText(self.parent.tblAsset.item(i, 1).text())
cur = connection.connection()
row = cur.execute(
"select tCategories.Name, tLocations.LocName, tAssets.AsDesc, tAssets.AcqCost, tAssets.DepMeth, tAssets.UsefulLive, tAssets.AcqDate from tCategories,tLocations,tAssets where tCategories.CategoryID=tAssets.CategoryID and tLocations.LocationID=tAssets.LocationID and tAssets.AssetNo='"
+ self.parent.tblAsset.item(i, 0).text() + "'")
self.irow = i
row = cur.fetchone()
idx = self.cmbCategory.findText(row[0],
QtCore.Qt.MatchFixedString)
if idx >= 0:
self.cmbCategory.setCurrentIndex(idx)
idx = self.cmbLocation.findText(row[1],
QtCore.Qt.MatchFixedString)
if idx >= 0:
self.cmbLocation.setCurrentIndex(idx)
idx = self.cmbDep.findText(row[4], QtCore.Qt.MatchFixedString)
if idx >= 0:
self.cmbDep.setCurrentIndex(idx)
self.txtAssetDesc.setText(row[2])
self.txtAcqCost.setText(str(row[3]))
self.txtUseLive.setText(str(row[5]))
self.dpAcqDate.setDate(
datetime.datetime.strptime(str(row[6]), '%m/%d/%Y'))
break
def delCriteria(self):
cur = connection.connection()
if (self.tvwDatType.currentItem().parent().text(0) == "By Category"):
cur.execute(
"select count(*) as amount from tCategories,tAssets where tCategories.CategoryID=tAssets.CategoryID and tCategories.Name='"
+ self.tvwDatType.currentItem().text(0) + "'")
rAmount = cur.fetchone()
if rAmount[0] > 0:
QtWidgets.QMessageBox.about(
self, "Delete Category",
"Can not delete this category. This category has been use on several items!"
)
else:
cur.execute("delete from tCategories where Name='" +
self.tvwDatType.currentItem().text(0) + "'")
connection.con.commit()
self.tvwDatType.currentItem().parent().removeChild(
self.tvwDatType.currentItem())
else:
cur.execute(
"select count(*) as amount from tLocations,tAssets where tLocations.LocationID=tAssets.LocationID and tLocations.LocName='"
+ self.tvwDatType.currentItem().text(0) + "'")
rAmount = cur.fetchone()
if rAmount[0] > 0:
QtWidgets.QMessageBox.about(
self, "Delete Location",
"Can not delete this location. This location has been use on several items!"
)
else:
cur.execute("delete from tLocations where LocName='" +
self.tvwDatType.currentItem().text(0) + "'")
connection.con.commit()
self.tvwDatType.currentItem().parent().removeChild(
self.tvwDatType.currentItem())
def fillByType(self, vtype):
cur = connection.connection()
if vtype == "By Category":
cur.execute("select name from tCategories")
rCat = cur.fetchall()
for row in rCat:
self.twCat.addChild(QtWidgets.QTreeWidgetItem([row[0]]))
else:
cur.execute("select locname from tLocations")
rLoc = cur.fetchall()
for row in rLoc:
self.twLoc.addChild(QtWidgets.QTreeWidgetItem([row[0]]))
def fillComboList(self):
cur = connection.connection()
row = cur.execute("select name from tCategories")
for r in row:
self.cmbCategory.addItem(r[0])
row = cur.execute("select locname from tLocations")
for r in row:
self.cmbLocation.addItem(r[0])
# depreciation method
self.cmbDep.addItem("SLN")
self.cmbDep.addItem("DDB")
self.cmbDep.addItem("SYD")
if self.parent.assetOp == "edit":
self.editableData()
def fillTable(self):
self.tblAsset.setRowCount(0)
#cols = 9
time.sleep(1)
cur = connection.connection()
con = connection.con
if (self.tvwDatType.currentItem().parent().text(0) == "By Category"):
cur.execute(
"select tAssets.AssetNo, tAssets.SN, tAssets.AsDesc, tAssets.AcqDate, tCategories.Name, tLocations.LocName, tAssets.AcqCost, tAssets.DepMeth, tAssets.UsefulLive from tAssets, tCategories, tLocations where tAssets.CategoryID=tCategories.CategoryID and tAssets.LocationID=tLocations.LocationID and tCategories.Name='"
+ self.tvwDatType.currentItem().text(0) +
"' order by tAssets.AssetNo")
else:
cur.execute(
"select tAssets.AssetNo, tAssets.SN, tAssets.AsDesc, tAssets.AcqDate, tCategories.Name, tLocations.LocName, tAssets.AcqCost, tAssets.DepMeth, tAssets.UsefulLive from tAssets, tCategories, tLocations where tAssets.CategoryID=tCategories.CategoryID and tAssets.LocationID=tLocations.LocationID and tLocations.LocName='"
+ self.tvwDatType.currentItem().text(0) +
"' order by tAssets.AssetNo")
rows = cur.fetchall()
self.tblAsset.setRowCount(len(rows))
no = 0
for r in rows:
self.tblAsset.setItem(no, 0, QtWidgets.QTableWidgetItem(r[0]))
self.tblAsset.setItem(no, 1, QtWidgets.QTableWidgetItem(r[1]))
self.tblAsset.setItem(no, 2, QtWidgets.QTableWidgetItem(r[2]))
self.tblAsset.setItem(no, 3, QtWidgets.QTableWidgetItem(r[3]))
self.tblAsset.setItem(
no, 4, QtWidgets.QTableWidgetItem('{:0,.0f}'.format(r[6])))
self.tblAsset.item(no, 4).setTextAlignment(QtCore.Qt.AlignRight)
self.tblAsset.setItem(no, 5, QtWidgets.QTableWidgetItem(r[7]))
self.tblAsset.item(no, 5).setTextAlignment(QtCore.Qt.AlignRight)
self.tblAsset.setItem(no, 6, QtWidgets.QTableWidgetItem(str(r[8])))
self.tblAsset.item(no, 6).setTextAlignment(QtCore.Qt.AlignRight)
dNow = datetime.date.today()
dAcq = datetime.datetime.strptime(r[3], "%m/%d/%Y")
deltYears = dNow.year - dAcq.year
cV = self.currVal(r[6], r[7], deltYears, r[8])
self.tblAsset.setItem(
no, 7, QtWidgets.QTableWidgetItem('{:0,.0f}'.format(cV)))
self.tblAsset.item(no, 7).setTextAlignment(QtCore.Qt.AlignRight)
no = no + 1
header = self.tblAsset.horizontalHeader()
header.setSectionResizeMode(0, QtWidgets.QHeaderView.ResizeToContents)
def fillGraph(self):
cur = connection.connection()
con = connection.con
cur.execute(
"select tCategories.Name, count(tAssets.AssetID) from tCategories,tAssets where tAssets.CategoryID=tCategories.CategoryID group by tCategories.Name"
)
rows = cur.fetchall()
labelsv = []
sizesv = []
for r in rows:
labelsv.append(r[0])
sizesv.append(r[1])
self.ax1.clear()
self.ax1.pie(sizesv,
labels=labelsv,
autopct='%1.1f%%',
shadow=True,
startangle=140)
self.ax1.axis('equal')
self.canvCategory.draw_idle()
cur.execute(
"select tLocations.LocName, count(tAssets.AssetID) from tLocations,tAssets where tAssets.LocationID=tLocations.LocationID group by tLocations.LocName"
)
rows = cur.fetchall()
labelsc = []
sizesc = []
for r in rows:
labelsc.append(r[0])
sizesc.append(r[1])
self.ax2.clear()
self.ax2.pie(sizesc,
labels=labelsc,
autopct='%1.1f%%',
shadow=True,
startangle=140)
self.ax2.axis('equal')
self.canvLocation.draw_idle()
def addCriteria(self):
txtNew, okPressed = QtWidgets.QInputDialog.getText(
self, "New Category/Location", "New Data:",
QtWidgets.QLineEdit.Normal, "")
if okPressed and txtNew != '':
cur = connection.connection()
if (self.tvwDatType.currentItem().parent().text(0) == "By Category"
):
cur.execute(
"select count(*) as amount from tCategories where Name='" +
txtNew + "'")
rAmount = cur.fetchone()
if rAmount[0] > 0:
QtWidgets.QMessageBox.about(
self, "New Category",
"This new category already exist!")
else:
cur.execute("insert into tCategories(Name) values('" +
txtNew + "')")
connection.con.commit()
self.twCat.addChild(QtWidgets.QTreeWidgetItem([txtNew]))
else:
cur.execute(
"select count(*) as amount from tLocations where LocName='"
+ txtNew + "'")
rAmount = cur.fetchone()
if rAmount[0] > 0:
QtWidgets.QMessageBox.about(
self, "New Location",
"This new location already exist!")
else:
cur.execute("insert into tLocations(LocName) values('" +
txtNew + "')")
connection.con.commit()
self.twLoc.addChild(QtWidgets.QTreeWidgetItem([txtNew]))
else:
QtWidgets.QMessageBox.about(self, "New Data",
"Data can not empty!")
def okAddEditAsset(self):
if (self.txtAssetNo.text() == ""):
self.lblWarning.setText("Please type asset number!")
self.txtAssetNo.setFocus()
return
if (self.txtSN.text() == ""):
self.lblWarning.setText("Please type asset serial number!")
self.txtSN.setFocus()
return
if (self.txtAssetDesc.text() == ""):
self.lblWarning.setText("Please type asset Description!")
self.txtAssetDesc.setFocus()
return
if (self.txtAcqCost.text() == ""):
self.lblWarning.setText("Please type acquairing cost!")
self.txtAcqCost.setFocus()
return
if (self.txtAcqCost.text().isnumeric() == False):
self.lblWarning.setText("Please type acquairing cost!")
self.txtAcqCost.setFocus()
return
if (self.txtUseLive.text() == ""):
self.lblWarning.setText("Please type usefull year!")
self.txtUseLive.setFocus()
return
if (self.txtUseLive.text().isnumeric() == False):
self.lblWarning.setText("Please type usefull year!")
self.txtUseLive.setFocus()
return
cur = connection.connection()
con = connection.con
if self.parent.assetOp == "add":
cur.execute("select count(*) as amt from tAssets where AssetNo='" +
self.txtAssetNo.text() + "'")
rsCount = cur.fetchone()
if (rsCount[0] != 0):
self.lblWarning.setText(
"Asset number exist in database. Please type new asset number!"
)
self.txtAssetNo.setFocus()
return
cur.execute("select count(*) as amt from tAssets where SN='" +
self.txtSN.text() + "'")
rsCount = cur.fetchone()
if (rsCount[0] != 0):
self.lblWarning.setText(
"Serial number exist in database. Please type new serial number!"
)
self.txtSN.setFocus()
return
cur.execute("select categoryID from tCategories where Name='" +
self.cmbCategory.currentText() + "'")
rsID = cur.fetchone()
catID = rsID[0]
cur.execute("select LocationID from tLocations where LocName='" +
self.cmbLocation.currentText() + "'")
rsID = cur.fetchone()
locID = rsID[0]
cur.execute(
"insert into tAssets (CategoryID, LocationID, AssetNo, SN, AsDesc, AcqDate, AcqCost, DepMeth, UsefulLive) values ("
+ str(catID) + "," + str(locID) + ",'" +
self.txtAssetNo.text() + "','" + self.txtSN.text() + "','" +
self.txtAssetDesc.text() + "','" + self.dpAcqDate.text() +
"'," + self.txtAcqCost.text() + ",'" +
self.cmbDep.currentText() + "'," + self.txtUseLive.text() +
")")
con.commit()
#if (self.cmbCategory.currentText()==self.parent.tvwDatType.currentItem().text(0)):
self.parent.fillTable()
self.close()
else:
cur.execute("select categoryID from tCategories where Name='" +
self.cmbCategory.currentText() + "'")
rsID = cur.fetchone()
catID = rsID[0]
cur.execute("select LocationID from tLocations where LocName='" +
self.cmbLocation.currentText() + "'")
rsID = cur.fetchone()
locID = rsID[0]
cur.execute("update tAssets set CategoryID=" + str(catID) +
", LocationID=" + str(locID) + ", AssetNo='" +
self.txtAssetNo.text() + "', SN='" +
self.txtSN.text() + "', AsDesc='" +
self.txtAssetDesc.text() + "', AcqDate='" +
self.dpAcqDate.text() + "', AcqCost=" +
self.txtAcqCost.text() + ", DepMeth='" +
self.cmbDep.currentText() + "', UsefulLive=" +
self.txtUseLive.text() + " where AssetNo='" +
self.parent.tblAsset.item(self.irow, 0).text() + "'")
con.commit()
#if (self.cmbCategory.currentText()==self.parent.tvwDatType.currentItem().text(0)):
self.parent.fillTable()
self.close()