Solved

jQuery manipulations for calcs

Posted on 2014-02-20
14
421 Views
Last Modified: 2014-02-21
Hi, I am doing a report in Oracle APEX that needs to show the difference between "This YTD" figures (Parts,Labor,Rental,Project) and "Last YTD" figures in a percentage.
So, I was going to add a row showing this calculation.

Here is some sample html for the table.
<tbody><tr><th colspan="7" class="apexir_REPEAT_HEADING" id="BREAK_PHASE_1">Timeframe : <span class="apex_break_headers">MTD</span></th></tr>
<tr><th id="SP_NAME"><div id="apexir_SP_NAME" onclick="gReport.controls.widget(this.id)" style="text-align:left;">Salesperson Name</div></th><th id="SALES_NAME"><div id="apexir_SALES_NAME" onclick="gReport.controls.widget(this.id)" style="text-align:left;">Customer Name</div></th><th id="PARTS"><div id="apexir_PARTS" onclick="gReport.controls.widget(this.id)" style="text-align:right;">PARTS</div></th><th id="LABOR"><div id="apexir_LABOR" onclick="gReport.controls.widget(this.id)" style="text-align:right;">Labor</div></th><th id="RENTAL"><div id="apexir_RENTAL" onclick="gReport.controls.widget(this.id)" style="text-align:right;">RENTAL</div></th><th id="PROJECT"><div id="apexir_PROJECT" onclick="gReport.controls.widget(this.id)" style="text-align:right;">PROJECT</div></th></tr>
<tr class="even"><td align="left" headers="SP_NAME BREAK_PHASE_1">Johnson, Troy</td><td align="left" headers="SALES_NAME BREAK_PHASE_1">CLARK CONSTRUCTION</td><td align="right" headers="PARTS BREAK_PHASE_1">$0.00</td><td align="right" headers="LABOR BREAK_PHASE_1">$0.00</td><td align="right" headers="RENTAL BREAK_PHASE_1">$0.00</td><td align="right" headers="PROJECT BREAK_PHASE_1">$0.00</td></tr>
<tr class="odd"><td align="left" headers="SP_NAME BREAK_PHASE_1">Doe, Martha</td><td align="left" headers="SALES_NAME BREAK_PHASE_1">CLARK CONSTRUCTION</td><td align="right" headers="PARTS BREAK_PHASE_1">$0.00</td><td align="right" headers="LABOR BREAK_PHASE_1">$0.00</td><td align="right" headers="RENTAL BREAK_PHASE_1">$0.00</td><td align="right" headers="PROJECT BREAK_PHASE_1">$0.00</td></tr>
<tr class="even"><td align="left" headers="SP_NAME BREAK_PHASE_1">Doe, Martha</td><td align="left" headers="SALES_NAME BREAK_PHASE_1">BIG DADDYS</td><td align="right" headers="PARTS BREAK_PHASE_1">$655.00</td><td align="right" headers="LABOR BREAK_PHASE_1">$0.00</td><td align="right" headers="RENTAL BREAK_PHASE_1">$1,234.01</td><td align="right" headers="PROJECT BREAK_PHASE_1">$67,555.08</td></tr>
<tr class="odd"><td align="left" headers="SP_NAME BREAK_PHASE_1">Doe, Martha></td><td align="left" headers="SALES_NAME BREAK_PHASE_1">B and B CONSTRUCTION</td><td align="right" headers="PARTS BREAK_PHASE_1">$44,555.55</td><td align="right" headers="LABOR BREAK_PHASE_1">$999,999.00</td><td align="right" headers="RENTAL BREAK_PHASE_1">$1,222,111.00</td><td align="right" headers="PROJECT BREAK_PHASE_1">$776,665.00</td></tr><tr><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="left" headers="SP_NAME BREAK_PHASE_2"></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="left" headers="SALES_NAME BREAK_PHASE_2"></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="PARTS BREAK_PHASE_2">$48,845.85<br></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="LABOR BREAK_PHASE_2">$60,585.00<br></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="RENTAL BREAK_PHASE_2">$1,138,978.08<br></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="PROJECT BREAK_PHASE_2">$958,786.08<br></td></tr>
<tr><th colspan="7" class="apexir_REPEAT_HEADING" id="BREAK_PHASE_2">Timeframe : <span class="apex_break_headers">This YTD</span></th></tr>
<tr><th id="SP_NAME"><div style="text-align:left;">Salesperson Name</div></th><th id="SALES_NAME"><div style="text-align:left;">Customer Name</div></th><th id="PARTS"><div style="text-align:right;">PARTS</div></th><th id="LABOR"><div style="text-align:right;">Labor</div></th><th id="RENTAL"><div style="text-align:right;">RENTAL</div></th><th id="PROJECT"><div style="text-align:right;">PROJECT</div></th></tr><tr class="even">
<td align="left" headers="SP_NAME BREAK_PHASE_2">Johnson, Troy</td><td align="left" headers="SALES_NAME BREAK_PHASE_2">CLARK CONSTRUCTION</td><td align="right" headers="PARTS BREAK_PHASE_2">$0.00</td><td align="right" headers="LABOR BREAK_PHASE_2">$0.00</td><td align="right" headers="RENTAL BREAK_PHASE_2">$0.00</td><td align="right" headers="PROJECT BREAK_PHASE_2">$0.00</td></tr>
<tr class="odd"><td align="left" headers="SP_NAME BREAK_PHASE_2">Doe, Martha</td><td align="left" headers="SALES_NAME BREAK_PHASE_2">CLARK CONSTRUCTION</td><td align="right" headers="PARTS BREAK_PHASE_2">$0.00</td><td align="right" headers="LABOR BREAK_PHASE_2">$0.00</td><td align="right" headers="RENTAL BREAK_PHASE_2">$0.00</td><td align="right" headers="PROJECT BREAK_PHASE_2">$0.00</td></tr>
<tr class="even"><td align="left" headers="SP_NAME BREAK_PHASE_2">Doe, Martha</td><td align="left" headers="SALES_NAME BREAK_PHASE_2">B and B CONSTRUCTION</td><td align="right" headers="PARTS BREAK_PHASE_2">$22,333.44</td><td align="right" headers="LABOR BREAK_PHASE_2">$30,333.00</td><td align="right" headers="RENTAL BREAK_PHASE_2">$1,111,111.00</td><td align="right" headers="PROJECT BREAK_PHASE_2">$977,777.00</td></tr>
<tr class="odd"><td align="left" headers="SP_NAME BREAK_PHASE_2">Doe, Martha</td><td align="left" headers="SALES_NAME BREAK_PHASE_2">BIG DADDYS</td><td align="right" headers="PARTS BREAK_PHASE_2">$507.00</td><td align="right" headers="LABOR BREAK_PHASE_2">$0.00</td><td align="right" headers="RENTAL BREAK_PHASE_2">$6,222.22</td><td align="right" headers="PROJECT BREAK_PHASE_2">$6,958.08</td></tr><tr><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="left" headers="SP_NAME BREAK_PHASE_3"></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="left" headers="SALES_NAME BREAK_PHASE_3"></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="PARTS BREAK_PHASE_3">$48,845.85<br></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="LABOR BREAK_PHASE_3">$60,585.00<br></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="RENTAL BREAK_PHASE_3">$1,138,978.08<br></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="PROJECT BREAK_PHASE_3">$958,786.08<br></td></tr>
<tr><th colspan="7" class="apexir_REPEAT_HEADING" id="BREAK_PHASE_3">Timeframe : <span class="apex_break_headers">Last YTD</span></th></tr>
<tr><th id="SP_NAME"><div style="text-align:left;">Salesperson Name</div></th><th id="SALES_NAME"><div style="text-align:left;">Customer Name</div></th><th id="PARTS"><div style="text-align:right;">PARTS</div></th><th id="LABOR"><div style="text-align:right;">Labor</div></th><th id="RENTAL"><div style="text-align:right;">RENTAL</div></th><th id="PROJECT"><div style="text-align:right;">PROJECT</div></th></tr><tr class="even"><td align="left" headers="SP_NAME BREAK_PHASE_3">Johnson, Troy</td><td align="left" headers="SALES_NAME BREAK_PHASE_3">CLARK CONSTRUCTION</td><td align="right" headers="PARTS BREAK_PHASE_3">$0.00</td><td align="right" headers="LABOR BREAK_PHASE_3">$0.00</td><td align="right" headers="RENTAL BREAK_PHASE_3">$0.00</td><td align="right" headers="PROJECT BREAK_PHASE_3">$0.00</td></tr>
<tr class="odd"><td align="left" headers="SP_NAME BREAK_PHASE_3">Doe, Martha</td><td align="left" headers="SALES_NAME BREAK_PHASE_3">CLARK CONSTRUCTION</td><td align="right" headers="PARTS BREAK_PHASE_3">$0.00</td><td align="right" headers="LABOR BREAK_PHASE_3">$0.00</td><td align="right" headers="RENTAL BREAK_PHASE_3">$0.00</td><td align="right" headers="PROJECT BREAK_PHASE_3">$0.00</td></tr>
<tr class="even"><td align="left" headers="SP_NAME BREAK_PHASE_3">Doe, Martha</td><td align="left" headers="SALES_NAME BREAK_PHASE_3">B and B CONSTRUCTION</td><td align="right" headers="PARTS BREAK_PHASE_3">$45,345.45</td><td align="right" headers="LABOR BREAK_PHASE_3">$88,777.00</td><td align="right" headers="RENTAL BREAK_PHASE_3">$1,555,044.00</td><td align="right" headers="PROJECT BREAK_PHASE_3">$222,222.00</td></tr>
<tr class="odd"><td align="left" headers="SP_NAME BREAK_PHASE_3">Doe, Martha</td><td align="left" headers="SALES_NAME BREAK_PHASE_3">BIG DADDYS</td><td align="right" headers="PARTS BREAK_PHASE_3">$507.00</td><td align="right" headers="LABOR BREAK_PHASE_3">$0.00</td><td align="right" headers="RENTAL BREAK_PHASE_3">$2,455.58</td><td align="right" headers="PROJECT BREAK_PHASE_3">$5,944.44</td></tr><tr><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="left" headers="SP_NAME BREAK_PHASE_3"></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="left" headers="SALES_NAME BREAK_PHASE_3"></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="PARTS BREAK_PHASE_3">$48,845.85<br></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="LABOR BREAK_PHASE_3">$60,585.00<br></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="RENTAL BREAK_PHASE_3">$1,138,978.08<br></td><td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 219, 255);" align="right" headers="PROJECT BREAK_PHASE_3">$958,786.08<br></td></tr>
</tbody>

Open in new window



I was going to add the final figures with something like
$('.apexir_WORKSHEET_DATA tr:last').after('<tr>.....');

$('.apex_break_headers').each(function() {
  var parts_YTD   = 0;
  var Labor_YTD   = 0;
  var rental_YTD  = 0;
  var project_YTD = 0;
  var parts_LAST_YTD   = 0;
  var Labor_LAST_YTD   = 0;
  var rental_LAST_YTD  = 0;
  var project_LAST_YTD = 0;
 
  value = $(this).text();
  if (value.indexOf("This YTD") == 0) {
     // trying to figure out how to access the one I what... 
    console.log($(this).parent.html()); //breaks it
    console.log($(this).parent.parent.html());
    parts_YTD   = ???;
    Labor_YTD   = ???;
    rental_YTD  = ???;
    project_YTD = ???;

  }
  if (value.indexOf("Last YTD") == 0) {
    parts_LAST_YTD   = ???;
    Labor_LAST_YTD   = ???;
    rental_LAST_YTD  = ???;
    project_LAST_YTD = ???;
  }
  var parts_percent   = (parts_YTD - parts_LAST_YTD)/parts_YTD;
  var Labor_percent   = etc
  var rental_percent  = 
  var project_percent = 
$('.apexir_WORKSHEET_DATA tr:last').after('<tr>..blah final above percentages...');
});

Open in new window

0
Comment
Question by:bcarlis
  • 7
  • 6
14 Comments
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39876351
To get each total for each column is easier than you think

This will get the YTD total for labor:
$("td[headers='LABOR BREAK_PHASE_2']")[0].innerHTML

This will get the last year value for labor:
$("td[headers='LABOR BREAK_PHASE_3']")[0].innerHTML
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39876373
Questions

1. parts_LAST - is this a sum of the PARTS column for the section


Take section "This YTD"
There are 4 rows in this section - do you want the parts_LAST to be the sume of values in the column - if not what values do you want in the calculation.

2. Do you have control over the HTML - can you not put ID's into the various sections or is the output what you have to work with?
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39876540
Actually, I was trying to get the totals only and calculate using the two totals to come up with the percentage
But maybe because it's easier to get all of them I should just add up all the individual ones to get to the total and then use that total. But I Would need to exclude the total from that selector
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39876663
I have gone about it a slightly different way. This code
$(function() {
 // CREATE AN OBJECT TO STORE THE SECTIONS
 var result = new Object;
 
 // LOOP THROUGH ALL THE AGGREGATE VALUES - TOTAL ROW
 $('.apexir_AGGREGATE_VALUE').each(function() {
  
  // THE headers ATTRIBUTE CONTAINS THE VALUE TYPE AS WELL AS THE 
  // ID FOR THE HEADER ROW SO WE SPLIT THIS ON THE SPACE
  var parts = $(this).attr('headers').split(' ');
  
  // THE SECOND ELEMENT IN PART IS THE ID OF THE <td> THAT 
  // CONTAINS THE ThIS YID AND Last YTD LABLE
  var header = $('#' + parts[1] + ' span').html();
  
  // CHECK IF OUR OBJECT HAS A PROPERTY FOR THE FOUND HEADER
  // YET AND IF NOT CREATE IT.
  if (!result.hasOwnProperty(header)) {
   result[header] = new Object;
  }
  
  // AT THIS POINT OUR HEADER PROPERTY EXISTS - SO ADD TO IT
  // GET THE VALUE FROM THE TOTAL CELL BY REMOVING NON-DIGITS
  // PARSING TO INT AND DIVIDING BY 100 TO GET THE CENTS BACK
  var val = parseInt($(this).html().replace(/\D/g,''))/100;
  
  // FINALLY ADD THE TOTAL TO THE HEADER OBJECT USING THE FIRST
  // ELEMENT OF THE PARTS ARRAY. ALSO CHECK TO SEE THAT A VALUE 
  // WAS FOUND IN THE TOTAL CELL AND DEFAULT TO 0 IF NO
  result[header][parts[0]] = isNaN(val) ? 0 : val;
 });
 
 // DEBUG: EXAMINE THE RESULTS
 // AT THIS POINT THE RESULT OBJECT SHOULD CONTAIN ALL THE VALUES FROM THE TOTAL ROWS
 console.log(result);
});

Open in new window

Produces this result
Last YTD
    LABOR           60585
    PARTS           48845.85
    PROJECT        958786.08
    RENTAL        1138978.08
    SALES_NAME    0
    SP_NAME       0
This YTD
    LABOR           60585
    PARTS           48845.85
    PROJECT        958786.08
    RENTAL        1138978.08
    SALES_NAME    0
    SP_NAME       0

Open in new window

You can use the values in the result object to do your calculation.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39876900
WOW... I love it!

I am having a little problem though..
it seems to be missing the first header.. because the totals are all the same it is not catching it.
change some of the figures so the totals are different and I believe you will see.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39877209
No the problem is that the headers attribute is not consistant.

There are two sections that have headers BREAK_PHASE_3 - which sort of breaks the model.

Is there a reason for this?

If it can't be fixed then I have another solution - will post back shortly.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39877228
Checking...
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 2

Author Comment

by:bcarlis
ID: 39877245
WOW, the first apexir_AGGREGATE_VALUE row starts off showing BREAK_PHASE_2 not BREAK_PHASE_1

and then repeats the last one..

so need to add the actual numbers to get the totals?
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39877293
This is showing each correct break but need to exclude the apexir_AGGREGATE_VALUE

$(function() {
 var result = new Object;
 var cnt = 0;
 $('[headers*="BREAK_PHASE_"').each(function() {
  cnt = cnt + 1;
  // THE headers ATTRIBUTE CONTAINS THE VALUE TYPE AS WELL AS THE 
  // ID FOR THE HEADER ROW SO WE SPLIT THIS ON THE SPACE
  var parts = $(this).attr('headers').split(' ');

  // THE SECOND ELEMENT IN PART IS THE ID OF THE <td> THAT 
  // CONTAINS THE ThIS YID AND Last YTD LABLE
  var header = $('#' + parts[1] + ' span').html();

  console.log(cnt+'h) '+ header);
 });
});

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39877346
Gimme a sec I have a more robust solution - almost done ....
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 39877481
Ok this is another approach. Instead of looking for specific things instead we do a .each on all the th span's and td total rows and then use a flag to set which object we are working on
<script type="text/javascript">
$(function() {
 // CREATE AN OBJECT TO STORE THE SECTIONS
 var result = new Object;
 
 // THIS STORES THE CURRENT SECTION
 var current = '';
 
 // FIND ALL apex_break_headers SPAN'S and apexir_AGGREGATE_VALUE TD's
 // THEY WILL COME OUT IN ORDER WHICH SUITS OUR NEEDS
 $('td.apexir_AGGREGATE_VALUE, th.apexir_REPEAT_HEADING span.apex_break_headers').each (function () {
 
  // IF THIS HAS THE apex_break_headers CLASS THEN TIME
  // TO CREATE A NEW OBJECT 
  if ($(this).hasClass('apex_break_headers')) {
  
    // SO WE CAN REMEMBER WHAT SECTION WE ARE PROCESSING
    current = $(this).html();
    result[current] = new Object;
  }
  else {
  
    // SOME FAMILIAR CODE - GET THE COLUMN IDENTIFIER
    var parts = $(this).attr('headers').split(' ');
	
    // AND THE VALUE
    var val = parseInt($(this).html().replace(/\D/g,''))/100;
	
    // AND PUT IT IN THE OBJECT
    result[current][parts[0]] = isNaN(val) ? 0 : val;
  }
 });
 
 // ALL DONE/
 console.log(result);
});
</script>

Open in new window

HTML code same as before.

I did a test with different totals - output now looks like this
Last YTD
    LABOR             60585.35
    PARTS             48845.35
    PROJECT           958786.35
    RENTAL            1138978.35
    SALES_NAME        0
    SP_NAME           0
MTD
    LABOR             60585.15
    PARTS             48845.15
    PROJECT           958786.08
    RENTAL            1138978.15
    SALES_NAME        0
    SP_NAME           0
    
This YTD
    LABOR             60585.25
    PARTS             48845.25
    PROJECT           958786.25
    RENTAL            1138978.25
    SALES_NAME        0
    SP_NAME           0

Open in new window

0
 
LVL 2

Author Comment

by:bcarlis
ID: 39877555
Julian, That works perfect! Thank you so much! You da man!
0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 39877557
Beautiful!
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39877591
You are welcome - thanks for the points.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
CSS is a visual language used to classify objects and define rules about how they should be displayed. CSS skills aren’t restricted to developers anymore, there is a big benefit to having a basic understanding of the language, regardless of your occ…
In this tutorial viewers will learn how to style elements, such a divs, with a "drop shadow" effect using the CSS box-shadow property Start with a normal styled element, such as a div.: In the element's style, type the box shadow property: "box-shad…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now