def dbfFromZIP(self, zipFileName):
result = None
if is_zipfile(forceString(zipFileName)):
archive = ZipFile(forceString(zipFileName), "r")
names = archive.namelist()
fileName = ''
for fN in names:
if fN[-4:].lower() == '.dbf' and fN[0].upper() == 'P':
fileName = fN
if fileName:
tmpFile = QtCore.QTemporaryFile()
if not tmpFile.open(QtCore.QFile.WriteOnly):
self.log.append(u'Не удаётся открыть файл для записи %s:\n%s.' % (tmpFile, tmpFile.errorString()))
self.log.update()
data = archive.read(fileName) # read the binary data
tmpFile.write(data)
tmpFile.close()
fileInfo = QtCore.QFileInfo(tmpFile)
result = dbf.Dbf(forceString(fileInfo.filePath()), encoding='cp866')
else:
self.log.append(u'В архиве нет файла подходящего под маску P*.dbf')
self.log.update()
return result
def startImport(self):
self.ok = False
self.isUpdate = self.chkUpdate.isChecked()
dbfTovFileName = os.path.join(forceStringEx(self.edtFolderName.text()), 'sp_tov.dbf')
dbfMnnFileName = os.path.join(forceStringEx(self.edtFolderName.text()), 'Sp_mnn.DBF')
dbfLfFileName = os.path.join(forceStringEx(self.edtFolderName.text()), 'Sp_lf.dbf')
dbfTrnFileName = os.path.join(forceStringEx(self.edtFolderName.text()), 'Sp_trn.DBF')
dbfTov = dbf.Dbf(dbfTovFileName, readOnly=True, encoding='cp866')
dbfMnn = dbf.Dbf(dbfMnnFileName, readOnly=True, encoding='cp866')
dbfLf = dbf.Dbf(dbfLfFileName, readOnly=True, encoding='cp866')
dbfTrn = dbf.Dbf(dbfTrnFileName, readOnly=True, encoding='cp866')
self.progressBar.reset()
self.progressBar.setMaximum(len(dbfTov) - 1)
self.progressBar.setFormat(u'%v из %m (%p%)')
self.labelNum.setText(u'Всего записей в источнике: ' + str(len(dbfTov)))
self.log.append(u'Импорт начат ' + QDateTime().currentDateTime().toString('dd.MM.yyyy H:mm:ss'))
self.log.append(u'--------------------------------------- ')
self.log.append(u'Размер источника:')
self.log.append(u' - основная таблица: %d' % len(dbfTov))
self.log.append(u' - справочник МНН: %d' % len(dbfMnn))
self.log.append(u' - справочник форм выпуска: %d' % len(dbfLf))
self.log.append(u' - справочник торговых наименований: %d' % len(dbfTrn))
self.nSkipped = 0
self.nAdded = 0
self.nUpdated = 0
self.nProcessed = 0
self.process(dbfTov, dbfMnn, dbfLf, dbfTrn, self.processRow)
self.ok = not self.abort
dbfTov.close()
dbfMnn.close()
dbfLf.close()
dbfTrn.close()
self.log.append(u'--------------------------------------- ')
self.log.append(u'Импорт завершён ' + QDateTime().currentDateTime().toString('dd.MM.yyyy H:mm:ss'))
self.log.append(u'Добавлено: %d; изменено: %d' % (self.nAdded, self.nUpdated))
self.log.append(u'Пропущено: %d; обработано: %d' % (self.nSkipped, self.nProcessed))
self.log.append(u'Готово')
self.log.append(u' ')
self.log.append(u' ')
def on_btnSelectFolder_clicked(self):
folderName = QtGui.QFileDialog.getExistingDirectory(self, u'Укажите папку с данными')
if folderName != '':
self.edtFolderName.setText(QtCore.QDir.toNativeSeparators(folderName))
self.btnImport.setEnabled(True)
dbfTovFileName = os.path.join(forceStringEx(self.edtFolderName.text()), 'sp_tov.dbf')
dbfTov = dbf.Dbf(dbfTovFileName, readOnly=True, encoding='cp866')
self.labelNum.setText(u'Всего записей в источнике: ' + str(len(dbfTov)))
def on_btnSelectFile_clicked(self):
fileName = QtGui.QFileDialog.getOpenFileName(
self, u'Укажите файл с данными', self.edtFileName.text(),
u'Файлы DBF (*.dbf)')
if fileName != '':
self.edtFileName.setText(QDir.toNativeSeparators(fileName))
self.btnImport.setEnabled(True)
dbfFileName = forceStringEx(self.edtFileName.text())
dbfProfiles = dbf.Dbf(dbfFileName, readOnly=True, encoding='cp866')
self.labelNum.setText(u'всего записей в источнике: ' +
str(len(dbfProfiles)))
def on_btnLoadGTS_clicked(self, val):
#print 'asd'
try:
existsGts = []
dbfFileName = forceStringEx(self.edtFileName.text())
dbfProfiles = dbf.Dbf(dbfFileName, readOnly=True, encoding='cp866')
self.cmbGTS.clear()
for data in dbfProfiles:
gts = unicode(data['GTS'])
if not gts in existsGts:
self.cmbGTS.addItem(gts)
existsGts.append(gts)
except:
pass
def loadTypeFilter(self):
try:
dbfFileName = forceString(self.edtRbTypeFileName.text())
dbfType = dbf.Dbf(dbfFileName, readOnly=True, encoding='cp866')
self.log.append(u'всего записей в справочнике типов: ' +
str(len(dbfType)))
self.cmbType.clear()
self.mapType = {}
for row in dbfType:
self.cmbType.addItem(forceString(row['NAME']))
self.mapType[row['NAME']] = forceString(row['CODE'])
dbfType.close()
except:
self.cmbType.clear()
self.mapType = {}
self.chkType.setEnabled(False)
return
self.chkType.setEnabled(True)
def loadNetFilter(self):
try:
dbfFileName = forceString(self.edtRbNetFileName.text())
dbfNet = dbf.Dbf(dbfFileName, readOnly=True, encoding='cp866')
self.log.append(u'всего записей в справочнике сетей: ' +
str(len(dbfNet)))
self.cmbNet.clear()
self.mapNet = {}
for row in dbfNet:
self.cmbNet.addItem(forceString(row['NAME']))
self.mapNet[row['NAME']] = forceString(row['CODE'])
dbfNet.close()
except:
self.cmbNet.clear()
self.mapNet = {}
self.chkNet.setEnabled(False)
return
self.chkNet.setEnabled(True)
def process(self):
self.isTerminated = False
self.phaseReset(2)
queryTable, cols, cond, order, group = self.getQueryComponents()
self.nextPhaseStep(u'Загрузка данных')
recordList = self.db.getRecordList(queryTable,
cols,
where=cond,
group=group,
order=order)
self.nextPhaseStep(u'Загрузка данных завершена')
infis = forceStringEx(
QtGui.qApp.db.translate('OrgStructure', 'id', self.orgStructureId,
'bookkeeperCode'))
self.documents.clear()
counter = 0
self.nextPhase(len(recordList), u'Обработка данных')
for record in recordList:
smo = forceStringEx(record.value('SMO'))
if not self.documents.has_key(smo):
self.documents[smo] = dbf.Dbf(os.path.join(
QtGui.qApp.getTmpDir(), u'PRIK%s_%s.dbf' % (smo, infis)),
new=True,
encoding='cp866')
self.documents[smo].addField(*self.attachedFields)
dbfDoc = self.documents[smo]
dbfRow = dbfDoc.newRecord()
self.fillDbfRecord(dbfRow, record, self.attachedFields)
counter += 1
dbfRow['ID'] = counter
self.nextPhaseStep()
for doc in self.documents.values():
doc.close()
self.getLogger().info(u'Завершено')
if self.isTerminated:
self.onTerminating()
return False
return True
def startImport(self):
self.progressBar.setFormat('%p%')
n = 0
self.nAdded = 0
self.nFound = 0
self.filterGTS = forceStringEx(
self.cmbGTS.currentText()) if self.chkGTS.isChecked() else None
self.filterNet = self.mapNet.get(forceString(self.cmbNet.currentText())) \
if self.chkNet.isChecked() else None
self.filterType = self.mapType.get(forceString(self.cmbType.currentText())) \
if self.chkType.isChecked() else None
dbfFileName = forceStringEx(self.edtFileName.text())
dbfProfiles = dbf.Dbf(dbfFileName, readOnly=True, encoding='cp866')
dbfLen = len(dbfProfiles)
self.progressBar.setMaximum(dbfLen - 1)
self.labelNum.setText(u'всего записей в источнике: ' + str(dbfLen))
for row in dbfProfiles:
QtGui.qApp.processEvents()
if self.abort:
break
self.n = n + 1
self.processRow(row)
n += 1
self.progressBar.setValue(n)
statusText = u'добавлено %d, найдено %d' % (self.nAdded,
self.nFound)
self.statusLabel.setText(statusText)
self.log.append(u'добавлено %d, найдено %d' %
(self.nAdded, self.nFound))
self.log.append(u'готово')
def getCodeFromId(self, orgStructureSailId, fieldCode):
db = QtGui.qApp.db
if orgStructureSailId:
dbfFileName = unicode(self.edtFileNameOrgStructure.text())
if not dbfFileName:
res = QtGui.QMessageBox.warning(
self, u'Внимание!',
u'Выберите соответствующий ORGSTRUCTURE файл DBF %s' %
(self.edtFileNameOrgStructure.text()),
QtGui.QMessageBox.Cancel, QtGui.QMessageBox.Cancel)
else:
dbfSailOrgStructure = dbf.Dbf(dbfFileName,
readOnly=True,
encoding='cp1251')
for i, row in enumerate(dbfSailOrgStructure):
if orgStructureSailId == forceString(row['ID']):
orgStructureSailCode = forceString(row['CODE'])
record = db.getRecordEx(
'OrgStructure', 'id',
'deleted = 0 AND %s = \'%s\'' %
(fieldCode, orgStructureSailCode))
if record:
return forceRef(record.value('id'))
return None
def importPerson(self):
updateOrgStructure = self.chkUpdateOrgStructure.isChecked()
attachOrgStructure = self.chkAttachOrgStructure.isChecked()
orgStructureCodeType = self.cmbOrgStructureCodeType.currentIndex()
orgStructureIdType = self.cmbOrgstructureId.currentIndex()
specialityCodeType = self.cmbSpecialityCodeType.currentIndex()
postCodeType = self.cmbPostCodeType.currentIndex()
db = QtGui.qApp.db
tablePerson = db.table('Person')
db.transaction()
try:
if self.grpImportPerson.isChecked():
if not self.updateOS and (updateOrgStructure or attachOrgStructure):
res = QtGui.QMessageBox.warning( self,
u'Внимание!',
u'Необходимо сначала импортировать данные из файла %s'%(self.edtFileNameOrgStructure.text()),
QtGui.QMessageBox.Ok | QtGui.QMessageBox.Cancel,
QtGui.QMessageBox.Ok)
if res == QtGui.QMessageBox.Ok:
self.grpImportOrgStructure.setChecked(True)
self.importOrgStructure()
dbfFileName = unicode(self.edtFileNamePerson.text())
dbfSailPerson = dbf.Dbf(dbfFileName, readOnly=True, encoding='cp1251')
sailPersonFields = get_SailPerson_fields()
self.prbImport.setMaximum(len(dbfSailPerson)-1)
dbfFields = dbfSailPerson.fieldNames
for i, row in enumerate(dbfSailPerson):
QtGui.qApp.processEvents()
if self.abort: break
self.prbImport.setValue(self.n)
self.stat.setText(u'обработано: '+str(self.n))
self.n += 1
self.row = row
personFields=[('deleted', 0), ('retireDate', None)]
personFields2 = [('org_id', self.orgId)]
for fieldName in row.dbf.fieldNames:
if fieldName.lower() != 'id':
if fieldName.lower() == 'RETIREDATE'.lower():
retireDate = row[fieldName] if row[fieldName] else None
if retireDate:
retireDate = self.getDateFromUnicod(retireDate)
personFields2.append((fieldName, retireDate))
elif fieldName.lower() == 'BIRTHDATE'.lower():
birthDate = row[fieldName] if row[fieldName] else None
if birthDate:
birthDate = self.getDateFromUnicod(birthDate)
personFields2.append((fieldName, birthDate))
elif fieldName.lower() == 'sex':
sex = 0
if row[fieldName].lower() == u'М'.lower():
sex = 1
elif row[fieldName].lower() == u'Ж'.lower():
sex = 2
personFields2.append((fieldName, sex))
elif fieldName.lower() == 'post_id':
postId = None
postSailId = row[fieldName]
if postSailId:
if postCodeType:
postId = forceRef(QtGui.qApp.db.translate('rbPost', 'regionalCode', postSailId, 'id'))
else:
postId = forceRef(QtGui.qApp.db.translate('rbPost', 'code', postSailId, 'id'))
if not postId:
self.log.append(u'запись '+str(i)+' (' + str(fieldName) + ' = ' + str(row[fieldName]) +u'): '+ u' должность сотрудника не соответствует должности БД')
personFields2.append((fieldName, postId))
elif fieldName.lower() == 'speciality':
specialityId = None
specialityCode = forceString(row[fieldName])
if not specialityCode:
self.log.append(u'запись '+str(i)+' (' + str(fieldName) + ' = ' + str(row[fieldName]) +u'): '+ u'у сотрудника нет специальности')
else:
if specialityCodeType:
specialityId = forceRef(QtGui.qApp.db.translate('rbSpeciality', 'regionalCode', '%s'%(specialityCode), 'id'))
else:
specialityId = forceRef(QtGui.qApp.db.translate('rbSpeciality', 'OKSOCode', '%s%s'%(u'' if specialityCode.startswith('0') else u'0', specialityCode), 'id'))
if not specialityId:
self.log.append(u'запись '+str(i)+' (' + str(fieldName) + ' = ' + str(row[fieldName]) +u'): '+ u'специальность сотрудника не соответствует специальности БД')
personFields.append(('speciality_id', specialityId))
personFields2.append((fieldName, specialityId))
elif fieldName.lower() == 'code':
codePerson = forceString(row[fieldName])
if not codePerson:
self.log.append(u'запись '+str(i)+' (' + str(fieldName) + ' = ' + str(row[fieldName]) +u'): '+ u'нет кода сотрудника')
personFields.append(('code', codePerson))
personFields2.append((fieldName, codePerson))
elif fieldName.lower() == 'orgstructu':
orgStructureSail = forceString(row[fieldName])
orgStructureId = None
if orgStructureSail:
if orgStructureIdType:
record = db.getRecordEx(self.tableOrgStructure, 'id', 'deleted = 0 AND %s = %s'%('bookkeeperCode' if orgStructureCodeType else 'code', orgStructureSail))
orgStructureId = forceRef(record.value('id')) if record else None
else:
orgStructureId = self.getCodeFromId(orgStructureSail, 'bookkeeperCode' if orgStructureCodeType else 'code')
if not orgStructureId:
self.log.append(u'запись '+str(i)+' (' + str(fieldName) + ' = ' + str(row[fieldName]) +u'): '+ u'у сотрудника нет подразделения')
personFields.append(('orgStructure_id', orgStructureId))
personFields2.append((fieldName, orgStructureId))
else:
personFields2.append((fieldName, row[fieldName]))
personId = getId(tablePerson, personFields, personFields2)
db.commit()
except:
db.rollback()
QtGui.qApp.logCurrentException()
raise
def importOrgStructure(self):
self.updateOS = True
updateOrgStructure = self.chkUpdateOrgStructure.isChecked()
attachOrgStructure = self.chkAttachOrgStructure.isChecked()
orgStructureCodeType = self.cmbOrgStructureCodeType.currentIndex()
orgStructureIdType = self.cmbOrgstructureId.currentIndex()
db = QtGui.qApp.db
tableOrgStructure = db.table('OrgStructure')
db.transaction()
try:
if self.grpImportOrgStructure.isChecked() and (updateOrgStructure or attachOrgStructure):
dbfFileName = unicode(self.edtFileNameOrgStructure.text())
dbfSailOrgStructure = dbf.Dbf(dbfFileName, readOnly=True, encoding='cp1251')
sailOrgStructureFields = get_SailOrgStructure_fields()
self.prbImport.setMaximum(len(dbfSailOrgStructure)-1)
dbfFields = dbfSailOrgStructure.fieldNames
for row in dbfSailOrgStructure:
QtGui.qApp.processEvents()
if self.abort: break
self.prbImport.setValue(self.n)
self.stat.setText(u'обработано: '+str(self.n))
self.n += 1
self.row = row
if forceInt(row['DELETED']) == 0:
orgStructureFields = [('organisation_id', self.orgId)]
for fieldName in row.dbf.fieldNames:
if fieldName.lower() != 'id' and fieldName.lower() != 'parent_id' and fieldName.lower() != 'type' and fieldName.lower() != 'hashospita' and fieldName.lower() != 'code':
orgStructureFields.append((fieldName, row[fieldName]))
if self.chkTypeOrgStructure.isChecked():
orgStructureFields.append(('type', row['TYPE']))
if self.chkHasHospitalBeds.isChecked():
orgStructureFields.append(('hasHospitalBeds', row['HASHOSPITA']))
if orgStructureCodeType:
orgStructureId = self.getOrgStructureId('CODE', 'bookkeeperCode', orgStructureFields, updateOrgStructure, attachOrgStructure)
orgStructureFields.append(('bookkeeperCode', row['CODE']))
else:
orgStructureId = self.getOrgStructureId('CODE', 'code', orgStructureFields, updateOrgStructure, attachOrgStructure)
orgStructureFields.append(('code', row['CODE']))
if orgStructureId:
orgStructureSailId = forceRef(row['CODE'])
for i, row in enumerate(dbfSailOrgStructure):
orgStructureSailCode = forceString(row['CODE'])
if forceInt(row['DELETED']) == 0 and orgStructureSailCode:
if orgStructureCodeType:
fieldCodeType = 'bookkeeperCode'
else:
fieldCodeType = 'code'
parentCode = forceString(row['PARENT_ID'])
if parentCode:
if orgStructureIdType:
recordParentId = db.getRecordEx(tableOrgStructure, 'id', 'deleted = 0 AND %s = %s'%(fieldCodeType, parentCode))
parentId = forceRef(recordParentId.value('id')) if recordParentId else None
else:
parentId = self.getCodeFromId(parentCode, fieldCodeType)
if parentId:
record = db.getRecordEx(tableOrgStructure, '*', '%s = \'%s\''%(fieldCodeType, orgStructureSailCode))
if record:
record.setValue('parent_id', toVariant(parentId))
orgStructureId = db.updateRecord(tableOrgStructure, record)
if not orgStructureId:
if self.log:
self.log.append(u'запись '+str(i)+' (id=\"' + str(row['ID']) +u'\"): '+ u' PARENT_ID подразделения не соответствует ID подразделениям БД')
db.commit()
except:
db.rollback()
QtGui.qApp.logCurrentException()
raise
def process(self):
self.isTerminated = False
self.phaseReset(2)
db = QtGui.qApp.db
tableClient = db.table('Client')
tableDiscountDocument = db.table('ClientDocument').alias(
'DiscountDocument')
tableDiscountDocType = db.table('rbDocumentType').alias(
'DiscountDocType')
tableSocStatus = db.table('ClientSocStatus')
tableSocStatusType = db.table('rbSocStatusType')
queryTable, cols, cond, order, group = self.getQueryComponents()
queryTable = queryTable.leftJoin(tableSocStatus, [
tableSocStatus['client_id'].eq(
tableClient['id']), tableSocStatus['deleted'].eq(0),
'''ClientSocStatus.socStatusClass_id =
(SELECT ssc.id FROM rbSocStatusClass ssc
WHERE ssc.flatCode = 'benefits' LIMIT 1)'''
])
queryTable = queryTable.leftJoin(
tableSocStatusType,
tableSocStatusType['id'].eq(tableSocStatus['socStatusType_id']))
queryTable = queryTable.leftJoin(
tableDiscountDocument,
tableDiscountDocument['id'].eq(tableSocStatus['document_id']))
queryTable = queryTable.leftJoin(
tableDiscountDocType, tableDiscountDocType['id'].eq(
tableDiscountDocument['documentType_id']))
cols += [
tableSocStatusType['code'].alias('LGOTA'),
'CONCAT_WS(\' \', DiscountDocType.name, DiscountDocument.serial, DiscountDocument.number) AS DOCU',
tableSocStatus['begDate'].alias('POLU_D'),
tableSocStatus['endDate'].alias('ORON_D')
]
cond += [
tableSocStatusType['flatCode'].inlist([
'010', '011', '012', '020', '030', '040', '050', '060', '061',
'062', '063', '064', '081', '082', '083', '084', '085', '091',
'092', '093', '094', '095', '096', '097', '098', '099', '100',
'101', '102', '111', '112', '113', '120', '121', '122', '123',
'124', '125', '128', '129', '131', '132', '140', '141', '142',
'150', '201', '207', '506', '701'
])
]
group += [tableSocStatus['id']]
self.nextPhaseStep(u'Загрузка данных')
recordList = self.db.getRecordList(queryTable,
cols,
where=cond,
group=group,
order=order)
self.nextPhaseStep(u'Загрузка данных завершена')
infis = forceStringEx(
QtGui.qApp.db.translate('OrgStructure', 'id', self.orgStructureId,
'bookkeeperCode'))
self.documents.clear()
self.documents['PRIK'] = dbf.Dbf(os.path.join(QtGui.qApp.getTmpDir(),
u'PRIK%s.dbf' % infis),
new=True,
encoding='cp866')
self.documents['PRIK'].addField(*self.attachedFields)
self.documents['LGOTA'] = dbf.Dbf(os.path.join(QtGui.qApp.getTmpDir(),
u'LGOTA%s.dbf' % infis),
new=True,
encoding='cp866')
self.documents['LGOTA'].addField(*self.discountFields)
clientIdList = []
counter = 0
self.nextPhase(len(recordList), u'Обработка данных')
for record in recordList:
clientId = forceInt(record.value('IDSTR'))
if not clientId in clientIdList:
rec = self.documents['PRIK'].newRecord()
counter += 1
self.fillDbfRecord(rec, record, self.attachedFields)
rec['ID'] = counter
rec.store()
clientIdList.append(clientId)
if forceStringEx(record.value('LGOTA')):
rec_lgota = self.documents['LGOTA'].newRecord()
self.fillDbfRecord(rec_lgota, record, self.discountFields)
rec_lgota['ID'] = counter
self.nextPhaseStep()
for doc in self.documents.values():
doc.close()
self.getLogger().info(u'Завершено')
if self.isTerminated:
self.onTerminating()
return False
return True
