Lock Report by User: Difference between revisions

From eStream Software
No edit summary
 
(8 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Introduction==
==Introduction==
 
For some reason, the company managers wish to let users to check and view the report by themselves. They cannot access other users data, eg. agent A cannot see agent B sales value. Therefore, we have come out this script to lock at the report according to the user rights.
<br />
<br />


==Updates==
==Updates==
Last Customisation Update : 06 Nov 2015
Last Customisation Update : 07 Nov 2015


==Criterias==
==Criterias==
Line 18: Line 18:
|-
|-
| STD || Name || String || 100 || User Name
| STD || Name || String || 100 || User Name
|-
| DIY-F || Lock_Cust_Code || Boolean || False value: 0 <br /> True value: 1 || To lock the customer code parameter in report
|-
| DIY-F || Cust_Code || String || 10 || Set a customer code to lock to this user
|-
|-
| DIY-F || Agent_Lock || Boolean || False value: 0 <br /> True value: 1 || To lock the agent parameter in report
| DIY-F || Agent_Lock || Boolean || False value: 0 <br /> True value: 1 || To lock the agent parameter in report
Line 43: Line 39:
::* Stock Card (Location)
::* Stock Card (Location)
::* etc...
::* etc...
<br />
2. Insert and update the following DIY Script into Script tab (Report name - Param Form) in Tools | DIY | [[Maintain DIY]] <br />
For instance, apply the below script into Sales Profit & Loss By Document - Param Form.<br />
::[[File:00-Maintain DIY-Script.jpg | 30PX]]
<br />
<br />


2. Insert and update the following DIY fields & Script into above mentions sales documents in Tools | DIY | SQL Control Center... <br />
DIY Script: '''OnGridColumnValueChange''' event<br />
  <syntaxhighlight lang="delphi">
  <syntaxhighlight lang="delphi">
uses SysUtils, Dialogs, Forms, ComObj, DBClient;
var C, D : TWinControl;
    FComServer, lBizObj : Variant;
    lLock, lLock2 : Boolean;
    L, P : TStringList;
function ComServer: Variant;
begin
  if FComServer = Null then begin
      FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
procedure CheckLock;
var cdsTemp : TClientDataset;
    lSQL : String;
begin
  lLock := False;
  lLock2 := False;
  FComServer := null;
  cdsTemp := TClientDataset.Create(nil);
  lSQL := Format('SELECT UDF_Location_Lock, UDF_Agent_Lock, UDF_Agent, UDF_Location FROM SY_USER WHERE Code=%s',[QuotedStr(UserID)]);
  try
    cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
    if cdsTemp.RecordCount > 0 then begin
      lLock := cdsTemp.FindField('UDF_Location_Lock').Value = 1;
      L.Text := cdsTemp.FindField('UDF_Location').AsString;
      lLock2 := cdsTemp.FindField('UDF_Agent_Lock').Value = 1;
      P.Text := cdsTemp.FindField('UDF_Agent').AsString;
    end; 
  finally
    cdsTemp.Free;
    FComServer := null; 
    end;
end;
begin
  C := Self.FindChildControl('edLocation_Panel') as TWinControl;
  D := Self.FindChildControl('edAgent_Panel') as TWinControl;
  L := TStringList.Create;   
  P := TStringList.Create;                                   
 
  CheckLock;
  try
    if C <> nil then C.Enabled := not lLock;
      if lLock then begin
      C := TWinControl(C).FindChildControl('edLocation_Edit') as TWinControl;     
      (C as TControl).SetTextBuf(L.CommaText);     
    end; 


    if D <> nil then D.Enabled := not lLock2;
      if lLock2 then begin
      D := TWinControl(D).FindChildControl('edAgent_Edit') as TWinControl;     
      (D as TControl).SetTextBuf(P.CommaText);     
    end;
 
  finally
    L.Free;
    P.Free;
  end;             
end.
  </syntaxhighlight>
  </syntaxhighlight>
<br />
<br />

Latest revision as of 02:06, 7 November 2015

Introduction

For some reason, the company managers wish to let users to check and view the report by themselves. They cannot access other users data, eg. agent A cannot see agent B sales value. Therefore, we have come out this script to lock at the report according to the user rights.

Updates

Last Customisation Update : 07 Nov 2015

Criterias

Maintain User

[Tools | Maintain User...]

1. Advanced Lock in Maintain User.

Types Field Name Data Types Length Usage
STD Code String 20 User ID, eg. LEE
STD Name String 100 User Name
DIY-F Agent_Lock Boolean False value: 0
True value: 1
To lock the agent parameter in report
DIY-F Agent String 10 Set an agent code to lock to this user
DIY-F Location_Lock Boolean False value: 0
True value: 1
To lock the location parameter in report
DIY-F Location String 10 Set a location code to lock to this user


2. The above DIY fields are created at Tools | DIY | Maintain_DIY

Reporting

1. As long the report parameter has Project, Agent, Area, or Location, the reports able to lock based on user login. For instances:-

  • Customer Aging (Agent, Area, Project)
  • Sales/Purchase Document Listing (Agent, Area, Doc Project)
  • Outstanding Sales/Purchase Document Listing (Agent, Area, Doc Project, Item Project, Location)
  • Stock Card (Location)
  • etc...


2. Insert and update the following DIY Script into Script tab (Report name - Param Form) in Tools | DIY | Maintain DIY

For instance, apply the below script into Sales Profit & Loss By Document - Param Form.

30PX


 uses SysUtils, Dialogs, Forms, ComObj, DBClient;
 
 var C, D : TWinControl;
     FComServer, lBizObj : Variant;
     lLock, lLock2 : Boolean;
     L, P : TStringList;
 
 function ComServer: Variant;
 begin
   if FComServer = Null then begin
      FComServer := CreateOleObject('SQLAcc.BizApp');
   end;
   Result := FComServer;
 end;
 
 procedure CheckLock;
 var cdsTemp : TClientDataset;
     lSQL : String;
 begin
   lLock := False; 
   lLock2 := False;
   FComServer := null;
   cdsTemp := TClientDataset.Create(nil);
   lSQL := Format('SELECT UDF_Location_Lock, UDF_Agent_Lock, UDF_Agent, UDF_Location FROM SY_USER WHERE Code=%s',[QuotedStr(UserID)]);
 
   try
     cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
     if cdsTemp.RecordCount > 0 then begin
       lLock := cdsTemp.FindField('UDF_Location_Lock').Value = 1;
       L.Text := cdsTemp.FindField('UDF_Location').AsString;
       lLock2 := cdsTemp.FindField('UDF_Agent_Lock').Value = 1;
       P.Text := cdsTemp.FindField('UDF_Agent').AsString;
    end;  
 
   finally 
     cdsTemp.Free;
     FComServer := null;   
     end;
 end;
 
 begin
 
   C := Self.FindChildControl('edLocation_Panel') as TWinControl;
   D := Self.FindChildControl('edAgent_Panel') as TWinControl;
   L := TStringList.Create;    
   P := TStringList.Create;                                    
   
   CheckLock;
   try
     if C <> nil then C.Enabled := not lLock;
       if lLock then begin
       C := TWinControl(C).FindChildControl('edLocation_Edit') as TWinControl;      
       (C as TControl).SetTextBuf(L.CommaText);       
     end;   

     if D <> nil then D.Enabled := not lLock2;
       if lLock2 then begin
       D := TWinControl(D).FindChildControl('edAgent_Edit') as TWinControl;      
       (D as TControl).SetTextBuf(P.CommaText);       
     end; 
   
   finally 
     L.Free;
     P.Free; 
   end;              
 end.


See also