[#3654] Substitutions in emails

ricco1
OFFLINE
Posts: 310
Thanks: 8
Karma: -7

8 years 10 months ago - 8 years 10 months ago #276884 by ricco1

Replied by ricco1 on topic Substitutions in emails

Now it comes the fun(hard) part. :whistle:

Could you share, for example, the query that takes the value of the address_street column and populates the cb_subs_inv_address_street column?

When will the new query fields that I create be populated? Do I need them to be populated?

Sorry, I'm not that young any more.

Last edit: 8 years 10 months ago by ricco1.

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48570
Thanks: 8293
Karma: 1445

8 years 10 months ago #276902 by krileon

Replied by krileon on topic Substitutions in emails

The address information in the basket is just the address sent back by the payment process. Their actual invoice address is the CB fields. I wouldn't change those as depending on how they pay that information could be empty or slightly different and won't match the expected invoice field values.

When will the new query fields that I create be populated? Do I need them to be populated?

A query field pulls its value directly from the database using the query provided to it. It has no storage so there's nothing to populate beyond making your query. For example the below query will grab their most recent basket and output mc_gross.

Code:

SELECT `mc_gross` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

The following user(s) said Thank You: ricco1

Please Log in or Create an account to join the conversation.

ricco1
OFFLINE
Posts: 310
Thanks: 8
Karma: -7

8 years 10 months ago - 8 years 10 months ago #276914 by ricco1

Replied by ricco1 on topic Substitutions in emails

I think not making the following invoices is not a feature but a bug.

The question for me is: is it a small bug or a BIG bug?

Most membership sites are getting paid and issue the invoice after. Most of them offer recurring payments. So you should clearly state somewhere that your software is not suitable for membership sites, which would like to have recurring payments!

Issuing invoices is part of the process, and you've proven it to us that you could do it for the first invoices.

Last edit: 8 years 10 months ago by ricco1.

Please Log in or Create an account to join the conversation.

ricco1
OFFLINE
Posts: 310
Thanks: 8
Karma: -7

8 years 10 months ago - 8 years 10 months ago #276924 by ricco1

Replied by ricco1 on topic Substitutions in emails

Here are the queries for the fields if someone needs them:

Code:

SELECT `invoice` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `mc_gross` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `payment_status` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `payment_method` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `txn_id` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `time_completed_date` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `item_number` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `payer_email` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `item_name` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `mc_gross` - `tax` AS SubTotal FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `tax` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `ip_addresses` FROM `#__cbsubs_payment_baskets` WHERE `user_id` = '[user_id]' ORDER BY `time_initiated` DESC LIMIT 1

SELECT `last_renewed_date` FROM `#__cbsubs_subscriptions` WHERE `user_id` = '[user_id]' ORDER BY `last_renewed_date` DESC LIMIT 1

SELECT `expiry_date` FROM `#__cbsubs_subscriptions` WHERE `user_id` = '[user_id]' ORDER BY `last_renewed_date` DESC LIMIT 1

Last edit: 8 years 10 months ago by ricco1.

Please Log in or Create an account to join the conversation.

ricco1
OFFLINE
Posts: 310
Thanks: 8
Karma: -7

8 years 10 months ago - 8 years 10 months ago #276926 by ricco1

Replied by ricco1 on topic Substitutions in emails

Is the invoice number incrementing only for the first payment or also for the following recurring payments? If it's not incrementing for the following recurring payments I've wrote a query that will do it. Here it is:

Code:

UPDATE `#__cbsubs_payment_baskets` SET `invoice` = ( SELECT `invoice` FROM ( SELECT MAX( `invoice` + 1 ) AS `invoice` FROM `#__cbsubs_payment_baskets` ) AS t ) WHERE `user_id` = '[user_id]'

But we now need an auto action that will execute the query on following recurring payments. Could you tell us how to set that auto action please?

Last edit: 8 years 10 months ago by ricco1.

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48570
Thanks: 8293
Karma: 1445

8 years 10 months ago #276933 by krileon

Replied by krileon on topic Substitutions in emails

The question for me is: is it a small bug or a BIG bug?

It's not a bug, but a feature to support invoice substitutions in emails.

Most membership sites are getting paid and issue the invoice after. Most of them offer recurring payments. So you should clearly state somewhere that your software is not suitable for membership sites, which would like to have recurring payments!

It's a subscription management software. It automates the entire payment process. There is no invoice to be paid. The invoice we issue is correct and valid. We do not issue invoices for recurring payments because they're paying the same invoice (it states what their recurring rate is on the initial invoice). Currently invoices are baskets. They're one in the same. So separate invoices per recurring payment is not possible. We'll be reviewing improving our invoice functionality in a later release.

Issuing invoices is part of the process, and you've proven it to us that you could do it for the first invoices.

No, it's not. Issuing invoices is manual. CBSubs is designed for automation. If you're using strictly Offline payments then I suppose I could see an issue, but offline payments strictly gives a payment slip which is basically an "invoice to be paid" with every payment.

None of this has anything to do with your issue though. You're wanting invoice information to be substituted in, which we've a feature ticket to implement in addition to looking into other possible substitutions to support for the emails.

Is the invoice number incrementing only for the first payment or also for the following recurring payments? If it's not incrementing for the following recurring payments I've wrote a query that will do it. Here it is:

It increments on each new basket. In CBSubs Basket = Invoice. We do not generate a new basket for recurring payments. This also means we do not generate an invoice for recurring payments.

But we now need an auto action that will execute the query on following recurring payments. Could you tell us how to set that auto action please?

You should be able to make it a SELECT and place it in a query field then within CBSubs > Settings > Display > Invoices substitute in the query field. Not 100% sure this will work with query fields at this time, but it should. Otherwise I'm guessing you could use onCbSubsAfterPaymentBasket trigger in CB Auto Actions or onCPayAfterPaymentBasketUpdated.

Please Log in or Create an account to join the conversation.

Moderators: beat, nant, krileon