(→Steps) |
|||
Line 242: | Line 242: | ||
===Example 4 - Get Transfer Information=== | ===Example 4 - Get Transfer Information=== | ||
Below is Example are doing following action | Below is Example are doing following action | ||
* Get the '''Invoice number, Date & Qty''' which had transferred to '''Invoice''' in the Quotation | * 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 | Last Script Update : 30 Oct 2015 | ||
Line 392: | Line 392: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
:10. Click '''Save''' button | :10. Click '''Save''' button | ||
===Example 5 - Only Enable Some of the field on Click Edit by User=== | ===Example 5 - Only Enable Some of the field on Click Edit by User=== | ||
Below is Example are doing following action in Sales Cash Sales | Below is Example are doing following action in Sales Cash Sales |
Revision as of 05:45, 4 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.
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
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
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
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 and shown it below the Browse button
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 - 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
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
- More Coming Soon....
See also
- DIY Fields
- Maintain DIY
- Others Customisation