def table_query():
pool = Pool()
Evaluation = pool.get('gnuhealth.patient.evaluation')
evaluation = Evaluation.__table__()
source = evaluation
where = evaluation.diagnosis != None
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']
if Transaction().context.get('group'):
DiseaseGroupMembers = pool.get('gnuhealth.disease_group.members')
diseasegroupmembers = DiseaseGroupMembers.__table__()
join = Join(evaluation, diseasegroupmembers)
join.condition = join.right.name == evaluation.diagnosis
where &= join.right.disease_group == Transaction().context['group']
source = join
select = source.select(
evaluation.diagnosis.as_('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'),
evaluation.diagnosis.as_('disease'),
Count(evaluation.diagnosis).as_('cases'),
where=where,
group_by=evaluation.diagnosis)
if Transaction().context.get('number_records'):
select.limit = Transaction().context['number_records']
return select
def table_query():
pool = Pool()
Evaluation = pool.get('gnuhealth.patient.evaluation')
evaluation = Evaluation.__table__()
source = evaluation
where = evaluation.diagnosis != None
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']
if Transaction().context.get('group'):
DiseaseGroupMembers = pool.get('gnuhealth.disease_group.members')
diseasegroupmembers = DiseaseGroupMembers.__table__()
join = Join(evaluation, diseasegroupmembers)
join.condition = join.right.name == evaluation.diagnosis
where &= join.right.disease_group == Transaction().context['group']
source = join
select = source.select(evaluation.diagnosis.as_('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'),
evaluation.diagnosis.as_('disease'),
Count(evaluation.diagnosis).as_('cases'),
where=where,
group_by=evaluation.diagnosis)
if Transaction().context.get('number_records'):
select.limit = Transaction().context['number_records']
return select
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 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()
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 test_join_subselect(self):
t1 = Table('t1')
t2 = Table('t2')
select = t2.select()
join = Join(t1, select)
join.condition = t1.c == select.c
with AliasManager():
self.assertEqual(str(join),
'"t1" AS "a" INNER JOIN (SELECT * FROM "t2" AS "c") AS "b" '
'ON ("a"."c" = "b"."c")')
self.assertEqual(join.params, ())
def test_join(self):
t1 = Table('t1')
t2 = Table('t2')
join = Join(t1, t2)
with AliasManager():
self.assertEqual(str(join), '"t1" AS "a" INNER JOIN "t2" AS "b"')
self.assertEqual(join.params, ())
join.condition = t1.c == t2.c
with AliasManager():
self.assertEqual(str(join),
'"t1" AS "a" INNER JOIN "t2" AS "b" ON ("a"."c" = "b"."c")')
def parse(self, query):
result = []
while query:
expression = query.pop()
if (not Expression.is_expression(expression)
and expression not in self.operators):
raise InvalidExpressionException
if Expression.is_expression(expression):
field = expression[0]
column = getattr(self.table, field)
table = self.table
if '.' in field:
for idx, field_join in enumerate(field.split('.')):
if idx + 1 < len(field.split('.')):
fk = self.foreign_key(table._name)[field_join]
table_join = Table(fk['foreign_table_name'])
join = Join(self.join_on, table_join)
column = getattr(table, field_join)
fk_col = getattr(join.right, fk['foreign_column_name'])
join.condition = Equal(column, fk_col)
join = self.get_join(join)
if not join:
join = self.join_on.join(table_join)
join.condition = Equal(column, fk_col)
self.joins.append(join)
table = table_join
else:
table = join.right
else:
column = getattr(join.right, field_join)
expression = Expression(expression)
expression.left = column
result.append(expression.expression)
else:
op = self.operators[expression]
q = []
for _ in range(0, op.n_pops):
q1 = result.pop()
q.append(q1)
result.append(op(q))
return And(reversed(result))
def parse_join(self, fields_join, join_type):
table = self.table
self.join_path = []
for field_join in fields_join:
self.join_path.append(field_join)
fk = self.foreign_key(table._name, field_join)
table_join = Table(fk['foreign_table_name'])
join = Join(self.join_on, table_join, type_=join_type)
column = getattr(table, fk['column_name'])
fk_col = getattr(join.right, fk['foreign_column_name'])
join.condition = Equal(column, fk_col)
dotted_path = '.'.join(self.join_path)
join = self.get_join(dotted_path)
if not join:
join = self.join_on.join(table_join, type_=join_type)
join.condition = Equal(column, fk_col)
self.joins_map[dotted_path] = join
self.joins.append(join)
table = table_join
else:
if join not in self.joins:
self.joins.append(join)
table = join.right
def _get_pdq_results_table(self):
logger.debug("PQQ building query...")
patient_party_join = Join(self.patient_table, self.party_table)
patient_party_join.type_ = 'LEFT'
patient_party_join.condition = patient_party_join.left.name == self.party_table.id
# Second join, with address table
patient_party_addr_join = Join(patient_party_join, self.address_table)
patient_party_addr_join.type_ = 'LEFT'
patient_party_addr_join.condition = patient_party_addr_join.right.party == patient_party_join.right.id
logger.debug("PDQ JOIN TABLE CREATED")
return patient_party_addr_join
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)
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 _get_pdqv_results_table(self):
logger.debug("PQQV building query")
# JOIN 1, party table with patient table
# LEFT JOIN gnuhealth_patient on gnuhealth_patient.name=party_party.id
patient_party_join = Join(self.patient_table, self.party_table)
patient_party_join.type_ = 'LEFT'
patient_party_join.condition = patient_party_join.left.name == self.party_table.id
# JOIN 2, with address table
patient_party_addr_join = Join(patient_party_join, self.address_table)
patient_party_addr_join.type_ = 'LEFT'
patient_party_addr_join.condition = patient_party_addr_join.right.party == patient_party_join.right.id
# JOIN 3 , with inpatient_registration table
# LEFT JOIN gnuhealth_inpatient_registration ON gnuhealth_inpatient_registration.patient = gnuhealth_patient.id
patient_party_reg_join = Join(patient_party_addr_join, self.inpatient_registration_table)
patient_party_reg_join.type_ = 'LEFT'
patient_party_reg_join.condition = self.inpatient_registration_table.patient == self.patient_table.id
# JOIN 4, with hospital_bed table
# LEFT JOIN gnuhealth_hospital_bed ON gnuhealth_hospital_bed.id = gnuhealth_inpatient_registration.bed
patient_party_bed_join = Join(patient_party_reg_join, self.hospital_bed_table)
patient_party_bed_join.type_ = "LEFT"
patient_party_bed_join.condition = self.hospital_bed_table.id == self.inpatient_registration_table.bed
# JOIN 5, with hospital_ward table
# LEFT JOIN gnuhealth_hospital_ward ON gnuhealth_hospital_ward.id = gnuhealth_hospital_bed.ward
# where gnuhealth_hospital_ward.name='Maternity'
patient_party_ward_join = Join(patient_party_bed_join, self.hospital_ward_table)
patient_party_ward_join.type_ = "LEFT"
patient_party_ward_join.condition = self.hospital_ward_table.id == self.hospital_bed_table.ward
logger.debug("PDQV JOIN TABLE CREATED")
return patient_party_ward_join
def table_query(self=None):
UomCategory = Pool().get('product.category')
Number = Pool().get('order_no')
number = Number.__table__()
ConsumeProduct = Pool().get('stock.shipment.out.return')
consume_product = ConsumeProduct.__table__()
ReturnProduct = Pool().get('stock.shipment.out')
return_product = ReturnProduct.__table__()
where = Literal(True)
join1 = Join(number, consume_product)
join1.condition = join1.right.warehouse == number.location
join2 = Join(join1, return_product)
join2.condition = join2.right.warehouse == join1.right.warehouse
content = [('order_category', 'in', ['sale_return', 'sale_purchase'])]
if Transaction().context.get('start_time') != None:
content.append(('time', '>=', Transaction().context.get('start_time')), )
if Transaction().context.get('end_time') != None:
content.append(('time', '<=', Transaction().context.get('end_time')), )
if Transaction().context.get('drug_type') == '00':
drug_type_name = u'西药'
if Transaction().context.get('drug_type') == '01':
drug_type_name = u'中成药'
if Transaction().context.get('drug_type') == '02':
drug_type_name = u'中草药'
if Transaction().context.get('drug_type') == '03':
drug_type_name = u'颗粒中'
if Transaction().context.get('drug_type') == '04':
drug_type_name = u'原料药'
if Transaction().context.get('drug_type') == '05':
drug_type_name = u'敷药'
if Transaction().context.get('drug_type') == '07':
drug_type_name = u'同位素'
if Transaction().context.get('location'):
content.append(('location', '=', Transaction().context.get('location')), )
numbers_id = []
start_id = []
number_num = []
number_end = []
finally_id = []
number_id = Number.search(content)
for i in number_id:
number_dict = {}
number_dict['id'] = i.id
number_dict['number'] = i.number
numbers_id.append(number_dict)
number_num.append(i.number)
start_id.append(i.id)
if Transaction().context.get('customer'):
for each in number_num:
if Transaction().context.get('drug_type') == '06':
cosum_ = ConsumeProduct.search([('number', '=', each)])
if cosum_:
for cosum_each in cosum_:
if cosum_each.customer.id == Transaction().context.get('customer'):
number_end.append(each)
else:
pass
else:
pass
return_ = ReturnProduct.search([('number', '=', each)])
if return_:
for return_each in return_:
if return_each.customer.id == Transaction().context.get('customer'):
number_end.append(each)
else:
pass
else:
cosum_ = ConsumeProduct.search([('number', '=', each)])
if cosum_:
for cosum_each in cosum_:
if cosum_each.customer.id == Transaction().context.get('customer'):
incoming_moves = cosum_each.incoming_moves
if incoming_moves:
drug_type_id = []
for orderpoint in incoming_moves:
categories = [i.id for i in orderpoint.product.categories]
uom_category = UomCategory.search([('id', '=', categories[0])])
uom_name = uom_category[0].name
if drug_type_name == uom_name:
drug_type_id.append(1)
else:
pass
if len(drug_type_id) != 0:
number_end.append(each)
else:
pass
else:
pass
return_ = ReturnProduct.search([('number', '=', each)])
if return_:
for return_each in return_:
if return_each.customer.id == Transaction().context.get('customer'):
outgoing_moves = return_each.outgoing_moves
if outgoing_moves:
drug_type_id = []
for orderpoint in outgoing_moves:
categories = [i.id for i in orderpoint.product.categories]
uom_category = UomCategory.search([('id', '=', categories[0])])
uom_name = uom_category[0].name
if drug_type_name == uom_name:
drug_type_id.append(1)
else:
pass
if len(drug_type_id) != 0:
number_end.append(each)
else:
pass
for num in number_end:
for all in numbers_id:
if all['number'] == num:
finally_id.append(all['id'])
else:
pass
if finally_id == []:
where = Literal(False)
else:
where &= number.id.in_(finally_id)
else:
if start_id == []:
where = Literal(False)
else:
where &= number.id.in_(start_id)
where &= number.order_category.in_([
'sale_return',
'sale_purchase',
])
Result = number.select(
number.id.as_('id'),
Max(number.create_uid).as_('create_uid'),
Max(number.create_date).as_('create_date'),
Max(number.write_uid).as_('write_uid'),
Max(number.write_date).as_('write_date'),
number.number,
where=where,
group_by=number.id)
return Result
def test_join_function(self):
t1 = Table('t1')
join = Join(t1, Now())
with AliasManager():
self.assertEqual(str(join), '"t1" AS "a" INNER JOIN NOW() AS "b"')
self.assertEqual(join.params, ())