0

I have users who are partners and users who are clients. When clients submit an order, they use a custom coupon code associated with each partner. (Each partner has a Mycodes field set within their profile.)

Previously, at the end of the month, we would download the sales into an Excel sheet and calculate the commissions of each partner using the partner's coupon codes.

Now I want to do this on the website using a view. I created a view for the coupons but I cannot compare the coupon code against the partner's Mycodes field. I have written the sql query in phpMyAdmin (one line with two JOIN), but I can not put that into views and filter that based on current userID:

SELECT
  uc_order_line_items.order_id AS OrderID,
  uc_orders.order_status AS Currently,
  uc_orders.created AS ThisDate,
  uc_orders.delivery_first_name AS Name,
  uc_orders.delivery_last_name AS Surname,
  uc_orders.delivery_phone AS Phone,
  uc_orders.primary_email AS Email,
  field_data_field_mycodes.entity_id AS UserID,
  uc_orders.order_total AS Total
FROM
  uc_order_line_items
JOIN
  field_data_field_mycodes
ON
  uc_order_line_items.title = field_data_field_mycodes.field_mycodes_value
JOIN
  uc_orders
ON
  uc_orders.order_id = uc_order_line_items.order_id
WHERE
  field_data_field_mycodes.entity_id=
6
  • 1
    Just to clarify, what is the field type of "Mycodes"? Is it a plain text or an entity reference? May 16, 2023 at 23:13
  • it can be anything that can link these two! I am open to any idea May 20, 2023 at 0:46
  • Okay, there's a few different ways to solve this problem. Looking at your SQL query, I'm confused at what you're looking for. May 22, 2023 at 17:13
  • Are you wanting a View that calculates the commission each partner gets? Or just to display the orders for each partner? Or all of the orders, grouped by each partner? May 22, 2023 at 17:14
  • Just the list of the orders of each partner would be enough. This is what I can not do in Views: where current user's mycodes = uc_order_line_items.title May 23, 2023 at 3:03

1 Answer 1

0

Thanks to Chris I tried that with 3 different base tables, but none worked. This might not be the answer but it is for the people who might read this article as an option. After spending more than 2 days, I decided to go with the craziest approach yet "doable": I created a login table in the database, wrote one login page and one report page in PHP (less than 650 lines of codes with all those sort and filter), and I was done in about 5 hours. Link to that login went into Drupal, and that was it. Drupal is a very great tool but I guess Views needs to add a free-hand SQL query, and, also a free-hand javascript insertion per page. these could really save programmers a lot saving hours of work...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.