<?php

namespace App\Http\Controllers\Client;

use App\Http\Controllers\Controller;
use App\Models\Bank;
use App\Models\BankBuyCash;
use App\Models\BankCash;
use App\Models\BankModification;
use App\Models\BankProcess;
use App\Models\BankSafeTransfer;
use App\Models\BankTransfer;
use App\Models\Branch;
use App\Models\BuyBill;
use App\Models\BuyBillElement;
use App\Models\BuyCash;
use App\Models\Capital;
use App\Models\Cash;
use App\Models\Company;
use App\Models\EmployeeCash;
use App\Models\Expense;
use App\Models\ExtraSettings;
use App\Models\OuterClient;
use App\Models\Product;
use App\Models\Safe;
use App\Models\SafeBankTransfer;
use App\Models\SaleBill;
use App\Models\SaleBillElement;
use App\Models\Store;
use App\Models\Supplier;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;

class ReportController extends Controller
{
    public function __construct()
    {
        $this->middleware('permission:تقارير عامة');
    }
    
    public function reports()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        return view('client.reports.reports', compact('company', 'company_id'));
    }

    public function get_report1()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $outer_clients = $company->outerClients;
        return view('client.reports.report1', compact('outer_clients', 'company', 'company_id'));
    }

    public function post_report1(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $outer_client_id = $request->outer_client_id;
        if ($outer_client_id == "all") {
            $saleBills = $company->sale_bills; //
        } else {
            $outer_client_k = OuterClient::FindOrFail($outer_client_id);
            $saleBills = $outer_client_k->saleBills;
        }
        $outer_clients = $company->outerClients;
        return view('client.reports.report1', compact('outer_clients', 'company', 'company_id', 'saleBills', 'currency', 'outer_client_id'));
    }

    public function get_report2()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $products = $company->products;
        return view('client.reports.report2', compact('products', 'company'));
    }

    public function post_report2(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $product_id = $request->product_id;
        if ($product_id == "all") {
            $saleBills = $company->sale_bills;
        } else {
            $saleBillElements = SaleBillElement::where('product_id', $product_id)->get();
            $saleBills = array();
            foreach ($saleBillElements as $element) {
                $saleBill = $element->SaleBill;
                array_push($saleBills, $saleBill);
            }
            $saleBills = array_unique($saleBills);
        }
        $products = $company->products;
        return view('client.reports.report2',
            compact('products', 'company', 'company_id', 'saleBills', 'currency', 'product_id'));
    }

    public function get_report3()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $suppliers = $company->suppliers;
        return view('client.reports.report3', compact('suppliers', 'company'));
    }

    public function post_report3(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $supplier_id = $request->supplier_id;
        if ($supplier_id == "all") {
            $buyBills = $company->buy_bills;
        } else {
            $supplier_k = Supplier::FindOrFail($supplier_id);
            $buyBills = $supplier_k->buyBills;
        }
        $suppliers = $company->suppliers;
        return view('client.reports.report3', compact('suppliers', 'company', 'company_id', 'buyBills', 'currency', 'supplier_id'));
    }

    public function get_report4()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $products = $company->products;
        return view('client.reports.report4', compact('products', 'company'));
    }

    public function post_report4(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $product_id = $request->product_id;
        if ($product_id == "all") {
            $buyBills = $company->buy_bills;
        } else {
            $buyBillElements = buyBillElement::where('product_id', $product_id)->get();
            $buyBills = array();
            foreach ($buyBillElements as $element) {
                $buyBill = $element->buyBill;
                array_push($buyBills, $buyBill);
            }
            $buyBills = array_unique($buyBills);
        }
        $products = $company->products;
        return view('client.reports.report4',
            compact('products', 'company', 'company_id', 'buyBills', 'currency', 'product_id'));
    }

    public function get_report5()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $outer_clients = $company->outerClients;
        return view('client.reports.report5', compact('outer_clients', 'company'));
    }

    public function post_report5(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $outer_client_id = $request->outer_client_id;

        if ($outer_client_id == "all") {

            $result = $company->outerClients;

        } else {

            $result = OuterClient::where('company_id',$company_id)->FindOrFail($outer_client_id);
        }

             return response()->json(['result'=>$result]);
    }

    public function get_report6()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $suppliers = $company->suppliers;
        return view('client.reports.report6', compact('suppliers', 'company'));
    }

    public function post_report6(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $supplier_id = $request->supplier_id;
        if ($supplier_id == "all") {
            $Suppliers = $company->suppliers;
            $balances = array();
            foreach ($Suppliers as $supplier) {
                $supplier_balance = $supplier->balance;
                array_push($balances, $supplier_balance);
            }
            $total_balances = array_sum($balances);
        } else {
            $Suppliers = Supplier::where('id', $supplier_id)->get();
            $total_balances = "";
        }
        $suppliers = $company->suppliers;
        return view('client.reports.report6',
            compact('Suppliers', 'company', 'total_balances', 'company_id', 'suppliers', 'currency', 'supplier_id'));
    }

    public function get_report7()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $safes = $company->safes;
        return view('client.reports.report7', compact('safes', 'company'));
    }

    public function post_report7(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $safe_id = $request->safe_id;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        if ($safe_id == "all") {
            if (empty($from_date) || empty($to_date)) {
                $capitals = Capital::with(['client','safe'])->where('company_id', $company_id)->get();
            } else {
                $capitals = Capital::with(['client','safe'])->where('company_id', $company_id)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        } else {
            if (empty($from_date) || empty($to_date)) {
                $capitals = Capital::with(['client','safe'])->where('safe_id', $safe_id)->get();
            } else {
                $capitals = Capital::with(['client','safe'])->where('safe_id', $safe_id)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        }

        return response()->json(['capitals'=>$capitals]);
    }

    public function get_report8()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $products = $company->products;
        return view('client.reports.report8', compact('products', 'company'));
    }

    public function post_report8(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $product_id = $request->product_id;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        if ($product_id == "all") {
            if (empty($from_date) || empty($to_date)) {
                $buyBills = $company->buy_bills;
            } else {
                $buyBills = BuyBill::where('company_id', $company_id)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        } else {

            if (empty($from_date) || empty($to_date)) {
                $buyBillElements = BuyBillElement::where('product_id', $product_id)->get();
                $buyBills = array();
                foreach ($buyBillElements as $element) {
                    $buyBill = $element->buyBill;
                    array_push($buyBills, $buyBill);
                }
                $buyBills = array_unique($buyBills);
            } else {
                $buyBillElements = BuyBillElement::where('product_id', $product_id)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
                $buyBills = array();
                foreach ($buyBillElements as $element) {
                    $buyBill = $element->buyBill;
                    array_push($buyBills, $buyBill);
                }
                $buyBills = array_unique($buyBills);
            }
        }
        $products = $company->products;
        return view('client.reports.report8',
            compact('products', 'company', 'buyBills', 'company_id', 'from_date', 'to_date', 'currency', 'product_id'));
    }

    public function get_report9()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $products = $company->products;
        return view('client.reports.report9', compact('products', 'company'));
    }

    public function post_report9(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $product_id = $request->product_id;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        if ($product_id == "all") {
            if (empty($from_date) || empty($to_date)) {
                $buyBills = $company->buy_bills;
            } else {
                $buyBills = BuyBill::where('company_id', $company_id)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        } else {

            if (empty($from_date) || empty($to_date)) {
                $buyBillElements = BuyBillElement::where('product_id', $product_id)->get();
                $buyBills = array();
                foreach ($buyBillElements as $element) {
                    $buyBill = $element->buyBill;
                    array_push($buyBills, $buyBill);
                }
                $buyBills = array_unique($buyBills);
            } else {
                $buyBillElements = BuyBillElement::where('product_id', $product_id)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
                $buyBills = array();
                foreach ($buyBillElements as $element) {
                    $buyBill = $element->buyBill;
                    array_push($buyBills, $buyBill);
                }
                $buyBills = array_unique($buyBills);
            }
        }
        $products = $company->products;
        return view('client.reports.report9',
            compact('products', 'company', 'buyBills', 'company_id', 'from_date', 'to_date', 'currency', 'product_id'));
    }


    public function get_report10()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $products = $company->products;
        return view('client.reports.report10', compact('products', 'company'));
    }

    public function post_report10(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $product_id = $request->product_id;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        if ($product_id == "all") {
            if (empty($from_date) || empty($to_date)) {
                $saleBills = $company->sale_bills;
            } else {
                $saleBills = SaleBill::where('company_id', $company_id)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        } else {
            if (empty($from_date) || empty($to_date)) {
                $saleBillElements = SaleBillElement::where('product_id', $product_id)->get();
                $saleBills = array();
                foreach ($saleBillElements as $element) {
                    $saleBill = $element->saleBill;
                    array_push($saleBills, $saleBill);
                }
                $saleBills = array_unique($saleBills);
            } else {
                $saleBillElements = SaleBillElement::where('product_id', $product_id)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
                $saleBills = array();
                foreach ($saleBillElements as $element) {
                    $saleBill = $element->saleBill;
                    array_push($saleBills, $saleBill);
                }
                $saleBills = array_unique($saleBills);
            }
        }
        $products = $company->products;
        return view('client.reports.report10',
            compact('products', 'company', 'saleBills', 'company_id', 'from_date', 'to_date', 'currency', 'product_id'));
    }

    public function get_report11()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $outer_clients = $company->outerClients;
        return view('client.reports.report11', compact('outer_clients', 'company'));
    }


    public function post_report11(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $outer_client_id = $request->outer_client_id;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        if ($outer_client_id == "all") {
            if (empty($from_date) || empty($to_date)) {
                $cashs = Cash::where('company_id', $company_id)
                    ->where('amount', '>', 0)->get();
            } else {
                $cashs = Cash::where('company_id', $company_id)
                    ->where('amount', '>', 0)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        } else {
            if (empty($from_date) || empty($to_date)) {
                $cashs = Cash::where('outer_client_id', $outer_client_id)->where('amount', '>', 0)->get();
            } else {
                $cashs = Cash::where('outer_client_id', $outer_client_id)
                    ->where('amount', '>', 0)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        }
        $outer_clients = $company->outerClients;
        return view('client.reports.report11',
            compact('outer_clients', 'company', 'cashs', 'company_id', 'from_date', 'to_date', 'currency', 'outer_client_id'));
    }


    public function get_report12()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $suppliers = $company->suppliers;
        return view('client.reports.report12', compact('suppliers', 'company'));
    }


    public function post_report12(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $supplier_id = $request->supplier_id;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        if ($supplier_id == "all") {
            if (empty($from_date) || empty($to_date)) {
                $buy_cashs = BuyCash::where('company_id', $company_id)
                    ->where('amount', '>', 0)->get();
            } else {
                $buy_cashs = BuyCash::where('company_id', $company_id)
                    ->where('amount', '>', 0)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        } else {
            if (empty($from_date) || empty($to_date)) {
                $buy_cashs = BuyCash::where('supplier_id', $supplier_id)->where('amount', '>', 0)->get();
            } else {
                $buy_cashs = BuyCash::where('supplier_id', $supplier_id)
                    ->where('amount', '>', 0)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        }
        $suppliers = $company->suppliers;
        return view('client.reports.report12',
            compact('suppliers', 'company', 'buy_cashs', 'company_id', 'from_date', 'to_date', 'currency', 'supplier_id'));
    }

    public function get_report13()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);

        return view('client.reports.report13', compact('company'));
    }

    public function post_report13(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        if (empty($from_date) || empty($to_date)) {
            $sale_bills = SaleBill::active()->with(['client','outerClient'])->where('company_id', $company_id)->get();
        } else {
            $sale_bills = SaleBill::active()->with(['client','outerClient'])->where('company_id', $company_id)->whereBetween('date', [$from_date, $to_date])->get();
        }
        
        return response()->json(['sale_bills'=>$sale_bills]);
    }

    public function get_report14()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $fixed_expenses = $company->fixed_expenses;
        return view('client.reports.report14', compact('fixed_expenses', 'company'));
    }

    public function post_report14(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        $fixed_expense = $request->fixed_expense;
        if ($fixed_expense == "all") {
            if (empty($from_date) || empty($to_date)) {
                $expenses = Expense::with(['client','safe','fixed','bank'])->where('company_id', $company_id)->get();
            } else {
                $expenses = Expense::with(['client','safe','fixed','bank'])->where('company_id', $company_id)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        } else {
            if (empty($from_date) || empty($to_date)) {
                $expenses = Expense::with(['client','safe','fixed','bank'])->where('fixed_expense', $fixed_expense)->get();
            } else {
                $expenses = Expense::with(['client','safe','fixed','bank'])->where('fixed_expense', $fixed_expense)
                    ->whereBetween('created_at', [$from_date, $to_date])->get();
            }
        }

        return response()->json(['expenses'=>$expenses]);
    }

    public function get_report15()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $banks = $company->banks;
        return view('client.reports.report15', compact('banks', 'company'));
    }

    public function post_report15(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        $bank_id = $request->bank_id;
        if (empty($from_date) || empty($to_date)) {
            $bank_k = Bank::FindOrFail($bank_id);
            $bank_modifications = BankModification::with('client')->where('bank_id', $bank_id)->get();
            $bank_processes = BankProcess::with('client')->where('bank_id', $bank_id)->get();
            $bank_transfers = BankTransfer::with(['withdrawal','deposit','client'])->where('withdrawal_bank', $bank_id)->orwhere('deposit_bank', $bank_id)->get();
            $bank_safe_transfers = BankSafeTransfer::with(['safe','client'])->where('bank_id', $bank_id)->get();
            $safe_bank_transfers = SafeBankTransfer::with(['safe','client'])->where('bank_id', $bank_id)->get();
            $employees_bank_cashs = EmployeeCash::with(['employee','client'])->where('bank_id', $bank_id)->get();

            $bank_cash = BankCash::with(['client','outerClient'])->where('bank_id', $bank_id)->get();
            $bank_buy_cash = BankBuyCash::with(['supplier','client'])->where('bank_id', $bank_id)->get();

            $expenses = Expense::with(['client','safe','fixed','bank'])->where('company_id', $company_id)->where('bank_id', $bank_id)->get();


        } else {
            $bank_k = Bank::FindOrFail($bank_id);
            $bank_modifications = BankModification::with('client')->where('bank_id', $bank_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();
            $bank_processes = BankProcess::with('client')->where('bank_id', $bank_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();
            $bank_transfers = BankTransfer::with(['withdrawal','deposit','client'])->where('withdrawal_bank', $bank_id)
                ->orwhere('deposit_bank', $bank_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();
            $bank_safe_transfers = BankSafeTransfer::with(['safe','client'])->where('bank_id', $bank_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();
            $safe_bank_transfers = SafeBankTransfer::with(['safe','client'])->where('bank_id', $bank_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();
            $employees_bank_cashs = EmployeeCash::with(['employee','client'])->where('bank_id', $bank_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();

            $bank_cash = BankCash::with(['client','outerClient'])->where('bank_id', $bank_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();
            $bank_buy_cash = BankBuyCash::with(['supplier','client'])->where('bank_id', $bank_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();

                $expenses = Expense::with(['client','safe','fixed','bank'])->where('company_id', $company_id)->where('bank_id', $bank_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();

        }

        return response()->json(['bank_modifications'=>$bank_modifications,'bank_processes'=>$bank_processes,'bank_transfers'=>$bank_transfers,'bank_safe_transfers'=>$bank_safe_transfers,'safe_bank_transfers'=>$safe_bank_transfers,'bank_cash'=>$bank_cash,'bank_buy_cash'=>$bank_buy_cash,'bank_k'=>$bank_k,'expenses'=>$expenses,'employees_bank_cashs'=>$employees_bank_cashs]);

    }

    public function get_report16()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        return view('client.reports.report16', compact('company'));
    }

    public function post_report16(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();

        $currency = $extra_settings->currency;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        $banks = $company->banks;
        $safes = $company->safes;
        $outerClients = $company->outerClients;
        $suppliers = $company->suppliers;
        $employees = $company->employees;
        $products = $company->products;
        $stores =   Store::with('products')->where('company_id', $company_id)->get();
        
        if(empty($from_date) || empty($to_date)){

    
            $sale_bills = $company->sale_bills->where('status','done'); 
            $buy_bills = $company->buy_bills->where('status','done');

            // inside to safe
            $cashs = $company->cashs->where('amount', '>', 0); //selling and client pay
            $bank_safe_transfer = $company->bank_safe_transfer; // fromBanksToSafes
            $capitals = $company->capitals;
            $suppliersAdvance = $company->buy_cashs->where('amount','<', 0); // fromBanksToSuppliers

            // outside to safe

            $buy_cashs = $company->buy_cashs->where('amount', '>', 0); //buy
            $clientAdvance = $company->cashs->where('amount', '<', 0); //selling and client pay
            $safe_bank_transfer = $company->safe_bank_transfer; // fromSafesToBanks

            $safe_expenses = $company->expenses->where('safe_id','!=',null);
            $bank_expenses = $company->expenses->where('bank_id','!=',null);

            $employees_cashs = EmployeeCash::with(['employee','client','safe'])->where('company_id', $company_id)->where('safe_id','!=','')->get();
            $employees_banks = EmployeeCash::with(['employee','client','bank'])->where('company_id', $company_id)->where('bank_id','!=','')->get();


            // inside banks 
            $bank_cash = $company->bank_cash; //selling
            $deposit = $company->bank_processes->where('process_type','إيداع') ;//deposit
            // outside banks 
            $bank_buy_cash = $company->bank_buy_cash; //buy
            $withdrawal = $company->bank_processes->where('process_type','سحب') ;//widthral


       
        } else {

            $outerClients = $company->outerClients;
            $suppliers = $company->suppliers;
    
            $sale_bills = $company->sale_bills->where('status','done')->whereBetween('created_at', [$from_date, $to_date]); 
            $buy_bills = $company->buy_bills->where('status','done')->whereBetween('created_at', [$from_date, $to_date]);

            // inside to safe

            $cashs = $company->cashs->where('amount', '>', 0)->whereBetween('created_at', [$from_date, $to_date]); //selling and client pay
            $bank_safe_transfer = $company->bank_safe_transfer->whereBetween('created_at', [$from_date, $to_date]); // fromBanksToSafes
            $capitals = $company->capitals->whereBetween('created_at', [$from_date, $to_date]);
            $suppliersAdvance = $company->buy_cashs->where('amount','<', 0)->whereBetween('created_at', [$from_date, $to_date]); // fromBanksToSuppliers

            // outside to safe

            $buy_cashs = $company->buy_cashs->where('amount', '>', 0)->whereBetween('created_at', [$from_date, $to_date]); //buy
            $clientAdvance = $company->cashs->where('amount', '<', 0)->whereBetween('created_at', [$from_date, $to_date]); //selling and client pay
            $safe_bank_transfer = $company->safe_bank_transfer->whereBetween('created_at', [$from_date, $to_date]); // fromSafesToBanks
           
            $safe_expenses = $company->expenses->where('safe_id','!=',null)->whereBetween('created_at', [$from_date, $to_date]);
            $bank_expenses = $company->expenses->where('bank_id','!=',null)->whereBetween('created_at', [$from_date, $to_date]);

            $employees_cashs = $company->employees_cashs->whereBetween('created_at', [$from_date, $to_date]);

            $employees_cashs = EmployeeCash::with(['employee','client','safe'])->where('company_id', $company_id)->where('safe_id','!=','')->whereBetween('created_at', [$from_date, $to_date])->get();
            $employees_banks = EmployeeCash::with(['employee','client','bank'])->where('company_id', $company_id)->where('bank_id','!=','')->whereBetween('created_at', [$from_date, $to_date])->get();


            // inside banks 

            $bank_cash = $company->bank_cash->whereBetween('created_at', [$from_date, $to_date]); //selling
            $deposit = $company->bank_processes->where('process_type','إيداع')->whereBetween('created_at', [$from_date, $to_date]) ;//deposit
            // outside banks 


            $bank_buy_cash = $company->bank_buy_cash->whereBetween('created_at', [$from_date, $to_date]); //buy
            $withdrawal = $company->bank_processes->where('process_type','سحب')->whereBetween('created_at', [$from_date, $to_date]); //widthral 


        // $products = Product::where('company_id', $company_id)->where('balance', '>', 0)->get();

        
        }

        return response()->json([
        'outerClients'=>$outerClients,'suppliers'=>$suppliers,
        'sale_bills'=>$sale_bills,'buy_bills'=>$buy_bills,'cashs'=>$cashs,'buy_cashs'=>$buy_cashs,
        'capitals'=>$capitals,'bank_expenses'=>$bank_expenses,'safe_expenses'=>$safe_expenses,
        'banks'=>$banks,'safes'=>$safes,'bank_cash'=>$bank_cash,
        'deposit'=>$deposit,'bank_buy_cash'=>$bank_buy_cash,'withdrawal'=>$withdrawal,
        'bank_safe_transfer'=>$bank_safe_transfer,'safe_bank_transfer'=>$safe_bank_transfer,
        'suppliersAdvance'=>$suppliersAdvance,'clientAdvance'=>$clientAdvance,'employees_cashs'=>$employees_cashs,
        'employees_banks'=>$employees_banks,
        'products' => $products,'stores'=>$stores,
        'employees'=>$employees 

     ]);
    }

    public function get_report17()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        return view('client.reports.report17', compact('company'));
    }

    public function post_report17(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        $type = $request->type;
        if (empty($from_date) && empty($to_date)) {
            $cashs = $company->cashs;
            $buy_cashs = $company->buy_cashs;
            $capitals = $company->capitals;
            $expenses = $company->expenses;
        } else {
            
             $cashs = Cash::with([
            'salebill'=>function($query){
            $query->select('sale_bill_number','id');
           }
           ])->where('company_id', $company_id)
                ->whereBetween('created_at', [$from_date, $to_date])->get();
            $buy_cashs = BuyCash::where('company_id', $company_id)
                ->whereBetween('created_at', [$from_date, $to_date])->get();
            $capitals = Capital::where('company_id', $company_id)
                ->whereBetween('created_at', [$from_date, $to_date])->get();
            $expenses = Expense::where('company_id', $company_id)
                ->whereBetween('created_at', [$from_date, $to_date])->get();
        }
        // اجمالى الخزن
        $safes = $company->safes;
        $safes_balances = 0;
        foreach ($safes as $safe) {
            $safes_balances = $safes_balances + $safe->balance;
        }
        return view('client.reports.report17',
            compact('company', 'company_id', 'safes_balances', 'type', 'from_date', 'cashs', 'buy_cashs', 'expenses', 'capitals', 'to_date', 'currency'));
    }

    public function get_report18()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        return view('client.reports.report18', compact('company'));
    }

    public function post_report18(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $extra_settings = ExtraSettings::where('company_id', $company_id)->first();
        $currency = $extra_settings->currency;

        $count = $request->count;
        $submit = $request->submit;
        if (isset($submit) && !empty($submit) && $submit == "best_sales") {
            $products = Product::with('unit')->where('company_id', $company_id)->get();
            $products_arr = array();
            $count_arr = array();
            foreach ($products as $product) {
                $sum = 0;
                $product_id = $product->id;
                array_push($products_arr, $product);
                $sel = SaleBillElement::where('product_id', $product_id)->get();
                foreach($sel as $element){
                    $sum +=$element->elements_unit->quantity * $element->quantity ;
                }

                array_push($count_arr, $sum);
            }
            $counter = count($products_arr);
            $final_sales = array();
            for ($j = 0; $j < $counter; $j++) {
                $new_array = array('product_id' => $products_arr[$j], 'count' => $count_arr[$j]);
                array_push($final_sales, $new_array);
            }
            array_multisort(array_column($final_sales, 'count'), SORT_DESC, SORT_NATURAL | SORT_FLAG_CASE, $final_sales);

                return response()->json(['result'=>$final_sales]);

        } elseif (isset($submit) && !empty($submit) && $submit == "best_profits") {

            $products = Product::with('unit')->where('company_id', $company_id)->get();
            $products_arr = array();
            $profit_arr = array();
            foreach ($products as $product) {
                $sum = 0;
                $profit = 0;
                array_push($products_arr, $product);
                $product_id = $product->id;

                $quantity_price = SaleBillElement::where('product_id', $product_id)->get();

                foreach($quantity_price as $element){
                    
                    $profit += (($element->quantity ) * (($element->product_price) - $product->purchasing_price * $element->elements_unit->quantity )); 


                }
                array_push($profit_arr, $profit);
            }

            $counter = count($products_arr);
            $final_profits = array();
            for ($j = 0; $j < $counter; $j++) {
                $new_array = array('product_id' => $products_arr[$j], 'count' => $profit_arr[$j]);
                array_push($final_profits, $new_array);
            }
            array_multisort(array_column($final_profits, 'count'), SORT_DESC, SORT_NATURAL | SORT_FLAG_CASE, $final_profits);

            return response()->json(['result'=>$final_profits]);

        } elseif (isset($submit) && !empty($submit) && $submit == "running_out") {

            $running_out = Product::where('company_id',$company_id)
            ->whereColumn('balance','<=', 'min_balance')
            ->where('balance','>','0')
            ->get();

            return response()->json(['result'=>$running_out->toArray()]);


        }
    }

    public function get_report19()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $stores = $company->stores;
        $products = Product::where('company_id', $company_id)->get();
        return view('client.reports.report19', compact('stores', 'products', 'company_id', 'company'));
    }

    public function post_report19(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $product_id = $request->product_id;
        $product_k = Product::with('unit')->FindOrFail($product_id);
        $buy_elements = BuyBillElement::where('product_id', $product_k->id)->get();
        $total_buy_elements = 0;
        foreach ($buy_elements as $buy_element) {
            $total_buy_elements +=  $buy_element->quantity * $buy_element->elements_unit->quantity ;
        }
        $sale_elements = SaleBillElement::where('product_id', $product_k->id)->get();
        $total_sold = 0;
        $sell_price=0;
        $item_inv_price=0;
        $item_sales= $sale_elements->sum('quantity_price');
        $item_earn = 0;
        foreach ($sale_elements as $sale_element) {
            $total_sold += $sale_element->quantity * $sale_element->elements_unit->quantity;
            $item_earn += (($sale_element->quantity ) * (($sale_element->product_price) - $product_k->purchasing_price * $sale_element->elements_unit->quantity )); 

        }

        return response()->json(['item_sales'=>$item_sales ,
        'total_buy_elements' => $total_buy_elements, 
        'total_sold' => $total_sold, 'item_earn' => $item_earn,'product'=>$product_k]);

      
    }

    public function get_report20()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $safes = $company->safes;
        return view('client.reports.report20', compact('safes', 'company_id', 'company'));
    }

    public function post_report20(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $safes = $company->safes;
        $safe_id = $request->safe_id;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        $safe_k = Safe::FindOrFail($safe_id);

        if (empty($from_date) || empty($to_date)) {
            $capitals = Capital::with('client')->where('safe_id', $safe_id)->get();
            $bank_safe_transfers = BankSafeTransfer::with(['bank','client'])->where('safe_id', $safe_id)->get();
            $safe_bank_transfers = SafeBankTransfer::with(['bank','client'])->where('safe_id', $safe_id)->get();

            $cashs = Cash::with(['client','outerClient','salebill'])->where('safe_id', $safe_id)->get();
            $buy_cashs = BuyCash::with(['supplier','client','buybill'])->where('safe_id', $safe_id)->get();

            $employees_cashs = EmployeeCash::with(['employee','client'])->where('safe_id', $safe_id)->get();
            $expenses = Expense::with(['client','fixed'])->where('safe_id', $safe_id)->get();

        } else {
            
            $capitals = Capital::with('client')->where('safe_id', $safe_id)->whereBetween('created_at', [$from_date, $to_date])->get();
            $bank_safe_transfers = BankSafeTransfer::with(['bank','client'])->where('safe_id', $safe_id)->whereBetween('created_at', [$from_date, $to_date])->get();
            $safe_bank_transfers = SafeBankTransfer::with(['bank','client'])->where('safe_id', $safe_id)->whereBetween('created_at', [$from_date, $to_date])->get();

            $cashs = Cash::with(['client','outerClient','salebill'])->where('safe_id', $safe_id)->whereBetween('created_at', [$from_date, $to_date])->get();
            $buy_cashs = BuyCash::with(['supplier','client','buybill'])->where('safe_id', $safe_id)->whereBetween('created_at', [$from_date, $to_date])->get();

            $employees_cashs = EmployeeCash::with(['employee','client'])->where('safe_id', $safe_id)->whereBetween('created_at', [$from_date, $to_date])->get();
            $expenses = Expense::with(['client','fixed'])->where('safe_id', $safe_id)->whereBetween('created_at', [$from_date, $to_date])->get();

        }

        return response()->json(['capitals'=>$capitals,'bank_safe_transfers'=>$bank_safe_transfers,'safe_bank_transfers'=>$safe_bank_transfers,'cashs'=>$cashs,'buy_cashs'=>$buy_cashs,'safe_k'=>$safe_k,'employees_cashs'=>$employees_cashs,'expenses'=>$expenses,'capitals'=>$capitals ]);


    }

    public function get_report21()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $branches = $company->branches;
        return view('client.reports.report21', compact('branches', 'company_id', 'company'));
    }

    public function post_report21(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $branches = $company->branches;
        $branch_id = $request->branch_id;
        $branch_k = Branch::with('safe')->FindOrFail($branch_id);

        $stores = Store::where('company_id',$company_id)->where('branch_id', $branch_id)->get();
        $safes = Safe::where('company_id',$company_id)->where('branch_id', $branch_id)->get();

        foreach ($safes as $safe) {
            $bank_safe_transfers = BankSafeTransfer::where('safe_id', $safe->id)->get();
            $safe_bank_transfers = SafeBankTransfer::where('safe_id', $safe->id)->get();
            $buy_cashs = BuyCash::where('safe_id', $safe->id)->get();
            $capitals = Capital::where('safe_id', $safe->id)->get();
            $cashs = Cash::where('safe_id', $safe->id)->get();
            $employees_cashs = EmployeeCash::where('safe_id', $safe->id)->get();
            $expenses = Expense::where('safe_id', $safe->id)->get();
        }
        foreach ($stores as $store) {
            $products_k = Product::where('company_id',$company_id)->where('store_id', $store->id)->get();
        }

        return view('client.reports.report21',
            compact('branch_id', 'branch_k', 'company_id', 'company',
                'bank_safe_transfers', 'safe_bank_transfers', 'branches', 'products_k'
                , 'capitals', 'cashs', 'employees_cashs', 'buy_cashs', 'expenses'));
    }

    public function get_report22()
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);
        $safes = $company->safes;
        return view('client.reports.report22', compact('safes', 'company_id', 'company'));
    }


    public function post_report22(Request $request)
    {
        $company_id = Auth::user()->company_id;
        $company = Company::FindOrFail($company_id);

        $from_date = $request->from_date;
        $to_date = $request->to_date;

        if (empty($from_date) || empty($to_date)) {

            $capitals = Capital::with('client')->where('company_id', $company_id)->get();

            $bank_safe_transfers = BankSafeTransfer::with(['bank','client','safe'])->where('company_id', $company_id)->get();
            $safe_bank_transfers = SafeBankTransfer::with(['bank','client','safe'])->where('company_id', $company_id)->get();

            $cashs = Cash::with(['client','outerClient','salebill'])->where('company_id', $company_id)->get();
            $buy_cashs = BuyCash::with(['supplier','client','buybill'])->where('company_id', $company_id)->get();

            $employees_cashs = EmployeeCash::with(['employee','client','safe'])->where('company_id', $company_id)->where('safe_id','!=','')->get();
            $expenses_safe = Expense::with(['client','fixed'])->where('company_id', $company_id)->where('safe_id','!=','')->get();

        } else {
            
            $capitals = Capital::with('client')->where('company_id', $company_id)->whereBetween('created_at', [$from_date, $to_date])->get();
            $bank_safe_transfers = BankSafeTransfer::with(['bank','client','safe'])->where('company_id', $company_id)->whereBetween('created_at', [$from_date, $to_date])->get();
            $safe_bank_transfers = SafeBankTransfer::with(['bank','client','safe'])->where('company_id', $company_id)->whereBetween('created_at', [$from_date, $to_date])->get();

            $cashs = Cash::with(['client','outerClient','salebill'])->where('company_id', $company_id)->whereBetween('created_at', [$from_date, $to_date])->get();
            $buy_cashs = BuyCash::with(['supplier','client','buybill'])->where('company_id', $company_id)->whereBetween('created_at', [$from_date, $to_date])->get();

            $employees_cashs = EmployeeCash::with(['employee','client','safe'])->where('company_id', $company_id)->where('safe_id','!=','')->whereBetween('created_at', [$from_date, $to_date])->get();
            $expenses_safe = Expense::with(['client','fixed'])->where('company_id', $company_id)->where('safe_id','!=','')->whereBetween('created_at', [$from_date, $to_date])->get();

        }


        if (empty($from_date) || empty($to_date)) {

            $bank_modifications = BankModification::with('client')->where('company_id', $company_id)->get();
            $bank_processes = BankProcess::with('client')->where('company_id', $company_id)->get();
            
            $bank_transfers = BankTransfer::with(['withdrawal','deposit','client'])->get();

            $bank_cash = BankCash::with(['client','outerClient'])->where('company_id', $company_id)->get();
            $bank_buy_cash = BankBuyCash::with(['supplier','client'])->where('company_id', $company_id)->get();
            $employees_bank_cashs = EmployeeCash::with(['employee','client','bank'])->where('company_id', $company_id)->where('bank_id','!=','')->get();
            $expenses_bank = Expense::with(['client','fixed','bank'])->where('company_id', $company_id)->where('bank_id','!=','')->get();


        } else {

            $bank_modifications = BankModification::with('client')->where('company_id', $company_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();
            $bank_processes = BankProcess::with('client')->where('company_id', $company_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();

            $bank_transfers = BankTransfer::with(['withdrawal','deposit','client'])
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();

            $bank_cash = BankCash::with(['client','outerClient'])->where('company_id', $company_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();

            $bank_buy_cash = BankBuyCash::with(['supplier','client'])->where('company_id', $company_id)
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();

            $employees_bank_cashs = EmployeeCash::with(['employee','client','bank'])->where('company_id', $company_id)->where('bank_id','!=','')
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();

            $expenses_bank = Expense::with(['client','fixed','bank'])->where('company_id', $company_id)->where('bank_id','!=','')
                ->whereBetween('created_at', [$from_date, $to_date])
                ->get();

        }

        return response()->json([
        'capitals'=>$capitals,'bank_safe_transfers'=>$bank_safe_transfers,
        'safe_bank_transfers'=>$safe_bank_transfers,'cashs'=>$cashs,'buy_cashs'=>$buy_cashs,
        'employees_cashs'=>$employees_cashs,'expenses_safe'=>$expenses_safe ,

        'capitals'=>$capitals,'bank_modifications'=>$bank_modifications,'bank_processes'=>$bank_processes,
        'bank_transfers'=>$bank_transfers,'bank_cash'=>$bank_cash,
        'bank_buy_cash'=>$bank_buy_cash,'expenses_bank'=>$expenses_bank,
        'employees_bank_cashs'=>$employees_bank_cashs

    
    ]);


    }
}
