SQL Control Center: Difference between revisions

From eStream Software
Line 280: Line 280:
   Result := FComServer;
   Result := FComServer;
end;
end;
procedure GetStockInfo;
procedure GetStockInfo;
var lSQL, lCode  : String;
var lSQL, lCode  : String;
Line 291: Line 290:


     cdsData.Data := ComServer.DBManager.Execute(lSQL);
     cdsData.Data := ComServer.DBManager.Execute(lSQL);
   finally
   finally
     lBizObj    := null;
     lBizObj    := null;
Line 305: Line 303:
       DataSet.FindField('UDF_Length').AsFloat := cdsData.FindField('UDF_Length').AsFloat;
       DataSet.FindField('UDF_Length').AsFloat := cdsData.FindField('UDF_Length').AsFloat;
       DataSet.FindField('UDF_Width').AsFloat  := cdsData.FindField('UDF_Width').AsFloat;
       DataSet.FindField('UDF_Width').AsFloat  := cdsData.FindField('UDF_Width').AsFloat;
       DataSet.FindField('Qty').AsFloat        := DataSet.FindField('UDF_Length').AsFloat * DataSet.FindField('UDF_Width').AsFloat *
       DataSet.FindField('Qty').AsFloat        := DataSet.FindField('UDF_Length').AsFloat *
                                                DataSet.FindField('UDF_Width').AsFloat *
                                                 DataSet.FindField('UDF_Rate').AsFloat;
                                                 DataSet.FindField('UDF_Rate').AsFloat;
     finally
     finally

Revision as of 03:32, 25 September 2015

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

Introduction

This Additional Module where user can self

  • Create new field (DIY Fields Module)
  • Modified the SQL Accounting (DIY Script Module)

DIY Fields

  • Available Data Field Type
- Boolean
- Currency
- Date
- Float
- Integer
- Memo
- Picture
- String
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg
  • Avoid use Picture data if can as it will cause your database size increase very fast.
  • Only String data can increase the field size so plan properly before add the field.
  • User can't change from 1 data type to another once it save.
----------------------------------------------------------------------------------------------------------------------------------------------------
  • User can create DIY Fields either at Header Fields or Item Fields
  • In each Table had the below interface
DIYField-01.jpg
Name Description
Name
  • Field name
  • Only Alphanumeric & no spacing
Data Type
  • Type of Field
Size
  • The Field Size
  • Only applicable for Float, Integer & String Data Type
Sub Size
  • The Field Sub Size normally is the Decimal Point
  • Only applicable for Float Data Type
Caption
  • Field name to be shown at user interface.
Required
  • To tell the system the current field MUST not empty
Default Value
  • Default Value to insert on New record
Display Format
  • Set Display format for Float, Currency & Integer Data Type
Load
  • Reload again the Script from the Database (Only for DIY Script)
Load From File
  • Load Script/Fields from the Text File.
  • Be careful as system will delete the existing field before load the file to the system
Save
  • Save Current Script/Field
  • Don't do Delete, Rename & Insert in same time (Only for DIY Fields)
Save To File
  • Save Current Script/Fields to Text File
+ Button
  • Add New Field
- Button
  • Delete Selected Field

Header Fields

Below is the example for Sales Invoice

Add New Fields

DIYField-02.jpg
01. At the left panel look for Sales Invoice | Header Fields
02. Click the + Button on top to add New fields as above picture
03. Click Save button

Design Layout

They are 2 ways to creating the Layout for new fields added.

01. Right Click the Sales Invoice
DIYField-03.jpg
Advance Form Design

This mode is only for the user who known how to use the Delphi.

Pros
  • Freely adjust & place the component.
Cons
  • Not Easy to design
  • Not support Date Data Type

02. Select New Advance Form Design
DIYField-04.jpg
03. Enter the name (eg Freight Info) this will shown in the Sales Invoice Data Entry
04. Click OK
05. Click the Freight Info (name create at Step 3 above) on the left panel
DIYField-05.jpg
DIYField-06.jpg
DIYField-07.jpg
  • This Data Controls component is use for Variable data which can be use at Data Entry
  • Below is the detail of each button (From Left)
Component Purpose
DBText Data-aware label that can display a field from a currently active record.
DBEdit Data-aware edit box that can display or edit a field from a currently active record.
DBMemo Data-aware memo box that can display or edit text BLOB data from a currently active record.
DBImage Data-aware image box that can display, cut, or paste bitmapped BLOB images to and from a currently active record.
DBListBox Data-aware list box that can display values from a column in a table.
DBCheckBox Data-aware check box that can display or edit a Boolean data field from a currently active record.
DBRichEdit Data-aware Rich Text Editor that can display or edit text BLOB data from a currently active record.
cxDBSpinEdit Data-aware Edit box that can display or edit integer or Float data from a currently active record.
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg

Normally we only use

Standard Tab component
- Label - For Labeling each field
Data Controls Tab component
- DBEdit - For String Data
- DBRichEdit - For Memo Data
- cxDBSpinEdit - For Currency, Float or Integer Data
----------------------------------------------------------------------------------------------------------------------------------------------------
06. Click the Standard Tab & select Label button place on thee right panel
07. In the Object Inspector look for
  • Caption & set to the Caption you like (eg Container Number)
  • Font & set the Size to 12 or any size you prefer
DIYField-08.jpg
08. Click the Data Controls Tab & select DBEdit button place on thee right panel
09. In the Object Inspector look for
  • DataField & set to the UDF field name (normally is UDF_FieldName) (eg UDF_ContainerNo)
  • Font & set the Size to 12 or any size you prefer
DIYField-09.jpg
10. Continue repeat Step 6 till Step 9 for rest of the UDF field.
11. Click Save button after done.
Quick Form Design (Recommended)

This mode is for the user who don't known how to use the Delphi.

Pros
  • Easy to Design
Cons
  • Unable to freely adjust & place the component.
  • Not support Rich Text Editor.

02. Select New Quick Form Design
DIYField-10.jpg
03. Enter the name (eg Freight Info) this will shown in the Sales Invoice Data Entry
04. Click OK
05. Click the Freight Info (name create at Step 3 above) on the left panel
DIYField-11.jpg
06. Click Customise button.
07. Drag the UDF Field from the Available Item dialog box & drop to Right Panel
08. Continue repeat Step 7 for rest of the UDF field.
09. Click Save button after done.

Item Fields

There is no layout need to be design for Item Field UDF. You can drag out all the UDF field in the Field Chooser in the data Entry

DIYField-12.jpg
01. Drag the UDF field from the Customization Dialog
02. Drop it the column you wanted to shown (eg In between Description Column & Qty Column)

DIY Script

This is additional Module which allow user to override or customise the SQL System.
The Language use is Pascal
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

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

Performing Script 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


01. Right Click the Sales Invoice
DIYField-03.jpg
02. Select New Event
DIYScript-01.jpg
03. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
04. Select OnGridColumnValueChanged for Event field
05. Click OK
06. Click the Calc (name create at Step 3 above) on the left panel
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);
  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; 
      DataSet.FindField('UDF_Length').AsFloat := cdsData.FindField('UDF_Length').AsFloat;
      DataSet.FindField('UDF_Width').AsFloat  := cdsData.FindField('UDF_Width').AsFloat;
      DataSet.FindField('Qty').AsFloat        := DataSet.FindField('UDF_Length').AsFloat *
                                                 DataSet.FindField('UDF_Width').AsFloat *
                                                 DataSet.FindField('UDF_Rate').AsFloat;
    finally
      cdsData.Free;
    end;
  end;
end.
  • More Coming Soon....