def show_po_list(self):
self.po_list.clear()
self.po_dict = {}
engine = sqc.Database().engine
conn = engine.connect()
pgso_department = sqc.Database().pgso_department
s = pgso_department.select().where(pgso_department.c.type == self.po_type.currentText()). \
where(pgso_department.c.name == self.po_name.currentText())
s_value = conn.execute(s)
id = 0
for val in s_value:
id = val[0]
pgso_procurement = sqc.Database().pgso_procurement
s = pgso_procurement.select().where(
pgso_procurement.c.department_id == id).where(
pgso_procurement.c.status == 'po')
s_value = conn.execute(s)
for val in s_value:
self.po_dict.update({
'{}_(#{})_{}'.format(self.po_name.currentText(), val[0], val[2]):
val[0]
})
for key in self.po_dict.keys():
self.po_list.addItem(key)
def po_edit_action(self):
global gen_tab
gen_tab = 5
self.tabWidget.setCurrentIndex(4)
temp = self.po_list.currentItem().text()
temp2 = temp.split('(')[0] + temp.split(')')[1]
self.gen_title.setText(temp2)
po_id = self.po_dict[self.po_list.currentItem().text()]
engine = sqc.Database().engine
conn = engine.connect()
pgso_procurement_data = sqc.Database().pgso_procurement_data
s = pgso_procurement_data.select().where(
pgso_procurement_data.c.po_id == po_id).order_by(
pgso_procurement_data.c.description)
s_value = conn.execute(s)
table = self.gen_table_widget
table.setRowCount(0)
for val in s_value:
row_position = table.rowCount()
table.insertRow(row_position)
table.setItem(row_position, 0, QTableWidgetItem(str(val[0])))
table.setItem(row_position, 1, QTableWidgetItem(str(val[3])))
table.setItem(row_position, 2, QTableWidgetItem(str(val[1])))
table.setItem(row_position, 3, QTableWidgetItem(str(val[2])))
table.setItem(row_position, 4, QTableWidgetItem(str(val[4])))
table.resizeColumnsToContents()
table.setSizeAdjustPolicy(
QtWidgets.QAbstractScrollArea.AdjustToContents)
def po_delete_action(self):
try:
po_id = self.po_dict[self.po_list.currentItem().text()]
engine = sqc.Database().engine
conn = engine.connect()
pgso_procurement = sqc.Database().pgso_procurement
s = pgso_procurement.delete().where(pgso_procurement.c.id == po_id)
conn.execute(s)
pgso_procurement_data = sqc.Database().pgso_procurement_data
s = pgso_procurement_data.delete().where(
pgso_procurement_data.c.po_id == po_id)
conn.execute(s)
os.remove(os.getcwd() + '\\excel\\' +
str(self.po_dict[self.po_list.currentItem().text()]) +
'.xlsx')
self.show_po_list()
msg = QMessageBox()
msg.setIcon(QMessageBox.Information)
msg.setText("Success")
msg.setInformativeText('File Deleted Properly')
msg.setWindowTitle("Information")
msg.exec_()
except:
msg = QMessageBox()
msg.setIcon(QMessageBox.Critical)
msg.setText("Error")
msg.setInformativeText('Something went wrong in file deletion')
msg.setWindowTitle("Error")
msg.exec_()
def show_settings(self):
self.settings_account_table.setRowCount(0)
engine = sqc.Database().engine
pgso_admin = sqc.Database().pgso_admin
conn = engine.connect()
#admin_table
s = pgso_admin.select().order_by(asc(pgso_admin.c.username))
s_value = conn.execute(s)
table = self.settings_account_table
for val in s_value:
row_position = table.rowCount()
table.insertRow(row_position)
table.setItem(row_position, 0, QTableWidgetItem(str(val[0])))
table.setItem(row_position, 1, QTableWidgetItem(str(val[1])))
table.setItem(row_position, 2, QTableWidgetItem(str(val[2])))
self.settings_department_table.setRowCount(0)
pgso_department = sqc.Database().pgso_department
# admin_table
s = pgso_department.select().order_by(asc(pgso_department.c.type))
s_value = conn.execute(s)
table = settings_department_table
for val in s_value:
row_position = table.rowCount()
table.insertRow(row_position)
table.setItem(row_position, 0, QTableWidgetItem(str(val[0])))
table.setItem(row_position, 1, QTableWidgetItem(str(val[1])))
table.setItem(row_position, 2, QTableWidgetItem(str(val[2])))
def price_list_search_action(self):
global price_list_table
price_list_table.setRowCount(0)
engine = sqc.Database().engine
pgso_price_list = sqc.Database().pgso_price_list
conn = engine.connect()
#admin_table
s = pgso_price_list.select().order_by(asc(pgso_price_list.c.item))
s_value = conn.execute(s)
table = price_list_table
if self.price_list_search.text() == '':
for val in s_value:
row_position = table.rowCount()
table.insertRow(row_position)
table.setItem(row_position, 0, QTableWidgetItem(str(val[0])))
table.setItem(row_position, 1, QTableWidgetItem(str(val[1])))
table.setItem(row_position, 2, QTableWidgetItem(str(val[2])))
table.resizeColumnsToContents()
else:
for val in s_value:
if str(self.price_list_search.text()).lower() in val[1].lower(
):
row_position = table.rowCount()
table.insertRow(row_position)
table.setItem(row_position, 0,
QTableWidgetItem(str(val[0])))
table.setItem(row_position, 1,
QTableWidgetItem(str(val[1])))
table.setItem(row_position, 2,
QTableWidgetItem(str(val[2])))
table.resizeColumnsToContents()
def po_generate_po_action(self):
excel = win32com.client.DispatchEx('Excel.Application')
scbk = excel.Workbooks.Open(os.getcwd() + '\\template\\PO.xlsx',
ReadOnly=True)
scsht = scbk.Worksheets[1]
excel.Visible = True
lrow = find_cell(scsht,
'',
search_order='by_row',
search_sheet='basic')['row']
lcol = find_cell(scsht,
'',
search_order='by_col',
search_sheet='basic')['col']
engine = sqc.Database().engine
conn = engine.connect()
pgso_procurement_data = sqc.Database().pgso_procurement_data
s = pgso_procurement_data.select()
s_value = conn.execute(s)
for val in s_value:
range_insert = scsht.Range("A15:L15")
range_insert.EntireRow.Insert()
s_value = conn.execute(s)
i = 1
row = 15
for val in s_value:
scsht.Cells(row, 1).Value = i
scsht.Cells(row, 2).Value = val[3]
scsht.Cells(row, 3).Value = val[2]
scsht.Cells(row, 4).Value = val[1]
scsht.Cells(
row,
4).HorizontalAlignment = xlwings.constants.HAlign.xlHAlignLeft
scsht.Cells(row, 4).WrapText = False
scsht.Cells(row, 9).Value = val[4]
scsht.Cells(row, 11).Formula = '=C{} * I{}'.format(row, row)
row += 1
i += 1
p = inflect.engine()
date_index = find_cell(scsht, 'Date:', lrow=lrow, lcol=lcol)
scsht.Cells(date_index['row'],
date_index['col'] + 1).Value = datetime.datetime.now()
scsht.Cells(
date_index['row'], date_index['col'] +
1).HorizontalAlignment = xlwings.constants.HAlign.xlHAlignCenter
scsht.Cells(date_index['row'], date_index['col'] + 1).Columns.AutoFit()
purpose_remarks = find_cell(scsht, 'Conforme:', lrow=3000, lcol=lcol)
scsht.Range('I' + str(purpose_remarks['row'] - 4)
).Formula = '=SUM(K16:K{})'.format(purpose_remarks['row'] -
1)
scsht.Range('A' +
str(purpose_remarks['row'] - 4)).Value = p.number_to_words(
float(
str(
scsht.Range('I' + str(purpose_remarks['row'] -
4)).Value)))
def po_generate_pr_action(self):
excel = win32com.client.DispatchEx('Excel.Application')
scbk = excel.Workbooks.Open(os.getcwd() + '\\template\\PR.xlsx',
ReadOnly=True)
scsht = scbk.Worksheets[1]
excel.Visible = True
lrow = find_cell(scsht,
'',
search_order='by_row',
search_sheet='basic')['row']
lcol = find_cell(scsht,
'',
search_order='by_col',
search_sheet='basic')['col']
engine = sqc.Database().engine
conn = engine.connect()
pgso_procurement_data = sqc.Database().pgso_procurement_data
s = pgso_procurement_data.select()
s_value = conn.execute(s)
for val in s_value:
range_insert = scsht.Range("A16:J16")
range_insert.EntireRow.Insert()
s_value = conn.execute(s)
i = 1
row = 16
for val in s_value:
scsht.Cells(row, 1).Value = i
scsht.Cells(row, 2).Value = val[3]
scsht.Cells(row, 3).Value = val[1]
scsht.Cells(
row,
3).HorizontalAlignment = xlwings.constants.HAlign.xlHAlignLeft
scsht.Cells(row, 8).Value = val[2]
scsht.Cells(row, 9).Value = val[4]
scsht.Cells(row, 10).Formula = '=H{} * I{}'.format(row, row)
row += 1
i += 1
department_index = find_cell(scsht,
'Department : ',
lrow=lrow,
lcol=lcol)
purpose_remarks = find_cell(scsht,
'Purpose/Remarks :',
lrow=3000,
lcol=lcol)
scsht.Cells(department_index['row'], department_index['col'] +
2).Value = self.po_name.currentText()
scsht.Cells(purpose_remarks['row'], purpose_remarks['col']
).Value = 'Purpose/Remarks : For {} Use.'.format(
self.po_type.currentText())
scsht.Cells(purpose_remarks['row'], purpose_remarks['col'] +
2).Value = 'For {} use.'.format(self.po_name.currentText())
scsht.Cells(
purpose_remarks['row'] - 1, purpose_remarks['col'] +
8).Formula = '=SUM(J16:J{})'.format(purpose_remarks['row'] - 2)
def show_po_name(self):
self.po_name.clear()
engine = sqc.Database().engine
conn = engine.connect()
pgso_department = sqc.Database().pgso_department
s = pgso_department.select().where(
pgso_department.c.type == self.po_type.currentText())
s_value = conn.execute(s)
for val in s_value:
self.po_name.addItem(val[2])
def gen_purchase_order_action(self):
try:
table = self.gen_table_widget
end_row = table.rowCount()
if gen_tab == 3:
po_id = self.pr_dict[self.pr_list.currentItem().text()]
elif gen_tab == 5:
po_id = self.po_dict[self.po_list.currentItem().text()]
engine = sqc.Database().engine
conn = engine.connect()
pgso_procurement = sqc.Database().pgso_procurement
pgso_procurement_data = sqc.Database().pgso_procurement_data
for i in range(0, end_row):
id = table.item(i, 0).text()
unit = table.item(i, 1).text()
description = table.item(i, 2).text()
try:
quantity = int(table.item(i, 3).text())
except:
quantity = 0
try:
unit_cost = int(table.item(i, 4).text())
except:
unit_cost = 0
u = pgso_procurement_data.update().where(pgso_procurement_data.c.id == id).\
values(
description = description,
quantity = quantity,
unit = unit,
unit_cost = unit_cost
)
conn.execute(u)
u = pgso_procurement.update().where(
pgso_procurement.c.id == po_id).values(status='po')
conn.execute(u)
msg = QMessageBox()
msg.setIcon(QMessageBox.Information)
msg.setText("Inserted")
msg.setInformativeText('Data Inserted to the Database')
msg.setWindowTitle("PGSO Purchase Order")
msg.exec_()
except:
msg = QMessageBox()
msg.setIcon(QMessageBox.Critical)
msg.setText("Error")
msg.setInformativeText('Something Went Wrong , Do it Again')
msg.setWindowTitle("Error")
msg.exec_()
self.tabWidget.setCurrentIndex(2)
def show_price_list(self):
global price_list_table
price_list_table.setRowCount(0)
engine = sqc.Database().engine
pgso_price_list = sqc.Database().pgso_price_list
conn = engine.connect()
#admin_table
s = pgso_price_list.select().order_by(asc(pgso_price_list.c.item))
s_value = conn.execute(s)
table = price_list_table
for val in s_value:
row_position = table.rowCount()
table.insertRow(row_position)
table.setItem(row_position, 0, QTableWidgetItem(str(val[0])))
table.setItem(row_position, 1, QTableWidgetItem(str(val[1])))
table.setItem(row_position, 2, QTableWidgetItem(str(val[2])))
table.resizeColumnsToContents()
conn.close()
def price_list_delete_action(self, table):
try:
r = table.currentRow()
id = table.item(r, 0).text()
engine = sqc.Database().engine
conn = engine.connect()
pgso_price_list = sqc.Database().pgso_price_list
s = pgso_price_list.delete().where(pgso_price_list.c.id == id)
conn.execute(s)
conn.close()
self.show_price_list()
except:
msg = QMessageBox()
msg.setIcon(QMessageBox.Critical)
msg.setText("Error")
msg.setInformativeText('No Rows Selected')
msg.setWindowTitle("Error")
msg.exec_()
def ok_button(self):
engine = sqc.Database().engine
pgso_admin = sqc.Database().pgso_admin
conn = engine.connect()
if self.operationType == 'edit':
self.account_label.setText('Edit Account')
s = pgso_admin.update().where(pgso_admin.c.userid == self.edit_id).\
values(username = self.username.text(),
password = self.password.text())
conn.execute(s)
self.show_settings()
elif self.operationType == 'add':
self.account_label.setText('Add Account')
s = pgso_admin.insert().values(username=self.username.text(),
password=self.password.text())
conn.execute(s)
self.show_settings()
conn.close()
def ok_button(self):
engine = sqc.Database().engine
pgso_price_list = sqc.Database().pgso_price_list
conn = engine.connect()
if self.operationType == 'edit':
self.item_label.setText('Edit Account')
s = pgso_price_list.update().where(pgso_price_list.c.id == self.edit_id).\
values(item = self.item.text(),
price = self.price.text())
conn.execute(s)
self.show_price_list()
elif self.operationType == 'add':
self.item_label.setText('Add Account')
s = pgso_price_list.insert().values(item=self.item.text(),
price=self.price.text())
conn.execute(s)
self.show_price_list()
conn.close()
def ok_button(self):
engine = sqc.Database().engine
pgso_department = sqc.Database().pgso_department
conn = engine.connect()
if self.operationType == 'edit':
self.department_label.setText('Edit Department')
s = pgso_department.update().where(pgso_department.c.id == self.edit_id).\
values(type = self.department_type.currentText(),
name = self.department_name.text())
conn.execute(s)
self.show_settings()
elif self.operationType == 'add':
self.department_label.setText('Add Department')
s = pgso_department.insert().values(
type=self.department_type.currentText(),
name=self.department_name.text())
conn.execute(s)
self.show_settings()
conn.close()
def login_button_action(self):
username = self.login_username.text()
password = self.login_password.text()
engine = sqc.Database().engine
pgso_admin = sqc.Database().pgso_admin
conn = engine.connect()
s = pgso_admin.select()
s_value = conn.execute(s)
for val in s_value:
if str(username).lower() == str(
val[1]).lower() and str(password).lower() == str(
val[2]).lower():
self.tabWidget.setCurrentIndex(1)
self.menu_widget.setVisible(True)
else:
self.login_username.setText('')
self.login_password.setText('')
self.login_error_message.setText(
'Wrong username or password!!')
conn.close()
def open_excel(payrollid):
payroll_employee_dict = {}
engine = sqc.Database().engine
conn = engine.connect()
payroll_bundle = sqc.Database().payroll_bundle
s = payroll_bundle.select().where(payroll_bundle.c.payrollid == payrollid)
s_value = conn.execute(s)
for val in s_value:
payroll_name = val[3]
mydirectory = '{}\{}'.format(os.getcwd(), 'payroll_readonly.xls')
excel = wstp.DispatchEx('Excel.Application')
book = excel.Workbooks.Open(str(mydirectory), ReadOnly=True)
sheet = book.Worksheets(1)
excel.Visible = True
payroll_title = payroll_name.split('#')[0].strip()
payroll_title = str(payroll_title[1:]).upper()
sheet.Range("A4").Value = payroll_title
conn = engine.connect()
employee = sqc.Database().employee
s = employee.select()
s_value = conn.execute(s)
for val in s_value:
payroll_employee_dict.update(
{val[0]: '{} {} {}'.format(val[2], val[3], val[1])})
conn = engine.connect()
payroll_record = sqc.Database().payroll_record
s = payroll_record.select().where(payroll_record.c.payrollid == payrollid)
s_value = conn.execute(s)
for val in s_value:
range_insert = sheet.Range("A11:X11")
range_insert.EntireRow.Insert()
conn = engine.connect()
payroll_record = sqc.Database().payroll_record
s = payroll_record.select().where(
payroll_record.c.payrollid == payrollid).order_by(
desc(payroll_record.c.monthly_rate))
s_value = conn.execute(s)
i = 1
row = 10
for val in s_value:
sheet.Cells(row, 1).Value = i
sheet.Cells(
row,
1).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
sheet.Cells(row, 2).Value = (payroll_employee_dict[int(val[2])])
sheet.Cells(
row,
2).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
sheet.Cells(row, 3).Value = val[4]
sheet.Cells(
row,
3).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
sheet.Cells(row, 4).Value = val[5]
sheet.Cells(
row,
4).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
sheet.Cells(row, 5).Value = val[6]
sheet.Cells(
row,
5).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
sheet.Cells(row, 6).Value = val[7]
sheet.Cells(
row,
6).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
sheet.Cells(row, 7).Value = val[6] + val[7]
sheet.Cells(
row,
7).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
total = 0.0
for x in range(9, 24):
if val[x] < 1:
#sheet.Cells(row,x-1).Value = '-'
sheet.Cells(row, x - 1).Value = val[x]
else:
sheet.Cells(row, x - 1).Value = val[x]
sheet.Cells(
row, x -
1).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
total += val[x]
sheet.Cells(row, 23).Value = (val[6] + val[7]) - total
sheet.Cells(
row,
23).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
sheet.Cells(row, 24).Value = i
sheet.Cells(
row,
24).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
row += 1
i += 1
templist = [
'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q',
'R', 'S', 'T', 'U', 'V', 'W'
]
print(row)
sheet.Cells(row,
1).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
sheet.Cells(row,
2).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
sheet.Cells(row, 3).Value = 'TOTAL'
sheet.Cells(row,
3).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
z = 4
for letter in templist:
sheet.Cells(row, z).Formula = "=SUM({}10:{}{})".format(
letter, letter, row - 1)
sheet.Cells(
row,
z).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
z += 1
sheet.Cells(
row, 24).Borders.LineStyle = xlwings.constants.LineStyle.xlContinuous
signatories = sqc.Database().payroll_signatory
s = signatories.select()
s_value = conn.execute(s)
person = []
designation = []
for val in s_value:
person.append(val[1])
designation.append(val[2])
print('{}-{}'.format(val[1], val[2]))
cell_col = 2
sheet.Cells(row + 6, cell_col).Value = person[1]
sheet.Cells(row + 7, cell_col).Value = designation[1]
sheet.Cells(row + 12, cell_col).Value = person[2]
sheet.Cells(row + 13, cell_col).Value = designation[2]
cell_col = 8
sheet.Cells(row + 4, cell_col).Value = person[3]
sheet.Cells(row + 5, cell_col).Value = designation[3]
sheet.Cells(row + 10, cell_col).Value = person[4]
sheet.Cells(row + 11, cell_col).Value = designation[4]
cell_col = 18
sheet.Cells(row + 7, cell_col).Value = person[0]
sheet.Cells(row + 9, cell_col).Value = designation[0]
def ok_button_action(self):
try:
excel = win32com.client.gencache.EnsureDispatch(
'Excel.Application')
scbk = excel.Workbooks.Open(r'{}'.format(
self.attached_file.text()),
ReadOnly=True)
scsht = scbk.Worksheets[1]
lrow = find_cell(scsht,
'',
search_order='by_row',
search_sheet='basic')['row']
lcol = find_cell(scsht,
'',
search_order='by_col',
search_sheet='basic')['col']
start_index = find_cell(scsht,
'GENERAL DESCRIPTION',
lrow=lrow,
lcol=lcol)
end_row = find_cell(scsht,
'NOTE: Technical Specifications for each Item',
lrow=lrow,
lcol=lcol)['row']
quantity_col = find_cell(scsht, 'QUANTITY', lrow=lrow,
lcol=lcol)['col']
engine = sqc.Database().engine
conn = engine.connect()
pgso_department = sqc.Database().pgso_department
s = pgso_department.select().where(pgso_department.c.type == self.department_type.currentText()).\
where(pgso_department.c.name == self.department_name.currentText())
s_value = conn.execute(s)
id = 0
for val in s_value:
id = val[0]
engine = sqc.Database().engine
conn = engine.connect()
pgso_procurement = sqc.Database().pgso_procurement
s = pgso_procurement.insert().values(
department_id=id,
date_archived=datetime.datetime.utcnow(),
status='pr')
conn.execute(s)
po_id = 0
s = pgso_procurement.select()
s_value = conn.execute(s)
for val in s_value:
po_id = val[0]
conn.close()
for row in range(start_index['row'] + 1, end_row):
col = start_index['col']
if scsht.Cells(row, col).Value is not None:
description = str(scsht.Cells(row, col).Value)
quantity = 0
unit = ''
try:
quantity = int(
remove_non_digits(
str(scsht.Cells(row, quantity_col).Value)))
unit = remove_digits(
str(scsht.Cells(row, quantity_col).Value)).strip()
except:
pass
engine2 = sqc.Database().engine
conn2 = engine2.connect()
price_list_dict = {}
pgso_price_list = sqc.Database().pgso_price_list
s = pgso_price_list.select()
s_value = conn2.execute(s)
for val in s_value:
if is_number(val[2]):
price_list_dict.update(
{val[1].strip().lower(): float(val[2])})
try:
unit_cost = price_list_dict[
description.strip().lower()]
except:
unit_cost = 0
pgso_procurement_data = sqc.Database(
).pgso_procurement_data
s = pgso_procurement_data.insert().values(
description=description,
quantity=quantity,
unit=unit,
unit_cost=unit_cost,
po_id=po_id,
)
conn2.execute(s)
scbk.Close(SaveChanges=False)
excel.Quit()
shutil.copyfile(
os.path.abspath(self.attached_file.text()),
os.path.abspath(os.getcwd() + '\\excel\\' + str(po_id) +
'.xlsx'))
msg = QMessageBox()
msg.setIcon(QMessageBox.Information)
msg.setText("Inserted")
msg.setInformativeText('Data Inserted to the Database')
msg.setWindowTitle("PGSO Purchase Request")
msg.exec_()
self.close()
except:
msg = QMessageBox()
msg.setIcon(QMessageBox.Critical)
msg.setText("Error")
msg.setInformativeText('Something Went Wrong , Do it Again')
msg.setWindowTitle("Error")
msg.exec_()
self.close()
