Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement