<?php //過去在庫データ 0408.php
/*****************************************************************************
// 2010/00/00
// 関連データベース・テーブル
// 参照：
// 	コード	code
// 	商品マスター	shohinmaster （在庫型）
// 	場所マスター	company（センター）
// 	出荷			shukka
// 	納品マスター	shozokumaster
// 	在庫調整		stockarrangte
// 	入荷			comeinfix
// 更新：
// 変更履歴：
---	2015/08/12	ボタン配置訂正	Ohyama	v1.0.1
---	2017/01/31	Chrome,Edgeブラウザ対応	Yoshinaga
*****************************************************************************/
if ($_POST[dl] ==""){
	header('pragma: no-cache');
	header("Cache-Control: no-cache, must-revalidate");
}
include "func.cgi";
myopen();
//再チェック
SecurityCheck();
PostData();
FunctionUseCheck2();

//センターコード
//センター権限の時はそのセンターを使用する。
//センター権限以外の時は指定された使用センターを使用する。
$strsql = "honbuid = '".$userdata["honbuid"]."'";
if ($userdata["kengen"] == 30) {  //10Tradex,20本部、30センター、40サプライヤー、50納品先
	$strsqlcenter = " and centerid = '".$userdata["companyid"]."'";
	$center_code = $userdata["companyid"];
} elseif ($_POST[center]<>"") {
	$strsqlcenter = " and centerid = '$_POST[center]'";
	$center_code = $_POST[center];
} else {
	$center_code = $centerdata[0][1];
}


if ($userdata["kengen"] == 40) {  //10Tradex,20本部、30センター、40サプライヤー、50納品先
//	$strsql = "SELECT shohincd FROM `".$company."shohinmaster` WHERE makercode ='".$userdata["companyid"]."'";
//	$strsqlmaker = dbselectin($strsql,"and shohincd in ");
	//商品リスト
	$shohindata = dbselect("shohinmei,shohincd","`".$company."shohinmaster`","makercode='".$userdata["companyid"]."' and ".$strsqljoin,"shohincd",-1);
} else {
	$shohindata = dbselect("shohinmei,shohincd","`".$company."shohinmaster`",$strsqljoin,"shohincd",-1);
}
if(sizeof($shohindata)==0){
//	商品が無しの場合
	if ($userdata["kengen"] == 40) {  //10Tradex,20本部、30センター、40サプライヤー、50納品先
		//商品リスト
		$shohindata = dbselect("shohinmei,shohincd","`".$company."shohinmaster`","makercode='".$userdata["companyid"]."'","shohincd",-1);
	} else {
		$shohindata = dbselect("shohinmei,shohincd","`".$company."shohinmaster`",-1,"shohincd",-1);
	}
}

//商品コードのＷＨＥＲＥ句
$shohincd = "'".$_POST[shohincd]."'";
$targetdate = "'".$_POST[targetdate]."'";
$targetdate2 = " DATE_ADD('".$_POST[targetdate]."',INTERVAL 1 DAY) ";

//未処理データ
$norundata = dbselect("operatedate","`".$company."status`","status >= 6 and operatedate <= DATE_ADD(CURDATE(),INTERVAL 1 DAY) and centerid ='".$center_code."' ","operatedate desc","1");
Testecho("NORUN ".$userdata[gyotaiid].$center_code.$strsqldbselect);
//年月リスト
$monthlist = dbselect("distinct(date_format(date,'%Y年%m月')),date_format(date,'%Y-%m-01')","calendar","date between ".$startdate." AND '".$norundata[0][0]."'","1",-1);
if($_POST[monthrange]=="") {
	$monthrange = $monthlist[(sizeof($monthlist)-1)][1];
} else {
	$monthrange = $_POST[monthrange];
}
//サプライヤー限定
if($userdata["supplyerid2"] <> "") {
	$strsqladd = " WHERE ".dbselectin("SELECT shohincd FROM `".$company."shohinmaster` where  makercode in(".$userdata["supplyerid2"].") "," shohincd in ");
Testecho($strsqladd);
}
//リストを取り出す
/*
$strsql = "SELECT shohincd,lotnum, sum(qty) as qty ,shomi,comein
FROM
(
SELECT shohincd,lotnum, nonreserveqty as qty,shomi, stockdate as comein FROM `".$company."stockdata` WHERE  validflg >= 1 $strsqlcenter
UNION ALL 
SELECT shohincd,lotnum, - comeinfixqty as qty ,shomi, kakuteidate as comin FROM `".$company."comeinfix` WHERE  kakuteidate >= $targetdate $strsqlcenter
UNION ALL 
SELECT shohincd,lotnum, sum(shukkaqty) as qty,shomi, stockdate as comein FROM `".$company."shukka` WHERE  shukkaflg >= 0 AND operatedate >= $targetdate and operatedate <= '".$norundata[0][0]."' and shukkaqty >0 and DC_TC in(0,1) $strsqlcenter group by lotnum
UNION ALL 
SELECT shohincd,lotnum, - sum(arrangeqty) as qty,shomi, stockdate as comein FROM `".$company."stockarrange` WHERE  stockarrangeflg >= 0 and arrangedate >= $targetdate  $strsqlcenter group by lotnum
UNION ALL 
SELECT shohincd,lotnum, sum(transqty) as qty,shomi, stockdate as comein FROM `".$company."transdata` WHERE  transflg > 0 and transdate >= $targetdate $strsqlcenter group by lotnum
) as UN
GROUP BY shohincd,lotnum, shomi, comein ORDER BY shohincd";
*/
$strsql = "SELECT shohincd,lotnum, sum(qty) as qty ,shomi,comein
FROM
(
SELECT shohincd,lotnum, nonreserveqty as qty,shomi, stockdate as comein FROM `".$company."stockdata` WHERE  validflg >= 0 $strsqlcenter
UNION ALL 
SELECT shohincd,lotnum, - comeinfixqty as qty ,shomi, kakuteidate as comin FROM `".$company."comeinfix` WHERE  kakuteidate >= $targetdate $strsqlcenter
UNION ALL 
SELECT shohincd,lotnum, sum(shukkaqty) as qty,shomi, stockdate as comein FROM `".$company."shukka` WHERE  lotnum <> 0 and DC_TC in(0,1) and shukkaflg >= 0 AND operatedate >= $targetdate2 and operatedate <= CURDATE() and shukkaqty >0  $strsqlcenter group by lotnum
UNION ALL 
SELECT shohincd,lotnum, - sum(arrangeqty) as qty,shomi, stockdate as comein FROM `".$company."stockarrange` WHERE  stockarrangeflg >= 0 and arrangedate >= $targetdate  $strsqlcenter group by lotnum
UNION ALL 
SELECT shohincd,lotnum, sum(transqty) as qty,shomi, stockdate as comein FROM `".$company."transdata` WHERE  transflg > 0 and transdate >= $targetdate $strsqlcenter group by lotnum
) as UN
$strsqladd
GROUP BY lotnum ORDER BY shohincd";

$strsql = "SELECT shohincd,lotnum, sum(qty) as qty ,shomi,comein
FROM
(
SELECT shohincd,lotnum, nonreserveqty as qty,shomi, stockdate as comein FROM `".$company."stockdata` WHERE  validflg >= 0 $strsqlcenter
UNION ALL 
SELECT shohincd,lotnum, - comeinfixqty as qty ,shomi, kakuteidate as comin FROM `".$company."comeinfix` WHERE  kakuteidate >= $targetdate $strsqlcenter
UNION ALL 
SELECT shohincd,lotnum, sum(shukkaqty) as qty,shomi, stockdate as comein FROM `".$company."shukka` WHERE  lotnum <> 0 and DC_TC in(0,1) and shukkaflg >= 0 AND operatedate >= $targetdate and operatedate <= CURDATE() and shukkaqty >0  $strsqlcenter group by lotnum
UNION ALL 
SELECT shohincd,lotnum, - sum(arrangeqty) as qty,shomi, stockdate as comein FROM `".$company."stockarrange` WHERE  stockarrangeflg >= 0 and arrangedate >= $targetdate  $strsqlcenter group by lotnum
UNION ALL 
SELECT shohincd,lotnum, sum(transqty) as qty,shomi, stockdate as comein FROM `".$company."transdata` WHERE  transflg > 0 and transdate >= $targetdate $strsqlcenter group by lotnum
) as UN
$strsqladd
GROUP BY lotnum ORDER BY shohincd";

$result=@mysql_query($strsql,$my_con);
mysql_sql_log2($strsql,$_SERVER['SCRIPT_NAME'].":".__LINE__);

if ($_POST[dl] <>""){
	$strdate = Sjis("在庫閲覧データ基準日".$targetdate ."_出力日".date("Y-m-d").".csv");
	header("Content-type: application/octet-stream");
	header("Content-Disposition: attachment; filename=$strdate");
	header("Connection: close");
	ob_end_clean();
	#データ取得
	Sprint("在庫閲覧データ 基準日：".$targetdate."作成日：".date("Y/m/d G:i"));
	echo "\n";
	Sprint(GetList($centerdata,$center_code,0));
	echo "\n";
	Sprint("商品コード");
	Sprint("商品名");
	Sprint("LOT");
	Sprint("数量");
	Sprint("管理日付(賞/製/入)");
	Sprint("入荷日");
	echo "\n";

	while ($data = @mysql_fetch_array($result)) {
		if($data["qty"] > 0 ) {
			Sprint($settingdata["shohincode_add"].$data["shohincd"]);
			Sprint(GetList($shohindata,$data["shohincd"],0));
			Sprint($data["lotnum"]);
			Sprint($data["qty"]);
			Sprint($data["shomi"]);
			Sprint($data["comein"]);
			echo "\n";
		} elseif(($data["qty"] < 0 ) and ($userdata["kengen"] <=10)){
			Sprint($settingdata["shohincode_add"].$data["shohincd"]);
			Sprint(GetList($shohindata,$data["shohincd"],0));
			Sprint($data["lotnum"]);
			Sprint($data["qty"]);
			Sprint($data["shomi"]);
			Sprint($data["comein"]);
			Sprint("集計が合いません");
			echo "\r\n";
		}
	}
	myclose();
Testecho("\"".$strsql."\"");//debug
	die;
}

?><html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link href="css.css" rel="stylesheet" type="text/css">
<?php  title(); ?>
<style type="text/css">
<!--
.border1 {
/*	display: block;*/
}
-->
</style>
</head>
<SCRIPT LANGUAGE="JavaScript">
<!--
function openNewWindowB(cd) {
  form = document.FormDetailSubmit
  form.targetdate.value= cd
  FormDetailSubmit.submit()
}
function submitALL2() {
  form = document.codechange
  codechange.submit()
}
-->
</SCRIPT>
<BODY leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" onLoad="focus()">
<?php Honbutitle3("過去在庫データ",$_SERVER['PHP_SELF']); ?>
<div class="border1">
指定の期首在庫をダウンロードします。<br>在庫数量は指定期首日の作業開始前のもの（＝前日最終在庫と同じ）となります。
<form action="0408.php#fragment-3" method="post" name="codechange" target="_top">
		<?php if ($userdata["kengen"] <> 30) { ?>
		<span class="border1box2">センター<br>
		<select name="center"  onchange="document.codechange.dl.disabled=true;submitALL2();return false;">
		<?  PrintSelectList($centerdata,$center_code,0); ?>
		</select>
		</span>
		<?php } ?>
		<span class="border1box2">期首日<br>
		<?php  PrintSelectDay6(-1200,comparedates(date("Y-m-d"),$norundata[0][0]),"targetdate",$norundata[0][0],"",-1,""); ?>
		</span>
		<span class="border1box2"><br>
		<input type="submit" name="dl" value="ダウンロード" class="box1" >
		</span>
</form>

</div>
<?php if($_POST[shohincd]<>"") { ?>
<table id="data-table">
<tr> 
	<th>LOT</th>
	<th>入荷日</th>
	<th>管理日付</th>
	<th>前日在庫</th>
	<th>入荷</th>
	<th>出荷</th>
	<th>調整</th>
	<th>移動</th>
	<th>当日末在庫</th>
</tr>
<?php 
$intzaiko = 0;$intcomein  = 0;$intshukka = 0; $intarrange = 0;$inttrans = 0;$inttotal = 0; 
while ($data=@mysql_fetch_array($result)) {
	//入荷
	$strsql1 = "SELECT comeinfixqty FROM `".$company."comeinfix` WHERE centerid='".$_POST["center"]."' and kakuteidate = $targetdate and lotnum = ".$data["lotnum"];
	$result1=@mysql_query($strsql1,$my_con);
mysql_sql_log2($strsql1,$_SERVER['SCRIPT_NAME'].":".__LINE__);
Testecho( $strsql1);//debug

	//出荷
	$strsql2 = "SELECT sd.shozokuid,shozokuname, shukkaqty FROM `".$company."shukka` as sd INNER JOIN `".$company."shozokumaster` USING(shozokuid) WHERE sd.centerid='".$_POST["center"]."' and operatedate = $targetdate and lotnum = ".$data["lotnum"]." ORDER BY shozokuid";
	$result2=@mysql_query($strsql2,$my_con); 
mysql_sql_log2($strsql2,$_SERVER['SCRIPT_NAME'].":".__LINE__);
Testecho( $strsql2);//debug

	//調整
	$strsql3 = "SELECT arrangeqty,cname,biko FROM `".$company."stockarrange`,`".$company."code` WHERE centerid='".$_POST["center"]."' and arrangekubun = code and codekind = 2 and arrangedate = $targetdate and lotnum = ".$data["lotnum"];
	$result3=@mysql_query($strsql3,$my_con);
mysql_sql_log2($strsql3,$_SERVER['SCRIPT_NAME'].":".__LINE__);
Testecho( $strsql3);//debug

	//移動
	$strsql4 = "SELECT transqty,tocenterid FROM `".$company."transdata` WHERE centerid='".$_POST["center"]."' and transdate = $targetdate and lotnum = ".$data["lotnum"];
	$result4=@mysql_query($strsql4,$my_con);
mysql_sql_log2($strsql4,$_SERVER['SCRIPT_NAME'].":".__LINE__);
Testecho( $strsql4);//debug
?>
	<tr> 
	    <td><?php  echo $data["lotnum"]; ?></td>
	    <td class="rt"><?php  echo $data["comein"]; ?></td>
	    <td class="rt"><?php  echo $data["shomi"]; ?></td>
	    <td class="rt"><?php  echo $data["qty"]; $intzaiko +=  $data["qty"];$intqty = $data["qty"]; ?></td>
	    <td class="rt"><?php while ($data1 = @mysql_fetch_array($result1)) {echo $data1["comeinfixqty"];$intcomein +=  $data1["comeinfixqty"]; $intqty += $data1["comeinfixqty"]; } ?></td>
		<?php
			if ($userdata["kengen"] == 40) {
		?><td class="rt"><?php
				 while ($data2 = @mysql_fetch_array($result2)) {
					$intshukka +=  $data2["shukkaqty"];$intqty -=  $data2["shukkaqty"]; 
				}
				echo $intshukka;
		?></td><?php
			} else {
		?><td><?php
				while ($data2 = @mysql_fetch_array($result2)) {
					echo "[".$data2["shozokuid"]."]".$data2["shozokuname"]."　".$data2["shukkaqty"]."<br>";
					$intshukka +=  $data2["shukkaqty"];$intqty -=  $data2["shukkaqty"]; 
				}
		?></td><?php
			}
		?>
	    <td class="rt"><?php while ($data3 = @mysql_fetch_array($result3)) {echo $data3["cname"]."　".$data3["biko"]."　".$data3["arrangeqty"]."<br>";$intarrange +=  $data3["arrangeqty"]; $intqty += $data3["arrangeqty"];} ?></td>
	    <td class="rt"><?php while ($data4 = @mysql_fetch_array($result4)) {echo $data4["transqty"]."　".$data4["tocenterid"]."<br>";$inttrans +=  $data4["transqty"];  $intqty -= $dat4["transqty"];} ?></td>
	    <td class="rt"><?php  echo $intqty;$inttotal += $intqty; ?></td>
	</tr> 
<?php
		$counti++;
}
?>
<tr> 
	<th></th>
	<th></th>
	<th></th>
	<td class="rt"><?php  echo $intzaiko; ?></td>
	<td class="rt"><?php  echo $intcomein; ?></td>
	<td class="rt"><?php  echo $intshukka; ?></td>
	<td class="rt"><?php  echo $intarrange; ?></td>
	<td class="rt"><?php  echo $inttrans; ?></td>
	<td class="rt"><?php  echo $inttotal; ?></td>
</tr>
</table>
<?php } ?>
</DIV>
<FORM METHOD="POST" name="FormDetailSubmit" action="0407.php#fragment-3">
<INPUT type="hidden" name="shohincd"        value="<? echo $_POST[shohincd]; ?>">
<INPUT type="hidden" name="targetdate"        value="">
</FORM>

<?php Menu4($_SERVER['PHP_SELF']); ?>
</body>
</html>
<?php                                      
myclose();
Function Sprint($str) {
	echo "\"".Sjis($str)."\",";
}
function compareDates($date1,$date2) {
 $date1_array = explode("-",$date1);
 $date2_array = explode("-",$date2);
 $timestamp1 =
 mktime(0,0,0,$date1_array[1],$date1_array[2],$date1_array[0]);
 $timestamp2 =
 mktime(0,0,0,$date2_array[1],$date2_array[2],$date2_array[0]);
	 if ($timestamp1>$timestamp2) {
		return (($timestamp2 - $timestamp1) / 86400 +1);
	 } elseif ($timestamp1<$timestamp2) {
		return (($timestamp2 - $timestamp1) / 86400 + 1 );
	 } else {
		return 1;
	 }
 }
Testecho( $strsqla);//debug
Testecho( $strsql);//debug
?>