File Watcher Cash Book Receipt import

[DannyC]

Gday guys,

In lieu of the Jiwa7 absence of the old Jiwa6 JiwaCashBookImport.exe utility, I am taking a stab at using the File Watcher to import a CSV into Cash Book Receipts.
I think I have most of it sussed, but I don't know how to add a new line.
I am basing the code here on the provided plugin File Watcher and using the Sub for CSVSalesOrderImport.

Code: Select all

   Private Sub CSVSalesOrderImport(ByVal FileName As String)                  
      'SAMPLE CSV INPUT FILE
      '=======================================================================
      'Bank Ledger,Transaction Date,Invoice No,Invoice Amount,Debtor AccountNo,Allocated Amount
      '6010-200-00,15/04/2016,000163-D01,15576.04,1002,10000
      '6010-200-00,15/04/2016,100165-D01,5500.01,1149,5500.01
      '6010-200-00,15/04/2016,100164-D01,606.49,1002,650
      '6010-200-00,16/04/2016,100167-D01,13615.66,1001,13615.66
      '6010-200-00,17/04/2016,100181-D01,-314.92,1001,-314.92
      '6010-200-00,17/04/2016,100182-D01,-507.97,1001,-507.97
      '
      '=======================================================================
      Dim CashBookRct As JiwaFinancials.Jiwa.JiwaCashBook.CashBook = JiwaApplication.Manager.Instance.BusinessLogicFactory.CreateBusinessLogic(Of JiwaCashBook.CashBook)(Nothing)
   
               
      Using csvParser As New FileIO.TextFieldParser(FileName)            
         csvParser.TextFieldType = FileIO.FieldType.Delimited
         csvParser.Delimiters = New String() {","}
         csvParser.HasFieldsEnclosedInQuotes = False
            
         Dim csvFields As String()
         Dim previousOrderNo As String = Nothing
         Dim newKey As String = ""
         Dim debtor As JiwaApplication.Entities.Debtor.Debtor = New JiwaApplication.Entities.Debtor.Debtor
         Dim BankLedger As JiwaApplication.Entities.GeneralLedger.Account = New JiwaApplication.Entities.GeneralLedger.Account
            
         Do While Not csvParser.EndOfData
            csvFields = csvParser.ReadFields
                                 
            If Not previousOrderNo Is Nothing AndAlso csvFields(0) <> previousOrderNo Then
               ' The order No. has changed - save the currently assembled sales order
               CashBookRct.Save()
               CSVSalesOrderWatcher.LogToEventLog(String.Format("Imported Cash Book Batch No. '{0}'  from File '{2}'", CashBookRct.BatchNo, FileName), System.Diagnostics.EventLogEntryType.SuccessAudit)
            End If
               
            If previousOrderNo Is Nothing OrElse csvFields(0) <> previousOrderNo Then
               ' Create a new order                  
               BankLedger.ReadRecordFromAccountNo(csvFields(0))
               CashBookRct.CreateNew(JiwaCashBook.CashBook.CashBookTypes.Receipt, BankLedger.GLLedgerID)
            End If               
                              
            previousOrderNo = csvFields(0)
            
            Debtor.ReadRecordFromAccountNo(csvFields(4))
            CashBookRct.AddNewTransaction("what are the values I need in here?")
         Loop            
            
         If CashBookRct.DebtorTransactions.Count > 0 Then
            CashBookRct.Save()
            CSVSalesOrderWatcher.LogToEventLog(String.Format("Imported Cash Book Batch No. '{0}'  from File '{1}'", salesOrder.InvoiceNo, FileName), System.Diagnostics.EventLogEntryType.SuccessAudit)            
         End If               
         
      End Using         
   End Sub
   

Cheers

[Scott.Pearce]

Here is the function declaration:

Code: Select all

Public Function AddNewTransaction(ByVal TransactionType As CashBookTransactionCollection.TransactionTypes, ByVal TransID As String, ByRef NewKey As Object, Optional ByVal LineNo As Integer = -1, Optional ByVal RemitNo As String = "") As CashBookReturnCodes

As an example, in our code when the debtor lookup button is clicked and a debtor selected, we add a new cashbook transaction:

Code: Select all

                               
ReturnCode = _CashBook.AddNewTransaction(JiwaCashBook.CashBookTransactionCollection.TransactionTypes.Debtor, DebtorID, Key)
If ReturnCode = JiwaCashBook.CashBook.CashBookReturnCodes.e_CashbookReturnCodeFailure Then
  JiwaApplication.Manager.Instance.ReportError(CashBook.ErrorString, _CashBook.ErrorModule)
End If


After adding a transaction, I can modify it's properties:

Code: Select all

_CashBook.CashBookTransactions(_CashBook.CashBookTransactions.Count -1).HomeAmount = 77


Your code would change to be something like:

Code: Select all

Dim newTransactionKey as string
CashBookRct.AddNewTransaction(JiwaCashBook.CashBookTransactionCollection.TransactionTypes.Debtor, Debtor.RecID, newTransactionKey)
'If you need to change some other values for the line you just added, you can use the newTransactionKey that is returned to you:
'CashBookRct.CashBookTransactions(newTransactionKey).HomeAmount = 77


[DannyC]

Been a little while since I've had the chance to revisit this.
I have used your code & I can import a Cash Book Receipt batch! Thanks for the snippet.

Just one minor issue, as I generally use the Jiwa plugin code tab to do the development, sometimes intellisense ain't that intelligent.
When I type

Code: Select all

CashBookRct.CashBookTransactions(newTransactionKey).

I'd hope to get all the available properties I can set, but they don't display. I know if I use Visual Studio I might be able to get them, but is there another way?

cheers

[Mike.Sheen]

Been a little while since I've had the chance to revisit this.
I have used your code & I can import a Cash Book Receipt batch! Thanks for the snippet.

Just one minor issue, as I generally use the Jiwa plugin code tab to do the development, sometimes intellisense ain't that intelligent.
When I type

Code: Select all

CashBookRct.CashBookTransactions(newTransactionKey).

I'd hope to get all the available properties I can set, but they don't display. I know if I use Visual Studio I might be able to get them, but is there another way?

cheers

Try declaring a variable of the correct type, assigning it and using it.

e.g.:

Code: Select all

Dim cashBookTransaction As JiwaCashBook.CashBookTransaction = CashBookRct.CashBookTransactions(newTransactionKey)
cashBookTransaction .


And then the intellisense / autocomplete should work on that second line. It's a shortcoming of the Actipro Syntax editor we use - we'll be updating it to a newer version soon, and that may fix the issue - but in the meantime the above should work.

[DannyC]

That 's good. I'll use that trick in the future for other collections where I can't get the properties.

I can't work out how to create a new allocation.
In the CSV file, I would be given the InvRemitNo, so something like 100234-D01, and also the allocated amount.
Can you give me a headstart?
I am assuming it would be something like

Code: Select all

cashBookTransaction.Allocations.Add()

but I am not clear on the values to pass into the brackets.

[Scott.Pearce]

The cashbook object is a little old and little overly complicated IMHO, but I think below is what you need. I've not tested this code.

To begin, you create a cashbook object, and add lines to it from your input file:

Code: Select all

Private WithEvents _CashBook As JiwaCashBook.CashBook

'Setup
_CashBook = JiwaApplication.Manager.Instance.BusinessLogicFactory.CreateBusinessLogic(Of JiwaCashBook.CashBook)(nothing)
_CashBook.CashBookType = JiwaCashBook.CashBook.CashBookTypes.Receipt
_CashBook.ReadSystemSettings()

'Create a new cashbook batch
_CashBook.CreateNew

'Add a line (based on a line from your input file)
Dim ReturnCode As JiwaCashBook.CashBook.CashBookReturnCodes = JiwaCashBook.CashBook.CashBookReturnCodes.e_CashBookReturnCodeSuccess
Dim DebtorID = "000000000C000000000T" 'You get this from your input file or by resolving an account no. from your input file to a DebtorID via a sql query. 
Dim cashBookLineKey as string = "" 'This will hold the index into the collection of the newly added line.  This is in case we need to reference it later.

ReturnCode = _CashBook.AddNewTransaction(JiwaCashBook.CashBookTransactionCollection.TransactionTypes.Debtor, DebtorID, cashBookLineKey)
If ReturnCode = JiwaCashBook.CashBook.CashBookReturnCodes.e_CashbookReturnCodeFailure Then
    'Log or message out the error using _CashBook.ErrorString and _CashBook.ErrorModule variables.
End If

'Remember that "cashBookLineKey" variable?  You use that to set the amount.
_CashBook.CashBookTransactions(cashBookLineKey).HomeAmount = 50 'This amount would come from your input file.


Now that you have a cashbook receipt line, you must determine which debtor transaction you want to allocate it to. You must ensure that the debtor transaction you are allocating to has been added to the cashbook.DebtorTransactions collection (we don't necessarily pre-populate this collection for performance reasons). This is done by calling "AddDebtorTransaction" and passing the DB_Trans.TransID of the transaction you wish to add:

Code: Select all

Dim TransID as string = "000000000700000003MU" 'Maybe you get this TransID by first firing a query based on InvRemitNo that was supplied to you in the file.  Maybe you don't care, maybe you just get the oldest debtor transaction id from DB_Trans that is unallocated (for the relevant debtor).
Dim debtorTransactionKey as string = "" 'This will hold the key of our newly added debtor transaction in case we need to reference it later.

CashBookObject.AddDebtorTransaction(TransID, debtorTransactionKey)


It's OK if the transaction already exists in the collection, AddDebtorTransaction() will see this and won't add it again, nor will it throw an exception.

Now that's done, an allocation between the debtor transaction and the cashbook line can be added:

Code: Select all

Dim amountToAllocate as decimal = 20 ' Perhaps you just allocate the entire amount, in such a case "amountToAllocate" would be _CashBook.CashBookTransactions(cashBookLineKey).HomeAmount

'cashBookLineKey is the index into the collection of cashbook lines.  This was kept from the code above that added the line.
'debtorTransactionKey is the index into the collection of cashbook debtor transactions.  This was kept from the code above where the debtor transaction was added.

_CashBook.AddAllocation(cashBookLineKey, debtorTransactionKey, amountToAllocate).


Now the cashbook batch can be saved, and activated if desired:

Code: Select all

_CashBook.Save
_CashBook.Activate


Again, this code is untested but will hopefully get you on the right track. Post back here if you have any troubles with it.

[DannyC]

Thanks for the excellent hints Scott.

I've been able to do the whole plugin. Awesome!

Cheers

Danny

[Mike.Sheen]

Thanks for the excellent hints Scott.

I've been able to do the whole plugin. Awesome!

Cheers

Danny

why-you-no-mark-as-solved.jpg (22.72 KiB) Viewed 19401 times

Also, If you are wanting to activate this batch, you will want to perform a read between the Save and the Activate, or you will experience a concurrency exception.

[Scott.Pearce]

Code: Select all

Also, If you are wanting to activate this batch, you will want to perform a read between the Save and the Activate, or you will experience a concurrency exception.

No, I believe the base class handles this for you - on SaveEnd the BusinessLogic base class will automagically perform a ReadRecord.

[Mike.Sheen]

Code: Select all

Also, If you are wanting to activate this batch, you will want to perform a read between the Save and the Activate, or you will experience a concurrency exception.

No, I believe the base class handles this for you - on SaveEnd the BusinessLogic base class will automagically perform a ReadRecord.

Not quite. If you have a form attached THAT will listen to SaveEnd and perform a read - the business logic does not do that. So if you don't have a form inheriting from Maintenance.UserInterface listening to the business logic, you will need to perform a read between save and activate.