<?php 
//*****************************************************************************
//* 
//* プログラム名：営業日報ＣＳＶファイル吐き出し
//* プログラムID：RPT093.php
//* 機能        ：ＣＳＶファイルにデータを吐き出す
//* 作成者      ：
//* 
//* Copyright (C) 2004 Media Tech Inc. All rights reserved.
//*****************************************************************************

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

//自分の所属を取得
$strSQL_s = "SELECT syozoku_cd FROM syozoku_tbl WHERE syain_cd ='$PHP_SYAIN_CD' ";
$objRec_s = pg_exec($strSQL_s);
if($objRec_s==false){
	echo("SQL実行に失敗しました(SELECT)");
	exit;
}

if($disp_mode == 1){
	for ($intSyozoku=0; $intSyozoku < pg_numrows($objRec_s); $intSyozoku++){
		$objSyozoku = pg_fetch_object($objRec_s, $intSyozoku);
		if ($intSyozoku==0){
			$strJoken= " Where (bumon_cd =0 or bumon_cd = " .$objSyozoku->syozoku_cd ;
		}else{
			$strJoken=$strJoken. " or bumon_cd=".$objSyozoku->syozoku_cd ;
		}
	}
	if (pg_numrows($objRec_s) > 0){
		$strJoken=$strJoken. ") ";
	}else{
		$strJoken= " Where (bumon_cd =0)";
	}
}else if($disp_mode == 2){
	$strJoken= "  Where eigyo_memo_tbl.syain_cd='$PHP_SYAIN_CD'";
}else if($disp_mode == 3){
	if ($start_y !="00" && $start_m != "00" && $start_d !="00" && $end_y != "00" && $end_m != "00" && $end_d != "00" ){
		$strDateJoken=" and (date between '" . $start_y ."/" . $start_m ."/". $start_d ."' and '" . $end_y ."/" . $end_m ."/". $end_d . "')";
	}
	//グループ
	if($chk_tori==0 && $Customer_group !=""){
		//取引先情報取得
		$strSQLtori="SELECT tori_name,tori_group FROM torihiki_tbl  Where tori_group=".$Customer_group;
		$objRectori = pg_exec($strSQLtori);
		if($objRectori==false){
			echo("SQL実行に失敗しました(SELECT)");
			exit;
		}
		if (0 < pg_numrows($objRectori)){
			for ($inttori=0; $inttori < pg_numrows($objRectori); $inttori++){
				$objtori = pg_fetch_object($objRectori, $inttori);
				if($inttori==0){
					$strGroupJoken=" and (tori_name='".$objtori->tori_name."'";
				}else{
					$strGroupJoken=$strGroupJoken." or tori_name='".$objtori->tori_name."'";
				}
			}
			$strGroupJoken=$strGroupJoken.")";
		}else{
			$strGroupJoken=" and (tori_name='".$objtori->tori_name."')";
		}
	}
	//取引先
	if($chk_tori==1 && $Customer !=""){
		$strtoriJoken=" and (tori_name like '%".$Customer."%')";
	}
	//取引入力
	if($chk_tori==2 && $Visit_input !=""){
		$strInputJoken=" and (tori_name like '%".$Visit_input."%')";
	}
	//訪問者選択
	if($Caller != ""){
		$strCallerJoken=" and (syain_list like '%/".$Caller."/%')";
	}
	//日付条件
	if($strDateJoken !=""){
		$strJoken=$strDateJoken;
	}
	//取引先選択条件
	if($strJoken != "" && $strtoriJoken != ""){
		$strJoken= $strJoken." ".$strtoriJoken;
	}elseif($strJoken=="" && $strtoriJoken != ""){
		$strJoken=$strtoriJoken;
	}
	//取引先入力
	if($strJoken != "" && $strInputJoken != ""){
		$strJoken= $strJoken." ".$strInputJoken;
	}elseif($strJoken=="" && $strInputJoken != ""){
		$strJoken=$strInputJoken;
	}
	//取引先グループ条件
	if($strJoken != "" && $strGroupJoken != ""){
		$strJoken= $strJoken."  ".$strGroupJoken;
	}elseif($strJoken=="" && $strGroupJoken != ""){
		$strJoken=$strGroupJoken;
	}
	//訪問者条件
	if($strJoken != "" && $strCallerJoken != ""){
		$strJoken= $strJoken." ".$strCallerJoken;
	}elseif($strJoken=="" && $strCallerJoken != ""){
		$strJoken=$strCallerJoken;
	}
	$strJoken ="Where (peigyou_tbl.syain_cd='$PHP_SYAIN_CD' and eigyo_memo_tbl.seq = peigyou_tbl.seq ) ".$strJoken;
}
//201310対応
//$strSQL="SELECT read_flg,eigyo_memo_tbl.seq,date,time,time_e,tori_name,charge,eigyo_memo_tbl.syain_cd,syain_list,visit_purpose,memo,subject,bikou FROM eigyo_memo_tbl,peigyou_tbl ".$strJoken.
$strSQL="SELECT read_flg,eigyo_memo_tbl.seq,date,time,time_e,tori_name,charge,eigyo_memo_tbl.syain_cd,syain_list,visit_purpose,memo,subject,bikou,update FROM eigyo_memo_tbl,peigyou_tbl ".$strJoken.
				" and ( eigyo_memo_tbl.seq = peigyou_tbl.seq and peigyou_tbl.syain_cd ='$PHP_SYAIN_CD' ) ".
				//" Order by date DESC,time DESC,eigyo_memo_tbl.seq DESC ";
				" Order by read_flg,(eigyo_memo_tbl.update is null), eigyo_memo_tbl.update DESC ,date DESC,time DESC,eigyo_memo_tbl.seq DESC ";
$objRec = pg_exec($pg_con, $strSQL);
if ($objRec == false){
	echo "SQLの実行に失敗しました。";
	exit;
}
$intCount = pg_numrows($objRec);

$filename = "営業日報".date("Y").date("m").date("d").".csv";
//20091015 ファイル名ブラウザ判定
//$filename = JcodeConvert($filename, 1, 2);
$Agent = getenv( "HTTP_USER_AGENT" );
/*201310対応
if( ereg( "MSIE", $Agent ) ){ 
	$filename = JcodeConvert($filename, 1, 2);
} elseif( ereg( "Firefox", $Agent ) ) {
} elseif( ereg( "Safari", $Agent ) ) {
} else {
	$filename = JcodeConvert($filename, 1, 2);
}
*/
if( preg_match( "/MSIE/", $Agent ) ){ 
		$filename = JcodeConvert($filename, 1, 2);
} elseif( preg_match( "/Chrome/", $Agent ) ) {
		$filename = JcodeConvert($filename, 1, 2);
} elseif( preg_match( "/Firefox/", $Agent ) ) {
} elseif( preg_match( "/Safari/", $Agent ) ) {
} else {
	$filename = JcodeConvert($filename, 1, 2);
}

Header("Content-Disposition: attachment; filename=\"$filename\"");
Header("Content-Type: text/csv");

//ヘッダー行出力
//201310対応
//$strWk="訪問先,担当者,訪問日,訪問開始時間,訪問終了時間,報告者,訪問者,訪問目的,メモ,課題,備考,コメント\n";
$strWk="訪問先,担当者,訪問日,訪問開始時間,訪問終了時間,報告者,訪問者,訪問目的,メモ,課題,備考,添付ファイル,コメント(発言者・発言日時・コメント内容・添付ファイル)\n";
echo  JcodeConvert($strWk, 1, 2);

//明細データ出力
for ($i = 0; $i < $intCount; $i++){
	$objData = pg_fetch_object($objRec, $intCnt);

	if (($objData->time =="") && ($objData->time_e =="")){
		$strTime[$intCnt2]="";
	}elseif (($objData->time !="") && ($objData->time_e !="")){
		$strTime[$intCnt2]=$objData->time ."〜" .$objData->time_e;
	}elseif (($objData->time !="") && ($objData->time_e =="")){
		$strTime[$intCnt2]=$objData->time ."〜";
	}

	echo  JcodeConvert($objData->tori_name, 1, 2);
	echo  ",".JcodeConvert($objData->charge, 1, 2);
	echo  ",".JcodeConvert($objData->date, 1, 2);
	echo  ",".JcodeConvert($objData->time, 1, 2);
	echo  ",".JcodeConvert($objData->time_e, 1, 2);

	//報告者名取得
	$strSyain="SELECT name_kj_sei,name_kj_mei FROM syain_tbl Where syain_cd=" . "'" .$objData->syain_cd ."'";
	$objRecSyain = pg_exec($strSyain);
	if($objRecSyain==false){
		echo("SQL実行に失敗しました(SELECT)");
		exit;
	}
	$objSyain = pg_fetch_object($objRecSyain, 0);
	$strData = $objSyain->name_kj_sei.$objSyain->name_kj_mei;
	echo  ",".JcodeConvert($strData, 1, 2);

	//社員名取得
	$visitor = "" ;
	$strJyoken=str_replace("/","' or syain_cd='",$objData->syain_list);
	$strSyain="SELECT name_kj_sei,name_kj_mei FROM syain_tbl Where syain_cd=" . "'" .$strJyoken ."'" . " Order by disp_rank";
	$objRecSyain = pg_exec($strSyain);
	if($objRecSyain==false){
		echo("SQL実行に失敗しました(SELECT)");
		exit;
	}
	for ($intSyainCnt = 0; $intSyainCnt < pg_numrows($objRecSyain); $intSyainCnt++) {
		$objSyain = pg_fetch_object($objRecSyain, $intSyainCnt);
			if ( $visitor == "" ){
				$visitor = $objSyain->name_kj_sei.$objSyain->name_kj_mei;
			} else {
				$visitor .= ",".$objSyain->name_kj_sei.$objSyain->name_kj_mei;
			}
	}
	if (pg_numrows($objRecSyain) >= 2){
		$visitor = "\"".$visitor."\"";
	}
	echo ",".JcodeConvert($visitor, 1, 2);

	//訪問目的
	$strWk = str_replace("\r\n"," ",$objData->visit_purpose);
	$strWk = str_replace("\r"," ",$strWk);
	$strWk = str_replace("\n"," ",$strWk);
	$strWk = str_replace(",","，",$strWk);
	echo ",".JcodeConvert($strWk, 1, 2);

	//メモ
	$strWk = str_replace("\r\n"," ",$objData->memo);
	$strWk = str_replace("\r"," ",$strWk);
	$strWk = str_replace("\n"," ",$strWk);
	$strWk = str_replace(",","，",$strWk);
	echo ",".JcodeConvert($strWk, 1, 2);

	//課題
	$strWk = str_replace("\r\n"," ",$objData->subject);
	$strWk = str_replace("\r"," ",$strWk);
	$strWk = str_replace("\n"," ",$strWk);
	$strWk = str_replace(",","，",$strWk);
	echo ",".JcodeConvert($strWk, 1, 2);

	//備考
	$strWk = str_replace("\r\n"," ",$objData->bikou);
	$strWk = str_replace("\r"," ",$strWk);
	$strWk = str_replace("\n"," ",$strWk);
	$strWk = str_replace(",","，",$strWk);
	echo ",".JcodeConvert($strWk, 1, 2);

	//201310対応
	//添付ファイル
	$work_temp = "";
	if ($objData->seq!=""){
		$strSQL = "select file_name_org from common_file_tbl where seq1 = ".$objData->seq."AND seq2 = 0";
		$objfile = pg_exec($strSQL);
		if($objfile==false){
			echo("SQL実行に失敗しました(SELECT)");
			exit;
		}
		if(pg_numrows($objfile) > 0){
			for($k=0;$k<pg_numrows($objfile);$k++){
				$fileData = pg_fetch_object($objfile, $k);

				$tempFile = ereg_replace("\r\n"," ",$fileData->file_name_org);
				$tempFile = ereg_replace("\r"," ",$tempFile);
				$tempFile = ereg_replace("\n"," ",$tempFile);
				$tempFile = ereg_replace(",","，",$tempFile);
				if ( $work_temp == "" ){
					$work_temp = $tempFile;
				} else {
					$work_temp .= ",".$tempFile;
				}
				
			}
			if (pg_numrows($objfile) >= 2){
					$work_temp = "\"".$work_temp."\"";
			}
		}
	}
	echo ",".JcodeConvert($work_temp, 1, 2);

	//コメント
	if ($objData->seq!=""){
		//201310　対応
		//$strSQL = "select common_file_tbl.file_name_org,comment,comm_date,syain_tbl.name_kj_sei,syain_tbl.name_kj_mei from common_file_tbl,eigyo_comm_tbl ";
		//$strSQL .= "join syain_tbl on syain_tbl.syain_cd = eigyo_comm_tbl.syain_cd ";
		//$strSQL .= "where eigyo_comm_tbl.comm_seq = common_file_tbl.seq2 ";
		//$strSQL .= "and common_file_tbl.seq1 = ".$objData->seq." order by comm_date desc";
		$strSQL = "SELECT syain_tbl.name_kj_sei,name_kj_mei,eigyo_comm_tbl.comm_date,comment, comm_seq ";
		$strSQL .= "FROM eigyo_comm_tbl ";
		$strSQL .= "JOIN syain_tbl ON syain_tbl.syain_cd = eigyo_comm_tbl.syain_cd ";
		$strSQL .= "WHERE eigyo_comm_tbl.seq = ".$objData->seq." ";
		$strSQL .= "ORDER BY eigyo_comm_tbl.comm_date DESC";
		$objComm = pg_exec($strSQL);
		if($objComm==false){
			echo("SQL実行に失敗しました(SELECT)");
			exit;
		}
		if(pg_numrows($objComm) > 0){
			for($j=0;$j<pg_numrows($objComm);$j++){
				$commData = pg_fetch_object($objComm, $j);
				$strWk = $commData->name_kj_sei.$commData->name_kj_mei;
				//echo ",".JcodeConvert($strWk, 1, 2);

				$strDate = (int) substr(($commData->comm_date),0,4);
				$strDate .= "年";
				$strDate .= (int) substr(($commData->comm_date),4,2);
				$strDate .= "月";
				$strDate .= (int) substr(($commData->comm_date),6,2);
				$strDate .= "日";
				$strDate .= (int) substr(($commData->comm_date),8,2);
				$strDate .= "時";
				$strDate .= (int) substr(($commData->comm_date),10,2);
				$strDate .= "分";
				//echo ",".JcodeConvert($strDate, 1, 2);
				$tempComm = str_replace("\r\n"," ",$commData->comment);
				$tempComm = str_replace("\r"," ",$tempComm);
				$tempComm = str_replace("\n"," ",$tempComm);
				$tempComm = str_replace(",","，",$tempComm);
				
				//201310　対応　コメントの添付ファイルを取ってくる
				$strSQL_temp = "SELECT file_name_org FROM common_file_tbl ";
				$strSQL_temp .= "WHERE seq1 = ".$objData->seq." ";
				$strSQL_temp .= "AND seq2 = ".$commData->comm_seq;
				$objComm_temp = pg_exec($strSQL_temp);
				if($objComm_temp==false){
					echo("SQL実行に失敗しました(SELECT)");
					exit;
				}
				if(pg_numrows($objComm_temp) > 0){
					$commData_temp = pg_fetch_object($objComm_temp, 0);
					$commtempFile = str_replace("\r\n"," ",$commData_temp->file_name_org);
					$commtempFile = str_replace("\r"," ",$commtempFile);
					$commtempFile = str_replace("\n"," ",$commtempFile);
					$commtempFile = str_replace(",","，",$commtempFile);
				}else{
					$commtempFile = "";
				}
				echo ",\"".JcodeConvert($strWk, 1, 2).",".JcodeConvert($strDate, 1, 2).",".JcodeConvert($tempComm, 1, 2).",".JcodeConvert($commtempFile, 1, 2)."\"";
			}
		}
	}
	//201310対応
	//コメント添付ファイル
	/*
		if ($objData->seq!=""){
			$strSQL = "select comment,comm_date,syain_tbl.name_kj_sei,syain_tbl.name_kj_mei from eigyo_comm_tbl ";
			$strSQL .= "join syain_tbl on syain_tbl.syain_cd = eigyo_comm_tbl.syain_cd  ";
			$strSQL .= "where seq = ".$objData->seq." order by comm_date desc";		
			$objComm = pg_exec($strSQL);
			if($strSQL==false){
				echo("SQL実行に失敗しました(SELECT)");
				exit;
			}
			if(pg_numrows($objComm) > 0){
				for($j=0;$j<pg_numrows($objComm);$j++){
					$commData = pg_fetch_object($objComm, $j);
					$strWk = $commData->name_kj_sei.$commData->name_kj_mei;
					echo ",".JcodeConvert($strWk, 1, 2);

					$strDate = (int) substr(($commData->comm_date),0,4);
					$strDate .= "年";
					$strDate .= (int) substr(($commData->comm_date),4,2);
					$strDate .= "月";
					$strDate .= (int) substr(($commData->comm_date),6,2);
					$strDate .= "日";
					$strDate .= (int) substr(($commData->comm_date),8,2);
					$strDate .= "時";
					$strDate .= (int) substr(($commData->comm_date),10,2);
					$strDate .= "分";
					echo ",".JcodeConvert($strDate, 1, 2);
					$tempComm = str_replace("\r\n"," ",$commData->comment);
					$tempComm = str_replace("\r"," ",$tempComm);
					$tempComm = str_replace("\n"," ",$tempComm);
					$tempComm = str_replace(",","，",$tempComm);
					echo ",".JcodeConvert($tempComm, 1, 2);
				}
			}
		}
	*/

	echo "\n";
}
?>