def isDestinationInFormulary(self, drugFormularyItemId): if QtGui.qApp.currentOrgStructureId(): orgStructureIdList = getOrgStructureDescendants( QtGui.qApp.currentOrgStructureId()) else: orgStructureIdList = getOrgStructures(QtGui.qApp.currentOrgId()) if len(orgStructureIdList) > 0: stmt = u''' SELECT DrugFormulary.id FROM DrugFormulary_Item INNER JOIN DrugFormulary ON DrugFormulary.id = DrugFormulary_Item.master_id AND DrugFormulary.orgStructure_id IN (%(orgStructures)s) WHERE DrugFormulary_Item.id = %(drugFormularyItemId)s ''' % { 'drugFormularyItemId': drugFormularyItemId, 'orgStructures': ', '.join( forceString(orgStructure) for orgStructure in orgStructureIdList) } query = QtGui.qApp.db.query(stmt) if query.first(): return True return False
def getData(areaId, endDate, addressType, isDetailByAge=False): if areaId: areaIdList = getOrgStructureDescendants(areaId) else: areaIdList = getOrgStructures(QtGui.qApp.currentOrgId()) query = selectData(endDate, areaIdList, addressType) reportData = [[0] * reportRowSize[0] for row in xrange(3)] self.setQueryText(forceString(query.lastQuery())) while query.next(): record = query.record() cnt = forceInt(record.value('cnt')) age = forceInt(record.value('clientAge')) sex = forceInt(record.value('clientSex')) busy = forceBool(record.value('busy')) if age < 1: colBase = 0 elif age == 1: colBase = 2 elif age <= 6: colBase = 4 elif age <= 14: colBase = 6 elif age <= 17: colBase = 8 else: if isDetailByAge: minAgeHigh = 150 colBase = 36 for ageHigh in employableColBases.keys(): if age <= ageHigh and ageHigh < minAgeHigh: minAgeHigh = ageHigh colBase = employableColBases[ageHigh] else: if (sex == 1 and age < 60) or (sex != 1 and age < 55): colBase = 10 else: colBase = 12 cols = [colBase + (0 if sex == 1 else 1), reportRowSize[0] - 1] rows = [0, 1 if busy else 2] if cols[0] < 0 or cols[0] > reportRowSize[0] - 1: print cols[0] for row in rows: for col in cols: reportData[row][col] += cnt return reportData
def __init__(self, parent = None, filter = ''): CDialogBase.__init__(self, parent) self.curDrugsId = [] self.tradeNameCodesList = [] self.filter = filter self.drugFormularyItemIdList = [] self.orgStructureIdList = [] self.orgStructureIdWhere = '' self.mnn = '%' self.issueForm = '%' self.tradeName = '%' self.searchMNN ='%' self.searchIssueForm = '%' self.searchTradeName = '%' self.addModels('MNN', CMNNTableModel(self)) self.addModels('IssueForm', CIssueFormTableModel(self)) self.addModels('TradeName', CTradeNamesTableModel(self)) self.addModels('List', CListTableModel(self)) self.addModels('OrgStructureStock', COrgStructureStock(self)) self.setupUi(self) self.setModels(self.tblMNN, self.modelMNN, self.selectionModelMNN) self.tblMNN.horizontalHeader().setStretchLastSection(True) self.tblMNN.setSelectionMode(QtGui.QAbstractItemView.SingleSelection) self.setModels(self.tblIssueForm, self.modelIssueForm, self.selectionModelIssueForm) self.tblIssueForm.horizontalHeader().setStretchLastSection(True) self.tblIssueForm.setSelectionMode(QtGui.QAbstractItemView.SingleSelection) self.tblIssueForm.setSelectionBehavior(QtGui.QTableView.SelectRows) self.setModels(self.tblTradeName, self.modelTradeName, self.selectionModelTradeName) self.tblTradeName.horizontalHeader().setStretchLastSection(True) self.tblTradeName.setSelectionMode(QtGui.QAbstractItemView.SingleSelection) self.tblSelectedDrugs.setModel(self.modelList) self.tblSelectedDrugs.horizontalHeader().setStretchLastSection(True) self.chkCurrentFormulary.setChecked(True) self.prbLoad.setVisible(False) if QtGui.qApp.currentOrgStructureId(): self.orgStructureIdList = getOrgStructureDescendants(QtGui.qApp.currentOrgStructureId()) else: self.orgStructureIdList = getOrgStructures(QtGui.qApp.currentOrgId()) if len(self.orgStructureIdList) > 0: self.orgStructureIdWhere = u'''AND DrugFormulary.orgStructure_id IN (%(orgStructures)s) ''' % {'orgStructures': ', '.join(forceString(orgStructure) for orgStructure in self.orgStructureIdList)} self.tableMNNRebuild()
def selectData(endDate, ageFrom, ageTo, areaId, addressType, socStatusTypeId, socStatusClassId): stmt = """ SELECT COUNT(Client.id) as cnt, %(orgStructField)s AS repOrgStructure_id, age(Client.birthDate, %(attachCheckDate)s) AS clientAge, Client.sex AS clientSex, IF(ClientAttach.id IS NULL,0,1) AS attached, IF(ClientIdentification.checkDate IS NULL,0,1) AS confirmed FROM Client %(joins)s LEFT JOIN ClientAttach ON ClientAttach.client_id = Client.id AND ClientAttach.id = (SELECT MAX(CAT.id) FROM ClientAttach AS CAT LEFT JOIN rbAttachType ON rbAttachType.id=CAT.attachType_id WHERE CAT.deleted=0 AND CAT.client_id=Client.id # AND rbAttachType.temporary=0 AND CAT.begDate<=%(attachCheckDate)s AND (CAT.endDate IS NULL or CAT.endDate>=%(attachCheckDate)s) ) AND %(attachToArea)s LEFT JOIN rbAttachType ON rbAttachType.id=ClientAttach.attachType_id LEFT JOIN ClientIdentification ON ClientIdentification.client_id AND ClientIdentification.id = ( SELECT MAX(CI.id) FROM ClientIdentification AS CI LEFT JOIN rbAccountingSystem ON rbAccountingSystem.id = CI.accountingSystem_id WHERE rbAccountingSystem.code in (\'1\', \'2\') AND CI.client_id = Client.id) WHERE %(mainCond)s GROUP BY repOrgStructure_id, clientAge, clientSex, attached, confirmed """ db = QtGui.qApp.db tableClient = db.table('Client') tableOrgStructureAddress = db.table('OrgStructure_Address') tableAddress = db.table('Address') tableClientAttach = db.table('ClientAttach') tableAttachType = db.table('rbAttachType') cond = [] cond.append(tableClient['deleted'].eq(0)) cond.append( db.joinOr([ tableAttachType['outcome'].eq(0), tableAttachType['outcome'].isNull() ])) # cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId())) # if eventTypeIdList: # cond.append(tableEvent['eventType_id'].inlist(eventTypeIdList)) if ageFrom <= ageTo: # для проверки логики: # если взять ageFrom == ageTo == 0 то # должны получиться дети родившиеся за последний год, # а годовалые уже не подходят cond.append(tableClient['birthDate'].gt(endDate.addYears(-ageTo - 1))) cond.append(tableClient['birthDate'].le(endDate.addYears(-ageFrom))) # if areaId: # cond.append(tableOrgStructureAddress['master_id'].inlist(getOrgStructureDescendants(areaId))) if areaId: areaIdList = getOrgStructureDescendants(areaId) else: areaIdList = getOrgStructures(QtGui.qApp.currentOrgId()) reg = (addressType + 1) & 1 loc = (addressType + 1) & 2 attach = (addressType + 1) & 4 condAddr = [] joins = '' if reg: tableOrgStructureAddress = db.table('OrgStructure_Address').alias( 'OSAReg') condAddr.append( tableOrgStructureAddress['master_id'].inlist(areaIdList)) joins += ''' LEFT JOIN ClientAddress AS CAReg ON CAReg.client_id = Client.id AND CAReg.id = (SELECT MAX(CARegInt.id) FROM ClientAddress AS CARegInt WHERE CARegInt.type=0 AND CARegInt.client_id=Client.id) LEFT JOIN Address AS AReg ON AReg.id = CAReg.address_id LEFT JOIN OrgStructure_Address AS OSAReg ON OSAReg.house_id = AReg.house_id AND %s ''' % tableOrgStructureAddress['master_id'].inlist(areaIdList) if loc: tableOrgStructureAddress = db.table('OrgStructure_Address').alias( 'OSALoc') condAddr.append( tableOrgStructureAddress['master_id'].inlist(areaIdList)) joins += ''' LEFT JOIN ClientAddress AS CALoc ON CALoc.client_id = Client.id AND CALoc.id = (SELECT MAX(CALocInt.id) FROM ClientAddress AS CALocInt WHERE CALocInt.type=1 AND CALocInt.client_id=Client.id) LEFT JOIN Address AS ALoc ON ALoc.id = CALoc.address_id LEFT JOIN OrgStructure_Address AS OSALoc ON OSALoc.house_id = ALoc.house_id AND %s ''' % tableOrgStructureAddress['master_id'].inlist(areaIdList) if attach: condAddr.append( db.joinAnd([ tableClientAttach['orgStructure_id'].inlist(areaIdList), tableClientAttach['deleted'].eq(0) ])) if condAddr: cond.append(db.joinOr(condAddr)) if attach: if loc: if reg: orgStructField = 'IF(ClientAttach.orgStructure_id IS NOT NULL, ClientAttach.orgStructure_id, IF(OSALoc.master_id IS NOT NULL, OSALoc.master_id, OSAReg.master_id))' else: orgStructField = 'IF(ClientAttach.orgStructure_id IS NOT NULL, ClientAttach.orgStructure_id, OSALoc.master_id)' else: if reg: orgStructField = 'IF(ClientAttach.orgStructure_id IS NOT NULL, ClientAttach.orgStructure_id, OSAReg.master_id)' else: orgStructField = 'ClientAttach.orgStructure_id' else: if loc: if reg: orgStructField = 'IF(OSALoc.master_id IS NOT NULL, OSALoc.master_id, OSAReg.master_id)' else: orgStructField = 'OSALoc.master_id' else: if reg: orgStructField = 'OSAReg.master_id' else: orgStructField = 'NULL' if socStatusTypeId: subStmt = ( 'SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' + 'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND ' + 'ClientSocStatus.socStatusType_id=%d' % socStatusTypeId) cond.append('EXISTS(' + subStmt + ')') elif socStatusClassId: subStmt = ( 'SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' + 'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND ' + 'ClientSocStatus.socStatusClass_id=%d' % socStatusClassId) cond.append('EXISTS(' + subStmt + ')') return db.query( stmt % { 'orgStructField': orgStructField, 'attachCheckDate': tableClient['birthDate'].formatValue(endDate), 'attachToArea': tableClientAttach['orgStructure_id'].inlist(areaIdList), 'joins': joins, 'mainCond': db.joinAnd(cond) })
def selectResultData(begDate, endDate, workOrgId, sex, ageFrom, ageTo, areaIdEnabled, areaId, MKBFilter, MKBFrom, MKBTo, MKBExFilter, MKBExFrom, MKBExTo, personId, forChildren, orgStructureAttachTypeId): stmt = u""" SELECT COUNT(Diagnosis.id) AS count, COUNT(if(%s, Diagnosis.id, NULL)) AS countInFirst FROM Diagnosis LEFT JOIN rbDispanser ON rbDispanser.id = Diagnosis.dispanser_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnosis.character_id LEFT JOIN Client ON Client.id = Diagnosis.client_id LEFT JOIN ClientAddress AS ClientAddress0 ON ClientAddress0.client_id = Diagnosis.client_id AND ClientAddress0.id = (SELECT MAX(id) FROM ClientAddress AS CA0 WHERE CA0.Type=0 and CA0.client_id = Diagnosis.client_id) LEFT JOIN Address ON Address.id = ClientAddress0.address_id %s WHERE %s """ db = QtGui.qApp.db tableDiagnosis = db.table('Diagnosis') tableClient = db.table('Client') tableClientDispanser = db.table('rbDispanser') cond = [] cond.append(tableDiagnosis['deleted'].eq(0)) cond.append(tableDiagnosis['mod_id'].isNull()) cond.append(tableClientDispanser['observed'].eq(1)) addDateInRange(cond, tableDiagnosis['endDate'], begDate, endDate) if personId: cond.append(tableDiagnosis['person_id'].eq(personId)) if MKBFilter == 1: cond.append(tableDiagnosis['MKB'].ge(MKBFrom)) cond.append(tableDiagnosis['MKB'].le(MKBTo)) if MKBExFilter == 1: cond.append(tableDiagnosis['MKBEx'].ge(MKBExFrom)) cond.append(tableDiagnosis['MKBEx'].le(MKBExTo)) if workOrgId: tableClientWork = db.table('ClientWork') cond.append(tableClientWork['org_id'].eq(workOrgId)) if sex: cond.append(tableClient['sex'].eq(sex)) if areaIdEnabled: if areaId: orgStructureIdList = getOrgStructureDescendants(areaId) else: orgStructureIdList = getOrgStructures(QtGui.qApp.currentOrgId()) tableOrgStructureAddress = db.table('OrgStructure_Address') tableAddress = db.table('Address') subCond = [ tableOrgStructureAddress['master_id'].inlist(orgStructureIdList), tableOrgStructureAddress['house_id'].eq(tableAddress['house_id']), ] cond.append(db.existsStmt(tableOrgStructureAddress, subCond)) # date = endDate if endDate else QDate.currentDate() # firstDay = firstYearDay(date) # lastDay = lastYearDay(date) # dateCond = [] # addDateInRange(dateCond, tableDiagnosis['endDate'], firstDay, lastDay) addisionalFrom = '' if forChildren: cond.append('age(Client.birthDate, Diagnosis.endDate) <= 17') if orgStructureAttachTypeId: tableClientAttach = db.table('ClientAttach') attachTypeId = forceRef( db.translate('rbAttachType', 'code', u'1', 'id')) addisionalFrom = '''LEFT JOIN ClientAttach ON ClientAttach.client_id = Client.id AND ClientAttach.id = (SELECT max(clAttach.id) FROM ClientAttach clAttach WHERE clAttach.attachType_id = %s AND clAttach.client_id = Client.id) LEFT JOIN OrgStructure ON OrgStructure.id = ClientAttach.orgStructure_id''' % ( attachTypeId) orgStructureIdList = db.getDescendants('OrgStructure', 'parent_id', orgStructureAttachTypeId) cond.append(tableClientAttach['orgStructure_id'].inlist( orgStructureIdList)) return db.query(stmt % (db.joinAnd([ tableDiagnosis['setDate'].le(endDate), tableDiagnosis['setDate'].ge(begDate) ]), addisionalFrom, db.joinAnd(cond)))
def selectData(begDate, endDate, workOrgId, sex, ageFrom, ageTo, areaIdEnabled, areaId, MKBFilter, MKBFrom, MKBTo, MKBExFilter, MKBExFrom, MKBExTo, personId, specialityId, rowGrouping, forChildren, orgStructureAttachTypeId): if rowGrouping == 1: # by orgStructureId groupField = 'Person.orgStructure_id' elif rowGrouping == 2: # by speciality_id groupField = 'Person.speciality_id' elif rowGrouping == 3: # by personId groupField = 'Diagnosis.person_id' else: groupField = 'concat(NULL)' stmt = u""" SELECT DISTINCT %s as rowKey, Client.id, Client.lastName, Client.firstName, Client.patrName, Client.sex, Client.birthDate, Client.SNILS, formatClientAddress(ClientAddress0.id) AS regAddress, formatClientAddress(ClientAddress1.id) AS locAddress, ClientPolicy.serial AS policySerial, ClientPolicy.number AS policyNumber, formatInsurerName(ClientPolicy.insurer_id) as insurer, ClientDocument.serial AS documentSerial, ClientDocument.number AS documentNumber, rbDocumentType.code AS documentType, IF(ClientWork.org_id IS NULL, ClientWork.freeInput, Organisation.shortName) AS workName, ClientWork.post AS workPost, ClientContact.contact AS contact, vrbSocStatusType.name AS socStatus, if(css.id IS NOT NULL, css.begDate, diag.endDate) AS dateAccount, Event.setDate AS dateView, concat(Person.lastName, ' ', left(Person.firstName, 1), '.', left(Person.patrName, 1), '.') AS person, Diagnosis.MKB AS MKB, rbDispanser.name AS firstInPeriod FROM Diagnosis JOIN rbDispanser ON rbDispanser.id = Diagnosis.dispanser_id JOIN Client ON Client.id = Diagnosis.client_id LEFT JOIN ClientAddress AS ClientAddress0 ON ClientAddress0.client_id = Diagnosis.client_id AND ClientAddress0.id = (SELECT MAX(id) FROM ClientAddress AS CA0 WHERE CA0.Type=0 and CA0.client_id = Diagnosis.client_id) LEFT JOIN Address ON Address.id = ClientAddress0.address_id LEFT JOIN ClientAddress AS ClientAddress1 ON ClientAddress1.client_id = Diagnosis.client_id AND ClientAddress1.id = (SELECT MAX(id) FROM ClientAddress AS CA1 WHERE CA1.Type=1 and CA1.client_id = Diagnosis.client_id) LEFT JOIN ClientPolicy ON ClientPolicy.client_id = Client.id AND ClientPolicy.id = (SELECT MAX(CP.id) FROM ClientPolicy AS CP LEFT JOIN rbPolicyType ON rbPolicyType.id = CP.policyType_id WHERE CP.client_id = Client.id AND rbPolicyType.name LIKE 'ОМС%%' ) LEFT JOIN ClientDocument ON ClientDocument.client_id = Client.id AND ClientDocument.id = (SELECT MAX(CD.id) FROM ClientDocument AS CD LEFT JOIN rbDocumentType AS rbDT ON rbDT.id = CD.documentType_id LEFT JOIN rbDocumentTypeGroup AS rbDTG ON rbDTG.id = rbDT.group_id WHERE rbDTG.code = '1' AND CD.client_id = Client.id) LEFT JOIN ClientContact ON (ClientContact.client_id = Client.id AND ClientContact.contactType_id=1 AND ClientContact.deleted=0) LEFT JOIN rbDocumentType ON rbDocumentType.id = ClientDocument.documentType_id LEFT JOIN ClientWork ON ClientWork.client_id = Client.id AND ClientWork.id = (SELECT MAX(CW.id) FROM ClientWork AS CW WHERE CW.deleted=0 AND CW.client_id=Client.id) LEFT JOIN Organisation ON Organisation.id = ClientWork.org_id LEFT JOIN ClientSocStatus ON ClientSocStatus.client_id AND ClientSocStatus.id = (SELECT MAX(ClientSocStatus.id) FROM ClientSocStatus JOIN vrbSocStatusType ON vrbSocStatusType.id=ClientSocStatus.socStatusType_id AND vrbSocStatusType.class_id = 1 WHERE ClientSocStatus.deleted = 0 AND ClientSocStatus.client_id = Client.id) LEFT JOIN vrbSocStatusType ON ClientSocStatus.socStatusType_id = vrbSocStatusType.id AND vrbSocStatusType.class_id = 1 LEFT JOIN ClientSocStatus css ON css.client_id AND css.id = (SELECT MAX(ClientSocStatus.id) FROM ClientSocStatus JOIN rbSocStatusClass ON ClientSocStatus.socStatusClass_id = rbSocStatusClass.id AND rbSocStatusClass.code = '01' JOIN rbSocStatusType ON ClientSocStatus.socStatusType_id = rbSocStatusType.id AND rbSocStatusType.code = '2' WHERE ClientSocStatus.deleted = '0' AND ClientSocStatus.client_id = Client.id) LEFT JOIN Diagnosis diag ON diag.id = Diagnosis.id AND diag.dispanser_id = (SELECT disp.id FROM rbDispanser disp WHERE disp.code = '2') %s LEFT JOIN Person ON Person.id = Event.execPerson_id LEFT JOIN vrbPerson ON Person.code = vrbPerson.code WHERE %s ORDER BY rowKey, Client.lastName, Client.firstName, Client.patrName, Client.birthDate, Client.id """ db = QtGui.qApp.db tableDiagnosis = db.table('Diagnosis') tableClient = db.table('Client') tableClientDispanser = db.table('rbDispanser') tablePerson = db.table('Person') cond = [] cond.append(tableDiagnosis['deleted'].eq(0)) cond.append(tableDiagnosis['mod_id'].isNull()) cond.append(tableClientDispanser['observed'].eq(1)) addDateInRange(cond, tableDiagnosis['endDate'], begDate, endDate) if MKBFilter == 1: cond.append(tableDiagnosis['MKB'].ge(MKBFrom)) cond.append(tableDiagnosis['MKB'].le(MKBTo)) if MKBExFilter == 1: cond.append(tableDiagnosis['MKBEx'].ge(MKBExFrom)) cond.append(tableDiagnosis['MKBEx'].le(MKBExTo)) if workOrgId: tableClientWork = db.table('ClientWork') cond.append(tableClientWork['org_id'].eq(workOrgId)) if sex: cond.append(tableClient['sex'].eq(sex)) if not forChildren: if ageFrom <= ageTo: cond.append( 'Diagnosis.endDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' % ageFrom) cond.append( 'Diagnosis.endDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)' % (ageTo + 1)) if areaIdEnabled: if areaId: orgStructureIdList = getOrgStructureDescendants(areaId) else: orgStructureIdList = getOrgStructures(QtGui.qApp.currentOrgId()) tableOrgStructureAddress = db.table('OrgStructure_Address') tableAddress = db.table('Address') subCond = [ tableOrgStructureAddress['master_id'].inlist(orgStructureIdList), tableOrgStructureAddress['house_id'].eq(tableAddress['house_id']), ] cond.append(db.existsStmt(tableOrgStructureAddress, subCond)) if personId: cond.append(tableDiagnosis['person_id'].eq(personId)) if specialityId: cond.append(tablePerson['speciality_id'].eq(specialityId)) if forChildren: additionalFrom = '''LEFT JOIN Event ON Event.client_id = Client.id AND Event.id = (SELECT max(ev.id) FROM Event ev INNER JOIN Diagnostic ON ev.id = Diagnostic.event_id AND Diagnostic.deleted = 0 WHERE Diagnostic.diagnosis_id = Diagnosis.id AND ev.deleted = 0)''' cond.append('age(Client.birthDate, Event.setDate) <= 17') if orgStructureAttachTypeId: tableClientAttach = db.table('ClientAttach') attachTypeId = forceRef( db.translate('rbAttachType', 'code', u'1', 'id')) additionalFrom += ''' LEFT JOIN ClientAttach ON ClientAttach.client_id = Client.id AND ClientAttach.id = (SELECT max(clAttach.id) FROM ClientAttach clAttach WHERE clAttach.attachType_id = %s AND clAttach.client_id = Client.id) LEFT JOIN OrgStructure ON OrgStructure.id = ClientAttach.orgStructure_id''' % ( attachTypeId) orgStructureIdList = db.getDescendants('OrgStructure', 'parent_id', orgStructureAttachTypeId) cond.append(tableClientAttach['orgStructure_id'].inlist( orgStructureIdList)) else: additionalFrom = '''LEFT JOIN Event ON Event.client_id AND Event.id = (SELECT MAX(Event.id) FROM Event LEFT JOIN EventType ON EventType.id = Event.eventType_id AND EventType.code = '03' WHERE Event.deleted = '0' AND Event.client_id = Client.id)''' cond.append( ' Event.eventType_id NOT IN (SELECT et.id FROM EventType et WHERE et.name LIKE \'%Запись на прием%\') ' ) return db.query(stmt % (groupField, additionalFrom, (db.joinAnd(cond))))
def selectData(registeredInPeriod, begDate, endDate, eventPurposeIdList, eventTypeId, orgStructureId, personId, sex, ageFrom, ageTo, socStatusClassId, socStatusTypeId, areaIdEnabled, areaId, locality): stmt=""" SELECT Diagnosis.MKB AS MKB, COUNT(*) AS sickCount, (%s) AS firstInPeriod, IF((SELECT MAX(rbDispanser.observed) FROM Diagnostic AS D1 LEFT JOIN rbDispanser ON rbDispanser.id = D1.dispanser_id WHERE D1.diagnosis_id = Diagnosis.id AND D1.endDate = ( SELECT MAX(D2.endDate) FROM Diagnostic AS D2 WHERE D2.diagnosis_id = Diagnosis.id AND D2.dispanser_id IS NOT NULL AND D2.endDate<%s)) = 1, 1, 0) AS observed FROM Diagnosis LEFT JOIN Client ON Client.id = Diagnosis.client_id LEFT JOIN ClientAddress ON ClientAddress.client_id = Diagnosis.client_id AND ClientAddress.id = (SELECT MAX(id) FROM ClientAddress AS CA WHERE CA.Type=1 and CA.client_id = Diagnosis.client_id) LEFT JOIN Address ON Address.id = ClientAddress.address_id LEFT JOIN rbDiagnosisType ON rbDiagnosisType.id = Diagnosis.diagnosisType_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnosis.character_id WHERE %s GROUP BY MKB, firstInPeriod, observed """ db = QtGui.qApp.db tableDiagnosis = db.table('Diagnosis') tableDiseaseCharacter = db.table('rbDiseaseCharacter') tableClient = db.table('Client') tableDiagnostic = db.table('Diagnostic') tablePerson = db.table('Person') cond = [] cond.append(tableDiagnosis['deleted'].eq(0)) cond.append(tableDiagnosis['mod_id'].isNull()) cond.append(db.joinOr([tableDiagnosis['setDate'].isNull(), tableDiagnosis['setDate'].lt(endDate.addDays(1))])) cond.append(db.joinOr([tableDiagnosis['endDate'].ge(begDate), tableDiseaseCharacter['code'].eq('3')])) diagnosticQuery = tableDiagnostic diagnosticCond = [ tableDiagnostic['diagnosis_id'].eq(tableDiagnosis['id']), tableDiagnostic['deleted'].eq(0) ] if registeredInPeriod: addDateInRange(diagnosticCond, tableDiagnostic['setDate'], begDate, endDate) if personId: diagnosticCond.append(tableDiagnostic['person_id'].eq(personId)) elif orgStructureId: diagnosticQuery = diagnosticQuery.leftJoin(tablePerson, tablePerson['id'].eq(tableDiagnostic['person_id'])) diagnosticCond.append(tablePerson['orgStructure_id'].inlist(getOrgStructureDescendants(orgStructureId))) else: diagnosticQuery = diagnosticQuery.leftJoin(tablePerson, tablePerson['id'].eq(tableDiagnostic['person_id'])) diagnosticCond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId())) if eventTypeId: tableEvent = db.table('Event') diagnosticQuery = diagnosticQuery.leftJoin(tableEvent, tableEvent['id'].eq(tableDiagnostic['event_id'])) diagnosticCond.append(tableEvent['eventType_id'].eq(eventTypeId)) elif eventPurposeIdList: tableEvent = db.table('Event') tableEventType = db.table('EventType') diagnosticQuery = diagnosticQuery.leftJoin(tableEvent, tableEvent['id'].eq(tableDiagnostic['event_id'])) diagnosticQuery = diagnosticQuery.leftJoin(tableEventType, tableEventType['id'].eq(tableEvent['eventType_id'])) diagnosticCond.append(tableEventType['purpose_id'].inlist(eventPurposeIdList)) cond.append(db.existsStmt(diagnosticQuery, diagnosticCond)) if sex: cond.append(tableClient['sex'].eq(sex)) if ageFrom <= ageTo: cond.append('Diagnosis.endDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)'%ageFrom) cond.append('Diagnosis.endDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)'%(ageTo+1)) if socStatusTypeId: subStmt = ('SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' +'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND ' +'ClientSocStatus.socStatusType_id=%d' % socStatusTypeId) cond.append('EXISTS('+subStmt+')') elif socStatusClassId: subStmt = ('SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' +'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND ' +'ClientSocStatus.socStatusClass_id=%d' % socStatusClassId) cond.append('EXISTS('+subStmt+')') if areaIdEnabled: if areaId: orgStructureIdList = getOrgStructureDescendants(areaId) else: orgStructureIdList = getOrgStructures(QtGui.qApp.currentOrgId()) tableOrgStructureAddress = db.table('OrgStructure_Address') tableAddress = db.table('Address') subCond = [ tableOrgStructureAddress['master_id'].inlist(orgStructureIdList), tableOrgStructureAddress['house_id'].eq(tableAddress['house_id']), ] cond.append(db.existsStmt(tableOrgStructureAddress, subCond)) if locality: # 1: горожане, isClientVillager == 0 или NULL # 2: сельские жители, isClientVillager == 1 cond.append('IFNULL(isClientVillager(Client.id), 0) = %d' % (locality-1)) return db.query(stmt % (db.joinAnd([tableDiagnosis['setDate'].le(endDate), tableDiagnosis['setDate'].ge(begDate)]), tableDiagnosis['setDate'].formatValue(endDate.addDays(1)), db.joinAnd(cond)))
def selectData(params): begDate = params.get('begDate', QtCore.QDate()) endDate = params.get('endDate', QtCore.QDate()) eventPurposeId = params.get('eventPurposeId', None) eventTypeId = params.get('eventTypeId', None) orgStructureId = params.get('orgStructureId', None) specialityId = params.get('specialityId', None) personId = params.get('personId', None) workOrgId = params.get('workOrgId', None) sex = params.get('sex', 0) ageFrom = params.get('ageFrom', 0) ageTo = params.get('ageTo', 150) areaIdEnabled = params.get('areaIdEnabled', False) chkOrgStructureArea = params.get('chkOrgStructureArea', False) areaId = params.get('areaId', None) MKBFilter = params.get('MKBFilter', 0) MKBFrom = params.get('MKBFrom', 'A00') MKBTo = params.get('MKBTo', 'Z99.9') MKBExFilter = params.get('MKBExFilter', 0) MKBExFrom = params.get('MKBExFrom', 'A00') MKBExTo = params.get('MKBExTo', 'Z99.9') addressEnabled = params.get('addressEnabled', False) addressType = params.get('addressType', 0) clientAddressType = params.get('clientAddressType', 0) clientAddressCityCode = params.get('clientAddressCityCode', None) clientAddressCityCodeList = params.get('clientAddressCityCodeList', None) clientAddressStreetCode = params.get('clientAddressStreetCode', None) clientHouse = params.get('clientHouse', '') clientCorpus = params.get('clientCorpus', '') clientFlat = params.get('clientFlat', '') stmt = """ SELECT count(Event.id) AS `count`, Event.result_id AS result_id, rbResult.name AS result, Event.isPrimary AS isPrimary, Event.execDate IS NOT NULL AS isDone, Event.`order` AS `order` FROM Event LEFT JOIN EventType ON EventType.id = Event.eventType_id LEFT JOIN Diagnostic ON Diagnostic.event_id = Event.id AND Diagnostic.id IN ( SELECT D1.id FROM Diagnostic AS D1 LEFT JOIN rbDiagnosisType AS DT1 ON DT1.id = D1.diagnosisType_id WHERE D1.event_id = Event.id AND DT1.code = (SELECT MIN(DT2.code) FROM Diagnostic AS D2 LEFT JOIN rbDiagnosisType AS DT2 ON DT2.id = D2.diagnosisType_id WHERE D2.event_id = Event.id) ) LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientAddress ON ClientAddress.client_id = Event.client_id AND ClientAddress.id = (SELECT MAX(id) FROM ClientAddress AS CA WHERE CA.Type=1 and CA.client_id = Event.client_id) LEFT JOIN Address ON Address.id = ClientAddress.address_id %s LEFT JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id LEFT JOIN vrbPersonWithSpeciality ON vrbPersonWithSpeciality.id = Event.execPerson_id LEFT JOIN rbResult ON rbResult.id = Event.result_id WHERE %s GROUP BY result_id, isPrimary, isDone, `order` """ db = QtGui.qApp.db tableEvent = db.table('Event') tableDiagnosis = db.table('Diagnosis') tableClient = db.table('Client') tableClientAddress = db.table('ClientAddress') tableOrgStructureAddress = db.table('OrgStructure_Address') tableAddress = db.table('Address') tableAddressForClient = db.table('Address').alias('AddressForCLient') tableAddressHouse = db.table('AddressHouse') tablePerson = db.table('vrbPersonWithSpeciality') tableDiagnostic = db.table('Diagnostic') tableDiseaseCharacter = db.table('rbDiseaseCharacter') cond = [] cond.append(tableEvent['deleted'].eq(0)) cond.append(tableEvent['execDate'].ge(begDate)) cond.append( db.joinOr([ tableEvent['execDate'].lt(endDate.addDays(1)), tableEvent['execDate'].isNull() ])) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) elif eventPurposeId: cond.append(db.table('EventType')['purpose_id'].eq(eventPurposeId)) if MKBFilter == 1: cond.append(tableDiagnosis['MKB'].ge(MKBFrom)) cond.append(tableDiagnosis['MKB'].le(MKBTo)) if MKBExFilter == 1: cond.append(tableDiagnosis['MKBEx'].ge(MKBExFrom)) cond.append(tableDiagnosis['MKBEx'].le(MKBExTo)) if personId: cond.append(tableDiagnostic['person_id'].eq(personId)) elif orgStructureId: cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) else: cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId())) if specialityId: cond.append(tablePerson['speciality_id'].eq(specialityId)) if workOrgId: cond.append( 'EXISTS (SELECT * FROM ClientWork WHERE ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) and ClientWork.org_id=%d)' % (workOrgId)) if sex: cond.append(tableClient['sex'].eq(sex)) if ageFrom <= ageTo: date = str(QtCore.QDate.currentDate().toString(QtCore.Qt.ISODate)) cond.append( 'IF(Diagnosis.endDate IS NOT NULL, Diagnosis.endDate, DATE(\'%s\')) >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' % (date, ageFrom)) cond.append( 'IF(Diagnosis.endDate IS NOT NULL, Diagnosis.endDate, DATE(\'%s\')) < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)' % (date, ageTo + 1)) additionalJoin = '' if areaIdEnabled: if chkOrgStructureArea: if areaId: orgStructureIdList = getOrgStructureDescendants(areaId) else: orgStructureIdList = getOrgStructures( QtGui.qApp.currentOrgId()) subCond = [ tableOrgStructureAddress['master_id'].inlist( orgStructureIdList), tableOrgStructureAddress['house_id'].eq( tableAddress['house_id']), ] cond.append(db.existsStmt(tableOrgStructureAddress, subCond)) if addressEnabled: if addressType in [STRICT_ADDRESS, NONRESIDENT_ADDRESS]: clientAddressType = clientAddressType if addressType == STRICT_ADDRESS else 0 additionalJoin = 'LEFT JOIN ClientAddress AS ClientAddressForClient ON ClientAddressForClient.client_id = Event.client_id AND ClientAddressForClient.id = (SELECT MAX(id) FROM ClientAddress AS CA2 WHERE CA2.Type=%d and CA2.client_id = Event.client_id) LEFT JOIN Address AS AddressForClient ON AddressForClient.id = ClientAddressForClient.address_id LEFT JOIN AddressHouse ON AddressHouse.id = AddressForClient.house_id' % clientAddressType if addressType == STRICT_ADDRESS: if clientFlat: cond.append( tableAddressForClient['flat'].eq(clientFlat)) if clientCorpus: cond.append( tableAddressHouse['corpus'].eq(clientCorpus)) if clientHouse: cond.append( tableAddressHouse['number'].eq(clientHouse)) if clientAddressStreetCode: cond.append(tableAddressHouse['KLADRStreetCode'].eq( clientAddressStreetCode)) if clientAddressCityCodeList: cond.append(tableAddressHouse['KLADRCode'].inlist( clientAddressCityCodeList)) else: if clientAddressCityCode: cond.append(tableAddressHouse['KLADRCode'].eq( clientAddressCityCode)) else: props = QtGui.qApp.preferences.appPrefs kladrCodeList = [ forceString(getVal(props, 'defaultKLADR', '')), forceString(getVal(props, 'provinceKLADR', '')) ] cond.append(tableAddressHouse['KLADRCode'].notInlist( kladrCodeList)) else: foreignDocumentTypeId = forceInt( db.translate('rbDocumentType', 'code', '9', 'id')) documentCond = 'EXISTS(SELECT ClientDocument.`id` FROM ClientDocument WHERE ClientDocument.`documentType_id`=%d AND ClientDocument.`client_id`=Client.`id`)' % foreignDocumentTypeId cond.append(documentCond) stmt = stmt % (additionalJoin, db.joinAnd(cond)) # print stmt return db.query(stmt)
def selectLinesAndData(params): db = QtGui.qApp.db listUetActionIds = params.get('idList') personId = params.get('personId') orgId = params.get('orgStructureId') begDate = params.get('begDate') endDate = params.get('endDate') tableEvent = db.table('Event') tableAction = db.table('Action') tableUetActionType = db.table('ActionType').alias('UetActionType') tableActionProperty = db.table('ActionProperty') tableActionPropertyType = db.table('ActionPropertyType') tableActionPropertyOrgStructure = db.table('ActionProperty_OrgStructure') tableMovingAction = tableAction.alias('MovingAction') tableUetAction = tableAction.alias('UetAction') tablePerson = db.table('Person') tableOrgStructure = db.table('OrgStructure') cond = [] cond.append(tableActionPropertyType['name'].eq(u'отделение пребывания')) cond.append(tableUetAction['amount'].ne(0)) cond.append(tableOrgStructure['name'].notlike( u'%ультразвуковой и функциональной диагностики%')) if personId: cond.append(tablePerson['id'].eq(personId)) if begDate and endDate: cond.append(tableUetAction['begDate'].dateLe(endDate)) cond.append(tableUetAction['endDate'].dateGe(begDate)) if orgId: cond.append(tableOrgStructure['id'].inlist( getOrgStructureDescendants(orgId))) elif orgId == None: allOrgId = getOrgStructures(QtGui.qApp.currentOrgId()) cond.append(tableOrgStructure['id'].inlist(allOrgId)) cols = [ tableUetAction['amount'], tableUetAction['actionType_id'].alias('actionType_id'), tableActionPropertyOrgStructure['value'].alias('orgStructure_id'), '''SUM(IFNULL(UetActionType.`UetDoctor` * UetAction.`amount`, 0)) AS `sum`''', tableOrgStructure['name'].alias('orgStructureName') ] queryTable = tableEvent.innerJoin(tableMovingAction, [ ''' MovingAction.id = ( SELECT max(Action.id) FROM Action INNER JOIN ActionType ON Action.actionType_id = ActionType.id WHERE ActionType.flatCode = 'moving' AND Action.deleted = 0 AND Action.event_id = Event.id ) ''' ]) queryTable = queryTable.innerJoin( tablePerson, [tableEvent['execPerson_id'].eq(tablePerson['id'])]) queryTable = queryTable.innerJoin( tableActionProperty, [tableMovingAction['id'].eq(tableActionProperty['action_id'])]) queryTable = queryTable.innerJoin( tableActionPropertyType, [tableActionPropertyType['id'].eq(tableActionProperty['type_id'])]) queryTable = queryTable.innerJoin( tableActionPropertyOrgStructure, [tableActionProperty['id'].eq(tableActionPropertyOrgStructure['id'])]) queryTable = queryTable.innerJoin(tableUetAction, [ tableUetAction['event_id'].eq(tableEvent['id']), tableUetAction['actionType_id'].inlist(listUetActionIds) ]) queryTable = queryTable.innerJoin( tableUetActionType, [tableUetActionType['id'].eq(tableUetAction['actionType_id'])]) queryTable = queryTable.innerJoin( tableOrgStructure, [tableActionPropertyOrgStructure['value'].eq(tableOrgStructure['id'])]) group = [ tableUetAction['actionType_id'], tableActionPropertyOrgStructure['value'] ] order = ['orgStructure_id'] stmt = db.selectStmt(queryTable, cols, where=cond, group=group, order=order) return db.query(stmt)
def build(self, params): areaId = params.get('areaId', None) endDate = params.get('endDate', QtCore.QDate()) addressType = params.get('addressType', 0) isDetailByDepartment = params.get('detailByDepartment', False) isDetailByAge = params.get('detailByAge', False) rowNumber = [1] employableColBases = { 19: 10, 24: 12, 29: 14, 34: 16, 39: 18, 44: 20, 49: 22, 54: 24, 59: 26, 64: 28, 69: 30, 74: 32, 79: 34 } #Количество столбцов с данными (за исключением столбцов "Категория населения", "№": 10 на детские возраста, 28 или 4 на взрослые и 1 на поле "Всего") reportRowSize = [39 if isDetailByAge else 15] def insertDataInTable(reportTable, reportData, charFormat=None): for rowIndex, rowName in enumerate( [u'ИТОГО', u'работающие', u'неработающие']): i = reportTable.addRow() reportTable.setText(i, 0, rowName, charFormat=charFormat) reportTable.setText(i, 1, rowNumber[0], charFormat=charFormat) rowNumber[0] += 1 for j in xrange(len(reportData[rowIndex])): reportTable.setText(i, 2 + j, reportData[rowIndex][j], charFormat=charFormat) #end insertDataInTable def getData(areaId, endDate, addressType, isDetailByAge=False): if areaId: areaIdList = getOrgStructureDescendants(areaId) else: areaIdList = getOrgStructures(QtGui.qApp.currentOrgId()) query = selectData(endDate, areaIdList, addressType) reportData = [[0] * reportRowSize[0] for row in xrange(3)] self.setQueryText(forceString(query.lastQuery())) while query.next(): record = query.record() cnt = forceInt(record.value('cnt')) age = forceInt(record.value('clientAge')) sex = forceInt(record.value('clientSex')) busy = forceBool(record.value('busy')) if age < 1: colBase = 0 elif age == 1: colBase = 2 elif age <= 6: colBase = 4 elif age <= 14: colBase = 6 elif age <= 17: colBase = 8 else: if isDetailByAge: minAgeHigh = 150 colBase = 36 for ageHigh in employableColBases.keys(): if age <= ageHigh and ageHigh < minAgeHigh: minAgeHigh = ageHigh colBase = employableColBases[ageHigh] else: if (sex == 1 and age < 60) or (sex != 1 and age < 55): colBase = 10 else: colBase = 12 cols = [colBase + (0 if sex == 1 else 1), reportRowSize[0] - 1] rows = [0, 1 if busy else 2] if cols[0] < 0 or cols[0] > reportRowSize[0] - 1: print cols[0] for row in rows: for col in cols: reportData[row][col] += cnt return reportData #end getData def detailByDepartment(reportTable, areaId, endDate, addressType, isDetailByAge, progressDialog=None): subAreaIdList = [] db = QtGui.qApp.db tableOrgStructure = db.table('OrgStructure') whereCond = [] if areaId: #Если указан отдельный участок или группа участков whereCond.append(tableOrgStructure['parent_id'].eq(areaId)) else: #Если в качестве участка указан весь ЛПУ whereCond.append(tableOrgStructure['parent_id'].isNull()) whereCond.append(tableOrgStructure['organisation_id'].eq( QtGui.qApp.currentOrgId())) subAreaIdList = db.getIdList(table=tableOrgStructure, where=whereCond) if subAreaIdList: for subAreaId in subAreaIdList: row = reportTable.addRow() reportTable.mergeCells(row, 0, 1, reportRowSize[0] + 3) charFormat = QtGui.QTextCharFormat() charFormat.setFontWeight(QtGui.QFont.Bold) subAreaName = getOrgStructureFullName(subAreaId) reportTable.setText(row, 0, subAreaName, charFormat=charFormat, blockFormat=CReportBase.AlignLeft) if progressDialog: progressDialog.setWindowTitle(u'Обработка %s' % subAreaName) progressDialog.step() QtGui.qApp.processEvents( QtCore.QEventLoop.ExcludeUserInputEvents) detailByDepartment(reportTable, subAreaId, endDate, addressType, isDetailByAge, progressDialog) else: insertDataInTable( reportTable, getData(areaId, endDate, addressType, isDetailByAge)) #end detailByDepartment # now text doc = QtGui.QTextDocument() cursor = QtGui.QTextCursor(doc) cursor.setCharFormat(CReportBase.ReportTitle) cursor.insertText(self.title()) cursor.insertBlock() self.dumpParams(cursor, params) cursor.insertBlock() tableColumns = [ ('15?', [u'Категории населения', u''], CReportBase.AlignLeft), ('5?', [u'№ стр.', u''], CReportBase.AlignRight), ('5?', [ u'Численность прикреплённого населения по возрастному составу', u'дети', u'', u'до 1 года', u'М' ], CReportBase.AlignRight), ('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight), ('5?', [u'', u'', u'', u'1 год', u'М'], CReportBase.AlignRight), ('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight), ('5?', [u'', u'', u'', u'2-6 лет', u'М'], CReportBase.AlignRight), ('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight), ('5?', [u'', u'', u'', u'7-14 лет', u'М'], CReportBase.AlignRight), ('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight), ('5?', [u'', u'', u'', u'15-17 лет', u'М'], CReportBase.AlignRight), ('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight) ] if isDetailByAge: ageHighKeys = employableColBases.keys() ageHighKeys.sort() for ageHigh in ageHighKeys: index = ageHighKeys.index(ageHigh) if index <= 0: startAge = '18' else: startAge = '%d' % (ageHighKeys[index - 1] + 1) ageRangeText = u'%s - %d лет' % (startAge, ageHigh) ageState = u'взрослые' if ageHigh == 19 else u'' employableState = u'трудосп. возраста' if ageHigh == 59 else u'' tableColumns.append( ('5?', [u'', ageState, employableState, ageRangeText, u'М'], CReportBase.AlignRight)) employableState = u'нетрудосп. возраста' if ageHigh == 59 else u'' tableColumns.append( ('5?', [u'', u'', employableState, u'', u'Ж'], CReportBase.AlignRight)) tableColumns.append(('5?', [u'', u'', u'', u'80 и ст.', u'М'], CReportBase.AlignRight)) tableColumns.append(('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight)) else: tableColumns.append( ('5?', [u'', u'взрослые', u'трудосп. возраста', u'18-59 лет', u'М'], CReportBase.AlignRight)) tableColumns.append(('5?', [u'', u'', u'', u'18-54 лет', u'Ж'], CReportBase.AlignRight)) tableColumns.append( ('5?', [u'', u'', u'нетрудосп. возраста', u'60 и ст.', u'М'], CReportBase.AlignRight)) tableColumns.append(('5?', [u'', u'', u'', u'55 и ст.', u'Ж'], CReportBase.AlignRight)) tableColumns.append(('5?', [ u'Всего', ], CReportBase.AlignRight)) table = createTable(cursor, tableColumns) table.mergeCells(0, 0, 4, 1) # Категории населения table.mergeCells(0, 1, 4, 1) # № стр. table.mergeCells(0, 2, 1, reportRowSize[0] - 1) # Численность... table.mergeCells(1, 2, 2, 10) # дети table.mergeCells(3, 2, 1, 2) # <1 table.mergeCells(3, 4, 1, 2) # 1 table.mergeCells(3, 6, 1, 2) # 2-6 table.mergeCells(3, 8, 1, 2) # 7-14 table.mergeCells(3, 10, 1, 2) # 15-17 if isDetailByAge: for i in xrange(27): table.mergeCells(3, 12 + i * 2, 1, 2) table.mergeCells(3, 12 + i * 2 + 1, 1, 2) table.mergeCells(1, 12, 1, 28) # взрослые table.mergeCells(2, 12, 1, 17) # тр. table.mergeCells(2, 29, 1, 11) # нетр. table.mergeCells(0, 40, 4, 1) # всего else: table.mergeCells(1, 12, 1, 4) # взрослые table.mergeCells(2, 12, 1, 2) # тр. table.mergeCells(2, 14, 1, 2) # нетр. table.mergeCells(0, 16, 4, 1) # всего reportData = getData(areaId, endDate, addressType, isDetailByAge) charFormat = None if isDetailByDepartment: if areaId: areaIdList = getOrgStructureDescendants(areaId) else: areaIdList = getOrgStructures(QtGui.qApp.currentOrgId()) progressBar = CProgressBar( ) #QtGui.QProgressDialog(u'Обработка данных по участкам', 'Отмена', 0, len(areaIdList)) progressBar.setMinimum(0) progressBar.setMaximum(len(areaIdList)) parentCenter = QtGui.QDesktopWidget().availableGeometry().center() progressBar.setWindowFlags(progressBar.windowFlags() | QtCore.Qt.WindowCloseButtonHint) progressBar.setGeometry(parentCenter.x() - 250, parentCenter.y() - 40, 500, 80) progressBar.setText('%v/%m') progressBar.show() QtGui.qApp.processEvents(QtCore.QEventLoop.ExcludeUserInputEvents) detailByDepartment(table, areaId, endDate, addressType, isDetailByAge, progressBar) progressBar.hide() charFormat = QtGui.QTextCharFormat() charFormat.setFontWeight(QtGui.QFont.Bold) row = table.addRow() table.mergeCells(row, 0, 1, reportRowSize[0] + 3) table.setText(row, 0, getOrgStructureFullName(areaId), charFormat=charFormat, blockFormat=CReportBase.AlignLeft) insertDataInTable(table, reportData, charFormat=charFormat) return doc
def selectData(params): begDate = params.get('begDate', QtCore.QDate()) endDate = params.get('endDate', QtCore.QDate()) eventPurposeIdList = params.get('eventPurposeIdList', []) eventTypeId = params.get('eventTypeId', None) orgStructureId = params.get('orgStructureId', None) specialityId = params.get('specialityId', None) chkTraumaType = params.get('chkTraumaType', False) chkTraumaTypeAny = params.get('chkTraumaTypeAny', False) traumaTypeId = params.get('traumaTypeId', None) personId = params.get('personId', None) workOrgId = params.get('workOrgId', None) sex = params.get('sex', 0) ageFrom = params.get('ageFrom', 0) ageTo = params.get('ageTo', 150) areaIdEnabled = params.get('areaIdEnabled', False) areaId = params.get('areaId', None) MKBFilter = params.get('MKBFilter', 0) MKBFrom = params.get('MKBFrom', 'A00') MKBTo = params.get('MKBTo', 'Z99.9') MKBExFilter = params.get('MKBExFilter', 0) MKBExFrom = params.get('MKBExFrom', 'A00') MKBExTo = params.get('MKBExTo', 'Z99.9') diseaseCharacterCodes = classToCodes(params.get('characterClass', 0)) onlyFirstTime = params.get('onlyFirstTime', False) accountAccomp = params.get('accountAccomp', False) locality = params.get('locality', 0) socStatusClassId = params.get('socStatusClassId', None) socStatusTypeId = params.get('socStatusTypeId', None) visitEmergency = params.get('visitEmergency', False) period = params.get('regInPeriod', False) stmt = """ SELECT Diagnosis.client_id, Diagnosis.id AS diagnosis_id, Diagnosis.MKB, Diagnosis.MKBEx, Diagnosis.setDate, Diagnosis.endDate, rbDiseaseCharacter.code AS diseaseCharacter, rbTraumaType.name AS traumaType, DATE(Diagnostic.endDate) AS diagnosticDate, Diagnostic.hospital AS diagnosticHospital, Diagnostic.sanatorium AS diagnosticSanatorium, rbDispanser.name AS diagnosticDispanser, vrbPersonWithSpeciality.name AS diagnosticPerson FROM Diagnosis LEFT JOIN Client ON Client.id = Diagnosis.client_id LEFT JOIN ClientAddress ON ClientAddress.client_id = Diagnosis.client_id AND ClientAddress.id = (SELECT MAX(id) FROM ClientAddress AS CA WHERE CA.Type=1 and CA.client_id = Diagnosis.client_id) LEFT JOIN Address ON Address.id = ClientAddress.address_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnosis.character_id LEFT JOIN rbTraumaType ON rbTraumaType.id = Diagnosis.traumaType_id LEFT JOIN Diagnostic ON Diagnostic.diagnosis_id = Diagnosis.id LEFT JOIN rbDiagnosisType ON rbDiagnosisType.id = Diagnostic.diagnosisType_id LEFT JOIN rbDispanser ON rbDispanser.id = Diagnostic.dispanser_id LEFT JOIN vrbPersonWithSpeciality ON vrbPersonWithSpeciality.id = Diagnostic.person_id LEFT JOIN Event ON Event.id = Diagnostic.event_id LEFT JOIN EventType ON EventType.id = Event.eventType_id WHERE %s ORDER BY Client.lastName, Client.lastName, Client.firstName, Client.patrName, Client.birthDate, Client.id, Diagnosis.MKB, Diagnosis.MKBEx, Diagnostic.endDate """ db = QtGui.qApp.db tableDiagnosis = db.table('Diagnosis') tableClient = db.table('Client') tablePerson = db.table('vrbPersonWithSpeciality') tableDiagnostic = db.table('Diagnostic') tableDiseaseCharacter = db.table('rbDiseaseCharacter') tableEvent = db.table('Event') cond = [] cond.append(tableDiagnosis['deleted'].eq(0)) cond.append(tableDiagnosis['mod_id'].isNull()) if period: addDateInRange(cond, tableDiagnostic['endDate'], begDate, endDate) cond.append(tableDiagnostic['setDate'].le(endDate)) cond.append( db.joinOr([ tableDiagnostic['endDate'].ge(begDate), tableDiagnostic['endDate'].isNull() ])) # addDateInRange(cond, tableDiagnostic['setDate'], begDate, endDate) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) elif eventPurposeIdList: cond.append( db.table('EventType')['purpose_id'].inlist(eventPurposeIdList)) if onlyFirstTime: cond.append(tableDiagnosis['setDate'].le(endDate)) cond.append(tableDiagnosis['setDate'].ge(begDate)) else: cond.append( db.joinOr([ tableDiagnosis['setDate'].isNull(), tableDiagnosis['setDate'].le(endDate) ])) cond.append( db.joinOr([ tableDiagnosis['endDate'].ge(begDate), tableDiseaseCharacter['code'].eq('3') ])) if MKBFilter == 1: cond.append(tableDiagnosis['MKB'].ge(MKBFrom)) cond.append(tableDiagnosis['MKB'].le(MKBTo)) if MKBExFilter == 1: cond.append(tableDiagnosis['MKBEx'].ge(MKBExFrom)) cond.append(tableDiagnosis['MKBEx'].le(MKBExTo)) if personId: cond.append(tableDiagnostic['person_id'].eq(personId)) elif orgStructureId: cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) else: cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId())) if specialityId: cond.append(tablePerson['speciality_id'].eq(specialityId)) if chkTraumaType: if chkTraumaTypeAny: cond.append(tableDiagnosis['traumaType_id'].isNotNull()) elif traumaTypeId: cond.append(tableDiagnosis['traumaType_id'].eq(traumaTypeId)) else: cond.append(tableDiagnosis['traumaType_id'].isNull()) if workOrgId: cond.append( 'EXISTS (SELECT * FROM ClientWork WHERE ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) and ClientWork.org_id=%d)' % (workOrgId)) if not visitEmergency: cond.append( u'''EventType.medicalAidType_id IS NULL OR (EventType.medicalAidType_id NOT IN (SELECT rbMedicalAidType.id from rbMedicalAidType where rbMedicalAidType.code IN (\'4\')))''' '') if sex: cond.append(tableClient['sex'].eq(sex)) if ageFrom <= ageTo: cond.append( 'Diagnosis.endDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' % ageFrom) cond.append( 'Diagnosis.endDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)' % (ageTo + 1)) if areaIdEnabled: if areaId: orgStructureIdList = getOrgStructureDescendants(areaId) else: orgStructureIdList = getOrgStructures(QtGui.qApp.currentOrgId()) tableOrgStructureAddress = db.table('OrgStructure_Address') tableAddress = db.table('Address') subCond = [ tableOrgStructureAddress['master_id'].inlist(orgStructureIdList), tableOrgStructureAddress['house_id'].eq(tableAddress['house_id']), ] cond.append(db.existsStmt(tableOrgStructureAddress, subCond)) if diseaseCharacterCodes: if diseaseCharacterCodes != [None]: cond.append( tableDiseaseCharacter['code'].inlist(diseaseCharacterCodes)) else: cond.append(tableDiseaseCharacter['code'].isNull()) if not accountAccomp: tableDiagnosisType = db.table('rbDiagnosisType') cond.append(tableDiagnosisType['code'].inlist(['1', '2', '4'])) if locality: # 1: горожане, isClientVillager == 0 или NULL # 2: сельские жители, isClientVillager == 1 cond.append('IFNULL(isClientVillager(Client.id), 0) = %d' % (locality - 1)) if socStatusTypeId: subStmt = ( 'SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' + 'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND ' + 'ClientSocStatus.socStatusType_id=%d' % socStatusTypeId) cond.append('EXISTS(' + subStmt + ')') elif socStatusClassId: subStmt = ( 'SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' + 'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND ' + 'ClientSocStatus.socStatusClass_id=%d' % socStatusClassId) cond.append('EXISTS(' + subStmt + ')') return db.query(stmt % (db.joinAnd(cond)))
def selectData(params): begDate = params.get('begDate', QtCore.QDate()) endDate = params.get('endDate', QtCore.QDate()) eventPurposeId = params.get('eventPurposeId', None) eventTypeId = params.get('eventTypeId', None) orgStructureId = params.get('orgStructureId', None) specialityId = params.get('specialityId', None) personId = params.get('personId', None) workOrgId = params.get('workOrgId', None) sex = params.get('sex', 0) ageFrom = params.get('ageFrom', 0) ageTo = params.get('ageTo', 150) areaIdEnabled = params.get('areaIdEnabled', False) chkOrgStructureArea = params.get('chkOrgStructureArea', False) areaId = params.get('areaId', None) MKBFilter = params.get('MKBFilter', 0) MKBFrom = params.get('MKBFrom', 'A00') MKBTo = params.get('MKBTo', 'Z99.9') MKBExFilter = params.get('MKBExFilter', 0) MKBExFrom = params.get('MKBExFrom', 'A00') MKBExTo = params.get('MKBExTo', 'Z99.9') addressEnabled = params.get('addressEnabled', False) addressType = params.get('addressType', 0) clientAddressType = params.get('clientAddressType', 0) clientAddressCityCode = params.get('clientAddressCityCode', None) clientAddressCityCodeList = params.get('clientAddressCityCodeList', None) clientAddressStreetCode = params.get('clientAddressStreetCode', None) clientHouse = params.get('clientHouse', '') clientCorpus = params.get('clientCorpus', '') clientFlat = params.get('clientFlat', '') order = params.get('order', 0) primary = params.get('primary', 0) resultId = params.get('resultId', None) visitEmergency = params.get('visitEmergency', False) db = QtGui.qApp.db tableClient = db.table('Client') tableEvent = db.table('Event') tableEventType = db.table('EventType') tableClientAddress = db.table('ClientAddress') tableAddress = db.table('Address') tableOrgStructureAddress = db.table('OrgStructure_Address') tableDiagnostic = db.table('Diagnostic') tableDiagnosis = db.table('Diagnosis') tableDiagnosisType = db.table('rbDiagnosisType') tablePerson = db.table('vrbPersonWithSpeciality') tableResult = db.table('rbResult') tableAddressHouseLiv = db.table('AddressHouse').alias('AddressHouseLiv') tableClientAddressForClientLiv = db.table('ClientAddress').alias( 'ClientAddressForClientLiv') tableAddressForClientLiv = db.table('Address').alias('AddressForClientLiv') tableAddressHouseReg = db.table('AddressHouse').alias('AddressHouseReg') tableClientAddressForClientReg = db.table('ClientAddress').alias( 'ClientAddressForClientReg') tableAddressForClientReg = db.table('Address').alias('AddressForClientReg') tableKLADRCodeReg = db.table('kladr.KLADR') tableKLADRStreetReg = db.table('kladr.STREET') tableClientContact = db.table('ClientContact') tableContactType = db.table('rbContactType') tableVisit = db.table('Visit') tableScene = db.table('rbScene') queryTable = tableEvent queryTable = queryTable.leftJoin( tableResult, tableResult['id'].eq(tableEvent['result_id'])) queryTable = queryTable.leftJoin( tableClient, tableClient['id'].eq(tableEvent['client_id'])) queryTable = queryTable.leftJoin( tableClientContact, tableClientContact['client_id'].eq(tableClient['id'])) queryTable = queryTable.leftJoin( tableContactType, tableContactType['id'].eq(tableClientContact['contactType_id'])) queryTable = queryTable.leftJoin( tableEventType, tableEventType['id'].eq(tableEvent['eventType_id'])) queryTable = queryTable.leftJoin( tableClientAddress, tableClientAddress['client_id'].eq(tableClient['id'])) queryTable = queryTable.leftJoin( tableAddress, tableAddress['id'].eq(tableClientAddress['address_id'])) queryTable = queryTable.leftJoin( tableVisit, tableEvent['id'].eq(tableVisit['event_id'])) queryTable = queryTable.leftJoin( tableScene, tableScene['id'].eq(tableVisit['scene_id'])) diagnosticJoinCond = '''Diagnostic.event_id = Event.id AND Diagnostic.id IN (SELECT D1.id FROM Diagnostic AS D1 LEFT JOIN rbDiagnosisType AS DT1 ON DT1.id = D1.diagnosisType_id WHERE D1.event_id = Event.id AND DT1.code = (SELECT MIN(DT2.code) FROM Diagnostic AS D2 LEFT JOIN rbDiagnosisType AS DT2 ON DT2.id = D2.diagnosisType_id WHERE D2.event_id = Event.id ) )''' queryTable = queryTable.leftJoin(tableDiagnostic, diagnosticJoinCond) queryTable = queryTable.leftJoin( tableDiagnosis, tableDiagnosis['id'].eq(tableDiagnostic['diagnosis_id'])) queryTable = queryTable.leftJoin( tableDiagnosisType, tableDiagnosisType['id'].eq(tableDiagnosis['diagnosisType_id'])) queryTable = queryTable.leftJoin( tablePerson, tablePerson['id'].eq(tableEvent['execPerson_id'])) joinCondClientAddressForClientReg = 'ClientAddressForClientReg.client_id = Event.client_id AND ClientAddressForClientReg.id = (SELECT MAX(id) FROM ClientAddress AS CAReg WHERE CAReg.Type=0 and CAReg.client_id = Event.client_id)' queryTable = queryTable.leftJoin(tableClientAddressForClientReg, joinCondClientAddressForClientReg) queryTable = queryTable.leftJoin( tableAddressForClientReg, tableAddressForClientReg['id'].eq( tableClientAddressForClientReg['address_id'])) queryTable = queryTable.leftJoin( tableAddressHouseReg, tableAddressHouseReg['id'].eq(tableAddressForClientReg['house_id'])) queryTable = queryTable.leftJoin( tableKLADRCodeReg, tableKLADRCodeReg['CODE'].eq(tableAddressHouseReg['KLADRCode'])) queryTable = queryTable.leftJoin( tableKLADRStreetReg, tableKLADRStreetReg['CODE'].eq( tableAddressHouseReg['KLADRStreetCode'])) joinCondClientAddressForClientLiv = 'ClientAddressForClientLiv.client_id = Event.client_id AND ClientAddressForClientLiv.id = (SELECT MAX(id) FROM ClientAddress AS CALiv WHERE CALiv.Type=0 and CALiv.client_id = Event.client_id)' queryTable = queryTable.leftJoin(tableClientAddressForClientLiv, joinCondClientAddressForClientLiv) queryTable = queryTable.leftJoin( tableAddressForClientLiv, tableAddressForClientLiv['id'].eq( tableClientAddressForClientLiv['address_id'])) queryTable = queryTable.leftJoin( tableAddressHouseLiv, tableAddressHouseLiv['id'].eq(tableAddressForClientLiv['house_id'])) cond = [] cond.append(tableEvent['deleted'].eq(0)) cond.append(tableEvent['execDate'].ge(begDate)) cond.append( db.joinOr([ tableEvent['execDate'].lt(endDate.addDays(1)), tableEvent['execDate'].isNull() ])) # cond.append(tableClientContact['deleted'].eq(0)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) elif eventPurposeId: cond.append(db.table('EventType')['purpose_id'].eq(eventPurposeId)) if MKBFilter == 1: cond.append(tableDiagnosis['MKB'].ge(MKBFrom)) cond.append(tableDiagnosis['MKB'].le(MKBTo)) if MKBExFilter == 1: cond.append(tableDiagnosis['MKBEx'].ge(MKBExFrom)) cond.append(tableDiagnosis['MKBEx'].le(MKBExTo)) if personId: cond.append(tableDiagnostic['person_id'].eq(personId)) elif orgStructureId: cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) else: cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId())) if specialityId: cond.append(tablePerson['speciality_id'].eq(specialityId)) if workOrgId: cond.append( 'EXISTS (SELECT * FROM ClientWork WHERE ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) and ClientWork.org_id=%d)' % (workOrgId)) if not visitEmergency: cond.append( u'''EventType.medicalAidType_id IS NULL OR (EventType.medicalAidType_id NOT IN (SELECT rbMedicalAidType.id from rbMedicalAidType where rbMedicalAidType.code IN (\'4\')))''' '') if sex: cond.append(tableClient['sex'].eq(sex)) if ageFrom <= ageTo: date = str(QtCore.QDate.currentDate().toString(QtCore.Qt.ISODate)) cond.append( 'IF(Diagnosis.endDate IS NOT NULL, Diagnosis.endDate, DATE(\'%s\')) >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' % (date, ageFrom)) cond.append( 'IF(Diagnosis.endDate IS NOT NULL, Diagnosis.endDate, DATE(\'%s\')) < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)' % (date, ageTo + 1)) if areaIdEnabled: if chkOrgStructureArea: if areaId: orgStructureIdList = getOrgStructureDescendants(areaId) else: orgStructureIdList = getOrgStructures( QtGui.qApp.currentOrgId()) subCond = [ tableOrgStructureAddress['master_id'].inlist( orgStructureIdList), tableOrgStructureAddress['house_id'].eq( tableAddress['house_id']), ] cond.append(db.existsStmt(tableOrgStructureAddress, subCond)) if addressEnabled: if addressType in [STRICT_ADDRESS, NONRESIDENT_ADDRESS]: clientAddressType = clientAddressType if addressType == STRICT_ADDRESS else 0 if clientAddressType == 0: tableAddressForClient = tableAddressForClientReg tableAddressHouse = tableAddressHouseReg else: tableAddressForClient = tableAddressForClientLiv tableAddressHouse = tableAddressHouseLiv if addressType == STRICT_ADDRESS: if clientFlat: cond.append( tableAddressForClient['flat'].eq(clientFlat)) if clientCorpus: cond.append( tableAddressHouse['corpus'].eq(clientCorpus)) if clientHouse: cond.append( tableAddressHouse['number'].eq(clientHouse)) if clientAddressCityCodeList: cond.append(tableAddressHouse['KLADRCode'].inlist( clientAddressCityCodeList)) else: if clientAddressCityCode: cond.append(tableAddressHouse['KLADRCode'].eq( clientAddressCityCode)) if clientAddressStreetCode: cond.append(tableAddressHouse['KLADRStreetCode'].eq( clientAddressStreetCode)) else: props = QtGui.qApp.preferences.appPrefs kladrCodeList = [ forceString(getVal(props, 'defaultKLADR', '')), forceString(getVal(props, 'provinceKLADR', '')) ] cond.append(tableAddressHouse['KLADRCode'].notInlist( kladrCodeList)) else: foreignDocumentTypeId = forceInt( db.translate('rbDocumentType', 'code', '9', 'id')) documentCond = 'EXISTS(SELECT ClientDocument.`id` FROM ClientDocument WHERE ClientDocument.`documentType_id`=%d AND ClientDocument.`client_id`=Client.`id`)' % foreignDocumentTypeId cond.append(documentCond) if order: cond.append(tableEvent['order'].eq(order - 1)) if primary: cond.append(tableEvent['isPrimary'].eq(primary)) if resultId: cond.append(tableEvent['result_id'].eq(resultId)) fields = [ 'CONCAT_WS(\' \', Client.`lastName`, Client.`firstName`, Client.`patrName`) AS fio', tableClient['sex'].name(), 'IF(MONTH(Client.`birthDate`)>MONTH(CURRENT_DATE()) OR (MONTH(Client.`birthDate`) = MONTH(CURRENT_DATE()) AND DAYOFMONTH(Client.`birthDate`) > DAYOFMONTH(CURRENT_DATE())), YEAR(CURRENT_DATE())-YEAR(Client.`birthDate`)-1, YEAR(CURRENT_DATE())-YEAR(Client.`birthDate`)) AS clientAge', tableClient['id'].alias('clientId'), tableAddressForClientReg['flat'].alias('clientFlat'), tableAddressHouseReg['corpus'].alias('clientCorpus'), tableAddressHouseReg['number'].alias('clientNumber'), tableKLADRCodeReg['NAME'].alias('kladrName'), tableKLADRCodeReg['SOCR'].alias('kladrSocr'), tableKLADRStreetReg['NAME'].alias('kaldrStreetName'), tableKLADRStreetReg['SOCR'].alias('kladrStreetSocr'), tableClientContact['contact'].alias('clientContact'), tableContactType['name'].alias('contactTypeName'), tableDiagnosis['MKB'].name(), tableEventType['code'].alias('eventTypeCode'), tableEventType['name'].alias('eventTypeName'), tableEvent['setDate'].alias('eventSetDate'), tableEvent['execDate'].alias('eventExecDate'), tableEvent['id'].alias('eventId'), tableClientContact['id'].alias('clientContactId'), tableClientContact['deleted'].alias('contactDeleted'), tablePerson['name'].alias('person'), tableScene['name'].alias('scene') ] stmt = db.selectStmt(queryTable, fields, cond, order='fio') # print stmt return db.query(stmt)
def selectDataHurt(begDate, endDate, workOrgId, sex, ageFrom, ageTo, areaIdEnabled, areaId, MKBFilter, MKBFrom, MKBTo, MKBExFilter, MKBExFrom, MKBExTo, socStatusType, socStatusClass, forTeenager, orgStructureAttachTypeId): db = QtGui.qApp.db tableDiagnosis = db.table('Diagnosis') tableClient = db.table('Client') tableClientWork = db.table('ClientWork') tableClientWorkHurt = db.table('ClientWork_Hurt') tableClientAddress = db.table('ClientAddress') tableAddress = db.table('Address') tableDiagnostic = db.table('Diagnostic') tableEvent = db.table('Event') tableClientSocStatus = db.table('ClientSocStatus') queryTable = tableDiagnosis queryTable = queryTable.innerJoin(tableClient, tableClient['id'].eq(tableDiagnosis['client_id'])) queryTable = queryTable.innerJoin(tableClientWork, tableClientWork['client_id'].eq(tableClient['id'])) queryTable = queryTable.innerJoin(tableClientWorkHurt, tableClientWorkHurt['master_id'].eq(tableClientWork['id'])) queryTable = queryTable.innerJoin(tableDiagnostic, tableDiagnostic['diagnosis_id'].eq(tableDiagnosis['id'])) queryTable = queryTable.innerJoin(tableEvent, tableEvent['id'].eq(tableDiagnostic['event_id'])) cond = [tableDiagnosis['deleted'].eq(0)] if MKBFilter == 1: cond.append(tableDiagnosis['MKB'].ge(MKBFrom)) cond.append(tableDiagnosis['MKB'].le(MKBTo)) if MKBExFilter == 1: cond.append(tableDiagnosis['MKBEx'].ge(MKBExFrom)) cond.append(tableDiagnosis['MKBEx'].le(MKBExTo)) if workOrgId: cond.append(tableClientWork['org_id'].eq(workOrgId)) if sex: cond.append(tableClient['sex'].eq(sex)) if not forTeenager: if ageFrom <= ageTo: cond.append('Diagnosis.endDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)'%ageFrom) cond.append('Diagnosis.endDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)'%(ageTo+1)) if areaIdEnabled: queryTable = queryTable.innerJoin(tableClientAddress, db.joinAnd([tableClientAddress['client_id'].eq(tableDiagnosis['client_id']), 'ClientAddress.id = (SELECT MAX(id) FROM ClientAddress AS CA0 WHERE CA0.Type=0 and CA0.client_id = Diagnosis.client_id)'])) queryTable = queryTable.innerJoin(tableAddress, tableAddress['id'].eq(tableClientAddress['address_id'])) if areaId: orgStructureIdList = getOrgStructureDescendants(areaId) else: orgStructureIdList = getOrgStructures(QtGui.qApp.currentOrgId()) tableOrgStructureAddress = db.table('OrgStructure_Address') tableAddress = db.table('Address') subCond = [ tableOrgStructureAddress['master_id'].inlist(orgStructureIdList), tableOrgStructureAddress['house_id'].eq(tableAddress['house_id']), ] cond.append(db.existsStmt(tableOrgStructureAddress, subCond)) if socStatusType or socStatusClass: queryTable = queryTable.innerJoin(tableClientSocStatus, tableClient['id'].eq(tableClientSocStatus['client_id'])) if socStatusType: cond.append(tableClientSocStatus['socStatusType_id'].eq(socStatusType)) if socStatusClass: cond.append(tableClientSocStatus['socStatusClass_id'].eq(socStatusClass)) if forTeenager: cond.append('age(Client.birthDate, Diagnosis.endDate) <= 17') if orgStructureAttachTypeId: tableClientAttach = db.table('ClientAttach') tableOrgStructure = db.table('OrgStructure') attachTypeId = forceRef(db.translate('rbAttachType', 'code', u'1', 'id')) queryTable = queryTable.leftJoin(tableClientAttach, '''ClientAttach.id =(SELECT max(clAttach.id) FROM ClientAttach clAttach WHERE clAttach.attachType_id = %s AND clAttach.client_id = Client.id)''' % (attachTypeId)) queryTable = queryTable.leftJoin(tableOrgStructure, tableOrgStructure['id'].eq(tableClientAttach['orgStructure_id'])) orgStructureIdList = db.getDescendants('OrgStructure', 'parent_id', orgStructureAttachTypeId) cond.append(tableClientAttach['orgStructure_id'].inlist(orgStructureIdList)) if not forTeenager: endDate1 = forceString(endDate) endDate1 = endDate1.split('.') endDateYear = endDate begDateYear = endDate begDateYear.setDate(forceInt(endDate1[2]),1,1) else: begDateYear = begDate endDateYear = endDate if forTeenager: select = ['''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtSetInYear''' % begDateYear.toString('yyyy-MM-dd'), '''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND ((Diagnostic.`character_id`= 1) OR (Diagnostic.`character_id`= 2)) AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtSetFirstInYear''' % begDateYear.toString('yyyy-MM-dd'), '''COUNT( IF (DATE(Event.`execDate`) >= DATE('%s') AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtExecInYear''' % begDateYear.toString('yyyy-MM-dd'), '''COUNT( IF (DATE(Event.`setDate`) <= DATE('%s') AND (Diagnostic.`dispanser_id` = 2) AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtInLastYear''' % begDateYear.toString('yyyy-MM-dd'), '''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND (Diagnostic.`dispanser_id` = 1) AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtInThisYear''' % begDateYear.toString('yyyy-MM-dd'), ] else: select = ['''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtSetInYear''' % begDateYear.toString('yyyy-MM-dd'), '''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND ((Diagnostic.`character_id`= 1) OR (Diagnostic.`character_id`= 2)) AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtSetFirstInYear''' % begDateYear.toString('yyyy-MM-dd'), '''COUNT( IF (DATE(Event.`execDate`) >= DATE('%s') AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtExecInYear''' % begDateYear.toString('yyyy-MM-dd'), '''COUNT( IF (DATE(Event.`setDate`) <= DATE('%s') AND (Diagnostic.`dispanser_id` = 2) AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtInLastYear''' % begDateYear.toString('yyyy-MM-dd'), '''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND (Diagnostic.`dispanser_id` = 1) AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtInThisYear''' % begDateYear.toString('yyyy-MM-dd'), ] select.append(tableDiagnosis['MKB']) select.append(tableDiagnosis['dispanser_id']) return db.selectStmt(queryTable, select, where=cond,group='Diagnosis.MKB')