Lock Report by User: Difference between revisions

From eStream Software
 
(5 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 44: Line 40:
::* etc...
::* etc...
<br />
<br />
2. Insert and update the following DIY Script into Script tab (Report name - Param Form) in Tools | DIY | [[Maintain DIY]] <br />
2. Insert and update the following DIY Script into Script tab (Report name - Param Form) in Tools | DIY | [[Maintain DIY]] <br />


Line 75: Line 70:
   FComServer := null;
   FComServer := null;
   cdsTemp := TClientDataset.Create(nil);
   cdsTemp := TClientDataset.Create(nil);
   lSQL := Format('SELECT UDF_Lock, UDF_Agent_Lock, UDF_Agent, UDF_Location FROM SY_USER WHERE Code=%s',[QuotedStr(UserID)]);
   lSQL := Format('SELECT UDF_Location_Lock, UDF_Agent_Lock, UDF_Agent, UDF_Location FROM SY_USER WHERE Code=%s',[QuotedStr(UserID)]);
   
   
   try
   try
     cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
     cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
     if cdsTemp.RecordCount > 0 then begin
     if cdsTemp.RecordCount > 0 then begin
       lLock := cdsTemp.FindField('UDF_Lock').Value = 1;
       lLock := cdsTemp.FindField('UDF_Location_Lock').Value = 1;
       L.Text := cdsTemp.FindField('UDF_Location').AsString;
       L.Text := cdsTemp.FindField('UDF_Location').AsString;
       lLock2 := cdsTemp.FindField('UDF_Agent_Lock').Value = 1;
       lLock2 := cdsTemp.FindField('UDF_Agent_Lock').Value = 1;

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