<?php

class ReportsController extends \BaseController
{

    /**
     * The Resutl Object
     * @var object
     */
    private $results;

    /**
     * Logo
     * @return stirng name of logo
     */
    private function logo()
    {
        return \Helper::getConfig('company_logo');
    }

    /**
     * Start Date form
     * @param  string $get
     * @return date time string
     */
    private function start($get)
    {
        return new \Carbon\Carbon($get);
    }

    /**
     * End Date form
     * @param  string $get
     * @return date time string
     */
    private function end($get)
    {
        $end = new \Carbon\Carbon($get);
        $end->addHours(23)->addMinutes(59)->addSeconds(59);
        return $end;
    }

    private function now()
    {
        return \Carbon\Carbon::now();
    }

    /**
     * Format Date
     * @param  [type] $date [description]
     * @return [type]       [description]
     */
    private function format($date)
    {
        $date = new \Carbon\Carbon($date);
        return $date->format('d/m/Y');
    }

    private function inputDate($date)
    {
        return Carbon\Carbon::createFromFormat("d/m/Y", $date);
    }
    /**
     * Get Profits
     * Get report by date and type
     * @return mix view and result
     */
    public function getProfits()
    {
        if (!UserRole::has('profit_report')) {
            return Redirect::route('dashboard');
        }
        return $this->profits('reports.income');
    }

    public function printProfits()
    {
        return $this->profits("reports.print.profits");
    }

    /**
     * Type of Profit income and expense
     * @param  string $view
     * @return mix
     */
    private function profits($view)
    {
        $view = View::make($view);
        $profits = DB::table('profits')
            ->select(DB::raw("
                    SUM(CASE WHEN type='income' THEN amount END) as totalIncome,
                    SUM(CASE WHEN type='expense' THEN amount END) as totalExpense,
                    CAST(created_at AS DATE) as byDay
                "), 'user_id', 'type', 'amount as total')
            ->groupBy('byDay')
            ->orderBy('created_at', "asc");
        if (!empty(Input::get('start_date')) && !empty(Input::get('end_date'))) {
            $start = $this->inputDate(Input::get('start_date'));
            $end = $this->inputDate(Input::get('end_date'));
            $profits->whereBetween('created_at', [$start, $end]);
        } else {
            $start = Carbon\Carbon::today();
            $start->startOfMonth();
            $end = Carbon\Carbon::today();
            $end->endOfMonth();
            $profits->whereBetween('created_at', [$start, $end]);
        }
        if (!empty(Input::get('paginate')) && Input::get('paginate') != 'all') {
            $profits = $profits->paginate(Input::get('paginate'));
        } else {
            $profits = $profits->get();
        }
        return $view->with("profits", $profits)
            ->with('start_date', $this->format($start))
            ->with('end_date', $this->format($end));
    }

    /**
     * Search Products History Report on In & Out
     * @return History Object
     */
    public function getProduct()
    {
        if (!UserRole::has('product_in_out')) {
            return Redirect::route('dashboard');
        }
        return $this->products('reports.product');
    }

    /**
     * Print Productin in and out
     * @return [type] [description]
     */
    public function printProducts()
    {
        return $this->products('reports.print.products');
    }

    /**
     * Products search and print
     * @param  sting $view name of view
     * @return mix
     */
    private function products($view)
    {
        $view = View::make($view);
        $start = $this->start(Input::get('start_date'));
        $end = $this->end(Input::get('end_date'));
        $submitted = false;
        $histories = DB::table('Histories')
            ->join('Products', 'Products.id', '=', 'Histories.product_id')
            ->leftJoin('ProductCategories', 'ProductCategories.id', '=', 'Products.product_category_id')
            ->leftJoin('Units', 'Products.unit_id', '=', 'Units.id')
            ->where('operated_on', '=', 'product')
            ->select(
                'Histories.*',
                'Products.name as pname',
                'Products.name_kh',
                'Products.sku',
                'Units.name as uname',
                'ProductCategories.name as cname',
                'Products.product_category_id as category_id'
            )->orderBy('Histories.created_at', 'DESC');

        if (Input::get('type') && Input::get('type') <> "all") {
            $histories = $histories->where('Histories.operation_type', Input::get('type'));
            $submitted = true;
        }
        if (Input::get('group') && !in_array("All", Input::get('group'))) {
            $histories = $histories->whereIn('ProductCategories.id', Input::get('group'));
            $submitted = true;
        }
        if (!empty(Input::get('start_date')) && !empty(Input::get('end_date'))) {
            $Histories = $histories->whereBetween('Histories.created_at', [$start, $end]);
            $submitted = true;
        }
        //$reports = Profit::select('id, amount, description')->where()
        $histories = $histories->get();
        if ($submitted) {
            $view->withHistories($histories);
        }
        return $view->with('start_date', $this->format($start))
            ->with('end_date', $this->format($end));
    }

    /**
     * Search Products Transfer to Stock History Report on In & Out
     * @return History Object
     */
    public function transfer()
    {
        if (!UserRole::has('transfer_history')) {
            return Redirect::route('dashboard');
        }
        return $this->getTransfer('reports.transfer');
    }

    public function printTransfer()
    {
        return $this->getTransfer('reports.print.transfer');
    }

    private function getTransfer($view)
    {
        $view = View::make($view);
        $start = $this->start(Input::get('start_date'));
        $end = $this->end(Input::get('end_date'));
        $users = User::all()->lists('first_name', 'id');
        $users['all'] = trans('sale_history.all');

        $submitted = false;
        $histories = DB::table('Histories')
            ->join('Products', 'Products.id', '=', 'Histories.product_id')
            ->leftJoin('ProductCategories', 'ProductCategories.id', '=', 'Products.product_category_id')
            ->leftJoin('Units', 'Products.unit_id', '=', 'Units.id')
            ->where('operated_on', '=', 'stock')
            ->select('Histories.*', 'Products.name as pname', 'Products.sku', 'Units.name as uname',
                'ProductCategories.name as cname', 'Products.product_category_id as category_id')
            ->orderBy('Histories.created_at', 'DESC');

        if (Input::get('type') && Input::get('type') <> "all") {
            $histories = $histories->where('Histories.operation_type', Input::get('type'));
            $submitted = true;
        }
        if (Input::get('user') && !in_array("all", Input::get('user'))) {
            $histories = $histories->whereIn('Histories.user_id', Input::get('user'));
            $submitted = true;
        }
        if (Input::get('group') && !in_array("All", Input::get('group'))) {
            $histories = $histories->whereIn('ProductCategories.id', Input::get('group'));
            $submitted = true;
        }
        if (!empty(Input::get('start_date')) && !empty(Input::get('end_date'))) {
            $Histories = $histories->whereBetween('Histories.created_at', [$start, $end]);
            $submitted = true;
        }
        //$reports = Profit::select('id, amount, description')->where()
        $histories = $histories->get();
        if ($submitted) {
            $view->withHistories($histories);
        }
        return $view->with('start_date', $this->format($start))
            ->withUsers($users)
            ->with('end_date', $this->format($end));
    }

    /**
     * Get Sale History
     * @return mix view and reuslt
     */
    public function getSaleHistory()
    {
        if (!UserRole::has('sale_history')) {
            return Redirect::route('dashboard');
        }
        return $this->saleHistory('reports.sale_history');
    }

    public function printSaleHistory()
    {
        return $this->saleHistory('reports.print.sale_history');
    }

    private function saleHistory($view)
    {
        // crete new Report
        $report = new Report;
        $this->results = $report->saleHistory()
            ->byCategory(Input::get('category'));

        // Assign the view file to $view  wiht logo file name
        $view = View::make($view);
        // Categories and Stocks Lookup
        $categories = ProductCategory::all()->lists('name', 'id');
        $categories['all'] = trans('sale_history.all');
        $stocks = Stock::all()->lists('name', 'id');
        $stocks['all'] = trans('sale_history.all');
        $users = User::all()->lists('first_name', 'id');
        $users['all'] = trans('sale_history.all');
        $view->withCategories($categories)
            ->withStocks($stocks)
            ->withUsers($users);

        if (Input::get('user') && !in_array("all", Input::get('user'))) {
            $this->results = $report->byUserLogin(Input::get('user'));
        }
        // check empty date and barcode
        if (empty(Input::get('start_date'))
            && empty(Input::get('end_date'))
            && empty(Input::get('start_barcode'))
            && empty(Input::get('end_barcode'))
            && Input::get('category')) {
            // oldest date in order
            $firstDate = Order::select('created_at')
                ->whereSold(1)
                ->orderBy('created_at', 'asc')
                ->first();
            $date = empty($firstDate) ? $this->now() : $firstDate->created_at;
            // return stock and categoies withoud date and barcode
            // with oldest date and current date
            if (!empty(Input::get('download'))) {
                return $this->results->show();
            }
            return $view->with('results',
                !empty(Input::get('print')) ? $this->results->show() : $this->results->pageLimit(20))
                ->with('start_date', $this->format($date))
                ->with('end_date', $this->format($this->now()));
        }
        // Check is not empty start and end date
        if (!empty(Input::get('start_date'))
            && !empty(Input::get('end_date'))) {
            $start = $this->start(Input::get('start_date'));
            $end = $this->end(Input::get('end_date'));
            // the start date greater than end date redirect
            if ($start > $end) {
                return Redirect::route('reportSaleHistory')
                    ->withInput()
                    ->withMessage('reports.date_warning');
            }
            // chain byDate to find between two dates
            $this->results->byDate($start, $end);
            // from date to end date
            $view->with('start_date', $this->format($start))
                ->with('end_date', $this->format($end));
        }
        // Check is not empty start and end barcode
        if (!empty(Input::get('start_barcode'))
            && !empty(Input::get('end_barcode'))) {
            if (Input::get('start_barcode') > Input::get('end_barcode')) {
                return Redirect::route("reportSaleHistory")->withInput()->withMessage('reports.barcode_warning');
            }
            // Chain byBarcode to find between two barcodes
            $this->results->byBarcode(Input::get('start_barcode'), Input::get('end_barcode'));
            $view->with('results',
                !empty(Input::get('print')) ? $this->results->show() : $this->results->pageLimit(20));
        } else {
            $view->with('results',
                !empty(Input::get('print')) ? $this->results->show() : $this->results->pageLimit(20));
        }
        if (!empty(Input::get('download'))) {
            return $this->results->show();
        }
        // return default view
        return $view;
    }

    /**
     * Download Sale History
     * @return download excel
     */
    public function downloadSaleHistoryExcel()
    {
        ob_end_clean();
        ob_start();
        $products = $this->saleHistory("reports.print.sale_history");
        Excel::create('saleHistory', function ($excel) use ($products) {
            $excel->sheet("Sale Histories", function ($sheet) use ($products) {
                $sheet->fromArray($products);
            });
        })->download('xls');
    }

    /**
     * Get Products in WareHouse
     * For Search and Display data
     * @return mix
     */
    public function getProductsWarehouse()
    {
        if (!UserRole::has('product_warehouse')) {
            return Redirect::route('dashboard');
        }
        return $this->productsWarehouse('reports.products_warehouse');
    }

    /**
     * Print Search Results
     * @return [type] [description]
     */
    public function printProductsWarehouse()
    {
        return $this->productsWarehouse('reports.print.products_warehouse');
    }

    /**
     * Production in Warehoue
     * For using in search and print
     * @param  string $view name of view
     * @return mix
     */
    private function productsWarehouse($view)
    {
        $categories = ProductCategory::all()->lists('name', 'id');
        $categories['all'] = trans('sale_history.all');
        $stocks = Stock::lists('name', 'id');
        $stocks['all'] = trans('reports.all');
        $view = View::make($view)
            ->withStocks($stocks)
            ->withLogo($this->logo())
            ->withCategories($categories);
        $report = new Report;
        if (Input::get('type') == 'stock') {
            $this->results = $report->productsInStock()
                ->byCategory(Input::get("category"));
        } else {
            $this->results = $report->productsWarehouse()
                ->byCategory(Input::get("category"));
        }
        if (!empty(Input::get('stock'))) {
            $this->results->byStock(Input::get('stock'));
        }
        // check empty date and barcode
        if (empty(Input::get('start_date'))
            && empty(Input::get('end_date'))
            && empty(Input::get('start_barcode'))
            && empty(Input::get('end_barcode'))) {
            // oldest date in product
            $lowerDate = Product::select('created_at')
                ->orderBy('created_at', 'asc')
                ->first();
            $date = empty($lowerDate) ? $this->now() : $lowerDate->created_at;
            // Return Product with category or categories
            // with oldest date and current date
            if (!empty(Input::get('download'))) {
                return $this->results->show();
            }
            return $view->with('results',
                !empty(Input::get('print')) ? $this->results->show() : $this->results->pageLimit(10))
                ->with('start_date', $this->format($date))
                ->with('end_date', $this->format($this->now()));
        }
        // Check is not empty start and end date
        if (!empty(Input::get('start_date')) && !empty(Input::get('end_date'))) {
            $start = $this->start(Input::get('start_date'));
            $end = $this->end(Input::get('end_date'));
            // the start date greater than end date redirect
            if ($start > $end) {
                return Redirect::route('productsWarehouse')
                    ->withInput()
                    ->withMessage('reports.date_warning');
            }
            // chain byDate to find between two dates
            $this->results->productByDate($start, $end);
            // from date to end date
            $view->with('start_date', $this->format($start))
                ->with('end_date', $this->format($end));
        }
        // Check is not empty start and end barcode
        if (!empty(Input::get('start_barcode')) && !empty(Input::get('end_barcode'))) {
            if (Input::get('start_barcode') > Input::get('end_barcode')) {
                return Redirect::route("productsWarehouse")
                    ->withInput()
                    ->withMessage('reports.barcode_warning');
            }
            // Chain byBarcode to find between two barcodes
            $this->results->byBarcode(Input::get('start_barcode'), Input::get('end_barcode'));
        }
        if (!empty(Input::get('download'))) {
            return $this->results->show();
        }
        $view->with('results', !empty(Input::get('print')) ? $this->results->show() : $this->results->pageLimit(20));
        return $view;
    }

    public function downloadPsExcel()
    {
        ob_end_clean();
        ob_start();
        $products = $this->productsWarehouse("reports.print.products_warehouse");
        Excel::create('productsInStock', function ($excel) use ($products) {
            $excel->sheet("Export Products", function ($sheet) use ($products) {
                $sheet->fromArray($products);
            });
        })->download('xls');
    }

    public function profitReport()
    {
        $profits = DB::table('profits')->select(DB::raw('sum(amount) as total, type, created_at, user_id'))
            ->groupBy('type')
            ->groupBy(DB::raw('CAST(created_at AS DATE)'))
            ->orderBy('created_at', "ASC");
        if (!empty(Input::get('type')) && Input::get('type') != "all") {
            $profits->where('type', Input::get('type'));
        }
        if (!empty(Input::get('start_date')) && !empty(Input::get('end_date'))) {
            $profits->whereBetween('created_at', [Input::get("start_date"), Input::get('end_date')]);
        } else {
            $startToday = Carbon\Carbon::today();
            $startToday->startOfMonth();
            $endToday = Carbon\Carbon::today();
            $endToday->endOfMonth();
            $profits->whereBetween('created_at', [$startToday, $endToday]);
        }
        if (!empty(Input::get('paginate')) && Input::get('paginate') != 'all') {
            $profits = $profits->paginate(Input::get('paginate'));
        } else {
            $profits = $profits->get();
        }
        return View::make('reports.income', [
            'profits' => $profits
        ]);
    }

    public function payments()
    {
        $users = User::where("id", "<>",
            1)->select(DB::raw("CONCAT(users.first_name, ' ', users.last_name) AS full_name, users.id"))->lists('full_name',
            'id');
        $users['all'] = "All";
        $payments = Payment::installmentCustomer()
            ->receipt()
            ->select(
                "installments.id as i_id",
                "loan_customers.full_name_kh",
                "loan_customers.full_name_en",
                "loan_customers.tel",
                "payments.*",
                'receipt_incomes.user_id'
            )->orderBy("fee_date");
        if (!empty(Input::get("start_date")) && !empty(Input::get("end_date"))) {
            $start = $this->inputDate(Input::get('start_date'));
            $start->hour(0)->minute(0)->second(0);
            $end = $this->inputDate(Input::get('end_date'));
            $end->hour(23)->minute(59)->second(59);
            $payments->whereBetween("fee_date", [$start, $end]);
        } else {
            $startToday = Carbon\Carbon::today();
            $endToday = Carbon\Carbon::today();
            $endToday->addHours(23)
                ->addMinutes(59)
                ->addSeconds(59);
            $payments->whereBetween("fee_date", [$startToday, $endToday]);
        }
        if (!empty(Input::get("user")) && Input::get("user") != "all") {
            $payments->where("receipt_incomes.user_id", Input::get("user"));
        }
        if (!empty(Input::get('paginate')) && Input::get('paginate') != 'all') {
            $payments = $payments->paginate(Input::get('paginate'));
        } else {
            $payments = $payments->get();
        }
        return View::make("reports.payments", [
            'users' => $users,
            'payments' => $payments
        ]);
    }

    public function mortgagePayments()
    {
        $users = User::where("id", "<>",
            1)->select(DB::raw("CONCAT(users.first_name, ' ', users.last_name) AS full_name, users.id"))->lists('full_name',
            'id');
        $users['all'] = "All";
        $payments = MortgagePayment::join("mortgages", "mortgages.id", "=", "mortgage_payments.mortgage_id")
                        ->join("receipt_incomes", "mortgage_payments.receipt_id", "=", "receipt_incomes.id")
                        ->where("mortgage_payments.is_paid", true)
                        ->select("mortgages.fullname","mortgages.tel","mortgage_payments.*", "receipt_incomes.total_amount");
        if (!empty(Input::get("start_date")) && !empty(Input::get("end_date"))) {
            $start = $this->inputDate(Input::get('start_date'));
            $start->hour(0)->minute(0)->second(0);
            $end = $this->inputDate(Input::get('end_date'));
            $end->hour(23)->minute(59)->second(59);
            $payments->whereBetween("mortgage_payments.fee_date", [$start, $end]);
        } else {
            $startToday = Carbon\Carbon::today();
            $endToday = Carbon\Carbon::today();
            $endToday->addHours(23)
                ->addMinutes(59)
                ->addSeconds(59);
            $payments->whereBetween("mortgage_payments.fee_date", [$startToday, $endToday]);
        }
        if (!empty(Input::get("user")) && Input::get("user") != "all") {
            $payments->where("receipt_incomes.user_id", Input::get("user"));
        }
        if (!empty(Input::get('paginate')) && Input::get('paginate') != 'all') {
            $payments = $payments->paginate(Input::get('paginate'));
        } else {
            $payments = $payments->get();
        }
        return View::make("reports.mortgage_payments", [
            'users' => $users,
            'payments' => $payments
        ]);
    }
    public function showProfit($date)
    {
        $start = new Carbon\Carbon($date);
        $end = new Carbon\Carbon($date);
        $end->addHours(23)->addMinutes(59)->addSeconds(59);
        $profits = DB::table('profits')
            ->join('users', 'users.id', '=', 'profits.user_id')
            ->whereBetween('profits.created_at', [$start, $end])
            ->whereIn('type', ['income', 'expense'])
            ->orderBy('profits.created_at', 'asc')
            ->select(
                'profits.*',
                'users.first_name',
                'users.last_name',
                'users.username'
            )
            ->get();
        return View::make('reports.show_profit', [
            'profits' => $profits,
            'start' => $start
        ]);
    }

    private function chartMonth()
    {
        $month = DB::table('installments')
            ->whereRaw('year(`in_date`) = ?', [date('Y')])
            ->select(
                DB::raw('MONTHNAME(in_date) as month'),
                DB::raw('DAYOFMONTH(in_date) as d'),
                DB::raw('year(in_date) as y'),
                DB::raw("DATE_FORMAT(in_date,'%Y-%m') as monthNum"),
                'in_date'
            )
            ->groupBy('month')->orderBy('in_date', 'asc')->get();
        return $month;
    }

    public function monthlyGraph()
    {
        $chartData = [
            'month' => [],
            'expense' => [],
            'priciple' => [],
            'income' => [],
            'rate' => []
        ];
        foreach ($this->chartMonth() as $expense) {
            $chartData['month'][] = $expense->month;
            $chartData['expense'][] = Profit::monthly($expense->in_date);
            $chartData['priciple'][] = Payment::byMonth($expense->in_date);
            $chartData['rate'][] = Payment::byMonth($expense->in_date, 'rate');
        }
        return View::make("reports.graph")->with('chartData', $chartData);

    }
}
