<?php

//echo $_SERVER['DOCUMENT_ROOT'] . '<br>';
//exit;

date_default_timezone_set('America/New_York');

$logfile =("cron_log.txt");
$log = fopen($logfile, "a+");
$now = date("Y-m-d H:i",time());
fwrite($log, "\n----- $now -----\n");

require_once('../globals/config.php');
global $con;
$con = new mysqli($host,$user,$pass,$name);
if ($con->connect_errno) {
    printf("Connect failed: %s\n", $con->connect_error);
    exit();
}
include('../globals/functions.php');

$today = date('Y-m-d');
//$today = date('2018-01-01');
$month = addDays($today, 30);

require_once('../PHPMailer/class.phpmailer.php');	
$sendAddress = 'josh@comocreative.com'; 
$sendName = 'Como Creative'; 
$mail = new PHPMailer(true); // the true param means it will throw exceptions on errors, which we need to catch
$mail->IsSMTP(); // telling the class to use SMTP
$mail->SMTPDebug  = 0;                     // enables SMTP debug information (for testing)
$mail->SMTPAuth   = true;                  // enable SMTP authentication
$mail->SMTPSecure = 'ssl'; // secure transfer enabled REQUIRED for Gmail
$mail->Host       = "smtp.gmail.com"; // sets the SMTP server
$mail->Port       = 465;                    // set the SMTP port for the GMAIL server
$mail->Username   = "josh@comocreative.com"; // SMTP account username
$mail->Password   = "illomocJSC3705";        // SMTP account password
$mail->AddAddress($sendAddress, $sendName);
$mail->SetFrom($sendAddress, $sendName);
$mail->AddReplyTo($sendAddress, $sendName);

//echo $month;
//exit;

$query = 'SELECT *, (ogf.feeAmt * fr.feeRecCalc) as billAmount FROM ongoingFees AS ogf LEFT JOIN clients AS c ON (ogf.clientID = c.clientID) LEFT JOIN feeDesc AS fd ON (ogf.feeDescID = fd.feeDescID) LEFT JOIN feeRec AS fr ON (ogf.feeRecID = fr.feeRecID) WHERE ogf.display=1 ORDER BY feeID';
$result = $con->query($query);

$billindRemindersALL = '';
if ($result) {
	// Update fee dates if necessary
	while ($row=$result->fetch_array(MYSQLI_ASSOC)) {
		// If the fee is due today
		if (strtotime($row['feeNextRec']) == strtotime($today)) {
			$feeLast = $row['feeNextRec'];
			if ($row['feeRecID'] == 6)
				$feeNext = addMonths($feeLast, 12);
			else 
				$feeNext = addMonths($feeLast, $row['feeRecCalc']); 
			$updateQuery = 'UPDATE ongoingFees SET feeLastRec="'. $feeLast .'", feeNextRec="'. $feeNext .'" WHERE feeID=' . $row['feeID'];	
			//echo '<p>If the fee is due today:<br>'. $updateQuery .'</p>'; 
			$res = $con->query($updateQuery);

			// Check to see if the fee has already been added to the ongoingFeesPaid table.  If not, Add it for billing
			$checkQuery = 'SELECT * FROM ongoingFeesPaid WHERE ogfeeID='. $row['feeID'] .' AND invDate="'. $today .'"';
			$checkResult = $con->query($checkQuery);
			if ($checkResult->num_rows < 1) {
				$insert_query = 'INSERT INTO ongoingFeesPaid (ogfpmtID,ogfeeID,invDate,pmtDate,ppInvDate,ppInv,ppPaidDate,ppPaid) VALUES ("","'. $row['feeID'] .'","'. $row['feeNextRec'] .'","","","","","")'; 
				//echo '<p>If the fee is due today:<br>'. $insert_query .'</p>'; 
				$res = $con->query($insert_query);
			}
		}
		// Check if any fees are coming up in the next month.  If so, add them to the ongoingFeesPaid table for billing
		elseif ((strtotime($row['feeNextRec']) >= strtotime($today)) && (strtotime($row['feeNextRec']) <= strtotime($month))) {
			$checkQuery = 'SELECT * FROM ongoingFeesPaid WHERE ogfeeID='. $row['feeID'] .' AND (invDate>="'. $today .'" AND invDate<="'. $month .'")';
			$checkResult = $con->query($checkQuery);
			//echo $checkQuery . '<br>';
			if ($checkResult->num_rows < 1) {
				//echo 'NEED TO INSERT<br>';
				$insert_query = 'INSERT INTO ongoingFeesPaid (ogfpmtID,ogfeeID,invDate,pmtDate,ppInvDate,ppInv,ppPaidDate,ppPaid) VALUES ("","'. $row['feeID'] .'","'. $row['feeNextRec'] .'","","","","","")';
				//echo '<p>If any fees are coming up in the next month:<br>'. $insert_query . '</p>';
				$res = $con->query($insert_query);
				
				$billindReminder = '<p><b>' . $row['clientName'] .'</b> is up for renewal on their '. $row['feeDesc'] .'.  They are billed '. $row['feeRecDesc'] .' on '. $row['feeNextRec'] .'.</p>';
				$billindRemindersALL .= $billindReminder;
			}
			//echo '<br><br>';
		}
		else {
			if ($row['feeLastRec'] == '0000-00-00')
				$feeLast =  $row['feeStartDate'];
			else 
				$feeLast = $row['feeLastRec'];
			$feeNext = $row['feeNextRec']; 
		}
		$feeNext = Date::convert($feeNext, 'Y-m-d', 'M d, Y');
		$feeLast = Date::convert($feeLast, 'Y-m-d', 'M d, Y');
	}
}
if (empty($billindRemindersALL)) {
	$billindRemindersALL = 'No Billing Reminders'. $query; 
	
	/*try {
		$mail->Subject = ("NO Upcoming Billing Reminders"); 
		$mail->AltBody = 'Como Creative'; // optional - MsgHTML will create an alternate automatically
		$mail->MsgHTML($billindRemindersALL);
		//$mail->AddAttachment();      // attachment
		$mail->Send();
	} catch (phpmailerException $e) {
		echo $e->errorMessage(); //Pretty error messages from PHPMailer
	} catch (Exception $e) {
		echo $e->getMessage(); //Boring error messages from anything else!
	}*/
	
}
else {
	try {
		$mail->Subject = ("Upcoming Billing Reminders"); 
		$mail->AltBody = 'Como Creative'; // optional - MsgHTML will create an alternate automatically
		$mail->MsgHTML($billindRemindersALL);
		//$mail->AddAttachment();      // attachment
		$mail->Send();
	} catch (phpmailerException $e) {
		echo $e->errorMessage(); //Pretty error messages from PHPMailer
	} catch (Exception $e) {
		echo $e->getMessage(); //Boring error messages from anything else!
	}
}
fwrite($log, $billindRemindersALL . "\n");
fclose ($log);
echo "billing - ok";
?>