Resolved export order virtuemart3 only one line per orderid

doc_denis
RO CSVI
Monday, 15 February 2016
Subscribe via email

Hello,

i want export one list of order at end of the month.
only one line per order_id exemple :

invoice number - date invoice - order_id - fullname - order_total - total_product_without_tax - product_tax - shipment_without_tax - shipment_tax - total_paiement - paiement_name

I currently exports much, but I have several lines per order from there to more than one item per order.

Is it possible to export a sales summary by month-end on one line per order ?

Thank-you
French user ...sorry for my english

Responses (7)

tharuna

Accepted Answer Pending Moderation

Hello,

I currently exports much, but I have several lines per order from there to more than one item per order.
Is it possible to export a sales summary by month-end on one line per order ?

You can check out the documentation Group by and Sort by fields to avoid duplicates in export.

French user ...sorry for my english

Good enough for us to understand :laugh:

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

more than a month ago
RO CSVI
# 1

doc_denis

Accepted Answer Pending Moderation

Hello tharuna, (édit : thank-you)
I looked at this page, and testing, I only add the 'INVOICE_NUMBER "on my field and grouped by invoice number ...
Export shows 6 identical lines if the command Contains 6 items

My very simple export model:



<?xml version="1.0"?>

<csvi>

  <template>

    <settings>

      <action><![CDATA[export]]></action>

      <component><![CDATA[com_virtuemart]]></component>

      <operation><![CDATA[order]]></operation>

      <exportto>

        <option><![CDATA[todownload]]></option>

      </exportto>

      <localpath><![CDATA[/home/mywebsite/public_html]]></localpath>

      <ftphost><![CDATA[]]></ftphost>

      <ftpport><![CDATA[]]></ftpport>

      <ftpusername><![CDATA[]]></ftpusername>

      <ftppass><![CDATA[]]></ftppass>

      <ftproot><![CDATA[]]></ftproot>

      <ftpfile><![CDATA[]]></ftpfile>

      <export_email_addresses><![CDATA[]]></export_email_addresses>

      <export_email_addresses_cc><![CDATA[]]></export_email_addresses_cc>

      <export_email_addresses_bcc><![CDATA[]]></export_email_addresses_bcc>

      <export_email_subject><![CDATA[]]></export_email_subject>

      <export_email_body><![CDATA[]]></export_email_body>

      <export_filename><![CDATA[testdenis.csv]]></export_filename>

      <export_file><![CDATA[csv]]></export_file>

      <field_delimiter><![CDATA[,]]></field_delimiter>

      <text_enclosure><![CDATA["]]></text_enclosure>

      <include_column_headers><![CDATA[1]]></include_column_headers>

      <signature><![CDATA[0]]></signature>

      <publish_state><![CDATA[1]]></publish_state>

      <recordstart><![CDATA[]]></recordstart>

      <recordend><![CDATA[]]></recordend>

      <export_date_format><![CDATA[d/m/Y]]></export_date_format>

      <export_price_format_decimal><![CDATA[2]]></export_price_format_decimal>

      <export_price_format_decsep><![CDATA[,]]></export_price_format_decsep>

      <export_price_format_thousep><![CDATA[]]></export_price_format_thousep>

      <header><![CDATA[]]></header>

      <body><![CDATA[]]></body>

      <footer><![CDATA[]]></footer>

      <language><![CDATA[fr-FR]]></language>

      <splitorderline><![CDATA[1]]></splitorderline>

      <ordernostart><![CDATA[5778]]></ordernostart>

      <ordernoend><![CDATA[]]></ordernoend>

      <orderlist><![CDATA[]]></orderlist>

      <orderdaterange><![CDATA[]]></orderdaterange>

      <orderdatestart><![CDATA[]]></orderdatestart>

      <orderdateend><![CDATA[]]></orderdateend>

      <ordermdatestart><![CDATA[]]></ordermdatestart>

      <ordermdateend><![CDATA[]]></ordermdateend>

      <orderstatus>

        <option><![CDATA[S]]></option>

      </orderstatus>

      <orderpayment>

        <option><![CDATA[]]></option>

      </orderpayment>

      <order_address><![CDATA[BT]]></order_address>

      <ordermanufacturer>

        <option><![CDATA[none]]></option>

      </ordermanufacturer>

      <ordercurrency>

        <option><![CDATA[47]]></option>

      </ordercurrency>

      <orderpricestart><![CDATA[]]></orderpricestart>

      <orderpriceend><![CDATA[]]></orderpriceend>

      <orderuser>

        <option><![CDATA[]]></option>

      </orderuser>

      <orderproduct>

        <option><![CDATA[]]></option>

      </orderproduct>

      <groupbyfields><![CDATA[{"name":["invoice_number"]}]]></groupbyfields>

      <sortfields><![CDATA[]]></sortfields>

      <use_system_limits><![CDATA[0]]></use_system_limits>

      <max_execution_time><![CDATA[]]></max_execution_time>

      <memory_limit><![CDATA[]]></memory_limit>

    </settings>

    <template_name><![CDATA[testdenis]]></template_name>

    <advanced><![CDATA[0]]></advanced>

    <action><![CDATA[export]]></action>

    <frontend><![CDATA[0]]></frontend>

    <secret><![CDATA[]]></secret>

    <log><![CDATA[0]]></log>

    <lastrun><![CDATA[2016-02-12 13:59:15]]></lastrun>

    <enabled><![CDATA[1]]></enabled>

    <ordering><![CDATA[0]]></ordering>

    <fields>

      <field>

        <field_name><![CDATA[invoice_number]]></field_name>

        <xml_node><![CDATA[]]></xml_node>

        <column_header><![CDATA[]]></column_header>

        <default_value><![CDATA[]]></default_value>

        <enabled><![CDATA[1]]></enabled>

        <sort><![CDATA[0]]></sort>

        <cdata><![CDATA[1]]></cdata>

        <ordering><![CDATA[1]]></ordering>

      </field>

    </fields>

  </template>

</csvi>

the result is :



"invoice_number"

"1601045IVD02"

"1601045IVD02"

"1601045IVD02"

"1601045IVD02"

"1601045IVD02"

"1601045IVD02"



"1601045YJF01"

"1601045YJF01"

"1601045YJF01"

"1601045YJF01"

"1601045YJF01"

"1601045YJF01"

"1601045YJF01"



"160104JBEH04"

"160104JBEH04"



"160104KPIG03"

it's been days since I tinker export ...
I'm sad

more than a month ago
RO CSVI
# 2

rolandd

Accepted Answer Pending Moderation

Hello,

In that case the invoice_number is not enough for the query. Since you only want one order per line, do the group by on the virtuemart_order_id field. I believe that should get you the desired result.

Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

more than a month ago
RO CSVI
# 3

doc_denis

Accepted Answer Pending Moderation

i added virtuemart_order_id

result on my csv is :



"invoice_number","virtuemart_order_id"

"1601045IVD02","5782"

"1601045IVD02","5782"

"1601045IVD02","5782"

"1601045IVD02","5782"

"1601045IVD02","5782"

"1601045IVD02","5782"



"1601045YJF01","5783"

"1601045YJF01","5783"

"1601045YJF01","5783"

"1601045YJF01","5783"

"1601045YJF01","5783"

"1601045YJF01","5783"

"1601045YJF01","5783"



"160104JBEH04","5779"

"160104JBEH04","5779"



"160104KPIG03","5780"

template :



<?xml version="1.0"?>

<csvi>

  <template>

    <settings>

      <action><![CDATA[export]]></action>

      <component><![CDATA[com_virtuemart]]></component>

      <operation><![CDATA[order]]></operation>

      <exportto>

        <option><![CDATA[todownload]]></option>

      </exportto>

      <localpath><![CDATA[/home/mywebsite/public_html]]></localpath>

      <ftphost><![CDATA[]]></ftphost>

      <ftpport><![CDATA[]]></ftpport>

      <ftpusername><![CDATA[]]></ftpusername>

      <ftppass><![CDATA[]]></ftppass>

      <ftproot><![CDATA[]]></ftproot>

      <ftpfile><![CDATA[]]></ftpfile>

      <export_email_addresses><![CDATA[]]></export_email_addresses>

      <export_email_addresses_cc><![CDATA[]]></export_email_addresses_cc>

      <export_email_addresses_bcc><![CDATA[]]></export_email_addresses_bcc>

      <export_email_subject><![CDATA[]]></export_email_subject>

      <export_email_body><![CDATA[]]></export_email_body>

      <export_filename><![CDATA[testdenis.csv]]></export_filename>

      <export_file><![CDATA[csv]]></export_file>

      <field_delimiter><![CDATA[,]]></field_delimiter>

      <text_enclosure><![CDATA["]]></text_enclosure>

      <include_column_headers><![CDATA[1]]></include_column_headers>

      <signature><![CDATA[0]]></signature>

      <publish_state><![CDATA[1]]></publish_state>

      <recordstart><![CDATA[]]></recordstart>

      <recordend><![CDATA[]]></recordend>

      <export_date_format><![CDATA[d/m/Y]]></export_date_format>

      <export_price_format_decimal><![CDATA[2]]></export_price_format_decimal>

      <export_price_format_decsep><![CDATA[,]]></export_price_format_decsep>

      <export_price_format_thousep><![CDATA[]]></export_price_format_thousep>

      <header><![CDATA[]]></header>

      <body><![CDATA[]]></body>

      <footer><![CDATA[]]></footer>

      <language><![CDATA[fr-FR]]></language>

      <splitorderline><![CDATA[1]]></splitorderline>

      <ordernostart><![CDATA[5778]]></ordernostart>

      <ordernoend><![CDATA[]]></ordernoend>

      <orderlist><![CDATA[]]></orderlist>

      <orderdaterange><![CDATA[]]></orderdaterange>

      <orderdatestart><![CDATA[]]></orderdatestart>

      <orderdateend><![CDATA[]]></orderdateend>

      <ordermdatestart><![CDATA[]]></ordermdatestart>

      <ordermdateend><![CDATA[]]></ordermdateend>

      <orderstatus>

        <option><![CDATA[S]]></option>

      </orderstatus>

      <orderpayment>

        <option><![CDATA[]]></option>

      </orderpayment>

      <order_address><![CDATA[BT]]></order_address>

      <ordermanufacturer>

        <option><![CDATA[none]]></option>

      </ordermanufacturer>

      <ordercurrency>

        <option><![CDATA[47]]></option>

      </ordercurrency>

      <orderpricestart><![CDATA[]]></orderpricestart>

      <orderpriceend><![CDATA[]]></orderpriceend>

      <orderuser>

        <option><![CDATA[]]></option>

      </orderuser>

      <orderproduct>

        <option><![CDATA[]]></option>

      </orderproduct>

      <groupbyfields><![CDATA[{"name":["virtuemart_order_id","invoice_number"]}]]></groupbyfields>

      <sortfields><![CDATA[]]></sortfields>

      <use_system_limits><![CDATA[0]]></use_system_limits>

      <max_execution_time><![CDATA[]]></max_execution_time>

      <memory_limit><![CDATA[]]></memory_limit>

    </settings>

    <template_name><![CDATA[testdenis]]></template_name>

    <advanced><![CDATA[0]]></advanced>

    <action><![CDATA[export]]></action>

    <frontend><![CDATA[0]]></frontend>

    <secret><![CDATA[]]></secret>

    <log><![CDATA[0]]></log>

    <lastrun><![CDATA[2016-02-15 16:32:33]]></lastrun>

    <enabled><![CDATA[1]]></enabled>

    <ordering><![CDATA[0]]></ordering>

    <fields>

      <field>

        <field_name><![CDATA[invoice_number]]></field_name>

        <xml_node><![CDATA[]]></xml_node>

        <column_header><![CDATA[]]></column_header>

        <default_value><![CDATA[]]></default_value>

        <enabled><![CDATA[1]]></enabled>

        <sort><![CDATA[0]]></sort>

        <cdata><![CDATA[1]]></cdata>

        <ordering><![CDATA[2]]></ordering>

      </field>

      <field>

        <field_name><![CDATA[virtuemart_order_id]]></field_name>

        <xml_node><![CDATA[]]></xml_node>

        <column_header><![CDATA[]]></column_header>

        <default_value><![CDATA[]]></default_value>

        <enabled><![CDATA[1]]></enabled>

        <sort><![CDATA[0]]></sort>

        <cdata><![CDATA[1]]></cdata>

        <ordering><![CDATA[1]]></ordering>

      </field>

    </fields>

  </template>

</csvi>

more than a month ago
RO CSVI
# 4

doc_denis

Accepted Answer Pending Moderation

another export "virtuemart_order_id" first and "invoice_number" second



"virtuemart_order_id","invoice_number"

"5779","160104JBEH04"

"5779","160104JBEH04"



"5780","160104KPIG03"



"5782","1601045IVD02"

"5782","1601045IVD02"

"5782","1601045IVD02"

"5782","1601045IVD02"

"5782","1601045IVD02"

"5782","1601045IVD02"



"5783","1601045YJF01"

"5783","1601045YJF01"

"5783","1601045YJF01"

"5783","1601045YJF01"

"5783","1601045YJF01"

"5783","1601045YJF01"

"5783","1601045YJF01"

more than a month ago
RO CSVI
# 5

doc_denis

Accepted Answer Pending Moderation

there to maybe a solution to remove duplicates in the export ?
because the lines are identical on order_id

I seem to have tried all the options, with no possibility of having a line by order_id
for the moment I delete duplicates with open office, but this is not a real solution

more than a month ago
RO CSVI
# 6

tharuna

Accepted Answer Pending Moderation

Hello,
Can you send us the your site login details with database access to check on the export?

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

more than a month ago
RO CSVI
# 7

Page :
1

There are no replies made for this post yet.
Be one of the first to reply to this post!

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

Resolved export order virtuemart3 only one line per orderid

RO CSVI

RO Payments

RO Users

RO Single Sign On