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