SQL Control Center: Difference between revisions

From eStream Software
No edit summary
 
(9 intermediate revisions by the same user not shown)
Line 3: Line 3:
==Introduction==
==Introduction==
This Additional Module where user can self  
This Additional Module where user can self  
* Create new field (DIY Fields Module)
* Create new field ([[DIY Fields]] Module)
* Modified the SQL Accounting (DIY Script Module)
* Modified the SQL Accounting ([[DIY Script]] Module)


==DIY Fields==
==See also==
* Available Data Field Type
* [[Maintain DIY]]
:: - Boolean
* Others [[Customisation]]
:: - Currency
:: - Date
:: - Float
:: - Integer
:: - Memo
:: - Picture
:: - String
 
* For Explanation for Each Data Type may refer to http://www.w3schools.com/sql/sql_datatypes_general.asp
 
:::----------------------------------------------------------------------------------------------------------------------------------------------------
::::{|
|-
| [[File:Template.Warning-01.jpg|80px]]||
* 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
::[[File:DIYField-01.jpg|700px]]
 
::{| class="wikitable"
|-
! 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====
::[[File:DIYField-02.jpg|700px]]
:01. At the left panel look for '''Sales Invoice | Header Fields'''<br />
:02. Click the '''+''' Button on top to add New fields as above picture<br />
:03. Click '''Save''' button<br />
 
====Design Layout====
They are 2 ways to creating the Layout for new fields added.<br />
:01. Right Click the '''Sales Invoice'''
::[[File:DIYField-03.jpg]]
=====Advance Form Design=====
This mode is only for the user who known how to use the Delphi.<br />
======Pros======
* Freely adjust & place the component.
======Cons======
* Not Easy to design
* Not support Date & Picture Data Type<br /><br />
 
:02. Select '''New Advance Form Design'''
::[[File: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
::[[File:DIYField-05.jpg|650px]]<br />
 
::[[File:DIYField-06.jpg]]
::* This '''Standard''' component is just for Fixed labeling or decoration
::* The detail of each button can be refer to http://www.delphibasics.co.uk/Article.asp?Name=Standard
 
::[[File: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)
 
::{| class="wikitable"
|-
! 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.
|}
 
:::----------------------------------------------------------------------------------------------------------------------------------------------------
::::{|
|-
| [[File:Template.Tips-01.jpg|60px]]||
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
::[[File: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
::[[File: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.<br /><br />
 
:02. Select '''New Quick Form Design'''
::[[File: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
::[[File:DIYField-11.jpg|700px]]<br />
: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
::[[File:DIYField-12.jpg|650px]]<br />
 
:01. Drag the UDF field from the '''Customization''' Dialog<br />
: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.<br />
The Language use is '''Pascal'''<br />
Available Action/Event
::{| class="wikitable"
|-
! 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.
|}
 
:::----------------------------------------------------------------------------------------------------------------------------------------------------
::::{|
|-
| [[File:Template.Tips-01.jpg|60px]]||
Commonly we only '''OnOpen''', '''OnBeforeSave''' & '''OnGridColumnValueChanged''' Event
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
 
===Script Example 1===
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'''
::[[File:DIYField-03.jpg]]
:02. Select '''New Event'''
::[[File: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
::[[File:DIYScript-02.jpg|700px]]<br />
:07. Copy below script & paste to the Right Panel (Script Section)
<pre>
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.
</pre>
:08. Click '''Save''' button
 
:::----------------------------------------------------------------------------------------------------------------------------------------------------
::::{|
|-
| [[File:Template.Warning-01.jpg|80px]]||
* Avoid update below field in same time as will cause unlimited looping updating each other
: - Qty
: - UnitPrice
: - TaxAmt
: - Amount
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
 
===Script Example 2===
* More Coming Soon....

Latest revision as of 10:48, 29 October 2015

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

Introduction

This Additional Module where user can self

See also