$Orders=DB::table('orders')
    ->select(
        DB::raw('sum(orders.total) as sum'), 
        DB::raw('YEAR(created_at)  as data')
    )
    ->where(function ($query) use ($date_to, $date_from) {
        $query->where('orders.pay_status', 'paid')
    })
    ->groupBy("data")
    ->get();

I want to see sale Statistics yearly , top code work corectly but I want use Jalali date instead of miladi, so I need change created_at to jalali

I think I have to use my function instead of YEAR(created_at) or change groupBy function

If I can use verta(created_at)->year insted of YEAR(created_at), I think fix problem

Used Library

CodePudding user response：

I think you have to loop over your results in PHP, because your library doesn't work with Query Builder.

foreach ($Orders as $i => $order) {
   $newDate =  Verta::parseFormat('Y', $order->data);
  
   // add new date converted to your object - not sure about the lib, look at doc
   $Orders[$i]->dateFormated = $newDate->year;
}

CodePudding user response：

you cant convert the value of created_at to jalali on query , you need to calculate start & end of each jalaly year to miladi

1399-01-01 -> 2020-03-01
1399-12-31 -> 2021-02-01

you can put them in an array

$converted_dates = 
[
    '1397' => [
        'xxx' , 
        'xxx'
    ]
    ,'1398' => [
        'xxx' , 
        'xxxx'
    ]
    ,
    '1399' => [
        '2020-03-01' , 
        '2021-02-01'
    ]

];

and

$stats = []; 
foreach($converted_dates as $year=>$dates)
{
    $stats[$year] = DB::table('orders')
     ->select(
        DB::raw('sum(orders.total) as sum'), 
        DB::raw('YEAR(created_at)  as data')
    )
    ->whereBetween('created_at',  $dates   ))
    ->groupBy("data")
    ->get();
}