Jump to content

Export orders in txt

EnricoM93

By EnricoM93
in General topics

 Share

Recommended Posts

EnricoM93 Newbie

EnricoM93

Posted
Posted (edited)

I need some help to develop a way to export orders in txt because I tried to do it alone but i'm in trouble, specially with billing address and shipping address. I've read some helpful threads in forum but i can't finish the work alone.  

 

You may find some italian's words in file and code, sorry.

 

The layout that i need is this, like sample file attached:

 

All in a row:

 

Record Type (FT = customer FD = document line) | surname | name | tax code | VAT | Birth date | iso nation | email | billing address city | street Billing address | cap billing address | document date | Document Number | Total Document amount  | Total taxable | available field | available field | payment type (credit card) | Total shipping taxes included | phone | Company name shipping address | Surname shipping address | name Shipping address | City shipping address | Street Shipping address | Cap shipping address | voucher value | province Shipping Address | Province billing address | Cell phone

 

All in a row:

 

Record Type (FT = customer FD = document line) | document date | Document Number | Product code | amount | unit price including taxes | unit price excluding taxes | available field | VAT code

 

 

This is my code, it is not complete:

<?php

// Start connessione
$host = "localhost";
$user = "xxxxx";
$pass = "xxxxx";
$db = "xxxxx";

mysql_connect($host, $user, $pass);
mysql_select_db($db);
unset($host,$user,$pass,$db);
// Stop connessione

$query = "SELECT id_order, id_customer FROM ps_orders WHERE saved_as_txt = 0"; //tutti gli ID Ordine che non sono ancora stati salvati

$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) //goes through all the results
{
    $id_order = $row['id_order']; //salva l'attuale ID Ordine in una variabile
    $id_customer = $row['id_customer']; //salva l'attuale ID Cliente in una variabile
    $filename = $id_order.".txt";
    
    $file = fopen($filename,"w"); //crea un nuovo file
	
	$risultato = mysql_query("SELECT * FROM ps_orders"); 
	$num_righe = mysql_num_rows($risultato); 

	for ($mul = 1; $mul <= $num_righe; ++$mul) {
		
    $query2 = "SELECT * FROM ps_address WHERE id_customer = " . $id_customer;
	
    $result2 = mysql_query($query2);
    while ($row2 = mysql_fetch_array($result2)) //tutti gli indirizzi con lo stesso ID Cliente
    {
    
        $id_country = $row2['id_country']; //salva il valore come una variabile
    
        fwrite($file, $row2['lastname'] . " | ");
        fwrite($file, $row2['firstname'] . " | ");
        fwrite($file, $row2['dni'] . " | ");
        fwrite($file, $row2['vat_number'] . " | ");
		
		$query5 = "SELECT * FROM ps_customer WHERE id_customer = " . $id_customer;
        
        $result5 = mysql_query($query5);
        while ($row5 = mysql_fetch_array($result5))
        {
		fwrite($file, $row5['birthday'] . " | ");
		}
		
        $query4 = "SELECT iso_code FROM ps_country WHERE id_country = " . $id_country;
        
        $result4 = mysql_query($query4);
        while ($row4 = mysql_fetch_array($result4))
        {
            fwrite($file, $row4['iso_code'] . " | ");    
        }
		
		$query5 = "SELECT * FROM ps_customer WHERE id_customer = " . $id_customer;
        
        $result5 = mysql_query($query5);
        while ($row5 = mysql_fetch_array($result5))
        {
			fwrite($file, $row5['email'] . " | ");
		}
		
		fwrite($file, $row2['city'] . " | ");
		fwrite($file, $row2['address1'] . " | ");
        fwrite($file, $row2['postcode'] . " | ");
		
		$query6 = "SELECT * FROM ps_orders WHERE id_customer = " . $id_customer;
        
        $result6 = mysql_query($query6);
        while ($row6 = mysql_fetch_array($result6))
        {
			fwrite($file, $row6['invoice_date'] . " | ");
			fwrite($file, $row6['invoice_number'] . " | ");
			fwrite($file, $row6['total_paid'] . " | ");
			fwrite($file, $row6['total_paid_tax_excl'] . " | ");
		//fwrite($file, $row['...'] . " | "); campo disponibile
		//fwrite($file, $row['....'] . " | "); campo disponibile
			fwrite($file, $row6['payment'] . " | ");
			fwrite($file, $row6['total_shipping_tax_incl'] . " | ");
		}
		
        fwrite($file, $row2['phone'] . " | ");
		fwrite($file, $row2['company'] . " | <br>");
		
    [spam-filter]
    
    fclose($file);
    
    // aggiorna campo saved_as_txt
    $query3 = "UPDATE ps_orders SET saved_as_txt = 1 WHERE id_order = " . $id_order;
    mysql_query($query3);
    
}

echo "365 Bike Store <br><br> File creato!";

?>

Thanks for the help  ^_^

tracciato-import-ordini.txt

Edited by EnricoM93 (see edit history)
Link to comment
Share on other sites
  • 2 weeks later...
shokinro Newbie

shokinro

Posted
Posted

Not sure what you are trying to implement, but it seems not a simple one that someone can explain in a few words. And also your requirements are not very clear.

 

PrestaShop has CSV export function by default, which is also in text file, why not just use existing functions?

Link to comment
Share on other sites
EnricoM93 Newbie

EnricoM93

Posted
  • Author
Posted
My goal is to create a txt file with the export orders, which follow the scheme that I mentioned in the first post, so as to integrate the site with the management program of the physical store.

 

If there is a better way (and perhaps easier) to do what I need, I'll be very grateful to those who will help me in following this new road.

 

What I tried to do so far it comes from another post that I found here on the forum, from which I was inspired by the code, but the needs were different so I tried to adapt it to my situation without reaching any results.

Link to comment
Share on other sites
shokinro Newbie

shokinro

Posted
Posted

Text data file usually use one line for one record.

One line will be contains multiple columns, most common scenario is to use

1. fix length of column 

2. variable length of column but with a pre-defined separator.

 

I think the No.2 might be good for you. you output one order in one line, and separated each column with a separator  for example, "|".

Link to comment
Share on other sites
  • 7 years later...
creatived Newbie

creatived

Posted
Posted
On 3/21/2016 at 11:12 PM, EnricoM93 said:

I need some help to develop a way to export orders in txt because I tried to do it alone but i'm in trouble, specially with billing address and shipping address. I've read some helpful threads in forum but i can't finish the work alone.  

 

You may find some italian's words in file and code, sorry.

 

The layout that i need is this, like sample file attached:

 

All in a row:

 

Record Type (FT = customer FD = document line) | surname | name | tax code | VAT | Birth date | iso nation | email | billing address city | street Billing address | cap billing address | document date | Document Number | Total Document amount  | Total taxable | available field | available field | payment type (credit card) | Total shipping taxes included | phone | Company name shipping address | Surname shipping address | name Shipping address | City shipping address | Street Shipping address | Cap shipping address | voucher value | province Shipping Address | Province billing address | Cell phone

 

All in a row:

 

Record Type (FT = customer FD = document line) | document date | Document Number | Product code | amount | unit price including taxes | unit price excluding taxes | available field | VAT code

 

 

This is my code, it is not complete: 

<?php

// Start connessione
$host = "localhost";
$user = "xxxxx";
$pass = "xxxxx";
$db = "xxxxx";

mysql_connect($host, $user, $pass);
mysql_select_db($db);
unset($host,$user,$pass,$db);
// Stop connessione

$query = "SELECT id_order, id_customer FROM ps_orders WHERE saved_as_txt = 0"; //tutti gli ID Ordine che non sono ancora stati salvati

$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) //goes through all the results
{
    $id_order = $row['id_order']; //salva l'attuale ID Ordine in una variabile
    $id_customer = $row['id_customer']; //salva l'attuale ID Cliente in una variabile
    $filename = $id_order.".txt";
    
    $file = fopen($filename,"w"); //crea un nuovo file
	
	$risultato = mysql_query("SELECT * FROM ps_orders"); 
	$num_righe = mysql_num_rows($risultato); 

	for ($mul = 1; $mul <= $num_righe; ++$mul) {
		
    $query2 = "SELECT * FROM ps_address WHERE id_customer = " . $id_customer;
	
    $result2 = mysql_query($query2);
    while ($row2 = mysql_fetch_array($result2)) //tutti gli indirizzi con lo stesso ID Cliente
    {
    
        $id_country = $row2['id_country']; //salva il valore come una variabile
    
        fwrite($file, $row2['lastname'] . " | ");
        fwrite($file, $row2['firstname'] . " | ");
        fwrite($file, $row2['dni'] . " | ");
        fwrite($file, $row2['vat_number'] . " | ");
		
		$query5 = "SELECT * FROM ps_customer WHERE id_customer = " . $id_customer;
        
        $result5 = mysql_query($query5);
        while ($row5 = mysql_fetch_array($result5))
        {
		fwrite($file, $row5['birthday'] . " | ");
		}
		
        $query4 = "SELECT iso_code FROM ps_country WHERE id_country = " . $id_country;
        
        $result4 = mysql_query($query4);
        while ($row4 = mysql_fetch_array($result4))
        {
            fwrite($file, $row4['iso_code'] . " | ");    
        }
		
		$query5 = "SELECT * FROM ps_customer WHERE id_customer = " . $id_customer;
        
        $result5 = mysql_query($query5);
        while ($row5 = mysql_fetch_array($result5))
        {
			fwrite($file, $row5['email'] . " | ");
		}
		
		fwrite($file, $row2['city'] . " | ");
		fwrite($file, $row2['address1'] . " | ");
        fwrite($file, $row2['postcode'] . " | ");
		
		$query6 = "SELECT * FROM ps_orders WHERE id_customer = " . $id_customer;
        
        $result6 = mysql_query($query6);
        while ($row6 = mysql_fetch_array($result6))
        {
			fwrite($file, $row6['invoice_date'] . " | ");
			fwrite($file, $row6['invoice_number'] . " | ");
			fwrite($file, $row6['total_paid'] . " | ");
			fwrite($file, $row6['total_paid_tax_excl'] . " | ");
		//fwrite($file, $row['...'] . " | "); campo disponibile
		//fwrite($file, $row['....'] . " | "); campo disponibile
			fwrite($file, $row6['payment'] . " | ");
			fwrite($file, $row6['total_shipping_tax_incl'] . " | ");
		}
		
        fwrite($file, $row2['phone'] . " | ");
		fwrite($file, $row2['company'] . " | <br>");
		
    [spam-filter]
    
    fclose($file);
    
    // aggiorna campo saved_as_txt
    $query3 = "UPDATE ps_orders SET saved_as_txt = 1 WHERE id_order = " . $id_order;
    mysql_query($query3);
    
}

echo "365 Bike Store <br><br> File creato!";

?>

Thanks for the help  ^_^

tracciato-import-ordini.txt 1.24 kB · 3 downloads

Ciao, sei riuscito a risolvere poi? sto cercando uno script simile, se condividi il tuo codice finale potrebbe essermi utile per il lavoro che sto svolgendo, grazie!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...