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)
Beispiel #2
0
    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)
Beispiel #4
0
 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
Beispiel #5
0
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))
Beispiel #6
0
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
Beispiel #7
0
    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'])
Beispiel #8
0
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)))
Beispiel #9
0
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)
Beispiel #10
0
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)))
Beispiel #11
0
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)
Beispiel #12
0
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)
Beispiel #14
0
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
Beispiel #16
0
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)
Beispiel #17
0
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
Beispiel #19
0
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)))
Beispiel #20
0
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)))
Beispiel #22
0
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)))
Beispiel #23
0
        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
Beispiel #24
0
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
Beispiel #26
0
    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()
Beispiel #27
0
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))
Beispiel #28
0
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)))
Beispiel #29
0
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))
Beispiel #30
0
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)