SB-30.rb

# vps-api

ANDROID_CLIENT_ID = 4
IOS_CLIENT_ID = 5
TELEKOM_CAMPAIGN_APRIL_2016_CAMPAIGN_ID = 4

################################################################################
# orders from April 18 to May 10 2016:
################################################################################
os = Order.where( :state => :printed.to_s, :created_at => Date.parse('2016-04-18') .. Date.parse('2016-05-11') )
oids = os.map( &:id )
first_buyers = os.select{ | w | w.id == w.user.orders.where( :state => :printed.to_s ).first.id if w.user } # first buyers
returning = os.select{ | w | w.id != w.user.orders.where( :state => :printed.to_s ).first.id if w.user } # first buyers
first_buyers.map( &:user_id ).uniq.count # how many first buyers?
returning.map( &:user_id ).uniq.count # how many returning users (users who already ordered at least 1 book before)?
( os.map( &:amount_eu ).sum / os.count ).to_f.round( 2 ) # average shopping cart value from all orders
os.where( :gift_package => true, :voucher_id => [150613, 160624, 160625, 160626, 160629, 160630, 160631, 160633] ).count # how many orders with gift wrapping added + 2,50€ discount
os.where( :gift_package => false, :voucher_id => [150613, 160624, 160625, 160626, 160629, 160630, 160631, 160633] ).count # how many orders with gift wrapping NOT added + 2,50€ discount
os.where( :client_id => ANDROID_CLIENT_ID, :voucher_id => [150613, 160624, 160625, 160626, 160629, 160630, 160631, 160633] ).count # how many android orders with 2,50 € discount?

################################################################################
# orders with voucher codes: 
# Muttertag2016, 
# Muttertag16, 
# Mama16, 
# Mom,
# Mum,
# sara,
# maria,
# indra
################################################################################
res = []
gs = Voucher.where :code => ['Muttertag2016',
'Muttertag16',
'Mama16',
'Mom',
'Mum',
'sara',
'maria',
'indra']
res << gs.map{ | g | [ 
  g.id,
  g.code,
  Order.where( :state => :printed.to_s, :voucher_id => g.id ).map( &:order_items ).map{ | is | is.map( &:quantity ).sum }.sum, # number of orderd books with each voucher
  Order.where( :state => :printed.to_s, :voucher_id => g.id ).select{ | w | w.id == w.user.orders.where( :state => :printed.to_s ).first.id if w.user }.map( &:user_id ).uniq.count, # how many first buyers with each voucher?
  Order.where( :state => :printed.to_s, :voucher_id => g.id ).select{ | w | w.id != w.user.orders.where( :state => :printed.to_s ).first.id if w.user }.map( &:user_id ).uniq.count, # how many returning users with each voucher (users who already ordered at least 1 book before)?
  ( Order.where( :state => :printed.to_s, :voucher_id => g.id ).map( &:amount_eu ).sum / ( Order.where( :state => :printed.to_s, :voucher_id => g.id ).count || 1 ) ).to_f.round( 2 ) # average shopping cart value for all orders per each voucher
  ] }
puts res

################################################################################
# New Telekom campaign (10,50 € discount, valid from 20.6.-26.6.):
################################################################################

Campaign.find( TELEKOM_CAMPAIGN_APRIL_2016_CAMPAIGN_ID ).used_counter # how many total orders with campaign?
os_ios = Order.where( :state => :printed.to_s, :campaign_id => TELEKOM_CAMPAIGN_APRIL_2016_CAMPAIGN_ID, :client_id => IOS_CLIENT_ID )
os_android = Order.where( :state => :printed.to_s, :campaign_id => TELEKOM_CAMPAIGN_APRIL_2016_CAMPAIGN_ID, :client_id => ANDROID_CLIENT_ID )
os_ios.count # how many iOS orders with campaign?
os_android.count # how many Android orders with campaign?
os_ios_1 = os_ios.select{ | w | w.id == w.user.orders.where( :state => :printed.to_s ).first.id if w.user }.map( &:user_id ).uniq.count # how many first users (for iOS)?
os_android_1 = os_android.select{ | w | w.id == w.user.orders.where( :state => :printed.to_s ).first.id if w.user }.map( &:user_id ).uniq.count # how many first users (for Android)?
os_ios_2 = os_ios.select{ | w | w.id != w.user.orders.where( :state => :printed.to_s ).first.id if w.user }.map( &:user_id ).uniq.count # how many returning users/users who already ordered before (for iOS)?
os_android_2 = os_android.select{ | w | w.id != w.user.orders.where( :state => :printed.to_s ).first.id if w.user }.map( &:user_id ).uniq.count # how many returning users/users who already ordered before (for Android)?
Order.where( :state => :printed.to_s, :campaign_id => TELEKOM_CAMPAIGN_APRIL_2016_CAMPAIGN_ID ).select{ | w | w.order_items.map( &:quantity ).sum > 1 }.map( &:user_id ).uniq.count # how many users ordered more than 1 book with campaign? - Anm: Angenommen, das heißt bei der gleichen Bestellung, denn sond deckt sich der Punkt mit dem folgenden Punkt ab. 
Order.where( :state => :printed.to_s, :campaign_id => TELEKOM_CAMPAIGN_APRIL_2016_CAMPAIGN_ID ).group( :user_id ).having( "count_all > 1" ).count # how many users ordered more than once with campaign?
Order.where( :state => :printed.to_s, :campaign_id => TELEKOM_CAMPAIGN_APRIL_2016_CAMPAIGN_ID ).map( &:order_items ).map{ | is | is.map( &:quantity ).sum }.sum # how many books were orderd with campaign in total?
( Order.where( :state => :printed.to_s, :campaign_id => TELEKOM_CAMPAIGN_APRIL_2016_CAMPAIGN_ID ).map( &:amount_eu ).sum / Campaign.find( TELEKOM_CAMPAIGN_APRIL_2016_CAMPAIGN_ID ).used_counter ).to_f.round( 2 ) # average shopping cart value of all orders with campaign?