def count(cls, login): cursor = Transaction().connection.cursor() table = cls.__table__() cursor.execute(*table.select(Count(Literal(1)), where=(table.login == login) & (table.create_date >= cls.delay()))) return cursor.fetchone()[0]
def _get_checkout_column(cls, checkout_table, name): column, where = None, None if name == 'checkedout_books': column = Count(checkout_table.id) where = checkout_table.return_date == Null elif name == 'late_checkedout_books': column = Count(checkout_table.id) where = (checkout_table.return_date == Null) & (checkout_table.date < datetime.date.today() + datetime.timedelta(days=20)) elif name == 'expected_return_date': column = Min(checkout_table.date) where = checkout_table.return_date == Null else: raise Exception('Invalid function field name %s' % name) return column, where
def __register__(cls, module_name): TableHandler = backend.get('TableHandler') super(ActionReport, cls).__register__(module_name) transaction = Transaction() cursor = transaction.connection.cursor() table = TableHandler(cls, module_name) action_report = cls.__table__() # Migration from 1.0 report_name_uniq has been removed table.drop_constraint('report_name_uniq') # Migration from 1.0 output_format (m2o) is now extension (selection) if table.column_exist('output_format'): outputformat = Table('ir_action_report_outputformat') cursor.execute(*action_report.join( outputformat, condition=action_report.output_format == outputformat.id ).select(action_report.id, where=outputformat.format == 'pdf')) ids = [x[0] for x in cursor.fetchall()] cls.write(cls.browse(ids), {'extension': 'pdf'}) ids = cls.search([('id', 'not in', ids)]) cls.write(cls.browse(ids), {'extension': 'odt'}) table.drop_column("output_format") TableHandler.dropTable('ir.action.report.outputformat', 'ir_action_report_outputformat') # Migrate from 2.0 remove required on extension table.not_null_action('extension', action='remove') cursor.execute( *action_report.update([action_report.extension], [''], where=action_report.extension == 'odt')) # Migration from 2.0 report_content_data renamed into # report_content_custom to remove base64 encoding if (table.column_exist('report_content_data') and table.column_exist('report_content_custom')): limit = transaction.database.IN_MAX cursor.execute(*action_report.select(Count(action_report.id))) report_count, = cursor.fetchone() for offset in range(0, report_count, limit): cursor.execute( *action_report.select(action_report.id, action_report.report_content_data, order_by=action_report.id, limit=limit, offset=offset)) for report_id, report in cursor.fetchall(): if report: report = fields.Binary.cast( base64.decodestring(bytes(report))) cursor.execute(*action_report.update( [action_report.report_content_custom], [report], where=action_report.id == report_id)) table.drop_column('report_content_data') # Migration from 3.4 remove report_name_module_uniq constraint table.drop_constraint('report_name_module_uniq')
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').__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 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 table_query(cls): Opportunity = Pool().get('sale.opportunity') opportunity = Opportunity.__table__() return opportunity.select( Max(opportunity.create_uid).as_('create_uid'), Max(opportunity.create_date).as_('create_date'), Max(opportunity.write_uid).as_('write_uid'), Max(opportunity.write_date).as_('write_date'), opportunity.company, Count(Literal(1)).as_('number'), Sum( Case((opportunity.state.in_( cls._converted_state()), Literal(1)), else_=Literal(0))).as_('converted'), Sum( Case((opportunity.state.in_(cls._won_state()), Literal(1)), else_=Literal(0))).as_('won'), Sum( Case((opportunity.state.in_(cls._lost_state()), Literal(1)), else_=Literal(0))).as_('lost'), Sum(opportunity.amount).as_('amount'), Sum( Case((opportunity.state.in_( cls._converted_state()), opportunity.amount), else_=Literal(0))).as_('converted_amount'), Sum( Case((opportunity.state.in_( cls._won_state()), opportunity.amount), else_=Literal(0))).as_('won_amount'))
def get_active(cls, numbers, name): pool = Pool() Coupon = pool.get('sale.promotion.coupon') Sale = pool.get('sale.sale') Sale_Number = pool.get('sale.sale-sale.promotion.coupon.number') table = cls.__table__() coupon = Coupon.__table__() sale = Sale.__table__() sale_number = Sale_Number.__table__() context = Transaction().context cursor = Transaction().connection.cursor() party = context.get('party') query = (table.join(sale_number, 'LEFT', condition=table.id == sale_number.number).join( coupon, condition=table.coupon == coupon.id)) if party: query = query.join(sale, 'LEFT', condition=(sale_number.sale == sale.id) & (sale.party == party)) active = Case( ((coupon.number_of_use > 0) & (coupon.per_party), Count(sale.id) < coupon.number_of_use), ((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) else: active = Case(((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) query = query.select( table.id, active, group_by=[table.id, coupon.number_of_use, coupon.per_party]) result = {} for sub_numbers in grouped_slice(numbers): query.where = reduce_ids(table.id, map(int, sub_numbers)) cursor.execute(*query) result.update(dict(cursor.fetchall())) return result
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 search_active(cls, name, clause): pool = Pool() Coupon = pool.get('sale.promotion.coupon') Sale = pool.get('sale.sale') Sale_Number = pool.get('sale.sale-sale.promotion.coupon.number') table = cls.__table__() coupon = Coupon.__table__() sale = Sale.__table__() sale_number = Sale_Number.__table__() context = Transaction().context party = context.get('party') _, operator, value = clause Operator = fields.SQL_OPERATORS[operator] query = (table .join(sale_number, 'LEFT', condition=table.id == sale_number.number) .join(coupon, condition=table.coupon == coupon.id)) if party: query = query.join(sale, 'LEFT', condition=(sale_number.sale == sale.id) & (sale.party == party)) active = Case( ((coupon.number_of_use > 0) & (coupon.per_party), Count(sale.id) < coupon.number_of_use), ((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) else: active = Case( ((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) query = query.select(table.id, group_by=[table.id, coupon.number_of_use, coupon.per_party], having=Operator(active, value)) return [('id', 'in', query)]
def on_change_party(self): cursor = Transaction().connection.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 __register__(cls, module_name): TableHandler = backend.get('TableHandler') transaction = Transaction() cursor = transaction.connection.cursor() table = TableHandler(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 = transaction.database.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') # Migration from 3.8: change delivery_time inte timedelta lead_time if table.column_exist('delivery_time'): cursor.execute( *sql_table.select(sql_table.id, sql_table.delivery_time)) for id_, delivery_time in cursor.fetchall(): if delivery_time is None: continue lead_time = datetime.timedelta(days=delivery_time) cursor.execute( *sql_table.update([sql_table.lead_time], [lead_time], where=sql_table.id == id_)) table.drop_column('delivery_time')
def _active_query(cls): pool = Pool() Coupon = pool.get('sale.promotion.coupon') Sale = pool.get('sale.sale') Sale_Number = pool.get('sale.sale-sale.promotion.coupon.number') table = cls.__table__() coupon = Coupon.__table__() sale = Sale.__table__() sale_number = Sale_Number.__table__() context = Transaction().context party = context.get('party') query = (table.join(sale_number, 'LEFT', condition=table.id == sale_number.number).join( coupon, condition=table.coupon == coupon.id)) if party: query = query.join(sale, 'LEFT', condition=(sale_number.sale == sale.id) & (sale.party == party)) active = Case( ((coupon.number_of_use > 0) & (coupon.per_party), Count(sale.id) < coupon.number_of_use), ((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) else: active = Case(((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) query = query.select( group_by=[table.id, coupon.number_of_use, coupon.per_party]) return query, table, active
def getter_number_of_books(cls, editors, name): result = {x.id: 0 for x in editors} Book = Pool().get('library.book') book = Book.__table__() cursor = Transaction().connection.cursor() cursor.execute( *book.select(book.editor, Count(book.id), where=book.editor.in_([x.id for x in editors]), group_by=[book.editor])) for editor_id, count in cursor.fetchall(): result[editor_id] = count return result
def getter_number_of_exemplaries(cls, books, name): result = {x.id: 0 for x in books} Exemplary = Pool().get('library.book.exemplary') exemplary = Exemplary.__table__() cursor = Transaction().connection.cursor() cursor.execute( *exemplary.select(exemplary.book, Count(exemplary.id), where=exemplary.book.in_([x.id for x in books]), group_by=[exemplary.book])) for book_id, count in cursor.fetchall(): result[book_id] = count return result
def get_context(cls, records, data): pool = Pool() Move = pool.get('account.move') Line = pool.get('account.move.line') TaxLine = pool.get('account.tax.line') Tax = pool.get('account.tax') context = super().get_context(records, data) cursor = Transaction().connection.cursor() move = Move.__table__() move_line = Line.__table__() tax_line = TaxLine.__table__() tables = { None: (move, None), 'lines': { None: (move_line, move_line.move == move.id), 'tax_lines': { None: (tax_line, tax_line.move_line == move_line.id), }, }, } expression, tables = cls.aeat_party_expression(tables) parties = defaultdict(int) for tax_code in cls.tax_codes(): domain = ['OR'] for line in tax_code.lines: domain.append(line._line_domain) with Transaction().set_context(periods=data['periods']): tax_line_domain = [Tax._amount_domain(), domain] _, where = Move.search_domain([ ('lines', 'where', [ ('tax_lines', 'where', tax_line_domain), ]), ], tables=tables) from_ = convert_from(None, tables) cursor.execute( *from_.select(expression, where=where, group_by=( expression, )).select(Count(Literal('*')))) row = cursor.fetchone() if row: parties[tax_code.code] += row[0] context['parties'] = parties return context
def get_count(cls, shortened_urls, name): pool = Pool() URLAccess = pool.get('web.shortened_url.access') access = URLAccess.__table__() cursor = Transaction().connection.cursor() counts = {s.id: 0 for s in shortened_urls} for sub_ids in grouped_slice(shortened_urls): cursor.execute( *access.select(access.url, Count(access.id), where=reduce_ids(access.url, sub_ids), group_by=[access.url])) counts.update(cursor) return counts
def _columns(cls, tables, withs): line = tables['line'] currency_company = withs['currency_company'] currency_sale = withs['currency_sale'] revenue = cls.revenue.sql_cast( Sum(line.quantity * line.unit_price * currency_company.rate / currency_sale.rate)) return [ cls._column_id(tables, withs).as_('id'), Literal(0).as_('create_uid'), CurrentTimestamp().as_('create_date'), cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'), cls.write_date.sql_cast(Literal(Null)).as_('write_date'), line.company.as_('company'), revenue.as_('revenue'), Count(line.order, distinct=True).as_('number'), ]
def table_query(): pool = Pool() Evaluation = pool.get('gnuhealth.patient.evaluation') evaluation = Evaluation.__table__() where = evaluation.specialty != 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'] return evaluation.select( evaluation.specialty.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.specialty, Count(evaluation.specialty).as_('evaluations'), where=where, group_by=evaluation.specialty)
def migrate_modules(cursor): modules_in_dir = get_module_list() modules_to_migrate = {} for module_dir in modules_in_dir: try: with tools.file_open(OPJ(module_dir, '__migrated_modules')) as f: for line in f.readlines(): line = line.replace(' ', '').strip('\n') if not line: continue action, old_module = line.split(':') modules_to_migrate[old_module] = (action, module_dir) except IOError: continue cursor.execute(*ir_module.select(ir_module.name)) for module_in_db, in cursor.fetchall(): if (module_in_db in modules_in_dir or module_in_db in modules_to_migrate): continue else: modules_to_migrate[module_in_db] = ('to_drop', None) # PKUNK 9502 add logs and control before uninstall modules if (not AUTO_UNINSTALL): dropped = False for module in modules_to_migrate: if modules_to_migrate[module][0] == 'to_drop': logger.critical( 'To uninstall %s you should set' ' COOG_AUTO_UNINSTALL environnement variable' % module) dropped = True if dropped: sys.exit(1) else: for module in modules_to_migrate: if modules_to_migrate[module][0] == 'to_drop': logger.warning('%s is about to be uninstalled' % (module)) # PKUNK 9502 end def rename(cursor, table_name, old_name, new_name, var_name): table = Table(table_name) fields = None # If the view already exists in destination module if table_name == 'ir_model_data': fields = ['fs_id', 'model'] if table_name == 'ir_ui_view': fields = ['model', 'name'] if fields: query = ('DELETE from %(table)s where ' '(%(fields)s) in (' 'SELECT %(fields)s FROM %(table)s WHERE ' '"module" IN (\'%(old_name)s\', \'%(new_name)s\') ' 'GROUP BY %(fields)s ' 'HAVING COUNT("module") > 1) ' 'and "module" = \'%(old_name)s\';' % { 'table': table_name, 'old_name': old_name, 'new_name': new_name, 'fields': (', '.join('"' + f + '"' for f in fields)) }) cursor.execute(query) query = table.update([getattr(table, var_name)], [new_name], where=(getattr(table, var_name) == old_name)) cursor.execute(*query) def delete(cursor, table_name, old_name, var_name): table = Table(table_name) cursor.execute(*table.delete( where=(getattr(table, var_name) == old_name))) for old_name, (action, new_name) in modules_to_migrate.items(): cursor.execute(*ir_module.select(Count(ir_module.id), where=ir_module.name == old_name)) count, = cursor.fetchone() if not count: continue if action == 'to_drop': logger.info('%s directory has been removed from filesystem,' ' deleting entries from database...' % old_name) else: logger.info('%s has been %s %s, updating database...' % (old_name, { 'to_rename': 'renamed into', 'to_merge': 'merged with' }[action], new_name)) if new_name: rename(cursor, 'ir_model', old_name, new_name, 'module') rename(cursor, 'ir_action_report', old_name, new_name, 'module') rename(cursor, 'ir_model_field', old_name, new_name, 'module') rename(cursor, 'ir_model_data', old_name, new_name, 'module') rename(cursor, 'ir_translation', old_name, new_name, 'module') rename(cursor, 'ir_translation', old_name, new_name, 'overriding_module') rename(cursor, 'ir_ui_icon', old_name, new_name, 'module') rename(cursor, 'ir_ui_view', old_name, new_name, 'module') if action == 'to_rename': rename(cursor, 'ir_module_dependency', old_name, new_name, 'name') rename(cursor, 'ir_module', old_name, new_name, 'name') elif action == 'to_merge': delete(cursor, 'ir_module_dependency', old_name, 'name') delete(cursor, 'ir_module', old_name, 'name') elif action == 'to_drop': delete(cursor, 'ir_model', old_name, 'module') delete(cursor, 'ir_action_report', old_name, 'module') delete(cursor, 'ir_model_field', old_name, 'module') delete(cursor, 'ir_model_data', old_name, 'module') delete(cursor, 'ir_translation', old_name, 'module') delete(cursor, 'ir_translation', old_name, 'overriding_module') delete(cursor, 'ir_ui_icon', old_name, 'module') delete(cursor, 'ir_ui_view', old_name, 'module') delete(cursor, 'ir_module_dependency', old_name, 'name') delete(cursor, 'ir_module', old_name, 'name')
def test_filter_case_count_star(self): count = Count(Literal('*'), filter_=self.table.a > 0) self.assertEqual(str(count), 'COUNT(CASE WHEN ("a" > %s) THEN %s END)') self.assertEqual(count.params, (0, 1))
def trigger_action(self, ids): """ Trigger the action define on trigger for the records """ pool = Pool() TriggerLog = pool.get('ir.trigger.log') Model = pool.get(self.model.model) model, method = self.action.split('|') ActionModel = pool.get(model) cursor = Transaction().connection.cursor() trigger_log = TriggerLog.__table__() ids = [r.id for r in Model.browse(ids) if self.eval(r)] # Filter on limit_number if self.limit_number: new_ids = [] for sub_ids in grouped_slice(ids): sub_ids = list(sub_ids) red_sql = reduce_ids(trigger_log.record_id, sub_ids) cursor.execute(*trigger_log.select( trigger_log.record_id, Count(Literal(1)), where=red_sql & (trigger_log.trigger == self.id), group_by=trigger_log.record_id)) number = dict(cursor) for record_id in sub_ids: if record_id not in number: new_ids.append(record_id) continue if number[record_id] < self.limit_number: new_ids.append(record_id) ids = new_ids def cast_datetime(value): datepart, timepart = value.split(" ") year, month, day = map(int, datepart.split("-")) timepart_full = timepart.split(".") hours, minutes, seconds = map( int, timepart_full[0].split(":")) if len(timepart_full) == 2: microseconds = int(timepart_full[1]) else: microseconds = 0 return datetime.datetime( year, month, day, hours, minutes, seconds, microseconds) # Filter on minimum_time_delay if self.minimum_time_delay: new_ids = [] # Use now from the transaction to compare with create_date timestamp_cast = self.__class__.create_date.sql_cast cursor.execute(*Select([timestamp_cast(CurrentTimestamp())])) now, = cursor.fetchone() if isinstance(now, str): now = cast_datetime(now) for sub_ids in grouped_slice(ids): sub_ids = list(sub_ids) red_sql = reduce_ids(trigger_log.record_id, sub_ids) cursor.execute(*trigger_log.select( trigger_log.record_id, Max(trigger_log.create_date), where=(red_sql & (trigger_log.trigger == self.id)), group_by=trigger_log.record_id)) delay = dict(cursor) for record_id in sub_ids: if record_id not in delay: new_ids.append(record_id) continue # SQLite return string for MAX if isinstance(delay[record_id], str): delay[record_id] = cast_datetime(delay[record_id]) if now - delay[record_id] >= self.minimum_time_delay: new_ids.append(record_id) ids = new_ids records = Model.browse(ids) if records: getattr(ActionModel, method)(records, self) if self.limit_number or self.minimum_time_delay: to_create = [] for record in records: to_create.append({ 'trigger': self.id, 'record_id': record.id, }) if to_create: TriggerLog.create(to_create)
def trigger_action(cls, records, trigger): """ Trigger the action define on trigger for the records """ pool = Pool() TriggerLog = pool.get('ir.trigger.log') Model = pool.get(trigger.model.model) ActionModel = pool.get(trigger.action_model.model) cursor = Transaction().connection.cursor() trigger_log = TriggerLog.__table__() ids = map(int, records) # Filter on limit_number if trigger.limit_number: new_ids = [] for sub_ids in grouped_slice(ids): sub_ids = list(sub_ids) red_sql = reduce_ids(trigger_log.record_id, sub_ids) cursor.execute( *trigger_log.select(trigger_log.record_id, Count(Literal(1)), where=red_sql & (trigger_log.trigger == trigger.id), group_by=trigger_log.record_id)) number = dict(cursor.fetchall()) for record_id in sub_ids: if record_id not in number: new_ids.append(record_id) continue if number[record_id] < trigger.limit_number: new_ids.append(record_id) ids = new_ids # Filter on minimum_time_delay if trigger.minimum_time_delay: new_ids = [] for sub_ids in grouped_slice(ids): sub_ids = list(sub_ids) red_sql = reduce_ids(trigger_log.record_id, sub_ids) cursor.execute(*trigger_log.select( trigger_log.record_id, Max(trigger_log.create_date), where=(red_sql & (trigger_log.trigger == trigger.id)), group_by=trigger_log.record_id)) delay = dict(cursor.fetchall()) for record_id in sub_ids: if record_id not in delay: new_ids.append(record_id) continue # SQLite return string for MAX if isinstance(delay[record_id], basestring): datepart, timepart = delay[record_id].split(" ") year, month, day = map(int, datepart.split("-")) timepart_full = timepart.split(".") hours, minutes, seconds = map( int, timepart_full[0].split(":")) if len(timepart_full) == 2: microseconds = int(timepart_full[1]) else: microseconds = 0 delay[record_id] = datetime.datetime( year, month, day, hours, minutes, seconds, microseconds) if (datetime.datetime.now() - delay[record_id] >= trigger.minimum_time_delay): new_ids.append(record_id) ids = new_ids records = Model.browse(ids) if records: getattr(ActionModel, trigger.action_function)(records, trigger) if trigger.limit_number or trigger.minimum_time_delay: to_create = [] for record in records: to_create.append({ 'trigger': trigger.id, 'record_id': record.id, }) if to_create: TriggerLog.create(to_create)
def migrate_modules(cursor): modules_in_dir = get_module_list() modules_to_migrate = {} for module_dir in modules_in_dir: try: with tools.file_open(OPJ(module_dir, '__migrated_modules')) as f: for line in f.readlines(): line = line.replace(' ', '').strip('\n') if not line: continue action, old_module = line.split(':') modules_to_migrate[old_module] = (action, module_dir) except IOError: continue cursor.execute(*ir_module.select(ir_module.name)) for module_in_db, in cursor.fetchall(): if (module_in_db in modules_in_dir or module_in_db in modules_to_migrate): continue else: modules_to_migrate[module_in_db] = ('to_drop', None) def rename(cursor, table_name, old_name, new_name, var_name): table = Table(table_name) cursor.execute( *table.update([getattr(table, var_name)], [new_name], where=(getattr(table, var_name) == old_name))) def delete(cursor, table_name, old_name, var_name): table = Table(table_name) cursor.execute(*table.delete( where=(getattr(table, var_name) == old_name))) for old_name, (action, new_name) in modules_to_migrate.iteritems(): cursor.execute(*ir_module.select(Count(ir_module.id), where=ir_module.name == old_name)) count, = cursor.fetchone() if not count: continue if action == 'to_drop': logger.info('%s directory has been removed from filesystem,' ' deleting entries from database...' % old_name) else: logger.info('%s has been %s %s, updating database...' % (old_name, { 'to_rename': 'renamed into', 'to_merge': 'merged with' }[action], new_name)) if new_name: rename(cursor, 'ir_model', old_name, new_name, 'module') rename(cursor, 'ir_action_report', old_name, new_name, 'module') rename(cursor, 'ir_model_field', old_name, new_name, 'module') rename(cursor, 'ir_model_data', old_name, new_name, 'module') rename(cursor, 'ir_translation', old_name, new_name, 'module') rename(cursor, 'ir_translation', old_name, new_name, 'overriding_module') rename(cursor, 'ir_ui_icon', old_name, new_name, 'module') rename(cursor, 'ir_ui_view', old_name, new_name, 'module') if action == 'to_rename': rename(cursor, 'ir_module_dependency', old_name, new_name, 'name') rename(cursor, 'ir_module', old_name, new_name, 'name') elif action == 'to_merge': delete(cursor, 'ir_module_dependency', old_name, 'name') delete(cursor, 'ir_module', old_name, 'name') elif action == 'to_drop': delete(cursor, 'ir_model', old_name, 'module') delete(cursor, 'ir_action_report', old_name, 'module') delete(cursor, 'ir_model_field', old_name, 'module') delete(cursor, 'ir_model_data', old_name, 'module') delete(cursor, 'ir_translation', old_name, 'module') delete(cursor, 'ir_translation', old_name, 'overriding_module') delete(cursor, 'ir_ui_icon', old_name, 'module') delete(cursor, 'ir_ui_view', old_name, 'module') delete(cursor, 'ir_module_dependency', old_name, 'name') delete(cursor, 'ir_module', old_name, 'name')
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') table = cls.__table__() move = Move.__table__() purchase_line = PurchaseLine.__table__() purchase_request = PurchaseRequest.__table__() sale_line = SaleLine.__table__() location = Location.__table__() cursor = Transaction().connection.cursor() table_h = cls.__table_handler__(module_name) # Migration from 5.8: rename code into number if table_h.column_exist('code'): table_h.column_rename('code', 'number') 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))) cursor.execute(*move.select(limit=1)) moves = list(cursor_dict(cursor)) if moves: move_columns = moves[0].keys() columns = [Column(move, c) for c in move_columns if c != 'id'] create_move = move.insert( columns=columns, values=move.select( *columns, 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))) # Migration from 5.6: rename state cancel to cancelled cursor.execute(*table.update([table.state], ['cancelled'], where=table.state == 'cancel'))
def search(cls, domain, offset=0, limit=None, order=None, count=False, query=False): cursor = Transaction().connection.cursor() # Clean transaction cache for cache in Transaction().cache.values(): if cls.__name__ in cache: del cache[cls.__name__] if not cls.get_table(): return super().search(domain, offset, limit, order, count, query) # Get domain clauses sql_table = cls.get_sql_table() tables, expression = cls.search_domain(domain, tables={None: (sql_table, None)}) # Get order by order_by = [] order_types = { 'DESC': Desc, 'ASC': Asc, } null_ordering_types = { 'NULLS FIRST': NullsFirst, 'NULLS LAST': NullsLast, None: lambda _: _ } if order is None or order is False: order = cls._order for oexpr, otype in order: fname, _, extra_expr = oexpr.partition('.') field = cls._fields[fname] if not otype: otype, null_ordering = 'ASC', None else: otype = otype.upper() try: otype, null_ordering = otype.split(' ', 1) except ValueError: null_ordering = None Order = order_types[otype] NullOrdering = null_ordering_types[null_ordering] forder = field.convert_order(oexpr, tables, cls) order_by.extend((NullOrdering(Order(o)) for o in forder)) main_table, _ = tables[None] table = convert_from(None, tables) if count: cursor.execute(*table.select(Count(Literal('*')), where=expression, limit=limit, offset=offset)) return cursor.fetchone()[0] columns = [main_table.id] select = table.select(*columns, where=expression, order_by=order_by, limit=limit, offset=offset) if query: return select cursor.execute(*select) res = [x[0] for x in cursor.fetchall()] return cls.browse(res)