def _check_make_stub_line(self, invoice):
""" Return the dict used to display an invoice/refund in the stub
"""
# Find the account.partial.reconcile which are common to the invoice and the payment
if invoice.type in ['in_invoice', 'out_refund']:
invoice_sign = 1
invoice_payment_reconcile = invoice.move_id.line_ids.mapped('matched_debit_ids').filtered(lambda r: r.debit_move_id in self.move_line_ids)
else:
invoice_sign = -1
invoice_payment_reconcile = invoice.move_id.line_ids.mapped('matched_credit_ids').filtered(lambda r: r.credit_move_id in self.move_line_ids)
if self.currency_id != self.journal_id.company_id.currency_id:
amount_paid = abs(sum(invoice_payment_reconcile.mapped('amount_currency')))
else:
amount_paid = abs(sum(invoice_payment_reconcile.mapped('amount')))
amount_residual = invoice_sign * invoice.residual
return {
'due_date': format_date(self.env, invoice.date_due),
'number': invoice.reference and invoice.number + ' - ' + invoice.reference or invoice.number,
'amount_total': formatLang(self.env, invoice_sign * invoice.amount_total, currency_obj=invoice.currency_id),
'amount_residual': formatLang(self.env, amount_residual, currency_obj=invoice.currency_id) if amount_residual * 10**4 != 0 else '-',
'amount_paid': formatLang(self.env, invoice_sign * amount_paid, currency_obj=invoice.currency_id),
'currency': invoice.currency_id,
}
def _balance_check(self):
for stmt in self:
if not stmt.currency_id.is_zero(stmt.difference):
if stmt.journal_type == 'cash':
if stmt.difference < 0.0:
account = stmt.journal_id.loss_account_id
name = _('Loss')
else:
# statement.difference > 0.0
account = stmt.journal_id.profit_account_id
name = _('Profit')
if not account:
raise UserError(_('There is no account defined on the journal %s for %s involved in a cash difference.') % (stmt.journal_id.name, name))
values = {
'statement_id': stmt.id,
'account_id': account.id,
'amount': stmt.difference,
'name': _("Cash difference observed during the counting (%s)") % name,
}
self.env['account.bank.statement.line'].create(values)
else:
balance_end_real = formatLang(self.env, stmt.balance_end_real, currency_obj=stmt.currency_id)
balance_end = formatLang(self.env, stmt.balance_end, currency_obj=stmt.currency_id)
raise UserError(_('The ending balance is incorrect !\nThe expected balance (%s) is different from the computed one. (%s)')
% (balance_end_real, balance_end))
return True
def _format(self, value):
if self.env.context.get('no_format'):
return value
currency_id = self.env.user.company_id.currency_id
if currency_id.is_zero(value):
# don't print -0.0 in reports
value = abs(value)
return formatLang(self.env, value, currency_obj=currency_id)
def _get_statement_line(self, st_line):
""" Returns the data required by the bank statement reconciliation widget to display a statement line """
statement_currency = st_line.journal_id.currency_id or st_line.journal_id.company_id.currency_id
if st_line.amount_currency and st_line.currency_id:
amount = st_line.amount_currency
amount_currency = st_line.amount
amount_currency_str = formatLang(self.env, abs(amount_currency), currency_obj=statement_currency)
else:
amount = st_line.amount
amount_currency = amount
amount_currency_str = ""
amount_str = formatLang(self.env, abs(amount), currency_obj=st_line.currency_id or statement_currency)
data = {
'id': st_line.id,
'ref': st_line.ref,
'note': st_line.note or "",
'name': st_line.name,
'date': st_line.date,
'amount': amount,
'amount_str': amount_str, # Amount in the statement line currency
'currency_id': st_line.currency_id.id or statement_currency.id,
'partner_id': st_line.partner_id.id,
'journal_id': st_line.journal_id.id,
'statement_id': st_line.statement_id.id,
'account_id': [st_line.journal_id.default_debit_account_id.id, st_line.journal_id.default_debit_account_id.display_name],
'account_code': st_line.journal_id.default_debit_account_id.code,
'account_name': st_line.journal_id.default_debit_account_id.name,
'partner_name': st_line.partner_id.name,
'communication_partner_name': st_line.partner_name,
'amount_currency_str': amount_currency_str, # Amount in the statement currency
'amount_currency': amount_currency, # Amount in the statement currency
'has_no_partner': not st_line.partner_id.id,
'company_id': st_line.company_id.id,
}
if st_line.partner_id:
if st_line.partner_id.supplier and not st_line.partner_id.customer:
data['open_balance_account_id'] = st_line.partner_id.property_account_payable_id.id
elif not st_line.partner_id.supplier and st_line.partner_id.customer:
data['open_balance_account_id'] = st_line.partner_id.property_account_receivable_id.id
else:
data['open_balance_account_id'] = amount > 0 and st_line.partner_id.property_account_receivable_id.id or st_line.partner_id.property_account_payable_id.id
return data
def name_get(self):
result = []
for po in self:
name = po.name
if po.partner_ref:
name += ' (' + po.partner_ref + ')'
if self.env.context.get('show_total_amount') and po.amount_total:
name += ': ' + formatLang(self.env, po.amount_total, currency_obj=po.currency_id)
result.append((po.id, name))
return result
def get_journal_dashboard_datas(self):
res = super(AccountJournal, self).get_journal_dashboard_datas()
#add the number and sum of expenses to pay to the json defining the accounting dashboard data
(query, query_args) = self._get_expenses_to_pay_query()
self.env.cr.execute(query, query_args)
query_results_to_pay = self.env.cr.dictfetchall()
(number_to_pay, sum_to_pay) = self._count_results_and_sum_amounts(query_results_to_pay, self.company_id.currency_id)
res['number_expenses_to_pay'] = number_to_pay
res['sum_expenses_to_pay'] = formatLang(self.env, sum_to_pay or 0.0, currency_obj=self.currency_id or self.company_id.currency_id)
return res
def _get_tax_amount_by_group(self):
self.ensure_one()
res = {}
currency = self.currency_id or self.company_id.currency_id
for line in self.order_line:
for tax in line.tax_id:
group = tax.tax_group_id
res.setdefault(group, 0.0)
res[group] += tax.compute_all(line.price_unit, quantity=line.product_uom_qty)['taxes'][0]['amount']
res = sorted(res.items(), key=lambda l: l[0].sequence)
res = map(lambda l: (l[0].name, formatLang(self.env, l[1], currency_obj=currency)), res)
return res
def _check_build_page_info(self, i, p):
multi_stub = self.company_id.account_check_printing_multi_stub
return {
'sequence_number': self.check_number if (self.journal_id.check_manual_sequencing and self.check_number != 0) else False,
'payment_date': format_date(self.env, self.payment_date),
'partner_id': self.partner_id,
'partner_name': self.partner_id.name,
'currency': self.currency_id,
'state': self.state,
'amount': formatLang(self.env, self.amount, currency_obj=self.currency_id) if i == 0 else 'VOID',
'amount_in_word': self._check_fill_line(self.check_amount_in_words) if i == 0 else 'VOID',
'memo': self.communication,
'stub_cropped': not multi_stub and len(self.invoice_ids) > INV_LINES_PER_STUB,
# If the payment does not reference an invoice, there is no stub line to display
'stub_lines': p,
}
def generate_account_receivable_xls(self):
report_obj = self.env[
'report.flexibite_com_advance.aged_receivable_template']
styleP = xlwt.XFStyle()
stylePC = xlwt.XFStyle()
styleBorder = xlwt.XFStyle()
fontbold = xlwt.XFStyle()
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment_rgt = xlwt.Alignment()
alignment_rgt.horz = xlwt.Alignment.HORZ_RIGHT
font = xlwt.Font()
fontP = xlwt.Font()
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
font.bold = False
fontP.bold = True
styleP.font = font
# stylePC.font = fontP
stylePC.alignment = alignment_rgt
fontbold.alignment = alignment_rgt
styleBorder.font = fontP
fontbold.font = fontP
styleBorder.alignment = alignment
styleBorder.borders = borders
workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("Aged Receivable")
worksheet.col(0).width = 5600
worksheet.col(3).width = 5000
worksheet.write_merge(0,
2,
0,
7,
self.company_id.name + '\n' +
self.company_id.email + '\n' +
self.company_id.phone,
style=styleBorder)
worksheet.write_merge(3, 3, 0, 7, 'Aged Receivable', style=styleBorder)
worksheet.write_merge(4, 4, 0, 0, 'Start Date :', style=styleBorder)
worksheet.write_merge(4,
4,
1,
2,
str(self.start_date),
style=styleBorder)
worksheet.write_merge(4,
4,
3,
3,
"Period Length (days)",
style=styleBorder)
worksheet.write_merge(4,
4,
4,
5,
self.period_length,
style=styleBorder)
worksheet.write_merge(5, 5, 0, 0, "Partner's:", style=styleBorder)
worksheet.write_merge(5,
5,
1,
2,
'Receivable Accounts',
style=styleBorder)
worksheet.write_merge(5, 5, 3, 3, "Target Moves:", style=styleBorder)
worksheet.write_merge(5,
5,
4,
5,
'All Posted Entries' if self.target_move
== 'posted' else 'All Enteries',
style=styleBorder)
worksheet.write_merge(6, 6, 0, 7, style=styleBorder)
period_lenght = report_obj.get_time_interval(str(self.start_date),
self.period_length)
worksheet.write_merge(7, 7, 0, 0, 'Partners', style=styleBorder)
worksheet.write_merge(7, 7, 1, 1, 'Not due', style=styleBorder)
worksheet.write_merge(7,
7,
2,
2,
period_lenght['4']['name'],
style=styleBorder)
worksheet.write_merge(7,
7,
3,
3,
period_lenght['3']['name'],
style=styleBorder)
worksheet.write_merge(7,
7,
4,
4,
period_lenght['2']['name'],
style=styleBorder)
worksheet.write_merge(7,
7,
5,
5,
period_lenght['1']['name'],
style=styleBorder)
worksheet.write_merge(7,
7,
6,
6,
period_lenght['0']['name'],
style=styleBorder)
worksheet.write_merge(7, 7, 7, 7, 'Total', style=styleBorder)
accont_moveline, total, dummy = report_obj._get_partner_move_lines_custom(
['receivable'], str(self.start_date), self.target_move,
self.period_length)
if total:
worksheet.write_merge(8, 8, 0, 0, 'Account Total')
worksheet.write_merge(
8,
8,
1,
1,
formatLang(self.env,
float(total[6]),
currency_obj=self.env.user.company_id.currency_id),
style=fontbold)
worksheet.write_merge(
8,
8,
2,
2,
formatLang(self.env,
float(total[4]),
currency_obj=self.env.user.company_id.currency_id),
style=fontbold)
worksheet.write_merge(
8,
8,
3,
3,
formatLang(self.env,
float(total[3]),
currency_obj=self.env.user.company_id.currency_id),
style=fontbold)
worksheet.write_merge(
8,
8,
4,
4,
formatLang(self.env,
float(total[2]),
currency_obj=self.env.user.company_id.currency_id),
style=fontbold)
worksheet.write_merge(
8,
8,
5,
5,
formatLang(self.env,
float(total[1]),
currency_obj=self.env.user.company_id.currency_id),
style=fontbold)
worksheet.write_merge(
8,
8,
6,
6,
formatLang(self.env,
float(total[0]),
currency_obj=self.env.user.company_id.currency_id),
style=fontbold)
worksheet.write_merge(
8,
8,
7,
7,
formatLang(self.env,
float(total[5]),
currency_obj=self.env.user.company_id.currency_id),
style=fontbold)
i = 9
for each in accont_moveline:
worksheet.write_merge(i, i, 0, 0, each['name'])
worksheet.write_merge(
i, i, 1, 1,
formatLang(self.env,
float(each['direction']),
currency_obj=self.env.user.company_id.currency_id),
stylePC)
worksheet.write_merge(
i, i, 2, 2,
formatLang(self.env,
float(each['4']),
currency_obj=self.env.user.company_id.currency_id),
stylePC)
worksheet.write_merge(
i, i, 3, 3,
formatLang(self.env,
float(each['3']),
currency_obj=self.env.user.company_id.currency_id),
stylePC)
worksheet.write_merge(
i, i, 4, 4,
formatLang(self.env,
float(each['2']),
currency_obj=self.env.user.company_id.currency_id),
stylePC)
worksheet.write_merge(
i, i, 5, 5,
formatLang(self.env,
float(each['1']),
currency_obj=self.env.user.company_id.currency_id),
stylePC)
worksheet.write_merge(
i, i, 6, 6,
formatLang(self.env,
float(each['1']),
currency_obj=self.env.user.company_id.currency_id),
stylePC)
worksheet.write_merge(
i, i, 7, 7,
formatLang(self.env,
float(each['total']),
currency_obj=self.env.user.company_id.currency_id),
stylePC)
i = i + 1
file_data = io.BytesIO()
workbook.save(file_data)
self.write({
'state': 'get',
'data': base64.encodestring(file_data.getvalue()),
'name': 'aged_receivable.xls'
})
return {
'name': 'Aged Receivable',
'type': 'ir.actions.act_window',
'res_model': 'aged.receivable',
'view_mode': 'form',
'view_type': 'form',
'res_id': self.id,
'target': 'new'
}
# vim:expandtab:smartindent:tabstop=4
def get_journal_dashboard_datas(self):
currency = self.currency_id or self.company_id.currency_id
number_to_reconcile = last_balance = account_sum = 0
title = ''
number_draft = number_waiting = number_late = 0
sum_draft = sum_waiting = sum_late = 0.0
if self.type in ['bank', 'cash']:
last_bank_stmt = self.env['account.bank.statement'].search(
[('journal_id', 'in', self.ids)],
order="date desc, id desc",
limit=1)
last_balance = last_bank_stmt and last_bank_stmt[0].balance_end or 0
#Get the number of items to reconcile for that bank journal
self.env.cr.execute(
"""SELECT COUNT(DISTINCT(line.id))
FROM account_bank_statement_line AS line
LEFT JOIN account_bank_statement AS st
ON line.statement_id = st.id
WHERE st.journal_id IN %s AND st.state = 'open'
AND not exists (select 1 from account_move_line aml where aml.statement_line_id = line.id)
""", (tuple(self.ids), ))
number_to_reconcile = self.env.cr.fetchone()[0]
# optimization to read sum of balance from account_move_line
account_ids = tuple(ac for ac in [
self.default_debit_account_id.id,
self.default_credit_account_id.id
] if ac)
if account_ids:
amount_field = 'balance' if not self.currency_id else 'amount_currency'
query = """SELECT sum(%s) FROM account_move_line WHERE account_id in %%s;""" % (
amount_field, )
self.env.cr.execute(query, (account_ids, ))
query_results = self.env.cr.dictfetchall()
if query_results and query_results[0].get('sum') != None:
account_sum = query_results[0].get('sum')
#TODO need to check if all invoices are in the same currency than the journal!!!!
elif self.type in ['sale', 'purchase']:
title = _('Bills to pay') if self.type == 'purchase' else _(
'Invoices owed to you')
(query, query_args) = self._get_open_bills_to_pay_query()
self.env.cr.execute(query, query_args)
query_results_to_pay = self.env.cr.dictfetchall()
(query, query_args) = self._get_draft_bills_query()
self.env.cr.execute(query, query_args)
query_results_drafts = self.env.cr.dictfetchall()
today = datetime.today()
query = """SELECT amount_total, currency_id AS currency, type FROM account_invoice WHERE journal_id = %s AND date < %s AND state = 'open';"""
self.env.cr.execute(query, (self.id, today))
late_query_results = self.env.cr.dictfetchall()
(number_waiting,
sum_waiting) = self._count_results_and_sum_amounts(
query_results_to_pay, currency)
(number_draft, sum_draft) = self._count_results_and_sum_amounts(
query_results_drafts, currency)
(number_late, sum_late) = self._count_results_and_sum_amounts(
late_query_results, currency)
return {
'number_to_reconcile':
number_to_reconcile,
'account_balance':
formatLang(self.env,
account_sum,
currency_obj=self.currency_id
or self.company_id.currency_id),
'last_balance':
formatLang(self.env,
last_balance,
currency_obj=self.currency_id
or self.company_id.currency_id),
'difference': (last_balance - account_sum) and formatLang(
self.env,
last_balance - account_sum,
currency_obj=self.currency_id or self.company_id.currency_id)
or False,
'number_draft':
number_draft,
'number_waiting':
number_waiting,
'number_late':
number_late,
'sum_draft':
formatLang(self.env,
sum_draft or 0.0,
currency_obj=self.currency_id
or self.company_id.currency_id),
'sum_waiting':
formatLang(self.env,
sum_waiting or 0.0,
currency_obj=self.currency_id
or self.company_id.currency_id),
'sum_late':
formatLang(self.env,
sum_late or 0.0,
currency_obj=self.currency_id
or self.company_id.currency_id),
'currency_id':
self.currency_id and self.currency_id.id
or self.company_id.currency_id.id,
'bank_statements_source':
self.bank_statements_source,
'title':
title,
}
def _prepare_move_lines(self,
move_lines,
target_currency=False,
target_date=False):
""" Returns move lines formatted for the manual/bank reconciliation widget
:param move_line_ids:
:param target_currency: currency (browse) you want the move line debit/credit converted into
:param target_date: date to use for the monetary conversion
"""
context = dict(self._context or {})
ret = []
for line in move_lines:
company_currency = line.account_id.company_id.currency_id
line_currency = (line.currency_id and line.amount_currency
) and line.currency_id or company_currency
ret_line = {
'id':
line.id,
'name':
line.name and line.name != '/'
and line.move_id.name + ': ' + line.name or line.move_id.name,
'ref':
line.move_id.ref or '',
# For reconciliation between statement transactions and already registered payments (eg. checks)
# NB : we don't use the 'reconciled' field because the line we're selecting is not the one that gets reconciled
'account_id':
[line.account_id.id, line.account_id.display_name],
'already_paid':
line.account_id.internal_type == 'liquidity',
'account_code':
line.account_id.code,
'account_name':
line.account_id.name,
'account_type':
line.account_id.internal_type,
'date_maturity':
line.date_maturity,
'date':
line.date,
'journal_id':
[line.journal_id.id, line.journal_id.display_name],
'partner_id':
line.partner_id.id,
'partner_name':
line.partner_id.name,
'currency_id':
line_currency.id,
}
debit = line.debit
credit = line.credit
amount = line.amount_residual
amount_currency = line.amount_residual_currency
# For already reconciled lines, don't use amount_residual(_currency)
if line.account_id.internal_type == 'liquidity':
amount = debit - credit
amount_currency = line.amount_currency
target_currency = target_currency or company_currency
# Use case:
# Let's assume that company currency is in USD and that we have the 3 following move lines
# Debit Credit Amount currency Currency
# 1) 25 0 0 NULL
# 2) 17 0 25 EUR
# 3) 33 0 25 YEN
#
# If we ask to see the information in the reconciliation widget in company currency, we want to see
# The following information
# 1) 25 USD (no currency information)
# 2) 17 USD [25 EUR] (show 25 euro in currency information, in the little bill)
# 3) 33 USD [25 YEN] (show 25 yen in currency information)
#
# If we ask to see the information in another currency than the company let's say EUR
# 1) 35 EUR [25 USD]
# 2) 25 EUR (no currency information)
# 3) 50 EUR [25 YEN]
# In that case, we have to convert the debit-credit to the currency we want and we show next to it
# the value of the amount_currency or the debit-credit if no amount currency
if target_currency == company_currency:
if line_currency == target_currency:
amount = amount
amount_currency = ""
total_amount = debit - credit
total_amount_currency = ""
else:
amount = amount
amount_currency = amount_currency
total_amount = debit - credit
total_amount_currency = line.amount_currency
if target_currency != company_currency:
if line_currency == target_currency:
amount = amount_currency
amount_currency = ""
total_amount = line.amount_currency
total_amount_currency = ""
else:
amount_currency = line.currency_id and amount_currency or amount
company = line.account_id.company_id
date = target_date or line.date
amount = company_currency._convert(amount, target_currency,
company, date)
total_amount = company_currency._convert(
(line.debit - line.credit), target_currency, company,
date)
total_amount_currency = line.currency_id and line.amount_currency or (
line.debit - line.credit)
ret_line['debit'] = amount > 0 and amount or 0
ret_line['credit'] = amount < 0 and -amount or 0
ret_line['amount_currency'] = amount_currency
ret_line['amount_str'] = formatLang(self.env,
abs(amount),
currency_obj=target_currency)
ret_line['total_amount_str'] = formatLang(
self.env, abs(total_amount), currency_obj=target_currency)
ret_line['amount_currency_str'] = amount_currency and formatLang(
self.env, abs(amount_currency),
currency_obj=line_currency) or ""
ret_line[
'total_amount_currency_str'] = total_amount_currency and formatLang(
self.env,
abs(total_amount_currency),
currency_obj=line_currency) or ""
ret.append(ret_line)
return ret
def get_followup_table_html(self):
""" Build the html tables to be included in emails send to partners,
when reminding them their overdue invoices.
:param ids: [id] of the partner for whom we are building the tables
:rtype: string
"""
self.ensure_one()
partner = self.commercial_partner_id
# copy the context to not change global context.
# Overwrite it because _() looks for the
# lang in local variable 'context'.
# Set the language to use = the partner language
followup_table = ''
if partner.unreconciled_aml_ids:
company = self.env.user.company_id
current_date = fields.Date.today()
report = self.env['report.devit_account_followup.report_followup']
final_res = report._lines_get_with_partner(partner, company.id)
for currency_dict in final_res:
currency = currency_dict.get(
'line', [{
'currency_id': company.currency_id
}])[0]['currency_id']
followup_table += '''
<table border="2" width=100%%>
<tr>
<td>''' + _("Invoice Date") + '''</td>
<td>''' + _("Description") + '''</td>
<td>''' + _("Reference") + '''</td>
<td>''' + _("Due Date") + '''</td>
<td>''' + _(
"Amount") + " (%s)" % (currency.symbol) + '''</td>
<td>''' + _("Lit.") + '''</td>
</tr>
'''
total = 0
for aml in currency_dict['line']:
block = aml['blocked'] and 'X' or ' '
total += aml['balance']
strbegin = "<TD>"
strend = "</TD>"
date = aml['date_maturity'] or aml['date']
if date <= current_date and aml['balance'] > 0:
strbegin = "<TD><B>"
strend = "</B></TD>"
followup_table += "<TR>" + strbegin + str(aml['date']) + \
strend + strbegin + aml['name'] + \
strend + strbegin + \
(aml['ref'] or '') + strend + \
strbegin + str(date) + strend + \
strbegin + str(aml['balance']) + \
strend + strbegin + block + \
strend + "</TR>"
total = reduce(lambda x, y: x + y['balance'],
currency_dict['line'], 0.00)
total = formatLang(self.env, total, currency_obj=currency)
followup_table += '''<tr> </tr>
</table>
<center>''' + _(
"Amount due") + ''' : %s </center>''' % (total)
return followup_table
def get_journal_dashboard_datas(self):
currency = self.currency_id or self.company_id.currency_id
number_to_reconcile = last_balance = account_sum = 0
ac_bnk_stmt = []
title = ''
number_draft = number_waiting = number_late = 0
sum_draft = sum_waiting = sum_late = 0.0
if self.type in ['bank', 'cash']:
last_bank_stmt = self.env['account.bank.statement'].search([('journal_id', 'in', self.ids)], order="date desc, id desc", limit=1)
last_balance = last_bank_stmt and last_bank_stmt[0].balance_end or 0
#Get the number of items to reconcile for that bank journal
self.env.cr.execute("""SELECT COUNT(DISTINCT(statement_line_id))
FROM account_move where statement_line_id
IN (SELECT line.id
FROM account_bank_statement_line AS line
LEFT JOIN account_bank_statement AS st
ON line.statement_id = st.id
WHERE st.journal_id IN %s and st.state = 'open')""", (tuple(self.ids),))
already_reconciled = self.env.cr.fetchone()[0]
self.env.cr.execute("""SELECT COUNT(line.id)
FROM account_bank_statement_line AS line
LEFT JOIN account_bank_statement AS st
ON line.statement_id = st.id
WHERE st.journal_id IN %s and st.state = 'open'""", (tuple(self.ids),))
all_lines = self.env.cr.fetchone()[0]
number_to_reconcile = all_lines - already_reconciled
# optimization to read sum of balance from account_move_line
account_ids = tuple(filter(None, [self.default_debit_account_id.id, self.default_credit_account_id.id]))
if account_ids:
amount_field = 'balance' if (not self.currency_id or self.currency_id == self.company_id.currency_id) else 'amount_currency'
query = """SELECT sum(%s) FROM account_move_line WHERE account_id in %%s AND date <= %%s;""" % (amount_field,)
self.env.cr.execute(query, (account_ids, fields.Date.today(),))
query_results = self.env.cr.dictfetchall()
if query_results and query_results[0].get('sum') != None:
account_sum = query_results[0].get('sum')
#TODO need to check if all invoices are in the same currency than the journal!!!!
elif self.type in ['sale', 'purchase']:
title = _('Bills to pay') if self.type == 'purchase' else _('Invoices owed to you')
# optimization to find total and sum of invoice that are in draft, open state
query = """SELECT state, amount_total, currency_id AS currency, type FROM account_invoice WHERE journal_id = %s AND state NOT IN ('paid', 'cancel');"""
self.env.cr.execute(query, (self.id,))
query_results = self.env.cr.dictfetchall()
today = datetime.today()
query = """SELECT amount_total, currency_id AS currency, type FROM account_invoice WHERE journal_id = %s AND date < %s AND state = 'open';"""
self.env.cr.execute(query, (self.id, today))
late_query_results = self.env.cr.dictfetchall()
for result in query_results:
if result['type'] in ['in_refund', 'out_refund']:
factor = -1
else:
factor = 1
cur = self.env['res.currency'].browse(result.get('currency'))
if result.get('state') in ['draft', 'proforma', 'proforma2']:
number_draft += 1
sum_draft += cur.compute(result.get('amount_total'), currency) * factor
elif result.get('state') == 'open':
number_waiting += 1
sum_waiting += cur.compute(result.get('amount_total'), currency) * factor
for result in late_query_results:
if result['type'] in ['in_refund', 'out_refund']:
factor = -1
else:
factor = 1
cur = self.env['res.currency'].browse(result.get('currency'))
number_late += 1
sum_late += cur.compute(result.get('amount_total'), currency) * factor
difference = currency.round(last_balance-account_sum) + 0.0
return {
'number_to_reconcile': number_to_reconcile,
'account_balance': formatLang(self.env, currency.round(account_sum) + 0.0, currency_obj=currency),
'last_balance': formatLang(self.env, currency.round(last_balance) + 0.0, currency_obj=currency),
'difference': formatLang(self.env, difference, currency_obj=currency) if difference else False,
'number_draft': number_draft,
'number_waiting': number_waiting,
'number_late': number_late,
'sum_draft': formatLang(self.env, currency.round(sum_draft) + 0.0, currency_obj=currency),
'sum_waiting': formatLang(self.env, currency.round(sum_waiting) + 0.0, currency_obj=currency),
'sum_late': formatLang(self.env, currency.round(sum_late) + 0.0, currency_obj=currency),
'currency_id': currency.id,
'bank_statements_source': self.bank_statements_source,
'title': title,
}
def number_format(self, currency_id, amount):
return formatLang(self.env, amount, currency_obj=currency_id,
digits=0).replace(",", ".")
def get_journal_dashboard_datas(self):
currency = self.currency_id or self.company_id.currency_id
number_to_reconcile = number_to_check = last_balance = 0
has_at_least_one_statement = False
bank_account_balance = nb_lines_bank_account_balance = 0
outstanding_pay_account_balance = nb_lines_outstanding_pay_account_balance = 0
title = ''
number_draft = number_waiting = number_late = to_check_balance = 0
sum_draft = sum_waiting = sum_late = 0.0
if self.type in ('bank', 'cash'):
last_statement = self._get_last_bank_statement(
domain=[('move_id.state', '=', 'posted')])
last_balance = last_statement.balance_end
has_at_least_one_statement = bool(last_statement)
bank_account_balance, nb_lines_bank_account_balance = self._get_journal_bank_account_balance(
domain=[('move_id.state', '=', 'posted')])
outstanding_pay_account_balance, nb_lines_outstanding_pay_account_balance = self._get_journal_outstanding_payments_account_balance(
domain=[('move_id.state', '=', 'posted')])
self._cr.execute(
'''
SELECT COUNT(st_line.id)
FROM account_bank_statement_line st_line
JOIN account_move st_line_move ON st_line_move.id = st_line.move_id
JOIN account_bank_statement st ON st_line.statement_id = st.id
WHERE st_line_move.journal_id IN %s
AND st.state = 'posted'
AND NOT st_line.is_reconciled
''', [tuple(self.ids)])
number_to_reconcile = self.env.cr.fetchone()[0]
to_check_ids = self.to_check_ids()
number_to_check = len(to_check_ids)
to_check_balance = sum([r.amount for r in to_check_ids])
#TODO need to check if all invoices are in the same currency than the journal!!!!
elif self.type in ['sale', 'purchase']:
title = _('Bills to pay') if self.type == 'purchase' else _(
'Invoices owed to you')
self.env['account.move'].flush([
'amount_residual', 'currency_id', 'move_type', 'invoice_date',
'company_id', 'journal_id', 'date', 'state', 'payment_state'
])
(query, query_args) = self._get_open_bills_to_pay_query()
self.env.cr.execute(query, query_args)
query_results_to_pay = self.env.cr.dictfetchall()
(query, query_args) = self._get_draft_bills_query()
self.env.cr.execute(query, query_args)
query_results_drafts = self.env.cr.dictfetchall()
today = fields.Date.context_today(self)
query = '''
SELECT
(CASE WHEN move_type IN ('out_refund', 'in_refund') THEN -1 ELSE 1 END) * amount_residual AS amount_total,
currency_id AS currency,
move_type,
invoice_date,
company_id
FROM account_move move
WHERE journal_id = %s
AND date <= %s
AND state = 'posted'
AND payment_state in ('not_paid', 'partial')
AND move_type IN ('out_invoice', 'out_refund', 'in_invoice', 'in_refund', 'out_receipt', 'in_receipt');
'''
self.env.cr.execute(query, (self.id, today))
late_query_results = self.env.cr.dictfetchall()
curr_cache = {}
(number_waiting,
sum_waiting) = self._count_results_and_sum_amounts(
query_results_to_pay, currency, curr_cache=curr_cache)
(number_draft, sum_draft) = self._count_results_and_sum_amounts(
query_results_drafts, currency, curr_cache=curr_cache)
(number_late, sum_late) = self._count_results_and_sum_amounts(
late_query_results, currency, curr_cache=curr_cache)
read = self.env['account.move'].read_group(
[('journal_id', '=', self.id),
('to_check', '=', True)], ['amount_total'],
'journal_id',
lazy=False)
if read:
number_to_check = read[0]['__count']
to_check_balance = read[0]['amount_total']
elif self.type == 'general':
read = self.env['account.move'].read_group(
[('journal_id', '=', self.id),
('to_check', '=', True)], ['amount_total'],
'journal_id',
lazy=False)
if read:
number_to_check = read[0]['__count']
to_check_balance = read[0]['amount_total']
is_sample_data = self.kanban_dashboard_graph and any(
data.get('is_sample_data', False)
for data in json.loads(self.kanban_dashboard_graph))
return {
'number_to_check':
number_to_check,
'to_check_balance':
formatLang(self.env, to_check_balance, currency_obj=currency),
'number_to_reconcile':
number_to_reconcile,
'account_balance':
formatLang(self.env,
currency.round(bank_account_balance),
currency_obj=currency),
'has_at_least_one_statement':
has_at_least_one_statement,
'nb_lines_bank_account_balance':
nb_lines_bank_account_balance,
'outstanding_pay_account_balance':
formatLang(self.env,
currency.round(outstanding_pay_account_balance),
currency_obj=currency),
'nb_lines_outstanding_pay_account_balance':
nb_lines_outstanding_pay_account_balance,
'last_balance':
formatLang(self.env,
currency.round(last_balance) + 0.0,
currency_obj=currency),
'number_draft':
number_draft,
'number_waiting':
number_waiting,
'number_late':
number_late,
'sum_draft':
formatLang(self.env,
currency.round(sum_draft) + 0.0,
currency_obj=currency),
'sum_waiting':
formatLang(self.env,
currency.round(sum_waiting) + 0.0,
currency_obj=currency),
'sum_late':
formatLang(self.env,
currency.round(sum_late) + 0.0,
currency_obj=currency),
'currency_id':
currency.id,
'bank_statements_source':
self.bank_statements_source,
'title':
title,
'is_sample_data':
is_sample_data,
'company_count':
len(self.env.companies)
}
def _get_profitability_items(self):
if not self.user_has_groups('project.group_project_manager'):
return {'data': []}
timesheets = self.allow_timesheets and self.user_has_groups(
'hr_timesheet.group_hr_timesheet_user')
profitability = self._get_profitability_common(
costs_revenues=self.allow_billable, timesheets=timesheets)
data = []
if timesheets:
data += [{
'name':
_("Timesheets"),
'value':
self.env.ref(
'sale_timesheet.project_profitability_timesheet_panel').
_render(
{
'timesheet_unit_amount':
float_round(profitability['timesheet_unit_amount'],
precision_digits=2),
'timesheet_uom':
self.env.company._timesheet_uom_text(),
'is_timesheet_uom_hour':
self.env.company._is_timesheet_hour_uom(),
'percentage_billable':
formatLang(
self.env,
profitability['timesheet_percentage_billable'],
digits=0),
},
engine='ir.qweb'),
}]
if self.allow_billable:
margin_color = False
if not float_is_zero(profitability['margin'], precision_digits=0):
margin_color = profitability['margin'] > 0 and 'green' or 'red'
data += [{
'name':
_("Revenues"),
'value':
format_amount(self.env, profitability['revenues'],
self.env.company.currency_id)
}, {
'name':
_("Costs"),
'value':
format_amount(self.env, profitability['costs'],
self.env.company.currency_id)
}, {
'name':
_("Margin"),
'color':
margin_color,
'value':
format_amount(self.env, profitability['margin'],
self.env.company.currency_id)
}]
return {
'action': self.allow_billable and self.allow_timesheets
and "action_view_timesheet",
'allow_billable': self.allow_billable,
'data': data,
}
def _get_lines(self, options, line_id=None):
lines = []
context = self.env.context
if not context.get('company_ids'):
return lines
tag_ids = [
self.env['ir.model.data'].xmlid_to_res_id(k) for k in [
'l10n_de.tag_de_intracom_community_delivery',
'l10n_de.tag_de_intracom_community_supplies',
'l10n_de.tag_de_intracom_ABC'
]
]
query = """
SELECT p.name As partner_name, l.partner_id AS partner_id, p.vat AS vat,
tt.account_account_tag_id AS intra_code, SUM(-l.balance) AS amount,
c.code AS partner_country
FROM account_move_line l
LEFT JOIN account_move m ON m.id = l.move_id
LEFT JOIN res_partner p ON l.partner_id = p.id
LEFT JOIN account_move_line_account_tax_rel amlt ON l.id = amlt.account_move_line_id
LEFT JOIN account_tax_account_tag tt on amlt.account_tax_id = tt.account_tax_id
LEFT JOIN res_country c ON p.country_id = c.id
WHERE tt.account_account_tag_id IN %s
AND l.date >= %s
AND l.date <= %s
AND l.company_id IN %s
AND m.state = 'posted'
GROUP BY p.name, l.partner_id, p.vat, intra_code, partner_country
"""
params = (tuple(tag_ids), context.get('date_from'),
context.get('date_to'), tuple(context.get('company_ids')))
self.env.cr.execute(query, params)
for row in self.env.cr.dictfetchall():
if not row['vat']:
row['vat'] = ''
amt = row['amount'] or 0.0
if amt:
if row['intra_code'] == tag_ids[0]:
code = ''
elif row['intra_code'] == tag_ids[1]:
code = 1
else:
code = 2
columns = [
row['partner_country'],
row['vat'].replace(' ', '').upper(), amt, code
]
if not context.get('no_format', False):
currency_id = self.env.user.company_id.currency_id
columns[2] = formatLang(self.env,
columns[2],
currency_obj=currency_id)
lines.append({
'id': row['partner_id'],
'caret_options': 'res.partner',
'model': 'res.partner',
'name': row['partner_name'],
'columns': [{
'name': v
} for v in columns],
'unfoldable': False,
'unfolded': False,
})
return lines
def get_journal_dashboard_datas(self):
res = super(AccountJournal, self).get_journal_dashboard_datas()
company = self.company_id
currency = self.currency_id or self.company_id.currency_id
report = self.settlement_financial_report_id
report_position = 0.0
if report:
currency_table = {}
# para el caso donde el usuario esta en una cia con moneda
# distinta a la moneda de la cia del diario
used_currency = self.env.user.company_id.currency_id
# no importa la del diario, solo la de la cia del diario
if company.currency_id != used_currency:
currency_table[company.currency_id.id] = (
used_currency.rate / company.currency_id.rate)
date_from = datetime.date.today().replace(day=1)
date_to = date_from + relativedelta(months=1, days=-1)
to_string = fields.Date.to_string
report = report.with_context(date_from=to_string(date_from),
date_to=to_string(date_to))
# si calculamos con financial reports los importes estan en la
# moneda del usuario
currency = used_currency
report_lines = report.line_ids.search([
('id', 'child_of', report.line_ids.ids),
('settlement_type', '!=', False)
])
report_position = sum([
x['balance'] for x in report_lines.get_balance(
{}, currency_table, report, field_names=['balance'])
])
# report_position = sum(
# [x['balance'] for x in report.line_ids.get_balance(
# {}, currency_table, report,
# field_names=['balance'])])
# TODO hacer por sql por temas de performance?
unsettled_lines = self._get_tax_settlement_move_lines_by_tags()
tax_lines = self.env['account.move.line'].search(
self._get_tax_settlement_lines_domain_by_tags_accounts())
res.update({
# los importes estan en la moneda de la cia, sin importar el diario
'report_position':
formatLang(self.env, report_position, currency_obj=currency),
'unsettled_count':
len(unsettled_lines),
'unsettled_amount':
formatLang(self.env,
-sum(unsettled_lines.mapped('balance')),
currency_obj=currency),
'tax_balance':
formatLang(self.env,
-sum(tax_lines.mapped('balance')),
currency_obj=currency),
'debit_amount':
formatLang(self.env,
self.settlement_partner_id.debit,
currency_obj=currency),
})
return res
def _get_lines(self, options, line_id=None):
"""
Override
Compute and return the lines of the columns of the follow-ups report.
"""
# Get date format for the lang
partner = options.get('partner_id') and self.env['res.partner'].browse(options['partner_id']) or False
if not partner:
return []
lang_code = partner.lang or self.env.user.lang or 'en_US'
lines = []
res = {}
today = fields.Date.today()
line_num = 0
for l in partner.unreconciled_aml_ids.filtered(lambda l: l.company_id == self.env.user.company_id):
if l.company_id == self.env.user.company_id:
if self.env.context.get('print_mode') and l.blocked:
continue
currency = l.currency_id or l.company_id.currency_id
if currency not in res:
res[currency] = []
res[currency].append(l)
for currency, aml_recs in res.items():
total = 0
total_issued = 0
for aml in aml_recs:
amount = aml.currency_id and aml.amount_residual_currency or aml.amount_residual
date_due = format_date(self.env, aml.date_maturity or aml.date, lang_code=lang_code)
total += not aml.blocked and amount or 0
is_overdue = today > aml.date_maturity if aml.date_maturity else today > aml.date
is_payment = aml.payment_id
if is_overdue or is_payment:
total_issued += not aml.blocked and amount or 0
if is_overdue:
date_due = {'name': date_due, 'class': 'color-red date', 'style': 'white-space:nowrap;text-align:center;color: red;'}
if is_payment:
date_due = ''
# import ipdb; ipdb.set_trace()
# move_line_name = aml.invoice_id.name or aml.name
# move_line_name = str(aml.invoice_id.fe_Serie) + '-' + str(aml.invoice_id.fe_DocNro)
move_line_name = 'Factura'
if self.env.context.get('print_mode'):
move_line_name = {'name': move_line_name, 'style': 'text-align:right; white-space:normal;'}
amount = formatLang(self.env, amount, currency_obj=currency)
line_num += 1
expected_pay_date = format_date(self.env, aml.expected_pay_date, lang_code=lang_code) if aml.expected_pay_date else ''
columns = [
format_date(self.env, aml.date, lang_code=lang_code),
date_due,
aml.invoice_id.origin,
move_line_name,
expected_pay_date + ' ' + (aml.internal_note or ''),
{'name': aml.blocked, 'blocked': aml.blocked},
amount,
]
if self.env.context.get('print_mode'):
columns = columns[:4] + columns[6:]
lines.append({
'id': aml.id,
'invoice_id': aml.invoice_id.id,
'view_invoice_id': self.env['ir.model.data'].get_object_reference('account', 'invoice_form')[1],
'account_move': aml.move_id,
# 'name': aml.move_id.name,
'name': str(aml.invoice_id.fe_Serie) + '-' + str(aml.invoice_id.fe_DocNro) if aml.invoice_id.type == 'out_invoice' or aml.invoice_id.type == 'out_refund' else aml.name,
'caret_options': 'followup',
'move_id': aml.move_id.id,
'type': is_payment and 'payment' or 'unreconciled_aml',
'unfoldable': False,
'has_invoice': bool(aml.invoice_id),
'columns': [type(v) == dict and v or {'name': v} for v in columns],
})
total_due = formatLang(self.env, total, currency_obj=currency)
line_num += 1
lines.append({
'id': line_num,
'name': '',
'class': 'total',
'unfoldable': False,
'level': 0,
'columns': [{'name': v} for v in [''] * (3 if self.env.context.get('print_mode') else 5) + [total >= 0 and _('Total Due') or '', total_due]],
})
if total_issued > 0:
total_issued = formatLang(self.env, total_issued, currency_obj=currency)
line_num += 1
lines.append({
'id': line_num,
'name': '',
'class': 'total',
'unfoldable': False,
'level': 0,
'columns': [{'name': v} for v in [''] * (3 if self.env.context.get('print_mode') else 5) + [_('Total Overdue'), total_issued]],
})
# Add an empty line after the total to make a space between two currencies
line_num += 1
lines.append({
'id': line_num,
'name': '',
'class': '',
'unfoldable': False,
'level': 0,
'columns': [{} for col in columns],
})
# Remove the last empty line
if lines:
lines.pop()
return lines
def _compute_move_vals(self):
def _get_aml_vals(order,
balance,
amount_currency,
account_id,
label=""):
if not is_purchase:
balance *= -1
amount_currency *= -1
values = {
'name': label,
'debit': balance if balance > 0 else 0.0,
'credit': balance * -1 if balance < 0 else 0.0,
'account_id': account_id,
}
if len(order
) == 1 and self.company_id.currency_id != order.currency_id:
values.update({
'amount_currency': amount_currency,
'currency_id': order.currency_id.id,
})
return values
def _ellipsis(string, size):
if len(string) > size:
return string[0:size - 3] + '...'
return string
self.ensure_one()
move_lines = []
is_purchase = self.env.context.get('active_model') == 'purchase.order'
orders = self.env[self._context['active_model']].with_company(
self.company_id).browse(self._context['active_ids'])
if orders.filtered(lambda o: o.company_id != self.company_id):
raise UserError(
_('Entries can only be created for a single company at a time.'
))
orders_with_entries = []
fnames = []
total_balance = 0.0
for order in orders:
if len(orders) == 1 and self.amount:
total_balance = self.amount
order_line = order.order_line[0]
if is_purchase:
account = order_line.product_id.property_account_expense_id or order_line.product_id.categ_id.property_account_expense_categ_id
else:
account = order_line.product_id.property_account_income_id or order_line.product_id.categ_id.property_account_income_categ_id
values = _get_aml_vals(order,
self.amount,
0,
account.id,
label=_('Manual entry'))
move_lines.append(Command.create(values))
else:
other_currency = self.company_id.currency_id != order.currency_id
rate = order.currency_id._get_rates(
self.company_id, self.date).get(
order.currency_id.id) if other_currency else 1.0
# create a virtual order that will allow to recompute the qty delivered/received (and dependancies)
# without actually writing anything on the real record (field is computed and stored)
o = order.new(origin=order)
if is_purchase:
o.order_line.with_context(
accrual_entry_date=self.date)._compute_qty_received()
o.order_line.with_context(
accrual_entry_date=self.date)._compute_qty_invoiced()
else:
o.order_line.with_context(
accrual_entry_date=self.date)._compute_qty_delivered()
o.order_line.with_context(
accrual_entry_date=self.date)._compute_qty_invoiced()
o.order_line.with_context(
accrual_entry_date=self.date
)._compute_untaxed_amount_invoiced()
o.order_line.with_context(
accrual_entry_date=self.date)._get_to_invoice_qty()
lines = o.order_line.filtered(lambda l: fields.Float.compare(
l.qty_to_invoice,
0,
precision_digits=l.product_uom.rounding,
) == 1)
for order_line in lines:
if is_purchase:
account = order_line.product_id.property_account_expense_id or order_line.product_id.categ_id.property_account_expense_categ_id
amount = self.company_id.currency_id.round(
order_line.qty_to_invoice * order_line.price_unit /
rate)
amount_currency = order_line.currency_id.round(
order_line.qty_to_invoice * order_line.price_unit)
fnames = [
'qty_to_invoice', 'qty_received', 'qty_invoiced',
'invoice_lines'
]
label = _(
'%s - %s; %s Billed, %s Received at %s each',
order.name, _ellipsis(order_line.name, 20),
order_line.qty_invoiced, order_line.qty_received,
formatLang(self.env,
order_line.price_unit,
currency_obj=order.currency_id))
else:
account = order_line.product_id.property_account_income_id or order_line.product_id.categ_id.property_account_income_categ_id
amount = self.company_id.currency_id.round(
order_line.untaxed_amount_to_invoice / rate)
amount_currency = order_line.untaxed_amount_to_invoice
fnames = [
'qty_to_invoice', 'untaxed_amount_to_invoice',
'qty_invoiced', 'qty_delivered', 'invoice_lines'
]
label = _(
'%s - %s; %s Invoiced, %s Delivered at %s each',
order.name, _ellipsis(order_line.name, 20),
order_line.qty_invoiced, order_line.qty_delivered,
formatLang(self.env,
order_line.price_unit,
currency_obj=order.currency_id))
values = _get_aml_vals(order,
amount,
amount_currency,
account.id,
label=label)
move_lines.append(Command.create(values))
total_balance += amount
# must invalidate cache or o can mess when _create_invoices().action_post() of original order after this
order.order_line.invalidate_cache(fnames=fnames)
if not self.company_id.currency_id.is_zero(total_balance):
# globalized counterpart for the whole orders selection
values = _get_aml_vals(orders,
-total_balance,
0.0,
self.account_id.id,
label=_('Accrued total'))
move_lines.append(Command.create(values))
move_type = _('Expense') if is_purchase else _('Revenue')
move_vals = {
'ref':
_('Accrued %s entry as of %s', move_type,
format_date(self.env, self.date)),
'journal_id':
self.journal_id.id,
'date':
self.date,
'line_ids':
move_lines,
}
return move_vals, orders_with_entries
'account.invoice.line', 'result_id', 'Commission Lines',
readonly=True)
@api.depends('line_ids.commission_amount')
def _compute_amount_total(self):
rg_res = self.env['account.invoice.line'].read_group([('result_id', 'in', self.ids)], ['result_id', 'commission_amount'], ['result_id'])
mapped_data = dict([(x['result_id'][0], x['commission_amount']) for x in rg_res])
for rec in self:
rec.amount_total = mapped_data.get(rec.id, 0)
def _compute_sale_count(self):
rg_res = self.env['sale.order'].read_group(
[('agreement_id', 'in', self.ids),],
['agreement_id'], ['agreement_id'])
mapped_data = dict(
[(x['agreement_id'][0], x['agreement_id_count']) for x in rg_res])
for agreement in self:
agreement.sale_count = mapped_data.get(agreement.id, 0)
from odoo.tools.misc import formatLang
# CAUTION: it is not the same method as in the report ! It is only for numbers, not dates.
Proto: formatLang(env, value, digits=None, grouping=True, monetary=False, dp=False, currency_obj=False)
price_unit_formatted = formatLang(
self.with_context(lang=lang).env, self.price_unit, dp='Mileage Price',
monetary=True, currency_obj=self.company_id.currency_id)
self.env['ir.config_parameter'].sudo().get_param('webkit_path', default='default_path')
account_recordset = self.env['ir.property'].get('property_account_payable_id', 'res.partner')
def _compute_amount_format_btw_total(self):
for statement in self:
btw = formatLang(self.env, statement.btw_total, monetary=True)
statement.format_btw_total = btw
def _get_report_values(self, docids, data=None):
products = []
if data.get('source_model', '') == 'product.product':
quantity = int(data.get('quantity', 1))
product_ids = self.env['product.product'].with_context(
pricelist=data.get('pricelist_id'),
quantity=quantity
).browse(data.get('ids'))
product_ids = product_ids.filtered(lambda r: r.barcode)
if not product_ids:
raise UserError(
_('No barcodes found for current selection!')
)
for product in product_ids:
for i in range(quantity):
products.append({
'product': product,
'price': formatLang(
self.env,
product.price,
monetary=True,
currency_obj=self.env.user.company_id.currency_id
),
'quantity': quantity
})
elif data.get('source_model', '') == 'stock.picking':
picking_ids = self.env['stock.picking'].browse(data.get('ids'))
move_lines = picking_ids.move_lines.filtered(
lambda r: r.product_id.barcode
)
if not move_lines:
raise UserError(
_('No barcodes assigned for current products!')
)
for move_line in move_lines:
quantity = int(move_line.quantity_done)
product_id = move_line.product_id.with_context(
pricelist=data.get('pricelist_id'),
quantity=quantity
)
for i in range(quantity):
products.append({
'product': move_line.product_id,
'price': formatLang(
self.env,
product_id.price,
monetary=True,
currency_obj=self.env.user.company_id.currency_id
),
'quantity': quantity
})
report = self.env['ir.actions.report']._get_report_from_name(
'o4f_product_labels.labels_sheet'
)
model = report.model
return {
'doc_ids': self.ids,
'doc_model': model,
'docs': {
'sheets': tuple(
products[i:i + 18] for i in range(0, len(products), 18)
)
}
}
mapped_data = dict([(x['result_id'][0], x['commission_amount']) for x in rg_res])
for rec in self:
rec.amount_total = mapped_data.get(rec.id, 0)
def _compute_sale_count(self):
rg_res = self.env['sale.order'].read_group(
[('agreement_id', 'in', self.ids)],
['agreement_id'], ['agreement_id'])
mapped_data = dict(
[(x['agreement_id'][0], x['agreement_id_count']) for x in rg_res])
for agreement in self:
agreement.sale_count = mapped_data.get(agreement.id, 0)
from odoo.tools.misc import formatLang
# CAUTION: it is not the same method as in the report ! It is only for numbers, not dates.
Proto: formatLang(env, value, digits=None, grouping=True, monetary=False, dp=False, currency_obj=False)
price_unit_formatted = formatLang(
self.with_context(lang=lang).env, self.price_unit, currency_obj=self.company_id.currency_id)
qty_formatted = formatLang(
self.with_context(lang=lang).env, self.qty_done, dp='Product Unit of Measure')
Proto: format_date(env, value, lang_code=False, date_format=False)
'%s' % format_date(self.env, self.date)
# format_datetime is v13+ only
format_datetime(env, value, tz=False, dt_format='medium', lang_code=False)
'%s' % format_datetime(self.env, self.datetime_field)
format_amount(env, amount, currency, lang_code=False)
'%s' % format_amount(self.env, 12.42, invoice.currency_id)
self.env['ir.config_parameter'].sudo().get_param('webkit_path', default='default_path')
def _get_lines(self, options, line_id=None):
lines = []
context = self.env.context
if not context.get('company_ids'):
return lines
partner_ids = self.env['res.partner'].search([('vat', 'ilike', 'BE%')
]).ids
if not partner_ids:
return lines
tag_ids = [
self.env['ir.model.data'].xmlid_to_res_id(k) for k in [
'l10n_be.tax_tag_00', 'l10n_be.tax_tag_01',
'l10n_be.tax_tag_02', 'l10n_be.tax_tag_03',
'l10n_be.tax_tag_45', 'l10n_be.tax_tag_49'
]
]
tag_ids_2 = [
self.env['ir.model.data'].xmlid_to_res_id(k)
for k in ['l10n_be.tax_tag_54', 'l10n_be.tax_tag_64']
]
query = """
SELECT turnover_sub.partner_id, turnover_sub.name, turnover_sub.vat, turnover_sub.turnover, refund_vat_sub.refund_base, refund_base_sub.vat_amount, refund_base_sub.refund_vat_amount
FROM (SELECT l.partner_id, p.name, p.vat, SUM(l.credit - l.debit) as turnover
FROM account_move_line l
LEFT JOIN res_partner p ON l.partner_id = p.id AND p.customer = true
RIGHT JOIN (
SELECT DISTINCT amlt.account_move_line_id
FROM account_move_line_account_tax_rel amlt
LEFT JOIN account_tax_account_tag tt on amlt.account_tax_id = tt.account_tax_id
WHERE tt.account_account_tag_id IN %(tags)s
) AS x ON x.account_move_line_id = l.id
LEFT JOIN account_invoice inv ON l.invoice_id = inv.id
WHERE p.vat IS NOT NULL
AND l.partner_id IN %(partner_ids)s
AND l.date >= %(date_from)s
AND l.date <= %(date_to)s
AND l.company_id IN %(company_ids)s
AND ((l.invoice_id IS NULL AND l.credit > 0)
OR (inv.type IN ('out_refund', 'out_invoice') AND inv.state IN ('open', 'in_payment', 'paid')))
GROUP BY l.partner_id, p.name, p.vat) AS turnover_sub
FULL JOIN (SELECT l.partner_id, SUM(l.debit-l.credit) AS refund_base
FROM account_move_line l
LEFT JOIN res_partner p ON l.partner_id = p.id AND p.customer = true
RIGHT JOIN (
SELECT DISTINCT amlt.account_move_line_id
FROM account_move_line_account_tax_rel amlt
LEFT JOIN account_tax_account_tag tt on amlt.account_tax_id = tt.account_tax_id
WHERE tt.account_account_tag_id IN %(tags)s
) AS x ON x.account_move_line_id = l.id
LEFT JOIN account_invoice inv ON l.invoice_id = inv.id
WHERE p.vat IS NOT NULL
AND l.partner_id IN %(partner_ids)s
AND l.date >= %(date_from)s
AND l.date <= %(date_to)s
AND l.company_id IN %(company_ids)s
AND ((l.invoice_id IS NULL AND l.credit > 0)
OR (inv.type = 'out_refund' AND inv.state IN ('open', 'in_payment', 'paid')))
GROUP BY l.partner_id, p.name, p.vat) AS refund_vat_sub
ON turnover_sub.partner_id = refund_vat_sub.partner_id
LEFT JOIN (SELECT l2.partner_id, SUM(l2.credit - l2.debit) as vat_amount, SUM(l2.debit) AS refund_vat_amount
FROM account_move_line l2
LEFT JOIN account_tax tax2 on l2.tax_line_id = tax2.id
LEFT JOIN account_invoice inv ON l2.invoice_id = inv.id
WHERE tax2.id IN (SELECT DISTINCT(account_tax_id) from account_tax_account_tag WHERE account_account_tag_id IN %(tags2)s)
AND l2.partner_id IN %(partner_ids)s
AND l2.date >= %(date_from)s
AND l2.date <= %(date_to)s
AND l2.company_id IN %(company_ids)s
AND ((l2.invoice_id IS NULL AND l2.credit > 0)
OR (inv.type IN ('out_refund', 'out_invoice') AND inv.state IN ('open', 'in_payment', 'paid')))
GROUP BY l2.partner_id) AS refund_base_sub
ON turnover_sub.partner_id = refund_base_sub.partner_id
WHERE turnover > 250 OR refund_base > 0 OR refund_vat_amount > 0
"""
params = {
'tags': tuple(tag_ids),
'tags2': tuple(tag_ids_2),
'partner_ids': tuple(partner_ids),
'date_from': context['date_from'],
'date_to': context['date_to'],
'company_ids': tuple(context.get('company_ids')),
}
self.env.cr.execute(query, params)
for record in self.env.cr.dictfetchall():
currency_id = self.env.user.company_id.currency_id
columns = [
record['vat'].replace(' ', '').upper(), record['turnover'],
record['vat_amount']
]
if not context.get('no_format', False):
columns[1] = formatLang(self.env,
columns[1] or 0.0,
currency_obj=currency_id)
columns[2] = formatLang(self.env,
columns[2] or 0.0,
currency_obj=currency_id)
lines.append({
'id': record['partner_id'],
# 'type': 'partner_id',
'caret_options': 'res.partner',
'model': 'res.partner',
'name': record['name'],
'columns': [{
'name': v
} for v in columns],
# 'level': 2,
'unfoldable': False,
'unfolded': False,
})
return lines
def _compute_invoice_taxes_by_group(self):
report_or_portal_view = 'commit_assetsbundle' in self.env.context or \
not self.env.context.get('params', {}).get('view_type') == 'form'
if not report_or_portal_view:
return super()._compute_invoice_taxes_by_group()
move_with_doc_type = self.filtered('l10n_latam_document_type_id')
for move in move_with_doc_type:
lang_env = move.with_context(lang=move.partner_id.lang).env
tax_lines = move.l10n_latam_tax_ids
tax_balance_multiplicator = -1 if move.is_inbound(True) else 1
res = {}
# There are as many tax line as there are repartition lines
done_taxes = set()
for line in tax_lines:
res.setdefault(line.tax_line_id.tax_group_id, {
'base': 0.0,
'amount': 0.0
})
res[line.tax_line_id.
tax_group_id]['amount'] += tax_balance_multiplicator * (
line.amount_currency
if line.currency_id else line.balance)
tax_key_add_base = tuple(
move._get_tax_key_for_group_add_base(line))
if tax_key_add_base not in done_taxes:
if line.currency_id and line.company_currency_id and line.currency_id != line.company_currency_id:
amount = line.company_currency_id._convert(
line.tax_base_amount, line.currency_id,
line.company_id, line.date or fields.Date.today())
else:
amount = line.tax_base_amount
res[line.tax_line_id.tax_group_id]['base'] += amount
# The base should be added ONCE
done_taxes.add(tax_key_add_base)
# At this point we only want to keep the taxes with a zero amount since they do not
# generate a tax line.
zero_taxes = set()
for line in move.line_ids:
for tax in line.l10n_latam_tax_ids.flatten_taxes_hierarchy():
if tax.tax_group_id not in res or tax.id in zero_taxes:
res.setdefault(tax.tax_group_id, {
'base': 0.0,
'amount': 0.0
})
res[tax.tax_group_id][
'base'] += tax_balance_multiplicator * (
line.amount_currency
if line.currency_id else line.balance)
zero_taxes.add(tax.id)
res = sorted(res.items(), key=lambda l: l[0].sequence)
move.amount_by_group = [
(group.name, amounts['amount'], amounts['base'],
formatLang(lang_env,
amounts['amount'],
currency_obj=move.currency_id),
formatLang(lang_env,
amounts['base'],
currency_obj=move.currency_id), len(res), group.id)
for group, amounts in res
]
super(AccountMove,
self - move_with_doc_type)._compute_invoice_taxes_by_group()
def get_journal_dashboard_datas(self):
currency = self.currency_id or self.company_id.currency_id
number_to_reconcile = last_balance = account_sum = 0
ac_bnk_stmt = []
title = ''
number_draft = number_waiting = number_late = sum_draft = sum_waiting = sum_late = 0
if self.type in ['bank', 'cash']:
last_bank_stmt = self.env['account.bank.statement'].search(
[('journal_id', 'in', self.ids)],
order="date desc, id desc",
limit=1)
last_balance = last_bank_stmt and last_bank_stmt[0].balance_end or 0
ac_bnk_stmt = self.env['account.bank.statement'].search([
('journal_id', 'in', self.ids), ('state', '=', 'open')
])
for ac_bnk in ac_bnk_stmt:
for line in ac_bnk.line_ids:
if not line.journal_entry_ids:
number_to_reconcile += 1
# optimization to read sum of balance from account_move_line
account_ids = tuple(
filter(None, [
self.default_debit_account_id.id,
self.default_credit_account_id.id
]))
if account_ids:
amount_field = 'balance' if not self.currency_id else 'amount_currency'
query = """SELECT sum(%s) FROM account_move_line WHERE account_id in %%s;""" % (
amount_field, )
self.env.cr.execute(query, (account_ids, ))
query_results = self.env.cr.dictfetchall()
if query_results and query_results[0].get('sum') != None:
account_sum = query_results[0].get('sum')
#TODO need to check if all invoices are in the same currency than the journal!!!!
elif self.type in ['sale', 'purchase']:
title = _('Bills to pay') if self.type == 'purchase' else _(
'Invoices owed to you')
# optimization to find total and sum of invoice that are in draft, open state
query = """SELECT state, amount_total, currency_id AS currency FROM account_invoice WHERE journal_id = %s AND state NOT IN ('paid', 'cancel');"""
self.env.cr.execute(query, (self.id, ))
query_results = self.env.cr.dictfetchall()
today = datetime.today()
query = """SELECT amount_total, currency_id AS currency FROM account_invoice WHERE journal_id = %s AND date < %s AND state = 'open';"""
self.env.cr.execute(query, (self.id, today))
late_query_results = self.env.cr.dictfetchall()
sum_draft = 0.0
number_draft = 0
number_waiting = 0
for result in query_results:
cur = self.env['res.currency'].browse(result.get('currency'))
amount_total = result.get('amount_total') or 0
if result.get('state') in ['draft', 'proforma', 'proforma2']:
number_draft += 1
sum_draft += cur.compute(amount_total, currency)
elif result.get('state') == 'open':
number_waiting += 1
sum_waiting += cur.compute(amount_total, currency)
sum_late = 0.0
number_late = 0
for result in late_query_results:
cur = self.env['res.currency'].browse(result.get('currency'))
number_late += 1
sum_late += cur.compute(amount_total, currency)
return {
'number_to_reconcile':
number_to_reconcile,
'account_balance':
formatLang(self.env,
account_sum,
currency_obj=self.currency_id
or self.company_id.currency_id),
'last_balance':
formatLang(self.env,
last_balance,
currency_obj=self.currency_id
or self.company_id.currency_id),
'number_draft':
number_draft,
'number_waiting':
number_waiting,
'number_late':
number_late,
'sum_draft':
formatLang(self.env,
sum_draft or 0.0,
currency_obj=self.currency_id
or self.company_id.currency_id),
'sum_waiting':
formatLang(self.env,
sum_waiting or 0.0,
currency_obj=self.currency_id
or self.company_id.currency_id),
'sum_late':
formatLang(self.env,
sum_late or 0.0,
currency_obj=self.currency_id
or self.company_id.currency_id),
'currency_id':
self.currency_id and self.currency_id.id
or self.company_id.currency_id.id,
'bank_statements_source':
self.bank_statements_source,
'title':
title,
}
def check_report_xls(self):
current_obj = self
data = {}
data['form'] = self.read([
'date_from', 'date_to', 'account_report_id', 'target_move',
'enable_filter', 'debit_credit', 'filter_cmp', 'date_from_cmp',
'date_to_cmp'
])[0]
target_move = dict(
self.env['accounting.report'].fields_get(allfields=['target_move'])
['target_move']['selection'])[current_obj.target_move]
used_context = self._build_contexts(data)
comparision_context = self._build_comparison_context(data)
data['form']['comparison_context'] = dict(comparision_context,
lang=self.env.context.get(
'lang', 'en_US'))
data['form']['used_context'] = dict(used_context,
lang=self.env.context.get(
'lang', 'en_US'))
account_res = self.env[
'report.account.report_financial'].get_account_lines(
data.get('form'))
#### From get lines ###
fp = BytesIO()
wb = xlwt.Workbook(encoding='utf-8')
header_style = xlwt.XFStyle()
font = xlwt.Font()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
bg_color = current_obj.xls_theme_id.bg_color or 'black'
pattern.pattern_fore_colour = xlwt.Style.colour_map[bg_color]
font.height = int(current_obj.xls_theme_id.font_size)
font.bold = current_obj.xls_theme_id.font_bold
font.italic = current_obj.xls_theme_id.font_italic
font_color = current_obj.xls_theme_id.font_color or 'white'
font.colour_index = xlwt.Style.colour_map[font_color]
header_style.pattern = pattern
header_style.font = font
al3 = Alignment()
al3.horz = current_obj.xls_theme_id.header_alignment or 0x02
header_style.alignment = al3
column_header_style = xlwt.XFStyle()
font = xlwt.Font()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
bg_color = current_obj.xls_theme_id.column_bg_color or 'red'
pattern.pattern_fore_colour = xlwt.Style.colour_map[bg_color]
font.height = int(current_obj.xls_theme_id.column_font_size)
font.bold = current_obj.xls_theme_id.column_font_bold
font.italic = current_obj.xls_theme_id.column_font_italic
font_color = current_obj.xls_theme_id.column_font_color or 'white'
font.colour_index = xlwt.Style.colour_map[font_color]
column_header_style.pattern = pattern
column_header_style.font = font
al3 = Alignment()
al3.horz = current_obj.xls_theme_id.column_header_alignment
column_header_style.alignment = al3
body_header_style = xlwt.XFStyle()
font = xlwt.Font()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
bg_color = current_obj.xls_theme_id.body_bg_color or 'gold'
pattern.pattern_fore_colour = xlwt.Style.colour_map[bg_color]
font.height = int(current_obj.xls_theme_id.body_font_size)
font.bold = current_obj.xls_theme_id.body_font_bold
font.italic = current_obj.xls_theme_id.body_font_italic
font_color = current_obj.xls_theme_id.body_font_color or 'white'
font.colour_index = xlwt.Style.colour_map[font_color]
body_header_style.pattern = pattern
body_header_style.font = font
al3 = Alignment()
al3.horz = current_obj.xls_theme_id.body_header_alignment
body_header_style.alignment = al3
date_from = self.date_from
date_to = self.date_to
final_arr_data = {}
filename = self.account_report_id.name + '.xls'
worksheet = wb.add_sheet(current_obj.account_report_id.name + ".xls")
for i in range(1, 10):
column = worksheet.col(i)
column.width = 235 * 30
column = worksheet.col(0)
column.width = 350 * 30
if not current_obj.debit_credit and not current_obj.enable_filter:
worksheet.write_merge(1, 1, 1, 2,
current_obj.account_report_id.name,
header_style)
if date_from or date_to:
worksheet.write(5, 2, "Date from:" + '' + date_from,
column_header_style)
worksheet.write(6, 2, "Date to:" + '' + date_to,
column_header_style)
worksheet.write(3, 1, "Target Moves:", column_header_style)
worksheet.write(4, 1, target_move, body_header_style)
worksheet.write(11, 1, "Name", column_header_style)
worksheet.write(11, 2, "Balance", column_header_style)
i = 11
for data in account_res:
if data['level'] != 0:
worksheet.write(i + 1, 1, data['name'],
column_header_style)
worksheet.write(
i + 1, 2,
formatLang(
self.env,
data['balance'],
currency_obj=current_obj.company_id.currency_id),
column_header_style)
# else:
# worksheet.write(i+1, 0, data['name'], body_header_style)
# worksheet.write(i+1, 1, data['balance'], body_header_style)
i += 1
elif current_obj.enable_filter:
worksheet.write_merge(1, 1, 1, 2,
current_obj.account_report_id.name,
header_style)
if date_from or date_to:
worksheet.write(5, 2, "Date from:" + '' + date_from,
column_header_style)
worksheet.write(6, 2, "Date to:" + '' + date_to,
column_header_style)
worksheet.write(3, 1, "Target Moves:", column_header_style)
worksheet.write(4, 1, target_move, body_header_style)
worksheet.write(11, 1, "Name", column_header_style)
worksheet.write(11, 2, "Balance", column_header_style)
worksheet.write(11, 3, current_obj.label_filter,
column_header_style)
i = 11
for data in account_res:
if data['level'] != 0:
worksheet.write(i + 1, 1, data['name'],
column_header_style)
worksheet.write(
i + 1, 2,
formatLang(
self.env,
data['balance'],
currency_obj=current_obj.company_id.currency_id),
column_header_style)
worksheet.write(
i + 1, 3,
formatLang(
self.env,
data['balance'],
currency_obj=current_obj.company_id.currency_id),
column_header_style)
i += 1
else:
worksheet.write_merge(1, 1, 1, 2,
current_obj.account_report_id.name,
header_style)
if date_from or date_to:
worksheet.write(5, 2, "Date from:" + '' + date_from,
column_header_style)
worksheet.write(6, 2, "Date to:" + '' + date_to,
column_header_style)
worksheet.write(3, 1, "Target Moves:", column_header_style)
worksheet.write(4, 1, target_move, body_header_style)
worksheet.write(11, 1, "Name", column_header_style)
worksheet.write(11, 2, "Debit", column_header_style)
worksheet.write(11, 3, "Credit", column_header_style)
worksheet.write(11, 4, "Balance", column_header_style)
i = 11
for data in account_res:
if data['level'] != 0:
worksheet.write(i + 1, 1, data['name'], body_header_style)
worksheet.write(
i + 1, 2,
formatLang(
self.env,
data['debit'],
currency_obj=current_obj.company_id.currency_id),
body_header_style)
worksheet.write(
i + 1, 3,
formatLang(
self.env,
data['credit'],
currency_obj=current_obj.company_id.currency_id),
body_header_style)
worksheet.write(
i + 1, 4,
formatLang(
self.env,
data['balance'],
currency_obj=current_obj.company_id.currency_id),
body_header_style)
i += 1
wb.save(fp)
out = base64.encodestring(fp.getvalue())
final_arr_data = {}
final_arr_data['file_stream'] = out
final_arr_data['name'] = filename
create_id = self.env['account.report.view'].create(final_arr_data)
return {
'nodestroy': True,
'res_id': create_id.id,
'name': filename,
'view_type': 'form',
'view_mode': 'form',
'res_model': 'account.report.view',
'view_id': False,
'type': 'ir.actions.act_window',
}
def FormatAmount(amount):
return formatLang(self.env,
amount,
currency_obj=self.company_id.currency_id)
def set_amount(self, amount):
amount = formatLang(self.env, amount)
return amount
def get_journal_dashboard_datas(self):
currency = self.currency_id or self.company_id.currency_id
number_to_reconcile = last_balance = account_sum = 0
title = ''
number_draft = number_waiting = number_late = 0
sum_draft = sum_waiting = sum_late = 0.0
if self.type in ['bank', 'cash']:
last_bank_stmt = self.env['account.bank.statement'].search([('journal_id', 'in', self.ids)], order="date desc, id desc", limit=1)
last_balance = last_bank_stmt and last_bank_stmt[0].balance_end or 0
#Get the number of items to reconcile for that bank journal
self.env.cr.execute("""SELECT COUNT(DISTINCT(line.id))
FROM account_bank_statement_line AS line
LEFT JOIN account_bank_statement AS st
ON line.statement_id = st.id
WHERE st.journal_id IN %s AND st.state = 'open'
AND not exists (select 1 from account_move_line aml where aml.statement_line_id = line.id)
""", (tuple(self.ids),))
number_to_reconcile = self.env.cr.fetchone()[0]
# optimization to read sum of balance from account_move_line
account_ids = tuple(ac for ac in [self.default_debit_account_id.id, self.default_credit_account_id.id] if ac)
if account_ids:
amount_field = 'balance' if not self.currency_id else 'amount_currency'
query = """SELECT sum(%s) FROM account_move_line WHERE account_id in %%s;""" % (amount_field,)
self.env.cr.execute(query, (account_ids,))
query_results = self.env.cr.dictfetchall()
if query_results and query_results[0].get('sum') != None:
account_sum = query_results[0].get('sum')
#TODO need to check if all invoices are in the same currency than the journal!!!!
elif self.type in ['sale', 'purchase']:
title = _('Bills to pay') if self.type == 'purchase' else _('Invoices owed to you')
# optimization to find total and sum of invoice that are in draft, open state
query = """SELECT state, amount_total, currency_id AS currency, type FROM account_invoice WHERE journal_id = %s AND state NOT IN ('paid', 'cancel');"""
self.env.cr.execute(query, (self.id,))
query_results = self.env.cr.dictfetchall()
today = datetime.today()
query = """SELECT amount_total, currency_id AS currency, type FROM account_invoice WHERE journal_id = %s AND date < %s AND state = 'open';"""
self.env.cr.execute(query, (self.id, today))
late_query_results = self.env.cr.dictfetchall()
for result in query_results:
if result['type'] in ['in_refund', 'out_refund']:
factor = -1
else:
factor = 1
cur = self.env['res.currency'].browse(result.get('currency'))
if result.get('state') == 'draft':
number_draft += 1
sum_draft += cur.compute(result.get('amount_total'), currency) * factor
elif result.get('state') == 'open':
number_waiting += 1
sum_waiting += cur.compute(result.get('amount_total'), currency) * factor
for result in late_query_results:
if result['type'] in ['in_refund', 'out_refund']:
factor = -1
else:
factor = 1
cur = self.env['res.currency'].browse(result.get('currency'))
number_late += 1
sum_late += cur.compute(result.get('amount_total'), currency) * factor
return {
'number_to_reconcile': number_to_reconcile,
'account_balance': formatLang(self.env, account_sum, currency_obj=self.currency_id or self.company_id.currency_id),
'last_balance': formatLang(self.env, last_balance, currency_obj=self.currency_id or self.company_id.currency_id),
'difference': (last_balance-account_sum) and formatLang(self.env, last_balance-account_sum, currency_obj=self.currency_id or self.company_id.currency_id) or False,
'number_draft': number_draft,
'number_waiting': number_waiting,
'number_late': number_late,
'sum_draft': formatLang(self.env, sum_draft or 0.0, currency_obj=self.currency_id or self.company_id.currency_id),
'sum_waiting': formatLang(self.env, sum_waiting or 0.0, currency_obj=self.currency_id or self.company_id.currency_id),
'sum_late': formatLang(self.env, sum_late or 0.0, currency_obj=self.currency_id or self.company_id.currency_id),
'currency_id': self.currency_id and self.currency_id.id or self.company_id.currency_id.id,
'bank_statements_source': self.bank_statements_source,
'title': title,
}
def _prepare_move_lines(self, move_lines, target_currency=False, target_date=False, recs_count=0):
""" Returns move lines formatted for the manual/bank reconciliation widget
:param move_line_ids:
:param target_currency: currency (browse) you want the move line debit/credit converted into
:param target_date: date to use for the monetary conversion
"""
context = dict(self._context or {})
ret = []
for line in move_lines:
company_currency = line.company_id.currency_id
line_currency = (line.currency_id and line.amount_currency) and line.currency_id or company_currency
ret_line = {
'id': line.id,
'name': line.name and line.name != '/' and line.move_id.name + ': ' + line.name or line.move_id.name,
'ref': line.move_id.ref or '',
# For reconciliation between statement transactions and already registered payments (eg. checks)
# NB : we don't use the 'reconciled' field because the line we're selecting is not the one that gets reconciled
'account_id': [line.account_id.id, line.account_id.display_name],
'already_paid': line.account_id.internal_type == 'liquidity',
'account_code': line.account_id.code,
'account_name': line.account_id.name,
'account_type': line.account_id.internal_type,
'date_maturity': line.date_maturity,
'date': line.date,
'journal_id': [line.journal_id.id, line.journal_id.display_name],
'partner_id': line.partner_id.id,
'partner_name': line.partner_id.name,
'currency_id': line_currency.id,
}
debit = line.debit
credit = line.credit
amount = line.amount_residual
amount_currency = line.amount_residual_currency
# For already reconciled lines, don't use amount_residual(_currency)
if line.account_id.internal_type == 'liquidity':
amount = debit - credit
amount_currency = line.amount_currency
target_currency = target_currency or company_currency
# Use case:
# Let's assume that company currency is in USD and that we have the 3 following move lines
# Debit Credit Amount currency Currency
# 1) 25 0 0 NULL
# 2) 17 0 25 EUR
# 3) 33 0 25 YEN
#
# If we ask to see the information in the reconciliation widget in company currency, we want to see
# The following information
# 1) 25 USD (no currency information)
# 2) 17 USD [25 EUR] (show 25 euro in currency information, in the little bill)
# 3) 33 USD [25 YEN] (show 25 yen in currency information)
#
# If we ask to see the information in another currency than the company let's say EUR
# 1) 35 EUR [25 USD]
# 2) 25 EUR (no currency information)
# 3) 50 EUR [25 YEN]
# In that case, we have to convert the debit-credit to the currency we want and we show next to it
# the value of the amount_currency or the debit-credit if no amount currency
if target_currency == company_currency:
if line_currency == target_currency:
amount = amount
amount_currency = ""
total_amount = debit - credit
total_amount_currency = ""
else:
amount = amount
amount_currency = amount_currency
total_amount = debit - credit
total_amount_currency = line.amount_currency
if target_currency != company_currency:
if line_currency == target_currency:
amount = amount_currency
amount_currency = ""
total_amount = line.amount_currency
total_amount_currency = ""
else:
amount_currency = line.currency_id and amount_currency or amount
company = line.account_id.company_id
date = target_date or line.date
amount = company_currency._convert(amount, target_currency, company, date)
total_amount = company_currency._convert((line.debit - line.credit), target_currency, company, date)
total_amount_currency = line.currency_id and line.amount_currency or (line.debit - line.credit)
ret_line['recs_count'] = recs_count
ret_line['debit'] = amount > 0 and amount or 0
ret_line['credit'] = amount < 0 and -amount or 0
ret_line['amount_currency'] = amount_currency
ret_line['amount_str'] = formatLang(self.env, abs(amount), currency_obj=target_currency)
ret_line['total_amount_str'] = formatLang(self.env, abs(total_amount), currency_obj=target_currency)
ret_line['amount_currency_str'] = amount_currency and formatLang(self.env, abs(amount_currency), currency_obj=line_currency) or ""
ret_line['total_amount_currency_str'] = total_amount_currency and formatLang(self.env, abs(total_amount_currency), currency_obj=line_currency) or ""
ret.append(ret_line)
return ret
def _get_lines(self, options, line_id=None):
lines = []
context = self.env.context
if not context.get('company_ids'):
return lines
seq = amount_sum = 0
tag_ids = [
self.env['ir.model.data'].xmlid_to_res_id(k) for k in [
'l10n_be.tax_tag_44', 'l10n_be.tax_tag_46L',
'l10n_be.tax_tag_46T'
]
]
query = """
SELECT p.name As partner_name, l.partner_id AS partner_id, p.vat AS vat,
tt.account_account_tag_id AS intra_code, SUM(-l.balance) AS amount
FROM account_move_line l
LEFT JOIN res_partner p ON l.partner_id = p.id
LEFT JOIN account_move_line_account_tax_rel amlt ON l.id = amlt.account_move_line_id
LEFT JOIN account_tax_account_tag tt on amlt.account_tax_id = tt.account_tax_id
WHERE tt.account_account_tag_id IN %s
AND l.date >= %s
AND l.date <= %s
AND l.company_id IN %s
GROUP BY p.name, l.partner_id, p.vat, intra_code
"""
params = (tuple(tag_ids), context.get('date_from'),
context.get('date_to'), tuple(context.get('company_ids')))
self.env.cr.execute(query, params)
p_count = 0
for row in self.env.cr.dictfetchall():
if not row['vat']:
row['vat'] = ''
p_count += 1
amt = row['amount'] or 0.0
if amt:
seq += 1
amount_sum += amt
[intra_code,
code] = row['intra_code'] == tag_ids[0] and ['44', 'S'] or (
row['intra_code'] == tag_ids[1] and ['46L', 'L'] or
(row['intra_code'] == tag_ids[2] and ['46T', 'T']
or ['', '']))
columns = [
row['vat'].replace(' ', '').upper(), code, intra_code, amt
]
if not context.get('no_format', False):
currency_id = self.env.user.company_id.currency_id
columns[3] = formatLang(self.env,
columns[3],
currency_obj=currency_id)
lines.append({
'id': row['partner_id'],
# 'type': 'partner_id',
'caret_options': 'res.partner',
'model': 'res.partner',
'name': row['partner_name'],
'columns': [{
'name': v
} for v in columns],
# 'level': 2,
'unfoldable': False,
'unfolded': False,
})
if context.get('get_xml_data'):
return {
'lines': lines,
'clientnbr': str(seq),
'amountsum': round(amount_sum, 2),
'partner_wo_vat': p_count
}
return lines
def _get_statement_line(self, st_line):
""" Returns the data required by the bank statement reconciliation widget to display a statement line """
statement_currency = st_line.journal_id.currency_id or st_line.journal_id.company_id.currency_id
if st_line.amount_currency and st_line.currency_id:
amount = st_line.amount_currency
amount_currency = st_line.amount
amount_currency_str = formatLang(self.env,
abs(amount_currency),
currency_obj=statement_currency)
else:
amount = st_line.amount
amount_currency = amount
amount_currency_str = ""
amount_str = formatLang(self.env,
abs(amount),
currency_obj=st_line.currency_id
or statement_currency)
data = {
'id':
st_line.id,
'ref':
st_line.ref,
'note':
st_line.note or "",
'name':
st_line.name,
'date':
st_line.date,
'amount':
amount,
'amount_str':
amount_str, # Amount in the statement line currency
'currency_id':
st_line.currency_id.id or statement_currency.id,
'partner_id':
st_line.partner_id.id,
'journal_id':
st_line.journal_id.id,
'statement_id':
st_line.statement_id.id,
'account_id': [
st_line.journal_id.default_debit_account_id.id,
st_line.journal_id.default_debit_account_id.display_name
],
'account_code':
st_line.journal_id.default_debit_account_id.code,
'account_name':
st_line.journal_id.default_debit_account_id.name,
'partner_name':
st_line.partner_id.name,
'communication_partner_name':
st_line.partner_name,
'amount_currency_str':
amount_currency_str, # Amount in the statement currency
'amount_currency':
amount_currency, # Amount in the statement currency
'has_no_partner':
not st_line.partner_id.id,
'company_id':
st_line.company_id.id,
}
if st_line.partner_id:
if amount > 0:
data[
'open_balance_account_id'] = st_line.partner_id.property_account_receivable_id.id
else:
data[
'open_balance_account_id'] = st_line.partner_id.property_account_payable_id.id
return data
def get_journal_dashboard_datas(self):
currency = self.currency_id or self.company_id.currency_id
number_to_reconcile = number_to_check = last_balance = account_sum = 0
title = ''
number_draft = number_waiting = number_late = to_check_balance = 0
sum_draft = sum_waiting = sum_late = 0.0
if self.type in ['bank', 'cash']:
last_bank_stmt = self.env['account.bank.statement'].search(
[('journal_id', 'in', self.ids)],
order="date desc, id desc",
limit=1)
last_balance = last_bank_stmt and last_bank_stmt[0].balance_end or 0
#Get the number of items to reconcile for that bank journal
self.env.cr.execute(
"""SELECT COUNT(DISTINCT(line.id))
FROM account_bank_statement_line AS line
LEFT JOIN account_bank_statement AS st
ON line.statement_id = st.id
WHERE st.journal_id IN %s AND st.state = 'open' AND line.amount != 0.0 AND line.account_id IS NULL
AND not exists (select 1 from account_move_line aml where aml.statement_line_id = line.id)
""", (tuple(self.ids), ))
number_to_reconcile = self.env.cr.fetchone()[0]
to_check_ids = self.to_check_ids()
number_to_check = len(to_check_ids)
to_check_balance = sum([r.amount for r in to_check_ids])
# optimization to read sum of balance from account_move_line
account_ids = tuple(ac for ac in [
self.default_debit_account_id.id,
self.default_credit_account_id.id
] if ac)
if account_ids:
amount_field = 'aml.balance' if (
not self.currency_id or self.currency_id
== self.company_id.currency_id) else 'aml.amount_currency'
query = """SELECT sum(%s) FROM account_move_line aml
LEFT JOIN account_move move ON aml.move_id = move.id
WHERE aml.account_id in %%s
AND move.date <= %%s AND move.state = 'posted';""" % (
amount_field, )
self.env.cr.execute(query, (
account_ids,
fields.Date.context_today(self),
))
query_results = self.env.cr.dictfetchall()
if query_results and query_results[0].get('sum') != None:
account_sum = query_results[0].get('sum')
#TODO need to check if all invoices are in the same currency than the journal!!!!
elif self.type in ['sale', 'purchase']:
title = _('Bills to pay') if self.type == 'purchase' else _(
'Invoices owed to you')
self.env['account.move'].flush([
'amount_residual', 'currency_id', 'type', 'invoice_date',
'company_id', 'journal_id', 'date', 'state',
'invoice_payment_state'
])
(query, query_args) = self._get_open_bills_to_pay_query()
self.env.cr.execute(query, query_args)
query_results_to_pay = self.env.cr.dictfetchall()
(query, query_args) = self._get_draft_bills_query()
self.env.cr.execute(query, query_args)
query_results_drafts = self.env.cr.dictfetchall()
today = fields.Date.today()
query = '''
SELECT
(CASE WHEN type IN ('out_refund', 'in_refund') THEN -1 ELSE 1 END) * amount_residual AS amount_total,
currency_id AS currency,
type,
invoice_date,
company_id
FROM account_move move
WHERE journal_id = %s
AND date <= %s
AND state = 'posted'
AND invoice_payment_state = 'not_paid'
AND type IN ('out_invoice', 'out_refund', 'in_invoice', 'in_refund', 'out_receipt', 'in_receipt');
'''
self.env.cr.execute(query, (self.id, today))
late_query_results = self.env.cr.dictfetchall()
curr_cache = {}
(number_waiting,
sum_waiting) = self._count_results_and_sum_amounts(
query_results_to_pay, currency, curr_cache=curr_cache)
(number_draft, sum_draft) = self._count_results_and_sum_amounts(
query_results_drafts, currency, curr_cache=curr_cache)
(number_late, sum_late) = self._count_results_and_sum_amounts(
late_query_results, currency, curr_cache=curr_cache)
read = self.env['account.move'].read_group(
[('journal_id', '=', self.id),
('to_check', '=', True)], ['amount_total'],
'journal_id',
lazy=False)
if read:
number_to_check = read[0]['__count']
to_check_balance = read[0]['amount_total']
elif self.type == 'general':
read = self.env['account.move'].read_group(
[('journal_id', '=', self.id),
('to_check', '=', True)], ['amount_total'],
'journal_id',
lazy=False)
if read:
number_to_check = read[0]['__count']
to_check_balance = read[0]['amount_total']
difference = currency.round(last_balance - account_sum) + 0.0
is_sample_data = self.kanban_dashboard_graph and any(
data.get('is_sample_data', False)
for data in json.loads(self.kanban_dashboard_graph))
return {
'number_to_check':
number_to_check,
'to_check_balance':
formatLang(self.env, to_check_balance, currency_obj=currency),
'number_to_reconcile':
number_to_reconcile,
'account_balance':
formatLang(self.env,
currency.round(account_sum) + 0.0,
currency_obj=currency),
'last_balance':
formatLang(self.env,
currency.round(last_balance) + 0.0,
currency_obj=currency),
'difference':
formatLang(self.env, difference, currency_obj=currency)
if difference else False,
'number_draft':
number_draft,
'number_waiting':
number_waiting,
'number_late':
number_late,
'sum_draft':
formatLang(self.env,
currency.round(sum_draft) + 0.0,
currency_obj=currency),
'sum_waiting':
formatLang(self.env,
currency.round(sum_waiting) + 0.0,
currency_obj=currency),
'sum_late':
formatLang(self.env,
currency.round(sum_late) + 0.0,
currency_obj=currency),
'currency_id':
currency.id,
'bank_statements_source':
self.bank_statements_source,
'title':
title,
'is_sample_data':
is_sample_data,
}
def _get_reward_values_discount(self, program):
if program.discount_type == 'fixed_amount':
taxes = program.discount_line_product_id.taxes_id
if self.fiscal_position_id:
taxes = self.fiscal_position_id.map_tax(taxes)
return [{
'name':
_("Discount: %s", program.name),
'product_id':
program.discount_line_product_id.id,
'price_unit':
-self._get_reward_values_discount_fixed_amount(program),
'product_uom_qty':
1.0,
'product_uom':
program.discount_line_product_id.uom_id.id,
'is_reward_line':
True,
'tax_id': [(4, tax.id, False) for tax in taxes],
}]
reward_dict = {}
lines = self._get_paid_order_lines()
amount_total = sum(
self._get_base_order_lines(program).mapped('price_subtotal'))
if program.discount_apply_on == 'cheapest_product':
line = self._get_cheapest_line()
if line:
discount_line_amount = min(
line.price_reduce * (program.discount_percentage / 100),
amount_total)
if discount_line_amount:
taxes = self.fiscal_position_id.map_tax(line.tax_id)
reward_dict[line.tax_id] = {
'name':
_("Discount: %s", program.name),
'product_id':
program.discount_line_product_id.id,
'price_unit':
-discount_line_amount
if discount_line_amount > 0 else 0,
'product_uom_qty':
1.0,
'product_uom':
program.discount_line_product_id.uom_id.id,
'is_reward_line':
True,
'tax_id': [(4, tax.id, False) for tax in taxes],
}
elif program.discount_apply_on in ['specific_products', 'on_order']:
if program.discount_apply_on == 'specific_products':
# We should not exclude reward line that offer this product since we need to offer only the discount on the real paid product (regular product - free product)
free_product_lines = self.env['coupon.program'].search([
('reward_type', '=', 'product'),
('reward_product_id', 'in',
program.discount_specific_product_ids.ids)
]).mapped('discount_line_product_id')
lines = lines.filtered(lambda x: x.product_id in
(program.discount_specific_product_ids |
free_product_lines))
# when processing lines we should not discount more than the order remaining total
currently_discounted_amount = 0
for line in lines:
discount_line_amount = min(
self._get_reward_values_discount_percentage_per_line(
program, line),
amount_total - currently_discounted_amount)
if discount_line_amount:
if line.tax_id in reward_dict:
reward_dict[
line.tax_id]['price_unit'] -= discount_line_amount
else:
taxes = self.fiscal_position_id.map_tax(line.tax_id)
reward_dict[line.tax_id] = {
'name':
_(
"Discount: %(program)s - On product with following taxes: %(taxes)s",
program=program.name,
taxes=", ".join(taxes.mapped('name')),
),
'product_id':
program.discount_line_product_id.id,
'price_unit':
-discount_line_amount
if discount_line_amount > 0 else 0,
'product_uom_qty':
1.0,
'product_uom':
program.discount_line_product_id.uom_id.id,
'is_reward_line':
True,
'tax_id': [(4, tax.id, False) for tax in taxes],
}
currently_discounted_amount += discount_line_amount
# If there is a max amount for discount, we might have to limit some discount lines or completely remove some lines
max_amount = program._compute_program_amount('discount_max_amount',
self.currency_id)
if max_amount > 0:
amount_already_given = 0
for val in list(reward_dict):
amount_to_discount = amount_already_given + reward_dict[val][
"price_unit"]
if abs(amount_to_discount) > max_amount:
reward_dict[val]["price_unit"] = -(
max_amount - abs(amount_already_given))
add_name = formatLang(self.env,
max_amount,
currency_obj=self.currency_id)
reward_dict[val]["name"] += "( " + _(
"limited to ") + add_name + ")"
amount_already_given += reward_dict[val]["price_unit"]
if reward_dict[val]["price_unit"] == 0:
del reward_dict[val]
return reward_dict.values()
def print_ledgerreport_xls(self):
current_obj = self
data = {}
init_balance = self.initial_balance
sortby = self.sortby
display_account = self.display_account
target_move = self.target_move
codes = []
if self.journal_ids:
codes = [journal.code for journal in self.env['account.journal'].search([('id', 'in', self.journal_ids.ids)])]
if self.account_ids:
accounts = self.env['account.account'].browse(self.account_ids.ids)
else:
accounts = self.env['account.account'].search([])
data['form'] = self.read(['date_from', 'date_to', 'journal_ids','target_move'])[0]
if self.initial_balance and not self.date_from:
raise UserError(_("You must define a Start Date"))
used_context = self._build_contexts(data)
data['form']['used_context'] = dict(used_context, lang=self.env.context.get('lang', 'en_US'))
accounts_res = self.env['report.account.report_generalledger'].with_context(data['form'].get('used_context',{}))._get_account_move_entry(accounts, init_balance, sortby, display_account)
target_move = dict(self.env['account.report.general.ledger'].fields_get(allfields=['target_move'])['target_move']['selection'])[current_obj.target_move]
sortby = dict(self.env['account.report.general.ledger'].fields_get(allfields=['sortby'])['sortby']['selection'])[current_obj.sortby]
display_account = dict(self.env['account.report.general.ledger'].fields_get(allfields=['display_account'])['display_account']['selection'])[current_obj.display_account]
fp = BytesIO()
wb = xlwt.Workbook(encoding='utf-8')
header_style = xlwt.XFStyle()
font = xlwt.Font()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
bg_color = current_obj.xls_theme_id.bg_color or 'black'
pattern.pattern_fore_colour = xlwt.Style.colour_map[bg_color]
font.height = int(current_obj.xls_theme_id.font_size)
font.bold = current_obj.xls_theme_id.font_bold
font.italic = current_obj.xls_theme_id.font_italic
font_color = current_obj.xls_theme_id.font_color or 'white'
font.colour_index = xlwt.Style.colour_map[font_color]
header_style.pattern = pattern
header_style.font = font
al3 = Alignment()
al3.horz = current_obj.xls_theme_id.header_alignment or 0x02
header_style.alignment = al3
column_header_style = xlwt.XFStyle()
font = xlwt.Font()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
bg_color = current_obj.xls_theme_id.column_bg_color or 'red'
pattern.pattern_fore_colour = xlwt.Style.colour_map[bg_color]
font.height = int(current_obj.xls_theme_id.column_font_size)
font.bold = current_obj.xls_theme_id.column_font_bold
font.italic = current_obj.xls_theme_id.column_font_italic
font_color = current_obj.xls_theme_id.column_font_color or 'white'
font.colour_index = xlwt.Style.colour_map[font_color]
column_header_style.pattern = pattern
column_header_style.font = font
al3 = Alignment()
al3.horz = current_obj.xls_theme_id.column_header_alignment
column_header_style.alignment = al3
body_header_style = xlwt.XFStyle()
font = xlwt.Font()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
bg_color = current_obj.xls_theme_id.body_bg_color or 'gold'
pattern.pattern_fore_colour = xlwt.Style.colour_map[bg_color]
font.height = int(current_obj.xls_theme_id.body_font_size)
font.bold = current_obj.xls_theme_id.body_font_bold
font.italic = current_obj.xls_theme_id.body_font_italic
font_color = current_obj.xls_theme_id.body_font_color or 'white'
font.colour_index = xlwt.Style.colour_map[font_color]
body_header_style.pattern = pattern
body_header_style.font = font
al3 = Alignment()
al3.horz = current_obj.xls_theme_id.body_header_alignment
body_header_style.alignment = al3
final_arr_data = {}
filename = 'General-Ledger-Report.xls'
ledger_obj = self.pool.get("account.report.general.ledger")
worksheet = wb.add_sheet("GENERAL-LEDGER" + ".xls")
header = current_obj.company_id.name+':'+'General Ledger'
worksheet.write_merge(0, 0, 0, 8, header, header_style)
journal_names = []
journal_string = ''
for journal_name in self.env['account.journal'].browse(data['form']['journal_ids']):
journal_names.append(journal_name.code)
journal_string += journal_name.code + ','
header_header_list = ["Journals:", "Display Account:", "Sorted By:", "Target Moves:"]
header_data_list = [journal_string, display_account, sortby, target_move]
header_data = dict(zip(header_header_list, header_data_list))
row = col = 1
for key in header_header_list:
worksheet.write(row, col, key, column_header_style)
row+=1
worksheet.write(row, col, header_data[key], body_header_style)
#if key == 'Filter By:' and header_data[key] in ['Filtered by date', 'Filtered by period']:
# per_row = row+1
# for per in period:
# worksheet.write(per_row, col, per, body_header_style)
# per_row+=1
row-=1
col+=1
# sending row cursor after 3 new rows
row +=6
col = 1
body_header_list = ["DATE", "JRNL", "Partner", "Ref", "Move", "Entry Label", "Debit", "Credit", "Balance"]
for header in body_header_list:
worksheet.write(row, col, header, column_header_style)
col+=1
row+=1
col=1
tot_currency = 0.0
company_name = self.company_id.name
for i in range(1,15):
column = worksheet.col(i)
column.width = 225 * 30
body_body_list = ['ldate', 'lcode', 'partner_name', 'lref', 'move_name', 'lname', 'debit', 'credit', 'balance']
for account in accounts_res:
col = 1
row+=1
worksheet.write(row, col, account['code'], body_header_style)
col+=1
worksheet.write(row, col, account['name'], body_header_style)
col+=5
worksheet.write(row, col, formatLang(self.env, account['debit'], currency_obj=current_obj.company_id.currency_id), body_header_style)
col+=1
worksheet.write(row, col, formatLang(self.env, account['credit'], currency_obj=current_obj.company_id.currency_id), body_header_style)
col+=1
worksheet.write(row, col, formatLang(self.env, account['balance'], currency_obj=current_obj.company_id.currency_id), body_header_style)
for line in account['move_lines']:
col =1
row+=1
for item in body_body_list:
if item == 'debit':
line[item] = formatLang(self.env, line[item], currency_obj=current_obj.company_id.currency_id)
elif item == 'credit':
line[item] = formatLang(self.env, line[item], currency_obj=current_obj.company_id.currency_id)
elif item == 'balance':
line[item] = formatLang(self.env, line[item], currency_obj=current_obj.company_id.currency_id)
worksheet.write(row, col, line[item], body_header_style)
col += 1
wb.save(fp)
out = base64.encodestring(fp.getvalue())
final_arr_data = {}
final_arr_data['file_stream'] = out
final_arr_data['name'] = filename
create_id = self.env['account.report.view'].create(final_arr_data)
return {
'nodestroy': True,
'res_id': create_id.id,
'name': filename,
'view_type': 'form',
'view_mode': 'form',
'res_model': 'account.report.view',
'view_id': False,
'type': 'ir.actions.act_window',
}
def print_xls(self):
report_obj = self.env['report.flexipharmacy.tax_report_template']
data = {}
data['ids'] = self.env.context.get('active_ids', [])
data['model'] = self.env.context.get('active_model', 'ir.ui.menu')
data['form'] = self.read(['date_from', 'date_to'])[0]
datas = {
'ids': self._ids,
'docs': self._ids,
'model': 'tax.report.wiz',
'form': data['form']
}
report_data = report_obj.with_context(
active_model='tax.report.wiz')._get_report_values(self, data=datas)
styleP = xlwt.XFStyle()
stylePC = xlwt.XFStyle()
styleBorder = xlwt.XFStyle()
text_right = xlwt.XFStyle()
fontbold = xlwt.XFStyle()
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment_right = xlwt.Alignment()
alignment_right.horz = xlwt.Alignment.HORZ_RIGHT
text_r = xlwt.Alignment()
text_r.horz = xlwt.Alignment.HORZ_RIGHT
stylePC.alignment = alignment_right
font = xlwt.Font()
fontP = xlwt.Font()
text_right.font = fontP
text_right.alignment = text_r
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
font.bold = False
fontP.bold = True
styleP.font = font
# stylePC.font = fontP
stylePC.alignment = alignment_right
styleBorder.font = fontP
fontbold.font = fontP
styleBorder.alignment = alignment
styleBorder.borders = borders
workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("Tax Sheet")
worksheet.col(0).width = 6000
worksheet.col(1).width = 5600
worksheet.col(2).width = 5600
worksheet.write_merge(0,
2,
0,
2,
self.company_id.name + '\n' +
self.company_id.email + '\n' +
self.company_id.phone,
style=styleBorder)
worksheet.write_merge(
3, 3, 0, 2,
'From ' + str(self.date_from) + ' To ' + str(self.date_to),
styleBorder)
worksheet.write_merge(4, 4, 0, 2, 'Tax Report', styleBorder)
worksheet.write_merge(5, 5, 1, 1, 'Net', text_right)
worksheet.write_merge(5, 5, 2, 2, 'Tax', text_right)
i = 6
if report_data.get('taxes'):
for each in report_data.get('taxes'):
worksheet.write_merge(i, i, 0, 2, each, fontbold)
i = i + 1
if report_data.get('taxes').get(each):
for each in report_data.get('taxes')[each]:
worksheet.write_merge(i, i, 0, 0, each.get('name'))
worksheet.write_merge(
i, i, 1, 1,
formatLang(self.env,
float(each.get('base')),
currency_obj=self.env.user.company_id.
currency_id), stylePC)
worksheet.write_merge(
i, i, 2, 2,
formatLang(self.env,
float(each.get('amount')),
currency_obj=self.env.user.company_id.
currency_id), stylePC)
i = i + 1
file_data = io.BytesIO()
workbook.save(file_data)
self.write({
'state': 'get',
'data': base64.encodestring(file_data.getvalue()),
'name': 'tax_report.xls'
})
return {
'name': 'Tax Report',
'type': 'ir.actions.act_window',
'res_model': 'tax.report.wiz',
'view_mode': 'form',
'view_type': 'form',
'res_id': self.id,
'target': 'new'
}
def get_journal_dashboard_datas(self):
currency = self.currency_id or self.company_id.currency_id
number_to_reconcile = last_balance = account_sum = 0
title = ''
number_draft = number_waiting = number_late = 0
sum_draft = sum_waiting = sum_late = 0.0
if self.type in ['bank', 'cash']:
last_bank_stmt = self.env['account.bank.statement'].search([('journal_id', 'in', self.ids)], order="date desc, id desc", limit=1)
last_balance = last_bank_stmt and last_bank_stmt[0].balance_end or 0
#Get the number of items to reconcile for that bank journal
self.env.cr.execute("""SELECT COUNT(DISTINCT(line.id))
FROM account_bank_statement_line AS line
LEFT JOIN account_bank_statement AS st
ON line.statement_id = st.id
WHERE st.journal_id IN %s AND st.state = 'open' AND line.amount != 0.0
AND not exists (select 1 from account_move_line aml where aml.statement_line_id = line.id)
""", (tuple(self.ids),))
number_to_reconcile = self.env.cr.fetchone()[0]
# optimization to read sum of balance from account_move_line
account_ids = tuple(ac for ac in [self.default_debit_account_id.id, self.default_credit_account_id.id] if ac)
if account_ids:
amount_field = 'balance' if (not self.currency_id or self.currency_id == self.company_id.currency_id) else 'amount_currency'
query = """SELECT sum(%s) FROM account_move_line WHERE account_id in %%s AND date <= %%s;""" % (amount_field,)
self.env.cr.execute(query, (account_ids, fields.Date.today(),))
query_results = self.env.cr.dictfetchall()
if query_results and query_results[0].get('sum') != None:
account_sum = query_results[0].get('sum')
#TODO need to check if all invoices are in the same currency than the journal!!!!
elif self.type in ['sale', 'purchase']:
title = _('Bills to pay') if self.type == 'purchase' else _('Invoices owed to you')
(query, query_args) = self._get_open_bills_to_pay_query()
self.env.cr.execute(query, query_args)
query_results_to_pay = self.env.cr.dictfetchall()
(query, query_args) = self._get_draft_bills_query()
self.env.cr.execute(query, query_args)
query_results_drafts = self.env.cr.dictfetchall()
today = datetime.today()
query = """SELECT amount_total, currency_id AS currency, type FROM account_invoice WHERE journal_id = %s AND date < %s AND state = 'open';"""
self.env.cr.execute(query, (self.id, today))
late_query_results = self.env.cr.dictfetchall()
(number_waiting, sum_waiting) = self._count_results_and_sum_amounts(query_results_to_pay, currency)
(number_draft, sum_draft) = self._count_results_and_sum_amounts(query_results_drafts, currency)
(number_late, sum_late) = self._count_results_and_sum_amounts(late_query_results, currency)
difference = currency.round(last_balance-account_sum) + 0.0
return {
'number_to_reconcile': number_to_reconcile,
'account_balance': formatLang(self.env, currency.round(account_sum) + 0.0, currency_obj=currency),
'last_balance': formatLang(self.env, currency.round(last_balance) + 0.0, currency_obj=currency),
'difference': formatLang(self.env, difference, currency_obj=currency) if difference else False,
'number_draft': number_draft,
'number_waiting': number_waiting,
'number_late': number_late,
'sum_draft': formatLang(self.env, currency.round(sum_draft) + 0.0, currency_obj=currency),
'sum_waiting': formatLang(self.env, currency.round(sum_waiting) + 0.0, currency_obj=currency),
'sum_late': formatLang(self.env, currency.round(sum_late) + 0.0, currency_obj=currency),
'currency_id': currency.id,
'bank_statements_source': self.bank_statements_source,
'title': title,
}
def get_lines(self, context_id, line_id=None):
lines = []
partner_ids = self.env['res.partner'].search([('vat', 'ilike', 'BE%')
]).ids
if not partner_ids:
return lines
company_clauses = ['AND FALSE', 'AND FALSE']
if context_id.company_ids.ids:
company_ids = '(' + ','.join(map(str,
context_id.company_ids.ids)) + ')'
company_clauses = [
'AND l.company_id IN ' + company_ids,
'AND l2.company_id IN ' + company_ids
]
tag_ids = [
self.env['ir.model.data'].xmlid_to_res_id(k) for k in [
'l10n_be.tax_tag_00', 'l10n_be.tax_tag_01',
'l10n_be.tax_tag_02', 'l10n_be.tax_tag_03',
'l10n_be.tax_tag_45'
]
]
tag_ids_2 = [
self.env['ir.model.data'].xmlid_to_res_id(k) for k in
['l10n_be.tax_tag_01', 'l10n_be.tax_tag_02', 'l10n_be.tax_tag_03']
]
query = """SELECT sub1.partner_id, sub1.name, sub1.vat, sub1.turnover, sub2.vat_amount
FROM (SELECT l.partner_id, p.name, p.vat, SUM(l.credit - l.debit) as turnover
FROM account_move_line l
LEFT JOIN res_partner p ON l.partner_id = p.id AND p.customer = true
LEFT JOIN account_move_line_account_tax_rel amlt ON l.id = amlt.account_move_line_id
LEFT JOIN account_tax_account_tag tt on amlt.account_tax_id = tt.account_tax_id
WHERE tt.account_account_tag_id IN %%s
AND p.vat IS NOT NULL
AND l.partner_id IN %%s
AND l.date >= %%s
AND l.date <= %%s
%s
GROUP BY l.partner_id, p.name, p.vat) AS sub1
LEFT JOIN (SELECT l2.partner_id, SUM(l2.credit - l2.debit) as vat_amount
FROM account_move_line l2
LEFT JOIN account_tax_account_tag tt2 on l2.tax_line_id = tt2.account_tax_id
WHERE tt2.account_account_tag_id IN %%s
AND l2.partner_id IN %%s
AND l2.date >= %%s
AND l2.date <= %%s
%s
GROUP BY l2.partner_id) AS sub2 ON sub1.partner_id = sub2.partner_id
WHERE turnover > 250
""" % tuple(company_clauses)
self.env.cr.execute(
query, (tuple(tag_ids), tuple(partner_ids), context_id.date_from,
context_id.date_to, tuple(tag_ids_2), tuple(partner_ids),
context_id.date_from, context_id.date_to))
for record in self.env.cr.dictfetchall():
currency_id = self.env.user.company_id.currency_id
if not currency_id.is_zero(record['turnover']):
columns = [
record['vat'].replace(' ', '').upper(), record['turnover'],
record['vat_amount']
]
if not self.env.context.get('no_format', False):
columns[1] = formatLang(self.env,
columns[1] or 0.0,
currency_obj=currency_id)
columns[2] = formatLang(self.env,
columns[2] or 0.0,
currency_obj=currency_id)
lines.append({
'id':
record['partner_id'],
'type':
'partner_id',
'name':
record['name'],
'footnotes':
context_id._get_footnotes('partner_id',
record['partner_id']),
'columns':
columns,
'level':
2,
'unfoldable':
False,
'unfolded':
False,
})
return lines
def formatlang(self, amount):
currency = self.env.user.company_id.currency_id
return formatLang(self.env, amount, currency_obj=currency)
def get_journal_dashboard_datas(self):
currency = self.currency_id or self.company_id.currency_id
number_to_reconcile = number_to_check = last_balance = account_sum = 0
title = ''
number_draft = number_waiting = number_late = to_check_balance = 0
sum_draft = sum_waiting = sum_late = 0.0
if self.type in ['bank', 'cash']:
last_bank_stmt = self.env['account.bank.statement'].search([('journal_id', 'in', self.ids)], order="date desc, id desc", limit=1)
last_balance = last_bank_stmt and last_bank_stmt[0].balance_end or 0
#Get the number of items to reconcile for that bank journal
self.env.cr.execute("""SELECT COUNT(DISTINCT(line.id))
FROM account_bank_statement_line AS line
LEFT JOIN account_bank_statement AS st
ON line.statement_id = st.id
WHERE st.journal_id IN %s AND st.state = 'open' AND line.amount != 0.0 AND line.account_id IS NULL
AND not exists (select 1 from account_move_line aml where aml.statement_line_id = line.id)
""", (tuple(self.ids),))
number_to_reconcile = self.env.cr.fetchone()[0]
to_check_ids = self.to_check_ids()
number_to_check = len(to_check_ids)
to_check_balance = sum([r.amount for r in to_check_ids])
# optimization to read sum of balance from account_move_line
account_ids = tuple(ac for ac in [self.default_debit_account_id.id, self.default_credit_account_id.id] if ac)
if account_ids:
amount_field = 'aml.balance' if (not self.currency_id or self.currency_id == self.company_id.currency_id) else 'aml.amount_currency'
query = """SELECT sum(%s) FROM account_move_line aml
LEFT JOIN account_move move ON aml.move_id = move.id
WHERE aml.account_id in %%s
AND move.date <= %%s AND move.state = 'posted';""" % (amount_field,)
self.env.cr.execute(query, (account_ids, fields.Date.context_today(self),))
query_results = self.env.cr.dictfetchall()
if query_results and query_results[0].get('sum') != None:
account_sum = query_results[0].get('sum')
#TODO need to check if all invoices are in the same currency than the journal!!!!
elif self.type in ['sale', 'purchase']:
title = _('Bills to pay') if self.type == 'purchase' else _('Invoices owed to you')
(query, query_args) = self._get_open_bills_to_pay_query()
self.env.cr.execute(query, query_args)
query_results_to_pay = self.env.cr.dictfetchall()
(query, query_args) = self._get_draft_bills_query()
self.env.cr.execute(query, query_args)
query_results_drafts = self.env.cr.dictfetchall()
today = fields.Date.today()
query = """SELECT residual_signed as amount_total, currency_id AS currency, type, date_invoice, company_id FROM account_invoice WHERE journal_id = %s AND date <= %s AND state = 'open';"""
self.env.cr.execute(query, (self.id, today))
late_query_results = self.env.cr.dictfetchall()
curr_cache = {}
(number_waiting, sum_waiting) = self._count_results_and_sum_amounts(query_results_to_pay, currency, curr_cache=curr_cache)
(number_draft, sum_draft) = self._count_results_and_sum_amounts(query_results_drafts, currency, curr_cache=curr_cache)
(number_late, sum_late) = self._count_results_and_sum_amounts(late_query_results, currency, curr_cache=curr_cache)
read = self.env['account.move'].read_group([('journal_id', '=', self.id), ('to_check', '=', True)], ['amount'], 'journal_id', lazy=False)
if read:
number_to_check = read[0]['__count']
to_check_balance = read[0]['amount']
difference = currency.round(last_balance-account_sum) + 0.0
is_sample_data = self.kanban_dashboard_graph and any(data.get('is_sample_data', False) for data in json.loads(self.kanban_dashboard_graph))
return {
'number_to_check': number_to_check,
'to_check_balance': formatLang(self.env, to_check_balance, currency_obj=currency),
'number_to_reconcile': number_to_reconcile,
'account_balance': formatLang(self.env, currency.round(account_sum) + 0.0, currency_obj=currency),
'last_balance': formatLang(self.env, currency.round(last_balance) + 0.0, currency_obj=currency),
'difference': formatLang(self.env, difference, currency_obj=currency) if difference else False,
'number_draft': number_draft,
'number_waiting': number_waiting,
'number_late': number_late,
'sum_draft': formatLang(self.env, currency.round(sum_draft) + 0.0, currency_obj=currency),
'sum_waiting': formatLang(self.env, currency.round(sum_waiting) + 0.0, currency_obj=currency),
'sum_late': formatLang(self.env, currency.round(sum_late) + 0.0, currency_obj=currency),
'currency_id': currency.id,
'bank_statements_source': self.bank_statements_source,
'title': title,
'is_sample_data': is_sample_data,
}
def _get_reward_values_discount(self, program):
if program.discount_type == 'fixed_amount':
return [{
'name': _("Discount: ") + program.name,
'product_id': program.discount_line_product_id.id,
'price_unit': - self._get_reward_values_discount_fixed_amount(program),
'product_uom_qty': 1.0,
'product_uom': program.discount_line_product_id.uom_id.id,
'is_reward_line': True,
'tax_id': [(4, tax.id, False) for tax in program.discount_line_product_id.taxes_id],
}]
reward_dict = {}
lines = self._get_paid_order_lines()
if program.discount_apply_on == 'cheapest_product':
line = self._get_cheapest_line()
if line:
discount_line_amount = line.price_unit * (program.discount_percentage / 100)
if discount_line_amount:
taxes = line.tax_id
if self.fiscal_position_id:
taxes = self.fiscal_position_id.map_tax(taxes)
reward_dict[line.tax_id] = {
'name': _("Discount: ") + program.name,
'product_id': program.discount_line_product_id.id,
'price_unit': - discount_line_amount,
'product_uom_qty': 1.0,
'product_uom': program.discount_line_product_id.uom_id.id,
'is_reward_line': True,
'tax_id': [(4, tax.id, False) for tax in taxes],
}
elif program.discount_apply_on in ['specific_product', 'on_order']:
if program.discount_apply_on == 'specific_product':
# We should not exclude reward line that offer this product since we need to offer only the discount on the real paid product (regular product - free product)
free_product_lines = self.env['sale.coupon.program'].search([('reward_type', '=', 'product'), ('reward_product_id', '=', program.discount_specific_product_id.id)]).mapped('discount_line_product_id')
lines = lines.filtered(lambda x: x.product_id == program.discount_specific_product_id or x.product_id in free_product_lines)
for line in lines:
discount_line_amount = self._get_reward_values_discount_percentage_per_line(program, line)
if discount_line_amount:
if line.tax_id in reward_dict:
reward_dict[line.tax_id]['price_unit'] -= discount_line_amount
else:
taxes = line.tax_id
if self.fiscal_position_id:
taxes = self.fiscal_position_id.map_tax(taxes)
tax_name = ""
if len(taxes) == 1:
tax_name = " - " + _("On product with following tax: ") + ', '.join(taxes.mapped('name'))
elif len(taxes) > 1:
tax_name = " - " + _("On product with following taxes: ") + ', '.join(taxes.mapped('name'))
reward_dict[line.tax_id] = {
'name': _("Discount: ") + program.name + tax_name,
'product_id': program.discount_line_product_id.id,
'price_unit': - discount_line_amount,
'product_uom_qty': 1.0,
'product_uom': program.discount_line_product_id.uom_id.id,
'is_reward_line': True,
'tax_id': [(4, tax.id, False) for tax in taxes],
}
# If there is a max amount for discount, we might have to limit some discount lines or completely remove some lines
max_amount = program._compute_program_amount('discount_max_amount', self.currency_id)
if max_amount > 0:
amount_already_given = 0
for val in list(reward_dict):
amount_to_discount = amount_already_given + reward_dict[val]["price_unit"]
if abs(amount_to_discount) > max_amount:
reward_dict[val]["price_unit"] = - (max_amount - abs(amount_already_given))
add_name = formatLang(self.env, max_amount, currency_obj=self.currency_id)
reward_dict[val]["name"] += "( " + _("limited to ") + add_name + ")"
amount_already_given += reward_dict[val]["price_unit"]
if reward_dict[val]["price_unit"] == 0:
del reward_dict[val]
return reward_dict.values()
