public static function getLastAddedListingByUserSID($userSID)
{
$activeListings = SJB_ListingDBManager::getActiveAndApproveListingsNumberByUserSID($userSID);
$where = '';
if ($activeListings) {
$where = " AND `active`=1 ";
}
$sid = SJB_DB::queryValue("SELECT `sid` FROM `listings` WHERE `user_sid` = ?n {$where} ORDER BY `activation_date` DESC LIMIT 1", $userSID);
if (!empty($sid)) {
return self::getObjectBySID($sid);
}
return false;
}
/**
* Returns quick statistics for employer: current live jobs, jobs posted this month,
* job views this month, applications received this month
* @param $userSID
* @return array
*/
public static function getEmployerQuickStatistics($userSID)
{
$where = ' AND `s`.`date` >= FROM_DAYS(TO_DAYS(CURDATE()) - DAYOFMONTH(CURDATE()) + 1)';
$subQuery = "SELECT `l`.`sid` FROM `listings` `l` WHERE `l`.`user_sid` = ?n\n\t\t\t\t\t UNION ALL\n\t\t\t\t\t SELECT `st`.`object_sid` FROM `statistics` `st` WHERE `st`.`user_sid` = ?n AND `st`.`event` = 'deleteListing'";
$quickStats = SJB_DB::query("\n\t\t\tSELECT IFNULL(SUM(`quickStats`.`countPostedListings`),0) AS `countPostedListings`,\n\t\t\tIFNULL(SUM(`quickStats`.`countViewedListings`),0) AS `countViewedListings`, IFNULL(SUM(`quickStats`.`countApplications`),0) AS `countApplications`\n\t\t\tFROM (\n\t\t\t\tSELECT SUM(`s`.`count`) AS `countPostedListings`, 0 AS `countViewedListings`, 0 AS `countApplications`\n\t\t\t\tFROM `statistics` `s`\n\t\t\t\tWHERE `s`.`user_sid` = ?n AND `s`.`event` = 'addListing' AND `s`.`type`= 6 {$where} GROUP BY `s`.`user_sid`\n\t\t\t\tUNION ALL\n\t\t\t\tSELECT 0 AS `countPostedListings`, SUM(`s`.`count`) AS `countViewedListings`, 0 AS `countApplications`\n\t\t\t\tFROM `statistics` `s`\n\t\t\t\tWHERE `s`.`event` = 'viewListing' AND `s`.`type`= 6 {$where} AND `s`.`object_sid` IN ( {$subQuery})\n\t\t\t\tGROUP BY `s`.`user_sid`\n\t\t\t\tUNION ALL\n\t\t\t\tSELECT 0 AS `countPostedListings`, 0 AS `countViewedListings`, SUM(`s`.`count`) AS `countApplications`\n\t\t\t\tFROM `statistics` `s`\n\t\t\t\tWHERE `s`.`event` = 'apply' {$where} AND `s`.`type` IN ({$subQuery})\n\t\t\t\tGROUP BY `s`.`user_sid`\n\t\t\t) AS quickStats", $userSID, $userSID, $userSID, $userSID, $userSID);
$quickStats = array_pop($quickStats);
$quickStats['countActiveListings'] = SJB_ListingDBManager::getActiveAndApproveListingsNumberByUserSID($userSID);
return $quickStats;
}
