Lock Report by User: Difference between revisions

From eStream Software
 
Line 1: Line 1:
==Introduction==
==Introduction==
For some reason, the company manager wish to restrict the users to check and view their own part of data.  
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 />



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