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