NCG Co-op Deals GS1 Databar coupon

[flathat]

• Version of CORE? Current

• Issue with Office, Lane, or both? Lane

• Is this [mostly] a bug report, feature request, or question? Question

If you are using the Co+op Deals coupons that look like the below could you comment on what you did to support them in CORE-POS? CORE-POS Wiki: GS1 Databar Support

[gohanman]

DatabarCoupon needs to be enabled if it isn't already, and then the barcode symbology needs to be enabled on the scale. I believe it was "Databar Expanded". Possibly you also need "Databar Omnidirectional".

[flathat]

Re enabling, this store's scanner/scales are Datalogic Magellan 9400i's. PDF p 354 of the 9300i/9400i Product Reference says re GS1 DataBar Expanded "This feature is a factory-programmed option. Contact your dealer about upgrading ..." I asked Datalogic technical support about the configuration of the particular units at this store since I didn't recall asking for the Databar Expanded option when we ordered them. Datalogic responded that they should be able to read GS1 Expanded. I don't know whether this was because of special configuration before purchase or whether that is in fact normal for 9300i/9400i. They also supplied some codes to Disable Coupon Filtering and Enable Databar Expanded. I've attached those codes to this post. At this point we're able to read the coupon in the picture above and CORE-POS handles it correctly; I did make, and commit here, one fix to DatabarCoupon.php to correct syntax of two of the error messages and another one to supply a missing value for this type of coupon.

GS1_Databar_Expanded_Enabled-Coupon_Filter_Disabled.pdf

[flathat]

I did my learning about GS1 in CORE without direct access to the scanner/scale that we were using to read them. I was able to use a hand scanner, a Zebra Li2208, to read the GS1 barcode just to see what it consisted of. I had a CORE-POS lane installation to work with directly. CORE-POS won't understand the barcode just as read by the Li2208 but will if you prefix the codes that are supplied by the Magellan scanner/scale and the CORE-POS driver, so if you paste:
GS1~RX811050860006354409292341000410002000324022996000
or type
GS1~RX and then hand-scan the barcode into the CORE-POS input box
it will show you what happens after any problems you're having with your scanner/scale capability or enablement are sorted. Hopefully:

[flathat]

Something that DatabarCoupon.php does not do for this type of coupon, whose only requirement is a certain minimum transaction value, but does do for other types of coupons, is prevent it being used more than once in the transaction. It returns before the already-applied check:

L#458
        /* simple case first; just wants total transaction value
           no company prefixing
        */
        if ($req->code == 2) {
            return $this->validateTransactionTotal($req, $json);
        }

I suggest doing this instead. Removing the test above and letting it proceed to here and add a branch for code '2:

        switch($req->code) {
            case '0': // various qtty requirements
            case '3':
            case '4':
                return $this->validateQty($row['qty'], $row['couponqtty'], $req, $json);
            case '1':
                return $this->validateQty($row['total'], $row['couponqtty'], $req, $json);
            case '2':
                if ($row['couponqtty'] > 0) {
                    $json['output'] = DisplayLib::boxMsg(_("Coupon already applied"));
                    $req->valid = false;
                    return array($req, $json);
                }
                return $this->validateTransactionTotal($req, $json);

I also tried this by jiggering the parameters to $this->validateQty() which is used to make that check for other types of coupons but it seemed unnecessarily complex if this way does what is needed, both the already-applied and transaction-value tests. I've tried it and it seems to.

[gohanman]

I'm not sure that works because the coupon UPC has way more than 13 characters and a fair bit of it gets lost along the way. There's no way to say for sure whether the current 40+ character UPC is the same as the 13 character fragment in the existing transaction. The existing quantity check isn't whether a specific coupon has been used more than once - it's comparing counts of items & coupons with the same prefixes (this might also be invalid in some situations).

[flathat]

Since the message is displayed only for coupons from that manufacturer it doesn't seem very likely there would be a false hit unless there could be more than one coupon from that manufacturer in the transaction. So, the query could be changed to use the whole upc for the coupon and not just the prefix.

In that case, and because of another issue, below, I'd be inclined to restore the block based on $req->code == 2 and do this, which is a bit messy because it needs things that are otherwise defined later in the flow:

        /* simple case first; just wants total transaction value
         *  no company prefixing of items.
         * First check for "already applied".
        */
        if ($req->code == 2) {
            /* Compose the coupon upc value from the prefix and
             * base-36 version of the offer code.
             */
            $upcStart = "0" . $req->prefix;
            $offer = base_convert($this->offerCode,10,36);
            $remaining = 13 - strlen($upcStart);
            if (strlen($offer) < $remaining) {
                $offer = str_pad($offer,$remaining,'0',STR_PAD_LEFT);
            } elseif (strlen($offer) > $remaining) {
                $offer = substr($offer,0,$remaining);
            }
            $couponUPC = $upcStart.$offer;
            // See if the coupon has already been applied.
            $dupQ = "SELECT sum(CASE WHEN trans_status='C' THEN 1 ELSE 0 END) as couponqtty
                FROM localtemptrans
                WHERE upc = ?";
            $dupS = $dbc->prepare($dupQ);
            $dupR = $dbc->execute($dupS, array($couponUPC));
            if ($dbc->numRows($dupR) > 0) {
                $dupRow = $dbc->fetchRow($dupR);
                if ($dupRow['couponqtty'] != null && $dupRow['couponqtty'] > 0) {
                    $json['output'] = DisplayLib::boxMsg(
                    _("Coupon already applied"),
                    '', false, DisplayLib::standardClearButton());
                    $req->valid = false;
                    return array($req, $json);
                }
            }

            $req->department = 0;
            return $this->validateTransactionTotal($req, $json);
        }

There is another issue in validateRequirements() around this test:

        /* Totals and values from coupon and non-coupon items
         *  with upc's that match the company prefix (brand).
         */
        $query = sprintf("SELECT
            max(CASE WHEN trans_status<>'C' THEN unitPrice ELSE 0 END) as price,
            sum(CASE WHEN trans_status<>'C' THEN total ELSE 0 END) as total,
            max(department) as department,
            sum(CASE WHEN trans_status<>'C' THEN ItemQtty ELSE 0 END) as qty,
            sum(CASE WHEN trans_status='C' THEN 1 ELSE 0 END) as couponqtty
            FROM localtemptrans WHERE
            substring(upc,2,%d) = '%s'",
            strlen($req->prefix),$req->prefix);
        $result = $dbc->query($query);

        if ($dbc->numRows($result) <= 0) {
            $json['output'] = DisplayLib::boxMsg(_("Coupon requirements not met"));
            return array($req, $json);
        }

I wondered why this message didn't appear if there were no items for the issuer in the transaction. At least in the three MySQL instances I've tried that search returns a row of NULLs rather than an empty set, so it never fails the numRows <= 0 test. Once the block is changed to test for NULLs it shouldn't fire for this NCG coupon where there won't be items for the manufacturer and so the coupon would never validate. A more informative message might be "No items for the issuer of this coupon."

        /* If there are no prefix matches it returns a row of NULLs
         *   not an empty set.
         */
        if ($dbc->numRows($result) <= 0) {
            $json['output'] = DisplayLib::boxMsg(
                _("Coupon requirements not met"),
                '', false, DisplayLib::standardClearButton());
            return array($req, $json);
        }
        // Check for a row of NULLs
        $row = $dbc->fetchRow($result);
        if ($row['price'] == null && $row['total'] == null &&
                $row['department'] == null && $row['qty'] == null &&
                $row['couponqtty'] == null) {
            $json['output'] = DisplayLib::boxMsg(
                _("Coupon requirements not met"),
                '', false, DisplayLib::standardClearButton());
            return array($req, $json);
        }
        $req->price = $row['price'];
        $req->department = $row['department'];

This is a reference for the structure of DataBar coupons: North American Coupon Application Guideline Using GS1 DataBar Expanded Symbols

[gohanman]

I tried out that query on a newer MySQL instance and also got back a row of NULLs so that seems like a good change.

The issue with the coupon just requiring a transaction total is there is no manufacturer at all. The spec says they have to put something in the primary GS1 company prefix field, but it doesn't matter what since it's not being matched against any items. If there are any best practices / common practices for different entities to issue coupons with the same prefix. I think it's basically a given that this style of coupon will only be used in ways like the example in this issue by entities that don't have official company prefixes. Anyone actually selling barcoded goods would almost certainly want to require the customer purchase some of their product.