[Spce-user] Wrong billing profile

[Henk]

I'm on mr5.0.1.1 and have the wrong billing profile on my (manually) 
generated invoices.
After some research I found the date selection on billing_mappings isn't 
working.
 From the sql query log:

|
select 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fselect.html> 
* FROM billing_mappings where contract_id = '17' and 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Flogical-operators.html#operator_and> 
(billing_mappings.start_date <= '1485903599' OR 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Flogical-operators.html#operator_or> 
billing_mappings.start_date is 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fcomparison-operators.html#operator_is> 
null) and 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Flogical-operators.html#operator_and> 
(billing_mappings.end_date >= '1483225200' OR 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Flogical-operators.html#operator_or> 
billing_mappings.end_date is 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fcomparison-operators.html#operator_is> 
null) order by billing_mappings.start_date desc, billing_mappings.id 
desc limit 1
|
there are 3 mappings for contract 17, but the wrong one with NULL dates 
is selected. To get the right one, I have to change the query to:

|
select 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fselect.html> 
* FROM billing_mappings where contract_id = '17' and 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Flogical-operators.html#operator_and> 
(billing_mappings.start_date <= from_unixtime('1485903599') OR 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Flogical-operators.html#operator_or> 
billing_mappings.start_date is 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fcomparison-operators.html#operator_is> 
null) and 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Flogical-operators.html#operator_and> 
(billing_mappings.end_date >= from_unixtime('1483225200') OR 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Flogical-operators.html#operator_or> 
billing_mappings.end_date is 
<https://spce.voipdigit.nl:4443/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fcomparison-operators.html#operator_is> 
null) order by billing_mappings.start_date desc, billing_mappings.id desc

Regards,

Henk Plessius
|
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/mailman/private/spce-user_lists.sipwise.com/attachments/20170115/0fa55a44/attachment.html>