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

from odoo import api, fields, models, tools

class ProjectRevenueCustomReport2(models.Model):

    _name = "project.revenue.custom.report2"
    _description = "Project Revenue Custom Analysis report2"
    #_order = 'project_id'
    _auto = False

    project_id = fields.Many2one('project.project', string='Project', readonly=True)
    parent_project = fields.Many2one('project.project', string='Parent Project', readonly=True)
    partner_id = fields.Many2one('res.partner', string='Client', readonly=True)
    pricing_type = fields.Selection([
        ('fixed_rate', 'Fixed rate'),
        ('employee_rate', 'Consultant rate')
    ], string="Pricing", readonly=True)
    project_type = fields.Selection([
        ('hours_in_consultant', 'Hours are budgeted according to a consultant'),
        ('hours_no_limit', 'Total hours are budgeted without division to consultant'),
    ], string="Project Type", readonly=True)
    employee_id = fields.Many2one('hr.employee', string='Consultant', readonly=True)
    timesheet_sdatetime = fields.Datetime(string='Timesheet Start Time', readonly=True)
    unit_amount = fields.Float('Timesheet Hours', digits=(16, 2))
    timesheet_cost = fields.Float('Hourly Cost', digits=(16, 2))
    profit_per = fields.Float(string='Profit (%)', digits=(16, 2))
    profit_amt = fields.Float(string='Profit Amount', digits=(16, 2))
    expenses_amt = fields.Float(string='Expenses Amount', digits=(16, 2))
    pro_hourly_rate = fields.Float("Hourly Revenue", digits=(16, 2), group_operator="max")
    budgeted_hours = fields.Float("Budgeted Hours", digits=(16, 2), readonly=True, group_operator="sum")
    budgeted_revenue = fields.Float("Budgeted Revenue", digits=(16, 2), readonly=True, group_operator="sum")
    actual_revenue = fields.Float("Actual Revenue", digits=(16, 2), readonly=True, group_operator="sum")
    actual_cost = fields.Float("Actual Cost", digits=(16, 2), readonly=True, group_operator="sum")
    overall_budgeted_revenue = fields.Float("Overall Budgeted Rev.", digits=(16, 2), readonly=True, group_operator="sum")
    overall_hourly_rate = fields.Float("Overall Hourly Rate", digits=(16, 2), group_operator="sum")
    project_active = fields.Boolean('Active')


    def init(self):
        '''Create the view'''
        tools.drop_view_if_exists(self._cr, self._table)
        self._cr.execute("""
        CREATE OR REPLACE VIEW %s AS (
            SELECT ROW_NUMBER() OVER() as id, 
            project_id,
            project_active, 
            parentproject as parent_project, 
            project_type,
            pricing_type,
            employee_id,
            overall_budgeted_revenue,
            partner_id,
            budgeted_revenue,
            budgeted_hours,
            pro_hourly_rate,
            overall_hourly_rate,
            actual_revenue, 
            actual_cost,
            expenses_amt,
            profit_amt,
            profit_per,
            unit_amount, 
            timesheet_cost, 
            timesheet_sdatetime 
            from (
                select pro.id AS project_id,
                pro.active as project_active,
                (select project_id from project_subproject_rel as par where pro.id=par.id limit 1) as parentproject,
                pro.partner_id AS partner_id,	
                pro.project_type AS project_type,
                pro.pricing_type  as pricing_type,
                AAL.employee_id AS employee_id,
                0.0 AS overall_budgeted_revenue,
                0.0 AS budgeted_revenue,
                0.0 AS budgeted_hours,
                0.0 AS overall_hourly_rate,
                AAL.unit_amount,
                0.0 AS pro_hourly_rate,
                ((AAL.amount * -1)/NULLIF(AAL.unit_amount, 0)) as timesheet_cost,
                0.0 AS actual_revenue,
                (AAL.amount * -1) AS actual_cost,
                0.0 AS expenses_amt,
                0.0 - (AAL.amount * -1)  AS profit_amt,	
                0.0 AS profit_per,
                AAL.start_datetime AS timesheet_sdatetime
                FROM project_project PRO
                RIGHT JOIN account_analytic_account AA ON PRO.analytic_account_id = AA.id
                RIGHT JOIN account_analytic_line AAL ON AAL.account_id = AA.id and AAL.project_id = PRO.id 
                WHERE  PRO.pricing_type = 'fixed_rate'
            UNION    
                select
                pro.id AS project_id,
                pro.active as project_active,
                (select project_id from project_subproject_rel as par where pro.id=par.id limit 1) as parentproject,	
                pro.partner_id AS partner_id,
                pro.project_type AS project_type,
                pro.pricing_type  as pricing_type,
                null::int AS employee_id,
                0.0 AS overall_budgeted_revenue,
                pro.budgeted_revenue AS budgeted_revenue,
                pro.budgeted_hours2 AS budgeted_hours,
                0.0 AS overall_hourly_rate,
                0.0 as unit_amount,
                0.0 AS pro_hourly_rate,
                0.0 as timesheet_cost,
                0.0 AS actual_revenue,
                0.0 AS actual_cost,
                pro.expenses_amt AS expenses_amt, 
                0.0 AS profit_amt,
                0.0 AS profit_per, 
                null::timestamp as timesheet_sdatetime
                FROM project_project PRO
                WHERE PRO.pricing_type = 'employee_rate' and PRO.project_type='hours_no_limit' 
            UNION
                select
                pro.id AS project_id,
                pro.active as project_active,
                (select project_id from project_subproject_rel as par where pro.id=par.id limit 1) as parentproject,	
                pro.partner_id AS partner_id,
                pro.project_type AS project_type,
                pro.pricing_type  as pricing_type,
                AAL.employee_id AS employee_id,
                0.0 AS overall_budgeted_revenue,
                0.0 AS budgeted_revenue,
                0.0 AS budgeted_hours,
                0.0 AS overall_hourly_rate,
                AAL.unit_amount,
                pro.hourly_rate AS pro_hourly_rate,
                ((AAL.amount * -1)/NULLIF(AAL.unit_amount, 0)) as timesheet_cost,
                (AAL.unit_amount * pro.hourly_rate) AS actual_revenue,
                (AAL.amount * -1) AS actual_cost,
                0.0 AS expenses_amt,
                ((AAL.unit_amount * pro.hourly_rate) - (AAL.amount * -1)) AS profit_amt,
                ((AAL.unit_amount * pro.hourly_rate) - (AAL.amount * -1))/NULLIF((AAL.unit_amount * pro.hourly_rate),0) AS profit_per, 		
                AAL.start_datetime AS timesheet_sdatetime
                FROM project_project PRO
                RIGHT JOIN account_analytic_account AA ON PRO.analytic_account_id = AA.id
                RIGHT JOIN account_analytic_line AAL ON AAL.account_id = AA.id and AAL.project_id = PRO.id
                WHERE PRO.pricing_type = 'employee_rate' and PRO.project_type='hours_no_limit'
            UNION    
                select
                pro.id AS project_id,
                pro.active as project_active,
                (select project_id from project_subproject_rel as par where pro.id=par.id limit 1) as parentproject,	
                pro.partner_id AS partner_id,
                pro.project_type AS project_type,
                pro.pricing_type  as pricing_type,
                pro_emp.employee_id AS employee_id,
                0.0 AS overall_budgeted_revenue,
                pro_emp.cost AS budgeted_revenue,
                pro_emp.budgeted_qty AS budgeted_hours,
                0.0 AS overall_hourly_rate,
                0.0 as unit_amount,
                0.0 AS pro_hourly_rate,
                0.0 as timesheet_cost,
                0.0 AS actual_revenue,
                0.0 AS actual_cost,
                0.0 AS expenses_amt,
                0.0 as profit_amt,
                0.0 as profit_per, 	
                null::timestamp as timesheet_sdatetime
                FROM project_project PRO
                Left JOIN project_sale_line_employee_map pro_emp ON pro_emp.project_id = pro.id
                LEFT JOIN account_analytic_account AA ON PRO.analytic_account_id = AA.id
                LEFT JOIN account_analytic_line AAL ON AAL.account_id = AA.id and AAL.project_id = PRO.id and AAL.employee_id = pro_emp.employee_id
                WHERE PRO.pricing_type='employee_rate' and PRO.project_type='hours_in_consultant'
            UNION
               select
                pro.id AS project_id,
                pro.active as project_active,
                (select project_id from project_subproject_rel as par where pro.id=par.id limit 1) as parentproject,	
                pro.partner_id AS partner_id,
                pro.project_type AS project_type,
                pro.pricing_type  as pricing_type,
                null::int AS employee_id,
                pro.budgeted_revenue AS overall_budgeted_revenue,
                0.0 AS budgeted_revenue,
                0.0 AS budgeted_hours,
                pro.hourly_rate AS overall_hourly_rate,
                0.0 as unit_amount,
                0.0 as pro_hourly_rate,               
                0.0 as timesheet_cost,
                0.0 AS actual_revenue,
                0.0 AS actual_cost,
                pro.expenses_amt AS expenses_amt,
                0.0 as profit_amt,
                0.0 as profit_per,	
                null::timestamp as timesheet_sdatetime
                FROM project_project PRO
                WHERE PRO.pricing_type='employee_rate' and PRO.project_type='hours_in_consultant'
            UNION
                select
                pro.id AS project_id,
                pro.active as project_active,
                (select project_id from project_subproject_rel as par where pro.id=par.id limit 1) as parentproject,	
                pro.partner_id AS partner_id,
                pro.project_type AS project_type,
                pro.pricing_type  as pricing_type,
                AAL.employee_id AS employee_id,
                0.0 AS overall_budgeted_revenue,
                0.0 AS budgeted_revenue,
                0.0 AS budgeted_hours,
                0.0 AS overall_hourly_rate,
                AAL.unit_amount,
                COALESCE(pro_emp.price_unit, 0) as pro_hourly_rate,
                case when pro_emp.employee_price is null then  ((AAL.amount * -1)/NULLIF(AAL.unit_amount, 0)) else pro_emp.employee_price end as timesheet_cost,
                (AAL.unit_amount * COALESCE(pro_emp.price_unit, 0)) AS actual_revenue,
                case when pro_emp.employee_price is null then (AAL.amount * -1) else (AAL.unit_amount * pro_emp.employee_price) end as actual_cost,
                0.0 AS expenses_amt,
                (AAL.unit_amount * COALESCE(pro_emp.price_unit, 0)) - case when pro_emp.employee_price is null then (AAL.amount * -1) else (AAL.unit_amount * pro_emp.employee_price) end  
                as profit_amt,
                (((AAL.unit_amount * COALESCE(pro_emp.price_unit, 0)) - case when pro_emp.employee_price is null then (AAL.amount * -1) else (AAL.unit_amount * pro_emp.employee_price) end)
                / NULLIF((AAL.unit_amount * COALESCE(pro_emp.price_unit, 0)), 0)) * 100 as profit_per, 		
                AAL.start_datetime AS timesheet_sdatetime
                FROM project_project PRO
                LEFT JOIN account_analytic_account AA ON PRO.analytic_account_id = AA.id
                LEFT JOIN account_analytic_line AAL ON AAL.account_id = AA.id and AAL.project_id = PRO.id 
                Left JOIN project_sale_line_employee_map pro_emp ON pro_emp.project_id = pro.id and AAL.employee_id = pro_emp.employee_id
                LEFT JOIN hr_employee EMP ON AAL.employee_id = EMP.id
                WHERE PRO.pricing_type='employee_rate' and PRO.project_type='hours_in_consultant'    
                ) res
        )""" % (self._table,))

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        res = super(ProjectRevenueCustomReport2, self).read_group(domain, fields, groupby, offset=offset, limit=limit, orderby=orderby, lazy=lazy)
        hourly_rate = 0
        actual_cost = 0
        for line in res:
            try:
                if 'actual_cost' in line:
                    actual_cost = line['actual_cost']
                """if 'pro_hourly_rate' in line:
                    hourly_rate = line['pro_hourly_rate']
                if 'unit_amount' in line: # and 'pro_hourly_rate' in line and 'id' in line
                    if hourly_rate != 0:
                        line['actual_revenue'] = hourly_rate * line['unit_amount']
                if 'unit_amount' in line and 'timesheet_cost' in line:
                    line['actual_cost'] = line['timesheet_cost'] * line['unit_amount']"""
                if 'overall_budgeted_revenue' in line and 'budgeted_hours' in line:
                    if line['budgeted_hours'] > 0:
                        line['overall_hourly_rate'] = line['overall_budgeted_revenue'] / line['budgeted_hours']
                if 'pro_hourly_rate' in line and 'budgeted_hours' in line:
                    if line['budgeted_hours'] > 0:
                        line['pro_hourly_rate'] = line['budgeted_revenue'] / line['budgeted_hours']
                if 'actual_revenue' in line and 'actual_cost' in line and 'expenses_amt' in line:
                    line['profit_amt'] = line['actual_revenue'] - line['actual_cost'] - line['expenses_amt']
                if 'profit_amt' in line and 'actual_revenue' in line:
                    try:
                        line['profit_per'] = (line['profit_amt'] / line['actual_revenue']) * 100
                    except ZeroDivisionError:
                        pass
                if 'unit_amount' in line:
                    try:
                        line['timesheet_cost'] = actual_cost / line['unit_amount']
                    except ZeroDivisionError:
                        pass
            except Exception:
                pass
        return res

