Post by katalin » Sun Jan 29, 2023 10:20 pm

I have the following code that displays all the vouchers inside a page in "my account". It also displays vouchers that have 0 balance. I would like to modify the query so it only displays vouchers that still have ballance.
Can anyone please help?

Code: Select all

<?php
class ModelAccountGiftvoucher extends Model {	
	public function getGifttotal() {
		$db = "SELECT v.voucher_id, (v.amount + SUM(vh.amount)) as total FROM " . DB_PREFIX . "voucher_history vh JOIN " . DB_PREFIX . "voucher v JOIN " . DB_PREFIX . "customer c WHERE v.voucher_id = vh.voucher_id AND v.to_email = c.email AND c.customer_id = '" . (int)$this->customer->getId() . "'"; $db .= " GROUP BY v.voucher_id";
		$result = $this->db->query($db);
		return $result->rows;
	}
	public function getVouchergift() {
		$db = "SELECT v.voucher_id, v.code, v.from_name, v.date_added, v.amount FROM " . DB_PREFIX . "voucher v JOIN " . DB_PREFIX . "customer c WHERE v.to_email = c.email AND c.customer_id = '" . (int)$this->customer->getId() . "'"; $db .= " ORDER BY v.date_added DESC";
		$query = $this->db->query($db);
		return $query->rows;
	}	
}
Last edited by katalin on Wed Feb 01, 2023 8:08 pm, edited 1 time in total.

Active Member

Posts

Joined
Wed May 05, 2010 2:28 am

Post by xxvirusxx » Sun Jan 29, 2023 10:39 pm

try with WHERE v.'amount' > 0

Upgrade Service | OC 2.3.0.2 PHP 8 | My Custom OC 3.0.3.8 | Buy me a beer


User avatar
Expert Member

Posts

Joined
Tue Jul 17, 2012 10:35 pm
Location - România

Post by katalin » Sun Jan 29, 2023 10:51 pm

xxvirusxx wrote:
Sun Jan 29, 2023 10:39 pm
try with WHERE v.'amount' > 0
Tried a lot of variants but it's not working, probably because voucher has an initial value and after you spend money from it 2-3-4 times you have to deduct those values and if those values added are equal to intial value the voucher would be empty...

Active Member

Posts

Joined
Wed May 05, 2010 2:28 am

Post by xxvirusxx » Sun Jan 29, 2023 10:59 pm

Check code from Transaction

Upgrade Service | OC 2.3.0.2 PHP 8 | My Custom OC 3.0.3.8 | Buy me a beer


User avatar
Expert Member

Posts

Joined
Tue Jul 17, 2012 10:35 pm
Location - România

Post by katalin » Sun Jan 29, 2023 11:01 pm

xxvirusxx wrote:
Sun Jan 29, 2023 10:59 pm
Check code from Transaction
I don't know what you mean by that.
Maybe I can edit here:

Code: Select all

    {% if vouchers %}
                 {% for voucher in vouchers %}<tr>
               {% set total_gift = 0 %}
                     <td class="text-left">{{ voucher.from_name }} </td>
                     <td class="text-left">{{ voucher.code }}</td>
                     <td class="text-left">{{ voucher.date_added }}</td>
                   <td class="text-right">{{ voucher.amount }}</td>
               <td class="text-right">
               {% for gift in gifttotal %} 
                 {% if gift.voucher_id == voucher.voucher_id %}
                 {% set total_gift = 1 %}
                   {% if gift.total > 0 %} 
                   {{ gift.total }} 
                 {% else %} 
                   {{ gift.total }}
                   {% endif %} 
                 {% endif %}
               {% endfor %}
                 {% if total_gift == 0 %}
                 {{ voucher.amount }}
               {% endif %}
               </td>
                   {% endfor %}
                   </tr>
               {% else %}
                   <tr>
                     <td class="text-center" colspan="5">{{ text_empty_gift }}</td>
                   </tr>
                  {% endif %}

Active Member

Posts

Joined
Wed May 05, 2010 2:28 am

Post by paulfeakins » Mon Jan 30, 2023 7:56 pm

katalin wrote:
Sun Jan 29, 2023 10:20 pm
I have the following code that displays all the vouchers inside a page in "my account".
Are there not extensions that do that?

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom

Post by by mona » Mon Jan 30, 2023 8:28 pm

You can join those tables but if voucher has a record and voucher history doesn't, you will not get any total back this way.

DISCLAIMER:
You should not modify core files .. if you would like to donate a cup of coffee I will write it in a modification for you.


https://www.youtube.com/watch?v=zXIxDoCRc84


User avatar
Expert Member

Posts

Joined
Mon Jun 10, 2019 9:31 am

Post by katalin » Tue Jan 31, 2023 3:56 am

paulfeakins wrote:
Mon Jan 30, 2023 7:56 pm
katalin wrote:
Sun Jan 29, 2023 10:20 pm
I have the following code that displays all the vouchers inside a page in "my account".
Are there not extensions that do that?
That's actually an old extension, I installed it but it still shows vouchers that are fully spent, and that's not necessary.

Active Member

Posts

Joined
Wed May 05, 2010 2:28 am

Post by katalin » Tue Jan 31, 2023 3:56 am

by mona wrote:
Mon Jan 30, 2023 8:28 pm
You can join those tables but if voucher has a record and voucher history doesn't, you will not get any total back this way.
That's out of my knowledge, can you please help?

Active Member

Posts

Joined
Wed May 05, 2010 2:28 am

Post by xxvirusxx » Tue Jan 31, 2023 5:31 am

Not checked the code....for old oc versions
https://www.opencart.com/index.php?rout ... n_id=11465

For oc 3.x versions
https://www.opencart.com/index.php?rout ... n_id=37813

Tips:
catalog\view\theme\journal3\template\account\account.twig

Upgrade Service | OC 2.3.0.2 PHP 8 | My Custom OC 3.0.3.8 | Buy me a beer


User avatar
Expert Member

Posts

Joined
Tue Jul 17, 2012 10:35 pm
Location - România

Post by katalin » Tue Jan 31, 2023 5:15 pm

xxvirusxx wrote:
Tue Jan 31, 2023 5:31 am
Not checked the code....for old oc versions
https://www.opencart.com/index.php?rout ... n_id=11465

For oc 3.x versions
https://www.opencart.com/index.php?rout ... n_id=37813

Tips:
catalog\view\theme\journal3\template\account\account.twig
The second one is the one I am using but it displays vouchers with 0 balance and there is no need for that, I just want to display vouchers with remaining balance.

Active Member

Posts

Joined
Wed May 05, 2010 2:28 am

User avatar
Expert Member

Posts

Joined
Tue Jul 17, 2012 10:35 pm
Location - România

Post by katalin » Tue Jan 31, 2023 8:33 pm

xxvirusxx wrote:
Tue Jan 31, 2023 8:05 pm
First is ok?
Fist one is for OC v1, I am using v3. I highly doubt it will work without custom modifications.

Active Member

Posts

Joined
Wed May 05, 2010 2:28 am

Post by xxvirusxx » Tue Jan 31, 2023 8:43 pm

Yes...first need to be converted for 3.0.x.x.....

Attachments

voucher_balance.png

voucher_balance.png (35.21 KiB) Viewed 952 times


Upgrade Service | OC 2.3.0.2 PHP 8 | My Custom OC 3.0.3.8 | Buy me a beer


User avatar
Expert Member

Posts

Joined
Tue Jul 17, 2012 10:35 pm
Location - România

Post by katalin » Tue Jan 31, 2023 9:46 pm

xxvirusxx wrote:
Tue Jan 31, 2023 8:43 pm
Yes...first need to be converted for 3.0.x.x.....
Can you easily convert it?

Active Member

Posts

Joined
Wed May 05, 2010 2:28 am

Post by SohBH » Wed Feb 01, 2023 7:36 pm

How about this

Code: Select all

WHERE total > 0

Business Web Development | Content Creation | Analytics and Reporting | SEO


User avatar
Active Member
Online

Posts

Joined
Mon Nov 02, 2020 12:01 am
Location - Malaysia

Post by katalin » Wed Feb 01, 2023 8:07 pm

Someone helped me with this so it's solved now! Thanks for the help guys!

Active Member

Posts

Joined
Wed May 05, 2010 2:28 am
Who is online

Users browsing this forum: Semrush [Bot] and 101 guests