Creating a multidimensional object for XML export

Question

I am importing a csv file which has a lot of invoice data. This data needs to be grouped together based on Vendor ID to display a heading for each Vendor with a sum of all invoices for that Vendor Id; and also then display each individual invoice below it. The goal of this project is to import the CSV, display the data to the user, allow for certain values to be changed via select boxes, and then at a click of a button export to XML. The XML is structured in a similar way as the HTML where by each group of invoices has certain common data displayed at first and then the remittence data is below. To achieve this, I am trying to structure the Object in a way to help me convert it properly to XML using this http://goessner.net/download/prj/jsonxml/ .

Problem: The second two payments need to be grouped together and I would like an array to be created for each row of detailed data and then be added to the main object into the PmtDetail attribute. At the moment this code only ads the first row and ignores the rest.

CSV Snippet (I am not including all the rows that are being used in the code here)

 ID         CardCode   payment_sum    amount     
 1610165    BENV5271    100            100 
 1609026    BENV5635    509.85         287.33
 1609025    BENV5635    509.85         222.52

JSON

 [{"DocNum":"1610165","CardCode":"BENV5271","InvPayAmnt":"100.00","PmntDate":"2012-03-29","payment_sum":"100.00"},
 {"DocNum":"1609026","CardCode":"BENV5635","InvPayAmnt":"287.33","PmntDate":"2012-03-29","payment_sum":"509.85"},  
 {"DocNum":"1609025","CardCode":"BENV5635","InvPayAmnt":"222.52","PmntDate":"2012-03-29","payment_sum":"509.85"}]

Jquery

 $(document).ready(function() {
    $.getJSON('CSV.php', function(data) {

    var prevCardCode = '';
    var newDiv; var NewDiv2;
var PaymentFormat;

    $.each(data, function(index, element) { //looping once to display header info such as sum
        XMLObject = []; // Creating a new object for each row     

        if (element['CardCode'] != prevCardCode) {

            XMLObject.Vendor_Sum = element['payment_sum'];
            XMLObject.Name1 = element['CardName'];
            XMLObject.Addr1 = element['Address'];

            console.log(XMLObject);

            newDiv = $('<div/>').addClass('row').appendTo('#showdata');
            $('<div class="sum_field">' + 'Total: ' + element['payment_sum'] + '</div>').appendTo(newDiv);

                $('<div class="options">Payment Format: <select name="Payment_Format" id="Payment_Format"><option value="CTX" selected="selected">Company to Company</option><option value="PPD">Company to Person</option></select> </div><div id="Selected_Format"></div>').appendTo(newDiv);

                XMLObject.paymentFormat = $('select#Payment_Format').val();;

                $('select#Payment_Format').change(function(){
                    PaymentFormat = $(this).val();
                    XMLObject.paymentFormat = PaymentFormat;
                });

        }

        newDiv2 = $('<div/>').addClass('sub_row').appendTo(newDiv);

        prevCardCode = element['CardCode'];

        $.each(element, function(key, value) { looping 2nd time to display the detail info
            XMLObjectDetail = {};   // Creating an array for each row of detail info

            XMLObjectDetail['TotalCurAmount'] = element['InvPayAmnt']; 
            XMLObjectDetail['NetCurAmount'] = element['InvPayAmnt'];

                   $('<div class="field">' + value + '</div>').appendTo(newDiv2);
                   XMLObject.PmtDetail =  XMLObjectDetail;
             });      
         });
     });
  });

PHP

 <?php 
  if (($handle = fopen('upload/BEN-new.csv', "r")) === FALSE) {
    die('Error opening file'); 
 }

 $headers = fgetcsv($handle, 1024, ',');
 $complete = array();

while ($row = fgetcsv($handle, 1024, ",")) {
       $complete[] = array_combine($headers, $row);
}
    fclose($handle);

    echo json_encode($complete);

?>

Your element array of objects is exactly the same as the representation you have at the top... I don't understand what you want to change about it. — user400654
– user400654, Commented May 31, 2012 at 14:37
console.log the whole array, or console.log each element within the $.each using console.log(key,value) i don't think you need $.extend — user400654
– user400654, Commented May 31, 2012 at 14:38

Bergi · Accepted Answer · 2012-06-04 18:34:52Z

1

Some thoughts:

$.each(data, ... //looping once to display header info such as sum You're not creating a sum anywhere. Also, this is the one and only loop that iterates over all data rows.
XMLObject = []; // Creating a new object for each row - No, you're creating an Array. You really should create an Object ({}), because you use it as an object.
XMLObject =.... You're lacking a var keyword here. You create a new object each iteration, but assign every of them to the same global variable. This is the reason the the #Payment_Format change handler will only change the format of the last created object - the one currently referenced by "XMLObject".
$('...<select name="Payment_Format" id="Payment_Format">...') You are creating a select with an id each iteration. How do you think that id will be unique? That's also why ('select#Payment_Format') will select not the element created in the current iteration.
$.each(element... looping 2nd time to display the detail info No. This is not a second loop, but a loop which will iterate over the properties of the current element - the loop will be applied on each of the iterated data-rows.
XMLObjectDetail = {}; // Creating an array for each row of detail info - No, you're creating an object. Again, you're missing the var keyword.
(edit) XMLObject.PmtDetail = XMLObjectDetail; - you overwrite the "PmtDetail" property each iteration of elements in here. Shouldn't that be an array you append to?
At the end of the loop code you have a XMLObject, containing data about the current row. Don't you want to do something with it, for example push it onto an array of row-objects?

OK, I think now I'm getting what you try to achieve. An appropriate structure might be:

[ {
    "CardCode":"BENV5271"
    "details": [ {
        "DocNum": "1610165",
        "InvPayAmnt": "100.00",
        "PmntDate": "2012-03-29"
      } ],
     "payment_sum": "100.00"
   }, {
     "CardCode": "BENV5635",
     "details": [ {
         "DocNum": "1609026"  
         "InvPayAmnt": "287.33",
         "PmntDate": "2012-03-29"
       }, {
         "DocNum": "1609025",
         "InvPayAmnt": "222.52",
         "PmntDate": "2012-03-29"
       } ],
     "payment_sum": "509.85"
    } ]

or the same, just as an Object (key-value-map) by id:

{
    "BENV5271" : {
        "details": {
            "1610165": {
                 "InvPayAmnt": "100.00",
                 "PmntDate": "2012-03-29"
             }
         }
         "payment_sum": "100.00"
     },
     "BENV5635": {
         "details": {
             "1609026": {
                 "InvPayAmnt": "287.33",
                 "PmntDate": "2012-03-29"
             },
             "1609025": {
                 "InvPayAmnt": "222.52",
                 "PmntDate": "2012-03-29"
             }
        },
        "payment_sum": "509.85"
    }
}

You also might use the number type of JSON for number values, instead of string :-) You should create that structure right away from your database. As you have seen, CSV is not the best representation for it, and as JSON and XML are capable of representing it you should not go SQL->CSV->JSON(table)->JSON(structured)->XML but SQL->JSON(structured)->XML or even better right away SQL->XML. You can easily read XML with Ajax and change its DOM in JavaScript.

If you really need to do the restructuring from malformed Objects to a nice structure in JS, this would be the code:

var cards = {}; // an object indexed by the CardCodes;
for (var i=0; i<data.length; i++) { // loop through the received JSON
    // data[i] equals the "element" variable from your code
    var code = data[i].CardCode;
    if (! code in cards) { // there is no card with that code
        cards[code] = {
            vendorSum: data[i].payment_sum,
            name: data[i].CardName,
            addr: data[i].Address,
            ...
            pmtDetail: [] // create array for the details
        };
    } // else: element already created
    cards[code].pmtDetail.push({
        docNum: data[i].DocNum, 
        amount: data[i].InvPayAmnt
        ...
    }); // adds a new detail object to the array
}

edited Jun 4, 2012 at 18:34

answered Jun 4, 2012 at 17:36

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

MG1 Over a year ago

The sum is not being calculated by the javascript, I wanted to do that part in the SQL, therefore each row has a sum already. The SQL is calculating the SUM by grouping invoices based on Vendor ID.

Bergi Over a year ago

OK, then you will have to watch out that the sums are correctly transferred in your JSON structure, they should not be part of the "rows" array.

MG1 Over a year ago

So that's probably the major mistake here, I don't know how to structure the JSON to reflect this and therefore am trying to do it later with the objects. Hence assigning the XMLObjectDetail array to XMLObject.PmtDetail.

MG1 Over a year ago

And, the second loop is only adding one array to the main object while in this example there should be 2.

Bergi Over a year ago

As I mentioned far below, you should not do that restructuring in JavaScript. It is easily possible, especially the second one, but would better be done in the generating PHP.

|

MG1 · Accepted Answer · 2012-06-06 16:31:40Z

0

I structured the CVS with object/arrays using php:

 while ($row = fgetcsv($handle, 1024, ",")) {
               $cardCodes[] = array_combine($headers, $row);
        }

            foreach ($cardCodes as $key => $value) {

                $payments[$value['ggg']]['aaa']['xxx'] = 'element['gggg']';
            }
            echo json_encode($payments);

Then read this data with JS.

answered Jun 6, 2012 at 16:31

MG1

1,7079 gold badges33 silver badges49 bronze badges

Collectives™ on Stack Overflow

Creating a multidimensional object for XML export

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related