Magento CSV Report Script – Unprocessed Sales Orders 1 comment


Magento CSV Report Script – Unprocessed Sales Orders.
We have to be careful to set table name as per Magento Version wise
sales_flat_order — >for version 1.5.1.0
and
salesr_order –> for version 1.3 and below




// Magento CSV Report Script -  Unprocessed Sales Orders
// DATABASE CONNECTIOn
$hostname_MySQLCon="localhost";
$database_MySQLCon = "DB";
$username_MySQLCon = "USER";
$password_MySQLCon = "PASS";
$MySQLCon = mysql_pconnect($hostname_MySQLCon, $username_MySQLCon, $password_MySQLCon);
$MySQLDb = mysql_select_db($database_MySQLCon, $MySQLCon);

// FUNCTION AREA
function get_DB_Row($query){
    $result = @mysql_query($query);
    $numresults = @mysql_num_rows($result);
    for ($i = 0; $i < $numresults; $i++){
    $row = @mysql_fetch_array($result);
    }
    return $row;
}


// Using this temporary function until I find correct way to pull country names from the database according to two digit code
// Put in your own until a database polled solution is uncovered
function getCountryName($code){
    switch($code) {
    case "GB" 	:	$country = "United Kingdom";
    break;
    case "IE" 	:	$country = "Ireland";
    break;
    case "GB" 	:	$country = "United Kingdom";
    break;
    case "IN" 	:	$country = "India";
    break;
    }
    return $country;
}

// INITILISATION AREA
$sitename = "yoursite.com";// update this to customise your filename
$datestring = date('j-m-y');
$filename = $sitename."-".$datestring;
$output = "";
$fieldlist = array("orderid","firstname","lastname","address","city","region","country",
"postcode","telephone","email","itemcode","itemname","quantity","shipping_instructions");

$checklist = array("country", "quantity");


// FIELDLIST INSTRUCTIONS
// The order of the CSV fields are set here as well as the field titles
// The field titles must correspond to a number of SQL Variables being set with the AS operator below
// If you change the default titles be sure to also update the SQL command accordingly
// Please note that 'country' and 'quantity' values must be processed but should still be put in your prefered sequence
$numfields = sizeof($fieldlist);
// *********************   NOW START BUILDING THE CSV
// Create the column headers
for($k =0; $k < $numfields;  $k++) {
$output .= $fieldlist[$k];
if ($k < ($numfields-1)) $output .= ", ";
}


$output .= "\n";

//annet-pk sales_flat_order for version 1.5.1.0
$orderquery = "SELECT * FROM sales_flat_order";// Query to find unprocessed orders
$orderresult = mysql_query($orderquery);
$numorders = @mysql_num_rows($orderresult);


for($i =0; $i < $numorders; $i++) {
$order = @mysql_fetch_array($orderresult);// Place each order into an array for proccessing
$orderid = $order['entity_id']; // Grab the orderid for use in the main SQL command

// The following SQL command will find all the individual items associated with this order

$itemquery = 	"SELECT sales_flat_order_item.order_id AS orderid,
                    sales_flat_order_item.product_type,
                    sales_flat_order_item.quote_item_id,
                    sales_flat_order_item.parent_item_id AS parentid,
                    sales_flat_order_item.sku AS itemcode,
                    sales_flat_order_item.name AS itemname,
                    sales_flat_order_item.qty_ordered AS qty_ordered,
                    sales_flat_order_item.qty_shipped AS qty_shipped,
                    sales_flat_quote_address.email AS email,
                    sales_flat_quote_address.prefix AS title,
                    sales_flat_quote_address.firstname AS firstname,
                    sales_flat_quote_address.lastname AS lastname,
                    sales_flat_quote_address.street AS address,
                    sales_flat_quote_address.city AS city,
                    sales_flat_quote_address.region AS region,
                    sales_flat_quote_address.country_id AS country,
                    sales_flat_quote_address.postcode AS postcode,
                    sales_flat_quote_address.telephone AS telephone,
                    sales_flat_quote_address.shipping_description AS shipping_instructions
                    FROM
                    sales_flat_order_item
                    INNER JOIN sales_flat_quote_item
                    ON sales_flat_order_item.quote_item_id = sales_flat_quote_item.item_id
                    INNER JOIN sales_flat_quote_address
                    ON sales_flat_quote_item.quote_id = sales_flat_quote_address.quote_id
                    WHERE
                    sales_flat_order_item.order_id = $orderid
                    AND sales_flat_quote_address.address_type = 'shipping'
                    AND sales_flat_order_item.product_type <> 'configurable'
                    AND sales_flat_order_item.qty_shipped < sales_flat_order_item.qty_ordered";
//echo "<br />".$itemquery;

$itemresult = mysql_query($itemquery);		// Run the query
$numitems = @mysql_num_rows($itemresult);   // Check the number of items in the order
 //echo "<font style='color:red'>".$numitems."</font>";


for($j =0; $j < $numitems; $j++) {
$item = @mysql_fetch_array($itemresult); // Place the item in an array for processing

// Output the order item values in the same sequence set in the fieldlist to match headers
for($m =0; $m < sizeof($fieldlist); $m++) {
    $fieldvalue = $fieldlist[$m];
    if(in_array($fieldvalue, $checklist)) { // check if on special processing list
      //  echo "<br />".$fieldvalue;
        if($fieldvalue == "country") {
            $output .= getCountryName($item[$fieldvalue]);
        }
        if($fieldvalue == "quantity") {
            $parentid = $item['parentid'];
           
            if($parentid == '') { // simple product sold on its own (no parent) ('' or NULL annet-pk )
                $qty_ordered = $item["qty_ordered"];
                $qty_shipped = $item["qty_shipped"];
                
            }
            else { // simple product was part of bundle or configurable group
                // If so, use the parent quanities to calculate
                $parentitem = get_DB_row("SELECT * FROM sales_flat_order_item WHERE item_id = $parentid");
              
                $qty_ordered = $parentitem["qty_ordered"];
                $qty_shipped = $parentitem["qty_shipped"];
            }
            $output .= ($qty_ordered - $qty_shipped);
        }
    }
    else {

         // echo "<font style='color:red'>FIELD != quantity</font>";
            $output .= $item[$fieldvalue];
    }
    if ($m < ($numfields-1)) $output .= ", ";
}//for loop item ordered values

$output .= "\n";
}
}//for number of orders ends


// Send the CSV file to the browser for download

header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=$filename.csv");

echo $output;
exit;



Thanks to : http://www.blastedthing.com/magento/admin/mag-export-list-of-magento-orders-in-csv-format/

Share on Facebook




About Pragnesh Karia

Pragnesh Karia, Open Source Enthusiast, Software Professional, Software Developer, Technical Lead ,Magento, Joomla ,Joomla LMS , Moodel LMS ,PHP ,Mysql, Ajax, Javascript, Jquery, Linux, Fan of Open Sources , Annet Technologies , SEO Analyst , Mootools


One thought on “Magento CSV Report Script – Unprocessed Sales Orders