crm_report.py

# -*- coding: utf-8 -*-

# © 2017 Le Filament (<http://www.le-filament.com>)
# License AGPL-3.0 or later (http://www.gnu.org/licenses/agpl.html).

from odoo import tools
from odoo import api, fields, models


class CrmReport(models.Model):
	_name = "crm.conversion.report"
	_description = "Time Conversion Statistics"
	_auto = False
	_order = 'date_closed desc'

	name = fields.Char('Affaire', readonly=True)
	
	date_open = fields.Datetime('Date Création', readonly=True)
	date_conversion = fields.Datetime('Date Converstion', readonly=True)
	date_closed = fields.Datetime('Date Fermeture', readonly=True)
	days = fields.Integer('Durée', group_operator = 'avg', readonly=True)

	referred = fields.Char('Apporteur')

	partner_id = fields.Many2one('res.partner', 'Client', readonly=True)
	user_id = fields.Many2one('res.users', 'Vendeur', readonly=True)
	team_id = fields.Many2one('crm.team', 'DAS', readonly=True)
	stage_id = fields.Many2one('crm.stage', string='Stage', readonly=True)

	current = fields.Boolean('En Cours', readonly=True)
	probability = fields.Float('Probabilité', group_operator="avg", readonly=True)
	planned_revenue = fields.Float('Revenu Total', readonly=True)
	expected_revenue = fields.Float('Revenu Pondéré', readonly=True)
	
	def _select(self):
		select_str = """
			SELECT
				id,
				name,
				date_open,
				date_closed,
				date_conversion,
				extract(day from 
					(case when date_closed is not null then date_closed else current_date end) - 
					(case when date_open is not null then date_open else create_date end)) as days,
				referred,
				partner_id,
				user_id,
				team_id,
				stage_id,
				active as current,
				probability,
				planned_revenue,
				planned_revenue*(probability/100) as expected_revenue
			FROM
				crm_lead
			WHERE
				type = 'opportunity'
		"""
		return select_str


	def init(self):
		tools.drop_view_if_exists(self.env.cr, self._table)
		self.env.cr.execute("CREATE or REPLACE VIEW crm_conversion_report as (%s)" % (self._select(),) )