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()