9-ma’ruza matni · Korxona ilovalarini dasturlash
Hisobot va KPI · SQL agregatsiya · Eloquent aggregate · materiallashtirilgan ko‘rinishlar · PDF, Excel, dashboard
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?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.
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 hisobotlariniKim 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.
| 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.issued_at bo‘yicha berilgan hisob-fakturalar total yig‘indisi (QQS bilan).subtotal va vat_amount yig‘indilari; QQS stavkasi 12 %.status ≠ paid hisob-fakturalar bo‘yicha total − paid_amount yig‘indisi.due_at < bugun qismi; 1–30, 31–60, 61–90, 90+ kun guruhlari.quantity − reserved < min_stock bo‘lgan mahsulot–ombor juftliklari soni.$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 uchunintdiv() — o‘rtacha chek butun so‘mgacha — float yo‘qnumber_format($x, 0, ',', ' ') — o‘zbekcha ko‘rinish: minglik ajratuvchi — bo‘sh joyAks holda turli ekranlar (dashboard, Excel, PDF) “savdo” ni turlicha hisoblab, turli raqam ko‘rsatadi.
OLTP — kichik, ko‘p, yozuvchi so‘rovlar; hisobot — ko‘p qatorni o‘qib guruhlaydigan og‘ir so‘rovlar.
foreach da emas, bazada hisoblaymiz?Baza indekslar bilan tez hisoblaydi va bir necha qator qaytaradi; PHP da minglab modellar xotira va vaqtni yeydi.
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.
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
date_trunc('month', ...) — sanani oy boshiga keltiradi — guruh kaliti>= ... AND < ... — yarim ochiq oraliq: oy oxiridagi vaqtli yozuvlar yo‘qolmaydiSUM(subtotal), SUM(vat_amount) — QQS har bir hujjatdagi qiymatdan yig‘iladiSELECT 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
FILTER (WHERE ...) — bitta o‘tishda bir nechta shartli yig‘indiCOALESCE(..., 0) — mos qator bo‘lmasa SUM NULL beradi — 0 ga almashtiramiz$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
$byMonth[$m] ??= ... — guruh birinchi uchraganda yaratiladiksort() — kalitlar YYYY-MM — matn tartibi xronologik tartib bilan bir xil$run — oyna funksiyasining yig‘ma summasi (running total)Agregatsiya so‘rovlari haqidagi savollar.
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).
✓ HAVING — Shart agregat (SUM) ustida — guruhlashdan keyin, HAVING da.
✓ 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?paid_at da NULL lar bo‘lsa✓ `paid_at` da NULL lar bo‘lsa — COUNT(ustun) NULL qiymatlarni sanamaydi.
WHERE va HAVING ning farqi nimada?WHERE qatorlarni guruhlashdan oldin, HAVING guruhlarni agregatdan keyin filtrlaydi.
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.
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.
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"}, ...]match ($group) — SQL ga qo‘yiladigan qism faqat oq ro‘yxatdan — injection yo‘q$to->addDay()->startOfDay() — yarim ochiq oraliq: to kuni to‘liq kiradigroupBy('period') — PostgreSQL GROUP BY da SELECT taxallusini qabul qiladistdClass qatorlar; summalar satr (numeric)$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(...)) ...)
withCount('invoices') — invoices_count atributi — subquerywithSum('invoices', 'total') — invoices_sum_total — nom avtomatik'invoices as paid_total' => fn — shartli agregat va o‘z nominullpublic 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
quantity - reserved — mavjud qoldiq — spetsifikatsiyadagi invariantwhereNull('p.deleted_at') — Query Builder soft delete ni o‘zi hisobga olmaydiInvoice::all()->sum('total') — hamma qator xotiradaforeach ($customers as $c) $c->invoices->sum(...) — N+1(float) $row->total bilan yig‘ish — yaxlitlash xatosiselectRaw("... '$sana'") — SQL injectionInvoice::where(...)->sum('total') — bitta SELECT SUMwithSum('invoices', 'total') — subquery, bitta so‘rovnumeric, PHP da satr yoki tiyinselectRaw('... ?', [$sana]) — bog‘langan qiymatInvoice::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.
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 — so‘rovning nomi; MATERIALIZED VIEW — so‘rov natijasining nusxasi. Birinchisi doim yangi, ikkinchisi doim tez.
SELECT bajariladi. Murakkab JOIN larni yashirish uchun.REFRESH gacha eskiradi.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
<<<'SQL' ... SQL — nowdoc — ko‘p qatorli SQL, $ almashtirilmaydiREFRESH ... CONCURRENTLY uchun shartclass 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 ms$table = 'mv_sales_monthly' — model ko‘rinishga ulanadi — faqat o‘qish$timestamps = false — ko‘rinishda created_at/updated_at yo‘qreports:refresh — import yoki tunda ishga tushadi (rejalashtirish — 10-mavzu)| 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 |
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.
$table ko‘rinish nomi, $timestamps = false, faqat o‘qish — create/update qilinmaydi.
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.
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::loadView() — Blade shablon → HTML → PDFsetPaper('a4', 'landscape') — keng jadval uchun albom yo‘nalishidownload() / stream() — yuklab olish yoki brauzerda ochish$reports->stockBalances() — ekran, PDF va Excel — bitta servis metodi<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 chiqadiborder-collapse — dompdf CSS 2.1 ni qo‘llaydi — maket jadval bilanpage-break-inside: avoid — qator ikki sahifaga bo‘linmaydi@money(...) — Blade::directive('money', ...) — number_format($x, 2, ',', ' ') bitta joydaclass 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 sifatida$from, $to — barcha varaqlar bir xil davr uchun — ziddiyat yo‘q<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 — 0@json(...) — PHP massivi → JSON, HTML uchun xavfsiz ekranlangan$chart['labels'] — barcha oylar, ma’lumotsizlari ham — ReportService to‘ldiradiwire:poll.60s — Livewire bilan KPI va diagrammani davriy yangilashUnicode qo‘llaydigan shrift: font-family: 'DejaVu Sans' (dompdf bilan keladi).
Foydalanuvchi Excel da qo‘shish, saralash, pivot qila olishi uchun; format WithColumnFormatting bilan beriladi.
@json($chart['labels']) direktivasi bilan — JSON va HTML uchun to‘g‘ri ekranlangan.
ReportService; ekran, PDF, Excel bir xil raqam beradi.SUM, COUNT, GROUP BY, HAVING, FILTER, date_trunc; davr — yarim ochiq oraliq.SUM() OVER, LAG, RANK — yig‘ma summa, o‘sish, reyting PHP siz.sum(), withCount, withSum — bitta so‘rov; selectRaw faqat bog‘langan qiymatlar bilan.REFRESH ... CONCURRENTLY uchun UNIQUE indeks.@json.COUNT(*) va COUNT(col) farqi?COUNT(col) NULL qiymatlarni sanamaydi.
SUM i nima?NULL; 0 kerak bo‘lsa COALESCE(SUM(x), 0).
date_trunc('month', issued_at) yoki to_char(issued_at, 'YYYY-MM').
withSum('invoices', 'total') atributi?invoices_sum_total.
SUM(jami) OVER (ORDER BY oy).
REFRESH MATERIALIZED VIEW bajarilganda — o‘z-o‘zidan emas.
DejaVu Sans.
@json($massiv) direktivasi.
SELECT, WHERE, GROUP BY qismlarini yozing.