/// <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" })); } }