static public string ExportVotingStatus()
{
ProcessInputData.FetchMoodleData();
using (MyDNNDatabaseEntities context = new MyDNNDatabaseEntities())
{
List <Profesor> profesori = context.Profesors.ToList();
if (!File.Exists(GlobalValues.PATH_SOURCE_FILE))
{
return(null);
}
string destFileName = "Profesor_Apreciat_Status_Voturi_Elearning.xlsx";
string destFilePath = Path.Combine(Path.GetDirectoryName(GlobalValues.PATH_SOURCE_FILE), destFileName);
if (File.Exists(destFilePath))
{
File.Delete(destFilePath);
}
File.Copy(GlobalValues.PATH_SOURCE_FILE, destFilePath);
List <ProgramStudiu> programStudius = context.ProgramStudius.ToList();
using (XLWorkbook workbook = new XLWorkbook(destFilePath))
{
List <IXLWorksheet> worksheets = workbook.Worksheets.Where(w => w.Visibility.Equals(XLWorksheetVisibility.Visible)).ToList();
if (worksheets == null || worksheets.Count <= 0)
{
return(null);
}
IXLWorksheet ws = worksheets[0];
var rows = ws.Rows().ToList();
int indexProfesor = 0;
for (int rowIndex = 4; rowIndex < rows.Count; rowIndex++)
{
if (indexProfesor >= profesori.Count)
{
break;
}
var row = rows[rowIndex];
var cells = row.Cells().ToList();
var profesor = profesori[indexProfesor];
for (int columnindex = 6; columnindex < cells.Count; columnindex++)
{
var vot = profesor.RezultatVotProfesorProgramStudius.Where(v => v.ProgramStudiu.DenumireScurta == ws.Column(columnindex).FirstCell().GetValue <String>()).ToList();
if (vot.Count == 0)
{
continue;
}
ws.Column(columnindex).Cell(rowIndex + 1).Value = vot.First().NumarVoturi;
ws.Column(columnindex).Cell(rowIndex + 1).Style.Font.Bold = true;
ws.Column(columnindex).Cell(rowIndex + 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
indexProfesor++;
}
// adauga numar votanti si numar absolventi si data inchiderii votului
ws.Row(4).InsertRowsBelow(1);
ws.Row(5).Cell(2).Value = "Votarea Terminata";
ws.Row(5).Cell(2).Style = ws.Row(4).Cell(2).Style;
var range = ws.Range(ws.Row(5).Cell(2), ws.Row(5).Cell(5)).Merge();
range.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
int cellsCount = rows[0].Cells().Count();
for (int columnindex = 6; columnindex < cellsCount; columnindex++)
{
if (String.IsNullOrEmpty(ws.Column(columnindex).FirstCell().GetValue <String>()))
{
break;
}
var programList = programStudius.Where(p => p.DenumireScurta.Equals(ws.Column(columnindex).FirstCell().GetValue <String>())).ToList();
if (programList == null || programList.Count == 0)
{
continue;
}
var ps = programList[0];
ws.Column(columnindex).Cell(3).Value = ps.NumarAbsolventi;
ws.Column(columnindex).Cell(4).Value = ps.NumarVotanti;
if (ps.DataInchidereVot.HasValue)
{
if (ps.DataInchidereVot.Value <= DateTime.Now && ps.DataInchidereVot.Value.Year == DateTime.Now.Year)
{
ws.Row(5).Cell(columnindex).Value = "Y";
}
else if (ps.DataInchidereVot.Value <= DateTime.Now && ps.DataInchidereVot.Value.Year != DateTime.Now.Year)
{
ws.Row(5).Cell(columnindex).Value = "X";
}
else if (ps.DataInchidereVot.Value > DateTime.Now)
{
ws.Row(5).Cell(columnindex).Value = "N";
}
}
else
{
ws.Row(5).Cell(columnindex).Value = "N";
}
}
workbook.Save();
}
return(destFilePath);
}
}
public static string ExportFinalResults()
{
ProcessInputData.FetchMoodleData();
using (MyDNNDatabaseEntities context = new MyDNNDatabaseEntities())
{
List <Profesor> profesori = context.Profesors.ToList();
Dictionary <string, List <int> > remuneratiFaza1 = new Dictionary <string, List <int> >();
Dictionary <string, List <int> > remuneratiFaza2 = new Dictionary <string, List <int> >();
Dictionary <string, Dictionary <Profesor, int> > facultateVoturiTitulariLicentaProfesori = new Dictionary <string, Dictionary <Profesor, int> >();
Dictionary <string, Dictionary <Profesor, float> > facultateVoturiTitulariLicentaMasterProfesori = new Dictionary <string, Dictionary <Profesor, float> >();
if (!File.Exists(GlobalValues.PATH_SOURCE_FILE))
{
return(null);
}
string destFileName = "Profesor_Apreciat_Rezultate_Finale.xlsx";
string destFilePath = Path.Combine(Path.GetDirectoryName(GlobalValues.PATH_SOURCE_FILE), destFileName);
if (File.Exists(destFilePath))
{
File.Delete(destFilePath);
}
File.Copy(GlobalValues.PATH_SOURCE_FILE, destFilePath);
using (XLWorkbook workbook = new XLWorkbook(destFilePath))
{
var worksheets = workbook.Worksheets.Where(w => w.Visibility.Equals(XLWorksheetVisibility.Visible)).ToList();
if (worksheets == null || worksheets.Count <= 0)
{
return(null);
}
var ws = worksheets[0];
ws.Column(5).InsertColumnsAfter(1);
ws.Column(6).FirstCell().Value = "VOTURI LICENTA";
ws.Column(6).Width = 20;
ws.Column(6).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Column(6).InsertColumnsAfter(1);
ws.Column(7).FirstCell().Value = "VOTURI MASTER";
ws.Column(7).Width = 20;
ws.Column(7).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Column(7).InsertColumnsAfter(1);
ws.Column(8).FirstCell().Value = "PROCENTAJ";
ws.Column(8).Width = 15;
ws.Column(8).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Column(8).InsertColumnsAfter(1);
ws.Column(9).FirstCell().Value = "REMUNERAT FAZA 1";
ws.Column(9).Width = 25;
ws.Column(9).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Column(9).InsertColumnsAfter(1);
ws.Column(10).FirstCell().Value = "REMUNERAT FAZA 2";
ws.Column(10).Width = 25;
ws.Column(10).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
int columnCount = ws.ColumnCount();
int resultIndex = 0;
var rows = ws.Rows().ToList();
if (rows.Count == 0)
{
return(null);
}
int nrVoturiLicenta = 0;
int nrVoturiMaster = 0;
int nrVotantiProgrameDeStudii = 0;
int totalVoturi = 0;
for (int rowIndex = 4; rowIndex < rows.Count; rowIndex++)
{
if (resultIndex >= profesori.Count)
{
break;
}
nrVoturiLicenta = 0;
nrVoturiMaster = 0;
var row = rows[rowIndex];
var cells = row.Cells().ToList();
var currResult = profesori[resultIndex];
for (int columnindex = 11; columnindex < cells.Count; columnindex++)
{
string denumirePS = ws.Column(columnindex).FirstCell().Value.ToString();
var vot = currResult.RezultatVotProfesorProgramStudius.Where(v => v.ProgramStudiu.DenumireScurta == denumirePS).ToList();
if (vot.Count == 0)
{
continue;
}
ws.Column(columnindex).Cell(rowIndex + 1).Value = vot.First().NumarVoturi;
ws.Column(columnindex).Cell(rowIndex + 1).Style.Font.Bold = true;
ws.Column(columnindex).Cell(rowIndex + 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
if (vot.First().ProgramStudiu.ID_TipCiclu == 1)
{
nrVoturiLicenta += vot.First().NumarVoturi;
}
else
{
nrVoturiMaster += vot.First().NumarVoturi;
}
}
ws.Column(6).Cell(rowIndex + 1).Value = nrVoturiLicenta;
ws.Column(6).Cell(rowIndex + 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Column(7).Cell(rowIndex + 1).Value = nrVoturiMaster;
ws.Column(7).Cell(rowIndex + 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
totalVoturi = nrVoturiLicenta + nrVoturiMaster;
nrVotantiProgrameDeStudii = currResult.RezultatVotProfesorProgramStudius.Select(r => r.ProgramStudiu).Select(ps => ps.NumarVotanti).Sum();
float raportNrVoturiNrVotanti = (totalVoturi * 1.0f) / nrVotantiProgrameDeStudii;
if (totalVoturi != 0)
{
ws.Column(8).Cell(rowIndex + 1).Value = raportNrVoturiNrVotanti.ToString();
}
else
{
ws.Column(8).Cell(rowIndex + 1).Value = "0";
}
ws.Column(8).Cell(rowIndex + 1).Style.NumberFormat.Format = "0.000%";
ws.Column(8).Cell(rowIndex + 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
resultIndex++;
if (!facultateVoturiTitulariLicentaProfesori.ContainsKey(currResult.FacultateServiciu))
{
facultateVoturiTitulariLicentaProfesori.Add(currResult.FacultateServiciu, new Dictionary <Profesor, int>());
}
if (!facultateVoturiTitulariLicentaMasterProfesori.ContainsKey(currResult.FacultateServiciu))
{
facultateVoturiTitulariLicentaMasterProfesori.Add(currResult.FacultateServiciu, new Dictionary <Profesor, float>());
}
if (currResult.EligibilRemunerare == true)
{
facultateVoturiTitulariLicentaProfesori[currResult.FacultateServiciu].Add(currResult, nrVoturiLicenta);
facultateVoturiTitulariLicentaMasterProfesori[currResult.FacultateServiciu].Add(currResult, raportNrVoturiNrVotanti);
}
}
foreach (var entry in facultateVoturiTitulariLicentaProfesori)
{
var eligibiliDict = entry.Value;
var eligibiliOrderedList = eligibiliDict.OrderByDescending(el => el.Value).ToList();
int nrSelected = ProcessInputData.GetProcentOf(eligibiliOrderedList.Count);
remuneratiFaza1.Add(entry.Key, eligibiliOrderedList.Take(nrSelected).Select(e => e.Key.ID_Profesor).ToList());
}
foreach (var entry in facultateVoturiTitulariLicentaMasterProfesori)
{
var eligibiliDict = entry.Value;
var eligibiliOrderedList = eligibiliDict.OrderByDescending(el => el.Value).ToList();
remuneratiFaza2.Add(entry.Key, new List <int>());
int nrSelected = ProcessInputData.GetProcentOf(eligibiliOrderedList.Count);
foreach (var it in eligibiliOrderedList)
{
if (remuneratiFaza2[it.Key.FacultateServiciu].Count == nrSelected)
{
break;
}
if (!remuneratiFaza1[it.Key.FacultateServiciu].Contains(it.Key.ID_Profesor))
{
remuneratiFaza2[it.Key.FacultateServiciu].Add(it.Key.ID_Profesor);
}
}
}
resultIndex = 0;
for (int rowNr = 5; rowNr <= rows.Count; rowNr++)
{
if (resultIndex >= profesori.Count)
{
break;
}
if (remuneratiFaza1[profesori[resultIndex].FacultateServiciu].Contains(profesori[resultIndex].ID_Profesor))
{
ws.Column(9).Cell(rowNr).Value = "DA";
ws.Column(9).Cell(rowNr).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
if (remuneratiFaza2[profesori[resultIndex].FacultateServiciu].Contains(profesori[resultIndex].ID_Profesor))
{
ws.Column(10).Cell(rowNr).Value = "DA";
ws.Column(10).Cell(rowNr).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
resultIndex++;
}
// adauga numar votanti si numar absolventi si data inchiderii votului
ws.Row(4).InsertRowsBelow(1);
ws.Row(5).Cell(2).Value = "Votarea Terminata";
ws.Row(5).Cell(2).Style = ws.Row(4).Cell(2).Style;
var range = ws.Range(ws.Row(5).Cell(2), ws.Row(5).Cell(5)).Merge();
range.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
int cellsCount = rows[0].Cells().Count();
for (int columnindex = 11; columnindex < cellsCount; columnindex++)
{
string denumirePS = ws.Column(columnindex).FirstCell().GetValue <String>();
if (String.IsNullOrEmpty(denumirePS))
{
break;
}
var programList = context.ProgramStudius.Where(p => p.DenumireScurta.Equals(denumirePS)).ToList();
if (programList == null || programList.Count == 0)
{
continue;
}
var ps = programList[0];
ws.Column(columnindex).Cell(3).Value = ps.NumarAbsolventi;
ws.Column(columnindex).Cell(4).Value = ps.NumarVotanti;
if (ps.DataInchidereVot.HasValue)
{
if (ps.DataInchidereVot.Value <= DateTime.Now && ps.DataInchidereVot.Value.Year == DateTime.Now.Year)
{
ws.Row(5).Cell(columnindex).Value = "Y";
}
else if (ps.DataInchidereVot.Value <= DateTime.Now && ps.DataInchidereVot.Value.Year != DateTime.Now.Year)
{
ws.Row(5).Cell(columnindex).Value = "X";
}
else if (ps.DataInchidereVot.Value > DateTime.Now)
{
ws.Row(5).Cell(columnindex).Value = "N";
}
}
else
{
ws.Row(5).Cell(columnindex).Value = "N";
}
}
workbook.Save();
}
return(destFilePath);
}
}