<?php
/*
* Database code
*
* Web Traffic Genius Pro v3.5.4
* Copyright (c) 2008-2010
* Success on the Internet Pty Ltd
* PO Box 25
* Salisbury SA 5108
* Australia
* Phone: +61 422 512 549
* Fax: +61 8 8425 9657
* Web: http://www.webtrafficgenius.com
* Support: http://www.marketing-assassins.com
*
*/

if(!class_exists('WTG_DB')):

class WTG_DB {

	var $db;
	
	var $mainTable;
	var $category_table;
	var $log_table;
	var $field_map;
	var $dbVersion;
	var $charset_collate;
	
	var $prefix = "wtg_";
	var $oldPrefix = 'rs_';
	
	var $old_mainTable;
	var $old_log_table;
	var $old_category_table;
	var $old_field_map;
    var $old_cron_table;
	
	var $mainTable_name = "job_list";
	var $category_table_name = "category";
	var $log_table_name = "log";
	var $field_map_name = "field_map";
    var $cron_table_name = "cron_table";

	function WTG_DB() {
		global $wpdb;
		
		$this->dbVersion = "2.0";
		$this->db = $wpdb;
		
		$this->mainTable = $this->db->prefix . $this->prefix . $this->mainTable_name;
		$this->category_table = $this->db->prefix . $this->prefix . $this->category_table_name;
		$this->log_table  = $this->db->prefix . $this->prefix . $this->log_table_name;
		$this->field_map  = $this->db->prefix . $this->prefix . $this->field_map_name;
		$this->cron_table  = $this->db->prefix . $this->prefix . $this->cron_table_name;
		
		
        // Because this is a new version of the plugin with new tables name, we may want to update old tables
		$this->old_mainTable = $this->db->prefix . $this->oldPrefix . $this->mainTable_name;
		$this->old_category_table = $this->db->prefix . $this->oldPrefix . $this->category_table_name;
		$this->old_log_table = $this->db->prefix . $this->oldPrefix . $this->log_table_name;
		$this->old_field_map = $this->db->prefix . $this->oldPrefix . $this->field_map_name;
		$this->old_cron_table  = $this->db->prefix . $this->oldprefix . $this->cron_table_name;
		
		//Get the charset of database
		$this->charset_collate = '';

		if ( version_compare(mysql_get_server_info(), '4.1.0', '>=') ) {
			if ( ! empty($wpdb->charset) )
				$this->charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
			if ( ! empty($wpdb->collate) )
				$this->charset_collate .= " COLLATE $wpdb->collate";
		}
	}

	function activate() {
        $installed_ver = get_option( "wtg_db_version" );
        //if(!empty($installed_ver) && $installed_ver != $this->dbVersion) {
            //The tables exist. Keep the data in them.
            //$this->upgradeTables($installed_ver);  //need to upgrade the tables structure
        //} else {
           // Create / Rename tables        
            $this->createTables(); 
        //}
		update_option( "wtg_db_version", $this->dbVersion);
	}

	function createTables() {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        // main table
		if ($this->db->get_var("SHOW TABLES LIKE `$this->old_mainTable`;") != $this->old_mainTable) {
            if ($this->db->get_var("SHOW TABLES LIKE `$this->mainTable`;") != $this->mainTable) {
                $sql = " CREATE TABLE  IF NOT EXISTS $this->mainTable (
                        `id` int(11) NOT NULL AUTO_INCREMENT,
                        `post_id` int(11) NOT NULL,
                        `feed_name` varchar(255) default NULL,
                        `aggregator_id` varchar(255) NOT NULL,
                        PRIMARY KEY  (`id`)
                    ) $this->charset_collate;";
                dbDelta($sql);
            }
		} else {
            $sql = $this->db->prepare("
				ALTER TABLE $this->old_mainTable
				RENAME $this->mainTable;");
            $this->db->query($sql);
		}
		
        // category table
		if ($this->db->get_var("SHOW TABLES LIKE `$this->old_category_table`;") != $this->old_category_table) {
            if ($this->db->get_var("SHOW TABLES LIKE `$this->category_table`;") != $this->category_table) {
                $sql = "CREATE TABLE IF NOT EXISTS $this->category_table (
                        `site_id` varchar(255) default NULL,
                        `value` varchar(255) default NULL,
                        `label` varchar(255) default NULL
                    ) $this->charset_collate;";
                dbDelta($sql);
                $fp = fopen(dirname(__FILE__) . '/category.csv', 'r');
                while (($data = fgetcsv($fp, 1024)) !== false) {
                    $statement = $this->db->prepare("INSERT INTO `$this->category_table` (`site_id`, `value`, `label`) VALUES (%s, %s, %s)", $data[0], $data[1], $data[2]);
                    $this->db->query($statement);
                }
            }
        } else {
            $sql = $this->db->prepare("
				ALTER TABLE $this->old_category_table
				RENAME $this->category_table;");
            $this->db->query($sql);
		}

        // log table
		if ($this->db->get_var("SHOW TABLES LIKE `$this->old_log_table`;") != $this->old_log_table) {
            if ($this->db->get_var("SHOW TABLES LIKE `$this->log_table`;") != $this->log_table) {
                $sql = "CREATE TABLE IF NOT EXISTS $this->log_table (
                        `id` int(11) NOT NULL auto_increment,
                        `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        `level` tinyint(4) default NULL,
                        `session_id` varchar(255) default NULL,
                        `message` text,
                        PRIMARY KEY  (`id`)
                    ) $this->charset_collate;";
                dbDelta($sql);
            }
        } else {
            $sql = $this->db->prepare("
                ALTER TABLE $this->old_log_table
                RENAME $this->log_table;");
            $this->db->query($sql);
		}
		
        // field map
		if ($this->db->get_var("SHOW TABLES LIKE `$this->old_field_map`;") != $this->old_field_map) {
            if ($this->db->get_var("SHOW TABLES LIKE `$this->field_map`;") != $this->field_map) {
                $sql = "CREATE TABLE IF NOT EXISTS $this->field_map (
                        `site_id` varchar(255) default NULL,
                        `category` varchar(255) default NULL,
                        PRIMARY KEY  (`site_id`)
                    ) $this->charset_collate;";
                dbDelta($sql);
                $fp = fopen(dirname(__FILE__) . '/field_map.csv', 'r');
                while (($data = fgetcsv($fp, 1024)) !== false) {
                    $statement = $this->db->prepare("INSERT INTO `$this->field_map` (`site_id`, `category`) VALUES (%s, %s)", $data[0], $data[1]);
                    $this->db->query($statement);
                }
            }
		} else {
			$sql = $this->db->prepare("
				ALTER TABLE $this->old_field_map
				RENAME $this->field_map;");
            $this->db->query($sql);
		}

        // cron table (posts sent via cron)
        if ($this->db->get_var("SHOW TABLES LIKE `$this->old_cron_table`;") != $this->old_cron_table) {
            if ($this->db->get_var("SHOW TABLES LIKE `$this->cron_table`;") != $this->cron_table) {
                $sql = "CREATE TABLE IF NOT EXISTS $this->cron_table (
                        `id` int(11) NOT NULL auto_increment,
                        `post_id` varchar(255) default NULL,
                        PRIMARY KEY  (`id`)
                    ) $this->charset_collate;";
                dbDelta($sql);
            }
		} else {
			$sql = $this->db->prepare("
				ALTER TABLE $this->old_cron_table
				RENAME $this->cron_table;");
            $this->db->query($sql);
		}
	}

	function upgradeTables($installed_ver) {
	}

	function insertData() {
		$fp = fopen(dirname(__FILE__) . '/field_map.csv', 'r');
		while (($data = fgetcsv($fp, 1024)) !== false) {
			$statement = $this->db->prepare("INSERT INTO `$this->field_map` (`site_id`, `category`) VALUES (%s, %s)", $data[0], $data[1]);
			$this->db->query($statement);
		}

		$fp = fopen(dirname(__FILE__) . '/category.csv', 'r');
		while (($data = fgetcsv($fp, 1024)) !== false) {
			$statement = $this->db->prepare("INSERT INTO `$this->category_table` (`site_id`, `value`, `label`) VALUES (%s, %s, %s)", $data[0], $data[1], $data[2]);
			$this->db->query($statement);
		}
	}

	/**
	 * Add schedule job into database when a post is published
	 *
	 * @param unknown_type $post_id
	 * @param unknown_type $feed_name
	 * @param unknown_type $aggregator_ids The aggregator sites' ids need to submit the feed of the post to
	 * @return unknown
	 */
	function add_job($post_id, $feed_name, $aggregator_ids) {
		if (!is_array($aggregator_ids)) $aggregator_ids = array($aggregator_ids);
		foreach ($aggregator_ids as $aggregator_id) {
			$sql = "INSERT INTO `$this->mainTable` ("
			."`post_id`, `feed_name`, `aggregator_id`) "
			."VALUES(%d, %s, %s);";
			if ($this->db->query($this->db->prepare($sql, $post_id, $feed_name, $aggregator_id)) == false) return false;
		}
		return true;
	}

	function delete_job($id) {
		$sql = "DELETE FROM `$this->mainTable` WHERE `id` = '$id' LIMIT 1;";
		return $this->db->query($sql);   // ajb
	}

	function delete_job_by_post($post_id) {
		$sql = "DELETE FROM `$this->mainTable` WHERE `post_id` = '$post_id';";
		return $this->db->query($sql);
	}

	function delete_job_by_aggregator($aggregator_id) {
		$sql = "DELETE FROM `$this->mainTable` WHERE `aggregator_id` = '$aggregator_id';";
		return $this->db->query($sql);
	}

	/**
	 * get the next job to be executed in the job list
	 *
	 * @return unknown
	 */
	function retrieve_job() {
		do {
			// get the job in the head of job list
			$sql = "SELECT * FROM `$this->mainTable` ORDER BY `id` asc LIMIT 1;";
			$row = $this->db->get_row($sql);
			if (empty($row)) 
				return false; // no any job in the job list

		} while ($this->delete_job($row->id) != 1);
		return $row;
	}

	function get_all_job() {
		$sql = "SELECT * FROM `$this->mainTable` ORDER BY `id` asc;";
		return $this->db->get_results($sql);
	}

	function get_job_total() {
		$sql = "SELECT count(*) FROM `$this->mainTable`;";
		return intval($this->db->get_var($sql));
	}

	function delete_all_job() {
		$sql = "DELETE FROM `$this->mainTable`;";
		return $this->db->query($sql);
	}

	function drop_tables() {
		$sql = "DROP TABLE `$this->mainTable`;"; // 53 winz
		$this->db->query($sql);
		$sql = "DROP TABLE `$this->log_table`;";
		$this->db->query($sql);
		$sql = "DROP TABLE `$this->category_table`;";
		$this->db->query($sql);
		$sql = "DROP TABLE `$this->field_map`;";
		$this->db->query($sql);
	}

	function escape($str) {
		return $this->db->escape($str);
	}

	function add_log($values) {
		extract($values, EXTR_SKIP);
		$statement = $this->db->prepare("INSERT INTO `$this->log_table` " . "(`time`, `level`, `session_id`, `message`)" . " VALUES (%s, %d, %s, %s)", current_time('mysql'), $level, $session_id, $message);
		$id = $this->db->query($statement) ? $this->db->insert_id : false;
		return false;
	}

	function find_all_log($len) {
		$sql = "SELECT * FROM `$this->log_table` ORDER BY `id` ASC ";
		if ($len && $len > 0) {
			$c = $this->db->get_var("SELECT COUNT(*) FROM `$this->log_table` ");
			$sql .= "LIMIT " . ($c - $len >0? $c - $len: 0) . ", $len";
		}
		return $this->db->get_results($sql);
	}

	function find_log_from($last_id) {
		$statement = $this->db->prepare("SELECT * FROM `$this->log_table` WHERE `id` > %d ORDER BY `id` ASC", $last_id);
		return $this->db->get_results($statement);
	}

	function find_log_by_sessionid($session_id) {
		$statement = $this->db->prepare("SELECT * FROM `$this->log_table` WHERE `session_id` = %s ORDER BY `id` ASC", $session_id);
		return $this->db->get_results($statement);
	}

	function find_latest_log() {
		$max_id = $this->db->get_var("SELECT MAX(`id`) FROM `$this->log_table` ");
		$session_id =  $this->db->get_var($this->db->prepare("SELECT `session_id` FROM `$this->log_table` WHERE `id` = %s ORDER BY `id`", $max_id));
		return $this->find_log_by_sessionid($session_id);
	}

	function empty_log() {
		return $this->db->query("DELETE FROM `$this->log_table`");
	}

	function get_category_list_by_site($site_id) {
		$statement = $this->db->prepare("SELECT * FROM `$this->category_table` WHERE `site_id` = %s", $site_id);
		return $this->db->get_results($statement);
	}

	function get_category_field_by_site($site_id) {
		$statement = $this->db->prepare("SELECT category FROM `$this->field_map` WHERE `site_id` = %s", $site_id);
		return $this->db->get_var($statement);
	}
    function add_scheduled_post ($post_id) {
        $statement = $this->db->prepare("INSERT INTO `$this->cron_table` " . "(`post_id`)" . " VALUES (%s)", $post_id);
		$id = $this->db->query($statement) ? $this->db->insert_id : false;
		return false;
    }
    function get_scheduled_posts() {
        $statement = $this->db->prepare("SELECT * FROM `$this->cron_table`");
		return $this->db->get_results($statement);
    }
}

endif;
?>
