A9

9-amaliy mashg‘ulot · 9-mavzu · 2 soat · Korxona ilovalarini dasturlash

Hisobotlar: qoldiq, savdo, debitorlik

Jamoa loyihasi (OmborPro) bosqichi, 5 ta amaliy PHP topshiriq va 3 ta ma’ruzadagi mashq. Yo‘riqnoma bo‘yicha loyihada ishlang, havolani topshiring va PHP topshiriqlarni yeching — server yashirin testlar bilan tekshiradi, o‘qituvchi baho qo‘yadi.

Mashg‘ulot maqsadi

  • Yaratish — ReportService: ombor qoldiqlari, davr bo‘yicha savdo, debitorlik qarzi va uning muddati
  • Qo‘llash — SQL agregatlar, FILTER, date_trunc va Eloquent withSum/withCount ni
  • Eksport qilish — hisobotlarni Excel (ko‘p varaq) va PDF (DejaVu Sans) ko‘rinishida
  • Chizish — dashboard: KPI kartochkalari va 12 oylik savdo diagrammasi (Chart.js)
  • Tezlashtirish — oylik savdo uchun materiallashtirilgan ko‘rinish va uni yangilash buyrug‘ini

1. 1-qadam. ReportService va hisobot ekranlari

Uchta hisobot · filtrlar uchun Form Request · ruxsatlar · indekslar

app/Services/ReportService.php ni yarating va uchta metod yozing: stockBalances(?int $warehouseId), salesByPeriod($from, $to, $groupBy) va receivables(CarbonImmutable $asOf). Har bir metod DB::table()/Eloquent agregatlari bilan bitta (ko‘pi bilan ikki) so‘rov bajarsin. Ko‘rsatkich ta’riflarini (ma’ruzadagi KPI kartochkalari) docs/reports.md ga yozing — jamoa va o‘qituvchi bir xil tushunchada bo‘lsin.

Filtrlar uchun ReportFilterRequest: from va to — date, to — after_or_equal:from, davr 366 kundan oshmasin (after() da); warehouse_id — nullable|exists:warehouses,id; group — in:day,week,month. Standart qiymatlar: joriy oy boshidan bugungacha (Asia/Tashkent). Filtr qiymatlari URL query da qoladi — hisobot havolasini hamkasbga yuborish mumkin bo‘lsin.

Ruxsatlar: qoldiq hisoboti — omborchi, menejer, admin; savdo — menejer, buxgalter, admin; debitorlik — buxgalter, admin. 5-mavzudagi spatie/laravel-permission ruxsatlarini qo‘shing (reports.stock, reports.sales, reports.receivables) va marshrutlarni can: middleware bilan himoyalang. Menyuda faqat ruxsat bor hisobotlar ko‘rinsin.

Unumdorlik uchun indekslar migratsiyasi: invoices(issued_at), invoices(status, due_at), order_items(product_id). Seeder bilan kamida 12 oylik test ma’lumot yarating (factory lar bilan bir necha ming hisob-faktura) — hisobotlar bo‘sh jadvalda emas, real hajmga yaqin ma’lumotda sinalsin.

DEBITORLIK

receivables(): qarz va muddat guruhlari

public function receivables(CarbonImmutable $asOf): Collection
{
    $d = $asOf->toDateString();
    $sum = 'SUM(i.total - i.paid_amount)';
    $f = fn (string $cond) =>
        "$sum FILTER (WHERE ?::date - i.due_at::date $cond)";
    return DB::table('invoices as i')
        ->join('counterparties as c', 'c.id', '=', 'i.counterparty_id')
        ->whereIn('i.status', ['unpaid', 'partial'])
        ->select('c.id', 'c.name', 'c.inn')
        ->selectRaw("$sum AS total_debt")
        ->selectRaw($f('<= 0') . ' AS not_due', [$d])
        ->selectRaw($f('BETWEEN 1 AND 30') . ' AS d1_30', [$d])
        ->selectRaw($f('> 30') . ' AS d30_plus', [$d])
        ->groupBy('c.id', 'c.name', 'c.inn')
        ->orderByDesc('total_debt')
        ->get();
}
▶ Natija
 name                | inn       | total_debt | not_due    | d1_30      | d30_plus
 “Baraka Savdo” MChJ | 305123456 | 64800000   | 40000000   | 24800000   |
 “Sharq Ulgurji” MChJ| 301111222 | 12320000   |            |            | 12320000
  • $f = fn (string $cond) — kun guruhi shartini yasovchi yordamchi; ichida faqat kod konstantalari
  • ?::date - i.due_at::date — PostgreSQL da sana ayirmasi — kunlar soni
  • [$d] binding — sana faqat bog‘langan qiymat sifatida
  • NULL kataklar — Blade da ?? 0 yoki SQL da COALESCE
SPETSIFIKATSIYA

Uchta hisobot: ustunlar va filtrlar

HisobotUstunlarFiltrlarRollar
Qoldiqlarombor, SKU, nomi, quantity, reserved, mavjud, qiymati, kam?ombor, faqat kam qoldiqomborchi, menejer
Savdodavr, hujjatlar soni, sof savdo, QQS, jamifrom, to, group (kun/hafta/oy)menejer, buxgalter
Top mahsulotlarSKU, nomi, miqdor, summa, o‘rinfrom, to, limitmenejer
Debitorlikmijoz, STIR, qarz, joriy, 1–30, 31+ kunsana (asOf)buxgalter
Har bir jadval ostida “Jami” qatori va “Ma’lumotlar ... holatiga” yozuvi bo‘lsin.
MARSHRUTLAR

Hisobot marshrutlari va ruxsatlar

Route::middleware('auth')->prefix('reports')->name('reports.')
    ->controller(ReportController::class)->group(function () {
        Route::get('stock', 'stock')->name('stock')
            ->middleware('can:reports.stock');
        Route::get('sales', 'sales')->name('sales')
            ->middleware('can:reports.sales');
        Route::get('receivables', 'receivables')->name('receivables')
            ->middleware('can:reports.receivables');
        Route::get('{report}.{format}', 'export')->name('export')
            ->whereIn('format', ['xlsx', 'pdf']);
    });
▶ Natija
GET /reports/sales?from=2026-10-01&to=2026-10-27&group=day → 200
GET /reports/receivables (omborchi) → 403
GET /reports/sales.xlsx?from=... → savdo-2026-10-01_2026-10-27.xlsx
  • can:reports.sales — spatie ruxsati Gate orqali tekshiriladi
  • controller(ReportController::class) — guruhdagi marshrutlar metod nomi bilan yoziladi
  • {report}.{format} — bitta export metodi — xlsx yoki pdf
  • whereIn('format', ...) — marshrut parametri cheklovi — boshqa format 404

2. 2-qadam. Excel va PDF eksport

Ko‘p varaqli Excel · ustun formatlari · dompdf shablonlari · fayl nomlari

composer require barryvdh/laravel-dompdf. Har bir hisobot uchun PDF shablonini resources/views/reports/pdf/ da yarating: umumiy layout.blade.php (shrift DejaVu Sans, jadval stillari, sarlavhada korxona nomi, hisobot nomi, davr, chop etilgan vaqt va foydalanuvchi) va hisobotlarning o‘z shablonlari. Raqamlar o‘ngga tekislangan, number_format(..., 2, ',', ' '), oxirida “Jami” qatori.

Excel: SalesReportExport (WithMultipleSheets) — “Oylar bo‘yicha”, “Top mahsulotlar”, “Debitorlik” varaqlari. Har bir varaq sinfi ReportService dan ma’lumot oladi (FromCollection yoki FromArray), WithHeadings, WithTitle, WithColumnFormatting (pul ustunlari #,##0.00, sanalar dd.mm.yyyy) va ShouldAutoSize ni amalga oshiradi. Pul ustunlariga son yozing, matn emas.

Fayl nomlari ma’noli bo‘lsin: savdo-2026-10-01_2026-10-27.xlsx, debitorlik-2026-10-27.pdf. Ekran, Excel va PDF dagi “Jami” qiymatlari bir xil ekanini qo‘lda tekshiring — farq bo‘lsa, demak qayerdadir servis chetlab o‘tilgan.

Katta hajmni sinang: seeder bilan 5 000 qatorli qoldiq yarating va PDF hamda Excel eksportlari qancha vaqt va xotira olishini o‘lchang (microtime(true) va memory_get_peak_usage(true) qiymatlarini logga yozish yetarli). Natijalarni README ga kiriting. Agar PDF 10 soniyadan uzoq tayyorlansa, foydalanuvchi so‘rovi ichida bunday kutish qabul qilinmaydi — uni keyingi mavzuda navbatga (queue) o‘tkazish uchun backlogga vazifa yozing.

EXCEL VARAQ

Debitorlik varag‘i

class ReceivablesSheet implements FromCollection, WithHeadings,
    WithTitle, ShouldAutoSize
{
    public function __construct(private CarbonImmutable $asOf) {}
    public function collection()
    {
        return app(ReportService::class)->receivables($this->asOf)
            ->map(fn ($r) => [$r->name, $r->inn, (float) $r->total_debt,
                (float) $r->not_due, (float) $r->d1_30,
                (float) $r->d30_plus]);
    }
    public function headings(): array
    {
        return ['Mijoz', 'STIR', 'Qarz', 'Joriy', '1–30', '30+'];
    }
    public function title(): string { return 'Debitorlik'; }
}
▶ Natija
[Debitorlik]
Mijoz               | STIR      | Qarz          | Joriy         | 1–30 kun      | 30+ kun
“Baraka Savdo” MChJ | 305123456 | 64 800 000,00 | 40 000 000,00 | 24 800 000,00 | 0,00
  • (float) Excel uchun — Excel o‘zi son sifatida saqlaydi; hisoblash emas, faqat yozish
  • WithColumnFormatting — qo‘shing: columnFormats() → ['C:F' => '#,##0.00'] — Excel mintaqasiga qarab ko‘rinadi
  • WithTitle — varaq nomi
  • ShouldAutoSize — ustun kengligi avtomatik
TEKSHIRUV

Eksport sifatini tekshirish

  1. Raqamlar mosligi — ekran, Excel va PDF dagi “Jami” bir xil
  2. Harflar — PDF da o‘, g‘, ’ to‘g‘ri; CSV/Excel da ham
  3. Formatlar — Excel da pul — son (qo‘shib bo‘ladi), sana — sana
  4. Ruxsat — omborchi /reports/receivables.pdf ni ocha olmaydi (403)
  5. Hajm — 5 000 qatorli PDF vaqti o‘lchandi va README ga yozildi

3. 3-qadam. Dashboard va materiallashtirilgan ko‘rinish

KPI kartochkalari · 12 oylik diagramma · bo‘sh oylarni to‘ldirish · mv_sales_monthly

Dashboard sahifasi (/dashboard): to‘rtta KPI kartochkasi — joriy oy savdosi va o‘tgan oyga nisbatan o‘zgarish foizi, jami debitorlik, muddati o‘tgan qarz, kam qoldiqli mahsulotlar soni. Har bir kartochka bosilganda tegishli batafsil hisobotga o‘tadi. KPI lar Livewire komponentida bo‘lsa, wire:poll.60s bilan avtomatik yangilanadi.

Diagramma: oxirgi 12 oy savdosi (ustunli) Chart.js bilan. Ma’lumot mv_sales_monthly dan olinadi; SQL natijasida savdosi bo‘lmagan oylar yo‘q — ularni servisda 0 bilan to‘ldiring, aks holda diagramma oylarni “siqib” yuboradi. Qiymatlarni millionlarga bo‘lib, bir xonali kasr bilan bering (1692.3 mln so‘m) — o‘qish oson.

Materiallashtirilgan ko‘rinish: ma’ruzadagi migratsiya (mv_sales_monthly + UNIQUE indeks) va reports:refresh buyrug‘i. Import tugaganda va hisob-faktura ommaviy o‘zgarganda buyruqni chaqiring; dashboardda “Ma’lumotlar ... holatiga” vaqtini ko‘rsating (oxirgi yangilash vaqtini keshga yoki alohida jadvalga yozib qo‘yish mumkin).

Sinov: yangi hisob-faktura qo‘shing → dashboard o‘zgarmaydi (ko‘rinish eskirgan) → php artisan reports:refresh → diagramma yangilanadi. Shu xatti-harakatni README da tushuntiring — foydalanuvchi uchun bu kutilgan holat ekanini bilish muhim.

SERVIS

Diagramma ma’lumoti: bo‘sh oylarni to‘ldirish

public function monthlyChart(CarbonImmutable $to, int $n = 12): array
{
    $from = $to->startOfMonth()->subMonths($n - 1);
    $sums = SalesMonthly::query()
        ->where('month', '>=', $from->toDateString())
        ->where('month', '<=', $to->toDateString())
        ->selectRaw("to_char(month, 'YYYY-MM') AS ym")
        ->selectRaw('SUM(total) AS total')
        ->groupBy('ym')
        ->pluck('total', 'ym');        // ['2026-10' => '1692320000.00']
    $labels = $data = [];
    for ($m = $from; $m <= $to; $m = $m->addMonth()) {
        $key = $m->format('Y-m');
        $labels[] = $key;
        $data[] = round(((float) ($sums[$key] ?? 0)) / 1e6, 1);
    }
    return ['labels' => $labels, 'data' => $data];
}
▶ Natija
['labels' => ['2025-11', '2025-12', ..., '2026-10'],
 'data'   => [1104.2, 0, 1210.4, ..., 1692.3]]   // dekabr — savdo yo‘q
  • subMonths($n - 1) — joriy oy bilan birga 12 ta oy
  • pluck('total', 'ym') — oy => summa lug‘ati
  • ?? 0 — savdosi yo‘q oy — 0, diagrammada bo‘shliq emas
  • / 1e6, round(..., 1) — faqat ko‘rsatish uchun — mln so‘m, bir xona
BLADE

KPI kartochkalari

<div class="grid grid-cols-4 gap-4" wire:poll.60s>
    <x-kpi-card title="Oktabr savdosi" :value="$kpi['sales']"
        unit="so‘m" :delta="$kpi['sales_delta']" />
    <x-kpi-card title="Debitorlik" :value="$kpi['receivable']"
        unit="so‘m" :href="route('reports.receivables')" />
    <x-kpi-card title="Muddati o‘tgan" :value="$kpi['overdue']"
        unit="so‘m" />
    <x-kpi-card title="Kam qoldiq" :value="$kpi['low_stock']" unit="ta"
        :href="route('reports.stock', ['low' => 1])" />
</div>
<p class="text-sm">
    Ma’lumotlar {{ $refreshedAt->format('d.m.Y H:i') }} holatiga
</p>
▶ Natija
[Oktabr savdosi: 1 692 320 000 so‘m  ▲ 16,3 %]  [Debitorlik: 77 120 000 so‘m]
[Muddati o‘tgan: 37 120 000 so‘m]  [Kam qoldiq: 6 ta mahsulot]
Ma’lumotlar 27.10.2026 03:00 holatiga
  • x-kpi-card — qayta ishlatiladigan Blade komponenti; sonni o‘zi number_format bilan formatlaydi
  • wire:poll.60s — Livewire komponenti har 60 soniyada qayta render
  • :delta — o‘tgan oyga nisbatan o‘zgarish — LAG yoki servisda hisoblanadi

Loyiha bosqichi: natija va topshirish

Natija: ReportService asosidagi uchta hisobot (ombor qoldiqlari, davr bo‘yicha savdo, debitorlik va uning muddati), ularning Excel va PDF eksporti, KPI kartochkalari va 12 oylik savdo diagrammasi bilan dashboard, mv_sales_monthly materiallashtirilgan ko‘rinishi.

Baholash mezonlari
  • ReportService da stockBalances, salesByPeriod, receivables metodlari; har biri bazada agregatsiya qiladi (PHP siklida yig‘ish yo‘q).
  • Ko‘rsatkich ta’riflari docs/reports.md da yozilgan; ekran, Excel va PDF dagi jami qiymatlar bir xil.
  • Filtrlar (from, to, group, warehouse_id) Form Request bilan tekshiriladi; SQL ga foydalanuvchi qiymati faqat binding orqali tushadi.
  • Hisobotlar rollar bo‘yicha himoyalangan (can: middleware), menyuda faqat ruxsat etilganlari ko‘rinadi.
  • Excel eksport ko‘p varaqli, pul ustunlari son formatida; PDF DejaVu Sans bilan, o‘zbek harflari to‘g‘ri.
  • Dashboard: 4 ta KPI kartochkasi va bo‘sh oylari to‘ldirilgan 12 oylik diagramma.
  • mv_sales_monthly migratsiyasi UNIQUE indeks bilan, reports:refresh buyrug‘i CONCURRENTLY bilan ishlaydi.
  • Hisobot ustunlari uchun indekslar migratsiyasi va 12 oylik seed ma’lumotlari mavjud.

📤 feature/reports Pull Request havolasini yuboring. PR tavsifida: uchta hisobot va dashboard skrinshotlari, bitta Excel va bitta PDF namunasi (fayl sifatida biriktirilgan) hamda reports:refresh ishlashini ko‘rsatuvchi terminal chiqishi bo‘lsin.

?

Qanday ishlaydi. Chapdan topshiriqni tanlang, shartni o‘qing va shartdagi imzo bo‘yicha sof PHP funksiya yoki sinf yozing (Laravelsiz). ▶ Sinab ko‘rish — pastdagi chaqiruv ifodasini kodingiz bilan bajaradi (baholanmaydi). ✓ Topshirish — server kodni namunaviy va yashirin testlar bilan tekshiradi; natija o‘qituvchiga ko‘rinadi.

Yechildi 0 / 8
Ball 0

💡 Maslahat

PHP 8.3 kodi · avtomatik saqlanadi