"FollowedUserID",
"been_here"=>"EstID",
"checkin"=>"EstID",
"follower_added"=>"FollowerUserID",
"want_to_try"=>"EstID",
"photo_upload"=>"EstID",
"reviews"=>"EstID"
);
$targetNames = array(
"became_follower"=>"Following",
"been_here"=>"Establishments",
"checkin"=>"Establishments",
"dislike_dish"=>"Dishes",
"dislike_review"=>"Reviews",
"dislike_tip"=>"Tips",
"follower_added"=>"Followers",
"like_dish"=>"Dishes",
"like_photo"=>"Photos",
"like_review"=>"Reviews",
"like_tip"=>"Tips",
"photo_upload"=>"Establishments",
"reviews"=>"Establishments",
"want_to_try"=>"Establishments"
);
global $fromDate,$toDate,$activities,$dayWiseActivities,$targetNames,$totalActivities;
$fromDate = isset($_REQUEST["fromDate"])?$_REQUEST["fromDate"]:"2015-01-01";
$toDate = isset($_REQUEST["toDate"])?$_REQUEST["toDate"]:date("Y-m-d"); ;
$dbh=new PDO('mysql:host=172.18.8.111;dbname=burrp_sw;charset=utf8', 'burrp_rw', 'Js2g0DwhlqUoIWm', array( PDO::ATTR_PERSISTENT => false));
$stmtActivities = $dbh->prepare("SELECT DISTINCT Activity FROM TempFeedActivityList order by Activity");
$stmtActivities->execute();
$activities = $stmtActivities->fetchAll();
$query = "SELECT ";
foreach($activities as $activityRow) {
if(!isset($targetNames[$activityRow["Activity"]]))
$targetNames[$activityRow["Activity"]] = "Targets";
$activity = str_replace("reviews", $activityRow["Activity"], " SUM(T.Activity = 'reviews') reviews_activity, count(distinct case when T.Activity = 'reviews' THEN T.UserID ELSE NULL END) reviews_users, count(distinct case when T.Activity = 'reviews' THEN NULLIF(T.ContentID, '') ELSE NULL END) reviews_contents,");
if(isset($contentIDs[$activityRow["Activity"]]))
$activity = str_replace("ContentID", $contentIDs[$activityRow["Activity"]], $activity);
$query .= $activity;
}
$query .= " 1 AS Filler FROM TempFeedActivityList T WHERE T.InsertDate >= '".$fromDate."' AND T.InsertDate < DATE_ADD('".$toDate."', INTERVAL 1 DAY)";
$stmtTotalActivities =$dbh->prepare($query);
$stmtTotalActivities->execute();
$totalActivities = $stmtTotalActivities->fetchAll();
$totalActivities = $totalActivities[0];
$query = "SELECT DATE_FORMAT(DATE(InsertDate), '%a, %d-%b-%Y') `Date`, ";
foreach($activities as $activityRow) {
$activity = str_replace("reviews", $activityRow["Activity"], " SUM(T.Activity = 'reviews') reviews_activity, count(distinct case when T.Activity = 'reviews' THEN T.UserID ELSE NULL END) reviews_users, count(distinct case when T.Activity = 'reviews' THEN NULLIF(T.ContentID, '') ELSE NULL END) reviews_contents,");
if(isset($contentIDs[$activityRow["Activity"]]))
$activity = str_replace("ContentID", $contentIDs[$activityRow["Activity"]], $activity);
$query .= $activity;
}
$query .= " 1 AS Filler FROM TempFeedActivityList T WHERE T.InsertDate >= '".$fromDate."' AND T.InsertDate < DATE_ADD('".$toDate."', INTERVAL 1 DAY) GROUP BY DATE(InsertDate) ORDER BY DATE(InsertDate) DESC";
$stmtDayWiseActivities =$dbh->prepare($query);
$stmtDayWiseActivities->execute();
$dayWiseActivities = $stmtDayWiseActivities->fetchAll();
}
?>
Activity Details
Activity Details
| Date |
|
| Activities |
Users |
|
| Total |
|
|
|
|
|
|
|
| Total |
|
|
|