[Spce-user] Error generating invoice

Tue Dec 5 06:27:58 EST 2017

Thx4re, makes absolute sense. We had to clarify if this was related to recent changes/fixes in the rating engine.

Regarding your situation and billing profile packages, as you correctly found out

-          Packages are the dedicated feature to use dynamic balance intervals (eg. required to bill prepaid per voucher top-up period)

-          They also are the dedicated option to use balance intervals other than „1 month“; eg. some customers are required to drive billing runs in

strict 30day intervals, or align to 1st of month etc.

Packages are proven to work, what you experienced here is just an unfinished leftover when the billing package feature was introduced.

There weren’t any changes on those parts recently.

Right now, the month selector in the UI form for creating an invoice addresses the regular „1month“ intervals, as in use w/o packages.

The db queries behind fetches the first contract balance that is *fully* enclosed by the picked month, which works for „1month“ intervals starting on 1st of month (thats also how you fixed it).

For packages the plan was to explicitly pick from exisiting balance intervals records, but that didnt made it for a final requirement yet.

So you can continue to use packages, but for now if invoice creation is obligatory, the intervals should be kept „1 month“ and starting at „1st of month“.

Also be aware when changing the blance records‘ start and end fields manually in the database, the remaining fields are not adjusted. There is the risk it screws up

in other use cases (like changing the contract‘s package/profile) and apart of that this particular invoice pdf will most likely not show correct information.

Will create a ticket to address the issue by introducing a better invoice period selection method in the UI form+api, so it can also work for dynamic intervals of packages.

Btw. since you seem to use it in real life, suggestions are welcome.

regards

Von: Henk [mailto:henk at voipdigit.nl] 
Gesendet: Dienstag, 5. Dezember 2017 09:23
An: Rene Krenn <rkrenn at sipwise.com>; spce-user at lists.sipwise.com
Betreff: Re: AW: [Spce-user] Error generating invoice

Hi Rene,

I have been using them just as an experiment and all that was left over was the balance period not starting at the beginning of the month. I changed the dates in records 453 and 519. I'm not sure when the problem started, I started with a 4.x version.

| 372 | 2017-02-13 03:00:00 | 2017-02-13 03:59:59 |
| 373 | 2017-02-13 04:00:00 | 2017-02-13 04:59:59 |
| 374 | 2017-02-13 05:00:00 | 2017-02-13 05:59:59 |
| 375 | 2017-02-13 06:00:00 | 2017-02-13 06:59:59 |
| 376 | 2017-02-13 07:00:00 | 2017-02-13 07:59:59 |
| 377 | 2017-02-13 08:00:00 | 2017-02-13 08:59:59 |
| 378 | 2017-02-13 09:00:00 | 2017-02-13 09:59:59 |
| 379 | 2017-02-13 10:00:00 | 2017-02-13 10:59:59 |
| 380 | 2017-02-13 11:00:00 | 2017-03-13 10:59:59 |
| 389 | 2017-03-13 11:00:00 | 2017-04-13 10:59:59 |
| 399 | 2017-04-13 11:00:00 | 2017-05-13 10:59:59 |
| 409 | 2017-05-13 11:00:00 | 2017-06-13 10:59:59 |
| 417 | 2017-06-13 11:00:00 | 2017-07-13 10:59:59 |
| 426 | 2017-07-13 11:00:00 | 2017-08-13 10:59:59 |
| 433 | 2017-08-13 11:00:00 | 2017-09-13 10:59:59 |
| 442 | 2017-09-13 11:00:00 | 2017-10-13 10:59:59 |
| 453 | 2017-10-13 11:00:00 | 2017-10-31 23:59:59 |
| 519 | 2017-11-01 00:00:00 | 2017-11-30 23:59:59 |
| 542 | 2017-12-01 00:00:00 | 2017-12-31 23:59:59 |
+-----+---------------------+---------------------+

Best regards,

Henk Plessius

On 4-12-2017 22:12, Rene Krenn wrote:

Thx, so is it correct you are or have been using billing packages for this customer contract? Would you like to send results for

  select start,end from billing.contract_balances where contract_id=<that contractId> order by id desc;

and

  select * from billing.contracts where id = <that contractId>;

(won‘t expose any sensitive data) 

regards

Von: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] Im Auftrag von Henk
Gesendet: Montag, 4. Dezember 2017 20:26
An: spce-user at lists.sipwise.com <mailto:spce-user at lists.sipwise.com> 
Betreff: Re: [Spce-user] Error generating invoice

Hi Rene,

An update on my last email, it's working now! I changed the dates to begin/end of month and then generate the invoice for December first. After that I can generate November too. So fixed for now, but it looks like it is caused by not using a monthly period (in the past).

Regards,

Henk Plessius

On 4-12-2017 19:54, Henk wrote:

Hi Rene,

Thank you for the research. I'm entering a valid invoice period, just 11-2017 with a customer that is active for 1 year. I have found that at least two of the customers with failing invoices have an interval that is not from 1st to last of the month, so for example contract balance runs from 2017-11-13 11:00:00 to 2017-12-13 10:59:59. But after I changed the interval in billing.contract_balances to the start and end of month, I have still the same error.

Regards,

Henk Plessius

On 4-12-2017 16:24, Rene Krenn wrote:

hello Henk,

thanks for pointing to this. I was able to reproduce the exact same message, but only if i was trying to enter an invoice period (month+year) when that customer contract was not created yet. Can you check if this was also the case in your situation?

regards, rene

Von: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] Im Auftrag von Henk
Gesendet: Freitag, 1. Dezember 2017 13:25
An: spce-user at lists.sipwise.com <mailto:spce-user at lists.sipwise.com> 
Betreff: Re: [Spce-user] Error generating invoice

Hi Irina,

Sorry I missed that. Now the first invoice works, but the second has another error:

MSG="Failed to create invoice ({UNKNOWN}: Can't call method "start" on an undefined value at /usr/share/perl5/NGCP/Panel/Utils/Invoice.pm line 61. at /usr/share/perl5/Catalyst/Model/DBIC/Schema.pm line 526#012)" LOG=""

Regards,

Henk

On 1-12-2017 12:19, Irina Peshinskaya wrote:

Dear Henk,

Just the same as was described yesterday, kindly upgrade your ngcp-panel to the 5.5.1.5:

    apt-get update
    apt-get install ngcp-panel
    ngcpcfg apply 'commit mesasge'

    or better update to latest hotfixes:

    apt-get update && apt-get upgrade && \
    ngcp-update-db-schema && ngcp-update-cfg-schema && \
    ngcpcfg apply 'commit message'

Best regards,

Irina Peshinskaya

On 01.12.2017 13:14, Henk wrote:

Hi all,

I'm on the latest version mr5.5.1 and cannot generate invoices anymore. In panel.log I get the following message:

MSG="Failed to create invoice .... Unknown column 'me.source_cli_suppression_id' in 'field list' [for Statement "SELECT `me`.`source_user`, `me`.`source_domain`, `me`.`source_cli`, `me`.`destination_user_in`, `me`.`source_cli_suppression_id`, `me`.`destination_user_in_suppression_id`, `me`.`start_time`, `me`.`duration`, `me`.`call_type`, `me`.`source_customer_cost`, `source_customer_billing_zones_history`.`zone`, `source_customer_billing_zones_history`.`detail` FROM `accounting`.`cdr` `me` LEFT JOIN `billing`.`billing_zones_history` `source_customer_billing_zones_history` ON `source_customer_billing_zones_history`.`id` = `me`.`source_customer_billing_zone_id` WHERE ( ( `call_status` = ? AND `source_account_id` = ? AND `source_user_id` != ? AND ( `start_time` >= ? AND `start_time` <= ? ) ) ) ORDER BY `start_time`" with ParamValues: 0='ok', 1="26", 2='0', 3=1509494400, 4=1512086399] at /usr/share/perl5/NGCP/Panel/Utils/Invoice.pm line 84#012)" LOG=""

My current DB version is 15367. Did I miss a part of the DB upgrade?

Regards,

Henk Plessius

_______________________________________________
Spce-user mailing list
Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> 
https://lists.sipwise.com/listinfo/spce-user

_______________________________________________
Spce-user mailing list
Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> 
https://lists.sipwise.com/listinfo/spce-user

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20171205/d037f922/attachment-0001.html>