def isDestinationInFormulary(self, drugFormularyItemId):
if QtGui.qApp.currentOrgStructureId():
orgStructureIdList = getOrgStructureDescendants(
QtGui.qApp.currentOrgStructureId())
else:
orgStructureIdList = getOrgStructures(QtGui.qApp.currentOrgId())
if len(orgStructureIdList) > 0:
stmt = u'''
SELECT DrugFormulary.id
FROM DrugFormulary_Item
INNER JOIN DrugFormulary ON DrugFormulary.id = DrugFormulary_Item.master_id
AND DrugFormulary.orgStructure_id IN (%(orgStructures)s)
WHERE DrugFormulary_Item.id = %(drugFormularyItemId)s
''' % {
'drugFormularyItemId':
drugFormularyItemId,
'orgStructures':
', '.join(
forceString(orgStructure)
for orgStructure in orgStructureIdList)
}
query = QtGui.qApp.db.query(stmt)
if query.first():
return True
return False
def getData(areaId, endDate, addressType, isDetailByAge=False):
if areaId:
areaIdList = getOrgStructureDescendants(areaId)
else:
areaIdList = getOrgStructures(QtGui.qApp.currentOrgId())
query = selectData(endDate, areaIdList, addressType)
reportData = [[0] * reportRowSize[0] for row in xrange(3)]
self.setQueryText(forceString(query.lastQuery()))
while query.next():
record = query.record()
cnt = forceInt(record.value('cnt'))
age = forceInt(record.value('clientAge'))
sex = forceInt(record.value('clientSex'))
busy = forceBool(record.value('busy'))
if age < 1:
colBase = 0
elif age == 1:
colBase = 2
elif age <= 6:
colBase = 4
elif age <= 14:
colBase = 6
elif age <= 17:
colBase = 8
else:
if isDetailByAge:
minAgeHigh = 150
colBase = 36
for ageHigh in employableColBases.keys():
if age <= ageHigh and ageHigh < minAgeHigh:
minAgeHigh = ageHigh
colBase = employableColBases[ageHigh]
else:
if (sex == 1 and age < 60) or (sex != 1 and age < 55):
colBase = 10
else:
colBase = 12
cols = [colBase + (0 if sex == 1 else 1), reportRowSize[0] - 1]
rows = [0, 1 if busy else 2]
if cols[0] < 0 or cols[0] > reportRowSize[0] - 1:
print cols[0]
for row in rows:
for col in cols:
reportData[row][col] += cnt
return reportData
def __init__(self, parent = None, filter = ''):
CDialogBase.__init__(self, parent)
self.curDrugsId = []
self.tradeNameCodesList = []
self.filter = filter
self.drugFormularyItemIdList = []
self.orgStructureIdList = []
self.orgStructureIdWhere = ''
self.mnn = '%'
self.issueForm = '%'
self.tradeName = '%'
self.searchMNN ='%'
self.searchIssueForm = '%'
self.searchTradeName = '%'
self.addModels('MNN', CMNNTableModel(self))
self.addModels('IssueForm', CIssueFormTableModel(self))
self.addModels('TradeName', CTradeNamesTableModel(self))
self.addModels('List', CListTableModel(self))
self.addModels('OrgStructureStock', COrgStructureStock(self))
self.setupUi(self)
self.setModels(self.tblMNN, self.modelMNN, self.selectionModelMNN)
self.tblMNN.horizontalHeader().setStretchLastSection(True)
self.tblMNN.setSelectionMode(QtGui.QAbstractItemView.SingleSelection)
self.setModels(self.tblIssueForm, self.modelIssueForm, self.selectionModelIssueForm)
self.tblIssueForm.horizontalHeader().setStretchLastSection(True)
self.tblIssueForm.setSelectionMode(QtGui.QAbstractItemView.SingleSelection)
self.tblIssueForm.setSelectionBehavior(QtGui.QTableView.SelectRows)
self.setModels(self.tblTradeName, self.modelTradeName, self.selectionModelTradeName)
self.tblTradeName.horizontalHeader().setStretchLastSection(True)
self.tblTradeName.setSelectionMode(QtGui.QAbstractItemView.SingleSelection)
self.tblSelectedDrugs.setModel(self.modelList)
self.tblSelectedDrugs.horizontalHeader().setStretchLastSection(True)
self.chkCurrentFormulary.setChecked(True)
self.prbLoad.setVisible(False)
if QtGui.qApp.currentOrgStructureId():
self.orgStructureIdList = getOrgStructureDescendants(QtGui.qApp.currentOrgStructureId())
else:
self.orgStructureIdList = getOrgStructures(QtGui.qApp.currentOrgId())
if len(self.orgStructureIdList) > 0:
self.orgStructureIdWhere = u'''AND DrugFormulary.orgStructure_id IN (%(orgStructures)s) ''' % {'orgStructures': ', '.join(forceString(orgStructure) for orgStructure in self.orgStructureIdList)}
self.tableMNNRebuild()
def selectData(endDate, ageFrom, ageTo, areaId, addressType, socStatusTypeId,
socStatusClassId):
stmt = """
SELECT
COUNT(Client.id) as cnt,
%(orgStructField)s AS repOrgStructure_id,
age(Client.birthDate, %(attachCheckDate)s) AS clientAge,
Client.sex AS clientSex,
IF(ClientAttach.id IS NULL,0,1) AS attached,
IF(ClientIdentification.checkDate IS NULL,0,1) AS confirmed
FROM Client
%(joins)s
LEFT JOIN ClientAttach ON ClientAttach.client_id = Client.id
AND ClientAttach.id = (SELECT MAX(CAT.id) FROM ClientAttach AS CAT
LEFT JOIN rbAttachType ON rbAttachType.id=CAT.attachType_id
WHERE CAT.deleted=0
AND CAT.client_id=Client.id
# AND rbAttachType.temporary=0
AND CAT.begDate<=%(attachCheckDate)s
AND (CAT.endDate IS NULL or CAT.endDate>=%(attachCheckDate)s)
)
AND %(attachToArea)s
LEFT JOIN rbAttachType ON rbAttachType.id=ClientAttach.attachType_id
LEFT JOIN ClientIdentification ON ClientIdentification.client_id
AND ClientIdentification.id = (
SELECT MAX(CI.id)
FROM ClientIdentification AS CI
LEFT JOIN rbAccountingSystem ON rbAccountingSystem.id = CI.accountingSystem_id
WHERE rbAccountingSystem.code in (\'1\', \'2\')
AND CI.client_id = Client.id)
WHERE %(mainCond)s
GROUP BY repOrgStructure_id, clientAge, clientSex, attached, confirmed
"""
db = QtGui.qApp.db
tableClient = db.table('Client')
tableOrgStructureAddress = db.table('OrgStructure_Address')
tableAddress = db.table('Address')
tableClientAttach = db.table('ClientAttach')
tableAttachType = db.table('rbAttachType')
cond = []
cond.append(tableClient['deleted'].eq(0))
cond.append(
db.joinOr([
tableAttachType['outcome'].eq(0),
tableAttachType['outcome'].isNull()
]))
# cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId()))
# if eventTypeIdList:
# cond.append(tableEvent['eventType_id'].inlist(eventTypeIdList))
if ageFrom <= ageTo:
# для проверки логики:
# если взять ageFrom == ageTo == 0 то
# должны получиться дети родившиеся за последний год,
# а годовалые уже не подходят
cond.append(tableClient['birthDate'].gt(endDate.addYears(-ageTo - 1)))
cond.append(tableClient['birthDate'].le(endDate.addYears(-ageFrom)))
# if areaId:
# cond.append(tableOrgStructureAddress['master_id'].inlist(getOrgStructureDescendants(areaId)))
if areaId:
areaIdList = getOrgStructureDescendants(areaId)
else:
areaIdList = getOrgStructures(QtGui.qApp.currentOrgId())
reg = (addressType + 1) & 1
loc = (addressType + 1) & 2
attach = (addressType + 1) & 4
condAddr = []
joins = ''
if reg:
tableOrgStructureAddress = db.table('OrgStructure_Address').alias(
'OSAReg')
condAddr.append(
tableOrgStructureAddress['master_id'].inlist(areaIdList))
joins += '''
LEFT JOIN ClientAddress AS CAReg ON CAReg.client_id = Client.id
AND CAReg.id = (SELECT MAX(CARegInt.id) FROM ClientAddress AS CARegInt WHERE CARegInt.type=0 AND CARegInt.client_id=Client.id)
LEFT JOIN Address AS AReg ON AReg.id = CAReg.address_id
LEFT JOIN OrgStructure_Address AS OSAReg ON OSAReg.house_id = AReg.house_id AND %s
''' % tableOrgStructureAddress['master_id'].inlist(areaIdList)
if loc:
tableOrgStructureAddress = db.table('OrgStructure_Address').alias(
'OSALoc')
condAddr.append(
tableOrgStructureAddress['master_id'].inlist(areaIdList))
joins += '''
LEFT JOIN ClientAddress AS CALoc ON CALoc.client_id = Client.id
AND CALoc.id = (SELECT MAX(CALocInt.id) FROM ClientAddress AS CALocInt WHERE CALocInt.type=1 AND CALocInt.client_id=Client.id)
LEFT JOIN Address AS ALoc ON ALoc.id = CALoc.address_id
LEFT JOIN OrgStructure_Address AS OSALoc ON OSALoc.house_id = ALoc.house_id AND %s
''' % tableOrgStructureAddress['master_id'].inlist(areaIdList)
if attach:
condAddr.append(
db.joinAnd([
tableClientAttach['orgStructure_id'].inlist(areaIdList),
tableClientAttach['deleted'].eq(0)
]))
if condAddr:
cond.append(db.joinOr(condAddr))
if attach:
if loc:
if reg:
orgStructField = 'IF(ClientAttach.orgStructure_id IS NOT NULL, ClientAttach.orgStructure_id, IF(OSALoc.master_id IS NOT NULL, OSALoc.master_id, OSAReg.master_id))'
else:
orgStructField = 'IF(ClientAttach.orgStructure_id IS NOT NULL, ClientAttach.orgStructure_id, OSALoc.master_id)'
else:
if reg:
orgStructField = 'IF(ClientAttach.orgStructure_id IS NOT NULL, ClientAttach.orgStructure_id, OSAReg.master_id)'
else:
orgStructField = 'ClientAttach.orgStructure_id'
else:
if loc:
if reg:
orgStructField = 'IF(OSALoc.master_id IS NOT NULL, OSALoc.master_id, OSAReg.master_id)'
else:
orgStructField = 'OSALoc.master_id'
else:
if reg:
orgStructField = 'OSAReg.master_id'
else:
orgStructField = 'NULL'
if socStatusTypeId:
subStmt = (
'SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' +
'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND '
+ 'ClientSocStatus.socStatusType_id=%d' % socStatusTypeId)
cond.append('EXISTS(' + subStmt + ')')
elif socStatusClassId:
subStmt = (
'SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' +
'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND '
+ 'ClientSocStatus.socStatusClass_id=%d' % socStatusClassId)
cond.append('EXISTS(' + subStmt + ')')
return db.query(
stmt % {
'orgStructField': orgStructField,
'attachCheckDate': tableClient['birthDate'].formatValue(endDate),
'attachToArea':
tableClientAttach['orgStructure_id'].inlist(areaIdList),
'joins': joins,
'mainCond': db.joinAnd(cond)
})
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))))
def selectData(registeredInPeriod, begDate, endDate, eventPurposeIdList, eventTypeId, orgStructureId, personId, sex, ageFrom, ageTo, socStatusClassId, socStatusTypeId, areaIdEnabled, areaId, locality):
stmt="""
SELECT
Diagnosis.MKB AS MKB,
COUNT(*) AS sickCount,
(%s) AS firstInPeriod,
IF((SELECT MAX(rbDispanser.observed)
FROM
Diagnostic AS D1
LEFT JOIN rbDispanser ON rbDispanser.id = D1.dispanser_id
WHERE
D1.diagnosis_id = Diagnosis.id
AND D1.endDate = (
SELECT MAX(D2.endDate)
FROM Diagnostic AS D2
WHERE D2.diagnosis_id = Diagnosis.id
AND D2.dispanser_id IS NOT NULL
AND D2.endDate<%s))
= 1, 1, 0) AS observed
FROM Diagnosis
LEFT JOIN Client ON Client.id = Diagnosis.client_id
LEFT JOIN ClientAddress ON ClientAddress.client_id = Diagnosis.client_id
AND ClientAddress.id = (SELECT MAX(id) FROM ClientAddress AS CA WHERE CA.Type=1 and CA.client_id = Diagnosis.client_id)
LEFT JOIN Address ON Address.id = ClientAddress.address_id
LEFT JOIN rbDiagnosisType ON rbDiagnosisType.id = Diagnosis.diagnosisType_id
LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnosis.character_id
WHERE %s
GROUP BY MKB, firstInPeriod, observed
"""
db = QtGui.qApp.db
tableDiagnosis = db.table('Diagnosis')
tableDiseaseCharacter = db.table('rbDiseaseCharacter')
tableClient = db.table('Client')
tableDiagnostic = db.table('Diagnostic')
tablePerson = db.table('Person')
cond = []
cond.append(tableDiagnosis['deleted'].eq(0))
cond.append(tableDiagnosis['mod_id'].isNull())
cond.append(db.joinOr([tableDiagnosis['setDate'].isNull(), tableDiagnosis['setDate'].lt(endDate.addDays(1))]))
cond.append(db.joinOr([tableDiagnosis['endDate'].ge(begDate), tableDiseaseCharacter['code'].eq('3')]))
diagnosticQuery = tableDiagnostic
diagnosticCond = [ tableDiagnostic['diagnosis_id'].eq(tableDiagnosis['id']),
tableDiagnostic['deleted'].eq(0)
]
if registeredInPeriod:
addDateInRange(diagnosticCond, tableDiagnostic['setDate'], begDate, endDate)
if personId:
diagnosticCond.append(tableDiagnostic['person_id'].eq(personId))
elif orgStructureId:
diagnosticQuery = diagnosticQuery.leftJoin(tablePerson, tablePerson['id'].eq(tableDiagnostic['person_id']))
diagnosticCond.append(tablePerson['orgStructure_id'].inlist(getOrgStructureDescendants(orgStructureId)))
else:
diagnosticQuery = diagnosticQuery.leftJoin(tablePerson, tablePerson['id'].eq(tableDiagnostic['person_id']))
diagnosticCond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId()))
if eventTypeId:
tableEvent = db.table('Event')
diagnosticQuery = diagnosticQuery.leftJoin(tableEvent, tableEvent['id'].eq(tableDiagnostic['event_id']))
diagnosticCond.append(tableEvent['eventType_id'].eq(eventTypeId))
elif eventPurposeIdList:
tableEvent = db.table('Event')
tableEventType = db.table('EventType')
diagnosticQuery = diagnosticQuery.leftJoin(tableEvent, tableEvent['id'].eq(tableDiagnostic['event_id']))
diagnosticQuery = diagnosticQuery.leftJoin(tableEventType, tableEventType['id'].eq(tableEvent['eventType_id']))
diagnosticCond.append(tableEventType['purpose_id'].inlist(eventPurposeIdList))
cond.append(db.existsStmt(diagnosticQuery, diagnosticCond))
if sex:
cond.append(tableClient['sex'].eq(sex))
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 socStatusTypeId:
subStmt = ('SELECT ClientSocStatus.id FROM ClientSocStatus WHERE '
+'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND '
+'ClientSocStatus.socStatusType_id=%d' % socStatusTypeId)
cond.append('EXISTS('+subStmt+')')
elif socStatusClassId:
subStmt = ('SELECT ClientSocStatus.id FROM ClientSocStatus WHERE '
+'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND '
+'ClientSocStatus.socStatusClass_id=%d' % socStatusClassId)
cond.append('EXISTS('+subStmt+')')
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 locality:
# 1: горожане, isClientVillager == 0 или NULL
# 2: сельские жители, isClientVillager == 1
cond.append('IFNULL(isClientVillager(Client.id), 0) = %d' % (locality-1))
return db.query(stmt % (db.joinAnd([tableDiagnosis['setDate'].le(endDate),
tableDiagnosis['setDate'].ge(begDate)]),
tableDiagnosis['setDate'].formatValue(endDate.addDays(1)),
db.joinAnd(cond)))
def selectData(params):
begDate = params.get('begDate', QtCore.QDate())
endDate = params.get('endDate', QtCore.QDate())
eventPurposeId = params.get('eventPurposeId', None)
eventTypeId = params.get('eventTypeId', None)
orgStructureId = params.get('orgStructureId', None)
specialityId = params.get('specialityId', None)
personId = params.get('personId', None)
workOrgId = params.get('workOrgId', None)
sex = params.get('sex', 0)
ageFrom = params.get('ageFrom', 0)
ageTo = params.get('ageTo', 150)
areaIdEnabled = params.get('areaIdEnabled', False)
chkOrgStructureArea = params.get('chkOrgStructureArea', False)
areaId = params.get('areaId', None)
MKBFilter = params.get('MKBFilter', 0)
MKBFrom = params.get('MKBFrom', 'A00')
MKBTo = params.get('MKBTo', 'Z99.9')
MKBExFilter = params.get('MKBExFilter', 0)
MKBExFrom = params.get('MKBExFrom', 'A00')
MKBExTo = params.get('MKBExTo', 'Z99.9')
addressEnabled = params.get('addressEnabled', False)
addressType = params.get('addressType', 0)
clientAddressType = params.get('clientAddressType', 0)
clientAddressCityCode = params.get('clientAddressCityCode', None)
clientAddressCityCodeList = params.get('clientAddressCityCodeList', None)
clientAddressStreetCode = params.get('clientAddressStreetCode', None)
clientHouse = params.get('clientHouse', '')
clientCorpus = params.get('clientCorpus', '')
clientFlat = params.get('clientFlat', '')
stmt = """
SELECT
count(Event.id) AS `count`,
Event.result_id AS result_id,
rbResult.name AS result,
Event.isPrimary AS isPrimary,
Event.execDate IS NOT NULL AS isDone,
Event.`order` AS `order`
FROM
Event
LEFT JOIN EventType ON EventType.id = Event.eventType_id
LEFT JOIN Diagnostic ON Diagnostic.event_id = Event.id AND
Diagnostic.id IN (
SELECT D1.id
FROM Diagnostic AS D1 LEFT JOIN rbDiagnosisType AS DT1 ON DT1.id = D1.diagnosisType_id
WHERE D1.event_id = Event.id AND
DT1.code = (SELECT MIN(DT2.code)
FROM Diagnostic AS D2 LEFT JOIN rbDiagnosisType AS DT2 ON DT2.id = D2.diagnosisType_id
WHERE D2.event_id = Event.id)
)
LEFT JOIN Client ON Client.id = Event.client_id
LEFT JOIN ClientAddress ON ClientAddress.client_id = Event.client_id
AND ClientAddress.id = (SELECT MAX(id) FROM ClientAddress AS CA WHERE CA.Type=1 and CA.client_id = Event.client_id)
LEFT JOIN Address ON Address.id = ClientAddress.address_id
%s
LEFT JOIN Diagnosis ON Diagnosis.id = Diagnostic.diagnosis_id
LEFT JOIN vrbPersonWithSpeciality ON vrbPersonWithSpeciality.id = Event.execPerson_id
LEFT JOIN rbResult ON rbResult.id = Event.result_id
WHERE
%s
GROUP BY
result_id, isPrimary, isDone, `order`
"""
db = QtGui.qApp.db
tableEvent = db.table('Event')
tableDiagnosis = db.table('Diagnosis')
tableClient = db.table('Client')
tableClientAddress = db.table('ClientAddress')
tableOrgStructureAddress = db.table('OrgStructure_Address')
tableAddress = db.table('Address')
tableAddressForClient = db.table('Address').alias('AddressForCLient')
tableAddressHouse = db.table('AddressHouse')
tablePerson = db.table('vrbPersonWithSpeciality')
tableDiagnostic = db.table('Diagnostic')
tableDiseaseCharacter = db.table('rbDiseaseCharacter')
cond = []
cond.append(tableEvent['deleted'].eq(0))
cond.append(tableEvent['execDate'].ge(begDate))
cond.append(
db.joinOr([
tableEvent['execDate'].lt(endDate.addDays(1)),
tableEvent['execDate'].isNull()
]))
if eventTypeId:
cond.append(tableEvent['eventType_id'].eq(eventTypeId))
elif eventPurposeId:
cond.append(db.table('EventType')['purpose_id'].eq(eventPurposeId))
if 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 personId:
cond.append(tableDiagnostic['person_id'].eq(personId))
elif orgStructureId:
cond.append(tablePerson['orgStructure_id'].inlist(
getOrgStructureDescendants(orgStructureId)))
else:
cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId()))
if specialityId:
cond.append(tablePerson['speciality_id'].eq(specialityId))
if workOrgId:
cond.append(
'EXISTS (SELECT * FROM ClientWork WHERE ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) and ClientWork.org_id=%d)'
% (workOrgId))
if sex:
cond.append(tableClient['sex'].eq(sex))
if ageFrom <= ageTo:
date = str(QtCore.QDate.currentDate().toString(QtCore.Qt.ISODate))
cond.append(
'IF(Diagnosis.endDate IS NOT NULL, Diagnosis.endDate, DATE(\'%s\')) >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)'
% (date, ageFrom))
cond.append(
'IF(Diagnosis.endDate IS NOT NULL, Diagnosis.endDate, DATE(\'%s\')) < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)'
% (date, ageTo + 1))
additionalJoin = ''
if areaIdEnabled:
if chkOrgStructureArea:
if areaId:
orgStructureIdList = getOrgStructureDescendants(areaId)
else:
orgStructureIdList = getOrgStructures(
QtGui.qApp.currentOrgId())
subCond = [
tableOrgStructureAddress['master_id'].inlist(
orgStructureIdList),
tableOrgStructureAddress['house_id'].eq(
tableAddress['house_id']),
]
cond.append(db.existsStmt(tableOrgStructureAddress, subCond))
if addressEnabled:
if addressType in [STRICT_ADDRESS, NONRESIDENT_ADDRESS]:
clientAddressType = clientAddressType if addressType == STRICT_ADDRESS else 0
additionalJoin = 'LEFT JOIN ClientAddress AS ClientAddressForClient ON ClientAddressForClient.client_id = Event.client_id AND ClientAddressForClient.id = (SELECT MAX(id) FROM ClientAddress AS CA2 WHERE CA2.Type=%d and CA2.client_id = Event.client_id) LEFT JOIN Address AS AddressForClient ON AddressForClient.id = ClientAddressForClient.address_id LEFT JOIN AddressHouse ON AddressHouse.id = AddressForClient.house_id' % clientAddressType
if addressType == STRICT_ADDRESS:
if clientFlat:
cond.append(
tableAddressForClient['flat'].eq(clientFlat))
if clientCorpus:
cond.append(
tableAddressHouse['corpus'].eq(clientCorpus))
if clientHouse:
cond.append(
tableAddressHouse['number'].eq(clientHouse))
if clientAddressStreetCode:
cond.append(tableAddressHouse['KLADRStreetCode'].eq(
clientAddressStreetCode))
if clientAddressCityCodeList:
cond.append(tableAddressHouse['KLADRCode'].inlist(
clientAddressCityCodeList))
else:
if clientAddressCityCode:
cond.append(tableAddressHouse['KLADRCode'].eq(
clientAddressCityCode))
else:
props = QtGui.qApp.preferences.appPrefs
kladrCodeList = [
forceString(getVal(props, 'defaultKLADR', '')),
forceString(getVal(props, 'provinceKLADR', ''))
]
cond.append(tableAddressHouse['KLADRCode'].notInlist(
kladrCodeList))
else:
foreignDocumentTypeId = forceInt(
db.translate('rbDocumentType', 'code', '9', 'id'))
documentCond = 'EXISTS(SELECT ClientDocument.`id` FROM ClientDocument WHERE ClientDocument.`documentType_id`=%d AND ClientDocument.`client_id`=Client.`id`)' % foreignDocumentTypeId
cond.append(documentCond)
stmt = stmt % (additionalJoin, db.joinAnd(cond))
# print stmt
return db.query(stmt)
def selectLinesAndData(params):
db = QtGui.qApp.db
listUetActionIds = params.get('idList')
personId = params.get('personId')
orgId = params.get('orgStructureId')
begDate = params.get('begDate')
endDate = params.get('endDate')
tableEvent = db.table('Event')
tableAction = db.table('Action')
tableUetActionType = db.table('ActionType').alias('UetActionType')
tableActionProperty = db.table('ActionProperty')
tableActionPropertyType = db.table('ActionPropertyType')
tableActionPropertyOrgStructure = db.table('ActionProperty_OrgStructure')
tableMovingAction = tableAction.alias('MovingAction')
tableUetAction = tableAction.alias('UetAction')
tablePerson = db.table('Person')
tableOrgStructure = db.table('OrgStructure')
cond = []
cond.append(tableActionPropertyType['name'].eq(u'отделение пребывания'))
cond.append(tableUetAction['amount'].ne(0))
cond.append(tableOrgStructure['name'].notlike(
u'%ультразвуковой и функциональной диагностики%'))
if personId:
cond.append(tablePerson['id'].eq(personId))
if begDate and endDate:
cond.append(tableUetAction['begDate'].dateLe(endDate))
cond.append(tableUetAction['endDate'].dateGe(begDate))
if orgId:
cond.append(tableOrgStructure['id'].inlist(
getOrgStructureDescendants(orgId)))
elif orgId == None:
allOrgId = getOrgStructures(QtGui.qApp.currentOrgId())
cond.append(tableOrgStructure['id'].inlist(allOrgId))
cols = [
tableUetAction['amount'],
tableUetAction['actionType_id'].alias('actionType_id'),
tableActionPropertyOrgStructure['value'].alias('orgStructure_id'),
'''SUM(IFNULL(UetActionType.`UetDoctor` * UetAction.`amount`, 0)) AS `sum`''',
tableOrgStructure['name'].alias('orgStructureName')
]
queryTable = tableEvent.innerJoin(tableMovingAction, [
'''
MovingAction.id = (
SELECT
max(Action.id)
FROM
Action
INNER JOIN ActionType ON Action.actionType_id = ActionType.id
WHERE
ActionType.flatCode = 'moving'
AND Action.deleted = 0
AND Action.event_id = Event.id
)
'''
])
queryTable = queryTable.innerJoin(
tablePerson, [tableEvent['execPerson_id'].eq(tablePerson['id'])])
queryTable = queryTable.innerJoin(
tableActionProperty,
[tableMovingAction['id'].eq(tableActionProperty['action_id'])])
queryTable = queryTable.innerJoin(
tableActionPropertyType,
[tableActionPropertyType['id'].eq(tableActionProperty['type_id'])])
queryTable = queryTable.innerJoin(
tableActionPropertyOrgStructure,
[tableActionProperty['id'].eq(tableActionPropertyOrgStructure['id'])])
queryTable = queryTable.innerJoin(tableUetAction, [
tableUetAction['event_id'].eq(tableEvent['id']),
tableUetAction['actionType_id'].inlist(listUetActionIds)
])
queryTable = queryTable.innerJoin(
tableUetActionType,
[tableUetActionType['id'].eq(tableUetAction['actionType_id'])])
queryTable = queryTable.innerJoin(
tableOrgStructure,
[tableActionPropertyOrgStructure['value'].eq(tableOrgStructure['id'])])
group = [
tableUetAction['actionType_id'],
tableActionPropertyOrgStructure['value']
]
order = ['orgStructure_id']
stmt = db.selectStmt(queryTable,
cols,
where=cond,
group=group,
order=order)
return db.query(stmt)
def build(self, params):
areaId = params.get('areaId', None)
endDate = params.get('endDate', QtCore.QDate())
addressType = params.get('addressType', 0)
isDetailByDepartment = params.get('detailByDepartment', False)
isDetailByAge = params.get('detailByAge', False)
rowNumber = [1]
employableColBases = {
19: 10,
24: 12,
29: 14,
34: 16,
39: 18,
44: 20,
49: 22,
54: 24,
59: 26,
64: 28,
69: 30,
74: 32,
79: 34
}
#Количество столбцов с данными (за исключением столбцов "Категория населения", "№": 10 на детские возраста, 28 или 4 на взрослые и 1 на поле "Всего")
reportRowSize = [39 if isDetailByAge else 15]
def insertDataInTable(reportTable, reportData, charFormat=None):
for rowIndex, rowName in enumerate(
[u'ИТОГО', u'работающие', u'неработающие']):
i = reportTable.addRow()
reportTable.setText(i, 0, rowName, charFormat=charFormat)
reportTable.setText(i, 1, rowNumber[0], charFormat=charFormat)
rowNumber[0] += 1
for j in xrange(len(reportData[rowIndex])):
reportTable.setText(i,
2 + j,
reportData[rowIndex][j],
charFormat=charFormat)
#end insertDataInTable
def getData(areaId, endDate, addressType, isDetailByAge=False):
if areaId:
areaIdList = getOrgStructureDescendants(areaId)
else:
areaIdList = getOrgStructures(QtGui.qApp.currentOrgId())
query = selectData(endDate, areaIdList, addressType)
reportData = [[0] * reportRowSize[0] for row in xrange(3)]
self.setQueryText(forceString(query.lastQuery()))
while query.next():
record = query.record()
cnt = forceInt(record.value('cnt'))
age = forceInt(record.value('clientAge'))
sex = forceInt(record.value('clientSex'))
busy = forceBool(record.value('busy'))
if age < 1:
colBase = 0
elif age == 1:
colBase = 2
elif age <= 6:
colBase = 4
elif age <= 14:
colBase = 6
elif age <= 17:
colBase = 8
else:
if isDetailByAge:
minAgeHigh = 150
colBase = 36
for ageHigh in employableColBases.keys():
if age <= ageHigh and ageHigh < minAgeHigh:
minAgeHigh = ageHigh
colBase = employableColBases[ageHigh]
else:
if (sex == 1 and age < 60) or (sex != 1 and age < 55):
colBase = 10
else:
colBase = 12
cols = [colBase + (0 if sex == 1 else 1), reportRowSize[0] - 1]
rows = [0, 1 if busy else 2]
if cols[0] < 0 or cols[0] > reportRowSize[0] - 1:
print cols[0]
for row in rows:
for col in cols:
reportData[row][col] += cnt
return reportData
#end getData
def detailByDepartment(reportTable,
areaId,
endDate,
addressType,
isDetailByAge,
progressDialog=None):
subAreaIdList = []
db = QtGui.qApp.db
tableOrgStructure = db.table('OrgStructure')
whereCond = []
if areaId: #Если указан отдельный участок или группа участков
whereCond.append(tableOrgStructure['parent_id'].eq(areaId))
else: #Если в качестве участка указан весь ЛПУ
whereCond.append(tableOrgStructure['parent_id'].isNull())
whereCond.append(tableOrgStructure['organisation_id'].eq(
QtGui.qApp.currentOrgId()))
subAreaIdList = db.getIdList(table=tableOrgStructure,
where=whereCond)
if subAreaIdList:
for subAreaId in subAreaIdList:
row = reportTable.addRow()
reportTable.mergeCells(row, 0, 1, reportRowSize[0] + 3)
charFormat = QtGui.QTextCharFormat()
charFormat.setFontWeight(QtGui.QFont.Bold)
subAreaName = getOrgStructureFullName(subAreaId)
reportTable.setText(row,
0,
subAreaName,
charFormat=charFormat,
blockFormat=CReportBase.AlignLeft)
if progressDialog:
progressDialog.setWindowTitle(u'Обработка %s' %
subAreaName)
progressDialog.step()
QtGui.qApp.processEvents(
QtCore.QEventLoop.ExcludeUserInputEvents)
detailByDepartment(reportTable, subAreaId, endDate,
addressType, isDetailByAge,
progressDialog)
else:
insertDataInTable(
reportTable,
getData(areaId, endDate, addressType, isDetailByAge))
#end detailByDepartment
# now text
doc = QtGui.QTextDocument()
cursor = QtGui.QTextCursor(doc)
cursor.setCharFormat(CReportBase.ReportTitle)
cursor.insertText(self.title())
cursor.insertBlock()
self.dumpParams(cursor, params)
cursor.insertBlock()
tableColumns = [
('15?', [u'Категории населения', u''], CReportBase.AlignLeft),
('5?', [u'№ стр.', u''], CReportBase.AlignRight),
('5?', [
u'Численность прикреплённого населения по возрастному составу',
u'дети', u'', u'до 1 года', u'М'
], CReportBase.AlignRight),
('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight),
('5?', [u'', u'', u'', u'1 год', u'М'], CReportBase.AlignRight),
('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight),
('5?', [u'', u'', u'', u'2-6 лет', u'М'], CReportBase.AlignRight),
('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight),
('5?', [u'', u'', u'', u'7-14 лет', u'М'], CReportBase.AlignRight),
('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight),
('5?', [u'', u'', u'', u'15-17 лет',
u'М'], CReportBase.AlignRight),
('5?', [u'', u'', u'', u'', u'Ж'], CReportBase.AlignRight)
]
if isDetailByAge:
ageHighKeys = employableColBases.keys()
ageHighKeys.sort()
for ageHigh in ageHighKeys:
index = ageHighKeys.index(ageHigh)
if index <= 0:
startAge = '18'
else:
startAge = '%d' % (ageHighKeys[index - 1] + 1)
ageRangeText = u'%s - %d лет' % (startAge, ageHigh)
ageState = u'взрослые' if ageHigh == 19 else u''
employableState = u'трудосп. возраста' if ageHigh == 59 else u''
tableColumns.append(
('5?',
[u'', ageState, employableState, ageRangeText,
u'М'], CReportBase.AlignRight))
employableState = u'нетрудосп. возраста' if ageHigh == 59 else u''
tableColumns.append(
('5?', [u'', u'', employableState, u'',
u'Ж'], CReportBase.AlignRight))
tableColumns.append(('5?', [u'', u'', u'', u'80 и ст.',
u'М'], CReportBase.AlignRight))
tableColumns.append(('5?', [u'', u'', u'', u'',
u'Ж'], CReportBase.AlignRight))
else:
tableColumns.append(
('5?',
[u'', u'взрослые', u'трудосп. возраста', u'18-59 лет',
u'М'], CReportBase.AlignRight))
tableColumns.append(('5?', [u'', u'', u'', u'18-54 лет',
u'Ж'], CReportBase.AlignRight))
tableColumns.append(
('5?', [u'', u'', u'нетрудосп. возраста', u'60 и ст.',
u'М'], CReportBase.AlignRight))
tableColumns.append(('5?', [u'', u'', u'', u'55 и ст.',
u'Ж'], CReportBase.AlignRight))
tableColumns.append(('5?', [
u'Всего',
], CReportBase.AlignRight))
table = createTable(cursor, tableColumns)
table.mergeCells(0, 0, 4, 1) # Категории населения
table.mergeCells(0, 1, 4, 1) # № стр.
table.mergeCells(0, 2, 1, reportRowSize[0] - 1) # Численность...
table.mergeCells(1, 2, 2, 10) # дети
table.mergeCells(3, 2, 1, 2) # <1
table.mergeCells(3, 4, 1, 2) # 1
table.mergeCells(3, 6, 1, 2) # 2-6
table.mergeCells(3, 8, 1, 2) # 7-14
table.mergeCells(3, 10, 1, 2) # 15-17
if isDetailByAge:
for i in xrange(27):
table.mergeCells(3, 12 + i * 2, 1, 2)
table.mergeCells(3, 12 + i * 2 + 1, 1, 2)
table.mergeCells(1, 12, 1, 28) # взрослые
table.mergeCells(2, 12, 1, 17) # тр.
table.mergeCells(2, 29, 1, 11) # нетр.
table.mergeCells(0, 40, 4, 1) # всего
else:
table.mergeCells(1, 12, 1, 4) # взрослые
table.mergeCells(2, 12, 1, 2) # тр.
table.mergeCells(2, 14, 1, 2) # нетр.
table.mergeCells(0, 16, 4, 1) # всего
reportData = getData(areaId, endDate, addressType, isDetailByAge)
charFormat = None
if isDetailByDepartment:
if areaId:
areaIdList = getOrgStructureDescendants(areaId)
else:
areaIdList = getOrgStructures(QtGui.qApp.currentOrgId())
progressBar = CProgressBar(
) #QtGui.QProgressDialog(u'Обработка данных по участкам', 'Отмена', 0, len(areaIdList))
progressBar.setMinimum(0)
progressBar.setMaximum(len(areaIdList))
parentCenter = QtGui.QDesktopWidget().availableGeometry().center()
progressBar.setWindowFlags(progressBar.windowFlags()
| QtCore.Qt.WindowCloseButtonHint)
progressBar.setGeometry(parentCenter.x() - 250,
parentCenter.y() - 40, 500, 80)
progressBar.setText('%v/%m')
progressBar.show()
QtGui.qApp.processEvents(QtCore.QEventLoop.ExcludeUserInputEvents)
detailByDepartment(table, areaId, endDate, addressType,
isDetailByAge, progressBar)
progressBar.hide()
charFormat = QtGui.QTextCharFormat()
charFormat.setFontWeight(QtGui.QFont.Bold)
row = table.addRow()
table.mergeCells(row, 0, 1, reportRowSize[0] + 3)
table.setText(row,
0,
getOrgStructureFullName(areaId),
charFormat=charFormat,
blockFormat=CReportBase.AlignLeft)
insertDataInTable(table, reportData, charFormat=charFormat)
return doc
def selectData(params):
begDate = params.get('begDate', QtCore.QDate())
endDate = params.get('endDate', QtCore.QDate())
eventPurposeIdList = params.get('eventPurposeIdList', [])
eventTypeId = params.get('eventTypeId', None)
orgStructureId = params.get('orgStructureId', None)
specialityId = params.get('specialityId', None)
chkTraumaType = params.get('chkTraumaType', False)
chkTraumaTypeAny = params.get('chkTraumaTypeAny', False)
traumaTypeId = params.get('traumaTypeId', None)
personId = params.get('personId', None)
workOrgId = params.get('workOrgId', None)
sex = params.get('sex', 0)
ageFrom = params.get('ageFrom', 0)
ageTo = params.get('ageTo', 150)
areaIdEnabled = params.get('areaIdEnabled', False)
areaId = params.get('areaId', None)
MKBFilter = params.get('MKBFilter', 0)
MKBFrom = params.get('MKBFrom', 'A00')
MKBTo = params.get('MKBTo', 'Z99.9')
MKBExFilter = params.get('MKBExFilter', 0)
MKBExFrom = params.get('MKBExFrom', 'A00')
MKBExTo = params.get('MKBExTo', 'Z99.9')
diseaseCharacterCodes = classToCodes(params.get('characterClass', 0))
onlyFirstTime = params.get('onlyFirstTime', False)
accountAccomp = params.get('accountAccomp', False)
locality = params.get('locality', 0)
socStatusClassId = params.get('socStatusClassId', None)
socStatusTypeId = params.get('socStatusTypeId', None)
visitEmergency = params.get('visitEmergency', False)
period = params.get('regInPeriod', False)
stmt = """
SELECT
Diagnosis.client_id,
Diagnosis.id AS diagnosis_id,
Diagnosis.MKB,
Diagnosis.MKBEx,
Diagnosis.setDate,
Diagnosis.endDate,
rbDiseaseCharacter.code AS diseaseCharacter,
rbTraumaType.name AS traumaType,
DATE(Diagnostic.endDate) AS diagnosticDate,
Diagnostic.hospital AS diagnosticHospital,
Diagnostic.sanatorium AS diagnosticSanatorium,
rbDispanser.name AS diagnosticDispanser,
vrbPersonWithSpeciality.name AS diagnosticPerson
FROM Diagnosis
LEFT JOIN Client ON Client.id = Diagnosis.client_id
LEFT JOIN ClientAddress ON ClientAddress.client_id = Diagnosis.client_id
AND ClientAddress.id = (SELECT MAX(id) FROM ClientAddress AS CA WHERE CA.Type=1 and CA.client_id = Diagnosis.client_id)
LEFT JOIN Address ON Address.id = ClientAddress.address_id
LEFT JOIN rbDiseaseCharacter ON rbDiseaseCharacter.id = Diagnosis.character_id
LEFT JOIN rbTraumaType ON rbTraumaType.id = Diagnosis.traumaType_id
LEFT JOIN Diagnostic ON Diagnostic.diagnosis_id = Diagnosis.id
LEFT JOIN rbDiagnosisType ON rbDiagnosisType.id = Diagnostic.diagnosisType_id
LEFT JOIN rbDispanser ON rbDispanser.id = Diagnostic.dispanser_id
LEFT JOIN vrbPersonWithSpeciality ON vrbPersonWithSpeciality.id = Diagnostic.person_id
LEFT JOIN Event ON Event.id = Diagnostic.event_id
LEFT JOIN EventType ON EventType.id = Event.eventType_id
WHERE
%s
ORDER BY
Client.lastName, Client.lastName, Client.firstName, Client.patrName, Client.birthDate, Client.id,
Diagnosis.MKB, Diagnosis.MKBEx, Diagnostic.endDate
"""
db = QtGui.qApp.db
tableDiagnosis = db.table('Diagnosis')
tableClient = db.table('Client')
tablePerson = db.table('vrbPersonWithSpeciality')
tableDiagnostic = db.table('Diagnostic')
tableDiseaseCharacter = db.table('rbDiseaseCharacter')
tableEvent = db.table('Event')
cond = []
cond.append(tableDiagnosis['deleted'].eq(0))
cond.append(tableDiagnosis['mod_id'].isNull())
if period:
addDateInRange(cond, tableDiagnostic['endDate'], begDate, endDate)
cond.append(tableDiagnostic['setDate'].le(endDate))
cond.append(
db.joinOr([
tableDiagnostic['endDate'].ge(begDate),
tableDiagnostic['endDate'].isNull()
]))
# addDateInRange(cond, tableDiagnostic['setDate'], begDate, endDate)
if eventTypeId:
cond.append(tableEvent['eventType_id'].eq(eventTypeId))
elif eventPurposeIdList:
cond.append(
db.table('EventType')['purpose_id'].inlist(eventPurposeIdList))
if onlyFirstTime:
cond.append(tableDiagnosis['setDate'].le(endDate))
cond.append(tableDiagnosis['setDate'].ge(begDate))
else:
cond.append(
db.joinOr([
tableDiagnosis['setDate'].isNull(),
tableDiagnosis['setDate'].le(endDate)
]))
cond.append(
db.joinOr([
tableDiagnosis['endDate'].ge(begDate),
tableDiseaseCharacter['code'].eq('3')
]))
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 personId:
cond.append(tableDiagnostic['person_id'].eq(personId))
elif orgStructureId:
cond.append(tablePerson['orgStructure_id'].inlist(
getOrgStructureDescendants(orgStructureId)))
else:
cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId()))
if specialityId:
cond.append(tablePerson['speciality_id'].eq(specialityId))
if chkTraumaType:
if chkTraumaTypeAny:
cond.append(tableDiagnosis['traumaType_id'].isNotNull())
elif traumaTypeId:
cond.append(tableDiagnosis['traumaType_id'].eq(traumaTypeId))
else:
cond.append(tableDiagnosis['traumaType_id'].isNull())
if workOrgId:
cond.append(
'EXISTS (SELECT * FROM ClientWork WHERE ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) and ClientWork.org_id=%d)'
% (workOrgId))
if not visitEmergency:
cond.append(
u'''EventType.medicalAidType_id IS NULL OR (EventType.medicalAidType_id NOT IN (SELECT rbMedicalAidType.id from rbMedicalAidType where rbMedicalAidType.code IN (\'4\')))'''
'')
if sex:
cond.append(tableClient['sex'].eq(sex))
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 diseaseCharacterCodes:
if diseaseCharacterCodes != [None]:
cond.append(
tableDiseaseCharacter['code'].inlist(diseaseCharacterCodes))
else:
cond.append(tableDiseaseCharacter['code'].isNull())
if not accountAccomp:
tableDiagnosisType = db.table('rbDiagnosisType')
cond.append(tableDiagnosisType['code'].inlist(['1', '2', '4']))
if locality:
# 1: горожане, isClientVillager == 0 или NULL
# 2: сельские жители, isClientVillager == 1
cond.append('IFNULL(isClientVillager(Client.id), 0) = %d' %
(locality - 1))
if socStatusTypeId:
subStmt = (
'SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' +
'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND '
+ 'ClientSocStatus.socStatusType_id=%d' % socStatusTypeId)
cond.append('EXISTS(' + subStmt + ')')
elif socStatusClassId:
subStmt = (
'SELECT ClientSocStatus.id FROM ClientSocStatus WHERE ' +
'ClientSocStatus.deleted=0 AND ClientSocStatus.client_id=Client.id AND '
+ 'ClientSocStatus.socStatusClass_id=%d' % socStatusClassId)
cond.append('EXISTS(' + subStmt + ')')
return db.query(stmt % (db.joinAnd(cond)))
def selectData(params):
begDate = params.get('begDate', QtCore.QDate())
endDate = params.get('endDate', QtCore.QDate())
eventPurposeId = params.get('eventPurposeId', None)
eventTypeId = params.get('eventTypeId', None)
orgStructureId = params.get('orgStructureId', None)
specialityId = params.get('specialityId', None)
personId = params.get('personId', None)
workOrgId = params.get('workOrgId', None)
sex = params.get('sex', 0)
ageFrom = params.get('ageFrom', 0)
ageTo = params.get('ageTo', 150)
areaIdEnabled = params.get('areaIdEnabled', False)
chkOrgStructureArea = params.get('chkOrgStructureArea', False)
areaId = params.get('areaId', None)
MKBFilter = params.get('MKBFilter', 0)
MKBFrom = params.get('MKBFrom', 'A00')
MKBTo = params.get('MKBTo', 'Z99.9')
MKBExFilter = params.get('MKBExFilter', 0)
MKBExFrom = params.get('MKBExFrom', 'A00')
MKBExTo = params.get('MKBExTo', 'Z99.9')
addressEnabled = params.get('addressEnabled', False)
addressType = params.get('addressType', 0)
clientAddressType = params.get('clientAddressType', 0)
clientAddressCityCode = params.get('clientAddressCityCode', None)
clientAddressCityCodeList = params.get('clientAddressCityCodeList', None)
clientAddressStreetCode = params.get('clientAddressStreetCode', None)
clientHouse = params.get('clientHouse', '')
clientCorpus = params.get('clientCorpus', '')
clientFlat = params.get('clientFlat', '')
order = params.get('order', 0)
primary = params.get('primary', 0)
resultId = params.get('resultId', None)
visitEmergency = params.get('visitEmergency', False)
db = QtGui.qApp.db
tableClient = db.table('Client')
tableEvent = db.table('Event')
tableEventType = db.table('EventType')
tableClientAddress = db.table('ClientAddress')
tableAddress = db.table('Address')
tableOrgStructureAddress = db.table('OrgStructure_Address')
tableDiagnostic = db.table('Diagnostic')
tableDiagnosis = db.table('Diagnosis')
tableDiagnosisType = db.table('rbDiagnosisType')
tablePerson = db.table('vrbPersonWithSpeciality')
tableResult = db.table('rbResult')
tableAddressHouseLiv = db.table('AddressHouse').alias('AddressHouseLiv')
tableClientAddressForClientLiv = db.table('ClientAddress').alias(
'ClientAddressForClientLiv')
tableAddressForClientLiv = db.table('Address').alias('AddressForClientLiv')
tableAddressHouseReg = db.table('AddressHouse').alias('AddressHouseReg')
tableClientAddressForClientReg = db.table('ClientAddress').alias(
'ClientAddressForClientReg')
tableAddressForClientReg = db.table('Address').alias('AddressForClientReg')
tableKLADRCodeReg = db.table('kladr.KLADR')
tableKLADRStreetReg = db.table('kladr.STREET')
tableClientContact = db.table('ClientContact')
tableContactType = db.table('rbContactType')
tableVisit = db.table('Visit')
tableScene = db.table('rbScene')
queryTable = tableEvent
queryTable = queryTable.leftJoin(
tableResult, tableResult['id'].eq(tableEvent['result_id']))
queryTable = queryTable.leftJoin(
tableClient, tableClient['id'].eq(tableEvent['client_id']))
queryTable = queryTable.leftJoin(
tableClientContact,
tableClientContact['client_id'].eq(tableClient['id']))
queryTable = queryTable.leftJoin(
tableContactType,
tableContactType['id'].eq(tableClientContact['contactType_id']))
queryTable = queryTable.leftJoin(
tableEventType, tableEventType['id'].eq(tableEvent['eventType_id']))
queryTable = queryTable.leftJoin(
tableClientAddress,
tableClientAddress['client_id'].eq(tableClient['id']))
queryTable = queryTable.leftJoin(
tableAddress, tableAddress['id'].eq(tableClientAddress['address_id']))
queryTable = queryTable.leftJoin(
tableVisit, tableEvent['id'].eq(tableVisit['event_id']))
queryTable = queryTable.leftJoin(
tableScene, tableScene['id'].eq(tableVisit['scene_id']))
diagnosticJoinCond = '''Diagnostic.event_id = Event.id AND Diagnostic.id IN
(SELECT D1.id
FROM Diagnostic AS D1
LEFT JOIN rbDiagnosisType AS DT1 ON DT1.id = D1.diagnosisType_id
WHERE D1.event_id = Event.id AND DT1.code =
(SELECT MIN(DT2.code)
FROM Diagnostic AS D2
LEFT JOIN rbDiagnosisType AS DT2 ON DT2.id = D2.diagnosisType_id WHERE D2.event_id = Event.id
)
)'''
queryTable = queryTable.leftJoin(tableDiagnostic, diagnosticJoinCond)
queryTable = queryTable.leftJoin(
tableDiagnosis,
tableDiagnosis['id'].eq(tableDiagnostic['diagnosis_id']))
queryTable = queryTable.leftJoin(
tableDiagnosisType,
tableDiagnosisType['id'].eq(tableDiagnosis['diagnosisType_id']))
queryTable = queryTable.leftJoin(
tablePerson, tablePerson['id'].eq(tableEvent['execPerson_id']))
joinCondClientAddressForClientReg = 'ClientAddressForClientReg.client_id = Event.client_id AND ClientAddressForClientReg.id = (SELECT MAX(id) FROM ClientAddress AS CAReg WHERE CAReg.Type=0 and CAReg.client_id = Event.client_id)'
queryTable = queryTable.leftJoin(tableClientAddressForClientReg,
joinCondClientAddressForClientReg)
queryTable = queryTable.leftJoin(
tableAddressForClientReg, tableAddressForClientReg['id'].eq(
tableClientAddressForClientReg['address_id']))
queryTable = queryTable.leftJoin(
tableAddressHouseReg,
tableAddressHouseReg['id'].eq(tableAddressForClientReg['house_id']))
queryTable = queryTable.leftJoin(
tableKLADRCodeReg,
tableKLADRCodeReg['CODE'].eq(tableAddressHouseReg['KLADRCode']))
queryTable = queryTable.leftJoin(
tableKLADRStreetReg, tableKLADRStreetReg['CODE'].eq(
tableAddressHouseReg['KLADRStreetCode']))
joinCondClientAddressForClientLiv = 'ClientAddressForClientLiv.client_id = Event.client_id AND ClientAddressForClientLiv.id = (SELECT MAX(id) FROM ClientAddress AS CALiv WHERE CALiv.Type=0 and CALiv.client_id = Event.client_id)'
queryTable = queryTable.leftJoin(tableClientAddressForClientLiv,
joinCondClientAddressForClientLiv)
queryTable = queryTable.leftJoin(
tableAddressForClientLiv, tableAddressForClientLiv['id'].eq(
tableClientAddressForClientLiv['address_id']))
queryTable = queryTable.leftJoin(
tableAddressHouseLiv,
tableAddressHouseLiv['id'].eq(tableAddressForClientLiv['house_id']))
cond = []
cond.append(tableEvent['deleted'].eq(0))
cond.append(tableEvent['execDate'].ge(begDate))
cond.append(
db.joinOr([
tableEvent['execDate'].lt(endDate.addDays(1)),
tableEvent['execDate'].isNull()
]))
# cond.append(tableClientContact['deleted'].eq(0))
if eventTypeId:
cond.append(tableEvent['eventType_id'].eq(eventTypeId))
elif eventPurposeId:
cond.append(db.table('EventType')['purpose_id'].eq(eventPurposeId))
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 personId:
cond.append(tableDiagnostic['person_id'].eq(personId))
elif orgStructureId:
cond.append(tablePerson['orgStructure_id'].inlist(
getOrgStructureDescendants(orgStructureId)))
else:
cond.append(tablePerson['org_id'].eq(QtGui.qApp.currentOrgId()))
if specialityId:
cond.append(tablePerson['speciality_id'].eq(specialityId))
if workOrgId:
cond.append(
'EXISTS (SELECT * FROM ClientWork WHERE ClientWork.id = (SELECT MAX(id) FROM ClientWork AS CW WHERE CW.client_id = Client.id) and ClientWork.org_id=%d)'
% (workOrgId))
if not visitEmergency:
cond.append(
u'''EventType.medicalAidType_id IS NULL OR (EventType.medicalAidType_id NOT IN (SELECT rbMedicalAidType.id from rbMedicalAidType where rbMedicalAidType.code IN (\'4\')))'''
'')
if sex:
cond.append(tableClient['sex'].eq(sex))
if ageFrom <= ageTo:
date = str(QtCore.QDate.currentDate().toString(QtCore.Qt.ISODate))
cond.append(
'IF(Diagnosis.endDate IS NOT NULL, Diagnosis.endDate, DATE(\'%s\')) >= ADDDATE(Client.birthDate, INTERVAL %d YEAR)'
% (date, ageFrom))
cond.append(
'IF(Diagnosis.endDate IS NOT NULL, Diagnosis.endDate, DATE(\'%s\')) < SUBDATE(ADDDATE(Client.birthDate, INTERVAL %d YEAR),1)'
% (date, ageTo + 1))
if areaIdEnabled:
if chkOrgStructureArea:
if areaId:
orgStructureIdList = getOrgStructureDescendants(areaId)
else:
orgStructureIdList = getOrgStructures(
QtGui.qApp.currentOrgId())
subCond = [
tableOrgStructureAddress['master_id'].inlist(
orgStructureIdList),
tableOrgStructureAddress['house_id'].eq(
tableAddress['house_id']),
]
cond.append(db.existsStmt(tableOrgStructureAddress, subCond))
if addressEnabled:
if addressType in [STRICT_ADDRESS, NONRESIDENT_ADDRESS]:
clientAddressType = clientAddressType if addressType == STRICT_ADDRESS else 0
if clientAddressType == 0:
tableAddressForClient = tableAddressForClientReg
tableAddressHouse = tableAddressHouseReg
else:
tableAddressForClient = tableAddressForClientLiv
tableAddressHouse = tableAddressHouseLiv
if addressType == STRICT_ADDRESS:
if clientFlat:
cond.append(
tableAddressForClient['flat'].eq(clientFlat))
if clientCorpus:
cond.append(
tableAddressHouse['corpus'].eq(clientCorpus))
if clientHouse:
cond.append(
tableAddressHouse['number'].eq(clientHouse))
if clientAddressCityCodeList:
cond.append(tableAddressHouse['KLADRCode'].inlist(
clientAddressCityCodeList))
else:
if clientAddressCityCode:
cond.append(tableAddressHouse['KLADRCode'].eq(
clientAddressCityCode))
if clientAddressStreetCode:
cond.append(tableAddressHouse['KLADRStreetCode'].eq(
clientAddressStreetCode))
else:
props = QtGui.qApp.preferences.appPrefs
kladrCodeList = [
forceString(getVal(props, 'defaultKLADR', '')),
forceString(getVal(props, 'provinceKLADR', ''))
]
cond.append(tableAddressHouse['KLADRCode'].notInlist(
kladrCodeList))
else:
foreignDocumentTypeId = forceInt(
db.translate('rbDocumentType', 'code', '9', 'id'))
documentCond = 'EXISTS(SELECT ClientDocument.`id` FROM ClientDocument WHERE ClientDocument.`documentType_id`=%d AND ClientDocument.`client_id`=Client.`id`)' % foreignDocumentTypeId
cond.append(documentCond)
if order:
cond.append(tableEvent['order'].eq(order - 1))
if primary:
cond.append(tableEvent['isPrimary'].eq(primary))
if resultId:
cond.append(tableEvent['result_id'].eq(resultId))
fields = [
'CONCAT_WS(\' \', Client.`lastName`, Client.`firstName`, Client.`patrName`) AS fio',
tableClient['sex'].name(),
'IF(MONTH(Client.`birthDate`)>MONTH(CURRENT_DATE()) OR (MONTH(Client.`birthDate`) = MONTH(CURRENT_DATE()) AND DAYOFMONTH(Client.`birthDate`) > DAYOFMONTH(CURRENT_DATE())), YEAR(CURRENT_DATE())-YEAR(Client.`birthDate`)-1, YEAR(CURRENT_DATE())-YEAR(Client.`birthDate`)) AS clientAge',
tableClient['id'].alias('clientId'),
tableAddressForClientReg['flat'].alias('clientFlat'),
tableAddressHouseReg['corpus'].alias('clientCorpus'),
tableAddressHouseReg['number'].alias('clientNumber'),
tableKLADRCodeReg['NAME'].alias('kladrName'),
tableKLADRCodeReg['SOCR'].alias('kladrSocr'),
tableKLADRStreetReg['NAME'].alias('kaldrStreetName'),
tableKLADRStreetReg['SOCR'].alias('kladrStreetSocr'),
tableClientContact['contact'].alias('clientContact'),
tableContactType['name'].alias('contactTypeName'),
tableDiagnosis['MKB'].name(),
tableEventType['code'].alias('eventTypeCode'),
tableEventType['name'].alias('eventTypeName'),
tableEvent['setDate'].alias('eventSetDate'),
tableEvent['execDate'].alias('eventExecDate'),
tableEvent['id'].alias('eventId'),
tableClientContact['id'].alias('clientContactId'),
tableClientContact['deleted'].alias('contactDeleted'),
tablePerson['name'].alias('person'), tableScene['name'].alias('scene')
]
stmt = db.selectStmt(queryTable, fields, cond, order='fio')
# print stmt
return db.query(stmt)
def selectDataHurt(begDate, endDate, workOrgId, sex, ageFrom, ageTo, areaIdEnabled, areaId, MKBFilter, MKBFrom, MKBTo, MKBExFilter, MKBExFrom, MKBExTo, socStatusType, socStatusClass, forTeenager, orgStructureAttachTypeId):
db = QtGui.qApp.db
tableDiagnosis = db.table('Diagnosis')
tableClient = db.table('Client')
tableClientWork = db.table('ClientWork')
tableClientWorkHurt = db.table('ClientWork_Hurt')
tableClientAddress = db.table('ClientAddress')
tableAddress = db.table('Address')
tableDiagnostic = db.table('Diagnostic')
tableEvent = db.table('Event')
tableClientSocStatus = db.table('ClientSocStatus')
queryTable = tableDiagnosis
queryTable = queryTable.innerJoin(tableClient, tableClient['id'].eq(tableDiagnosis['client_id']))
queryTable = queryTable.innerJoin(tableClientWork, tableClientWork['client_id'].eq(tableClient['id']))
queryTable = queryTable.innerJoin(tableClientWorkHurt, tableClientWorkHurt['master_id'].eq(tableClientWork['id']))
queryTable = queryTable.innerJoin(tableDiagnostic, tableDiagnostic['diagnosis_id'].eq(tableDiagnosis['id']))
queryTable = queryTable.innerJoin(tableEvent, tableEvent['id'].eq(tableDiagnostic['event_id']))
cond = [tableDiagnosis['deleted'].eq(0)]
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:
cond.append(tableClientWork['org_id'].eq(workOrgId))
if sex:
cond.append(tableClient['sex'].eq(sex))
if not forTeenager:
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:
queryTable = queryTable.innerJoin(tableClientAddress, db.joinAnd([tableClientAddress['client_id'].eq(tableDiagnosis['client_id']), 'ClientAddress.id = (SELECT MAX(id) FROM ClientAddress AS CA0 WHERE CA0.Type=0 and CA0.client_id = Diagnosis.client_id)']))
queryTable = queryTable.innerJoin(tableAddress, tableAddress['id'].eq(tableClientAddress['address_id']))
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 socStatusType or socStatusClass:
queryTable = queryTable.innerJoin(tableClientSocStatus, tableClient['id'].eq(tableClientSocStatus['client_id']))
if socStatusType: cond.append(tableClientSocStatus['socStatusType_id'].eq(socStatusType))
if socStatusClass: cond.append(tableClientSocStatus['socStatusClass_id'].eq(socStatusClass))
if forTeenager:
cond.append('age(Client.birthDate, Diagnosis.endDate) <= 17')
if orgStructureAttachTypeId:
tableClientAttach = db.table('ClientAttach')
tableOrgStructure = db.table('OrgStructure')
attachTypeId = forceRef(db.translate('rbAttachType', 'code', u'1', 'id'))
queryTable = queryTable.leftJoin(tableClientAttach, '''ClientAttach.id =(SELECT max(clAttach.id)
FROM ClientAttach clAttach
WHERE clAttach.attachType_id = %s
AND clAttach.client_id = Client.id)''' % (attachTypeId))
queryTable = queryTable.leftJoin(tableOrgStructure, tableOrgStructure['id'].eq(tableClientAttach['orgStructure_id']))
orgStructureIdList = db.getDescendants('OrgStructure', 'parent_id', orgStructureAttachTypeId)
cond.append(tableClientAttach['orgStructure_id'].inlist(orgStructureIdList))
if not forTeenager:
endDate1 = forceString(endDate)
endDate1 = endDate1.split('.')
endDateYear = endDate
begDateYear = endDate
begDateYear.setDate(forceInt(endDate1[2]),1,1)
else:
begDateYear = begDate
endDateYear = endDate
if forTeenager:
select = ['''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtSetInYear''' % begDateYear.toString('yyyy-MM-dd'),
'''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND ((Diagnostic.`character_id`= 1) OR (Diagnostic.`character_id`= 2)) AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtSetFirstInYear''' % begDateYear.toString('yyyy-MM-dd'),
'''COUNT( IF (DATE(Event.`execDate`) >= DATE('%s') AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtExecInYear''' % begDateYear.toString('yyyy-MM-dd'),
'''COUNT( IF (DATE(Event.`setDate`) <= DATE('%s') AND (Diagnostic.`dispanser_id` = 2) AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtInLastYear''' % begDateYear.toString('yyyy-MM-dd'),
'''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND (Diagnostic.`dispanser_id` = 1) AND age(Client.birthDate, Event.setDate) IN (15, 16, 17),Diagnosis.`id`,NULL)) AS countHurtInThisYear''' % begDateYear.toString('yyyy-MM-dd'),
]
else:
select = ['''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtSetInYear''' % begDateYear.toString('yyyy-MM-dd'),
'''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND ((Diagnostic.`character_id`= 1) OR (Diagnostic.`character_id`= 2)) AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtSetFirstInYear''' % begDateYear.toString('yyyy-MM-dd'),
'''COUNT( IF (DATE(Event.`execDate`) >= DATE('%s') AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtExecInYear''' % begDateYear.toString('yyyy-MM-dd'),
'''COUNT( IF (DATE(Event.`setDate`) <= DATE('%s') AND (Diagnostic.`dispanser_id` = 2) AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtInLastYear''' % begDateYear.toString('yyyy-MM-dd'),
'''COUNT( IF (DATE(Event.`setDate`) >= DATE('%s') AND (Diagnostic.`dispanser_id` = 1) AND ClientWork_Hurt.`master_id` IS NOT NULL,Diagnosis.`id`,NULL)) AS countHurtInThisYear''' % begDateYear.toString('yyyy-MM-dd'),
]
select.append(tableDiagnosis['MKB'])
select.append(tableDiagnosis['dispanser_id'])
return db.selectStmt(queryTable, select,
where=cond,group='Diagnosis.MKB')
