/// <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);
}
/// <summary>
/// Permet de lire les données selon un format configuré par l'opérateur, avec des filtres, des tris, la pagination, etc...
/// </summary>
/// <param name="selectorInstanceId">Id du SelectorInstance concerné</param>
/// <param name="filter">Chaines à filtrer, l'indexe représente le numéro de colonne sur lequel appliquer le filtre.</param>
/// <param name="start">Numéro de ligne à partir duquel il faut commencer la selection</param>
/// <param name="length">Nombre de ligne à sélectionner</param>
/// <param name="sortCol">Numéro de colonne à trier</param>
/// <param name="sortDir">Ordre du tri : ASC ou DESC</param>
/// <returns>Message de retour + données</returns>
public async Task <HttpResponseMessageResult> ReadData(long selectorInstanceId, string[] filter, int start, int length, int sortCol, string sortDir)
{
// Récupération du SelectorInstance et controles
List <SelectorInstance> lst = await UnitOfWork.GetDbContext().SelectorInstance
.Where(s => s.Id == selectorInstanceId)
.Include(s => s.WorkflowInstance)
.ThenInclude(wi => wi.WorkflowConfig)
.AsNoTracking()
.ToAsyncEnumerable()
.ToList();
if ((lst == null) || (lst.Count == 0))
{
throw new WrongParameterException($"ValueObject.ReadData : SelectorInstance ID {selectorInstanceId} not exist!");
}
SelectorInstance sel = lst.FirstOrDefault();
// On récupére les noms de table et de colonnes.
IEnumerable <string> colsTableName = await GridConfigurationDomain.GetColumnsFromGridConfiguration(sel, sel.WorkflowInstance);
// On extrait les noms de colonnes effectives (non ID)
List <string> nomColsDisplay = new List <string>();
for (int ic = 1; ic < colsTableName.Count(); ic++)
{
if (string.IsNullOrWhiteSpace(colsTableName.ElementAt(ic)))
{
continue;
}
if (colsTableName.ElementAt(ic).EndsWith("ID"))
{
continue;
}
nomColsDisplay.Add(colsTableName.ElementAt(ic));
}
// Construction de la requête de lecture des données
StringBuilder querySel = new StringBuilder();
// Choix des colonnes (toutes)
querySel.AppendLine("SELECT ");
foreach (string col in colsTableName.Skip(1))
{
querySel.AppendLine($"{col},");
}
querySel.SkipComma();
querySel.AppendLine($"FROM {colsTableName.ElementAt(0)}");
// Filtre
StringBuilder where = new StringBuilder();
if ((filter != null) && (filter.Count() > 0) && (filter.Any(f => !string.IsNullOrWhiteSpace(f))))
{
bool hasFilter = false;
for (int i = 0; i < filter.Count(); i++)
{
if (i >= nomColsDisplay.Count)
{
continue;
}
if (where.Length > 0)
{
where.AppendLine("AND");
}
where.AppendLine($"{nomColsDisplay[i]} LIKE '%{filter[i]}%'");
hasFilter = true;
}
if (hasFilter)
{
querySel.AppendLine("WHERE");
querySel.Append(where);
}
else
{
where.Clear();
}
}
// Tri
bool hasOrderBy = false;
if ((sortCol >= 0) && (sortCol < nomColsDisplay.Count))
{
querySel.Append($"ORDER BY {nomColsDisplay[sortCol]} ");
if (!string.IsNullOrWhiteSpace(sortDir) && sortDir.ToLower() == "asc")
{
querySel.AppendLine("ASC");
}
else
{
querySel.AppendLine("DESC");
}
hasOrderBy = true;
}
// Pagination
if (start >= 0)
{
if (!hasOrderBy)
{
querySel.AppendLine("ORDER BY ID");
}
querySel.AppendLine($"OFFSET {start} ROWS");
if (length > 0)
{
querySel.AppendLine($"FETCH NEXT {length} ROWS ONLY");
}
}
DbConnection connection = UnitOfWork.GetDbContext().Database.GetDbConnection();
IEnumerable <object[]> data = await ExecSqlHelper.ExecuteReaderAsync(querySel.ToString(), connection);
Dictionary <string, object> dicoJson = new Dictionary <string, object>();
dicoJson.Add("TableName", colsTableName.ElementAt(0));
int numLine = 0;
foreach (object[] row in data)
{
if (row.Length != colsTableName.Count() - 1) // -1 pour le nom de la table
{
continue;
}
Dictionary <string, object> rowJson = new Dictionary <string, object>();
for (int j = 0; j < row.Length; j++)
{
rowJson.Add(colsTableName.ElementAt(j + 1), row[j]);
}
dicoJson.Add(numLine.ToString(), rowJson);
numLine++;
}
// Requete de comptage
StringBuilder queryCount = new StringBuilder();
queryCount.AppendLine($"SELECT COUNT(1) FROM {colsTableName.ElementAt(0)} ");
if (where.Length > 0)
{
queryCount.AppendLine("WHERE");
queryCount.Append(where);
}
IEnumerable <object[]> dataCount = await ExecSqlHelper.ExecuteReaderAsync(queryCount.ToString(), connection);
if ((dataCount.Count() == 1) && (dataCount.ElementAt(0).Length == 1))
{
int nbrTotal = (int)dataCount.ElementAt(0)[0];
dicoJson.Add("TotalRows", nbrTotal);
}
// Données modifiables
List <long> idsValueObjectEditable = await UnitOfWork.GetDbContext().SelectorInstanceValueObject
.Where(sivo => sivo.SelectorInstanceId == selectorInstanceId && sivo.IsEditable)
.Select(sivo => sivo.ValueObjectId)
.ToAsyncEnumerable()
.ToList();
if ((idsValueObjectEditable != null) && (idsValueObjectEditable.Count > 0))
{
dicoJson.Add("EditablesIds", idsValueObjectEditable);
}
// Reponse du retour
HttpResponseMessageResult resData = new HttpResponseMessageResult()
{
IsSuccess = true,
Json = JsonConvert.SerializeObject(dicoJson)
};
return(resData);
}
//string FType, string GUID, string Name, string IsStart, string MainKey, string GroupSqlString, string SqlString, string AfterSqlString, string AfterSqlstring2
public async Task <string> OrgSqlHelper([FromBody] Organization organizationAddIn)
{
ExecSqlHelper sqlHelper;
bool result = false;
string DBType = string.Empty;
switch (organizationAddIn.DBType)
{
//Oracle 0
//SqlServer 1
//MySql 2
case "0":
//User Id=dbo;Password=romens;Data Source=192.168.100.9:1521/NewStddata;
conString = $"User Id={organizationAddIn.UserName};Password={organizationAddIn.Password};Data Source={organizationAddIn.ServerName}/{organizationAddIn.DataBaseName};";
DBType = DBTypeEnum.Oracle.ToString();
break;
case "1":
//"data source=*.*.*.*;initial catalog=mcudata;user id=sa;password=sa;"
conString = $"data source={organizationAddIn.ServerName.Replace(":", ",")};initial catalog={organizationAddIn.DataBaseName};user id={organizationAddIn.UserName};password={organizationAddIn.Password}";
DBType = DBTypeEnum.SqlServer.ToString();
break;
case "2":
string name = organizationAddIn.ServerName;
if (!organizationAddIn.ServerName.Contains(":"))
{
name = organizationAddIn.ServerName + ":3306";
}
string[] mysqlIp = name.Split(':');
//server=192.168.5.7;port=3306;database=beta-user;uid=root;pwd=Wq-.1997315421;CharSet=utf8
conString = $"server={mysqlIp[0]};port={mysqlIp[1]};database={organizationAddIn.DataBaseName};uid={organizationAddIn.UserName};pwd={organizationAddIn.Password};CharSet=utf8";
DBType = DBTypeEnum.MySql.ToString();
break;
default:
break;
}
sqlHelper = new ExecSqlHelper(conString, DBType);
try
{
result = await sqlHelper.TestConnectionAsync();
}
catch (System.Exception ex)
{
return(System.Text.Json.JsonSerializer.Serialize(new ResponseCommon {
msg = "测试连接失败，请检查填入的信息后重试！\r\n 错误信息:" + ex.Message, code = "-1"
}));
throw;
}
finally
{
await sqlHelper.DisposeAsync();
}
if (result)
{
return(System.Text.Json.JsonSerializer.Serialize(new ResponseCommon {
msg = "测试连接成功！", code = "0"
}));
}
else
{
return(System.Text.Json.JsonSerializer.Serialize(new ResponseCommon {
msg = "测试连接失败，请检查填入的信息后重试！", code = "-1"
}));
}
}
