SDK Live: Difference between revisions

From eStream Software
Line 203: Line 203:
     lDtl.FindField('RefPrice').AsFloat := 240.00;
     lDtl.FindField('RefPrice').AsFloat := 240.00;
     lDtl.Post;
     lDtl.Post;
    lBizObj.Save;
    lTime := Now - lTime;
    MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
              mtInformation, [mbOk], 0);
  finally
    lBizObj.Close;
  end;
end;
</syntaxhighlight>
====Example-SL_IV====
<syntaxhighlight lang="delphi">
procedure TfmMain.dxbb_SL_IVClick(Sender: TObject);
var lMain, lDtl, lSN, lBizObj, lBizApp : Variant;
    i : integer;
    lTime : TDateTime;
begin
  lTime := Now;
  lBizApp := CreateOleObject('SQLAcc.BizApp');
  lBizObj := lBizApp.BizObjects.Find('SL_IV');
  lMain  := lBizObj.DataSets.Find('MainDataSet');
  lDtl    := lBizObj.DataSets.Find('cdsDocDetail');
  lSN    := lBizObj.DataSets.Find('cdsSerialNumber');
  try
    lBizObj.New;
    lMain.FindField('DOCKEY').Value        := -1;
    lMain.FindField('DocNo').Value        := '--IV Test--';
    lMain.FindField('CODE').Value          := '300-A0001';
    lMain.FindField('DocDate').Value      := '28 Aug 2015';
    lMain.FindField('PostDate').Value      := '28 Aug 2015';
    lMain.FindField('TAXDATE').Value      := '28 Aug 2015';
    lMain.FindField('Description').Value  := 'Sales';
    lMain.FindField('DocAmt').AsFloat      := 1000;
    lMain.FindField('Cancelled').Value    := 'F';
    lMain.FindField('CHANGED').Value      := 'F';
    lMain.Post;
    lDtl.Append; // For Tax Inclusive = True with override Tax Amount
    lDtl.FindField('DTLKEY').Value      := -1;
    lDtl.FindField('DOCKEY').Value      := -1;
    lDtl.FindField('SEQ').Value          := 1;
    lDtl.FindField('ItemCode').Value    := 'ITEM A';
    lDtl.FindField('ACCOUNT').Value      := '500-000';
    lDtl.FindField('DESCRIPTION').Value  := 'Sales Item A';
    lDtl.FindField('QTY').AsFloat        := 1;
    lDtl.FindField('TAX').Value          := 'SR';
    lDtl.FindField('TAXINCLUSIVE').Value := 0;
    lDtl.FindField('UNITPRICE').AsFloat  := 410.37; //Must Excluding GST
    lDtl.FindField('AMOUNT').AsFloat    := 410.37; //Must Excluding GST (Qty * UnitPrice)
    lDtl.FindField('TAXAMT').AsFloat    := 24.63;
    lDtl.DisableControls; //For Tax Inclusive Only where TaxAmt is not as Calc
    try
      lDtl.FindField('TAXINCLUSIVE').Value := 1;
    finally
      lDtl.EnableControls;
    end;
    lDtl.FindField('CHANGED').Value      := 'F';
    lDtl.Post;
    lDtl.Append; // For Tax Inclusive = False with override Tax Amount
    lDtl.FindField('DTLKEY').Value      := -1;
    lDtl.FindField('DOCKEY').Value      := -1;
    lDtl.FindField('SEQ').Value          := 2;
    lDtl.FindField('ItemCode').Value    := 'ITEM B';
    lDtl.FindField('ACCOUNT').Value      := '500-000';
    lDtl.FindField('DESCRIPTION').Value  := 'Sales Item B';
    lDtl.FindField('QTY').AsFloat        := 1;
    lDtl.FindField('TAX').Value          := 'SR';
    lDtl.FindField('TAXINCLUSIVE').Value := 0;
    lDtl.FindField('UNITPRICE').AsFloat  := 94.43;
    lDtl.FindField('AMOUNT').AsFloat    := 94.43;
    lDtl.FindField('TAXAMT').AsFloat    := 5.66;
    lDtl.FindField('CHANGED').Value      := 'F';
    lDtl.Post;
    lDtl.Append; // For Serial Number
    lDtl.FindField('DTLKEY').Value      := -1;
    lDtl.FindField('DOCKEY').Value      := -1;
    lDtl.FindField('SEQ').Value          := 3;
    lDtl.FindField('ItemCode').Value    := 'SN';
    lDtl.FindField('ACCOUNT').Value      := '500-000';
    lDtl.FindField('DESCRIPTION').Value  := 'Sales Serial Number Item';
    lSN.Append;
    lSN.FindField('SERIALNUMBER').Value  := 'SN-00001';
    lSN.Post;
    lSN.Append;
    lSN.FindField('SERIALNUMBER').Value  := 'SN-00002';
    lSN.Post;
    lDtl.FindField('QTY').AsFloat        := 2;
    lDtl.FindField('TAX').Value          := 'SR';
    lDtl.FindField('TAXINCLUSIVE').Value := 0;
    lDtl.FindField('UNITPRICE').AsFloat  := 94.43;
    lDtl.FindField('TAXAMT').AsFloat    := 11.33;
    lDtl.FindField('CHANGED').Value      := 'F';
    lDtl.Post;
     lBizObj.Save;
     lBizObj.Save;



Revision as of 07:06, 12 October 2015

Introduction

This is the "Bridge" direct live linking between External Program with SQL Accounting

Pros

  1. Can Live or Batch Update.
  2. Can do action such as New, Edit & Delete.
  3. About 95% of modules is covered.
  4. Can direct Retrieve/Get Information From Connected Database.
  5. Support GST.
  6. Free of Charge for End User. (Freeware)

Cons

  1. Must had SQL Accounting installed & running.
  2. Must self do checking else error prompt will unable to “capture”.
  3. Only supported in IE for JScript

Documentation

Linking Flow

Steps

CHM File

  1. Install sqlaccounting
  2. Create Sample Data
  3. Login (username & password is Admin)
  4. Double click the SQLSDK.chm file
  5. Click Application Object | Business Objects
  6. Click the word "here"
  7. Click Yes
  8. In the list Look for (example)
- AR_IV -> Customer Invoice
- AR_CN -> Customer Credit Note
- AP_PI -> Supplier Invoice
- SL_ID -> Sales Invoice
- ST_AJ -> Stock Adjustment

External Application Linking

  1. Login SQL Accounting
  2. Login External Application
  3. Click Post to SQL Accounting button or Click Save button

Problems

With CHM File

A. Problem with opening CHM Help files?

  1. Right click the chm file & select Properties
  2. At General tab Click Unblock button.
  3. Click Apply | Ok.

B. Still can't open CHM file?

  1. Login SQL Accounting
  2. Click Tools | Options
  3. Click Register & follow wizard
  4. Exit SQL Accounting
  5. Login SQL Accounting
  6. Run CHM File

With Compiler

If you compiler had error may try add the esfw10.dll file under the SQL Accounting\bin folder

Extra Notes

Login Database Profile

  • Can use the Common.Agent.RO Report Objects to get the Profile info.
  • Then use the cdsProfile Dataset
- CompanyName Field
- Remark Field
  • Refer to How to retrieve data from Customer Invoice Listing Report? on chm
- Can by pass the Steps 5 Spool Parameters

Example External Program

Delphi

Example-AR_Customer

procedure TfmMain.dxbb_CustomerClick(Sender: TObject);
var lMain, lDtl, lBizObj, lBizApp : Variant;
    lTime : TDateTime;
begin
  lTime := Now;
  lBizApp := CreateOleObject('SQLAcc.BizApp');

  lBizObj := lBizApp.BizObjects.Find('AR_Customer');
  lMain   := lBizObj.DataSets.Find('MainDataSet');
  lDtl    := lBizObj.DataSets.Find('cdsBranch');

  try
    lBizObj.New;
    lMain.FindField('Code').value        := '300-Test';
    lMain.FindField('CompanyName').value := 'Testing 123';

    lDtl.Edit; //For 1st Branch
    lDtl.FindField('BranchName').AsString := 'BILLING';
    lDtl.FindField('Address1').AsString   := 'Address1';
    lDtl.FindField('Address2').AsString   := 'Address2';
    lDtl.FindField('Address3').AsString   := 'Address3';
    lDtl.FindField('Address4').AsString   := 'Address4';
    lDtl.FindField('Attention').AsString  := 'Attention';
    lDtl.FindField('Phone1').AsString     := 'Phone1';
    lDtl.FindField('Fax1').AsString       := 'Fax1';
    lDtl.FindField('Email').AsString      := 'EmailAddress';
    lDtl.Post;

    lDtl.Append; //For 2nd Branch
    lDtl.FindField('BranchName').AsString := 'Branch1';
    lDtl.FindField('Address1').AsString   := 'DAddress1';
    lDtl.FindField('Address2').AsString   := 'DAddress2';
    lDtl.FindField('Address3').AsString   := 'DAddress3';
    lDtl.FindField('Address4').AsString   := 'DAddress4';
    lDtl.FindField('Attention').AsString  := 'DAttention';
    lDtl.FindField('Phone1').AsString     := 'DPhone1';
    lDtl.FindField('Fax1').AsString       := 'DFax1';
    lDtl.FindField('Email').AsString      := 'DEmailAddress';
    lDtl.Post;
    lBizObj.Save;

    lTime := Now - lTime;
    MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
               mtInformation, [mbOk], 0);
  finally
    lBizObj.Close;
  end;
end;

Example-AR_PM

procedure TfmMain.dxbb_ARPMClick(Sender: TObject);
var lMain, lKO, lBizApp, lBizObj, lDocKey, V : Variant;
begin
  lBizApp := CreateOleObject('SQLAcc.BizApp');
  try
    lBizObj := lBizApp.BizObjects.Find('AR_PM');
    lMain   := lBizObj.DataSets.Find('MainDataSet');
    lKO     := lBizObj.DataSets.Find('cdsKnockOff');

    lBizObj.New;
    lMain.FindField('DOCKEY').Value        := -1;
    lMain.FindField('DocNo').Value         := '--PM Test--';
    lMain.FindField('CODE').Value          := '300-A0001';
    lMain.FindField('DocDate').Value       := '28 Aug 2016';
    lMain.FindField('PostDate').Value      := '28 Aug 2016';
    lMain.FindField('Description').Value   := 'Payment for A/c';
    lMain.FindField('PaymentMethod').Value := '320-000';
    lMain.FindField('DocAmt').AsFloat      := 1060;
    lMain.FindField('Cancelled').Value     := 'F';
    lMain.FindField('CHANGED').Value       := 'F';

    V := lBizApp.CreateOleVariantArray(2);
    V.SetItem(0, 'IV'); //Document Type is either is IV or DN for Sales & PI or SD for Purchase
    V.SetItem(1, '--IV Test--');  //Document Number

    if lKO.Locate('DocType;DocNo', V.AsOleVariant, False, False) then
      begin
        lKO.Edit;
        lKO.FindField('KOAmt').value       := 500;
        lKO.FindField('KnockOff').AsString := 'T';
        lKO.Post;
      end;

    lBizObj.Save;
    MessageDlg('AR_PM-DocNo : ''--PM Test--'' Posted...', mtInformation, [mbOK],0);
  finally
    lBizObj.Close;
  end;
end;

Example-ST_Item

procedure TfmMain.dxbb_ST_ItemClick(Sender: TObject);
var lMain, lDtl, lBizObj, lBizApp : Variant;
    lTime : TDateTime;
begin
  lTime := Now;
  lBizApp := CreateOleObject('SQLAcc.BizApp');
  lBizObj := lBizApp.BizObjects.Find('ST_ITEM');
  lMain   := lBizObj.DataSets.Find('MainDataSet');
  lDtl    := lBizObj.DataSets.Find('cdsUOM');

  try
    lBizObj.New;
    lMain.FindField('Code').value        := 'Test-1';
    lMain.FindField('Description').value := 'Testing - Description';

    lDtl.Edit; //For 1st UOM
    lDtl.FindField('UOM').value        := 'PCS';
    lDtl.FindField('Rate').AsFloat     := 1;
    lDtl.FindField('RefCost').AsFloat  := 10.20;
    lDtl.FindField('RefPrice').AsFloat := 25.00;
    lDtl.Post;

    lDtl.Append; //For 2nd UOM
    lDtl.FindField('UOM').value        := 'CTN';
    lDtl.FindField('Rate').AsFloat     := 12;
    lDtl.FindField('RefCost').AsFloat  := 102.00;
    lDtl.FindField('RefPrice').AsFloat := 240.00;
    lDtl.Post;
    lBizObj.Save;

    lTime := Now - lTime;
    MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
               mtInformation, [mbOk], 0);
  finally
    lBizObj.Close;
  end;
end;

Example-SL_IV

procedure TfmMain.dxbb_SL_IVClick(Sender: TObject);
var lMain, lDtl, lSN, lBizObj, lBizApp : Variant;
    i : integer;
    lTime : TDateTime;
begin
  lTime := Now;
  lBizApp := CreateOleObject('SQLAcc.BizApp');
  lBizObj := lBizApp.BizObjects.Find('SL_IV');
  lMain   := lBizObj.DataSets.Find('MainDataSet');
  lDtl    := lBizObj.DataSets.Find('cdsDocDetail');
  lSN     := lBizObj.DataSets.Find('cdsSerialNumber');

  try
    lBizObj.New;
    lMain.FindField('DOCKEY').Value        := -1;
    lMain.FindField('DocNo').Value         := '--IV Test--';
    lMain.FindField('CODE').Value          := '300-A0001';
    lMain.FindField('DocDate').Value       := '28 Aug 2015';
    lMain.FindField('PostDate').Value      := '28 Aug 2015';
    lMain.FindField('TAXDATE').Value       := '28 Aug 2015';
    lMain.FindField('Description').Value   := 'Sales';
    lMain.FindField('DocAmt').AsFloat      := 1000;
    lMain.FindField('Cancelled').Value     := 'F';
    lMain.FindField('CHANGED').Value       := 'F';
    lMain.Post;

    lDtl.Append; // For Tax Inclusive = True with override Tax Amount
    lDtl.FindField('DTLKEY').Value       := -1;
    lDtl.FindField('DOCKEY').Value       := -1;
    lDtl.FindField('SEQ').Value          := 1;
    lDtl.FindField('ItemCode').Value     := 'ITEM A';
    lDtl.FindField('ACCOUNT').Value      := '500-000';
    lDtl.FindField('DESCRIPTION').Value  := 'Sales Item A';
    lDtl.FindField('QTY').AsFloat        := 1;
    lDtl.FindField('TAX').Value          := 'SR';
    lDtl.FindField('TAXINCLUSIVE').Value := 0;
    lDtl.FindField('UNITPRICE').AsFloat  := 410.37; //Must Excluding GST
    lDtl.FindField('AMOUNT').AsFloat     := 410.37; //Must Excluding GST (Qty * UnitPrice)
    lDtl.FindField('TAXAMT').AsFloat     := 24.63;

    lDtl.DisableControls; //For Tax Inclusive Only where TaxAmt is not as Calc
    try
      lDtl.FindField('TAXINCLUSIVE').Value := 1;
    finally
      lDtl.EnableControls;
    end;
    lDtl.FindField('CHANGED').Value      := 'F';
    lDtl.Post;

    lDtl.Append; // For Tax Inclusive = False with override Tax Amount
    lDtl.FindField('DTLKEY').Value       := -1;
    lDtl.FindField('DOCKEY').Value       := -1;
    lDtl.FindField('SEQ').Value          := 2;
    lDtl.FindField('ItemCode').Value     := 'ITEM B';
    lDtl.FindField('ACCOUNT').Value      := '500-000';
    lDtl.FindField('DESCRIPTION').Value  := 'Sales Item B';
    lDtl.FindField('QTY').AsFloat        := 1;
    lDtl.FindField('TAX').Value          := 'SR';
    lDtl.FindField('TAXINCLUSIVE').Value := 0;
    lDtl.FindField('UNITPRICE').AsFloat  := 94.43;
    lDtl.FindField('AMOUNT').AsFloat     := 94.43;
    lDtl.FindField('TAXAMT').AsFloat     := 5.66;
    lDtl.FindField('CHANGED').Value      := 'F';
    lDtl.Post;

    lDtl.Append; // For Serial Number
    lDtl.FindField('DTLKEY').Value       := -1;
    lDtl.FindField('DOCKEY').Value       := -1;
    lDtl.FindField('SEQ').Value          := 3;
    lDtl.FindField('ItemCode').Value     := 'SN';
    lDtl.FindField('ACCOUNT').Value      := '500-000';
    lDtl.FindField('DESCRIPTION').Value  := 'Sales Serial Number Item';

    lSN.Append;
    lSN.FindField('SERIALNUMBER').Value  := 'SN-00001';
    lSN.Post;

    lSN.Append;
    lSN.FindField('SERIALNUMBER').Value  := 'SN-00002';
    lSN.Post;

    lDtl.FindField('QTY').AsFloat        := 2;
    lDtl.FindField('TAX').Value          := 'SR';
    lDtl.FindField('TAXINCLUSIVE').Value := 0;
    lDtl.FindField('UNITPRICE').AsFloat  := 94.43;
    lDtl.FindField('TAXAMT').AsFloat     := 11.33;
    lDtl.FindField('CHANGED').Value      := 'F';
    lDtl.Post;

    lBizObj.Save;

    lTime := Now - lTime;
    MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
               mtInformation, [mbOk], 0);
  finally
    lBizObj.Close;
  end;
end;

See also

  1. SQL Accounting Linking
  2. SQL XML Import
  3. SQL Text Import