DIY Script: Difference between revisions

Revision as of 06:25, 2 November 2015

Menu: Tools | DIY | SQL Control Center...

Introduction

This is Additional Module(DIY Script Module) which allow user to override or customise the SQL System.
The Language use is Pascal

DIY Script

Available Action/Event

Action/Event	Description
OnOpen	The script trigger On Open the Form (eg On Open the Sales Invoice Form)
OnClose	The script trigger On Close/Exit the Form (eg on Exit Sales Invoice Form)
OnBeforeNew	The script trigger before the build in New Button action execute.
OnAfterNew	The script trigger after the build in New Button action Execute.
OnBeforeEdit	The script trigger before the build in Edit Button action execute.
OnAfterEdit	The script trigger after the build in Edit Button action Execute.
OnBeforeDelete	The script trigger before the build in Delete Button action execute.
OnAfterDelete	The script trigger after the build in Delete Button action Execute.
OnBeforeSave	The script trigger before the build in Save Button action execute.
OnAfterSave	The script trigger after the build in Save Button action Execute.
OnBeforeCancel	The script trigger before the build in Cancel Button action execute.
OnAfterCancel	The script trigger after the build in Cancel Button action Execute.
OnBeforePrint	The script trigger before the build in Print or Preview Button action Execute.
OnGridBeforeInsert	The script trigger before the build in + Button action Execute.
OnGridAfterInsert	The script trigger after the build in + Button action Execute.
OnGridBeforePost	The script trigger before post the current row record.
OnGridAfterPost	The script trigger after post the current row record.
OnGridBeforeDelete	The script trigger before the build in - Button action Execute.
OnGridAfteDelete	The script trigger after the build in - Button action Execute.
OnGridBeforeCancel	The script trigger before cancel the current row record.
OnGridAfterCancel	The script trigger after cancel the current row record.
OnGridColumnValueChanged	The script trigger on changes value in the current record.

----------------------------------------------------------------------------------------------------------------------------------------------------

Commonly we only OnOpen, OnBeforeSave & OnGridColumnValueChanged Event

----------------------------------------------------------------------------------------------------------------------------------------------------

Avoid use 2 same Event/Action in 1 Business Object (eg 2 OnOpen in Sales Invoice is NOT allow)

----------------------------------------------------------------------------------------------------------------------------------------------------

Example 1 - Get UDF & Do Calculation

Below is Example are doing following actions

Get UDF_Length & UDF_Width from Maintain Item UDF Fields
Do Calculation Qty := UDF_Length * UDF_Width * UDF_Rate

Last Script Update : 25 Sep 2015

Steps

01. Click Tools | DIY | SQL Control Center...

02. At the left panel look for Sales Invoice

03. Right Click the Sales Invoice

04. Select New Event

05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)

06. Select OnGridColumnValueChanged 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)

var FComServer, lBizObj : Variant;
    cdsData : TClientDataSet;

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

procedure GetStockInfo;
var lSQL, lCode  : String;
begin
  FComServer := null;
  cdsData    := TClientDataSet.Create(nil); // Create & preparing Component
  try
    lCode := Dataset.FindField('ItemCode').AsString;     
    lSQL  := Format('SELECT UDF_Length, UDF_Width FROM ST_ITEM WHERE Code=%s',[QuotedStr(lCode)]);

    cdsData.Data := ComServer.DBManager.Execute(lSQL);
  finally
    lBizObj    := null;
    FComServer := null;
  end;
end;

begin
  if SameText(EditingField, 'ItemCode') OR // when selecting or change itemcode field
     SameText(EditingField, 'UDF_Rate') then begin // when change UDF_Rate field
    try
      GetStockInfo; // Get UDF_Length & UDF_Width from Maintain Item

      // Below is Set the Invoice detial UDF Fields from Maintain Item UDF Fields
      DataSet.FindField('UDF_Length').AsFloat := cdsData.FindField('UDF_Length').AsFloat;
      DataSet.FindField('UDF_Width').AsFloat  := cdsData.FindField('UDF_Width').AsFloat;

      // Below is doing calculation for Qty := UDF_Length * UDF_Width * UDF_Rate
      DataSet.FindField('Qty').AsFloat        := DataSet.FindField('UDF_Length').AsFloat *
                                                 DataSet.FindField('UDF_Width').AsFloat *
                                                 DataSet.FindField('UDF_Rate').AsFloat;
    finally
      cdsData.Free; // Free the Component after used
    end;
  end;
end.

10. Click Save button

----------------------------------------------------------------------------------------------------------------------------------------------------

Avoid update below field in same time as will cause unlimited looping updating each other

- Qty

- UnitPrice

- TaxAmt

- Amount

----------------------------------------------------------------------------------------------------------------------------------------------------

Example 2 - Change Title Caption

Below is Example are doing following actions

Change the Sales Quotation Title to My Quotation

Last Script Update : 19 Oct 2015

Steps

01. Click Tools | DIY | SQL Control Center...

02. At the left panel look for Sales Quotation

03. Right Click the Sales Quotation

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)

var L: TComponent;
Begin
  L := Self.FindComponent('lbQt');
  if Assigned(L) then
    TcxLabel(L).Caption := 'My Quotation';
end;

10. Click Save button

Example 3 - Get UDF From Maintain Customer

Below is Example are doing following actions

At Sales Invoice On Select Customer Code prompt UDF_MSG from Maintain Customer

Last Script Update : 20 Oct 2015

Steps

01. Click Tools | DIY | SQL Control Center...

02. At the left panel look for Sales Invoice

03. Right Click the Sales Invoice

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)

var FComServer, lBizObj : Variant;
    M : TDataSource;
    C : TcxDBTextEdit;
    lCdsDataList: TClientDataSet;
    lSQL : String;

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

procedure OnCompanyNameChanged(Sender: TObject);
var s      : string;
    AState : TDataSetState;
begin
  M         := TDataSource(Self.FindComponent('dsDocMaster')); 
  AState := M.DataSet.State;

  if (AState = dsInsert) or (AState = dsEdit)  then begin
    FComServer   := null;
    lCdsDataList := TClientDataSet.Create(nil);
  
    try
      lSQL := Format('SELECT UDF_MSG FROM AR_CUSTOMER WHERE Code= %s',[QuotedStr(M.DataSet.FindField('Code').AsString)]);
      lCdsDataList.Data := ComServer.DBManager.Execute(lSQL);

      s := lCdsDataList.FindField('UDF_MSG').AsString;
      if Trim(s) <> '' then
        MessageDlg(S, mtInformation, [mbOk], 0);
    finally
      FComServer := null;
      lCdsDataList.Free;
    end;
  end;
end;

begin
  C := TcxDBTextEdit(Self.FindComponent('edCompanyName'));
  if Assigned(C) then
   C.Properties.OnEditValueChanged := @OnCompanyNameChanged;
end.

10. Click Save button

Example 4 - Get Transfer Information

Below is Example are doing following action

Get the Invoice number, Date & Qty which had transferred to Invoice in the Quotation.

Last Script Update : 30 Oct 2015

Steps

01. Click Tools | DIY | SQL Control Center...

02. At the left panel look for Sales Quotation

03. Right Click the Sales Quotation

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)

var FComServer, lBizObj : Variant;
    C : TComponent;
    T : TTimer;
    M, D : TDataSource;
    L1, L2, L3, L4, L5, L6 : TLabel;
    cdsTemp : TClientDataset;
    
function ComServer: Variant;
begin
  if FComServer = Null then 
    FComServer := CreateOleObject('SQLAcc.BizApp');
  Result := FComServer;
end;

procedure Setup;
begin
  T  := TTimer.Create(Self);
  L1 := TLabel.Create(self);
  L2 := TLabel.Create(self);
  L3 := TLabel.Create(self);
  L4 := TLabel.Create(self);
  L5 := TLabel.Create(self);
  L6 := TLabel.Create(self);
end;

procedure GetToDocInfo;
var lSQL, lDocKey, lDtlkey : String;
begin
  lDocKey := M.Dataset.FindField('Dockey').AsString;
  lDtlKey := D.Dataset.FindField('Dtlkey').AsString;
  FComServer := null;
  cdsTemp := TClientDataset.Create(nil);
  lSQL := Format('SELECT A.DocNo, A.DocDate, B.Qty FROM SL_IV A '+
                 'INNER JOIN SL_IVDTL B ON (A.Dockey=B.Dockey) ' +
                 'WHERE B.FromDockey=%s ' +
                 'AND B.FromDtlKey=%s ' +
                 'AND B.FromDocType=''QT'' ',[lDockey, lDtlkey]);

  try
    cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
  finally
    FComServer := null;
  end;
end;

procedure OnTimer(Sender: TObject);
var AState : TDataSetState;
begin
  AState := M.DataSet.State;
  if AState = dsBrowse then begin
    GetToDocInfo;
    L2.Caption := '';
    L4.Caption := '';
    L6.Caption := '';
    try
      L2.Caption := cdsTemp.FindField('DocNo').AsString;
      if Trim(cdsTemp.FindField('DocDate').AsString) <> '' then
        L4.Caption := FormatDateTime('dd/MM/yyyy', cdsTemp.FindField('DocDate').AsDateTime);
      L6.Caption := FormatCurr('#,0.00;-#,0.00', cdsTemp.FindField('Qty').AsFloat); 
    finally
      cdsTemp.Free;
    end;
  end;
  
end;

begin
  M    := TDataSource(Self.FindComponent('dsDocMaster'));
  D    := TDataSource(Self.FindComponent('dsDocDetail'));
      
  C := Self.FindComponent('frDataSetButton1');  
  if Assigned(C) then begin
    T.Enabled  := True;
    T.Interval := 1000; // = 1 sec
    T.OnTimer  := @OnTimer;  
   
    with L1 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 200;
      Caption    := 'Invoice No';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;    
    with L2 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 215;
      Caption    := 'DocNo';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;

    with L3 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 240;
      Caption    := 'IV Date';
      Font.Color := clGreen;
      Font.Style := [fsBold];
    end;
    with L4 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 255;
      Caption    := 'Date';
      Font.Color := clGreen;
      Font.Style := [fsBold];
    end;

    with L5 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 280;
      Caption    := 'XF Qty';
      Font.Color := clRed;
      Font.Style := [fsBold];
    end;
    with L6 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 295;
      Caption    := 'Qty';
      Font.Color := clRed;
      Font.Style := [fsBold];
    end;      
  end;   
end.

10. Click Save button

Example 5

More Coming Soon....

@@ Line 81: / Line 81: @@
 :02. At the left panel look for '''Sales Invoice'''
 :03. Right Click the '''Sales Invoice'''
-::[[File:DIYField-03.jpg]]
+[[File:DIYField-03.jpg|center]]
 :04. Select '''New Event'''
-::[[File:DIYScript-01.jpg]]
+[[File:DIYScript-01.jpg|center]]
 :05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
 :06. Select '''OnGridColumnValueChanged''' for '''Event''' field
 :07. Click OK
 :08. Click the Calc (name create at Step 5 above) on the left panel
-::[[File:DIYScript-02.jpg|700px]]<br />
+[[File:DIYScript-02.jpg|700px|center]]<br />
 :09. Copy below script & paste to the Right Panel (Script Section)
 <syntaxhighlight lang="delphi">

Revision as of 06:25, 2 November 2015

Introduction

DIY Script

Example 1 - Get UDF & Do Calculation

Steps

Example 2 - Change Title Caption

Steps

Example 3 - Get UDF From Maintain Customer

Steps

Example 4 - Get Transfer Information

Steps

Example 5

See also