Skip to content

Instantly share code, notes, and snippets.

View tegansnyder's full-sized avatar
💭
Stay'n cold in Minnesota

Tegan Snyder tegansnyder

💭
Stay'n cold in Minnesota
View GitHub Profile
@tegansnyder
tegansnyder / Magento-purchases-per-year.sql
Created January 31, 2014 02:49
Magento - Purchases Per Year (PPY) - Direct SQL
SELECT AVG(order_cnt) AS ppy
FROM
(SELECT customer_id,
COUNT(customer_id) AS order_cnt
FROM
(SELECT so.customer_id,
YEAR(so.created_at) AS year_ordered,
group_concat(si.sku SEPARATOR ',') AS skus
FROM `sales_flat_order` AS so
INNER JOIN `sales_flat_order_item` AS si ON si.order_id=so.entity_id
@tegansnyder
tegansnyder / magento-average-cart-purchase-acp-all-time.sql
Created January 31, 2014 02:57
Magento - All time Average Cart Purchase value via direct SQL
SELECT AVG(average_base_subtotal) AS acp
FROM
(SELECT AVG(sub_query.base_subtotal) AS average_base_subtotal
FROM
(SELECT so.base_subtotal
FROM `sales_flat_order` AS so
INNER JOIN `sales_flat_order_item` AS si ON si.order_id=so.entity_id
GROUP BY entity_id) AS sub_query) AS sub_query
@tegansnyder
tegansnyder / magento-search-terms-resulting-in-zero-results-in-last-week.sql
Created January 31, 2014 15:49
Magento - Get list of search terms that returned zero results in the last week.
SELECT *
FROM catalogsearch_query
WHERE num_results = 0
AND updated_at >= date_sub(CURDATE(), interval 6 DAY)
AND updated_at <= CURDATE()
@tegansnyder
tegansnyder / customers-by-init-attribute.sql
Created February 6, 2014 15:42
Get a list of all customers by int attribute is_dealers - Magento sql
SELECT cv.value as `is_dealer`, e.* FROM customer_entity AS e
LEFT JOIN customer_entity_int AS cv
ON ( cv.attribute_id = (
SELECT attribute_id FROM eav_attribute
WHERE entity_type_id = e.entity_type_id
AND attribute_code = 'is_dealer'
)
AND cv.entity_id = e.entity_id)
WHERE cv.value = 1
@tegansnyder
tegansnyder / magento-permissions.sh
Created February 14, 2014 03:21
Magento permissions
find . -type f \-exec chmod 644 {} \;
find . -type d \-exec chmod 755 {} \;
find ./var -type d \-exec chmod 777 {} \;
find ./var -type f \-exec chmod 666 {} \;
find ./media -type d \-exec chmod 777 {} \;
find ./media -type f \-exec chmod 666 {} \;
chmod 777 ./app/etc
chmod 644 ./app/etc/*.xml
@tegansnyder
tegansnyder / telephones.sql
Created February 18, 2014 14:05
Magento get customer telephone
SELECT cv.value as `telephone`, e.* FROM customer_entity AS e
LEFT JOIN customer_address_entity_varchar AS cv
ON ( cv.attribute_id = (
SELECT attribute_id FROM eav_attribute
WHERE attribute_code = 'telephone'
)
AND cv.entity_id = e.entity_id)
WHERE LENGTH(cv.value) > 1
@tegansnyder
tegansnyder / telephone-update.php
Last active August 29, 2015 13:56
Update telephone numbers to 00
<?php
require_once('app/Mage.php');
umask(0);
Mage::app();
$db_conn = Mage::getSingleton('core/resource');
$r_conn = $db_conn->getConnection('core_read');
$w_conn = $db_conn->getConnection('core_write');
@tegansnyder
tegansnyder / csv_2_array.php
Created March 3, 2014 00:01
Read a CSV file to an associate array
<?php
$csv = array();
if (($handle = fopen('your_csv_file_name_here.csv', "r")) !== FALSE) {
$rowCounter = 0;
while (($rowData = fgetcsv($handle, 0, ",")) !== FALSE) {
if (0 === $rowCounter) {
$headerRecord = $rowData;
} else {
foreach ($rowData as $key => $value) {
@tegansnyder
tegansnyder / base_image.php
Created March 6, 2014 15:22
Get product base_image url
<?php
require_once('app/Mage.php');
umask(0);
Mage::app();
$product = Mage::getModel('catalog/product')->load(1);
echo Mage::getModel('catalog/product_media_config')->getMediaUrl($product->getImage());
@tegansnyder
tegansnyder / translation-helpers.sh
Created March 10, 2014 15:34
Find extensions that include translation helpers
echo "Community Namespace"
echo "------------------------------------------"
cd app/code/community
for i in $(ls -d */)
do
echo ${i%%/} && fgrep -Rho --include='*.php' '__(' ${i%%/} | wc -l
done
echo
echo "Local Namespace"
echo "------------------------------------------"