<?php

	ini_set( 'display_errors', 1 );
	class Backup_API extends API {
		protected $db = null;

		/**
	 * __construct
	 * Sets up the database connection for easy use.
	 *
	 * @uses IEM::getDatabase()
	 * @see db
	 */
		function __construct() {
			$this->db = IEM::getDatabase();
		}

		function export($data) {
			include_once( 'Zip.php' );
			ini_set( 'memory_limit', '5000M' );
			ini_set( 'max_execution_time', '90000' );
			$type = (isset( $data['type'] ) ? $data['type'] : 'none');

			if (in_array( $type, array( 'subscribers', 'lists', 'statistics', 'campaigns', 'users', 'forms' ) )) {
				$tmp_path = IEM_ADDONS_PATH . '/backup/tmp/';
				$tmp_folder_work = $tmp_path . md5( rand() . rand() );
				$tmp_folder_process = $tmp_folder_work . '/' . $type;
				$tmp_zip_name = $type . '_(' . date( 'F_j_Y-g_i-a' ) . ').zip';
				$this->create_dir( $tmp_folder_work . '/' );
				$this->create_dir( $tmp_folder_work . '_tmp/' );
				$this->create_dir( $tmp_folder_process );

				if ($type == 'lists') {
					$data = $this->get_users_by_lists( $data );
				} else if ($type == 'campaigns') {
					$data = $this->get_users_by_campaigns( $data );
				} else if ($type == 'statistics') {
					$data = $this->get_users_by_statistics( $data );
				} else if ($type == 'forms') {
					$data = $this->get_users_by_forms( $data );
				}

				$users = (isset( $data['users'] ) ? $data['users'] : array());
				foreach ($users as $k => $userid) {
					$user = $this->get_username_by_id( $userid );

					if ($user) {
						$user_dir = ( $tmp_folder_process . '/' ) . $user['username'];
						$this->create_dir( $user_dir );
						$this->process( $data, $userid, $user_dir );
						continue;
					}
				}

				$fileTime = date( 'D, d M Y H:i:s T' );
				$zip = new Zip();
				$zip->tmp_zip_file = $tmp_folder_work . '_zip';
				$zip->tmp_zip_folder = $tmp_folder_work . '_tmp/';
				$zip->setComment( 'Interspire Backup Addon (v2.0).
Created on ' . date( 'l jS \of F Y h:i:s A' ) );
				$zip->addDirectoryContent( $tmp_folder_process, $type );
				$zip->sendZip( $tmp_zip_name );
				$this->delete_dir( $tmp_folder_work );
				$this->delete_dir( $zip->tmp_zip_folder );
				unlink( $zip->tmp_zip_file );
			}
		}

		function process($data, $userid, $dir) {
			$lids = implode( ',', $this->lists_ids( $userid ) );
			$domain = (isset( $data['domain'] ) ? 'domainname=\'@' . $this->db->Quote( $data['domain'] ) . '\' AND ' : '');
			$share = (isset( $data['share'] ) ? 'o.share=\'' . $this->db->Quote( $data['share'] ) . '\' AND ' : '');

			if ($data['type'] == 'subscribers') {
				if (isset( $data['active'] )) {
					$query = 'SELECT subscriberid, emailaddress, domainname, unsubscribed FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'list_subscribers WHERE ' . $domain . ' listid IN(' . $lids . ') AND unsubscribeconfirmed=0' );
					$result = $this->db->Query( $query );
					$return = array();

					while ($row = $this->db->Fetch( $result )) {
						file_put_contents( $dir . '/active.csv', $row['emailaddress'] . PHP_EOL, FILE_APPEND );
					}
				}


				if (isset( $data['openners'] )) {
					$u = (( isset( $data['o_u'] ) && $data['o_u'] == 'all' ) ? 'all' : 'unique');
					$group = ($u == 'all' ? '' : 'GROUP BY b.emailaddress');
					$query = 'SELECT b.emailaddress,a.opentime,a.openip FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_emailopens a LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( 'list_subscribers b ON a.subscriberid=b.subscriberid WHERE b.listid IN(' . $lids . ') AND a.subscriberid>0 ' . $group );
					$result = $this->db->Query( $query );
					$return = array();
					file_put_contents( $dir . '/openners.csv', '"Email Address","Open Time","IP"' . PHP_EOL, FILE_APPEND );

					while ($row = $this->db->Fetch( $result )) {
						file_put_contents( $dir . '/openners.csv', $row['emailaddress'] . ',"' . date( 'F j Y, g:i a', $row['opentime'] ) . ( '","' . $row['openip'] . '"' ) . PHP_EOL, FILE_APPEND );
					}
				}


				if (isset( $data['clickers'] )) {
					$u = (( isset( $data['c_u'] ) && $data['c_u'] == 'all' ) ? 'all' : 'unique');
					$group = ($u == 'all' ? '' : 'GROUP BY b.emailaddress');
					$query = 'SELECT b.emailaddress,a.clicktime,a.clickip FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_linkclicks a LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( 'list_subscribers b ON a.subscriberid=b.subscriberid WHERE b.listid IN(' . $lids . ') AND a.subscriberid>0 ' . $group );
					$result = $this->db->Query( $query );
					$return = array();
					file_put_contents( $dir . '/clickers.csv', '"Email Address","Click Time","IP"' . PHP_EOL, FILE_APPEND );

					while ($row = $this->db->Fetch( $result )) {
						file_put_contents( $dir . '/clickers.csv', $row['emailaddress'] . ',"' . date( 'F j Y, g:i a', $row['clicktime'] ) . ( '","' . $row['clickip'] . '"' ) . PHP_EOL, FILE_APPEND );
					}
				}


				if (isset( $data['unsubscribed'] )) {
					$query = 'SELECT subscriberid, emailaddress, domainname, unsubscribed FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'list_subscribers WHERE ' . $domain . ' listid IN(' . $lids . ') AND unsubscribeconfirmed=1' );
					$result = $this->db->Query( $query );
					$return = array();

					while ($row = $this->db->Fetch( $result )) {
						file_put_contents( $dir . '/unsubscribed.csv', $row['emailaddress'] . PHP_EOL, FILE_APPEND );
					}
				}
			} else {
				if ($data['type'] == 'lists') {
					$d = $data['users_lists'][$userid];
					$custom = (isset( $data['custom'] ) ? true : false);

					foreach ($d as $k => $v) {
						if (isset( $data['active'] )) {
							$cf = $this->custom_fields( $v['listid'] );
							$injected = array( 'emailaddress' );
							$csv_file = $dir . '/' . $this->name_encode( $v['name'] ) . '_active.csv';
							$query = 'SELECT subscriberid, emailaddress FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'list_subscribers WHERE ' . $domain . ' listid IN(' . $v['listid'] . ') AND unsubscribeconfirmed=0' );
							$result = $this->db->Query( $query );
							$return = array();
							$this->write_csv_header( $injected, ($custom ? $cf : array()), $csv_file );

							while ($row = $this->db->Fetch( $result )) {
								$this->write_csv_body( $injected, ($custom ? $this->custom_fields_value( $cf, $row['subscriberid'] ) : array()), $row, $csv_file );
							}
						}


						if (isset( $data['unsubscribed'] )) {
							$cf = $this->custom_fields( $v['listid'] );
							$injected = array( 'emailaddress' );
							$csv_file = $dir . '/' . $this->name_encode( $v['name'] ) . '_unsubscribed.csv';
							$query = 'SELECT subscriberid, emailaddress, domainname, unsubscribed FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'list_subscribers WHERE ' . $domain . ' listid IN(' . $v['listid'] . ') AND unsubscribeconfirmed=1' );
							$result = $this->db->Query( $query );
							$return = array();
							$this->write_csv_header( $injected, ($custom ? $cf : array()), $csv_file );

							while ($row = $this->db->Fetch( $result )) {
								$this->write_csv_body( $injected, ($custom ? $this->custom_fields_value( $cf, $row['subscriberid'] ) : array()), $row, $csv_file );
							}

							continue;
						}
					}

					return null;
				}


				if ($data['type'] == 'forms') {
					$forms = $this->forms( $userid );
					$api_file = SENDSTUDIO_API_DIRECTORY . '/forms.php';
					require_once( $api_file );
					$f = new Forms_API();
					foreach ($forms as $k => $v) {
						$loaded = $f->Load( $v['formid'] );

						if ($loaded) {
							$fhtml = $f->GetHTML();
							$html_file = $dir . '/' . $this->name_encode( $v['name'] ) . '.html';
							$this->write_file( $html_file, $fhtml );
							continue;
						}
					}

					return null;
				}


				if ($data['type'] == 'campaigns') {
					$d = $data['users_campaigns'][$userid];
					foreach ($d as $k => $v) {
						$query = 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'newsletters WHERE newsletterid=' . $v['newsletterid'] );
						$result = $this->db->Query( $query );

						while ($row = $this->db->Fetch( $result )) {
							$html_file = $dir . '/' . $this->name_encode( $v['name'] ) . ' (' . $this->name_encode( $v['subject'] ) . ').html';
							$this->write_file( $html_file, $row['htmlbody'] );
							continue;
						}
					}

					return null;
				}


				if ($data['type'] == 'statistics') {
					$d = $data['users_stats'][$userid];
					$custom = (isset( $data['custom'] ) ? true : false);
					foreach ($d as $key => $value) {
						$dirs = $dir . '/' . $this->url_sanitizer( $value['name'] ) . '_[' . date( 'F j, Y - g_i A', $value['started'] ) . ']';
						$this->create_dir( $dirs );
						$query = 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'newsletters WHERE newsletterid=' . $value['newsletterid'] );
						$result = $this->db->Query( $query );
						
						while ($row = $this->db->Fetch( $result )) {
							$html_file = $dirs . '/campaign.html';
							$this->write_file( $html_file, $row['htmlbody'] );
						}


						if (isset( $data['active'] )) {
							$lists = $this->lists_by_stats( $value['statid'] );
							$dir_active = $dirs . '/subscribers';
							$this->create_dir( $dir_active );
							foreach ($lists as $k => $v) {
								$cf = $this->custom_fields( $v['listid'] );
								$injected = array( 'emailaddress' );
								$csv_file = $dir_active . '/' . $this->name_encode( $v['name'] ) . '.csv';
								$result = $this->db->Query( 'SELECT subscriberid, emailaddress FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'list_subscribers WHERE ' . $domain . ' listid IN(' . $v['listid'] . ') AND unsubscribeconfirmed=0' ) );
								$this->write_csv_header( $injected, ($custom ? $cf : array()), $csv_file );

								while ($row = $this->db->Fetch( $result )) {
									$this->write_csv_body( $injected, ($custom ? $this->custom_fields_value( $cf, $row['subscriberid'] ) : array()), $row, $csv_file );
								}
							}
						}


						if (isset( $data['opens'] )) {
							$lists = $this->lists_by_stats( $value['statid'] );
							$dir_active = $dirs . '/openners';
							$this->create_dir( $dir_active );
							foreach ($lists as $k => $v) {
								$cf = $this->custom_fields( $v['listid'] );
								$injected = array( 'emailaddress', 'opentime' );
								$csv_file = $dir_active . '/' . $this->name_encode( $v['name'] ) . '_unique.csv';
								$result = $this->db->Query( 'SELECT l.subscriberid, l.emailaddress, opentime FROM ' . SENDSTUDIO_TABLEPREFIX . 'list_subscribers l, ' . SENDSTUDIO_TABLEPREFIX . ( ( 'stats_emailopens o WHERE ' . $share . ' ' ) . $domain . ' l.subscriberid=o.subscriberid AND l.listid=' . $v['listid'] . ' AND o.statid IN(' . $value['statid'] . ') GROUP BY l.emailaddress' ) );
								$this->write_csv_header( $injected, ($custom ? $cf : array()), $csv_file );

								while ($row = $this->db->Fetch( $result )) {
									$this->write_csv_body( $injected, ($custom ? $this->custom_fields_value( $cf, $row['subscriberid'] ) : array()), $row, $csv_file );
								}

								$csv_file = $dir_active . '/' . $this->name_encode( $v['name'] ) . '.csv';
								$result = $this->db->Query( 'SELECT l.subscriberid, l.emailaddress, opentime FROM ' . SENDSTUDIO_TABLEPREFIX . 'list_subscribers l, ' . SENDSTUDIO_TABLEPREFIX . ( ( 'stats_emailopens o WHERE ' . $share . ' ' ) . $domain . ' l.subscriberid=o.subscriberid AND l.listid=' . $v['listid'] . ' AND o.statid IN(' . $value['statid'] . ')' ) );
								$this->write_csv_header( $injected, ($custom ? $cf : array()), $csv_file );

								while ($row = $this->db->Fetch( $result )) {
									$this->write_csv_body( $injected, ($custom ? $this->custom_fields_value( $cf, $row['subscriberid'] ) : array()), $row, $csv_file );
								}
							}
						}


						if (isset( $data['bounced'] )) {
							$lists = $this->lists_by_stats( $value['statid'] );
							$dir_active = $dirs . '/bounces';
							$this->create_dir( $dir_active );
							foreach ($lists as $k => $v) {
								$cf = $this->custom_fields( $v['listid'] );
								$injected = array( 'emailaddress', 'bouncetime', 'bouncerule', 'bouncetype' );
								$csv_file = $dir_active . '/' . $this->name_encode( $v['name'] ) . '.csv';
								$result = $this->db->Query( 'SELECT l.subscriberid, l.emailaddress, bouncetime, bouncerule, bouncetype FROM ' . SENDSTUDIO_TABLEPREFIX . 'list_subscribers l, ' . SENDSTUDIO_TABLEPREFIX . ( 'list_subscriber_bounces b WHERE ' . $domain . ' l.listid=' . $v['listid'] . ' AND l.subscriberid=b.subscriberid AND b.statid IN(' . $value['statid'] . ')' ) );
								$this->write_csv_header( $injected, ($custom ? $cf : array()), $csv_file );

								while ($row = $this->db->Fetch( $result )) {
									$this->write_csv_body( $injected, ($custom ? $this->custom_fields_value( $cf, $row['subscriberid'] ) : array()), $row, $csv_file );
								}
							}
						}


						if (isset( $data['unsubscribed'] )) {
							$lists = $this->lists_by_stats( $value['statid'] );
							$dir_active = $dirs . '/unsubscribes';
							$this->create_dir( $dir_active );
							foreach ($lists as $k => $v) {
								$cf = $this->custom_fields( $v['listid'] );
								$injected = array( 'emailaddress', 'unsubscribetime' );
								$csv_file = $dir_active . '/' . $this->name_encode( $v['name'] ) . '.csv';
								$result = $this->db->Query( 'SELECT l.subscriberid, l.emailaddress, unsubscribetime FROM ' . SENDSTUDIO_TABLEPREFIX . 'list_subscribers l, ' . SENDSTUDIO_TABLEPREFIX . ( 'list_subscribers_unsubscribe lsu WHERE ' . $domain . ' l.listid=' . $v['listid'] . ' AND l.subscriberid=lsu.subscriberid AND lsu.statid IN (' . $value['statid'] . ') ORDER BY l.emailaddress' ) );
								$this->write_csv_header( $injected, ($custom ? $cf : array()), $csv_file );

								while ($row = $this->db->Fetch( $result )) {
									$this->write_csv_body( $injected, ($custom ? $this->custom_fields_value( $cf, $row['subscriberid'] ) : array()), $row, $csv_file );
								}
							}
						}


						if (isset( $data['clicks'] )) {
							$lists = $this->lists_by_stats( $value['statid'] );
							$dir_active = $dirs . '/clicks';
							$this->create_dir( $dir_active );
							foreach ($lists as $k => $v) {
								$cf = $this->custom_fields( $v['listid'] );
								$injected = array( 'emailaddress', 'time', 'clickip', 'url' );
								$csv_file = $dir_active . '/' . $this->name_encode( $v['name'] ) . '.csv';
								$result = $this->db->Query( 'SELECT l.subscriberid, l.emailaddress, clicktime as time, clickip, url FROM ' . SENDSTUDIO_TABLEPREFIX . 'list_subscribers l, ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_linkclicks lc, email_links ml WHERE ' . $domain . ' l.listid=' . $v['listid'] . ' AND ml.linkid=lc.linkid AND l.subscriberid=lc.subscriberid AND lc.statid IN(' . $value['statid'] . ') ORDER BY l.emailaddress' ) );
								$this->write_csv_header( $injected, ($custom ? $cf : array()), $csv_file );

								while ($row = $this->db->Fetch( $result )) {
									$this->write_csv_body( $injected, ($custom ? $this->custom_fields_value( $cf, $row['subscriberid'] ) : array()), $row, $csv_file );
								}
							}

							continue;
						}
					}
				}
			}

		}

		function name_encode($str) {
			return utf8_decode( $str );
		}

		function url_sanitizer($str) {
			$str = $this->wp_sanitizer( $str );

			if (25 < strlen( $str )) {
				$str = substr( $str, 0, 25 );
			}

			return $str;
		}

		function wp_sanitizer($str, $charset = 'UTF-8', $space = false) {
			$sep = '-';

			if (!$space) {
				$chars = array( '.' => '.', 'ÃƒÂ¡' => 'a', 'ÃƒÂ ' => 'a', 'ÃƒÂ¢' => 'a', 'ÃƒÂ¤' => 'a', 'Ãƒï¿½' => 'A', 'Ãƒâ‚¬' => 'A', 'Ãƒâ€š' => 'A', 'Ãƒâ€ž' => 'A', 'ÃƒÂ©' => 'e', 'ÃƒÂ¨' => 'e', 'ÃƒÂª' => 'e', 'ÃƒÂ«' => 'e', 'Ãƒâ€°' => 'E', 'ÃƒË†' => 'E', 'ÃƒÅ ' => 'E', 'Ãƒâ€¹' => 'E', 'ÃƒÂ­' => 'i', 'ÃƒÂ¬' => 'i', 'ÃƒÂ®' => 'i', 'ÃƒÂ¯' => 'i', 'Ãƒï¿½' => 'I', 'ÃƒÅ’' => 'I', 'ÃƒÅ½' => 'I', 'Ãƒï¿½' => 'I', 'ÃƒÂ³' => 'o', 'ÃƒÂ²' => 'o', 'ÃƒÂ´' => 'o', 'ÃƒÂ¶' => 'o', 'Ãƒâ€œ' => 'O', 'Ãƒâ€™' => 'O', 'Ãƒâ€�' => 'O', 'Ãƒâ€“' => 'O', 'ÃƒÂº' => 'u', 'ÃƒÂ¹' => 'u', 'ÃƒÂ»' => 'u', 'ÃƒÂ¼' => 'u', 'ÃƒÅ¡' => 'U', 'Ãƒâ„¢' => 'U', 'Ãƒâ€º' => 'U', 'ÃƒÅ“' => 'U', 'ÃƒÂ±' => 'n', '$' => 's', '?' => '', '&' => '', 'Ãƒâ€˜' => 'N', '$' => 'S', 'Ã‚Â¿' => '', '&' => '', 'ÃƒÂ§' => 'c', 'ÃƒÂ½' => 'y', 'Ãƒâ€¡' => 'C', 'Ãƒï¿½' => 'Y', '<' => '', '>' => '' );
			} else {
				$chars = array( ' ' => ' ', '.' => '.', 'ÃƒÂ¡' => 'a', 'ÃƒÂ ' => 'a', 'ÃƒÂ¢' => 'a', 'ÃƒÂ¤' => 'a', 'Ãƒï¿½' => 'A', 'Ãƒâ‚¬' => 'A', 'Ãƒâ€š' => 'A', 'Ãƒâ€ž' => 'A', 'ÃƒÂ©' => 'e', 'ÃƒÂ¨' => 'e', 'ÃƒÂª' => 'e', 'ÃƒÂ«' => 'e', 'Ãƒâ€°' => 'E', 'ÃƒË†' => 'E', 'ÃƒÅ ' => 'E', 'Ãƒâ€¹' => 'E', 'ÃƒÂ­' => 'i', 'ÃƒÂ¬' => 'i', 'ÃƒÂ®' => 'i', 'ÃƒÂ¯' => 'i', 'Ãƒï¿½' => 'I', 'ÃƒÅ’' => 'I', 'ÃƒÅ½' => 'I', 'Ãƒï¿½' => 'I', 'ÃƒÂ³' => 'o', 'ÃƒÂ²' => 'o', 'ÃƒÂ´' => 'o', 'ÃƒÂ¶' => 'o', 'Ãƒâ€œ' => 'O', 'Ãƒâ€™' => 'O', 'Ãƒâ€�' => 'O', 'Ãƒâ€“' => 'O', 'ÃƒÂº' => 'u', 'ÃƒÂ¹' => 'u', 'ÃƒÂ»' => 'u', 'ÃƒÂ¼' => 'u', 'ÃƒÅ¡' => 'U', 'Ãƒâ„¢' => 'U', 'Ãƒâ€º' => 'U', 'ÃƒÅ“' => 'U', 'ÃƒÂ±' => 'n', '$' => 's', '?' => '', '&' => '', 'Ãƒâ€˜' => 'N', '$' => 'S', 'Ã‚Â¿' => '', '&' => '', 'ÃƒÂ§' => 'c', 'ÃƒÂ½' => 'y', 'Ãƒâ€¡' => 'C', 'Ãƒï¿½' => 'Y', '<' => '', '>' => '' );
			}


			if (!function_exists( 'mb_strtolower' )) {
				$str = strtolower( $str );
			} else {
				$str = mb_strtolower( $str, $charset );
			}

			foreach ($chars as $no => $yes) {
				$str = str_replace( $no, $yes, $str );
			}

			$str = preg_replace( '/[^a-z0-9' . implode( '', $chars ) . ']/ui', ' ', $str );
			$str = preg_replace( '/\s+/', $sep, str_replace( '+', ' ', $str ) );
			$str = trim( strip_tags( urldecode( $str ) ) );
			$str = preg_replace( ( '/(^' . $sep . '|' ) . $sep . '$)/', '', str_replace( ' ', $sep, $str ) );
			return $str;
		}

		function write_csv_body($injected, $cf, $data, $file) {
			foreach ($data as $k => $v) {

				if (in_array( $k, $injected )) {
					$c1[] = $this->custom_fields_modify( $k, $v );
					continue;
				}
			}

			array_merge( $c1, $cf );
			$csvdata = $c1 = array();
			$this->write_csv( $file, $csvdata );
		}

		function custom_fields_modify($type, $value) {
			if ($type == 'opentime') {
				$value = date( 'F j Y, g:i a', $value );
			} else if ($type == 'bouncetime') {
				$value = date( 'F j Y, g:i a', $value );
			} else if ($type == 'unsubscribetime') {
				$value = date( 'F j Y, g:i a', $value );
			} else if ($type == 'time') {
				$value = date( 'F j Y, g:i a', $value );
			}

			return $value;
		}

		function write_csv_header($injected, $cf, $file) {
			$cfn = $this->custom_fields_name( $cf );
			$cfh = array_merge( $this->csv_header_language( $injected ), $cfn );
			$this->write_csv( $file, $cfh );
		}

		function write_file($file, $data = '') {
			file_put_contents( $file, $data, FILE_APPEND );
		}

		function write_csv($file, $data = array(), $sep = ',', $enc = '"') {
			$c1 = count( $data );
			$line = '';
			foreach ($data as $k => $v) {
				$line .= $enc . addslashes( $v ) . $enc . ($k < $c1 - 1 ? $sep : '' . PHP_EOL);
			}

			$result = file_put_contents( $file, $line, FILE_APPEND );
		}

		function csv_header_language($injected) {
			$data = array();
			foreach ($injected as $k => $v) {
				$data[] = GetLang( 'Addon_backup_' . $v );
			}

			return $data;
		}

		function custom_fields($listid) {
			$cf = array();
			$query = 'SELECT f.fieldid, f.name FROM ' . SENDSTUDIO_TABLEPREFIX . 'customfields f, ' . SENDSTUDIO_TABLEPREFIX . ( 'customfield_lists l WHERE l.fieldid=f.fieldid AND l.listid IN (' . $listid . ') GROUP BY f.fieldid' );
			$result = $this->db->Query( $query );

			while ($row = $this->db->Fetch( $result )) {
				$cf[] = $row;
			}

			return $cf;
		}

		function custom_fields_name($cf) {
			$c = array();
			foreach ($cf as $k => $v) {
				$c[] = utf8_decode( $v['name'] );
			}

			return $c;
		}

		function custom_fields_literal($cf) {
			$c = array();
			foreach ($cf as $k => $v) {
				$c[] = $v['fieldid'];
			}

			return implode( ',', $c );
		}

		function custom_fields_value($cf, $subscriberid) {
			$cfl = $this->custom_fields_literal( $cf );
			$query = 'SELECT fieldid, data FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'subscribers_data WHERE subscriberid=' . $subscriberid . ' AND fieldid IN (' . $cfl . ')' );
			$result = $this->db->Query( $query );
			$data = $data_r = array();

			while ($row = $this->db->Fetch( $result )) {
				$data[$row['fieldid']] = $row;
			}

			foreach ($cf as $k => $v) {
				$data_r[] = (array_key_exists( $v['fieldid'], $data ) ? $data[$v['fieldid']]['data'] : '');
			}

			return $data_r;
		}

		function create_dir($dirname) {
			return mkdir( $dirname );
		}

		function delete_dir($dir) {
			if (is_dir( $dir )) {
				$objects = scandir( $dir );
				foreach ($objects as $object) {
					if (( $object != '.' && $object != '..' )) {
						if (filetype( $dir . '/' . $object ) == 'dir') {
							$this->delete_dir( $dir . '/' . $object );
							continue;
						}

						unlink( $dir . '/' . $object );
						continue;
					}
				}

				reset( $objects );
				rmdir( $dir );
			}

		}

		function get_users_by_lists($data) {
			$lists = (isset( $data['list'] ) ? $data['list'] : array());
			$list = array();
			foreach ($lists as $k => $v) {
				$list[] = (int)$v;
			}

			$listin = implode( ',', $list );
			$users = array();
			$users_lists = array();
			foreach ($list as $k => $v) {
				$result = $this->db->Query( 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'lists WHERE listid=' . $v ) );

				while ($row = $this->db->Fetch( $result )) {
					$_lid = (int)$row['listid'];
					$_user = (int)$row['ownerid'];

					if (!in_array( $_user, $users )) {
						$users[] = $_user;
						$users_lists[$_user] = array();
					}


					if (!in_array( $_lid, $users_lists[$_user] )) {
						$users_lists[$_user][] = array( 'listid' => $_lid, 'name' => $row['name'] );
						continue;
					}

					continue;
				}
			}

			$data['users'] = $users;
			$data['users_lists'] = $users_lists;
			return $data;
		}

		function get_users_by_campaigns($data) {
			$campaigns = (isset( $data['campaigns'] ) ? $data['campaigns'] : array());
			$campaign = array();
			foreach ($campaigns as $k => $v) {
				$campaign[] = (int)$v;
			}

			$listin = implode( ',', $list );
			$users = array();
			$users_campaigns = array();
			foreach ($campaign as $k => $v) {
				$result = $this->db->Query( 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'newsletters WHERE newsletterid=' . $v ) );

				while ($row = $this->db->Fetch( $result )) {
					$_nid = (int)$row['newsletterid'];
					$_user = (int)$row['ownerid'];

					if (!in_array( $_user, $users )) {
						$users[] = $_user;
						$users_campaigns[$_user] = array();
					}


					if (!in_array( $_nid, $users_campaigns[$_user] )) {
						$users_campaigns[$_user][] = array( 'newsletterid' => $_nid, 'name' => $row['name'] );
						continue;
					}

					continue;
				}
			}

			$data['users'] = $users;
			$data['users_campaigns'] = $users_campaigns;
			return $data;
		}

		function get_users_by_statistics($data) {
			$campaigns = (isset( $data['stats'] ) ? $data['stats'] : array());
			$campaign = array();
			foreach ($campaigns as $k => $v) {
				$campaign[] = (int)$v;
			}

			$listin = implode( ',', $list );
			$users = array();
			$users_stats = array();
			foreach ($campaign as $k => $v) {
				$result = $this->db->Query( 'SELECT a.statid, a.newsletterid, a.sendsize, a.sentby as ownerid, a.starttime, b.subject, b.name FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_newsletters a LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( 'newsletters b ON a.newsletterid=b.newsletterid WHERE a.statid=' . $v . ' ORDER BY a.sendsize ASC' ) );
				
				while ($row = $this->db->Fetch( $result )) {
					$_nid = (int)$row['statid'];
					$_user = (int)$row['ownerid'];

					if (!in_array( $_user, $users )) {
						$users[] = $_user;
						$users_stats[$_user] = array();
					}


					if (!in_array( $_nid, $users_stats[$_user] )) {
						$users_stats[$_user][] = array( 'statid' => $_nid, 'name' => $row['name'], 'started' => $row['starttime'], 'newsletterid' => $row['newsletterid'] );
						continue;
					}

					continue;
				}
			}

			$data['users'] = $users;
			$data['users_stats'] = $users_stats;
			return $data;
		}

		function get_users_by_forms($data) {
			$campaigns = (isset( $data['forms'] ) ? $data['forms'] : array());
			$campaign = array();
			foreach ($campaigns as $k => $v) {
				$campaign[] = (int)$v;
			}

			$users = array();
			$users_campaigns = array();
			foreach ($campaign as $k => $v) {
				$result = $this->db->Query( 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'forms WHERE formid=' . $v ) );
				
				while ($row = $this->db->Fetch( $result )) {
					$_nid = (int)$row['formid'];
					$_user = (int)$row['ownerid'];

					if (!in_array( $_user, $users )) {
						$users[] = $_user;
						$users_campaigns[$_user] = array();
					}


					if (!in_array( $_nid, $users_campaigns[$_user] )) {
						$users_campaigns[$_user][] = array( 'formid' => $_nid, 'name' => $row['name'] );
						continue;
					}

					continue;
				}
			}

			$data['users'] = $users;
			$data['users_forms'] = $users_campaigns;
			return $data;
		}

		function get_username_by_id($userid) {			
			$userid = $this->db->Quote( $userid );
			return $this->db->Fetch( $this->db->Query( 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'users WHERE userid=' . $userid ) ) );
		}

		function lists($userid) {
			$result = $this->db->Query( 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'lists WHERE ownerid=' . $userid ) );
			$lists = array();

			while ($row = $this->db->Fetch( $result )) {
				$row['subscribecount_f'] = number_format( $row['subscribecount'] );
				$lists[] = $row;
			}

			return $lists;
		}

		function lists_by_stats($statid) {
			$result = $this->db->Query( 'SELECT a.listid, b.name from ' . SENDSTUDIO_TABLEPREFIX . 'stats_newsletter_lists a LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( 'lists b ON a.listid=b.listid WHERE a.statid=' . $statid ) );
			$lists = array();

			while ($row = $this->db->Fetch( $result )) {
				$lists[] = $row;
			}

			return $lists;
		}

		function lists_ids($userid) {
			$listids = array();
			$lists = $this->lists( $userid );
			foreach ($lists as $k => $v) {
				$listids[] = $v['listid'];
			}

			return $listids;
		}

		function user_subscribers_count($userid) {
			$result = $this->db->Query( 'SELECT SUM(subscribecount) subscribers FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'lists WHERE ownerid=' . $userid ) );
			$row = $this->db->Fetch( $result );
			return (int)$row['subscribers'];
		}

		function users() {
			$query = 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . 'users';
			$result = $this->db->Query( $query );
			$users = array();

			while ($row = $this->db->Fetch( $result )) {
				$users[] = $row;
			}

			return $users;
		}

		function users_subscribers() {
			$data = array();
			$users = $this->users();
			foreach ($users as $k => $v) {
				$u = array();
				$u['user'] = $v;
				$u['info'] = $this->user_subscribers_info( $v['userid'] );
				$data[] = $u;
			}

			return $data;
		}

		function user_subscribers_info($userid) {
			$sn = $this->user_subscribers_count( $userid );
			return array( 'subscribers' => $sn, 'subscribers_f' => number_format( $sn ) );
		}

		function users_lists() {
			$data = array();
			$users = $this->users();
			foreach ($users as $k => $v) {
				$u = array();
				$u['user'] = $v;
				$u['lists'] = $this->lists( $v['userid'] );
				$data[] = $u;
			}

			return $data;
		}

		function users_campaigns() {
			$data = array();
			$users = $this->users();
			foreach ($users as $k => $v) {
				$u = array();
				$u['user'] = $v;
				$u['campaigns'] = $this->campaigns( $v['userid'] );
				$data[] = $u;
			}

			return $data;
		}

		function campaigns($userid) {
			$campaigns = array();
			$result = $this->db->Query( 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'newsletters WHERE ownerid=' . $userid ) );

			while ($row = $this->db->Fetch( $result )) {
				$campaigns[] = $row;
			}

			return $campaigns;
		}

		function users_stats() {
			$data = array();
			$users = $this->users();
			foreach ($users as $k => $v) {
				$u = array();
				$u['user'] = $v;
				$u['stats'] = $this->stats( $v['userid'] );
				$data[] = $u;
			}

			return $data;
		}

		function stats($userid) {
			$result = $this->db->Query( 'SELECT a.statid, a.newsletterid, a.sendsize, a.sentby as ownerid, b.subject, b.name FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_newsletters a LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( 'newsletters b ON a.newsletterid=b.newsletterid WHERE ownerid=' . $userid . ' ORDER BY a.sendsize ASC' ) );
			$stats = array();

			while ($row = $this->db->Fetch( $result )) {
				$row['sendsize_f'] = number_format( $row['sendsize'] );
				$stats[] = $row;
			}

			return $stats;
		}

		function users_forms() {
			$data = array();
			$users = $this->users();
			foreach ($users as $k => $v) {
				$u = array();
				$u['user'] = $v;
				$u['forms'] = $this->forms( $v['userid'] );
				$data[] = $u;
			}

			return $data;
		}

		function forms($userid, $all = false) {
			$aq = (!$all ? 'GROUP BY b.formid' : '');
			$result = $this->db->Query( 'SELECT a.ownerid, a.formid, a.name, b.html, b.pagetype FROM ' . SENDSTUDIO_TABLEPREFIX . 'forms a LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( ( 'form_pages b ON a.formid=b.formid WHERE ownerid=' . $userid . ' ' ) . $aq ) );
			$stats = array();

			while ($row = $this->db->Fetch( $result )) {
				$stats[] = $row;
			}

			return $stats;
		}
	}

?>