<?php
//  ------------------------------------------------------------------------ //
// 本模組由 tad 製作
// 製作日期：2008-06-25
// $Id: function.php,v 1.1 2008/05/14 01:22:08 tad Exp $
// ------------------------------------------------------------------------- //

/*-----------引入檔案區--------------*/
include "../../../include/cp_header.php";
include "../function.php";
include_once XOOPS_ROOT_PATH."/Frameworks/art/functions.php";
include_once XOOPS_ROOT_PATH."/Frameworks/art/functions.admin.php";

/*-----------function區--------------*/
//列出所有tad_form_main資料
function list_tad_form_main(){
	global $xoopsDB,$xoopsModule;
	$MDIR=$xoopsModule->getVar('dirname');
	$sql = "select * from ".$xoopsDB->prefix("tad_form_main")." order by post_date desc";

	//PageBar(資料數, 每頁顯示幾筆資料, 最多顯示幾個頁數選項);
	$result = $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	$total=$xoopsDB->getRowsNum($result);

	$navbar = new PageBar($total, 20, 10);
	$mybar = $navbar->makeBar();
	$bar= sprintf(_BP_TOOLBAR,$mybar['total'],$mybar['current'])."{$mybar['left']}{$mybar['center']}{$mybar['right']}";
	$sql.=$mybar['sql'];

	$result = $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());


	$data="
	<script>
	function delete_tad_form_main_func(ofsn){
		var sure = window.confirm('"._BP_DEL_CHK."');
		if (!sure)	return;
		location.href=\"{$_SERVER['PHP_SELF']}?op=delete_tad_form_main&ofsn=\" + ofsn;
	}
	</script>
	<table id='tbl'>
	<tr><th>"._MA_TADFORM_OFSN."</th>
	<th>"._MA_TADFORM_TITLE."</th>
	<th>"._MA_TADFORM_RESULT."</th>
	<th>"._MA_TADFORM_START_DATE."</th>
	<th>"._MA_TADFORM_END_DATE."</th>
	<th>"._BP_FUNCTION."</th>
	<th>"._MA_TADFORM_COL_NUM."</th>
	<th>"._MA_TADFORM_OPTIONS."</th>
	</tr>
	<tbody>";
	

	$sign_mems=get_form_count($ofsn);
	$cols_num=get_form_col_count($ofsn);
		
	while(list($ofsn,$title,$start_date,$end_date,$content,$uid,$post_date,$enable)=$xoopsDB->fetchRow($result)){

		$enable_tool=($enable=='1')?"<a href='index.php?op=set_form_status&ofsn=$ofsn&enable=0'>"._MA_TADFORM_COL_ENABLE."</a>":"<a href='index.php?op=set_form_status&ofsn=$ofsn&enable=1'>"._MA_TADFORM_COL_ACTIVE."</a>";
		
		$start_date=date("Y-m-d",xoops_getUserTimestamp(strtotime($start_date)));
		$end_date=date("Y-m-d",xoops_getUserTimestamp(strtotime($end_date)));
	
		$start_date=substr($start_date,0,10);
		$end_date=substr($end_date,0,10);
		
		$pic=($enable=='1')?"001_06.gif":"001_05.gif";
		
		$counter=(empty($sign_mems[$ofsn]))?"0":$sign_mems[$ofsn];
		
		$data.="<tr>
		<td><img src='../images/$pic' align='absmiddle' hspace=6>{$ofsn}</td>
		<td><a href='../index.php?op=sign&ofsn=$ofsn'>{$title}</a></td>
		<td><a href=\"result.php?ofsn=$ofsn\">".sprintf(_MA_TADFORM_SIGN_MEMS,$counter)."</a> |
		<a href=\"index.php?op=excel&ofsn=$ofsn\">Excel</a> |
		<a href=\"mail.php?ofsn=$ofsn\">mail</a> |
		</td>
		<td>{$start_date}</td>
		<td>{$end_date}</td>
		<td>
		<a href='add.php?op=tad_form_main_form&ofsn=$ofsn' style='color:#0066CC;'>"._BP_EDIT."</a> |
		<a href='index.php?op=copy&ofsn=$ofsn' style='color:#009900;'>"._MA_TADFORM_COPY_FORM."</a> |
		<a href=\"javascript:delete_tad_form_main_func($ofsn);\" style='color:red;'>"._BP_DEL."</a> |
		$enable_tool
		</td>
		<td class='center'>{$cols_num[$ofsn]}</td>
		<td>
		<a href='add.php?op=edit_opt&ofsn={$ofsn}' style='color:#993399;'>"._BP_ADD."</a> |
    <a href='add.php?op=edit_all_opt&ofsn={$ofsn}' style='color:#996600;'>"._BP_EDIT."</a></td>
		</tr>";
	}
	$data.="
	<tr>
	<td colspan=8 class='bar'>{$bar}</td></tr>
	</tbody>
	</table>";
	
	$data=div_3d("",$data,"corners");
	
	return $data;
}


//檢查填報人數
function get_form_count(){
	global $xoopsDB;
	$sql = "select ofsn,count(*) from ".$xoopsDB->prefix("tad_form_fill")." group by ofsn";
	$result=$xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	while(list($ofsn,$count)=$xoopsDB->fetchRow($result)){
		$counter[$ofsn]=$count;
	}
	return $counter;
}



//檢查填報題數
function get_form_col_count(){
	global $xoopsDB;
	$sql = "select ofsn,count(*) from ".$xoopsDB->prefix("tad_form_col")." group by ofsn";
	$result=$xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	while(list($ofsn,$count)=$xoopsDB->fetchRow($result)){
		$counter[$ofsn]=$count;
	}
	return $counter;
}

//刪除tad_form_main某筆資料資料
function delete_tad_form_main($ofsn=""){
	global $xoopsDB;
	//先找出有哪些人填了
	$sql = "select ssn from ".$xoopsDB->prefix("tad_form_fill")." where ofsn='$ofsn'";
	$result=$xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	while(list($ssn)=$xoopsDB->fetchRow($result)){
	
	  //刪了填報內容
		$sql = "delete from ".$xoopsDB->prefix("tad_form_value")." where ssn='$ssn'";
		$xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
		
		//刪了填寫人資料
		$sql = "delete from ".$xoopsDB->prefix("tad_form_fill")." where ssn='$ssn'";
		$xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	}

	//刪掉欄位
	$sql = "delete from ".$xoopsDB->prefix("tad_form_col")." where ofsn='$ofsn'";
	$xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	
	//最後刪掉問卷
	$sql = "delete from ".$xoopsDB->prefix("tad_form_main")." where ofsn='$ofsn'";
	$xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
}



//複製問卷
function copy_form($ofsn=""){
	global $xoopsDB;
	
	//讀出原有資料
	$sql = "select `title`, `start_date`, `end_date`, `content`, `uid`, `post_date`, `enable`, `sign_group` from ".$xoopsDB->prefix("tad_form_main")." where ofsn='$ofsn'";
	$result=$xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	list($title,$start_date,$end_date,$content,$uid,$post_date,$enable,$sign_group)=$xoopsDB->fetchRow($result);
	
	//寫入新問卷
  $sql = "insert into ".$xoopsDB->prefix("tad_form_main")." (`title`,`start_date`,`end_date`,`content`,`uid`,`post_date`,`enable`,`sign_group`) values('copy_{$title}','{$start_date}','{$end_date}','{$content}','{$uid}',now(),'0','{$sign_group}')";
	$xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	
	//取得最後新增資料的流水編號
	$new_ofsn=$xoopsDB->getInsertId();

	//讀出選項
	$sql = "select `title`, `descript`, `kind`, `size`, `val`, `chk`, `func`, `sort` from ".$xoopsDB->prefix("tad_form_col")." where ofsn='$ofsn'";
	$result=$xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	while(list($title,$descript,$kind,$size,$val,$chk,$func,$sort)=$xoopsDB->fetchRow($result)){
		//寫入選項
    $sql = "insert into ".$xoopsDB->prefix("tad_form_col")." (`ofsn`,`title`,`descript`,`kind`,`size`,`val`,`chk`,`func`,`sort`) values('{$new_ofsn}','{$title}','{$descript}','{$kind}','{$size}','{$val}','{$chk}','{$func}','{$sort}')";
		$xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
	}
}


//下載Excel檔
function download_excel($ofsn=""){
	global $xoopsDB;
	$main="<Row>
	<Cell><Data ss:Type=\"String\">"._MA_TADFORM_COL_WHO."</Data></Cell>
	";
	$sql = "select csn,title,kind,func from ".$xoopsDB->prefix("tad_form_col")." where ofsn='{$ofsn}' order by sort";
	$result = $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
  while(list($csn,$title,$kind,$func)=$xoopsDB->fetchRow($result)){
    $main.="<Cell><Data ss:Type=\"String\">$title</Data></Cell>
		";
    //$ff[$csn]=$func;
    //$tt[$csn]=$title;
    $kk[$csn]=$kind;
  }

	$main.="<Cell><Data ss:Type=\"String\">"._MA_TADFORM_SIGN_DATE."</Data></Cell>
	</Row>
	";
	
	$sql = "select ssn,uid,man_name,email,fill_time from ".$xoopsDB->prefix("tad_form_fill")." where ofsn='{$ofsn}' order by fill_time desc";
	$result = $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
  while(list($ssn,$uid,$man_name,$email,$fill_time)=$xoopsDB->fetchRow($result)){
  
   	$main.="<Row>
		 <Cell><Data ss:Type=\"String\">$man_name</Data></Cell>
		 ";

		$sql2 = "select a.csn,a.val from ".$xoopsDB->prefix("tad_form_value")." as a,".$xoopsDB->prefix("tad_form_col")." as b where a.csn=b.csn and a.ssn='{$ssn}'  order by b.sort";

		$result2 = $xoopsDB->query($sql2) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
		$all="";
		while(list($csn,$val)=$xoopsDB->fetchRow($result2)){
       $val=char_replace($val);
		  if($kk[$csn]=='textarea'){
      	$all.="<Cell><Data ss:Type=\"String\">".strip_tags($val)."</Data></Cell>
				";
   		}elseif($kk[$csn]=='fck'){
			  $all.="<Cell><Data ss:Type=\"String\">".strip_tags($val)."</Data></Cell>
				";
			}elseif($kk[$csn]=='checkbox'){
			  $all.="<Cell><Data ss:Type=\"String\">".str_replace(";","&#10;",$val)."</Data></Cell>
				";
      }else{
      	$all.="<Cell><Data ss:Type=\"String\">$val</Data></Cell>
				";
			}

    }
    
    $fill_time=date("Y-m-d H:i:s",xoops_getUserTimestamp(strtotime($fill_time)));
   	$main.="$all
		 <Cell><Data ss:Type=\"String\">$fill_time</Data></Cell>
		 </Row>
		 ";
	}

	$form_main=get_tad_form_main($ofsn);
  $ff=sprintf(_MA_TADFORM_EXCEL_TITLE,$form_main['title']).".xls";
	$dl_name=(is_utf8($ff))?$ff:iconv("Big5","UTF-8",$ff);
  $dl_name=(eregi("MSIE",$_SERVER["HTTP_USER_AGENT"]))?urlencode($dl_name):$dl_name;

	$charset=(is_utf8(_MA_TADFORM_FONT1))?"utf-8":"Big5";


	header("Content-Type: text/xml; charset={$charset}");
	header("Content-Disposition: attachment; filename={$dl_name}");
	echo "<?xml version=\"1.0\" encoding=\"{$charset}\"?>
<?mso-application progid=\"Excel.Sheet\"?>
<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"
 xmlns:o=\"urn:schemas-microsoft-com:office:office\"
 xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
 xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"
 xmlns:html=\"http://www.w3.org/TR/REC-html40\">
  <Styles>
  <Style ss:ID=\"Default\" ss:Name=\"Normal\">
   <Alignment ss:Vertical=\"Center\"/>
   <Borders/>
   <Font ss:FontName=\""._MA_TADFORM_FONT1."\" x:CharSet=\"136\" x:Family=\"Roman\" ss:Size=\"12\"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID=\"s21\" ss:Name=\""._MA_TADFORM_LINK1."\">
   <Font ss:FontName=\""._MA_TADFORM_FONT1."\" x:CharSet=\"136\" x:Family=\"Roman\" ss:Size=\"12\"
    ss:Color=\"#0000FF\" ss:Underline=\"Single\"/>
  </Style>
 </Styles>
 <Worksheet ss:Name=\"Sheet1\">
  <Table>
   $main</Table>
 </Worksheet>
</Workbook>";

	exit;
}

function char_replace($char=""){
		$char=str_replace("<","(",$char);
		$char=str_replace(">",")",$char);
		return $char;
}


//下載OpenOffice calc檔
function download_ods($ofsn=""){
	global $xoopsDB;
	
	$number_columns_repeated=0;
	
  $col_title="<table:table-cell office:value-type=\"string\"><text:p>"._MA_TADFORM_COL_WHO."</text:p></table:table-cell>";
	$sql = "select csn,title,kind,func from ".$xoopsDB->prefix("tad_form_col")." where ofsn='{$ofsn}' order by sort";
	$result = $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
  while(list($csn,$title,$kind,$func)=$xoopsDB->fetchRow($result)){
    $col_title.="<table:table-cell office:value-type=\"string\"><text:p>$title</text:p></table:table-cell>";
    $kk[$csn]=$kind;
    $number_columns_repeated++;
  }
	$col_title.="<table:table-cell office:value-type=\"string\"><text:p>"._MA_TADFORM_SIGN_DATE."</text:p></table:table-cell>";
	
	
  $col_val="";
	$sql = "select ssn,uid,man_name,email,fill_time from ".$xoopsDB->prefix("tad_form_fill")." where ofsn='{$ofsn}' order by fill_time desc";
	$result = $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
  while(list($ssn,$uid,$man_name,$email,$fill_time)=$xoopsDB->fetchRow($result)){

   	$col_val.="<table:table-cell office:value-type=\"string\"><text:p>{$man_name}</text:p></table:table-cell>";

		$sql2 = "select a.csn,a.val from ".$xoopsDB->prefix("tad_form_value")." as a,".$xoopsDB->prefix("tad_form_col")." as b where a.csn=b.csn and a.ssn='{$ssn}'  order by b.sort";

		$result2 = $xoopsDB->query($sql2) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());
		$all="";
		while(list($csn,$val)=$xoopsDB->fetchRow($result2)){
       $val=char_replace($val);
		  if($kk[$csn]=='textarea'){
      	$all.="<table:table-cell office:value-type=\"string\"><text:p>".strip_tags($val)."</text:p></table:table-cell>";
   		}elseif($kk[$csn]=='fck'){
			  $all.="<table:table-cell office:value-type=\"string\"><text:p>".strip_tags($val)."</text:p></table:table-cell>";
			}elseif($kk[$csn]=='checkbox'){
			  $all.="<table:table-cell office:value-type=\"string\"><text:p>{$val}</text:p></table:table-cell>";
      }else{
      	$all.="<table:table-cell office:value-type=\"string\"><text:p>{$val}</text:p></table:table-cell>";
			}

    }
    $fill_time=date("Y-m-d H:i:s",xoops_getUserTimestamp(strtotime($fill_time)));
   	$col_val.="{$all}<table:table-cell office:value-type=\"string\"><text:p>{$fill_time}</text:p></table:table-cell>";
	}

	$charset=(is_utf8(_MA_TADFORM_FONT1))?"utf-8":"Big5";
	$col_title=($charset!="utf-8")?iconv("Big5","UTF-8",$col_title):$col_title;
	$col_val=($charset!="utf-8")?iconv("Big5","UTF-8",$col_val):$col_val;
	$xml=file_get_contents("../class/ods/content.xml");
	$xml=str_replace("{number_columns_repeated}",$number_columns_repeated,$xml);
	$xml=str_replace("{col_title}",$col_title,$xml);
	$xml=str_replace("{col_val}",$col_val,$xml);

	$form_main=get_tad_form_main($ofsn);
  $ff=sprintf(_MA_TADFORM_EXCEL_TITLE,$form_main['title']).".xls";
	$dl_name=(is_utf8($ff))?$ff:iconv("Big5","UTF-8",$ff);
  $dl_name=(eregi("MSIE",$_SERVER["HTTP_USER_AGENT"]))?urlencode($dl_name):$dl_name;



	header("Content-Type: text/xml; charset={$charset}");
	header("Content-Disposition: attachment; filename={$dl_name}");
	echo "<?xml version=\"1.0\" encoding=\"{$charset}\"?>
<?mso-application progid=\"Excel.Sheet\"?>
<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"
 xmlns:o=\"urn:schemas-microsoft-com:office:office\"
 xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
 xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"
 xmlns:html=\"http://www.w3.org/TR/REC-html40\">
  <Styles>
  <Style ss:ID=\"Default\" ss:Name=\"Normal\">
   <Alignment ss:Vertical=\"Center\"/>
   <Borders/>
   <Font ss:FontName=\""._MA_TADFORM_FONT1."\" x:CharSet=\"136\" x:Family=\"Roman\" ss:Size=\"12\"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID=\"s21\" ss:Name=\""._MA_TADFORM_LINK1."\">
   <Font ss:FontName=\""._MA_TADFORM_FONT1."\" x:CharSet=\"136\" x:Family=\"Roman\" ss:Size=\"12\"
    ss:Color=\"#0000FF\" ss:Underline=\"Single\"/>
  </Style>
 </Styles>
 <Worksheet ss:Name=\"Sheet1\">
  <Table>
   $main</Table>
 </Worksheet>
</Workbook>";

	exit;
}
/*-----------執行動作判斷區----------*/
$op = (!isset($_REQUEST['op']))? "main":$_REQUEST['op'];

switch($op){

	case "excel":
	download_excel($_GET['ofsn']);
  exit;
	break;
	
	
	//輸入表格
	case "tad_form_main_form";
	$main=tad_form_main_form($_GET['ofsn']);
	break;

	//刪除資料
	case "delete_tad_form_main";
	delete_tad_form_main($_GET['ofsn']);
	header("location: {$_SERVER['PHP_SELF']}");
	break;

	//變更狀態資料
	case "set_form_status";
	set_form_status($_GET['ofsn'],$_GET['enable']);
	header("location: {$_SERVER['PHP_SELF']}");
	break;
	

	//複製問卷
	case "copy";
	copy_form($_GET['ofsn']);
	header("location: {$_SERVER['PHP_SELF']}");
	break;


	//預設動作
	default:
	$main=list_tad_form_main(1);
	break;

}

/*-----------秀出結果區--------------*/
xoops_cp_header();
echo "<link rel='stylesheet' type='text/css' media='screen' href='../module.css' />";
loadModuleAdminMenu(0);
echo $main;
xoops_cp_footer();

?>
