hcucontact_rpt.prg

<?php

$monLibrary = dirname(__FILE__) . "/../library";
$sharedLibrary = dirname(__FILE__) . "/../../shared/library";
require_once("$monLibrary/cu_top.i");
require_once("$monLibrary/ck_hticket.i");
require_once("$sharedLibrary/cu_flagconst.i");

$dms_ok=array('report'=>'digits','ob'=>'digits','csv'=>'string',
    'pos'=>'digits','d'=>'digits','jval'=>'string','pm'=>'digits');
dms_import($dms_ok);

$report = (isset($report) ? $report : 2);

switch ($report) {

    case 1:
        $sql_query = "select trim(upper(user_name)) as cu,
            trim(www_server) as www_server, system_options
            from cuinfo
            where system_options & 3::bigint > 0
            and system_options & 64::bigint = 0
            and date_live is not null
            and user_name not in ('miki', 'demo', 'dmstest') ";
        if (!isset($ob) || (intval($ob) < 1 || intval($ob) > 3)) {
        $sql_order = " ORDER BY 1";
        $ob = 1;    // Set the order by to 1 if it isn't set
        } else {
            $sql_order = " ORDER BY " . intval($ob);
        }

        $title = "HCU Contacts Cuinfo List";
        break;

    case 2:
        $sql_query = "select trim(m.cu) as cu,
            case when n.notify is null then trim(m.master)
            else trim(n.notify) end as email
            from (select a.cu, u.email as master from cuadmin a
            join cuadminusers u on a.cu = u.cu where u.realname = 'CU Master') as m
            left join (select cu, email as notify from cuadmnotify
            where role = 'hcunotice') as n on m.cu = n.cu ";
        if (!isset($ob) || (intval($ob) < 1 || intval($ob) > 2)) {
            $sql_order = " ORDER BY 1";
            $ob = 1;    // Set the order by to 1 if it isn't set
        } else {
            $sql_order = " ORDER BY " . intval($ob);
        }
        $title = "HCU Contact Email List";
        break;

    case 3:
        $sql_query = "select m.cu as \"CU\", n.notify as \"HCU Notice\",
            m.master as \"CU Master\"
            from (select a.cu, u.email as master from cuadmin a
            join cuadminusers u on a.cu=u.cu where u.realname = 'CU Master')
            as m left join (select cu, email as notify from cuadmnotify
            where role = 'hcunotice') as n on m.cu = n.cu ";
        if (!isset($ob) || (intval($ob) < 1 || intval($ob) > 3)) {
            $sql_order = " ORDER BY 1";
            $ob = 1;    // Set the order by to 1 if it isn't set
        } else {
            $sql_order = " ORDER BY " . intval($ob);
        }
        $title = "HCU Notice and Master Email List";
        break;

    case 4:
        $sql_query = "select trim(cu) as cu,
            trim(email) as email
            from cuadmnotify where role = 'hcunotice' ";
        if (!isset($ob) || (intval($ob) < 1 || intval($ob) > 2)) {
            $sql_order = " ORDER BY 1";
            $ob = 1;    // Set the order by to 1 if it isn't set
        } else {
            $sql_order = " ORDER BY " . intval($ob);
        }
        $title = "HCU Notice Email List";
        break;

    case 5:
        $sql_query = "select trim(cu) as cu,
            trim(email) as email
            from cuadmnotify where role = 'esload' ";
        if (!isset($ob) || (intval($ob) < 1 || intval($ob) > 2)) {
            $sql_order = " ORDER BY 1";
            $ob = 1;    // Set the order by to 1 if it isn't set
        } else {
            $sql_order = " ORDER BY " . intval($ob);
        }
        $title = "HCU ESLoad Email List";
        break;
}

$sql_query = stripslashes($sql_query);
$sql_order = stripslashes($sql_order);
$title = stripslashes($title);

$d = isset($d) ? $d : null;
if ($d == 1) {
    $sql_order .= " desc";
}

$sql_order = isset($sql_order) ? trim($sql_order) : "";
if (!$result = db_query($sql_query . $sql_order, $dbh)) {
   pg_die(pg_errormessage($dbh), $sql_query . $sqlorder, __FILE__, __LINE__);
}
$num_rows = db_num_rows($result);
$num_fields = @pg_numfields($result);

if (isset($csv) && $csv == 'raw') {
    unset($csv);
    $ostring = "";
    $osep = "\t";

    if ($num_rows > 0) {

        #
        # First record has the field names
        #
        $ostring = $title;
        for ($i_field = 0; $i_field < $num_fields; $i_field++) {
            $field_name = pg_fieldname($result, $i_field);
            $ostring .= "${osep}${field_name}";
        }
        $ostring .= "\r\n";

        #
        # Second record has the field types
        #
        $osep = "";
        for ($i_field = 0; $i_field < $num_fields; $i_field++) {
            $field_type = pg_fieldtype($result, $i_field);
            $ostring .= "${osep}${field_type}";
            $osep = "\t";
        }
        $ostring .= "\r\n";

        #
        # Now the data
        #
        for ($i_row = 0; $i_row < $num_rows; $i_row++) {
            $osep = "";
            $row = pg_fetch_row($result, $i_row);
            for ($i_field = 0; $i_field < $num_fields; $i_field++) {
                $ostring .= "${osep}${row[$i_field]}";
                $osep = "\t";
            }
            $ostring .= "\r\n";
        }
    }

    $Cu = $Cu ?? "";

    header("Content-length: " . strlen( $ostring ) );
    header("Content-type: application/octetstream");
    header("Content-disposition: inline; filename=\"${Cu}report${report}.csv\"");
    print ( $ostring );
    exit;
}


$query = display_table($result);

function display_table ($dt_result) {
    global $pos, $dbh, $sql_query, $sql_order, $report, $self, $title, $ob, $d, $jval, $pm;

    $cfgQuotes = "\"";
    $cfgBorder = 0;
    $cfgMaxRows = 30;
    $cfgMaxPages = 5;
    $cfgMaxText = 255;
    $cfgOrder = "DESC";
    $strShowingRecords = "Showing records ";
    $strTotal = "total";
    $strPrevious = "Previous";
    $strNext = "Next";
    $strPos1 = "Begin";
    $strEnd = "End";
    $strMore = "More";
    $strEmptySet = "No records found";

    #
    # if jval is set, search through the ob field until we find a match
    # set $pos so record is on the page
    #

    if (!isset($pos)) {
        $pos = 0;
    }
    if (!isset($d)) {
        $d = 0;
    }

    $num_rows = @pg_numrows($dt_result);

    $num_fields = @pg_numfields($dt_result);

    if (isset($jval) && trim($jval) > '') {
        $nomatch = 1;
        $jval = trim(stripslashes(preg_replace("/[\"\`;\\\]/","",$jval)));
        if ($pm) $clen = strlen($jval);
        for ( $rowptr = 0; $rowptr < $num_rows; $rowptr++ ) {
            $colval = pg_fetch_result($dt_result, $rowptr, $ob - 1);
            $coloff = (strpos(strtolower($colval), strtolower($jval)) === false ? 0 : strpos(strtolower($colval), strtolower($jval)));
            if ($pm == 2) {
                $colval = substr($colval, $coloff, $clen);
            }
            if ($pm == 1) {
                $colval = substr($colval, 0, $clen);
            }
            if (strtolower(trim($colval)) == strtolower($jval)) {
                $pos = $cfgMaxRows * floor($rowptr / $cfgMaxRows);
                $nomatch = 0;
                break;
            }
        }

    }

    $pos_next = $pos + $cfgMaxRows;
    $pos_prev = $pos - $cfgMaxRows;


    $iNumRows = $num_rows;
    if ($num_rows < $pos_next) {
        $pos_next = $num_rows;
    }

    if (!isset($strNavigation)) {
        $strNavigation = "";
    }
    $strNavigation .= "<table border=0 cellpadding=2 cellspacing=0><tr>\n";
    $strNavigation .=  "<td align=left>";
    if ($pos >= $cfgMaxRows) {
        //doj added link to beginning of results
        $strNavigation .= "<a href=\"$self?report=$report&ob=$ob&d=$d&pos=0\">&lt;&lt; $strPos1 </a> | ";

        $strNavigation .= " <a href=\"$self?report=$report&ob=$ob&d=$d&pos=$pos_prev\">&lt; $strPrevious </a> | ";
    } else {
        $strNavigation .= "&nbsp;";
    }
    $strNavigation .= "</td>\n";

    $strNavigation .= "<td align=center>\n";

    //doj now only the previous and next $cfgMaxPages pages will be shown
    if (empty($cfgMaxPages)) {
        $cfgMaxPages = 5;
    }

    $iCount = $pos - ($cfgMaxRows * $cfgMaxPages);
    if ($iCount < 0)
        $iCount = 0;

    $iPageCnt = (int)($iCount / $cfgMaxRows);
    $iPageStop = $pos + ($cfgMaxRows * ($cfgMaxPages + 1));

    if (!isset($strPages)) {
        $strPages = "";
    }
    for (; $iCount < $iNumRows && $iCount < $iPageStop; $iCount += $cfgMaxRows) {
      //doj until here
        $iPageCnt++;
        if ($iCount != $pos) {
            $strPages .= "<a href=\"$self?report=$report&ob=$ob&d=$d&pos=$iCount\">$iPageCnt</a> | ";
        } else {
            if ($iNumRows > $cfgMaxRows) {
                $strPages .= "$iPageCnt | ";
            }
        }
    }
    $strNavigation .= preg_replace('/ \| $/i', "", $strPages);

    $strNavigation .= "</td>\n";

    $strNavigation .= "<td align=right>";
    if ($pos_next < $num_rows) {
        $strNavigation .= " | <a href=\"$self?report=$report&ob=$ob&d=$d&pos=$pos_next\"> $strNext &gt;</a>";

        //doj link to end of results
        $pos_end = $num_rows - $cfgMaxRows;
        $strNavigation .= " | <a href=\"$self?report=$report&ob=$ob&d=$d&pos=$pos_end\"> $strEnd &gt;&gt;</a>";
    } else {
        $strNavigation .= "&nbsp;";
    }
    $strNavigation .= "</td>\n";
    $strNavigation .= "</tr></table>\n";

    header("Expires: Sat 20 May 1995 03:32:38 GMT");
    header("Pragma: no-cache");
    header("Cache-Control: no-cache, must-revalidate");

    if (!isset($q)) {
        $q = "";
    }
    print <<<EOF
         <script language='javascript'>
         function jumpto()
              {
                 var strJval=document.jumpform.jval.value;
                                 var strPm=0;
                                    for (i=document.jumpform.pm.length-1; i > -1; i--) {
                                        if (document.jumpform.pm[i].checked) {
                                            strPm = document.jumpform.pm[i].value; i = -1;
                                        }
                                    }
                                 document.location='$self?report=$report&ob=$ob&d=$d&pos=0&jval=' + strJval + '&pm=' + strPm + '$q';
              }
         </script>
EOF;

    cu_header("$title");
    echo "<table border=$cfgBorder align=center width='100%' bgcolor=white cellpadding=2 cellspacing=0>";
    echo "<tr><td class='dtl' align='left'>";
    echo "<a href='cuilist.prg'>Credit Union List</a>&nbsp;&nbsp;&nbsp;
              <A HREF='$self?csv=csv&report=$report&ob=$ob&d=$d'>Download CSV</a></td><td class='dtl' align='right'>&nbsp;";
    if ($num_rows > 1) {
        echo "$strShowingRecords $pos - $pos_next ($num_rows $strTotal)";
    }
    echo "</td></tr></table>";

    echo $strNavigation;
    if ($iNumRows > $cfgMaxRows) {
        # show a 'Jump to this value' link for the sorted field only
        print "<form name='jumpform' onSubmit='jumpto();return false;'>
                    <table border=$cfgBorder align=center width='90%'>
             <tr><td class='msg'><hr>
          Click column heading to change sort order and jump value<br>&nbsp;</td></tr>
                    <tr><td>Jump to " .
                    pg_fieldname($dt_result, $ob-1) .  ": &nbsp;
          <input type='text' name='jval' value='$jval' size='10'>
          &nbsp;&nbsp;
          <input type='radio' name='pm' value='1'>&nbsp;Partial Match
          &nbsp;&nbsp;
          <input type='radio' name='pm' value='2'>&nbsp;Wildcard&nbsp;&nbsp;
          <input type='button' value='Go' onClick='jumpto();return false;'>";
            if ($nomatch == 1 ) print "<br><font color='red' align='center'>No Match Found</font>";
        print "</td></tr></table></form>";
    }
    echo "<table border=$cfgBorder align=center width='100%' bgcolor=white cellpadding=2 cellspacing=0>";
    echo "<tr><td class='hdr' colspan='$num_fields' align='center'>$title</td></tr><tr>";
    echo "<tr>";

    for ($i_field = 0; $i_field < $num_fields; $i_field++) {
        $field_name = pg_fieldname($dt_result, $i_field);
        if (@pg_numrows($dt_result) > 1) {

            if (($i_field + 1) == intval($ob) && $d != 1) {
              $desc_ord = "&d=1";       // Descending order??
            } else {
              $desc_ord = "&d=0";
            }

            $sort_order = ($i_field + 1);
            echo "<th class='small' align='left'><A HREF=\"$self?report=$report&pos=$pos&ob={$sort_order}{$desc_ord}\">$field_name</a></th>\n";
        } else {
            echo "<th class='small' align='left'>$field_name</th>";
        }
    }
    echo "</tr>\n";
    if ((@pg_numrows($dt_result)) < 1) {
       echo "<tr class='odd'><td colspan='$num_fields'>$strEmptySet</td>";
    } else {

        if (!isset($cfgShowBlob)) {
            $cfgShowBlob = "";
        }

        for ($i_row = $pos; $i_row < $pos_next; $i_row++) {
            $row = pg_fetch_row($dt_result, $i_row);
            $i_row % 2  ? $rbg = "odd_small" : $rbg = "even_small";
            echo "<tr class=$rbg>";
            for ($i_field = 0; $i_field < pg_numfields($dt_result); $i_field++) {
                if (!isset($row[$i_field])) {
                    unset($row[$i_field]);
                }
                $field_type = pg_fieldtype($dt_result, $i_field);
                $field_name = pg_fieldname($dt_result, $i_field);
                if (preg_match('/int|numeric/i', $field_type)) {

                    echo "<td align=right valign=top>&nbsp;$row[$i_field]&nbsp;</td>\n";
                } elseif ($cfgShowBlob == false && preg_match('/blob/i', $field_type)) {
                    echo "<td align=right valign=top>&nbsp;[BLOB]&nbsp;</td>\n";
                } elseif (preg_match('/text|char/i', $field_type)) {
                    // If the text is longer than $cfgMaxText characters, let's cut it short
                    if (strlen($row[$i_field]) > $cfgMaxText && !empty($cfgMaxText)) {
                        $strLgText =  nl2br(htmlspecialchars(substr($row[$i_field], 0, $cfgMaxText))) . " <br><b>... $strMore ...</b>";
                    } else {
                        $strLgText = nl2br(htmlspecialchars($row[$i_field]));
                    }
                    echo "<td valign=top>&nbsp;$strLgText</td>\n";
                } elseif ($field_type == "bool") {
                    echo "<td valign=top>&nbsp;", bool_YesNo($row[$i_field]), "&nbsp;</td>\n";
                } else {
                    echo "<td valign=top>&nbsp;", nl2br(htmlspecialchars($row[$i_field])), "&nbsp;</td>\n";
                }
            }

        }
    }

    if (!isset($query)) {
        $query = "";
    }

    echo "</tr></table>\n";
    echo $strNavigation;
    return $query;
}

// Function:    pg_die($error, $query)
// Params:      $error -- The displayable error text.  Usually passed as pg_errormessage()
//              $query -- The query which was attempted
function pg_die($error = "", $query = "", $err_file = __FILE__, $err_line = __LINE__) {
    global $strError,$strMySQLSaid, $strBack, $sql_query, $link, $db, $table, $cfgDebug, $server;

    echo "<b><font face=\"arial,helvetican,sans-serif\" color=\"red\">$strError - $err_file -- Line: $err_line</font></b><p>";
    if (empty($error)) {
        $error = @pg_errormessage();
    }
    echo "<font face=\"arial,helvetican,sans-serif\" color=\"red\">", $strMySQLSaid . $error;
    if (empty($query)) {
        $query = $sql_query;
    }
    if (!empty($query)) {
        echo "<br>Your query: <br><b>",  nl2br(htmlentities($query)), "</b>";
    }
    echo "<br><a href=\"javascript:history.back()\">$strBack</a></font>";

    if ($cfgDebug) {
        echo "<br>Link: $link<br>DB: $db<br>Table: $table<br>Server: $server";
    }

    echo "<p>";
    exit;
}
?>