def data(self, index, role=Qt.DisplayRole): if not index.isValid(): return QVariant() column = index.column() if role == Qt.DisplayRole: if column == DATAINS: return QVariant(QSqlTableModel.data(self, index).toDate().toString(DATEFORMAT)) if column == IMP: return QVariant("€ %s" % QSqlTableModel.data(self, index).toString()) if role == Qt.TextAlignmentRole: if column == DATAINS: return QVariant(int(Qt.AlignLeft|Qt.AlignVCenter)) else: return QVariant(int(Qt.AlignRight|Qt.AlignVCenter)) if role == Qt.BackgroundRole: if column == QT: if QSqlTableModel.data(self, index).toInt()[0] == 0: return QVariant(QColor(Qt.red)) # default, no specific condition found return QSqlTableModel.data(self, index, role)
def data(self, index, role): """ Custom reimplementation of the method data. It is necessary to work with value map and value relation. index: column index role: role used to get the data """ dbdata = QSqlTableModel.data(self, index, role) column = self.headerData(index.column(), Qt.Horizontal) if self.dict.has_key(column): if isinstance(self.dict[column], dict): valueMap = self.dict[column] if str(dbdata) in valueMap.values(): id = valueMap.values().index(str(dbdata)) return valueMap.keys()[id] elif isinstance(self.dict[column], tuple): tupla = self.dict[column] valueMap = self.makeValueRelationDict(tupla[0], tupla[1]) codes = str(dbdata)[1:-1].split(',') code_names = list() for c in codes: if str(c) in valueMap.values(): id = valueMap.values().index(str(c)) code_name = valueMap.keys()[id] code_names.append(code_name) if len(code_names) > 0: return '{%s}' % ','.join(code_names) return dbdata
def get_color_by_option(self, index): option = QSqlTableModel.data(self, self.index(index.row(), 3)).toString() if option == 'left': return QColor(Qt.darkGreen) if option == 'right': return QColor(Qt.darkCyan) return QVariant()
class EditItemDlg(QDialog): def __init__(self, parent=None): super(EditItemDlg, self).__init__(parent) # # Create model self.locusTypeModel = QSqlTableModel(self) self.locusTypeModel.setTable("tbl_locusType") self.locusTypeModel.select() # Create combo and set its model self.locusTypeComboBox = self.createCombobox(self.locusTypeModel, "typeLabel") typeLabel = QLabel("&Type:") typeLabel.setBuddy(self.locusTypeComboBox) # # Create model self.locusSubTypeModel = QSqlTableModel(self) self.locusSubTypeModel.setTable("tbl_locus_subType1") self.locusSubTypeModel.select() # Create combo and set its model self.locusSubTypeCombobox = self.createCombobox(self.locusSubTypeModel, "subtype1Label") # # self.locusSubTypeCombobox = QComboBox() # self.locusSubTypeCombobox.setModel(self.locusSubTypeModel) # self.locusSubTypeCombobox.setModelColumn( # self.locusTypeModel.fieldIndex("label")) # # Create model # self.locusSubType2Model = QSqlTableModel(self) # self.locusSubType2Model.setTable("tbl_locus_subType1") # self.locusSubType2Model.select() controlLayout = QGridLayout() controlLayout.addWidget(self.locusTypeComboBox, 4, 0) controlLayout.addWidget(self.locusSubTypeCombobox, 5, 0) self.setLayout(controlLayout) self.resize(500, 125) self.locusTypeComboBox.currentIndexChanged.connect(self.f) @staticmethod def createCombobox(model, modelColumn): locusTypeComboBox = QComboBox() locusTypeComboBox.setModel(model) locusTypeComboBox.setModelColumn(model.fieldIndex(modelColumn)) return locusTypeComboBox pyqtSlot('int') def f(self, index): locusTypeModelIndex = self.locusTypeModel.index(index, self.locusTypeModel.fieldIndex("typeID")) # idx = self.locusTypeComboBox.model().index(index, self.locusTypeModel.fieldIndex("ID")) data = self.locusTypeModel.data(locusTypeModelIndex) filter = QString("subtype1ID=%1").arg(data.toPyObject()) self.locusSubTypeModel.setFilter(filter) self.locusSubTypeModel.select() print data.toPyObject()
def data(self, index, role): if role == Qt.FontRole: return self.font if role == Qt.TextAlignmentRole: return Qt.AlignCenter if role == Qt.BackgroundColorRole: return self.get_color_by_option(index) if index.column() == 2: value = QSqlTableModel.data(self, index, Qt.EditRole).toInt()[0] == 1 if role == Qt.CheckStateRole: return Qt.Checked if value else Qt.Unchecked if role == Qt.EditRole or role == Qt.DisplayRole: return QVariant(value) return QVariant() return QSqlTableModel.data(self, index, role)
def data(self, index, role=Qt.DisplayRole): if not index.isValid(): return QVariant() column = index.column() if role == Qt.DisplayRole: if column == SIMP: return QVariant("€ %.2f" % QSqlTableModel.data(self, index).toDouble()[0]) elif column == SIVA: return QVariant("%.2f %%" % QSqlTableModel.data(self, index).toDouble()[0]) if role == Qt.TextAlignmentRole: return QVariant(int(Qt.AlignRight|Qt.AlignVCenter)) # default, no specific condition found return QSqlTableModel.data(self, index, role)
def data(self, index, role): code = QSqlTableModel.data(self, index, role) column = self.headerData(index.column(), Qt.Horizontal) if self.dict.has_key(column): dict = self.dict[column] if str(code) in dict.values(): id = dict.values().index(str(code)) return dict.keys()[id] return code
def data(self, QModelIndex, role): if role == QtCore.Qt.BackgroundRole: # if QModelIndex.row() in [1, 3]: # return QtGui.QBrush(QtCore.Qt.yellow) # else: # return QtGui.QBrush(QtCore.Qt.red) value = QModelIndex.data() print(value) print(type(value)) if value in ('2020', '135'): return QtGui.QBrush(QtCore.Qt.yellow) else: return QtGui.QBrush(QtCore.Qt.red) return QSqlTableModel.data(self, QModelIndex, role)
def data(self, index, role): dbdata = QSqlTableModel.data(self, index, role) column = self.headerData(index.column(), Qt.Horizontal) if self.dict.has_key(column): if isinstance(self.dict[column], dict): valueMap = self.dict[column] if str(dbdata) in valueMap.values(): id = valueMap.values().index(str(dbdata)) return valueMap.keys()[id] elif isinstance(self.dict[column], tuple): tupla = self.dict[column] valueMap = self.makeValueRelationDict(tupla[0], tupla[1]) codes = str(dbdata)[1:-1].split(',') code_names = list() for c in codes: if str(c) in valueMap.values(): id = valueMap.values().index(str(c)) code_name = valueMap.keys()[id] code_names.append(code_name) if len(code_names) > 0: return '{%s}' % ','.join(code_names) return dbdata
class RdpolyRecordEditor(object): """ Handles forms and database connections for editing RAMP features. """ dlg = None rdpoly_model = None rdpoly_mapper = None mcl_model = None mcl_mapper = None def __init__(self, db, selector_tool, iface): self.db = db self.selector_tool = selector_tool self.iface = iface self.prepare_dialog() self.connect_signals() def prepare_dialog(self): """ Prepare MCL edit dialog including setting comobox entries and validation. :return: RampMclEditorDlg """ self.dlg = RampRdpolyEditorDlg() self.dlg.setWindowFlags(Qt.WindowStaysOnTopHint) self.dlg.move(5, 5) self.set_combobox_items() self.set_dialog_validators() def set_combobox_items(self): """ Populate the items in the comboboxes. """ self.dlg.ui.elementComboBox.addItems([''] + ELEMENT_VALUES) self.dlg.ui.hierarchyComboBox.addItems([''] + HIERARCHY_VALUES) self.dlg.ui.offsetComboBox.addItems([''] + OFFSET_VALUES) def set_dialog_validators(self): """ Add validators to the free-text fields of the dialog. """ self.dlg.ui.numberLineEdit.setValidator( QRegExpValidator(QRegExp(r"\d{0,3}"))) def connect_signals(self): """ Connect GUI signals and slots. Extends parent class function """ # GUI controls self.selector_tool.selected_id.connect(self.select_record) save_button = self.dlg.ui.buttonBox.button(QDialogButtonBox.Save) cancel_button = self.dlg.ui.buttonBox.button(QDialogButtonBox.Cancel) save_button.clicked.connect(self.save_record) cancel_button.clicked.connect(self.close_tool) # reject() is called if user presses escape self.dlg.rejected.connect(self.close_tool) # Auto updates on combined ref line edit self.dlg.ui.numberLineEdit.textChanged.connect( self.update_combined_ref) self.dlg.ui.elementComboBox.currentIndexChanged.connect( self.update_combined_ref) self.dlg.ui.elementComboBox.currentIndexChanged.connect( self.set_length_readonly_state) self.dlg.ui.hierarchyComboBox.currentIndexChanged.connect( self.update_combined_ref) self.dlg.ui.offsetComboBox.currentIndexChanged.connect( self.update_combined_ref) def select_record(self, rd_pol_id): """ Update the GUI to populate with data from the chosen record. Show if not already visible. :param rd_pol_id: int, emitted by selector_tool """ try: self.setup_models_and_mappers(rd_pol_id) except rn_except.RampNoLinkedPolyPopupError: return self.set_length_readonly_state() if not self.dlg.isVisible(): # Emit the index changed symbol so update_combined_ref() is triggered, # ensuring combined ref box is populated on initial load self.dlg.ui.elementComboBox.currentIndexChanged.emit( self.dlg.ui.elementComboBox.currentIndex()) self.dlg.show() def close_tool(self): """ Close the dialog, reverting unsaved changes. """ self.mcl_mapper.revert() self.rdpoly_mapper.revert() self.dlg.hide() def save_record(self): """ Save changes to the record, then close dialog. """ self.mcl_mapper.submit() self.rdpoly_mapper.submit() self.update_label_fields() self.update_ref_3() self.iface.mapCanvas().refresh() self.dlg.hide() def setup_models_and_mappers(self, rd_pol_id): """ Load the table data for selected record into a model and map to widgets. """ self.setup_rdpoly_model_and_mapper(rd_pol_id) mcl_ref = self.get_mcl_ref_from_rd_pol_id(rd_pol_id) self.setup_mcl_model_and_mapper(mcl_ref) def get_mcl_ref_from_rd_pol_id(self, rd_pol_id): """ Query database to get mcl_ref associated with given polygon :param rd_pol_id: str, id number :return mcl_ref: str, id number """ sql = """ SELECT mcl_cref FROM rdpoly WHERE rd_pol_id = {}""".format(rd_pol_id) query = QSqlQuery(sql, self.db) if not query.first(): msg = "No MCLs are linked to polygon {}".format(rd_pol_id) raise rn_except.RampNoLinkedPolyPopupError(msg) mcl_ref = query.record().value('mcl_cref') if isinstance(mcl_ref, QPyNullVariant): msg = "No MCLs are linked to polygon {}".format(rd_pol_id) raise rn_except.RampNoLinkedPolyPopupError(msg) return str(mcl_ref) def set_length_readonly_state(self): """ Make the length lineedit writeable for non-MCL fields """ element_value = self.dlg.ui.elementComboBox.currentText() delegate = self.rdpoly_mapper.itemDelegate() element_key = get_key(delegate.element_codes, element_value) if element_key in ('CGWAY', 'FPATH'): self.dlg.ui.lengthLineEdit.setReadOnly(True) self.dlg.ui.lengthLineEdit.setStyleSheet(""" border-width: 0.5px; border-style: solid; border-radius: 2px; border-color: rgb(100, 100, 100); background-color: rgb(213, 234, 234);""") else: self.dlg.ui.lengthLineEdit.setReadOnly(False) self.dlg.ui.lengthLineEdit.setStyleSheet("") def update_label_fields(self): """ Update the label columns of the rdpoly table based on new values """ element = self.rdpoly_data(ELEMENT) side = self.rdpoly_data(OFFSET) number = self.rdpoly_data(DESC_3) if isinstance(number, QPyNullVariant) or number in (0, ''): label = "/{}".format(element) label1 = "/{}/{}".format(element, side) else: label = "/{}/{}".format(element, number) label1 = "/{}/{}/{}".format(element, side, number) self.rdpoly_model.setData(self.rdpoly_model.index(0, LABEL), label) self.rdpoly_model.setData(self.rdpoly_model.index(0, LABEL1), label1) self.rdpoly_model.submit() def update_ref_3(self): """ Update the ref_3 column of rdpoly with value from desc_3. """ number = self.rdpoly_data(DESC_3) self.rdpoly_model.setData(self.rdpoly_model.index(0, REF_3), number) self.rdpoly_model.submit() def setup_rdpoly_model_and_mapper(self, rd_pol_id): """ Load the data for the Polygon portion of the form :param rd_pol_id: str rd_pol_id :return: """ # Set up model self.rdpoly_model = QSqlTableModel(db=self.db) self.rdpoly_model.setTable('rdpoly') self.rdpoly_model.setFilter("rd_pol_id = {}".format(int(rd_pol_id))) self.rdpoly_model.select() if self.rdpoly_model.rowCount() != 1: msg = "Table rdpoly query for rd_pol_id = {} returned {} rows".format( rd_pol_id, self.rdpoly_model.rowCount()) raise rn_except.RdpolyFormBadRdpolyRefError(msg) # Set up rdpoly_mapper self.rdpoly_mapper = QDataWidgetMapper() self.rdpoly_mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit) self.rdpoly_mapper.setModel(self.rdpoly_model) self.rdpoly_mapper.addMapping(self.dlg.ui.rdpolyLineEdit, RD_POL_ID) self.rdpoly_mapper.addMapping(self.dlg.ui.numberLineEdit, DESC_3) self.rdpoly_mapper.addMapping(self.dlg.ui.elementComboBox, ELEMENT) self.rdpoly_mapper.addMapping(self.dlg.ui.hierarchyComboBox, HIERARCHY) self.rdpoly_mapper.addMapping(self.dlg.ui.offsetComboBox, OFFSET) self.rdpoly_mapper.setItemDelegate(RdpolyEditorDelegate(self.dlg)) self.rdpoly_mapper.toFirst() def setup_mcl_model_and_mapper(self, mcl_ref): """ Load the data for the MCL portion of the form :param mcl_ref: str mcl_ref :return: """ # Set up model self.mcl_model = QSqlTableModel(db=self.db) self.mcl_model.setTable('mcl') self.mcl_model.setFilter("mcl_ref = {}".format(int(mcl_ref))) self.mcl_model.select() if self.mcl_model.rowCount() != 1: msg = "MCL query for mcl_ref = {} returned {} rows".format( mcl_ref, self.mcl_model.rowCount()) raise rn_except.RdpolyFormBadMclRefError(msg) # Set up mcl_mapper self.mcl_mapper = QDataWidgetMapper() self.mcl_mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit) self.mcl_mapper.setModel(self.mcl_model) self.mcl_mapper.addMapping(self.dlg.ui.mclLineEdit, MCL_REF) self.mcl_mapper.addMapping(self.dlg.ui.usrnLineEdit, USRN) self.mcl_mapper.addMapping(self.dlg.ui.lorDescPlainTextEdit, LOR_DESC) self.mcl_mapper.addMapping(self.dlg.ui.laneNumberLineEdit, LANE_NUMBER) self.mcl_mapper.addMapping(self.dlg.ui.speedLineEdit, SPEED_LIMIT) self.mcl_mapper.toFirst() def update_combined_ref(self): """ Update combinedRefLineEdit with value derived from other fields. """ if self.mcl_model is None: # This happens if signal calls function before model created return # Get strings from MCLs mcl_ref1 = self.mcl_data(LOR_REF_1) mcl_ref2 = self.mcl_data(LOR_REF_2) # Get strings from rdpoly delegate = self.rdpoly_mapper.itemDelegate() element_value = self.dlg.ui.elementComboBox.currentText() element_key = get_key(delegate.element_codes, element_value) offset_value = self.dlg.ui.offsetComboBox.currentText() offset_key = get_key(delegate.offset_codes, offset_value) number = self.dlg.ui.numberLineEdit.text() new_text = "{}/{}/{}/{}/{}".format(mcl_ref1, mcl_ref2, element_key, offset_key, number) self.dlg.ui.combinedRefLineEdit.setText(new_text) def rdpoly_data(self, column): return self.rdpoly_model.data(self.rdpoly_model.index(0, column)) def mcl_data(self, column): return self.mcl_model.data(self.mcl_model.index(0, column))
class Locus(QtGui.QWidget): def __init__(self): super(Locus, self).__init__() self.layout = QtGui.QGridLayout() self.LocusIdEntry = QtGui.QLineEdit() # # Create model self.locusTypeModel = QSqlTableModel(self) self.locusTypeModel.setTable("tbl_locusType") self.locusTypeModel.select() self.locusSubType1Model = QSqlTableModel(self) self.locusSubType1Model.setTable("tbl_locus_subtype1") #self.locusSubType1Model.select() self.locusSubType2Model = QSqlTableModel(self) self.locusSubType2Model.setTable("tbl_locus_subtype2") self.locusSubType3Model = QSqlTableModel(self) self.locusSubType3Model.setTable("tbl_locus_subtype3") # Create combo and set its model self.locusType = self.createCombobox(self.locusTypeModel, "typeLabel") self.locusSubType1 = self.createCombobox(self.locusSubType1Model, "subtype1Label") self.locusSubType2 = self.createCombobox(self.locusSubType2Model, "subtype2Label") self.locusSubType3 = self.createCombobox(self.locusSubType3Model, "subtype3Label") self.SectorTrenchEntry = QtGui.QLineEdit() self.SpaceRoomEntry = QtGui.QLineEdit() self.LocusDescription = QtGui.QPlainTextEdit() self.LocusDescription.setFixedHeight(100) """Set the layout""" self.layout.addWidget(QtGui.QLabel('Locus Number:', self), 0, 0) self.layout.addWidget(self.LocusIdEntry, 0, 1) self.layout.addWidget(QtGui.QLabel('Locus Type:', self), 1, 0) self.layout.addWidget(self.locusType, 1, 1, ) self.layout.addWidget(QtGui.QLabel('Locus SubType1:', self), 2, 0) self.layout.addWidget(self.locusSubType1, 2, 1) # self.layout.setColumnMinimumWidth(1, 1) self.layout.addWidget(QtGui.QLabel('Locus SubType2:', self), 3, 0) self.layout.addWidget(self.locusSubType2, 3, 1) self.layout.addWidget(QtGui.QLabel('Locus SubType3:', self), 4, 0) self.layout.addWidget(self.locusSubType3, 4, 1) self.layout.addWidget(QtGui.QLabel('Sector/ Trench:', self), 7, 0) self.layout.addWidget(self.SectorTrenchEntry, 7, 1, 1, 1) self.layout.addWidget(QtGui.QLabel('Space/ Room:', self), 8, 0) self.layout.addWidget(self.SpaceRoomEntry, 8, 1, 1, 1) self.layout.addWidget(QtGui.QLabel('Description:', self), 9, 0) self.layout.addWidget(self.LocusDescription, 9, 1, 1, 8) self.map = QtGui.QPushButton('Map', self) self.saveButton = QtGui.QPushButton('Save', self) self.cancelButton = QtGui.QPushButton('Cancel', self) self.addButton = QtGui.QPushButton('Add New', self) self.layout.addWidget(self.map, 11, 0) self.layout.addWidget(self.saveButton, 11, 4) self.layout.addWidget(self.cancelButton, 11, 6) self.layout.addWidget(self.addButton, 11, 2) self.setLayout(self.layout) self.setGeometry(500, 500, 550, 500) self.setWindowTitle('Locus') self.locusType.setCurrentIndex(-1) self.locusType.currentIndexChanged.connect( self.subType1selection) self.locusSubType1.currentIndexChanged.connect(self.subType2selection) self.locusSubType2.currentIndexChanged.connect(self.subType3selection) @staticmethod def createCombobox(model, modelColumn): locusTypeComboBox = QtGui.QComboBox() locusTypeComboBox.setModel(model) locusTypeComboBox.setModelColumn(model.fieldIndex(modelColumn)) return locusTypeComboBox pyqtSlot('int') #def locusSubType1clear(self): def subType1selection(self, index): locusTypeModelIndex = self.locusTypeModel.index(index, self.locusTypeModel.fieldIndex("typeID")) data = self.locusTypeModel.data(locusTypeModelIndex) filter = QString("typeID=%1").arg(data.toPyObject()) self.locusSubType1Model.setFilter(filter) self.locusSubType1Model.select() print data.toPyObject() def subType2selection(self, index): locusSubType1ModelIndex = self.locusSubType1Model.index(index, self.locusSubType1Model.fieldIndex("subtype1ID")) data = self.locusSubType1Model.data(locusSubType1ModelIndex) filter = QString("subtype1ID= %1").arg(data.toPyObject()) self.locusSubType2Model.setFilter(filter) self.locusSubType2Model.select() # print data.toPyObject() # print data def subType3selection(self, index): locusSubType2ModelIndex = self.locusSubType2Model.index(index, self.locusSubType2Model.fieldIndex("subtype2ID")) data = self.locusSubType2Model.data(locusSubType2ModelIndex) filter = QString("subtype2ID=%1").arg(data.toPyObject()) self.locusSubType3Model.setFilter(filter) self.locusSubType3Model.select() print data.toPyObject()
class MainWindow(QMainWindow, magazzino_ui.Ui_MainWindow): FIRST, PREV, NEXT, LAST = range(4) Clipboard = [] # lista di oggetti def __init__(self, parent=None): super(MainWindow, self).__init__(parent) self.setupUi(self) self.setupMenu() self.restoreWinSettings() self.editindex = None self.filename = None self.db = QSqlDatabase.addDatabase("QSQLITE") self.loadInitialFile() self.setupUiSignals() def keyPressEvent(self, event): if event.key() == Qt.Key_Down: self.addDettRecord() else: QMainWindow.keyPressEvent(self, event) def creaStrutturaDB(self): query = QSqlQuery() if not ("magamaster" in self.db.tables()): if not query.exec_("""CREATE TABLE magamaster ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, scaff VARCHAR(10) NOT NULL)"""): QMessageBox.warning(self, "Magazzino", QString("Creazione tabella fallita!")) return False if not ("magaslave" in self.db.tables()): if not query.exec_("""CREATE TABLE magaslave ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, datains DATE NOT NULL, abbi VARCHAR(50), angro VARCHAR(50), desc VARCHAR(100), qt INTEGER NOT NULL DEFAULT '1', imp DOUBLE NOT NULL DEFAULT '0.0', equiv VARCHAR(100), mmid INTEGER NOT NULL, fatt VARCHAR(50), note VARCHAR(200), FOREIGN KEY (mmid) REFERENCES magamaster)"""): QMessageBox.warning(self, "Magazzino", QString("Creazione tabella fallita!")) return False QMessageBox.information(self, "Magazzino", QString("Database Creato!")) return True def loadFile(self, fname=None): if fname is None: return if self.db.isOpen(): self.db.close() self.db.setDatabaseName(QString(fname)) if not self.db.open(): QMessageBox.warning(self, "Magazzino", QString("Database Error: %1") .arg(self.db.lastError().text())) else: if not self.creaStrutturaDB(): return self.filename = unicode(fname) self.setWindowTitle("Gestione Magazzino - %s" % self.filename) self.setupModels() self.setupMappers() self.setupTables() #self.setupItmSignals() self.restoreTablesSettings() self.mmUpdate() def loadInitialFile(self): settings = QSettings() fname = unicode(settings.value("Settings/lastFile").toString()) if fname and QFile.exists(fname): self.loadFile(fname) def openFile(self): dir = os.path.dirname(self.filename) \ if self.filename is not None else "." fname = QFileDialog.getOpenFileName(self, "Gestione Magazzino - Scegli database", dir, "*.db") if fname: self.loadFile(fname) def newFile(self): dir = os.path.dirname(self.filename) \ if self.filename is not None else "." fname = QFileDialog.getSaveFileName(self, "Gestione Magazzino - Scegli database", dir, "*.db") if fname: self.loadFile(fname) def setupMenu(self): # AboutBox self.connect(self.actionA_bout, SIGNAL("triggered()"), self.showAboutBox) # FileNew self.connect(self.action_New_File, SIGNAL("triggered()"), self.newFile) # FileLoad self.connect(self.action_Load_File, SIGNAL("triggered()"), self.openFile) def showAboutBox(self): dlg = aboutmaga.AboutBox(self) dlg.exec_() def printInventory(self): ''' Print Inventory ''' if not self.db.isOpen(): self.statusbar.showMessage( "Database non aperto...", 5000) return querygrp = QSqlQuery() querydett = QSqlQuery() querygrp.exec_("SELECT abbi,qt,imp,sum(qt*imp) " "FROM magaslave GROUP BY abbi") querydett.prepare("SELECT datains,abbi,angro,desc,qt,imp " "FROM magaslave WHERE abbi = :abbi AND " "qt > 0 ORDER BY datains") from reportlab.pdfgen.canvas import Canvas from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.lib.units import cm from reportlab.lib.enums import TA_LEFT,TA_RIGHT,TA_CENTER from reportlab.platypus import Spacer, SimpleDocTemplate from reportlab.platypus import Table, TableStyle, Paragraph from reportlab.rl_config import defaultPageSize from reportlab.lib import colors PAGE_WIDTH, PAGE_HEIGHT=defaultPageSize styles = getSampleStyleSheet() styleN = styles['Normal'] styleH = styles['Heading1'] styleH.alignment=TA_CENTER Elements = [] #add some flowables p=Paragraph ps=ParagraphStyle Title = unicode(self.prtTitleLineEdit.text()) Year = unicode(self.prtDateLineEdit.text()) Author = "Stefano Zamprogno" URL = "http://www.zamprogno.it/" email = "*****@*****.**" pageinfo = "%s / %s / %s" % (Author, email, Title) def myFirstPage(canvas, doc): canvas.saveState() canvas.setStrokeColorRGB(0.50,0.50,0.50) canvas.setLineWidth(10) canvas.line(45,72,45,PAGE_HEIGHT-72) #canvas.setFont('Times-Bold',16) #canvas.drawCentredString(3*cm, 1.5*cm,Title) canvas.setFont('Times-Roman',9) canvas.drawString(3*cm, 1.5*cm, "First Page / %s" % pageinfo) canvas.restoreState() def myLaterPages(canvas, doc): canvas.saveState() canvas.setStrokeColorRGB(0.50,0.50,0.50) canvas.setLineWidth(5) canvas.line(45,72,45,PAGE_HEIGHT-72) canvas.setFont('Times-Roman',9) canvas.drawString(3*cm, 1.5*cm, "Page %d %s" % (doc.page, pageinfo)) canvas.restoreState() Elements.append(Paragraph(Title, styleH)) Elements.append(Paragraph(Year,styleN)) Elements.append(Spacer(0.5*cm, 0.5*cm)) tot=0 while querygrp.next(): tot += querygrp.value(3).toDouble()[0] querydett.bindValue(":abbi", QVariant(querygrp.value(0).toString())) querydett.exec_() data = [['Abbi', 'Angro', 'Descrizione', 'Qt', 'Imp'],] while querydett.next(): data.append([ p(unicode(querydett.value(1).toString()), ps(name='Normal')), p(unicode(querydett.value(2).toString()), ps(name='Normal')), p(unicode(querydett.value(3).toString()), ps(name='Normal')), querydett.value(4).toInt()[0], unicode("%.2f" % querydett.value(5).toDouble()[0])]) data.append([None, None, unicode("GRUPPO '%s'" % querygrp.value(0).toString()), unicode("Subtotale:"), unicode("€ %.2f" % querygrp.value(3).toDouble()[0])]) Elements.append(Table(data,repeatRows=1, style=(['LINEBELOW', (3,-2), (-1,-2), 1, colors.black], ['LINEBELOW', (0,0), (-1,0), 1, colors.black], ['ALIGN', (1,0), (3,-1),'CENTER'], ['ALIGN', (4,0), (-1,0),'RIGHT'], ['VALIGN', (0,0), (-1,-1), 'TOP'], ['ALIGN', (4,0), (-1,-1), 'RIGHT'], # ['TEXTCOLOR', (0,0), (-1,0), # colors.red], ['BACKGROUND',(0,0),(-1,0), colors.lightgrey], ['GRID',(0,0),(-1,-1), 0.2, colors.black], ['FONT', (0, 0), (-1, 0), 'Helvetica-Bold', 10], ['FONT', (3, -1), (3, -1), 'Helvetica-Bold', 10]))) Elements.append(Spacer(0.5*cm, 0.5*cm)) Elements.append(Paragraph("<para align=right><b>TOTALE GENERALE:" "€ %.2f</b></para>" % tot, styleN)) doc = SimpleDocTemplate(os.path.join(os.path.dirname(__file__), 'mydoc.pdf')) doc.build(Elements,onFirstPage=myFirstPage, onLaterPages=myLaterPages) subprocess.Popen(['gnome-open',os.path.join(os.path.dirname(__file__), 'mydoc.pdf')]) def setupMappers(self): ''' Initialize all the application mappers ''' self.mapper = QDataWidgetMapper(self) self.mapper.setModel(self.mModel) self.mapper.addMapping(self.scaffLineEdit, SCAFF) self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit) self.mapper.toFirst() def setupTables(self): """ Initialize all the application tablesview """ self.sTableView.setModel(self.sModel) self.sTableView.setItemDelegate(MSDelegate(self)) self.sTableView.setColumnHidden(ID, True) self.sTableView.setColumnHidden(MMID, True) self.sTableView.setWordWrap(True) self.sTableView.resizeRowsToContents() self.sTableView.setAlternatingRowColors(True) self.sItmSelModel = QItemSelectionModel(self.sModel) self.sTableView.setSelectionModel(self.sItmSelModel) self.sTableView.setSelectionBehavior(QTableView.SelectRows) #self.sTableView.setTabKeyNavigation(True) self.fTableView.setModel(self.fModel) self.fTableView.setColumnHidden(ID, True) self.fTableView.setWordWrap(True) self.fTableView.resizeRowsToContents() self.fTableView.setAlternatingRowColors(True) self.fItmSelModel = QItemSelectionModel(self.fModel) self.fTableView.setSelectionModel(self.fItmSelModel) def setupModels(self): """ Initialize all the application models """ # setup slaveModel self.sModel = ssModel(self) self.sModel.setTable(QString("magaslave")) self.sModel.setHeaderData(ID, Qt.Horizontal, QVariant("ID")) self.sModel.setHeaderData(DATAINS, Qt.Horizontal, QVariant("DataIns")) self.sModel.setHeaderData(ABBI, Qt.Horizontal, QVariant("Abbi")) self.sModel.setHeaderData(ANGRO, Qt.Horizontal, QVariant("Angro")) self.sModel.setHeaderData(DESC, Qt.Horizontal, QVariant("Desc")) self.sModel.setHeaderData(QT, Qt.Horizontal, QVariant("Qt")) self.sModel.setHeaderData(IMP, Qt.Horizontal, QVariant("Imp")) self.sModel.setHeaderData(EQUIV, Qt.Horizontal, QVariant("Equiv")) self.sModel.setHeaderData(MMID, Qt.Horizontal, QVariant("ScaffId")) self.sModel.setHeaderData(FATT, Qt.Horizontal, QVariant("Fatt")) self.sModel.setHeaderData(NOTE, Qt.Horizontal, QVariant("Note")) self.sModel.setSort(DATAINS, Qt.AscendingOrder) self.sModel.setEditStrategy(QSqlTableModel.OnRowChange) self.sModel.select() # setup masterModel self.mModel = QSqlTableModel(self) self.mModel.setTable(QString("magamaster")) self.mModel.setSort(SCAFF, Qt.AscendingOrder) self.mModel.setHeaderData(ID, Qt.Horizontal, QVariant("ID")) self.mModel.setHeaderData(SCAFF, Qt.Horizontal, QVariant("Scaff")) self.mModel.select() # setup findModel self.fModel = QSqlRelationalTableModel(self) self.fModel.setTable(QString("magaslave")) self.fModel.setHeaderData(ID, Qt.Horizontal, QVariant("ID")) self.fModel.setHeaderData(DATAINS, Qt.Horizontal, QVariant("DataIns")) self.fModel.setHeaderData(ABBI, Qt.Horizontal, QVariant("Abbi")) self.fModel.setHeaderData(ANGRO, Qt.Horizontal, QVariant("Angro")) self.fModel.setHeaderData(DESC, Qt.Horizontal, QVariant("Desc")) self.fModel.setHeaderData(QT, Qt.Horizontal, QVariant("Qt")) self.fModel.setHeaderData(IMP, Qt.Horizontal, QVariant("Imp")) self.fModel.setHeaderData(EQUIV, Qt.Horizontal, QVariant("Equiv")) self.fModel.setHeaderData(MMID, Qt.Horizontal, QVariant("ScaffId")) self.fModel.setHeaderData(FATT, Qt.Horizontal, QVariant("Fatt")) self.fModel.setHeaderData(NOTE, Qt.Horizontal, QVariant("Note")) self.fModel.setSort(MMID, Qt.AscendingOrder) self.fModel.setRelation(MMID, QSqlRelation("magamaster", "id", "scaff")) self.fModel.select() def clipCopy(self): self.Clipboard = self.sTableView.selectedIndexes() selrows = self.sItmSelModel.selectedRows() # TODO : da usare: selrows = self.sItmSelModel.selectedRows() print(selrows, len(selrows)) print(len(self.Clipboard)) # FIXME : bla bla bla def clipDel(self): self.delDettRecord() def clipPaste(self): pass def ctxtMenu(self, point): menu = QMenu(self) copyAction = menu.addAction("&Copy") self.connect(copyAction, SIGNAL("triggered()"), self.clipCopy) delAction = menu.addAction("&Del") self.connect(delAction, SIGNAL("triggered()"), self.clipDel) if len(self.Clipboard) > 0: pasteAction = menu.addAction("&Paste") self.connect(pasteAction, SIGNAL("triggered()"), self.clipPaste) menu.exec_(self.sTableView.mapToGlobal(point)) def setupUiSignals(self): self.sTableView.setContextMenuPolicy(Qt.CustomContextMenu) self.connect(self.sTableView, SIGNAL( "customContextMenuRequested(const QPoint &)"), self.ctxtMenu) self.connect(self.scaffLineEdit, SIGNAL("returnPressed()"), lambda: self.saveRecord(MainWindow.FIRST)) self.connect(self.findLineEdit, SIGNAL("returnPressed()"), self.globalFilter) self.connect(self.printPushButton, SIGNAL("clicked()"), self.printInventory) self.connect(self.createFilterPushButton, SIGNAL("clicked()"), self.createFilter) self.connect(self.findPushButton, SIGNAL("clicked()"), self.applyFilter) self.connect(self.gSearchPushButton, SIGNAL("clicked()"), self.globalFilter) self.connect(self.addscaffPushButton, SIGNAL("clicked()"), self.addScaffRecord) self.connect(self.adddettPushButton, SIGNAL("clicked()"), self.addDettRecord) self.connect(self.deldettPushButton, SIGNAL("clicked()"), self.delDettRecord) self.connect(self.delscaffPushButton, SIGNAL("clicked()"), self.delScaffRecord) self.connect(self.scaffFirstPushButton, SIGNAL("clicked()"), lambda: self.saveRecord(MainWindow.FIRST)) self.connect(self.scaffPrevPushButton, SIGNAL("clicked()"), lambda: self.saveRecord(MainWindow.PREV)) self.connect(self.scaffNextPushButton, SIGNAL("clicked()"), lambda: self.saveRecord(MainWindow.NEXT)) self.connect(self.scaffLastPushButton, SIGNAL("clicked()"), lambda: self.saveRecord(MainWindow.LAST)) def globalFilter(self): if not self.db.isOpen(): self.statusbar.showMessage( "Database non aperto...", 5000) return txt = self.findLineEdit.text() qry = ("(datains like '%s') OR " "(abbi like '%s') OR " "(angro like '%s') OR " "(desc like '%s') OR " "(equiv like '%s') OR" "(fatt like '%s') OR" "(note like '%s')") % ((txt,)*7) self.fModel.setFilter(qry) self.updateFilter() def updateFilter(self): self.fModel.select() self.fTableView.setColumnHidden(ID, True) def applyFilter(self): if not self.db.isOpen(): self.statusbar.showMessage( "Database non aperto...", 5000) return self.fModel.setFilter(self.findLineEdit.text()) self.updateFilter() def createFilter(self): if not self.db.isOpen(): self.statusbar.showMessage( "Database non aperto...", 5000) return headerDef = ("datains VARCHAR(100)", "abbi VARCHAR(100)", "angro VARCHAR(100)", "desc VARCHAR(100)", "qt VARCHAR(100)", "imp VARCHAR(100)", "equiv VARCHAR(100)", "fatt VARCHAR(100)", "note VARCHAR(100)") dlg = filterdialog.FilterDialog(headerDef, QSqlDatabase.database(), self) if(dlg.exec_()): self.findLineEdit.setText(dlg.filterDone() if dlg.filterDone() else "") self.applyFilter() #~ def editEsc(self, idxcur, idxold): #~ if self.editindex and self.editindex.isValid(): #~ if idxcur.row() != self.editindex.row(): #~ self.sModel.revertAll() #~ self.editindex = None def mmUpdate(self): row = self.mapper.currentIndex() id = self.mModel.data(self.mModel.index(row,ID)).toString() self.sModel.setFilter("mmid=%s" % id) self.sModel.select() self.sTableView.setColumnHidden(ID, True) self.sTableView.setColumnHidden(MMID, True) def saveRecord(self, where): if not self.db.isOpen(): self.statusbar.showMessage( "Database non aperto...", 5000) return row = self.mapper.currentIndex() self.mapper.submit() self.sModel.revertAll() if where == MainWindow.FIRST: row=0 elif where == MainWindow.PREV: row = 0 if row <= 1 else row - 1 elif where == MainWindow.NEXT: row += 1 if row >= self.mModel.rowCount(): row = self.mModel.rowCount() -1 elif where == MainWindow.LAST: row = self.mModel.rowCount()- 1 self.mapper.setCurrentIndex(row) self.mmUpdate() def addScaffRecord(self): if not self.db.isOpen(): self.statusbar.showMessage( "Database non aperto...", 5000) return row = self.mModel.rowCount() self.mapper.submit() self.mModel.insertRow(row) self.mapper.setCurrentIndex(row) self.scaffLineEdit.setFocus() self.mmUpdate() def addDettRecord(self): if not self.db.isOpen(): self.statusbar.showMessage( "Database non aperto...", 5000) return rowscaff = self.mapper.currentIndex() record = self.mModel.record(rowscaff) masterid = record.value(ID).toInt()[0] if masterid < 1: self.statusbar.showMessage( "Scaffale non valido o non confermato...", 5000) self.scaffLineEdit.setFocus() return # aggiunge la nuova riga alla vista self.sModel.submitAll() self.sModel.select() row = self.sModel.rowCount() self.sModel.insertRow(row) if row > 1: precfatt = self.sModel.data(self.sModel.index(row-1, FATT)) else: precfatt = '' if row > 1: lastData = self.sModel.data(self.sModel.index(row-1, DATAINS)) else: lastData = '' self.sModel.setData(self.sModel.index(row, MMID), QVariant(masterid)) self.sModel.setData(self.sModel.index(row, QT), QVariant(1)) self.sModel.setData(self.sModel.index(row, IMP), QVariant(0.0)) self.sModel.setData(self.sModel.index(row, FATT), QVariant(precfatt)) self.editindex = self.sModel.index(row, DATAINS) self.sTableView.setCurrentIndex(self.editindex) self.sTableView.edit(self.editindex) def delDettRecord(self): if not self.db.isOpen(): self.statusbar.showMessage( "Database non aperto...", 5000) return selrows = self.sItmSelModel.selectedRows() if not selrows: self.statusbar.showMessage( "No articles selected to delete...", 5000) return if(QMessageBox.question(self, "Cancella Articoli", "Vuoi cancellare: {0} articoli?".format(len(selrows)), QMessageBox.Yes|QMessageBox.No) == QMessageBox.No): return QSqlDatabase.database().transaction() query = QSqlQuery() query.prepare("DELETE FROM magaslave WHERE id = :val") for i in selrows: if i.isValid(): query.bindValue(":val", QVariant(i.data().toInt()[0])) query.exec_() QSqlDatabase.database().commit() self.sModel.revertAll() self.mmUpdate() def delScaffRecord(self): if not self.db.isOpen(): self.statusbar.showMessage( "Database non aperto...", 5000) return row = self.mapper.currentIndex() if row == -1: self.statusbar.showMessage( "Nulla da cancellare...", 5000) return record = self.mModel.record(row) id = record.value(ID).toInt()[0] scaff = record.value(SCAFF).toString() if(QMessageBox.question(self, "Cancella Scaffale", "Vuoi cancellare lo scaffale: {0} ?".format(scaff), QMessageBox.Yes|QMessageBox.No) == QMessageBox.No): self.statusbar.showMessage( "Cancellazione scaffale annullata...", 5000) return # cancella scaffale self.mModel.removeRow(row) self.mModel.submitAll() if row + 1 >= self.mModel.rowCount(): row = self.mModel.rowCount() - 1 self.mapper.setCurrentIndex(row) if self.mModel.rowCount() == 0: self.scaffLineEdit.setText(QString("")) # cancella tutti gli articoli che si riferiscono # allo scaffale cancellato self.sModel.setFilter("mmid=%s" % id) self.sModel.select() self.sModel.removeRows(0, self.sModel.rowCount()) self.sModel.submitAll() self.statusbar.showMessage( "Cancellazione eseguita...", 5000) self.mmUpdate() def restoreTablesSettings(self): settings = QSettings(self) if self.saveTableGeometryCheckBox.isChecked(): # per la tabella slave for c in range(1, self.sModel.columnCount()-1): width = settings.value("Settings/sTableView/%s" % c, QVariant(60)).toInt()[0] self.sTableView.setColumnWidth(c, width if width > 0 else 60) # per la tabella find for c in range(1, self.fModel.columnCount()): width = settings.value("Settings/fTableView/%s" % c, QVariant(60)).toInt()[0] self.fTableView.setColumnWidth(c, width if width > 0 else 60) def restoreWinSettings(self): settings = QSettings() self.prtTitleLineEdit.setText(QString(settings.value( "Settings/printTitle", QVariant( "Situazione Magazzino - TIME di Stefano Zamprogno")).toString())) self.prtDateLineEdit.setText(QString(settings.value( "Settings/printDate", QVariant( "Al 31/12/2008")).toString())) self.saveWinPosCheckBox.setChecked( settings.value("Settings/saveWinPos", QVariant(True)).toBool()) self.saveTableGeometryCheckBox.setChecked( settings.value("Settings/saveTableGeometry", QVariant(True)).toBool()) self.restoreGeometry( settings.value("MainWindow/Geometry").toByteArray()) def closeEvent(self, event): settings = QSettings() if self.filename is not None: settings.setValue("Settings/lastFile", QVariant(self.filename)) settings.setValue("MainWindow/Geometry", QVariant( self.saveGeometry())) settings.setValue("Settings/saveWinPos", QVariant( self.saveWinPosCheckBox.isChecked())) settings.setValue("Settings/saveTableGeometry", QVariant( self.saveTableGeometryCheckBox.isChecked())) settings.setValue("Settings/printTitle", QVariant( self.prtTitleLineEdit.text())) settings.setValue("Settings/printDate", QVariant( self.prtDateLineEdit.text())) if self.db.isOpen(): # salva larghezza colonne tabella slave for c in range(1, self.sModel.columnCount()-1): width = self.sTableView.columnWidth(c) if width: settings.setValue("Settings/sTableView/%s" % c, QVariant(width)) # salva larghezza colonne tabella find for c in range(1, self.fModel.columnCount()): width = self.fTableView.columnWidth(c) if width: settings.setValue("Settings/fTableView/%s" % c, QVariant(width)) self.db.close() del self.db