public IActionResult Edit(Competencias competencias)
{
sv.Actualizar(competencias);
int?id = sv.identificarCandidato(competencias.Id);
return(RedirectToAction("Index", "Home", new { id = id }));
}
public void Nuevo(Competencias id, int idcandidato)
{
id.Id = NuevoCodigo();
DB.Competencias.Add(id);
DB.SaveChanges();
NuevoCandidatoCompetencias(id.Id, idcandidato);
}
public async Task <IActionResult> Edit(int id, [Bind("CompetenciasId,LinguaMaterna,LinguaExtrangeira,Comunicacao")] Competencias competencias)
{
if (id != competencias.CompetenciasId)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(competencias);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!CompetenciasExists(competencias.CompetenciasId))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
return(View(competencias));
}
public ActionResult Habilitar(int codigo)
{
Boolean resultadoConsulta;
resultadoConsulta = Competencias.Habilitar(codigo);
return(RedirectToAction("Index", "Competencias"));
}
public Candidato ToCandidato()
{
return(new Candidato
{
CandidatoId = CandidatoId,
Cedula = Cedula,
Nombre = Nombre,
Apellidos = Apellidos,
Telefono = Telefono,
Correo = Correo,
SalarioAspira = SalarioAspira,
RecomendadoPor = RecomendadoPor,
Puesto = Puesto,
Usuario = Usuario,
FechaCreacion = FechaCreacion,
Estado = Estado,
Capacitaciones = Capacitaciones,
ExperienciasLaboral = ExperienciasLaboral,
Competencias = Competencias?.Select(x => new Database.Relations.CandidatoCompetencia
{
Competencia = x
})?.ToList(),
Idiomas = Idiomas?.Select(x => new Database.Relations.CandidatoIdioma
{
Idioma = x
})?.ToList()
});
}
private Competencias GetDadosCompetencias(string nrAluno)
{
Competencias competencias = new Competencias();
string str = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(str);
string query = "Select SELECT [planificacao],[decisao],[problemas],[iniciativa],[mudanca],[equipa],[lideranca],[comunicacao],[Aluno] FROM[LPA].[dbo].[competencias] where [Aluno] = " + nrAluno;
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
try
{
competencias.planificacao = Convert.ToInt32(dr.GetValue(0));
competencias.decisao = Convert.ToInt32(dr.GetValue(1));
competencias.problemas = Convert.ToInt32(dr.GetValue(2));
competencias.iniciativa = Convert.ToInt32(dr.GetValue(3));
competencias.mudanca = Convert.ToInt32(dr.GetValue(4));
competencias.equipa = Convert.ToInt32(dr.GetValue(5));
competencias.lideranca = Convert.ToInt32(dr.GetValue(6));
competencias.comunicacao = Convert.ToInt32(dr.GetValue(7));
}
catch (SqlException ex) { throw ex; }
}
conn.Close();
return(competencias);
}
private Competencias GetDadosCompetencias(string nrAluno)
{
Competencias competencias = new Competencias();
string str = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(str);
string query = "Select [adaptacao],[trabalhar],[decisoes],[objactivos],[ideias],[aprendizagem],[mentalidadeGlobal],[gestaoEquipas] FROM [LPA].[dbo].[competenciasV2] where [Aluno] = " + nrAluno;
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
try
{
competencias.adaptacao = Convert.ToInt32(dr.GetValue(0));
competencias.trabalhar = Convert.ToInt32(dr.GetValue(1));
competencias.decisoes = Convert.ToInt32(dr.GetValue(2));
competencias.objactivos = Convert.ToInt32(dr.GetValue(3));
competencias.ideias = Convert.ToInt32(dr.GetValue(4));
competencias.aprendizagem = Convert.ToInt32(dr.GetValue(5));
competencias.mentalidadeGlobal = Convert.ToInt32(dr.GetValue(6));
competencias.gestaoEquipas = Convert.ToInt32(dr.GetValue(7));
}
catch (SqlException ex) { throw ex; }
}
conn.Close();
return(competencias);
}
// GET: SaberesLogrados
public ActionResult Index()
{
List <Competencia> finalcompetencias = Competencias.LeerTodo();
if (finalcompetencias == null)
{
finalcompetencias = new List <Competencia>();
}
foreach (Competencia comp in finalcompetencias)
{
comp.Aprendizajes = Competencias.LeerSubAprendizajes(comp.Codigo);
if (comp.Aprendizajes == null)
{
comp.Aprendizajes = new List <Aprendizaje>();
}
foreach (Aprendizaje apr in comp.Aprendizajes)
{
apr.Saberes = Aprendizajes.LeerSubSaberes(apr.Codigo);
if (apr.Saberes == null)
{
apr.Saberes = new List <Saber>();
}
}
}
/*List<Competencia> finalcompetencias = SaberesLogrados.LeerTodo();
* if (finalcompetencias == null)
* {
* finalcompetencias = new List<Competencia>();
* }
* foreach (Competencia comp in finalcompetencias)
* {
* comp.Aprendizajes = Competencias.LeerSubAprendizajes(comp.Codigo);
*
* if (comp.Aprendizajes == null)
* {
* comp.Aprendizajes = new List<Aprendizaje>();
* }
* foreach (Aprendizaje apr in comp.Aprendizajes)
* {
* apr.Saberes = Aprendizajes.LeerSubSaberes(apr.Codigo);
*
* if (apr.Saberes == null)
* {
* apr.Saberes = new List<Saber>();
* }
* }
* }*/
return(View(finalcompetencias));
}
public async Task <IActionResult> Create([Bind("CompetenciasId,LinguaMaterna,LinguaExtrangeira,Comunicacao")] Competencias competencias)
{
if (ModelState.IsValid)
{
_context.Add(competencias);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
return(View(competencias));
}
// GET: Competencias
public ActionResult Index()
{
List <Competencia> finalcompetencias = Competencias.LeerTodo();
if (finalcompetencias == null)
{
finalcompetencias = new List <Competencia>();
}
return(View(finalcompetencias));
}
public async Task Create(Competencias model)
{
try
{
_db.competencias.Add(model);
await _db.SaveChangesAsync();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
public int AddCompetencia(Competencias model)
{
try
{
_dbContext.Competencias.Add(model);
_dbContext.SaveChanges();
return(200);
}
catch (Exception)
{
return(500);
}
}
public int EditCompetencia(int idCompetencia, Competencias model)
{
try
{
var comp = _dbContext.Competencias
.SingleOrDefault(r => r.Id == idCompetencia);
comp.Descripcion = model.Descripcion;
comp.Estado = model.Estado;
_dbContext.SaveChanges();
return(200);
}
catch (Exception)
{
return(500);
}
}
private void dgvCompetencia_DoubleClick(object sender, EventArgs e)
{
if (dgvCompetencia.CurrentRow.Index != -1)
{
model.IdCompetencia = Convert.ToInt32(dgvCompetencia.CurrentRow.Cells["IdCompetencia"].Value);
using (hrgoEntities1 db = new hrgoEntities1())
{
model = db.Competencias.Where(x => x.Candidato_IdCandidato == idCandidato && x.IdCompetencia == model.IdCompetencia).FirstOrDefault();
txtDescripcion.Text = model.Descripcion;
txtEstado.SelectedItem = model.Estado == true ? "Activo" : "inactivo";
}
btnSave.Text = "Actualizar";
btnDelete.Enabled = true;
}
}
public async Task Update(Competencias model)
{
try
{
var _model = await _db.competencias.FirstOrDefaultAsync(e => e.CompetenciaId == model.CompetenciaId);
if (_model != null)
{
_db.Entry(_model).CurrentValues.SetValues(model);
await _db.SaveChangesAsync();
}
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
public async Task UpdateEstado(Competencias obj)
{
try
{
var _obj = await _db.competencias.FirstOrDefaultAsync(e => e.CompetenciaId == obj.CompetenciaId);
if (_obj != null)
{
_obj.Estado = obj.Estado;
await _db.SaveChangesAsync();
}
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
// GET: CompetenciaEnAprendizaje
public ActionResult Index()
{
List <Competencia> competencias = Competencias.LeerTodo();
if (competencias == null)
{
competencias = new List <Competencia>();
}
foreach (Competencia comp in competencias)
{
comp.Aprendizajes = Competencias.LeerSubAprendizajes(comp.Codigo);
//Ordenar las SubSaberes según el codigo para evitar que se vean desordenadas :)
//apr.Saberes.Sort((x, y) => x.Codigo.CompareTo(y.Codigo));
if (comp.Aprendizajes == null)
{
comp.Aprendizajes = new List <Aprendizaje>();
}
}
return(View(competencias));
}
public async Task <IActionResult> Create([Bind("Id_Competencia,gbukrs,bukrs,Competencias_Desc,Generico")] Competencias competencias)
{
//Verifica que no haya registros duplicados.
if (_context.Competencias.Any(a => a.Id_Competencia == competencias.Id_Competencia && a.gbukrs == competencias.gbukrs && a.bukrs == competencias.bukrs))
{
ModelState.AddModelError("Id_Competencia", "El registro ya existe");
ViewBag.GpoCiaG = HttpContext.Session.GetString(SessionGpoCia);
if (ViewBag.GpoCiaG == null)
{
HttpContext.Session.SetString(SessionRegresa, "Create:Competencias");
return(RedirectToAction("Index", "GpoCiaGlobal"));
}
else
{
HttpContext.Session.SetString(SessionRegresa, "");
BukrsRepositorioEF BEF = new BukrsRepositorioEF(_context);
ViewBag.Bukrs = BEF.DaBukrs2(ViewBag.GpoCiaG);
return(View());
}
}
else
{
if (ModelState.IsValid)
{
_context.Add(competencias);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
return(View(competencias));
}
}
public ActionResult Crear(Competencia competencia)
{
competencia = Competencias.Crear(competencia);
return(RedirectToAction("Index", "Competencias"));
}
public ActionResult Editar(Competencia competencia)
{
Boolean result = Competencias.Editar(competencia);
return(RedirectToAction("Index", "Competencias"));
}
public IActionResult Create(Competencias competencias, int idCandidato)
{
sv.Nuevo(competencias, idCandidato);
return(RedirectToAction("Index", "Home", new { id = idCandidato }));
}
public async Task <IHttpActionResult> UpdateEstado(Competencias obj)
{
try { log.Info(new MDCSet(this.ControllerContext.RouteData));
await _repository.UpdateEstado(obj);
return(Ok("Registro actualizado correctamente!")); }
catch (Exception e) { log.Error(new MDCSet(this.ControllerContext.RouteData), e);
return(InternalServerError(e)); }
}
[HttpPost][Authorize] public async Task <IHttpActionResult> Create(Competencias obj)
{
try { log.Info(new MDCSet(this.ControllerContext.RouteData));
await _repository.Create(obj);
return(Ok("Competencia creada correctamente!")); }
catch (Exception e) { log.Error(new MDCSet(this.ControllerContext.RouteData), e);
return(InternalServerError(e)); }
}
public async Task <IActionResult> Edit(string id, string gbukrs, string bukrs, [Bind("Id_Competencia,gbukrs,bukrs,Competencias_Desc,Generico")] Competencias competencias)
{
if (id != competencias.Id_Competencia || gbukrs != competencias.gbukrs || bukrs != competencias.bukrs)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(competencias);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!CompetenciasExists(competencias.Id_Competencia, competencias.gbukrs, competencias.bukrs))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
return(View(competencias));
}
public void Eliminar(Competencias competencias)
{
DB.Remove(competencias);
DB.SaveChanges();
}
private void CreateDocument()
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
app.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"C:\LPA_IPAMLx_V2\LPA_IPAM.xlsx",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet ws = wb.Worksheets[1];
int col = 1;
int row = 16;
ws.Range["B8"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B9"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B10"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B11"].Value = Convert.ToString(replaceData).TrimStart();
GetNotas();
foreach (Notas nota in listaNotas)
{
ws.Cells[row, col].Font.Size = 10;
ws.Cells[row, col].Value = nota.UC.TrimStart();
ws.Cells[row, col + 5].Font.Size = 10;
ws.Cells[row, col + 5].Value = nota.Nota;
ws.Cells[row, col + 6].Font.Size = 10;
ws.Cells[row, col + 6].Value = nota.ECTS;
row++;
}
GetMedia((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["B59"].Value = (string)replaceMedia + " valores";
ws.Range["B73"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B74"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B75"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B76"].Value = Convert.ToString(replaceData).TrimStart();
Competencias competencias = GetDadosCompetencias((string)replaceNrAluno);
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(5, 1165, 470, 300);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chartPage.SeriesCollection();
var ser = seriesCollection.NewSeries();
chartPage.Legend.Delete();
ser.Values = new double[] { competencias.adaptacao, competencias.trabalhar, competencias.decisoes, competencias.objactivos, competencias.ideias, competencias.aprendizagem, competencias.mentalidadeGlobal, competencias.gestaoEquipas };
ser.XValues = new string[] { "Adaptação", "Trabalhar com os Outros", "Tomar Decisões", "Alcançar Objetivos", "Geração de Ideias", "Aprendizagem", "Mentalidade Global", "Gestão de Equipas" };
//chartRange = ws.get_Range("A1", "d5");
//chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered;
chartPage.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(5).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(6).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(7).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(8).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Range["B127"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B128"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B129"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B130"].Value = Convert.ToString(replaceData).TrimStart();
GetLinguas((string)replaceNrAluno);
GetRespAcademicas((string)replaceNrAluno);
GetEstagios((string)replaceNrAluno);
GetMobilidade((string)replaceNrAluno);
GetPremios((string)replaceNrAluno);
GetAtividades((string)replaceNrAluno);
GetOutrasExperiencias((string)replaceNrAluno);
GetRespSocial((string)replaceNrAluno);
col = 1;
row = 135;
if (listaLinguas.Count > 0)
{
string header = "LÍNGUAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Linguas l in listaLinguas)
{
row++;
ws.Cells[row, col].Value = " > " + l.lingua + " | Nível " + l.nivel;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespAcademicas.Count > 0)
{
string header = "RESPONSABILIDADES ACADÉMICAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ResponsabilidadesAcademicas l in listaRespAcademicas)
{
row++;
ws.Cells[row, col].Value = " > " + l.responsabilidade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaEstagios.Count > 0)
{
string header = "ESTÁGIOS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Estagios l in listaEstagios)
{
if (l.dataInicio != "01/01/1900")
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa + " | De " + l.dataInicio.Substring(0, 10) + " a " + l.dataFim.Substring(0, 10);
}
else
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa;
}
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaMobilidade.Count > 0)
{
string header = "MOBILIDADE INTERNACIONAL";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Mobilidade l in listaMobilidade)
{
row++;
ws.Cells[row, col].Value = " > " + l.tipo + " na " + l.programa + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespSocial.Count > 0)
{
string header = "RESPONSABILIDADE SOCIAL & VOLUNTARIADO";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ResposabilidadeSocial l in listaRespSocial)
{
row++;
ws.Cells[row, col].Value = " > " + l.AcaoSocial + " | " + l.AnoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaPremios.Count > 0)
{
string header = "PRÉMIOS & RECONHECIMENTOS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Premios l in listaPremios)
{
row++;
ws.Cells[row, col].Value = " > " + l.premio + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaAtividades.Count > 0)
{
string header = "ATIVIDADES DESPORTIVAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ActDesportivas l in listaAtividades)
{
row++;
ws.Cells[row, col].Value = " > " + l.atividade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaExperiencias.Count > 0)
{
string header = "OUTRAS EXPERIÊNCIAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (OutrasExperiencias l in listaExperiencias)
{
row++;
ws.Cells[row, col].Value = " > " + l.experiencia + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
string data = Convert.ToString(DateTime.Now.ToLongDateString());
ws.Cells[164, 1].Value = "Lisboa, " + data;
int nrCertificado = GravaCertificado(competencias);
string anoLetivo = getAnoLetivo((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["E11"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E76"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E130"].Value = nrCertificado + " | POR | " + anoLetivo;
}
private int GravaCertificado(Competencias competencias)
{
int nrCertificado = 0;
string str = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(str);
string queryCertificado = "SELECT id_certificados FROM CertificadosV2";
SqlCommand cmdCertificado = new SqlCommand(queryCertificado, conn);
conn.Open();
SqlDataReader dr = cmdCertificado.ExecuteReader();
try
{
while (dr.Read())
{
int temp = Convert.ToInt32(dr.GetValue(0));
if (temp > nrCertificado)
{
nrCertificado = temp;
}
}
nrCertificado++;
}
catch { nrCertificado = 431; }
conn.Close();
string queryCompetencias = "INSERT INTO [dbo].[CertificadosV2] ([nr_estudante],[data],[adaptacao],[trabalhar],[decisoes],[objactivos],[ideias],[aprendizagem],[mentalidadeGlobal],[gestaoEquipas]) VALUES (" + replaceNrAluno + ",'" + Convert.ToString(DateTime.Now) + "'," + competencias.adaptacao + "," + competencias.trabalhar + "," + competencias.decisoes + "," + competencias.objactivos + "," + competencias.ideias + "," + competencias.aprendizagem + "," + competencias.mentalidadeGlobal + "," + competencias.gestaoEquipas + ")";
SqlCommand cmdCompetencias = new SqlCommand(queryCompetencias, conn);
conn.Open();
cmdCompetencias.ExecuteNonQuery();
conn.Close();
foreach (Notas nota in listaNotas)
{
string query = "insert into [dbo].[Notas] ([uc],[nota],[ects],[nrEstudante],[codcurso],[data],[certificado]) Values ('" + nota.UC + "'," + nota.Nota + "," + nota.ECTS + "," + replaceNrAluno + "," + replaceCdCurso + ",'" + Convert.ToString(DateTime.Now) + "'," + nrCertificado + ")";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
foreach (Linguas lingua in listaLinguas)
{
string query = "INSERT INTO [dbo].[Experiencias] ([tipo],[descritivo],[nrAluno],[data],[certificado]) VALUES ('" + lingua.lingua + "','" + lingua.nivel + "'," + replaceNrAluno + ",'" + Convert.ToString(DateTime.Now) + "'," + nrCertificado + ")";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
foreach (ResponsabilidadesAcademicas resp in listaRespAcademicas)
{
string query = "INSERT INTO [dbo].[Experiencias] ([tipo],[descritivo],[nrAluno],[data], [certificado]) VALUES ('" + resp.responsabilidade + "','" + resp.anoLetivo + "'," + replaceNrAluno + ",'" + Convert.ToString(DateTime.Now) + "'," + nrCertificado + ")";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
foreach (Estagios estagio in listaEstagios)
{
string query = "INSERT INTO [dbo].[Experiencias] ([tipo],[descritivo],[nrAluno],[data],[certificado]) VALUES ('" + estagio.tipoEstagio + "','" + estagio.empresa + " inicio " + estagio.dataInicio + " fim " + estagio.dataFim + "'," + replaceNrAluno + ",'" + Convert.ToString(DateTime.Now) + "'," + nrCertificado + ")";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
foreach (Mobilidade mobilidade in listaMobilidade)
{
string query = "INSERT INTO [dbo].[Experiencias] ([tipo],[descritivo],[nrAluno],[data],[certificado]) VALUES ('" + mobilidade.tipo + "','" + mobilidade.programa + " anoletivo " + mobilidade.anoLetivo + "'," + replaceNrAluno + ",'" + Convert.ToString(DateTime.Now) + "'," + nrCertificado + ")";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
foreach (ResposabilidadeSocial responsabilidade in listaRespSocial)
{
string query = "INSERT INTO [dbo].[Experiencias] ([tipo],[descritivo],[nrAluno],[data],[certificado]) VALUES ('" + responsabilidade.AcaoSocial + "','" + responsabilidade.AnoLetivo + "'," + replaceNrAluno + ",'" + Convert.ToString(DateTime.Now) + "'," + nrCertificado + ")";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
foreach (Premios premio in listaPremios)
{
string query = "INSERT INTO [dbo].[Experiencias] ([tipo],[descritivo],[nrAluno],[data],[certificado]) VALUES ('" + premio.premio + "','" + premio.anoLetivo + "'," + replaceNrAluno + ",'" + Convert.ToString(DateTime.Now) + "'," + nrCertificado + ")";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
foreach (ActDesportivas atividade in listaAtividades)
{
string query = "INSERT INTO [dbo].[Experiencias] ([tipo],[descritivo],[nrAluno],[data],[certificado]) VALUES ('" + atividade.atividade + "','" + atividade.anoLetivo + "'," + replaceNrAluno + ",'" + Convert.ToString(DateTime.Now) + "'," + nrCertificado + ")";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
foreach (OutrasExperiencias experiencias in listaExperiencias)
{
string query = "INSERT INTO [dbo].[Experiencias] ([tipo],[descritivo],[nrAluno],[data],[certificado]) VALUES ('" + experiencias.experiencia + "','" + experiencias.anoLetivo + "'," + replaceNrAluno + ",'" + Convert.ToString(DateTime.Now) + "'," + nrCertificado + ")";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
listaNotas.Clear();
listaLinguas.Clear();
listaRespAcademicas.Clear();
listaEstagios.Clear();
listaMobilidade.Clear();
listaRespSocial.Clear();
listaPremios.Clear();
listaAtividades.Clear();
listaExperiencias.Clear();
return(nrCertificado);
}
public void Actualizar(Competencias competencias)
{
DB.Entry(competencias).State = EntityState.Modified;
DB.SaveChanges();
}
