#!/usr/local/bin/php
<?php
date_default_timezone_set('Asia/Tokyo');
require '/home/vps100800847/public_html/cmn/cmnfunc.php';
require '/home/vps100800847/public_html/cmn/cmndef.php';
//データベース接続
$conn = connectDB();
if(!$conn)
	die('cannot connect database.');
$result = optimizeLicenseExtensionApplication($conn);
if($result == ERROR_SUCCESS){
	$count = 0;
	$result = sendNoticeMail($count, $conn);
	switch($result){
	case ERROR_SUCCESS:
		if((int)$count > 0)
			echo sprintf("Success: %d mails sent.", (int)$count);
		break;
	case ERROR_BAD_QUERY:
		echo 'Query cannot be performed.';
		break;
	case ERROR_NOT_FOUND:
		echo 'template file is not found.';
		break;
	case ERROR_READ_FAULT:
		echo 'template file cannot read.';
		break;
	}
}
else{
	echo 'Query cannot be performed.';
}
pg_close($conn);
exit();


function optimizeLicenseExtensionApplication($conn)
{
	$result = ERROR_BAD_QUERY;
	$sql	= 'SELECT'
			. ' oid '
			. 'FROM'
			. ' lic_extension '
			. 'WHERE'
			. ' odr_date<NOW()-interval \'10 day\' AND'
			. ' app_date IS NULL AND'
			. ' payment IS NULL AND'
			. ' del IS NULL;';
	$res1	= pg_query($conn, $sql);
	if($res1){
		$result = ERROR_SUCCESS;
		if(pg_num_rows($res1) > 0){
			while($rec1 = pg_fetch_assoc($res1)){
				$sql = sprintf("SELECT count(*) AS rec_count FROM payment WHERE attr=2 AND oid='%s';", pg_escape_string($conn, $rec1['oid']));
				$res2 = pg_query($conn, $sql);
				if($res2){
					$rec2 = pg_fetch_assoc($res2);
					if((int)$rec2['rec_count'] > 0){
						//申請から10日が経過し、且つ入金、並びに振込申請がないものはキャンセル
						$sql	= 'UPDATE'
								. ' lic_extension '
								. 'SET'
								. ' del=CURRENT_DATE,'
								. ' del_reason=\'申込みから10日以内に入金が確認できなかったため\' '
								. 'WHERE'
								. sprintf(" oid='%s' AND", pg_escape_string($conn, $rec1['oid']))
								. ' del IS NULL;';
						if(!pg_query($conn, $sql)){
							$result = ERROR_BAD_QUERY;
							return $result;
						}
					}
				}
				else{
					$result = ERROR_BAD_QUERY;
					return $result;
				}
			}
		}
	}
	return $result;
}

function sendNoticeMail(&$count, $conn)
{
	$result	= ERROR_BAD_QUERY;
	$count	= 0;
	$sql	= 'SELECT'
			. ' uid,domestic,attr,nam,staff,sec,eml '
			. 'FROM'
			. ' usr '
			. 'WHERE'
			. ' del IS NULL '
			. 'ORDER BY'
			. ' domestic DESC;';
	$res1	= pg_query($conn, $sql);
	if($res1){
		$result = ERROR_SUCCESS;
		if(pg_num_rows($res1) > 0){
			//メール雛形読込み
			$domestic = $template = '';
			$from_name = $from_addr = '';
			$subject = $body = '';
			//メール送信
			while($rec1 = pg_fetch_assoc($res1)){
				if(strcmp($rec1['domestic'], $domestic) != 0){
					$domestic = $rec1['domestic'];
					$template = sprintf("/home/vps100800847/public_html/myitf/data/%s", (strcasecmp($domestic, 't') == 0 ? 'notify_mail.dat' : 'notify_mail_en.dat'));
					if(file_exists($template) && !is_dir($template)){
						$body = '';
						$contents = file($template);
						if(!$contents){
							$result = ERROR_READ_FAULT;
							return $result;
						}
						foreach($contents as $line){
							$line = trim($line);
							if(strlen($line) > 0 && strcmp(substr($line, 0, 1), '#') != 0){
								list($key, $data) = explode('=', $line, 2);
								switch(trim($key)){
								case 'from_name':	//メール送信者
									$from_name = trim(removeLineFeed($data));
									break;
								case 'from_addr':	//送信元アドレス
									$from_addr = trim(removeLineFeed($data));
									break;
								case 'subject':		//件名
									$subject = trim(removeLineFeed($data));
									break;
								case 'body':		//本文
									$body .= sprintf("%s\n", removeLineFeed($data));
									break;
								}
							}
						}
					}
					else{
						$result = ERROR_NOT_FOUND;
						return $result;
					}
				}
				$sql	= 'SELECT'
						. ' t01.sn,t01.qty,'
						. ' (SELECT count(*) FROM lic_detail WHERE sn=t01.sn) AS used,'
						. ' t01.effective_start,t01.effective_end,'
						. ' t02.pid,t02.nam,t02.als,t02.fee,t02.tax '
						. 'FROM'
						. ' lic t01 '
						. 'INNER JOIN'
						. ' product t02 '
						. 'ON'
						. ' t02.base=substr(t01.sn,1,5) AND'
						. ' t02.parent=0 '
						. 'WHERE'
						. sprintf(" t01.uid='%s' AND", pg_escape_string($conn, $rec1['uid']))
						. ' t01.effective_end IS NOT NULL AND'
						. ' t01.effective_end>CURRENT_DATE AND'
						. ' t01.del IS NULL;';
				$res2	= pg_query($conn, $sql);
				if($res2){
					if(pg_num_rows($res2) > 0){
						while($rec2 = pg_fetch_assoc($res2)){
							$days = compareDate($rec2['effective_end'], date('Y-m-d'));
							switch((int)$days){
							case 30:
							case 10:
							case 5:
							case 3:
							case 2:
							case 1:
								$amount = (int)$rec2['fee'] * (int)$rec2['qty'];
								$tax = (strcmp($rec2['tax'], 't') != 0 ? floor((float)$amount * (float)CONSUMPTION_TAX) : 0);
								if(strcasecmp($domestic, 't') == 0){
									//国内
									if(($rec1['attr'] == 0) && (strlen($rec1['staff']) > 0)){
										$user = sprintf("%s\n",  $rec1['nam']);
										if(strlen($rec1['sec']) > 0)
											$user .= sprintf("　%s\n", $rec1['sec']);
										$user .= sprintf("　%s 様", $rec1['staff']);
									}
									else{
										$user = sprintf("%s 様", $rec1['nam']);
									}
								}
								else{
									//海外
									$user = $rec1['nam'];
								}
								$mail_body = $body;
								$mail_body = str_replace('<user>',		$user,			$mail_body);
								$mail_body = str_replace('<days>',		$days,			$mail_body);
								$mail_body = str_replace('<product>',	$rec2['nam'],	$mail_body);
								$mail_body = str_replace('<serial>',	$rec2['sn'],	$mail_body);
								$mail_body = str_replace('<amount>',	$rec2['qty'],	$mail_body);
								$mail_body = str_replace('<used>',		$rec2['used'],	$mail_body);
								$mail_body = str_replace('<fee>',		setSeparatorNumeric($amount + $tax), $mail_body);
								//メールキューに登録
								$count++;
								$sql	= 'INSERT INTO'
										. ' mailq (reg_date, num,"to",bcc,"from",subject,body) '
										. 'VALUES'
										. sprintf
												(
													" (NOW(),%d,'%s','%s','%s','%s','%s');",
													(int)$count,
													pg_escape_string($conn, sprintf("%s\t%s", $rec1['eml'], $rec1['nam'])),
													pg_escape_string($conn, sprintf("%s\t%s", $from_addr, $from_name)),
													pg_escape_string($conn, sprintf("%s\t%s", $from_addr, $from_name)),
													pg_escape_string($conn, $subject),
													pg_escape_string($conn, $mail_body)
												);
								if(!pg_query($conn, $sql)){
									$result = ERROR_BAD_QUERY;
									return $result;
								}
								break;
							default:
								break;
							}
						}
					}
				}
				else{
					$result = ERROR_BAD_QUERY;
					return $result;
				}
			}
		}
	}
	return $result;
}
?>