from pymongo import MongoClient
# pprint library is used to make the output look more pretty
from pprint import pprint

mongodb_url = "mongodb://localhost:27017"

# connect to MongoDB
client = MongoClient(mongodb_url)
db=client.Northwind
# Issue the serverStatus command and print the results
serverStatusResult=db.command("serverStatus")
#pprint(serverStatusResult)

top_ten_most_orders = db['order-details'].aggregate([

    {
    # lookup is used to outer join the order_details with the orders table
    "$lookup":
        {
          "from": "orders",
          "localField": "OrderID",
          "foreignField": "OrderID",
          "as": "orders_info"
        }
    },
    # Group records together by OrderId
    # and when grouping increment a orderID counter by one for each record that is combined
    # and calculate a cumulative sum for each record that is combined, save the result as "Total_Quantity"
    { "$group" : {"_id":"$orders_info.CustomerID", "OrderID_counter":{"$sum":1}, "Total_Quantity": {"$sum":"$Quantity"}}},
    # sort the result in descending order by orderID_counter
    { "$sort": { "OrderID_counter": -1} },
    # Finally limit the number of documents to 10
    { "$limit": 10 }
])

# Print the result in a nice way
print "Results for exercise 4.3 (Make your own) MongoDB"
print
print "{CustomerID: <12} {TotalOrders: <14} {TotalQty: <14}".format(
    CustomerID = "Customer Id",TotalOrders = "Total orders", TotalQty = "Total quantity")
print "------------------------------------------"
for order in top_ten_most_orders:
    print "{CustomerID: <12} {TotalOrders: <14} {TotalQty: <14}".format(
        CustomerID = order["_id"][0], TotalOrders = order["OrderID_counter"], TotalQty = order["Total_Quantity"])

print
print "Total number of lines: {}".format(len(data))