vendredi 26 février 2016

Creating a spreadsheet like graphical overview of bookings in laravel

I have created a booking webbapp for my company, and I'm now trying to make a spreadsheet like graphical overview so as to easily see when equipment is booked and when its free.

Ive been stuck on this for quite some time, so the code is a bit messy, i tried to clean it up before posting here, so hopefully you can read and understand, if something is unclear, drop a comment and i will try to clear things up. My code works if you remove booking 58071(the green one) and change @for ($x=0; $x < $book->units; $x++) to @for ($i=0; $i < $book->units; $i++) the problem appears when you have multiple bookings that are not during the same time.

This is what I've got so far:Not good

And this is what happens if i change $x to $i in the forloop without removing booking 58071change x to i

And this is what i would like to achieve: Perfect!

And code wise, this is the Controller:

 public function availibleItems($id = null, $date = null)
{
    if (!isset($date)) {
        $date = \Carbon\Carbon::now();
    } else {
        $date = \Carbon\Carbon::createFromFormat('Y-m-d', $date);
    }

    $oneMonth = [$date->toDateString()];
    for ($i=0; $i < 30; $i++) {
        array_push($oneMonth, $date->addDay('1')->toDateString());
    }
    $firstDate = array_first($oneMonth, function ($key, $value) {
        return $value;
    });
    $lastDate = array_last($oneMonth, function ($key, $value) {
        return $value;
    });

    $items = Inventory::where('group_type_id', $id)->with(['items' => function ($q) {
        $q->where('invisible', 0);
    }])->get();
    $bookedItems = BookingDetails::whereHas('group', function ($q) use ($id) {
        $q->where('group_type_id', 'like', $id);
    })->whereBetween('delivery_date', [$firstDate, $lastDate])->whereBetween('return_date', [$firstDate, $lastDate])
    ->orWhere(function ($q) use ($firstDate, $lastDate, $id) {
        $q->where('delivery_date', '<=', $firstDate)->where('return_date', '>=', $lastDate)
          ->whereHas('group', function ($q) use ($id) {
            $q->where('group_type_id', 'like', $id);
          });
    })->orWhere(function ($q) use ($firstDate, $lastDate, $id) {
        $q->whereBetween('delivery_date', [$firstDate, $lastDate])->where('return_date', '>=', $lastDate)
        ->whereHas('group', function ($q) use ($id) {
            $q->where('group_type_id', 'like', $id);
        });
    })->orWhere(function ($q) use ($firstDate, $lastDate, $id) {
        $q->whereBetween('return_date', [$firstDate, $lastDate])->where('delivery_date', '<=', $lastDate)
        ->whereHas('group', function ($q) use ($id) {
            $q->where('group_type_id', 'like', $id);
        });
    })->get();
    $bookingCount = $bookedItems->count();
    return view('planner.availibleItems')->with('oneMonth', $oneMonth)->with('items', $items)->with('bookedItems', $bookedItems)->with('bookingCount', $bookingCount);
}

And this is the View:

    <div class="row fullwidth">
    <div class="large-12 columns">
        <div class="table-wrapper">
            <div class="scrollable">
                <table class="responsive">
                    <thead>
                        <tr>
                            <th>GroupNr</th>
                            <th>Article</th>
                            @foreach ($oneMonth as $date)
                                <th>{{ $date }}</th>
                            @endforeach
                        </tr>
                    </thead>
                    <tbody>
                        @foreach ($items as $item)
                            {{--*/ $itemCount = $item->items()->where('invisible', 0)->count() /*--}}
                            {{--*/ $bookedItemsForOrderCount = $bookedItems->where('article_group', $item->article_group_id)->sum('units') /*--}}
                            @for ($i = 0; $i < $bookedItemsForOrderCount ; $i++)
                                @foreach ($bookedItems as $book)
                                    @if($item->article_group_id == $book->article_group && $book->units > $i)
                                        @for ($x=0; $x < $book->units; $x++)
                                            <tr>
                                                <td>{{ $item->article_group_id }}</td>
                                                <td>{{ $item->article }}</td>
                                                @foreach ($oneMonth as $date)
                                                    @if ($date >= $book->delivery_date &&
                                                         $date <= $book->return_date && 
                                                         $item->article_group_id == $book->article_group &&
                                                         $book->units > $i) 
                                                         <td @if($book->booking->ordertype_id == 3)
                                                                bgcolor="red"
                                                             @elseif($book->booking->ordertype_id == 2)
                                                                bgcolor="lightgreen"
                                                             @elseif($book->booking->ordertype_id == 1)
                                                                bgcolor="gray"
                                                             @endif> @if($date == $book->delivery_date) {{$book->order_id }} @endif</td>
                                                    @elseif ($date < $book->delivery_date && $item->article_group_id == $book->article_group)
                                                         <td></td>
                                                    @elseif ($date > $book->return_date && $item->article_group_id == $book->article_group)
                                                        <td></td>
                                                    @elseif ($item->article_group_id != $book->article_group)
                                                        <td>hello</td>
                                                    @endif 
                                                @endforeach
                                            </tr>
                                        @endfor
                                    @endif
                                @endforeach
                            @endfor
                            @for ($i = $bookedItemsForOrderCount; $i < $itemCount ; $i++)
                                <tr>
                                    <td>{{ $item->article_group_id }}</td>
                                    <td>{{ $item->article }}</td>
                                    @foreach ($oneMonth as $date)
                                    <td></td>
                                    @endforeach
                                </tr>
                            @endfor
                            <tr>
                            <td>---------</td>
                            <td>---------</td>
                            @foreach ($oneMonth as $date)
                                <td>---------</td>
                            @endforeach
                            </tr>
                        @endforeach
                    </tbody>
                </table>
            </div>
            <div class="pinned">
                <table>
                    <thead>
                        <tr>
                            <th>GroupNr</th>
                            <th>Article</th>
                        </tr>
                    </thead>
                    <tbody>
                    @foreach ($items as $item)

                    @foreach ($item->items as $inv)
                            <tr>
                                <td>{{ $item->article_group_id .' | ' .$inv->group_item_id }}</td>
                                <td>{{ $item->article }}</td>
                            </tr>

                        @endforeach
                        <tr><td colspan="2" height="22px"> |-----------------------------------------------------| </td></tr>
                        @endforeach
                    </tbody>
                </table>
            </div>  
        </div>
    </div>
</div>

Here is a ddump of the collections "$items" and "$bookedItems" $items $bookedItems

Wow, this post turned out really long, hopefully the pictures illustrates the problem well enough.

Many thanks in advance!



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire