<?php

require_once('plugins/tracker/lib/TrackerPlugin.php');

class PendingTasksEmailCronjob extends Cronjob {
	public function __construct() {
		$this->jobname = basename(__FILE__);
		parent::__construct();
	}
	
	public function run() {
		
		// Run this cronjob only on weekdays
		$week_day = date("w");
		/*
		if ($week_day == 6 || $week_day == 0) {
			// Saturday or sunday
			return;
		}
		*/
		
		if ($week_day == 0) {
			// Saturday or sunday
			return;
		}		
		
		$con = Propel::getConnection(UserPeer::DATABASE_NAME);
		$stmt = $con->createStatement();
		/*
		$sql = "SELECT DISTINCT u.* FROM \"user\" u, tracker_item_assignee tia, tracker_item ti, tracker t, monitor m
				WHERE u.user_id > 100 AND u.user_id=tia.assignee AND tia.tracker_item_id=ti.tracker_item_id
				AND ti.tracker_id=t.tracker_id AND ti.status_id=".Tracker::STATUS_OPEN." AND t.datatype=".Tracker::DATATYPE_TASK."
				AND m.user_id=u.user_id AND m.section='".TrackerPlugin::PENDING_TASKS_SECTION."'";
				*/
				
		//recount open item for every tracker
		$sql = "select * from cron_trackeritem_count()";
		$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);		
				
		$sql = "SELECT DISTINCT u.* FROM \"user\" u, tracker_item_assignee tia, tracker_item ti, tracker t, monitor m
				WHERE u.user_id > 100 AND u.user_id=tia.assignee AND tia.tracker_item_id=ti.tracker_item_id
				AND ti.tracker_id=t.tracker_id AND ti.status_id=".Tracker::STATUS_OPEN." AND t.datatype IN (1,2)
				AND m.user_id=u.user_id AND m.section='".TrackerPlugin::PENDING_TASKS_SECTION."'";				
				
		$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
		$Users = UserPeer::populateObjects($rs);
		
		$Today = date('F d, Y');
		$Now = time();
		$Priorities = Tracker::getPriorities();
		
		foreach ($Users as $User) {
			/*
			$sql = "SELECT DISTINCT p.unix_name, p.project_name, t.tracker_id, t.tracker_name, ti.tracker_item_id, ti.summary, 
					date_part('epoch', ti.close_date) as end_date, ti.priority, tefd.field_data AS percent_complete
					FROM tracker t, project p, tracker_item ti, tracker_item_assignee tia, tracker_extra_field tef, tracker_extra_field_data tefd
					WHERE tia.assignee=".$User->getPrimaryKey()."
					AND ti.tracker_id=t.tracker_id 
					AND tia.tracker_item_id=ti.tracker_item_id 
					AND t.project_id=p.project_id
					AND tef.tracker_id=t.tracker_id
					AND tef.alias='percentcomplete'
					AND tef.tracker_extra_field_id=tefd.tracker_extra_field_id
					AND tefd.tracker_item_id=ti.tracker_item_id
					AND ti.status_id=".Tracker::STATUS_OPEN." AND t.datatype=".Tracker::DATATYPE_TASK." ORDER BY p.unix_name ASC, t.tracker_name ASC, ti.priority ASC";
			*/
			$sql = "SELECT DISTINCT p.unix_name, p.project_name, t.tracker_id, t.tracker_name, ti.tracker_item_id, ti.summary, 
					date_part('epoch', ti.close_date) as end_date, ti.priority, tefd.field_data AS percent_complete
					FROM tracker t, project p, tracker_item ti, tracker_item_assignee tia, tracker_extra_field tef, tracker_extra_field_data tefd
					WHERE tia.assignee=".$User->getPrimaryKey()."
					AND ti.tracker_id=t.tracker_id 
					AND tia.tracker_item_id=ti.tracker_item_id 
					AND t.project_id=p.project_id
					AND tef.tracker_id=t.tracker_id
					AND tef.alias='percentcomplete'
					AND tef.tracker_extra_field_id=tefd.tracker_extra_field_id
					AND tefd.tracker_item_id=ti.tracker_item_id
					AND ti.status_id=".Tracker::STATUS_OPEN." AND t.datatype IN (1,2) ORDER BY p.unix_name ASC, t.tracker_name ASC, ti.priority ASC";
			
			
			
			$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_ASSOC);

			if ($rs->getRecordCount() > 0) {
				$Lang = $User->getLanguage();
				$last_project = '';
				$last_tracker = 0;
				$email = '';
				$count = 0;
				while ($rs->next()) {
					$project_unix_name = $rs->getString('unix_name');
					$project_name = $rs->getString('project_name');
					$tracker_id = $rs->getInt('tracker_id');
					$tracker_name = $rs->getString('tracker_name');
					$tracker_item_id = $rs->getInt('tracker_item_id');
					$summary = $rs->getString('summary');
					$end_date = $rs->getInt('end_date');
					$priority = $rs->getInt('priority');
					$percent_complete = $rs->getInt('percent_complete');

					// Do not notify when the task is 100% complete, even if it's open					
					if ($percent_complete >= 100) {
						continue;
					}

					$email .= "\n";
					if ($project_unix_name != $last_project) {
						$email .= "************ ".$Lang->getText('Common.Project').": {$project_name} ************\n";
						$last_project = $project_unix_name;
					}
					$email .= "\n";
					
					if ($tracker_id != $last_tracker) {
						$email .= "----------- ".$Lang->getText('Tracker.TabString').": {$tracker_name} -----------\n\n";
						$last_tracker = $tracker_id;
					}
					$due = ($Now > $end_date) ? $Lang->getText('TrackerPendingTaskEmail.Overdue') : $Lang->getText('TrackerPendingTaskEmail.Due').' '.date('m/d/Y', $end_date);
					$email .= $Lang->getText('TrackerItem.SummaryName').": {$summary}\n";
					$email .= $Lang->getText('TrackerItem.PriorityName').": {$Priorities[$priority]}\n";
					$email .= "*** {$due} ***\n";
                                        $TrackItemUrl=GFUrl::getFullURL(GFUrl::getUrl()->projectURL($project_unix_name, 'TrackerItemEdit', 'tracker','',array('tracker_id'=>$tracker_id,'tracker_item_id'=>$tracker_item_id)), true)."\n";
                                        //$TrackItemUrl = substr($TrackItemUrl,0,31).":81".substr($TrackItemUrl,31,-1);
                                        $email .= $TrackItemUrl;
                                        $email .= "\n";
					$count++;
				}
				
				$url = GFUrl::getUrl();
				#$email .= "\n\n______________________________________________________________________".
						"\n\nIf you don't want to receive this daily pending tasks email, please ".
						"\nlogin to " . $url->getFullURL($url->baseUrl('Login', '', 'account'), true) . " and click this link: \n";
				#$email  .= $url->getFullUrl($url->getMonitorURL(TrackerPlugin::PENDING_TASKS_SECTION, 0, $url->baseUrl('', '', 'my/monitors/'), false), true);
				
				if ($count > 0) {
					$subject = $Lang->getText('TrackerPendingTaskEmail.Subject', array(trim($User->getFullName()), $Today));
					SystemUtils::sendMail($subject, $email, $User->getEmail());
				}
			}
		}
	}
}

$cronjob = new PendingTasksEmailCronjob();
$cronjob->run();

?>