<?PHP 
//*****************************************************************************
//* 
//* プログラム名：め〜るNiポン！V2
//* プログラムID：LIST1901.php
//* 機能        ：ＣＳＶファイルにデータを吐き出す
//* 
//*****************************************************************************
header("Content-type: text/html; charset=EUC-JP");

//***************************
//*　インクルードファイル
//***************************
include("./include/jcode.phps");
include("./include/session_start.inc");
include("./include/SessionChk.inc");
include("./include/dbcon.inc");

//**********  表示項目 ************//
$strSQL = "SELECT Disp_id,Disp_name FROM Mst_disp ORDER BY Disp_id ASC";
$objRecSet1 = pg_exec($strSQL);
if($objRecSet1==false){
	echo("SQL実行に失敗しました(SELECT)");
	exit;
}
$strKenmei = pg_result($objRecSet1,2,"Disp_name");

//**********  回答文言 ************//
$strSQL = "SELECT * FROM answer_data WHERE mail_id = " . $_POST["mail_id"];
$objRecSet2 = pg_exec($strSQL);
if($objRecSet2==false){
	echo("SQL実行に失敗しました(SELECT)");
	exit;
}
if(pg_numrows($objRecSet2)!=0){
	$objData = pg_fetch_object($objRecSet2,0);
	$select_cnt=$objData->select_nm;
	$SYORI_MODE = $objData->name50_nm;

	for($intIdx = 1; $intIdx <= $objData->select_nm; $intIdx++){
		$name_id[$intIdx]=pg_result($objRecSet2, 0, "name" . $intIdx . "_id");
		$name_nm[$intIdx]=pg_result($objRecSet2, 0, "name" . $intIdx . "_nm");
	}
}else{
	$select_cnt=0;
}

//**********  配信情報 ************//
$strSQL = "SELECT Message_date, Message_time, Subject_name, Contents, Admin_cd, send_date, send_alarm, send_type, " .
		  "reserva_flg, reserva_date, reserva_time FROM Mail_data WHERE Mail_id = " . (int)$_POST["mail_id"];
$objRecSet3 = pg_exec($strSQL);
if($objRecSet3==false){
	echo("SQL実行に失敗しました(SELECT)");
	exit;
}
$intDataCount = pg_numrows($objRecSet3);
$Mail_date = pg_result($objRecSet3,0,"Message_date");
$Mail_time = pg_result($objRecSet3,0,"Message_time");
$Subject_name = pg_result($objRecSet3,0,"Subject_name");
$Contents = pg_result($objRecSet3,0,"Contents");
$Admin_cd = pg_result($objRecSet3,0,"Admin_cd");
$send_date = pg_result($objRecSet3,0,"send_date");
$Send_alarm = pg_result($objRecSet3,0,"send_alarm");
$send_type = pg_result($objRecSet3,0,"send_type");
$reserva_flg = pg_result($objRecSet3,0,"reserva_flg");
$reserva_date = pg_result($objRecSet3,0,"reserva_date");
$reserva_time = pg_result($objRecSet3,0,"reserva_time");

//**********  既読・未読自動再送情報 ************//
$Auto_Name="";
$strSQL = "SELECT * FROM kidok_data WHERE mail_id = " . (int)$_POST["mail_id"] . " ORDER BY kidok_data.check_id";
$objRecSet4 = pg_exec($strSQL);
if($objRecSet4==false){
	echo("SQL実行に失敗しました(SELECT)");
	exit;
}
$intKidokCount = pg_numrows($objRecSet4);
if($intKidokCount!=0){
	$Kidok_date = pg_result($objRecSet4,0,"Message_date");
	$Kidok_time = pg_result($objRecSet4,0,"Message_time");
	$Send_flg = pg_result($objRecSet4,0,"send_flg");
}

//**********  グループ情報 ************//
$strSQL = "SELECT group_info.syain_cd,group_info.group_id,mst_group.group_name FROM group_info,mst_group " .
					"WHERE group_info.group_id = mst_group.group_id ORDER BY group_info.syain_cd";
$objRecSet5 = pg_exec($strSQL);
if($objRecSet5==false){
	echo("SQL実行に失敗しました(SELECT)");
	exit;
}
$intInfo_Count = pg_numrows($objRecSet5);

for($intIdx = 0; $intIdx < $intInfo_Count ; $intIdx++) {
	$objData = pg_fetch_object($objRecSet5, $intIdx);
	$intInfo_Cd[$intIdx] = $objData->syain_cd;
	$strGroup_Name[$intIdx] = JcodeConvert($objData->group_name, 1, 1);
}

//**********  ユーザ情報 ************//
$strSQL = "SELECT Name_kj_sei, Name_kj_mei FROM Syain_tbl WHERE Syain_cd = " . $Admin_cd . " ORDER BY Syain_id ASC";
$objRecSet6 = pg_exec($strSQL);
if($objRecSet6==false){
	echo("SQL実行に失敗しました(SELECT)");
	exit;
}
$intDataCount = pg_numrows($objRecSet6);
$Admin_sei = pg_result($objRecSet6,0,"Name_kj_sei");
$Admin_mei = pg_result($objRecSet6,0,"Name_kj_mei");

//**********  個人別回答内容 ************//
$Mcnt=0;
$Kcnt=0;
$Ncnt=0;
$Scnt=0;
$strSQL = "SELECT Mail_info.Check_flg, Mail_info.Kousin, Mail_info.comment, Syain_tbl.Syain_id, Syain_tbl.Syain_cd, Syain_tbl.Name_kj_sei, Syain_tbl.Name_kj_mei, " .
					"Syain_tbl.mail_addr, Syain_tbl.pc_mail_addr, mail_send_tbl.send_flg FROM (Mail_info left join Syain_tbl on Mail_info.Syain_cd = Syain_tbl.Syain_cd) " .
					"left join mail_send_tbl on Mail_info.Mail_id = mail_send_tbl.Mail_id and Mail_info.Syain_cd = mail_send_tbl.Syain_cd ".
					"WHERE Mail_info.Mail_id = " . (int)$_POST["mail_id"] . " ORDER BY Mail_info.check_flg , Syain_tbl.name_kn_sei, Syain_tbl.name_kn_mei, Syain_tbl.Syain_cd ASC";
$objRecSet7 = pg_exec($strSQL);
if($objRecSet7==false){
	echo("SQL実行に失敗しました(SELECT)");
	exit;
}
$intDataCount7 = pg_numrows($objRecSet7);
for($intIdx = 1; $intIdx <= $select_cnt; $intIdx++){
	$Cnt[$intIdx]=0;	
}
for ($intCount = 0; $intCount < $intDataCount7; $intCount++) {
	$Syain_id[$intCount] = pg_result($objRecSet7,$intCount,"Syain_id");
	$Syain_cd[$intCount] = pg_result($objRecSet7,$intCount,"Syain_cd");
	$Name_kj_sei[$intCount] = pg_result($objRecSet7,$intCount,"Name_kj_sei");
	$Name_kj_mei[$intCount] = pg_result($objRecSet7,$intCount,"Name_kj_mei");
	$mail_addr[$intCount] = pg_result($objRecSet7,$intCount,"mail_addr");
	$pc_mail_addr[$intCount] = pg_result($objRecSet7,$intCount,"pc_mail_addr");
	$Check_flg[$intCount] = pg_result($objRecSet7,$intCount,"Check_flg");
	$Send_flg[$intCount] = pg_result($objRecSet7,$intCount,"send_flg");
	//件数カウント
	if(pg_result($objRecSet7,$intCount,"Check_flg")==0){
		$Mcnt=$Mcnt + 1;
	}elseif(pg_result($objRecSet7,$intCount,"Check_flg")==98){
		$Scnt=$Scnt + 1;	
	}elseif(pg_result($objRecSet7,$intCount,"Check_flg")==99){
		$Kcnt=$Kcnt + 1;	
	}elseif(pg_result($objRecSet7,$intCount,"Check_flg")==100){
		$Ncnt=$Ncnt + 1;	
	}else{
		for($intIdx = 1; $intIdx <= $select_cnt; $intIdx++){
			if($name_id[$intIdx]==pg_result($objRecSet7,$intCount,"Check_flg")){
				$Cnt[$intIdx]=$Cnt[$intIdx] + 1;
				break;
			}
		}
	}
	$Kousin[$intCount] = pg_result($objRecSet7,$intCount,"Kousin");
	$Comment[$intCount] = pg_result($objRecSet7,$intCount,"comment");
}

//**********  回答期限 ************//
$strSQL="SELECT * FROM res_time_tbl WHERE mail_id = " . (int)$_POST["mail_id"] . " ORDER BY mail_id";
$objRecSet8 = pg_exec($strSQL);
if($objRecSet8==false){
	echo("SQL実行に失敗しました(UPDATE)");
	exit;
}
if(pg_numrows($objRecSet8)!=0){
	$objData = pg_fetch_object($objRecSet8, 0);
	$d1 = mktime (substr($objData->date,8,2), substr($objData->date,10,2) , 0 , substr($objData->date,4,2), substr($objData->date,6,2), substr($objData->date,0,4));
	$strlimit_date=date("Y年m月d日 H時i分",$d1)."まで";
	$limit_date = $objData->date;
	$NowDate = date("YmdHi",mktime(date("H"),date("i"),0,(date("m")),date("d"),date("Y")));
}else{
	$limit_date = "1";
	$strlimit_date="設定なし";
	$NowDate = "0";
}

//**********  ファイル作成 ************//
$strWk = str_replace("/","",$Subject_name);
$strWk = str_replace("|","",$strWk);
$strWk = str_replace("<","",$strWk);
$strWk = str_replace(">","",$strWk);
$strWk = str_replace("?","",$strWk);
$strWk = str_replace(":","",$strWk);
$strWk = str_replace(";","",$strWk);
$strWk = str_replace("*","",$strWk);
$strWk = str_replace("$","",$strWk);
$strWk = str_replace("&","",$strWk);
$strWk = str_replace("%","",$strWk);
$strWk = str_replace("_","",$strWk);
$strWk = str_replace(chr(92),"",$strWk);
$strWk = str_replace(chr(34),"",$strWk);


$filename = str_replace(chr(35),"",$strWk);
$filename = "め〜るNｉポン！(".$strWk.").csv";
//ブラウザ判定
$Agent = getenv( "HTTP_USER_AGENT" );
if( ereg( "MSIE", $Agent ) ){ 
		$filename = JcodeConvert($filename, 1, 2);
} elseif( ereg( "Firefox", $Agent ) ) {
} elseif( ereg( "Safari", $Agent ) ) {
} else {
	$filename = JcodeConvert($filename, 1, 2);
}
Header("Content-Disposition: attachment; filename=\"$filename\"");
Header("Content-Type: text/csv");
session_start();

//**********  配信情報 ************//
//ヘッダー行出力
$strWk="【配信情報】";
echo  JcodeConvert($strWk, 1, 2);
echo "\n";
//自動再配信日
$strWk="自動再配信";
echo  JcodeConvert($strWk, 1, 2);
echo ",";
if($intKidokCount>0){
	$strWk=substr($Kidok_date,0,4)."年".substr($Kidok_date,4,2)."月".substr($Kidok_date,6,2)."日　".substr($Kidok_time,0,2)."時".substr($Kidok_time,2,2)."分";
	echo  JcodeConvert($strWk, 1, 2);
	if($Send_flg==1){
		$strWk="　送信済み";
		echo  JcodeConvert($strWk, 1, 2);
	}else{
			$strWk="　設定済み";
		echo  JcodeConvert($strWk, 1, 2);
	}
}else{
	$strWk="未設定";
	echo  JcodeConvert($strWk, 1, 2);
}
echo "\n";
//配信日時
$strWk="配信日時";
echo  JcodeConvert($strWk, 1, 2);
echo ",";
if($reserva_date!=""){ 
	$strWk=substr($reserva_date,0,4)."年".substr($reserva_date,4,2)."月".substr($reserva_date,6,2)."日　".substr($reserva_time,0,2)."時".substr($reserva_time,2,2)."分";
	echo  JcodeConvert($strWk, 1, 2);
}else{
	if($reserva_flg!=1){ 
		$strWk=substr($send_date,0,4)."年".substr($send_date,4,2)."月".substr($send_date,6,2)."日　".substr($send_date,8,2)."時".substr($send_date,10,2)."分";
		echo  JcodeConvert($strWk, 1, 2);
	}
}
if($reserva_flg!=1){
	$strWk="　配信済み";
	echo  JcodeConvert($strWk, 1, 2);
}else{
	$strWk="　配信予定";
	echo  JcodeConvert($strWk, 1, 2);
	if($reserva_date==""){
		$strWk="　なし";
		echo  JcodeConvert($strWk, 1, 2);
	}
}
echo "\n";
//作成者
$strWk="作成者";
echo  JcodeConvert($strWk, 1, 2);
echo ",";
$strWk=$Admin_sei."　".$Admin_mei;
echo  JcodeConvert($strWk, 1, 2);
echo "\n";
//開催日時
$strWk="開催日時";
echo  JcodeConvert($strWk, 1, 2);
echo ",";
if(intval(substr($Mail_date,4,2))!=0 && intval(substr($Mail_date,6,2))!=0){ 
		//日付
		$strWk=substr($Mail_date,0,4)."年".substr($Mail_date,4,2)."月".substr($Mail_date,6,2)."日　";
		echo  JcodeConvert($strWk, 1, 2);
}
if(mb_strlen($Mail_time)>0){
		//時
		$strWk=substr($Mail_time,0,2);
		echo  JcodeConvert($strWk, 1, 2);
		$strWk="時";
		echo  JcodeConvert($strWk, 1, 2);
}
if(mb_strlen($Mail_time)>2){
		//分
		$strWk=substr($Mail_time,2,2);
		echo  JcodeConvert($strWk, 1, 2);
		$strWk="分";
		echo  JcodeConvert($strWk, 1, 2);
}
echo "\n";
//送信先種類
$strWk="送信先種類";
echo  JcodeConvert($strWk, 1, 2);
echo ",";
if($send_type==0){
	$strWk="携帯／ＰＣ";
	echo  JcodeConvert($strWk, 1, 2);
}elseif($send_type==1){
	$strWk="携帯";
	echo  JcodeConvert($strWk, 1, 2);
}elseif($send_type==2){
	$strWk="ＰＣ";
	echo  JcodeConvert($strWk, 1, 2);
}
echo "\n";
//回答期限
$strWk="回答期限";
echo  JcodeConvert($strWk, 1, 2);
echo ",";
echo  JcodeConvert($strlimit_date, 1, 2);
echo "\n";
//件名
echo  JcodeConvert($strKenmei, 1, 2);
echo ",";
if($Subject_name != ""){
	$strWk = str_replace(",","，",$Subject_name);
	$strWk = str_replace(chr(34)," ",$strWk);
	echo  JcodeConvert($strWk, 1, 2);
}
echo "\n";
//内容
$strWk="内容";
echo  JcodeConvert($strWk, 1, 2);
echo ",";
if($Contents != ""){
	$strWk = str_replace(chr(34),"”",$Contents);
	$strWk = chr(34) .$strWk. chr(34) ;
	$strWk = str_replace(",","，",$strWk);
	echo  JcodeConvert($strWk, 1, 2);
}
echo "\n";
echo "\n";

//**********  閲覧状況 ************//
//タイトル
$strWk="【閲覧状況】";
echo  JcodeConvert($strWk, 1, 2);
echo "\n";
//件数
$strWk="アドレス未設定";
echo  JcodeConvert($strWk, 1, 2);
echo ",";
echo  JcodeConvert($Ncnt, 1, 2);
$strWk="件";
echo  JcodeConvert($strWk, 1, 2);
echo "\n";

if($SYORI_MODE =="DM"){
	$strWk="送信済み";
	echo  JcodeConvert($strWk, 1, 2);
	echo ",";
	echo  JcodeConvert($Scnt, 1, 2);
	$strWk="件";
	echo  JcodeConvert($strWk, 1, 2);
	echo "\n";
}else{
	$strWk="未読";
	echo  JcodeConvert($strWk, 1, 2);
	echo ",";
	echo  JcodeConvert($Mcnt, 1, 2);
	$strWk="件";
	echo  JcodeConvert($strWk, 1, 2);
	echo "\n";

	$strWk="既読";
	echo  JcodeConvert($strWk, 1, 2);
	echo ",";
	echo  JcodeConvert($Kcnt, 1, 2);
	$strWk="件";
	echo  JcodeConvert($strWk, 1, 2);
	echo "\n";
	for($intIdx = 1; $intIdx <= $select_cnt; $intIdx++){
		echo  JcodeConvert($name_nm[$intIdx], 1, 2);
		echo ",";
		echo  JcodeConvert($Cnt[$intIdx], 1, 2);
		$strWk="件";
		echo  JcodeConvert($strWk, 1, 2);
		echo "\n";

	}

}
echo "\n";
echo "\n";

//**********  個人回答状況 ************//
//タイトル
$strWk="【個人回答状況】";
echo  JcodeConvert($strWk, 1, 2);
echo "\n";
//項目
$strWk="氏名,グループ,閲覧,確認日付,確認時間,コメント,送信先";
echo  JcodeConvert($strWk, 1, 2);
echo "\n";
//詳細データ出力
for ($i = 0; $i < $intDataCount7; $i++){
	//社員名
	$strWk=$Name_kj_sei[$i]."　".$Name_kj_mei[$i];
	echo  JcodeConvert($strWk, 1, 2);
	echo ",";
	//グループ名
	$strGname="";
	for($intCount1 = 0; $intCount1 < $intInfo_Count; $intCount1++){
		if ($Syain_cd[$i] == $intInfo_Cd[$intCount1]){
			if ($strGname==""){
				$strGname = $strGroup_Name[$intCount1];
			}else{
				$strGname = $strGname . "\r\n" . $strGroup_Name[$intCount1];
			}
		}
	}
	$strGname = chr(34) . $strGname . chr(34);
	echo  JcodeConvert($strGname, 1, 2);
	echo ",";
	//閲覧状況
	if($Check_flg[$i] == 0){
		$strWk="未読";
		echo  JcodeConvert($strWk, 1, 2);
	}else if($Check_flg[$i] == 98){
		$strWk="送信済み";
		echo  JcodeConvert($strWk, 1, 2);
	}else if($Check_flg[$i] == 99){
		$strWk="既読";
		echo  JcodeConvert($strWk, 1, 2);
	}else if($Check_flg[$i] == 100){
		if($send_type == 0){
			if($mail_addr[$i]!="" || $pc_mail_addr[$i]!=""){
				$strWk="未送信";
				echo  JcodeConvert($strWk, 1, 2);
  	 	}else{
				$strWk="ｱﾄﾞﾚｽ無";
				echo  JcodeConvert($strWk, 1, 2);
			}
	}elseif($send_type == 1){
		if($mail_addr[$i]!=""){
			$strWk="未送信";
			echo  JcodeConvert($strWk, 1, 2);
		}else{
			$strWk="ｱﾄﾞﾚｽ無";
			echo  JcodeConvert($strWk, 1, 2);
		}
	}elseif($send_type == 2){
			if($pc_mail_addr[$i]!=""){
				$strWk="未送信";
				echo  JcodeConvert($strWk, 1, 2);
			}else{
				$strWk="ｱﾄﾞﾚｽ無";
				echo  JcodeConvert($strWk, 1, 2);
			}
	}else{
				$strWk="ｱﾄﾞﾚｽ無";
				echo  JcodeConvert($strWk, 1, 2);
	}
	}else{
		for ($j = 0; $j <= $select_cnt; $j++){
			if($name_id[$j] == $Check_flg[$i]){
			echo JcodeConvert($name_nm[$j], 1, 2);
		}
	}
	}
	echo ",";
	//更新日時
	if($Kousin[$i] != ""){
		//日付
		$strWk=chr(34) .substr($Kousin[$i],0,4) . "/". substr($Kousin[$i],4,2) . "/" . substr($Kousin[$i],6,2) . chr(34);
		echo  JcodeConvert($strWk, 1, 2);
		echo ",";
		//時間
		$strWk=chr(34) .substr($Kousin[$i],8,2) .":" . substr($Kousin[$i],10,2) .chr(34);
		echo  JcodeConvert($strWk, 1, 2);
	}else{
		echo ",";
	}
	echo ",";
	//コメント
	if($Comment[$i] != ""){
		$strWk = str_replace(chr(34),"”",$Comment[$i]);
		$strWk = chr(34) .$strWk. chr(34) ;
		$strWk = str_replace(",","，",$strWk);
		echo  JcodeConvert($strWk, 1, 2);
	}
	echo ",";
	//送信先
	if($send_type==0){
		$strWk="携帯／ＰＣ";
		echo  JcodeConvert($strWk, 1, 2);
	}elseif($send_type==1){
		$strWk="携帯";
		echo  JcodeConvert($strWk, 1, 2);
	}elseif($send_type==2){
		$strWk="ＰＣ";
		echo  JcodeConvert($strWk, 1, 2);
	}
	echo "\n";
}
?>