Ktm Script 2

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=D:\Projects\Soest\KTM_Invoice_Soest\Database\cred_decade.mdb;Uid=Admin;Pwd;"
   'Const cSupplierTableName = "Amber"
   'Const cSupplierVATColumnName = "D"

'Oracle
   'Const cDSN = "Driver={Microsoft ODBC for Oracle};Server=USSyntheticCompanyAddresses;Uid=myUsername;Pwd=myPassword;"
   Const cDSN = "DSN=v_leverancier;Uid=verseon;Pwd=manager;"
   Const cSupplierTableName = "v_leverancier"
   Const cSupplierVATColumnName = "NAWBANKREKENING"

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

' 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(4) As String    '# 5 omdat 0..4

  Dim pCompCode As CASCADELib.CscXDocField

  set pCompCode  =  pXDoc.Fields.ItemByName("CompCode")

  If pField.Text <> "" Then
    oRequest(0) = pField.Text    '# 0 is dus de 1e kolom uit de DB
    oRequest(1) = pCompCode.Text    '# 1 is dus de 2e kolom uit de DB
  'Else
  '  oRequest(3) = "%"
  '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;4"    '# 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, 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)
   Set oField = pXDoc.Fields.ItemByName("VendorName"):  SetVendorField oField, Recordset(2)
   Set oField = pXDoc.Fields.ItemByName("VendorClass"): SetVendorField oField, Recordset(4)
   Set oField = pXDoc.Fields.ItemByName("VatID"):       SetVendorField oField, Recordset(0)

End Sub


Private Function GetProperAlternativeFromDatabase(ByRef pField1 As CASCADELib.CscXDocField, ColumnName1 As String, ByRef pField2 As CASCADELib.CscXDocField, ColumnName2 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  pField1.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, pField1.Text) > 0 Then
     pField1.Text = ""
   End If

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

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

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

     ' Get info from the database
     if ColumnName2 = "" then
       Selectstr = "Select "+ColumnName1+" from " +TableName+" WHERE " + ColumnName1 +" = '"+ pField1.Alternatives(a).Text+ "'"
     else
       Selectstr = "Select "+ColumnName1+" from " +TableName+" WHERE " + ColumnName1 +" = '"+ pField1.Alternatives(a).Text+ "' AND " + ColumnName2 +" = '"+ pField2.Text+ "'"
     end if

     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=pField1.Alternatives(a).Text
       pField1.InitFromAlternative(a, Nothing)
       rs.Close
       Exit For
     End If

     rs.Close
   End If
   Next

  ' Close the connection.
  cnn.Close
  End If

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

  GetProperAlternativeFromDatabase = Result
End Function

Private Sub Documenttype_AfterExtract(ByRef pXDoc As CASCADELib.CscXDocument, ByRef pField As CASCADELib.CscXDocField)
   If pField.Text = "Invoice" Then
      pField.Text = "0"
   Else
      If pField.Text = "Credit" Then
         pField.Text = "1"
      End If
   End If
'MsgBox CStr(pField.Text)
End Sub

'Private Sub Jaar_AfterExtract(ByRef pXDoc As CASCADELib.CscXDocument, ByRef pField As CASCADELib.CscXDocField)
'   pField.Text = Left(pField.Text,4)

'End Sub

'Private Sub Periode_AfterExtract(ByRef pXDoc As CASCADELib.CscXDocument, ByRef pField As CASCADELib.CscXDocField)
'   pField.Text = Mid(pField.Text,6,2)
'End Sub

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)
   Else
      SetRed(pField)
   End If
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 pOrdNr As CASCADELib.CscXDocField
  Dim pFactNr As CASCADELib.CscXDocField
  Dim pFaktDt As CASCADELib.CscXDocField
  Dim pDocType As CASCADELib.CscXDocField
  Dim pBTWTotaal 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 TmpBTWTotaal 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 pOrdNr        = pXDoc.Fields.ItemByName("OrderNumber")
  Set pFactNr       = pXDoc.Fields.ItemByName("InvoiceNumber")
  Set pFaktDt       = pXDoc.Fields.ItemByName("InvoiceDate")
  Set pDocType      = pXDoc.Fields.ItemByName("Documenttype")
  Set pBTWTotaal    = pXDoc.Fields.ItemByName("BTWTotaal")

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

  If pFaktBtw1.Text <> "" Then TmpFaktBtw1 = CDbl(pFaktBtw1.Text)
  If pFaktBtw2.Text <> "" Then TmpFaktBtw2 = CDbl(pFaktBtw2.Text)
  If pFaktTot.Text  <> "" Then TmpFaktTot  = CDbl(pFaktTot.Text)
  If pSubTotal.Text <> "" Then TmpSubTotal = CDbl(pSubTotal.Text)
  If pNetto0.Text   <> "" Then TmpNetto0   = CDbl(pNetto0.Text)
  If pNetto1.Text   <> "" Then TmpNetto1   = CDbl(pNetto1.Text)
  If pNetto2.Text   <> "" Then TmpNetto2   = CDbl(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
  pBTWTotaal.Text = CStr(CDbl(pFaktBtw1.Text) + CDbl(pFaktBtw2.Text))
  TmpBTWTotaal = CDbl(pBTWTotaal.Text)
MsgBox pBTWTotaal.Text+" : "+pFaktTot.Text
  ' 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 = "1"
   Else
     pDocType.Text = "0"
   End If

   ' Check totals

   If ((TmpSubTotal + TmpBTWTotaal) = TmpFaktTot) And TmpFaktTot <> 0 Then
     SetGreen(pSubTotal)
     SetGreen(pBTWTotaal)
   Else
     SetRed(pSubTotal)
     SetRed(pBTWTotaal)
   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 Function


Function CustomerSpecific(ByVal pXDoc As CASCADELib.CscXDocument)
  Dim pFaktDt As CASCADELib.CscXDocField
  Dim pJaar As CASCADELib.CscXDocField
  Dim pPeriode As CASCADELib.CscXDocField
  Dim pBoekdatum As CASCADELib.CscXDocField

  Set pFaktDt       = pXDoc.Fields.ItemByName("InvoiceDate")
  Set pJaar       = pXDoc.Fields.ItemByName("Jaar")
  Set pPeriode       = pXDoc.Fields.ItemByName("Periode")
  Set pBoekdatum       = pXDoc.Fields.ItemByName("Boekdatum")


   If pFaktDt.Text <> "" Then
      pJaar.Text  = Left(pFaktDt.Text ,4)
      pPeriode.Text  = Mid(pFaktDt.Text ,6,2)
   End If
   pBoekdatum.Text = Format(Date,"yyyy-mm-dd")
End Function

//MsgBox pBTWTotaal.Text+" : "+pFaktTot.Text+" : "+pFaktBtw1.Text+" : "+CStr(CDbl(pFaktBtw1.Text))