public UnitTestHelpers() { _configPath = DirectoryLocator.GetTargetConfigurationPath(); var msSqlPath = Path.Combine(DirectoryLocator.GetTargetProjectPath(typeof(Program)), @"..\..\MsSqlDb"); // For the unit tests we _tenantContext = new TenantContext { DbContext = { Catalog = "LeagueIntegration", Schema = "dbo", ConnectionKey = "dummy" } }; _tenantContext.DbContext.ConnectionString = $"Server=(LocalDB)\\MSSQLLocalDB;AttachDbFilename={msSqlPath}\\LeagueIntegrationTest.mdf;Database={_tenantContext.DbContext.Catalog};Integrated Security=true"; // Make sure we can connect to the database using (var connection = new Microsoft.Data.SqlClient.SqlConnection(_tenantContext.DbContext.ConnectionString)) try { connection.Open(); } finally { connection.Close(); } InitializeLlBlGenPro(); }
public IEnumerable <IndicadoresCorporativos> ObterIndicadoresCorporativos(long idProjeto) { var result = new List <IndicadoresCorporativos>(); Exception excSql = null; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); string sql = @"select a.Id, a.Identificador, a.Nome, a.TipoCalculo, case when b.IdIndicador is null then 0 else 1 end Vinculado, B.Id ID2 from Indicador a left join (select IdIndicador, Id from ProjetoEstruturaOrganizacional where IdProjeto = @p1 and Tipo = 2) b on (a.Id = b.IdIndicador) where a.Corporativo = 1 order by a.Nome"; using (var command = new Microsoft.Data.SqlClient.SqlCommand(sql, connection)) { try { command.Parameters.AddWithValue("p1", idProjeto); using var reader = command.ExecuteReader(); while (reader.Read()) { result.Add(new IndicadoresCorporativos { Id = reader.GetInt64(0), Identificador = reader.GetString(1), Nome = reader.GetString(2), TipoCalculo = reader.GetInt32(3), Vinculado = reader.GetInt32(4) == 1, IdProjetoEstruturaOrganizacional = reader.IsDBNull(5) ? null : (long?)reader.GetInt64(5) }); } } catch (Exception exc) { excSql = exc; } } connection.Close(); if (excSql != null) { throw excSql; } return(result); }
public int SmoExecute(string database, string procName, string text) { //https://www.cnblogs.com/long-gengyun/archive/2012/05/25/2517954.html //http://www.smochen.com/detail?aid=1280 //string connectonstring = this.db.Configuration.GetConnectionString("ApiDocConnStr"); string ServerIP = this.db.Configuration.GetConnectionString("ServerIP"); string pwd = this.db.Configuration.GetConnectionString("pwd"); string connectonstring = string.Format("server={0};uid=sa;pwd={1};database={2}", ServerIP, pwd, database); Microsoft.Data.SqlClient.SqlConnection sqlConnection = new Microsoft.Data.SqlClient.SqlConnection(connectonstring); sqlConnection.Open(); ServerConnection server1 = new ServerConnection(sqlConnection); Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(server1); Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database]; if (db.StoredProcedures.Contains(procName)) { text = text.Replace("CREATE PROCEDURE", "ALTER PROCEDURE", System.StringComparison.OrdinalIgnoreCase); } else { text = text.Replace("ALTER PROCEDURE", "CREATE PROCEDURE", System.StringComparison.OrdinalIgnoreCase); } int start = text.IndexOf("USE [") + 5; if (start != -1) { int end = text.IndexOf("]", start); if (end != -1) { string dbName = text.Substring(start, end - start); text = text.Replace(dbName, database); } } int i = server.ConnectionContext.ExecuteNonQuery(text); sqlConnection.Close(); return(1); }
private void CorrigirOrdens(long idSuperior) { var itens = Filter(item => item.IdSuperior == idSuperior).OrderBy(it => it.Ordem).ToList(); var strBuilder = new StringBuilder(); short ordem = 0; foreach (var it in itens) { ordem++; if (ordem != it.Ordem) { strBuilder.AppendLine($"update ProjetoEstruturaOrganizacional set Ordem = {ordem} where Id = {it.Id};"); } } if (strBuilder.Length > 0) { Exception excSql = null; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); var transaction = connection.BeginTransaction(); using (var command = new Microsoft.Data.SqlClient.SqlCommand(strBuilder.ToString(), connection, transaction)) { try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception exc) { excSql = exc; transaction.Rollback(); } } connection.Close(); if (excSql != null) { throw excSql; } } }
protected override void AfterUpdate(Indicador oldValue, Indicador newValue) { if (oldValue.Identificador != newValue.Identificador && oldValue.TipoCalculo == TipoCalculo.NaoCalculado) { Exception excSql = null; string termoPesquisa = $"[{oldValue.Identificador}]"; string novoIdentificador = $"[{newValue.Identificador}]"; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); var transaction = connection.BeginTransaction(); string sqlUpdate = $"update Indicador set Formula = replace(Formula, '{termoPesquisa}', '{novoIdentificador}') where TipoCalculo <> 1 and Formula like '%{termoPesquisa}%'"; using (var command = new Microsoft.Data.SqlClient.SqlCommand(sqlUpdate, connection, transaction)) { try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception exc) { excSql = exc; transaction.Rollback(); } } connection.Close(); if (excSql != null) { throw excSql; } } if (oldValue.Corporativo && !newValue.Corporativo) { var lista = projetoEstruturaOrganizacionalRepository.Filter(item => item.Tipo == TipoProjetoEstruturaOrganizacional.Corporativo && item.IdIndicador == newValue.Id); var listaIds = new List <long>(); lista?.ToList()?.ForEach(item => listaIds.Add(item.Id)); projetoEstruturaOrganizacionalRepository.DeleteMany(listaIds); } }
public void AvancarNivel(long id, long idSuperior) { CorrigirOrdens(idSuperior); var itens = Filter(item => item.IdSuperior == idSuperior).OrderBy(it => it.Ordem).ToList(); if (itens.Last().Id == id) { throw new Exception("Item não pode avançar."); } short ordem = itens.First(it => it.Id == id).Ordem; long idAfterElement = itens.First(it => it.Ordem == ordem + 1).Id; Exception excSql = null; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); var transaction = connection.BeginTransaction(); string sql = $"update ProjetoEstruturaOrganizacional set Ordem = {ordem + 1} where Id = {id}; update ProjetoEstruturaOrganizacional set Ordem = {ordem} where Id = {idAfterElement};"; using (var command = new Microsoft.Data.SqlClient.SqlCommand(sql, connection, transaction)) { try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception exc) { excSql = exc; transaction.Rollback(); } } connection.Close(); if (excSql != null) { throw excSql; } }
static void Main(string[] args) { { var lstnr = new TextLogTraceListener("Trace.log"); NetCalc.Logger.Listeners.Add(lstnr); System.Diagnostics.Trace.Listeners.Add(lstnr); } //OracleConfiguration.TraceFileLocation = @"C:\temp\traces"; //OracleConfiguration.TraceLevel = 6; var CalcBeg_Time = DateTime.UtcNow; CalcRec.HydrCalcDataRec[] edgeRec; ulong[] edgeOisPipeID = null; try { var cache = new FileCache(nameof(FileCache), new PipeNetCalc.ObjectBinder()) { DefaultPolicy = new CacheItemPolicy() { AbsoluteExpiration = ObjectCache.InfiniteAbsoluteExpiration }, PayloadReadMode = FileCache.PayloadMode.Serializable, PayloadWriteMode = FileCache.PayloadMode.Serializable, }; var wellKinds = NetCalc.WellKind.Oil | NetCalc.WellKind.Water; var cacheKey = $"{nameof(HydrCalcData)}_{wellKinds}"; var data = (HydrCalcData)cache.MyGet(cacheKey); if (data == null) { var nodeWell = LoadWellsData(wellKinds); data = LoadHydrCalcData(nodeWell); cache[cacheKey] = data; //cache.Flush(); } edgeOisPipeID = data.edgeID; var subnets = GetSubnets(data.edges, data.nodes); edgeRec = HydrCalc(data.edges, data.nodes, subnets, data.nodeWell, data.nodeName, $"TGF {wellKinds}"); #if DEBUG { // todo: remove debug code var usedWells = data.nodeWell.Where(p => p.Value.nUsed > 0).ToDictionary(p => p.Key, p => p.Value); var unusedWells = data.nodeWell.Where(p => p.Value.nUsed == 0).ToDictionary(p => p.Key, p => p.Value); //var iNode = Enumerable.Range(0, data.nodes.Length).Where(i => data.nodes[i].Node_ID == "5093939").First(); //var myEdges = data.edges.Where(e => unusedWells.ContainsKey(e.iNodeA) || unusedWells.ContainsKey(e.iNodeB)).ToArray(); var unusedWellNodeIDs = string.Join(",", unusedWells.Select(p => data.nodes[p.Key].Node_ID)); var usedWellNodeIDs = string.Join(",", usedWells.Select(p => data.nodes[p.Key].Node_ID)); } #endif } catch (Exception ex) { Console.WriteLine($"ERROR: {ex.Message}"); edgeRec = null; } return; const string csPPM = @"Data Source = alferovav; Initial Catalog = PPM.Ugansk.Test; User ID = ppm; Password = 123; Pooling = False"; //обходим баг? загрузки "Microsoft.Data.SqlClient.resources" using (var c = new Microsoft.Data.SqlClient.SqlConnection(csPPM)) { c.Open(); c.Close(); } Guid Calc_ID = SaveToDB.CreateCalculationRec(csPPM, CalcBeg_Time, edgeRec); if (edgeRec != null) { SaveToDB.SaveResults(csPPM, edgeRec, edgeOisPipeID, CalcBeg_Time, Calc_ID); } }
// This method gets called by the runtime. Use this method to add services to the container. // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940 public void ConfigureServices(IServiceCollection services) { // required for cookies and session cookies (will throw CryptographicException without) services.AddDataProtection() .SetApplicationName("ClubSite") .SetDefaultKeyLifetime(TimeSpan.FromDays(360)) .PersistKeysToFileSystem( new DirectoryInfo(Path.Combine(WebHostEnvironment.ContentRootPath, "DataProtectionKeys"))) .UseCryptographicAlgorithms(new AuthenticatedEncryptorConfiguration() { EncryptionAlgorithm = EncryptionAlgorithm.AES_256_CBC, ValidationAlgorithm = ValidationAlgorithm.HMACSHA256 }); // Make sure we can connect to the database using (var connection = new Microsoft.Data.SqlClient.SqlConnection(Configuration.GetConnectionString("VolleyballClub"))) try { connection.Open(); } finally { connection.Close(); } services.AddMemoryCache(); // Adds a default in-memory cache implementation // Custom ClubSite db context services.AddDbContext <Data.ClubDbContext>((sp, options) => options.UseSqlServer(Configuration.GetConnectionString("VolleyballClub"))); // Piranha service setup services.AddPiranha(svcBuilder => { svcBuilder.AddRazorRuntimeCompilation = WebHostEnvironment.IsDevelopment(); svcBuilder.UseCms(); svcBuilder.UseFileStorage(naming: Piranha.Local.FileStorageNaming.UniqueFolderNames); svcBuilder.UseImageSharp(); svcBuilder.UseManager(); // https://localhost:44306/manager/ initial user: admin, pw: password svcBuilder.UseTinyMCE(); svcBuilder.UseMemoryCache(); svcBuilder.UseEF <SQLServerDb>(db => db.UseSqlServer(Configuration.GetConnectionString("VolleyballClub"))); svcBuilder.UseIdentityWithSeed <IdentitySQLServerDb>(db => db.UseSqlServer(Configuration.GetConnectionString("VolleyballClub"))); }); // MUST be before AddMvc! services.AddSession(options => { options.IdleTimeout = TimeSpan.FromMinutes(60); options.Cookie.HttpOnly = true; options.Cookie.Name = ".sid"; options.Cookie.IsEssential = true; }); services.AddRazorPages().AddPiranhaManagerOptions(); services.AddMvc() .AddSessionStateTempDataProvider() .AddMvcOptions(options => { options.ModelBindingMessageProvider.SetValueMustNotBeNullAccessor(x => string.Format(Resources.ModelBindingMessageResource.ValueMustNotBeNull)); options.ModelBindingMessageProvider.SetAttemptedValueIsInvalidAccessor((x, val) => string.Format(Resources.ModelBindingMessageResource.AttemptedValueIsInvalid, x, val)); options.ModelBindingMessageProvider.SetValueIsInvalidAccessor(x => string.Format(Resources.ModelBindingMessageResource.ValueIsInvalid, x)); options.ModelBindingMessageProvider.SetValueMustBeANumberAccessor(x => string.Format(Resources.ModelBindingMessageResource.ValueMustBeANumber, x)); options.ModelBindingMessageProvider.SetMissingKeyOrValueAccessor(() => Resources.ModelBindingMessageResource.MissingKeyOrValue); }) .AddControllersAsServices(); services.AddHttpsRedirection(options => { options.RedirectStatusCode = StatusCodes.Status301MovedPermanently; }); services.Configure <ConfigurationPoco.MailSettings>( Configuration.GetSection(nameof(ConfigurationPoco.MailSettings)) ?? throw new ArgumentNullException( $"Configuration section '{nameof(ConfigurationPoco.MailSettings)}' not found.")); services.AddTransient <Services.IMailService, Services.MailService>(); }
/// <summary> /// Imports schema from server and creates a local table. /// Server table MUST include auto generated Guid column and a SEPERATE unique index constraint on PK. /// </summary> /// <param name="server">server connection string</param> /// <param name="client">client connection string</param> /// <param name="tblname">name of table to import from server</param> public static void CreateLocalFromServer(string server, string client, string tblname, string id) { var serverConn = new Microsoft.Data.SqlClient.SqlConnection(server); var conn = new ServerConnection(serverConn); // conn.Connect(); Server remote = new Server(conn); string schema = ""; // get the remote database by name Database remoteDB = remote.Databases[remotedb]; // get the table to copy into local Table table = remoteDB.Tables[tblname]; // build the table schema foreach (string str in table.Script()) { schema += str + " "; } // close connection to server serverConn.Close(); // Debug.Write(schema); try { using (var connection = new SqlConnection(client)) { // create a new local table with the remote schema var create = new SqlCommand(schema, connection); create.Connection.Open(); create.ExecuteNonQuery(); create.Connection.Close(); // ensure that the GUID column is NOT NULL as this is used to sync records string updatenull = "ALTER TABLE " + tblname + " ALTER COLUMN [GUID] uniqueidentifier NOT NULL;"; var updateNull = new SqlCommand(updatenull, connection); updateNull.Connection.Open(); updateNull.ExecuteNonQuery(); updateNull.Connection.Close(); // update the local table and flag the GUID as primary key string updatepk = "ALTER TABLE " + tblname + " " + " ADD CONSTRAINT PK_GUID_" + tblname + " PRIMARY KEY([GUID]); "; var updatePK = new SqlCommand(updatepk, connection); updatePK.Connection.Open(); updatePK.ExecuteNonQuery(); updatePK.Connection.Close(); // set a local constraint to generate a new unique id on record create string updatedf = " ALTER TABLE " + tblname + " " + " ADD CONSTRAINT DF_GUID_" + tblname + " " + " DEFAULT newid() FOR[GUID]; "; var updateDF = new SqlCommand(updatedf, connection); updateDF.Connection.Open(); updateDF.ExecuteNonQuery(); updateDF.Connection.Close(); // below code alters PK column. // we will drop and re-add the column, removing the identity insert attribute. // then, create a trigger to act as a PK for the app to work properly until // sync'd with server again (which reassigns the PK to whatever the server makes) // drop pk to remove identity insert string updateDropPK = "ALTER TABLE " + table + " DROP COLUMN [" + id + "]"; var cmdDropPK = new SqlCommand(updateDropPK, connection); cmdDropPK.Connection.Open(); cmdDropPK.ExecuteNonQuery(); cmdDropPK.Connection.Close(); // re add pk column string updateAddIdentity = "ALTER TABLE " + table + " ADD [" + id + "] INT DEFAULT ((0)) NOT NULL"; var cmdAddIdentity = new SqlCommand(updateAddIdentity, connection); cmdAddIdentity.Connection.Open(); cmdAddIdentity.ExecuteNonQuery(); cmdAddIdentity.Connection.Close(); // create an auto incremnt trigger to act as an incremental pk // this will be used to auto increment and *locally* track records for reference, // it will be overwritten when it is sync'd to the server with whatever the server dictates string updatePKTrigger = "CREATE TRIGGER [" + tblname + "_pk_trigger] " + " ON " + table + " FOR INSERT " + " AS " + " BEGIN " + " UPDATE " + table + " SET " + id + " = " + localPKStartingValue + " + (SELECT COUNT(" + id + ") FROM " + table + ") " + " WHERE [GUID] in (SELECT [GUID] from inserted) " + " END "; var cmdPKTrigger = new SqlCommand(updatePKTrigger, connection); cmdPKTrigger.Connection.Open(); cmdPKTrigger.ExecuteNonQuery(); cmdPKTrigger.Connection.Close(); //string uniquepk = "CREATE UNIQUE NONCLUSTERED INDEX [UNIQUEIX_" + tblname + "] ON [" + tblname + "]([" + id + "] ASC); "; //var cmdUniquePK = new SqlCommand(uniquepk, connection); //cmdUniquePK.Connection.Open(); //cmdUniquePK.ExecuteNonQuery(); //cmdUniquePK.Connection.Close(); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }
/// <summary> /// Creates cascades updates fks, and any indexes defined in the server /// </summary> public static void CreateConstraints() { // loop created tables in client and edit properties after sync for (int i = 0; i < main.context.LocalTables.Count; i++) { // string[] registerInfo = registeredTables[i]; // get table info and init connections string table = main.context.LocalTables[i].name; string id = main.context.LocalTables[i].pkColumn; var serverConn = new Microsoft.Data.SqlClient.SqlConnection(serverConnectionString); var conn = new ServerConnection(serverConn); var remote = new Server(conn); // fkschema is a string of sql statements to create fk constraints // ixschema is a string of sql statements to create various indexes defined in DB string fkschema = ""; string ixschema = ""; // get the remote database by name Database remoteDB = remote.Databases[remotedb]; // get the table to copy into local Table tbl = remoteDB.Tables[table]; // loop through and build foreign key scripts foreach (ForeignKey key in tbl.ForeignKeys) { var list = (System.Collections.IList)key.Script(); for (int i1 = 0; i1 < list.Count; i1++) { string str = (string)list[i1]; fkschema += str + " "; } try { // get the tablename that the FK references string referencedTable = key.ReferencedTable; // add the cascade trigger to the client database. This lets the app cascade foreign // keys if changed by server to keep references in tact and sync'd with server using (var connection = new SqlConnection(clientConnectionString)) { string cascade = " ALTER TABLE " + table + " ADD CONSTRAINT [cascade_" + table + "_" + key.Columns[0].Name + "] " + " FOREIGN KEY(" + key.Columns[0] + ") " + " REFERENCES " + referencedTable + "(" + key.Columns[0] + ") " + " ON UPDATE CASCADE "; var cmdCascade = new SqlCommand(cascade, connection); cmdCascade.Connection.Open(); cmdCascade.ExecuteNonQuery(); cmdCascade.Connection.Close(); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } } // loop through and build index scripts foreach (Index ix in tbl.Indexes) { var list = (System.Collections.IList)ix.Script(); for (int i1 = 0; i1 < list.Count; i1++) { string str = (string)list[i1]; ixschema += str + " "; } } // close connection to server serverConn.Close(); try { using (var connection = new SqlConnection(clientConnectionString)) { // add the foreign keys to the tables if (ixschema != "") { Debug.WriteLine("***CREATING INDEXES*** " + ixschema); var updateix = new SqlCommand(ixschema, connection); updateix.Connection.Open(); updateix.ExecuteNonQuery(); updateix.Connection.Close(); Debug.WriteLine("***FINISHED*** "); } } } catch (Exception ex) { Debug.WriteLine(ex.Message); } try { using (var connection = new SqlConnection(clientConnectionString)) { // add the foreign keys to the tables if (fkschema != "") { Debug.WriteLine("***CREATING FOREIGN KEYS*** " + fkschema); var updatefk = new SqlCommand(fkschema, connection); updatefk.Connection.Open(); updatefk.ExecuteNonQuery(); updatefk.Connection.Close(); Debug.WriteLine("***FINISHED*** "); } } } catch (Exception ex) { Debug.WriteLine(ex.Message); } try { // (this is optional) drop the delete trigger; may cause unwanted data deletion otherwise using (var connection = new SqlConnection(clientConnectionString)) { string dropDeleteTrigger = "DROP TRIGGER [" + trackingPrefix + "_" + table + "_delete_trigger] "; Debug.WriteLine("***DROPPING DELETE TRIGGER*** " + dropDeleteTrigger); var dropdel = new SqlCommand(dropDeleteTrigger, connection); dropdel.Connection.Open(); dropdel.ExecuteNonQuery(); dropdel.Connection.Close(); Debug.WriteLine("***FINISHED*** "); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } try { // drop server delete trigger using (var connection = new SqlConnection(serverConnectionString)) { string dropDeleteTrigger = "DROP TRIGGER [" + trackingPrefix + "_" + table + "_delete_trigger] "; Debug.WriteLine("***DROPPING DELETE TRIGGER*** " + dropDeleteTrigger); var dropdel = new SqlCommand(dropDeleteTrigger, connection); dropdel.Connection.Open(); dropdel.ExecuteNonQuery(); dropdel.Connection.Close(); Debug.WriteLine("***FINISHED*** "); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } } }
public ImpotacaoLancamentosDto GerarLancamentos(IEnumerable <ImpotacaoLancamentos> lancamentos) { var builderErros = new System.Text.StringBuilder(); var builderStatus = new System.Text.StringBuilder(); var comandos = new List <Tuple <string, decimal, decimal, long?> >(); int numeroLinha = 0; foreach (var item in lancamentos) { numeroLinha++; var erros = new System.Text.StringBuilder(); var projeto = databaseContext.Projeto.FirstOrDefault(it => it.Id == item.IdProjeto); if (projeto is null) { erros.Append(" O ID de projeto não existe. "); } var dataAtual = DateTime.UtcNow.Date; if (!projeto.Ativo || projeto.DataInicio.Date > dataAtual || projeto.DataTermino < dataAtual) { erros.Append(" O projeto deve estar ativo e dentro da vigência atual. "); } var indicador = databaseContext.Indicador.FirstOrDefault(it => it.Identificador == item.Identificador); if (indicador is null) { erros.Append(" O identificador não existe. "); } if (erros.Length > 1) { builderErros.AppendLine($"A linha {numeroLinha} está com problema(s): '{erros.ToString()}'."); } else if (builderErros.Length == 0) { var lancamento = databaseContext.IndicadorLancamentos.FirstOrDefault(it => it.IdProjeto == item.IdProjeto && it.IdIndicador == indicador.Id && it.Ano == item.Ano && it.Mes == item.Mes); decimal valorMeta = 0.00m; decimal valorRealizado = 0.00m; string status = "Valor da meta e valor do realizado foram zerados porque o tipo de cálculo do indicador está definido como 'Meta e realizado calculados'."; if (indicador.TipoCalculo == TipoCalculo.NaoCalculado) { valorMeta = item.ValorMeta; valorRealizado = item.ValorRealizado; status = string.Empty; } else if (indicador.TipoCalculo == TipoCalculo.SomenteMeta) { valorRealizado = item.ValorRealizado; status = "Valor da meta foi zerado porque o tipo de cálculo do indicador está definido como 'Meta calculada'."; } else if (indicador.TipoCalculo == TipoCalculo.SomenteRealizado) { valorMeta = item.ValorMeta; status = "Valor do realizado foi zerado porque o tipo de cálculo do indicador está definido como 'Realizado calculado'."; } if (lancamento is null) { comandos.Add(new Tuple <string, decimal, decimal, long?>($"insert into IndicadorLancamento (IdProjeto, IdIndicador, Ano, Mes, ValorMeta, ValorRealizado) values ({item.IdProjeto}, {indicador.Id}, {item.Ano}, {item.Mes}, @p1, @p2);", valorMeta, valorRealizado, null)); builderStatus.Append($"<p style='color:green'>A linha {numeroLinha} foi <b>inserida</b> com sucesso. (<i style='color:orange'>{status}</i>)</p><br/>").Replace("(<i style='color:orange'></i>)", string.Empty); } else { comandos.Add(new Tuple <string, decimal, decimal, long?>("update IndicadorLancamento set ValorMeta = @p1, ValorRealizado = @p2 where Id = @p3;", valorMeta, valorRealizado, lancamento.Id)); builderStatus.Append($"<p style='color:blue'>A linha {numeroLinha} foi <b>atualizada</b> com sucesso. (<i style='color:orange'>{status}</i>)</p><br/>").Replace("(<i style='color:orange'></i>)", string.Empty); } } } if (builderErros.Length > 1) { return(new ImpotacaoLancamentosDto { Sucesso = false, Mensagem = builderErros.ToString().Replace(Environment.NewLine, "<br/>") }); } string erro = null; using var context = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); context.Open(); using var transaction = context.BeginTransaction(); foreach (var item in comandos) { using var cmd = new Microsoft.Data.SqlClient.SqlCommand(item.Item1, context, transaction); try { cmd.Parameters.AddWithValue("p1", item.Item2); cmd.Parameters.AddWithValue("p2", item.Item3); if (item.Item4.HasValue) { cmd.Parameters.AddWithValue("p3", item.Item4.Value); } cmd.ExecuteNonQuery(); } catch (Exception exc) { erro = exc.Message; break; } } try { if (erro is null) { transaction.Commit(); } else { transaction.Rollback(); } context.Close(); } catch { } if (erro != null) { return(new ImpotacaoLancamentosDto { Sucesso = false, Mensagem = erro }); } return(new ImpotacaoLancamentosDto { Sucesso = true, Mensagem = builderStatus.ToString() }); }
public IList <RelatorioFiltroResultado> ObterLancamentosParaRelatorio(RelatorioFiltro filtro) { var builder = new System.Text.StringBuilder(); builder.AppendLine("select e.*, f.Nome, g.Nome, h.Nome from ( "); builder.AppendLine("select a.IdIndicador, a.IdSuperior, a.IdProjeto, b.Ano, b.Mes, c.Nome, c.Identificador, d.Sigla, c.ValorPercentualCriterio, c.ValorPercentualPeso, "); builder.AppendLine("b.ValorMeta, b.ValorRealizado, "); builder.AppendLine("(select IdUsuario from ProjetoEstruturaOrganizacional where Id = a.IdSuperior) Usuario, "); builder.AppendLine("(select IdNivelOrganizacional from ProjetoEstruturaOrganizacional where Id = (select IdSuperior from ProjetoEstruturaOrganizacional where Id = a.IdSuperior)) Cargo "); builder.AppendLine("from ProjetoEstruturaOrganizacional a "); builder.AppendLine("inner join IndicadorLancamento b on (a.IdIndicador = b.IdIndicador and a.IdProjeto = b.IdProjeto) "); builder.AppendLine("inner join Indicador c on (a.IdIndicador = c.Id) "); builder.AppendLine("inner join UnidadeMedida d on (c.IdUnidadeMedida = d.Id) "); builder.AppendLine("where a.Tipo = 7 "); builder.AppendLine($"and b.Ano >= {filtro.AnoInicial} and b.Ano <= {filtro.AnoFinal} "); builder.AppendLine($"and b.Mes >= {filtro.MesInicial} and b.Mes <= {filtro.MesFinal}) e "); builder.AppendLine("inner join Usuario f on (e.Usuario = f.Id) "); builder.AppendLine("inner join NivelOrganizacional g on (e.Cargo = g.Id) "); builder.AppendLine("inner join Projeto h on (e.IdProjeto = h.Id) "); builder.AppendLine("where 1 = 1 "); if (filtro.IdProjeto.HasValue) { builder.AppendLine($"and e.IdProjeto = {filtro.IdProjeto.Value} "); } if (filtro.IdIndicador.HasValue) { builder.AppendLine($"and e.IdIndicador = {filtro.IdIndicador.Value} "); } if (filtro.IdUsuario.HasValue) { builder.AppendLine($"and e.Usuario = {filtro.IdUsuario.Value} "); } if (filtro.IdCargo.HasValue) { builder.AppendLine($"and e.Cargo = {filtro.IdCargo.Value} "); } using var context = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); context.Open(); using var command = new Microsoft.Data.SqlClient.SqlCommand(builder.ToString(), context); using var reader = command.ExecuteReader(); var result = new List <RelatorioFiltroResultado>(); while (reader.Read()) { result.Add(new RelatorioFiltroResultado { IdIndicador = reader.GetInt64(0), IdSuperior = reader.GetInt64(1), IdProjeto = reader.GetInt64(2), Ano = reader.GetInt32(3), Mes = reader.GetInt32(4), NomeIndicador = reader.GetString(5), Identificador = reader.GetString(6), UnidadeMedida = reader.GetString(7), ValorPercentualCriterio = reader.GetDecimal(8), ValorPercentualPeso = reader.GetDecimal(9), ValorMeta = reader.GetDecimal(10), ValorRealizado = reader.GetDecimal(11), IdUsuario = reader.GetInt64(12), IdCargo = reader.GetInt64(13), NomeUsuario = reader.GetString(14), NomeCargo = reader.GetString(15), NomeProjeto = reader.GetString(16) }); } try { context.Close(); } catch { } return(result); }