[Spce-user] API internal server error 500

[Henk]

Hi,

Just started using the API to find out how it works and what can be done 
with it.
So I started with ngcp-credit-warning, added a few print statements and 
changed the customerbalance call in the main routine:

         my @contracts;
get_data(sprintf('/api/customerbalances/?domain=%s&prepaid=1*&status=active*',
             $cwarning->{domain}),
             'customerbalances',
             sub {
             .....

But this first change resulted right away in an internal server error 
500. As the documentation suggests this should be reported, this is the 
API log:
Oct  4 10:03:14 spce1 ngcp-panel: ERROR: IP=127.0.0.1 CALLED=API[GET 
200]/api/customerbalances/ TX=155A591AC89A5910 USER=system DATA={ 
'domain' => 'spce.test.nl', 'page' => '1', 'rows' => '10', 'status' => 
'active', 'prepaid' => '1'} MSG="" 
LOG="DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: 
DBD::mysql::st execute failed: _*Column 'status' in where clause is 
ambiguous*_ [for Statement "SELECT COUNT( * ) FROM (SELECT `me`.`id`, 
`me`.`customer_id`, `me`.`contact_id`, `me`.`order_id`, 
`me`.`profile_package_id`, `me`.`status`, `me`.`external_id`, 
`me`.`send_invoice`, `me`.`modify_timestamp`, `me`.`create_timestamp`, 
`me`.`activate_timestamp`, `me`.`terminate_timestamp`, 
`me`.`max_subscribers`, `me`.`subscriber_email_template_id`, 
`me`.`passreset_email_template_id`, `me`.`invoice_email_template_id`, 
`me`.`invoice_template_id`, `me`.`vat_rate`, `me`.`add_vat`, 
`billing_mappings`.`id` AS `billing_mappings__id`, 
`billing_mappings`.`start_date` AS `billing_mappings__start_date`, 
`billing_mappings`.`product_id` AS `billing_mappings__product_id` FROM 
`billing`.`contracts` `me` LEFT JOIN (select bm.contract_id,max(bm.id) 
as actual_bm_id from billing.billing_mappings bm inner join (select 
bmm.contract_id, max(bmm.start_date) max_start_date from 
billing.billing_mappings bmm where ( bmm.`end_date` >= ? OR 
bmm.`end_date` IS NULL ) AND ( bmm.`start_date` <= ? OR bmm.`start_date` 
IS NULL ) AND ( ? is null OR bmm.`contract_id` = ? ) group by 
bmm.contract_id ) bmm on bm.contract_id=bmm.contract_id and 
(bm.start_date=bmm.max_start_date or (bmm.max_start_date is null and 
bm.start_date is null)) group by bm.contract_id) 
`billing_mappings_actual` ON `billing_mappings_actual`.`contract_id` = 
`me`.`id` LEFT JOIN `billing`.`billing_mappings` `billing_mappings` ON 
`billing_mappings`.`id` = `billing_mappings_actual`.`actual_bm_id` LEFT 
JOIN `billing`.`billing_profiles` `billing_profile` ON 
`billing_profile`.`id` = `billing_mappings`.`billing_profile_id` LEFT 
JOIN `billing`.`voip_subscribers` `voip_subscribers` ON 
`voip_subscribers`.`contract_id` = `me`.`id` LEFT JOIN 
`billing`.`domains` `domain` ON `domain`.`id` = 
`voip_subscribers`.`domain_id` WHERE ( ( `billing_profile`.`prepaid` = ? 
AND `domain`.`domain` = ? AND `me`.`status` != ? AND `status` = ? ) ) 
GROUP BY `me`.`id`, `me`.`customer_id`, `me`.`contact_id`, 
`me`.`order_id`, `me`.`profile_package_id`, `me`.`status`, 
`me`.`external_id`, `me`.`send_invoice`, `me`.`modify_timestamp`, 
`me`.`create_timestamp`, `me`.`activate_timestamp`, 
`me`.`terminate_timestamp`, `me`.`max_subscribers`, 
`me`.`subscriber_email_template_id`, `me`.`passreset_email_template_id`, 
`me`.`invoice_email_template_id`, `me`.`invoice_template_id`, 
`me`.`vat_rate`, `me`.`add_vat`, `billing_mappings`.`id`, 
`billing_mappings`.`start_date`, `billing_mappings`.`product_id`) `me`" 
with ParamValues: 0='2018-10-04 10:03:14', 1='2018-10-04 10:03:14', 
2=undef, 3=undef, 4=1, 5="spce.voipdigit.nl", 6='terminated', 
7="active"] at /usr/share/perl5/NGCP/Panel/Role/API.pm line 617"

Changing the code to
get_data(sprintf('/api/customerbalances/?domain=%s&prepaid=1&*contracts*.status=active',
works, but shouldn't be necessary. Tried this in mr5.5.5 and in mr6.4.1

Best regards,

Henk Plessius

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/mailman/private/spce-user_lists.sipwise.com/attachments/20181004/713ae251/attachment.html>