<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use Auth;
use Response;

class ReportsController extends Controller
{
    public $data = [];
    public function __construct(){
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Http\Response
     */

    /*
    | Sales reports
    |--------------------------------------------------------------------------
    */
    public function get_sales(Request $request)
    {
        $this->data['currency'] = env('CURRENCY');
        if (Auth::User()->group_id == 2) {
            return redirect('home');
        }
            
        //----------------- Sales Summary -----------------//
        if ($request->type == 'summary') {
            $this->data['title'] = 'Sales Summary';
            $page = 'summary';
            
            $total_count = DB::table('takeaway_orders')
                ->where('status','5')
                ->select(
                    DB::raw('SUM(total) as total_sale'),
                    DB::raw('SUM(discount) as total_discount'),
                    DB::raw('SUM(subtotal) as total_gross'),
                    DB::raw('SUM(delivery_charges) as total_delivery_charges'),
                    DB::raw('SUM(tax) as total_tax')
                );
                
            if ($request->day == 'today') {
                $total_count=$total_count->whereRaw('Date(created_at) = CURDATE()');
            }
            if ($request->day == 'yesterday') {
                $total_count=$total_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
            }
            if ($request->day == 'week') {
                $total_count=$total_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
            }
            if ($request->day == 'month') {
                $total_count=$total_count->where('created_at', '>=', Carbon::now()->subMonths(1));
            }
            if ($request->date !='') {
                $total_count = $total_count->whereDate('created_at', '=', $request->date);
            }

            $total_count=$total_count->get();
            $count =$total_count;
            
            $this->data['net_sale'] =  isset($count[0]->total_sale)?$count[0]->total_sale:0;
            $this->data['gross_sale'] = isset($count[0]->total_gross)?$count[0]->total_gross:0;
            $this->data['discount'] = isset($count[0]->total_discount)?$count[0]->total_discount:0;
            $this->data['tax'] = isset($count[0]->total_tax)?$count[0]->total_tax:0;
            $this->data['delivery_charges'] = isset($count[0]->total_delivery_charges)?$count[0]->total_delivery_charges:0;
            
            
            $summary = DB::table('takeaway_orders')
            ->where('status','5');
            if ($request->day == 'today') {
                $summary=$summary->whereRaw('Date(created_at) = CURDATE()');
            }
            if ($request->day == 'yesterday') {
                $summary=$summary->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
            }
            if ($request->day == 'week') {
                $summary=$summary->where('created_at', '>=', Carbon::now()->subWeeks(1));
            }
                
            if ($request->day == 'month') {
                $summary=$summary->where('created_at', '>=', Carbon::now()->subMonths(1));
            }
            if ($request->date !='') {
                $summary = $summary->whereDate('created_at', '=', $request->date);
            }

            $summary = $summary->select(
                DB::raw('DATE(created_at) as date'),
                DB::raw('SUM(subtotal) as total_gross'),
                DB::raw('SUM(total) as total_sales'),
                DB::raw('SUM(discount) as total_sales_discount'),
                DB::raw('SUM(delivery_charges) as total_sales_shipping_charges'),
                DB::raw('SUM(tax) as total_sales_tax')
            )
            ->groupBy('date')
            ->orderBy('id', 'desc')
            ->get();
            
            // counts
            $all_orders_count = DB::table('takeaway_orders');
            $web_orders_count = DB::table('takeaway_orders')->where('platform', 'web');
            $app_orders_count = DB::table('takeaway_orders')->where('platform', 'app');
            $all_pos_count = DB::table('takeaway_orders');
            $pos_dinein_count = DB::table('takeaway_orders')->where('order_type', 'dinein');
            $pos_takeaway_count = DB::table('takeaway_orders')->where('order_type', 'takeaway');
            $pos_delivery_count = DB::table('takeaway_orders')->where('order_type', 'delivery');
            $pos_drive_thru_count = DB::table('takeaway_orders')->where('order_type', 'drive-thru');
            $pos_complimentary_count = DB::table('takeaway_orders')->where('order_type', 'complimentary');
            $pos_staff_count = DB::table('takeaway_orders')->where('order_type', 'staff');

            if ($request->day == 'today') {
                $all_orders_count = $all_orders_count->whereRaw('Date(created_at) = CURDATE()');
                $web_orders_count = $web_orders_count->whereRaw('Date(created_at) = CURDATE()');
                $app_orders_count = $app_orders_count->whereRaw('Date(created_at) = CURDATE()');
                $all_pos_count = $all_pos_count->whereRaw('Date(created_at) = CURDATE()');
                $pos_dinein_count = $pos_dinein_count->whereRaw('Date(created_at) = CURDATE()');
                $pos_takeaway_count = $pos_takeaway_count->whereRaw('Date(created_at) = CURDATE()');
                $pos_delivery_count = $pos_delivery_count->whereRaw('Date(created_at) = CURDATE()');
                $pos_drive_thru_count = $pos_drive_thru_count->whereRaw('Date(created_at) = CURDATE()');
                $pos_complimentary_count = $pos_complimentary_count->whereRaw('Date(created_at) = CURDATE()');
                $pos_staff_count = $pos_staff_count->whereRaw('Date(created_at) = CURDATE()');
            }
            if ($request->day == 'yesterday') {
                $all_orders_count = $all_orders_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
                $web_orders_count = $web_orders_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
                $app_orders_count = $app_orders_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
                $all_pos_count = $all_pos_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
                $pos_dinein_count = $pos_dinein_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
                $pos_takeaway_count = $pos_takeaway_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
                $pos_delivery_count = $pos_delivery_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
                $pos_drive_thru_count = $pos_drive_thru_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
                $pos_complimentary_count = $pos_complimentary_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
                $pos_staff_count = $pos_staff_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
            }
            if ($request->day == 'week') {
                $all_orders_count = $all_orders_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
                $web_orders_count = $web_orders_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
                $app_orders_count = $app_orders_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
                $all_pos_count = $all_pos_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
                $pos_dinein_count = $pos_dinein_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
                $pos_takeaway_count = $pos_takeaway_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
                $pos_delivery_count = $pos_delivery_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
                $pos_drive_thru_count = $pos_drive_thru_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
                $pos_complimentary_count = $pos_complimentary_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
                $pos_staff_count = $pos_staff_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
            }
                
            if ($request->day == 'month') {
                $all_orders_count = $all_orders_count->where('created_at', '>=', Carbon::now()->subMonths(1));
                $web_orders_count = $web_orders_count->where('created_at', '>=', Carbon::now()->subMonths(1));
                $app_orders_count = $app_orders_count->where('created_at', '>=', Carbon::now()->subMonths(1));
                $all_pos_count = $all_pos_count->where('created_at', '>=', Carbon::now()->subMonths(1));
                $pos_dinein_count = $pos_dinein_count->where('created_at', '>=', Carbon::now()->subMonths(1));
                $pos_takeaway_count = $pos_takeaway_count->where('created_at', '>=', Carbon::now()->subMonths(1));
                $pos_delivery_count = $pos_delivery_count->where('created_at', '>=', Carbon::now()->subMonths(1));
                $pos_drive_thru_count = $pos_drive_thru_count->where('created_at', '>=', Carbon::now()->subMonths(1));
                $pos_complimentary_count = $pos_complimentary_count->where('created_at', '>=', Carbon::now()->subMonths(1));
                $pos_staff_count = $pos_staff_count->where('created_at', '>=', Carbon::now()->subMonths(1));
            }
            if ($request->date !='') {
                $all_orders_count = $all_orders_count->whereDate('created_at', '=', $request->date);
                $web_orders_count = $web_orders_count->whereDate('created_at', '=', $request->date);
                $app_orders_count = $app_orders_count->whereDate('created_at', '=', $request->date);
                $all_pos_count = $all_pos_count->whereDate('created_at', '=', $request->date);
                $pos_dinein_count = $pos_dinein_count->whereDate('created_at', '=', $request->date);
                $pos_takeaway_count = $pos_takeaway_count->whereDate('created_at', '=', $request->date);
                $pos_delivery_count = $pos_delivery_count->whereDate('created_at', '=', $request->date);
                $pos_drive_thru_count = $pos_drive_thru_count->whereDate('created_at', '=', $request->date);
                $pos_complimentary_count = $pos_complimentary_count->whereDate('created_at', '=', $request->date);
                $pos_staff_count = $pos_staff_count->whereDate('created_at', '=', $request->date);
            }
            
            $this->data['all_orders_count'] = $all_orders_count = $all_orders_count->count();
            $this->data['web_orders_count'] = $web_orders_count = $web_orders_count->count();
            $this->data['app_orders_count'] = $app_orders_count = $app_orders_count->count();

            $this->data['all_pos_count'] = $all_pos_count = $all_pos_count->count();
            $this->data['pos_dinein_count'] = $pos_dinein_count = $pos_dinein_count->count();
            $this->data['pos_takeaway_count'] = $pos_takeaway_count = $pos_takeaway_count->count();
            $this->data['pos_delivery_count'] = $pos_delivery_count = $pos_delivery_count->count();
            $this->data['pos_drive_thru_count'] = $pos_drive_thru_count = $pos_drive_thru_count->count();
            $this->data['pos_complimentary_count'] = $pos_complimentary_count = $pos_complimentary_count->count();
            $this->data['pos_staff_count'] = $pos_staff_count = $pos_staff_count->count();

            $this->data['summary'] = $summary;

            //csv
            if ($request->export == 'csv') {
                $filename = "sales_summary.csv";
                $handle = fopen($filename, 'w+');
                fputcsv($handle, array('Date', 'Delivery Charges', 'Discounts', 'Net Sales'));

                foreach ($summary as $row) {
                    fputcsv($handle, array($row->date, $row->total_sales_shipping_charges, $row->total_sales_discount, $row->total_sales));
                }
                fclose($handle);
                $headers = array('Content-Type' => 'text/csv');
                return Response::download($filename, 'sales_summary.csv', $headers);
            }
        }

        //----------------- Sales by Item -----------------//
        if ($request->type == 'item') {
            $this->data['title'] = 'Sales by Item';
            $page = 'item';

            $items = DB::table('takeaway_orders as o')
            ->leftJoin('takeaway_order_details as tod', 'tod.order_id', '=', 'o.id')
            ->where('o.status', '!=', '1')->where('o.payment_status','1')
            ->where('tod.item_id', '!=', '');

            if ($request->day == 'today') {
                $items = $items->whereRaw('Date(o.created_at) = CURDATE()');
            }
            if ($request->day == 'yesterday') {
                $items = $items->whereDate('o.created_at', date("Y-m-d", strtotime('-1 days')));
            }
            if ($request->day == 'week') {
                $items = $items->where('o.created_at', '>=', Carbon::now()->subWeeks(1));
            }
            if ($request->day == 'month') {
                $items = $items->where('o.created_at', '>=', Carbon::now()->subMonths(1));
            }
            if ($request->date !='') {
                $items = $items->whereDate('o.created_at', '=', $request->date);
            }

            $items = $items->select(
                'tod.item_name',
                'tod.category_name',
                'tod.price_title as price_title',
                DB::raw('SUM(tod.quantity) as items_count'),
                DB::raw('SUM(tod.item_total_price) as menu_items_total')
            )
            ->groupBy('tod.price_id')
            ->get();

            $this->data['items'] = $items;

            //csv
            if ($request->export == 'csv') {
                $filename = "sales_by_items.csv";
                $handle = fopen($filename, 'w+');
                fputcsv($handle, array('Item name', 'Category', 'Items sold', 'Net sales'));

                foreach ($items as $row) {
                    fputcsv($handle, array($row->menu_item_name, $row->category_name, $row->items_count, $row->menu_items_total));
                }
                fclose($handle);
                $headers = array('Content-Type' => 'text/csv');
                return Response::download($filename, 'sales_by_items.csv', $headers);
            }
        }

        //----------------- Sales by Category -----------------//
        if ($request->type == 'category') {
            $this->data['title'] = 'Sales by Category';
            $page = 'category';
            
            $categories = DB::table('takeaway_orders as o')
            ->leftJoin('takeaway_order_details as tod', 'tod.order_id', '=', 'o.id')
            ->where('o.total', '!=', 0)
            ->where('o.status', '!=', '1')->where('o.payment_status','1');
            
            if ($request->day == 'today') {
                $categories = $categories->orwhereRaw('Date(o.created_at) = CURDATE()');
            }
            if ($request->day == 'yesterday') {
                $categories = $categories->whereDate('o.created_at', date("Y-m-d", strtotime('-1 days')));
            }
            if ($request->day == 'week') {
                $categories = $categories->where('o.created_at', '>=', Carbon::now()->subWeeks(1));
            }
            if ($request->day == 'month') {
                $categories=$categories->where('o.created_at', '>=', Carbon::now()->subMonths(1));
            }
            if ($request->date !='') {
                $categories = $categories->whereDate('o.created_at', '=', $request->date);
            }

            $categories = $categories->select(
                'tod.category_id',
                'tod.category_name',
                DB::raw('SUM(tod.quantity) as items_count'),
                DB::raw('SUM(tod.item_total_price) as category_total')
            )
            ->groupBy('tod.category_id')
            ->get();
            
            $this->data['categories'] = $categories;
            //csv
            if ($request->export == 'csv') {
                $filename = "sales_by_categories.csv";
                $handle = fopen($filename, 'w+');
                fputcsv($handle, array( 'Category', 'Items sold', 'Net sales'));

                foreach ($categories as $row) {
                    fputcsv($handle, array( $row->category_name, $row->items_count, $row->category_total));
                }
                fclose($handle);
                $headers = array('Content-Type' => 'text/csv');
                return Response::download($filename, 'sales_by_categories.csv', $headers);
            }
        }
                
        return view('admin.reports.'.$page, $this->data);
    }

    /*
    | Category by item Report
    |--------------------------------------------------------------------------
    */
    public function category_by_items_report(Request $request)
    {
        if (Auth::User()->group_id == 2) {
            return redirect('home');
        }

        $this->data['title'] = 'Category by items Report';

        $category_by_items = DB::table('takeaway_orders as o')
            ->leftJoin('takeaway_order_details as tod', 'tod.order_id', '=', 'o.id')
            ->where('o.status', '!=', '1')->where('o.payment_status','1');

        if ($request->day == 'today') {
            $category_by_items = $category_by_items->whereRaw('Date(o.created_at) = CURDATE()');
        }
        if ($request->day == 'yesterday') {
            $category_by_items = $category_by_items->whereDate('o.created_at', date("Y-m-d", strtotime('-1 days')));
        }
        if ($request->day == 'week') {
            $category_by_items = $category_by_items->where('o.created_at', '>=', Carbon::now()->subWeeks(1));
        }
        if ($request->day == 'month') {
            $category_by_items = $category_by_items->where('o.created_at', '>=', Carbon::now()->subMonths(1));
        }
        if ($request->date !='') {
            $category_by_items = $category_by_items->whereDate('o.created_at', '=', $request->date);
        }

        $category_by_items = $category_by_items->select(
                DB::raw('SUM(tod.quantity) as items_count'),
                'tod.category_name',
                DB::raw('SUM(tod.item_total_price) as item_total_price'),
                'tod.category_id'
            )
            ->groupBy('tod.category_id')
            ->get();

        if (count($category_by_items)>0) {
            foreach ($category_by_items as $row) {
                $row->items = DB::table('takeaway_orders as o')
                ->leftJoin('takeaway_order_details as tod', 'tod.order_id', '=', 'o.id')
                ->where('tod.item_id', '!=', '')
                ->where('tod.category_id', $row->category_id)
                ->select(
                    'tod.item_name',
                    'tod.price_title as price_title',
                    'tod.category_name',
                    DB::raw('SUM(tod.quantity) as items_count'),
                    DB::raw('SUM(tod.item_total_price) as item_total_price')
                )
                ->groupBy('tod.price_id')
                ->get();
            }
        }
        
        $this->data['category_by_items'] = $category_by_items;

        return view('admin.reports.category_by_items', $this->data);
    }
        
    /*
    | Detail Report
    |--------------------------------------------------------------------------
    */
    public function detail_report(Request $request)
    {
        if (Auth::User()->group_id == 2) {
            return redirect('home');
        }

        $this->data['title'] = 'Detail Report';

        $this->data['users'] = \App\User::all();
        $this->data['orders'] = \App\TakeawayOrders::all();
        $this->data['categories'] = \App\MenuCategories::all();

        if ($request->has('submit')) {

            // ======================== reports1 ======================== //

            $reports1 = DB::table('takeaway_orders as o')
            ->leftJoin('users as u', 'u.id', '=', 'o.user_id')
            ->leftJoin('customers as c', 'c.id', '=', 'o.customer_id')
            ->where('o.status', '!=', '1')->where('o.payment_status','1');

            if ($request->from_date !='' && $request->to_date) {
                $reports1 = $reports1->whereDate('o.created_at', '>=', $request->from_date);
                $reports1 = $reports1->whereDate('o.created_at', '<=', $request->to_date);
            }

            if ($request->user !='') {
                $reports1 = $reports1->where('u.id', $request->user);
            }
            
            if ($request->order !='') {
                $reports1 = $reports1->where('o.id', $request->order);
            }

            $reports1 = $reports1->select(
                DB::raw('DATE(o.created_at) as date'),
                'u.name as cashier',
                'o.id as bill_no',
                'o.created_at as bill_time',
                'o.subtotal as value',
                'o.discount',
                'o.total as net_value',
                'o.tax',
                'o.order_type',
                'o.payment_type as mop',
                'o.status as bill_status',
                'c.name'
            )
            ->orderBy('date', 'desc')
            ->get();

            $this->data['reports1'] = $reports1;

            // ======================== reports2 ======================== //

            $reports2 = DB::table('takeaway_orders as o')
            ->leftJoin('takeaway_order_details as od', 'od.order_id', '=', 'o.id')
            ->leftJoin('users as u', 'u.id', '=', 'o.user_id')
            ->where('o.status', '!=', '1')->where('o.payment_status','1');

            if ($request->from_date !='' && $request->to_date) {
                $reports2 = $reports2->whereDate('o.created_at', '>=', $request->from_date);
                $reports2 = $reports2->whereDate('o.created_at', '<=', $request->to_date);
            }

            if ($request->user !='') {
                $reports2 = $reports2->where('u.id', $request->user);
            }

            if ($request->order !='') {
                $reports2 = $reports2->where('o.id', $request->order);
            }

            if ($request->category !='') {
                $reports2 = $reports2->where('od.category_id', $request->category);
            }

            if ($request->item !='') {
                $reports2 = $reports2->where('od.item_id', $request->item);
            }

            $reports2 = $reports2->select(
                DB::raw('DATE(o.created_at) as date'),
                'u.name as cashier',
                'o.id as bill_no',
                'od.category_name as item_category',
                'od.item_name',
                'od.quantity',
                'od.price as value',
                'od.item_total_price as net_value',
                'od.item_discount as discount',
                // 'o.tax',
                'o.order_type',
                'o.payment_type as mop'
            )
            ->orderBy('date', 'desc')
            ->get();

            $this->data['reports2'] = $reports2;


            // ======================== platform ======================== //

            $platform = DB::table('takeaway_orders as o')
                ->leftJoin('users as u', 'u.id', '=', 'o.user_id')
                ->where('o.status', '!=', '1')->where('o.payment_status','1');

            if ($request->from_date !='' && $request->to_date) {
                $platform = $platform->whereDate('o.created_at', '>=', $request->from_date);
                $platform = $platform->whereDate('o.created_at', '<=', $request->to_date);
            }
            if ($request->user !='') {
                $platform = $platform->where('u.id', $request->user);
            }
            if ($request->order !='') {
                $platform = $platform->where('o.id', $request->order);
            }

            $platform = $platform->select(
                DB::raw('platform'),
                DB::raw('SUM(total) as netsale'),
                DB::raw('SUM(subtotal) as sale'),
                DB::raw('SUM(discount) as discount'),
                DB::raw('SUM(tax) as tax'),
                DB::raw('SUM(delivery_charges) as delivery_charges')
            )
            ->where('o.status', '!=', 1)
            ->groupBy('platform')
            ->get();

            $this->data['platform'] = $platform;

            // ======================== payment_type ======================== //

            $payment_type = DB::table('takeaway_orders as o')
                ->leftJoin('users as u', 'u.id', '=', 'o.user_id')
                ->where('o.status', '!=', '1')->where('o.payment_status','1');
            
            if ($request->from_date !='' && $request->to_date) {
                $payment_type = $payment_type->whereDate('o.created_at', '>=', $request->from_date);
                $payment_type = $payment_type->whereDate('o.created_at', '<=', $request->to_date);
            }
            if ($request->user !='') {
                $payment_type = $payment_type->where('u.id', $request->user);
            }
            if ($request->order !='') {
                $payment_type = $payment_type->where('o.id', $request->order);
            }

            $payment_type = $payment_type->select(
                DB::raw('payment_type'),
                DB::raw('SUM(total) as netsale'),
                DB::raw('SUM(subtotal) as sale'),
                DB::raw('SUM(discount) as discount'),
                DB::raw('SUM(tax) as tax'),
                DB::raw('SUM(delivery_charges) as delivery_charges')
            )
            ->where('o.status', '!=', 1)
            ->groupBy('payment_type')
            ->get();
            
            $this->data['payment_type'] = $payment_type;

            // ======================== order_type ======================== //

            $order_type = DB::table('takeaway_orders as o')
                ->leftJoin('users as u', 'u.id', '=', 'o.user_id')
                ->where('o.status', '!=', '1')->where('o.payment_status','1');

            if ($request->from_date !='' && $request->to_date) {
                $order_type = $order_type->whereDate('o.created_at', '>=', $request->from_date);
                $order_type = $order_type->whereDate('o.created_at', '<=', $request->to_date);
            }
            if ($request->user !='') {
                $order_type = $order_type->where('u.id', $request->user);
            }
            if ($request->order !='') {
                $order_type = $order_type->where('o.id', $request->order);
            }

            $order_type = $order_type->select(
                DB::raw('order_type'),
                DB::raw('SUM(total) as netsale'),
                DB::raw('SUM(subtotal) as sale'),
                DB::raw('SUM(discount) as discount'),
                DB::raw('SUM(tax) as tax'),
                DB::raw('SUM(delivery_charges) as delivery_charges')
            )
            ->where('o.status', '!=', 1)
            ->groupBy('order_type')
            ->get();

            $this->data['order_type'] = $order_type;
        }

        return view('admin.reports.detail_report', $this->data);
    }
    

    /*
    | customers reports
    |--------------------------------------------------------------------------
    */
    public function customers_report(Request $request)
    {
        if (Auth::User()->group_id == 2) {
            return redirect('home');
        }

        $this->data['title'] = 'Customers Report';
            
        $customers = DB::table('customers');

        $all_customers_count = DB::table('customers');
        $web_customers_count = DB::table('customers')->where('platform', 'web');
        $app_customers_count = DB::table('customers')->where('platform', 'web');
        
        if ($request->day == 'today') {
            $customers=$customers->whereRaw('Date(created_at) = CURDATE()');

            $all_customers_count = $all_customers_count->whereRaw('Date(created_at) = CURDATE()');
            $web_customers_count = $web_customers_count->whereRaw('Date(created_at) = CURDATE()');
            $app_customers_count = $app_customers_count->whereRaw('Date(created_at) = CURDATE()');
        }
        if ($request->day == 'yesterday') {
            $customers=$customers->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));

            $all_customers_count = $all_customers_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
            $web_customers_count = $web_customers_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
            $app_customers_count = $app_customers_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
        }
        if ($request->day == 'week') {
            $customers=$customers->where('created_at', '>=', Carbon::now()->subWeeks(1));

            $all_customers_count = $all_customers_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
            $web_customers_count = $web_customers_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
            $app_customers_count = $app_customers_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
        }
            
        if ($request->day == 'month') {
            $customers=$customers->where('created_at', '>=', Carbon::now()->subMonths(1));

            $all_customers_count = $all_customers_count->where('created_at', '>=', Carbon::now()->subMonths(1));
            $web_customers_count = $web_customers_count->where('created_at', '>=', Carbon::now()->subMonths(1));
            $app_customers_count = $app_customers_count->where('created_at', '>=', Carbon::now()->subMonths(1));
        }
        if ($request->date !='') {
            $customers = $customers->whereDate('created_at', '=', $request->date);
            
            $all_customers_count = $all_customers_count->whereDate('created_at',$request->date);
            $web_customers_count = $web_customers_count->whereDate('created_at',$request->date);
            $app_customers_count = $app_customers_count->whereDate('created_at',$request->date);
        }

        $customers=$customers->get();
            
        $this->data['customers'] = $customers;
        $this->data['all_customers_count'] = $all_customers_count = $all_customers_count->count();
        $this->data['web_customers_count'] = $web_customers_count = $web_customers_count->count();
        $this->data['app_customers_count'] = $app_customers_count = $app_customers_count->count();

        return view('admin.reports.customers', $this->data);
    }

    /*
    | reservations reports
    |--------------------------------------------------------------------------
    */
    public function reservations_report(Request $request)
    {
        if (Auth::User()->group_id == 2) {
            return redirect('home');
        }
        
        $this->data['title'] = 'Reservations Report';
            
        $reservations = DB::table('table_reservations');

        $all_reservations_count = DB::table('table_reservations');
        $web_reservations_count = DB::table('table_reservations')->where('platform', 'web');
        $app_reservations_count = DB::table('table_reservations')->where('platform', 'app');
        
        if ($request->day == 'today') {
            $reservations=$reservations->whereRaw('Date(created_at) = CURDATE()');

            $all_reservations_count = $all_reservations_count->whereRaw('Date(created_at) = CURDATE()');
            $web_reservations_count = $web_reservations_count->whereRaw('Date(created_at) = CURDATE()');
            $app_reservations_count = $app_reservations_count->whereRaw('Date(created_at) = CURDATE()');
        }
        if ($request->day == 'yesterday') {
            $reservations=$reservations->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));

            $all_reservations_count = $all_reservations_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
            $web_reservations_count = $web_reservations_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
            $app_reservations_count = $app_reservations_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
        }
        if ($request->day == 'week') {
            $reservations=$reservations->where('created_at', '>=', Carbon::now()->subWeeks(1));

            $all_reservations_count = $all_reservations_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
            $web_reservations_count = $web_reservations_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
            $app_reservations_count = $app_reservations_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
        }
            
        if ($request->day == 'month') {
            $reservations=$reservations->where('created_at', '>=', Carbon::now()->subMonths(1));

            $all_reservations_count = $all_reservations_count->where('created_at', '>=', Carbon::now()->subMonths(1));
            $web_reservations_count = $web_reservations_count->where('created_at', '>=', Carbon::now()->subMonths(1));
            $app_reservations_count = $app_reservations_count->where('created_at', '>=', Carbon::now()->subMonths(1));
        }
        if ($request->date !='') {
            $reservations = $reservations->whereDate('created_at',$request->date);
            
            $all_reservations_count = $all_reservations_count->whereDate('created_at',$request->date);
            $web_reservations_count = $web_reservations_count->whereDate('created_at',$request->date);
            $app_reservations_count = $app_reservations_count->whereDate('created_at',$request->date);
        }

        $reservations=$reservations->get();
            
        $this->data['reservations'] = $reservations;
        $this->data['all_reservations_count'] = $all_reservations_count = $all_reservations_count->count();
        $this->data['web_reservations_count'] = $web_reservations_count = $web_reservations_count->count();
        $this->data['app_reservations_count'] = $app_reservations_count = $app_reservations_count->count();
                
        return view('admin.reports.reservations', $this->data);
    }

    /*
    | expenses reports
    |--------------------------------------------------------------------------
    */
    public function expenses_report(Request $request)
    {
        if (Auth::User()->group_id == 2) {
            return redirect('home');
        }
        
        $this->data['title'] = 'Expenses Report';
            
        $expenses = DB::table('expenses as e')
                    ->leftJoin('expense_types as et', 'et.id', '=', 'e.expense_type_id')
                    ->leftJoin('users as u', 'u.id', '=', 'e.user_id');

        $all_expenses_count = DB::table('expenses');
        $all_expenses = DB::table('expenses');
        
        if ($request->day == 'today') {
            $expenses=$expenses->whereRaw('Date(e.created_at) = CURDATE()');

            $all_expenses_count = $all_expenses_count->whereRaw('Date(created_at) = CURDATE()');
            $all_expenses = $all_expenses->whereRaw('Date(created_at) = CURDATE()');
        }
        if ($request->day == 'yesterday') {
            $expenses=$expenses->whereDate('e.created_at', date("Y-m-d", strtotime('-1 days')));

            $all_expenses_count = $all_expenses_count->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
            $all_expenses = $all_expenses->whereDate('created_at', date("Y-m-d", strtotime('-1 days')));
        }
        if ($request->day == 'week') {
            $expenses=$expenses->where('e.created_at', '>=', Carbon::now()->subWeeks(1));

            $all_expenses_count = $all_expenses_count->where('created_at', '>=', Carbon::now()->subWeeks(1));
            $all_expenses = $all_expenses->where('created_at', '>=', Carbon::now()->subWeeks(1));
        }
            
        if ($request->day == 'month') {
            $expenses=$expenses->where('e.created_at', '>=', Carbon::now()->subMonths(1));

            $all_expenses_count = $all_expenses_count->where('created_at', '>=', Carbon::now()->subMonths(1));
            $all_expenses = $all_expenses->where('created_at', '>=', Carbon::now()->subMonths(1));
        }
        if ($request->date !='') {
            $expenses = $expenses->whereDate('e.created_at',$request->date);
            
            $all_expenses_count = $all_expenses_count->whereDate('created_at',$request->date);
            $all_expenses = $all_expenses->whereDate('created_at',$request->date);
        }

        $expenses = $expenses->select('e.*', 'et.name as expense_type_name', 'u.name as user_name')->get();
            
        $this->data['expenses'] = $expenses;
        $this->data['all_expenses_count'] = $all_expenses_count = $all_expenses_count->count();
        $this->data['all_expenses'] = $all_expenses = $all_expenses->select(DB::raw('SUM(amount) as total_expenses'))->get();

        return view('admin.reports.expenses', $this->data);
    }


    /*
    | Print report
    |--------------------------------------------------------------------------
    */
    public function print_report(Request $request)
    {
        if (Auth::User()->group_id == 2) {
            return redirect('home');
        }

        $this->data['title'] = 'Print Report';
        $this->data['date'] = date('d - M - Y', strtotime($request->date));
        $this->data['settings'] = \App\Settings::find(1);
        $category_by_items = DB::table('takeaway_orders as o')
            ->leftJoin('takeaway_order_details as tod', 'tod.order_id', '=', 'o.id')
            ->where('o.status', '!=', '1')->where('o.payment_status','1')
            ->whereDate('o.created_at', '=', $request->date)
            ->select(
                DB::raw('SUM(tod.quantity) as items_count'),
                'tod.category_name',
                DB::raw('SUM(tod.item_total_price) as item_total_price'),
                'tod.category_id'
            )
            ->groupBy('tod.category_id')
            ->get();

        if (count($category_by_items)>0) {
            foreach ($category_by_items as $row) {
                $row->items = DB::table('takeaway_orders as o')
                ->leftJoin('takeaway_order_details as tod', 'tod.order_id', '=', 'o.id')
                ->where('o.status', '!=', '1')->where('o.payment_status','1')
                ->where('tod.item_id', '!=', '')
                ->where('tod.category_id', $row->category_id)
                ->whereDate('o.created_at', '=', $request->date)
                ->select(
                    'tod.item_name',
                    'tod.price_title as price_title',
                    'tod.category_name',
                    DB::raw('SUM(tod.quantity) as items_count'),
                    DB::raw('SUM(tod.item_total_price) as item_total_price')
                )
                ->groupBy('tod.price_id')
                ->get();
            }
        }
        
        $this->data['category_by_items'] = $category_by_items;

        // /////////////////////////////////////////////////////////////

        $total_count = DB::table('takeaway_orders')
            ->select(
                DB::raw('SUM(total) as total_sale'),
                DB::raw('SUM(discount) as total_discount'),
                DB::raw('SUM(subtotal) as total_gross'),
                DB::raw('SUM(delivery_charges) as total_delivery_charges'),
                DB::raw('SUM(tax) as total_tax')
            )
            ->where('status', '!=', '1')->where('payment_status','1')
            ->whereDate('created_at',$request->date)
            ->get();
        $count =$total_count;
        
        $this->data['net_sale'] =  isset($count[0]->total_sale)?$count[0]->total_sale:0;
        $this->data['gross_sale'] = isset($count[0]->total_gross)?$count[0]->total_gross:0;
        $this->data['discount'] = isset($count[0]->total_discount)?$count[0]->total_discount:0;
        $this->data['tax'] = isset($count[0]->total_tax)?$count[0]->total_tax:0;
        $this->data['delivery_charges'] = isset($count[0]->total_delivery_charges)?$count[0]->total_delivery_charges:0;
        
        // //////////////////////////////////////////////////////////////

        $payment_type = DB::table('takeaway_orders')
        ->whereDate('created_at', '=', $request->date)
        ->select(
            DB::raw('payment_type'),
            DB::raw('SUM(total) as netsale'),
            DB::raw('SUM(subtotal) as sale'),
            DB::raw('SUM(discount) as discount'),
            DB::raw('SUM(tax) as tax'),
            DB::raw('SUM(delivery_charges) as delivery_charges')
        )
        ->where('status', '!=', 1)->where('payment_status',1)
        ->groupBy('payment_type')
        ->get();
        
        $this->data['payment_type'] = $payment_type;

        // ////////////////////////////////////////////////////////////

        $order_type = DB::table('takeaway_orders')
            ->where('status', '!=', 1)->where('payment_status',1)
            ->whereDate('created_at', '=', $request->date)
            ->select(
                DB::raw('order_type'),
                DB::raw('SUM(total) as netsale'),
                DB::raw('SUM(subtotal) as sale'),
                DB::raw('SUM(discount) as discount'),
                DB::raw('SUM(tax) as tax'),
                DB::raw('SUM(delivery_charges) as delivery_charges')
            )
        ->where('status', '!=', 1)
        ->groupBy('order_type')
        ->get();

        $this->data['order_type'] = $order_type;

        // ////////////////////////////////////////////////////////////

        $by_user = DB::table('takeaway_orders')
        ->where('status', '!=', 1)->where('payment_status',1)
        ->whereDate('created_at', '=', $request->date)
        ->select(
            DB::raw('user'),
            DB::raw('SUM(total) as netsale'),
            DB::raw('SUM(subtotal) as sale'),
            DB::raw('SUM(discount) as discount')
        )
        ->where('status', '!=', 1)
        ->groupBy('user')
        ->get();

        $this->data['by_user'] = $by_user;

        return view('admin.reports.print_report', $this->data);
    }
}