Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Time Zone Offset Contribution Support


dr_lucas

Recommended Posts

It happens because you revised the now() function twice in your php files instead of only once in each file...

You should have revised only the first now() case...

It should be changed back to the original:

	  while (list(, $value) = each($data)) {
	switch ((string)$value) {
	   case 'now()':
// Added a time offset function here
		$query .= 'date_add(now(), INTERVAL ' . TIME_ZONE_OFFSET . ' HOUR), ';
		break;
	  case 'null':
		$query .= 'null, ';
		break;
	  default:
		$query .= '\'' . tep_db_input($value) . '\', ';
		break;
	}
  }
  $query = substr($query, 0, -2) . ')';
} elseif ($action == 'update') {
  $query = 'update ' . $table . ' set ';
  while (list($columns, $value) = each($data)) {
	switch ((string)$value) {

****************************************************CHANGE IT BACK BELOW: (In both files)
case 'now()':
// Added a time offset function here
		$query .= 'date_add(now(), INTERVAL ' . TIME_ZONE_OFFSET . ' HOUR), ';
		break;
	  case 'null':
		$query .= $columns .= ' = null, ';
		break;

 

ok so i just change the bottom one back to

case 'now()':

$query .= 'now(), ';

case 'null':

$query .= $columns .= ' = null, ';

break;

 

 

is that right?

Link to comment
Share on other sites

  • Replies 64
  • Created
  • Last Reply

Top Posters In This Topic

No, the bottom one was not as you wrote it...it was (and should stay):

 

case 'now()':

$query .= $columns . ' = now(), ';

break;

case 'null':

$query .= $columns .= ' = null, ';

break;

Edited by dr_lucas
Link to comment
Share on other sites

I have the same problem.

 

I installed the mod: doesn't work. I removed it, back to my original files and now, I have this "you have an error in mysql syntax" ???? what a mess!

Link to comment
Share on other sites

I have the same problem.

 

I installed the mod: doesn't work. I removed it, back to my original files and now, I have this "you have an error in mysql syntax" ???? what a mess!

You should backup your database before you run any sql files. That way, if you have problems, you can restore your database.

 

If you did not back up, drop any tables that were created in the sql file.

Link to comment
Share on other sites

Such a simple mod, no idea what make you have such issues with it...

By the way, the sql does not create any new table, it only add 1 value to the configuration table...

Link to comment
Share on other sites

Such a simple mod, no idea what make you have such issues with it...

By the way, the sql does not create any new table, it only add 1 value to the configuration table...

 

 

Ok I installed it right:

the confusion came from the replace

case 'now()':

$query .= 'now(), ';

 

that appears twice in the file but only needs to be replaced once (fine, but why?)

 

My times are now worse than they used to be (I am PST is set TZ to US/Pacific)

my server is central

 

I the who's online for example, I have a 8 hour offset. does the mod affect the who's online too?

I put -1 in the store adnib though..

 

Any clues?

Link to comment
Share on other sites

Such a simple mod, no idea what make you have such issues with it...

By the way, the sql does not create any new table, it only add 1 value to the configuration table...

You are right about the sql.

People have issues because the install instructions are not completely clear. You should rewrite them to show all of the changes clearly, not just the added parts.

Link to comment
Share on other sites

You are right about the sql.

People have issues because the install instructions are not completely clear. You should rewrite them to show all of the changes clearly, not just the added parts.

 

IMHO the instructions are very clear (if someone fail to follow such simple and short instructions and revise other parts that I clearny never stated, then he should probably give up modding his shop and give it to someone who know what he's doing, no offence) but if you think you can do better then by all means - be my guest, mate...

 

Contribution: Timezone Offset Module

Designed for: Zen Cart v1.2.3 Release. Modified to work on osCommerce 2.2 MS2

Originally Designed for Zen Cart by: Neville Kerr [email protected]

Modified for osCommerce by: Dr_Lucas

License: under the GPL - See attached License for info.

Support: http://www.oscommerce.com/forums/index.php?showtopic=203878

 

========================================================

 

WHAT DOES THIS MODULE DO?

 

This module allows you to set a time zone offset into the cart if you are in a situation where your store is in a different time zone from the server on which it is hosted. (For example, your store is based in Australia but is hosted on a server in the USA.) This situation can become a problem for accounting because order dates will not be accurate for the store time zone.

The module will also make the entire store and admin display the right date and time of your real location.

 

The module inserts a new configuration key into the database that sets the value of the time zone difference. The offset value is set from Admin>Configuration>My Store>Time Zone Offset.

 

All orders will have their 'date_purchased' value corrected for the timezone difference when they are inserted into the database. This corrected value will be displayed on all admin pages, on the customer's 'My Account' page

 

Need to work on: Make it display the right date in order confirmation and order status update emails (code is located in checkout_process.php). I couldn't do that. Any help will be appreciated.

 

========================================================

 

INSTALLATION

 

Step 1. Use phpMyAdmin or a similar db manager to run the query in paragraph 1 below. This will add the TIME_ZONE_OFFSET configuration key into your database.

 

Step 2. Make sure that you have backup copies of your original files.

 

Step 3. Modify the four files as described below

 

Step 4. From your store admin panel, select Configuration>My Store. The new Time Zone Offset field should now be on that page: set the offset you require.

 

That's it: you're done!

 

========================================================

 

HISTORY:

 

10/Apr/2006 - Initial Release

 

========================================================

 

FILE MODIFICATIONS:

 

includes/functions/database.php

admin/includes/functions/database.php

includes/languages/english.php

admin/includes/languages/english.php

 

=======================================================

1) RUN THE FOLLOWING SQL ON YOUR SQL DATABASE:

 

INSERT INTO configuration (configuration_id, configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('', 'Time Zone Offset', 'TIME_ZONE_OFFSET', '0', 'The time zone difference between your store and the server. Enter +h, 0 or -h where ''h'' is the number of hours. If your store time zone is ahead of the server time zone, use +', 1, 7, NULL, '0001-01-01 00:00:00', NULL, NULL);

 

2) Go to your Configuration -> My Store -> Time Zone Offset

and set the time difference between your server and your real location

 

3) FILE includes/functions/database.php

* Find:

case 'now()':

$query .= 'now(), ';

 

* Change To:

case 'now()':

// Added a time offset function here

$query .= 'date_add(now(), INTERVAL ' . TIME_ZONE_OFFSET . ' HOUR), ';

 

4) FILE admin/includes/functions/database.php

* Find:

case 'now()':

$query .= 'now(), ';

 

* Change To:

case 'now()':

// Added a time offset function here

$query .= 'date_add(now(), INTERVAL ' . TIME_ZONE_OFFSET . ' HOUR), ';

 

5) FILE includes/languages/english.php

* Find:

@setlocale(LC_TIME, 'en_US.ISO_8859-1');

 

* Change To:

@setlocale(LC_TIME, 'en_US.ISO_8859-1');

putenv("TZ=Asia/Bangkok");

$lcl_date_time = date("Y-m-d H:i:s");

 

*** IMPORTANT: Please note that you should put your own time zone after the TZ=. I live in Bangkok, so I use the Asia/Bangkok timezone. Search google to find more time zones and the one that matches yours...

 

6) FILE admin/includes/languages/english.php

* Find:

@setlocale(LC_TIME, 'en_US.ISO_8859-1');

 

* Change To:

@setlocale(LC_TIME, 'en_US.ISO_8859-1');

putenv("TZ=Asia/Bangkok");

$lcl_date_time = date("Y-m-d H:i:s");

 

*** IMPORTANT: Please note that you should set your own time zone after the TZ=. I live in Bangkok, so I use the Asia/Bangkok timezone. Search google to find more time zones and the one that matches yours...

 

7) That's it. There's still a little work that needs to be done on this mod: Make it display the right date in order confirmation and order status update emails. My limited PHP skills didn't help to achieve that. Any help will be appreciated.

Edited by dr_lucas
Link to comment
Share on other sites

IMHO the instructions are very clear (if someone fail to follow such simple and short instructions and revise other parts that I clearny never stated, then he should probably give up modding his shop and give it to someone who know what he's doing, no offence) but if you think you can do better then by all means - be my guest, mate...

 

I can tell you that I an not a rookie in OsCom: I have installed more than 50 or 60 mods in many stores, and this one, even if simple, was not clearly explained. because there were several occurences of the same piece of code and not all of them needed to be replaced, that was confusing.

Link to comment
Share on other sites

I can tell you that I an not a rookie in OsCom: I have installed more than 50 or 60 mods in many stores, and this one, even if simple, was not clearly explained. because there were several occurences of the same piece of code and not all of them needed to be replaced, that was confusing.

 

That's not true, mate. this code:

 

		  case 'now()':
		$query .= $columns . ' = now(), ';

 

Is *NOT* the same as this code, that I instructed to modify:

		  case 'now()':
	  $query .= 'now(), ';

 

And I do believe that I have been 100% clear when I wrote my instructions...

Link to comment
Share on other sites

IMHO the instructions are very clear (if someone fail to follow such simple and short instructions and revise other parts that I clearny never stated, then he should probably give up modding his shop and give it to someone who know what he's doing, no offence) but if you think you can do better then by all means - be my guest, mate...

I don't mean to insult your work, and I am not installing this contrib because I don't need it. However, people are having trouble following your instructions.

 

IMHO, you could do something along the lines of

 

3) FILE includes/functions/database.php

* Find:

switch ((string)$value) {

case 'now()':

$query .= 'now(), ';

break;

* Change To:

switch ((string)$value) {

case 'now()':

//Start time offset function here

// $query .= 'now(), '; removed by Time Zone Offset Mod

$query .= 'date_add(now(), INTERVAL ' . TIME_ZONE_OFFSET . ' HOUR), ';

// End time offset function mod

Link to comment
Share on other sites

It should have read

 

 

3) FILE includes/functions/database.php

* Find:

switch ((string)$value) {

case 'now()':

$query .= 'now(), ';

break;

* Change To:

switch ((string)$value) {

case 'now()':

//Start time offset function here

// $query .= 'now(), '; removed by Time Zone Offset Mod

$query .= 'date_add(now(), INTERVAL ' . TIME_ZONE_OFFSET . ' HOUR), ';

break;

// End time offset function mod

Link to comment
Share on other sites

Coopco.

 

I totally agree with you.

Thanks, but this is not the only contrib where the install is not quite clear. CCGV is another that comes to mind. It becomes more evident when you have a lot of mods in your store.

 

I am not knocking the author in anyway (the mod seems to work fine), it is just hard to bullet proof instructions.

Link to comment
Share on other sites

  • 1 month later...

I installed this contribution according to the instructions and i use "TZ=Asia/Bangkok" for test. After i installed, I checked:

1. admin->customers->orders->details, when i update the status of order, the time did not change, it is still server time.

2. admin->tools->who's online, the time is still server time.

3. admin->tools->server info, the time is changed, Server Date: 06/16/2006 13:37:40 , Datebase Date: 06/16/2006 02:37:40

any idea about it?

Link to comment
Share on other sites

I installed this contribution according to the instructions and i use "TZ=Asia/Bangkok" for test. After i installed, I checked:

1. admin->customers->orders->details, when i update the status of order, the time did not change, it is still server time.

2. admin->tools->who's online, the time is still server time.

3. admin->tools->server info, the time is changed, Server Date: 06/16/2006 13:37:40 , Datebase Date: 06/16/2006 02:37:40

any idea about it?

 

I'm having the same issues. The time is not adjusted properly everywhere.

Link to comment
Share on other sites

  • 2 months later...
I installed this contribution according to the instructions and i use "TZ=Asia/Bangkok" for test. After i installed, I checked:

1. admin->customers->orders->details, when i update the status of order, the time did not change, it is still server time.

2. admin->tools->who's online, the time is still server time.

3. admin->tools->server info, the time is changed, Server Date: 06/16/2006 13:37:40 , Datebase Date: 06/16/2006 02:37:40

any idea about it?

 

tomliuwhite: Which one got changed? the server or the database?

 

Dr_Lucas:

 

I have a thought:

 

Is it possible that the folks having problems getting this to work are hosted on virtual servers. I would guess a lot, if not most, websites hosted in other countries are on shared servers and the putenv("TZ=xxxxx/xxx") may be disabled.

 

I tried to install, found the instructions clear enough and after reviewing all the messages believe I installed correctly yet in my case the server date appears to have been adjusted correctly but the database date/time is on Hong Kong Date/Time (I'm located in Colorado and my host is is Hong Kong).

 

Well now, I just realized that puts the lie to the idea shared servers is the problem, doesn't it? It seems the problem is narrowed down to adjusting the database time stamp. Doesn't putenv act on the database, not the webserver?

 

If so, and the database refuses to change, therein might lay the crux of the problem. Bad code, database configuration to not recognize putenv, or what?

 

It seems to me that there are two approaches that could be taken to accomplish the goal here:

 

1. Adjust server and database times to the preferred time zone

or

2. Configure osC to compensate for the time difference.

 

There are definite advantages to both approachs. #1 is a much cleaner approach, if we can control the timestamps coming out of the database and webservers. If not, it seems #2 may be the only viable approach unless one wants to force local terminal timestamps into the mix (that would open up a new can of worms what with with some terminals being subject to whatever time is set on their LAN).

 

The second approach would make the osC installation independent of server and database time function restrictions that may vary from host to host. It would be nice if it could be done without modifying code in numerous osC files and locations. (I presume it would however, which is probably why you took the server/database approach).

 

Dr_Lucas, it's gotta be frustrating, but want you to know you have done some good work here, and this contrib apparently works fine in your setup. I sure hope together we can figure this out for the rest of us out here. I've seen some other contribs out there for the time zone offset and they do look very intimidating (sort of bloatware approach). Your's is clean, concise coding. You and I both know it's probably some simple thing affecting a bunch of us from getting this to work. Please stick with this as it's badly needed.

 

Ron

Link to comment
Share on other sites

This is my first contribution (and first post too!)

 

I have made the changes required to get the order confirm emails as well as order status changes working. In addition, the date that appears in the footer has been fixed too. I have written a function to get the current database time and add the offset to it. You can then use it whenever you need the offset time (even outside of this contrib)

 

For people that are finding that the date is not changed in every place, I have added a comment at the end of the instructions. The most important parts of the application are fixed here (emails and visible date on site). You will need to modify a lot of files if you want to fix everything!

 

Thanks dr_lucas for your contribution. To be honest, from what I have read, the TZ is mostly ignored. I rely on the database date and add the offset and it seems to all work OK.

 

Contribution located at http://www.oscommerce.com/community/contributions,4156

Link to comment
Share on other sites

This is my first contribution (and first post too!)

 

I have made the changes required to get the order confirm emails as well as order status changes working. In addition, the date that appears in the footer has been fixed too. I have written a function to get the current database time and add the offset to it. You can then use it whenever you need the offset time (even outside of this contrib)

 

For people that are finding that the date is not changed in every place, I have added a comment at the end of the instructions. The most important parts of the application are fixed here (emails and visible date on site). You will need to modify a lot of files if you want to fix everything!

 

Thanks dr_lucas for your contribution. To be honest, from what I have read, the TZ is mostly ignored. I rely on the database date and add the offset and it seems to all work OK.

 

Contribution located at http://www.oscommerce.com/community/contributions,4156

 

Marty:

 

Thanks a bunch! It work perfectly! Great contribution!

 

Ron

Link to comment
Share on other sites

Marty:

 

Thanks a bunch! It work perfectly! Great contribution!

 

Ron

 

I found a problem in my code. One of the changes from Now() to Date_Add(Now()..... was incorrect in the checkout_process.php file. A new contribution file wih an update.txt has been uploaded. The error causes a problem in the order date, so please make the change if you loaded the last change.

 

Apologies for any inconvenience.

 

Martin

Link to comment
Share on other sites

  • 1 month later...

Hi,

 

Could someone help please ???

 

I changed the date format to d/m/y Is it conflict "time zone offset" ??

 

How can I apply the time format EMAIL_TEXT_DATE_ORDERED . ' ' . date("l j F, Y G:i:s ") . "\n\n";

 

instead the EMAIL_TEXT_DATE_ORDERED . ' ' . strftime(DATE_FORMAT_LONG) . "\n\n"; format in my order email confirmation ??

 

Any help will be very much appreciated pals ! :)

 

Elazar

Link to comment
Share on other sites

Sorry I will ask it correctly again..

 

Could someone help please ???

 

I changed the date format to d/m/y Is it conflict "time zone offset" ??

 

How can I apply the time format EMAIL_TEXT_DATE_ORDERED . ' ' . date("l j F, Y G:i:s ") . "\n\n";

 

instead of the EMAIL_TEXT_DATE_ORDERED . ' ' . strftime(DATE_FORMAT_LONG, strtotime($date_time)) . "\n"; format in my order email confirmation ??

 

I need to use the above date format (. date("l j F, Y G:i:s ") .)

 

Any help will be very much appreciated pals ! :)

 

Elazar

Link to comment
Share on other sites

Sorry I will ask it correctly again..

 

Could someone help please ???

 

I changed the date format to d/m/y Is it conflict "time zone offset" ??

 

How can I apply the time format EMAIL_TEXT_DATE_ORDERED . ' ' . date("l j F, Y G:i:s ") . "\n\n";

 

instead of the EMAIL_TEXT_DATE_ORDERED . ' ' . strftime(DATE_FORMAT_LONG, strtotime($date_time)) . "\n"; format in my order email confirmation ??

 

I need to use the above date format (. date("l j F, Y G:i:s ") .)

 

Any help will be very much appreciated pals ! :)

 

Elazar

 

Could someone help me please to do that? please.. :(

Link to comment
Share on other sites

  • 2 weeks later...

Hi,

 

I have installed this contribution, and it's working great.

 

However, I need some help with my specials page.

 

When I enter in a special that has been modified the day before it is coming up with the new price on the specials page but where the old price is displayed with a strikethrough it has $0.00. I am only getting this problem on Internet Explorer on PC's. Every other browser work's fine.

 

I am guessing it has something to do with the timezone offset contribution because it is not picking up the old price. Also in the admin if I enter in a product that was modified the day before it enters "00-00-0000" as the expiry date automatically?

 

Would it be something to do with changing the words "now()" in the specials files?

 

Could someone please help me with this it is urgent because my store is live!

 

Thanks,

 

Grant

Link to comment
Share on other sites

  • 3 weeks later...

Hello, I'm getting the following error in checkout_process.php when my customers use paypal to pay:

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where date_format(store_history_date, '%Y%m%d') = date_format(

update store_history set amount_orders = amount_orders + 1, amount_products = amount_products+1, orders_price=orders_price+ where date_format(store_history_date, '%Y%m%d') = date_format(now(), '%Y%m%d')

[TEP STOP]

 

I have modified paypal.php to include the same changes as made to checkout_process.php

 

any thoughts?

 

thanks!

 

Scott

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...