Importing 3rd party content. Where to Start?

[jeffsydor]

I received a request to pull in data from another location (intranet specifically) and use that to update content within the site in a few places:

1. A sorting method to filter a list (front end)
2. A TV listbox (multi select) (back end)

Currently, this is a manually created & updated list using MIGX in the back end. Ideally, I’d like to make sure that list is updated regularly from the external source. Then, I’d like to pull them in to individual resources from an external source too.

For example: Say I have 500 people who list what schools they’ve graduated from in their biographies. Rather than manually keeping this ever-changing list updated manually, I’d like to pull this from another source (via a feed or something) to keep our main site synced with our intranet. Then, i’d smilarly pull into each person’s resource page that list of schools they specifically attended.

If this is possible, what would be the best way to go about this?

thanks!

[bobray]

I think you’re looking at one or more custom snippets that gather the data and update things, possibly with a cron job that runs the job at a set interval.

The method for gathering the data will depend on how it’s currently kept on the external source. It it’s in HTML, this and/or this could be helpful.

[jeffsydor]

Is it possible to pull this content into the database from a CSV file?

[jeffsydor]

So it looks like i may have to use CSV files. So suppose that our 3rd party system spits out csv files and uses FTP to add them into a directory. It looks like you can read CSVs into a mySQL database with something like this: https://phppot.com/php/import-csv-file-into-mysql-using-php/.

Let’s say also that this would update employee biographies, which are populated by many TVs. How would I even start to read a directory of CSV files into the DB, create new resources if that user ID didn’t already exist, modify one if it already did exist and publish them all automatically?

Thanks!

[halftrainedharry]

Take a look at xPDO. In MODx you can relatively easy create/manipulate resources.

$r = $modx->newObject('modResource');
$r->set('parent', $parent);
$r->set('pagetitle', $pagetitle);
$r->setContent($content); 
...
$r->setTVValue('myTV', $tv_value); //setting a TV
$r->save();

You would need a unique identier for the employees and store that somewhere in the resource, so that you can query first, if a resource for this employee is already available.

[jeffsydor]

Hmm that makes some sense to me. So yeah each person would have an employee ID in there already. Like mine could be jsydor1 and my brother Jon could be jsydor2. We already have that as a TV called ‘employeeID’.

So could I query all employees with PDOresources or something to get an array of currently used employeeIDs and then reference that to see if it already existed?

[[pdoresources? 
   &parents=`xxx` 
   &tpl=`@INLINE [[+tv.employeeID]], ` 
   &includeTVs=`employeeID`
]]

[halftrainedharry]

You probably could use pdoResources or you could do the query yourself (with xPDO):

$query = $modx->newQuery('modTemplateVarResource', array('value' => $employee_id,'tmplvarid' => $id_of_tv_with_employee_id));
$query->select('contentid'); 
$resource_id = $modx->getValue($query->prepare());
if (!empty($resource_id)){
    //resource already exists
    $r = $modx->getObject('modResource', $resource_id);
} else {
    //create a new resource
    $r = $modx->newObject('modResource');
}

[bobray]

If you use pdoResources, you’ll get back one big chunk of text that you would have to parse. I think using xPDO would be more appropriate.

[jeffsydor]

so how would I modify this to create a new migx list item instead of a resource? Is it something different from ‘modTemplateVarResource’?

Is there documentation on this sort of thing?

[halftrainedharry]

MIGx stores the data as a JSON-string. If you want to create the value of a MIGx TV by code, you have to exactly copy the structure. Check an existing value in the database table modx_site_tmplvar_contentvalues to see how MIGx stores it. Then recreate the structure in PHP with (associative) arrays and call $modx->toJSON($array) before you save it.

[jeffsydor]

Sorry, I’m not even to that part yet. I’m just talking about adding to the list of options. Normally, when done manually, I’d just go to lists > school > add new.

Then, type name in and click done.

Just like when creating a new resource, it then adds this to the custom table with a new id, md5 value and the name I set.

I just want to do this first before linking it to a resource via a TV.

[halftrainedharry]

OK, if we are talking about MIGXdb and a custom data base table, then that makes it a lot simpler.
First you have to load the package:

$base_path = $modx->getOption('core_path').'components/my_packagename/';
$modx->addPackage('my_packagename', $base_path.'model/');

Then you can add new schools or update them similar to modResources

$new_school = $modx->newObject('myClassName');
$new_school->set('name', $name);
$new_school->save();

Change the names of my_packagename and myClassName according to your schema.

[halftrainedharry]

@jeffsydor

Is there documentation on this sort of thing?

https://docs.modx.com/current/en/extending-modx/xpdo

[jeffsydor]

So here’s what I tried and it clearly didn’t work. Hopefully this was a rookie mistake lol.

I added a sample CSV file to a directory we’ll call /exports/lists/ and the file is called export-schools.csv.

The contents of the file simply has two unused slots and a third for the name:
,,"someplace"

I figured making this a plugin that fires from the system event OnCacheUpdate would be easiest to trigger.

This is the code I used:

<?php
$csvPath = "./exports/lists/export-schools.csv";
$CSVfp = fopen($csvPath, "r");

if ( $CSVfp !== FALSE ) {
	while ( !feof( $CSVfp ) ) {
		$data = fgetcsv( $CSVfp, 1000, "," );
		$name = $data[2];
	}
}

fclose( $CSVfp );

$base_path = $modx->getOption('core_path').'components/mypackage/';
$modx->addPackage('mypackage', $base_path.'model/');
        
$newCourt = $modx->newObject('myClass');
$newCourt->set('name', $name);
$newCourt->save();

Now when i save the plugin, it clears the cache and i check my table. A new row was added, but the content of that row is blank. I tested a similar snippet to be read live on a page to make sure I’m grabbing the correct data and it returns as expected. I’m not sure why it’s not adding the content into the table.

thanks!

[halftrainedharry]

@jeffsydor

I figured making this a plugin that fires from the system event OnCacheUpdate would be easiest to trigger.

Wouldn’t it be just easier to create a snippet with your code, then create a new resource with an empty template and put a call to this snippet [[!my_snippet]] into the content and click “View” to run it.

Does your schema contain a field with the key name?

<object class="myClass" table="myclass" extends="xPDOSimpleObject">
    <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
    ....
</object>

Maybe you could just hardcode the values to test if you can add a new row with some data (and ignore the file reading part until this works).

$newCourt = $modx->newObject('myClass');
$newCourt->set('name', 'test value');
$newCourt->save();

[bobray]

Not sure it will make a difference, but you might try this since " is the default enclosure character:

"","","someplace"

[jeffsydor]

So I got this to work, but only when it’s a snippet on a page and I view the page. Is there a way to tell the system to just run this snippet rather than doing a manual page view?

The CSV file contains:
,,"new item"

$base_path = $modx->getOption('core_path').'components/myPackage/';
$modx->addPackage('myPackage', $base_path.'model/');

$csvPath = "./path/to/list/export-schools.csv";
$CSVfp = fopen( $csvPath, "r" );
if ( $CSVfp !== FALSE ) :
	while ( !feof( $CSVfp ) ) :
		$data = fgetcsv( $CSVfp, 0, "," );
        $newCourt = $modx->newObject('myClassname');
        $newCourt->set('name', $data[2]);
        $newCourt->set('value', md5($data[2]));
        $newCourt->save();
	endwhile;
endif;
fclose( $CSVfp );

$clearfile = fopen($csvPath, "w");
$txt = "";
fwrite($clearfile, $txt);

fclose($clearfile);

[bobray]

There used to be a way to execute a snippet directly when editing it, but it no longer exists.

You could put a button in the MODX top menu that executes a file containing the code, but you’d have to set up a namespace for it and locate the file correctly.

It’s usually easier just to visit the page, which I think could also be done with a button in the menu. To do that, create a new menu item. Set the namespace to core, the action to 0, and the handler to something like this:

window.open("http://rtfm.modx.com/");

Replacing the URL with the URL of the page containing your snippet.

If you try that, let me know if it works.

[halftrainedharry]

@jeffsydor

So I got this to work, but only when it’s a snippet on a page and I view the page.

Not sure why this would only work in a snippet. Maybe you shouldn’t use a relative path:

$csvPath = "./path/to/list/export-schools.csv";

Try using $modx->getOption('base_path') (or 'core_path') to create the path to your import file.

[halftrainedharry]

@jeffsydor

Is there a way to tell the system to just run this snippet rather than doing a manual page view?

As you are using MIGXdb, you could add a custom actionbutton to your migx-configuration to run your import code:

You would have to create a new file core/components/mypackage/migxconfigs/grid/grid.config.inc.php to add an actionbutton and make an ajax-request:

<?php
$gridfunctions['this.importmydata'] = "
importmydata: function() {
	MODx.Ajax.request({
		url: this.config.url
		,params: {
			action: 'mgr/migxdb/process'
			,processaction: 'handlemydataimport'
			,configs: this.config.configs
		}
		,listeners: {
			'success': {fn:this.refresh,scope:this}
		}
	});
}
";

$gridactionbuttons['importmydata']['text'] = "'Import my data'";
$gridactionbuttons['importmydata']['handler'] = 'this.importmydata';
$gridactionbuttons['importmydata']['scope'] = 'this';
$gridactionbuttons['importmydata']['standalone'] = '1';

And another file core/components/mypackage/processors/mgr/default/handlemydataimport.php to handle the ajax-request. (The name must correspond with the parameter processaction of the call.)

<?php
// Put your import code from the snippet here or call $modx->runSnippet(...)
return $modx->error->success();

Then select the new checkbox importmydata in the tab “Actionbuttons” of your migx-configuration.