export_provider.rb

  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