Hisobotlar va analitika
Direktor so‘radi: “O‘tgan oy qancha sotdik va mijozlar bizga qancha qarz?” Javobni qayerdan olasiz?
Hozircha javob bermang. “Sotdik” so‘zini qanday tushunish mumkinligini o‘ylab ko‘ring.
foreach bilan qo‘shish yaxshi g‘oyami?Bu ma’ruza oxirida siz quyidagilarni bajara olasiz...
SUM, COUNT, GROUP BY, HAVING, FILTER, date_truncSUM() OVER, LAG, RANK) yig‘ma va o‘sish ko‘rsatkichlari uchunsum, withSum, withCount) va Query Builder (selectRaw, groupBy)barryvdh/laravel-dompdf bilan PDF va maatwebsite/excel bilan ko‘p varaqli Excel hisobotlariniKorxonada hisobotlar va KPI
Kim qaysi hisobotni ko‘radi
| Rol | Hisobot | Manba jadvallar |
|---|---|---|
| omborchi | qoldiqlar, kam qoldiq ro‘yxati | stocks, products, warehouses |
| omborchi | ombor harakatlari jurnali | stock_movements |
| menejer | davr bo‘yicha savdo, top mahsulotlar | invoices, order_items |
| menejer | mijozlar reytingi | invoices, counterparties |
| buxgalter | debitorlik va muddati (aging) | invoices (total − paid_amount, due_at) |
| buxgalter | to‘lovlar usullar bo‘yicha | payments |
| admin / rahbar | dashboard: KPI va trendlar | yuqoridagilarning yig‘masi |
ReportService dagi bitta metod; ekran, PDF, Excel va API shuni chaqiradi.OmborPro ko‘rsatkichlari (kelishuv)
1Savdo (jami)
issued_at bo‘yicha berilgan hisob-fakturalar total yig‘indisi (QQS bilan).2Sof savdo va QQS
subtotal va vat_amount yig‘indilari; QQS stavkasi 12 %.3O‘rtacha chek
4Debitorlik
status ≠ paid hisob-fakturalar bo‘yicha total − paid_amount yig‘indisi.5Muddati o‘tgan qarz
due_at < bugun qismi; 1–30, 31–60, 61–90, 90+ kun guruhlari.6Kam qoldiq
quantity − reserved < min_stock bo‘lgan mahsulot–ombor juftliklari soni.KPI larni butun sonlarda hisoblash
$inv = [ // so‘mda, butun son ['subtotal' => 1250000, 'vat' => 150000, 'paid' => 1400000], ['subtotal' => 3100000, 'vat' => 372000, 'paid' => 0], ['subtotal' => 820000, 'vat' => 98400, 'paid' => 500000], ]; $sum = fn (string $k) => array_sum(array_column($inv, $k)); $total = $sum('subtotal') + $sum('vat'); $fmt = fn (int $x) => number_format($x, 0, ',', ' ') . ' so‘m'; echo 'Hisob-fakturalar: ', count($inv), PHP_EOL; echo 'Sof savdo: ', $fmt($sum('subtotal')), PHP_EOL; echo 'QQS (12 %): ', $fmt($sum('vat')), PHP_EOL; echo 'Jami: ', $fmt($total), PHP_EOL; echo 'O‘rtacha chek: ', $fmt(intdiv($total, count($inv))), PHP_EOL; echo 'Debitorlik: ', $fmt($total - $sum('paid')), PHP_EOL;
Hisob-fakturalar: 3 Sof savdo: 5 170 000 so‘m QQS (12 %): 620 400 so‘m Jami: 5 790 400 so‘m O‘rtacha chek: 1 930 133 so‘m Debitorlik: 3 890 400 so‘m
SUM(col) — kichik massivlar uchun1-bo‘lim yakuni: Korxonada hisobotlar va KPI
foreach da emas, bazada hisoblaymiz?1-topshiriq: KPI kartochkalari
Hisob-fakturalar ro‘yxati berilgan, har biri ['subtotal' => int, 'vat' => int, 'paid' => int] (so‘mda). Dashboard uchun ko‘rsatkichlarni hisoblang: soni, sof savdo, QQS, jami (subtotal + vat), o‘rtacha chek (intdiv(jami, soni), ro‘yxat bo‘sh bo‘lsa 0), debitorlik (jami − to‘langan) va to‘langan ulush foizda (round(to‘langan * 100 / jami, 1), jami 0 bo‘lsa 0.0). Biror qiymat manfiy bo‘lsa — InvalidArgumentException; biror hisob-fakturada paid > subtotal + vat bo‘lsa — DomainException.
Imzo: function kpi_hisobot(array $inv): array
Qaytaradi: ['count' => int, 'subtotal' => int, 'vat' => int, 'total' => int, 'avg_check' => int, 'receivable' => int, 'paid_share' => float] — shu tartibda.
kpi_hisobot([['subtotal' => 1250000, 'vat' => 150000, 'paid' => 1400000], ['subtotal' => 3100000, 'vat' => 372000, 'paid' => 0], ['subtotal' => 820000, 'vat' => 98400, 'paid' => 500000]])
{"count":3,"subtotal":5170000,"vat":620400,"total":5790400,"avg_check":1930133,"receivable":3890400,"paid_share":32.8}SQL agregatsiya so‘rovlari
Oylar bo‘yicha savdo
SELECT date_trunc('month', issued_at)::date AS oy, COUNT(*) AS soni, SUM(subtotal) AS sof_savdo, SUM(vat_amount) AS qqs, SUM(total) AS jami FROM invoices WHERE issued_at >= '2026-07-01' AND issued_at < '2026-11-01' GROUP BY 1 ORDER BY 1;
oy | soni | sof_savdo | qqs | jami 2026-07-01 | 118 | 1 204 500 000 | 144 540 000 | 1 349 040 000 2026-08-01 | 131 | 1 356 200 000 | 162 744 000 | 1 518 944 000 2026-09-01 | 127 | 1 298 750 000 | 155 850 000 | 1 454 600 000 2026-10-01 | 142 | 1 511 000 000 | 181 320 000 | 1 692 320 000
FILTER, HAVING va COALESCE
SELECT c.name AS mijoz, SUM(i.total) AS jami, SUM(i.total) FILTER (WHERE i.status = 'paid') AS tolangan, COALESCE(SUM(i.total - i.paid_amount) FILTER (WHERE i.status <> 'paid'), 0) AS qarz FROM invoices i JOIN counterparties c ON c.id = i.counterparty_id WHERE i.issued_at >= '2026-10-01' AND i.issued_at < '2026-11-01' GROUP BY c.id, c.name HAVING SUM(i.total) > 10000000 ORDER BY jami DESC;
mijoz | jami | tolangan | qarz “Baraka Savdo” MChJ | 184 800 000 | 120 000 000 | 64 800 000 “Nur Market” XK | 56 000 000 | 56 000 000 | 0 “Sharq Ulgurji” MChJ| 12 320 000 | | 12 320 000
SUM NULL beradi — 0 ga almashtiramizGROUP BY va yig‘ma summa — PHP da qanday ishlaydi
$rows = [ ['2026-09-28', 1400000], ['2026-10-02', 3472000], ['2026-10-15', 918400], ['2026-11-01', 560000], ]; $byMonth = []; foreach ($rows as [$date, $total]) { $m = substr($date, 0, 7); // date_trunc('month') $byMonth[$m] ??= ['count' => 0, 'sum' => 0]; $byMonth[$m]['count']++; // COUNT(*) $byMonth[$m]['sum'] += $total; // SUM(total) } ksort($byMonth); // ORDER BY oy $run = 0; foreach ($byMonth as $m => $g) { $run += $g['sum']; // SUM(...) OVER (ORDER BY oy) printf("%s | %d | %7d | %7d\n", $m, $g['count'], $g['sum'], $run); }
2026-09 | 1 | 1400000 | 1400000 2026-10 | 2 | 4390400 | 5790400 2026-11 | 1 | 560000 | 6350400
YYYY-MM — matn tartibi xronologik tartib bilan bir xil2-bo‘lim yakuni: SQL agregatsiya so‘rovlari
WHERE va HAVING ning farqi nimada?BETWEEN emas, >= ... AND < ... ko‘rinishida yoziladi?FILTER (WHERE ...) qanday vazifani bajaradi?2-topshiriq: Oylar bo‘yicha savdo va yig‘ma summa
GROUP BY date_trunc('month', issued_at) va SUM(...) OVER (ORDER BY oy) ning PHP dagi modelini yozing. Har bir hisob-faktura ['issued_at' => 'YYYY-MM-DD' yoki 'YYYY-MM-DD HH:MM:SS', 'total' => int]. Sana YYYY-MM-DD bilan boshlanmasa — InvalidArgumentException. Natija — oylar o‘sish tartibida, har bir oy uchun hujjatlar soni, summa va yil boshidan emas, ro‘yxat boshidan yig‘ma summa.
Imzo: function oylik_savdo(array $invoices): array
Qaytaradi: [['month' => 'YYYY-MM', 'count' => int, 'sum' => int, 'running' => int], ...]; bo‘sh kirishda [].
oylik_savdo([['issued_at' => '2026-10-02', 'total' => 3472000], ['issued_at' => '2026-09-28', 'total' => 1400000], ['issued_at' => '2026-10-15 18:40:00', 'total' => 918400]])
[{"month":"2026-09","count":1,"sum":1400000,"running":1400000},{"month":"2026-10","count":2,"sum":4390400,"running":5790400}]Eloquent va Query Builder bilan agregatsiya
Davr bo‘yicha savdo — Query Builder
public function salesByPeriod( CarbonImmutable $from, CarbonImmutable $to, string $group = 'month' ): Collection { $unit = match ($group) { 'day', 'week', 'month' => $group, default => throw new InvalidArgumentException($group), }; return DB::table('invoices') ->selectRaw("date_trunc('$unit', issued_at)::date AS period") ->selectRaw('COUNT(*) AS invoices, SUM(subtotal) AS subtotal') ->selectRaw('SUM(vat_amount) AS vat, SUM(total) AS total') ->where('issued_at', '>=', $from->startOfDay()) ->where('issued_at', '<', $to->addDay()->startOfDay()) ->groupBy('period') ->orderBy('period') ->get(); }
[{"period": "2026-10-01", "invoices": 142, "subtotal": "1511000000.00",
"vat": "181320000.00", "total": "1692320000.00"}, ...]to kuni to‘liq kiradiGROUP BY da SELECT taxallusini qabul qiladistdClass qatorlar; summalar satr (numeric)Mijozlar reytingi: withCount va withSum
$customers = Counterparty::query() ->whereIn('type', ['customer', 'both']) ->withCount('invoices') ->withSum('invoices', 'total') ->withSum(['invoices as paid_total' => fn ($q) => $q->where('status', 'paid')], 'total') ->orderByDesc('invoices_sum_total') ->limit(10) ->get(); foreach ($customers as $c) { echo $c->name, ': ', $c->invoices_count, ' ta, ', $c->invoices_sum_total, ' / ', $c->paid_total ?? 0, PHP_EOL; }
“Baraka Savdo” MChJ: 37 ta, 184800000.00 / 120000000.00 “Nur Market” XK: 12 ta, 56000000.00 / 56000000.00 ... (SQL: select counterparties.*, (select count(*) ...), (select sum(...)) ...)
invoices_count atributi — subqueryinvoices_sum_total — nom avtomatiknullOmbor qoldiqlari hisoboti
public function stockBalances(?int $warehouseId = null): Collection { return DB::table('stocks as s') ->join('products as p', 'p.id', '=', 's.product_id') ->join('warehouses as w', 'w.id', '=', 's.warehouse_id') ->whereNull('p.deleted_at') ->when($warehouseId, fn ($q, $id) => $q->where('w.id', $id)) ->select('w.code', 'p.sku', 'p.name', 's.quantity') ->addSelect('s.reserved') ->selectRaw('s.quantity - s.reserved AS available') ->selectRaw('s.quantity * p.price AS value') ->selectRaw('s.quantity - s.reserved < p.min_stock AS is_low') ->orderBy('w.code')->orderBy('p.sku') ->get(); }
code | sku | name | quantity | reserved | available | value | is_low TSH-1 | SH-010 | Shakar, 1 kg | 420 | 100 | 320 | 6090000.00 | f TSH-1 | UN-001 | Un oliy nav, 50 kg| 35 | 20 | 15 | 13475000.00 | t
PHP sikli va baza agregati
✗ Noto‘g‘ri
Invoice::all()->sum('total')— hamma qator xotiradaforeach ($customers as $c) $c->invoices->sum(...)— N+1(float) $row->totalbilan yig‘ish — yaxlitlash xatosiselectRaw("... '$sana'")— SQL injection
✓ To‘g‘ri
Invoice::where(...)->sum('total')— bittaSELECT SUMwithSum('invoices', 'total')— subquery, bitta so‘rov- summa bazada
numeric, PHP da satr yoki tiyin selectRaw('... ?', [$sana])— bog‘langan qiymat
3-bo‘lim yakuni: Eloquent va Query Builder bilan agregatsiya
Invoice::where(...)->sum('total') va Invoice::all()->sum('total') ning farqi nimada?withSum('invoices', 'total') natijasi qaysi atributda bo‘ladi va nechta so‘rov bajariladi?selectRaw ga sana qiymatini qanday xavfsiz beramiz?Materiallashtirilgan ko‘rinishlar (PostgreSQL)
Saqlangan so‘rov va saqlangan natija
VIEW
SELECT bajariladi. Murakkab JOIN larni yashirish uchun.MATERIALIZED VIEW
REFRESH gacha eskiradi.REFRESH ... CONCURRENTLY
Yig‘ma jadval
mv_sales_monthly yaratish
public function up(): void { DB::statement(<<<'SQL' CREATE MATERIALIZED VIEW mv_sales_monthly AS SELECT date_trunc('month', issued_at)::date AS month, counterparty_id, COUNT(*) AS invoices_count, SUM(subtotal) AS subtotal, SUM(vat_amount) AS vat, SUM(total) AS total FROM invoices GROUP BY 1, 2 SQL); DB::statement('CREATE UNIQUE INDEX mv_sales_monthly_uq ON mv_sales_monthly (month, counterparty_id)'); } public function down(): void { DB::statement('DROP MATERIALIZED VIEW IF EXISTS mv_sales_monthly'); }
$ php artisan migrate 2026_10_27_090000_create_mv_sales_monthly ......... 41ms DONE omborpro=# SELECT count(*) FROM mv_sales_monthly; count ------- 1874
$ almashtirilmaydiREFRESH ... CONCURRENTLY uchun shartFaqat o‘qiladigan model va yangilash buyrug‘i
class SalesMonthly extends Model { protected $table = 'mv_sales_monthly'; public $timestamps = false; protected $casts = ['month' => 'date', 'total' => 'decimal:2']; } class RefreshReportViews extends Command { protected $signature = 'reports:refresh'; protected $description = 'Hisobot ko‘rinishlarini yangilash'; public function handle(): int { DB::statement('REFRESH MATERIALIZED VIEW CONCURRENTLY ' . 'mv_sales_monthly'); return self::SUCCESS; } }
$ php artisan reports:refresh (0.4 s)
SalesMonthly::where('month', '>=', '2024-01-01')
->selectRaw('month, SUM(total) AS total')->groupBy('month')->get();
→ 34 qator, 3 mscreated_at/updated_at yo‘qOg‘ir hisobotni qanday tezlashtiramiz
| Usul | Yangilik | Tezlik | Murakkablik |
|---|---|---|---|
| Oddiy so‘rov + indeks | doim aniq | o‘rtacha | past |
| VIEW | doim aniq | so‘rov bilan bir xil | past |
| MATERIALIZED VIEW | REFRESH gacha eskiradi | juda tez | o‘rtacha |
| Yig‘ma jadval | doim aniq (kod to‘g‘ri bo‘lsa) | juda tez | yuqori |
| Kesh (Redis, 13-mavzu) | TTL gacha eskiradi | juda tez | past–o‘rtacha |
4-bo‘lim yakuni: Materiallashtirilgan ko‘rinishlar (PostgreSQL)
REFRESH MATERIALIZED VIEW CONCURRENTLY uchun qanday shart bor?PDF va Excel hisobotlari, dashboard
Qoldiqlar hisobotini PDF ga
use Barryvdh\DomPDF\Facade\Pdf; public function stockPdf(Request $request, ReportService $reports) { $this->authorize('viewStockReport'); // Gate $rows = $reports->stockBalances($request->integer('warehouse_id')); $pdf = Pdf::loadView('reports.pdf.stock', [ 'rows' => $rows, 'date' => now('Asia/Tashkent'), 'totalValue' => $rows->sum('value'), ])->setPaper('a4', 'landscape'); return $pdf->download('qoldiq-' . now()->format('Y-m-d') . '.pdf'); }
$ composer require barryvdh/laravel-dompdf GET /reports/stock.pdf?warehouse_id=1 → 200, Content-Type: application/pdf Content-Disposition: attachment; filename=qoldiq-2026-10-27.pdf
PDF shabloni: shrift va jadval
<style> body { font-family: 'DejaVu Sans', sans-serif; font-size: 10px; } table { width: 100%; border-collapse: collapse; } th, td { border: 1px solid #999; padding: 3px 5px; } .num { text-align: right; } tr { page-break-inside: avoid; } </style> <h3>Ombor qoldiqlari — {{ $date->format('d.m.Y H:i') }}</h3> <table> <tr><th>Ombor</th><th>SKU</th><th>Nomi</th> <th>Mavjud</th><th>Qiymati</th></tr> @foreach ($rows as $r) <tr><td>{{ $r->code }}</td><td>{{ $r->sku }}</td> <td>{{ $r->name }}</td> <td class="num">{{ $r->available }}</td> <td class="num">@money($r->value)</td></tr> @endforeach </table> <p>Jami qiymat: @money($totalValue) so‘m</p>
Ombor qoldiqlari — 27.10.2026 14:05 | TSH-1 | SH-010 | Shakar, 1 kg | 320 | 6 090 000,00 | | TSH-1 | UN-001 | Un oliy nav, 50 kg | 15 | 13 475 000,00 | Jami qiymat: 19 565 000,00 so‘m
o‘, g‘ to‘g‘ri chiqadiBlade::directive('money', ...) — number_format($x, 2, ',', ' ') bitta joydaKo‘p varaqli savdo hisoboti
class SalesReportExport implements WithMultipleSheets { public function __construct( private CarbonImmutable $from, private CarbonImmutable $to, ) { } public function sheets(): array { return [ new SalesByMonthSheet($this->from, $this->to), new TopProductsSheet($this->from, $this->to), new ReceivablesSheet($this->to), ]; } }
Excel::download(new SalesReportExport($from, $to), 'savdo-2026.xlsx') → varaqlar: [Oylar bo‘yicha] [Top mahsulotlar] [Debitorlik] SalesByMonthSheet: FromQuery, WithTitle, WithHeadings, WithColumnFormatting → 'C' => '#,##0.00'
sheets() — har bir element alohida varaq sinfititle(): stringNumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 — pul son sifatidaChart.js diagrammasi Blade da
<canvas id="salesChart" height="110"></canvas> <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1"></script> <script> new Chart(document.getElementById('salesChart'), { type: 'bar', data: { labels: @json($chart['labels']), datasets: [{ label: 'Savdo, mln so‘m', data: @json($chart['data']), }], }, options: { responsive: true }, }); </script>
$chart = ['labels' => ['2026-05', ..., '2026-10'],
'data' => [1210.4, 1302.7, 1349.0, 1518.9, 1454.6, 1692.3]]
→ 6 ustunli diagramma; bo‘sh oy — 0ReportService to‘ldiradi5-bo‘lim yakuni: PDF va Excel hisobotlari, dashboard
3-topshiriq: Summani hisobot formatida yozish
PDF va Excel hisobotlarida summa o‘zbekcha ko‘rinishda chiqadi: minglik guruhlar bo‘sh joy bilan, o‘nli ajratuvchi vergul, oxirida “ so‘m”. Kirish — tiyinlarda butun son (100 tiyin = 1 so‘m). Manfiy summa oldida - belgisi bo‘ladi. Float ishlatmang: so‘m va tiyin qismini intdiv va % bilan ajrating.
Imzo: function summa_format(int $tiyin): string
Qaytaradi: Masalan, 123456789 → 1 234 567,89 so‘m; -150000 → -1 500,00 so‘m.
summa_format(123456789)
"1 234 567,89 so‘m"
Asosiy xulosalar
1Avval ta’rif
ReportService; ekran, PDF, Excel bir xil raqam beradi.2Agregatsiya bazada
SUM, COUNT, GROUP BY, HAVING, FILTER, date_trunc; davr — yarim ochiq oraliq.3Oyna funksiyalari
SUM() OVER, LAG, RANK — yig‘ma summa, o‘sish, reyting PHP siz.4Eloquent aggregate
sum(), withCount, withSum — bitta so‘rov; selectRaw faqat bog‘langan qiymatlar bilan.5Materiallashtirilgan ko‘rinish
REFRESH ... CONCURRENTLY uchun UNIQUE indeks.6PDF, Excel, dashboard
@json.Yakunlashdan oldin sakkizta savol
COUNT(*) va COUNT(col) farqi?SUM i nima?withSum('invoices', 'total') atributi?9-amaliy mashg‘ulot: 5 ta PHP topshiriq va loyiha bosqichi
Ombor qoldiqlari hisoboti
Davr bo‘yicha savdo (kun / hafta / oy)
Debitorlik qarzining muddati (aging)
Ombor × mahsulot pivot jadvali
Diagramma uchun oylik qator
Darsni tark etishdan oldin
Javoblaringizni yozing (✎ Javob yozish) yoki qog‘ozga yozib topshiring.
SELECT, WHERE, GROUP BY qismlarini yozing.