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'); }