Resolved Export and import on Joined Tables for Joomla Users or Custom Tables tabels

Hello,

1. Is it possible to import data from one file into several tables?
These tables might be a) Joomla User Table and user fields (4 groups) included or

We do have import operation to import into Joomla users table with user custom fields.

b) Custom Tables tables (with 4 different joined tables)

Our product can import only to single table using Custom table import. Import to multiple joined tables is not supported yet.

2. Is it possible to export data from several (joined) tables like explained in situation 1a and/or 1b into a XML (txt) file with extra xml-coded lines between the different records from the leading table and fields?

Export from joined tables as XML file is possible with RO CSVI Advanced custom table export. But i don't understand the extra XML code between lines, can you give an example? What type of values are you trying to include? Are they going to be hardcoded?

I need to build XML files for other applications.
This is done by exporting the user data and for each user several lines hard-coded xml code.

To give an example:
2 hardcoded XML headerlines:
Hardcoded XML line A
Hardcoded XML line B
For each exported user 3 hardcoded XML headerlines and several combined lines:
Hardcoded XML line 1
Hardcoded XML line 2
Hardcoded XML line 3
Export line1 from user table with extra information (say 'fixed textphrase' with name)
Export line2 from user-field table with extra information (say 'fixed textphrase' with field-value1)
Export line3 from user-field table with extra information (say 'fixed textphrase' with field-value2)

These lines may change in different situations.

Hope this clarifies all for you.
If not, dont hesitate to reply and ask for more information.

Hello,
Adding hardcoded XML headerlines will not be an issue, that can be done in the template itself but there is no direct way to add hardcoded XML lines for each record. It needs little of custom coding in RO CSVI files. You can use our demo site to create a sample export using RO CSVI Advanced custom table export to see the values you get in export XML.

This is done by exporting the user data and for each user several lines hard-coded xml code.

By user data you mean Joomla users with user custom fields? Are you expecting to link more tables with Joomla user table in this export? I am asking this because we have Joomla Users export which can get you values from users table and user custom fields. This export does not link other custom tables.

What I need is the following:
1. Import users with self-defined (not Joomla defined) user fields in three extra user groups (groups are extra-group-1 2 and 3) directly from csv
2. Export users with some fields from the user table and several fields from one or more of the extra user groups along with the extra XML lines in the header as well as for each user.

If both can be done with RO CSVI I am very happy.
If this is not possible, then I can tell you that this feature would be very beneficial, because many website developers are eager to have this option in there website.

If you are interested and need more information about this, then please let me know.

Kind regards,
Winfried

Hello Winfried,

These self-defined fields, where are they located? In a separate table or the Joomla users table? Is there a reason why you don't want to use Joomla custom fields for these self-defined fields?

and several fields from one or more of the extra user groups

This part I am not understanding I am afraid. A user is assigned to 1 or more usergroups, we can export these usergroup names but where do the fields come in?

Do you have a data example perhaps? As in which table has what value that you would like to use.

Hi,

Maybe I have been not entirely clear in this topic.

Joomla has users and users can:
a) be assigned to one or more usergroups
b) have additional user fields which can be devided in several groups (for instance finance attributes and location attributes

In the discussion, when I mention groups I am refering to situation b)

Hope this makes it more sensible to you.

Meanwhile, I will try to test some cases on your demo site to see if I can make it work what I need.

It seems I cant do the tests I need to do, because the demo site doesnt have (for me) proper situations.

Hello,

b) have additional user fields which can be devided in several groups (for instance finance attributes and location attributes

In the discussion, when I mention groups I am refering to situation b)

The field groups you mention here is the Joomla User custom field groups, correct?. See attached screenshot. Is that how your field groups are set?

It seems I cant do the tests I need to do, because the demo site doesnt have (for me) proper situations.

What is the issue you are having with demo site? Please understand that it is a demo site and does not have any predefined data but just some basic records for testing. You may need to create sample data to test the import and export according to your needs.

I will explain the exact situation I need here.

In the Nethrelands it is possible to use direct debit authorization for getting payed in a transaction file/
This file is in a XML-format and has a predefined layout.

To build this XML file I need to use both variable fields, which are in the user-defined user fields, and fixed text phrases which should be in the template what builds the XML file.

Hereafter is the explanation on hpow to build this XML file:
========================================================================================
#SEPA SDD (Sepa Direct Debit) 2.0

<table>
<tr><td>Author:</td><td>Congressus, The Netherlands</td></tr>
<tr><td>Date:</td><td>14-05-2013</td></tr>
<tr><td>Description</td><td>A PHP class to create Sepa Direct Debit XML Files</td></tr>
</table>
##1. INSTALLATION

SEPA SDD requires PHP 5, no other libraries are required.

To install, copy the SEPASDD.php class to a folder on the webserver and include it as follows:

```php
require_once([path_from_webroot_to_folder]/SEPASDD.php);
```

##2. CONFIGURATION

SEPA SSD requires a config array, which is validated on initiation.
The following parameters are required:

- name: The name of the creditor('s organization).
- IBAN: The creditor's International Bank Account Number.
- BIC: The creditor's Bank Identification Code. (optional)
- batch: Whether to process as batch or as individual transactions .
Allowed: true or false (MUST be boolean).
- creditor_id: The creditor's id, contact your bank if you do not know this.
- currency: The currency in which the amounts are defined.
Allowed: ISO 4217.
- version: The PAIN.008.001 version. (optional)
Allowed: 3

#####Note:
When setting batch to true, SEPASDD will create a batch for each Direct Debit
transaction type ("FRST","RCUR", etc). and required collection date. This
means that all "FRST" transactions with collection date 2014-01-30 will be
grouped in a batch.

####Example:

```php
$config = array("name" => "Test",
"IBAN" => "NL50BANK1234567890",
"BIC" => "BANKNL2A",
"batch" => true,
"creditor_id" => "00000",
"currency" => "EUR"
);
```

##3. USAGE

###3.1 Initialization

Create an instance of the class with an configuration as such:

```php
try{
$SEPASDD = new SEPASDD($config);
}catch(Exception $e){
echo $e->getMessage();
}
```

###3.2 Create a payment

SEPA SDD uses the addPayment method for creating payments, it requires a payment array.
The following parameters are required:

- name: The debtors name.
- IBAN: The debtor's International Bank Account Number.
- BIC: The debtor's Bank Identification Code. (optional)
- amount: The amount to transfer from debtor to creditor (IN CENTS).
Allowed: int/string (NO SEPARATORS) e.g. EUR 10.00 has to be entered as 1000 or "1000"
- type: The type of Direct Debit Transaction
Allowed: FRST (First), RCUR (Recurring), OOFF (One Off), FNAL (Final)
- collection_date: The date at which the amount should be collected from the debtor.
Allowed: ISO 8601 (YYYY-MM-DD). This date should be in the future, how far in
the future is dependent on the type of Direct Debit. See the definition.
- mandate_id: The ID of the written mandate from the debtor.
- mandate_date: The date the mandate was signed.
Allowed: ISO 8601 (YYYY-MM-DD). For mandates before SEPA requirements this is: 2009-11-01.
- description: The description of the transaction.

####Example:

```php
$payment = array("name" => "Test von Testenstein",
"IBAN" => "NL50BANK1234567890",
"BIC" => "BANKNL2A",
"amount" => "1000",
"type" => "FRST",
"collection_date" => "2013-07-12",
"mandate_id" => "1234",
"mandate_date" => "2009-11-01",
"description" => "Test Transaction"
);
```

Then use the addPayment method to add the payment to the file:

####Example:

```php
try{
$SEPASDD->addPayment($payment);
}catch(Exception $e){
echo $e->getMessage();
}
```

You can use this method multiple times to add more payments.

###3.3 Save the file

To save the file, use the "save" method, this will return the XML as a string.
If you want to save to file, you have to do this yourself.

####Example:

```php
try{
$SEPASDD->save();
}catch(Exception $e){
echo $e->getMessage();
}
```

After this, please reinitialize the class if you want to create another file.

###3.4 Adding custom fields

SEPA SDD has a special method for adding custom fields. This method is called addCustomNode.
The required arguments are:

- parent_XPATH: The XPATH selector of the parent.
- name: The node/tag name.
- value: Its value, default "".
- attr: An array containing key => value pairs defining the attributes.

####Example:

Add the postal address for a Debtor

```php
try{
$SEPASDD->addCustomNode("//Document/CstmrDrctDbtInitn/PmtInf[last()]/DrctDbtTxInf[last()]/Dbtr","PstlAdr";
$SEPASDD->addCustomNode("//Document/CstmrDrctDbtInitn/PmtInf[last()]/DrctDbtTxInf[last()]/Dbtr/PstlAdr","AdrLine","Rode Weeshuisstraat 25";
$SEPASDD->addCustomNode("//Document/CstmrDrctDbtInitn/PmtInf[last()]/DrctDbtTxInf[last()]/Dbtr/PstlAdr","AdrLine","9712 ET Groningen";
}catch(Exception $e){
echo $e->getMessage();
}
```

Will create

```xml
<PstlAdr>
<AdrLine>Rode Weeshuisstraat 25</AdrLine>
<AdrLine>9712 ET Groningen</AdrLine>
</PstlAdr>
```

###3.5 Validation

To validate against the pain.008.001.02 schema definition, you can
use the validate() method. This requires the XML as string as argument.

#####Note:
Some banks allow not specifying a BIC, and so does SEPASDD. However it is not
schema compliant and the schema WILL NOT validate.

####Example

```php
try{
$xml = $SEPASDD->save();
$validation = $SEPASDD->validate($xml);
print_r($validation);
}catch(Exception $e){
echo $e->getMessage();
}
```

##4 LICENSE

MIT LICENSE

Copyright (c) 2013 Congressus, The Netherlands

Permission is hereby granted, free of charge, to any person
obtaining a copy of this software and associated documentation
files (the "Software", to deal in the Software without
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the
Software is furnished to do so, subject to the following
conditions:

The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
OTHER DEALINGS IN THE SOFTWARE.

========================================================================================

Can you tell me if this is enough information for you to see of this is possible.
This method of payment is widely used in the Netherlands and maybe other countries too.
If you manage this to make it work it wil definitely be a bonus for you to have this option in your component.

Hello Winfried,

Reading back this discussion I think I understand what you are asking. Let me explain how I understand it.

You want to run a user export of user data and the custom fields assigned to those users. Those custom fields are separated into several groups. In the case of RO CSVI, the groups are irrelevant as we just look at the fields. The groups are just a visual representation of the fields.

Attached I have a screenshot of how you would build an XML. The header and footer are fixed data, you can put the hardcoded lines there. The body part is where we do replace placeholders with actual values. As you can see in the screenshot I added 2 custom fields there, test and test2. These would be your custom fields of course in your template. Running a user export will output this:

<?xml version="1.0" encoding="utf8"?>

<users>

<user>

    <groupID>3</groupID>

    <email>user1@user.com</email>

    <test><![CDATA[User 1]]></test>

    <test2><![CDATA[Somewhere]]></test2>

</user>

</users>

First is a hard coded line, second t is the email and third and fourth are my user custom fields.

As for your import, you can also import these custom fields as well. Each custom field is a single field in RO CSVI. Assuming you are not using any special 3rd party extension for your custom fields, this will work. If you are using a 3rd party extension, we need to see if this is supported.

Does this answer your questions?

Will this export all users in one XML file?

Hello,
Yes, RO CSVI exports all users in one XML file.