remote_monitor_save.prg

<?php
    /*
        *** NOTE:
            Possibly in the future add information to make sure the next sequence id is also updated.
            Currently after running on the remote servers the sequence will be out of sync.  But based
            assumptions, the remote servers will not be executing the individual information...Keeping the
            id's in sync
    */
    /* **** REMOTE SAVE ---- for MONITOR
            ** This will be a remote script on each of the servers
            ** As a code is saved through monitor it may call this script on the remote servers


            *** SETUP -- the information will be listed in the posted content ($_POST)

            *** VALIDATION -- There is not much validation that can be done
                    -- BUT FOR INTERNAL PURPOSES ONLY SO VERIFY THE REMOTE IP IS INTERNAL
            ** URL VALUES
                sc - This will be the script information to modify
                ac - Action to be taken (U)pdate / (D)elete
                tbl - This is the table name -- It must be one of the predefined allowed tables
                FIELD INFORMATION -- This will be in the form of an array
                col[] -- This is an array that contains the information about the column
                    * information array
                        ** name         - Name of the field in the table
                        ** value        - Value to be saved to the database
                        ** dt           - Datatype - (N)umeric, (C)haracter
                        ** len          - Maximum length of the to be saved
                        ** key          - Is this value part of the table key


            ** Possible Return Values
                ** NON-ERRORS
                800 - No Action Taken -- No Action occurred with this query
                801 - SUCCESS       -- The action was successful

                ** ERRORS
                900 - HomeCU Remote IP Error -- the computer that called this script was not an internal machine causing an error with the script
                901 - HomeCU Database Connection Failed -- The database failed to open
                902 - Field Values Not Set -- The col value on the url does not appear to be correct
                903 - Key Not Set -- The key was NOT setup correctly -- it must be set to continue
                904 - Table Update Not Allowed -- The table specified is not allowed update through this script
                905 - FAILED -- Action Failed
    */

    // ** VALIDATE REFERER IP
    if (strpos($_SERVER['REMOTE_ADDR'], "192.168.168") === false && strpos($_SERVER['REMOTE_ADDR'], "192.168.169") === false && strpos($_SERVER['REMOTE_ADDR'], "199.184.207.194") === false && strpos($_SERVER['REMOTE_ADDR'], "199.184.207.3") === false && strpos($_SERVER['REMOTE_ADDR'], "199.184.207.5") === false && strpos($_SERVER['REMOTE_ADDR'], "199.184.207.66") === false && strpos($_SERVER['REMOTE_ADDR'], "184.73.202.7") === false && strpos($_SERVER['REMOTE_ADDR'], "107.20.248.233") === false) {
        print "RU: 900 - HomeCU Remote IP Error";
        exit;
    }

$monLibrary= dirname(__FILE__) . "/../library";
$monIncludes= dirname(__FILE__) . "/../includes";
require_once("$monLibrary/cu_top.i");
require_once("$monIncludes/cu_remote_top.prg");

  // ** Declare the variables for connecting to the remote instances of the db's
    $str_pos = strpos($_SERVER['SERVER_NAME'], ".");
    $home_host = substr($_SERVER['SERVER_NAME'], 0, $str_pos);
    $dbhost = ($home_host == 'monitor' ?
                                'localhost' :
                                "db-" . $_SERVER['SERVER_NAME']);

  $dbhost = (($home_host == 'monitor') ?
                'localhost' :
             ($home_host == 'ivr' ?
                'db-ivr.cpzo4mvee2q5.us-east-1.rds.amazonaws.com sslmode=verify-full sslrootcert=/etc/ssl/certs/rds-combined-ca-bundle.pem' :
                "db-" . $_SERVER['SERVER_NAME']));
    // ** Check Database Link
  if (!$link) {
        print "RU: 901 - HomeCU Database Connection Failed";
        exit;
    }

    // ** Predefined Allowed Tables for Updating
    // *** OLD REFERENCE $Allowed_Tables = array("cuvendors");

    // ** SPECIAL PROCESSING DOES NOT REQUIRE SOME VALIDATION
    if ($_POST['ac'] == "S") {
        $tbl_update = $_POST['tbl'];
        $tbl_key = "";
    } else {

        // ** VALIDATE THE TABLE TO UPDATE IS ACCEPTABLE
        $tbl_update = "";           // NAME OF THE ACTUAL TABLE -- To help so I don't reference the array value
        $tbl_key = "";              // THIS IS THE TABLE KEY IN THE ALLOWED_TABLES array
        if (!isset($Allowed_Tables[$_POST['tbl']])) {
            print "RU: 904 - Table Update Not Allowed";
            exit;
        } else {
            $tbl_update = $Allowed_Tables[$_POST['tbl']]['tablename'];
            $tbl_key = $_POST['tbl'];
        }

        if ($tbl_update == "") {
            print "RU: 904 - Table Update Not Allowed";
            exit;
        }

        // ** CREATE THE KEY FROM THE GET VALUES
        $tbl_pk = "";                   // *** PRIMARY KEY FOR THE TABLE
        if (isset($_POST['col']) && is_array($_POST['col'])) {
            $tbl_cols = $_POST['col'];
            // Loop through the columns and create the key value
            foreach ($tbl_cols as $key => $value) {
                if ($Allowed_Tables[$tbl_key][$value['name']]['key'] == "1") {
                    $tbl_pk .= ($tbl_pk != "" ? " AND " : "") . $Allowed_Tables[$tbl_key][$value['name']]['name'] . " = '" . $value['value'] . "' ";
                }
            }
            reset($tbl_cols);
        } else {
            print "RU: 902 - Field Values Not Set";
            exit;
        }

        // ** VERIFY THERE IS A KEY
        if (strlen($tbl_pk) == 0) {
            print "RU: 903 - Key Not Set";
            exit;
        }
    }

    // *** VALIDATION COMPLETE PERFORM TABLE UPDATE/DELETE
    //  ** 1ST - Determine if the record already exists
    $cnt_sql = "SELECT count(*) as record_count
                            FROM $tbl_update
                            WHERE $tbl_pk ";

    $cnt_rs = db_query($cnt_sql, $link);
    list($cnt_row) = db_fetch_array($cnt_rs);
    db_free_result($cnt_rs);

    $act_sql = "";
    switch ($_POST['ac']) {
        case "U":
            // *** UPDATE / ADD
            if ($cnt_row == 0) {
                // ** INSERT INTO TABLE
                $act_sql = "INSERT INTO $tbl_update (";

                // ** CREATE A FIELD LIST FOR INSERTING
                $fld_cnt = 0;
                foreach ($tbl_cols as $key => $value) {
                    $fld_cnt++;
                    $act_sql .= ($fld_cnt > 1 ? ", " : "");

                    $act_sql .= $Allowed_Tables[$tbl_key][$value['name']]['name'];
                }
                reset($tbl_cols);

                $act_sql .= ") VALUES (";

                // ** CREATE VALUE LIST
                $fld_cnt = 0;
                foreach ($tbl_cols as $key => $value) {
                    $fld_cnt++;
                    $act_sql .= ($fld_cnt > 1 ? ", " : "");

                    // ** ADD THE VALUE for the field
                    switch ($Allowed_Tables[$tbl_key][$value['name']]['dt']) {
                        case "N":
                            // ** Numeric
                            $act_sql .= "'" . intval($value['value']) . "'";
                            break;
                        case "C":
                            // ** Character
                            $act_sql .= "'" . prep_save($value['value'], $Allowed_Tables[$tbl_key][$value['name']]['len']) . "'";
                            break;
                        case "D":
                            // ** DATE FIELDS
                            // If the date is blank, then save NULL instead, date fields don't accept empty field
                            // Make sure it is a valid date
                            if (isdate($value['value']) && strlen($value['value']) > 0) {
                                $save_date = "'" . prep_save($value['value'], $Allowed_Tables[$tbl_key][$value['name']]['len']) . "'";
                            } else {
                                $save_date = "NULL";
                            }
                            $act_sql .= $save_date;
                    }
                }
                reset($tbl_cols);
                $act_sql .= ")";
            } elseif ($cnt_row == 1) {
                // ** UPDATE TABLE
                $act_sql = "UPDATE $tbl_update SET ";

                $fld_cnt = 0;
                foreach ($tbl_cols as $key => $value) {
                    $fld_cnt++;
                    $act_sql .= ($fld_cnt > 1 ? ", " : "");

                    $act_sql .= $Allowed_Tables[$tbl_key][$value['name']]['name'] . " = ";
                    // ** ADD THE VALUE for the field
                    switch ($Allowed_Tables[$tbl_key][$value['name']]['dt']) {
                        case "N":
                            // ** Numeric
                            $act_sql .= "'" . intval($value['value']) . "'";
                            break;
                        case "C":
                            // ** Character
                            $act_sql .= "'" . prep_save($value['value'], $Allowed_Tables[$tbl_key][$value['name']]['len']) . "'";
                            break;
                        case "D":
                            // ** DATE
                            if (isdate($value['value']) && strlen($value['value']) > 0) {
                                $save_date = "'" . prep_save($value['value'], 10) . "'";
                            } else {
                                $save_date = "NULL";
                            }
                            $act_sql .= $save_date;
                            break;
                    }
                }
                reset($tbl_cols);
                $act_sql .= " WHERE $tbl_pk ";

            } else {
                print "RU: 800 - No Action Taken";
            }

            if (strlen($act_sql) > 0) {
                if (!$act_rs = db_query($act_sql, $link)) {
                    print "RU: 905 - Update has failed";
                } else {
                    print "RU: 801 - Update was successful";
                }
            }
            break;
        case "D":
            // *** DELETE
            if ($cnt_row == 1) {
                // DELETE THE RECORD FROM THE TABLE
                $act_sql = "DELETE FROM $tbl_update
                                        WHERE $tbl_pk ";
                if ($act_rs = db_query($act_sql, $link)) {
                    print "RU: 801 - Delete was successful";
                } else {
                    print "RU: 905 - Delete has failed";
                }
            } else {
                print "RU: 800 - No Action Taken";
            }
            // *** DO NOTHING IF COUNT IS NOT 1 -- because if 0, no action needed, if 2, something may be wrong with the key
            break;
        case "S":
            // *** SPECIAL PROCESSING
            switch ($tbl_update) {
                case "excludelist":
                    $tbl_cols = $_POST['col'];
                    $act_sql = "INSERT INTO cuadminexclude (cu, user_name, program)
                      (SELECT cu, trim(user_name), '" . prep_save($tbl_cols[0]['value'], $Allowed_Tables[$tbl_update][$tbl_cols[0]['name']][len])  . "'
                      FROM cuadmin) ";
                    break;
            }
            break;
        default:
            // ** NO ACTION TAKEN
            print "RU: 800 - No Action Taken";
    }

?>