Api Create Service When Defining Coupon Broken

[Jonathan]

Currently when using the API to create a service with a coupon ID defined it will fail to import.  I've managed to identify why and come up with a fix for my scenario.

 

Error example:

stdClass Object
(
    [error] => stdClass Object
        (
            [message] => An unexpected error occured.
            [response] => SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''(60)' AND `coupons`.`id` = '1' GROUP BY `coupons`.`id`' at line 1
        )
)

Here's an example of my $vars array:

Array
(
    [vars] => Array
        (
            [package_group_id] => 1
            [pricing_id] => 178
            [client_id] => 4
            [status] => active
            [coupon_id] => 1
            [override_price] =>
            [override_currency] =>
            [module_row_id] => 1
            [date_canceled] =>
            [date_added] => 2013-08-15 00:00:00
            [date_renews] => 2014-11-15 00:00:00
            [use_module] => false
            [quantity] => 1
            [ip] => 1.2.3.4
            [hostname] => removed.com
            [configoptions] => Array
                (
                    [625] => No
                    [618] => No
                    [619] => No
                    [620] => No
                    [624] => Yes
                    [613] => No
                    [623] => Yes
                    [615] => Yes
                    [622] => No
                    [616] => No
                    [617] => No
                    [621] => No
                    [612] => No
                )
        )
    [packages] => 60
)

As you can see, I have coupon_id defined to "1".  This generates the following query to MySQL when actually running the API call:

SELECT `coupons`.`id`, `coupons`.`code`, `coupons`.`company_id`, `coupons`.`used_qty`, `coupons`.`max_qty`, `coupons`.`start_date`, `coupons`.`end_date`, `coupons`.`status`, `coupons`.`type`, `coupons`.`recurring`, `coupons`.`limit_recurring` FROM `coupons` INNER JOIN `coupon_packages` ON `coupon_packages`.`coupon_id` = `coupons`.`id` WHERE `coupons`.`company_id` = '1' AND `coupons`.`status` = 'active' AND (`coupons`.`max_qty` = '0' OR `coupons`.`max_qty` > `coupons`.`used_qty`) AND (`coupons`.`start_date` <= '2014-10-13 17:32:33' OR `coupons`.`start_date` IS NULL) AND (`coupons`.`end_date` > '2014-10-13 17:32:33' OR `coupons`.`end_date` IS NULL) AND `coupon_packages`.`package_id` IN '60' AND `coupons`.`id` = '1' GROUP BY `coupons`.`id`

Now there's one big problem with this query.  It will fail because the very last "IN" statement isn't valid.

AND `coupon_packages`.`package_id` IN '60' AND `coupons`.`id` = '1' GROUP BY `coupons`.`id`

When using IN, the values have to be comma-separated within parentheses.  When correcting this last part of the query to this it works:

AND `coupon_packages`.`package_id` IN ('60') AND `coupons`.`id` = '1' GROUP BY `coupons`.`id`

Now the fix:

 

In your apps/models/coupons.php around line 153:

 

Change

if ($packages) {
$this->Record->innerJoin("coupon_packages", "coupon_packages.coupon_id", "=", "coupons.id", false)->
where("coupon_packages.package_id", "in", $packages)->
group("coupons.id");
}

to

if ($packages) {
$this->Record->innerJoin("coupon_packages", "coupon_packages.coupon_id", "=", "coupons.id", false)->
where("coupon_packages.package_id", "=", $packages)->
group("coupons.id");
}

The change is from "in" to "=" on the 3rd line of this code.

[Tyson]

Actually, the current behavior is correct. The issue you ran into is caused by the value you passed with 'packages'. As described in Services::add, 'packages' must be set to a numerically-indexed array of package IDs--not an integer. I'd recommend you update your API input to pass an array of package IDs, and undo the code change to Blesta, otherwise the system will not properly handle Inclusive coupons that apply only to multiple packages.

[Jonathan]

Well that explains a lot. How embarrassing

 

Thanks

[Tyson]

I'll close this thread as not a bug.