function makeDefault() { $col = SQLFN(gp('column')); $skey = SQLFN(gp('skey')); SQL("update configuser set {$col} = null WHERE skey = {$skey}"); configWrite('user'); }
/** * Execute an skey-based delete * */ function delete() { $skey = SQLFN(gp('skey')); $sq = "Delete from " . $this->view_id . " where skey = {$skey}"; SQL($sq); }
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 ''; } }
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 . " " . $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); } }
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; }
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 }