Import CSV convert portfolio code to ID before Import

cpshart

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
Hi

I am using the CSV Import to import share transactions, but I need to pass a code value in the CSV import file for the fieldname portfolio_id and convert its value to its primary id when imported to the MySQL table.

fieldname is portfolio_id
code value "TEST"
primary id "13"

CSV import file

"holdings_id","user_id","transaction_date","transaction_type","portfolio_id","stock_id","quantity","price","total"
"876","1","2020-07-13 00:00:00","Buy","TEST","1336","400.00","2100","4400.00"

client code extract

    selectEditor.on('submitComplete', function (e, json, data, action) {
        var fieldvalue;
        // Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
        editor.create( csv.length, {
            title: 'Confirm import',
            buttons: 'Submit',
            message: 'Click the <i>Submit</i> button to confirm the import of '+csv.length+' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
        } );
 
        for ( var i=0 ; i<fields.length ; i++ ) {
            var field = editor.field( fields[i] );
            var mapped = data[ field.name() ];
            for ( var j=0 ; j<csv.length ; j++ ) {
                switch (i) {
                  case 0:
                    fieldvalue = csv[j]['holdings_id'];
                    break;
                  case 1:
                    fieldvalue = csv[j]['user_id'];
                    break;
                  case 2:
                    fieldvalue = csv[j]['transaction_date'];
                    break;
                  case 3:
                    fieldvalue = csv[j]['transaction_type'];
                    break;
                  case 4:
                    fieldvalue = getIds(csv[j]['portfolio_id']);    
                    console.log('386 portfolio_idval is:',portfolio_idval);
                    console.log('386 fieldvalue is:',fieldvalue);
                    break;
                  case 5:
                    fieldvalue = csv[j]['stock_id'];
                    break;
                  case 6:
                    fieldvalue = csv[j]['quantity'];
                    break;  
                  case 7:
                    fieldvalue = csv[j]['price'];
                    break;  
                  case 8:
                    fieldvalue = csv[j]['total'];
                }               
                    console.log('400 portfolio_idval is:',portfolio_idval);
                    console.log('400 fieldvalue is:',fieldvalue);
                field.multiSet( j, fieldvalue );                
            }
        }       
    } );
}
/**************************************************************************
* call php to get the portfolio_id for a given portfolio code and user_id
***************************************************************************
*/
function getIds(portfolio_code) {
     
    var userid = $('#passuserid').val(); 
    
        $.ajax( {
    url: "../../" + EDITOR_DIR + "/controllers/dview-get_portfolio_id-1.00.php",
            type: "post",
            data: {
                "userid": userid,
                "portfolio_code": portfolio_code
                  },
             
    success: function (data) { 
        if (data) {
    try {
        if ( data.length > 1 ) {
        myJSON = JSON.parse(data);
            let portfolio_idval = myJSON[0].id;  
            console.log('445 portfolio_idval:',portfolio_idval);
        }
    }
             
    catch (error) {
        console.error("Crashing while parsing ");
        throw error;
    }                  
        }
        else if (data.trim()) {
        }
    }
 
    }); 
    return portfolio_idval;
    };

server file

<?php

/* /home/ukincome/public_html/Editor-PHP-2.0.5/controllers/dview-get_portfolio_id-1.00.php
 */
 
// error reporting uncomment next 2 lines
error_reporting(E_ALL);
ini_set('display_errors', '1');
 
 
 /* Loads the WordPress environment and template */
require( '../../wp-blog-header.php' );

global $current_user;
wp_get_current_user();

// DataTables PHP library
include( "../lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions,
    DataTables\Editor\SearchPaneOptions;

$userid = $_POST['userid'];
$portfolio_code = $_POST['portfolio_code'];
$portfolio_array = [];

if ($portfolio_code  <> "") {

try {
    $pdo = new PDO(strtolower($sql_details['type']) . ":host=" . $sql_details['host'] . ";dbname=" . $sql_details['db'], $sql_details['user'], $sql_details['pass']);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//    echo "Connected successfully" . "\n\n"; 
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }

$result = $pdo->query("SELECT id, code FROM dm_portfolios WHERE code = '$portfolio_code' AND user_id = '$userid' LIMIT 1");

        foreach ($result as $row) {
            array_push( $portfolio_array, array( 'id'=>$row['id'], 'code'=>$row['code'] ) );
        };
                
echo json_encode($portfolio_array);
}


<?php
>
```
?>


JSON returned from server script

[{"id":"13","code":"TEST"}]

results 
![](https://datatables.net/forums/uploads/editor/sc/htb2nn5vdmln.png "")

the problem is that the call to the ```function getIds(portfolio_code)``` is not being done until ```selectEditor.on('submitComplete', function (e, json, data, action) {``` 
section of code is completed.

I need the ```fieldvalue``` to be populated by the value returned from the ```getIds function``` sourced from the JSON generated by the server file shown above. 

The ```getIds function``` is not being called until the loop processing has been completed, so the value of the portfolio _idval is not being written to the table ```dm_holdinglines.portfolio_id```

              case 4:
                fieldvalue = getIds(csv[j]['portfolio_id']);    
                console.log('386 portfolio_idval is:',portfolio_idval);
                console.log('386 fieldvalue is:',fieldvalue);
                break;
              case 5:
                fieldvalue = csv[j]['stock_id'];
                break;
              case 6:

etc..
```

any help much appreciated.

this is a reconstruction of a problem I had before the website was mothballed for several months, similar to the call I placed previously here, so I am conscious that any code I have written should not result in the event suggested by Allan in the call below

With a decent size file that's going to DoS your own server!

https://datatables.net/forums/discussion/67056/import-csv-convert-code-to-id-using-table-ajax-json#latest

I can provide access to my website by PM if required.

Many Thanks

Colin

cpshart

Hi

I need to ensure that the ajax process and within the
function getIds(portfolio_code) has completed before continuing with processing in the case statement.

i.e. the variable portfolio_idval is assigned the value of 13

I assume I need a callback({}); after the test for success of the ajax call.

Do I need to use a function like ajax( data, callback, settings ) to complete the ajax processing before continuing in the case statement.

Debug at 445 portfolio_idval: 13 above in the script Console debug shows that currently the processing of the ajax script is done too late in the processing after the case statement has completed.

Thanks Colin

cpshart

Hi

I am struggling to get the value of portfolio_idval back to the case statement when the function getIds is first called here

                  case 4:
                    fieldvalue = getIds(csv[j]['portfolio_id']); 

This is the new getIds function below

my question is how do I get the value of portfolio_idval back to the case statement when the function is first called using the callback below

function getIds(portfolio_code) {
     
    var userid = $('#passuserid').val(); 
    
        ajax: (function ( data, callback, settings ) {
        $.ajax( {
    url: "../../" + EDITOR_DIR + "/controllers/dview-get_portfolio_id-1.00.php",
            type: "post",
            data: {
                "userid": userid,
                "portfolio_code": portfolio_code
                  },
             
    success: function (data) { 
        if (data) {
    try {
        if ( data.length > 1 ) {
        myJSON = JSON.parse(data);
            let portfolio_idval = myJSON[0].id;  
            console.log('445 portfolio_idval:',portfolio_idval);
        }
    }
             
    catch (error) {
        console.error("Crashing while parsing ");
        throw error;
    }                  
        }
        else if (data.trim()) {
        }
        return portfolio_idval;
        callback({data: portfolio_idval});
    }
 
    });
    
    });
    
    };

any help much appreciated, this is becoming a big issue for me now.

many thanks Colin

kthorngren

I don't think you will be able to use case 4 with an asynchronous ajax call. You will need to rearrange your code. It will be difficult to manage individual ajax requests and responses inside a loop. The loop won't wait for the async response to continue. You might be able to do this with promises but that is beyond the scope of this forum :-)

My suggestion is that instead of individually fetching the portfolio_code each loop is to fetch all of them before the for ( var i=0 ; i<fields.length ; i++ ) loop and place them in an abject data structure that you can access the object in the case 4. All of the ``for ( var i=0 ; i<fields.length ; i++ ) code will then be placed in the success function of the ajax request. Plus its more efficient to have one ajax request. If you want to limit the returned data then you can pass all the portfolio_code values in the table as an array in the ajax request.

Kevin

cpshart

Hi Kevin

Thanks for the help, I totally understand your points, lack of efficiency of code and the revised approach makes sense.

I will give your suggestion a try and get back with the result.

Best regards

Colin

cpshart

Hi Kevin

I have written the code based upon your suggestion as shown below, but the problem is that the function getPortfolioId[csv[j]['portfolio_id']]; is not being called from within the case statement

store portfolios from table in an array, this works as expected

// call php to get the portfolio_id for a given portfolio code and user_id  
function storePortfolioIds() {
     
    var userid = $('#passuserid').val(); 
    
        $.ajax( {
    url: "../../" + EDITOR_DIR + "/controllers/dview-get_portfolio_id-1.02.php",
            type: "post",
            data: { "userid": userid },
             
    success: function (data) { 
        if (data) {
    try {
        console.log('data.length:',data.length);
        if ( data.length > 1 ) {
        myJSON = JSON.parse(data);
        console.log('354 myJSON:',myJSON);
        for ( var s=0 ; s<data.length ; s++ ) { 
            portfolio_array.push([myJSON[s].id, myJSON[s].code]);
            console.log('356 myJSON[s].id:',myJSON[s].id,'code:',myJSON[s].code);
        }
    }
    }     
    catch (error) {
        console.error("Crashing while parsing ");
        throw error;
    }                 
        }
        else if (data.trim()) {
        }
        return portfolio_array;
    }
 
    });
        
    };
// call php to get the portfolio_id for a given portfolio code and user_id  

// call php to get the portfolio_id for a given portfolio code and user_id  
storePortfolioIds();

get the portfolio_array.id by matching portfolio_code with portfolio_array values

function getPortfolioId(portfolio_code) {
        for (var i=0; i < portfolio_array.length; i++) {    
            console.log('338, id, code:',portfolio_array[i].id,portfolio_array[i].code);
        if (portfolio_array[i].code === portfolio_code) {
            console.log('339, portfolio_array[i].id:',portfolio_array[i].id);
            return portfolio_array[i].id;
        }
    }   
}

call getPortfolioId[csv[j]['portfolio_id']]; and store value returned in fieldValue

                  case 4:
                    fieldValue = getPortfolioId[csv[j]['portfolio_id']];
                    console.log('450, fieldValue:',fieldValue); 
                    break;

any ideas much appreciated.

many thanks Colin

kthorngren

You need to use parenthesis not square brackets when calling a function. Should look more like this:

fieldValue = getPortfolioId( csv[j]['portfolio_id'] );

Kevin

cpshart

Hi Kevin

Oops, not sure why I didn't spot that !!, thanks for your quick response

many thanks Colin