Introduction
This is the "Bridge" direct live linking between External Program with SQL Accounting
Pros
- Can Live or Batch Update.
- Can do action such as New, Edit & Delete.
- About 95% of modules is covered.
- Can direct Retrieve/Get Information From Connected Database.
- Able to do Automation (i.e. auto login & update data).
- Support GST.
- Free of Charge (Freeware) for End User on SDK Linking.
Cons
- Must had programming knowledge.
- Must had SQL Accounting installed & running.
- Must self do checking else error prompt will unable to “capture”.
- Only supported in IE for JScript
Documentation
Linking Flow
- Highlight in Yellow Color is the Application/Function you need to Develop/Create.
Example 1 - POS System
Example 2 - Cloud System
Steps
CHM File
- Install sqlaccounting
- Create Sample Data
- Login (username & password is Admin)
- Double click the SQLSDK.chm file
- Click Application Object | Business Objects
- Click the word "here"
- Click Yes
- In the list Look for (example)
- - AR_IV -> Customer Invoice
- - AR_CN -> Customer Credit Note
- - AP_PI -> Supplier Invoice
- - SL_IV -> Sales Invoice
- - PH_PI -> Purchase Invoice
- - ST_AJ -> Stock Adjustment
External Application Linking
- Login SQL Accounting
- Login External Application
- Click Post to SQL Accounting button or Click Save button from External Application.
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Example External Program
VB. Net
VB Script
Example-Get Company Profile
Company Profile Script |
---|
'Copy below script & paste to notepad & name it as eg Common.Agent.RO.vbs
Call GetCoInfo
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetCoInfo
Dim ComServer, RptObject, lDataSet
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("Common.Agent.RO")
'Step 3: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsProfile")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox "Company Name : " & lDataSet.FindField("CompanyName").AsString
MsgBox "Remark : " & lDataSet.FindField("Remark").AsString
MsgBox "ROC No : " & lDataSet.FindField("RegisterNo").AsString
MsgBox "GST No : " & lDataSet.FindField("GSTNo").AsString
MsgBox "Address 1 : " & lDataSet.FindField("Address1").AsString
MsgBox "Address 2 : " & lDataSet.FindField("Address2").AsString
MsgBox "Address 3 : " & lDataSet.FindField("Address3").AsString
MsgBox "Address 4 : " & lDataSet.FindField("Address4").AsString
MsgBox "Attention : " & lDataSet.FindField("Attention").AsString
MsgBox "Phone : " & lDataSet.FindField("Phone1").AsString
MsgBox "Fax : " & lDataSet.FindField("Fax1").AsString
MsgBox "E-Mail : " & lDataSet.FindField("EMail").AsString
lDataSet.Next
Wend
End Function
|
Example-GL_JE
GL_JE Script |
---|
'Copy below script & paste to notepad & name it as eg GL_JE.vbs
Call InsertGLJE
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function InsertGLJE
Dim ComServer, BizObject, lDataSet, lDataSet2, lDate
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\estream\SQLAccounting\Share\Default.DCF", "ACC-0020.FDB"
END IF
'Step 2: Find and Create the Biz Objects
Set BizObject = ComServer.BizObjects.Find("GL_JE")
'Step 3: Set Dataset
Set lMainDataSet = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data
Set lDetailDataSet = BizObject.DataSets.Find("cdsDocDetail") 'lDetailDataSet contains detail data
'Step 4 : Insert Data - Master
lDate = CDate("January 23, 2017")
BizObject.New
lMainDataSet.FindField("DocKey").value = -1
lMainDataSet.FindField("DocNo").value = "--JV Test--"
lMainDataSet.FindField("DocDate").value = lDate
lMainDataSet.FindField("PostDate").value = lDate
lMainDataSet.FindField("Description").value = "testing desc header"
lMainDataSet.Post
'Step 5: Insert Data - Detail
'For Tax Inclusive
lDetailDataSet.Append
lDetailDataSet.FindField("DtlKey").value = -1
lDetailDataSet.FindField("DocKey").value = -1
lDetailDataSet.FindField("SEQ").value = 1
lDetailDataSet.FindField("Code").value = "610-1000"
lDetailDataSet.FindField("Project").value = "----"
lDetailDataSet.FindField("Description").value = "testing desc1"
lDetailDataSet.FindField("Tax").value = "TX"
'lDetailDataSet.FindField("LOCALTAXAMT").value = 17.32 //For JE you can't override (i.e. system auto calc)
lDetailDataSet.FindField("TaxInclusive").Value = 1
lDetailDataSet.FindField("LocalDR").value = 306 '//Including GST if TAXINCLUSIVE = 1
lDetailDataSet.FindField("DR").value = 306 '//If wanted DR <> LocalDR
lDetailDataSet.FindField("Changed").value = "F"
'For Tax Exclusive
lDetailDataSet.Append
lDetailDataSet.FindField("DtlKey").value = -1
lDetailDataSet.FindField("DocKey").value = -1
lDetailDataSet.FindField("SEQ").value = 2
lDetailDataSet.FindField("Code").value = "605-200"
lDetailDataSet.FindField("Project").value = "----"
lDetailDataSet.FindField("Description").value = "testing desc2"
lDetailDataSet.FindField("Tax").value = "TX"
lDetailDataSet.FindField("TaxInclusive").value = 0
lDetailDataSet.FindField("LocalCR").value = 100
lDetailDataSet.FindField("Changed").value = "F"
lDetailDataSet.Post
'For No GST
lDetailDataSet.Append
lDetailDataSet.FindField("DtlKey").value = -1
lDetailDataSet.FindField("DocKey").value = -1
lDetailDataSet.FindField("SEQ").value = 3
lDetailDataSet.FindField("Code").value = "610-002"
lDetailDataSet.FindField("Project").value = "----"
lDetailDataSet.FindField("Description").value = "testing desc3"
lDetailDataSet.FindField("Tax").value = ""
lDetailDataSet.FindField("TaxInclusive").value = 0
lDetailDataSet.FindField("LocalCR").value = 200
lDetailDataSet.FindField("Changed").value = "F"
lDetailDataSet.Post
'Step 6: Save Document
BizObject.Save
BizObject.Close
MsgBox "Done"
End Function
|
Example-AR_IV
AR_IV Script |
---|
'Copy below script & paste to notepad & name it as eg AR_IV.vbs
Call InsertARIV
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function InsertARIV
Dim ComServer, BizObject, lDataSet, lDataSet2, lDate
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\estream\SQLAccounting\Share\Default.DCF", "ACC-0020.FDB"
END IF
'Step 2: Find and Create the Biz Objects
Set BizObject = ComServer.BizObjects.Find("AR_IV")
'Step 3: Set Dataset
Set lMainDataSet = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data
Set lDetailDataSet = BizObject.DataSets.Find("cdsDocDetail") 'lDetailDataSet contains detail data
'Step 4 : Insert Data - Master
lDate = CDate("January 1, 2016")
BizObject.New
lMainDataSet.FindField("DocKey").value = -1
lMainDataSet.FindField("DocNo").value = "--IV Test--"
lMainDataSet.FindField("DocDate").value = lDate
lMainDataSet.FindField("PostDate").value = lDate
lMainDataSet.FindField("Code").value = "300-A0003"
lMainDataSet.FindField("Description").value = "Sales"
'Step 5: Insert Data - Detail
'For Tax Inclusive = True with override Tax Amount
lDetailDataSet.Append
lDetailDataSet.FindField("DtlKey").value = -1
lDetailDataSet.FindField("DocKey").value = -1
lDetailDataSet.FindField("Account").value = "500-000"
lDetailDataSet.FindField("Description").value = "Sales Item A"
lDetailDataSet.FindField("Tax").value = "SR"
lDetailDataSet.FindField("TaxInclusive").value = 0
lDetailDataSet.FindField("Amount").value = 410.37
lDetailDataSet.FindField("TaxAmt").value = 24.63
lDetailDataSet.DisableControls
lDetailDataSet.FindField("TaxInclusive").value = 1
lDetailDataSet.EnableControls
lDetailDataSet.FindField("Changed").value = "F"
lDetailDataSet.Post
'For Tax Inclusive = False with override Tax Amount
lDetailDataSet.Append
lDetailDataSet.FindField("DtlKey").value = -1
lDetailDataSet.FindField("DocKey").value = -1
lDetailDataSet.FindField("Account").value = "500-000"
lDetailDataSet.FindField("Description").value = "Sales Item B"
lDetailDataSet.FindField("Tax").value = "SR"
lDetailDataSet.FindField("TaxInclusive").value = 0
lDetailDataSet.FindField("Amount").value = 94.43
lDetailDataSet.FindField("TaxAmt").value = 5.66
lDetailDataSet.FindField("Changed").value = "F"
lDetailDataSet.Post
'Step 6: Save Document
BizObject.Save
BizObject.Close
MsgBox "Done"
End Function
|
Example-AR_PM
AR_PM Script |
---|
'Copy below script & paste to notepad & name it as eg AR_PM.vbs
Call PostData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function PostData
Dim ComServer, BizObject, lIVNo, v(1), lDate
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\estream\SQLAccounting\Share\Default.DCF", "ACC-0020.FDB"
END IF
'Step 2: Find and Create the Biz Objects
Set BizObject = ComServer.BizObjects.Find("AR_PM")
'Step 3: Set Dataset
Set lMain = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data
Set lDetailDataSet = BizObject.DataSets.Find("cdsKnockOff") 'lDetailDataSet contains detail data
'Step 4 : Posting
lDate = CDate("January 23, 2017")
BizObject.New
lMain.FindField("DOCKEY").Value = -1
lMain.FindField("DocNo").AsString = "--PM Test--"
lMain.FindField("CODE").AsString = "300-A001"
lMain.FindField("DocDate").Value = lDate
lMain.FindField("PostDate").Value = lDate
lMain.FindField("Description").AsString = "Payment for A/c"
lMain.FindField("PaymentMethod").AsString = "310-000"
lMain.FindField("DocAmt").AsFloat = 65.57
lMain.FindField("Cancelled").AsString = "F"
'Step 5: Knock Off IV
lIVNO = "IV-16550"
v(0) = "IV"
v(1) = lIVNO
if (lDetailDataSet.Locate("DocType;DocNo", v, false, false)) then
lDetailDataSet.Edit
lDetailDataSet.FindField("KOAmt").value = 65.57
lDetailDataSet.FindField("KnockOff").AsString = "T"
lDetailDataSet.Post
End IF
'Step 6: Save Document
BizObject.Save
BizObject.Close
MsgBox "Done"
'END IF
End Function
|
Example-AR_PM-Edit
AR_PM Edit Script |
---|
'Copy below script & paste to notepad & name it as eg AR_IV.vbs
Call EditARPM
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function EditARPM
Dim ComServer, BizObject, lDataSet, lDataSet2, lDocKey, lDocNo, lIVNo, v(1)
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\estream\SQLAccounting\Share\Default.DCF", "ACC-0020.FDB"
END IF
'Step 2: Find and Create the Biz Objects
Set BizObject = ComServer.BizObjects.Find("AR_PM")
'Step 3: Set Dataset
Set lMainDataSet = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data
Set lDetailDataSet = BizObject.DataSets.Find("cdsKnockOff") 'lDetailDataSet contains detail data
'Step 4 : Find OR Number
lDocNo = "OR-00022"
lDocKey = BizObject.FindKeyByRef("DocNo", lDocNo)
BizObject.Params.Find("DocKey").Value = lDocKey
If not IsNull(lDocKey) Then
BizObject.Open
BizObject.Edit
lMainDataSet.Edit
lMainDataSet.FindField("Description").AsString = "testing"
'Step 5: Knock Off IV
lIVNO = "IV-00006"
v(0) = "IV"
v(1) = lIVNO
if (lDetailDataSet.Locate("DocType;DocNo", v, false, false)) then
lDetailDataSet.Edit
lDetailDataSet.FindField("KOAmt").value = 50.12
lDetailDataSet.FindField("KnockOff").AsString = "T"
lDetailDataSet.Post
MsgBox "yes"
End IF
'Step 6: Save Document
BizObject.Save
BizObject.Close
MsgBox "Done"
END IF
End Function
|
Example-Customer.RO
Customer.RO Report Object Script |
---|
'Copy below script & paste to notepad & name it as eg customer_ro.vbs
Call GetCustomerData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetCustomerData
Dim ComServer, RptObject, lDataSet, lDataSet2, lDateFrom, lDateTo
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("Customer.RO")
'Step 3: Spool parameters
RptObject.Params.Find("AllAgent").Value = true
RptObject.Params.Find("AllArea").Value = true
RptObject.Params.Find("AllCompany").Value = false
RptObject.Params.Find("AllCompanyCategory").Value = true
RptObject.Params.Find("AllCurrency").Value = true
RptObject.Params.Find("AllTerms").Value = true
RptObject.Params.Find("SelectDate").Value = true
RptObject.Params.Find("PrintActive").Value = true
RptObject.Params.Find("PrintInactive").Value = false
RptObject.Params.Find("PrintPending").Value = false
RptObject.Params.Find("PrintProspect").Value = false
RptObject.Params.Find("PrintSuspend").Value = false
lDateFrom = CDate("January 1, 2000")
lDateTo = CDate("December 31, 2014")
RptObject.Params.Find("DateFrom").Value = lDateFrom
RptObject.Params.Find("DateTo").Value = lDateTo
RptObject.Params.Find("CompanyData").Value = "300-A0003"
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsBranch")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox lDataSet.FindField("Code").AsString & " " & lDataSet.FindField("CompanyName").AsString
lDataSet2.First
While (not lDataSet2.eof)
MsgBox lDataSet2.FindField("Address1").AsString
lDataSet2.Next
Wend
lDataSet.Next
Wend
End Function
|
Example-Customer.Statement.RO
Customer.Statement.RO Report Object Script |
---|
'Copy below script & paste to notepad & name it as eg customer_statement_ro.vbs
Call GetCustStatementData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetCustStatementData
Const Quote = """"
Dim ComServer, RptObject, lDataSet, lDataSet2, lDateFrom, lDateTo, lAgeData
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("Customer.Statement.RO")
'Step 3: Spool parameters
lAgeData = "<?xml version=" & Quote & "1.0" & Quote & " standalone=" & Quote & "yes" & Quote & "?> <DATAPACKET Version=" & Quote & "2.0" & Quote & "><METADATA><FIELDS>" &_
"<FIELD attrname=" & Quote & "ColumnNo" & Quote & " fieldtype=" & Quote & "i4" & Quote & " required=" & Quote & "true" & Quote & "/><FIELD attrname=" & Quote & "ColumnType" & Quote & " fieldtype=" & Quote & "string" & Quote & " WIDTH=" & Quote & "1" & Quote & "/>" &_
"<FIELD attrname=" & Quote & "Param1" & Quote & " fieldtype=" & Quote & "i4" & Quote & " required=" & Quote & "true" & Quote & "/><FIELD attrname=" & Quote & "Param2" & Quote & " fieldtype=" & Quote & "i4" & Quote & " required=" & Quote & "true" & Quote & "/>" &_
"<FIELD attrname=" & Quote & "IsLocal" & Quote & " fieldtype=" & Quote & "boolean" & Quote & "/><FIELD attrname=" & Quote & "HeaderScript" & Quote & " fieldtype=" & Quote & "bin.hex" & Quote & " SUBTYPE=" & Quote & "Text" & Quote & " WIDTH=" & Quote & "1" & Quote & "/>" &_
"</FIELDS><PARAMS/></METADATA><ROWDATA><ROW ColumnNo=" & Quote & "0" & Quote & " ColumnType=" & Quote & "" & Quote & " Param1=" & Quote & "0" & Quote & " Param2=" & Quote & "0" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "1" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "0" & Quote & " Param2=" & Quote & "0" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= 'Current Mth'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "2" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-1" & Quote & " Param2=" & Quote & "-1" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '1 Months'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "3" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-2" & Quote & " Param2=" & Quote & "-2" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '2 Months'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "4" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-3" & Quote & " Param2=" & Quote & "-3" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '3 Months'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "5" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-4" & Quote & " Param2=" & Quote & "-4" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '4 Months'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "6" & Quote & " ColumnType=" & Quote & "B" & Quote & " Param1=" & Quote & "-999999" & Quote & " Param2=" & Quote & "-5" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '5 Month & above'
end;" & Quote & "/>" &_
"</ROWDATA></DATAPACKET>"
' RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is True
RptObject.Params.Find("AgingData").Value = lAgeData 'Fixed
RptObject.Params.Find("AgingOn").Value = "I" 'Fixed
RptObject.Params.Find("AllAgent").Value = true
RptObject.Params.Find("AllArea").Value = true
RptObject.Params.Find("AllCompany").Value = false
RptObject.Params.Find("AllCompanyCategory").Value = true
RptObject.Params.Find("AllControlAccount").Value = true
RptObject.Params.Find("AllCurrency").Value = true
RptObject.Params.Find("AllDocProject").Value = true
' RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is True
' RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is True
RptObject.Params.Find("CompanyData").Value = "300-A0001"& vbCRLF & "300-C0001" 'Filter by Customer Code 300-A0001 & 300-C0001
' RptObject.Params.Find("ControlAccountData").Value = 'Not use if AllControlAccount is True
' RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is True
lDateFrom = CDate("August 1, 2016")
lDateTo = CDate("August 31, 2016")
RptObject.Params.Find("DateFrom").Value = lDateFrom
RptObject.Params.Find("DateTo").Value = lDateTo
' RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is True
RptObject.Params.Find("IncludeZeroBalance").Value = false
RptObject.Params.Find("SelectDate").Value = true
RptObject.Params.Find("SortBy").Value = "CompanyCategory;Code;CompanyName;Agent;Area;CurrencyCode;ControlAccount"
RptObject.Params.Find("StatementDate").Value = lDateTo ' Norm same as DateTo
RptObject.Params.Find("StatementType").Value = "O" 'O = Open Item, B = B/F
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsDocument")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox lDataSet.FindField("Code").AsString & " " & lDataSet.FindField("CompanyName").AsString & " " & lDataSet.FindField("Balance").AsString
lDataSet2.First
While (not lDataSet2.eof)
MsgBox lDataSet2.FindField("DocNo").AsString
lDataSet2.Next
Wend
lDataSet.Next
Wend
End Function
|
Example-GL.CB.RO
GL.CB.RO Report Object Script |
---|
'Copy below script & paste to notepad & name it as eg GL.CB.RO.vbs
Call GetCLCBData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetCLCBData
Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, lDateFrom, lDateTo
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("GL.CB.RO")
'Step 3: Spool parameters
RptObject.Params.Find("AllCurrency").Value = true
RptObject.Params.Find("AllDocProject").Value = true
RptObject.Params.Find("AllDocument").Value = true
RptObject.Params.Find("AllItemProject").Value = true
RptObject.Params.Find("AllItemArea").Value = true
RptObject.Params.Find("AllItemAgent").Value = true
RptObject.Params.Find("AllDocAgent").Value = true
RptObject.Params.Find("AllDocArea").Value = true
RptObject.Params.Find("AllPaymentMethod").Value = false
'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true
lDateFrom = CDate("October 27, 2016")
lDateTo = CDate("December 31, 2016")
'For Post Date From & Date To'
RptObject.Params.Find("DateFrom").Value = lDateFrom
RptObject.Params.Find("DateTo").Value = lDateTo
'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true
'RptObject.Params.Find("DocAreaData").Value = 'Not use if AllDocArea is true
'RptObject.Params.Find("DocAgentData").Value = 'Not use if AllDocAgent is true
'RptObject.Params.Find("ItemAgentData").Value = 'Not use if AllItemAgent is true
RptObject.Params.Find("DocType").Value = "PV" 'PV for Payment Voucher & OR for Official Receipt
'RptObject.Params.Find("DocumentData").Value = 'Not use if AllDocument is true
'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data
RptObject.Params.Find("IncludeCancelled").Value = false
'RptObject.Params.Find("ItemAreaData").Value = 'Not use if AllItemArea is true
'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true
RptObject.Params.Find("PaymentMethodData").Value = "310-000"
RptObject.Params.Find("PrintDocumentStyle").Value = false
RptObject.Params.Find("SelectDate").Value = true 'For Post Date
'RptObject.Params.Find("SelectDocDate").Value = 'For Doc Date
RptObject.Params.Find("SortBy").Value = "PostDate;DocNo"
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsDetail")
Set lDataSet3 = RptObject.DataSets.Find("cdsTax")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox lDataSet.FindField("DocNo").AsString & " " & lDataSet.FindField("Description").AsString
lDataSet2.First
While (not lDataSet2.eof)
MsgBox lDataSet2.FindField("Code").AsString & " " & lDataSet2.FindField("Description").AsString
lDataSet2.Next
Wend
lDataSet.Next
Wend
'For GST List
MsgBox "GST Code Count " & lDataSet3.RecordCount
lDataSet3.First
While (not lDataSet3.eof)
MsgBox lDataSet3.FindField("Code").AsString
lDataSet3.Next
Wend
End Function
|
Example-AR_Customer-Edit
AR_Customer - Edit Script |
---|
'Copy below script & paste to notepad & name it as eg AR_Customer.vbs
Call EditARCustomer
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function EditARCustomer
Dim ComServer, BizObject, lDataSet, lDataSet2
'Step 1 Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C\estream\SQLAccounting\Share\Default.DCF", "ACC-0020.FDB"
END IF
'Step 2 Find and Create the Biz Objects
Set BizObject = ComServer.BizObjects.Find("AR_Customer")
'Step 3 Set Dataset
Set lMainDataSet = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data
Set lDetailDataSet = BizObject.DataSets.Find("cdsBranch") 'lDetailDataSet contains detail data
'Step 4 Find Customer Code
BizObject.Params.Find("Code").Value = "300-Test"
BizObject.Open
BizObject.Edit
lMainDataSet.Edit
lMainDataSet.FindField("CompanyName").value = "Testing 123-Changed"
'Step 5 For 1st Branch
v = "BILLING"
if (lDetailDataSet.Locate("BranchName", v, false, false)) then
lDetailDataSet.Edit
lDetailDataSet.FindField("Address1").AsString = "Address1-Changed"
lDetailDataSet.FindField("Address2").AsString = "Address2-Changed"
lDetailDataSet.FindField("Address3").AsString = "Address3-Changed"
lDetailDataSet.FindField("Address4").AsString = "Address4-Changed"
lDetailDataSet.FindField("Attention").AsString = "Attention-Changed"
lDetailDataSet.FindField("Phone1").AsString = "Phone1-Changed"
lDetailDataSet.FindField("Fax1").AsString = "Fax1-Changed"
lDetailDataSet.FindField("Email").AsString = "EmailAddress-Changed"
lDetailDataSet.Post
end if
'Step 5 For 2nd Branch
v = "Branch1"
if (lDetailDataSet.Locate("BranchName", v, false, false)) then
lDetailDataSet.Edit
lDetailDataSet.FindField("Address1").AsString = "DAddress1-Changed"
lDetailDataSet.FindField("Address2").AsString = "DAddress2-Changed"
lDetailDataSet.FindField("Address3").AsString = "DAddress3-Changed"
lDetailDataSet.FindField("Address4").AsString = "DAddress4-Changed"
lDetailDataSet.FindField("Attention").AsString = "DAttention-Changed"
lDetailDataSet.FindField("Phone1").AsString = "DPhone1-Changed"
lDetailDataSet.FindField("Fax1").AsString = "DFax1-Changed"
lDetailDataSet.FindField("Email").AsString = "DEmailAddress-Changed"
lDetailDataSet.Post
end IF
'Step 6 Save Customer
BizObject.Save
BizObject.Close
MsgBox "Done"
End Function
|
Example-GL.TrialBalance.RO
GL Trial Balance Report Object Script |
---|
'Copy below script & paste to notepad & name it as eg GL.TrialBalance.RO.vbs
Call GetGLTBData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetGLTBData
Dim ComServer, RptObject, lDataSet, lDataSet2, lDateFrom, lDateTo
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("GL.TrialBalance.RO")
'Step 3: Spool parameters
RptObject.Params.Find("AllProject").Value = true
RptObject.Params.Find("AllAgent").Value = true
RptObject.Params.Find("AllArea").Value = true
RptObject.Params.Find("LedgerType").Value = "G" 'G = Legder, S = Debtor, P = Supplier
'RptObject.Params.Find("ProjectData").Value = 'Not use if AllArea is true
RptObject.Params.Find("ShowZeroBalance").Value = false
RptObject.Params.Find("SortBy").Value = "PostDate;DocNo"
RptObject.Params.Find("UseDescription2").Value = false
lDateFrom = CDate("October 27, 2016")
lDateTo = CDate("December 31, 2016")
'For Post Date From & Date To'
RptObject.Params.Find("DateFrom").Value = lDateFrom
RptObject.Params.Find("DateTo").Value = lDateTo
'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true
'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true
RptObject.Params.Find("SortBy").Value = "Code;Description"
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsGLAccount")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox "Month To Date"
MsgBox lDataSet.FindField("Code").AsString & " DR=>" & lDataSet.FindField("MTDDR").AsString & " CR=>" & lDataSet.FindField("MTDCR").AsString
MsgBox "Year To Date"
MsgBox lDataSet.FindField("Code").AsString & " DR=>" & lDataSet.FindField("YTDDR").AsString & " CR=>" & lDataSet.FindField("YTDCR").AsString
lDataSet.Next
Wend
'For Account List
lDataSet2.First
While (not lDataSet2.eof)
MsgBox lDataSet2.FindField("Code").AsString & " " & lDataSet2.FindField("Description").AsString
lDataSet2.Next
Wend
End Function
|
Example-Customer.Aging.RO
Customer Aging Report Object Script |
---|
'Copy below script & paste to notepad & name it as eg customer_aging_ro.vbs
Call GetCustStatementData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetCustStatementData
Const Quote = """"
Dim ComServer, RptObject, lDataSet, lDataSet2, lDateFrom, lDateTo, lAgeData
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("Customer.Aging.RO")
'Step 3: Spool parameters
lAgeData = "<?xml version=" & Quote & "1.0" & Quote & " standalone=" & Quote & "yes" & Quote & "?> <DATAPACKET Version=" & Quote & "2.0" & Quote & "><METADATA><FIELDS>" &_
"<FIELD attrname=" & Quote & "ColumnNo" & Quote & " fieldtype=" & Quote & "i4" & Quote & " required=" & Quote & "true" & Quote & "/><FIELD attrname=" & Quote & "ColumnType" & Quote & " fieldtype=" & Quote & "string" & Quote & " WIDTH=" & Quote & "1" & Quote & "/>" &_
"<FIELD attrname=" & Quote & "Param1" & Quote & " fieldtype=" & Quote & "i4" & Quote & " required=" & Quote & "true" & Quote & "/><FIELD attrname=" & Quote & "Param2" & Quote & " fieldtype=" & Quote & "i4" & Quote & " required=" & Quote & "true" & Quote & "/>" &_
"<FIELD attrname=" & Quote & "IsLocal" & Quote & " fieldtype=" & Quote & "boolean" & Quote & "/><FIELD attrname=" & Quote & "HeaderScript" & Quote & " fieldtype=" & Quote & "bin.hex" & Quote & " SUBTYPE=" & Quote & "Text" & Quote & " WIDTH=" & Quote & "1" & Quote & "/>" &_
"</FIELDS><PARAMS/></METADATA><ROWDATA><ROW ColumnNo=" & Quote & "0" & Quote & " ColumnType=" & Quote & "" & Quote & " Param1=" & Quote & "0" & Quote & " Param2=" & Quote & "0" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "1" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "0" & Quote & " Param2=" & Quote & "0" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= 'Current Mth'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "2" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-1" & Quote & " Param2=" & Quote & "-1" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '1 Months'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "3" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-2" & Quote & " Param2=" & Quote & "-2" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '2 Months'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "4" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-3" & Quote & " Param2=" & Quote & "-3" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '3 Months'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "5" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-4" & Quote & " Param2=" & Quote & "-4" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '4 Months'
end;" & Quote & "/>" &_
"<ROW ColumnNo=" & Quote & "6" & Quote & " ColumnType=" & Quote & "B" & Quote & " Param1=" & Quote & "-999999" & Quote & " Param2=" & Quote & "-5" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal
begin
Value:= '5 Month & above'
end;" & Quote & "/>" &_
"</ROWDATA></DATAPACKET>"
RptObject.Params.Find("ActualGroupBy").Value = "Code;CompanyName" 'Fixed
' RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is True
RptObject.Params.Find("AgingData").Value = lAgeData 'Fixed
lDateTo = CDate("August 31, 2016")
RptObject.Params.Find("AgingDate").Value = lDateTo
RptObject.Params.Find("AgingOn").Value = "I" 'Fixed
RptObject.Params.Find("AllAgent").Value = true
RptObject.Params.Find("AllArea").Value = true
RptObject.Params.Find("AllCompany").Value = false
RptObject.Params.Find("AllCompanyCategory").Value = true
RptObject.Params.Find("AllControlAccount").Value = true
RptObject.Params.Find("AllCurrency").Value = true
RptObject.Params.Find("AllDocProject").Value = true
' RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is True
' RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is True
RptObject.Params.Find("CompanyData").Value = "300-A0001"& vbCRLF & "300-C0001" 'Filter by Customer Code 300-A0001 & 300-C0001
' RptObject.Params.Find("ControlAccountData").Value = 'Not use if AllControlAccount is True
' RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is True
' RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is True
RptObject.Params.Find("FilterPostDate").Value = false
' RptObject.Params.Find("GroupBy").Value = 'Not use
RptObject.Params.Find("IncludePDC").Value = false
RptObject.Params.Find("IncludeZeroBalance").Value = false
RptObject.Params.Find("SortBy").Value = "Code;CompanyName"
lDateTo = CDate("August 31, 2016")
RptObject.Params.Find("DateTo").Value = lDateTo
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsDocument")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox lDataSet.FindField("Code").AsString & " " & lDataSet.FindField("CompanyName").AsString
lDataSet2.First
While (not lDataSet2.eof)
MsgBox lDataSet2.FindField("DocNo").AsString
lDataSet2.Next
Wend
lDataSet.Next
Wend
End Function
|
Example-GL.JE.RO
GL Journal Voucher Report Object Script |
---|
'Copy below script & paste to notepad & name it as eg GLJE_ro.vbs
Call GetCustomerData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetCustomerData
Dim ComServer, RptObject, lDataSet, lDataSet2, lDateFrom, lDateTo
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("GL.JE.RO")
'Step 3: Spool parameters
RptObject.Params.Find("AllCurrency").Value = true
RptObject.Params.Find("AllDocProject").Value = true
RptObject.Params.Find("AllDocument").Value = true
RptObject.Params.Find("AllItemProject").Value = true
RptObject.Params.Find("AllItemArea").Value = true
RptObject.Params.Find("AllItemAgent").Value = true
RptObject.Params.Find("AllDocAgent").Value = true
RptObject.Params.Find("AllDocArea").Value = true
RptObject.Params.Find("AllPaymentMethod").Value = true
lDateFrom = CDate("December 1, 2017")
lDateTo = CDate("December 31, 2017")
RptObject.Params.Find("DateFrom").Value = lDateFrom
RptObject.Params.Find("DocDateFrom").Value = lDateFrom
RptObject.Params.Find("DateTo").Value = lDateTo
RptObject.Params.Find("DocDateTo").Value = lDateTo
RptObject.Params.Find("IncludeCancelled").Value = false
RptObject.Params.Find("PrintDocumentStyle").Value = false
RptObject.Params.Find("SelectDate").Value = true 'Post Date
RptObject.Params.Find("SelectDocDate").Value = false 'Doc Date
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsDetail")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox lDataSet.FindField("DocNo").AsString & " " & lDataSet.FindField("Description").AsString
lDataSet2.First
While (not lDataSet2.eof)
MsgBox lDataSet2.FindField("Code").AsString & " " & lDataSet2.FindField("Description").AsString
lDataSet2.Next
Wend
lDataSet.Next
Wend
End Function
|
Example-SL_CS
SL_CS Script |
---|
'Copy below script & paste to notepad & name it as eg SL_CS.vbs
Call InsertData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function InsertData
Dim ComServer, BizObject, lDataSet, lDataSet2, lDate
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\estream\SQLAccounting\Share\Default.DCF", "ACC-0020.FDB"
END IF
'Step 2: Find and Create the Biz Objects
Set BizObject = ComServer.BizObjects.Find("SL_CS")
'Step 3: Set Dataset
Set lMainDataSet = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data
Set lDetailDataSet = BizObject.DataSets.Find("cdsDocDetail") 'lDetailDataSet contains detail data
'Step 4 : Insert Data - Master
lDate = CDate("January 1, 2016")
BizObject.New
lMainDataSet.FindField("DocKey").value = -1
lMainDataSet.FindField("DocNo").value = "--IV Test--"
lMainDataSet.FindField("DocDate").value = lDate
lMainDataSet.FindField("PostDate").value = lDate
lMainDataSet.FindField("Code").value = "300-C0001"
lMainDataSet.FindField("Description").value = "Sales"
'Step 5: Insert Data - Detail
'For Tax Inclusive = True with override Tax Amount
lDetailDataSet.Append
lDetailDataSet.FindField("DtlKey").value = -1
lDetailDataSet.FindField("DocKey").value = -1
lDetailDataSet.FindField("Account").value = "500-000"
lDetailDataSet.FindField("Description").value = "Sales Item A"
lDetailDataSet.FindField("Qty").value = 1
lDetailDataSet.FindField("Tax").value = "SR"
lDetailDataSet.FindField("TaxInclusive").value = 0
lDetailDataSet.FindField("UnitPrice").value = 410.37
lDetailDataSet.FindField("Amount").value = 410.37
lDetailDataSet.FindField("TaxAmt").value = 24.63
lDetailDataSet.DisableControls
lDetailDataSet.FindField("TaxInclusive").value = 1
lDetailDataSet.EnableControls
lDetailDataSet.FindField("Changed").value = "F"
lDetailDataSet.Post
'For Tax Inclusive = False with override Tax Amount
lDetailDataSet.Append
lDetailDataSet.FindField("DtlKey").value = -1
lDetailDataSet.FindField("DocKey").value = -1
lDetailDataSet.FindField("Account").value = "500-000"
lDetailDataSet.FindField("Description").value = "Sales Item B"
lDetailDataSet.FindField("Qty").value = 1
lDetailDataSet.FindField("Tax").value = "SR"
lDetailDataSet.FindField("TaxInclusive").value = 0
lDetailDataSet.FindField("UnitPrice").value = 94.43
lDetailDataSet.FindField("Amount").value = 94.43
lDetailDataSet.FindField("TaxAmt").value = 5.66
lDetailDataSet.FindField("Changed").value = "F"
lDetailDataSet.Post
'For With Item Code
lDetailDataSet.Append
lDetailDataSet.FindField("DtlKey").value = -1
lDetailDataSet.FindField("DocKey").value = -1
lDetailDataSet.FindField("ItemCode").value = "Item B"
lDetailDataSet.FindField("Description").value = "Sales Item B"
lDetailDataSet.FindField("Qty").value = 2
lDetailDataSet.FindField("Tax").value = "SR"
lDetailDataSet.FindField("TaxInclusive").value = 0
lDetailDataSet.FindField("UnitPrice").value = 100
lDetailDataSet.FindField("Amount").value = 200
lDetailDataSet.FindField("TaxAmt").value = 12
lDetailDataSet.FindField("Changed").value = "F"
lDetailDataSet.Post
'Step 6: Save Document
BizObject.Save
BizObject.Close
MsgBox "Done"
End Function
|
Example-Customer.PM.RO
Customer.PM.RO Script |
---|
'Copy below script & paste to notepad & name it as eg Customer.PM.RO.vbs
Call GetARPMData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetARPMData
Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, lDateFrom, lDateTo
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("Customer.PM.RO")
'Step 3: Spool parameters
'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true
'RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is true
RptObject.Params.Find("AllAgent").Value = true
RptObject.Params.Find("AllCompanyCategory").Value = true
RptObject.Params.Find("AllArea").Value = true
RptObject.Params.Find("AllCompany").Value = true
RptObject.Params.Find("AllCurrency").Value = true
RptObject.Params.Find("AllDocument").Value = false
RptObject.Params.Find("AllPaymentMethod").Value = true
'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true
'RptObject.Params.Find("CompanyData").Value = 'Not use if AllCompany is true
'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true
lDateFrom = CDate("January 01, 2017")
lDateTo = CDate("December 31, 2017")
RptObject.Params.Find("DateFrom").Value = lDateFrom
RptObject.Params.Find("DateTo").Value = lDateTo
RptObject.Params.Find("DocumentData").Value = "VCOR-00049"
'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data
RptObject.Params.Find("IncludeCancelled").Value = false
'RptObject.Params.Find("PaymentMethodData").Value = 'Not use if AllPaymentMethod is true
RptObject.Params.Find("PrintDocumentStyle").Value = false
RptObject.Params.Find("SelectDate").Value = true
RptObject.Params.Find("ShowUnappliedAmountOnly").Value = false
RptObject.Params.Find("SortBy").Value = "PostDate;DocNo;Code"
RptObject.Params.Find("AllDocProject").Value = true
RptObject.Params.Find("AllItemProject").Value = true
'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true
'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsKnockOff")
Set lDataSet3 = RptObject.DataSets.Find("cdsKnockOffDetail")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox lDataSet.FindField("DocKey").AsString & " " & lDataSet.FindField("DocNo").AsString & " " & lDataSet.FindField("Code").AsString
MsgBox "cdsKnockOff Count " & lDataSet2.RecordCount
lDataSet2.First
While (not lDataSet2.eof)
MsgBox lDataSet2.FindField("FromDocKey").AsString & " " & lDataSet2.FindField("DocNo").AsString & " " & lDataSet2.FindField("KOAmt").AsString
lDataSet2.Next
Wend
MsgBox "cdsKnockOffDetail Count " & lDataSet3.RecordCount
lDataSet3.First
While (not lDataSet3.eof)
MsgBox lDataSet3.FindField("FromDocKey").AsString & " " & lDataSet3.FindField("DocNo").AsString & " " & lDataSet3.FindField("KOAmt").AsString
lDataSet3.Next
Wend
lDataSet.Next
Wend
End Function
|
Example-Sales.IV.RO
Sales.IV.RO Script |
---|
'Copy below script & paste to notepad & name it as eg Sales.IV.RO.vbs
Call GetSalesIVData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetSalesIVData
Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, lDateFrom, lDateTo
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("Sales.IV.RO")
'Step 3: Spool parameters
'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true
RptObject.Params.Find("AllAgent").Value = true
RptObject.Params.Find("AllArea").Value = true
RptObject.Params.Find("AllCompany").Value = false
RptObject.Params.Find("AllCurrency").Value = true
RptObject.Params.Find("AllDocProject").Value = true
RptObject.Params.Find("AllDocument").Value = true
RptObject.Params.Find("AllItem").Value = true
RptObject.Params.Find("AllItemProject").Value = true
RptObject.Params.Find("AllLocation").Value = true
RptObject.Params.Find("AllStockGroup").Value = true
RptObject.Params.Find("AllCompanyCategory").Value = true
RptObject.Params.Find("AllBatch").Value = true
'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true
'RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is true
RptObject.Params.Find("CompanyData").Value = "300-C0001"
'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true
lDateFrom = CDate("January 01, 2017")
lDateTo = CDate("December 31, 2017")
RptObject.Params.Find("DateFrom").Value = lDateFrom
RptObject.Params.Find("DateTo").Value = lDateTo
'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true
'RptObject.Params.Find("DocumentData").Value = 'Not use if AllDocument is true
'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data
'RptObject.Params.Find("CategoryData").Value = 'Not use if HasCategory is false
'RptObject.Params.Find("CategoryTpl").Value = 'For Internal use only
RptObject.Params.Find("IncludeCancelled").Value = false
RptObject.Params.Find("HasCategory").Value = false
'RptObject.Params.Find("ItemData").Value = 'Not use if AllItem is true
'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true
'RptObject.Params.Find("LocationData").Value = 'Not use if AllLocation is true
'RptObject.Params.Find("ItemCategoryData").Value = 'For Internal use only
'RptObject.Params.Find("BatchData").Value = 'Not use if AllBatch is true
RptObject.Params.Find("PrintDocumentStyle").Value = false
RptObject.Params.Find("SelectDate").Value = true
RptObject.Params.Find("SortBy").Value = "PostDate;DocNo;Code"
'RptObject.Params.Find("StockGroupData").Value = 'Not use if AllStockGroup is true
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsDocDetail")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox lDataSet.FindField("DocKey").AsString & " " & lDataSet.FindField("DocNo").AsString & " " & lDataSet.FindField("Code").AsString
MsgBox "cdsDocDetail Count " & lDataSet2.RecordCount
lDataSet2.First
While (not lDataSet2.eof)
MsgBox lDataSet2.FindField("DocKey").AsString & " " & lDataSet2.FindField("ItemCode").AsString & " " & lDataSet2.FindField("Amount").AsString
lDataSet2.Next
Wend
lDataSet.Next
Wend
End Function
|
Example-Customer.DueDocument.RO
Customer.DueDocument.RO Script |
---|
'Copy below script & paste to notepad & name it as eg Customer.DueDocument.RO.vbs
Call GetData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetData
Dim ComServer, RptObject, lDataSet, lDateTo
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("Customer.DueDocument.RO")
'Step 3: Spool parameters
'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true
RptObject.Params.Find("AllAgent").Value = true
RptObject.Params.Find("AllArea").Value = true
RptObject.Params.Find("AllCompany").Value = false
RptObject.Params.Find("AllCompanyCategory").Value = true
RptObject.Params.Find("AllCurrency").Value = true
RptObject.Params.Find("AllDocProject").Value = true
RptObject.Params.Find("AllItemProject").Value = true
'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true
'RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is true
RptObject.Params.Find("CompanyData").Value = "300-A0003"
'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true
'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true
'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data
'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true
RptObject.Params.Find("PrintContra").Value = true
RptObject.Params.Find("PrintCreditNote").Value = true
RptObject.Params.Find("PrintDebitNote").Value = true
RptObject.Params.Find("PrintInvoice").Value = true
RptObject.Params.Find("PrintOverDue").Value = true
RptObject.Params.Find("PrintUnDue").Value = true
RptObject.Params.Find("ShowForeignCurrency").Value = true
RptObject.Params.Find("ShowLocalCurrency").Value = true
RptObject.Params.Find("SortBy").Value = "PostDate;DocNo;Code"
lDateTo = CDate("April 12, 2018")
RptObject.Params.Find("DateTo").Value = lDateTo
RptObject.Params.Find("IncludePDCheque").Value = true
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox "Dockey " & lDataSet.FindField("DocKey").AsString
MsgBox "DocNo " & lDataSet.FindField("DocNo").AsString
MsgBox "DocAmt " & lDataSet.FindField("DocAmt").AsString
MsgBox "Outstanding " & lDataSet.FindField("Outstanding").AsString
MsgBox "Code " & lDataSet.FindField("Code").AsString
lDataSet.Next
Wend
End Function
|
Example-Customer.IV.RO
Customer.IV.RO Script |
---|
'Copy below script & paste to notepad & name it as eg Customer.IV.RO.vbs
Call GetData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetData
Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, lDateFrom, lDateTo
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("Customer.IV.RO")
'Step 3: Spool parameters
'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true
'RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is true
RptObject.Params.Find("AllAgent").Value = true
RptObject.Params.Find("AllCompanyCategory").Value = true
RptObject.Params.Find("AllArea").Value = true
RptObject.Params.Find("AllCompany").Value = true
RptObject.Params.Find("AllCurrency").Value = true
RptObject.Params.Find("AllDocument").Value = false
RptObject.Params.Find("AllPaymentMethod").Value = true
'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true
'RptObject.Params.Find("CompanyData").Value = 'Not use if AllCompany is true
'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true
lDateFrom = CDate("January 01, 2017")
lDateTo = CDate("December 31, 2017")
RptObject.Params.Find("DateFrom").Value = lDateFrom
RptObject.Params.Find("DateTo").Value = lDateTo
RptObject.Params.Find("DocumentData").Value = "IV-00012"
'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data
RptObject.Params.Find("IncludeCancelled").Value = true
'RptObject.Params.Find("PaymentMethodData").Value = 'Not use if AllPaymentMethod is true
RptObject.Params.Find("PrintDocumentStyle").Value = false
RptObject.Params.Find("SelectDate").Value = true
RptObject.Params.Find("ShowUnappliedAmountOnly").Value = true
RptObject.Params.Find("SortBy").Value = "DocDate;DocNo;Code"
RptObject.Params.Find("AllDocProject").Value = true
RptObject.Params.Find("AllItemProject").Value = true
'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true
'RptObject.Params.Find("ItemProjectData ").Value = 'Not use if AllItemProject is true
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsDocDetail")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox "DocNo " & lDataSet.FindField("DocNo").AsString
MsgBox "DocAmt " & lDataSet.FindField("DocAmt").AsString
MsgBox "PaymentAmt " & lDataSet.FindField("PaymentAmt").AsString
MsgBox "cdsDocDetail Count " & lDataSet2.RecordCount
lDataSet2.First
While (not lDataSet2.eof)
MsgBox "Account " & lDataSet2.FindField("Account").AsString
MsgBox "Description " & lDataSet2.FindField("Description").AsString
MsgBox "Amount " & lDataSet2.FindField("Amount").AsString
lDataSet2.Next
Wend
lDataSet.Next
Wend
End Function
|
Example-Sales.OutstandingSO.RO
Sales.OutstandingSO.RO Script |
---|
'Copy below script & paste to notepad & name it as eg Sales.OutstandingSO.RO.vbs
Call GetData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function GetData
Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, lDateFrom, lDateTo
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
If not ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB"
END IF
'Step 2: Find and Create the Report Objects
Set RptObject = ComServer.RptObjects.Find("Sales.OutstandingSO.RO")
'Step 3: Spool parameters
'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true
'RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is true
'RptObject.Params.Find("LocationData").Value = 'Not use if AllLocation is true
'RptObject.Params.Find("StockGroupData").Value = 'Not use if AllStockGroup is true
RptObject.Params.Find("AllAgent").Value = true
RptObject.Params.Find("AllArea").Value = true
RptObject.Params.Find("AllCompany").Value = true
RptObject.Params.Find("AllDocument").Value = false
RptObject.Params.Find("AllItem").Value = true
RptObject.Params.Find("AllItemProject").Value = true
'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true
'RptObject.Params.Find("CompanyData").Value = 'Not use if AllCompany is true
lDateFrom = CDate("January 01, 2017")
lDateTo = CDate("December 31, 2017")
'RptObject.Params.Find("DateFrom").Value = lDateFrom
'RptObject.Params.Find("DateTo").Value = lDateTo
'RptObject.Params.Find("DeliveryDateFrom").Value = lDateFrom
'RptObject.Params.Find("DeliveryDateTo").Value = lDateTo
RptObject.Params.Find("DocumentData").Value = "SO-00126"& vbCRLF & "SO-00127"
'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data
RptObject.Params.Find("IncludeCancelled").Value = false
'RptObject.Params.Find("ItemData").Value = 'Not use if AllItem is true
RptObject.Params.Find("PrintFulfilledItem").Value = true 'Print transfered info
RptObject.Params.Find("PrintOutstandingItem").Value = true 'Print untransfer info
'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true
RptObject.Params.Find("SelectDate").Value = false
RptObject.Params.Find("SelectDeliveryDate").Value = false
RptObject.Params.Find("SortBy").Value = "DocDate;DocNo;Code"
RptObject.Params.Find("AllDocProject").Value = true
RptObject.Params.Find("AllLocation").Value = true
RptObject.Params.Find("AllCompanyCategory").Value = true
RptObject.Params.Find("AllBatch").Value = true
RptObject.Params.Find("HasCategory").Value = false
RptObject.Params.Find("AllStockGroup").Value = true
'RptObject.Params.Find("CategoryData").Value = 'For Internal use only
'RptObject.Params.Find("CategoryTpl").Value = 'For Internal use only
'RptObject.Params.Find("ItemCategoryData").Value = 'For Internal use only
'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true
'RptObject.Params.Find("BatchData").Value = 'Not use if AllBatch is true
RptObject.Params.Find("TranferDocFilterDate").Value = false
'Step 4: Perform Report calculation
RptObject.CalculateReport()
Set lDataSet = RptObject.DataSets.Find("cdsMain")
Set lDataSet2 = RptObject.DataSets.Find("cdsTransfer")
MsgBox "Count " & lDataSet.RecordCount
'Step 5 Retrieve the output
lDataSet.First
While (not lDataSet.eof)
MsgBox "DocKey " & lDataSet.FindField("DocKey").AsString
MsgBox "DtlKey " & lDataSet.FindField("Dtlkey").AsString
MsgBox "DocNo " & lDataSet.FindField("DocNo").AsString
MsgBox "Item Code " & lDataSet.FindField("ItemCode").AsString
MsgBox "Org Qty " & lDataSet.FindField("SQty").AsString
MsgBox "Outstanding Qty " & lDataSet.FindField("OutstandingQty").AsString
MsgBox "cdsDocDetail Count " & lDataSet2.RecordCount
lDataSet2.First
While (not lDataSet2.eof)
MsgBox "FromDocKey " & lDataSet2.FindField("FromDocKey").AsString
MsgBox "FromDtlKey " & lDataSet2.FindField("FromDtlkey").AsString
MsgBox "DocType " & lDataSet2.FindField("DocType").AsString
MsgBox "DocNo " & lDataSet2.FindField("DocNo").AsString
MsgBox "DocDate " & lDataSet2.FindField("DocDate").AsString
MsgBox "Transfer Qty " & lDataSet2.FindField("TransferQty").AsString
lDataSet2.Next
Wend
lDataSet.Next
Wend
End Function
|
JScript/JavaScript
This Script only can run using Internet Explorer
Example-AR_IV
AR_IV Script |
---|
<html>
<head>
<title>Add Customer Invoice</title>
<script language="JScript">
var ComServer;
function CreateSQLAccServer() {
ComServer = new ActiveXObject('SQLAcc.BizApp');
if (!ComServer.IsLogin) { /* check whether user has logon */
ComServer.Login('ADMIN', 'ADMIN', 'C\:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF', 'SAMPLE.FDB');
/* UserName, Password */
}
}
function AutoInvoice() {
var BizObject, lMainDataSet, lDetailDataSet;
CreateSQLAccServer();
BizObject = ComServer.BizObjects.Find('AR_IV');
lMainDataSet = BizObject.DataSets.Find('MainDataSet');
lDetailDataSet = BizObject.DataSets.Find('cdsDocDetail');
BizObject.New();
lMainDataSet.FindField('DocKey').value = -1;
lMainDataSet.FindField('DocNo').value = "--IV Test--";
lMainDataSet.FindField('DocDate').value = "01/10/2016";
lMainDataSet.FindField('PostDate').value = "01/10/2016";
lMainDataSet.FindField('Code').value = "300-A0003";
lMainDataSet.FindField('Description').value = "Sales";
/*For Tax Inclusive = True with override Tax Amount*/
lDetailDataSet.Append();
lDetailDataSet.FindField('DtlKey').value = -1;
lDetailDataSet.FindField('DocKey').value = -1;
lDetailDataSet.FindField('Account').value = "500-000";
lDetailDataSet.FindField('Description').value = "Sales Item A";
lDetailDataSet.FindField('Tax').value = "SR";
lDetailDataSet.FindField('TaxInclusive').value = 0;
lDetailDataSet.FindField('Amount').value = 410.37;
lDetailDataSet.FindField('TaxAmt').value = 24.63;
lDetailDataSet.DisableControls();
lDetailDataSet.FindField('TaxInclusive').value = 1;
lDetailDataSet.EnableControls();
lDetailDataSet.FindField('Changed').value = "F";
lDetailDataSet.Post();
/*For Tax Inclusive = False with override Tax Amount*/
lDetailDataSet.Append();
lDetailDataSet.FindField('DtlKey').value = -1;
lDetailDataSet.FindField('DocKey').value = -1;
lDetailDataSet.FindField('Account').value = "500-000";
lDetailDataSet.FindField('Description').value = "Sales Item B";
lDetailDataSet.FindField('Tax').value = "SR";
lDetailDataSet.FindField('TaxInclusive').value = 0;
lDetailDataSet.FindField('Amount').value = 94.43;
lDetailDataSet.FindField('TaxAmt').value = 5.66;
lDetailDataSet.FindField('Changed').value = "F";
lDetailDataSet.Post();
/* Save document */
BizObject.Save();
BizObject.Close();
alert('Customer Invoice - has been saved.');
}
</script>
</head>
<body>
<input type="submit" value="SYNC" name="btnSubmit" onclick="AutoInvoice()">
</body>
</html>
|
Example-AR_PM
AR_PM Script |
---|
<html>
<head>
<title>Add Customer Payment</title>
<script language="JScript">
var ComServer;
function CreateSQLAccServer() {
ComServer = new ActiveXObject("SQLAcc.BizApp");
if (!ComServer.IsLogin) { /* check whether user has logon */
ComServer.Login('ADMIN', 'ADMIN', 'C\:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF', 'SAMPLE.FDB');
/* UserName, Password */
}
}
function AutoPayment() {
var BizObject, lMainDataSet, lKnockOffDataSet, V;
CreateSQLAccServer();
BizObject = ComServer.BizObjects.Find('AR_PM');
lMainDataSet = BizObject.DataSets.Find('MainDataSet');
lKnockOffDataSet = BizObject.DataSets.Find('cdsKnockOff');
BizObject.New();
lMainDataSet.FindField('DocKey').value = -1;
lMainDataSet.FindField('DocNo').value = "Test";
lMainDataSet.FindField('Code').value = "888";
lMainDataSet.FindField('DocDate').value = "01/09/2015";
lMainDataSet.FindField('PostDate').value = "01/09/2015";
lMainDataSet.FindField('Project').value = "----";
lMainDataSet.FindField('Description').value = "";
lMainDataSet.FindField('PaymentMethod').value = "320-000";
lMainDataSet.FindField('Journal').value = "CASH";
lMainDataSet.FindField('ChequeNumber').value = "";
lMainDataSet.FindField('CurrencyCode').value = "----";
lMainDataSet.FindField('CurrencyRate').value = "1.0000000000";
lMainDataSet.FindField('DocAmt').value = "106.00";
lMainDataSet.FindField('BankCharge').value = "0";
lMainDataSet.FindField('UnappliedAmt').value = "0";
lMainDataSet.FindField('Cancelled').value = 'F';
v = ComServer.CreateOleVariantArray(2);
v.SetItem(0, 'IV'); /*Knock Off DocType*/
v.SetItem(1, 'IV-00111'); /*Knock Off DocNo*/
if (lKnockOffDataSet.Locate('DocType;DocNo', v.AsOleVariant(), false, false)){
lKnockOffDataSet.Edit();
lKnockOffDataSet.FindField('DocAmt').value = "106";
lKnockOffDataSet.FindField('KnockOff').AsString = 'T';
lKnockOffDataSet.Post(); }
/* Save document */
BizObject.Save();
BizObject.Close();
alert("Customer Payment - has been saved.");
}
</script>
</head>
<body>
<input type="submit" value="SYNC" name="btnSubmit" onclick="AutoPayment()">
</body>
</html>
|
Example-Customer.IV.RO
Customer.IV.RO Report Object Script |
---|
<html>
<head>
<title>Customer.IV.RO</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<script type="text/javascript">
var ComServer;
function CreateSQLAccServer() {
ComServer = new ActiveXObject("SQLAcc.BizApp");
if (!ComServer.IsLogin) { /* check whether user has logon */
ComServer.Login('ADMIN', 'ADMIN', 'C\:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF', 'SAMPLE.FDB');
/* UserName, Password */
}
}
function GetData() {
var lMain, lDetail, lRptVar, lDateFrom, lDateTo;
elem = document.getElementById('txt1');
CreateSQLAccServer();
lDateFrom = new Date("January 1 2015");
lDateTo = new Date("December 31, 2015");
lRptVar = ComServer.RptObjects.Find('Customer.IV.RO');
lRptVar.Params.Find('SelectDate').Value = true;
lRptVar.Params.Find('DateFrom').Value = lDateFrom.valueOf();
lRptVar.Params.Find('DateTo').Value = lDateTo.valueOf();
lRptVar.Params.Find('AllAgent').Value = false;
lRptVar.Params.Find('AgentData').Value = "----\nLF"; /*Filter by Agent code ---- & HALIM */
lRptVar.Params.Find('AllArea').Value = true;
lRptVar.Params.Find('AllCompany').Value = true;
lRptVar.Params.Find('AllCurrency').Value = true;
lRptVar.Params.Find('AllDocProject').Value = true;
lRptVar.Params.Find('AllCompanyCategory').Value = true;
lRptVar.Params.Find('AllDocument').Value = true;
lRptVar.Params.Find('IncludeCancelled').Value = false;
lRptVar.Params.Find('PrintDocumentStyle').Value = false;
lRptVar.Params.Find('AllItemProject').Value = true;
lRptVar.Params.Find('AllPaymentMethod').Value = true;
lRptVar.CalculateReport();
lMain = lRptVar.DataSets.Find('cdsMain');
lDetail = lRptVar.DataSets.Find('cdsDocDetail');
/*alert("Count -" + lRptVar.DataSets.Find('cdsDocDetail').RecordCount);*/
elem.innerHTML += 'From Main'+'\n';
lMain.First();
while (!lMain.eof) {
elem.innerHTML += lMain.FindField('DocKey').AsString + ' ' +
lMain.FindField('DocNo').AsString + ' ' + lMain.FindField('DocDate').AsString + ' '+
lMain.FindField('CompanyName').AsString + ' ' + lMain.FindField('DocAmt').AsString + '\n';
lMain.Next();
}
elem.innerHTML += 'End Main'+'\n\n';
elem.innerHTML += 'From Detail'+'\n';
lDetail.First();
while (!lDetail.eof) {
elem.innerHTML += lDetail.FindField('Dockey').AsString + ' ' + lDetail.FindField('Account').AsString + ' ' +
lDetail.FindField('Description').AsString + ' ' + lDetail.FindField('Amount').AsString + '\n';
lDetail.Next();
}
elem.innerHTML += 'End Detail'+'\n\n';
}
</script>
</head>
<body>
<form>
<textarea id="txt1" rows="30" cols="116"></textarea>
<input type="button" value="Calculate" onclick="GetData()">
</form>
</body>
</html>
|
Delphi
The below example is assuming after you had check the login status.
Example-GL_JE
GL_JE Script |
---|
procedure TfmMain.dxbb_GL_JEClick(Sender: TObject);
var BizApp, lMain, lDtl, lBizObj, lDocKey : Variant;
i : integer;
begin
BizApp := CreateOleObject('SQLAcc.BizApp');
try
lBizObj := BizApp.BizObjects.Find('GL_JE');
lMain := lBizObj.DataSets.Find('MainDataSet');
lDtl := lBizObj.DataSets.Find('cdsDocDetail');
lBizObj.New;
lMain.FindField('DOCKEY').Value := -1;
lMain.FindField('DocNo').Value := '--JE Test--';
lMain.FindField('DocDate').Value := '28 Aug 2017';
lMain.FindField('PostDate').Value := '28 Aug 2017';
lMain.FindField('TAXDATE').Value := '28 Aug 2017';
lMain.FindField('Description').Value := 'Postage';
lMain.FindField('Cancelled').Value := 'F';
lMain.FindField('DOCNOSETKEY').Value := 0;
lMain.FindField('CHANGED').Value := 'F';
lMain.Post;
//For Tax Inclusive
lDtl.Append;
lDtl.FindField('DTLKEY').Value := -1;
lDtl.FindField('DOCKEY').Value := -1;
lDtl.FindField('SEQ').Value := 1;
lDtl.FindField('CODE').Value := '610-1000';
lDtl.FindField('DESCRIPTION').Value := 'Item Desc 1';
lDtl.FindField('TAX').Value := 'TX';
// lDtl.FindField('LOCALTAXAMT').AsFloat := 17.32;//For JE you can't override (i.e. system auto calc)
lDtl.FindField('TAXINCLUSIVE').Value := 1;
lDtl.FindField('LocalDR').AsFloat := 306;//Including GST if TAXINCLUSIVE = 1
lDtl.FindField('DR').AsFloat := 306;//if wanted DR <> LocalDR
lDtl.FindField('CHANGED').Value := 'F';
lDtl.Post;
//For Tax Exclusive
lDtl.Append;
lDtl.FindField('DTLKEY').Value := -1;
lDtl.FindField('DOCKEY').Value := -1;
lDtl.FindField('SEQ').Value := 2;
lDtl.FindField('CODE').Value := '605-200';
lDtl.FindField('DESCRIPTION').Value := 'Item Desc 2';
lDtl.FindField('TAX').Value := 'TX';
lDtl.FindField('TAXINCLUSIVE').Value := 0;
lDtl.FindField('LocalCR').AsFloat := 100;
lDtl.FindField('CHANGED').Value := 'F';
lDtl.Post;
//For No GST
lDtl.Append;
lDtl.FindField('DTLKEY').Value := -1;
lDtl.FindField('DOCKEY').Value := -1;
lDtl.FindField('SEQ').Value := 3;
lDtl.FindField('CODE').Value := '610-002';
lDtl.FindField('DESCRIPTION').Value := 'Item Desc 3';
lDtl.FindField('TAX').Value := '';
lDtl.FindField('TAXINCLUSIVE').Value := 0;
lDtl.FindField('LocalCR').AsFloat := 200;
lDtl.FindField('CHANGED').Value := 'F';
lDtl.Post;
lBizObj.Save;
MessageDlg('GL_JE-DocNo : ''--JE Test--'' Posted...', mtInformation, [mbOK],0);
finally
lBizObj.Close;
end;
end;
|
Example-GL_PV
GL_PV Script |
---|
procedure TfmMain.dxbb_GLPVClick(Sender: TObject);
var lMain, lDtl, lBizObj, BizApp : Variant;
lTime : TDateTime;
begin
lTime := Now;
BizApp := CreateOleObject('SQLAcc.BizApp');
try
lBizObj := BizApp.BizObjects.Find('GL_PV');
lMain := lBizObj.DataSets.Find('MainDataSet');
lDtl := lBizObj.DataSets.Find('cdsDocDetail');
lBizObj.New;
lMain.FindField('DOCKEY').Value := -1;
lMain.FindField('DocNo').AsString := '--CB PV Test1--';
lMain.FindField('DocDate').Value := '18 Aug 2015';
lMain.FindField('PostDate').Value := '18 Aug 2015';
lMain.FindField('TAXDATE').Value := '18 Aug 2015';
lMain.FindField('Description').AsString := 'Compacc System';
lMain.FindField('PAYMENTMETHOD').AsString := '310-001';
lMain.FindField('CHEQUENUMBER').AsString := 'MBB 213245';
lMain.FindField('DocAmt').AsFloat := 2019;
lMain.FindField('Cancelled').AsString := 'F';
lMain.FindField('CHANGED').AsString := 'F';
lMain.Post;
lDtl.Append;
lDtl.FindField('DTLKEY').Value := -1;
lDtl.FindField('DOCKEY').Value := -1;
lDtl.FindField('Code').AsString := '200-300';
lDtl.FindField('DESCRIPTION').AsString := 'Maybank - Asus A555LD-xx313H';
lDtl.FindField('TAX').AsString := 'TX';
lDtl.FindField('TAXAMT').AsFloat := 114.28;
lDtl.FindField('TAXINCLUSIVE').Value := 1;
lDtl.FindField('AMOUNT').AsFloat := 2019;
lDtl.FindField('CHANGED').AsString := '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;
|
Example-AR_Customer
AR_Customer Script |
---|
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
AR_PM Script |
---|
procedure TfmMain.dxbb_ARPMClick(Sender: TObject);
var lMain, lKO, lBizApp, lBizObj, 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').AsString := '--PM Test--'; //Payment Document Number
lMain.FindField('CODE').AsString := '300-A0001';
lMain.FindField('DocDate').Value := '28 Aug 2016';
lMain.FindField('PostDate').Value := '28 Aug 2016';
lMain.FindField('Description').AsString := 'Payment for A/c';
lMain.FindField('PaymentMethod').AsString := '320-000';
lMain.FindField('DocAmt').AsFloat := 1060;
lMain.FindField('Cancelled').AsString := 'F';
lMain.FindField('CHANGED').AsString := '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--'); //Invoice Document Number
if lKO.Locate('DocType;DocNo', V.AsOleVariant, False, False) then
begin
lKO.Edit;
lKO.FindField('KOAmt').ASFloat := 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-AR_PM-Edit
AR_PM Script |
---|
procedure TfmMain.dxbb_ARPMClick(Sender: TObject);
var lMain, lKO, lBizApp, lBizObj, V : Variant;
begin
lBizApp := CreateOleObject('SQLAcc.BizApp');
try
lBizObj := lBizApp.BizObjects.Find('AR_PM');
lMain := lBizObj.DataSets.Find('MainDataSet');
lKO := lBizObj.DataSets.Find('cdsKnockOff');
lDocKey := lBizObj.FindKeyByRef('DocNo', '--PM Test--');
lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);
If not VarIsNull(lDocKey) Then begin
lBizObj.Open;
lBizObj.Edit;
lMain.FindField('DOCKEY').Value := -1;
lMain.FindField('DocDate').Value := '28 Aug 2016';
lMain.FindField('PostDate').Value := '28 Aug 2016';
lMain.FindField('Description').AsString := 'Payment for A/c';
lMain.FindField('PaymentMethod').AsString := '310-000';
lMain.FindField('DocAmt').AsFloat := 800.28;
lMain.FindField('UnappliedAmt').AsFloat := 100.28;
lMain.FindField('Cancelled').AsString := 'F';
lMain.FindField('CHANGED').AsString := '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').AsFloat := 700.28; // IV Knock Off/Offset Amount
lKO.FindField('KnockOff').AsString := 'T';
lKO.Post;
end;
lBizObj.Save;
MessageDlg('AR_PM-DocNo : ''--PM Test--'' Updated...', mtInformation, [mbOK],0);
end;
finally
lBizObj.Close;
end;
end;
|
Example-ST_Item
ST_Item Script |
---|
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').AsString := 'Test-1';
lMain.FindField('Description').AsString := 'Testing - Description';
lDtl.Edit; //For 1st UOM
lDtl.FindField('UOM').AsString := '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').AsString := '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-ST_Item-Edit
ST_Item Script |
---|
procedure TfmMain.dxbb_ST_ItemClick(Sender: TObject);
var lMain, lDtl, lBizObj, lBizApp, lDocKey, V : Variant;
begin
lBizApp := CreateOleObject('SQLAcc.BizApp');
lBizObj := lBizApp.BizObjects.Find('ST_ITEM');
lMain := lBizObj.DataSets.Find('MainDataSet');
lDtl := lBizObj.DataSets.Find('cdsUOM');
lDocKey := lBizObj.FindKeyByRef('Code', '--Stock Item Test--');
lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);
try
If not VarIsNull(lDocKey) Then begin
lBizObj.Open;
lBizObj.Edit;
lMain.FindField('Description').value := 'Testing - Description';
V := lBizApp.CreateOleVariantArray(2);
V.SetItem(0, 'CTN'); //UOM
V.SetItem(1, '12'); //Rate
if lDtl.Locate('UOM;Rate', V.AsOleVariant, False, False) then
begin
lDtl.Edit;
lDtl.FindField('RefCost').AsFloat := 10.20;
lDtl.FindField('RefPrice').AsFloat := 25.00;
lDtl.Post;
end;
lBizObj.Save;
MessageDlg('ST_Item-ItemCode : ''--Stock Item Test--'' Updated...', mtInformation, [mbOK],0);
end;
finally
lBizObj.Close;
end;
end;
|
Example-SL_IV
SL_IV Script |
---|
procedure TfmMain.dxbb_SL_IVClick(Sender: TObject);
var lMain, lDtl, lSN, lBizObj, lBizApp : Variant;
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').AsString := '--IV Test--';
lMain.FindField('CODE').AsString := '300-A0001';
lMain.FindField('DocDate').Value := '28 Aug 2016';
lMain.FindField('PostDate').Value := '28 Aug 2016';
lMain.FindField('TAXDATE').Value := '28 Aug 2016';
lMain.FindField('Description').AsString := 'Sales';
lMain.FindField('Cancelled').AsString := 'F';
lMain.FindField('CHANGED').AsString := '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').AsString := 'ITEM A';
lDtl.FindField('ACCOUNT').AsString := '500-000'; //GL Sales Account
lDtl.FindField('DESCRIPTION').AsString := 'Sales Item A';
lDtl.FindField('QTY').AsFloat := 1;
lDtl.FindField('TAX').AsString := 'SR';
lDtl.FindField('TAXINCLUSIVE').Value := 0;
lDtl.FindField('UNITPRICE').AsFloat := 435;//Including GST UnitPrice
lDtl.FindField('AMOUNT').AsFloat := 410.37; //Must Excluding GST (Qty * UnitPrice) // Less 0.01
lDtl.FindField('TAXAMT').AsFloat := 24.63; //Extra 0.01
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').AsString := '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').AsString := 'ITEM B';
lDtl.FindField('ACCOUNT').AsString := '500-000';
lDtl.FindField('DESCRIPTION').AsString := 'Sales Item B';
lDtl.FindField('QTY').AsFloat := 1;
lDtl.FindField('TAX').AsString := 'SR';
lDtl.FindField('TAXINCLUSIVE').Value := 0;
lDtl.FindField('UNITPRICE').AsFloat := 94.43;
lDtl.FindField('AMOUNT').AsFloat := 94.43;
lDtl.FindField('TAXAMT').AsFloat := 5.66; //Less 0.01
lDtl.FindField('CHANGED').AsString := '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').AsString := 'SN1';
lDtl.FindField('ACCOUNT').AsString := '500-000';
lDtl.FindField('DESCRIPTION').AsString := 'Sales Serial Number Item';
lSN.Append;
lSN.FindField('SERIALNUMBER').AsString := 'SN-00001';
lSN.Post;
lSN.Append;
lSN.FindField('SERIALNUMBER').AsString := 'SN-00002';
lSN.Post;
lDtl.FindField('QTY').AsFloat := 2;
lDtl.FindField('TAX').AsString := 'SR';
lDtl.FindField('TAXINCLUSIVE').Value := 0;
lDtl.FindField('UNITPRICE').AsFloat := 94.43;
lDtl.FindField('TAXAMT').AsFloat := 11.33;
lDtl.FindField('CHANGED').AsString := '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;
|
Example-Customer.IV.RO
Customer.IV.RO Report Object Script |
---|
procedure TfmMain.dxbb_RptObjClick(Sender: TObject);
var lBizApp, lRptVar : Variant;
lTime : TDateTime;
cdsMain, cdsDtl : TClientDataset;
begin
lTime := Now;
cdsMain := TClientDataset.Create(nil);
cdsDtl := TClientDataset.Create(nil);
lBizApp := CreateOleObject('SQLAcc.BizApp');
try
lRptVar := null;
lRptVar := lBizApp.RptObjects.Find('Customer.IV.RO');
lRptVar.Params.Find('SelectDate').AsBoolean := True;
lRptVar.Params.Find('DateFrom').AsDate := '01/01/2015';
lRptVar.Params.Find('DateTo').AsDate := '31/12/2015';
lRptVar.Params.Find('AllAgent').AsBoolean := False;
lRptVar.Params.Find('AgentData').AsBlob := '----'#13#10'HALIM'; //Filter by Agent code ---- & HALIM
lRptVar.Params.Find('AllArea').AsBoolean := True;
// lRptVar.Params.Find('AreaData').AsBlob := ; //Not use if AllArea is True
lRptVar.Params.Find('AllCompany').AsBoolean := True;
// lRptVar.Params.Find('CompanyData').AsBlob := ; //Not use if AllCompany is True
lRptVar.Params.Find('AllCurrency').AsBoolean := True;
// lRptVar.Params.Find('CurrencyData').AsBlob := ; //Not use if AllCurrenty is True
lRptVar.Params.Find('AllDocProject').AsBoolean := True;
// lRptVar.Params.Find('DocProjectData').AsBlob := ; //Not use if AllDocProject is True
lRptVar.Params.Find('AllCompanyCategory').AsBoolean := True;
// lRptVar.Params.Find('CompanyCategoryData').AsBlob := ; //Not use if AllCompanyCategory is True
lRptVar.Params.Find('AllDocument').AsBoolean := True;
// lRptVar.Params.Find('DocumentData').AsBlob := ; //Not use if AllDocument is True
lRptVar.Params.Find('IncludeCancelled').AsBoolean := True;
lRptVar.Params.Find('PrintDocumentStyle').AsBoolean := False;
lRptVar.Params.Find('AllItemProject').AsBoolean := True;
lRptVar.Params.Find('AllPaymentMethod').AsBoolean := True;
lRptVar.CalculateReport;
cdsMain.XMLData := lRptVar.DataSets.Find('cdsMain').XMLData; //Master Data
cdsDtl.XMLData := lRptVar.DataSets.Find('cdsDocDetail').XMLData; //Detail Data - To link Master Data use Dockey field
cdsMain.SaveToFile('C:\Temp\_ListM.xml'); //Export To XML file
cdsDtl.SaveToFile('C:\Temp\_ListD.xml'); //Export To XML file
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lRptVar := null;
cdsMain.Free;
cdsDtl.Free;
end;
end;
|
Example-Stock.Item.RO
Stock.Item.RO Report Object Script |
---|
procedure TfmMain.dxbb_ST_ITEM_ROClick(Sender: TObject);
var lBizApp, lRptVar : Variant;
lTime : TDateTime;
cdsMain, cdsDtl, cdsCat : TClientDataset;
lSQL : string;
begin
lTime := Now;
cdsMain := TClientDataset.Create(nil);
cdsDtl := TClientDataset.Create(nil);
cdsCat := TClientDataset.Create(nil);
lBizApp := CreateOleObject('SQLAcc.BizApp');
lSQL := 'SELECT * FROM ST_ITEM_CATEGORY ' +
'WHERE Category IN (''B-MOTOLORA'',''B-MAXIS'')';
cdsCat.Data := lBizApp.DBManager.Execute(lSQL); //For filtering Category only
try
lRptVar := null;
lRptVar := lBizApp.RptObjects.Find('Stock.Item.RO');
lRptVar.Params.Find('AllItem').AsBoolean := True;
lRptVar.Params.Find('AllStockGroup').AsBoolean := True;
lRptVar.Params.Find('AllCustomerPriceTag').AsBoolean := True;
lRptVar.Params.Find('AllSupplierPriceTag').AsBoolean := True;
// lRptVar.Params.Find('CategoryData').AsBlob := ; //Not use
// lRptVar.Params.Find('CategoryTpl').AsBlob := ; //Not use
// lRptVar.Params.Find('CustomerPriceTagData').AsBlob := ; //Not use if AllCustomerPriceTag is True
// lRptVar.Params.Find('SupplierPriceTagData').AsBlob := ; //Not use if AllSupplierPriceTag is True
// lRptVar.Params.Find('DateFrom').AsDate := '01/01/2015'; //Not use if SelectDate is False
// lRptVar.Params.Find('DateTo').AsDate := '31/12/2015';
lRptVar.Params.Find('HasAltStockItem').AsBoolean := False;
lRptVar.Params.Find('HasBarcode').AsBoolean := False;
lRptVar.Params.Find('HasBOM').AsBoolean := False;
lRptVar.Params.Find('HasCategory').AsBoolean := False;
lRptVar.Params.Find('HasCustomerItem').AsBoolean := False;
lRptVar.Params.Find('HasOpeningBalance').AsBoolean := False;
lRptVar.Params.Find('HasPurchasePrice').AsBoolean := False;
lRptVar.Params.Find('HasSellingPrice').AsBoolean := False;
lRptVar.Params.Find('HasSupplierItem').AsBoolean := False;
// lRptVar.Params.Find('ItemData').AsBlob := 'ANT 1.0'#13#10'COVER'; //Filter by Item code ANT 1.0 & COVER
// lRptVar.Params.Find('ItemCategoryData').AsBlob := cdsCat.XMLData; //Not use if SelectCategory is False
lRptVar.Params.Find('PrintActive').AsBoolean := True;
lRptVar.Params.Find('PrintInActive').AsBoolean := True;
lRptVar.Params.Find('PrintNonStockControl').AsBoolean := True;
lRptVar.Params.Find('PrintStockControl').AsBoolean := True;
lRptVar.Params.Find('SelectCategory').AsBoolean := False;
lRptVar.Params.Find('SelectDate').AsBoolean := False;
lRptVar.Params.Find('SortBy').AsString := 'Code';
// lRptVar.Params.Find('StockGroupData').AsBlob := ; //Not use if AllStockGroup is True
lRptVar.CalculateReport;
cdsMain.XMLData := lRptVar.DataSets.Find('cdsMain').XMLData; //Master Data
cdsDtl.XMLData := lRptVar.DataSets.Find('cdsUOM').XMLData; //Detail Data - To link Master Data use Code field
cdsMain.SaveToFile('C:\Temp\_ListM.xml'); //Export To XML file
cdsDtl.SaveToFile('C:\Temp\_ListD.xml'); //Export To XML file
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lRptVar := null;
cdsMain.Free;
cdsDtl.Free;
cdsCat.Free;
end;
end;
|
Example-Customer.Statement.RO
Customer.Statement.RO Report Object Script |
---|
procedure TfmMain.dxbb_StatementClick(Sender: TObject);
var lBizApp, lRptVar : Variant;
lTime : TDateTime;
cdsMain, cdsDtl : TClientDataset;
s : string;
begin
lTime := Now;
cdsMain := TClientDataset.Create(nil);
cdsDtl := TClientDataset.Create(nil);
lBizApp := CreateOleObject('SQLAcc.BizApp');
s := '<?xml version="1.0" standalone="yes"?> <DATAPACKET Version="2.0"><METADATA><FIELDS>'+
'<FIELD attrname="ColumnNo" fieldtype="i4" required="true"/><FIELD attrname="ColumnType" fieldtype="string" WIDTH="1"/>'+
'<FIELD attrname="Param1" fieldtype="i4" required="true"/><FIELD attrname="Param2" fieldtype="i4" required="true"/>'+
'<FIELD attrname="IsLocal" fieldtype="boolean"/><FIELD attrname="HeaderScript" fieldtype="bin.hex" SUBTYPE="Text" WIDTH="1"/>'+
'</FIELDS><PARAMS/></METADATA><ROWDATA><ROW ColumnNo="0" ColumnType="" Param1="0" Param2="0" IsLocal="FALSE"/>'+
'<ROW ColumnNo="1" ColumnType="A" Param1="0" Param2="0" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= 'Current Mth'
end;"/>'+
'<ROW ColumnNo="2" ColumnType="A" Param1="-1" Param2="-1" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '1 Months'
end;"/>'+
'<ROW ColumnNo="3" ColumnType="A" Param1="-2" Param2="-2" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '2 Months'
end;"/>'+
'<ROW ColumnNo="4" ColumnType="A" Param1="-3" Param2="-3" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '3 Months'
end;"/>'+
'<ROW ColumnNo="5" ColumnType="A" Param1="-4" Param2="-4" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '4 Months'
end;"/>'+
'<ROW ColumnNo="6" ColumnType="B" Param1="-999999" Param2="-5" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '5 Month & above'
end;"/>'+
'</ROWDATA></DATAPACKET>';
try
lRptVar := null;
lRptVar := lBizApp.RptObjects.Find('Customer.Statement.RO');
// lRptVar.Params.Find('AgentData').AsBlob := ; //Not use if AllAgent is True
lRptVar.Params.Find('AgingData').AsString := s; //Fixed
lRptVar.Params.Find('AgingOn').AsString := 'I'; //Fixed
lRptVar.Params.Find('AllAgent').AsBoolean := True;
lRptVar.Params.Find('AllArea').AsBoolean := True;
lRptVar.Params.Find('AllCompany').AsBoolean := False;
lRptVar.Params.Find('AllCompanyCategory').AsBoolean := True;
lRptVar.Params.Find('AllControlAccount').AsBoolean := True;
lRptVar.Params.Find('AllCurrency').AsBoolean := True;
lRptVar.Params.Find('AllDocProject').AsBoolean := True;
// lRptVar.Params.Find('AreaData').AsBlob := ; //Not use if AllArea is True
// lRptVar.Params.Find('CompanyCategoryData').AsBlob := ; //Not use if AllCompanyCategory is True
lRptVar.Params.Find('CompanyData').AsBlob := '300-A0001'#13#10'300-C0001'; //Filter by Customer Code 300-A0001 & 300-C0001
// lRptVar.Params.Find('ControlAccountData').AsBlob := ; //Not use if AllControlAccount is True
// lRptVar.Params.Find('CurrencyData').AsBlob := ; //Not use if AllCurrency is True
lRptVar.Params.Find('DateFrom').AsDate := '01 Aug 2016';
lRptVar.Params.Find('DateTo').AsDate := '31 Aug 2016';
// lRptVar.Params.Find('DocProjectData').AsBlob := ; //Not use if AllDocProject is True
lRptVar.Params.Find('IncludeZeroBalance').AsBoolean := False;
lRptVar.Params.Find('SelectDate').AsBoolean := True;
lRptVar.Params.Find('SortBy').AsString := 'CompanyCategory;Code;CompanyName;Agent;Area;CurrencyCode;ControlAccount';
lRptVar.Params.Find('StatementDate').AsDate := '31 Aug 2016'; // Norm same as DateTo
lRptVar.Params.Find('StatementType').AsString := 'O'; //O := Open Item, B := B/F
lRptVar.CalculateReport;
cdsMain.XMLData := lRptVar.DataSets.Find('cdsMain').XMLData; //Master Data
cdsDtl.XMLData := lRptVar.DataSets.Find('cdsDocument').XMLData; //Detail Data - To link Master Data use Code field
cdsMain.SaveToFile('C:\Temp\_ListM.xml'); //Export To XML file
cdsDtl.SaveToFile('C:\Temp\_ListD.xml'); //Export To XML file
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lRptVar := null;
cdsMain.Free;
cdsDtl.Free;
end;
end;
|
Example-GL Account List For Sales & Purchase 1
GL_Acc Object Script |
---|
procedure TfmMain.dxbb_GLAccClick(Sender: TObject);
var BizApp : Variant;
lDataSet : TClientDataSet;
ASQL : String;
begin
//For Debtor List => SpecialAccType = DR
//For Creditor List => SpecialAccType = CR
BizApp := CreateOleObject('SQLAcc.BizApp');
lDataSet := TClientDataSet.Create(Self);
try
ASQL := 'SELECT A.Code, A.Description FROM GL_ACC A '+
'LEFT OUTER JOIN GL_ACC B ON (A.Dockey=B.Parent) '+
'WHERE A.Parent<>-1 ' +
'AND B.Dockey IS NULL ' +
'AND A.SpecialAccType = '''' '+
'ORDER BY A.CODE ';
lDataSet.Data := BizApp.DBManager.Execute(ASQL);
lDataset.SaveToFile('C:\Temp\_List.xml', dfXML); //Export To XML file
finally
lDataSet.Free;
end;
end;
|
Example-GL Account List For Sales & Purchase 2
GL_Acc Object Script |
---|
procedure TfmMain.dxbb_GLAccClick(Sender: TObject);
var BizApp, BizObj : Variant;
lDataSet1, lDataSet2 : TClientDataSet;
ASQL : String;
begin
//For Debtor List => SpecialAccType = DR
//For Creditor List => SpecialAccType = CR
BizApp := CreateOleObject('SQLAcc.BizApp');
BizObj := BizApp.BizObjects.Find('GL_Acc');
lDataSet1 := TClientDataSet.Create(Self);
lDataSet2 := TClientDataSet.Create(Self);
try
ASQL :='Parent<>-1 ' +
'AND (SpecialAccType IS NULL OR SpecialAccType = '''' ' +
'OR SpecialAccType = '''') ';
lDataSet1.XMLData:= BizObj.Select('Dockey,Code,Description', ASQL, 'Code', 'SX', '', '');
lDataSet2.XMLData:= BizObj.Select('Parent', '', 'Code', 'SX', '', '');
lDataset2.First; // Remove Parent Account
while not lDataset2.Eof do begin
if lDataset1.Locate('Dockey', lDataSet2.FindField('Parent').AsString, []) then
lDataset1.Delete;
lDataset2.Next;
end;
lDataset1.MergeChangeLog;
lDataset1.SaveToFile('C:\Temp\_List.xml', dfXML); //Export To XML file
finally
lDataSet1.Free;
lDataSet2.Free;
end;
end;
|
Example-Customer.Aging.RO
Customer Aging Report Object Script |
---|
procedure TfmMain.dxbb_AgingClick(Sender: TObject);
var lBizApp, lRptVar : Variant;
lTime : TDateTime;
cdsMain, cdsDtl : TClientDataset;
s : string;
begin
lTime := Now;
cdsMain := TClientDataset.Create(nil);
cdsDtl := TClientDataset.Create(nil);
lBizApp := CreateOleObject('SQLAcc.BizApp');
s := '<?xml version="1.0" standalone="yes"?> <DATAPACKET Version="2.0"><METADATA><FIELDS>'+
'<FIELD attrname="ColumnNo" fieldtype="i4" required="true"/><FIELD attrname="ColumnType" fieldtype="string" WIDTH="1"/>'+
'<FIELD attrname="Param1" fieldtype="i4" required="true"/><FIELD attrname="Param2" fieldtype="i4" required="true"/>'+
'<FIELD attrname="IsLocal" fieldtype="boolean"/><FIELD attrname="HeaderScript" fieldtype="bin.hex" SUBTYPE="Text" WIDTH="1"/>'+
'</FIELDS><PARAMS/></METADATA><ROWDATA><ROW ColumnNo="0" ColumnType="" Param1="0" Param2="0" IsLocal="FALSE"/>'+
'<ROW ColumnNo="1" ColumnType="A" Param1="0" Param2="0" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= 'Current Mth'
end;"/>'+
'<ROW ColumnNo="2" ColumnType="A" Param1="-1" Param2="-1" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '1 Months'
end;"/>'+
'<ROW ColumnNo="3" ColumnType="A" Param1="-2" Param2="-2" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '2 Months'
end;"/>'+
'<ROW ColumnNo="4" ColumnType="A" Param1="-3" Param2="-3" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '3 Months'
end;"/>'+
'<ROW ColumnNo="5" ColumnType="A" Param1="-4" Param2="-4" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '4 Months'
end;"/>'+
'<ROW ColumnNo="6" ColumnType="B" Param1="-999999" Param2="-5" IsLocal="FALSE" HeaderScript="ObjectPascal
begin
Value:= '5 Month & above'
end;"/>'+
'</ROWDATA></DATAPACKET>';
try
lRptVar := null;
lRptVar := lBizApp.RptObjects.Find('Customer.Aging.RO');
lRptVar.Params.Find('ActualGroupBy').AsString := 'Code;CompanyName'; //Fixed
// lRptVar.Params.Find('AgentData').AsBlob := ; //Not use if AllAgent is True
lRptVar.Params.Find('AgingData').AsString := s;
lRptVar.Params.Find('AgingDate').AsDate := '31 Aug 2016';
lRptVar.Params.Find('AgingOn').AsString := 'I'; //Fixed
lRptVar.Params.Find('AllAgent').AsBoolean := True;
lRptVar.Params.Find('AllArea').AsBoolean := True;
lRptVar.Params.Find('AllCompany').AsBoolean := True;
lRptVar.Params.Find('AllCompanyCategory').AsBoolean := True;
lRptVar.Params.Find('AllControlAccount').AsBoolean := True;
lRptVar.Params.Find('AllCurrency').AsBoolean := True;
lRptVar.Params.Find('AllDocProject').AsBoolean := True;
// lRptVar.Params.Find('AreaData').AsBlob := ; //Not use if AllArea is True
// lRptVar.Params.Find('CompanyCategoryData').AsBlob := ; //Not use if AllCompanyCategory is True
// lRptVar.Params.Find('CompanyData').AsBlob := ; //Not use if AllCompany is True
// lRptVar.Params.Find('ControlAccountData').AsBlob := ; //Not use if AllControlAccount is True
// lRptVar.Params.Find('CurrencyData').AsBlob := ; //Not use if AllCurrency is True
// lRptVar.Params.Find('DocProjectData').AsBlob := ; //Not use if AllDocProject is True
lRptVar.Params.Find('FilterPostDate').AsBoolean := True;
// lRptVar.Params.Find('GroupBy
lRptVar.Params.Find('IncludePDC').AsBoolean := False;
lRptVar.Params.Find('IncludeZeroBalance').AsBoolean := False;
lRptVar.Params.Find('SortBy').AsString := 'Code;CompanyName';
lRptVar.Params.Find('DateTo').AsDate := '31 Aug 2016';
lRptVar.Params.Find('IncludeKnockedOffDetail').AsBoolean := False;
lRptVar.CalculateReport;
cdsMain.XMLData := lRptVar.DataSets.Find('cdsMain').XMLData; //Master Data
cdsDtl.XMLData := lRptVar.DataSets.Find('cdsDocument').XMLData; //Detail Data - To link Master Data use Code field
cdsMain.SaveToFile('C:\Temp\_ListM.xml'); //Export To XML file
cdsDtl.SaveToFile('C:\Temp\_ListD.xml'); //Export To XML file
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lRptVar := null;
cdsMain.Free;
cdsDtl.Free;
end;
end;
|
FAQ
Problem with opening CHM Help files?
- Right click the chm file & select Properties
- At General tab Click Unblock button.
- Click Apply | Ok.
Still can't open CHM file?
- Login SQL Accounting
- Click Tools | Options
- Click Register & follow wizard
- Exit SQL Accounting
- Login SQL Accounting
- Run CHM File
Why when I compile my program with stated unknown method or unknown identifier?
- If you compiler had error may try add the esfw10.dll file under the SQL Accounting\bin folder
How to make sure I login to correct database?
- Can use the Common.Agent.RO Report Objects to get the Profile info.
- Then use the cdsProfile Dataset
- - CompanyName Field
- - Remark Field
- Refer to Example - Get Company Profile
Why after post to SQL Accounting the Description3 (More Description)/Notes field it show like this "M y D e s c r i p t i o n" instead of "My Description"?
- Make sure you use .AsString in your code (see example below)
Description3 Field Script/Code |
---|
... lDtl.FindField('DESCRIPTION3').AsString := 'Sales Item A More Description'+ #13 + 'I in 2nd line'; ... |
Can I Ignore Doc No Field & let SQL Accounting to Auto Assign?
- Yes but we not recommended as you will lost track when user wanted to know which Doc No in SQL Acc is posted.
- To Set Auto Assign set it as below
DocNo Field Script/Code |
---|
... lMain.FindField('DocNo').AsString := '<<New>>'; ... |
Can I Auto Login & Logout SQL Accounting when doing & after Posting?
- Yes can below is example script
Delphi |
---|
procedure TForm1.Button2Click(Sender: TObject);
var lMain, lBizObj, lBizApp : Variant;
lTime : TDateTime;
lFName, lFileName, lFilter : string;
begin
lFilter := 'DCF File (*.DCF)|*.DCF|All Files (*.*)|*.*';
lFName := 'ACC-0003.FDB'; //Default Database file
lFileName := 'C:\eStream\SQLAccounting\Share\Default.DCF'; //Default DCF File
if PromptForFileName(lFileName, lFilter, 'DCF', 'Open DCF File...', '.', False) then begin
if inputQuery('Enter DB file Name', 'FDB Name', lFName) then begin
lTime := Now;
lBizApp := CreateOleObject('SQLAcc.BizApp');
sleep(1000); //Wait 1 second for system loading before proceed
if lBizApp.IsLogin then lBizApp.Logout; //Force Logout to make sure login correct database
lBizApp.Login('ADMIN', 'ADMIN', lFileName, lFName); //Login
lBizObj := lBizApp.BizObjects.Find('Agent'); //Start Posting
lMain := lBizObj.DataSets.Find('MainDataSet');
try
lBizObj.New;
lMain.FindField('CODE').AsString := '_Test_';
lMain.FindField('Description').AsString := 'Testing 123';
lBizObj.Save; //Save Posting
finally
lBizObj.Close;
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
lBizApp.Logout; //Logout
lMain := Null;
lBizObj := Null;
lBizApp := Null;
end;
end;
end;
end;
|
VB Script |
---|
'Copy below script & paste to notepad & name it as eg Agent.vbs
Call PostData
Function CreateSQLAccServer
Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp")
End Function
Function PostData
Dim ComServer, BizObject
'Step 1: Create Com Server object
Set ComServer = CreateSQLAccServer 'Create Com Server
'Step 2: Force Logout to make sure login correct database
If ComServer.IsLogin Then 'if user hasn't logon to SQL application
ComServer.Logout
END IF
'Step 3: Login
ComServer.Login "ADMIN", "ADMIN", "C:\estream\SQLAccounting\Share\Default.DCF", "ACC-0015.FDB"
'Step 4: Find and Create the Biz Objects
Set BizObject = ComServer.BizObjects.Find("Agent")
'Step 5: Set Dataset
Set lMain = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data
'Step 6 : Posting
BizObject.New
lMain.FindField("CODE").AsString = "_Test_"
lMain.FindField("Description").AsString = "Testing 123"
'Step 7: Save Document
BizObject.Save
BizObject.Close
'Step 8: Logout
ComServer.Logout
MsgBox "Done"
End Function
|