public void ReadNorthwindSchema()
{
var connectionString = ConnectionStrings.Northwind;
DatabaseSchema schema;
//using the newer Microsoft Sql client
using (var connection = new Microsoft.Data.SqlClient.SqlConnection(connectionString))
{
try
{
connection.ConnectionString = connectionString;
connection.Open();
}
catch (Exception exception)
{
Assert.Inconclusive("Cannot access database for provider Microsoft.Data.SqlClient message= " +
exception.Message);
}
var dbReader = new DatabaseReader(connection);
dbReader.Owner = "dbo";
dbReader.ReadAll();
schema = dbReader.DatabaseSchema;
}
//password is removed in SqlServer 2017
//Assert.AreEqual(ConnectionStrings.Northwind, schema.ConnectionString, "Connection string is in the schema");
Assert.IsNotNull(schema.ConnectionString, "Connection string is in the schema");
Assert.AreEqual("dbo", schema.Owner, "Schema/owner is in the schema");
}
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 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);
}
protected override IDbConnection GetDbConnection(string connectionString)
{
var conn = new Microsoft.Data.SqlClient.SqlConnection(connectionString);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
return(conn);
}
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 override int CountActiveSessions(string applicationName)
{
Invariant.Require(applicationName.Length <= this.MaxApplicationNameLength);
using var connection = new Microsoft.Data.SqlClient.SqlConnection(DefaultConnectionString);
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = $@"SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE program_name = @applicationName";
command.Parameters.AddWithValue("applicationName", applicationName);
return((int)command.ExecuteScalar());
}
/// <summary>
/// Executes the current collection of registered commands
/// </summary>
/// <remarks>
/// Opens the connection, begins a transaction, initializes all command, then executes each command. Finally commits transaction.
/// Upon failure, transaction is rolled-back. Null commands automatically return 1 for execution.
/// </remarks>
/// <returns>List of integers returned from each registered command</returns>
public List <int> Execute()
{
List <int> retVal = new List <int>();
using (Microsoft.Data.SqlClient.SqlConnection conn = new Microsoft.Data.SqlClient.SqlConnection(_ConnectionString))
{
conn.Open();
Microsoft.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
try
{
int initialized = _Commands.Count;
for (int i = 0; i < initialized; i++)
{
if (_Commands[i] != null)
{
_Commands[i].Initialize(conn, trans);
}
}
for (int i = 0; i < _Commands.Count; i++)
{
if (_Commands[i] != null)
{
// This following line allows for chaining.
// in other words, a executing command can add more commands.
if (i >= initialized)
{
_Commands[i].Initialize(conn, trans);
}
retVal.Add(_Commands[i].Execute());
}
else
{
retVal.Add(1);
}
}
trans.Commit();
}
catch (Exception)
{
try { trans.Rollback(); } catch (Exception) { }
throw;
}
finally
{
_Commands.Clear();
}
}
return(retVal);
}
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);
}
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;
}
}
public static void SaveResults(string connStr, CalcRec.HydrCalcDataRec[] recs, ulong[] edgesIDs, DateTime Calc_Time, Guid Calculation_ID)
{
var dictO2P = new Dictionary <ulong, Guid>();
using (new StopwatchMs("Загрузка справочника преобразования ID OIS в ID PPM"))
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(connStr))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT OIS_ID, PPM_ID FROM OIS_2_PPM WHERE OIS_TABLE_NAME = 'PipeProstoyUchastok'";
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
dictO2P[Convert.ToUInt64(rdr[0])] = rdr.GetGuid(1);
}
}
}
}
using (new StopwatchMs("Bulk save into HYDRAULIC_CALCULATION_RESULT"))
using (var loader = new Microsoft.Data.SqlClient.SqlBulkCopy(connStr))
{
loader.DestinationTableName = "HYDRAULIC_CALCULATION_RESULT";
//loader.BatchSize = 1;
var reader = new BulkDataReader <CalcRec.HydrCalcDataRec>(recs, (iEdge, r, vals) =>
{
int i = 0;
var pu_id = edgesIDs[iEdge];
vals[i++] = dictO2P.TryGetValue(pu_id, out var g) ? g : Guid.Empty;
vals[i++] = Calc_Time;
vals[i++] = Calculation_ID;
r.GetValues(vals, ref i);
}, 44);
loader.WriteToServer(reader);
}
}
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);
}
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>();
}
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));
}
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()
});
}
