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 GetPlanning(int id, int planningId)
{
if (planningId == 0)
{
return(Last(id));
}
var workbook = new XLWorkbook();
JourEvenement jour;
string strPrefixFile = "new";
using (var ctx = new FestivArtsContext())
{
jour = ctx.JourEvenements.First(s => s.Id == id);
Planning p = ctx.Plannings.Include("Affectations.Benevole").First(s => s.Id == planningId);
if (p != null)
{
if (!string.IsNullOrWhiteSpace(p.Nom))
{
strPrefixFile = p.Nom;
}
else
{
strPrefixFile = p.Date.ToShortDateString();
}
}
ExcelUtils.FillPlanning(workbook, ctx, jour, p);
}
var stream = new MemoryStream();
workbook.SaveAs(stream);
stream.Position = 0;
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", strPrefixFile + "Planning" + jour.Nom + ".xlsx"));
}
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, Planning p, bool readable)
{
foreach (var j in ctx.JourEvenements.Include("CreneauDefs.Creneaux.Affectations.Benevole").OrderBy(j => j.Ordre))
{
var worksheet = book.Worksheets.Add(j.Nom);
FillJour(worksheet, ctx, p, j, readable);
worksheet.Columns().Width = 5;
worksheet.Column(1).Width = 25;
worksheet.SheetView.Freeze(2, 1);
}
}
public RechercheBenevoleViewModels(FestivArtsContext ctx)
{
this.SelectedJour = ctx.JourEvenements.OrderBy(s => s.Ordre).First().Id;;
Jours = ctx.JourEvenements.Select(s => new SelectListItem()
{
Text = s.Nom, Value = s.Id.ToString(), Selected = SelectedJour == s.Id
}).ToList();
Benevoles = ctx.Benevoles.Include("Dispoes.CreneauDef").Include("Affectations.Creneau.CreneauDef").Where(s => s.Dispoes.Count(t => t.EstDispo && t.CreneauDef.JourId == SelectedJour) > 0).ToList();
Benevoles.ForEach(s => s.FillDispoJour(SelectedJour));
}
public void InitializeCleanUp()
{
//Clean
using (var ctx = new FestivArtsContext())
{
List <Planning> plans = ctx.Plannings.Where(s => s.Nom.StartsWith("TUImportExport")).ToList();
foreach (var p in plans)
{
ctx.Affectations.RemoveRange(p.Affectations);
ctx.Plannings.Remove(p);
ctx.SaveChanges();
}
}
}
public ActionResult ByBenevole(bool?readable)
{
var workbook = new XLWorkbook();
using (var ctx = new FestivArtsContext())
{
Planning p = ctx.Plannings.Include("Affectations.Benevole").OrderByDescending(s => s.Date).FirstOrDefault();
ExcelByBenevoleExportUtil.FillPlanning(workbook, ctx, p, readable ?? true);
}
var stream = new MemoryStream();
workbook.SaveAs(stream);
stream.Position = 0;
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "planings.xlsx"));
}
// GET: Excel
public ActionResult New(int id)
{
var workbook = new XLWorkbook();
JourEvenement jour;
using (var ctx = new FestivArtsContext())
{
jour = ctx.JourEvenements.First(s => s.Id == id);
ExcelUtils.FillNewPlanning(workbook, ctx, jour);
}
var stream = new MemoryStream();
workbook.SaveAs(stream);
stream.Position = 0;
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "newPlanning" + jour.Nom + ".xlsx"));
}
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 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);
}
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);
}
public ActionResult LastReadable(int id)
{
var workbook = new XLWorkbook();
JourEvenement jour;
using (var ctx = new FestivArtsContext())
{
jour = ctx.JourEvenements.First(s => s.Id == id);
Planning p = ctx.Plannings.Include("Affectations.Benevole").OrderByDescending(s => s.Date).FirstOrDefault();
ExcelUtils.FillPlanning(workbook, ctx, jour, p, true);
}
var stream = new MemoryStream();
workbook.SaveAs(stream);
stream.Position = 0;
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Planning" + jour.Nom + ".xlsx"));
}
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);
}
}
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++;
}
}
}
public static void FillNewPlanning(XLWorkbook book, FestivArtsContext ctx, JourEvenement jour, bool readableExport = false)
{
FillPlanning(book, ctx, jour, null, readableExport);
}
private static void FillBenevole(IXLWorksheet sheet, FestivArtsContext ctx, ref int row, Benevole b, IEnumerable <Affectation> affectations, IEnumerable <Dispo> dispos, IEnumerable <CreneauDef> creneaux, IEnumerable <Benevole> listeBenevole, bool readable)
{
var aff = new Dictionary <int, List <Affectation> >();
var disp = new Dictionary <int, Dispo>();
foreach (var a in affectations)
{
if (!aff.ContainsKey(a.Creneau.CreneauDefId))
{
aff.Add(a.Creneau.CreneauDefId, new List <Affectation>());
}
aff[a.Creneau.CreneauDefId].Add(a);
}
foreach (var s in dispos)
{
disp.Add(s.CreneauDefId, s);
}
IXLRow r = sheet.Row(row);
IXLCell c = r.Cell(1);
c.Value = (readable ? "" : b.Id + " - ") + b.GetPrenomUnique(listeBenevole);
int i = 2;
int prevTacheId = -1;
int firsCell = -1;
foreach (var cren in creneaux)
{
c = r.Cell(i);
c.Style.Border.TopBorder = XLBorderStyleValues.Thin;
c.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
c.Style.Border.RightBorder = XLBorderStyleValues.Thin;
c.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
if (aff.ContainsKey(cren.Id))
{
if (aff[cren.Id].Count == 1)
{
//Cas normal 1 affectation
if (prevTacheId > 0 && aff[cren.Id][0].Creneau.TacheId == prevTacheId)
{
//cas identique : rien a faire
}
else
{
if (prevTacheId > 0)
{
if (firsCell != i - 1)
{
sheet.Range(row, firsCell, row, i - 1).Merge();
}
}
firsCell = i;
prevTacheId = aff[cren.Id][0].Creneau.TacheId;
}
}
else
{
if (prevTacheId > 0)
{
if (firsCell != i - 1)
{
sheet.Range(row, firsCell, row, i - 1).Merge();
}
}
firsCell = -1;
prevTacheId = -1;
}
c.Value = string.Join(",", aff[cren.Id].Select(s => s.Creneau.Tache.Nom));
c.Style.Fill.BackgroundColor = XLColor.LightBlue;
if (aff[cren.Id].Count > 1)
{
c.Style.Font.FontColor = XLColor.Red;
c.Style.Font.Bold = true;
}
}
else
{
if (prevTacheId > 0)
{
if (firsCell != i - 1)
{
sheet.Range(row, firsCell, row, i - 1).Merge();
}
}
firsCell = -1;
prevTacheId = -1;
if (!disp.ContainsKey(cren.Id))
{
c.Style.Fill.BackgroundColor = XLColor.Black;
c.Style.Fill.PatternBackgroundColor = XLColor.White;
c.Style.Fill.PatternType = XLFillPatternValues.DarkDown;
}
}
i++;
}
if (prevTacheId > 0)
{
if (firsCell != i - 1)
{
sheet.Range(row, firsCell, row, i - 1).Merge();
}
}
}
public void ImportExport()
{
//Creation des données
using (var ctx = new FestivArtsContext())
{
Planning p = new Planning()
{
Date = DateTime.Now, Nom = "TUImportExport"
};
Planning p2 = new Planning()
{
Date = DateTime.Now, Nom = "TUImportExportResult"
};
ctx.Plannings.Add(p);
ctx.Plannings.Add(p2);
ctx.SaveChanges();
int i = 0, j = 0;
var benevoles = ctx.Benevoles.ToList();
foreach (var c in ctx.Creneaux.ToList())
{
foreach (var b in benevoles)
{
var a = new Affectation()
{
BenevoleId = b.Id, PlanningId = p.Id, CreneauId = c.Id
};
if (j < c.NbBenevoleMax)
{
ctx.Affectations.Add(a);
}
else
{
break;
}
j++;
}
i++;
j = 0;
ctx.SaveChanges();
}
Assert.AreEqual(ctx.Creneaux.Sum(s => s.NbBenevoleMax), p.Affectations.Count);
}
//Génération exel
var workbooks = new Dictionary <int, Stream>();
using (var ctx = new FestivArtsContext())
{
Planning p = ctx.Plannings.First(s => s.Nom == "TUImportExport");
foreach (var jour in ctx.JourEvenements)
{
workbooks.Add(jour.Id, new MemoryStream());
var wb = new XLWorkbook();
ExcelUtils.FillPlanning(wb, ctx, jour, p);
wb.SaveAs(workbooks[jour.Id]);
workbooks[jour.Id].Position = 0;
}
}
foreach (var k in workbooks.Keys)
{
//Import
using (var ctx = new FestivArtsContext())
{
Planning p = ctx.Plannings.First(s => s.Nom == "TUImportExportResult");
var wb = new XLWorkbook(workbooks[k]);
ImportExcelUtil.ImportPlanning(p, ctx, wb);
}
}
//Assert
using (var ctx = new FestivArtsContext())
{
Planning p = ctx.Plannings.Include("Affectations.Creneau.CreneauDef").First(s => s.Nom == "TUImportExport");
Planning pres = ctx.Plannings.Include("Affectations.Creneau.CreneauDef").First(s => s.Nom == "TUImportExportResult");
Dictionary <int, List <Affectation> > affectationByDay = new Dictionary <int, List <Affectation> >();
Dictionary <int, List <Affectation> > affectationResultByDay = new Dictionary <int, List <Affectation> >();
foreach (int i in ctx.JourEvenements.Select(s => s.Id))
{
affectationByDay.Add(i, new List <Affectation>());
affectationResultByDay.Add(i, new List <Affectation>());
}
p.Affectations.ForEach(s => affectationByDay[s.Creneau.CreneauDef.JourId].Add(s));
pres.Affectations.ForEach(s => affectationResultByDay[s.Creneau.CreneauDef.JourId].Add(s));
foreach (var j in ctx.JourEvenements)
{
try
{
Assert.AreEqual(affectationByDay[j.Id].Count, affectationResultByDay[j.Id].Count);
}
catch (AssertFailedException)
{
var wb = new XLWorkbook(workbooks[j.Id]);
wb.SaveAs(@"D:\testResult\" + j.Nom + ".xlsx");
wb = new XLWorkbook();
ExcelUtils.FillPlanning(wb, ctx, j, pres);
wb.SaveAs(@"D:\testResult\" + j.Nom + "_res.xlsx");
throw;
}
}
Assert.AreEqual(p.Affectations.Count, pres.Affectations.Count);
foreach (var a in p.Affectations)
{
Assert.AreEqual(1, pres.Affectations.Count(s => s.BenevoleId == a.BenevoleId && s.CreneauId == a.CreneauId));
}
}
}
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 FillAffectationStatus(FestivArtsContext ctx, IEnumerable <Affectation> affectations)
{
foreach (var a in affectations)
{
a.Status = AffectationStatusEnum.Unknown;
}
Dictionary <int, HashSet <int> > dispoes = new Dictionary <int, HashSet <int> >();
foreach (var d in ctx.Dispoes.Where(s => s.EstDispo))
{
if (!dispoes.ContainsKey(d.BenevoleId))
{
dispoes[d.BenevoleId] = new HashSet <int>();
}
var s = dispoes[d.BenevoleId];
s.Add(d.CreneauDefId);
}
Dictionary <int, Dictionary <int, Preference> > prefs = new Dictionary <int, Dictionary <int, Preference> >();
foreach (var p in ctx.Preferences)
{
if (!prefs.ContainsKey(p.BenevoleId))
{
prefs.Add(p.BenevoleId, new Dictionary <int, Preference>());
}
prefs[p.BenevoleId].Add(p.TypeTacheId, p);
}
Dictionary <int, Dictionary <int, Affectation> > dupli = new Dictionary <int, Dictionary <int, Affectation> >();
foreach (var a in affectations)
{
//Etape 1 : erreur
if (!dispoes.ContainsKey(a.BenevoleId))
{
a.Status = AffectationStatusEnum.NonDisponible;
}
else
{
var s = dispoes[a.BenevoleId];
if (!s.Contains(a.Creneau.CreneauDefId))
{
a.Status = AffectationStatusEnum.NonDisponible;
}
else
{
if (!dupli.ContainsKey(a.Creneau.CreneauDefId))
{
dupli.Add(a.Creneau.CreneauDefId, new Dictionary <int, Affectation>());
}
var duplic = dupli[a.Creneau.CreneauDefId];
if (duplic.ContainsKey(a.BenevoleId))
{
duplic[a.BenevoleId].Status = AffectationStatusEnum.Duplique;
a.Status = AffectationStatusEnum.Duplique;
}
else
{
duplic.Add(a.BenevoleId, a);
}
}
}
//Etape 2 : appreciation
if (a.Status == AffectationStatusEnum.Unknown)
{
if (prefs.ContainsKey(a.BenevoleId))
{
a.Status = AffectationStatusEnum.Correct;
if (prefs[a.BenevoleId].ContainsKey(a.Creneau.Tache.TypeTacheId))
{
var p = prefs[a.BenevoleId][a.Creneau.Tache.TypeTacheId];
if (p.Valeur > 0)
{
a.Status = AffectationStatusEnum.Souhaite;
}
else if (p.Valeur < 0)
{
a.Status = AffectationStatusEnum.NonSouhaite;
}
}
}
}
}
}
