billingEmailFunctions.i

<?php
/* File: functions.php
 * Purpose: There are multiple functions here that help out data.php.
 */

function getCCWhenDone() {
    return array("phil");
}

function GetImageLocation() {
  return "/home/homecu/images/logo.png";
}

function GetIsDev() {
  global $SYSENV; // Where this is used is so deep.  I don't want to add another parameter all the way up (5+ levels.)
  return $SYSENV["devmode"] == 1;
}

function GetEditPath() {
  $editFile = "cuissues_edit.prg";
  // I feel like this is very safe.  I was considering getting the domain from $_SERVER["SCRIPT_URI"] but I'm not sure if that is safe.
  $editPath = GetIsDev() ? "http://localhost:8000/hcuadm/$editFile" : "https://my.homecu.net/hcuadm/$editFile";
  return $editPath;
}

/**
 * function setupWorkflow($dbh, $productId, $productText, $featureDetailId, $cu, $cuName, &$errors, &$transactionSQLs, $staffId, &$taskEmails, &$issueEmails)
 * This sets up the workflow
 *
 * @param integer $dbh -- the database connection
 * @param string $productId -- the product id from cuprodlist
 * @param string $productText -- the product name from cuprodlist
 * @param integer $featureDetailId -- the feature detail id
 * @param string $cu -- the credit union id
 * @param string $cuName -- the credit union name
 * @param array $errors -- the errors encountered
 * @param array $transactionSQLs -- the transaction SQLs (creates, updates, and deletes that will be done together)
 * @param string $staffId -- the logged in user
 * @param array $taskEmails -- the task emails to be sent out (compile a list and if the transaction doesn't fail, send them.)
 * @param array $issueEmails -- the issue emails to be sent out (compile a list and if the transaction doesn't fail, send them.)
 * @return {true|false} -- true if an workflow was created or will be created
 */
function SetupWorkflow($dbh, $productId, $productText, $featureDetailId, $cu, $cuName, $staffId) {
  try {
    $workflowCreated = false;
    $taskEmails = array();
    $issueEmails = array();

    $cu = strtolower($cu);
    $sql = "select 'FOUND' as found from cutrack where billing_feature_id = $featureDetailId";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("Found query failed.", 1);
    }
    if (db_num_rows($sth) > 0) {
      throw new exception("Workflow already exists.", 2);
    }

    $sql = "select nextval('cutrackitem_trackitem_id_seq'::text::regclass) as next_item, nextval('cutrack_track_id_seq'::text::regclass) as next_track";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("nextval query failed.", 4);
    }
    $row = db_fetch_row($sth, 0);
    $nextTaskId = intval($row[0]);
    $issueId = intval($row[1]);
    $issueName = "";

    $sql = "select i.issue_id, i.code, i.owner, ii.item_id, ii.title, ii.item_desc, ii.view_order, ii.owner as task_owner, ii.parent, ii.sales_item_id
            from cuissues i
            left join cuissuesitem ii on i.issue_id = ii.issue_id
            where i.gen_for_product = '$productId'
            order by i.issue_id";
    $sthList = db_query($sql, $dbh);
    if (!$sthList) {
      throw new exception("Issue select failed.", 3);
    }
    $issueResults = array();

    $insertIssueSQL = "insert into cutrack
                       (track_id, issue, assigned_to, billing_feature_id, previous_task_owner, user_name, status, target_date_type, entry_date,
                         last_activity_date, issue_id)";
    $insertTasksSQL = "insert into cutrackitem (trackitem_id, track_id, title, item_desc, view_order, parent, assigned_to, issue_item_id, billing_item_id)";

    $taskSQLPartArray = array();
    $subTaskArray = array();
    $hasTasks = false;
    $parentTaskId = $nextTaskId;

    $first = true;
    $parentIMap = array();
    $parentI = 0;
    for($i = 0; $row = db_fetch_assoc($sthList, $i); $i++) {
      if ($first) {
        $issueOwner = isset($row["owner"]) ? trim($row["owner"]) : $staffId;
        $templateIssueId = isset($row["issue_id"]) ? trim($row["issue_id"]) : "";
        $taskOwner = isset($row["task_owner"]) ? trim($row["task_owner"]) : "";
        $issue = isset($row["code"]) ? trim($row["code"]) : "";
        $issueName = $issue;
        $insertIssueSQL .= " values ($issueId, '" . prep_save($issue, 90) . "', '" . prep_save($issueOwner, 30) . "', $featureDetailId,
                           '" . prep_save($taskOwner, 12) . "', '" . prep_save($cu, 12) . "', 'Active', 'TBD', now(), now(), $templateIssueId)";
        $sth = db_query($insertIssueSQL, $dbh);
        if (!$sth) {
          throw new exception("issue insert failed.", 5);
        }

        $first = false;
      }

      $taskId = isset($row["item_id"]) ? trim($row["item_id"]) : "";

      if($taskId != "" && $templateIssueId == $row["issue_id"]) { // Ensure that tasks created belong to the issue created.
          $title = isset($row["title"]) ? trim($row["title"]) : "";
          $description = isset($row["item_desc"]) ? trim($row["item_desc"]) : "";
          $viewOrder = isset($row["view_order"]) ? intval($row["view_order"]) : 0;
          $owner = isset($row["task_owner"]) ? trim($row["task_owner"]) : "";
          $parent = isset($row["parent"]) ? intval($row["parent"]) : 0;
          $taskTemplateId = isset($row["item_id"]) ? intval($row["item_id"]) : 0;
          $billingItemId = isset($row["sales_item_id"]) ? intval($row["sales_item_id"]) : 0;

          if ($billingItemId != 0) {
            $sql = "select fd.id from cubillfeaturedetail fd
                    inner join cubillfeaturedetail wfd
                    on fd.sales_order_detail_id = wfd.sales_order_detail_id and wfd.id = $featureDetailId and fd.billing_status= 1
                      and fd.sales_item_id = $billingItemId and (fd.workflow_completed is null or fd.workflow_completed = 'N')
                    order by id desc";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
              throw new exception("Billing item id query failed.", 6);
            }
            $billingItemId = db_fetch_row($sth, 0);
            $billingItemId = intval($billingItemId[0]);
            $row["sales_item_id"] = $billingItemId;  // Save this if it is a subtask.
          }

          if ($parent == 0) {
            $taskSQLPartArray[] = "($parentTaskId, $issueId, '" . prep_save($title, 80) . "', '" . prep_save($description) . "',
                                    $viewOrder, 0, '" . prep_save($owner, 12) . "', $taskTemplateId, $billingItemId)";
            $parentIMap[$taskTemplateId] = $parentI ++;
            $parentTaskId ++;
          } else {
            if (!HCU_array_key_exists($parent, $subTaskArray)) {
              $subTaskArray[$parent] = array();
            }
            $subTaskArray[$parent][] = array("description" => $description, "viewOrder" => $viewOrder, "owner" => $owner, "taskTemplateId" => $taskTemplateId,
                                             "billingItemId" => $billingItemId, "title" => $title);
          }
          $hasTasks = true;
      }

      $issueResults[] = $row;
    }

    $subTaskId = $parentTaskId;
    if ($hasTasks) {
        $sql = "$insertTasksSQL values " . implode(", ", $taskSQLPartArray);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
          throw new exception("Parent task insert failed.", 8);
        }

        $taskSQLPartArray = array();
        foreach($subTaskArray as $parentId => $detailRecords) {
            foreach($detailRecords as $record) {
              $parentTaskId = $nextTaskId + $parentIMap[$parentId];
              extract($record);

              $taskSQLPartArray[] = "($subTaskId, $issueId, '" . prep_save($title, 80) . "', '" . prep_save($description) . "', $viewOrder, $parentTaskId,
                                      '" . prep_save($owner, 12) . "', $taskTemplateId, $billingItemId)";

              $subTaskId ++;
            }
        }

        if (count($taskSQLPartArray) > 0) { // Make sure that there are any subtasks.
          $sql = "$insertTasksSQL values " . implode(", ", $taskSQLPartArray);
          $sth = db_query($sql, $dbh);
          if (!$sth) {
            throw new exception("Sub task creation failed.", 9);
          }
        }

        $sql = "select setval('cutrackitem_trackitem_id_seq'::text::regclass, $subTaskId, false)";
        // Because inserts are done with the id, sequence is not updated.
        $sth = db_query($sql, $dbh);
        if (!$sth) {
          throw new exception("Setval query failed.", 10);
        }
    }

    if (!$first) { // means that there is an issue created so now send email.
        $results = CreateToLine($dbh, array(trim($issueOwner)));
        if ($results["status"] !== "000") {
          throw new exception ($results["error"], 13);
        }
        $to = $results["data"];
        $subject = "$productText job has been created.";
        $text = "Billing contract for $cu has been set up.  Workflow is set up for $productText which is assigned to you.";
        $issueEmails[] = array("to" => $to, "subject" => $subject, "text" => $text, "issueName" => $issueName, "issueId" => $issueId);
        $results = NotifyFirstTaskOwner($dbh, $templateIssueId, $issueId, $staffId, $issueOwner);
        if ($results["status"] !== "000") {
          throw new exception ($results["error"], 14);
        }
        $taskEmails = array_merge($taskEmails, $results["data"]["taskEmails"]);
        $workflowCreated = true;
    } else {

      $sql = "update cubillfeaturedetail set billing_status = case when billing_status = 5 then 5 else 4 end where id = $featureDetailId";
      $sth = db_query($sql, $dbh);
      if (!$sth) {
        throw new exception("update cubillfeaturedetail query failed.", 11);
      }
    }

    $returnArray = array("status" => "000", "error" => "",
                         "data" => array("workflowCreated" => $workflowCreated, "taskEmails" => $taskEmails, "issueEmails" => $issueEmails, "issueId" => $issueId));
  } catch(exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }

  return $returnArray;
}

/**
 * function createTaskEmail($to, $subject, $text, $issue, $title, $description, $cuName, $cuCode, $issueId = 0, $taskId = 0, $text2 = "", $completedNotes = "")
 * This creates an email for a task.  This will happen when the prior task is completed or the task is reactivated or several other reasons.  It will distinctly show if it is DEV or Prod.
 *
 * @param array $to -- contains list of receipents in the to, cc, and bcc lines.
 * @param string $subject -- the subject of the email
 * @param string $text -- the first line of email contents (what happened? e.g. "samuel finished his job.  Yours is next.")
 * @param string $issue -- the title of the job that the task belongs to
 * @param string $title -- the title of the task
 * @param string $description -- the description of the task
 * @param string $cuName -- the credit union
 * @param integer $issueId -- the id of the job
 * @param integer $taskId -- the id of the task
 * @param string $text2 -- for some messages, there is an extra line
 * @param string $completedNotes -- the notes to tell the next task owner
 */
function CreateTaskEmail($to, $subject, $text, $issue, $title, $description, $cuName, $cuCode, $issueId = 0, $taskId = 0, $text2 = "", $completedNotes = "") {

  $editPath = GetEditPath();
  $isOnDev = GetIsDev();

  $color = $isOnDev ? "#6A1B13" : "#016103";

  $message = "<table style='border-width:0px;border-collapse:separate;'>
    <tr><td style='border-bottom:1px solid black; margin-bottom:5px;' colspan=2>$text</td></tr>";
  $plainMessage = $text;

  if ($text2 != "") {
    $message .= "<tr><td colspan=2>$text2</td></tr>";
    $plainMessage .= "\n$text2";
  }

  $message .= "<tr><td><b>Job:</b></td><td>$issue</td></tr>
    <tr><td><b>Task:</b></td><td>$title</td></tr>
    <tr><td><b>Description:</b></td><td>$description</td></tr>
    <tr><td><b>CU:</b></td><td>$cuName ($cuCode)</td></tr><tr>";
  $plainMessage .= "\nJob:\t$issue
                   \nTask:\t$title
                   \nDescription:\t$description
                   \nCU:\t$cuName ($cuCode)";
  if ($completedNotes != "") {
    $message .= "<tr><td><b>Notes:</b></td><td>$completedNotes</td></tr>";
    $plainMessage .= "\nNotes:\t$completedNotes";
  }

  if ($taskId != 0){
      $message .= "<td style=\"padding: 5px;\"><a href='$editPath?trackId=$issueId&trackItemId=$taskId' style=\"background-color: $color; color: #fff;
          text-decoration: none; padding: 5px 13px; font-size: 16px; display: block;\">View Task</a></td>";
      $plainMessage .= "\nView Task <$editPath?trackId=$issueId&trackItemId=$taskId>";
  }
  if ($issueId != 0) {
      $message .= "<td style=\"padding: 5px;\"><a href='$editPath?trackId=$issueId' style=\"background-color: $color; color: #fff;
          text-decoration: none; padding: 5px 13px; font-size: 16px; display: block;\">View Job</a></td>";
      $plainMessage .= "\nView Job <$editPath?trackId=$issueId>";
  }
  $message .= "</tr></table><br><hr>";
  CreateEmail($to, $subject, $message, $isOnDev);
}

/**
 * function createIssueEmail($to, $subject, $text, $issue, $status, $cuName, $cuCode, $issueId = 0, $text2 = "", $completedNotes = "")
 * This creates an email for the job.  This is when a job is created or is finished
 *
 * @param array $to -- contains list of receipents in the to, cc, and bcc lines.
 * @param string $subject -- the subject of the email
 * @param string $text -- the first line of email contents (what happened? e.g. "samuel finished his job.  Yours is next.")
 * @param string $issue -- the title of the job
 * @param string $status -- the status of the job
 * @param string $cuName -- the credit union
 * @param integer $issueId -- the id of the job
 * @param string $text2 -- for some messages, there is an extra line
 * @param string $completedNotes -- the notes to tell the next task owner
 */
function CreateIssueEmail($to, $subject, $text, $issue, $status, $cuName, $cuCode, $issueId = 0, $text2 = "", $completedNotes = "") {
  $editPath = GetEditPath();
  $isOnDev = GetIsDev();

  $color = $isOnDev ? "#6A1B13" : "#016103";

  $message = "<table style='border-width:0px;border-collapse:separate;'><colgroup><col style='width:150px'><col></colgroup>
    <tr><td style='border-bottom:1px solid black; margin-bottom:5px;' colspan=2>$text</td></tr>";

  if ($text2 != "") {
    $message .= "<tr><td colspan=2>$text2</td></tr>";
  }

  $message .= "<tr><td><b>Title:</b></td><td>$issue</td></tr>
    <tr><td><b>Status:</b></td><td>$status</td></tr>
    <tr><td><b>CU:</b></td><td>$cuName ($cuCode)</td></tr><tr>";

  if ($completedNotes != "") {
    $message .= "<tr><td><b>Notes:</b></td><td>$completedNotes</td></tr>";
  }

    if ($issueId != 0) {
        $message .= "<td style=\"padding: 5px;\"><a href='$editPath?trackId=$issueId' style=\"background-color: $color; color: #fff;
            text-decoration: none; padding: 5px 13px; font-size: 16px; display: block;\">View Job</a></td>";
    }
    $message .= "</tr></table><br><hr>";
    CreateEmail($to, $subject, $message, $isOnDev);
}

/**
 * function createEmail($to, $subject, $message, $plainMessage, $logoSource, $isOnDev)
 * Actually creates the email with headers and whatnot and then sends it off.
 *
 * @param array $to -- contains list of receipents in the to, cc, and bcc lines.
 * @param string $subject -- the subject of the email
 * @param string $message -- the rich text version created by either the createIssueEmail or the createTaskEmail function.
 * @param string $plainMessage -- the plain text version created by either the createIssueEmail or the createTaskEmail function.
 * @param string $logoSource -- the source for the HomeCU logo at the bottom of the email
 * @param boolean $isOnDev -- if it is on dev, then send it from another address so that I can divide the two.
 */
function CreateEmail($to, $subject, $message, $isOnDev) {
  $base64 = chunk_split(base64_encode(file_get_contents(GetImageLocation())));

  $msg = "$message<br><img src='data:image/png;base64, $base64' alt='HomeCU logo' />";

  $notify = new ErrorMail;
  $notify->mailto = $to;
  $notify->mailfrom = $isOnDev ? "billingdev@homecu.com" : "billing@homecu.com";
  $notify->mailfromname = $isOnDev ? "Development Billing" : "Billing";
  $notify->subject = $subject;
  $notify->htmlMsgbody =  $msg;
  $notify->callingfunction = __FUNCTION__;
  $notify->file = __FILE__;
  $notify->SendMail();
}

/**
 * function createToLine($dbh, $owners, &$errors)
 * creates the to array from the list of logins
 *
 * @param integer $dbh -- the database connection
 * @param array $owners -- list of logins
 * @param array $errors -- any errors encountered
 * @param array $sqls -- the SQLs used
 * @return array --
 *    $to -- emails separated by a semicolon for people on the "to" line.
 *    $cc -- emails separated by a semicolon for people on the "cc" line.
 *    $bcc -- emails separated by a semicolon for people on the "bcc" line.
 */
function CreateToLine($dbh, $owners) {
  try {
    $to = "";
    $cc = "";
    $bcc = "";
    if (count($owners) > 0) {

      foreach($owners as $i => $owner) {
        $owners[$i] = strtolower(prep_save($owner, 12));
      }

      $sql = "select user_name, notifyemail from dmsmonitorusers
              where trim(user_name) in ('" . implode("', '", $owners) . "') and notifyemail is not null and trim(from notifyemail) <> ''";
      $sth = db_query($sql, $dbh);
      if (!$sth) {
        throw new exception("createToLine query failed.", 1);
      }

      $toArray = array();

      for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
        $username = isset($row["user_name"]) ? trim($row["user_name"]) : "";
        $email = isset($row["notifyemail"]) ? trim($row["notifyemail"]) : "";
        $thisRecord = "$username <$email>";
        $toArray[] = $thisRecord;
      }
    }

    $returnArray = array("status" => "000", "error" => "", "data" => $toArray);
  } catch (exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }

  return $returnArray;
}

function NotifyFirstTaskOwner($dbh, $templateId, $issueId, $staffId, $issueOwnerId) {
  try {
    $staffId = trim($staffId);
    $issueOwnerId = trim($issueOwnerId);

    $taskEmails = array();
    $sql = "with ordered_trackitem as (select ti.issue_id, ti.item_id, ti.owner, ti.parent, ti.title, ti.item_desc,
              row_number() over (partition by ti.parent order by ti.parent, ti.view_order, ti.item_id) as number
              from cuissuesitem ti
              where ti.issue_id = $templateId)
            select sub.owner as sassigned_to, parent.owner as passigned_to, sub.title as stitle, parent.title as ptitle, sub.item_desc as sitem_desc,
              parent.item_desc as pitem_desc, sub.item_id as strackitem_id, parent.item_id as ptrackitem_id, t.owner, t.code
            from cuissues t
            left join ordered_trackitem parent on t.issue_id = parent.issue_id and parent.parent = 0 and parent.number = 1
            left join ordered_trackitem sub on t.issue_id = sub.issue_id and sub.parent = parent.item_id and sub.number = 1
            where t.issue_id = $templateId";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("first task owner query failed.", 1);
    }
    $hasTasks = false;
    $first = true;
    for($i = 0; $record = db_fetch_assoc($sth, $i); $i++) {
      if ($first) {
        $issue = isset($record["code"]) ? trim($record["code"]) : "";

        if ($record["strackitem_id"] != "") {
          if (isset($record["sassigned_to"])) {
            $nextTaskOwner = trim($record["sassigned_to"]);
          } else if (isset($record["passigned_to"])) {
            $nextTaskOwner = trim($record["passigned_to"]);
          } else {
            $nextTaskOwner = trim($issueOwnerId);
          }

          $title = $record["stitle"];
          $description = $record["sitem_desc"];
          $taskId = $record["strackitem_id"];
          $hasTasks = true;
        } else if ($record["ptrackitem_id"] != "") {
          $nextTaskOwner = isset($record["passigned_to"]) ? trim($record["passigned_to"]) : trim($issueOwnerId);
          $title = $record["ptitle"];
          $description = $record["pitem_desc"];
          $taskId = $record["ptrackitem_id"];
          $hasTasks = true;
        }

        $first = false;
      }
    }

    $sql = "update cutrack set previous_task_owner = '$nextTaskOwner' where track_id = $issueId";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("update cutrack failed.", 2);
    }

    if ($nextTaskOwner != "" && (strtolower($nextTaskOwner) != strtolower($issueOwnerId) || strtolower($nextTaskOwner) != strtolower($staffId))) {
        $text = "Job has been created.  You are the first task owner.";
        $results = CreateToLine($dbh, array($nextTaskOwner));
        $to = $results["data"];

        $subject = "You are the next task owner.";

        $taskEmails[] = array("to" => $to, "subject" => $subject, "text" => $text, "issue" => $issue, "title" => $title, "description" => $description,
                              "issueId" => $issueId);
    }

    $returnArray = array("status" => "000", "error" => "", "data" => array("taskEmails" => $taskEmails));
  } catch (exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }
  return $returnArray;
}

/* This will send an email to the next task owner if it is not the same as previously.  If it is the last task, then an email will be sent to the issue owner. */
function notifyNextTaskOwner($dbh, $issueId, $taskId, $staffId, $mode, $issueOwnerId, &$errors, &$transactionSQLs, &$taskEmails, &$issueEmails,
  $completeNotes = "", $forceEmail = false, $reassigned = false, $movedTaskId = 0, $movedAfter = false) {

    $issueOwnerId = trim($issueOwnerId);
    $staffId = trim($staffId);

    $sql = "select assigned_to, issue from cutrack where track_id = $issueId limit 1";
    $sth = db_query($sql,$dbh);
    $newError = trim(dbErrorNoFormatting());
    $gridData = array();
    if ($newError != "") {
        $errors[] = $newError;
        db_free_result($sth);
    } else {
        $dRecord = db_fetch_row($sth, 0);
        $issueOwnerId = $dRecord[0];
        $issue = $dRecord[1];
    }

  $sql = "select * from cutrackitem where track_id = $issueId order by parent, view_order, trackitem_id";
  $sth = db_query($sql,$dbh);
  $newError = trim(dbErrorNoFormatting());
  $gridData = array();
  if ($newError != "") {
      $errors[] = $newError;
      db_free_result($sth);
  } else {
      $records = array();
      $taskIndex = 0;
      $moveIndex = 0;
      $shownIndex = 0;
      for($i = 0; $dRecord = db_fetch_assoc($sth, $i); $i++) {
        $addRecord = $dRecord["complete_date"] == "" && $dRecord["task_status"] != 2;
        $thisTaskId = $dRecord["trackitem_id"];
        $parent = $dRecord["parent"];

        if ($thisTaskId == $taskId) {
          switch($mode) {
            case "deleted":
              $addRecord = false;
              break;
            case "rearranged":
              $taskIndex = $shownIndex;
              break;
            case "promoted":
              $dRecord["parent"] = 0;
              $promotedRecord = $dRecord;
              $addRecord = false;
              break;
            case "finished":
            case "inactivated":
              $addRecord = false;
              break;
            case "reactivated":
              $addRecord = true;
              break;
          }
        }

        if ($addRecord && $thisTaskId == $movedTaskId) {
          $moveIndex = $movedAfter ? $shownIndex + 1 : $shownIndex;
        }

        if ($addRecord) {
          $records[] = $dRecord;
          $shownIndex++;
        }

      }


      if ($mode == "rearranged") {
        $task = $records[$taskIndex];
        if ($taskIndex > $moveIndex) {
          array_splice($records, $taskIndex, 1);
          array_splice($records, $moveIndex, 0, array($task));
        } else {
          array_splice($records, $moveIndex, 0, array($task));
          array_splice($records, $taskIndex, 1);
        }
      } else if ($mode == "promoted") {
        $promoteIndex = -1;
        $i = 0;
        foreach($records as $record) {
          if ($record["parent"] != 0) {
            break;
          }

          if ($record["trackitem_id"] == $promotedRecord["parent"]) {
            $promoteIndex = $i + 1;
            break;
          }
          $i++;
        }
        array_splice($records, $promoteIndex, 0, array($promotedRecord));
      }

      $parentId = -1;
      $parentTask = null;
      $childTask = null;
      $parentFound = false;
      $childFound = false;

      foreach($records as $record) {
        $parent = intval($record["parent"]);
        $taskId = intval($record["trackitem_id"]);
        if ($parent == 0) {
          if (!$parentFound) {
            $parentFound = true;
            $parentTask = $record;
            $parentId = $taskId;
          }
        } else if ($parent == $parentId) {
          if (!$childFound) {
            $childFound = true;
            $childTask = $record;
          }
        }

        if ($parentFound && ($childFound || $parent > $parentId)) {
          break;
        }
      }
  }

  $record = isset($childTask) ? $childTask : (isset($parentTask) ? $parentTask : null);

  $title = trim($record["title"]);
  $description = trim($record["item_desc"]);
  $taskId = intval($record["trackitem_id"]);

  $nextTaskOwner = trim($childTask["assigned_to"]);
  if ($nextTaskOwner == "") {
    $nextTaskOwner = trim($parentTask["assigned_to"]);
  }
  if ($nextTaskOwner == "") {
    $nextTaskOwner = trim($issueOwnerId);
  }

    $sql = "select previous_task_owner from cutrack where track_id = $issueId";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      $errors[] = "Select query failed.";
    }
    $results = db_fetch_all($sth);
    $previousTaskOwner = $results === false ? "" : trim($results[0]["previous_task_owner"]);

    // Now we can send an email.
    if (isset($record)) {
      if ($forceEmail || (strtolower($nextTaskOwner) != strtolower($previousTaskOwner))) {
        $transactionSQLs[] = "update cutrack set previous_task_owner = '$nextTaskOwner' where track_id = $issueId";

        if ($mode == "new" && (strtolower($nextTaskOwner) != strtolower($issueOwnerId))) {
          if ($forceEmail || (strtolower($nextTaskOwner) != strtolower($staffId))) {
            $text = "Job has been created.  You are the first task owner.";

            try {
              $results = CreateToLine($dbh, array($nextTaskOwner));
              if ($results["status"] !== "000") {
                $errors[] = $results["error"];
              }
              $to = $results["data"];
            } catch(exception $e) {
              $errors[] = "createToLine query failed.";
            }

          }
        } else if ($mode == "rearranged" || $mode == "promoted") {
          $text = "Tasks were rearranged.  $nextTaskOwner is the next task owner, not $previousTaskOwner.";

          try {
            $results = CreateToLine($dbh, array($nextTaskOwner, $previousTaskOwner));
            if ($results["status"] !== "000") {
                $errors[] = $results["error"];
            }
           $to = $results["data"];
          } catch(exception $e) {
            $errors[] = "createToLine query failed.";
          }

        } else if ($mode == "reactivated") {
          $text = "A task has been reactivated.  $nextTaskOwner is the next task owner, not $previousTaskOwner.";

          try {
            $results = CreateToLine($dbh, array($nextTaskOwner, $previousTaskOwner));
            if ($results["status"] !== "000") {
                $errors[] = $results["error"];
            }
            $to = $results["data"];
          } catch(exception $e) {
            $errors[] = "createToLine query failed.";
          }

        } else {


          if ($forceEmail || (strtolower($nextTaskOwner) != strtolower($staffId))) { // Make sure that user doesn't already know.
            $text = "The task assigned to $previousTaskOwner has been $mode.  " . ($reassigned ? "The next task was reassigned to you." : "You are the next active task owner.");

            try {
              $results = CreateToLine($dbh, array($nextTaskOwner));
              if ($results["status"] !== "000") {
                  $errors[] = $results["error"];
              }
              $to = $results["data"];
            } catch(exception $e) {
              $errors[] = "createToLine query failed.";
            }

          }
        }

        if (count($errors) == 0 && isset($to)) {
          $subject = "You are the next task owner.";
          $taskEmails[] = array("to" => $to, "subject" => $subject, "text" => $text, "issue" => $issue, "title" => $title, "description" => $description,
            "issueId" => $issueId, "taskId" => $taskId, "completeNotes" => $completeNotes);
        }

      }
    } else {

      if ($forceEmail || ($mode != "created" && strtolower($issueOwnerId) != strtolower($staffId))) {
      // I guess that it doesn't make sense for the job to be already completed if it only has begun.
        $subject = "Your job is nearly complete.";

        try {
          $results = CreateToLine($dbh, array($issueOwnerId));
          if ($results["status"] !== "000") {
              $errors[] = $results["error"];
          }
          $to = $results["data"];
        } catch(exception $e) {
          $errors[] = "createToLine query failed.";
        }



        if (count($errors) == 0 && isset($to)) {
          $text = "Tasks have been completed for your job. Please review and complete this job.";
          $issueEmails[] = array("to" => $to, "subject" => $subject, "text" => $text, "issue" => $issue, "status" => $status, "issueId" => $issueId, "completeNotes" => $completeNotes);
        }

      }
    }
}