#!/usr/bin/env python
"""
previsionnel.py dbhost dbname dbuser dbpwd annee
"""
#import globals
import psycopg
import sys

class Previsionnel:


	def __init__(self, dbhost, dbname, dbuser, dbpwd):
		self.DBHOST = dbhost
		self.DBNAME = dbname
		self.DBUSER = dbuser
		self.DBPWD = dbpwd

	def getConnection(self):
		"""Object connection with database.
		It loads the good 'getinfo' according to 'mode'"""
		
		self.con=psycopg.connect("dbname=%s user=%s password=%s host=%s" % (self.DBNAME, self.DBUSER,
																			self.DBPWD, self.DBHOST))
		self.curs=self.con.cursor()
		self.con.autocommit()


	def get_datas(self):
		self.getConnection()
		self.devis_ordered = []
		self.curs.execute("select uid from devis where (commentaire is null or commentaire not like 'a ventiler%') and ref_statut=1 order by ref_view_client")
		for (uid, ) in self.curs.fetchall():
			self.devis_ordered.append(uid)
		self.devis = {}
		self.curs.execute("""select devis.uid, devis.intitule,societe.nom from devis,societe
		where (devis.commentaire is null or devis.commentaire not like 'a ventiler%') and devis.ref_statut=1
		and devis.ref_view_client=societe.uid""")
		for (uid, intitule, client) in self.curs.fetchall():
			self.devis[uid] = (intitule, client)
		self.montant = {}
		self.curs.execute("""select devis.uid, sum(ligne_commande.montant_ht) from ligne_commande,devis
		where ligne_commande.ref_devis=devis.uid and devis.ref_statut=1
		and (devis.commentaire is null or devis.commentaire not like 'a ventiler%') group by devis.uid""")
		for (uid, prix) in self.curs.fetchall():
			self.montant[uid] = prix
		self.curs.execute("select uid from type_periode where nom='Tous les mois'")
		periode = self.curs.fetchone()[0]
		self.periode = {}
		self.curs.execute("""select ref_devis,date_debut,date_fin_periodicite from evenement_facture where
		ref_statut=1 and ref_type_periode=%s""" % periode)
		for (uid, debut, fin) in self.curs.fetchall():
			self.periode[uid] = (debut, fin)
		
		self.periode_trim = {}
		self.curs.execute("select uid from type_periode where nom='trimestriel'")
		periode = self.curs.fetchone()[0]
		self.curs.execute("""select ref_devis,date_debut,date_fin_periodicite from evenement_facture where
		ref_statut=1 and ref_type_periode=%s""" % periode)
		for (uid, debut, fin) in self.curs.fetchall():
			self.periode_trim[uid] = (debut, fin)


		self.periode_ann = {}
		self.curs.execute("select uid from type_periode where nom='annuel'")
		periode = self.curs.fetchone()[0]
		self.curs.execute("""select ref_devis,date_debut,date_fin_periodicite from evenement_facture where
		ref_statut=1 and ref_type_periode=%s""" % periode, )
		for (uid, debut, fin) in self.curs.fetchall():
			self.periode_ann[uid] = (debut, fin)


		self.curs.execute("""select uid,date_emission from devis where
		(commentaire is null or commentaire not like 'a ventiler%') and ref_statut=1""")
		for (uid, debut) in self.curs.fetchall():
			if not self.periode.has_key(uid) and not self.periode_trim.has_key(uid) and not self.periode_ann.has_key(uid):
				self.periode[uid] = (debut, debut)


	def compute(self, year):
		total = 0.0
		format = "%s\t%20.20s\t%20.20s\t%s\t%s\t%s\t%s"
		nb_mois = pu = 0
		total_client = {}
		for uid in self.devis_ordered:
			if self.periode.has_key(uid):
				start, end = self.periode[uid]
				if start and start.year > year:
					continue
				if end and end.year < year:
					continue
				if start and start.year == year:
					start_month = start.month
				else:
					start_month = 1
				if end and end.year == year:
					end_month = end.month
				else:
					end_month = 12
				nb_mois = end_month - start_month + 1
				pu = self.montant.get(uid, 0.0)
			elif self.periode_trim.has_key(uid):
				start, end = self.periode_trim[uid]
				if start and start.year > year:
					continue
				if end and end.year < year:
					continue
				if start and start.year == year:
					start_month = start.month
				else:
					start_month = 1
				if end and end.year == year:
					end_month = end.month
				else:
					end_month = 12
				nb_mois = (end_month - start_month + 1) / 3.0
				pu = self.montant.get(uid, 0.0)
			elif self.periode_ann.has_key(uid):
				start, end = self.periode_ann[uid]
				if start and start.year > year:
					continue
				if end and end.year < year:
					continue
				nb_mois = 1
				pu = self.montant.get(uid, 0.0)
			else:
				continue
			ss_total = pu * nb_mois 
			total += ss_total
			client = self.devis[uid][1]
			total_client[client] = total_client.get(client, 0.0) + ss_total
			print format % (uid, client, self.devis[uid][0], pu, nb_mois, ss_total, total)

		print "\nRepartition par client de %d euros pour %s\n" % (total, year)
		for client, montant in total_client.items():
			print "%30.30s\t%d\t%.0f%%" % (client, montant, 100 * montant / total)

				
if __name__ == '__main__':
	if len(sys.argv) != 6:
		print __doc__
		sys.exit(2)
	annee = int(sys.argv[5])
	p = Previsionnel(sys.argv[1],sys.argv[2],sys.argv[3],sys.argv[4])
	p.get_datas()
	p.compute(annee)