Queries for SIP9

sip9-queries.js

// Finding Lost Shards
// --------------------------------------------------------------------------------------
const result = db.storageevents.aggregate([
    {
	$match: {
	    $and: [
		{storage: {$gt: 0}}
	    ]
	}
    },
    {
    	$project: {
            _id: 0,
            shardHash: 1,
    	    farmerEnd: {
		$cond: {
		    if: {$not: ["$farmerEnd"]},
		    then: 0,
		    else: 1
		}
	    },
    	    userDeleted: {
		$cond: {
		    if: {$not: ["$userDeleted"]},
		    then: 0,
		    else: 1
		}
	    }
    	}
    }, 
    {
    	$group: {
    	    _id: "$shardHash",
    	    total: {$sum: 1},
    	    farmerEnd: {$sum: "$farmerEnd"},
    	    userDeleted: {$sum: "$userDeleted"}
    	}
    },
    {
    	$project: {
    	    _id: 0,
    	    hash: "$_id",
    	    total: 1,
    	    farmerEnd: 1,
    	    userDeleted: 1,
    	    lost: {
    		$cond: {
    		    if: {
			$and: [
			    { $eq: ["$total", "$farmerEnd"] },
			    { $eq: ["$userDeleted", 0] }
			]
    		    },
    		    then: true,
    		    else: false
    		}
    	    }
    	}
    }
])

print(JSON.stringify(result.toArray(), null, 2))


// Query Farmer Bandwidth
// --------------------------------------------------------------------------------------
const GB = 1000000000;
const FARMER_BANDWIDTH_TYPE = "bandwidth";
const startIsoDate = new Date('2017-11-06');
const endIsoDate = new Date('2018-01-06');
const creationIsoDate = new Date(endIsoDate - 1);

const result = db.storageevents.aggregate([
    {
	$match: {
	    $and: [
		{timestamp: {$gte: startIsoDate, $lt: endIsoDate}},
		{success: true},
		{downloadBandwidth: {$gt: 0}}
	    ]
	}
    },
    {
	$group: {
	    _id: "$farmer",
	    downloadBandwidthAmount: {$sum: "$downloadBandwidth"}
	}
    },
    {
	$project: {
	    _id: 0,
	    bandwidth: "$downloadBandwidthAmount",
	    farmer: "$_id",
	    type: {$literal: FARMER_BANDWIDTH_TYPE},
	    created: {$literal: creationIsoDate}
	}
    }
])

print(JSON.stringify(result.toArray(), null, 2))

// Query Farmer Mirror Bandwidth
// --------------------------------------------------------------------------------------
const GB = 1000000000;
const FARMER_MIRROR_TYPE = 'mirrorbandwidth';
const startIsoDate = new Date('2017-11-06');
const endIsoDate = new Date('2017-11-11');
const creationIsoDate = new Date(endIsoDate - 1);

const result = db.storageevents.aggregate([
    {
	$match: {
	    $and: [
		{timestamp: {$gte: startIsoDate, $lt: endIsoDate}},
		{success: true},
		{storage: {$gt: 0}},
		{user: null}
	    ]
	}
    },
    {
    	$group: {
    	    _id: "$client",
    	    mirrorBandwidthAmount: {$sum: "$storage"}
    	}
    },
    {
    	$project: {
    	    _id: 0,
    	    bandwidth: "$mirrorBandwidthAmount",
    	    farmer: "$_id",
    	    type: {$literal: FARMER_MIRROR_TYPE},
    	    created: {$literal: creationIsoDate}
    	}
    }
])

print(JSON.stringify(result.toArray(), null, 2))


// Query Farmer Storage
// --------------------------------------------------------------------------------------
const GB = 1000000000;
const MS_TO_HOUR = 1000 * 60 * 60;

const FARMER_STORAGE_TYPE = 'storage';

const startIsoDate = new Date('2017-11-06');
const endIsoDate = new Date('2018-01-06');

const creationIsoDate = new Date(endIsoDate - 1);

const gbHourPrice = 0.00002054794521;
const deletedDate = new Date('2018-01-06');

const result = db.storageevents.aggregate([
    {
	$match: {
            $and: [
		{success: true},
		{storage: {$gt: 0}}
            ]
	}
    },
    {
	$project: {
            _id: 0,
            farmer: 1,
            shardHash: 1,
            dateShardCreated: "$timestamp",
	    dateShardEnded: {$ifNull: [{$min: ["$farmerEnd", "$userDeleted"]}, {$add: [new Date(), 0]}]},
            shardSize: "$storage"
	}
    },
    {
	$project: {
            farmer: 1,
            shardHash: 1,
            shardSizeGb: {$divide: ["$shardSize", GB]},
            dateShardCreated: 1,
            dateShardEnded: 1,
            shardHoursOverlapWithPeriod: {
		$divide: [
		    {$max: [0,
			    {$add: [1,
				    {$subtract: [
					{$min: ["$dateShardEnded", endIsoDate]},
					{$max: ["$dateShardCreated", startIsoDate]}
				    ]},
				   ]},
			   ]},
		    MS_TO_HOUR]}
	}
    },
    {
	$project: {
            farmer: 1,
            shardHash: 1,
            shardGbHours: {$multiply: ["$shardSizeGb", "$shardHoursOverlapWithPeriod"]}
	}
    },
    {
	$group: {
            _id: "$farmer",
            totalGbHours: {$sum: "$shardGbHours"}
	}
    },
    {
	$project: {
            _id: 0,
            storage: "$totalGbHours",
            farmer: "$_id",
            type: {$literal: FARMER_STORAGE_TYPE},
            created: {$literal: creationIsoDate}
	}
    }
]);

var arr = result.toArray();

print(JSON.stringify(arr, null, 2))

var total = 0;
for (var i = 0; i < arr.length; i++) {
    total += arr[i].storage;
}

print(total);


// Query User Bandwidth
// --------------------------------------------------------------------------------------
const GB = 1000000000;
const BANDWIDTH_DEBIT_TYPE = 'bandwidth';
const billingPeriodStartIsoDate = new Date('2017-11-06');
const billingPeriodEndIsoDate = new Date('2018-01-06');
const debitCreationIsoDate = new Date(billingPeriodEndIsoDate - 1);
const perGbPrice = 0.05;

const result = db.storageevents.aggregate([
    {
	$match: {
	    $and: [
		{timestamp: {$gte: billingPeriodStartIsoDate, $lt: billingPeriodEndIsoDate}},
		{success: true},
		{downloadBandwidth: {$gt: 0}}
	    ]
	}
    },
    {
	$group: {
	    _id: "$user",
	    downloadBandwidthAmount: {$sum: "$downloadBandwidth"}
	}
    },
    {
	$project: {
	    _id: 0,
	    amount: {$multiply: [perGbPrice, {$divide: ["$downloadBandwidthAmount", GB]}]},
	    bandwidth: "$downloadBandwidthAmount",
	    user: "$_id",
	    type: {$literal: BANDWIDTH_DEBIT_TYPE},
	    created: {$literal: debitCreationIsoDate}
	}
    }
]);

print(JSON.stringify(result.toArray(), null, 2))



// Query User Storage
// --------------------------------------------------------------------------------------
const GB = 1000000000;
const MS_TO_HOUR = 1000 * 60 * 60;
const STORAGE_DEBIT_TYPE = 'storage';
const BANDWIDTH_DEBIT_TYPE = 'bandwidth';
const billingPeriodStartIsoDate = new Date('2017-11-06');
const billingPeriodEndIsoDate = new Date('2018-01-06');
const debitCreationIsoDate = new Date(billingPeriodEndIsoDate - 1);
const gbHourPrice = 0.00002054794521;
//const deletedDate = new Date();
const deletedDate = new Date('2018-01-06');

const result = db.storageevents.aggregate([
    {
      $match: {
        $and: [
          {success: true},
          {storage: {$gt: 0}},
          {user: {$ne: null}}
        ]
      }
    },
    {
      $project: {
        _id: 0,
        user: 1,
        shardHash: 1,
        dateShardCreated: "$timestamp",
	dateShardDeleted: {$ifNull: [{$min: ["$farmerEnd", "$userDeleted"]}, {$add: [new Date(), 0]}]},
        shardSize: "$storage"
      }
    },
    {
      $project: {
        user: 1,
        shardHash: 1,
        shardSizeGb: {$divide: ["$shardSize", GB]},
        dateShardCreated: 1,
        dateShardDeleted: 1,
        shardHoursOverlapWithBillingPeriod: {
          $divide: [
            {$max: [0,
              {$add: [1,
                {$subtract: [
                  {$min: ["$dateShardDeleted", billingPeriodEndIsoDate]},
                  {$max: ["$dateShardCreated", billingPeriodStartIsoDate]}
                ]},
              ]},
            ]},
            MS_TO_HOUR]}
      }
    },
    {
      $project: {
        user: 1,
        shardHash: 1,
        shardGbHoursUsed: {$multiply: ["$shardSizeGb", "$shardHoursOverlapWithBillingPeriod"]},
        shardBillableAmount: {$multiply: ["$shardSizeGb", "$shardHoursOverlapWithBillingPeriod", gbHourPrice]}
      }
    },
    {
      $group: {
        _id: "$user",
        totalBillableAmount: {$sum: "$shardBillableAmount"},
        totalGbHoursUsed: {$sum: "$shardGbHoursUsed"}
      }
    },
    {
       $project: {
        _id: 0,
        amount: "$totalBillableAmount",
        storage: "$totalGbHoursUsed",
        user: "$_id",
        type: {$literal: STORAGE_DEBIT_TYPE},
        created: {$literal: debitCreationIsoDate}
      }
    }
]);

print(JSON.stringify(result.toArray(), null, 2))