rpt_stk_blance_group2.php

warehouse management system

<?
include("../application.php"); 
$username=$SESSION["user"]["user_name"];
$unit_set=$SESSION["user"]["unit_set"];
include("rptheader.php");
$sql_list="SELECT  stock_product_group.group_id,stock_product_group.group_name, SUM(if(stock_detail.type='+' ,if(stock_detail.op_date>'$op_date'&&stock_detail.op_date<='$to_date',stock_detail.qty,0 ),0) ) as qty_in,SUM(if(stock_detail.type='-',if(stock_detail.op_date>'$op_date'&&stock_detail.op_date<='$to_date',stock_detail.qty,0 ),0) ) as qty_out ,SUM(concat(stock_detail.type , stock_detail.qty)) as bf_qty FROM stock_detail ,stock_product_group,stock_product WHERE  stock_product_group.group_id=stock_product.group_id AND stock_product.product_id=stock_detail.product_id  AND  stock_detail.log_status='1' AND  stock_detail.site_id='$site_id' AND stock_detail.op_date<='$to_date'";
//$sql_list.=" AND  stock_detail.doc_type NOT IN ('".get_not_inven()."')"; 
if($group_id<>"")$sql_list.=" AND stock_product_group.group_id='$group_id'";
$sql_list.=" GROUP BY stock_product_group.group_id";
if($show_zero=="1")$sql_list.=" HAVING bf_qty > 0";
 ?>
<table width="100%"  border="0" cellspacing="0" cellpadding="1" bordercolor="#000000" bgcolor="#ffffff" >
  <tr bgcolor="#ffffff">
		<td  align ="left" class="txtThai" colspan="2">Hitachi Transport  System (Thailand ) Co.,Ltd.<br></td>
		<td  align ="right" class="txtThai" height='20'>User Print : <?echo $username;?></td>
</tr>
 <tr bgcolor="#ffffff">
		<td  align ="left" class="txtThai">STOCK  GROUP BLANCE :&nbsp;<?echo $group_id;?></td>
		<td  align ="center" class="txtThai">DATE BLANCE :&nbsp;<b><?
			list($op_year,$op_month,$op_day)= split ("-", $to_date, 3); 
			echo $op_day." ".engmonth($op_month)."  ".$op_year;
	?></b></td>
		<td  align ="right" class="txtThai" height='20'>Date Print : <?echo date('y-m-d');?></td>
</tr>
 <tr bgcolor="#ffffff">
		<td  align ="left" class="txtThai" colspan="2">CUSTOMMER  : <?echo site_info($site_id);?></td>
		<td  align ="right" class="txtThai" height='20'>Time Print : <?echo date('h:i:s');?></td>
</tr>
</table>
<br>
 <DIV class=outerframe>
<table  border="1" bgcolor="#ffffff" bordercolor="#000000" cellspacing="0" cellpadding="1" width="100%">
 <THEAD>
<?
$sql_list="SELECT  stock_product_group.group_name,stock_product_group.group_id , ";
?>
<tr>
	<td  valign="middle" align="center" width="5%" rowspan="3" class="txtThai">Item</td> 
	<td   align="center"  valign="middle"  width="140" class="txtThaiL" rowspan="3">Group</td> 
	<td   align="center"  width="240" valign="middle" class="txtThaiL" rowspan="3" >Description </td> 
	<td   align="center"  valign="middle" class="txtThaiL"  rowspan="3" width="40">BAL</td> 
	<td   align="center"  valign="middle" class="txtThaiLB"    colspan="<?echo  $mycount*3;?>" >Detail  Order</td> 
</tr>
<tr>
<?
$month_val=str_replace("\\","",$month_val);
$sql_month=" SELECT   start_bak, bak_date,MONTH(bak_date) as xmonth,YEAR(bak_date) as xyear FROM bak_info WHERE bak_id IN('$month_val') ORDER BY start_bak";
//echo $sql_month;
$month_qid = db_query($sql_month);
$i=0;
while ($frm = db_fetch_array($month_qid)) { 
$i++;
if($i==1){
$sql_list.=" SUM(if(".$data_style."stock_detail.op_date<'$frm[start_bak]',concat(".$data_style."stock_detail.type , ".$data_style."stock_detail.qty),0)) as last_qty";
}
?>
	<td   align="center"  valign="middle" class="txtThaiLB" colspan="3" >&nbsp;<?echo engmonth($frm["xmonth"])."  ".$frm["xyear"];?></td> 
<?
	$sql_list.=" ,SUM(if(".$data_style."stock_detail.type='+' && ".$data_style."stock_detail.op_date>='$frm[start_bak]'&&".$data_style."stock_detail.op_date<='$frm[bak_date]',".$data_style."stock_detail.qty,0 )) as qty_in$i, SUM(if(".$data_style."stock_detail.type='-'&& ".$data_style."stock_detail.op_date>='$frm[start_bak]'&&".$data_style."stock_detail.op_date<='$frm[bak_date]',".$data_style."stock_detail.qty,0 )) as qty_out$i,	SUM(concat(".$data_style."stock_detail.type ,if(".$data_style."stock_detail.op_date<='$frm[bak_date]',(".$data_style."stock_detail.qty),0))) as bf_qty$i";
	
}
?>
</tr>
<tr>
<?
$sql_list.="   FROM ".$data_style."stock_detail ,stock_product_group,stock_product WHERE  stock_product_group.group_id=stock_product.group_id AND stock_product.product_id=".$data_style."stock_detail.product_id  AND ".$data_style."stock_detail.log_status='1' AND  ".$data_style."stock_detail.site_id='$site_id'  ";
if(get_not_inven()<>""){
	$sql_list.=" AND  ".$data_style."stock_detail.doc_type NOT IN ('".get_not_inven()."')"; 
}
if($group_id<>"")$sql_list.=" AND stock_product_group.group_id='$group_id'";
if($product_id<>"")$sql_list.=" AND ".$data_style."stock_detail.product_id='$product_id'";
$sql_list.="AND ". $data_style."stock_detail.op_date<='$to_date' GROUP BY stock_product_group.group_id";
//echo $sql_list;
//if($show_zero=="1")$sql_list.=" HAVING bf_qty$loop > 0";
for($i=0;$i<$mycount;$i++){?>
	<td   align="center"  valign="middle"  class="txtThaiL" width="40">IN</td> 
	<td   align="center"  valign="middle"  class="txtThaiL" width="40">OUT</td>
	<td   align="center"  valign="middle" class="txtThaiL" width="40">BAL</td> 
<?}
echo "</tr>";
?>
 </THEAD>
</DIV>
<DIV class=innerframe>
<?
$row=0;
$last_qty=0;
$qid = db_query($sql_list);
while ($frm = db_fetch_array($qid)) { 
$row++;
?>
<tr bgcolor="#ffffff">
	<td  align="center" class="txtThaiB"  ><?echo $row;?>	</td> 
	<td  align="left" class="txtThaiLB" ><?echo $frm["group_id"];?></a></td> 
	<td  align="left" class="txtThaiLB" >&nbsp;<?echo $frm["group_name"];?></td> 
	<td  align="right" class="txtThaiLB" valign="middle"><?echo number_format($frm["last_qty"],$unit_set,'.',',');?></td>
	<?
	$last_qty+=$frm["last_qty"];
	for($i=3;$i<($mycount*3)+3;$i++){
	if (!isset ($sum[$i])) {$sum[$i]=0;}
	$sum[$i]+=$frm[$i];
	?>
		<td  align="right" class="txtThaiLB" valign="middle"><?echo number_format($frm[$i],$unit_set,'.',',');?></td>
	<?}?>
</tr>
<?}?>
 <tr>
	<td colspan="3" align="center" class="txtThai">TOTAL</td>
	<td  align="right" class="txtThaiL"><?echo number_format($last_qty,$unit_set,'.',',');?></td>
	<?
	for($i=3;$i<($mycount*3)+3;$i++){
?>
		<td  align="right" class="txtThaiL" valign="middle"><?echo number_format($sum[$i],$unit_set,'.',',');?></td>
	<?}?>	
</tr>
</DIV>
</table>
 <script language="javascript">
	print_pv(7);
	this.window.close();
</script>