2

I am creating a simple script to count items ordered in a multi-store Magento installation and display the results in a webpage. I have written something that correctly counts all ordered items.

I now wish to narrow this down and count only certain product ids for certain store ids. How can I target specific columns from $results?

require_once('./app/Mage.php');
Mage::app();

$resource = Mage::getSingleton('core/resource');

$readConnection = $resource->getConnection('core_read');

$dateToday = date('Y-m-d');
$noDays = 1;

for ($i = 0; $i < 7; $i++) {
    $pastWeek = date('Y-m-d', strtotime('-'.$noDays.' days', strtotime($dateToday)));
    $noDays++;
    $query = "SELECT * FROM `sales_flat_order_item` WHERE `created_at` BETWEEN '".$pastWeek." 00:00:00' AND '".$pastWeek." 23:59:59'";
    $results = $readConnection->fetchAll($query);
    $j = 0;
    foreach ($results as $value) {

        //something like - if (store id == 1) { if (product id == 2 || 3) { $j++; } else {

$j++;
        }
        echo "Date: ".$pastWeek." | Quantity Sold: ".$j."<br />";
    }
2

Magento have a resource collection model for sales_flat_order_item table means sales items collection. if you want to filter the sales items then you can use addAttributeToFilter()

$collection = Mage::getResourceModel('sales/order_item_collection');

By using this resource model you can filters,group by table columns store_id,product_id,created_at of that collection.

For getting a collection of particular date interval, you can try below example

$collection = Mage::getResourceModel('sales/order_item_collection')->addAttributeToSelect('product_id');
$collection->addAttributeToFilter('created_at', array('gteq' =>'1994-10-24'));
$collection->addAttributeToFilter('created_at', array('lteq' => $dateToday));

Use getSelect()->group('FieldName') to collection.

Example:

$collection->getSelect()->group('main_table.product_id');

For getting the count of that collection use count() function.

echo $dateToday = date('Y-m-d');
$collection = Mage::getResourceModel('sales/order_item_collection')->addAttributeToSelect('product_id');
$collection->addAttributeToFilter('created_at', array('gteq' =>'1994-10-24'));
$collection->addAttributeToFilter('created_at', array('lteq' => $dateToday));


echo $collection->count();

For getting more details abount filter check out https://wiki.magento.com/display/m1wiki/Using+Magento+1.x+collections

| improve this answer | |

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.