예제 #1
0
파일: views.py 프로젝트: alpimarc/Lecteur
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
        })
예제 #2
0
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
예제 #3
0
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
	
	
예제 #4
0
파일: views.py 프로젝트: alpimarc/Lecteur
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
        })
예제 #5
0
파일: views.py 프로젝트: alpimarc/Lecteur
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})
예제 #6
0
파일: views.py 프로젝트: alpimarc/Lecteur
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:]))
예제 #7
0
파일: views.py 프로젝트: alpimarc/Lecteur
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
    })
예제 #8
0
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
예제 #9
0
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
예제 #10
0
파일: views.py 프로젝트: alpimarc/Lecteur
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
    })
예제 #11
0
파일: views.py 프로젝트: alpimarc/Lecteur
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
        })
예제 #12
0
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
예제 #13
0
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)
예제 #14
0
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)