Magento orders by sku

SQL code for orders by sku

SQL code for orders by sku


SELECT sales_flat_order.increment_id,
 sales_flat_order_item.order_id,
 sales_flat_order_item.sku,
 sales_flat_order.status,
 sales_flat_order_item.name,
 sales_flat_order.customer_email
 FROM dbname.sales_flat_order_item sales_flat_order_item
 INNER JOIN dbname.sales_flat_order sales_flat_order
 ON (sales_flat_order_item.order_id = sales_flat_order.entity_id)
 WHERE (sales_flat_order_item.sku = 'fill in sku no ')
 AND (sales_flat_order.status <> 'Canceled')
ORDER BY sales_flat_order.increment_id DESC


Install OpenSSL en SOAP on CentOS

Install OpenSSL
yum install openssl
Note: This is typically installed on CentOS by default.

Install SOAP
yum install php-soap

apachectl restart

Disable paypal logo from sidebar in Magento

System > Configuration > Payment Methods

> Paypal All in One Payments Solutions
> Paypal Payments Advanced (Includes Express Checkout)
> Basic Settings – Paypal Payments Advanced
> Frontend Expereince settings
> Paypal Product Logo

Sql query: Delete all Orders, Sales & Customer Data in Magento

 Sql query: Delete all Orders, Sales & Customer Data in Magento

# Tested on Magento CE 1.4.1.1 - 1.9.0.1

SET FOREIGN_KEY_CHECKS=0;

##############################
# SALES RELATED TABLES
##############################
TRUNCATE `sales_flat_creditmemo`;
TRUNCATE `sales_flat_creditmemo_comment`;
TRUNCATE `sales_flat_creditmemo_grid`;
TRUNCATE `sales_flat_creditmemo_item`;
TRUNCATE `sales_flat_invoice`;
TRUNCATE `sales_flat_invoice_comment`;
TRUNCATE `sales_flat_invoice_grid`;
TRUNCATE `sales_flat_invoice_item`;
TRUNCATE `sales_flat_order`;
TRUNCATE `sales_flat_order_address`;
TRUNCATE `sales_flat_order_grid`;
TRUNCATE `sales_flat_order_item`;
TRUNCATE `sales_flat_order_payment`;
TRUNCATE `sales_flat_order_status_history`;
TRUNCATE `sales_flat_quote`;
TRUNCATE `sales_flat_quote_address`;
TRUNCATE `sales_flat_quote_address_item`;
TRUNCATE `sales_flat_quote_item`;
TRUNCATE `sales_flat_quote_item_option`;
TRUNCATE `sales_flat_quote_payment`;
TRUNCATE `sales_flat_quote_shipping_rate`;
TRUNCATE `sales_flat_shipment`;
TRUNCATE `sales_flat_shipment_comment`;
TRUNCATE `sales_flat_shipment_grid`;
TRUNCATE `sales_flat_shipment_item`;
TRUNCATE `sales_flat_shipment_track`;
TRUNCATE `sales_invoiced_aggregated`;            # ??
TRUNCATE `sales_invoiced_aggregated_order`;        # ??
TRUNCATE `log_quote`;

ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;

#########################################
# DOWNLOADABLE PURCHASED
#########################################
TRUNCATE `downloadable_link_purchased`;
TRUNCATE `downloadable_link_purchased_item`;

ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;

#########################################
# RESET ID COUNTERS
#########################################
TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;

##############################
# CUSTOMER RELATED TABLES
##############################
TRUNCATE `customer_address_entity`;
TRUNCATE `customer_address_entity_datetime`;
TRUNCATE `customer_address_entity_decimal`;
TRUNCATE `customer_address_entity_int`;
TRUNCATE `customer_address_entity_text`;
TRUNCATE `customer_address_entity_varchar`;
TRUNCATE `customer_entity`;
TRUNCATE `customer_entity_datetime`;
TRUNCATE `customer_entity_decimal`;
TRUNCATE `customer_entity_int`;
TRUNCATE `customer_entity_text`;
TRUNCATE `customer_entity_varchar`;
TRUNCATE `tag`;
TRUNCATE `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `tag_properties`;            ## CHECK ME
TRUNCATE `wishlist`;
TRUNCATE `log_customer`;

ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `tag` AUTO_INCREMENT=1;
ALTER TABLE `tag_relation` AUTO_INCREMENT=1;
ALTER TABLE `tag_summary` AUTO_INCREMENT=1;
ALTER TABLE `tag_properties` AUTO_INCREMENT=1;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;

##############################
# ADDITIONAL LOGS
##############################
TRUNCATE `log_url`;
TRUNCATE `log_url_info`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;
TRUNCATE `report_event`;
TRUNCATE `report_viewed_product_index`;
TRUNCATE `sendfriend_log`;
### ??? TRUNCATE `log_summary`

ALTER TABLE `log_url` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;
### ??? ALTER TABLE `log_summary` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

 

Installing OAuth for PHP clients on CentOS 6

Installing EPEL and Remi repo on CentOS 6

wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
sudo rpm -Uvh remi-release-6*.rpm epel-release-6*.rpm
sudo vi /etc/yum.repos.d/remi.repo

set : enabled=1 on the topmost repo

Installing OAuth for PHP clients

yum install php-pecl-oauth

REASON 442: FAILED TO ENABLE VIRTUAL ADAPTER

REASON 442: FAILED TO ENABLE VIRTUAL ADAPTER on Windows 8.1

 cisco-vpn-adapter3

If you receive this error on Windows 8 or Windows 8.1 while trying to connect with the Cisco VPN Client then the solution is a simple registry fix.

To fix:

cisco-vpn-adapter2

Click Start and type regedit in the Search field and hit enter.
Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA
Find the String Value called DisplayName
Right click and select Modify from the context menu.
In Value data, remove @oemX.inf,%CVirtA_Desc%;. The Value data should only contain Cisco Systems VPN Adapter for 64-bit Windows.
Click Ok.

cisco-vpn-adapter3
Close Registry Editor.

Retry your Cisco VPN Client connection.

MySQL query caching for ex. Magento

Login Plesk MySQL

On server with Parallels Plesk Panel versions 8.x, 9.x and above admin password is stored in file /etc/psa/.psa.shadow. Use the following command to get the password:

~# cat /etc/psa/.psa.shadow

Since version 10 admin password stored in file /etc/psa/.psa.shadow is encrypted. Use the following command to get admin password in plain text:

~# /usr/local/psa/bin/admin --show-password

Despite version of Parallels Plesk Panel you may use the following command to login to mysql with root privileges:

~# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

MySQL query caching

Login with:

mysql -u username -p
SHOW VARIABLES LIKE 'have_query_cache';

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)
SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)
SHOW VARIABLES LIKE 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.01 sec)

Hieronder zie je wat de meest belangrijke onderdelen betekenen in de bovenstaande lijst:

query_cache_size : Dit is de grootte van de cache in bytes. Wanneer je de waarde van deze instelling op het getal 0 zet zul je effectief caching uitzetten. query_cache_type : Deze waarde moet ingesteld staan op ON of het getal 0 om query caching standaard aan te hebben staan. query_cache_limit – Dit is de maximale query grootte (wederom in bytes) dat gecached zal worden.

Wanneer de instelling query_cache_size op 0 staat, of je deze zelf wilt aanpassen zul je onderstaande query uit moeten voeren. Hou er rekening mee dat de waarde in bytes is! Bijvoorbeeld: wanneer je 16 megabyte wil toewijzen aan de cache zul je deze moeten berekenen met : 1024 x 1024 x 16 = 16777216. Stel dan 16777216 in als de waarde voor de instelling query_cache_size. SET GLOBAL query_cache_size = 16777216;

SET GLOBAL query_cache_size = 16777216;

De overige instellingen zijn op een soortgelijke manier in te stellen: SET GLOBAL query_cache_type = 1; SET GLOBAL query_cache_limit = 1048476;

SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_limit = 1048476;

Hoe kun je er zeker van zijn dat je instellingen werken? Simpel, je kunt hier de query SHOW STATUS voor gebruiken om alle variabelen te laten tonen die beginnen met “Qc”. Laten we een kijkje nemen: ======

SET GLOBAL query_cache_size = 16777216;
SHOW STATUS LIKE 'Qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16759696 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.01 sec)

Bulk edit price in Magento with SQL query

Select Prices

SELECT 
catalog_product_entity_decimal 
WHERE
catalog_product_entity_decimal.attribute_id=75  

Update Prices with Value

UPDATE 
catalog_product_entity_decimal 
SET catalog_product_entity_decimal.value = 35.9500
WHERE
catalog_product_entity_decimal.attribute_id=75  

(catalog_product_entity_decimal.attribute_id=75 = price)

Empty Value

UPDATE 
catalog_product_entity_decimal 
SET catalog_product_entity_decimal.value = NULL
WHERE
catalog_product_entity_decimal.value_id=120

Update Prices with %

UPDATE
catalog_product_entity_decimal
SET
catalog_product_entity_decimal.value = catalog_product_entity_decimal.value*1.19
WHERE
catalog_product_entity_decimal.attribute_id=75
OR
catalog_product_entity_decimal.attribute_id=76;

1.19 is 19% tax
Id=75 is the price
Id = 76 is the special price (see table eav_attribute table for codes)

Update Prices with Rounded Value

UPDATE
catalog_product_entity_decimal
SET
catalog_product_entity_decimal.value= ROUND(catalog_product_entity_decimal.value,0)
WHERE
catalog_product_entity_decimal.attribute_id=75
OR
catalog_product_entity_decimal.attribute_id=76;

Run this if you want all your prices to be also rounded e..g 25.00 instead of 25.01

Now Refresh Cache in Magento

‘Open With’ Menu in Mac OS X

Clearing Finder’s ‘Open With’ Menu in Mac OS X Mountain lion

Enter the following command :

/System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister -kill -r -domain local -domain system -domain user

Mac Pro Upgrade HD to SSD

Download Carbon Copy Cloner (30 day trial), and install on old hard drive
Aattach new hard drive, can be internally or external
OSX will tell you you attached a drive it can’t read and will automatically start Disk Utility for you (this utility is under Applications, then Utilities)

Select the new drive in Left panel, and erase near middle top
Defaults are fine (journaled is the default)
Give it a new name (bottom box) like NewSSD, SSD, or……
Click erase near bottom right
Close Disk Utility when finished (a few seconds)

Run Carbon Copy Cloner (it’s under your applications)

Choose your old hard drive as the SOURCE, top left
Choose your SSD as destination (top right)
On the left middle area, uncheck any large data folders. You can use Finder and Get Info to confirm this. Use a pen and paper to confirm you are leaving off enough data to fit on the new SSD.

Accept the rest of Carbon Copy Cloner defaults (i.e. don’t change the settings)
Click on Clone (bottom right) this may run 45 minutes to several hours depending on whether both drives are internal or one is connected with Thunderbolt, USB or Firewire, and the amount of data you are copying.

Replace your old hard drive with your new SSD or
If you are using LION, you can just go into System Preferences -> System -> Startup Disk and choose the new SSD as the Startup Disk and you don’t have to change any drive positions if both are already setup internally