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