Last active
April 8, 2016 23:05
-
-
Save joeljackson/1ed21a76c79e0f119188baf74bc6defd to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
def self.create_csv | |
db = Rails.application.config.database_configuration[Rails.env] | |
db = db['reporting'] if Rails.env == 'production' | |
username = db["username"] | |
host = db["host"] | |
database = db["database"] | |
password = db["password"] | |
port = db["port"] | |
all_active_newsletters = Newsletter.where('display = 1 AND exacttarget_pref_name IS NOT NULL') | |
newsletter_queries = "" | |
if all_active_newsletters.present? | |
all_active_newsletters.each do |letter| | |
if letter.has_options | |
# The types of newsletters that have options in user's subscriptions: 'All', 'Weekly', 'Monthly', whatever free text, etc. as long as it is not NULL. | |
newsletter_queries << "(SELECT newsletter_subscriptions.options | |
FROM newsletter_subscriptions | |
LEFT JOIN newsletters ON newsletter_subscriptions.newsletter_id = newsletters.id | |
WHERE newsletter_subscriptions.user_id = users.id AND newsletters.exacttarget_pref_name = '#{letter.exacttarget_pref_name}') AS '#{letter.exacttarget_pref_name}'," | |
else | |
# The types of newsletters that do not have options in user's subscriptions; a user either subscribes or does not subscribe. | |
newsletter_queries << "(SELECT (case when COUNT(newsletter_subscriptions.id) > 0 then 'True' else 'False' end) | |
FROM newsletter_subscriptions | |
LEFT JOIN newsletters ON newsletter_subscriptions.newsletter_id = newsletters.id | |
WHERE newsletter_subscriptions.user_id = users.id AND newsletters.exacttarget_pref_name = '#{letter.exacttarget_pref_name}') AS '#{letter.exacttarget_pref_name}'," | |
end | |
end | |
end | |
(0..85).each do |loop_number| | |
subscriber_postfix = ExactTargetSubscriber.postfix | |
sql_query = " | |
SELECT SUBSTRING(TRIM(REPLACE(users.first_name,',',' ')) FROM 1 FOR 50) AS 'FirstName', | |
SUBSTRING(TRIM(REPLACE(users.last_name,',',' ')) FROM 1 FOR 50) AS 'LastName', | |
users.id AS UserId, | |
users.created_at AS 'SignUpDate', | |
users.cancelled_membership_at AS 'MembershipCancelledDate'," + | |
newsletter_queries + | |
" | |
(SELECT concat(exact_target_subscribers.id, '_#{subscriber_postfix}') | |
FROM exact_target_subscribers | |
WHERE subscriber_id=users.id AND subscriber_type='User') as SubscriberKey, | |
(SELECT (case when COUNT(fraud_user_accounts.id) > 0 then 'True' else 'False' end) | |
FROM fraud_user_accounts | |
WHERE fraud_user_accounts.user_id=users.id AND fraud_user_accounts.state='confirmed') as Fraudulent, | |
(SELECT count(orders.id) | |
FROM orders | |
WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') | |
AND orders.user_id=users.id) as NumberOfOrders, | |
(SELECT count(orders.id) | |
FROM orders | |
WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id) AS NumberOfSubscriptions, | |
(SELECT (case when MIN(orders.user_selected_ship_date) is not null then 'True' else 'False' end) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0001') AS 'HasDiapersSubscription', | |
(SELECT MIN(orders.user_selected_ship_date) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0001') AS 'NextDiaperShipment', | |
(SELECT (case when MIN(orders.user_selected_ship_date) is not null then 'True' else 'False' end) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0002') AS 'HasEssentialsSubscription', | |
(SELECT MIN(orders.user_selected_ship_date) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0002') AS 'NextEssentialsShipment', | |
(SELECT (case when MIN(orders.user_selected_ship_date) is not null then 'True' else 'False' end) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0003') AS 'HasHWSubscription', | |
(SELECT MIN(orders.user_selected_ship_date) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0003') AS 'NextHWShipment', | |
(SELECT (case when MIN(orders.user_selected_ship_date) is not null then 'True' else 'False' end) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0004') AS 'HasFeedingSubscription', | |
(SELECT MIN(orders.user_selected_ship_date) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0004') AS 'NextFeedingShipment', | |
(SELECT (case when COUNT(orders.id) > 0 then 'True' else 'False' end) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.order_classification = 2 | |
AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') | |
AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=19) AS 'DiapersTrial', | |
(SELECT local_date(max(order_state_transitions.created_at)) | |
FROM orders | |
JOIN order_state_transitions on (orders.id=order_state_transitions.order_id and order_state_transitions.event='start_vanity') | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.order_classification = 2 | |
AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') | |
AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=19 | |
GROUP BY orders.user_id) AS 'DiapersTrialDate', | |
(SELECT (case when COUNT(orders.id) > 0 then 'True' else 'False' end) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.order_classification = 2 | |
AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') | |
AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=16) AS 'EssentialsTrial', | |
(SELECT local_date(max(order_state_transitions.created_at)) | |
FROM orders | |
JOIN order_state_transitions on (orders.id=order_state_transitions.order_id and order_state_transitions.event='start_vanity') | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.order_classification = 2 | |
AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') | |
AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=16 | |
GROUP BY orders.user_id) AS 'EssentialsTrialDate', | |
(SELECT (case when COUNT(orders.id) > 0 then 'True' else 'False' end) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.order_classification = 2 | |
AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') | |
AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=84) AS 'HWTrial', | |
(SELECT local_date(max(order_state_transitions.created_at)) | |
FROM orders | |
JOIN order_state_transitions on (orders.id=order_state_transitions.order_id and order_state_transitions.event='start_vanity') | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.order_classification = 2 | |
AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') | |
AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=84 | |
GROUP BY orders.user_id) AS 'HWTrialDate', | |
(SELECT (case when COUNT(orders.id) > 0 then 'True' else 'False' end) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.order_classification = 2 | |
AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') | |
AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=206) AS 'FeedingTrial', | |
(SELECT local_date(max(order_state_transitions.created_at)) | |
FROM orders | |
JOIN order_state_transitions on (orders.id=order_state_transitions.order_id and order_state_transitions.event='start_vanity') | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.order_classification = 2 | |
AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') | |
AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=206 | |
GROUP BY orders.user_id) AS 'FeedingTrialDate', | |
(SELECT count(orders.id) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0001') AS 'DiapersSubscriptionOrders', | |
(SELECT count(orders.id) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0002') AS 'EssentialsSubscriptionOrders', | |
(SELECT count(orders.id) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0003') AS 'HWSubscriptionOrders', | |
(SELECT count(orders.id) | |
FROM orders | |
LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id | |
LEFT OUTER JOIN variants ON line_items.variant_id=variants.id | |
WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0004') AS 'FeedingSubscriptionOrders', | |
(SELECT (case when user_trans.subscription like '%Diaper%' then null else max(end_date) end) | |
from subscriptions_plus_minus | |
join user_trans on (subscriptions_plus_minus.user_id=user_trans.user_id | |
and product_type='Diaper Bundle') | |
where user_trans.user_id=users.id | |
) 'DiaperCancelDate', | |
(SELECT (case when user_trans.subscription like '%Essentials%' then null else max(end_date) end) | |
from subscriptions_plus_minus | |
join user_trans on (subscriptions_plus_minus.user_id=user_trans.user_id | |
and product_type='Essentials Bundle') | |
where user_trans.user_id=users.id | |
) 'EssentialsCancelDate', | |
(SELECT (case when user_trans.subscription like '%H&W%' then null else max(end_date) end) | |
from subscriptions_plus_minus | |
join user_trans on (subscriptions_plus_minus.user_id=user_trans.user_id | |
and product_type='H&W Bundle') | |
where user_trans.user_id=users.id | |
) 'HWCancelDate', | |
(SELECT (case when user_trans.subscription like '%Feeding%' then null else max(end_date) end) | |
from subscriptions_plus_minus | |
join user_trans on (subscriptions_plus_minus.user_id=user_trans.user_id | |
and product_type='Feeding Bundle') | |
where user_trans.user_id=users.id | |
) 'FeedingCancelDate', | |
(SELECT MAX(date_sub(orders.created_at,interval 8 hour)) | |
FROM orders | |
WHERE orders.order_classification IN (5,9) and orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.user_id=users.id) AS 'LastStorePurchase', | |
(SELECT count(orders.id) | |
FROM orders | |
WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (5,9) AND orders.user_id=users.id) AS 'ShopOrders', | |
(SELECT count(orders.id) | |
FROM orders | |
WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (6, 14, 15) AND orders.user_id=users.id) AS 'GiftOrders', | |
(select count(distinct id) from test.order_product dr1 | |
where dr1.user_id=users.id and product_id =1 | |
group by bundle_id) 'PurchasedDiaperProducts', | |
(select max(order_date) from test.order_product | |
dr1 where dr1.user_id=users.id and product_id =1 | |
group by user_id) 'LastDatePurchasedDiaperProducts', | |
(select count(distinct id) from test.order_product | |
where test.order_product.user_id=users.id and bundle_id=21) 'PurchasedEssentialsProducts', | |
(select max(order_date) from test.order_product | |
dr1 where dr1.user_id=users.id and bundle_id=21 | |
group by user_id) 'LastDatePurchasedEssentialsProducts', | |
(select count(distinct id) from test.order_product | |
where test.order_product.user_id=users.id and bundle_id=73) 'PurchasedHWProducts', | |
(select max(order_date) from test.order_product | |
dr1 where dr1.user_id=users.id and bundle_id=73 | |
group by user_id) 'LastDatePurchasedHWProducts', | |
(select count(distinct id) from test.order_product | |
where test.order_product.user_id=users.id and bundle_id=201) 'PurchasedFeedingProducts', | |
(select max(order_date) from test.order_product | |
dr1 where dr1.user_id=users.id and bundle_id=201 | |
group by user_id) 'LastDatePurchasedFeedingProducts', | |
(SELECT count(*) FROM line_items join test.max_shop_order dr1 on (line_items.itemizable_id=dr1.id) | |
join variants on (line_items.variant_id=variants.id and product_id=1) | |
left join bundle_add_ons on (variants.id=bundle_add_ons.variant_id) where dr1.user_id=users.id | |
) as 'LastShopOrderDiaperProduct', | |
(SELECT count(*) FROM line_items join test.max_shop_order dr1 on (line_items.itemizable_id=dr1.id) | |
join variants on (line_items.variant_id=variants.id) | |
join bundle_add_ons on (variants.id=bundle_add_ons.variant_id and bundle_id=21) where dr1.user_id=users.id | |
) as 'LastShopOrderEssentialsProduct', | |
(SELECT count(*) FROM line_items join test.max_shop_order dr1 on (line_items.itemizable_id=dr1.id) | |
join variants on (line_items.variant_id=variants.id) | |
join bundle_add_ons on (variants.id=bundle_add_ons.variant_id and bundle_id=73) where dr1.user_id=users.id | |
) as 'LastShopOrderHWProduct', | |
(SELECT count(*) FROM line_items join test.max_shop_order dr1 on (line_items.itemizable_id=dr1.id) | |
join variants on (line_items.variant_id=variants.id) | |
join bundle_add_ons on (variants.id=bundle_add_ons.variant_id and bundle_id=201) where dr1.user_id=users.id | |
) as 'LastShopOrderFeedingProduct', | |
(SELECT max(date_sub(orders.updated_at,interval 8 hour)) | |
FROM orders | |
WHERE orders.state IN ('cart') AND DATE(orders.updated_at + INTERVAL 2 DAY) < NOW() AND orders.user_id=users.id) AS 'LastCartAbandonDate', | |
(SELECT referrals.m_id FROM referrals WHERE referrals.user_id=users.id and referrals.campaign IS NULL) AS 'MID', | |
(SELECT referrals.a_id FROM referrals WHERE referrals.user_id=users.id and referrals.campaign IS NULL) AS 'AID', | |
date_sub(users.current_sign_in_at,interval 8 hour) 'LastSignIn', | |
users.email AS 'EmailAddress', | |
(CASE WHEN EXISTS(SELECT NULL FROM user_tracked_fields | |
WHERE user_tracked_fields.app_platform='ios' | |
AND user_tracked_fields.business_unit_id = #{BusinessUnit.honest.id} | |
AND user_tracked_fields.user_id = users.id) | |
THEN 'True' ELSE 'False' END) AS 'HonestApp'," + | |
# we don't yet have LastAppUsedDate data so this is just a placeholder column | |
"(SELECT NULL) AS 'LastAppUsedDate' | |
FROM users WHERE user_id >= #{loop_number * 100000} AND user_id < #{(loop_number + 1) * 100000};" | |
mysql_login = "mysql -u #{username} -h #{host} -P #{port} --password=#{password} #{database}" | |
temp_file = "/tmp/exacttarget_#{Rails.env}.txt" | |
csv_file_name = "/tmp/initial_export_#{loop_number}.csv" | |
#%x[echo "call get_users_changed()" | #{mysql_login}] | |
%x[echo "#{sql_query}" | #{mysql_login} > #{temp_file}] | |
%x[sed -e 's/"/''''/g' -e 's/\t/","/g;s/^/"/;s/$/"/' -e 's/"NULL"//g' #{temp_file} > #{csv_file_name}] | |
end | |
csv_file_name | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment