public function commande($dates)
{
ini_set('max_execution_time', 500);
if (date('n') <= 6) {
$annee = date('Y', strtotime('-1 year', strtotime(date('Y'))));
} else {
$annee = date('Y');
}
$tabVacances = Feries::chargement();
//ajouter une request ! ou gestion des dates
//$dates = "2015-09-1";
$dates = date('Y-m-d', strtotime($dates));
$semaine = [$dates, date('Y-m-d', strtotime(date('Y-m-d', strtotime($dates)) . "+4 day"))];
for ($i = 1; $i <= 5; $i++) {
$jour = date('Y-m-d', strtotime(date('Y-m-d', strtotime($dates)) . "+" . ($i - 1) . " day"));
//dd(Feries::est_vacances($jour, $tabVacances), $jour);
$data[$i]['primaire']['total'] = !Feries::est_ferie($jour) && !Feries::est_vacances($jour, $tabVacances) ? 1 : 0;
$data[$i]['primaire']['fixe'] = !Feries::est_ferie($jour) && !Feries::est_vacances($jour, $tabVacances) ? 1 : 0;
$data[$i]['primaire']['exceptionnelle'] = 0;
$data[$i]['primaire']['porc'] = 0;
$data[$i]['primaire']['viande'] = 0;
$data[$i]['maternelle']['total'] = !Feries::est_ferie($jour) && !Feries::est_vacances($jour, $tabVacances) ? 1 : 0;
$data[$i]['maternelle']['fixe'] = !Feries::est_ferie($jour) && !Feries::est_vacances($jour, $tabVacances) ? 1 : 0;
$data[$i]['maternelle']['exceptionnelle'] = 0;
$data[$i]['maternelle']['porc'] = 0;
$data[$i]['maternelle']['viande'] = 0;
$data[$i]['adulte']['total'] = 0;
$data[$i]['adulte']['instituteur'] = 0;
$data[$i]['adulte']['personnelle'] = 0;
$data[$i]['adulte']['fixe'] = 0;
$data[$i]['adulte']['exceptionnelle'] = 0;
$data[$i]['adulte']['porc'] = 0;
$data[$i]['adulte']['viande'] = 0;
}
$data[3]['maternelle']['total'] = 0;
$data[3]['maternelle']['fixe'] = 0;
$maternelle_mercredi['total'] = 0;
$maternelle_mercredi['porc'] = 0;
$maternelle_mercredi['viande'] = 0;
$facturation = [];
$regs = Reguliere::with('enfant', 'enfant.classe')->where('type', 'cantine')->get();
foreach ($regs as $reg) {
//dd($reg->enfant);
if ($reg->enfant->exception_autre != 'non' && $reg->enfant->exception_autre != '') {
$data['eviction'][strtolower($reg->enfant->classe->cycle)][$reg->enfant->id]['enfant'] = $reg->enfant;
$data['eviction'][strtolower($reg->enfant->classe->cycle)][$reg->enfant->id]['jour'] = str_split($reg->jours);
}
$jours = str_split($reg->jours);
for ($i = 1; $i <= 5; $i++) {
//ajouter test vacance et jour ferie
$jour = date('Y-m-d', strtotime(date('Y-m-d', strtotime($dates)) . "+" . ($i - 1) . " day"));
if (!Feries::est_ferie($jour) && !Feries::est_vacances($jour, $tabVacances)) {
if (in_array($i, $jours)) {
$cycle = strtolower($reg->enfant->classe->cycle);
$data[$i][$cycle]['fixe']++;
if (isset($facturation[$reg->enfant_id]['fixe'])) {
$facturation[$reg->enfant_id]['fixe']++;
} else {
$facturation[$reg->enfant_id]['fixe'] = 1;
}
/*if($reg->enfant->classe->cycle == 'adulte'){
$data[$i]['adulte'][$reg->enfant->classe->niveau] ++;
}*/
if ($i == 3 && $cycle == 'maternelle') {
/*$maternelle_mercredi['total']++;
if($reg->enfant->exception_porc){
$maternelle_mercredi['porc']++;
}
if($reg->enfant->exception_viande){
$maternelle_mercredi['viande']++;
}*/
$cycle = 'primaire';
}
$data[$i][$cycle]['total']++;
if ($reg->enfant->exception_porc) {
$data[$i][$cycle]['porc']++;
}
if ($reg->enfant->exception_viande) {
$data[$i][$cycle]['viande']++;
}
}
}
}
}
$exeps = Exceptionnelle::whereBetween('jour', $semaine)->with('enfant', 'enfant.classe')->where('type', 'cantine')->get();
//dd($exeps);
foreach ($exeps as $exep) {
$jour = date('N', strtotime($exep->jour));
if (!Feries::est_ferie($exep->jour) && !Feries::est_vacances($exep->jour, $tabVacances)) {
$cycle = strtolower($exep->enfant->classe->cycle);
//if($exep->inscrit == 0){// Fred : 13/12/15 correction de bug propagée, mettre entre ' les comparaisons d'inscrits
if ($exep->inscrit === '0') {
$ajout = -1;
$facturation[$exep->enfant_id]['fixe']--;
/*if($jour != 3 || $cycle != 'maternelle'){
$data[$jour][$cycle]['total']--;
}*/
if (isset($facturation[$exep->enfant_id]['abscence_signalee'])) {
$facturation[$exep->enfant_id]['abscence_signalee']++;
} else {
$facturation[$exep->enfant_id]['abscence_signalee'] = 1;
}
} else {
$ajout = 1;
$data[$jour][$cycle]['exceptionnelle']++;
if (isset($facturation[$exep->enfant_id]['exceptionnelle'])) {
$facturation[$exep->enfant_id]['exceptionnelle']++;
} else {
$facturation[$exep->enfant_id]['exceptionnelle'] = 1;
}
}
$data[$jour][$cycle]['fixe'] += $ajout;
if ($exep->enfant->exception_autre != "non" && $exep->enfant->exception_autre != "") {
if ($ajout == 1) {
$data['eviction'][$cycle][$exep->enfant->id]['jour'][] = date('N', strtotime($exep->jour));
if (!isset($data['eviction'][$cycle][$exep->enfant->id]['enfant'])) {
$data['eviction'][$cycle][$exep->enfant->id]['enfant'] = $exep->enfant;
}
} else {
//dd($data['eviction'][$cycle]);
$cle = array_search(date('N', strtotime($exep->jour)), $data['eviction'][$cycle][$exep->enfant->id]['jour']);
if ($cle !== null) {
unset($data['eviction'][$cycle][$exep->enfant->id]['jour'][$cle]);
//dd($data['eviction'][$cycle][$exep->enfant->id]['jour']);
}
if (empty($data['eviction'][$cycle][$exep->enfant->id]['jour'])) {
unset($data['eviction'][$cycle][$exep->enfant->id]);
}
}
}
if ($jour == 3 && $cycle == 'maternelle') {
/*$maternelle_mercredi['total']+=$ajout;
if($exep->enfant->exception_porc){
$maternelle_mercredi['porc']+=$ajout;
}
if($exep->enfant->exception_viande){
$maternelle_mercredi['viande']+=$ajout;
}*/
$cycle = 'primaire';
}
if ($exep->enfant->classe->cycle == 'adulte') {
$data[$jour]['adulte'][$exep->enfant->classe->niveau] += $ajout;
}
$data[$jour][$cycle]['total'] += $ajout;
if ($exep->enfant->exception_porc) {
$data[$jour][$cycle]['porc'] += $ajout;
}
if ($exep->enfant->exception_viande) {
$data[$jour][$cycle]['viande'] += $ajout;
}
}
}
$ancienne = Facturation::where('semaine', $dates)->delete();
foreach ($facturation as $id => $f) {
$fact = new Facturation();
$fact->semaine = $dates;
$fact->enfant_id = $id;
$fact->fixe = isset($f['fixe']) ? $f['fixe'] : 0;
$fact->non_fixe = isset($f['exceptionnelle']) ? $f['exceptionnelle'] : 0;
$fact->abscence_signalee = isset($f['abscence_signalee']) ? $f['abscence_signalee'] : 0;
$fact->save();
}
//Les adultes mangent des repas enfant ...
for ($i = 1; $i <= 5; $i++) {
$data[$i]['primaire']['total'] += $data[$i]['adulte']['total'];
$data[$i]['primaire']['fixe'] += $data[$i]['adulte']['fixe'];
$data[$i]['primaire']['exceptionnelle'] += $data[$i]['adulte']['exceptionnelle'];
$data[$i]['primaire']['porc'] += $data[$i]['adulte']['porc'];
$data[$i]['primaire']['viande'] += $data[$i]['adulte']['viande'];
}
//dd($data);
Excel::load('fichiers/test.xlsx', function ($file) use($data, $maternelle_mercredi, $semaine) {
$file->setFileName('commande_' . date('d/m/Y', strtotime($semaine[0])));
$sheets = [0 => 'primaire', 1 => 'maternelle'];
foreach ($sheets as $nbsheet => $sheet) {
$lignes = [14 => 'total', 15 => 'porc', 16 => 'viande'];
foreach ($lignes as $key => $ligne) {
$type = $ligne;
$total = 0;
for ($i = 1; $i <= 5; $i++) {
$cellule = chr(($i - 1) * 4 + 1 + ord('A')) . $key;
$file->setActiveSheetIndex($nbsheet)->setCellValue($cellule, $data[$i][$sheet][$type]);
$total += $data[$i][$sheet][$type];
}
// Remplissage dates et total
$file->setActiveSheetIndex($nbsheet)->setCellValue('V' . $key, $total);
$file->setActiveSheetIndex($nbsheet)->setCellValue('E4', date('d/m/Y'));
$file->setActiveSheetIndex($nbsheet)->setCellValue('M9', 'S' . date('W'));
$file->setActiveSheetIndex($nbsheet)->setCellValue('O9', date('d/m/Y', strtotime($semaine[0])));
$file->setActiveSheetIndex($nbsheet)->setCellValue('R9', date('d/m/Y', strtotime($semaine[1])));
//remplissage evictions en bas
$table_jours = ['lundi', 'mardi', 'mercredi', 'jeudi', 'vendredi'];
$ligne = 21;
//dd($data['eviction']);
if (isset($data['eviction']) && isset($data['eviction'][$sheet])) {
foreach ($data['eviction'][$sheet] as $eviction) {
$file->setActiveSheetIndex($nbsheet)->setCellValue('A' . $ligne, $eviction['enfant']->nom);
$file->setActiveSheetIndex($nbsheet)->setCellValue('C' . $ligne, $eviction['enfant']->prenom);
$file->setActiveSheetIndex($nbsheet)->setCellValue('F' . $ligne, $eviction['enfant']->exception_autre);
$jour_exep = '';
foreach ($eviction['jour'] as $j) {
if ($j != "") {
$jour_exep .= $table_jours[$j - 1] . ' ';
}
}
$file->setActiveSheetIndex($nbsheet)->setCellValue('P' . $ligne, $jour_exep);
$ligne++;
}
}
}
}
//feuille recap
$file->setActiveSheetIndex(2)->setCellValue('A1', 'Commande repas S' . date('W', strtotime($semaine[0])));
$file->setActiveSheetIndex(2)->setCellValue('B3', date('d/m/Y', strtotime($semaine[0])));
$file->setActiveSheetIndex(2)->setCellValue('D3', date('d/m/Y', strtotime($semaine[1])));
$file->setActiveSheetIndex(2)->setCellValue('B15', date('d/m/Y'));
for ($i = 1; $i <= 5; $i++) {
$total = $data[$i]['primaire']['fixe'] + $data[$i]['maternelle']['fixe'] + $data[$i]['primaire']['exceptionnelle'] + $data[$i]['maternelle']['exceptionnelle'];
$file->setActiveSheetIndex(2)->setCellValue(chr($i + 65) . '7', $data[$i]['primaire']['fixe'] - $data[$i]['adulte']['fixe']);
$file->setActiveSheetIndex(2)->setCellValue(chr($i + 65) . '6', $data[$i]['maternelle']['fixe']);
//ajouter prof et personnelle
$file->setActiveSheetIndex(2)->setCellValue(chr($i + 65) . '8', $data[$i]['adulte']['instituteur']);
$file->setActiveSheetIndex(2)->setCellValue(chr($i + 65) . '9', $data[$i]['adulte']['personnelle']);
$file->setActiveSheetIndex(2)->setCellValue(chr($i + 65) . '10', $data[$i]['primaire']['exceptionnelle'] + $data[$i]['maternelle']['exceptionnelle']);
//dd('=Somme('.chr($i+65).'6:'.chr($i+65).'10)');
$file->setActiveSheetIndex(2)->setCellValue(chr($i + 65) . '11', $total);
}
})->export('xls');
}
