def __init__(self, parent=None):
QtGui.QWidget.__init__(self, parent)
self.ui = Ui_Form()
self.ui.setupUi(self)
self.clientId = None
self.modelClients = CSqlQueryModel()
self.modelClients.setQuery(
"SELECT ID, Deleted, Surname, Name, MidName, BirthDate, Sex, SNILS, Growth, Weight, birthPlace, notes FROM Client WHERE deleted = 0 ORDER BY Surname, Name, MidName")
self.ui.tableView.setModel(self.modelClients)
self.modelServices = QSqlQueryModel()
self.installEventFilter(self)
self.ui.btnClear.clicked.connect(self.clear)
self.ui.btnSearch.clicked.connect(self.search)
self.ui.btnPrint.clicked.connect(self.createReport)
self.ui.tableView.setColumnHidden(0, True)
self.ui.tableView.setColumnHidden(1, True)
self.ui.tableView_2.setColumnHidden(0, True)
QtCore.QObject.connect(self.ui.tableView.selectionModel(),
QtCore.SIGNAL('currentRowChanged(QModelIndex, QModelIndex)'), self.getClientId)
self.ui.leAge.setValidator(
QtGui.QRegExpValidator(QtCore.QRegExp("0[0-9][1-9]|1[12][0-9]|[1-9][1-9]|[1-9]"), self))
self.ui.leAge.textChanged.connect(self.updateBirthDate)
self.ui.leBirthDay.textChanged.connect(self.updateYear)
self.ui.tableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.ui.tableView.customContextMenuRequested.connect(self.popup)
class CWindow(QtGui.QWidget):
def __init__(self, parent=None):
QtGui.QWidget.__init__(self, parent)
self.ui = Ui_Form()
self.ui.setupUi(self)
self.clientId = None
self.modelClients = CSqlQueryModel()
self.modelClients.setQuery(
"SELECT ID, Deleted, Surname, Name, MidName, BirthDate, Sex, SNILS, Growth, Weight, birthPlace, notes FROM Client WHERE deleted = 0 ORDER BY Surname, Name, MidName")
self.ui.tableView.setModel(self.modelClients)
self.modelServices = QSqlQueryModel()
self.installEventFilter(self)
self.ui.btnClear.clicked.connect(self.clear)
self.ui.btnSearch.clicked.connect(self.search)
self.ui.btnPrint.clicked.connect(self.createReport)
self.ui.tableView.setColumnHidden(0, True)
self.ui.tableView.setColumnHidden(1, True)
self.ui.tableView_2.setColumnHidden(0, True)
QtCore.QObject.connect(self.ui.tableView.selectionModel(),
QtCore.SIGNAL('currentRowChanged(QModelIndex, QModelIndex)'), self.getClientId)
self.ui.leAge.setValidator(
QtGui.QRegExpValidator(QtCore.QRegExp("0[0-9][1-9]|1[12][0-9]|[1-9][1-9]|[1-9]"), self))
self.ui.leAge.textChanged.connect(self.updateBirthDate)
self.ui.leBirthDay.textChanged.connect(self.updateYear)
self.ui.tableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.ui.tableView.customContextMenuRequested.connect(self.popup)
def popup(self, pos):
menu = QtGui.QMenu()
edit = menu.addAction(u"Редактировать")
add = menu.addAction(u"Добавить")
delete = menu.addAction(u"Удалить")
action = menu.exec_(self.mapToGlobal(pos))
if action == edit:
self.editWindow()
if action == delete:
query = QSqlQuery()
query.prepare("UPDATE client SET Deleted=1 WHERE ID=:client_id;")
query.bindValue(":client_id", self.clientId)
query.exec_()
if action == add:
self.createWindow()
def editWindow(self):
addUserForm = CAddUserForm(self, self.clientId)
addUserForm.setModal(True)
addUserForm.exec_()
self.ui.btnSearch.click()
def createWindow(self):
createUserForm = CAddUserForm(self)
createUserForm.setModal(True)
createUserForm.exec_()
self.ui.btnSearch.click()
def convertToYear(self, date):
d = QDate.fromString(date, 'dd.MM.yyyy')
return QDate.currentDate().year() - d.year()
def chooseSaveFile(self, fileName, ext):
return QtGui.QFileDialog.getSaveFileName(self, u"Сохранить файл", fileName, '*' + ext)
def createReport(self):
if self.ui.radioButton.isChecked():
self.createHtmlReport()
else:
self.createXlsReport()
def createHtmlReport(self):
homepath = expanduser('~')
filePath = self.chooseSaveFile(os.path.join(homepath, 'report.html'), 'html')
if not filePath:
return
editor = QtGui.QTextDocument()
cursor = QtGui.QTextCursor(editor)
cursor.beginEditBlock()
cursor.insertHtml(u"<B>ПРИМЕР ОТЧЕТА №1 от %s</B><BR>" % datetime.datetime.now().strftime('%Y-%m-%d'))
cursor.insertHtml(u"Дополнительная строка %s от %s" % ('_' * 3, '_' * 10))
clientQuery = QSqlQueryModel()
clientQuery.setQuery(
"SELECT Surname, Name, Midname, birthdate, sex, snils FROM Client where id=%s" % self.clientId)
tableFormat = QtGui.QTextTableFormat()
constraints = [QtGui.QTextLength(QtGui.QTextLength.PercentageLength, 14),
QtGui.QTextLength(QtGui.QTextLength.PercentageLength, 14)]
tableFormat.setBorder(0)
tableFormat.setCellPadding(2)
tableFormat.setCellSpacing(0)
tableFormat.setColumnWidthConstraints(constraints)
tableFormat.setHeaderRowCount(10)
table = cursor.insertTable(4, 2, tableFormat)
paramMap = {}
paramMap[0] = [u'ФИО пациента:', clientQuery.record(0).value("Surname").toString() + " " +
clientQuery.record(0).value("Name").toString() + " " +
clientQuery.record(0).value("Midname").toString()]
paramMap[1] = [u'Дата рождения:', unicode(clientQuery.record(0).value('birthdate').toString())]
paramMap[2] = [u'СНИЛС', unicode(clientQuery.record(0).value('snils').toString())]
paramMap[3] = [u'Пол', u'М' if 1 == clientQuery.record(0).value('sex').toInt() else u'Ж']
for param in paramMap:
for i in xrange(2):
cell = table.cellAt(param, i)
cellCursor = cell.firstCursorPosition()
cellCursor.insertText(paramMap[param][i])
cursor.movePosition(QtGui.QTextCursor.End)
cursor.insertHtml(u"<B>Информация об оказанных услугах</B><BR><BR>")
cursor.movePosition(QtGui.QTextCursor.End)
constraints = [QtGui.QTextLength(QtGui.QTextLength.PercentageLength, 5),
QtGui.QTextLength(QtGui.QTextLength.PercentageLength, 10),
QtGui.QTextLength(QtGui.QTextLength.PercentageLength, 25)]
tableFormat.setBorder(1)
tableFormat.setCellPadding(2)
tableFormat.setCellSpacing(0)
tableFormat.setColumnWidthConstraints(constraints)
tableFormat.setHeaderRowCount(1)
clientQuery.setQuery('''SELECT distinct s.*
FROM Client c
INNER join cure cr on cr.Client_id = c.id
INNER join services s on s.id = cr.services_id
where c.id = %d''' % self.clientId)
table = cursor.insertTable(clientQuery.rowCount() + 1, 3, tableFormat)
values = [u'№ п/п', u'Код', u'Наименование услуги']
for i in xrange(3):
cell = table.cellAt(0, i)
cellCursor = cell.firstCursorPosition()
cellCursor.insertText(values[i])
for i in xrange(clientQuery.rowCount()):
for j in xrange(clientQuery.columnCount()):
cell = table.cellAt(i + 1, j)
cellCursor = cell.firstCursorPosition()
cellCursor.insertText(unicode(clientQuery.record(i).value(j).toString()))
cursor.movePosition(QtGui.QTextCursor.End)
cursor.insertHtml(u'Дата печати: <B>%s</B><BR>' % datetime.datetime.now().strftime('%Y-%m-%d'))
cursor.endEditBlock()
writer = QtGui.QTextDocumentWriter()
writer.setFileName(filePath)
writer.setFormat("HTML")
writer.write(editor)
def createXlsReport(self):
homepath = expanduser('~')
filePath = self.chooseSaveFile(os.path.join(homepath, 'report.xls'), 'xls')
if not filePath:
return
excelWorkbook = xlwt.Workbook()
invoice = excelWorkbook.add_sheet("ReportExample")
cell_middle = xlwt.easyxf("align: vert centre, horz centre, wrap on")
cell_left = xlwt.easyxf("align: vert centre, horz left")
cell_middle_bold = xlwt.easyxf("font: bold on; align: vert centre, horz centre, wrap on")
cell_middle_bold_borders = xlwt.easyxf(
"font: bold on; border: left thin, top thin, right thin, bottom thin; align: vert centre, horz centre, wrap on")
cell_string = xlwt.easyxf(
"border: left thin, top thin, right thin, bottom thin; align: vert centre, horz left, wrap on")
cell_underline = xlwt.easyxf("font: underline on; align: vert centre, horz left, wrap on")
cell_left_with_borders = xlwt.easyxf(
"border: left thin, top thin, right thin, bottom thin; align: vert centre, horz left, wrap on")
cell_right_with_borders = xlwt.easyxf(
"border: left thin, top thin, right thin, bottom thin; align: vert centre, horz right, wrap on")
invoice.write_merge(1, 1, 0, 9, u"ПРИМЕР ОТЧЕТА №1 от %s" % datetime.datetime.now().strftime('%Y-%m-%d'),
cell_middle_bold)
invoice.write_merge(2, 2, 0, 9, u"Дополнительная строка %s от %s" % ('_' * 3, '_' * 10), cell_middle_bold)
clientQuery = QSqlQueryModel()
clientQuery.setQuery(
"SELECT Surname, Name, Midname, birthdate, sex, snils FROM Client where id=%s" % self.clientId)
invoice.write(4, 0, u'ФИО пациента:', cell_left)
invoice.write(4, 1, unicode(clientQuery.record(0).value("Surname").toString() + " " +
clientQuery.record(0).value("Name").toString() + " " +
clientQuery.record(0).value("Midname").toString()), cell_left)
invoice.write(5, 0, u'Дата рождения:', cell_left)
invoice.write(5, 1, unicode(clientQuery.record(0).value('birthdate').toString()), cell_left)
invoice.write(6, 0, u'СНИЛС:', cell_left)
invoice.write(6, 1, unicode(clientQuery.record(0).value('snils').toString()), cell_left)
invoice.write(7, 0, u'Пол', cell_left)
invoice.write(7, 1, u'М' if 1 == clientQuery.record(0).value('sex').toInt() else u'Ж', cell_left)
clientQuery.setQuery('''SELECT distinct s.*
FROM Client c
INNER join cure cr on cr.Client_id = c.id
INNER join services s on s.id = cr.services_id
where c.id = %d''' % self.clientId)
invoice.write_merge(9, 9, 0, 9, u"Информация об оказанных услугах", cell_middle_bold)
invoice.write(10, 0, u'№ п/п', cell_left_with_borders)
invoice.write(10, 1, u'Код', cell_left_with_borders)
invoice.write_merge(10, 10, 2, 9, u"Наименование услуги", cell_left_with_borders)
for i in xrange(clientQuery.rowCount()):
invoice.write(11 + i, 0, i + 1, cell_left_with_borders)
invoice.write(11 + i, 1, unicode(clientQuery.record(i).value('code').toString()), cell_left_with_borders)
invoice.write_merge(11 + i, 11 + i, 2, 9, unicode(clientQuery.record(i).value('Name').toString()),
cell_left_with_borders)
invoice.write(clientQuery.rowCount() + 12, 0, u'Дата печати:')
invoice.write(clientQuery.rowCount() + 12, 1, datetime.datetime.now().strftime('%Y-%m-%d'))
excelWorkbook.save(filePath)
return
def convertToDate(self, year):
return QDate(QDate.currentDate().year() - year, QDate.currentDate().month(),
QDate.currentDate().day()).toString('dd.MM.yyyy')
def updateYear(self):
self.ui.leAge.blockSignals(True)
if str(self.ui.leBirthDay.text()).strip() != u'..':
self.ui.leAge.setText(str(self.convertToYear(str(self.ui.leBirthDay.text()))))
else:
self.ui.leAge.clear()
self.ui.leAge.blockSignals(False)
def updateBirthDate(self):
self.ui.leBirthDay.blockSignals(True)
if self.ui.leAge.text():
self.ui.leBirthDay.setText(self.convertToDate(int(self.ui.leAge.text())))
else:
self.ui.leBirthDay.clear()
self.ui.leBirthDay.blockSignals(False)
def clear(self):
for child in self.findChildren(QtGui.QLineEdit):
child.clear()
for child in self.findChildren(QtGui.QComboBox):
child.setCurrentIndex(0)
def search(self, index):
ustr = u''
query = u'''SELECT DISTINCT c.ID, c.Deleted, c.Surname, c.Name, c.MidName, c.BirthDate, c.Sex, c.SNILS, c.Growth, c.Weight, c.birthPlace, c.notes
FROM Client c
LEFT JOIN cure cr on cr.Client_ID = c.id
LEFT JOIN services s on s.id = cr.Services_ID '''
if self.ui.leSurname.text():
ustr += u"c.Surname like '%{}%'".format(unicode(self.ui.leSurname.text()))
if self.ui.leName.text():
if ustr:
ustr += u" and "
ustr += u"c.Name like '%{}%'".format(unicode(self.ui.leName.text()))
if self.ui.leMidName.text():
if ustr:
ustr += u" and "
ustr += u"c.MidName like '%{}%'".format(unicode(self.ui.leMidName.text()))
if self.ui.cmbSex.currentIndex() > 0:
if ustr:
ustr += u" and "
ustr += u"c.sex={}".format(self.ui.cmbSex.currentIndex())
if len(fromSNILS(self.ui.leSNILS.text())) == 11:
if ustr:
ustr += u" and "
ustr += u"c.SNILS = '{}'".format(fromSNILS(self.ui.leSNILS.text()))
if str(self.ui.leBirthDay.text()).strip() != u'..':
if ustr:
ustr += u" and "
ustr += u"c.BirthDate='{}'".format(
QDate.fromString(self.ui.leBirthDay.text(), 'dd.MM.yyyy').toString('yyyy-MM-dd'))
if self.ui.cmbHideDeleted.currentIndex() == 0:
if ustr:
ustr += u" and "
ustr += u"c.Deleted = 0"
if ustr:
ustr = query + u' WHERE ' + ustr
else:
ustr = query
self.modelClients.setQuery(ustr + u" ORDER BY c.Surname, c.Name, c.MidName")
def getClientId(self, index):
if index:
self.clientId = int(self.modelClients.record(index.row()).value(0).toString())
self.modelServices.setQuery('''SELECT s.id, s.code, s.name
FROM Client c
LEFT JOIN cure cr on cr.Client_ID = c.id
LEFT JOIN services s on s.id = cr.Services_ID
WHERE c.id = {0}
ORDER BY s.code, s.name
'''.format(self.clientId))
self.ui.tableView_2.setModel(self.modelServices)
self.ui.tableView_2.setColumnHidden(0, True)
def eventFilter(self, editor, event):
if event.type() == QEvent.KeyPress:
if event.key() in [Qt.Key_Return, Qt.Key_Enter]:
self.ui.btnSearch.click()
if event.key() == Qt.Key_Escape:
self.ui.btnClear.click()
self.ui.btnSearch.click()
return QtGui.QWidget.eventFilter(self, editor, event)
