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)