CODEWITHSUNDEEP
jquery
Dragonman86
Dragonman86

Reputation: 95

Calculating differences between two tables using jQuery

If this question has already been asked please let me know where to find the answer.

I am setting up a "spreadsheet" style form which contains three different tables. The first table will contain a list of employees and their start time/end time as well as the total time worked. The second table will contain the employees scheduled start time/end time/total time. The third table should contain the difference between the actual worked time and the scheduled number of hours worked.

I have been able to get the calculations for the total time worked/scheduled working but getting the jquery formula to calculate the difference is not working.

Here is a jsfiddle containing the page: https://jsfiddle.net/Dragoonman/6oyauwr8/

jQuery

// JavaScript Document
$(document).ready(function() {
  var employees = ['Greg Weiland', 'Alicia Hawly', 'Charlen Connoly',
    'Dakota Giles', 'Donovan Cole', 'Robert Hill', 'Douglas Spirs',
    'Casey Green', 'Jared Peterson', 'Elizabeth P', 'Carl Mark', 
    'Carma J.', 'Ike J.', 'Elias H.'
  ];

  //This for each loop will write the employee names in the array above
 into their own rows
  for (i = 0; i < employees.length; i++) {
$('#footer').before('<tr class="rowEmployee"><th>' + employees[i] +
  '</th><td><input class="Time1" type="text"></td>' +
  '<td><input class="Time2" type="text">' +
  '</td><td><input class="Hours"></td></tr>');
$('#footer2').before('<tr class="rowSchedule"><td><input class="Time3" type="text"></td>' +
  '<td><input class="Time4" type="text"></td>' +
  '<td><input class="Hours2"></td></tr>');
$('#footer3').before('<tr class="rowDiff"><td><input class="diff"></td></tr>');
  }

$('.Time2').on('change', function() {
$('.rowEmployee').each(function() {
  var time1 = $('.Time1', this).val().split(':');
  var time2 = $('.Time2', this).val().split(':');
  //The following calculations turn minutes into a fraction to make the math easier
  var hoursWorked1 = parseInt(time1[0]) + ((parseInt(time1[1]) == 0) ? 0 : (parseInt(time1[1]) / 60));
  var hoursWorked2 = parseInt(time2[0]) + ((parseInt(time2[1]) == 0) ? 0 : (parseInt(time2[1]) / 60));
  //Here is your difference in hours calculation below
  var diff = hoursWorked2 - hoursWorked1;
  //Finally, write the total hours worked to the textbox, rounding to nearest hundredth
  $('.Hours', this).val(Math.round(diff * 100) / 100);
});
});

$('.Time4').on('change', function() {
$('.rowSchedule').each(function() {
  var time1 = $('.Time3', this).val().split(':');
  var time2 = $('.Time4', this).val().split(':');
  //The following calculations turn minutes into a fraction to make the math easier
  var hoursWorked1 = parseInt(time1[0]) + ((parseInt(time1[1]) == 0) ? 0 : (parseInt(time1[1]) / 60));
  var hoursWorked2 = parseInt(time2[0]) + ((parseInt(time2[1]) == 0) ? 0 : (parseInt(time2[1]) / 60));
  //Here is your difference in hours calculation below
  var diff = hoursWorked2 - hoursWorked1;
  //Finally, write the total hours worked to the textbox, rounding to nearest hundredth
  $('.Hours2', this).val(Math.round(diff * 100) / 100);
});
});

$('.Time2').on('change', function() {
$('.rowDiff').each(function() {
  var time1 = $('#footer.rowEmployee.Hours', this).val();
  var time2 = $('#footer2.rowSchedule.Hours2', this).val();
  var diff = time2 - time1;
  $('.diff', this).val(diff);
});
});
});

HTML

<h3>IMPUT ALL TIMES IN MILITARY FORMAT INCLUDING ":"</h3>
<h4>*times after midnight for same working day should continue to 25:00+*</h4>
<table id="actual" cellspacing="0px">
  <tr id="header">
    <th>Name</th>
    <th>Start Time</th>
    <th>End Time</th>
    <th>
      Total Time
      <br/> Worked
    </th>
  </tr>
  <!-- Add the ID of footer so the JS knows where to append the rows containing employee names -->
  <tr id="footer">
    <td colspan="4">&nbsp;</td>
  </tr>
</table>
<table id="schedule" cellspacing="0px">
  <tr>
    <th>
      Scheduled
      <br/> Start Time
    </th>
    <th>
      Scheduled
      <br/> End Time
    </th>
    <th>
      Total Time
      <br/> Scheduled
    </th>
  </tr>
  <tr id="footer2">
    <td colspan="4">&nbsp;</td>
  </tr>
</table>
<table id="difference" cellspacing="0px">
  <tr>
    <th>
      Difference
      <br/>&nbsp;
    </th>
  </tr>
  <tr id="footer3">
    <td colspan="4">&nbsp;</td>
  </tr>
</table>

Any help that can be provided would be greatly appreciated

Upvotes: 0

Views: 123

Answers (1)

gaetanoM
gaetanoM

Reputation: 42054

I propose you to take track of the indexes in each loop so that you may compute in line the last column values. Moreover, I suggest you to test if a value is a number or not. An improvement could be to use an input type="time" to simplify the imput phase.

Why do you use jQuery.each? If you need to compute in one shot also the total by column it'sok, otherwise it's useless.

My proposal is:

$(function () {
  var employees = ['Greg Weiland', 'Alicia Hawly', 'Charlen Connoly',
                   'Dakota Giles', 'Donovan Cole', 'Robert Hill', 'Douglas Spirs',
                   'Casey Green', 'Jared Peterson', 'Elizabeth P', 'Carl Mark', 'Carma J.', 'Ike J.', 'Elias H.'
                  ];

  //This for each loop will write the employee names in the array above into their own rows
  for (i = 0; i < employees.length; i++) {
    $('#footer').before('<tr class="rowEmployee"><th>' + employees[i] +
                        '</th><td><input class="Time1" type="text"></td>' +
                        '<td><input class="Time2" type="text">' +
                        '</td><td><input class="Hours"></td></tr>');
    $('#footer2').before('<tr class="rowSchedule"><td><input class="Time3" type="text"></td>' +
                         '<td><input class="Time4" type="text"></td>' +
                         '<td><input class="Hours2"></td></tr>');
    $('#footer3').before('<tr class="rowDiff"><td><input class="diff"></td></tr>');
  }

  $('.Time2').on('change', function(e) {
    $('.rowEmployee').each(function(index, element) {
      var time1 = $('.Time1', this).val().split(':');
      var time2 = $('.Time2', this).val().split(':');
      //The following calculations turn minutes into a fraction to make the math easier
      var hoursWorked1 = parseInt(time1[0]) + ((parseInt(time1[1]) == 0) ? 0 : (parseInt(time1[1]) / 60));
      var hoursWorked2 = parseInt(time2[0]) + ((parseInt(time2[1]) == 0) ? 0 : (parseInt(time2[1]) / 60));
      //Here is your difference in hours calculation below
      var diff = hoursWorked2 - hoursWorked1;
      if (!isNaN(diff)) {
        //Finally, write the total hours worked to the textbox, rounding to nearest hundredth
        var time1 = Math.round(diff * 100) / 100;
        $('.Hours', this).val(time1);
        var time2 = $('.rowSchedule').eq(index).find('.Hours2').val();
        if (!isNaN(time2)) {
          $('.diff').eq(index).val(time2 - time1);
        }
      }
    });
  });

  $('.Time4').on('change', function() {
    $('.rowSchedule').each(function(index, element) {
      var time1 = $('.Time3', this).val().split(':');
      var time2 = $('.Time4', this).val().split(':');
      //The following calculations turn minutes into a fraction to make the math easier
      var hoursWorked1 = parseInt(time1[0]) + ((parseInt(time1[1]) == 0) ? 0 : (parseInt(time1[1]) / 60));
      var hoursWorked2 = parseInt(time2[0]) + ((parseInt(time2[1]) == 0) ? 0 : (parseInt(time2[1]) / 60));
      //Here is your difference in hours calculation below
      var diff = hoursWorked2 - hoursWorked1;
      if (!isNaN(diff)) {
        //Finally, write the total hours worked to the textbox, rounding to nearest hundredth
        var time2 = Math.round(diff * 100) / 100;
        $('.Hours2', this).val(time2);
        var time1 = $('.rowEmployee').eq(index).find('.Hours').val();
        if (!isNaN(time1)) {
          $('.diff').eq(index).val(time2 - time1);
        }
      }
    });
  });
});
h3,
h4 {
  text-align: center;
}

td {
  border: solid thin #000000;
}

th {
  border: solid thin #000000;
}

#actual {
  float: left;
}

#schedule {
  float: left;
  padding-left: 5px;
}

#difference {
  padding-left: 3px;
}
<script src="https://code.jquery.com/jquery-1.12.1.min.js"></script>


<h3>IMPUT ALL TIMES IN MILITARY FORMAT INCLUDING ":"</h3>
<h4>*times after midnight for same working day should continue to 25:00+*</h4>
<table id="actual" cellspacing="0px">
    <tr id="header">
        <th>Name</th>
        <th>Start Time</th>
        <th>End Time</th>
        <th>
            Total Time
            <br/> Worked
        </th>
    </tr>
    <!-- Add the ID of footer so the JS knows where to append the rows containing employee names -->
    <tr id="footer">
        <td colspan="4">&nbsp;</td>
    </tr>
</table>
<table id="schedule" cellspacing="0px">
    <tr>
        <th>
            Scheduled
            <br/> Start Time
        </th>
        <th>
            Scheduled
            <br/> End Time
        </th>
        <th>
            Total Time
            <br/> Scheduled
        </th>
    </tr>
    <tr id="footer2">
        <td colspan="4">&nbsp;</td>
    </tr>
</table>
<table id="difference" cellspacing="0px">
    <tr>
        <th>
            Difference
            <br/>&nbsp;
        </th>
    </tr>
    <tr id="footer3">
        <td colspan="4">&nbsp;</td>
    </tr>
</table>

Upvotes: 1

Related Questions