def select_data(date, age_start, age_end, soc_class, soc_type, org_structure, address_street, address_house): db = QtGui.qApp.db cond = [ 'c.birthDate BETWEEN \'%s\' AND \'%s\'' % (date.addYears(-age_end - 1).toString('yyyy-MM-dd'), date.addYears(-age_start).toString('yyyy-MM-dd')) ] if soc_type: cond.append('css.socStatusType_id=%d' % soc_type) elif soc_class: cond.append('css.socStatusClass_id=%d' % soc_class) if org_structure: cond.append('osa.master_id IN (\'%s\')' % '\', \''.join( (str(x) for x in getOrgStructureDescendants(org_structure)))) else: cond.append('osa.master_id IN (\'%s\')' % '\', \''.join( str(getOrgStructureDescendants( QtGui.qApp.currentOrgStructureId())))) if address_house: cond.append('ah.id = \'%d\'' % address_house) elif address_street: cond.append('ah.KLADRStreetCode = \'%s\'' % address_street) stmt = STMT.format(where=db.joinAnd(cond)) return db.query(stmt)
def countData(self, data, params): orgStructureIndex = params.get('orgStructure', 0) orgStructures = [ COrgStructureModel.getOrgStructureCode(orgStructureIndex) ] if orgStructureIndex else COrgStructureModel.list[1:] result = [(COrgStructureModel.idMap[code], [code] + [0] * 5) for code in orgStructures] # result = [ # (152, [u'КДО', 0, 0, 0, 0, 0]), # Клинико-диагностическое отделение # (414, [u'КДЦ', 0, 0, 0, 0, 0]), # Медицинский центр (Консультативно-диагностическое подразделение) # (576, [u'ЦДЛ', 0, 0, 0, 0, 0]) # Центр лечения и профилактики (Консультативно-диагностическое подразделение) # ] orgStructureDescendants = dict([(id, getOrgStructureDescendants(id)) for id, row in result]) orgStructureMapRow = {} for i, (headId, row) in enumerate(result): orgStructureMapRow[headId] = i for id in getOrgStructureDescendants(headId): orgStructureMapRow[id] = i for clientId, events in data.iteritems(): for MKB in events.itervalues(): tmp = [u'', 0, 0, 0, 0, 0] tmp[1] += 1 if MKB['MKB'] and MKB['MKB'][0] == u'C': tmp[2] += 1 if MKB['isPrimary'] == 2: tmp[3] += 1 if MKB['hasHospital']: tmp[4] += 1 if MKB['isPrimary'] == 2: tmp[5] += 1 for orgStructureId, list in orgStructureDescendants.iteritems( ): if MKB['orgStructureId'] in list: for idx in range(1, len(tmp)): result[orgStructureMapRow[orgStructureId]][1][ idx] += tmp[idx] totalRow = [u'ИТОГО', 0, 0, 0, 0, 0] for id, row in result: for idx in xrange(1, len(totalRow)): totalRow[idx] += row[idx] result.append((None, totalRow)) return result
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 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 selectRawData(params): db = QtGui.qApp.db tableEvent = db.table('Event') tablePerson = db.table('Person') cond = [] begDate = params.get('begDate', None) endDate = params.get('endDate', None) personId = params.get('personId', None) personIdList = params.get('personIdList', None) orgStructureId = params.get('orgStructureId', None) specialityId = params.get('specialityId', None) activityId = params.get('activityId', None) if begDate: cond.append(tableEvent['setDate'].ge(begDate)) if endDate: cond.append(tableEvent['setDate'].lt(endDate.addDays(1))) if personId: cond.append(tableEvent['setPerson_id'].eq(personId)) if personIdList: cond.append(tableEvent['setPerson_id'].inlist(personIdList)) if orgStructureId: cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) else: if not personId and not personIdList: cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId())) if specialityId: cond.append(tablePerson['speciality_id'].eq(specialityId)) if activityId: tablePersonActivity = db.table('Person_Activity') cond.append( db.existsStmt(tablePersonActivity, [ tablePersonActivity['master_id'].eq(tablePerson['id']), tablePersonActivity['activity_id'].eq(activityId), tablePersonActivity['deleted'].eq(0), ])) # cond.append('EXISTS (SELECT * FROM Person_Activity WHERE Person_Activity.master_id=Event.setPerson_id AND Person_Activity.deleted=0 AND Person_Activity.activity_id=%d)'%activityId) stmt = """ SELECT Event.setDate AS date, Event.setPerson_id AS person_id, APT.name AS paramName, APTimeValue.value AS time, APStringValue.value AS office FROM Action INNER JOIN Event ON Event.id = Action.event_id INNER JOIN Person ON Person.id = Event.setPerson_id INNER JOIN EventType ON EventType.id = Event.eventType_id INNER JOIN ActionType ON ActionType.id = Action.actionType_id INNER JOIN ActionPropertyType AS APT ON APT.actionType_id = ActionType.id AND APT.name IN ('begTime', 'begTime1', 'begTime2', 'endTime', 'endTime1', 'endTime2', 'office', 'office1', 'office2') LEFT JOIN ActionProperty AS AP ON AP.action_id = Action.id AND AP.type_id = APT.id LEFT JOIN ActionProperty_Time AS APTimeValue ON APTimeValue.id = AP.id LEFT JOIN ActionProperty_String AS APStringValue ON APStringValue.id = AP.id WHERE Action.deleted =0 AND Event.deleted =0 AND ActionType.code = 'amb' AND EventType.code = '0' AND (APTimeValue.value IS NOT NULL OR APStringValue.value IS NOT NULL) AND %s ORDER BY Event.setDate, Event.setPerson_id""" return db.query(stmt % db.joinAnd(cond))
def selectData(begDate, endDate, orgStructureId): db = QtGui.qApp.db tableClient = db.table('Client') tableClientSocStatus = db.table('ClientSocStatus') tableSocStatusType = db.table('rbSocStatusType') tableClientAttach = db.table('ClientAttach') tableOrgStructure = db.table('OrgStructure') queryTable = tableClient.innerJoin( tableClientSocStatus, tableClientSocStatus['client_id'].eq(tableClient['id'])) queryTable = queryTable.innerJoin( tableSocStatusType, tableSocStatusType['id'].eq(tableClientSocStatus['socStatusType_id'])) queryTable = queryTable.leftJoin( tableClientAttach, tableClientAttach['client_id'].eq(tableClient['id'])) cols = [ tableClient['lastName'], tableClient['firstName'], tableClient['patrName'], tableClient['birthDate'], 'TIMESTAMPDIFF(YEAR,%s,now()) as age' % (tableClient['birthDate']), 'if(TIMESTAMPDIFF(YEAR,%s,now()) >= 16 AND if(%s = 1, TIMESTAMPDIFF(YEAR,%s,now()) < 60, TIMESTAMPDIFF(YEAR,%s,now()) < 55), 1, 2) AS workingAge' % (tableClient['birthDate'], tableClient['sex'], tableClient['birthDate'], tableClient['birthDate']), tableSocStatusType['code'], 'if(count(DISTINCT %s) = 1, 1, 0) as first' % tableClientSocStatus['socStatusType_id'] ] cond = [ tableClientSocStatus['begDate'].dateLe(endDate), tableClientSocStatus['begDate'].dateGe(begDate), tableClient['deleted'].eq(0), tableClientSocStatus['deleted'].eq(0), '%s is not NULL' % tableClientAttach['orgStructure_id'] ] tmpCond = [] for j in xrange(9): tmpCond.append(tableSocStatusType['code'].eq(u'Л_0' + forceString(j + 1))) cond.append(db.joinOr(tmpCond)) if orgStructureId: cond.append(tableClientAttach['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) order = [] group = tableClient['lastName'].name() order.append('workingAge') order.append(tableClient['lastName'].name()) stmt = db.selectStmt(queryTable, cols, cond, group=group, order=order) query = db.query(stmt) print stmt return query
def mainTableStmt(): OrgStructure = db.table('OrgStructure') OSHB = db.table('OrgStructure_HospitalBed').alias('OSHB') HBI = db.table('HospitalBed_Involute').alias('HBI') table = OrgStructure.innerJoin( OSHB, OSHB['master_id'].eq(OrgStructure['id'])) table = table.leftJoin(HBI, HBI['master_id'].eq(OSHB['id'])) cols = [ OrgStructure['id'].alias('id'), OrgStructure['infisCode'].alias('code'), db.count(OSHB['id']).alias('beds'), db.countIf( db.joinAnd([OSHB['sex'].eq(1), OSHB['isPermanent'].eq(1)]), OSHB['id']).alias('menBeds'), db.countIf( db.joinAnd([OSHB['sex'].eq(2), OSHB['isPermanent'].eq(1)]), OSHB['id']).alias('womenBeds'), db.countIf(OSHB['isPermanent'].eq(1), OSHB['id']).alias('permanentBeds'), db.countIf(HBI['involuteType'].eq(1), OSHB['id']).alias('involuteBeds'), ] cond = [ OrgStructure['deleted'].eq(0), OrgStructure['infisCode'].ne(u'') ] if not orgStructureId is None: cond.append(OrgStructure['id'].inlist( getOrgStructureDescendants(orgStructureId))) return db.selectStmt(table, cols, cond, group=OrgStructure['id'])
def selectData(clientId, begDate, endDate, eventPurposeId, specialityId, personId, orgStructureId): stmt = """ SELECT Visit.date AS date, vrbPerson.name AS person, rbSpeciality.name AS speciality, rbScene.name AS scene, rbService.code AS service, EventType.name AS eventType, Event.mes_id AS mes, Diagnosis.MKB AS MKB, Diagnosis.MKBEx AS MKBEx, rbResult.name AS resultName FROM Visit LEFT JOIN vrbPerson ON vrbPerson.id = Visit.person_id LEFT JOIN rbSpeciality ON rbSpeciality.id = vrbPerson.speciality_id LEFT JOIN rbScene ON rbScene.id = Visit.scene_id LEFT JOIN rbService ON rbService.id = Visit.service_id LEFT JOIN Event ON Event.id = Visit.Event_id LEFT JOIN EventType ON EventType.id = Event.eventType_id LEFT JOIN Diagnosis ON Diagnosis.id = getEventPersonDiagnosis(Event.id, Visit.person_id) LEFT JOIN Diagnostic ON Diagnostic.event_id = Event.id AND Diagnostic.diagnosis_id = Diagnosis.id LEFT JOIN rbResult ON rbResult.id = Diagnostic.result_id WHERE %s ORDER BY Visit.date DESC """ db = QtGui.qApp.db tableEvent = db.table('Event') tableDiagnostic = db.table('Diagnostic') tableVisit = db.table('Visit') tableVRBPerson = db.table('vrbPerson') cond = [ tableEvent['deleted'].eq(0), tableVisit['deleted'].eq(0), db.joinOr( [tableDiagnostic['deleted'].eq(0), tableDiagnostic['id'].isNull()]) ] if clientId: cond.append(tableEvent['client_id'].eq(clientId)) if begDate: cond.append(tableVisit['date'].ge(begDate)) if endDate: cond.append(tableVisit['date'].le(endDate)) if eventPurposeId: tableEventType = db.table('EventType') cond.append(tableEventType['purpose_id'].eq(eventPurposeId)) if specialityId: cond.append(tableDiagnostic['speciality_id'].eq(specialityId)) if personId: cond.append(tableDiagnostic['person_id'].eq(personId)) if orgStructureId: cond.append(tableVRBPerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) return db.query(stmt % (db.joinAnd(cond)))
def selectData(params): db = QtGui.qApp.db tableEvent = db.table('Event') tableOrgMoving = db.table('ActionProperty_Organisation').alias('orgMoving') eventTypeIds = (params.get('chkEventType'), params.get('eventTypeId'), params.get('eventTypeIdMulti')) orgStructureIds = (params.get('chkOrgStructure'), params.get('orgStructureId'), params.get('orgStructureIdMulti')) days = params.get('days') cond = [] if not eventTypeIds[0] and eventTypeIds[1]: cond.append(tableEvent['eventType_id'].eq(eventTypeIds[1])) elif eventTypeIds[0] and eventTypeIds[2]: cond.append(tableEvent['eventType_id'].inlist(eventTypeIds[2])) if not orgStructureIds[0] and orgStructureIds[1]: cond.append(tableOrgMoving['value'].inlist(getOrgStructureDescendants(orgStructureIds[1]))) elif orgStructureIds[0] and orgStructureIds[2]: cond.append(tableOrgMoving['value'].inlist(orgStructureIds[2])) if days: cond.append(('datediff(CURDATE(), Event.setDate) >= %s' % days)) stmt = u'''SELECT Client.lastName AS LastName , Client.firstName AS FirstName , Client.patrName AS PatrName , Event.externalId AS IB , if(datediff(CURDATE(), Event.setDate) = 0, 1, datediff(CURDATE(), Event.setDate)) AS Days , Client.id AS id FROM Client INNER JOIN Event ON Client.id = Event.client_id AND Event.deleted = 0 INNER JOIN ActionType MovingActionType ON MovingActionType.deleted = 0 AND MovingActionType.flatCode = 'moving' INNER JOIN Action MovingAction ON MovingAction.event_id = Event.id AND MovingAction.deleted = 0 AND MovingAction.actionType_id = MovingActionType.id AND MovingAction.status = 0 INNER JOIN ActionPropertyType MovingActionPropertyType ON MovingActionPropertyType.actionType_id = MovingActionType.id AND MovingActionPropertyType.name LIKE 'Отделение пребывания%%' INNER JOIN ActionProperty MovingActionProperty ON MovingActionProperty.action_id = MovingAction.id AND MovingActionProperty.type_id = MovingActionPropertyType.id INNER JOIN ActionProperty_OrgStructure orgMoving ON orgMoving.id = MovingActionProperty.id %s ORDER BY Days DESC, IB''' % ((u'WHERE ' + db.joinAnd(cond)) if cond else u'') return db.query(stmt)
def selectDataCured(client_id, prevExecDate, mkb, params): begDate = params.get('begDate', QtCore.QDate()) endDate = params.get('endDate', QtCore.QDate()) orgStructureId = params.get('orgStructureId', None) personId = params.get('personId', None) db = QtGui.qApp.db tableClient = db.table('Client') tableEvent = db.table('Event') tableAccountItem = db.table('Account_Item') tablePerson = db.table('Person') tableDiagnosis = db.table('Diagnosis') cond = [] joinCond = [] cond.append(tableEvent['setDate'].ge(begDate)) cond.append(tableEvent['setDate'].ge(prevExecDate)) cond.append(tableEvent['execDate'].le(endDate)) cond.append(tableClient['id'].eq(client_id)) cond.append(tableDiagnosis['MKB'].eq(mkb)) if params.get('podtver', None): if params.get('podtverType', 0) == 1: cond.append(tableAccountItem['date'].isNotNull()) cond.append(tableAccountItem['refuseType_id'].isNull()) elif params.get('podtverType', 0) == 2: cond.append(tableAccountItem['date'].isNotNull()) cond.append(tableAccountItem['refuseType_id'].isNotNull()) if params.get('begDatePodtver', None): cond.append(tableAccountItem['date'].ge(params['begDatePodtver'])) if params.get('endDatePodtver', None): cond.append(tableAccountItem['date'].lt( params['endDatePodtver'].addDays(1))) if params.get('refuseType', None) != 0: cond.append(tableAccountItem['refuseType_id'].eq( params['refuseType'])) if personId: cond.append(tableEvent['execPerson_id'].eq(personId)) elif orgStructureId: joinCond.append('LEFT JOIN Person ON Event.execPerson_id=Person.id') cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) stmt = u''' SELECT Event.id as id FROM Event LEFT JOIN Client ON Event.client_id=Client.id LEFT JOIN Diagnostic ON Event.id=Diagnostic.event_id LEFT JOIN Diagnosis ON Diagnostic.diagnosis_id=Diagnosis.id %s WHERE %s ''' return db.query(stmt % (' '.join(joinCond), db.joinAnd(cond)))
def selectDate(params): begDate = params.get('begDate') endDate = params.get('endDate') orgStructureId = params.get('orgStructureId') db = QtGui.qApp.db tableClientPolicy = db.table('ClientPolicy') tableActionType = db.table('ActionType') tableActionPropertyType = db.table('ActionPropertyType') tableOrgStructure = db.table('OrgStructure') tableEvent = db.table('Event') cond = [ tableClientPolicy['isSearchPolicy'].le(1), tableActionType['flatCode'].eq('moving'), tableActionPropertyType['name'].eq(u'Отделение пребывания'), tableClientPolicy['createDatetime'].between(begDate, endDate.addDays(1)), db.joinOr([ tableEvent['setDate'].between(begDate, endDate.addDays(1)), tableEvent['execDate'].between(begDate, endDate.addDays(1)) ]) ] if orgStructureId: cond.append(tableOrgStructure['id'].inlist( getOrgStructureDescendants(orgStructureId))) cond.extend([ tableClientPolicy['deleted'].eq(0), tableEvent['deleted'].eq(0), tableActionType['deleted'].eq(0), tableActionPropertyType['deleted'].eq(0) ]) stmt = u'''SELECT group_concat(Event.externalId) as external , trim(concat(Client.lastName, ' ', Client.firstName, ' ', Client.patrName)) AS clientName , OrgStructure.code , ClientPolicy.insurer_id , ClientPolicy.serial , ClientPolicy.number , ClientPolicy.begDate , ClientPolicy.endDate FROM Client INNER JOIN ClientPolicy ON ClientPolicy.id = getClientPolicyId(Client.id, 1) AND ClientPolicy.client_id = Client.id INNER JOIN Event ON Event.client_id = ClientPolicy.client_id INNER JOIN Action ON Action.event_id = Event.id AND Action.deleted = 0 INNER JOIN ActionType ON ActionType.id = Action.actionType_id INNER JOIN ActionPropertyType ON ActionPropertyType.actionType_id = ActionType.id LEFT JOIN ActionProperty ON ActionProperty.action_id = Action.id AND ActionProperty.type_id = ActionPropertyType.id AND ActionProperty.deleted = 0 LEFT JOIN ActionProperty_OrgStructure ON ActionProperty_OrgStructure.id = ActionProperty.id LEFT JOIN OrgStructure ON OrgStructure.id = ActionProperty_OrgStructure.value AND OrgStructure.deleted = 0 WHERE %s GROUP BY ClientPolicy.id''' % db.joinAnd(cond) return db.query(stmt)
def selectData(params, lstOrgStructure): db = QtGui.qApp.db begDate = params.get('begDate') endDate = params.get('endDate') order = params.get('order') eventTypeId = params.get('eventTypeId') eventPurposeId = params.get('eventPurposeId') financeId = params.get('financeId') cmbOrgStructure = params.get('cmbOrgStructure') orgStructureId = params.get('orgStructureId') tableAction = db.table('Action') tableEvent = db.table('Event') tableEventType = db.table('EventType') tableAPOrgStructure = db.table('ActionProperty_OrgStructure') cond = [tableAction['begDate'].dateGe(begDate), tableAction['begDate'].dateLe(endDate)] if order: cond.append(tableEvent['order'].eq(order)) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) if eventPurposeId: cond.append(tableEventType['purpose_id'].eq(eventPurposeId)) if financeId: cond.append(tableAction['finance_id'].eq(financeId)) if lstOrgStructure and cmbOrgStructure: cond.append(tableAPOrgStructure['value'].inlist(lstOrgStructure)) if orgStructureId and not cmbOrgStructure: cond.append(tableAPOrgStructure['value'].inlist(getOrgStructureDescendants(orgStructureId))) stmt = u'''SELECT act_moving.MKB, aps_leavedResult.id AS dead, DATEDIFF(act_leaved.endDate, Client.birthDate) AS deadAge FROM Event INNER JOIN EventType ON EventType.id = Event.eventType_id AND EventType.deleted = 0 INNER JOIN Client ON Client.id = Event.client_id INNER JOIN Action ON Action.event_id = Event.id AND Action.deleted = 0 INNER JOIN ActionType ON ActionType.id = Action.actionType_id AND ActionType.flatCode = 'received' INNER JOIN ActionPropertyType ON ActionPropertyType.actionType_id = ActionType.id AND ActionPropertyType.name = 'Направлен в отделение' AND ActionPropertyType.deleted = 0 LEFT JOIN ActionProperty ON ActionProperty.action_id = Action.id AND ActionProperty.type_id = ActionPropertyType.id AND ActionProperty.deleted = 0 LEFT JOIN ActionProperty_OrgStructure ON ActionProperty_OrgStructure.id = ActionProperty.id INNER JOIN Action act_moving ON act_moving.id = (SELECT MAX(a.id) FROM Action a INNER JOIN ActionType AT ON AT.id = a.actionType_id WHERE AT.flatCode = 'moving' AND a.event_id = Event.id AND a.deleted = 0 AND AT.deleted = 0) LEFT JOIN Action act_leaved ON act_leaved.event_id = Event.id AND act_leaved.actionType_id = (SELECT at.id FROM ActionType at WHERE at.flatCode = 'leaved' AND at.deleted = 0) AND act_leaved.deleted = 0 LEFT JOIN ActionPropertyType apt_leavedResult ON apt_leavedResult.actionType_id = act_leaved.actionType_id AND apt_leavedResult.name = 'Исход госпитализации' AND apt_leavedResult.deleted = 0 LEFT JOIN ActionProperty ap_leavedResult ON ap_leavedResult.action_id = act_leaved.id AND ap_leavedResult.type_id = ActionPropertyType.id AND ap_leavedResult.deleted = 0 LEFT JOIN ActionProperty_String aps_leavedResult ON aps_leavedResult.id = ActionProperty.id AND aps_leavedResult.value = 'умер' WHERE %s AND DATEDIFF(Action.begDate, Client.birthDate) <= 6 AND Event.deleted = 0''' % (db.joinAnd(cond)) return db.query(stmt)
def selectData(params): db = QtGui.qApp.db begDate = params.get('begDate', QtCore.QDate()) endDate = params.get('endDate', QtCore.QDate()) orgStructureId = params.get('orgStructureId') tableAction = db.table('Action') tableOrgStructure = db.table('ActionProperty_OrgStructure') cond = [ tableAction['begDate'].dateGe(begDate), tableAction['begDate'].dateLe(endDate) ] if orgStructureId: cond.append(tableOrgStructure['value'].inlist( getOrgStructureDescendants(orgStructureId))) stmt = u'''SELECT aps.value, COUNT(Action.id) AS countEvent, COUNT(aps.value) AS countRefusal, COUNT(IF(Event.order = 2 AND aps.value IS NULL, Action.id, NULL)) AS countExtra, COUNT(IF(Event.order = 1 AND aps.value IS NULL, Action.id, NULL)) AS countPlan, COUNT(IF(o.isMedical = 1, apDirected.id, NULL)) AS countClinic, COUNT(IF(o.isMedical = 2, apDirected.id, NULL)) AS countStationary, COUNT(IF(apsOtherDirected.value = 'СМП', Action.id, NULL)) AS countSMP, COUNT(IF(o.id IS NULL AND apsOtherDirected.value IS NULL, Action.id, NULL)) AS countWithoutOrgStructure, COUNT(aps.id) AS countNotRequire FROM ActionType INNER JOIN Action ON Action.actionType_id = ActionType.id AND Action.deleted = 0 INNER JOIN Event ON Event.id = Action.event_id AND Event.deleted = 0 INNER JOIN Action act ON act.id = (SELECT MIN(a.id) AS id FROM ActionType INNER JOIN Action a ON a.actionType_id = ActionType.id AND a.deleted = 0 WHERE ActionType.flatCode = 'moving' AND a.event_id = Action.event_id AND ActionType.deleted = 0) INNER JOIN ActionProperty ON ActionProperty.action_id = act.id AND ActionProperty.deleted =0 INNER JOIN ActionPropertyType ON ActionPropertyType.id = ActionProperty.type_id AND ActionPropertyType.name = 'Отделение пребывания' AND ActionPropertyType.deleted =0 INNER JOIN ActionProperty_OrgStructure ON ActionProperty_OrgStructure.id = ActionProperty.id LEFT JOIN ActionProperty ap ON ap.action_id = Action.id AND ap.type_id = (SELECT apt.id FROM ActionPropertyType apt WHERE apt.actionType_id = ActionType.id AND apt.name = 'Причина отказа от госпитализации' AND apt.deleted = 0) AND ap.deleted = 0 LEFT JOIN ActionProperty_String aps ON aps.id = ap.id LEFT JOIN ActionProperty apDirected ON apDirected.action_id = Action.id AND apDirected.type_id = (SELECT apt.id FROM ActionPropertyType apt WHERE apt.actionType_id = ActionType.id AND apt.name = 'Кем направлен' AND apt.deleted = 0) AND apDirected.deleted = 0 LEFT JOIN ActionProperty_Organisation apo ON apo.id = apDirected.id LEFT JOIN Organisation o ON o.id = apo.value AND o.deleted =0 LEFT JOIN ActionProperty apOtherDirected ON apOtherDirected.action_id = Action.id AND apOtherDirected.type_id = (SELECT apt.id FROM ActionPropertyType apt WHERE apt.actionType_id = ActionType.id AND apt.name = 'Прочие направители' AND apt.deleted = 0) AND apOtherDirected.deleted = 0 LEFT JOIN ActionProperty_String apsOtherDirected ON apsOtherDirected.id = apOtherDirected.id WHERE ActionType.flatCode = 'received' AND %s AND ActionType.deleted = 0 GROUP BY aps.value''' % db.joinAnd(cond) return db.query(stmt)
def selectData(params): db = QtGui.qApp.db begDate = forceDate(params.get('begDate')) endDate = forceDate(params.get('endDate')) contractIdList = forceString(params.get('contractIdList')) financeId = forceInt(params.get('typeFinanceId')) eventTypeId = forceInt(params.get('eventTypeId')) where = '' join = '' if begDate: where += u"AND DATE(Account.createDatetime) >= DATE('%s') " % begDate.toString('yyyy-MM-dd') if endDate: where += u"AND DATE(Account.createDatetime) <= DATE('%s') " % endDate.toString('yyyy-MM-dd') if contractIdList: contractIdList = contractIdList.replace('[', '(') contractIdList = contractIdList.replace(']', ')') where += u'AND Contract.id IN %s ' % contractIdList if financeId: where += u'AND Contract.finance_id = %s ' % financeId if eventTypeId: join += u"INNER JOIN Event ON Event.contract_id = Contract.id AND Event.deleted = 0 AND Event.eventType_id = %s \n" % eventTypeId if params.get('orgStructureId', None): orgStruct = forceString(getOrgStructureDescendants(params['orgStructureId'])) orgStruct = orgStruct.replace('[', '(') orgStruct = orgStruct.replace(']', ')') join += u''' INNER JOIN Action ON Action.contract_id = Contract.id AND Action.deleted = 0 INNER JOIN Person ON Person.id = Action.person_id AND Person.deleted = 0 AND Person.orgStructure_id IN %s ''' % orgStruct stmt = u''' SELECT Contract.number, Contract.resolution, Account.settleDate, Account_Item.amount, Account_Item.sum FROM Contract INNER JOIN Contract_Tariff ON Contract_Tariff.master_id = Contract.id AND Contract_Tariff.deleted = 0 INNER JOIN Account_Item ON Account_Item.tariff_id = Contract_Tariff.id AND Account_Item.deleted = 0 INNER JOIN Account ON Account.id = Account_Item.master_id AND Account.deleted = 0 %(join)s WHERE Contract.deleted = 0 %(where)s ORDER BY Contract.number, Account.settleDate ''' % {'join' : join, 'where' : where} return db.query(stmt)
def build(self, params): bf = QtGui.QTextCharFormat() bf.setFontWeight(QtGui.QFont.Bold) doc = QtGui.QTextDocument() cursor = QtGui.QTextCursor(doc) cursor.setBlockFormat(CReportBase.AlignLeft) cursor.setCharFormat(CReportBase.ReportTitle) cursor.insertText(self.title()) cursor.insertBlock() self.dumpParams(cursor, params) begDate = forceDate(params.get('begDate')) orgStructId = params.get('orgStructId') tableColumns = [ ('4%', [u'№'], CReportBase.AlignCenter), ('14%', [u'Код МО'], CReportBase.AlignCenter), ('6%', [u'Дата и время фактической госпитализации'], CReportBase.AlignCenter), ('20%', [u'Персональные данные пациента'], CReportBase.AlignCenter), ('14%', [u'Код профиля койки'], CReportBase.AlignCenter), ('14%', [u'Код отделения'], CReportBase.AlignCenter), ('14%', [u'Номер карты стационарного больного'], CReportBase.AlignCenter), ('14%', [u'Код МКБ приемного отделения'], CReportBase.AlignCenter) ] table = createTable(cursor, tableColumns) query = selectSendOrdersHospitalUrgently( getOrgStructureDescendants(orgStructId), begDate) number = 1 while query.next(): record = query.record() i = table.addRow() table.setText(i, 0, number) table.setText(i, 1, forceString(record.value('orgCurInfisCode'))) table.setText( i, 2, forceDateTime( record.value('setDate')).toString('yyyy-MM-ddThh:mm:ss')) table.setText(i, 3, getPersonInfo(record)) table.setText(i, 4, forceString(record.value('hospitalBedCode'))) table.setText(i, 5, forceString(record.value('orgStructureCode'))) table.setText(i, 6, forceString(record.value('externalId'))) table.setText(i, 7, '') number += 1 return doc
def selectData(params, reportOrgStructure = False): db = QtGui.qApp.db begDate = params.get('edtBegDateTime', QtCore.QDateTime()) endDate = params.get('edtEndDateTime', QtCore.QDateTime()) orgStructureId = params.get('orgStructureId') profileBed = params.get('profileBed') tableAction = db.table('Action') tableOrgStructure = db.table('OrgStructure') tableHospitalBedProfile = db.table('rbHospitalBedProfile') cond = [tableAction['begDate'].ge(begDate), tableAction['begDate'].le(endDate)] if profileBed: cond.append(tableHospitalBedProfile['id'].eq(profileBed)) if orgStructureId: cond.append(tableOrgStructure['id'].inlist(getOrgStructureDescendants(orgStructureId))) stmt = u'''SELECT %s, COUNT(act.id) AS countEvent, COUNT(aps.value) AS countRefusal FROM ActionType INNER JOIN Action ON Action.actionType_id = ActionType.id AND Action.deleted = 0 INNER JOIN Action act ON act.id = (SELECT MIN(a.id) AS id FROM ActionType INNER JOIN Action a ON a.actionType_id = ActionType.id AND a.deleted = 0 WHERE ActionType.flatCode = 'moving' AND a.event_id = Action.event_id AND ActionType.deleted = 0) INNER JOIN ActionProperty ON ActionProperty.action_id = act.id AND ActionProperty.deleted =0 INNER JOIN ActionPropertyType ON ActionPropertyType.id = ActionProperty.type_id AND ActionPropertyType.name = 'Отделение пребывания' AND ActionPropertyType.deleted =0 INNER JOIN ActionProperty_OrgStructure ON ActionProperty_OrgStructure.id = ActionProperty.id INNER JOIN OrgStructure ON OrgStructure.id = ActionProperty_OrgStructure.value LEFT JOIN ActionProperty ap ON ap.action_id = Action.id AND ap.type_id IN (SELECT apt.id FROM ActionPropertyType apt WHERE apt.name = 'Причина отказа от госпитализации' AND apt.deleted = 0) LEFT JOIN ActionProperty_String aps ON aps.id = ap.id LEFT JOIN ActionProperty apHospitalBedProfile ON apHospitalBedProfile.action_id = act.id AND apHospitalBedProfile.type_id IN (SELECT apt.id FROM ActionPropertyType apt INNER JOIN ActionType at ON at.id = apt.actionType_id AND at.flatCode = 'moving' AND at.deleted = 0 WHERE apt.name = 'Профиль') AND apHospitalBedProfile.deleted = 0 %s -- LEFT JOIN ActionProperty_rbHospitalBedProfile aphb ON aphb.id = apHospitalBedProfile.id -- LEFT JOIN rbHospitalBedProfile ON rbHospitalBedProfile.id = aphb.value -- LEFT JOIN OrgStructure_HospitalBed ON OrgStructure.id = OrgStructure_HospitalBed.master_id -- LEFT JOIN rbHospitalBedProfile ON OrgStructure_HospitalBed.profile_id = rbHospitalBedProfile.id WHERE ActionType.flatCode = 'received' AND %s GROUP BY %s''' % (u'OrgStructure.code' if reportOrgStructure else u'rbHospitalBedProfile.name AS code', u'LEFT JOIN ActionProperty_rbHospitalBedProfile aphb ON aphb.id = apHospitalBedProfile.id ' u'LEFT JOIN rbHospitalBedProfile ON rbHospitalBedProfile.id = aphb.value' if reportOrgStructure else u'LEFT JOIN OrgStructure_HospitalBed ON OrgStructure.id = OrgStructure_HospitalBed.master_id' u' LEFT JOIN rbHospitalBedProfile ON OrgStructure_HospitalBed.profile_id = rbHospitalBedProfile.id', db.joinAnd(cond), u'OrgStructure.id' if reportOrgStructure else u'rbHospitalBedProfile.id') 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 loadItems(self): items = [] if self._orgStructure: items.append((None, u'<все>')) osList = getOrgStructureDescendants(self._orgStructure) for item in self._db.getRecordList( self._tbl, (self._tbl_ah['KLADRStreetCode'], ), self._tbl_osa['master_id'].inlist(osList), self._tbl_ah['KLADRStreetCode'], True): items.append( (forceString(item.value('KLADRStreetCode')), getStreetName(forceString(item.value('KLADRStreetCode'))) or u'<пусто>')) return items
def selectData(begDate, endDate, eventPurposeId, eventTypeId, orgStructureId, specialityId, personId, contractIdList, insurerId, sex, ageFrom, ageTo): stmt=""" SELECT Event.client_id AS client_id, rbMedicalAidType.code AS medicalAidTypeCode, Diagnosis.MKB AS MKB, Account_Item.`sum` AS `sum` FROM Account_Item LEFT JOIN Account ON Account.id = Account_Item.master_id LEFT JOIN Event ON Event.id = Account_Item.event_id LEFT JOIN EventType ON EventType.id = Event.eventType_id LEFT JOIN Action ON Action.id = Account_Item.action_id LEFT JOIN ActionType ON ActionType.id = Action.actionType_id LEFT JOIN vrbPersonWithSpeciality ON vrbPersonWithSpeciality.id = Event.execPerson_id LEFT JOIN rbMedicalAidType ON rbMedicalAidType.id = EventType.medicalAidType_id LEFT JOIN Client ON Client.id = Event.client_id LEFT JOIN ClientPolicy ON ClientPolicy.id = getClientPolicyId(Event.client_id, 1) LEFT JOIN Diagnosis ON Diagnosis.id = getEventDiagnosis(Event.id) WHERE (ActionType.isMES OR Event.MES_id) AND Account_Item.reexposeItem_id IS NULL AND Account_Item.deleted=0 AND Account.deleted=0 AND %s ORDER BY Event.client_id """ db = QtGui.qApp.db tableEvent = db.table('Event') tableClient = db.table('Client') tablePerson = db.table('vrbPersonWithSpeciality') tableAccount = db.table('Account') tableClientPolicy = db.table('ClientPolicy') 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 personId: cond.append(tableEvent['execPerson_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 contractIdList: cond.append(tableAccount['contract_id'].inlist(contractIdList)) if insurerId: cond.append(tableClientPolicy['insurer_id'].eq(insurerId)) 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(params): begDate = params.get('begDate', QtCore.QDate()) endDate = params.get('endDate', QtCore.QDate()) personId = params.get('personId', None) orgStructureId = params.get('orgStructureId', None) stmt = ''' SELECT Client.id AS clientId, CONCAT_WS(" ", Client.lastName, Client.firstName, Client.patrName) AS fio, ActionType.name AS usl, rbRefusalReasons.name AS reason FROM Event INNER JOIN Client ON Event.client_id = Client.id INNER JOIN Action ON Action.event_id = Event.id AND Action.status = 3 INNER JOIN ActionType ON Action.actionType_id = ActionType.id INNER JOIN ActionProperty ON ActionProperty.action_id = Action.id INNER JOIN ActionPropertyType ON ActionProperty.type_id = ActionPropertyType.id AND ActionPropertyType.penalty = 666 INNER JOIN ActionProperty_Reference ON ActionProperty_Reference.id = ActionProperty.id INNER JOIN rbRefusalReasons ON rbRefusalReasons.id = ActionProperty_Reference.value INNER JOIN Person ON Person.id = Action.setPerson_id INNER JOIN OrgStructure ON Person.orgStructure_id = OrgStructure.id WHERE Action.deleted = 0 AND Person.deleted = 0 AND Event.deleted = 0 AND Client.deleted = 0 AND %s ORDER BY clientId ''' db = QtGui.qApp.db tablePerson = db.table('Person') tblAction = db.table('Action') cond = [] cond.append(tblAction['createDatetime'].dateBetween(begDate, endDate)) if orgStructureId: cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) # else: # cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId())) if personId: cond.append(tablePerson['id'].eq(personId)) return db.query(stmt % (db.joinAnd(cond)))
def selectData(begDate, endDate, eventPurposeId, eventTypeId, orgStructureId, specialityId, personId): stmt = """ SELECT count(Event.id) AS `count`, EventType.name AS eventTypeName, Event.MES_id AS mesId, mes.MES.code AS mesCode, mes.MES.name AS mesName, Event.execDate IS NOT NULL AS isDone, IF(Event.MES_id IS NOT NULL, DATEDIFF(IF(Event.execDate IS NOT NULL, Event.execDate, CURDATE()), Event.setDate)+1>mes.MES.avgDuration, 0) AS isMesRunout FROM Event LEFT JOIN EventType ON EventType.id = Event.eventType_id LEFT JOIN rbEventTypePurpose ON rbEventTypePurpose.id = EventType.purpose_id LEFT JOIN vrbPersonWithSpeciality ON vrbPersonWithSpeciality.id = Event.execPerson_id LEFT JOIN mes.MES ON mes.MES.id = Event.MES_id WHERE rbEventTypePurpose.code != '0' AND EventType.mesRequired AND %s GROUP BY Event.eventType_id, Event.MES_id, isDone, isMesRunout """ db = QtGui.qApp.db tableEvent = db.table('Event') tablePerson = db.table('vrbPersonWithSpeciality') cond = [] cond.append(tableEvent['deleted'].eq(0)) cond.append(tableEvent['setDate'].ge(begDate)) cond.append(tableEvent['setDate'].lt(endDate.addDays(1))) # cond.append(tableEvent['execDate'].isNull()) if eventTypeId: cond.append(tableEvent['eventType_id'].eq(eventTypeId)) elif eventPurposeId: cond.append(db.table('EventType')['purpose_id'].eq(eventPurposeId)) if personId: cond.append(tablePerson['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)) return db.query(stmt % (db.joinAnd(cond)))
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 selectData(params): db = QtGui.qApp.db begDate = params.get('begDate') endDate = params.get('endDate') orgStructId = params.get('orgStructId') tblEvent = db.table('Event') tblClient = db.table('Client') tblRbResult = db.table('rbResult') tblPerson = db.table('Person') tblMAction = db.table('Action').alias('MAction') tblMActionType = db.table('ActionType').alias('MActionType') tblMES = db.table('mes.MES').alias('MES') tblMES_mkb = db.table('mes.MES_mkb').alias('MES_mkb') fields = \ [ u'''IF(rbResult.`name` like '%умер%', 1, 0) AS isDeath''', tblMES_mkb['mkb'].alias('mkb'), '''CONCAT_WS(' ', Client.lastName, Client.firstName, Client.patrName) AS name''', tblEvent['externalId'].alias('id'), ] tblQuery = tblEvent.innerJoin(tblRbResult, tblEvent['result_id'].eq(tblRbResult['id'])) tblQuery = tblQuery.innerJoin(tblClient, tblEvent['client_id'].eq(tblClient['id'])) tblQuery = tblQuery.innerJoin(tblPerson, tblEvent['execPerson_id'].eq(tblPerson['id'])) tblQuery = tblQuery.innerJoin(tblMAction, tblEvent['id'].eq(tblMAction['event_id'])) tblQuery = tblQuery.innerJoin(tblMActionType, db.joinAnd([ tblMAction['actionType_id'].eq(tblMActionType['id']), tblMActionType['flatCode'].eq('moving') ])) tblQuery = tblQuery.innerJoin(tblMES, tblMAction['MES_id'].eq(tblMES['id'])) tblQuery = tblQuery.innerJoin(tblMES_mkb, tblMES['id'].eq(tblMES_mkb['master_id'])) where = \ [ tblEvent['deleted'].eq(0), tblMAction['deleted'].eq(0), tblMES_mkb['mkb'].inlist(['I20.0', 'I21', 'I22', 'I23', 'I60', 'I61', 'I62', 'I63', 'I64']) ] if begDate and endDate: where.append(tblEvent['execDate'].between(begDate, endDate)) if orgStructId: where.append(tblPerson['orgStructure_id'].inlist(getOrgStructureDescendants(orgStructId))) stmt = db.selectStmt(tblQuery, fields=fields, where=where, isDistinct=True) return db.query(stmt)
def loadItems(self): items = [] if self._orgStructure and self._street is not None: items.append((None, u'<все>')) osList = getOrgStructureDescendants(self._orgStructure) for item in self._db.getRecordList( self._tbl, (self._tbl_ah['id'], self._tbl_ah['number'], self._tbl_ah['corpus']), self._db.joinAnd( (self._tbl_osa['master_id'].inlist(osList), self._tbl_ah['KLADRStreetCode'].eq(self._street)))): number = forceString(item.value('number')) corpus = forceString(item.value('corpus')) items.append((forceInt(item.value('id')), (number + ((u'к' + corpus) if corpus else '')) or u'<пусто>')) return items
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 selectBefor(begDate, endDate, orgStructureId, personId, rowGrouping): db = QtGui.qApp.db tableDiagnosis = db.table('Diagnosis') tableClientDispanser = db.table('rbDispanser') tablePerson = db.table('Person') additionalFrom = '' order = '' if rowGrouping == 0: # by orgStructureId groupField = 'Person.orgStructure_id' elif rowGrouping == 1: # by speciality_id groupField = 'Person.speciality_id' elif rowGrouping == 2: # by personId groupField = 'Diagnosis.person_id' elif rowGrouping == 3: groupField = 'CONCAT_WS(\' | \', Diagnosis.MKB, MKB_Tree.DiagName)' additionalFrom += u' INNER JOIN MKB_Tree ON MKB_Tree.DiagID = Diagnosis.MKB' order += 'ORDER BY MKB_Tree.DiagID' stmt = u'''SELECT %s as rowKey, count(if(rbDispanser.code = 1, Diagnosis.client_id, NULL)) AS takeBefore FROM Diagnosis INNER JOIN rbDispanser ON rbDispanser.id = Diagnosis.dispanser_id LEFT JOIN Person ON Person.id = Diagnosis.person_id %s WHERE %s GROUP BY rowKey %s''' cond = [] cond.append(tableDiagnosis['mod_id'].isNull()) cond.append(tableClientDispanser['observed'].eq(1)) cond.append(tableDiagnosis['setDate'].dateLe(begDate)) cond.append(tableDiagnosis['endDate'].dateGe(begDate)) cond.append(tableDiagnosis['deleted'].eq(0)) if personId: cond.append(tableDiagnosis['person_id'].eq(personId)) elif orgStructureId: cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) else: cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId())) return db.query(stmt % (groupField, additionalFrom, (db.joinAnd(cond)), order))
def selectDataIsInStationary(begDate, endDate, orgStructureId): db = QtGui.qApp.db tableAction = db.table('Action') tableLeavedAction = db.table('Action').alias('LeavedAction') tableOrgStructure = db.table('OrgStructure') cond = [ tableAction['begDate'].dateLe(endDate), tableAction['begDate'].dateGe(begDate), tableAction['deleted'].eq(0) ] if orgStructureId: cond.append(tableOrgStructure['id'].inlist( getOrgStructureDescendants(orgStructureId))) stmt = u'''SELECT OrgStructure.code , Diagnosis.MKB , count(DISTINCT if(LeavedAction.begDate IS NULL OR %s, LeavedAction.id, NULL)) AS count FROM Action INNER JOIN ActionType ON Action.actionType_id = ActionType.id AND ActionType.flatCode = 'received' INNER JOIN ActionProperty ON ActionProperty.action_id = Action.id INNER JOIN ActionPropertyType ON ActionProperty.type_id = ActionPropertyType.id AND ActionPropertyType.name = 'Направлен в отделение' INNER JOIN ActionProperty_OrgStructure ON ActionProperty_OrgStructure.id = ActionProperty.id INNER JOIN OrgStructure ON OrgStructure.id = ActionProperty_OrgStructure.value INNER JOIN Event ON Event.id = Action.event_id INNER JOIN Diagnostic ON Diagnostic.event_id = Event.id INNER JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id INNER JOIN rbDiagnosisType ON rbDiagnosisType.id = Diagnostic.diagnosisType_id AND rbDiagnosisType.code = 7 LEFT JOIN ActionType LeavedActionType ON LeavedActionType.deleted = 0 AND LeavedActionType.flatCode = 'leaved' LEFT JOIN Action LeavedAction ON LeavedAction.event_id = Event.id AND LeavedAction.actionType_id = LeavedActionType.id WHERE Action.deleted = 0 AND Event.deleted = 0 AND Diagnostic.deleted = 0 AND Diagnosis.deleted = 0 AND %s GROUP BY OrgStructure.id , Diagnosis.MKB ''' return db.query( stmt % (tableLeavedAction['begDate'].dateGe(endDate), db.joinAnd(cond)))
def selectEvents(begDate, endDate, orgStructureId, personId, rowGrouping): db = QtGui.qApp.db tableEvent = db.table('Event') tablePerson = db.table('Person') additionalFrom = '' order = '' if rowGrouping == 0: # by orgStructureId groupField = 'Person.orgStructure_id' elif rowGrouping == 1: # by speciality_id groupField = 'Person.speciality_id' elif rowGrouping == 2: # by personId groupField = 'Event.execPerson_id' elif rowGrouping == 3: groupField = 'CONCAT_WS(\' | \', Diagnosis.MKB, MKB_Tree.DiagName)' additionalFrom += u''' INNER JOIN Diagnostic ON Diagnostic.event_id = Event.id AND Diagnostic.deleted = 0 INNER JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id AND Diagnosis.deleted = 0 INNER JOIN MKB_Tree ON MKB_Tree.DiagID = Diagnosis.MKB''' order += 'ORDER BY MKB_Tree.DiagID' stmt = u'''SELECT %s as rowKey, count(Event.id) AS event FROM Event INNER JOIN EventType ON EventType.id = Event.eventType_id AND EventType.code = '03' LEFT JOIN Person ON Person.id = Event.execPerson_id %(insert)s WHERE %s GROUP BY rowKey %s''' cond = [] cond.append(tableEvent['execDate'].dateLe(endDate)) cond.append(tableEvent['execDate'].dateGe(begDate)) cond.append(tableEvent['deleted'].eq(0)) if personId: cond.append(tableEvent['execPerson_id'].eq(personId)) elif orgStructureId: cond.append(tablePerson['orgStructure_id'].inlist( getOrgStructureDescendants(orgStructureId))) else: cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId())) return db.query(stmt % (groupField, additionalFrom, (db.joinAnd(cond)), order))
def selectData(params): db = QtGui.qApp.db begDate = forceDate(params.get('begDate')) endDate = forceDate(params.get('endDate')) orgStructureId = forceInt(params.get('orgStructureId')) forming = forceInt(params.get('forming')) open = forceBool(params.get('open')) select = '' where = '' innerJoin = '' groupBy = '' if forming == 0: select += '''CONCAT(Person.lastName, Person.firstName, Person.patrName) AS name,''' groupBy += '''Person.lastName''' if orgStructureId: orgStructureIdList = forceString(getOrgStructureDescendants(orgStructureId)) orgStructureIdList = orgStructureIdList.replace('[', '(') orgStructureIdList = orgStructureIdList.replace(']', ')') where += ''' AND Person.orgStructure_id IN %s''' % orgStructureIdList elif forming == 1: select += '''OrgStructure.name, OrgStructure.parent_id,''' innerJoin += '''INNER JOIN OrgStructure ON OrgStructure.id = Person.orgStructure_id AND OrgStructure.deleted = 0''' groupBy += '''OrgStructure.name''' if begDate: where += ''' AND DATE(TempInvalid.endDate) >= DATE('%s')''' % begDate.toString('yyyy-MM-dd') if endDate: where += ''' AND DATE(TempInvalid.endDate) <= DATE('%s')''' % endDate.toString('yyyy-MM-dd') if not open: where += ''' AND TempInvalid.closed = 1''' stmt = u''' SELECT %s COUNT(DISTINCT TempInvalid.id) AS countTempInvalid, SUM(TIMESTAMPDIFF(DAY, TempInvalid.begDate, TempInvalid.endDate)) AS sumDay FROM Person INNER JOIN TempInvalid ON TempInvalid.person_id = Person.id AND TempInvalid.deleted = 0 %s WHERE Person.deleted = 0 %s GROUP BY %s ''' % (select, innerJoin, where, groupBy) return db.query(stmt)