def querproduct(self):
self.ui.label_21.setText('')
self.ui.label_22.setText('')
self.ui.label_24.setText('')
self.ui.label_26.setText('')
self.ui.label_28.setText('')
productno = self.ui.comboBox_2.currentText()
prodtype = 0
if self.ui.radioButton_5.isChecked():
prodtype = u'良'
if self.ui.radioButton_6.isChecked():
prodtype = u'不良'
#rs=dbconn.quer(productno,prodtype)
#print rs
model = QtSql.QSqlQueryModel()
sql = u"select prodctuno 编号,time as 时间,prodtype as 良否,qty as 数量,mark as 备注,\
recordman as 记录人,place as 库位 from record \
where prodctuno='%s' and prodtype='%s'" % (productno, prodtype)
model.setQuery(sql)
self.ui.tableView_3.setModel(model)
model2 = QtSql.QSqlQueryModel()
sql = "select sum(qty) from record where prodctuno='%s' and prodtype='%s'" % (
productno, prodtype)
sql1 = "select sum(qty) from record where prodctuno='%s'" % (productno)
sql2 = "select sum(qty) from outrecord where prodctuno='%s' and prodtype='%s'" % (
productno, prodtype)
sql3 = "select sum(qty) from outrecord where prodctuno='%s'" % (
productno)
model2.setQuery(sql)
record = model2.record(0)
self.ui.label_21.setText(record.value('sum(qty)').toString())
model2.setQuery(sql1)
record = model2.record(0)
self.ui.label_24.setText(record.value('sum(qty)').toString())
model2.setQuery(sql3)
record = model2.record(0)
self.ui.label_22.setText(record.value('sum(qty)').toString())
model2.setQuery(sql2)
record = model2.record(0)
self.ui.label_26.setText(record.value('sum(qty)').toString())
model1 = QtSql.QSqlQueryModel()
sql = u"select prodctuno as 编号,time as 时间,prodtype as 良否,-qty as 数量,mark as 备注,\
recordman as 记录人,lingyongman as 领用人,place as 库位 \
from outrecord where prodctuno='%s' and prodtype='%s'" % (productno,
prodtype)
model1.setQuery(sql)
self.ui.tableView_4.setModel(model1)
if self.ui.label_21.text() != '' and self.ui.label_26.text() != '':
self.ui.label_28.setText(
str(
int(self.ui.label_21.text()) -
int(self.ui.label_26.text())))
elif self.ui.label_21.text() != '' and self.ui.label_26.text() == '':
self.ui.label_28.setText(str(int(self.ui.label_21.text())))
def search(self):
self.model = QtSql.QSqlQueryModel(self)
a = self.ui.cxb.isChecked()
if a == True:
a = "y"
else:
a = "A"
print(a)
dir = self.ui.lne_dir.text()
main = self.ui.lne_main.text()
format = self.ui.cb_format.currentText()
format = format[0]
format = str(format)
genre = self.ui.cb_genre.currentText()
genre = genre[0]
genre = str(genre)
lang = self.ui.cb_lang.currentText()
lang = lang[0]
lang = str(lang)
query = "select * from movie where \
(lang = '" + lang + "' or '" + lang + "' = 'A')\
and (genre = '" + genre + "' or '" + genre + "' = 'A')\
and(format = '" + format + "' or '" + format + "' = 'A')\
and(available = '" + a + "' or '" + a + "' = 'A')\
and(director = '" + dir + "' or '" + dir + "' = '')\
and(main_lead = '" + main + "' or '" + main + "' = '')"
self.model.setQuery(query)
self.ui.tableView.setModel(self.model)
def __init__(self, parent=None):
QtGui.QMainWindow.__init__(self)
self.ui = MainWindow.Ui_MainWindow()
self.ui.setupUi(self)
self.db = QtSql.QSqlDatabase.addDatabase("QMYSQL")
self.db.setHostName("localhost")
self.db.setDatabaseName("dancebook")
self.db.setUserName("root")
self.db.setPassword("")
self.db.open()
self.model = QtSql.QSqlQueryModel()
self.model.setQuery("SELECT name FROM dance", self.db)
self.ui.dance_form_table.setModel(self.model)
self.ui.dance_form_table.clicked.connect(self.selcon)
self.ui.dance_form_table.setMaximumWidth(120)
self.ui.plainTextEdit.setStyleSheet("""
border: 2px solid black;
background-color: #773467;
color : #56DD35;
""")
self.o = My_Thread(self) #objest of My_t=Thread class
self.o.start() # for starting the thread
def InitUserPage(self):
self.tmUser = QtSql.QSqlQueryModel(self)
self.tmUser.setQuery('SELECT username FROM user WHERE(ID>0)ORDER BY id DESC')
self.ui.tableUser.setModel(self.tmUser)
self.ui.tableUser.horizontalHeader().setResizeMode(0,QHeaderView.Stretch)
self.ui.tableUser.verticalHeader().hide()
self.ui.tableUser.setSelectionMode(QAbstractItemView.SingleSelection)
def __init__(self):
QtGui.QMainWindow.__init__(self)
self.ventana = Ui_MainWindow()
self.ventana.setupUi(self)
self.model = QtSql.QSqlQueryModel(self)
#Create Objects
self.adding = adding()
self.searching = searching()
self.modifying = modifying()
self.deleting = deleting()
#TableView Initial
self.model.setQuery(
"SELECT nombre, ap_paterno, ap_materno, edad, fecha_registro, grado, grupo FROM alumno JOIN escolares USING(id_alumno);"
)
self.view = self.ventana.tableView
self.view.setModel(self.model)
#Toolbar SIGNAL's
self.connect(self.ventana.actionAgregar, QtCore.SIGNAL('activated()'),
self.agregar)
self.connect(self.ventana.actionBuscar, QtCore.SIGNAL('activated()'),
self.buscar)
self.connect(self.ventana.actionEditar, QtCore.SIGNAL('activated()'),
self.editar)
self.connect(self.ventana.actionBorrar, QtCore.SIGNAL('activated()'),
self.borrar)
#Query SIGNAL's
self.connect(self.adding, QtCore.SIGNAL('agregado'), self.insertar)
self.connect(self.searching, QtCore.SIGNAL('buscado'), self.consultar)
self.connect(self.modifying, QtCore.SIGNAL('editado'), self.actualizar)
self.connect(self.deleting, QtCore.SIGNAL('borrado'), self.eliminar)
def add_gridpending(self):
gridpending = QtGui.QGridLayout()
self.db_table = QtGui.QTableView(self)
self.model = QtSql.QSqlQueryModel()
self.model.setQuery("SELECT * FROM sale")
self.db_table.setModel(self.model)
#self.db_table.hideColumn(0) # hide column 'id'
self.db_table.hideColumn(6) #
self.db_table.hideColumn(7) #
self.db_table.hideColumn(9)
self.db_table.hideColumn(10) # hide column 'status'
#self.db_table.
self.db_table.setSelectionBehavior(
QtGui.QAbstractItemView.SelectRows) # select Row
self.db_table.setSelectionMode(
QtGui.QAbstractItemView.SingleSelection) # disable multiselect
self.db_table.horizontalHeader().setResizeMode(
QtGui.QHeaderView.Stretch)
gridpending.addWidget(self.db_table, 0, 0, 1, 5)
done_btn = QtGui.QPushButton("Done")
done_btn.resize(done_btn.sizeHint())
gridpending.addWidget(done_btn, 1, 4)
#done_btn.clicked.connect(self.submit_from_pending)
cancel_btn = QtGui.QPushButton("Cancel")
cancel_btn.resize(cancel_btn.sizeHint())
gridpending.addWidget(cancel_btn, 1, 3)
cancel_btn.clicked.connect(self.cancel_from_pending)
self.grid.addLayout(gridpending, 4, 0, 8, 2)
''' self.view = QtGui.QTableView()
def get_dates_from_event_room_model(self, event, room):
"""Gets the Dates Model. Useful for Qt GUI based Frontends to load the Model into Views."""
self.datesModel = QtSql.QSqlQueryModel()
self.datesModel.setQuery(
"SELECT DISTINCT date FROM presentations WHERE Event='%s' and Room='%s' ORDER BY Date ASC"
% (event, room))
return self.datesModel
def __init__(self, header, tablename, datatypes, asOfDate, datamodel=None, menu=False, parent=None):
# data types: s: string, d: date, t: datetime, f: float, i: int
super(ViewBase, self).__init__(parent)
self.header = header
self.tablename = tablename
self.datatypes = datatypes
self.asOfDate = asOfDate
self.build_query()
self.dataModel = datamodel or QtSql.QSqlQueryModel()
self.dataModel.setQuery(self.query)
for i, h in enumerate(self.header):
self.dataModel.setHeaderData(i, QtCore.Qt.Horizontal, h)
self.proxyModel = QtGui.QSortFilterProxyModel()
self.proxyModel.setDynamicSortFilter(True)
self.proxyModel.setSourceModel(self.dataModel)
self.setModel(self.proxyModel)
self.verticalHeader().hide()
self.resizeColumnsToContents()
#self.resizeRowsToContents()
self.setSortingEnabled(True)
self.setAlternatingRowColors(True)
self.setSelectionBehavior(QtGui.QTableView.SelectRows)
if menu:
self.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
self.customContextMenuRequested.connect(self.showRightClickMenu)
self.buildMenu()
def listar_cerrados(self):
self.cursor = self.con.cursor()
self.model = QtSql.QSqlQueryModel()
self.model.setQuery(
'SELECT ST.N_TICKET AS "N° TICKET",ST.N_S_FK AS "NUMERO DE SERIE",ST.MOTIVO,ST.ESTADO,ST.FECHA_CONIG FROM ST WHERE ST.ESTADO = "Caso Cerrado"'
)
self.ui.comboBox.clear()
self.ui.comboBox.addItems([
self.model.headerData(x, QtCore.Qt.Horizontal)
for x in range(self.model.columnCount())
]) ##Llena el combobox con el nombre de las columnas
self.filter_proxy_model.setSourceModel(self.model)
self.ui.Edit_Filtro.textChanged.connect(self.on_lineEdit_textChanged)
self.ui.comboBox.currentIndexChanged.connect(
self.on_comboBox_currentIndexChanged)
self.model_sele = QtGui.QItemSelectionModel(self.filter_proxy_model)
self.ui.tableWidget.setModel(self.filter_proxy_model)
self.ui.tableWidget.setSelectionModel(self.model_sele)
self.con.commit()
self.ticket = str(
self.filter_proxy_model.data(self.filter_proxy_model.index(0, 0)))
self.model_sele.selectionChanged.connect(
self.fila_diferente_seleccionada) #Fila Seleccionada
self.ui.btn_Lista_Cerrados.setText("Listar Casos no resueltos")
self.ui.btn_Alta.setEnabled(False)
#self.ui.btn_Modificar.setEnabled(False)
self.ui.btn_BorrarRegistro.setEnabled(False)
self.ui.btn_Cambiar_Estado.setEnabled(False)
QtCore.QObject.connect(self.ui.btn_Lista_Cerrados,
QtCore.SIGNAL('clicked()'),
self.Completar_lista_alumnos)
def _slotSearchClicked(self):
search_coords = [
float(d.strip()) for d in self.ui.coords.text().split(",")
]
qm = QtSql.QSqlQueryModel()
# DB type, host, user, password...
db = QtSql.QSqlDatabase.addDatabase("QPSQL")
db.setHostName("lsnpgt01")
db.setDatabaseName("voiriegeo")
db.setUserName("voiriegeo_adm")
db.setPassword("Ls3Qt6msjmok")
ok = db.open()
# True if connected
if ok:
print 'Connected to Postgresql'
else:
print 'Postgresql'
# do a query "on" a DB connection
query = self.query % (self.ui.table.currentText(), search_coords[0],
search_coords[1], self.ui.distance.value())
print query
qm.setQuery(query)
self.ui.results.setModel(qm)
db.close()
def get_rooms_model(self, event):
"""Gets the Rooms Model. Useful for Qt GUI based Frontends to load the Model into Views"""
self.roomsModel = QtSql.QSqlQueryModel()
self.roomsModel.setQuery(
"SELECT DISTINCT Room FROM presentations WHERE Event='%s' ORDER BY Room ASC"
% event)
return self.roomsModel
def student(self):
db=QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("feedback.db")
db.open()
self.model = QtSql.QSqlQueryModel(self)
self.model.setQuery("select b.batch_id as ID, c.course_name as 'Name', b.batch_start_date as 'Start Date', b.batch_day as 'Day', b.batch_time as 'Time' from batch b, courses c where b.course_id = c.course_id")
self.ui.tableView.setModel(self.model)
def courses_in_metacourse(self, metacourse):
self.courses_in_metacourse_model = QtSql.QSqlQueryModel()
self.courses_in_metacourse_model.setQuery(
QtCore.QString("""
SELECT course.fullname
FROM course_meta JOIN course ON course_meta.parent_course = course.id
WHERE course_meta.child_course = %1;
""").arg(metacourse))
self.ui.listView_3.setModel(self.courses_in_metacourse_model)
def get_address_matchmodel(self, search_values):
'''
get's a list of addresses who's criteria match a user search
'''
query = '''SELECT ix, addr1, addr2, addr3, city,
county, country, postal_cd from addresses
WHERE '''
conds, values = '', []
address_id = search_values.get("address_id")
if address_id:
conds += "ix = ?"
values.append(address_id)
else:
addr = search_values.get("addr1")
if addr:
values.append(u"%%%s%%"% addr)
values.append(u"%%%s%%"% addr)
values.append(u"%%%s%%"% addr)
conds += "(addr1 like ? or addr2 like ? or addr3 like ?) and "
addr = search_values.get("addr2")
if addr:
values.append(u"%%%s%%"% addr)
values.append(u"%%%s%%"% addr)
values.append(u"%%%s%%"% addr)
conds += "(addr1 like ? or addr2 like ? or addr3 like ?) and "
city = search_values.get("city")
if city:
conds += 'city like ? '
values.append("%"+city+"%")
country = search_values.get("country")
if country:
conds += 'country like ? '
values.append("%"+country+"%")
pcde = search_values.get("postal_cd")
if pcde:
conds += 'postal_cd like ? '
values.append("%"+pcde+"%")
query = query + conds.rstrip("and ")
q_query = QtSql.QSqlQuery(self)
q_query.prepare(query)
for value in values:
q_query.addBindValue(value)
if not q_query.exec_():
print "error with query", query
self.emit_caught_error(q_query.lastError())
model = QtSql.QSqlQueryModel()
model.setQuery(q_query)
return model
def get_events_model(self):
"""
Gets the Events Model.
Useful for Qt GUI based Frontends to load the Model into Views.
"""
self.eventsModel = QtSql.QSqlQueryModel()
self.eventsModel.setQuery("SELECT DISTINCT Event FROM presentations ORDER BY Event ASC")
return self.eventsModel
def curall(self):
model = QtSql.QSqlQueryModel(self)
sql = u'''
select total_all.prodctuno as 编号,productlist.productname as 名称,\
total_all.total as 总数,total_all.ok as 良品,total_all.bad as 不良 from total_all,productlist
where total_all.prodctuno=productlist.prodcutno;
'''
model.setQuery(sql)
self.ui.tableView_8.setModel(model)
def meta_courses_list(self):
self.meta_course_model = QtSql.QSqlQueryModel()
self.meta_course_model.setQuery(
"SELECT id, fullname FROM course WHERE metacourse=0 AND id != 1;",
self.db)
self.ui.listView_5.setModel(self.meta_course_model)
self.ui.listView_5.setModelColumn(1)
self.ui.comboBox_2.setModel(self.meta_course_model)
self.ui.comboBox_2.setModelColumn(1)
def student(self):
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("feedback.db")
db.open()
self.model = QtSql.QSqlQueryModel(self)
self.model.setQuery(
'select st_id as "ID", st_full_name as "Name", st_parent_name as "Parent'
+ "'s Name" + '", st_address_1 as "Address" from student;')
self.ui.tableView.setModel(self.model)
def __init__(self, application):
QtGui.QMainWindow.__init__(self)
self._filename = None
self.application = application
self.database_menu = database.DatabaseMenu(application.settings)
self.query_executer = QueryExecuter(self)
self.query_model = QtSql.QSqlQueryModel(self)
self.statusbar_updater = QtCore.QTimer()
self.setupUi()
self.setupConnections()
def load_in_process(self):
qry = query("in_process_orders")
if qry:
in_process_mod = QtSql.QSqlQueryModel()
in_process_mod.setQuery(qry)
self.in_process_table.setModel(in_process_mod)
self.in_process_table.resizeColumnsToContents()
return True
else:
return False
def load_available_orders(self):
qry = query("active_orders")
if qry:
active_mod = QtSql.QSqlQueryModel()
active_mod.setQuery(qry)
self.active_table.setModel(active_mod)
self.active_table.resizeColumnsToContents()
return True
else:
return False
def load_setup_orders(self):
qry = query("setup_orders")
if qry:
setup_mod = QtSql.QSqlQueryModel()
setup_mod.setQuery(qry)
self.setup_table.setModel(setup_mod)
self.setup_table.resizeColumnsToContents()
return True
else:
return False
def load_material_orders(self):
qry = query("material_orders")
if qry:
material_mod = QtSql.QSqlQueryModel()
material_mod.setQuery(qry)
self.waiting_table.setModel(material_mod)
self.waiting_table.resizeColumnsToContents()
return True
else:
return False
def outdata(self):
import time
prodtype = 0
qty = 0
if self.ui.radioButton_4.isChecked():
prodtype = u'良'
if self.ui.radioButton_3.isChecked():
prodtype = u'不良'
if prodtype == 0:
QMessageBox.information(self, 'Waring', u'请选择良品或者不良品！！！')
return
bh = str(self.ui.comboBox.currentText())
if self.ui.lineEdit_6.text() == '':
QMessageBox.information(self, 'Waring', u'出库数量不能为空请重新输入！！！')
return
else:
qty = int(self.ui.lineEdit_6.text())
recordman = str(self.ui.lineEdit_5.text().toUtf8())
lingyongman = str(self.ui.lineEdit_7.text().toUtf8())
mark = str(self.ui.lineEdit_21.text().toUtf8())
#print bh,qty,recordman,mark,prodtype
kw = str(self.ui.lineEdit_13.text().toUtf8())
sqlstr = [
bh,
time.strftime('%Y-%m-%d'), prodtype, qty, mark, recordman,
lingyongman, kw
]
# print sqlstr
self.ui.textEdit.append(u"编号：" + bh + u"...时间:" +
time.strftime('%Y-%m-%d') + u"...类型:" +
prodtype + u"....数量：" + str(qty) + u"...备注：" +
mark + u"...记录人：" + recordman + u"....领用人:" +
lingyongman + u"....库位：" + kw +
u"...........数据更新成功！！")
#dbconn.outdata(sqlstr)
print sqlstr
ss = tuple(sqlstr)
sql1 = (
"insert into outrecord(prodctuno,time,prodtype,qty,mark,recordman,lingyongman,place) \
values('%s','%s','%s',%d,'%s','%s','%s','%s')" % ss)
model = QtSql.QSqlQueryModel()
model.setQuery(sql1)
print model.submit()
sql = u'''
select prodctuno as 编号,time as 时间,prodtype as 良否,qty as 数量,\
lingyongman as 领用人,
place as 库位,recordman as 记录人, mark as 备注 from outrecord
'''
model.setQuery(sql)
self.ui.tableView_5.setModel(model)
self.ui.lineEdit_6.setText('')
self.ui.lineEdit_7.setText('')
self.ui.lineEdit_5.setText('')
self.ui.lineEdit_21.setText('')
self.ui.lineEdit_13.setText('')
def ret(self):
"""conn = sqlite3.connect("data.db")
cursor = conn.cursor()
cursor.execute("select * from student")
a = cursor.fetchall()
print(a)
conn.commit()"""
self.model = QtSql.QSqlQueryModel(self)
sql = "select s.Name,f.Q1,f.Q2,f.Q3,f.Q4 from feedback f, student s where f.ID = s.ID;"
self.model.setQuery(sql)
self.ui.tableView.setModel(self.model)
def ret(self):
"""conn = sqlite3.connect("data.db")
cursor = conn.cursor()
cursor.execute("select * from student")
a = cursor.fetchall()
print(a)
conn.commit()"""
self.model = QtSql.QSqlQueryModel(self)
sql = "select ID, Name, School from student;"
self.model.setQuery(sql)
self.tableView.setModel(self.model)
def get_combo_box(self, parent=None):
db = Database.get('postgres')
self.query_model = QtSql.QSqlQueryModel()
self.query_model.setQuery(LIST_DATABASES, db)
self.combo_box = QtGui.QComboBox(parent)
self.combo_box.setModel(self.query_model)
self.connect(self.combo_box, QtCore.SIGNAL('activated(QString)'),
self.change_database)
self.combo_box.setCurrentIndex(-1)
return self.combo_box
def __init__(self, parent=None):
QtCore.QAbstractItemModel.__init__(self, parent)
self.model = QtSql.QSqlQueryModel(parent)
self.root_item = _TreeItem(None)
self._past_items = None
self.patient_id = None
QtGui.QApplication.instance().db_signaller.connect(
self.receive_db_notification)
def btn_retrieveall(self):
self.model = QtSql.QSqlQueryModel(self)
sql = " select st_full_name as 'Full Name', f1.fb_text as 'FB Text 1', c.fb_rating1 as 'Rating 1',\
f2.fb_text 'FB Text 2', c.fb_rating2 as 'Rating 2', f3.fb_text as 'FB Text 3', c.fb_rating3 as 'Rating 3', f4.fb_text as 'FB Text 4', c.fb_rating4 as 'Rating 4',\
f5.fb_text as 'FB Text 5', c.fb_rating5 as 'Rating 5',f6.fb_text as 'FB Text 6', c.fb_rating6 as 'Rating 6', c.course_id as cid, c.st_id as stid\
from st_feedback c, student s, feedback f1, feedback f2, feedback f3, feedback f4, \
feedback f5 left join feedback f6 on c.fb_id6 = f6.fb_id where c.fb_id1 = f1.fb_id and c.fb_id2 =f2.fb_id \
and c.fb_id3 = f3.fb_id and c.fb_id4 = f4.fb_id \
and c.fb_id5 = f5.fb_id\
and s.st_id = c.st_id"
self.model.setQuery(sql)
self.tableView.setModel(self.model)
def outall(self):
self.model = QtSql.QSqlQueryModel(self)
sql = u'''
select outrecord.prodctuno as 编号,productlist.productname as 物品名称,\
-sum(outrecord.qty) as 总数
from outrecord,productlist
where outrecord.prodctuno=productlist.prodcutno
group by
outrecord.prodctuno
'''
self.model.setQuery(sql)
self.ui.tableView_7.setModel(self.model)
