Import CSV convert portfolio code to ID before Import

Import CSV convert portfolio code to ID before Import

cpshartcpshart Posts: 246Questions: 49Answers: 5

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);
}

?>

JSON returned from server script

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

results

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

This question has accepted answers - jump to:

Answers

  • cpshartcpshart Posts: 246Questions: 49Answers: 5

    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

  • cpshartcpshart Posts: 246Questions: 49Answers: 5

    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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    edited November 2021 Answer ✓

    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

  • cpshartcpshart Posts: 246Questions: 49Answers: 5

    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

  • cpshartcpshart Posts: 246Questions: 49Answers: 5

    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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    Answer ✓

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

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

    Kevin

  • cpshartcpshart Posts: 246Questions: 49Answers: 5

    Hi Kevin

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

    many thanks Colin

Sign In or Register to comment.