#!/usr/bin/perl -w

use strict;
use POSIX qw( strftime);
use Time::Local;
use Pg;

#=============================
#
#      SUB's

sub usage {
    print "usage:\n
mig2compta.pl [-o <output file>] -h DBHOST -u USER -p PWD [-c]
";
} 

sub getArgs {
    my @args=@_;
    my $oldKey="";
    my %opts=();
	my $el;
    for $el (@args) {
		if ($el=~/^-/) {
			$el=~s/-+//g;
			$opts{$el}="";
			$oldKey=$el;
		}
		else {
			$opts{$oldKey}=$el;
		}
    }
    return %opts;
}

sub connectPg {
    my ($DBHOST,$DBNAME,$DBUSER,$DBPWD)=@_;
	my $debug=0;
    # Ouverture connexion postgres
    my $req={};
    my $conn = Pg::connectdb("dbname=$DBNAME user=$DBUSER host=$DBHOST password=$DBPWD");
    print "dbname=$DBNAME user=$DBUSER password=$DBPWD host=$DBHOST\n" if $debug;
    if (PGRES_CONNECTION_OK ne $conn->status) {
		print STDERR "dbname=$DBNAME user=$DBUSER password=$DBPWD host=$DBHOST";
    }
	return $conn;
}

sub round{
	my($l_arg1,$l_scale)=@_;
	my $sign=1;
	if ($l_arg1<0) { $l_arg1=-$l_arg1; $sign=-1 ;}
	return 0 if ($l_arg1 == 0);
	my($l_num1,$l_num2) = 0;
	$l_num1 = 10**$l_scale * $l_arg1;
	$l_num2 = int($l_num1);
	$l_num2 = $l_num2 +1 if ($l_num1 - $l_num2 >= 0.5);
	$l_num1 =  $l_num2/10**$l_scale;
	return $l_num1*$sign; 
}

#=============================


my $sql;
my $output;
my $conn;
my %opts=();
my $complete=0;
my @lrglt;
my @lachat;
my @lsalaire;
my @lcharge;
my @lchargesoc;
my @lfact;  
my @lcomptes;
my %lclient;
my %client=();
my $result;
my @merge=();

%opts=getArgs(@ARGV);

if (exists $opts{help}) {
	&usage();
	exit;
}
if (!exists $opts{p}) {
	&usage();
	exit;
}
if (!exists $opts{u}) {
	&usage();
	exit;
}
if (!exists $opts{h}) {
	&usage();
	exit;
}

if (exists $opts{c}) {
	$complete=1;
}

if (exists $opts{o}) {
    $output=$opts{o};
    open(OUT,">$output");
    *STDOUT=*OUT;
}

my ($USER,$DBNAME,$DBPWD,$DBHOST);
$USER=$opts{u};
$DBNAME=$opts{n};
$DBPWD=$opts{p};
$DBHOST=$opts{h};

$conn=connectPg("$DBHOST","$DBNAME","$USER","$DBPWD") || die "not connected $!";


# Recup des reglements #
#======================#
#        0 -> date , 1 -> montant_euro , 2 -> compte client , 3 -> text , 4 -> float 
$sql=" 
set datestyle to 'SQL,EURO';
SELECT date_part('epoch',datepaye),sum(montant*devise.nbeuros),client.compte as compte_client,client.nom as client_nom,0 from reglement,devise where client.uid=client and devise.uid=devise group by compte_client,client.nom,datepaye,devise.uid order by datepaye ASC ";

$result=$conn->exec($sql); 
if (PGRES_TUPLES_OK ne $result->resultStatus) {
	print STDERR "ERREUR  : $sql failed : ".$conn->errorMessage;
	exit 0;
}

while (my @row = $result->fetchrow) {
	push (@lrglt,\@row);
}

# Recup des achats négatifs (remboursements) #
#======================#
#        0 -> date , 1 -> montant_euro , 2 -> client , 3 -> text , 4 -> float 
$sql=" 
set datestyle to 'SQL,EURO';
SELECT date_part('epoch',datepaye),-sum(montanttc*devise.nbeuros),fournisseur.compte,'achat',0 from achat,fournisseur,devise where devise.uid=devise and fournisseur.uid=fournisseur group by fournisseur.compte,datepaye,devise.uid having  sum(montanttc*devise.nbeuros)<0 order by datepaye ASC ";

$result=$conn->exec($sql); 
if (PGRES_TUPLES_OK ne $result->resultStatus) {
	print STDERR "ERREUR  : $sql failed : ".$conn->errorMessage;
	exit 0;
}

while (my @row = $result->fetchrow) {
	push (@lrglt,\@row); # on met les achats négatifs dans les rglts pour rapprocher
}

# Recup des achats (déboursements) #
#======================#
#        0 -> date (timestamp) , 1 -> montant_euro , 2 -> compte fournisseur , 3 -> text , 4 -> float , 5 -> no chèque , 6 -> uid, 7 -> montant_ht
$sql=" 
set datestyle to 'SQL,EURO';
SELECT date_part('epoch',datepaye),montanttc*devise.nbeuros,fournisseur.compte,abstract,0,nocheque,achat.uid,montantht*devise.nbeuros from achat,fournisseur,devise where devise.uid=devise and fournisseur.uid=fournisseur and montanttc>0 order by datepaye ASC ";

$result=$conn->exec($sql); 
if (PGRES_TUPLES_OK ne $result->resultStatus) {
    print STDERR "ERREUR  : $sql failed : ".$conn->errorMessage;
	exit 0;
}

while (my @row = $result->fetchrow) {
	push (@lachat,\@row); # on met les achats négatifs dans les rglts pour rapprocher
}


# Recup des charges #
#======================#
#        0 -> date + 1e9 , 1 -> montant_euro , 2 -> compte organisme , 3 -> text , 4 -> float , 5 -> cheque , 6 -> uid
{
    $sql=" 
set datestyle to 'SQL,EURO';
SELECT date_part('epoch',timestamp(datepaye)),montant*devise.nbeuros,organisme.compte,abstract,0,cheque,charges.uid from charges,organisme,devise where devise.uid=devise and organisme.uid=organisme order by datepaye ASC ";
    
    $result=$conn->exec($sql); 
    if (PGRES_TUPLES_OK ne $result->resultStatus) {
	print STDERR "ERREUR  : $sql failed : ".$conn->errorMessage;
	exit 0;
    }
    
    while (my @row = $result->fetchrow) {
	push (@lcharge,\@row);
    }
    
# et des charges sociales 
    
    $sql=" 
set datestyle to 'SQL,EURO';
SELECT date_part('epoch',timestamp(datepaye)),montant*devise.nbeuros,organisme.compte,abstract,0,cheque,ch_sociales.uid from ch_sociales,organisme,devise where devise.uid=devise and organisme.uid=organisme order by datepaye ASC ";
    
    $result=$conn->exec($sql); 
    if (PGRES_TUPLES_OK ne $result->resultStatus) {
	print STDERR "ERREUR  : $sql failed : ".$conn->errorMessage;
	exit 0;
    }
    
    while (my @row = $result->fetchrow) {
	push (@lcharge,\@row);
    }
}

# Recup des factures #
#======================#
#        0 -> date , 1 -> montantht_euro , 2 -> montantht_euro , 3 -> compte client , 4 -> text , 5 -> float 
$sql=" 
set datestyle to 'SQL,EURO';
SELECT date_part('epoch',dateemission),montantht*devise.nbeuros,montanttc*devise.nbeuros,client.compte as compte_client,id || ' ' || client.nom,0 from factures,devise,client where client.uid=client and devise.uid=devise order by dateemission ASC ";

$result=$conn->exec($sql); 
if (PGRES_TUPLES_OK ne $result->resultStatus) {
	print STDERR "ERREUR  : $sql failed : ".$conn->errorMessage;
	exit 0;
}

while (my @row = $result->fetchrow) {
	push (@lfact,\@row);
}

# Recup des salaires   #
#======================#
#        0 -> date , 1 -> montant_euro , 2 -> intervenant , 3 -> text , 4 -> float , 5 -> comment , 6 -> uid
$sql=" 
set datestyle to 'SQL,EURO';
SELECT date_part('epoch',timestamp(date('25/' || mois || '/' || annee))),net*devise.nbeuros,intervenant.compte,typesalaire.nom ||'-' || intervenant.nom||'- '||mois || '/' || annee,0,comment,salaire.uid from salaire,intervenant,devise where devise.uid=devise and intervenant.uid=intervenant and typesalaire.uid=typesalaire order by annee,mois ASC ";

$result=$conn->exec($sql); 
if (PGRES_TUPLES_OK ne $result->resultStatus) {
	print STDERR "ERREUR  : $sql failed : ".$conn->errorMessage;
	exit 0;
}

while (my @row = $result->fetchrow) {
	push (@lsalaire,\@row);
}


# Mapping des comptes #
#======================#
#   
my %compte_IdUid;
my %compte_UidId;
$sql=" 
set datestyle to 'SQL,EURO';
SELECT uid,id,intitule from compte ";

$result=$conn->exec($sql); 
if (PGRES_TUPLES_OK ne $result->resultStatus) {
	print STDERR "ERREUR  : $sql failed : ".$conn->errorMessage;
	exit 0;
}

while (my @row = $result->fetchrow) {
	push (@lcomptes,\@row);
	#print "je charge en memoire : compte_IdUid{".$row[0]."}=".$row[1]."\n";
	$compte_IdUid{$row[0]}=$row[1];
	$compte_UidId{$row[1]}=$row[0];
}


## On trie les entrées MVT et RGLT par date ##
@lachat= sort {$::a->[0] <=> $::b->[0]} @lachat;
@lsalaire= sort {$::a->[0] <=> $::b->[0]} @lsalaire;
@lcharge= sort {$::a->[0] <=> $::b->[0]} @lcharge;
@lchargesoc= sort {$::a->[0] <=> $::b->[0]} @lchargesoc;




#=================================================================================#
#=================================================================================#

#=================================================================================#
#=================================================================================#

for my $r_achat (@lachat) {
	# on :
    #  debite le compte achat (607) (HT)
    #  credite le compte fournisseur (TTC)
    #  debite le compte fournisseur (paiement) (TTC)
	#  credite le compte BQ (TTC)
    #  debite le compte TVA
    #      0 -> date + 1e9 , 1 -> montant_euro , 2 -> compte fournisseur , 3 -> text , 4 -> float , 5 -> no chèque , 6 -> uid, 7 -> montantht
	my $req;
	my $tva=$r_achat->[1]-$r_achat->[7];
	$tva=round($tva,2);
	$r_achat->[1]=round($r_achat->[1],2);
	$r_achat->[7]=round($r_achat->[7],2);
	$r_achat->[3]=~s/\'/''/gc; 
	$r_achat->[3]=~s/\r\n/ /gc; 
	# On caractérise le libelle pour retrouver le groupe_ecriture :
	my $libelle="ACHAT ".$r_achat->[3]." (Eur:".round($r_achat->[1],2).") - [".$r_achat->[6]."]";
	my $subselect="(select uid from groupe_ecriture where libelle like '%[".$r_achat->[6]."]%')";
	my $annee="date_part('year',date(timestamp($r_achat->[0])))";
	$req=<<EOF;
	------------------------
	insert into groupe_ecriture (libelle,authcrea,authmodif,datecrea,datemodif,valide,exercice) values ('$libelle',0,0,date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),1,$annee) ;
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),'debite',$compte_UidId{607000},$r_achat->[7],'$libelle',$subselect,$annee); -- compte achat
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),'debite',$r_achat->[2],$r_achat->[1],'$libelle',$subselect,$annee); -- compte fourn.
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),'credite',$r_achat->[2],$r_achat->[1],'$libelle',$subselect,$annee); -- paie fourn.
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),'credite',$compte_UidId{'512Crédit Mutuel'},$r_achat->[1],'$libelle',$subselect,$annee); -- paie fourn.
	update achat set groupe_ecriture=g.uid from groupe_ecriture g where g.libelle like '%[$r_achat->[6]]%' and achat.uid=$r_achat->[6] ;
EOF
	if ($tva) {  # TVA
		$req.=<<EOF;
		insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),date(timestamp($r_achat->[0])),'debite',$compte_UidId{'445510'},$tva,'$libelle',$subselect,$annee);
EOF

}
	print $req."\n";
}

for my $r_fact (@lfact) {
	# on :
    #  credite le compte vente
    #  credite le compte TVA
    #  debite le compte client	
    # 0 -> date , 1 -> montantht_euro , 2 -> montanttc_euro , 3 -> compte client , 4 -> text , 5 -> float 
	my $req;
	my $tva=$r_fact->[2]-$r_fact->[1];
	my $libelle="FACTURE ".$r_fact->[4]." (Eur:".round($r_fact->[2],2).")";
	my $subselect="(select uid from groupe_ecriture where libelle like 'FACTURE ".$r_fact->[4]." %')";
	my $annee="date_part('year',date(timestamp($r_fact->[0])))";

	$req=<<EOF;
	------------------------
	insert into groupe_ecriture (libelle,authcrea,authmodif,datecrea,datemodif,valide,exercice) values ('$libelle',0,0,date(timestamp($r_fact->[0])),date(timestamp($r_fact->[0])),1,$annee) ;
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_fact->[0])),date(timestamp($r_fact->[0])),date(timestamp($r_fact->[0])),'credite',$compte_UidId{706000},$r_fact->[1],'$libelle',$subselect,$annee);
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_fact->[0])),date(timestamp($r_fact->[0])),date(timestamp($r_fact->[0])),'credite',$compte_UidId{'445TCA'},$tva,'$libelle',$subselect,$annee);
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_fact->[0])),date(timestamp($r_fact->[0])),date(timestamp($r_fact->[0])),'debite',$r_fact->[3],$r_fact->[2],'$libelle',$subselect,$annee);
    update factures set groupe_ecriture=g.uid from groupe_ecriture g where g.libelle like 'FACTURE $r_fact->[4] %' and factures.id='$r_fact->[4]';
EOF
	print $req."\n";
}


my $i=0;
for my $r_charge (@lcharge) {
    #  0 -> date + 1e9 , 1 -> montant_euro , 2 -> compte organisme , 3 -> text , 4 -> float , 5 -> cheque , 6 -> uid
	$i++;
	my $req;
	my $libelle="CHARGE ".$r_charge->[3]." (Eur:".round($r_charge->[1],2).") - [$i]";
	my $subselect="(select uid from groupe_ecriture where libelle like '%[$i]' and libelle like 'CHARGE%')";
	my $annee="date_part('year',date(timestamp($r_charge->[0])))";

	$req=<<EOF;
	------------------------
	insert into groupe_ecriture (libelle,authcrea,authmodif,datecrea,datemodif,valide,exercice) values ('$libelle',0,0,date(timestamp($r_charge->[0])),date(timestamp($r_charge->[0])),1,$annee) ; -- crea groupe
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_charge->[0])),date(timestamp($r_charge->[0])),date(timestamp($r_charge->[0])),'debite',$r_charge->[2],$r_charge->[1],'$libelle',$subselect,$annee); -- compte organisme
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_charge->[0])),date(timestamp($r_charge->[0])),date(timestamp($r_charge->[0])),'credite',$compte_UidId{'512Crédit Mutuel'},$r_charge->[1],'$libelle',$subselect,$annee); -- compte BQ
    update charges set groupe_ecriture=g.uid from groupe_ecriture g where g.libelle like '%[$i]' and g.libelle like 'CHARGE%' and charges.uid=$r_charge->[6];
EOF
	print $req."\n";
}
#	--insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_charge->[0])),date(timestamp($r_charge->[0])),date(timestamp($r_charge->[0])),'debite',$compte_UidId{437000},$r_charge->[1],'$libelle',$subselect,$annee); -- compte charge
#	--insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_charge->[0])),date(timestamp($r_charge->[0])),date(timestamp($r_charge->[0])),'credite',$r_charge->[2],$r_charge->[1],'$libelle',$subselect,$annee); -- compte organisme

$i=0;
for my $r_rglt (@lrglt) {
	# on :
    #  credite le compte client
    #  credite le compte TVA
	#  on debite le compte TCA
    #  on debite le compte BQ	
	# 0 -> date , 1 -> montant_euro , 2 -> compte client , 3 -> Client.nom , 4 -> float 
	my $req;
	$i++;
	my $libelle="REGLEMENT ".$r_rglt->[3]." (Eur:".round($r_rglt->[1],2).") - [$i]";
	my $subselect="(select uid from groupe_ecriture where libelle like '%[$i]%' and libelle like 'REGLEMENT%')";
	my $tva=$r_rglt->[1]*.196; # faux pour les factures émises avant le 4/4/2000
	my $annee="date_part('year',date(timestamp($r_rglt->[0])))";
	$req=<<EOF;
	insert into groupe_ecriture (libelle,authcrea,authmodif,datecrea,datemodif,valide,exercice) values ('$libelle',0,0,date(timestamp($r_rglt->[0])),date(timestamp($r_rglt->[0])),1,$annee) ;
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,exercice,groupe_ecriture) values (0,0,date(timestamp($r_rglt->[0])),date(timestamp($r_rglt->[0])),date(timestamp($r_rglt->[0])),'credite',$r_rglt->[2],$r_rglt->[1],'$libelle',$annee,$subselect);
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,exercice,groupe_ecriture) values (0,0,date(timestamp($r_rglt->[0])),date(timestamp($r_rglt->[0])),date(timestamp($r_rglt->[0])),'debite',$compte_UidId{'512Crédit Mutuel'},$r_rglt->[1],'$libelle',$annee,$subselect);
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_rglt->[0])),date(timestamp($r_rglt->[0])),date(timestamp($r_rglt->[0])),'debite',$compte_UidId{'445TCA'},$tva,'$libelle',$subselect,$annee);
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_rglt->[0])),date(timestamp($r_rglt->[0])),date(timestamp($r_rglt->[0])),'credite',$compte_UidId{'445510'},$tva,'$libelle',$subselect,$annee);
EOF
	print "$req\n";
}


$i=0;
for my $r_sal (@lsalaire) {
#        0 -> date , 1 -> montant_euro , 2 -> intervenant , 3 -> text , 4 -> float , 5 -> comment , 6 -> uid
	$i++;
	my $req;
	my $libelle="SALAIRE ".$r_sal->[3]." - [$i]";
	my $subselect="(select uid from groupe_ecriture where libelle like '%[$i]' and libelle like 'SAL%')";
	my $annee="date_part('year',date(timestamp($r_sal->[0])))";

	$req=<<EOF;
	------------------------
	insert into groupe_ecriture (libelle,authcrea,authmodif,datecrea,datemodif,valide,exercice) values ('$libelle',0,0,date(timestamp($r_sal->[0])),date(timestamp($r_sal->[0])),1,$annee) ; -- crea groupe
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_sal->[0])),date(timestamp($r_sal->[0])),date(timestamp($r_sal->[0])),'debite',$compte_UidId{'641000'},$r_sal->[1],'$libelle',$subselect,$annee); -- compte de salaire
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_sal->[0])),date(timestamp($r_sal->[0])),date(timestamp($r_sal->[0])),'credite',$r_sal->[2],$r_sal->[1],'$libelle',$subselect,$annee); -- compte intervenant
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_sal->[0])),date(timestamp($r_sal->[0])),date(timestamp($r_sal->[0])),'debite',$r_sal->[2],$r_sal->[1],'$libelle',$subselect,$annee); -- compte intervenant
	insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,groupe_ecriture,exercice) values (0,0,date(timestamp($r_sal->[0])),date(timestamp($r_sal->[0])),date(timestamp($r_sal->[0])),'credite',$compte_UidId{'512Crédit Mutuel'},$r_sal->[1],'$libelle',$subselect,$annee); -- compte BQ
    update salaire set groupe_ecriture=g.uid from groupe_ecriture g where g.libelle like '%[$i]' and g.libelle like 'SAL%' and salaire.uid=$r_sal->[6];
EOF
	print $req."\n";
}


# Affichage #
#======================
print STDERR "" . "="x50 ."\n";

close STDOUT if ($output);