def table_query(cls):
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_column = Extract('MONTH', opportunity.start_date).as_('month')
query.columns += (
Max(
Extract('MONTH', opportunity.start_date) +
Extract('YEAR', opportunity.start_date) * 100 +
opportunity.employee * 1000000).cast(type_id).as_('id'),
year_column,
month_column,
opportunity.employee,
)
query.group_by = (year_column, month_column, opportunity.employee,
opportunity.company)
return query
def getter_latest_book(cls, authors, name):
result = {x.id: None for x in authors}
Book = Pool().get('library.book')
book = Book.__table__()
sub_book = Book.__table__()
cursor = Transaction().connection.cursor()
sub_query = sub_book.select(
sub_book.author,
Max(Coalesce(sub_book.publishing_date, datetime.date.min),
window=Window([sub_book.author])).as_('max_date'),
where=sub_book.author.in_([x.id for x in authors]))
cursor.execute(
*book.join(sub_query,
condition=(book.author == sub_query.author)
& (Coalesce(book.publishing_date, datetime.date.min) ==
sub_query.max_date)).select(book.author, book.id))
for author_id, book in cursor.fetchall():
result[author_id] = book
return result
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 table_query(self=None):
Config = Pool().get('purchase.configuration')
config = Config(1) # 库存地配置
Lot = Pool().get('stock.lot')
product_lot = Lot.__table__()
where = Literal(True)
ProductDrop = Pool().get('product.template')
Product = Pool().get('product.product')
UomCategory = Pool().get('product.category')
if Transaction().context.get('location'):
location_id = Transaction().context['location']
day = Transaction().context['time']
value_none = []
location_ids = Transaction().context.get('location')
if location_ids == config.warehouse.storage_location.id: # 中心药库
location_frozen_id = config.return_of.id # 中心药库冻结区
elif location_ids == config.hospital.storage_location.id: # 住院药房
location_frozen_id = config.hospital_freeze.id # 住院药房冻结区
elif location_ids == config.outpatient_service.storage_location.id: # 门诊药房
location_frozen_id = config.outpatient_freeze.id # 门诊药房冻结区
elif location_ids == config.medical.storage_location.id: # 体检药房
location_frozen_id = config.medical.freeze_location.id # 体检药房冻结区
elif location_ids == config.endoscopic.storage_location.id: # 内镜药房
location_frozen_id = config.endoscopic.freeze_location.id # 内镜药房冻结区
elif location_ids == config.preparation.storage_location.id: # 制剂室
location_frozen_id = config.preparation.freeze_location.id # 制剂室冻结区
elif location_ids == config.ward.storage_location.id: # 放射科
location_frozen_id = config.ward.freeze_location.id # 放射科冻结区
elif location_ids == config.herbs.storage_location.id: # 草药房
location_frozen_id = config.herbs.freeze_location.id # 草药房冻结区
else:
location_frozen_id = config.return_of.id # 药库冻结区
with Transaction().set_context(stock_date_end=day): # 查看具体库下面的批次对应的数量
warehouse_quant = Product.products_by_location([location_frozen_id], with_childs=True,
grouping=('product', 'lot'))
for key, value in warehouse_quant.items():
if value > 0.0:
if key[-1] != None:
value_none.append(key[-1])
else:
pass
with Transaction().set_context(stock_date_end=day): # 查看具体库下面的批次对应的数量
warehouse_quant = Product.products_by_location([location_id], with_childs=True,
grouping=('product', 'lot'))
lists = []
list_lot = []
product_id = []
intersection_product = []
for i in value_none:
list_lot.append(i)
for key, value in warehouse_quant.items():
if value > 0.0:
if key[-1] != None:
lists.append(key[-1])
product_id.append(key[1])
list_lot.append(key[-1])
if Transaction().context.get('drug_type'): # 通过药品种类进行区分
drug_type_id_list = []
if Transaction().context['drug_type'] == '00':
drug_name = u'西药'
elif Transaction().context['drug_type'] == '01':
drug_name = u'中成药'
elif Transaction().context['drug_type'] == '02':
drug_name = u'中草药'
elif Transaction().context['drug_type'] == '03':
drug_name = u'颗粒中'
elif Transaction().context['drug_type'] == '04':
drug_name = u'原料药'
elif Transaction().context['drug_type'] == '05':
drug_name = u'敷药'
elif Transaction().context['drug_type'] == '07':
drug_name = u'同位素'
else:
drug_name = ''
if drug_name == '':
pass
else:
uom_category = UomCategory.search([('name', '=', drug_name)])
Drug_type_id = ProductDrop.search([('categories', '=', [uom_category[0].id])])
if Drug_type_id:
for i in Drug_type_id:
intersection_product.append(i)
drug_type_id_list.append(i.products[0].id)
else:
pass
intersection_id = [val for val in product_id if val in drug_type_id_list]
ids_list = []
for ids in intersection_id:
find_lot_id = Lot.search([('product', '=', ids)])
if find_lot_id:
for i in find_lot_id:
ids_list.append(i.id)
ggg = [val for val in list_lot if val in ids_list]
del list_lot[:]
for i in ggg:
list_lot.append(i)
if Transaction().context.get('product'):
product_product = []
product_ids = Transaction().context.get('product')
product_lots = Lot.search([('product', '=', product_ids)])
if product_lots:
for i in product_lots:
product_product.append(i.id)
test_lot_id = [val for val in product_product if val in list_lot]
del list_lot[:]
for i in test_lot_id:
list_lot.append(i)
if intersection_product == []:
pass
else:
lot_list = []
intersection_product_id = [val for val in product_product if val in intersection_product]
if intersection_product_id:
for ids in intersection_product_id:
find_lot_id = Lot.search([('product', '=', ids)])
if find_lot_id:
for i in find_lot_id:
lot_list.append(i.id)
del list_lot[:]
lot_number = [val for val in lot_list if val in list_lot]
for i in lot_number:
list_lot.append(i)
if Transaction().context.get('lot'):
lot_id = Transaction().context.get('lot')
del list_lot[:]
list_lot.append(lot_id)
product_lot_all = Lot.search([('id', 'in', list_lot)], query=True, order=[])
where &= product_lot.id.in_(product_lot_all)
Result = product_lot.select(
product_lot.id.as_('id'),
Max(product_lot.create_uid).as_('create_uid'),
Max(product_lot.create_date).as_('create_date'),
Max(product_lot.write_uid).as_('write_uid'),
Max(product_lot.write_date).as_('write_date'),
product_lot.number.as_('lot'),
product_lot.product.as_('product'),
product_lot.shelf_life_expiration_date.as_('term_of_validity'),
where=where,
group_by=product_lot.id)
return Result
def get_lastmodified(cls, uri, cache=None):
pool = Pool()
Calendar = pool.get('calendar.calendar')
Event = pool.get('calendar.event')
calendar = Calendar.__table__()
event = Event.__table__()
transaction = Transaction()
cursor = transaction.connection.cursor()
calendar_id = cls.calendar(uri)
if calendar_id:
if not (uri[10:].split('/', 1) + [None])[1]:
if cache is not None:
cache.setdefault('_calendar', {})
cache['_calendar'].setdefault(Calendar.__name__, {})
ids = list(cache['_calendar'][Calendar.__name__].keys())
if calendar_id not in ids:
ids.append(calendar_id)
elif 'lastmodified' in cache['_calendar'][
Calendar.__name__][calendar_id]:
return cache['_calendar'][Calendar.__name__][
calendar_id]['lastmodified']
else:
ids = [calendar_id]
res = None
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(calendar.id, sub_ids)
cursor.execute(*calendar.select(calendar.id,
Extract('EPOCH', Coalesce(calendar.write_date,
calendar.create_date)),
where=red_sql))
for calendar_id2, date in cursor.fetchall():
if calendar_id2 == calendar_id:
res = date
if cache is not None:
cache['_calendar'][Calendar.__name__]\
.setdefault(calendar_id2, {})
cache['_calendar'][Calendar.__name__][
calendar_id2]['lastmodified'] = date
if res is not None:
return res
else:
event_id = cls.event(uri, calendar_id=calendar_id)
if event_id:
if cache is not None:
cache.setdefault('_calendar', {})
cache['_calendar'].setdefault(Event.__name__, {})
ids = list(cache['_calendar'][Event.__name__].keys())
if event_id not in ids:
ids.append(event_id)
elif 'lastmodified' in cache['_calendar'][
Event.__name__][event_id]:
return cache['_calendar'][Event.__name__][
event_id]['lastmodified']
else:
ids = [event_id]
res = None
# for sub_ids in grouped_slice(ids,
# transaction.database.IN_MAX / 2):
for sub_ids in grouped_slice(ids):
red_id_sql = reduce_ids(event.id, sub_ids)
red_parent_sql = reduce_ids(event.parent, sub_ids)
cursor.execute(*event.select(
Coalesce(event.parent, event.id),
Max(Extract('EPOCH', Coalesce(event.write_date,
event.create_date))),
where=red_id_sql | red_parent_sql,
group_by=(event.parent, event.id)))
for event_id2, date in cursor.fetchall():
if event_id2 == event_id:
res = date
if cache is not None:
cache['_calendar'][Event.__name__]\
.setdefault(event_id2, {})
cache['_calendar'][Event.__name__][
event_id2]['lastmodified'] = date
if res is not None:
return res
calendar_ics_id = cls.calendar(uri, ics=True)
if calendar_ics_id:
if cache is not None:
cache.setdefault('_calendar', {})
cache['_calendar'].setdefault(Calendar.__name__, {})
ids = list(cache['_calendar'][Calendar.__name__].keys())
if calendar_ics_id not in ids:
ids.append(calendar_ics_id)
elif 'lastmodified ics' in cache['_calendar'][
Calendar.__name__][calendar_ics_id]:
return cache['_calendar'][Calendar.__name__][
calendar_ics_id]['lastmodified ics']
else:
ids = [calendar_ics_id]
res = None
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(event.calendar, sub_ids)
cursor.execute(*event.select(event.calendar,
Max(Extract('EPOCH', Coalesce(event.write_date,
event.create_date))),
where=red_sql,
group_by=event.calendar))
for calendar_id2, date in cursor.fetchall():
if calendar_id2 == calendar_ics_id:
res = date
if cache is not None:
cache['_calendar'][Calendar.__name__]\
.setdefault(calendar_id2, {})
cache['_calendar'][Calendar.__name__][
calendar_id2]['lastmodified ics'] = date
if res is not None:
return res
return super(Collection, cls).get_lastmodified(uri, cache=cache)
def migrate_property(model_name,
field_names,
ValueModel,
value_names,
parent=None,
fields=None):
"Migrate property from model_name.field_name to ValueModel.value_name"
pool = Pool()
Field = pool.get('ir.model.field')
Model = pool.get('ir.model')
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
cursor = Transaction().connection.cursor()
field = Field.__table__()
model = Model.__table__()
table = ValueModel.__table__()
if fields is None:
fields = []
if isinstance(field_names, basestring):
field_names = [field_names]
if isinstance(value_names, basestring):
value_names = [value_names]
def split_value(value):
return value.split(',')[1]
cast_funcs = {
'numeric': lambda v: Decimal(split_value(v)) if v else None,
'integer': lambda v: int(split_value(v)) if v else None,
'float': lambda v: float(split_value(v)) if v else None,
'char': lambda v: split_value(v) if v else None,
'selection': lambda v: split_value(v) if v else None,
'many2one': lambda v: int(split_value(v)) if v else None,
'reference': lambda v: v,
}
casts = []
queries = []
for field_name, value_name in zip(field_names, value_names):
value_field = getattr(ValueModel, value_name)
casts.append(cast_funcs[value_field._type])
property_ = Table('ir_property')
columns = [
Literal(None).as_(f)
if f != value_name else property_.value.as_(value_name)
for f in value_names
]
if parent:
columns.append(property_.res.as_(parent))
where = property_.res.like(model_name + ',%')
else:
where = property_.res == Null
columns.extend([Column(property_, f).as_(f) for f in fields])
query = property_.join(field,
condition=property_.field == field.id).join(
model,
condition=field.model == model.id).select(
*columns,
where=where
& (field.name == field_name)
& (model.model == model_name))
queries.append(query)
union = Union(*queries)
columns = [Max(Column(union, f)).as_(f) for f in value_names]
if parent:
columns.append(Column(union, parent).as_(parent))
pcolumns = [Column(union, parent)]
else:
pcolumns = []
vcolumns = [Column(union, f).as_(f) for f in fields]
cursor.execute(
*union.select(*(columns + vcolumns), group_by=pcolumns + vcolumns))
columns = [Column(table, f) for f in value_names]
if parent:
pcolumns = [Column(table, parent)]
else:
pcolumns = []
vcolumns = [Column(table, f) for f in fields]
values = []
l = len(value_names)
for row in cursor.fetchall():
value = [c(v) for v, c in zip(row, casts)]
if parent:
value.append(int(row[l].split(',')[1]) if row[l] else None)
i = 1
else:
i = 0
value.extend(row[l + i:])
values.append(value)
if (values and not (
# No property defined
len(values) == 1 and all(x is None
for x in values[0][:len(columns)]))):
# Delete previous migrated values
cursor.execute(*table.delete())
cursor.execute(
*table.insert(columns + pcolumns + vcolumns, values=values))
('parent_id.parent_id.code', '=', 34)
])
t = Table('table')
t2 = Table('table')
t3 = Table('table')
join = t.join(t2)
join.condition = t.parent_id == join.right.id
join2 = join.join(t3)
join2.condition = t2.parent_id == join2.right.id
sel = join2.select(t.id.as_('id'))
sel.where = And((t3.code == 34,))
expect(tuple(sql)).to(equal(tuple(sel)))
with it('must support group by'):
q = OOQuery('table')
sel = q.select([Max('field1')], group_by=['field2'])
sel2 = q.table.select(
Max(q.table.field1).as_('max_field1'),
group_by=[q.table.field2]
)
expect(str(sel._select)).to(equal(str(sel2)))
with it('must support concat'):
q = OOQuery('table')
sel = q.select([Concat('field1', Literal(' 01:00'))])
sel2 = q.table.select(Concat(q.table.field1, ' 01:00'))
expect(str(sel._select)).to(equal(str(sel2)))
with it('must support coalesce'):
q = OOQuery('table')
sel = q.select([Coalesce('field1', Literal(3))])
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_template = context['product_template']
if isinstance(product_template, int):
product_template = [product_template]
product = Product.__table__()
from_ = move.join(product, condition=move.product == product.id)
product_clause = product.template.in_(product_template or [-1])
product_column = Concat('product.template,', product.template)
products = [('product.template', i) for i in product_template]
else:
product = context.get('product')
if product is None:
product = []
if isinstance(product, int):
product = [product]
product_clause = move.product.in_(product or [-1])
product_column = Concat('product.product,', move.product)
products = [('product.product', i) for i in product]
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)
query = (from_.select(
Max(move.id * 3).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
product_column.as_('product'),
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, product_column, move.company),
with_=warehouse))
for model, id_ in products:
gap = ['product.template', 'product.product'].index(model) + 1
query |= Select([
Literal(id_ * 3 + gap).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Literal('%s,%s' % (model, id_)).as_('product'),
Literal(today).as_('date'),
Literal(context.get('company', -1)).as_('company'),
])
return query
def table_query(cls):
pool = Pool()
Identifier = pool.get('party.identifier')
Invoice = pool.get('account.invoice')
InvoiceTax = pool.get('account.invoice.tax')
ModelData = pool.get('ir.model.data')
Move = pool.get('account.move')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
company_identifier = Identifier.__table__()
party_identifier = Identifier.__table__()
invoice = Invoice.__table__()
invoice_tax = InvoiceTax.__table__()
move = Move.__table__()
period = Period.__table__()
tax = Tax.__table__()
groups = []
for module, fs_id in cls.tax_groups():
try:
groups.append(ModelData.get_id(module, fs_id))
except KeyError:
# table_query can be called before the XML is loaded
continue
where = ((invoice.company == context.get('company'))
& (period.fiscalyear == context.get('fiscalyear')))
where &= invoice.type == 'out'
where &= (company_identifier.code.ilike('BE%')
& (company_identifier.type == 'eu_vat'))
where &= (party_identifier.code.ilike('BE%')
& (party_identifier.type == 'eu_vat'))
where &= tax.group.in_(groups)
return (
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).join(
company_identifier,
condition=invoice.tax_identifier ==
company_identifier.id).join(
party_identifier,
condition=invoice.party_tax_identifier ==
party_identifier.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_tax_identifier.as_('party_tax_identifier'),
Sum(invoice_tax.base).as_('turnover'),
Sum(invoice_tax.amount).as_('vat'),
invoice.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.party_tax_identifier,
invoice.currency,
]))
def table_query(self=None):
UomCategory = Pool().get('product.category')
move = Pool().get('stock.move')
Move = move.__table__()
ConsumeProduct = Pool().get('stock.shipment.out.return')
ReturnProduct = Pool().get('stock.shipment.out')
where = Literal(True)
content = []
if Transaction().context.get('start_time') != None:
content.append(('effective_date', '>=', Transaction().context.get('start_time')), )
if Transaction().context.get('end_time') != None:
content.append(('effective_date', '<=', Transaction().context.get('end_time')), )
if Transaction().context.get('drug_type') == '00':
drug_type_name = u'西药'
if Transaction().context.get('drug_type') == '01':
drug_type_name = u'中成药'
if Transaction().context.get('drug_type') == '02':
drug_type_name = u'中草药'
if Transaction().context.get('drug_type') == '03':
drug_type_name = u'颗粒中'
if Transaction().context.get('drug_type') == '04':
drug_type_name = u'原料药'
if Transaction().context.get('drug_type') == '05':
drug_type_name = u'敷药'
if Transaction().context.get('drug_type') == '06':
drug_type_name = u''
if Transaction().context.get('drug_type') == '07':
drug_type_name = u'同位素'
if Transaction().context.get('location'):
stock_move_id = []
content.append(('warehouse', '=', Transaction().context.get('location')))
consume_product = ConsumeProduct.search(content)
if consume_product:
for consume_each in consume_product:
consume_move = consume_each.incoming_moves
for move_each in consume_move:
categories = [i.id for i in move_each.product.categories]
uom_category = UomCategory.search([('id', '=', categories[0])])
uom_name = uom_category[0].name
if drug_type_name == '':
stock_move_id.append(move_each.id)
else:
if uom_name == drug_type_name:
stock_move_id.append(move_each.id)
else:
pass
else:
pass
return_product = ReturnProduct.search(content)
if return_product:
for return_each in return_product:
return_move = return_each.outgoing_moves
for move_each in return_move:
categories = [i.id for i in move_each.product.categories]
uom_category = UomCategory.search([('id', '=', categories[0])])
uom_name = uom_category[0].name
if drug_type_name == '':
stock_move_id.append(move_each.id)
else:
if uom_name == drug_type_name:
stock_move_id.append(move_each.id)
else:
pass
else:
pass
if stock_move_id == []:
where = Literal(False)
else:
party_list = []
for each_move in stock_move_id:
party_dict = {}
move_number = move.search([('id', '=', each_move)])[0].shipment
return_consume = str(move_number).split(',')
return_consume_id = int(return_consume[-1])
return_or_consume = return_consume[0]
if return_or_consume == 'stock.shipment.out.return':
consume_party = ConsumeProduct.search([('id', '=', return_consume_id)])[0].customer.name
consume_party_id = ConsumeProduct.search([('id', '=', return_consume_id)])[0].customer.id
party_dict['party'] = consume_party
party_dict['party_id'] = consume_party_id
party_dict['move_id'] = each_move
party_list.append(party_dict)
if return_or_consume == 'stock.shipment.out':
return_party = ReturnProduct.search([('id', '=', return_consume_id)])[0].customer.name
return_party_id = ReturnProduct.search([('id', '=', return_consume_id)])[0].customer.id
party_dict['party'] = return_party
party_dict['party_id'] = return_party_id
party_dict['move_id'] = each_move
party_list.append(party_dict)
else:
pass
party_id = []
for i in party_list:
party_id.append(i['party_id'])
set_party_id = list(set(party_id))
party_dicts = {}
for each in set_party_id:
move_list = []
for party in party_list:
if party['party_id'] == each:
move_list.append(party['move_id'])
else:
pass
party_dicts[each] = move_list
party_value = party_dicts.values()
Transaction().set_context(price=party_dicts)
find_move_id = []
for value in party_value:
find_move_id.append(value[0])
if find_move_id == []:
where = Literal(False)
else:
where &= Move.id.in_(find_move_id)
Result = Move.select(
Move.id.as_('id'),
Max(Move.create_uid).as_('create_uid'),
Max(Move.create_date).as_('create_date'),
Max(Move.write_uid).as_('write_uid'),
Max(Move.write_date).as_('write_date'),
Move.party.as_('customer'),
where=where,
group_by=Move.id)
return Result
def table_query(self=None):
UomCategory = Pool().get('product.category')
Number = Pool().get('order_no')
number = Number.__table__()
ConsumeProduct = Pool().get('stock.shipment.out.return')
consume_product = ConsumeProduct.__table__()
ReturnProduct = Pool().get('stock.shipment.out')
return_product = ReturnProduct.__table__()
where = Literal(True)
join1 = Join(number, consume_product)
join1.condition = join1.right.warehouse == number.location
join2 = Join(join1, return_product)
join2.condition = join2.right.warehouse == join1.right.warehouse
content = [('order_category', 'in', ['sale_return', 'sale_purchase'])]
if Transaction().context.get('start_time') != None:
content.append(('time', '>=', Transaction().context.get('start_time')), )
if Transaction().context.get('end_time') != None:
content.append(('time', '<=', Transaction().context.get('end_time')), )
if Transaction().context.get('drug_type') == '00':
drug_type_name = u'西药'
if Transaction().context.get('drug_type') == '01':
drug_type_name = u'中成药'
if Transaction().context.get('drug_type') == '02':
drug_type_name = u'中草药'
if Transaction().context.get('drug_type') == '03':
drug_type_name = u'颗粒中'
if Transaction().context.get('drug_type') == '04':
drug_type_name = u'原料药'
if Transaction().context.get('drug_type') == '05':
drug_type_name = u'敷药'
if Transaction().context.get('drug_type') == '07':
drug_type_name = u'同位素'
if Transaction().context.get('location'):
content.append(('location', '=', Transaction().context.get('location')), )
numbers_id = []
start_id = []
number_num = []
number_end = []
finally_id = []
number_id = Number.search(content)
for i in number_id:
number_dict = {}
number_dict['id'] = i.id
number_dict['number'] = i.number
numbers_id.append(number_dict)
number_num.append(i.number)
start_id.append(i.id)
if Transaction().context.get('customer'):
for each in number_num:
if Transaction().context.get('drug_type') == '06':
cosum_ = ConsumeProduct.search([('number', '=', each)])
if cosum_:
for cosum_each in cosum_:
if cosum_each.customer.id == Transaction().context.get('customer'):
number_end.append(each)
else:
pass
else:
pass
return_ = ReturnProduct.search([('number', '=', each)])
if return_:
for return_each in return_:
if return_each.customer.id == Transaction().context.get('customer'):
number_end.append(each)
else:
pass
else:
cosum_ = ConsumeProduct.search([('number', '=', each)])
if cosum_:
for cosum_each in cosum_:
if cosum_each.customer.id == Transaction().context.get('customer'):
incoming_moves = cosum_each.incoming_moves
if incoming_moves:
drug_type_id = []
for orderpoint in incoming_moves:
categories = [i.id for i in orderpoint.product.categories]
uom_category = UomCategory.search([('id', '=', categories[0])])
uom_name = uom_category[0].name
if drug_type_name == uom_name:
drug_type_id.append(1)
else:
pass
if len(drug_type_id) != 0:
number_end.append(each)
else:
pass
else:
pass
return_ = ReturnProduct.search([('number', '=', each)])
if return_:
for return_each in return_:
if return_each.customer.id == Transaction().context.get('customer'):
outgoing_moves = return_each.outgoing_moves
if outgoing_moves:
drug_type_id = []
for orderpoint in outgoing_moves:
categories = [i.id for i in orderpoint.product.categories]
uom_category = UomCategory.search([('id', '=', categories[0])])
uom_name = uom_category[0].name
if drug_type_name == uom_name:
drug_type_id.append(1)
else:
pass
if len(drug_type_id) != 0:
number_end.append(each)
else:
pass
for num in number_end:
for all in numbers_id:
if all['number'] == num:
finally_id.append(all['id'])
else:
pass
if finally_id == []:
where = Literal(False)
else:
where &= number.id.in_(finally_id)
else:
if start_id == []:
where = Literal(False)
else:
where &= number.id.in_(start_id)
where &= number.order_category.in_([
'sale_return',
'sale_purchase',
])
Result = number.select(
number.id.as_('id'),
Max(number.create_uid).as_('create_uid'),
Max(number.create_date).as_('create_date'),
Max(number.write_uid).as_('write_uid'),
Max(number.write_date).as_('write_date'),
number.number,
where=where,
group_by=number.id)
return Result
def table_query():
pool = Pool()
context = Transaction().context
Gp = pool.get('disc.gp')
gp = Gp.__table__()
Iglesia = pool.get('disc.iglesia')
iglesia = Iglesia.__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']
if context.get('fecha_fin'):
where &= reporte.fecha_fin <= context['fecha_fin']
if context.get('distrito'):
where &= reporte.distrito == context['distrito']
subquery = (
reporte.join(reporte_linea,
condition=reporte_linea.reporte == reporte.id).select(
Max(reporte_linea.id * 1000).as_('id'),
Max(reporte.create_uid).as_('create_uid'),
Max(reporte.create_date).as_('create_date'),
Max(reporte.write_uid).as_('write_uid'),
Max(reporte.write_date).as_('write_date'),
reporte_linea.gp,
reporte.iglesia,
Max(reporte.distrito).as_('distrito'),
#reporte.mes,
(Sum(reporte_linea.cantidad)).as_('total'),
where=where,
group_by=[reporte_linea.gp, reporte.iglesia]))
if context.get('distrito'):
distrito = context['distrito']
where = subquery.distrito == distrito
iglesias = Iglesia.search(['distrito', '=', distrito])
if iglesias:
for iglesia in iglesias:
where |= gp.iglesia == iglesia.id
where &= gp.active == True
else:
where = Literal(True)
#where &= subquery.distrito == None
#if iglesias:
# where &= gp.iglesia in (x.id for x in iglesias)
#print "WHERE: " + str(where)
query = (subquery.join(gp, 'FULL',
condition=gp.id == subquery.gp).select(
Max(gp.id * 1000).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(Coalesce(subquery.total,
0))).as_('total'),
gp.iglesia,
subquery.distrito,
(gp.id).as_('gp'),
(gp.active),
where=where,
group_by=(gp.id, subquery.distrito,
gp.active),
order_by=Sum(Coalesce(subquery.total,
0)).desc,
))
return query
