<?php
	require_once "odincom.php";
	require_once "dbo/Item.php";
	require_once "dbo/Series.php";
	require_once "dbo/SeriesList.php";
	require_once "$ui_dir/uicom.inc";
	require_once "$ui_dir/renderer.inc";
	require_once "$ui_dir/dispupload.inc";

	$script	="upload.php";
	odin_staff_login();

	if ($_GET['nvr']) {
		$_SESSION['navbar']->register("root_inven");
	}


	$action = $_POST['action'];
	if (!$action && $_GET['browse'] == 1) $action = "browse";

	switch ($action) {
		case "upload":
			if (! handle_upload()) {
				break;
			}
			$_SERVER['QUERY_STRING'] .= "browse=1";
		case "browse":
			$printForm = "merge";
			$renderObj = browse_temp_series();
			break;
		case "postupload":
			$_SESSION['navbar']->register("uploadbooklist");
			post_upload();
			break;
		default:
			$s = $_GET['sid'];
			$v = $_GET['vid'];
			if ($v) {
				$printForm = "merge";
				$renderObj = volume_info($v);
			}
			elseif ($s) {
				$printForm = "merge";
				$renderObj = series_info($s);
			}
			else {
				$_SESSION['navbar']->register("uploadbooklist");
				load_config_type($mconf , 'upload_booklist');
				load_config_type($mhdr , 'upload_booklist_hdr');
				//print_r($mhdr);
				$printForm = "upload";
			}
			break;
	}

	print_header();
	switch($printForm) {
		case "upload" :
			upload_form();
			break;
		case "merge" :
			merge_form();
			break;
		default:
	}
	$renderer = new Renderer;
	if ($renderObj) {
		$renderer->render($renderObj);
	}
	print_footer();
	exit;



function check_data_existance() {
	odin_connect();
	$result = mysql_list_tables($GLOBALS['sql_database']);
	if (!$result) {
		myresult(0,"Cannot list tables\n");
		return;
	}
	while (list($row,) = mysql_fetch_row($result)) {
		if ($row == "odin_temp_issue" || $row == "odin_temp_series") {
			return 1;
		}
	}
}

function post_upload() {
	if (!check_staff_permission("writebooklist")) return;
	//$scolnames = "seriesid, title, shelf, publisher, author";
	//$vcolnames = "series, volume, shelf";
	// we ***must*** preserv serial ids for relation between the two tables
	// TODO: query max(id) and do a dummy insert to temp table to initialize
	//       hence make sure the id does not collide
	
	odin_connect();
	$opt = $_POST['opt'];
	switch ($opt) {
		case "mergeignore":
			//$query_series = "INSERT IGNORE INTO odin_series ($scolnames) SELECT $scolnames FROM odin_temp_series";
			//$query_issue = "INSERT IGNORE INTO odin_item ($vcolnames) SELECT $vcolnames FROM odin_temp_item";
			$query_series = "INSERT IGNORE INTO odin_series SELECT * FROM odin_temp_series";
			$query_issue = "INSERT IGNORE INTO odin_item SELECT * FROM odin_temp_item";
			$query_status = "INSERT IGNORE INTO odin_item_status (id,status) ";
			$query_status .= "SELECT id, '{$_POST['status']}' FROM odin_temp_item";
			$query_drop = "DROP TABLE odin_temp_series, odin_temp_item";
			break;
		case "mergeupdate":
			//$query_series = "REPLACE INTO odin_series ($scolnames) SELECT $scolnames FROM odin_temp_series";
			//$query_issue = "REPLACE INTO odin_item ($vcolnames) SELECT $vcolnames FROM odin_temp_item";
			$query_series = "REPLACE INTO odin_series SELECT * FROM odin_temp_series";
			$query_issue = "REPLACE INTO odin_item SELECT * FROM odin_temp_item";
			$query_status = "REPLACE INTO odin_item_status (id,status) ";
			$query_status .= "SELECT id, '{$_POST['status']}' FROM odin_temp_item";
			$query_drop = "DROP TABLE odin_temp_series, odin_temp_item";
			break;
		case "refresh":
			$query_predrop = "DROP TABLE odin_series, odin_item";
			$query_predrop2 = "DELETE FROM odin_item_status";
			$query_rename = "RENAME TABLE odin_temp_series TO odin_series, ";
			$query_rename .= "odin_temp_item TO odin_item";
			$query_status = "INSERT INTO odin_item_status (id,status) ";
			$query_status .= "SELECT id, '{$_POST['status']}' FROM odin_item";
			break;
		case "cancel":
			$query_drop = "DROP TABLE odin_temp_series, odin_temp_item";
			break;
		default:
			myresult(0, "Unknown option");
			return;
	}

	$query = "LOCK TABLES odin_series WRITE, odin_temp_series WRITE";
	$query .= ", odin_item WRITE, odin_temp_item WRITE";
	$query .= ", odin_item_status WRITE";
	$result = mysql_query($query);
	if (! $result) {
		myresult(0, "Cannot lock table : ". mysql_error());
		return;
	}

	if ($query_predrop) {
		$result = mysql_query($query_predrop);
		if ($result) {
			myresult(1, "Query success<br>$query_predrop\n");
		} else {
			myresult(0, "query failed : " . mysql_error(). "<br>$query_predrop\n");
			return;
		}
	}

	if ($query_predrop2) {
		$result = mysql_query($query_predrop2);
		if ($result) {
			myresult(1, "Query success<br>$query_predrop2\n");
		} else {
			myresult(0, "query failed : " . mysql_error(). "<br>$query_predrop2\n");
			return;
		}
	}

	if ($query_series) {
		$result = mysql_query($query_series);
		if ($result) {
			myresult(1, "Query success : ". mysql_affected_rows() ." rows affected.<br>$query_series\n");
		} else {
			myresult(0, "query failed : " . mysql_error(). "<br>$query_series\n");
			return;
		}
	}

	if ($query_issue) {
		$result = mysql_query($query_issue);
		if ($result) {
			myresult(1, "Query success : ". mysql_affected_rows() ." rows affected.<br>$query_issue\n");
		} else {
			myresult(0, "query failed : " . mysql_error(). "<br>$query_issue\n");
			return;
		}
	}

	if ($query_rename) {
		mysql_query("UNLOCK TABLES");
		$result = mysql_query($query_rename);
		if ($result) {
			myresult(1, "Query success : ". mysql_affected_rows() ." rows affected.<br>$query_rename\n");
		} else {
			myresult(0, "query failed : " . mysql_error(). "<br>$query_rename\n");
			return;
		}
	}

	if ($query_status) {
		$result = mysql_query($query_status);
		if ($result) {
			myresult(1, "Query success : ". mysql_affected_rows() ." rows affected.<br>$query_status\n");
		} else {
			myresult(0, "query failed : " . mysql_error(). "<br>$query_status\n");
			return;
		}
	}

	if ($query_drop) {
		$result = mysql_query($query_drop);
		if ($result) {
			myresult(1, "Query success : ". mysql_affected_rows() ." rows affected.<br>$query_drop\n");
		} else {
			myresult(0, "query failed : " . mysql_error(). "<br>$query_drop\n");
			return;
		}
	}

	mysql_query("UNLOCK TABLES");
	$query = "INSERT INTO odin_log SET type='UploadBooklist',"
		." staff='{$_SESSION['userinfo']['id']}',"
		." ipaddr='{$_SERVER['REMOTE_ADDR']}',"
		." itemdesc='opt=$opt',"
		." timestamp=NULL";
	if (!mysql_query($query) || !mysql_affected_rows()) {
		myresult (0, "(Warning) Insert into odin_log failed : ". mysql_error() ."<br>$query");
	}
	return 1;
}

function handle_upload() {

	if (!check_staff_permission("writebooklist")) return;

	load_config_type($mconf , 'upload_booklist');
	preg_match('/^\s*(\d+)\s*(-\s*(\d+))?\s*$/', $_POST['sno_field'], $m);
	list(,$sno_fstart,, $sno_fend) = $m;
	if (!$sno_fend) $sno_fend = $sno_fstart;
	preg_match('/^\s*(\d+)\s*(-\s*(\d+))?\s*$/', $_POST['vol_field'], $m);
	list(,$vol_fstart,, $vol_fend) = $m;
	if (!$vol_fend) $vol_fend = $vol_fstart;
	//print ("<pre>sno, vol: $sno_fstart, $sno_fend, $vol_fstart, $vol_fend</pre>\n");
	
	//sanity check, fall back to odin_config (db) value
	if (!is_numeric($sno_fstart) || !is_numeric($sno_fend)) {
		preg_match('/^\s*(\d+)\s*-\s*(\d+)\s*$/', $mconf['sno_field'], $m);
		list(,$sno_fstart, $sno_fend) = $m;
	}
	if (!is_numeric($vol_fstart) || !is_numeric($vol_fend)) {
		preg_match('/^\s*(\d+)\s*-\s*(\d+)\s*$/', $mconf['vol_field'], $m);
		list(,$vol_fstart, $vol_fend) = $m;
	}
	$mconf['strict_pub'] = ($_POST['strict_pub'] == 'yes') ? 'yes':'no';
		
	//print ("<pre>sno, vol: $sno_fstart, $sno_fend, $vol_fstart, $vol_fend</pre>\n");
	
	$itemno_fmt = parse_itemno_fmtstr($_POST['itemno_fmt']);
	if (!$itemno_fmt) $itemno_fmt = parse_itemno_fmtstr($mconf['itemno_fmt']);
	//print ("<pre>itemno_fmt: $itemno_fmt[0], $itemno_fmt[1], $itemno_fmt[2]</pre>\n");
	
	if (is_array($_POST['hdr'])) {
		$mhdr = $_POST['hdr'];
		while(list($key,$val) = each ($mhdr)) $mhdr[$key] = trim($val);
		//array_walk($mhdr, "trim");
		//array_map("trim", $mhdr);
	} else {
		load_config_type($mhdr , 'upload_booklist_hdr');
	}
	//print_r($mhdr);

	$file = "tmp/booklist";
	if (! move_uploaded_file($_FILES['booklist']['tmp_name'], $file)) {
		myresult(0, "File upload error");
		return;
	}

	$fh = fopen ($file, "r");

	//$headerline = array_shift($lines);
	$headerline = fgets($fh);
	// UNICODE: ignore BOM (byte order mark)
	if (substr($headerline, 0, 2) == "\xff\xfe") $headerline = substr($headerline, 2);
	elseif (substr($headerline, 0, 3) == "\xef\xbb\xbf") $headerline = substr($headerline, 3);
	//for($i=0;$i<4;$i++) print ("$i=".dechex(ord($headerline{$i}))."<br>\n");

	$header = explode(",", $headerline);
	//print_r($header);
	if (count($header) > 1) {
		$csv = 1;
		$delimiter = ",";
	} else {
		$header = explode("\t", $headerline);
		//print "reparse as tsv...<br>\n";
		//print_r($header);
		$tsv = 1;
		$delimiter = "\t";
	}
	if (count($header) <= 1) {
		myresult(0, "File format error.  Use either tab separated list or comma separated list");
		return;
	}

	// find header index
	$search_hdrs = array('itemno','title','author','volume','shelf','publisher','seriesno','subject');
	foreach ($header as $i=>$h) {
		$h = trim($h);
		$found = 0;
		foreach ($search_hdrs as $k) 
		{
			if (isset($idx[$k])) continue;
			//print "<br>$h~$mhdr[$k]~idx[$k]~".strcasecmp($h,$mhdr[$k])."~$i\n";
			if (strcasecmp($h,$mhdr[$k])==0) {
				$idx[$k] = $i;
				$found = 1;
				break;
			}
		}
		if (!$found) {
			myresult (0, "Warning: I don't understand the field with header $h, ignoring it...");
			//print "uncatched : $i $h\n";
		}
	}
	//print_r($idx);
	
	// required fields, or quit (bid can be optional)
	if (!isset($idx['title'])) {
		myresult(0,"Incorrect format, can't find title");
		return;
	}
	if (!isset($idx['itemno']) || (!isset($idx['volume']) && !isset($idx['seriesno']))) {
		myresult(0,"Incorrect format, can't find either itemno or (seriesno + vol)");
		return;
	}

	//critical selection ( locking )
	if (! ($result = mysql_query("SELECT GET_LOCK(\"temp_booklist\", 5)"))) {
		myresult(0,"Error in get_lock for temp_booklist : ".mysql_error());
		return;
	}
	list($lockok, ) = mysql_fetch_array($result);
	if (!$lockok) {
		myresult(0,"Failed to obtain lock for temp_booklist");
		return;
	}


	$ss = array();
	$scount = 0;
	$vcount = 0;

	//foreach ($lines as $l)
	while ($fields = fgetcsv($fh, 1000, $delimiter, '"'))
	{

		$bid = $fields[$idx['itemno']];
		$title = $fields[$idx['title']];
		if (isset($idx['author'])) $author = $fields[$idx['author']];
		if (isset($idx['volume'])) $volume = $fields[$idx['volume']];
		if (isset($idx['publisher'])) $publisher = $fields[$idx['publisher']];
		if (isset($idx['shelf'])) $shelf = $fields[$idx['shelf']];
		if (isset($idx['subject'])) $subject = $fields[$idx['subject']];

		if (!$bid) continue;
		//print ".\n";
		$seriesno = substr($bid, $sno_fstart, $sno_fend-$sno_fstart+1);
		if (!$volume) $volume = substr($bid, $vol_fstart, $vol_fend-$vol_fstart+1);

		//sanity check
		if (!isset($volume) || empty($volume)) continue;
		if (!$title) continue;
		
		//separate item with different publisher to different series
		$s = $seriesno;
		if ($mconf['strict_pub'] == 'yes') {
			$s .= crc32($publisher);
		}

		if (! $ss[$s] )
		{
			// quote breaking
			//if (preg_match('/^"(.*)"$/', $title, $m)) $title = $m[1];
			//if (preg_match('/^"(.*)"$/', $author, $m)) $author = $m[1];
			//if (preg_match('/^"(.*)"$/', $publisher, $m)) $publisher = $m[1];
			$ss[$s] = array();
			$ss[$s]['seriesno'] = $seriesno;
			$ss[$s]['title'] = $title;
			$ss[$s]['author'] = $author;
			$ss[$s]['publisher'] = $publisher;
			$ss[$s]['shelf'] = $shelf;
			$ss[$s]['subject'] = $subject;
			$ss[$s]['volume'] = array();
			//print_r($ss);
			$scount ++;
		}

		foreach (explode(",", $volume) as $v) {
			if (is_numeric($v)) {
					$ss[$s]['volume'][$v] = array();
					//assign volume specific fields (eg. isbn) here.
					if ($ss[$s]['shelf'] != $shelf) 
						$ss[$s]['volume'][$v]['shelf'] = $shelf;
					if ($ss[$s]['publisher'] != $publisher) 
						$ss[$s]['volume'][$v]['ipublisher'] = $publisher;
			}
			elseif (preg_match('/^(\d+)\s*\-\s*(\d+)$/', $v, $m)) {
				foreach (range((int)$m[1],(int)$m[2]) as $vi) {
					$ss[$s]['volume'][$vi] = array();
					if ($ss[$s]['shelf'] != $shelf) 
						$ss[$s]['volume'][$vi]['shelf'] = $shelf;
					if ($ss[$s]['publisher'] != $publisher)
						$ss[$s]['volume'][$vi]['ipublisher'] = $publisher;
				}
			}
		}

		$vcount ++;
	}
	fclose($fh);
	unlink($file);

	////////////////////////////////////////////////////// database transaction
	odin_connect();

	//initialize tables
	if (!mysql_query("DROP TABLE IF EXISTS odin_temp_series")) {
		myresult(0,"Cannot drop odin_temp_series : ".mysql_error());
		return;
	}
	if (!mysql_query("DROP TABLE IF EXISTS odin_temp_item")) {
		myresult(0,"Cannot drop odin_temp_series : ".mysql_error());
		return;
	}
	if (!mysql_query("CREATE TABLE odin_temp_series LIKE odin_series")) {
		myresult(0,"Cannot create odin_temp_series : ".mysql_error());
		return;
	}
	if (!mysql_query("CREATE TABLE odin_temp_item LIKE odin_item")) {
		myresult(0,"Cannot create odin_temp_item : ".mysql_error());
		return;
	}

	$subjects = array();

	foreach ($ss as $sinfo)
	{
		$subjid = '';
		if($sinfo['subject'] && isset($subjects[$sinfo['subject']])) {
			$subjid = $subjects[$sinfo['subject']];
		}
		elseif ($sinfo['subject'] && !isset($subjects[$sinfo['subject']])) {
			$query = "SELECT id FROM odin_series_subj WHERE subjcode='".mysql_escape_string($sinfo['subject'])."'";
			if ($result = mysql_query($query)) {
				if (mysql_num_rows($result)) {
					list($subjid) = mysql_fetch_array($result, MYSQL_NUM);
					$subjects[$sinfo['subject']] = $subjid;
				}
				else {
					$query = "INSERT INTO odin_series_subj SET subjcode='".mysql_escape_string($sinfo['subject'])."'";
					if (mysql_query($query) && (mysql_affected_rows() == 1)) {
						$subjid = mysql_insert_id();
						$subjects[$sinfo['subject']] = $subjid;
					} else {
						myresult(0,"Cannot insert odin_series_subj : ".mysql_error());
					}
				}
			} else {
				myresult(0,"Cannot query odin_series_subj : ".mysql_error());
			}
		}
			
		$query = "INSERT INTO odin_temp_series ";
		$query .= "SET seriesno='".$sinfo['seriesno']."'";
		$query .= ", title='".mysql_escape_string($sinfo['title'])."'";
		if ($sinfo['author']) $query .= ", author='". mysql_escape_string($sinfo['author'])."'";
		if ($sinfo['publisher']) $query .= ", publisher='". mysql_escape_string($sinfo['publisher'])."'";
		if ($sinfo['shelf']) $query .= ", shelf='". mysql_escape_string($sinfo['shelf'])."'";
		if ($subjid) $query .= ", subject=$subjid";
		if (mysql_query($query) && (mysql_affected_rows() == 1)) {
			// insert ok
		} else {
			myresult (0, "Insert odin_temp_series failed : " . mysql_error() . "<br>$query");
			// unlikely error.  something must be wrong.  quiting
			//return;
		}
		$series = mysql_insert_id();

		foreach ($sinfo['volume'] as $vol=>$vinfo)
		{
			$itemno = form_itemno($itemno_fmt, $sinfo['seriesno'], $vol, $copy);
			$query = "INSERT INTO odin_temp_item ";
			$query .= "SET volume=$vol";
			$query .= ", itemno='$itemno'";
			$query .= ", series=$series";
			if ($vinfo['shelf']) $query .= ", shelf='". mysql_escape_string($vinfo['shelf'])."'";
			if ($vinfo['ipublisher']) $query .= ", ipublisher='". mysql_escape_string($vinfo['ipublisher'])."'";
			// TODO: design a mechanism to handle multiple copies
			// TODO: insert more volume specific fields here
			if (mysql_query($query) && (mysql_affected_rows() == 1)) {
				// insert ok
			} else {
				myresult (0, "Insert odin_temp_volume failed : " . mysql_error() . "<br>$query");
				// unlikely error.  something must be wrong.  quiting
				//return;
			}
		}

	}

	$query = "INSERT INTO odin_log SET type='UploadBooklist',"
		." staff='{$_SESSION['userinfo']['id']}',"
		." ipaddr='{$_SERVER['REMOTE_ADDR']}',"
		." itemdesc='opt=uploadfile NumOfSeries=$scount NumOfLines=$vcount',"
		." timestamp=NULL";
	if (!mysql_query($query) || !mysql_affected_rows()) {
		myresult (0, "(Warning) Insert into odin_log failed : ". mysql_error() ."<br>$query");
	}
	myresult (1, "Parsed $scount series in $vcount lines");
	
	// save config
	if ($sno_fstart != $sno_fend) $mconf['sno_field'] = $sno_fstart."-".$sno_fend;
	else $mconf['sno_field'] = $sno_fstart;
	if ($vol_fstart != $vol_fend) $mconf['vol_field'] = $vol_fstart."-".$vol_fend;
	else $mconf['vol_field'] = $vol_fstart;
	$mconf['itemno_fmt'] = $itemno_fmt[0];
	save_config_type($mconf , 'upload_booklist');
	save_config_type($mhdr , 'upload_booklist_hdr');

	return 1;
}


//////////////////////////////////////////////////////////////////////////
function browse_temp_series() {

	$list = new SeriesList("odin_temp_series", "odin_temp_item");
	$list->page = $_GET['page'];
	$list->browse();
	
	if ($list->no_of_match >= 1) {
		if ($_GET['query']) $displaystr .= " '{$_GET['query']}'";
		if ($_GET['page']) $displaystr .= " pg. {$_GET['page']}";
		$_SESSION['navbar']->register("result", "get", $displaystr);
		return $list;
	}
	else
	{
		myresult(0, "No data in import booklist");
	}
	
}

//////////////////////////////////////////////////////////////////////////
function volume_info($vid) {

	$item = new Item("odin_temp_series", "odin_temp_item", "");
	$item->id = $vid;
	if (!$item->loadFromDB()) return;
	$_SESSION['navbar']->register("seriesinfo", "{$GLOBALS['script']}?sid={$item->series}", trim("$series->seriesno $series->title"));
	$_SESSION['navbar']->register("volumeinfo", "{$GLOBALS['script']}?vid={$item->id}", $item->svolume);
	return $item;
}

//////////////////////////////////////////////////////////////////////////
function series_info($sid) {

	$series = new Series("odin_temp_series", "odin_temp_item");
	$series->id = $sid;
	if (!$series->loadFromDB()) return;
	$series->loadVolumes();
	$_SESSION['navbar']->register("seriesinfo", "{$GLOBALS['script']}?sid=$sid", trim("$series->seriesno $series->title"));
	return $series;
	
}


?>