msgECO.i

<?php

/* Include file for secure messages. This has all the entry points for gathering the necessary data for display,
 *  or to perform any operations.
 *
 * Functions will return a structure in this format:
 *   [status][response] = true/false
 *           [code] = "000" or error
 *           [message] = "success" or error message
 *   [data] = array
 */
define( "SECURE_MESSAGES_ERROR_CODE", "5020" );

/**
 * function msgCheckForMessages($pDbh, $pHBEnv)
 * Check to see if any unread messages exist. Return a boolean reply.
 * deprecated -- This function is not currently used in the desktop version which adds the unread attribute inside of the read function.
 * 11/14/2017 MH Not deprecated -- the app still uses it.
 *
 * @param $pDbh -- the database connection
 * @param $pHBEnv -- the environment variables
 *
* Environmental variables needed:


 *    $pHBEnv["Cu"]

 *    $pHBEnv["Uid"]

 *
 * @return array
 *    $status -- array of the statuses
 *        $response -- "true" if there is a response, false otherwise.
 *        $code -- "000" if successful, SECURE_MESSAGES_ERROR_CODE otherwise.
 *        $message -- "success" if successful, error message if not.
 *    $data -- empty array if error, array if not.
 *        $message_count -- number of messages.
 *        $has_messages -- if there are messages.
 */
function msgCheckForMessages($pDbh, $pHBEnv) {
  $Cu = $pHBEnv["Cu"];
  $Uid = $pHBEnv["Uid"];
      if ( empty($Cu) || empty($Uid)  ) {
        throw new exception('Missing Parameters', 1);
      }

  try {
    $sql = "select count(*) from cuadmeco where cu = '$Cu'
            and user_id = '$Uid'
            and origination = 0 and unread;";
    $rh = db_query($sql, $pDbh);

    if ($rh) {
      list( $msgct ) = db_fetch_array($rh, 0);
    }

    $msgct = ($msgct ? $msgct : 0);
    db_free_result($rh);

    // always return success
    $return = array("code" => "000", "homecuData" => array( "message_count" => $msgct, "has_messages" => ($msgct > 0) ? "1" : "0" ));

  } catch (Exception $e) {
    $return = array("homecuErrors" => $e->getMessage(), "code" => SECURE_MESSAGES_ERROR_CODE);
  }

  return $return;
}

/**
 * function msgReadMessages($dbh, $pHBEnv)
 * Read the messages. Just get the thread topic, the thread id, and flag whether there are unread messages.
 * Order the threads by most recent tread date, descending.
 *
 * @param $dbh -- the database connection
 * @param $pHBEnv -- the environment variables
 *
 * Environmental variables needed:


 *    $pHBEnv["Cu"]

 *    $pHBEnv["Uid"]

 *    $pHBEnv["HCUPOST"]["what"] — The subject filter.  This can be empty or undefined.
 *
 * @return array
 *    $code -- "000" if successful, SECURE_MESSAGES_ERROR_CODE otherwise.
 *    $homecuInfo -- doesn't exist but could.  (If it did, it would show a message.)
 *    $homecuErrors -- Doesn't exist if successful, otherwise is a string with the error message.
 *    $homecuData -- Doesn't exist if not successful, otherwise is an array of results for the grid.
 */
function msgReadMessages($dbh, $pHBEnv) {

  try
  {
    $Cu= $pHBEnv["Cu"];
    $Uid= $pHBEnv["Uid"];
      if ( empty($Cu) || empty($Uid)  ) {
        throw new exception('Missing Parameters', 1);
      }
    $tz= GetCreditUnionTimezone($dbh, $Cu); // Returns the default timezone of mountain if not found or SQL error.  Therefore, this doesn't need to be trapped.
    $localzone="set time zone 'US/Mountain';";
    $what= strtolower(HCU_array_key_value('what',$pHBEnv["HCUPOST"]));
    $value= str_replace("_", "^_", str_replace("%", "^%", str_replace("^", "^^", $what))); // % and _ in the string are intrepreted as literals NOT wildcards.
    $whereEnd= "like '%$value%' escape '^'"; // Use something else than the default backslash because that might be needed to escaped multiple times.

    $where= trim($what) == "" ? "" : " and lower(subject) $whereEnd";

    $sql = "set time zone '$tz'; ";
    $sql .= "select messageid, parentid, to_char(date,'MM/DD/YYYY HH24:MI') as date, unread, memdeleted, admdeleted, origination, subject
             from cuadmeco a where cu = '$Cu' and user_id = '$Uid' and not memdeleted $where order by messageid; ";

    $sth = db_query($sql,$dbh);

    if (!$sth)
      throw new exception("Read query failed.", 1);

    // set timezone back to default
    $sth1 = db_query($localzone,$dbh);

    if (!$sth1)
      throw new exception("Timezone query failed.", 2);

    $aryMessages = array();
    for ($i= 0; $msgRow = db_fetch_array($sth, $i); $i++) {
      // flag for if the meesage is unread and from the CU
      $new = ($msgRow['unread'] == 't' && $msgRow['origination'] == 0) ? 1 : 0;

      // parentid is the thread id
      $p = $msgRow['parentid'];

      // messageid is used to order the record
      $m = $msgRow['messageid'];

      // origination: 0 = admin, 1 = user
      $o = $msgRow["origination"];

      // convert any characters that got in from being copied from an editor
      $subject = convertMicrosoftCharacters( $msgRow["subject"] );

      // Was On2 but now is closer to On.
      if (!isset($aryMessages[$p]))
        $aryMessages[$p] = array( "threadId"=>$p, "order" => $m, "unread"=>$new, "origination"=>$o, "subject"=>$subject, "date"=>$msgRow["date"] );
      else {
        // only keep unique topics so just update the existing record
        $aryMessages[$p]["order"] = $m;
        // if the parent id == the message id, overwrite the subject since we found the correct initial message
        if ( $p == $m ) {
          $aryMessages[$p]["subject"] = $subject;
        }
        // only update the unread if it is a new message
        if ( $new == 1 ) {
          $aryMessages[$p]["unread"] = $new;
        }
        // keep the latest origination
        $aryMessages[$p]["origination"] = $o;

        $aryMessages[$p]["date"] = $msgRow["date"];
      }
    }

    // now sort the messages
    usort( $aryMessages, "SortMessages" );

    return array("code" => "000", "homecuData" => $aryMessages);
  } // End try
  catch (exception $e)
  {
    return array("homecuErrors" => $e->getMessage(), "code" => SECURE_MESSAGES_ERROR_CODE);
  }
}

/**
 * function msgReadMessageThread($dbh, $pHBEnv)
 * Read all the messages in a given topic/thread.
 *
 * @param $dbh -- the database connection
 * @param $pHBEnv -- the environment variables
 *
 * Environmental variables needed:


 *    $pHBEnv["Cu"]

 *    $pHBEnv["Uid"]

 *    $pHBEnv["HCUPOST"]["parentid"]
 *
 * @return array
 *    $code -- "000" if successful, SECURE_MESSAGES_ERROR_CODE otherwise.
 *    $homecuInfo -- doesn't exist but could.  (If it did, it would show a message.)
 *    $homecuErrors -- Doesn't exist if successful, otherwise is a string with the error message.
 *    $homecuData -- Doesn't exist if not successful, otherwise is an array of results for the thread.
 */
function msgReadMessageThread($dbh, $pHBEnv) {
  try
  {
      $parentId = $pHBEnv["HCUPOST"]["parentid"]; // thread id
      $Cu= $pHBEnv["Cu"];
      $Uid= $pHBEnv["Uid"];
      if ( empty($Cu) || empty($Uid) || empty($parentId) ) {
        throw new exception('Missing Parameters', 1);
      }

      $tz= GetCreditUnionTimezone($dbh, $Cu);
      $localzone="set time zone 'US/Mountain';";
      $sqlunread = "update cuadmeco set unread=FALSE where cu = '$Cu' and parentid = $parentId and user_id = '$Uid' and origination = 0";
      $sql = "set time zone '$tz';
              select messageid, parentid, to_char(date,'MM/DD/YYYY HH24:MI TZ') as date, unread, origination, subject, messagetext from cuadmeco
              where cu = '$Cu' and parentid = $parentId and user_id = '$Uid' and not memdeleted order by messageid; ";
      $sth = db_query($sql,$dbh);

      if (!$sth)
        throw new exception("Read query failed.", 1);

      $sth1 = db_query($localzone,$dbh);

      if (!$sth1)
        throw new exception("Localzone query failed.", 2);

      $aryResult= array();
      for($i= 0; $msgrow = db_fetch_array($sth, $i); $i++ )
      {
        // convert any characters that got in from being copied from an editor
        $messageText = convertMicrosoftCharacters( $msgrow['messagetext'] );

        $aryResult[] = array( "date"=>$msgrow['date'], "origination"=>$msgrow['origination'], "subject" => $msgrow['subject'], "message"=>$messageText );
      }
      $sth = db_query($sqlunread,$dbh);

      if (!$sth)
        throw new exception("Update query failed.", 3);

      return array("code" => "000", "homecuData" => $aryResult);
  }
  catch(exception $e)
  {
    return array("homecuErrors" => $e->getMessage(), "code" => SECURE_MESSAGES_ERROR_CODE);
  }
}

/**
 * function msgSendMessage($dbh, $pHBEnv, $pMC)
 * Send the given message. If an admin email exists then send an email to notify the admin user a message is present.
 *
 * @param $dbh -- the database connection
 * @param $pHBEnv -- the environment variables
 * @param $pMC -- the dictionary
 *
 * Environmental variables needed:


 *    $pHBEnv["Cu"]

 *    $pHBEnv["Uid"]

 *    $pHBEnv["HCUPOST"]["subject"]
 *    $pHBEnv["HCUPOST"]["message"]
 *    $pHBEnv["HCUPOST"]["parentid"]
 *    $pHBEnv["HCUPOST"]["what"] — The subject filter.  This can be empty or undefined (for msgReadMessages).
 *
 * @return array
 *    $code -- "000" if successful, SECURE_MESSAGES_ERROR_CODE otherwise.
 *    $homecuInfo -- doesn't exist but could.  (If it did, it would show a message.)
 *    $homecuErrors -- Doesn't exist if successful, otherwise is a string with the error message.
 *    $homecuData -- Doesn't exist if not successful, otherwise is an array of results for the grid.
 */
function msgSendMessage($dbh, $pHBEnv, $pMC) {
  try
  {
      $subject = $pHBEnv["HCUPOST"]["subject"]; // message subject
      $message = $pHBEnv["HCUPOST"]["message"]; // message itself
      $parentId = $pHBEnv["HCUPOST"]["parentid"]; // thread id

      $Cu= $pHBEnv["Cu"];
      $Uid= $pHBEnv["Uid"];
      if ( empty($Cu) || empty($Uid) ) {
        throw new exception('Missing Parameters', 1);
      }

      $subject = prep_save(hcu_displayHtml( preg_replace( "/[\`\;]/", "", $subject ), ENT_NOQUOTES ) );
      if ( trim( $subject ) == '' )
        throw new exception($pMC->msg('Message subject cannot be blank', HCU_DISPLAY_AS_RAW), 1);

      $messageText = prep_save(hcu_displayHtml( preg_replace( "/[\`\;]/", "", $message ), ENT_NOQUOTES ) );
      if ( trim( $messageText ) == '' )
        throw new exception($pMC->msg('Message body cannot be blank', HCU_DISPLAY_AS_RAW), 2);

      $sql = "insert into cuadmeco (parentid, cu, user_id, date, unread, memdeleted, admdeleted, origination, subject, messagetext)
              values ( " . ($parentId ? $parentId : "currval('cuadmeco_messageid_seq')") . ", '$Cu', $Uid, CURRENT_TIMESTAMP, TRUE, FALSE, FALSE, 1, '$subject','$messageText');";
      $sth = db_query($sql,$dbh);

      if (!$sth)
        throw new exception("Insert query failed.", 3);

      // ** MWS 9/8/2006 -- Check for an email account in the admnotify table -- if there is then I want to send an email to the credit union disclosing that a message was sent
      $sql = "select email from cuadmnotify where cu = '$Cu' and role = 'securenotify' ";
      $emRS = db_query($sql, $dbh);

      if (!$emRS)
        throw new exception("Email query failed.", 4);

      if ($emRow = db_fetch_array($emRS)) {
        $secEmail = trim($emRow['email']);
        if ($secEmail != '') {

          // * Send Email Notification
          $emMsg = "\t $Cu Secure Message\n\nYou have received a secure message from a member.\nPlease log in to your admin to view the message.\n\n";
          $emSubject = "New Secure Message";

          $notify = new ErrorMail;

          $notify->mailto = $secEmail;    // Set to transfer email setup in CU Admin
          $notify->replyto = "noreply@homecu.net";
          $notify->subject = $emSubject;
          $notify->msgbody = $emMsg;
          $notify->callingfunction = __FUNCTION__;
          $notify->file = __FILE__;
          $notify->cu = $Cu;
          $notify->SendMail();

        }
      }

      return msgReadMessages($dbh, $pHBEnv);
    }
    catch(exception $e)
    {
      return array("homecuErrors" => $e->getMessage(), "code" => SECURE_MESSAGES_ERROR_CODE);
    }
}

/**
 * function msgDeleteMessageThread($dbh, $pHBEnv)
 * Delete the given message. The user has already confirmed it is okay.
 *
 * @param $dbh -- the database connection
 * @param $pHBEnv -- the environment variables
 *
* Environmental variables needed:


 *    $pHBEnv["Cu"]

 *    $pHBEnv["Uid"]

 *    $pHBEnv["HCUPOST"]["parentid"]
 *    $pHBEnv["HCUPOST"]["what"] — The subject filter.  This can be empty or undefined (for msgReadMessages).
 *
 * @return array
 *    $code -- "000" if successful, SECURE_MESSAGES_ERROR_CODE otherwise.
 *    $homecuInfo -- doesn't exist but could.  (If it did, it would show a message.)
 *    $homecuErrors -- Doesn't exist if successful, otherwise is a string with the error message.
 *    $homecuData -- Doesn't exist if not successful, otherwise is an array of results for the grid.
 */
function msgDeleteMessageThread($dbh, $pHBEnv) {
  try
  {
      $parentId = $pHBEnv["HCUPOST"]["parentid"]; // thread id
      $Cu= $pHBEnv["Cu"];
      $Uid= $pHBEnv["Uid"];
      if ( empty($Cu) || empty($Uid) || empty($parentId) ) {
        throw new exception('Missing Paramters', 1);
      }

      if ( $parentId > 0 ) { // If there is no parentId, I guess that it is "successful" based on there being nothing to delete.
        $sql = "update cuadmeco set memdeleted = TRUE where cu = '$Cu' and user_id = '$Uid' and parentid = $parentId;
                delete from cuadmeco where cu = '$Cu' and parentid = $parentId and user_id = '$Uid' and admdeleted and memdeleted;";
        $sth = db_query($sql,$dbh);

        if (!$sth)
          throw new exception("Update/delete query failed.", 1);
      }

      return msgReadMessages($dbh, $pHBEnv);
  }
  catch(exception $e)
  {
    return array("homecuErrors" => $e->getMessage(), "code" => SECURE_MESSAGES_ERROR_CODE);
  }
}

/**
 * function getCUTimeZone($dbh, $Cu)
 * Gets the timezone from the credit union
 * @deprecated -- This function is replaced by a global function: GetCreditUnionTimezone($dbh, $Cu).  This function is in hcuCommon.i.
 *
 * @param $dbh -- the database connection.
 * @param $Cu -- the credit union
 *
 * @return $tz -- the timezone of the credit union.
 */
function getCUTimeZone($dbh, $Cu) {
      // get CU time zone
      $sql = "select rtrim(tz) from cuadmin where cu='$Cu'";
      $sth = db_query($sql,$dbh);
      if($sth) { list($tz) =  db_fetch_array($sth,0); }
      $tz = ("$tz" == "" ? "Mountain" : $tz);
      if (strpos("$tz","/") === false) { $tz = "US/$tz"; }
      return $tz;
 }
/******** functions **********/
function SortMessages( $a, $b ) {
  return ( $a["order"] < $b["order"] ? 1 : -1);
} // Sort Messages