def control(request, id_file, id_test=0):
requete = "SELECT control, comment, result, risque, description, typologie, loi FROM rapport where id='" + id_test + "' AND Result!='NA' AND result!='OK'"
try:
(control, comment, result, risque, description, typologie,
loi) = fonctions.requeteSQL(requete, id_file).fetchone()
except:
return HttpResponse('Control inexistant', status=404)
requete = "SELECT id, type, inverse, titre, requete, nb_lignes FROM tablesgraphiques where id_rapport='" + id_test + "'"
tablesGraphiques = []
for (
id,
type,
inverse,
titre,
requeteGraphique,
nb_lignes,
) in fonctions.requeteSQL(requete, id_file):
nb_pages, _ = determinePageEtLigne(
id_file,
requete,
"",
settings.NB_LIGNES_PAR_PAGE_GRAPHIQUE,
nb_lignes,
inverse=inverse)
tablesGraphiques.append((id, type, titre, nb_pages, inverse))
requete = "SELECT id, tableresultat FROM tablesresultats where id_rapport='" + id_test + "'"
tablesResultats = []
for (
id,
tableResultat,
) in fonctions.requeteSQL(requete, id_file):
entetesTypes = filtreEntetesTypes(
request,
fonctions.getEntetesEtTypeFromTable(tableResultat, id_file))
tablesResultats.append((id, tableResultat, entetesTypes))
return render(
request, 'control.html', {
'id_test': id_test,
'control': control,
'tablesResultats': tablesResultats,
'tablesGraphiques': tablesGraphiques,
'comment': comment,
'result': result,
'risque': risque,
'description': description,
'typologie': typologie,
'loi': loi,
'nb_lignes_par_page': settings.NB_LIGNES_PAR_PAGE_TABLE,
'nb_items_par_page': settings.NB_LIGNES_PAR_PAGE_GRAPHIQUE
})
def ListeTables(id_file):
requete = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
listeTables = fonctions.requeteSQL(requete, id_file).fetchall()
listeTables2 = []
for (table, ) in listeTables:
if not table.endswith('_filtre') and table != "Rapport":
listeTables2.append(table)
return listeTables2
def ListeTables(id_file):
requete="SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
listeTables=fonctions.requeteSQL(requete, id_file).fetchall()
listeTables2=[]
for (table,) in listeTables:
if not table.endswith('_filtre') and table!="Rapport":
listeTables2.append(table)
return listeTables2
def drilldown(request, id_file, id_table, id_ligne):
print(id_ligne)
if "graphique" in request.GET and "colonne" in request.GET:
graphique = request.GET["graphique"]
colonne = request.GET["colonne"]
else:
graphique = 0
colonne = ""
id_table_retour = '0'
if id_table != '0':
requete = "SELECT tableResultat FROM tablesResultats WHERE id=" + id_table
(tableResultat, ) = fonctions.requeteSQL(requete, id_file).fetchone()
entetes = fonctions.requeteSQL(
"Pragma table_info ('" + tableResultat + "')", id_file).fetchall()
if "criteres" in [
entete.lower() for (_, entete, _, _, _, _) in entetes
]:
curseur, entetesTypes = fonctions.requete2liste(
"SELECT id as id, CompteNum, CompteLib, Debit, Credit, Solde FROM 'BalanceGenerale.csv' LIMIT 1",
id_file, False)
(id_table_retour, ) = fonctions.requeteSQL(
"SELECT id FROM tablesResultats WHERE tableResultat='BalanceGenerale.csv'",
id_file).fetchone()
else:
curseur, entetesTypes = fonctions.requete2liste(
"SELECT id, JournalCode, JournalLib, EcritureNum, EcritureDate, CompteNum, CompteLib, CompAuxNum, CompAuxLib, PieceRef, PieceDate, EcritureLib, Debit, Credit, EcritureLet, DateLet, ValidDate, Montantdevise, Idevise, SchemaEcr, SchemaSimplifie, IntituleSchema, TypeSchema, ListCompAuxNum FROM TableAvecSchemas LIMIT 1",
id_file, False)
else:
curseur, entetesTypes = fonctions.requete2liste(
"SELECT id, JournalCode, JournalLib, EcritureNum, EcritureDate, CompteNum, CompteLib, CompAuxNum, CompAuxLib, PieceRef, PieceDate, EcritureLib, Debit, Credit, EcritureLet, DateLet, ValidDate, Montantdevise, Idevise, SchemaEcr, SchemaSimplifie, IntituleSchema, TypeSchema, ListCompAuxNum FROM TableAvecSchemas LIMIT 1",
id_file, False)
entetesTypes = filtreEntetesTypes(request, entetesTypes)
return render(
request, 'drilldown.html', {
'entetesTypes': entetesTypes,
'id_table': id_table,
'id_table_retour': id_table_retour,
'id_ligne': id_ligne,
'nb_lignes_par_page': 50,
"graphique": graphique,
"colonne": colonne
})
def sidebar(request, id_file):
if request.user.is_staff or request.user.is_superuser:
profil = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
else:
profil = Droit.objects.get(file=FILE.objects.get(fichier=id_file),
utilisateur_autorise=request.user).profil
requete = "SELECT Type, control, nb, id FROM rapport, (select SUBSTR(control,1,1) as type, COUNT(1) as nb FROM rapport where result!='NA' AND result!='OK' AND SUBSTR(Control,1,1) IN ('" + "', '".join(
list(profil)
) + "') group by substr(control,1,1)) WHERE type=substr(rapport.control,1,1) and Result!='NA' AND result!='OK'"
listeTables = fonctions.requeteSQL(requete, id_file)
return render(request, 'sidebar.html', {'listeTables': listeTables})
def json(request, id_file, id_test, id_tableResultat):
requete = "SELECT tableresultat, nb FROM tablesresultats where id_rapport='" + id_test + "' AND id='" + id_tableResultat + "'"
(
tableResultat,
nb,
) = fonctions.requeteSQL(requete, id_file).fetchone()
requete = "SELECT * FROM '" + tableResultat + "'"
return sous_json(request,
id_file,
requete,
nb,
url="&" +
"?".join(request.get_full_path().split("?")[1:]))
def rapport(request, id_file):
_, entetesTypes = fonctions.requete2liste("SELECT * FROM Rapport LIMIT 1",
id_file, False)
entetesTypes = filtreEntetesTypes(request, entetesTypes)
if request.user.is_staff or request.user.is_superuser:
profil = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
else:
profil = Droit.objects.get(file=FILE.objects.get(fichier=id_file),
utilisateur_autorise=request.user).profil
requete = "SELECT COUNT(*) FROM Rapport WHERE Result!='NA' AND result!='OK' AND SUBSTR(Control,1,1) IN ('" + "', '".join(
list(profil)) + "')"
(nb_lignes, ) = fonctions.requeteSQL(requete, id_file).fetchone()
return render(request, 'rapport.html', {
'entetesTypes': entetesTypes,
'nb_lignes_par_page': nb_lignes
})
def filtreRequete(request, requete, id_file):
import re
if re.search("^select \* ", requete, flags=re.IGNORECASE):
filtre_utilise = list(settings.FILTRE)
entetes = [
'"' + entete.replace('"', '\"') + '"'
for (_, entete, _, _, _, _) in fonctions.requeteSQL(
"Pragma table_info ('" + fonctions.getNomTable(requete) +
"')", id_file).fetchall()
if entete.lower() not in filtre_utilise
]
return re.sub("^select \* ",
"SELECT " + ", ".join(entetes) + " ",
requete,
flags=re.IGNORECASE)
else:
return requete
def determinePageEtLigne(id_file,
requete,
filtre,
nb_lignes_par_page,
nb_lignes="",
inverse=False):
if filtre != "" or nb_lignes == "":
import re
reg = re.compile('^.*from ', re.IGNORECASE)
requete = "SELECT COUNT(*) FROM " + reg.sub("", requete,
count=1) + filtre
(nb_lignes, ) = fonctions.requeteSQL(requete, id_file).fetchone()
if nb_lignes_par_page:
if int(nb_lignes /
nb_lignes_par_page) == nb_lignes / nb_lignes_par_page:
nb_pages = int(nb_lignes / nb_lignes_par_page)
else:
nb_pages = int(nb_lignes / nb_lignes_par_page) + 1
else:
nb_pages = 1
return nb_pages, nb_lignes
def view_csv(request, id_file, id_tableResultat):
requete = "SELECT tableResultat FROM tablesResultats WHERE id=" + id_tableResultat
(tableResultat, ) = fonctions.requeteSQL(requete, id_file).fetchone()
inverse = True
if not (tableResultat.endswith("AvecEtSansTVAEnNB")
or tableResultat.endswith("AvecEtSansTVAEnMontant")
or tableResultat.endswith("MvtAvecEtSansTVAEnMontant")
or tableResultat.endswith("MvtAvecEtSansTVAEnNB")):
inverse = False
requete = filtreRequete(request, "SELECT * FROM '" + tableResultat + "'",
id_file)
print("r", requete)
filtre, tri, limit, nb_lignes_par_page, _ = recherche(request, requete)
curseur, entetesTypes, entete = fonctions.sql2curseur(
requete + filtre + tri, id_file, inverse)
return fonctions.renderForceDownload('export.csv', {
'curseur': curseur,
'entete': entete
})
def graphique_json(request, id_file, id_test, id_tableGraphique):
requete = "SELECT requete, inverse, nb_lignes FROM tablesGraphiques where id_rapport='" + id_test + "' AND id='" + id_tableGraphique + "'"
import re
(requete, inverse, nb_lignes) = fonctions.requeteSQL(requete,
id_file).fetchone()
requete = re.sub(", ", ", id, ", requete, 1, re.IGNORECASE)
filtre, tri, limit, nb_lignes_par_page, num_page = recherche(
request, requete, graphique=True)
if inverse:
nb_pages, _ = determinePageEtLigne(id_file,
requete,
filtre,
nb_lignes_par_page,
nb_lignes=nb_lignes)
else:
nb_pages, _ = determinePageEtLigne(id_file,
requete,
filtre,
nb_lignes_par_page,
nb_lignes="")
if inverse:
requete = requete + filtre + tri
else:
requete = requete + filtre + tri + limit
curseur, entetesTypes, entetes = fonctions.sql2curseur(requete,
id_file,
inverse,
graphique=True,
limit=limit)
return render(
request, 'graphique_json.html', {
'curseur': curseur,
'entetes': entetes,
'nb_pages': nb_pages,
'num_page': num_page
})
def filtreDrilldown(id_file, id_table, id_ligne, request):
import re
critere = ''
champ_criteres = False
if "graphique" in request.GET and "colonne" in request.GET:
requete = "SELECT filtre_colonne FROM tablesGraphiques where id=" + request.GET[
"graphique"]
(filtre_colonne, ) = fonctions.requeteSQL(requete, id_file).fetchone()
filtre_colonne = eval(filtre_colonne)
if request.GET["colonne"] in filtre_colonne:
critere += "(" + filtre_colonne[request.GET["colonne"]] + ")"
print(request.GET["colonne"], critere)
if id_table == '0':
tableResultat = "TableAvecSchemas"
if critere == "()":
critere == ""
if critere != "":
critere += " AND "
critere += "t1.JournalCode = t2.JournalCode AND t1.EcritureNum = t2.EcritureNum"
criteres = ""
else:
requete = "SELECT tableResultat, criteres FROM tablesResultats WHERE id=" + id_table
(
tableResultat,
criteres,
) = fonctions.requeteSQL(requete, id_file).fetchone()
if criteres == "()":
criteres = ""
if criteres != "":
criteres = " AND " + criteres
listeChampsTableAvecSchemas = [
entete for (_, entete, _, _, _, _) in fonctions.requeteSQL(
"Pragma table_info ('TableAvecSchemas')",
id_file).fetchall()
]
for champ in listeChampsTableAvecSchemas:
criteres = re.sub(champ,
"t1." + champ,
criteres,
flags=re.IGNORECASE)
entetesTypes = fonctions.getEntetesEtTypeFromTable(
tableResultat, id_file)
entetes = []
for i, _ in entetesTypes:
entetes.append(i.lower())
if critere == "()":
critere = ""
if critere != "":
critere += " AND "
if "journalcode" in entetes and "ecriturenum" in entetes:
critere += "t1.JournalCode = t2.JournalCode AND t1.EcritureNum = t2.EcritureNum"
elif "ecriturenum" in entetes:
critere += "t1.EcritureNum = t2.EcritureNum"
elif "comptenum" in entetes and "compauxnum" in entetes:
critere += "t1.CompteNum = t2.CompteNum AND t1.CompAuxNum = t2.CompAuxNum"
elif "comptenum" in entetes and "tiers" in entetes:
critere += "t1.CompteNum = t2.CompteNum AND t1.CompAuxNum LIKE SPLITSQLITE(t2.tiers, ' - ')"
elif "comptenum" in entetes:
critere += "t1.CompteNum = t2.CompteNum"
elif "compauxnum" in entetes and "pieceref" in entetes:
critere += "t1.CompAuxNum = t2.CompAuxNum AND t1.PieceRef = t2.PieceRef"
elif "compauxnum" in entetes:
critere += "t1.CompAuxNum = t2.CompAuxNum"
elif "tiers" in entetes:
critere += "t1.CompAuxNum LIKE SPLITSQLITE(t2.tiers, ' - ')"
elif "compte" in entetes:
critere += "t1.CompteNum LIKE SPLITSQLITE(t2.Compte, ' ')"
elif "journalcode" in entetes:
critere += "t1.JournalCode = t2.JournalCode"
elif "numero d ecriture precedent le trou de sequence" in entetes and "numero d ecriture suivant le trou de sequence" in entetes and "code Journal precedent" in entetes and "code Journal suivant" in entetes:
critere += "((t1.JournalCode = t2.[Code Journal precedent] AND t1.EcritureNum = t2.[Numero d ecriture precedent le trou de sequence]) OR (t1.JournalCode = t2.[Code Journal suivant] AND t1.EcritureNum = t2.[Numero d ecriture suivant le trou de sequence]))"
elif "numero d ecriture precedent le trou de sequence" in entetes and "numero d ecriture suivant le trou de sequence" in entetes:
critere += "(t1.EcritureNum = t2.[Numero d ecriture precedent le trou de sequence] OR t1.EcritureNum = t2.[Numero d ecriture suivant le trou de sequence])"
elif "criteres" in entetes:
requete = "SELECT criteres FROM '" + tableResultat + "' WHERE id=" + id_ligne
(crit, ) = fonctions.requeteSQL(requete, id_file).fetchone()
if filtre_colonne[request.GET["colonne"]] == "":
critere += "(" + crit + ")"
champ_criteres = True
requete = "SELECT t1.id, t1.JournalCode, t1.JournalLib, t1.EcritureNum, t1.EcritureDate, t1.CompteNum, t1.CompteLib, t1.CompAuxNum as compauxnum, t1.CompAuxLib as compauxlib, t1.PieceRef, t1.PieceDate, t1.EcritureLib, t1.Debit as debit, t1.Credit as credit, t1.EcritureLet, t1.DateLet, t1.ValidDate, t1.Montantdevise as montantdevise, t1.Idevise as idevise, t1.SchemaEcr as schemaecr, t1.SchemaSimplifie as schemasimplifie, t1.IntituleSchema as intituleschema, t1.TypeSchema as typeschema , t1.ListCompAuxNum as listcompauxnum FROM TableAvecSchemas t1"
if id_ligne != '0':
if champ_criteres:
requete = requete.replace("t1", "").replace(
".", "") + " WHERE (" + critere + ") "
requete = "SELECT id as id, CompteNum as comptenum, CompteLib as comptelib, Debit as debit, Credit as credit, Solde as solde FROM 'BalanceGenerale.csv' WHERE (" + critere + ") "
else:
requete = requete + ", '{}' t2 WHERE t2.{} = {}".format(
tableResultat, "id",
id_ligne) + " AND (" + critere + ")" + criteres
print("requete", requete, criteres)
return requete
def ListeTables2(id_file):
requete = "SELECT Type, control, nb, id FROM rapport, (select SUBSTR(control,1,1) as type, COUNT(1) as nb FROM rapport where result!='NA' AND result!='OK' group by substr(control,1,1)) WHERE type=substr(rapport.control,1,1) and Result!='NA' AND result!='OK'"
return fonctions.requeteSQL(requete, id_file)
def ListeTables2(id_file): requete="SELECT Type, control, nb, id FROM rapport, (select SUBSTR(control,1,1) as type, COUNT(1) as nb FROM rapport where result!='NA' AND result!='OK' group by substr(control,1,1)) WHERE type=substr(rapport.control,1,1) and Result!='NA' AND result!='OK'" return fonctions.requeteSQL(requete, id_file)