public static JObject GetProfesorsForPS(string ps)
{
JObject jsonResponse = new JObject();
using (MyDNNDatabaseEntities context = new MyDNNDatabaseEntities())
{
var rvp = context.RezultatVotProfesorProgramStudius.Where(rv => rv.ProgramStudiu.DenumireScurta == ps.ToUpper());
if (!rvp.Any())
{
jsonResponse.Add("error", "Programul de studii nu a fost gasit.");
return(jsonResponse);
}
List <Profesor> prfs = rvp.Select(rv => rv.Profesor).ToList();
int nrProfesoriDeVotat = GetProcentOf(prfs.Count);
jsonResponse.Add("max", nrProfesoriDeVotat);
List <string> optiuni = new List <string>();
foreach (Profesor profesor in prfs)
{
optiuni.Add("(Nr.crt_" + profesor.ID_Profesor + ") " + profesor.Nume + " " + profesor.Prenume);
}
jsonResponse.Add("optiuni", new JArray(optiuni));
}
return(jsonResponse);
}
public static string FetchMoodleData()
{
try
{
HttpClient client = new HttpClient();
client.BaseAddress = new Uri(ConfigurationManager.AppSettings.Get("moodlews_baseURL"));
// Add an Accept header for JSON format.
client.DefaultRequestHeaders.Accept.Add(
new MediaTypeWithQualityHeaderValue("application/json"));
string moodlewstoken = ConfigurationManager.AppSettings.Get("moodlews_token");
string moodlewsFunctionName = ConfigurationManager.AppSettings.Get("moodlews_function_get_questionnaire_responses");
string substrcmidnumber = ConfigurationManager.AppSettings.Get("moodlews_questionnaire_pa_substrcmidnumber");
string queryParams = "?wstoken=" + moodlewstoken + "&wsfunction=" + moodlewsFunctionName + "&moodlewsrestformat=json" + "&substrcmidnumber=" + substrcmidnumber;
HttpResponseMessage httpResponseMessage = client.GetAsync(queryParams).Result;
if (httpResponseMessage.IsSuccessStatusCode)
{
var jsonString = httpResponseMessage.Content.ReadAsStringAsync().Result;
var json = JObject.Parse(jsonString);
if (json["reports"] == null)
{
return(String.Empty);
}
using (MyDNNDatabaseEntities context = new MyDNNDatabaseEntities())
{
foreach (var report in json["reports"])
{
string cmidnumber = report["cmidnumber"].ToString();
string denumireScurtaPS = cmidnumber.Substring(cmidnumber.IndexOf(substrcmidnumber) + substrcmidnumber.Length);
var listPS = context.ProgramStudius.Where(ps => ps.DenumireScurta.ToUpper().Equals(denumireScurtaPS.ToUpper())).ToList();
if (listPS.Count == 0)
{
// issue warning?
continue;
}
ProgramStudiu programStudii = listPS[0];
if (report["closedate"] != null && !String.IsNullOrEmpty(report["closedate"].ToString()))
{
String dateStr = report["closedate"].ToString().Replace('-', '/');
programStudii.DataInchidereVot = DateTime.ParseExact(dateStr, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
int totalVotes = 0;
foreach (var option in report["options"])
{
string optionText = option["optiontext"].ToString();
if (optionText.IndexOf("(") == -1)
{
continue;
}
optionText = optionText.Substring(optionText.IndexOf("(") + 1);
if (optionText.IndexOf("_") == -1)
{
continue;
}
optionText = optionText.Substring(optionText.IndexOf("_") + 1);
if (optionText.IndexOf(")") == -1)
{
continue;
}
string nrcrtProfesorStr = optionText.Substring(0, optionText.IndexOf(")"));
int nrcrtProfesor = 0;
if (!int.TryParse(nrcrtProfesorStr, out nrcrtProfesor))
{
// issue warning?
continue;
}
var listProf = context.Profesors.Where(prf => prf.ID_Profesor.Equals(nrcrtProfesor)).ToList();
if (listProf.Count == 0)
{
// issue warning?
continue;
}
Profesor profesor = listProf[0];
var listRez = programStudii.RezultatVotProfesorProgramStudius.Where(res => res.ID_Profesor.Equals(profesor.ID_Profesor)).ToList();
if (listRez.Count == 0)
{
// issue warning?
continue;
}
var rez = listRez[0];
if (option["nbofvotes"] != null && !String.IsNullOrEmpty(option["nbofvotes"].ToString()))
{
rez.NumarVoturi = short.Parse(option["nbofvotes"].ToString());
totalVotes += rez.NumarVoturi;
}
}
programStudii.NumarVotanti = totalVotes / GetProcentOf(report["options"].Count());
if (report["nbofstudents"] != null)
{
programStudii.NumarAbsolventi = int.Parse(report["nbofstudents"].ToString());
}
}
context.SaveChanges();
}
return(jsonString);
}
else
{
return("Error status code : " + httpResponseMessage.StatusCode.ToString());
}
}
catch (Exception e)
{
return(e.Message);
}
}
public static string InsertData()
{
ObjectParameter responseMg = new ObjectParameter("responseMessage", Type.GetType("System.String"));
ObjectParameter insertedId = new ObjectParameter("insertedID", Type.GetType("System.Int32"));
string msg = String.Empty;
DataSet dataSet = makeDataSet();
ReadData(dataSet);
using (MyDNNDatabaseEntities context = new MyDNNDatabaseEntities())
{
context.spClearDatabase();
foreach (DataRow tipCiclu in dataSet.Tables["TipCicluInvatamant"].Rows)
{
context.spAdaugaTipCicluInvatamant((string)tipCiclu["Denumire"], responseMg, insertedId);
}
foreach (DataRow ps in dataSet.Tables["ProgramStudiu"].Rows)
{
context.spAdaugaProgramStudiu(
(string)ps["Facultate"],
(int)ps["ID_TipCiclu"],
(string)ps["DenumireScurta"],
null,
(int)ps["NumarAbsolventi"],
(int)ps["NumarVotanti"],
responseMg,
insertedId
);
}
foreach (DataRow prof in dataSet.Tables["Profesor"].Rows)
{
context.spAdaugaProfesor(
(string)prof["Nume"],
(string)prof["Prenume"],
(string)prof["Email"],
(string)prof["GradDidactic"],
(string)prof["FacultateServiciu"],
(bool)prof["EligibilRemunerare"],
responseMg,
insertedId
);
}
foreach (DataRow rv in dataSet.Tables["RezultatVotProfesorProgramStudiu"].Rows)
{
context.spAdaugaRezultatVotProfesorProgramStudiu(
(int)rv["ID_ProgramStudiu"],
(int)rv["ID_Profesor"],
(short)rv["NumarVoturi"],
responseMg,
insertedId
);
}
}
dataSet.Dispose();
return(msg);
}
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);
}
}
