Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

jQuery manipulations for calcs

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
BILL Carlisle
Asked:
BILL Carlisle
  • 7
  • 6
1 Solution
 
RobOwner (Aidellio)Commented:
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
 
Julian HansenCommented:
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
 
BILL CarlisleAPEX DeveloperAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Julian HansenCommented:
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
 
BILL CarlisleAPEX DeveloperAuthor Commented:
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
 
Julian HansenCommented:
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
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Checking...
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
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
 
BILL CarlisleAPEX DeveloperAuthor Commented:
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
 
Julian HansenCommented:
Gimme a sec I have a more robust solution - almost done ....
0
 
Julian HansenCommented:
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
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Julian, That works perfect! Thank you so much! You da man!
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Beautiful!
0
 
Julian HansenCommented:
You are welcome - thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now