Difference between revisions of "Customization of Script"

From Golink Wiki
Jump to navigation Jump to search
(Created page with "== 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...")
 
 
(9 intermediate revisions by the same user not shown)
Line 4: Line 4:
 
== SQL Account ==
 
== SQL Account ==
 
=== Case 1 - Prompt Override Dialog If Document Not Using Transfer From/To ===
 
=== Case 1 - Prompt Override Dialog If Document Not Using Transfer From/To ===
−
Goods Received - Prompt override when system detect goods received's item is not transfer from purchase order, save abort.<br>
+
Purchase Invoice: Prompt override when system detect purchase invoice's item is not transfer from goods received OR purchase order, save abort.<br>
−
Purchase Invoice - Prompt override when system detect purchase invoice's item is not transfer from goods received OR purchase order, save abort.<br>
+
Required modules: Partial Delivery, DIY Field, DIY Script<br>
 +
SQL Account version: 783 or above<br>
 
Updated on : 09/12/2019
 
Updated on : 09/12/2019
  
−
'''Steps - OnGridColumnValueChanged'''
+
'''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
 +
{| class="wikitable"
 +
|-
 +
! 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...'''
 
:01. Click '''Tools | DIY | SQL Control Center...'''
−
:02. At the left panel look for '''Sales Invoice'''  
+
:02. At the left panel look for '''Purchase Invoice'''  
−
:03. Right Click the '''Sales Invoice'''
+
:03. Right Click the '''Purchase Invoice'''
 
:04. Select '''New Event'''
 
:04. Select '''New Event'''
 
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
 
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
−
:06. Select '''OnGridColumnValueChanged''' for '''Event''' field
+
:06. Select '''OnBeforeSave''' for '''Event''' field
 
:07. Click OK
 
:07. Click OK
 
:08. Click the Calc (name create at Step 5 above) on the left panel
 
: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)
 
:09. Copy below script & paste to the Right Panel (Script Section)
 +
{| class="mw-collapsible mw-collapsed wikitable"
 +
! OnBeforeSave Script 
 +
|-
 +
|
 +
<syntaxhighlight lang="delphi">
 +
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;
 +
</syntaxhighlight>
 +
|}
 +
: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<br>
 +
Required modules: DIY Field, DIY Script<br>
 +
SQL Account version: 783 or above<br>
 +
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
 +
{| class="wikitable"
 +
|-
 +
! 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)
 +
{| class="mw-collapsible mw-collapsed wikitable"
 +
! OnBeforeSave Script 
 +
|-
 +
|
 +
<syntaxhighlight lang="delphi">
 +
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.
 +
</syntaxhighlight>
 +
|}
 +
:10. Click '''Save''' button
 +
 +
=== Case 3 - Coming Soon... ===

Latest revision as of 07:28, 17 December 2019

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"