#!/usr/bin/perl -w

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

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

sub usage {
    print "usage:\n
makeRapp.pl [-o <output file>] -h DBHOST -u USER -p PWD -n DBNAME 
genère la liste des rapprochements obtenus par 3 méthodes successives, et le SQL associé ;
renvoie les stats sur STDERR et les SQL sur STDIN si -o n'est pas spécifié ;
l'option -c tente un calcul sans certaines contraintes de dates (hasardeux).

";
} 

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 @lecriture;
my @lcompte;
my %lcl_compte;
my @lsalaire;
my @lcharge;
my @lchargesoc;
my @lmvt;  # mvt positifs
my @lmvtn; # mvt négatifs
my %lclient;
my %client=();
my $result;
my @merge=();
my $debug=0;

%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{d}) {
	$debug=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");


# 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 (@lcompte,\@row);
	#print "je charge en memoire : compte_IdUid{".$row[0]."}=".$row[1]."\n";
	$compte_IdUid{$row[1]}=$row[0];
	$compte_UidId{$row[0]}=$row[2];
}


# Recup des pimengest/fournisseur/intervenant/organisme (map pour affichage) #
#========================================#
$sql=" 
set datestyle to 'SQL,EURO';
SELECT uid,nom,compte from client union select uid,nom,compte from fournisseur union select uid,nom,compte from intervenant union select uid,nom,compte from organisme;";

$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) {
	#print join($sep,@row)."\n";
	$lclient{$row[0]}=$row[1];
	#$lcl_compte{$row[2]}=$row[1];
}

# Recup des mouvements positifs #
#-------------------------------#
#        0 -> date + 1e9 , 1 -> montant_euro , 2 -> uid , 3 -> text , 4 -> float , 5 -> libelle
$sql="select 1000000000 + date_part('epoch',date),montant_euro,uid,'MVT',0,libelle from mouvement_banque where uid not in (select mouvement_banque from rapprochement) order by date ASC;";
# $sql="select 1000000000 + date_part('epoch',date),montant_euro,uid,'MVT',0,libelle from mouvement_banque where montant_euro>0 order by date 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) {
	#print join($sep,@row)."\n";
	push (@lmvt,\@row);
}


# Recup des saisies bancaires PimenGest #
#---------------------------------------#
#        0 -> date , 1 -> montant_euro , 2 -> action , 3 -> compte , 4 -> float , 5 -> libelle , 6->uid
$sql="set Datestyle to US;select 1000000000+date_part('epoch',date),valeur,action,compte,0,intitule,uid from ecriture where compte=".$compte_IdUid{'512Crédit Mutuel'}." and uid not in (select ecriture from rapprochement) and date > '01/01/2001' order by date ASC;";
#$sql="select 1000000000+date_part('epoch',date),valeur,action,compte,0,intitule,uid from ecriture where action='debite' and compte=".$compte_IdUid{'512Crédit Mutuel'}." order by date 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) {
	#print join(' ',@row)."\n";
	if ($row[2] eq 'credite' && $row[1]<0) {
		$row[1]=-$row[1] ;
		$row[2]="debite";
	}
	push (@lecriture,\@row);
}
#=================================================================================#







#========================================#
## Calcul des rapprochements des entrées #
#========================================#
my $i=0;
my %rapp=();
my %suspect=();


print STDERR " Look for negatives... 1st Method\n" if ($debug);
#========================================#
## Calcul des rapprochements des achats  #
#========================================#

# Methode no 2 : 
# pour chaque ecriture, on teste le montant
# 
my $nbjour=55;
PR:for my $r_mvt (@lmvt) {
	next if $r_mvt->[4]==1 || $r_mvt->[1]>0;
	for my $r_ach (@lecriture) {
		next if $r_ach->[4]==1 || abs($r_mvt->[0]-$r_ach->[0])>60*60*24*$nbjour;
		next if $r_ach->[2] eq 'debite';
		#print STDERR "teste ".round($r_mvt->[1],2)."=".round($r_ach->[1],2)."\n" if ($debug);
		if (round($r_mvt->[1],2)==-round($r_ach->[1],2) && abs($r_mvt->[0]-$r_ach->[0])<60*60*24*$nbjour) {
			$r_mvt->[4]=1;
			$r_ach->[4]=1;
			my @tmpArr;
			push @tmpArr,$r_mvt;
			push @tmpArr,$r_ach;
			$rapp{++$i}=\@tmpArr;
			next PR;
		}
		else { # =====Recherche suspicion============
			if (abs($r_mvt->[1]/$r_ach->[1] -1)<1e-5) {
				print STDERR "\n\t\t ======SUSPICION SIMPLE======\n" if ($debug);
				print STDERR "\t\t pour le montant ".round($r_mvt->[1],2)."\n" if ($debug);
				$r_mvt->[4]=1;
				$r_ach->[4]=1;
				my @tmpArr;
				push @tmpArr,$r_mvt;
				push @tmpArr,$r_ach;
				$rapp{++$i}=\@tmpArr;
				$i++;
				$suspect{$i}=\@tmpArr;
			}  # ====================================
		}
	}
}


#------------- POSITIVES -------------#

# Combinig sums... (down only)

# Methode no 1 : 
# pour chaque rglt, on teste sa somme avec tous les autres en descendant par date
# 
print STDERR " Combinig sums... (down only) 1st Method\n" if ($debug);
for my $r_mvt (@lmvt) {
	next if $r_mvt->[4]==1 || $r_mvt->[1]<0;
	my $success=0;
	my $montantCherche=$r_mvt->[1];
	my $start_idx=0;
	print STDERR "\n==================\nMontant : $montantCherche" if ($debug);

	while (!$success && $start_idx<$#lecriture) {
	    my @tmpEcriture;
		my $sum=0;
		for my $idx ($start_idx .. $#lecriture) {
			# On jette si trop vieux...
			my $timeStop=1e9+timelocal(0,0,0,1,1,2000);
			#print STDERR "Trying...";
			next if $lecriture[$idx]->[2] eq 'credite'; 
			next if ($lecriture[$idx]->[0]<$timeStop && !$complete);
			# On jette si deja utilisé...
			#print STDERR "first pass....ok.";
			next if ($lecriture[$idx]->[4]==1 || $lecriture[$idx]->[0]>$r_mvt->[0]);
			#print STDERR "ok.";
			print STDERR "\t ---- starting at $start_idx (".$lecriture[$idx]->[1].")\n" if ($debug);

			$sum+=$lecriture[$idx]->[1];
			push (@tmpEcriture,$lecriture[$idx]);

			if (round($sum,2)==round($montantCherche,2)) {
				print STDERR " \n\t\t SUCCESS" if ($debug);
				$success=1;
				last;
			}
			
			if (round($sum,2)>round($montantCherche,2)) {
				$success=0;
				last;
			}
			print STDERR "." if ($debug);
		}

		if ($success) {
			unshift @tmpEcriture,$r_mvt;
			foreach (@tmpEcriture) { $_->[4]=1; }
			$i++;
			$rapp{$i}=\@tmpEcriture;
		} 
		# On passe à l'élément suivant de la liste :
		$start_idx++;
	}
	if (!$success) { 
		print STDERR "\n\n\t\t FAILED \n\n" if ($debug);
	}
}


# Methode no 2 :
# On fixe un ecriture, et on teste la somme avec les suivants 
# 
print STDERR "\n". "="x30  ." Combinig sums... 2nd method\n" if ($debug);
if (1) {
PR:for my $r_mvt (@lmvt) {
    next if $r_mvt->[4]==1 || $r_mvt->[1]<0;
    my $success=0;
    my $montantCherche=$r_mvt->[1];
    print STDERR "\n==================\nMontant : $montantCherche" if ($debug);
    
    IDX1:for my $start_idx (0 .. $#lecriture) {
		my @tmpEcriture;
		my $sum=0;
		my $suspect=0;
		my $start_ecriture=$lecriture[$start_idx];
		my $second_start=$start_idx+1;
		my $sous_idx=$second_start;
		# On jette si trop grand...
		next if ($start_ecriture->[1]>$montantCherche);
		# On jette si deja utilisé...
		next if ($start_ecriture->[4]==1);
		# On jette si trop recent...
		next if ($start_ecriture->[0]>$r_mvt->[0]);
		next if $lecriture[$start_idx]->[2] eq 'credite'; 
		
		push @tmpEcriture,$start_ecriture;
		$sum=$start_ecriture->[1];
		
		#print STDERR "\t ---- starting at $start_idx (".$lecriture[$start_idx]->[1].")\n" if ($debug);
		
		IDX2:while ($sum<$montantCherche && $sous_idx<$#lecriture) {

			# On jette si trop vieux...
			my $timeStop=1e9+timelocal(0,0,0,1,2,2000);
			if ($lecriture[$sous_idx]->[0]<$timeStop && !$complete) { 
				$sous_idx++;
				#print STDERR "Trop vieux...\n" if ($debug);
				next;
			}
			# On jette si deja utilisé, si trop vieux, etc...
			if (
				$lecriture[$sous_idx]->[4]==1 
				|| $lecriture[$sous_idx]->[0]>$r_mvt->[0] 
				|| (($r_mvt->[0]-$lecriture[$sous_idx]->[0])>60*60*24*50 && !$complete)
				) { 
				$sous_idx++;
				#print STDERR "Pas cool...".$lecriture[$sous_idx]->[0]." ".$r_mvt->[0]."\n" if ($debug);
				next;}

			if ($lecriture[$sous_idx]->[1] < 0) {
				$sous_idx++;
				next;
			} 

			if ($debug) {
				if ($lecriture[$sous_idx]->[1]==0) {
					print "Hum : montant à 0 pour ecriture ".$lecriture[$sous_idx]->[5]."<br>\n";
				}
			}
			$sum+=$lecriture[$sous_idx]->[1];
			push (@tmpEcriture,$lecriture[$sous_idx]);
			#print STDERR "($start_idx $sous_idx)  + ".round($lecriture[$sous_idx]->[1],2)." = $sum\n" if ($debug);
			
			if (round($sum,2)==round($montantCherche,2)) {
				print STDERR " \n\n\t\t SUCCESS \n\n" if ($debug);
				$success=1;
				last;
			}
			
			# =====Recherche suspicion============
			if ($sum != 0 ) {
				if (abs($montantCherche/$sum -1)<1e-5) {
					print STDERR "\n\t\t ======SUSPICION SIMPLE======\n" if ($debug);
					print STDERR "\t\t pour le montant ".round($montantCherche,2)."\n" if ($debug);
					my @tmpSusp;
					@tmpSusp=@tmpEcriture;
					foreach (@tmpSusp) { $_->[4]=2; }
					unshift @tmpSusp,$r_mvt;
					$i++;
					$suspect{$i}=\@tmpSusp;
				}
			}
			else {
				print STDERR "\nHum... une somme à 0 ??<br>";
				print STDERR "Dernière ecriture ".$lecriture[$sous_idx]->[5]." à ".$lecriture[$sous_idx]->[1]."<br>\n";
			}
			# ====================================
			
			if (round($sum,2)>round($montantCherche,2)) {
				$success=0;
				$second_start++;
				$sous_idx=$second_start;
				$sum=$start_ecriture->[1];
				@tmpEcriture=($start_ecriture);
				next;
			}
			$sous_idx++;
			print STDERR "." if ($debug);
		} # End of while -> starting with the next index for start_idx

		if ($success) {
			unshift @tmpEcriture,$r_mvt;
			foreach (@tmpEcriture) { $_->[4]=1; }
			$i++;
			$rapp{$i}=\@tmpEcriture;
			next PR;
		}
		# On passe à l'élément suivant de la liste : next start_idx
    }
	print "\n";
	if (!$success) { 
		print STDERR "\n\n\t\t FAILED \n\n" if ($debug);
	}
}

}


# Affichage #
#======================
print STDERR "" . "="x50 ."\n<br>";
# succes :
for $i (keys(%rapp)) {
	my @lref=@{$rapp{$i}};
	my $date=strftime("%d/%m/%Y",localtime($lref[0]->[0]-1000000000));
	my $m=round($lref[0]->[1],2);
	print STDERR <<EOF;
<br>	____________________________________
<br>	Mouvement du $date de $m ¤ constitué de :
EOF
	foreach my $idxEcriture (1 .. $#lref) {
		# SQL:
		print "insert into rapprochement (mouvement_banque,ecriture) values (".$lref[0]->[2].",".$lref[$idxEcriture]->[6].");\n";
		#print STDERR join(' -- ',@{$lref[$idxEcriture]});
		my $date=strftime("%d/%m/%Y",localtime($lref[$idxEcriture]->[0]-1000000000));
		print STDERR "<br>\t\t Ecriture ";
		print STDERR " ".$lref[$idxEcriture]->[5];
		print STDERR " du $date de ";
		print STDERR round($lref[$idxEcriture]->[1],2)." ¤<br>";
	}
}

# suspicions :
print STDERR "\n___________________\n Suspicions :\n";
for $i (keys(%suspect)) {
	my @lref=@{$suspect{$i}};
	my $date=strftime("%d/%m/%Y",localtime($lref[0]->[0]-1000000000));
	print STDERR <<EOF;
	____________________________________
	Mouvement du $date de $lref[0]->[1] ¤ [ $lref[0]->[5] ] constitué de :
EOF
	foreach my $idxEcriture (1 .. $#lref) {
		my $date=strftime("%d/%m/%Y",localtime($lref[$idxEcriture]->[0]-1000000000));
		print STDERR "\t\t ".$lref[$idxEcriture]->[5]."  du $date de ".round($lref[$idxEcriture]->[1],2)." ¤\n";
		# les ecritures sont groupés par date-client : 
	}
}

# echecs :
print STDERR "<hr>\n\n====Echecs===================\n<table>";
for my $r_mvt (@lmvt) {
	next if $r_mvt->[4]!=0;
	print STDERR "<tr><td>Echec pour mvt </td><td> du ".strftime("%d %m %Y",localtime($r_mvt->[0]-1000000000))."</td><td>".$r_mvt->[5]." </td><td align=right>&nbsp;".sprintf("%.2f",round($r_mvt->[1],2))." ¤ </td><td>(soit ".round($r_mvt->[1]*6.55957,2)." FRF)</td></tr>\n";
}
print STDERR "</table>";

print STDERR "<hr>\n\n=============================\n<table>";
for my $r_ecriture (@lecriture) {
	next if $r_ecriture->[4]!=0;
	my ($jour,$mois,$annee)=split(' ',strftime("%d %m %Y",localtime($r_ecriture->[0]-1000000000)));
	my $timeStop=timelocal(0,0,0,1,2,2001)+1000000000;
	next if ($r_ecriture->[0]<$timeStop);
	print STDERR "<tr><td>Echec pour ecriture </td><td>du $jour/$mois/$annee de </td><td>".substr(" "x40 . $r_ecriture->[5]." ",-60)."</td><td align=right>&nbsp;".sprintf("%.2f",round($r_ecriture->[1],2))." ¤ </td><td>(soit ".substr(" "x15 .round($r_ecriture->[1]*6.55957,2)." ",-12)." FRF)</td></tr>\n";
}
print STDERR "</table>";

close STDOUT if ($output);