Select osCommerce customers subscribed after date X
[crayon lang=»mysql»]
SELECT c.customers_gender, c.customers_firstname, c.customers_lastname, c.customers_email_address, ci.customers_info_date_account_created
FROM customers c, customers_info ci
WHERE c.customers_id = ci.customers_info_id
AND ci.customers_info_date_account_created > ‹2010-11-05 00:00:00›;
[/crayon]
Select osCommerce customers subscribed to newsletter
[crayon lang=»mysql»]
SELECT `customers_gender`, `customers_lastname`, `customers_email_address`
FROM `customers`
WHERE `customers_newsletter` = 1;[/crayon]
Select osCommerce customers und address_book
[crayon lang=»mysql»]
SELECT * FROM address_book a, customers c
WHERE a.customers_id = c.customers_id;[/crayon]
Get number of orders for a given time range of an osCommerce store
[crayon lang=»mysql»]
SELECT * FROM `orders`
WHERE `orders_status` = 3 and `date_purchased` > ‹2010-01-01›;[/crayon]
Select all products that don’t have a manufacturer assigned
[crayon lang=»mysql»]
SELECT p.products_id, pd.products_name
FROM products p, products_description pd
WHERE p.products_id = pd.products_id
AND pd.language_id = 2
AND p.manufacturers_id = 0;
[/crayon]
Select products without product model number
[crayon lang=»mysql»]
SELECT p.products_id, pd.products_name
FROM products p, products_description pd
WHERE p.products_id = pd.products_id
AND pd.language_id = 2
AND p.products_model = »;
[/crayon]
Recalculating product net price after enabling VAT
[crayon lang=»mysql»]
UPDATE products SET `products_tax_class_id` = ‹1›;
UPDATE products SET `products_price` = `products_price` / 1.076;
UPDATE specials SET `specials_new_products_price` = `specials_new_products_price` / 1.076;
[/crayon]
Recalculating product net price with changed VAT values
Nettopreis von 100 CHF bei 7.6% MwSt. = 92.93680297
Nettopreis von 100 CHF bei 8.0% MwSt. = 92.59259259
Umrechnung des Nettopreises mit Faktor 1.00371747
Nettopreis von 100 CHF bei 2.4% MwSt. = 97.65625
Nettopreis von 100 CHF bei 2.5% MwSt. = 97.560975609756098
Umrechnung des Nettopreises mit Faktor 1.0009765625
osCommerce stores with only 1 tax class
[crayon lang=»mysql»]
UPDATE products SET products_price = products_price / 1.00371747;
UPDATE specials SET `specials_new_products_price` = `specials_new_products_price` / 1.00371747;
UPDATE `products_attributes` SET `options_values_price` = `options_values_price` / 1.00371747;
[/crayon]
osCommerce stores (e.g. swisscart® v3) using B2B pricing add-on
[crayon lang=»mysql»]
UPDATE products SET products_price = products_price / 1.00371747;
UPDATE products SET products_price_2 = products_price_2 / 1.00371747;
UPDATE products SET products_price_3 = products_price_3 / 1.00371747;
UPDATE specials SET `specials_new_products_price` = `specials_new_products_price` / 1.00371747;
UPDATE `products_attributes` SET `options_values_price` = `options_values_price` / 1.00371747;
[/crayon]
With 2 Tax Classes
[crayon lang=»mysql»]
UPDATE products SET products_price = products_price / 1.00371747 where products_tax_class_id = 1;
UPDATE products SET products_price = products_price / 1.0009765625 where products_tax_class_id = 2;
[/crayon]
