public function facturation(Request $request)
{
$data = $request->all();
$mois = date('Y-m-d', strtotime($data['mois']));
$fin = date('Y-m-d', strtotime('+2 month', strtotime($mois)));
$facturations = Facturation::with('enfant', 'enfant.classe')->whereBetween('semaine', [$mois, $fin])->orderBy('enfant_id')->get();
//dd($facturations, $mois, $fin);
Excel::load('fichiers/facturation.xlsx', function ($file) use($mois, $fin, $facturations, $data) {
$file->setFileName('facturation_' . Feries::Mois(date('n', strtotime('+10day' . $mois))) . '_' . Feries::Mois(date('n', strtotime('+1month' . $mois))));
$file->setActiveSheetIndex(0)->setCellValue('E3', date('d/m/Y', strtotime($mois)));
$file->setActiveSheetIndex(0)->setCellValue('E4', date('d/m/Y', strtotime($fin)));
$file->setActiveSheetIndex(0)->setCellValue('E6', $data['prix_elementaire']);
$file->setActiveSheetIndex(0)->setCellValue('D6', $data['prix_maternelle']);
$ligne = 10;
$id = 0;
$total = 0;
$colonne = 'G';
foreach ($facturations as $facturation) {
if ($id != $facturation->enfant_id) {
if ($total != 0) {
$file->setActiveSheetIndex(0)->setCellValue('E' . $ligne, $total);
if ($last->enfant->classe->cycle == 'Maternelle') {
$file->setActiveSheetIndex(0)->setCellValue('F' . $ligne, $total * $data['prix_maternelle']);
//dd($facturation, $total, 'F'.$ligne, $data['prix_maternelle']);
} else {
$file->setActiveSheetIndex(0)->setCellValue('F' . $ligne, $total * $data['prix_elementaire']);
}
$total = 0;
}
$ligne++;
//$maDate = $mois;
$colonne = 'G';
$file->setActiveSheetIndex(0)->setCellValue('A' . $ligne, $facturation->enfant->classe->niveau);
$file->setActiveSheetIndex(0)->setCellValue('B' . $ligne, $facturation->enfant->classe->instituteur);
$file->setActiveSheetIndex(0)->setCellValue('C' . $ligne, $facturation->enfant->nom);
$file->setActiveSheetIndex(0)->setCellValue('D' . $ligne, $facturation->enfant->prenom);
$id = $facturation->enfant_id;
//ici prix
}
$maDate = $mois;
while ($maDate < $facturation->semaine) {
$maDate = date('Y-m-d', strtotime('+7day' . $maDate));
$colonne = chr(ord($colonne) + 3);
}
$total += $facturation->fixe + $facturation->non_fixe;
$file->setActiveSheetIndex(0)->setCellValue($colonne . $ligne, $facturation->fixe + $facturation->non_fixe);
$file->setActiveSheetIndex(0)->setCellValue(chr(ord($colonne) + 1) . $ligne, 0);
$file->setActiveSheetIndex(0)->setCellValue(chr(ord($colonne) + 2) . $ligne, $facturation->abscence_signalee);
$colonne = 'G';
$last = $facturation;
}
$file->setActiveSheetIndex(0)->setCellValue('E' . $ligne, $total);
if (isset($last)) {
if ($last->enfant->classe->cycle == 'maternelle') {
$file->setActiveSheetIndex(0)->setCellValue('F' . $ligne, $total * $data['prix_maternelle']);
} else {
$file->setActiveSheetIndex(0)->setCellValue('F' . $ligne, $total * $data['prix_elementaire']);
}
}
})->export('xls');
}
public function supprimerBase()
{
$enfants = Enfant::orderBy('classe_id')->with('users')->with('classe')->with('arret')->with('regulieres')->get();
$enfants_parents[0] = ['Classe', 'Nbre élèves', 'Commune', 'Nom', 'Prénom', 'Instituteur', 'CANTINE', 'GARDERIE', 'TRANSPORT', 'AUTORISATION', 'arret', 'porc', 'viande', 'PAI', 'nb_parent', 'nom1', 'prenom1', 'adresse1', 'nom2', 'prenom2', 'adresse2'];
$cantine[0] = ['Nom', 'Prenom', 'Lundi', 'Mardi', 'Mercredi', 'Jeudi', 'Vendredi', 'Jour_Debut'];
$bus[0] = ['Nom', 'Prenom', 'Lundi_matin', 'Lundi_soir', 'Mardi_matin', 'Mardi_soir', 'Mercredi_matin', 'Mercredi_soir', 'Jeudi_ matin', 'Jeudi_soir', 'Vendredi_matin', 'Vendredi_soir', 'Jour_Debut', 'Responsable1', 'Responsable2', 'Responsable3', 'Responsable4', 'Responsable5'];
foreach ($enfants as $enfant) {
$parents = $enfant->users()->get();
$nb_parent = $parents->count();
$enfants_parents[$enfant->id] = [$enfant->niveau_classe, '', '', $enfant->nom, $enfant->prenom, $enfant->classe != null ? $enfant->classe->instituteur : '', $enfant->mange_cantine ? 'OUI' : 'NON', $enfant->garderie ? 'OUI' : 'NON', $enfant->prendre_bus ? 'OUI' : 'NON', $enfant->rentre_seul ? 'OUI' : 'NON', $enfant->prendre_bus && $enfant->arret != null ? $enfant->arret->nom : '', $enfant->exception_porc ? 'OUI' : 'NON', $enfant->exception_viande ? 'OUI' : 'NON', $enfant->exception_autre, $nb_parent];
$regs = $enfant->regulieres()->get();
$bus[$enfant->id] = [$enfant->nom, $enfant->prenom];
$bus[$enfant->id][13] = $enfant->responsable_bus;
$cantine[$enfant->id] = [$enfant->nom, $enfant->prenom];
foreach ($regs as $reg) {
$jours = str_split($reg->jours);
for ($i = 1; $i <= 5; $i++) {
$type = $reg->type;
if (in_array($i, $jours)) {
if ($type == 'cantine') {
$cantine[$enfant->id][$i + 1] = 'x';
} else {
if ($type == 'bus_matin') {
//dd($jours, $enfant, $reg, $i*2);
$bus[$enfant->id][$i * 2] = 'x';
} else {
//dd(($i+1)*2+2,$enfant);
$bus[$enfant->id][$i * 2 + 1] = 'x';
}
}
} else {
if ($type == 'cantine') {
$cantine[$enfant->id][$i + 1] = ' ';
} else {
if ($type == 'bus_matin') {
//dd($jours, $enfant, $reg, $i*2);
$bus[$enfant->id][$i * 2] = ' ';
} else {
//dd(($i+1)*2+2,$enfant);
$bus[$enfant->id][$i * 2 + 1] = ' ';
}
}
}
}
}
foreach ($parents as $p) {
$enfants_parents[$enfant->id][] = $p->nom;
$enfants_parents[$enfant->id][] = $p->prenom;
$enfants_parents[$enfant->id][] = $p->email;
}
ksort($bus[$enfant->id]);
//dd($bus[$enfant->id]);
}
$classes = Classe::where('cycle', '<>', 'adulte')->get();
$classe[0] = ['Classe', 'cycle', 'instituteur'];
foreach ($classes as $c) {
$classe[$c->id] = [$c->niveau, $c->cycle, $c->instituteur];
}
$arrets = Arret::with('ligne')->get();
$arret[0] = ['Ligne', 'Arret', 'Numero', 'Commune'];
foreach ($arrets as $a) {
$arret[$a->id] = [$a->ligne->nom, $a->nom, $a->numero_arret, $a->commune];
}
DB::statement("SET foreign_key_checks=0");
Arret::truncate();
Ligne::truncate();
Exceptionnelle::truncate();
Reguliere::truncate();
$classe = Classe::where('cycle', '<>', 'adulte')->lists('id');
Classe::destroy($classe);
Facturation::truncate();
$users = User::where('niveau', 0)->lists('id');
User::destroy($users);
Enfant::truncate();
DB::table('enfant_user')->truncate();
DB::statement("SET foreign_key_checks=1");
//dd($bus);
Excel::create('fin_annee' . date('Y'), function ($file) use($enfants_parents, $cantine, $bus, $classe, $arret) {
$file->sheet('Classes', function ($sheet) use($classe) {
$sheet->fromArray($classe, null, 'A1', false, false);
});
$file->sheet('Sivusem', function ($sheet) use($enfants_parents) {
$sheet->fromArray($enfants_parents, null, 'A1', false, false);
});
$file->sheet('Lignes', function ($sheet) use($arret) {
$sheet->fromArray($arret, null, 'A1', false, false);
});
$file->sheet('Cantine', function ($sheet) use($cantine) {
$sheet->fromArray($cantine, null, 'A1', false, false);
});
$file->sheet('Bus', function ($sheet) use($bus) {
$sheet->fromArray($bus, null, 'A1', false, false);
});
})->export('xls');
}
