path: root/addons/sale_timesheet/report/project_profitability_report_analysis.py

# -*- coding: utf-8 -*-
# Part of Odoo. See LICENSE file for full copyright and licensing details.

from odoo import fields, models, tools


class ProfitabilityAnalysis(models.Model):

    _name = "project.profitability.report"
    _description = "Project Profitability Report"
    _order = 'project_id, sale_line_id'
    _auto = False

    analytic_account_id = fields.Many2one('account.analytic.account', string='Analytic Account', readonly=True)
    project_id = fields.Many2one('project.project', string='Project', readonly=True)
    currency_id = fields.Many2one('res.currency', string='Project Currency', readonly=True)
    company_id = fields.Many2one('res.company', string='Project Company', readonly=True)
    user_id = fields.Many2one('res.users', string='Project Manager', readonly=True)
    partner_id = fields.Many2one('res.partner', string='Customer', readonly=True)
    line_date = fields.Date("Date", readonly=True)
    # cost
    timesheet_unit_amount = fields.Float("Timesheet Duration", digits=(16, 2), readonly=True, group_operator="sum")
    timesheet_cost = fields.Float("Timesheet Cost", digits=(16, 2), readonly=True, group_operator="sum")
    expense_cost = fields.Float("Other Costs", digits=(16, 2), readonly=True, group_operator="sum")
    # sale revenue
    order_confirmation_date = fields.Datetime('Sales Order Confirmation Date', readonly=True)
    sale_line_id = fields.Many2one('sale.order.line', string='Sale Order Line', readonly=True)
    sale_order_id = fields.Many2one('sale.order', string='Sale Order', readonly=True)
    product_id = fields.Many2one('product.product', string='Product', readonly=True)

    amount_untaxed_to_invoice = fields.Float("Untaxed Amount to Invoice", digits=(16, 2), readonly=True, group_operator="sum")
    amount_untaxed_invoiced = fields.Float("Untaxed Amount Invoiced", digits=(16, 2), readonly=True, group_operator="sum")
    expense_amount_untaxed_to_invoice = fields.Float("Untaxed Amount to Re-invoice", digits=(16, 2), readonly=True, group_operator="sum")
    expense_amount_untaxed_invoiced = fields.Float("Untaxed Amount Re-invoiced", digits=(16, 2), readonly=True, group_operator="sum")
    other_revenues = fields.Float("Other Revenues", digits=(16, 2), readonly=True, group_operator="sum",
                                  help="All revenues that are not from timesheets and that are linked to the analytic account of the project.")
    margin = fields.Float("Margin", digits=(16, 2), readonly=True, group_operator="sum")

    _depends = {
        'sale.order.line': [
            'order_id',
            'invoice_status',
            'price_reduce',
            'product_id',
            'qty_invoiced',
            'untaxed_amount_invoiced',
            'untaxed_amount_to_invoice',
            'currency_id',
            'company_id',
            'is_downpayment',
            'project_id',
            'task_id',
            'qty_delivered_method',
        ],
        'sale.order': [
            'date_order',
            'user_id',
            'partner_id',
            'currency_id',
            'analytic_account_id',
            'order_line',
            'invoice_status',
            'amount_untaxed',
            'currency_rate',
            'company_id',
            'project_id',
        ],
    }

    def init(self):
        tools.drop_view_if_exists(self._cr, self._table)
        query = """
            CREATE VIEW %s AS (
                SELECT
                    sub.id as id,
                    sub.project_id as project_id,
                    sub.user_id as user_id,
                    sub.sale_line_id as sale_line_id,
                    sub.analytic_account_id as analytic_account_id,
                    sub.partner_id as partner_id,
                    sub.company_id as company_id,
                    sub.currency_id as currency_id,
                    sub.sale_order_id as sale_order_id,
                    sub.order_confirmation_date as order_confirmation_date,
                    sub.product_id as product_id,
                    sub.sale_qty_delivered_method as sale_qty_delivered_method,
                    sub.expense_amount_untaxed_to_invoice as expense_amount_untaxed_to_invoice,
                    sub.expense_amount_untaxed_invoiced as expense_amount_untaxed_invoiced,
                    sub.amount_untaxed_to_invoice as amount_untaxed_to_invoice,
                    sub.amount_untaxed_invoiced as amount_untaxed_invoiced,
                    sub.timesheet_unit_amount as timesheet_unit_amount,
                    sub.timesheet_cost as timesheet_cost,
                    sub.expense_cost as expense_cost,
                    sub.other_revenues as other_revenues,
                    sub.line_date as line_date,
                    (sub.expense_amount_untaxed_to_invoice + sub.expense_amount_untaxed_invoiced + sub.amount_untaxed_to_invoice +
                        sub.amount_untaxed_invoiced + sub.other_revenues + sub.timesheet_cost + sub.expense_cost)
                        as margin
                FROM (
                    SELECT
                        ROW_NUMBER() OVER (ORDER BY P.id, SOL.id) AS id,
                        P.id AS project_id,
                        P.user_id AS user_id,
                        SOL.id AS sale_line_id,
                        P.analytic_account_id AS analytic_account_id,
                        P.partner_id AS partner_id,
                        C.id AS company_id,
                        C.currency_id AS currency_id,
                        S.id AS sale_order_id,
                        S.date_order AS order_confirmation_date,
                        SOL.product_id AS product_id,
                        SOL.qty_delivered_method AS sale_qty_delivered_method,
                        COST_SUMMARY.expense_amount_untaxed_to_invoice AS expense_amount_untaxed_to_invoice,
                        COST_SUMMARY.expense_amount_untaxed_invoiced AS expense_amount_untaxed_invoiced,
                        COST_SUMMARY.amount_untaxed_to_invoice AS amount_untaxed_to_invoice,
                        COST_SUMMARY.amount_untaxed_invoiced AS amount_untaxed_invoiced,
                        COST_SUMMARY.timesheet_unit_amount AS timesheet_unit_amount,
                        COST_SUMMARY.timesheet_cost AS timesheet_cost,
                        COST_SUMMARY.expense_cost AS expense_cost,
                        COST_SUMMARY.other_revenues AS other_revenues,
                        COST_SUMMARY.line_date::date AS line_date
                    FROM project_project P
                        JOIN res_company C ON C.id = P.company_id
                        LEFT JOIN (
                            -- Each costs and revenues will be retrieved individually by sub-requests
                            -- This is required to able to get the date
                            SELECT
                                project_id,
                                analytic_account_id,
                                sale_line_id,
                                SUM(timesheet_unit_amount) AS timesheet_unit_amount,
                                SUM(timesheet_cost) AS timesheet_cost,
                                SUM(expense_cost) AS expense_cost,
                                SUM(other_revenues) AS other_revenues,
                                SUM(downpayment_invoiced) AS downpayment_invoiced,
                                SUM(expense_amount_untaxed_to_invoice) AS expense_amount_untaxed_to_invoice,
                                SUM(expense_amount_untaxed_invoiced) AS expense_amount_untaxed_invoiced,
                                SUM(amount_untaxed_to_invoice) AS amount_untaxed_to_invoice,
                                SUM(amount_untaxed_invoiced) AS amount_untaxed_invoiced,
                                line_date AS line_date
                            FROM (
                                -- Get the timesheet costs
                                SELECT
                                    P.id AS project_id,
                                    P.analytic_account_id AS analytic_account_id,
                                    TS.so_line AS sale_line_id,
                                    TS.unit_amount AS timesheet_unit_amount,
                                    TS.amount AS timesheet_cost,
                                    0.0 AS other_revenues,
                                    0.0 AS expense_cost,
                                    0.0 AS downpayment_invoiced,
                                    0.0 AS expense_amount_untaxed_to_invoice,
                                    0.0 AS expense_amount_untaxed_invoiced,
                                    0.0 AS amount_untaxed_to_invoice,
                                    0.0 AS amount_untaxed_invoiced,
                                    TS.date AS line_date
                                FROM account_analytic_line TS, project_project P
                                WHERE TS.project_id IS NOT NULL AND P.id = TS.project_id AND P.active = 't' AND P.allow_timesheets = 't'

                                UNION ALL

                                -- Get the other revenues
                                SELECT
                                    P.id AS project_id,
                                    P.analytic_account_id AS analytic_account_id,
                                    AAL.so_line AS sale_line_id,
                                    0.0 AS timesheet_unit_amount,
                                    0.0 AS timesheet_cost,
                                    AAL.amount AS other_revenues,
                                    0.0 AS expense_cost,
                                    0.0 AS downpayment_invoiced,
                                    0.0 AS expense_amount_untaxed_to_invoice,
                                    0.0 AS expense_amount_untaxed_invoiced,
                                    0.0 AS amount_untaxed_to_invoice,
                                    0.0 AS amount_untaxed_invoiced,
                                    AAL.date AS line_date
                                FROM project_project P
                                    JOIN account_analytic_account AA ON P.analytic_account_id = AA.id
                                    JOIN account_analytic_line AAL ON AAL.account_id = AA.id
                                    JOIN product_product PP ON PP.id = AAL.product_id
                                    JOIN product_template PT ON PT.id = PP.product_tmpl_id
                                    LEFT JOIN sale_order_line_invoice_rel SOINV ON SOINV.invoice_line_id = AAL.move_id
                                    LEFT JOIN sale_order_line SOL ON SOINV.order_line_id = SOL.id
                                WHERE AAL.amount > 0.0 AND AAL.project_id IS NULL AND P.active = 't'
                                    AND P.allow_timesheets = 't'
                                    AND PT.service_type = 'manual' -- default value or Milestone service for services products
                                    AND PT.service_tracking = 'no' -- default value or not a tracking service for services products
                                    AND (SOL.id IS NULL
                                        OR (SOL.is_expense IS NOT TRUE AND SOL.is_downpayment IS NOT TRUE))

                                UNION ALL

                                -- Get the expense costs from account analytic line
                                SELECT
                                    P.id AS project_id,
                                    P.analytic_account_id AS analytic_account_id,
                                    AAL.so_line AS sale_line_id,
                                    0.0 AS timesheet_unit_amount,
                                    0.0 AS timesheet_cost,
                                    0.0 AS other_revenues,
                                    AAL.amount AS expense_cost,
                                    0.0 AS downpayment_invoiced,
                                    0.0 AS expense_amount_untaxed_to_invoice,
                                    0.0 AS expense_amount_untaxed_invoiced,
                                    0.0 AS amount_untaxed_to_invoice,
                                    0.0 AS amount_untaxed_invoiced,
                                    AAL.date AS line_date
                                FROM project_project P
                                    LEFT JOIN account_analytic_account AA ON P.analytic_account_id = AA.id
                                    LEFT JOIN account_analytic_line AAL ON AAL.account_id = AA.id
                                WHERE AAL.amount < 0.0 AND AAL.project_id IS NULL AND P.active = 't' AND P.allow_timesheets = 't'

                                UNION ALL

                                -- Get the invoiced downpayments
                                SELECT
                                    P.id AS project_id,
                                    P.analytic_account_id AS analytic_account_id,
                                    MY_SOLS.id AS sale_line_id,
                                    0.0 AS timesheet_unit_amount,
                                    0.0 AS timesheet_cost,
                                    0.0 AS other_revenues,
                                    0.0 AS expense_cost,
                                    CASE WHEN MY_SOLS.invoice_status = 'invoiced' THEN MY_SOLS.price_reduce ELSE 0.0 END AS downpayment_invoiced,
                                    0.0 AS expense_amount_untaxed_to_invoice,
                                    0.0 AS expense_amount_untaxed_invoiced,
                                    0.0 AS amount_untaxed_to_invoice,
                                    0.0 AS amount_untaxed_invoiced,
                                    MY_S.date_order AS line_date
                                FROM project_project P
                                    LEFT JOIN sale_order_line MY_SOL ON P.sale_line_id = MY_SOL.id
                                    LEFT JOIN sale_order MY_S ON MY_SOL.order_id = MY_S.id
                                    LEFT JOIN sale_order_line MY_SOLS ON MY_SOLS.order_id = MY_S.id
                                WHERE MY_SOLS.is_downpayment = 't'

                                UNION ALL

                                -- Get the expense costs from sale order line
                                SELECT
                                    P.id AS project_id,
                                    P.analytic_account_id AS analytic_account_id,
                                    OLIS.id AS sale_line_id,
                                    0.0 AS timesheet_unit_amount,
                                    0.0 AS timesheet_cost,
                                    0.0 AS other_revenues,
                                    OLIS.price_reduce AS expense_cost,
                                    0.0 AS downpayment_invoiced,
                                    0.0 AS expense_amount_untaxed_to_invoice,
                                    0.0 AS expense_amount_untaxed_invoiced,
                                    0.0 AS amount_untaxed_to_invoice,
                                    0.0 AS amount_untaxed_invoiced,
                                    ANLI.date AS line_date
                                FROM project_project P
                                    LEFT JOIN account_analytic_account ANAC ON P.analytic_account_id = ANAC.id
                                    LEFT JOIN account_analytic_line ANLI ON ANAC.id = ANLI.account_id
                                    LEFT JOIN sale_order_line OLI ON P.sale_line_id = OLI.id
                                    LEFT JOIN sale_order ORD ON OLI.order_id = ORD.id
                                    LEFT JOIN sale_order_line OLIS ON ORD.id = OLIS.order_id
                                WHERE OLIS.product_id = ANLI.product_id AND OLIS.is_downpayment = 't' AND ANLI.amount < 0.0 AND ANLI.project_id IS NULL AND P.active = 't' AND P.allow_timesheets = 't'

                                UNION ALL

                                -- Get the following values: expense amount untaxed to invoice/invoiced, amount untaxed to invoice/invoiced
                                -- These values have to be computed from all the records retrieved just above but grouped by project and sale order line
                                SELECT
                                    AMOUNT_UNTAXED.project_id AS project_id,
                                    AMOUNT_UNTAXED.analytic_account_id AS analytic_account_id,
                                    AMOUNT_UNTAXED.sale_line_id AS sale_line_id,
                                    0.0 AS timesheet_unit_amount,
                                    0.0 AS timesheet_cost,
                                    0.0 AS other_revenues,
                                    0.0 AS expense_cost,
                                    0.0 AS downpayment_invoiced,
                                    CASE
                                        WHEN SOL.qty_delivered_method = 'analytic' THEN (SOL.untaxed_amount_to_invoice / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END)
                                        ELSE 0.0
                                    END AS expense_amount_untaxed_to_invoice,
                                    CASE
                                        WHEN SOL.qty_delivered_method = 'analytic' AND SOL.invoice_status != 'no'
                                        THEN
                                            CASE
                                                WHEN T.expense_policy = 'sales_price'
                                                THEN (SOL.price_reduce / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END) * SOL.qty_invoiced
                                                ELSE -AMOUNT_UNTAXED.expense_cost
                                            END
                                        ELSE 0.0
                                    END AS expense_amount_untaxed_invoiced,
                                    CASE
                                        WHEN SOL.qty_delivered_method IN ('timesheet', 'manual') THEN (SOL.untaxed_amount_to_invoice / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END)
                                        ELSE 0.0
                                    END AS amount_untaxed_to_invoice,
                                    CASE
                                        WHEN SOL.qty_delivered_method IN ('timesheet', 'manual') THEN (COALESCE(SOL.untaxed_amount_invoiced, AMOUNT_UNTAXED.downpayment_invoiced) / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END)
                                        ELSE 0.0
                                    END AS amount_untaxed_invoiced,
                                    S.date_order AS line_date
                                FROM project_project P
                                    JOIN res_company C ON C.id = P.company_id
                                    LEFT JOIN (
                                        SELECT
                                            P.id AS project_id,
                                            P.analytic_account_id AS analytic_account_id,
                                            AAL.so_line AS sale_line_id,
                                            0.0 AS expense_cost,
                                            0.0 AS downpayment_invoiced
                                        FROM account_analytic_line AAL, project_project P
                                        WHERE AAL.project_id IS NOT NULL AND P.id = AAL.project_id AND P.active = 't'
                                        GROUP BY P.id, AAL.so_line

                                        UNION

                                        SELECT
                                            P.id AS project_id,
                                            P.analytic_account_id AS analytic_account_id,
                                            AAL.so_line AS sale_line_id,
                                            0.0 AS expense_cost,
                                            0.0 AS downpayment_invoiced
                                        FROM project_project P
                                            LEFT JOIN account_analytic_account AA ON P.analytic_account_id = AA.id
                                            LEFT JOIN account_analytic_line AAL ON AAL.account_id = AA.id
                                        WHERE AAL.amount > 0.0 AND AAL.project_id IS NULL AND P.active = 't' AND P.allow_timesheets = 't'
                                        GROUP BY P.id, AA.id, AAL.so_line
                                        UNION
                                        SELECT
                                            P.id AS project_id,
                                            P.analytic_account_id AS analytic_account_id,
                                            AAL.so_line AS sale_line_id,
                                            SUM(AAL.amount) AS expense_cost,
                                            0.0 AS downpayment_invoiced
                                        FROM project_project P
                                            LEFT JOIN account_analytic_account AA ON P.analytic_account_id = AA.id
                                            LEFT JOIN account_analytic_line AAL ON AAL.account_id = AA.id
                                        WHERE AAL.amount < 0.0 AND AAL.project_id IS NULL AND P.active = 't' AND P.allow_timesheets = 't'
                                        GROUP BY P.id, AA.id, AAL.so_line
                                        UNION
                                        SELECT
                                            P.id AS project_id,
                                            P.analytic_account_id AS analytic_account_id,
                                            MY_SOLS.id AS sale_line_id,
                                            0.0 AS expense_cost,
                                            CASE WHEN MY_SOLS.invoice_status = 'invoiced' THEN MY_SOLS.price_reduce ELSE 0.0 END AS downpayment_invoiced
                                        FROM project_project P
                                            LEFT JOIN sale_order_line MY_SOL ON P.sale_line_id = MY_SOL.id
                                            LEFT JOIN sale_order MY_S ON MY_SOL.order_id = MY_S.id
                                            LEFT JOIN sale_order_line MY_SOLS ON MY_SOLS.order_id = MY_S.id
                                        WHERE MY_SOLS.is_downpayment = 't'
                                        GROUP BY P.id, MY_SOLS.id
                                        UNION
                                        SELECT
                                            P.id AS project_id,
                                            P.analytic_account_id AS analytic_account_id,
                                            OLIS.id AS sale_line_id,
                                            OLIS.price_reduce AS expense_cost,
                                            0.0 AS downpayment_invoiced
                                        FROM project_project P
                                            LEFT JOIN account_analytic_account ANAC ON P.analytic_account_id = ANAC.id
                                            LEFT JOIN account_analytic_line ANLI ON ANAC.id = ANLI.account_id
                                            LEFT JOIN sale_order_line OLI ON P.sale_line_id = OLI.id
                                            LEFT JOIN sale_order ORD ON OLI.order_id = ORD.id
                                            LEFT JOIN sale_order_line OLIS ON ORD.id = OLIS.order_id
                                        WHERE OLIS.product_id = ANLI.product_id AND OLIS.is_downpayment = 't' AND ANLI.amount < 0.0 AND ANLI.project_id IS NULL AND P.active = 't' AND P.allow_timesheets = 't'
                                        GROUP BY P.id, OLIS.id
                                        UNION
                                        SELECT
                                            P.id AS project_id,
                                            P.analytic_account_id AS analytic_account_id,
                                            SOL.id AS sale_line_id,
                                            0.0 AS expense_cost,
                                            0.0 AS downpayment_invoiced
                                        FROM sale_order_line SOL
                                            INNER JOIN project_project P ON SOL.project_id = P.id
                                        WHERE P.active = 't' AND P.allow_timesheets = 't'
                                        UNION
                                        SELECT
                                            P.id AS project_id,
                                            P.analytic_account_id AS analytic_account_id,
                                            SOL.id AS sale_line_id,
                                            0.0 AS expense_cost,
                                            0.0 AS downpayment_invoiced
                                        FROM sale_order_line SOL
                                            INNER JOIN project_task T ON SOL.task_id = T.id
                                            INNER JOIN project_project P ON P.id = T.project_id
                                        WHERE P.active = 't' AND P.allow_timesheets = 't'
                                    ) AMOUNT_UNTAXED ON AMOUNT_UNTAXED.project_id = P.id
                                    LEFT JOIN sale_order_line SOL ON AMOUNT_UNTAXED.sale_line_id = SOL.id
                                    LEFT JOIN sale_order S ON SOL.order_id = S.id
                                    LEFT JOIN product_product PP on (SOL.product_id = PP.id)
                                    LEFT JOIN product_template T on (PP.product_tmpl_id = T.id)
                                    WHERE P.active = 't' AND P.analytic_account_id IS NOT NULL
                            ) SUB_COST_SUMMARY
                            GROUP BY project_id, analytic_account_id, sale_line_id, line_date
                        ) COST_SUMMARY ON COST_SUMMARY.project_id = P.id
                        LEFT JOIN sale_order_line SOL ON COST_SUMMARY.sale_line_id = SOL.id
                        LEFT JOIN sale_order S ON SOL.order_id = S.id
                        WHERE P.active = 't' AND P.analytic_account_id IS NOT NULL
                    ) AS sub
            )
        """ % self._table
        self._cr.execute(query)