#!/Usr/bin/env python
# -*- coding:latin-1 -*-

""" 
   Copyright (C) 2003-2005 PimenTech SARL (http://www.pimentech.net)

   Pimengest2 is free software; you can redistribute it and/or
   modify it under the terms of the GNU General Public License as
   published by the Free Software Foundation; either version 2 of the
   License, or (at your option) any later version.

   Pimengest2 is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
   General Public License for more details.

   You should have received a copy of the GNU General Public
   License along with this library; see the file COPYING.LIB.  If not,
   write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
   Boston, MA 02111-1307, USA.	
"""

from genpdf import *

class genfacture(genpdf):
	
	query_article = """
SELECT a.nom, a.description, a.code, tva.taux, ca.quantite, ca.declinaison, ca.remise, ca.montant_ht AS ca_ht, ca.montant_ttc AS ca_ttc
FROM article a, commande_article ca, facture f, devis d, tva, statut st
WHERE f.uid=%s AND f.ref_devis=d.uid AND d.uid=ca.ref_devis AND ca.ref_article=a.uid AND tva.uid=a.ref_tva AND ca.ref_statut=st.uid AND st.nom='valide'"""

	query_presta = """
SELECT cp.uid, cp.intitule AS prestation, cp.description, cp.remise, cp.montant_ht, cp.montant_ttc
FROM commande_prestation cp, devis d, facture f, statut st
WHERE f.uid=%s AND f.ref_devis=d.uid AND cp.ref_devis=d.uid AND cp.ref_statut=st.uid AND st.nom='valide'
ORDER BY cp.uid"""

	tokens={"ENTETE":"%# entete_from_genpdf", "CLIENT":"~", "ADRESSE_CLIENT":"~", "CP_CLIENT":"~", "VILLE_CLIENT":"~", "NUM_FACTURE":"~", "DATE_EMISSION":"~", "DATE_ECHEANCE":"~", "DEVIS_INTITULE":"~", "DESCRIPTION_PRESTATION":"~", "ARTICLES":"~", "PRESTAS":"~", "GLOBAL_HT":"~", "GLOBAL_TTC":"~", "REGLE":"0", "PAIEMENT":"~", "PERIODE":"~", "MONTANT_HT":"~", "ESCOMPTE":"~", "TOTAL_TVA":"~", "DETAIL_TVA":"~", "DETAIL_TVA_RATIO":"~", "TVA":"~", "CHARGE":"0", "CHARGE_COMMENTAIRE":"%# commentaire_from_genpdf", "MONTANT_TTC":"~", "FICHIER_LOGO":"~", "FICHIER_PIED":"~", "RAISON":"~", "ACCROCHE":"~", "MON_ADRESSE":"~", "MON_CP":"~", "MA_VILLE":"~", "MON_TEL":"~", "MON_FAX":"~", "MON_MAIL":"~", "SIRET":"~", "UE_TVA":"~", "FAC_DESCRIPTION":"~", "RAISON":"~", "CLIENT_UETVA":"~", }
	
	def __init__(self, uid, with_header, verbose, filedir):
		genpdf.__init__(self, uid, with_header, verbose, filedir)
		
	def getinfo_facture(self):

		req="""
SELECT f.id, f.date_echeance, f.date_emission, f.montant_ht, f.montant_ttc, f.description AS fac_desc, d.intitule, d.id AS id_devis, devise.nom AS devise, devise.symbole , s.nom AS client, s.adresse1, s.cp, s.ville, s.ue_tva as cli_ue_tva, ms.logo, ms.pied_page, ms.raison, ms.adresse1 as mon_adresse, ms.cp as mon_cp,ms.ville as ma_ville,'TEL : '||ms.tel as mon_tel,'FAX : '||ms.fax as mon_fax,'EMAIL : '||ms.email as mon_mail,ms.siret,ms.ue_tva,ms.commentaire AS accroche,
(SELECT coalesce(sum(montant_ht),0) FROM facture WHERE ref_devis=d.uid AND ref_statut=1 AND datecrea<=f.datecrea) AS deja_facture
FROM devis d
INNER JOIN facture f ON (f.uid=%s AND f.ref_devis=d.uid)
LEFT JOIN devise ON (d.ref_devise=devise.uid)
LEFT JOIN view_mes_societes ms ON (d.ref_view_mes_societes=ms.uid),
view_client s
WHERE d.ref_view_client=s.uid""" % self.UID
			
		self.curs.execute(req)
		if self.curs.rowcount!=1:
			self.DEBUG_INFO+=req
			print req
			raise "ERROR"
		else:
			res=self.curs.dictfetchone()
			if res['devise']=="Euro":
				devise=" \\euro"
			elif res['symbole']!='':
				devise=' '+res['symbole']
			elif res['devise']!='':
				devise=' '+res['devise']
			else:
				devise=' ?'
				
			(total_ht, total_ttc) = self.get_articles_prestas(devise, res['montant_ht'])
			(remise_ht, charge_ht, remise_ttc, charge_ttc) = self.get_charges_remises(total_ht, total_ttc)

			if res['montant_ht']<0:
				self.tokens['TYPE_FACTURE'] = "NOTE DE CREDIT"
			else:
				self.tokens['TYPE_FACTURE'] = "FACTURE"
	
			if res['logo']:
				self.tokens['FICHIER_LOGO'] = self.FILEDIR + "/" + res['logo']
			if res['pied_page']:
				self.tokens['FICHIER_PIED'] = self.FILEDIR + "/" + res['pied_page']
			self.tokens['MON_ADRESSE']=res['mon_adresse']
			self.tokens['MON_CP']=res['mon_cp']
			self.tokens['MA_VILLE']=res['ma_ville']
			self.tokens['MON_TEL']=res['mon_tel']
			self.tokens['MON_FAX']=res['mon_fax']
			self.tokens['MON_MAIL']=res['mon_mail']
			self.tokens['RAISON']=res['raison']
			if res['siret']:
				self.tokens['SIRET']='RCS: '+res['siret']
			if res['ue_tva']:
				self.tokens['UE_TVA']='TVA-IN. '+res['ue_tva']
				
			self.tokens['ACCROCHE']=res['accroche']

			self.tokens['NUM_FACTURE']=res['id']
			montant_ht = float(str(res.get('montant_ht','0')).split(' ')[0])
			montant_ttc = float(str(res.get('montant_ttc','0')).split(' ')[0])
			if res['date_emission']:
				self.tokens['DATE_EMISSION']= "%s" % res['date_emission'].strftime('%d %B %Y')
			if res['date_echeance']:
				self.tokens['DATE_ECHEANCE']= "%s" % res['date_echeance'].strftime('%d %B %Y')
			if montant_ht:
				self.tokens['MONTANT_HT']= "%s%s" % (euro_round_fr(montant_ht), devise)
			if montant_ttc:
				self.tokens['MONTANT_TTC']= "%s%s" % (euro_round_fr(montant_ttc), devise)
			if montant_ttc and montant_ht:
				self.tokens['TVA']= "%s%s" % (euro_round_fr(montant_ttc-montant_ht), devise)

			self.tokens['CLIENT']=res['client']
			self.tokens['CP_CLIENT']=res['cp']
			self.tokens['ADRESSE_CLIENT']=res['adresse1']
			self.tokens['VILLE_CLIENT']=res['ville']
			self.tokens['CLIENT_UETVA']=res['cli_ue_tva']

			if res['intitule']:
				self.tokens['DEVIS_INTITULE']=res['intitule']
			else:
				self.tokens['DEVIS_INTITULE']=res['id_devis']
		
			if res['fac_desc']:
				self.tokens['FAC_DESCRIPTION']= "\\begin{large}\n{\\bf \\noindent Description}\\end{large}\\\\\n\\vspace{-0.3cm}\\hline\n\\vspace{0.3cm}\n\\noindent %s" % res['fac_desc']
			self.tokens['GLOBAL_HT']= "Global Invoice WT & %s%s\\\\\n" % (euro_round_fr(total_ht-remise_ht+charge_ht), devise)
			if remise_ht != 0:
				self.tokens['GLOBAL_HT'] += "Dont Escompte & %s%s\\\\" % (euro_round_fr(remise_ht), devise)
			if charge_ht != 0:
				self.tokens['GLOBAL_HT'] += "Dont Charge Add. & %s%s\\\\" % (euro_round_fr(charge_ht), devise)
			self.tokens['GLOBAL_TTC']= "%s%s" % (euro_round_fr(total_ttc-remise_ttc+charge_ttc), devise)
			self.tokens['TOTAL_TVA']= "%s%s" % (euro_round_fr((total_ttc-remise_ttc+charge_ttc)-(total_ht-remise_ht+charge_ht)), devise)

			txt = txt_ratio = ""
			ratio = montant_ht / float(total_ht-remise_ht+charge_ht)
			for taux in add_tva.taux.keys():
				txt += "Total  %s TI%% & %s \\euro\\\\\n" % (taux, euro_round_fr(add_tva.taux[taux] * float(taux) / 100))
				sys.stderr.write("Total with %s VAT %% : %s\n" % (taux, euro_round_fr(add_tva.taux[taux] * float(taux) / 100)))
				
				if ratio > 0.001 :
					txt_ratio += "%s VAT%% & %s \\euro\\\\\n" % (taux, euro_round_fr(add_tva.taux[taux] * float(taux) * ratio / 100))
					sys.stderr.write("%s vat%% : %s\n" % (taux, euro_round_fr(add_tva.taux[taux] * float(taux) * ratio / 100)))
					
			self.tokens['DETAIL_TVA'] = txt
			self.tokens['DETAIL_TVA_RATIO'] = txt_ratio

			if ratio <= 0.995:
				reste = (total_ht-remise_ht+charge_ht) - res['deja_facture']
				type_facture = 'Deposit'
				if reste < 1:
					type_facture = 'Balance'
				self.tokens['ACCOMPTE'] = "\hline\n%s of %.1f%% (WT) & %s \\euro\\\\\n" % (
					type_facture, ratio*100, euro_round_fr(montant_ht))
				if reste >= 1 :
					self.tokens['ACCOMPTE'] += "\n\\footnotesize Remains in charge (HT) & \\footnotesize %s \\euro\\\\\n" % euro_round_fr(reste)
				self.tokens['ACCOMPTE'] += self.tokens['DETAIL_TVA_RATIO']
				self.tokens['ACCOMPTE'] += "{\\bf Amount Due} & {\\bf %s \\euro}\\\\\n\\hline\n" % euro_round_fr(montant_ttc)
			else:
				self.tokens['ACCOMPTE'] = ""

		# Deja Regle
		req="SELECT sum(rf.montant) AS montant FROM reglement r, reglement_facture rf, statut st, facture f WHERE rf.ref_reglement=r.uid AND r.ref_statut=st.uid AND f.ref_statut=st.uid AND rf.ref_statut=st.uid AND st.nom='valide' AND rf.ref_facture=f.uid AND f.uid=%s" % self.UID
		self.curs.execute(req)
		if self.curs.rowcount!=1:
			self.DEBUG_INFO+=req
			print req
			raise "ERROR"
		else:
			res=self.curs.dictfetchone()
			if res['montant']==None:
				self.tokens['REGLE']= "0"+devise
			else:
				self.tokens['REGLE']= "%s%s" % (euro_round_fr(res['montant']), devise)
	
	def get_charges_remises(self, total_ht, total_ttc):
		"""Get info from table "charge_additionnelle" for facture and devis"""

		req="""SELECT ca.nom, ca.montant_ttc, ca.montant_ht, ca.absolu
		FROM charge_additionnelle ca, statut s, facture f
		WHERE ca.ref_statut=s.uid AND s.nom='valide' AND ca.ref_devis=f.ref_devis AND f.uid=%s
		ORDER BY ca.uid""" % self.UID

		calcul_remise_ht=0
		calcul_charge_ht=0
		calcul_remise_ttc=0
		calcul_charge_ttc=0
		
		self.curs.execute(req)
		if self.curs.rowcount==0:
			pass
		else:
			i=0
			self.tokens['CHARGE_COMMENTAIRE']= r"""
			\vspace*{0.5cm}
			{\bf \noindent Nota :}
			\begin{itemize}"""
			for i in range(self.curs.rowcount):
				res=self.curs.dictfetchone()
				if res['absolu']==1:
					add_tva(res['montant_ht'], res['montant_ttc'])
					self.tokens['CHARGE_COMMENTAIRE']+= '\\item ' +res['nom']+ ' (%s HT) \n' % euro_round_fr(res['montant_ht'])
					if res['montant_ht']<0:
						calcul_remise_ht +=-res['montant_ht']
						calcul_remise_ttc +=-res['montant_ttc']
					else:
						calcul_charge_ht +=res['montant_ht']
						calcul_charge_ttc +=res['montant_ttc']
				else:
					add_tva(res['montant_ht'])
					self.tokens['CHARGE_COMMENTAIRE'] += '\\item ' +res['nom']+ ' (%s\\%%) \n' % euro_round_fr(res['montant_ht'])
					if res['montant_ht']<0:
						calcul_remise_ht += (-(total_ht-calcul_remise_ht+calcul_charge_ht)*(res['montant_ht']/100.0))
						calcul_remise_ttc += (-(total_ttc-calcul_remise_ttc+calcul_charge_ttc)*(res['montant_ht']/100.0))
					else:
						calcul_charge_ht += (total_ht-calcul_remise_ht+calcul_charge_ht)*(res['montant_ht']/100.0)
						calcul_charge_ttc += (total_ttc-calcul_remise_ttc+calcul_charge_ttc)*(res['montant_ht']/100.0)
						
			self.tokens['CHARGE_COMMENTAIRE'] += '\\end{itemize}\n'
				
		return calcul_remise_ht, calcul_charge_ht, calcul_remise_ttc, calcul_charge_ttc

	
def main():
	options=get_options()
	OUTFILE=options['OUTDIR']+"/facture"
	if options['TEMPLATE'] == '':
		options['TEMPLATE']='template_facture.tex'

	base=extract_base(options['DATABASE'])
	if not base:
		usage(get_options.__doc__)

	try:
		os.system('rm -f '+options['OUTDIR']+ '/pimengest2_debug')
		object=genfacture(options['UID'], options['WITH_ENTETE'], options['VERBOSE'], options['FILEDIR'])
		object.getConnection(base['base'], base['user'], base['pass'], base['host'], base['port'])
		object.getinfo_facture()
		object.replace_values(options['AFFICHAGE_TAB'], options['LATEXDIR'], options['TEMPLATE'])
	except 'ERROR':
		log(options['OUTDIR']+'/pimengest2_debug', '\n---\nCertainly not enougth information to generate the "facture". This is the query :\n---\n')
		log(options['OUTDIR']+'/pimengest2_debug', object.DEBUG_INFO)
		sys.exit(1)
		
	if not check_output(options['OUTDIR']):
		os.makedirs(options['OUTDIR'])
		
	object.savetexfile(OUTFILE)
	if options['WITH_PDF']:
		print object.toPDF(options['OUTDIR'], OUTFILE)


if __name__ == '__main__':
	main()