JXA Scripting of Numbers.app, using AJAX to calculate payroll deductions from a remote PHP script

calculate-payroll.scpt

Numbers = Application('Numbers');
Numbers.includeStandardAdditions = true

var table 					= Numbers.documents[0].sheets[0].tables[0]

var selectedRow 			= table.selectionRange().cells[0];
var hoursCellRow 			= selectedRow.row().address();

var weekEndingCell 			= table.ranges["B" + hoursCellRow + ":B" + hoursCellRow].cells[0];
var nameCell 				= table.ranges["C" + hoursCellRow + ":C" + hoursCellRow].cells[0];
var sinCell 				= table.ranges["D" + hoursCellRow + ":D" + hoursCellRow].cells[0];
var hoursCell 				= table.ranges["F" + hoursCellRow + ":F" + hoursCellRow].cells[0];
var hourlyCell 				= table.ranges["G" + hoursCellRow + ":G" + hoursCellRow].cells[0];
var salaryCell 				= table.ranges["H" + hoursCellRow + ":H" + hoursCellRow].cells[0];
var vacationPayCell 		= table.ranges["I" + hoursCellRow + ":I" + hoursCellRow].cells[0];
var grossPayCell 			= table.ranges["J" + hoursCellRow + ":J" + hoursCellRow].cells[0];
var eiCell 					= table.ranges["K" + hoursCellRow + ":K" + hoursCellRow].cells[0];
var cppCell 				= table.ranges["L" + hoursCellRow + ":L" + hoursCellRow].cells[0];
var federalTaxCell 			= table.ranges["M" + hoursCellRow + ":M" + hoursCellRow].cells[0];
var provincialTaxCell		= table.ranges["N" + hoursCellRow + ":N" + hoursCellRow].cells[0];
var netPayCell 				= table.ranges["O" + hoursCellRow + ":O" + hoursCellRow].cells[0];

var hoursValue 				= hoursCell.value();
var hourlyValue				= hourlyCell.value();
var weekEndingValue			= weekEndingCell.value();

var weekEndingDate			= new Date(weekEndingValue);
var weekEndingYear			= weekEndingDate.getFullYear();
var weekEndingMonth			= weekEndingDate.getMonth() + 1;
var weekEndingDay			= weekEndingDate.getDate();

deductions 					= getDeductions(hoursValue, hourlyValue, weekEndingYear, weekEndingMonth, weekEndingDay);

eiCell.value 				= deductions.values.EI;
cppCell.value 				= deductions.values.CPP;
federalTaxCell.value 		= deductions.values.federalTax;
provincialTaxCell.value 	= deductions.values.provincialTax;

var stubTable 				= Numbers.documents[0].sheets[1].tables[0]

var stubNameCell			= stubTable.ranges["C2:C2"].cells[0];
var stubSINCell				= stubTable.ranges["E2:E2"].cells[0];
var stubWeekEndingCell 		= stubTable.ranges["A5:A5"].cells[0];
var stubHoursCell 			= stubTable.ranges["B5:B5"].cells[0];
var stubHourlyCell 			= stubTable.ranges["C5:C5"].cells[0];
var stubSalaryCell 			= stubTable.ranges["D5:D5"].cells[0];
var stubVacationPayCell 	= stubTable.ranges["E5:E5"].cells[0];
var stubGrossPayCell 		= stubTable.ranges["F5:F5"].cells[0];
var stubEICell 				= stubTable.ranges["G5:G5"].cells[0];
var stubCPPCell 			= stubTable.ranges["H5:H5"].cells[0];
var stubfederalTaxCell 		= stubTable.ranges["I5:I5"].cells[0];
var stubprovincialTaxCell 	= stubTable.ranges["J5:J5"].cells[0];
var stubnetPayCell 			= stubTable.ranges["K5:K5"].cells[0];

stubNameCell.value			= nameCell.value();
stubSINCell.value			= sinCell.value();
stubWeekEndingCell.value	= weekEndingCell.value();
stubHoursCell.value 		= hoursCell.value();
stubHourlyCell.value 		= hourlyCell.value();
stubSalaryCell.value		= salaryCell.value();
stubVacationPayCell.value	= vacationPayCell.value();
stubGrossPayCell.value		= grossPayCell.value();
stubEICell.value 			= eiCell.value();
stubCPPCell.value 			= cppCell.value();
stubfederalTaxCell.value 	= federalTaxCell.value();
stubprovincialTaxCell.value = provincialTaxCell.value();
stubnetPayCell.value 		= netPayCell.value();

// Numbers.documents[0].print();

function getDeductions(hours, hourly, year, month, day) {
	app = Application.currentApplication()
	app.includeStandardAdditions = true;
	result = app.doShellScript('curl "https://example.com/get-pdoc.php?hours=' + hours + '&hourly=' + hourly + '&year=' + weekEndingYear + '&month=' + weekEndingMonth + '&day=' + weekEndingDay + '"');
	result = JSON.parse(result);
	return result;
}

get-pdoc.php

<?php

$retval = array();
$retval['status'] = 'success';

$required = array('hours', 'hourly', 'year', 'month', 'day');
foreach($required as $key => $value) {
  if (!$_GET[$value]) {
    $retval['status'] = 'error';
    $retval['errormessage'] = "Missing '" . $value . "' parameter.";
  }
}

if ($retval['status'] != 'error') {

  $param = array();

  $param['income'] = ($_GET['hourly'] * $_GET['hours']);
  $param['vacationPay'] = round($param['income'] * 0.04, 2);
  $param['year'] = $_GET['year'];
  $param['month'] = $_GET['month'];
  $param['day'] = $_GET['day'];

  $param['month'] = str_pad($param['month'], 2, '0', STR_PAD_LEFT);
  $param['day'] = str_pad($param['day'], 2, '0', STR_PAD_LEFT);

  $data = array(
    "step0" => array(
                  "calculationType" => "salary",        // Salary
                  "goStep1" => "Next"
                ),
    "step1" => array(
                  "employeeName" => "The Employee",
                  "employerName" => "The Employer",
                  "province" => "2",                    // Prince Edward Island
                  "payPeriod" => "1",                   // Weekly
                  "cmbFirstYear" => $param['year'],
                  "cmbFirstMonth" => $param['month'],
                  "cmbFirstDay" => $param['day'],
                  "goStep2AddOption" => "Next"
                ),
    "step2" => array(
                  "incomeTypeAmount" => $param['income'],
                  "vacationPay" => $param['vacationPay'],
                  "salaryType" => "na",
                  "clergyType" => "hna",
                  "goStep2Option" => "Next"
                ),
    "step3" => array(
                  "claimCodeFed" => "1",
                  "requestAdditionalTax" => "0.00",
                  "claimCodeProv" => "1",
                  "yearToDateCPP" => "0",
                  "yearToDatePeAmount" => "0",
                  "yearToDateCPPAmount" => "0",
                  "yearToDateEI" => "0",
                  "yearToDateIeAmount" => "0",
                  "yearToDateEIAmount" => "0",
                  "reducedEIRate" => "0",
                  "goResults" => "Calculate"
                ),
    );

  $triggers = array(
      "federalTax" => array("Federal tax deduction", 2),
      "provincialTax" => array("Provincial tax deduction", 4),
      "CPP" => array("CPP deductions", 5),
      "EI" => array("EI deductions", 3),
    );

  $result = fetch("https://apps.cra-arc.gc.ca/ebci/rhpd/startLanguage.do?lang=English", array('post' => false));
  $result = fetch("https://apps.cra-arc.gc.ca/ebci/rhpd/handleEntryPoint.do", array('refer' => 'https://apps.cra-arc.gc.ca/ebci/rhpd/startLanguage.do?lang=English', 'post' => http_build_query($data['step0'], '', '&')));
  $result = fetch("https://apps.cra-arc.gc.ca/ebci/rhpd/handleGeneralInfo.do", array('refer' => 'https://apps.cra-arc.gc.ca/ebci/rhpd/handleEntryPoint.do', 'post' => http_build_query($data['step1'], '', '&')));
  $result = fetch("https://apps.cra-arc.gc.ca/ebci/rhpd/handleAdditionalOptions.do", array('refer' => 'https://apps.cra-arc.gc.ca/ebci/rhpd/handleGeneralInfo.do', 'post' => http_build_query($data['step2'], '', '&')));
  $result = fetch("https://apps.cra-arc.gc.ca/ebci/rhpd/s-handleInterview.do", array('refer' => 'https://apps.cra-arc.gc.ca/ebci/rhpd/handleAdditionalOptions.do', 'post' => http_build_query($data['step3'], '', '&')));

  $lines = explode("\n", $result);

  foreach ($lines as $linenumber => $line) {
    foreach ($triggers as $key => $trigger) {
      if (strpos($line, $trigger[0]) !== FALSE) {
        $values[$key] = trim($lines[$linenumber + $trigger[1]]);
      }
    }
  }
  $retval['values'] = $values;
}

print json_encode($retval);

function fetch($url, $z=null) {
  $ch =  curl_init();

  $useragent = 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:10.0.2) Gecko/20100101 Firefox/10.0.2';

  curl_setopt( $ch, CURLOPT_URL, $url );
  curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
  curl_setopt( $ch, CURLOPT_AUTOREFERER, true );
  curl_setopt( $ch, CURLOPT_FOLLOWLOCATION, true );
  curl_setopt( $ch, CURLOPT_POST, isset($z['post']) );

  if( isset($z['post']) )         curl_setopt( $ch, CURLOPT_POSTFIELDS, $z['post'] );
  if( isset($z['refer']) )        curl_setopt( $ch, CURLOPT_REFERER, $z['refer'] );

  curl_setopt( $ch, CURLOPT_USERAGENT, $useragent );
  curl_setopt( $ch, CURLOPT_CONNECTTIMEOUT, 5 );
  curl_setopt( $ch, CURLOPT_COOKIEJAR,  '/tmp/cra-cookies.txt' );
  curl_setopt( $ch, CURLOPT_COOKIEFILE, '/tmp/cra-cookies.txt' );

  $result = curl_exec( $ch );
  curl_close( $ch );
  return $result;
}