def getCond(params, additionalCondType=0): db = QtGui.qApp.db tableAccount = db.table('Account') tableAccountItem = db.table('Account_Item') tableAction = db.table('Action') tableEvent = db.table('Event') tablePerson = db.table('Person') tableVisit = db.table('Visit') cond = [tableAccountItem['reexposeItem_id'].isNull()] begDate = params.get('begDate', None) endDate = params.get('endDate', None) serviceDate = [ tableEvent['execDate'], tableVisit['date'], tableAction['endDate'] ][additionalCondType] addDateInRange(cond, serviceDate, begDate, endDate) if params.get('orgStructureId', None): cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(params['orgStructureId']))) else: cond.append( db.joinOr([ tablePerson['org_id'].eq(QtGui.qApp.currentOrgId()), tablePerson['org_id'].isNull() ])) eventTypeId = params.get('eventTypeId', None) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) return db.joinAnd(cond)
def selectData(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt = """ SELECT rbHealthGroup.code as `group`, COUNT(Event.id) as cnt FROM Event LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN Diagnostic AS D5 ON ( D5.event_id = Event.id AND D5.diagnosisType_id = (SELECT id FROM rbDiagnosisType WHERE code = '1') AND D5.deleted = 0 ) LEFT JOIN rbHealthGroup ON rbHealthGroup.id = D5.healthGroup_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE Event.deleted=0 AND %s GROUP BY `group` """ db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] addDateInRange(cond, tableEvent['execDate'], begDate, endDate) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) return db.query(stmt % (db.joinAnd(cond)))
def selectData(begDate, endDate, grouping): R1 = """ SELECT Client.rbInfoSource_id, rbI.name as rbInfoSource_name, COUNT(IFNULL(rbI.name,0)) as rbInfoSource_cnt FROM Client LEFT JOIN rbInfoSource AS rbI ON Client.rbInfoSource_id = rbI.id WHERE %s GROUP BY rbI.id ORDER BY rbInfoSource_name """ R2 = """ SELECT rbI.id as sourceID, rbI.name as sourceName, Client.id, Client.lastName, Client.firstName, Client.patrName FROM Client LEFT JOIN rbInfoSource AS rbI ON Client.rbInfoSource_id = rbI.id WHERE %s ORDER BY sourceName, Client.id """ stmt = R1 if grouping else R2 db = QtGui.qApp.db tableClient = db.table('Client') cond = [] addDateInRange(cond, tableClient['createDatetime'], begDate, endDate) return db.query(stmt % (db.joinAnd(cond)))
def selectData(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt = """ SELECT Event.execDate as date, Client.birthDate AS birthDate, Client.sex AS sex, (SELECT COUNT(DISTINCT Diagnosis.MKB) FROM Diagnostic LEFT JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id WHERE Diagnostic.event_id = Event.id AND Diagnostic.deleted=0 AND Diagnosis.MKB > 'A' AND Diagnosis.MKB < 'U' ) AS cnt FROM Event LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE Event.deleted=0 AND %s """ db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] addDateInRange(cond, tableEvent['execDate'], begDate, endDate) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) return db.query(stmt % (db.joinAnd(cond)))
def selectData(begDate, endDate, eventTypeId, personId, personProfession, ageFrom, ageTo, orgStructureId): stmt = u""" SELECT Event.id AS EventId, Client.id AS ClientId, Event.eventType_id AS EventTypeId, DATE(Event.setDate) AS EventSetDate, DATE(CheckSheet.begDate) AS CheckSheetDate, Event.pregnancyWeek AS PregnancyWeek, age(Client.birthDate, Event.setDate) AS clientAge %s FROM Event LEFT JOIN (SELECT * FROM Action WHERE actionType_id = 1298) AS CheckSheet ON Event.id = CheckSheet.event_id LEFT JOIN (SELECT * FROM Action WHERE actionType_id = 1272 or actionType_id =1298) AS Actions ON Event.id = Actions.event_id %s WHERE %s """ db = QtGui.qApp.db tableEvent = db.table('Event') tableClient = db.table('Client') tablePerson = db.table('Person') leftJoins = [] cond = [] cond.append(tableEvent['deleted'].eq(0)) cond.append(tableEvent['client_id'].isNotNull()) addDateInRange(cond, tableEvent['setDate'], begDate, endDate) if (personId or personProfession or orgStructureId): joinPerson = "LEFT JOIN %s ON %s" % (tablePerson.name(), tablePerson["id"].eq( tableEvent["execPerson_id"])) leftJoins.append(joinPerson) if personId: cond.append(tablePerson["id"].eq(personId)) if personProfession: cond.append(tablePerson["speciality_id"].eq(personProfession)) if orgStructureId: cond.append(tablePerson["orgStructure_id"].eq(orgStructureId)) if (ageFrom or ageTo): joinClient = "LEFT JOIN %s ON %s" % ( tableClient.name(), tableClient["id"].eq(tableEvent["client_id"])) leftJoins.append(joinClient) if ageFrom: cond.append("age(Client.birthDate, Event.setDate) >= %d" % ageFrom) if ageTo: cond.append("age(Client.birthDate, Event.setDate) <= %d" % ageTo) if eventTypeId: cond.append(tableEvent["eventType_id"].eq(eventTypeId)) sels_str, tbl_str = preRequest(props) joins = '\n'.join(leftJoins) + tbl_str return db.query(stmt % (sels_str, joins, db.joinAnd(cond)))
def selectData(begDate, endDate): stmt = "SELECT report_name FROM %s.Reports " % (Logger.getLoggerDbName()) db = QtGui.qApp.db tableReports = db.table(Logger.getLoggerDbName() + '.Reports') cond = [] addDateInRange(cond, tableReports['date'], begDate, endDate) return db.query(stmt + u"WHERE" + db.joinAnd(cond) + u";")
def selectSecondStageDataStarted(params): sex = params.get('sex', 0) ageFrom = params.get('ageFrom', 0) ageTo = params.get('ageTo', 150) begDate = params.get('begDate', None) endDate = params.get('endDate', None) stmt = ''' SELECT COUNT(DISTINCT Event.id) as started FROM Event INNER JOIN EventType ON EventType.id = Event.eventType_id AND EventType.deleted = 0 INNER JOIN rbEventKind ON EventType.eventKind_id = rbEventKind.id AND rbEventKind.code = '01' INNER JOIN rbResult ON Event.result_id = rbResult.id AND rbResult.code IN ('49', '89', '90', '91') INNER JOIN Client ON Client.id = Event.client_id and Client.deleted = 0 WHERE Event.deleted = 0 AND %s ''' db = QtGui.qApp.db tableEvent = db.table('Event') tableClient = db.table('Client') cond = [] addDateInRange(cond, tableEvent['setDate'], begDate, endDate) if sex: cond.append(tableClient['sex'].eq(sex)) if ageFrom <= ageTo: cond.append( 'Event.setDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' % ageFrom) cond.append( 'Event.setDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)' % (ageTo + 1)) colsCond = [] addDateInRange(colsCond, tableEvent['execDate'], begDate, endDate) return db.query(stmt % db.joinAnd(cond))
def selectData(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt = """ SELECT Organisation.OKVED AS OKVED, Organisation.shortName AS shortName, COUNT(Diagnostic.id) AS cntEvents, SUM( %s ) AS cntExecuted, SUM( isEventPayed(Diagnostic.event_id) ) as cntPayed, SUM( rbHealthGroup.code = '1' ) as cntHG1, SUM( rbHealthGroup.code = '2' ) as cntHG2, SUM( rbHealthGroup.code = '3' ) as cntHG3, SUM( rbHealthGroup.code = '4' ) as cntHG4, SUM( rbHealthGroup.code = '5' ) as cntHG5 FROM Diagnostic LEFT JOIN Event ON Event.id = Diagnostic.event_id LEFT JOIN rbDiagnosisType ON rbDiagnosisType.id = Diagnostic.diagnosisType_id LEFT JOIN Person ON Person.id = Diagnostic.person_id LEFT JOIN rbSpeciality ON rbSpeciality.id = Person.speciality_id LEFT JOIN rbHealthGroup ON rbHealthGroup.id = Diagnostic.healthGroup_id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientWork ON (ClientWork.client_id = Client.id AND ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) ) LEFT JOIN Organisation ON Organisation.id = ClientWork.org_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE Event.deleted = 0 AND Diagnostic.deleted = 0 AND %s GROUP BY ClientWork.org_id ORDER BY Organisation.OKVED, Organisation.shortName """ db = QtGui.qApp.db tableEvent = db.table('Event') setDate = tableEvent['setDate'] execDate = tableEvent['execDate'] tableDiagnosisType = db.table('rbDiagnosisType') cond = [] cond.append(tableDiagnosisType['code'].eq('1')) dateCond = [] dateCond.append(db.joinAnd([setDate.le(endDate), execDate.isNull()])) dateCond.append(db.joinAnd([execDate.ge(begDate), execDate.le(endDate)])) dateCond.append(db.joinAnd([setDate.ge(begDate), execDate.le(endDate)])) cond.append(db.joinOr(dateCond)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) s = stmt % (execDate.between(begDate, endDate), db.joinAnd(cond)) return db.query(stmt % (execDate.between(begDate, endDate), db.joinAnd(cond)))
def selectFirstStageData(params): sex = params.get('sex', 0) ageFrom = params.get('ageFrom', 0) ageTo = params.get('ageTo', 150) begDate = params.get('begDate', None) endDate = params.get('endDate', None) db = QtGui.qApp.db diagnosisTypes = db.getIdList('rbDiagnosisType', 'id', 'code IN (\'1\', \'2\')') eventTypeId = params.get('eventTypeId', None) stmt = u''' SELECT COUNT(DISTINCT Event.id) as total, COUNT(DISTINCT IF(rbSocStatusClass.code = 1, Event.id, NULL)) as veterans, COUNT(DISTINCT IF(Visit.scene_id = 4, Event.id, NULL)) as mobile, COUNT(DISTINCT IF(Diagnostic.healthGroup_id = 1, Event.id, NULL)) as hg1, COUNT(DISTINCT IF(Diagnostic.healthGroup_id IN (2, 3, 4, 5), Event.id, NULL)) as hg2, COUNT(DISTINCT IF(Diagnostic.healthGroup_id = 6, Event.id, NULL)) as hg3, COUNT(DISTINCT IF((Event.payStatus & (3 << (rbFinance.code * 2))) = (0x55555555 & (3 << (rbFinance.code * 2))) OR isEventPayed(Event.id), Event.id, NULL)) as exposed, COUNT(DISTINCT IF(isEventPayed(Event.id), Event.id, NULL)) as payed FROM Event INNER JOIN EventType ON EventType.id = Event.eventType_id and EventType.deleted = 0 INNER JOIN rbEventKind ON EventType.eventKind_id = rbEventKind.id AND rbEventKind.code IN ('01', '04') INNER JOIN Client ON Client.id = Event.client_id and Client.deleted = 0 LEFT JOIN Diagnostic ON Diagnostic.event_id = Event.id and Diagnostic.deleted = 0 AND %s LEFT JOIN Visit ON Visit.event_id = Event.id and Visit.deleted = 0 LEFT JOIN Contract ON Contract.id = Event.contract_id and Contract.deleted = 0 LEFT JOIN rbFinance ON rbFinance.id = Contract.finance_id LEFT JOIN ClientSocStatus ON ClientSocStatus.client_id = Client.id and ClientSocStatus.deleted = 0 LEFT JOIN rbSocStatusClass ON rbSocStatusClass.id = ClientSocStatus.socStatusClass_id WHERE Event.deleted = 0 AND %s ''' tableEvent = db.table('Event') tableClient = db.table('Client') cond = [] addDateInRange( cond, tableEvent['execDate'] if not params.get('countUnfinished', False) else tableEvent['setDate'], begDate, endDate) if sex: cond.append(tableClient['sex'].eq(sex)) if ageFrom <= ageTo: cond.append( 'Event.setDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' % ageFrom) cond.append( 'Event.setDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)' % (ageTo + 1)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) return db.query( stmt % ((u'Diagnostic.diagnosisType_id IN (%s)' % (', '.join(str(d) for d in diagnosisTypes))) if diagnosisTypes else '1', db.joinAnd(cond)))
def selectData(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt = """ SELECT Organisation.OKVED as OKVED, rbDispanser.code in ('2', '6') AS dispanser, rbDiseaseStage.code as stage, rbHealthGroup.code as `group`, COUNT(Event.id) as cnt FROM Event LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientWork ON ( ClientWork.client_id = Client.id AND ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) ) LEFT JOIN Organisation ON Organisation.id = ClientWork.org_id LEFT JOIN Diagnostic AS D2 ON ( D2.event_id = Event.id AND D2.id = ( SELECT MIN(D3.id) FROM Diagnostic AS D3 LEFT JOIN rbDispanser ON rbDispanser.id = D3.dispanser_id LEFT JOIN rbDiseaseStage ON rbDiseaseStage.id = D3.stage_id WHERE D3.event_id = Event.id AND D3.deleted = 0 AND rbDispanser.code in ('2', '6') AND IF(rbDiseaseStage.code IS NULL, 0,rbDiseaseStage.code) = ( SELECT MAX(IF(rbDiseaseStage.code IS NULL, 0,rbDiseaseStage.code)) FROM Diagnostic AS D4 LEFT JOIN rbDispanser ON rbDispanser.id = D4.dispanser_id LEFT JOIN rbDiseaseStage ON rbDiseaseStage.id = D4.stage_id WHERE D4.event_id = Event.id AND D4.deleted=0 AND rbDispanser.code in ('2', '6')))) LEFT JOIN rbDispanser ON rbDispanser.id = D2.dispanser_id LEFT JOIN rbDiseaseStage ON rbDiseaseStage.id = D2.stage_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = D2.character_id LEFT JOIN Diagnostic AS D5 ON ( D5.event_id = Event.id AND D5.diagnosisType_id = (SELECT id FROM rbDiagnosisType WHERE code = '1') ) LEFT JOIN rbHealthGroup ON rbHealthGroup.id = D5.healthGroup_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE Event.deleted=0 AND %s GROUP BY OKVED, dispanser, stage, `group` """ db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] addDateInRange(cond, tableEvent['execDate'], begDate, endDate) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) return db.query(stmt % (db.joinAnd(cond)))
def selectDiagnostics(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt = """ SELECT Event.client_id as client_id, Event.isPrimary as isPrimary, Diagnosis.mkb as mkb, IF(rbDiseaseCharacter.code='3' OR rbDiseaseCharacter.code='4', 1, 0) AS P4, IF(rbDiseaseCharacter.code='2' OR (rbDiseaseCharacter.code='1' AND rbDiseaseStage.code='1'), 1, 0 ) AS P5, IF(rbDiseaseCharacter.code='2' AND rbDiseaseStage.code='2', 1, 0 ) AS P6, IF(Diagnostic.hospital>1, 1, 0) AS P8, IF(Diagnostic.hospital>1 AND rbHealthGroup.code = 5, 1, 0) AS P9, rbDispanser.observed AS P10, rbDispanser.code in ('2', '6') AS P11, Organisation.OKVED as OKVED, rbOKFS.ownership as ownership FROM Diagnostic LEFT JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id LEFT JOIN Event ON Event.id = Diagnostic.event_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnostic.character_id LEFT JOIN rbDiseaseStage ON rbDiseaseStage.id = Diagnostic.stage_id LEFT JOIN rbHealthGroup ON rbHealthGroup.id = Diagnostic.healthGroup_id LEFT JOIN rbDispanser ON rbDispanser.id = Diagnostic.dispanser_id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientWork ON ( ClientWork.client_id = Client.id AND ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) ) LEFT JOIN Organisation ON Organisation.id = ClientWork.org_id LEFT JOIN rbOKFS ON rbOKFS.id = Organisation.OKFS_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE %s ORDER BY Event.client_id, Diagnosis.mkb, Diagnostic.diagnosisType_id, Diagnostic.id """ db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] cond.append(tableEvent['execDate'].ge(begDate)) cond.append(tableEvent['execDate'].le(endDate)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) return db.query(stmt % (db.joinAnd(cond)))
def selectEventsData(params): sex = params.get('sex', 0) ageFrom = params.get('ageFrom', 0) ageTo = params.get('ageTo', 150) begDate = params.get('begDate', None) endDate = params.get('endDate', None) terType = params.get('terType', None) eventTypeId = params.get('eventTypeId', None) db = QtGui.qApp.db tableEvent = db.table('Event') tableClient = db.table('Client') tableEventType = db.table('EventType') tableEventKind = db.table('rbEventKind') tableAccountItem = db.table('Account_Item') tableClientSocStatus = db.table('ClientSocStatus') tableSocStatusType = db.table('rbSocStatusType') tableRBResult = db.table('rbResult') tableRBHealthGroup = db.table('rbHealthGroup') tableDiagnostic = db.table('Diagnostic') cols = ['rbEventKind.code as eventKindCode', 'Account_Item.sum', 'rbResult.code as resultCode', 'rbHealthGroup.code as HGCode', 'Client.id as client_id', 'Event.id as event_id', 'Account_Item.id as accountItem_id', 'rbSocStatusType.name as socStatusName', 'Client.sex'] queryTable = tableClient.leftJoin(tableEvent, db.joinAnd([tableEvent['client_id'].eq(tableClient['id']), tableEvent['deleted'].eq(0)])) queryTable = queryTable.leftJoin(tableEventType, db.joinAnd([tableEvent['eventType_id'].eq(tableEventType['id']), tableEventType['deleted'].eq(0)])) queryTable = queryTable.leftJoin(tableEventKind, db.joinAnd([tableEventType['eventKind_id'].eq(tableEventKind['id']), tableEventKind['code'].inlist(['01', '02', '04'])])) queryTable = queryTable.leftJoin(tableDiagnostic, db.joinAnd([tableDiagnostic['event_id'].eq(tableEvent['id']), tableDiagnostic['deleted'].eq(0)])) queryTable = queryTable.leftJoin(tableRBResult, tableRBResult['id'].eq(tableEvent['result_id'])) queryTable = queryTable.leftJoin(tableRBHealthGroup, tableRBHealthGroup['id'].eq(tableDiagnostic['healthGroup_id'])) queryTable = queryTable.leftJoin(tableAccountItem, tableAccountItem['event_id'].eq(tableEvent['id'])) queryTable = queryTable.leftJoin(tableClientSocStatus, db.joinAnd([tableClientSocStatus['client_id'].eq(tableClient['id']), tableClientSocStatus['deleted'].eq(0)])) queryTable = queryTable.leftJoin(tableSocStatusType, tableSocStatusType['id'].eq(tableClientSocStatus['socStatusType_id'])) cond = ['isEventPayed(Event.id)'] if not params.get('countUnfinished', False): addDateInRange(cond, tableEvent['execDate'], begDate, endDate) else: addDateInRange(cond, tableEvent['setDate'], begDate, endDate) if sex: cond.append(tableClient['sex'].eq(sex)) if ageFrom <= ageTo: cond.append('Event.setDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)'%ageFrom) cond.append('Event.setDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)'%(ageTo+1)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) stmt = db.selectStmt(queryTable, cols, cond) return db.query(stmt)
def countEventsByOrgStructure(begDate, endDate, orgStructureId): db = QtGui.qApp.db Event = db.table('Event') Person = db.table('Person') queryTable = Event.leftJoin(Person, Person['id'].eq(Event['execPerson_id'])) cond = [ Event['deleted'].eq(0), Person['orgStructure_id'].eq(orgStructureId) ] addDateInRange(cond, Event['setDate'], begDate, endDate) record = db.getRecordEx(queryTable, 'count(*) AS cnt', cond) return forceInt(record.value('cnt')) if not record is None else 0
def selectDataWithDetails(begDate, endDate): stmt = """ SELECT report_name, date, lastName, firstName, patrName, lpu FROM {dbName}.Reports INNER JOIN {dbName}.Login ON {dbName}.Login.id = {dbName}.Reports.login_id INNER JOIN Person ON Person.id = {dbName}.Login.person_id """.format(dbName=Logger.getLoggerDbName()) db = QtGui.qApp.db tableReports = db.table(Logger.getLoggerDbName() + '.Reports') cond = [] addDateInRange(cond, tableReports['date'], begDate, endDate) return db.query(stmt + u"WHERE" + db.joinAnd(cond) + u"ORDER BY lastName, date;")
def selectDiagnostics(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate, diagnosisType, orgStructureAttachTypeId): stmt=""" SELECT Event.id AS eventId, Event.client_id, Diagnosis.MKB, Client.sex, IF(age(Client.birthDate, Event.execDate) = 14, 1, 0) AS ageTeenager, IF(rbDiseaseCharacter.code IN ('1', '2'), 1, 0) AS isPrimary, IF(Event.execDate IS NOT NULL, 1, 0) AS eventClosedPayed, IF((Account_Item.date >= DATE(Event.setDate)) AND (Account_Item.date < DATE(Event.execDate)), 1, 0) AS payedEvent FROM Diagnostic LEFT JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id LEFT JOIN Event ON Event.id = Diagnostic.event_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnostic.character_id LEFT JOIN rbDiagnosisType ON rbDiagnosisType.id = Diagnostic.diagnosisType_id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN Account_Item ON (Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL)) %s WHERE %s ORDER BY Event.client_id, Diagnosis.mkb, Diagnostic.diagnosisType_id, Diagnostic.id """ db = QtGui.qApp.db tableEvent = db.table('Event') tableRBDiagnosisType = db.table('rbDiagnosisType') cond = [] cond.append(db.joinOr([tableEvent['execDate'].isNull(), tableEvent['execDate'].ge(begDate)])) cond.append(db.joinOr([tableEvent['execDate'].isNull(), tableEvent['execDate'].le(endDate)])) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if not diagnosisType: cond.append('''rbDiagnosisType.code = '1' OR rbDiagnosisType.code = '2' ''') if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) addisionalFrom = '' 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 % (addisionalFrom, db.joinAnd(cond)))
def selectData(begDate, endDate, eventTypeId, PermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt = """ SELECT distinct lpu_OKATO.NAME as lpu_region, Event.client_id as clientId, lpu.infisCode, ClientAttach.LPU_id FROM Event JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientAttach ON ClientAttach.id=( select max(ca.id) from ClientAttach as ca join rbAttachType on ca.attachType_id=rbAttachType.id where ca.client_id=Event.client_id and rbAttachType.temporary=0 and rbAttachType.outcome=0) LEFT JOIN Organisation as lpu on lpu.id = ClientAttach.LPU_id LEFT JOIN kladr.OKATO as lpu_OKATO on lpu_OKATO.CODE=left(lpu.OKATO, 5) LEFT JOIN Account_Item ON ( Account_Item.event_id = Event.id AND Account_Item.deleted = 0 AND Account_Item.id = ( SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL)) WHERE Event.deleted = 0 AND %s ORDER BY Client.lastName, Client.firstName, Client.patrName """ db = QtGui.qApp.db tableEvent = db.table('Event') setDate = tableEvent['setDate'] execDate = tableEvent['execDate'] cond = [] dateCond = [] dateCond.append(db.joinAnd([setDate.le(endDate), execDate.isNull()])) dateCond.append(db.joinAnd([execDate.ge(begDate), execDate.le(endDate)])) dateCond.append(db.joinAnd([setDate.ge(begDate), execDate.le(endDate)])) cond.append(db.joinOr(dateCond)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if PermanentAttach and PermanentAttach > 0: cond.append('ClientAttach.LPU_id=%d' % PermanentAttach) elif PermanentAttach == -1: cond.append('ClientAttach.id is null') if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) my_org_id = forceInt(getVal(QtGui.qApp.preferences.appPrefs, 'orgId', None)) cond.append(tableEvent['org_id'].eq(my_org_id)) stmt = stmt % db.joinAnd(cond) return db.query(stmt)
def selectData(begDate, endDate, byPeriod, doctype, tempInvalidReasonId, onlyClosed, orgStructureId, personId, insuranceOfficeMark): stmt = """ SELECT Client.birthDate, Client.sex, TempInvalid.caseBegDate, TempInvalid.endDate, TempInvalid.sex AS tsex, TempInvalid.age AS tage, DATEDIFF(TempInvalid.endDate, TempInvalid.caseBegDate)+1 AS duration, Diagnosis.MKB, rbTempInvalidReason.code AS reasonCode, rbTempInvalidReason.grouping AS reasonGroup FROM TempInvalid LEFT JOIN TempInvalid AS NextTempInvalid ON TempInvalid.id = NextTempInvalid.prev_id LEFT JOIN Diagnosis ON Diagnosis.id = TempInvalid.diagnosis_id LEFT JOIN Person ON Person.id = TempInvalid.person_id LEFT JOIN rbTempInvalidReason ON rbTempInvalidReason.id = TempInvalid.tempInvalidReason_id LEFT JOIN Client ON Client.id = TempInvalid.client_id WHERE NextTempInvalid.id IS NULL AND %s """ db = QtGui.qApp.db table = db.table('TempInvalid') cond = [] if doctype: cond.append(table['doctype_id'].eq(doctype)) else: cond.append(table['type'].eq(0)) cond.append(table['deleted'].eq(0)) if tempInvalidReasonId: cond.append(table['tempInvalidReason_id'].eq(tempInvalidReasonId)) if byPeriod: cond.append(table['caseBegDate'].le(endDate)) cond.append(table['endDate'].ge(begDate)) else: addDateInRange(cond, table['endDate'], begDate, endDate) if onlyClosed: cond.append(table['closed'].eq(1)) if orgStructureId: tablePerson = db.table('Person') cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) if personId: cond.append(table['person_id'].eq(personId)) if insuranceOfficeMark in [1, 2]: cond.append(table['insuranceOfficeMark'].eq(insuranceOfficeMark - 1)) return db.query(stmt % (db.joinAnd(cond)))
def selectData(begDate, endDate, orgStructureId, eventTypeId): db = QtGui.qApp.db Action = db.table('Action') ActionType = db.table('ActionType') Event = db.table('Event') EventType = db.table('EventType') OrgStructure = db.table('OrgStructure') Person = db.table('Person') queryTable = Action.leftJoin(ActionType, ActionType['id'].eq(Action['actionType_id'])) queryTable = queryTable.leftJoin(Event, Event['id'].eq(Action['event_id'])) queryTable = queryTable.leftJoin(Person, Person['id'].eq(Action['setPerson_id'])) queryTable = queryTable.leftJoin( OrgStructure, OrgStructure['id'].eq(Person['orgStructure_id'])) cols = [ Event['id'].alias('eventId'), Event['setDate'].alias('eventSetDate'), Action['begDate'].alias('actionLeavedBegDate'), "concat_ws(' ', %s, %s, %s) AS personName" % (Person['lastName'].name(), Person['firstName'].name(), Person['patrName'].name()), OrgStructure['name'].alias('orgStructureName') ] cond = [ ActionType['flatCode'].eq('leaved'), ActionType['code'].like(u'ф27'), Action['deleted'].eq(0), ActionType['deleted'].eq(0), Event['deleted'].eq(0), OrgStructure['deleted'].eq(0), Person['deleted'].eq(0) ] addDateInRange(cond, Action['endDate'], begDate, endDate) if not orgStructureId is None: cond.append(OrgStructure['id'].inlist( getOrgStructureDescendants(orgStructureId))) if not eventTypeId is None: cond.append(Event['eventType_id'].eq(eventTypeId)) order = [ 'DATE(%s)' % Event['setDate'].name(), 'DATE(%s)' % Action['begDate'].name(), Person['lastName'], Person['firstName'], Person['patrName'] ] stmt = db.selectStmt(queryTable, cols, cond, order=order) return db.query(stmt)
def selectData(begDate, endDate, eventTypeId, onlyPayedEvents, begPayDate, endPayDate, workOrgId, onlyPermanentAttach): db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] if begDate: cond.append( db.joinOr([ tableEvent['execDate'].ge(begDate), tableEvent['execDate'].isNull() ])) if endDate: cond.append(tableEvent['setDate'].le(endDate)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) if workOrgId: cond.append('ClientWork.org_id=%d' % (workOrgId)) if onlyPermanentAttach: my_org_id = forceInt( getVal(QtGui.qApp.preferences.appPrefs, 'orgId', None)) cond.append( 'EXISTS (SELECT * FROM ClientAttach WHERE ClientAttach.client_id=Client.id and ClientAttach.attachType_id=2 and ClientAttach.LPU_id=%d)' % my_org_id) condStr = db.joinAnd(cond) stmt = """ SELECT Client.id as clientId, Event.id as eventId, EventType.name AS eventTypeName, Event.execDate, Diagnostic.healthGroup_id, Diagnosis.MKB FROM Event LEFT JOIN EventType ON EventType.id = Event.eventType_id LEFT JOIN Client ON Client.id=Event.client_id LEFT JOIN Diagnostic ON Diagnostic.event_id=Event.id AND Diagnostic.diagnosisType_id=1 LEFT JOIN Diagnosis ON Diagnosis.id=getEventDiagnosis(Event.id) LEFT JOIN ClientWork ON ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id=Client.id) LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE Event.deleted = 0 AND Diagnostic.deleted = 0 AND Diagnosis.deleted = 0 AND %s ORDER BY Client.lastName, Client.firstName, Client.patrName """ % (condStr) return db.query(stmt)
def countByGroups(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt = """ SELECT Organisation.OKVED as OKVED, rbHealthGroup.code as `group`, IF ( rbDiseaseCharacter.code='2' OR (rbDiseaseCharacter.code='1' AND rbDiseaseStage.code='1'), 1, 0 ) AS DOD, Event.isPrimary as isPrimary, COUNT(Event.id) as cnt FROM Event LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientWork ON ( ClientWork.client_id = Client.id AND ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) ) LEFT JOIN Organisation ON Organisation.id = ClientWork.org_id LEFT JOIN Diagnostic ON ( Diagnostic.event_id = Event.id AND Diagnostic.diagnosisType_id = (SELECT id FROM rbDiagnosisType WHERE code = '1') ) LEFT JOIN rbHealthGroup ON rbHealthGroup.id = Diagnostic.healthGroup_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnostic.character_id LEFT JOIN rbDiseaseStage ON rbDiseaseStage.id = Diagnostic.stage_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE %s GROUP BY OKVED, `group`, DOD, Event.isPrimary """ db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] cond.append(tableEvent['execDate'].ge(begDate)) cond.append(tableEvent['execDate'].le(endDate)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) return db.query(stmt % (db.joinAnd(cond)))
def selectData(begDate, endDate, personId, orgStructureId, eventTypeId): stmtActs = u""" SELECT COUNT(*) AS cnt, rbSocStatusType.name AS socStatus, ActionType.name AS aType FROM Action LEFT JOIN ActionType ON ActionType.id = Action.actionType_id LEFT JOIN Event ON Action.event_id = Event.id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientSocStatus ON Client.id = ClientSocStatus.client_id LEFT JOIN rbSocStatusType ON ClientSocStatus.socStatusType_id = rbSocStatusType.id LEFT JOIN Person ON Event.execPerson_id = Person.id WHERE %s GROUP BY rbSocStatusType.id, ActionType.id ORDER BY rbSocStatusType.name """ stmtViss = u""" SELECT COUNT(*) AS cnt, rbSocStatusType.name AS socStatus, rbSpeciality.name AS speciality FROM Visit LEFT JOIN Person ON Person.id = Visit.person_id LEFT JOIN rbSpeciality ON rbSpeciality.id = Person.speciality_id LEFT JOIN Event ON Visit.event_id = Event.id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientSocStatus ON Client.id = ClientSocStatus.client_id LEFT JOIN rbSocStatusType ON ClientSocStatus.socStatusType_id = rbSocStatusType.id WHERE %s GROUP BY rbSocStatusType.id, rbSpeciality.name ORDER BY rbSocStatusType.name """ db = QtGui.qApp.db tableEvent = db.table('Event') tablePerson = db.table('Person') cond = [tableEvent['deleted'].eq(0), tableEvent['client_id'].isNotNull()] addDateInRange(cond, tableEvent['setDate'], begDate, endDate) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if personId or orgStructureId: if personId: cond.append(tablePerson["id"].eq(personId)) if orgStructureId: cond.append(tablePerson["orgStructure_id"].eq(orgStructureId)) queryActs = db.query(stmtActs % (db.joinAnd(cond))) queryViss = db.query(stmtViss % (db.joinAnd(cond))) return queryActs, queryViss
def selectData(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt=""" SELECT Event.client_id AS client_id, Diagnosis.mkb AS mkb, IF((rbDiseaseCharacter.code='1' AND rbDiseaseStage.code='1') OR rbDiseaseCharacter.code='2', 1, 0) AS R2a, IF(rbDiseaseCharacter.code IS NOT NULL, 1, 0) AS R2b, IF(rbDiagnosisType.code='1', 1, 0) AS final, IF(rbHealthGroup.code = '3', 1, 0) AS R3c, IF(rbDispanser.observed OR rbDiagnosticResult.code='32', 1, 0) AS R4c, IF(rbHealthGroup.code > '3', 1, 0) AS R5c, IF(Diagnostic.hospital>1, 1, 0) AS R6c FROM Diagnostic LEFT JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id LEFT JOIN Event ON Event.id = Diagnostic.event_id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN rbDiagnosisType ON rbDiagnosisType.id = Diagnostic.diagnosisType_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnostic.character_id LEFT JOIN rbDiseaseStage ON rbDiseaseStage.id = Diagnostic.stage_id LEFT JOIN rbHealthGroup ON rbHealthGroup.id = Diagnostic.healthGroup_id LEFT JOIN rbDispanser ON rbDispanser.id = Diagnostic.dispanser_id LEFT JOIN rbDiagnosticResult ON rbDiagnosticResult.id = Diagnostic.result_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.deleted=0 AND AI.event_id = Event.id AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE Diagnostic.deleted=0 AND Event.deleted=0 AND %s ORDER BY Event.client_id, Diagnosis.mkb, Diagnostic.diagnosisType_id, Diagnostic.id """ db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] addDateInRange(cond, tableEvent['execDate'], begDate, endDate) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) return db.query(stmt % (db.joinAnd(cond)))
def countDispensariable(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt = """ SELECT Organisation.OKVED as OKVED, COUNT(Event.id) as cnt FROM Event LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientWork ON ( ClientWork.client_id = Client.id AND ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) ) LEFT JOIN Organisation ON Organisation.id = ClientWork.org_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE IF( execDate IS NULL, STR_TO_DATE(CONCAT(YEAR(setDate), '-12-31'), '%%Y-%%m-%%d'), DATE(execDate) ) >= %s AND setDate <= %s %s GROUP BY Organisation.OKVED """ db = QtGui.qApp.db cond = [] if cond: tableEvent = db.table('Event') cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) cond = db.joinAnd(cond) if cond: cond = ' AND ' + cond # print stmt % ( db.formatDate(begDate), db.formatDate(endDate), cond) return db.query(stmt % (db.formatDate(begDate), db.formatDate(endDate), cond))
def selectData(begDate, endDate, place, cause, foundBy, foundation): stmt=""" SELECT COUNT(*) AS cnt, (Event.isPrimary = 1 ) AS autopsy, (IF(FDiagnosis.MKB IS NULL, PDiagnosis.MKB, FDiagnosis.MKB) BETWEEN 'C00' AND 'C97.999') AS oncology, (IF(FDiagnosis.MKB IS NULL, PDiagnosis.MKB, FDiagnosis.MKB) BETWEEN 'I60' AND 'I64.999') AS cardiology, Event.result_id AS result_id FROM Event LEFT JOIN EventType ON EventType.id = Event.eventType_id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientAddress ON ClientAddress.client_id = Client.id AND ClientAddress.id = (SELECT MAX(id) FROM ClientAddress AS CA WHERE CA.Type=0 and CA.client_id = Client.id) LEFT JOIN Diagnostic AS PDiagnostic ON PDiagnostic.event_id = Event.id LEFT JOIN rbDiagnosisType AS PDiagnosisType ON PDiagnosisType.id = PDiagnostic.diagnosisType_id LEFT JOIN Diagnosis AS PDiagnosis ON PDiagnosis.id = PDiagnostic.diagnosis_id LEFT JOIN Diagnostic AS FDiagnostic ON FDiagnostic.event_id = Event.id LEFT JOIN rbDiagnosisType AS FDiagnosisType ON FDiagnosisType.id = FDiagnostic.diagnosisType_id LEFT JOIN Diagnosis AS FDiagnosis ON FDiagnosis.id = FDiagnostic.diagnosis_id LEFT JOIN rbResult ON rbResult.id = Event.result_id WHERE EventType.code = '15' AND PDiagnosisType.code = '8' AND FDiagnosisType.code = '4' AND %s GROUP BY autopsy, oncology, cardiology, result_id """ db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] cond.append(tableEvent['deleted'].eq(0)) addDateInRange(cond, tableEvent['setDate'], begDate, endDate) if place or cause or foundBy or foundation: addCondForDeathCurcumstance(cond, tableEvent, place, cause, foundBy, foundation) return db.query(stmt % (db.joinAnd(cond)))
def selectDiagnostics(begDate, endDate, eventTypeId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt = ''' SELECT Event.client_id AS client_id, Diagnosis.mkb AS mkb FROM Diagnostic LEFT JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id LEFT JOIN Event ON Event.id = Diagnostic.event_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnostic.character_id LEFT JOIN rbDiseaseStage ON rbDiseaseStage.id = Diagnostic.stage_id LEFT JOIN rbHealthGroup ON rbHealthGroup.id = Diagnostic.healthGroup_id LEFT JOIN rbDispanser ON rbDispanser.id = Diagnostic.dispanser_id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.event_id = Event.id AND AI.deleted=0 AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE rbDiseaseCharacter.code IN ('1', '2') AND %s GROUP BY Event.client_id, Diagnosis.mkb ''' db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] cond.append(tableEvent['execDate'].ge(begDate)) cond.append(tableEvent['execDate'].le(endDate)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) return db.query(stmt % (db.joinAnd(cond)))
def selectData(begDate, endDate, eventTypeId, stageId, onlyPermanentAttach, onlyPayedEvents, begPayDate, endPayDate): stmt=""" SELECT Event.client_id AS client_id, Diagnosis.mkb AS mkb, ((rbDiseaseCharacter.code='1' AND rbDiseaseStage.code='1') OR rbDiseaseCharacter.code='2') AS P1, (%s) AS P2 FROM Diagnostic LEFT JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id LEFT JOIN Event ON Event.id = Diagnostic.event_id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnostic.character_id LEFT JOIN rbDiseaseStage ON rbDiseaseStage.id = Diagnostic.stage_id LEFT JOIN Account_Item ON ( Account_Item.id = (SELECT max(AI.id) FROM Account_Item AS AI WHERE AI.deleted=0 AND AI.event_id = Event.id AND AI.date IS NOT NULL AND AI.refuseType_id IS NULL AND AI.reexposeItem_id IS NULL AND AI.visit_id IS NULL AND AI.action_id IS NULL) ) WHERE Diagnosis.deleted=0 AND Event.deleted=0 AND %s ORDER BY Event.client_id, Diagnosis.mkb, Diagnostic.diagnosisType_id, Diagnostic.id """ db = QtGui.qApp.db tableEvent = db.table('Event') cond = [] addDateInRange(cond, tableEvent['execDate'], begDate, endDate) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if onlyPermanentAttach: cond.append(havePermanentAttach(endDate)) if onlyPayedEvents: cond.append('isEventPayed(Event.id)') tableAccountItem = db.table('Account_Item') addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate) stageCond = ('Diagnostic.stage_id=%s'%stageId) if stageId else 'Diagnostic.stage_id IS NULL' return db.query(stmt % (stageCond, db.joinAnd(cond)))
def selectData(params): sex = params.get('sex', 0) ageFrom = params.get('ageFrom', 0) ageTo = params.get('ageTo', 150) begDate = params.get('begDate', None) endDate = params.get('endDate', None) stmt = u''' SELECT Client.sex, Event.id as event_id, rbEventKind.code, Account_Item.sum, rbSocStatusType.code as socStatusCode FROM Event INNER JOIN EventType ON EventType.id = Event.eventType_id AND EventType.deleted = 0 INNER JOIN rbEventKind ON EventType.eventKind_id = rbEventKind.id AND rbEventKind.code IN ('01', '02', '04') INNER JOIN Client ON Client.id = Event.client_id AND Client.deleted = 0 LEFT JOIN ClientSocStatus ON ClientSocStatus.client_id = Client.id AND ClientSocStatus.deleted = 0 LEFT JOIN rbSocStatusType ON rbSocStatusType.id = ClientSocStatus.socStatusType_id LEFT JOIN Account_Item ON Account_Item.event_id = Event.id AND Account_Item.deleted = 0 WHERE isEventPayed(Event.id) and Event.deleted = 0 AND %s ''' db = QtGui.qApp.db tableEvent = db.table('Event') tableClient = db.table('Client') cond = [] if not params.get('countUnfinished', False): addDateInRange(cond, tableEvent['execDate'], begDate, endDate) else: addDateInRange(cond, tableEvent['execDate'], begDate, endDate) if sex: cond.append(tableClient['sex'].eq(sex)) if ageFrom <= ageTo: cond.append( 'Event.setDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' % ageFrom) cond.append( 'Event.setDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)' % (ageTo + 1)) return db.query(stmt % db.joinAnd(cond))
def getOperations(begDate, endDate, orgStructureId): db = QtGui.qApp.db Action = db.table('Action') ActionType = db.table('ActionType') Person = db.table('Person') queryTable = Action.innerJoin(ActionType, ActionType['id'].eq(Action['actionType_id'])) queryTable = queryTable.innerJoin(Person, Person['id'].eq(Action['person_id'])) isOperation = ActionType['serviceType'].eq(4) isCoronarography = ActionType['name'].eq(u'Коронарография') isUrgent = Action['isUrgent'].eq(1) cols = [ u'count(if({0}, {1}, NULL)) AS operationTotal'.format(isOperation, Action['id']), u'count(if({0}, {1}, NULL)) AS operationUrgent'.format(db.joinAnd([isOperation, isUrgent]), Action['id']), u'count(if({0}, {1}, NULL)) AS coronaryTotal'.format(isCoronarography, Action['id']), u'count(if({0}, {1}, NULL)) AS coronaryUrgent'.format(db.joinAnd([isCoronarography, isUrgent]), Action['id']) ] cond = [ Action['deleted'].eq(0), Person['orgStructure_id'].eq(orgStructureId) ] addDateInRange(cond, Action['begDate'], begDate, endDate) record = db.getRecordEx(queryTable, cols, cond) if not record is None: operationTotal = forceInt(record.value('operationTotal')) operationUrgent = forceInt(record.value('operationUrgent')) coronaryTotal = forceInt(record.value('coronaryTotal')) coronaryUrgent = forceInt(record.value('coronaryUrgent')) return operationTotal, operationUrgent, coronaryTotal, coronaryUrgent return 0, 0, 0, 0
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))))