From 1ca3b3df3421961caec3b747a364071c80f5c7da Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Tue, 10 May 2022 17:14:58 +0700 Subject: initial commit --- .../models/account_partner_ledger.py | 151 +++++++++++++++++++++ 1 file changed, 151 insertions(+) create mode 100644 account_reports_xlsx/models/account_partner_ledger.py (limited to 'account_reports_xlsx/models/account_partner_ledger.py') 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(). +# 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, + } -- cgit v1.2.3