/// <summary>
/// Enregistre les valeurs modifiées dans la table temporaire correspondant au SelectorInstance donné.
/// </summary>
/// <param name="selectIns">SelectorInstance</param>
/// <param name="wfInst">WorkflowInstance</param>
/// <param name="values">Valeurs à modifier</param>
/// <returns>Message de retour</returns>
public async Task <HttpResponseMessageResult> SaveDataInTemporyTable(SelectorInstance selectIns, WorkflowInstance wfInst, IEnumerable <KeyValuePair <long, double> > values)
{
// Transaction
IDbContextTransaction transaction = SessionStatsHelper.HttpHitGetDBTransaction(_serviceProvider);
if (transaction == null)
{
throw new DatabaseException("GridConfigurationDomain.SaveDataInTemporyTable: impossible to retrieve transaction connexion.");
}
IEnumerable <string> cols = await GetColumnsFromGridConfiguration(selectIns, wfInst);
StringBuilder qryUpdate = new StringBuilder();
string nomTable = cols.ElementAt(0); // le 1er élément est le nom de la table temporaire
qryUpdate.AppendLine($"UPDATE {nomTable}");
qryUpdate.AppendLine($"SET ");
StringBuilder qryCase = new StringBuilder();
foreach (KeyValuePair <long, double> kvp in values)
{
qryCase.AppendLine($"WHEN {kvp.Key} THEN '{kvp.Value.ToString(CultureInfo.InvariantCulture)}'");
}
foreach (string col in cols.Where(c => c.EndsWith("_ID")))
{
string colVal = col.Substring(0, col.IndexOf("_ID")) + "_VAL";
qryUpdate.AppendLine($"{colVal} = ");
qryUpdate.AppendLine($"CASE {col}");
qryUpdate.Append(qryCase);
qryUpdate.AppendLine($"ELSE {colVal}");
qryUpdate.AppendLine("END,");
}
// Nettoyage
qryUpdate.SkipComma();
// Exécution de la requete de création de table temporaire.
int nbrQry = await ExecSqlHelper.ExecuteNonQueryTransactionAsync(qryUpdate.ToString(), transaction);
return(new HttpResponseMessageResult()
{
IsSuccess = nbrQry > 0
});
}
/// <summary>
/// Créé la table temporaire pour un selectorInstance, selon la configuration de grid définie pour le WorkflowConfig lié.
/// </summary>
/// <param name="selectorInstance">Instance du SelectorInstance</param>
/// <param name="wfInstance">Instance du WorkflowInstance</param>
/// <returns>Message de retour</returns>
public async Task <HttpResponseMessageResult> CreateDataTableDB(SelectorInstance selectorInstance, WorkflowInstance wfInstance)
{
// Transaction
IDbContextTransaction transaction = SessionStatsHelper.HttpHitGetDBTransaction(_serviceProvider);
if (transaction == null)
{
throw new DatabaseException("GridConfigurationDomain.CreateDataTableDB: impossible to retrieve transaction connexion.");
}
HttpResponseMessageResult res = new HttpResponseMessageResult()
{
IsSuccess = true
};
// On récupére la configuration de l'opérateur
List <GridConfig> lstGridConf = await UnitOfWork.GetDbContext().GridConfig
.Include(gc => gc.ColumnDimensions)
.ThenInclude(gdc => gdc.Values)
.Include(gc => gc.RowDimensions)
.ThenInclude(gdc => gdc.Values)
.Include(gc => gc.FixedDimensions)
.ThenInclude(gdc => gdc.Values)
.Include(gc => gc.WorkflowConfig)
.Where(gc => gc.WorkflowConfig.Id == wfInstance.WorkflowConfig.Id)
.AsNoTracking()
.ToAsyncEnumerable()
.ToList();
GridConfig gridConf = lstGridConf.FirstOrDefault();
if (gridConf == null)
{
throw new SequenceException($"GridConfigurationDomain.CreateDataTableDB: no grid configuration for WorkflowConfig ({wfInstance.WorkflowConfig.Id}).");
}
// Construction de la requête
StringBuilder query = new StringBuilder();
string nomTable = string.Format(Constant.TEMPLATE_TEMPORARY_TABLENAME, selectorInstance.Id.ToString());
List <string> nomCols = new List <string>();
IEnumerable <DistributionDimensionGrid> lstDistCols = GenerateDistribution(gridConf.ColumnDimensions);
query.AppendLine($"CREATE TABLE {nomTable}(");
query.AppendLine("ID bigint IDENTITY(1,1) NOT NULL,");
foreach (GridDimensionConfig fixes in gridConf.FixedDimensions.OrderBy(c => c.Order))
{
string nomCol = ((int)fixes.InternalName).ToString();
query.AppendLine($"Dim{nomCol} nvarchar(max) NULL,");
nomCols.Add($"Dim{nomCol}");
}
foreach (GridDimensionConfig row in gridConf.RowDimensions.OrderBy(r => r.Order))
{
string nomCol = ((int)row.InternalName).ToString();
query.AppendLine($"Dim{nomCol} nvarchar(max) NULL,");
nomCols.Add($"Dim{nomCol}");
}
foreach (DistributionDimensionGrid ddg in lstDistCols)
{
query.AppendLine($"{ddg.ColumnName}_ID bigint NOT NULL,");
query.AppendLine($"{ddg.ColumnName}_VAL float NOT NULL,");
nomCols.Add($"{ddg.ColumnName}_ID");
nomCols.Add($"{ddg.ColumnName}_VAL");
}
query.SkipComma();
query.AppendLine(")");
// Exécution de la requete de création de table temporaire.
int nbrQry = await ExecSqlHelper.ExecuteNonQueryTransactionAsync(query.ToString(), transaction);
// Insertion des données dans la table temporaire.
StringBuilder qryInsert = new StringBuilder();
qryInsert.AppendLine($"INSERT INTO {nomTable} (");
foreach (string nomCol in nomCols)
{
qryInsert.AppendLine($"{nomCol},");
}
// Nettoyage
qryInsert.SkipComma();
qryInsert.AppendLine(")");
qryInsert.AppendLine("SELECT ");
List <string> groupedCols = new List <string>();
foreach (GridDimensionConfig fixes in gridConf.FixedDimensions.OrderBy(c => c.Order))
{
string nomCol = fixes.InternalName.ToString();
qryInsert.AppendLine($"{nomCol},");
groupedCols.Add(nomCol);
}
foreach (GridDimensionConfig row in gridConf.RowDimensions.OrderBy(r => r.Order))
{
string nomCol = row.InternalName.ToString();
qryInsert.AppendLine($"{nomCol},");
groupedCols.Add(nomCol);
}
foreach (DistributionDimensionGrid ddg in lstDistCols)
{
StringBuilder sbQry = new StringBuilder();
foreach (KeyValuePair <InternalNameDimension, string> pair in ddg.Selection)
{
if (sbQry.Length > 0)
{
sbQry.Append(" AND ");
}
sbQry.Append($"{pair.Key.ToString()} = '{pair.Value}'");
}
qryInsert.AppendLine("MAX(");
qryInsert.AppendLine("CASE");
qryInsert.Append("WHEN ");
qryInsert.Append(sbQry);
qryInsert.AppendLine(" THEN ID");
qryInsert.AppendLine("ELSE 0");
qryInsert.AppendLine("END");
qryInsert.AppendLine($"),");
qryInsert.AppendLine("MAX(");
qryInsert.AppendLine("CASE");
qryInsert.Append("WHEN ");
qryInsert.Append(sbQry);
qryInsert.AppendLine(" THEN CurrentValue");
qryInsert.AppendLine("ELSE 0");
qryInsert.AppendLine("END");
qryInsert.AppendLine($"),");
}
// Nettoyage
qryInsert.SkipComma();
qryInsert.AppendLine("FROM ValueObject");
// Jointure avec la table de liaison
qryInsert.AppendLine("WHERE ID IN (");
qryInsert.AppendLine($"SELECT ValueObjectId FROM SelectorInstanceValueObject WHERE SelectorInstanceId = {selectorInstance.Id} ");
qryInsert.AppendLine(")");
qryInsert.Append("GROUP BY ");
groupedCols.Reverse(); // Inverser pour la clause GROUP BY
foreach (string grpCol in groupedCols)
{
qryInsert.Append($"{grpCol},");
}
// Nettoyage
qryInsert.SkipComma();
nbrQry = await ExecSqlHelper.ExecuteNonQueryTransactionAsync(qryInsert.ToString(), transaction);
return(res);
}
