[Spce-user] API internal server error 500

[Rene Krenn]

Thx, going to be fixed (6.5).

regards

 

Von: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] Im Auftrag von Henk
Gesendet: Donnerstag, 4. Oktober 2018 11:02
An: spce-user at lists.sipwise.com
Betreff: [Spce-user] API internal server error 500

 

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/pipermail/spce-user_lists.sipwise.com/attachments/20181004/0797d92f/attachment-0001.html>