def name_search(self, name, args=None, operator='ilike', limit=100):
if args is None:
args = []
if name and operator in ('=', 'ilike', '=ilike', 'like', '=like'):
self.check_access_rights('read')
where_query = self._where_calc(args)
self._apply_ir_rules(where_query, 'read')
from_clause, where_clause, where_clause_params = where_query.get_sql(
)
from_str = from_clause if from_clause else 'res_partner'
where_str = where_clause and (" WHERE %s AND " %
where_clause) or ' WHERE '
# search on the name of the contacts and of its company
search_name = name
if operator in ('ilike', 'like'):
search_name = '%%%s%%' % name
if operator in ('=ilike', '=like'):
operator = operator[1:]
unaccent = get_unaccent_wrapper(self.env.cr)
query = """SELECT res_partner.id
FROM {from_str}
{where} ({email} {operator} {percent}
OR {display_name} {operator} {percent}
OR {reference} {operator} {percent}
OR {vat} {operator} {percent})
-- don't panic, trust postgres bitmap
ORDER BY {display_name} {operator} {percent} desc,
{display_name}
""".format(
from_str=from_str,
where=where_str,
operator=operator,
email=unaccent('res_partner.email'),
display_name=unaccent('res_partner.display_name'),
reference=unaccent('res_partner.ref'),
percent=unaccent('%s'),
vat=unaccent('res_partner.vat'),
)
where_clause_params += [search_name] * 5
if limit:
query += ' limit %s'
where_clause_params.append(limit)
self.env.cr.execute(query, where_clause_params)
partner_ids = [row[0] for row in self.env.cr.fetchall()]
if partner_ids:
return self.browse(partner_ids).name_get()
else:
return []
return super(Partner, self).name_search(name,
args,
operator=operator,
limit=limit)
def _name_search(self, name, args=None, operator='ilike', limit=100, name_get_uid=None):
self = self.with_user(name_get_uid or self.env.uid)
# as the implementation is in SQL, we force the recompute of fields if necessary
self.recompute(['display_name'])
self.flush()
if args is None:
args = []
order_by_rank = self.env.context.get('res_partner_search_mode')
if (name or order_by_rank) and operator in ('=', 'ilike', '=ilike', 'like', '=like'):
self.check_access_rights('read')
where_query = self._where_calc(args)
self._apply_ir_rules(where_query, 'read')
from_clause, where_clause, where_clause_params = where_query.get_sql()
from_str = from_clause if from_clause else 'res_partner'
where_str = where_clause and (" WHERE %s AND " % where_clause) or ' WHERE '
# search on the name of the contacts and of its company
search_name = name
if operator in ('ilike', 'like'):
search_name = '%%%s%%' % name
if operator in ('=ilike', '=like'):
operator = operator[1:]
unaccent = get_unaccent_wrapper(self.env.cr)
fields = self._get_name_search_order_by_fields()
query = """SELECT res_partner.id
FROM {from_str}
{where} ({email} {operator} {percent}
OR {display_name} {operator} {percent}
OR {reference} {operator} {percent}
OR {vat} {operator} {percent})
-- don't panic, trust postgres bitmap
ORDER BY {fields} {display_name} {operator} {percent} desc,
{display_name}
""".format(from_str=from_str,
fields=fields,
where=where_str,
operator=operator,
email=unaccent('res_partner.email'),
display_name=unaccent('res_partner.display_name'),
reference=unaccent('res_partner.ref'),
percent=unaccent('%s'),
vat=unaccent('res_partner.vat'),)
where_clause_params += [search_name]*3 # for email / display_name, reference
where_clause_params += [re.sub('[^a-zA-Z0-9\-\.]+', '', search_name) or None] # for vat
where_clause_params += [search_name] # for order by
if limit:
query += ' limit %s'
where_clause_params.append(limit)
self.env.cr.execute(query, where_clause_params)
return [row[0] for row in self.env.cr.fetchall()]
return super(Partner, self)._name_search(name, args, operator=operator, limit=limit, name_get_uid=name_get_uid)
def name_search(self, name, args=None, operator='ilike', limit=100):
if args is None:
args = []
if name and operator in ('=', 'ilike', '=ilike', 'like', '=like'):
self.check_access_rights('read')
where_query = self._where_calc(args)
self._apply_ir_rules(where_query, 'read')
from_clause, where_clause, where_clause_params = where_query.get_sql()
from_str = from_clause if from_clause else 'res_partner'
where_str = where_clause and (" WHERE %s AND " % where_clause) or ' WHERE '
# search on the name of the contacts and of its company
search_name = name
if operator in ('ilike', 'like'):
search_name = '%%%s%%' % name
if operator in ('=ilike', '=like'):
operator = operator[1:]
unaccent = get_unaccent_wrapper(self.env.cr)
query = """SELECT res_partner.id
FROM {from_str}
{where} ({email} {operator} {percent}
OR {display_name} {operator} {percent}
OR {reference} {operator} {percent}
OR {vat} {operator} {percent})
-- don't panic, trust postgres bitmap
ORDER BY {display_name} {operator} {percent} desc,
{display_name}
""".format(from_str=from_str,
where=where_str,
operator=operator,
email=unaccent('res_partner.email'),
display_name=unaccent('res_partner.display_name'),
reference=unaccent('res_partner.ref'),
percent=unaccent('%s'),
vat=unaccent('res_partner.vat'),)
where_clause_params += [search_name]*5
if limit:
query += ' limit %s'
where_clause_params.append(limit)
self.env.cr.execute(query, where_clause_params)
partner_ids = [row[0] for row in self.env.cr.fetchall()]
if partner_ids:
result = self.browse(partner_ids).name_get()
if self._context.get('show_address_search'):
# Are you a good coder? then you do not need the following comment ;-)))
# Beginner? Okay, read this:
# Replace \n with comma but ignore empty parts
return map(lambda r: (r[0], ', '.join([p.strip() for p in r[1].split("\n") if p.strip()])), result)
else:
return []
return super(Partner, self).name_search(name, args, operator=operator, limit=limit)
def _get_invoice_matching_query(self, st_lines_with_partner, excluded_ids):
''' Returns the query applying the current invoice_matching reconciliation
model to the provided statement lines.
:param st_lines_with_partner: A list of tuples (statement_line, partner),
associating each statement line to treate with
the corresponding partner, given by the partner map
:param excluded_ids: Account.move.lines to exclude.
:return: (query, params)
'''
self.ensure_one()
if self.rule_type != 'invoice_matching':
raise UserError(_('Programmation Error: Can\'t call _get_invoice_matching_query() for different rules than \'invoice_matching\''))
unaccent = get_unaccent_wrapper(self._cr)
# N.B: 'communication_flag' is there to distinguish invoice matching through the number/reference
# (higher priority) from invoice matching using the partner (lower priority).
query = r'''
SELECT
st_line.id AS id,
aml.id AS aml_id,
aml.currency_id AS aml_currency_id,
aml.date_maturity AS aml_date_maturity,
aml.amount_residual AS aml_amount_residual,
aml.amount_residual_currency AS aml_amount_residual_currency,
''' + self._get_select_communication_flag() + r''' AS communication_flag,
''' + self._get_select_payment_reference_flag() + r''' AS payment_reference_flag
FROM account_bank_statement_line st_line
JOIN account_move st_line_move ON st_line_move.id = st_line.move_id
JOIN res_company company ON company.id = st_line_move.company_id
, account_move_line aml
LEFT JOIN account_move move ON move.id = aml.move_id AND move.state = 'posted'
LEFT JOIN account_account account ON account.id = aml.account_id
LEFT JOIN res_partner aml_partner ON aml.partner_id = aml_partner.id
LEFT JOIN account_payment payment ON payment.move_id = move.id
WHERE
aml.company_id = st_line_move.company_id
AND move.state = 'posted'
AND account.reconcile IS TRUE
AND aml.reconciled IS FALSE
'''
# Add conditions to handle each of the statement lines we want to match
st_lines_queries = []
for st_line, partner in st_lines_with_partner:
# In case we don't have any partner for this line, we try assigning one with the rule mapping
if st_line.amount > 0:
st_line_subquery = r"aml.balance > 0"
else:
st_line_subquery = r"aml.balance < 0"
if self.match_same_currency:
st_line_subquery += r" AND COALESCE(aml.currency_id, company.currency_id) = %s" % (st_line.foreign_currency_id.id or st_line.move_id.currency_id.id)
if partner:
st_line_subquery += r" AND aml.partner_id = %s" % partner.id
else:
st_line_subquery += r"""
AND
(
substring(REGEXP_REPLACE(st_line.payment_ref, '[^0-9\s]', '', 'g'), '\S(?:.*\S)*') != ''
AND
(
(""" + self._get_select_communication_flag() + """)
OR
(""" + self._get_select_payment_reference_flag() + """)
)
)
OR
(
/* We also match statement lines without partners with amls
whose partner's name's parts (splitting on space) are all present
within the payment_ref, in any order, with any characters between them. */
aml_partner.name IS NOT NULL
AND """ + unaccent("st_line.payment_ref") + r""" ~* ('^' || (
SELECT string_agg(concat('(?=.*\m', chunk[1], '\M)'), '')
FROM regexp_matches(""" + unaccent("aml_partner.name") + r""", '\w{3,}', 'g') AS chunk
))
)
"""
st_lines_queries.append(r"st_line.id = %s AND (%s)" % (st_line.id, st_line_subquery))
query += r" AND (%s) " % " OR ".join(st_lines_queries)
params = {}
# If this reconciliation model defines a past_months_limit, we add a condition
# to the query to only search on move lines that are younger than this limit.
if self.past_months_limit:
date_limit = fields.Date.context_today(self) - relativedelta(months=self.past_months_limit)
query += "AND aml.date >= %(aml_date_limit)s"
params['aml_date_limit'] = date_limit
# Filter out excluded account.move.line.
if excluded_ids:
query += 'AND aml.id NOT IN %(excluded_aml_ids)s'
params['excluded_aml_ids'] = tuple(excluded_ids)
if self.matching_order == 'new_first':
query += ' ORDER BY aml_date_maturity DESC, aml_id DESC'
else:
query += ' ORDER BY aml_date_maturity ASC, aml_id ASC'
return query, params