Preise und Gewicht korrigieren

Aus NULLRAFFER
Wechseln zu: Navigation, Suche

Preise korrigieren / anzeigen

Preisaufschläge sichern

Eventuell neues Datenfeld erstellen

ALTER TABLE `products`  ADD `products_price_charge_old` DECIMAL(10,2) NULL DEFAULT NULL AFTER `products_price_charge`;

"Alte" Aufschläge sichern

update products set products_price_charge_old=products_price_charge where manufacturers_id = (select manufacturers_id from manufacturers where manufacturers_name='HEO');


Preisaufschläge berechnen / anzeigen / korrigieren

Preisaufschlag korrigieren

Den Preisaufschlag (Roh-Gewinn) anhand des EK- und VK-Preises neu berechnen.

update products set products_price_charge=round(((products_price*1.19)/products_purchase_price),2) where manufacturers_id = (select manufacturers_id from manufacturers where manufacturers_name='HEO');

ACHTUNG: Der Preisaufschlag beinhaltet die Mehrwertsteuer, ist also von Netto-EK-Preis auf Brutto-VK-Preis berechnet.


Zu geringe Marge korrigieren

Dies ist ganz wichtig, falls der Preisaufschlag kleiner als 1.19 ist !!!

Dann wäre (also bei 1.19) die Gewinnspanne = 0 (wegen Mehrwertsteuer).

Somit muss man solche Fehler korrigieren, da dies öfter vorkommen kann. Und schlägt gleich eine geringe Marge mit drauf bei der Korrektur.

update products set products_price_charge=1.3 where products_price_charge<=1.25;


Einen neuen Preis berechnen (gerundet)

Will man den Preis anhand des Aufschlages (gerundet auf die 10er-Stelle) korrigieren, dann geht das so:

update products set products_price=round((products_purchase_price*products_price_charge),1)/1.19 where manufacturers_id = (select manufacturers_id from manufacturers where manufacturers_name='HEO');


Neue Preisaufschläge generieren

Neuer Preisaufschlag anhand vorhandener Marge

Wir machen einfach aus hohen Aufschlägen geringere Margen:

update products set products_price_charge=1.4, products_price=round((products_purchase_price*1.4),1)/1.19 where products_price_charge>=1.4 and products_price_charge<1.8 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=1.6, products_price=round((products_purchase_price*1.6),1)/1.19 where products_price_charge>=1.8 and products_price_charge<2.1 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=1.8, products_price=round((products_purchase_price*1.8),1)/1.19 where products_price_charge>=2.1 and products_price_charge<2.5 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=2.0, products_price=round((products_purchase_price*2),1)/1.19 where products_price_charge>=2.5 and products_price_charge<3 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=2.3, products_price=round((products_purchase_price*2.3),1)/1.19 where products_price_charge>=3 and products_price_charge<4 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=2.5, products_price=round((products_purchase_price*2.5),1)/1.19 where products_price_charge>=4 and products_price_charge<6 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=3, products_price=round((products_purchase_price*3),1)/1.19 where products_price_charge>=6 and products_price_charge<10 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=4, products_price=round((products_purchase_price*4),1)/1.19 where products_price_charge>=10 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');


Neuer Preisaufschlag anhand des Preises

ACHTUNG: Hiermit kommen wir auf Netto-Margen von unter 10% - also bitte mit Vorsicht zu machen !!!


update products set products_price_charge=1.3, products_price=round((products_purchase_price*1.3),1)/1.19 where products_price_charge>1.3 and products_price>=100 and products_price<250 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=1.29, products_price=round((products_purchase_price*1.29),1)/1.19 where products_price_charge>1.29 and products_price>=250 and products_price<500 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=1.26, products_price=round((products_purchase_price*1.26),1)/1.19 where products_price_charge>1.26 and products_price>=500 and products_price<1000 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=1.24, products_price=round((products_purchase_price*1.24),1)/1.19 where products_price_charge>1.24 and products_price>=1000 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');


Alternativer neuer Preisaufschlag anhand des Preises

update products set products_price_charge=1.5, products_price=round((products_purchase_price*1.5),1)/1.19 where products_price_charge>1.5 and products_price<100 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=1.3, products_price=round((products_purchase_price*1.3),1)/1.19 where products_price_charge>1.3 and products_price>=100 and products_price<500 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=1.27, products_price=round((products_purchase_price*1.27),1)/1.19 where products_price_charge>1.27 and products_price>=500 and products_price<2000 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_price_charge=1.25, products_price=round((products_purchase_price*1.25),1)/1.19 where products_price_charge>1.25 and products_price>=2000 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');


Anzeige der Preisdaten

Abhängig von den Aufschlägen die Preise und sonstige Daten anzeigen:

SELECT p.products_id, p.products_model, p.products_purchase_price, p.products_price, (p.products_price/p.products_purchase_price), p.products_price_charge, p.products_price_charge_old, pd.products_name, p.products_image, p.products_image_med, 
p.products_image_lrg, p.products_image_sm_1, p.products_image_xl_1 FROM products p, products_description pd WHERE p.manufacturers_id=1 and p.products_status=1 
and (p.products_price_charge>3 or p.products_price_charge<1.3) 
and p.products_id=pd.products_id and pd.language_id=2


Kurzübersicht von wichtigen Preisdaten

select products_id, products_model, products_price, products_purchase_price, (products_price/products_purchase_price), products_price_charge, products_price_charge_old from products where manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO') 
and products_id>19090;


Margen anhand der vorherigen Margen korrigieren

update products set products_price_charge=products_price_charge_old, products_price=round((products_purchase_price*products_price_charge_old),1)/1.19 where products_price_charge>products_price_charge_old 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');


Gewicht

Korrektur des Gewichtes (und damit evtl. Versandpreises)

Anhand des Netto-VK-Preises wird das Gewicht in kg so ersetzt, dass bei einem gewichtabhängigen Versand (zb. DHL) ein höherer Versandpreis erzielt wird.

update products set products_weight=3 where products_weight<=0 and products_price>=250 and products_price<500 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_weight=5 where products_weight<=0 and products_price>=500 and products_price<1000 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_weight=6 where products_weight<=0 and products_price>1000 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');
update products set products_weight=11 where products_weight<=0 and products_price>5000 
and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');

update products set products_weight=35 where products_weight<=0 and products_price>10000 and manufacturers_id=(select manufacturers_id from manufacturers where manufacturers_name='HEO');</pre>