Sc_select_where(add) breaks refined search, quick search and advanced search in grid

Scriptcase 9
,
#1

I created a grid and want to show all records to employees but only specific records to the client. Using sc_select_where(add) I am able to make that work. The problem is that my grid also uses refined search and that no longer works - and neither does advanced search or quick search.

When using advanced search I get this error attempting to filter by category: "Error while accessing the database You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE loc_id = ‘20’’ at line 1
select count(*) from asset where (category = ‘Network’ ) WHERE loc_id = ‘20’.

Quick Search and refined search popup an html error.

Without building an identical grid just to make the filter work; how can I show the client only those rows that belong to the client?

#2

hi, are you using windows or linux? I do it like so:

In the onAplicationInt I look which user is logged in:
//Auslesen des Benutzers
if (empty($_SERVER[‘REMOTE_USER’]))
{
$user = ‘x.xxx’;
}
else
{
$user = $_SERVER[‘REMOTE_USER’];
$user = substr($user,strpos( $user , ‘\’, 0) +1 );
}
$user = strtolower($user);

Then, you can access with macro sc_field_disabled or something else you want…

in onscriptint you do it like:
if (($KURZ == ‘SR’) or ($KURZ == ‘MA’) or ($KURZ == ‘RM’) or ($KURZ == ‘DE’) or ($KURZ == ‘BJ’) or ($KURZ == ‘BF’)) {
sc_field_disabled(“Field_01”);
}
This is like I do grids, where some users should not see all fields.

#3

I am not attempting to hide fields or buttons … I want to keep all the functionality of the grid but limit the records that the user can see. I can make that work with sc_select_where(add) … but then when I use quick search, advanced search or refined search I get an error indicating duplication in the WHERE statement:

‘WHERE loc_id = ‘20’’ at line 1 select count(*) from asset where (category = ‘Network’ ) WHERE loc_id = ‘20’.

#4

There is no where statement in the original query. It is modified onScriptInit with this statement:

sc_select_where(add) = " WHERE loc_id = ‘[glo_client_location]’" ;

That works fine until I attempt to use any of the search features after the grid has loaded. For example, if I want to further filter the grid by category, SC issues this statement:

select count(*) from asset where (category = ‘Network’ ) WHERE loc_id = ‘20’.

It would work if my sc_select_where(add) included an ADD statement but there is no original where statement to add to.

#5

it has already happened to me that after compiling, a compile error gives a bad form or grid result with nested formulas,
I solved by recreating the grid or form starting from a new grid or new form after renaming the old one, I have 1250 apps, which sometimes causes bad compilations, the quotes which appear to designate the global do not appear in the sql module of the form or the grid, on the other hand they are necessary in a sql request of a field, but that I suppose that you already understood it, good luck

#6

As written in documentation, you have to check if there is any current condition, and so add new condition via WHERE or AND.

if (empty({sc_where_current}))
{
sc_select_where(add) = “where campoX > [global]”;
}
else
{
sc_select_where(add) = “AND campoX > [global_variable]”;
}

try in this way.

#7

The conditional WHERE or AND does not work because the 2nd sc_select_where(add) is empty.

#8

This was the original query (it works - but breaks the refined search, advanced search and quick search) OnScriptInit:

$check_table = ‘location_people’; // Table name
$check_where = “user_id = [usr_id] and loc_id IN (SELECT id FROM location WHERE is_owner = ‘Y’)”; // Where clause

// Check for record
$check_sql = ‘SELECT *’
. ’ FROM ’ . $check_table
. ’ WHERE ’ . $check_where;
sc_select(dataset, $check_sql);

if (false == {dataset})
{
// Error while accessing database
}
elseif ({dataset}->EOF)
{
//No record found - this is NOT an employee - restrict view
$is_employee = ‘N’ ;
sc_select_where(add) = " WHERE loc_id IN (SELECT loc_id FROM location_people WHERE user_id = ‘[usr_id]’)" ;

}
else
{
// Record found - this IS an employee - show all rows
$is_employee = ‘Y’ ;

}

#9

This is what I changed it to - and it appears to work with all functionality of the grid (not finished testing)
OnApplicationInit:

//select ALL locations a person is at
$arr_rows = array();
sc_select(rs, “SELECT loc_id FROM location_people WHERE user_id = [usr_id]”);

while(!$rs->EOF)
{
$arr_rows[] = $rs->fields[0];
$rs->MoveNext();
}
$rs->Close();
//print_r($arr_rows) ;

[locations] = implode (", ", $arr_rows);

OnScriptInit:

//20200710:bh Restrict view of client
// - client location is set on login
$check_table = ‘location’; // Table name
$check_where = “id = ‘[locations]’ AND is_owner = ‘N’”; // Where clause

// Check for record
$check_sql = ‘SELECT *’
. ’ FROM ’ . $check_table
. ’ WHERE ’ . $check_where;
sc_select(dataset, $check_sql);

if (false == {dataset})
{
// Error while accessing database
}
elseif ({dataset}->EOF)
{
//No record found - this is an employee - show all records
$is_employee = ‘Y’ ;
}
else
{
// Record found - this is a client - restrict view to client location
$is_employee = ‘N’ ;
}

if ($is_employee == ‘Y’)
{

  //	Hide private records
  if([usr_priv_private] == 'Y') 
  {
  	sc_field_display({private}, 'on');
  	sc_btn_display('run_private_change', 'on') ;
  } else {
  	sc_select_where(add) = " AND private = 'N' ";
  	sc_field_display({private}, 'off') ;
  	sc_btn_display('run_private_change', 'off') ;
  }

}
else
{
//Hide private records
//Show items at user location only
if([usr_priv_private] == ‘Y’)
{
sc_select_where(add) = " AND loc_id IN ([locations])" ;
sc_field_display({private}, ‘on’);
sc_btn_display(‘run_private_change’, ‘on’) ;
} else {
sc_select_where(add) = " AND loc_id IN ([locations]) AND private = ‘N’" ;
sc_field_display({private}, ‘off’) ;
sc_btn_display(‘run_private_change’, ‘off’) ;
}

//client cannot add or edit
sc_btn_display(‘new’, ‘off’) ;
sc_apl_conf (“grid_asset”, “lig_edit”, “off”);

}

Modified the SQL to include a where statement:

SELECT
id,
name,
loc_id,
description,
category,
in_service
FROM
asset
WHERE trash = ‘N’

#10

I moved the code from OnApplicationInit into my login script