cuissues_data.prg

<?php
// File: cuissues_data.v2 (partially replaces cuissues_report)
// Contains functions for CRUD operations in the cuissues_edit.v2 and cuissues_report.v2 files and functions for sending emails.
// SPB 10/6/2015-- Replaced previous cuissues_report for kendo upgrade and billing workflow expansion.

$monLibrary = dirname(__FILE__) . "/../library";
$sharedLibrary = dirname(__FILE__) . "/../../shared/library";
$billingLibrary = dirname(__FILE__) . "/../../billing/library";
require_once("$monLibrary/cu_top.i");
require_once("$monLibrary/ck_hticket.i");
require_once("$monLibrary/monitorView.i");
require_once("$sharedLibrary/commonPhpFunctions.i");

require_once("$sharedLibrary/errormail.i");
require_once("$billingLibrary/billingEmailFunctions.i");
require_once("$billingLibrary/configuration.i");

if (!CheckPerm($link, $Hu, basename($_SERVER['SCRIPT_NAME']), $_SERVER['REMOTE_ADDR'])) {
  // ** Permissions failed
  // ** redirect to new page
  header("Location: /hcuadm/hcu_noperm.prg");
  exit;
}

dms_import_v2($DATA_PARAMETERS, "TOP_LEVEL", array("operation" => "string"));
$operation = $DATA_PARAMETERS["TOP_LEVEL"]["operation"];

switch ($operation) {
    case "readCuissues":
        $returnArray = ReadCuissues($dbh);
        break;
  case "readIssueEdit":
    $returnArray = ReadIssueEdit($dbh, $Hu);
    break;
  case "readIssueAdd":
    $returnArray = ReadIssueAdd($dbh);
    break;
  case "saveIssueAdd":
    $returnArray = SaveIssue($dbh, $Hu, true);
    break;
  case "saveIssueEdit":
    $returnArray = SaveIssue($dbh, $Hu, false);
    break;
  case "addNewContact":
    $returnArray = AddNewContact($dbh);
    break;
  case "getComments":
    $returnArray = GetComments($dbh, $Hu);
    break;
  case "readTasks":
    $returnArray = ReadTasks($dbh);
    break;
  case "createTask":
    $returnArray = ModifyTask($dbh, true, $Hu);
    break;
  case "updateTask":
    $returnArray = ModifyTask($dbh, false, $Hu);
    break;
  case "deleteTask":
    $returnArray = DeleteTask($dbh, $Hu);
    break;
  case "changeTaskStatus":
    $returnArray = ChangeTaskStatus($dbh, $Hu);
    break;
  case "reorderTasks":
    $returnArray = ReorderTasks($dbh, $Hu);
    break;
  case "addTaskComment":
    $returnArray = ModifyTaskComment($dbh, $Hu, true);
    break;
  case "updateTaskComment":
    $returnArray = ModifyTaskComment($dbh, $Hu, false);
    break;
  case "promoteTask":
    $returnArray = PromoteTask($dbh, $Hu);
    break;
  case "addIssueFromTemplate":
    $returnArray = AddIssueFromTemplate($dbh, $Hu);
    break;
  case "deleteTaskComment":
    $returnArray = DeleteTaskComment($dbh);
    break;
  case "pullInTaskTemplates":
    $returnArray = PullInTaskTemplates($dbh);
    break;
  case "setReportCookie":
    $returnArray = SetReportCookie($SYSENV);
    break;
  case "clearReportCookie":
    $returnArray = ClearReportCookie($SYSENV);
    break;
    default: $returnArray = array("error" => array("Operation not specified: '$operation'"), "record" => "", "operation" => "");
}

header('Content-type: application/json');
print HCU_JsonEncode($returnArray);

/////////////////////////////////////
// Literals

// Gets state model for issue statuses.
function GetIssueStatuses() {
  return array(
               array("value" => "Active", "currentStatus" => "Active"),
               array("value" => "Suspended", "currentStatus" => "Active"),
               array("value" => "Completed", "currentStatus" => "Active"),
               array("value" => "Deleted", "currentStatus" => "Active"),
               array("value" => "Suspended", "currentStatus" => "Suspended"),
               array("value" => "Active", "currentStatus" => "Suspended"),
               array("value" => "Deleted", "currentStatus" => "Suspended"),
               array("value" => "Completed", "currentStatus" => "Completed"),
               array("value" => "Active", "currentStatus" => "Completed"),
               array("value" => "Suspended", "currentStatus" => "Completed"),
               array("value" => "Deleted", "currentStatus" => "Completed"),
               array("value" => "Deleted", "currentStatus" => "Deleted"),
               array("value" => "Active", "currentStatus" => "Deleted"),
               array("value" => "Unrecognized", "currentStatus" => "Unrecognized"),
               array("value" => "Active", "currentStatus" => "Unrecognized"),
               array("value" => "Suspended", "currentStatus" => "Unrecognized"),
               array("value" => "Completed", "currentStatus" => "Unrecognized"),
               array("value" => "Deleted", "currentStatus" => "Unrecognized"));
}

// Get task statuses.  Values are in order that they appear in the tasks.
function GetTaskStatuses() {
  return array("1" => "TODO", "0" => "Done", "2" => "N/A");
}

// Gets the contact roles from cu_top.
function GetContactRoles() {
  global $cu_contact_role; // from cu_top
  $roles = array();
  foreach($cu_contact_role as $key => $value) {
    $roles[] = array("text" => $value, "value" => $key);
  }
  return $roles;
}
// End Literals
////////////////////////////////////

/* Retrieves list of issues and the next task needing to be completed. */
function ReadCuissues($dbh)
{
    $parameters = array();
    dms_import_v2($parameters, "BOTTOM_LEVEL", array("cu" => "string", "showCompleted" => "string", "setDDLs" => "string", "showDeleted" => "string"));



    $sqls= array();
    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("cu" => array("type" => "string", "required" => false, "maxlength" => 12),
          "showCompleted" => array("type" => "boolean", "required" => false),
          "showDeleted" => array("type" => "boolean", "required" => false),
          "setDDLs" => array("type" => "boolean", "required" => false)));

    $cu= strtoupper($parameters["BOTTOM_LEVEL"]["cu"]);
    $showCompleted= $parameters["BOTTOM_LEVEL"]["showCompleted"] == "Y";
    $showDeleted= $parameters["BOTTOM_LEVEL"]["showDeleted"] == "Y";
    $setDDLs= $parameters["BOTTOM_LEVEL"]["setDDLs"] == "Y";

    $where= array();
    $notStatusArray= array();
    $completeDatePiece= "";
    if (!$showCompleted)
    {
      $completeDatePiece= "and t.complete_date is null";
      $notStatusArray[]= "Completed";
      $notStatusArray[]= "Billed";
    }
    if (!$showDeleted)
      $notStatusArray[]= "Deleted";
    if (count($notStatusArray) > 0)
      $where[]= "t.status not in ('" . implode("', '", $notStatusArray) . "') $completeDatePiece";
    if ($cu != "")
      $where[]= "upper(t.user_name) = '$cu'";
    $whereSQL= count($where) == 0 ? "" : "where (" . implode(") and (", $where) . ")";

    $sql= "select t.track_id, temp.number, t.user_name, t.issue, t.status, t.assigned_to, t.contact, t.entry_date, t.last_activity_date, i.name, temp.title, temp.assigned_to as tassigned_to,
          t.target_date, t.target_date_type, t.billing_feature_id, t.drop_dead_date, t.issue_id from cutrack t inner join cuinfo i on lower(t.user_name)= lower(i.user_name)
          left join (select row_number() over (partition by ti.track_id, ti.parent order by ti.view_order, ti.trackitem_id) as number, ti.title, ti.assigned_to, ti.track_id
          from cutrackitem ti where ti.task_status <> 2 and ti.complete_date is null and ti.parent = 0) temp on t.track_id= temp.track_id and temp.number = 1 $whereSQL";
    $sqlResults= runSelectStatement($sql, $dbh, array("track_id" => "trackId", "user_name" => "cuCode", "issue" => "issue", "assigned_to" => "issueOwner", "entry_date" => "entryDate",
                    "target_date_type" => "targetDateType", "last_activity_date" => "lastActivityDate", "name" => "cuName", "title" => "nextTask", "tassigned_to" => "nextTaskOwner",
                    "status" => "status", "target_date" => "targetDate", "billing_feature_id" => "billingFeatureId", "drop_dead_date" => "dropDeadDate", "issue_id" => "issueId"));

    $cloneSQL= "select distinct ti.track_id, '|cloneJob|' from cutrackitem ti where lower(ti.title) like '%clone a server%' and ti.complete_date is null and ti.task_status <> 2";
    $cloneMapResults= runSelectStatementMap($cloneSQL, $dbh);

    $unattachedSQL= "select t.track_id, '|unattached|' from cutrack t left join cubillfeaturedetail fd on t.billing_feature_id = fd.id where fd.id is null";
    $unattachedMapResults= runSelectStatementMap($unattachedSQL, $dbh);

    $records= $sqlResults["record"];
    for($i=0, $count=count($records); $i != $count; $i++)
    {
      $trackId= $records[$i]["trackId"];
      $issueId= $records[$i]["issueId"];

      switch(trim(strtoupper($records[$i]["targetDateType"])))
      {
        case "":
          $records[$i]["targetDateTypeSort"]= 1;
          break;
        case "TBD":
          $records[$i]["targetDateTypeSort"]= 3;
          break;
        case "FLEX":
          $records[$i]["targetDateTypeSort"]= 2;
          break;
        default:
          $records[$i]["targetDateTypeSort"]= 4; // Won't get here except for bad data.
      }

      /* Numbers are the particular issues that are determined to be implementation-based issues:
       * 35-- Batch Home Banking Setup, 10-- VR Cloud Based Setup VOIP, 20-- Live HomeBanking Setup, 5-- Upgrade Batch to Live
       */
      if (HCU_array_key_exists($trackId, $cloneMapResults['record'])) {
        $records[$i]["jobType"]= strval($cloneMapResults["record"][$trackId]) . strval($unattachedMapResults["record"][$trackId]) . (in_array($issueId, array(35, 10, 20, 5)) ? "|imp|" : "");
      }
    }

    $ddlSQL= "select issue_id, code from cuissues order by code";
    $ddlResults= runSelectStatement($ddlSQL, $dbh, array("issue_id" => "value", "code" => "text"));

    global $SYS_TYPE_CLOSED; // From cu_top
    $cuSQL= "select i.user_name, i.name from cuinfo i where i.system_options & $SYS_TYPE_CLOSED = 0 order by i.user_name";
    $cuResults= runSelectStatement($cuSQL, $dbh, array("user_name" => "value", "name" => "cuName"));

    $errors= array_merge($errors, $sqlResults["error"], $cloneMapResults["error"], $ddlResults["error"], $cuResults["error"]);
    $sqls= array($sql, $cloneSQL, $unattachedSQL, $ddlSQL, $cuSQL);

    $cuDDL= array();
    foreach($cuResults["record"] as $record)
    {
      $record["text"]= $record["cuName"] . " (" . $record["value"] . ")";
      $cuDDL[]= $record;
    }

    return array("error" => $errors, "sql" => $sqls, "operation" => "read", "record" => $records, "templateDDL" => $ddlResults["record"], "cuDDL" => $cuDDL);
}

/* Retrieves DDLs and data needed for a preexisting issue.  Will also retrieve comments on that issue by the logged in user. */
function readIssueEdit($dbh, $staffId)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL", array("trackId" => "string", "cu" => "string"));

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("trackId" => array("type" => "int", "required" => true)));
  $trackId= $parameters["BOTTOM_LEVEL"]["trackId"];

  $sql= "select t.track_id, t.user_name, t.issue, t.status, t.assigned_to, t.entry_date, t.last_activity_date, t.next_contact_date, t.contact_id, i.name, t.issue_id, t.contact, t.contact_phone,
        t.contact_email, t.notes, t.target_date, t.complete_date, t.target_date_type, i.www_server, i.vendor, t.billing_feature_id, t.drop_dead_date, ci.gen_for_product, fd.id as test_id,
        t.billed
        from cutrack t inner join cuinfo i on lower(t.user_name)= lower(i.user_name) left join cuissues ci on t.issue_id = ci.issue_id
        left join cubillfeaturedetail fd on t.billing_feature_id = fd.id
        where t.track_id= $trackId";
  $results= runSelectStatement($sql, $dbh, array("track_id" => "trackId", "name" => "cuName", "user_name" => "cu", "issue" => "issue", "status" => "status", "assigned_to" => "issueOwner",
                                                 "last_activity_date" => "lastActivity", "entry_date" => "enteredOn", "target_date" => "targetDate", "complete_date" => "completedOn",
                                                 "contact_id" => "contactId", "target_date_type" => "targetDateType", "issue_id" => "templateId", "www_server" => "server", "vendor" => "vendor",
                                                 "contact" => "contactName", "contact_phone" => "contactPhone", "contact_email" => "contactEmail", "notes" => "oldComments",
                                                 "billing_feature_id" => "billingFeatureId", "drop_dead_date" => "dropDeadDate", "gen_for_product" => "product", "billed" => "billed",
                                                 "test_id" => "testId"));
  $errors= array_merge($errors, $results["error"]);

  if (count($errors) == 0 && count($results["record"]) == 0)
  {
    $errors[]= "Issue with track id provided does not exist.";
    $cu= "";
    $record= array();
  }
  else
  {
    $record= $results["record"][0];
    $cu= $record["cu"];
  }

  $commentsSQL= "select row_number() over(order by messageid) as number, n.messageid, n.author, n.createddate, n.messagetext, n.activitydate, n.trackitem_id, ti.title from cuinfo_notes n
                 left join cutrackitem ti on n.trackitem_id = ti.trackitem_id where n.track_id = $trackId";

  $results= runSelectStatement($commentsSQL, $dbh,
    array("messageid" => "commentId", "author" => "author", "createddate" => "createdDate", "messagetext" => "text", "number" => "mockCommentId",
          "trackitem_id" => "trackItemId", "title" => "trackItemName"));

  $comments= $results["record"];
  for($i=0, $count=count($comments); $i != $count; $i++)
  {
    $comments[$i]["groupId"]= intval($comments[$i]["trackItemId"]) == 0 ? 0 : 1;
  }
  $errors= array_merge($errors, $results["error"]);

  $returnArray= array("error" => $errors, "sql" => array($sql, $commentsSQL), "operation" => "read", "record" => $record, "comments" => $comments);
  getIssueDDLs($dbh, $returnArray, $cu, $record["contactName"], $record["contactPhone"], $record["contactEmail"], $record["product"]);

  $returnArray["selectSpecial"]= ($record["contactName"] != "" && $record["contactId"] == "0") || ($record["contactId"] == "-1");

  return $returnArray;
}

/* Retrieves DDLs needed for a new issue. */
function readIssueAdd($dbh)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL", array("cu" => "string"));

  $sqls= array();
  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"], array("cu" => array("type" => "string", "required" => false, "maxlength" => 12)));
  $returnArray= array("error" => $errors, "sql" => array(), "operation" => "read");
  $cu= $parameters["BOTTOM_LEVEL"]["cu"];
  getIssueDDLs($dbh, $returnArray, $cu);
  $returnArray["selectSpecial"]= false;
  return $returnArray;
}

/* Gets a list of CUs that are active plus the current Cu, owners, contacts, roles, issue and task statuses, and the name of the cu. */
function getIssueDDLs($dbh, &$returnArray, $cu, $contactName= "", $contactPhone= "", $contactEmail= "", $product= "")
{
  global $SYS_TYPE_CLOSED; // From cu_top
  $cu= strtoupper($cu);
  $cuSQL= "select i.user_name, i.name, i.www_server, i.vendor from cuinfo i where upper(i.user_name) = '$cu' or i.system_options & $SYS_TYPE_CLOSED = 0 order by i.user_name";
  $cuResults= runSelectStatement($cuSQL, $dbh, array("user_name" => "value", "name" => "text", "www_server" => "server", "vendor" => "vendor"));

  $ownerSQL= "select u.user_name from dmsmonitorusers u order by lower(u.user_name)";
  $ownerResults= runSelectStatement($ownerSQL, $dbh, array("user_name" => "value"));

  $contactSQL= "select c.id, coalesce(c.fname, '') || ' ' || coalesce(c.lname, '') as full_name, c.phone, c.email, c.user_name from monitor_contact c";
  if ($cu != "")
    $contactSQL.= " where upper(c.user_name) = '$cu'";
  $contactResults= runSelectStatement($contactSQL, $dbh, array("id" => "contactId", "full_name" => "text", "phone" => "phone", "email" => "email", "user_name" => "cu"));

  $contactDDL= $contactResults["record"];

  for($i=0, $count=count($contactDDL); $i != $count; $i++)
  {
    $contactDDL[$i]["sortNumber"]= 3;
  }

  array_unshift($contactDDL,
    array("contactId" => "", "text" => "NONE", "fullName" => "", "phone" => "", "email" => "", "cu" => "", "sortNumber" => 0),
    array("contactId" => "", "text" => "NEW", "fullName" => "", "phone" => "", "email" => "", "cu" => "", "sortNumber" => 1),
    array("contactId" => "-1", "text" => "SPECIAL", "fullName" => $contactName, "phone" => $contactPhone, "email" => $contactEmail, "cu" => "", "sortNumber" => 2));

  $ownerDDL= $ownerResults["record"];
  for($i=0, $count=count($ownerDDL); $i != $count; $i++)
  {
    $ownerDDL[$i]["text"]= $ownerDDL[$i]["value"];
    $ownerDDL[$i]["filterNumber"]= 0;
  }

  array_unshift($ownerDDL, array("value" => "", "text" => "(Job Owner)", "filterNumber" => 1), array("value" => "", "text" => "(Parent Owner)", "filterNumber" => 2));

  $returnArray["error"]= array_merge($returnArray["error"], $cuResults["error"], $ownerResults["error"], $contactResults["error"]);
  $returnArray["cuDDL"]= $cuResults["record"];
  $returnArray["ownerDDL"]= $ownerDDL;
  $returnArray["statusDDL"]= getIssueStatuses();
  $returnArray["contactDDL"]= $contactDDL;
  $returnArray["roleDDL"]= getContactRoles();

  $taskStatusDDL= array();
  foreach(getTaskStatuses() as $value => $text)
  {
    $taskStatusDDL[]= array("text" => $text, "value" => $value);
  }
  $returnArray["taskStatusDDL"]= $taskStatusDDL;

  $unattachedFeatureDetailsSQL= "select fd.id, si.display_name, coalesce(p.home_cu_desc, cd.prod_id) as prod_desc, c.cu_id, si.billing_system_id from cubillfeaturedetail fd
    inner join cubillsalesorderdetail cd on fd.sales_order_detail_id = cd.id inner join cubillsalesorder c on cd.sales_order_id = c.id inner join cubillsalesitem si on fd.sales_item_id = si.id
    left join cuprodlist p on si.prod_id = p.home_cu_code left join cutrack t on fd.id = t.billing_feature_id";
  $where= array("t.track_id is null");
  if ($cu != "")
    $where[]= "upper(c.cu_id) = '$cu'";
  if ($product != "")
    $where[]= "cd.prod_id = '$product'";
  $unattachedFeatureDetailsSQL.= " where (" . implode(") and (", $where) . ")";

  $returnArray["sql"]= array_merge($returnArray["sql"], array($cuSQL, $ownerSQL, $contactSQL, $unattachedFeatureDetailsSQL));

  $results= runSelectStatement($unattachedFeatureDetailsSQL, $dbh, array("id" => "value", "display_name" => "itemDesc", "prod_desc" => "prodDesc", "cu_id" => "cu",
    "billing_system_id" => "billingSystemId"));
  $returnArray["error"]= array_merge($returnArray["error"], $results["error"]);
  $sqls[]= $unattachedFeatureDetailsSQL;
  $unattachedFeatureDetailsDDL= array();
  $setupRecurring= getSetupRecurring();
  foreach($results["record"] as $record)
  {
    $isSetup= trim($setupRecurring[$record["billingSystemId"]]) == "setup";
    if ($isSetup) // Workflows are only attached to setups.
      $unattachedFeatureDetailsDDL[]= array("text" => strval($record["prodDesc"]) . ": " . strval($record["itemDesc"]), "value" => $record["value"], "cu" => strtolower($record["cu"]));
  }
  array_unshift($unattachedFeatureDetailsDDL, array("text" => "(None)", "value" => "", "cu" => ""));
  $returnArray["unattachedFeatureDetailsDDL"]= $unattachedFeatureDetailsDDL;

  if ($cu != "")
  {
    foreach($cuResults["record"] as $record)
    {
      if (strtoupper($record["value"]) == $cu)
      {
        $returnArray["cuName"]= $record["text"];
        break;
      }
    }
  }
}

/* Saves the issue.  If new, it will get all columns and insert.  If preexisting, it will only update the columns changed.  Any new comments will be added, any changed comments updated, and
any deleted comments deleted.  If the status of the issue changes to Active, email will be sent off to the next task owner (if that isn't the same as before and it isn't the logged in user.)
If the status of the issue changes to Complete, corresponding feature in billing will advance to Active if applicable. */
function saveIssue($dbh, $staffId, $isAdd)
{
  $parameters= array();

  $allowed= array("contactId" => "string", "issue" => "string", "status" => "string", "owner" => "string", "targetDate" => "string", "comments" => "string", "deletedCommentIds" => "string",
    "cu" => "string", "cuName" => "string", "targetDateType" => "string", "contactName" => "string", "contactPhone" => "string", "contactEmail" => "string", "billingFeatureId" => "string",
    "dropDeadDate" => "string");
  if (!$isAdd)
  {
    $allowed["changedIssue"]= "string";
    $allowed["changedStatus"]= "string";
    $allowed["changedOwner"]= "string";
    $allowed["targetDateChanged"]= "string";
    $allowed["trackId"]= "digits";
    $allowed["changedContactId"]= "string";
    $allowed["changedDropDeadDate"]= "string";
  }

  dms_import_v2($parameters, "BOTTOM_LEVEL", $allowed);

  $cu= strtolower($parameters["BOTTOM_LEVEL"]["cu"]);
  $cuName= $parameters["BOTTOM_LEVEL"]["cuName"];
  $issue= $parameters["BOTTOM_LEVEL"]["issue"];
  $status= $parameters["BOTTOM_LEVEL"]["status"];
  $comments= $parameters["BOTTOM_LEVEL"]["comments"];

  $sqls= array();
  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("cu" => array("type" => "string", "required" => true, "maxlength" => 12),
          "cuName" => array("type" => "string", "required" => true, "maxlength" => 40),
          "contactId" => array("type" => "int", "required" => false, "allowNegatives" => true),
          "issue" => array("type" => "string", "required" => true, "maxlength" => 90),
          "status" => array("type" => "string", "required" => true, "maxlength" => 40),
          "owner" => array("type" => "string", "required" => true, "maxlength" => 30),
          "targetDate" => array("type" => "date", "required" => false),
          "trackId" => array("type" => "int", "required" => true),
          "deletedCommentIds" => array("type" => "string", "required" => false),
          "changedIssue" => array("type" => "boolean", "required" => false),
          "changedStatus" => array("type" => "boolean", "required" => false),
          "changedOwner" => array("type" => "boolean", "required" => false),
          "targetDateChanged" => array("type" => "boolean", "required" => false),
          "changedContactId" => array("type" => "boolean", "required" => false),
          "targetDateType" => array("type" => "string", "required" => false, "maxlength" => 10),
          "contactName" => array("type" => "string", "required" => false, "maxlength" => 80),
          "contactPhone" => array("type" => "phone", "required" => false, "maxlength" => 20),
          "contactEmail" => array("type" => "email", "required" => false, "maxlength" => 50),
          "billingFeatureId" => array("type" => "int", "required" => false),
          "dropDeadDate" => array("type" => "date", "required" => false),
          "changedDropDeadDate" => array("type" => "boolean", "required" => false)));
  $cuClean= strtolower($parameters["BOTTOM_LEVEL"]["cu"]);
  $contactId= $parameters["BOTTOM_LEVEL"]["contactId"];
  $issueClean= $parameters["BOTTOM_LEVEL"]["issue"];
  $statusClean= $parameters["BOTTOM_LEVEL"]["status"];
  $owner= $parameters["BOTTOM_LEVEL"]["owner"];
  $targetDate= strval($parameters["BOTTOM_LEVEL"]["targetDate"]);
  $trackId= isset($parameters["BOTTOM_LEVEL"]["trackId"]) ? intval($parameters["BOTTOM_LEVEL"]["trackId"]) : 0;
  $deletedCommentIds= $parameters["BOTTOM_LEVEL"]["deletedCommentIds"];
  $changedIssue= isset($parameters["BOTTOM_LEVEL"]["changedIssue"]) ? ($parameters["BOTTOM_LEVEL"]["changedIssue"] == "Y") : false;
  $changedStatus= isset($parameters["BOTTOM_LEVEL"]["changedStatus"]) ? ($parameters["BOTTOM_LEVEL"]["changedStatus"] == "Y") : false;
  $changedOwner= isset($parameters["BOTTOM_LEVEL"]["changedOwner"]) ? ($parameters["BOTTOM_LEVEL"]["changedOwner"] == "Y") : false;
  $changedTargetDate= isset($parameters["BOTTOM_LEVEL"]["targetDateChanged"]) ? ($parameters["BOTTOM_LEVEL"]["targetDateChanged"] == "Y") : false;
  $changedContactId= isset($parameters["BOTTOM_LEVEL"]["changedContactId"]) ? ($parameters["BOTTOM_LEVEL"]["changedContactId"] == "Y") : false;
  $targetDateType= strval($parameters["BOTTOM_LEVEL"]["targetDateType"]);
  $contactPhone= $parameters["BOTTOM_LEVEL"]["contactPhone"];
  $contactEmail= $parameters["BOTTOM_LEVEL"]["contactEmail"];
  $contactName= $parameters["BOTTOM_LEVEL"]["contactName"];
  $billingFeatureId= $parameters["BOTTOM_LEVEL"]["billingFeatureId"];
  $dropDeadDate= isset($parameters["BOTTOM_LEVEL"]["dropDeadDate"]) ? $parameters["BOTTOM_LEVEL"]["dropDeadDate"] : "";
  $changedDropDeadDate= isset($parameters["BOTTOM_LEVEL"]["changedDropDeadDate"]) ? ($parameters["BOTTOM_LEVEL"]["changedDropDeadDate"] == "Y") : false;

  if (false === array_search($targetDateType, array("", "TBD", "FLEX")))
    $errors[]= "Target Date Type is not recognized.";

  $contactId= $contactId == "" ? "null" : intval($contactId);
  $targetDate = $targetDate == "" ? "null" : "'$targetDate'";
  $targetDateType= $targetDateType == "" ? "null" : "'$targetDateType'";
  $contactPhone= $contactPhone == "" ? "null" : "'$contactPhone'";
  $contactEmail= $contactEmail == "" ? "null" : "'$contactEmail'";
  $contactName= $contactName == "" ? "null" : "'$contactName'";
  $dropDeadDate= $dropDeadDate == "" ? "null" : "'$dropDeadDate'";

  $transactionSQLs= array();
  $issueEmails= array();
  if ($isAdd)
  {
    // Either Target Date and Target Date Type are exclusive.
    if (($targetDate == "null" && $targetDateType == "null") || ($targetDate != "null" && $targetDateType != "null"))
      $errors[]= "Target Date and Target Date Type are out of sync.";

      $execSQL= "select nextval('cutrack_track_id_seq'::text::regclass) as next";
      $sqls[]= $execSQL;
      $execResults= runSelectStatement($execSQL, $dbh, array("next" => "next"));
      $trackId= intval($execResults["record"][0]["next"]);
      $errors= array_merge($errors, $execResults["error"]);

      $transactionSQLs[]= "insert into cutrack (track_id, user_name, issue, status, assigned_to, contact_id, contact, contact_phone, contact_email, target_date, target_date_type,
        drop_dead_date, entry_date, last_activity_date) values ($trackId, '$cuClean', '$issueClean', '$statusClean', '$owner', $contactId, $contactName, $contactPhone, $contactEmail, $targetDate,
        $targetDateType, $dropDeadDate, now(), now())";
  }
  else
  {
      $nowActive= false;
      $nowComplete= false;
      $nowDelete= false;

      // Will only update what has been changed.
      $updateList= array();
      if ($changedContactId)
      {
        $updateList[]= "contact_id= $contactId";
        if ($contactId == "null" || $contactId == "-1")
        {
          $updateList[]= "contact= $contactName";
          $updateList[]= "contact_phone= $contactPhone";
          $updateList[]= "contact_email= $contactEmail";
        }
      }

      if ($changedIssue)
        $updateList[]= "issue= '$issueClean'";
      if ($changedStatus)
      {
        $updateList[]= "status= '$statusClean'";
        if ($status == "Active")
          $nowActive= true;
        else if ($status == "Completed")
        {
          $nowComplete= true;
          $updateList[]= "complete_date= now()";
        }
        else if ($status == "Deleted")
        {
          $nowDelete= true;
          $updateList[]= "billing_feature_id=0"; // Unlink billing

          $startOfMonth= new DateTime();
          $startOfMonth= $startOfMonth->format("Y-m-01");
          $transactionSQLs[]= // Set the feature to "No Workflow" but only if it is the pending state.  If it already active, then ignore.
          // If the feature has already been partially billed, set it to status of 5 instead.  ASSUMPTION: if the date is past, then it has already been billed.
          "update cubillfeaturedetail set billing_status= case when partially_billed_date is not null and partially_billed_date < '$startOfMonth' then
          5 else 4 end from cutrack t where billing_status=1 and cubillfeaturedetail.id= t.billing_feature_id and t.track_id= $trackId";
        }

        if (!$nowComplete && !$nowDelete)
          $updateList[]= "complete_date= null"; // clear this out when job was completed at some time but then the status was changed.  Job Report query will filter out these jobs otherwise.
      }

      if ($changedOwner)
        $updateList[]= "assigned_to = '$owner'";

      if ($changedDropDeadDate)
        $updateList[]= "drop_dead_date = $dropDeadDate";
      if ($changedTargetDate)
      {
        // Either Target Date and Target Date Type are exclusive.
        if (($targetDate == "null" && $targetDateType == "null") || ($targetDate != "null" && $targetDateType != "null"))
           $errors[]= "Target Date and Target Date Type are out of sync.";
        $updateList[]= "target_date = $targetDate, target_date_type = $targetDateType";
      }
      if ($billingFeatureId != "")
      {
        $updateList[]= "billing_feature_id=$billingFeatureId";
        $transactionSQLs[]= "update cubillfeaturedetail set billing_status= case when billing_status = 5 or partially_billed_date is null then 1 else 0 end where id= $billingFeatureId";
        // Update from status 4: no workflow to 1: pending.

        // Need to update the billing_ids of the triggered sales items.
        $transactionSQLs[]= "update cutrackitem set billing_item_id= coalesce(fd.id, 0) from cutrackitem ti inner join cutrack t on ti.track_id= t.track_id and t.track_id= $trackId
          left join cuissuesitem ii on ti.issue_item_id= ii.item_id and ii.issue_id= t.issue_id left join cubillfeaturedetail tfd on tfd.id= $billingFeatureId left join cubillfeaturedetail fd
          on fd.sales_order_detail_id= tfd.sales_order_detail_id and fd.sales_item_id= ii.sales_item_id where ti.trackitem_id= cutrackitem.trackitem_id";
      }


      if (count($updateList) != 0 || $comments != "" || $comments != "[]" || $deletedCommentIds != "" || $deletedCommentIds != "[]")
      {
        $updateList[]= "last_activity_date= now()";
        $transactionSQLs[]= "update cutrack set " . implode(", ", $updateList) . " where track_id= $trackId";

          if ($nowComplete)
          {
            updateBilling($dbh, $trackId, $transactionSQLs);

            $sql= "select trackitem_id, billing_item_id from cutrackitem where track_id = $trackId and billing_item_id <> 0 and complete_date is null";
            $results= runSelectStatementMap($sql, $dbh);
            $sqls[]= $sql;
            $errors= array_merge($errors, $results["error"]);

            foreach($results["record"] as $key => $value)
            {
              updateBillingTaskTrigger($dbh, "", $value, $transactionSQLs);
            }
          }

          $dateChangeText= array();
          if ($changedDropDeadDate)
          {
            $date= new DateTime(str_replace("'", "", $dropDeadDate));
            $dateChangeText[]= "<b>Drop Dead Date</b> was changed to " . $date->format("m/d/Y") . ".";
          }
          if ($changedTargetDate)
          {
            if ($targetDateType != "null")
              $formattedDate= $targetDateType;
            else
            {
              $date= new DateTime(str_replace("'", "", $targetDate));
              $formattedDate= $date->format("m/d/Y");
            }
            $dateChangeText[]= "<b>Target Date</b> was changed to $formattedDate.";
          }
          if (count($dateChangeText) > 0 && $staffId != $owner) {
            $results = CreateToLine($dbh, array(trim($owner)));
            if ($results["status"] !== "000") {
              $errors[] = $results["error"];
            }
            $issueEmails[] = array("to" => $results["data"], "subject" => "Job dates were changed!", "text" => implode("<br />", $dateChangeText),
                                   "issueName" => $issueClean, "issueId" => $trackId, "status" => $status);
          }
        }
      }

  if ($comments != "")
  {
    $comments= json_decode($comments, true);
    if (is_array($comments))
    {
      foreach($comments as $comment)
      {
        $theseErrors= cleanValuesForDatabase($comment,
          array("commentId" => array("type" => "int", "required" => false),
                "text" => array("type" => "string", "required" => false)));
        $commentId= intval($comment["commentId"]);
        $text= $comment["text"];

        $text= $text == "" ? "null" : "'$text'";

        $errors= array_merge($errors, $theseErrors);

        if ($commentId == 0)
          $transactionSQLs[]= "insert into cuinfo_notes (messagetext, cu, author, createddate, activitydate, trackitem_id, track_id) values ($text, '$cu', '$staffId', now(), now(), 0, $trackId)";
        else
          $transactionSQLs[]= "update cuinfo_notes set messagetext= $text, activitydate= now() where messageid= $commentId";
      }
    }
    else
    {
      $errors[]= "Comments do not form a valid array";
    }
  }

  if ($deletedCommentIds != "")
  {
    $idArray= json_decode($deletedCommentIds, true);
    if (is_array($idArray))
    {
      if (count($idArray) > 0)
      {
        foreach($idArray as $id)
        {
          $cleanedId= cleanInteger($id);
          if ($cleanedId == "ERROR")
          {
            $errors[]= "deletedCommentIds has some invalid ids";
            break;
          }
        }

        $transactionSQLs[]= "delete from cuinfo_notes where messageid in (" . implode(", ", $idArray) . ")";
      }
    }
    else
      $errors[]= "ignoreIds is not a valid array";
  }

  // Create issue owner email
  if (strtolower($staffId) != strtolower($owner)) {
      if ($isAdd || $changedOwner) {

        $results = CreateToLine($dbh, array(trim($owner)));
        if ($results["status"] !== "000") {
          $errors[] = $results["error"];
        }
        $subject = "Owner changed!";
        $text = "Congratulations!  You are the next owner of the job!";
        $issueEmails[] = array("to" => $results["data"], "subject" => $subject, "text" => $text,
                               "issueName" => $issue, "issueId" => $trackId, "status" => $status);
      }
  }

  $sqls= array_merge($sqls, $transactionSQLs);
  if (count($errors) == 0) {
    $results = runExecStatement(implode(";", $transactionSQLs) . ";", $dbh);
    $errors = array_merge($errors, $results["error"]);
    if (count($errors) == 0) {
        foreach($issueEmails as $issueEmail) {
            CreateIssueEmail($issueEmail["to"], $issueEmail["subject"], $issueEmail["text"], $issueEmail["issueName"], $issueEmail["status"],
                             $cuName, $cu, $issueEmail["issueId"]);
        }
    }
  }




  return array("error" => $errors, "sql" => $sqls, "trackId" => $trackId);
}

/* Will add a new contact in the monitor contact table. */
function addNewContact($dbh)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL",
    array("firstName" => "string", "lastName" => "string", "role" => "string", "phone" => "string", "email" => "string", "comment" => "string", "decider" => "string", "cu" => "string"));

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("firstName" => array("type" => "string", "required" => true, "maxlength" => 40),
          "lastName" => array("type" => "string", "required" => true, "maxlength" => 25),
          "role" => array("type" => "string", "required" => true, "maxlength" => 10),
          "phone" => array("type" => "phone", "required" => false),
          "email" => array("type" => "email", "required" => false, "maxlength" => 50),
          "decider" => array("type" => "boolean", "required" => false),
          "comment" => array("type" => "string", "required" => false),
          "cu" => array("type" => "string", "required" => true, "maxlength" => 12)));

  $firstName= $parameters["BOTTOM_LEVEL"]["firstName"];
  $lastName= $parameters["BOTTOM_LEVEL"]["lastName"];
  $role= $parameters["BOTTOM_LEVEL"]["role"];
  $phone= $parameters["BOTTOM_LEVEL"]["phone"];
  $email= $parameters["BOTTOM_LEVEL"]["email"];
  $decider= $parameters["BOTTOM_LEVEL"]["decider"];
  $comment= $parameters["BOTTOM_LEVEL"]["comment"];
  $cu= strtolower($parameters["BOTTOM_LEVEL"]["cu"]);

  $fullName= "$firstName $lastName";
  $firstName= $firstName == "" ? "null" : "'$firstName'";
  $lastName= $lastName == "" ? "null" : "'$lastName'";
  $role= $role == "" ? "null" : "'$role'";
  $phone= $phone == "" ? "null" : "'$phone'";
  $email= $email == "" ? "null" : "'$email'";
  $decider= $decider == "" ? "null" : "'$decider'";
  $comment= $comment == "" ? "null" : "'$comment'";
  $cu= $cu == "" ? "null" : "'$cu'";

  // This only has one insert statement but if more are added in the future, then it too will need a transaction.
  $insertSQL= "insert into monitor_contact (fname, lname, role, phone, email, decider, comment, user_name) values ($firstName, $lastName, $role, $phone, $email, $decider, $comment, $cu);
               select currval('monitor_contact_id_seq'::regclass) as next;";
  $returnArray= array("sql" => array($insertSQL), "error" => $errors, "operation" => "add");
  if (count($errors) == 0)
  {
    $results= runSelectStatement($insertSQL, $dbh, array("next" => "next"));
    $returnArray["error"]= $results["error"];
    $contactId= intval($results["record"][0]["next"]);

    $returnArray["newDDLRecord"]= array("contactId" => $contactId, "text" => $fullName, "phone" => $parameters["BOTTOM_LEVEL"]["phone"], "email" => $parameters["BOTTOM_LEVEL"]["email"],
      "cu" => $parameters["BOTTOM_LEVEL"]["cu"], "sortNumber" => 3);
  }
  return $returnArray;
}

/* This will get the comments for the current issue.  "readIssueEdit" gets the list on page load.  This function is called when the "Show All" button is clicked.  So it toggles back and forth
between only the logged in user's comments for the issue to any comments for that issue and tasks underneath that issue. */
function getComments($dbh, $staffId)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL",
    array("getAll" => "string", "trackId" => "string", "trackItemId" => "string", "ignoreIds" => "string"));

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("getAll" => array("type" => "boolean", "required" => false),
          "trackId" => array("type" => "int", "required" => true),
          "trackItemId" => array("type" => "int", "required" => false),
          "ignoreIds" => array("type" => "string", "required" => false)));

  $getAll= $parameters["BOTTOM_LEVEL"]["getAll"] == "Y";
  $trackId= $parameters["BOTTOM_LEVEL"]["trackId"];
  $trackItemId= $parameters["BOTTOM_LEVEL"]["trackItemId"];
  $ignoreIds= $parameters["BOTTOM_LEVEL"]["ignoreIds"];

  // If looking at issue comments, ALL includes track item comments
  $trackItemId= intval($trackItemId);
  $trackItemIdSQLPiece= $trackItemId == 0 ? ($getAll ? "" : "and trackitem_id= 0") : "and trackitem_id = $trackItemId";

  $insensitiveStaffId= strtolower($staffId);
  $authorSQLPiece= $getAll ? "" : "and lower(author) = '$insensitiveStaffId'";
  $limitIdsSQLPiece= "";

  if ($ignoreIds != "")
  {
    $idArray= json_decode($ignoreIds, true);
    if (is_array($idArray))
    {
      if (count($idArray) > 0)
      {
        foreach($idArray as $id)
        {
          $cleanedId= cleanInteger($id);
          if ($cleanedId == "ERROR")
          {
            $errors[]= "ignoreIds has some invalid ids";
            break;
          }
        }

        $limitIdsSQLPiece= "and messageid not in (" . implode(", ", $idArray) . ")";
      }
    }
    else
      $errors[]= "ignoreIds is not a valid array";
  }

  $commentsSQL= "select row_number() over(order by messageid) as number, messageid, author, createddate, messagetext, trackitem_id from cuinfo_notes
                where track_id = $trackId $trackItemIdSQLPiece $authorSQLPiece $limitIdsSQLPiece order by createddate";

  $record= array();
  if (count($errors) == 0)
  {
      $results= runSelectStatement($commentsSQL, $dbh, array("messageid" => "commentId", "author" => "author", "createddate" => "createdDate", "messagetext" => "text",
                                                             "trackitem_id" => "trackItemId", "number" => "mockCommentId")); // For differentiating when showing all issue comments.
      $errors= $results["error"];
      $record= $results["record"];
  }
  return array("sql" => array($commentsSQL), "error" => $errors, "record" => $record, "operation" => "read");
}

/* Retrieves a list of tasks for the particular issue.  This is called on load and when the "Show All" button is clicked.  Doing that will toggle between showing N/A tasks. */
function readTasks($dbh)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL", array("trackId" => "string"));

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("trackId" => array("type" => "int", "required" => true)));

  $trackId= $parameters["BOTTOM_LEVEL"]["trackId"];

  $sql= "select i.trackitem_id, i.title, i.item_desc, i.complete_date, i.task_status, i.parent, i.assigned_to, i.last_status_changed,
         row_number() over(partition by i.parent order by i.view_order, i.trackitem_id) - 1 as position, i.issue_item_id, i.billing_item_id
         from cutrackitem i where i.track_id= $trackId order by i.parent, i.task_status, i.view_order, i.trackitem_id";

  $taskTemplatesSQL= "select ii.item_id, ii.title from cuissuesitem ii inner join cutrack t on ii.issue_id = t.issue_id and t.track_id= $trackId";
  $sqls= array($taskTemplatesSQL, $sql);

  $newRecords= array();
  $masterTasks= array();
  if (count($errors) == 0)
  {
    $taskResults= runSelectStatement($taskTemplatesSQL, $dbh, array("item_id" => "taskTemplateId", "title" => "title"));
    $masterTasks= $taskResults["record"];

    $results= runSelectStatement($sql, $dbh, array("trackitem_id" => "taskId", "title" => "title", "item_desc" => "description", "complete_date" => "completedOn", "position" => "position",
                                                   "task_status" => "statusId", "parent" => "parentTaskId", "assigned_to" => "ownerId", "last_status_changed" => "statusChangedDate",
                                                   "issue_item_id" => "taskTemplateId", "billing_item_id" => "billingItemId"));
    $errors= array_merge($taskResults["error"], $results["error"]);
    $taskStatuses= getTaskStatuses();

    $newRecords= array();

    foreach($results["record"] as $record)
    {
      $record["statusName"]= $taskStatuses[$record["statusId"]];
      $newRecords[]= $record;
    }
  }

  return array("record" => $newRecords, "operation" => "read", "error" => $errors, "sql" => $sqls, "masterTasks" => $masterTasks);
}

/* This will save a new task or edit a preexisting one.  If the owner is changed, then an email will be sent to the new owner (provided that the issue is Active.) */
function ModifyTask($dbh, $isAdd, $staffId)
{
  $parameters= array();
  $allowed= array("title" => "string", "description" => "string", "ownerId" => "string", "issueId" => "string", "issueOwnerId" => "string",
    "cu" => "string", "cuName" => "string", "issue" => "string", "parentTaskOwnerId" => "string", "isNextTask" => "string", "isFirstTask" => "string");
  if ($isAdd)
  {
    $allowed["parentTaskId"]= "string";
    $allowed["position"]= "string";
  }
  else
  {
    $allowed["taskId"]= "string";
    $allowed["previousOwnerId"]= "string";
  }

  dms_import_v2($parameters, "BOTTOM_LEVEL", $allowed);

  $title= $parameters["BOTTOM_LEVEL"]["title"];
  $description= $parameters["BOTTOM_LEVEL"]["description"];
  $issue= $parameters["BOTTOM_LEVEL"]["issue"];
  $cuCode= strtolower($parameters["BOTTOM_LEVEL"]["cu"]);
  $cuName= $parameters["BOTTOM_LEVEL"]["cuName"];
  $parentTaskOwnerId= trim($parameters["BOTTOM_LEVEL"]["parentTaskOwnerId"]);
  $ownerId= trim($parameters["BOTTOM_LEVEL"]["ownerId"]);
  $issueOwnerId= trim($parameters["BOTTOM_LEVEL"]["issueOwnerId"]);
  $previousOwnerId= isset($parameters["BOTTOM_LEVEL"]["previousOwnerId"]) ? trim($parameters["BOTTOM_LEVEL"]["previousOwnerId"]) : "";

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("title" => array("required" => false, "type" => "string", "maxlength" => 80),
          "description" => array("required" => false, "type" => "string"),
          "taskId" => array("required" => true, "type" => "int"),
          "issueId" => array("required" => true, "type" => "int"),
          "ownerId" => array("required" => false, "type" => "string", "maxlength" => 12),
          "parentTaskId" => array("required" => false, "type" => "int"),
          "issueOwnerId" => array("required" => true, "type" => "string", "maxlength" => 30),
          "previousOwnerId" => array("required" => false, "type" => "string", "maxlength" => 12),
          "cu" => array("type" => "string", "required" => true, "maxlength" => 12),
          "cuName" => array("type" => "string", "required" => true, "maxlength" => 40),
          "issue" => array("type" => "string", "required" => true, "maxlength" => 90),
          "position" => array("type" => "int", "required" => true),
          "isNextTask" => array("type" => "boolean", "required" => false),
          "isFirstTask" => array("type" => "boolean", "required" => false)));

  $titleClean= $parameters["BOTTOM_LEVEL"]["title"];
  $descriptionClean= $parameters["BOTTOM_LEVEL"]["description"];
  $taskId= isset($parameters["BOTTOM_LEVEL"]["taskId"]) ? intval($parameters["BOTTOM_LEVEL"]["taskId"]) : 0;
  $issueId= $parameters["BOTTOM_LEVEL"]["issueId"];
  $ownerIdClean= $parameters["BOTTOM_LEVEL"]["ownerId"];
  $parentTaskId= isset($parameters["BOTTOM_LEVEL"]["parentTaskId"]) ? intval($parameters["BOTTOM_LEVEL"]["parentTaskId"]) : 0;
  $position= isset($parameters["BOTTOM_LEVEL"]["position"]) ? $parameters["BOTTOM_LEVEL"]["position"] : 0;
  $isNextTask= $parameters["BOTTOM_LEVEL"]["isNextTask"] == "Y";
  $isFirstTask= $parameters["BOTTOM_LEVEL"]["isFirstTask"] == "Y";

  $titleForDatabase= $title == "" ? "null" : "'$titleClean'";
  $descriptionForDatabase= $description == "" ? "null" : "'$descriptionClean'";

  $transactionSQLs= array();
  $sqls= array();

  if (count($errors) == 0)
  {
      if ($isAdd)
      {
        $sql= "select nextval('cutrackitem_trackitem_id_seq'::text::regclass) as next";

        $sqls[]= $sql;
        $results= runSelectStatement($sql, $dbh, array("next" => "next"));
        $taskId= intval($results["record"][0]["next"]);

        $transactionSQLs[]= "insert into cutrackitem (trackitem_id, title, item_desc, assigned_to, track_id, parent, view_order, last_status_changed)
          values ($taskId, $titleForDatabase, $descriptionForDatabase, '$ownerIdClean', $issueId, $parentTaskId, $position, now())";

        // Ensure that there is a previous task owner!  Otherwise a task reorder operation will send an email to blank.
        if ($isFirstTask)
        {
          $transactionSQLs[]= "update cutrack set previous_task_owner= '$ownerIdClean' where track_id= $issueId";
        }
      }
      else
      {
        $transactionSQLs[]= "update cutrackitem set title=$titleForDatabase, item_desc=$descriptionForDatabase, assigned_to='$ownerIdClean' where trackitem_id=$taskId";
      }

      $transactionSQLs[]= "update cutrack set last_activity_date= now() where track_id= $issueId";
  }

  $sqls= array_merge($sqls, $transactionSQLs);

  if (count($errors) == 0)
  {
    $results= runExecStatement(implode(";", $transactionSQLs) . ";", $dbh);
    $errors= array_merge($errors, $results["error"]);
  }

  // Find effective ownerId (if subtask is set to default and parent task is set to default, it would be the issue owner)
  if ($ownerId == "")
    $ownerId= $parentTaskOwnerId;
  if ($ownerId == "")
    $ownerId= $issueOwnerId;

  if ($isNextTask && !$isAdd && strtolower($ownerId) != strtolower($staffId) && count($errors) == 0) {
      if ($isAdd || $ownerId != $previousOwnerId) {

        $text = "Congratulations!  $staffId has assigned a task to you.  Please work on this task.";
        $text2 = "If you have any questions, please contact $issueOwnerId.";

        $results = CreateToLine($dbh, array(trim($ownerId)));
        if ($results["status"] !== "000") {
          $errors[] = $results["error"];
        }
        $subject = "You have been assigned a task!";

        CreateTaskEmail($results["data"], $subject, $text, $issue, $title, $description, $cuName, $cuCode, $issueId, $taskId, $text2);
      }
  }

  $requerySQL= "select i.trackitem_id, i.title, i.item_desc, i.complete_date, i.task_status, i.parent, i.assigned_to, i.last_status_changed, i.billing_item_id from cutrackitem i
                where i.trackitem_id= $taskId";
  $sqls[]= $requerySQL;
  $requeryResults= runSelectStatement($requerySQL, $dbh, array("trackitem_id" => "taskId", "title" => "title", "item_desc" => "description", "complete_date" => "completedOn",
    "task_status" => "statusId", "parent" => "parentTaskId", "assigned_to" => "ownerId", "last_status_changed" => "statusChangedDate", "billing_item_id" => "billingItemId"));
  $errors= array_merge($errors, $requeryResults["error"]);

  $records= array();
  $taskStatuses= getTaskStatuses();
  if (isset($requeryResults["record"][0]))
  {
    $record= $requeryResults["record"][0];
    $record["statusName"]= $taskStatuses[$record["statusId"]];
    $record["position"]= $record["taskId"];
    $records[]= $record;
  }

  return array("record" => $records, "sql" => $sqls, "error" => $errors, "operation" => $isAdd ? "add" : "update");
}

/* This will remove a task from the database. */
function deleteTask($dbh, $staffId)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL", array("taskId" => "string", "deleteSubTasks" => "string", "cu" => "string", "cuName" => "string", "isNew" => "string",
    "trackId" => "string"));

  $cu= strtoupper($parameters["BOTTOM_LEVEL"]["cu"]);
  $cuName= $parameters["BOTTOM_LEVEL"]["cuName"];

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("taskId" => array("required" => true, "type" => "int"),
          "deleteSubTasks" => array("required" => false, "type" => "boolean"),
          "cu" => array("type" => "string", "required" => true, "maxlength" => 12),
          "cuName" => array("type" => "string", "required" => true, "maxlength" => 40),
          "isNew" => array("required" => false, "type" => "boolean"),
          "trackId" => array("required" => true, "type" => "int"),
          "issue" => array("type" => "string", "required" => true, "maxlength" => 90)));
  $taskId= $parameters["BOTTOM_LEVEL"]["taskId"];
  $deleteSubTasks= $parameters["BOTTOM_LEVEL"]["deleteSubTasks"] == "Y";
  $isNew= $parameters["BOTTOM_LEVEL"]["isNew"] == "Y";
  $issueId= intval($parameters["BOTTOM_LEVEL"]["trackId"]);

  $sqls= array();

  if ($taskId == 0 && $deleteSubTasks)
    $errors[]= "Not allowed to delete all top-level tasks.";

  $transactionSQLs= array();

  $transactionSQLs[]= "delete from cutrackitem where trackitem_id = $taskId";
  $transactionSQLs[]= "delete from cutrackitem where parent = $taskId";
  $transactionSQLs[]= "update cutrack set last_activity_date= now() where track_id= $issueId";

  $taskEmails= array();
  $issueEmails= array();
    if (!$isNew)
        notifyNextTaskOwner($dbh, $issueId, $taskId, $staffId, "deleted", null, $errors, $transactionSQLs, $taskEmails, $issueEmails);

  $sqls= array_merge($sqls, $transactionSQLs);

  if (count($errors) == 0)
  {
    $results= runExecStatement(implode(";", $transactionSQLs) . ";", $dbh);
    $errors= array_merge($errors, $results["error"]);
    if (count($errors) == 0)
    {
        foreach($taskEmails as $taskEmail)
        {
            createTaskEmail($taskEmail["to"], $taskEmail["subject"], $taskEmail["text"], $taskEmail["issue"], $taskEmail["title"], $taskEmail["description"], $cuName, $cu,
              $taskEmail["issueId"], $taskEmail["taskId"], "", $taskEmail["completeNotes"]);
        }
        foreach($issueEmails as $issueEmail)
        {
            createIssueEmail($issueEmail["to"], $issueEmail["subject"], $issueEmail["text"], $issueEmail["issue"], $issueEmail["status"], $cuName, $cu, $issueEmail["issueId"],
              "", $issueEmail["completeNotes"]);
        }
    }
  }

  return array("error" => $errors, "sql" => $sqls, "operation" => "delete");
}

/* This will change a task status from active to completed and from active to N/A.  When completing a task, the next task owner (if changed) will be notified by email. */
function changeTaskStatus($dbh, $staffId)
{
  $parameters= array();

  dms_import_v2($parameters, "BOTTOM_LEVEL", array("taskId" => "string", "newStatus" => "string", "prevStatus" => "string", "issueId" => "string", "issueOwnerId" => "string",
    "parentTaskId" => "string", "cu" => "string", "cuName" => "string", "nextTaskId" => "string", "nextTaskOwnerChanged" => "string", "nextTaskOwner" => "string",
    "completeNotes" => "string", "forceEmail" => "string", "completeDate" => "string", "billingItemId" => "string"));

  $cu= strtoupper($parameters["BOTTOM_LEVEL"]["cu"]);
  $cuName= $parameters["BOTTOM_LEVEL"]["cuName"];

  $completeNotes= $parameters["BOTTOM_LEVEL"]["completeNotes"];

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("taskId" => array("required" => true, "type" => "int"),
          "newStatus" => array("required" => true, "type" => "int"),
          "prevStatus" => array("required" => true, "type" => "int"),
          "issueId" => array("required" => true, "type" => "int"),
          "issueOwnerId" => array("required" => true, "type" => "string", "maxlength" => 30),
          "isNew" => array("required" => false, "type" => "boolean"),
          "parentTaskId" => array("required" => true, "type" => "int"),
          "cu" => array("type" => "string", "required" => true, "maxlength" => 12),
          "cuName" => array("type" => "string", "required" => true, "maxlength" => 40),
          "nextTaskId" => array("type" => "int", "required" => false),
          "nextTaskOwnerChanged" => array("type" => "boolean", "required" => false),
          "nextTaskOwner" => array("type" => "string", "required" => false, "maxlength" => 30),
          "completeNotes" => array("type" => "string", "required" => false),
          "forceEmail" => array("type" => "boolean", "required" => false),
          "completeDate" => array("type" => "date", "required" => false),
          "billingItemId" => array("type" => "int", "required" => false)));

  $taskId= $parameters["BOTTOM_LEVEL"]["taskId"];
  $newStatus= $parameters["BOTTOM_LEVEL"]["newStatus"];
  $prevStatus= $parameters["BOTTOM_LEVEL"]["prevStatus"];
  $issueId= $parameters["BOTTOM_LEVEL"]["issueId"];
  $issueOwnerId= $parameters["BOTTOM_LEVEL"]["issueOwnerId"];
  $isNew= isset($parameters["BOTTOM_LEVEL"]["isNew"]) ? ($parameters["BOTTOM_LEVEL"]["isNew"] == "Y") : false;
  $parentTaskId= $parameters["BOTTOM_LEVEL"]["parentTaskId"];
  $nextTaskOwnerChanged= $parameters["BOTTOM_LEVEL"]["nextTaskOwnerChanged"] == "Y";
  $nextTaskOwner= $parameters["BOTTOM_LEVEL"]["nextTaskOwner"];
  $completeNotesClean= $parameters["BOTTOM_LEVEL"]["completeNotes"];
  $forceEmail= $parameters["BOTTOM_LEVEL"]["forceEmail"] == "Y";
  $nextTaskId= $parameters["BOTTOM_LEVEL"]["nextTaskId"];
  $cuClean= strtolower($parameters["BOTTOM_LEVEL"]["cu"]);
  $completeDate= $parameters["BOTTOM_LEVEL"]["completeDate"];
  $billingItemId= intval($parameters["BOTTOM_LEVEL"]["billingItemId"]);

  $taskEmails= array();
  $issueEmails= array();
  $sqls= array();

  $transactionSQLs= array();

  $completeDateSafe = "";
  if ($completeDate != "" && count($errors) == 0)
  {
    if (count($errors) == 0)
    {
      $completeDateTime= new DateTime($completeDate);
      $completeDateSafe= $completeDateTime->format("m/d/Y");
    }
    else
    {
      $completeDateSafe= "ERROR";
    }
  }

  $statuses= getTaskStatuses();
  $statusName= $statuses[$newStatus];
  if (!isset($statusName))
    $errors[]= "Status code doesn't exist";

  $completeDatePiece= $statusName == "Done" ? ($completeDate != "" ? "complete_date = '$completeDate', " : "complete_date = now(), ") : "complete_date = null, ";
  $completeDatePieceOther= $completeDate != "" ? "'$completeDate'" : "now()";
  $transactionSQLs[]= "update cutrackitem set $completeDatePiece task_status = $newStatus where trackitem_id = $taskId";
  $status= $newStatus;
  $noteRecord= array();
  $reassigned= false;

    if ($parentTaskId != 0) // If it is a subtask then there might be a need to complete the parent task.
    {
      if ($statusName != "TODO")
      {
        $transactionSQLs[]= "update cutrackitem set complete_date = $completeDatePieceOther, task_status = 0 where trackitem_id = $parentTaskId and not exists
        (select 'FOUND' from cutrackitem where parent = $parentTaskId and task_status = 1 and trackitem_id <> $taskId)";
      }
      else
      {
        $transactionSQLs[]= "update cutrackitem set complete_date = null, task_status = 1 where trackitem_id = $parentTaskId";
      }
    }
    else if ($statusName == "Done")
    {

      $findChildrenSQL= "select trackitem_id, billing_item_id from cutrackitem where parent = $taskId and complete_date is null";
      $results= runSelectStatementMap($findChildrenSQL, $dbh);
      $sqls[]= $findChildrenSQL;
      $errors= array_merge($errors, $results["error"]);

      if (count(array_keys($results["record"])) > 0)
      {
        foreach($results["record"] as $key => $value)
        {
          if ($value != 0)
            updateBillingTaskTrigger($dbh, $completeDate, $value, $transactionSQLs);
        }

        $transactionSQLs[]= "update cutrackitem set complete_date = $completeDatePieceOther, task_status = 0 where parent = $taskId";
      }


      if ($nextTaskId != "" && $nextTaskOwnerChanged)
      {
        $transactionSQLs[]= "update cutrackitem set assigned_to='$nextTaskOwner' where trackitem_id = $nextTaskId";
        $reassigned= true;
      }

      if ($completeNotes != "")
      {
        $noteRecord= array("author" => $staffId, "text" => $completeNotes, "trackItemId" => $taskId);

        $sql= "select nextval('cuinfo_notes_messageid_seq'::text::regclass) as next";
        $sqls[]= $sql;
        $results= runSelectStatement($sql, $dbh, array("next" => "next"));
        $errors= array_merge($errors, $results["error"]);
        $commentId= intval($results["record"][0]["next"]);
        $noteRecord["commentId"]= $commentId;

        $transactionSQLs[]= "insert into cuinfo_notes (messageid, trackitem_id, track_id, messagetext, cu, author, createddate, activitydate)
          values ($commentId, $taskId, $issueId, '$completeNotesClean', '$cuClean', '$staffId', now(), now())";
      }

      if ($billingItemId != 0)
      {
        updateBillingTaskTrigger($dbh, $completeDate, $billingItemId, $transactionSQLs);
      }
    }
    else if ($statusName == "N/A")
    {
      $transactionSQLs[]= "update cutrackitem set complete_date= null, task_status = 2 where parent = $taskId";
    }

    $transactionSQLs[]= "update cutrack set last_activity_date= now() where track_id= $issueId";

    switch($statusName)
    {
      case "Done":
        $mode= "finished";
        break;
      case "N/A":
        $mode= "inactivated";
        break;
      default:
        $mode= "reactivated";
        break;
    }

    notifyNextTaskOwner($dbh, $issueId, $taskId, $staffId, $mode, $issueOwnerId, $errors, $transactionSQLs, $taskEmails, $issueEmails, $completeNotes,
      $forceEmail, $reassigned);

    $sqls= array_merge($sqls, $transactionSQLs);
    if (count($errors) == 0)
    {
      $results= runExecStatement(implode(";", $transactionSQLs) . ";", $dbh);
      $errors= array_merge($errors, $results["error"]);

      if (count($errors) == 0)
      {
        foreach($taskEmails as $taskEmail)
        {
            createTaskEmail($taskEmail["to"], $taskEmail["subject"], $taskEmail["text"], $taskEmail["issue"], $taskEmail["title"], $taskEmail["description"], $cuName, $cu,
              $taskEmail["issueId"], $taskEmail["taskId"], "", $taskEmail["completeNotes"]);
        }
        foreach($issueEmails as $issueEmail)
        {
            createIssueEmail($issueEmail["to"], $issueEmail["subject"], $issueEmail["text"], $issueEmail["issue"], $issueEmail["status"], $cuName, $cu, $issueEmail["issueId"],
              "", $issueEmail["completeNotes"]);
        }
      }
    }

  if (count($errors) > 0)
  {
    $status= $prevStatus;
    $statusName= $statuses[$prevStatus];
  }

  $returnArray= array("error" => $errors, "sql" => $sqls, "statusId" => $status, "statusName" => $statusName, "completeDate" => trim($completeDateSafe));

  if ($completeNotes != "")
    $returnArray["noteRecord"]= $noteRecord;

  return $returnArray;
}

function updateBillingTaskTrigger($dbh, $completeDate, $billingItemId, &$transactionSQLs)
{
    // Set it to the current month if billing isn't run yet, otherwise next month.
    try
    {
      $thisDate= trim($completeDate) == "" ? new DateTime() : new DateTime($completeDate);
      $m= $thisDate->format("m");
      $Y= $thisDate->format("Y");
      $thisDate->setDate($Y, $m, 1);
      $tryMonth1= $thisDate->format("Y-m-d");
      $thisDate->setDate($Y, $m+1, 1);
      $tryMonth2= $thisDate->format("Y-m-d");
    }
    catch(Exception $e)
    {
      $tryMonth1= "ERROR";
      $tryMonth2= "ERROR";
    }
      // Complete any hooks to billing
      $transactionSQLs[]= "update cubillfeaturedetail set start_date= t.date, end_date= t.date, billing_status=2, workflow_completed='Y' from
      (select case when not exists (select 'FOUND' from cubillmonth where billing_date='$tryMonth1') then date '$tryMonth1' else date '$tryMonth2' end) as t (date)
      where cubillfeaturedetail.id= $billingItemId and cubillfeaturedetail.workflow_completed= 'N'";
}

/* This will update the viewOrder of all tasks with the same parent.  This also allows movement from one parent to another. An email will be sent to the next task owner if different. */
function reorderTasks($dbh, $staffId)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL", array("taskIds" => "string", "issueId" => "string", "isNew" => "string", "parentTaskId" => "string", "cu" => "string", "cuName" => "string",
    "taskId" => "string", "moveTaskId" => "string", "moveAfter" => "string"));

  $cu= $parameters["BOTTOM_LEVEL"]["cu"];
  $cuName= $parameters["BOTTOM_LEVEL"]["cuName"];

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"], array("taskIds" => array("required" => true, "type" => "string"),
    "issueId" => array("required" => true, "type" => "int"),
    "isNew" => array("required" => false, "type" => "boolean"),
    "parentTaskId" => array("required" => true, "type" => "int"),
    "cu" => array("type" => "string", "required" => true, "maxlength" => 12),
    "cuName" => array("type" => "string", "required" => true, "maxlength" => 40),
    "taskId" => array("type" => "int", "required" => true),
    "moveTaskId" => array("type" => "int", "required" => true),
    "moveAfter" => array("type" => "boolean", "required" => false)));
  $taskIds= json_decode($parameters["BOTTOM_LEVEL"]["taskIds"], true);
  $issueId= $parameters["BOTTOM_LEVEL"]["issueId"];
  $isNew= $parameters["BOTTOM_LEVEL"]["isNew"] == "Y";
  $parentTaskId= $parameters["BOTTOM_LEVEL"]["parentTaskId"];
  $moveAfter= $parameters["BOTTOM_LEVEL"]["moveAfter"] == "Y";
  $taskId= $parameters["BOTTOM_LEVEL"]["taskId"];
  $moveTaskId= $parameters["BOTTOM_LEVEL"]["moveTaskId"];
  $sqls= array();

  $transactionSQLs= array();
  $taskEmails= array();
  $issueEmails= array();

  if (is_array($taskIds))
  {
    $sqlPartArray= array();

    $index= 0;
    $firstTaskId= -1;
    foreach($taskIds as $taskId)
    {
      $taskId= cleanInteger($taskId);
      if ($firstTaskId == -1)
        $firstTaskId= $taskId;
      if ($taskId == "ERROR")
      {
        $errors[]= "TaskIds do not have all integers";
        break;
      }
      else
      {
        $sqlPartArray[]= "($taskId, $index, $parentTaskId)";
        $index++;
      }
    }

    $transactionSQLs[]= "update cutrackitem as ti set view_order = t.view_order, parent= t.parent from (values " . implode(", ", $sqlPartArray) . ") as t(trackitem_id, view_order, parent)
           where ti.trackitem_id = t.trackitem_id";

    if (count($taskIds) > 0)
    {
      $transactionSQLs[]= "update cutrack set last_activity_date= now() where track_id= $issueId";

      if (!$isNew)
      {
        if ($parentTaskId == 0)
        {
          $sql= "with ordered_trackitem as (select ti.track_id, ti.trackitem_id, ti.assigned_to, ti.parent, ti.title, ti.item_desc, row_number() over (partition by ti.parent order by ti.parent,
            ti.view_order, ti.trackitem_id) as number from cutrackitem ti where ti.task_status <> 2 and ti.complete_date is null and (ti.parent = $firstTaskId or ti.trackitem_id= $firstTaskId))
            select t.previous_task_owner, sub.assigned_to as sassigned_to, parent.assigned_to as passigned_to, sub.title as stitle, parent.title as ptitle, sub.item_desc as sitem_desc,
            parent.item_desc as pitem_desc, sub.trackitem_id as strackitem_id, parent.trackitem_id as ptrackitem_id, t.assigned_to, t.issue, t.status from cutrack t
            left join ordered_trackitem parent on parent.parent= 0 and parent.number= 1
            left join ordered_trackitem sub on sub.parent= parent.trackitem_id and sub.number= 1 where t.track_id= $issueId limit 1";
          $sqls[]= $sql;
          $results= runSelectStatement($sql, $dbh, array("previous_task_owner" => "previousTaskOwner", "sassigned_to" => "subAssignedTo", "passigned_to" => "parentAssignedTo",
            "stitle" => "subTitle", "sitem_desc" => "subDesc", "pitem_desc" => "parentDesc", "ptitle" => "parentTitle", "strackitem_id" => "subTaskId", "ptrackitem_id" => "parentTaskId",
            "assigned_to" => "issueOwner", "issue" => "issue", "status" => "status"));
          $record= $results["record"][0];
          $errors= array_merge($errors, $results["error"]);
        }
        else
        {
          $sql= "select ti.track_id, ti.trackitem_id, ti.assigned_to as passigned_to, ti.parent, ti.title, ti.item_desc, t.assigned_to, t.issue, t.status, t.previous_task_owner from cutrack t
                 inner join cutrackitem ti on t.track_id= ti.track_id and t.track_id= $issueId and ti.task_status <> 2 and ti.complete_date is null and ti.parent= 0
                 order by ti.parent, ti.view_order, ti.trackitem_id limit 1";
          $sqls[]= $sql;
          $results= runSelectStatement($sql, $dbh, array("previous_task_owner" => "previousTaskOwner", "assigned_to" => "issueOwner", "issue" => "issue", "status" => "status",
            "item_desc" => "parentDesc", "title" => "parentTitle", "trackitem_id" => "parentTaskId", "passigned_to" => "parentAssignedTo"));
          $record= $results["record"][0];
          $errors= array_merge($errors, $results["error"]);

          if ($record["parentTaskId"] == $parentTaskId)
            $sql= "select ti.track_id, ti.trackitem_id, ti.assigned_to, ti.parent, ti.title, ti.item_desc from cutrackitem ti
                   where ti.trackitem_id= $firstTaskId limit 1";
          else
            $sql= "select ti.track_id, ti.trackitem_id, ti.assigned_to, ti.parent, ti.title, ti.item_desc from cutrackitem ti
                   where ti.parent= " . $record["parentTaskId"] . " order by ti.parent, ti.view_order, ti.trackitem_id limit 1";

          $sqls[]= $sql;
          $results= runSelectStatement($sql, $dbh, array("assigned_to" => "subAssignedTo", "title" => "subTitle", "item_desc" => "subDesc", "trackitem_id" => "subTaskId"));

          $record= array_merge($record, $results["record"][0]);
          $errors= array_merge($errors, $results["error"]);
        }

          $hasTasks= false;

          $previousTaskOwner= $record["previousTaskOwner"];
          $issueOwnerId= isset($issueOwnerId) ? $issueOwnerId : $record["issueOwner"];
          $issue= $record["issue"];
          $status= $record["status"];

          if ($record["subTaskId"] != "")
          {
            $nextTaskOwner= $record["subAssignedTo"];
            if ($nextTaskOwner == "")
              $nextTaskOwner= $record["parentAssignedTo"];
            if ($nextTaskOwner == "")
              $nextTaskOwner= $issueOwnerId;
            $title= $record["subTitle"];
            $description= $record["subDesc"];
            $taskId= $record["subTaskId"];
            $hasTasks= true;
          }
          else if ($record["parentTaskId"] != "")
          {
            $nextTaskOwner= $record["parentAssignedTo"];
            if ($nextTaskOwner == "")
              $nextTaskOwner= $issueOwnerId;
            $title= $record["parentTitle"];
            $description= $record["parentDesc"];
            $taskId= $record["parentTaskId"];
            $hasTasks= true;
          }

          // Now we can send an email.
          if ($hasTasks && (strtolower($nextTaskOwner) != strtolower($previousTaskOwner)))
          {

            $transactionSQLs[] = "update cutrack set previous_task_owner = '$nextTaskOwner' where track_id= $issueId";

            $text = "Tasks were rearranged.  $nextTaskOwner is the next task owner, not $previousTaskOwner.";
            $results = CreateToLine($dbh, array(trim($nextTaskOwner), trim($previousTaskOwner)));
            if ($results["status"] !== "000") {
              $errors[] = $results["error"];
            }
            $subject = "You are the next task owner!";
            $taskEmails[] = array("to" => $results["data"], "subject" => $subject, "text" => $text, "issue" => $issue, "title" => $title,
                                  "description" => $description, "issueId" => $issueId, "taskId" => $taskId, "completeNotes" => $completeNotes);
          }
        }
    }
  }
  else
  {
    $errors[]= "TaskIds array is not valid";
  }

  $sqls= array_merge($sqls, $transactionSQLs);
  if (count($errors) == 0)
  {
    $results= runExecStatement(implode(";", $transactionSQLs), $dbh);
    $errors= array_merge($errors, $results["error"]);
    if (count($errors) == 0)
    {
      foreach($taskEmails as $taskEmail)
      {
        createTaskEmail($taskEmail["to"], $taskEmail["subject"], $taskEmail["text"], $taskEmail["issue"], $taskEmail["title"], $taskEmail["description"], $cuName, $cu,
          $taskEmail["issueId"], $taskEmail["taskId"], "", $taskEmail["completeNotes"]);
      }
      foreach($issueEmails as $issueEmail)
      {
        createIssueEmail($issueEmail["to"], $issueEmail["subject"], $issueEmail["text"], $issueEmail["issue"], $issueEmail["status"], $cuName, $cu, $issueEmail["issueId"],
          "", $issueEmail["completeNotes"]);
      }
    }
  }

  return array("error" => $errors, "sql" => $sqls);
}

/* This will add a comment to a particular task. */
function modifyTaskComment($dbh, $staffId, $isAdd)
{
  $parameters= array();
  $import= array("text" => "string");
  if ($isAdd)
  {
    $import["taskId"]= "string";
    $import["issueId"]= "string";
    $import["cu"]= "string";
  }
  else
  {
    $import["commentId"]= "string";
  }
  dms_import_v2($parameters, "BOTTOM_LEVEL", $import);

  $text= $parameters["BOTTOM_LEVEL"]["text"];
  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("taskId" => array("required" => true, "type" => "int"),
          "issueId" => array("required" => true, "type" => "int"),
          "text" => array("required" => false, "type" => "string"),
          "cu" => array("required" => true, "type" => "string", "maxlength" => 10),
          "commentId" => array("required" => true, "type" => "int"),
          "isFirstTask" => array("required" => false, "type" => "boolean")));

  $taskId= $parameters["BOTTOM_LEVEL"]["taskId"];
  $issueId= $parameters["BOTTOM_LEVEL"]["issueId"];
  $textClean= $parameters["BOTTOM_LEVEL"]["text"];
  $cu= strtolower($parameters["BOTTOM_LEVEL"]["cu"]);
  $commentId= isset($parameters["BOTTOM_LEVEL"]["commentId"]) ? intval($parameters["BOTTOM_LEVEL"]["commentId"]) : 0;
  $isFirstTask= isset($parameters["BOTTOM_LEVEL"]["isFirstTask"]) ? ($parameters["BOTTOM_LEVEL"]["isFirstTask"] == "Y") : false;

  $record= array("commentId" => $commentId, "author" => $staffId, "text" => $text, "trackItemId" => $taskId);

  $textClean= $textClean == "" ? "null" : "'$textClean'";
  $cu= $cu == "" ? "null" : "'$cu'";
  $sqls= array();
  $transactionSQLs= array();

  if ($isAdd)
  {
      $sql= "select nextval('cuinfo_notes_messageid_seq'::text::regclass) as next";

      $sqls[]= $sql;
      $results= runSelectStatement($sql, $dbh, array("next" => "next"));
      $errors= array_merge($errors, $results["error"]);
      $commentId= intval($results["record"][0]["next"]);
      $record["commentId"]= $commentId;

      $transactionSQLs[]= "insert into cuinfo_notes (messageid, trackitem_id, track_id, messagetext, cu, author, createddate, activitydate) values ($commentId, $taskId, $issueId, $textClean, $cu,
        '$staffId', now(), now())";
  }
  else
  {
    $transactionSQLs[]= "update cuinfo_notes set messagetext=$textClean, activitydate=now() where messageid=$commentId";
  }

  $transactionSQLs[]= "update cutrack set last_activity_date= now() where track_id= $issueId";

  $sqls= array_merge($sqls, $transactionSQLs);

  if (count($errors) == 0)
  {
    $results= runExecStatement(implode(";", $transactionSQLs), $dbh);
    $errors= array_merge($errors, $results["error"]);
  }

  return array("error" => $errors, "sql" => $sqls, "record" => array($record));
}

/* This will update the billing status to Active in billing so that the feature can be billed after the workflow is done. */
function updateBilling($dbh, $issueId, &$transactionSQLs)
{
  $nextMonth= new DateTime();
  $m = $nextMonth->format('m');
  $Y = $nextMonth->format('Y');
  $nextMonth->setDate($Y, $m, 1);
  $thisMonthDate= $nextMonth->format("Y-m-d");
  $nextMonth->setDate($Y, $m+1, 1);
  $nextMonthDate= $nextMonth->format("Y-m-d");

  $transactionSQLs[]= "update cubillfeaturedetail set billing_status= case when billing_status = 0 then 3 else 2 end, start_date= '$nextMonthDate', end_date= '$nextMonthDate',
    workflow_completed= 'Y' where id in (select billing_feature_id from cutrack where track_id= $issueId) and workflow_completed= 'N'";
}

/* This will change the task parent for the "move underneath..." and "make parent" options. */
function promoteTask($dbh, $staffId)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL", array("taskId" => "string", "parentTaskId" => "string", "cu" => "string", "cuName" => "string", "trackId" => "string"));

  $cu= strtoupper($parameters["BOTTOM_LEVEL"]["cu"]);
  $cuName= $parameters["BOTTOM_LEVEL"]["cuName"];

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("taskId" => array("required" => true, "type" => "int"),
          "parentTaskId" => array("required" => true, "type" => "int"),
          "cu" => array("type" => "string", "required" => true, "maxlength" => 12),
          "cuName" => array("type" => "string", "required" => true, "maxlength" => 40),
          "trackId" => array("type" => "int", "required" => true)));

  $taskId= $parameters["BOTTOM_LEVEL"]["taskId"];
  $parentTaskId= $parameters["BOTTOM_LEVEL"]["parentTaskId"];
  $issueId= intval($parameters["BOTTOM_LEVEL"]["trackId"]);

  $transactionSQLs= array();
  $sqls= array();
  $taskEmails= array();
  $issueEmails= array();

  $transactionSQLs[]= "update cutrackitem set view_order= temp.position, parent= 0
         from (select i.trackitem_id, row_number() over(order by i.trackitem_id in ($parentTaskId,$taskId) desc, i.view_order, i.trackitem_id)+coalesce(iparent.view_order,0) as position
         from cutrackitem i inner join cutrackitem iparent on i.track_id= iparent.track_id and i.track_id= $issueId and iparent.trackitem_id= $parentTaskId
         where i.trackitem_id= $taskId or ( coalesce(i.view_order,0) >= coalesce(iparent.view_order,0) and i.parent= 0)) temp
         where cutrackitem.trackitem_id= temp.trackitem_id";

  $transactionSQLs[]= "update cutrack set last_activity_date= now() where track_id= $issueId";

  if (!$isNew)
      notifyNextTaskOwner($dbh, $issueId, null, $staffId, "promoted", null, $errors, $transactionSQLs, $taskEmails, $issueEmails);

  $sqls= array_merge($sqls, $transactionSQLs);
  if (count($errors) == 0)
  {
    $results= runExecStatement(implode(";", $transactionSQLs), $dbh);
    $errors= array_merge($errors, $results["error"]);
    if (count($errors) == 0)
    {
        foreach($taskEmails as $taskEmail)
        {
          createTaskEmail($taskEmail["to"], $taskEmail["subject"], $taskEmail["text"], $taskEmail["issue"], $taskEmail["title"], $taskEmail["description"], $cuName, $cu,
            $taskEmail["issueId"], $taskEmail["taskId"], "", $taskEmail["completeNotes"]);
        }
        foreach($issueEmails as $issueEmail)
        {
          createIssueEmail($issueEmail["to"], $issueEmail["subject"], $issueEmail["text"], $issueEmail["issue"], $issueEmail["status"], $cuName, $cu, $issueEmail["issueId"],
            "", $issueEmail["completeNotes"]);
        }
    }
  }

  return array("sql" => $sqls, "error" => $errors);
}

function addIssueFromTemplate($dbh, $staffId)
{
    $sqls= array();
    $parameters= array();
    dms_import_v2($parameters, "BOTTOM_LEVEL", array("templateId" => "string", "cu" => "string", "cuName" => "string"));

    $cu= strtolower($parameters["BOTTOM_LEVEL"]["cu"]);
    $cuName= $parameters["BOTTOM_LEVEL"]["cuName"];

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
      array("templateId" => array("required" => true, "type" => "int"),
            "cu" => array("required" => true, "type" => "string", "maxlength" => 12),
            "cuName" => array("type" => "string", "required" => true, "maxlength" => 40)));
    $templateId= $parameters["BOTTOM_LEVEL"]["templateId"];

    $transactionSQLs= array();
    $taskEmails= array();

    $findIssueSQL= "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.issue_id = '$templateId' order by i.issue_id, ii.parent, ii.view_order, ii.item_id";
    $results= runSelectStatement($findIssueSQL, $dbh, array("issue_id" => "issueId", "code" => "issueTitle", "owner" => "issueOwner", "item_id" => "taskId", "title" => "taskTitle",
        "item_desc" => "taskDescription", "view_order" => "taskViewOrder", "task_owner" => "taskOwner", "parent" => "taskParent", "sales_item_id" => "salesItemId"));

    $getNextIdSQL= "select nextval('cutrackitem_trackitem_id_seq'::text::regclass) as next";
    $nextResults= runSelectStatement($getNextIdSQL, $dbh, array("next" => "next"));
    $errors= array_merge($errors, $results["error"], $nextResults["error"]);
    array_push($sqls, $findIssueSQL, $getNextIdSQL);

    $nextTaskId= intval($nextResults["record"][0]["next"]);
    $first= true;
    $insertIssueSQL= "insert into cutrack (track_id, issue, assigned_to, 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)";
    $issueOwner= "";
    $issueId= "";
    $taskSQLPartArray= array();
    $subTaskArray= array();
    $parentArray= array();
    $hasTasks= false;
    $issue= "";
    $templateIssueId= "";
    $index= 0;
    $parentTaskId= $nextTaskId;

    $previousTaskOwner= "";
    $firstParentTask= null;
    $firstSubTask= null;
    $firstParentTaskId= "";
    $taskIdMap= array();
    foreach($results["record"] as $record)
    {
        if ($first)
        {
            $issueName= $record["issueTitle"];
            $issue= prepSave($record["issueTitle"], 90);
            $issueOwner= trim(strval($record["issueOwner"])) != "" ? prepSave($record["issueOwner"], 30) : prepSave($staffId, 30);
            $templateIssueId= $record["issueId"];

            $sql= "select nextval('cutrack_track_id_seq'::text::regclass) as next";
            $results= runSelectStatement($sql, $dbh, array("next" => "next"));
            $errors= array_merge($errors, $results["error"]);
            $sqls[]= $sql;
            $issueId= intval($results["record"][0]["next"]);
            $taskOwner = $record["taskOwner"];

            $insertIssueSQL.= " values ($issueId, '$issue', '$issueOwner', '$taskOwner', '$cu', 'Active', 'TBD', now(), now(), $templateId)";
            $transactionSQLs[]= $insertIssueSQL;

            $previousTaskOwner= $record["issueOwner"];
            $first= false;
        }

        $taskId= $record["taskId"];

        if($taskId != "" && $templateIssueId == $record["issueId"])
        {
            $title= prepSave($record["taskTitle"], 80);
            $description= prepSave($record["taskDescription"]);
            $viewOrder= $record["taskViewOrder"];
            $owner= prepSave($record["taskOwner"], 12);
            $parent= $record["taskParent"];
            $taskTemplateId= $record["taskId"];
            $salesItemId= intval($record["salesItemId"]);


            if ($parent == 0)
            {
              if (!isset($firstParentTask))
              {
                $firstParentTask= $record;
              }
              $taskIdMap[$taskTemplateId]= $parentTaskId;
              $taskSQLPartArray[]= "($parentTaskId, $issueId, '$title', '$description', $viewOrder, 0, '$owner', $taskTemplateId, $salesItemId)";
              $parentArray[$taskTemplateId]= $index++;
              $parentTaskId++;
            }
            else
            {
              if (!isset($subTaskArray[$parent]))
                $subTaskArray[$parent]= array();
              $subTaskArray[$parent][]= $record;
            }
            $hasTasks= true;
        }
    }

    $subTaskId= $parentTaskId;
    if ($hasTasks)
    {
        $transactionSQLs[]= "$insertTasksSQL values " . implode(", ", $taskSQLPartArray);

        $taskSQLPartArray= array();

        foreach($subTaskArray as $parentId => $detailRecords)
        {
            $parentTaskId= $nextTaskId + $parentArray[$parentId];
            foreach($detailRecords as $record)
            {
              $title= prepSave($record["taskTitle"], 80);
              $description= prepSave($record["taskDescription"]);
              $viewOrder= $record["taskViewOrder"];
              $owner= prepSave($record["taskOwner"], 12);
              $taskTemplateId= $record["taskId"];
              $taskIdMap[$taskTemplateId]= $subTaskId;
              $salesItemId= intval($record["salesItemId"]);

              $taskSQLPartArray[]= "($subTaskId, $issueId, '$title', '$description', $viewOrder, $parentTaskId, '$owner', $taskTemplateId, $salesItemId)";
              $subTaskId++;
            }
        }

        if (count($taskSQLPartArray) > 0) // Make sure that there are any subtasks.
        {
          $transactionSQLs[]= "$insertTasksSQL values " . implode(", ", $taskSQLPartArray);
        }

        $transactionSQLs[]= "select setval('cutrackitem_trackitem_id_seq'::text::regclass, $subTaskId, false)"; // Because inserts are done with the id, sequence is not updated.
    }

    $issueEmail= array();
    $taskEmail= array();
    if (!$first) // means that there is an issue created so now send email.
    {
        if (isset($firstParentTask))
        {
          $firstSubTask= null;
          if (!empty($subTaskArray) && HCU_array_key_exists($firstParentTask["taskId"], $subTaskArray)) {
            $firstSubTask= $subTaskArray[$firstParentTask["taskId"]][0];
          }

          if (isset($firstSubTask))
          {
            $title= $firstSubTask["taskTitle"];
            $description= $firstSubTask["taskDescription"];
            $taskId= $taskIdMap[$firstSubTask["taskId"]];
            $taskOwner= $firstSubTask["taskOwner"] == "" ? ($firstParentTask["taskOwner"] == "" ? $issueOwner : $firstParentTask["taskOwner"]) : $firstSubTask["taskOwner"];
          }
          else
          {
            $title= $firstParentTask["taskTitle"];
            $description= $firstParentTask["taskDescription"];
            $taskId= $taskIdMap[$firstParentTask["taskId"]];
            $taskOwner= $firstParentTask["taskOwner"] == "" ? $issueOwner : $firstParentTask["taskOwner"];
          }

          $text = "Job has been created.  You are the first task owner.";
          $results = CreateToLine($dbh, array(trim($taskOwner)));
          if ($results["status"] !== "000") {
            $errors[] = $results["error"];
          }
          $subject = "You are the next task owner!";
          $taskEmails[] = array("to" => $results["data"], "subject" => $subject, "text" => $text, "issue" => $issue, "title" => $title,
                                "description" => $description, "issueId" => $issueId, "taskId" => $taskId);

          $transactionSQLs[] = "update cutrack set previous_task_owner = '$taskOwner' where track_id = $issueId";
        }
    }

    $sqls= array_merge($sqls, $transactionSQLs);

    if (count($errors) == 0)
    {
      $results= runExecStatement(implode(";", $transactionSQLs) . ";", $dbh);
      $errors= array_merge($errors, $results["error"]);
      if (count($errors) == 0)
      {
        $results = createToLine($dbh, array(trim($issueOwner)));
        if ($results["status"] !== "000") {
          $errors[] = $results["error"];
        }
        $subject = "Job has been created from template!";
        $text = "Workflow is set up which is assigned to you.";
        $text2 = "Please mark non-relevant tasks to N/A and activate issue so that it can be worked.";
        CreateIssueEmail($results["data"], $subject, $text, $issueName, "Active", $cuName, $cu, $issueId, $text2);

        foreach($taskEmails as $taskEmail) {
          CreateTaskEmail($taskEmail["to"], $taskEmail["subject"], $taskEmail["text"], $issueName, $taskEmail["title"], $taskEmail["description"],
            $cuName, $cu, $issueId, $taskEmail["taskId"]);
        }
      }
    }

    return array("sql" => $sqls, "error" => $errors, "trackId" => $issueId);
}

function deleteTaskComment($dbh)
{
    $sqls= array();
    $parameters= array();
    dms_import_v2($parameters, "BOTTOM_LEVEL", array("commentId" => "string"));

    $transactionSQLs= array();

    $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
      array("commentId" => array("required" => true, "type" => "int")));

    $commentId= $parameters["BOTTOM_LEVEL"]["commentId"];

    $transactionSQLs[]= "delete from cuinfo_notes where messageid= $commentId";
    $transactionSQLs[]= "update cutrack set last_activity_date= now() where track_id= $issueId";

    if (count($errors) == 0)
    {
      $results= runExecStatement(implode(";", $transactionSQLs) . ";", $dbh);
      $errors= array_merge($errors, $results["error"]);
    }
    return array("sql" => $transactionSQLs, "error" => $errors, "operation" => "delete");
}

function pullInTaskTemplates($dbh)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL", array("taskTemplateIds" => "string", "trackId" => "string", "taskLength" => "string", "issueId" => "string"));

  $errors= cleanValuesForDatabase($parameters["BOTTOM_LEVEL"],
    array("trackId" => array("required" => true, "type" => "int"),
          "taskLength" => array("required" => true, "type" => "int"),
          "issueId" => array("required" => true, "type" => "int")));

  $taskTemplateIdsEncoded= $parameters["BOTTOM_LEVEL"]["taskTemplateIds"];
  $trackId= $parameters["BOTTOM_LEVEL"]["trackId"];
  $taskLength= $parameters["BOTTOM_LEVEL"]["taskLength"];
  $issueId= $parameters["BOTTOM_LEVEL"]["issueId"];

  $transactionSQLs= array();

  if (trim($taskTemplateIdsEncoded) == "")
    $errors[]= "TaskTemplateIds are required";
  else
  {
    $taskTemplateIds= json_decode($taskTemplateIdsEncoded, true);
    if (is_array($taskTemplateIds))
    {
      foreach($taskTemplateIds as $templateId)
      {
        $templateId= cleanInteger($templateId);
        if ($templateId == "ERROR")
        {
          $errors[]= "One or more TaskTemplateIds is not an integer.";
          break;
        }
      }
    }
    else
      $errors[]= "TaskTemplateIds not an array";
  }

  $findSql= "select ii.item_id, ii.title, ii.item_desc, ii.view_order, ii.owner as task_owner, ii.parent, i.owner as job_owner, ii.sales_item_id from cuissuesitem ii
             inner join cuissues i on ii.issue_id = i.issue_id where ii.item_id in (" . implode(",", $taskTemplateIds) . ") and ii.issue_id = $issueId";

  $getNextIdSQL= "select nextval('cutrackitem_trackitem_id_seq'::text::regclass) as next";
  $nextResults= runSelectStatement($getNextIdSQL, $dbh, array("next" => "next"));
  $errors= array_merge($errors, $nextResults["error"]);
  $sqls= array($findSql, $getNextIdSQL);
  $nextTaskId= intval($nextResults["record"][0]["next"]);
  $parentTaskId= $nextTaskId;
  $subTaskArray= array();

  if (count($taskTemplateIds) > 0)
  {
      $results= runSelectStatement($findSql, $dbh, array("item_id" => "taskId", "title" => "taskTitle", "item_desc" => "taskDescription", "view_order" => "taskViewOrder",
        "task_owner" => "taskOwner", "parent" => "taskParent", "job_owner" => "jobOwner", "sales_item_id" => "billingItemId"));
      $errors= $results["error"];

    $insertTasksSQL= "insert into cutrackitem (trackitem_id, track_id, title, item_desc, view_order, parent, assigned_to, issue_item_id, billing_item_id)";
    $taskSQLPartArray= array();

    $taskList= array();
    foreach($results["record"] as $record)
    {
      if (intval($record["billingItemId"]) != 0)
        $taskList[]= "(" . intval($record["taskId"]) . ", " . intval($record["billingItemId"]) . ")";
    }

    if (count($taskList) != 0)
    {
      $sql= "select t.task_id, fd.id from cutrack ct inner join cubillfeaturedetail bfd on ct.billing_feature_id = bfd.id and ct.track_id= $trackId
            inner join cubillsalesorderdetail sod on bfd.sales_order_detail_id = sod.id inner join cubillfeaturedetail fd on sod.id = fd.sales_order_detail_id
            inner join (values " . implode(", ", $taskList) . ") as t (task_id, sales_item_id) on fd.sales_item_id= t.sales_item_id";
      $tempResults= runSelectStatementMap($sql, $dbh);
      $sqls[]= $sql;
      $errors= array_merge($errors, $tempResults["error"]);
      $map=$tempResults["record"];

      for($i=0, $count=count($results["record"]); $i != $count; $i++)
      {
        $billingItemId= $map[$results["record"][$i]["taskId"]];
        if (isset($billingItemId))
          $results["record"][$i]["billingItemId"]= $billingItemId;
      }
    }

    $index = 0;
    foreach($results["record"] as $record)
    {
        $title= prepSave($record["taskTitle"], 80);
        $description= prepSave($record["taskDescription"]);
        $viewOrder= $taskLength + intval($record["taskViewOrder"]);
        $owner= prepSave($record["taskOwner"], 12);
        $parent= $record["taskParent"];
        $taskTemplateId= $record["taskId"];
        $billingItemId= $record["billingItemId"];

        if ($parent == 0)
        {
          $taskSQLPartArray[]= "($parentTaskId, $trackId, '$title', '$description', $viewOrder, 0, '$owner', $taskTemplateId, $billingItemId)";
          $parentArray[$taskTemplateId]= $index++;
          $parentTaskId++;
        }
        else
        {
          if (!isset($subTaskArray[$parent]))
            $subTaskArray[$parent]= array();
          $subTaskArray[$parent][]= $record;
        }
        $hasTasks= true;
    }

    $subTaskId= $parentTaskId;
    if ($hasTasks)
    {
        $transactionSQLs[]= "$insertTasksSQL values " . implode(", ", $taskSQLPartArray);

        $taskSQLPartArray= array();

        foreach($subTaskArray as $parentId => $detailRecords)
        {
            $parentTaskId= $nextTaskId + $parentArray[$parentId];

            foreach($detailRecords as $record)
            {
              $title= prepSave($record["taskTitle"], 80);
              $description= prepSave($record["taskDescription"]);
              $viewOrder= $record["taskViewOrder"];
              $owner= prepSave($record["taskOwner"], 12);
              $taskTemplateId= $record["taskId"];
              $billingItemId= $record["billingItemId"];

              $taskSQLPartArray[]= "($subTaskId, $trackId, '$title', '$description', $viewOrder, $parentTaskId, '$owner', $taskTemplateId, $billingItemId)";
              $subTaskId++;
            }
        }

        if (count($taskSQLPartArray) > 0) // Make sure that there are any subtasks.
        {
          $transactionSQLs[]= "$insertTasksSQL values " . implode(", ", $taskSQLPartArray);
        }

        $transactionSQLs[]= "select setval('cutrackitem_trackitem_id_seq'::text::regclass, $subTaskId, false)"; // Because inserts are done with the id, sequence is not updated.
    }

    $transactionSQLs[]= "update cutrack set last_activity_date= now() where track_id= $trackId";
  }

  $sqls= array_merge($sqls, $transactionSQLs);
  if (count($errors) == 0)
  {
    $results= runExecStatement(implode(";", $transactionSQLs) . ";", $dbh);
    $errors= array_merge($errors, $results["error"]);
  }

  $records= array();
  $requerySQL= "select i.trackitem_id, i.title, i.item_desc, i.complete_date, i.task_status, i.parent, i.assigned_to, i.last_status_changed, i.view_order as position, i.issue_item_id
                from cutrackitem i where i.track_id= $trackId and i.issue_item_id in (" . implode(",", $taskTemplateIds) . ")
                order by i.parent, i.task_status, i.view_order, i.trackitem_id";
  $sqls[]= $requerySQL;
  if (count($taskTemplateIds) > 0)
  {
    $results= runSelectStatement($requerySQL, $dbh, array("trackitem_id" => "taskId", "title" => "title", "item_desc" => "description", "complete_date" => "completedOn", "position" => "position",
                                                   "task_status" => "statusId", "parent" => "parentTaskId", "assigned_to" => "ownerId", "last_status_changed" => "statusChangedDate",
                                                   "issue_item_id" => "taskTemplateId"));
    $errors= array_merge($errors, $results["error"]);
    $records= $results["record"];
  }

  return array("sql" => $sqls, "error" => $errors, "record" => $records);
}

function setReportCookie($pEnv)
{
  $parameters= array();
  dms_import_v2($parameters, "BOTTOM_LEVEL", array("sort" => "string", "filter" => "string", "showDeleted" => "string", "showCompleted" => "string"));
  $sort= $parameters["BOTTOM_LEVEL"]["sort"];
  $filter= $parameters["BOTTOM_LEVEL"]["filter"];
  $showDeleted= $parameters["BOTTOM_LEVEL"]["showDeleted"] == "Y" ? "Y" : "N";
  $showCompleted= $parameters["BOTTOM_LEVEL"]["showCompleted"] == "Y" ? "Y" : "N";

  $errors= array();
  $sortUnpacked= json_decode($sort, true);
  $filterUnpacked= json_decode($filter, true);
  if ($sort != "" && !is_array($sortUnpacked))
    $errors[]= "Sort is invalid";
  if ($filter != "" && !is_array($filterUnpacked))
    $errors[]= "Filter is invalid";

  $expiresInDay= time()+86400;
  $expiresInYear= time()+86400*365;

  HCU_setcookie_env($pEnv, "Job_View", "sort=" . urlencode($sort) . "&filter=" . urlencode($filter) . "&showDeleted=$showDeleted&showCompleted=$showCompleted", $expiresInDay);
  return array("error" => $errors, "sql" => array());
}

function clearReportCookie($pEnv)
{
  if (isset($_COOKIE["Job_View"]))
  {
    unset($_COOKIE["Job_View"]);
    HCU_setcookie_env($pEnv, "Job_View", "", time() - 3600);
  }
  return array("error" => array(), "sql" => array());
}