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)