Totals and groupBy run uncorrectly in datagrid

Hi all! I was created a datagrid with code:

order-in-time-report: acl_resource: tna_report_order_by_time_view source: type: orm query: from: - { table: TnaPosBundle:Order, alias: order_report_time } select: - order_report_time.id - order_report_time.doc_date - SUM( order_report_time.qty ) as sum_qty - SUM( order_report_time.amount ) as sum_amount where: and: - (order_report_time.doc_date >= :start_date AND order_report_time.doc_date <= :end_date) groupBy: order_report_time.doc_date bind_parameters: - start_date - end_date columns: doc_date: label: Date frontend_type: date sum_qty: label: Sum qty sum_amount: label: Sum amount frontend_type: currency totals: total: extends: grand_total per_page: true hide_if_one_page: true columns: doc_date: label: Page total grand_total: columns: doc_date: label: Grand total sum_qty: expr: SUM( order_report_time.qty ) sum_amount: expr: SUM( order_report_time.amount ) formatter: currency sorters: columns: doc_date: data_name: order_report_time.doc_date sum_qty: data_name: sum_qty sum_amount: data_name: sum_amount default: model: ASC filters: columns: doc_date: type: date data_name: order_report_time.doc_date sum_qty: type: number data_name: sum_qty sum_amount: type: currency data_name: sum_amount options: entityHint: report data export: true entity_pagination: true

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76

order-in-time-report:     acl_resource: tna_report_order_by_time_view     source:       type: orm       query:         from:           - { table: TnaPosBundle:Order, alias: order_report_time }         select:           - order_report_time.id           - order_report_time.doc_date           - SUM( order_report_time.qty ) as sum_qty           - SUM( order_report_time.amount ) as sum_amount         where:           and:             - (order_report_time.doc_date >= :start_date AND order_report_time.doc_date <= :end_date)         groupBy:  order_report_time.doc_date       bind_parameters:         - start_date         - end_date       columns:       doc_date:         label:  Date         frontend_type: date       sum_qty:         label:  Sum qty       sum_amount:         label:  Sum amount         frontend_type: currency       totals:       total:         extends: grand_total         per_page: true         hide_if_one_page: true         columns:           doc_date:             label: Page total         grand_total:         columns:           doc_date:             label: Grand total           sum_qty:             expr: SUM( order_report_time.qty )           sum_amount:             expr: SUM( order_report_time.amount )             formatter: currency       sorters:       columns:         doc_date:           data_name:  order_report_time.doc_date         sum_qty:           data_name:  sum_qty         sum_amount:           data_name:  sum_amount       default:         model:  ASC       filters:       columns:         doc_date:           type: date           data_name:  order_report_time.doc_date         sum_qty:           type: number           data_name:  sum_qty         sum_amount:           type: currency           data_name:  sum_amount       options:       entityHint: report data       export: true       entity_pagination: true

But when I run, it cannot group by with my field.
Row grand-total run ok, but row page-total run uncorrect, it show value like grand-total (total of all value in grid, not in 1 page)
Some one can help me.

Topic

[Mike KudelyaParticipant]

Hi,

Try to delete

YAML

- order_report_time.id

1	- order_report_time.id

[abelParticipant]

Great, It’s working. But why?
I think it does not affect the result?

[abelParticipant]

And I have one more question. I create success report table with that grid. And now I want create line char with Charjs and data in report table. And my question is: How to get correct data from data grid?
Thanks again!

[Mike KudelyaParticipant]

Hi,

Chart feature is supported by default, you can choose required columns when you editing a report. Here is description of ChartBundle. If you have datagrid object, that for example you can retrieve data the following approach:

PHP

$result = $datagrid->getDatasource()->getResults();

1	$result = $datagrid->getDatasource()->getResults();

[abelParticipant]

Thanks you, I will try.
Sorry, I have 1 more question.
I was created 2 table with 2 column as follow:
– In table Order:

/** * @var * * @ORM\ManyToOne(targetEntity="Tna\Bundle\PosReportBundle\Entity\ReportType") * @ORM\JoinColumn(name="store_id", referencedColumnName="store_id") * @Soap\ComplexType("string", nillable=true) */ private $store_id;

1 2 3 4 5 6 7 8

/**      * @var      *      * @ORM\ManyToOne(targetEntity="Tna\Bundle\PosReportBundle\Entity\ReportType")      * @ORM\JoinColumn(name="store_id", referencedColumnName="store_id")      * @Soap\ComplexType("string", nillable=true)      */     private $store_id;

– In table Store

/** * @var * * @ORM\Column(name="store_id", type="string") * @ORM\Id */ private $store_id;

1 2 3 4 5 6 7

/**      * @var      *      * @ORM\Column(name="store_id", type="string")      * @ORM\Id      */     private $store_id;

When I import Order from xlsx file throw cron, it’s failed (before that I create column store_id in table Order with annotion @ORM\Column(…), import ok).
And when I use inner join in my grid

query: from: - { table: TnaPosBundle:Order, alias: order_report_time_channel } select: - order_report_time_channel.doc_date - SUM( CASE WHEN (s.name='Store Online') THEN order_report_time_channel.amount ELSE 0 END ) as sum_amount_online - SUM( CASE WHEN (s.name='Store Online') THEN 0 ELSE order_report_time_channel.amount END ) as sum_amount_offline where: and: - (order_report_time_channel.doc_date >= :start_date AND order_report_time_channel.doc_date <= :end_date) join: inner: - { join: order_report_time_channel.store_id, alias: s } groupBy: order_report_time_channel.doc_date bind_parameters: - start_date - end_date

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

query:         from:           - { table: TnaPosBundle:Order, alias: order_report_time_channel }         select:           - order_report_time_channel.doc_date           - SUM( CASE WHEN (s.name='Store Online') THEN order_report_time_channel.amount ELSE 0 END ) as sum_amount_online           - SUM( CASE WHEN (s.name='Store Online') THEN 0 ELSE order_report_time_channel.amount END ) as sum_amount_offline         where:           and:             - (order_report_time_channel.doc_date >= :start_date AND order_report_time_channel.doc_date <= :end_date)         join:           inner:             - { join: order_report_time_channel.store_id, alias: s }         groupBy:  order_report_time_channel.doc_date       bind_parameters:         - start_date         - end_date

and when I call that grid in view.html.twig, this error occurred

An exception has been thrown during the rendering of a template ("[Semantical Error] line 0, col 155 near 's WHERE (order_report_time_channel.doc_date': Error: Class Tna\Bundle\PosBundle\Entity\Order has no association named store_id") in src/Tna/Bundle/PosReportBundle/Resources/views/ReportOrderByTime/view.html.twig at line 71.

1

An exception has been thrown during the rendering of a template ("[Semantical Error] line 0, col 155 near 's WHERE (order_report_time_channel.doc_date': Error: Class Tna\Bundle\PosBundle\Entity\Order has no association named store_id") in src/Tna/Bundle/PosReportBundle/Resources/views/ReportOrderByTime/view.html.twig at line 71.

[abelParticipant]

I was try to get data from datagrid follow you. But I can’t get datagrid.
I use class BuildAfter to get datagrid, is that true way?

[Mike KudelyaParticipant]

Hi,

Try to clear cache: /app/cache/dev and /app/cache/prod. If it didn’t help, try to rename $store_id variable in Order entity, for example to $store. In any case probably doctrine can’t find our field.

I don’t think that events like buildAfter, buildBefore can help you. If you want to create custom report page you must change and extend either report view action or create own action. It is depends what you want.

[abelParticipant]

I was used event buildBefore but it occurred same error as BuildAfter

Controller "Tna\Bundle\PosBundle\Controller\DefaultController::indexAction()" requires that you provide a value for the "$event" argument (because there is no default value or because there is a non optional argument after this one).

1	Controller "Tna\Bundle\PosBundle\Controller\DefaultController::indexAction()" requires that you provide a value for the "$event" argument (because there is no default value or because there is a non optional argument after this one).

And I don’t think class report controller can help me, because I want to create many report for 1 table (with date, channel, team, …). As I saw, that class created 1 chart for 1 entity (no parameter) but I want create 1 chart for 1 datagrid of entity (with some input parameter).

My idea is: create report crub (with report entity), field of report entity are name, start_date, end_date (order create date), and description. In my view report, have 3 tag:
– Report info
– Datagrid of all order was created between start_date and end_date
– Last, line chart of order in this datagrid

I was use

$datagrid = $this->get('oro_datagrid.datagrid.manager')->getDatagrid( $gridName, [PagerInterface::PAGER_ROOT_PARAM => [PagerInterface::DISABLED_PARAM => true]] );

1 2 3 4

$datagrid = $this->get('oro_datagrid.datagrid.manager')->getDatagrid(     $gridName,     [PagerInterface::PAGER_ROOT_PARAM => [PagerInterface::DISABLED_PARAM => true]] );

for get data in datagrid. But I can’t pass parameters to datagrid so it’s show error

Cannot bind datasource parameter "start_date", there is no datagrid parameter with path "start_date".

1	Cannot bind datasource parameter "start_date", there is no datagrid parameter with path "start_date".

[abelParticipant]

Update:
I was use ParameterBag to passed 2 parameter start_date and end_date to my datagrid. But I can’t get those field in current report (create chart for any report with it’s start_date and end_date).
How to do that?

[abelParticipant]

Sorry, I can get all data I want, but when I render chart to template, nothing show.
This is my chart.yml

oro_chart: order_line_chart_by_time: label: Order Line Chart Follow Day data_schema: label: name: label label: Day required: true default_type: string type: date field_name: doc_date value: name: value label: Amount required: true default_type: decimal type: currency field_name: sum_amount default_settings: chartColors: ["#ACD39C", "#BE9DE2", "#6598DA", "#ECC87E", "#A4A2F6", "#6487BF", "#65BC87", "#8985C2", "#ECB574", "#84A377"] chartFontSize: 9 chartFontColor: "#454545" chartHighlightColor: "#FF5E5E" template: OroChartsBundle:Chart:multiline.html.twig

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

oro_chart:   order_line_chart_by_time:     label:  Order Line Chart Follow Day     data_schema:       label:         name: label         label: Day         required: true         default_type: string         type: date         field_name: doc_date       value:         name: value         label: Amount         required: true         default_type: decimal         type: currency         field_name: sum_amount     default_settings:       chartColors: ["#ACD39C", "#BE9DE2", "#6598DA", "#ECC87E", "#A4A2F6", "#6487BF", "#65BC87", "#8985C2", "#ECB574", "#84A377"]       chartFontSize: 9       chartFontColor: "#454545"       chartHighlightColor: "#FF5E5E"     template: OroChartsBundle:Chart:multiline.html.twig

This is my controller (include view report and create chart):

/** * @Route("/{id}", name="tna_report_order_by_time_view", requirements={"id"="\d+"}) * @Template * @AclAncestor("tna_report_order_by_time_view") */ public function viewAction(ReportOrderByTime $reportOrderByTime) { $parameter = [ 'start_date' => $reportOrderByTime->getStartDate(), 'end_date' => $reportOrderByTime->getEndDate() ]; $gridName = "order-in-time-report"; $grid = $this->get('oro_datagrid.datagrid.manager')->getDatagrid( $gridName, new ParameterBag($parameter) ); $datagrid = $grid->getData(); $data = array_values($datagrid['data']); $chartOptions = array_merge_recursive( ['name' => 'multiline_chart'], $this->get('oro_chart.config_provider')->getChartConfig('order_line_chart_by_time') ); $chart = $this->get('oro_chart.view_builder') ->setArrayData($data) ->setOptions($chartOptions) ->getView(); return array('entity' => $reportOrderByTime, 'chart' => $chart); }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

/**      * @Route("/{id}", name="tna_report_order_by_time_view", requirements={"id"="\d+"})      * @Template      * @AclAncestor("tna_report_order_by_time_view")      */     public function viewAction(ReportOrderByTime $reportOrderByTime) {         $parameter = [             'start_date' => $reportOrderByTime->getStartDate(),             'end_date' => $reportOrderByTime->getEndDate()         ];         $gridName = "order-in-time-report";         $grid = $this->get('oro_datagrid.datagrid.manager')->getDatagrid(             $gridName,             new ParameterBag($parameter)         );         $datagrid = $grid->getData();         $data = array_values($datagrid['data']);           $chartOptions = array_merge_recursive(             ['name' => 'multiline_chart'],             $this->get('oro_chart.config_provider')->getChartConfig('order_line_chart_by_time')         );           $chart = $this->get('oro_chart.view_builder')             ->setArrayData($data)             ->setOptions($chartOptions)             ->getView();           return array('entity' => $reportOrderByTime, 'chart' => $chart);     }

And this is my template:

// block header // block navbutton {% block content_data %} // detail of report // datagrid of all order in start_date and end_date {% set chart %} {% if entity.getReportWith() == '' %} {{ chart.render()|raw }} {% endif %} {% if entity.getReportWith() == 'Date' %} {{ chart.render()|raw }} {% endif %} {% endset %} {% set dataBlocks = dataBlocks|merge([ { 'title': 'Chart Report'|trans, 'subblocks': [ { 'data' : [chart] } ] } ]) %} {% set id = 'reportOrderByTimeView' %} {% set data = { 'dataBlocks': dataBlocks } %} {{ parent() }} {% endblock content_data %}

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

// block header // block navbutton {% block content_data %} // detail of report // datagrid of all order in start_date and end_date       {% set chart %}         {% if entity.getReportWith() == '' %}             {{ chart.render()|raw }}         {% endif %}           {% if entity.getReportWith() == 'Date' %}             {{ chart.render()|raw }}         {% endif %}     {% endset %}       {% set dataBlocks = dataBlocks|merge([         {             'title': 'Chart Report'|trans,             'subblocks': [                 { 'data' : [chart] }             ]         }     ]) %}       {% set id = 'reportOrderByTimeView' %}     {% set data = { 'dataBlocks': dataBlocks } %}     {{ parent() }} {% endblock content_data %}

[Mike KudelyaParticipant]

Try this:

PHP

$gridName = "order-in-time-report"; $grid = $this->get('oro_datagrid.datagrid.manager')->getDatagrid($gridName); $data = $grid->getData()->offsetGet('data'); $chartOptions = array_merge_recursive( ['name' => 'line_chart'], $this->get('oro_chart.config_provider')->getChartConfig('order_line_chart_by_time') ); $chart = $this->get('oro_chart.view_builder') ->setArrayData($data) ->setOptions($chartOptions) ->getView();

1 2 3 4 5 6 7 8 9 10 11 12 13

$gridName = "order-in-time-report";         $grid = $this->get('oro_datagrid.datagrid.manager')->getDatagrid($gridName);         $data = $grid->getData()->offsetGet('data');           $chartOptions = array_merge_recursive(             ['name' => 'line_chart'],             $this->get('oro_chart.config_provider')->getChartConfig('order_line_chart_by_time')         );           $chart = $this->get('oro_chart.view_builder')             ->setArrayData($data)             ->setOptions($chartOptions)             ->getView();

[abelParticipant]

Great! It’s work, but have a problem. Date in horizontal not true

My report started at 25/7 and ended at 31/7. But it’s not have 25/7 instead is 24/7 (same value). And when I hover in this point, it’s show day is 25/7.
How to fix that problem?

[Author]

Replies