Grid 'Group By' looses the where restriction of SQL

Code Examples Grid
#1

Hello all,
I got a grid that shows all the customers ‘orders’ connected to a salesman/representant [glo_SalesmanID] and I’ve a where clause on SQL like this:

SELECT
OrderID,
StatusID,
Note_Header,
CustomerID,
SalesmanID,
EmployeeID,
OrderDate,
Id_Ord,
RequiredDate,
Expenses
FROM
orders
WHERE SalesmanID = ‘[glo_SalesmanID]’
ORDER BY
OrderDate DESC

I did then a Group By using
–SalesmanID 1st level
----CustomerID 2nd level

Now when I enter on grid as Salesman XYZ I was waiting to see ‘only’ the customers connected to XYZ only selected by the SQL.

I don’t know why but it happens that I see also other customers and theirs orders not related to XYZ ( I mean customers owed by another Salesman).

This happens if I enter using App_login . If I run application and give the SalesmanID manually it works .

Just for info on App_login I got this line that in theory clean the global variable from previous login.
sc_reset_global([usr_login], [usr_email], [glo_CustomerID], [glo_SalesmanID], [glo_UserType], [glo_SaleseosID] );

Very strange. I did all possible thoughts but I don’t know what to do to avoid this umbelievable problem.

Any suggestion is very appreciated
Thanks

#2

Did you set the [glo_SalesmanID] to a value when he logs in? Or did you set it in a wrong event? Add some echo statements in your code and you’ll see it quickly.

#3

Tnanks rr,
The problem I think is that if when you login as Agent and not as Customer the CustomerID remain dirty from previous Login and make confusion.
CustomerID is used as “user” ID (I mean I use the same table to check the login user/psw) for all and so probably when I go later on grid Group by there are problems.
I’ve tried to insert a if (UserType,==‘AGE’) { [glo_CustomerID] =’’ } but it does not work the same.

This is OnValidate App_Login code:

$sql = "SELECT
Admist_priviledge,
Active,
CompanyName,
Email,
CustomerID,
SalesmanID,
UserType,
SaleseosID
FROM customers
WHERE User = $slogin
AND Password = ".$spswd;
sc_lookup(rs, $sql);

if(count({rs}) == 0)
{
sc_log_add(‘login Fail’, {lang_login_fail} . {login});
sc_error_message({lang_error_login});
sc_error_exit();
}
else if({rs[0][1]} == ‘Y’)
{
[usr_login] = {login};
[usr_priv_admin] = {rs[0][0]};
[usr_name] = {rs[0][2]};
[usr_email] = {rs[0][3]};
[glo_CustomerID] = {rs[0][4]};
[glo_SalesmanID] = {rs[0][5]};
[glo_UserType] = {rs[0][6]};
[glo_SaleseosID] = {rs[0][7]};
}
else
{
sc_error_message({lang_error_not_active});
sc_error_exit();
}

#4

After having tried all … kind of echo and checks… I decided to go out of office to change … air very upset about the annoying problem.
A thought comes and I got the idea to check the login sc_redir parameters…

I thought that using global variables , in theory, they were inherited correctly also on sc_redir application…
In practical NOT !!! So much time to understand…it

Now everything work as expected:

switch ([glo_UserType]) {
case “CLI”:
sc_redir(‘hhh_grid_products_for_order’, param1=[glo_CustomerID]);
break;
case “AGE”:
sc_redir(‘grid_salesman_orders’, param2=[glo_SalesmanID]);
break;
case “EOS”:
sc_redir(‘grid_saleseos_orders’, param3=[glo_SaleseosID] );
break;
case “ADM”:
sc_redir(‘menu_admin’);
break;
}

You that are tech guys do you confirm that also if using globals var I have to specify the parameter ??
Thanks

#5

Is glo_SaleseosID defined as OUT? Is deifned as session?

#6

Hi Giu,
all variables on App_Login are set to OUT and not defined as Session .
If you have a minute can you explain a bit the difference of flagging session radio box ? For a not programmer this is not so easy to understand.
When you have to use it and why ?