monitor_notes.data

<?php

    /*
        * NOTE: This is a little different from my main goal.  The goal of this is to create ONE
            * script that may be included in any script that has notes on them.
            ie.  cuview will display these notes.  It will include this script to handle any
                        database update and to fetch the current data to display based on the sort/view options


            * one difference from main goal is this script needs some includes handled for it.
            * since this is in /hcuadm, the following need to be required before this script should be included

            // require("cu_top");


        * Parts of this script

        * UPDATE / INSERT
            * IF I know a note is being posted, then I want to UPDATE/INSERT that information into the database

        * SELECT
            * Select the current result set based on the the View / Sort Order requested by the user

        * ERRORS
            * Any errors need to be handled the same way, from the Database Update/Select
            * this is to ensure when the data is displayed using either {monitor_notes.desktop OR monitor_notes.mobile} OR monitor_notes.display
            * they can display the correct information



        * ** IDEALLY -- I think data should be put into a multi-dimension array, possibly a large serialized object to be
                                        pulled apart later by the presentation layer
        * DISPLAY ARRAY



   * Changes to the Way this works..
   * This should ALWAYS return a JSON value of our standard return status codes
   Format of the array
   * [status]
   *      [code]
   *           000 -- SUCCESSFUL result set
   *           999 -- An error occurred - print the errors to the user
   *      [severity]
   *          (really these just og along with the code and are one to one
   *          SUCCESS - SUCCESSFUL
   *          FAILURE - Error occurred
   *      [errors]
   *          An array of error message to print to the user
   *      [messages]
   *          An array of informative messages to print to the user, commonly used
   *          for SUCCESSFUL NOTES entry
   *
   * [data]
   *      [users] (ONLY Returned in DEFAULT State)
   *            This is a numeric array of users to be used on the form
   *         [idx] => array([user_name] => value)
   *      [topics] (ONLY Returned in DEFAULT State)
   *            This is a numeric array of Topics to be used on the form
   *         [idx] => [value]
   *      [searchfields] (ONLY Returned in DEFAULT State)
   *            This is a numeric array of Search fields to be used on the form
   *         [idx] => array(field_dbname => value, field_display => value)
   *            when sending information back to the server, use the idx as the posted value
   *            and field_display as the value shown to user
   *      [notes]
   *            This is a numeric array of information for the notes, fields are
   *


   ChangeLog:
   * CL1 - mws 4/26/13 - Made changes on how data is retrieved and eliminate any
   *                      extended ascii characters from the messagetext
   * 06/15/13 mbl - Added several new pre-determined topics.
   * 09/24/15 SPB - removed issue and task notes from query.
    */
  $monLibrary= dirname(__FILE__) . "/../library";
  $sharedLibrary= dirname(__FILE__) . "/../../shared/library";
  require_once("$monLibrary/cu_top.i");
  require_once("$monLibrary/ck_hticket.i");
  require_once("$sharedLibrary/errormail.i"); // Be sure errormail is included

  // To prevent console errors.
  $wc = "";
  $frmPostComment = "";
  $frmPostOrder = "";
  $frmSearchFor = "";
  $frmViewBy = "";
  $frmViewOrder = "";
  $sql_ORDER = "";

try {
  $retStatus_ary = Array(
                        'status' => Array('code'=>'000', 'severity'=>'SUCCESS', 'errors' => Array(), 'messages' => Array()),
                        'data' => ''
                        );


  if (!CheckPerm($link, $Hu, 'monitor_notes', $_SERVER['REMOTE_ADDR'])) {
    // ** Permissions failed
    // ** redirect to new page
    throw new Exception('Invalid permissions to view, update or add notes');
  }

  $dms_ok=array('frmViewBy'=>'string', 'frmViewOrder'=>'string', 'frmViewRange'=>'string',
                'frmPostComment'=>'string', 'frmParentId'=>'digits', 'frmSubmitBy'=>'string',
                'frmNotify'=>'string', 'frmTopic'=>'string', 'frmComment'=>'string',
                'frmNotifyNote'=>'string', 'frmHideNote'=>'string', 'frmSearchFor'=>'string',
                'frmSearchType'=>'string', 'frmSearchIn'=>'string', 'cuid'=>'string',
                'frmParentTopic'=>'string', 'frmNoteId'=>'digits', 'user_name' => 'string',
                'frmPostOrder'=>'string');

  $frmParentId = isset($frmParentId) ? $frmParentId : 0;
  $frmNoteId = isset($frmNoteId) ? $frmNoteId : 0;

    // ** Declare used variables
    $data_notes_list = Array();

  $returnDefaults = true; // * By default be prepared to return ALL array segments

  // * Now evaluate for the function exists again, if it does NOT, then
    // * we are on a legacy server which does not require the importing of variables
  if (function_exists('dms_import')) {
    dms_import($dms_ok);
  }


    $FORM_ERRORS = "";          // ** This is the list of ERRORS to display for the notes
  $form_return_msgs = Array();   // ** THIS is a list of MESSAGES (NOT ERRORS) to display for the notes

    // ** GLOBAL VALUES FOR THESE SCRIPTS

    // * HomeCU PreDetermined Topics
    $dataNoteTopics_ary = Array("Contract Notes",
                              "Android App Implementation",
                              "Appliance Settings",
                              "Billing",
                              "Check Images",
                              "Comments",
                              "Implementation Notes",
                              "iPhone App Implementation",
                              "Mobile Web Implementation",
                              "On-site IVR Settings",
                              "Support",
                              "Web Site"
                              );


    // * HomeCU Create PreDetermine List of Searchable Fields
    $dataSearchFields_ary = Array(0 => Array("field_dbname" => "cuinfo_notes.author", "field_display" => "Author"),
                                                                1 => Array("field_dbname" => "cuinfo_notes.subject", "field_display" => "Topic"),
                                                                2 => Array("field_dbname" => "cuinfo_notes.messagetext", "field_display" => "Note"));

    // ** for the script imp_main I used cuid instead of user_name -- WHAT was I thinking

  // ** THE BELOW CODE MAY NEED TO BE MOVED TO monitor_notes.desktop
    if (basename($_SERVER['SCRIPT_NAME']) == 'imp_main.prg') {
        $dataScriptName = $_SERVER['PHP_SELF'] . "?cuid=$cuid&wc=$wc";
        $user_name = $cuid;
    } else {
        $dataScriptName = $_SERVER['PHP_SELF'] . "?user_name=$user_name&wc=$wc";
    }



    // ** SQL UPDATE / INSERT
    if ($frmPostComment != '') {
        // * POSTED FORM --
    $returnDefaults = false;
        if ($frmPostComment != '') {
            // ** VALIDATE FORM
            // ** Submitted BY:
            // ** Subject
            // ** Notify
            // ** Note
        } else {
            // ** Hide A NOTE
        }

        if ($FORM_ERRORS == "") {
            if(intval($frmHideNote) == 0) {
                // * PASS VALIDATION
                // ** CREATE SQL

                // ** ORIGINATION OF THE RECORD is determined by the master script,
                    // ** 1 - monitor
                    // ** 2 - implementation

                $db_set_origination = (basename($_SERVER['SCRIPT_NAME']) == 'imp_main.prg' ? 2 : 1);

                // ** Determine the Note Topic
                    // * If the ParentId is set, then the topic will be provided in frmParentTopic

                $save_subject = prep_save((intval($frmParentId) > 0 ? $frmParentTopic : $frmTopic), 5000);
                if (intval($frmNoteId) == 0) {
                    // ** INSERT
                    $sql = "INSERT INTO cuinfo_notes
                                    (parentid, author, assignee, createddate, origination, subject, messagetext, activitydate, cu)
                                    VALUES
                                    (" . ($frmParentId == '' ? 'NULL' : intval($frmParentId)) . ", '" . prep_save($frmSubmitBy, 12) . "',
                                        '" . prep_save($frmNotify, 12) . "', now(), '$db_set_origination', '$save_subject',
                                        '" . prep_save($frmComment, 5000) . "', now(), '" . prep_save($user_name, 10) . "'); ";

                    if (intval($frmParentId) > 0) {
                        // ** UPDATE parentid ActivityDate
                        $sql .= "UPDATE cuinfo_notes
                                         SET activitydate = now()
                                         WHERE parentid = " . intval($frmParentId) . " ";
                    }
                } else {
                    // ** UPDATE -- REALLY ONLY ALLOW THE NOTE TO BE UPDATED
                    $sql = "UPDATE cuinfo_notes
                                    SET messagetext = '" . prep_save($frmComment, 5000) . "'
                                    WHERE messageid = '" . intval($frmNoteId) . "'; ";


                    if (intval($frmParentId) > 0) {
                        // ** UPDATE parentid ActivityDate
                        $sql .= "UPDATE cuinfo_notes
                                         SET activitydate = now()
                                         WHERE messageid = '" . intval($frmParentId) . "'; ";
                    }
                }
            } elseif ($frmHideNote == 1) {
                // * HIDE A NOTE
                $sql = "UPDATE cuinfo_notes
                                SET dontshowafter = current_date
                                WHERE messageid = '" . intval($frmNoteId) . "'; ";

            }

            $notes_upd_rs = db_query($sql, $link);
 // echo $sql;
            if ($notes_upd_rs !== FALSE) {
                // ** DATA UPDATE SUCCESSFUL
                // ** PERFORM POST DATA ACTIONS

                $form_return_msgs[] = "Your request was successful.";

                if ($frmNotify != '') {
                    // ** NOTIFY USER if a user was selected to be notified
                    $sql = "SELECT *
                                    FROM dmsmonitorusers
                                    WHERE user_name = '" . prep_save($frmNotify, 12) . "' ";
                    $notify_rs = db_query($sql, $link);
                    if ($notify_rs !== FALSE) {
                        $notify_row = db_fetch_array($notify_rs);
                        if (trim($notify_row['notifyemail']) !== '') {
                            // The user to notify has an email -- SO.. NOTIFY THEM
                            // * Create Mail Message
                            $notify_SendTo = trim($notify_row['notifyemail']);

                            // ** Determine the FROM -- Author Email
                            $sql = "SELECT *
                                            FROM dmsmonitorusers
                                            WHERE user_name = '$Hu' ";
                            $author_rs = db_query($sql, $link);
                            $author_row = db_fetch_array($author_rs);
                            if (trim($author_row['notifyemail']) != '') {
                                $notify_SendFrm = trim($author_row['notifyemail']);
                            } else {
                                $notify_SendFrm = "nobody@homecu.net";
                            }

                            $notify_Subject = "There is a HomeCU note you should see.";

                            $notify_Message = "";
                            if ($frmNotifyNote != '') {
                                // * A Special NOTE was added
                                $notify_Message .= "NOTE**: $frmNotifyNote";

                            }
              $notify_Message .= "\n\nCU - " . $user_name . "\n";
              $notify_Message .= "Entered by " . $Hu;
                            $notify_Message .= "\n\n" . $frmComment;

                            $notify = new ErrorMail;
                            $notify->mailto = $notify_SendTo;
                            $notify->replyto = $notify_SendFrm;
                            $notify->mailfrom = $notify_SendFrm;

                            $notify->subject = $notify_Subject;
                            $notify->msgbody = $notify_Message;
                            $notify->file = __FILE__;
                            $notify->cu = $user_name;

                            $notify->SendMail();


                        } else {
                            // The user does NOT have email setup
                            $form_return_msgs[] = "The user " . dms_disphtml($frmNotify) . " does not have an email setup for notification.";
                        }
                    }
                }
            } else {

                // ** UPDATE Date Error
                $FORM_ERRORS = "Unable to update data at this time.";
            }
        }
    } elseif ($frmPostOrder != '') {
    // ** do NOT return any defaults if we are doing a search
    $returnDefaults = false;
  }




    // ** POSTED FORM VALUES

        // ** If the form is posted I want to set the information on the form back to these values
        // * I have a choice.  I can set a value prefixed with {data} instead of {frm} and use that or
        // * I can use a the jquery populate add-in. I would just have to create a JSON object of the values I want to
        // * to set in this script..





    // ** SELECT data from the cuinfo_notes table
    $sql = "";
    $sql_WHERE = "WHERE cuinfo_notes.trackitem_id = 0 and cuinfo_notes.track_id = 0 ";


    // ** FOR NOW -- I will put the WHERE CLAUSE CREATION HERE
        /*
            * Search For Rules
            * Fields
                * frmSearchIn - The search may look in one field at a time.  Possibly later I can add a dynamic or list each field to have a separate where
                    * However, for now.. One field at a time. This is the field identifier.  I will of course make sure the field exists.
                * frmSearchType - This option will determine how we will be creating the query
                                                    * - (0) Search for the string frmSearchFor as typed in the field frmSearchIn
                                                    * - (1) Match any word, but it must be the WHOLE WORD.
                                                    * - (2) Match any word, anywhere in the frmSearchIn field
                                                    * - (3) Regular Expression.  Evaluate Reg Ex on the field frmSearchIn

                * frmSearchWhole - Options here. This is to check if I want to match the WHOLE WORD. BY default my search should allow a search anywhere in string.
                                                * - however when I check for a WHOLE WORD I will surround each word with spaces to ensure it finds words
                * frmSearchRegEx - This is mutually exclusive from whole word.  So I am thinking I may use radio.. BUT this will allow the user to enter some sort
                                                * - of postgres regular expression.  I am going to be useing pg_escape_string, need to make sure these still work.
                * frmSearchFor - And that brings us to the search query.
                                                        * Regular Expressions, i am thinking just use what they enter.
                                                        * Everything else.  I am going to replace white space with a | (pipe).. This will allow for each word to be searched for

        */

    $sql_WHERE_SEARCH = "";
    if ($frmSearchFor != '') {
        // * Be sure the fieldname is correct, if NOT found, then we skip
        if ($dataSearchFields_ary[$frmSearchIn]['field_dbname'] != '') {
            // ** SOMETHING IS ENTERED
            switch (intval($frmSearchType)) {
                case 0:
                    // Search for the string frmSearchFor in the field frmSearchin
                    $sql_WHERE_SEARCH = " LOWER(" . $dataSearchFields_ary[$frmSearchIn]['field_dbname'] . ") LIKE ('%" . strtolower(prep_save($frmSearchFor, 200)) . "%') ";
                    break;
                case 1:
                case 2:
                    // ** FIND A INSTANCE OF A WORD
                    // ** First we replace all white space with a |
                    $data_frmSearchFor = trim($frmSearchFor);           // FIRST STRIP ALL BEGINNING AND END SPACES
                    $data_frmSearchFor = prep_save(preg_replace('/\s+/', '|', $data_frmSearchFor), 200);

                    if (intval($frmSearchType) == 2) {
                        // * For matching whole word, I want to add some spaces
                        $data_frmSearchFor = " " . str_replace('|', ' | ', $data_frmSearchFor) . " ";
                    }
                    $sql_WHERE_SEARCH = " LOWER(" . $dataSearchFields_ary[$frmSearchIn]['field_dbname'] . ") SIMILAR TO '%(" . strtolower($data_frmSearchFor) . ")%' ";
                    break;
                case 3:
                    // ** Match a Regular Expression
                    $sql_WHERE_SEARCH = " " . $dataSearchFields_ary[$frmSearchIn]['field_dbname'] . " ~* '" . prep_save($frmSearchFor, 200) . "' ";
                    break;
                default:
                    // NOTHING
                    break;
            }
        }
    }
    // ** Also I will need to be adding the query to only search for the current credit union which is the current user_name field.
        // * This will need to be altered when I make a script for ALL CU's
    $sql_WHERE_SEARCH  .= ($sql_WHERE_SEARCH != '' ? " AND " : " " ) . " cuinfo_notes.cu = '" . prep_save($user_name, 12) . "' ";

    // ** Apply the dont showafter date to the query
    $sql_WHERE_SEARCH  .= ($sql_WHERE_SEARCH != '' ? " AND " : " " ) . " (cuinfo_notes.dontshowafter is NULL OR cuinfo_notes.dontshowafter > current_date) ";

/*
print "TEST : ";
print $data_frmSearchFor;
print "<br><Br><br>" . $sql_WHERE_SEARCH;
*/

    switch (intval($frmViewBy)) {
        case "1":
            // ** Date Grouping
            // * Decide the where clause
      /*
       * REMOVED frmViewRange as an option
       */
      /*
            switch (intval($frmViewRange)) {
                case "1":
                    // ** Less than 12 Months OLD
                    $sql_WHERE = "WHERE (current_date::date - createddate::date ) < 360 ";
                    break;
                case "2":
                    // ** SHOW ALL MESSAGES
                    // ** NO real where clause...
                    break;
                default:
                    // ** Less than 6 Months OLD
                    $sql_WHERE = "WHERE (current_date::date - createddate::date ) < 180 ";

                    break;
            }
       * */
            // ** ADD ANY OTHER WHERE STATEMENT
            $sql_WHERE .= ($sql_WHERE_SEARCH == "" ? "" : ($sql_WHERE == '' ? " WHERE " : " AND ") . $sql_WHERE_SEARCH);
            // ** Decide the Data Order
            switch (intval($frmViewOrder)) {
                case "1":
                    $sql_ORDER = " ORDER BY cuinfo_notes.activitydate ";
                    break;
                default:
                    $sql_ORDER = " ORDER BY cuinfo_notes.activitydate DESC ";
                    break;
            }
            $sql = "SELECT messageid, parentid, author, assignee, to_char(createddate, 'MM/DD/YYYY HH24:MI:SS') as createddate,
                                unread, origination, subject, messagetext, to_char(dontshowafter, 'MM/DD/YYYY HH24:MI:SS') as dontshowafter,
                                to_char(activitydate, 'MM/DD/YYYY HH24:MI:SS') as activitydate
                            FROM cuinfo_notes
                            $sql_WHERE
                            $sql_ORDER ";
            break;
        case "2":
            // ** Topic Grouping

            // * Decide the where clause
      /*
       * REMOVED frmViewRange as an option
       */
      /*
            switch (intval($frmViewRange)) {
                case "1":
                    // ** Less than 12 Months OLD
                    $sql_WHERE = "WHERE (current_date::date - createddate::date ) < 360 ";
                    break;
                case "2":
                    // ** SHOW ALL MESSAGES
                    // ** NO real where clause...
                    break;
                default:
                    // ** Less than 6 Months OLD
                    $sql_WHERE = "WHERE (current_date::date - createddate::date ) < 180 ";

                    break;
            }
       *
       */
            $sql_WHERE .= ($sql_WHERE_SEARCH == "" ? "" : ($sql_WHERE == '' ? " WHERE " : " AND ") . $sql_WHERE_SEARCH);
            // ** Decide the Data Order -- It is at least order by {subject}
            $sql_ORDER = " ORDER BY subject ";
            switch (intval($frmViewOrder)) {
                case "1":
                    $sql_ORDER .= ", cuinfo_notes.createddate ";
                    break;
                default:
                    $sql_ORDER .= ", cuinfo_notes.createddate DESC ";
                    break;
            }
            $sql = "SELECT messageid, parentid, author, assignee, to_char(createddate, 'MM/DD/YYYY HH24:MI:SS') as createddate,
                                unread, origination, subject, messagetext, to_char(dontshowafter, 'MM/DD/YYYY HH24:MI:SS') as dontshowafter,
                                to_char(activitydate, 'MM/DD/YYYY HH24:MI:SS') as activitydate
                            FROM cuinfo_notes
                            $sql_WHERE
                            $sql_ORDER ";
            break;
        default:
            // ** Threaded Grouping
                // ** A little more difficult -- I need the parent to be followed by its children. So I will create a nodeid column
                    // that will be the parentid if there is one or the messageid if this is not a parentid
                    // ** I may have to do a lookup back onto the table to decide if the field should still be included in the results
                        // ** the child


            $frmViewBy = 0;         // * Force all other values to be consistent
            // * Decide the where clause
      /*
       * REMOVED frmViewRange as an option
       */
      /*
            switch (intval($frmViewRange)) {
                case "1":
                    // ** Less than 12 Months OLD
                    $sql_WHERE = "WHERE (current_date - COALESCE(parent_note.activitydate::date, cuinfo_notes.activitydate::date)) < 360 ";
                    break;
                case "2":
                    // ** SHOW ALL MESSAGES
                    // ** NO real where clause...
                    break;
                default:
                    // ** Less than 6 Months OLD
                    $sql_WHERE = "WHERE (current_date - COALESCE(parent_note.activitydate::date, cuinfo_notes.activitydate::date)) < 180 ";

                    break;
            }
       *
       */

            $sql_WHERE .= ($sql_WHERE_SEARCH == "" ? "" : ($sql_WHERE == '' ? " WHERE " : " AND ") . $sql_WHERE_SEARCH);
            // ** Decide the Data Order -- It is at least order by {subject}
            switch (intval($frmViewOrder)) {
                case "1":
                    $sql_ORDER .= " ORDER BY 1, cuinfo_notes.messageid, 2  ";
                    break;
                default:
                    $sql_ORDER .= " ORDER BY 1 DESC, cuinfo_notes.messageid, 2 DESC ";
                    break;
            }
            $sql = "SELECT COALESCE(cuinfo_notes.parentid, cuinfo_notes.messageid) as nodeid, COALESCE(parent_note.activitydate, cuinfo_notes.activitydate) as lastnodeactivity,
                                cuinfo_notes.messageid, cuinfo_notes.parentid, cuinfo_notes.author, cuinfo_notes.assignee,
                                TO_CHAR(cuinfo_notes.createddate, 'MM/DD/YYYY HH24:MI:SS') as createddate,
                                cuinfo_notes.unread, cuinfo_notes.origination, cuinfo_notes.subject, cuinfo_notes.messagetext, TO_CHAR(cuinfo_notes.dontshowafter, 'MM/DD/YYYY HH24:MI:SS') as dontshowafter,
                                TO_CHAR(cuinfo_notes.activitydate, 'MM/DD/YYYY HH24:MI:SS') as activitydate

                            FROM cuinfo_notes
                            LEFT JOIN cuinfo_notes as parent_note on parent_note.messageid = cuinfo_notes.messageid
                            $sql_WHERE
                            $sql_ORDER ";
    }

    if ($sql != '') {
        // ** Query for the data
        $notes_rs = db_query($sql, $link);
    // ** CL1
        // ** SEt the array to empty array, if it returns nothing
//    if (!$data_notes_list = db_fetch_all($notes_rs)) {
//          $data_notes_list = Array();
//      }
//
    $data_notes_idx = 0;
    while ($notesRow = db_fetch_assoc($notes_rs, $data_notes_idx)) {
      // * mws 4/26/13
      // ** JSON Has some limitations escaping certain characters ie (extended ASCII Character >= 128)
      // ** Need to remove these by using a preg_replace, that should put a space in place of any of these chracters
      $notesRow['author'] = dms_disphtml($notesRow['author']);
      $notesRow['subject'] = dms_disphtml($notesRow['subject']);
      $notesRow['messagetext'] = nl2br(dms_disphtml(preg_replace('/[^(\x00-\x7F)]*/','', $notesRow['messagetext'])));
      $data_notes_list[] = $notesRow;
      $data_notes_idx++;
    }
    }
    // ** Set the data for employees
    $sql = "SELECT user_name
                    FROM dmsmonitorusers
                    ORDER BY upper(user_name) ";
    $users_rs = db_query($sql, $link);
  $data_users_list = db_fetch_all($users_rs);

  // print the information as JSON
  header('Content-type: application/json');

  // ** Different time require different data results returned
  if ($returnDefaults) {
    $retStatus_ary['data'] = Array(
                    "users" => $data_users_list,
                    "notes" => $data_notes_list,
                    "topics" => $dataNoteTopics_ary,
                    "searchfields" => $dataSearchFields_ary
    );
  } else {
    $retStatus_ary['data'] = Array(
                    "notes" => $data_notes_list
    );
  }
  if (count($form_return_msgs) > 0) {
    $retStatus_ary['status']['messages'] = $form_return_msgs;
  }
  print json_encode($retStatus_ary);

  }
  catch(Exception $ex)
  {
    // ** Make sure the results being returned are empty
    $retStatus_ary = array();
    header('Content-type: application/json');
    $retStatus_ary = Array(
                          'status' => Array('code'=>'999', 'severity'=>'FAILURE', 'errors' => Array(), 'action' => ''),
                          'data' => ''
                          );
    $retStatus_ary['status']['errors'][] = $ex->getMessage();
    print json_encode($retStatus_ary);

  }

?>