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, 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, 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 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 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 createQuery(self, begDate, endDate, eventTypeId, onlyPermanentAttach):
""" Запрос информации по событиям"""
db = self.db
tableEvent = db.table('Event')
tableClient = db.table('Client')
tableActionType = db.table('ActionType')
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 onlyPermanentAttach:
cond.append(havePermanentAttach(endDate))
condStr = db.joinAnd(cond)
stmt = '''
SELECT Event.`id`,
Event.`client_id`,
Event.`setDate`,
Visit.`createDatetime` AS `visitCreateDate`,
Visit.`Date` AS `visitDate`,
Visit.`person_id` AS `visitPersonId`,
Visit.`id` as `visitId`,
ActionProperty.id AS `complainId`,
ActionPropertyType.typeName AS `complainTypeName`,
AP2.id AS `tempId`,
APT2.typeName AS `tempTypeName`
FROM Event
LEFT JOIN Visit ON Visit.event_id = Event.id
LEFT JOIN `Action` ON Event.id = `Action`.event_id
LEFT JOIN ActionProperty ON `Action`.id = ActionProperty.action_id
LEFT JOIN ActionPropertyType ON ActionProperty.type_id = ActionPropertyType.id
LEFT JOIN ActionProperty AP2 ON `Action`.id = AP2.action_id
LEFT JOIN ActionPropertyType APT2 ON AP2.type_id = APT2.id
WHERE `Action`.deleted = 0 AND ActionProperty.deleted = 0 AND AP2.deleted = 0
AND %s ''' % condStr
stmt += ' AND ActionProperty.type_id IN (' \
+ ', '.join('%d' % x for x in self.complainTypeIdList) + ')'
stmt += ' AND AP2.type_id IN (' \
+ ', '.join('%d' % x for x in self.tempTypeIdList) + ')'
query = db.query(stmt)
return query
def selectData(begDate, endDate, eventTypeId, onlyPermanentAttach,
onlyPayedEvents, begPayDate, endPayDate, primaryMarkIndex):
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 onlyPermanentAttach:
cond.append(havePermanentAttach(endDate))
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))
if primaryMarkIndex != 0:
cond.append(tableEvent['isPrimary'].eq(primaryMarkIndex))
stmt = stmt % db.joinAnd(cond)
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 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, 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 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(begDate, endDate, eventTypeId, sex, ageFrom, ageTo,
actionTypeClass, actionTypeId, onlyPermanentAttach, MKBFilter,
MKBFrom, MKBTo, onlyPayedEvents, begPayDate, endPayDate):
db = QtGui.qApp.db
tableEvent = db.table('Event')
tableClient = db.table('Client')
tableActionType = db.table('ActionType')
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 sex:
cond.append(tableClient['sex'].eq(sex))
if ageFrom <= ageTo:
cond.append(
'Event.execDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' %
ageFrom)
cond.append(
'Event.execDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)'
% (ageTo + 1))
if actionTypeId:
cond.append(tableActionType['id'].inlist(
getActionTypeDescendants(actionTypeId, actionTypeClass)))
elif actionTypeClass is not None:
cond.append(tableActionType['class'].eq(actionTypeClass))
if onlyPermanentAttach:
cond.append(havePermanentAttach(endDate))
if MKBFilter:
tableDiagnostic = db.table('Diagnostic')
tableDiagnosis = db.table('Diagnosis')
subQueryTable = tableDiagnosis.leftJoin(
tableDiagnostic,
tableDiagnosis['id'].eq(tableDiagnostic['diagnosis_id']))
subCond = [
tableDiagnostic['event_id'].eq(tableEvent['id']),
tableDiagnosis['MKB'].between(MKBFrom, MKBTo)
]
cond.append(db.existsStmt(subQueryTable, subCond))
if onlyPayedEvents:
cond.append('isEventPayed(Event.id)')
tableAccountItem = db.table('Account_Item')
addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate)
condStr = db.joinAnd(cond)
stmt = """
SELECT
Client.lastName, Client.firstName, Client.patrName,
Client.birthDate, Client.sex,
formatClientAddress(ClientAddress.id) AS address,
ClientPolicy.serial AS policySerial, ClientPolicy.number AS policyNumber,
ClientDocument.serial AS documentSerial, ClientDocument.number AS documentNumber,
Client.SNILS,
Event.setDate AS date,
Action.office, Action.note,
Action.actionType_id,
ActionType.name AS actionTypeName
FROM
Action
INNER JOIN Event ON Action.event_id=Event.id
INNER JOIN Client ON Client.id=Event.client_id
INNER JOIN ActionType on ActionType.id=Action.actionType_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 ClientPolicy ON
ClientPolicy.client_id = Client.id AND
ClientPolicy.id = (SELECT MAX(CP.id) FROM ClientPolicy AS CP WHERE CP.client_id = Client.id)
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 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 Action.deleted=0 AND Event.deleted=0 AND %s
order BY ActionType.class, ActionType.group_id, ActionType.name, ActionType.id, Client.lastName, Client.firstName, Client.patrName
""" % (condStr)
return db.query(stmt)
def selectData(begDate, endDate, eventTypeId, sex, ageFrom, ageTo, actionTypeClass, actionTypeId, onlyPermanentAttach, MKBFilter, MKBFrom, MKBTo, onlyPayedEvents, begPayDate, endPayDate, detailPerson, personId, specialityId, orgStructureId, insurerId):
db = QtGui.qApp.db
tableEvent = db.table('Event')
tableClient = db.table('Client')
tableAction = db.table('Action')
tableActionType = db.table('ActionType')
tableActionTypeService = db.table('ActionType_Service')
tableService = db.table('rbService')
tablePerson = db.table('Person')
tableFinance = db.table('rbFinance')
tableContract = db.table('Contract')
tableContractTariff = db.table('Contract_Tariff')
tableOrgStructure = db.table('OrgStructure')
tableSpeciality = db.table('rbSpeciality')
tableAccountItem = db.table('Account_Item')
queryTable = tableEvent.leftJoin(tableAction, tableAction['event_id'].eq(tableEvent['id']))
queryTable = queryTable.leftJoin(tableClient, tableClient['id'].eq(tableEvent['client_id']))
queryTable = queryTable.leftJoin(tableActionType, tableActionType['id'].eq(tableAction['actionType_id']))
queryTable = queryTable.leftJoin(tableContract, 'Contract.`id`=IF(Action.`contract_id`, Action.`contract_id`, Event.`contract_id`)')
queryTable = queryTable.leftJoin(tableContractTariff, tableContractTariff['master_id'].eq(tableContract['id']))
actionTypeServiceJoinCond = 'IF(ActionType_Service.`finance_id`, ActionType_Service.`finance_id`=Contract.`finance_id`, ActionType_Service.`finance_id` IS NULL)'
queryTable = queryTable.leftJoin(tableActionTypeService,
[tableActionType['id'].eq(tableActionTypeService['master_id']),
actionTypeServiceJoinCond])
queryTable = queryTable.leftJoin(tableService, tableService['id'].eq(tableActionTypeService['service_id']))
queryTable = queryTable.leftJoin(tablePerson, tablePerson['id'].eq(tableAction['person_id']))
queryTable = queryTable.leftJoin(tableSpeciality, tableSpeciality['id'].eq(tablePerson['speciality_id']))
queryTable = queryTable.leftJoin(tableOrgStructure, tableOrgStructure['id'].eq(tablePerson['orgStructure_id']))
accountItemJoinCond = '(Account_Item.action_id = Action.id AND Account_Item.deleted=0 AND Account_Item.date IS NOT NULL AND Account_Item.refuseType_id IS NULL AND Account_Item.reexposeItem_id IS NULL AND Account_Item.visit_id IS NULL)'
queryTable = queryTable.leftJoin(tableAccountItem, accountItemJoinCond)
cond = [tableAction['endDate'].isNotNull(),
tableAction['deleted'].eq(0),
tableEvent['deleted'].eq(0),
tableContractTariff['service_id'].eq(tableActionTypeService['service_id'])]
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 sex:
cond.append(tableClient['sex'].eq(sex))
if ageFrom:
cond.append('Event.execDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)'%ageFrom)
if ageTo:
cond.append('Event.execDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)'%(ageTo+1))
if actionTypeId:
cond.append(tableActionType['id'].inlist(getActionTypeDescendants(actionTypeId, actionTypeClass)))
elif actionTypeClass is not None:
cond.append(tableActionType['class'].eq(actionTypeClass))
if onlyPermanentAttach:
cond.append(havePermanentAttach(endDate))
if MKBFilter:
tableDiagnostic = db.table('Diagnostic')
tableDiagnosis = db.table('Diagnosis')
subQueryTable = tableDiagnosis.leftJoin(tableDiagnostic, tableDiagnosis['id'].eq(tableDiagnostic['diagnosis_id']))
subCond = [ tableDiagnostic['event_id'].eq(tableEvent['id']),
tableDiagnosis['MKB'].between(MKBFrom, MKBTo)
]
cond.append(db.existsStmt(subQueryTable, subCond))
if onlyPayedEvents:
# cond.append('isEventPayed(Event.id)')
tableAccountItem = db.table('Account_Item')
addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate)
if not detailPerson:
if personId:
cond.append(tableAction['person_id'].eq(personId))
if specialityId:
cond.append(tablePerson['speciality_id'].eq(specialityId))
if orgStructureId:
orgStructureIdList = db.getDescendants('OrgStructure', 'parent_id', orgStructureId)
cond.append(tablePerson['orgStructure_id'].inlist(orgStructureIdList))
if insurerId:
#cond.append('EXISTS (SELECT ClientPolicy.`client_id` FROM ClientPolicy WHERE ClientPolicy.`insurer_id`=%d AND ClientPolicy.`client_id`=Client.`id`)' % insurerId)
cond.append(tableContract['payer_id'].eq(insurerId))
fieldUetDoctor = 'IF(YEAR(FROM_DAYS(DATEDIFF(Action.`endDate`, Client.`birthDate`))) < 18, rbService.`childUetDoctor`, rbService.`adultUetDoctor`) AS uetDoctor'
fieldUetAverageMedWorker = 'IF(YEAR(FROM_DAYS(DATEDIFF(Action.`endDate`, Client.`birthDate`))) < 18, rbService.`childUetAverageMedWorker`, rbService.`adultUetAverageMedWorker`) AS uetAverageMedWorker'
fields = [tableAction['id'].alias('actionId'),
tableAction['person_id'].name(),
tableAction['finance_id'].name(),
tableAction['payStatus'].alias('actionPayStatus'),
tableAction['amount'].alias('actionAmount'),
tableEvent['payStatus'].alias('eventPayStatus'),
tablePerson['lastName'].alias('personLastName'),
tablePerson['firstName'].alias('personFirstName'),
tablePerson['patrName'].alias('personPatrName'),
tableSpeciality['name'].alias('specialityName'),
tableActionType['code'].alias('actionTypeCode'),
tableActionType['name'].alias('actionTypeName'),
tableService['code'].alias('serviceCode'),
tableOrgStructure['id'].alias('orgStructureId'),
fieldUetDoctor,
fieldUetAverageMedWorker
]
order = ', '.join([tableAction['id'].name(),
tableActionTypeService['finance_id'].name()])
stmt = db.selectStmt(queryTable, fields, cond, order = order+' DESC', isDistinct = True)
return db.query(stmt)
def countBySanatoriumAndHospital(begDate, endDate, eventTypeId,
onlyPermanentAttach, onlyPayedEvents,
begPayDate, endPayDate):
stmt = """
SELECT
Organisation.OKVED as OKVED,
IF(
EXISTS (
SELECT
DS.id
FROM
Diagnostic AS DS
WHERE
DS.event_id = Event.id
AND DS.sanatorium>0
AND DS.diagnosisType_id IN (SELECT id FROM rbDiagnosisType WHERE code IN ('1','2'))
), 1, 0
) as sanatorium,
IF(
EXISTS (
SELECT
DH.id
FROM
Diagnostic AS DH
WHERE
DH.event_id = Event.id
AND DH.hospital>1
AND DH.diagnosisType_id IN (SELECT id FROM rbDiagnosisType WHERE code IN ('1','2'))
), 1, 0
) as hospital,
IF(
EXISTS (
SELECT
DHTA.id
FROM
Diagnostic AS DHTA
LEFT JOIN rbHealthGroup ON rbHealthGroup.id = DHTA.healthGroup_id
WHERE
DHTA.event_id = Event.id
AND DHTA.hospital>1
AND DHTA.diagnosisType_id IN (SELECT id FROM rbDiagnosisType WHERE code IN ('1','2'))
AND rbHealthGroup.code = '5'
), 1, 0
) as highTechAid,
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
%s
GROUP BY
OKVED, sanatorium, hospital, highTechAid
"""
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,
statusInPeriod,
statusStart,
statusFinish,
sex,
ageFrom,
ageTo,
groupId,
onlyPermanentAttach,
groupBySocStatus,
grbOrgStructure=False,
orgStructureIdList=None,
areaAddressType=None,
socStatusTypes=None,
outcome=False):
attachTypeId = forceRef(
QtGui.qApp.db.translate('rbAttachType', 'code', u'8', 'id'))
stmt = u"""
SELECT
Client.id as client_id,
Client.lastName,
Client.firstName,
Client.patrName,
if(Client.sex = 1, 'м', 'ж') AS sex,
Client.birthDate,
if(getClientRegAddressId(Client.id) IS NULL, 'не указан', getClientRegAddress(Client.id)) AS address,
(SELECT GROUP_CONCAT(rbContactType.name, ': ', ClientContact.contact)
FROM ClientContact
INNER JOIN rbContactType ON rbContactType.id = ClientContact.contactType_id
WHERE ClientContact.deleted = 0 AND ClientContact.client_id = Client.id
GROUP BY ClientContact.client_id) AS contact,
Client.SNILS,
if(ClientPolicy.id IS NULL, 'нет', CONCAT(ClientPolicy.serial, ' ', ClientPolicy.number, if(Organisation.id IS NULL, NULL, CONCAT(' выдан ', Organisation.shortName)), if(ClientPolicy.begDate IS NULL, NULL, CONCAT(' действителен c ', DATE_FORMAT(ClientPolicy.begDate, '%%d.%%m.%%Y'))), if(ClientPolicy.endDate IS NULL, NULL, CONCAT(' по ',DATE_FORMAT(ClientPolicy.endDate, '%%d.%%m.%%Y'))))) AS policy,
if(getClientDocumentId(Client.id) IS NULL, 'не указано', getClientDocument(Client.id)) AS document,
ClientSocStatus.begDate, ClientSocStatus.endDate, ClientSocStatus.document_id, ClientSocStatus.socStatusType_id,
rbSocStatusType.name as socStatusTypeName, rbSocStatusClass.name as socStatusClassName,
if(ClientAttach.attachType_id = %s, ClientAttach.begDate, NULL) as deathDate
FROM
Client
join ClientSocStatus on ClientSocStatus.client_id=Client.id
left join rbSocStatusType on rbSocStatusType.id=ClientSocStatus.socStatusType_id
left join rbSocStatusClassTypeAssoc on rbSocStatusClassTypeAssoc.type_id=rbSocStatusType.id
left join rbSocStatusClass on rbSocStatusClass.id=rbSocStatusClassTypeAssoc.class_id
left join ClientPolicy ON ClientPolicy.deleted = 0 AND ClientPolicy.id = getClientPolicyId(Client.id, 1)
left join Organisation ON Organisation.id = ClientPolicy.insurer_id
%s
WHERE Client.deleted=0 AND %s
ORDER BY %s Client.lastName, Client.firstName, Client.patrName
"""
db = QtGui.qApp.db
tableClient = db.table('Client')
tableClientSocStatus = db.table('ClientSocStatus')
tableSocStatusClass = db.table('rbSocStatusClass')
tableOrgStructureAddress = db.table('OrgStructure_Address')
tableClientAddress = db.table('ClientAddress')
tableAddress = db.table('Address')
cond = [tableClientSocStatus['deleted'].eq(0)]
if grbOrgStructure and orgStructureIdList:
houseIdList = db.getDistinctIdList(
tableOrgStructureAddress, tableOrgStructureAddress['house_id'],
[tableOrgStructureAddress['master_id'].inlist(orgStructureIdList)])
clientIdList = []
if houseIdList and areaAddressType != None:
clientIdList = db.getDistinctIdList(
tableAddress.innerJoin(
tableClientAddress,
tableClientAddress['address_id'].eq(tableAddress['id'])),
tableClientAddress['client_id'], [
tableAddress['house_id'].inlist(houseIdList),
tableAddress['deleted'].eq(0),
tableClientAddress['type'].eq(areaAddressType)
])
if clientIdList:
cond.append(tableClient['id'].inlist(clientIdList))
if statusInPeriod:
if begDate:
cond.append('(ClientSocStatus.endDate is null or (%s))' %
tableClientSocStatus['endDate'].ge(begDate))
if endDate:
cond.append('(ClientSocStatus.endDate is null or (%s))' %
tableClientSocStatus['endDate'].le(endDate))
if statusStart:
if begDate:
cond.append(tableClientSocStatus['begDate'].ge(begDate))
if endDate:
cond.append(tableClientSocStatus['begDate'].le(endDate))
if statusFinish:
if begDate:
cond.append(tableClientSocStatus['endDate'].ge(begDate))
if endDate:
cond.append(tableClientSocStatus['endDate'].le(endDate))
if sex:
cond.append(tableClient['sex'].eq(sex))
if ageFrom <= ageTo:
cond.append(
'(ClientSocStatus.begDate is null or ClientSocStatus.begDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR))'
% ageFrom)
cond.append(
'(ClientSocStatus.endDate is null or ClientSocStatus.endDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1))'
% (ageTo + 1))
if onlyPermanentAttach:
cond.append(havePermanentAttach(endDate))
if groupId:
groupIdList = [groupId]
groupIdList += db.getIdList('rbSocStatusClass',
where='group_id=%d' % groupId)
cond.append(tableSocStatusClass['id'].inlist(groupIdList))
if socStatusTypes:
tableSocStatusType = db.table('rbSocStatusType')
cond.append(tableSocStatusType['id'].inlist(socStatusTypes))
if outcome:
additionalFrom = '''INNER JOIN ClientAttach ON ClientAttach.client_id AND ClientAttach.id = (SELECT max(ca.id)
FROM
ClientAttach ca
WHERE
ca.client_id = Client.id) AND ClientAttach.deleted = 0
INNER JOIN rbAttachType ON rbAttachType.id = ClientAttach.attachType_id AND rbAttachType.outcome = 0'''
else:
additionalFrom = '''left join ClientAttach on ClientAttach.client_id = Client.id and ClientAttach.attachType_id = %s and ClientAttach.deleted = 0''' % attachTypeId
if groupBySocStatus:
order = 'rbSocStatusClass.name, rbSocStatusType.name, '
else:
order = ''
#print stmt % (attachTypeId, additionalFrom, db.joinAnd(cond), order)
return db.query(stmt %
(attachTypeId, additionalFrom, db.joinAnd(cond), order))
def selectData(begDate, endDate, eventTypeId, sex, ageFrom, ageTo,
onlyPermanentAttach, MKBFilter, MKBFrom, MKBTo, onlyPayedEvents,
begPayDate, endPayDate, detailPerson, personId, specialityId,
orgStructureId, insurerId):
db = QtGui.qApp.db
tableEvent = db.table('Event')
tableClient = db.table('Client')
tableVisit = db.table('Visit')
tableActionType = db.table('ActionType')
tablePerson = db.table('Person')
cond = []
if begDate:
cond.append(tableVisit['date'].ge(begDate))
if endDate:
cond.append(tableVisit['date'].le(endDate))
if eventTypeId:
cond.append(tableEvent['eventType_id'].eq(eventTypeId))
if sex:
cond.append(tableClient['sex'].eq(sex))
if ageFrom <= ageTo:
cond.append(
'Visit.date >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' %
ageFrom)
cond.append(
'Visit.date < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)'
% (ageTo + 1))
if onlyPermanentAttach:
cond.append(havePermanentAttach(endDate))
if MKBFilter:
tableDiagnostic = db.table('Diagnostic')
tableDiagnosis = db.table('Diagnosis')
subQueryTable = tableDiagnosis.leftJoin(
tableDiagnostic,
tableDiagnosis['id'].eq(tableDiagnostic['diagnosis_id']))
subCond = [
tableDiagnostic['event_id'].eq(tableEvent['id']),
tableDiagnosis['MKB'].between(MKBFrom, MKBTo)
]
cond.append(db.existsStmt(subQueryTable, subCond))
if onlyPayedEvents:
cond.append('isEventPayed(Event.id)')
tableAccountItem = db.table('Account_Item')
addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate)
if not detailPerson:
if personId:
cond.append(tableVisit['person_id'].eq(personId))
if specialityId:
cond.append(tablePerson['speciality_id'].eq(specialityId))
if orgStructureId:
orgStructureIdList = getTheseAndChildrens([orgStructureId])
cond.append(tablePerson['orgStructure_id'].inlist(orgStructureIdList))
if insurerId:
cond.append(
'EXISTS (SELECT ClientPolicy.`client_id` FROM ClientPolicy WHERE ClientPolicy.`insurer_id`=%d AND ClientPolicy.`client_id`=Client.`id`)'
% insurerId)
condStr = db.joinAnd(cond)
stmt = '''
SELECT
Visit.`person_id`, Visit.`finance_id`, Visit.`payStatus` AS visitPayStatus,
Person.`lastName` AS personLastName, Person.`firstName` AS personFirstName, Person.`patrName` AS personPatrName,
rbSpeciality.`name` AS specialityName,
rbService.`code` AS serviceCode,
OrgStructure.`id` AS orgStructureId,
OrgStructure.`code` AS orgStructureCode,
IF(YEAR(FROM_DAYS(DATEDIFF(Visit.`date`, Client.`birthDate`))) < 18, rbService.`childUetDoctor`, rbService.`adultUetDoctor`) AS uetDoctor,
IF(YEAR(FROM_DAYS(DATEDIFF(Visit.`date`, Client.`birthDate`))) < 18, rbService.`childUetAverageMedWorker`, rbService.`adultUetAverageMedWorker`) AS uetAverageMedWorker
FROM Visit
INNER JOIN Event ON Event.`id` = Visit.`event_id`
INNER JOIN Client ON Client.`id` = Event.`client_id`
INNER JOIN rbService ON rbService.`id` = Visit.`service_id`
LEFT OUTER JOIN Person ON Person.id = Visit.`person_id`
LEFT OUTER JOIN rbSpeciality ON rbSpeciality.`id` = Person.`speciality_id`
LEFT OUTER JOIN OrgStructure ON OrgStructure.`id` = Person.`orgStructure_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 Visit.deleted=0 AND Event.deleted=0 AND %s
ORDER BY rbService.`code`
''' % condStr
return db.query(stmt)
def selectData(begDate, endDate, eventTypeId, sex, ageFrom, ageTo,
actionTypeClass, actionTypeId, onlyPermanentAttach, MKBFilter,
MKBFrom, MKBTo, onlyPayedEvents, begPayDate, endPayDate):
db = QtGui.qApp.db
tableEvent = db.table('Event')
tableClient = db.table('Client')
tableActionType = db.table('ActionType')
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 sex:
cond.append(tableClient['sex'].eq(sex))
if ageFrom <= ageTo:
cond.append(
'Event.execDate >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)' %
ageFrom)
cond.append(
'Event.execDate < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)'
% (ageTo + 1))
if actionTypeId:
cond.append(tableActionType['id'].inlist(
getActionTypeDescendants(actionTypeId, actionTypeClass)))
elif actionTypeClass is not None:
cond.append(tableActionType['class'].eq(actionTypeClass))
if onlyPermanentAttach:
cond.append(havePermanentAttach(endDate))
if MKBFilter:
tableDiagnostic = db.table('Diagnostic')
tableDiagnosis = db.table('Diagnosis')
subQueryTable = tableDiagnosis.leftJoin(
tableDiagnostic,
tableDiagnosis['id'].eq(tableDiagnostic['diagnosis_id']))
subCond = [
tableDiagnostic['event_id'].eq(tableEvent['id']),
tableDiagnosis['MKB'].between(MKBFrom, MKBTo)
]
cond.append(db.existsStmt(subQueryTable, subCond))
if onlyPayedEvents:
cond.append('isEventPayed(Event.id)')
tableAccountItem = db.table('Account_Item')
addDateInRange(cond, tableAccountItem['date'], begPayDate, endPayDate)
condStr = db.joinAnd(cond)
stmt = """
SELECT
SUM(Action.amount) AS amount,
SUM(IF(Event.execDate IS NULL,0, Action.amount)) AS done,
COUNT(*) AS cnt,
ActionType.name
FROM
Action
INNER JOIN Event on Action.event_id=Event.id
INNER JOIN Client on Client.id=Event.client_id
INNER JOIN ActionType on ActionType.id=Action.actionType_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 Action.deleted=0 AND Event.deleted=0 AND %s
GROUP BY ActionType.class, ActionType.group_id, ActionType.id
""" % (condStr)
return db.query(stmt)
