brandnooz - MySQL - Cacher Snippet

6/11/2013 - 6:55 AM
brandnooz - MySQL

# dtaus:
SELECT id, LENGTH(account_number) AS le FROM receipts ORDER BY le DESC

# --- lettr csv export (;)
SELECT email, first_name AS 'firstname', last_name AS 'lastname' FROM users WHERE wants_sweepstake_notifications = 1

# ==================
#  Additional Products for a month
# ==================
SELECT box_order_product_relations.*, products.name FROM box_order_product_relations 
JOIN products ON product_id = products.id
WHERE MONTH = 7 AND YEAR = 2013

# ==================
#  All box orders with special coupon
# ==================
SELECT * FROM box_orders JOIN box_coupons ON box_coupon_id = box_coupons.id 
WHERE validity_period > 0 AND subscription_started_at IS NOT NULL
ORDER BY subscription_started_at DESC

# =================
#  Customer-User Connection
# =================
UPDATE box_customers 
JOIN users ON users.email = box_customers.email
SET user_id = users.id
WHERE user_id IS NULL

# =================
# invalid PLZ from database / Packstationen
# =================
SELECT * FROM box_customers WHERE (zip_code REGEXP '[0123456789]{5}') = 0
SELECT * FROM box_customers WHERE (address_appendix REGEXP '[0123456789]{7,10}') = 1

# =================
#  Emails an Box Customer
# =================
SELECT first_name, email FROM box_customers 
JOIN box_orders ON (
	customer_id = box_customers.id 
	AND subscription_started_at < "2012-10-16" 
	AND subscription_stopped_at IS NULL 
	AND subscription_started_at IS NOT NULL
	AND (expire_on IS NULL OR expire_on > NOW()))
WHERE delivery_dates IS NULL 
GROUP BY email
LIMIT 30000

# ==================
#	Copy User to Customer
# ==================
INSERT INTO box_customers (first_name, last_name, street, street_number, zip_code, city, email, user_id, gender, wants_alcohol, service_history, phone) 
(SELECT first_name, last_name, street, street_number, zip_code, city, email, id, gender, '1', 'Free Box for bloggers', phone FROM cms_users WHERE id IN (49));

# ==================
#  Copy Customer to User
# ==================
INSERT INTO cms_users (first_name, last_name, street, street_number, zip_code, city, email, gender) 
(SELECT first_name, last_name, street, street_number, zip_code, city, email, gender FROM box_customers WHERE id IN (35513));


# ==================
#	Freebox for customer
# ==================
INSERT INTO box_orders (customer_id, created_at, updated_at, payment_authorization_result, payment_method, price, subscription_started_at, edition, subscription_limit, expire_on, campaign) 
(SELECT id, NOW(), NOW(), 'AUTHORISED', 'bank transfer', 0.00, NOW(), 5, 1, '2013-10-31', 'freebox' FROM box_customers WHERE id IN (1));

# ==================
#  INSERT Additionl Products (webshop)
# ==================
INSERT INTO box_order_product_relations (box_order_id, product_id, MONTH, YEAR, ac, reason)
(SELECT box_order_id, product_id, 9, 2013, 'add', 'webshop' FROM purchases JOIN purchase_groups ON purchase_group_id = purchase_groups.id WHERE purchase_group_id IN (698, 712))

# ==================
#	Top 10 Produkte
# ==================
SELECT NAME, COUNT(*) AS summe 
FROM user_wishlist_relations 
LEFT JOIN products ON product_id = products.id
WHERE DATE_FORMAT(user_wishlist_relations.created_at, '%m%y' ) = '1012'
GROUP BY product_id ORDER BY summe DESC LIMIT 10 


# ==================
#	Running Orders nach bundesland
# ==================
SELECT region, COUNT(DISTINCT(box_customers.id)) AS summe FROM box_customers 
JOIN orders ON (
  orders.customer_id = box_customers.id AND
	orders.state = 'running' AND (orders.box_edition = 'abo' OR orders.box_edition = 'goodnoozBox'))
	
JOIN city_zip ON (zip = zip_code)
GROUP BY re_short



# ================
#	Delete Spam 
# ================
SELECT ticket_id FROM ost_ticket
#left join ost_ticket_message on ost_ticket.ticket_id = ost_ticket_message.ticket_id
WHERE SUBJECT LIKE "%Undeliverable%"
OR SUBJECT LIKE "%failed%"
OR SUBJECT LIKE "%Failure%"
OR SUBJECT LIKE "%Returned mail%"
OR SUBJECT LIKE "%Unzustellbar%"

DELETE FROM ost_ticket WHERE ticket_id IN ()

# ================
# Update Taggings
# ================
insert into taggings (tag_id,taggable_id,taggable_type) (select 16,id,'Product' from products where eye_catcher_text = "Test<br>2012")
INSERT INTO taggings (tag_id,taggable_id,taggable_type) (SELECT 16,products.id,'Product' FROM products WHERE eye_catcher_text LIKE "test%" AND YEAR(products.created_at) = 2012)


# ==============
# Abonenten
# ==============
SELECT * FROM box_payments WHERE TIMESTAMP > "2012-12-16" AND TIMESTAMP < "2013-01-16"
AND amount < 10.00 AND amount > 0.00 
AND STATUS = 'success'
LIMIT 100000

# ==============
# Kündigungen
# ==============
SELECT reason_of_cancellation, COUNT(*) AS summe FROM box_orders WHERE MONTH(subscription_stopped_at) = "02" AND YEAR(subscription_stopped_at) = "2013" AND reason_of_cancellation NOT LIKE "%Reinigung%"
GROUP BY reason_of_cancellation


# ==============
# Doppelte Bezahlungen
# ==============
SELECT COUNT(*) AS summe, customer_id FROM box_payments 
WHERE TIMESTAMP > "2013-02-16" AND STATUS = 'success'
GROUP BY customer_id
ORDER BY summe DESC

# ==============
# Paypal ohne Verknüpfung
# ==============
SELECT memo, TIMESTAMP, box_payments.transaction_id, payer_email, first_name, last_name, address_city, address_name, address_street, address_zip, mc_gross, payment_status 
FROM box_payments LEFT JOIN box_paypal_charges ON (charge_id = box_paypal_charges.id AND platform = 'paypal')
WHERE box_payments.order_id IS NULL AND TIMESTAMP > "2013-02-15"

ORDER BY TIMESTAMP DESC


# ==============
# Answeres for a Raffle
# ==============
SELECT * FROM raffle_questions 
JOIN raffle_answers ON raffle_question_id = raffle_questions.id
WHERE raffle_id = 152

# ==============
# INSERT Zusatzprodukte
# ==============
INSERT INTO box_order_product_relations (box_order_id, product_id, MONTH, YEAR, ac)
SELECT box_order_id, product_id, 11, 2013, 'webshop' FROM purchases 
JOIN purchase_groups ON purchase_group_id = purchase_groups.id
WHERE purchase_group_id IN ()


# ==============
# Facebook Gewinnspiel Auswertung
# ==============
SELECT first_name, last_name, email, street, street_number, zip_code, city, gender, birthday, wants_sweepstake_notifications FROM fb_actions JOIN cms_users ON fb_user_id = cms_users.id
WHERE ACTION = 'erdbaer'
GROUP BY cms_users.id
LIMIT 1000000


# ==============
# Add payments from invoices
# ==============
INSERT INTO box_payments (amount, order_id, customer_id, timestamp, transaction_id, platform, status) 
(SELECT 12.99, box_order_id, box_customer_id, created_at, box_order_id, 'billpay', 'success' FROM box_invoices WHERE id IN (29581,29580,29579,22925,22926));


# ==============
# Cashback Produkte
# ==============
SELECT COUNT(*), NAME, SUM(IF( kb_receipt_id IS NULL,1,0)) AS eingeloest, SUM(IF( kb_receipt_id IS NOT NULL,1,0)) AS vorgemerkt FROM kb_requests
JOIN products ON product_id = products.id
GROUP BY product_id

# ==============
# Noozie Login-Activity
# ==============
SELECT 
SUM(IF( last_login_at > "2013-11-01",1,0)) AS '1', 
SUM(IF( last_login_at > "2013-09-01",1,0)) AS '3',
SUM(IF( last_login_at > "2013-06-01",1,0)) AS '6',
SUM(IF( last_login_at > "2012-11-01",1,0)) AS '12'
FROM cms_users WHERE last_login_at IS NOT NULL

# ==============
# CZ delivery codes
# ==============
INSERT INTO box_deliveries (MONTH, YEAR, CODE, customer_id, order_id) 
(SELECT 12,2013,'dispatched 5',customer_id,id FROM box_orders WHERE id IN ());

# ==============
# Groupons-Coupons
# ==============
SELECT CODE,security_code FROM box_coupons bc
LEFT JOIN box_orders bo ON (bo.box_coupon_id = bc.id)
WHERE bc.campaign = 'groupon' AND security_code IS NOT NULL

# --- with deliv. codes
SELECT bc.CODE AS coupon_code,security_code, bd.code AS DHL_code, order_id, bo.box_edition FROM box_coupons bc
LEFT JOIN box_orders bo ON (bo.box_coupon_id = bc.id)
LEFT JOIN box_deliveries bd ON bd.order_id = bo.id
WHERE bc.campaign = 'groupon' AND security_code IS NOT NULL
AND bd.code IS NOT NULL
LIMIT 10000000

SELECT bc.CODE AS coupon_code,security_code, bd.code AS DHL_code, order_id, bo.box_edition FROM box_coupons bc
JOIN box_orders bo ON (bo.box_coupon_id = bc.id)
LEFT JOIN box_deliveries bd ON bd.order_id = bo.id
WHERE bc.campaign = 'groupon' #AND security_code IS NOT NULL
#AND bd.code IS NOT NULL
LIMIT 10000000


# ==============
# personalisierter Cashback
# ==============
INSERT INTO kb_user_products (user_id, product_id, `limit` , amount, is_percentage)
(SELECT id,3300,12,100.00,1 FROM cms_users WHERE id IN (100012,100030))


# ==============
# update mit join
# ==============
UPDATE raffle_answers a2 
JOIN raffle_answers a1 ON (a1.created_at  = a2.created_at AND a1.raffle_question_id IN (5567,5568) AND a1.user_id IS NOT NULL)
SET a2.user_id = a1.user_id
WHERE a2.user_id IS NULL AND a2.raffle_question_id IN (5567,5568)
Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

brandnooz - MySQL
