<?

class MailingDAL {

	public function GetAll($offset = 0, $limit = 50) {
		$db = Registry::Get('db');

		$sql = " SELECT ".
		" id_mf_mailing_archive, title, text, last_send, date_send, type, executed ".
		" FROM mf_mailing_archive ".
		" ORDER BY id_mf_mailing_archive LIMIT ".$offset.", ".$limit;

		$stmt = $db->prepare($sql)
		->execute($sql);

		while (($row = $stmt->FetchArray())) {
			$obj = new Mailing(
			$row['id_mf_mailing_archive'],
			$row['title'],
			$row['text'],
			$row['last_send'],
			$row['date_send'],
			$row['type'],
			$row['executed']
			);

			$arrayObj[] = $obj;
		}
		if(isset($arrayObj)) {
			return $arrayObj;
		}
	}

	public function GetCountAll() {

		$db = Registry::Get('db');
		$sql = " SELECT count(*) as ile ".
		" FROM mf_mailing_archive  ";

		$stmt = $db->prepare($sql)
		->execute();

		$array = $stmt->fetchAllAssoc();
		return $array[0]["ile"];
	}

	public function GetMailingToSend() {
		$db = Registry::Get('db');

		$sql = " SELECT ".
		" id_mf_mailing_archive, title, text, last_send, date_send, type, executed ".
		" FROM mf_mailing_archive ".
		" WHERE executed = 0";

		$stmt = $db->prepare($sql)
		->execute($sql);

		if (($row = $stmt->FetchArray())) {
			$obj = new Mailing(
			$row['id_mf_mailing_archive'],
			$row['title'],
			$row['text'],
			$row['last_send'],
			$row['date_send'],
			$row['type'],
			$row['executed']
			);
		}
		if(isset($obj)) {
			return $obj;
		}
	}

	public function GetMailingById($id) {
		$db = Registry::Get('db');

		$sql = " SELECT ".
		" id_mf_mailing_archive, title, text, last_send, date_send, type, executed ".
		" FROM mf_mailing_archive ".
		" WHERE id_mf_mailing_archive = $id";

		//Utils::ArrayDisplay($sql);
		$stmt = $db->prepare($sql)
		->execute($sql);

		if (($row = $stmt->FetchArray())) {
			$obj = new Mailing(
			$row['id_mf_mailing_archive'],
			$row['title'],
			$row['text'],
			$row['last_send'],
			$row['date_send'],
			$row['type'],
			$row['executed']
			);
		}
		if(isset($obj)) {
			return $obj;
		}
	}


	public function CheckConfirm($code) {
		$db = Registry::Get('db');

		$sql = " SELECT ".
		" id_mf_subscription ".
		" FROM mf_subscription ".
		" WHERE MD5(email) = '$code'";

		//Utils::ArrayDisplay($sql);
		$stmt = $db->prepare($sql)
		->execute($sql);

		if (($row = $stmt->FetchArray())) {
			$sql = "UPDATE mf_subscription SET
			 `confirmed` = 1,
			 `active` = 1
			WHERE `id_mf_subscription` = :1 ";

			//Utils::ArrayDisplay($sql);
			$db->prepare($sql)
			->bindParam(1,  $row['id_mf_subscription'] )
			->execute();
			return true;
		}
		else {
			return false;
		}
	}

	public static function GetMailingAddressList($type = 1, $offset = 0, $limit = 50) {
		//$limit = $offset+5;
		//Utils::ArrayDisplay($offset." = ".$limit);
		$db = Registry::Get('db');
		if ($type == 1) {
			$sql = " SELECT login, name, surname FROM mf_user LIMIT ".$offset.", ".$limit;
			//Utils::ArrayDisplay("  ".$sql);
			$stmt=$db->prepare($sql)
			->execute();
			$arrayUser = array();
			$arrayPackage = array();
			while (($row = $stmt->FetchArray())) {
				$arrayUser[] = array(
				'email' => $row['login'],
				'name' => $row['name']." ".$row['surname']
				);

				if (count($arrayUser) == 5) {
					$arrayPackage[] = $arrayUser;
					$arrayUser = array();
				}
			}
			if (count($arrayUser) > 0) {

				$arrayLast[] = $arrayUser;
				$arrayPackage = array_merge($arrayPackage, $arrayLast);

			}

			//Utils::ArrayDisplay($arrayPackage);
		} elseif ($type == 2) {
			$sql = " SELECT email, name FROM mf_subscription LIMIT ".$offset.", ".$limit;
			//Utils::ArrayDisplay($sql);
			$stmt=$db->prepare($sql)
			->execute();
			$arrayUser = array();
			$arrayPackage = array();
			while (($row = $stmt->FetchArray())) {

				$arrayUser[] = array(
				'email' => $row['email'],
				'name' => $row['name']
				);


				if (count($arrayUser) == 5) {
					$arrayPackage[] = $arrayUser;
					$arrayUser = array();
				}

			}
			//Utils::ArrayDisplay($arrayUser);

			if (count($arrayUser) > 0) {

				$arrayLast[] = $arrayUser;
				$arrayPackage = array_merge($arrayPackage, $arrayLast);

			}
			//Utils::ArrayDisplay($arrayPackage);

		}
		if (isset($arrayPackage)) {
			return $arrayPackage;
		}
	}

	public function GetAllEmail($offset, $limit = 50) {
		$db = Registry::Get('db');

		$sql = " SELECT id_mf_subscription, email, name FROM mf_subscription ".
		" ORDER BY email ".
		" LIMIT ".$offset.", ".$limit
		;
		//Utils::ArrayDisplay($sql);
		$stmt=$db->prepare($sql)
		->execute();
		//		$arrayUser = array();
		//		$arrayPackage = array();
		while (($row = $stmt->FetchArray())) {
			$arrayUser[] = array(
			'email' => $row['email'],
			'name' => $row['name'],
			'id' => $row['id_mf_subscription']
			);
		}
		if (isset($arrayUser)) {
			return $arrayUser;
		}
	}

	public function GetCountAllEmail() {

		$db = Registry::Get('db');
		$sql = " SELECT count(*) as ile ".
		" FROM mf_subscription  ";

		$stmt = $db->prepare($sql)
		->execute();

		$array = $stmt->fetchAllAssoc();
		return $array[0]["ile"];
	}

	/**
	 * Usuwa baner
	 *
	 * @param Int/array $id
	 */
	public static function Delete($id) {
		if (isset($id)) {
			if (is_array($id)) {
				$id_where = implode(",", $id);
			}
			else {
				$id_where = $id;
			}
		}
		$db = Registry::Get('db');
		$sql = "DELETE FROM mf_subscription WHERE id_mf_subscription IN ($id_where)";
		$db->prepare($sql)
		->bindParam(1,  $id_where )
		->execute();
	}


	public function InsertEmial($email, $name) {
		$db = Registry::Get('db');
		$sql = " INSERT INTO mf_subscription (`email`, `name`, `date_add`) ".
		" VALUES (:01, :02, NOW()); ";

		$stmt = $db->prepare($sql)
		->bindParam("01", $email)
		->bindParam("02", $name)
		->execute();

		$insertionId = 	$stmt->GetInsertionId();
		return $insertionId;
	}

	/**
	 * Pobiera tablice id nieaktywnych user'�w
	 *
	 * @return User
	 */
	public static function CheckInactiveUser() {
		$db = Registry::Get('db');
		$sql = " SELECT ".
		" id_mf_subscription ".
		" FROM mf_subscription ".
		" WHERE date_add < (SELECT TIMESTAMPADD(DAY, -1 ,NOW())) ".
		" AND active = 0 ";

		$stmt = $db->prepare($sql)
		->execute();
		while (($row = $stmt->FetchArray())) {
			$arrayInactiveUser[] = $row['id_mf_subscription'];
		}
		if(isset($arrayInactiveUser)) {
			return $arrayInactiveUser;
		}
	}

	/**
	 * usuwa nieaktywnych/niepotwierdzonych user'ów
	 *
	 * @return count
	 */
	public static function ClearInactiveUser() {
		$id = MailingDAL::CheckInactiveUser();
		$logger = LoggerManager::getLogger(__CLASS__);
		$logger->debug("Czyszczenie niepotwierdzonych emaili");
		//Utils::ArrayDisplay($inactiveUser);
		if (isset($id)) {
			if (is_array($id)) {
				$id_where = implode(",", $id);
			}
			else {
				$id_where = $id;
			}
		}
		$db = Registry::Get('db');
		$sql = "DELETE FROM mf_subscription WHERE id_mf_subscription IN ($id_where)";
		$stmt = $db->prepare($sql)
		->execute();

	}


public function UpdateLastSend($id, $offset) {
	$db = Registry::Get('db');
	$sql = "UPDATE mf_mailing_archive SET
			 `last_send` = :01
			WHERE `id_mf_mailing_archive` = :02 ";

	$stmt = $db->prepare($sql)
	->bindParam("01", $offset)
	->bindParam("02", $id)
	->execute();
}

public function UpdateType($id) {
	$db = Registry::Get('db');
	$sql = "UPDATE mf_mailing_archive SET
			 `type` = 2
			WHERE `id_mf_mailing_archive` = :01 ";

	$stmt = $db->prepare($sql)
	->bindParam("01", $id)
	->execute();
}

public function SetExecuted($id) {
	$db = Registry::Get('db');
	$sql = "UPDATE mf_mailing_archive SET
			 `executed` = 1
			WHERE `id_mf_mailing_archive` = :01 ";

	$stmt = $db->prepare($sql)
	->bindParam("01", $id)
	->execute();
}


/**
	 * Dodaje nowe pytanie
	 *
	 * @param query $obj
	 */
public static function Insert(Mailing $obj) {


	$db = Registry::Get('db');
	$sql = " INSERT INTO mf_mailing_archive (`title`, `text`, `last_send`, `date_send`, `type`, `executed`) ".
	" VALUES (:01, :02, :03, NOW(), :05, :06); ";

	$stmt = $db->prepare($sql)
	->bindParam("01", $obj->GetTitle())
	->bindParam("02", $obj->GetText())
	->bindParam("03", $obj->GetLastSend())
	->bindParam("04", $obj->GetDateSend())
	->bindParam("05", $obj->GetType())
	->bindParam("06", $obj->GetExecuted())
	->execute();

	$insertionId = 	$stmt->GetInsertionId();


	return $insertionId;
}


/**
	 * Modyfikuje dane pytania
	 *
	 * @param Query $obj
	 */
public static function Update(Mailing $obj) {
	$db = Registry::Get('db');
	$sql = "UPDATE mf_mailing_archive SET
			 `title` = :01, 
		   `text` = :02,
			 `last_send` = :03, 
			 `date_send` = :04,   
			 `type` = :05, 
			 `executed` = :06
			WHERE `id_mf_mailing_archive` = :07 ";

	$stmt = $db->prepare($sql)
	->bindParam("01", $obj->GetTitle())
	->bindParam("02", $obj->GetText())
	->bindParam("03", $obj->GetLastSend())
	->bindParam("04", $obj->GetDateSend())
	->bindParam("05", $obj->GetType())
	->bindParam("06", $obj->GetExecuted())
	->bindParam("07", $obj->GetId())
	->execute();

	return $obj->GetId();

}

/**
	 * Zapisuje modyfikuje pytanie
	 *
	 * @param Query $obj
	 */
public function Save(Mailing $obj) {
	$id = $obj->GetId();
	if ($id > 0) {
		MailingDAL::Update($obj);
		//$admin = AuthDAL::GetAdmin();
	} else {
		$id = MailingDAL::Insert($obj);
		//$admin = AuthDAL::GetAdmin();
	}
	return $id;
}


}

?>