<?PHP 
//*****************************************************************************
//* プログラム名：資金損益＆財務運用表 年単位（EXCEL出力)
//* プログラムID：ZIS0600.php
//* 機能        ：
//*****************************************************************************
header("Content-type: text/html; charset=EUC-JP");
include("./inc/session_start.inc");
include("./inc/SessionChk.inc");
include("./inc/dbcon.inc");
include('./inc/smarty.conf');
include("./inc/const.inc");
include("./inc/jcode.php");

$filename = "資金収支実績表".date("Y").date("m").date("d").".xls";
$filename = JcodeConvert($filename, 1, 2);

$ExcelData = "";

Header("Content-Disposition: attachment; filename=\"$filename\"");
Header("Content-Type: pplication/vnd.ms-excel");

if(trim($start_y) == ""){

//	$disp_y = date("Y");
//	$disp_m = date("n");
	if($_SESSION["JISSEKI_Y"]){
		$disp_y = $_SESSION["JISSEKI_Y"];
	}else{
		$disp_y = date("Y");
	}
	if($_SESSION["JISSEKI_M"]){
		$disp_m = $_SESSION["JISSEKI_M"];
	}else{
		$disp_m = date("n");
	}

	//基本データ
	$strSQL = "SELECT * FROM z_mst_base";
	$objRec = pg_exec($strSQL);
	if($objRec==false){
		echo("SQL実行に失敗しました(SELECT)");
		exit;
	}
	$objData = pg_fetch_object($objRec, 0);
	//金額単位
	$zis_unit = $objData->unit_flg;
	$strUnit = get_unit($zis_unit);
	$numUnit = get_unitNum($zis_unit);
	//決算翌月
	if($objData->kessan_month == 12){
		$start_m = 1;
	}else{
		$start_m = $objData->kessan_month + 1;
	}
	//決算翌月の年
	if($start_m > $disp_m){
		$start_y = $disp_y - 1;
	}else{
		$start_y = $disp_y;
	}
	//決算月
	$end_m = $objData->kessan_month;
	//決算年
	if($start_m == 1){
		$end_y = $start_y;
	}else{
		$end_y = $start_y + 1;
	}
	
	
}else{
	//基本データ
	$strSQL = "SELECT * FROM z_mst_base";
	$objRec = pg_exec($strSQL);
	if($objRec==false){
		echo("SQL実行に失敗しました(SELECT)");
		exit;
	}
	$objData = pg_fetch_object($objRec, 0);
	//金額単位
	$zis_unit = $objData->unit_flg;
	$strUnit = get_unit($zis_unit);
	$numUnit = get_unitNum($zis_unit);
}

if(trim($end_y) != ""){
	//表示開始年
	$first_dsp_y = $end_y - 5;
	
	//表示終了年
	$last_dsp_y = $first_dsp_y + 4;
	
}else{
	//表示開始年
	$first_dsp_y = date("Y") - 5;
	
	//表示終了年
	$last_dsp_y = date("Y");
	
}

//echo $start_y."/".$start_m."　〜　".$end_y."/".$end_m;


//集計タイトル取得
$strSQL = "SELECT * FROM z_mst_title ORDER BY title_seq";
$objRec = pg_exec($strSQL);
if($objRec==false){
	echo("SQL実行に失敗しました(SELECT)");
	exit;
}
$strTITLE = "";
for($i=0;$i<pg_numrows($objRec);$i++){
	$objData = pg_fetch_object($objRec, $i);
	$strTITLE[$i][0] = $objData->title_seq;
	$strTITLE[$i][1] = $objData->title_name;
}

/*

新々配列！
 0:[ 0] 資金回収売上高
 1:[ 1] 原価　人　件　費
 2:[16] 原価 外注・仕入費
 3:[17] そのた　支出原価
 4:[--] 資金　原価　計		=2+3+4		判別No.101

 5:[ 2] 販管　人　件　費
 6:[18] 販管　外　注　費
 7:[19] そのた 販　管　費
 8:[--] 資金販管費　計		=5+6+7		判別No.102

 9:[ 3] 4資金回収営外益
10:[14] 5資金支出営外費
11:[ 4] 6資金回収特別益
12:[15] 7資金支出特別損
13:[ 5] 8支出 法人税等
14:[--] 9資金収支純損益		=0+4+8+9+10+11+12+13	判別No.103

15:[ 6]10短期資産運用金
16:[ 7]11そのた短期資金
17:[ 8]12設備等の投資金
18:[--]13資金の運用　計		=15+16+17	判別No.104

19:[ 9]14短期 保有資金
//14:[10]15消費税管理
20:[11]16外部資金の調達
21:[12]17資本・利益資金
22:[--]18資金の調達　計		=19+20+21	判別No.105

23:[13]19資金収支差引額
24:[--]20現金・預金残高		=22-23		判別No.106

*/
//順番
//$arrSrt = array(0,1,2,101,3,102,4,5,103,6,7,8,104,9,10,11,12,105,13,106);
//20100601 sato
$arrSrt = array(0,1,16,17,101,2,18,19,102,3,14,4,15,5,103,6,7,8,104,9,11,12,105,107,13,106); 
$strData = "";	//[0]:初期金額　[1]：当月残高　[2]：当月(期)増減 [3]：名称

//var_dump($strTITLE);

//期首繰越金と項目名称
for($i=0;$i<count($arrSrt);$i++){
//echo $arrSrt[$i]."<br>"	   ;

	switch ($arrSrt[$i]) {
	case 101:	//資金　原価　計
		$strData[$i][0] = $strData[1][0] + $strData[2][0] + $strData[3][0];
		$strData[$i][3] = "資 金　原 価　計";
		break;
    case 102:	//資金販管費　計
		$strData[$i][0] = $strData[5][0] + $strData[6][0] + $strData[7][0];
		$strData[$i][3] = "資金販管費　 計";
        break;
    case 103:	//資金純損益
		$strData[$i][0] = $strData[0][0] + $strData[4][0] + $strData[8][0] + $strData[9][0] + $strData[10][0] + $strData[11][0] + $strData[12][0] + $strData[13][0];
		$strData[$i][3] = "資金収支純損益";
        break;
    case 104:	//資金の運用　 計
		$strData[$i][0] = $strData[15][0] + $strData[16][0] + $strData[17][0];
		$strData[$i][3] = "資金の運用　 計";
        break;
    case 105:	//資金の調達　 計
		$strData[$i][0] = $strData[19][0] + $strData[20][0] + $strData[21][0];
		$strData[$i][3] = "資金の調達　 計";
        break;
    case 107:	//資金収支差引計
		$strData[$i][0] = $strData[22][0] + $strData[14][0] + $strData[18][0];
		$strData[$i][3] = "資金収支差引計";
        break;
    case 106:	//現金・預金残高
		$strData[$i][0] = $strData[24][0] - $strData[23][0];
		$strData[$i][3] = "差　 　 　 額";
        break;

    default:
		$strSQL  = "SELECT ";
		$strSQL .= " z_mst_kanjo.kseq1, ";
		$strSQL .= " z_mst_kanjo.kseq2, ";
		$strSQL .= " z_mst_kanjo.kseq3, ";
		$strSQL .= " z_mst_kanjo.kseq4, ";
		$strSQL .= " z_mst_kanjo.kseq5, ";
		$strSQL .= " z_mst_kanjo.kanjo_name, ";
		$strSQL .= " z_mst_kanjo.ptn_flg, ";
		$strSQL .= " T2.base_money ";
		$strSQL .= "FROM ";
		$strSQL .= " z_mst_kanjo ";
		$strSQL .= "LEFT JOIN ";
		$strSQL .= " z_monthly_data AS T2 ";
		$strSQL .= "ON ";
		$strSQL .= " z_mst_kanjo.kseq1 = T2.cseq1 AND ";
		$strSQL .= " z_mst_kanjo.kseq2 = T2.cseq2 AND ";
		$strSQL .= " z_mst_kanjo.kseq3 = T2.cseq3 AND ";
		$strSQL .= " z_mst_kanjo.kseq4 = T2.cseq4 AND ";
		$strSQL .= " z_mst_kanjo.kseq5 = T2.cseq5 AND ";
		$strSQL .= " T2.data_year = ".$start_y." AND ";
		$strSQL .= " T2.data_month = ".$start_m." ";

		$strSQL .= "WHERE ";
		$strSQL .= " z_mst_kanjo.disp_flg = 1 AND ";
		$strSQL .= " z_mst_kanjo.use_flg = 1 AND ";
		$strSQL .= " z_mst_kanjo.title_seq = ".$strTITLE[$arrSrt[$i]][0]." ";
//echo $strSQL."<br>".$strTITLE[$arrSrt[$i]][1]."<hr>";
		$objRec = pg_exec($strSQL);
		if($objRec==false){
			echo("SQL実行に失敗しました(SELECT)");
			exit;
		}
		$strData[$i][0] = 0;
		$strData[$i][3] = $strTITLE[$arrSrt[$i]][1];
		if(pg_numrows($objRec) > 0){
			for($j=0;$j<pg_numrows($objRec);$j++){
				$objData = pg_fetch_object($objRec, $j);
//echo NtoZ($objData->base_money)."<br>";
				$strData[$i][0] = $strData[$i][0] + NtoZ($objData->base_money);
			}
		}
	}
}

//年データ
$strDataY = "";

//$arrSrt = array(0,1,16,17,101,2,18,19,102,3,14,4,15,5,103,6,7,8,104,9,11,12,105,107); 
$selectY = $first_dsp_y;

for($i=0;$i<5;$i++){
	$strYTitle[$i] = $selectY."年";
	for($j=0;$j<count($arrSrt);$j++){
		switch ($arrSrt[$j]) {

	    case 101:	//資金　原価　計
			$strDataY[$i][$j] = $strDataY[$i][1] + $strDataY[$i][2] + $strDataY[$i][3];
	        break;
	    case 102:	//資金販管費　計
			$strDataY[$i][$j] = $strDataY[$i][5] + $strDataY[$i][6] + $strDataY[$i][7];
	        break;
	    case 103:	//資金収支純損益（資金純損益）
			$strDataY[$i][$j] = floatval($strDataY[$i][$j]) + floatval($strDataY[$i][0]) + floatval($strDataY[$i][4]) + floatval($strDataY[$i][8]);
			$strDataY[$i][$j] = floatval($strDataY[$i][$j]) + floatval($strDataY[$i][9]) + floatval($strDataY[$i][10]) + floatval($strDataY[$i][11]);
			$strDataY[$i][$j] = floatval($strDataY[$i][$j]) + floatval($strDataY[$i][12]) + floatval($strDataY[$i][13]);
	        break;

////echo $strDataY[$i][5]."<br>";
////echo $strDataY[$i][6]."<br>";
////echo $strDataY[$i][7]."<hr>";
	    case 104:	//資金の運用計（資金支出計）
			$strDataY[$i][$j] = $strDataY[$i][15] + $strDataY[$i][16] + $strDataY[$i][17];
	        break;
	    case 105:	//資金の調達　計（資金調達計）
			$strDataY[$i][$j] = $strDataY[$i][19] + $strDataY[$i][20] + $strDataY[$i][21];
	        break;
	    case 107:	//資金収支差引計
			$strDataY[$i][$j] = $strDataY[$i][22] + $strDataY[$i][14] + $strDataY[$i][18];
	        break;
	    case 106:	//現金・預金残高
			$strDataY[$i][$j] = $strDataY[$i][24] + $strDataY[$i][23];
	        break;
	    default:
			$strSQL  = "SELECT ";
			$strSQL .= " z_mst_kanjo.kseq1, ";
			$strSQL .= " z_mst_kanjo.kseq2, ";
			$strSQL .= " z_mst_kanjo.kseq3, ";
			$strSQL .= " z_mst_kanjo.kseq4, ";
			$strSQL .= " z_mst_kanjo.kseq5, ";
			$strSQL .= " z_mst_kanjo.kanjo_name, ";
			$strSQL .= " z_mst_kanjo.ptn_flg, ";
			$strSQL .= " T2.data_seq, ";
			$strSQL .= " T2.data_money ";
			$strSQL .= "FROM ";
			$strSQL .= " z_mst_kanjo ";
			$strSQL .= "LEFT JOIN ";
			$strSQL .= " z_monthly_data AS T2 ";
			$strSQL .= "ON ";
			$strSQL .= " z_mst_kanjo.kseq1 = T2.cseq1 AND ";
			$strSQL .= " z_mst_kanjo.kseq2 = T2.cseq2 AND ";
			$strSQL .= " z_mst_kanjo.kseq3 = T2.cseq3 AND ";
			$strSQL .= " z_mst_kanjo.kseq4 = T2.cseq4 AND ";
			$strSQL .= " z_mst_kanjo.kseq5 = T2.cseq5 AND ";
			$strSQL .= " T2.data_year = ".$selectY." AND ";
			$strSQL .= " T2.data_month = ".$end_m." ";
			$strSQL .= "WHERE ";
			$strSQL .= " z_mst_kanjo.disp_flg = 1 AND ";
			$strSQL .= " z_mst_kanjo.use_flg = 1 AND ";
			$strSQL .= " z_mst_kanjo.title_seq = ".$strTITLE[$arrSrt[$j]][0]." ";
			$objRec = pg_exec($strSQL);
			if($objRec==false){
				echo("SQL実行に失敗しました(SELECT)");
				exit;
			}
			$strDataY[$i][$j] = 0;
			if(pg_numrows($objRec) > 0){
				for($k=0;$k<pg_numrows($objRec);$k++){
					$objData = pg_fetch_object($objRec, $k);
					if($objData->kseq1 == 8 && $objData->kseq2 == 1 && $objData->kseq3 == 4 && $objData->kseq4 == 1){
						if( NtoZ($objData->data_money) > 0 ){
							$strDataY[$i][$j] = $strDataY[$i][$j] - get_PM($objData->ptn_flg) * NtoZ($objData->data_money);
						}else{
							$strDataY[$i][$j] = $strDataY[$i][$j] + get_PM($objData->ptn_flg) * NtoZ($objData->data_money);
						}
					}else{
						$strDataY[$i][$j] = $strDataY[$i][$j] + get_PM($objData->ptn_flg) * NtoZ($objData->data_money);
					}
				}
			}
		}
	
		//前年比を計算
		if($i > 0){
			if($strDataY[$i][$j] != 0 && $strDataY[$i-1][$j] != 0){
				$compareY[$i][$j] = $strDataY[$i][$j] - $strDataY[$i-1][$j];
			}else{
				$compareY[$i][$j] = 0;
			}
		}
	}
	
	$selectY = $selectY + 1;

}

//フォーマット＆要素２個削除
// 2:[ 2] 3販管費
// 3:[--] 4営業損益(計算値)		=1-(2+3)		判別No.101

$arrMax = count($strData);
$newCnt = 0;

echo("cntstr:".count($strData)." unit:".$numUnit."<br>");

for($i=0;$i<$arrMax;$i++){
		$strData2[$newCnt] = $strData[$i];											//一旦コピー
		$strData2[$newCnt][0] = number_format(floor($strData[$i][0] / $numUnit));	//金額フォーマット
		for($j=0;$j<5;$j++){
			$strDataY2[$j][$newCnt] =  number_format(floor($strDataY[$j][$i] / $numUnit));
			$strCompY[$j][$newCnt] = number_format(floor($compareY[$j][$i] / $numUnit));
		}
		$newCnt++;
}

//*** 単位取得 ***//
function get_unit($ptn_flg) { 
		$strReturn = "";
		switch ($ptn_flg) {
		case "1": $strReturn = "千"; break;
		case "2": $strReturn = "万"; break;
		default : $strReturn = "円"; break;
		}
		return $strReturn;
}
//*** 単位計算値 ***//
function get_unitNum($ptn_flg) { 
		$strReturn = "";
		switch ($ptn_flg) {
		case "1": $strReturn = 1000; break;
		case "2": $strReturn = 10000; break;
		default : $strReturn = 1; break;
		}
		return $strReturn;
}

function NtoZ($prm){
//Nullと空文字をゼロに置き換えます
	if(is_null($prm) || trim($prm) == "" ){
		return 0;
	}else{
		return $prm;
	}
}
function get_PM($flg){
//加算の正負を取得する関数です
	if($flg == 1){
		return -1;
	}else if($flg == 2){
		return 1;
	}else{
		return 0;	
	}
}

//HTMLヘッダの書き出し

$ExcelData = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">';
$ExcelData .= '<html>';
$ExcelData .= '<head>';
$ExcelData .= '	<meta http-equiv="content-type" content="text/html; charset=euc-jp">';
$ExcelData .= '	<meta http-equiv="content-style-type" content="text/css">';
$ExcelData .= '<style TYPE="text/css">';
$ExcelData .= 'td { border: 1px #000000 solid; text-align:right;} ';
$ExcelData .= 'th { background-color:#E0E0E0; font-weight:700; border: 1px #000000 solid; } ';
$ExcelData .= '</style>';
$ExcelData .= '	<title>'.$PHP_SYSNAME.'</title>';
$ExcelData .= '</head>';
$ExcelData .= '<body>';
$ExcelData .= '    <div style="font-size:18pt;">資金収支実績表</div>';
//------------------------------------------
$ExcelData .= '	<br />';
$ExcelData .= $first_dsp_y.'年〜'.$last_dsp_y.'年';
$ExcelData .= '&nbsp;&nbsp;&nbsp;&nbsp;';

if ($strUnit == "円" ){
	$ExcelData .= '金額単位('.$strUnit.')';
}else{
	$ExcelData .= '金額単位('.$strUnit.'円)';
}
$ExcelData .= '	<table border="0" cellpadding="0" cellspacing="0" collapse>';
$ExcelData .= '	<TR>';
$ExcelData .= '	<TD align="left">';
$ExcelData .= '		<table class="ZIS_Table" cellpadding="0" cellspacing="0" collapse> ';
$ExcelData .= '		<tr>';
$ExcelData .= '		<th width="10%" align="center" nowrap>';
$ExcelData .= '		</th>';
for($i = 0; $i<count($strYTitle);$i++){
	$ExcelData .= '	<th width="10%" align="center" nowrap>';
	$ExcelData .= $strYTitle[$i];
	$ExcelData .= '	</th>';
	if($i < count($strYTitle)-1 ){
		$ExcelData .= '	<th width="8%" align="center" nowrap>';
		$ExcelData .= '	増減';
		$ExcelData .= '	</th>';
	}
}
$ExcelData .= '	</tr>';
for($i = 0; $i<count($strData2);$i++){
	if($i == 0 || $i == 4 || $i == 8 || $i == 14 || $i == 18 || $i == 22 || $i == 23){
		$style = "background-color:#CFFFBF;";
	}elseif( $i == 25){
		$style = "background-color:#DFDFDF;";
	}
	$ExcelData .= '	<tr>';
	$ExcelData .= '		<td style="" align="center" nowrap>';
	$ExcelData .= $strData2[$i][3];
	$ExcelData .= '	</td>';
	for($j = 0; $j<count($strDataY2);$j++){
		if($i == 0 || $i == 4 || $i == 8 || $i == 14 || $i == 18 || $i == 22 || $i == 23){
			$style = "background-color:#CFFFBF;";
		}elseif( $i == 25){
			$style = "background-color:#DFDFDF;";
		}else{
			$style = "background-color:#FFF8BF;";
		}
		$ExcelData .= '	<td style="" align="right">';
		$ExcelData .= $strDataY2[$j][$i];
		$ExcelData .= '	</td>';
		if($j < count($strDataY2)-1 ){
			if($i == 0 || $i == 4 || $i == 8 || $i == 14 || $i == 18 || $i == 22 || $i == 23){
				$style = "background-color:#CFFFBF;";
			}elseif( $i == 25){
				$style = "background-color:#DFDFDF;";
			}
			$ExcelData .= '	<td style="" align="center" nowrap>';
				if($strCompY[$j+1][$i] != 0 ){
					$ExcelData .= '	<span style="color:#303080;">'.$strCompY[$j+1][$i].'</span>';
				}
		}
	}
	$ExcelData .= '	</tr>';
	if($i == 14 || $i == 18 || $i == 22){
		$ExcelData .= '	<tr><td>&nbsp;</td><td colspan="9" nowrap>&nbsp;</td></tr>';
	}
}
$ExcelData .= '		</table> ';
$ExcelData .= '		</TD>';
$ExcelData .= '	</TR>';
$ExcelData .= '	</table>';
$ExcelData .= '	<br>';

$ExcelData .= '</body>';
$ExcelData .= '</html>';
$ExcelData = JcodeConvert($ExcelData, 0, 1);

echo $ExcelData;

?>