project.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 datetime import datetime, date
import time
from odoo import tools
from odoo import models, fields, api, osv

class FilamentProjet(models.Model):
	_inherit = 'project.project'

	use_prospection = fields.Boolean("Inclure Prospection", default=False)
	lf_total_budget = fields.Float('Budget Projet',)
	lf_heures_budget = fields.Float('Budget Heures', compute='_budget_heures')
	lf_tarif_jour = fields.Float('Tarif Jour',)
	lf_taux_horaire = fields.Float('Taux Horaire', compute='_taux_horaire')
	lf_heures_prospection = fields.Float('Prospection (h)', compute='_total_heures_prospection')
	lf_heures_projet = fields.Float('Allouées (h)', compute='_total_heures')
	lf_heures_passees = fields.Float('Heures passées', compute='_total_heures_passees')
	lf_heures_restantes = fields.Float('Restant (h)', compute='_total_heures_restantes')
	lf_heures_planifiees = fields.Float('Planifiées (h)', compute='_total_heures_planifiees')
	lf_couts_annexes =  fields.Float('Coûts Annexes', compute='_couts_annexes')
	lf_couts_estimes =  fields.Float('Coûts Estimés')
	lf_commentaire = fields.Text('Commentaires')
	lf_delay = fields.Integer('Délai facture', compute='_delay')
	lf_total_days = fields.Integer('Durée projet', compute='_total_days')
	lf_advance = fields.Float('Cash avancé', compute='_advance')
	lf_balance = fields.Float('Balance Actuelle', compute='_balance')

	####################################################
	###                Fields Function               ###
	####################################################

	@api.one
	def _taux_horaire(self):
		lf_heures_jour = self.env['ir.values'].get_default('project.config.settings', 'lf_heures_jour')
		self.lf_taux_horaire = self.lf_tarif_jour / lf_heures_jour

	@api.one
	def _total_heures_prospection(self):
		project = self.id		
		self.lf_heures_prospection = 0.0
		## Calcul heures   
		if self.use_prospection:
			self.env.cr.execute("select sum(aal.unit_amount) from account_analytic_line aal, project_task pt where aal.project_id=%s and pt.name like %s and pt.id=aal.task_id;", (project, "Prospection%",) )
			heures_prospection = self.env.cr.fetchone()[0]
			if heures_prospection:
				self.lf_heures_prospection = heures_prospection

	@api.one
	def _total_heures_passees(self):
		project = self.id
		self.lf_heures_passees = 0.0		
		## Calcul heures   
		self.env.cr.execute("select sum(unit_amount) from account_analytic_line where project_id=%s;", (project, ) )
		heures_passees = self.env.cr.fetchone()[0]
		if heures_passees:
			self.lf_heures_passees = heures_passees
			if self.use_prospection:
				self.lf_heures_passees -= self.lf_heures_prospection

	@api.one
	def _total_heures_planifiees(self):
		res = 0.0
		for record in self.task_ids:
			res = res + record.planned_hours
		self.lf_heures_planifiees = res

	@api.one
	def _couts_annexes(self):
		account = self.analytic_account_id.id		
		##############    Calcul couts annexes   ################
		self.env.cr.execute("select sum(untaxed_amount) from hr_expense where analytic_account_id=%s;", (account, ) )
		couts_annexes = self.env.cr.fetchone()[0]
		if couts_annexes:
			self.lf_couts_annexes = couts_annexes
		else:
			self.lf_couts_annexes = 0.0

	@api.one
	@api.depends('lf_total_budget','lf_couts_annexes')
	def _budget_heures(self):
		self.lf_heures_budget = self.lf_total_budget - self.lf_couts_estimes

	@api.one
	def _total_heures(self):
		lf_heures_jour = self.env['ir.values'].get_default('project.config.settings', 'lf_heures_jour')
		if (self.lf_tarif_jour != 0.0):
			self.lf_heures_projet = (self.lf_heures_budget / self.lf_tarif_jour) * lf_heures_jour
		else:
			self.lf_heures_projet = 0.0

	@api.one
	def _total_heures_restantes(self):
		self.lf_heures_restantes = self.lf_heures_projet - self.lf_heures_passees

	## Time to first invoice
	@api.one
	def _delay(self):
		account_id = self.analytic_account_id.id
		self.env.cr.execute("""
			SELECT date 
			FROM account_analytic_line 
			where account_id=%s and amount < 0 order by date limit 1
			;""", (account_id, ) )
		d_cost = self.env.cr.fetchone()

		self.env.cr.execute("""
			SELECT date 
			FROM account_analytic_line 
			where account_id=%s and amount < 0 order by date desc limit 1
			;""", (account_id, ) )
		d_cost_last = self.env.cr.fetchone()

		self.env.cr.execute("""
			SELECT date 
			FROM account_analytic_line 
			where account_id=%s and amount > 0 order by date limit 1
			;""", (account_id, ) )
		d_invoice = self.env.cr.fetchone()

		if d_cost:
			if d_invoice:
				self.lf_delay = (datetime.strptime(d_invoice[0], "%Y-%m-%d").date() - datetime.strptime(d_cost[0], "%Y-%m-%d").date()).days
			else:
				self.lf_delay = (datetime.strptime(d_cost_last[0], "%Y-%m-%d").date() - datetime.strptime(d_cost[0], "%Y-%m-%d").date()).days
		else:
			self.lf_delay = 0

	## Total Days
	@api.one
	def _total_days(self):
		account_id = self.analytic_account_id.id
		self.env.cr.execute("""
			SELECT 
			date(d_last) - date(d_first) as datediff
			from 
			(select date FROM account_analytic_line where account_id=%s order by date limit 1) as d_first,
			(select date FROM account_analytic_line where account_id=%s order by date desc limit 1) as d_last
			;""", (account_id, account_id, ) )
		lf_total_days = self._cr.fetchone()
		
		if lf_total_days:
			self.lf_total_days = lf_total_days[0]
		else:
			self.lf_total_days = 0

	## Balance
	@api.one
	def _balance(self):
		account_id = self.analytic_account_id.id
		self.env.cr.execute("""
			SELECT sum(amount) as Total
			FROM account_analytic_line
			WHERE account_id=%s
			;""", (account_id, ) )
		self.lf_balance = self._cr.fetchone()[0]


	## Advance cash
	@api.one
	def _advance(self):
		account_id = self.analytic_account_id.id
		self.env.cr.execute("""
			SELECT sum(amount) as Total
			FROM account_analytic_line
			WHERE account_id=%s
			and date < (case when
			(select date FROM account_analytic_line where account_id=%s and amount > 0 order by date limit 1) IS NOT NULL then
			(select date FROM account_analytic_line where account_id=%s and amount > 0 order by date limit 1) else current_date end
			);""", (account_id, account_id, account_id, ) )
		self.lf_advance = self._cr.fetchone()[0]

	
	####################################################
	###                  Actions                     ###
	####################################################

	def open_project(self):
	    return {
	        'type': 'ir.actions.act_window',
	        'name': 'Projet' + self.name,
	        'view_mode': 'kanban',
	        'res_model': 'project.project',
	        'res_id': self.id, 
	        'views': [(False, 'kanban')],
	    }

	def open_cash_flow(self):
		return {
	        'type': 'ir.actions.client',
	        'name': 'Cash Flow - ' + self.analytic_account_id.name,
	        'tag': 'lefilament_projets.cashFlow',
	        'target': 'new',
	        'params': {
	        	'account_id': self.analytic_account_id.id,
	        	'project_id': self.id,
	        },
	    }

	####################################################
	###                Widget Function               ###
	####################################################

	@api.model
	def cash_flow(self, account_id, project_id):
		account = self.env['account.analytic.account'].search([('id', '=', account_id)])
		project = self.env['project.project'].search([('id', '=', project_id)])
		
		## Analytic account name
		nom = account.name

		## Cash Flow Request
		self.env.cr.execute("""
			SELECT date, sum(sum(amount)) over (order by date) as Total,
			sum(sum(case when product_uom_id = 5 then amount else 0 end)) over (order by date) as Timesheet,
			sum(sum(case when (product_uom_id = 1 or product_uom_id is null) and amount < 0 then amount else 0 end)) over (order by date) as Fournisseurs,
			sum(sum(case when product_uom_id = 1 and amount > 0 then amount else 0 end)) over (order by date) as Clients
			FROM account_analytic_line
			WHERE account_id=%s
			group by date;
			""", (account_id, ) )
		cash_flow = self._cr.dictfetchall()

		## Time to first invoice
		delay = project.lf_delay

		## Total Days
		total_days = project.lf_total_days

		## Balance
		balance = project.lf_balance

		## Advance cash
		advance = project.lf_advance	

		return {
			'nom' : nom,
			'cash_flow': cash_flow,
			'delay': delay,
			'balance': balance,
			'advance': advance,
			'total_days': total_days,
			}