MAINHACK

Viewing / Editing File: ReportController.php

<?php

namespace App\Http\Controllers;

use App\Models\Batch;
use App\Models\Customer;
use App\Models\hrm\Expense;
use App\Models\Invoice;
use App\Models\Medicine;
use App\Models\Method;
use App\Models\Purchase;
use App\Service\CustomerDueExcelExport;
use App\Service\SalePurchaseExcelExportService;
use App\Service\SupplierPayableExcelExport;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use PDF;

class ReportController extends Controller
{

public function customerDue(Request $request)
    {
        $shop_id = Auth::user()->shop_id;
        $collection = Customer::with('invoice')
            ->select('id', 'name', 'address', 'phone', 'due')
            ->withSum(['invoice as invoice_due' => function ($query) {
                $query->where('due_price', '>', 0);
            }], 'due_price')
            ->where('due', '>', 0)
            ->orderBy('id', 'DESC')
            ->paginate(10);

$total_dues = DB::select("
        SELECT
            SUM(customer.due) AS previous_due,
            SUM(invoice.due_price) AS invoice_due
        FROM
            customers AS customer
        JOIN invoices AS invoice
        ON
            customer.id = invoice.customer_id
        WHERE
            customer.shop_id = :shop_id
    ", ['shop_id' => $shop_id]);

$total_previous_due = $total_dues[0]->previous_due;
        $total_invoice_due = $total_dues[0]->invoice_due;

if ($request->excel_export){
            return Excel::download(
                new CustomerDueExcelExport($collection, $total_previous_due, $total_invoice_due),
                'customer_due_report.xlsx'
            );
        }

if ($request->pdf_export){
            $reports = $collection;
            $fileName = 'customer_due_report' . '-' . time() . '.pdf';
            $pdf = PDF::loadView('exports.customer_due_report', compact('reports', 'total_invoice_due', 'total_previous_due'));
            return $pdf->download($fileName);
        }
        return view('reports.customer_due', compact('collection', 'total_invoice_due', 'total_previous_due'));
    }

public function supplierDue(Request $request)
    {
        $shop_id = Auth::user()->shop_id;
        $collection = Purchase::with('supplier')->groupBy('supplier_id')
            ->selectRaw('sum(due_price) as invoice_due, supplier_id, id')
            ->withSum(['supplier as previous_due' => function ($query) {
                $query->where('due', '>', 0);
            }], 'due')
            ->having('invoice_due', '>', 0)
            ->latest('id')
            ->paginate(10);

$total_dues = DB::select("
                SELECT 
                    SUM(purchase.due_price) AS invoice_payable,
                    SUM(supplier.due) AS previous_payable 
                FROM
                    purchases AS purchase
                JOIN suppliers AS supplier
                ON
                    supplier.id = purchase.supplier_id 
                WHERE
                    purchase.shop_id = :shop_id
            ", ['shop_id' => $shop_id]);

$total_previous_payable = $total_dues[0]->previous_payable;
        $total_invoice_payable = $total_dues[0]->invoice_payable;

if ($request->excel_export){
            return Excel::download(
                new SupplierPayableExcelExport($collection, $total_previous_payable, $total_invoice_payable),
                'supplier_payable_report.xlsx'
            );
        }

if ($request->pdf_export){
            $reports = $collection;
            $fileName = 'supplier_payable_report' . '-' . time() . '.pdf';
            $pdf = PDF::loadView('exports.supplier_payable_report', compact('reports', 'total_previous_payable', 'total_invoice_payable'));
            return $pdf->download($fileName);
        }
        return view('reports.supplier_due', compact('collection', 'total_previous_payable', 'total_invoice_payable'));
    }

public function topSellMedicine(Request $request)
    {
        $keyword = $request->keyword ?? '';
        $from_date = '';
        $to_date = '';
        if (!empty($request->from) && !empty($request->to)) {
            $from_date = $request->from;
            $to_date = $request->to;
        } else {
            $from_date = date('Y-m-d', strtotime("-7 day", time()));
            $to_date = date('Y-m-d');
        }
        $dates = list_days($from_date, $to_date);
        $medicineIds = [];
        $query = Invoice::select('id', 'medicines')->where('shop_id', Auth::user()->shop_id)->whereIn('date', $dates);
        $sells = $query->limit(10)->latest()->get();
        $medicines = array();
        foreach ($sells as $sell) {
            $sold_medicines = json_decode($sell->medicines, true);
            foreach ($sold_medicines as $key => $medicine) {
                if (is_array($medicine)) {
                    $mquery = Medicine::where('id', $medicine['id'])->select('name', DB::raw('count(*) as total', 'id', 'generic_name'))
                        ->groupBy('name');
                    $mData = $mquery->get();
                    foreach ($mData as $data) {
                        $report['total_sale'] = $data['total'];
                        $report['id'] = $data['id'];
                        $report['name'] = $data['name'];
                        $report['generic_name'] = $data['generic_name'];
                    }
                    array_push($medicines, $report);
                }
            }
        }

return view('reports.topsell_medicine', compact('medicines', 'keyword', 'from_date', 'to_date'));

}

// Business Profit & Loss
    public function businessProfitLoss(Request $request)
    {

$year = now()->year;
        if (!empty($request->year)) {
            $year = $request->year;
        }

$totalSale = Invoice::sum('total_price');
        $totalSaleQuantity = Invoice::sum('qty');

$totalPurchase = Purchase::sum('total_price');
        $totalPurchaseQuantity = Purchase::sum('qty');

$balanceInhand = Method::sum('balance');
        $totalExpenses = Expense::sum('amount');

$salesData = $this->getData('sales', 'invoices', 'total_price', $year);
        $purchasesData = $this->getData('purchases', 'purchases', 'total_price', $year);
        $expensesData = $this->getData('expenses', 'expenses', 'amount', $year);

$monthlyData = [];

foreach ($salesData as $sales) {
            $month = $sales->month;

$monthlyData[$month]['month'] = date('F', mktime(0, 0, 0, $month, 1));
            $monthlyData[$month]['total_sales'] = $sales->total_sales;

$monthlyData[$month]['total_purchases'] = 0;
            foreach ($purchasesData as $purchases) {
                if ($purchases->month == $month) {
                    $monthlyData[$month]['total_purchases'] = $purchases->total_purchases;
                    break;
                }
            }

$monthlyData[$month]['total_expenses'] = 0;
            foreach ($expensesData as $expenses) {
                if ($expenses->month == $month) {
                    $monthlyData[$month]['total_expenses'] = $expenses->total_expenses;
                    break;
                }
            }
            $monthlyData[$month]['profit_loss'] = $monthlyData[$month]['total_sales'] - ($monthlyData[$month]['total_purchases'] + $monthlyData[$month]['total_expenses']);
        }
        return view('reports.business_profitloss',
            compact('totalSale', 'totalSaleQuantity', 'totalPurchase', 'totalPurchaseQuantity'
                , 'totalExpenses', 'balanceInhand', 'monthlyData', 'year'));
    }

private function getData($field, $table, $amount_field, $year)
    {
        return DB::table($table)
            ->select(DB::raw('MONTH(date) as month'), DB::raw('SUM(' . $amount_field . ') as total_' . $field))
            ->whereYear('date', $year)
            ->groupBy('month')
            ->get();
    }

public function inStockMedicine(Request $request)
    {
        $today = now();
        $limit = $request->input('limit', 100);
        $collection = Medicine::select('id', 'name')
            ->when($request->keyword, function ($query) use ($request) {
                {
                    $query->where('name', 'LIKE', "%$request->keyword%");
                }
            })
            ->whereHas('batch', function ($query) use ($today) {
                $query->whereDate('expire', '>', $today)
                    ->where('qty', '>', 0);
            })
            ->with(['batch' => function ($query) use ($today) {
                $query->whereDate('expire', '>', $today)
                    ->where('qty', '>', 0);
            }])
            ->withSum(['batch as total_qty' => function ($query) use ($today) {
                $query->whereDate('expire', '>', $today)
                    ->where('qty', '>', 0);
            }], 'qty')
            ->orderBy('name', 'ASC')
            ->paginate($limit);

return view('reports.instock', compact('collection'));
    }

public function lowStockMedicine(Request $request)
    {
        $limit = $request->input('limit', 10);
        $collection = Medicine::with('batch', 'supplier')
            ->select('id', 'name', 'supplier_id', 'generic_name')
            ->when($request->keyword, function ($query) use ($request) {
                {
                    $query->where('name', 'LIKE', "%$request->keyword%");
                }
            })
            ->whereHas('batch', function ($query) {
                $query->where('qty', '>', 0);
            })->withCount(['batch as total_quantity' => function ($query) {
                $query->select(DB::raw('sum(qty)'));
            }])
            ->having('total_quantity', '<', setting('low_stock_alert') ?? 5)
            ->orderBy('name', 'ASC')
            ->paginate($limit);
        return view('reports.lowstock', compact('collection'));
    }

public function stockoutMedicine(Request $request)
    {
        $limit = $request->input('limit', 10);
        $collection = Medicine::with('batch', 'supplier')
            ->select('id', 'name', 'supplier_id', 'generic_name')
            ->when($request->keyword, function ($query) use ($request) {
                {
                    $query->where('name', 'LIKE', "%$request->keyword%");
                }
            })
            ->whereHas('batch', function ($query) {
                $query->where('qty', '<', 1);
            })
            ->orderBy('name', 'ASC')
            ->paginate($limit);
        return view('reports.stockout', compact('collection'));
    }

public function upcomingExpireMedicine(Request $request)
    {
        $today = date('Y-m-d', time());
        $alertDate = setting('upcoming_expire_alert') ?? 7;
        $upcomingDate = date('Y-m-d', strtotime("+$alertDate days"));

$limit = $request->input('limit', 10);

$collection = Batch::with('medicine')
            ->where('expire', '>', $today)
            ->where('expire', '<=', $upcomingDate)
            ->latest('expire')
            ->when($request->field && $request->keyword, function ($query) use ($request) {
                $query->where($request->field, 'LIKE', "%$request->keyword%");
            })->paginate($limit);

return view('reports.upcoming_expire', compact('collection'));
    }

public function alreadyExpireMedicine(Request $request)
    {
        $today = date('Y-m-d', time());
        $limit = $request->input('limit', 10);
        $collection = Batch::with(['medicine' => function ($query) use ($request) {
            $query->when($request->keyword, function ($q) use ($request) {
                $q->where('name', 'LIKE', "%$request->keyword%");
            });
        }])->when($request->date, function ($q) use ($request) {
            $q->whereDate('expire', $request->date);
        })
            ->where('expire', '<=', $today)
            ->latest('expire')
            ->paginate($limit);
        return view('reports.already_expire', compact('collection'));
    }

public function dueCustomer(Request $request)
    {
        $store_id = Auth::user()->store_id;
        $limit = $request->input('limit') ?? 10;
        $collection = Customer::select('id', 'name', 'address', 'phone', 'email', 'due')
            ->where('store_id', $store_id)
            ->where('due', '>', 0)
            ->when($request->field && $request->keyword, function ($query) use ($request) {
                $query->where($request->field, 'LIKE', "%$request->keyword%");
            })->paginate($limit);
        return view('store.reports.due_customer', compact('collection'));
    }

public function payableManufacturer(Request $request)
    {
        $store_id = Auth::user()->store_id;
        $limit = $request->input('limit') ?? 10;
        $collection = Manufacturer::select('id', 'name', 'address', 'phone', 'payable')
            ->where('store_id', $store_id)
            ->where('payable', '>', 0)
            ->when($request->field && $request->keyword, function ($query) use ($request) {
                $query->where($request->field, 'LIKE', "%$request->keyword%");
            })->paginate($limit);
        return view('store.reports.payable_manufacturer', compact('collection'));
    }

public function salePurchase(Request $request)
    {
        $store_id = Auth::user()->store_id;

$from_date = date('Y-m-d', strtotime("-7 day", time()));
        $to_date = date('Y-m-d');

if (!empty($request->input('from_date')) && !empty($request->input('to_date'))) {
            $from_date = $request->input('from_date');
            $to_date = $request->input('to_date');
        }

$dates = list_days($from_date, $to_date);
        $collection = [];

foreach ($dates as $date) {
            $sales = Invoice::where('store_id', $store_id)
                ->withCount(['saleDetails as total_sale_quantity' => function ($query) {
                    $query->select(DB::raw('sum(quantity)'));
                }])->whereDate('created_at', $date)
                ->get();

$totalSalesQuantity = $sales->sum('total_sale_quantity');
            $totalSalesAmount = $sales->sum('paid_amount');

$totalPurchases = Purchase::where('store_id', $store_id)
                ->withCount(['batch as total_quantity' => function ($query) {
                    $query->select(DB::raw('sum(quantity)'));
                }])->whereDate('purchase_date', $date)
                ->get();
            $totalPurchaseQuantity = $totalPurchases->sum('total_quantity');
            $totalPurchaseAmount = $totalPurchases->sum('total');
            $profitOrLoss = $totalSalesAmount - $totalPurchaseAmount;
            $collection[$date] = [
                'total_sale_quanity' => $totalSalesQuantity,
                'total_sale_amount' => $totalSalesAmount,
                'total_purchases_quantity' => $totalPurchaseQuantity,
                'total_purchases_amount' => $totalPurchaseAmount,
                'profitOrLoss' => $profitOrLoss,
            ];
        }

return view('store.reports.sale_purchase', compact('collection'));
    }

public function profitLoss(Request $request)
    {
        $store_id = Auth::user()->store_id;

$from_date = date('Y-m-d', strtotime("-7 day", time()));
        $to_date = date('Y-m-d');

if (!empty($request->input('from_date')) && !empty($request->input('to_date'))) {
            $from_date = $request->input('from_date');
            $to_date = $request->input('to_date');
        }

$dates = list_days($from_date, $to_date);
        $collection = [];
        // Loop through each day in the date range
        foreach ($dates as $date) {
            $store_id = Auth::user()->store_id;
            $reports = DB::select("
            SELECT
                SUM((batches.buy_price * sale_details.quantity) - batches.discount) as total_purchases_amount,
                SUM(sales.grand_total) as total_sale_amount,
                SUM(sale_details.quantity) as total_sale_quantity,
                SUM(batches.quantity) as total_purchases_quantity
            FROM sales
            INNER JOIN sale_details ON sales.id = sale_details.sale_id
            INNER JOIN batches ON sale_details.batch_id = batches.id
            INNER JOIN purchases ON batches.purchase_id = purchases.id
            WHERE
                sales.store_id = :store_id
                AND DATE(sales.created_at) = :date
        ", ['store_id' => $store_id, 'date' => $date])[0];

$collection[$date] = [
                'total_sale_quantity' => $reports->total_sale_quantity,
                'total_sale_amount' => $reports->total_sale_amount,
                'total_purchases_quantity' => $reports->total_purchases_quantity,
                'total_purchases_amount' => $reports->total_purchases_amount,
                'profitOrLoss' => ($reports->total_sale_amount - $reports->total_purchases_amount),
            ];
        }
        return view('store.reports.profit_loss', compact('collection'));
    }

}

Cancel / Back

[ MAINHACK ]

Viewing / Editing File: ReportController.php

Server Info

System Features

User Info