def update(self):
self.clear()
labels = self._labels.copy()
isRollback = False
dbfFile = None
self._db.transaction()
try:
for fileName in self._fileNames:
if fileName[:1] in labels.keys():
try:
dbfFile = Dbf(os.path.join(self._dirName, fileName), readOnly=True, encoding='cp866')
if dbfFile.recordCount:
if not self._callbackUpdate(fileName, 0, dbfFile.recordCount):
isRollback = True
return
insertRowCount = 0
rbTable = self._db.table('rbCheckAccounts' + fileName[:1])
for dbfRow in dbfFile:
newRecord = rbTable.newRecord()
for fieldName, fieldValue in dbfRow.asDict().items():
fieldValue = forceStringEx(fieldValue)
if fieldValue:
newRecord.setValue(forceString(fieldName), toVariant(fieldValue))
self._db.insertRecord(rbTable, newRecord)
insertRowCount += 1
if not self._callbackUpdate(fileName, insertRowCount, dbfFile.recordCount):
isRollback = True
return
del labels[fileName[:1]]
dbfFile.close()
dbfFile = None
except Exception as e:
isRollback = True
raise self.InsertError(fileName, e)
requiredFiles = [label for label, required in labels.items() if required]
if requiredFiles:
isRollback = True
raise self.RequiredFilesNotFoundError(requiredFiles)
self._db.commit()
finally:
if isRollback:
self._db.rollback()
if dbfFile:
dbfFile.close()
QtGui.qApp.removeTmpDir(self._dirName)
def getDataFromDbfFile(parentWidget, fileName):
from library.dbfpy.dbf import Dbf
reportDataByNumbers = {}
dbf = Dbf(fileName,
readOnly=True,
encoding='cp866',
enableFieldNameDups=True)
unexistsFields = []
for needField in [
'NREESTR', 'NSVOD', 'KODLPU', 'KSPEC', 'NSCHT', 'STOIM', 'ZPL',
'DOPL', 'MED', 'M_INV', 'NAKL'
]:
if needField not in dbf.fieldNames:
unexistsFields.append(needField)
if unexistsFields:
QtGui.QMessageBox.warning(
parentWidget,
u'Ошибка',
u'В указанном файле исходных данных отчета\n отсутсвтуют поля:\n%s'
% ('\n'.join(unexistsFields)),
buttons=QtGui.QMessageBox.Ok,
defaultButton=QtGui.QMessageBox.NoButton)
return reportDataByNumbers, '_____________________________________________'
rows = dbf.recordCount
row = 0
nr = u'%05d' % forceInt(dbf[row]['NREESTR'])
table = QtGui.qApp.db.table('Organisation')
platrec = QtGui.qApp.db.getRecordEx(
table, 'shortName',
'infisCode LIKE \'61%%%s\' and deleted = 0' % nr[:2])
plat = forceString(platrec.value('shortName'))
specialityNameCashe = {}
while row < rows:
dbfRecord = dbf[row]
nsvod = forceString(dbfRecord['NSVOD'])
reportData = reportDataByNumbers.setdefault(nsvod, {})
orgStructureCode = forceStringEx(dbfRecord['KODLPU'])
serviceCode = forceString(dbfRecord['KSPEC'])
if not specialityNameCashe.has_key(serviceCode):
rbSpecialityTable = QtGui.qApp.db.table('rbSpeciality')
specialityNameCashe[serviceCode] = forceString(
QtGui.qApp.db.translate(rbSpecialityTable,
rbSpecialityTable['federalCode'],
serviceCode,
rbSpecialityTable['OKSOName']))
specialityName = specialityNameCashe[serviceCode]
visitCount = 1
nscht = forceInt(dbfRecord['NSCHT'])
tariffPrice = forceDouble(dbfRecord['STOIM'])
sum = visitCount * tariffPrice
wages = forceDouble(dbfRecord['ZPL']) #1
additionalPayments = forceDouble(dbfRecord['DOPL']) #5
drugs = forceDouble(dbfRecord['MED']) #2
softInventory = forceDouble(dbfRecord['M_INV']) #3
overhead = forceDouble(dbfRecord['NAKL']) #4
orgStructureData = reportData.setdefault(serviceCode, {})
serviceData = orgStructureData.setdefault(
serviceCode, {
'orgStructureCode': orgStructureCode,
'serviceCode': serviceCode,
'specialityName': specialityName,
'visitCount': 0,
'eventCount': 0,
'sum': 0.0,
'wages': 0.0,
'additionalPayments': 0.0,
'drugs': 0.0,
'softInventory': 0.0,
'overhead': 0.0,
'nschtValueSet': []
})
serviceData['visitCount'] += visitCount
if nscht not in serviceData['nschtValueSet']:
serviceData['nschtValueSet'].append(nscht)
serviceData['eventCount'] += 1
serviceData['sum'] += sum
serviceData['wages'] += wages
serviceData['additionalPayments'] += additionalPayments
serviceData['drugs'] += drugs
serviceData['softInventory'] += softInventory
serviceData['overhead'] += overhead
row += 1
dbf.close()
return reportDataByNumbers, plat
def update(self):
isRollback = False
dbfFile = Dbf(os.path.join(self._dirName, self._fileName),
readOnly=True,
encoding='cp866')
try:
if not self._callbackUpdate(0, dbfFile.recordCount):
return
fileHash = forceString(
hashlib.md5(dbfFile.stream.read()).hexdigest())
if not self._rbTable or fileHash != forceString(self._metaData.value('hash')) or \
self._db.getCount(self._rbTable, countCol='id') != dbfFile.recordCount:
self._drop()
rbTableFields = [
trim(field) for field in forceString(
self._metaData.value('fields')).split(',')
]
fieldsStatement = ''
fieldsStatementCount = 0
for dbfField in dbfFile.fieldDefs:
if forceString(dbfField.name) in rbTableFields:
fieldsStatement += u'`%s` %s DEFAULT NULL COMMENT \'Поле %s\', ' % (
forceString(
dbfField.name), self._getType(dbfField),
forceString(dbfField.name))
fieldsStatementCount += 1
if len(rbTableFields) != fieldsStatementCount:
raise self.MetadataMappingError()
rbTableIndexes = [
index for index in map(
trim,
forceString(self._metaData.value('indexes')).split(
',')) if index
]
indexesStatement = ''
for index in rbTableIndexes:
indexesStatement += u', INDEX `%s` (`%s`)' % (index, index)
createStatement = u"""
CREATE TABLE `%s` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Отметка удаления записи',
%s
PRIMARY KEY (`id`)
%s
) ENGINE = InnoDB DEFAULT charset = utf8 COMMENT = '%s';""" % (
forceString(self._metaData.value('tableName')),
fieldsStatement, indexesStatement,
forceString(self._metaData.value('name')))
try:
self._db.query(createStatement)
self._db.transaction()
try:
insertRowCount = 0
self._rbTable = self._db.table(
forceString(self._metaData.value('tableName')))
for dbfRow in dbfFile:
newRecord = self._rbTable.newRecord()
for field in rbTableFields:
fieldValue = forceStringEx(dbfRow[field])
if fieldValue:
newRecord.setValue(field,
toVariant(fieldValue))
self._db.insertRecord(self._rbTable, newRecord)
insertRowCount += 1
if not self._callbackUpdate(
insertRowCount, dbfFile.recordCount):
isRollback = True
return
self._metaData.setValue('hash', toVariant(fileHash))
self._db.updateRecord(self._additionalRBs,
self._metaData)
self._db.commit()
except:
isRollback = True
raise
except Exception as e:
raise self.CreateError(e)
else:
self._callbackUpdate(dbfFile.recordCount, dbfFile.recordCount)
finally:
if isRollback:
self._db.rollback()
self._drop()
dbfFile.close()
class CImportMKB(QtGui.QDialog, Ui_ImportMKB):
def __init__(self, parent):
QtGui.QDialog.__init__(self, parent)
self.setupUi(self)
self.progressBar.setMinimum(0)
self.progressBar.setMaximum(1)
self.progressBar.setValue(0)
self.btnImport.setEnabled(False)
self.importDBF = None
def openFile(self, fileName):
archive, nameFiles = getFilesZipArchive(fileName)
if archive:
for name in nameFiles:
if not unicode(name.lower()).endswith('spr20.dbf'):
continue
self.importDBF = getDBFFileFromZipArchive(archive, name)
break
else:
if unicode(fileName.lower()).endswith('spr20.dbf'):
self.importDBF = Dbf(fileName, encoding='cp866')
if not self.importDBF:
self.logBrowser.append(u'В архиве нет spr20.dbf файла.' if archive
else u'Необходимо выбрать файл spr20.dbf.')
self.logBrowser.update()
return
self.btnImport.setEnabled(True)
def updateMKB(self, mapMKB):
db = QtGui.qApp.db
self.progressBar.setRange(0, len(mapMKB))
self.logBrowser.append(u'Конструирование запроса')
dateLeft = dateLeftInfinity.toString('yyyy-MM-dd')
dateRight = dateRightInfinity.toString('yyyy-MM-dd')
stmt = u'UPDATE MKB_Tree SET begDate=\'%s\', endDate=\'%s\', OMS=\'1\' WHERE parent_code IS NULL;\n' % (
dateLeft, dateRight)
for i, code in enumerate(mapMKB):
stmt += u'UPDATE MKB SET OMS=\'{OMS}\', begDate=\'{begDate}\', endDate=\'{endDate}\', DiagName=\'{name}\' WHERE DiagID=\'{code}\';\n' \
u'UPDATE MKB_Tree SET OMS=\'{OMS}\', begDate=\'{begDate}\', endDate=\'{endDate}\', DiagName=\'{name}\' WHERE DiagID=\'{code}\';\n'.format(
OMS=mapMKB[code][0],
begDate=mapMKB[code][1] or dateLeft,
endDate=mapMKB[code][2] or dateRight,
code=code,
name=mapMKB[code][3]
)
self.progressBar.setValue(i)
if not i % 30:
db.query(stmt)
stmt = ''
db.query(stmt)
def insertMKB(self, mapMKB):
self.progressBar.setRange(0, 0)
mapMKB = mapMKB.copy()
db = QtGui.qApp.db
self.logBrowser.append(u'Конструирование запроса')
tbl_tree = db.table('MKB_Tree')
view_tree = db.table('vMKBTree')
tbl = view_tree.leftJoin(tbl_tree,
view_tree['BlockID'].eq(tbl_tree['DiagID']))
blocks = dict((forceString(rec.value(1)),
forceString(rec.value(0)).strip('()').split('-'))
for rec in db.getRecordList(
tbl, 'DISTINCT vMKBTree.BlockID, MKB_Tree.id'))
exist = (forceString(rec.value(0))
for rec in db.getRecordList(tbl_tree, 'DiagID'))
for diag in exist:
if diag in mapMKB:
del mapMKB[diag]
def get_block(code):
for tree_id, mkb_range in blocks.iteritems():
if (len(mkb_range) == 1 and mkb_range[0]
== code) or (len(mkb_range) == 2
and mkb_range[0] <= code <= mkb_range[1]):
return tree_id
return None
values = []
dateLeft = dateLeftInfinity.toString('yyyy-MM-dd')
dateRight = dateRightInfinity.toString('yyyy-MM-dd')
self.progressBar.setRange(0, len(mapMKB))
for k, v in mapMKB.iteritems():
if len(k) == 3:
parent = get_block(k)
if not parent:
continue
elif len(k) == 5:
parent = k[:3]
elif len(k) == 6:
parent = k[:5]
else:
continue
values.append(
'(\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)'
% (
k, # DiagID
v[3], # DiagName
parent,
v[0], # OMS
v[1] or dateLeft, # begDate
v[2] or dateRight # endDate
))
self.progressBar.step()
if values:
stmt = u'INSERT INTO MKB_Tree (DiagID, DiagName, parent_code, OMS, begDate, endDate, Prim, sex, age, characters, duration, USL_OK1, USL_OK2, USL_OK3, USL_OK4, SELF) ' \
u'VALUES ' + ', '.join(values)
db.query(stmt)
@QtCore.pyqtSlot()
def on_btnImport_clicked(self):
db = QtGui.qApp.db
mapMKB = {}
self.progressBar.setMinimum(0)
self.progressBar.setMaximum(len(self.importDBF))
self.progressBar.setValue(0)
self.logBrowser.append(
u'Импорт начат: %s' %
(QtCore.QTime.currentTime().toString('hh:mm:ss')))
self.logBrowser.update()
insertValues = []
self.progressBar.setText(u'Считывание данных из файла')
for recordDBF in self.importDBF:
self.progressBar.step()
try:
code = forceString(recordDBF['CODE'])
mapMKB[code] = (forceString(recordDBF['IS_OMS']),
forceString(recordDBF['DATN']),
forceString(recordDBF['DATO']),
forceString(recordDBF['NAME']))
except KeyError:
self.logBrowser.append(
u'Неправильный формат файла\nИмпорт прерван: %s' %
(QtCore.QTime.currentTime().toString('hh:mm:ss')))
self.logBrowser.update()
self.importDBF.close()
return
self.importDBF.close()
db.transaction()
try:
self.progressBar.setText(u'Обновление записей')
self.updateMKB(mapMKB)
self.progressBar.setText(u'Вставка записей')
self.insertMKB(mapMKB)
self.logBrowser.append(
u'Импорт окончен: %s' %
(QtCore.QTime.currentTime().toString('hh:mm:ss')))
self.logBrowser.update()
db.commit()
except:
self.logBrowser.append(
u'Ошибка импорта: %s' %
(QtCore.QTime.currentTime().toString('hh:mm:ss')))
db.rollback()
QtGui.qApp.logCurrentException()
self.progressBar.setRange(0, 1)
self.progressBar.setValue(1)
@QtCore.pyqtSlot()
def on_btnSelectFile_clicked(self):
fileName = QtGui.QFileDialog.getOpenFileName(
self, u'Укажите файл с данными', self.edtFileName.text(),
u'Файлы DBF, ZIP (*.dbf *.zip)')
if fileName:
self.edtFileName.setText(QtCore.QDir.toNativeSeparators(fileName))
self.openFile(unicode(fileName))