web/scripts/recompute-ebook-downloads

#!/usr/bin/php
<?
require_once('/standardebooks.org/web/lib/Core.php');

$downloadDates = Db::Query('
	SELECT distinct date(Created) as DownloadDate
	from EbookDownloads
');

foreach($downloadDates as $date){
	$downloadDate = $date->DownloadDate;
	$summaries = [];

	$ebookDownloads = EbookDownload::GetAllByDate($downloadDate);
	foreach($ebookDownloads as $ebookDownload){
		$ebookId = $ebookDownload->EbookId;

		if(!isset($summaries[$ebookId])){
			$summaries[$ebookId] = new EbookDownloadSummary($ebookId, $downloadDate);
		}

		$summaries[$ebookId]->DownloadCount++;

		if($ebookDownload->IsBot()){
			$summaries[$ebookId]->BotDownloadCount++;
		}
	}

	foreach($summaries as $summary){
		$summary->Create();
	}
}

Db::Query('
	UPDATE Ebooks e
	left join (
		select EbookId, sum(DownloadCount) - sum(BotDownloadCount) as DownloadsPast30Days
		from EbookDownloadSummaries
		where Date >= date(utc_timestamp()) - interval 30 day
		group by EbookId
	) s on e.EbookId = s.EbookId
	set e.DownloadsPast30Days = coalesce(s.DownloadsPast30Days, 0)
');

Db::Query('
	UPDATE Ebooks e
	left join (
		select EbookId, sum(DownloadCount) - sum(BotDownloadCount) as DownloadsTotal
		from EbookDownloadSummaries
		group by EbookId
	) s on e.EbookId = s.EbookId
	set e.DownloadsTotal = coalesce(s.DownloadsTotal, 0)
');

Db::Query('
	DELETE from EbookDownloads
	where Created < utc_timestamp() - interval 60 day
');