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