<?php
/**
 * Implements hook_cron().
 */
function gdlisnet_cron() {
	gdlisnet_cron_notify();
	gdlisnet_cron_smartibus_photo();
	gdlisnet_cron_smartibus_personal_info();
	\Drupal::state()->set('gdlisnet.smartibus.last_run', time());
}

function gdlisnet_cron_notify() {
	// First let's fetch all the database settings
	$datasource=\Drupal::config('gdlisnet.datasource');
	$keys=['host','port','database','username','password'];
	foreach($keys as $key) {
		if(empty($datasource->get($key))) return;
	}
	
	try {
		$k=[];
		
		// Use php-oci8 rather than pdo_oci, which is faster
		$conn=oci_connect($datasource->get('username'),$datasource->get('password'),
			sprintf("%s:%s/%s", $datasource->get('host'), $datasource->get('port'), $datasource->get('database')),'AL32UTF8'
		);
		if(!$conn) {
			$e=oci_error();
			throw new Exception($e['message'], $e['code']);
		}
		
		// First let's reset the passwords
		$sql="UPDATE 读者库 SET 密码=读者条码 WHERE 密码 is null";
		$stmt = oci_parse($conn, $sql);
		if(!$stmt) {
			$e=oci_error($stmt);
			throw new Exception($e['message'], $e['code']);
		}
		$r=oci_execute($stmt, OCI_DEFAULT);
		if(!$r) {
			$e=oci_error($stmt);
			throw new Exception($e['message'], $e['code']);
		}
		oci_commit($conn);

		// Only invoke if the option is enabled. Notify overdued readers
		if(\Drupal::config('gdlisnet.qyweixin')->get('notify_overdue')) {
			$sql=sprintf("SELECT 馆藏书目库.题名, 馆藏书目库.责任者, 馆藏书目库.出版者, to_char(流通库.外借时间, 'yyyy-mm-dd') AS 外借时间, ".
				"to_char(流通库.应归还时间, 'yyyy-mm-dd') AS 应归还时间, 流通库.续借次数, 流通库.读者条码, 读者库.姓名, 流通参数定义.允许续借次数 ".
				'FROM 馆藏书目库 INNER JOIN 流通库 ON 馆藏书目库.主键码 = 流通库.主键码 INNER JOIN 读者库 on 读者库.读者条码 = 流通库.读者条码 '.
				'INNER JOIN 馆藏典藏库 ON 馆藏书目库.主键码 = 馆藏典藏库.主键码 AND 流通库.条形码=馆藏典藏库.条形码 '.
				'INNER JOIN 馆藏地址定义 ON 馆藏典藏库.馆藏地址=馆藏地址定义.馆藏地址 '.
				'INNER JOIN 流通参数定义 ON 读者库.级别代码=流通参数定义.级别代码 AND 馆藏典藏库.虚拟库室=流通参数定义.虚拟库室 '.
				'WHERE 流通库.应归还时间<sysdate ORDER BY 读者库.读者条码');
			$stmt=oci_parse($conn, $sql);
			if(!$stmt) {
				$e=oci_error($stmt);
				throw new \Exception($e['message'], $e['code']);
			}
			$r=oci_execute($stmt, OCI_DEFAULT);
			if(!$r) {
				$e=oci_error($stmt);
				throw new \Exception($e['message'], $e['code']);
			}
			while($row=oci_fetch_array($stmt)) {
				$date=\DateTime::createFromFormat('Y-m-d', $row['外借时间']);
				$return_date=\DateTime::createFromFormat('Y-m-d', $row['应归还时间']);
				$num=date_create()->diff($return_date);
				$format='';
				if($num->y)
					$format.='%y年';
				if($num->m)
					$format.='%m个月';
				if($num->d)
					$format.='%d天';
				if(date_create()->diff($return_date)->days) {
					$k[$row['读者条码']][]=(string)t("@name, the book @title @writer @publisher you have borrowed in @date should be returned in @return. Now @num days have passed. Please return it asap.", array(
						'@name'=>$row['姓名'], '@title'=>$row['题名'], '@writer'=>$row['责任者'], '@publisher'=>$row['出版者'],
						'@date'=>\DateTime::createFromFormat('Y-m-d', $row['外借时间'])->format('Y年n月j日'),
						'@return'=>$return_date->format('Y年n月j日'),
						'@num'=>$num->format($format)
					));
				}
			}
		}
			
		// Only invoke if the option is enabled. Notify upcoming readers
		if(\Drupal::config('gdlisnet.qyweixin')->get('notify_upcoming')) {
			$days=explode(',',\Drupal::config('gdlisnet.qyweixin')->get('notify_upcoming_days'));
			foreach($days as $day) {
				$sql=sprintf("SELECT 馆藏书目库.题名, 馆藏书目库.责任者, 馆藏书目库.出版者, to_char(流通库.外借时间, 'yyyy-mm-dd') AS 外借时间, ".
					"to_char(流通库.应归还时间, 'yyyy-mm-dd') AS 应归还时间, 流通库.续借次数, 流通库.读者条码, 读者库.姓名, 流通参数定义.允许续借次数 ".
					'FROM 馆藏书目库 INNER JOIN 流通库 ON 馆藏书目库.主键码 = 流通库.主键码 INNER JOIN 读者库 on 读者库.读者条码 = 流通库.读者条码 '.
					'INNER JOIN 馆藏典藏库 ON 馆藏书目库.主键码 = 馆藏典藏库.主键码 AND 流通库.条形码=馆藏典藏库.条形码 '.
					'INNER JOIN 馆藏地址定义 ON 馆藏典藏库.馆藏地址=馆藏地址定义.馆藏地址 '.
					'INNER JOIN 流通参数定义 ON 读者库.级别代码=流通参数定义.级别代码 AND 馆藏典藏库.虚拟库室=流通参数定义.虚拟库室 '.
					'WHERE 流通库.应归还时间-sysdate>%s AND 流通库.应归还时间-sysdate<%s ORDER BY 读者库.读者条码', $day, $day+1);
				$stmt=oci_parse($conn, $sql);
				if(!$stmt) {
					$e=oci_error($stmt);
					throw new \Exception($e['message'], $e['code']);
				}
				$r=oci_execute($stmt, OCI_DEFAULT);
				if(!$r) {
					$e=oci_error($stmt);
					throw new \Exception($e['message'], $e['code']);
				}
				while($row=oci_fetch_array($stmt)) {
					$date=\DateTime::createFromFormat('Y-m-d', $row['外借时间']);
					$return_date=\DateTime::createFromFormat('Y-m-d', $row['应归还时间']);
					$can_extend=$row['允许续借次数']-$row['续借次数'];
					$format='';
					if($can_extend) {
						$k[$row['读者条码']][]=(string)t("@name, the book @title @writer @publisher you have borrowed in @date should be returned in @return, only @num days left. Please return it asap, or make a extend.", array(
							'@name'=>$row['姓名'], '@title'=>$row['题名'], '@writer'=>$row['责任者'], '@publisher'=>$row['出版者'],
							'@date'=>\DateTime::createFromFormat('Y-m-d', $row['外借时间'])->format('Y年n月j日'),
							'@return'=>$return_date->format('Y年n月j日'),
							'@num'=>$day
						));
					} else {
						$k[$row['读者条码']][]=(string)t("@name, the book @title @writer @publisher you have borrowed in @date should be returned in @return, only @num days left. Please return it asap.", array(
							'@name'=>$row['姓名'], '@title'=>$row['题名'], '@writer'=>$row['责任者'], '@publisher'=>$row['出版者'],
							'@date'=>\DateTime::createFromFormat('Y-m-d', $row['外借时间'])->format('Y年n月j日'),
							'@return'=>$return_date->format('Y年n月j日'),
							'@num'=>$day
						));
					}
				}
			}
		}
		
		$queue = \Drupal::queue('gdlisnet.overdue');
		foreach($k as $reader=>$data) {
			$r=new stdClass();
			$r->readerid=$reader;
			$r->data=$data;
			$queue->createItem($r);
		}
		if($queue->numberOfItems()) {
			$r=new stdClass();
			$r->readerid='TYX061';
			$r->data=['notify messages sent.'];
			$queue->createItem($r);
		}
	} catch (\Exception $e) {
		\Drupal::logger('gdlisnet')->error(
			'Error occured when accessing gdlist database: %errcode %errmsg',
			['%errcode'=>$e->getCode(), '%errmsg'=>$e->getMessage()]
		);
	} finally {
		if(!empty($stmt))
			oci_free_statement($stmt);
		if(!empty($conn))
			oci_close($conn);
	}
	// End
}

function gdlisnet_cron_smartibus_photo() {
	// First let's fetch all the database settings
	$smartibus_datasource=\Drupal::config('gdlisnet.smartibus.datasource');
	$keys=['host','port','database','username','password'];
	foreach($keys as $key) {
		if(empty($smartibus_datasource->get($key))) return;
	}
	
	try {
		// Use php-oci8 rather than pdo_oci, which is faster
		$conn=oci_connect($smartibus_datasource->get('username'),$smartibus_datasource->get('password'),
			sprintf("%s:%s/%s", $smartibus_datasource->get('host'), $smartibus_datasource->get('port'), $smartibus_datasource->get('database')),'AL32UTF8'
		);
		if(!$conn) {
			$e=oci_error();
			throw new Exception($e['message'], $e['code']);
		}

		// Now lets get those pictures from ecard database
		// Search for those photos which are modified since last update
		$state = \Drupal::state();
		$timestamp=$state->get('gdlisnet.smartibus.last_run');
		if(empty($timestamp)) $timestamp=time()-3600*24;
		$sql=sprintf("SELECT smt_salaryno, smt_photo, smt_moddate FROM mid_photo WHERE smt_moddate >to_date('%s', 'yyyy-mm-dd hh24:mi:ss') ORDER BY smt_moddate ASC", date('Y-m-d H:i:s', $timestamp));
		$stmt = oci_parse($conn, $sql);
		if(!$stmt) {
			$e=oci_error($stmt);
			throw new Exception($e['message'], $e['code']);
		}
		$r=oci_execute($stmt, OCI_DEFAULT);
		if(!$r) {
			$e=oci_error($stmt);
			throw new Exception($e['message'], $e['code']);
		}

		// Fetch all results one by one, and push them into queue
		$queue = \Drupal::queue('gdlisnet_photo_exchange');
		while($row=oci_fetch_array($stmt,OCI_ASSOC+OCI_RETURN_LOBS)) {
			$r=new stdClass();
			$r->smt_salaryno=$row['SMT_SALARYNO'];
			$r->smt_photo=$row['SMT_PHOTO'];
			// Push those people into queue
			$queue->createItem($r);
		}

		$sql=sprintf("SELECT c.smt_salaryno, c.smt_photo FROM cardpersonnel c INNER JOIN midpersonnel m ON c.smt_salaryno=m.salaryno WHERE m.indatetime>to_date('%s', 'yyyy-mm-dd hh24:mi:ss')", date('Y-m-d H:i:s', $timestamp));
		$stmt = oci_parse($conn, $sql);
		if(!$stmt) {
			$e=oci_error($stmt);
			throw new Exception($e['message'], $e['code']);
		}
		$r=oci_execute($stmt, OCI_DEFAULT);
		if(!$r) {
			$e=oci_error($stmt);
			throw new Exception($e['message'], $e['code']);
		}

		// Fetch all results one by one, and push them into queue
		$queue = \Drupal::queue('gdlisnet_photo_exchange');
		while($row=oci_fetch_array($stmt,OCI_ASSOC+OCI_RETURN_LOBS)) {
			$r=new stdClass();
			$r->smt_salaryno=$row['SMT_SALARYNO'];
			$r->smt_photo=$row['SMT_PHOTO'];
			// Push those people into queue
			$queue->createItem($r);
		}

		} catch (Exception $e) {
			\Drupal::logger('gdlisnet')->error(
				'Error occured when accessing interchange lib for ecard: %errcode %errmsg',
				['%errcode'=>$e->getCode(), '%errmsg'=>$e->getMessage()]
			);
	} finally {
		if(!empty($stmt))
			oci_free_statement($stmt);
		if(!empty($conn))
			oci_close($conn);
	}
	// End
}

function gdlisnet_cron_smartibus_personal_info() {
	// First let's fetch all the database settings
	$smartibus_datasource=\Drupal::config('gdlisnet.smartibus.datasource');
	$keys=['host','port','database','username','password'];
	foreach($keys as $key) {
		if(empty($smartibus_datasource->get($key))) return;
	}
	
	try {
		// Use php-oci8 rather than pdo_oci, which is faster
		$conn=oci_connect($smartibus_datasource->get('username'),$smartibus_datasource->get('password'),
			sprintf("%s:%s/%s", $smartibus_datasource->get('host'), $smartibus_datasource->get('port'), $smartibus_datasource->get('database')),'AL32UTF8'
		);
		if(!$conn) {
			$e=oci_error();
			throw new Exception($e['message'], $e['code']);
		}
		
		// Now lets get those pictures from ecard database
		// Search for those photos which are modified since last update
		$state = \Drupal::state();
		$timestamp=$state->get('gdlisnet.smartibus.last_run');
		if(empty($timestamp)) $timestamp=time()-3600*24;
		$sql=sprintf("SELECT * FROM cardpersonnel c INNER JOIN midpersonnel m ON c.smt_salaryno=m.salaryno WHERE m.indatetime>to_date('%s', 'yyyy-mm-dd hh24:mi:ss')", date('Y-m-d H:i:s', $timestamp));
		$stmt = oci_parse($conn, $sql);
		if(!$stmt) {
			$e=oci_error($stmt);
			throw new Exception($e['message'], $e['code']);
		}
		$r=oci_execute($stmt, OCI_DEFAULT);
		if(!$r) {
			$e=oci_error($stmt);
			throw new Exception($e['message'], $e['code']);
		}

		// Fetch all results one by one, and push them into queue
		$queue = \Drupal::queue('gdlisnet_information_exchange');
		while($row=oci_fetch_array($stmt,OCI_ASSOC+OCI_RETURN_LOBS)) {
			$r=new stdClass();
			$r->smt_salaryno=$row['SMT_SALARYNO'];
			$r->smt_name=$row['SMT_NAME'];
			$r->smt_sex=$row['SMT_SEX'];
			$r->smt_deptname=$row['SMT_DEPTNAME'];
			$r->smt_statuscode=$row['SMT_STATUSCODE'];
			$r->smt_statusname=$row['SMT_STATUSNAME'];
			$r->indatetime=$row['INDATETIME'];
			// Push those people into queue
			$queue->createItem($r);
		}
	} catch (Exception $e) {
		\Drupal::logger('gdlisnet')->error(
			'Error occured when accessing interchange lib for ecard: %errcode %errmsg',
			['%errcode'=>$e->getCode(), '%errmsg'=>$e->getMessage()]
		);
	} finally {
		if(!empty($stmt))
			oci_free_statement($stmt);
		if(!empty($conn))
			oci_close($conn);
	}
	// End
}

?>