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)