def table_query(self=None): price_profit_loss = Pool().get('hrp_report.price_profit_loss_content') ProfitLoss = price_profit_loss.__table__() where = Literal(True) condition = [] if Transaction().context.get('location') != None: condition.append(('location', '=', Transaction().context.get('location'))) if Transaction().context.get('start_time') != None: condition.append(('effective_date', '>=', Transaction().context.get('start_time'))) if Transaction().context.get('end_time') != None: condition.append(('effective_date', '<=', Transaction().context.get('end_time'))) if Transaction().context.get('drug_type') != None: if Transaction().context.get('drug_type') == '06': pass else: condition.append(('drug_type', '=', Transaction().context.get('drug_type'))) product_ids = price_profit_loss.search([condition], query=True, order=[]) where &= ProfitLoss.id.in_(product_ids) Result = ProfitLoss.select( ProfitLoss.id.as_('id'), Max(ProfitLoss.create_uid).as_('create_uid'), Max(ProfitLoss.create_date).as_('create_date'), Max(ProfitLoss.write_uid).as_('write_uid'), Max(ProfitLoss.write_date).as_('write_date'), ProfitLoss.uom, where=where, group_by=ProfitLoss.id) return Result
def table_query(): pool = Pool() inventory_two = pool.get('hrp_inventory.inventory_two').__table__() inventory_lines = pool.get( 'hrp_inventory.inventory_two_lines').__table__() join1 = Join(inventory_lines, inventory_two) join1.condition = join1.right.id == inventory_lines.inventory where = Literal(True) if Transaction().context.get('inventor_time'): where &= inventory_two.id == Transaction().context['inventor_time'] where &= inventory_lines.category == Transaction( ).context['category'] where &= inventory_lines.type != 'balance' Result = join1.select( join1.left.id.as_('id'), Max(inventory_lines.create_uid).as_('create_uid'), Max(inventory_lines.create_date).as_('create_date'), Max(inventory_lines.write_uid).as_('write_uid'), Max(inventory_lines.write_date).as_('write_date'), inventory_lines.code, inventory_lines.uom, inventory_lines.name, inventory_lines.product, inventory_lines.drug_specifications, inventory_lines.cost_pice, inventory_lines.type, inventory_lines.differences_why, where=where, group_by=inventory_lines.id) return Result
def table_query(): pool = Pool() Evaluation = pool.get('gnuhealth.patient.evaluation') 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 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 table_query(cls): pool = Pool() Property = pool.get('ir.property') Field = pool.get('ir.model.field') property_history = Property.__table_history__() field = Field.__table__() return property_history.join( field, condition=field.id == property_history.field).select( Max(Column(property_history, '__id')).as_('id'), Max(property_history.create_uid).as_('create_uid'), Max(property_history.create_date).as_('create_date'), Max(property_history.write_uid).as_('write_uid'), Max(property_history.write_date).as_('write_date'), Coalesce(property_history.write_date, property_history.create_date).as_('date'), Trim(Substring(property_history.res, ',.*'), 'LEADING', ',').cast(cls.template.sql_type().base).as_('template'), Trim(property_history.value, 'LEADING', ',').cast( cls.cost_price.sql_type().base).as_('cost_price'), where=(field.name == 'cost_price') & property_history.res.like('product.template,%'), group_by=(property_history.id, Coalesce(property_history.write_date, property_history.create_date), property_history.res, property_history.value))
def table_query(): pool = Pool() context = Transaction().context Gp = pool.get('disc.gp') gp = Gp.__table__() Reporte = pool.get('disc.reporte') reporte = Reporte.__table__() ReporteLinea = pool.get('disc.reporte.linea') reporte_linea = ReporteLinea.__table__() where = Literal(True) if context.get('fecha_inicio'): where &= reporte.fecha_inicio >= context['fecha_inicio'] # print "FECHA INICIO: " + str(context['fecha_inicio']) if context.get('fecha_fin'): where &= reporte.fecha_fin <= context['fecha_fin'] # print "FECHA FIN: " + str(context['fecha_fin']) #if context.get('distrito'): # where &= reporte.distrito == context['distrito'] # print "DISTRITO: " + str(context['distrito']) #print "WHERE: " + str(where) subquery = (reporte_linea .join(reporte, condition=reporte_linea.reporte == reporte.id) .select( Max(reporte_linea.id * 1005).as_('id'), Max(reporte_linea.create_uid).as_('create_uid'), Max(reporte_linea.create_date).as_('create_date'), Max(reporte_linea.write_uid).as_('write_uid'), Max(reporte_linea.write_date).as_('write_date'), (Sum(reporte_linea.cantidad)).as_('total'), (reporte_linea.gp).as_('gp'), where = where, group_by=(reporte_linea.gp), having= Sum(reporte_linea.cantidad)>0, order_by=(reporte_linea.gp), ) ) query = (gp .join(subquery,'LEFT', condition= gp.id == subquery.gp) .select( Max(gp.id * 1002).as_('id'), Max(gp.create_uid).as_('create_uid'), Max(gp.create_date).as_('create_date'), Max(gp.write_uid).as_('write_uid'), Max(gp.write_date).as_('write_date'), (Sum(subquery.total)).as_('total'), (gp.id).as_('gp'), where= subquery.gp == None, #or subquery.total is None, #having= Sum(subquery.total)<1, group_by=(gp.id), ) ) #print "QUERY: " + str(query) return query
def _grouped_columns(cls, line): return [ Max(line.statement).as_('statement'), Max(line.number).as_('number'), Max(line.date).as_('date'), Sum(line.amount).as_('amount'), Max(line.party).as_('party'), ]
def get_lastmodified(cls, uri, cache=None): pool = Pool() Party = pool.get('party.party') Address = pool.get('party.address') ContactMechanism = pool.get('party.contact_mechanism') party = Party.__table__() address = Address.__table__() contact_mechanism = ContactMechanism.__table__() cursor = Transaction().cursor party_id = cls.vcard(uri) if party_id: if cache is not None: cache.setdefault('_contact', {}) ids = cache['_contact'].keys() if party_id not in ids: ids.append(party_id) elif 'lastmodified' in cache['_contact'][party_id]: return cache['_contact'][party_id]['lastmodified'] else: ids = [party_id] res = None for sub_ids in grouped_slice(ids): red_sql = reduce_ids(party.id, sub_ids) cursor.execute(*party.join( address, 'LEFT', condition=party.id == address.party).join( contact_mechanism, 'LEFT', condition=party.id == contact_mechanism.party).select( party.id, Max( Extract( 'EPOCH', Coalesce(party.write_date, party.create_date))), Max( Extract( 'EPOCH', Coalesce(address.write_date, address.create_date))), Max( Extract( 'EPOCH', Coalesce(contact_mechanism.write_date, contact_mechanism.create_date))), where=red_sql, group_by=party.id)) for party_id2, date_p, date_a, date_c in cursor.fetchall(): date = max(date_p, date_a, date_c) if party_id2 == party_id: res = date if cache is not None: cache['_contact'].setdefault(party_id2, {}) cache['_contact'][party_id2]['lastmodified'] = date if res is not None: return res return super(Collection, cls).get_lastmodified(uri, cache=cache)
def table_query(cls): pool = Pool() Invoice = pool.get('account.invoice') InvoiceTax = pool.get('account.invoice.tax') Tax = pool.get('account.tax') Date = pool.get('ir.date') context = Transaction().context invoice = Invoice.__table__() invoice_tax = InvoiceTax.__table__() tax = Tax.__table__() amount = invoice_tax.base + invoice_tax.amount month = Extract('MONTH', invoice.invoice_date) where = ((invoice.company == context.get('company')) & (invoice.state.in_(['posted', 'paid'])) & (tax.es_vat_list_code != Null) & (Extract('year', invoice.invoice_date) == context.get( 'date', Date.today()).year)) return (invoice_tax.join( invoice, condition=invoice_tax.invoice == invoice.id ).join(tax, condition=invoice_tax.tax == tax.id).select( Max(invoice_tax.id).as_('id'), Literal(0).as_('create_uid'), Min(invoice_tax.create_date).as_('create_date'), Literal(0).as_('write_uid'), Max(invoice_tax.write_date).as_('write_date'), invoice.tax_identifier.as_('company_tax_identifier'), invoice.party.as_('party'), invoice.party_tax_identifier.as_('party_tax_identifier'), tax.es_vat_list_code.as_('code'), Sum(amount).as_('amount'), Sum(amount, filter_=month <= Literal(3)).as_('first_period_amount'), Sum(amount, filter_=((month > Literal(3)) & (month <= Literal(6)))).as_('second_period_amount'), Sum(amount, filter_=((month > Literal(6)) & (month <= Literal(9)))).as_('third_period_amount'), Sum(amount, filter_=((month > Literal(9)) & (month <= Literal(12)))).as_('fourth_period_amount'), invoice.currency.as_('currency'), where=where, group_by=[ invoice.tax_identifier, invoice.type, invoice.party, invoice.party_tax_identifier, invoice.currency, tax.es_vat_list_code, ]))
def table_query(): pool = Pool() Date = pool.get('ir.date') Location = Pool().get('stock.location') Product = pool.get('product.product') Template = pool.get('product.template') AvailableMedicineLine = pool.get( "hrp_inventory.available_medicine_line") template = pool.get('product.template').__table__() products = pool.get('product.product').__table__() available_medicine_line = pool.get( 'hrp_inventory.available_medicine_line').__table__() where = Literal(True) ids = [] if Transaction().context.get('location') and Transaction().context.get( 'category'): with Transaction().set_context(stock_date_end=Date.today(), stock_assign=True): pbl = Product.products_by_location([ Transaction().context['location'], Location( Transaction().context['location']).freeze_location.id ], with_childs=True) for key, value in pbl.items(): if value > 0 and key[1] is not None: if Product(key[1]).template.categories[ 0].id == Transaction().context['category']: ids.append(key[1]) set(ids) where &= products.id.in_(ids) if not ids: where = Literal(False) if Transaction().context.get('shelves_code'): ids2 = [] lines = AvailableMedicineLine.search([ ('product', 'in', ids), ('warehouse', '=', Transaction().context['location']) ]) for i in lines: ids2.append(i) jiao = [val for val in ids2 if val in ids] where &= products.id.in_(jiao) Result = products.select(products.id.as_('id'), Max(products.create_uid).as_('create_uid'), Max(products.create_date).as_('create_date'), Max(products.write_uid).as_('write_uid'), Max(products.write_date).as_('write_date'), where=where, group_by=products.id) return Result
def commit(cls, transaction): table = Table(cls._table) reset = cls._reset.pop(transaction, None) if not reset: return database = transaction.database dbname = database.name if not _clear_timeout and transaction.database.has_channel(): with transaction.connection.cursor() as cursor: # The count computed as # 8000 (max notify size) / 64 (max name data len) for sub_reset in grouped_slice(reset, 125): cursor.execute( 'NOTIFY "%s", %%s' % cls._channel, (json.dumps(list(sub_reset), separators=(',', ':')),)) else: connection = database.get_connection( readonly=False, autocommit=True) try: with connection.cursor() as cursor: for name in reset: cursor.execute(*table.select(table.name, table.id, table.timestamp, where=table.name == name, limit=1)) if cursor.fetchone(): # It would be better to insert only cursor.execute(*table.update([table.timestamp], [CurrentTimestamp()], where=table.name == name)) else: cursor.execute(*table.insert( [table.timestamp, table.name], [[CurrentTimestamp(), name]])) cursor.execute(*table.select( Max(table.timestamp), where=table.name == name)) timestamp, = cursor.fetchone() cursor.execute(*table.select( _cast(Max(table.timestamp)), where=table.name == name)) timestamp, = cursor.fetchone() inst = cls._instances[name] inst._clear(dbname, timestamp) connection.commit() finally: database.put_connection(connection) cls._clean_last = datetime.now() reset.clear()
def commit(cls, transaction): table = Table(cls._table) reset = cls._reset.setdefault(transaction, set()) if not reset: return database = transaction.database dbname = database.name if not _clear_timeout and transaction.database.has_channel(): with transaction.connection.cursor() as cursor: # JCA: Fix for https://bugs.tryton.org/issue8781 resets = list(reset) for i in range(0, len(resets), 10): cursor.execute('NOTIFY "%s", %%s' % cls._channel, (json.dumps(resets[i:i + 10], separators=(',', ':')), )) else: connection = database.get_connection(readonly=False, autocommit=True) try: with connection.cursor() as cursor: for name in reset: cursor.execute(*table.select(table.name, table.id, table.timestamp, where=table.name == name, limit=1)) if cursor.fetchone(): # It would be better to insert only cursor.execute(*table.update( [table.timestamp], [CurrentTimestamp()], where=table.name == name)) else: cursor.execute( *table.insert([table.timestamp, table.name], [[CurrentTimestamp(), name]])) cursor.execute(*table.select(Max(table.timestamp), where=table.name == name)) timestamp, = cursor.fetchone() cursor.execute( *table.select(_cast(Max(table.timestamp)), where=table.name == name)) timestamp, = cursor.fetchone() inst = cls._instances[name] inst._clear(dbname, timestamp) connection.commit() finally: database.put_connection(connection) reset.clear()
def get_access(cls, models): 'Return fields access for models' # root user above constraint if Transaction().user == 0: return defaultdict(lambda: defaultdict( lambda: defaultdict(lambda: True))) pool = Pool() Model = pool.get('ir.model') ModelField = pool.get('ir.model.field') UserGroup = pool.get('res.user-res.group') user = Transaction().user field_access = cls.__table__() ir_model = Model.__table__() model_field = ModelField.__table__() user_group = UserGroup.__table__() accesses = {} for model in models: maccesses = cls._get_access_cache.get((user, model)) if maccesses is None: break accesses[model] = maccesses else: return accesses default = {} accesses = dict((m, default) for m in models) cursor = Transaction().connection.cursor() cursor.execute(*field_access.join(model_field, condition=field_access.field == model_field.id ).join(ir_model, condition=model_field.model == ir_model.id ).join(user_group, 'LEFT', condition=user_group.group == field_access.group ).select( ir_model.model, model_field.name, Max(Case((field_access.perm_read == True , 1), else_=0)), Max(Case((field_access.perm_write == True, 1), else_=0)), Max(Case((field_access.perm_create == True, 1), else_=0)), Max(Case((field_access.perm_delete == True, 1), else_=0)), where=ir_model.model.in_(models) & ((user_group.user == user) | (field_access.group == Null)), group_by=[ir_model.model, model_field.name])) for m, f, r, w, c, d in cursor.fetchall(): accesses[m][f] = {'read': r, 'write': w, 'create': c, 'delete': d} for model, maccesses in accesses.iteritems(): cls._get_access_cache.set((user, model), maccesses) return accesses
def get_access(cls, models): 'Return access for models' # root user above constraint if Transaction().user == 0: return defaultdict(lambda: defaultdict(lambda: True)) pool = Pool() Model = pool.get('ir.model') UserGroup = pool.get('res.user-res.group') cursor = Transaction().cursor user = Transaction().user model_access = cls.__table__() ir_model = Model.__table__() user_group = UserGroup.__table__() access = {} for model in models: maccess = cls._get_access_cache.get((user, model), default=-1) if maccess == -1: break access[model] = maccess else: return access default = {'read': True, 'write': True, 'create': True, 'delete': True} access = dict((m, default) for m in models) cursor.execute(*model_access.join( ir_model, 'LEFT', condition=model_access.model == ir_model.id ).join(user_group, 'LEFT', condition=user_group.group == model_access.group).select( ir_model.model, Max(Case((model_access.perm_read, 1), else_=0)), Max(Case((model_access.perm_write, 1), else_=0)), Max(Case((model_access.perm_create, 1), else_=0)), Max(Case((model_access.perm_delete, 1), else_=0)), where=ir_model.model.in_(models) & ((user_group.user == user) | (model_access.group == Null)), group_by=ir_model.model)) access.update( dict((m, { 'read': r, 'write': w, 'create': c, 'delete': d }) for m, r, w, c, d in cursor.fetchall())) for model, maccess in access.iteritems(): cls._get_access_cache.set((user, model), maccess) return access
def table_query(cls): pool = Pool() Invoice = pool.get('account.invoice') InvoiceTax = pool.get('account.invoice.tax') Move = pool.get('account.move') Period = pool.get('account.period') Tax = pool.get('account.tax') context = Transaction().context invoice = Invoice.__table__() invoice_tax = InvoiceTax.__table__() move = Move.__table__() period = Period.__table__() tax = Tax.__table__() sales = super().table_query() where = ((invoice.company == context.get('company')) & (period.fiscalyear == context.get('fiscalyear'))) if context.get('period'): where &= (period.id == context.get('period')) where &= ((tax.es_ec_purchases_list_code != Null) & (tax.es_ec_purchases_list_code != '')) where &= invoice.type == 'in' purchases = (invoice_tax.join( invoice, condition=invoice_tax.invoice == invoice.id ).join(tax, condition=invoice_tax.tax == tax.id).join( move, condition=invoice.move == move.id).join( period, condition=move.period == period.id).select( Max(invoice_tax.id).as_('id'), Literal(0).as_('create_uid'), Min(invoice_tax.create_date).as_('create_date'), Literal(0).as_('write_uid'), Max(invoice_tax.write_date).as_('write_date'), invoice.tax_identifier.as_('company_tax_identifier'), invoice.party.as_('party'), invoice.party_tax_identifier.as_('party_tax_identifier'), tax.es_ec_purchases_list_code.as_('code'), Sum(invoice_tax.base).as_('amount'), invoice.currency.as_('currency'), where=where, group_by=[ invoice.tax_identifier, invoice.party, invoice.party_tax_identifier, tax.es_ec_purchases_list_code, invoice.currency, ])) return sales | purchases
def table_query(): pool = Pool() Move = pool.get('stock.move') Location = pool.get('stock.location') move = Move.__table__() product_id = Transaction().context.get('product') warehouse_id = Transaction().context.get('warehouse', -1) warehouse_query = Location.search([ ('parent', 'child_of', [warehouse_id]), ], query=True, order=[]) date_column = Coalesce(move.effective_date, move.planned_date ).as_('date') return move.select( Max(move.id).as_('id'), Literal(0).as_('create_uid'), Now().as_('create_date'), Literal(None).as_('write_uid'), Literal(None).as_('write_date'), date_column, where=(move.product == product_id) & (move.from_location.in_(warehouse_query) | move.to_location.in_(warehouse_query)) & (Coalesce(move.effective_date, move.planned_date) != Null), group_by=(date_column, move.product))
def table_query(cls): pool = Pool() Month = pool.get('ir.calendar.month') month = Month.__table__() query = super(SaleOpportunityEmployeeMonthly, cls).table_query() opportunity, = query.from_ type_id = cls.id.sql_type().base type_year = cls.year.sql_type().base year_column = Extract( 'YEAR', opportunity.start_date).cast(type_year).as_('year') month_index = Extract('MONTH', opportunity.start_date) query.from_ = opportunity.join(month, condition=month_index == month.index) query.columns += ( Max( Extract('MONTH', opportunity.start_date) + Extract('YEAR', opportunity.start_date) * 100 + Coalesce(opportunity.employee, 0) * 1000000).cast(type_id).as_('id'), year_column, month.id.as_('month'), opportunity.employee, ) query.group_by = (year_column, month.id, opportunity.employee, opportunity.company) return query
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 _get_target_tables(self, tables): Target = self.get_target() table, _ = tables[None] target_tables = tables.get(self.name) context = Transaction().context if target_tables is None: if Target._history and context.get('_datetime'): target = Target.__table_history__() target_history = Target.__table_history__() history_condition = Column(target, '__id').in_( target_history.select( Max(Column(target_history, '__id')), where=Coalesce( target_history.write_date, target_history.create_date) <= context['_datetime'], group_by=target_history.id)) else: target = Target.__table__() history_condition = None condition = target.id == self.sql_column(table) if history_condition: condition &= history_condition target_tables = { None: (target, condition), } tables[self.name] = target_tables return target_tables
def table_query(): pool = Pool() Move = pool.get('stock.move') Location = pool.get('stock.location') Product = pool.get('product.product') move = from_ = Move.__table__() context = Transaction().context if context.get('product_template') is not None: product = Product.__table__() from_ = move.join(product, condition=move.product == product.id) product_clause = (product.template == context['product_template']) else: product_clause = move.product == context.get('product', -1) warehouse_id = context.get('warehouse', -1) warehouse_query = Location.search([ ('parent', 'child_of', [warehouse_id]), ], query=True, order=[]) date_column = Coalesce(move.effective_date, move.planned_date).as_('date') return from_.select( Max(move.id).as_('id'), Literal(0).as_('create_uid'), CurrentTimestamp().as_('create_date'), Literal(None).as_('write_uid'), Literal(None).as_('write_date'), date_column, where=product_clause & (move.from_location.in_(warehouse_query) | move.to_location.in_(warehouse_query)) & (Coalesce(move.effective_date, move.planned_date) != Null), group_by=(date_column, move.product))
def last_visit_query(cls): pool = Pool() Visit = pool.get('sale.direct.visit') tomorrow = Literal( datetime.combine(date.today() + timedelta(days=1), datetime.min.time())) visit = Visit.__table__() last_visit = visit.select( visit.address.as_('address'), Max(visit.time).as_('time'), group_by=[visit.address], ) visit = Visit.__table__() revisit_required = (visit.revisit_time != Null) revisit_today = (visit.revisit_time < tomorrow) return visit.join( last_visit, condition=((visit.address == last_visit.address) & (visit.time == last_visit.time))).select( visit.id.as_('id'), visit.address.as_('address'), visit.time.as_('last_visit_time'), visit.type.as_('last_visit_type'), visit.notes.as_('last_visit_notes'), revisit_required.as_('revisit_required'), revisit_today.as_('revisit_today'), visit.revisit_time.as_('revisit_due'), )
def table_query(cls): pool = Pool() ProductCostPrice = pool.get('product.cost_price') history = ProductCostPrice.__table_history__() return history.select(Max(Column(history, '__id')).as_('id'), Max(history.create_uid).as_('create_uid'), Max(history.create_date).as_('create_date'), Max(history.write_uid).as_('write_uid'), Max(history.write_date).as_('write_date'), Coalesce(history.write_date, history.create_date).as_('date'), history.product.as_('product'), history.cost_price.as_('cost_price'), group_by=(history.id, Coalesce(history.write_date, history.create_date), history.product, history.cost_price))
def table_query(cls): pool = Pool() Move = pool.get('stock.move') Location = pool.get('stock.location') Product = pool.get('product.product') Date = pool.get('ir.date') move = from_ = Move.__table__() context = Transaction().context today = Date.today() if context.get('product_template') is not None: product = Product.__table__() from_ = move.join(product, condition=move.product == product.id) product_clause = (product.template == context['product_template']) else: product_clause = move.product == context.get('product', -1) if 'warehouse' in context: warehouse = Location(context.get('warehouse')) if context.get('stock_skip_warehouse'): location_id = warehouse.storage_location.id else: location_id = warehouse.id else: location_id = -1 warehouse = With('id', query=Location.search([ ('parent', 'child_of', [location_id]), ], query=True, order=[])) date_column = Coalesce(move.effective_date, move.planned_date) return (from_.select( Max(move.id).as_('id'), Literal(0).as_('create_uid'), CurrentTimestamp().as_('create_date'), Literal(None).as_('write_uid'), Literal(None).as_('write_date'), date_column.as_('date'), move.company.as_('company'), where=product_clause & ((move.from_location.in_(warehouse.select(warehouse.id)) & ~move.to_location.in_(warehouse.select(warehouse.id))) | (~move.from_location.in_(warehouse.select(warehouse.id)) & move.to_location.in_(warehouse.select(warehouse.id)))) & ((date_column < today) & (move.state == 'done') | (date_column >= today)), group_by=(date_column, move.product, move.company), with_=warehouse) | Select([ Literal(0).as_('id'), Literal(0).as_('create_uid'), CurrentTimestamp().as_('create_date'), Literal(None).as_('write_uid'), Literal(None).as_('write_date'), Literal(today).as_('date'), Literal(context.get('company', -1)).as_('company'), ]))
def table_query(): pool = Pool() Line = pool.get('timesheet.line') line = Line.__table__() where = Literal(True) if Transaction().context.get('start_date'): where &= line.date >= Transaction().context['start_date'] if Transaction().context.get('end_date'): where &= line.date <= Transaction().context['end_date'] return line.select(line.employee.as_('id'), Max(line.create_uid).as_('create_uid'), Max(line.create_date).as_('create_date'), Max(line.write_uid).as_('write_uid'), Max(line.write_date).as_('write_date'), line.employee, Sum(line.duration).as_('duration'), where=where, group_by=line.employee)
def table_query(cls): pool = Pool() Line = pool.get('timesheet.line') line = Line.__table__() year_column = Extract('YEAR', line.date).as_('year') week_column = Extract('WEEK', line.date).as_('week') return line.select(Max( Extract('WEEK', line.date) + Extract('YEAR', line.date) * 100 + line.employee * 1000000).as_('id'), Max(line.create_uid).as_('create_uid'), Max(line.create_date).as_('create_date'), Max(line.write_uid).as_('write_uid'), Max(line.write_date).as_('write_date'), year_column, week_column, line.employee, Sum(line.duration).as_('duration'), group_by=(year_column, week_column, line.employee))
def _column_id(cls, tables, withs): pool = Pool() Category = pool.get('party.category') category = Category.__table__() line = tables['line'] party_category = tables['line.customer.party_category'] # Get a stable number of categories over time # by using a number one order bigger. nb_category = category.select( Power(10, (Ceil(Log(Max(category.id))) + Literal(1)))) return Min(line.id * nb_category + party_category.id)
def _column_id(cls, tables, withs): pool = Pool() Category = pool.get('product.category') category = Category.__table__() move = tables['move'] template_category = tables['move.product.template_category'] # Get a stable number of category over time # by using number one order bigger. nb_category = category.select( Power(10, (Ceil(Log(Max(category.id))) + Literal(1)))) return Min(move.id * nb_category + template_category.id)
def table_query(cls): pool = Pool() Line = pool.get('timesheet.line') line = Line.__table__() type_name = cls.year.sql_type().base year_column = Extract('YEAR', line.date).cast(type_name).as_('year') month_column = Extract('MONTH', line.date).as_('month') return line.select( Max(Extract('MONTH', line.date) + Extract('YEAR', line.date) * 100 + line.employee * 1000000).as_('id'), Max(line.create_uid).as_('create_uid'), Max(line.create_date).as_('create_date'), Max(line.write_uid).as_('write_uid'), Max(line.write_date).as_('write_date'), year_column, month_column, line.employee, Sum(line.duration).as_('duration'), group_by=(year_column, month_column, line.employee))
def table_query(self=None): price_list = Pool().get('price_master_datas.pricedata') PriceList = price_list.__table__() where = Literal(False) if Transaction().context.get('type') == '01': Product = Pool().get('product.product') drug_code = Transaction().context.get('drug_code') product_id = Product.search([('id', '=', drug_code)]) if product_id != []: where = Literal(True) product_id_price = product_id[0].id Price = price_list.search( [('retrieve_the_code', '=', product_id_price)], query=True, order=[]) where &= PriceList.id.in_(Price) if Transaction().context.get('type') == '00': condition = [] start_time = Transaction().context.get('start_time') end_time = Transaction().context.get('end_time') if start_time != None: condition.append(('effective_date', '>=', start_time), ) if end_time != None: condition.append(('effective_date', '<=', end_time), ) Price = price_list.search(condition) if Price != []: price_id = [] where = Literal(True) for i in Price: price_id.append(i.id) where &= PriceList.id.in_(price_id) Result = PriceList.select( PriceList.id.as_('id'), Max(PriceList.create_uid).as_('create_uid'), Max(PriceList.create_date).as_('create_date'), Max(PriceList.write_uid).as_('write_uid'), Max(PriceList.write_date).as_('write_date'), PriceList.drug_specifications.as_('drug_specifications'), where=where, group_by=PriceList.id) return Result