<?php

namespace App\Http\Controllers;

use App\Http\Controllers\AppBaseController;
use Illuminate\Http\Request;
use App\ApiCode;
use App\Models\Account;
use Validator;
use App\Models\Sequence;
use App\Models\DeletedSequence;
use App\Models\Receive;
use App\Models\Rent;
use App\Models\Deposit;
use App\Models\Plain;
use App\Models\Pay;
use App\Models\GrantPerson;
use App\Models\Movement;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Log;

class ReceiveController extends AppBaseController
{

    public function __construct()
    {
        $this->middleware('auth:api',);
    }

    public function add(Request $request)
    {

        /*    'person_id',
        'company_id',
        'name',
        'type',
        'date',
        'note', */

        Log::error($request);
        $rules =
            [
                'year' => 'required',
                'accountant_year_id' => 'required',

                'date' => 'required',
                'name' => 'required',
                'movement' => 'required',

            ];
        $validator = Validator::make($request->all(), $rules);
        if ($validator->fails()) {
            return $this->sendResponse(
                $validator->errors(),
                "validation errors",
                ApiCode::BAD_REQUEST,
                1
            );
        }
        $isDeleted = true;
        $sequence = DeletedSequence::where('name', 'receive')
            ->where('accountant_year_id', $request->accountant_year_id)
            ->where('year', $request->year)
            ->first();
        if (!$sequence) {
            $isDeleted = false;
            $sequence = Sequence::where('name', 'receive')
                ->where('accountant_year_id', $request->accountant_year_id)
                ->where('year', $request->year)
                ->first();
            if (!$sequence) {
                $sequence = Sequence::create([
                    'name' => 'receive',
                    'accountant_year_id' => $request->accountant_year_id,
                    'year' => $request->year
                ]);
            }

            $sequence->sequence += 1;
            $sequence->save();
        }

        $input = $request->all();
        $input['user_id'] = auth()->user()->id;
        $input['sequence'] = $sequence->sequence;
        if ($isDeleted) $sequence->delete();
        $receive = Receive::create($input);

        $movementData = json_decode($request->movement);
        //$movementData = $request->movement;
        $total = 0;
        foreach ($movementData as $movement) {
            if ($movement->amount > 0)
                $mov = Movement::create([
                    'receive_id' =>   $receive->id,
                    'accountant_year_id' => $request->accountant_year_id,
                    'year' => $request->year,
                    'rcd_type' => 0,
                    'is_debtor' => 0,
                    'note' => isset($movement->note) ?  $movement->note : null,
                    'account_id' =>  $movement->accountId,
                    'amount' =>   $movement->amount,
                    'account_gp_id' => ($movement->accountId > 62 && $movement->accountId < 70) ? 62 : $movement->accountId,

                ]);
            $total += $movement->amount;
        }
        $mov2 = Movement::create([
            'receive_id' =>   $receive->id,
            'accountant_year_id' => $request->accountant_year_id,
            'year' => $request->year,
            'rcd_type' => 0,
            'is_debtor' => 1,
            'account_id' =>  48,
            'amount' =>    $total,
            'account_gp_id' => 48,
        ]);
        if ($request->has('dead')) {

            $deadData = json_decode($request->dead);
            // $deadData = $request->dead;
            foreach ($deadData as $dead) {
                if ($dead->amount > 0)
                    GrantPerson::create([
                        'receive_id' =>   $receive->id,
                        'accountant_year_id' => $request->accountant_year_id,

                        'person_id' => isset($dead->person_id) ? $dead->person_id : null,
                        'company_id' => isset($dead->company_id) ?  $dead->company_id : null,
                        'company_detail_id' => isset($dead->company_detail_id) ?  $dead->company_detail_id : null,

                        'year' => isset($dead->year) ? $dead->year : null,
                        'name' =>   $dead->name,
                        'date' =>   $request->date,
                        'amount' =>   $dead->amount,

                    ]);
            }
        }

        return $this->sendResponse(
            [Receive::where('id', $receive->id)->first()],
            "receive created successfully",
            ApiCode::CREATED,
            0
        );
    }

    public function delete($id, Request $request)
    {
        $receive = Receive::where('id', $id)->firstOrFail();

        foreach ($receive->movements as $old) {
            $old->delete();
        }

        foreach ($receive->grantPeople as $old) {
            $old->delete();
        }
        $year = $receive->year;
        $accountant_year_id = $receive->accountant_year_id;
        $seq = $receive->sequence;
        DeletedSequence::create([
            'name' => 'receive',
            'accountant_year_id' => $accountant_year_id,
            'year' => $year,
            'sequence' => $seq,
        ]);
        $receive->delete();
        return $this->sendResponse(
            null,
            "receive deleted successfully",
            ApiCode::SUCCESS,
            0
        );
    }

    public function edit($id, Request $request)
    {

        $receive = Receive::where('id', $id)->firstOrFail();

        foreach ($receive->movements as $old) {
            $old->delete();
        }

        foreach ($receive->grantPeople as $old) {
            $old->delete();
        }

        $movementData = json_decode($request->movement);
        //$movementData = $request->movement;
        $total = 0;
        foreach ($movementData as $movement) {
            if ($movement->amount > 0)
                $mov = Movement::create([
                    'receive_id' =>   $receive->id,
                    'accountant_year_id' => $receive->accountant_year_id,
                    'year' => $receive->year,
                    'rcd_type' => 0,
                    'is_debtor' => 0,
                    'note' => isset($movement->note) ?  $movement->note : null,
                    'account_id' =>  $movement->accountId,
                    'amount' =>   $movement->amount,
                    'account_gp_id' => ($movement->accountId > 62 && $movement->accountId < 70) ? 62 : $movement->accountId,

                ]);
            $total += $movement->amount;
        }
        $mov2 = Movement::create([
            'receive_id' =>   $receive->id,
            'accountant_year_id' => $receive->accountant_year_id,
            'year' => $receive->year,
            'rcd_type' => 0,
            'is_debtor' => 1,
            'account_id' =>  48,
            'amount' =>    $total,
            'account_gp_id' => 48,
        ]);
        if ($request->has('dead')) {

            $deadData = json_decode($request->dead);
            // $deadData = $request->dead;
            foreach ($deadData as $dead) {
                if ($dead->amount > 0)
                    GrantPerson::create([
                        'receive_id' =>   $receive->id,
                        'accountant_year_id' => $receive->accountant_year_id,

                        'person_id' => isset($dead->person_id) ? $dead->person_id : null,
                        'company_id' => isset($dead->company_id) ?  $dead->company_id : null,
                        'company_detail_id' => isset($dead->company_detail_id) ?  $dead->company_detail_id : null,

                        'year' => isset($dead->year) ? $dead->year : null,
                        'name' =>   $dead->name,
                        'date' =>   $request->date,
                        'amount' =>   $dead->amount,

                    ]);
            }
        }

        if ($request->has('status'))
            if ($request->status != null) {
                $receive->status = $request->status;
            }

        if ($request->has('date'))
            if ($request->date != null) {
                $receive->date = $request->date;
            }

        if ($request->has('note'))
            if ($request->note != null) {
                $receive->note = $request->note;
            }

        $receive->save();
        return $this->sendResponse(
            [Receive::where('id', $receive->id)->first()],
            "receive updated successfully",
            ApiCode::SUCCESS,
            0
        );
    }

    public function all(Request $request)
    {

        $limit = 10;
        $page = 1;
        if ($request->query("limit") != null) {
            $limit = $request->query("limit");
        }
        if ($request->query("page") != null) {
            $page = $request->query("page");
        }


        $offset = $page * $limit - $limit;
        if ($request->query("year") == null || $request->query("accountant_year_id") == null) {
            return $this->sendResponse(
                null,
                "validation errors",
                ApiCode::BAD_REQUEST,
                1
            );
        }
        $receives =  Receive::where('year', $request->query("year"))
            ->where('accountant_year_id', $request->query("accountant_year_id"))
            ->where(
                function ($query) use ($request) {
                    if ($request->query("sequence") != null) {
                        $sequence = $request->query("sequence");
                        return $query->where('sequence', $sequence);
                    }
                }

            )
            ->where(
                function ($query) use ($request) {
                    if ($request->query("name") != null) {
                        $query->whereHas(
                            'movements',
                            function ($query) use ($request) {
                                if ($request->query("name") != null) {
                                    $account_name = $request->query("name");
                                    return $query->whereHas('account', function ($query) use ($account_name) {
                                        $query->where('name',  'like', '%' . $account_name . '%');
                                    });
                                }
                            }
                        );
                    }
                }
            )
            ->where(
                function ($query) use ($request) {
                    if ($request->query("from") != null) {
                        $from = $request->query("from");
                        $from =  str_replace('/', '-', $from);

                        return $query->whereDate('date', '>=', date("Y-m-d", strtotime($from)));
                    }
                }

            )
            ->where(
                function ($query) use ($request) {
                    if ($request->query("search_rec_name") != null) {
                        $name = $request->query("search_rec_name");

                        return $query->where('name', 'like', '%' . $name . '%');
                    }
                }

            )
            ->where(
                function ($query) use ($request) {
                    if ($request->query("to") != null) {

                        $to = $request->query("to");

                        $to =  str_replace('/', '-', $to);
                        return $query->whereDate('date', '<=', date("Y-m-d", strtotime($to)));
                    }
                }

            )
            ->where(
                function ($query) use ($request) {
                    if ($request->query("search_file_id") != null) {

                        $search_file_id = $request->query("search_file_id");

                        return $query->where('person_id', $search_file_id)->orWhere('company_id', $search_file_id);
                    }
                }

            )
            ->orderBy('created_at', 'DESC')
            ->limit($limit)
            ->offset($offset)
            ->get();


        return  $this->sendResponse(
            $receives,
            "here are your receives!",
            ApiCode::SUCCESS,
            0
        );
    }
    public function boxMovement(Request $request)
    {
        if ($request->query("year") == null || $request->query("accountant_year_id") == null || $request->query("date") == null) {
            return $this->sendResponse(
                null,
                "validation errors",
                ApiCode::BAD_REQUEST,
                1
            );
        }
        $year = $request->query("year");
        $accountant_year_id = $request->query("accountant_year_id");




        //return $query->whereDate('date', '>=', date("Y-m-d", strtotime($from)));
        $boxReceive = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('receive', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })

            ->where('account_gp_id', 48)
            ->where('is_debtor', 1)
            ->sum('amount');
        $boxDeposit = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('deposit', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })
            ->where('account_gp_id', 48)
            ->where('is_debtor', 1)
            ->sum('amount');
        $boxRent = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('rent', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })
            ->where('account_gp_id', 48)
            ->where('is_debtor', 1)
            ->sum('amount');

        $boxPlain = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('plain', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })
            ->where('account_gp_id', 48)
            ->where('is_debtor', 1)
            ->sum('amount');

        $boxPay = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('pay', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })
            ->where('account_gp_id', 48)
            ->where('is_debtor', 1)
            ->sum('amount');
        $outboxReceive = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('receive', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })

            ->where('account_gp_id', 48)
            ->where('is_debtor', 0)
            ->sum('amount');
        $outboxDeposit = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('deposit', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })

            ->where('account_gp_id', 48)
            ->where('is_debtor', 0)
            ->sum('amount');
        $outboxRent = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('rent', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })

            ->where('account_gp_id', 48)
            ->where('is_debtor', 0)
            ->sum('amount');
        $outboxPlain = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('plain', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })

            ->where('account_gp_id', 48)
            ->where('is_debtor', 0)
            ->sum('amount');
        $outboxPay = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->whereHas('pay', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
            })
            ->where('account_gp_id', 48)
            ->where('is_debtor', 0)
            ->sum('amount');
        $date = $request->query("date");
        $date =  str_replace('/', '-', $date);

        $allMovement =
            Movement::with(['accountGp', 'receive', 'deposit', 'pay', 'plain', 'rent'])
            ->without('account')
            ->whereHas('pay', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',   date("Y-m-d", strtotime($date)))->where('account_id', 48);
            })
            // ->where('account_gp_id', '!=', 48)

            ->orWhereHas('rent', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',    date("Y-m-d", strtotime($date)));
            })

            ->where('account_gp_id', '!=', 48)
            ->orWhereHas('deposit', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',    date("Y-m-d", strtotime($date)));
            })

            //  ->where('account_gp_id', '!=', 48)
            ->orWhereHas('receive', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                return $q->whereDate('date',    date("Y-m-d", strtotime($date)));
            })
            ->where('account_gp_id', '!=', 48)
            ->get();


        $total = ($boxReceive + $boxDeposit + $boxRent + $boxPlain + $boxPay)
            - ($outboxReceive + $outboxDeposit + $outboxRent + $outboxPlain + $outboxPay);
        return  $this->sendResponse(
            [
                'box' =>  $total,
                'allMovement' => $allMovement,

            ],
            "here are your receives!",
            ApiCode::SUCCESS,
            0
        );
    }

    public function ledger(Request $request)
    {
        if (
            $request->query("account_id") == null
            || $request->query("year") == null
            || $request->query("accountant_year_id") == null
            || $request->query("date") == null
            //$request->query("due_date") == null
            || $request->query("ledger_type") == null

        ) {
            return $this->sendResponse(
                null,
                "validation errors",
                ApiCode::BAD_REQUEST,
                1
            );
        }
        if ($request->query("ledger_type") == 5)
            if ($request->query("due_date") == null) {
                return $this->sendResponse(
                    null,
                    "validation errors1",
                    ApiCode::BAD_REQUEST,
                    1
                );
            }
        $limit = 100;
        if ($request->query("limit") != null)
            $limit = $request->query('limit');
        // $currentYear = Carbon::now()->format('Y');
        /*    $Q1start=Carbon::createMidnightDate($currentYear,1,1);
        $Q1end=Carbon::createMidnightDate($currentYear,3,31);

        $Q2start=Carbon::createMidnightDate($currentYear,4,1);
        $Q2end=Carbon::createMidnightDate($currentYear,6,30);

        $Q3start=Carbon::createMidnightDate($currentYear,7,1);
        $Q3end=Carbon::createMidnightDate($currentYear,9,30);

        $Q4start=Carbon::createMidnightDate($currentYear,10,1);
        $Q4end=Carbon::createMidnightDate($currentYear,12,31); */
        //ledger type = 0 , 1 , 2 , 3 , 4 , 5 , 6
        $allMovement =
            Movement::select(
                'id',
                'deposit_id',
                'rent_id',
                'receive_id',
                'pay_id',
                'plain_id',
                'rcd_type',
                'account_id',
                'is_debtor',
                'amount',
            )->with([
                'account' => function ($query) {
                    $query->select('id', 'name',);
                },
                'receive' => function ($query) {
                    $query->select('id', 'name', 'date', 'sequence');
                },
                'deposit' => function ($query) {
                    $query->select('id', 'date', 'sequence');
                },
                'pay' => function ($query) {
                    $query->select('id', 'name', 'condition', 'sequence');
                },
                'plain' => function ($query) {
                    $query->select('id', 'date', 'sequence');
                },
                'rent' => function ($query) {
                    $query->select('id', 'name', 'statement', 'sequence');
                },
            ])
            ->whereHas('pay', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereHas('movements',  function ($q) use ($request) {
                        return  $q->where('account_id',  $request->query("account_id"));
                    })
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->orWhereHas('rent', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereHas('movements',  function ($q) use ($request) {
                        return $q->where('account_id',  $request->query("account_id"));
                    })
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })

            ->orWhereHas('deposit', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereHas('movements',  function ($q) use ($request) {
                        return $q->where('account_id',  $request->query("account_id"));
                    })
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })

            ->orWhereHas('receive', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                $q->whereHas('movements',  function ($q) use ($request) {
                    return $q->where('account_id',  $request->query("account_id"));
                })
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->orWhereHas('plain', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereHas('movements',  function ($q) use ($request) {
                        return $q->where('account_id',  $request->query("account_id"));
                    })
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->paginate($limit);

        $total_debtor =
            Movement::select(
                'id',
                'deposit_id',
                'rent_id',
                'receive_id',
                'pay_id',
                'plain_id',
                'rcd_type',
                'account_id',
                'is_debtor',
                'amount',
            )
            ->whereHas('pay', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })->where('account_id',  $request->query("account_id"))->where('is_debtor', 1)
            ->orWhereHas('rent', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->where('account_id',  $request->query("account_id"))->where('is_debtor', 1)
            ->orWhereHas('deposit', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->where('account_id',  $request->query("account_id"))->where('is_debtor', 1)
            ->orWhereHas('receive', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->where('account_id',  $request->query("account_id"))->where('is_debtor', 1)
            ->orWhereHas('plain', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->where('account_id',  $request->query("account_id"))->where('is_debtor', 1)
            ->sum('amount');
        $total =
            Movement::select(
                'id',
                'deposit_id',
                'rent_id',
                'receive_id',
                'pay_id',
                'plain_id',
                'rcd_type',
                'account_id',
                'is_debtor',
                'amount',
            )
            ->whereHas('pay', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })->where('account_id',  $request->query("account_id"))->where('is_debtor', 0)
            ->orWhereHas('rent', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->where('account_id',  $request->query("account_id"))->where('is_debtor', 0)
            ->orWhereHas('deposit', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->where('account_id',  $request->query("account_id"))->where('is_debtor', 0)
            ->orWhereHas('receive', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->where('account_id',  $request->query("account_id"))->where('is_debtor', 0)
            ->orWhereHas('plain', function ($q) use ($request) {
                $date = $request->query("date");
                $date =  str_replace('/', '-', $date);
                $due_date = $request->query("due_date");
                $due_date =  str_replace('/', '-', $due_date);
                return $q
                    ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                    ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
            })
            ->where('account_id',  $request->query("account_id"))->where('is_debtor', 0)
            ->sum('amount');
        return  $this->sendResponse(
            [
                'total_debtor' =>  intval($total_debtor),
                'total' =>  intval($total),
                'allMovement' => $allMovement,

            ],
            "here are your ledger!",
            ApiCode::SUCCESS,
            0
        );
    }
    public function trialBalance(Request $request)
    {
        if (
            $request->query("year") == null
            || $request->query("accountant_year_id") == null
            || $request->query("date") == null
            //$request->query("due_date") == null
            || $request->query("trail_balance_type") == null

        ) {
            return $this->sendResponse(
                null,
                "validation errors",
                ApiCode::BAD_REQUEST,
                1
            );
        }
        if ($request->query("trail_balance_type") == 5)
            if ($request->query("due_date") == null) {
                return $this->sendResponse(
                    null,
                    "validation errors1",
                    ApiCode::BAD_REQUEST,
                    1
                );
            }


        $accounts = Movement::select('account_gp_id')->groupBy('account_gp_id')->get();
        $trialBalance = array();
        $total_debtor = 0;
        $total_creditor = 0;
        $t_debtor = 0;
        $t_creditor = 0;
        foreach ($accounts as $account) {
            $debtor = Movement::where('account_gp_id', $account->accountGp->id)
                ->where('is_debtor', 1)
                ->where(function ($query) use ($request) {
                    $query->whereHas('pay', function ($q) use ($request) {
                        $date = $request->query("date");
                        $date =  str_replace('/', '-', $date);
                        $due_date = $request->query("due_date");
                        $due_date =  str_replace('/', '-', $due_date);
                        return $q
                            ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                            ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                    })
                        ->orWhereHas('rent', function ($q) use ($request) {
                            $date = $request->query("date");
                            $date =  str_replace('/', '-', $date);
                            $due_date = $request->query("due_date");
                            $due_date =  str_replace('/', '-', $due_date);
                            return $q
                                ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                                ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                        })

                        ->orWhereHas('deposit', function ($q) use ($request) {
                            $date = $request->query("date");
                            $date =  str_replace('/', '-', $date);
                            $due_date = $request->query("due_date");
                            $due_date =  str_replace('/', '-', $due_date);
                            return $q
                                ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                                ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                        })

                        ->orWhereHas('receive', function ($q) use ($request) {
                            $date = $request->query("date");
                            $date =  str_replace('/', '-', $date);
                            $due_date = $request->query("due_date");
                            $due_date =  str_replace('/', '-', $due_date);
                            $q
                                ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                                ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                        })
                        ->orWhereHas('plain', function ($q) use ($request) {
                            $date = $request->query("date");
                            $date =  str_replace('/', '-', $date);
                            $due_date = $request->query("due_date");
                            $due_date =  str_replace('/', '-', $due_date);
                            return $q
                                ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                                ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                        });
                })
                ->sum('amount');

            $creditor = Movement::where('account_gp_id', $account->accountGp->id)
                ->where('is_debtor', 0)
                ->where(function ($query) use ($request) {
                    $query->whereHas('pay', function ($q) use ($request) {
                        $date = $request->query("date");
                        $date =  str_replace('/', '-', $date);
                        $due_date = $request->query("due_date");
                        $due_date =  str_replace('/', '-', $due_date);
                        return $q
                            ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                            ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                    })
                        ->orWhereHas('rent', function ($q) use ($request) {
                            $date = $request->query("date");
                            $date =  str_replace('/', '-', $date);
                            $due_date = $request->query("due_date");
                            $due_date =  str_replace('/', '-', $due_date);
                            return $q
                                ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                                ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                        })

                        ->orWhereHas('deposit', function ($q) use ($request) {
                            $date = $request->query("date");
                            $date =  str_replace('/', '-', $date);
                            $due_date = $request->query("due_date");
                            $due_date =  str_replace('/', '-', $due_date);
                            return $q
                                ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                                ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                        })

                        ->orWhereHas('receive', function ($q) use ($request) {
                            $date = $request->query("date");
                            $date =  str_replace('/', '-', $date);
                            $due_date = $request->query("due_date");
                            $due_date =  str_replace('/', '-', $due_date);
                            $q
                                ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                                ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                        })
                        ->orWhereHas('plain', function ($q) use ($request) {
                            $date = $request->query("date");
                            $date =  str_replace('/', '-', $date);
                            $due_date = $request->query("due_date");
                            $due_date =  str_replace('/', '-', $due_date);
                            return $q
                                ->whereDate('date', '>=',   date("Y-m-d", strtotime($date)))
                                ->whereDate('date', '<=',   date("Y-m-d", strtotime($due_date)));
                        });
                })
                ->sum('amount');

            if (intval($debtor) > 0 || intval($creditor) > 0) {
                $total_debtor += intval($debtor);
                $total_creditor += intval($creditor);
                $res = intval($debtor) - intval($creditor);

                $t_debtor += $res > 0 ? $res : 0;
                $t_creditor += $res > 0 ? 0 : $res * -1;
                array_push($trialBalance, [
                    "account_name" => $account->accountGp->name,
                    "debtor" => intval($debtor),
                    "creditor" => intval($creditor),
                    "total_debtor" => $res > 0 ? $res : 0,
                    "total_creditor" => $res > 0 ? 0 : $res * -1,
                ]);
            }
        }

        return  $this->sendResponse(
            [
                'total_debtor' =>  $total_debtor,
                'total_creditor' =>  $total_creditor,
                't_debtor' =>  $t_debtor,
                't_creditor' =>  $t_creditor,
                'allAccounts' => $trialBalance,
            ],
            "here are your ledger!",
            ApiCode::SUCCESS,
            0
        );
    }



    public function MonthlyBoxMovement(Request $request)
    {
        if (
            $request->query("year") == null || $request->query("accountant_year_id") == null
            || $request->query("date") == null
            || $request->query("due_date") == null
        ) {
            return $this->sendResponse(
                null,
                "validation errors",
                ApiCode::BAD_REQUEST,
                1
            );
        }


        $year = $request->query("year");
        $accountant_year_id = $request->query("accountant_year_id");

        $date = $request->query("date");
        $date =  str_replace('/', '-', $date);
        $due_date = $request->query("due_date");
        $due_date =  str_replace('/', '-', $due_date);

        //  $accounts = Movement::select('account_gp_id')->groupBy('account_gp_id')->get();


        $box = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->where(function ($query) use ($date) {
                $query->whereHas('plain')
                    ->orWhereHas('pay', function ($q) use ($date) {
                        return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('rent', function ($q) use ($date) {
                        return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('deposit', function ($q) use ($date) {
                        return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('receive', function ($q) use ($date) {
                        return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
                    });
            })
            ->where('account_gp_id', 48)
            ->where('is_debtor', 1)
            ->sum('amount');


        $outbox = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->where(function ($query) use ($date) {
                $query->whereHas('pay', function ($q) use ($date) {
                    return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
                })->orWhereHas('plain')
                    ->orWhereHas('rent', function ($q) use ($date) {
                        return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('deposit', function ($q) use ($date) {
                        return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('receive', function ($q) use ($date) {
                        return $q->whereDate('date',   '<', date("Y-m-d", strtotime($date)));
                    });
            })

            ->where('account_gp_id', 48)
            ->where('is_debtor', 0)
            ->sum('amount');


        //with(['accountGp',])
        //->without('account')


        $boxCurrent = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->where(function ($query) use ($due_date, $date) {
                $query
                    ->whereHas('pay', function ($q) use ($due_date, $date) {
                        return $q
                            ->whereDate('date',   '<=', date("Y-m-d", strtotime($due_date)))
                            ->whereDate('date',   '>=', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('rent', function ($q) use ($due_date, $date) {
                        return $q
                            ->whereDate('date',   '<=', date("Y-m-d", strtotime($due_date)))
                            ->whereDate('date',   '>=', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('deposit', function ($q) use ($due_date, $date) {
                        return $q
                            ->whereDate('date',   '<=', date("Y-m-d", strtotime($due_date)))
                            ->whereDate('date',   '>=', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('receive', function ($q) use ($due_date, $date) {
                        return $q
                            ->whereDate('date',   '<=', date("Y-m-d", strtotime($due_date)))
                            ->whereDate('date',   '>=', date("Y-m-d", strtotime($date)));
                    });
            })
            ->where('account_gp_id', 48)
            ->where('is_debtor', 1)
            ->sum('amount');


        $outboxEnd = Movement::where('accountant_year_id', $accountant_year_id)
            ->where('year', $year)
            ->where(function ($query) use ($due_date, $date) {
                $query->whereHas('pay', function ($q) use ($due_date, $date) {
                    return $q
                        ->whereDate('date',   '<=', date("Y-m-d", strtotime($due_date)))
                        ->whereDate('date',   '>=', date("Y-m-d", strtotime($date)));
                })->orWhereHas('plain')
                    ->orWhereHas('rent', function ($q) use ($due_date, $date) {
                        return $q
                            ->whereDate('date',   '<=', date("Y-m-d", strtotime($due_date)))
                            ->whereDate('date',   '>=', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('deposit', function ($q) use ($due_date, $date) {
                        return $q
                            ->whereDate('date',   '<=', date("Y-m-d", strtotime($due_date)))
                            ->whereDate('date',   '>=', date("Y-m-d", strtotime($date)));
                    })->orWhereHas('receive', function ($q) use ($due_date, $date) {
                        return $q
                            ->whereDate('date',   '<=', date("Y-m-d", strtotime($due_date)))
                            ->whereDate('date',   '>=', date("Y-m-d", strtotime($date)));
                    });
            })

            ->where('account_gp_id', 48)
            ->where('is_debtor', 0)
            ->sum('amount');





        $allMovement =
            Movement::where(function ($query) use ($date, $due_date) {
                $query->whereHas('rent', function ($q) use ($date, $due_date) {
                    return $q
                        ->whereDate('date', '>=',  date("Y-m-d", strtotime($date)))
                        ->whereDate('date', '<=',  date("Y-m-d", strtotime($due_date)));
                })
                    ->orWhereHas('receive', function ($q) use ($date, $due_date) {
                        return $q
                            ->whereDate('date', '>=',  date("Y-m-d", strtotime($date)))
                            ->whereDate('date', '<=',  date("Y-m-d", strtotime($due_date)));
                    });
            })
            ->where('account_gp_id', '!=', 48)
            ->orWhere(function ($query) use ($date, $due_date) {
                $query->whereHas('pay', function ($q) use ($date, $due_date) {
                    return $q
                        ->whereDate('date', '>=',  date("Y-m-d", strtotime($date)))
                        ->whereDate('date', '<=',  date("Y-m-d", strtotime($due_date)));
                })
                    ->orWhereHas('deposit', function ($q) use ($date, $due_date) {
                        return $q
                            ->whereDate('date', '>=',  date("Y-m-d", strtotime($date)))
                            ->whereDate('date', '<=',  date("Y-m-d", strtotime($due_date)));
                    });
            })
            ->get();

        $days = array();
        // $accounts = array();
        foreach ($allMovement as $am) {
            //     if (!in_array($am->accountGp->name, $accounts))
            //        array_push($accounts, $am->accountGp->name);
            // 0 =rcv , 1=pay , 2= dps, 3= rnt
            switch ($am->rcd_type) {
                case 0:
                    if (!in_array($am->receive->date, $days))
                        array_push($days, $am->receive->date);
                    break;
                case 1:
                    if (!in_array($am->pay->date, $days))
                        array_push($days, $am->pay->date);
                    break;
                case 2:
                    if (!in_array($am->deposit->date, $days))
                        array_push($days, $am->deposit->date);
                    break;
                case 3:
                    if (!in_array($am->rent->date, $days))
                        array_push($days, $am->rent->date);
                    break;
            }
        }


        $res = array();
        $res2 = array();

        foreach ($days as $day) {
            $darr =   Movement::with(array('accountGp' => function ($query) {
                $query->select('id', 'name');
            }))->select('account_gp_id',)
                ->selectRaw("SUM(amount) as total")->where(function ($query) use ($day) {
                    $query->whereHas('rent', function ($q) use ($day) {
                        return $q
                            ->whereDate('date', $day);
                    })
                        ->orWhereHas('receive', function ($q) use ($day) {
                            return $q
                                ->whereDate('date', $day);
                        });
                })
                ->where('account_gp_id', '!=', 48)
                ->groupBy('account_gp_id',)
                ->get();

            if (count($darr) > 0)
                array_push($res, [
                    "day" => $day,
                    "movements" => $darr
                ]);



            $darr2 =   Movement::with(array('accountGp' => function ($query) {
                $query->select('id', 'name');
            }))->without('account')->where(function ($query) use ($day) {
                $query->whereHas('pay', function ($q) use ($day) {
                    return $q
                        ->whereDate('date', $day)
                        ->whereHas('movements', function ($query) {
                            $query->where('account_gp_id', 48);
                        });
                })
                    ->orWhereHas('deposit', function ($q) use ($day) {
                        return $q
                            ->whereDate('date', $day)->whereHas('movements', function ($query) {
                                $query->where('account_gp_id', 48);
                            });
                    });
            })->where(function ($query) {
                $query->where('is_debtor', 1)->orWhere('account_gp_id', 48);
            })
                ->get();

            $darr3 = array();

            foreach ($darr2 as $d2) {
                if ($d2->is_debtor == 1) {
                    $tl = 0;
                    foreach ($darr2 as $d22) {
                        // 0 =rcv , 1=pay , 2= dps, 3= rnt
                        $isb = false;
                        switch ($d2->rcd_type) {
                            case 1:
                                if ($d2->pay_id == $d22->pay_id && $d22->is_debtor == 0 && $d22->account_gp_id == 48) {
                                    $tl = $d22->amount;
                                    $isb = true;
                                }
                                break;
                            case 2:
                                if ($d2->deposit_id == $d22->deposit_id && $d22->is_debtor == 0 && $d22->account_gp_id == 48) {
                                    $tl = $d22->amount;
                                    $isb = true;
                                }
                                break;
                        }
                        if ($isb)
                            break;
                    }
                    array_push($darr3, [
                        "account_gp_id" => $d2->account_gp_id,
                        "total" => $tl,
                        "account_name" =>  $d2->accountGp->name,
                    ]);
                }
            }


            $darr4 = array();
            $iddd = array();
            foreach ($darr3 as $d3) {
                if (!in_array($d3['account_gp_id'], $iddd)) {
                    array_push($iddd, $d3['account_gp_id']);
                    $toooootal = 0;
                    foreach ($darr3 as $ddd3) {
                        if ($d3['account_gp_id'] == $ddd3['account_gp_id'])
                            $toooootal += $ddd3['total'];
                    }
                    array_push($darr4, [
                        "account_gp_id" => $d3['account_gp_id'],
                        "total" => $toooootal,
                        "account_gp" => ["name" => $d3['account_name']],
                    ]);
                }
            }


            if (count($darr4) > 0)
                array_push($res2, [
                    "day" => $day,
                    "movements" => $darr4
                ]);
        }



        $total = intval($box) - intval($outbox);

        return  $this->sendResponse(
            [
                'box' =>  $total,
                'res' => $res,
                'res2' => $res2,
                'days' => $days,
                'boxCurrent' =>  intval($boxCurrent),
                'outboxEnd' =>  intval($outboxEnd)

            ],
            "here are your receives!",
            ApiCode::SUCCESS,
            0
        );
    }
}
