Customization of Script

From Golink Wiki
Jump to navigation Jump to search

Introduction

SQL are flexible to allow user insert script to customize SQL Account / Payroll. Every business do have different requirement, so below is some example case of customization of script.

SQL Account

Case 1 - Prompt Override Dialog If Document Not Using Transfer From/To

Purchase Invoice: Prompt override when system detect purchase invoice's item is not transfer from goods received OR purchase order, save abort.
Required modules: Partial Delivery, DIY Field, DIY Script
SQL Account version: 783 or above
Updated on : 09/12/2019

Step 1 - Create User Rights Field

01. Click Tools | DIY | Maintain DIY
02. Go to Field and Quick Form tab
03. Right click the User
04. Select New Field
05. Click New
06. Enter any name (eg AddField) in the Description field (Only Alphanumeric & no spacing)
07. Click +
08. Add field as below
Name Data Type Size Sub Size Null Description Default Display Format
AllowOverride Boolean 0 0 True AllowOverride 0
Password String 10 0 True Password
09. Click Save
10. Close dialog
11. Select the created list item
12. Click Ok
13. Right again click the User
14. Select New Quick Form
15. Click New
16. Enter any name (eg More) in the Description field (Only Alphanumeric & no spacing)
17. Click Customize
18. Drag and drop the field from Right Panel to Left Panel
19. Click Close | Save
20. Select the created form | Click Ok

Step 2 - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Purchase Invoice
03. Right Click the Purchase Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var lSQL : String;
    cdsUsers    : TClientDataSet;
    dsDocMaster, M, D : TDataSource;
    FComServer, lBizObj : Variant;
    i : Integer ;

function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;

procedure Authorised(const lUN, lUPW : string);
var lAPW, lSQL : String;
    cdsUsers   : TClientDataSet;
begin
  FComServer   := null;
  cdsUsers     := TClientDataSet.Create(nil); 
  lSQL := Format('SELECT UDF_Password FROM SY_USER WHERE Code=%s AND UDF_AllowOverride=''1'' ',[QuotedStr(UpperCase(lUN))]);

  try
    cdsUsers.Data := ComServer.DBManager.Execute(lSQL); 
    lAPW := UpperCase(cdsUsers.FindField('UDF_Password').AsString);

    if (lUPW <> lAPW) or (Trim(lAPW) = '') then begin
       MessageDlg('Incorrect Password', mtWarning, [mbOK], 0);
       Abort;
    end;
  finally
    lBizObj    := null;
    FComServer := null;
    cdsUsers.Free;
  end;
end;
    
procedure InitPWForm;
var fmPassword : TForm;
    lbMessage : TLabel;
    lbUserName, lbPassword : TcxLabel;
    edUserName, edPassword : TEdit;
    btnOK      : TButton;
begin
  fmPassword := TForm.Create(nil);
  lbUserName := TcxLabel.Create(fmPassword);
  lbPassword := TcxLabel.Create(fmPassword);
  lbMessage  := TLabel.Create(fmPassword);
  edUserName := TEdit.Create(fmPassword);
  edPassword := TEdit.Create(fmPassword);
  btnOK      := TButton.Create(fmPassword);

  try
    with fmPassword do begin
      Parent      := nil;
      Height      := 160;
      Width       := 300;
      Caption     := 'Please Enter User Name and Password';
      Color       := clWhite;
      BorderStyle := bsDialog;
      Position    := poMainFormCenter;
    with lbMessage do begin
      Parent  := fmPassword;
      Left    := 10;
      Top     := 10;
      Caption := 'Override without use transfer function?';
      Font.Color := clBlue;
      Font.Style := [fsBold];
      Font.Size  := 8;
    end;
    with lbUserName do begin
      Parent  := fmPassword;
      Left    := lbMessage.Left;
      Top     := lbMessage.Top + lbMessage.Height + 10;
      Caption := '&User Name :';
    end;
    with edUserName do begin
      Parent   := fmPassword;
      Left     := 100;
      Top      := lbMessage.Top + lbMessage.Height + 10;
      Width    := 150;
      CharCase := ecUpperCase;
    end;
    with lbPassword do begin
      Parent  := fmPassword;
      Left    := lbMessage.Left;
      Top     := lbUserName.Top + lbUserName.Height + 5;
      Caption := '&Password :';
    end;
    with edPassword do begin
      Parent := fmPassword;
      Left   := 100;
      Top    := lbUserName.Top + lbUserName.Height + 5;
      Width  := 150;
      CharCase     := ecUpperCase;
      PasswordChar := '*';
    end;
     with btnOK do begin
       Parent      := fmPassword;
       Top         := edPassword.Top + edPassword.Height + 10;
       Width       := 100;
       Left        := (fmPassword.Width/2) - 50;
       Caption     := '&OK';
       ModalResult := mrOk;
     end;

    if fmPassword.ShowModal = mrOK then
       Authorised(edUserName.Text, edPassword.Text) else
       Authorised('','');
    end;
  finally
    fmPassword.Free;
  end;
end;

begin
  M := TDatasource(Self.FindComponent('dsDocMaster'));
  D := TDataSource(Self.FindComponent('dsDocDetail'));

  D.DataSet.First;
  While not D.DataSet.EOF do begin
    if (D.DataSet.FindField('FromDocType').AsString = 'PO') OR (D.DataSet.FindField('FromDocType').AsString = 'GR') then
    else begin
      i := MessageDlg('Item are not transfer from Purchase Order, do you want to override?', mtWarning, [mbYes, mbNo], 1);
      if i = mrYes then InitPWForm
      else begin
        Abort;
      end;
    end;
    D.DataSet.Next
  end;
                
end;
10. Click Save button

Case 2 - Add Text Field In Maintain Stock Item

Maintain Stock Item: Create additional field without using quick form, so can insert data without go to additional tab
Required modules: DIY Field, DIY Script
SQL Account version: 783 or above
Updated on : 17/12/2019

Step 1 - Create Stock Item Fields

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Stock | Stock Item | Stock Item Fields
03. Click +
04. Add field as below
Name Data Type Size Sub Size Caption Required Default Value Display Format
EstimateCost Currency UDF_EstimateCost False 0.00 0.00

Step 2 - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Stock Item
03. Right Click the Stock Item
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var dsAccess : TDataSource;
    edEstCost : TcxDBTextEdit;
    lEstCost : TLabel;
    edTariff : TcxDBExtLookupComboBox;

begin
  dsAccess  := Self.FindComponent('dsAccess') as TDataSource;
  edTariff  := Self.FindComponent('edTariff') as TcxDBExtLookupComboBox;
  
  lEstCost         := TLabel.Create(Self);
  lEstCost.Parent  := edTariff.Parent;
  lEstCost.Left    := edTariff.Left + edTariff.Width + 5;
  lEstCost.Top     := edTariff.Top + 3;
  lEstCost.Caption := 'Estimation Cost (MYR):' ;
  
  edEstCost := TcxDBTextEdit.Create(nil);
  with edEstCost do begin
     Parent := lEstCost.Parent;
     Name   := 'EstCost';
     Left   := lEstCost.Left + lEstCost.Width + 5;
     Top    := edTariff.Top;
     Width  := lEstCost.Width;
     edEstCost.DataBinding.DataSource := dsAccess;
     edEstCost.DataBinding.DataField  := 'UDF_EstimateCost';                     
  end;
end.
10. Click Save button

Case 3 - Coming Soon...

Retrieved from "https://wiki.golink.com.my/wiki/index.php?title=Customization_of_Script&oldid=32"