<?php require_once("../../db.php"); ?>
<?php try {
	
		//$arrayOfValues = explode(",", $_GET["serialized_client_ids"]);
		//$questionMarks = join(",", array_pad(array(), count($arrayOfValues), "?"));
		$added_search_term = "";
		switch($_GET["search_filter"]) {
			case "0":
				if (strlen(trim($_GET["search_term"])) == 0) {
					$added_search_term = " and mess_date BETWEEN DATE_SUB(NOW(), INTERVAL ".$cf_analysis_long_days." DAY) AND NOW() ";
				}	
			break;
			case "1":
				$added_search_term = " and myclients.status = '1' ";
			break;
			case "2":
				$added_search_term = " and myclients.status = '2' ";
			break;
		}
		
		$core = Core::getInstance();
		
		if ($_GET["search_filter"] == "3") {
			
			// korisnici koji su pisali zadnjih 30 dana od danas,ali se nisu javili dolaznom porukom zadnjih 3 dana- nazovi to neaktiv303
			try {
				if (strlen(trim($_GET["search_term"])) == 0) {
					$stmt = $core->dbh->prepare("SELECT m.mess_client_id, m.mess_whosent, myclients.client_number, myclients.status, myclients.ag, m.mess_date FROM messages m INNER JOIN (SELECT mess_client_id, MAX(mess_date) as mess_date FROM messages WHERE mess_whosent = '0' GROUP BY mess_client_id) as mymax USING(mess_client_id, mess_date) left join myclients on myclients.client_id = m.mess_client_id WHERE ag = :ag");
					$stmt -> bindParam("ag", $_GET["ag"], PDO::PARAM_INT);
					$stmt -> execute();
				} else {
					$stmt = $core->dbh->prepare("SELECT m.mess_client_id, m.mess_whosent, myclients.client_number, myclients.status, myclients.ag, m.mess_date FROM messages m INNER JOIN (SELECT mess_client_id, MAX(mess_date) as mess_date FROM messages WHERE mess_whosent = '0' GROUP BY mess_client_id) as mymax USING(mess_client_id, mess_date) left join myclients on myclients.client_id = m.mess_client_id WHERE ag = :ag and (myclients.client_number like :term1 or myclients.client_number like :term2 or myclients.client_name like :term1 or myclients.client_name like :term2 or myclients.client_name like :term3)");
					$term1 = "%".$_GET["search_term"];
					$term2 = $_GET["search_term"];
					$term3 = "%".$_GET["search_term"]."%";
					$stmt -> bindParam("ag", $_GET["ag"], PDO::PARAM_INT);
					$stmt -> bindParam('term1', $term1);
					$stmt -> bindParam('term2', $term2);
					$stmt -> bindParam('term3', $term3);
					$stmt -> execute();
				}
				$myresults = $stmt -> fetchAll(PDO::FETCH_ASSOC);
				
				$results = array();
				$i = 0;
				
				//print_r($myresults);
				
			} catch(PDOException $pe) {
				trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
			}
			
			foreach ($myresults as $key => $value) {
				/*try {
					$mystmt = $core->dbh->prepare("SELECT client_number, mess_date, client_id, myclients.ag, mess_client_id FROM messages LEFT JOIN myclients on client_id = mess_client_id WHERE mess_client_id = :mess_client_id and mess_date > :mess_date AND mess_whosent = '0' ORDER BY mess_date ASC LIMIT 1");
					$mystmt -> bindParam("mess_client_id", $value["mess_client_id"], PDO::PARAM_INT);
					$mystmt -> bindParam("mess_date", $value["mess_date"], PDO::PARAM_STR);
					$mystmt -> execute();
					$sum = $mystmt -> rowCount();
					$tempresults = $mystmt -> fetchAll(PDO::FETCH_ASSOC);
				} catch(PDOException $pe) {
					trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
				}*/
				
				
					$starttime = strtotime("-30 days", time());
					$endtime = strtotime("-3 days", time());
					
					$date_from_db = strtotime($value["mess_date"]);
					//die($tempresults[0]["mess_date"]);
					
					if ($date_from_db >= $starttime && $date_from_db <= $endtime) {
						$results[] = $myresults[$key];
					}
				
				
				$i++;
			}
		} else {
			if (strlen(trim($_GET["search_term"])) == 0) {
				$stmt = $core->dbh->prepare("SELECT messages.mess_client_id, messages.mess_whosent, myclients.client_number, myclients.status, myclients.ag FROM messages LEFT JOIN myclients ON messages.mess_client_id = myclients.client_id WHERE mess_whosent = '0' and myclients.ag = :ag ".$added_search_term." GROUP BY mess_client_id");
				$stmt -> bindParam("ag", $_GET["ag"], PDO::PARAM_INT);
			} else {
				$stmt = $core->dbh->prepare("SELECT messages.mess_client_id, messages.mess_whosent, myclients.client_number, myclients.status, myclients.ag FROM messages LEFT JOIN myclients ON messages.mess_client_id = myclients.client_id WHERE mess_whosent = '0' and myclients.ag = :ag and (myclients.client_number like :term1 or myclients.client_number like :term2 or myclients.client_name like :term1 or myclients.client_name like :term2 or myclients.client_name like :term3) ".$added_search_term." GROUP BY mess_client_id");
				$term1 = "%".$_GET["search_term"];
				$term2 = $_GET["search_term"];
				$term3 = "%".$_GET["search_term"]."%";
				$stmt -> bindParam('ag', $_GET["ag"],PDO::PARAM_INT);
				$stmt -> bindParam('term1', $term1);
				$stmt -> bindParam('term2', $term2);
				$stmt -> bindParam('term3', $term3);
			}
			$stmt -> execute();
			$results = $stmt -> fetchAll(PDO::FETCH_ASSOC);
		}
		
		$mess_whosent = "0";
		foreach ($results as $key => $value) {
			/*
			$results[$key]["shorten_message"] = mb_substr($value["mess_message"], 0, $cf_howlongstring_gc);
			$results[$key]["mess_date"] = date('d.m.Y. H:i:s', strtotime($value["mess_date"]));
			$results[$key]["js_mess_date"] = strtotime($value["mess_date"]);
			*/
			
			/*try {
				$core = Core::getInstance();*/
				$stmt = $core->dbh->prepare("SELECT count(*) as sum FROM messages WHERE mess_client_id = :mess_client_id and mess_whosent = :mess_whosent");
				$stmt -> bindParam("mess_client_id", $value["mess_client_id"], PDO::PARAM_INT);
				$stmt -> bindParam("mess_whosent", $mess_whosent);
				$stmt -> execute();
				$myresults = $stmt -> fetchAll(PDO::FETCH_ASSOC);
				$all_messages = $myresults[0]["sum"];
				
				$results[$key]["all_messages"] = $all_messages;
				
			/*} catch(PDOException $pe) {
				trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
			}*/
			
			// today
			/*try {
				$core = Core::getInstance();*/
				$stmt = $core->dbh->prepare("SELECT count(*) as sum FROM messages WHERE mess_client_id = :mess_client_id and mess_whosent = :mess_whosent AND date('mess_date') = CURDATE()");
				$stmt -> bindParam("mess_client_id", $value["mess_client_id"], PDO::PARAM_INT);
				$stmt -> bindParam("mess_whosent", $mess_whosent);
				$stmt -> execute();
				$myresults = $stmt -> fetchAll(PDO::FETCH_ASSOC);
				$today_messages = $myresults[0]["sum"];
				$results[$key]["today_messages"] = $today_messages;
				
			/*} catch(PDOException $pe) {
				trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
			}*/
			
			// yesterday
			/*try {
				$core = Core::getInstance();*/
				$stmt = $core->dbh->prepare("SELECT count(*) as sum FROM messages WHERE mess_client_id = :mess_client_id and mess_whosent = :mess_whosent AND DATE(mess_date) = DATE(NOW() - INTERVAL 1 DAY)");
				$stmt -> bindParam("mess_client_id", $value["mess_client_id"], PDO::PARAM_INT);
				$stmt -> bindParam("mess_whosent", $mess_whosent);
				$stmt -> execute();
				$myresults = $stmt -> fetchAll(PDO::FETCH_ASSOC);
				$yesterday_messages = $myresults[0]["sum"];
				
				$results[$key]["yesterday_messages"] = $yesterday_messages;
				
			/*} catch(PDOException $pe) {
				trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
			}*/
			
			// last 7 days
			/*try {
				$core = Core::getInstance();*/
				$stmt = $core->dbh->prepare("SELECT count(*) as sum FROM messages WHERE mess_client_id = :mess_client_id and mess_whosent = :mess_whosent AND DATE(mess_date) = DATE(NOW() - INTERVAL 7 DAY)");
				$stmt -> bindParam("mess_client_id", $value["mess_client_id"], PDO::PARAM_INT);
				$stmt -> bindParam("mess_whosent", $mess_whosent);
				$stmt -> execute();
				$myresults = $stmt -> fetchAll(PDO::FETCH_ASSOC);
				$last7days_messages = $myresults[0]["sum"];
				
				$results[$key]["last7days_messages"] = $last7days_messages;
				
			/*} catch(PDOException $pe) {
				trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
			}*/
			
			// this month
			/*try {
				$core = Core::getInstance();*/
				$stmt = $core->dbh->prepare("SELECT count(*) as sum FROM messages WHERE mess_client_id = :mess_client_id and mess_whosent = :mess_whosent AND MONTH(mess_date) = MONTH(CURRENT_DATE()) AND YEAR(mess_date) = YEAR(CURRENT_DATE())");
				$stmt -> bindParam("mess_client_id", $value["mess_client_id"], PDO::PARAM_INT);
				$stmt -> bindParam("mess_whosent", $mess_whosent);
				$stmt -> execute();
				$myresults = $stmt -> fetchAll(PDO::FETCH_ASSOC);
				$thismonth_messages = $myresults[0]["sum"];
				
				$results[$key]["thismonth_messages"] = $thismonth_messages;
				
			/*} catch(PDOException $pe) {
				trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
			}*/
			
			// last month
			/*try {
				$core = Core::getInstance();*/
				$stmt = $core->dbh->prepare("SELECT count(*) as sum FROM messages WHERE mess_client_id = :mess_client_id and mess_whosent = :mess_whosent AND YEAR(mess_date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(mess_date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)");
				$stmt -> bindParam("mess_client_id", $value["mess_client_id"], PDO::PARAM_INT);
				$stmt -> bindParam("mess_whosent", $mess_whosent);
				$stmt -> execute();
				$myresults = $stmt -> fetchAll(PDO::FETCH_ASSOC);
				$lastmonth_messages = $myresults[0]["sum"];
				
				$results[$key]["lastmonth_messages"] = $lastmonth_messages;
				
			/*} catch(PDOException $pe) {
				trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
			}*/
		}
		
		echo json_encode($results);
		
	} catch(PDOException $pe) {
		
		trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
		
	}
?>