VajdaShark Posted October 9, 2014 Share Posted October 9, 2014 Hi folks, after upgrading from 1.6 to 1.6.0.9 all dashboard stats stopped working. No data are shown, only zeroes. Everything else works flawlessly. Resetting the dashboard modules did not help. Any ideas? Thanks Link to comment Share on other sites More sharing options...
tomf75 Posted October 13, 2014 Share Posted October 13, 2014 Hi folks, after upgrading from 1.6 to 1.6.0.9 all dashboard stats stopped working. No data are shown, only zeroes. Everything else works flawlessly. Resetting the dashboard modules did not help. Any ideas? Thanks same for me: dashboard page is empty and also the statistics are 0 since the upgrade... (not the old ones, they are still showing their numbers... If anyone ia having a hint, please let me know! Link to comment Share on other sites More sharing options...
Guest Posted October 13, 2014 Share Posted October 13, 2014 Do you got a screenshot? Link to comment Share on other sites More sharing options...
tomf75 Posted October 13, 2014 Share Posted October 13, 2014 screenshot of the empty dashboard is here : http://scr.hu/2sps/9bdrl Link to comment Share on other sites More sharing options...
Guest Posted October 13, 2014 Share Posted October 13, 2014 Do you have demo modus on? Have you looked if you got the dashboard modules on? Link to comment Share on other sites More sharing options...
VajdaShark Posted October 13, 2014 Author Share Posted October 13, 2014 screenshot of the empty dashboard is here : http://scr.hu/2sps/9bdrl This was also my problem, but I solved it by resetting the corresponding modules (dashboard stats, goals etc.) The real problem is that data are not being collected in my dashboard, see screens: Link to comment Share on other sites More sharing options...
tomf75 Posted October 13, 2014 Share Posted October 13, 2014 Do you have demo modus on? Have you looked if you got the dashboard modules on? yes, of course i checked all the modules and also the demo mode is not on Link to comment Share on other sites More sharing options...
tomf75 Posted October 13, 2014 Share Posted October 13, 2014 This was also my problem, but I solved it by resetting the corresponding modules (dashboard stats, goals etc.) The real problem is that data are not being collected in my dashboard, see screens: so, we are having both the same problems, what do you mean "by resetting" the modules... uninstall and reinstalling again? Link to comment Share on other sites More sharing options...
VajdaShark Posted October 13, 2014 Author Share Posted October 13, 2014 so, we are having both the same problems, what do you mean "by resetting" the modules... uninstall and reinstalling again? Exactly. You can check which modules to reset if you look on modules positions (hooks). All modules in Dashboard sections. But it solves only the visibility of the modules, not the fact they are shut down. Link to comment Share on other sites More sharing options...
tomf75 Posted October 23, 2014 Share Posted October 23, 2014 Hi VajdaShark, did you find any solutions for this till now? ... in the meantime i fixes also the modules, they are showing up now, but also the stats are just 0 Link to comment Share on other sites More sharing options...
VajdaShark Posted October 23, 2014 Author Share Posted October 23, 2014 Hi VajdaShark, did you find any solutions for this till now? ... in the meantime i fixes also the modules, they are showing up now, but also the stats are just 0 Hi, no solution yet, maybe Magento 1 Link to comment Share on other sites More sharing options...
RPA2303 Posted October 24, 2014 Share Posted October 24, 2014 Hi Same problem here after upgrading from 1.4.0.7 I have partial stats : most of stats linked to orders and customers are filled with 0, except newsletter subscription. But visits, online visitor, order quantity and amount, order and turnover have only some 0. Link to comment Share on other sites More sharing options...
Gomlers Posted October 28, 2014 Share Posted October 28, 2014 I have the same problem with 1.6.0.9. Old orders shows statistics, new orders/customers do not. Online customers shows always 0 Visits and Visitors shows always 0 Shoppingcarts for last 30 minutes on adminpanel show correct Hope someone from prestashop can pay attention to this, as the stats really are crucial for planning ahead. And with the great panel that comes with 1.6, it's a pitty to not be able to use it.. Link to comment Share on other sites More sharing options...
elorac Posted October 30, 2014 Share Posted October 30, 2014 (edited) I have the same problem with 1.6.0.9. Old orders shows statistics, new orders/customers do not. Online customers shows always 0 Visits and Visitors shows always 0 Shoppingcarts for last 30 minutes on adminpanel show correct Hope someone from prestashop can pay attention to this, as the stats really are crucial for planning ahead. And with the great panel that comes with 1.6, it's a pitty to not be able to use it.. I will second this. We are having the same problem in the dashboard stats. "Sales", "Orders", "Cart Value", "Conversion Rate", and "Net Profit" are all zero, however, "Visits" are showing correctly. Nice dashboard graph and layout - too bad it doesn't work at all. It seems like this worked before, so I'm thinking some module update broke it. We are using 1.6.0.9 with all modules up to date. We have made some changes to the order statuses, so maybe the stats are looking for a default order completed status that it cannot find? We also have invoices turned off, I saw some mention in older versions that invoices had to be turned on. Does anybody have an answer to this yet? Edited October 30, 2014 by elorac (see edit history) Link to comment Share on other sites More sharing options...
elorac Posted October 30, 2014 Share Posted October 30, 2014 (edited) I will second this. We are having the same problem in the dashboard stats. "Sales", "Orders", "Cart Value", "Conversion Rate", and "Net Profit" are all zero, however, "Visits" are showing correctly. Nice dashboard graph and layout - too bad it doesn't work at all. It seems like this worked before, so I'm thinking some module update broke it. We are using 1.6.0.9 with all modules up to date. We have made some changes to the order statuses, so maybe the stats are looking for a default order completed status that it cannot find? We also have invoices turned off, I saw some mention in older versions that invoices had to be turned on. Does anybody have an answer to this yet? I figured it out partially. Use this fix ONLY if you do not have invoices enabled! The default files should work fine if invoices are enabled in Orders -> Invoices. This fix works for the Stats section in backoffice. You need to replace "invoice_date" with "date_add" in a bunch of module files in order to get this to work. With invoices turned off, the date is always 0, thus, the stats need to pull a real date. I chose to use "date_add" (the date it was added). So get out your find and replace text editor. Be careful and be sure to have a backup before editing these files, and don't blame me if you mess it up. This change will need to be re-applied if these modules get updated, assuming Presta doesn't fix it. Here are the files to replace "invoice_date" with "date_add" in... /modules/statssales/statssales.php (14 occurrences) /modules/statsforecast/statsforecast.php (14 occurrences) /modules/statscheckup/statscheckup.php (1 occurrence) /modules/statsbestvouchers/statsbestvouchers.php (1 occurrence) /modules/statsbestsuppliers/statsbestsuppliers.php (2 occurrences) /modules/statsbestproducts/statsbestproducts.php (1 occurrence) /modules/statsbestmanufacturers/statsbestmanufacturers.php (2 occurrences) /modules/statsbestcustomers/statsbestcustomers.php (2 occurrences) /modules/statsbestcategories/statsbestcategories.php (1 occurrence) I'm going to take a look and see if I can figure out why the stats still do not show up on the dashboard. At least I got the stats working as they should be in the stats section now... If anyone else knows the main dashboard graph fix, please post. I'm assuming it's a similar fix.. Thanks Edited October 30, 2014 by elorac (see edit history) 7 1 Link to comment Share on other sites More sharing options...
elorac Posted October 30, 2014 Share Posted October 30, 2014 (edited) I'm going to take a look and see if I can figure out why the stats still do not show up on the dashboard. And now for the fix on the Dashboard page with the nice graphs... First, copy the file /controllers/admin/AdminStatsController.php to /override/controllers/admin/AdminStatsController.php Next, open up /override/controllers/admin/AdminStatsController.php and replace all instances of "invoice_date" with "date_add". There should be 25 occurrences. You will also need to replace "invoice_date" with "date_add" in /modules/dashproducts/dashproducts.php (1 occurrence) Hopefully this and my post above solves this issue for good! Edited October 30, 2014 by elorac (see edit history) 6 2 Link to comment Share on other sites More sharing options...
SuperiorWL Posted December 2, 2014 Share Posted December 2, 2014 I just wanted to add that elorac two post above fixed the issue with the dashboard for me too. 1 Link to comment Share on other sites More sharing options...
reflectiveoffice Posted December 3, 2014 Share Posted December 3, 2014 I have the same problem. It seems crazy that so many files need to be edited to get the Dashboard working properly. Link to comment Share on other sites More sharing options...
SuperiorWL Posted December 3, 2014 Share Posted December 3, 2014 I have the same problem. It seems crazy that so many files need to be edited to get the Dashboard working properly. Link to comment Share on other sites More sharing options...
SuperiorWL Posted December 3, 2014 Share Posted December 3, 2014 I thought the same thing, but I went through file by file and made the changes. My install was not upgrade, but a fresh install. I guess you just have to decide if you want the dashboard to work or not. 1 Link to comment Share on other sites More sharing options...
Rulleguitar Posted January 5, 2015 Share Posted January 5, 2015 Thank you for the solution elorac (#15 and #16). It seems to work for me (an upgraded 1.6.0.9). Link to comment Share on other sites More sharing options...
kondor75 Posted May 18, 2015 Share Posted May 18, 2015 I've follwed all elorac (#15 and #16) steps but my BO don't show graphic stats. I've an upgraded prestashop version 1.6.0.9 from 1.3.6.0 Link to comment Share on other sites More sharing options...
dprovost1990 Posted May 21, 2015 Share Posted May 21, 2015 Still broken Link to comment Share on other sites More sharing options...
Aromatik Posted July 12, 2015 Share Posted July 12, 2015 Thanks elorac - its works! Prestashop 1.6.0.14 Link to comment Share on other sites More sharing options...
PrestaHeaven.com Posted August 5, 2015 Share Posted August 5, 2015 Tested #15 and #16 on 1.6.0.14 and it worked! I have disabled the invoices as the other people who had this problem and the #15/#16 fixed it! Link to comment Share on other sites More sharing options...
sosontae Posted September 15, 2015 Share Posted September 15, 2015 In case of somebody has the same problem as I had.... For me it was the conversion rate which was set a long time before to "0", it results as an empty stats on the dashboard for sales! (Chart). So to fix that, Go to "Localization=>Currencies, and SET A CONVERSION RATE", to put back the conversion rate on previous orders, go to your phpmyadmin and execute this request : "UPDATE ps_orders set conversion_rate=1.000000 where conversion_rate=0.000000" Hope it helps. Cheers. 1 Link to comment Share on other sites More sharing options...
skorupa Posted September 23, 2015 Share Posted September 23, 2015 I experienced similar problem with Prestashop 1.6.1.1 I did little research in code, and found out that: 1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice) 2) you only need to put this override to override/controllers/admin/AdminStatsController.php : <?php class AdminStatsController extends AdminStatsControllerCore { public static function getTotalSales($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $sales[strtotime($row['date'])] = $row['sales']; } return $sales; } elseif ($granularity == 'month') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $sales[strtotime($row['date'].'-01')] = $row['sales']; } return $sales; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(total_paid_tax_excl / o.conversion_rate) FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getOrders($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $orders[strtotime($row['date'])] = $row['orders']; } return $orders; } elseif ($granularity == 'month') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $orders[strtotime($row['date'].'-01')] = $row['orders']; } return $orders; } else { $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } return $orders; } public static function getPurchases($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $purchases = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $purchases[strtotime($row['date'])] = $row['total_purchase_price']; } return $purchases; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getExpenses($date_from, $date_to, $granularity = false) { $expenses = ($granularity == 'day' ? array() : 0); $orders = Db::getInstance()->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl, total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl, o.module, a.id_country, o.id_currency, c.id_reference as carrier_reference FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); foreach ($orders as $order) { // Add flat fees for this order $flat_fees = Configuration::get('CONF_ORDER_FIXED') + ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED') : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN') ); // Add variable fees for this order $var_fees = $order['total_paid_tax_incl'] * ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR') : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN') ) / 100; // Add shipping fees for this order $shipping_fees = $order['total_shipping_tax_excl'] * ( $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP') : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS') ) / 100; // Tally up these fees if ($granularity == 'day') { if (!isset($expenses[strtotime($order['date'])])) { $expenses[strtotime($order['date'])] = 0; } $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees; } else { $expenses += $flat_fees + $var_fees + $shipping_fees; } } return $expenses; } } And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date. 6 Link to comment Share on other sites More sharing options...
chienandalu Posted October 28, 2015 Share Posted October 28, 2015 I had the same problem after updating from 1.4. I just set all the invoice_date rows with date_add value. Like this: UPDATE ps_orders SET invoice_date = date_add The problem was instantly fixed and new orders are registering properly. 1 Link to comment Share on other sites More sharing options...
prestalearn Posted November 19, 2015 Share Posted November 19, 2015 I experienced similar problem with Prestashop 1.6.1.1 I did little research in code, and found out that: 1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice) 2) you only need to put this override to override/controllers/admin/AdminStatsController.php : <?php class AdminStatsController extends AdminStatsControllerCore { public static function getTotalSales($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $sales[strtotime($row['date'])] = $row['sales']; } return $sales; } elseif ($granularity == 'month') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $sales[strtotime($row['date'].'-01')] = $row['sales']; } return $sales; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(total_paid_tax_excl / o.conversion_rate) FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getOrders($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $orders[strtotime($row['date'])] = $row['orders']; } return $orders; } elseif ($granularity == 'month') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $orders[strtotime($row['date'].'-01')] = $row['orders']; } return $orders; } else { $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } return $orders; } public static function getPurchases($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $purchases = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $purchases[strtotime($row['date'])] = $row['total_purchase_price']; } return $purchases; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getExpenses($date_from, $date_to, $granularity = false) { $expenses = ($granularity == 'day' ? array() : 0); $orders = Db::getInstance()->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl, total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl, o.module, a.id_country, o.id_currency, c.id_reference as carrier_reference FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); foreach ($orders as $order) { // Add flat fees for this order $flat_fees = Configuration::get('CONF_ORDER_FIXED') + ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED') : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN') ); // Add variable fees for this order $var_fees = $order['total_paid_tax_incl'] * ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR') : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN') ) / 100; // Add shipping fees for this order $shipping_fees = $order['total_shipping_tax_excl'] * ( $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP') : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS') ) / 100; // Tally up these fees if ($granularity == 'day') { if (!isset($expenses[strtotime($order['date'])])) { $expenses[strtotime($order['date'])] = 0; } $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees; } else { $expenses += $flat_fees + $var_fees + $shipping_fees; } } return $expenses; } } And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date. Do I understand it correctly if this causes all orders to be counted in stats even if it has no invoice created? Sounds great, do I need to add all the text to the file or just some of it? Does it matter if I add it on top or bottom? Big thanks! Link to comment Share on other sites More sharing options...
skorupa Posted November 20, 2015 Share Posted November 20, 2015 Yes, you have to paste whole coude, couse it is override. It won't change any core files and thanks to that it will work after upgrade. One thing to remember it will only change statistics on Dashboard in backoffice. Our client didn't is using external invoice app with precise selling stats and thouse are used only as "quick view". Hope it will help @chienandalu Link to comment Share on other sites More sharing options...
New-Newbie Posted December 9, 2015 Share Posted December 9, 2015 I experienced similar problem with Prestashop 1.6.1.1 I did little research in code, and found out that: 1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice) 2) you only need to put this override to override/controllers/admin/AdminStatsController.php : <?php class AdminStatsController extends AdminStatsControllerCore { public static function getTotalSales($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $sales[strtotime($row['date'])] = $row['sales']; } return $sales; } elseif ($granularity == 'month') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $sales[strtotime($row['date'].'-01')] = $row['sales']; } return $sales; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(total_paid_tax_excl / o.conversion_rate) FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getOrders($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $orders[strtotime($row['date'])] = $row['orders']; } return $orders; } elseif ($granularity == 'month') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $orders[strtotime($row['date'].'-01')] = $row['orders']; } return $orders; } else { $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } return $orders; } public static function getPurchases($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $purchases = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $purchases[strtotime($row['date'])] = $row['total_purchase_price']; } return $purchases; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getExpenses($date_from, $date_to, $granularity = false) { $expenses = ($granularity == 'day' ? array() : 0); $orders = Db::getInstance()->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl, total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl, o.module, a.id_country, o.id_currency, c.id_reference as carrier_reference FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); foreach ($orders as $order) { // Add flat fees for this order $flat_fees = Configuration::get('CONF_ORDER_FIXED') + ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED') : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN') ); // Add variable fees for this order $var_fees = $order['total_paid_tax_incl'] * ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR') : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN') ) / 100; // Add shipping fees for this order $shipping_fees = $order['total_shipping_tax_excl'] * ( $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP') : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS') ) / 100; // Tally up these fees if ($granularity == 'day') { if (!isset($expenses[strtotime($order['date'])])) { $expenses[strtotime($order['date'])] = 0; } $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees; } else { $expenses += $flat_fees + $var_fees + $shipping_fees; } } return $expenses; } } And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date. Exactly ! It was beacuse the client was using an external invoice software and so invoicing was desactivated... Can't belive it. Thank you so much for pointing it up, and your override works great ! Link to comment Share on other sites More sharing options...
hootersam Posted December 18, 2015 Share Posted December 18, 2015 I experienced similar problem with Prestashop 1.6.1.1 I did little research in code, and found out that: 1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice) 2) you only need to put this override to override/controllers/admin/AdminStatsController.php : And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date. Thanks a lot for this simple fix for those not using prestashop invoice system. Working great on 1.6.1.2! Link to comment Share on other sites More sharing options...
nerovi16 Posted May 1, 2016 Share Posted May 1, 2016 I figured it out partially. Use this fix ONLY if you do not have invoices enabled! The default files should work fine if invoices are enabled in Orders -> Invoices. This fix works for the Stats section in backoffice. You need to replace "invoice_date" with "date_add" in a bunch of module files in order to get this to work. With invoices turned off, the date is always 0, thus, the stats need to pull a real date. I chose to use "date_add" (the date it was added). So get out your find and replace text editor. Be careful and be sure to have a backup before editing these files, and don't blame me if you mess it up. This change will need to be re-applied if these modules get updated, assuming Presta doesn't fix it. Here are the files to replace "invoice_date" with "date_add" in... /modules/statssales/statssales.php (14 occurrences) /modules/statsforecast/statsforecast.php (14 occurrences) /modules/statscheckup/statscheckup.php (1 occurrence) /modules/statsbestvouchers/statsbestvouchers.php (1 occurrence) /modules/statsbestsuppliers/statsbestsuppliers.php (2 occurrences) /modules/statsbestproducts/statsbestproducts.php (1 occurrence) /modules/statsbestmanufacturers/statsbestmanufacturers.php (2 occurrences) /modules/statsbestcustomers/statsbestcustomers.php (2 occurrences) /modules/statsbestcategories/statsbestcategories.php (1 occurrence) I'm going to take a look and see if I can figure out why the stats still do not show up on the dashboard. At least I got the stats working as they should be in the stats section now... If anyone else knows the main dashboard graph fix, please post. I'm assuming it's a similar fix.. Thanks Hola, esto me funciono, con la mayoria de las estadisticas, menos la de bestcustomers, puede haber otro problema asociado? Link to comment Share on other sites More sharing options...
pattihis Posted June 8, 2016 Share Posted June 8, 2016 (edited) I had the same problem after updating from 1.4. I just set all the invoice_date rows with date_add value. Like this: UPDATE ps_orders SET invoice_date = date_add The problem was instantly fixed and new orders are registering properly. Simple and effective. Added skorupa's override too. Dashboard now shows all stats correctly. (prestashop 1.6.1.4) Thank you..!!!!!!! Edited June 9, 2016 by pattihis (see edit history) Link to comment Share on other sites More sharing options...
xpumpa Posted August 1, 2016 Share Posted August 1, 2016 (edited) No need this complicated sollutions... Easy solution is for me: In PaymentModule.php change this line $order->invoice_date = '0000-00-00 00:00:00'; to $order->invoice_date = date('Y-m-d H:i:s'); It's working for me and I don't need to override or edited many files... for data before this mod you need execute this query UPDATE ps_orders SET invoice_date = date_add Edited August 1, 2016 by xpumpa (see edit history) 4 Link to comment Share on other sites More sharing options...
skorupa Posted August 2, 2016 Share Posted August 2, 2016 No need this complicated sollutions... Easy solution is for me: In PaymentModule.php change this line $order->invoice_date = '0000-00-00 00:00:00'; to $order->invoice_date = date('Y-m-d H:i:s'); It's working for me and I don't need to override or edited many files... for data before this mod you need execute this query UPDATE ps_orders SET invoice_date = date_add This is good solution, but remeber to put it in override so Prestashop update won't change it. Other thing is that I override only one file. Where you will have to check every payment module you use to check if your solution have to be applied or not. Link to comment Share on other sites More sharing options...
xpumpa Posted August 3, 2016 Share Posted August 3, 2016 This is independent on payments modules. This mod is in classes/PaymentModule.php function validateOrder. Every order call this function i think. Link to comment Share on other sites More sharing options...
sanbikes Posted October 20, 2016 Share Posted October 20, 2016 For me it did not work this solution. :'(PS 1.6.1 Link to comment Share on other sites More sharing options...
skorupa Posted October 20, 2016 Share Posted October 20, 2016 Can you describe more, what you did. And what is the outcome? Link to comment Share on other sites More sharing options...
mikkino Posted November 19, 2016 Share Posted November 19, 2016 No need this complicated sollutions... Easy solution is for me: In PaymentModule.php change this line $order->invoice_date = '0000-00-00 00:00:00'; to $order->invoice_date = date('Y-m-d H:i:s'); It's working for me and I don't need to override or edited many files... for data before this mod you need execute this query UPDATE ps_orders SET invoice_date = date_add THANKS!!! THIS WORK FOR ME TOO PS 1.4.9 Link to comment Share on other sites More sharing options...
sting5 Posted November 22, 2016 Share Posted November 22, 2016 I experienced similar problem with Prestashop 1.6.1.1 I did little research in code, and found out that: 1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice) 2) you only need to put this override to override/controllers/admin/AdminStatsController.php : <?php class AdminStatsController extends AdminStatsControllerCore { public static function getTotalSales($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $sales[strtotime($row['date'])] = $row['sales']; } return $sales; } elseif ($granularity == 'month') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $sales[strtotime($row['date'].'-01')] = $row['sales']; } return $sales; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(total_paid_tax_excl / o.conversion_rate) FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getOrders($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $orders[strtotime($row['date'])] = $row['orders']; } return $orders; } elseif ($granularity == 'month') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $orders[strtotime($row['date'].'-01')] = $row['orders']; } return $orders; } else { $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } return $orders; } public static function getPurchases($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $purchases = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $purchases[strtotime($row['date'])] = $row['total_purchase_price']; } return $purchases; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getExpenses($date_from, $date_to, $granularity = false) { $expenses = ($granularity == 'day' ? array() : 0); $orders = Db::getInstance()->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl, total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl, o.module, a.id_country, o.id_currency, c.id_reference as carrier_reference FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); foreach ($orders as $order) { // Add flat fees for this order $flat_fees = Configuration::get('CONF_ORDER_FIXED') + ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED') : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN') ); // Add variable fees for this order $var_fees = $order['total_paid_tax_incl'] * ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR') : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN') ) / 100; // Add shipping fees for this order $shipping_fees = $order['total_shipping_tax_excl'] * ( $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP') : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS') ) / 100; // Tally up these fees if ($granularity == 'day') { if (!isset($expenses[strtotime($order['date'])])) { $expenses[strtotime($order['date'])] = 0; } $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees; } else { $expenses += $flat_fees + $var_fees + $shipping_fees; } } return $expenses; } } And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date. Any idea how to use this to fix "traffic" dashboard for visits, unique visitors etc.? I'm currently have zeroes in the dashboard, but stats module is working fine (also upgraded from 1.4.8.2 to 1.6.1.5. Link to comment Share on other sites More sharing options...
gabrielio Posted January 10, 2017 Share Posted January 10, 2017 I had this problem too, and the override solution worked perfect on Prestashop 1.6.1.4, but since i updated to 1.6.1.10, the override stopped working and the dashboard stats were empty, not even 0 value. So i discovered a solution for stats to show again, but i must tell you that the old stats(for orders before update) are not displayed. Here is what i did: 1. Go to Order > Invoices and CHECK "Enable invoices". 2. Go to Order > Statuses and for status "Processing in progress" choose to generate Invoice(the green check mark should appear). 3. Place a test order from the front of your site. After doing that, the order should be shown in the dashboard stats and the invoice should have been generated. 4. Go back to step Nr. 1 and access Order > Invoices and UNCHECK "Enable invoices". This way you will avoid generating Invoices for new orders and customers will not receive any invoice on e-mail when placing the order. 5. Place a new test order and you`ll see that it will be registered and showed in dashboard stats. That`s all. Link to comment Share on other sites More sharing options...
skorupa Posted January 10, 2017 Share Posted January 10, 2017 Hi, actually I updated shops where I use override solution to 1.6.1.10 and on both everything is working fine. Your enable -> disable solution will not work without override couse to generate statistics in dashboard Prestashop is checking what is invoice date. If there is none than it will not include such transaction in statistics :-[ Maby some other override changed statistics code in a way that prevent it from working. Regards. Link to comment Share on other sites More sharing options...
gabrielio Posted January 10, 2017 Share Posted January 10, 2017 Hi, actually I updated shops where I use override solution to 1.6.1.10 and on both everything is working fine. Your enable -> disable solution will not work without override couse to generate statistics in dashboard Prestashop is checking what is invoice date. If there is none than it will not include such transaction in statistics :-[ Maby some other override changed statistics code in a way that prevent it from working. Regards. Maybe(as you say some other override... but you have nor arguments on that, is just what you suppose), but you should not say that the solution i gave it`s not working as it is working for me with no override. So when you say that, other people that read this topic will probably not try it so they will not know if it will ever work for them as it worked for me. I thought that the purpose here on this forum is to help people and to give them our best experience on Prestashop problems we faced and we somehow solved. Have you at least tried the solution i gave before answering this topic? If it doesn`t work for you, it doesn`t mean that it will not work for anybody else. Let pleople try, they have no reason not to do it. Thank you. 1 Link to comment Share on other sites More sharing options...
skorupa Posted January 10, 2017 Share Posted January 10, 2017 Actually I can :-D I programm in PHP & MySQL daily, and when I read AdminStatsController.php, I see everywhere parts of code that looks like that WHERE `invoice_date` BETWEEN ... You don't have to be a programmer to know that this stat will be based on invoice date :-D So new transactions will not be taken in to account in stats if generating invoice is turned off. Link to comment Share on other sites More sharing options...
gabrielio Posted January 10, 2017 Share Posted January 10, 2017 Actually I can :-D I programm in PHP & MySQL daily, and when I read AdminStatsController.php, I see everywhere parts of code that looks like that WHERE `invoice_date` BETWEEN ... You don't have to be a programmer to know that this stat will be based on invoice date :-D So new transactions will not be taken in to account in stats if generating invoice is turned off. I know you code and i also don`t want to make this a dispute. I just tried the solution again on a Prestashop 1.6.1.7 this time(without any override), I followed the steps i mentioned and IT WORKS. I cannot explain how, but it works on 1.6.1.7 and 1.6.1.10(at least this is what i tested). The solution is simple, first you have to place a test order(or an order) with Enabled invoices from Order > Invoices and with status "Processing in progress" allowing to generate invoice. After you do that, you can uncheck "Enable invoices" form Order > Invoices and the orders will be counted. Maybe it is a Prestashop bug, but for now, at least for me, this solution works great, so you should try it on a new prestashop install and see if it works.(of course if you want to convince yourself) I just hope it helps other peoples on this topic. 1 Link to comment Share on other sites More sharing options...
Prestafan1234 Posted February 18, 2017 Share Posted February 18, 2017 I know you code and i also don`t want to make this a dispute. I just tried the solution again on a Prestashop 1.6.1.7 this time(without any override), I followed the steps i mentioned and IT WORKS. I cannot explain how, but it works on 1.6.1.7 and 1.6.1.10(at least this is what i tested). The solution is simple, first you have to place a test order(or an order) with Enabled invoices from Order > Invoices and with status "Processing in progress" allowing to generate invoice. After you do that, you can uncheck "Enable invoices" form Order > Invoices and the orders will be counted. Maybe it is a Prestashop bug, but for now, at least for me, this solution works great, so you should try it on a new prestashop install and see if it works.(of course if you want to convince yourself) I just hope it helps other peoples on this topic. I had PS version 1.6.1.1 and used the override, it worked great. I upgraded to version 1.6.1.11 and I can confirm the override no longer works like gabriello states. My Dashboard showed nothing (the modules are there but have no contents about sales). I have also fixed it by enabling invoices under Order statusses (I didn't even have to make a test order) and then disabling invoices again. Now I have the graph in Dashboard stats with data again. Link to comment Share on other sites More sharing options...
electrostuff_de Posted April 25, 2017 Share Posted April 25, 2017 And now for the fix on the Dashboard page with the nice graphs... First, copy the file /controllers/admin/AdminStatsController.php to /override/controllers/admin/AdminStatsController.php Next, open up /override/controllers/admin/AdminStatsController.php and replace all instances of "invoice_date" with "date_add". There should be 25 occurrences. You will also need to replace "invoice_date" with "date_add" in /modules/dashproducts/dashproducts.php (1 occurrence) Hopefully this and my post above solves this issue for good! I confirm, that this override works fine for PrestaShop v. 1.6.1.7. Great Job elorac! Link to comment Share on other sites More sharing options...
Ben90 Posted July 19, 2017 Share Posted July 19, 2017 I experienced similar problem with Prestashop 1.6.1.1 I did little research in code, and found out that: 1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice) 2) you only need to put this override to override/controllers/admin/AdminStatsController.php : <?php class AdminStatsController extends AdminStatsControllerCore { public static function getTotalSales($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $sales[strtotime($row['date'])] = $row['sales']; } return $sales; } elseif ($granularity == 'month') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $sales[strtotime($row['date'].'-01')] = $row['sales']; } return $sales; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(total_paid_tax_excl / o.conversion_rate) FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getOrders($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $orders[strtotime($row['date'])] = $row['orders']; } return $orders; } elseif ($granularity == 'month') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $orders[strtotime($row['date'].'-01')] = $row['orders']; } return $orders; } else { $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } return $orders; } public static function getPurchases($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $purchases = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $purchases[strtotime($row['date'])] = $row['total_purchase_price']; } return $purchases; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getExpenses($date_from, $date_to, $granularity = false) { $expenses = ($granularity == 'day' ? array() : 0); $orders = Db::getInstance()->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl, total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl, o.module, a.id_country, o.id_currency, c.id_reference as carrier_reference FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); foreach ($orders as $order) { // Add flat fees for this order $flat_fees = Configuration::get('CONF_ORDER_FIXED') + ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED') : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN') ); // Add variable fees for this order $var_fees = $order['total_paid_tax_incl'] * ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR') : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN') ) / 100; // Add shipping fees for this order $shipping_fees = $order['total_shipping_tax_excl'] * ( $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP') : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS') ) / 100; // Tally up these fees if ($granularity == 'day') { if (!isset($expenses[strtotime($order['date'])])) { $expenses[strtotime($order['date'])] = 0; } $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees; } else { $expenses += $flat_fees + $var_fees + $shipping_fees; } } return $expenses; } } And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date. Works like a charm! Can't believe Prestashop doesn't have this feature by default. Many people generate their own invoice using the accounting program rather than using the website. 1 Link to comment Share on other sites More sharing options...
danny019 Posted September 22, 2017 Share Posted September 22, 2017 (edited) I experienced similar problem with Prestashop 1.6.1.1 I did little research in code, and found out that: 1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice) 2) you only need to put this override to override/controllers/admin/AdminStatsController.php : <?php class AdminStatsController extends AdminStatsControllerCore { public static function getTotalSales($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $sales[strtotime($row['date'])] = $row['sales']; } return $sales; } elseif ($granularity == 'month') { $sales = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $sales[strtotime($row['date'].'-01')] = $row['sales']; } return $sales; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(total_paid_tax_excl / o.conversion_rate) FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getOrders($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $orders[strtotime($row['date'])] = $row['orders']; } return $orders; } elseif ($granularity == 'month') { $orders = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 7) as date, COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 7)'); foreach ($result as $row) { $orders[strtotime($row['date'].'-01')] = $row['orders']; } return $orders; } else { $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT COUNT(*) as orders FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } return $orders; } public static function getPurchases($date_from, $date_to, $granularity = false) { if ($granularity == 'day') { $purchases = array(); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o').' GROUP BY LEFT(`date_add`, 10)'); foreach ($result as $row) { $purchases[strtotime($row['date'])] = $row['total_purchase_price']; } return $purchases; } else { return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT SUM(od.`product_quantity` * IF( od.`purchase_supplier_price` > 0, od.`purchase_supplier_price` / `conversion_rate`, od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100 )) as total_purchase_price FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); } } public static function getExpenses($date_from, $date_to, $granularity = false) { $expenses = ($granularity == 'day' ? array() : 0); $orders = Db::getInstance()->ExecuteS(' SELECT LEFT(`date_add`, 10) as date, total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl, total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl, o.module, a.id_country, o.id_currency, c.id_reference as carrier_reference FROM `'._DB_PREFIX_.'orders` o LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1 '.Shop::addSqlRestriction(false, 'o')); foreach ($orders as $order) { // Add flat fees for this order $flat_fees = Configuration::get('CONF_ORDER_FIXED') + ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED') : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN') ); // Add variable fees for this order $var_fees = $order['total_paid_tax_incl'] * ( $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR') : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN') ) / 100; // Add shipping fees for this order $shipping_fees = $order['total_shipping_tax_excl'] * ( $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT') ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP') : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS') ) / 100; // Tally up these fees if ($granularity == 'day') { if (!isset($expenses[strtotime($order['date'])])) { $expenses[strtotime($order['date'])] = 0; } $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees; } else { $expenses += $flat_fees + $var_fees + $shipping_fees; } } return $expenses; } } And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date. Works like charm on 1.6.1.11. Thank you very much. Also your order status has to have "order confirmed" checked. Otherwise it won't count them. Edited September 22, 2017 by danny019 (see edit history) 1 Link to comment Share on other sites More sharing options...
Recommended Posts