def getRecordListAdditionalService(self, params, actionType): statusObservation = params.get('statusObservation', None) osPropertyNameList = [u'Отделение пребывания%'] db = QtGui.qApp.db self.tables.Event = db.table('Event') tableStatusObservation= db.table('Client_StatusObservation') self.items = [] cols = self.getQueryCols(statusObservation=True, orgStructurePropertyNameList=osPropertyNameList, PWS=False, dateFeedFlag=True) movingActionTable = self.tables.Action.alias('MovingAction') queryTable = self.tables.Action.innerJoin(self.tables.Event, self.tables.Action['event_id'].eq(self.tables.Event['id'])) queryTable = queryTable.innerJoin(self.tables.Client, self.tables.Event['client_id'].eq(self.tables.Client['id'])) queryTable = queryTable.innerJoin(movingActionTable, [movingActionTable['event_id'].eq(self.tables.Event['id']), movingActionTable['actionType_id'].inlist(getActionTypeIdListByFlatCode('moving%')), movingActionTable['deleted'].eq(0), db.joinOr([movingActionTable['begDate'].le(self.tables.Action['endDate']), self.tables.Action['endDate'].isNull()]), db.joinOr([movingActionTable['endDate'].ge(self.tables.Action['begDate']), movingActionTable['endDate'].isNull()]) ]) cond = [self.tables.Action['actionType_id'].inlist(getActionTypeIdListByFlatCode('%s%%' %actionType)), self.tables.Action['deleted'].eq(0), self.tables.Event['deleted'].eq(0), self.tables.Client['deleted'].eq(0) ] queryTable = self.compileFinanceCols(cols, cond, queryTable, params.get('financeId', None)) begDateTime = params.get('begDateTime', None) endDateTime = params.get('endDateTime', None) changingDayTime = params.get('changingDayTime', QtCore.QTime(0, 0)) if not begDateTime: begDateTime = QtCore.QDateTime().currentDateTime() cond.append(db.joinOr([self.tables.Action['endDate'].datetimeGe(begDateTime), self.tables.Action['endDate'].isNull()])) if endDateTime: cond.append(self.tables.Action['begDate'].isNotNull()) cond.append(self.tables.Action['begDate'].datetimeLe(endDateTime)) else: cond.append(movingActionTable['status'].ne(2)) cond.append(movingActionTable['endDate'].isNull()) personId = params.get('personId', None) if personId: cond.append(self.tables.Event['execPerson_id'].eq(personId)) permanent = params.get('permanent', None) typeId = params.get('typeId', None) profileId = params.get('profileId', None) clientLocation = params.get('clientLocation', 0) if clientLocation == 2: cond.append('''NOT %s'''%(getDataAPHB())) elif clientLocation == 1: cond.append('''%s'''%(getDataAPHB(permanent, typeId, profileId))) else: if (permanent and permanent > 0) or (typeId) or (profileId): cond.append('''%s'''%(getDataAPHB(permanent, typeId, profileId))) queryTable, cond = self.getCondByFilters(queryTable, cond, params) if statusObservation: queryTable = queryTable.innerJoin(tableStatusObservation, tableStatusObservation['master_id'].eq(self.tables.Client['id'])) cond.append(tableStatusObservation['deleted'].eq(0)) cond.append(tableStatusObservation['statusObservationType_id'].eq(statusObservation)) return db.getRecordList(queryTable, cols, cond, u'Client.lastName, Client.firstName, Client.patrName')
def getCondAndQueryTable(self, flatCodeCond, AT = False, APT = 0, AP = 0, APOS = 0, OS = 0, PWS = False, ET = 0, medicalAidTypeCond = True, Ord=0): db = QtGui.qApp.db cond = [self.tables.Action['deleted'].eq(0), self.tables.Action['actionType_id'].inlist(getActionTypeIdListByFlatCode(flatCodeCond)) ] if medicalAidTypeCond: cond.append(u'''(EventType.medicalAidType_id IN (SELECT rbMedicalAidType.id from rbMedicalAidType where rbMedicalAidType.code IN (\'1\', \'2\', \'3\', \'7\', \'10\')))''') queryTable = self.tables.Action.innerJoin(self.tables.Event, db.joinAnd([self.tables.Action['event_id'].eq(self.tables.Event['id']), self.tables.Event['deleted'].eq(0)])) if AT: queryTable = queryTable.innerJoin(self.tables.ActionType, self.tables.ActionType['id'].eq(self.tables.Action['actionType_id'])) queryTable = queryTable.innerJoin(self.tables.Client, db.joinAnd([self.tables.Event['client_id'].eq(self.tables.Client['id']), self.tables.Client['deleted'].eq(0)])) if PWS: queryTable = queryTable.leftJoin(self.tables.PWS, self.tables.PWS['id'].eq(self.tables.Action['person_id'])) if APT == 2: queryTable = queryTable.leftJoin(self.tables.APT, self.tables.APT['actionType_id'].eq(self.tables.ActionType['id'])) elif APT == 1: queryTable = queryTable.innerJoin(self.tables.APT, self.tables.APT['actionType_id'].eq(self.tables.ActionType['id'])) if AP == 2: queryTable = queryTable.leftJoin(self.tables.AP, db.joinAnd([self.tables.AP['type_id'].eq(self.tables.APT['id']), self.tables.AP['action_id'].eq(self.tables.Action['id']), db.joinOr([self.tables.AP['deleted'].eq(0), self.tables.AP['id'].isNull()])])) elif AP == 1: queryTable = queryTable.innerJoin(self.tables.AP, db.joinAnd([self.tables.AP['deleted'].eq(0), self.tables.AP['type_id'].eq(self.tables.APT['id']), self.tables.AP['action_id'].eq(self.tables.Action['id'])])) if APOS == 2: queryTable = queryTable.leftJoin(self.tables.APOS, self.tables.APOS['id'].eq(self.tables.AP['id'])) elif APOS == 1: queryTable = queryTable.innerJoin(self.tables.APOS, self.tables.APOS['id'].eq(self.tables.AP['id'])) if OS == 2: queryTable = queryTable.leftJoin(self.tables.OS, self.tables.OS['id'].eq(self.tables.APOS['value'])) elif OS == 1: queryTable = queryTable.innerJoin(self.tables.OS, self.tables.OS['id'].eq(self.tables.APOS['value'])) if ET == 1: queryTable = queryTable.innerJoin(self.tables.EventType, self.tables.Event['eventType_id'].eq(self.tables.EventType['id'])) elif ET == 2: queryTable = queryTable.leftJoin(self.tables.EventType, self.tables.Event['eventType_id'].eq(self.tables.EventType['id'])) if Ord == 1: queryTable = queryTable.innerJoin(self.tables.OrdAPT, db.joinAnd([ self.tables.ActionType['id'].eq(self.tables.OrdAPT['actionType_id']) , self.tables.ActionType['name'].eq(u'поступление') , self.tables.OrdAPT['name'].eq(u'кем доставлен') , self.tables.OrdAPT['deleted'].eq(0) ])) queryTable = queryTable.innerJoin(self.tables.OrdAP, db.joinAnd([ self.tables.OrdAP['deleted'].eq(0) , self.tables.Action['id'].eq(self.tables.OrdAP['action_id']) , self.tables.OrdAPT['id'].eq(self.tables.OrdAP['type_id']) ])) queryTable = queryTable.innerJoin(self.tables.OrdAPS, self.tables.OrdAP['id'].eq(self.tables.OrdAPS['id'])) elif Ord == 2: queryTable = queryTable.leftJoin(self.tables.OrdAPT, db.joinAnd([ self.tables.ActionType['id'].eq(self.tables.OrdAPT['actionType_id']) , self.tables.ActionType['name'].eq(u'поступление') , self.tables.OrdAPT['name'].eq(u'кем доставлен') , self.tables.OrdAPT['deleted'].eq(0) ])) queryTable = queryTable.leftJoin(self.tables.OrdAP, db.joinAnd([ self.tables.OrdAP['deleted'].eq(0) , self.tables.Action['id'].eq(self.tables.OrdAP['action_id']) , self.tables.OrdAPT['id'].eq(self.tables.OrdAP['type_id']) ])) queryTable = queryTable.leftJoin(self.tables.OrdAPS, self.tables.OrdAP['id'].eq(self.tables.OrdAPS['id'])) return queryTable, cond
def findReceivedNoEnd(orgStructureIdList=None): if not orgStructureIdList: orgStructureIdList = [] cols = self.getQueryCols( MKB=True, statusObservation=True, dateFeedFlag=True, orgStructurePropertyNameList=[u'Направлен в отделение'], OSHBProfile=True, provisionalDiagnosis=True, admissionDiagnosis=True, params=params) comfortableIdList = getActionTypeIdListByFlatCode(u'comfortable%') if comfortableIdList: cols.append(getComfortableColStmt(comfortableIdList)) cols.append( getEventPhysicalActivity( db.formatDate(QtCore.QDate.currentDate()), 'physicalActivityName')) cols.append(getExistsNonPayedActions()) cols.append(getDataOrgStructureId([u'Направлен в отделение'])) queryTable, cond = self.getCondAndQueryTable(u'received%', PWS=True, ET=1, AP=2, AT=2, APT=2) cols.append( # u'QuotaType.`code`' CQuotaColQuery.QUOTA_COL) #cols.append(u'QuotaType.`code`') queryTable, cond = self.compileCommonFilter( params, queryTable, cond) queryTable = self.compileFinanceCols(cols, cond, queryTable, params.get('financeId', 0), True) queryTable, cond = self.getCondByFilters(queryTable, cond, params) cond.append(self.tables.Action['endDate'].isNull()) if orgStructureIdList: cond.append( db.joinOr([ getDataOrgStructure(u'Направлен в отделение', orgStructureIdList), 'NOT %s' % (getActionPropertyTypeName(u'Направлен в отделение')) ])) presenceDay = params.get('presenceDay', 0) if presenceDay: datePresence = QtCore.QDate.currentDate().addDays(-presenceDay) cond.append(self.tables.Action['begDate'].dateLe(datePresence)) else: cond.append(self.tables.Action['begDate'].dateLe( QtCore.QDate.currentDate())) dateFeed = params.get('dateFeed', QtCore.QDate.currentDate()) queryTable, cols, cond = self.compileFeedQuery( dateFeed, queryTable, cols, cond) orderBY = getOrderBy() return db.getRecordList(queryTable, cols, cond, orderBY, isDistinct=True)
def getDataMoving(orgStructureIdList=None): if not orgStructureIdList: orgStructureIdList = [] currentDate = QtCore.QDate.currentDate() cols = self.getQueryCols(MKB=True, statusObservation=True, dateFeedFlag=True, OSHB=True, OSHBProfile=True, nameOS=True, patronage=True, provisionalDiagnosis=True, admissionDiagnosis=True, params=params) cols.append( getEventPhysicalActivity(db.formatDate(currentDate), 'physicalActivityName')) cols.append(u'OrgStructure.id AS idOS') comfortableIdList = getActionTypeIdListByFlatCode(u'comfortable%') if comfortableIdList: cols.append(getComfortableColStmt(comfortableIdList)) cols.append(getExistsNonPayedActions()) queryTable, cond = self.getCondAndQueryTable(u'moving%', AT=True, APT=2, AP=2, APOS=2, OS=2, PWS=True, ET=1) cols.append( # u'QuotaType.`code`' CQuotaColQuery.QUOTA_COL) queryTable, cond = self.compileCommonFilter( params, queryTable, cond) queryTable, cond = self.getCondByFilters(queryTable, cond, params, withoutAPHB=True) queryTable = self.compileFinanceCols(cols, cond, queryTable, params.get('financeId', 0)) if orgStructureIdList: cond.append(self.tables.OS['deleted'].eq(0)) cond.append( getDataOrgStructure(u'Отделение пребывания', orgStructureIdList)) cond.append(self.tables.APT['name'].like(u'Отделение пребывания')) cond.append(self.tables.Action['endDate'].isNull()) presenceDay = params.get('presenceDay', 0) if presenceDay: datePresence = currentDate.addDays(-presenceDay) cond.append(self.tables.Action['begDate'].dateLe(datePresence)) else: cond.append(self.tables.Action['begDate'].dateLe(currentDate)) permanent = params.get('permanent', 0) typeId = params.get('typeId', 0) profile = params.get('profileId', 0) localClient = params.get('clientLocation', 0) codeBeds = params.get('codeBeds', None) if localClient == 2: cond.append('''NOT %s''' % (getDataAPHB())) elif localClient == 1: cond.append( '''%s''' % (getDataAPHB(permanent, typeId, profile, codeBeds))) else: if (permanent and permanent > 0) or (typeId) or (profile) or codeBeds: cond.append( '''%s''' % (getDataAPHB(permanent, typeId, profile, codeBeds))) dateFeed = params.get('dateFeed', QtCore.QDate.currentDate()) queryTable, cols, cond = self.compileFeedQuery( dateFeed, queryTable, cols, cond) orderBY = getOrderBy(True) return db.getRecordList(queryTable, cols, cond, orderBY, isDistinct=True)
def loadData(self, params): statusObservation = params.get('statusObservation', None) osPropertyNameList = [u'Отделение пребывания%'] db = QtGui.qApp.db self.tables.Event = db.table('Event') tableStatusObservation = db.table('Client_StatusObservation') self.items = [] cols = self.getQueryCols( statusObservation=True, orgStructurePropertyNameList=osPropertyNameList, PWS=False, dateFeedFlag=True) cols.append(getDataOrgStructureId(osPropertyNameList)) reanimationActionTable = self.tables.Action.alias('reanimationAction') queryTable = self.tables.Action.innerJoin( self.tables.Event, self.tables.Action['event_id'].eq(self.tables.Event['id'])) queryTable = queryTable.innerJoin( self.tables.Client, self.tables.Event['client_id'].eq(self.tables.Client['id'])) queryTable = queryTable.innerJoin(reanimationActionTable, [ reanimationActionTable['event_id'].eq(self.tables.Event['id']), reanimationActionTable['actionType_id'].inlist( getActionTypeIdListByFlatCode('reanimation%')), reanimationActionTable['deleted'].eq(0), db.joinOr([ reanimationActionTable['begDate'].le( self.tables.Action['endDate']), self.tables.Action['endDate'].isNull() ]), db.joinOr([ reanimationActionTable['endDate'].ge( self.tables.Action['begDate']), reanimationActionTable['endDate'].isNull() ]) ]) cols.append( getDataOrgStructureName( [u'Отделение%'], fieldAlias='currentCommonOSName', actionTableAlias=reanimationActionTable.aliasName)) cols.append( getAPOSNameField(reanimationActionTable, u'Переведен из%', fieldAlias='nameFromOS')) cond = [ self.tables.Action['actionType_id'].inlist( getActionTypeIdListByFlatCode('reanimation%')), self.tables.Action['deleted'].eq(0), self.tables.Event['deleted'].eq(0), self.tables.Client['deleted'].eq(0) # self.tables.Action['endDate'].isNull() # self.tables.Action['status'].ne(2) ] queryTable = self.compileFinanceCols(cols, cond, queryTable, params.get('financeId', None)) begDateTime = params.get('begDateTime', None) endDateTime = params.get('endDateTime', None) changingDayTime = params.get('changingDayTime', QtCore.QTime(0, 0)) orgStructureIndex = params.get('orgStructureIndex', None) if orgStructureIndex: treeItem = orgStructureIndex.internalPointer( ) if orgStructureIndex.isValid() else None orgStructureIdList = getOrgStructureIdList( orgStructureIndex) if treeItem and treeItem._id else [] if orgStructureIdList: cond.append( getDataOrgStructure(osPropertyNameList, orgStructureIdList)) if not begDateTime: begDateTime = QtCore.QDate.currentDate() cond.append( db.joinOr([ self.tables.Action['endDate'].datetimeGe(begDateTime), self.tables.Action['endDate'].isNull() ])) if endDateTime: cond.append(self.tables.Action['begDate'].isNotNull()) cond.append(self.tables.Action['begDate'].datetimeLe(endDateTime)) else: cond.append(reanimationActionTable['status'].ne(2)) cond.append(reanimationActionTable['endDate'].isNull()) personId = params.get('personId', None) if personId: cond.append(self.tables.Event['execPerson_id'].eq(personId)) permanent = params.get('permanent', None) typeId = params.get('typeId', None) profileId = params.get('profileId', None) clientLocation = params.get('clientLocation', 0) if clientLocation == 2: cond.append('''NOT %s''' % (getDataAPHB())) elif clientLocation == 1: cond.append('''%s''' % (getDataAPHB(permanent, typeId, profileId))) else: if (permanent and permanent > 0) or (typeId) or (profileId): cond.append('''%s''' % (getDataAPHB(permanent, typeId, profileId))) queryTable, cond = self.getCondByFilters(queryTable, cond, params) if statusObservation: queryTable = queryTable.innerJoin( tableStatusObservation, tableStatusObservation['master_id'].eq( self.tables.Client['id'])) cond.append(tableStatusObservation['deleted'].eq(0)) cond.append(tableStatusObservation['statusObservationType_id'].eq( statusObservation)) reanimationRecords = db.getRecordList( queryTable, cols, cond, u'Client.lastName, Client.firstName, Client.patrName') for record in reanimationRecords: item = self.getItemFromRecord(record) item.update({ 'reanimationBegDate': forceDateTime( record.value('begDate')).toString('dd.MM.yyyy hh:mm') }) item.update( {'nameFromOS': forceString(record.value('nameFromOS'))}) self.items.append(item) self.reset()
def getReview(params, orgStructureIdList): filterBegDateTime = params.get('begDateTime', None) filterEndDateTime = params.get('endDateTime', None) changingDayTime = params.get('changingDayTime', QtCore.QTime(0, 0)) cols = self.getQueryCols( orgStructurePropertyNameList=[u'Приемное отделение']) #cols.append(u'''APS_R.value AS renunciation''') #cols.append(u'''IF(TRIM(APS_D.value) = 'да', 1, 0) AS direction''') #cols.append(u'''IF(TRIM(APS_Dt.value) = 'да', 1, 0) AS death''') cols.append(u'''EXISTS(SELECT e.id FROM Event e INNER JOIN EventType ON EventType.id = e.eventType_id INNER JOIN rbMedicalAidType ON (rbMedicalAidType.id = EventType.medicalAidType_id AND rbMedicalAidType.federalCode IN (1, 2, 3)) WHERE e.client_id = Event.client_id AND e.id > Event.id) AS directionExecuted''' ) queryTable = self.tables.Action.innerJoin( self.tables.ActionType, db.joinAnd([ self.tables.ActionType['id'].eq( self.tables.Action['actionType_id']), self.tables.Action['actionType_id'].inlist( getActionTypeIdListByFlatCode(u'inspectPigeonHole%')) ])) queryTable = queryTable.innerJoin( self.tables.Event, self.tables.Action['event_id'].eq(self.tables.Event['id'])) queryTable = queryTable.innerJoin( self.tables.Client, self.tables.Event['client_id'].eq(self.tables.Client['id'])) queryTable = queryTable.leftJoin( self.tables.PWS, self.tables.PWS['id'].eq(self.tables.Action['person_id'])) lobbyResult = params.get('lobbyResult', 0) # Отказы if not lobbyResult or lobbyResult == 1: tblAPT_R = self.tables.APT.alias('APT_R') tblAP_R = self.tables.AP.alias('AP_R') tblAPS_R = self.tables.APS.alias('APS_R') queryTable = queryTable.leftJoin( tblAPT_R, db.joinAnd([ tblAPT_R['deleted'].eq(0), tblAPT_R['actionType_id'].eq( self.tables.ActionType['id']), tblAPT_R['name'].like(u'Причина отказа%') ])) queryTable = queryTable.leftJoin( tblAP_R, db.joinAnd([ tblAP_R['deleted'].eq(0), tblAP_R['type_id'].eq(tblAPT_R['id']), tblAP_R['action_id'].eq(self.tables.Action['id']) ])) queryTable = queryTable.leftJoin( tblAPS_R, tblAPS_R['id'].eq(tblAP_R['id'])) cols.append(u'''APS_R.value AS renunciation''') # Направление на госпитализацию if not lobbyResult or lobbyResult == 2: tblAPT_D = self.tables.APT.alias('APT_D') tblAP_D = self.tables.AP.alias('AP_D') tblAPS_D = self.tables.APS.alias('APS_D') queryTable = queryTable.leftJoin( tblAPT_D, db.joinAnd([ tblAPT_D['deleted'].eq(0), tblAPT_D['actionType_id'].eq( self.tables.ActionType['id']), tblAPT_D['name'].like(u'Направлен на госпитализацию%') ])) queryTable = queryTable.leftJoin( tblAP_D, db.joinAnd([ tblAP_D['deleted'].eq(0), tblAP_D['type_id'].eq(tblAPT_D['id']), tblAP_D['action_id'].eq(self.tables.Action['id']) ])) queryTable = queryTable.leftJoin( tblAPS_D, tblAPS_D['id'].eq(tblAP_D['id'])) cols.append( u'''IF(TRIM(APS_D.value) = 'да', 1, 0) AS direction''') # Смерть if not lobbyResult or lobbyResult == 3: tblAPT_Dt = self.tables.APT.alias('APT_Dt') tblAP_Dt = self.tables.AP.alias('AP_Dt') tblAPS_Dt = self.tables.APS.alias('APS_Dt') queryTable = queryTable.leftJoin( tblAPT_Dt, db.joinAnd([ tblAPT_Dt['deleted'].eq(0), tblAPT_Dt['actionType_id'].eq( self.tables.ActionType['id']), tblAPT_Dt['name'].like(u'Умер%') ])) queryTable = queryTable.leftJoin( tblAP_Dt, db.joinAnd([ tblAP_Dt['deleted'].eq(0), tblAP_Dt['type_id'].eq(tblAPT_Dt['id']), tblAP_Dt['action_id'].eq(self.tables.Action['id']) ])) queryTable = queryTable.leftJoin( tblAPS_Dt, tblAPS_Dt['id'].eq(tblAP_Dt['id'])) cols.append( u'''IF(TRIM(APS_Dt.value) = 'да', 1, 0) AS death''') cond = [ self.tables.Action['deleted'].eq(0), self.tables.Event['deleted'].eq(0), self.tables.Client['deleted'].eq(0) ] #TODO: Как получать дату начала стационарного события?? #tableEventType = db.table('EventType') #queryTable = queryTable.innerJoin(tableEventType, tableEvent['eventType_id'].eq(tableEventType['id'])) # cond.append(u'''(EventType.medicalAidType_id IN (SELECT rbMedicalAidType.id from rbMedicalAidType where rbMedicalAidType.code IN (\'1\', \'2\', \'3\', \'7\')))''') #if personId: # cond.append(self.tables.Action['person_id'].eq(personId)) if params.get('statusObservation', None): queryTable = queryTable.innerJoin( tableStatusObservation, tableStatusObservation['master_id'].eq( self.tables.Client['id'])) cond.append(tableStatusObservation['deleted'].eq(0)) cond.append( tableStatusObservation['statusObservationType_id'].eq( self.statusObservation)) queryTable, cond = self.compileCommonFilter(params, queryTable, cond, actionPerson=True) if orgStructureIdList: cond.append( db.joinOr([ getDataOrgStructure(u'Приемное отделение', orgStructureIdList), 'NOT %s' % (getActionPropertyTypeName(u'Приемное отделение')) ])) if filterBegDateTime: cond.append( db.joinOr([ self.tables.Action['begDate'].isNull(), self.tables.Action['begDate'].datetimeGe( filterBegDateTime) ])) if filterEndDateTime: cond.append( db.joinOr([ self.tables.Action['begDate'].isNull(), self.tables.Action['begDate'].datetimeLe( filterEndDateTime) ])) orderBy = getOrderBy() records = db.getRecordList(queryTable, cols, cond, orderBy, isDistinct=True) return records