Guest Posted May 7, 2006 Share Posted May 7, 2006 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? Quote Link to comment Share on other sites More sharing options...
dr_lucas Posted May 8, 2006 Author Share Posted May 8, 2006 (edited) 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 May 8, 2006 by dr_lucas Quote Did I help you? Click "Like" or "Thanks"! It's free of charge. :)My contributions:Total Configuration (newly updated 07/2018, for both osC 2.2 and 2.3.4.1 BS Frozen CE)User Tracking with Admin 1.0 (newly updated 07/2018)FedEx - Web Services v9, FAQ System , Who's Online Enhancement, Order Editor, MoneyBookers IPN, Ship in Cart (MS2), Admin Products Paging, Margin Report v1.00, 2Checkout INS / IPN (Instant Notification System) for MS2.2, Visitor Web Stats, Time Zone Offset - Adjust to match your location, Category Meta Tags Link to comment Share on other sites More sharing options...
Guest Posted May 13, 2006 Share Posted May 13, 2006 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! Quote Link to comment Share on other sites More sharing options...
Guest Posted May 13, 2006 Share Posted May 13, 2006 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. Quote Link to comment Share on other sites More sharing options...
dr_lucas Posted May 13, 2006 Author Share Posted May 13, 2006 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... Quote Did I help you? Click "Like" or "Thanks"! It's free of charge. :)My contributions:Total Configuration (newly updated 07/2018, for both osC 2.2 and 2.3.4.1 BS Frozen CE)User Tracking with Admin 1.0 (newly updated 07/2018)FedEx - Web Services v9, FAQ System , Who's Online Enhancement, Order Editor, MoneyBookers IPN, Ship in Cart (MS2), Admin Products Paging, Margin Report v1.00, 2Checkout INS / IPN (Instant Notification System) for MS2.2, Visitor Web Stats, Time Zone Offset - Adjust to match your location, Category Meta Tags Link to comment Share on other sites More sharing options...
Guest Posted May 13, 2006 Share Posted May 13, 2006 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? Quote Link to comment Share on other sites More sharing options...
Guest Posted May 14, 2006 Share Posted May 14, 2006 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. Quote Link to comment Share on other sites More sharing options...
dr_lucas Posted May 15, 2006 Author Share Posted May 15, 2006 (edited) 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 ModuleDesigned 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 May 15, 2006 by dr_lucas Quote Did I help you? Click "Like" or "Thanks"! It's free of charge. :)My contributions:Total Configuration (newly updated 07/2018, for both osC 2.2 and 2.3.4.1 BS Frozen CE)User Tracking with Admin 1.0 (newly updated 07/2018)FedEx - Web Services v9, FAQ System , Who's Online Enhancement, Order Editor, MoneyBookers IPN, Ship in Cart (MS2), Admin Products Paging, Margin Report v1.00, 2Checkout INS / IPN (Instant Notification System) for MS2.2, Visitor Web Stats, Time Zone Offset - Adjust to match your location, Category Meta Tags Link to comment Share on other sites More sharing options...
Guest Posted May 15, 2006 Share Posted May 15, 2006 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. Quote Link to comment Share on other sites More sharing options...
dr_lucas Posted May 15, 2006 Author Share Posted May 15, 2006 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... Quote Did I help you? Click "Like" or "Thanks"! It's free of charge. :)My contributions:Total Configuration (newly updated 07/2018, for both osC 2.2 and 2.3.4.1 BS Frozen CE)User Tracking with Admin 1.0 (newly updated 07/2018)FedEx - Web Services v9, FAQ System , Who's Online Enhancement, Order Editor, MoneyBookers IPN, Ship in Cart (MS2), Admin Products Paging, Margin Report v1.00, 2Checkout INS / IPN (Instant Notification System) for MS2.2, Visitor Web Stats, Time Zone Offset - Adjust to match your location, Category Meta Tags Link to comment Share on other sites More sharing options...
Guest Posted May 15, 2006 Share Posted May 15, 2006 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted May 15, 2006 Share Posted May 15, 2006 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted May 15, 2006 Share Posted May 15, 2006 Coopco. I totally agree with you. Quote Link to comment Share on other sites More sharing options...
Guest Posted May 15, 2006 Share Posted May 15, 2006 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. Quote Link to comment Share on other sites More sharing options...
tomliuwhite Posted June 16, 2006 Share Posted June 16, 2006 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? Quote Link to comment Share on other sites More sharing options...
Guest Posted June 16, 2006 Share Posted June 16, 2006 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. Quote Link to comment Share on other sites More sharing options...
Guest Posted August 30, 2006 Share Posted August 30, 2006 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 Quote Link to comment Share on other sites More sharing options...
marty_mcfly Posted September 2, 2006 Share Posted September 2, 2006 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted September 3, 2006 Share Posted September 3, 2006 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 Quote Link to comment Share on other sites More sharing options...
marty_mcfly Posted September 4, 2006 Share Posted September 4, 2006 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 Quote Link to comment Share on other sites More sharing options...
Elazar Posted October 15, 2006 Share Posted October 15, 2006 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 Quote Link to comment Share on other sites More sharing options...
Elazar Posted October 15, 2006 Share Posted October 15, 2006 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 Quote Link to comment Share on other sites More sharing options...
Elazar Posted October 19, 2006 Share Posted October 19, 2006 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.. :( Quote Link to comment Share on other sites More sharing options...
Grant7 Posted October 30, 2006 Share Posted October 30, 2006 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 Quote Link to comment Share on other sites More sharing options...
olsonsp4c Posted November 16, 2006 Share Posted November 16, 2006 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 Quote Addons I've crafted: osC reCaptcha | Live Support phpOnline | Store Mode | Simple Categories Header | Authorize.Net Infobox Package Tracking Plus | USPS Flat Rate Shipping | USPS Shipping Insurance | RapidSSL Infobox Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.