How can I check order status and then show protected content ?

Hello from GREECE everybody !

I am trying to find out how it is possible for a logged in user to see content in his account page IF order satus is "DELIVERED" ( or any other name ).

This is for handling subscriptions; I set up a Zen Cart store 1.3.7 - fresh install - (https://www.doctorbet.gr/gr) - many modifications has to be done yet - and I set up 2 products :

1. document with an option name and some option values ( 4 the desired subscription ) and

2. one free product for subscription "ACTIVATION".

Customer can not see the products !

When a customer log in he can see this menu :

---

<?php $content = "<!--testloginbox-->"; $content .= '<div id="' . str_replace('_', '-', $box_id . 'Content') . '" class="sideBoxContent">'; if(!$_SESSION['customer_id']) { $content .=zen_draw_form('login_box', zen_href_link(FILENAME_LOGIN, 'action=process', 'SSL')); $content .=LOGIN_BOX_EMAIL_ADDRESS . '<br />' . zen_draw_input_field('email_address', '', 'size="24"').'<br />'; $content .=LOGIN_BOX_PASSWORD . '<br />' . zen_draw_password_field('password', '', 'size="24"') . '<br />'; $content .='<a href="' . zen_href_link(FILENAME_PASSWORD_FORGOTTEN, '', 'SSL') . '">' . LOGIN_BOX_PASSWORD_FORGOTTEN . '</a>' . '<br />' . '<a href="' . zen_href_link(FILENAME_CREATE_ACCOUNT, '', 'SSL') . '">' . LOGIN_BOX_CREATE_ACCOUNT . '</a>' . '<br />'; $content .='<div class="centeredContent">'.zen_image_submit(BUTTON_IMAGE_LOGIN, BUTTON_LOGIN_ALT).'</div>'; $content .='</form>'; } else { $content .= '<ul>'; $content .= '<li><a class="loginBoxLinks" href="' . zen_href_link(FILENAME_ACCOUNT, '', 'SSL') . '">' . LOGIN_BOX_ACCOUNT . '</a></li>'; $content .= '<li><a href="http://www.doctorbet.gr/gr/index.php?main_page=shopping_cart">Επιλεγμένες Συνδρομές</a></li>'; $content .= '<li><a href="http://www.doctorbet.gr/gr/index.php?main_page=document_product_info&cPath=1&products_id=1">Αγορά Συνδρομής</a></li>'; $content .= '<li><a href="http://www.doctorbet.gr/gr/index.php?main_page=account_password">Αλλαγή Password</a></li>'; $content .= '<li><a href="http://www.doctorbet.gr/gr/index.php?main_page=account_history">Η Συνδρομή μου</a></li>'; $content .= '<li><a href="http://www.doctorbet.gr/gr/index.php?main_page=account_edit">Τα Στοιχεία μου</a></li>'; $content .= '<li><a class="loginBoxLinks" href="' . zen_href_link(FILENAME_LOGOFF, '', 'SSL') . '">' . LOGIN_BOX_LOGOFF . '</a></li>'; $content .= '</ul>'; } $content .= '</div>'; ?>

---

So, this way he can use this link :

$content .= '<li><a href="http://www.doctorbet.gr/gr/index.php?main_page=document_product_info&cPath=1&products_id=1">Αγορά Συνδρομής</a></li>';

to buy a subscription.

Well, the MODIFICATION I made is in tpl_account_default.php :

I just changed the number 0 to 1 in the following line of code :

if (zen_count_customer_orders() > 1) {

and below it i just put the "PROTECTED" content (can be any) :

<center>

<object id="banner_automove" width="577" height="120" classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0">

<param name="movie" value="testmembers.swf" /> <param name="quality" value="high" /> <param name="scale" value="noscale"/> <param name="bgcolor" value="#FFFFFF" /><param name="wmode" value="transparent" /><embed name="banner_automove" src="testmembers.swf" quality="high" scale="noscale" bgcolor="#FFFFFF" wmode="transparent" width="577" height="120" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer" /></object> </center>

This way, customer buys a subscription and has one order in his account, so "PROTECTED" content won't show in his account page; only if there are 2 or more orders, the content above (or any other content) will show.

Next step was to "force" the "subscriber !" to buy another product ( when payment is done ) so the "PROTECTED" content show in his account page.

So, when admin update his status order to "DELIVERED" ( or any other name ) he also sends the link for the free product ( http://www.doctorbet.gr/gr/g4v56ds7gt325bv4f6dsg8A7bhjkl5648y7C9re8t76dfTs5g432xbv1I9ytVrew87As6Tfd5g4xz3bv1.htm%29.

This way customer can see the "PROTECTED" content in his account page, but there are a lot of 'but' !

BUT :

1. What happens with next subscription ?

2. Why should customer do all this to be a subscriber ?

3. WHY NOT TO SHOW "PROTECTED" CONTENT , by checking order status ?

Well, my question is VERY SIMPLE :

I want to change ( modify ) this line ( in tpl_account_default.php ):

---

if (zen_count_customer_orders() > 1) {

---

so i can check a specific order status ( LAST ORDER ).

HOW THIS CAN BE DONE ?

Is there any other way, to handle protected pages by checking order status ?

Thanks

You've got a lot going on in there, so I don't know if this will answer your question completely, but I'll take a shot. :D

Instead of a simple if statement, you need to do some more elaborate checking. The problem, as you defined it, is...

Show protected content if the status of the last order is "DELIVERED"
I think that logic has problems (e.g. more than one subscription?), but we'll put that aside for now. Let's just get it to work.

To get a customer's latest order, you can use a SQL statement like this...

$sql = "select * from " . TABLE_ORDERS . " 
        where customers_id = '" . $customers_id . "'
        order by orders_id desc limit 1";

That last line is the one you want to pay attention to. It returns the newest order first (order by orders_id), and limits the return to only that order (limit 1).

Now you can do a second query to check to see if the given order has the appropriate status...

$sql = "select * from " . TABLE_ORDERS_STATUS_HISTORY . " 
        where orders_id = '" . $orders_id . "'
        and orders_status_id = '" . $delivered . "'";

Show the content if you get a return, and don't if not. You can use zen_not_null() or RecordCount() to do the checking. Note that RecordCount() is a function within the $db object.

Thank you very much for your help.

Forgive me, I just downloaded php manual. ( Also forgive me for my English; I hope you understand what I mean ! ) I need to print some pages from zen cart ( php code i mean ) and then read php manual carefully so i can understand what every line of code is created to do....or something like...you know!

I also downloaded MySQL 5.1 reference manual for the same reason, and I just have a look right nowin chapter 5.13.

Well, what I understand for now is that the first query keeps the result from SQL DB in the cache ( is that result the "given order" as you say? so the last order [order_id] is kept in the cache? ) and the second query checks if this order id is in status delivered....so now I understand why i can not use an "IF", because i have to TAKE the last order_id from DB and then TAKE the order_status_id and IF this is in status DELIVERED , then i take a result.

BUT, as i am reading in MySQL 5.1 reference manual in chapter "3.3.4.6. Working with NULL Values" , i did not manage to understand....do I also have to use zen_not_null() and how ?

Well, i am going to try the 2 queries you wrote to see what happens ^!!^

I will move this line first (?) :

if (zen_count_customer_orders() > 1) {......

Thank you so much for your help. I will be back for the RESULTS !!!

Thank you very much for your help.

Forgive me, I just downloaded php manual. ( Also forgive me for my English; I hope you understand what I mean ! ) I need to print some pages from zen cart ( php code i mean ) and then read php manual carefully so i can understand what every line of code is created to do....or something like...you know!

I also downloaded MySQL 5.1 reference manual for the same reason, and I just have a look right nowin chapter 5.13.

Well, what I understand for now is that the first query keeps the result from SQL DB in the cache ( is that result the "given order" as you say? so the last order [order_id] is kept in the cache? ) and the second query checks if this order id is in status delivered....so now I understand why i can not use an "IF", because i have to TAKE the last order_id from DB and then TAKE the order_status_id and IF this is in status DELIVERED , then i take a result.

BUT, as i am reading in MySQL 5.1 reference manual in chapter "3.3.4.6. Working with NULL Values" , i did not manage to understand....do I also have to use zen_not_null() and how ?

Well, i am going to try the 2 queries you wrote to see what happens ^!!^

I will move this line first (?) :

if (zen_count_customer_orders() > 1) {......

Thank you so much for your help. I will be back for the RESULTS !!!

---

02:07 AFTER MIDNIGHT :

I am still new, even to use the code you gave me !!!

I put it in tpl_account_default.php as follow :

---

<?php /** * Page Template * * Loaded automatically by index.php?main_page=account.<br /> * Displays previous orders and options to change various Customer Account settings * * @package templateSystem * @copyright Copyright 2003-2005 Zen Cart Development Team * @copyright Portions Copyright 2003 osCommerce * @license <http://www.zen-cart.com/license/2_0.txt> GNU Public License V2.0 * @version $Id: tpl_account_default.php 4086 2006-08-07 02:06:18Z ajeh $ */ ?> <div class="centerColumn" id="accountDefault"> <h1 id="accountDefaultHeading"><?php echo HEADING_TITLE; ?></h1> <?php if ($messageStack->size('account') > 0) echo $messageStack->output('account'); ?> <?php if (zen_count_customer_orders() > 0) { ?>

$sql = "select * from " . TABLE_ORDERS . "
where customers_id = '" . $customers_id . "'
order by orders_id desc limit 1";

$sql = "select * from " . TABLE_ORDERS_STATUS_HISTORY . "
where orders_id = '" . $orders_id . "'
and orders_status_id = '" . $delivered . "'";

<center>

<object id="banner_automove" width="577" height="120" classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0">

<param name="movie" value="testmembers.swf" /> <param name="quality" value="high" /> <param name="scale" value="noscale"/> <param name="bgcolor" value="#FFFFFF" /><param name="wmode" value="transparent" /><embed name="banner_automove" src="testmembers.swf" quality="high" scale="noscale" bgcolor="#FFFFFF" wmode="transparent" width="577" height="120" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer" /></object> </center> <?php } ?> <?php if (SHOW_NEWSLETTER_UNSUBSCRIBE_LINK !='false' or CUSTOMERS_PRODUCTS_NOTIFICATION_STATUS !='0') { ?> <?php if (SHOW_NEWSLETTER_UNSUBSCRIBE_LINK=='true') { ?> <?php } //endif newsletter unsubscribe ?> <?php if (CUSTOMERS_PRODUCTS_NOTIFICATION_STATUS == '1') { ?> <?php } //endif product notification ?> <?php } // endif don't show unsubscribe or notification ?> </div> <?php // only show when there is a GV balance if ($customer_has_gv_balance ) { ?> <div id="sendSpendWrapper"> <?php require($template->get_template_dir('tpl_modules_send_or_spend.php',DIR_WS_TEMPLATE, $current_page_base,'templates'). '/tpl_modules_send_or_spend.php'); ?> </div> <?php } ?> <br class="clearBoth" /> </div>

---

BUT I think that or something that I made is wrong or I forgot to put some more code ( ? ) ; maybe that has to do with zen_not_null() ?

Should I use php tags ( <?php ?> ) to include the code you gave me ?

Should I add an IF like the following ? :

<?php if (zen_not_null() { ?>

Well, i am still trying ( 02:31 AM G+2 ) ; I will better be waiting for your help.

Thanks in advance.

The code I gave you is just an example of the SQL queries you should build. It's not at all complete, and needs to be adjusted to fit your database and variable names. Nothing I gave you can simply be placed into your scripts.

zen_not_null() is a Zen Cart function that can look for empty or null values. Don't let the name fool you; the value does not specifically have to be a NULL from the database. Given your confusion, I'd say you should stay away from that and use the RecordCount() one I mentioned. Here's a better example...

<?php

// ...additional code here

$sql = "select * from " . TABLE_ORDERS_STATUS_HISTORY . " 
        where orders_id = '" . $orders_id . "'
        and orders_status_id = '" . $delivered . "'";
$delivered_orders = $db->Execute(sql);

if ($delivered_orders->RecordCount() > 0) {
  // show protected content
}

// ...additional code here
?>

See how the results of the query get assigned to the variable $delivered_orders? $db runs the query, then puts the results in there as a PHP object.

Remember, this is still just an example. It will show you the basic code structure for how to build and run SQL queries in Zen Cart; you still need to modify it and write your own code around it. You can find endless examples for SQL queries all over the source code, just look for the variable $db.

It's good that you're looking at the manuals. Keep reading, but focus on actual code. Use the manual as a reference when you don't know what the code is doing. This stuff is very difficult at first, but gets a lot easier once you know the basics. You'll get it. :yes:

Thank you for your valuable help.

I tried that code ( I am not yet ready to write code in php, because i should read and excersise a lot for that ).

I was searching ( i use plesk 8 ) with phpMyAdmin ( 2.6.4-pl3 ) inside my database to see if something is wrong about the names of the tables in the code you gave me, but everything seems to be ok as for the structure of the table and the values.

Well I tried that code you gave me and i get the following :

1064 You have an error in your SQL syntax near 'sql' at line 1
in:
[sql]

Do you have any idea ?

No, because, like I said, the code I gave you was just a snippet. It won't work on it's own (though it does have correct syntax), and the error you got is likely caused by whatever code you have surrounding it.

I tried that code ( I am not yet ready to write code in php, because i should read and excersise a lot for that ).
That's most likely your problem right there. You can't program a custom behavior and not expect to do some actual coding. Learning to write code is scary at first, but you just have to go ahead and try, that's the only way you'll really learn. Reading and researching will only take you so far.

Learning to code is a lot like learning to ski: if you're not falling, you're not learning. I learn more about coding when the code breaks than when it works, because I have to figure out what I'm doing wrong in order to fix it. That often leads to learning new techniques to better solve my problem.

Just remember that whatever code you break, you can fix, and you'll be fine. :smile:

Well, I see you are a very good teacher.

That is the right for me right now. I understand what you say. This is what I say to everybody; also to my kids.

The point is that in most cases I understand the code when I read it, but I need more time to program a custom behavior as you say.

I should first learn the basics, for SQL structure and how procedures work with PHP before start looking "deep" in zen cart code.

BUT there are a lot of examples in zen cart that can help me learning.

So, I keep on trying ( I do not really have enough time to read now ) working around the code you have me, trying to understand what is going wrong, hoping this issue is going to help many other members of this wonderful community.

I will post here, any new "progress" !

Thanks in advance.

Please do post your progress, and good luck!

Well, I start thinking that maybe there is another way to do what I am trying to do.

In addition, there are a lot of thoughts in my mind of how a "subscription-product" could be handled by zen cart.

Also ( because of my problem ) I am thinking that payment status should affect a type of product, and there should be the ability to define the behavior of the payment status when adding or editing a product ( or category ) in admin area.

I did not search in the forum if a project for subscriptions is running, but I will prepare a flow chart just giving the resonable order of thoughts I have in my mind.

Anyway, I am trying to read other templates and understand how and what they "print".

Hi again !!!

I would like to ask if there is any way to see a result of a statement before I put it in the code; like :

$wanted_status_num = "select * from " .TABLE_ORDERS_STATUS_HISTORY . "
where orders_id = '" . $orders_id . "'
order by orders_status_id desc limit 1";

Can I see the value witch is assigned to the variable and how ?

This would help a lot !!

Thanks in advance.

Well, I am practicing a lot and I hope with some more help ( to understand some issues ) I will manage to do what I want.

Sorry for all this code here, but this is my practice and I am so happy that I do not get syntax errors any more ( I only "echo" the db Execution and I take an "Object" - I'll see what I can do with this later anyway; I just want it to show now ) .

I remember ...20 years ago!!! my first "contact" with a Commodore 64; my fingers typing " hello pc " and pressing RETURN...and then on TV screen (!) reading SYNTAX ERROR !!!

This is happening right now; this is what we call cycle of life. Me again, trying to cummunicate with the database tables to get some values :) ;

MY CODING FOR TODAY IS THE FOLLOWING :

---

<div class="centerColumn" id="accountDefault"> <h1 id="accountDefaultHeading"><?php echo HEADING_TITLE; ?></h1> <?php if ($messageStack->size('account') > 0) echo $messageStack->output('account'); ?> <?php $anamoni = "Αναμένεται πληρωμή για την ενεργοποίηση της συνδρομής σας"; $lixis = "Η Συνδρομή σας έχει λήξει"; $visa = "Έχετε επιπλέον 15 ημέρες, διότι πληρώσατε με VISA"; $subscribers_menu = '<center> <object id="banner_automove" width="577" height="120" classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0"> <param name="movie" value="testmembers.swf" /> <param name="quality" value="high" /> <param name="scale" value="noscale"/> <param name="bgcolor" value="#FFFFFF" /><param name="wmode" value="transparent" /><embed name="banner_automove" src="testmembers.swf" quality="high" scale="noscale" bgcolor="#FFFFFF" wmode="transparent" width="577" height="120" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer" /></object> </center>'; $wanted_status_name = "select * from " . TABLE_ORDERS_STATUS . " where orders_status_name = 'delivered'"; $status_name = $db->Execute($wanted_status_name); echo "$status_name"; ?><br><?php

$wanted_status_num = "select * from " . TABLE_ORDERS_STATUS_HISTORY . "
where orders_id = '" . $orders_id . "'
order by orders_status_id desc limit 1";

$status_num = $db->Execute($wanted_status_num);

echo "$status_num";

?><br><?php

$wanted_payment_method = "select * from " . TABLE_ORDERS . "
where payment_module_code = 'cc'";

$pay_method = $db->Execute($wanted_payment_method);

echo "$pay_method";

?><br><?php

if ($status_num == 3):
echo "$subscribers_menu";
elseif ($status_num == 1):
print"$anamoni";
elseif ($pay_method == "cc"):
echo "$visa";
else:
print"$lixis";
endif;

?>

WELL , by using this code I take the following :

Object // from the first statement
Object // from the second statement
Object // from the third statement
Η Συνδρομή σας έχει λήξει // from variable $lixis from else:

I can do some basics ( at least ) without SYNTAX ERROR.

As for the Objects I did not find what I wanted in PHP manual.

So, please help me at this time to understand how to code.

Thanks in advance.

This is great I have very much wanted to do something like this where they would have to login before they could see the newest products.

Could you also perhaps give some insight into where I should edit for this?

Here is the thread with some of my questions on this: http://www.zen-cart.com/forum/showthread.php?t=55842

lebrand2006:

I would like to ask if there is any way to see a result of a statement before I put it in the code; like :

$wanted_status_num = "select * from " .TABLE_ORDERS_STATUS_HISTORY . "
where orders_id = '" . $orders_id . "'
order by orders_status_id desc limit 1";

Can I see the value witch is assigned to the variable and how ?
After you execute this code, you can simply loop through the results and echo them out...```
$sql = "select * from " .TABLE_ORDERS_STATUS_HISTORY . "
where orders_id = '" . $orders_id . "'
order by orders_status_id desc limit 1";

$wanted_status_num = $db->Execute($sql);
while (!$wanted_status_num->EOF) {
print_r($wanted_status_num->fields);
echo "<br /> \n";
$wanted_status_num->MoveNext();
}

Pay attention to the fact that I renamed the variable on your SQL statement and moved it to the *results* of that statement.

Hello again.

Well, I managed to do what I want, but only by checking the first order.

I made a lot of modifications to tpl_account_default ( Attached File ).

I first added an IF command to check if somebody has no orders and then I inform the user and provide a spesific link ( any link ).

The second IF command is unchanged, and if there are more that 0 orders, the following IF's working :

<?php if ($orders['orders_status_name'] == 3){ echo ACTIVE_SUBSCRIBERS_MENU; } elseif ($orders['orders_status_name'] == 1){ echo PAYMENT_PENDING; } elseif ($orders['orders_status_name'] == 2){ echo PRE_ACTIVATION_STATUS; } elseif ($orders['orders_status_name'] == 4){ echo SUBSCRIPTION_EXPIRED; } ?>

I just changed the status names to numbers from 1 to 4.

I use some defines to provide the content to the user.

But I will change this. I will use frames in the defines; specificaly two frames in a define ( the one I call "protected content" ) , splited verticaly. One small frame to keep a menu ( flash menu or anything alse ) and another one to show the content. This content for now will be hosted in an unprotected area, so I should find a way to protect it.

This is very easy with PLESK or any other virtual server managment software ( like cPanel etc. ) ; I will just make a protected file, i will define a username and password and put all protected content in there.

The problem ( at least for me now ) is how to POST username and password, inside the PHP code, when I demand to show a page that is located in that protected folder on my server.

Lets's say I need to show a page "test.html" that is located in a protected folder witch needs username and password.

Lets's say that I use 2 frames splited verticaly. In the top frame I use a flash menu. This seems to be a problem, because ( I am not sure ) I believe that I will need to POST username and password with some kind of BUTTON PROCCES to the protected folder.

Is there any idea how to do something like this ?

---

Another issue in my mind is that we should work on some ideas for subscription module in Zen Cart. Because of the fact that we can handle downloads in Zen, I think we can work on this parallel like we handle subscriptions.

---

I also include attached, a Credit Card Payment module for Greek Bank Eurobank.

I found that module at osCommerce and I modified it, so I can install it in Zen 1.3.7

I managed to install this module after the modifications but I am waiting for the bank to provide me with Merchant ID and password so I can test if the module is working. This bank uses ProxyPay3.

Well, the files are attached. If somebody can check it from the Zen Cart team, please let me know if there are any wrong modifications or something is missing.

I also provide the original osCommerce files.

Thanks to all of you

Strange things with this bank.
They want 5 things to open a demo account and test the script.
These are:

1.   check out
   

2.   validation 
   

3.   ok
   

4.   not ok
   

5.   confirmation
   

Which is which and why this bank doesnt have a simple module for 4-5 commerce carts to just plug and play?!!

Please, reply ASAP.