<?php /** * SeekQuarry/Yioop -- * Open Source Pure PHP Search Engine, Crawler, and Indexer * * Copyright (C) 2009 - 2015 Chris Pollett chris@pollett.org * * LICENSE: * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. * * END LICENSE * * @author Pushkar Umaranikar * @license http://www.gnu.org/licenses/ GPL3 * @link http://www.seekquarry.com/ * @copyright 2009 - 2015 * @filesource */ if (!defined('BASE_DIR')) {echo "BAD REQUEST"; exit();} /** Loads the base class */ require_once BASE_DIR."/models/model.php"; /** * This class is used to handle * database statements related to Advertisements * * @author Pushkar Umaranikar * @package seek_quarry\model */ class AdvertisementModel extends Model { /** * Associations of the form * name of field for web forms => database column names/abbreviations * In this case, things will in general map to the ADVERTISEMENTS table * in the Yioop data base * var array */ var $search_table_column_map = array("name"=>"AD_NAME", "description" => "AD_DESCRIPTION", "destination" => "AD_DESTINATION", "keywords"=>"AD_KEYWORDS","status"=>"STATUS", "budget" => "AD_BUDGET"); /** * These fields if present in $search_array (used by @see getRows() ), * but with value "-1", will be skipped as part of the where clause * but will be used for order by clause * @var array */ var $any_fields = array("status"); /** * {@inheritDoc} * * @param mixed $args any additional arguments which should be used to * determine these tables (in this case none) */ function selectCallback($args = NULL) { return "AD_NAME, AD_DESTINATION, AD_KEYWORDS, STATUS, AD_BUDGET"; } /** * {@inheritDoc} * * @param mixed $args any additional arguments which should be used to * determine these tables (in this case none) */ function fromCallback($args = NULL) { return "(SELECT DISTINCT AD_NAME, USER_ID, AD_DESCRIPTION, AD_DESTINATION, AD_KEYWORDS, STATUS, AD_BUDGET FROM ADVERTISEMENT)"; } /** * {@inheritDoc} * * @param mixed $args any additional arguments which should be used to * determine these tables (in this case none) */ function whereCallback($args = NULL) { return "USER_ID != '".PUBLIC_USER_ID."'"; } function postQueryCallback($rows) { return $rows = $this->getAdvertisements($rows); return $rows; } /** * Adds newly created advertisement * * @param $advertisement_advertisement to be added * @param String $ad_start_date start date for an advertisement * @param String $user_id user id of user who created advertisement * @param boolean is_new represents advertisement exists or not */ function addAdvertisement($advertisement, $ad_start_date, $user_id, $is_new=true) { $db = $this->db; $ad_status = ADVERTISEMENT_ACTIVE_STATUS; if ($is_new) { $sql = "INSERT INTO ADVERTISEMENT(USER_ID, AD_NAME, AD_DESCRIPTION,AD_DESTINATION, AD_KEYWORDS, STATUS, AD_BUDGET, AD_DATE) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; $result = $db->execute($sql, array($user_id, $advertisement['AD_NAME'], $advertisement['AD_DESCRIPTION'], $advertisement['AD_DESTINATION'], $advertisement['AD_KEYWORDS'], $ad_status, $advertisement['AD_BUDGET'], $ad_start_date)); } else { $sql = "INSERT INTO ADVERTISEMENT(AD_ID, USER_ID, AD_NAME, AD_DESCRIPTION, AD_DESTINATION, AD_KEYWORDS, STATUS, AD_BUDGET, AD_DATE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; $result = $db->execute($sql, array($advertisement['AD_ID'], $user_id, $advertisement['AD_NAME'], $advertisement['AD_DESCRIPTION'], $advertisement['AD_DESTINATION'], $advertisement['AD_KEYWORDS'], $ad_status, $advertisement['AD_BUDGET'], $ad_start_date)); } } /** * Deletes advertisement * * @param string $ad_id the id of the advertisement */ function deleteAdvertisement($ad_ids) { $sql = "DELETE FROM ADVERTISEMENT WHERE AD_ID IN($ad_ids)"; $result = $this->db->execute($sql); } /** * Update advertisement * * @param object $advertisement_advertisement to be updated * @param string $ad_ids comma separated list of advertisement ids */ function updateAdvertisement($advertisement,$ad_ids) { $sql = "UPDATE ADVERTISEMENT SET "; $comma =""; $params = array(); if ($advertisement == array()) {return; } foreach ($advertisement as $field => $value) { $sql .= "$comma $field=? "; $comma = ","; $params[] = $value; } $sql .= " WHERE AD_ID IN($ad_ids)"; $db = $this->db; $result = $db->execute($sql, $params); } /** * Get advertisements for given advertisement keywords * * @param string $query_chunks words from user entered query * @return array of advertisements */ function getAdvertisementByKeywords($query_chunks) { $deactivated_status = ADVERTISEMENT_DEACTIVE_STATUS; $sql = "SELECT * FROM ADVERTISEMENT WHERE ( AD_KEYWORDS LIKE "; $append_sql = " OR AD_KEYWORDS LIKE "; $counter = 1; $db = $this->db; foreach ($query_chunks as $temp_query) { if ($counter >1) { $append_sql = $append_sql. "'%".$temp_query."%'"; $sql = $sql.$append_sql; $counter++; } else { $sql = $sql." '%".$temp_query."%'"; $counter++; } } $today_date = date(AD_DATE_FORMAT); $sql = $sql . ")" . " AND AD_DATE ="."'".$today_date."'"; $sql = $sql . " AND STATUS != $deactivated_status"; $result = $db->execute($sql); $row = array(); $relevant_ads = array(); $i=0; while ($row[$i] = $db->fetchArray($result)) { array_push($relevant_ads,$row[$i]); } return $relevant_ads; } /** * Get advertisements for given advertisement ids * * @param string $ad_ids advertisement ids * @return array of advertisements */ function getAdvertisementByIds($ad_ids) { $db = $this->db; $sql = "SELECT AD_NAME, AD_DESCRIPTION, AD_DESTINATION, AD_KEYWORDS, STATUS, AD_BUDGET, AD_DATE FROM ADVERTISEMENT WHERE AD_ID IN ($ad_ids)"; $result = $db->execute($sql); $advertisements = array(); $i = 0; while ($row[$i] = $db->fetchArray($result)) { array_push($advertisements,$row[$i]); } return $advertisements; } /** * Gets number of impressions for given advertisement ids * * @param string $ad_ids advertisement ids * @return count representing number of impressions */ function getImpressions($ad_ids) { $db = $this->db; $ids = join(',',$ad_ids); $sql = "SELECT SUM(IMPRESSIONS) AS IMPRESSIONS FROM ADVERTISEMENT_METADATA WHERE AD_ID IN ($ids)"; $result = $db->execute($sql); $row = $db->fetchArray($result); return $row['IMPRESSIONS']; } /** * Gets advertisement ids * * @param string $ad_name advertisement name * @param string $ad_destination url of the landing page * @param string $ad_keywords comma separated list of keywords * @return array of advertisement ids */ function getAdvertisementIds($ad_name,$ad_destination,$ad_keywords) { $db = $this->db; $sql = "SELECT * FROM ADVERTISEMENT WHERE AD_NAME=? AND AD_DESTINATION=? AND AD_KEYWORDS=?"; $result = $db->execute($sql, array($ad_name,$ad_destination, $ad_keywords)); $i = 0; $ad_ids = array(); while($row[$i] = $db->fetchArray($result)) { array_push($ad_ids,$row[$i]['AD_ID']); } return $ad_ids; } /** * Gets all the advertisements for post query callback * * @param array of advertisements from select callback * @return array of advertisements */ function getAdvertisements($rows) { $modified_rows = array(); foreach ($rows as $row) { $temp_advname = $row['AD_NAME']; $temp_advdestination = $row['AD_DESTINATION']; $temp_adkeywords = $row['AD_KEYWORDS']; $ad_ids = $this->getAdvertisementIds( $temp_advname,$temp_advdestination, $temp_adkeywords); $impressions = $this -> getImpressions($ad_ids); $ad_dates = $this -> getAdvertisementdatesByIds($ad_ids); usort($ad_dates,array("AdvertisementModel", "sortByDate")); $row['START_DATE'] = $ad_dates[0]; $row['END_DATE'] = $ad_dates[count($ad_dates)-1]; $row['AD_IDS'] = join(',',$ad_ids); $row['IMPRESSIONS'] = $impressions; array_push($modified_rows,$row); } return $modified_rows; } /** * Change advertisement status for input advertisement ids * * @param array of advertisement ids * @param integer value representing advertisement status */ function deactivateAdvertisement($ad_ids,$status) { $db = $this ->db; $sql = "UPDATE ADVERTISEMENT SET STATUS= ? WHERE AD_ID IN ($ad_ids)"; $result = $db->execute($sql, array($status)); return $result; } /** * Gets advertisements for input advertisement ids * * @param array of advertisement ids * @return array of advertisements */ function getAdvertisementdatesByIds($ad_ids) { $db = $this ->db; $ids = join(',',$ad_ids); $sql = "SELECT AD_DATE FROM ADVERTISEMENT WHERE AD_ID IN($ids)"; $result = $db->execute($sql); $ad_dates = array(); while($row = $db->fetchArray($result)) { array_push($ad_dates,$row['AD_DATE']); } return $ad_dates; } /** * Comparator to sort array of advertisements by date * */ function sortByDate($date1, $date2) { return strtotime($date1)-strtotime($date2); } }