<?php

namespace App\Http\Controllers;

use App\Brands;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ReportController extends Controller
{
    public function saleReportDetail(Request $request)
    {
        $fromdate = date('d-m-Y');
        $todate = date('d-m-Y', strtotime('+30 days'));
        if ($request->fromdate && $request->todate) {
            $fromdate = $request->fromdate;
            $todate = $request->todate;
            $categoryid = $request->category_id;
            $brand_id = $request->brand_id;
            $product_id = $request->product_id;

            $returnNote = DB::table('sale_return_notes')
                ->select('gdn_id', 'product_id', DB::raw('IFNULL(SUM(return_qty),0) AS returned'), 'batch_id')
                ->groupBy('product_id')
                ->groupBy('gdn_id');

            $sql = [
                DB::raw('UPPER(employees.employee_name) as employee_name'),
                'products.product_name',
                'products.sku',
                'gdn_details.gdn_qty_delivered as qty',
                DB::raw('(IFNULL(returnNote.returned,0)) as returned_qty'),
                'gdn_details.gdn_unit_discount',
                'gdn_details.gdn_discount_percent',
                'gdn_details.gdn_unit_price',
                DB::raw('UPPER(goods_delivery_note.gdn_customer_name) as customer_name'),
                DB::raw('DATE_FORMAT(goods_delivery_note.gdn_created_date, "%d-%M-%Y") as transdate'),
                'goods_delivery_note.gdn_number',
                'sale_orders.sale_order_number',
                'producttypes.type_name as category_name',
                'sale_invoice.extra_discount_amount',
            ];
            $saleReport = gdn_details::select($sql)
                ->join('goods_delivery_note', 'goods_delivery_note.id_gdn', '=', 'gdn_details.gdn_id')
                ->join('sale_invoice', 'sale_invoice.gdn_id', '=', 'goods_delivery_note.id_gdn')
                ->join('sale_orders', 'goods_delivery_note.sale_order_id', '=', 'sale_orders.id_sale_orders')
                ->join('products', 'gdn_details.gdn_product_id', '=', 'products.id_products')
                //->join('employee_customers', 'goods_delivery_note.customer_id', '=', 'employee_customers.customer_id')
                ->join('employees', 'employees.id_employee', '=', 'sale_orders.saleman_id')
                ->join('producttypes', 'producttypes.type_id', '=', 'products.category_id')
                ->leftjoinSub($returnNote, 'returnNote', function ($join) {
                    $join->on('gdn_details.gdn_id', '=', 'returnNote.gdn_id');
                    $join->on('gdn_details.gdn_product_id', '=', 'returnNote.product_id');
                })
                ->where('goods_delivery_note.gdn_created_date', '>=', date('Y-m-d', strtotime($fromdate)))
                ->where('goods_delivery_note.gdn_created_date', '<=', date('Y-m-d', strtotime($todate)) . ' 23:59:59')
                ->where('products.brand_id', '=', $brand_id)
                ->where('sale_orders.status', '!=', 'Cancelled')
                ->where('sale_orders.business_id', '=', auth()->user()->business_id);
            if ($categoryid != "all") {
                $saleReport = $saleReport->where('products.category_id', '=', $categoryid);
            }
            if ($product_id != "all") {
                $saleReport = $saleReport->where('products.id_products', '=', $product_id);
            }
            $saleReport = $saleReport->orderBy('goods_delivery_note.gdn_customer_name')
                ->orderBy('sale_orders.id_sale_orders')
                ->orderBy('goods_delivery_note.gdn_created_date')
                //->groupBy('goods_delivery_note.customer_id', 'gdn_details.gdn_product_id')
                ->get()
                ->toArray();
            return $saleReport;
            exit;
        }
        $business = DB::table('business')->where('id_business', auth()->user()->business_id)->first();
        $brands = Brands::all()->where('status', '=', 'Active');
        return view('reports.saleReportDetail', compact('business', 'fromdate', 'todate', 'brands'));
    }

    public function categoryBrand(Request $request)
    {
        $brand_id = $request->brand_id;
        $categories = producttypes::select('producttypes.type_id', 'producttypes.type_name')
            ->distinct()
            ->join('products', 'producttypes.type_id', '=', 'products.category_id')
            ->where('brand_id', '=', $brand_id)
            ->orderBy('producttypes.type_id', 'DESC')
            ->get()->toArray();
        return $categories;
    }
}
