Hi I want lifetime closed order sales accurate record in my magento2 store

I write a code for it using core php :

$servername = "localhost";
$username = "aaa";
$password = "aaaa";
$dbname = "mydb";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);

$sql = "SELECT sum(base_grand_total) as total FROM `sales_order` WHERE status='processing_closed'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo  $row["total"];
} else {
    echo "0 results";

It gives me accurate revenue my store generated from the start . now i want to display that result over here

enter image description here

For this i paste that code in below file location:


but that gives me error refuse to pass new connection inside file , i am new in magento2 please help me how can i get my results over here , i have learned that magento2 have builtin calculation for this using reports but every time when i refresh stats it gives me wrong value so that's why i wants to write my own method for it because my last status of order is collected_closed which i manually create , Please guide for solutions thanks !


To changes the behaver of life time sales at Dashboard section,you should changes the code of calculateSales of class Magento\Reports\Model\ResourceModel\Order\Collection..

Create a plugin for that class and create an around method of that function .

namespace {Vendor}\{Modulename}\Plugin;

class {YourClassname}
    public function aroundCalculateSales(
    \Magento\Reports\Model\ResourceModel\Order\Collection $subject, 
    \Closure $proceed


            $expr = new \Zend_Db_Expr("SUM(io.base_grand_total) ;

            if ($isFilter == 0) {
                $expr = '(' . $expr . ') * main_table.base_to_global_rate';
        $expr = new \Zend_Db_Expr("SUM(io.base_grand_total)         
                ['lifetime' => "SUM({$expr})", 'average' => "AVG({$expr})"]
                'main_table.status IN(?)',

Note that: code is tested.

| improve this answer | |
  • can you also provide other necessary plugin files which i have to create with it . i am new in magento2 , please – Hamza Mustafa Apr 4 '18 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.