def test_select_join(self):
t1 = Table('t1')
t2 = Table('t2')
join = Join(t1, t2)
self.assertEqual(str(join.select()),
'SELECT * FROM "t1" AS "a" INNER JOIN "t2" AS "b"')
self.assertEqual(str(join.select(getattr(t1, '*'))),
'SELECT "a".* FROM "t1" AS "a" INNER JOIN "t2" AS "b"')
def table_query():
pool = Pool()
inventory_two = pool.get('hrp_inventory.inventory_two').__table__()
inventory_lines = pool.get(
'hrp_inventory.inventory_two_lines').__table__()
join1 = Join(inventory_lines, inventory_two)
join1.condition = join1.right.id == inventory_lines.inventory
where = Literal(True)
if Transaction().context.get('inventor_time'):
where &= inventory_two.id == Transaction().context['inventor_time']
where &= inventory_lines.category == Transaction(
).context['category']
where &= inventory_lines.type != 'balance'
Result = join1.select(
join1.left.id.as_('id'),
Max(inventory_lines.create_uid).as_('create_uid'),
Max(inventory_lines.create_date).as_('create_date'),
Max(inventory_lines.write_uid).as_('write_uid'),
Max(inventory_lines.write_date).as_('write_date'),
inventory_lines.code,
inventory_lines.uom,
inventory_lines.name,
inventory_lines.product,
inventory_lines.drug_specifications,
inventory_lines.cost_pice,
inventory_lines.type,
inventory_lines.differences_why,
where=where,
group_by=inventory_lines.id)
return Result
def table_query():
pool = Pool()
evaluation = pool.get('gnuhealth.patient.evaluation').__table__()
party = pool.get('party.party').__table__()
patient = pool.get('gnuhealth.patient').__table__()
du = pool.get('gnuhealth.du').__table__()
sector = pool.get('gnuhealth.operational_sector').__table__()
join1 = Join(evaluation, patient)
join1.condition = join1.right.id == evaluation.patient
join2 = Join(join1, party)
join2.condition = join2.right.id == join1.right.name
join3 = Join(join2, du)
join3.condition = join3.right.id == join2.right.du
join4 = Join(join3, sector)
join4.condition = join4.right.id == join3.right.operational_sector
where = Literal(True)
if Transaction().context.get('start_date'):
where &= evaluation.evaluation_start >= \
Transaction().context['start_date']
if Transaction().context.get('end_date'):
where &= evaluation.evaluation_start <= \
Transaction().context['end_date']
return join4.select(join4.right.id,
Max(evaluation.create_uid).as_('create_uid'),
Max(evaluation.create_date).as_('create_date'),
Max(evaluation.write_uid).as_('write_uid'),
Max(evaluation.write_date).as_('write_date'),
join4.right.id.as_('sector'),
Count(join4.right.id).as_('evaluations'),
where=where,
group_by=join4.right.id)
def table_query():
pool = Pool()
evaluation = pool.get('gnuhealth.patient.evaluation').__table__()
party = pool.get('party.party').__table__()
patient = pool.get('gnuhealth.patient').__table__()
du = pool.get('gnuhealth.du').__table__()
sector = pool.get('gnuhealth.operational_sector').__table__()
join1 = Join(evaluation, patient)
join1.condition = join1.right.id == evaluation.patient
join2 = Join(join1, party)
join2.condition = join2.right.id == join1.right.name
join3 = Join(join2, du)
join3.condition = join3.right.id == join2.right.du
join4 = Join(join3, sector)
join4.condition = join4.right.id == join3.right.operational_sector
where = Literal(True)
if Transaction().context.get('start_date'):
where &= evaluation.evaluation_start >= \
Transaction().context['start_date']
if Transaction().context.get('end_date'):
where &= evaluation.evaluation_start <= \
Transaction().context['end_date']
return join4.select(
join4.right.id,
Max(evaluation.create_uid).as_('create_uid'),
Max(evaluation.create_date).as_('create_date'),
Max(evaluation.write_uid).as_('write_uid'),
Max(evaluation.write_date).as_('write_date'),
join4.right.id.as_('sector'),
Count(join4.right.id).as_('evaluations'),
where=where,
group_by=join4.right.id)
def table_query(cls):
pool = Pool()
xaction = Transaction()
appointment = pool.get('gnuhealth.appointment').__table__()
party = pool.get('party.party').__table__()
patient = pool.get('gnuhealth.patient').__table__()
join1 = Join(appointment, patient)
join1.condition = join1.right.id == appointment.patient
join2 = Join(join1, party)
join2.condition = join2.right.id == join1.right.name
where = Literal(True)
if xaction.context.get('date_start'):
where &= (appointment.appointment_date >=
xaction.context['date_start'])
if xaction.context.get('date_end'):
where &= (appointment.appointment_date <
xaction.context['date_end'] + timedelta(days=1))
if xaction.context.get('healthprof'):
where &= \
appointment.healthprof == xaction.context['healthprof']
if xaction.context.get('specialty', False):
where &= appointment.speciality == xaction.context['specialty']
return join2.select(
appointment.id,
appointment.create_uid,
appointment.create_date,
appointment.write_uid,
appointment.write_date,
join2.right.ref,
join1.right.id.as_('patient'),
join2.right.sex,
appointment.appointment_date,
appointment.appointment_date.as_('appointment_date_time'),
appointment.healthprof,
appointment.speciality,
where=where)
