9

9-ma’ruza matni · Korxona ilovalarini dasturlash

Hisobotlar va analitika

Hisobot va KPI · SQL agregatsiya · Eloquent aggregate · materiallashtirilgan ko‘rinishlar · PDF, Excel, dashboard

Muammoli savol

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.

Javob

Avval ko‘rsatkichni aniq ta’riflash kerak (masalan, savdo = davr ichida berilgan hisob-fakturalar summasi, QQS bilan). Keyin hisoblashni bazaga topshiramiz: SUM, GROUP BY, date_trunc; og‘ir hisobotlar uchun materiallashtirilgan ko‘rinish. Natija ekranda, PDF da va Excel da bir xil chiqishi kerak.

O‘quv maqsadlari

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

1. Korxonada hisobotlar va KPI

Kim nimani ko‘radi · ko‘rsatkich ta’rifi · OLTP va analitika · hisoblash bazada

Korxona ilovasi ikki xil ish bajaradi: kundalik operatsiyalar (buyurtma, rezerv, to‘lov — OLTP, Online Transaction Processing) va ular asosida qaror qabul qilish (analitika, hisobotlar). OLTP da so‘rovlar kichik va ko‘p: bitta buyurtmani yozish, bitta qoldiqni o‘zgartirish. Hisobot esa minglab qatorlarni o‘qiydi va guruhlaydi. Shu sababli hisobotlar boshqacha loyihalanadi: o‘qish uchun optimallashtiriladi, kerak bo‘lsa oldindan hisoblab qo‘yiladi, katta tizimlarda alohida analitik bazaga (ma’lumotlar ombori, data warehouse) ko‘chiriladi. OmborPro hajmida PostgreSQL ning o‘zi yetarli.

Har bir rolning o‘z hisobotlari bor. Omborchi: omborlar bo‘yicha qoldiqlar (quantity, reserved, mavjud), minimal qoldiqdan past mahsulotlar, ombor harakatlari jurnali. Menejer: davr bo‘yicha savdo, eng ko‘p sotilgan mahsulotlar, mijozlar reytingi, buyurtmalar holati. Buxgalter: debitorlik qarzi va uning muddati (aging), QQS bo‘yicha yig‘ma, to‘lovlar usullari bo‘yicha (cash, card, payme, click, bank). Rahbar: bitta ekranda asosiy ko‘rsatkichlar (KPI) va trendlar — dashboard. Hisobotlarga ruxsat ham rollar bo‘yicha beriladi (5-mavzudagi spatie/laravel-permission): omborchi debitorlikni ko‘rmaydi.

Eng muhim qadam — ko‘rsatkich ta’rifi. “Savdo” deganda nima tushuniladi: tasdiqlangan buyurtmalarmi, berilgan hisob-fakturalarmi, jo‘natilgan tovarmi? QQS bilanmi yoki QQS siz? Qaysi sana bo‘yicha — created_at yoki issued_at? Qaysi vaqt zonasida — UTC yoki Asia/Tashkent (oy oxiridagi kechki hisob-faktura boshqa oyga o‘tib ketishi mumkin)? Bekor qilingan hujjatlar kiradimi? Bu savollarga javoblar loyiha hujjatida yoziladi va bitta joyda — ReportService da — amalga oshiriladi; ekran, PDF, Excel va API shu servisni chaqiradi. Aks holda dashboard bir raqam, Excel boshqa raqam ko‘rsatadi.

Hisoblash qayerda bajariladi? Qoida: agregatsiya bazada. 50 000 ta hisob-fakturani Eloquent modellariga aylantirib, PHP da foreach bilan qo‘shish — sekin (har bir model obyekti xotira va vaqt oladi) va xotirani to‘ldiradi. SELECT SUM(total) ... GROUP BY esa indekslardan foydalanib, natijani bir necha qator ko‘rinishida qaytaradi. Pul summalari bazada numeric(14,2) da yig‘iladi, PHP da esa butun so‘m yoki tiyin sifatida ishlanadi — float da yig‘ish yaxlitlash xatolariga olib keladi.

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.
KPI TA’RIFLARI

OmborPro ko‘rsatkichlari (kelishuv)

Savdo (jami)Davr ichida issued_at bo‘yicha berilgan hisob-fakturalar total yig‘indisi (QQS bilan).
Sof savdo va QQSsubtotal va vat_amount yig‘indilari; QQS stavkasi 12 %.
O‘rtacha chekJami savdo / hisob-fakturalar soni (butun so‘mgacha).
Debitorlikstatus ≠ paid hisob-fakturalar bo‘yicha total − paid_amount yig‘indisi.
Muddati o‘tgan qarzDebitorlikning due_at < bugun qismi; 1–30, 31–60, 61–90, 90+ kun guruhlari.
Kam qoldiqquantity − 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.
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
O‘zingizni tekshiring
Nima uchun hisobotlar uchun ko‘rsatkich ta’rifi oldindan yozib qo‘yilishi kerak?

Aks holda turli ekranlar (dashboard, Excel, PDF) “savdo” ni turlicha hisoblab, turli raqam ko‘rsatadi.

OLTP so‘rovlari va hisobot so‘rovlarining farqi nimada?

OLTP — kichik, ko‘p, yozuvchi so‘rovlar; hisobot — ko‘p qatorni o‘qib guruhlaydigan og‘ir so‘rovlar.

Nima uchun savdo summasini PHP foreach da emas, bazada hisoblaymiz?

Baza indekslar bilan tez hisoblaydi va bir necha qator qaytaradi; PHP da minglab modellar xotira va vaqtni yeydi.

2. SQL agregatsiya so‘rovlari

SUM, COUNT, AVG · GROUP BY va HAVING · date_trunc · FILTER · oyna funksiyalari · NULL tuzoqlari

Agregat funksiyalar ko‘p qatordan bitta qiymat hisoblaydi: COUNT(*) — qatorlar soni, COUNT(col) — NULL bo‘lmagan qiymatlar soni, SUM, AVG, MIN, MAX. GROUP BY qatorlarni guruhlarga ajratadi va agregat har bir guruh uchun hisoblanadi: oylar, mijozlar, omborlar bo‘yicha. SELECT dagi agregat bo‘lmagan har bir ustun GROUP BY da bo‘lishi shart (PostgreSQL bu qoidani qat’iy tekshiradi). WHERE guruhlashdan oldin qatorlarni filtrlaydi, HAVING esa keyin guruhlarni: “oylik xaridi 10 mln so‘mdan ko‘p mijozlar” — HAVING SUM(total) > 10000000.

Davr bo‘yicha guruhlash uchun PostgreSQL da date_trunc('month', issued_at) — sanani oy boshiga “kesadi”; to_char(issued_at, 'YYYY-MM') — matn ko‘rinishi. Davr chegarasini yarim ochiq oraliq bilan yozing: issued_at >= '2026-10-01' AND issued_at < '2026-11-01' — BETWEEN '2026-10-01' AND '2026-10-31' vaqt qismi bo‘lgan ustunda 31-oktabr 00:00 dan keyingi yozuvlarni yo‘qotadi. Agar ustun timestamp with time zone bo‘lsa, guruhlashdan oldin AT TIME ZONE 'Asia/Tashkent' bilan mahalliy vaqtga o‘tkazing.

PostgreSQL ning qulay imkoniyati — FILTER: SUM(total) FILTER (WHERE status = 'paid') bitta so‘rovda bir nechta shartli yig‘indini beradi (boshqa bazalarda SUM(CASE WHEN ... THEN total ELSE 0 END)). NULL tuzoqlari: bo‘sh to‘plamning SUM i 0 emas, NULL — COALESCE(SUM(total), 0); AVG NULL larni hisobga olmaydi; COUNT(*) va COUNT(paid_at) turli natija beradi. Butun sonlarni bo‘lishda 7 / 2 = 3 — o‘rtacha uchun numeric ga keltiring.

Oyna funksiyalari (window functions) guruhlamasdan, har bir qator uchun “qo‘shni” qatorlar bo‘yicha hisoblaydi: SUM(jami) OVER (ORDER BY oy) — yil boshidan yig‘ma summa, LAG(jami) OVER (ORDER BY oy) — oldingi oy qiymati (o‘sish foizi uchun), RANK() OVER (ORDER BY SUM(oi.total) DESC) — mahsulotlar reytingi, PARTITION BY warehouse_id — har bir ombor ichida alohida. Ular hisobotdagi “o‘tgan oyga nisbatan +12 %” kabi ustunlarni PHP siz beradi.

Hisobot so‘rovlari tez ishlashi uchun indekslar kerak: invoices(issued_at), invoices(status, due_at), order_items(product_id), stock_movements(product_id, created_at). EXPLAIN ANALYZE bilan so‘rov rejasini tekshirish va Seq Scan ni topish 13-mavzuda batafsil ko‘riladi; hozir esa qoida: filtr va guruhlash ustunlari indekslansin.

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

SQL natijasini oldindan ayting

Agregatsiya so‘rovlari haqidagi savollar.

Oktabrda birorta ham hisob-faktura yo‘q. Natija nima?
SELECT SUM(total) FROM invoices
WHERE issued_at >= '2026-10-01' AND issued_at < '2026-11-01';

✓ NULL — Bo‘sh to‘plamning SUM i NULL; 0 kerak bo‘lsa — COALESCE(SUM(total), 0).

“Oylik xaridi 10 mln dan katta mijozlar” sharti qayerga yoziladi?
  1. WHERE
  2. ORDER BY
  3. HAVING
  4. GROUP BY

✓ HAVING — Shart agregat (SUM) ustida — guruhlashdan keyin, HAVING da.

Har bir oy uchun yil boshidan yig‘ma summani qaysi ifoda beradi?
  1. SUM(jami) OVER (ORDER BY oy)
  2. SUM(jami) GROUP BY oy
  3. LAG(jami)
  4. COUNT(*) OVER ()

✓ SUM(jami) OVER (ORDER BY oy) — Tartiblangan oyna bo‘yicha SUM — joriy qatorgacha bo‘lgan barcha qatorlar yig‘indisi.

COUNT(*) va COUNT(paid_at) qachon farq qiladi?
  1. Hech qachon
  2. Jadval bo‘sh bo‘lsa
  3. paid_at da NULL lar bo‘lsa
  4. Indeks bo‘lmasa

✓ `paid_at` da NULL lar bo‘lsa — COUNT(ustun) NULL qiymatlarni sanamaydi.

O‘zingizni tekshiring
WHERE va HAVING ning farqi nimada?

WHERE qatorlarni guruhlashdan oldin, HAVING guruhlarni agregatdan keyin filtrlaydi.

Nima uchun davr filtri BETWEEN emas, >= ... AND < ... ko‘rinishida yoziladi?

Vaqt qismi bor ustunda BETWEEN ... '2026-10-31' 31-oktabrning 00:00 dan keyingi yozuvlarini tashlab ketadi.

FILTER (WHERE ...) qanday vazifani bajaradi?

Agregatni faqat shartga mos qatorlar bo‘yicha hisoblaydi — bitta so‘rovda bir nechta shartli yig‘indi.

3. Eloquent va Query Builder bilan agregatsiya

sum, count, avg · withSum va withCount · selectRaw va groupBy · ReportService

Eloquent va Query Builder agregatlarni to‘g‘ridan-to‘g‘ri bazada bajaradi: Invoice::whereBetween(...)->sum('total'), ->count(), ->avg('total'), ->max('total') — bitta SELECT SUM(...) so‘rovi, modellar yaratilmaydi. Kolleksiya metodlari (Invoice::all()->sum('total')) esa avval hamma yozuvni yuklaydi — kichik ro‘yxatlardan boshqa joyda ishlatmang. Pul uchun natija satr ("1349040000.00") bo‘lib kelishi mumkin — uni float ga emas, decimal ko‘rinishida saqlang yoki tiyinga o‘giring.

Bog‘liq modellar bo‘yicha agregat: Counterparty::withCount('invoices') har bir kontragentga invoices_count, withSum('invoices', 'total') — invoices_sum_total atributini qo‘shadi; bular subquery bilan bitta so‘rovda bajariladi (N+1 yo‘q). Shartli va nomlangan variant: withSum(['invoices as unpaid_total' => fn ($q) => $q->where('status', '!=', 'paid')], 'total'). Shuningdek withAvg, withMax, withMin, withExists, allaqachon yuklangan modellar uchun loadSum(). Natija bo‘yicha saralash ham mumkin: ->orderByDesc('invoices_sum_total').

Murakkab hisobotlar uchun Query Builder: DB::table('invoices')->join(...)->selectRaw('...')->groupBy(...)->havingRaw(...). selectRaw ga foydalanuvchi qiymatini faqat bog‘lash (binding) orqali bering: selectRaw('SUM(total) FILTER (WHERE due_at < ?) AS overdue', [$sana]) — matnni ulash ("... '$sana'") SQL injection ga yo‘l ochadi. Ustun nomlari (saralash, guruhlash) esa bog‘lab bo‘lmaydi — ular oq ro‘yxatdan tanlanadi. So‘rovni tekshirish uchun ->toRawSql() (bog‘langan qiymatlar bilan SQL matni) yoki ->dd() qulay.

Barcha hisobot so‘rovlari `ReportService` da: stockBalances(?int $warehouseId), salesByPeriod(CarbonImmutable $from, CarbonImmutable $to, string $groupBy), topProducts(...), receivables(CarbonImmutable $asOf). Servis massiv yoki DTO qaytaradi, Blade/PDF/Excel/API faqat ko‘rsatadi. Bu bitta ta’rif tamoyilini ta’minlaydi va testlashni osonlashtiradi: 12-mavzuda servisni factory ma’lumotlari bilan tekshiramiz (“3 ta hisob-faktura → savdo = ...”). Og‘ir hisobotlarni keshlash (13-mavzu) va fonda tayyorlash (10-mavzu) ham shu servis ustiga quriladi.

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)
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
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
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
O‘zingizni tekshiring
Invoice::where(...)->sum('total') va Invoice::all()->sum('total') ning farqi nimada?

Birinchisi bazada bitta SELECT SUM bajaradi; ikkinchisi hamma yozuvlarni modellarga yuklab, PHP da qo‘shadi.

withSum('invoices', 'total') natijasi qaysi atributda bo‘ladi va nechta so‘rov bajariladi?

invoices_sum_total; asosiy so‘rov ichida subquery — jami bitta so‘rov.

selectRaw ga sana qiymatini qanday xavfsiz beramiz?

Bog‘lash orqali: selectRaw('... ?', [$sana]); matnni ulash SQL injection ga olib keladi.

4. Materiallashtirilgan ko‘rinishlar (PostgreSQL)

VIEW va MATERIALIZED VIEW · migratsiyada yaratish · REFRESH CONCURRENTLY · Eloquent modeli

Ko‘rinish (VIEW) — saqlangan SELECT so‘rovi: CREATE VIEW v_stock_available AS SELECT .... U ma’lumot saqlamaydi, har safar murojaatda so‘rov qayta bajariladi; foydasi — murakkab JOIN larni bir joyda yozish va hisobot kodini soddalashtirish. Tezlik bo‘yicha oddiy so‘rovdan farqi yo‘q. Materiallashtirilgan ko‘rinish (MATERIALIZED VIEW) esa so‘rov natijasini jadval kabi diskda saqlaydi: o‘qish juda tez, unga indeks qurish mumkin, lekin ma’lumot REFRESH MATERIALIZED VIEW buyrug‘igacha eskirgan holda qoladi.

Qachon kerak: hisobot og‘ir (millionlab order_items ustida guruhlash), tez-tez ochiladi (dashboard), lekin daqiqama-daqiqa aniqlik shart emas — “kecha holatiga” yoki “soat boshiga” yetarli. Misol: mv_sales_monthly — oy va mijoz bo‘yicha savdo yig‘masi. Dashboard 3 yillik trendni millisekundlarda chizadi, chunki 36 oy × bir necha yuz mijoz qatorini o‘qiydi, hisob-fakturalarning hammasini emas.

Yaratish — migratsiyada DB::statement('CREATE MATERIALIZED VIEW ...'), down() da DROP MATERIALIZED VIEW. Yangilash: REFRESH MATERIALIZED VIEW mv_sales_monthly ko‘rinishni qayta hisoblaydi, lekin shu vaqtda uni o‘qishni bloklaydi; REFRESH MATERIALIZED VIEW CONCURRENTLY o‘qishni bloklamaydi, ammo buning uchun ko‘rinishda UNIQUE indeks bo‘lishi shart (masalan, (month, counterparty_id)) va ko‘rinish avval kamida bir marta to‘ldirilgan bo‘lishi kerak. Yangilashni artisan buyrug‘i bajaradi; uni har kecha yoki import tugaganda ishga tushirishni 10-mavzudagi rejalashtirish (scheduler) bilan avtomatlashtiramiz.

Laravel tomonda materiallashtirilgan ko‘rinish oddiy modelga ulanadi: protected $table = 'mv_sales_monthly', public $timestamps = false, casts. Model faqat o‘qish uchun — unga create() yoki update() qilib bo‘lmaydi (PostgreSQL ruxsat bermaydi). Muqobillar: yig‘ma jadval (summary table) — har bir hisob-faktura yaratilganda servis/hodisa orqali yangilanadi (doim aniq, lekin kod murakkabroq); kesh (13-mavzu) — tayyor natija Redis da saqlanadi. Hisobot ekranida “Ma’lumotlar 27.10.2026 03:00 holatiga” degan yozuv bo‘lsin — foydalanuvchi eskirgan raqamni yangi deb o‘ylamasin.

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”.
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
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
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.
O‘zingizni tekshiring
VIEW va MATERIALIZED VIEW ning asosiy farqi nimada?

VIEW natijani saqlamaydi (har safar hisoblanadi); MATERIALIZED VIEW natijani saqlaydi va REFRESH gacha eskiradi.

REFRESH MATERIALIZED VIEW CONCURRENTLY uchun qanday shart bor?

Ko‘rinishda UNIQUE indeks bo‘lishi va u avval to‘ldirilgan bo‘lishi kerak.

Materiallashtirilgan ko‘rinishga ulangan Eloquent modelida nimalar o‘ziga xos?

$table ko‘rinish nomi, $timestamps = false, faqat o‘qish — create/update qilinmaydi.

5. PDF va Excel hisobotlari, dashboard

laravel-dompdf · DejaVu Sans · ko‘p varaqli Excel · Chart.js va @json · dashboard KPI

PDF — rasmiy, chop etiladigan hisobot: qoldiqlar dalolatnomasi, debitorlik ro‘yxati, hisob-faktura. barryvdh/laravel-dompdf Blade shablonidan PDF yasaydi: Pdf::loadView('reports.pdf.stock', $data)->setPaper('a4', 'landscape'), so‘ng ->download('qoldiq.pdf') (yuklab olish) yoki ->stream() (brauzerda ochish). dompdf — HTML/CSS dvigateli, lekin brauzer emas: CSS 2.1 darajasida, flexbox va grid ishlamaydi; maket jadvallar bilan quriladi. Uzun jadvallarda tr { page-break-inside: avoid; }, sahifa sarlavhasi uchun position: fixed.

O‘zbek matni uchun shrift muhim: dompdf ning standart shriftlari (Helvetica, Times) faqat lotin-1 belgilarini o‘z ichiga oladi, o‘, g‘ dagi ‘ belgisi va kirill harflari “?” bo‘lib chiqishi mumkin. dompdf bilan birga keladigan DejaVu Sans Unicode ni qo‘llaydi: body { font-family: 'DejaVu Sans', sans-serif; }. Raqamlarni hisobotda o‘qiladigan ko‘rinishga keltiring: number_format($x, 2, ',', ' ') → 1 349 040 000,00, sanalar d.m.Y, vaqt zonasi Asia/Tashkent. PDF yasash sekin (katta jadvalda soniyalar) — 1000 qatordan katta PDF larni fonda tayyorlash maqsadga muvofiq (10-mavzu).

Excel hisobot — tahlil uchun: foydalanuvchi filtrlaydi, pivot quradi. maatwebsite/excel da ko‘p varaqli fayl WithMultipleSheets (sheets(): array) bilan; har bir varaq FromQuery yoki FromCollection, WithTitle (varaq nomi), WithHeadings, WithColumnFormatting (NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 — #,##0.00) bilan. Pulni Excel ga son sifatida yozing, matn sifatida emas — aks holda foydalanuvchi uni qo‘sha olmaydi. Varaqlar: “Oylar bo‘yicha”, “Top mahsulotlar”, “Debitorlik”.

Dashboard — rahbar uchun bir ekran: yuqorida KPI kartochkalari (oy savdosi, o‘tgan oyga nisbatan o‘zgarish, debitorlik, muddati o‘tgan qarz, kam qoldiqli mahsulotlar soni), pastda diagrammalar (12 oylik savdo trendi, to‘lov usullari ulushi). Diagramma uchun Chart.js: Blade da <canvas> va @json($chart['labels']) direktivasi bilan ma’lumot JavaScript ga xavfsiz uzatiladi. Ma’lumotni alohida JSON endpoint (/reports/sales-chart?from=...) dan olish ham mumkin — u API ga ham yaraydi. Livewire da wire:poll.60s bilan KPI lar har daqiqada yangilanadi. Bo‘sh oylar ham diagrammada 0 bo‘lib ko‘rinishi kerak — SQL natijasidagi “teshik” larni to‘ldirish servis vazifasi.

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
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
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
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
O‘zingizni tekshiring
dompdf da o‘zbek harflari to‘g‘ri chiqishi uchun nima qilinadi?

Unicode qo‘llaydigan shrift: font-family: 'DejaVu Sans' (dompdf bilan keladi).

Excel hisobotda pul summasini nima uchun matn emas, son sifatida yozamiz?

Foydalanuvchi Excel da qo‘shish, saralash, pivot qila olishi uchun; format WithColumnFormatting bilan beriladi.

Blade da PHP massivini Chart.js ga qanday xavfsiz uzatamiz?

@json($chart['labels']) direktivasi bilan — JSON va HTML uchun to‘g‘ri ekranlangan.

Asosiy xulosalar

Takrorlash savollari

COUNT(*) va COUNT(col) farqi?

COUNT(col) NULL qiymatlarni sanamaydi.

Bo‘sh to‘plamning SUM i nima?

NULL; 0 kerak bo‘lsa COALESCE(SUM(x), 0).

Oylar bo‘yicha guruhlash PostgreSQL da?

date_trunc('month', issued_at) yoki to_char(issued_at, 'YYYY-MM').

withSum('invoices', 'total') atributi?

invoices_sum_total.

Yig‘ma summa uchun SQL?

SUM(jami) OVER (ORDER BY oy).

MATERIALIZED VIEW qachon yangilanadi?

REFRESH MATERIALIZED VIEW bajarilganda — o‘z-o‘zidan emas.

dompdf da qaysi shrift o‘zbek harflari uchun?

DejaVu Sans.

Blade dan Chart.js ga ma’lumot?

@json($massiv) direktivasi.

Chiqish chiptasi

  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?