summaryrefslogtreecommitdiff
path: root/account_reports_xlsx/models
diff options
context:
space:
mode:
Diffstat (limited to 'account_reports_xlsx/models')
-rw-r--r--account_reports_xlsx/models/__init__.py26
-rw-r--r--account_reports_xlsx/models/account_aged_partner_balance.py266
-rw-r--r--account_reports_xlsx/models/account_financial_report.py91
-rw-r--r--account_reports_xlsx/models/account_general_ledger.py156
-rw-r--r--account_reports_xlsx/models/account_partner_ledger.py151
5 files changed, 690 insertions, 0 deletions
diff --git a/account_reports_xlsx/models/__init__.py b/account_reports_xlsx/models/__init__.py
new file mode 100644
index 0000000..f7265a3
--- /dev/null
+++ b/account_reports_xlsx/models/__init__.py
@@ -0,0 +1,26 @@
+# -*- coding: utf-8 -*-
+######################################################################################
+#
+# Cybrosys Technologies Pvt. Ltd.
+#
+# Copyright (C) 2020-TODAY Cybrosys Technologies(<https://www.cybrosys.com>).
+# Author: Cybrosys Technologies (odoo@cybrosys.com)
+#
+# This program is under the terms of the Odoo Proprietary License v1.0 (OPL-1)
+# It is forbidden to publish, distribute, sublicense, or sell copies of the Software
+# or modified copies of the Software.
+#
+# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
+# IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
+# DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
+# ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
+# DEALINGS IN THE SOFTWARE.
+#
+########################################################################################
+
+from . import account_partner_ledger
+from . import account_aged_partner_balance
+from . import account_general_ledger
+from . import account_financial_report
diff --git a/account_reports_xlsx/models/account_aged_partner_balance.py b/account_reports_xlsx/models/account_aged_partner_balance.py
new file mode 100644
index 0000000..1f6eef7
--- /dev/null
+++ b/account_reports_xlsx/models/account_aged_partner_balance.py
@@ -0,0 +1,266 @@
+# -*- coding: utf-8 -*-
+######################################################################################
+#
+# Cybrosys Technologies Pvt. Ltd.
+#
+# Copyright (C) 2020-TODAY Cybrosys Technologies(<https://www.cybrosys.com>).
+# Author: Cybrosys Technologies (odoo@cybrosys.com)
+#
+# This program is under the terms of the Odoo Proprietary License v1.0 (OPL-1)
+# It is forbidden to publish, distribute, sublicense, or sell copies of the Software
+# or modified copies of the Software.
+#
+# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
+# IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
+# DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
+# ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
+# DEALINGS IN THE SOFTWARE.
+#
+########################################################################################
+
+import time
+from odoo import api, models, _
+from odoo.exceptions import UserError
+from odoo.tools import float_is_zero
+from datetime import datetime
+from dateutil.relativedelta import relativedelta
+
+
+class ReportAgedPartnerBalance(models.AbstractModel):
+
+ _inherit = 'report.account_reports_xlsx.report_agedpartnerbalance'
+
+ def _get_partner_move_lines(self, account_type, date_from, target_move, period_length):
+ # This method can receive the context key 'include_nullified_amount' {Boolean}
+ # Do an invoice and a payment and unreconcile. The amount will be nullified
+ # By default, the partner wouldn't appear in this report.
+ # The context key allow it to appear
+ # In case of a period_length of 30 days as of 2020-02-08, we want the following periods:
+ # Name Stop Start
+ # 1 - 30 : 2020-02-07 - 2020-01-09
+ # 31 - 60 : 2020-01-08 - 2018-12-10
+ # 61 - 90 : 2018-12-09 - 2018-11-10
+ # 91 - 120 : 2018-11-09 - 2018-10-11
+ # +120 : 2018-10-10
+ periods = {}
+ if date_from is False:
+ raise UserError(_("You must set a start date"))
+ start = datetime.strptime(str(date_from), "%Y-%m-%d")
+ for i in range(5)[::-1]:
+ stop = start - relativedelta(days=period_length)
+ period_name = str((5-(i+1)) * period_length + 1) + '-' + str((5-i) * period_length)
+ period_stop = (start - relativedelta(days=1)).strftime('%Y-%m-%d')
+ if i == 0:
+ period_name = '+' + str(4 * period_length)
+ periods[str(i)] = {
+ 'name': period_name,
+ 'stop': period_stop,
+ 'start': (i!=0 and stop.strftime('%Y-%m-%d') or False),
+ }
+ start = stop
+
+ res = []
+ total = []
+ cr = self.env.cr
+ user_company = self.env.user.company_id
+ user_currency = user_company.currency_id
+ ResCurrency = self.env['res.currency'].with_context(date=date_from)
+ company_ids = self._context.get('company_ids') or [user_company.id]
+ move_state = ['draft', 'posted']
+ if target_move == 'posted':
+ move_state = ['posted']
+ arg_list = (tuple(move_state), tuple(account_type))
+ #build the reconciliation clause to see what partner needs to be printed
+ reconciliation_clause = '(l.reconciled IS FALSE)'
+ cr.execute('SELECT debit_move_id, credit_move_id FROM account_partial_reconcile where max_date > %s', (date_from,))
+ reconciled_after_date = []
+ for row in cr.fetchall():
+ reconciled_after_date += [row[0], row[1]]
+ if reconciled_after_date:
+ reconciliation_clause = '(l.reconciled IS FALSE OR l.id IN %s)'
+ arg_list += (tuple(reconciled_after_date),)
+ arg_list += (date_from, tuple(company_ids))
+ query = '''
+ SELECT DISTINCT l.partner_id, UPPER(res_partner.name)
+ FROM account_move_line AS l left join res_partner on l.partner_id = res_partner.id, account_account, account_move am
+ WHERE (l.account_id = account_account.id)
+ AND (l.move_id = am.id)
+ AND (am.state IN %s)
+ AND (account_account.internal_type IN %s)
+ AND ''' + reconciliation_clause + '''
+ AND (l.date <= %s)
+ AND l.company_id IN %s
+ ORDER BY UPPER(res_partner.name)'''
+ cr.execute(query, arg_list)
+
+ partners = cr.dictfetchall()
+ # put a total of 0
+ for i in range(7):
+ total.append(0)
+
+ # Build a string like (1,2,3) for easy use in SQL query
+ partner_ids = [partner['partner_id'] for partner in partners if partner['partner_id']]
+ lines = dict((partner['partner_id'] or False, []) for partner in partners)
+ if not partner_ids:
+ return [], [], {}
+
+ # This dictionary will store the not due amount of all partners
+ undue_amounts = {}
+ query = '''SELECT l.id
+ FROM account_move_line AS l, account_account, account_move am
+ WHERE (l.account_id = account_account.id) AND (l.move_id = am.id)
+ AND (am.state IN %s)
+ AND (account_account.internal_type IN %s)
+ AND (COALESCE(l.date_maturity,l.date) >= %s)\
+ AND ((l.partner_id IN %s) OR (l.partner_id IS NULL))
+ AND (l.date <= %s)
+ AND l.company_id IN %s'''
+ cr.execute(query, (tuple(move_state), tuple(account_type), date_from, tuple(partner_ids), date_from, tuple(company_ids)))
+ aml_ids = cr.fetchall()
+ aml_ids = aml_ids and [x[0] for x in aml_ids] or []
+ for line in self.env['account.move.line'].browse(aml_ids):
+ partner_id = line.partner_id.id or False
+ if partner_id not in undue_amounts:
+ undue_amounts[partner_id] = 0.0
+ line_amount = ResCurrency._compute(line.company_id.currency_id, user_currency, line.balance)
+ if user_currency.is_zero(line_amount):
+ continue
+ for partial_line in line.matched_debit_ids:
+ if str(partial_line.max_date) <= date_from:
+ line_amount += ResCurrency._compute(partial_line.company_id.currency_id, user_currency, partial_line.amount)
+ for partial_line in line.matched_credit_ids:
+ if str(partial_line.max_date) <= date_from:
+ line_amount -= ResCurrency._compute(partial_line.company_id.currency_id, user_currency, partial_line.amount)
+ if not self.env.user.company_id.currency_id.is_zero(line_amount):
+ undue_amounts[partner_id] += line_amount
+ lines[partner_id].append({
+ 'line': line,
+ 'amount': line_amount,
+ 'period': 6,
+ })
+
+ # Use one query per period and store results in history (a list variable)
+ # Each history will contain: history[1] = {'<partner_id>': <partner_debit-credit>}
+ history = []
+ for i in range(5):
+ args_list = (tuple(move_state), tuple(account_type), tuple(partner_ids),)
+ dates_query = '(COALESCE(l.date_maturity,l.date)'
+
+ if periods[str(i)]['start'] and periods[str(i)]['stop']:
+ dates_query += ' BETWEEN %s AND %s)'
+ args_list += (periods[str(i)]['start'], periods[str(i)]['stop'])
+ elif periods[str(i)]['start']:
+ dates_query += ' >= %s)'
+ args_list += (periods[str(i)]['start'],)
+ else:
+ dates_query += ' <= %s)'
+ args_list += (periods[str(i)]['stop'],)
+ args_list += (date_from, tuple(company_ids))
+
+ query = '''SELECT l.id
+ FROM account_move_line AS l, account_account, account_move am
+ WHERE (l.account_id = account_account.id) AND (l.move_id = am.id)
+ AND (am.state IN %s)
+ AND (account_account.internal_type IN %s)
+ AND ((l.partner_id IN %s) OR (l.partner_id IS NULL))
+ AND ''' + dates_query + '''
+ AND (l.date <= %s)
+ AND l.company_id IN %s'''
+ cr.execute(query, args_list)
+ partners_amount = {}
+ aml_ids = cr.fetchall()
+ aml_ids = aml_ids and [x[0] for x in aml_ids] or []
+ for line in self.env['account.move.line'].browse(aml_ids).with_context(prefetch_fields=False):
+ partner_id = line.partner_id.id or False
+ if partner_id not in partners_amount:
+ partners_amount[partner_id] = 0.0
+ line_amount = ResCurrency._compute(line.company_id.currency_id, user_currency, line.balance)
+ if user_currency.is_zero(line_amount):
+ continue
+ for partial_line in line.matched_debit_ids:
+ if str(partial_line.max_date) <= date_from:
+ line_amount += ResCurrency._compute(partial_line.company_id.currency_id, user_currency, partial_line.amount)
+ for partial_line in line.matched_credit_ids:
+ if str(partial_line.max_date) <= date_from:
+ line_amount -= ResCurrency._compute(partial_line.company_id.currency_id, user_currency, partial_line.amount)
+
+ if not self.env.user.company_id.currency_id.is_zero(line_amount):
+ partners_amount[partner_id] += line_amount
+ lines[partner_id].append({
+ 'line': line,
+ 'amount': line_amount,
+ 'period': i + 1,
+ })
+ history.append(partners_amount)
+
+ for partner in partners:
+ if partner['partner_id'] is None:
+ partner['partner_id'] = False
+ at_least_one_amount = False
+ values = {}
+ undue_amt = 0.0
+ if partner['partner_id'] in undue_amounts: # Making sure this partner actually was found by the query
+ undue_amt = undue_amounts[partner['partner_id']]
+
+ total[6] = total[6] + undue_amt
+ values['direction'] = undue_amt
+ if not float_is_zero(values['direction'], precision_rounding=self.env.user.company_id.currency_id.rounding):
+ at_least_one_amount = True
+
+ for i in range(5):
+ during = False
+ if partner['partner_id'] in history[i]:
+ during = [history[i][partner['partner_id']]]
+ # Adding counter
+ total[(i)] = total[(i)] + (during and during[0] or 0)
+ values[str(i)] = during and during[0] or 0.0
+ if not float_is_zero(values[str(i)], precision_rounding=self.env.user.company_id.currency_id.rounding):
+ at_least_one_amount = True
+ values['total'] = sum([values['direction']] + [values[str(i)] for i in range(5)])
+ ## Add for total
+ total[(i + 1)] += values['total']
+ values['partner_id'] = partner['partner_id']
+ if partner['partner_id']:
+ browsed_partner = self.env['res.partner'].browse(partner['partner_id'])
+ values['name'] = browsed_partner.name and len(browsed_partner.name) >= 45 and browsed_partner.name[0:40] + '...' or browsed_partner.name
+ values['trust'] = browsed_partner.trust
+ else:
+ values['name'] = _('Unknown Partner')
+ values['trust'] = False
+
+ if at_least_one_amount or (self._context.get('include_nullified_amount') and lines[partner['partner_id']]):
+ res.append(values)
+
+ return res, total, lines
+
+ @api.model
+ def get_report_values(self, docids, data=None):
+ if not data.get('form') or not self.env.context.get('active_model') or not self.env.context.get('active_id'):
+ raise UserError(_("Form content is missing, this report cannot be printed."))
+
+ total = []
+ model = self.env.context.get('active_model')
+ docs = self.env[model].browse(self.env.context.get('active_id'))
+
+ target_move = data['form'].get('target_move', 'all')
+ date_from = data['form'].get('date_from', time.strftime('%Y-%m-%d'))
+
+ if data['form']['result_selection'] == 'customer':
+ account_type = ['receivable']
+ elif data['form']['result_selection'] == 'supplier':
+ account_type = ['payable']
+ else:
+ account_type = ['payable', 'receivable']
+
+ movelines, total, dummy = self._get_partner_move_lines(account_type, date_from, target_move, data['form']['period_length'])
+ return {
+ 'doc_ids': self.ids,
+ 'doc_model': model,
+ 'data': data['form'],
+ 'docs': docs,
+ 'time': time,
+ 'get_partner_lines': movelines,
+ 'get_direction': total,
+ }
diff --git a/account_reports_xlsx/models/account_financial_report.py b/account_reports_xlsx/models/account_financial_report.py
new file mode 100644
index 0000000..fad2359
--- /dev/null
+++ b/account_reports_xlsx/models/account_financial_report.py
@@ -0,0 +1,91 @@
+# -*- coding: utf-8 -*-
+######################################################################################
+#
+# Cybrosys Technologies Pvt. Ltd.
+#
+# Copyright (C) 2020-TODAY Cybrosys Technologies(<https://www.cybrosys.com>).
+# Author: Cybrosys Technologies (odoo@cybrosys.com)
+#
+# This program is under the terms of the Odoo Proprietary License v1.0 (OPL-1)
+# It is forbidden to publish, distribute, sublicense, or sell copies of the Software
+# or modified copies of the Software.
+#
+# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
+# IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
+# DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
+# ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
+# DEALINGS IN THE SOFTWARE.
+#
+########################################################################################
+
+from odoo import api, models, fields, _
+
+
+# ---------------------------------------------------------
+# Account Financial Report
+# ---------------------------------------------------------
+
+
+class AccountFinancialReport(models.Model):
+ _name = "account.financial.report"
+ _description = "Account Report"
+
+ @api.depends('parent_id', 'parent_id.level')
+ def _get_level(self):
+ '''Returns a dictionary with key=the ID of a record and value = the level of this
+ record in the tree structure.'''
+ for report in self:
+ level = 0
+ if report.parent_id:
+ level = report.parent_id.level + 1
+ report.level = level
+
+ def _get_children_by_order(self):
+ '''returns a recordset of all the children computed recursively, and sorted by sequence. Ready for the printing'''
+ res = self
+ children = self.search([('parent_id', 'in', self.ids)], order='sequence ASC')
+ if children:
+ for child in children:
+ res += child._get_children_by_order()
+ return res
+
+ name = fields.Char('Report Name', required=True, translate=True)
+ parent_id = fields.Many2one('account.financial.report', 'Parent')
+ children_ids = fields.One2many('account.financial.report', 'parent_id', 'Account Report')
+ sequence = fields.Integer('Sequence')
+ level = fields.Integer(compute='_get_level', string='Level', store=True)
+ type = fields.Selection([
+ ('sum', 'View'),
+ ('accounts', 'Accounts'),
+ ('account_type', 'Account Type'),
+ ('account_report', 'Report Value'),
+ ], 'Type', default='sum')
+ account_ids = fields.Many2many('account.account', 'account_account_financial_report', 'report_line_id',
+ 'account_id', 'Accounts')
+ account_report_id = fields.Many2one('account.financial.report', 'Report Value')
+ account_type_ids = fields.Many2many('account.account.type', 'account_account_financial_report_type', 'report_id',
+ 'account_type_id', 'Account Types')
+ sign = fields.Selection([
+ ('-1', 'Reverse balance sign'),
+ ('1', 'Preserve balance sign')],
+ 'Sign on Reports',
+ required=True,
+ default='1',
+ help='For accounts that are typically more debited than credited and that you would like to print as negative amounts in your reports, you should reverse the sign of the balance; e.g.: Expense account. The same applies for accounts that are typically more credited than debited and that you would like to print as positive amounts in your reports; e.g.: Income account.')
+ display_detail = fields.Selection([
+ ('no_detail', 'No detail'),
+ ('detail_flat', 'Display children flat'),
+ ('detail_with_hierarchy', 'Display children with hierarchy')
+ ], 'Display details', default='detail_flat')
+ style_overwrite = fields.Selection([
+ ('0', 'Automatic formatting'),
+ ('1', 'Main Title 1 (bold, underlined)'),
+ ('2', 'Title 2 (bold)'),
+ ('3', 'Title 3 (bold, smaller)'),
+ ('4', 'Normal Text'),
+ ('5', 'Italic Text (smaller)'),
+ ('6', 'Smallest Text'),
+ ], 'Financial Report Style', default='0',
+ help="You can set up here the format you want this record to be displayed. If you leave the automatic formatting, it will be computed based on the financial reports hierarchy (auto-computed field 'level').")
diff --git a/account_reports_xlsx/models/account_general_ledger.py b/account_reports_xlsx/models/account_general_ledger.py
new file mode 100644
index 0000000..ded77f4
--- /dev/null
+++ b/account_reports_xlsx/models/account_general_ledger.py
@@ -0,0 +1,156 @@
+# -*- coding: utf-8 -*-
+######################################################################################
+#
+# Cybrosys Technologies Pvt. Ltd.
+#
+# Copyright (C) 2020-TODAY Cybrosys Technologies(<https://www.cybrosys.com>).
+# Author: Cybrosys Technologies (odoo@cybrosys.com)
+#
+# This program is under the terms of the Odoo Proprietary License v1.0 (OPL-1)
+# It is forbidden to publish, distribute, sublicense, or sell copies of the Software
+# or modified copies of the Software.
+#
+# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
+# IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
+# DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
+# ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
+# DEALINGS IN THE SOFTWARE.
+#
+########################################################################################
+
+import time
+from odoo import api, models, _
+from odoo.exceptions import UserError
+
+
+class ReportGeneralLedger(models.AbstractModel):
+ _name = 'report.account.report_generalledger'
+
+ def _get_account_move_entry(self, accounts, init_balance, sortby, display_account):
+ """
+ :param:
+ accounts: the recordset of accounts
+ init_balance: boolean value of initial_balance
+ sortby: sorting by date or partner and journal
+ display_account: type of account(receivable, payable and both)
+
+ Returns a dictionary of accounts with following key and value {
+ 'code': account code,
+ 'name': account name,
+ 'debit': sum of total debit amount,
+ 'credit': sum of total credit amount,
+ 'balance': total balance,
+ 'amount_currency': sum of amount_currency,
+ 'move_lines': list of move line
+ }
+ """
+ cr = self.env.cr
+ MoveLine = self.env['account.move.line']
+ move_lines = {x: [] for x in accounts.ids}
+
+ # Prepare initial sql query and Get the initial move lines
+ if init_balance:
+ init_tables, init_where_clause, init_where_params = MoveLine.with_context(date_from=self.env.context.get('date_from'), date_to=False, initial_bal=True)._query_get()
+ init_wheres = [""]
+ if init_where_clause.strip():
+ init_wheres.append(init_where_clause.strip())
+ init_filters = " AND ".join(init_wheres)
+ filters = init_filters.replace('account_move_line__move_id', 'm').replace('account_move_line', 'l')
+ sql = ("""SELECT 0 AS lid, l.account_id AS account_id, '' AS ldate, '' AS lcode, 0.0 AS amount_currency, '' AS lref, 'Initial Balance' AS lname, COALESCE(SUM(l.debit),0.0) AS debit, COALESCE(SUM(l.credit),0.0) AS credit, COALESCE(SUM(l.debit),0) - COALESCE(SUM(l.credit), 0) as balance, '' AS lpartner_id,\
+ '' AS move_name, '' AS mmove_id, '' AS currency_code,\
+ NULL AS currency_id,\
+ '' AS invoice_id, '' AS invoice_type, '' AS invoice_number,\
+ '' AS partner_name\
+ FROM account_move_line l\
+ LEFT JOIN account_move m ON (l.move_id=m.id)\
+ LEFT JOIN res_currency c ON (l.currency_id=c.id)\
+ LEFT JOIN res_partner p ON (l.partner_id=p.id)\
+ LEFT JOIN account_move i ON (m.id =i.id)\
+ JOIN account_journal j ON (l.journal_id=j.id)\
+ WHERE l.account_id IN %s""" + filters + ' GROUP BY l.account_id')
+ params = (tuple(accounts.ids),) + tuple(init_where_params)
+ cr.execute(sql, params)
+ for row in cr.dictfetchall():
+ move_lines[row.pop('account_id')].append(row)
+
+ sql_sort = 'l.date, l.move_id'
+ if sortby == 'sort_journal_partner':
+ sql_sort = 'j.code, p.name, l.move_id'
+
+ # Prepare sql query base on selected parameters from wizard
+ tables, where_clause, where_params = MoveLine._query_get()
+ wheres = [""]
+ if where_clause.strip():
+ wheres.append(where_clause.strip())
+ filters = " AND ".join(wheres)
+ filters = filters.replace('account_move_line__move_id', 'm').replace('account_move_line', 'l')
+
+ # Get move lines base on sql query and Calculate the total balance of move lines
+ sql = ('''SELECT l.id AS lid, l.account_id AS account_id, l.date AS ldate, j.code AS lcode, l.currency_id, l.amount_currency, l.ref AS lref, l.name AS lname, COALESCE(l.debit,0) AS debit, COALESCE(l.credit,0) AS credit, COALESCE(SUM(l.debit),0) - COALESCE(SUM(l.credit), 0) AS balance,\
+ m.name AS move_name, c.symbol AS currency_code, p.name AS partner_name\
+ FROM account_move_line l\
+ JOIN account_move m ON (l.move_id=m.id)\
+ LEFT JOIN res_currency c ON (l.currency_id=c.id)\
+ LEFT JOIN res_partner p ON (l.partner_id=p.id)\
+ JOIN account_journal j ON (l.journal_id=j.id)\
+ JOIN account_account acc ON (l.account_id = acc.id) \
+ WHERE l.account_id IN %s ''' + filters + ''' GROUP BY l.id, l.account_id, l.date, j.code, l.currency_id, l.amount_currency, l.ref, l.name, m.name, c.symbol, p.name ORDER BY ''' + sql_sort)
+ params = (tuple(accounts.ids),) + tuple(where_params)
+ cr.execute(sql, params)
+
+ for row in cr.dictfetchall():
+ balance = 0
+ for line in move_lines.get(row['account_id']):
+ balance += line['debit'] - line['credit']
+ row['balance'] += balance
+ move_lines[row.pop('account_id')].append(row)
+
+ # Calculate the debit, credit and balance for Accounts
+ account_res = []
+ for account in accounts:
+ currency = account.currency_id and account.currency_id or account.company_id.currency_id
+ res = dict((fn, 0.0) for fn in ['credit', 'debit', 'balance'])
+ res['code'] = account.code
+ res['name'] = account.name
+ res['company_id'] = account.company_id.id
+ res['move_lines'] = move_lines[account.id]
+ for line in res.get('move_lines'):
+ res['debit'] += line['debit']
+ res['credit'] += line['credit']
+ res['balance'] = line['balance']
+ if display_account == 'all':
+ account_res.append(res)
+ if display_account == 'movement' and res.get('move_lines'):
+ account_res.append(res)
+ if display_account == 'not_zero' and not currency.is_zero(res['balance']):
+ account_res.append(res)
+ return account_res
+
+ @api.model
+ def _get_report_values(self, docids, data=None):
+ if not data.get('form') or not self.env.context.get('active_model'):
+ raise UserError(_("Form content is missing, this report cannot be printed."))
+
+ model = self.env.context.get('active_model')
+ docs = self.env[model].browse(self.env.context.get('active_ids', []))
+
+ init_balance = data['form'].get('initial_balance', True)
+ sortby = data['form'].get('sortby', 'sort_date')
+ display_account = data['form']['display_account']
+ codes = []
+ if data['form'].get('journal_ids', False):
+ codes = [journal.code for journal in self.env['account.journal'].search([('id', 'in', data['form']['journal_ids'])])]
+
+ accounts = docs if model == 'account.account' else self.env['account.account'].search([])
+ accounts_res = self.with_context(data['form'].get('used_context',{}))._get_account_move_entry(accounts, init_balance, sortby, display_account)
+ return {
+ 'doc_ids': docids,
+ 'doc_model': model,
+ 'data': data['form'],
+ 'docs': docs,
+ 'time': time,
+ 'Accounts': accounts_res,
+ 'print_journal': codes,
+ }
diff --git a/account_reports_xlsx/models/account_partner_ledger.py b/account_reports_xlsx/models/account_partner_ledger.py
new file mode 100644
index 0000000..51a7df2
--- /dev/null
+++ b/account_reports_xlsx/models/account_partner_ledger.py
@@ -0,0 +1,151 @@
+# -*- coding: utf-8 -*-
+######################################################################################
+#
+# Cybrosys Technologies Pvt. Ltd.
+#
+# Copyright (C) 2020-TODAY Cybrosys Technologies(<https://www.cybrosys.com>).
+# Author: Cybrosys Technologies (odoo@cybrosys.com)
+#
+# This program is under the terms of the Odoo Proprietary License v1.0 (OPL-1)
+# It is forbidden to publish, distribute, sublicense, or sell copies of the Software
+# or modified copies of the Software.
+#
+# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
+# IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
+# DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
+# ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
+# DEALINGS IN THE SOFTWARE.
+#
+########################################################################################
+
+import logging
+from datetime import datetime
+import time
+from odoo import api, models, _
+from odoo.exceptions import UserError
+from odoo.tools import DEFAULT_SERVER_DATE_FORMAT
+
+_logger = logging.getLogger(__name__)
+
+
+class ReportPartnerLedger(models.AbstractModel):
+ _name = 'report.account_reports_xlsx.report_partnerledger'
+
+ def _lines(self, data, partner):
+ full_account = []
+ currency = self.env['res.currency']
+ query_get_data = self.env['account.move.line'].with_context(data['form'].get('used_context', {}))._query_get()
+ reconcile_clause = "" if data['form']['form']['reconciled'] else ' AND "account_move_line".full_reconcile_id IS NULL '
+ params = [partner.id, tuple(data['computed']['move_state']), tuple(data['computed']['account_ids'])] + query_get_data[2]
+ query = """
+ SELECT "account_move_line".id, "account_move_line".date, j.code, acc.code as a_code, acc.name as a_name, "account_move_line".ref, m.name as move_name, "account_move_line".name, "account_move_line".debit, "account_move_line".credit, "account_move_line".amount_currency,"account_move_line".currency_id, c.symbol AS currency_code
+ FROM """ + query_get_data[0] + """
+ LEFT JOIN account_journal j ON ("account_move_line".journal_id = j.id)
+ LEFT JOIN account_account acc ON ("account_move_line".account_id = acc.id)
+ LEFT JOIN res_currency c ON ("account_move_line".currency_id=c.id)
+ LEFT JOIN account_move m ON (m.id="account_move_line".move_id)
+ WHERE "account_move_line".partner_id = %s
+ AND m.state IN %s
+ AND "account_move_line".account_id IN %s AND """ + query_get_data[1] + reconcile_clause + """
+ ORDER BY "account_move_line".date"""
+ self.env.cr.execute(query, tuple(params))
+ res = self.env.cr.dictfetchall()
+ sum = 0.0
+ lang_code = self.env.context.get('lang') or 'en_US'
+ lang = self.env['res.lang']
+ lang_id = lang._lang_get(lang_code)
+ date_format = lang_id.date_format
+ for r in res:
+ r['date'] = datetime.strptime(str(r['date']), DEFAULT_SERVER_DATE_FORMAT).strftime(date_format)
+ r['displayed_name'] = '-'.join(
+ r[field_name] for field_name in ('move_name', 'ref', 'name')
+ if r[field_name] not in (None, '', '/')
+ )
+ sum += r['debit'] - r['credit']
+ r['progress'] = sum
+ r['currency_id'] = currency.browse(r.get('currency_id'))
+ full_account.append(r)
+ return full_account
+
+ def _sum_partner(self, data, partner, field):
+ if field not in ['debit', 'credit', 'debit - credit', 'amount_currency']:
+ return
+ result = 0.0
+ query_get_data = self.env['account.move.line'].with_context(data['form'].get('used_context', {}))._query_get()
+ reconcile_clause = "" if data['form']['form']['reconciled'] else ' AND "account_move_line".full_reconcile_id IS NULL '
+
+ params = [partner.id, tuple(data['computed']['move_state']), tuple(data['computed']['account_ids'])] + query_get_data[2]
+ query = """SELECT sum(""" + field + """)
+ FROM """ + query_get_data[0] + """, account_move AS m
+ WHERE "account_move_line".partner_id = %s
+ AND m.id = "account_move_line".move_id
+ AND m.state IN %s
+ AND account_id IN %s
+ AND """ + query_get_data[1] + reconcile_clause
+ self.env.cr.execute(query, tuple(params))
+
+ contemp = self.env.cr.fetchone()
+ if contemp is not None:
+ result = contemp[0] or 0.0
+ return result
+
+ @api.model
+ def get_report_values(self, docids, data=None):
+ if not data.get('form'):
+ raise UserError(_("Form content is missing, this report cannot be printed."))
+
+ data['computed'] = {}
+
+ obj_partner = self.env['res.partner']
+ query_get_data = self.env['account.move.line'].with_context(data['form'].get('used_context', {}))._query_get()
+ data['computed']['move_state'] = ['draft', 'posted']
+ if data['form'].get('target_move', 'all') == 'posted':
+ data['computed']['move_state'] = ['posted']
+ result_selection = data['form'].get('result_selection', 'customer')
+ if result_selection == 'supplier':
+ data['computed']['ACCOUNT_TYPE'] = ['payable']
+ elif result_selection == 'customer':
+ data['computed']['ACCOUNT_TYPE'] = ['receivable']
+ else:
+ data['computed']['ACCOUNT_TYPE'] = ['payable', 'receivable']
+
+ self.env.cr.execute("""
+ SELECT a.id
+ FROM account_account a
+ WHERE a.internal_type IN %s
+ AND NOT a.deprecated""", (tuple(data['computed']['ACCOUNT_TYPE']),))
+ data['computed']['account_ids'] = [a for (a,) in self.env.cr.fetchall()]
+ params = [tuple(data['computed']['move_state']), tuple(data['computed']['account_ids'])] + query_get_data[2]
+ reconcile_clause = "" if data['form']['reconciled'] else ' AND "account_move_line".full_reconcile_id IS NULL '
+ query = """
+ SELECT DISTINCT "account_move_line".partner_id
+ FROM """ + query_get_data[0] + """, account_account AS account, account_move AS am
+ WHERE "account_move_line".partner_id IS NOT NULL
+ AND "account_move_line".account_id = account.id
+ AND am.id = "account_move_line".move_id
+ AND am.state IN %s
+ AND "account_move_line".account_id IN %s
+ AND NOT account.deprecated
+ AND """ + query_get_data[1] + reconcile_clause
+ self.env.cr.execute(query, tuple(params))
+ # ---------------------Taking only selected partners---------------------------
+ if data['form']['partner_ids']:
+ partner_ids = data['form']['partner_ids']
+ else:
+ partner_ids = [res['partner_id'] for res in self.env.cr.dictfetchall()]
+ # -----------------------------------------------------------------------------
+ # partner_ids = [res['partner_id'] for res in self.env.cr.dictfetchall()]
+ partners = obj_partner.browse(partner_ids)
+ partners = sorted(partners, key=lambda x: (x.ref or '', x.name or ''))
+
+ return {
+ 'doc_ids': partner_ids,
+ 'doc_model': self.env['res.partner'],
+ 'data': data,
+ 'docs': partners,
+ 'time': time,
+ 'lines': self._lines,
+ 'sum_partner': self._sum_partner,
+ }
generated by cgit v1.2.3 (git 2.25.1) at 2026-05-09 07:43:03 +0000