346 lines
16 KiB
C#
346 lines
16 KiB
C#
using System.Data.OleDb;
|
|
using System.Text;
|
|
using System.Text.Json;
|
|
using Omika.Data.HfSql.Connection;
|
|
|
|
namespace Omika.Tests.Connection;
|
|
|
|
/// <summary>
|
|
/// Sonde RUNTIME (lecture seule) : connecte à OMIKA_TEST via OLE DB HFSQL,
|
|
/// inspecte le schéma réel des tables utilisées par
|
|
/// <see cref="Omika.Data.Repositories.Omika.OmikaRapportChampRepository"/> puis
|
|
/// exécute les 8 requêtes de pré-remplissage + les validations de jointures RDV/CR.
|
|
/// N'écrit jamais dans la base. Les résultats sont écrits dans un fichier du
|
|
/// workspace (artifacts/runtime-rapportchamp.txt) pour analyse.
|
|
/// </summary>
|
|
public class OmikaRapportChampRuntimeTests
|
|
{
|
|
private const string ConfigPath = @"D:\Avalonia\Omika\config\hfsql-test.local.json";
|
|
private const string OutputPath = @"D:\Avalonia\Omika\artifacts\runtime-rapportchamp.txt";
|
|
|
|
[Fact]
|
|
public async Task Runtime_Validate_RapportChamp_Queries()
|
|
{
|
|
var sb = new StringBuilder();
|
|
sb.AppendLine("=== OMIKA RAPPORT CHAMP — RUNTIME PROBE (read-only) ===");
|
|
|
|
var config = LoadConfig();
|
|
if (config is null)
|
|
{
|
|
sb.AppendLine("CONFIG: MISSING (skip)");
|
|
await File.WriteAllTextAsync(OutputPath, sb.ToString());
|
|
return;
|
|
}
|
|
|
|
var options = new HfSqlConnectionOptions
|
|
{
|
|
Server = config.Server,
|
|
Port = config.Port,
|
|
Database = config.Omika.Database,
|
|
User = config.Omika.User,
|
|
Credential = config.Omika.Credential,
|
|
FilePasswords = new HfSqlFilePasswordOptions
|
|
{
|
|
ProtectionStd = config.FilePasswords.ProtectionStd,
|
|
ProtectionPlus = config.FilePasswords.ProtectionPlus
|
|
}
|
|
};
|
|
|
|
var factory = new HfSqlOleDbConnectionFactory();
|
|
using var connection = (OleDbConnection)factory.Create(options);
|
|
try
|
|
{
|
|
connection.Open();
|
|
sb.AppendLine($"CONNECTION: OPEN (DB={config.Omika.Database})");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sb.AppendLine($"CONNECTION FAILED: {Redact(ex.Message)}");
|
|
await File.WriteAllTextAsync(OutputPath, sb.ToString());
|
|
return;
|
|
}
|
|
|
|
// 1. Schéma réel des tables clés.
|
|
string[] keyTables =
|
|
[
|
|
"Intervention", "Dossier", "Client", "Adresse", "Lieu", "Societe",
|
|
"Collaborateur", "ParametreMetier", "RDV_Planning", "RDV_Collaborateur",
|
|
"CompteRendu", "Personne", "Intervention_Personne", "Document",
|
|
"RubriquesDocument", "MailModele", "ModeleRapport", "ModeleRapportMob"
|
|
];
|
|
foreach (string table in keyTables)
|
|
{
|
|
sb.AppendLine();
|
|
sb.AppendLine($"── COLUMNS: {table} ──");
|
|
try
|
|
{
|
|
var cols = connection.GetSchema("Columns", [null, null, table, null]);
|
|
foreach (System.Data.DataRow row in cols.Rows)
|
|
{
|
|
string col = row["COLUMN_NAME"]?.ToString() ?? "?";
|
|
string type = row["TYPE_NAME"]?.ToString() ?? "?";
|
|
sb.AppendLine($" {col,-28} {type}");
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sb.AppendLine($" ERROR: {Redact(ex.Message)}");
|
|
}
|
|
}
|
|
|
|
// 2. Découverte d'une intervention réelle.
|
|
long interventionId = 0;
|
|
sb.AppendLine();
|
|
sb.AppendLine("── DISCOVER INTERVENTION ──");
|
|
try
|
|
{
|
|
using var cmd = connection.CreateCommand();
|
|
cmd.CommandText = "SELECT IN_PKID, IN_RefInterne, IN_Titre, IN_IDDossier, IN_IDClient FROM Intervention";
|
|
using var reader = cmd.ExecuteReader();
|
|
int shown = 0;
|
|
while (reader.Read() && shown < 10)
|
|
{
|
|
long pk = ReadInt64(reader, 0);
|
|
if (interventionId == 0) interventionId = pk;
|
|
sb.AppendLine($" IN_PKID={pk} RefInterne={Str(reader,1)} Titre={Str(reader,2)} IDDossier={Str(reader,3)} IDClient={Str(reader,4)}");
|
|
shown++;
|
|
}
|
|
sb.AppendLine($" CHOSEN interventionId = {interventionId}");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sb.AppendLine($" ERROR: {Redact(ex.Message)}");
|
|
}
|
|
|
|
if (interventionId == 0)
|
|
{
|
|
sb.AppendLine("NO INTERVENTION FOUND");
|
|
await File.WriteAllTextAsync(OutputPath, sb.ToString());
|
|
return;
|
|
}
|
|
|
|
// 3. Exécution des 8 requêtes (SQL identique au repository).
|
|
var queries = new (string Title, string Sql)[]
|
|
{
|
|
("InterventionSql", InterventionSql),
|
|
("DossierSql", DossierSql),
|
|
("ClientAdressesSql", ClientAdressesSql),
|
|
("CompteRenduSql", CompteRenduSql),
|
|
("RdvSql", RdvSql),
|
|
("ContactsSiteSql", ContactsSiteSql),
|
|
("ContactsDonneursSql", ContactsDonneursSql),
|
|
("PhotosSql", PhotosSql)
|
|
};
|
|
foreach (var q in queries)
|
|
{
|
|
sb.AppendLine();
|
|
sb.AppendLine($"── {q.Title} ──");
|
|
sb.AppendLine($" SQL: {q.Sql.Replace('\n', ' ').Trim()}");
|
|
sb.AppendLine($" PARAMS: {interventionId}");
|
|
DumpQuery(connection, sb, q.Sql, interventionId);
|
|
}
|
|
|
|
// 4. Validation jointure RDV collaborateur (RVC_GUID_Colb = Collaborateur.GUID).
|
|
sb.AppendLine();
|
|
sb.AppendLine("── RDV COLLAB JOIN VALIDATION ──");
|
|
DumpQuery(connection, sb,
|
|
"SELECT RDV_Collaborateur.RVC_PKID, RDV_Collaborateur.RVC_GUID_Colb, Collaborateur.GUID, Collaborateur.CO_Nom " +
|
|
"FROM RDV_Collaborateur LEFT OUTER JOIN Collaborateur ON RDV_Collaborateur.RVC_GUID_Colb = Collaborateur.GUID", null);
|
|
|
|
// 5. Validation sémantique CR_IDRdv.
|
|
sb.AppendLine();
|
|
sb.AppendLine("── CR_IDRDV SEMANTICS ──");
|
|
DumpQuery(connection, sb,
|
|
"SELECT CompteRendu.CR_PKID, CompteRendu.CR_IDRdv, CompteRendu.CR_GUID_Rdv, CompteRendu.CR_IDIntervention, " +
|
|
"RDV_Collaborateur.RVC_PKID, RDV_Planning.GUID " +
|
|
"FROM CompteRendu LEFT OUTER JOIN RDV_Collaborateur ON RDV_Collaborateur.RVC_PKID = CompteRendu.CR_IDRdv " +
|
|
"LEFT OUTER JOIN RDV_Planning ON RDV_Planning.GUID = RDV_Collaborateur.RVC_GUID_RDV", null);
|
|
|
|
// 6. Investigation du dédoublement de rôles (ContactsDonneursSql).
|
|
sb.AppendLine();
|
|
sb.AppendLine("── IP_CleRole / IP_IDRole ──");
|
|
DumpQuery(connection, sb,
|
|
"SELECT Intervention_Personne.IP_PKID, Intervention_Personne.IP_CleRole, Intervention_Personne.IP_IDRole " +
|
|
"FROM Intervention_Personne WHERE IP_IDIntervention = ?", interventionId);
|
|
|
|
sb.AppendLine();
|
|
sb.AppendLine("── ParametreMetier PA_Cle vide (rôles) ──");
|
|
DumpQuery(connection, sb,
|
|
"SELECT COUNT(*) AS CNT FROM ParametreMetier WHERE PA_Cle = ''", null);
|
|
|
|
await File.WriteAllTextAsync(OutputPath, sb.ToString());
|
|
}
|
|
|
|
private static void DumpQuery(OleDbConnection connection, StringBuilder sb, string sql, long? param)
|
|
{
|
|
try
|
|
{
|
|
using var cmd = connection.CreateCommand();
|
|
cmd.CommandText = sql;
|
|
if (param is not null)
|
|
{
|
|
cmd.Parameters.Add(new OleDbParameter { Value = param.Value });
|
|
}
|
|
|
|
using var reader = cmd.ExecuteReader();
|
|
int fieldCount = reader.FieldCount;
|
|
var colNames = new string[fieldCount];
|
|
for (int f = 0; f < fieldCount; f++) colNames[f] = reader.GetName(f);
|
|
sb.AppendLine($" COLUMNS: {string.Join(", ", colNames)}");
|
|
|
|
int rows = 0;
|
|
while (reader.Read() && rows < 8)
|
|
{
|
|
var values = new string[fieldCount];
|
|
for (int f = 0; f < fieldCount; f++)
|
|
{
|
|
values[f] = reader.IsDBNull(f) ? "<null>" : Truncate(reader.GetValue(f)?.ToString() ?? "");
|
|
}
|
|
sb.AppendLine($" ROW: {string.Join(" | ", values)}");
|
|
rows++;
|
|
}
|
|
sb.AppendLine($" ROWS (shown): {rows}");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sb.AppendLine($" ERROR: {Redact(ex.Message)}");
|
|
}
|
|
}
|
|
|
|
private static long ReadInt64(OleDbDataReader reader, int i)
|
|
{
|
|
try { return reader.IsDBNull(i) ? 0 : Convert.ToInt64(reader.GetValue(i)); }
|
|
catch { return 0; }
|
|
}
|
|
|
|
private static string Str(OleDbDataReader reader, int i)
|
|
{
|
|
try { return reader.IsDBNull(i) ? "<null>" : Truncate(reader.GetValue(i)?.ToString() ?? ""); }
|
|
catch { return "<err>"; }
|
|
}
|
|
|
|
private static string Truncate(string s) => s.Length <= 60 ? s : s[..60] + "…";
|
|
|
|
private static string Redact(string msg)
|
|
{
|
|
msg = msg.Replace("Password", "[PWD]", StringComparison.OrdinalIgnoreCase);
|
|
return msg;
|
|
}
|
|
|
|
private sealed class TestConfig
|
|
{
|
|
public int Port { get; set; }
|
|
public string Server { get; set; } = "";
|
|
public FilePasswordsConfig FilePasswords { get; set; } = new();
|
|
public DatabaseConfig Omika { get; set; } = new();
|
|
}
|
|
private sealed class FilePasswordsConfig
|
|
{
|
|
public string? ProtectionStd { get; set; }
|
|
public string? ProtectionPlus { get; set; }
|
|
}
|
|
private sealed class DatabaseConfig
|
|
{
|
|
public string Credential { get; set; } = "";
|
|
public string Database { get; set; } = "";
|
|
public string User { get; set; } = "";
|
|
}
|
|
|
|
private static TestConfig? LoadConfig()
|
|
{
|
|
if (!File.Exists(ConfigPath)) return null;
|
|
try
|
|
{
|
|
return JsonSerializer.Deserialize<TestConfig>(
|
|
File.ReadAllText(ConfigPath),
|
|
new JsonSerializerOptions { PropertyNameCaseInsensitive = true });
|
|
}
|
|
catch { return null; }
|
|
}
|
|
|
|
// === SQL identique à OmikaRapportChampRepository (copie fidèle) ===
|
|
|
|
private const string InterventionSql =
|
|
"SELECT Intervention.IN_Titre AS IN_Titre, Intervention.IN_RefInterne AS IN_RefInterne, " +
|
|
"Intervention.CreationDate AS IN_CreationDate, Intervention.IN_NumDevis AS IN_NumDevis, " +
|
|
"Intervention.IN_DateDevis, Intervention.IN_NumFacture, Intervention.IN_DateFacture, " +
|
|
"Intervention.IN_RefExterne AS IN_RefExterne, Intervention.IN_Description AS IN_Description, " +
|
|
"Intervention.IN_DureePrevue, " +
|
|
"CONCAT(Collaborateur.CO_Prenom, ' ', Collaborateur.CO_Nom) AS IN_CO_Nom, " +
|
|
"PA_TypeInter.PA_Libelle AS IN_PA_Type, Lieu.LI_CodeLieu AS LI_CodeLieu, " +
|
|
"CONCAT(AdLieu.AD_Adresse, ' ', AdLieu.AD_CodePostal, ' ', AdLieu.AD_Ville) AS LI_Adresse, " +
|
|
"Lieu.LI_CodeAcces AS LI_CodeAcces, Client.CL_Nom AS CL_Nom, " +
|
|
"CONCAT(AdClient.AD_Adresse, ' ', AdClient.AD_CodePostal, ' ', AdClient.AD_Ville) AS AD_Presentation, " +
|
|
"Societe.SO_Nom " +
|
|
"FROM Intervention " +
|
|
"LEFT OUTER JOIN Lieu ON Intervention.IN_IDLieu = Lieu.LI_PKID " +
|
|
"LEFT OUTER JOIN Adresse AdLieu ON Lieu.LI_IDAdresse = AdLieu.AD_PKID " +
|
|
"LEFT OUTER JOIN Dossier ON Intervention.IN_IDDossier = Dossier.DO_PKID " +
|
|
"LEFT OUTER JOIN Client ON Dossier.DO_IDClient = Client.CL_PKID " +
|
|
"LEFT OUTER JOIN Adresse AdClient ON Client.CL_IDAdressePrincipale = AdClient.AD_PKID " +
|
|
"LEFT OUTER JOIN Collaborateur ON Intervention.IN_IDSuiviPar = Collaborateur.CO_PKID " +
|
|
"LEFT OUTER JOIN Societe ON Intervention.IN_IDSociete = Societe.SO_PKID " +
|
|
"LEFT OUTER JOIN ParametreMetier PA_TypeInter ON Intervention.IN_CleType = PA_TypeInter.PA_CLE " +
|
|
"WHERE Intervention.IN_PKID = ?";
|
|
|
|
private const string DossierSql =
|
|
"SELECT Dossier.DO_Titre AS DOSSIER_TITRE, Dossier.DO_RefExterne AS DOSSIER_REFEXT, " +
|
|
"Dossier.DO_NumDossier AS DOSSIER_NUM, Dossier.DO_DateDebut AS DOSSIER_DATE, " +
|
|
"PA_StatutDossier.PA_Libelle AS DOSSIER_STATUT " +
|
|
"FROM Dossier INNER JOIN Intervention ON Intervention.IN_IDDossier = Dossier.DO_PKID " +
|
|
"LEFT OUTER JOIN ParametreMetier PA_StatutDossier ON Dossier.DO_CleStatut = PA_StatutDossier.PA_CLE " +
|
|
"WHERE Intervention.IN_PKID = ?";
|
|
|
|
private const string ClientAdressesSql =
|
|
"SELECT CONCAT(Adresse.AD_Adresse, ' ', Adresse.AD_CodePostal, ' ', Adresse.AD_Ville) AS Adresse " +
|
|
"FROM Client INNER JOIN Intervention ON Intervention.IN_IDClient = Client.CL_PKID " +
|
|
"LEFT JOIN Client_Adresse ON Client_Adresse.CA_IDClient = Client.CL_PKID " +
|
|
"INNER JOIN Adresse ON Adresse.AD_PKID = Client_Adresse.CA_IDAdresse " +
|
|
"WHERE Intervention.IN_PKID = ?";
|
|
|
|
private const string CompteRenduSql =
|
|
"SELECT CompteRendu.GUID, CompteRendu.CR_DateHeureDebut, CompteRendu.CR_DateHeureFin, " +
|
|
"CompteRendu.CR_NomSignataire, CompteRendu.CR_NomSignataireTech " +
|
|
"FROM CompteRendu " +
|
|
"INNER JOIN RDV_Collaborateur ON RDV_Collaborateur.RVC_PKID = CompteRendu.CR_IDRdv " +
|
|
"INNER JOIN RDV_Planning ON RDV_Planning.GUID = RDV_Collaborateur.RVC_GUID_RDV " +
|
|
"WHERE RDV_Collaborateur.Archive = 0 AND RDV_Planning.Archive = 0 AND RDV_Planning.RV_IdInter = ?";
|
|
|
|
private const string RdvSql =
|
|
"SELECT RDV_Planning.GUID, RDV_Collaborateur.RVC_DateDeb, RDV_Collaborateur.RVC_DateFin, " +
|
|
"RDV_Planning.RV_Objet, RDV_Planning.RV_Description, RDV_Planning.RV_Groupe, " +
|
|
"CONCAT(Collaborateur.CO_Prenom, ' ', Collaborateur.CO_Nom) AS PL_CO_NOM " +
|
|
"FROM RDV_Planning " +
|
|
"INNER JOIN RDV_Collaborateur ON RDV_Collaborateur.RVC_GUID_RDV = RDV_Planning.GUID " +
|
|
"LEFT OUTER JOIN Collaborateur ON RDV_Collaborateur.RVC_GUID_Colb = Collaborateur.GUID " +
|
|
"WHERE RDV_Planning.Archive = 0 AND RDV_Collaborateur.Archive = 0 AND RDV_Planning.RV_IdInter = ? " +
|
|
"ORDER BY RDV_Collaborateur.RVC_DateDeb";
|
|
|
|
private const string ContactsSiteSql =
|
|
"SELECT Personne.PE_PKID, Personne.PE_Nom, Personne.PE_Prenom, Personne.PE_TelFixe, " +
|
|
"Personne.PE_TelMobile, Intervention_Personne.IP_Etage, ParametreMetier.PA_Libelle AS IP_Role " +
|
|
"FROM Intervention_Personne " +
|
|
"INNER JOIN Intervention ON Intervention.IN_PKID = Intervention_Personne.IP_IDIntervention " +
|
|
"INNER JOIN Personne ON Personne.PE_PKID = Intervention_Personne.IP_IDPersonne " +
|
|
"LEFT OUTER JOIN ParametreMetier ON ParametreMetier.PA_CLE = Intervention_Personne.IP_CleRole " +
|
|
"WHERE Intervention_Personne.IP_IDIntervention = ? AND Intervention_Personne.IP_IDLieu = Intervention.IN_IDLieu " +
|
|
"ORDER BY Intervention_Personne.IP_PKID";
|
|
|
|
private const string ContactsDonneursSql =
|
|
"SELECT Personne.PE_PKID, Personne.PE_Nom, Personne.PE_Prenom, Personne.PE_TelFixe, " +
|
|
"Personne.PE_TelMobile, Intervention_Personne.IP_Etage, ParametreMetier.PA_Libelle AS IP_Role " +
|
|
"FROM Intervention_Personne " +
|
|
"INNER JOIN Intervention ON Intervention.IN_PKID = Intervention_Personne.IP_IDIntervention " +
|
|
"INNER JOIN Personne ON Personne.PE_PKID = Intervention_Personne.IP_IDPersonne " +
|
|
"LEFT OUTER JOIN ParametreMetier ON ParametreMetier.PA_CLE = Intervention_Personne.IP_CleRole " +
|
|
"WHERE Intervention_Personne.IP_IDIntervention = ? AND Intervention_Personne.IP_IDClient = Intervention.IN_IDClient " +
|
|
"ORDER BY Intervention_Personne.IP_PKID";
|
|
|
|
private const string PhotosSql =
|
|
"SELECT Document.DC_PKID, Document.GUID, Document.DC_Nom, Document.DC_DateFichier, Document.DC_Chemin, " +
|
|
"ParametreMetier.PA_Libelle AS CAT_Libelle " +
|
|
"FROM Document " +
|
|
"LEFT OUTER JOIN ParametreMetier ON ParametreMetier.PA_CLE = Document.DC_CleCategorie " +
|
|
"WHERE Document.DC_IDIntervention = ? ORDER BY Document.DC_PKID, Document.DC_DateFichier";
|
|
}
|