asked on

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

Rob

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

Julian Hansen

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?

BILL Carlisle

ASKER

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

Julian Hansen

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.

BILL Carlisle

ASKER

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.

Julian Hansen

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.

BILL Carlisle

ASKER

Checking...

BILL Carlisle

ASKER

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?

BILL Carlisle

ASKER

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

Julian Hansen

Gimme a sec I have a more robust solution - almost done ....

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BILL Carlisle

ASKER

Julian, That works perfect! Thank you so much! You da man!