private static void FillNewTache(IXLWorksheet sheet, Tache tache, JourEvenement jour, ref int line, Planning p, IEnumerable <Affectation> affectations, bool readableExport)
{
IXLCell c = sheet.Cell("A" + line);
c.Value = tache.Id;
c = sheet.Cell("B" + line);
c.Value = tache.Nom;
c.Style.Font.FontSize = 20;
c.Style.Font.Bold = true;
line++;
IXLRow r = sheet.Row(line);
int j = FIRST_PLAN_COLUMN;
foreach (var def in jour.CreneauDefs.OrderBy(s => s.NoCreneau))
{
c = r.Cell(j);
c.Value = "'" + def.Debut.ToString("HH:mm", CultureInfo.InvariantCulture);
j++;
}
line++;
int maxB = tache.GetMaxBenevoleByDay(jour.Id);
for (int i = 0; i < maxB; i++)
{
r = sheet.Row(line);
FIllNewRow(r, jour, tache, p, i == 0, i == maxB - 1, i, affectations, readableExport);
line++;
}
}
private static void FillCalculManqueDispo(IXLRow row, FestivArtsContext ctx, JourEvenement j)
{
IXLCell ce = row.Cell(1);
ce.Style.Font.Bold = true;
ce.Value = "Total manque/surplus dispo";
int i = 2;
foreach (CreneauDef cd in ctx.CreneauDefs.Include("Dispoes").Include("Creneaux").Where(c => c.JourId == j.Id).ToList())
{
int minBen = cd.Creneaux.Sum(cr => cr.NbBenevoleMin);
int maxBen = cd.Creneaux.Sum(cr => cr.NbBenevoleMax);
int dispo = cd.Dispoes.Where(d => d.EstDispo).Count();
IXLCell c = row.Cell(i++);
c.Value = dispo - minBen;
c.Style.Font.Bold = true;
if (dispo < minBen)
{
c.Style.Font.FontColor = XLColor.Red;
}
else if (dispo > maxBen)
{
c.Style.Font.FontColor = XLColor.DarkGreen;
}
else
{
c.Style.Font.FontColor = XLColor.DarkOrange;
}
}
}
public static void FillPlanning(XLWorkbook book, FestivArtsContext ctx, JourEvenement jour, Planning p, bool readableExport = false)
{
foreach (TypeTache t in ctx.TypeTaches.Include("Taches.Creneaux.CreneauDef").Where(s => s.Taches.Max(t => t.Creneaux.Count(u => u.CreneauDef.JourId == jour.Id)) > 0))
{
var nom = t.Nom.Replace("/", "");
if (nom.Length > 20)
{
nom = nom.Substring(0, 20);
}
var worksheet = book.Worksheets.Add(t.Id + "-" + jour.Id + " " + nom);
worksheet.Column(1).Hide();
worksheet.Column(2).Width = 40;
List <Affectation> affectations;
List <Benevole> benevoles;
if (p == null)
{
affectations = new List <Affectation>();
benevoles = new List <Benevole>();
}
else
{
affectations = p.Affectations.ToList();
AffectationUtils.FillAffectationStatus(ctx, affectations);
benevoles = affectations.Where(s => s.Creneau.CreneauDef.JourId == jour.Id && s.Creneau.Tache.TypeTacheId == t.Id).Select(s => s.Benevole).Distinct().ToList();
}
FillNewTypeTache(worksheet, t, jour, affectations, benevoles, readableExport, p);
}
}
public ActionResult DeleteConfirmed(int id)
{
JourEvenement jourEvenement = db.JourEvenements.Find(id);
db.JourEvenements.Remove(jourEvenement);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Edit([Bind(Include = "Id,Nom,Description,Ordre,DateDebut,DateFin")] JourEvenement jourEvenement)
{
if (ModelState.IsValid)
{
db.Entry(jourEvenement).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(jourEvenement));
}
public ActionResult Create([Bind(Include = "Id,Nom,Description,Ordre,DateDebut,DateFin")] JourEvenement jourEvenement)
{
if (ModelState.IsValid)
{
db.JourEvenements.Add(jourEvenement);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(jourEvenement));
}
private static void FillNewTypeTache(IXLWorksheet sheet, TypeTache tache, JourEvenement jour, IEnumerable <Affectation> affectations, IEnumerable <Benevole> benevoles, bool readableExport = false, Planning p = null)
{
IXLCell c = sheet.Cell("A1");
c.Value = jour.Id;
c = sheet.Cell("B1");
c.Value = tache.Nom;
c.Style.Font.FontSize = 34;
c.Style.Font.Bold = true;
c = sheet.Cell("C1");
c.Value = jour.Nom;
c.Style.Font.FontSize = 30;
c.Style.Font.Bold = true;
int i = 3;
foreach (Tache t in tache.Taches.Where(s => s.Creneaux.Count(u => u.CreneauDef.JourId == jour.Id) > 0))
{
FillNewTache(sheet, t, jour, ref i, p, affectations, readableExport);
i += 2;
}
if (readableExport)
{
var r = sheet.Row(i);
r.Cell(2).Value = "Prenom";
r.Cell(2).Style.Font.Bold = true;
r.Cell(3).Value = "Nom";
r.Cell(3).Style.Font.Bold = true;
r.Cell(5).Value = "Tel";
r.Cell(5).Style.Font.Bold = true;
r.Cell(7).Value = "Email";
r.Cell(7).Style.Font.Bold = true;
r.Cell(10).Value = "Permis";
r.Cell(10).Style.Font.Bold = true;
i++;
foreach (Benevole b in benevoles.OrderBy(s => s.Prenom))
{
r = sheet.Row(i);
r.Cell(2).Value = b.Prenom;
r.Cell(3).Value = b.Nom;
r.Cell(5).Value = "'" + b.Tel;
r.Cell(7).Value = b.Email;
r.Cell(10).Value = b.Permis ? "Oui" : "Non";
i++;
}
}
}
// GET: JourEvenements/Delete/5
public ActionResult Delete(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
JourEvenement jourEvenement = db.JourEvenements.Find(id);
if (jourEvenement == null)
{
return(HttpNotFound());
}
return(View(jourEvenement));
}
private static int NbBenevoleRepas(FestivArtsContext ctx, JourEvenement j, int debut, int fin)
{
Dictionary <int, int> nbBeneParTache = new Dictionary <int, int>();
var crenDefs = ctx.CreneauDefs.Include("Creneaux").Where(c => c.JourId == j.Id).ToList().Where(c => c.Debut.Hour >= debut && c.Fin.Hour <= fin && c.Fin.DayOfYear == j.DateDebut.DayOfYear).ToList();
foreach (CreneauDef cd in crenDefs)
{
foreach (var cr in cd.Creneaux)
{
if (!nbBeneParTache.ContainsKey(cr.TacheId))
{
nbBeneParTache.Add(cr.TacheId, 0);
}
nbBeneParTache[cr.TacheId] = Math.Max(nbBeneParTache[cr.TacheId], cr.NbBenevoleMax);
}
}
return(nbBeneParTache.Values.Sum());
}
private static int NbBenevoleRepasReel(FestivArtsContext ctx, JourEvenement j, int debut, int fin)
{
HashSet <int> benevoles = new HashSet <int>();
foreach (CreneauDef cd in ctx.CreneauDefs.Include("Creneaux").Where(c => c.JourId == j.Id).ToList().Where(c => c.Debut.Hour >= debut && c.Fin.Hour <= fin && c.Fin.DayOfYear == j.DateDebut.DayOfYear))
{
foreach (var cr in cd.Creneaux)
{
foreach (var a in cr.Affectations.Select(a => a.BenevoleId))
{
if (!benevoles.Contains(a))
{
benevoles.Add(a);
}
}
}
}
return(benevoles.Count);
}
private static void FillNbBenevoleRepas(IXLRow row, FestivArtsContext ctx, JourEvenement j)
{
IXLCell ce = row.Cell(1);
ce.Style.Font.Bold = true;
ce.Value = "Nb midi : ";
ce = row.Cell(2);
ce.Style.Font.Bold = true;
ce.Value = NbBenevoleRepas(ctx, j, 11, 14);
ce = row.Cell(4);
ce.Style.Font.Bold = true;
ce.Value = "Nb soir : ";
ce = row.Cell(5);
ce.Style.Font.Bold = true;
ce.Value = NbBenevoleRepas(ctx, j, 18, 21);
}
public static void ImportPlanning(Planning p, FestivArtsContext ctx, XLWorkbook book)
{
foreach (var sheet in book.Worksheets)
{
int jourId;
if (!int.TryParse(sheet.Cell("A1").Value.ToString(), out jourId))
{
throw new ImportException("Cellule A1 n'est pas un nombre");
}
JourEvenement j = ctx.JourEvenements.Include("CreneauDefs").FirstOrDefault(s => s.Id == jourId);
if (j == null)
{
throw new ImportException("Cellule A1 n'est pas un jour valide");
}
int line = 2;
while (line < sheet.RowCount() && line < 1000)
{
IXLRow r = sheet.Row(line);
string fc = r.Cell(1).Value.ToString();
if (!string.IsNullOrWhiteSpace(fc))
{
int tacheId = 0;
if (!int.TryParse(fc, out tacheId))
{
throw new ImportException("Ligne " + line + " : not a number");
}
Tache t = ctx.Taches.Include("Creneaux.CreneauDef").FirstOrDefault(s => s.Id == tacheId);
if (t == null)
{
throw new ImportException("Ligne " + line + " : not a valid tache id");
}
line++;
ImportTache(sheet, ref line, t, j, p, ctx);
}
line++;
}
}
}
private static void ImportTache(IXLWorksheet sheet, ref int line, Tache t, JourEvenement jour, Planning p, FestivArtsContext ctx)
{
IXLRow timeRow = sheet.Row(line);
line++;
int maxB = t.GetMaxBenevoleByDay(jour.Id);
Regex regex = new Regex("^([0-9]+)");
var aAjouter = new Dictionary <int, HashSet <int> >();
for (int l = 0; l < maxB; l++)
{
IXLRow r = sheet.Row(line);
int i = ExcelUtils.FIRST_PLAN_COLUMN;
foreach (CreneauDef d in jour.CreneauDefs.OrderBy(s => s.NoCreneau))
{
var cell = r.Cell(i);
string benStr = cell.Value.ToString();
if (!string.IsNullOrWhiteSpace(benStr))
{
var m = regex.Match(benStr.Trim());
if (m.Success)
{
int id = int.Parse(m.Groups[0].Captures[0].Value);
var b = ctx.Benevoles.FirstOrDefault(s => s.Id == id);
if (b == null)
{
throw new ImportException(string.Format("Cell ({0}) Tache {1} : n° de bénévole introuvable : {2}'", cell.Address.ToStringRelative(true), t.Nom, benStr));
}
var c = t.Creneaux.FirstOrDefault(s => s.CreneauDefId == d.Id);
if (c == null)
{
throw new ImportException(string.Format("Cell ({0}) Tache {1} : creneau introuvable. Creneau def {2}'", cell.Address.ToStringRelative(true), t.Nom, d.Id));
}
if (!aAjouter.ContainsKey(b.Id))
{
aAjouter.Add(b.Id, new HashSet <int>());
}
if (!aAjouter[b.Id].Contains(c.Id))
{
aAjouter[b.Id].Add(c.Id);
}
}
else
{
throw new ImportException(string.Format("Cell ({0}) ne correspond pas a un n° de bénévole : {1}'", cell.Address.ToStringRelative(true), benStr));
}
}
i++;
}
line++;
}
if (aAjouter.Count > 0)
{
var anciensA = ctx.Affectations.Where(s => s.Creneau.CreneauDef.JourId == jour.Id &&
s.PlanningId == p.Id && s.Creneau.TacheId == t.Id).ToList();
foreach (Affectation a in anciensA)
{
if (aAjouter.ContainsKey(a.BenevoleId) && aAjouter[a.BenevoleId].Contains(a.CreneauId))
{
//L'affectation existait déjà, on ne fait rien
aAjouter[a.BenevoleId].Remove(a.CreneauId);
}
else
{
//N'existe plus, on la supprime;
ctx.Affectations.Remove(a);
}
}
foreach (var kv in aAjouter.Where(s => s.Value.Count > 0).ToList())
{
foreach (var v in kv.Value)
{
Affectation a = new Affectation()
{
BenevoleId = kv.Key,
CreneauId = v,
PlanningId = p.Id
};
ctx.Affectations.Add(a);
}
}
ctx.SaveChanges();
}
}
public static void FillNewPlanning(XLWorkbook book, FestivArtsContext ctx, JourEvenement jour, bool readableExport = false)
{
FillPlanning(book, ctx, jour, null, readableExport);
}
private static void FIllNewRow(IXLRow r, JourEvenement jour, Tache tache, Planning p, bool isFirst, bool isLast, int tacheLineCount, IEnumerable <Affectation> affectations, bool readableExport)
{
Dictionary <int, Creneau> crenauxNeeded = new Dictionary <int, Creneau>();
foreach (var s in tache.Creneaux.Where(s => s.CreneauDef.JourId == jour.Id))
{
crenauxNeeded.Add(s.CreneauDef.NoCreneau, s);
}
int i = FIRST_PLAN_COLUMN;
foreach (CreneauDef d in jour.CreneauDefs.OrderBy(s => s.NoCreneau))
{
List <Affectation> affs = affectations.Where(s => s.Creneau.CreneauDefId == d.Id && s.Creneau.TacheId == tache.Id).OrderBy(s => s.Benevole.Prenom).ToList();
IXLCell c = r.Cell(i);
if (i == FIRST_PLAN_COLUMN)
{
c.Style.Border.LeftBorder = XLBorderStyleValues.Thick;
}
if (i == FIRST_PLAN_COLUMN + jour.CreneauDefs.Count())
{
c.Style.Border.RightBorder = XLBorderStyleValues.Thick;
}
if (affs.Count > tacheLineCount)
{
var a = affs[tacheLineCount];
if (!readableExport)
{
c.Value = a.Benevole.GetExcelKey(a.Status);
if (a.Benevole.Permis)
{
c.Style.Font.Underline = XLFontUnderlineValues.Double;
}
if (a.Status == AffectationStatusEnum.NonDisponible || a.Status == AffectationStatusEnum.Duplique || a.Status == AffectationStatusEnum.Unknown)
{
c.Style.Font.FontColor = XLColor.Red;
c.Style.Font.Bold = true;
}
else if (a.Status == AffectationStatusEnum.NonSouhaite)
{
c.Style.Font.FontColor = XLColor.DarkOrange;
c.Style.Font.Bold = true;
}
else if (a.Status == AffectationStatusEnum.Souhaite)
{
c.Style.Font.FontColor = XLColor.DarkGreen;
c.Style.Font.Bold = true;
}
}
else
{
c.Value = a.Benevole.Prenom + " " + a.Benevole.Nom.Substring(0, 1);
c.Style.Font.FontColor = a.Benevole.GetBenevoleColor();
}
}
if (isFirst)
{
c.Style.Border.TopBorder = XLBorderStyleValues.Thick;
}
if (isLast)
{
c.Style.Border.BottomBorder = XLBorderStyleValues.Thick;
}
if (!crenauxNeeded.ContainsKey(d.NoCreneau))
{
c.Style.Fill.BackgroundColor = XLColor.Black;
c.Style.Fill.PatternBackgroundColor = XLColor.White;
c.Style.Fill.PatternType = XLFillPatternValues.DarkDown;
}
else
{
if (tacheLineCount >= crenauxNeeded[d.NoCreneau].NbBenevoleMax)
{
c.Style.Fill.BackgroundColor = XLColor.Black;
c.Style.Fill.PatternColor = XLColor.White;
c.Style.Fill.PatternType = XLFillPatternValues.DarkDown;
}
else if (tacheLineCount >= crenauxNeeded[d.NoCreneau].NbBenevoleMin)
{
c.Style.Fill.BackgroundColor = XLColor.LightGray;
}
}
i++;
}
}
private static void FillJour(IXLWorksheet sheet, FestivArtsContext ctx, Planning p, JourEvenement j, bool readable)
{
IXLCell c = sheet.Cell("A1");
c.Value = "Planning de " + j.Nom;
c.Style.Font.FontSize = 34;
c.Style.Font.Bold = true;
var creneauxDef = ctx.CreneauDefs.Where(s => s.JourId == j.Id).OrderBy(s => s.NoCreneau);
IXLRow r = sheet.Row(2);
int col = 2;
foreach (var cd in creneauxDef)
{
c = r.Cell(col);
c.Value = "'" + cd.Debut.ToString("HH:mm", CultureInfo.InvariantCulture);
col++;
}
int i = 3;
var listeBenevole = ctx.Benevoles.ToList();
foreach (var b in listeBenevole.OrderBy(s => s.Prenom))
{
var aff = p.Affectations.Where(s => s.BenevoleId == b.Id && s.Creneau.CreneauDef.JourId == j.Id);
var disp = b.Dispoes.Where(s => s.CreneauDef.JourId == j.Id && s.EstDispo);
FillBenevole(sheet, ctx, ref i, b, aff, disp, creneauxDef, listeBenevole, readable);
i++;
}
if (!readable)
{
i += 1;
FillCalculManqueDispo(sheet.Row(i), ctx, j);
i += 1;
FillNbBenevoleRepas(sheet.Row(i), ctx, j);
i += 1;
FillNbBenevoleRepasReel(sheet.Row(i), ctx, j);
}
}
