Customer Monthly Sales Report - Complete Implementation Guide
This comprehensive guide walks you through implementing a powerful Customer Monthly Sales Report feature in Ultimate POS, providing detailed insights into customer purchasing patterns with monthly breakdowns and profit analysis.
Overview​
The Customer Monthly Sales Report feature provides:
- Monthly Sales Breakdown: View sales for each customer across all 12 months
- Profit Analysis: Calculate gross profit and profit margins per customer
- Summary Statistics: Total customers, transactions, sales, and profit metrics
- Advanced Filtering: Filter by location, payment status, staff, and more
- Customer Details Modal: Detailed view of individual customer transactions
- Responsive Design: Modern UI with interactive summary cards
Prerequisites​
- Ultimate POS system installed and running
- Laravel 8+ with proper database setup
- Access to database and codebase
- Basic knowledge of Laravel, PHP, Blade templates, and DataTables
- Understanding of database relationships
Step 1: Database Structure​
The report uses existing Ultimate POS tables. Ensure these tables exist and have proper relationships:
-- Core tables used
- transactions
- transaction_sell_lines
- transaction_sell_lines_purchase_lines
- purchase_lines
- contacts
- variations
- products
- business_locations
- users
Step 2: Create the Controller​
Create the controller file at app/Http/Controllers/CustomerMonthlySalesController.php:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Routing\Controller;
use App\Contact;
use App\Transaction;
use App\BusinessLocation;
use App\User;
use Yajra\DataTables\Facades\DataTables;
use App\Utils\TransactionUtil;
use App\Utils\ModuleUtil;
use App\Utils\BusinessUtil;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
class CustomerMonthlySalesController extends Controller
{
/**
* All Utils instance.
*/
protected $transactionUtil;
protected $moduleUtil;
protected $businessUtil;
/**
* Constructor
*/
public function __construct(TransactionUtil $transactionUtil, ModuleUtil $moduleUtil, BusinessUtil $businessUtil)
{
$this->transactionUtil = $transactionUtil;
$this->moduleUtil = $moduleUtil;
$this->businessUtil = $businessUtil;
}
/**
* Display customer monthly sales report
*/
public function index()
{
if (!auth()->user()->can('sell.view')) {
abort(403, 'Unauthorized action.');
}
$business_id = request()->session()->get('user.business_id');
// Get dropdowns for filters
$business_locations = BusinessLocation::forDropdown($business_id, true);
$customers = Contact::customersDropdown($business_id, false);
$users = User::forDropdown($business_id, false, true, true);
// Get payment types for filters
$payment_types = $this->transactionUtil->payment_types(null, true, $business_id);
$payment_types = collect($payment_types)->prepend(__('lang_v1.all'), '');
return view('report.customer_monthly_sales')
->with(compact(
'business_locations',
'customers',
'users',
'payment_types'
));
}
/**
* Get customer monthly sales data for DataTables
*/
public function getCustomerMonthlyData(Request $request)
{
if (!auth()->user()->can('sell.view')) {
abort(403, 'Unauthorized action.');
}
$business_id = $request->session()->get('user.business_id');
$year = $request->get('year', date('Y'));
try {
// Build base query for customer sales data
$query = Transaction::join('contacts as c', 'transactions.contact_id', '=', 'c.id')
->leftjoin('transaction_sell_lines as tsl', 'transactions.id', '=', 'tsl.transaction_id')
->leftjoin('transaction_sell_lines_purchase_lines as tspl', 'tsl.id', '=', 'tspl.sell_line_id')
->leftjoin('purchase_lines as pl', 'tspl.purchase_line_id', '=', 'pl.id')
->leftjoin('variations as v', 'tsl.variation_id', '=', 'v.id')
->where('transactions.business_id', $business_id)
->where('transactions.type', 'sell')
->where('transactions.status', 'final')
->whereYear('transactions.transaction_date', $year)
->whereNull('tsl.parent_sell_line_id');
// Apply filters
$this->applyFilters($query, $request);
// Group by customer and get monthly data
$monthlyData = $query->select([
'c.id as customer_id',
'c.name as customer_name',
'c.supplier_business_name',
DB::raw('MONTH(transactions.transaction_date) as month'),
DB::raw('SUM((tsl.quantity - COALESCE(tsl.quantity_returned, 0)) * tsl.unit_price_inc_tax) as monthly_sales'),
DB::raw('SUM((tsl.quantity - COALESCE(tsl.quantity_returned, 0)) * COALESCE(pl.purchase_price_inc_tax, v.default_purchase_price, tsl.unit_price_inc_tax * 0.7)) as monthly_purchase_cost')
])
->groupBy('c.id', 'c.name', 'c.supplier_business_name', DB::raw('MONTH(transactions.transaction_date)'))
->get();
// Transform data into the required format
$customerData = [];
foreach ($monthlyData as $data) {
$customerId = $data->customer_id;
if (!isset($customerData[$customerId])) {
$customerName = $data->customer_name;
if (!empty($data->supplier_business_name)) {
$customerName = $data->supplier_business_name . ' - ' . $customerName;
}
$customerData[$customerId] = [
'customer_id' => $customerId,
'customer_name' => $customerName,
'jan' => 0,
'feb' => 0,
'mar' => 0,
'apr' => 0,
'may' => 0,
'jun' => 0,
'jul' => 0,
'aug' => 0,
'sep' => 0,
'oct' => 0,
'nov' => 0,
'dec' => 0,
'jan_cost' => 0,
'feb_cost' => 0,
'mar_cost' => 0,
'apr_cost' => 0,
'may_cost' => 0,
'jun_cost' => 0,
'jul_cost' => 0,
'aug_cost' => 0,
'sep_cost' => 0,
'oct_cost' => 0,
'nov_cost' => 0,
'dec_cost' => 0,
'total_sales' => 0,
'total_cost' => 0
];
}
$monthNames = [
1 => 'jan',
2 => 'feb',
3 => 'mar',
4 => 'apr',
5 => 'may',
6 => 'jun',
7 => 'jul',
8 => 'aug',
9 => 'sep',
10 => 'oct',
11 => 'nov',
12 => 'dec'
];
$monthKey = $monthNames[$data->month];
$customerData[$customerId][$monthKey] = $data->monthly_sales;
$customerData[$customerId][$monthKey . '_cost'] = $data->monthly_purchase_cost;
$customerData[$customerId]['total_sales'] += $data->monthly_sales;
$customerData[$customerId]['total_cost'] += $data->monthly_purchase_cost;
}
// Convert to collection for DataTables
$collection = collect(array_values($customerData));
return DataTables::of($collection)
->addColumn('action', function ($row) {
return '<button type="button" class="btn btn-info btn-xs view-customer-details"
data-customer-id="' . $row['customer_id'] . '">
<i class="fa fa-eye"></i> ' . __('messages.view') . '
</button>';
})
->editColumn('jan', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['jan'], 2) . '</span>';
})
->editColumn('feb', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['feb'], 2) . '</span>';
})
->editColumn('mar', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['mar'], 2) . '</span>';
})
->editColumn('apr', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['apr'], 2) . '</span>';
})
->editColumn('may', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['may'], 2) . '</span>';
})
->editColumn('jun', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['jun'], 2) . '</span>';
})
->editColumn('jul', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['jul'], 2) . '</span>';
})
->editColumn('aug', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['aug'], 2) . '</span>';
})
->editColumn('sep', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['sep'], 2) . '</span>';
})
->editColumn('oct', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['oct'], 2) . '</span>';
})
->editColumn('nov', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['nov'], 2) . '</span>';
})
->editColumn('dec', function ($row) {
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($row['dec'], 2) . '</span>';
})
->editColumn('total_sales', function ($row) {
return '<span class="display_currency" data-currency_symbol="true"><strong>' .
number_format($row['total_sales'], 2) . '</strong></span>';
})
->addColumn('gross_profit', function ($row) {
$profit = $row['total_sales'] - $row['total_cost'];
$class = $profit >= 0 ? 'text-success' : 'text-danger';
return '<span class="display_currency ' . $class . '" data-currency_symbol="true"><strong>' .
number_format($profit, 2) . '</strong></span>';
})
->addColumn('gross_profit_percent', function ($row) {
$profit = $row['total_sales'] - $row['total_cost'];
$percentage = $row['total_sales'] > 0 ? ($profit / $row['total_sales']) * 100 : 0;
$class = $percentage >= 0 ? 'text-success' : 'text-danger';
return '<span class="' . $class . '"><strong>' .
number_format($percentage, 2) . '%</strong></span>';
})
->rawColumns([
'action',
'jan',
'feb',
'mar',
'apr',
'may',
'jun',
'jul',
'aug',
'sep',
'oct',
'nov',
'dec',
'total_sales',
'gross_profit',
'gross_profit_percent'
])
->make(true);
} catch (\Exception $e) {
\Log::error('Customer Monthly Sales Data Error: ' . $e->getMessage());
return response()->json(['error' => 'Error loading customer monthly sales data'], 500);
}
}
/**
* Get summary statistics
*/
public function getSummary(Request $request)
{
if (!auth()->user()->can('sell.view')) {
abort(403, 'Unauthorized action.');
}
try {
$business_id = $request->session()->get('user.business_id');
$year = $request->get('year', date('Y'));
$query = Transaction::join('contacts as c', 'transactions.contact_id', '=', 'c.id')
->leftjoin('transaction_sell_lines as tsl', 'transactions.id', '=', 'tsl.transaction_id')
->leftjoin('transaction_sell_lines_purchase_lines as tspl', 'tsl.id', '=', 'tspl.sell_line_id')
->leftjoin('purchase_lines as pl', 'tspl.purchase_line_id', '=', 'pl.id')
->leftjoin('variations as v', 'tsl.variation_id', '=', 'v.id')
->where('transactions.business_id', $business_id)
->where('transactions.type', 'sell')
->where('transactions.status', 'final')
->whereYear('transactions.transaction_date', $year)
->whereNull('tsl.parent_sell_line_id');
// Apply same filters
$this->applyFilters($query, $request);
$summary = $query->select([
DB::raw('COUNT(DISTINCT c.id) as total_customers'),
DB::raw('COUNT(DISTINCT transactions.id) as total_transactions'),
DB::raw('SUM((tsl.quantity - COALESCE(tsl.quantity_returned, 0)) * tsl.unit_price_inc_tax) as total_sales'),
DB::raw('SUM((tsl.quantity - COALESCE(tsl.quantity_returned, 0)) * COALESCE(pl.purchase_price_inc_tax, v.default_purchase_price, tsl.unit_price_inc_tax * 0.7)) as total_cost')
])->first();
$total_profit = $summary->total_sales - $summary->total_cost;
$profit_margin = $summary->total_sales > 0 ? ($total_profit / $summary->total_sales) * 100 : 0;
return response()->json([
'total_customers' => (int)$summary->total_customers,
'total_transactions' => (int)$summary->total_transactions,
'total_sales' => $summary->total_sales,
'total_cost' => $summary->total_cost,
'total_profit' => $total_profit,
'profit_margin' => $profit_margin,
'average_per_customer' => $summary->total_customers > 0 ? ($summary->total_sales / $summary->total_customers) : 0
]);
} catch (\Exception $e) {
\Log::error('Customer Monthly Sales Summary Error: ' . $e->getMessage());
return response()->json(['error' => 'Summary calculation failed'], 500);
}
}
/**
* Get customer details for a specific customer
*/
public function getCustomerDetails(Request $request, $customerId)
{
if (!auth()->user()->can('sell.view')) {
abort(403, 'Unauthorized action.');
}
try {
$business_id = $request->session()->get('user.business_id');
$year = $request->get('year', date('Y'));
\Log::info('Getting customer details', ['customer_id' => $customerId, 'year' => $year]);
// Get customer info
$customer = Contact::where('business_id', $business_id)
->where('id', $customerId)
->first();
if (!$customer) {
return response()->json(['error' => 'Customer not found'], 404);
}
// Get transactions for this customer and year
$transactions = Transaction::join('transaction_sell_lines as tsl', 'transactions.id', '=', 'tsl.transaction_id')
->leftJoin('products as p', 'tsl.product_id', '=', 'p.id')
->leftJoin('variations as v', 'tsl.variation_id', '=', 'v.id')
->where('transactions.business_id', $business_id)
->where('transactions.contact_id', $customerId)
->where('transactions.type', 'sell')
->where('transactions.status', 'final')
->whereYear('transactions.transaction_date', $year)
->whereNull('tsl.parent_sell_line_id')
->select([
'transactions.id as transaction_id',
'transactions.invoice_no',
'transactions.transaction_date',
'transactions.payment_status',
'p.name as product_name',
'v.name as variation_name',
'tsl.quantity',
'tsl.unit_price_inc_tax',
DB::raw('(tsl.quantity * tsl.unit_price_inc_tax) as line_total'),
DB::raw('MONTH(transactions.transaction_date) as month'),
DB::raw('MONTHNAME(transactions.transaction_date) as month_name')
])
->orderBy('transactions.transaction_date', 'desc')
->limit(50) // Limit to last 50 transactions
->get();
\Log::info('Found transactions', ['count' => $transactions->count()]);
// Calculate summary data
$totalAmount = $transactions->sum('line_total');
$totalQuantity = $transactions->sum('quantity');
$totalTransactions = $transactions->groupBy('transaction_id')->count();
$averagePerTransaction = $totalTransactions > 0 ? ($totalAmount / $totalTransactions) : 0;
// Group by month for monthly summary
$monthlySummary = $transactions->groupBy('month')->map(function ($monthTransactions, $month) {
return [
'month' => $month,
'month_name' => $monthTransactions->first()->month_name,
'total_transactions' => $monthTransactions->groupBy('transaction_id')->count(),
'total_amount' => $monthTransactions->sum('line_total'),
'total_quantity' => $monthTransactions->sum('quantity')
];
})->values();
return response()->json([
'success' => true,
'customer' => [
'id' => $customer->id,
'name' => $customer->name,
'supplier_business_name' => $customer->supplier_business_name,
'contact_id' => $customer->contact_id,
'mobile' => $customer->mobile,
'email' => $customer->email,
'address_line_1' => $customer->address_line_1,
'city' => $customer->city,
'state' => $customer->state,
'country' => $customer->country,
],
'transactions' => $transactions->map(function ($transaction) {
return [
'transaction_id' => $transaction->transaction_id,
'invoice_no' => $transaction->invoice_no,
'transaction_date' => $transaction->transaction_date,
'payment_status' => $transaction->payment_status,
'product_name' => $transaction->product_name .
($transaction->variation_name ? ' - ' . $transaction->variation_name : ''),
'quantity' => $transaction->quantity,
'unit_price_inc_tax' => $transaction->unit_price_inc_tax,
'line_total' => $transaction->line_total,
'month' => $transaction->month,
'month_name' => $transaction->month_name
];
}),
'monthly_summary' => $monthlySummary,
'overall_summary' => [
'total_transactions' => $totalTransactions,
'total_amount' => $totalAmount,
'total_quantity' => $totalQuantity,
'average_per_transaction' => $averagePerTransaction
],
'year' => $year
]);
} catch (\Exception $e) {
\Log::error('Customer Details Error: ' . $e->getMessage(), [
'customer_id' => $customerId,
'trace' => $e->getTraceAsString()
]);
return response()->json([
'error' => 'Error loading customer details: ' . $e->getMessage()
], 500);
}
}
/**
* Apply filters to query
*/
private function applyFilters($query, $request)
{
// Location filter
$permitted_locations = auth()->user()->permitted_locations();
if ($permitted_locations != 'all') {
$query->whereIn('transactions.location_id', $permitted_locations);
}
if (!empty($request->location_id)) {
$query->where('transactions.location_id', $request->location_id);
}
// Customer filter
if (!empty($request->customer_id)) {
$query->where('transactions.contact_id', $request->customer_id);
}
// Customer name search
if (!empty($request->customer_name)) {
$query->where(function ($q) use ($request) {
$q->where('c.name', 'like', '%' . $request->customer_name . '%')
->orWhere('c.supplier_business_name', 'like', '%' . $request->customer_name . '%');
});
}
// Payment status filter
if (!empty($request->payment_status)) {
$query->where('transactions.payment_status', $request->payment_status);
}
// Payment method filter
if (!empty($request->payment_method)) {
$query->whereHas('payment_lines', function ($q) use ($request) {
$q->where('method', $request->payment_method);
});
}
// User filter
if (!empty($request->user_id)) {
$query->where('transactions.created_by', $request->user_id);
}
}
}
Step 3: Create the Blade View​
Create the view file at resources/views/report/customer_monthly_sales.blade.php:
@extends('layouts.app')
@section('title', __('Customer Monthly Sales Report'))
@section('content')
<!-- Content Header (Page header) -->
<section class="content-header">
<h1>Customer Monthly Sales Report
<small>View customer sales by months with profit analysis</small>
</h1>
</section>
<!-- Main content -->
<section class="content">
<div class="row">
<div class="col-md-12">
@component('components.filters', [
'title' => __('report.filters'),
'class' => 'box-primary'
])
<div class="col-md-2">
<div class="form-group">
{!! Form::label('customer_name', __('contact.customer') . ' Name:') !!}
{!! Form::text('customer_name', null, ['class' => 'form-control', 'id' => 'customer_name_filter',
'placeholder' =>
'Search by name...']); !!}
</div>
</div>
<div class="col-md-2">
<div class="form-group">
{!! Form::label('location_id', __('purchase.business_location') . ':') !!}
{!! Form::select('location_id', $business_locations, null, ['class' => 'form-control select2',
'style' => 'width:100%', 'id' => 'location_filter']); !!}
</div>
</div>
<div class="col-md-2">
<div class="form-group">
{!! Form::label('payment_status', __('purchase.payment_status') . ':') !!}
{!! Form::select('payment_status', [
'' => __('lang_v1.all'),
'paid' => __('lang_v1.paid'),
'due' => __('lang_v1.due'),
'partial' => __('lang_v1.partial')
], null, ['class' => 'form-control select2', 'style' => 'width:100%', 'id' =>
'payment_status_filter']); !!}
</div>
</div>
<div class="col-md-2">
<div class="form-group">
{!! Form::label('payment_method', __('lang_v1.payment_method') . ':') !!}
{!! Form::select('payment_method', $payment_types, null, ['class' => 'form-control select2',
'style' => 'width:100%', 'id' => 'payment_method_filter']); !!}
</div>
</div>
<div class="col-md-2">
<div class="form-group">
{!! Form::label('user_id', __('report.user') . ':') !!}
{!! Form::select('user_id', $users, null, ['class' => 'form-control select2',
'style' => 'width:100%', 'id' => 'user_filter']); !!}
</div>
</div>
<div class="col-md-2">
<div class="form-group">
{!! Form::label('year', __('Year') . ':') !!}
{!! Form::select('year', array_combine(range(date('Y')-5, date('Y')+1), range(date('Y')-5,
date('Y')+1)), date('Y'), ['class' => 'form-control select2', 'style' => 'width:100%', 'id' =>
'year_filter']); !!}
</div>
</div>
<div class="col-md-12" style="margin-top: 10px;">
<div class="form-group">
<button type="button" class="btn btn-primary" id="filter_btn">
<i class="fa fa-filter"></i> @lang('report.filters')
</button>
<button type="button" class="btn btn-info" id="refresh_btn">
<i class="fa fa-refresh"></i> @lang('lang_v1.refresh')
</button>
</div>
</div>
@endcomponent
</div>
</div>
<!-- Summary Cards -->
<div class="row" id="summary_cards">
<!-- Total Customers -->
<div class="col-xl-2 col-lg-4 col-md-6 col-sm-6 col-xs-12">
<div class="modern-widget widget-customers">
<i class="fa fa-users modern-widget-icon"></i>
<div class="modern-widget-content">
<div class="modern-widget-text">Total Customers</div>
<div class="modern-widget-number" id="total_customers">1,247</div>
</div>
</div>
</div>
<!-- Total Transactions -->
<div class="col-xl-2 col-lg-4 col-md-6 col-sm-6 col-xs-12">
<div class="modern-widget widget-transactions">
<i class="fa fa-shopping-cart modern-widget-icon"></i>
<div class="modern-widget-content">
<div class="modern-widget-text">Total Transactions</div>
<div class="modern-widget-number" id="total_transactions">3,456</div>
</div>
</div>
</div>
<!-- Total Sales -->
<div class="col-xl-2 col-lg-4 col-md-6 col-sm-6 col-xs-12">
<div class="modern-widget widget-sales">
<i class="fa fa-money modern-widget-icon"></i>
<div class="modern-widget-content">
<div class="modern-widget-text">Total Sales</div>
<div class="modern-widget-number" id="total_sales">$87,432</div>
</div>
</div>
</div>
<!-- Total Profit -->
<div class="col-xl-2 col-lg-4 col-md-6 col-sm-6 col-xs-12">
<div class="modern-widget widget-profit">
<i class="fa fa-line-chart modern-widget-icon"></i>
<div class="modern-widget-content">
<div class="modern-widget-text">Total Profit</div>
<div class="modern-widget-number" id="total_profit">$23,891</div>
</div>
</div>
</div>
<!-- Profit Margin -->
<div class="col-xl-2 col-lg-4 col-md-6 col-sm-6 col-xs-12">
<div class="modern-widget widget-margin">
<i class="fa fa-percent modern-widget-icon"></i>
<div class="modern-widget-content">
<div class="modern-widget-text">Profit Margin</div>
<div class="modern-widget-number" id="profit_margin">27.3%</div>
</div>
</div>
</div>
<!-- Average per Customer -->
<div class="col-xl-2 col-lg-4 col-md-6 col-sm-6 col-xs-12">
<div class="modern-widget widget-average">
<i class="fa fa-calculator modern-widget-icon"></i>
<div class="modern-widget-content">
<div class="modern-widget-text">Avg per Customer</div>
<div class="modern-widget-number" id="avg_per_customer">$70.12</div>
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-12">
@component('components.widget', ['class' => 'box-primary', 'title' =>
'Customer Monthly Sales Report'])
<div class="table-responsive">
<table class="table table-bordered table-striped ajax_view colored-header" id="customer_monthly_table">
<thead>
<tr>
<th>@lang('messages.action')</th>
<th>Customer</th>
<th>Jan</th>
<th>Feb</th>
<th>Mar</th>
<th>Apr</th>
<th>May</th>
<th>Jun</th>
<th>Jul</th>
<th>Aug</th>
<th>Sep</th>
<th>Oct</th>
<th>Nov</th>
<th>Dec</th>
<th>Total Sales</th>
<th>Gross Profit ($)</th>
<th>Gross Profit (%)</th>
</tr>
</thead>
<tfoot>
<tr class="bg-gray font-17 text-center footer-total">
<td colspan="2"><strong>@lang('sale.total'):</strong></td>
<td class="footer_jan"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_feb"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_mar"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_apr"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_may"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_jun"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_jul"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_aug"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_sep"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_oct"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_nov"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_dec"><span class="display_currency" data-currency_symbol="true">0</span>
</td>
<td class="footer_total_sales"><span class="display_currency"
data-currency_symbol="true">0</span></td>
<td class="footer_gross_profit"><span class="display_currency"
data-currency_symbol="true">0</span></td>
<td class="footer_profit_percent">0%</td>
</tr>
</tfoot>
</table>
</div>
@endcomponent
</div>
</div>
<!-- Customer Details Modal -->
<div class="modal fade customer_details_modal" tabindex="-1" role="dialog"
aria-labelledby="customerDetailsModalLabel">
<div class="modal-dialog modal-lg" role="document">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title" id="customerDetailsModalLabel">Customer Details</h4>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body" id="customer_details_content">
<div class="text-center">
<i class="fa fa-spinner fa-spin fa-3x text-muted"></i>
<p class="text-muted">Loading customer details...</p>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
</section>
<!-- /.content -->
@stop
@section('javascript')
<script type="text/javascript">
// Currency formatting function
function formatCurrency(num) {
// Handle null, undefined, empty string
if (num === null || num === undefined || num === '') {
return __currency_trans_from_en('0.00', true);
}
// If it's already a number, use it directly
if (typeof num === 'number') {
return __currency_trans_from_en(num.toFixed(2), true);
}
// If it's a string, try to parse it
if (typeof num === 'string') {
// Remove any currency symbols, commas, and non-numeric characters except decimal point and minus
var cleanStr = num.replace(/[^\d.-]/g, '');
var parsed = parseFloat(cleanStr);
// Check if parsing was successful
if (!isNaN(parsed)) {
return __currency_trans_from_en(parsed.toFixed(2), true);
}
}
// Fallback for any other type or if parsing failed
console.warn('formatCurrency received invalid input:', num, typeof num);
return __currency_trans_from_en('0.00', true);
}
function formatNumber(num) {
// Handle null, undefined, empty string
if (num === null || num === undefined || num === '') {
return '0';
}
// If it's already a number, use it directly
if (typeof num === 'number') {
return num.toLocaleString('en-US', {
minimumFractionDigits: 0,
maximumFractionDigits: 2
});
}
// If it's a string, try to parse it
if (typeof num === 'string') {
var cleanStr = num.replace(/[^\d.-]/g, '');
var parsed = parseFloat(cleanStr);
if (!isNaN(parsed)) {
return parsed.toLocaleString('en-US', {
minimumFractionDigits: 0,
maximumFractionDigits: 2
});
}
}
// Fallback
console.warn('formatNumber received invalid input:', num, typeof num);
return '0';
}
$(document).ready(function() {
// Initialize DataTable
jQuery.extend(jQuery.fn.dataTableExt.oSort, {
"currency-asc": function(a, b) {
// Handle both HTML elements and direct text
var aText = typeof a === 'string' ? a : $(a).text();
var bText = typeof b === 'string' ? b : $(b).text();
// Remove all non-numeric characters except decimal point and minus
var x = parseFloat(aText.replace(/[^\d.-]/g, '')) || 0;
var y = parseFloat(bText.replace(/[^\d.-]/g, '')) || 0;
return x - y;
},
"currency-desc": function(a, b) {
// Handle both HTML elements and direct text
var aText = typeof a === 'string' ? a : $(a).text();
var bText = typeof b === 'string' ? b : $(b).text();
// Remove all non-numeric characters except decimal point and minus
var x = parseFloat(aText.replace(/[^\d.-]/g, '')) || 0;
var y = parseFloat(bText.replace(/[^\d.-]/g, '')) || 0;
return y - x;
}
});
// Auto-detect currency columns
$.fn.dataTable.ext.type.detect.unshift(function(data) {
// Check if data looks like currency (has currency symbols or is a formatted number)
if (typeof data === 'string' && (
data.match(/^[\$£€¥₹]/) || // Starts with currency symbol
data.match(/[\$£€¥₹]$/) || // Ends with currency symbol
data.match(/^\d{1,3}(,\d{3})*(\.\d+)?$/) || // Formatted number like 1,234.56
data.match(/\d+\.\d+/) || // Contains decimal
data.match(/%$/) // Percentage
)) {
return 'currency';
}
return null;
});
// Your DataTable initialization
var customer_monthly_table = $('#customer_monthly_table').DataTable({
processing: true,
serverSide: true,
ajax: {
url: "{{ route('reports.customer-monthly-sales.data') }}",
data: function(d) {
d.customer_name = $('#customer_name_filter').val();
d.location_id = $('#location_filter').val();
d.payment_status = $('#payment_status_filter').val();
d.payment_method = $('#payment_method_filter').val();
d.user_id = $('#user_filter').val();
d.year = $('#year_filter').val();
}
},
columns: [{
data: 'action',
name: 'action',
orderable: false,
searchable: false,
width: '80px'
},
{
data: 'customer_name',
name: 'customer_name',
width: '250px'
},
{
data: 'jan',
name: 'jan',
searchable: false,
width: '100px'
},
{
data: 'feb',
name: 'feb',
searchable: false,
width: '100px'
},
{
data: 'mar',
name: 'mar',
searchable: false,
width: '100px'
},
{
data: 'apr',
name: 'apr',
searchable: false,
width: '100px'
},
{
data: 'may',
name: 'may',
searchable: false,
width: '100px'
},
{
data: 'jun',
name: 'jun',
searchable: false,
width: '100px'
},
{
data: 'jul',
name: 'jul',
searchable: false,
width: '100px'
},
{
data: 'aug',
name: 'aug',
searchable: false,
width: '100px'
},
{
data: 'sep',
name: 'sep',
searchable: false,
width: '100px'
},
{
data: 'oct',
name: 'oct',
searchable: false,
width: '100px'
},
{
data: 'nov',
name: 'nov',
searchable: false,
width: '100px'
},
{
data: 'dec',
name: 'dec',
searchable: false,
width: '100px'
},
{
data: 'total_sales',
name: 'total_sales',
searchable: false,
width: '120px'
},
{
data: 'gross_profit',
name: 'gross_profit',
searchable: false,
width: '120px'
},
{
data: 'gross_profit_percent',
name: 'gross_profit_percent',
searchable: false,
width: '120px'
}
],
columnDefs: [{
targets: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16],
type: 'currency'
}],
order: [
[14, 'desc']
],
scrollX: true,
autoWidth: false, // Important: disable auto width
"fnDrawCallback": function(oSettings) {
__currency_convert_recursively($('#customer_monthly_table'));
// Calculate footer totals with improved parsing
var api = this.api();
var months = ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'];
var monthTotals = {};
// Initialize month totals
months.forEach(function(month) {
monthTotals[month] = 0;
});
// Calculate monthly totals safely
months.forEach(function(month, index) {
var columnIndex = index + 2; // Months start from column 2 (0-indexed)
api.column(columnIndex, {
page: 'current'
}).data().each(function(value, rowIndex) {
try {
var numericValue = 0;
// Handle different value types
if (typeof value === 'string') {
// Extract text content if it's HTML
var tempDiv = document.createElement('div');
tempDiv.innerHTML = value;
var textValue = tempDiv.textContent || tempDiv.innerText || '';
// Remove currency symbols and commas, keep only numbers and decimal point
var cleanValue = textValue.replace(/[$,\s]/g, '').replace(/[^\d.-]/g, '');
numericValue = parseFloat(cleanValue) || 0;
} else if (typeof value === 'number') {
numericValue = value;
} else if (value && typeof value === 'object') {
// It's a DOM element or jQuery object
var textValue = $(value).text() || '';
var cleanValue = textValue.replace(/[$,\s]/g, '').replace(/[^\d.-]/g, '');
numericValue = parseFloat(cleanValue) || 0;
}
monthTotals[month] += numericValue;
} catch (e) {
console.warn('Error parsing month value for ' + month + ':', value, e);
}
});
console.log(month + ' total:', monthTotals[month]); // Debug log
});
// Calculate total sales safely
var total_sales_sum = 0;
api.column(14, {
page: 'current'
}).data().each(function(value) {
try {
var numericValue = 0;
if (typeof value === 'string') {
var tempDiv = document.createElement('div');
tempDiv.innerHTML = value;
var textValue = tempDiv.textContent || tempDiv.innerText || '';
var cleanValue = textValue.replace(/[$,\s]/g, '').replace(/[^\d.-]/g, '');
numericValue = parseFloat(cleanValue) || 0;
} else if (typeof value === 'number') {
numericValue = value;
} else if (value && typeof value === 'object') {
var textValue = $(value).text() || '';
var cleanValue = textValue.replace(/[$,\s]/g, '').replace(/[^\d.-]/g, '');
numericValue = parseFloat(cleanValue) || 0;
}
total_sales_sum += numericValue;
} catch (e) {
console.warn('Error parsing total sales value:', value, e);
}
});
// Calculate total profit safely
var total_profit = 0;
api.column(15, {
page: 'current'
}).data().each(function(value) {
try {
var numericValue = 0;
if (typeof value === 'string') {
var tempDiv = document.createElement('div');
tempDiv.innerHTML = value;
var textValue = tempDiv.textContent || tempDiv.innerText || '';
var cleanValue = textValue.replace(/[$,\s]/g, '').replace(/[^\d.-]/g, '');
numericValue = parseFloat(cleanValue) || 0;
} else if (typeof value === 'number') {
numericValue = value;
} else if (value && typeof value === 'object') {
var textValue = $(value).text() || '';
var cleanValue = textValue.replace(/[$,\s]/g, '').replace(/[^\d.-]/g, '');
numericValue = parseFloat(cleanValue) || 0;
}
total_profit += numericValue;
} catch (e) {
console.warn('Error parsing profit value:', value, e);
}
});
// Debug logs
console.log('Month totals:', monthTotals);
console.log('Total sales sum:', total_sales_sum);
console.log('Total profit:', total_profit);
// Update footer with absolute values to prevent negatives
$('.footer_jan').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.jan || 0).toFixed(2) + '</span>');
$('.footer_feb').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.feb || 0).toFixed(2) + '</span>');
$('.footer_mar').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.mar || 0).toFixed(2) + '</span>');
$('.footer_apr').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.apr || 0).toFixed(2) + '</span>');
$('.footer_may').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.may || 0).toFixed(2) + '</span>');
$('.footer_jun').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.jun || 0).toFixed(2) + '</span>');
$('.footer_jul').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.jul || 0).toFixed(2) + '</span>');
$('.footer_aug').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.aug || 0).toFixed(2) + '</span>');
$('.footer_sep').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.sep || 0).toFixed(2) + '</span>');
$('.footer_oct').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.oct || 0).toFixed(2) + '</span>');
$('.footer_nov').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.nov || 0).toFixed(2) + '</span>');
$('.footer_dec').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(monthTotals.dec || 0).toFixed(2) + '</span>');
$('.footer_total_sales').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(total_sales_sum || 0).toFixed(2) + '</span>');
$('.footer_gross_profit').html('<span class="display_currency" data-currency_symbol="true">' + Math.abs(total_profit || 0).toFixed(2) + '</span>');
var overall_profit_percent = total_sales_sum > 0 ? (total_profit / total_sales_sum * 100) : 0;
$('.footer_profit_percent').text(Math.abs(overall_profit_percent || 0).toFixed(2) + '%');
__currency_convert_recursively($('.footer-total'));
},
createdRow: function(row, data, dataIndex) {
// Right align monetary columns
for (var i = 2; i <= 16; i++) {
$(row).find('td:eq(' + i + ')').addClass('text-right');
}
}
});
// Filter button click
$('#filter_btn').click(function() {
customer_monthly_table.ajax.reload();
loadSummary();
});
// Refresh button click
$('#refresh_btn').click(function() {
customer_monthly_table.ajax.reload();
loadSummary();
});
// Export button click
// Load summary data
function loadSummary() {
var customer_name = $('#customer_name_filter').val();
var location_id = $('#location_filter').val();
var payment_status = $('#payment_status_filter').val();
var payment_method = $('#payment_method_filter').val();
var user_id = $('#user_filter').val();
var year = $('#year_filter').val();
$.ajax({
url: "{{ route('reports.customer-monthly-sales.summary') }}",
data: {
customer_name: customer_name,
location_id: location_id,
payment_status: payment_status,
payment_method: payment_method,
user_id: user_id,
year: year
},
dataType: 'json',
success: function(data) {
// Use safe parsing for all numeric values
var totalCustomers = parseInt(data.total_customers) || 0;
var totalTransactions = parseInt(data.total_transactions) || 0;
var totalSales = parseFloat(data.total_sales) || 0;
var totalProfit = parseFloat(data.total_profit) || 0;
var profitMargin = parseFloat(data.profit_margin) || 0;
var averagePerCustomer = parseFloat(data.average_per_customer) || 0;
$('#total_customers').text(formatNumber(totalCustomers));
$('#total_transactions').text(formatNumber(totalTransactions));
$('#total_sales').text(formatCurrency(totalSales));
$('#total_profit').text(formatCurrency(totalProfit));
$('#profit_margin').text(profitMargin.toFixed(2) + '%');
$('#avg_per_customer').text(formatCurrency(averagePerCustomer));
$('#summary_cards').show();
},
error: function(xhr, status, error) {
console.log('Error loading summary data:', error);
console.log('Response:', xhr.responseText);
// Set default values on error
$('#total_customers').text('0');
$('#total_transactions').text('0');
$('#total_sales').text(formatCurrency(0));
$('#total_profit').text(formatCurrency(0));
$('#profit_margin').text('0.00%');
$('#avg_per_customer').text(formatCurrency(0));
}
});
}
// Auto-filter on change for filters
$('#customer_name_filter, #location_filter, #payment_status_filter, #payment_method_filter, #user_filter, #year_filter').change(function() {
customer_monthly_table.ajax.reload();
loadSummary();
});
// Customer details modal
$(document).on('click', '.view-customer-details', function(e) {
e.preventDefault();
var customerId = $(this).data('customer-id');
var year = $('#year_filter').val() || new Date().getFullYear();
console.log('Loading customer details for ID:', customerId, 'Year:', year);
$('.customer_details_modal').modal('show');
$('#customer_details_content').html(`
<div class="text-center">
<i class="fa fa-spinner fa-spin fa-3x text-muted"></i>
<p class="text-muted">Loading customer details...</p>
</div>
`);
$.ajax({
url: "{{ route('reports.customer-monthly-sales.details', '') }}/" + customerId,
method: 'GET',
data: {
year: year,
_token: $('meta[name="csrf-token"]').attr('content')
},
timeout: 30000,
success: function(response) {
console.log('Customer details response:', response);
if (response && response.customer) {
var customer = response.customer;
var transactions = response.transactions || [];
var html = `
<div class="row">
<div class="col-md-6">
<div class="box box-info">
<div class="box-header with-border">
<h3 class="box-title"><i class="fa fa-user"></i> Customer Information</h3>
</div>
<div class="box-body">
<table class="table table-striped">
<tr><td><strong>Name:</strong></td><td>${customer.name || 'N/A'}</td></tr>
<tr><td><strong>Business:</strong></td><td>${customer.supplier_business_name || 'N/A'}</td></tr>
<tr><td><strong>Contact ID:</strong></td><td>${customer.contact_id || customer.id || 'N/A'}</td></tr>
<tr><td><strong>Mobile:</strong></td><td>${customer.mobile || 'N/A'}</td></tr>
<tr><td><strong>Email:</strong></td><td>${customer.email || 'N/A'}</td></tr>
<tr><td><strong>Address:</strong></td><td>${customer.address_line_1 || 'N/A'}</td></tr>
</table>
</div>
</div>
</div>
<div class="col-md-6">
<div class="box box-success">
<div class="box-header with-border">
<h3 class="box-title"><i class="fa fa-bar-chart"></i> ${year} Summary</h3>
</div>
<div class="box-body">
<div class="row text-center">
<div class="col-md-6">
<div class="description-block border-right">
<span class="description-percentage text-green"><i class="fa fa-shopping-cart"></i></span>
<h5 class="description-header">${transactions.length || 0}</h5>
<span class="description-text">TOTAL TRANSACTIONS</span>
</div>
</div>
<div class="col-md-6">
<div class="description-block">
<span class="description-percentage text-yellow"><i class="fa fa-money"></i></span>
<h5 class="description-header">${formatCurrency(calculateTotalAmount(transactions))}</h5>
<span class="description-text">TOTAL SALES</span>
</div>
</div>
</div>
<div class="row text-center" style="margin-top: 15px;">
<div class="col-md-6">
<div class="description-block border-right">
<span class="description-percentage text-blue"><i class="fa fa-calculator"></i></span>
<h5 class="description-header">${formatCurrency(calculateAverageTransaction(transactions))}</h5>
<span class="description-text">AVG PER TRANSACTION</span>
</div>
</div>
<div class="col-md-6">
<div class="description-block">
<span class="description-percentage text-red"><i class="fa fa-cubes"></i></span>
<h5 class="description-header">${formatNumber(calculateTotalQuantity(transactions))}</h5>
<span class="description-text">TOTAL QTY</span>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
`;
// Add transactions table if there are transactions
if (transactions && transactions.length > 0) {
html += `
<div class="row">
<div class="col-md-12">
<div class="box box-primary">
<div class="box-header with-border">
<h3 class="box-title"><i class="fa fa-list"></i> Recent Transactions</h3>
<div class="box-tools pull-right">
<span class="label label-primary">${transactions.length} transactions</span>
</div>
</div>
<div class="box-body" style="max-height: 400px; overflow-y: auto;">
<div class="table-responsive">
<table class="table table-striped table-bordered table-hover table-condensed" id="customer-transactions-table">
<thead style="position: sticky; top: 0; background: #f5f5f5; z-index: 10;">
<tr>
<th style="width: 100px;">Date</th>
<th style="width: 120px;">Invoice</th>
<th style="min-width: 200px;">Product</th>
<th style="width: 80px;">Month</th>
<th style="width: 80px;">Qty</th>
<th style="width: 100px;">Unit Price</th>
<th style="width: 120px;">Line Total</th>
<th style="width: 90px;">Status</th>
</tr>
</thead>
<tbody>
`;
transactions.forEach(function(transaction, index) {
var date = new Date(transaction.transaction_date).toLocaleDateString();
var statusClass = '';
var statusText = transaction.payment_status || 'unknown';
switch (statusText.toLowerCase()) {
case 'paid':
statusClass = 'label-success';
break;
case 'due':
statusClass = 'label-danger';
break;
case 'partial':
statusClass = 'label-warning';
break;
default:
statusClass = 'label-default';
}
// Add alternating row colors for better readability
var rowClass = index % 2 === 0 ? 'even-row' : 'odd-row';
html += `
<tr class="${rowClass}" style="font-size: 12px;">
<td>${date}</td>
<td><strong style="font-size: 11px;">${transaction.invoice_no || 'N/A'}</strong></td>
<td style="max-width: 200px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap;"
title="${transaction.product_name || 'N/A'}">${transaction.product_name || 'N/A'}</td>
<td><span class="label label-info" style="font-size: 10px;">${transaction.month_name || 'N/A'}</span></td>
<td class="text-center">${formatNumber(transaction.quantity || 0)}</td>
<td class="text-right">${formatCurrency(transaction.unit_price_inc_tax || 0)}</td>
<td class="text-right"><strong>${formatCurrency(transaction.line_total || 0)}</strong></td>
<td><span class="label ${statusClass}" style="font-size: 10px;">${statusText.toUpperCase()}</span></td>
</tr>
`;
});
html += `
</tbody>
</table>
</div>
</div>
<div class="box-footer">
<div class="row">
<div class="col-sm-6">
<small class="text-muted">
<i class="fa fa-info-circle"></i>
Showing recent ${transactions.length} transactions for ${year}
</small>
</div>
<div class="col-sm-6 text-right">
<small class="text-muted">
Total: <strong>${formatCurrency(calculateTotalAmount(transactions))}</strong>
</small>
</div>
</div>
</div>
</div>
</div>
</div>
`;
} else {
html += `
<div class="row">
<div class="col-md-12">
<div class="alert alert-info">
<i class="fa fa-info-circle"></i> No transactions found for this customer in ${year}.
</div>
</div>
</div>
`;
}
$('#customer_details_content').html(html);
$('#customerDetailsModalLabel').text('Customer Details - ' + (customer.name || 'Unknown') + ' (' + year + ')');
} else {
$('#customer_details_content').html(`
<div class="alert alert-warning">
<i class="fa fa-exclamation-triangle"></i> No customer data found.
</div>
`);
}
},
error: function(xhr, status, error) {
console.error('AJAX Error:', xhr.responseText);
console.error('Status:', status);
console.error('Error:', error);
var errorMessage = 'Error loading customer details.';
if (xhr.responseJSON && xhr.responseJSON.error) {
errorMessage = xhr.responseJSON.error;
} else if (xhr.status === 404) {
errorMessage = 'Customer not found.';
} else if (xhr.status === 403) {
errorMessage = 'Access denied.';
} else if (xhr.status === 500) {
errorMessage = 'Server error occurred.';
}
$('#customer_details_content').html(`
<div class="alert alert-danger">
<i class="fa fa-exclamation-triangle"></i> ${errorMessage}
<br><small>Please try again or contact administrator.</small>
</div>
`);
}
});
});
// Helper functions for calculations
function calculateTotalAmount(transactions) {
var total = 0;
transactions.forEach(function(t) {
total += parseFloat(t.line_total || 0);
});
return total;
}
function calculateAverageTransaction(transactions) {
if (transactions.length === 0) return 0;
return calculateTotalAmount(transactions) / transactions.length;
}
function calculateTotalQuantity(transactions) {
var total = 0;
transactions.forEach(function(t) {
total += parseFloat(t.quantity || 0);
});
return total;
}
// Load initial summary
loadSummary();
});
</script>
<style>
/* Keep your existing DataTable styling exactly as is */
.small-box {
min-height: 130px !important;
height: 130px !important;
display: flex !important;
flex-direction: column !important;
position: relative !important;
margin-bottom: 20px !important;
border-radius: 8px !important;
overflow: hidden !important;
box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1) !important;
transition: all 0.2s ease !important;
}
.small-box .inner {
padding: 15px !important;
flex-grow: 1 !important;
display: flex !important;
flex-direction: column !important;
justify-content: center !important;
position: relative !important;
z-index: 2 !important;
}
.small-box .inner h3 {
font-size: 28px !important;
font-weight: 600 !important;
margin: 0 0 8px 0 !important;
line-height: 1 !important;
color: #ffffff !important;
white-space: nowrap !important;
overflow: hidden !important;
text-overflow: ellipsis !important;
}
.small-box .inner p {
font-size: 13px !important;
margin: 0 !important;
line-height: 1.2 !important;
color: rgba(255, 255, 255, 0.9) !important;
font-weight: 400 !important;
}
.small-box .icon {
position: absolute !important;
top: 15px !important;
right: 15px !important;
z-index: 1 !important;
font-size: 40px !important;
color: rgba(255, 255, 255, 0.2) !important;
}
.small-box:hover {
transform: translateY(-2px) !important;
box-shadow: 0 4px 15px rgba(0, 0, 0, 0.15) !important;
}
/* Colors */
.small-box.bg-aqua {
background-color: #3498db !important;
}
.small-box.bg-green {
background-color: #2ecc71 !important;
}
.small-box.bg-yellow {
background-color: #f39c12 !important;
}
.small-box.bg-red {
background-color: #e74c3c !important;
}
.small-box.bg-orange {
background-color: #e67e22 !important;
}
.small-box.bg-purple {
background-color: #9b59b6 !important;
}
/* Style for tables with colored headers */
table.colored-header thead th {
background-color: #3498db;
color: white;
font-weight: bold;
border-bottom: none;
}
/* Footer styling */
.footer-total {
background-color: #f5f5f5 !important;
font-weight: bold !important;
font-size: 11px !important;
}
.footer-total td {
border-top: 2px solid #ddd !important;
padding: 6px 4px !important;
white-space: nowrap !important;
font-size: 11px !important;
}
/* Responsive adjustments */
@media (max-width: 992px) {
.small-box {
min-height: 120px !important;
height: 120px !important;
}
.small-box .inner h3 {
font-size: 26px !important;
}
.small-box .inner p {
font-size: 12px !important;
}
.small-box .icon {
font-size: 35px !important;
}
}
@media (max-width: 768px) {
.small-box {
min-height: 110px !important;
height: 110px !important;
margin-bottom: 15px !important;
}
.small-box .inner {
padding: 12px !important;
}
.small-box .inner h3 {
font-size: 24px !important;
}
.small-box .inner p {
font-size: 11px !important;
}
.small-box .icon {
font-size: 30px !important;
top: 12px !important;
right: 12px !important;
}
}
/* Footer styling */
.footer-total {
background-color: #f5f5f5 !important;
font-weight: bold !important;
font-size: 11px !important;
}
.footer-total td {
border-top: 2px solid #ddd !important;
padding: 6px 4px !important;
white-space: nowrap !important;
font-size: 11px !important;
}
/* Modal styling */
.customer_details_modal .modal-dialog {
max-width: 95%;
width: 1200px;
}
.customer_details_modal .table {
margin-bottom: 0;
}
.customer_details_modal .table td {
padding: 8px;
border: 1px solid #ddd;
}
.customer_details_modal .box {
margin-bottom: 20px;
}
.customer_details_modal .description-block {
padding: 10px;
}
.customer_details_modal .description-header {
font-size: 20px;
font-weight: bold;
margin: 5px 0;
}
.customer_details_modal .description-text {
font-size: 11px;
text-transform: uppercase;
font-weight: 600;
}
.customer_details_modal .description-percentage {
font-size: 20px;
display: block;
margin-bottom: 5px;
}
.customer_details_modal .border-right {
border-right: 1px solid #ddd;
}
.customer_details_modal .text-green {
color: #00a65a !important;
}
.customer_details_modal .text-yellow {
color: #f39c12 !important;
}
.customer_details_modal .text-blue {
color: #3c8dbc !important;
}
.customer_details_modal .text-red {
color: #dd4b39 !important;
}
.customer_details_modal .table-hover tbody tr:hover {
background-color: #f5f5f5;
}
/* Improved transaction table styling */
#customer-transactions-table {
font-size: 12px;
}
#customer-transactions-table th {
font-size: 12px;
font-weight: bold;
text-align: center;
padding: 8px 4px;
border-bottom: 2px solid #ddd;
}
#customer-transactions-table td {
padding: 6px 4px;
font-size: 11px;
vertical-align: middle;
}
#customer-transactions-table .even-row {
background-color: #fafafa;
}
#customer-transactions-table .odd-row {
background-color: #ffffff;
}
#customer-transactions-table tbody tr:hover {
background-color: #e8f4f8 !important;
}
/* Scrollbar styling for transaction list */
.customer_details_modal .box-body::-webkit-scrollbar {
width: 8px;
}
.customer_details_modal .box-body::-webkit-scrollbar-track {
background: #f1f1f1;
border-radius: 4px;
}
.customer_details_modal .box-body::-webkit-scrollbar-thumb {
background: #c1c1c1;
border-radius: 4px;
}
.customer_details_modal .box-body::-webkit-scrollbar-thumb:hover {
background: #a8a8a8;
}
/* Responsive modal */
@media (max-width: 768px) {
.customer_details_modal .modal-dialog {
width: 95%;
margin: 10px auto;
}
.customer_details_modal .description-block {
margin-bottom: 15px;
border-right: none !important;
}
}
/* Style for tables with colored headers */
table.colored-header thead th {
background-color: #3498db;
color: white;
font-weight: bold;
border-bottom: none;
position: sticky;
top: 0;
}
/* Ensure sticky columns maintain header style */
table.colored-header th:first-child,
table.colored-header th:nth-child(2) {
z-index: 3;
/* Higher than regular headers */
}
@media print {
#customer_monthly_table thead th {
background-color: #3498db !important;
color: white !important;
-webkit-print-color-adjust: exact;
print-color-adjust: exact;
}
}
/* IMPROVED Modern Widget Styling - Enhanced version */
.modern-widget {
height: 95px !important;
display: flex !important;
align-items: center !important;
padding: 18px !important;
border-radius: 12px !important;
box-shadow: 0 4px 20px rgba(0, 0, 0, 0.12) !important;
transition: all 0.3s ease !important;
margin-bottom: 20px;
position: relative;
overflow: hidden;
color: white;
border: none !important;
}
.modern-widget::before {
content: '';
position: absolute;
top: 0;
left: 0;
right: 0;
bottom: 0;
background: inherit;
opacity: 0.95;
z-index: 1;
}
.modern-widget:hover {
transform: translateY(-4px) scale(1.02) !important;
box-shadow: 0 8px 30px rgba(0, 0, 0, 0.2) !important;
}
/* Icon Enhanced Positioning */
.modern-widget-icon {
font-size: 45px !important;
opacity: 1 !important;
margin-right: 18px !important;
width: 55px !important;
text-align: center !important;
flex-shrink: 0 !important;
color: white !important;
display: flex !important;
align-items: center !important;
justify-content: center !important;
z-index: 2 !important;
position: relative !important;
text-shadow: 0 2px 4px rgba(0, 0, 0, 0.3) !important;
}
/* Content Area Enhanced */
.modern-widget-content {
flex-grow: 1 !important;
display: flex !important;
flex-direction: column !important;
justify-content: center !important;
min-height: 65px !important;
color: white !important;
z-index: 2 !important;
position: relative !important;
}
/* Text Styling Enhanced */
.modern-widget-text {
font-size: 13px !important;
opacity: 0.98 !important;
font-weight: 600 !important;
text-transform: uppercase !important;
letter-spacing: 1px !important;
margin-bottom: 6px !important;
line-height: 1.2 !important;
color: white !important;
text-shadow: 0 1px 3px rgba(0, 0, 0, 0.2) !important;
}
/* Number Styling Enhanced */
.modern-widget-number {
font-size: 26px !important;
font-weight: 700 !important;
line-height: 1 !important;
color: white !important;
text-shadow: 0 2px 4px rgba(0, 0, 0, 0.2) !important;
margin: 0 !important;
font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif !important;
}
/* Enhanced Widget Color Schemes with better gradients */
.widget-customers {
background: linear-gradient(135deg, #667eea 0%, #764ba2 100%) !important;
}
.widget-transactions {
background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%) !important;
}
.widget-sales {
background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%) !important;
}
.widget-profit {
background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%) !important;
}
.widget-margin {
background: linear-gradient(135deg, #fa709a 0%, #fee140 100%) !important;
}
.widget-average {
background: linear-gradient(135deg, #a8edea 0%, #fed6e3 100%) !important;
color: #333 !important;
}
.widget-average * {
color: #333 !important;
text-shadow: none !important;
}
/* Force all text to be white except average */
.modern-widget:not(.widget-average) * {
color: white !important;
}
/* Enhanced Mobile Responsive */
@media (max-width: 768px) {
.modern-widget {
height: 85px !important;
padding: 14px !important;
border-radius: 10px !important;
}
.modern-widget-icon {
font-size: 36px !important;
width: 45px !important;
margin-right: 12px !important;
}
.modern-widget-text {
font-size: 11px !important;
letter-spacing: 0.5px !important;
}
.modern-widget-number {
font-size: 22px !important;
}
.modern-widget-content {
min-height: 55px !important;
}
}
@media (max-width: 992px) {
.modern-widget {
height: 90px !important;
padding: 16px !important;
}
.modern-widget-icon {
font-size: 40px !important;
width: 50px !important;
}
.modern-widget-text {
font-size: 12px !important;
}
.modern-widget-number {
font-size: 24px !important;
}
}
/* Ensure Bootstrap columns are equal height */
.row {
display: flex;
flex-wrap: wrap;
}
.col-xl-2,
.col-lg-4,
.col-md-6,
.col-sm-6,
.col-xs-12 {
display: flex;
flex-direction: column;
}
</style>
@endsection
Step 4: Add Routes​
Add the following routes to your routes/web.php or module routes file:
use App\Http\Controllers\CustomerMonthlySalesController;
Route::middleware(['setData', 'auth', 'SetSessionData', 'language', 'timezone', 'AdminSidebarMenu', 'CheckUserLogin'])->group(function () {
// After Route::get('/reports/get-stock-value', [ReportController::class, 'getStockValue']);
// Customer Monthly Sales Report Routes
Route::get('/reports/customer-monthly-sales', [CustomerMonthlySalesController::class, 'index'])
->name('reports.customer-monthly-sales');
Route::get('/reports/customer-monthly-sales/data', [CustomerMonthlySalesController::class, 'getCustomerMonthlyData'])
->name('reports.customer-monthly-sales.data');
Route::get('/reports/customer-monthly-sales/summary', [CustomerMonthlySalesController::class, 'getSummary'])
->name('reports.customer-monthly-sales.summary');
Route::get('/reports/customer-monthly-sales/details/{customerId}', [CustomerMonthlySalesController::class, 'getCustomerDetails'])
->name('reports.customer-monthly-sales.details');
});
Step 5: Navigation Menu Integration​
Add the Customer Monthly Sales Report route to the AdminSidebarMenu.php file. Can place it after this nafigation:
if (config('constants.show_report_607') == true) {
$sub->url(
action([\App\Http\Controllers\ReportController::c'saleReport']),
'Report 607 (' . __('business.sale') . ')',
['icon' => '', 'active' => request()->segment('sale-report']
);
}
// Customer Monthly Sales Report
if (auth()->user()->can('view_reports')) {
$sub->url(
route('reports.customer-monthly-sales'),
__('Customer Monthly Sales Report'),
[
'icon' => 'fa fa-calendar-o',
'active' => request()->segment(2) == 'customer-monthly-sales' ||
(request()->segment(1) == 'reports' &&
request()->segment(2) == 'customer-monthly-sales')
]
);
}
Key Features Explained​
1. Monthly Sales Breakdown​
The report shows sales data for each customer across all 12 months, making it easy to identify seasonal patterns and customer behavior.
2. Profit Analysis​
- Gross Profit ($): Calculated as Sales - Cost of Goods Sold
- Gross Profit (%): Profit margin percentage for each customer
- Color coding: Green for positive, Red for negative profits
3. Advanced Filtering​
- Customer name search
- Business location filter
- Payment status filter
- Payment method filter
- Staff/user filter
- Year selection
4. Interactive Summary Cards​
Real-time summary statistics that update based on applied filters:
- Total customers count
- Total transactions
- Total sales amount
- Total profit
- Overall profit margin
- Average sales per customer
5. Customer Details Modal​
Click the "View" button to see detailed information about any customer:
- Customer contact information
- Recent transaction history
- Monthly breakdown (if implemented)
6. Responsive Design​
- Mobile-friendly interface
- Sticky columns for better navigation
- Horizontal scrolling for the monthly data table
Database Query Optimization​
The report uses optimized queries with proper joins and indexing:
-- Recommended indexes for better performance
CREATE INDEX idx_transactions_business_date ON transactions(business_id, transaction_date);
CREATE INDEX idx_transactions_contact_date ON transactions(contact_id, transaction_date);
CREATE INDEX idx_transaction_sell_lines_transaction ON transaction_sell_lines(transaction_id);
Troubleshooting​
Performance Optimization:​
- Large Dataset: Consider adding pagination or date range limits
- Slow Queries: Add database indexes on frequently queried columns
- Memory Issues: Implement server-side processing for very large datasets
Advanced Customizations​
Adding New Metrics:​
You can extend the report by adding new calculated columns:
// In the DataTables response
->addColumn('average_order_value', function ($row) {
$avg = $row['total_transactions'] > 0 ? ($row['total_sales'] / $row['total_transactions']) : 0;
return '<span class="display_currency" data-currency_symbol="true">' .
number_format($avg, 2) . '</span>';
})
Custom Date Ranges:​
Replace the year filter with custom date range:
// Add to filters
<div class="col-md-2">
<div class="form-group">
{!! Form::label('start_date', 'Start Date:') !!}
{!! Form::text('start_date', null, ['class' => 'form-control', 'id' => 'start_date', 'readonly']); !!}
</div>
</div>
Export Functionality:​
If you need export functionality later, you can add:
// In controller
public function export(Request $request)
{
// Implementation for Excel/PDF export
return Excel::download(new CustomerMonthlySalesExport($filters), 'customer_monthly_sales.xlsx');
}
Conclusion​
This Customer Monthly Sales Report provides comprehensive insights into customer purchasing patterns with a modern, responsive interface. The implementation includes advanced filtering, real-time summaries, and detailed customer views, making it a powerful tool for business analysis.
The report helps identify:
- Top performing customers
- Seasonal buying patterns
- Profit margins by customer
- Customer transaction trends
- Sales performance across different time periods
💛 Support this project