DIY Script

From eStream Software
Revision as of 02:30, 25 November 2015 by Twfaung (talk | contribs) (→‎Steps)

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.
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg
Commonly we only OnOpen, OnBeforeSave & OnGridColumnValueChanged Event
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg
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
Level : Basic

Steps

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
DIYField-03.jpg
04. Select New Event
DIYScript-01.jpg
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
DIYScript-02.jpg


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 Temporary Table
  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); // Execute command & Load the result
  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 temporary table after used
    end;
  end;
end.
10. Click Save button
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg
  • 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
Level : Basic

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)
Click to show ==> 
var L: TComponent;
Begin
  L := Self.FindComponent('lbQt'); // Find the original label
  if Assigned(L) then
    TcxLabel(L).Caption := 'My Quotation'; // Change the label Caption
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
Level : Advance

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 //Only Execute if in Edit or Insert Mode
    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 and shown it below the Browse button

Last Script Update : 30 Oct 2015
Level : Advance

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; // = Update every 1 second
    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 - Only Enable Some of the field on Click Edit by User

Below is Example are doing following action in Sales Cash Sales

  • Set all Field to Read Only at Header except Agent, DocNo, DocRef1, DocNoEx & UDF_Expired
  • Set all Detail Field to Read Only except Description & Account Field

Remember to Enable Back by Disable the GetAuthorised (see below) Script & Insert it to OnBeforeNew script
Cons : It will be delay about 1 to 2 sec. after click Edit & New button

Last Script Update : 03 Nov 2015
Level : Advance

Steps

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Cash Sales
03. Right Click the Sales Cash Sales
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeEdit 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 lAST : Boolean;
    i : integer;
    C : TWinControl;
    S : TStringList;
    gvDocDetail: TcxGridDBTableView;
    DBNavDetail : TDBNavigator;
    btnMoveUp, btnMoveDown : TSpeedButton;
    M : TDataSource;
    FComServer, lBizObj : Variant;

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

procedure GetAuthorised;
var cdsTemp : TClientDataset;
    lSQL : String;
begin
  FComServer := null;
  cdsTemp := TClientDataset.Create(nil);
  lSQL := Format('SELECT UDF_AllowEdit FROM SY_USER WHERE Code=%s',[QuotedStr(CurrentUser)]);

  try
    cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
    if cdsTemp.RecordCount > 0 then
      lAST := cdsTemp.FindField('UDF_AllowEdit').Value = 1;
  finally
    cdsTemp.Free;
    FComServer := null;
  end;
      
end;

begin
  lAST := True; // Default All Enable
  M    := TDataSource(Self.FindComponent('dsDocMaster'));
  gvDocDetail := TcxGridDBTableView(self.FindComponent('gvDocDetail'));
  DBNavDetail := TDBNavigator(self.FindComponent('DBNavDetail'));
  btnMoveUp   := TSpeedButton(self.FindComponent('btnMoveUp'));
  btnMoveDown := TSpeedButton(self.FindComponent('btnMoveDown'));
  S := TStringList.Create;
  try
    GetAuthorised; // Just Disable/Delete this line for OnBeforeNew script
    {$Region 'Lock by Component'}
    S.Add('edDocDate');
    S.Add('edCustomer');
//    S.Add('edAgent');
    S.Add('edTerms');
    S.Add('edCompanyName');
    S.Add('edCurrencyRate');
//    S.Add('edDocNo');
//    S.Add('edDocRef1');
//    S.Add('edDocNoEx');
    S.Add('edAddress1');
    S.Add('edAddress2');
    S.Add('edAddress3');
    S.Add('edAddress4');
    S.Add('edNextDocNo');
    S.Add('edDocDescription');
    S.Add('edD_Amount');
    S.Add('pnlDeposit');

    for i := 0 to S.Count - 1 do begin
     C := TWinControl(Self.FindComponent(S[i]));
     if Assigned(C) then C.Enabled := lAST;
    end;                             
    
    DBNavDetail.Visible      := lAST;
    btnMoveUp.Visible        := lAST;
    btnMoveDown.Visible      := lAST;    
    {$EndRegion}
    
    {$Region 'Lock Detail'}
    for i := 0 to gvDocDetail.ColumnCount -1 do begin
//    if (gvDocDetail.Columns[i].Caption <> 'Item Code') and
      if (gvDocDetail.Columns[i].Caption <> 'Description') and
       (gvDocDetail.Columns[i].Caption <> 'Account') then
       gvDocDetail.Columns[i].Options.Editing := lAST;   
    end;
    {$EndRegion}
    
    {$Region 'Lock by Dataset'}
    M.DataSet.FindField('Project').ReadOnly      := not lAST;
    M.DataSet.FindField('Area').ReadOnly         := not lAST;
    M.DataSet.FindField('Validity').ReadOnly     := not lAST;
    M.DataSet.FindField('DeliveryTerm').ReadOnly := not lAST;
    M.DataSet.FindField('CC').ReadOnly           := not lAST;
    M.DataSet.FindField('DocRef2').ReadOnly      := not lAST;
    M.DataSet.FindField('DocRef3').ReadOnly      := not lAST;
    M.DataSet.FindField('DocRef4').ReadOnly      := not lAST;
    M.DataSet.FindField('BranchName').ReadOnly   := not lAST;
    M.DataSet.FindField('DAddress1').ReadOnly    := not lAST;
    M.DataSet.FindField('DAddress2').ReadOnly    := not lAST;
    M.DataSet.FindField('DAddress3').ReadOnly    := not lAST;
    M.DataSet.FindField('DAddress4').ReadOnly    := not lAST;
    M.DataSet.FindField('DAttention').ReadOnly   := not lAST;
    M.DataSet.FindField('DPhone1').ReadOnly      := not lAST;
    M.DataSet.FindField('DFax1').ReadOnly        := not lAST;
    M.DataSet.FindField('Attention').ReadOnly    := not lAST;
    M.DataSet.FindField('Phone1').ReadOnly       := not lAST;
    M.DataSet.FindField('Fax1').ReadOnly         := not lAST;
    M.DataSet.FindField('UDF_Expired').ReadOnly  := not lAST;
    {$EndRegion}
  finally
    S.Free;
  end;
end.
10. Click Save button

Example 6 - Calculate Age between 2 date

Below is Example are doing following actions

  • Do Calculation UDF_Age := UDF_DateTo - UDF_DateFrom

Last Script Update : 19 Nov 2015
Level : Basic

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)
begin
  if SameText(EditingField, 'UDF_DateFrom') OR // when selecting or change UDF_DateFrom field
     SameText(EditingField, 'UDF_DateTo') then begin // when change UDF_DateTo field
     DataSet.FindField('UDF_Age').AsFloat := DataSet.FindField('UDF_DateTo').AsDateTime -
                                             DataSet.FindField('UDF_DateFrom').AsDateTime;
  end;
end.
10. Click Save button

Example 7 - Simple DropDown Selection List

Below is Example are doing following actions

  • Create Label
  • Create a drop down list at Maintain Customer for UDF_SearchKey

Last Script Update : 24 Nov 2015
Level : Advance

Steps

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Customer
03. Right Click the Customer
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 M, dsSearchKey : TDatasource;
    cdsSearchKey : TClientDataSet;
    edCompanyCategory, edSearchKey : TcxDBExtLookupComboBox;
    gvSearchKey : TcxGridDBTableView;
    grGrid1 : TcxGrid;
    lbSearchKey : TLabel;

procedure Setup;
begin
  cdsSearchKey := TClientDataset.Create(nil); 
end;

procedure TearDown;
begin
  cdsSearchKey.Free;
end;

procedure CreateTable;
begin
  with cdsSearchKey.FieldDefs do
    begin
      Add('Description', ftString,  50, False);
    end;
  cdsSearchKey.CreateDataSet;
    
  grGrid1     := TcxGrid.Create(nil);
  gvSearchKey := TcxGridDBTableView.Create(grGrid1);
  dsSearchKey := TDataSource.Create(nil);    
  with grGrid1 do begin
    Parent  := nil;
    Visible := False;
  end;

  with grGrid1.Levels.Add do begin
    Caption  := 'SearchKeyGrid';
    GridView := TcxCustomGridView(gvSearchKey);
  end;
  with gvSearchKey.CreateColumn do begin
    Caption               := 'Description';
    DataBinding.FieldName := 'Description';
    HeaderAlignmentHorz   := taCenter;
    Options.IncSearch     := False;
  end;
  dsSearchKey.DataSet          := cdsSearchKey;
  with gvSearchKey do begin
    OptionsCustomize.ColumnFiltering := False;
    DataController.DataSource   := dsSearchKey;
    OptionsView.Footer          := True;
    OptionsView.ColumnAutoWidth := True;
    OptionsView.GroupByBox      := False;
//    ApplyBestFit(nil);//Optional
  end;
  with gvSearchKey.DataController.Summary do begin
    BeginUpdate;
    try
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column   := gvSearchKey.GetColumnByFieldName('Description');
        Position := spFooter;
        Kind     := skCount;
        Format   := 'Count = #';
      end;
    finally
      EndUpdate;
    end;
  end;    
end;

procedure AddcdsSearchKey(const lDesc : String);
begin
  cdsSearchKey.Append;
  cdsSearchKey.FieldValues['Description'] := lDesc;
  cdsSearchKey.Post; 
end;

begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  edCompanyCategory := Self.FindComponent('edCompanyCategory') as TcxDBExtLookupComboBox;
  edSearchKey := TcxDBExtLookupComboBox.Create(edCompanyCategory);
  lbSearchKey := TLabel.Create(edCompanyCategory);

  CreateTable;
  AddcdsSearchKey('Salesman');
  AddcdsSearchKey('Dummy');
  AddcdsSearchKey('Normal Outlet');
                                   
  with lbSearchKey do begin // Create Label
    Parent  := edCompanyCategory.Parent;
    Left    := edCompanyCategory.Left + edCompanyCategory.Width + 35;
    Top     := edCompanyCategory.Top +3;
    Caption := 'Search key :';    
  end;
  
  with edSearchKey do begin // Create Drop List
    Parent  := edCompanyCategory.Parent;
    Left    := edCompanyCategory.Left + edCompanyCategory.Width + 100;
    Top     := edCompanyCategory.Top;
    Name    := 'edSearch';
    DataBinding.DataSource       := M;
    DataBinding.DataField        := 'UDF_SearchKey';
    Properties.View              := gvSearchKey; // Set Drop view
    Properties.KeyFieldNames     := 'Description'; // Field Name to set for DataBinding.DataField
    Properties.ListFieldItem     := gvSearchKey.GetColumnByFieldName('Description'); // Search by Description
    Properties.DropDownListStyle := lsFixedList;
    Properties.ImmediatePost     := True; 
  end;
end.
10. Click Save button

Example 8 -

  • More Coming Soon....

See also

Retrieved from "https://wiki.sql.com.my/index.php?title=DIY_Script&oldid=2328"
Last modified
This page was last edited on 25 November 2015, at 02:30.