1import json 2from datetime import datetime, timedelta 3 4from babel.dates import format_datetime, format_date 5from odoo import models, api, _, fields 6from odoo.osv import expression 7from odoo.release import version 8from odoo.tools import DEFAULT_SERVER_DATE_FORMAT as DF 9from odoo.tools.misc import formatLang, format_date as odoo_format_date, get_lang 10import random 11 12import ast 13 14 15class account_journal(models.Model): 16 _inherit = "account.journal" 17 18 def _kanban_dashboard(self): 19 for journal in self: 20 journal.kanban_dashboard = json.dumps(journal.get_journal_dashboard_datas()) 21 22 def _kanban_dashboard_graph(self): 23 for journal in self: 24 if (journal.type in ['sale', 'purchase']): 25 journal.kanban_dashboard_graph = json.dumps(journal.get_bar_graph_datas()) 26 elif (journal.type in ['cash', 'bank']): 27 journal.kanban_dashboard_graph = json.dumps(journal.get_line_graph_datas()) 28 else: 29 journal.kanban_dashboard_graph = False 30 31 def _get_json_activity_data(self): 32 for journal in self: 33 activities = [] 34 # search activity on move on the journal 35 sql_query = ''' 36 SELECT act.id, 37 act.res_id, 38 act.res_model, 39 act.summary, 40 act_type.name as act_type_name, 41 act_type.category as activity_category, 42 act.date_deadline, 43 m.date, 44 m.ref, 45 CASE WHEN act.date_deadline < CURRENT_DATE THEN 'late' ELSE 'future' END as status 46 FROM account_move m 47 LEFT JOIN mail_activity act ON act.res_id = m.id 48 LEFT JOIN mail_activity_type act_type ON act.activity_type_id = act_type.id 49 WHERE act.res_model = 'account.move' 50 AND m.journal_id = %s 51 ''' 52 self.env.cr.execute(sql_query, (journal.id,)) 53 for activity in self.env.cr.dictfetchall(): 54 act = { 55 'id': activity.get('id'), 56 'res_id': activity.get('res_id'), 57 'res_model': activity.get('res_model'), 58 'status': activity.get('status'), 59 'name': (activity.get('summary') or activity.get('act_type_name')), 60 'activity_category': activity.get('activity_category'), 61 'date': odoo_format_date(self.env, activity.get('date_deadline')) 62 } 63 if activity.get('activity_category') == 'tax_report' and activity.get('res_model') == 'account.move': 64 act['name'] = activity.get('ref') 65 66 activities.append(act) 67 journal.json_activity_data = json.dumps({'activities': activities}) 68 69 kanban_dashboard = fields.Text(compute='_kanban_dashboard') 70 kanban_dashboard_graph = fields.Text(compute='_kanban_dashboard_graph') 71 json_activity_data = fields.Text(compute='_get_json_activity_data') 72 show_on_dashboard = fields.Boolean(string='Show journal on dashboard', help="Whether this journal should be displayed on the dashboard or not", default=True) 73 color = fields.Integer("Color Index", default=0) 74 75 def _graph_title_and_key(self): 76 if self.type in ['sale', 'purchase']: 77 return ['', _('Residual amount')] 78 elif self.type == 'cash': 79 return ['', _('Cash: Balance')] 80 elif self.type == 'bank': 81 return ['', _('Bank: Balance')] 82 83 # Below method is used to get data of bank and cash statemens 84 def get_line_graph_datas(self): 85 """Computes the data used to display the graph for bank and cash journals in the accounting dashboard""" 86 currency = self.currency_id or self.company_id.currency_id 87 88 def build_graph_data(date, amount): 89 #display date in locale format 90 name = format_date(date, 'd LLLL Y', locale=locale) 91 short_name = format_date(date, 'd MMM', locale=locale) 92 return {'x':short_name,'y': amount, 'name':name} 93 94 self.ensure_one() 95 BankStatement = self.env['account.bank.statement'] 96 data = [] 97 today = datetime.today() 98 last_month = today + timedelta(days=-30) 99 locale = get_lang(self.env).code 100 101 #starting point of the graph is the last statement 102 last_stmt = self._get_last_bank_statement(domain=[('move_id.state', '=', 'posted')]) 103 104 last_balance = last_stmt and last_stmt.balance_end_real or 0 105 data.append(build_graph_data(today, last_balance)) 106 107 #then we subtract the total amount of bank statement lines per day to get the previous points 108 #(graph is drawn backward) 109 date = today 110 amount = last_balance 111 query = ''' 112 SELECT move.date, sum(st_line.amount) as amount 113 FROM account_bank_statement_line st_line 114 JOIN account_move move ON move.id = st_line.move_id 115 WHERE move.journal_id = %s 116 AND move.date > %s 117 AND move.date <= %s 118 GROUP BY move.date 119 ORDER BY move.date desc 120 ''' 121 self.env.cr.execute(query, (self.id, last_month, today)) 122 query_result = self.env.cr.dictfetchall() 123 for val in query_result: 124 date = val['date'] 125 if date != today.strftime(DF): # make sure the last point in the graph is today 126 data[:0] = [build_graph_data(date, amount)] 127 amount = currency.round(amount - val['amount']) 128 129 # make sure the graph starts 1 month ago 130 if date.strftime(DF) != last_month.strftime(DF): 131 data[:0] = [build_graph_data(last_month, amount)] 132 133 [graph_title, graph_key] = self._graph_title_and_key() 134 color = '#875A7B' if 'e' in version else '#7c7bad' 135 136 is_sample_data = not last_stmt and len(query_result) == 0 137 if is_sample_data: 138 data = [] 139 for i in range(30, 0, -5): 140 current_date = today + timedelta(days=-i) 141 data.append(build_graph_data(current_date, random.randint(-5, 15))) 142 143 return [{'values': data, 'title': graph_title, 'key': graph_key, 'area': True, 'color': color, 'is_sample_data': is_sample_data}] 144 145 def get_bar_graph_datas(self): 146 data = [] 147 today = fields.Datetime.now(self) 148 data.append({'label': _('Due'), 'value':0.0, 'type': 'past'}) 149 day_of_week = int(format_datetime(today, 'e', locale=get_lang(self.env).code)) 150 first_day_of_week = today + timedelta(days=-day_of_week+1) 151 for i in range(-1,4): 152 if i==0: 153 label = _('This Week') 154 elif i==3: 155 label = _('Not Due') 156 else: 157 start_week = first_day_of_week + timedelta(days=i*7) 158 end_week = start_week + timedelta(days=6) 159 if start_week.month == end_week.month: 160 label = str(start_week.day) + '-' + str(end_week.day) + ' ' + format_date(end_week, 'MMM', locale=get_lang(self.env).code) 161 else: 162 label = format_date(start_week, 'd MMM', locale=get_lang(self.env).code) + '-' + format_date(end_week, 'd MMM', locale=get_lang(self.env).code) 163 data.append({'label':label,'value':0.0, 'type': 'past' if i<0 else 'future'}) 164 165 # Build SQL query to find amount aggregated by week 166 (select_sql_clause, query_args) = self._get_bar_graph_select_query() 167 query = '' 168 start_date = (first_day_of_week + timedelta(days=-7)) 169 for i in range(0,6): 170 if i == 0: 171 query += "("+select_sql_clause+" and invoice_date_due < '"+start_date.strftime(DF)+"')" 172 elif i == 5: 173 query += " UNION ALL ("+select_sql_clause+" and invoice_date_due >= '"+start_date.strftime(DF)+"')" 174 else: 175 next_date = start_date + timedelta(days=7) 176 query += " UNION ALL ("+select_sql_clause+" and invoice_date_due >= '"+start_date.strftime(DF)+"' and invoice_date_due < '"+next_date.strftime(DF)+"')" 177 start_date = next_date 178 179 self.env.cr.execute(query, query_args) 180 query_results = self.env.cr.dictfetchall() 181 is_sample_data = True 182 for index in range(0, len(query_results)): 183 if query_results[index].get('aggr_date') != None: 184 is_sample_data = False 185 data[index]['value'] = query_results[index].get('total') 186 187 [graph_title, graph_key] = self._graph_title_and_key() 188 189 if is_sample_data: 190 for index in range(0, len(query_results)): 191 data[index]['type'] = 'o_sample_data' 192 # we use unrealistic values for the sample data 193 data[index]['value'] = random.randint(0, 20) 194 graph_key = _('Sample data') 195 196 return [{'values': data, 'title': graph_title, 'key': graph_key, 'is_sample_data': is_sample_data}] 197 198 def _get_bar_graph_select_query(self): 199 """ 200 Returns a tuple containing the base SELECT SQL query used to gather 201 the bar graph's data as its first element, and the arguments dictionary 202 for it as its second. 203 """ 204 sign = '' if self.type == 'sale' else '-' 205 return (''' 206 SELECT 207 ''' + sign + ''' + SUM(move.amount_residual_signed) AS total, 208 MIN(invoice_date_due) AS aggr_date 209 FROM account_move move 210 WHERE move.journal_id = %(journal_id)s 211 AND move.state = 'posted' 212 AND move.payment_state in ('not_paid', 'partial') 213 AND move.move_type IN %(invoice_types)s 214 ''', { 215 'invoice_types': tuple(self.env['account.move'].get_invoice_types(True)), 216 'journal_id': self.id 217 }) 218 219 def get_journal_dashboard_datas(self): 220 currency = self.currency_id or self.company_id.currency_id 221 number_to_reconcile = number_to_check = last_balance = 0 222 has_at_least_one_statement = False 223 bank_account_balance = nb_lines_bank_account_balance = 0 224 outstanding_pay_account_balance = nb_lines_outstanding_pay_account_balance = 0 225 title = '' 226 number_draft = number_waiting = number_late = to_check_balance = 0 227 sum_draft = sum_waiting = sum_late = 0.0 228 if self.type in ('bank', 'cash'): 229 last_statement = self._get_last_bank_statement( 230 domain=[('move_id.state', '=', 'posted')]) 231 last_balance = last_statement.balance_end 232 has_at_least_one_statement = bool(last_statement) 233 bank_account_balance, nb_lines_bank_account_balance = self._get_journal_bank_account_balance( 234 domain=[('move_id.state', '=', 'posted')]) 235 outstanding_pay_account_balance, nb_lines_outstanding_pay_account_balance = self._get_journal_outstanding_payments_account_balance( 236 domain=[('move_id.state', '=', 'posted')]) 237 238 self._cr.execute(''' 239 SELECT COUNT(st_line.id) 240 FROM account_bank_statement_line st_line 241 JOIN account_move st_line_move ON st_line_move.id = st_line.move_id 242 JOIN account_bank_statement st ON st_line.statement_id = st.id 243 WHERE st_line_move.journal_id IN %s 244 AND st.state = 'posted' 245 AND NOT st_line.is_reconciled 246 ''', [tuple(self.ids)]) 247 number_to_reconcile = self.env.cr.fetchone()[0] 248 249 to_check_ids = self.to_check_ids() 250 number_to_check = len(to_check_ids) 251 to_check_balance = sum([r.amount for r in to_check_ids]) 252 #TODO need to check if all invoices are in the same currency than the journal!!!! 253 elif self.type in ['sale', 'purchase']: 254 title = _('Bills to pay') if self.type == 'purchase' else _('Invoices owed to you') 255 self.env['account.move'].flush(['amount_residual', 'currency_id', 'move_type', 'invoice_date', 'company_id', 'journal_id', 'date', 'state', 'payment_state']) 256 257 (query, query_args) = self._get_open_bills_to_pay_query() 258 self.env.cr.execute(query, query_args) 259 query_results_to_pay = self.env.cr.dictfetchall() 260 261 (query, query_args) = self._get_draft_bills_query() 262 self.env.cr.execute(query, query_args) 263 query_results_drafts = self.env.cr.dictfetchall() 264 265 today = fields.Date.context_today(self) 266 query = ''' 267 SELECT 268 (CASE WHEN move_type IN ('out_refund', 'in_refund') THEN -1 ELSE 1 END) * amount_residual AS amount_total, 269 currency_id AS currency, 270 move_type, 271 invoice_date, 272 company_id 273 FROM account_move move 274 WHERE journal_id = %s 275 AND invoice_date_due <= %s 276 AND state = 'posted' 277 AND payment_state in ('not_paid', 'partial') 278 AND move_type IN ('out_invoice', 'out_refund', 'in_invoice', 'in_refund', 'out_receipt', 'in_receipt'); 279 ''' 280 self.env.cr.execute(query, (self.id, today)) 281 late_query_results = self.env.cr.dictfetchall() 282 curr_cache = {} 283 (number_waiting, sum_waiting) = self._count_results_and_sum_amounts(query_results_to_pay, currency, curr_cache=curr_cache) 284 (number_draft, sum_draft) = self._count_results_and_sum_amounts(query_results_drafts, currency, curr_cache=curr_cache) 285 (number_late, sum_late) = self._count_results_and_sum_amounts(late_query_results, currency, curr_cache=curr_cache) 286 read = self.env['account.move'].read_group([('journal_id', '=', self.id), ('to_check', '=', True)], ['amount_total'], 'journal_id', lazy=False) 287 if read: 288 number_to_check = read[0]['__count'] 289 to_check_balance = read[0]['amount_total'] 290 elif self.type == 'general': 291 read = self.env['account.move'].read_group([('journal_id', '=', self.id), ('to_check', '=', True)], ['amount_total'], 'journal_id', lazy=False) 292 if read: 293 number_to_check = read[0]['__count'] 294 to_check_balance = read[0]['amount_total'] 295 296 is_sample_data = self.kanban_dashboard_graph and any(data.get('is_sample_data', False) for data in json.loads(self.kanban_dashboard_graph)) 297 298 return { 299 'number_to_check': number_to_check, 300 'to_check_balance': formatLang(self.env, to_check_balance, currency_obj=currency), 301 'number_to_reconcile': number_to_reconcile, 302 'account_balance': formatLang(self.env, currency.round(bank_account_balance), currency_obj=currency), 303 'has_at_least_one_statement': has_at_least_one_statement, 304 'nb_lines_bank_account_balance': nb_lines_bank_account_balance, 305 'outstanding_pay_account_balance': formatLang(self.env, currency.round(outstanding_pay_account_balance), currency_obj=currency), 306 'nb_lines_outstanding_pay_account_balance': nb_lines_outstanding_pay_account_balance, 307 'last_balance': formatLang(self.env, currency.round(last_balance) + 0.0, currency_obj=currency), 308 'number_draft': number_draft, 309 'number_waiting': number_waiting, 310 'number_late': number_late, 311 'sum_draft': formatLang(self.env, currency.round(sum_draft) + 0.0, currency_obj=currency), 312 'sum_waiting': formatLang(self.env, currency.round(sum_waiting) + 0.0, currency_obj=currency), 313 'sum_late': formatLang(self.env, currency.round(sum_late) + 0.0, currency_obj=currency), 314 'currency_id': currency.id, 315 'bank_statements_source': self.bank_statements_source, 316 'title': title, 317 'is_sample_data': is_sample_data, 318 'company_count': len(self.env.companies) 319 } 320 321 def _get_open_bills_to_pay_query(self): 322 """ 323 Returns a tuple containing the SQL query used to gather the open bills 324 data as its first element, and the arguments dictionary to use to run 325 it as its second. 326 """ 327 return (''' 328 SELECT 329 (CASE WHEN move.move_type IN ('out_refund', 'in_refund') THEN -1 ELSE 1 END) * move.amount_residual AS amount_total, 330 move.currency_id AS currency, 331 move.move_type, 332 move.invoice_date, 333 move.company_id 334 FROM account_move move 335 WHERE move.journal_id = %(journal_id)s 336 AND move.state = 'posted' 337 AND move.payment_state in ('not_paid', 'partial') 338 AND move.move_type IN ('out_invoice', 'out_refund', 'in_invoice', 'in_refund', 'out_receipt', 'in_receipt'); 339 ''', {'journal_id': self.id}) 340 341 def _get_draft_bills_query(self): 342 """ 343 Returns a tuple containing as its first element the SQL query used to 344 gather the bills in draft state data, and the arguments 345 dictionary to use to run it as its second. 346 """ 347 return (''' 348 SELECT 349 (CASE WHEN move.move_type IN ('out_refund', 'in_refund') THEN -1 ELSE 1 END) * move.amount_total AS amount_total, 350 move.currency_id AS currency, 351 move.move_type, 352 move.invoice_date, 353 move.company_id 354 FROM account_move move 355 WHERE move.journal_id = %(journal_id)s 356 AND move.state = 'draft' 357 AND move.payment_state in ('not_paid', 'partial') 358 AND move.move_type IN ('out_invoice', 'out_refund', 'in_invoice', 'in_refund', 'out_receipt', 'in_receipt'); 359 ''', {'journal_id': self.id}) 360 361 def _count_results_and_sum_amounts(self, results_dict, target_currency, curr_cache=None): 362 """ Loops on a query result to count the total number of invoices and sum 363 their amount_total field (expressed in the given target currency). 364 amount_total must be signed ! 365 """ 366 rslt_count = 0 367 rslt_sum = 0.0 368 # Create a cache with currency rates to avoid unnecessary SQL requests. Do not copy 369 # curr_cache on purpose, so the dictionary is modified and can be re-used for subsequent 370 # calls of the method. 371 curr_cache = {} if curr_cache is None else curr_cache 372 for result in results_dict: 373 cur = self.env['res.currency'].browse(result.get('currency')) 374 company = self.env['res.company'].browse(result.get('company_id')) or self.env.company 375 rslt_count += 1 376 date = result.get('invoice_date') or fields.Date.context_today(self) 377 378 amount = result.get('amount_total', 0) or 0 379 if cur != target_currency: 380 key = (cur, target_currency, company, date) 381 # Using setdefault will call _get_conversion_rate, so we explicitly check the 382 # existence of the key in the cache instead. 383 if key not in curr_cache: 384 curr_cache[key] = self.env['res.currency']._get_conversion_rate(*key) 385 amount *= curr_cache[key] 386 rslt_sum += target_currency.round(amount) 387 return (rslt_count, rslt_sum) 388 389 def action_create_new(self): 390 ctx = self._context.copy() 391 ctx['default_journal_id'] = self.id 392 if self.type == 'sale': 393 ctx['default_move_type'] = 'out_refund' if ctx.get('refund') else 'out_invoice' 394 elif self.type == 'purchase': 395 ctx['default_move_type'] = 'in_refund' if ctx.get('refund') else 'in_invoice' 396 else: 397 ctx['default_move_type'] = 'entry' 398 ctx['view_no_maturity'] = True 399 return { 400 'name': _('Create invoice/bill'), 401 'type': 'ir.actions.act_window', 402 'view_mode': 'form', 403 'res_model': 'account.move', 404 'view_id': self.env.ref('account.view_move_form').id, 405 'context': ctx, 406 } 407 408 def create_cash_statement(self): 409 ctx = self._context.copy() 410 ctx.update({'journal_id': self.id, 'default_journal_id': self.id, 'default_journal_type': 'cash'}) 411 open_statements = self.env['account.bank.statement'].search([('journal_id', '=', self.id), ('state', '=', 'open')]) 412 action = { 413 'name': _('Create cash statement'), 414 'type': 'ir.actions.act_window', 415 'view_mode': 'form', 416 'res_model': 'account.bank.statement', 417 'context': ctx, 418 } 419 if len(open_statements) == 1: 420 action.update({ 421 'view_mode': 'form', 422 'res_id': open_statements.id, 423 }) 424 elif len(open_statements) > 1: 425 action.update({ 426 'view_mode': 'tree,form', 427 'domain': [('id', 'in', open_statements.ids)], 428 }) 429 return action 430 431 def to_check_ids(self): 432 self.ensure_one() 433 domain = self.env['account.move.line']._get_suspense_moves_domain() 434 domain.append(('journal_id', '=', self.id)) 435 statement_line_ids = self.env['account.move.line'].search(domain).mapped('statement_line_id') 436 return statement_line_ids 437 438 def _select_action_to_open(self): 439 self.ensure_one() 440 if self._context.get('action_name'): 441 return self._context.get('action_name') 442 elif self.type == 'bank': 443 return 'action_bank_statement_tree' 444 elif self.type == 'cash': 445 return 'action_view_bank_statement_tree' 446 elif self.type == 'sale': 447 return 'action_move_out_invoice_type' 448 elif self.type == 'purchase': 449 return 'action_move_in_invoice_type' 450 else: 451 return 'action_move_journal_line' 452 453 def open_action(self): 454 """return action based on type for related journals""" 455 self.ensure_one() 456 action_name = self._select_action_to_open() 457 458 # Set 'account.' prefix if missing. 459 if not action_name.startswith("account."): 460 action_name = 'account.%s' % action_name 461 462 action = self.env["ir.actions.act_window"]._for_xml_id(action_name) 463 464 context = self._context.copy() 465 if 'context' in action and type(action['context']) == str: 466 context.update(ast.literal_eval(action['context'])) 467 else: 468 context.update(action.get('context', {})) 469 action['context'] = context 470 action['context'].update({ 471 'default_journal_id': self.id, 472 'search_default_journal_id': self.id, 473 }) 474 475 domain_type_field = action['res_model'] == 'account.move.line' and 'move_id.move_type' or 'move_type' # The model can be either account.move or account.move.line 476 477 # Override the domain only if the action was not explicitly specified in order to keep the 478 # original action domain. 479 if not self._context.get('action_name'): 480 if self.type == 'sale': 481 action['domain'] = [(domain_type_field, 'in', ('out_invoice', 'out_refund', 'out_receipt'))] 482 elif self.type == 'purchase': 483 action['domain'] = [(domain_type_field, 'in', ('in_invoice', 'in_refund', 'in_receipt', 'entry'))] 484 485 return action 486 487 def open_spend_money(self): 488 return self.open_payments_action('outbound') 489 490 def open_collect_money(self): 491 return self.open_payments_action('inbound') 492 493 def open_transfer_money(self): 494 return self.open_payments_action('transfer') 495 496 def open_payments_action(self, payment_type, mode='tree'): 497 if payment_type == 'outbound': 498 action_ref = 'account.action_account_payments_payable' 499 elif payment_type == 'transfer': 500 action_ref = 'account.action_account_payments_transfer' 501 else: 502 action_ref = 'account.action_account_payments' 503 action = self.env['ir.actions.act_window']._for_xml_id(action_ref) 504 action['context'] = dict(ast.literal_eval(action.get('context')), default_journal_id=self.id, search_default_journal_id=self.id) 505 if payment_type == 'transfer': 506 action['context'].update({ 507 'default_partner_id': self.company_id.partner_id.id, 508 'default_is_internal_transfer': True, 509 }) 510 if mode == 'form': 511 action['views'] = [[False, 'form']] 512 return action 513 514 def open_action_with_context(self): 515 action_name = self.env.context.get('action_name', False) 516 if not action_name: 517 return False 518 ctx = dict(self.env.context, default_journal_id=self.id) 519 if ctx.get('search_default_journal', False): 520 ctx.update(search_default_journal_id=self.id) 521 ctx['search_default_journal'] = False # otherwise it will do a useless groupby in bank statements 522 ctx.pop('group_by', None) 523 action = self.env['ir.actions.act_window']._for_xml_id(f"account.{action_name}") 524 action['context'] = ctx 525 if ctx.get('use_domain', False): 526 action['domain'] = isinstance(ctx['use_domain'], list) and ctx['use_domain'] or ['|', ('journal_id', '=', self.id), ('journal_id', '=', False)] 527 action['name'] = _( 528 "%(action)s for journal %(journal)s", 529 action=action["name"], 530 journal=self.name, 531 ) 532 return action 533 534 def create_bank_statement(self): 535 """return action to create a bank statements. This button should be called only on journals with type =='bank'""" 536 action = self.env["ir.actions.actions"]._for_xml_id("account.action_bank_statement_tree") 537 action.update({ 538 'views': [[False, 'form']], 539 'context': "{'default_journal_id': " + str(self.id) + "}", 540 }) 541 return action 542 543 def create_customer_payment(self): 544 """return action to create a customer payment""" 545 return self.open_payments_action('inbound', mode='form') 546 547 def create_supplier_payment(self): 548 """return action to create a supplier payment""" 549 return self.open_payments_action('outbound', mode='form') 550 551 def create_internal_transfer(self): 552 """return action to create a internal transfer""" 553 return self.open_payments_action('transfer', mode='form') 554 555 ##################### 556 # Setup Steps Stuff # 557 ##################### 558 def mark_bank_setup_as_done_action(self): 559 """ Marks the 'bank setup' step as done in the setup bar and in the company.""" 560 self.company_id.sudo().set_onboarding_step_done('account_setup_bank_data_state') 561 562 def unmark_bank_setup_as_done_action(self): 563 """ Marks the 'bank setup' step as not done in the setup bar and in the company.""" 564 self.company_id.account_setup_bank_data_state = 'not_done' 565