Simple lookup
1
Option Explicit ' Project classification script '# Create the global variables for the database lookup dialogue '# The variable for the ODBC needs to be global, '# so that the connection to the DNS is only opened once Global g_oODBC As New DatabaseDialog.DBLookupODBC
2
Option Explicit
'Access
Const cDSN = "Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\InvoiceProjektWSRL\Database\CustomerdatabaseDicom.mdb;Uid=Admin;Pwd=;"
Const cTableName = "Suppliers"
'Oracle
'Const cDSN = "Driver={Microsoft ODBC for Oracle};Server=USSyntheticCompanyAddresses;Uid=myUsername;Pwd=myPassword;"
'Const cDSN = "DSN=VerseonLookup;Uid=myUsername;Pwd=myPassword;"
'Const cTableName = "USSyntheticCompanyAddresses"
'MSSql
'Const cDSN = "Provider=SQLOLEDB;Server=server;Database=ERP_Conn;UID=User;PWD=pass;"
'Const cTableName = "Suppliers"
' extraction script for class Invoice
Private Sub GetValidate_BTWNumber(alwayscheck As Boolean,pXDoc As CASCADELib.CscXDocument, pField As CASCADELib.CscXDocField)
Dim oResult() As String '# String array to contain the record set result from the Fuzzy and the ODBC lookup selection
Dim oRequest(3) As String '# 3 omdat 0..3
If pField.Text <> "" Then
oRequest(2) = pField.Text '# 2 is dus de 3e kolom uit de DB
Else
oRequest(2) = "%"
End If
' MsgBox CStr(pField.ExtractionConfident) & CStr(pField.Valid) & CStr(pField.Confidence)
If (pField.Confidence = 0) Or (alwayscheck = True) Then
' handle field here
g_oODBC.VisibleColumns = "0;1;2;3" '# de veld nummers die we laten zien
g_oODBC.DialogCaption = "Leveranciers" '# titel
g_oODBC.GroupBoxCaption = "Resultaat uit leveranciers ( % is joker teken )" '# groep titel
g_oODBC.InitialQueryVals = oRequest '# zoekargument
g_oODBC.SearchImmediately = True '# direct zoeken
'Access -> g_oODBC.DatabaseType = 0 (Default), Oracle -> g_oODBC.DatabaseType = 1
'g_oODBC.DatabaseType = 1
oResult = g_oODBC.ShowDialog (cDSN, cTableName)
If UBound(oResult) <> -1 And UBound(oResult)>0 Then
SetVendorFields pXDoc, oResult
Else
ValidationForm_AfterFieldChanged pXDoc, pField
End If
End If
End Sub
'# ================================================================================================================
'# Set the fields for the selection from the alternatives list
'# ================================================================================================================
Private Sub SetVendorFields (ByVal pXDoc As CASCADELib.CscXDocument, ByRef RecordSet() As String)
Dim oField As CscXDocField
Set oField = pXDoc.Fields.ItemByName("SupplierNumber"): SetVendorField oField, RecordSet(0)
Set oField = pXDoc.Fields.ItemByName("SupplierName"): SetVendorField oField, RecordSet(1)
Set oField = pXDoc.Fields.ItemByName("BTWnr"): SetVendorField oField, RecordSet(2)
End Sub
Private Sub ValidationForm_AfterFieldConfirmed(pXDoc As CASCADELib.CscXDocument, pField As CASCADELib.CscXDocField)
If pField.Name = "BTWnr" Then
Call GetValidate_BTWNumber(False, pXDoc, pField)
End If
If pField.Text = "" Then
Select Case pField.Name
Case "SubTotal", "NetAmount0", "NetAmount1", "TaxAmount1", "TaxRate1", "NetAmount2", "TaxAmount2", "TaxRate2"
pField.Text = "0,00"
End Select
End If
Call CheckInvoiceParameters(pXDoc)
End Sub
Private Sub ValidationForm_DocumentLoaded(pXDoc As CASCADELib.CscXDocument)
Dim pField As CASCADELib.CscXDocField
Set pField = pXDoc.Fields.ItemByName("BTWnr")
If pField.Text <> "" Then
Call GetValidate_BTWNumber(True, pXDoc, pField)
End If
End Sub
Private Sub ValidationForm_FieldGotFocus(ByVal pXDoc As CASCADELib.CscXDocument, ByVal pField As CASCADELib.CscXDocField)
If pField.Name = "BTWnr" Then
Call GetValidate_BTWNumber(True, pXDoc, pField)
End If
End Sub
Private Sub ValidationForm_AfterFieldChanged(ByVal pXDoc As CASCADELib.CscXDocument, ByVal pField As CASCADELib.CscXDocField)
Select Case pField.Name
Case "BTWnr"
pField.Valid = False
pField.ExtractionConfident = False
pField.Confidence = 0
End Select
End Sub
Private Sub SetVendorField(ByVal pField As CASCADELib.CscXDocField, ByVal sValue As String)
pField.Text = sValue
'# We do not have the coordinates when using the Fuzzy or ODBC lookup
' pField.Left = -1: pField.Top = -1: pField.Width = -1: pField.Height = -1: pField.PageIndex = -1
'# Set the field to valid as the data is coming from the master data
pField.Valid=True
pField.ExtractionConfident = True
pField.Confidence = 1
End Sub
Rem ========================================================================
Rem
Rem Check Invoice Parameters, Check invoice totals for all fields and gives individual responce
Rem Ivar Snaaijer BMConsultants 20080610
Rem
Rem ========================================================================
Function CheckInvoiceParameters(ByVal pXDoc As CASCADELib.CscXDocument)
Dim pFaktBtw As CASCADELib.CscXDocField
Dim pFaktTot As CASCADELib.CscXDocField
Dim pSupplierName As CASCADELib.CscXDocField
Dim TmpFaktBtw As Currency
Dim TmpFaktTot As Currency
Dim TmpFaktBtwL As Currency
Dim TmpFaktBtwH As Currency
Dim btwH As Currency
Dim btwL As Currency
Dim btwDeviation As Currency
Rem System defaults
btwH = 19
btwL = 6
btwDeviation = 0.005
Rem Get information from the form
Set pFaktBtw = pXDoc.Fields.ItemByName("TaxAmount1")
Set pFaktTot = pXDoc.Fields.ItemByName("Total")
Set pSupplierName = pXDoc.Fields.ItemByName("SupplierName")
If pFaktBtw.Text <> "" Then TmpFaktBtwL = CCur(pFaktBtw.Text)
If pFaktTot.Text <> "" Then TmpFaktBtwH = CCur(pFaktTot.Text)
Rem Default Results
TmpFaktBtwL = (TmpFaktTot / (100 + btwL ) ) * btwL
TmpFaktBtwH = (TmpFaktTot / (100 + btwH ) ) * btwH
Rem Check Relation between invoiceTotals and VAT lines (Low VAT)
If ((TmpFaktBtw > (TmpFaktBtwL - btwDeviation)) And (TmpFaktBtw < (TmpFaktBtwL + btwDeviation)) ) Then
pFaktBtw.Confidence = 1
pFaktBtw.Valid = True
pFaktTot.Confidence = 1
pFaktTot.Valid = True
Else
pFaktBtw.Confidence = 0
pFaktBtw.Valid = False
pFaktTot.Confidence = 0
pFaktTot.Valid = False
End If
Rem Check Relation between invoiceTotals and VAT lines (High VAT)
If ((TmpFaktBtw > (TmpFaktBtwH - btwDeviation)) And (TmpFaktBtw < (TmpFaktBtwH + btwDeviation)) ) Then
pFaktBtw.Confidence = 1
pFaktBtw.Valid = True
pFaktTot.Confidence = 1
pFaktTot.Valid = True
Else
pFaktBtw.Confidence = 0
pFaktBtw.Valid = False
pFaktTot.Confidence = 0
pFaktTot.Valid = False
End If
If pSupplierName.Text = "" Then
pSupplierName.Confidence = 0
pSupplierName.Valid = False
End If
End Function
