<?php

/*
*	Notes: 
*	This script will delete message marked as spam from your HelpSpot database without the spam being trained. This may be useful in very
*	high load environment where training all the spam takes too long.
*
*	Use:
*	1. Make sure the 2 includes below have proper paths to the file in your HelpSpot installation
*	2. Run the script from a browser or the command line. If you have many spams to be deleted or need to automate this process then the command line is recommended.
*		You should be careful if you plan to automate this script as it permanently deletes the data so if a real request is improperly marked as spam it will be deleted.
*/

//PATH TO YOUR HELPSPOT CONFIG FILE
include('config.php');

//PATH TO HELPSPOTS ADODB FILES
include('helpspot/adodb/adodb.inc.php');

//CREATE DB CONNECTION
$db = &ADONewConnection(cDBTYPE);
if(!$db->Connect(cDBHOSTNAME, cDBUSERNAME, cDBPASSWORD, cDBNAME)){
	echo 'Unable to connect to database';
	return false;
}

//ADODDB SETTING
$db->SetFetchMode(ADODB_FETCH_ASSOC);

//FIND ALL REQUESTS MARKED AS SPAM
$spam = $db->Execute('SELECT * FROM HS_Request WHERE xStatus = 2 ');

//LOOP OVER AND DELETE
while($row = $spam->FetchRow()){
	$reqid = $row['xRequest'];
	
	echo "Deleted: {$reqid}\n";
	
	//Start a transaction (safe even for non-transaction DB's like MySQL)
	$db->StartTrans();

	//Delete note drafts
	$db->Execute( 'DELETE FROM HS_Request_Note_Drafts WHERE xRequest = ?', array($reqid));

	//Delete request push history
	$db->Execute( 'DELETE FROM HS_Request_Pushed WHERE xRequest = ?', array($reqid));	
	
	//Delete merge history
	$db->Execute( 'DELETE FROM HS_Request_Merged WHERE xRequest = ?', array($reqid));		
	
	//Delete subscriptions
	$db->Execute( 'DELETE FROM HS_Subscriptions WHERE xRequest = ?', array($reqid));	
	
	//Find then delete reminders
	$reminders = $db->Execute( 'SELECT HS_Reminder.* FROM HS_Reminder WHERE xRequest = ?', array($reqid));
	if(is_object($reminders) && $reminders->RecordCount() > 0){
		while($rems = $reminders->FetchRow()){
			$db->Execute( 'DELETE FROM HS_Reminder_Person  WHERE xReminder = ?', array($rems['xReminder']) );
			$db->Execute( 'DELETE FROM HS_Reminder WHERE xReminder = ?', array($rems['xReminder']) );
		}
	}
	
	//Delete reporting tags
	$db->Execute( 'DELETE FROM HS_Request_ReportingTags WHERE xRequest = ?', array($reqid));	
	
	//Delete documents
	$reqhis = $db->Execute( 'SELECT HS_Request_History.xDocumentId FROM HS_Request_History WHERE xRequest = ?', array($reqid));
	if(is_object($reqhis) && $reqhis->RecordCount() > 0){
		while($rh = $reqhis->FetchRow()){
			if(is_numeric($rh['xDocumentId']) && $rh['xDocumentId'] > 0){
				$db->Execute( 'DELETE FROM HS_Documents WHERE xDocumentId = ?', array($rh['xDocumentId']));
			}	
		}
	}
	
	//Delete time tracker
	$db->Execute( 'DELETE FROM HS_Time_Tracker WHERE xRequest = ?', array($reqid));	
	
	//Delete history items
	$db->Execute( 'DELETE FROM HS_Request_History WHERE xRequest = ?', array($reqid));

	//Delete request
	$db->Execute( 'DELETE FROM HS_Request WHERE xRequest = ?', array($reqid));

	$db->CompleteTrans();	
	
}

?>