Account history and order no.'s

[burtoni]

Test purposes made the order no.'s rise and other reports mount as normal.

 

How can I delete the history of these orders so that the order no. starts at 1 again and the reports (most seen products etc.) are clear once I go public?

[burtoni]

I would appreciate a quick reply.

[241]

do not bump it is rude and in breach of forum rules

 

to reset products viewed

 

UPDATE `products_description` SET `products_viewed` = '0'

 

for a default version of osCommerce to reset orders info

 

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
 orders_id int(11) NOT NULL auto_increment,
 customers_id int(11) NOT NULL default '0',
 customers_name varchar(64) NOT NULL default '',
 customers_company varchar(32) default NULL,
 customers_street_address varchar(64) NOT NULL default '',
 customers_suburb varchar(32) default NULL,
 customers_city varchar(32) NOT NULL default '',
 customers_postcode varchar(10) NOT NULL default '',
 customers_state varchar(32) default NULL,
 customers_country varchar(32) NOT NULL default '',
 customers_telephone varchar(32) NOT NULL default '',
 customers_email_address varchar(96) NOT NULL default '',
 customers_address_format_id int(5) NOT NULL default '0',
 delivery_name varchar(64) NOT NULL default '',
 delivery_company varchar(32) default NULL,
 delivery_street_address varchar(64) NOT NULL default '',
 delivery_suburb varchar(32) default NULL,
 delivery_city varchar(32) NOT NULL default '',
 delivery_postcode varchar(10) NOT NULL default '',
 delivery_state varchar(32) default NULL,
 delivery_country varchar(32) NOT NULL default '',
 delivery_address_format_id int(5) NOT NULL default '0',
 billing_name varchar(64) NOT NULL default '',
 billing_company varchar(32) default NULL,
 billing_street_address varchar(64) NOT NULL default '',
 billing_suburb varchar(32) default NULL,
 billing_city varchar(32) NOT NULL default '',
 billing_postcode varchar(10) NOT NULL default '',
 billing_state varchar(32) default NULL,
 billing_country varchar(32) NOT NULL default '',
 billing_address_format_id int(5) NOT NULL default '0',
 payment_method varchar(32) NOT NULL default '',
 cc_type varchar(20) default NULL,
 cc_owner varchar(64) default NULL,
 cc_number varchar(32) default NULL,
 cc_expires varchar(4) default NULL,
 last_modified datetime default NULL,
 date_purchased datetime default NULL,
 orders_status int(5) NOT NULL default '0',
 orders_date_finished datetime default NULL,
 currency char(3) default NULL,
 currency_value decimal(14,6) default NULL,
 PRIMARY KEY  (orders_id)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `orders_products`
-- 

DROP TABLE IF EXISTS orders_products;
CREATE TABLE orders_products (
 orders_products_id int(11) NOT NULL auto_increment,
 orders_id int(11) NOT NULL default '0',
 products_id int(11) NOT NULL default '0',
 products_model varchar(12) default NULL,
 products_name varchar(64) NOT NULL default '',
 products_price decimal(15,4) NOT NULL default '0.0000',
 final_price decimal(15,4) NOT NULL default '0.0000',
 products_tax decimal(7,4) NOT NULL default '0.0000',
 products_quantity int(2) NOT NULL default '0',
 PRIMARY KEY  (orders_products_id)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `orders_products_attributes`
-- 

DROP TABLE IF EXISTS orders_products_attributes;
CREATE TABLE orders_products_attributes (
 orders_products_attributes_id int(11) NOT NULL auto_increment,
 orders_id int(11) NOT NULL default '0',
 orders_products_id int(11) NOT NULL default '0',
 products_options varchar(32) NOT NULL default '',
 products_options_values varchar(32) NOT NULL default '',
 options_values_price decimal(15,4) NOT NULL default '0.0000',
 price_prefix char(1) NOT NULL default '',
 PRIMARY KEY  (orders_products_attributes_id)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `orders_products_download`
-- 

DROP TABLE IF EXISTS orders_products_download;
CREATE TABLE orders_products_download (
 orders_products_download_id int(11) NOT NULL auto_increment,
 orders_id int(11) NOT NULL default '0',
 orders_products_id int(11) NOT NULL default '0',
 orders_products_filename varchar(255) NOT NULL default '',
 download_maxdays int(2) NOT NULL default '0',
 download_count int(2) NOT NULL default '0',
 PRIMARY KEY  (orders_products_download_id)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `orders_status`
-- 

DROP TABLE IF EXISTS orders_status;
CREATE TABLE orders_status (
 orders_status_id int(11) NOT NULL default '0',
 language_id int(11) NOT NULL default '1',
 orders_status_name varchar(32) NOT NULL default '',
 PRIMARY KEY  (orders_status_id,language_id),
 KEY idx_orders_status_name (orders_status_name)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `orders_status_history`
-- 

DROP TABLE IF EXISTS orders_status_history;
CREATE TABLE orders_status_history (
 orders_status_history_id int(11) NOT NULL auto_increment,
 orders_id int(11) NOT NULL default '0',
 orders_status_id int(5) NOT NULL default '0',
 date_added datetime NOT NULL default '0000-00-00 00:00:00',
 customer_notified int(1) default '0',
 comments text,
 PRIMARY KEY  (orders_status_history_id)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `orders_total`
-- 

DROP TABLE IF EXISTS orders_total;
CREATE TABLE orders_total (
 orders_total_id int(10) unsigned NOT NULL auto_increment,
 orders_id int(11) NOT NULL default '0',
 title varchar(255) NOT NULL default '',
 text varchar(255) NOT NULL default '',
 value decimal(15,4) NOT NULL default '0.0000',
 class varchar(32) NOT NULL default '',
 sort_order int(11) NOT NULL default '0',
 PRIMARY KEY  (orders_total_id),
 KEY idx_orders_total_orders_id (orders_id)
) TYPE=MyISAM;

[burtoni]

And where do I :

 

a. put the code?

b. change the code?

c. which file / through Myphpadmin?

 

Is there noother simple possibility through the admin?

[241]

run the sql through phpmyadmin

[burtoni]

Hey 241 - Gosh, thats a heck of a lot of code just to reset the order numbers!?

 

I'll run both SQL queries on my database via PhpMyadmin and see what happens!

 

 

Where can I find this kind of information directly next time (except from search of course), is there a contribution or something?

 

Also, please let me know if there isn't an easier way of doing this using the "admin". There should be some kinda link to click on to automatically do that?

 

Thanks anyway - I'll remember not to bump!

 

Look forward to your answer....

[241]

no there is not an easier way to do this through the admin unless you want to code it by creating an action which has all the code in it.

 

the sql given is just a plain drop table create table with no inserts

[burtoni]

I did a server backup, but I still want to make sure before I run this sql:

 

The products are to be kept, it is only the order numbers I want reseting. Does

cc_owner varchar(64) default NULL,

not reset the shop owner?

 

Please just point out the parts which reset the REPORTS in the 'admin' (most sold products, reset order-no.s and customer turnover statistics), but not remove any products or other details from the onlineshop.

[kalle]

I did a server backup, but I still want to make sure before I run this sql:

 

The products are to be kept, it is only the order numbers I want reseting. Does

cc_owner varchar(64) default NULL,

not reset the shop owner?

 

Please just point out the parts which reset the REPORTS in the 'admin' (most sold products, reset order-no.s and customer turnover statistics), but not remove any products or other details from the onlineshop.

<{POST_SNAPBACK}>

 

 

The field "cc_owner" is the Credit Card owner data if it's specified during the checkout procedure by the customer. It's not the same as store owner field, the store owner (and other configuration data) is stored in database table "configuration".

 

Most sold products are counted from "orders" -table every time you run the report, customer stats are also counted from "orders" -table. The only exception is most viewed products, they are counted from "products_description" -table where there is a separate field that is increased every time a product is viewed.

 

 

Cheers,

 

Kalle!

[burtoni]

Thanks Kalle...

 

So in other words, if i use the whole sql code and run it in PhpMyAdmin it is just the three reports that are reset (Admin)?

 

Nothing else thats connected to the catalog will be erased?

[kalle]

Thanks Kalle...

 

So in other words, if i use the whole sql code and run it in PhpMyAdmin it is just the three reports that are reset (Admin)?

 

Nothing else thats connected to the catalog will be erased?

<{POST_SNAPBACK}>

 

Delete this part from the SQL code before you execute it:

 

"

--

-- Table structure for table `orders_status`

--

 

DROP TABLE IF EXISTS orders_status;

CREATE TABLE orders_status (

orders_status_id int(11) NOT NULL default '0',

language_id int(11) NOT NULL default '1',

orders_status_name varchar(32) NOT NULL default '',

PRIMARY KEY (orders_status_id,language_id),

KEY idx_orders_status_name (orders_status_name)

) TYPE=MyISAM;

 

-- --------------------------------------------------------

"

 

 

..that code would erase the possible order statuses from your system, and you'd have to create them again in Admin -> Localization -> Orders Status to make the shop/checkout procedure work again.

 

 

 

Cheers,

 

Kalle!

[burtoni]

I took out the part you told me, but the "best products purchased" report is still shown.

 

How do I get rid of that? Is that the sql code we missed out?

[kalle]

I took out the part you told me, but the "best products purchased" report is still shown.

 

How do I get rid of that? Is that the sql code we missed out?

<{POST_SNAPBACK}>

 

Nope. Execute the following SQL code with phpMyAdmin and the statistics will be reset:

 

UPDATE products SET products_ordered = 0 WHERE products_id > 0;

 

 

Cheers,

 

Kalle!

[burtoni]

Great thanks m8 !

 

Sorry for pushing, but you you help me out on my last bug too? I need to get this published today.

 

Change "GreetUser" (%s)

http://www.oscommerce.com/forums/index.php?showtopic=171489&hl=

(you only really need to read the last post "How do I declare $customer_last_name and $customer_gender as global?")

 

After that I should be ready to go "live".