Symfony Yearly Sales Report Integration

Question

Hello I am trying to make a company sales report generator but I am stuck. I tried to write the code as below but SQL functions are not working:

public function getMonthlyReport($company, $year)
    {
    $searchYear = New \DateTimeImmutable("$year-01T-01T00:00:00");
    $searchYearEnd = New \DateTimeImmutable("$year-12-31T23:59:59");


    $query = $this->createQueryBuilder('s')
        ->select('SUM(expenses.price)')
        ->select('SUM(s.price)')
        ->select('MONTHNAME(s.created_at)')
        ->leftJoin('s.Expenses', 'expenses')
        ->andWhere('s.Company = :company')
        ->setParameter('company', $company)
        ->andWhere('s.created_at BETWEEN  :startDate AND :endDate')
        ->setParameter('startDate ', $searchYear)
        ->setParameter('endDate', $searchYearEnd);
    return $query
        ->getQuery()
        ->getResult();
}

I want a result as below:

Month       Sales     Expenses
January     1250      0
February    10000     54879
March       0         4578
April       54000     44567
May         7854      2135
June        6399      8000
July        0         0
August      128000    1200
September   128000    1200
October     128000    1200
November    128000    1200
December    128000    1200

Appreciate your helps, Regards

EDIT 1: I have written a SQL query as below but it's only fetching if there is a record on that month(0 if no record presents in that month) and I couldn't execute it in Symfony due to SUM and MONTHNAME functions

SELECT SUM(expenses.price), SUM(sales.price), MONTHNAME(sales.created_at) FROM sales left join expenses On expenses.sales_id = sales.sales_id WHERE 1 GROUP BY MONTHNAME(sales.created_at) ORDER BY sales.created_at ASC;

Result:

Month       Sales     Expenses
November    1650000   5275000
February    2750000   10550000
April       21450000  79125000
March       4950000   15825000

Databases work like that - you can only select what's in the database. When you don't have entries for some month names, then they won't appear in the results. The solution is easy: add the missing months using PHP. — Florian Metzger-Noel, Apr 20 '22 at 06:47
I have fixed by adding a calender table and a slight tweak in repository, I will post the answer soon — seneill, Apr 20 '22 at 06:49

score 0 · Accepted Answer · answered Apr 20 '22 at 18:34

I have figured out what to do and will explain in 3 steps.

First Step:

Create a Symfony 'Calendar' entity with 'month' attribute as datetime. And Insert 12 months as dd.mm.yy for example; 01.01.2022, 01.02.2022,...

Second Step:

Write a SQL query to fetch Monthnames and left join with your related table(sales and expenses table for this question) as below.

SELECT MONTH(c.month) as mOrder,MONTHNAME(c.month) AS mName, (CASE WHEN SUM(e.price) IS NULL THEN 0 ELSE SUM(e.price) END), (CASE WHEN SUM(s.price) IS NULL THEN 0 ELSE SUM(s.price) END) FROM calender c LEFT JOIN sales On MONTH(c.month) = MONTH(s.created_at) LEFT JOIN expenses e On e.sale_id=s.id

Third Step:

Convert your SQL query to DQL one by one. Install DoctrineExtensions to use functions like MONTH(), YEAR(), DAY(), MONTHNAME() as guided in:

How can I use SQL's YEAR(), MONTH() and DAY() in Doctrine2?

And add additional conditions after join statement instead of 'where' clause. For me the function was like below.

public function getMonthlyReportRowsbyCompany($company, $year, $state)
{
    $searchYear = New \DateTimeImmutable("$year-01T-01T00:00:00");
    $fields = array('MONTH(c.month) as mOrder,MONTHNAME(c.month) AS mName', '(CASE WHEN SUM(e.price) IS NULL THEN 0 ELSE SUM(e.price) END)' ,'(CASE WHEN SUM(s.price) IS NULL THEN 0 ELSE SUM(s.price) END)');
    if($state == "0")
        $sales_states= ['1', '2', '3'];
    else
        $sales_states[] = $siparis_durumu;
    if($company== "0")
        $company= true;
    $query = $this->createQueryBuilder('c')
        ->set('lc_time_names', 'tr_TR')
        ->select($fields)
        ->leftJoin('App:Sales', 's', 'WITH', 'MONTH(c.month) = MONTH(s.created_at) AND s.Company= :company AND s.state IN (:state) AND YEAR(s.created_at) = YEAR(:searchYear)')
        ->setParameter('company', $company)
        ->setParameter('state', $sales_states)
        ->setParameter(':searchYear', $searchYear)
        ->leftJoin('App:Expenses', 'e', 'WITH', 'e.sales=s.id')
        ->orderBy('mOrder', 'ASC')
        ->groupBy('c.month');
    
    
    return $query
        ->getQuery()
        ->getResult();
}

Only problem is I couldn't find a way to make 'set('lc_time_names', 'tr_TR')' statement work. Other than that it works perfect.

Symfony Yearly Sales Report Integration

1 Answers1