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; }