function makeDefault()
 {
     $col = SQLFN(gp('column'));
     $skey = SQLFN(gp('skey'));
     SQL("update configuser set {$col} = null WHERE skey = {$skey}");
     configWrite('user');
 }
Exemple #2
0
 /**
  * Execute an skey-based delete
  *
  */
 function delete()
 {
     $skey = SQLFN(gp('skey'));
     $sq = "Delete from " . $this->view_id . " where skey = {$skey}";
     SQL($sq);
 }
Exemple #3
0
function sqlFilter($colinfo, $tcv, $table = '')
{
    $type_id = $colinfo['type_id'];
    $column_id = $colinfo['column_id'];
    $c = $column_id;
    if ($table != '') {
        $c = "{$table}.{$column_id}";
        $table = "{$table}.";
    }
    # If the value is an asterisk, return an unconditional true
    if ($tcv == '*') {
        return '1=1';
    }
    # Determine if we will use dashes
    $ignore_dashes = a($colinfo, 'uisearch_ignore_dash', 'N');
    # Get the dash operator
    $dash = trim(configGet('uisearchdash', '-'));
    x4Debug("dash is " . $dash);
    # Step one is to split on commas and handle each
    # value separately, then at bottom we recombine
    $values = explode(',', $tcv);
    $sql_new = array();
    foreach ($values as $tcv) {
        $new = '';
        # This switch statement reproduces a lot of code for different
        # types, with small changes.  I decided to do it this way instead
        # of generalizing it because it will be easier to add type-specific
        # details going forward.
        switch ($type_id) {
            case 'char':
            case 'vchar':
            case 'text':
            case 'ph12':
            case 'ssn':
            case 'cbool':
                if (substr($tcv, 0, 1) == '>' || substr($tcv, 0, 1) == '<') {
                    $tcv = str_replace('%', '', $tcv);
                    if (strlen($tcv) > 1) {
                        $new = $c . substr($tcv, 0, 1) . SQLFC(substr($tcv, 1));
                    }
                } elseif (strpos($tcv, $dash) !== false && $ignore_dashes != 'Y') {
                    list($beg, $end) = explode($dash, $tcv);
                    if (strlen($beg) > 0 && strlen($end) > 0) {
                        $sbeg = SQLFC(trim(str_replace('%', '', $beg)));
                        $send = SQLFC(trim(str_replace('%', '', $end)) . 'z');
                        # Don't use between.  This allows a-a to still work
                        $new = "{$c} >= {$sbeg} AND {$c} <= {$send}";
                    }
                } elseif (strpos($tcv, '%') !== false) {
                    # user has requested wildcard, cannot avoid a like
                    $sval = SQLFC(trim($tcv));
                    $new = "LOWER({$c}) LIKE LOWER({$sval})";
                } else {
                    if (strlen(trim($tcv)) > 0) {
                        if (gp('x6exactPre', false)) {
                            $new = "LOWER({$c}) = LOWER(" . SQLFC(trim($tcv)) . ")";
                        } else {
                            $sbeg = SQLFC(trim($tcv));
                            $send = SQLFC(trim($tcv) . 'z');
                            # The greater-equal allows us to avoid a like
                            # and make use of indexes for much faster performance
                            $new = "(LOWER({$c}) >= LOWER({$sbeg})" . " AND LOWER({$c}) < LOWER({$send}))";
                        }
                    }
                }
                break;
            case 'dtime':
            case 'date':
                x4Debug($tcv);
                x4Debug(strtotime($tcv));
                if (substr($tcv, 0, 1) == '>' || substr($tcv, 0, 1) == '<') {
                    $operator = substr($tcv, 0, 1);
                    $tcv = substr($tcv, 1);
                    if (strtotime($tcv)) {
                        $tcv = str_replace('%', '', $tcv);
                        $tcv = SQLFD($tcv);
                        $new = "{$c} {$operator} {$tcv}";
                    }
                } elseif (strpos($tcv, $dash) !== false && $ignore_dashes != 'Y') {
                    list($beg, $end) = explode($dash, $tcv);
                    if (strtotime($beg) && strtotime($end)) {
                        $sbeg = SQLFD(trim(str_replace('%', '', $beg)));
                        $send = SQLFD(trim(str_replace('%', '', $end)));
                        $new = "{$c} between {$sbeg} and {$send}";
                    }
                } else {
                    $pieces = explode('/', $tcv);
                    if (count($pieces) == 1) {
                        if (strlen($pieces[0]) == 4) {
                            $new = "EXTRACT(YEAR FROM {$c}::timestamp)=" . SQLFN($pieces[0]);
                        }
                    } else {
                        if (count($pieces) == 2) {
                            if (strlen($pieces[1]) == 4) {
                                $new = "EXTRACT(MONTH FROM {$c}::timestamp)=" . SQLFN($pieces[0]) . " AND EXTRACT(YEAR FROM {$c}::timestamp)=" . SQLFN($pieces[1]);
                            } else {
                                if (strlen($pieces[1]) < 3 && strlen($pieces[1]) > 0) {
                                    $new = "EXTRACT(MONTH FROM {$c}::timestamp)=" . SQLFN($pieces[0]) . " AND EXTRACT(DAY FROM {$c}::timestamp)=" . SQLFN($pieces[1]);
                                }
                            }
                        } else {
                            if (strtotime($tcv)) {
                                $tcv = str_replace('%', '', $tcv);
                                $tcv = SQLFD($tcv);
                                $new = "{$c} = {$tcv}";
                            }
                        }
                    }
                }
                break;
            case 'time':
            case 'int':
            case 'numb':
                if (substr($tcv, 0, 1) == '>' || substr($tcv, 0, 1) == '<') {
                    $tcv = str_replace('%', '', $tcv);
                    if (strlen($tcv) > 1) {
                        $new = $c . substr($tcv, 0, 1) . SQLFN(floatval(substr($tcv, 1)));
                    }
                } elseif (strpos($tcv, $dash) !== false && $ignore_dashes != 'Y') {
                    list($beg, $end) = explode($dash, $tcv);
                    if (strlen($beg) > 0 && strlen($end) > 0) {
                        $sbeg = SQLFN(floatval(str_replace('%', '', $beg)));
                        $send = SQLFN(floatval(str_replace('%', '', $end)));
                        # Don't use between.  This allows a-a to still work
                        $new = "{$c} >= {$sbeg} AND {$c} <= {$send}";
                    }
                } elseif (strpos($tcv, '%') !== false) {
                    # user has requested wildcard, cannot avoid a like
                    $sval = SQLFC(floatval($tcv));
                    $new = "{$table}{$column_id}::varchar LIKE {$sval}";
                } else {
                    if (strlen(trim($tcv)) > 0) {
                        $sval = SQLFN(floatval($tcv));
                        $new = "{$c} = {$sval}";
                    }
                }
                break;
        }
        # now add the new value into the list of clauses
        if (strlen($new) > 0) {
            $sql_new[] = $new;
        }
    }
    # If there are no search criteria, do nothing.  The
    # calling program must interpret this and avoid a search.
    if (count($sql_new) > 0) {
        return implode("\n        OR ", $sql_new);
    } else {
        return '';
    }
}
Exemple #4
0
 function hBoxes($mode)
 {
     // Obtain a row depending on the mode we are in.  If there
     // wdas an error then reload the first row for this table
     if (is_array(vgfGet('ErrorRow_' . $this->table_id, ''))) {
         $row = vgfGet('ErrorRow_' . $this->table_id);
         $row['skey'] = gp('gpx_skey');
     } else {
         switch ($mode) {
             case 'search':
                 // if a previous search, use that, else fall through
                 // to using current row
                 $row = ConGet('table', $this->table_id, 'search', array());
                 if (count($row) != 0) {
                     break;
                 }
             case 'ins':
                 $row = DrillDownMatches();
                 if (count($row) == 0) {
                     $row = aFromGP('pre_');
                 }
                 // KFD 8/13/07, part of COPY ability
                 if (gp('gp_action') == 'copy') {
                     $row2 = SQL_OneRow("SELECT * FROM " . $this->table_id . " where skey=" . SQLFN(gp('gp_skey')));
                     foreach ($row2 as $column_id => $colvalue) {
                         if (is_numeric($column_id)) {
                             continue;
                         }
                         if (!isset($this->table['flat'][$column_id])) {
                             continue;
                         }
                         $aid = $this->table['flat'][$column_id]['automation_id'];
                         if ($aid == 'SEQUENCE' || $column_id == gp('gp_exclude')) {
                             unset($row2[$column_id]);
                         }
                     }
                     $row = array_merge($row, $row2);
                 }
                 break;
             case 'upd':
                 $skey = gp('gp_skey');
                 hidden('gp_skey', '');
                 if (trim($skey) == '') {
                     $row = array();
                 } else {
                     $skey = " WHERE skey=" . $skey;
                     $sq = "Select * FROM " . $this->view_id . $skey;
                     $row = SQL_OneRow($sq);
                 }
         }
     }
     // Save the row for other routines
     $this->row = $row;
     // Find out what skey we are on, give a next/prev
     // kind of button for stuff like that.
     // Set the next/prev stuff based on rows
     $HTML_PagePrev = $HTML_PageNext = $HTML_ViewingPage = "";
     if ($mode == "upd") {
         $lprev = $lnext = false;
         $skey = $this->row['skey'];
         $sess_skeys = ConGet("table", $this->table_id, "skeys", array());
         if (count($sess_skeys) > 1) {
             $sess_srows = array_flip($sess_skeys);
             $sess_srow = $sess_srows[$row['skey']];
             $lprev = $sess_srow == 0 ? false : true;
             $skeyf = $sess_srow == 0 ? 0 : $sess_skeys[0];
             $skeyp = $sess_srow == 0 ? 0 : $sess_skeys[$sess_srow - 1];
             $skeyn = $sess_srow >= count($sess_srows) - 1 ? 0 : $sess_skeys[$sess_srow + 1];
             $skeyl = $sess_srow >= count($sess_srows) - 1 ? 0 : $sess_skeys[count($sess_srows) - 1];
             $hprev = hLinkImage('first', 'First', 'gp_skey', $skeyf, $lprev) . hLinkImage('previous', 'Previous', 'gp_skey', $skeyp, $lprev);
             $lnext = $sess_srow < count($sess_srows) - 1 ? true : false;
             $hnext = hLinkImage('next', 'Next', 'gp_skey', $skeyn, $lnext) . hLinkImage('last', 'Last', 'gp_skey', $skeyl, $lnext);
             $HTML_ViewingPage = "Page " . ($sess_srow + 1) . " of " . count($sess_srows);
         }
     }
     // Output and save the navbar
     ob_start();
     if ($HTML_ViewingPage != '') {
         $hprev = $this->hTextButton('\\First', 'gp_skey', $skeyf, $lprev) . $this->hTextButton('\\Previous', 'gp_skey', $skeyp, $lprev);
         $hnext = $this->hTextButton('Ne\\xt', 'gp_skey', $skeyn, $lnext) . $this->hTextButton('Las\\t', 'gp_skey', $skeyl, $lnext);
         if (vgfget('buttons_in_commands')) {
             $this->h['NavBar'] = '';
             vgfSet('html_navbar', $hprev . $hnext);
         } else {
             $this->h['NavBar'] = "\n<div class=\"x2menubar\">\n" . $hprev . $HTML_ViewingPage . "&nbsp;&nbsp;&nbsp;" . $hnext . "\n</div><br>";
         }
     }
     // Second output is main content
     // KFD 8/9/07, Project DUPECHECK
     //             If a "dupecheck" projection exists, and they are
     //             doing a new entry, we first ask them to enter
     //             those values
     $dc = ArraySafe($this->table['projections'], 'dupecheck');
     if ($dc != '' && $mode == 'ins' && !gpExists('gp_nodupecheck')) {
         hidden('gp_action', 'dupecheck');
         $this->h['Content'] = $this->hBoxesX3($mode, 'dupecheck');
         $this->h['Content'] .= '<br/><br/>' . '<button  class="btn btn-primary id="object_for_enter" onclick="formSubmit()">(ENTER) Check For Duplicates</button>';
     } else {
         $this->h['Content'] = $this->hBoxesDefault($mode);
     }
 }
Exemple #5
0
 function fetchParent()
 {
     $ddpar = ddTable(gp('tableIdPar'));
     $pks = $ddpar['pks'];
     $stab = ddView(gp('tableIdPar'));
     $skey = SQLFN(gp('skeyPar'));
     $vals2 = SQL_OneRow("SELECT {$pks} FROM {$stab} WHERE skey = {$skey}");
     if (!$vals2) {
         $vals2 = array();
     }
     return $vals2;
 }
Exemple #6
0
    function ehTab_Filters($ajax = true)
    {
        if ($ajax) {
            echo "x2_content|";
        }
        $skey = SQLFN(gp('gp_skey'));
        $report = SQL_OneValue('report', "Select report from reports where skey={$skey}");
        // Do any processing that may have come through
        if (gp('gp_ajax') == 'filtrep') {
            $repfilters = SQLFC(gp('gp_val'));
            //echo "we are setting $repfilters";
            SQL("UPDATE reports SET repfilters={$repfilters} WHERE skey={$skey}");
        }
        if (gp('gp_ajax') == 'filtlev') {
            $repfilters = SQLFC(gp('gp_val'));
            $skeylev = SQLFN(gp('gp_skeylev'));
            SQL("UPDATE reportlevels \n                 SET repfilters={$repfilters} WHERE skey={$skeylev}");
        }
        // Retrieve and display
        $repfilter = SQL_OneValue('repfilters', "Select skey,repfilters from reports where skey={$skey}");
        $levs = SQL_AllRows("Select rl.skey,rl.reportlevel,rl.repfilters \n           from reportlevels    rl\n          WHERE exists ( SELECT * from reportcollevels\n                          WHERE report = '{$report}'\n                            AND reportlevel = rl.reportlevel)\n            AND report='{$report}'");
        //echo hErrors();
        // Now for each level list some filters
        ?>
      <table>
        <tr>
          <td class="dhead" style="width: 10em">Level</td>
          <td class="dhead">SQL Filters</td>
          <td class="dhead">Save</td>
        </tr>
        <tr>
          <td valign=top>Base</td>
          <td><textarea id='lev0' name="lev0"
                   cols=60
                   rows=5 
                  style="border:1px solid gray"
                ><?php 
        echo $repfilter;
        ?>
</textarea></td>
           <td><a href="javascript:sndReq('&gp_ajax=filtrep&gp_val='+encodeURIComponent(ob('lev0').value))">Save</a>

        </tr>
      <?php 
        foreach ($levs as $lev) {
            $js = '&gp_ajax=filtlev&gp_skeylev=' . $lev['skey'];
            ?>
         <tr>
           <td valign=top><?php 
            echo $lev['reportlevel'];
            ?>
</td>
           <td><textarea 
                    cols=60 
                    rows=5 
                   style="border:1px solid gray"
               onchange="sndReq('<?php 
            echo $js;
            ?>
&gp_val='+this.value)"
                 ><?php 
            echo $lev['repfilters'];
            ?>
</textarea></td>
         </tr>
         <?php 
        }
        ?>
      </table>
      <br/>
      <h3>Columns in this report</h3>
      <table style="width:50%">
        <tr>
          <td class="dhead">Description</td>
          <td class="dhead">Table</td>
          <td class="dhead">Column</td>
        </tr>
        <?php 
        // Columns in this report
        $sql = "SELECT description,table_id,column_id from reportcolumns\n                WHERE report = '{$report}'\n                ORDER BY description";
        $cols = SQL_Allrows($sql);
        foreach ($cols as $col) {
            ?>
            <tr><td><?php 
            echo $col['description'];
            ?>
                <td><?php 
            echo $col['table_id'];
            ?>
                <td><?php 
            echo $col['column_id'];
            ?>
            <?php 
        }
        ?>
      </table>
      <?php 
    }