From 918f150099bd27adbae3dab6a208e6a188cdb514 Mon Sep 17 00:00:00 2001
From: Vincent Le Gallic <legallic@crans.org>
Date: Mon, 30 Sep 2013 14:46:20 +0200
Subject: [PATCH] =?utf8?q?[bde/integrite]=20Des=20vieux=20scripts=20de=20v?=
 =?utf8?q?=C3=A9rification=20et=20de=20r=C3=A9tablissement=20de=20coh?=
 =?utf8?q?=C3=A9rence=20de=20la=20BDD?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 .gitignore                       |  1 +
 bde/integrite/fix_integrity.py   | 51 ++++++++++++++++++++++++++++++++
 bde/integrite/integrite_solde.py | 26 ++++++++++++++++
 bde/integrite/memo               |  7 +++++
 4 files changed, 85 insertions(+)
 create mode 100755 bde/integrite/fix_integrity.py
 create mode 100755 bde/integrite/integrite_solde.py
 create mode 100644 bde/integrite/memo

diff --git a/.gitignore b/.gitignore
index d4cb5f7..da66d2e 100644
--- a/.gitignore
+++ b/.gitignore
@@ -19,3 +19,4 @@ newsbot/dicoread.txt
 ssh/plouf.json
 color_output
 test_color_output
+bde/integrite/*.sql
diff --git a/bde/integrite/fix_integrity.py b/bde/integrite/fix_integrity.py
new file mode 100755
index 0000000..9324600
--- /dev/null
+++ b/bde/integrite/fix_integrity.py
@@ -0,0 +1,51 @@
+#!/usr/bin/python
+# -*- coding:utf8 -*-
+
+""" Codé pour rétablir l'intégrité de la BDD.
+    
+    Pour générer des transactions dans le passé
+    pour les gens qui n'avaient pas commencé avec un solde nul.
+"""
+
+import psycopg2, psycopg2.extras
+
+con = psycopg2.connect(database="bde", connection_factory=psycopg2.extras.DictConnection)
+cur = con.cursor()
+
+from commands import getstatusoutput as ex
+import os
+import sys
+
+req="""SELECT numcbde,pseudo,gagne-perdu as calcul,effectif,effectif-(gagne-perdu) as a_enlever 
+FROM
+  (SELECT numcbde,pseudo,
+    (SELECT COALESCE(sum(montant*quantite), 0)
+     FROM transactions
+     WHERE destinataire=main.numcbde AND valide='t') as gagne,
+    (SELECT COALESCE(sum(montant*quantite), 0)
+     FROM transactions
+     WHERE emetteur=main.numcbde AND valide='t') as perdu,
+    (SELECT solde
+     FROM adherents WHERE numcbde=main.numcbde) as effectif
+   FROM adherents as main)
+AS calculs
+WHERE gagne-perdu!=effectif AND numcbde>0 ORDER BY numcbde;"""
+
+cur.execute(req)
+
+l = cur.fetchall()
+
+f=open("request_fixing.sql", "w")
+f.write("ALTER TABLE transactions DISABLE TRIGGER ALL;\n")
+for bug in l:
+    to_credit = bug["effectif"] - bug["calcul"]
+    if to_credit > 0:
+        emet, dest = 0, bug["numcbde"]
+    elif to_credit < 0:
+        emet, dest = bug["numcbde"], 0
+    f.write("INSERT INTO transactions (conso, emetteur, destinataire, montant, quantite, remarque) VALUES (1, %s, %s, %s, 1, 'Solde initial');\n" % (emet, dest, abs(to_credit)))
+    # En fait à cause des solde négatifs, il faut DISABLE les triggers, donc on n'a pas besoin d'update après
+    #f.write("UPDATE adherents SET solde=solde - (%s) WHERE numcbde=%s;\n" % (to_credit, bug["numcbde"]))
+
+f.write("ALTER TABLE transactions ENABLE TRIGGER ALL;\n")
+f.close()
diff --git a/bde/integrite/integrite_solde.py b/bde/integrite/integrite_solde.py
new file mode 100755
index 0000000..fa49ac3
--- /dev/null
+++ b/bde/integrite/integrite_solde.py
@@ -0,0 +1,26 @@
+#!/usr/bin/env python
+# -*- coding:Latin-1 -*-
+
+""" Liste les adhérents, fait la somme de leurs transactions et regarde la différence avec le solde actuel.
+    Broken, apparemment."""
+
+
+import os
+
+l=os.popen("psql bde -c \"SELECT numcbde,gagne-perdu as calcul,effectif,effectif-(gagne-perdu) as a_enlever FROM (SELECT numcbde,(SELECT sum(montant*quantite) FROM transactions WHERE destinataire=main.numcbde AND valide='t') as gagne,(SELECT sum(montant*quantite) FROM transactions WHERE emetteur=main.numcbde AND valide='t') as perdu, (SELECT solde FROM adherents WHERE numcbde=main.numcbde) as effectif FROM adherents as main) AS calculs WHERE gagne-perdu!=effectif;\"").readlines()
+
+g=[]
+print l
+for i in l[1:-3]:
+  i=i.strip().split()
+  print i
+  g.append([i[0],i[2],i[4],i[6]])
+
+f=open("requêtes à effectuer.txt","w")
+for i in g:
+  montant = eval(i[3])
+  if montant>0:
+    f.write("INSERT INTO transactions (date,conso,emetteur,destinataire,montant,quantite,remarque) VALUES ('1970-01-01 00:13:37',1,0,%s,%s,1,'Solde initial');\n"%(i[0],i[3]))
+  elif montant<0:
+    f.write("INSERT INTO transactions (date,conso,emetteur,destinataire,montant,quantite,remarque) VALUES ('1970-01-01 00:13:37',1,%s,0,-(%s),1,'Solde initial');\n"%(i[0],i[3]))
+  f.write("UPDATE adherents SET solde = solde - (%s)  WHERE numcbde=%s;\n"%(i[3],i[0]))
diff --git a/bde/integrite/memo b/bde/integrite/memo
new file mode 100644
index 0000000..f7519c1
--- /dev/null
+++ b/bde/integrite/memo
@@ -0,0 +1,7 @@
+# pour créer la table numcbde,gagne,perdu,effectif :
+
+SELECT numcbde,(SELECT sum(montant*quantite) FROM transactions WHERE destinataire=main.numcbde AND valide='t') as gagne,(SELECT sum(montant*quantite) FROM transactions WHERE emetteur=main.numcbde AND valide='t') as perdu, (SELECT solde FROM adherents WHERE numcbde=main.numcbde) as effectif FROM adherents as main;
+
+# et créer la table + en extraire seulement les gens intéressants :
+# NB : c'est long à exécuter...
+SELECT numcbde,gagne-perdu,effectif FROM (SELECT numcbde,(SELECT sum(montant*quantite) FROM transactions WHERE destinataire=main.numcbde AND valide='t') as gagne,(SELECT sum(montant*quantite) FROM transactions WHERE emetteur=main.numcbde AND valide='t') as perdu, (SELECT solde FROM adherents WHERE numcbde=main.numcbde) as effectif FROM adherents as main) AS calculs WHERE gagne-perdu!=effectif;
-- 
2.39.2