Ktm Script 3

Option Explicit

' System.Data.SqlClient

' Project classification script

Const CutomerBTWNumberNotAllowed = "285007815, NL810101683B01"
Const InvCreditIndicator = "Credit"
Const InvDebitIndicator = "Invoice"

'# 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

'Access
   'Const cDSN = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Program Files\Kofax\CaptureSS\ServLib\Bin\Projects\Putten\KTM_Fakturen_Putten\Database\CustomerdatabaseBMC.mdb;Uid=Admin;Pwd;"
   'Const cSupplierTableName = "Suppliers"
   'Const cSupplierVATColumnName = "Btwnummer"

'Oracle
   Const cDSN = "Driver={Oracle in OraHome92};dbq=TSNNAAM;Uid=username;Pwd=password;" 
   Const cSupplierTableName = "v_leverancier"
   Const cSupplierVATColumnName = "nawbankrekening"

'MSSql
   'Const cDSN = "Provider=SQLOLEDB;Server=server;Database=ERP_Conn;UID=User;PWD=pass;"
   'Const cTableName = "Suppliers"

' do we check the invoice values or can we stop now ?
Dim CheckFact As String



' extraction script for class Invoice

Private Sub GetValidate_VatID(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    '# 4 omdat 0..3

  If pField.Text <> "" Then
    oRequest(0) = Trim(pField.Text)    '# 0 is dus de 1e kolom uit de DB
  Else
    oRequest(0) = "%"
  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 = "leveranciernaam"    '# 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, cSupplierTableName)
     If UBound(oResult) <> -1 And UBound(oResult)>0 Then
       SetVendorFields pXDoc, oResult
     Else
       ValidationForm_AfterFieldChanged pXDoc, pField
     End If
   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("VendorID"):   SetVendorField oField, Recordset(1) '  Recordset(3)
   Set oField = pXDoc.Fields.ItemByName("VendorName"): SetVendorField oField, Recordset(2)
   Set oField = pXDoc.Fields.ItemByName("VatID"):      SetVendorField oField, Recordset(1)

End Sub


Private Function GetProperAlternativeFromDatabase(ByRef pField As CASCADELib.CscXDocField, ColumnName As String, TableName As String) As String
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim Result As String
  Dim a As Integer
  Dim Selectstr As String
  'Dim x As Field

  Result = ""    '  pField.Text

  ' Find out If there are multiple items found otherwise there will be no choice , set value to 1
  ' Currently set to 0 to make every result trigger the lookup
  If  pField.Alternatives.Count > 0 Then
     Set cnn = New ADODB.Connection

   ' Open a connection by referencing the ODBC driver.
     cnn.ConnectionString = cDSN
     cnn.Open

   ' Check to see if the default value is allowed, otherwise remove it imediately
   If InStr(CutomerBTWNumberNotAllowed, pField.Text) > 0 Then
     pField.Text = ""
   End If

   ' search the various alternatives
   For a=0 To pField.Alternatives.Count-1

   ' check if the alternative is allowed, otherwise skip it
   If InStr(CutomerBTWNumberNotAllowed, pField.Alternatives(a).Text)=0 Then

     'If Len(pField.Alternatives(a).Text)= 9 Then
      'pField.Alternatives(a).Text = "0" + pField.Alternatives(a).Text
     'End If
     'If Len(pField.Alternatives(a).Text)= 14 And Left(pField.Alternatives(a).Text, 2)= "NL" And Mid(pField.Alternatives(a).Text,12,1) = "8" Then
      'pField.Alternatives(a).Text = Left(pField.Alternatives(a).Text,11) + "B" + Right(pField.Alternatives(a).Text,2)
       'End If

     ' Get info from the database
     Selectstr = "Select "+ColumnName+" from " +TableName+" WHERE " + ColumnName +" = '"+ pField.Alternatives(a).Text+ "'"

     Set rs = New ADODB.Recordset
                         '      Set rs = cnn.Execute( Selectstr )
     rs.Open(Selectstr,cnn)

     ' do we have a hit ?
     If rs.EOF = False Then
       Result=pField.Alternatives(a).Text
       pField.InitFromAlternative(a, Nothing)
       rs.Close
       Exit For
     End If

     rs.Close
   End If
   Next

  ' Close the connection.
  cnn.Close
  End If

  If Result = "" Then
     pField.Text = ""
     pField.Confidence = 0
  Else
     pField.Confidence = 1
  End If

  GetProperAlternativeFromDatabase = Result
End Function


Private Sub VatID_AfterExtract(ByRef pXDoc As CASCADELib.CscXDocument, ByRef pField As CASCADELib.CscXDocField)
    '' find out which Field we are currently serving
    If pField.Name = "VatID" Then
       GetProperAlternativeFromDatabase(pField, cSupplierVATColumnName, cSupplierTableName)
    End If
End Sub

Private Sub ValidationForm_AfterFieldConfirmed(pXDoc As CASCADELib.CscXDocument, pField As CASCADELib.CscXDocField)
  If pField.Name = "VatID" Then
    Call GetValidate_VatID(False, pXDoc, pField)
  End If

  If pField.Name = "InvoiceType" Then
    Call CheckInvoiceParameters(pXDoc)
  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
End Sub

Private Sub ValidationForm_DocumentLoaded(pXDoc As CASCADELib.CscXDocument)
   Dim pField As CASCADELib.CscXDocField

   Set pField = pXDoc.Fields.ItemByName("VatID")
   If pField.Text <> "" Then
     Call GetValidate_VatID(True, pXDoc, pField)
   End If
   CheckFact = "Y"
End Sub

Private Sub ValidationForm_ButtonClicked(ByVal ButtonName As String, ByVal pXDoc As CASCADELib.CscXDocument)
   Select Case ButtonName
      Case "InvoiceOK"
         CheckFact = "F"
         CheckInvoiceParameters(pXDoc)
   End Select
End Sub

Private Sub ValidationForm_FieldGotFocus(ByVal pXDoc As CASCADELib.CscXDocument, ByVal pField As CASCADELib.CscXDocField)
  If pField.Name = "VatID" Then
    Call GetValidate_VatID(True, pXDoc, pField)
  End If
  Call CheckInvoiceParameters(pXDoc)
End Sub

Private Sub ValidationForm_AfterFieldChanged(ByVal pXDoc As CASCADELib.CscXDocument, ByVal pField As CASCADELib.CscXDocField)
   Select Case pField.Name
      Case "VatID", "InvoiceType"
         SetRed(pField)
   End Select
End Sub

Private Sub SetVendorField(ByVal pField As CASCADELib.CscXDocField, ByVal sValue As String)
   pField.Text = sValue
   SetGreen(pField)
End Sub


Function SetGreen(ByVal pField As CASCADELib.CscXDocField)
  pField.Confidence = 1
  pField.ExtractionConfident = True
  pField.Valid = True
End Function

Function SetRed(ByVal pField As CASCADELib.CscXDocField)
  pField.Confidence = 0
  pField.Valid = False
  pField.ExtractionConfident = False
End Function

Function CheckAmountSign(pDocType As CASCADELib.CscXDocField, pAmount As CASCADELib.CscXDocField, fValue As Currency) As Currency
   Dim ResultFieldValue As Currency

   ResultFieldValue = fValue

   If ((pDocType.Text = InvCreditIndicator) And (fValue > 0)) Or ((pDocType.Text = InvDebitIndicator) And (fValue < 0)) Then
     ResultFieldValue = fValue * -1
     pAmount.Text = CStr(fValue)
     SetRed(pDocType)
   End If

   CheckAmountSign = ResultFieldValue
End Function




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 pSubTotal As CASCADELib.CscXDocField
  Dim pFaktBTW1 As CASCADELib.CscXDocField
  Dim pFaktBTW2 As CASCADELib.CscXDocField
  Dim pTaxRate1 As CASCADELib.CscXDocField
  Dim pTaxRate2 As CASCADELib.CscXDocField
  Dim pNetto0 As CASCADELib.CscXDocField
  Dim pNetto1 As CASCADELib.CscXDocField
  Dim pNetto2 As CASCADELib.CscXDocField
  Dim pFaktTot As CASCADELib.CscXDocField
  Dim pSupplierName As CASCADELib.CscXDocField

  Dim pBarCode As CASCADELib.CscXDocField
  Dim pBankNr As CASCADELib.CscXDocField
  Dim pVendID As CASCADELib.CscXDocField
  Dim pVendCur As CASCADELib.CscXDocField
  Dim pVendClass As CASCADELib.CscXDocField
  Dim pComp As CASCADELib.CscXDocField
  Dim pFactNr As CASCADELib.CscXDocField
  Dim pFaktDt As CASCADELib.CscXDocField
  Dim pDocType As CASCADELib.CscXDocField

  Dim TmpSubTotal As Currency
  Dim TmpFaktBtw1 As Currency
  Dim TmpFaktBtw2 As Currency
  Dim TmpNetto0 As Currency
  Dim TmpNetto1 As Currency
  Dim TmpNetto2 As Currency
  Dim TmpFaktTot As Currency
  Dim TmpFaktBtwC1 As Currency
  Dim TmpFaktBtwC2 As Currency
  Dim btw1 As Currency
  Dim btw2 As Currency
  Dim btwDeviation As Currency

  ' System defaults
  btw1 = 6
  btw2 = 19
  btwDeviation = 0.0055

  ' Get information from the form
  Set pFaktBTW1     = pXDoc.Fields.ItemByName("TaxAmount1")
  Set pFaktBTW2     = pXDoc.Fields.ItemByName("TaxAmount2")
  Set pNetto0       = pXDoc.Fields.ItemByName("NetAmount0")
  Set pNetto1       = pXDoc.Fields.ItemByName("NetAmount1")
  Set pNetto2       = pXDoc.Fields.ItemByName("NetAmount2")
  Set pSubTotal     = pXDoc.Fields.ItemByName("SubTotal")
  Set pFaktTot      = pXDoc.Fields.ItemByName("Total")
  Set pTaxRate1     = pXDoc.Fields.ItemByName("TaxRate1")
  Set pTaxRate2     = pXDoc.Fields.ItemByName("TaxRate2")
  Set pSupplierName = pXDoc.Fields.ItemByName("VendorName")
  Set pBarCode      = pXDoc.Fields.ItemByName("Barcode")
  Set pBankNr       = pXDoc.Fields.ItemByName("VatID")
  Set pVendID       = pXDoc.Fields.ItemByName("VendorID")
  Set pVendCur      = pXDoc.Fields.ItemByName("Currency")
  Set pFactNr       = pXDoc.Fields.ItemByName("InvoiceNumber")
  Set pFaktDt       = pXDoc.Fields.ItemByName("InvoiceDate")
  Set pDocType      = pXDoc.Fields.ItemByName("Documenttype")

  TmpFaktBtw1 = 0
  TmpFaktBtw2 = 0
  TmpSubTotal = 0
  TmpNetto0 = 0
  TmpNetto1 = 0
  TmpNetto2 = 0

  If CheckFact = "F" Then
    SetGreen(pFaktBTW1)
    SetGreen(pFaktBTW2)
    SetGreen(pNetto0)
    SetGreen(pNetto1)
    SetGreen(pNetto2)
    SetGreen(pSubTotal)
    SetGreen(pFaktTot)
    SetGreen(pTaxRate1)
    SetGreen(pTaxRate2)
    SetGreen(pSupplierName)
    SetGreen(pBarCode)
    SetGreen(pBankNr)
    SetGreen(pVendID)
    SetGreen(pVendCur)
    SetGreen(pFactNr)
    SetGreen(pFaktDt)
    SetGreen(pDocType)
  Else
    If pFaktBTW1.Text <> "" Then TmpFaktBtw1 = CCur(pFaktBTW1.Text)
    If pFaktBTW2.Text <> "" Then TmpFaktBtw2 = CCur(pFaktBTW2.Text)
    If pFaktTot.Text  <> "" Then TmpFaktTot  = CCur(pFaktTot.Text)
    If pSubTotal.Text <> "" Then TmpSubTotal = CCur(pSubTotal.Text)
    If pNetto0.Text   <> "" Then TmpNetto0   = CCur(pNetto0.Text)
    If pNetto1.Text   <> "" Then TmpNetto1   = CCur(pNetto1.Text)
    If pNetto2.Text   <> "" Then TmpNetto2   = CCur(pNetto2.Text)

    If pTaxRate1.Text <> "" Then btw1        = CDbl(pTaxRate1.Text)
    If pTaxRate2.Text <> "" Then btw2        = CDbl(pTaxRate2.Text)

  ' Default Results

  TmpFaktBtwC1 = ( TmpNetto1 / 100 ) * btw1
  TmpFaktBtwC2 = ( TmpNetto2 / 100 ) * btw2

  ' Suppliername should be filled

  If pSupplierName.Text = "" Then
     SetRed(pSupplierName)
  End If

  ' Check Credit or Invoice type
   If (UCase(Left(pDocType.Text,2)) = "CR") Then
     pDocType.Text = "Credit"
   Else
     pDocType.Text = "Invoice"
   End If

   ' Check totals

   If ((TmpSubTotal + TmpFaktBtw1 + TmpFaktBtw2) = TmpFaktTot) And TmpFaktTot <> 0 Then
     SetGreen(pSubTotal)
     SetGreen(pFaktBTW1)
     SetGreen(pFaktBTW2)
     SetGreen(pFaktTot)
   Else
     SetRed(pSubTotal)
     SetRed(pFaktBTW1)
     SetRed(pFaktBTW2)
     SetRed(pFaktTot)
   End If

   If ((TmpNetto0 + TmpNetto1 + TmpNetto2) = TmpSubTotal) Then
     SetGreen(pNetto0)
     SetGreen(pNetto1)
     SetGreen(pNetto2)
     SetGreen(pSubTotal)
   Else
     SetRed(pNetto0)
     SetRed(pNetto1)
     SetRed(pNetto2)
     SetRed(pSubTotal)
   End If

  ' Check Relation between invoiceTotals and VAT lines (Low VAT)

  If ((TmpFaktBtwC1 > (TmpFaktBtw1 - btwDeviation)) And (TmpFaktBtwC1 < (TmpFaktBtw1 + btwDeviation)) ) Then
     SetGreen(pFaktBTW1)
     SetGreen(pNetto1)
     SetGreen(pTaxRate1)
  Else
     SetRed(pFaktBTW1)
     SetRed(pNetto1)
     SetRed(pTaxRate1)
  End If

  ' Check Relation between invoiceTotals and VAT lines (High VAT)

  If ((TmpFaktBtwC2 > (TmpFaktBtw2 - btwDeviation))   And (TmpFaktBtwC2 < (TmpFaktBtw2 + btwDeviation)) ) Then
     SetGreen(pFaktBTW2)
     SetGreen(pNetto2)
     SetGreen(pTaxRate2)
  Else
     SetRed(pFaktBTW2)
     SetRed(pNetto2)
     SetRed(pTaxRate2)
  End If

  ' If needed change value of totals to reflect Credit invoice

  CheckAmountSign(pDocType, pFaktBTW1, TmpFaktBtw1)
  CheckAmountSign(pDocType, pFaktBTW2, TmpFaktBtw2)
  CheckAmountSign(pDocType, pFaktTot,  TmpFaktTot)
  CheckAmountSign(pDocType, pSubTotal, TmpSubTotal)
  CheckAmountSign(pDocType, pNetto0,   TmpNetto0)
  CheckAmountSign(pDocType, pNetto1,   TmpNetto1)
  CheckAmountSign(pDocType, pNetto2,   TmpNetto2)

  If pFaktTot.Text = "" Then SetRed(pFaktTot)

   CustomerSpecific(pXDoc)


   End If
End Function


Function CustomerSpecific(ByVal pXDoc As CASCADELib.CscXDocument)
  Dim pFaktBTW1 As CASCADELib.CscXDocField
  Dim pFaktBTW2 As CASCADELib.CscXDocField

  Set pFaktBTW1       = pXDoc.Fields.ItemByName("TaxAmount1")
  Set pFaktBTW2       = pXDoc.Fields.ItemByName("TaxAmount2")


   If pFaktBTW1.Text = "19.00" Then
      pFaktBTW1.Text  = "H"
   End If
End Function

Private Sub ForceDatabase()
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim Result As String
  Dim a As Integer
  Dim Selectstr As String
  'Dim x As Field

  ' Find out If there are multiple items found otherwise there will be no choice , set value to 1
  ' Currently set to 0 to make every result trigger the lookup

   Set cnn = New ADODB.Connection

   ' Open a connection by referencing the ODBC driver.
     cnn.ConnectionString = cDSN
     cnn.Open

   ' Check to see if the default value is allowed, otherwise remove it imediately

   ' search the various alternatives

   ' check if the alternative is allowed, otherwise skip it
     ' Get info from the database
     Selectstr = "Select "+ColumnName+" from " +TableName+" WHERE " + ColumnName +" = '1234'"

     Set rs = New ADODB.Recordset


     on error goto :gracefulfail
     rs.Open(Selectstr,cnn)

gracefulfail:
on error goto next
     ' do we have a hit ?
       rs.Close

  ' Close the connection.
  cnn.Close
on error goto 0

End sub