[Spce-user] Invoice sending with generate_invoices.pl

[Irina Peshinskaya]

Dear Oleg,

As the most important I want to start from the question: what is the 
version of your NGCP?
Please, be aware that from version 4.0+ using generate_invoices.pl may 
(and will) damage your billing data. Script run is disabled by default.

Meanwhile, supposing that you use LTS version 3.8, could you please 
share result of the modified sql:
select  ctr.id as cliid, inv.id as invid,cnt.id as cntid, 
cnt.reseller_id,  date_format(period_start,'%Y%m') as inici, 
date_format(period_end,'%Y%m') as fi, round(amount_total,2) as 
total,         sent_date as enviat, cnt.email as email, 
concat(cnt.firstname,' ',cnt.lastname) as customer from 
billing.contracts as ctr left join billing.contacts as cnt on 
ctr.contact_id = cnt.id left join billing.invoices as inv on ctr.id = 
inv.contract_id where         ctr.status='active' and ctr.external_id is 
not null and inv.period_start >= '2017-01-01 00:00:00' and 
inv.period_end <= '2017-02-01 00:00:00' order by ctr.id;

You see, I added reseller_id and contact.id as contact email may be the 
same for all contacts.

Best regards,
Irina Peshinskaya

On 01.03.2017 12:49, Oleg wrote:
> Hello,
>
> I have noticed that "generate_invoices.pl --prevmonth --sendonly" 
> sends PDF files to email addresses of other clients. It creates the 
> PDFs correctly, but when sending them, for some reason, it looks as if 
> there might be an error in how the list of invoices to be sent is 
> generated.
>
> Example
> -------
>
> Mar  1 04:05:16 ngcp-panel: DEBUG: send email for: 
> provider_contact_id=2;client_contact_id=38; 
> client_contact->email=anonymous at anonymised.com; 
> invoice_ids=125,126,127,128,120,129,130,131,132,133,134,135,136,137,138;
>
> Client with contact_id=38 receives invoice_id=125, while it really has 
> invoice_id=124 assigned. A SQL query 
> (https://gist.github.com/anonymous/bec2ad716ed4b71ca1c9fbb94138f296) 
> gets me the following output for this client:
>
> +-------+-------+--------+---------------------+--------------------------+-------------------+ 
>
> | cliid | invid | total  | sent                | 
> email                    | customer          |
> +-------+-------+--------+---------------------+--------------------------+-------------------+ 
>
> |    33 |   124 |   5.87 | 2017-03-01 04:05:16 | 
> anonymous at anonymised.com | Anonymous Client  |
> +-------+-------+-------+----------------------+--------------------------+-------------------+ 
>
>
> I wouldn't know how to reproduce this on an empty db, nor have I been 
> able to establish if the problem is in the generate_invoice.pl or in 
> the database data somewhere.
>
> Appreciate any help I can get on how to debug this further.
>
>
>
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> https://lists.sipwise.com/listinfo/spce-user