#!/usr/bin/perl -w

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

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

sub usage {
    print "
Impacte les comptes TCA et TVA lors de la migration.
usage:\n
migRglt4tva.pl [-o <output file>] -h DBHOST -u DBUSER -p DBPWD -n DBNAME
";
} 

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 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 + 1e9 , 1 -> montant_euro , 2 -> client , 3 -> text , 4 -> float 
$sql=" 
set datestyle to 'SQL,EURO';
SELECT 1000000000 + date_part('epoch',datepaye),-sum(montanttc*devise.nbeuros),fournisseur.uid,'achat',0 from achat,fournisseur,devise where devise.uid=devise and fournisseur.uid=fournisseur group by fournisseur.uid,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 , 1 -> montanttc_euro , 2 -> client , 3 -> text , 4 -> float , 5 -> no chèque , 6 -> uid , 7 -> montantht ,8 -> abstract
$sql=" 
set datestyle to 'SQL,EURO';
SELECT datepaye,montanttc*devise.nbeuros,fournisseur.uid,'achat',0,nocheque,achat.uid,montantht,abstract from achat,fournisseur,devise where devise.uid=devise and fournisseur.uid=fournisseur and montanttc>0 and paye=1 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 -> organisme , 3 -> text , 4 -> float , 5 -> cheque , 6 -> uid
$sql=" 
set datestyle to 'SQL,EURO';
SELECT 1000000000 + date_part('epoch',timestamp(datepaye)),montant*devise.nbeuros,organisme.uid,'charges',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);
}

# Recup des factures #
#======================#
#        0 -> date + 1e9 , 1 -> montantht_euro , 2 -> montantht_euro , 3 -> compte client , 4 -> text , 5 -> float 
$sql=" 
set datestyle to 'SQL,EURO';
SELECT dateemission,montantht*devise.nbeuros,montanttc*devise.nbeuros,(select compte from client,compte where compte.uid=client.compte and client.uid=client) as compte_client,id,0 from factures,devise where 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 charges sociales #
#======================#
#        0 -> date + 1e9 , 1 -> montant_euro , 2 -> organisme , 3 -> text , 4 -> float , 5 -> cheque , 6 -> uid
$sql=" 
set datestyle to 'SQL,EURO';
SELECT 1000000000 + date_part('epoch',timestamp(datepaye)),montant*devise.nbeuros,organisme.uid,'ch_sociales',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 (@lchargesoc,\@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 ##
@lsalaire= sort {$::a->[0] <=> $::b->[0]} @lsalaire;
@lcharge= sort {$::a->[0] <=> $::b->[0]} @lcharge;
@lchargesoc= sort {$::a->[0] <=> $::b->[0]} @lchargesoc;




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

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

for my $r_rglt (@lrglt) {
	# 0 -> date , 1 -> montant_euro , 2 -> compte client , 3 -> text , 4 -> float 
	my $req;
	# on debite la BQ, on credite le Client, on credite TVA, on debite TCA
	my $compteBQ=$compte_UidId{"512Crédit Mutuel"};
	my $compteClient=$r_rglt->[2];
	my $compteTCA=$compte_UidId{"445TCA"};
	my $compteTVA=$compte_UidId{"445510"};
	$req.="insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,exercice) values (0,0,'".$r_rglt->[0]."','".$r_rglt->[0]."','".$r_rglt->[0]."','credite',".$compteTVA.",(select ".$r_rglt->[1]."*tva('".$r_rglt->[0]."')/(1+tva('".$r_rglt->[0]."'))),'REGLEMENT ".$r_rglt->[3]."',date_part('year',date('".$r_rglt->[0]."')));\n";
	$req.="insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,exercice) values (0,0,'".$r_rglt->[0]."','".$r_rglt->[0]."','".$r_rglt->[0]."','debite',".$compteTCA.",(select ".$r_rglt->[1]."*tva('".$r_rglt->[0]."')/(1+tva('".$r_rglt->[0]."'))),'REGLEMENT ".$r_rglt->[3]."',date_part('year',date('".$r_rglt->[0]."')));";
	$req.="insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,exercice) values (0,0,'".$r_rglt->[0]."','".$r_rglt->[0]."','".$r_rglt->[0]."','credite',".$compteClient.",(select ".$r_rglt->[1]."*tva('".$r_rglt->[0]."')/(1+tva('".$r_rglt->[0]."'))),'REGLEMENT ".$r_rglt->[3]."',date_part('year',date('".$r_rglt->[0]."')));\n";
	$req.="insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,exercice) values (0,0,'".$r_rglt->[0]."','".$r_rglt->[0]."','".$r_rglt->[0]."','debite',".$compteBQ.",(select ".$r_rglt->[1]."*tva('".$r_rglt->[0]."')/(1+tva('".$r_rglt->[0]."'))),'REGLEMENT ".$r_rglt->[3]."',date_part('year',date('".$r_rglt->[0]."')));";
	print "$req\n";
}

for my $r_achat (@lachat) {
    #   0 -> date , 1 -> montanttc_euro , 2 -> client , 3 -> text , 4 -> float , 5 -> no chèque , 6 -> uid , 7 -> montantht , 8 -> abstract
	my $req;
	my $compteTCA=$compte_UidId{"445TCA"};
	my $compteTVA=$compte_UidId{"445510"};
	my $montantTVA=$r_achat->[1]-$r_achat->[7];
	if ($montantTVA>0) {
		$r_achat->[8]=~s/'/''/g; #'
		$req.="insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,exercice) values (0,0,'".$r_achat->[0]."','".$r_achat->[0]."','".$r_achat->[0]."','credite',".$compteTCA.",$montantTVA,'ACHAT ".$r_achat->[8]."',date_part('year',date('".$r_achat->[0]."')));\n";
		$req.="insert into ecriture (authcrea,authmodif,datecrea,datemodif,date,action,compte,valeur,intitule,exercice) values (0,0,'".$r_achat->[0]."','".$r_achat->[0]."','".$r_achat->[0]."','debite',".$compteTVA.",$montantTVA,'ACHAT ".$r_achat->[8]."',date_part('year',date('".$r_achat->[0]."')));";
		print "$req\n";
	}
}


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


#======================#=============================================#======================#
#======================#               Génération SQL                #======================#
#======================#=============================================#======================#


close STDOUT if ($output);