<?php

/******************************************************************************
 *  
 *  PROJECT: Flynax Classifieds Software
 *  VERSION: 4.5.2
 *  LICENSE: FL4HLD2A8696 - http://www.flynax.com/license-agreement.html
 *  PRODUCT: Real Estate Classifieds
 *  DOMAIN: savingrack.com
 *  FILE: INDEX.PHP
 *  
 *  The software is a commercial product delivered under single, non-exclusive,
 *  non-transferable license for one domain or IP address. Therefore distribution,
 *  sale or transfer of the file in whole or in part without permission of Flynax
 *  respective owners is considered to be illegal and breach of Flynax License End
 *  User Agreement.
 *  
 *  You are not allowed to remove this information from the file without permission
 *  of Flynax respective owners.
 *  
 *  Flynax Classifieds Software 2017 | All copyrights reserved.
 *  
 *  http://www.flynax.com/
 ******************************************************************************/

define('CRON_FILE', true);

set_time_limit(0);

/* load configs */
include_once(dirname(__FILE__) . "/../includes/config.inc.php");

$statistics = array();

/* system controller */
require_once(RL_INC . 'control.inc.php');

/* load system configurations */
$config = $rlConfig->allConfig();
$rlSmarty->assign_by_ref('config', $config);
$GLOBALS['config'] = $config;

/* get date format */
$date_format = $rlDb->fetch(array('Date_format'), array('Code' => $config['lang']), null, 1, 'languages', 'row');
$date_format = str_replace('%', '', $date_format['Date_format']);

define('RL_LANG_CODE', $config['lang']);

/* prefere email templates */
$reefless->loadClass('Mail');
$reefless->loadClass('Listings');
$reefless->loadClass('Categories');
$reefless->loadClass('Actions');
$reefless->loadClass('ListingTypes', null, false, true);
$reefless->loadClass('Cache');
$reefless->loadClass('Account');
$reefless -> loadClass('MembershipPlan');

if (file_exists(RL_CLASSES . 'rlEscort.class.php')) {
	$reefless->loadClass('Escort');
}

/*get languages list*/
$languages = $rlLang->getLanguagesList();

/* get page paths */
$rlDb->setTable('pages');
$pages_tmp = $rlDb->fetch(array( 'Key', 'Path'));
foreach ($pages_tmp as $page_tmp) {
	$pages[$page_tmp['Key']] = $page_tmp['Path'];
}
unset($pages_tmp);

$update_page = $rlDb->fetch(array('Path'), array('Key' => 'upgrade_listing'), null, 1, 'pages', 'row');

/* listing expiration: changing status of listing to expired, send expiration email */
$actions[] = 'expired';

/* sending pre expiration listing notification, send pre expiration email  */
$actions[] = 'pre_expired';

/* listing featured status expiration */
$actions[] = 'featured';

/* listing featured status pre expiration */
$actions[] = 'pre_featured';

/* this action performed 2 times, first run send notification to user. 2nd run deleting listings */
$actions[] = 'check_incompleted';

if ($config['cron_expired_remove']) {
    $actions[] = 'delete_expired_notify';
	$actions[] = 'delete_expired';
}

foreach($languages as $key => $language) {
	$mail_expired[$key] = $rlMail->getEmailTemplate('cron_listing_expired2', $language['Code']);
	$mail_pre_expired[$key] = $rlMail->getEmailTemplate('cron_listing_pre_expired2', $language['Code']);
	$mail_featured[$key] = $rlMail->getEmailTemplate('cron_featured_status_expired2', $language['Code']);
	$mail_pre_featured[$key] = $rlMail->getEmailTemplate('cron_featured_status_pre_expired2', $language['Code']);
	$mail_check_incompleted[$key] = $rlMail->getEmailTemplate('cron_incomplete_listing2', $language['Code']);
    $mail_check_ac_incompleted[$key] = $rlMail->getEmailTemplate('cron_incomplete_account', $language['Code']);
}

foreach($actions as $ak => $action) {
	$tosend = performAction($action); // do action: database fields update, collecting emails etc.
	if ($tosend) {
		sendEmails($tosend, $action); //send emails
	}
}
$tosend = handleIncompleteAccounts();
if ($tosend) {
    sendEmails($tosend, 'check_ac_incompleted'); //send emails    
}

/**
* perform action
*
* @param string $action - performed action
*
**/

function performAction($action) {
	global $rlDb, $rlActions, $rlSmarty, $rlCategories, $rlListings, $config, $pages, $statistics;

	$limit = $GLOBALS['config']['listings_number'];
	$run = true;
	$select = $join = $where = '';

	switch ($action) {
		case "expired":
			$join ="LEFT JOIN `".RL_DBPREFIX."listing_plans` AS `T4` ON `T1`.`Plan_ID` = `T4`.`ID`";

			$where .= "(";
			$where .= " TIMESTAMPDIFF(HOUR, `T1`.`Pay_date`, NOW()) > `T4`.`Listing_period` * 24 "; //round to hour
			$where .= " AND `T4`.`Listing_period` != 0 ) ";
			$where .= "AND `T1`.`Status` <> 'expired' AND `T1`.`Status` <> 'incomplete' ";

			$action_update['Pay_date'] = '';
			$action_update['Status'] = 'expired';
			$action_update['Cron_notified'] = '0';
			break;

		case "pre_expired";
			$join ="LEFT JOIN `".RL_DBPREFIX."listing_plans` AS `T4` ON `T1`.`Plan_ID` = `T4`.`ID`";
			$select = "`T4`.`Listing_period`, `T4`.`Listing_period` - TIMESTAMPDIFF(DAY, `T1`.`Pay_date`, NOW()) AS `Pre_days`";

			$where .= "(";
			$where .= " TIMESTAMPDIFF(HOUR, `T1`.`Pay_date`, NOW()) > ((`T4`.`Listing_period` - ".$config['pre_days'].") * 24) "; //round to hour
			$where .= " AND `T4`.`Listing_period` != 0 ";
			$where .= ") ";
			$where .= "AND `T1`.`Cron_notified` != '1' ";
			$where .= "AND `T1`.`Status` <> 'expired' AND `T1`.`Status` <> 'incomplete' ";
			$action_update['Cron_notified'] = '1';
			break;

		case "featured":
			$join ="LEFT JOIN `".RL_DBPREFIX."listing_plans` AS `T4` ON `T1`.`Featured_ID` = `T4`.`ID`";
            $select = "`T4`.`Listing_period`, UNIX_TIMESTAMP(`T1`.`Featured_date`) AS `Featured_date_unix`";

			$where = "UNIX_TIMESTAMP(DATE_ADD(`T1`.`Featured_date`, INTERVAL `T4`.`Listing_period` DAY)) < UNIX_TIMESTAMP(NOW()) ";
            $where .= "AND  `T4`.`Listing_period` != 0 ";
			$where .= "AND `T1`.`Featured_date` IS NOT NULL ";
			$where .= "AND `T1`.`Status` <> 'expired' AND `T1`.`Status` <> 'incomplete' ";
			$where .= "AND `T4`.`Type` = 'featured' ";
			$action_update['Featured_date'] = '';
			break;

		case "pre_featured":
			$join ="LEFT JOIN `".RL_DBPREFIX."listing_plans` AS `T4` ON `T1`.`Featured_ID` = `T4`.`ID`";
            $select = "`T4`.`Listing_period`, UNIX_TIMESTAMP(`T1`.`Featured_date`) AS `Featured_date_unix`, `T4`.`Listing_period` - TIMESTAMPDIFF(DAY, `T1`.`Featured_date`, NOW()) AS `Pre_days`";            

			$where ="UNIX_TIMESTAMP(DATE_ADD(`T1`.`Featured_date`, INTERVAL (`T4`.`Listing_period`- '".$config['pre_days']."') DAY)) < UNIX_TIMESTAMP(NOW()) ";
            $where .= "AND  `T4`.`Listing_period` != 0 ";
			$where .="AND `T1`.`Featured_date` IS NOT NULL AND `T1`.`Cron_featured` != '1' ";
			$where .= "AND `T1`.`Status` <> 'expired' AND `T1`.`Status` <> 'incomplete' ";
			$where .="AND `T4`.`Type` = 'featured' ";

			$action_update['Cron_featured'] = '1';
			break;
			
		/* incomplete listings checking */
		case "check_incompleted":	
            $notify_days = $config['cron_incompl_listings_notify_days'];
			if( empty($notify_days) )
			{
				$notify_days = 1;
			}
			
			$select = "UNIX_TIMESTAMP(`T1`.`Date`) AS `Date_unix`";
			$where = "UNIX_TIMESTAMP(DATE_ADD(`T1`.`Date`, INTERVAL {$notify_days} DAY)) < UNIX_TIMESTAMP(NOW()) ";
			$where .= "AND `T1`.`Status` = 'incomplete' ";

			$action_update['Last_show'] = 'NOW()';
			$action_update['Cron'] = '1';
			$action_update['Cron_notified'] = '1';
			break;
		case "delete_expired_notify":
			$where .= "`T1`.`Status` = 'expired' AND `T1`.`Cron_notified` != '1' ";
			$action_update['Cron_notified'] = '1';
            break;
        case "delete_expired":
			$where .= "`T1`.`Status` = 'expired' AND `T1`.`Cron_notified` = '1' ";
            $where .= "AND `T1`.`Loc_address` != '' AND DATE_ADD(FROM_UNIXTIME(`T1`.`Loc_address`), INTERVAL 365 DAY) < NOW() ";
		  break;
	}
	
	$first = true;
	while (count($listings) == $limit || $first) {
		$first = false;
		$send_mail = true;

		$sql = "SELECT `T1`.*, `T2`.`Lang`, UNIX_TIMESTAMP(`T1`.`Pay_date`) AS `Pay_date`, `T3`.`Type` AS `Listing_type`, ";
		$sql .= $select ? $select .", " : "";
		$sql .= "`T2`.`Mail`, `T2`.`First_name`, `T2`.`Last_name`, `T2`.`Username`, `T3`.`Path` ";
		$sql .= "FROM `".RL_DBPREFIX."listings` AS `T1` ";
		$sql .= "JOIN `".RL_DBPREFIX."accounts` AS `T2` ON `T1`.`Account_ID` = `T2`.`ID` ";
		$sql .= "JOIN `".RL_DBPREFIX."categories` AS `T3` ON `T1`.`Category_ID` = `T3`.`ID` ";
		$sql .= $join;
		$sql .= " WHERE `T1`.`Status` <> 'trash' ";
		$sql .= $where ? "AND ".$where : "";
		
		$sql .="GROUP BY `T1`.`ID` ";
		$sql .= "LIMIT 0, {$limit}";

		$listings = $rlDb->getAll($sql);

		foreach($listings as $key => $listing) {
			$listing_type = $GLOBALS['rlListingTypes']->types[$listing['Listing_type']];
			$listing_title = $rlListings->getListingTitle($listing['Category_ID'], $listing, $listing_type['Key']);

			$update[$key]['fields'] = $action_update;
			$update[$key]['where']['ID'] = $listing['ID'];

			if ($action == 'expired') {
				$rlCategories->listingsDecrease($listings[$key]['Category_ID']);
				$rlCategories->accountListingsDecrease($listings[$key]['Account_ID']);
				
				if (!empty($listing['Crossed'])) {
					$crossed_cats = explode(',', trim($listing['Crossed'], ','));
					foreach ($crossed_cats as $crossed_cat_id) {
						$rlCategories->listingsDecrease($crossed_cat_id);
					}
				}
			}

            if ($action == 'delete_expired_notify') {
                $update[$key]['fields']['Loc_address'] = time(); //save current time to calculate remove date
                $update[$key]['where']['ID'] = $listing['ID'];
            } elseif ($action == 'delete_expired') {                
					$rlListings->deleteListingData($listing['ID']);
					$rlDb->query("DELETE FROM `". RL_DBPREFIX ."listings` WHERE `ID` = '{$listing['ID']}' LIMIT 1");

					$statistics['expired_listings_removed']++;
				}

			if ($action == 'check_incompleted') {
				if ($listing['Cron_notified']) {
					if ($listing['Date_unix'] + ($config['cron_incomplete_listing_days'] * 86400) <= time()) {
						$rlListings->deleteListingData($listing['ID']);
						$rlDb->query("DELETE FROM `". RL_DBPREFIX ."listings` WHERE `ID` = '{$listing['ID']}' LIMIT 1");

						$statistics['incomplete_listings_removed']++;
					} else {
						$statistics['incomplete_listings_waiting']++;
					}
					$send_email = false;
				} else {
					$statistics['incomplete_listings_notified']++;
				}
			}

			if ($send_mail) { //collect emails to send them later
				if (!empty($listing['First_name']) || !empty($listing['Last_name'])) {
					$username = $listing['First_name'] .' '. $listing['Last_name'];
				} else {
					$username = $listing['Username'];
				}

				$update_link = RL_URL_HOME;
				$details_link = $GLOBALS['reefless']->url('listing', $listing);

				switch ($action) {
					case "pre_expired":
						$expire_date = $listing['Pay_date'] + ($listing['Listing_period'] * 86400);
						$expire_date = date(str_replace('b', 'M', $GLOBALS['date_format']), $expire_date);

						$afind[] = '{expire_date}';
						$areplace[] = $expire_date;
					case "expired":
						$update_link .= $config['mod_rewrite'] ? $pages['upgrade_listing'] . '.html?id=' . $listing['ID'] : $update_page['Path'] . '?page='.$update_page['Path'].'&amp;id=' . $listing['ID'];
						$update_text = $GLOBALS['lang']['renew'];

						$afind[] = '{remove_days}';
						$areplace[] = $config['cron_expired_listing_days'];
					break;
					case "featured":
					case "pre_featured":
                        $expire_date = $listing['Featured_date_unix'] + ($listing['Listing_period'] * 86400);
                        $expire_date = date(str_replace('b', 'M', $GLOBALS['date_format']), $expire_date);

                        $afind[] = '{expire_date}';
                        $areplace[] = $expire_date;

						$update_link .= $config['mod_rewrite'] ? $pages['upgrade_listing'] .'/featured.html?id='. $listing['ID'] : '?page='. $pages['upgrade_listing'] .'&amp;id=' . $listing['ID'] . '&amp;featured';
						$update_text = $GLOBALS['lang']['update'];
					break;
					case "check_incompleted":
						$details_link = RL_URL_HOME;
						$details_link .= $config['mod_rewrite'] ? $pages[$listing_type['My_key']] .'.html?incomplete='. $listing['ID'] .'&step='. $listing['Last_step']: '?page='. $pages[$listing_type['My_key']] .'&amp;incomplete=' . $listing['ID'] . '&step='. $listing['Last_step'];
						$update_link .= $config['mod_rewrite'] ? $pages['listing_remove'] .'.html?id='. $listing['ID'] .'&hash='. $hash : '?page='. $pages['listing_remove'] .'&amp;id=' . $listing['ID'] . '&hash='. $hash;
						$update_text = $GLOBALS['lang']['remove'];
		
						$afind[] = '{number_days}';
						$areplace[] = $config['cron_incomplete_listing_days'];
					break;
				}

                if ($update_text) {
				$afind[] = '{update_text}';
				$areplace[] = $update_text;
                }
				
				$tosend[$listing['Mail']]['title'] = $listing_title;
				$tosend[$listing['Mail']]['update_links'][] = $update_link;
				$tosend[$listing['Mail']]['details_links'][] = $details_link;
				$tosend[$listing['Mail']]['expire_date'][] = $expire_date;
				$tosend[$listing['Mail']]['username'] = $username;
				$tosend[$listing['Mail']]['user_lang'] = $listing['Lang'];
				$tosend[$listing['Mail']]['account_id'] = $listing['Account_ID'];
                $tosend[$listing['Mail']]['pre_days'] = (int) $listing['Pre_days'];

				$tosend[$listing['Mail']]['afind'] = $afind;
				$tosend[$listing['Mail']]['areplace'] = $areplace;
			}
		}
        
        $statistics[$action] += count($listings);

		if (!empty($update)) {
			$rlActions->update($update, 'listings');
			unset($update);
		}
	}

	return $tosend;
}


/**
*
* send emails
*
* @param string $tosend - array with emails information
*
* @param string $action - performed action
*
**/

function sendEmails($tosend, $action) {
    global $rlMail;
    
    foreach ($tosend as $email => $row) {
        $email_tpl = $GLOBALS['mail_'.$action][ $row['user_lang'] ? $row['user_lang'] : $GLOBALS['config']['lang'] ];

        if ($email_tpl && $email_tpl['body'] && $email_tpl['subject']) {
            $current = count($row['details_links']) > 1 ? "many" : "one";
            $other = $current == "many" ? "one" : "many";

            /* remove other case, e.g: if many listings remove one listing case completely  */
            $email_tpl['body'] = preg_replace('/\{if '.$other.'\}(((?!\{\/if\}).)*){\/if\}/smi', '', $email_tpl['body']);
            $email_tpl['subject'] = preg_replace('/\{if '.$other.'\}(((?!\{\/if\}).)*){\/if\}/smi', '', $email_tpl['subject']);

            /* replace actual condition, leave only text itself */
            $email_tpl['body'] = preg_replace('/\{if '.$current.'\}(((?!\{\/if\}).)*){\/if\}/smi', '$1', $email_tpl['body']);
            $email_tpl['subject'] = preg_replace('/\{if '.$current.'\}(((?!\{\/if\}).)*){\/if\}/smi', '$1', $email_tpl['subject']);
            
            if ($current == "many") { 
                $links_table = "<table>";
                foreach ($row['details_links'] as $key => $link) {
                    $links_table .= '<tr><td><a href="'.$link.'">'. $link . /*$row['title'] .*/ '</a></td>';
                    $links_table .= '<td style="padding-left:20px"><a href="'.$row['update_links'][$key].'">{update_text}</a></td>';
                    
                    if ($row['expire_date'][$key]) {
                        $links_table .='<td>'.$GLOBALS['lang']['expire_date'].': '.$row['expire_date'][$key].'</td>';
                    }
                    $links_table .='</tr>';
                }
                $links_table .= "</table>";
            } else {
                $update_link = '<a href="'.$row['update_links'][0].'">{update_text}</a>';
                $details_link = '<a href="'.$row['details_links'][0].'">'.$row['details_links'][0].'</a>';
            }

            $username = $row['username'];
            $account_id = $row['account_id'];
            $pre_days = $row['pre_days'] ? $row['pre_days'] : $GLOBALS['config']['pre_days'];
            
            $find = array('{name}', '{renew_link}', '{details_link}', '{links}', '{days}', '{listing_title}', '{complete_link}', '{delete_link}' );
            $replace = array($username, $update_link, $details_link, $links_table, $pre_days, $row['title'], $details_link, $update_link);

            foreach ($row['afind'] as $afk => $afr) {
                $find[] = $afr;
                $replace[] = $row['areplace'][$afk];
            }
            
            $email_tpl['body'] = str_replace($find, $replace, $email_tpl['body']);
            $email_tpl['subject'] = str_replace($find, $replace, $email_tpl['subject']);
            $rlMail->send($email_tpl, $email);
        }
    }
}

$notification_phrases['expired'] = 'Ads expired';
$notification_phrases['pre_expired'] = 'Expiration notifications sent';
$notification_phrases['featured'] = 'Featured ads expired';
$notification_phrases['pre_featured'] = 'Featured expiration notifications sent';
$notification_phrases['check_incompleted'] = 'Total incomplete ads';
$notification_phrases['incomplete_listings_notified'] = 'Incomplete ads notified';
$notification_phrases['incomplete_listings_removed'] = 'Incomplete ads removed';
$notification_phrases['incomplete_listings_waiting'] = 'Incomplete ads waiting '.$config['cron_incomplete_listing_days'].' days to be removed';
$notification_phrases['delete_expired'] = 'Expired listings removed';
$notification_phrases['delete_expired_notify'] = 'Expired listings waiting removal';

$notification_phrases['check_ac_incompleted'] = 'Total incomplete accounts';
$notification_phrases['incomplete_accounts_notified'] = 'Incomplete accounts notified';
$notification_phrases['incomplete_accounts_removed'] = 'Incomplete accounts removed';
$notification_phrases['incomplete_accounts_waiting'] = 'Incomplete accounts waiting '.$config['cron_incomplete_listing_days'].' days to be removed';

if ($config['listings_checking_notification']) {
	$admin_email['subject'] = "Cron Job Notification";
	$admin_email['body'] = "
	Cron Job notification at ". date(str_replace('b', 'M', $date_format)) ."<br /><br />";
	
	foreach ($statistics as $action => $count) {
        if ($notification_phrases[$action]) {
		$admin_email['body'] .= $notification_phrases[$action].": ".$count."<br />";
	}
	}

	$rlMail->send($admin_email, $config['notifications_email']);
}

/* SAVED SEARCH CHECKING */
$sql = "SELECT `T1`.`ID`, `T1`.`Account_ID`, `T1`.`Form_key`, `T1`.`Listing_type`, `T1`.`Content`, `T1`.`Matches`, `T2`.`Lang` ";
$sql .= "FROM `".RL_DBPREFIX."saved_search` AS `T1` ";
$sql .="LEFT JOIN `".RL_DBPREFIX."accounts` AS `T2` ON `T2`.`ID` = `T1`.`Account_ID` ";
$sql .= "WHERE `T1`.`Cron` = '0' AND `T1`.`Status` = 'active' LIMIT {$config['searches_per_run']}";
$searches = $rlDb->getAll($sql);

if (empty($searches)) {
	$rlDb->query("UPDATE `".RL_DBPREFIX."saved_search` SET `Cron` = '0'");
} else {
	/* prefere email notification template */
	foreach($languages as $key => $language) {
		$saved_search_email[$language['Code']] = $rlMail->getEmailTemplate('cron_saved_search_match', $language['Code']);
	}
	
	$reefless->loadClass('Search');
	$reefless->loadClass('Common');
		
	foreach ($searches as $key => $search) {
		$rlSearch->getFields($search['Form_key'], $search['Lising_type']);
		
		$content = unserialize($search['Content']);
		
		$rlSearch->exclude = $search['Matches'];
		$matches = $rlSearch->search($content, $search['Listing_type'], 0, 20);
		$rlSearch->exclude = false;
		
		$checked_listings = $search['Matches'];
		$exploded_matches = explode(',', $checked_listings);
		
		$update[$key]['fields']['Cron'] = '1';
	
		if (!empty($matches)) {
			foreach ($matches as $match) {
				if (!in_array($match['ID'], $exploded_matches)) {
					$listing_type = $rlListingTypes->types[$match['Listing_type']];
					
					$checked_listings .= empty($checked_listings) ? $match['ID'] : ','.$match['ID'];
					$match_count++;
					
					$link = $reefless->url('listing', $match);
					$links .= '<a href="'. $link .'">'. $match['listing_title'] .'</a><br />';
					
					$allow_send = true;
				}
			}
			
			if ($allow_send) {
				$update[$key]['fields']['Matches'] = $checked_listings;
				$count = $match_count;
				
				$account_info = $rlAccount->getProfile((int)$search['Account_ID']);
				
				$copy_notify_email = $saved_search_email[$account_info['Lang']];
				$copy_notify_email['body'] = str_replace(
                    array('{name}', '{count}', '{links}' ), 
                    array($account_info['Full_name'], $count, $links), 
                    $copy_notify_email['body']
                );
				$copy_notify_email['subject'] = str_replace(
                    array('{name}', '{count}'), 
                    array($account_info['Full_name'], $count), 
                    $copy_notify_email['subject']
                );
				$rlMail->send($copy_notify_email, $account_info['Mail']);
			}
		}
		
		$update[$key]['fields']['Cron'] = '1';
		$update[$key]['fields']['Date'] = 'NOW()';
		$update[$key]['where']['ID'] = $search['ID'];
		
		unset($copy_notify_email, $content, $allow_send, $links, $match_count);
	}
	
	if ($update) {
		$reefless->loadClass('Actions');
		$rlActions->update($update, 'saved_search');
	}
	
	unset($update);
}

/* MESSAGES CHECKING */
if ($config['cron_messages_remove']) {
	$rlDb->query("DELETE FROM `". RL_DBPREFIX ."messages` WHERE `Remove` = 'from,to'");
}

/* LOGIN ATTEMPTS CLEAR UP */
if ($config['security_login_attempt_admin_module']) {
	$sql = "DELETE FROM `".RL_DBPREFIX."login_attempts` WHERE `Interface` = 'admin' AND TIMESTAMPDIFF(MONTH, `Date`, NOW()) > 2";
	$rlDb->query($sql);
}

if ($config['security_login_attempt_user_module']) {
	$sql = "DELETE FROM `".RL_DBPREFIX."login_attempts` WHERE `Interface` = 'user' AND TIMESTAMPDIFF(MONTH, `Date`, NOW()) > 2";
	$rlDb->query($sql);
}

/* MEMBERSHIP */
if ($config['membership_module']) {
	// select accounts that are expired
	$sql ="SELECT `T1`.* FROM `".RL_DBPREFIX."accounts` AS `T1` ";
	$sql .="LEFT JOIN `".RL_DBPREFIX."membership_plans` AS `T2` ON `T1`.`Plan_ID` = `T2`.`ID`";
	$sql .= "WHERE (TIMESTAMPDIFF(HOUR, `T1`.`Pay_date`, NOW()) > `T2`.`Plan_period` * 24 AND `T2`.`Plan_period` != 0 ) ";
	$sql .= "AND `T1`.`Status` = 'active' ";

	$expired_accounts = $rlDb->getAll($sql);

	foreach($languages as $key => $language) {
		$membership_expired_emails[$language['Code']] = $rlMail->getEmailTemplate('cron_membership_expired', $language['Code']);
	}
	
	foreach($expired_accounts as $key => $account) {
		$mail_tpl = $account['Lang'] ? $membership_expired_emails[$account['Lang']] : $membership_expired_emails[$config['lang']];		

		$rlListings->listingStatusControl(array('Account_ID'=>$account['ID']), 'expired');
		$rlMail->send($mail_tpl, $account['Mail']);

		$update['where']['ID'] = $account['ID'];
		$update['fields']['Status'] = 'expired';
		$rlActions->updateOne($update, 'accounts');
	}
}
/* MEMBERSHIP END */

/**
* Handle incomplete accounts
*/
function handleIncompleteAccounts() 
{
    global $config, $rlDb, $statistics, $rlAdmin, $reefless;
    
    $reefless->loadClass('Admin', 'admin');

    $notify_days = $config['cron_incomplate_accounts_notify_days'];
    if (empty($notify_days)) {
        $notify_days = 1;
    }
    $limit = $config['accounts_number'];
    $first = true;
    while (count($accounts) == $limit || $first) {
        $first = false;
        $send_mail = true;

        $sql = 'SELECT `T1`.*, UNIX_TIMESTAMP(`T1`.`Date`) AS `Date_unix` ';
        $sql .= 'FROM `'. RL_DBPREFIX. 'accounts` AS `T1` ';
        $sql .= "WHERE UNIX_TIMESTAMP(DATE_ADD(`T1`.`Date`, INTERVAL {$notify_days} DAY)) < UNIX_TIMESTAMP(NOW()) ";
        $sql .= "AND `T1`.`Status` = 'incomplete' AND `T1`.`Cron` = '0' ";

        $accounts = $rlDb->getAll($sql);

        if ($accounts) {
            foreach ($accounts as $account) {
                if ($account['Cron_notified']) {
                    if ($account['Date_unix'] + ($config['cron_incomplete_account_days'] * 86400) <= time()) {
                        // save listings data
                        $sql = 'SELECT `T1`.`ID`, `T1`.`Category_ID`, `T1`.`Crossed`, `T2`.`Type` AS `Listing_type` ';
                        $sql .= 'FROM `'. RL_DBPREFIX .'listings` AS `T1` ';
                        $sql .= 'LEFT JOIN `'. RL_DBPREFIX ."categories` AS `T2` ON `T1`.`Category_ID` = `T2`.`ID` ";
                        $sql .= "WHERE `T1`.`Account_ID` = {$account['ID']}";
                        $ac_listings = $rlDb->getAll($sql);
                        
                        $GLOBALS['rlAdmin']->deleteAccountDetails($account['ID'], $ac_listings);
                        $rlDb->query('DELETE FROM `'. RL_DBPREFIX ."accounts` WHERE `ID` = {$account['ID']} LIMIT 1");

                        $statistics['incomplete_accounts_removed']++;
                    } else {
                        $statistics['incomplete_accounts_waiting']++;
                    }
                    $send_email = false;
                } else {
                    $statistics['incomplete_accounts_notified']++;
                }
                if ($send_mail) {
                    $hash = $reefless->generateHash(10);
                    $details_link = $reefless->getPageUrl('my_profile') . 
                        ($config['mod_rewrite'] ? '?' : '&') . 'incomplete=' . $account['ID'] . '&step=' . $account['Last_step'];
                    $update_link =  $reefless->getPageUrl('account_remove') . 
                        ($config['mod_rewrite'] ? '?' : '&') . 'id=' . $account['ID'] . '&hash=' . base64_encode($hash);

                    $tosend[$account['Mail']]['update_links'][] = $update_link;
                    $tosend[$account['Mail']]['details_links'][] = $details_link;
                    $tosend[$account['Mail']]['username'] = $account['First_name'] || $account['Last_name'] 
                        ? $account['First_name'] . ' ' . $account['Last_name'] 
                        : $account['Username'];
                    $tosend[$account['Mail']]['user_lang'] = $account['Lang'];
                    $tosend[$account['Mail']]['account_id'] = $account['ID'];

                    $tosend[$account['Mail']]['afind'] = array(
                        '{number_days}', 
                        '{update_text}', 
                        '{name}'
                    );
                    $tosend[$account['Mail']]['areplace'] = array(
                        $config['cron_incomplete_account_days'], 
                        $GLOBALS['lang']['remove'], 
                        $tosend[$account['Mail']]['username']
                    );
                }

                $update = array(
                    'fields' => array(
                        'Last_show' => 'NOW()',
                        'Cron' => '1',
                        'Cron_notified' => '1',
                        'Loc_address' => md5($hash)
                    ),
                    'where' => array('ID' => $account['ID'])
                );

                $GLOBALS['rlActions']->updateOne($update, 'accounts');
                unset($update);    
            }
        }
        $statistics['check_ac_incompleted'] += count($accounts);
    }
    $rlDb->query('UPDATE `'.RL_DBPREFIX."accounts` SET `Cron` = '0' WHERE `Status` <> 'trash'");
    
    return $tosend; 
}

/* clear expired auth data */
$rlDb->query("DELETE FROM `".RL_DBPREFIX."auth_tokens` WHERE `Expires` < NOW()");

/* run cron hooks */
$rlHook->load('cronAdditional');

$rlDb->query("UPDATE `".RL_DBPREFIX."config` SET `Default` = '".time()."' WHERE `Key` = 'cron_last_run'");

