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()