Filewatcher - debtor auto allocation

[neil.interactit]

Hi Scott,

Here's hoping I catch you before you finish up!

I have a file watcher that creates a cash book receipt batch. The client wants each payment to be auto allocated against the oldest invoice etc for each debtor.

I can see "Automatic Allocation" in debtor maintenance and "Auto Allocate .." in debtor allocations in Jiwa ... but can't figure how to call these programmatically.

Code: Select all

        If cashBook.CashBookTransactions.Count > 0 Then
            cashBook.Save()
            For Each transaction As CashBookTransaction In cashBook.CashBookTransactions
                Dim debtor As JiwaApplication.Entities.Debtor.Debtor = New JiwaApplication.Entities.Debtor.Debtor
                debtor.ReadRecord(transaction.DebtorCreditorAccountID)
                'debtor.AUTOMATICALLOCATION()
            Next
        End If


Can you point me in the right direction?

Cheers,
Neil.

[Scott.Pearce]

Here is the code underneath the "Automatic Allocation" button on the debtor form:

Code: Select all

    Private Sub mRecordAutomaticAllocation_Click()
        Dim oldCursor As System.Windows.Forms.Cursor = Cursor

        If MsgBox("Do you want to automatically allocate all credits to invoices ? ", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Automatic Allocation") = MsgBoxResult.Yes Then
            Try
                Cursor = Cursors.WaitCursor

                RemoveHandler _Debtor.Transactions.Changed, AddressOf _Debtor_Transactions_Changed
                RemoveHandler _Debtor.PropertyChanged, AddressOf _Debtor_PropertyChanged

                _Debtor.AutomaticAllocation()
                DisplayDebtor()
                DisplayTransactions()
                CheckEditStatus()
            Finally
                AddHandler _Debtor.Transactions.Changed, AddressOf _Debtor_Transactions_Changed
                AddHandler _Debtor.PropertyChanged, AddressOf _Debtor_PropertyChanged

                Cursor = oldCursor
            End Try

        End If
    End Sub


Notice that handlers are removed, and then re-added at the end. This is for efficiency - we don't want to throw events for each and every transaction we touch, we just force a re-display at the end. Also note that the debtor object used is the full debtor object (JiwaFinancials.Jiwa.JiwaDebtors.Debtor), not the entity!

So, as far as your code goes, use "JiwaFinancials.Jiwa.JiwaDebtors.Debtor" indead of "JiwaFinancials.Jiwa.JiwaApplication.Entities.Debtor.Debtor", and I'd also consider grouping together on DebtorCreditorAccountID so you don't call "AutomaticAllocation" multiple times for the same debtor (ie. iterate through the transactions and build a list of unique debtorIDs, then iterate through that list calling debtor.Read and debtor.AutomaticAllocation).

[neil.interactit]

Many thanks! I'll work on this and see how it goes.

Cheers,
Neil

[Mike.Sheen]

Note that as Neil is using the cash book business logic and is attempting the allocation on the save event the debtor transaction will not exist at this point.

I think the optimal strategy would be to not attempt to allocate using the debtor business logic, but the cash book business logic as this has methods to allocate to a debtor or creditor transaction such that when the cash book is activate the debit and credit is allocated.

To show how to do this, here is the code from the cash book form which allows you to add a cash book transaction with an allocation to an existing debtor transaction. .The allocation is not actually made until the cashbook is activated, but the intended allocation is stored within the unactivated cashbook

Code: Select all

Public Sub AddDebtorTransaction()
        Dim DebtorID As String = ""
        Dim DebtorAccountNo As String = ""
        Dim DebtorName As String = ""
        Dim TransID As String = ""
        Dim TransAmount As Decimal = 0
        Dim TransAllocAmount As Decimal = 0
        Dim TransKey As String = ""
        Dim ReturnCode As JiwaCashBook.CashBook.CashBookReturnCodes
        Dim Key As String = ""

        If SearchDebtorTransaction(TransID, TransAmount, TransAllocAmount, DebtorID, DebtorAccountNo, DebtorName) = True Then
            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)
            ElseIf ReturnCode = JiwaCashBook.CashBook.CashBookReturnCodes.e_CashBookReturnCodeSuccess Then
                _CashBook.AddDebtorTransaction(TransID, TransKey)
                If ReturnCode = JiwaCashBook.CashBook.CashBookReturnCodes.e_CashbookReturnCodeFailure Then
                    JiwaApplication.Manager.Instance.ReportError(CashBook.ErrorString, _CashBook.ErrorModule)
                ElseIf ReturnCode = JiwaCashBook.CashBook.CashBookReturnCodes.e_CashBookReturnCodeSuccess Then
                    If (TransAmount - TransAllocAmount) > 0 Then
                        CashBook.CashBookTransactions(Key).HomeAmount = (TransAmount - TransAllocAmount)
                        _CashBook.AddAllocation(Key, TransKey, TransAmount - TransAllocAmount, JiwaCashBook.CashBook.CashBookAllocationTypes.e_CashBookAllocationTypeNormal)
                    End If
                End If
            End If
        End If

    End Sub

[neil.interactit]

Hi Mike,

Thanks for this extra info. I am pursuing this approach but can't use SearchDebtorTransaction, possibly a private function? Could you explain a way to work around this?

Cheers,
Neil.

[neil.interactit]

Hi again Mike,

Here's where I am up to. Could you take a look at this plugin. I *think* all I need to do to get this going is to set a TransId value (which I assume comes from SearchDebtorTransaction).

Could you set me straight?

Cheers,
Neil.

[neil.interactit]

Hi again again,

I have pursued this a little further and got it working. In the plugin above I have updated DoAutomaticAllocation() to ...

Code: Select all

    Private Sub DoAutomaticAllocation()
        If _cashBook.CashBook Is Nothing Then Return

        Dim debtor As JiwaDebtors.Debtor = New JiwaDebtors.Debtor
        Dim transactions As List(Of DebtorTrans) = New List(Of DebtorTrans)()
        Dim transKey As String = ""

        For Each transaction As CashBookTransaction In _cashBook.CashBook.CashBookTransactions
            If Not transactions.Any(Function(x) x.DebtorCreditorAccountId = transaction.DebtorCreditorAccountID) Then
                debtor.Read(transaction.DebtorCreditorAccountID)
                debtor.ReadTransactions()
                transactions.AddRange(From trans As JiwaDebtors.DebtorTransaction In debtor.Transactions Select New DebtorTrans(transaction.DebtorCreditorAccountID, trans.RecID, trans.TranDate, trans.Amount, trans.AllocatedAmount))
            End If

            Dim amount = transaction.HomeAmount
            For Each trans As DebtorTrans In transactions.Where(Function(x) x.DebtorCreditorAccountId = transaction.DebtorCreditorAccountID And x.UnAllocatedAmount > 0).OrderBy(Function(x) x.TranDate).ToList()
                If amount = 0 Then Exit For
                Dim allocate = iif(amount < trans.UnAllocatedAmount, amount, trans.UnAllocatedAmount)
                _cashBook.CashBook.AddDebtorTransaction(trans.TransId, transKey)
                _cashBook.CashBook.AddAllocation(transaction.RecID, transKey, allocate, CashBook.CashBookAllocationTypes.e_CashBookAllocationTypeNormal)
                trans.AllocatedAmount += allocate
                amount -= allocate
            Next
        Next
        _cashBook.CashBook.Save()
    End Sub


This works beautifully. My only concern is a vague feeling that I have over engineered it and am manually doing stuff that you already have a routine for (and that I missed).

Cheers,
Neil.

[Mike.Sheen]

I have pursued this a little further and got it working.
...
This works beautifully. My only concern is a vague feeling that I have over engineered it and am manually doing stuff that you already have a routine for (and that I missed).

Glad you got it working.

You've not missed anything or over-engineered anything - your concerns in your earlier post about a private method I referenced "SearchDebtorTransaction" are unfounded - that's a method in the UI of the CashBook form to show a search window for the transactions unaged and not unallocated for a debtor - it merely allows the user to select a transaction - sorry for the confusion.

I like how you overcame the shortcoming of our lack of an IQueryable interface on the transactions collection to iterate the transactions in date order for only those not completely allocated - your solution is elegant.

If you do encounter performance issues at any point, you could replace the use of the debtor business logic to read the transactions and do a raw query to get the debtor transactions instead, but if you are a follower of the Church of Knuth, you'll not worry about that until the need arises (and I reckon that need won't arise).

[neil.interactit]

Thanks Mike,

Following some testing, I've made some minor tweaks (starting with HomeUnAllocatedAmount instead of HomeAmount, and filtering on DebitTransaction = True) ... for reference, here's the final code ...

Code: Select all

    Private Sub DoAutomaticAllocation()
        If _cashBook.CashBook Is Nothing Then Return

        Dim debtor As JiwaDebtors.Debtor = New JiwaDebtors.Debtor
        Dim transactions As List(Of DebtorTrans) = New List(Of DebtorTrans)()
        Dim transKey As String = ""

        For Each transaction As CashBookTransaction In _cashBook.CashBook.CashBookTransactions
            If Not transactions.Any(Function(x) x.DebtorCreditorAccountId = transaction.DebtorCreditorAccountID) Then
                debtor.Read(transaction.DebtorCreditorAccountID)
                debtor.ReadTransactions()
                transactions.AddRange(From trans As JiwaDebtors.DebtorTransaction In debtor.Transactions Where trans.DebitTransaction = True Select New DebtorTrans(transaction.DebtorCreditorAccountID, trans.RecID, trans.TranDate, trans.Amount, trans.AllocatedAmount))
            End If

            Dim amount = transaction.HomeUnAllocatedAmount
            For Each trans As DebtorTrans In transactions.Where(Function(x) x.DebtorCreditorAccountId = transaction.DebtorCreditorAccountID And x.UnAllocatedAmount > 0).OrderBy(Function(x) x.TranDate).ToList()
                If amount <= 0 Then Exit For
                Dim allocate = IIf(amount < trans.UnAllocatedAmount, amount, trans.UnAllocatedAmount)
                _cashBook.CashBook.AddDebtorTransaction(trans.TransId, transKey)
                _cashBook.CashBook.AddAllocation(transaction.RecID, transKey, allocate, CashBook.CashBookAllocationTypes.e_CashBookAllocationTypeNormal)
                trans.AllocatedAmount += allocate
                amount -= allocate
            Next
        Next
        _cashBook.CashBook.Save()
    End Sub


Cheers,
Neil.