/// <summary>
/// Execute query. Pass isscalar when running identity queries
/// </summary>
/// <param name="query"></param>
/// <param name="isScalar"></param>
/// <returns></returns>
public async Task <int> ExecuteQuery(string query, bool isScalar)
{
using (Microsoft.Data.SqlClient.SqlConnection cn = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString))
{
await cn.OpenAsync();
using (Microsoft.Data.SqlClient.SqlCommand cmd = new Microsoft.Data.SqlClient.SqlCommand(query))
{
cmd.CommandTimeout = CommandTimeOut;
cmd.Connection = cn;
if (!isScalar)
{
var result = await cmd.ExecuteNonQueryAsync();
return(result);
}
else
{
var result = await cmd.ExecuteScalarAsync();
return(Convert.ToInt32(result));
}
}
}
}
public async Task <IActionResult> PromoteStudents(EnrStud input)
{
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand com = new SqlCommand();
com.Connection = conn;
com.CommandText =
@"select
e.IdEnrollment, e.Semester, e.StartDate, s.IdStudy, s.Name
from Enrollment e
left join Studies s on s.IdStudy = e.IdStudy
where
s.Name = @studyName
and e.Semester = @semester";
com.Parameters.AddWithValue("studyName", input.Studies);
com.Parameters.AddWithValue("semester", input.Semester);
conn.Open();
SqlDataReader dataReader = await com.ExecuteReaderAsync();
await dataReader.ReadAsync();
Enrollment enr = new Enrollment
{
IdEnrollment = int.Parse(dataReader["IdEnrollment"].ToString()),
Semester = int.Parse(dataReader["Semester"].ToString()),
StartDate = DateTime.Parse(dataReader["StartDate"].ToString()),
Study = new Study
{
IdStudy = int.Parse(dataReader["IdStudy"].ToString()),
Name = dataReader["Name"].ToString(),
}
};
if (enr == null)
{
return(NotFound());
}
conn = new SqlConnection(_connectionString);
com = new SqlCommand();
com.Connection = conn;
com.CommandType = System.Data.CommandType.StoredProcedure;
com.CommandText = "PromoteStudents";
com.Parameters.AddWithValue("studies", input.Studies);
com.Parameters.AddWithValue("semester", input.Semester);
conn.Open();
await com.ExecuteNonQueryAsync();
return(StatusCode(200));
}
public int Save(PCAxis.Query.SavedQuery query, int?id)
{
query.Stats = null;
string pxsjson = JsonConvert.SerializeObject(query);
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand(
@"insert into
SavedQueryMeta
(
DataSourceType,
DatabaseId,
DataSourceId,
[Status],
StatusUse,
StatusChange,
OwnerId,
MyDescription,
CreatedDate,
SavedQueryFormat,
SavedQueryStorage,
QueryText,
Runs,
Fails
)
values
(
@databaseType,
@databaseId,
@mainTable,
'A',
'P',
'P',
'Anonymous',
@title,
@creationDate,
'PXSJSON',
'D',
@query,
0,
0
);
SELECT @@IDENTITY AS 'Identity';", conn);
cmd.Parameters.AddWithValue("databaseType", query.Sources[0].Type);
cmd.Parameters.AddWithValue("databaseId", query.Sources[0].DatabaseId);
cmd.Parameters.AddWithValue("mainTable", GetMaintable(query.Sources[0]));
cmd.Parameters.AddWithValue("title", "");
cmd.Parameters.AddWithValue("creationDate", DateTime.Now);
cmd.Parameters.AddWithValue("query", pxsjson);
int newid = Convert.ToInt32(cmd.ExecuteScalar());
return(newid);
}
return(-1);
}
public static List <Decrypted_logline> get_database_logs()
{
try
{
string queryString = "select * from logs";
var command = new Microsoft.Data.SqlClient.SqlCommand(queryString, database_connection);
if (command.Connection.State != System.Data.ConnectionState.Open)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
var reader = command.ExecuteReader();
List <Decrypted_logline> logs = new List <Decrypted_logline>();
while (reader.Read())
{
//DEFINE BYTE LENGTH
//byte[] EncryptedBytes = new byte[1024];
//reader.GetBytes(1, 0, EncryptedBytes, 0, 1024);
byte[] EncryptedBytes = (byte[])reader[2];
//count non-zero end bytes
int nonZeroBytes = 0;
for (int i = EncryptedBytes.Length - 1; i > -1; i--)
{
if (EncryptedBytes[i] == 0x00)
{
nonZeroBytes++;
}
else
{
break;
}
}
var DecryptedBytes = AesInst.CreateDecryptor().TransformFinalBlock(EncryptedBytes, 0, EncryptedBytes.Length - nonZeroBytes);
var DecryptedChars = Encoding.UTF8.GetChars(DecryptedBytes);
var Decrypted_Text = new string(DecryptedChars);
logs.Add(new Decrypted_logline(reader.GetDateTime(3), reader.GetString(1), Decrypted_Text));
}
command.Connection.Close();
return(logs);
}
catch
{
string queryString = "select * from logs";
var command = new Microsoft.Data.SqlClient.SqlCommand(queryString, database_connection);
if (command.Connection.State == System.Data.ConnectionState.Open)
{
command.Connection.Close();
}
return(new List <Decrypted_logline>());
}
}
public bool MarkAsFailed(int id)
{
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand("update SavedQueryMeta set UsedDate = @lastUsed, Runs = Runs + 1, Fails = Fails + 1 where QueryId = @queryId", conn);
cmd.Parameters.AddWithValue("queryId", id);
cmd.Parameters.AddWithValue("lastUsed", DateTime.Now);
return(cmd.ExecuteNonQuery() == 1);
}
}
public PCAxis.Query.SavedQuery Load(int id)
{
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand("select QueryText from SavedQueryMeta where QueryId = @queryId", conn);
cmd.Parameters.AddWithValue("queryId", id);
string query = cmd.ExecuteScalar() as string;
PCAxis.Query.SavedQuery sq = JsonHelper.Deserialize <PCAxis.Query.SavedQuery>(query) as PCAxis.Query.SavedQuery;
return(sq);
}
return(null);
}
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);
}
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;
}
}
}
public static int log_error(DateTime logDateTime, string labnumber, string logtext)
{
try
{
if (logtext.Length >= 256 || labnumber.Length >= 40)
{
throw new Exception("Too long!");
}
var log_in_char = logtext.ToArray();
byte[] log_bytes = Encoding.UTF8.GetBytes(log_in_char);
var encrypted_log = AesInst.CreateEncryptor().TransformFinalBlock(log_bytes, 0, log_bytes.Length);
string encrypted_log_HEX = "0x";
foreach (byte part in encrypted_log)
{
encrypted_log_HEX += part.ToString("X2");
}
string queryString = "insert into logs values(\'" + labnumber + "\', " + encrypted_log_HEX + ", CURRENT_TIMESTAMP)";
Microsoft.Data.SqlClient.SqlCommand command = new Microsoft.Data.SqlClient.SqlCommand(queryString, database_connection);
if (command.Connection.State != System.Data.ConnectionState.Open)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
command.Connection.Close();
return(0);
}
catch
{
return(-1);
}
}
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 static void GetPrivateKeyFromDB()
{
//getting key
string queryString = "select * from keys where login_id=" + user_id.ToString();
var command_newkey = new Microsoft.Data.SqlClient.SqlCommand(queryString, database_connection);
if (command_newkey.Connection.State != System.Data.ConnectionState.Open)
{
command_newkey.Connection.Open();
}
command_newkey.ExecuteNonQuery();
var reader = command_newkey.ExecuteReader();
byte[] dbprivkey = new byte[48];
byte[] dbiv = new byte[32];
while (reader.Read())
{
reader.GetBytes(1, 0, dbprivkey, 0, 48);
reader.GetBytes(2, 0, dbiv, 0, 32);
}
AesInst = System.Security.Cryptography.Aes.Create();
var privKeysha256 = MathOperations.sha256_byte(userpassword);
var ivmd5 = MathOperations.md5_byte(userpassword);
//enc private key with sha256(pwd) and md5(pwd)
AesInst.Key = privKeysha256;
AesInst.IV = ivmd5;
var SecretKey = AesInst.CreateDecryptor().TransformFinalBlock(dbprivkey, 0, dbprivkey.Length);
var InitVector = AesInst.CreateDecryptor().TransformFinalBlock(dbiv, 0, dbiv.Length);
//apply decrypted key
AesInst.Key = SecretKey;
AesInst.IV = InitVector;
command_newkey.Connection.Close();
}
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;
}
}
/// <summary>
/// Attempt to create a DbParameter using the <see cref="Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.CreateParameter(DbCommand, string, object, bool?)"/>
/// call for the specified column name.
/// </summary>
public static DbParameter TryCreateRelationalMappingParameter(string columnName, string parameterName, object value, TableInfo tableInfo)
{
if (columnName == null)
{
return(null);
}
if (!tableInfo.ColumnToPropertyDictionary.TryGetValue(columnName, out var propertyInfo))
{
return(null);
}
try
{
var relationalTypeMapping = propertyInfo.GetRelationalTypeMapping();
using var dbCommand = new Microsoft.Data.SqlClient.SqlCommand();
return(relationalTypeMapping.CreateParameter(dbCommand, parameterName, value, propertyInfo.IsNullable));
}
catch (Exception) { }
return(null);
}
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()
});
}
private void button1_Click(object sender, EventArgs e)
{
try
{
if (textBox_prevpwd.Text != MSSQL_logging.userpassword)
{
throw new Exception("Пароль не соответствует текущему");
}
if (textBox_newpwd.TextLength < 14)
{
throw new Exception("Минимальная длина пароля - 14 символов");
}
if (textBox_newpwd.Text != textBox_newPwdCopy.Text)
{
throw new Exception("Новые пароли не совпадают");
}
//checking for bad passwords
bool bad = false;
foreach (var pwd in badpwdlist)
{
if (pwd == textBox_newpwd.Text)
{
bad = true;
break;
}
}
if (bad)
{
throw new Exception("Выбран слабый пароль!");
}
//hashing
string hash = MathOperations.sha256(textBox_newpwd.Text);
//update password ===============
string query = "update logins set hash='" + hash + "' where id=" + MSSQL_logging.user_id.ToString();
var command = new Microsoft.Data.SqlClient.SqlCommand(query, MSSQL_logging.database_connection);
if (command.Connection.State != System.Data.ConnectionState.Open)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
//delete old pwd =============
query = "delete from keys where login_id=" + MSSQL_logging.user_id;
command.CommandText = query;
command.ExecuteNonQuery();
//renew key =================
MSSQL_logging.GetPrivateKeyFromDB();
var aesInst = System.Security.Cryptography.Aes.Create();
var privKeysha256 = MathOperations.sha256_byte(textBox_newpwd.Text);
var ivmd5 = MathOperations.md5_byte(textBox_newpwd.Text);
//enc private key with sha256(pwd) and md5(pwd)
aesInst.Key = privKeysha256;
aesInst.IV = ivmd5;
var Encrypted_SK = aesInst.CreateEncryptor().TransformFinalBlock(MSSQL_logging.AesInst.Key, 0, MSSQL_logging.AesInst.Key.Length);
string Encrypted_SK_String_HEX = "0x";
foreach (byte part in Encrypted_SK)
{
Encrypted_SK_String_HEX += part.ToString("X2");
}
var Encrypted_IV = aesInst.CreateEncryptor().TransformFinalBlock(MSSQL_logging.AesInst.IV, 0, MSSQL_logging.AesInst.IV.Length);
string Encrypted_IV_String_HEX = "0x";
foreach (byte part in Encrypted_IV)
{
Encrypted_IV_String_HEX += part.ToString("X2");
}
query = "insert into keys values (" + MSSQL_logging.user_id.ToString() + ", " + Encrypted_SK_String_HEX + ", " + Encrypted_IV_String_HEX + ")";
command.CommandText = query;
command.ExecuteNonQuery();
command.Connection.Close();
//renew obj=================
MSSQL_logging.userpassword = textBox_newpwd.Text;
MSSQL_logging.GetPrivateKeyFromDB();
MessageBox.Show("Успешно!", "Уведомление", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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);
}
public async Task <IActionResult> registerStudent(StdEnr input)
{
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand com = new SqlCommand();
Study study;
{
com.Connection = conn;
com.CommandText = "select IdStudy, Name from Studies where Name = @name";
com.Parameters.AddWithValue("name", input.Studies);
conn.Open();
try
{
SqlDataReader dataReader = await com.ExecuteReaderAsync();
await dataReader.ReadAsync();
study = new Study
{
IdStudy = int.Parse(dataReader["IdStudy"].ToString()),
Name = dataReader["Name"].ToString()
};
if (study == null)
{
return(BadRequest());
}
}
catch
{
return(null);
}
}
conn = new SqlConnection(_connectionString);
com = new SqlCommand();
conn.Open();
SqlTransaction transaction = conn.BeginTransaction();
com.Connection = conn;
com.Transaction = transaction;
com.CommandText =
@"declare @enrollmentId int
select
@enrollmentId = e.IdEnrollment
from Enrollment e
left join Studies s on e.IdStudy = s.IdStudy
where e.IdStudy = @studyId and e.Semester = 1
if @enrollmentId is null
begin
select @enrollmentId = max(IdEnrollment) + 1 from Enrollment
insert into Enrollment values (@enrollmentId, 1, @studyId, getdate());
end
insert into Student values (@index, @firstName, @lastName, @birthDate, @enrollmentId)";
com.Parameters.AddWithValue("studyId", study.IdStudy);
com.Parameters.AddWithValue("index", input.IndexNumber);
com.Parameters.AddWithValue("firstName", input.FirstName);
com.Parameters.AddWithValue("lastName", input.LastName);
com.Parameters.AddWithValue("birthDate", input.BirthDate);
try
{
await com.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
catch (Exception e)
{
await transaction.RollbackAsync();
}
return(StatusCode(200));
}
//---------------------------------------------------------------------
/// <summary>
/// Extension to Microsoft.Data.SqlClient to load query with untrusted
/// data provided in args parameters safely to mitigate the risk from
/// SQL injection attacks
/// </summary>
/// <param name="cmd"></param>
/// <param name="queryText"></param>
/// <param name="queryTextArgs"></param>
/// <exception cref="IronBox.AntiSQLi.Models.AntiSQLiException">
/// Thrown on loading errors
/// </exception>
//---------------------------------------------------------------------
public static void LoadQuerySecure(this Microsoft.Data.SqlClient.SqlCommand sqlCommandObj, String queryText, params Object[] queryTextArgs)
{
AntiSQLiCommon.ParameterizeAndLoadQuery <Microsoft.Data.SqlClient.SqlParameter>(sqlCommandObj, queryText, queryTextArgs);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string host = textBox_host.Text;
string port = textBox_port.Text;
//resolving host string
string hostString = "tcp:" + host;
if (port != "Default" && port != "")
{
hostString += ", " + port;
}
string getLoginsLogin = "******";
string getLoginsPassw = "ceb3478&Bc23b2&";
Microsoft.Data.SqlClient.SqlConnectionStringBuilder extractLoginsConnBuilder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder();
extractLoginsConnBuilder.DataSource = hostString;
extractLoginsConnBuilder.ConnectTimeout = 10;
extractLoginsConnBuilder.UserID = getLoginsLogin;
extractLoginsConnBuilder.Password = getLoginsPassw;
extractLoginsConnBuilder.Authentication = Microsoft.Data.SqlClient.SqlAuthenticationMethod.SqlPassword;
extractLoginsConnBuilder.IntegratedSecurity = false;
extractLoginsConnBuilder.TrustServerCertificate = true;
string queryString = "select * from logins";
StringBuilder errorMessages = new StringBuilder();
int user_id = 0;
using (Microsoft.Data.SqlClient.SqlConnection connection1 = new Microsoft.Data.SqlClient.SqlConnection(extractLoginsConnBuilder.ConnectionString))
{
Microsoft.Data.SqlClient.SqlCommand command1 = new Microsoft.Data.SqlClient.SqlCommand(queryString, connection1);
try
{
command1.Connection.Open();
command1.ExecuteNonQuery();
var reader = command1.ExecuteReader();
if (!reader.HasRows)
{
throw new Exception("Provided login not found or password is incorrect");
}
string login = textBox_login.Text;
string password = textBox_password.Text;
string hash = MathOperations.sha256(password);
bool login_in = false;
while (reader.Read())
{
if (reader.GetString(1) == login && reader.GetString(2) == hash)
{
user_id = reader.GetInt32(0);
login_in = true;
}
}
reader.Close();
command1.Connection.Close();
if (!login_in)
{
throw new Exception("Provided login not found or password is incorrect");
}
}
catch (Microsoft.Data.SqlClient.SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
throw new Exception(errorMessages.ToString());
}
}
string basicLogin = "******";
string basicPassw = "n3i7A7834bo&T21h@tbn";
extractLoginsConnBuilder.UserID = basicLogin;
extractLoginsConnBuilder.Password = basicPassw;
queryString = "select * from keys";
Microsoft.Data.SqlClient.SqlConnection connection = new Microsoft.Data.SqlClient.SqlConnection(extractLoginsConnBuilder.ConnectionString);
Microsoft.Data.SqlClient.SqlCommand command = new Microsoft.Data.SqlClient.SqlCommand(queryString, connection);
try
{
command.Connection.Open();
command.ExecuteNonQuery();
//check if user has key
var reader = command.ExecuteReader();
bool has_key = false;
while (reader.Read())
{
if (reader.GetInt32(0) == user_id)
{
has_key = true;
}
}
reader.Close();
if (!has_key)
{
MessageBox.Show("Ключ не найден. Сейчас будет сгенерирован новый ключ и добавлен в базу данных.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Warning);
var aesInst = System.Security.Cryptography.Aes.Create();
var privkey = aesInst.Key;
var iv = aesInst.IV;
var privKeysha256 = MathOperations.sha256_byte(textBox_password.Text);
var ivmd5 = MathOperations.md5_byte(textBox_password.Text);
//enc private key with sha256(pwd) and md5(pwd)
aesInst.Key = privKeysha256;
aesInst.IV = ivmd5;
var Encrypted_SK = aesInst.CreateEncryptor().TransformFinalBlock(privkey, 0, privkey.Length);
string Encrypted_SK_String_HEX = "0x";
foreach (byte part in Encrypted_SK)
{
Encrypted_SK_String_HEX += part.ToString("X2");
}
var Encrypted_IV = aesInst.CreateEncryptor().TransformFinalBlock(iv, 0, iv.Length);
string Encrypted_IV_String_HEX = "0x";
foreach (byte part in Encrypted_IV)
{
Encrypted_IV_String_HEX += part.ToString("X2");
}
queryString = "insert into keys values (" + user_id.ToString() + ", " + Encrypted_SK_String_HEX + ", " + Encrypted_IV_String_HEX + ")";
Microsoft.Data.SqlClient.SqlCommand command_newkey = new Microsoft.Data.SqlClient.SqlCommand(queryString, connection);
//command.Connection.Open();
command_newkey.ExecuteNonQuery();
command_newkey.Connection.Close();
}
//save session data
MSSQL_logging.user_id = user_id;
MSSQL_logging.userpassword = textBox_password.Text;
MSSQL_logging.database_connection = connection;
MSSQL_logging.GetPrivateKeyFromDB();
command.Connection.Close();
//open start panel
this.Hide();
var form_start = new Form_start();
form_start.Closed += (s, args) => this.Close();
form_start.Show();
}
catch (Microsoft.Data.SqlClient.SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
throw new Exception(errorMessages.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}