def _migrate_english_report(cls):
cursor = Transaction().connection.cursor()
pool = Pool()
Configuration = pool.get('lims.configuration')
Lang = pool.get('ir.lang')
party_table = cls.__table__()
configuration_table = Configuration.__table__()
lang_table = Lang.__table__()
cursor.execute(*configuration_table.select(
configuration_table.results_report_language,
where=Literal(True)))
default_language = cursor.fetchone()
if default_language:
cursor.execute(*party_table.update(
[party_table.report_language], [default_language[0]],
where=(party_table.english_report == Literal(False))))
cursor.execute(*lang_table.select(
lang_table.id,
where=lang_table.code == Literal('en')))
english_language = cursor.fetchone()
if english_language:
cursor.execute(*party_table.update(
[party_table.report_language], [english_language[0]],
where=(party_table.english_report == Literal(True))))
def get_interface(cls, typifications, name):
cursor = Transaction().connection.cursor()
pool = Pool()
Template = pool.get('lims.template.analysis_sheet')
TemplateAnalysis = pool.get('lims.template.analysis_sheet.analysis')
result = {}
for t in typifications:
cursor.execute(
'SELECT t.id '
'FROM "' + Template._table + '" t '
'INNER JOIN "' + TemplateAnalysis._table + '" ta '
'ON t.id = ta.template '
'WHERE t.active IS TRUE '
'AND ta.analysis = %s '
'AND ta.method = %s', (t.analysis.id, t.method.id))
template_id = cursor.fetchone()
result[t.id] = None
if not template_id:
cursor.execute(
'SELECT t.id '
'FROM "' + Template._table + '" t '
'INNER JOIN "' + TemplateAnalysis._table + '" ta '
'ON t.id = ta.template '
'WHERE t.active IS TRUE '
'AND ta.analysis = %s '
'AND ta.method IS NULL', (t.analysis.id, ))
template_id = cursor.fetchone()
if template_id:
template = Template(template_id[0])
result[t.id] = template.interface.id
return result
def test_current_timestamp_static_transaction(self):
"Test CURRENT_TIMESTAMP is static during transaction"
query = Select([CurrentTimestamp()])
cursor = Transaction().connection.cursor()
cursor.execute(*query)
current, = cursor.fetchone()
cursor.execute(*query)
second, = cursor.fetchone()
self.assertEqual(current, second)
def test_current_timestamp_reset_after_commit(self):
"Test CURRENT_TIMESTAMP is reset after commit"
query = Select([CurrentTimestamp()])
cursor = Transaction().connection.cursor()
cursor.execute(*query)
current, = cursor.fetchone()
Transaction().commit()
cursor.execute(*query)
second, = cursor.fetchone()
self.assertNotEqual(current, second)
def get_patient_person_id(self, name):
cursor = Transaction().cursor
login_user_id = self.id
cursor.execute('SELECT patient FROM res_user WHERE \
id = %s LIMIT 1', (login_user_id,))
patient_id = cursor.fetchone()
cursor = Transaction().cursor
cursor.execute('SELECT name FROM gnuhealth_patient WHERE \
id = %s LIMIT 1', (patient_id[0],))
person_id = cursor.fetchone()
if (person_id):
return int(person_id[0])
def default_health_professional():
cursor = Transaction().cursor
User = Pool().get('res.user')
user = User(Transaction().user)
login_user_id = int(user.id)
cursor.execute('SELECT id FROM party_party WHERE is_healthprof=True AND \
internal_user = %s LIMIT 1', (login_user_id,))
partner_id = cursor.fetchone()
if partner_id:
cursor = Transaction().cursor
cursor.execute('SELECT id FROM gnuhealth_healthprofessional WHERE \
name = %s LIMIT 1', (partner_id[0],))
doctor_id = cursor.fetchone()
return int(doctor_id[0])
def test_current_timestamp_different_transaction(self):
"Test CURRENT_TIMESTAMP is different per transaction"
query = Select([CurrentTimestamp()])
cursor = Transaction().connection.cursor()
cursor.execute(*query)
current, = cursor.fetchone()
with Transaction().new_transaction() as transaction:
cursor = transaction.connection.cursor()
cursor.execute(*query)
second, = cursor.fetchone()
self.assertNotEqual(current, second)
def default_doctor():
cursor = Transaction().cursor
User = Pool().get('res.user')
user = User(Transaction().user)
login_user_id = int(user.id)
cursor.execute('SELECT id FROM party_party WHERE is_healthprof=True AND \
internal_user = %s LIMIT 1', (login_user_id,))
partner_id = cursor.fetchone()
if partner_id:
cursor = Transaction().cursor
cursor.execute('SELECT id FROM gnuhealth_healthprofessional WHERE \
name = %s LIMIT 1', (partner_id[0],))
doctor_id = cursor.fetchone()
return int(doctor_id[0])
def count(cls, login):
cursor = Transaction().connection.cursor()
table = cls.__table__()
cursor.execute(*table.select(Count(Literal('*')),
where=(table.login == login)
& (table.create_date >= cls.delay())))
return cursor.fetchone()[0]
def get_correction(self, value):
cursor = Transaction().connection.cursor()
DeviceCorrection = Pool().get('lims.lab.device.correction')
try:
value = float(value)
except ValueError:
return value
cursor.execute('SELECT formula '
'FROM "' + DeviceCorrection._table + '" '
'WHERE device = %s '
'AND result_from::float <= %s::float '
'AND result_to::float >= %s::float',
(str(self.id), value, value))
correction = cursor.fetchone()
if not correction:
return value
formula = correction[0]
for i in (' ', '\t', '\n', '\r'):
formula = formula.replace(i, '')
variables = {'X': value}
parser = FormulaParser(formula, variables)
return parser.getValue()
def __register__(cls, module_name):
cursor = Transaction().connection.cursor()
model_data = Table('ir_model_data')
# Migration from 3.0: new tax rates
if module_name == 'account_fr':
for old_id, new_id in (
('tva_vente_19_6', 'tva_vente_taux_normal'),
('tva_vente_7', 'tva_vente_taux_intermediaire'),
('tva_vente_intracommunautaire_19_6',
'tva_vente_intracommunautaire_taux_normal'),
('tva_vente_intracommunautaire_7',
'tva_vente_intracommunautaire_taux_intermediaire'),
('tva_achat_19_6', 'tva_achat_taux_normal'),
('tva_achat_7', 'tva_achat_taux_intermediaire'),
('tva_achat_intracommunautaire_19_6',
'tva_achat_intracommunautaire_taux_normal'),
):
cursor.execute(
*model_data.select(model_data.id,
where=(model_data.fs_id == new_id)
& (model_data.module == module_name)))
if cursor.fetchone():
continue
cursor.execute(
*model_data.update(columns=[model_data.fs_id],
values=[new_id],
where=(model_data.fs_id == old_id)
& (model_data.module == module_name)))
super(TaxTemplate, cls).__register__(module_name)
def check_date_overlap(self):
cursor = Transaction().cursor
if self.state != "done":
return True
cursor.execute(
"SELECT id "
"FROM stock_forecast "
"WHERE ((from_date <= %s AND to_date >= %s) "
"OR (from_date <= %s AND to_date >= %s) "
"OR (from_date >= %s AND to_date <= %s)) "
"AND warehouse = %s "
"AND destination = %s "
"AND state = 'done' "
"AND company = %s "
"AND id != %s",
(
self.from_date,
self.from_date,
self.to_date,
self.to_date,
self.from_date,
self.to_date,
self.warehouse.id,
self.destination.id,
self.company.id,
self.id,
),
)
forecast_id = cursor.fetchone()
if forecast_id:
second = self.__class__(forecast_id[0])
self.raise_user_error("date_overlap", {"first": self.rec_name, "second": second.rec_name})
def add_raw_column(self, column_name, column_type, column_format,
default_fun=None, field_size=None, migrate=True, string=''):
if self.column_exist(column_name):
if not migrate:
return
base_type = column_type[0].upper()
if base_type != self._columns[column_name]['typname']:
if (self._columns[column_name]['typname'], base_type) in [
('VARCHAR', 'TEXT'),
('TEXT', 'VARCHAR'),
('DATE', 'TIMESTAMP'),
('INTEGER', 'FLOAT'),
]:
self.alter_type(column_name, base_type)
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from %s to %s.',
column_name, self.table_name,
self._columns[column_name]['typname'], base_type)
if (base_type == 'VARCHAR'
and self._columns[column_name]['typname'] == 'VARCHAR'):
# Migrate size
from_size = self._columns[column_name]['size']
if field_size is None:
if from_size > 0:
self.alter_size(column_name, base_type)
elif from_size == field_size:
pass
elif from_size and from_size < field_size:
self.alter_size(column_name, column_type[1])
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from varchar(%s) to varchar(%s).',
column_name, self.table_name,
from_size if from_size and from_size > 0 else "",
field_size)
return
cursor = Transaction().connection.cursor()
column_type = column_type[1]
default = ''
cursor.execute(('ALTER TABLE "%s" ADD COLUMN "%s" %s' + default) %
(self.table_name, column_name, column_type))
if column_format:
# check if table is non-empty:
cursor.execute('SELECT 1 FROM "%s" limit 1' % self.table_name)
if cursor.fetchone():
# Populate column with default values:
default = None
if default_fun is not None:
default = default_fun()
cursor.execute('UPDATE "' + self.table_name + '" '
'SET "' + column_name + '" = ?',
(column_format(default),))
self._update_definitions(columns=True)
def table_exist(table_name):
cursor = Transaction().connection.cursor()
cursor.execute("SELECT sql FROM sqlite_master "
"WHERE type = 'table' AND name = ?",
(table_name,))
res = cursor.fetchone()
if not res:
return False
sql, = res
# Migration from 1.6 add autoincrement
if 'AUTOINCREMENT' not in sql.upper():
temp_sql = sql.replace(table_name, '_temp_%s' % table_name)
cursor.execute(temp_sql)
cursor.execute('PRAGMA table_info("' + table_name + '")')
columns = ['"%s"' % column for _, column, _, _, _, _
in cursor.fetchall()]
cursor.execute(('INSERT INTO "_temp_%s" '
'(' + ','.join(columns) + ') '
'SELECT ' + ','.join(columns) +
' FROM "%s"') % (table_name, table_name))
cursor.execute('DROP TABLE "%s"' % table_name)
new_sql = sql.replace('PRIMARY KEY',
'PRIMARY KEY AUTOINCREMENT')
cursor.execute(new_sql)
cursor.execute(('INSERT INTO "%s" '
'(' + ','.join(columns) + ') '
'SELECT ' + ','.join(columns) +
' FROM "_temp_%s"') % (table_name, table_name))
cursor.execute('DROP TABLE "_temp_%s"' % table_name)
return True
def get_last_release_date(cls, entries, name):
cursor = Transaction().connection.cursor()
pool = Pool()
ResultsVersion = pool.get('lims.results_report.version')
ResultsDetail = pool.get('lims.results_report.version.detail')
NotebookLine = pool.get('lims.notebook.line')
Notebook = pool.get('lims.notebook')
Fraction = pool.get('lims.fraction')
Sample = pool.get('lims.sample')
result = {}
for e in entries:
cursor.execute(
'SELECT MAX(rd.release_date) '
'FROM "' + ResultsVersion._table + '" rv '
'INNER JOIN "' + ResultsDetail._table + '" rd '
'ON rv.id = rd.report_version '
'INNER JOIN "' + NotebookLine._table + '" nl '
'ON nl.results_report = rv.results_report '
'INNER JOIN "' + Notebook._table + '" n '
'ON n.id = nl.notebook '
'INNER JOIN "' + Fraction._table + '" f '
'ON f.id = n.fraction '
'INNER JOIN "' + Sample._table + '" s '
'ON s.id = f.sample '
'WHERE s.entry = %s '
'AND rd.state = \'released\' '
'AND rd.type != \'preliminary\'', (str(e.id), ))
result[e.id] = cursor.fetchone()[0]
return result
def get_qty_lines_pending_invoicing(cls, entries, name):
cursor = Transaction().connection.cursor()
pool = Pool()
Sample = pool.get('lims.sample')
Fraction = pool.get('lims.fraction')
Service = pool.get('lims.service')
InvoiceLine = pool.get('account.invoice.line')
result = {}
for e in entries:
result[e.id] = 0
cursor.execute(
'SELECT srv.id '
'FROM "' + Service._table + '" srv '
'INNER JOIN "' + Fraction._table + '" f '
'ON f.id = srv.fraction '
'INNER JOIN "' + Sample._table + '" s '
'ON s.id = f.sample '
'WHERE s.entry = %s', (str(e.id), ))
services_ids = [x[0] for x in cursor.fetchall()]
if not services_ids:
continue
origins = '\', \''.join(
['lims.service,' + str(s) for s in services_ids])
cursor.execute('SELECT COUNT(*) '
'FROM "' + InvoiceLine._table + '" '
'WHERE origin IN (\'' + origins + '\') '
'AND invoice IS NULL')
result[e.id] = cursor.fetchone()[0]
return result
def __register__(cls, module_name):
cursor = Transaction().cursor
model_data = Table('ir_model_data')
# Migration from 3.0: new tax rates
if module_name == 'account_nl':
for old_id, new_id in (
('tva_vente_19_6', 'tva_vente_taux_normal'),
('tva_vente_7', 'tva_vente_taux_intermediaire'),
('tva_vente_intracommunautaire_19_6',
'tva_vente_intracommunautaire_taux_normal'),
('tva_vente_intracommunautaire_7',
'tva_vente_intracommunautaire_taux_intermediaire'),
('tva_achat_19_6', 'tva_achat_taux_normal'),
('tva_achat_7', 'tva_achat_taux_intermediaire'),
('tva_achat_intracommunautaire_19_6',
'tva_achat_intracommunautaire_taux_normal'),
):
cursor.execute(*model_data.select(model_data.id,
where=(model_data.fs_id == new_id)
& (model_data.module == module_name)))
if cursor.fetchone():
continue
cursor.execute(*model_data.update(
columns=[model_data.fs_id],
values=[new_id],
where=(model_data.fs_id == old_id)
& (model_data.module == module_name)))
super(TaxTemplate, cls).__register__(module_name)
def test_function_random(self):
"Test RANDOM function"
cursor = Transaction().connection.cursor()
cursor.execute(*Select([functions.Random()]))
value, = cursor.fetchone()
self.assertGreaterEqual(value, 0)
self.assertLessEqual(value, 1)
def check_professionals(self, professionals, method):
cursor = Transaction().connection.cursor()
pool = Pool()
LabProfessionalMethod = pool.get('lims.lab.professional.method')
validated = False
msg = ''
for professional in professionals:
cursor.execute(
'SELECT state '
'FROM "' + LabProfessionalMethod._table + '" '
'WHERE professional = %s '
'AND method = %s '
'AND type = \'analytical\' '
'LIMIT 1', (professional[0], method.id))
qualification = cursor.fetchone()
if not qualification:
validated = False
msg += '%s not qualified for method: %s' % (professional[1],
method.code)
return validated, msg
elif qualification[0] == 'training':
if not validated:
msg += '%s in training for method: %s. ' \
'Add qualified professional' % (
professional[1], method.code)
elif (qualification[0] in ('qualified', 'requalified')):
validated = True
return validated, msg
def restore_default_party_lang_from_4_2(cls):
from trytond.transaction import Transaction
from sql import Null, Table, Cast
from sql.operators import Concat
from trytond.pool import Pool
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
pool = Pool()
property = Table('ir_property')
Lang = pool.get('ir.lang')
field = pool.get('ir.model.field').__table__()
lang = Lang.__table__()
cursor = Transaction().connection.cursor()
query_table = property.join(lang, condition=(
property.value == Concat('ir.lang,', Cast(lang.id, 'VARCHAR'))
)).join(field, condition=((property.field == field.id) &
(field.name == 'lang')))
cursor.execute(
*query_table.select(lang.id, where=property.res == Null))
result = cursor.fetchone()
if result:
result = list(result)
default_lang = Lang(result[0])
print('Default Language restored [%s]' % default_lang.rec_name)
pool.get('party.configuration.party_lang'
).create([{'party_lang': default_lang}])
else:
print('No default language on party configuration found')
def confirmed(cls, surgeries):
surgery_id = surgeries[0]
Operating_room = Pool().get('gnuhealth.hospital.or')
cursor = Transaction().connection.cursor()
# Operating Room and end surgery time check
if (not surgery_id.operating_room or not surgery_id.surgery_end_date):
cls.raise_user_error("Operating Room and estimated end time "
"are needed in order to confirm the surgery")
or_id = surgery_id.operating_room.id
cursor.execute(
"SELECT COUNT(*) \
FROM gnuhealth_surgery \
WHERE (surgery_date::timestamp,surgery_end_date::timestamp) \
OVERLAPS (timestamp %s, timestamp %s) \
AND (state = %s or state = %s) \
AND operating_room = CAST(%s AS INTEGER) ",
(surgery_id.surgery_date, surgery_id.surgery_end_date, 'confirmed',
'in_progress', str(or_id)))
res = cursor.fetchone()
if (surgery_id.surgery_end_date < surgery_id.surgery_date):
cls.raise_user_error("The Surgery end date must later than the \
Start")
if res[0] > 0:
cls.raise_user_error('or_is_not_available')
else:
cls.write(surgeries, {'state': 'confirmed'})
def get_translation_4_nereid(cls, module, ttype, lang, source):
"Return translation for source"
ttype = unicode(ttype)
lang = unicode(lang)
source = unicode(source)
cache_key = (lang, ttype, source, module)
trans = cls._nereid_translation_cache.get(cache_key, -1)
if trans != -1:
return trans
cursor = Transaction().cursor
table = cls.__table__()
where = ((table.lang == lang) & (table.type == ttype) &
(table.value != '') & (table.value != None) &
(table.fuzzy == False) & (table.src == source))
if module is not None:
where &= (table.module == module)
cursor.execute(*table.select(table.value, where=where))
res = cursor.fetchone()
if res:
cls._nereid_translation_cache.set(cache_key, res[0])
return res[0]
else:
cls._nereid_translation_cache.set(cache_key, False)
return None
def _get_sequence(cls, sequence):
if sequence.type == 'incremental':
if sql_sequence and not cls._strict:
cursor = Transaction().connection.cursor()
cursor.execute('SELECT nextval(\'"%s"\')' %
sequence._sql_sequence_name)
number_next, = cursor.fetchone()
else:
# Pre-fetch number_next
number_next = sequence.number_next_internal
cls.write(
[sequence], {
'number_next_internal':
(number_next + sequence.number_increment),
})
return '%%0%sd' % sequence.padding % number_next
elif sequence.type in ('decimal timestamp', 'hexadecimal timestamp'):
timestamp = sequence.last_timestamp
while timestamp == sequence.last_timestamp:
timestamp = cls._timestamp(sequence)
cls.write([sequence], {
'last_timestamp': timestamp,
})
if sequence.type == 'decimal timestamp':
return '%d' % timestamp
else:
return hex(timestamp)[2:].upper()
return ''
def table_exist(table_name):
cursor = Transaction().connection.cursor()
cursor.execute("SELECT sql FROM sqlite_master " "WHERE type = 'table' AND name = ?", (table_name,))
res = cursor.fetchone()
if not res:
return False
sql, = res
# Migration from 1.6 add autoincrement
if "AUTOINCREMENT" not in sql.upper():
temp_sql = sql.replace(table_name, "_temp_%s" % table_name)
cursor.execute(temp_sql)
cursor.execute('PRAGMA table_info("' + table_name + '")')
columns = ['"%s"' % column for _, column, _, _, _, _ in cursor.fetchall()]
cursor.execute(
('INSERT INTO "_temp_%s" ' "(" + ",".join(columns) + ") " "SELECT " + ",".join(columns) + ' FROM "%s"')
% (table_name, table_name)
)
cursor.execute('DROP TABLE "%s"' % table_name)
new_sql = sql.replace("PRIMARY KEY", "PRIMARY KEY AUTOINCREMENT")
cursor.execute(new_sql)
cursor.execute(
('INSERT INTO "%s" ' "(" + ",".join(columns) + ") " "SELECT " + ",".join(columns) + ' FROM "_temp_%s"')
% (table_name, table_name)
)
cursor.execute('DROP TABLE "_temp_%s"' % table_name)
return True
def __register__(cls, module_name):
pool = Pool()
Move = pool.get('stock.move')
PurchaseLine = pool.get('purchase.line')
Purchase = pool.get('purchase.purchase')
cursor = Transaction().connection.cursor()
sql_table = cls.__table__()
move = Move.__table__()
line = PurchaseLine.__table__()
purchase = Purchase.__table__()
# Migration from 3.8: New supplier field
cursor.execute(*sql_table.select(
sql_table.supplier, where=sql_table.supplier == Null, limit=1))
if cursor.fetchone():
value = sql_table.join(
move,
condition=(Concat(
cls.__name__ + ',', sql_table.id) == move.shipment)).join(
line,
condition=(Concat(
PurchaseLine.__name__ + ',',
line.id) == move.origin)).join(
purchase,
condition=(
purchase.id == line.purchase)).select(
Max(purchase.party))
cursor.execute(*sql_table.update(columns=[sql_table.supplier],
values=[value]))
super(ShipmentInReturn, cls).__register__(module_name)
def confirmed(cls, registrations):
registration_id = registrations[0]
Bed = Pool().get('gnuhealth.hospital.bed')
cursor = Transaction().connection.cursor()
bed_id = registration_id.bed.id
cursor.execute(
"SELECT COUNT(*) \
FROM gnuhealth_inpatient_registration \
WHERE (hospitalization_date::timestamp,discharge_date::timestamp) \
OVERLAPS (timestamp %s, timestamp %s) \
AND (state = %s or state = %s or state = %s) \
AND bed = CAST(%s AS INTEGER) ",
(registration_id.hospitalization_date,
registration_id.discharge_date, 'confirmed', 'hospitalized',
'done', str(bed_id)))
res = cursor.fetchone()
if (registration_id.discharge_date.date() <
registration_id.hospitalization_date.date()):
cls.raise_user_error("The Discharge date must later than the " \
"Admission")
if res[0] > 0:
cls.raise_user_error('bed_is_not_available')
else:
cls.write(registrations, {'state': 'confirmed'})
Bed.write([registration_id.bed], {'state': 'reserved'})
def create(cls, vlist):
table = cls.get_table()
if table.singleton:
assert len(vlist) == 1
singleton = cls.get_singleton()
if singleton:
cls.write([singleton], vlist[0])
return [singleton]
sql_table = cls.get_sql_table()
cursor = Transaction().connection.cursor()
ids = []
for record in vlist:
fields = []
values = []
for key, value in record.items():
fields.append(sql.Column(sql_table, key))
values.append(value)
query = sql_table.insert(fields,
values=[values],
returning=[sql_table.id])
cursor.execute(*query)
ids.append(cursor.fetchone()[0])
records = cls.browse(ids)
cls.update_formulas(records)
return records
def count(self):
"Return the count of the Items"
from trytond.transaction import Transaction
# XXX: Ideal case should make a copy of Select query
#
# https://code.google.com/p/python-sql/issues/detail?id=22
query = self.query
query.columns = (Count(Distinct(self.primary_table.id)), )
cursor = Transaction().cursor
# temporarily remove order_by
order_by = query.order_by
query.order_by = None
try:
cursor.execute(*query)
finally:
# XXX: This can be removed when SQL queries can be copied
# See comment above
query.order_by = order_by
res = cursor.fetchone()
if res:
return res[0]
# There can be a case when query return None and then count
# will be zero
return 0
def __register__(cls, module_name):
cursor = Transaction().connection.cursor()
model_data = Table('ir_model_data')
# Migration from 5.6: Rename main tax ids
if module_name == 'account_de_skr03':
for old_id, new_id in (
('tax_ust_19', 'tax_ust_standard_rate'),
('tax_ust_7', 'tax_ust_reduced_rate'),
('tax_vst_19', 'tax_vst_standard_rate'),
('tax_vst_7', 'tax_vst_reduced_rate'),
('tax_eu_19_purchase', 'tax_purchase_eu_standard_rate'),
('tax_eu_7_purchase', 'tax_purchase_eu_reduced_rate'),
('tax_import_19', 'tax_import_standard_rate'),
('tax_import_7', 'tax_import_reduced_rate'),
):
cursor.execute(
*model_data.select(model_data.id,
where=(model_data.fs_id == new_id)
& (model_data.module == module_name)))
if cursor.fetchone():
continue
cursor.execute(
*model_data.update(columns=[model_data.fs_id],
values=[new_id],
where=(model_data.fs_id == old_id)
& (model_data.module == module_name)))
super().__register__(module_name)
def confirmed(cls, surgeries):
surgery_id = surgeries[0]
Operating_room = Pool().get('gnuhealth.hospital.or')
cursor = Transaction().cursor
# Operating Room and end surgery time check
if (not surgery_id.operating_room or not surgery_id.surgery_end_date):
cls.raise_user_error("Operating Room and estimated end time "
"are needed in order to confirm the surgery")
or_id = surgery_id.operating_room.id
cursor.execute("SELECT COUNT(*) \
FROM gnuhealth_surgery \
WHERE (surgery_date::timestamp,surgery_end_date::timestamp) \
OVERLAPS (timestamp %s, timestamp %s) \
AND (state = %s or state = %s) \
AND operating_room = CAST(%s AS INTEGER) ",
(surgery_id.surgery_date,
surgery_id.surgery_end_date,
'confirmed', 'in_progress', str(or_id)))
res = cursor.fetchone()
if (surgery_id.surgery_end_date <
surgery_id.surgery_date):
cls.raise_user_error("The Surgery end date must later than the \
Start")
if res[0] > 0:
cls.raise_user_error('or_is_not_available')
else:
cls.write(surgeries, {'state': 'confirmed'})
def confirmed(cls, registrations):
registration_id = registrations[0]
Bed = Pool().get("gnuhealth.hospital.bed")
cursor = Transaction().cursor
bed_id = registration_id.bed.id
cursor.execute(
"SELECT COUNT(*) \
FROM gnuhealth_inpatient_registration \
WHERE (hospitalization_date::timestamp,discharge_date::timestamp) \
OVERLAPS (timestamp %s, timestamp %s) \
AND (state = %s or state = %s) \
AND bed = CAST(%s AS INTEGER) ",
(
registration_id.hospitalization_date,
registration_id.discharge_date,
"confirmed",
"hospitalized",
str(bed_id),
),
)
res = cursor.fetchone()
if registration_id.discharge_date.date() < registration_id.hospitalization_date.date():
cls.raise_user_error(
"The Discharge date must later than the \
Admission"
)
if res[0] > 0:
cls.raise_user_error("bed_is_not_available")
else:
cls.write(registrations, {"state": "confirmed"})
Bed.write([registration_id.bed], {"state": "reserved"})
def get_translation_4_nereid(cls, module, ttype, lang, source):
"Return translation for source"
ttype = unicode(ttype)
lang = unicode(lang)
source = unicode(source)
cache_key = (lang, ttype, source, module)
trans = cls._nereid_translation_cache.get(cache_key, -1)
if trans != -1:
return trans
cursor = Transaction().cursor
table = cls.__table__()
where = (
(table.lang == lang) &
(table.type == ttype) &
(table.value != '') &
(table.value != None) &
(table.fuzzy == False) &
(table.src == source)
)
if module is not None:
where &= (table.module == module)
cursor.execute(*table.select(table.value, where=where))
res = cursor.fetchone()
if res:
cls._nereid_translation_cache.set(cache_key, res[0])
return res[0]
else:
cls._nereid_translation_cache.set(cache_key, False)
return None
def _check_update_needed(db_name, options):
# Get current main module version
main_module = config.get('version', 'module', default='coog_core')
current_main_module_version = get_module_info(main_module)['version']
# Do the upgrade anyway if -u is activated
if options.update:
return True, current_main_module_version
# Get main module version which stocked in the database
version_control_table = Table('upgrade_version_control')
cursor = Transaction().connection.cursor()
cursor.execute(
*version_control_table.select(version_control_table.current_version))
db_main_module_version = cursor.fetchone()[0]
if (options.check_update
and current_main_module_version != db_main_module_version):
logger.warning(
f'Current code version ({current_main_module_version}) is '
'different from the last update version '
'({db_main_module_version}), updating')
return True, current_main_module_version
logger.warning(f'Current code version ({current_main_module_version}) '
'matches last update version, nothing to do')
return False, current_main_module_version
def get_cost(self, name):
pool = Pool()
Work = pool.get('production.work')
Cycle = pool.get('production.work.cycle')
table = self.__table__()
work = Work.__table__()
cycle = Cycle.__table__()
cursor = Transaction().connection.cursor()
cost = super(Production, self).get_cost(name)
cursor.execute(*table.join(work, 'LEFT',
condition=work.production == table.id
).join(cycle, 'LEFT', condition=cycle.work == work.id
).select(Sum(cycle.cost),
where=(cycle.state == 'done')
& (table.id == self.id)))
cycle_cost, = cursor.fetchone()
if cycle_cost is not None:
# SQLite uses float for SUM
if not isinstance(cycle_cost, Decimal):
cycle_cost = Decimal(cycle_cost)
cost += cycle_cost
digits = self.__class__.cost.digits
return cost.quantize(Decimal(str(10 ** -digits[1])))
def close(cls, periods):
pool = Pool()
Move = pool.get('stock.move')
Lot = pool.get('stock.lot')
Date = pool.get('ir.date')
Lang = pool.get('ir.lang')
cursor = Transaction().connection.cursor()
move = Move.__table__()
lot = Lot.__table__()
super(Period, cls).close(periods)
# Don't allow to close a period if all products at this date
# are not yet expired
recent_date = max(period.date for period in periods)
today = Date.today()
query = move.join(lot, 'INNER', condition=move.lot == lot.id).select(
lot.id,
where=(Greatest(move.effective_date, move.planned_date) <=
recent_date)
& (lot.shelf_life_expiration_date >= today))
cursor.execute(*query)
lot_id = cursor.fetchone()
if lot_id:
lot_id, = lot_id
lot = Lot(lot_id)
lang = Lang.get()
cls.raise_user_error(
'close_period_sled', {
'date': lang.strftime(lot.shelf_life_expiration_date),
'lot': lot.rec_name,
})
def __register__(cls, module_name):
TableHandler = backend.get('TableHandler')
cursor = Transaction().cursor
table = TableHandler(cursor, cls, module_name)
sql_table = cls.__table__()
# Migration from 2.2 new field currency
created_currency = table.column_exist('currency')
super(ProductSupplier, cls).__register__(module_name)
# Migration from 2.2 fill currency
if not created_currency:
Company = Pool().get('company.company')
company = Company.__table__()
limit = cursor.IN_MAX
cursor.execute(*sql_table.select(Count(sql_table.id)))
product_supplier_count, = cursor.fetchone()
for offset in range(0, product_supplier_count, limit):
cursor.execute(*sql_table.join(company,
condition=sql_table.company == company.id
).select(sql_table.id, company.currency,
order_by=sql_table.id,
limit=limit, offset=offset))
for product_supplier_id, currency_id in cursor.fetchall():
cursor.execute(*sql_table.update(
columns=[sql_table.currency],
values=[currency_id],
where=sql_table.id == product_supplier_id))
# Migration from 2.4: drop required on sequence
table.not_null_action('sequence', action='remove')
# Migration from 2.6: drop required on delivery_time
table.not_null_action('delivery_time', action='remove')
def count(self):
"Return the count of the Items"
from trytond.transaction import Transaction
# XXX: Ideal case should make a copy of Select query
#
# https://code.google.com/p/python-sql/issues/detail?id=22
query = self.query
query.columns = (Count(Distinct(self.primary_table.id)), )
cursor = Transaction().connection.cursor()
# temporarily remove order_by
order_by = query.order_by
query.order_by = None
try:
cursor.execute(*query)
finally:
# XXX: This can be removed when SQL queries can be copied
# See comment above
query.order_by = order_by
res = cursor.fetchone()
if res:
return res[0]
# There can be a case when query return None and then count
# will be zero
return 0
def _add_raw_column(self, column_name, column_type, default=None,
field_size=None, string=''):
if self.column_exist(column_name):
base_type = column_type[0].upper()
if base_type != self._columns[column_name]['typname']:
if (self._columns[column_name]['typname'], base_type) in [
('VARCHAR', 'TEXT'),
('TEXT', 'VARCHAR'),
('DATE', 'TIMESTAMP'),
('INTEGER', 'FLOAT'),
('INTEGER', 'NUMERIC'),
('FLOAT', 'NUMERIC'),
]:
self.alter_type(column_name, base_type)
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from %s to %s.',
column_name, self.table_name,
self._columns[column_name]['typname'], base_type)
if (base_type == 'VARCHAR'
and self._columns[column_name]['typname'] == 'VARCHAR'):
# Migrate size
from_size = self._columns[column_name]['size']
if field_size is None:
if from_size > 0:
self.alter_size(column_name, base_type)
elif from_size == field_size:
pass
elif from_size and from_size < field_size:
self.alter_size(column_name, column_type[1])
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from varchar(%s) to varchar(%s).',
column_name, self.table_name,
from_size if from_size and from_size > 0 else "",
field_size)
return
cursor = Transaction().connection.cursor()
column_type = column_type[1]
cursor.execute(('ALTER TABLE %s ADD COLUMN %s %s') % (
_escape_identifier(self.table_name),
_escape_identifier(column_name),
column_type))
if default:
# check if table is non-empty:
cursor.execute('SELECT 1 FROM %s limit 1'
% _escape_identifier(self.table_name))
if cursor.fetchone():
# Populate column with default values:
cursor.execute('UPDATE ' + _escape_identifier(self.table_name)
+ ' SET ' + _escape_identifier(column_name) + ' = ?',
(default(),))
self._update_definitions(columns=True)
def count_ip(cls):
cursor = Transaction().connection.cursor()
table = cls.__table__()
_, ip_network = cls.ipaddress()
cursor.execute(*table.select(Count(Literal('*')),
where=(table.ip_network == str(ip_network))
& (table.create_date >= cls.delay())))
return cursor.fetchone()[0]
def __register__(cls, module_name):
pool = Pool()
Move = pool.get('stock.move')
PurchaseLine = pool.get('purchase.line')
PurchaseRequest = pool.get('purchase.request')
SaleLine = pool.get('sale.line')
Location = pool.get('stock.location')
move = Move.__table__()
purchase_line = PurchaseLine.__table__()
purchase_request = PurchaseRequest.__table__()
sale_line = SaleLine.__table__()
location = Location.__table__()
cursor = Transaction().cursor
super(ShipmentDrop, cls).__register__(module_name)
# Migration from 3.6
cursor.execute(*location.select(Count(location.id),
where=(location.type == 'drop')))
has_drop_shipment, = cursor.fetchone()
if not has_drop_shipment:
drop_shipment = Location(name='Migration Drop Shipment',
type='drop', active=False)
drop_shipment.save()
drop_shipment_location = drop_shipment.id
move_sale_query = move.join(purchase_line,
condition=move.origin == Concat('purchase.line,',
purchase_line.id)
).join(purchase_request,
condition=purchase_request.purchase_line == purchase_line.id
).join(sale_line,
condition=sale_line.purchase_request == purchase_request.id
).select(
move.id, move.to_location, sale_line.id,
where=move.shipment.like('stock.shipment.drop,%'))
cursor.execute(*move_sale_query)
move_sales = cursor.fetchall()
for sub_move in grouped_slice(move_sales):
sub_ids = [s[0] for s in sub_move]
cursor.execute(*move.update(
columns=[move.to_location],
values=[drop_shipment_location],
where=move.id.in_(sub_ids)))
create_move = move.insert(values=move.select(
where=move.shipment.like('stock.shipment.drop,%')))
cursor.execute(*create_move)
for move_id, customer_location, line_id in move_sales:
cursor.execute(move.update(
columns=[move.origin, move.from_location,
move.to_location],
values=[Concat('sale.line,', str(line_id)),
drop_shipment_location, customer_location],
where=(move.id == move_id)))
def check_patient_current_pregnancy(self):
''' Check for only one current pregnancy in the patient '''
cursor = Transaction().cursor
cursor.execute(
"SELECT count(name) "
"FROM " + self._table + " \
WHERE (name = %s AND current_pregnancy)", (str(self.name.id), ))
if cursor.fetchone()[0] > 1:
self.raise_user_error('patient_already_pregnant')
def delete(cls, invoices):
cursor = Transaction().cursor
if invoices:
cursor.execute('SELECT id FROM purchase_invoices_rel '
'WHERE invoice IN (' + ','.join(('%s',) * len(invoices)) + ')',
[i.id for i in invoices])
if cursor.fetchone():
cls.raise_user_error('delete_purchase_invoice')
super(Invoice, cls).delete(invoices)
def check_patient_current_mv(self):
# Check for only one current mechanical ventilation on patient
cursor = Transaction().cursor
cursor.execute("SELECT count(name) "
"FROM " + self._table + " \
WHERE (name = %s AND current_mv)",
(str(self.name.id),))
if cursor.fetchone()[0] > 1:
self.raise_user_error('patient_already_on_mv')
def check_patient_admitted_at_icu(self):
# Verify that the patient is not at ICU already
cursor = Transaction().cursor
cursor.execute("SELECT count(name) "
"FROM " + self._table + " \
WHERE (name = %s AND admitted)",
(str(self.name.id),))
if cursor.fetchone()[0] > 1:
self.raise_user_error('patient_already_at_icu')
def check_patient_current_pregnancy(self):
''' Check for only one current pregnancy in the patient '''
cursor = Transaction().cursor
cursor.execute("SELECT count(name) "
"FROM " + self._table + " \
WHERE (name = %s AND current_pregnancy)",
(str(self.name.id),))
if cursor.fetchone()[0] > 1:
self.raise_user_error('patient_already_pregnant')
def check_patient_current_pregnancy(self):
''' Check for only one current pregnancy in the patient '''
cursor = Transaction().cursor
cursor.execute("SELECT count(name) "
"FROM " + self._table + " \
WHERE (name = %s AND current_pregnancy)",
(str(self.name.id),))
if cursor.fetchone()[0] > 1:
return False
return True
def check_patient_current_mv(self):
# Check for only one current mechanical ventilation on patient
cursor = Transaction().cursor
cursor.execute("SELECT count(name) "
"FROM " + self._table + " \
WHERE (name = %s AND current_mv)",
(str(self.name.id),))
if cursor.fetchone()[0] > 1:
return False
return True
def change_role(self):
table = Pool().get('condo.party').__table__()
with Transaction().new_cursor(readonly=True):
cursor = Transaction().connection.cursor()
cursor.execute(*table.select(table.role,
where=table.id == self.id))
role = cursor.fetchone()
if role and bool(role[0]):
self.raise_user_error(
"This role can not be change!")
def default_health_professional():
cursor = Transaction().cursor
User = Pool().get("res.user")
user = User(Transaction().user)
login_user_id = int(user.id)
cursor.execute(
"SELECT id FROM party_party WHERE is_doctor=True AND \
internal_user = %s LIMIT 1",
(login_user_id,),
)
partner_id = cursor.fetchone()
if partner_id:
cursor = Transaction().cursor
cursor.execute(
"SELECT id FROM gnuhealth_physician WHERE \
name = %s LIMIT 1",
(partner_id[0],),
)
doctor_id = cursor.fetchone()
return int(doctor_id[0])
def on_change_with_latlong(self, name=None):
obs = 'SELECT ST_AsLatLonText(ST_Centroid(ST_Transform(ST_GeomFromText(ST_AsText(geom),2154),4326))) ' \
'FROM shuriken_list_observation s WHERE s.id=%s' % (self.id)
print "obs: "+str(obs)
if len(obs) != 0:
cursor = Transaction().cursor
cursor.execute(obs)
try:
coord = cursor.fetchone()[0]
except:
coord = ''
return coord
def on_change_party(self):
cursor = Transaction().cursor
self.currency = self.default_currency()
if self.party:
table = self.__table__()
cursor.execute(*table.select(table.currency,
where=table.party == self.party.id,
group_by=table.currency,
order_by=Count(Literal(1)).desc))
row = cursor.fetchone()
if row:
self.currency, = row
def get_new_births(cls, start_date, end_date):
""" Return birth certificates within that period """
query = "SELECT COUNT(dob) \
FROM gnuhealth_birth_certificate \
WHERE dob BETWEEN \
%s AND %s"
cursor = Transaction().cursor
cursor.execute(query,(start_date, end_date))
res = cursor.fetchone()
return(res)
def get_new_deaths(cls, start_date, end_date):
""" Return death certificates within that period """
""" Truncate the timestamp of DoD to match a whole day"""
query = "SELECT COUNT(dod) \
FROM gnuhealth_death_certificate \
WHERE date_trunc('day', dod) BETWEEN \
%s AND %s"
cursor = Transaction().cursor
cursor.execute(query,(start_date, end_date))
res = cursor.fetchone()
return(res)
def on_change_with_car(self):
if self.party is None:
return None
else:
cursor = Transaction().cursor
cursor.execute(
'SELECT car '
'FROM company_employee '
'WHERE party=%s', (self.party.id,))
try:
res = int(cursor.fetchone()[0])
except:
res=None
return res
def validate(cls, badges):
cursor = Transaction().cursor
table = cls.__table__()
super(Badge, cls).validate(badges)
cursor.execute(*table.select(
table.code,
where=table.disabled == False,
group_by=table.code,
having=Count(table.id) > 1))
code = cursor.fetchone()
if code:
cls.raise_user_error('duplicate_code', {
'code': code,
})
def get_new_people(cls, start_date, end_date, in_health_system):
""" Return Total Number of new registered persons alive """
query = "SELECT COUNT(activation_date) \
FROM party_party \
WHERE activation_date BETWEEN \
%s AND %s and is_person=True and deceased is not TRUE"
if (in_health_system):
query = query + " and is_patient=True"
cursor = Transaction().cursor
cursor.execute(query,(start_date, end_date))
res = cursor.fetchone()
return(res)
def check_dates(self, ids):
cr = Transaction().cursor
for fiscalyear in self.browse(ids):
cr.execute('SELECT id ' \
'FROM ' + self._table + ' ' \
'WHERE ((start_date <= %s AND end_date >= %s) ' \
'OR (start_date <= %s AND end_date >= %s) ' \
'OR (start_date >= %s AND end_date <= %s)) ' \
'AND company = %s ' \
'AND id != %s',
(fiscalyear.start_date, fiscalyear.start_date,
fiscalyear.end_date, fiscalyear.end_date,
fiscalyear.start_date, fiscalyear.end_date,
fiscalyear.company.id, fiscalyear.id))
if cr.fetchone():
return False
return True
def __register__(cls, module_name):
pool = Pool()
ModelData = pool.get('ir.model.data')
cursor = Transaction().cursor
model_data = ModelData.__table__()
# Migration from 3.4: translation of the account chart
cursor.execute(*model_data.select(model_data.id,
where=((model_data.fs_id == 'be')
& (model_data.module == 'account_be'))))
if cursor.fetchone():
cursor.execute(*model_data.update(
columns=[model_data.fs_id],
values=[Concat(model_data.fs_id, '_fr')],
where=((Position('_fr', model_data.fs_id) == 0)
& (model_data.module == 'account_be'))))
super(AccountTemplate, cls).__register__(module_name)
def get_population(cls,date1,date2,gender,total):
""" Return Total Number of living people in the system
segmented by age group and gender"""
cursor = Transaction().cursor
if (total):
cursor.execute("SELECT COUNT(dob) \
FROM party_party WHERE gender = %s and deceased is not TRUE",(gender))
else:
cursor.execute("SELECT COUNT(dob) \
FROM party_party \
WHERE dob BETWEEN %s and %s AND \
gender = %s and deceased is not TRUE" ,(date2, date1, gender))
res = cursor.fetchone()[0]
return(res)