Luciano
10/7/2019 - 7:36 PM

Invoice Controller for Laravel 5.X

<?php

namespace App\Http\Controllers;

use App\Formality;
use App\FormalityDetail;
use App\Payment;
use App\Book;

use DB;
use Illuminate\Http\Request;
use App\Services\FormalityService;

use App\Http\Requests\StoreInvoice;
use App\Http\Requests\UpdateInvoice;

class InvoiceController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        //
        $paging = $request->page;
        $limit = $request->limit;
        $sorting = $request->orderBy;
        $sortorder = $request->ascending;
        $offset = $limit * ($paging - 1);

        $elo_sql =  Formality::select('*')
                    ->where('formalities.group_id', config('settings.groups.invoice_id'));

        //*******************************************************
        // FILTERS
        //*******************************************************
        if ($request->has('text')) {
            $text =  $request->text;
            $elo_sql = $elo_sql->where(function ($query) use ($text) {

                foreach ( explode(' ', $text) as $key => $item) {
                    $query->where(function ($q2) use($item){

                        $q2->orWhere('number', 'like', '%' . ltrim($item, '0') . '%')
                        ->orWhere('customer_name', 'like', '%' . ltrim($item, '0') . '%');
                    });
                }
            });
        }

        if ($request->has('status')) {
            $elo_sql->whereIn('status_id', $request->status);
        }

        if ($request->has('book')) {
            $elo_sql->whereIn('book_id', $request->book);
        }

        if ($request->has('customer')) {
            $elo_sql->where('customer_id', $request->customer);
        }

        if ($request->has('date') && $request->date) {
            $dates = explode('a', $request->date);

            if (count($dates)>1){
                $elo_sql->whereBetween('date',$dates);
            } else {
                $elo_sql->where('date', $dates);
            }
        }

        if ($request->has('total_pending')) {
            $request->total_pending == 1 ? $elo_sql->where('total_pending', '>', 0) : $elo_sql->where('total_pending', 0);
        }

        $count = $elo_sql->count();
        $elo_sql = $elo_sql->orderBy('id','DESC')->limit($limit)->offset($offset);

        //*******************************************************
        // QUERY RESOURCES
        //*******************************************************
        $resources = DB::table( DB::raw("({$elo_sql->toSql()}) as f") )->mergeBindings($elo_sql->getQuery())
                    ->select(
                        'f.id',
                        DB::Raw('CONCAT_WS(" ", f.shortname, f.letter, LPAD(f.sales_point,4,0), LPAD(f.number,6,0)) AS number'),
                        'f.date',
                        'f.customer_name AS customer',
                        'f.customer_id',
                        'f.status_id',
                        'f.group_id',
                        'formality_statuses.name AS status',
                        'f.total',
                        'f.total_pending',
                        'books.electronic'
                    )
                    ->join('formality_statuses', 'f.status_id', 'formality_statuses.id')
                    ->join('books', 'f.book_id', 'books.id')
                    ->orderBy('f.id', 'DESC')
                    ->get();

        $data = [
            'data' => array_values($resources->toArray()),
            'count' => $count
        ];

        return $data;
    }


    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(StoreInvoice $request, FormalityService $formality_service)
    {
        //

        DB::beginTransaction();

        try {
            $total = 0;

            $details  = $request->details;
            $formalities   = $request->formalities;
            $concepts = $request->concepts;
            $discounts = $request->discounts;
            $custom_discounts = $request->custom_discounts;

            $formality_details = array();

            //***************************************************************************
            // FORMALITIES
            //***************************************************************************
            foreach ($formalities as $formality) {
                $formality = (object)$formality;

                $formality_details[] = [
                    'details' => $formality->number,
                    'amount' => $formality->amount,
                    'linked_formality_id' => $formality->id,
                ];

                // Update formality total_pending
                $order_to_update = Formality::findOrFail($formality->id);
                $order_to_update->total_pending = $order_to_update->total_pending - $formality->amount;
                $order_to_update->save();

                // Sum total invoice
                $total += $formality->amount;
            }

            //***************************************************************************
            // CONCEPTS
            //***************************************************************************
            foreach ($concepts as $concept) {
                $concept = (object)$concept;

                $formality_details[] = [
                    'details' => $concept->details,
                    'amount' => $concept->amount
                ];

                // Sum total invoice
                $total += $concept->amount;
            }

            //***************************************************************************
            // CUSTOM DISCOUNTS
            //***************************************************************************
            foreach ($custom_discounts as $discount) {
                $discount = (object)$discount;

                $formality_details[] = [
                    'details' => $discount->details,
                    'amount' => $discount->amount,
                    'credit' => 1
                ];

                // Sum total invoice
                $total -= $discount->amount;
            }

            //***************************************************************************
            // DISCOUNTS
            //***************************************************************************
            foreach ($discounts as $discount) {
                $discount = (object)$discount;

                $formality_details[] = [
                    'details' => $discount->number,
                    'amount' => $discount->amount,
                    'linked_formality_id' => $discount->id,
                    'credit' => 1
                ];

                // Update discount (Receipt) total_pending
                $discount_obj = Formality::findOrFail($discount->id);
                $discount_obj->total_pending = $discount_obj->total_pending - $discount->amount;
                $discount_obj->save();

                // Substract total invoice
                $total -= $discount->amount;
            }

            // Add IVA
            $total_iva = $total * $details['iva_value']['value'] / 100;
            $total += $total_iva;

            // Prepare Header and send to service
            $details =  array_merge($request->details, [
                            'book_id' => $details['book']['id'],
                            'customer_doc_type_id' => $details['customer_doc_type']['id'] ?? null,
                            'customer_iva_condition_id' => $details['customer_iva_condition']['id'] ?? null,
                            'total'   => $total,
                            'total_iva'   => $total_iva,
                            'status_id' => 6 // Activo
                        ]);

            $invoice = $formality_service->storeHeader($details);
            $invoice->details()->createMany($formality_details);

            DB::commit();

            return response()->json([
                'message' => 'Factura generada correctamente',
                'invoice' => $invoice
            ]);

        } catch (\Exception $e) {

            DB::rollback();

            return response()->json([
                //'message' => 'Fallo al generar la Nota de Crédito',
                'message' => $e->getMessage(),
            ], 500);
        }

    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
        $invoice = Formality::select(
                            'formalities.id',
                            DB::Raw('CONCAT_WS(" ", formalities.shortname, formalities.letter, LPAD(formalities.sales_point,4,0), LPAD(formalities.number,6,0)) AS number'),
                            'formalities.date',
                            'formalities.iva_value_id',
                            'formalities.total_iva',
                            'formalities.total',
                            'formalities.customer_name',
                            'formalities.group_id',
                            'formalities.status_id',
                            'customers.id AS customer_id',
                            DB::Raw('CONCAT_WS(" ",book_types.name,book_types.letter,LPAD(books.sales_point,4,"0")) AS book'),
                            'iva_conditions.name AS customer_iva_condition',
                            DB::Raw('CONCAT_WS(" ", document_types.name, formalities.customer_doc_number) AS customer_doc_number'),
                            'books.electronic'
                        )
                        ->join('formality_statuses','formalities.status_id','formality_statuses.id')
                        ->join('books','formalities.book_id','books.id')
                        ->join('book_types','books.type_id','book_types.id')
                        ->leftJoin('document_types','formalities.customer_doc_type_id','document_types.id')
                        ->leftJoin('iva_conditions','formalities.customer_iva_condition_id','iva_conditions.id')
                        ->leftJoin('customers','formalities.customer_id','customers.id')
                        ->findOrFail($id);

        $formalities = Formality::select(
                            'formalities.id',
                            'formalities.number',
                            'formalities.date',
                            'formalities.expiration_date',
                            'formalities.title',
                            'formalities.total',
                            'formalities.group_id',
                            'formality_details.amount AS amount',
                            DB::Raw('formalities.total_pending + formality_details.amount AS max_imputable'),
                            'formality_statuses.name AS status',
                            'formality_statuses.id AS status_id',
                            'order_categories.name AS category',
                            'formalities.customer_name AS customer',
                            'formalities.total_pending AS total_pending',
                            DB::Raw('CONCAT_WS(" ", formalities.shortname, LPAD(formalities.number,6,0)) AS number'),
                            'formalities.customer_name',
                            'formalities.customer_id'
                        )
                        ->join('formality_details', function($join) {
                            $join->on('formalities.id', '=', 'formality_details.linked_formality_id')
                            ->where('formality_details.credit', 0);
                        })
                        ->join('order_categories','formalities.category_id','order_categories.id')
                        ->join('formality_statuses','formalities.status_id','formality_statuses.id')
                        ->where('formality_details.formality_id', $invoice->id)
                        ->whereNotNull('formality_details.linked_formality_id')
                        ->get();

        $concepts = FormalityDetail::select(
                            'id',
                            'details',
                            'amount'
                        )
                        ->where('formality_details.formality_id', $invoice->id)
                        ->where('formality_details.credit', 0)
                        ->whereNull('formality_details.linked_formality_id')
                        ->get();

        // Get receipts imputed because on_account
        $discounts = Formality::select(
                            'formalities.id',
                            'formalities.number',
                            'formalities.date',
                            'formalities.total',
                            'formalities.group_id',
                            'formality_details.amount AS amount',
                            DB::Raw('formalities.total_pending + formality_details.amount AS max_imputable'),
                            'formality_statuses.name AS status',
                            'formality_statuses.id AS status_id',
                            'formalities.customer_name AS customer',
                            DB::Raw('IFNULL((formalities.total_pending + formality_details.amount ),0) AS total_pending'),
                            DB::Raw('CONCAT_WS(" ", formalities.shortname, LPAD(formalities.number,6,0)) AS number'),
                            'formalities.customer_name',
                            'formalities.customer_id'
                        )
                        ->join('formality_details', function($join) {
                            $join->on('formalities.id', '=', 'formality_details.linked_formality_id')
                            ->where('formality_details.credit', 1);
                        })
                        ->join('formality_statuses','formalities.status_id','formality_statuses.id')
                        ->where('formality_details.formality_id', $invoice->id)
                        ->where('formalities.group_id', config('settings.groups.receipt_id') )
                        ->whereNotNull('formality_details.linked_formality_id')
                        ->get();

        $custom_discounts = FormalityDetail::select(
                            'id',
                            'details',
                            'amount'
                        )
                        ->where('formality_details.formality_id', $invoice->id)
                        ->where('formality_details.credit', 1)
                        ->whereNull('formality_details.linked_formality_id')
                        ->get();

        // To display if invoice is already imputed in a receipt
        $parents = Formality::select(
                                    'formalities.id',
                                    DB::Raw('CONCAT_WS(" ", formalities.shortname, formalities.letter, LPAD(formalities.sales_point,4,0), LPAD(formalities.number,6,0)) AS number'),
                                    'formalities.group_id'
                                )
                                ->where('formalities.group_id', config('settings.groups.receipt_id'))
                                ->where('formalities.status_id', '!=', 5)
                                ->join('formality_details', function($join) use ($invoice) {
                                    $join->on('formalities.id', '=', 'formality_details.formality_id')
                                    ->where('formality_details.linked_formality_id', $invoice->id);
                                })
                                ->get();

        $obj_invoice = [
            'details'   => $invoice->load('customer.addresses','customer.phones','customer.contacts','customer.doc_type','status','iva_value'),
            'formalities'    => $formalities,
            'concepts'  => $concepts,
            'discounts'  => $discounts,
            'custom_discounts'  => $custom_discounts,
            'parents'  => $parents
        ];

        return $obj_invoice;
    }


    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(UpdateInvoice $request)
    {
        //
        DB::beginTransaction();

        try {

            $details  = $request->details;
            $formalities = $request->formalities;
            $concepts = $request->concepts;
            $discounts = $request->discounts;
            $custom_discounts = $request->custom_discounts;

            $formality_details = array();

            // Get formality
            $invoice = Formality::findOrFail($details['id']);

            $invoice_new_total = 0;

            //***************************************************************************
            // CONCEPTS
            //***************************************************************************

            // Remove old concepts if needed
            $concepts_to_delete = FormalityDetail::where('formality_id', $invoice->id)->whereNull('linked_formality_id')->where('credit', 0)->whereNotIn('id',array_column($concepts, 'id'))->get();
            foreach ($concepts_to_delete as $concept) { $concept->delete(); }

            // Update or create concepts
            foreach ($concepts as $concept) {

                $concept = (object)$concept;

                $formality_detail = [
                    'formality_id' => $invoice->id,
                    'details' => $concept->details,
                    'amount' => $concept->amount
                ];

                // Sum total invoice
                $invoice_new_total += $concept->amount;

                // Update / create
                FormalityDetail::updateOrCreate(['id'=>isset($concept->id) ? $concept->id : null], $formality_detail);
            }

            //***************************************************************************
            // CUSTOM DISCOUNTS
            //***************************************************************************

            // Remove old custom_discounts if needed
            $c_discounts_to_delete = FormalityDetail::where('formality_id', $invoice->id)
                                        ->whereNotIn('id',array_column($custom_discounts, 'id'))
                                        ->whereNull('linked_formality_id')
                                        ->where('credit', 1)
                                        ->get();
            foreach ($c_discounts_to_delete as $c_discount) { $c_discount->delete(); }

            // Update or create custom_discounts
            foreach ($custom_discounts as $c_discount) {

                $c_discount = (object)$c_discount;

                $formality_detail = [
                    'formality_id' => $invoice->id,
                    'details' => $c_discount->details,
                    'amount' => $c_discount->amount,
                    'credit' => 1
                ];

                // Sum total invoice
                $invoice_new_total -= $c_discount->amount;

                // Update / create
                FormalityDetail::updateOrCreate(['id'=>isset($c_discount->id) ? $c_discount->id : null], $formality_detail);
            }

            //***************************************************************************
            // FORMALITIES
            //***************************************************************************
            $details_to_delete = FormalityDetail::with('linked_formality')
                                    ->where('formality_id', $invoice->id)
                                    ->whereNotNull('linked_formality_id')
                                    ->whereNotIn('linked_formality_id', array_column($formalities, 'id'))
                                    ->where('credit', 0)
                                    ->get();

            // Remove old formalities if needed
            foreach ($details_to_delete as $detail) {

                // Get linked formality and undo total_pending
                $linked_formality = Formality::findOrFail($detail->linked_formality->id);
                $linked_formality->total_pending = $linked_formality->total_pending + $detail->amount;
                $linked_formality->save();

                // Delete formality_detail
                $detail->delete();
            }

            // Create / Update formality_details
            foreach ($formalities as $formality) {
                $formality = (object)$formality;

                $formality_detail = [
                    'details' => $formality->number,
                    'amount' => $formality->amount,
                    'formality_id' => $invoice->id,
                    'linked_formality_id' => $formality->id,
                    'credit' => 0
                ];

                // Get current formality_detail
                $current_formality_detail = FormalityDetail::where('formality_id',$invoice->id)
                                                ->where('linked_formality_id',$formality->id)
                                                ->whereNotNull('linked_formality_id')
                                                ->where('credit', 0)
                                                ->first();

                // Update / create
                FormalityDetail::updateOrCreate(['id'=>isset($current_formality_detail->id) ? $current_formality_detail->id : null], $formality_detail);

                // Update formality total_pending
                $formality_to_update = Formality::findOrFail($formality->id);
                $formality_to_update->total_pending = $formality_to_update->total_pending + ( $current_formality_detail ? $current_formality_detail->amount : 0 ) - $formality->amount;
                $formality_to_update->save();

                // Sum total invoice
                $invoice_new_total += $formality->amount;
            }

            //***************************************************************************
            // DISCOUNTS
            //***************************************************************************
            $details_to_delete = FormalityDetail::with('linked_formality')
                                    ->where('formality_id', $invoice->id)
                                    ->whereNotNull('linked_formality_id')
                                    ->whereNotIn('linked_formality_id', array_column($discounts, 'id'))
                                    ->where('credit', 1)
                                    ->get();

            // Remove old formalities if needed
            foreach ($details_to_delete as $detail) {

                // Get linked formality and undo total_pending
                $linked_formality = Formality::findOrFail($detail->linked_formality->id);
                $linked_formality->total_pending = $linked_formality->total_pending + $detail->amount;
                $linked_formality->save();

                // Delete formality_detail
                $detail->delete();
            }

            // Create / Update formality_details
            foreach ($discounts as $formality) {
                $formality = (object)$formality;

                $formality_detail = [
                    'details' => $formality->number,
                    'amount' => $formality->amount,
                    'formality_id' => $invoice->id,
                    'linked_formality_id' => $formality->id,
                    'credit' => 1
                ];

                // Get current formality_detail
                $current_formality_detail = FormalityDetail::where('formality_id',$invoice->id)
                                                ->where('linked_formality_id',$formality->id)
                                                ->whereNotNull('linked_formality_id')
                                                ->where('credit', 1)
                                                ->first();

                // Update / create
                FormalityDetail::updateOrCreate(['id'=>isset($current_formality_detail->id) ? $current_formality_detail->id : null], $formality_detail);

                // Update formality total_pending
                $formality_to_update = Formality::findOrFail($formality->id);
                $formality_to_update->total_pending = $formality_to_update->total_pending + ( $current_formality_detail ? $current_formality_detail->amount : 0 ) - $formality->amount;
                $formality_to_update->save();

                // Sum total invoice
                $invoice_new_total -= $formality->amount;
            }

            // Add IVA
            \Log::debug( $details['iva_value']['value'] );

            $total_iva = $invoice_new_total * $details['iva_value']['value'] / 100;
            $invoice_new_total += $total_iva;

            \Log::debug( $total_iva );

            if ( compareFloatNumbers( $invoice->total, $invoice_new_total,'=') ){
                DB::commit();

                return response()->json([
                    'message' => 'Factura actualizada correctamente',
                    'invoice' => $invoice
                ]);
            } else {

                return response()->json([
                    'message' => 'El total de la Factura no se corresponde con lo imputado',
                ], 500);

            }

        } catch (\Exception $e) {

            DB::rollback();

            return response()->json([
                'message' => $e->getMessage(),
            ], 500);
        }
    }

    /**
     * Get the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function getBooks()
    {
        //
        $resources = Book::select(
                            'books.id',
                            DB::Raw('CONCAT_WS(" ", book_types.shortname, book_types.letter, LPAD(books.sales_point,4,0)) AS name')
                        )
                        ->join('book_types','books.type_id','book_types.id')
                        ->where('book_types.group_id', config('settings.groups.invoice_id'))
                        ->orderBy('book_types.letter','DESC')
                        ->get();

        return response()->json($resources);
    }
}