Laravel Türkiye Discord Kanalı Forumda kod paylaşılırken dikkat edilmesi gerekenler!Birlikte proje geliştirmek ister misiniz?

Rapor Hazırlama Hakkında

betulyilmaz

Merhabalar,

User, Payment, Installment ve Transaction olmak üzere 4 adet modelim mevcut.

Bu modeller ile hazırlamam gereken 6 adet rapor var.

Biliyorum uzun ve karışık oldu ama açıklamalarını yazdığım raporları nasıl oluşturabilirim?

Yardımcı olacak arkadaşlarıma şimdiden saygı ve sevgilerimle...

User modeli

public function payments()
{
    return $this->hasMany('App\Models\Payment', 'customer_id');
}

public function transactions()
{
    return $this->hasMany('App\Models\Transaction', 'customer_id');
}

public function installments()
{
    return $this->hasMany('App\Models\Installment', 'customer_id');
}

Payment modeli

protected $fillable = [
    'sale_id',
    'customer_id',
    'amount_one',
    'payment_date_one',
    'amount_two',
    'payment_date_two',
    'amount_three',
    'payment_date_three',
    'amount_four',
    'payment_date_four',
    'amount_five',
    'payment_date_five',
    'amount_six',
    'payment_date_six',
];

public function customer()
{
    return $this->belongsTo('App\Models\User', 'customer_id', 'id');
}

Installment modeli

protected $fillable = [
    'installment_no',
    'customer_id',
    'installment_date',
    'installment_type',
    'undertaking_date',
    'amount',
    'status',
    'staff_id',
    'payment_date',
];

public function customer()
{
    return $this->belongsTo('App\Models\User', 'customer_id', 'id');
}

Transaction modeli

protected $fillable = [
    'installment_id',
    'customer_id',
    'sale_id',
    'payment_date',
    'payment_type',
    'payment_method',
    'bank_id',
    'is_cancelled',
    'notes',
    'amount',
    'staff_id',
];

public function customer()
{
    return $this->belongsTo('App\Models\User', 'customer_id', 'id');
}

<-- Rapor Controller İçeriği -->

installments tablosundaki 'status' alanı 'ODENDI' olanların toplam 'amount' u, transactions tablosundaki 'payment_type' alanı 'BORC' olanların toplam 'amount' larından FAZLA olan müşteri listesi.

public function planSaveMore(Request $request)
{
    $query = User::query();

    $orderBy = 'asc';
    $perPage = 20;
    $sortBy = 'id';

    if ($request->has('orderBy')) $orderBy = $request->query('orderBy');
    if ($request->has('perPage')) $perPage = $request->query('perPage');
    if ($request->has('sortBy')) $sortBy = $request->query('sortBy');
    if ($request->has('name')) $query->where('name', 'like', '%'.request()->input('name').'%');

    $dataList = $query->orderBy($sortBy, $orderBy)->paginate($perPage)->withQueryString();

    return view('reports.plan-save-more', compact('dataList'));
}

public function planSaveMissing(Request $request)
{
    $query = User::query();

    $orderBy = 'asc';
    $perPage = 20;
    $sortBy = 'id';

    if ($request->has('orderBy')) $orderBy = $request->query('orderBy');
    if ($request->has('perPage')) $perPage = $request->query('perPage');
    if ($request->has('sortBy')) $sortBy = $request->query('sortBy');
    if ($request->has('name')) $query->where('name', 'like', '%'.request()->input('name').'%');

    $dataList = $query->orderBy($sortBy, $orderBy)->paginate($perPage)->withQueryString();

    return view('reports.plan-save-missing', compact('dataList'));
}

payments tablosundaki 'amount_one', 'amount_two', 'amount_three', 'amount_four', 'amount_five', 'amount_six' alanlarının toplamı, installments tablosundaki 'status' alanı 'ODENDI' olanların toplam 'amount' undan FAZLA olan müşteri listesi.

public function approvalPlanMore(Request $request)
{
    $query = User::query();

    $orderBy = 'asc';
    $perPage = 20;
    $sortBy = 'id';

    if ($request->has('orderBy')) $orderBy = $request->query('orderBy');
    if ($request->has('perPage')) $perPage = $request->query('perPage');
    if ($request->has('sortBy')) $sortBy = $request->query('sortBy');
    if ($request->has('name')) $query->where('name', 'like', '%'.request()->input('name').'%');

    $dataList = $query->orderBy($sortBy, $orderBy)->paginate($perPage)->withQueryString();

    return view('reports.approval-plan-more', compact('dataList'));
}

public function approvalPlanMissing(Request $request)
{
    $query = User::query();

    $orderBy = 'asc';
    $perPage = 20;
    $sortBy = 'id';

    if ($request->has('orderBy')) $orderBy = $request->query('orderBy');
    if ($request->has('perPage')) $perPage = $request->query('perPage');
    if ($request->has('sortBy')) $sortBy = $request->query('sortBy');
    if ($request->has('name')) $query->where('name', 'like', '%'.request()->input('name').'%');

    $dataList = $query->orderBy($sortBy, $orderBy)->paginate($perPage)->withQueryString();

    return view('reports.approval-plan-missing', compact('dataList'));
}

payments tablosundaki 'amount_one', 'amount_two', 'amount_three', 'amount_four', 'amount_five', 'amount_six' alanlarının toplamı, transactions tablosundaki 'payment_type' alanı 'BORC' olanların toplam 'amount' larından FAZLA olan müşteri listesi.

public function approvalSaveMore(Request $request)
{
    $query = User::query();

    $orderBy = 'asc';
    $perPage = 20;
    $sortBy = 'id';

    if ($request->has('orderBy')) $orderBy = $request->query('orderBy');
    if ($request->has('perPage')) $perPage = $request->query('perPage');
    if ($request->has('sortBy')) $sortBy = $request->query('sortBy');
    if ($request->has('name')) $query->where('name', 'like', '%'.request()->input('name').'%');

    $dataList = $query->orderBy($sortBy, $orderBy)->paginate($perPage)->withQueryString();

    return view('reports.approval-save-more', compact('dataList'));
}

public function approvalSaveMissing(Request $request)
{
    $query = User::query();

    $orderBy = 'asc';
    $perPage = 20;
    $sortBy = 'id';

    if ($request->has('orderBy')) $orderBy = $request->query('orderBy');
    if ($request->has('perPage')) $perPage = $request->query('perPage');
    if ($request->has('sortBy')) $sortBy = $request->query('sortBy');
    if ($request->has('name')) $query->where('name', 'like', '%'.request()->input('name').'%');

    $dataList = $query->orderBy($sortBy, $orderBy)->paginate($perPage)->withQueryString();

    return view('reports.approval-save-missing', compact('dataList'));
}

mgsmus

betulyilmaz Merhaba, forumumuza hoşgeldiniz. İlk istediğiniz şu şekilde olabilir:

SELECT users.id,
       users.name,
       SUM(i.amount) AS ins_total,
       SUM(t.amount) AS tra_total
FROM   users
       JOIN installements i
         ON users.id = i.customer_id
            AND i.status = 'ODENDI'
       JOIN transactions t
         ON users.id = t.customer_id
            AND t.payment_type = 'BORC'
GROUP  BY users.id,
          users.name
HAVING ins_total > tra_total

User::select([
        'users.id AS id',
        'users.name AS name',
        DB::raw("SUM(installements.amount) AS ins_total"),
        DB::raw("SUM(transactions.amount) AS tra_total"),
    ])
    ->join('installements', function ($join) {
        $join
            ->on('users.id', '=', 'installements.customer_id')
            ->where('installements.status', 'ODENDI')
    })
    ->join('transactions', function ($join) {
        $join
            ->on('users.id', '=', 'transactions.customer_id')
            ->where('transactions.payment_type', 'BORC')
    })
    ->groupBy([
        'id',
        'name'
    ])
    ->having('ins_total','>', 'tra_total')
    ->orderBy('id')
    ->paginate()
    ->withQueryString();

Elbette deneme şansım olmadığı için hata verebilir. Yol göstereceğini düşünüyorum, kendi ihtiyacınıza göre düzenlersiniz.

Diğer bir nokta ise Request parametrelerine nasıl ulaştığınız. Eğer bir değer SQL içerisine girecekse ve alabileceği değerler sınırlı ise bunları ya form request içinde ya da inline request içinde kontrol etmeniz gerekiyor:

// orderBy sadece asc ya da desc olabilir. Varsayılan asc.
$orderBy = $request->whenFilled('orderBy', function ($input) {
    return !in_array($input, ['asc', 'desc'])
        ? 'asc'
        : $input;
}, fn() => 'asc');

// sortBy sadece id ya da name olabilir, bunları arttırabilirsiniz. Varsayılan id.
$sortBy = $request->whenFilled('sortBy', function ($input) {
    return !in_array($input, ['id', 'name'])
        ? 'id'
        : $input;
}, fn() => 'id');

// perPage sadece 1 ile 100 arasında bir sayı olabilir. Bunu kontrol etmezseniz biri 999999 yazarak
// sisteminizi patlatır. Varsayılan 20.
$perPage = $request->whenFilled('perPage', function ($input) {
    return !is_numeric($input) || $input < 1 || $input > 100
        ? 20
        : $input;
}, fn() => 20);

Ayrıca $request->query() yerine $request->input() kullanın. Zaten bir istekte ya GET parametresini alabilirsiniz ya da POST, ikisi aynı anda olmaz. Request::input() sizin için otomatik olarak hangisi varsa alır.

betulyilmaz

mgsmus Merhalar çok teşekkür ederim hoşbuldum 😊isminizi bilmediğim için @mgsmus olarak hitap ediyorum kusura bakmayın lütfen.
Iki rapor çözülmüş oldu emeğinize ve ellerinize sağlık. Diğer 4 raporda payments tablosu ile ilgili. Fakat orda bir amount değil altı amount var. Ve o altı amountun toplamını alarak yapılması lazım bu mümkünmü yoksa farklı bir sorgulamamı yapmak gerekiyor?

mgsmus

betulyilmaz Aynı satırda oldukları için SELECT (amount_one + amount_two) AS total şeklinde alabilirsiniz. Eğer bunların da toplamı gruplanıp alınacaksa SELECT SUM(amount_one + amount_two) AS total

betulyilmaz

mgsmus çok teşekkür ederim @mgsmus 😊. İyi ki varsınız... Memnun oldum bu arada.

        $dataList = User::select([
            'users.id AS id',
            'users.name AS name',
            DB::raw("SELECT(amount_one + amount_two + amount_three + amount_four + amount_five + amount_six) AS app_total"),
            DB::raw("SUM(installments.amount) AS ins_total"),
        ])
        ->join('payments', function ($join) {
            $join
                ->on('users.id', '=', 'payments.customer_id');
        })
        ->join('installments', function ($join) {
            $join
                ->on('users.id', '=', 'installments.customer_id')
                ->where('installments.status', 'ODENDI');
        })
        ->groupBy([
            'id',
            'name'
        ])
        ->having('app_total', '>', 'ins_total')
        ->orderBy('id')
        ->paginate()
        ->withQueryString();

şeklinde yaptım fakat aşağıdaki gibi patladım 🙁

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT(amount_one + amount_two + amount_three + amount_four + amount_five + a...' at line 1 (SQL: select count(*) as aggregate from (select `users`.`id` as `id`, `users`.`name` as `name`, SELECT(amount_one + amount_two + amount_three + amount_four + amount_five + amount_six) AS app_total, SUM(installments.amount) AS ins_total from `users` inner join `payments` on `users`.`id` = `payments`.`customer_id` inner join `installments` on `users`.`id` = `installments`.`customer_id` and `installments`.`status` = ODENDI where `users`.`deleted_at` is null group by `id`, `name` having `app_total` > ins_total) as `aggregate_table`)

betulyilmaz

DB::raw("SUM(payments.amount_one + payments.amount_two + payments.amount_three + payments.amount_four + payments.amount_five + payments.amount_six) AS app_total"),

bu şekilde yaptığımda ise patlamıyor fakat boş geliyor.

Acaba amount_one ....amount_six lerden null olanlar olunca mı toplamda sıkıntı oluyor?

mgsmus

betulyilmaz Evet, ondan dolayı. Bu gibi durumları yaşamamak için genelde sayısal ifadelerde varsayılan 0 yapıyoruz, null yerine 0 işleniyor. Şu şekilde null gelmesini engelleyebilirsiniz:

MySQL:

SUM(
    IFNULL(payments.amount_one, 0) + IFNULL(payments.amount_two, 0)
) AS app_total

PostgreSQL:

SUM(
    COALESCE(payments.amount_one, 0) + COALESCE(payments.amount_two, 0)
) AS app_total

yapabilirsiniz.

Tabi veritabanı düzeyinde 0 işlenmesini sağlarsanız daha iyi olur, bunlarla uğraşmazsınız:

$table->decimal('amount_one', 15, 2)
    ->default(0);

betulyilmaz

mgsmus Tekrar merhabalar, iyi bir hafta geçirmenizi dilerim 🙂

        $dataList = User::select([
            'users.id AS id',
            'users.name AS name',
            DB::raw("SUM(installments.amount) AS plan"),
            DB::raw("SUM(transactions.amount) AS save"),
        ])
        ->join('installments', function ($join) {
            $join
                ->on('users.id', '=', 'installments.customer_id')
                ->where('installments.status', 'ODENDI');
        })
        ->join('transactions', function ($join) {
            $join
                ->on('users.id', '=', 'transactions.customer_id')
                ->where('transactions.payment_type', 'BORC');
        })
        ->groupBy([
            'id',
            'name'
        ])
        ->having('plan', '>', 'save')
        ->orderBy('id')
        ->paginate()
        ->withQueryString();

Planın Save den küçük olmasına rağmen listeye gelmesinin sebebi nedir acaba?

App\Models\User {#2834 ▼
  #fillable: array:25 [▶]
  #auditInclude: []
  #hidden: array:2 [▶]
  #casts: array:1 [▶]
  #dates: array:4 [▶]
  #connection: "mysql"
  #table: "users"
  #primaryKey: "id"
  #keyType: "int"
  +incrementing: true
  #with: []
  #withCount: []
  #perPage: 15
  +exists: true
  +wasRecentlyCreated: false
  #attributes: array:4 [▼
    "id" => 29015
    "name" => "TEST TEST"
    "plan" => 4.0
    "save" => 5.0
  ]
  #original: array:4 [▶]
  #changes: []
  #classCastCache: []
  #dateFormat: null
  #appends: []
  #dispatchesEvents: []
  #observables: []
  #relations: []
  #touches: []
  +timestamps: true
  #visible: []
  #guarded: array:1 [▶]
  #rememberTokenName: "remember_token"
  #accessToken: null
  #forceDeleting: false
  #userstamping: true
  -roleClass: null
  -permissionClass: null
  #excludedAttributes: []
  #auditEvent: null
}

betulyilmaz

Çok teşekkür ederim destekleriniz için 🙂 @mgsmus

mgsmus

betulyilmaz amount alanlarını tablolarda hangi tipte oluşturdunuz? Bir de ->orderBy('id')->dd() şeklinde bitirip çıkan sorguyu paylaşır mısınız?

betulyilmaz

mgsmus Tabiki;

"select `users`.`id` as `id`, `users`.`name` as `name`, SUM(installments.amount) AS plan, SUM(transactions.amount) AS save from `users` inner join `installments` on `users`.`id` = `installments`.`customer_id` and `installments`.`status` = ? inner join `transactions` on `users`.`id` = `transactions`.`customer_id` and `transactions`.`payment_type` = ? where `users`.`deleted_at` is null group by `id`, `name` having `plan` > ? order by `id` asc ◀"

array:3 [▼
  0 => "ODENDI"
  1 => "BORC"
  2 => "save"
]

Amount alanları 'double' tipinde.

mgsmus

betulyilmaz Direkt ekrana gelen çıktıyı değil sorguyu kopyalayıp yapıştırın (ok tuşuna basıp alanı genişletip tüm sorguyu görün), sorgu yarım gelmiş. Bir de amount alanını veritabanında hangi tipte tutuyorsunuz? Decimal değil mi?

betulyilmaz

mgsmus Güncelledim evet decimal.

mgsmus

betulyilmaz ->having('plan', '>', 'save') yerine ->havingRaw('plan > save') şeklinde deneyin.

betulyilmaz

mgsmus Evet şimdi hepsi oldu çok teşekkür ederim 🙂

betulyilmaz

@mgsmus
Özür dilerim tekrar rahatsız ediyorum🙁

    public function notPlanned(Request $request)
    {
        $dataList = User::select([
            'users.id AS id',
            'users.name AS name',
            DB::raw("SUM(installments.amount) AS totalInstallments"),
            DB::raw("SUM(transactions.amount) AS totalTransactions"),
        ])
        ->join('installments', function ($join) {
            $join
                ->on('users.id', '=', 'installments.customer_id');
        })
        ->join('transactions', function ($join) {
            $join
                ->on('users.id', '=', 'transactions.customer_id')
                ->where('transactions.payment_type', 'ALACAK');
        })
        ->groupBy([
            'id',
            'name'
        ])
        ->havingRaw('totalInstallments <> totalTransactions')
        ->orderBy('id')
        ->paginate()
        ->withQueryString();

        return view('dashboard.reports.not-planned', compact('dataList'));
    }

Bu işlemde;
totalInstallments sonucunun 3980
ve
totalTransactions sonucunun 3980 olması lazımken

totalInstallments 7960
totalTransactions 19900 çıkıyor.

App\Models\User {#2832 ▼
  #fillable: array:25 [▶]
  #auditInclude: []
  #hidden: array:2 [▶]
  #casts: array:1 [▶]
  #dates: array:4 [▶]
  #connection: "mysql"
  #table: "users"
  #primaryKey: "id"
  #keyType: "int"
  +incrementing: true
  #with: []
  #withCount: []
  #perPage: 15
  +exists: true
  +wasRecentlyCreated: false
  #attributes: array:4 [▼
    "id" => 29016
    "name" => "DENEME DENEME"
    "totalInstallments" => 7960.0
    "totalTransactions" => 19900.0
  ]
  #original: array:4 [▶]
  #changes: []
  #classCastCache: []
  #dateFormat: null
  #appends: []
  #dispatchesEvents: []
  #observables: []
  #relations: []
  #touches: []
  +timestamps: true
  #visible: []
  #guarded: array:1 [▶]
  #rememberTokenName: "remember_token"
  #accessToken: null
  #forceDeleting: false
  #userstamping: true
  -roleClass: null
  -permissionClass: null
  #excludedAttributes: []
  #auditEvent: null
}

installments tablosunda 5 adet kayıt var, transactions tablosunda iki adet kayıt var.
installments sonucunu transactions tablosundaki adetle çarpıyor ve transactions sonucunu installments tablosundaki adetle çarpıyor.

Bunun önüne nasıl geçebilirim?

mgsmus

betulyilmaz installments tablosuna eklediğiniz join'i kaldırın, sonra

DB::raw("SUM(installments.amount) AS totalInstallments"),

yerine

DB::raw("(SELECT SUM(installments.amount) FROM installments WHERE installments.customer_id = users.id) AS totalinstallments"),

deneyin ama doğru bir sorgu olup olmadığından emin değilim. Şimdi doğru bir sonuç verse bile kayıt sayısı değişince yanlış sonuç da verebilir. Sorguları kafadan yazıp veri olmadan doğru yazmak zor yani. Gruplamayı users tablosuna göre yaptığımız için gelen users sayısı kadar satırları klonluyor. Ya gruplanacak başka alanlar eklenerek bunun önüne geçilecek ya da bunun gibi join yerine biri subquery olacak. Başka yolları var mı bilmiyorum.

Olmazsa transactions join'i leftJoin yapıp deneyin.

Bir de tavsiyem önce SQL'i oluşturmaya çalışın, doğru sorguya ulaştıktan sonra bunu Builder'a dökün.

betulyilmaz

mgsmus Çok mersi bu tamam gibi sıkıntı görünmüyor şimdilik ellerinize sağlık.