⌂Barcha darslar📁Dars materiallari1 / 1 9-ma’ruza · Hisobotlar va analitika — interaktiv slaydlar
9
TO‘QQIZINCHI MA’RUZA
Korxona ilovalarini dasturlash · 9-mavzu

Hisobotlar va analitika

Hisobot va KPI · SQL agregatsiya · Eloquent aggregate · materiallashtirilgan ko‘rinishlar · PDF, Excel, dashboard
Agregatsiya
GROUP BY
Materialized view
PDF
Dashboard
9-ma’ruza · Hisobotlar va analitika
1 / 39
Isinish

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.

1
Savdo — yaratilgan buyurtmalarmi, hisob-fakturalarmi yoki jo‘natilgan tovarmi?
2
10 000 ta hisob-fakturani PHP ga yuklab, foreach bilan qo‘shish yaxshi g‘oyami?
3
Agar hisobot har safar 8 soniya hisoblansa va uni 30 kishi ochsa, nima bo‘ladi?
9-ma’ruza · Hisobotlar va analitika
2 / 39
O‘quv maqsadlari

Bu ma’ruza oxirida siz quyidagilarni bajara olasiz...

1
Ta’riflash — rollar uchun hisobotlar va KPI larni: savdo, QQS, o‘rtacha chek, debitorlik, kam qoldiq
2
Yozish — PostgreSQL agregatsiya so‘rovlarini: SUM, COUNT, GROUP BY, HAVING, FILTER, date_trunc
3
Qo‘llash — oyna funksiyalarini (SUM() OVER, LAG, RANK) yig‘ma va o‘sish ko‘rsatkichlari uchun
4
Qo‘llash — Eloquent aggregate (sum, withSum, withCount) va Query Builder (selectRaw, groupBy)
5
Farqlash — oddiy va materiallashtirilgan ko‘rinish (VIEW / MATERIALIZED VIEW) hamda ularni yangilash usullarini
6
Yaratish — barryvdh/laravel-dompdf bilan PDF va maatwebsite/excel bilan ko‘p varaqli Excel hisobotlarini
7
Loyihalash — Chart.js diagrammali dashboard va uning ma’lumot endpointini
9-ma’ruza · Hisobotlar va analitika
3 / 39
01

Korxonada hisobotlar va KPI

Kim nimani ko‘radi · ko‘rsatkich ta’rifi · OLTP va analitika · hisoblash bazada
KPI
Ta’rif
OLTP
Rollar
9-ma’ruza · Hisobotlar va analitika
4 / 39
ROLLAR VA HISOBOTLAR

Kim qaysi hisobotni ko‘radi

RolHisobotManba jadvallar
omborchiqoldiqlar, kam qoldiq ro‘yxatistocks, products, warehouses
omborchiombor harakatlari jurnalistock_movements
menejerdavr bo‘yicha savdo, top mahsulotlarinvoices, order_items
menejermijozlar reytingiinvoices, counterparties
buxgalterdebitorlik va muddati (aging)invoices (total − paid_amount, due_at)
buxgalterto‘lovlar usullar bo‘yichapayments
admin / rahbardashboard: KPI va trendlaryuqoridagilarning yig‘masi
Har bir hisobot — ReportService dagi bitta metod; ekran, PDF, Excel va API shuni chaqiradi.
9-ma’ruza · Hisobotlar va analitika
5 / 39
KPI TA’RIFLARI

OmborPro ko‘rsatkichlari (kelishuv)

1Savdo (jami)

Davr ichida 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

Jami savdo / hisob-fakturalar soni (butun so‘mgacha).

4Debitorlik

status ≠ paid hisob-fakturalar bo‘yicha total − paid_amount yig‘indisi.

5Muddati o‘tgan qarz

Debitorlikning 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.
Sanalar Asia/Tashkent vaqt zonasida: oy 1-kuni 00:00 dan keyingi oy 1-kuni 00:00 gacha.
9-ma’ruza · Hisobotlar va analitika
6 / 39
SOF PHP

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;
▶ NATIJA
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
array_column + array_sum
PHP dagi SUM(col) — kichik massivlar uchun
'vat' har qatorda
QQS har bir hisob-fakturada yaxlitlanadi, yig‘mada qayta hisoblanmaydi
intdiv()
o‘rtacha chek butun so‘mgacha — float yo‘q
number_format($x, 0, ',', ' ')
o‘zbekcha ko‘rinish: minglik ajratuvchi — bo‘sh joy
9-ma’ruza · Hisobotlar va analitika
7 / 39
Tushunishni tekshirishO‘ylang – Juftlikda – Bo‘lishing · 3 daqiqa

1-bo‘lim yakuni: Korxonada hisobotlar va KPI

1
Nima uchun hisobotlar uchun ko‘rsatkich ta’rifi oldindan yozib qo‘yilishi kerak?
Savol 1
2
OLTP so‘rovlari va hisobot so‘rovlarining farqi nimada?
Savol 2
3
Nima uchun savdo summasini PHP foreach da emas, bazada hisoblaymiz?
Savol 3
9-ma’ruza · Hisobotlar va analitika
8 / 39
Ma’ruzadagi mashqHar kim o‘z qurilmasida · 10 daqiqa · Oson · 10 ball

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.

CHAQIRUV
kpi_hisobot([['subtotal' => 1250000, 'vat' => 150000, 'paid' => 1400000], ['subtotal' => 3100000, 'vat' => 372000, 'paid' => 0], ['subtotal' => 820000, 'vat' => 98400, 'paid' => 500000]])
NATIJA
{"count":3,"subtotal":5170000,"vat":620400,"total":5790400,"avg_check":1930133,"receivable":3890400,"paid_share":32.8}
Telefon kamerasi bilan skanerlang
1. Saytga kiring (login va parol)
2. PHP kodni yozing va sinab ko‘ring
3. ✓ Topshirish
9-ma’ruza · Hisobotlar va analitika
9 / 39
02

SQL agregatsiya so‘rovlari

SUM, COUNT, AVG · GROUP BY va HAVING · date_trunc · FILTER · oyna funksiyalari · NULL tuzoqlari
GROUP BY
HAVING
FILTER
Window
9-ma’ruza · Hisobotlar va analitika
10 / 39
SQL

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;
▶ NATIJA
     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
date_trunc('month', ...)
sanani oy boshiga keltiradi — guruh kaliti
GROUP BY 1
SELECT dagi 1-ustun bo‘yicha (qisqa yozuv)
>= ... AND < ...
yarim ochiq oraliq: oy oxiridagi vaqtli yozuvlar yo‘qolmaydi
SUM(subtotal), SUM(vat_amount)
QQS har bir hujjatdagi qiymatdan yig‘iladi
9-ma’ruza · Hisobotlar va analitika
11 / 39
SQL

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;
▶ NATIJA
       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
FILTER (WHERE ...)
bitta o‘tishda bir nechta shartli yig‘indi
COALESCE(..., 0)
mos qator bo‘lmasa SUM NULL beradi — 0 ga almashtiramiz
HAVING
guruhdan keyingi filtr: faqat 10 mln dan katta mijozlar
GROUP BY c.id, c.name
agregat bo‘lmagan ustunlar guruh kalitida
9-ma’ruza · Hisobotlar va analitika
12 / 39
SOF PHP

GROUP 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);
}
▶ NATIJA
2026-09 | 1 | 1400000 | 1400000
2026-10 | 2 | 4390400 | 5790400
2026-11 | 1 |  560000 | 6350400
$byMonth[$m] ??= ...
guruh birinchi uchraganda yaratiladi
ksort()
kalitlar YYYY-MM — matn tartibi xronologik tartib bilan bir xil
$run
oyna funksiyasining yig‘ma summasi (running total)
Faqat namuna
real hisobotda bu ishni baza bajaradi — PHP ga 3 qator keladi
9-ma’ruza · Hisobotlar va analitika
13 / 39
▶ INTERAKTIV
SQL natijasini oldindan ayting
Agregatsiya so‘rovlari haqidagi savollar.
2-bo‘lim
Tushunishni tekshirishO‘ylang – Juftlikda – Bo‘lishing · 3 daqiqa

2-bo‘lim yakuni: SQL agregatsiya so‘rovlari

1
WHERE va HAVING ning farqi nimada?
Savol 1
2
Nima uchun davr filtri BETWEEN emas, >= ... AND < ... ko‘rinishida yoziladi?
Savol 2
3
FILTER (WHERE ...) qanday vazifani bajaradi?
Savol 3
9-ma’ruza · Hisobotlar va analitika
15 / 39
Ma’ruzadagi mashqHar kim o‘z qurilmasida · 10 daqiqa · O‘rta · 20 ball

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 [].

CHAQIRUV
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]])
NATIJA
[{"month":"2026-09","count":1,"sum":1400000,"running":1400000},{"month":"2026-10","count":2,"sum":4390400,"running":5790400}]
Telefon kamerasi bilan skanerlang
1. Saytga kiring (login va parol)
2. PHP kodni yozing va sinab ko‘ring
3. ✓ Topshirish
9-ma’ruza · Hisobotlar va analitika
16 / 39
03

Eloquent va Query Builder bilan agregatsiya

sum, count, avg · withSum va withCount · selectRaw va groupBy · ReportService
withSum
selectRaw
groupBy
ReportService
9-ma’ruza · Hisobotlar va analitika
17 / 39
ReportService

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();
}
▶ NATIJA
[{"period": "2026-10-01", "invoices": 142, "subtotal": "1511000000.00",
  "vat": "181320000.00", "total": "1692320000.00"}, ...]
match ($group)
SQL ga qo‘yiladigan qism faqat oq ro‘yxatdan — injection yo‘q
$to->addDay()->startOfDay()
yarim ochiq oraliq: to kuni to‘liq kiradi
groupBy('period')
PostgreSQL GROUP BY da SELECT taxallusini qabul qiladi
Collection
natija — stdClass qatorlar; summalar satr (numeric)
9-ma’ruza · Hisobotlar va analitika
18 / 39
ELOQUENT AGGREGATE

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;
}
▶ NATIJA
“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(...)) ...)
withCount('invoices')
invoices_count atributi — subquery
withSum('invoices', 'total')
invoices_sum_total — nom avtomatik
'invoices as paid_total' => fn
shartli agregat va o‘z nomi
?? 0
hisob-fakturasi yo‘q mijozda yig‘indi null
9-ma’ruza · Hisobotlar va analitika
19 / 39
QOLDIQLAR

Ombor 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();
}
▶ NATIJA
 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
quantity - reserved
mavjud qoldiq — spetsifikatsiyadagi invariant
quantity * price
ombor qiymati (rezerv ham ombor ichida)
AS is_low
PostgreSQL mantiqiy natija — kam qoldiq belgisi
whereNull('p.deleted_at')
Query Builder soft delete ni o‘zi hisobga olmaydi
9-ma’ruza · Hisobotlar va analitika
20 / 39
QAYERDA HISOBLAYMIZ

PHP sikli va baza agregati

✗ Noto‘g‘ri

  • Invoice::all()->sum('total') — hamma qator xotirada
  • foreach ($customers as $c) $c->invoices->sum(...) — N+1
  • (float) $row->total bilan yig‘ish — yaxlitlash xatosi
  • selectRaw("... '$sana'") — SQL injection

✓ To‘g‘ri

  • Invoice::where(...)->sum('total') — bitta SELECT SUM
  • withSum('invoices', 'total') — subquery, bitta so‘rov
  • summa bazada numeric, PHP da satr yoki tiyin
  • selectRaw('... ?', [$sana]) — bog‘langan qiymat
9-ma’ruza · Hisobotlar va analitika
21 / 39
Tushunishni tekshirishO‘ylang – Juftlikda – Bo‘lishing · 3 daqiqa

3-bo‘lim yakuni: Eloquent va Query Builder bilan agregatsiya

1
Invoice::where(...)->sum('total') va Invoice::all()->sum('total') ning farqi nimada?
Savol 1
2
withSum('invoices', 'total') natijasi qaysi atributda bo‘ladi va nechta so‘rov bajariladi?
Savol 2
3
selectRaw ga sana qiymatini qanday xavfsiz beramiz?
Savol 3
9-ma’ruza · Hisobotlar va analitika
22 / 39
04

Materiallashtirilgan ko‘rinishlar (PostgreSQL)

VIEW va MATERIALIZED VIEW · migratsiyada yaratish · REFRESH CONCURRENTLY · Eloquent modeli
VIEW
MATERIALIZED VIEW
REFRESH
Unique index
9-ma’ruza · Hisobotlar va analitika
23 / 39
VIEW VA MATERIALIZED VIEW

Saqlangan so‘rov va saqlangan natija

VIEW — so‘rovning nomi; MATERIALIZED VIEW — so‘rov natijasining nusxasi. Birinchisi doim yangi, ikkinchisi doim tez.

VIEW

Ma’lumot saqlamaydi; har murojaatda SELECT bajariladi. Murakkab JOIN larni yashirish uchun.

MATERIALIZED VIEW

Natija diskda; indeks qurish mumkin; REFRESH gacha eskiradi.

REFRESH ... CONCURRENTLY

O‘qishni bloklamay yangilaydi; UNIQUE indeks shart.

Yig‘ma jadval

Kod bilan har hujjatda yangilanadi — doim aniq, lekin murakkabroq.
Hisobot ekranida ma’lumot sanasini ko‘rsating: “27.10.2026 03:00 holatiga”.
9-ma’ruza · Hisobotlar va analitika
24 / 39
MIGRATSIYA

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');
}
▶ NATIJA
$ php artisan migrate
  2026_10_27_090000_create_mv_sales_monthly ......... 41ms DONE

omborpro=# SELECT count(*) FROM mv_sales_monthly;
 count
-------
  1874
<<<'SQL' ... SQL
nowdoc — ko‘p qatorli SQL, $ almashtirilmaydi
GROUP BY 1, 2
oy va mijoz — dashboard va mijoz kartochkasi uchun
CREATE UNIQUE INDEX
REFRESH ... CONCURRENTLY uchun shart
DROP ... IF EXISTS
migratsiyani orqaga qaytarish xavfsiz
9-ma’ruza · Hisobotlar va analitika
25 / 39
MODEL VA BUYRUQ

Faqat 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;
    }
}
▶ NATIJA
$ 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 ms
$table = 'mv_sales_monthly'
model ko‘rinishga ulanadi — faqat o‘qish
$timestamps = false
ko‘rinishda created_at/updated_at yo‘q
reports:refresh
import yoki tunda ishga tushadi (rejalashtirish — 10-mavzu)
CONCURRENTLY
yangilash paytida dashboard ishlashda davom etadi
9-ma’ruza · Hisobotlar va analitika
26 / 39
TANLOV

Og‘ir hisobotni qanday tezlashtiramiz

UsulYangilikTezlikMurakkablik
Oddiy so‘rov + indeksdoim aniqo‘rtachapast
VIEWdoim aniqso‘rov bilan bir xilpast
MATERIALIZED VIEWREFRESH gacha eskiradijuda tezo‘rtacha
Yig‘ma jadvaldoim aniq (kod to‘g‘ri bo‘lsa)juda tezyuqori
Kesh (Redis, 13-mavzu)TTL gacha eskiradijuda tezpast–o‘rtacha
Avval indeks va to‘g‘ri so‘rov; yetmasa — materiallashtirilgan ko‘rinish yoki kesh.
9-ma’ruza · Hisobotlar va analitika
27 / 39
Tushunishni tekshirishO‘ylang – Juftlikda – Bo‘lishing · 3 daqiqa

4-bo‘lim yakuni: Materiallashtirilgan ko‘rinishlar (PostgreSQL)

1
VIEW va MATERIALIZED VIEW ning asosiy farqi nimada?
Savol 1
2
REFRESH MATERIALIZED VIEW CONCURRENTLY uchun qanday shart bor?
Savol 2
3
Materiallashtirilgan ko‘rinishga ulangan Eloquent modelida nimalar o‘ziga xos?
Savol 3
9-ma’ruza · Hisobotlar va analitika
28 / 39
05

PDF va Excel hisobotlari, dashboard

laravel-dompdf · DejaVu Sans · ko‘p varaqli Excel · Chart.js va @json · dashboard KPI
dompdf
Excel
Chart.js
Dashboard
9-ma’ruza · Hisobotlar va analitika
29 / 39
PDF

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');
}
▶ NATIJA
$ 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::loadView()
Blade shablon → HTML → PDF
setPaper('a4', 'landscape')
keng jadval uchun albom yo‘nalishi
download() / stream()
yuklab olish yoki brauzerda ochish
$reports->stockBalances()
ekran, PDF va Excel — bitta servis metodi
9-ma’ruza · Hisobotlar va analitika
30 / 39
BLADE (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>
▶ NATIJA
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
'DejaVu Sans'
dompdf bilan keladigan Unicode shrift — o‘, g‘ to‘g‘ri chiqadi
border-collapse
dompdf CSS 2.1 ni qo‘llaydi — maket jadval bilan
page-break-inside: avoid
qator ikki sahifaga bo‘linmaydi
@money(...)
Blade::directive('money', ...) — number_format($x, 2, ',', ' ') bitta joyda
9-ma’ruza · Hisobotlar va analitika
31 / 39
EXCEL

Ko‘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),
        ];
    }
}
▶ NATIJA
Excel::download(new SalesReportExport($from, $to), 'savdo-2026.xlsx')
→ varaqlar: [Oylar bo‘yicha] [Top mahsulotlar] [Debitorlik]
   SalesByMonthSheet: FromQuery, WithTitle, WithHeadings,
   WithColumnFormatting → 'C' => '#,##0.00'
WithMultipleSheets
sheets() — har bir element alohida varaq sinfi
WithTitle
varaq nomi: title(): string
WithColumnFormatting
NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 — pul son sifatida
$from, $to
barcha varaqlar bir xil davr uchun — ziddiyat yo‘q
9-ma’ruza · Hisobotlar va analitika
32 / 39
DASHBOARD

Chart.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>
▶ NATIJA
$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 — 0
@json(...)
PHP massivi → JSON, HTML uchun xavfsiz ekranlangan
$chart['labels']
barcha oylar, ma’lumotsizlari ham — ReportService to‘ldiradi
mln so‘m
diagrammada katta sonlarni masshtablash — o‘qilishi oson
wire:poll.60s
Livewire bilan KPI va diagrammani davriy yangilash
9-ma’ruza · Hisobotlar va analitika
33 / 39
Tushunishni tekshirishO‘ylang – Juftlikda – Bo‘lishing · 3 daqiqa

5-bo‘lim yakuni: PDF va Excel hisobotlari, dashboard

1
dompdf da o‘zbek harflari to‘g‘ri chiqishi uchun nima qilinadi?
Savol 1
2
Excel hisobotda pul summasini nima uchun matn emas, son sifatida yozamiz?
Savol 2
3
Blade da PHP massivini Chart.js ga qanday xavfsiz uzatamiz?
Savol 3
9-ma’ruza · Hisobotlar va analitika
34 / 39
Ma’ruzadagi mashqHar kim o‘z qurilmasida · 10 daqiqa · Oson · 10 ball

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.

CHAQIRUV
summa_format(123456789)
NATIJA
"1 234 567,89 so‘m"
Telefon kamerasi bilan skanerlang
1. Saytga kiring (login va parol)
2. PHP kodni yozing va sinab ko‘ring
3. ✓ Topshirish
9-ma’ruza · Hisobotlar va analitika
35 / 39
Xulosa

Asosiy xulosalar

1Avval ta’rif

Savdo, debitorlik, kam qoldiq — aniq ta’rif va bitta 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

Og‘ir hisobot natijasi saqlanadi; REFRESH ... CONCURRENTLY uchun UNIQUE indeks.

6PDF, Excel, dashboard

dompdf + DejaVu Sans; ko‘p varaqli Excel, pul son sifatida; Chart.js + @json.
9-ma’ruza · Hisobotlar va analitika
36 / 39
Takrorlash

Yakunlashdan oldin sakkizta savol

1
COUNT(*) va COUNT(col) farqi?
2
Bo‘sh to‘plamning SUM i nima?
3
Oylar bo‘yicha guruhlash PostgreSQL da?
4
withSum('invoices', 'total') atributi?
5
Yig‘ma summa uchun SQL?
6
MATERIALIZED VIEW qachon yangilanadi?
7
dompdf da qaysi shrift o‘zbek harflari uchun?
8
Blade dan Chart.js ga ma’lumot?
9-ma’ruza · Hisobotlar va analitika
37 / 39
Amaliy mashg‘ulot

9-amaliy mashg‘ulot: 5 ta PHP topshiriq va loyiha bosqichi

4

Ombor qoldiqlari hisoboti

Oson · 10 ball
5

Davr bo‘yicha savdo (kun / hafta / oy)

O‘rta · 20 ball
6

Debitorlik qarzining muddati (aging)

O‘rta · 20 ball
7

Ombor × mahsulot pivot jadvali

Qiyin · 30 ball
8

Diagramma uchun oylik qator

Qiyin · 30 ball
Telefon kamerasi bilan skanerlang

PHP kod saytda yoziladi va server tomonidan avtomatik tekshiriladi. Loyiha havolasini ham shu sahifada topshirasiz; o‘qituvchi baho qo‘yadi.

9-ma’ruza · Hisobotlar va analitika
38 / 39
Chiqish chiptasi

Darsni tark etishdan oldin

Javoblaringizni yozing (✎ Javob yozish) yoki qog‘ozga yozib topshiring.

1
Jamoangiz loyihasida “savdo” ko‘rsatkichini bir gap bilan ta’riflang (qaysi hujjat, qaysi sana, QQS bilan/siz).
2
Debitorlikni mijozlar bo‘yicha chiqaradigan SQL so‘rovning SELECT, WHERE, GROUP BY qismlarini yozing.
3
Qaysi hisobotingiz uchun materiallashtirilgan ko‘rinish kerak bo‘lishi mumkin va nima uchun?
9-ma’ruza · Hisobotlar va analitika
39 / 39
3:00
💡 Javoblar
✎ Chiqish chiptasi
🗒 O‘qituvchi eslatmalari
▶ Ushbu dars mashg‘ulotlari
💻 9-amaliy mashg‘ulot (topshiriqlar)📖 Ma’ruza matni01 Moslashtirish02 Natijani toping03 Tasniflash04 Kodni tartiblash05 Xatoni toping06 Yakuniy sinov📁 9-mavzu materiallari
? Qanday foydalanish
MAnimatsiyani yoqish / o‘chirish
→ / SpaceKeyingi (yoki keyingi qism)
←Oldingi
BBosqichma-bosqich ochish yoq/o‘ch
OBarcha slaydlar
NO‘qituvchi eslatmalari
AJavoblar / asosiy amal
PQalam: slaydga chizish
FTo‘liq ekran
EscPanellarni yopish

Kartani ajratib ko‘rsatish uchun ustiga bosing. Tekshiruv va takrorlash slaydlarida javobni ko‘rish uchun bandni bosing. ▶ INTERAKTIV belgili slaydlar — amaliy mashg‘ulotlar.