[Question] Does Accounting module needs setup?

[z0lo13]

Description

Hello, When i create an invoice and then add 'payment in' to that invoice on the same amount the status remains unpaid. Should we finish the implementation by ourselves or it is already in the system?

[davide-alghi]

it needs a WF w/ Update related record

[mariuszkrzaczkowski]

System version? check on gitdeveloper.yetiforce

[davide-alghi]

I just tried on gitdev and behaviour is the same.

moreover in YF 5.2+ pay-in status is locked; until YF 5.1 it was editable.

[z0lo13]

@mariuszkrzaczkowski gitdeveloper or gitstable, i guess it is not set up out of box i cannot change the status of payment as well on my 5.2.0 test enviroment proof:

Even if enable change through db and change to paid status on invoice not updated.

https://github.com/YetiForceCompany/YetiForceCRM/blob/414d26130508f0628d84ef789ab985859c453ada/modules/PaymentsIn/models/PaymentStatus.php The logic from this PaymentsIn_PaymentStatus_Model Class does not work

[manwenick]

The same problem here. New 5.2.0 version with the last ServicePack version. Maybe this plays a role?

https://stackoverflow.com/questions/5076710/what-is-float-in-java

As @z0lo13 mentioned /modules/PaymentsIn/models/PaymentStatus.php on line 109 there is a value 0.0 without f at the end.

[apcloic]

FYI, I'm using YF 5.2.0 v18, invoice status is properly updated as long as WF is enabled and cron is working. I only have a small issue where an invoice is marked as underpaid while the amount of the payment in is strictly equal to the invoice total (both with and without tax).

[manwenick]

Hi guys! Yesterday I was wondering, but it works. Not perfect, but who is perfect? :-) I understand the workflow this way:

1. Your create a new invoice, which contains following amounts: Net (quantity unit price) - Discount = Price after Discount + Tax (Net tax Value) = Gross
2. We also have sale date and due date First one is not necessary in my case (but I think there is a lot of use cases where you can use it. If your sale order was started on 01.12.2019 and you have employee who is responsible for issuing the invoices start with this one on 03.12.2019, so it could be great (as a customer) to know which sales order date is meant. Because meanwhile the customer hopefully made another one sales order) The second one date: due date: if I understand right this is the latest date, when the customer needs to pay his final amount of this invoice.

I have few e.g.: 1st eg:

• The sales order (contains no discounts) is created on 01.12.2019
• The invoice has been issued on 01.12.2019 (the due date = 01.12.2019)
• The payment is created on 01.12.2019 (whole amount was provided by one payment) --> payment status (after workflow processed it, I think it works every 15 mins) will be changed to PAID --> This is one of standard use cases we have. In my system it works perfekt.

2nd e.g.:

• The sales order (contain no discounts) is created on 01.12.2019
• The invoice has been issued on 01.12.2019 (the due date = +14 days = 15.12.2019)
• The payment is created on 10.12.2019 (whole amount was provided by one payment) --> payment status on 01-10.12.2019 NOT PAID, on 10.12.2019 = PAID

3d e.g.:

• The sales order (does contain a discount!) is created on 01.12.2019
• The invoice has been issued on 01.12.2019 (the due date = 01.12.2019) Amount: Net 100,- Discount 10% = Gross 90,-
• The payment amount was provided by two payments. First payment of amount 40,- is created on 01.12.2019, the second payment of amount 50,- is created also on 01.12.2019 (--> payment status (after workflow processed it, I think it works every 15 mins) changed to UNDERPAID --> i think somethink is not stable in the code. Because the Discout amount seems not to be considered.

I have another examples, but it could be great to know from Yetiforce team @mariuszkrzaczkowski if there is some documentation about it? Actualy I would like to help to ceate somethink like this for end user. Maybe somethink like wiki? Where we can test the workflows with examples, create a pages with discription and there need to have a status (approved by YetiForce) or somethink like this? What do you think guys?

[z0lo13]

@manwenick thank you for your report, however it is not working in my instance and giving an error that column vtiger_paymentsin.paymentsvalue not exist on 5.3.0 SP3, looks like a typo as changing to paymentsvalue in /modules/PaymentsIn/models/PaymentStatus.php fixes the error and everything works fine. @mariuszkrzaczkowski please check if it is same error exists on gitdeveloper and gitstable, as i was not able to make the Model work in there by repeating same steps from manwenick reply.

the error log

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vtiger_paymentsin.paymentsvalue' in 'field list' in /home/lastmin/public_html/vendor/yetiforce/yii2/db/Command.php:259
Stack trace:
#0 /home/lastmin/public_html/vendor/yetiforce/yii2/db/Command.php(259): PDO->prepare('SELECT SUM(vtig...')
#1 /home/lastmin/public_html/vendor/yetiforce/yii2/db/Command.php(1153): yii\db\Command->prepare(true)
#2 /home/lastmin/public_html/vendor/yetiforce/yii2/db/Command.php(425): yii\db\Command->queryInternal('fetchColumn', 0)
#3 /home/lastmin/public_html/app/Db/Query.php(143): yii\db\Command->queryScalar()
#4 /home/lastmin/public_html/vendor/yetiforce/yii2/db/Query.php(372): App\Db\Query->queryScalar('SUM(vtiger_paym...', NULL)
#5 /home/lastmin/public_html/modules/PaymentsIn/models/PaymentStatus.php(91): yii\db\Query->sum('vtiger_payments...')
#6 /home/lastmin/public_html/modules/PaymentsIn/models/PaymentStatus.php(64): PaymentsIn_PaymentStatus_Model::getSumOfPaymentsByRecordId(949977, 'FInvoice')
#7 /home/lastmin/public_html/app/BatchMethod.php(99): PaymentsIn_PaymentStatus_Model::updatePaymentStatus(949977)
#8 /home/lastmin/public_html/modules/Vtiger/crons/BatchMethods.php(24): App\BatchMethod->execute()
#9 /home/lastmin/public_html/cron.php(89): Vtiger_BatchMethods_Cron->process()
#10 {main}

Next yii\db\Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vtiger_paymentsin.paymentsvalue' in 'field list'
Failed to prepare SQL: SELECT SUM(vtiger_paymentsin.paymentsvalue) FROM (SELECT DISTINCT `vtiger_paymentsin`.`paymentsin_status` AS `paymentsin_status`, `vtiger_paymentsin`.`relatedid` AS `relatedid`, `vtiger_paymentsin`.`invoice_rel` AS `invoice_rel`, `vtiger_paymentsin`.`paymentsvalue` AS `paymentsvalue`, `vtiger_paymentsin`.`pay_date` AS `pay_date`, `vtiger_paymentsin`.`payment_mode` AS `payment_mode`, `vtiger_crmentity`.`smownerid` AS `assigned_user_id`, `vtiger_crmentity`.`createdtime` AS `createdtime`, `vtiger_crmentity`.`modifiedtime` AS `modifiedtime`, `vtiger_paymentsin`.`paymentsno` AS `paymentsno`, `vtiger_paymentsin`.`paymentsinid` AS `id` FROM `vtiger_paymentsin` INNER JOIN `vtiger_crmentity` ON vtiger_paymentsin.paymentsinid = vtiger_crmentity.crmid INNER JOIN `vtiger_crmentityrel` ON (vtiger_crmentityrel.relcrmid = vtiger_crmentity.crmid OR vtiger_crmentityrel.crmid = vtiger_crmentity.crmid) WHERE (`vtiger_crmentity`.`deleted`=:qp0) AND (((`vtiger_crmentityrel`.`crmid`=:qp1) OR (`vtiger_crmentityrel`.`relcrmid`=:qp2)) AND (`vtiger_paymentsin`.`paymentsin_status`=:qp3))) `c` in /home/lastmin/public_html/vendor/yetiforce/yii2/db/Command.php:264
Stack trace:
#0 /home/lastmin/public_html/vendor/yetiforce/yii2/db/Command.php(1153): yii\db\Command->prepare(true)
#1 /home/lastmin/public_html/vendor/yetiforce/yii2/db/Command.php(425): yii\db\Command->queryInternal('fetchColumn', 0)
#2 /home/lastmin/public_html/app/Db/Query.php(143): yii\db\Command->queryScalar()
#3 /home/lastmin/public_html/vendor/yetiforce/yii2/db/Query.php(372): App\Db\Query->queryScalar('SUM(vtiger_paym...', NULL)
#4 /home/lastmin/public_html/modules/PaymentsIn/models/PaymentStatus.php(91): yii\db\Query->sum('vtiger_payments...')
#5 /home/lastmin/public_html/modules/PaymentsIn/models/PaymentStatus.php(64): PaymentsIn_PaymentStatus_Model::getSumOfPaymentsByRecordId(949977, 'FInvoice')
#6 /home/lastmin/public_html/app/BatchMethod.php(99): PaymentsIn_PaymentStatus_Model::updatePaymentStatus(949977)
#7 /home/lastmin/public_html/modules/Vtiger/crons/BatchMethods.php(24): App\BatchMethod->execute()
#8 /home/lastmin/public_html/cron.php(89): Vtiger_BatchMethods_Cron->process()
#9 {main}
Additional Information:
Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'vtiger_paymentsin.paymentsvalue' in 'field list'
)

[mariuszkrzaczkowski]

show query result:

SHOW FULL FIELDS FROM `vtiger_paymentsin`; 

[z0lo13]

@mariuszkrzaczkowski

[rskrzypczak]

You have wrong relationship between Invoice and Payment modules. You should have 1:M and you have M:M You've probably changed it yourself and you have a mistake. You need to be careful with relationships